CE Cash In Transit - draft
Description
Categories: BI Publisher
Application: Cash Management
Source: Cash In Transit Report (XML) - Not Supported: Reserved For Future Use
Short Name: CEXCSHTR_XML
DB package: CE_CEXCSHTR_XMLP_PKG
Source: Cash In Transit Report (XML) - Not Supported: Reserved For Future Use
Short Name: CEXCSHTR_XML
DB package: CE_CEXCSHTR_XMLP_PKG
SELECT 'Payments' type, c.vendor_name supplier_customer, ba.bank_account_name bank_account_name, ba.bank_account_num bank_account_num, ba.currency_code bank_account_currency, c.check_date payment_date, c.future_pay_due_date maturity_date, to_char(c.check_number) payrcpt_num, c.payment_method_lookup_code payment_method, c.currency_code c_currency_code, nvl(c.amount,0) amount, DECODE(c.currency_code,ba.currency_code, NVL(-c.amount,0), NVL(-c.base_amount,0)) cash_in_transit, DECODE(c.currency_code,ba.currency_code, NVL(c.amount,0), NVL(c.base_amount,0)) base_amount , c.check_number check_number, '20' transaction_order , ou.name ORG_NAME FROM ce_bank_accounts ba, ce_bank_branches_v bb, ap_checks_all c, ce_bank_acct_uses_all bau, ce_security_profiles_gt ou, ce_system_parameters sys WHERE bb.branch_party_id = ba.bank_branch_id AND ba.bank_account_id = bau.bank_account_id AND (c.void_date IS NULL AND (c.cleared_date IS NULL OR c.cleared_date > NVL(:P_AS_OF_DATE_T, sysdate)) OR (c.status_lookup_code = 'VOIDED' AND c.void_date > NVL(:P_AS_OF_DATE_T, sysdate) ) ) AND bau.bank_account_id = NVL(:P_BANK_ACCOUNT_ID,bau.bank_account_id) and bau.bank_acct_use_id = c.ce_bank_acct_use_id and bau.AP_USE_ENABLE_FLAG = 'Y' and bau.org_id = c.org_id AND BAU.ORG_ID = OU.ORGANIZATION_ID AND OU.ORGANIZATION_TYPE = 'OPERATING_UNIT' and ba.account_owner_org_id = sys.legal_entity_id AND c.check_date <= NVL(:P_AS_OF_DATE_T,sysdate) AND c.check_date >= sys.CASHBOOK_BEGIN_DATE AND DECODE(c.vendor_id,'',-1,c.vendor_id) = DECODE(c.status_lookup_code,'SET UP', -1, 'SPOILED' , -2 , c.vendor_id) and :P_TYPE IN ('AR_AND_AP', 'PAYMENTS', 'ALL') and c.org_id = nvl(:P_ORG_ID, C.ORG_ID) UNION ALL SELECT 'Receipts' type, HZ.PARTY_name supplier_customer, ba.bank_account_name bank_account_name, ba.bank_account_num bank_account_num, ba.currency_code bank_account_currency, acrh.trx_date payment_date, ps.due_date maturity_date, acr.receipt_number payrcpt_num, arm.name payment_method, acr.currency_code c_currency_code, nvl(acrh.amount,0) amount, DECODE(acr.currency_code,ba.currency_code, NVL(acrh.amount,0), NVL(acrh.acctd_amount,0)) cash_in_transit, DECODE(acr.currency_code,ba.currency_code, NVL(acrh.amount,0), NVL(acrh.acctd_amount,0)) base_amount, 0 check_number, '10' transaction_order, ou.name ORG_NAME FROM ar_receipt_methods arm, ar_payment_schedules_all ps, ce_bank_accounts ba, ce_bank_branches_v bb, HZ_CUST_ACCOUNTS CU, HZ_PARTIES HZ, ar_cash_receipts acr, ar_cash_receipt_history_all acrh, ar_cash_receipt_history_all acrh_prev, ce_bank_acct_uses_all bau, ce_security_profiles_gt ou, ce_system_parameters sys WHERE arm.receipt_method_id = acr.receipt_method_id AND CU.CUST_ACCOUNT_ID(+) = ACR.PAY_FROM_CUSTOMER AND HZ.PARTY_ID(+) = CU.PARTY_ID AND ba.bank_branch_id = bb.branch_party_id AND ba.bank_account_id = bau.bank_account_id and bau.bank_acct_use_id = acr.remit_bank_acct_use_id and bau.org_id = acr.org_id AND BAU.ORG_ID = OU.ORGANIZATION_ID AND OU.ORGANIZATION_TYPE = 'OPERATING_UNIT' and ba.account_owner_org_id = sys.legal_entity_id AND bau.bank_account_id = NVL(:P_BANK_ACCOUNT_ID,bau.bank_account_id ) AND acr.type IN ('CASH','MISC') AND ps.cash_receipt_id (+) = acrh.cash_receipt_id AND acr.cash_receipt_id = acrh.cash_receipt_id AND acrh_prev.cash_receipt_history_id(+) = acrh.prv_stat_cash_receipt_hist_id AND ((acrh.status = 'REMITTED' and acrh.trx_date <= NVL(:P_AS_OF_DATE_T, sysdate)) OR (acrh.status = 'CLEARED' AND acrh.trx_date > NVL(:P_AS_OF_DATE_T, sysdate)) OR ( acrh.status = 'REVERSED' AND acrh.trx_date > NVL(:P_AS_OF_DATE_T, sysdate) AND acrh_prev.status = 'REMITTED' ) ) AND acrh.current_record_flag ='Y' AND acr.receipt_date <= NVL(:P_AS_OF_DATE_T,sysdate) AND acrh.trx_date >= sys.CASHBOOK_BEGIN_DATE and not exists ( select * from ar_payment_schedules_all ps2 where ps2.reversed_cash_receipt_id = acrh.cash_receipt_id) and :P_TYPE IN ('AR_AND_AP', 'RECEIPTS','ALL') AND acr.org_id = NVL(:P_ORG_ID, acr.org_id) UNION ALL SELECT 'ROI_LINE' type, NULL supplier_customer, ba.bank_account_name bank_account_name, ba.bank_account_num bank_account_num, ba.currency_code bank_account_currency, roi.trx_date payment_date, TO_DATE(NULL) maturity_date, roi.trx_number payrcpt_num, trx_type_dsp payment_method, roi.currency_code c_currency_code, NVL(roi.amount,0) amount, DECODE(roi.currency_code, ba.currency_code, DECODE(roi.trx_type, 'PAYMENT', NVL(-roi.amount,0), NVL(roi.amount, 0)), DECODE(roi.trx_type, 'PAYMENT', NVL(-roi.acctd_amount, 0), NVL(roi.acctd_amount,0))) cash_in_transit, DECODE(roi.currency_code,ba.currency_code, NVL(roi.amount,0), NVL(roi.acctd_amount,0)) base_amount, 0 check_number, '30' transaction_order, NULL ORG_NAME FROM CE_999_INTERFACE_V roi, ce_BANK_ACCTS_GT_v ba, ce_BANK_BRANCHES_v bb, CE_SYSTEM_PARAMETERS sys, CE_STATEMENT_RECON_GT_V cre WHERE ba.bank_branch_id = bb.branch_party_id AND ba.bank_account_id = roi.bank_account_id and ba.account_owner_org_id = sys.legal_entity_id AND ba.RECON_ENABLE_OI_FLAG = 'Y' AND roi.status = ba.RECON_OI_FLOAT_STATUS AND roi.bank_account_id = NVL(:P_BANK_ACCOUNT_ID, roi.bank_account_id ) AND roi.trx_date <= NVL(:P_AS_OF_DATE_T, sysdate) AND roi.trx_date >= sys.CASHBOOK_BEGIN_DATE AND CRE.REFERENCE_ID(+) = roi.TRX_ID AND CRE.REFERENCE_TYPE(+) = 'ROI_LINE' AND NVL(CRE.STATUS_FLAG,'U') = 'U' AND NVL(CRE.CURRENT_RECORD_FLAG,'Y') = 'Y' and :P_TYPE IN ('ROI_LINES', 'ALL') UNION ALL SELECT 'Payroll' type, NULL supplier_customer, ba.bank_account_name bank_account_name, ba.bank_account_num bank_account_num, ba.currency_code bank_account_currency, ppa.effective_date payment_date, to_date(NULL) maturity_date, paa.serial_number payrcpt_num, popm.org_payment_method_name payment_method, popm.currency_code c_currency_code, ppp.value amount, DECODE(popm.currency_code,ba.currency_code, NVL(-ppp.value,0), NVL(-ppp.base_currency_value,0)) cash_in_transit, DECODE(popm.currency_code,ba.currency_code, NVL(ppp.value,0), NVL(ppp.base_currency_value,0)) base_amount , 0 check_number, '40' transaction_order, ou.name ORG_NAME FROM ce_BANK_BRANCHES_V bb, ce_BANK_ACCOUNTS ba, GL_SETS_OF_BOOKS sob, CE_SYSTEM_PARAMETERS sys, PAY_CE_RECONCILED_PAYMENTS pcrp, PAY_PRE_PAYMENTS ppp, PAY_ASSIGNMENT_ACTIONS paa, PAY_PAYROLL_ACTIONS ppa, PAY_ORG_PAYMENT_METHODS_F popm, ce_bank_acct_uses_all bau, ce_security_profiles_gt ou WHERE bb.branch_party_id = ba.bank_branch_id AND ba.bank_account_id = bau.bank_account_id AND bau.payroll_bank_account_id = popm.external_account_id and BAU.ORG_ID = OU.ORGANIZATION_ID and OU.ORGANIZATION_TYPE = 'BUSINESS_GROUP' and ba.account_owner_org_id = sys.legal_entity_id AND sys.set_of_books_id = sob.set_of_books_id AND pcrp.assignment_action_id (+) = paa.assignment_action_id AND ppp.org_payment_method_id = popm.org_payment_method_id AND ppa.payroll_action_id = paa.payroll_action_id AND paa.pre_payment_id = ppp.pre_payment_id AND ppa.action_type IN ( 'P', 'H', 'E','M') AND paa.action_status IN ('C', 'V') AND ((pcrp.cleared_date is null OR pcrp.cleared_date > NVL(:P_AS_OF_DATE_T, sysdate) ) OR ( (paa.action_status = 'V') AND ppa.effective_date > NVL(:P_AS_OF_DATE_T, sysdate) ) ) AND ba.bank_account_id = NVL(:P_BANK_ACCOUNT_ID, ba.bank_account_id) AND ppa.date_earned <= NVL(:P_AS_OF_DATE_T, sysdate) AND ppa.date_earned >= sys.CASHBOOK_BEGIN_DATE AND ppa.effective_date between popm.effective_start_date and popm.effective_end_date AND NOT EXISTS ( select * from pay_action_interlocks pai where pai.locked_action_id = paa.assignment_action_id) and :P_TYPE IN ( 'PAYROLLS','ALL') AND bau.org_id = NVL(:P_BG_ORG_ID, bau.org_id ) UNION ALL SELECT 'Payroll' type, NULL supplier_customer, ba.bank_account_name bank_account_name, ba.bank_account_num bank_account_num, ba.currency_code bank_account_currency, ppa.effective_date payment_date, to_date(NULL) maturity_date, paa.serial_number payrcpt_num, popm.org_payment_method_name payment_method, popm.currency_code c_currency_code, ppp.value amount, DECODE(popm.currency_code,ba.currency_code, NVL(-ppp.value,0), NVL(-ppp.base_currency_value,0)) cash_in_transit, DECODE(popm.currency_code,ba.currency_code, NVL(ppp.value,0), NVL(ppp.base_currency_value,0)) base_amount , 0 check_number, '40' transaction_order, ou.name ORG_NAME FROM ce_BANK_BRANCHES_V bb, ce_BANK_ACCOUNTS ba, GL_SETS_OF_BOOKS sob, CE_SYSTEM_PARAMETERS sys, PAY_CE_RECONCILED_PAYMENTS pcrp, PAY_PRE_PAYMENTS ppp, PAY_ASSIGNMENT_ACTIONS paa, PAY_PAYROLL_ACTIONS ppa, PAY_ORG_PAYMENT_METHODS_F popm, PAY_ACTION_INTERLOCKS pai, ce_bank_acct_uses_all bau, ce_security_profiles_gt ou WHERE bb.branch_party_id = ba.bank_branch_id AND ba.bank_account_id = bau.bank_account_id AND bau.payroll_bank_account_id = popm.external_account_id and BAU.ORG_ID = OU.ORGANIZATION_ID and OU.ORGANIZATION_TYPE = 'BUSINESS_GROUP' and ba.account_owner_org_id = sys.legal_entity_id AND sys.set_of_books_id = sob.set_of_books_id AND pcrp.assignment_action_id (+) = paa.assignment_action_id AND ppp.org_payment_method_id = popm.org_payment_method_id AND ppa.payroll_action_id = paa.payroll_action_id AND paa.pre_payment_id = ppp.pre_payment_id AND ppa.action_type = 'H' AND paa.action_status = 'C' AND ba.bank_account_id = NVL(:P_BANK_ACCOUNT_ID, ba.bank_account_id) AND ppa.date_earned <= NVL(:P_AS_OF_DATE_T, sysdate) AND ppa.date_earned >= sys.CASHBOOK_BEGIN_DATE AND pai.locked_action_id = paa.assignment_action_id AND ppa.effective_date between popm.effective_start_date and popm.effective_end_date AND EXISTS ( select * from pay_assignment_actions paa2, pay_payroll_actions ppa2 where paa2.assignment_action_id = pai.locking_action_id and paa2.payroll_action_id = ppa2.payroll_action_id and ppa2.effective_date > NVL(:P_AS_OF_DATE_T, sysdate)) and :P_TYPE IN ( 'PAYROLLS','ALL') AND bau.org_id = NVL(:P_BG_ORG_ID, bau.org_id ) ORDER BY &C_SORT_BY_LEX |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Bank Account Number | LOV Oracle | ||
Transaction Type | LOV Oracle | ||
Payroll Business Group | LOV Oracle | ||
AP/AR Operating Unit | LOV Oracle | ||
As Of Date | Date | ||
Sort By | LOV Oracle | ||
CE_BG_DUMMY | |||
CE_OU_DUMMY |