Reports2017-11-18T12:27:27+00:00

AP Supplier Statement

➜ Back to Library
Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Supplier Statement
Short Name: APTPSTMT
DB package: AP_TP_STMT_PKG 
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 /*Bug 14724500*/
      ,ai.payment_status_flag                      payment_status_flag
      ,TO_CHAR(ai.gl_date, 'DD-MON-YYYY')          gl_date /*Bug14724500*/
      ,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 /*Bug12561354*/
FROM   ap_invoices     ai
      ,gl_periods      gp
      ,gl_ledgers      gled
      ,ap_lookup_codes alc
WHERE  ai.vendor_id                = :vendor_id
  AND  ai.vendor_site_id           = :vendor_site_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              = 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.invoice_date BETWEEN :P_FROM_DOC_DATE AND :P_TO_DOC_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))
  &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 /*Bug14724500*/
      ,NULL                                            payment_status_flag
      ,TO_CHAR(aip.accounting_date, 'DD-MON-YYYY')     gl_date /*Bug14724500*/
      ,ac.currency_code                                currency_code
      ,aip.amount * NVL(aip.exchange_rate,1)           accounted_amount
      ,aip.amount                                      entered_amount
      ,ac.description                                  description /* Added for bug#9701882 */
      ,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 /*Bug12561354*/
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  ac.vendor_id               = :vendor_id
  AND  ac.vendor_site_id          = :vendor_site_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  ac.check_date       BETWEEN :P_FROM_DOC_DATE AND :P_TO_DOC_DATE
  AND  aip.accounting_date BETWEEN :P_FROM_GL_DATE AND :P_TO_GL_DATE
  &gc_pmt_accounted
  &gc_pmt_org_id
  &gc_pmt_currency
/* UNION ALL
SELECT 'A'                                    transaction_type      
      ,NULL                                   lookup_value
      ,'PA'                                   lookup_code
      ,aipre.invoice_num||'/'||ai.invoice_num doc_number
      ,ai.invoice_date                        doc_date
      ,NULL                                   payment_status_flag
      ,aid.accounting_date                    gl_date
      ,ai.invoice_currency_code               currency_code
      ,(((aid.amount)* NVL(ai.exchange_rate,1))*-1) accounted_amount_applied 
      ,((aid.amount)*-1) amount_applied
      ,TRIM(BOTH '/' FROM aipre.description||'/'||ai.description) description
      ,gp.period_name   gp_period_name
      ,gp.period_num    gp_period_num
      ,gp.start_date    gp_start_date
      ,NULL             posting_status
      ,NULL             approval_status
      ,NULL             validate_status
	  ,NULL             cancel_date
FROM   ap_invoices                  ai
      ,ap_invoice_distributions_all aid
      ,ap_invoices                  aipre
      ,ap_invoice_distributions_all aidpre
      ,gl_periods                   gp
      ,gl_ledgers                   gled
WHERE  ai.invoice_id              = aid.invoice_id
  AND  aid.prepay_distribution_id = aidpre.invoice_distribution_id
  AND  aipre.invoice_id           = aidpre.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             = aipre.set_of_books_id
  AND  aid.accounting_date BETWEEN gp.start_date AND gp.end_date
  AND  aid.accounting_date BETWEEN :P_FROM_DOC_DATE AND :P_TO_DOC_DATE
  AND  aid.accounting_date BETWEEN :P_FROM_GL_DATE AND :P_TO_GL_DATE
  AND  ai.vendor_id               = :vendor_id
  AND  ai.vendor_site_id          = :vendor_site_id
  AND  aid.line_type_lookup_code  = 'PREPAY'
  AND ai.wfapproval_status NOT IN ('CANCEL','CANCELLED')
  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))
  &gc_unapproved_trx
  &gc_currency
  &gc_org_id  */
ORDER BY 14,13,7,5
Parameter Name SQL text Validation
Reporting Context Name 
 
Accounted Transactions 
 
 LOV Oracle
Include Unapproved Transaction 
 
 LOV Oracle
Include Unvalidated Transactions 
 
 LOV Oracle
Vendor Type 
 
 LOV Oracle
Pay Group 
 
 LOV Oracle
Currency 
 
 LOV Oracle
Supplier Name To 
 
 LOV Oracle
Supplier Name From 
 
 LOV Oracle
GL Date To 
 
 Date
GL Date From 
 
 Date
Document Date To 
 
 Date
Document Date From 
 
 Date
Reporting Context 
 
 LOV Oracle
Reporting Level 
 
 LOV Oracle
Download
Related Blitz Reports
JA Third Party Detail Trial Balance with Contra Accounts
AP Withholding Tax by Invoice
AP Open Balances Revaluation
AP Turnover
AP Prepayment Balance Report - Functional Currency (CZech)
JA India - Cash/Bank Book - draft
AP Prepayment Balance Report - Entered Currency (CZech)
AP Prepayment Balance
AP Advances in Selected Currency

© 2019 Copyright by Enginatics GmbH

By continuing to use the site, you agree to the use of cookies. Accept