Oracle EBS AP Open Invoice

Oracle EBS AP Open Invoice

Tables:-
AP_INVOICES_ALL
AP_INVOICE_LINES_ALL
AP_SUPPLIERS
AP_SUPPLIER_SITES_ALL
AP_TERMS_TL
AP_PAYMENT_SCHEDULES_ALL

SQL Example:-
AP Open Invoice - Header

SELECT
g.name OPERATING_UNIT
,b.invoice_num INVOICE_NUM
,b.invoice_type_lookup_code INVOICE_TYPE
,b.invoice_date INVOICE_DATE
,b.gl_date GL_DATE
,e.vendor_name VENDOR_NAME
,f.vendor_site_code VENDOR_SITE_CODE
,h.name PAY_TERMS_NAME
,b.terms_date PAY_TERMS_DATE
,b.payment_method_code PAYMENT_METHOD
,b.invoice_amount INVOICE_AMOUNT
,b.invoice_currency_code INVOICE_CURRENCY_CODE
,b.exchange_rate EXCHANGE_RATE
,(SELECT gdct.user_conversion_type FROM APPS.gl_daily_conversion_types gdct
WHERE gdct.conversion_type=b.exchange_rate_type AND rownum=1) " EXCHANGE_RATE_TYPE"
,b.exchange_date EXCHANGE_DATE
,b.payment_currency_code PAYMENT_CURRENCY_CODE
,b.description DESCRIPTION
,a.concatenated_segments LIABILITY_ACCOUNT
,b.attribute_category ATTRIBUTE_CATEGORY
,(select SUM(AMOUNT) FROM APPS.AP_INVOICE_LINES_ALL ail WHERE ail.invoice_id=b.invoice_id
AND ail.line_type_lookup_code IN ('AWT')) "WHT_AMOUNT"
,b.DOCUMENT_SUB_TYPE INVOICE_SUB_TYPE
,APS.DUE_DATE "Invoice Due Date"
FROM
APPS.AP_INVOICES_ALL b
,apps.ap_invoice_lines_all    ail
,APPS.AP_SUPPLIERS e
,APPS.AP_SUPPLIER_SITES_ALL f
,APPS.HR_OPERATING_UNITS g
,APPS.AP_TERMS_TL h
,APPS.AP_PAYMENT_SCHEDULES_ALL aps
,APPS.GL_CODE_COMBINATIONS_KFV a
,APPS.xle_entity_profiles     xep
WHERE 1=1
AND a.code_combination_id = b.accts_pay_code_combination_id
 AND b.vendor_id = e.vendor_id
 AND b.org_id = g.organization_id
 AND e.vendor_id = f.vendor_id
 AND b.vendor_site_id(+) = f.vendor_site_id
 AND b.terms_id = h.term_id
 AND h.enabled_flag   ='Y'
 AND h.language  ='US'
 AND b.payment_status_flag in ('N','P')
AND APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS(b.invoice_id, b.invoice_amount, b.payment_status_flag, b.invoice_type_lookup_code)!='CANCELLED'
 AND APPS.AP_INVOICES_PKG.GET_POSTING_STATUS(b.invoice_id)in ('Y','P','S')
AND b.INVOICE_ID=aps.INVOICE_ID(+)
AND ail.invoice_id =b.invoice_id(+)
AND g.default_legal_context_id = xep.legal_entity_id
AND b.legal_entity_id=xep.legal_entity_id
AND g.name='&&';

SQL Example:-
AP Open Invoice - Line

SELECT
a.name line_ou_name
,b.invoice_num line_invoice_num
,c.line_number
,c.line_type_lookup_code "Line Type"
,c.amount "Distribution Line Amount"
,d.concatenated_segments distribution_account
,c.description Description
,e.tax_status_code "TAX CLASSIFICATION CODE"
,e.tax_regime_code "TAX REGIME CODE"
,e.tax TAX
,e.tax_jurisdiction_code JURDICTION
,e.tax_status_code tax_status
,e.tax_rate_code "RATE NAME"
,e.tax_rate RATE
,b.attribute_category "Global Data Elements"
,c.tax_classification_code LINE_TAX_CODE
,g.vendor_name
,ail.PO_NUMBER "PO Number"
,ail.PO_RELEASE_NUMBER "PO Release"
,ail.PO_LINE_NUMBER "PO Line"
,ail.PO_SHIPMENT_NUMBER "PO Shipment"
,ail.MATCHING_BASIS "Match Basis"
,ail.PO_DISTRIBUTION_NUMBER "PO Distribution"
,ail.RECEIPT_NUMBER "Receipt Number"
,ail.RECEIPT_LINE_NUMBER "Receipt Line Number"
,ail.QUANTITY_INVOICED "Quantity Invoiced"
,ail.UNIT_MEAS_LOOKUP_CODE UOM
,ail.UNIT_PRICE "Unit Price"
FROM
APPS.HR_OPERATING_UNITS a
 ,APPS.AP_INVOICES_ALL b
 ,APPS.ap_invoice_lines_all c
 ,APPS.AP_INVOICE_LINES_V ail
 ,APPS.GL_CODE_COMBINATIONS_KFV d
 ,APPS.zx_lines_summary e
 ,APPS.AP_SUPPLIERS g
 ,APPS.GL_CODE_COMBINATIONS_KFV h
 WHERE 1=1
 AND b.invoice_id = c.invoice_id
 AND b.org_id = a.organization_id
 AND b.accts_pay_code_combination_id=d.code_combination_id(+)
 AND c.default_dist_ccid = d.code_combination_id(+)
 AND c.invoice_id =e.trx_id(+)
 AND b.vendor_id = g.vendor_id AND b.payment_status_flag in ('N','P')
 AND b.invoice_num=e.TRX_NUMBER(+)
 AND b.ORG_ID=e.INTERNAL_ORGANIZATION_ID(+)
 AND h.code_combination_id = b.accts_pay_code_combination_id
 AND APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS(b.invoice_id, b.invoice_amount, b.payment_status_flag, b.invoice_type_lookup_code)!='CANCELLED'
 AND APPS.AP_INVOICES_PKG.GET_POSTING_STATUS(b.invoice_id)in ('Y','P','S')
 AND c.org_id=ail.org_id
 AND c.invoice_id=ail.invoice_id
 AND c.line_number=ail.line_number
 AND e.CANCEL_FLAG is null
AND a.name='&&';