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_HOLDS_V

Source

View Text - Preformatted


SELECT AH.ROWID ROW_ID
  , AH.ATTRIBUTE1 ATTRIBUTE1
  , AH.ATTRIBUTE10 ATTRIBUTE10
  , AH.ATTRIBUTE11 ATTRIBUTE11
  , AH.ATTRIBUTE12 ATTRIBUTE12
  , AH.ATTRIBUTE13 ATTRIBUTE13
  , AH.ATTRIBUTE14 ATTRIBUTE14
  , AH.ATTRIBUTE15 ATTRIBUTE15
  , AH.ATTRIBUTE2 ATTRIBUTE2
  , AH.ATTRIBUTE3 ATTRIBUTE3
  , AH.ATTRIBUTE4 ATTRIBUTE4
   , AH.ATTRIBUTE5 ATTRIBUTE5
   , AH.ATTRIBUTE6 ATTRIBUTE6
   , AH.ATTRIBUTE7 ATTRIBUTE7
   , AH.ATTRIBUTE8 ATTRIBUTE8
   , AH.ATTRIBUTE9 ATTRIBUTE9
   , AH.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
   , AH.CREATED_BY CREATED_BY
   , AH.CREATION_DATE CREATION_DATE
   , AH.HELD_BY HELD_BY
   , AH.HOLD_DATE HOLD_DATE
   , AH.HOLD_LOOKUP_CODE HOLD_LOOKUP_CODE
   , ALC1.DESCRIPTION HOLD_REASON /*Bug 15978920*/
   , AH.INVOICE_ID INVOICE_ID
   , AH.LAST_UPDATED_BY LAST_UPDATED_BY
   , AH.LAST_UPDATE_DATE LAST_UPDATE_DATE
   , AH.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
   , AH.LINE_LOCATION_ID LINE_LOCATION_ID
   , AH.RELEASE_LOOKUP_CODE RELEASE_LOOKUP_CODE
   , ALC3.DESCRIPTION RELEASE_REASON  /*Bug 15978920*/
   , AH.STATUS_FLAG STATUS_FLAG
   , AH.RESPONSIBILITY_ID RESPONSIBILITY_ID
   , AH.ORG_ID ORG_ID
   , AHC.USER_RELEASEABLE_FLAG USER_RELEASEABLE_FLAG
   , AHC.USER_UPDATEABLE_FLAG USER_UPDATEABLE_FLAG
   , AHC.POSTABLE_FLAG POSTABLE_FLAG
   , ALC1.DISPLAYED_FIELD HOLD_NAME
   , ALC2.DISPLAYED_FIELD HOLD_TYPE
   , ALC3.DISPLAYED_FIELD RELEASE_NAME
   , (SELECT ALC4.DISPLAYED_FIELD FROM AP_LOOKUP_CODES ALC4 
   	WHERE ALC4.LOOKUP_TYPE (+) = 'NLS TRANSLATION'
   	AND ALC4.LOOKUP_CODE (+) = 'SYSTEM') USER_NAME_SYSTEM
   , AI.INVOICE_NUM INVOICE_NUM
   , DECODE(AH.HELD_BY, 5,(SELECT ALC5.DISPLAYED_FIELD FROM AP_LOOKUP_CODES ALC5 
           WHERE ALC5.LOOKUP_TYPE (+) = 'NLS TRANSLATION'
           AND ALC5.LOOKUP_CODE (+) = 'SYSTEM'), FU.USER_NAME) 
      HELD_BY_USER_NAME
   , DECODE(AH.RELEASE_LOOKUP_CODE, NULL, NULL,
      DECODE(AH.LAST_UPDATED_BY,5, (SELECT ALC6.DISPLAYED_FIELD
   	FROM AP_LOOKUP_CODES ALC6 
           WHERE ALC6.LOOKUP_TYPE (+) = 'NLS TRANSLATION'
           AND ALC6.LOOKUP_CODE (+) = 'SYSTEM'), FU2.USER_NAME))
       RELEASE_BY_USER_NAME
   , DECODE(AH.RELEASE_LOOKUP_CODE, NULL, NULL, AH.LAST_UPDATE_DATE)
      RELEASE_DATE
   , NVL(PH.CLM_DOCUMENT_NUMBER, PH.SEGMENT1) PO_NUMBERS       /*9481666 - CLM*/
   , NVL(PL.LINE_NUM_DISPLAY, TO_CHAR(PL.LINE_NUM)) PO_LINE_NUM /*9481666 - CLM*/
   , HL.LOCATION_CODE SHIP_TO_LOCATION
   , PLL.PO_HEADER_ID PO_HEADER_ID
   , PLL.PO_LINE_ID PO_LINE_ID
   , PLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID
   , DECODE(PLT.MATCHING_BASIS,'QUANTITY',PLL.QUANTITY,
            'AMOUNT',PLL.AMOUNT) SHIPMENT_ORDERED
   , DECODE(PLT.MATCHING_BASIS,'QUANTITY',PLL.QUANTITY_BILLED,
   	 'AMOUNT',PLL.AMOUNT_BILLED) SHIPMENT_BILLED
   , DECODE(PLT.MATCHING_BASIS,'QUANTITY',PLL.QUANTITY_RECEIVED,
   	 'AMOUNT',PLL.AMOUNT_RECEIVED) SHIPMENT_RECEIVED
   , DECODE(PLT.MATCHING_BASIS,'QUANTITY',PLL.QUANTITY_ACCEPTED,'AMOUNT',NULL)
   , PLL.PO_RELEASE_ID
   , PRL.RELEASE_NUM
   , RTXN.TRANSACTION_DATE
   , RSL.LINE_NUM
   , RSH.RECEIPT_NUM
   , DECODE(PLT.MATCHING_BASIS,'QUANTITY',RTXN.QUANTITY,
   	 'AMOUNT',RTXN.AMOUNT) RECEIPT_RECEIVED
   , DECODE(PLT.MATCHING_BASIS,'QUANTITY',RTXN.QUANTITY_BILLED,
   	 'AMOUNT',RTXN.AMOUNT_BILLED) RECEIPT_BILLED
   /*Bug 4929438*/
   , (SELECT DECODE(PLT.MATCHING_BASIS,'QUANTITY',SUM(RTXN1.QUANTITY),
   		 'AMOUNT', NULL)
      FROM RCV_TRANSACTIONS RTXN1
      WHERE RTXN1.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
      AND   RTXN1.PARENT_TRANSACTION_ID = RTXN.TRANSACTION_ID
      AND RTXN1.TRANSACTION_TYPE     IN ('ACCEPT','TRANSFER') /* bug 13451498 */
      )RECEIPT_ACCEPTED
   , PLT.MATCHING_BASIS
   ,AH.HOLD_ID
   ,ALC4.displayed_field WF_STATUS_DISPLAY
   ,AH.WF_STATUS
   FROM   
           AP_HOLDS              AH,
           AP_HOLD_CODES         AHC,
           AP_LOOKUP_CODES       ALC1,
           AP_LOOKUP_CODES       ALC2,
           AP_LOOKUP_CODES       ALC3,
           AP_LOOKUP_CODES       ALC4,
           AP_INVOICES_ALL       AI,
           FND_USER              FU,
           FND_USER              FU2,
           PO_HEADERS_ALL        PH,
           PO_LINES_ALL          PL,
   	       PO_LINE_TYPES	      PLT,
           PO_LINE_LOCATIONS_ALL PLL,
           PO_RELEASES_ALL       PRL,
           RCV_TRANSACTIONS      RTXN,
           RCV_SHIPMENT_LINES    RSL,
           RCV_SHIPMENT_HEADERS  RSH,
           HR_LOCATIONS          HL
    WHERE  AH.HOLD_LOOKUP_CODE     = AHC.HOLD_LOOKUP_CODE (+)
    AND    ALC1.LOOKUP_TYPE (+)    = 'HOLD CODE'
    AND    ALC1.LOOKUP_CODE (+)    = AH.HOLD_LOOKUP_CODE
    AND    ALC2.LOOKUP_TYPE (+)    = 'HOLD TYPE'
    AND    ALC2.LOOKUP_CODE (+)    = AHC.HOLD_TYPE
    AND    ALC3.LOOKUP_TYPE (+)    = 'HOLD CODE'
    AND    ALC3.LOOKUP_CODE (+)    = AH.RELEASE_LOOKUP_CODE
    AND    ALC4.LOOKUP_TYPE (+)     = 'HOLD_WORKFLOW_STATUS'
    AND    ALC4.LOOKUP_CODE (+)    = AH.WF_STATUS
    AND    AH.INVOICE_ID           = AI.INVOICE_ID
    AND    AH.HELD_BY              = FU.USER_ID (+)
    AND    AH.LAST_UPDATED_BY      = FU2.USER_ID (+)
    AND    AH.LINE_LOCATION_ID     = PLL.LINE_LOCATION_ID (+)
    AND    PLL.PO_LINE_ID          = PL.PO_LINE_ID (+)
    AND    PL.LINE_TYPE_ID		= PLT.LINE_TYPE_ID (+)
    AND    PLL.PO_HEADER_ID        = PH.PO_HEADER_ID (+)
    AND    PLL.SHIP_TO_LOCATION_ID = HL.LOCATION_ID (+)
    AND    PLL.PO_RELEASE_ID       = PRL.PO_RELEASE_ID (+)
    AND    AH.RCV_TRANSACTION_ID   = RTXN.TRANSACTION_ID (+)
    AND    RTXN.SHIPMENT_LINE_ID   = RSL.SHIPMENT_LINE_ID (+)
    AND    RSL.SHIPMENT_HEADER_ID  = RSH.SHIPMENT_HEADER_ID (+)

View Text - HTML Formatted

SELECT AH.ROWID ROW_ID
, AH.ATTRIBUTE1 ATTRIBUTE1
, AH.ATTRIBUTE10 ATTRIBUTE10
, AH.ATTRIBUTE11 ATTRIBUTE11
, AH.ATTRIBUTE12 ATTRIBUTE12
, AH.ATTRIBUTE13 ATTRIBUTE13
, AH.ATTRIBUTE14 ATTRIBUTE14
, AH.ATTRIBUTE15 ATTRIBUTE15
, AH.ATTRIBUTE2 ATTRIBUTE2
, AH.ATTRIBUTE3 ATTRIBUTE3
, AH.ATTRIBUTE4 ATTRIBUTE4
, AH.ATTRIBUTE5 ATTRIBUTE5
, AH.ATTRIBUTE6 ATTRIBUTE6
, AH.ATTRIBUTE7 ATTRIBUTE7
, AH.ATTRIBUTE8 ATTRIBUTE8
, AH.ATTRIBUTE9 ATTRIBUTE9
, AH.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, AH.CREATED_BY CREATED_BY
, AH.CREATION_DATE CREATION_DATE
, AH.HELD_BY HELD_BY
, AH.HOLD_DATE HOLD_DATE
, AH.HOLD_LOOKUP_CODE HOLD_LOOKUP_CODE
, ALC1.DESCRIPTION HOLD_REASON /*BUG 15978920*/
, AH.INVOICE_ID INVOICE_ID
, AH.LAST_UPDATED_BY LAST_UPDATED_BY
, AH.LAST_UPDATE_DATE LAST_UPDATE_DATE
, AH.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, AH.LINE_LOCATION_ID LINE_LOCATION_ID
, AH.RELEASE_LOOKUP_CODE RELEASE_LOOKUP_CODE
, ALC3.DESCRIPTION RELEASE_REASON /*BUG 15978920*/
, AH.STATUS_FLAG STATUS_FLAG
, AH.RESPONSIBILITY_ID RESPONSIBILITY_ID
, AH.ORG_ID ORG_ID
, AHC.USER_RELEASEABLE_FLAG USER_RELEASEABLE_FLAG
, AHC.USER_UPDATEABLE_FLAG USER_UPDATEABLE_FLAG
, AHC.POSTABLE_FLAG POSTABLE_FLAG
, ALC1.DISPLAYED_FIELD HOLD_NAME
, ALC2.DISPLAYED_FIELD HOLD_TYPE
, ALC3.DISPLAYED_FIELD RELEASE_NAME
, (SELECT ALC4.DISPLAYED_FIELD
FROM AP_LOOKUP_CODES ALC4 WHERE ALC4.LOOKUP_TYPE (+) = 'NLS TRANSLATION' AND ALC4.LOOKUP_CODE (+) = 'SYSTEM') USER_NAME_SYSTEM
, AI.INVOICE_NUM INVOICE_NUM
, DECODE(AH.HELD_BY
, 5
, (SELECT ALC5.DISPLAYED_FIELD
FROM AP_LOOKUP_CODES ALC5
WHERE ALC5.LOOKUP_TYPE (+) = 'NLS TRANSLATION'
AND ALC5.LOOKUP_CODE (+) = 'SYSTEM')
, FU.USER_NAME) HELD_BY_USER_NAME
, DECODE(AH.RELEASE_LOOKUP_CODE
, NULL
, NULL
, DECODE(AH.LAST_UPDATED_BY
, 5
, (SELECT ALC6.DISPLAYED_FIELD FROM AP_LOOKUP_CODES ALC6
WHERE ALC6.LOOKUP_TYPE (+) = 'NLS TRANSLATION'
AND ALC6.LOOKUP_CODE (+) = 'SYSTEM')
, FU2.USER_NAME)) RELEASE_BY_USER_NAME
, DECODE(AH.RELEASE_LOOKUP_CODE
, NULL
, NULL
, AH.LAST_UPDATE_DATE) RELEASE_DATE
, NVL(PH.CLM_DOCUMENT_NUMBER
, PH.SEGMENT1) PO_NUMBERS /*9481666 - CLM*/
, NVL(PL.LINE_NUM_DISPLAY
, TO_CHAR(PL.LINE_NUM)) PO_LINE_NUM /*9481666 - CLM*/
, HL.LOCATION_CODE SHIP_TO_LOCATION
, PLL.PO_HEADER_ID PO_HEADER_ID
, PLL.PO_LINE_ID PO_LINE_ID
, PLL.LINE_LOCATION_ID PO_LINE_LOCATION_ID
, DECODE(PLT.MATCHING_BASIS
, 'QUANTITY'
, PLL.QUANTITY
, 'AMOUNT'
, PLL.AMOUNT) SHIPMENT_ORDERED
, DECODE(PLT.MATCHING_BASIS
, 'QUANTITY'
, PLL.QUANTITY_BILLED
, 'AMOUNT'
, PLL.AMOUNT_BILLED) SHIPMENT_BILLED
, DECODE(PLT.MATCHING_BASIS
, 'QUANTITY'
, PLL.QUANTITY_RECEIVED
, 'AMOUNT'
, PLL.AMOUNT_RECEIVED) SHIPMENT_RECEIVED
, DECODE(PLT.MATCHING_BASIS
, 'QUANTITY'
, PLL.QUANTITY_ACCEPTED
, 'AMOUNT'
, NULL)
, PLL.PO_RELEASE_ID
, PRL.RELEASE_NUM
, RTXN.TRANSACTION_DATE
, RSL.LINE_NUM
, RSH.RECEIPT_NUM
, DECODE(PLT.MATCHING_BASIS
, 'QUANTITY'
, RTXN.QUANTITY
, 'AMOUNT'
, RTXN.AMOUNT) RECEIPT_RECEIVED
, DECODE(PLT.MATCHING_BASIS
, 'QUANTITY'
, RTXN.QUANTITY_BILLED
, 'AMOUNT'
, RTXN.AMOUNT_BILLED) RECEIPT_BILLED /*BUG 4929438*/
, (SELECT DECODE(PLT.MATCHING_BASIS
, 'QUANTITY'
, SUM(RTXN1.QUANTITY)
, 'AMOUNT'
, NULL)
FROM RCV_TRANSACTIONS RTXN1
WHERE RTXN1.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND RTXN1.PARENT_TRANSACTION_ID = RTXN.TRANSACTION_ID
AND RTXN1.TRANSACTION_TYPE IN ('ACCEPT'
, 'TRANSFER') /* BUG 13451498 */ )RECEIPT_ACCEPTED
, PLT.MATCHING_BASIS
, AH.HOLD_ID
, ALC4.DISPLAYED_FIELD WF_STATUS_DISPLAY
, AH.WF_STATUS
FROM AP_HOLDS AH
, AP_HOLD_CODES AHC
, AP_LOOKUP_CODES ALC1
, AP_LOOKUP_CODES ALC2
, AP_LOOKUP_CODES ALC3
, AP_LOOKUP_CODES ALC4
, AP_INVOICES_ALL AI
, FND_USER FU
, FND_USER FU2
, PO_HEADERS_ALL PH
, PO_LINES_ALL PL
, PO_LINE_TYPES PLT
, PO_LINE_LOCATIONS_ALL PLL
, PO_RELEASES_ALL PRL
, RCV_TRANSACTIONS RTXN
, RCV_SHIPMENT_LINES RSL
, RCV_SHIPMENT_HEADERS RSH
, HR_LOCATIONS HL
WHERE AH.HOLD_LOOKUP_CODE = AHC.HOLD_LOOKUP_CODE (+)
AND ALC1.LOOKUP_TYPE (+) = 'HOLD CODE'
AND ALC1.LOOKUP_CODE (+) = AH.HOLD_LOOKUP_CODE
AND ALC2.LOOKUP_TYPE (+) = 'HOLD TYPE'
AND ALC2.LOOKUP_CODE (+) = AHC.HOLD_TYPE
AND ALC3.LOOKUP_TYPE (+) = 'HOLD CODE'
AND ALC3.LOOKUP_CODE (+) = AH.RELEASE_LOOKUP_CODE
AND ALC4.LOOKUP_TYPE (+) = 'HOLD_WORKFLOW_STATUS'
AND ALC4.LOOKUP_CODE (+) = AH.WF_STATUS
AND AH.INVOICE_ID = AI.INVOICE_ID
AND AH.HELD_BY = FU.USER_ID (+)
AND AH.LAST_UPDATED_BY = FU2.USER_ID (+)
AND AH.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID (+)
AND PLL.PO_LINE_ID = PL.PO_LINE_ID (+)
AND PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID (+)
AND PLL.PO_HEADER_ID = PH.PO_HEADER_ID (+)
AND PLL.SHIP_TO_LOCATION_ID = HL.LOCATION_ID (+)
AND PLL.PO_RELEASE_ID = PRL.PO_RELEASE_ID (+)
AND AH.RCV_TRANSACTION_ID = RTXN.TRANSACTION_ID (+)
AND RTXN.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID (+)
AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)