select distinct
rcx.customer_trx_id TRX_ID,
NVL(rps.exchange_rate,1) exchange_rate,
hca.cust_account_id Cusid,
substr(hp.party_name,1, 25) Customer,
nvl(substr(hca.account_number,1,15), 'No customer number') Cusno,
substr(hl.address1,1,20)|| ' '|| substr(hl.city,1,15) Site,
nvl(substr(hcsu.tax_reference,1, 20),'No TAX ID') tax_id,
substr(rcx.invoice_currency_code,1,3) Currency_Code,
rcx.trx_number Invoice_number,
rcx.trx_date Invoice_date,
substr(rcxt.type ,1,10) Trans_Type,
sum(rps.amount_due_original) Original_Frgn_amount,
sum(rps.amount_due_remaining) Amount_frgn_Due_Rem,
sum(nvl(rps.exchange_rate,1)*rps.amount_due_original) Original_func_amount,
sum(nvl(rps.exchange_rate,1)*nvl(rps.amount_due_remaining, 0)) Amount_func_Due_Rem,
min (rps.due_date) Due_Date,
:P_AS_OF_DATE1-(min(to_date(rps.due_date,'DD-MON-RRRR'))) Days_Past_Due,
&C_PRT_TOT_TAX_FUNC C_PRT_TOT_TAX_FUNC,
&C_PRT_TOT_TAX_FUNC_REM C_PRT_TOT_TAX_FUNC_REM,
&C_PRT_TOT_SITE_FUNC C_PRT_TOT_SITE_FUNC,
&C_PRT_TOT_SITE_FUNC_REM C_PRT_TOT_SITE_FUNC_REM,
&C_PRT_TOT_NET_FOREIGN C_PRT_TOT_NET_FOREIGN,
&C_PRT_TOT_NET_FOR_REM C_PRT_TOT_NET_FOR_REM,
&C_PRT_TOT_R_FOREIGN C_PRT_TOT_R_FOREIGN,
&C_PRT_TOT_O_FOREIGN C_PRT_TOT_O_FOREIGN,
&C_PRT_TOT_CUR_FUNC C_PRT_TOT_CUR_FUNC,
&C_PRT_TOT_CUR_FUNC_REM C_PRT_TOT_CUR_FUNC_REM,
JG_JGZZRDLR_XMLP_PKG.CF_INVOICE_DATEFORMULA(rcx.trx_date) CF_INVOICE_DATE,
-- &C_PRT_ORIG_FRGN_AMOUNT C_PRT_ORIG_FRGN_AMOUNT,
-- &C_PRT_FRGN_AMT_DUE_REM C_PRT_FRGN_AMT_DUE_REM,
-- &C_PRT_ORIG_FUNC_AMOUNT C_PRT_ORIG_FUNC_AMOUNT,
-- &C_PRT_FUNC_AMT_DUE_REM C_PRT_FUNC_AMT_DUE_REM,
sum(rps.amount_due_original) C_PRT_ORIG_FRGN_AMOUNT,
JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(sum ( rps.amount_due_remaining ), JG_JGZZRDLR_XMLP_PKG.applied_amountformula(rcx.customer_trx_id)) C_PRT_FRGN_AMT_DUE_REM,
sum(nvl(rps.exchange_rate,1)*rps.amount_due_original) C_PRT_ORIG_FUNC_AMOUNT,
JG_JGZZRDLR_XMLP_PKG.fun_cur_amt_remformula(NVL ( rps.exchange_rate , 1 ), JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(sum ( rps.amount_due_remaining ), JG_JGZZRDLR_XMLP_PKG.applied_amountformula(rcx.customer_trx_id)), sum ( nvl ( rps.exchange_rate , 1 ) * nvl ( rps.amount_due_remaining , 0 ) )) C_PRT_FUNC_AMT_DUE_REM,
JG_JGZZRDLR_XMLP_PKG.C_DUE_DATEFORMULA(min (rps.due_date)) C_DUE_DATE,
JG_JGZZRDLR_XMLP_PKG.applied_amountformula(rcx.customer_trx_id) APPLIED_AMOUNT,
JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(sum ( rps.amount_due_remaining ), JG_JGZZRDLR_XMLP_PKG.applied_amountformula(rcx.customer_trx_id)) ORG_CUR_AMT_REM,
JG_JGZZRDLR_XMLP_PKG.fun_cur_amt_remformula(NVL ( rps.exchange_rate , 1 ), JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(sum ( rps.amount_due_remaining ), JG_JGZZRDLR_XMLP_PKG.applied_amountformula(rcx.customer_trx_id)), sum ( nvl ( rps.exchange_rate , 1 ) * nvl ( rps.amount_due_remaining , 0 ) )) FUN_CUR_AMT_REM,
JG_JGZZRDLR_XMLP_PKG.org_cur_amountformula(JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(sum ( rps.amount_due_remaining ), JG_JGZZRDLR_XMLP_PKG.applied_amountformula(rcx.customer_trx_id)), sum ( rps.amount_due_original )) ORG_CUR_AMOUNT,
JG_JGZZRDLR_XMLP_PKG.fun_cur_amountformula(JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(sum ( rps.amount_due_remaining ), JG_JGZZRDLR_XMLP_PKG.applied_amountformula(rcx.customer_trx_id)), sum ( nvl ( rps.exchange_rate , 1 ) * rps.amount_due_original )) FUN_CUR_AMOUNT
from
ra_cust_trx_types rcxt,
ar_payment_schedules rps,
ra_customer_trx rcx,
hz_parties hp, hz_cust_accounts hca,
hz_locations hl,
hz_party_sites hps,
hz_cust_acct_sites hcas,
hz_cust_site_uses hcsu
where
rps.customer_trx_id = rcx.customer_trx_id
and hp.party_id = hca.party_id
and hca.cust_account_id = rcx.bill_to_customer_id
and rcx.cust_trx_type_id = rcxt.cust_trx_type_id
and hca.cust_account_id = hcas.cust_account_id
and hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
and hp.party_id=hps.party_id
and hps.location_id=hl.location_id
and hcsu.site_use_id=rcx.bill_to_site_use_id
and rcx.invoice_currency_code like decode(:P_CURRENCY, 'All','%','','%' ,:P_CURRENCY)
and TRUNC(rps.gl_date) <=TRUNC( :P_DATE_TO)
AND TRUNC(rcx.trx_date ) <=TRUNC(:P_DATE_TO)
group by
hca.cust_account_id,
hp.party_name, hca.account_number,
hl.address1, hl.city,
hcsu.tax_reference,
rcx.invoice_currency_code,
rcx.trx_date,
rcx.trx_number,
rcx.trx_date,
rcxt.type,
rcx.customer_trx_id,
NVL(rps.exchange_rate,1)
UNION
select distinct
-1 TRX_ID,
-1 exchange_rate,
hca.cust_account_id Cusid,
substr(hp.party_name,1, 25) Customer,
nvl(substr(hca.account_number,1,15), 'No customer number') Cusno,
substr(hl.address1,1,20)|| ' '|| substr(hl.city,1,15) Site,
nvl(substr(hcsu.tax_reference,1, 20),'No TAX ID') tax_id,
substr(rps.invoice_currency_code,1,3) Currency_Code,
'On-Account and Unapplied' Invoice_number,
to_date(NULL) Invoice_date,
'ONACC' Trans_type,
0 Original_Frgn_amount,
sum(rps.amount_due_remaining) Amount_frgn_Due_Rem,
0 Original_func_amount,
sum((-1)*nvl(rps.amount_due_remaining, 0)) Amount_func_Due_Rem,
rps.due_date Due_Date,
:P_AS_OF_DATE1-(min(to_date(rps.due_date,'DD-MON-RRRR'))) Days_Past_Due,
&C_PRT_TOT_TAX_FUNC C_PRT_TOT_TAX_FUNC,
&C_PRT_TOT_TAX_FUNC_REM C_PRT_TOT_TAX_FUNC_REM,
&C_PRT_TOT_SITE_FUNC C_PRT_TOT_SITE_FUNC,
&C_PRT_TOT_SITE_FUNC_REM C_PRT_TOT_SITE_FUNC_REM,
&C_PRT_TOT_NET_FOREIGN C_PRT_TOT_NET_FOREIGN,
&C_PRT_TOT_NET_FOR_REM C_PRT_TOT_NET_FOR_REM,
&C_PRT_TOT_R_FOREIGN C_PRT_TOT_R_FOREIGN,
&C_PRT_TOT_O_FOREIGN C_PRT_TOT_O_FOREIGN,
&C_PRT_TOT_CUR_FUNC C_PRT_TOT_CUR_FUNC,
&C_PRT_TOT_CUR_FUNC_REM C_PRT_TOT_CUR_FUNC_REM,
JG_JGZZRDLR_XMLP_PKG.CF_INVOICE_DATEFORMULA(to_date(NULL)) CF_INVOICE_DATE,
&C_PRT_ORIG_FRGN_AMOUNT C_PRT_ORIG_FRGN_AMOUNT,
&C_PRT_FRGN_AMT_DUE_REM C_PRT_FRGN_AMT_DUE_REM,
&C_PRT_ORIG_FUNC_AMOUNT C_PRT_ORIG_FUNC_AMOUNT,
&C_PRT_FUNC_AMT_DUE_REM C_PRT_FUNC_AMT_DUE_REM,
JG_JGZZRDLR_XMLP_PKG.C_DUE_DATEFORMULA(rps.due_date) C_DUE_DATE,
JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1) APPLIED_AMOUNT,
JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(sum ( rps.amount_due_remaining ), JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)) ORG_CUR_AMT_REM,
JG_JGZZRDLR_XMLP_PKG.fun_cur_amt_remformula(-1, JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(sum ( rps.amount_due_remaining ), JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)), sum ( nvl ( rps.exchange_rate , 1 ) * nvl ( rps.amount_due_remaining , 0 ) )) FUN_CUR_AMT_REM,
JG_JGZZRDLR_XMLP_PKG.org_cur_amountformula(JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(sum ( rps.amount_due_remaining ), JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)), 0) ORG_CUR_AMOUNT,
JG_JGZZRDLR_XMLP_PKG.fun_cur_amountformula(JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(sum ( rps.amount_due_remaining ), JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)), 0) FUN_CUR_AMOUNT
from
hz_parties hp, hz_cust_accounts hca,
hz_locations hl,
hz_party_sites hps,
hz_cust_acct_sites hcas,
hz_cust_site_uses hcsu,
ar_payment_schedules rps,
ar_cash_receipt_history crh
where rps.amount_due_remaining <> 0
and rps.cash_receipt_id IS NOT NULL
AND hcas.party_site_id = hps.party_site_id
and hp.party_id = hca.party_id
and hca.cust_account_id = hcas.cust_account_id
and hcas.cust_acct_site_id=hcsu.cust_acct_site_id
and hcsu.site_use_id=rps.customer_site_use_id
and hp.party_id=hps.party_id
and hps.location_id=hl.location_id
and rps.invoice_currency_code like decode(:P_CURRENCY, 'All','%','','%' ,:P_CURRENCY)
and TRUNC(crh.trx_date ) <= TRUNC( :P_DATE_TO)
and rps.cash_receipt_id = crh.cash_receipt_id
and crh.current_record_flag ='Y'
and crh.status ='CLEARED'
group by
hca.cust_account_id,
hp.party_name,
hca.account_number,
hl.address1,
hl.city,
hcsu.tax_reference,
rps.invoice_currency_code,
rps.due_date,
-1
HAVING sum(rps.amount_due_remaining)<>0
UNION
SELECT DISTINCT
-1 TRX_ID,
-1 exchange_rate,
hca.cust_account_id Cusid,
substr(hp.party_name,1, 25) Customer,
nvl(substr(hca.account_number,1,15), 'No customer number') Cusno,
substr(hl.address1,1,20)|| ' '|| substr(hl.city,1,15) Site,
nvl(substr(hcsu.tax_reference,1, 20),'No TAX ID') tax_id,
rps.invoice_currency_code Currency_Code,
rps.trx_number Invoice_Number,
rps.trx_date Invoice_date,
rps.class Trans_Type,
&P_RISKVAR1 Original_Frgn_amount,
0 Amount_frgn_Due_Rem,
(-1)* &P_RISKVAR1 Original_func_amount,
0 Amount_func_Due_Rem,
rps.due_date Due_Date,
:P_AS_OF_DATE1 -to_date(rps.due_date,'DD-MON-RRRR') Days_Past_Due,
&C_PRT_TOT_TAX_FUNC C_PRT_TOT_TAX_FUNC,
&C_PRT_TOT_TAX_FUNC_REM C_PRT_TOT_TAX_FUNC_REM,
&C_PRT_TOT_SITE_FUNC C_PRT_TOT_SITE_FUNC,
&C_PRT_TOT_SITE_FUNC_REM C_PRT_TOT_SITE_FUNC_REM,
&C_PRT_TOT_NET_FOREIGN C_PRT_TOT_NET_FOREIGN,
&C_PRT_TOT_NET_FOR_REM C_PRT_TOT_NET_FOR_REM,
&C_PRT_TOT_R_FOREIGN C_PRT_TOT_R_FOREIGN,
&C_PRT_TOT_O_FOREIGN C_PRT_TOT_O_FOREIGN,
&C_PRT_TOT_CUR_FUNC C_PRT_TOT_CUR_FUNC,
&C_PRT_TOT_CUR_FUNC_REM C_PRT_TOT_CUR_FUNC_REM,
JG_JGZZRDLR_XMLP_PKG.CF_INVOICE_DATEFORMULA(rps.trx_date) CF_INVOICE_DATE,
&C_PRT_ORIG_FRGN_AMOUNT C_PRT_ORIG_FRGN_AMOUNT,
&C_PRT_FRGN_AMT_DUE_REM C_PRT_FRGN_AMT_DUE_REM,
&C_PRT_ORIG_FUNC_AMOUNT C_PRT_ORIG_FUNC_AMOUNT,
&C_PRT_FUNC_AMT_DUE_REM C_PRT_FUNC_AMT_DUE_REM,
JG_JGZZRDLR_XMLP_PKG.C_DUE_DATEFORMULA(rps.due_date) C_DUE_DATE,
JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1) APPLIED_AMOUNT,
JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(0, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)) ORG_CUR_AMT_REM,
JG_JGZZRDLR_XMLP_PKG.fun_cur_amt_remformula(-1, JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(0, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)) , 0) FUN_CUR_AMT_REM,
JG_JGZZRDLR_XMLP_PKG.org_cur_amountformula(JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(0, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)) , 0) ORG_CUR_AMOUNT,
JG_JGZZRDLR_XMLP_PKG.fun_cur_amountformula(JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(0, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)) , 0) FUN_CUR_AMOUNT
from
ar_payment_schedules rps,
ar_cash_receipts cr,
ar_cash_receipt_history crh,
ar_receivable_applications ara,
hz_parties hp, hz_cust_accounts hca,
hz_locations hl,
hz_party_sites hps,
hz_cust_acct_sites hcas,
hz_cust_site_uses hcsu
where ara.applied_payment_schedule_id = rps.payment_schedule_id
and ara.amount_applied = rps.amount_due_original
and ara.cash_receipt_id = cr.cash_receipt_id
and cr.cash_receipt_id = crh.cash_receipt_id
AND hcas.party_site_id = hps.party_site_id
and hp.party_id = hca.party_id
and hca.cust_account_id = rps.customer_id
and rps.customer_site_use_id = hcsu.site_use_id
and hcas.cust_acct_site_id=hcsu.cust_acct_site_id
and cr.customer_site_use_id = hcsu.site_use_id
and hp.party_id=hps.party_id
and hps.location_id=hl.location_id
and cr.reversal_date is null
and crh.current_record_flag||'' = 'Y'
and rps.amount_due_remaining = 0
and crh.status not in (decode (crh.factor_flag, 'Y', 'RISK_ELIMINATED','N','CLEARED'),'REVERSED')
and rps.invoice_currency_code like decode(:P_CURRENCY, 'All','%','','%' ,:P_CURRENCY)
and TRUNC(rps.gl_date) <=TRUNC( :P_DATE_TO)
AND TRUNC(rps.trx_date) <=TRUNC( :P_DATE_TO)
and TRUNC(ara.apply_date) <= TRUNC( :P_DATE_TO)
AND (-1)* &P_RISKVAR1 <>0
UNION
SELECT DISTINCT
-1 TRX_ID,
-1 exchange_rate,
hca.cust_account_id Cusid,
substr(hp.party_name,1, 25) Customer,
nvl(substr(hca.account_number,1,15), 'No customer number') Cusno,
substr(hl.address1,1,20)|| ' '|| substr(hl.city,1,15) Site,
nvl(substr(hcsu.tax_reference,1, 20),'No TAX ID') tax_id,
rcx.invoice_currency_code Currency_Code,
'Receipts at Risk' Invoice_Number,
to_date(NULL) Invoice_date,
'RECT' Trans_Type,
0 Original_Frgn_amount,
&P_RISKVAR2 Amount_frgn_Due_Rem,
0 Original_func_amount,
nvl(rcx.exchange_rate,1)* &P_RISKVAR2 Amount_func_Due_Rem,
aps.due_date Due_Date,
:P_AS_OF_DATE1 -to_date(aps.due_date,'DD-MON-RRRR') Days_Past_Due,
&C_PRT_TOT_TAX_FUNC C_PRT_TOT_TAX_FUNC,
&C_PRT_TOT_TAX_FUNC_REM C_PRT_TOT_TAX_FUNC_REM,
&C_PRT_TOT_SITE_FUNC C_PRT_TOT_SITE_FUNC,
&C_PRT_TOT_SITE_FUNC_REM C_PRT_TOT_SITE_FUNC_REM,
&C_PRT_TOT_NET_FOREIGN C_PRT_TOT_NET_FOREIGN,
&C_PRT_TOT_NET_FOR_REM C_PRT_TOT_NET_FOR_REM,
&C_PRT_TOT_R_FOREIGN C_PRT_TOT_R_FOREIGN,
&C_PRT_TOT_O_FOREIGN C_PRT_TOT_O_FOREIGN,
&C_PRT_TOT_CUR_FUNC C_PRT_TOT_CUR_FUNC,
&C_PRT_TOT_CUR_FUNC_REM C_PRT_TOT_CUR_FUNC_REM,
JG_JGZZRDLR_XMLP_PKG.CF_INVOICE_DATEFORMULA(to_date(NULL)) CF_INVOICE_DATE,
&C_PRT_ORIG_FRGN_AMOUNT C_PRT_ORIG_FRGN_AMOUNT,
&C_PRT_FRGN_AMT_DUE_REM C_PRT_FRGN_AMT_DUE_REM,
&C_PRT_ORIG_FUNC_AMOUNT C_PRT_ORIG_FUNC_AMOUNT,
&C_PRT_FUNC_AMT_DUE_REM C_PRT_FUNC_AMT_DUE_REM,
JG_JGZZRDLR_XMLP_PKG.C_DUE_DATEFORMULA(aps.due_date) C_DUE_DATE,
JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1) APPLIED_AMOUNT,
JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(&P_RISKVAR2, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)) ORG_CUR_AMT_REM,
JG_JGZZRDLR_XMLP_PKG.fun_cur_amt_remformula((-1), JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(&P_RISKVAR2, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)), nvl(rcx.exchange_rate,1)* &P_RISKVAR2) FUN_CUR_AMT_REM,
JG_JGZZRDLR_XMLP_PKG.org_cur_amountformula(JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(&P_RISKVAR2, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)), 0) ORG_CUR_AMOUNT,
JG_JGZZRDLR_XMLP_PKG.fun_cur_amountformula(JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(&P_RISKVAR2, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)), 0) FUN_CUR_AMOUNT
from
ar_payment_schedules aps,
ar_cash_receipts cr,
ar_cash_receipt_history crh,
ar_receivable_applications ara,
hz_parties hp, hz_cust_accounts hca,
ra_customer_trx rcx,
hz_locations hl,
hz_party_sites hps,
hz_cust_acct_sites hcas,
hz_cust_site_uses hcsu
where ara.applied_customer_trx_id = rcx.customer_trx_id
and ara.payment_schedule_id = aps.payment_schedule_id
and cr.cash_receipt_id = aps.cash_receipt_id
and cr.cash_receipt_id = crh.cash_receipt_id
and hp.party_id = hca.party_id
AND hcas.party_site_id = hps.party_site_id
and hca.cust_account_id = aps.customer_id
and hca.cust_account_id = hcas.cust_account_id
and hcas.cust_acct_site_id=hcsu.cust_acct_site_id
and cr.customer_site_use_id = hcsu.site_use_id
and hp.party_id=hps.party_id
and hps.location_id=hl.location_id
and ara.status = 'APP'
and cr.reversal_date is null
and crh.current_record_flag||'' = 'Y'
and crh.status not in (decode (crh.factor_flag, 'Y', 'RISK_ELIMINATED','N','CLEARED'),'REVERSED')
and rcx.invoice_currency_code like decode(:P_CURRENCY, 'All','%','','%' ,:P_CURRENCY)
and TRUNC(ara.apply_date) <= TRUNC( :P_DATE_TO)
and ((TRUNC(crh.trx_date) <=( :P_DATE_TO) and crh.status ='CLEARED' ) or crh.status <> 'CLEARED')
AND &P_RISKVAR2 <> 0
UNION
SELECT DISTINCT
-1 TRX_ID,
-1 exchange_rate,
hca.cust_account_id Cusid,
substr(hp.party_name,1, 25) Customer,
nvl(substr(hca.account_number,1,15), 'No customer number') Cusno,
substr(hl.address1,1,20)|| ' '|| substr(hl.city,1,15) Site,
nvl(substr(hcsu.tax_reference,1, 20),'No TAX ID') tax_id,
rps.invoice_currency_code Currency_Code,
'Receipts at Risk' Invoice_Number,
to_date(NULL) Invoice_date,
'RECT' Trans_Type,
0 Original_Frgn_amount,
&P_RISKVAR3 Amount_frgn_Due_Rem,
0 Original_func_amount,
(-1)* &P_RISKVAR3 Amount_func_Due_Rem,
rps.due_date Due_Date,
:P_AS_OF_DATE1 -to_date(rps.due_date,'DD-MON-RRRR') Days_Past_Due,
&C_PRT_TOT_TAX_FUNC C_PRT_TOT_TAX_FUNC,
&C_PRT_TOT_TAX_FUNC_REM C_PRT_TOT_TAX_FUNC_REM,
&C_PRT_TOT_SITE_FUNC C_PRT_TOT_SITE_FUNC,
&C_PRT_TOT_SITE_FUNC_REM C_PRT_TOT_SITE_FUNC_REM,
&C_PRT_TOT_NET_FOREIGN C_PRT_TOT_NET_FOREIGN,
&C_PRT_TOT_NET_FOR_REM C_PRT_TOT_NET_FOR_REM,
&C_PRT_TOT_R_FOREIGN C_PRT_TOT_R_FOREIGN,
&C_PRT_TOT_O_FOREIGN C_PRT_TOT_O_FOREIGN,
&C_PRT_TOT_CUR_FUNC C_PRT_TOT_CUR_FUNC,
&C_PRT_TOT_CUR_FUNC_REM C_PRT_TOT_CUR_FUNC_REM,
JG_JGZZRDLR_XMLP_PKG.CF_INVOICE_DATEFORMULA(to_date(NULL)) CF_INVOICE_DATE,
&C_PRT_ORIG_FRGN_AMOUNT C_PRT_ORIG_FRGN_AMOUNT,
&C_PRT_FRGN_AMT_DUE_REM C_PRT_FRGN_AMT_DUE_REM,
&C_PRT_ORIG_FUNC_AMOUNT C_PRT_ORIG_FUNC_AMOUNT,
&C_PRT_FUNC_AMT_DUE_REM C_PRT_FUNC_AMT_DUE_REM,
JG_JGZZRDLR_XMLP_PKG.C_DUE_DATEFORMULA(rps.due_date) C_DUE_DATE,
JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1) APPLIED_AMOUNT,
JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(&P_RISKVAR3, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)) ORG_CUR_AMT_REM,
JG_JGZZRDLR_XMLP_PKG.fun_cur_amt_remformula(-1, JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(&P_RISKVAR3, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)), nvl(rps.exchange_rate,1)* &P_RISKVAR3) FUN_CUR_AMT_REM,
JG_JGZZRDLR_XMLP_PKG.org_cur_amountformula(JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(&P_RISKVAR3, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)), 0) ORG_CUR_AMOUNT,
JG_JGZZRDLR_XMLP_PKG.fun_cur_amountformula(JG_JGZZRDLR_XMLP_PKG.org_cur_amt_remformula(&P_RISKVAR3, JG_JGZZRDLR_XMLP_PKG.applied_amountformula(-1)), 0) FUN_CUR_AMOUNT
from
ar_payment_schedules rps,
ar_cash_receipts cr,
ar_cash_receipt_history crh,
ar_receivable_applications ara,
hz_parties hp, hz_cust_accounts hca,
hz_locations hl,
hz_party_sites hps,
hz_cust_acct_sites hcas,
hz_cust_site_uses hcsu
where ara.payment_schedule_id = rps.payment_schedule_id
and rps.invoice_currency_code like decode(:P_CURRENCY, 'All','%','','%' ,:P_CURRENCY)
and cr.cash_receipt_id = ara.cash_receipt_id
and cr.cash_receipt_id = rps.cash_receipt_id
and cr.cash_receipt_id = crh.cash_receipt_id
and hp.party_id = hca.party_id
and hca.cust_account_id = rps.customer_id
and hca.cust_account_id = hcas.cust_account_id
and hcas.cust_acct_site_id=hcsu.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id
and cr.customer_site_use_id = hcsu.site_use_id
and hp.party_id=hps.party_id
and hps.location_id=hl.location_id
and ara.status = 'UNAPP'
and cr.reversal_date is null
and crh.current_record_flag||'' = 'Y'
and crh.status not in (decode (crh.factor_flag, 'Y', 'RISK_ELIMINATED','N','CLEARED'),'REVERSED')
and rps.status = 'OP'
and TRUNC(rps.gl_date) <=TRUNC( :P_DATE_TO)
and ((TRUNC(crh.trx_date) <= TRUNC(:P_DATE_TO) and crh.status ='CLEARED') or crh .status <> 'CLEARED')
and &P_RISKVAR3 <> 0
and hca.cust_account_id=:Cusid1
ORDER BY 3,6,5,7,10,11 |