CE Cleared Transactions - draft

Description
Categories: BI Publisher, Financials
Application: Cash Management
Source: Cleared Transactions Report (XML) - Not Supported: Reserved For Future Use
Short Name: CEXCLEAR_XML
DB package: CE_CEXCLEAR_XMLP_PKG
SELECT aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
BankParty.PARTY_NAME bank_name,
BranchParty.PARTY_NAME bank_branch_name,
/*abb.bank_name bank_name,
abb.bank_branch_name bank_branch_name,*/
'PAYMENT' type,
'PAYMENT' break_type,
'Payment' type_dsp,
c.vendor_name supplier_customer,
c.check_date trx_date,
c.future_pay_due_date maturity_date,
c.amount bank_account_amount,
to_char(c.check_number) trx_number,
lk.payment_method_name payment_method,
c.currency_code currency_code,
c.amount amount,
DECODE(c.currency_code,aba.currency_code,
c.amount,NVL(c.base_amount,c.amount)) account_amount,
30 transaction_order,
to_number(NULL) batch_id,
NULL batch_number,
NULL remittance_number,
c.cleared_date cleared_date,
DECODE(c.currency_code,aba.currency_code,
c.cleared_amount,
nvl(c.cleared_base_amount,c.cleared_amount)) cleared_amount,
--fnd_access_control_util.get_org_name(bau.org_id) ORG_NAME
ou.name ORG_NAME,
/*CE_CEXCLEAR_XMLP_PKG.c_grand_cleared_amountformula('PAYMENT',:C_SUM_AMOUNT,:C_SUM_BATCH_AMOUNT) C_GRAND_CLEARED_AMOUNT,*/ 
CE_CEXCLEAR_XMLP_PKG.c_amountformula(c.amount,'PAYMENT',NULL,to_number (NULL )) C_AMOUNT,
/*CE_CEXCLEAR_XMLP_PKG.c_bank_account_amountformula(DECODE (c.currency_code ,aba.currency_code ,c.amount ,NVL (c.base_amount ,c.amount ) )) C_BANK_ACCOUNT_AMOUNT,*/
CE_CEXCLEAR_XMLP_PKG.C_BATCH_AMOUNT_p C_BATCH_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_BANK_ACCOUNT_AMOUNT_p C_BATCH_BANK_ACCOUNT_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_CLEARED_AMOUNT_p C_BATCH_CLEARED_AMOUNT
FROM AP_CHECKS_ALL c,
IBY_PAYMENT_METHODS_VL lk,
CE_BANK_ACCOUNTS aba,
HZ_PARTIES BankParty,
HZ_PARTIES BranchParty,
CE_BANK_ACCT_USES_ALL BAU,
CE_SECURITY_PROFILES_GT OU
/*CE_BANK_BRANCHES_V abb,
ce_bank_acct_uses_ou_v bau*/
WHERE c.cleared_date is not null and
LK.payment_method_code=c.payment_method_code and
--aba.bank_branch_id=abb.branch_party_id and
--abb.branch_party_id=:P_BANK_BRANCH and
ABA.BANK_BRANCH_ID=BranchParty.PARTY_ID AND 
ABA.BANK_ID=BankParty.PARTY_ID AND 
ABA.BANK_BRANCH_ID=:P_BANK_BRANCH and
aba.bank_account_id=bau.bank_account_id and
--c.bank_account_id=NVL(:P_BANK_ACCOUNT,c.bank_account_id) and
bau.bank_account_id=nvl(:P_BANK_ACCOUNT,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=OU.ORGANIZATION_ID AND 
OU.ORGANIZATION_TYPE='OPERATING_UNIT' and
bau.org_id=c.org_id and
c.cleared_date >= NVL(:P_DATE_FROM,c.cleared_date) and
c.cleared_date<= NVL(:P_DATE_TO,c.cleared_date) and
:P_BATCH_OR_TRX='T' and
--:P_TYPE IN ('ALL','PAYMENTS') 
:P_TYPE IN ('AR_AND_AP','PAYMENTS','ALL') and
bau.org_id=NVL(:P_ORG_ID,bau.org_id )
UNION ALL
SELECT aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
BankParty.PARTY_NAME bank_name,
BranchParty.PARTY_NAME bank_branch_name,
'CASH' type,
cr.type break_type,
DECODE(cr.type,'CASH',L1.meaning,
'MISC',:C_RECEIPT_TRANSLATION) type_dsp,
hz.party_name supplier_customer,
cr.receipt_date trx_date,
aps.due_date maturity_date,
cr.amount bank_account_amount,
cr.receipt_number trx_number,
arm.name payment_method,
cr.currency_code currency_code,
cr.amount amount,
DECODE(cr.currency_code,aba.currency_code,crh.amount,
nvl(crh.acctd_amount,crh.amount)) account_amount,
10 transaction_order,
to_number(NULL) batch_id,
NULL batch_number,
NULL remittance_number,
crh.trx_date cleared_date,
DECODE(cr.currency_code,aba.currency_code,crh.amount,
nvl(crh.acctd_amount,crh.amount)) cleared_amount,
ou.name ORG_NAME,
/*CE_CEXCLEAR_XMLP_PKG.c_grand_cleared_amountformula('PAYMENT',:C_SUM_AMOUNT,:C_SUM_BATCH_AMOUNT) C_GRAND_CLEARED_AMOUNT,*/
CE_CEXCLEAR_XMLP_PKG.c_amountformula(cr.amount,'CASH',NULL,to_number(NULL)) C_AMOUNT,
/*CE_CEXCLEAR_XMLP_PKG.c_bank_account_amountformula(DECODE (c.currency_code ,aba.currency_code ,c.amount ,NVL (c.base_amount ,cr.amount ) )) C_BANK_ACCOUNT_AMOUNT,*/
CE_CEXCLEAR_XMLP_PKG.C_BATCH_AMOUNT_p C_BATCH_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_BANK_ACCOUNT_AMOUNT_p C_BATCH_BANK_ACCOUNT_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_CLEARED_AMOUNT_p C_BATCH_CLEARED_AMOUNT
FROM AR_CASH_RECEIPTS cr,
AR_CASH_RECEIPT_HISTORY_ALL crh,
CE_BANK_ACCOUNTS aba,
--CE_BANK_BRANCHES_V abb,
CE_LOOKUPS L1,
--RA_CUSTOMERS rc,
HZ_CUST_ACCOUNTS CU,
HZ_PARTIES HZ,
AR_PAYMENT_SCHEDULES_ALL aps,
AR_RECEIPT_METHODS arm,
HZ_PARTIES BankParty,
HZ_PARTIES BranchParty,
CE_BANK_ACCT_USES_ALL BAU,
CE_SECURITY_PROFILES_GT OU
WHERE L1.lookup_type='TRX_TYPE' and
L1.lookup_code=cr.type and
cr.cash_receipt_id=crh.cash_receipt_id and
CU.CUST_ACCOUNT_ID(+)=CR.PAY_FROM_CUSTOMER and
HZ.PARTY_ID(+)=CU.PARTY_ID and
--rc.customer_id(+)=cr.pay_from_customer and
--aba.bank_account_id=cr.remittance_bank_account_id and
--aba.bank_branch_id=abb.branch_party_id and
aps.cash_receipt_id(+)=cr.cash_receipt_id and
arm.receipt_method_id=cr.receipt_method_id and
ABA.BANK_BRANCH_ID=BranchParty.PARTY_ID AND 
ABA.BANK_ID=BankParty.PARTY_ID AND 
ABA.BANK_BRANCH_ID=:P_BANK_BRANCH and
aba.bank_account_id=bau.bank_account_id and
bau.AR_USE_ENABLE_FLAG='Y' and
BAU.ORG_ID=OU.ORGANIZATION_ID AND 
OU.ORGANIZATION_TYPE='OPERATING_UNIT' and
aba.bank_account_id=NVL(:P_BANK_ACCOUNT,aba.bank_account_id) and
bau.bank_acct_use_id=cr.remit_bank_acct_use_id and
bau.org_id=cr.org_id and
crh.status in ('CLEARED','RISK_ELIMINATED') and
crh.current_record_flag='Y' and
crh.trx_date >= NVL(:P_DATE_FROM,crh.trx_date) and
crh.trx_date <= NVL(:P_DATE_TO,crh.trx_date) and
:P_BATCH_OR_TRX='T' and
--:P_TYPE IN ('ALL','RECEIPTS') 
:P_TYPE IN ('AR_AND_AP','RECEIPTS','ALL') and
bau.org_id=NVL(:P_ORG_ID,bau.org_id )
UNION ALL -- payment batch
SELECT ba.bank_account_name bank_account_name,
ba.bank_account_num bank_account_num,
BankParty.PARTY_NAME bank_name,
BranchParty.PARTY_NAME bank_branch_name,
'PAYMENT' type,
'PAYMENT' break_type,
L1.meaning type_dsp,
NULL supplier_customer,
apbt.check_date trx_date,
to_date(NULL) maturity_date,
to_number(NULL) bank_account_amount,
NVL(apbt.batch_identifier,apbt.checkrun_name) trx_number,
NULL payment_method,
apbt.currency_code currency_code,
to_number(NULL) amount,
to_number(NULL) account_amount,
20 transaction_order,
apbt.checkrun_id batch_id,
apbt.checkrun_name batch_number,
apbt.batch_identifier remittance_number,
to_date(NULL) cleared_date,
to_number(NULL) cleared_amount,
ou.name ORG_NAME,
/*CE_CEXCLEAR_XMLP_PKG.c_grand_cleared_amountformula('PAYMENT',:C_SUM_AMOUNT,:C_SUM_BATCH_AMOUNT) C_GRAND_CLEARED_AMOUNT,*/ 
CE_CEXCLEAR_XMLP_PKG.c_amountformula(to_number(NULL),'PAYMENT',apbt.checkrun_name,apbt.checkrun_id) C_AMOUNT,
/*CE_CEXCLEAR_XMLP_PKG.c_bank_account_amountformula(DECODE (c.currency_code ,aba.currency_code ,c.amount ,NVL (c.base_amount ,to_number(NULL) ) )) C_BANK_ACCOUNT_AMOUNT,*/
CE_CEXCLEAR_XMLP_PKG.C_BATCH_AMOUNT_p C_BATCH_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_BANK_ACCOUNT_AMOUNT_p C_BATCH_BANK_ACCOUNT_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_CLEARED_AMOUNT_p C_BATCH_CLEARED_AMOUNT
FROM AP_INV_SELECTION_CRITERIA_ALL apbt,
CE_BANK_ACCOUNTS ba,
CE_LOOKUPS L1,
HZ_PARTIES BankParty,
HZ_PARTIES BranchParty,
CE_BANK_ACCT_USES_ALL BAU,
CE_SECURITY_PROFILES_GT OU
WHERE apbt.bank_account_id=ba.bank_account_id AND
apbt.status='CONFIRMED' AND
BA.BANK_BRANCH_ID=BranchParty.PARTY_ID AND 
BA.BANK_ID=BankParty.PARTY_ID AND 
BA.BANK_BRANCH_ID=:P_BANK_BRANCH and
ba.bank_account_id=bau.bank_account_id and
--ba.bank_account_id=NVL(:P_BANK_ACCOUNT,ba.bank_account_id) AND
bau.bank_account_id=nvl(:P_BANK_ACCOUNT,bau.bank_account_id) and
bau.AP_USE_ENABLE_FLAG='Y' and
BAU.ORG_ID=OU.ORGANIZATION_ID AND 
OU.ORGANIZATION_TYPE='OPERATING_UNIT' and
bau.bank_acct_use_id=apbt.ce_bank_acct_use_id and
bau.org_id=apbt.org_id and
L1.lookup_type='BATCH_TYPE' AND
L1.lookup_code='PAYMENT' AND
--:P_TYPE IN ('ALL','PAYMENTS') AND
:P_TYPE IN ('AR_AND_AP','PAYMENTS','ALL') AND
:P_BATCH_OR_TRX='B' AND
bau.org_id=NVL(:P_ORG_ID,bau.org_id ) and
EXISTS (select null
from AP_CHECKS_all c
WHERE c.checkrun_name=apbt.checkrun_name AND
c.org_id=bau.org_id and
c.cleared_date >= NVL(:P_DATE_FROM,c.cleared_date) and
c.cleared_date <= NVL(:P_DATE_TO,c.cleared_date) and
c.cleared_date is not null)
UNION ALL -- iPayment batch
SELECT ba.bank_account_name bank_account_name,
ba.bank_account_num bank_account_num,
BankParty.PARTY_NAME bank_name,
BranchParty.PARTY_NAME bank_branch_name,
'PAYMENT' type,
'PAYMENT' break_type,
L1.meaning type_dsp,
NULL supplier_customer,
apbt.payment_date trx_date,
to_date(NULL) maturity_date,
to_number(NULL) bank_account_amount,
to_char(apbt.PAYMENT_INSTRUCTION_ID) trx_number,
NULL payment_method,
apbt.payment_currency_code currency_code,
to_number(NULL) amount,
to_number(NULL) account_amount,
20 transaction_order,
apbt.PAYMENT_INSTRUCTION_ID batch_id,
to_char(apbt.PAYMENT_INSTRUCTION_ID ) batch_number,
to_char(apbt.PAYMENT_INSTRUCTION_ID ) remittance_number,
to_date(NULL) cleared_date,
to_number(NULL) cleared_amount,
fnd_access_control_util.get_org_name(bau.org_id) ORG_NAME,
/*CE_CEXCLEAR_XMLP_PKG.c_grand_cleared_amountformula('PAYMENT',:C_SUM_AMOUNT,:C_SUM_BATCH_AMOUNT) C_GRAND_CLEARED_AMOUNT,*/ 
CE_CEXCLEAR_XMLP_PKG.c_amountformula(to_number(NULL),'PAYMENT',to_char(apbt.PAYMENT_INSTRUCTION_ID ),apbt.PAYMENT_INSTRUCTION_ID ) C_AMOUNT,
/*CE_CEXCLEAR_XMLP_PKG.c_bank_account_amountformula(DECODE (c.currency_code ,aba.currency_code ,c.amount ,NVL (c.base_amount ,c.amount ) )) C_BANK_ACCOUNT_AMOUNT,*/
CE_CEXCLEAR_XMLP_PKG.C_BATCH_AMOUNT_p C_BATCH_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_BANK_ACCOUNT_AMOUNT_p C_BATCH_BANK_ACCOUNT_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_CLEARED_AMOUNT_p C_BATCH_CLEARED_AMOUNT
FROM iby_pay_instructions_all apbt,
CE_BANK_ACCTS_GT_V ba,
CE_LOOKUPS L1,
--ce_bank_acct_uses bau
HZ_PARTIES BankParty,
HZ_PARTIES BranchParty,
CE_BANK_ACCT_USES_ALL BAU
WHERE apbt.internal_bank_account_id=ba.bank_account_id AND
--apbt.status='CONFIRMED' AND
--ba.bank_branch_id=bb.branch_PARTY_id AND
BA.BANK_BRANCH_ID=BranchParty.PARTY_ID AND 
BA.BANK_ID=BankParty.PARTY_ID AND 
BA.BANK_BRANCH_ID=:P_BANK_BRANCH and
ba.bank_account_id=bau.bank_account_id and
--ba.bank_account_id=NVL(:P_BANK_ACCOUNT,ba.bank_account_id) AND
bau.bank_account_id=nvl(:P_BANK_ACCOUNT,bau.bank_account_id) and
ba.AP_USE_ALLOWED_FLAG='Y' and
--bau.bank_acct_use_id=apbt.ce_bank_acct_use_id and
L1.lookup_type='BATCH_TYPE' AND
L1.lookup_code='PAYMENT' AND
--:P_TYPE IN ('ALL','PAYMENTS') AND
:P_TYPE IN ('AR_AND_AP','PAYMENTS','ALL') AND
:P_BATCH_OR_TRX='B' AND
bau.org_id=NVL(:P_ORG_ID,bau.org_id ) and
bau.org_id=apbt.org_id and
EXISTS (select null
from AP_CHECKS_all c,IBY_FD_PAYMENTS_V pay
WHERE pay.PAYMENT_INSTRUCTION_ID=apbt.PAYMENT_INSTRUCTION_ID
and c.payment_id=pay.payment_id 
and c.org_id=bau.org_id
and c.cleared_date is not null
)
UNION ALL -- receipt batch
SELECT ba.bank_account_name bank_account_name,
ba.bank_account_num bank_account_num,
BankParty.PARTY_NAME bank_name,
BranchParty.PARTY_NAME bank_branch_name,
'RECEIPT' type,
'RECEIPT' break_type,
L1.meaning type_dsp,
NULL supplier_customer,
arbt.batch_date trx_date,
to_date(NULL) maturity_date,
to_number(NULL) bank_account_amount,
NVL(arbt.bank_deposit_number,arbt.name) trx_number,
NULL payment_method,
arbt.currency_code currency_code,
to_number(NULL) amount,
to_number(NULL) account_amount,
10 transaction_order,
arbt.batch_id batch_id,
arbt.name batch_number,
arbt.bank_deposit_number remittance_number,
to_date(NULL) cleared_date,
to_number(NULL) cleared_amount,
fnd_access_control_util.get_org_name(bau.org_id) ORG_NAME,
/*CE_CEXCLEAR_XMLP_PKG.c_grand_cleared_amountformula('PAYMENT',:C_SUM_AMOUNT,:C_SUM_BATCH_AMOUNT) C_GRAND_CLEARED_AMOUNT,*/ 
CE_CEXCLEAR_XMLP_PKG.c_amountformula(to_number(NULL),'RECEIPT',arbt.name,arbt.batch_id) C_AMOUNT,
/*CE_CEXCLEAR_XMLP_PKG.c_bank_account_amountformula(DECODE (c.currency_code ,aba.currency_code ,c.amount ,NVL (c.base_amount ,c.amount ) )) C_BANK_ACCOUNT_AMOUNT,*/
CE_CEXCLEAR_XMLP_PKG.C_BATCH_AMOUNT_p C_BATCH_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_BANK_ACCOUNT_AMOUNT_p C_BATCH_BANK_ACCOUNT_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_CLEARED_AMOUNT_p C_BATCH_CLEARED_AMOUNT
FROM AR_BATCHES_all arbt,
--CE_BANK_BRANCHES_V bb,
CE_BANK_ACCOUNTS ba,
CE_LOOKUPS L1,
--ce_bank_acct_uses_ou_v bau
HZ_PARTIES BankParty,
HZ_PARTIES BranchParty,
CE_BANK_ACCT_USES_ALL BAU,
CE_SECURITY_PROFILES_GT OU
WHERE --arbt.remittance_bank_account_id=ba.bank_account_id AND
--ba.BANK_branch_id=bb.branch_PARTY_id AND
BA.BANK_BRANCH_ID=BranchParty.PARTY_ID AND 
BA.BANK_ID=BankParty.PARTY_ID AND 
BA.BANK_BRANCH_ID=:P_BANK_BRANCH and
--bb.branch_party_id=:P_BANK_BRANCH AND
ba.bank_account_id=NVL(:P_BANK_ACCOUNT,ba.bank_account_id) AND
ba.bank_account_id=bau.bank_account_id and
bau.AR_USE_ENABLE_FLAG='Y' and
BAU.ORG_ID=OU.ORGANIZATION_ID AND 
OU.ORGANIZATION_TYPE='OPERATING_UNIT' and
bau.bank_acct_use_id=arbt.remit_bank_acct_use_id and
bau.org_id=arbt.org_id and
L1.lookup_type='BATCH_TYPE' AND
L1.lookup_code='RECEIPT' AND
--:P_TYPE IN ('ALL','RECEIPTS') AND
:P_TYPE IN ('AR_AND_AP','RECEIPTS','ALL') AND 
:P_BATCH_OR_TRX='B' AND
bau.org_id=NVL(:P_ORG_ID,bau.org_id ) and
EXISTS (SELECT null 
FROM ar_cash_receipt_history_all ar,
ar_cash_receipt_history_all arh
WHERE ar.cash_receipt_id=arh.cash_receipt_id AND
arh.batch_id=arbt.batch_id AND
arh.org_id=bau.org_id and
ar.status='CLEARED' AND
ar.trx_date >= nvl(:P_DATE_FROM,ar.trx_date) AND
ar.trx_date <= nvl(:P_DATE_TO,ar.trx_date) AND
ar.current_record_flag='Y'
)
UNION ALL -- Payroll Payments
SELECT ba.bank_account_name bank_account_name,
ba.bank_account_num bank_account_num,
BankParty.PARTY_NAME bank_name,
BranchParty.PARTY_NAME bank_branch_name,
'PAYROLL' type,
'PAYROLL' break_type,
'Payroll' type_dsp,
c.vendor_name supplier_customer,
c.trx_date trx_date,
c.maturity_date maturity_date,
c.amount bank_account_amount,
c.trx_number trx_number,
popm.org_payment_method_name payment_method,
c.currency_code currency_code,
c.amount amount,
c.bank_account_amount account_amount,
40 transaction_order,
to_number(NULL) batch_id,
NULL batch_number,
NULL remittance_number,
c.cleared_date cleared_date,
c.amount_cleared cleared_amount,
fnd_access_control_util.get_org_name(c.org_id) ORG_NAME,
/*CE_CEXCLEAR_XMLP_PKG.c_grand_cleared_amountformula('PAYMENT',:C_SUM_AMOUNT,:C_SUM_BATCH_AMOUNT) C_GRAND_CLEARED_AMOUNT,*/ 
CE_CEXCLEAR_XMLP_PKG.c_amountformula(c.amount,'PAYROLL',NULL,to_number(NULL)) C_AMOUNT,
/*CE_CEXCLEAR_XMLP_PKG.c_bank_account_amountformula(DECODE (c.currency_code ,aba.currency_code ,c.amount ,NVL (c.base_amount ,c.amount ) )) C_BANK_ACCOUNT_AMOUNT,*/
CE_CEXCLEAR_XMLP_PKG.C_BATCH_AMOUNT_p C_BATCH_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_BANK_ACCOUNT_AMOUNT_p C_BATCH_BANK_ACCOUNT_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_CLEARED_AMOUNT_p C_BATCH_CLEARED_AMOUNT
FROM CE_801_RECONCILED_V c,
--CE_BANK_BRANCHES_V bb,
HZ_PARTIES BankParty,
HZ_PARTIES BranchParty,
CE_BANK_ACCOUNTS ba,
PAY_PRE_PAYMENTS ppp,
PAY_ASSIGNMENT_ACTIONS paa,
PAY_ORG_PAYMENT_METHODS_F popm
WHERE ppp.org_payment_method_id=popm.org_payment_method_id and
paa.assignment_action_id=c.trx_id and
paa.pre_payment_id=ppp.pre_payment_id and
--ba.bank_branch_id=bb.Branch_PARTY_id and
ba.bank_account_id=c.bank_account_id and
BA.BANK_BRANCH_ID=BranchParty.PARTY_ID AND 
BA.BANK_ID=BankParty.PARTY_ID AND 
BA.BANK_BRANCH_ID=:P_BANK_BRANCH and
c.bank_account_id=NVL(:P_BANK_ACCOUNT,c.bank_account_id) and
c.cleared_date >= NVL(:P_DATE_FROM,c.cleared_date) and
c.cleared_date <= NVL(:P_DATE_TO,c.cleared_date) and
c.trx_date between popm.effective_start_date and popm.effective_end_date and
:P_BATCH_OR_TRX='T' and
:P_TYPE in ('PAYROLLS','ALL') and
c.org_id=NVL(:P_BG_ORG_ID,c.org_id)
UNION ALL -- eft payroll
SELECT ba.bank_account_name bank_account_name,
ba.bank_account_num bank_account_num,
BankParty.PARTY_NAME bank_name,
BranchParty.PARTY_NAME bank_branch_name,
'PAYROLL' type,
'PAYROLL' break_type,
'Payroll' type_dsp,
c.vendor_name supplier_customer,
c.trx_date trx_date,
c.maturity_date maturity_date,
c.amount bank_account_amount,
c.trx_number trx_number,
popm.org_payment_method_name payment_method,
c.currency_code currency_code,
c.amount amount,
c.bank_account_amount account_amount,
40 transaction_order,
to_number(NULL) batch_id,
c.batch_name batch_number,
NULL remittance_number,
c.cleared_date cleared_date,
c.amount_cleared cleared_amount,
fnd_access_control_util.get_org_name(c.org_id) ORG_NAME,
/*CE_CEXCLEAR_XMLP_PKG.c_grand_cleared_amountformula('PAYMENT',:C_SUM_AMOUNT,:C_SUM_BATCH_AMOUNT) C_GRAND_CLEARED_AMOUNT,*/ 
CE_CEXCLEAR_XMLP_PKG.c_amountformula(c.amount,'PAYROLL',c.batch_name,to_number (NULL )) C_AMOUNT,
/*CE_CEXCLEAR_XMLP_PKG.c_bank_account_amountformula(DECODE (c.currency_code ,aba.currency_code ,c.amount ,NVL (c.base_amount ,c.amount ) )) C_BANK_ACCOUNT_AMOUNT,*/
CE_CEXCLEAR_XMLP_PKG.C_BATCH_AMOUNT_p C_BATCH_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_BANK_ACCOUNT_AMOUNT_p C_BATCH_BANK_ACCOUNT_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_CLEARED_AMOUNT_p C_BATCH_CLEARED_AMOUNT
FROM CE_801_EFT_RECONCILED_V c,
--AP_BANK_BRANCHES bb,
CE_BANK_ACCOUNTS ba,
HZ_PARTIES BankParty,
HZ_PARTIES BranchParty,
PAY_PRE_PAYMENTS ppp,
PAY_ASSIGNMENT_ACTIONS paa,
PAY_ORG_PAYMENT_METHODS_F popm
WHERE ppp.org_payment_method_id=popm.org_payment_method_id and
paa.assignment_action_id=c.trx_id and
paa.pre_payment_id=ppp.pre_payment_id and
--ba.bank_branch_id=bb.bank_branch_id and
ba.bank_account_id=c.bank_account_id and
BA.BANK_BRANCH_ID=BranchParty.PARTY_ID AND 
BA.BANK_ID=BankParty.PARTY_ID AND 
BA.BANK_BRANCH_ID=:P_BANK_BRANCH and
c.bank_account_id=NVL(:P_BANK_ACCOUNT,c.bank_account_id) and
c.cleared_date >= NVL(:P_DATE_FROM,c.cleared_date) and
c.cleared_date <= NVL(:P_DATE_TO,c.cleared_date) and
c.trx_date between popm.effective_start_date and popm.effective_end_date and
:P_BATCH_OR_TRX='T' and
:P_TYPE in ('PAYROLLS','ALL') and
c.org_id=NVL(:P_BG_ORG_ID,c.org_id)
UNION ALL -- roi_line
SELECT aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
abb.bank_name bank_name,
abb.bank_branch_name bank_branch_name,
'ROI_LINE' type,
'ROI_LINE' break_type,
'ROI_LINE' type_dsp,
NULL supplier_customer,
c.trx_date trx_date,
TO_DATE(NULL) maturity_date,
c.amount bank_account_amount,
c.trx_number trx_number,
NULL payment_method,
c.currency_code currency_code,
c.amount amount,
DECODE(c.currency_code,aba.currency_code,
c.amount,NVL(c.acctd_amount,c.amount)) account_amount,
--c.amount account_amount,
50 transaction_order,
to_number(NULL) batch_id,
NULL batch_number,
NULL remittance_number,
c.cleared_date cleared_date,
DECODE(c.currency_code,aba.currency_code,
c.cleared_amount,
nvl(c.acctd_cleared_amount,c.cleared_amount)) cleared_amount,
-- c.cleared_amount cleared_amount,
NULL ORG_NAME,
/*CE_CEXCLEAR_XMLP_PKG.c_grand_cleared_amountformula('PAYMENT',:C_SUM_AMOUNT,:C_SUM_BATCH_AMOUNT) C_GRAND_CLEARED_AMOUNT,*/ 
CE_CEXCLEAR_XMLP_PKG.c_amountformula(c.amount,'ROI_LINE',NULL,to_number (NULL )) C_AMOUNT,
/*CE_CEXCLEAR_XMLP_PKG.c_bank_account_amountformula(DECODE (c.currency_code ,aba.currency_code ,c.amount ,NVL (c.base_amount ,c.amount ) )) C_BANK_ACCOUNT_AMOUNT,*/
CE_CEXCLEAR_XMLP_PKG.C_BATCH_AMOUNT_p C_BATCH_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_BANK_ACCOUNT_AMOUNT_p C_BATCH_BANK_ACCOUNT_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_CLEARED_AMOUNT_p C_BATCH_CLEARED_AMOUNT
FROM CE_999_INTERFACE_V c,
CE_BANK_ACCTS_GT_V aba,
CE_BANK_BRANCHES_V abb
WHERE aba.bank_branch_id=abb.branch_party_id
and abb.branch_party_id=:P_BANK_BRANCH
and aba.bank_account_id=c.bank_account_id
and c.bank_account_id=NVL(:P_BANK_ACCOUNT,c.bank_account_id)
--and c.status=NVL(sys.open_interface_clear_status,'#')
and c.status=NVL(aba.RECON_OI_CLEARED_STATUS,'#')
and c.cleared_date >= NVL(:P_DATE_FROM,c.cleared_date)
and c.cleared_date <= NVL(:P_DATE_TO,c.cleared_date)
and :P_BATCH_OR_TRX='T'
and :P_TYPE in ('ROI_LINES','ALL')
and c.trx_type='CASH'
--and aba.ACCOUNT_OWNER_ORG_ID=sys.legal_entity_id
UNION ALL
SELECT aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
abb.bank_name bank_name,
abb.bank_branch_name bank_branch_name,
'ROI_LINE' type,
'ROI_LINE' break_type,
'ROI_LINE' type_dsp,
NULL supplier_customer,
c.trx_date trx_date,
TO_DATE(NULL) maturity_date,
c.amount bank_account_amount,
c.trx_number trx_number,
NULL payment_method,
c.currency_code currency_code,
c.amount amount,
DECODE(c.currency_code,aba.currency_code,
c.amount,NVL(c.acctd_amount,c.amount)) account_amount,
--c.acctd_amount account_amount,
60 transaction_order,
to_number(NULL) batch_id,
NULL batch_number,
NULL remittance_number,
c.cleared_date cleared_date,
DECODE(c.currency_code,aba.currency_code,
c.cleared_amount,
nvl(c.acctd_cleared_amount,c.cleared_amount)) cleared_amount,
NULL ORG_NAME,
/*CE_CEXCLEAR_XMLP_PKG.c_grand_cleared_amountformula('PAYMENT',:C_SUM_AMOUNT,:C_SUM_BATCH_AMOUNT) C_GRAND_CLEARED_AMOUNT,*/ 
CE_CEXCLEAR_XMLP_PKG.c_amountformula(c.amount,'ROI_LINE',NULL,to_number (NULL )) C_AMOUNT,
/*CE_CEXCLEAR_XMLP_PKG.c_bank_account_amountformula(DECODE (c.currency_code ,aba.currency_code ,c.amount ,NVL (c.base_amount ,c.amount ) )) C_BANK_ACCOUNT_AMOUNT,*/
CE_CEXCLEAR_XMLP_PKG.C_BATCH_AMOUNT_p C_BATCH_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_BANK_ACCOUNT_AMOUNT_p C_BATCH_BANK_ACCOUNT_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_CLEARED_AMOUNT_p C_BATCH_CLEARED_AMOUNT
--c.cleared_amount cleared_amount
FROM CE_999_INTERFACE_V c,
ce_BANK_ACCTS_GT_v aba,
ce_BANK_BRANCHES_v abb
WHERE aba.bank_branch_id=abb.branch_party_id
and abb.branch_party_id=:P_BANK_BRANCH
and aba.bank_account_id=c.bank_account_id
and c.bank_account_id=NVL(:P_BANK_ACCOUNT,c.bank_account_id)
--and c.status=NVL(sys.open_interface_clear_status,'#')
and c.status=NVL(aba.RECON_OI_CLEARED_STATUS,'#')
and c.cleared_date >= NVL(:P_DATE_FROM,c.cleared_date)
and c.cleared_date <= NVL(:P_DATE_TO,c.cleared_date)
and :P_BATCH_OR_TRX='T'
and :P_TYPE in ('ROI_LINES','ALL')
and c.trx_type='PAYMENT'
--and aba.ACCOUNT_OWNER_ORG_ID=sys.legal_entity_id
union all
SELECT aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
abb.bank_name bank_name,
abb.bank_branch_name bank_branch_name,
'ROI_LINE' type,
'ROI_LINE' break_type,
'ROI_LINE' type_dsp,
NULL supplier_customer,
c.trx_date trx_date,
TO_DATE(NULL) maturity_date,
c.amount bank_account_amount,
c.trx_number trx_number,
NULL payment_method,
c.currency_code currency_code,
c.amount amount,
c.bank_account_amount account_amount,
50 transaction_order,
to_number(NULL) batch_id,
NULL batch_number,
NULL remittance_number,
c.cleared_date cleared_date,
c.amount_cleared cleared_amount,
fnd_access_control_util.get_org_name(c.legal_entity_id) ORG_NAME,
/*CE_CEXCLEAR_XMLP_PKG.c_grand_cleared_amountformula('PAYMENT',:C_SUM_AMOUNT,:C_SUM_BATCH_AMOUNT) C_GRAND_CLEARED_AMOUNT,*/ 
CE_CEXCLEAR_XMLP_PKG.c_amountformula(c.amount,'ROI_LINE',NULL,to_number (NULL )) C_AMOUNT,
/*CE_CEXCLEAR_XMLP_PKG.c_bank_account_amountformula(DECODE (c.currency_code ,aba.currency_code ,c.amount ,NVL (c.base_amount ,c.amount ) )) C_BANK_ACCOUNT_AMOUNT,*/
CE_CEXCLEAR_XMLP_PKG.C_BATCH_AMOUNT_p C_BATCH_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_BANK_ACCOUNT_AMOUNT_p C_BATCH_BANK_ACCOUNT_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_CLEARED_AMOUNT_p C_BATCH_CLEARED_AMOUNT
FROM CE_185_reconciled_V c,
--ce_BANK_ACCTS_GT_v aba,
ce_BANK_ACCOUNTS aba,
ce_BANK_BRANCHES_v abb
WHERE aba.bank_branch_id=abb.branch_party_id
and abb.branch_party_id=:P_BANK_BRANCH
and aba.bank_account_id=c.bank_account_id
and c.bank_account_id=NVL(:P_BANK_ACCOUNT,c.bank_account_id)
and c.cleared_date >= NVL(:P_DATE_FROM,c.cleared_date)
and c.cleared_date <= NVL(:P_DATE_TO,c.cleared_date)
and :P_BATCH_OR_TRX='T'
and :P_TYPE in ('XTR_LINES','ALL')
and c.trx_type='CASH'
and c.LEGAL_ENTITY_ID=nvl(:P_LEGAL_ENTITY_ID,c.LEGAL_ENTITY_ID)
--and c.LEGAL_ENTITY_ID=sys.LEGAL_ENTITY_ID
union all
SELECT aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
abb.bank_name bank_name,
abb.bank_branch_name bank_branch_name,
'ROI_LINE' type,
'ROI_LINE' break_type,
'ROI_LINE' type_dsp,
NULL supplier_customer,
c.trx_date trx_date,
TO_DATE(NULL) maturity_date,
c.amount bank_account_amount,
c.trx_number trx_number,
NULL payment_method,
c.currency_code currency_code,
c.amount amount,
c.bank_account_amount account_amount,
60 transaction_order,
to_number(NULL) batch_id,
NULL batch_number,
NULL remittance_number,
c.cleared_date cleared_date,
c.amount_cleared cleared_amount,
fnd_access_control_util.get_org_name(c.legal_entity_id) ORG_NAME,
/*CE_CEXCLEAR_XMLP_PKG.c_grand_cleared_amountformula('PAYMENT',:C_SUM_AMOUNT,:C_SUM_BATCH_AMOUNT) C_GRAND_CLEARED_AMOUNT,*/ 
CE_CEXCLEAR_XMLP_PKG.c_amountformula(c.amount,'ROI_LINE',NULL,to_number (NULL )) C_AMOUNT,
/*CE_CEXCLEAR_XMLP_PKG.c_bank_account_amountformula(DECODE (c.currency_code ,aba.currency_code ,c.amount ,NVL (c.base_amount ,c.amount ) )) C_BANK_ACCOUNT_AMOUNT,*/
CE_CEXCLEAR_XMLP_PKG.C_BATCH_AMOUNT_p C_BATCH_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_BANK_ACCOUNT_AMOUNT_p C_BATCH_BANK_ACCOUNT_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_CLEARED_AMOUNT_p C_BATCH_CLEARED_AMOUNT
FROM CE_185_reconciled_V c,
--ce_BANK_ACCTS_GT_v aba,
ce_BANK_ACCOUNTS aba,
ce_BANK_BRANCHES_v abb
WHERE aba.bank_branch_id=abb.branch_party_id
and abb.branch_party_id=:P_BANK_BRANCH
and aba.bank_account_id=c.bank_account_id
and c.bank_account_id=NVL(:P_BANK_ACCOUNT,c.bank_account_id)
and c.cleared_date >= NVL(:P_DATE_FROM,c.cleared_date)
and c.cleared_date <= NVL(:P_DATE_TO,c.cleared_date)
and :P_BATCH_OR_TRX='T'
and :P_TYPE in ('XTR_LINES','ALL')
and c.trx_type='PAYMENT'
and c.LEGAL_ENTITY_ID=nvl(:P_LEGAL_ENTITY_ID,c.LEGAL_ENTITY_ID)
--and c.LEGAL_ENTITY_ID=sys.LEGAL_ENTITY_ID
UNION ALL -- cashflow receipt
SELECT aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
BankParty.PARTY_NAME bank_name,
BranchParty.PARTY_NAME bank_branch_name,
'CASH' type,
'CASH' break_type,
'Cash' type_dsp,
null supplier_customer,
cc.CASHFLOW_DATE trx_date,
to_date(null) maturity_date,
cc.CASHFLOW_AMOUNT bank_account_amount,
nvl(CC.BANK_TRXN_NUMBER,cc.cashflow_id) trx_number,
null payment_method,
cc.CASHFLOW_CURRENCY_CODE currency_code,
cc.CASHFLOW_AMOUNT amount,
DECODE(cc.CASHFLOW_currency_code,aba.currency_code,cc.cashflow_amount,
nvl(cc.base_amount,cc.cashflow_amount)) account_amount,
70 transaction_order,
to_number(NULL) batch_id,
NULL batch_number,
NULL remittance_number,
cc.cleared_date cleared_date,
DECODE(cc.CASHFLOW_CURRENCY_CODE ,aba.currency_code,cc.cashflow_amount,
nvl(cc.base_amount,cc.cashflow_amount)) cleared_amount,
ou.name ORG_NAME ,
/*CE_CEXCLEAR_XMLP_PKG.c_grand_cleared_amountformula('PAYMENT',:C_SUM_AMOUNT,:C_SUM_BATCH_AMOUNT) C_GRAND_CLEARED_AMOUNT,*/
CE_CEXCLEAR_XMLP_PKG.c_amountformula(cc.CASHFLOW_AMOUNT,'CASH',NULL,to_number (NULL )) C_AMOUNT,
/*CE_CEXCLEAR_XMLP_PKG.c_bank_account_amountformula(DECODE (c.currency_code ,aba.currency_code ,c.amount ,NVL (c.base_amount ,c.amount ) )) C_BANK_ACCOUNT_AMOUNT,*/
CE_CEXCLEAR_XMLP_PKG.C_BATCH_AMOUNT_p C_BATCH_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_BANK_ACCOUNT_AMOUNT_p C_BATCH_BANK_ACCOUNT_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_CLEARED_AMOUNT_p C_BATCH_CLEARED_AMOUNT
FROM --CE_BANK_ACCTS_GT_V ABA,
CE_BANK_ACCOUNTS aba,
CE_CASHFLOWS CC,
--CE_CASHFLOW_ACCT_H CCH,
HZ_PARTIES BankParty,
HZ_PARTIES BranchParty,
CE_SECURITY_PROFILES_GT OU
WHERE ABA.BANK_BRANCH_ID=BranchParty.PARTY_ID AND 
ABA.BANK_ID=BankParty.PARTY_ID AND 
ABA.BANK_BRANCH_ID=:P_BANK_BRANCH and
aba.ACCOUNT_OWNER_ORG_ID= OU.ORGANIZATION_ID AND 
OU.ORGANIZATION_TYPE='LEGAL_ENTITY' and
aba.bank_account_id=NVL(:P_BANK_ACCOUNT,aba.bank_account_id) and
ABA.BANK_ACCOUNT_ID=CC.CASHFLOW_BANK_ACCOUNT_ID AND
ABA.ACCOUNT_OWNER_ORG_ID=cc.CASHFLOW_LEGAL_ENTITY_ID AND 
cc.CASHFLOW_STATUS_CODE='CLEARED' and
cc.cleared_date >= NVL(:P_DATE_FROM,cc.cleared_date) and
cc.cleared_date <= NVL(:P_DATE_TO,cc.cleared_date) and
cc.CASHFLOW_DIRECTION='RECEIPT' and
:P_BATCH_OR_TRX='T' and
:P_TYPE IN ('CASHFLOWS','ALL') 
UNION ALL -- cashflow payments
SELECT aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
BankParty.PARTY_NAME bank_name,
BranchParty.PARTY_NAME bank_branch_name,
'PAYMENT' type,
'PAYMENT' break_type,
'Payment' type_dsp,
null supplier_customer,
cc.CASHFLOW_DATE trx_date,
to_date(null) maturity_date,
cc.CASHFLOW_AMOUNT bank_account_amount,
nvl(CC.BANK_TRXN_NUMBER,cc.cashflow_id) trx_number,
null payment_method,
cc.CASHFLOW_CURRENCY_CODE currency_code,
cc.CASHFLOW_AMOUNT amount,
DECODE(cc.CASHFLOW_currency_code,aba.currency_code,cc.cashflow_amount,
nvl(cc.base_amount,cc.cashflow_amount)) account_amount,
80 transaction_order,
to_number(NULL) batch_id,
NULL batch_number,
NULL remittance_number,
cc.cleared_date cleared_date,
DECODE(cc.CASHFLOW_CURRENCY_CODE ,aba.currency_code,cc.cashflow_amount,
nvl(cc.base_amount,cc.cashflow_amount)) cleared_amount,
ou.name ORG_NAME ,
/*CE_CEXCLEAR_XMLP_PKG.c_grand_cleared_amountformula('PAYMENT',:C_SUM_AMOUNT,:C_SUM_BATCH_AMOUNT) C_GRAND_CLEARED_AMOUNT,*/
CE_CEXCLEAR_XMLP_PKG.c_amountformula(cc.CASHFLOW_AMOUNT,'PAYMENT',NULL,to_number (NULL )) C_AMOUNT,
/*CE_CEXCLEAR_XMLP_PKG.c_bank_account_amountformula(DECODE (c.currency_code ,aba.currency_code ,c.amount ,NVL (c.base_amount ,c.amount ) )) C_BANK_ACCOUNT_AMOUNT,*/
CE_CEXCLEAR_XMLP_PKG.C_BATCH_AMOUNT_p C_BATCH_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_BANK_ACCOUNT_AMOUNT_p C_BATCH_BANK_ACCOUNT_AMOUNT,
CE_CEXCLEAR_XMLP_PKG.C_BATCH_CLEARED_AMOUNT_p C_BATCH_CLEARED_AMOUNT
FROM --CE_BANK_ACCTS_GT_V ABA,
CE_BANK_ACCOUNTS aba,
CE_CASHFLOWS CC,
--CE_CASHFLOW_ACCT_H CCH,
HZ_PARTIES BankParty,
HZ_PARTIES BranchParty,
CE_SECURITY_PROFILES_GT OU
WHERE ABA.BANK_BRANCH_ID=BranchParty.PARTY_ID AND 
ABA.BANK_ID=BankParty.PARTY_ID AND 
ABA.BANK_BRANCH_ID =:P_BANK_BRANCH and
aba.ACCOUNT_OWNER_ORG_ID= OU.ORGANIZATION_ID AND 
OU.ORGANIZATION_TYPE='LEGAL_ENTITY' and
aba.bank_account_id=NVL(:P_BANK_ACCOUNT,aba.bank_account_id) and
ABA.BANK_ACCOUNT_ID=CC.CASHFLOW_BANK_ACCOUNT_ID AND
ABA.ACCOUNT_OWNER_ORG_ID=cc.CASHFLOW_LEGAL_ENTITY_ID AND 
cc.CASHFLOW_STATUS_CODE='CLEARED' and
cc.cleared_date >= NVL(:P_DATE_FROM,cc.cleared_date) and
cc.cleared_date <= NVL(:P_DATE_TO,cc.cleared_date) and
cc.CASHFLOW_DIRECTION='PAYMENT' and
:P_BATCH_OR_TRX='T' and
:P_TYPE IN ('CASHFLOWS','ALL') 
/*&C_ORDER_BY*/
order by 1,2,3,4,17,5,23,16,10,6
Parameter Name SQL text Validation
CE_OU_DUMMY
 
CE_BG_DUMMY
 
CE_LE_DUMMY
 
Order By
 
LOV Oracle
Cleared Date To
 
Date
Cleared Date From
 
Date
AP/AR Operating Unit
 
LOV Oracle
Payroll Business Group
 
LOV Oracle
Treasury Legal Entity
 
LOV Oracle
Transaction Type
 
LOV Oracle
Transaction/Batch
 
LOV Oracle
Bank Account Number
 
LOV Oracle
Bank Branch
 
LOV Oracle
Ask a question