AP Supplier Statement

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 NameSQL textValidation
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