SELECT
AI .INVOICE_ID,
AI.INVOICE_NUM,
TO_CHAR (
AI.INVOICE_AMOUNT
+ (SELECT NVL (SUM (amount), 0)
FROM ap_invoice_lines_All
WHERE invoice_id = AI.INVOICE_ID
AND LINE_TYPE_LOOKUP_CODE = ‘AWT’),
FND_CURRENCY_CACHE.GET_FORMAT_MASK (AI.INVOICE_CURRENCY_CODE, 30)
)
invoice_amount,
AI.INVOICE_DATE,
TO_CHAR (
NVL (AI.TAX_AMOUNT, 0),
FND_CURRENCY_CACHE.GET_FORMAT_MASK (AI.INVOICE_CURRENCY_CODE, 30)
)
TAX_AMOUNT,
AI.VENDOR_ID,
AI.INVOICE_CURRENCY_CODE,
AI.VENDOR_SITE_ID,
TO_CHAR (
NVL (AI.AMOUNT_PAID, 0),
FND_CURRENCY_CACHE.GET_FORMAT_MASK (AI.INVOICE_CURRENCY_CODE, 30)
)
AMOUNT_paid,
TO_CHAR (
NVL (AI.DISCOUNT_AMOUNT_TAKEN, 0),
FND_CURRENCY_CACHE.GET_FORMAT_MASK (AI.INVOICE_CURRENCY_CODE, 30)
)
DISCOUNT_AMOUNT_TAKEN,
AI.DESCRIPTION,
AI.CREATION_DATE,
” AS PO_HEADER_ID,
AI.APPROVAL_STATUS,
AI.ORG_ID,
AI.WFAPPROVAL_STATUS,
PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE,
PV.EMPLOYEE_ID,
PV.VENDOR_NAME,
PV.SEGMENT1 VENDOR_NUMBER,
AI.INVOICE_TYPE_LOOKUP_CODE,
AL.DISPLAYED_FIELD AS invoice_type_display,
AI.PAYMENT_STATUS_FLAG PAYMENT_STATUS,
” PO_NUMBER,
” REL_NUMBER,
” PAYMENT_NUMBER,
” receipt_number,
HOU.NAME AS ORG_NAME,
” PACKING_SLIP,
” AS hold_status,
” AS receipt_id,
” AS po_release_id,
” AS check_id,
CANCELLED_DATE,
” AS invoice_Status,
” due_Date,
DECODE (AI.PAYMENT_STATUS_FLAG,
‘Y’,
FND_MESSAGE_CACHE.GET_STRING (‘POS’, ‘POS_PAID’),
‘N’,
FND_MESSAGE_CACHE.GET_STRING (‘POS’, ‘POS_NOT_PAID’),
‘P’,
FND_MESSAGE_CACHE.GET_STRING (‘POS’, ‘POS_PARTIALLY_PAID’))
PAYMENT_STATUS_displayed,
” AS payment_Date,
ai.VOUCHER_NUM,
ai.invoice_amount
+ (SELECT NVL (SUM (amount), 0)
FROM ap_invoice_lines_All
WHERE invoice_id = AI.INVOICE_ID
AND LINE_TYPE_LOOKUP_CODE = ‘AWT’)
AS invoice_Amount_number,
AI.INVOICE_AMOUNT
– NVL (AI.AMOUNT_PAID, 0)
– NVL (AI.DISCOUNT_AMOUNT_TAKEN, 0)
+ (SELECT NVL (SUM (amount), 0)
FROM ap_invoice_lines_All
WHERE invoice_id = AI.INVOICE_ID
AND LINE_TYPE_LOOKUP_CODE = ‘AWT’)
AS due_Amount_number,
” AS bv_po_switch,
” AS bv_pay_switch,
” AS bv_receipt_switch,
” AS bv_pay_Date_switch,
” AS bv_hold_switch,
ai.approval_ready_flag,
ai.source,
FNBA.BATCH_CURRENCY RECKONING_CURRENCY,
FNAIA.NETTED_AMT NETTED_AMOUNT,
DECODE (
(FUN_NET_APAR_UTILS_GRP.GET_INVOICE_NETTED_STATUS (AI.INVOICE_ID)),
‘Y’,
‘BVRepEnabled’,
‘BVRepDisabled’
)
REPORT_SWITCHER,
APS.DISCOUNT_DATE DISCOUNT_DATE,
TO_CHAR (
APS.DISCOUNT_AMOUNT_AVAILABLE,
FND_CURRENCY_CACHE.GET_FORMAT_MASK (AI.INVOICE_CURRENCY_CODE, 30)
)
DISCOUNT_AMOUNT_AVAILABLE,
APS.REMIT_TO_SUPPLIER_NAME,
APS.REMIT_TO_SUPPLIER_SITE,
DECODE (
:1,
‘Y’,
POS_AP_INVOICES_PKG.GET_VALIDATION_STATUS (AI.ORG_ID,
AI.INVOICE_ID),
NULL
)
validation_status
FROM AP_INVOICES AI,
AP_PAYMENT_SCHEDULES_ALL APS,
HR_ALL_ORGANIZATION_UNITS_TL HOU,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVS,
AP_LOOKUP_CODES AL,
FUN_NET_BATCHES_ALL FNBA,
FUN_NET_AP_INVS_ALL FNAIA
WHERE APS.INVOICE_ID(+) = AI.INVOICE_ID
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND AI.VENDOR_ID = PV.VENDOR_ID
AND HOU.ORGANIZATION_ID(+) = AI.ORG_ID
AND HOU.LANGUAGE(+) = USERENV (‘LANG’)
AND AI.INVOICE_TYPE_LOOKUP_CODE = AL.LOOKUP_CODE(+)
AND AL.LOOKUP_TYPE = ‘INVOICE TYPE’
AND FNAIA.INVOICE_ID(+) = AI.INVOICE_ID
AND FNBA.BATCH_ID(+) = FNAIA.BATCH_ID
Recent Posts