eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AP_VIEW_PREPAYS_FR_PREPAY_V

Source

View Text - Preformatted


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, 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 INVOICE_NUM, 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, AIL.PREPAY_INVOICE_ID PREPAY_ID, AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE FROM AP_INVOICES_ALL AI, AP_INVOICE_LINES AIL, AP_SUPPLIERS PV, AP_SUPPLIER_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 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 (+) AND AI.INVOICE_TYPE_LOOKUP_CODE NOT IN ('PREPAYMENT', 'CREDIT','DEBIT') UNION SELECT AIL.ROWID ROW_ID, AIL.INVOICE_ID INVOICE_ID, AIL.LINE_NUMBER INVOICE_LINE_NUMBER, AID1.INVOICE_ID PREPAY_INVOICE_ID, AID1.INVOICE_LINE_NUMBER PREPAY_LINE_NUMBER, (-1)*AP_MATCHING_UTILS_PKG.Get_Recoup_Amt_Per_Prepay_Line (AIL.INVOICE_ID,AIL.LINE_NUMBER, AID1.INVOICE_ID,AID1.INVOICE_LINE_NUMBER) PREPAY_AMOUNT_APPLIED, (-1)*AP_MATCHING_UTILS_PKG.Get_Recoup_Tax_Amt_Per_Ppay_Ln (AIL.INVOICE_ID,AIL.LINE_NUMBER, AID1.INVOICE_ID,AID1.INVOICE_LINE_NUMBER) TAX_AMOUNT_APPLIED, 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 INVOICE_NUM, 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, AID1.INVOICE_ID PREPAY_ID, AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE FROM AP_INVOICES_ALL AI, /*Std Inv */ AP_INVOICE_LINES AIL, /*Item Line on Std Inv*/ AP_INVOICE_DISTRIBUTIONS AID, /*Prepay Distributions on Item line*/ AP_INVOICE_DISTRIBUTIONS AID1, /*Item dists on Prepayment Inv Item line*/ AP_SUPPLIERS PV, AP_SUPPLIER_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 AIL.LINE_TYPE_LOOKUP_CODE = 'ITEM' AND NVL(AIL.DISCARDED_FLAG,'N') <> 'Y' AND AID.INVOICE_ID = AIL.INVOICE_ID AND AID.INVOICE_LINE_NUMBER = AIL.LINE_NUMBER AND AID.LINE_TYPE_LOOKUP_CODE = 'PREPAY' AND AID1.INVOICE_DISTRIBUTION_ID = AID.PREPAY_DISTRIBUTION_ID 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 AI.INVOICE_TYPE_LOOKUP_CODE NOT IN ('PREPAYMENT', 'CREDIT','DEBIT')

View Text - HTML Formatted

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
, 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 INVOICE_NUM
, 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
, AIL.PREPAY_INVOICE_ID PREPAY_ID
, AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
FROM AP_INVOICES_ALL AI
, AP_INVOICE_LINES AIL
, AP_SUPPLIERS PV
, AP_SUPPLIER_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 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 (+)
AND AI.INVOICE_TYPE_LOOKUP_CODE NOT IN ('PREPAYMENT'
, 'CREDIT'
, 'DEBIT') UNION SELECT AIL.ROWID ROW_ID
, AIL.INVOICE_ID INVOICE_ID
, AIL.LINE_NUMBER INVOICE_LINE_NUMBER
, AID1.INVOICE_ID PREPAY_INVOICE_ID
, AID1.INVOICE_LINE_NUMBER PREPAY_LINE_NUMBER
, (-1)*AP_MATCHING_UTILS_PKG.GET_RECOUP_AMT_PER_PREPAY_LINE (AIL.INVOICE_ID
, AIL.LINE_NUMBER
, AID1.INVOICE_ID
, AID1.INVOICE_LINE_NUMBER) PREPAY_AMOUNT_APPLIED
, (-1)*AP_MATCHING_UTILS_PKG.GET_RECOUP_TAX_AMT_PER_PPAY_LN (AIL.INVOICE_ID
, AIL.LINE_NUMBER
, AID1.INVOICE_ID
, AID1.INVOICE_LINE_NUMBER) TAX_AMOUNT_APPLIED
, 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 INVOICE_NUM
, 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
, AID1.INVOICE_ID PREPAY_ID
, AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
FROM AP_INVOICES_ALL AI
, /*STD INV */ AP_INVOICE_LINES AIL
, /*ITEM LINE ON STD INV*/ AP_INVOICE_DISTRIBUTIONS AID
, /*PREPAY DISTRIBUTIONS ON ITEM LINE*/ AP_INVOICE_DISTRIBUTIONS AID1
, /*ITEM DISTS ON PREPAYMENT INV ITEM LINE*/ AP_SUPPLIERS PV
, AP_SUPPLIER_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 AIL.LINE_TYPE_LOOKUP_CODE = 'ITEM'
AND NVL(AIL.DISCARDED_FLAG
, 'N') <> 'Y'
AND AID.INVOICE_ID = AIL.INVOICE_ID
AND AID.INVOICE_LINE_NUMBER = AIL.LINE_NUMBER
AND AID.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
AND AID1.INVOICE_DISTRIBUTION_ID = AID.PREPAY_DISTRIBUTION_ID
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 AI.INVOICE_TYPE_LOOKUP_CODE NOT IN ('PREPAYMENT'
, 'CREDIT'
, 'DEBIT')