SELECT   CU.*,
           H.HA_CODE IMEI,H.ORDER_ID_ENTRY,
           H.MANUFACTURER,
           H.MODEL,
           --H.DESCRIPTION,
           SKU,
           INV.OTTRANSACTION_DATE PURCHASE_DATE,
           TAC_CODE
    FROM   (SELECT   CUSTOMER_ID,
                     OH.BILLING_ACCOUNT_ID,
                     OTXACT,
                     OTSEQ,
                     OTTRANSACTION_DATE
              FROM   ORDERHDR_ALL OH, ORDERTRAILER OT
             WHERE   OH.OHXACT = OT.OTXACT) INV,
           (SELECT   OTC.STRING01,
                     OTC.CHAR_ID,
                     OTC.STRING04,
                     OTC.OTXACT,
                     OTC.OTSEQ,
                     OTC.ORDER_ID_ENTRY
              FROM   ORDERTRLR_CHARACTERISTICS OTC, CHARACTERISTICS_DEF CD
             WHERE       OTC.CHAR_ID = CD.CHAR_ID
                     AND CD.CHAR_SHDES = 'FDHGE'
                     AND STRING04 = 'Handset') CH,
           (SELECT   HA.HA_CODE,
                     HA.SKU,
                     HA.CUSTOMER_ID,
                     HA.CO_ID,
                     HA.VALID_TO,
                     HA.ORDER_ID_ENTRY,
                     HA.DESCRIPTION,
                     D.MANUFACTURER,
                     D.MODEL, 
                     DV.TAC_CODE
              FROM   HARDWARE_ASSET HA,
                     MPDNPTAB M,
                     EPCCSOM.DEVICE_VARIANT DV,
                     EPCCSOM.DEVICE_OFFERING D
             WHERE       HA.NPCODE = M.NPCODE
                     AND M.SHDES = 'IMEI'
                     AND HA.SKU = DV.SKU
                     --AND SUBSTR(HA_CODE, 1, 8) = DV.TAC_CODE(+)
                     and dv.tac_code not like '%'||substr(ha_Code, 1, 8)||'%'
                     AND DV.PRODUCT_OFFERING_ID = D.PRODUCT_OFFERING_ID) H,
           (SELECT   CUA.CUSTOMER_ID,
                     BA.BILLING_ACCOUNT_ID,
                     BA.BILLING_ACCOUNT_CODE,
                     DN.DN_NUM MSISDN,
                     COA.CO_ID,
                     COA.CO_CODE
              FROM   CUSTOMER_ALL CUA,
                     ( SELECT BA.* FROM                     
                     BILLING_ACCOUNT BA, BILLACC_CHARACTERISTICS BAC, CHARACTERISTICS_DEF CD
                     WHERE BA.BILLING_ACCOUNT_ID = BAC.BILLING_ACCOUNT_ID
                     AND BAC.CHAR_ID = CD.CHAR_ID
                     AND CD.CHAR_SHDES='BAEXT'
                     AND STRING23='No') BA,
                     CONTRACT_ALL COA,
                     (SELECT   BAA.CONTRACT_ID,
                               CSC.CO_ID,
                               CSC.DN_ID,
                               CSC.SEQNO,
                               CSC.PROFILE_ID,
                               CSC.SNCODE,
                               DN.DN_NUM,
                               BAA.BILLING_ACCOUNT_ID
                        FROM   BILLING_ACCOUNT_ASSIGNMENT BAA,
                               CONTR_SERVICES_CAP CSC,
                               DIRECTORY_NUMBER DN
                       WHERE   BAA.CONTRACT_ID = CSC.CO_ID
                               AND CSC.DN_ID = DN.DN_ID
                               AND CSC.SEQNO IN
                                        (SELECT   MAX (SEQNO)
                                           FROM   CONTR_SERVICES_CAP
                                          WHERE   CO_ID = CSC.CO_ID
                                                  AND SNCODE = CSC.SNCODE
                                                  AND PROFILE_ID =
                                                        CSC.PROFILE_ID)) DN
             WHERE       CUA.CUSTOMER_ID = COA.CUSTOMER_ID
                     AND COA.CO_ID = DN.CO_ID
                     AND CUA.CUSTOMER_ID = BA.CUSTOMER_ID
                     AND BA.BILLING_ACCOUNT_ID = DN.BILLING_ACCOUNT_ID) CU
   WHERE       INV.OTXACT = CH.OTXACT
           AND INV.OTSEQ = CH.OTSEQ
           AND INV.CUSTOMER_ID = H.CUSTOMER_ID
           AND CH.STRING01 = H.SKU
           AND INV.CUSTOMER_ID = CU.CUSTOMER_ID
           AND INV.BILLING_ACCOUNT_ID = CU.BILLING_ACCOUNT_ID
           AND H.CO_ID = CU.CO_ID
           AND H.ORDER_ID_ENTRY = CH.ORDER_ID_ENTRY
           AND H.VALID_TO IS NULL
           and model is null
           --AND CU.BILLING_ACCOUNT_ID in
           --(1000025685,1000024723,1000022541,1000022645 )
ORDER BY   OTTRANSACTION_DATE DESC
