JG Regional Dunning Letter - draft
Description
Categories: BI Publisher
Application: Regional Localizations
Source: Regional Dunning Letter (XML) - Not Supported: Reserved For Future Use
Short Name: JGZZRDLR_XML
DB package: JG_JGZZRDLR_XMLP_PKG
Source: Regional Dunning Letter (XML) - Not Supported: Reserved For Future Use
Short Name: JGZZRDLR_XML
DB package: JG_JGZZRDLR_XMLP_PKG
Run
JG Regional Dunning Letter - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |