View: POS_VIEW_PO_SUMMARY_V

Product:	POS - iSupplier Portal
Description:	This view is used to display all Purchase Orders from archive tables.
Implementation/DBA Data:	APPS.POS_VIEW_PO_SUMMARY_V

View Text

SELECT POH.ACCEPTANCE_REQUIRED_FLAG
, POH.ACCEPTANCE_DUE_DATE
, POH.APPROVED_DATE
, POH.COMMENTS
, POH.REVISION_NUM
, POH.AGENT_ID
, POH.PO_HEADER_ID
, POH.VENDOR_CONTACT_ID
, POH.VENDOR_ID
, POH.VENDOR_SITE_ID
, NVL(POH.CLOSED_CODE
, 'OPEN') CLOSED_CODE
, POH.CURRENCY_CODE
, POH.CANCEL_FLAG
, POH.FROZEN_FLAG
, POH.USER_HOLD_FLAG USER_HOLD_FLAG
, POH.CREATION_DATE
, POH.SEGMENT1 PO_NUM
, POV.VENDOR_NAME
, PVS.VENDOR_SITE_CODE
, POV.SEGMENT1 VENDOR_NUMBER
, POH.TYPE_LOOKUP_CODE
, DECODE(POH.TYPE_LOOKUP_CODE
, 'BLANKET'
, DECODE(POH.GLOBAL_AGREEMENT_FLAG
, 'Y'
, FND_MESSAGE_CACHE.GET_STRING('POS'
, 'POS_POTYPE_GBLA')
, FND_MESSAGE_CACHE.GET_STRING('POS'
, 'POS_POTYPE_BLKT'))
, 'CONTRACT'
, DECODE(POH.GLOBAL_AGREEMENT_FLAG
, 'Y'
, FND_MESSAGE_CACHE.GET_STRING('POS'
, 'POS_POTYPE_GCNTR')
, 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) PO_RELEASE_ID
, 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.GLOBAL_AGREEMENT_FLAG
, POH.SEGMENT1 SEGMENT1
, TO_NUMBER(NULL) RELEASE_NUM
, PDTA.SECURITY_LEVEL_CODE
, PDTA.ACCESS_LEVEL_CODE
FROM PO_HEADERS_ARCHIVE_ALL POH
, PO_HEADERS_ALL PH
, HR_ALL_ORGANIZATION_UNITS_TL HOU
, PO_VENDOR_SITES_ALL PVS
, PO_VENDORS POV
, PO_DOCUMENT_TYPES_ALL_B PDTA
WHERE POH.PO_HEADER_ID = PH.PO_HEADER_ID
AND POH.TYPE_LOOKUP_CODE IN ('BLANKET'
, 'CONTRACT'
, 'PLANNED'
, 'STANDARD')
AND POH.VENDOR_ID = POV.VENDOR_ID
AND PVS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID
AND POH.LATEST_EXTERNAL_FLAG = 'Y'
AND HOU.ORGANIZATION_ID (+)= POH.ORG_ID
AND HOU.LANGUAGE (+)= USERENV('LANG')
AND PDTA.ORG_ID=POH.ORG_ID
AND PDTA.DOCUMENT_SUBTYPE=POH.TYPE_LOOKUP_CODE
AND PDTA.DOCUMENT_TYPE_CODE IN ('PO'
, 'PA') UNION ALL SELECT POR.ACCEPTANCE_REQUIRED_FLAG
, POR.ACCEPTANCE_DUE_DATE
, POR.APPROVED_DATE
, POH.COMMENTS
, POR.REVISION_NUM
, POR.AGENT_ID
, POR.PO_HEADER_ID
, POH.VENDOR_CONTACT_ID
, POH.VENDOR_ID
, POH.VENDOR_SITE_ID
, NVL(POR.CLOSED_CODE
, 'OPEN') CLOSED_CODE
, POH.CURRENCY_CODE
, POR.CANCEL_FLAG
, POR.FROZEN_FLAG
, POR.HOLD_FLAG USER_HOLD_FLAG
, POR.RELEASE_DATE CREATION_DATE
, POH.SEGMENT1 || '-' || POR.RELEASE_NUM PO_NUM
, POV.VENDOR_NAME
, PVS.VENDOR_SITE_CODE
, POV.SEGMENT1 VENDOR_NUMBER
, POH.TYPE_LOOKUP_CODE
, 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.PO_RELEASE_ID
, 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
, NULL GLOBAL_AGREEMENT_FLAG
, POH.SEGMENT1 SEGMENT1
, POR.RELEASE_NUM RELEASE_NUM
, PDTA.SECURITY_LEVEL_CODE
, PDTA.ACCESS_LEVEL_CODE
FROM PO_VENDOR_SITES_ALL PVS
, PO_VENDORS POV
, PO_RELEASES_ARCHIVE_ALL POR
, PO_HEADERS_ARCHIVE_ALL POH
, PO_RELEASES_ALL PR
, HR_ALL_ORGANIZATION_UNITS_TL HOU
, PO_DOCUMENT_TYPES_ALL PDTA
WHERE POR.PO_RELEASE_ID = PR.PO_RELEASE_ID
AND POH.PO_HEADER_ID = POR.PO_HEADER_ID
AND POH.VENDOR_ID = POV.VENDOR_ID
AND PVS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID
AND POH.TYPE_LOOKUP_CODE IN ( 'BLANKET'
, 'PLANNED')
AND POH.LATEST_EXTERNAL_FLAG = 'Y'
AND POR.LATEST_EXTERNAL_FLAG = 'Y'
AND HOU.ORGANIZATION_ID (+)= POH.ORG_ID
AND HOU.LANGUAGE (+)= USERENV('LANG')
AND PDTA.ORG_ID=POH.ORG_ID
AND PDTA.DOCUMENT_SUBTYPE= DECODE(POH.TYPE_LOOKUP_CODE
, 'PLANNED'
, 'SCHEDULED'
, POH.TYPE_LOOKUP_CODE)
AND PDTA.DOCUMENT_TYPE_CODE ='RELEASE'

Columns