View: SO_SERVICE_DETAILS

Product:	OE - Order Entry
Description:
Implementation/DBA Data:	APPS.SO_SERVICE_DETAILS

View Text

SELECT L.LINE_ID LINE_ID
, DECODE(L.PARENT_LINE_ID
, ''
, ''
, L.LINE_NUMBER) OPTION_LINE_NUMBER
, L.PARENT_LINE_ID PARENT_LINE_ID
, L.COMPONENT_CODE COMPONENT_CODE
, L.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, L.ORDERED_QUANTITY QUANTITY
, MSI.DESCRIPTION DESCRIPTION
, 0 ROW_LEVEL
, L.LINE_ID ID_COLUMN
FROM SO_LINES L
, MTL_SYSTEM_ITEMS MSI
WHERE MSI.INVENTORY_ITEM_ID = L.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = FND_PROFILE.VALUE('SO_ORGANIZATION_ID' ) UNION SELECT L.LINE_ID LINE_ID
, DECODE(L.PARENT_LINE_ID
, ''
, ''
, L.LINE_NUMBER) OPTION_LINE_NUMBER
, L.PARENT_LINE_ID PARENT_LINE_ID
, L.COMPONENT_CODE COMPONENT_CODE
, L.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, LSD.CUSTOMER_PRODUCT_QUANTITY QUANTITY
, ' ' || S.NAME ||' '|| CP.CURRENT_SERIAL_NUMBER || ' ' || CP.CURRENT_CP_REVISION_ID DESCRIPTION
, 4 ROW_LEVEL
, LINE_SERVICE_DETAIL_ID ID_COLUMN
FROM SO_LINES L
, SO_LINE_SERVICE_DETAILS LSD
, CS_SYSTEMS S
, CS_CUSTOMER_PRODUCTS CP
WHERE NVL(L.S25
, 18) != 14
AND LSD.LINE_ID = L.LINE_ID
AND LSD.SYSTEM_ID = S.SYSTEM_ID(+)
AND LSD.CUSTOMER_PRODUCT_ID = CP.CUSTOMER_PRODUCT_ID(+)
AND L.LINE_ID IN (SELECT LINE_ID
FROM SO_LINE_SERVICE_DETAILS LD2
WHERE LD2.LINE_ID = L.LINE_ID GROUP BY LD2.LINE_ID HAVING COUNT(*) > 1) UNION SELECT L.LINE_ID LINE_ID
, DECODE(L.PARENT_LINE_ID
, ''
, ''
, L.LINE_NUMBER) OPTION_LINE_NUMBER
, L.PARENT_LINE_ID PARENT_LINE_ID
, L.COMPONENT_CODE COMPONENT_CODE
, L.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, CP.QUANTITY QUANTITY
, ' ' || S.NAME ||' ' || R.SERIAL_NUMBER || ' ' || R.REVISION DESCRIPTION
, 5 ROW_LEVEL
, R.CP_REVISION_ID ID_COLUMN
FROM SO_LINES L
, CS_CP_REVISIONS R
, CS_CUSTOMER_PRODUCTS CP
, CS_CUSTOMER_PRODUCT_STATUSES CPS
, CS_SYSTEMS S
WHERE NVL(L.S25
, 18) = 14
AND L.LINE_ID = CP.ORIGINAL_ORDER_LINE_ID
AND L.LINE_ID = R.ORDER_LINE_ID
AND CP.CUSTOMER_PRODUCT_ID = R.CUSTOMER_PRODUCT_ID
AND CP.CUSTOMER_PRODUCT_STATUS_ID = CPS.CUSTOMER_PRODUCT_STATUS_ID
AND CPS.CANCELLED_FLAG = 'N'
AND CP.SYSTEM_ID = S.SYSTEM_ID
AND L.LINE_ID IN (SELECT R2.ORDER_LINE_ID
FROM CS_CP_REVISIONS R2
, CS_CUSTOMER_PRODUCTS CP2
, CS_CUSTOMER_PRODUCT_STATUSES CPS2
WHERE L.LINE_ID = CP.ORIGINAL_ORDER_LINE_ID
AND L.LINE_ID = R2.ORDER_LINE_ID
AND CP2.CUSTOMER_PRODUCT_ID = R2.CUSTOMER_PRODUCT_ID
AND CP2.CUSTOMER_PRODUCT_STATUS_ID = CPS2.CUSTOMER_PRODUCT_STATUS_ID
AND CPS2.CANCELLED_FLAG = 'N' GROUP BY R2.ORDER_LINE_ID HAVING COUNT(*) > 1)

Columns