Query to Find Receipt Class and its GL Combinition Query

SELECT ARC.NAME ReceiptClass,

       ARC.CREATION_METHOD_CODE Creation_Mehthod,

       DECODE(ARC.REMIT_METHOD_CODE,

              'STANDARD',

              'Standard',

              NULL,

              'No Remittance') Remittance_Method,

       DECODE(ARC.CLEAR_FLAG,

              'Y',

              'By Matching',

              NULL,

              'Directly',

              'Directly') Clearance_Method,

       ARM.NAME Receipt_Mehtod_Name,

       ARM.PRINTED_NAME Printed_Name,

       ARM.START_DATE Effective_Date,

       ARM.END_DATE End_Date,

       HOU.NAME Operating_Unit,

       CBB.bank_name Bank_Name,

       CBB.bank_branch_name Branch_Name,

       CBA.BANK_ACCOUNT_NAME Bank_Account_Name,

       CBA.CURRENCY_CODE Currency,

       RM.MIN_RECEIPT_AMOUNT Min_Receipt_Amount,

       RM.RISK_ELIMINATION_DAYS Risk_Elimination_Days,

       RM.CLEARING_DAYS Clearing_Days,

       RM.OVERRIDE_REMIT_ACCOUNT_FLAG Override_Bank,

       RM.START_DATE Effective_Days,

       RM.END_DATE End_Days,

       RM.PRIMARY_FLAG Primary_Flag,

       (select gcc.concatenated_segments

          from gl_code_combinations_kfv gcc

         where gcc.code_combination_id = RM.CASH_CCID) Cash,

       (select gcc.concatenated_segments

          from gl_code_combinations_kfv gcc

         where gcc.code_combination_id = RM.RECEIPT_CLEARING_CCID) Receipt_Confirmation,

       (select gcc.concatenated_segments

          from gl_code_combinations_kfv gcc

         where gcc.code_combination_id = RM.REMITTANCE_CCID) Remittance,

       (select gcc.concatenated_segments

          from gl_code_combinations_kfv gcc

         where gcc.code_combination_id = RM.FACTOR_CCID) Factoring,

       (select gcc.concatenated_segments

          from gl_code_combinations_kfv gcc

         where gcc.code_combination_id = RM.SHORT_TERM_DEBT_CCID) Short_Term_Debt,

       (select gcc.concatenated_segments

          from gl_code_combinations_kfv gcc

         where gcc.code_combination_id = RM.BANK_CHARGES_CCID) Bank_Charges,

       (select gcc.concatenated_segments

          from gl_code_combinations_kfv gcc

         where gcc.code_combination_id = RM.UNAPPLIED_CCID) Unapplied_receipts,

       (select gcc.concatenated_segments

          from gl_code_combinations_kfv gcc

         where gcc.code_combination_id = RM.UNIDENTIFIED_CCID) Unidentified_Receipts,

       (select gcc.concatenated_segments

          from gl_code_combinations_kfv gcc

         where gcc.code_combination_id = RM.ON_ACCOUNT_CCID) On_Account,

       arc.receipt_class_id,

       hou.organization_id

  FROM AR_RECEIPT_CLASSES             ARC,

       AR_RECEIPT_METHODS             ARM,

       HR_ALL_ORGANIZATION_UNITS      HOU,

       CE_BANK_ACCT_USES_ALL          CBAU,

       CE_BANK_ACCOUNTS               CBA,

       CE_BANK_BRANCHES_V             CBB,

       AR_RECEIPT_METHOD_ACCOUNTS_ALL RM

 WHERE ARM.RECEIPT_CLASS_ID = ARC.RECEIPT_CLASS_ID

   AND ARM.RECEIPT_METHOD_ID = RM.RECEIPT_METHOD_ID

   AND RM.ORG_ID = HOU.ORGANIZATION_ID

   AND CBAU.BANK_ACCT_USE_ID = RM.REMIT_BANK_ACCT_USE_ID

   AND CBAU.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID

   AND CBB.branch_party_id = CBA.BANK_BRANCH_ID

   AND HOU.NAME = &p_org_name

   AND RM.END_DATE IS NULL

   AND ARM.END_DATE  is null

 order by hou.name, arc.name

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;