View: PO_POS_VAL_TRX_V

Product:	PO - Purchasing
Description:	PUB
Implementation/DBA Data:	APPS.PO_POS_VAL_TRX_V

View Text

SELECT POH.CLM_DOCUMENT_NUMBER SEGMENT1
, PDT.TYPE_NAME
, POH.CREATION_DATE
, POH.PO_HEADER_ID
, POH.VENDOR_ID
, POH.TYPE_LOOKUP_CODE
, HP.PARTY_NAME VENDOR_NAME
, POH.AGENT_ID
, FLV.MEANING
, POH.COMMENTS
, PDTB.SECURITY_LEVEL_CODE
, HRE.FULL_NAME
, PDTB.ACCESS_LEVEL_CODE
, PDT.DOCUMENT_TYPE_CODE
, POH.FROZEN_FLAG
, POH.CURRENCY_CODE
, POH.VENDOR_SITE_ID
, POH.RATE_TYPE
, POH.START_DATE
, POH.END_DATE
, POH.AUTHORIZATION_STATUS
, FLV2.MEANING
, POH.CONSIGNED_CONSUMPTION_FLAG
, POH.GLOBAL_AGREEMENT_FLAG
, POH.ORG_ID
FROM FND_LOOKUP_VALUES FLV2
, PO_DOCUMENT_TYPES_ALL_TL PDT
, PO_DOCUMENT_TYPES_ALL_B PDTB
, PER_PEOPLE_F HRE
, FND_LOOKUP_VALUES FLV
, PO_HEADERS POH
, AP_SUPPLIERS APS
, HZ_PARTIES HP
WHERE APS.VENDOR_ID (+) = POH.VENDOR_ID
AND APS.PARTY_ID = HP.PARTY_ID(+)
AND POH.TYPE_LOOKUP_CODE IN ('STANDARD'
, 'PLANNED'
, 'BLANKET'
, 'CONTRACT')
AND PDT.DOCUMENT_TYPE_CODE = PDTB.DOCUMENT_TYPE_CODE
AND PDT.DOCUMENT_SUBTYPE = PDTB.DOCUMENT_SUBTYPE
AND PDT.LANGUAGE = USERENV('LANG')
AND POH.CLM_DOCUMENT_NUMBER IS NOT NULL
AND PDT.DOCUMENT_TYPE_CODE = DECODE(POH.TYPE_LOOKUP_CODE
, 'STANDARD'
, 'PO'
, 'PLANNED'
, 'PO'
, 'BLANKET'
, 'PA'
, 'CONTRACT'
, 'PA'
, NULL)
AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE
AND POH.AGENT_ID = HRE.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE
AND HRE .EFFECTIVE_END_DATE
AND FLV.LOOKUP_TYPE = 'PO TYPE'
AND FLV.VIEW_APPLICATION_ID = 201
AND FLV.LOOKUP_CODE = POH.TYPE_LOOKUP_CODE
AND FLV.SECURITY_GROUP_ID = FND_GLOBAL.LOOKUP_SECURITY_GROUP(FLV.LOOKUP_TYPE
, FLV.VIEW_APPLICATION_ID)
AND FLV.LANGUAGE = USERENV('LANG')
AND NVL(POH.CANCEL_FLAG
, 'N') IN ('N'
, 'I')
AND NVL(POH.CLOSED_CODE
, 'OPEN') NOT IN ('FINALLY CLOSED')
AND FLV2.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND FLV2.VIEW_APPLICATION_ID = 201
AND FLV2.LOOKUP_CODE = NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
AND FLV2. SECURITY_GROUP_ID = FND_GLOBAL.LOOKUP_SECURITY_GROUP(FLV2.LOOKUP_TYPE
, FLV2. VIEW_APPLICATION_ID)
AND FLV2.LANGUAGE = USERENV('LANG')
AND PDT.ORG_ID = POH.ORG_ID
AND PDT.ORG_ID = PDTB.ORG_ID UNION SELECT POH.SEGMENT1 SEGMENT1
, PDT.TYPE_NAME
, POH.CREATION_DATE
, POH.PO_HEADER_ID
, POH.VENDOR_ID
, POH.TYPE_LOOKUP_CODE
, HP.PARTY_NAME VENDOR_NAME
, POH.AGENT_ID
, FLV.MEANING
, POH.COMMENTS
, PDTB.SECURITY_LEVEL_CODE
, HRE.FULL_NAME
, PDTB.ACCESS_LEVEL_CODE
, PDT.DOCUMENT_TYPE_CODE
, POH.FROZEN_FLAG
, POH.CURRENCY_CODE
, POH.VENDOR_SITE_ID
, POH.RATE_TYPE
, POH.START_DATE
, POH.END_DATE
, POH.AUTHORIZATION_STATUS
, FLV2.MEANING
, POH.CONSIGNED_CONSUMPTION_FLAG
, POH.GLOBAL_AGREEMENT_FLAG
, POH.ORG_ID
FROM FND_LOOKUP_VALUES FLV2
, PO_DOCUMENT_TYPES_ALL_TL PDT
, PO_DOCUMENT_TYPES_ALL_B PDTB
, PER_PEOPLE_F HRE
, FND_LOOKUP_VALUES FLV
, PO_HEADERS POH
, AP_SUPPLIERS APS
, HZ_PARTIES HP
WHERE APS.VENDOR_ID (+) = POH.VENDOR_ID
AND APS.PARTY_ID = HP.PARTY_ID(+)
AND POH.TYPE_LOOKUP_CODE IN ( 'STANDARD'
, 'PLANNED'
, 'BLANKET'
, 'CONTRACT' )
AND PDT.DOCUMENT_TYPE_CODE = PDTB.DOCUMENT_TYPE_CODE
AND PDT.DOCUMENT_SUBTYPE = PDTB.DOCUMENT_SUBTYPE
AND PDT.LANGUAGE = USERENV('LANG')
AND POH.CLM_DOCUMENT_NUMBER IS NULL
AND PDT.DOCUMENT_TYPE_CODE = DECODE(POH.TYPE_LOOKUP_CODE
, 'STANDARD'
, 'PO'
, 'PLANNED'
, 'PO'
, 'BLANKET'
, 'PA'
, 'CONTRACT'
, 'PA'
, NULL)
AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE
AND POH.AGENT_ID = HRE.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE
AND HRE.EFFECTIVE_END_DATE
AND FLV.LOOKUP_TYPE = 'PO TYPE'
AND FLV.VIEW_APPLICATION_ID = 201
AND FLV.LOOKUP_CODE = POH.TYPE_LOOKUP_CODE
AND FLV.SECURITY_GROUP_ID = FND_GLOBAL.LOOKUP_SECURITY_GROUP(FLV.LOOKUP_TYPE
, FLV.VIEW_APPLICATION_ID)
AND FLV.LANGUAGE = USERENV('LANG')
AND NVL(POH.CANCEL_FLAG
, 'N') IN ( 'N'
, 'I' )
AND NVL(POH.CLOSED_CODE
, 'OPEN') NOT IN ( 'FINALLY CLOSED' )
AND FLV2.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND FLV2.VIEW_APPLICATION_ID = 201
AND FLV2.LOOKUP_CODE = NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
AND FLV2.SECURITY_GROUP_ID = FND_GLOBAL.LOOKUP_SECURITY_GROUP(FLV2.LOOKUP_TYPE
, FLV2.VIEW_APPLICATION_ID)
AND FLV2.LANGUAGE = USERENV('LANG')
AND PDT.ORG_ID = POH.ORG_ID
AND PDT.ORG_ID = PDTB.ORG_ID

Columns