AP_SLA_INVOICES_TRANSACTION_V

Details

• Schema: FUSION

• Object owner: AP

• Object type: VIEW

Columns

Name
INVOICE_ID PARTY_NAME PARTY_SITE_NAME INVOICE_NUM ORIGINAL_INVOICE_AMOUNT INVOICE_AMOUNT INVOICE_CURRENCY_CODE PAYMENT_STATUS BASE_AMOUNT INVOICE_TYPE_LOOKUP_CODE INVOICE_DATE CANCELLED_DATE DESCRIPTION TERMS_DATE DUE_DAYS AMOUNT_REMAINING

Query

SQL_Statement

SELECT AI.invoice_id, HZP.party_name, HZPS.party_site_name, AI.invoice_num, DECODE(AI.cancelled_date,NULL,AI.invoice_amount,AI.cancelled_amount) original_invoice_amount, AI.invoice_amount, AI.invoice_currency_code, ALC.DISPLAYED_FIELD PAYMENT_STATUS, /* Bug 7039815 */ NVL(AI.base_amount,AI.invoice_amount) base_amount, /*bug 8770808*/ AI.invoice_type_lookup_code, AI.invoice_date, AI.cancelled_date, AI.description, AI.terms_date, (SELECT atl.due_days FROM AP_TERMS_LINES ATL WHERE AI.terms_id = ATL.term_id AND ROWNUM = 1) due_days, DECODE(AI.payment_status_flag,'Y',0,(select sum(aps.amount_remaining) from AP_PAYMENT_SCHEDULES_ALL aps where aps.invoice_id = ai.invoice_id )) Amount_Remaining from AP_INVOICES_ALL AI ,HZ_PARTIES HZP ,HZ_PARTY_SITES HZPS ,POZ_SUPPLIERS_V AS1 ,AP_LOOKUP_CODES ALC /* Bug 7039815 */ where AI.vendor_id = AS1.vendor_id (+) /* Bug7019211, Bug6150175 */ and AI.party_id = HZP.party_id and AI.party_site_id = HZPS.party_site_id(+) AND ALC.LOOKUP_TYPE (+) = 'INVOICE PAYMENT STATUS' /* Bug 7039815 */ AND ALC.LOOKUP_CODE (+) = AI.PAYMENT_STATUS_FLAG