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 , AH.HOLD_REASON HOLD_REASON , 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 , AH.RELEASE_REASON RELEASE_REASON , 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 , PH.SEGMENT1 PO_NUMBERS , PL.LINE_NUM PO_LINE_NUM , 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 = 'ACCEPT') 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
, AH.HOLD_REASON HOLD_REASON
, 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
, AH.RELEASE_REASON RELEASE_REASON
, 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
, PH.SEGMENT1 PO_NUMBERS
, PL.LINE_NUM PO_LINE_NUM
, 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 = 'ACCEPT') 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 (+)