AP Supplier Statement
Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Supplier Statement
Short Name: APTPSTMT
DB package: AP_TP_STMT_PKG
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 |
