AR Adjustment Approval

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Adjustment Approval Report (XML)
Short Name: ARXCTA_XML
DB package: AR_ARXCTA_XMLP_PKG
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
Ask a question
Parameter Name SQL text Validation
Currency
 
LOV Oracle
Created By High
 
LOV Oracle
Created By Low
 
LOV Oracle
Status High
 
LOV Oracle
Status Low
 
LOV Oracle
GL Date High
 
Date
GL Date Low
 
Date
Invoice Type High
 
LOV Oracle
Invoice Type Low
 
LOV Oracle
Invoice Number High
 
LOV Oracle
Invoice Number Low
 
LOV Oracle
Customer Number High
 
LOV Oracle
Customer Number Low
 
LOV Oracle
Customer Name High
 
LOV Oracle
Customer Name Low
 
LOV Oracle
Activity Name High
 
LOV Oracle
Activity Name Low
 
LOV Oracle
Order By
 
LOV Oracle