AP Supplier Statement

Description
Categories: Enginatics
Repository: Github
Application: Payables
Source: Supplier Statement
Short Name: APTPSTMT
DB package: AP_TP_STMT_PKG
Run AP Supplier Statement and other Oracle EBS reports with Blitz Report™ on our demo environment
with q_supplier as (SELECT 
   asup.segment1 vendor_number,
   asup.vendor_name vendor_name,
   asup.vendor_name_alt vendor_name_alt,
   asup.vendor_id vendor_id,
   ass.vendor_site_id vendor_site_id,
   ass.vendor_site_code vendor_site_code,
   ass.vendor_site_code_alt vendor_site_code_alt,
   ass.address_line1 ||' '|| ass.address_line2 ||' '|| ass.address_line3 ||' '|| ass.city ||' '|| ass.state ||' '|| ass.zip vendor_site_address,
   ass.vat_registration_num vat_registration_num,
   hro.name organization_name,
   hro.organization_id organization_id,
   AP_TP_STMT_PKG.balance_brought_forward(ass.vendor_id,ass.vendor_site_id,ass.org_id) balance_brought_forward
  FROM 
   ap_suppliers asup,
   ap_supplier_sites_all ass,
   hr_operating_units hro
  WHERE
   asup.vendor_id = ass.vendor_id
   AND ass.org_id = hro.organization_id
   AND asup.enabled_flag = 'Y' 
   &gc_reporting_entity 
   &gc_supplier_name 
   &gc_vend_type 
   &gc_pay_group 
   ),
q_main as (SELECT 
   'I' transaction_type,
   alc.displayed_field lookup_value,
   ai.invoice_type_lookup_code lookup_code,
   ai.invoice_num doc_number,
   TO_CHAR(ai.invoice_date, 'DD-MON-YYYY') doc_date,
   ai.payment_status_flag payment_status_flag,
   TO_CHAR(ai.gl_date, 'DD-MON-YYYY') gl_date,
   ai.invoice_currency_code currency_code,
   ai.invoice_amount * NVL(ai.exchange_rate,1) accounted_amount,
   ai.invoice_amount entered_amount,
   ai.description description,
   gp.period_name gp_period_name,
   gp.period_num gp_period_num,
   gp.start_date gp_start_date,
   AP_INVOICES_PKG.get_posting_status (ai.invoice_id) posting_status,
   AP_INVOICES_PKG.get_wfapproval_status(ai.invoice_id,ai.org_id) approval_status,
   AP_TP_STMT_PKG.invoice_validate_status(ai.invoice_id) validate_status,
   fnd_date.date_to_chardate(ai.cancelled_date) cancel_date,
   ai.vendor_id,
   ai.vendor_site_id,
   'I' || ai.invoice_id id
  FROM 
   ap_invoices ai,
   gl_periods gp,
   gl_ledgers gled,
   ap_lookup_codes alc
  WHERE
   gled.period_set_name = gp.period_set_name
   AND gled.accounted_period_type = gp.period_type
   AND gp.adjustment_period_flag ='N'
   AND gled.ledger_id = ai.set_of_books_id
   AND ai.invoice_type_lookup_code = alc.lookup_code
   AND ai.invoice_type_lookup_code <> 'PREPAYMENT'
   AND alc.lookup_type = 'INVOICE TYPE'
   AND ai.gl_date BETWEEN gp.start_date AND gp.end_date
   AND ai.gl_date BETWEEN :P_FROM_GL_DATE AND :P_TO_GL_DATE
   AND AP_INVOICES_PKG.get_posting_status(ai.invoice_id) = DECODE(:P_ACCOUNTED,'ACCOUNTED','Y' ,'UNACCOUNTED','N' ,AP_INVOICES_PKG.get_posting_status(ai.invoice_id))
   and 1=1 
   &gc_unapproved_trx 
   &gc_currency 
   &gc_validate_inv 
   &gc_org_id 
  UNION ALL 
  SELECT 
   'P' transaction_type,
   alc.displayed_field lookup_value,
   ac.payment_method_lookup_code lookup_code,
   to_char(ac.check_number)||'/'||ai.invoice_num doc_number,
   TO_CHAR(ac.check_date, 'DD-MON-YYYY') doc_date,
   NULL payment_status_flag,
   TO_CHAR(aip.accounting_date, 'DD-MON-YYYY') gl_date,
   ac.currency_code currency_code,
   aip.amount * NVL(aip.exchange_rate,1) accounted_amount,
   aip.amount entered_amount,
   ac.description description ,
   gp.period_name gp_period_name,
   gp.period_num gp_period_num,
   gp.start_date gp_start_date,
   aip.accrual_posted_flag posting_status,
   alc1.displayed_field approval_status,
   NULL validate_status,
   NULL cancel_date,
   ac.vendor_id,
   ac.vendor_site_id,
   'P' || aip.invoice_payment_id id
  FROM 
   ap_invoices ai,
   ap_invoice_payments aip,
   ap_checks ac,
   gl_periods gp,
   gl_ledgers gled,
   ap_lookup_codes alc,
   ap_lookup_codes alc1
  WHERE
   aip.check_id = ac.check_id
   AND aip.invoice_id = ai.invoice_id
   AND gled.period_set_name = gp.period_set_name
   AND gled.accounted_period_type = gp.period_type
   AND gp.adjustment_period_flag ='N'
   AND gled.ledger_id = aip.set_of_books_id
   AND ac.payment_type_flag = alc.lookup_code
   AND alc.lookup_type = 'PAYMENT TYPE'
   AND alc1.lookup_type = 'CHECK STATE'
   AND ac.status_lookup_code = alc1.lookup_code
   AND aip.accounting_date BETWEEN gp.start_date AND gp.end_date
   AND aip.accounting_date BETWEEN :P_FROM_GL_DATE AND :P_TO_GL_DATE
   AND 2=2 
   &gc_pmt_accounted 
   &gc_pmt_org_id 
   &gc_pmt_currency 
   )
--
-- Main Query Start Here
--
select 
 :p_reporting_entity_name &reporting_entity_col_name ,
 q.Vendor_name supplier_name,
 q.Vendor_Number supplier_number,
 q.Vendor_Alternative_Name supplier_alt_name,
 q.Vendor_Tax_Registration supplier_tax_registration,
 q.Vendor_Site_Code supplier_site_code,
 q.Vendor_Site_Alternative_Code supplier_site_alt_code,
 q.vendor_site_address supplier_site_address, 
 &lp_operating_unit_column
 q.Period_Name,
 replace(q.Record_Type,'_',' ') record_type,
 q.balance_bought_forward_debit,
 q.balance_bought_forward_credit,
 q.net_debit,
 q.net_credit,
 q.cumulative_debit,
 q.cumulative_credit,
 q.balance_bought_forward_credit - q.balance_bought_forward_debit balance_bought_forward_amount,
 q.net_credit - q.net_debit net_amount,
 q.cumulative_credit - q.cumulative_debit cumualtive_amount
 &document_columns
from 
 (select 
   z.*,
   case z.record_type
    when 'Period Summary' then sum(nvl(z.Debit,0)) over (partition by z.vendor_site_id order by z.seq rows between unbounded preceding and current row) - sum(nvl(z.Debit,0)) over (partition by z.vendor_site_id,z.period_name)
    when 'Supplier Site Summary' then 0
    when 'Operating Unit Summary' then 0
    when 'Supplier Summary' then 0
    when '&reporting_entity_col_name Summary' then 0
    else null
   end balance_bought_forward_debit,
   case z.record_type
    when 'Period Summary' then sum(nvl(z.Credit,0)) over (partition by z.vendor_site_id order by z.seq rows between unbounded preceding and current row) + sum(nvl(z.bbf,0)) over (partition by z.vendor_site_id) - sum(nvl(z.Credit,0)) over (partition by z.vendor_site_id,z.period_name)
    when 'Supplier Site Summary' then sum(nvl(z.bbf,0)) over (partition by z.vendor_site_id)
    when 'Operating Unit Summary' then sum(nvl(z.bbf,0)) over (partition by z.vendor_id,z.organization_id)
    when 'Supplier Summary' then sum(nvl(z.bbf,0)) over (partition by z.vendor_id)
    when '&reporting_entity_col_name Summary' then sum(nvl(z.bbf,0)) over ()
    else null
   end balance_bought_forward_credit,
   case z.record_type
    when 'Period Summary' then sum(nvl(z.Debit,0)) over (partition by z.vendor_site_id,z.period_name)
    when 'Supplier Site Summary' then sum(nvl(z.Debit,0)) over (partition by z.vendor_site_id)
    when 'Operating Unit Summary' then sum(nvl(z.Debit,0)) over (partition by z.vendor_id,z.organization_id)
    when 'Supplier Summary' then sum(nvl(z.Debit,0)) over (partition by z.vendor_id)
    when '&reporting_entity_col_name Summary' then sum(nvl(z.Debit,0)) over ()
    else null
   end net_debit,
   case z.record_type
    when 'Period Summary' then sum(nvl(z.Credit,0)) over (partition by z.vendor_site_id,z.period_name)
    when 'Supplier Site Summary' then sum(nvl(z.Credit,0)) over (partition by z.vendor_site_id)
    when 'Operating Unit Summary' then sum(nvl(z.Credit,0)) over (partition by z.vendor_id,z.organization_id)
    when 'Supplier Summary' then sum(nvl(z.Credit,0)) over (partition by z.vendor_id)
    when '&reporting_entity_col_name Summary' then sum(nvl(z.Credit,0)) over ()
    else null
   end net_credit,
   case z.record_type
    when 'Period Summary' then sum(nvl(z.Debit,0)) over (partition by z.vendor_site_id order by z.seq rows between unbounded preceding and current row)
    when 'Supplier Site Summary' then sum(nvl(z.Debit,0)) over (partition by z.vendor_site_id order by z.seq rows between unbounded preceding and current row)
    when 'Operating Unit Summary' then sum(nvl(z.Debit,0)) over (partition by z.vendor_id,z.organization_id order by z.seq rows between unbounded preceding and current row)
    when 'Supplier Summary' then sum(nvl(z.Debit,0)) over (partition by z.vendor_id order by z.seq rows between unbounded preceding and current row)
    when '&reporting_entity_col_name Summary' then sum(nvl(z.Debit,0)) over ()
    else null
   end cumulative_debit,
   case z.record_type
    when 'Period Summary' then sum(nvl(z.Credit,0)) over (partition by z.vendor_site_id order by z.seq rows between unbounded preceding and current row) + sum(nvl(z.bbf,0)) over (partition by z.vendor_site_id)
    when 'Supplier Site Summary' then sum(nvl(z.Credit,0)) over (partition by z.vendor_site_id order by z.seq rows between unbounded preceding and current row) + sum(nvl(z.bbf,0)) over (partition by z.vendor_site_id)
    when 'Operating Unit Summary' then sum(nvl(z.Credit,0)) over (partition by z.vendor_id,z.organization_id order by z.seq rows between unbounded preceding and current row) + sum(nvl(z.bbf,0)) over (partition by z.vendor_id,z.organization_id)
    when 'Supplier Summary' then sum(nvl(z.Credit,0)) over (partition by z.vendor_id order by z.seq rows between unbounded preceding and current row) + sum(nvl(z.bbf,0)) over (partition by z.vendor_id)
    when '&reporting_entity_col_name Summary' then sum(nvl(z.Credit,0)) over () + sum(nvl(z.bbf,0)) over ()
    else null
   end cumulative_credit
  from 
   (select 
     rownum seq,
     y.*
    from 
     (select 
       x.*
      from 
       (-- dummy period summary record
         select distinct 
         'Period Summary' Record_Type,
         q_supplier.vendor_name Vendor_name,
         q_supplier.vendor_number Vendor_Number,
         q_supplier.vendor_name_alt Vendor_Alternative_Name,
         q_supplier.vat_registration_num Vendor_Tax_Registration,
         q_supplier.organization_name Operating_Unit,
         q_supplier.vendor_site_code Vendor_Site_Code,
         q_supplier.vendor_site_code_alt Vendor_Site_Alternative_Code,
         q_supplier.vendor_site_address vendor_site_address,
         q_main.gp_period_name Period_Name,
         null Document_Type,
         null Document_Number,
         null Document_Date,
         null Document_Status,
         null GL_Date,
         null Description,
         to_number(null) Debit,
         to_number(null) Credit,
         to_number(null) Original_Amount,
         null Currency_Code,
         1 sort_order,
         q_main.gp_start_date period_start_date,
         q_main.gp_period_num period_num,
         q_supplier.vendor_id,
         q_supplier.vendor_site_id,
         q_supplier.organization_id,
         to_number(null) bbf
        from 
         q_supplier,
         q_main
        where
         q_main.vendor_id = q_supplier.vendor_id
         and q_main.vendor_site_id = q_supplier.vendor_site_id 
        union all 
        -- dummy supplier site summary record
        select distinct 
         'Supplier Site Summary' Record_Type,
         q_supplier.vendor_name Vendor_name,
         q_supplier.vendor_number Vendor_Number,
         q_supplier.vendor_name_alt Vendor_Alternative_Name,
         q_supplier.vat_registration_num Vendor_Tax_Registration,
         q_supplier.organization_name Operating_Unit,
         q_supplier.vendor_site_code Vendor_Site_Code,
         q_supplier.vendor_site_code_alt Vendor_Site_Alternative_Code,
         q_supplier.vendor_site_address vendor_site_address,
         null Period_Name,
         null Document_Type,
         null Document_Number,
         null Document_Date,
         null Document_Status,
         null GL_Date,
         null Description,
         to_number(null) Debit,
         to_number(null) Credit,
         to_number(null) Original_Amount,
         null Currency_Code,
         2 sort_order,
         to_date(null) period_start_date,
         to_number(null) period_num,
         q_supplier.vendor_id,
         q_supplier.vendor_site_id,
         q_supplier.organization_id,
         q_supplier.balance_brought_forward bbf
        from 
         q_supplier
        union all 
        -- dummy operating unit summary record
        select distinct 
         'Operating Unit Summary' Record_Type,
         q_supplier.vendor_name Vendor_name,
         q_supplier.vendor_number Vendor_Number,
         q_supplier.vendor_name_alt Vendor_Alternative_Name,
         null Vendor_Tax_Registration,
         q_supplier.organization_name Operating_Unit,
         null Vendor_Site_Code,
         null Vendor_Site_Alternative_Code,
         null vendor_site_address,
         null Period_Name,
         null Document_Type,
         null Document_Number,
         null Document_Date,
         null Document_Status,
         null GL_Date,
         null Description,
         to_number(null) Debit,
         to_number(null) Credit,
         to_number(null) Original_Amount,
         null Currency_Code,
         3 sort_order,
         to_date(null) period_start_date,
         to_number(null) period_num,
         q_supplier.vendor_id,
         to_number(null) vendor_site_id,
         q_supplier.organization_id,
         to_number(null) bbf
        from 
         q_supplier
        where
         :p_reporting_level != '3000' -- don't show OU Summary when run by OU
        union all 
        -- dummy supplier summary record
        select distinct 
         'Supplier Summary' Record_Type,
         q_supplier.vendor_name Vendor_name,
         q_supplier.vendor_number Vendor_Number,
         q_supplier.vendor_name_alt Vendor_Alternative_Name,
         null Vendor_Tax_Registration,
         null Operating_Unit,
         null Vendor_Site_Code,
         null Vendor_Site_Alternative_Code,
         null vendor_site_address,
         null Period_Name,
         null Document_Type,
         null Document_Number,
         null Document_Date,
         null Document_Status,
         null GL_Date,
         null Description,
         to_number(null) Debit,
         to_number(null) Credit,
         to_number(null) Original_Amount,
         null Currency_Code,
         4 sort_order,
         to_date(null) period_start_date,
         to_number(null) period_num,
         q_supplier.vendor_id,
         to_number(null) vendor_site_id,
         to_number(null) organization_id,
         to_number(null) bbf
        from 
         q_supplier
        union all 
        -- dummy reporting entity summary record
        select distinct 
         '&reporting_entity_col_name Summary' Record_Type,
         null Vendor_name,
         null Vendor_Number,
         null Vendor_Alternative_Name,
         null Vendor_Tax_Registration,
         null Operating_Unit,
         null Vendor_Site_Code,
         null Vendor_Site_Alternative_Code,
         null vendor_site_address,
         null Period_Name,
         null Document_Type,
         null Document_Number,
         null Document_Date,
         null Document_Status,
         null GL_Date,
         null Description,
         to_number(null) Debit,
         to_number(null) Credit,
         to_number(null) Original_Amount,
         null Currency_Code,
         5 sort_order,
         to_date(null) period_start_date,
         to_number(null) period_num,
         to_number(null) vendor_id,
         to_number(null) vendor_site_id,
         to_number(null) organization_id,
         to_number(null) bbf
        from 
         dual
        union all 
        -- transactions
        select 
         'Transaction' Record_Type,
         q_supplier.vendor_name Vendor_name,
         q_supplier.vendor_number Vendor_Number,
         q_supplier.vendor_name_alt Vendor_Alternative_Name,
         q_supplier.vat_registration_num Vendor_Tax_Registration,
         q_supplier.organization_name Operating_Unit,
         q_supplier.vendor_site_code Vendor_Site_Code,
         q_supplier.vendor_site_code_alt Vendor_Site_Alternative_Code,
         q_supplier.vendor_site_address vendor_site_address,
         q_main.gp_period_name Period_Name,
         case q_main.transaction_type
          when 'A' then 'Prepayment Application'
          else q_main.lookup_value
         end Document_Type,
         q_main.doc_number Document_Number,
         q_main.doc_date Document_Date,
         nvl2(q_main.cancel_date,'Cancelled/ ',null) || case q_main.validate_status
          when 'Y' then 'Validated/ '
          when 'N' then 'Un-Validated/ '
          else null
         end ||case q_main.posting_status
          when 'Y' then 'Accounted'
          when 'N' then 'Not Accounted'
          else null
         end ||case q_main.lookup_code
          when 'PA' then '/ Applied'
          else null
         end Document_Status,
         q_main.gl_date GL_Date,
         q_main.description Description,
         case q_main.transaction_type
          when 'I' then to_number(null)
          else q_main.accounted_amount
         end Debit,
         case q_main.transaction_type
          when 'I' then q_main.accounted_amount
          else to_number(null)
         end Credit,
         q_main.entered_amount Original_Amount,
         q_main.currency_code Currency_Code,
         -1 sort_order,
         q_main.gp_start_date period_start_date,
         q_main.gp_period_num period_num,
         q_supplier.vendor_id,
         q_supplier.vendor_site_id,
         q_supplier.organization_id,
         to_number(null) bbf
        from 
         q_supplier,
         q_main
        where
         q_main.vendor_id = q_supplier.vendor_id
         and q_main.vendor_site_id = q_supplier.vendor_site_id ) x
      order by 
       x.vendor_name,
       x.vendor_number,
       x.operating_unit,
       x.vendor_site_code,
       nvl(x.period_start_date,to_date('31/12/4712','DD/MM/YYYY')),
       nvl(x.period_num,99),
       nvl(x.period_name,'####'),
       x.sort_order,
       x.gl_date,
       x.document_date) y) z
  order by 
   z.seq) q
where
 (   (:p_summary_only = 'N' and q.record_type = 'Transaction') 
  or (:p_summary_level = 'Period' and q.record_type not in ('Transaction')) 
  or (:p_summary_level = 'Supplier Site' and q.record_type not in ('Period Summary','Transaction')) 
  or (:p_summary_level = 'Operating Unit' and q.record_type not in ('Supplier Site Summary','Period Summary','Transaction')) 
  or (:p_summary_level = 'Supplier' and q.record_type = 'Supplier Summary') 
  or (:p_reporting_level = '3000' and q.record_type = 'Operating_Unit Summary') 
  or (:p_reporting_level = '1000' and q.record_type = 'Ledger Summary') 
 ) 
order by 
 q.seq
Parameter Name SQL text Validation
Reporting Level
 
LOV Oracle
Reporting Context
 
LOV Oracle
GL Date From
 
Date
GL Date To
 
Date
Document Date From
 
Date
Document Date To
 
Date
Supplier Name From
 
LOV Oracle
Supplier Name To
 
LOV Oracle
Currency
 
LOV Oracle
Pay Group