[Home] [Help]
View: AP_VIEW_PREPAYS_V
View Text
SELECT
AIL.ROWID ROW_ID
,
AIL.INVOICE_ID INVOICE_ID
,
AIL.LINE_NUMBER INVOICE_LINE_NUMBER
,
AIL.PREPAY_INVOICE_ID PREPAY_INVOICE_ID
,
AIL.PREPAY_LINE_NUMBER PREPAY_LINE_NUMBER
,
(-1)*(AIL.AMOUNT - NVL(AIL.INCLUDED_TAX_AMOUNT
, 0)) PREPAY_AMOUNT_APPLIED
,
NULLIF((-1)*(NVL(AIL.TOTAL_REC_TAX_AMOUNT
, 0) +
NVL(AIL.TOTAL_NREC_TAX_AMOUNT
, 0))
, 0) TAX_AMOUNT_APPLIED
,
AP_PREPAY_UTILS_PKG.GET_LINE_PREPAY_AMT_REMAINING
(AIL.PREPAY_INVOICE_ID
,
AIL.PREPAY_LINE_NUMBER) PREPAY_AMOUNT_REMAINING
,
AIL.ACCOUNTING_DATE ACCOUNTING_DATE
,
AIL.PERIOD_NAME PERIOD_NAME
,
AIL.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
,
AIL.DESCRIPTION DESCRIPTION
,
AIL.INVOICE_INCLUDES_PREPAY_FLAG INVOICE_INCLUDES_PREPAY_FLAG
,
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.VENDOR_ID VENDOR_ID
,
AI.VENDOR_SITE_ID VENDOR_SITE_ID
,
PH.SEGMENT1 PO_NUMBER
,
PV.VENDOR_NAME VENDOR_NAME
,
PV.SEGMENT1 VENDOR_NUMBER
,
PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE
,
RSH.RECEIPT_NUM RECEIPT_NUMBER
,
AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
FROM
AP_INVOICES_ALL AI
,
AP_INVOICE_LINES AIL
,
PO_VENDORS PV
,
PO_VENDOR_SITES_ALL PVS
,
PO_HEADERS_ALL PH
,
RCV_TRANSACTIONS RTXNS
,
RCV_SHIPMENT_HEADERS RSH
,
RCV_SHIPMENT_LINES RSL
WHERE AI.INVOICE_ID = AIL.PREPAY_INVOICE_ID
AND AIL.AMOUNT < 0
AND NVL(AIL.DISCARDED_FLAG
, 'N') <> 'Y'
AND AIL.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
AND AI.VENDOR_ID = PV.VENDOR_ID
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND AIL.PO_HEADER_ID = PH.PO_HEADER_ID (+)
AND AIL.RCV_TRANSACTION_ID = RTXNS.TRANSACTION_ID (+)
AND RTXNS.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID (+)
AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
UNION
SELECT
AIL.ROWID ROW_ID
,
AIL.INVOICE_ID INVOICE_ID
,
AIL.LINE_NUMBER INVOICE_LINE_NUMBER
,
AIL1.INVOICE_ID PREPAY_INVOICE_ID
,
AIL1.LINE_NUMBER PREPAY_LINE_NUMBER
,
(-1)*AP_MATCHING_UTILS_PKG.GET_RECOUP_AMT_PER_PREPAY_LINE
(AIL.INVOICE_ID
, AIL.LINE_NUMBER
,
AIL1.INVOICE_ID
, AIL1.LINE_NUMBER) PREPAY_AMOUNT_APPLIED
,
(-1)*AP_MATCHING_UTILS_PKG.GET_RECOUP_TAX_AMT_PER_PPAY_LN
(AIL.INVOICE_ID
, AIL.LINE_NUMBER
,
AIL1.INVOICE_ID
, AIL1.LINE_NUMBER) TAX_AMOUNT_APPLIED
,
AP_PREPAY_UTILS_PKG.GET_LINE_PREPAY_AMT_REMAINING
(AIL.PREPAY_INVOICE_ID
,
AIL.PREPAY_LINE_NUMBER) PREPAY_AMOUNT_REMAINING
,
AIL.ACCOUNTING_DATE ACCOUNTING_DATE
,
AIL.PERIOD_NAME PERIOD_NAME
,
AIL.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
,
AIL.DESCRIPTION DESCRIPTION
,
AIL.INVOICE_INCLUDES_PREPAY_FLAG INVOICE_INCLUDES_PREPAY_FLAG
,
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
,
AI1.INVOICE_NUM PREPAY_NUMBER
,
AI.VENDOR_ID VENDOR_ID
,
AI.VENDOR_SITE_ID VENDOR_SITE_ID
,
PH.SEGMENT1 PO_NUMBER
,
PV.VENDOR_NAME VENDOR_NAME
,
PV.SEGMENT1 VENDOR_NUMBER
,
PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE
,
RSH.RECEIPT_NUM RECEIPT_NUMBER
,
AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
FROM
AP_INVOICES_ALL AI
, /*STD INV */
AP_INVOICES_ALL AI1
, /*PREPAY INV*/
AP_INVOICE_LINES AIL
, /*'ITEM' LINE ON STD INV*/
AP_INVOICE_LINES AIL1
, /*'ITEM' LINE ON PREPAYMENT INV*/
AP_INVOICE_DISTRIBUTIONS AID
, /*PREPAY DIST ON STD INV */
AP_INVOICE_DISTRIBUTIONS AID1
, /*ITEM DIST ON PREPAYMENT INV */
PO_VENDORS PV
,
PO_VENDOR_SITES_ALL PVS
,
PO_HEADERS_ALL PH
,
RCV_TRANSACTIONS RTXNS
,
RCV_SHIPMENT_HEADERS RSH
,
RCV_SHIPMENT_LINES RSL
WHERE AI.INVOICE_ID = AIL.INVOICE_ID
AND AID.INVOICE_ID = AIL.INVOICE_ID
AND AID.INVOICE_LINE_NUMBER = AIL.LINE_NUMBER
AND AIL.LINE_TYPE_LOOKUP_CODE IN ('ITEM'
, 'RETAINAGE RELEASE')
AND NVL(AIL.DISCARDED_FLAG
, 'N') <> 'Y'
AND AID.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
AND AI.VENDOR_ID = PV.VENDOR_ID
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND AIL.PO_HEADER_ID = PH.PO_HEADER_ID (+)
AND AIL.RCV_TRANSACTION_ID = RTXNS.TRANSACTION_ID (+)
AND RTXNS.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID (+)
AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND AID1.INVOICE_DISTRIBUTION_ID = AID.PREPAY_DISTRIBUTION_ID
AND AIL1.INVOICE_ID = AID1.INVOICE_ID
AND AIL1.LINE_NUMBER = AID1.INVOICE_LINE_NUMBER
AND AI1.INVOICE_ID = AIL1.INVOICE_ID