VIEW: APPS.PO_POS_VAL_TRX_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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