AP Accounts Payable Negative Supplier Balance

Description
Categories: BI Publisher, Enginatics
Repository: Github
Application: Payables
Source: Accounts Payable Negative Supplier Balance
Short Name: APXNVBAL
DB package: XLA_TB_AP_REPORT_PVT
select 
 x.account liability_account
,x.third_party_name supplier_name
&sum_or_detail_cols
from
 (select
    xtb.account
  ,xtb.third_party_name
  ,xtb.user_trx_identifier_value_3
  ,xtb.user_trx_identifier_value_10
  ,xtb.user_trx_identifier_value_8
  ,xtb.ledger_currency_code
  ,xtb.src_acctd_rounded_orig_amt
  ,xtb.src_acctd_rounded_rem_amt
  ,sum(xtb.src_acctd_rounded_rem_amt) over(partition by xtb.account,xtb.third_party_name)  sum_supp_liability
  from 
    (       &p_sql_statement
     and :l_run_detail_report = 'Y'
    ) xtb
 ) x
where
      1=1
and x.sum_supp_liability < 0
&summary_group_by
Parameter Name SQL text Validation
Account From
 
Char
Account To
 
Char
Custom Parameter 3
 
Char
Include Write Offs
 
LOV Oracle
Security
 
Char
Security Identifier (Character)
 
Char
Show Transaction Detail 1
 
LOV Oracle
Start Date
 
Date
Valuation Method
 
Char
XLA_SRS_SUBLEDGERS
 
LOV Oracle
Journal Source
 
LOV Oracle
Chart of Accounts
 
Number
Account Balance
 
LOV Oracle
Summarize Balances
 
LOV Oracle
Operating Unit
 
LOV Oracle
Report Definition
 
LOV Oracle
As of Date
 
Date
Third Party Name
 
LOV Oracle
Show Transaction Detail
 
LOV Oracle
Show Transaction Detail
,sum(x.src_acctd_rounded_orig_amt) amount
,sum(x.src_acctd_rounded_rem_amt) remaining_amount
Show Transaction Detail
,x.user_trx_identifier_value_3 invoice_number
,x.user_trx_identifier_value_10 invoice_description
,substr(x.user_trx_identifier_value_8,1,10) invoice_date
,x.ledger_currency_code currency
,x.src_acctd_rounded_orig_amt amount
,x.src_acctd_rounded_rem_amt remaining_amount
Show Transaction Detail
group by
 x.account
,x.third_party_name
order by
  1,2
Show Transaction Detail
order by
  1,2,3,5