View: EDW_ITEM_ONETIME_ITEMORG_LCV

Product:	ENG - Engineering
Description:	This view holds item organization level onetime item information
Implementation/DBA Data:	APPS.EDW_ITEM_ONETIME_ITEMORG_LCV

View Text

SELECT DISTINCT L.ITEM_DESCRIPTION || '-' || CAT.CATEGORY_ID || '-' || L.ORG_ID || '-' || INST.INSTANCE_CODE || '-ONETIME'
, INST.INSTANCE_CODE INSTANCE
, L.ITEM_DESCRIPTION || '-' || CAT.CATEGORY_ID || '-' || L.ORG_ID || '-' || INST.INSTANCE_CODE || '-ONETIME' ITEM_NUMBER_FK
, 'NA_EDW' PROD_FAMILY_FK
, CAT.CATEGORY_ID ||'-'||INST.INSTANCE_CODE CATSET_CATEGORY_FK
, SUBSTR(L.ITEM_DESCRIPTION || '(' || CAT.CONCATENATED_SEGMENTS || '
, ' || L.ORG_ID|| ')'
, 1
, 240)
, SUBSTR(L.ITEM_DESCRIPTION || '(' || CAT.CONCATENATED_SEGMENTS || '
, ' || L.ORG_ID|| ')'
, 1
, 80)
, 'NA_EDW' BUYER_FK
, 'NA_EDW' PLANNER_FK
, 'Y'
, NULL ITEM_NUMBER
, L.ITEM_DESCRIPTION DESCRIPTION
, L.ORG_ID
, NULL MAKE_OR_BUY_FLAG
, NULL LIST_UNIT_PRICE
, NULL MARKET_PRICE
, NULL TAXABLE_FLAG
, NULL STOCKABLE_FLAG
, NULL INTERNAL_ORD_FLAG
, NULL INV_PLANNING_CODE
, NULL LOT_CONTROL
, NULL OUTSIDE_OP_FLAG
, NULL PRICE_TOLERANCE_PERCENT
, NULL PURCHASABLE_FLAG
, NULL SHELF_LIFE_CODE
, NULL SHELF_LIFE_DAYS
, NULL TAX_CODE
, NULL REVISION_CONTROL
, NULL INSP_REQUIRED
, NULL RECEIPT_REQUIRED
, NULL LOCATOR_CONTROL
, NULL EFFECTIVITY_CONTROL
, NULL SERIAL_CONTROL
, NULL MRP_PLN_METHOD
, NULL APPROVED_SUPPLIER
, NULL SUBSTITUTE_RCPT
, NULL UNORDERED_RCPT
, NULL EXPRS_DELIVERY
, NULL RFQ_REQUIRED_FLAG
, MIN(L.CREATION_DATE)
, MAX(L.LAST_UPDATE_DATE)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM MTL_CATEGORIES_KFV CAT
, PO_LINES_ALL L
, EDW_LOCAL_INSTANCE INST
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'
, INST.INSTANCE_CODE
, L.ITEM_DESCRIPTION || '-' || CAT.CATEGORY_ID || '-' || L.FROM_ORGANIZATION_ID || '-' || INST.INSTANCE_CODE || '-ONETIME'
, 'NA_EDW'
, CAT.CATEGORY_ID ||'-'||INST.INSTANCE_CODE
, SUBSTR(L.ITEM_DESCRIPTION || '(' || CAT.CONCATENATED_SEGMENTS || '
, ' || L.FROM_ORGANIZATION_ID|| ')'
, 1
, 240)
, SUBSTR(L.ITEM_DESCRIPTION
, 1
, 80)
, 'NA_EDW'
, 'NA_EDW'
, 'Y'
, NULL
, L.ITEM_DESCRIPTION
, L.FROM_ORGANIZATION_ID
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, MIN(L.CREATION_DATE)
, MAX(L.LAST_UPDATE_DATE)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
FROM MTL_CATEGORIES_KFV CAT
, PO_REQUISITION_LINES_ALL REQ
, RCV_SHIPMENT_LINES L
, EDW_LOCAL_INSTANCE INST
WHERE CAT.CATEGORY_ID = L.CATEGORY_ID
AND REQ.REQUISITION_LINE_ID = L.REQUISITION_LINE_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.CONCATENATED_SEGMENTS
, CAT.CATEGORY_ID
, INST.INSTANCE_CODE WITH READ ONLY

Columns