CE Cash In Transit - draft

➜ Back to Library
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
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 NameSQL textValidation
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
 
Download
Related Blitz Reports
CE Cleared Transactions - draft AP Bills Payable Maturity Date AP Void Payment Register - draft AP Payment Exceptions - draft AR Miscellaneous Receipts AP Bank Account Listing - draft AP Invoice Payments AP Payment Register - draft CE General Ledger Reconciliation - draft
Blitz Report In Action
Blitz Report™