This discussion is archived
2 Replies Latest reply: Nov 21, 2012 2:24 AM by VijayReddyChandamuri RSS

Regarding change in the account number for an existing service number.

VijayReddyChandamuri Newbie
Currently Being Moderated
Hi,

Here the scenario is as it goes:

A customer with an account number(20230) has a service number(420605254937) mapped to it.

In the month of September. Till 28/09, the account number is mapped to this service number. For the next two days. It has been moved to another account number(22555)

Now in the Billing Summary/Account Summary when I select the month of September, it should show me both the account numbers one contains some data and some contains the other. But it is showing only the old account number in the drop down.

select * from
(
     select unique
'false' checkbox,
ad.account_key as account_key,
ad.account_num as account_num,
                    ad.contact_name as contact_name
from
EDX_RPT_ACCOUNT_DIM ad,
(
select
xr4.*
from
(
(
select
xr3.CHILD_KEY,
xr3.OBJECTURI,
xr3.OBJECTTYPE
from
EDX_RPT_HIERARCHY_XREF_DIM xr3
where
xr3.OBJECTTYPE='edx:amf:billingaccount:'
     start with child_key in (?)
connect by prior child_key = parent_key
) xr4
),
EDX_RPT_HIERARCHY_NODE_PERIOD np
where
xr4.CHILD_KEY=np.XREF_KEY

     and np.PERIOD_KEY=?

) xr5
where
xr5.OBJECTURI='edx:amf:billingaccount:' || ad.BILLER_ID || '|' || ad.ACCOUNT_NUM

          and ad.ACCOUNT_NUM like '%' || ? || '%'

                         and ad.contact_name like '%' || ? || '%'


order by
account_num asc
     
)
order by
account_key asc;



Please let me know how to edit this query.

what I have done was: I removed this part " and ad.ACCOUNT_NUM like '%' || ? || '%'" and it was giving me all the account numbers in that month. But it was user specific and it may affect some other users.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points