AR Past Due Invoice (1)

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Past Due Invoice Report (XML)
Short Name: ARXPDI_XML
DB package: AR_ARXPDI_XMLP_PKG
select	ps.invoice_currency_code Currency_Code,
                DECODE(upper(:p_order_by),upper(:p_salesperson),srep.name, NULL) Salesrep,
                ctx.customer_trx_id Trx_Id,
                DECODE (upper(:p_order_by),upper(:p_balance_due),ps.due_amount,0) Balance_Due_Customer,
                CUST.CUST_ACCOUNT_ID	    Cust_Id ,
	SUBSTRB(PARTY.PARTY_NAME,1,50) Customer_Name,
	CUST.ACCOUNT_NUMBER Customer_Number,
       	ps.trx_number Invoice_Number,
	substr(arpt_sql_func_util.get_trx_type_details(ctx.cust_trx_type_id,'NAME'),1,4) Type,
	ctx.purchase_order Purchase_Order,
	ps.trx_date Invoice_Date,
	ps.due_date Due_Date,
	ps.amount_due_original Invoice_Amount,
                  nvl(ps.tax_original,0.00) Tax_Amount ,
	ps.due_amount                             Balance_Due,
	:rp_as_of_date - trunc(ps.due_date) Past_Days_Due,
                  &lp_query_show_bill cons_bill_number, 
	AR_ARXPDI_XMLP_PKG.c_data_foundformula(ps.invoice_currency_code) C_DATA_FOUND, 
	AR_ARXPDI_XMLP_PKG.c_temp_salformula(ps.invoice_currency_code,DECODE(upper(:p_order_by),upper(:p_salesperson),srep.name, NULL)) C_TEMP_SAL, 
	AR_ARXPDI_XMLP_PKG.c_custom_checkformula(ps.invoice_currency_code, CUST.CUST_ACCOUNT_ID) C_CUSTOM_CHECK,
	AR_ARXPDI_XMLP_PKG.RP_OLD_CURR_p RP_OLD_CURR,
	AR_ARXPDI_XMLP_PKG.RP_CURR_CHECK_p RP_CURR_CHECK,
	AR_ARXPDI_XMLP_PKG.RP_OLD_CUSTOMER_p RP_OLD_CUSTOMER,
	AR_ARXPDI_XMLP_PKG.RP_CUST_CHECK_p RP_CUST_CHECK,
	AR_ARXPDI_XMLP_PKG.invoice_number_consformula(ps.trx_number, ctx.trx_number) Invoice_Number_Cons
from 	HZ_customer_profiles      cp, 
     	HZ_customer_profiles      cp1, 
                  ar_collectors                    col, 
                  HZ_CUST_ACCOUNTS                   CUST, 
	HZ_PARTIES PARTY,
	ra_customer_trx              ctx, 
	ra_salesreps                    srep, 
                  &lp_table_show_bill
(              select 
                        arpt_sql_func_util.get_balance_due_as_of_date(payment_schedule_id,:p_as_of_date,class)     due_amount,
                          invoice_currency_code,
                         amount_due_remaining,
                          payment_schedule_id,
                          trx_date,
                          trx_number,
                          status,
                          due_date,
                          actual_date_closed,
                          amount_due_original,
                          tax_original  ,
                          customer_site_use_id  ,
                          customer_trx_id            ,
                          cons_inv_id
             from      ar_payment_schedules )   ps
 where   CUST.CUST_ACCOUNT_ID             = ctx.bill_to_customer_id
and       CUST.PARTY_ID  =      PARTY.PARTY_ID
and       ctx.customer_trx_id         = ps.customer_trx_id
and       ctx.primary_salesrep_id  = srep.salesrep_id (+)
and       cp.CUST_ACCOUNT_ID     = CUST.CUST_ACCOUNT_ID
and       cp.site_use_id is null
and       cp1.site_use_id (+)          = ps.customer_site_use_id
and       col.collector_id               = NVL(cp1.collector_id, cp.collector_id)
and      ps.actual_date_closed  > :rp_as_of_date
and       ps.due_amount != 0
&lp_collector_low
&lp_collector_high
&lp_customer_name_low
&lp_customer_name_high
&lp_customer_number_low
&lp_customer_number_high
&lp_amount_low
&lp_amount_high
&lp_past_days_due_low
&lp_past_days_due_high
&lp_invoice_type_low
&lp_invoice_type_high
&lp_salesrep_low
&lp_salesrep_high
&lp_where_show_bill 
&lp_order_by
Parameter Name SQL text Validation
Salesperson/Agent High
 
LOV Oracle
Salesperson/Agent Low
 
LOV Oracle
Type High
 
LOV Oracle
Type Low
 
LOV Oracle
Customer Number High
 
LOV Oracle
Customer Number Low
 
LOV Oracle
Customer Name High
 
LOV Oracle
Customer Name Low
 
LOV Oracle
Collector High
 
LOV Oracle
Collector Low
 
LOV Oracle
Balance Due High
 
Number
Balance Due Low
 
Number
Days Late High
 
Number
Days Late Low
 
Number
As of Date
 
Date
Order By
 
LOV Oracle