AR Disputed Invoice

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Disputed Invoice Report (XML)
Short Name: ARXDIR_XML
DB package: AR_ARXDIR_XMLP_PKG
select
        ar_payment_schedules.invoice_currency_code   Currency_Main,
	        DECODE(UPPER(:p_order_by),'CUSTOMER',CUST.ACCOUNT_NUMBER,NULL) Dummy_Customer_Number,
	      DECODE(UPPER(:p_order_by),'CUSTOMER',SUBSTRB(PARTY.PARTY_NAME,1,50),NULL) Dummy_Customer_Name,
                         ar_payment_schedules.trx_number Number_,
	        ra_cust_trx_types.name          Type ,
	        ar_payment_schedules.due_date                Due_Date,
	        SUBSTRB(PARTY.PARTY_NAME,1,50)     Customer_Name ,
	       CUST.ACCOUNT_NUMBER    Customer_Number,
    	       CUST.CUST_ACCOUNT_ID                    Customer_Id ,
	        ar_collectors.name             Collector   ,
	        ar_payment_schedules.amount_due_original     Invoice_Amount,
	        ar_payment_schedules.amount_due_remaining    Balance_Due ,
	        ar_payment_schedules.amount_in_dispute       Dispute_Amount,
	        ar_payment_schedules.customer_trx_id         Customer_Trx_Id,
                DECODE(:functional_acct_unit, NULL,
                ROUND( ar_payment_schedules.amount_due_original *
                         NVL(ar_payment_schedules.exchange_rate, 1),
                         :Functional_Currency_Preci),
                ROUND(  ( ar_payment_schedules.amount_due_original *
                          NVL(ar_payment_schedules.exchange_rate, 1))/
                          :functional_acct_unit
                       ) * :functional_acct_unit
                       ) func_amount_due,
        ar_payment_schedules.acctd_amount_due_remaining func_amount_rem,
                DECODE(:functional_acct_unit, NULL,
                 ROUND( ar_payment_schedules.amount_in_dispute *
                         NVL(ar_payment_schedules.exchange_rate, 1),
                         :Functional_Currency_Preci),
                  ROUND(  ( ar_payment_schedules.amount_in_dispute *
                          NVL(ar_payment_schedules.exchange_rate, 1))/
                          :functional_acct_unit
                       ) * :functional_acct_unit
                       ) func_amount_dis,
     &lp_query_show_bill cons_bill_number,
	AR_ARXDIR_XMLP_PKG.c_data_not_foundformula(ar_payment_schedules.invoice_currency_code) C_DATA_NOT_FOUND,
	AR_ARXDIR_XMLP_PKG.c_currency_summary_labelformul(ar_payment_schedules.invoice_currency_code) C_CURRENCY_SUMMARY_LABEL,
	AR_ARXDIR_XMLP_PKG.c_cust_summary_labelformula(DECODE ( UPPER ( :p_order_by ) , 'CUSTOMER' , SUBSTRB ( PARTY.PARTY_NAME , 1 , 50 ) , NULL )) C_CUST_SUMMARY_LABEL,
	AR_ARXDIR_XMLP_PKG.cons_numberformula(ar_payment_schedules.trx_number, &lp_query_show_bill) CONS_NUMBER
from
  	        ar_payment_schedules,
	        ra_cust_trx_types,
	       HZ_CUST_ACCOUNTS CUST,
	       HZ_PARTIES PARTY,
	        HZ_customer_profiles cust_cp,
	        HZ_customer_profiles site_cp,
                          &lp_table_show_bill
	        ar_collectors
	where
	        CUST.PARTY_ID = PARTY.PARTY_ID
	AND  ar_payment_schedules.cust_trx_type_id  = ra_cust_trx_types.cust_trx_type_id
	 and ar_payment_schedules.customer_id            = CUST.CUST_ACCOUNT_ID
	 and  CUST.CUST_ACCOUNT_ID                         = cust_cp.CUST_ACCOUNT_ID
   	 and  cust_cp.site_use_id is null
	 and ar_payment_schedules.customer_site_use_id = site_cp.site_use_id(+)
                    and nvl(site_cp.collector_id,cust_cp.collector_id) = ar_collectors.collector_id
	  and ar_payment_schedules.amount_in_dispute != 0
	  &lp_due_date_low
         	  &lp_due_date_high
	  &lp_item_number_low
	  &lp_item_number_high
	  &lp_customer_name_low
	  &lp_customer_name_high
	  &lp_customer_number_low
	  &lp_customer_number_high
	  &lp_collector_low
	  &lp_collector_high
                     &lp_where_show_bill
                    &lp_status
                     &lp_order_by
Parameter Name SQL text Validation
Ledger ID
 
LOV Oracle
Invoice Status
 
LOV Oracle
Collector High
 
LOV Oracle
Collector Low
 
LOV Oracle
Due Date High
 
Date
Due Date Low
 
Date
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
Order By
 
LOV Oracle