Product: | CS - Service |
---|---|
Description: | This view stores information regarding installation details of product captured during order or quote entry. |
Implementation/DBA Data: | Not implemented in this database |
SELECT LID.LINE_INST_DETAIL_ID
, LID.ORDER_LINE_ID
, LID.QUOTE_LINE_SHIPMENT_ID
, LID.SOURCE_LINE_INST_DETAIL_ID
, LID.TRANSACTION_TYPE_ID
, LID.SYSTEM_ID
, LID.CUSTOMER_PRODUCT_ID
, LID.CUSTOMER_PRODUCT_TYPE_CODE
, LID.CUSTOMER_PRODUCT_QUANTITY
, LID.INSTALLED_AT_PARTY_SITE_ID
, LID.INSTALLED_CP_RETURN_BY_DATE
, LID.INSTALLED_CP_RMA_LINE_ID
, LID.NEW_CP_RMA_LINE_ID
, LID.NEW_CP_RETURN_BY_DATE
, LID.EXPECTED_INSTALLATION_DATE
, LID.START_DATE_ACTIVE
, LID.END_DATE_ACTIVE
, LID.CREATED_BY
, LID.CREATION_DATE
, LID.LAST_UPDATED_BY
, LID.LAST_UPDATE_DATE
, LID.LAST_UPDATE_LOGIN
, LID.CONTEXT
, LID.ATTRIBUTE1
, LID.ATTRIBUTE2
, LID.ATTRIBUTE3
, LID.ATTRIBUTE4
, LID.ATTRIBUTE5
, LID.ATTRIBUTE6
, LID.ATTRIBUTE7
, LID.ATTRIBUTE8
, LID.ATTRIBUTE9
, LID.ATTRIBUTE10
, LID.ATTRIBUTE11
, LID.ATTRIBUTE12
, LID.ATTRIBUTE13
, LID.ATTRIBUTE14
, LID.ATTRIBUTE15
, LID.OBJECT_VERSION_NUMBER
, TT.NAME TRANSACTION_TYPE
, INVENTORY.REFERENCE_NUMBER REFERENCE_NUMBER
, INVENTORY.SERIAL_NUMBER CURRENT_SERIAL_NUMBER
, INVENTORY.INVENTORY_ITEM_ID
, INVENTORY.PRODUCT PRODUCT
, INVENTORY.DESCRIPTION PRODUCT_DESCRIPTION
, SY.NAME SYSTEM_NAME
, NEW.ORDER_NUMBER NEW_CP_ORDER_NUMBER
, NEW.LINE_NUMBER NEW_CP_LINE_NUMBER
, INSTALL.ORDER_NUMBER INSTALLED_CP_ORDER_NUMBER
, INSTALL.LINE_ID INSTALLED_CP_LINE_NUMBER
, QUOTES.QUOTE_NUMBER QUOTE_NUMBER
, QUOTES.LINE_NUMBER QUOTE_LINE_NUMBER
FROM CS_SYSTEMS SY
, CS_TRANSACTION_TYPES TT
, CS_LINE_INST_DETAILS LID
, ( SELECT ASOH1.ORDER_NUMBER ORDER_NUMBER
, ASOL1.LINE_NUMBER LINE_NUMBER
, ASOL1.HEADER_ID HEADER_ID
, ASOL1.LINE_ID LINE_ID
FROM ASO_I_ORDER_LINES_V ASOL1
, ASO_I_ORDER_HEADERS_V ASOH1
WHERE ASOL1.HEADER_ID = ASOH1.HEADER_ID ) INSTALL
, ( SELECT ASOH2.ORDER_NUMBER ORDER_NUMBER
, ASOL2.LINE_NUMBER LINE_NUMBER
, ASOL2.HEADER_ID HEADER_ID
, ASOL2.LINE_ID LINE_ID
FROM ASO_I_ORDER_LINES_V ASOL2
, ASO_I_ORDER_HEADERS_V ASOH2
WHERE ASOL2.HEADER_ID = ASOH2.HEADER_ID ) NEW
, ( SELECT CP.CUSTOMER_PRODUCT_ID CUSTOMER_PRODUCT_ID
, CP.INVENTORY_ITEM_ID
, KFV.DESCRIPTION DESCRIPTION
, KFV.CONCATENATED_SEGMENTS PRODUCT
, CP.REFERENCE_NUMBER REFERENCE_NUMBER
, CP.CURRENT_SERIAL_NUMBER SERIAL_NUMBER
FROM CS_CUSTOMER_PRODUCTS CP
, MTL_SYSTEM_ITEMS_KFV KFV
WHERE CP.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID
AND KFV.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID ) INVENTORY
, ( SELECT ASOQH.QUOTE_NUMBER QUOTE_NUMBER
, ASOQL.LINE_NUMBER LINE_NUMBER
, ASOQL.QUOTE_LINE_ID QUOTE_LINE_ID
, ASOQL.QUOTE_HEADER_ID QUOTE_HEADER_ID
, ASOS.SHIPMENT_ID SHIPMENT_ID
FROM ASO_QUOTE_HEADERS_ALL ASOQH
, ASO_QUOTE_LINES_ALL ASOQL
, ASO_SHIPMENTS ASOS
WHERE ASOQH.QUOTE_HEADER_ID = ASOQL.QUOTE_HEADER_ID
AND ASOQL.QUOTE_LINE_ID = ASOS.QUOTE_LINE_ID ) QUOTES
WHERE TT.TRANSACTION_TYPE_ID = LID.TRANSACTION_TYPE_ID
AND SY.SYSTEM_ID(+) = LID.SYSTEM_ID
AND LID.QUOTE_LINE_SHIPMENT_ID = QUOTES.SHIPMENT_ID(+)
AND LID.INSTALLED_CP_RMA_LINE_ID = INSTALL.LINE_ID(+)
AND LID.NEW_CP_RMA_LINE_ID = NEW.LINE_ID(+)
AND LID.CUSTOMER_PRODUCT_ID = INVENTORY.CUSTOMER_PRODUCT_ID(+)