View: POS_VIEWPO_SUMMARY_V

View Text

SELECT 'PO' PO_RELEASE_FLAG
, POH.ACCEPTANCE_DUE_DATE
, POH.APPROVED_DATE
, POA.ACTION_DATE
, POH.REVISION_NUM
, POH.PO_HEADER_ID
, POH.VENDOR_CONTACT_ID
, POH.VENDOR_ID
, POH.VENDOR_SITE_ID
, POH.COMMENTS
, POH.CURRENCY_CODE
, POH.ACCEPTANCE_REQUIRED_FLAG
, POH.CREATION_DATE
, POH.SEGMENT1 PO_NUM
, POH.AGENT_ID
, DECODE(POH.TYPE_LOOKUP_CODE
, 'BLANKET'
, FND_MESSAGE_CACHE.GET_STRING('POS'
, 'POS_POTYPE_BLKT')
, 'CONTRACT'
, FND_MESSAGE_CACHE.GET_STRING('POS'
, 'POS_POTYPE_CNTR')
, 'STANDARD'
, FND_MESSAGE_CACHE.GET_STRING('POS'
, 'POS_POTYPE_STD')
, 'PLANNED'
, FND_MESSAGE_CACHE.GET_STRING('POS'
, 'POS_POTYPE_PLND')) TYPE_NAME
, TO_NUMBER(NULL) RELEASE_NUM
, TO_NUMBER(NULL) PO_RELEASE_ID
, POA.ACCEPTED_FLAG ACCEPTANCE_FLAG
, POH.ORG_ID
, HOU.NAME ORG_NAME
, POH.ATTRIBUTE1
, POH.ATTRIBUTE2
, POH.ATTRIBUTE3
, POH.ATTRIBUTE4
, POH.ATTRIBUTE5
, POH.ATTRIBUTE6
, POH.ATTRIBUTE7
, POH.ATTRIBUTE8
, POH.ATTRIBUTE9
, POH.ATTRIBUTE10
, POH.ATTRIBUTE11
, POH.ATTRIBUTE12
, POH.ATTRIBUTE13
, POH.ATTRIBUTE14
, POH.ATTRIBUTE15
, POH.ATTRIBUTE_CATEGORY
, POH.SHIP_VIA_LOOKUP_CODE
, POH.SHIP_TO_LOCATION_ID
, DECODE (POH.CANCEL_FLAG
, 'I'
, NULL
, POH.CANCEL_FLAG) CANCEL_FLAG
, NVL(POH.CLOSED_CODE
, 'OPEN') CLOSED_CODE
, NVL(POH.FROZEN_FLAG
, 'N') FROZEN_FLAG
, POH.TYPE_LOOKUP_CODE
FROM PO_ACCEPTANCES POA
, PO_HEADERS_ALL POH
, HR_ALL_ORGANIZATION_UNITS_TL HOU
WHERE POH.TYPE_LOOKUP_CODE IN ('BLANKET'
, 'CONTRACT'
, 'PLANNED'
, 'STANDARD')
AND POH.APPROVED_FLAG = 'Y'
AND POH.AUTHORIZATION_STATUS NOT IN ('IN PROCESS'
, 'INCOMPLETE'
, 'REQUIRES REAPPROVAL')
AND POH.PO_HEADER_ID = POA.PO_HEADER_ID (+)
AND POH.REVISION_NUM = POA.REVISION_NUM (+)
AND ( POA.CREATION_DATE = (SELECT MAX(CREATION_DATE)
FROM PO_ACCEPTANCES PA2
WHERE POH.PO_HEADER_ID = PA2.PO_HEADER_ID
AND PA2.PO_RELEASE_ID IS NULL) OR POA.CREATION_DATE IS NULL)
AND POA.PO_RELEASE_ID IS NULL
AND HOU.ORGANIZATION_ID (+)= POH.ORG_ID
AND HOU.LANGUAGE (+)= USERENV('LANG') UNION ALL SELECT 'RELEASE' PO_RELEASE_FLAG
, POR.ACCEPTANCE_DUE_DATE
, POR.APPROVED_DATE
, POA.ACTION_DATE
, POR.REVISION_NUM
, POR.PO_HEADER_ID
, POH.VENDOR_CONTACT_ID
, POH.VENDOR_ID
, POH.VENDOR_SITE_ID
, POH.COMMENTS
, POH.CURRENCY_CODE
, POR.ACCEPTANCE_REQUIRED_FLAG
, POR.RELEASE_DATE
, POH.SEGMENT1 PO_NUM
, POR.AGENT_ID
, DECODE(POR.RELEASE_TYPE
, 'BLANKET'
, FND_MESSAGE_CACHE.GET_STRING('POS'
, 'POS_POTYPE_BLKTR')
, 'SCHEDULED'
, FND_MESSAGE_CACHE.GET_STRING('POS'
, 'POS_POTYPE_PLNDR')) TYPE_NAME
, POR.RELEASE_NUM
, POR.PO_RELEASE_ID
, POA.ACCEPTED_FLAG ACCEPTANCE_FLAG
, POR.ORG_ID
, HOU.NAME ORG_NAME
, POR.ATTRIBUTE1
, POR.ATTRIBUTE2
, POR.ATTRIBUTE3
, POR.ATTRIBUTE4
, POR.ATTRIBUTE5
, POR.ATTRIBUTE6
, POR.ATTRIBUTE7
, POR.ATTRIBUTE8
, POR.ATTRIBUTE9
, POR.ATTRIBUTE10
, POR.ATTRIBUTE11
, POR.ATTRIBUTE12
, POR.ATTRIBUTE13
, POR.ATTRIBUTE14
, POR.ATTRIBUTE15
, POR.ATTRIBUTE_CATEGORY
, POH.SHIP_VIA_LOOKUP_CODE
, POH.SHIP_TO_LOCATION_ID
, DECODE (POR.CANCEL_FLAG
, 'I'
, NULL
, POR.CANCEL_FLAG) CANCEL_FLAG
, NVL(POR.CLOSED_CODE
, 'OPEN') CLOSED_CODE
, NVL(POR.FROZEN_FLAG
, 'N') FROZEN_FLAG
, POH.TYPE_LOOKUP_CODE
FROM PO_RELEASES_ALL POR
, PO_ACCEPTANCES POA
, PO_HEADERS_ALL POH
, HR_ALL_ORGANIZATION_UNITS_TL HOU
WHERE POH.PO_HEADER_ID = POR.PO_HEADER_ID
AND POH.TYPE_LOOKUP_CODE IN ( 'BLANKET'
, 'PLANNED')
AND POR.APPROVED_FLAG = 'Y'
AND POR.AUTHORIZATION_STATUS NOT IN ('IN PROCESS'
, 'INCOMPLETE'
, 'REQUIRES REAPPROVAL')
AND POR.PO_RELEASE_ID = POA.PO_RELEASE_ID (+)
AND POR.REVISION_NUM = POA.REVISION_NUM (+)
AND ( POA.CREATION_DATE = (SELECT MAX(CREATION_DATE)
FROM PO_ACCEPTANCES PA2
WHERE POR.PO_RELEASE_ID= PA2.PO_RELEASE_ID) OR POA.CREATION_DATE IS NULL)
AND HOU.ORGANIZATION_ID (+)= POH.ORG_ID
AND HOU.LANGUAGE (+)= USERENV('LANG')

Columns