AR Disputed Invoice

Description
Categories: BI Publisher, Enginatics
Repository: Github
Imported Oracle standard disputed invoice report
Source: Disputed Invoice Report (XML)
Short Name: ARXDIR_XML
DB package: AR_ARXDIR_XMLP_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

select
 x.Company_Name
,x.Currency_Main  currency
,x.cons_number    billing_number
,x.Type
,x.Due_Date
,x.Customer_Name
,x.Customer_Number
,x.Collector
,x.Invoice_Amount
,x.Balance_Due
,x.Dispute_Amount
,x.notes
from
(
select
  sob.Company_Name,
  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(sob.functional_acct_unit, NULL, ROUND( ar_payment_schedules.amount_due_original * NVL(ar_payment_schedules.exchange_rate, 1), sob.Functional_Currency_Precision), ROUND( ( ar_payment_schedules.amount_due_original * NVL(ar_payment_schedules.exchange_rate, 1))/ sob.functional_acct_unit ) * sob.functional_acct_unit ) func_amount_due,
  ar_payment_schedules.acctd_amount_due_remaining func_amount_rem,
  DECODE(sob.functional_acct_unit, NULL, ROUND( ar_payment_schedules.amount_in_dispute * NVL(ar_payment_schedules.exchange_rate, 1), sob.Functional_Currency_Precision), ROUND( ( ar_payment_schedules.amount_in_dispute * NVL(ar_payment_schedules.exchange_rate, 1))/ sob.functional_acct_unit ) * sob.functional_acct_unit ) func_amount_dis,
  &lp_query_show_bill_mo 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_mo) cons_number,
  notes.text notes
from
  (SELECT sob.name  Company_Name,
          sob.set_of_books_id,
          sob.chart_of_accounts_id     coa_id,
          sob.currency_code            Functional_Currency,
          cur.precision                Functional_Currency_Precision,
          cur.minimum_accountable_unit functional_acct_unit
   FROM   gl_sets_of_books     sob,
          fnd_currencies       cur
    WHERE sob.currency_code = cur.currency_code
  ) sob,
  hr_operating_units hou,
  ar_payment_schedules_all ar_payment_schedules,
  ra_customer_trx_all,
  ra_cust_trx_types_all  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_mo ar_collectors,
  ( select distinct
        y.customer_trx_id
      , listagg(y.text,chr(10)) within group (order by y.text) over (partition by y.customer_trx_id) text
      from
        ( select
            arn.customer_trx_id
          , arn.text
          , sum(lengthb(arn.text)+1) over (partition by arn.customer_trx_id order by arn.note_id rows between unbounded preceding and current row) lengthb
          from
            ( select
                ar_notes.customer_trx_id
              , ar_notes.note_id
              , ar_notes.text
              from
                ar_notes
            ) arn
        ) y
      where
        y.lengthb <= 4000
    )    notes
where
      cust.party_id = party.party_id
  and ar_payment_schedules.customer_trx_id = ra_customer_trx_all.customer_trx_id
  and ra_customer_trx_all.set_of_books_id = sob.set_of_books_id
  and  ra_customer_trx_all.org_id = hou.organization_id
  and  hou.set_of_books_id = sob.set_of_books_id
  AND ar_payment_schedules.cust_trx_type_id = ra_cust_trx_types.cust_trx_type_id
  AND ar_payment_schedules.org_id = ra_cust_trx_types.org_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
  and ra_customer_trx_all.customer_trx_id = notes.customer_trx_id (+)
  and 1=1
  &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_mo
  &lp_order_by
) x
Parameter Name SQL text Validation
Operating Unit
hou.name=:operating_unit
LOV
Order By
 
LOV Oracle
Customer Name Low
 
LOV Oracle
Customer Name High
 
LOV Oracle
Customer Number Low
 
LOV Oracle
Customer Number High
 
LOV Oracle
Invoice Number Low
 
LOV Oracle
Invoice Number High
 
LOV Oracle
Due Date Low
 
Date
Due Date High
 
Date
Collector Low
 
LOV Oracle
Collector High
 
LOV Oracle
Invoice Status
ar_payment_schedules.status = decode(:p_invoice_status,'O','OP','CL')
LOV Oracle