View: EDW_ITEM_ONETIME_ITEM_LCV

Product:	ENG - Engineering
Description:	This view holds item level onetime items' information
Implementation/DBA Data:	APPS.EDW_ITEM_ONETIME_ITEM_LCV

View Text

SELECT DISTINCT L.ITEM_DESCRIPTION || '-' || CAT.CATEGORY_ID || '-' || L.ORG_ID || '-' || INST.INSTANCE_CODE ||'-ONETIME' ITEM_NUMBER_PK
, INST.INSTANCE_CODE INSTANCE
, CAT.CATEGORY_ID || '-' || INST.INSTANCE_CODE CATSET_CATEGORY_FK
, 'NA_EDW' PRODUCT_GROUP_FK
, SUBSTR(L.ITEM_DESCRIPTION || '(' || CAT.CONCATENATED_SEGMENTS|| ')'
, 1
, 240) ITEM_NUMBER_DP
, SUBSTR(L.ITEM_DESCRIPTION || '(' || CAT.CONCATENATED_SEGMENTS|| ')'
, 1
, 80) NAME
, ' Y' ONE_TIME_FLAG
, L.ITEM_DESCRIPTION DESCRIPTION
, NVL(L.ITEM_DESCRIPTION
, 'NA_EDW') ITEM_NAME
, MIN(L.CREATION_DATE)
, MAX(L.LAST_UPDATE_DATE)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM EDW_LOCAL_INSTANCE INST
, MTL_CATEGORIES_KFV CAT
, PO_LINES_ALL L
WHERE CAT.CATEGORY_ID = L.CATEGORY_ID
AND L.ITEM_ID IS NULL GROUP BY L.ITEM_DESCRIPTION
, L.ORG_ID
, CAT.CATEGORY_ID
, CAT.CONCATENATED_SEGMENTS
, INST.INSTANCE_CODE UNION SELECT DISTINCT L.ITEM_DESCRIPTION || '-' || CAT.CATEGORY_ID || '-' || L.FROM_ORGANIZATION_ID || '-' || INST.INSTANCE_CODE ||'-ONETIME' ITEM_NUMBER_PK
, INST.INSTANCE_CODE
, CAT.CATEGORY_ID || '-' || INST.INSTANCE_CODE CATSET_CATEGORY_FK
, 'NA_EDW'
, SUBSTR(L.ITEM_DESCRIPTION || '(' || CAT.CONCATENATED_SEGMENTS|| ')'
, 1
, 240) ITEM_NUMBER_DP
, SUBSTR(L.ITEM_DESCRIPTION
, 1
, 80) NAME
, 'Y'
, L.ITEM_DESCRIPTION
, NVL(L.ITEM_DESCRIPTION
, 'NA_EDW') ITEM_NAME
, MIN(L.CREATION_DATE)
, MAX(L.LAST_UPDATE_DATE)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM EDW_LOCAL_INSTANCE INST
, MTL_CATEGORIES_KFV CAT
, RCV_SHIPMENT_LINES L
WHERE CAT.CATEGORY_ID = L.CATEGORY_ID
AND L.ITEM_ID IS NULL
AND NOT EXISTS ( SELECT 1
FROM PO_LINES_ALL P
WHERE P.ITEM_ID IS NULL
AND P.ITEM_DESCRIPTION = L.ITEM_DESCRIPTION
AND P.CATEGORY_ID = L.CATEGORY_ID) GROUP BY L.ITEM_DESCRIPTION
, L.FROM_ORGANIZATION_ID
, CAT.CATEGORY_ID
, CAT.CONCATENATED_SEGMENTS
, INST.INSTANCE_CODE WITH READ ONLY

Columns