AP_APPLY_PREPAYS

Details

Schema: FUSION
Object owner: AP
Object type: VIEW

Columns

Name
INVOICE_ID PREPAY_LINE_NUMBER PREPAY_AMOUNT_REMAINING TAX_AMOUNT_REMAINING AMOUNT ACCOUNTING_DATE PERIOD_NAME SET_OF_BOOKS_ID DESCRIPTION PO_LINE_LOCATION_ID PO_DISTRIBUTION_ID RCV_TRANSACTION_ID ORG_ID PREPAY_NUMBER INVOICE_DATE INVOICE_AMOUNT VENDOR_ID VENDOR_SITE_ID INVOICE_CURRENCY_CODE PAYMENT_CURRENCY_CODE PAYMENT_CROSS_RATE PO_HEADER_ID PO_NUMBER VENDOR_SITE_CODE EARLIEST_SETTLEMENT_DATE

Name

INVOICE_ID

PREPAY_LINE_NUMBER

PREPAY_AMOUNT_REMAINING

TAX_AMOUNT_REMAINING

AMOUNT

ACCOUNTING_DATE

PERIOD_NAME

SET_OF_BOOKS_ID

DESCRIPTION

PO_LINE_LOCATION_ID

PO_DISTRIBUTION_ID

RCV_TRANSACTION_ID

ORG_ID

PREPAY_NUMBER

INVOICE_DATE

INVOICE_AMOUNT

VENDOR_ID

VENDOR_SITE_ID

INVOICE_CURRENCY_CODE

PAYMENT_CURRENCY_CODE

PAYMENT_CROSS_RATE

PO_HEADER_ID

PO_NUMBER

VENDOR_SITE_CODE

EARLIEST_SETTLEMENT_DATE

Query

SQL_Statement
SELECT AIL.INVOICE_ID INVOICE_ID, AIL.LINE_NUMBER PREPAY_LINE_NUMBER, AP_PREPAY_UTILS_PKG.GET_LINE_PREPAY_AMT_REMAINING (AIL.INVOICE_ID, AIL.LINE_NUMBER) PREPAY_AMOUNT_REMAINING, NULLIF(AP_PREPAY_UTILS_PKG.GET_INC_TAX_PP_AMT_REMAINING (AIL.INVOICE_ID, AIL.LINE_NUMBER), 0) TAX_AMOUNT_REMAINING, AIL.AMOUNT AMOUNT, AIL.ACCOUNTING_DATE ACCOUNTING_DATE, AIL.PERIOD_NAME PERIOD_NAME, AIL.SET_OF_BOOKS_ID SET_OF_BOOKS_ID, AIL.DESCRIPTION DESCRIPTION, AIL.PO_LINE_LOCATION_ID PO_LINE_LOCATION_ID, AIL.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID, AIL.RCV_TRANSACTION_ID RCV_TRANSACTION_ID, AIL.ORG_ID ORG_ID, AI.INVOICE_NUM PREPAY_NUMBER, AI.INVOICE_DATE INVOICE_DATE, AI.INVOICE_AMOUNT INVOICE_AMOUNT, AI.VENDOR_ID VENDOR_ID, AI.VENDOR_SITE_ID VENDOR_SITE_ID, AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE, AI.PAYMENT_CURRENCY_CODE PAYMENT_CURRENCY_CODE, AI.PAYMENT_CROSS_RATE PAYMENT_CROSS_RATE, AI.PO_HEADER_ID PO_HEADER_ID, PH.SEGMENT1 PO_NUMBER, PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE, AI.EARLIEST_SETTLEMENT_DATE EARLIEST_SETTLEMENT_DATE FROM AP_INVOICES_ALL AI, AP_INVOICE_LINES_ALL AIL, POZ_SUPPLIER_SITES_ALL_M PVS, PO_HEADERS_ALL PH, PO_LINE_LOCATIONS_ALL PLL WHERE AI.INVOICE_ID = AIL.INVOICE_ID AND AIL.LINE_TYPE_LOOKUP_CODE = 'ITEM' AND AI.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT' AND AI.PAYMENT_STATUS_FLAG = 'Y' AND AI.VENDOR_ID = PVS.VENDOR_ID AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID AND trunc(AI.EARLIEST_SETTLEMENT_DATE) <= trunc(SYSDATE) AND AP_PREPAY_UTILS_PKG.GET_LINE_PREPAY_AMT_REMAINING (AIL.INVOICE_ID, AIL.LINE_NUMBER) > 0 AND NVL(AIL.DISCARDED_FLAG,'N') <> 'Y' AND NVL(AIL.LINE_SELECTED_FOR_APPL_FLAG,'N') <> 'Y' AND AIL.PREPAY_APPL_REQUEST_ID IS NULL AND AIL.PO_HEADER_ID = PH.PO_HEADER_ID (+) AND AIL.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID(+) AND NVL(PLL.SHIPMENT_TYPE,'DUMMY') <> 'PREPAYMENT' /Contract Payments/ AND AIL.INVOICE_ID IN (SELECT AIL.INVOICE_ID FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP WHERE FSP.ORG_ID = AIL.ORG_ID AND ((NVL(FSP.PURCH_ENCUMBRANCE_FLAG, 'N') = 'N' AND NOT EXISTS (SELECT MATCH_STATUS_FLAG FROM AP_INVOICE_DISTRIBUTIONS_ALL AID WHERE AID.INVOICE_ID = AIL.INVOICE_ID AND NVL(AID.MATCH_STATUS_FLAG, 'N') NOT IN ('T', 'A'))) OR (NVL(FSP.PURCH_ENCUMBRANCE_FLAG, 'N') = 'Y' AND NOT EXISTS (SELECT MATCH_STATUS_FLAG FROM AP_INVOICE_DISTRIBUTIONS_ALL AID1 WHERE AID1.INVOICE_ID = AIL.INVOICE_ID AND NVL(AID1.MATCH_STATUS_FLAG, 'N') <> 'A' )) ) ) AND NOT EXISTS (SELECT H.INVOICE_ID FROM AP_HOLDS H, AP_HOLD_CODES C WHERE H.INVOICE_ID = AIL.INVOICE_ID AND H.HOLD_LOOKUP_CODE = C.HOLD_LOOKUP_CODE AND ((H.RELEASE_LOOKUP_CODE IS NULL) AND (C.POSTABLE_FLAG = 'N' OR C.POSTABLE_FLAG = 'X')))

SQL_Statement

SELECT

AIL.INVOICE_ID INVOICE_ID,

AIL.LINE_NUMBER PREPAY_LINE_NUMBER,

AP_PREPAY_UTILS_PKG.GET_LINE_PREPAY_AMT_REMAINING

(AIL.INVOICE_ID,

AIL.LINE_NUMBER) PREPAY_AMOUNT_REMAINING,

NULLIF(AP_PREPAY_UTILS_PKG.GET_INC_TAX_PP_AMT_REMAINING

(AIL.INVOICE_ID,

AIL.LINE_NUMBER), 0) TAX_AMOUNT_REMAINING,

AIL.AMOUNT AMOUNT,

AIL.ACCOUNTING_DATE ACCOUNTING_DATE,

AIL.PERIOD_NAME PERIOD_NAME,

AIL.SET_OF_BOOKS_ID SET_OF_BOOKS_ID,

AIL.DESCRIPTION DESCRIPTION,

AIL.PO_LINE_LOCATION_ID PO_LINE_LOCATION_ID,

AIL.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID,

AIL.RCV_TRANSACTION_ID RCV_TRANSACTION_ID,

AIL.ORG_ID ORG_ID,

AI.INVOICE_NUM PREPAY_NUMBER,

AI.INVOICE_DATE INVOICE_DATE,

AI.INVOICE_AMOUNT INVOICE_AMOUNT,

AI.VENDOR_ID VENDOR_ID,

AI.VENDOR_SITE_ID VENDOR_SITE_ID,

AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE,

AI.PAYMENT_CURRENCY_CODE PAYMENT_CURRENCY_CODE,

AI.PAYMENT_CROSS_RATE PAYMENT_CROSS_RATE,

AI.PO_HEADER_ID PO_HEADER_ID,

PH.SEGMENT1 PO_NUMBER,

PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE,

AI.EARLIEST_SETTLEMENT_DATE EARLIEST_SETTLEMENT_DATE

FROM

AP_INVOICES_ALL AI,

AP_INVOICE_LINES_ALL AIL,

POZ_SUPPLIER_SITES_ALL_M PVS,

PO_HEADERS_ALL PH,

PO_LINE_LOCATIONS_ALL PLL

WHERE AI.INVOICE_ID = AIL.INVOICE_ID

AND AIL.LINE_TYPE_LOOKUP_CODE = 'ITEM'

AND AI.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'

AND AI.PAYMENT_STATUS_FLAG = 'Y'

AND AI.VENDOR_ID = PVS.VENDOR_ID

AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID

AND trunc(AI.EARLIEST_SETTLEMENT_DATE) <= trunc(SYSDATE)

AND AP_PREPAY_UTILS_PKG.GET_LINE_PREPAY_AMT_REMAINING

(AIL.INVOICE_ID,

AIL.LINE_NUMBER) > 0

AND NVL(AIL.DISCARDED_FLAG,'N') <> 'Y'

AND NVL(AIL.LINE_SELECTED_FOR_APPL_FLAG,'N') <> 'Y'

AND AIL.PREPAY_APPL_REQUEST_ID IS NULL

AND AIL.PO_HEADER_ID = PH.PO_HEADER_ID (+)

AND AIL.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID(+)

AND NVL(PLL.SHIPMENT_TYPE,'DUMMY') <> 'PREPAYMENT' /*Contract Payments*/

AND AIL.INVOICE_ID IN

(SELECT AIL.INVOICE_ID

FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP

WHERE FSP.ORG_ID = AIL.ORG_ID

AND ((NVL(FSP.PURCH_ENCUMBRANCE_FLAG, 'N') = 'N'

AND NOT EXISTS

(SELECT MATCH_STATUS_FLAG

FROM AP_INVOICE_DISTRIBUTIONS_ALL AID

WHERE AID.INVOICE_ID = AIL.INVOICE_ID

AND NVL(AID.MATCH_STATUS_FLAG, 'N')

NOT IN ('T', 'A')))

OR (NVL(FSP.PURCH_ENCUMBRANCE_FLAG, 'N') = 'Y'

AND NOT EXISTS

(SELECT MATCH_STATUS_FLAG

FROM AP_INVOICE_DISTRIBUTIONS_ALL AID1

WHERE AID1.INVOICE_ID = AIL.INVOICE_ID

AND NVL(AID1.MATCH_STATUS_FLAG, 'N') <> 'A' ))

)

AND NOT EXISTS (SELECT H.INVOICE_ID

FROM AP_HOLDS H, AP_HOLD_CODES C

WHERE H.INVOICE_ID = AIL.INVOICE_ID

AND H.HOLD_LOOKUP_CODE = C.HOLD_LOOKUP_CODE

AND ((H.RELEASE_LOOKUP_CODE IS NULL) AND

(C.POSTABLE_FLAG = 'N' OR

C.POSTABLE_FLAG = 'X')))