ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F14A3869E053B46B63588994 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 Y JG JGZZRDLR_XML US JG Regional Dunning Letter - draft Application: Regional Localizations Source: Regional Dunning Letter (XML) - Not Supported: Reserved For Future Use Short Name: JGZZRDLR_XML DB package: JG_JGZZRDLR_XMLP_PKG BI Publisher &c_prt_frgn_amt_due_rem &c_prt_func_amt_due_rem &c_prt_orig_frgn_amount &c_prt_orig_func_amount &c_prt_tot_cur_func &c_prt_tot_cur_func_rem &c_prt_tot_net_for_rem &c_prt_tot_net_foreign &c_prt_tot_o_foreign &c_prt_tot_r_foreign &c_prt_tot_site_func &c_prt_tot_site_func_rem &c_prt_tot_tax_func &c_prt_tot_tax_func_rem &p_riskvar1 &p_riskvar2 &p_riskvar3 :c_currency_code :c_rar_yes_no :c_set_of_books_name :c_struct_num :cusid1 :p_as_of_date :p_as_of_date1 :p_chart_of_accounts_id :p_conc_request_id :p_currency :p_cust_id :p_customer_from :p_customer_to :p_date_to :p_debug_switch :p_dunning_letter_id :p_incl_rar :p_riskvar1 :p_riskvar2 :p_riskvar3 :p_set_of_books_id :p_site :p_tax_id :p_where_curr 1 10 :p_as_of_date Date select sysdate from dual Y US As of Date 2 20 :p_dunning_letter_id LOV Oracle JG_DUNNING_LETTER Y select dunning_letter_id id, letter_name value, null description from ar_dunning_letters_tl where language=userenv('LANG') order by letter_name Y US Dunning Letter 3 30 :p_customer_from LOV Oracle JGZZ_CUSTOMER_NAME Y Y select party.party_name id, party.party_name value, null description from hz_parties party,hz_cust_accounts cust_acct where cust_acct.party_id=party.party_id and cust_acct.cust_account_id= (select max(cust_acct2.cust_account_id) from hz_parties party2, hz_cust_accounts cust_acct2 where cust_acct2.party_id=party2.party_id and party2.party_name=party.party_name) order by party.party_name US Customer From 4 40 :p_customer_to LOV Oracle JGZZ_CUSTOMER_NAME Y Y select party.party_name id, party.party_name value, null description from hz_parties party,hz_cust_accounts cust_acct where cust_acct.party_id=party.party_id and cust_acct.cust_account_id= (select max(cust_acct2.cust_account_id) from hz_parties party2, hz_cust_accounts cust_acct2 where cust_acct2.party_id=party2.party_id and party2.party_name=party.party_name) order by party.party_name US Customer To 5 50 :p_currency LOV Oracle GL_SRS_CURRENCIES Y select currency_code id, currency_code value, null description from fnd_currencies_vl where enabled_flag in ('Y','N') order by currency_code US Currency Code 6 60 :p_incl_rar LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description select meaning from fnd_lookups where lookup_type='YES_NO' and lookup_code='N' Y US Include Receipts at Risk