CRM Module - AMC Expiry Query

SELECT
rownum SR_NO, OPERATING_UNIT, CONTRACT_NUMBER, CUSTOMER_NAME,LOCATION,
         BILL_TO,SHIP_TO,SITE_INCHARGE_NAME, CONTACT_DETAILS,
         MACHINE_MODEL, SERIAL_NUMBER, START_DATE, END_DATE,
         PAYMENT_TERMS, SALES_PERSON_NAME,AMOUNT, CLE_ID
FROM (SELECT  XX.SR_NO, XX.OPERATING_UNIT, XX.CONTRACT_NUMBER, XX.CUSTOMER_NAME,XX.CITY LOCATION,
         XX.BILL_TO, XX.SHIP_TO, XX.SITE_INCHARGE_NAME, XX.CONTACT_DETAILS,
         XX.MACHINE_MODEL, XX.SERIAL_NUMBER, XX.START_DATE, XX.END_DATE,
         XX.PAYMENT_TERMS, XX.SALES_PERSON_NAME,XX.AMOUNT, XX.CLE_ID
    FROM (SELECT DISTINCT rownum SR_NO, X.OPERATING_UNIT, X.CONTRACT_NUMBER, X.CUSTOMER_NAME,X.CITY, X.PAYMENT_TERM_ID,
                 (SELECT DISTINCT    HL.ADDRESS1
                                  || HL.ADDRESS2
                                  || HL.ADDRESS3
                                  || HL.CITY
                                  || HL.STATE
                             FROM OKC_K_HEADERS_ALL_B OKHAB,
                                  HZ_CUST_SITE_USES_ALL HCSUA,
                                  HZ_CUST_ACCT_SITES_ALL HCASA,
                                  HZ_PARTY_SITES HPS,
                                  HZ_LOCATIONS HL
                            WHERE OKHAB.CONTRACT_NUMBER = X.CONTRACT_NUMBER
                              AND HCSUA.SITE_USE_ID =
                                                     OKHAB.SHIP_TO_SITE_USE_ID
                              AND HCASA.CUST_ACCT_SITE_ID =
                                                       HCSUA.CUST_ACCT_SITE_ID
                              AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID
                              AND HL.LOCATION_ID = HPS.LOCATION_ID) BILL_TO,
                 X.SHIP_TO, X.SITE_INCHARGE_NAME, X.CONTACT_DETAILS,
                 X.MACHINE_MODEL, X.SERIAL_NUMBER, X.START_DATE, X.END_DATE,
                 (SELECT RT.NAME FROM RA_TERMS RT ,okc_k_headers_all_b a
                    WHERE A.PAYMENT_TERM_ID=RT.TERM_ID
                    AND a.ID = X.ID) PAYMENT_TERMS,
                 (select LAST_NAME ||' '|| FIRST_NAME from PER_ALL_PEOPLE_F where person_id =
                   ( select PERSON_ID from jtf_rs_salesreps where salesrep_id = NVL(X.OBJECT1_ID1,salesrep_id)))
                                                            SALES_PERSON_NAME,
                 X.AMOUNT, X.CLE_ID
            FROM (SELECT DISTINCT HR.NAME AS OPERATING_UNIT,
                                  OKHAB.CONTRACT_NUMBER AS CONTRACT_NUMBER,I.CLE_ID,
                                  OKHAB.PAYMENT_TERM_ID,OK.OBJECT1_ID1,
                                  OLDV.CUSTOMER_ACCOUNT_NAME CUSTOMER_NAME,CA.CITY,
                                  OKHAB.ID,
                                  (   CA.ADDRESS1
                                   || CA.ADDRESS2
                                   || CA.ADDRESS3
                                   || CA.CITY
                                  ) SHIP_TO,
                                  OKLB.ATTRIBUTE1 SITE_INCHARGE_NAME,
                                  OKLB.ATTRIBUTE2 CONTACT_DETAILS,
                                  MTL.DESCRIPTION MACHINE_MODEL,
                                  CII.SERIAL_NUMBER SERIAL_NUMBER,
--                                  OAL.START_DATE, OAL.END_DATE,
                                 (TO_CHAR (OAL.START_DATE,'DD-MON-RRRR ')) START_DATE,
                                  OAL.END_DATE END_DATE,
                                  ROUND(OKLB.PRICE_NEGOTIATED) AS AMOUNT
                  FROM            OKC_K_HEADERS_ALL_B OKHAB,
                                  OKC_K_LINES_B OKLB,
                                  HR_OPERATING_UNITS HR,
                                  OKS_LINE_DETAILS_V OLDV,
                                  OKS_AUTH_LINES_V OAL,
                                  OKC_K_ITEMS I,
                                  CSI_ITEM_INSTANCES CII,
                                  MTL_SYSTEM_ITEMS_B MTL,
                                  CSI_HZPTY_ADDRESSES_V CA,
                                  OKC_CONTACTS OK,
                                  HZ_PARTY_SITE_USES HZP
                            WHERE HR.ORGANIZATION_ID = OKHAB.AUTHORING_ORG_ID
                              AND OKLB.DNZ_CHR_ID = OKHAB.ID
                              AND OLDV.CONTRACT_ID = OKHAB.ID
                              AND OAL.CLE_ID = OKLB.CLE_ID
                              AND I.CLE_ID = OKLB.ID
                              AND CII.INSTANCE_ID = I.OBJECT1_ID1
                              AND CII.INVENTORY_ITEM_ID =
                                                         MTL.INVENTORY_ITEM_ID
                              AND OKHAB.INV_ORGANIZATION_ID =
                                                           MTL.ORGANIZATION_ID
                              AND CII.LOCATION_ID = CA.LOCATION
                              AND CA.LOCATION = HZP.PARTY_SITE_ID(+)
                              AND SITE_USE_TYPE(+) = 'INSTALL_AT'
                              AND HZP.STATUS(+) = 'A'
                              AND CA.STATUS = 'A'
and ok.DNZ_CHR_ID = NVL(OKHAB.ID , OK.dnz_chr_id)
AND ok.SALES_GROUP_ID IS NOT NULL
                              AND OKHAB.CONTRACT_NUMBER =
                                     NVL (:CONTRACT_NUMBER,
                                          OKHAB.CONTRACT_NUMBER
                                         )
                              AND OKHAB.ORG_ID = NVL (:P_ORG_ID, OKHAB.ORG_ID)
                              AND TRUNC (OAL.END_DATE)
                                     BETWEEN NVL (TO_DATE (:P_START_DATE),
                                                  TRUNC (OAL.END_DATE)
                                                 )
                                         AND NVL (TO_DATE (:P_END_DATE),
                                                  TRUNC (SYSDATE)
                                                 )
                                                 ) X
                                                 WHERE X.CUSTOMER_NAME IS NOT NULL
                                                 ORDER BY X.CLE_ID ASC
                                                 ) XX     WHERE XX.SALES_PERSON_NAME IS NOT NULL
UNION ALL
 SELECT  XX.SR_NO, XX.OPERATING_UNIT, XX.CONTRACT_NUMBER, XX.CUSTOMER_NAME, XX.CITY LOCATION,
         XX.BILL_TO, XX.SHIP_TO, XX.SITE_INCHARGE_NAME, XX.CONTACT_DETAILS,
         XX.MACHINE_MODEL, NULL SERIAL_NUMBER, XX.START_DATE, XX.END_DATE,
         XX.PAYMENT_TERMS, XX.SALES_PERSON_NAME,XX.AMOUNT, XX.CLE_ID FROM (
 SELECT DISTINCT rownum SR_NO ,X.OPERATING_UNIT, X.CONTRACT_NUMBER, X.CUSTOMER_NAME,/*X.CITY ,*/ X.PAYMENT_TERM_ID,
                 (SELECT DISTINCT    HL.ADDRESS1
                                  || HL.ADDRESS2
                                  || HL.ADDRESS3
                                  || HL.CITY
                                  || HL.STATE
                             FROM OKC_K_HEADERS_ALL_B OKHAB,
                                  HZ_CUST_SITE_USES_ALL HCSUA,
                                  HZ_CUST_ACCT_SITES_ALL HCASA,
                                  HZ_PARTY_SITES HPS,
                                  HZ_LOCATIONS HL
                            WHERE OKHAB.CONTRACT_NUMBER = X.CONTRACT_NUMBER
                              AND HCSUA.SITE_USE_ID =
                                                     OKHAB.SHIP_TO_SITE_USE_ID
                              AND HCASA.CUST_ACCT_SITE_ID =
                                                       HCSUA.CUST_ACCT_SITE_ID
                              AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID
                              AND HL.LOCATION_ID = HPS.LOCATION_ID) BILL_TO,
                              ( SELECT DISTINCT  HLA.CITY
                              FROM OKC_K_HEADERS_ALL_B OKHAB,
                                  HZ_CUST_SITE_USES_ALL HCSUA,
                                  HZ_CUST_ACCT_SITES_ALL HCASA,
                                  HZ_PARTY_SITES HPS,
                                  HZ_LOCATIONS HLA
                            WHERE OKHAB.CONTRACT_NUMBER =X.CONTRACT_NUMBER
                               and hcsua.SITE_USE_ID=okhab.BILL_TO_SITE_USE_ID
and hcasa.CUST_ACCT_SITE_ID = hcsua.CUST_ACCT_SITE_ID
and hps.PARTY_SITE_ID = hcasa.PARTY_SITE_ID
and HLA.LOCATION_ID = hps.LOCATION_ID) CITY,
                              ( SELECT DISTINCT    HLA.ADDRESS1
                                  || HLA.ADDRESS2
                                  || HLA.ADDRESS3
                                  || HLA.CITY
                                  || HLA.STATE
                             FROM OKC_K_HEADERS_ALL_B OKHAB,
                                  HZ_CUST_SITE_USES_ALL HCSUA,
                                  HZ_CUST_ACCT_SITES_ALL HCASA,
                                  HZ_PARTY_SITES HPS,
                                  HZ_LOCATIONS HLA
                            WHERE OKHAB.CONTRACT_NUMBER =X.CONTRACT_NUMBER
                               and hcsua.SITE_USE_ID=okhab.BILL_TO_SITE_USE_ID
and hcasa.CUST_ACCT_SITE_ID = hcsua.CUST_ACCT_SITE_ID
and hps.PARTY_SITE_ID = hcasa.PARTY_SITE_ID
and HLA.LOCATION_ID = hps.LOCATION_ID) SHIP_TO, X.SITE_INCHARGE_NAME, X.CONTACT_DETAILS,
                 X.MACHINE_MODEL, X.START_DATE, X.END_DATE,
                 (SELECT RT.NAME FROM RA_TERMS RT ,okc_k_headers_all_b a
                    WHERE A.PAYMENT_TERM_ID=RT.TERM_ID
                    AND a.ID = X.ID) PAYMENT_TERMS,
                 (select LAST_NAME ||' '|| FIRST_NAME from PER_ALL_PEOPLE_F where person_id =
                   ( select PERSON_ID from jtf_rs_salesreps where salesrep_id = NVL(X.OBJECT1_ID1,salesrep_id)))
                                                            SALES_PERSON_NAME,
                 X.AMOUNT, X.CLE_ID FROM (
SELECT DISTINCT HR.NAME AS OPERATING_UNIT,
                                  OKHAB.CONTRACT_NUMBER AS CONTRACT_NUMBER,I.CLE_ID,
                                  OKHAB.PAYMENT_TERM_ID,OK.OBJECT1_ID1,
                                  OLDV.CUSTOMER_ACCOUNT_NAME CUSTOMER_NAME,
                                  OKHAB.ID,
                                  OKLB.ATTRIBUTE1 SITE_INCHARGE_NAME,
                                  OKLB.ATTRIBUTE2 CONTACT_DETAILS,
                                  MTL.DESCRIPTION MACHINE_MODEL,
                                  NULL SERIAL_NUMBER,
                                  (TO_CHAR (OAL.START_DATE,'DD-MON-RRRR ') )START_DATE,
                                    OAL.END_DATE END_DATE,
                                  OKLB.PRICE_NEGOTIATED AS AMOUNT
                  FROM            OKC_K_HEADERS_ALL_B OKHAB,
                                  OKC_K_LINES_B OKLB,
                                  HR_OPERATING_UNITS HR,
                                  OKS_LINE_DETAILS_V OLDV,
                                  OKS_AUTH_LINES_V OAL,
                                  OKC_K_ITEMS I,
--                                  CSI_ITEM_INSTANCES CII,
                                  MTL_SYSTEM_ITEMS_B MTL,
--                                  CSI_HZPTY_ADDRESSES_V CA,
                                  OKC_CONTACTS OK,
                                  HZ_PARTY_SITE_USES HZP
                            WHERE HR.ORGANIZATION_ID = OKHAB.AUTHORING_ORG_ID
                              AND OKLB.DNZ_CHR_ID = OKHAB.ID
                              AND OLDV.CONTRACT_ID = OKHAB.ID
                              AND OAL.CLE_ID = OKLB.CLE_ID
                              AND I.CLE_ID = OKLB.ID
                              AND I.OBJECT1_ID2 = TO_CHAR(MTL.ORGANIZATION_ID)
                              AND I.OBJECT1_ID1 = TO_CHAR(MTL.INVENTORY_ITEM_ID)
                              AND OKHAB.INV_ORGANIZATION_ID =
                                                           MTL.ORGANIZATION_ID
                              AND MTL.ITEM_TYPE IS NOT NULL
and ok.DNZ_CHR_ID = NVL(OKHAB.ID , OK.dnz_chr_id)
AND ok.SALES_GROUP_ID IS NOT NULL
                              AND OKHAB.CONTRACT_NUMBER =
                                     NVL (:CONTRACT_NUMBER,
                                          OKHAB.CONTRACT_NUMBER
                                         )
                              AND OKHAB.ORG_ID = NVL (:P_ORG_ID, OKHAB.ORG_ID)
                              AND TRUNC (OAL.END_DATE)
                                     BETWEEN NVL (TO_DATE (:P_START_DATE),
                                                  TRUNC (OAL.END_DATE)
                                                 )
                                         AND NVL (TO_DATE (:P_END_DATE),
                                                  TRUNC (SYSDATE)
                                                 )
                                                 )X
                                                  WHERE X.CUSTOMER_NAME IS NOT NULL
                                                  ORDER BY X.CLE_ID ASC
                                                 )XX  
                                                )
                                                ORDER BY SR_NO