View: ICX_EDM_PURCHASE_ORDER_V

Product:	ICX - Oracle iProcurement
Description:	Project Related Purchase Orders View
Implementation/DBA Data:	APPS.ICX_EDM_PURCHASE_ORDER_V

View Text

SELECT DISTINCT DIST.PROJECT_ID
, DIST.TASK_ID
, MRP_GET_PROJECT.PROJECT(DIST.PROJECT_ID)
, MRP_GET_PROJECT.TASK(DIST.TASK_ID)
, POH.SEGMENT1 PO_NUM
, PDT.TYPE_NAME
, POLC.DISPLAYED_FIELD AUTHORIZATION_STATUS
, POH.CREATION_DATE ORDER_DATE
, POH.PRINTED_DATE
, POH.REVISION_NUM
, POH.REVISED_DATE
, TO_CHAR(PO_TOTALS_PO_SV.GET_PO_TOTAL(POH.PO_HEADER_ID)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(POH.CURRENCY_CODE
, 30)) AMOUNT
, POH.CURRENCY_CODE
, PO_INQ_SV.GET_PERSON_NAME(POH.AGENT_ID) BUYER_NAME
, V.VENDOR_NAME SUPPLIER_NAME
, DECODE (VC.LAST_NAME
, NULL
, NULL
, VC.LAST_NAME||'
, '|| VC.FIRST_NAME) SUPPLIER_CONTACT_NAME
, VS.VENDOR_SITE_CODE
, V.ATTRIBUTE14 SUPPLIER_URL
, POLC2.DISPLAYED_FIELD FOB
, AT.NAME TERMS_NAME
, POLC3.DISPLAYED_FIELD FREIGHT_TERMS
, POLC4.DISPLAYED_FIELD CLOSURE_STATUS
, TO_NUMBER(NULL) PO_RELEASE_NUMBER
, HRL1.LOCATION_CODE SHIP_TO_LOCATION
, POH.SHIP_VIA_LOOKUP_CODE
, HRL2.LOCATION_CODE BILL_TO_LOCATION
, POH.PO_HEADER_ID
, POH.AGENT_ID BUYER_ID
, POH.VENDOR_ID SUPPLIER_ID
, POH.VENDOR_CONTACT_ID
, POH.VENDOR_SITE_ID
, POH.TERMS_ID
, POH.BILL_TO_LOCATION_ID
, POH.SHIP_TO_LOCATION_ID
, TO_NUMBER(NULL) PO_RELEASE_ID
FROM PO_DOCUMENT_TYPES_ALL PDT
, PO_LOOKUP_CODES POLC
, PO_LOOKUP_CODES POLC2
, PO_LOOKUP_CODES POLC3
, PO_LOOKUP_CODES POLC4
, PO_VENDORS V
, PO_VENDOR_SITES_ALL VS
, PO_VENDOR_CONTACTS VC
, AP_TERMS AT
, HR_LOCATIONS HRL1
, HR_LOCATIONS HRL2
, PO_HEADERS_ALL POH
, PO_DISTRIBUTIONS_ALL DIST
WHERE PDT.DOCUMENT_TYPE_CODE IN ('PO'
, 'PA')
AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE
AND V.VENDOR_ID = POH.VENDOR_ID
AND VS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID
AND VC.VENDOR_CONTACT_ID (+) = POH.VENDOR_CONTACT_ID
AND AT.TERM_ID = POH.TERMS_ID
AND HRL1.LOCATION_ID = POH.SHIP_TO_LOCATION_ID
AND HRL2.LOCATION_ID = POH.BILL_TO_LOCATION_ID
AND POLC.LOOKUP_CODE = NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
AND POLC.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND POLC2.LOOKUP_CODE = POH.FOB_LOOKUP_CODE
AND POLC2.LOOKUP_TYPE = 'FOB'
AND POLC3.LOOKUP_CODE = POH.FREIGHT_TERMS_LOOKUP_CODE
AND POLC3.LOOKUP_TYPE = 'FREIGHT TERMS'
AND POLC4.LOOKUP_CODE = NVL(POH.CLOSED_CODE
, 'OPEN')
AND POLC4.LOOKUP_TYPE= 'DOCUMENT STATE'
AND POH.TYPE_LOOKUP_CODE = 'STANDARD'
AND POH.APPROVED_FLAG IN ('Y'
, 'R')
AND DIST.PO_HEADER_ID = POH.PO_HEADER_ID
AND NVL(PDT.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1 )
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)
AND NVL(VS.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)
AND NVL(POH.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1 )
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)
AND NVL(DIST.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99) UNION ALL SELECT DISTINCT DIST.PROJECT_ID
, DIST.TASK_ID
, MRP_GET_PROJECT.PROJECT(DIST.PROJECT_ID)
, MRP_GET_PROJECT.TASK(DIST.TASK_ID)
, POH.SEGMENT1||'-'||POR.RELEASE_NUM PO_NUM
, PDT.TYPE_NAME DOCUMENT_TYPE
, POLC.DISPLAYED_FIELD AUTHORIZATION_STATUS
, POR.RELEASE_DATE ORDER_DATE
, POR.PRINTED_DATE
, POR.REVISION_NUM
, POR.REVISED_DATE
, POH.CURRENCY_CODE
, TO_CHAR(PO_TOTALS_PO_SV.GET_RELEASE_TOTAL(POR.PO_RELEASE_ID)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(POH.CURRENCY_CODE
, 30)) AMOUNT
, PO_INQ_SV.GET_PERSON_NAME(POR.AGENT_ID) BUYER_NAME
, V.VENDOR_NAME
, DECODE (VC.LAST_NAME
, NULL
, NULL
, VC.LAST_NAME||'
, '||VC.FIRST_NAME) SUPPLIER_CONTACT_NAME
, VS.VENDOR_SITE_CODE
, V.ATTRIBUTE14 SUPPLIER_URL
, POLC2.DISPLAYED_FIELD FOB
, AT.NAME TERMS
, POLC3.DISPLAYED_FIELD FREIGHT_TERMS
, POLC4.DISPLAYED_FIELD CLOSURE_STATUS
, POR.RELEASE_NUM PO_RELEASE_NUMBER
, HRL1.LOCATION_CODE SHIP_TO_LOCATION
, POH.SHIP_VIA_LOOKUP_CODE
, HRL2.LOCATION_CODE BILL_TO_LOCATION
, POR.PO_HEADER_ID
, POR.AGENT_ID BUYER_ID
, POH.VENDOR_ID
, POH.VENDOR_CONTACT_ID
, POH.VENDOR_SITE_ID
, POH.TERMS_ID
, POH.SHIP_TO_LOCATION_ID
, POH.BILL_TO_LOCATION_ID
, POR.PO_RELEASE_ID
FROM PO_DOCUMENT_TYPES_ALL PDT
, PO_LOOKUP_CODES POLC
, PO_LOOKUP_CODES POLC2
, PO_LOOKUP_CODES POLC3
, PO_LOOKUP_CODES POLC4
, PO_VENDORS V
, PO_VENDOR_SITES_ALL VS
, PO_VENDOR_CONTACTS VC
, AP_TERMS AT
, HR_LOCATIONS HRL1
, HR_LOCATIONS HRL2
, PO_RELEASES_ALL POR
, PO_HEADERS_ALL POH
, PO_DISTRIBUTIONS_ALL DIST
WHERE POH.PO_HEADER_ID = POR.PO_HEADER_ID
AND PDT.DOCUMENT_TYPE_CODE = 'RELEASE'
AND PDT.DOCUMENT_SUBTYPE = POR.RELEASE_TYPE
AND V.VENDOR_ID = POH.VENDOR_ID
AND VS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID
AND VC.VENDOR_CONTACT_ID(+) = POH.VENDOR_CONTACT_ID
AND AT.TERM_ID = POH.TERMS_ID
AND HRL1.LOCATION_ID = POH.SHIP_TO_LOCATION_ID
AND HRL2.LOCATION_ID = POH.BILL_TO_LOCATION_ID
AND POLC.LOOKUP_CODE = NVL(POR.AUTHORIZATION_STATUS
, 'INCOMPLETE')
AND POLC.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND POLC2.LOOKUP_CODE = POH.FOB_LOOKUP_CODE
AND POLC2.LOOKUP_TYPE = 'FOB'
AND POLC3.LOOKUP_CODE = POH.FREIGHT_TERMS_LOOKUP_CODE
AND POLC3.LOOKUP_TYPE = 'FREIGHT TERMS'
AND POLC4.LOOKUP_CODE = NVL(POR.CLOSED_CODE
, 'OPEN')
AND POLC4.LOOKUP_TYPE = 'DOCUMENT STATE'
AND POH.TYPE_LOOKUP_CODE = 'BLANKET'
AND POR.APPROVED_FLAG IN ('Y'
, 'R')
AND DIST.PO_HEADER_ID = POH.PO_HEADER_ID
AND NVL(PDT.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1 )
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)
AND NVL(VS.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)
AND NVL(POR.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1 )
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)
AND NVL(POH.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1 )
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)
AND NVL(DIST.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)

Columns