2 Replies Latest reply: Nov 21, 2012 4:24 AM by VijayReddyChandamuri RSS

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

    VijayReddyChandamuri
      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.