View: AP_HOLDS_V

Product:	AP - Payables
Description:
Implementation/DBA Data:	APPS.AP_HOLDS_V

View Text

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 (+)

Columns