Monday 29 January 2018

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;

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