select
rtrim(rpad(l_types.meaning,11)) invoice_type,
A.trx_number trx_number,
rtrim(rpad(types.name,8)) type,
substrb(PARTY.party_name,1,50) name, B.account_number customer_number, A.trx_date invoice_date,
P.due_date ps_due_date,
nvl(
P.amount_due_original +
decode(a.initial_customer_trx_id,
'', 0,
nvl(com_adj.amount,0)
),
(
rec.amount *
(
NVL(TL.RELATIVE_AMOUNT, 100) *
(100/NVL(T.BASE_AMOUNT,100))
) / 100
)
) amount,
rtrim(rpad(A.invoice_currency_code, 11)) invoice_currency_code,
printing_last_printed last_print_date,
decode(printing_pending, 'Y', '*', '') printing_pending,
A.last_printed_sequence_num last_printed_sequence_num,
TL.sequence_num sequence_num,
AR_RAXINVPR_XMLP_PKG.installment_last_print_datefor(TL.sequence_num, A.last_printed_sequence_num, decode ( printing_pending , 'Y' , '*' , '' ), printing_last_printed) INSTALLMENT_LAST_PRINT_DATE,
AR_RAXINVPR_XMLP_PKG.INSTALLMENT_PRINTING_PENDING_p INSTALLMENT_PRINTING_PENDING
,AR_RAXINVPR_XMLP_PKG.D_AmountFormula()
FROM &p_table1
WHERE &p_where1
UNION
select
rtrim(rpad(l_types.meaning,11)) invoice_type,
A.trx_number trx_number,
rtrim(rpad(types.name,8)) type,
substrb(PARTY.party_name,1,50) name, B.account_number customer_number, A.trx_date invoice_date,
P.due_date ps_due_date,
P.amount_due_original +
decode(a.initial_customer_trx_id,
'', 0,
nvl(com_adj.amount,0)
) amount,
rtrim(rpad(A.invoice_currency_code,11) ) invoice_currency_code,
printing_last_printed last_print_date,
decode(printing_pending, 'Y', '*', '') printing_pending,
A.last_printed_sequence_num last_printed_sequence_num,
P.terms_sequence_number sequence_num,
AR_RAXINVPR_XMLP_PKG.installment_last_print_datefor(TL.sequence_num, A.last_printed_sequence_num, decode ( printing_pending , 'Y' , '*' , '' ), printing_last_printed) INSTALLMENT_LAST_PRINT_DATE,
AR_RAXINVPR_XMLP_PKG.INSTALLMENT_PRINTING_PENDING_p INSTALLMENT_PRINTING_PENDING
,AR_RAXINVPR_XMLP_PKG.D_AmountFormula()
FROM &p_table2
WHERE &p_where2
order by 9,1,3,2,6,7 |