VIEW: APPS.EDW_ITEM_ONETIME_ITEMORG_LCV

Source

View Text - Preformatted

SELECT substrb( l.ITEM_DESCRIPTION || '-' || cat.category_id || '-' || l.org_id,1,1000) item_org_pk, null INSTANCE , substrb(l.ITEM_DESCRIPTION || '-' || cat.category_id || '-' || l.org_id ,1,1000) ITEM_NUMBER_FK , 'NA_EDW' prod_family_fk , cat.category_id CATSET_CATEGORY_FK , substrb(l.ITEM_DESCRIPTION || '(' || cat.CONCATENATED_SEGMENTS || ',' || l.org_id|| ')',1,240) , substrb(l.ITEM_DESCRIPTION || '(' || cat.CONCATENATED_SEGMENTS || ',' || l.org_id|| ')',1,320) , 'NA_EDW' BUYER_FK , 'NA_EDW' PLANNER_FK , 'Y' , NULL ITEM_NUMBER , substrb(l.item_description,1,240) 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 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 UNION ALL select substrb(l.ITEM_DESCRIPTION || '-' || cat.category_id || '-' || l.from_organization_id,1,1000) , null, substrb(l.ITEM_DESCRIPTION || '-' || cat.category_id || '-' || l.from_organization_id ,1,1000) , 'NA_EDW' , cat.category_id , substrb(l.ITEM_DESCRIPTION || '(' || cat.CONCATENATED_SEGMENTS || ',' || l.from_organization_id|| ')',1,240) , substrb(l.ITEM_DESCRIPTION,1,320) , 'NA_EDW' , 'NA_EDW' , 'Y' , NULL , substrb(l.item_description,1,240) , 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 where cat.category_id = l.category_id and req.requisition_line_id = l.requisition_line_id and l.item_id is null and l.po_header_id is null and l.po_line_id is null Group by l.ITEM_DESCRIPTION, l.from_organization_id, cat.CONCATENATED_SEGMENTS, cat.category_id WITH READ ONLY

View Text - HTML Formatted

SELECT SUBSTRB( L.ITEM_DESCRIPTION || '-' || CAT.CATEGORY_ID || '-' || L.ORG_ID
, 1
, 1000) ITEM_ORG_PK
, NULL INSTANCE
, SUBSTRB(L.ITEM_DESCRIPTION || '-' || CAT.CATEGORY_ID || '-' || L.ORG_ID
, 1
, 1000) ITEM_NUMBER_FK
, 'NA_EDW' PROD_FAMILY_FK
, CAT.CATEGORY_ID CATSET_CATEGORY_FK
, SUBSTRB(L.ITEM_DESCRIPTION || '(' || CAT.CONCATENATED_SEGMENTS || '
, ' || L.ORG_ID|| ')'
, 1
, 240)
, SUBSTRB(L.ITEM_DESCRIPTION || '(' || CAT.CONCATENATED_SEGMENTS || '
, ' || L.ORG_ID|| ')'
, 1
, 320)
, 'NA_EDW' BUYER_FK
, 'NA_EDW' PLANNER_FK
, 'Y'
, NULL ITEM_NUMBER
, SUBSTRB(L.ITEM_DESCRIPTION
, 1
, 240) 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
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 UNION ALL SELECT SUBSTRB(L.ITEM_DESCRIPTION || '-' || CAT.CATEGORY_ID || '-' || L.FROM_ORGANIZATION_ID
, 1
, 1000)
, NULL
, SUBSTRB(L.ITEM_DESCRIPTION || '-' || CAT.CATEGORY_ID || '-' || L.FROM_ORGANIZATION_ID
, 1
, 1000)
, 'NA_EDW'
, CAT.CATEGORY_ID
, SUBSTRB(L.ITEM_DESCRIPTION || '(' || CAT.CONCATENATED_SEGMENTS || '
, ' || L.FROM_ORGANIZATION_ID|| ')'
, 1
, 240)
, SUBSTRB(L.ITEM_DESCRIPTION
, 1
, 320)
, 'NA_EDW'
, 'NA_EDW'
, 'Y'
, NULL
, SUBSTRB(L.ITEM_DESCRIPTION
, 1
, 240)
, 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
WHERE CAT.CATEGORY_ID = L.CATEGORY_ID
AND REQ.REQUISITION_LINE_ID = L.REQUISITION_LINE_ID
AND L.ITEM_ID IS NULL
AND L.PO_HEADER_ID IS NULL
AND L.PO_LINE_ID IS NULL GROUP BY L.ITEM_DESCRIPTION
, L.FROM_ORGANIZATION_ID
, CAT.CONCATENATED_SEGMENTS
, CAT.CATEGORY_ID WITH READ ONLY