Query to find Active Customer in Oracle Apps

SELECT hou.name Organization_name,

       ----------------------

       --Customer Information

       ----------------------

       hp.party_id,

       hp.party_name       "CUSTOMER_NAME",

       hca.cust_account_id,

       hca.account_number,

       hcas.org_id,

       ---------------------------

       --Customer Site Information

       ---------------------------

       hcas.cust_acct_site_id,

       hps.party_site_number,

       hcsu.site_use_code,

       -----------------------

       --Customer Site Address

       -----------------------

       hl.address1,

       hl.address2,

       hl.address3,

       hl.address4,

       hl.city,

       hl.postal_code,

       hl.state,

       hl.province,

       hl.county,

       hl.country,

       hl.address_style

  FROM hz_parties             hp,

       hz_party_sites         hps,

       hz_cust_accounts_all   hca,

       hz_cust_acct_sites_all hcas,

       hz_cust_site_uses_all  hcsu,

       hz_locations           hl,

       hr_operating_units     hou

 WHERE 1 = 1

   AND hp.party_id = hca.party_id

   AND hca.cust_account_id = hcas.cust_account_id(+)

   AND hps.party_site_id(+) = hcas.party_site_id

   AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id

   AND hps.location_id = hl.location_id(+)

   AND hp.party_type = 'ORGANIZATION' -- only ORGANIZATION Party types

   AND hp.status = 'A' -- only Active Parties/Customers

   AND hps.status = 'A'

   AND hcas.org_id = &porg_id

   AND hcas.org_id = hou.organization_id

   AND hcsu.site_use_code = 'BILL_TO'

-- AND hp.party_name = 'ABU DHABI ISLAMIC BANK'

 ORDER BY hp.party_name, hca.account_number;

Customer Ledger/ Debtor Ledger

SELECT   ORG_ID,
                          CUSTOMER_NAME,
                          CUSTOMER_NUMBER,
                          CUSTOMER_ID,
                          TRX_DATE,
                          TRX_NUMBER,
                          CUSTOMER_TRX_ID,
                          VOUCHER_NO,
                          DUE_DATE,
                          AMOUNT_DR,
                          AMOUNT_CR,
                          USER_NAME,
                          EXCISE_INVOICE_NO,
                          TAX_INVOICE_NO,
                          GL_DATE,
                          CODE_COMBINATION,
                          ACCOUNT,
                          AC_DESC,
                          NAME,
                          VOC_SEQ
                   FROM   (  SELECT   HR.NAME ORG_ID,
                                      ARC.CUSTOMER_NAME,
                                      ARC.CUSTOMER_NUMBER,
                                      ARC.CUSTOMER_ID,
                                      RCT.TRX_DATE,
                                      RCT.TRX_NUMBER,
                                      RCT.CUSTOMER_TRX_ID,
                                      RCT.DOC_SEQUENCE_VALUE VOUCHER_NO,
                                      (SELECT   DUE_DATE
                                         FROM   AR_PAYMENT_SCHEDULES_ALL
                                        WHERE   CUSTOMER_TRX_ID =
                                                   RCT.CUSTOMER_TRX_ID
                                                AND ROWNUM = 1)
                                         DUE_DATE,
                                      NVL (
                                         DECODE (
                                            RCTT.TYPE,
                                            'INV',
                                            DECODE (
                                               RCT.INVOICE_CURRENCY_CODE,
                                               'INR',
                                               SUM (RCTL.AMOUNT),
                                               SUM (RCTL.AMOUNT)
                                               * RCT.EXCHANGE_RATE
                                            ),
                                            'DM',
                                            DECODE (
                                               RCT.INVOICE_CURRENCY_CODE,
                                               'INR',
                                               SUM (RCTL.AMOUNT),
                                               SUM (RCTL.AMOUNT)
                                               * RCT.EXCHANGE_RATE
                                            ),
                                            'DEP',
                                            DECODE (
                                               RCT.INVOICE_CURRENCY_CODE,
                                               'INR',
                                               SUM (RCTL.AMOUNT),
                                               SUM (RCTL.AMOUNT)
                                               * RCT.EXCHANGE_RATE
                                            )
                                         ),
                                         0
                                      )
                                         AMOUNT_DR,
                                      NVL (
                                         DECODE (
                                            RCTT.TYPE,
                                            'CM',
                                            DECODE (
                                               RCT.INVOICE_CURRENCY_CODE,
                                               'INR',
                                               ABS (SUM (RCTL.AMOUNT)),
                                               ABS (SUM (RCTL.AMOUNT))
                                               * RCT.EXCHANGE_RATE
                                            )
                                         ),
                                         0
                                      )
                                         AMOUNT_CR,
                                      FU.USER_NAME,
                                      (SELECT   JATL.EXCISE_INVOICE_NO
                                         FROM   JAI_AR_TRX_LINES JATL
                                        WHERE   JATL.CUSTOMER_TRX_ID =
                                                   RCT.CUSTOMER_TRX_ID
                                                AND ROWNUM = 1)
                                         EXCISE_INVOICE_NO,
                                      RCTL.GL_DATE,
                                      GCC.CONCATENATED_SEGMENTS CODE_COMBINATION,
                                      GCC.SEGMENT3 ACCOUNT,
                                      (SELECT   DESCRIPTION
                                         FROM   RA_CUSTOMER_TRX_LINES_ALL
                                        WHERE   CUSTOMER_TRX_ID =
                                                   RCT.CUSTOMER_TRX_ID
                                                AND ROWNUM = 1)
                                         AC_DESC,
                                      RCTT.NAME,
                                      (SELECT   NAME
                                         FROM   FND_DOCUMENT_SEQUENCES FDS
                                        WHERE   FDS.DOC_SEQUENCE_ID =
                                                   RCT.DOC_SEQUENCE_ID)
                                         VOC_SEQ,
                                      (SELECT   NVL (JOW.VAT_INVOICE_NO,
                                                     WND.ATTRIBUTE1)
                                         FROM   RA_CUSTOMER_TRX_LINES_ALL RCTL,
                                                JAI_OM_WSH_LINES_ALL JOW,
                                                WSH_NEW_DELIVERIES WND
                                        WHERE   RCTL.INTERFACE_LINE_ATTRIBUTE3 =
                                                   TO_CHAR (JOW.DELIVERY_ID)
                                                AND WND.DELIVERY_ID =
                                                      JOW.DELIVERY_ID
                                                AND RCTL.CUSTOMER_TRX_ID =
                                                      RCT.CUSTOMER_TRX_ID
                                                AND ROWNUM = 1)
                                         TAX_INVOICE_NO
                               FROM   RA_CUSTOMER_TRX_ALL RCT,
                                      RA_CUST_TRX_LINE_GL_DIST_ALL RCTL,
                                      FND_USER FU,
                                      RA_CUST_TRX_TYPES_ALL RCTT,
                                      GL_CODE_COMBINATIONS_KFV GCC,
                                      FND_FLEX_VALUES_VL FFVL,
                                      AR_CUSTOMERS ARC,
                                      HR_OPERATING_UNITS HR,
                                      HZ_CUST_ACCOUNTS HCA
                              WHERE   RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
                                      AND RCTL.ACCOUNT_CLASS = 'REC'
                                      AND FU.USER_ID = RCT.CREATED_BY
                                      AND RCTT.CUST_TRX_TYPE_ID =
                                            RCT.CUST_TRX_TYPE_ID
                                      AND GCC.CODE_COMBINATION_ID =
                                            RCTL.CODE_COMBINATION_ID
                                      AND GCC.SEGMENT3 = FFVL.FLEX_VALUE
                                      AND FFVL.FLEX_VALUE_SET_ID = 1014163
                                      AND ARC.CUSTOMER_ID =
                                            RCT.BILL_TO_CUSTOMER_ID
                                      AND RCT.ORG_ID = RCTT.ORG_ID
                                      --AND RCT.TRX_NUMBER = '84002579'
                                      AND RCT.COMPLETE_FLAG = 'Y'
                                      AND ARC.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
                                      AND TRUNC (RCTL.GL_DATE) BETWEEN P_FROM_DATE
                                                                   AND  P_TO_DATE
                                      AND ARC.CUSTOMER_ID =
                                            NVL (P_CUSTOMER_ID, ARC.CUSTOMER_ID)
                                      -- AND GCC.SEGMENT3 = NVL(:P_ACCOUNT, GCC.SEGMENT3)
                                      --AND RCT.ORG_ID = 81
                                      ---NVL(:P_ORG_ID, RCT.ORG_ID)
                                      --- AND SUBSTR(HCA.CUSTOMER_CLASS_CODE, 1,2) = NVL(:P_BUSINESS_LINE, SUBSTR(HCA.CUSTOMER_CLASS_CODE, 1,2))
                                      AND RCT.ORG_ID = HR.ORGANIZATION_ID
                                      AND NVL (ARC.CUSTOMER_CATEGORY_CODE, '~') =
                                            NVL (
                                               P_CUST_TYPE,
                                               NVL (ARC.CUSTOMER_CATEGORY_CODE,
                                                    '~')
                                            )
                           GROUP BY   ARC.CUSTOMER_NAME,
                                      ARC.CUSTOMER_NUMBER,
                                      ARC.CUSTOMER_ID,
                                      RCT.TRX_DATE,
                                      RCT.TRX_NUMBER,
                                      RCT.DOC_SEQUENCE_VALUE,
                                      RCT.CUSTOMER_TRX_ID,
                                      FU.USER_NAME,
                                      RCTL.GL_DATE,
                                      RCTT.NAME,
                                      GCC.CONCATENATED_SEGMENTS,
                                      GCC.SEGMENT3,
                                      FFVL.DESCRIPTION,
                                      RCT.INVOICE_CURRENCY_CODE,
                                      RCT.EXCHANGE_RATE,
                                      RCTT.TYPE,
                                      RCT.ORG_ID,
                                      RCT.DOC_SEQUENCE_ID,
                                      HR.NAME
                           UNION
                             SELECT   HR.NAME ORG_ID,
                                      ARC.CUSTOMER_NAME,
                                      ARC.CUSTOMER_NUMBER,
                                      ARC.CUSTOMER_ID,
                                      ACR.RECEIPT_DATE TRX_DATE,
                                      ACR.RECEIPT_NUMBER TRX_NUMBER,
                                      ACR.CASH_RECEIPT_ID CUSTOMER_TRX_ID,
                                      ACR.DOC_SEQUENCE_VALUE VOUCHER_NO,
                                      ACR.RECEIPT_DATE DUE_DATE,
                                      NVL (SUM (ADR.ACCTD_AMOUNT_CR), 0)
                                         AMOUNT_DR,
                                      NVL (SUM (ADR.ACCTD_AMOUNT_DR), 0)
                                      - NVL (
                                           (SELECT   SUM (ARA.AMOUNT_APPLIED)
                                              FROM   AR_RECEIVABLE_APPLICATIONS_V ARA
                                             WHERE   ARA.CASH_RECEIPT_ID =
                                                        ACR.CASH_RECEIPT_ID
                                                     AND ARA.TRX_NUMBER =
                                                           'Refund'),
                                           0
                                        )
                                         AMOUNT_CR,
                                      FU.USER_NAME,
                                      NULL EXCISE_INVOICE_NO,
                                      ACRH.GL_DATE GL_DATE,
                                      GCC.CONCATENATED_SEGMENTS CODE_COMBINATION,
                                      GCC.SEGMENT3 ACCOUNT,
                                      NULL                /*FFVL.DESCRIPTION*/
                                          AC_DESC,
                                      ARM.NAME NAME,
                                      (SELECT   NAME
                                         FROM   FND_DOCUMENT_SEQUENCES FDS
                                        WHERE   FDS.DOC_SEQUENCE_ID =
                                                   ACR.DOC_SEQUENCE_ID)
                                         VOC_SEQ,
                                      NULL TAX_INVOICE_NO
                               FROM   AR_CASH_RECEIPTS_ALL ACR,
                                      AR_CASH_RECEIPT_HISTORY_ALL ACRH,
                                      AR_DISTRIBUTIONS_ALL ADR,
                                      FND_USER FU,
                                      AR_CUSTOMERS ARC,
                                      GL_CODE_COMBINATIONS_KFV GCC,
                                      FND_FLEX_VALUES_VL FFVL,
                                      AR_RECEIPT_METHODS ARM,
                                      HR_OPERATING_UNITS HR,
                                      HZ_CUST_ACCOUNTS HCA
                              WHERE   ACR.CASH_RECEIPT_ID = ACRH.CASH_RECEIPT_ID
                                      AND FU.USER_ID = ACR.CREATED_BY
                                      AND ARM.RECEIPT_METHOD_ID =
                                            ACR.RECEIPT_METHOD_ID
                                      AND ARC.CUSTOMER_ID = ACR.PAY_FROM_CUSTOMER
                                      AND GCC.CODE_COMBINATION_ID =
                                            ADR.CODE_COMBINATION_ID
                                      AND ADR.SOURCE_TYPE IN
                                               ('REMITTANCE', 'CASH')
                                      --AND ACRH.STATUS='CLEARED'
                                      --AND ADR.SOURCE_TYPE='CASH'
                                      AND ADR.SOURCE_ID =
                                            ACRH.CASH_RECEIPT_HISTORY_ID
                                      AND GCC.SEGMENT3 = FFVL.FLEX_VALUE
                                      AND FFVL.FLEX_VALUE_SET_ID = 1014163
                                      AND ARC.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
                                      -- AND ACR.RECEIPT_NUMBER ='SR0600200'--'SUPP. INV.NO.-10107033'
                                      AND TRUNC (ACRH.GL_DATE) BETWEEN P_FROM_DATE
                                                                   AND  P_TO_DATE
                                      AND ARC.CUSTOMER_ID =
                                            NVL (P_CUSTOMER_ID, ARC.CUSTOMER_ID)
                                      --AND ACR.ORG_ID = 81
                                      --NVL(:P_ORG_ID, ACR.ORG_ID)
                                      --AND ARC.CUSTOMER_ID BETWEEN
                                      --   NVL(:P_FROM_CUSTOMER_ID, ARC.CUSTOMER_ID) AND
                                      --  NVL(:P_TO_CUSTOMER_ID, ARC.CUSTOMER_ID)
                                      ------  AND GCC.SEGMENT3 = NVL(:P_ACCOUNT, GCC.SEGMENT3)
                                      ---AND SUBSTR(HCA.CUSTOMER_CLASS_CODE, 1,2) = NVL(:P_BUSINESS_LINE, SUBSTR(HCA.CUSTOMER_CLASS_CODE, 1,2))
                                      AND NVL (ARC.CUSTOMER_CATEGORY_CODE, '~') =
                                            NVL (
                                               P_CUST_TYPE,
                                               NVL (ARC.CUSTOMER_CATEGORY_CODE,
                                                    '~')
                                            )
                                      AND CONFIRMED_FLAG = 'Y'
                                      AND ACR.ORG_ID = HR.ORGANIZATION_ID
                           GROUP BY   ACR.RECEIPT_DATE,
                                      ACR.RECEIPT_NUMBER,
                                      ACR.DOC_SEQUENCE_VALUE,
                                      ACR.RECEIPT_DATE,
                                      FU.USER_NAME,
                                      ARC.CUSTOMER_NAME,
                                      ARC.CUSTOMER_NUMBER,
                                      ARC.CUSTOMER_ID,
                                      ACR.CASH_RECEIPT_ID,
                                      ACRH.GL_DATE,
                                      GCC.CONCATENATED_SEGMENTS,
                                      GCC.SEGMENT3,
                                      FFVL.DESCRIPTION,
                                      ACR.ORG_ID,
                                      HR.NAME,
                                      ACR.DOC_SEQUENCE_ID,
                                      ARM.NAME) A
                  WHERE   A.DUE_DATE = NVL (NULL, A.DUE_DATE)
                          AND AMOUNT_DR <> AMOUNT_CR
               ORDER BY   TRX_DATE, GL_DATE;