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_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,
  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

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
, 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