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
No comments:
Post a Comment