View: AHL_OSP_ORDERS_V

Product:	AHL - Complex Maintenance Repair and Overhaul
Description:	View to retrieve all OSP order information about an OSP order including values for keys by joining AHL_OSP_ORDERS_VL, PO_HEADERS_V, OE_ORDER_HEADERS_ALL and FND_LOOKUPS_VALUES_VL.
Implementation/DBA Data:	APPS.AHL_OSP_ORDERS_V

View Text

SELECT B.ROWID ROW_ID
, B.OSP_ORDER_ID
, B.OBJECT_VERSION_NUMBER
, B.LAST_UPDATE_DATE
, B.LAST_UPDATED_BY
, B.CREATION_DATE
, B.CREATED_BY
, B.LAST_UPDATE_LOGIN
, B.OSP_ORDER_NUMBER
, B.ORDER_TYPE_CODE
, FND1.MEANING ORDER_TYPE
, B.SINGLE_INSTANCE_FLAG
, FND2.MEANING SINGLE_INSTANCE_MEANING
, B.PO_HEADER_ID
, NULL PO_NUMBER
, B.OE_HEADER_ID
, OE.ORDER_NUMBER SHIPMENT_NUMBER
, B.VENDOR_ID
, VEND.VENDOR_NAME VENDOR
, B.VENDOR_SITE_ID
, VENS.VENDOR_SITE_CODE VENDOR_LOCATION
, B.CUSTOMER_ID
, CUST.PARTY_NAME CUSTOMER
, B.ORDER_DATE
, B.CONTRACT_ID
, CTRCT.CONTRACT_NUMBER CONTRACT_NUMBER
, B.CONTRACT_TERMS
, B.OPERATING_UNIT_ID
, B.PO_SYNCH_FLAG
, B.STATUS_CODE
, FND.MEANING STATUS
, B.PO_BATCH_ID
, B.PO_REQUEST_ID
, B.PO_AGENT_ID
, BUYERS.FULL_NAME BUYER
, B.SECURITY_GROUP_ID
, B.ATTRIBUTE_CATEGORY
, B.ATTRIBUTE1
, B.ATTRIBUTE2
, B.ATTRIBUTE3
, B.ATTRIBUTE4
, B.ATTRIBUTE5
, B.ATTRIBUTE6
, B.ATTRIBUTE7
, B.ATTRIBUTE8
, B.ATTRIBUTE9
, B.ATTRIBUTE10
, B.ATTRIBUTE11
, B.ATTRIBUTE12
, B.ATTRIBUTE13
, B.ATTRIBUTE14
, B.ATTRIBUTE15
, T.DESCRIPTION
, B.PO_INTERFACE_HEADER_ID
FROM AHL_OSP_ORDERS_TL T
, AHL_OSP_ORDERS_B B
, OE_ORDER_HEADERS_ALL OE
, PO_AGENTS_NAME_V BUYERS
, HZ_PARTIES CUST
, PO_VENDORS VEND
, PO_VENDOR_SITES_ALL VENS
, OKC_K_HEADERS_B CTRCT
, FND_LOOKUP_VALUES FND
, FND_LOOKUP_VALUES FND1
, FND_LOOKUP_VALUES FND2
WHERE B.OSP_ORDER_ID = T.OSP_ORDER_ID
AND T.LANGUAGE = USERENV('LANG')
AND B.OPERATING_UNIT_ID = NVL (TO_NUMBER (DECODE (SUBSTR (USERENV ('CLIENT_INFO')
, 1
, 10)
, ' '
, NULL
, SUBSTR (USERENV ('CLIENT_INFO')
, 1
, 10)))
, -99)
AND B.PO_HEADER_ID IS NULL
AND B.OE_HEADER_ID = OE.HEADER_ID(+)
AND B.PO_AGENT_ID = BUYERS.BUYER_ID(+)
AND B.CUSTOMER_ID = CUST.PARTY_ID(+)
AND B.VENDOR_ID = VEND.VENDOR_ID(+)
AND B.VENDOR_SITE_ID = VENS.VENDOR_SITE_ID(+)
AND B.CONTRACT_ID = CTRCT.ID(+)
AND B.STATUS_CODE = FND.LOOKUP_CODE
AND FND.LOOKUP_TYPE = 'AHL_OSP_STATUS_TYPE'
AND B.ORDER_TYPE_CODE = FND1.LOOKUP_CODE
AND FND1.LOOKUP_TYPE = 'AHL_OSP_ORDER_TYPE'
AND B.SINGLE_INSTANCE_FLAG = FND2.LOOKUP_CODE(+)
AND FND2. LOOKUP_TYPE = 'AHL_YES_NO_TYPE'
AND FND.LANGUAGE = USERENV('LANG')
AND FND1.LANGUAGE = USERENV('LANG')
AND FND2.LANGUAGE = USERENV('LANG') UNION ALL SELECT B.ROWID ROW_ID
, B.OSP_ORDER_ID
, B.OBJECT_VERSION_NUMBER
, B.LAST_UPDATE_DATE
, B.LAST_UPDATED_BY
, B.CREATION_DATE
, B.CREATED_BY
, B.LAST_UPDATE_LOGIN
, B.OSP_ORDER_NUMBER
, B.ORDER_TYPE_CODE
, FND1.MEANING ORDER_TYPE
, B.SINGLE_INSTANCE_FLAG
, FND2.MEANING SINGLE_INSTANCE_MEANING
, B.PO_HEADER_ID
, PO.SEGMENT1 PO_NUMBER
, B.OE_HEADER_ID
, OE.ORDER_NUMBER SHIPMENT_NUMBER
, PO.VENDOR_ID
, VEND.VENDOR_NAME VENDOR
, PO.VENDOR_SITE_ID
, VENS.VENDOR_SITE_CODE VENDOR_LOCATION
, B.CUSTOMER_ID
, CUST.PARTY_NAME CUSTOMER
, B.ORDER_DATE
, B.CONTRACT_ID
, CTRCT.CONTRACT_NUMBER CONTRACT_NUMBER
, B.CONTRACT_TERMS
, B.OPERATING_UNIT_ID
, B.PO_SYNCH_FLAG
, B.STATUS_CODE
, FND.MEANING STATUS
, B.PO_BATCH_ID
, B.PO_REQUEST_ID
, B.PO_AGENT_ID
, BUYERS.FULL_NAME BUYER
, B.SECURITY_GROUP_ID
, B.ATTRIBUTE_CATEGORY
, B.ATTRIBUTE1
, B.ATTRIBUTE2
, B.ATTRIBUTE3
, B.ATTRIBUTE4
, B.ATTRIBUTE5
, B.ATTRIBUTE6
, B.ATTRIBUTE7
, B.ATTRIBUTE8
, B.ATTRIBUTE9
, B.ATTRIBUTE10
, B.ATTRIBUTE11
, B.ATTRIBUTE12
, B.ATTRIBUTE13
, B.ATTRIBUTE14
, B.ATTRIBUTE15
, T.DESCRIPTION
, B.PO_INTERFACE_HEADER_ID
FROM AHL_OSP_ORDERS_TL T
, AHL_OSP_ORDERS_B B
, PO_HEADERS PO
, OE_ORDER_HEADERS_ALL OE
, PO_AGENTS_NAME_V BUYERS
, HZ_PARTIES CUST
, PO_VENDORS VEND
, PO_VENDOR_SITES_ALL VENS
, OKC_K_HEADERS_B CTRCT
, FND_LOOKUP_VALUES FND
, FND_LOOKUP_VALUES FND1
, FND_LOOKUP_VALUES FND2
WHERE B.OSP_ORDER_ID = T.OSP_ORDER_ID
AND T.LANGUAGE = USERENV('LANG')
AND B.OPERATING_UNIT_ID = NVL (TO_NUMBER (DECODE (SUBSTR (USERENV ('CLIENT_INFO')
, 1
, 10)
, ' '
, NULL
, SUBSTR (USERENV ('CLIENT_INFO')
, 1
, 10)))
, -99)
AND B.PO_HEADER_ID IS NOT NULL
AND B.PO_HEADER_ID = PO.PO_HEADER_ID
AND B.OE_HEADER_ID = OE.HEADER_ID(+)
AND B.PO_AGENT_ID = BUYERS.BUYER_ID(+)
AND B.CUSTOMER_ID = CUST.PARTY_ID(+)
AND PO.VENDOR_ID = VEND.VENDOR_ID
AND PO.VENDOR_SITE_ID = VENS.VENDOR_SITE_ID
AND B.CONTRACT_ID = CTRCT.ID(+)
AND B.STATUS_CODE = FND.LOOKUP_CODE
AND FND.LOOKUP_TYPE = 'AHL_OSP_STATUS_TYPE'
AND B.ORDER_TYPE_CODE = FND1.LOOKUP_CODE
AND FND1.LOOKUP_TYPE = 'AHL_OSP_ORDER_TYPE'
AND B.SINGLE_INSTANCE_FLAG = FND2.LOOKUP_CODE(+)
AND FND2. LOOKUP_TYPE = 'AHL_YES_NO_TYPE'
AND FND.LANGUAGE = USERENV('LANG')
AND FND1.LANGUAGE = USERENV('LANG')
AND FND2.LANGUAGE = USERENV('LANG')

Columns