View: POS_BILL_ONLY_ORDERS_V

Product:	POS - iSupplier Portal
Description:	used to select consumption advices.
Implementation/DBA Data:	APPS.POS_BILL_ONLY_ORDERS_V

View Text

SELECT POH.APPROVED_DATE
, POH.CREATION_DATE
, POH.COMMENTS
, POH.REVISION_NUM
, POH.AGENT_ID
, POS_GET.GET_PERSON_NAME_CACHE(POH.AGENT_ID) AGENT_NAME
, POH.BILL_TO_LOCATION_ID
, NVL(HRL.LOCATION_CODE
, SUBSTR(RTRIM(HZ.ADDRESS1)||'-'||RTRIM(HZ.CITY)
, 1
, 20)) BILL_TO_LOCATION
, POH.PO_HEADER_ID
, POH.VENDOR_ID
, POH.VENDOR_SITE_ID
, POH.VENDOR_CONTACT_ID
, POH.CLOSED_CODE
, POLC.DISPLAYED_FIELD STATUS
, POH.CURRENCY_CODE
, POH.TYPE_LOOKUP_CODE
, DECODE(POH.TYPE_LOOKUP_CODE
, 'BLANKET'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_POTYPE_BLKT')
, 'STANDARD'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_POTYPE_STD')) TYPE_NAME
, POH.SEGMENT1 PO_NUM
, POV.VENDOR_NAME
, PVS.VENDOR_SITE_CODE
, TO_NUMBER(NULL) PO_RELEASE_ID
, DECODE(POH.TYPE_LOOKUP_CODE
, 'BLANKET'
, TO_CHAR(POH.BLANKET_TOTAL_AMOUNT
, FND_CURRENCY_CACHE.GET_FORMAT_MASK(POH.CURRENCY_CODE
, 30))
, TO_CHAR(POS_TOTALS_PO_SV.GET_PO_TOTAL(POH.PO_HEADER_ID)
, FND_CURRENCY_CACHE.GET_FORMAT_MASK(POH.CURRENCY_CODE
, 30))) AMOUNT
, 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
FROM PO_VENDOR_SITES_ALL PVS
, PO_VENDORS POV
, PO_HEADERS_ALL POH
, HR_ALL_ORGANIZATION_UNITS_TL HOU
, HR_LOCATIONS_ALL_TL HRL
, HZ_LOCATIONS HZ
, PO_LOOKUP_CODES POLC
WHERE POH.TYPE_LOOKUP_CODE IN ('BLANKET'
, 'STANDARD')
AND POH.VENDOR_ID = POV.VENDOR_ID
AND PVS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID
AND POH.APPROVED_FLAG = 'Y'
AND NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE') NOT IN ('IN PROCESS'
, 'INCOMPLETE'
, 'REQUIRES REAPPROVAL')
AND HOU.ORGANIZATION_ID (+) = POH.ORG_ID
AND HOU.LANGUAGE (+) = USERENV('LANG')
AND POH.CONSIGNED_CONSUMPTION_FLAG = 'Y'
AND NVL(POH.CLOSED_CODE
, 'OPEN') = POLC.LOOKUP_CODE
AND POLC.LOOKUP_TYPE = 'DOCUMENT STATE'
AND HRL.LOCATION_ID (+) = POH.BILL_TO_LOCATION_ID
AND HRL.LANGUAGE (+) = USERENV('LANG')
AND HZ.LOCATION_ID (+) = POH.BILL_TO_LOCATION_ID
AND HZ.LANGUAGE (+) = USERENV('LANG') UNION ALL SELECT POR.APPROVED_DATE
, POR.CREATION_DATE
, POH.COMMENTS
, POR.REVISION_NUM
, POR.AGENT_ID
, POS_GET.GET_PERSON_NAME_CACHE(POR.AGENT_ID) AGENT_NAME
, POH.BILL_TO_LOCATION_ID
, NVL(HRL.LOCATION_CODE
, SUBSTR(RTRIM(HZ.ADDRESS1)||'-'||RTRIM(HZ.CITY)
, 1
, 20)) BILL_TO_LOCATION
, POR.PO_HEADER_ID
, POH.VENDOR_ID
, POH.VENDOR_SITE_ID
, POH.VENDOR_CONTACT_ID
, POR.CLOSED_CODE
, POLC.DISPLAYED_FIELD STATUS
, POH.CURRENCY_CODE
, POH.TYPE_LOOKUP_CODE
, DECODE(POR.RELEASE_TYPE
, 'BLANKET'
, FND_MESSAGE.GET_STRING('POS'
, 'POS_POTYPE_BLKTR')) TYPE_NAME
, POH.SEGMENT1 || '-' || POR.RELEASE_NUM PO_NUM
, POV.VENDOR_NAME
, PVS.VENDOR_SITE_CODE
, POR.PO_RELEASE_ID
, TO_CHAR(POS_TOTALS_PO_SV.GET_RELEASE_TOTAL(POR.PO_RELEASE_ID)
, FND_CURRENCY_CACHE.GET_FORMAT_MASK(POH.CURRENCY_CODE
, 30)) AMOUNT
, 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
FROM PO_VENDOR_SITES_ALL PVS
, PO_VENDORS POV
, PO_RELEASES_ALL POR
, PO_HEADERS_ALL POH
, HR_ALL_ORGANIZATION_UNITS_TL HOU
, HR_LOCATIONS_ALL_TL HRL
, HZ_LOCATIONS HZ
, PO_LOOKUP_CODES POLC
WHERE 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')
AND POR.APPROVED_FLAG = 'Y'
AND NVL(POR.AUTHORIZATION_STATUS
, 'INCOMPLETE') NOT IN ('IN PROCESS'
, 'INCOMPLETE'
, 'REQUIRES REAPPROVAL')
AND HOU.ORGANIZATION_ID (+)= POH.ORG_ID
AND HOU.LANGUAGE (+)= USERENV('LANG')
AND POR.CONSIGNED_CONSUMPTION_FLAG = 'Y'
AND NVL(POR.CLOSED_CODE
, 'OPEN') = POLC.LOOKUP_CODE
AND POLC.LOOKUP_TYPE = 'DOCUMENT STATE'
AND HRL.LOCATION_ID (+) = POH.BILL_TO_LOCATION_ID
AND HRL.LANGUAGE (+) = USERENV('LANG')
AND HZ.LOCATION_ID (+) = POH.BILL_TO_LOCATION_ID
AND HZ.LANGUAGE (+) = USERENV('LANG')

Columns