VIEW: APPS.POS_VIEW_PO_SUMMARY_V

Source

View Text - Preformatted

SELECT POH.ACCEPTANCE_REQUIRED_FLAG, POH.ACCEPTANCE_DUE_DATE, POH.APPROVED_DATE, PH.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.CLM_DOCUMENT_NUMBER 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.CLM_DOCUMENT_NUMBER SEGMENT1, TO_NUMBER(NULL) RELEASE_NUM, PDTA.SECURITY_LEVEL_CODE, PDTA.ACCESS_LEVEL_CODE, NVL(PDSH.CLM_FLAG,'N') CLM_FLAG, PH.CLM_ISSUING_OFFICE, PH.CLM_EFFECTIVE_DATE 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, PO_DOC_STYLE_HEADERS PDSH 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') AND NVL(POH.STYLE_ID, 1) = PDSH.STYLE_ID (+) AND PDSH.STATUS (+) = 'ACTIVE' 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.CLM_DOCUMENT_NUMBER || '-' || 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.CLM_DOCUMENT_NUMBER SEGMENT1, POR.RELEASE_NUM RELEASE_NUM, PDTA.SECURITY_LEVEL_CODE, PDTA.ACCESS_LEVEL_CODE, 'N' CLM_FLAG, NULL CLM_ISSUING_OFFICE, NULL CLM_EFFECTIVE_DATE 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'

View Text - HTML Formatted

SELECT POH.ACCEPTANCE_REQUIRED_FLAG
, POH.ACCEPTANCE_DUE_DATE
, POH.APPROVED_DATE
, PH.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.CLM_DOCUMENT_NUMBER 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.CLM_DOCUMENT_NUMBER SEGMENT1
, TO_NUMBER(NULL) RELEASE_NUM
, PDTA.SECURITY_LEVEL_CODE
, PDTA.ACCESS_LEVEL_CODE
, NVL(PDSH.CLM_FLAG
, 'N') CLM_FLAG
, PH.CLM_ISSUING_OFFICE
, PH.CLM_EFFECTIVE_DATE
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
, PO_DOC_STYLE_HEADERS PDSH
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')
AND NVL(POH.STYLE_ID
, 1) = PDSH.STYLE_ID (+)
AND PDSH.STATUS (+) = 'ACTIVE' 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.CLM_DOCUMENT_NUMBER || '-' || 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.CLM_DOCUMENT_NUMBER SEGMENT1
, POR.RELEASE_NUM RELEASE_NUM
, PDTA.SECURITY_LEVEL_CODE
, PDTA.ACCESS_LEVEL_CODE
, 'N' CLM_FLAG
, NULL CLM_ISSUING_OFFICE
, NULL CLM_EFFECTIVE_DATE
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'