Query to Find Receipt Class and its GL Combination Query

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

Post a Comment