select paysch.trx_number Invoice_Number,
substrb(party.party_name,1,50) Customer_Name ,
paysch.trx_date Invoice_Date ,
round (sysdate - paysch.due_date) Days_Overdue ,
decode( :p_currency_code,
NULL, decode( :minimum_accountable_unit,
NULL, round( nvl( paysch.exchange_rate, 1) *
paysch.tax_original,
:precision
),
round( nvl( paysch.exchange_rate, 1) *
paysch.tax_original /
:minimum_accountable_unit
) * :minimum_accountable_unit
),
paysch.tax_original
) Original_Amount ,
decode( :p_currency_code,
NULL, decode( :minimum_accountable_unit,
NULL, round( nvl( paysch.exchange_rate, 1) *
paysch.TAX_REMAINING,
:precision),
round( nvl( paysch.exchange_rate, 1) *
paysch.TAX_REMAINING /
:minimum_accountable_unit) * :minimum_accountable_unit),
paysch.TAX_REMAINING) Amount_Outstanding,
ZX_ZXXATB_XMLP_PKG.C_DATA_NOT_FOUNDFORMULA(paysch.trx_number) C_DATA_NOT_FOUND
from ar_payment_schedules_all paysch,
hz_cust_accounts_all cust,
hz_parties party
where paysch.customer_id = cust.cust_account_id
and cust.party_id = party.party_id
and paysch.invoice_currency_code =
decode( :p_currency_code,
NULL , paysch.invoice_currency_code,
:p_currency_code)
&lp_start_trx_date
&lp_end_trx_date
&LP_REP_CONTEXT_WH
and amount_due_remaining <= tax_original
and tax_original != 0
and nvl(tax_remaining,0) <> 0
and paysch.status = 'OP'
and amount_due_remaining > 0
order by decode( :p_order_by,
'Invoice Number', trx_number,
'Customer', party.party_name,
'Invoice Date', to_char(trx_date, 'J'),
party.party_name
) |
