SELECT
ps.invoice_currency_code Currency_Code,
DECODE (:p_order_by ,'Adjustment Status',l2.meaning,NULL) Status_1,
DECODE (:p_order_by,'Adjustment Name',rcvbl_trx.name,NULL) Name_1,
DECODE (:p_order_by,'Creator',fndc.user_name,NULL) Created_By,
cust.cust_account_id Customer_Id ,
substrb(party.party_name,1,50) Customer_Name,
cust.account_number Customer_Number,
trx.trx_number Invoice_Number,
ra_cust_trx_types.name Type,
ps.due_date Due_Date,
l2.meaning Status,
rcvbl_trx.name Name,
l1.meaning Reason,
fndc.user_name Entered_By,
adj.approved_by Approved_By,
TO_CHAR(adj.gl_date,'DD-MON-RR') GL_Date,
DECODE(:p_curr_code,NULL , ROUND(adj.acctd_amount,2),
adj.amount) Adjustment_Amount,
ROUND(adj.acctd_amount,2) Adjustment_Amount_Func,
AR_ARXCTA_XMLP_PKG.c_data_not_foundformula(ps.invoice_currency_code) C_DATA_NOT_FOUND,
AR_ARXCTA_XMLP_PKG.c_currency_summary_labelformul(ps.invoice_currency_code) C_currency_summary_label,
AR_ARXCTA_XMLP_PKG.c_status_summary_labelformula(ps.invoice_currency_code, DECODE ( :p_order_by , 'Adjustment Status' , l2.meaning , NULL )) C_status_summary_label,
AR_ARXCTA_XMLP_PKG.c_name_summary_labelformula(ps.invoice_currency_code, DECODE ( :p_order_by , 'Adjustment Name' , rcvbl_trx.name , NULL )) C_name_summary_label,
AR_ARXCTA_XMLP_PKG.c_creator_labelformula(ps.invoice_currency_code, DECODE ( :p_order_by , 'Creator' , fndc.user_name , NULL )) C_creator_label
from ar_payment_schedules ps,
hz_cust_accounts cust,
hz_parties party,
ar_receivables_trx rcvbl_trx,
ra_cust_trx_types,
ra_customer_trx trx,
ar_adjustments adj,
ar_lookups l1,
ar_lookups l2,
fnd_user fndc
where adj.receivables_trx_id = rcvbl_trx.receivables_trx_id
&lp_gl_date_low
&lp_gl_date_high
and adj.reason_code = l1.lookup_code(+)
and l1.lookup_type(+) = 'ADJUST_REASON'
and adj.payment_schedule_id = ps.payment_schedule_id
and ps.invoice_currency_code = DECODE(:p_curr_code,NULL, ps.invoice_currency_code,
:p_curr_code)
and ps.customer_trx_id = trx.customer_trx_id
and trx.cust_trx_type_id = ra_cust_trx_types.cust_trx_type_id
&lp_invoice_low
&lp_invoice_high
&lp_adjustment_name_low
&lp_adjustment_name_high
&lp_invoice_type_low
&lp_invoice_type_high
&lp_customer_name_low
&lp_customer_name_high
&lp_customer_number_low
&lp_customer_number_high
and ps.customer_id = cust.cust_account_id
and cust.party_id = party.party_id
and adj.status= l2.lookup_code
and l2.lookup_type = 'APPROVAL_TYPE'
&lp_status_low
&lp_status_high
and adj.status != 'U'
and adj.created_by = fndc.user_id
&lp_created_by_low
&lp_created_by_high
&lp_order_by |