Oracle Cloud Fusion R13 Account Payable (AP) Invoice Aging - draft SQL
=========================================================================
SELECT hp.party_name C_VENDOR_NAME,
v.segment1 C_VENDOR_NUMBER,
decode(upper(:P_SORT_OPTION),
'VENDOR NAME',decode(:C_VENDOR_NAME_SELECT,
'%',upper(hp.party_name),
hp.party_name),
i.invoice_type_lookup_code) C_PRIMARY_BRK,
decode(upper(:P_SORT_OPTION),
'VENDOR NAME',decode(:C_VENDOR_NAME_SELECT,
'%', decode(:SORT_BY_ALTERNATE, 'Y', upper(hp.organization_name_phonetic), upper(hp.party_name)),
decode(:SORT_BY_ALTERNATE, 'Y', hp.organization_name_phonetic, hp.party_name)),
i.invoice_type_lookup_code) C_PRIMARY_BRK_REAL,
hp.party_name C_SHORT_VENDOR_NAME,
v.vendor_id C_VENDOR_ID,
i.vendor_site_id C_CONTACT_SITE_ID,
vs.vendor_site_code C_VENDOR_SITE_CODE,
decode(:SORT_BY_ALTERNATE, 'Y', vs.vendor_site_code_alt, vs.vendor_site_code) C_VENDOR_SITE_CODE_BRK,
nvl(vs.state,' ') C_VENDOR_STATE,
nvl(substr(vs.city,1,15),' ') C_VENDOR_CITY,
ps.payment_num C_REFERENCE_NUMBER,
i.vendor_site_id C_ADDRESS_ID,
nvl(substr(i.invoice_type_lookup_code,1,20), ' ') C_INVOICE_TYPE,
i.invoice_id C_PAYMENT_SCHED_ID,
nvl(to_char(ps.due_date,'DD-MON-RR'),' ') C_DUE_DATE,
decode(i.invoice_currency_code,:C_BASE_CURRENCY_CODE,
decode(:C_BASE_MIN_ACCT_UNIT,
0,round(((nvl(ps.amount_remaining, 0)/(nvl(i.payment_cross_rate,1))) *
nvl(i.exchange_rate,1)),:C_BASE_PRECISION),
round(((nvl(ps.amount_remaining, 0)/(nvl(i.payment_cross_rate,1))) *
nvl(i.exchange_rate,1))/:C_BASE_MIN_ACCT_UNIT) *
:C_BASE_MIN_ACCT_UNIT),
decode(i.exchange_rate,NULL,0,
decode(:C_BASE_MIN_ACCT_UNIT,
0,round(((nvl(ps.amount_remaining, 0)/(nvl(i.payment_cross_rate,1))) *
nvl(i.exchange_rate,1)),:C_BASE_PRECISION),
round(((nvl(ps.amount_remaining, 0)/(nvl(i.payment_cross_rate,1))) *
nvl(i.exchange_rate,1))/:C_BASE_MIN_ACCT_UNIT) *
:C_BASE_MIN_ACCT_UNIT))) C_AMT_DUE_REMAINING,
decode(i.invoice_currency_code,:C_BASE_CURRENCY_CODE,
decode(:C_BASE_MIN_ACCT_UNIT,
0,round(((nvl(ps.gross_amount, 0)/(nvl(i.payment_cross_rate,1))) *
nvl(i.exchange_rate,1)),:C_BASE_PRECISION),
round(((nvl(ps.gross_amount, 0)/(nvl(i.payment_cross_rate,1))) *
nvl(i.exchange_rate,1))/:C_BASE_MIN_ACCT_UNIT) *
:C_BASE_MIN_ACCT_UNIT),
decode(i.exchange_rate,NULL,0,
decode(:C_BASE_MIN_ACCT_UNIT,
0,round(((nvl(ps.gross_amount, 0)/(nvl(i.payment_cross_rate,1))) *
nvl(i.exchange_rate,1)),:C_BASE_PRECISION),
round(((nvl(ps.gross_amount, 0)/(nvl(i.payment_cross_rate,1))) *
nvl(i.exchange_rate,1))/:C_BASE_MIN_ACCT_UNIT) *
:C_BASE_MIN_ACCT_UNIT))) C_AMT_DUE_ORIGINAL,
i.accts_pay_code_combination_id C_VENDOR_TRX_ID,
i.invoice_num C_INVOICE_NUMBER,
i.invoice_num C_INVOICE_NUM_SHORT,
to_char(i.invoice_date,'DD-MON-RR') C_INVOICE_DATE,
ceil(to_date(to_char(SYSDATE,'DD-MON-RR'),'DD-MON-RR') -
to_date(ps.due_date,'DD-MON-RR')) C_DAYS_PAST_DUE,
decode(i.invoice_currency_code,
:C_BASE_CURRENCY_CODE, ' ',
decode(i.exchange_rate,
NULL,'*',
' ')) C_DATA_CONVERTED,
nvl(i.exchange_rate, 1) C_EXCHANGE_RATE,
decode(:C_VENDOR_NAME_SELECT,
'%',decode(:SORT_BY_ALTERNATE, 'Y', upper(hp.organization_name_phonetic), upper(hp.party_name)),
decode(:SORT_BY_ALTERNATE, 'Y', hp.organization_name_phonetic, hp.party_name)) C_VENDOR_NAME_BRK,
--AP_APXINAGE_XMLP_PKG.c_per_inv_due_amt_1formula(:C_SUM_INV_DUE_AMT_1, :C_SUM_AMT_REMAINING) C_PER_INV_DUE_AMT_1,
--AP_APXINAGE_XMLP_PKG.c_pgbrk_data_convertedformula(:C_SUM_DATA_CONVERTED) C_PGBRK_DATA_CONVERTED,
--AP_APXINAGE_XMLP_PKG.c_per_inv_due_amt_2formula(:C_SUM_INV_DUE_AMT_2, :C_SUM_AMT_REMAINING) C_PER_INV_DUE_AMT_2,
--AP_APXINAGE_XMLP_PKG.c_per_inv_due_amt_3formula(:C_SUM_INV_DUE_AMT_3, :C_SUM_AMT_REMAINING) C_PER_INV_DUE_AMT_3,
--AP_APXINAGE_XMLP_PKG.c_per_inv_due_amt_4formula(:C_SUM_INV_DUE_AMT_4, :C_SUM_AMT_REMAINING) C_PER_INV_DUE_AMT_4,
--AP_APXINAGE_XMLP_PKG.c_per_v_inv_amt_1formula(:C_SUM_V_INV_AMT_1, :C_SUM_V_DUE_REMAINING) C_PER_V_INV_AMT_1,
--AP_APXINAGE_XMLP_PKG.c_per_v_inv_amt_2formula(:C_SUM_V_INV_AMT_2, :C_SUM_V_DUE_REMAINING) C_PER_V_INV_AMT_2,
---AP_APXINAGE_XMLP_PKG.c_per_v_inv_amt_3formula(:C_SUM_V_INV_AMT_3, :C_SUM_V_DUE_REMAINING) C_PER_V_INV_AMT_3,
--AP_APXINAGE_XMLP_PKG.c_per_v_inv_amt_4formula(:C_SUM_V_INV_AMT_4, :C_SUM_V_DUE_REMAINING) C_PER_V_INV_AMT_4,
--AP_APXINAGE_XMLP_PKG.c_v_data_convertedformula(:C_SUM_V_DATA_CONVERTED) C_V_DATA_CONVERTED,
--AP_APXINAGE_XMLP_PKG.c_contact_lineformula(i.vendor_site_id) C_CONTACT_LINE,
--AP_APXINAGE_XMLP_PKG.c_percent_remainingformula(decode ( i.invoice_currency_code , :C_BASE_CURRENCY_CODE , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.gross_amount , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.gross_amount , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) , decode ( i.exchange_rate , NULL , 0 , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.gross_amount , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.gross_amount , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) ) ), decode ( i.invoice_currency_code , :C_BASE_CURRENCY_CODE , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) , decode ( i.exchange_rate , NULL , 0 , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) ) )) C_PERCENT_REMAINING,
AP_APXINAGE_XMLP_PKG.c_inv_due_amt_1formula(ceil ( to_date ( to_char ( SYSDATE , 'DD-MON-RR' ) , 'DD-MON-RR' ) - ps.due_date ), decode ( i.invoice_currency_code , :C_BASE_CURRENCY_CODE , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) , decode ( i.exchange_rate , NULL , 0 , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) ) )) C_INV_DUE_AMT_1,
--AP_APXINAGE_XMLP_PKG.c_inv_due_amt_2formula(ceil ( to_date ( to_char ( SYSDATE , 'DD-MON-RR' ) , 'DD-MON-RR' ) - ps.due_date ), decode ( i.invoice_currency_code , :C_BASE_CURRENCY_CODE , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) , decode ( i.exchange_rate , NULL , 0 , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) ) )) C_INV_DUE_AMT_2,
--AP_APXINAGE_XMLP_PKG.c_inv_due_amt_3formula(ceil ( to_date ( to_char ( SYSDATE , 'DD-MON-RR' ) , 'DD-MON-RR' ) - ps.due_date ), decode ( i.invoice_currency_code , :C_BASE_CURRENCY_CODE , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) , decode ( i.exchange_rate , NULL , 0 , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) ) )) C_INV_DUE_AMT_3,
--AP_APXINAGE_XMLP_PKG.c_inv_due_amt_4formula(ceil ( to_date ( to_char ( SYSDATE , 'DD-MON-RR' ) , 'DD-MON-RR' ) - ps.due_date ), decode ( i.invoice_currency_code , :C_BASE_CURRENCY_CODE , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) , decode ( i.exchange_rate , NULL , 0 , decode ( :C_BASE_MIN_ACCT_UNIT , 0 , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) , :C_BASE_PRECISION ) , round ( ( ( nvl ( ps.amount_remaining , 0 ) / ( nvl ( i.payment_cross_rate , 1 ) ) ) * nvl ( i.exchange_rate , 1 ) ) / :C_BASE_MIN_ACCT_UNIT ) * :C_BASE_MIN_ACCT_UNIT ) ) )) C_INV_DUE_AMT_4,
--AP_APXINAGE_XMLP_PKG.c_check_data_convertedformula(decode ( i.invoice_currency_code , :C_BASE_CURRENCY_CODE , ' ' , decode ( i.exchange_rate , NULL , '*' , ' ' ) )) C_CHECK_DATA_CONVERTED
FROM ap_payment_schedules ps,
ap_invoices i,
hz_parties hp,
POZ_SUPPLIERS v,
POZ_SUPPLIER_SITES_ALL_M vs
WHERE i.invoice_id = ps.invoice_id
AND i.party_id = hp.party_id
AND hp.party_id = v.party_id (+)
AND i.vendor_site_id = vs.vendor_site_id (+)
&P_PARTY_PREDICATE
AND ((to_date(to_char(SYSDATE,'DD-MON-RR'),'DD-MON-RR') - ps.due_date)
between :C_MINDAYS and :C_MAXDAYS)
AND i.invoice_type_lookup_code like :C_INVOICE_TYPE_SELECT
AND i.cancelled_date IS NULL
AND (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate,1)) != 0
&P_AMOUNT_PREDICATE
AND i.payment_status_flag in ('N','P')
&P_ORDER_BY
No comments:
Post a Comment