[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE(ITEM_CATEGORY_SET1, NULL, 'NA_EDW', ITEM_CATEGORY_SET1),
DECODE(ITEM_CATEGORY_SET2, NULL, 'NA_EDW', ITEM_CATEGORY_SET2),
DECODE(ITEM_CATEGORY_SET3, NULL, 'NA_EDW', ITEM_CATEGORY_SET3),
DECODE(ITEM_ORG_CATEGORY_SET1, NULL, 'NA_EDW', ITEM_ORG_CATEGORY_SET1),
DECODE(ITM_HRCHY3_COLL_TYPE, NULL, 'NA_EDW', ITM_HRCHY3_COLL_TYPE),
DECODE(ITM_HRCHY3_VBH_TOP_NODE, NULL, 'NA_EDW', ITM_HRCHY3_VBH_TOP_NODE)
INTO l_item_catset1_name,
l_item_catset2_name,
l_item_catset3_name,
l_itemorg_catset1_name,
l_itm_hrchy3_coll_type,
l_itm_hrchy3_vbh_top_node
FROM EDW_LOCAL_SYSTEM_PARAMETERS;
SELECT CATEGORY_SET_NAME
INTO l_item_catset3_name
FROM MTL_CATEGORY_SETS_VL
WHERE CATEGORY_SET_ID = g_vbh_catset_id;
SELECT mtd.functional_area_id
INTO l_functional_area_id
FROM mtl_category_sets mcs,
mtl_default_category_sets mtd
WHERE mcs.category_set_name = p_category_set_name
AND mcs.category_set_id = mtd.category_set_id;
SELECT instance_code
FROM EDW_LOCAL_INSTANCE;
SELECT
mti.concatenated_segments,
mti.organization_id,
COUNT(mti.inventory_item_id)
FROM
mtl_system_items_kfv mti
GROUP BY
mti.concatenated_segments,
mti.organization_id
HAVING COUNT(inventory_item_id) > 1;
SELECT
inventory_item_id,
organization_id
FROM
mtl_system_items_kfv
WHERE concatenated_segments = l_name
AND organization_id = l_org_id;
SELECT TO_DATE(p_from_date, 'YYYY/MM/DD HH24:MI:SS'),
TO_DATE(p_to_date,'YYYY/MM/DD HH24:MI:SS')
INTO l_FROM_date, l_to_date FROM DUAL;
l_rows_inserted NUMBER:=0;
INSERT INTO EDW_ITEM_ITEMREV_LSTG(
CREATION_DATE,
EFFECTIVE_DATE,
ERROR_CODE,
INSTANCE,
ITEM_ORG_FK,
ITEM_ORG_FK_KEY,
ITEM_REVISION,
ITEM_REVISION_DP,
ITEM_REVISION_PK,
LAST_UPDATE_DATE,
LEVEL_NAME,
NAME,
REQUEST_ID,
ROW_ID,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
OPERATION_CODE,
COLLECTION_STATUS)
SELECT
CREATION_DATE,
EFFECTIVE_DATE,
NULL, --ERROR_CODE,
INSTANCE,
ITEM_ORG_FK,
NULL, --ITEM_ORG_FK_KEY,
SUBSTRB(ITEM_REVISION, 1, 240),
SUBSTRB(ITEM_REVISION_DP, 1, 240),
ITEM_REVISION_PK,
LAST_UPDATE_DATE,
NULL, --LEVEL_NAME,
SUBSTRB(NAME, 1, 320),
NULL, --REQUEST_ID,
NULL, --ROW_ID,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
NULL, -- OPERATION_CODE
'READY'
FROM EDW_ITEM_ITEMREV_LCV
WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
l_rows_inserted := SQL%ROWCOUNT;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
' rows into the staging table');
EDW_ITEMS_M_C.g_row_count:=EDW_ITEMS_M_C.g_row_count+l_rows_inserted;
l_rows_inserted NUMBER := 0;
INSERT INTO EDW_ITEM_PRDFAM_LSTG(
ALL_FK,
ALL_FK_KEY,
DESCRIPTION,
ERROR_CODE,
INSTANCE,
NAME,
PRODUCT_FAMILY,
PROD_FAMILY_DP,
PROD_FAMILY_PK,
REQUEST_ID,
ROW_ID,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
OPERATION_CODE,
COLLECTION_STATUS,
CREATION_DATE,
LAST_UPDATE_DATE)
SELECT
ALL_FK,
NULL, --ALL_FK_KEY,
DESCRIPTION,
NULL, --ERROR_CODE,
l_instance, --INSTANCE, /* Bug# 2558245 */
SUBSTRB(NAME, 1, 320),
PRODUCT_FAMILY,
PROD_FAMILY_DP,
PROD_FAMILY_PK || '-' || l_instance, -- PROD_FAMILY_PK, /* Bug# 2558245 */
NULL, --REQUEST_ID,
NULL, --ROW_ID,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
NULL, -- OPERATION_CODE
'READY',
CREATION_DATE,
LAST_UPDATE_DATE
FROM EDW_ITEM_PRDFAM_LCV
WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
l_rows_inserted := SQL%ROWCOUNT;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
' rows into the staging table');
l_rows_inserted NUMBER := 0;
SELECT instance_code
FROM edw_local_instance;
SELECT
MIC.INVENTORY_ITEM_ID,
MIC.ORGANIZATION_ID
FROM
MTL_CATEGORIES CAT,
MTL_ITEM_CATEGORIES MIC,
MTL_CATEGORY_SETS SETS
WHERE CAT.CATEGORY_ID = MIC.CATEGORY_ID
AND MIC.CATEGORY_SET_ID = SETS.CATEGORY_SET_ID
AND MIC.LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date
AND SETS.CATEGORY_SET_NAME = l_itemorg_catset1_name;
l_rows_inserted := 0;
INSERT INTO EDW_ITEM_ITEMORG_TEMP(
APPROVED_SUPPLIER,
BUYER_FK,
CREATION_DATE,
CATSET_CATEGORY_FK,
CATSET_CATEGORY_FK_KEY,
DESCRIPTION,
ERROR_CODE,
EXPRS_DELIVERY,
HAZARD_CLASS_ID,
INSP_REQUIRED,
INSTANCE,
INTERNAL_ORD_FLAG,
INV_PLANNING_CODE,
ITEM_NUMBER,
ITEM_NUMBER_FK,
ITEM_NUMBER_FK_KEY,
ITEM_ORG_DP,
ITEM_ORG_PK,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LOCATOR_CONTROL,
EFFECTIVITY_CONTROL,
LOT_CONTROL,
MAKE_OR_BUY_FLAG,
MARKET_PRICE,
MRP_PLN_METHOD,
NAME,
ONE_TIME_FLAG,
OUTSIDE_OP_FLAG,
PLANNER_FK,
PRICE_TOL_PERCENT,
PROD_FAMILY_FK,
PROD_FAMILY_FK_KEY,
PURCHASABLE_FLAG,
RECEIPT_REQUIRED,
REQUEST_ID,
REVISION_CONTROL,
RFQ_REQUIRED_FLAG,
ROW_ID,
SERIAL_CONTROL,
SHELF_LIFE_CODE,
SHELF_LIFE_DAYS,
STOCKABLE_FLAG,
SUBSTITUTE_RCPT,
TAXABLE_FLAG,
TAX_CODE,
UNIT_LIST_PRICE,
UNORDERED_RCPT,
UN_NUMBER_ID,
SEGMENT1,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
OPERATION_CODE,
COLLECTION_STATUS,
ITEM_TYPE) /* Enh# 2544906 */
SELECT
APPROVED_SUPPLIER,
BUYER_FK,
CREATION_DATE,
CATSET_CATEGORY_FK,
INVENTORY_ITEM_ID, --CATSET_CATEGORY_FK_KEY,
DESCRIPTION,
NULL, --ERROR_CODE,
EXPRS_DELIVERY,
HAZARD_CLASS_ID,
INSP_REQUIRED,
l_instance,
INTERNAL_ORD_FLAG,
SUBSTRB(INV_PLANNING_CODE, 1, 40),
SUBSTRB(ITEM_NUMBER, 1, 240),
ITEM_NUMBER_FK || '-' || l_instance, -- ITEM_NUMBER_FK, /* Bug# 2558245 */
NULL, --ITEM_NUMBER_FK_KEY,
SUBSTRB(ITEM_ORG_DP, 1, 240),
ITEM_ORG_PK || '-' || l_instance, -- ITEM_ORG_PK, /* Bug# 2558245 */
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LOCATOR_CONTROL,
EFFECTIVITY_CONTROL,
LOT_CONTROL,
SUBSTRB(MAKE_OR_BUY_FLAG, 1, 40),
MARKET_PRICE,
MRP_PLN_METHOD,
SUBSTRB(NAME, 1, 320),
NULL, --ONE_TIME_FLAG,
OUTSIDE_OP_FLAG,
PLANNER_FK,
PRICE_TOL_PERCENT,
PROD_FAMILY_FK,
ORGANIZATION_ID, --PROD_FAMILY_FK_KEY,
PURCHASABLE_FLAG,
RECEIPT_REQUIRED,
NULL, --REQUEST_ID,
REVISION_CONTROL,
RFQ_REQUIRED_FLAG,
NULL, --ROW_ID,
SERIAL_CONTROL,
SHELF_LIFE_CODE,
SHELF_LIFE_DAYS,
STOCKABLE_FLAG,
SUBSTITUTE_RCPT,
TAXABLE_FLAG,
TAX_CODE,
UNIT_LIST_PRICE,
UNORDERED_RCPT,
UN_NUMBER_ID,
SEGMENT1,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
NULL, -- OPERATION_CODE
'READY',
ITEM_TYPE /* Enh# 2544906 */
FROM EDW_ITEM_ITEMORG_LCV
WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
l_rows_inserted := SQL%ROWCOUNT;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
' rows into the staging table');
/** Need to INSERT additional items for whom the category assignments
** have changed.
*/
edw_log.put_line('Inserting additional items due to category changes ');
INSERT INTO EDW_ITEM_ITEMORG_TEMP(
APPROVED_SUPPLIER,
BUYER_FK,
CREATION_DATE,
CATSET_CATEGORY_FK,
CATSET_CATEGORY_FK_KEY,
DESCRIPTION,
ERROR_CODE,
EXPRS_DELIVERY,
HAZARD_CLASS_ID,
INSP_REQUIRED,
INSTANCE,
INTERNAL_ORD_FLAG,
INV_PLANNING_CODE,
ITEM_NUMBER,
ITEM_NUMBER_FK,
ITEM_NUMBER_FK_KEY,
ITEM_ORG_DP,
ITEM_ORG_PK,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LOCATOR_CONTROL,
EFFECTIVITY_CONTROL,
LOT_CONTROL,
MAKE_OR_BUY_FLAG,
MARKET_PRICE,
MRP_PLN_METHOD,
NAME,
ONE_TIME_FLAG,
OUTSIDE_OP_FLAG,
PLANNER_FK,
PRICE_TOL_PERCENT,
PROD_FAMILY_FK,
PROD_FAMILY_FK_KEY,
PURCHASABLE_FLAG,
RECEIPT_REQUIRED,
REQUEST_ID,
REVISION_CONTROL,
RFQ_REQUIRED_FLAG,
ROW_ID,
SERIAL_CONTROL,
SHELF_LIFE_CODE,
SHELF_LIFE_DAYS,
STOCKABLE_FLAG,
SUBSTITUTE_RCPT,
TAXABLE_FLAG,
TAX_CODE,
UNIT_LIST_PRICE,
UNORDERED_RCPT,
UN_NUMBER_ID,
SEGMENT1,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
OPERATION_CODE,
COLLECTION_STATUS,
ITEM_TYPE) /* Enh# 2544906 */
SELECT
APPROVED_SUPPLIER,
BUYER_FK,
CREATION_DATE,
CATSET_CATEGORY_FK,
INVENTORY_ITEM_ID, --CATSET_CATEGORY_FK_KEY,
DESCRIPTION,
NULL, --ERROR_CODE,
EXPRS_DELIVERY,
HAZARD_CLASS_ID,
INSP_REQUIRED,
l_instance,
INTERNAL_ORD_FLAG,
SUBSTRB(INV_PLANNING_CODE, 1, 40),
SUBSTRB(ITEM_NUMBER, 1, 240),
ITEM_NUMBER_FK || '-' || l_instance, -- ITEM_NUMBER_FK, /* Bug# 2558245 */
NULL, --ITEM_NUMBER_FK_KEY,
SUBSTRB(ITEM_ORG_DP, 1, 240),
ITEM_ORG_PK || '-' || l_instance, -- ITEM_ORG_PK, /* Bug# 2558245 */
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LOCATOR_CONTROL,
EFFECTIVITY_CONTROL,
LOT_CONTROL,
SUBSTRB(MAKE_OR_BUY_FLAG, 1, 40),
MARKET_PRICE,
MRP_PLN_METHOD,
SUBSTRB(NAME, 1, 320),
NULL, --ONE_TIME_FLAG,
OUTSIDE_OP_FLAG,
PLANNER_FK,
PRICE_TOL_PERCENT,
PROD_FAMILY_FK,
ORGANIZATION_ID, --PROD_FAMILY_FK_KEY,
PURCHASABLE_FLAG,
RECEIPT_REQUIRED,
NULL, --REQUEST_ID,
REVISION_CONTROL,
RFQ_REQUIRED_FLAG,
NULL, --ROW_ID,
SERIAL_CONTROL,
SHELF_LIFE_CODE,
SHELF_LIFE_DAYS,
STOCKABLE_FLAG,
SUBSTITUTE_RCPT,
TAXABLE_FLAG,
TAX_CODE,
UNIT_LIST_PRICE,
UNORDERED_RCPT,
UN_NUMBER_ID,
SEGMENT1,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
NULL, -- OPERATION_CODE
'READY',
ITEM_TYPE /* Enh# 2544906 */
FROM EDW_ITEM_ITEMORG_LCV
WHERE inventory_item_id = category_assignments_rec.inventory_item_id
AND organization_id = category_assignments_rec.organization_id
AND last_update_date NOT BETWEEN l_push_date_range1 AND l_push_date_range2; /* Bug# 2659263 */
edw_log.put_line('Done Inserting category changed items into item-org ');
UPDATE EDW_ITEM_ITEMORG_TEMP
SET CATSET_CATEGORY_FK =
(SELECT DECODE(COUNT(cat.category_id), 1, TO_CHAR(1)||'-'||MAX(cat.category_id) || '-' || l_instance, 'NA_EDW')
FROM
MTL_ITEM_CATEGORIES cat,
MTL_CATEGORY_SETS_TL tl
WHERE cat.organization_id = prod_family_fk_key
AND cat.inventory_item_id = catset_category_fk_key
AND tl.category_set_name = l_itemorg_catset1_name
AND cat.category_set_id = tl.category_set_id
AND tl.language = userenv('LANG')),
PROD_FAMILY_FK =
(SELECT DECODE(count(cat.category_id), 1, max(cat.category_id) || '-' || l_instance, 'NA_EDW')
FROM
MTL_ITEM_CATEGORIES cat,
MTL_CATEGORY_SETS_TL tl
WHERE cat.organization_id = prod_family_fk_key
AND cat.inventory_item_id = catset_category_fk_key
AND tl.category_set_name = 'Product Family'
AND cat.category_set_id = tl.category_set_id
AND tl.language = userenv('LANG'));
UPDATE EDW_ITEM_ITEMORG_TEMP
SET MAKE_OR_BUY_FLAG =
(SELECT lkup.meaning
FROM mfg_lookups lkup
WHERE lkup.lookup_type = 'MTL_PLANNING_MAKE_BUY'
AND lkup.lookup_code = TO_NUMBER(make_or_buy_flag)),
LOCATOR_CONTROL =
(SELECT lkup.meaning
FROM mfg_lookups lkup
WHERE lkup.lookup_type = 'MTL_LOCATION_CONTROL'
AND lkup.lookup_code = TO_NUMBER(locator_control)),
EFFECTIVITY_CONTROL =
(SELECT lkup.meaning
FROM mfg_lookups lkup
WHERE lkup.lookup_type = 'MTL_EFFECTIVITY_CONTROL'
AND lkup.lookup_code = TO_NUMBER(effectivity_control)),
LOT_CONTROL =
(SELECT lkup.meaning
FROM mfg_lookups lkup
WHERE lkup.lookup_type = 'MTL_LOT_CONTROL'
AND lkup.lookup_code = TO_NUMBER(lot_control)),
INV_PLANNING_CODE =
(SELECT lkup.meaning
FROM mfg_lookups lkup
WHERE lkup.lookup_type = 'MTL_MATERIAL_PLANNING'
AND lkup.lookup_code = TO_NUMBER(inv_planning_code)),
MRP_PLN_METHOD =
(SELECT lkup.meaning
FROM mfg_lookups lkup
WHERE lkup.lookup_type = 'MRP_PLANNING_CODE'
AND lkup.lookup_code = TO_NUMBER(mrp_pln_method)),
REVISION_CONTROL =
(SELECT lkup.meaning
FROM mfg_lookups lkup
WHERE lkup.lookup_type = 'MTL_ENG_QUANTITY'
AND lkup.lookup_code = TO_NUMBER(revision_control)),
SHELF_LIFE_CODE =
(SELECT lkup.meaning
FROM mfg_lookups lkup
WHERE lkup.lookup_type = 'MTL_SHELF_LIFE'
AND lkup.lookup_code = TO_NUMBER(shelf_life_code)),
SERIAL_CONTROL =
(SELECT lkup.meaning
FROM mfg_lookups lkup
WHERE lkup.lookup_type = 'MTL_SERIAL_NUMBER'
AND lkup.lookup_code = TO_NUMBER(serial_control)),
CATSET_CATEGORY_FK_KEY = NULL,
PROD_FAMILY_FK_KEY = NULL,
CATSET_CATEGORY_FK = NVL(CATSET_CATEGORY_FK, 'NA_EDW'),
PROD_FAMILY_FK = NVL(PROD_FAMILY_FK, 'NA_EDW');
edw_log.put_line('Inserting TEMP table records into staging table');
INSERT INTO EDW_ITEM_ITEMORG_LSTG(
APPROVED_SUPPLIER,
BUYER_FK,
CREATION_DATE,
CATSET_CATEGORY_FK,
CATSET_CATEGORY_FK_KEY,
DESCRIPTION,
ERROR_CODE,
EXPRS_DELIVERY,
HAZARD_CLASS_ID,
INSP_REQUIRED,
INSTANCE,
INTERNAL_ORD_FLAG,
INV_PLANNING_CODE,
ITEM_NUMBER,
ITEM_NUMBER_FK,
ITEM_NUMBER_FK_KEY,
ITEM_ORG_DP,
ITEM_ORG_PK,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LOCATOR_CONTROL,
EFFECTIVITY_CONTROL,
LOT_CONTROL,
MAKE_OR_BUY_FLAG,
MARKET_PRICE,
MRP_PLN_METHOD,
NAME,
ONE_TIME_FLAG,
OUTSIDE_OP_FLAG,
PLANNER_FK,
PRICE_TOL_PERCENT,
PROD_FAMILY_FK,
PROD_FAMILY_FK_KEY,
PURCHASABLE_FLAG,
RECEIPT_REQUIRED,
REQUEST_ID,
REVISION_CONTROL,
RFQ_REQUIRED_FLAG,
ROW_ID,
SERIAL_CONTROL,
SHELF_LIFE_CODE,
SHELF_LIFE_DAYS,
STOCKABLE_FLAG,
SUBSTITUTE_RCPT,
TAXABLE_FLAG,
TAX_CODE,
UNIT_LIST_PRICE,
UNORDERED_RCPT,
UN_NUMBER_ID,
SEGMENT1,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
OPERATION_CODE,
COLLECTION_STATUS,
ITEM_TYPE) /* Enh# 2544906 */
SELECT
APPROVED_SUPPLIER,
BUYER_FK,
CREATION_DATE,
CATSET_CATEGORY_FK,
CATSET_CATEGORY_FK_KEY, --CATSET_CATEGORY_FK_KEY,
DESCRIPTION,
NULL, --ERROR_CODE,
EXPRS_DELIVERY,
HAZARD_CLASS_ID,
INSP_REQUIRED,
INSTANCE,
INTERNAL_ORD_FLAG,
SUBSTRB(INV_PLANNING_CODE, 1, 40),
SUBSTRB(ITEM_NUMBER, 1, 240),
ITEM_NUMBER_FK,
ITEM_NUMBER_FK_KEY, --ITEM_NUMBER_FK_KEY,
SUBSTRB(ITEM_ORG_DP, 1, 240),
ITEM_ORG_PK,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LOCATOR_CONTROL,
EFFECTIVITY_CONTROL,
LOT_CONTROL,
SUBSTRB(MAKE_OR_BUY_FLAG, 1, 40),
MARKET_PRICE,
MRP_PLN_METHOD,
SUBSTRB(NAME, 1, 320),
NULL, --ONE_TIME_FLAG,
OUTSIDE_OP_FLAG,
PLANNER_FK,
PRICE_TOL_PERCENT,
PROD_FAMILY_FK,
PROD_FAMILY_FK_KEY, --PROD_FAMILY_FK_KEY,
PURCHASABLE_FLAG,
RECEIPT_REQUIRED,
NULL, --REQUEST_ID,
REVISION_CONTROL,
RFQ_REQUIRED_FLAG,
NULL, --ROW_ID,
SERIAL_CONTROL,
SHELF_LIFE_CODE,
SHELF_LIFE_DAYS,
STOCKABLE_FLAG,
SUBSTITUTE_RCPT,
TAXABLE_FLAG,
TAX_CODE,
UNIT_LIST_PRICE,
UNORDERED_RCPT,
UN_NUMBER_ID,
SEGMENT1,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
NULL, -- OPERATION_CODE
'READY',
ITEM_TYPE /* Enh# 2544906 */
FROM EDW_ITEM_ITEMORG_TEMP;
INSERT INTO EDW_ITEM_ITEMORG_LSTG(
APPROVED_SUPPLIER,
BUYER_FK,
CREATION_DATE,
CATSET_CATEGORY_FK,
CATSET_CATEGORY_FK_KEY,
DESCRIPTION,
ERROR_CODE,
EXPRS_DELIVERY,
INSP_REQUIRED,
INSTANCE,
INTERNAL_ORD_FLAG,
INV_PLANNING_CODE,
ITEM_NUMBER,
ITEM_NUMBER_FK,
ITEM_NUMBER_FK_KEY,
ITEM_ORG_DP,
ITEM_ORG_PK,
LAST_UPDATE_DATE,
LOCATOR_CONTROL,
EFFECTIVITY_CONTROL,
LOT_CONTROL,
MAKE_OR_BUY_FLAG,
MARKET_PRICE,
MRP_PLN_METHOD,
NAME,
ONE_TIME_FLAG,
OUTSIDE_OP_FLAG,
PLANNER_FK,
PRICE_TOL_PERCENT,
PROD_FAMILY_FK,
PROD_FAMILY_FK_KEY,
PURCHASABLE_FLAG,
RECEIPT_REQUIRED,
REQUEST_ID,
REVISION_CONTROL,
RFQ_REQUIRED_FLAG,
ROW_ID,
SERIAL_CONTROL,
SHELF_LIFE_CODE,
SHELF_LIFE_DAYS,
STOCKABLE_FLAG,
SUBSTITUTE_RCPT,
TAXABLE_FLAG,
TAX_CODE,
UNIT_LIST_PRICE,
UNORDERED_RCPT,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
OPERATION_CODE,
COLLECTION_STATUS)
SELECT
APPROVED_SUPPLIER,
BUYER_FK,
CREATION_DATE,
'NA_EDW', --CATSET_CATEGORY_FK
NULL, --CATSET_CATEGORY_FK_KEY,
DESCRIPTION,
NULL, --ERROR_CODE,
EXPRS_DELIVERY,
INSP_REQUIRED,
l_instance,
INTERNAL_ORD_FLAG,
SUBSTRB(INV_PLANNING_CODE, 1, 40),
SUBSTRB(ITEM_NUMBER, 1, 240),
ITEM_NUMBER_FK || '-' || l_instance || '-ONETIME', -- ITEM_NUMBER_FK, /* Bug# 2558245 */
NULL, --ITEM_NUMBER_FK_KEY,
SUBSTRB(ITEM_ORG_DP, 1, 240),
ITEM_ORG_PK || '-' || l_instance || '-ONETIME', -- ITEM_ORG_PK, /* Bug# 2558245 */
LAST_UPDATE_DATE,
LOCATOR_CONTROL,
EFFECTIVITY_CONTROL,
LOT_CONTROL,
SUBSTRB(MAKE_OR_BUY_FLAG, 1, 40),
MARKET_PRICE,
MRP_PLN_METHOD,
SUBSTRB(NAME, 1, 320),
ONE_TIME_FLAG,
OUTSIDE_OP_FLAG,
PLANNER_FK,
PRICE_TOL_PERCENT,
NVL(PROD_FAMILY_FK, 'NA_EDW'),
NULL, --PROD_FAMILY_FK_KEY,
PURCHASABLE_FLAG,
RECEIPT_REQUIRED,
NULL, --REQUEST_ID,
REVISION_CONTROL,
RFQ_REQUIRED_FLAG,
NULL, --ROW_ID,
SERIAL_CONTROL,
SHELF_LIFE_CODE,
SHELF_LIFE_DAYS,
STOCKABLE_FLAG,
SUBSTITUTE_RCPT,
TAXABLE_FLAG,
TAX_CODE,
UNIT_LIST_PRICE,
UNORDERED_RCPT,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
NULL, -- OPERATION_CODE
'READY'
FROM EDW_ITEM_ONETIME_ITEMORG_LCV
WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
l_rows_inserted := SQL%ROWCOUNT;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
' rows into the staging table');
l_rows_inserted := 0;
INSERT INTO EDW_ITEM_ITEMREV_LSTG(
ITEM_REVISION_PK,
ITEM_ORG_FK,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
NAME,
INSTANCE,
COLLECTION_STATUS)
SELECT
SUBSTRB(ITEM_ORG_PK || '-' || l_instance, 1, 315)|| '-IORG', /* Bug# 2558245 */
SUBSTRB(ITEM_ORG_PK || '-' || l_instance, 1, 320), /* Bug# 2558245 */
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SUBSTRB(l_all_item_revs || ' (' || NAME || ')', 1, 320),
l_instance,
'READY'
FROM EDW_ITEM_ITEMORG_LCV
WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
l_rows_inserted := SQL%ROWCOUNT;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
' rows into the staging table');
EDW_ITEMS_M_C.g_row_count:=EDW_ITEMS_M_C.g_row_count+l_rows_inserted;
l_rows_inserted := 0;
INSERT INTO EDW_ITEM_ITEMREV_LSTG(
ITEM_ORG_FK,
ITEM_REVISION_PK,
NAME,
INSTANCE,
COLLECTION_STATUS)
SELECT
SUBSTRB(ITEM_ORG_PK || '-' || l_instance,1,312) || '-ONETIME', /* Bug# 2558245 */
SUBSTRB(ITEM_ORG_PK || '-' || l_instance,1,307) || '-ONETIME' || '-IORG',
SUBSTRB(l_all_item_revs || '(' || NAME || ')',1,320),
l_instance,
'READY' --COLLECTION_STATUS
FROM EDW_ITEM_ONETIME_ITEMORG_LCV
WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
l_rows_inserted := SQL%ROWCOUNT;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
' rows into the staging table');
EDW_ITEMS_M_C.g_row_count:=EDW_ITEMS_M_C.g_row_count+l_rows_inserted;
l_rows_inserted NUMBER := 0;
SELECT instance_code
FROM edw_local_instance;
SELECT
MIC.INVENTORY_ITEM_ID,
MIC.ORGANIZATION_ID
FROM
MTL_ITEM_CATEGORIES MIC,
MTL_CATEGORY_SETS_TL SETS
WHERE MIC.CATEGORY_SET_ID = SETS.CATEGORY_SET_ID
AND MIC.LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date
AND SETS.LANGUAGE = userenv('LANG')
AND SETS.CATEGORY_SET_NAME IN (l_item_catset1_name,
l_item_catset2_name,
l_item_catset3_name);
AND MIC.LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date
AND SETS.CATEGORY_SET_NAME IN (l_item_catset1_name,
l_item_catset2_name,
l_item_catset3_name);
INSERT INTO EDW_ITEM_ITEM_TEMP(
CREATION_DATE,
CATSET1_CATEGORY_FK,
CATSET1_CATEGORY_FK_KEY,
CATSET2_CATEGORY_FK,
CATSET2_CATEGORY_FK_KEY,
CATSET3_CATEGORY_FK,
CATSET3_CATEGORY_FK_KEY,
DESCRIPTION,
ERROR_CODE,
INSTANCE,
ITEM_NAME,
ITEM_NUMBER_DP,
ITEM_NUMBER_PK,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
NAME,
ONE_TIME_FLAG,
PRODUCT_GROUP_FK,
PRODUCT_GROUP_FK_KEY,
REQUEST_ID,
ROW_ID,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
OPERATION_CODE,
COLLECTION_STATUS,
ITEM_TYPE) /* Enh# 2544906 */
SELECT
CREATION_DATE,
CATSET1_CATEGORY_FK,
INVENTORY_ITEM_ID, -- CATSET1_CATEGORY_FK_KEY
CATSET2_CATEGORY_FK,
NULL, -- CATSET2_CATEGORY_FK_KEY,
CATSET3_CATEGORY_FK,
NULL, -- CATSET3_CATEGORY_FK_KEY,
DESCRIPTION,
NULL, --ERROR_CODE,
l_instance, --INSTANCE, /* Bug# 2558245 */
ITEM_NAME,
ITEM_NUMBER_DP,
ITEM_NUMBER_PK || '-' || l_instance, -- ITEM_NUMBER_PK, /* Bug# 2558245 */
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
SUBSTRB(NAME, 1, 320),
NULL, --ONE_TIME_FLAG,
PRODUCT_GROUP_FK,
ORGANIZATION_ID, --PRODUCT_GROUP_FK_KEY,
NULL, --REQUEST_ID,
NULL, --ROW_ID,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
NULL, -- OPERATION_CODE
'READY',
ITEM_TYPE /* Enh# 2544906 */
FROM EDW_ITEM_ITEM_LCV
WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
l_rows_inserted := l_rows_inserted + SQL%ROWCOUNT;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
' rows into the staging table');
/** Need to INSERT additional items for whom the category assignments
** have changed.
*/
edw_log.put_line('Inserting additional items due to category changes ');
INSERT INTO EDW_ITEM_ITEM_TEMP(
CREATION_DATE,
CATSET1_CATEGORY_FK,
CATSET1_CATEGORY_FK_KEY,
CATSET2_CATEGORY_FK,
CATSET2_CATEGORY_FK_KEY,
CATSET3_CATEGORY_FK,
CATSET3_CATEGORY_FK_KEY,
DESCRIPTION,
ERROR_CODE,
INSTANCE,
ITEM_NAME,
ITEM_NUMBER_DP,
ITEM_NUMBER_PK,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
NAME,
ONE_TIME_FLAG,
PRODUCT_GROUP_FK,
PRODUCT_GROUP_FK_KEY,
REQUEST_ID,
ROW_ID,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
OPERATION_CODE,
COLLECTION_STATUS,
ITEM_TYPE) /* Enh# 2544906 */
SELECT
CREATION_DATE,
CATSET1_CATEGORY_FK,
eil.INVENTORY_ITEM_ID, -- CATSET1_CATEGORY_FK_KEY
CATSET2_CATEGORY_FK,
NULL, -- CATSET2_CATEGORY_FK_KEY,
CATSET3_CATEGORY_FK,
NULL, -- CATSET3_CATEGORY_FK_KEY,
DESCRIPTION,
NULL, --ERROR_CODE,
l_instance, --INSTANCE, /* Bug# 2558245 */
ITEM_NAME,
ITEM_NUMBER_DP,
ITEM_NUMBER_PK || '-' || l_instance, -- ITEM_NUMBER_PK, /* Bug# 2558245 */
eil.INVENTORY_ITEM_ID,
eil.ORGANIZATION_ID,
LAST_UPDATE_DATE,
SUBSTRB(NAME, 1, 320),
NULL, --ONE_TIME_FLAG,
PRODUCT_GROUP_FK,
eil.ORGANIZATION_ID, --PRODUCT_GROUP_FK_KEY,
NULL, --REQUEST_ID,
NULL, --ROW_ID,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
NULL, -- OPERATION_CODE
'READY',
ITEM_TYPE /* Enh# 2544906 */
FROM EDW_ITEM_ITEM_LCV eil,
(
SELECT
MIC.INVENTORY_ITEM_ID,
MIC.ORGANIZATION_ID
FROM
MTL_ITEM_CATEGORIES MIC,
MTL_CATEGORY_SETS_TL SETS
WHERE MIC.CATEGORY_SET_ID = SETS.CATEGORY_SET_ID
AND MIC.LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date
AND SETS.LANGUAGE = userenv('LANG')
AND SETS.CATEGORY_SET_NAME IN (l_item_catset1_name,
l_item_catset2_name,
l_item_catset3_name)) category_assignments_rec
WHERE eil.inventory_item_id = category_assignments_rec.inventory_item_id
AND eil.organization_id = category_assignments_rec.organization_id
AND last_update_date not BETWEEN l_push_date_range1 AND l_push_date_range2; /* Bug# 2659263 */
edw_log.put_line('Done Inserting category changed items into item ');
UPDATE EDW_ITEM_ITEM_TEMP
SET CATSET1_CATEGORY_FK =
(SELECT DECODE(COUNT(cat.category_id), 1, TO_CHAR(2)||'-'||MAX(cat.category_id) || '-' || l_instance, 'NA_EDW')
FROM
MTL_ITEM_CATEGORIES cat,
MTL_CATEGORY_SETS sets
WHERE cat.organization_id = product_group_fk_key
AND cat.inventory_item_id = catset1_category_fk_key
-- AND sets.control_level = 1 Bug : 3720586
AND sets.category_set_name = l_item_catset1_name
AND cat.category_set_id = sets.category_set_id),
CATSET2_CATEGORY_FK =
(SELECT DECODE(COUNT(cat.category_id), 1, TO_CHAR(2)||'-'||MAX(cat.category_id) || '-' || l_instance, 'NA_EDW')
FROM
MTL_ITEM_CATEGORIES cat,
MTL_CATEGORY_SETS sets
WHERE cat.organization_id = product_group_fk_key
AND cat.inventory_item_id = catset1_category_fk_key
AND sets.control_level = 1
AND sets.category_set_name = l_item_catset2_name
AND cat.category_set_id = sets.category_set_id),
CATSET3_CATEGORY_FK = edw_itemcustom_m_c.get_product_category_set_fk(catset1_category_fk_key,
product_group_fk_key, instance),
PRODUCT_GROUP_FK = edw_items_pkg.get_prod_grp_fk(catset1_category_fk_key,
product_group_fk_key, instance);
UPDATE EDW_ITEM_ITEM_TEMP
SET CATSET1_CATEGORY_FK =
(SELECT DECODE(COUNT(cat.category_id), 1, TO_CHAR(2)||'-'||MAX(cat.category_id) || '-' || l_instance, 'NA_EDW')
FROM
MTL_ITEM_CATEGORIES cat,
MTL_CATEGORY_SETS sets
WHERE cat.organization_id = product_group_fk_key
AND cat.inventory_item_id = catset1_category_fk_key
-- AND sets.control_level = 1 Bug : 3720586
AND sets.category_set_name = l_item_catset1_name
AND cat.category_set_id = sets.category_set_id),
CATSET2_CATEGORY_FK =
(SELECT DECODE(COUNT(cat.category_id), 1, TO_CHAR(2)||'-'||MAX(cat.category_id) || '-' || l_instance, 'NA_EDW')
FROM
MTL_ITEM_CATEGORIES cat,
MTL_CATEGORY_SETS sets
WHERE cat.organization_id = product_group_fk_key
AND cat.inventory_item_id = catset1_category_fk_key
AND sets.control_level = 1
AND sets.category_set_name = l_item_catset2_name
AND cat.category_set_id = sets.category_set_id),
CATSET3_CATEGORY_FK =
(SELECT DECODE(COUNT(cat.category_id), 1, TO_CHAR(2)||'-'||MAX(cat.category_id) || '-' || l_instance, 'NA_EDW')
FROM
MTL_ITEM_CATEGORIES cat,
MTL_CATEGORY_SETS sets
WHERE cat.organization_id = product_group_fk_key
AND cat.inventory_item_id = catset1_category_fk_key
AND sets.control_level = 1
AND sets.category_set_name = l_item_catset3_name
AND cat.category_set_id = sets.category_set_id),
PRODUCT_GROUP_FK = edw_items_pkg.get_prod_grp_fk(catset1_category_fk_key,
product_group_fk_key, instance);
UPDATE EDW_ITEM_ITEM_TEMP
SET
CATSET1_CATEGORY_FK = NVL(CATSET1_CATEGORY_FK, 'NA_EDW'),
CATSET2_CATEGORY_FK = NVL(CATSET2_CATEGORY_FK, 'NA_EDW'),
CATSET3_CATEGORY_FK = NVL(CATSET3_CATEGORY_FK, 'NA_EDW'),
PRODUCT_GROUP_FK = NVL(PRODUCT_GROUP_FK, 'NA_EDW'),
PRODUCT_GROUP_FK_KEY = NULL,
CATSET1_CATEGORY_FK_KEY = NULL;
INSERT INTO EDW_ITEM_ITEM_LSTG(
CREATION_DATE,
CATSET1_CATEGORY_FK,
CATSET1_CATEGORY_FK_KEY,
CATSET2_CATEGORY_FK,
CATSET2_CATEGORY_FK_KEY,
CATSET3_CATEGORY_FK,
CATSET3_CATEGORY_FK_KEY,
DESCRIPTION,
ERROR_CODE,
INSTANCE,
ITEM_NAME,
ITEM_NUMBER_DP,
ITEM_NUMBER_PK,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
NAME,
ONE_TIME_FLAG,
PRODUCT_GROUP_FK,
PRODUCT_GROUP_FK_KEY,
REQUEST_ID,
ROW_ID,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
OPERATION_CODE,
COLLECTION_STATUS,
ITEM_TYPE) /* Enh# 2544906 */
SELECT
CREATION_DATE,
CATSET1_CATEGORY_FK,
NULL, --CATSET1_CATEGORY_FK_KEY
CATSET2_CATEGORY_FK,
NULL, --CATSET2_CATEGORY_FK_KEY
CATSET3_CATEGORY_FK,
NULL, --CATSET3_CATEGORY_FK_KEY
DESCRIPTION,
NULL, --ERROR_CODE,
INSTANCE,
ITEM_NAME,
ITEM_NUMBER_DP,
ITEM_NUMBER_PK,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
SUBSTRB(NAME, 1, 320),
NULL, --ONE_TIME_FLAG,
PRODUCT_GROUP_FK, --PRODUCT_FAMILY_FK
NULL, --PRODUCT_GROUP_FK_KEY,
NULL, --REQUEST_ID,
NULL, --ROW_ID,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
NULL, -- OPERATION_CODE
'READY',
ITEM_TYPE /* Enh# 2544906 */
FROM EDW_ITEM_ITEM_TEMP;
INSERT INTO edw_item_itemorg_lstg(
ITEM_ORG_PK,
ITEM_NUMBER_FK,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
NAME,
INSTANCE,
COLLECTION_STATUS,
CATSET_CATEGORY_FK,
PROD_FAMILY_FK)
SELECT
SUBSTRB(ITEM_NUMBER_PK || '-' || l_instance ||'-ITEM', 1, 1000), /* Bug# 2558245 */
ITEM_NUMBER_PK || '-' || l_instance, /* Bug# 2558245 */
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SUBSTRB(l_all_item_orgs || '(' || NAME || ')', 1, 320),
l_instance, --INSTANCE, /* Bug# 2558245 */
'READY',
'NA_EDW',
'NA_EDW'
FROM edw_item_item_lcv
WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
l_rows_inserted := SQL%ROWCOUNT;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
' rows into the staging table');
INSERT INTO edw_item_itemrev_lstg(
ITEM_REVISION_PK,
ITEM_ORG_FK,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
NAME,
INSTANCE,
COLLECTION_STATUS)
SELECT
SUBSTRB(ITEM_NUMBER_PK || '-' || l_instance , 1, 315) || '-ITEM', /* Bug# 2558245 */
SUBSTRB(ITEM_NUMBER_PK || '-' || l_instance , 1, 315) || '-ITEM', /* Bug# 2558245 */
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SUBSTRB(l_all_item_revs || '(' || NAME || ')', 1, 320),
l_instance, --INSTANCE, /* Bug# 2558245 */
'READY'
FROM edw_item_item_lcv
WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
l_rows_inserted := SQL%ROWCOUNT;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
' rows into the staging table');
EDW_ITEMS_M_C.g_row_count:=EDW_ITEMS_M_C.g_row_count+l_rows_inserted;
INSERT INTO EDW_ITEM_ITEM_LSTG(
CREATION_DATE,
CATSET1_CATEGORY_FK,
CATSET1_CATEGORY_FK_KEY,
CATSET2_CATEGORY_FK,
CATSET2_CATEGORY_FK_KEY,
CATSET3_CATEGORY_FK,
CATSET3_CATEGORY_FK_KEY,
DESCRIPTION,
ERROR_CODE,
INSTANCE,
ITEM_NAME,
ITEM_NUMBER_DP,
ITEM_NUMBER_PK,
LAST_UPDATE_DATE,
NAME,
ONE_TIME_FLAG,
PRODUCT_GROUP_FK,
PRODUCT_GROUP_FK_KEY,
REQUEST_ID,
ROW_ID,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
OPERATION_CODE,
COLLECTION_STATUS)
SELECT
CREATION_DATE,
TO_CHAR(2)||'-'||CATSET_CATEGORY_FK||'-'||l_instance, -- Bug# 2848291 added l_instance
NULL, --CATSET1_CATEGORY_FK_KEY,
'NA_EDW',
NULL, --CATSET2_CATEGORY_FK_KEY,
'NA_EDW',
NULL, --CATSET3_CATEGORY_FK_KEY,
DESCRIPTION,
NULL, --ERROR_CODE,
l_instance, --INSTANCE, /* Bug# 2558245 */
SUBSTRB(ITEM_NAME, 1, 240),
SUBSTRB(ITEM_NUMBER_DP, 1, 240),
ITEM_NUMBER_PK || '-' || l_instance || '-ONETIME', -- ITEM_NUMBER_PK, /* Bug# 2558245 */
LAST_UPDATE_DATE,
SUBSTRB(NAME, 1, 320),
ONE_TIME_FLAG,
NVL(PRODUCT_GROUP_FK, 'NA_EDW'),
NULL, --PRODUCT_GROUP_FK_KEY,
NULL, --REQUEST_ID,
NULL, --ROW_ID,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
OPERATION_CODE,
'READY'
FROM EDW_ITEM_ONETIME_ITEM_LCV
WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
l_rows_inserted := SQL%ROWCOUNT;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
' rows into the staging table');
INSERT INTO edw_item_itemorg_lstg(
ITEM_ORG_PK,
ITEM_NUMBER_FK,
NAME,
INSTANCE,
COLLECTION_STATUS,
CATSET_CATEGORY_FK,
PROD_FAMILY_FK)
SELECT
SUBSTRB(ITEM_NUMBER_PK || '-' || l_instance,1,987)||'-ONETIME'||'-ITEM', /* Bug# 2558245 */
ITEM_NUMBER_PK || '-' || l_instance || '-ONETIME', /* Bug# 2558245 */
SUBSTRB(l_all_item_orgs || '(' || NAME || ')', 1, 320),
l_instance, --INSTANCE, /* Bug# 2558245 */
'READY',
'NA_EDW',
'NA_EDW'
FROM edw_item_onetime_item_lcv
WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
l_rows_inserted := SQL%ROWCOUNT;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
' rows into the staging table');
INSERT INTO edw_item_itemrev_lstg(
ITEM_REVISION_PK,
ITEM_ORG_FK,
NAME,
INSTANCE,
COLLECTION_STATUS)
SELECT
SUBSTRB(ITEM_NUMBER_PK || '-' || l_instance,1,307) || '-ONETIME' || '-ITEM', /* Bug# 2558245 */
SUBSTRB(ITEM_NUMBER_PK || '-' || l_instance,1,307) || '-ONETIME' || '-ITEM', /* Bug# 2558245 */
SUBSTRB(l_all_item_revs || '(' || NAME || ')', 1, 320),
l_instance, --INSTANCE, /* Bug# 2558245 */
'READY'
FROM edw_item_onetime_item_lcv
WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2;
l_rows_inserted := SQL%ROWCOUNT;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
' rows into the staging table');
EDW_ITEMS_M_C.g_row_count:=EDW_ITEMS_M_C.g_row_count+l_rows_inserted;
PROCEDURE Insert_Category(
p_from_date DATE ,
p_to_date DATE ,
p_staging_table_name VARCHAR2,
p_view_name VARCHAR2,
p_category_set_name VARCHAR2,
p_control_level NUMBER) IS
l_stmt VARCHAR2(5000) := NULL;
l_rows_inserted NUMBER := 0;
l_where_clause := ' WHERE last_update_date BETWEEN :l_push_date_range1 AND :l_push_date_range2 AND category_set_name = :l_category_set_name';
l_stmt := 'SELECT count(*) row_cnt FROM '||p_view_name||l_where_clause;
l_rows_inserted:=dbms_sql.execute_and_fetch(l_cursor, true);
edw_log.put_line('No rows fetched for insert into '||p_staging_table_name);
l_rows_inserted := 0;
edw_log.debug_line('VBH insert - Pulling from prior level staging table');
ELSE -- Selecting from level other than 10, hence take FK itself
l_fk_value := 'CATEGORY_FK';
l_where_clause := ' WHERE last_update_date BETWEEN :l_push_date_range1 AND :l_push_date_range2 ' ||
' AND category_set_name = :l_category_set_name';
edw_log.debug_line('Constructing insert stmt');
l_stmt:= 'INSERT INTO '||p_staging_table_name||' ('||
l_fk_name || ','||
l_fk_key || ','||
' CATEGORY_NAME,'||
' CATEGORY_SET_NAME,'||
' CREATION_DATE,' ||
' DESCRIPTION,'||
' ERROR_CODE,' ||
' INSTANCE,' ||
' CATEGORY_DP,'||
' CATEGORY_PK,'||
' CATEGORY_ID,'||
' CATEGORY_SET_ID,'||
' LAST_UPDATE_DATE,' ||
' NAME,' ||
' REQUEST_ID,' ||
' ROW_ID,' ||
' USER_ATTRIBUTE1,' ||
' USER_ATTRIBUTE2,' ||
' USER_ATTRIBUTE3,' ||
' USER_ATTRIBUTE4,' ||
' USER_ATTRIBUTE5,' ||
' OPERATION_CODE,' ||
' COLLECTION_STATUS ) '||
' SELECT '||
l_fk_value || ','||
' NULL,' ||
' NULL,' ||
' CATEGORY_SET_NAME,'||
' CREATION_DATE,' ||
' DESCRIPTION,'||
' NULL,' ||
'''' || l_instance || '''' || ',' || /* Bug# 2558245 */
' CATEGORY_DP,' ||
l_pk_value || ','||
' CATEGORY_ID,'||
' CATEGORY_SET_ID,'||
' LAST_UPDATE_DATE,' ||
' SUBSTRB(NAME, 1, 320),' ||
' NULL,' ||
' NULL,' ||
' USER_ATTRIBUTE1,' ||
' USER_ATTRIBUTE2,' ||
' USER_ATTRIBUTE3,' ||
' USER_ATTRIBUTE4,' ||
' USER_ATTRIBUTE5,' ||
' NULL,' ||
'''READY'''||
' FROM '||l_view_name||l_where_clause; -- Bug# 3296641
l_rows_inserted := SQL%ROWCOUNT ;
edw_log.debug_line('Bind l_category_set_name variable, and last_update dates');
l_rows_inserted:=dbms_sql.execute(l_cursor);
edw_log.put_line('Inserted '||TO_CHAR(l_rows_inserted)||
' rows into the staging table');
END Insert_Category;
PROCEDURE Insert_VBH_Category(
p_from_date DATE,
p_to_date DATE,
p_view_name VARCHAR2,
p_category_set_name VARCHAR2,
p_no_of_catset_lvls NUMBER,
p_hrchy_top_node VARCHAR2,
p_control_level NUMBER) IS
l_stmt VARCHAR2(7000) := NULL;
l_rows_inserted NUMBER := 0;
SELECT FLEX_VALUE_SET_ID, APPLICATION_COLUMN_NAME
FROM FND_ID_FLEX_SEGMENTS
WHERE APPLICATION_ID = '401'
AND ID_FLEX_CODE = 'MCAT'
AND ID_FLEX_NUM =
(SELECT STRUCTURE_ID
FROM MTL_CATEGORY_SETS_VL
WHERE CATEGORY_SET_ID = c_category_set_id)
AND ENABLED_FLAG = 'Y';
SELECT CONTROL_LEVEL, MULT_ITEM_CAT_ASSIGN_FLAG, STRUCTURE_ID
FROM MTL_CATEGORY_SETS_VL
WHERE CATEGORY_SET_ID = c_category_set_id;
SELECT 'X' INTO l_struct_code
FROM FND_ID_FLEX_STRUCTURES_VL
WHERE ID_FLEX_NUM = l_structure_id
AND ID_FLEX_STRUCTURE_CODE = 'PRODUCT_CATEGORIES'
AND APPLICATION_ID = '401'
AND ID_FLEX_CODE = 'MCAT';
l_where_clause := ' WHERE LCV_VIEW.LAST_UPDATE_DATE BETWEEN :l_push_date_range1'||
' AND :l_push_date_range2'||
' AND LCV_VIEW.CATEGORY_SET_NAME = :l_category_set_name'||
' AND LCV_VIEW.CATEGORY_ID = MTC.CATEGORY_ID'||
' AND MTC.'||l_segment_num||' = FFVC.FLEX_VALUE'||
' AND FFVC.FLEX_VALUE_SET_ID = :l_value_set_id'||
' AND FFVC.PARENT_FLEX_VALUE = :l_hrchy_top_node';
l_where_clause := ' where LCV_VIEW.LAST_UPDATE_DATE between :l_push_date_range1' ||
' and :l_push_date_range2' ||
' and LCV_VIEW.CATEGORY_SET_NAME = :l_category_set_name' ||
' and LCV_VIEW.NAME = FFVC.FLEX_VALUE' ||
' and FFVC.FLEX_VALUE_SET_ID = :l_value_set_id' ||
' and FFVC.PARENT_FLEX_VALUE = :l_hrchy_top_node';
THEN /* hence SELECT FROM AND INSERT INTO lowest lvl table */
l_prior_staging_table_name := l_staging_table_name;
l_where_clause := ' WHERE LCV_VIEW.LAST_UPDATE_DATE BETWEEN :l_push_date_range1'||
' AND :l_push_date_range2'||
' AND LCV_VIEW.CATEGORY_SET_NAME = :l_category_set_name'||
' AND LCV_VIEW.CATEGORY_ID = MTC.CATEGORY_ID'||
' AND MTC.'||l_segment_num||' = FFVC.FLEX_VALUE'||
' AND FFVC.FLEX_VALUE_SET_ID = :l_value_set_id'||
' AND FFVC.PARENT_FLEX_VALUE = PARENT_MTC.'||l_segment_num||
' AND PARENT_MTC.CATEGORY_ID = PARENT_LCV_VIEW.CATEGORY_ID'||
' AND PARENT_LCV_VIEW.CATEGORY_SET_NAME = :l_category_set_name'||
' AND PARENT_LCV_VIEW.CATEGORY_PK || '||''''||'-'||l_instance||'''' ||
'= SUBSTRB(PARENT_STAGING.CATEGORY_PK,3)'||
' AND PARENT_STAGING.COLLECTION_STATUS = ''READY''';
l_where_clause := ' where LCV_VIEW.LAST_UPDATE_DATE between :l_push_date_range1' ||
' and :l_push_date_range2' ||
' and LCV_VIEW.CATEGORY_SET_NAME = :l_category_set_name' ||
' and LCV_VIEW.NAME = FFVC.FLEX_VALUE' ||
' and FFVC.FLEX_VALUE_SET_ID = :l_value_set_id' ||
' and FFVC.PARENT_FLEX_VALUE = PARENT_STAGING.NAME' ||
' and PARENT_STAGING.COLLECTION_STATUS = ''READY''';
THEN /* don't SELECT children that already exist in staging table */
l_not_exists_clause := ' AND not exists '||
' (SELECT NULL '||
' FROM '||l_staging_table_name||' STAGING'||
' WHERE SUBSTRB(STAGING.CATEGORY_PK,3) = LCV_VIEW.CATEGORY_PK ||'||''''||'-'||l_instance||'''' || /* Bug# 2558245 */
' AND STAGING.COLLECTION_STATUS = ''READY'')';
l_stmt:= 'INSERT INTO '||l_staging_table_name||' ('||
l_fk_name || ','||
l_fk_key || ','||
' CATEGORY_NAME,'||
' CATEGORY_SET_NAME,'||
' CREATION_DATE,' ||
' DESCRIPTION,'||
' ERROR_CODE,' ||
' INSTANCE,' ||
' CATEGORY_DP,'||
' CATEGORY_PK,'||
' CATEGORY_ID,'||
' CATEGORY_SET_ID,'||
' LAST_UPDATE_DATE,' ||
' NAME,' ||
' REQUEST_ID,' ||
' ROW_ID,' ||
' USER_ATTRIBUTE1,' ||
' USER_ATTRIBUTE2,' ||
' USER_ATTRIBUTE3,' ||
' USER_ATTRIBUTE4,' ||
' USER_ATTRIBUTE5,' ||
' OPERATION_CODE,' ||
' COLLECTION_STATUS ) '||
' SELECT '||
l_fk_value || ','||
' NULL,' ||
' NULL,' ||
' LCV_VIEW.CATEGORY_SET_NAME,'||
' LCV_VIEW.CREATION_DATE,' ||
' FFVC.DESCRIPTION,'||
' NULL,' ||
'''' || l_instance || '''' || ',' || /* Bug# 2558245 */
' LCV_VIEW.CATEGORY_DP,' ||
''''||TO_CHAR(p_control_level)||'-'||''''||'||'|| 'LCV_VIEW.CATEGORY_PK' ||'||' ||''''||'-'||l_instance||''',' || /* Bug# 2558245 */
' LCV_VIEW.CATEGORY_ID,'||
' LCV_VIEW.CATEGORY_SET_ID,'||
' LCV_VIEW.LAST_UPDATE_DATE,' ||
' SUBSTRB(LCV_VIEW.NAME, 1, 320),' ||
' NULL,' ||
' NULL,' ||
' LCV_VIEW.USER_ATTRIBUTE1,' ||
' LCV_VIEW.USER_ATTRIBUTE2,' ||
' LCV_VIEW.USER_ATTRIBUTE3,' ||
' LCV_VIEW.USER_ATTRIBUTE4,' ||
' LCV_VIEW.USER_ATTRIBUTE5,' ||
' NULL,' ||
'''READY'''||
l_from_clause||
l_where_clause;
l_rows_inserted := SQL%ROWCOUNT ;
edw_log.debug_line('Bind l_category_set_name variable, and last_update dates');
l_rows_inserted:=dbms_sql.execute(l_cursor);
edw_log.put_line('Inserted '||TO_CHAR(l_rows_inserted)||
' rows into the staging table');
IF l_rows_inserted = 0 THEN
l_lower_lvl_exists := FALSE;
END Insert_VBH_Category;
PROCEDURE INSERT_CATEGORY_HIERARCHY(
p_from_date DATE,
p_to_date DATE,
p_view_name VARCHAR2,
p_category_set_name VARCHAR2,
p_category_set_id NUMBER,
p_no_of_catset_lvls NUMBER,
p_control_level NUMBER) IS
l_stmt VARCHAR2(7000) := NULL;
l_rows_inserted NUMBER := 0;
SELECT CONTROL_LEVEL, MULT_ITEM_CAT_ASSIGN_FLAG, VALIDATE_FLAG
FROM MTL_CATEGORY_SETS_VL
WHERE CATEGORY_SET_ID = c_category_set_id;
THEN /* hence SELECT FROM AND INSERT INTO lowest lvl table */
l_prior_staging_table_name := l_staging_table_name;
THEN /* don't SELECT children that already exist in staging table */
l_not_exists_clause := ' AND not exists '||
' (SELECT NULL '||
' FROM '||l_staging_table_name||' STAGING'||
' WHERE SUBSTRB(STAGING.CATEGORY_PK,3) = LCV_VIEW.CATEGORY_PK ||'||''''||'-'||l_instance||'''' || /* Bug# 2558245 */
' AND STAGING.COLLECTION_STATUS = ''READY'')';
l_stmt:= 'INSERT INTO '||l_staging_table_name||' ('||
l_fk_name || ','||
l_fk_key || ','||
' CATEGORY_NAME,'||
' CATEGORY_SET_NAME,'||
' CREATION_DATE,' ||
' DESCRIPTION,'||
' ERROR_CODE,' ||
' INSTANCE,' ||
' CATEGORY_DP,'||
' CATEGORY_PK,'||
' CATEGORY_ID,'||
' CATEGORY_SET_ID,'||
' LAST_UPDATE_DATE,' ||
' NAME,' ||
' REQUEST_ID,' ||
' ROW_ID,' ||
' USER_ATTRIBUTE1,' ||
' USER_ATTRIBUTE2,' ||
' USER_ATTRIBUTE3,' ||
' USER_ATTRIBUTE4,' ||
' USER_ATTRIBUTE5,' ||
' OPERATION_CODE,' ||
' COLLECTION_STATUS ) '||
' SELECT '||
l_fk_value || ','||
' NULL,' ||
' NULL,' ||
' LCV_VIEW.CATEGORY_SET_NAME,'||
' LCV_VIEW.CREATION_DATE,' ||
' LCV_VIEW.DESCRIPTION,'||
' NULL,' ||
'''' || l_instance || '''' || ',' || /* Bug# 2558245 */
' LCV_VIEW.CATEGORY_DP,' ||
''''||TO_CHAR(p_control_level)||'-'||''''||'||'|| 'LCV_VIEW.CATEGORY_PK' ||'||' ||''''||'-'||l_instance||''',' || /* Bug# 2558245 */
' LCV_VIEW.CATEGORY_ID,'||
' LCV_VIEW.CATEGORY_SET_ID,'||
' LCV_VIEW.LAST_UPDATE_DATE,' ||
' SUBSTRB(LCV_VIEW.NAME, 1, 320),' ||
' NULL,' ||
' NULL,' ||
' LCV_VIEW.USER_ATTRIBUTE1,' ||
' LCV_VIEW.USER_ATTRIBUTE2,' ||
' LCV_VIEW.USER_ATTRIBUTE3,' ||
' LCV_VIEW.USER_ATTRIBUTE4,' ||
' LCV_VIEW.USER_ATTRIBUTE5,' ||
' NULL,' ||
'''READY'''||
l_from_clause||
l_where_clause;
l_rows_inserted := SQL%ROWCOUNT ;
edw_log.debug_line('Bind l_category_set_name variable, and last_update dates');
l_rows_inserted:=dbms_sql.execute(l_cursor);
edw_log.put_line('Inserted '||TO_CHAR(l_rows_inserted)||
' rows into the staging table');
IF l_rows_inserted = 0 THEN
l_lower_lvl_exists := FALSE;
END INSERT_CATEGORY_HIERARCHY;
l_rows_inserted NUMBER := 0;
SELECT
CATEGORY_PK || '-' || l_instance CATEGORY_PK, /* Bug# 2558245 */
CREATION_DATE,
l_instance INSTANCE, -- INSTANCE /* Bug# 2558245 */
'NA_EDW' ITEM_ORG_FK,
NULL ITEM_ORG_FK_KEY,
NULL ITEM_REVISION,
CATEGORY_ID,
CATEGORY_SET_ID,
LAST_UPDATE_DATE,
SUBSTRB('(' || NAME || ')', 1, 320) NAME,
'READY' COLLECTION_STATUS
FROM edw_item_item_org_cat_lcv
WHERE last_update_date BETWEEN l_push_date_range1 AND l_push_date_range2 AND category_set_name = c_category_set_name;
SELECT
CATEGORY_PK || '-' || l_instance CATEGORY_PK, /* Bug# 2558245 */
CREATION_DATE,
l_instance INSTANCE, -- INSTANCE /* Bug# 2558245 */
'NA_EDW' ITEM_ORG_FK,
NULL ITEM_ORG_FK_KEY,
NULL ITEM_REVISION,
CATEGORY_ID,
CATEGORY_SET_ID,
LAST_UPDATE_DATE,
SUBSTRB('(' || NAME || ')', 1, 320) NAME,
'READY' COLLECTION_STATUS
FROM edw_item_item_org_cat_lcv
WHERE category_set_name = c_category_set_name;
edw_log.put_line('Determining the collection view to SELECT FROM');
Insert_Category(
p_from_date => l_push_date_range1,
p_to_date => l_push_date_range2,
p_staging_table_name => l_staging_table_name,
p_view_name => l_view_name,
p_category_set_name => l_itemorg_catset1_name,
p_control_level => l_control_level);
Insert_Category(
p_from_date => l_push_date_range1,
p_to_date => l_push_date_range2,
p_staging_table_name => l_staging_table_name,
p_view_name => l_view_name,
p_category_set_name => l_item_catset1_name,
p_control_level => l_control_level);
Insert_Category(
p_from_date => l_push_date_range1,
p_to_date => l_push_date_range2,
p_staging_table_name => l_staging_table_name,
p_view_name => l_view_name,
p_category_set_name => l_item_catset2_name,
p_control_level => l_control_level);
Insert_VBH_Category(
p_from_date => l_push_date_range1,
p_to_date => l_push_date_range2,
p_view_name => 'EDW_ITEM_VBH_CAT_LCV',
p_category_set_name => l_item_catset3_name,
p_no_of_catset_lvls => 10,
p_hrchy_top_node => l_itm_hrchy3_vbh_top_node,
p_control_level => l_control_level);
Insert_Category(
p_from_date => l_push_date_range1,
p_to_date => l_push_date_range2,
p_staging_table_name => l_staging_table_name,
p_view_name => l_prior_staging_table_name,
p_category_set_name => l_item_catset3_name,
p_control_level => l_control_level);
INSERT INTO EDW_ITEM_CATSETI3_C1_LSTG
(CATEGORY_FK,
CATEGORY_FK_KEY,
CATEGORY_NAME,
CATEGORY_SET_NAME,
CREATION_DATE,
DESCRIPTION,
ERROR_CODE,
INSTANCE,
CATEGORY_DP,
CATEGORY_PK,
CATEGORY_ID,
CATEGORY_SET_ID,
LAST_UPDATE_DATE,
NAME,
REQUEST_ID,
ROW_ID,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
OPERATION_CODE,
COLLECTION_STATUS )
SELECT
'NA_EDW',
NULL,
NULL,
CATEGORY_SET_NAME,
CREATION_DATE,
DESCRIPTION,
NULL,
l_instance, /* Bug# 2558245 */
CATEGORY_DP,
TO_CHAR(l_control_level) || '-' || CATEGORY_PK || '-' || l_instance, /* Bug# 2558245 */
CATEGORY_ID,
CATEGORY_SET_ID,
LAST_UPDATE_DATE,
SUBSTRB(NAME, 1, 320),
NULL,
NULL,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
NULL,
'READY'
FROM EDW_ITEM_ITEM_CAT_LCV LCV
WHERE LCV.LAST_UPDATE_DATE BETWEEN l_push_date_range1 AND l_push_date_range2
AND LCV.CATEGORY_SET_NAME = l_item_catset3_name
AND NOT EXISTS
(SELECT NULL
FROM EDW_ITEM_CATSETI3_C1_LSTG LSTG
WHERE SUBSTRB(LSTG.CATEGORY_PK, 3) = LCV.CATEGORY_PK || '-' || l_instance /* Bug 2558245 */
AND LSTG.CATEGORY_SET_NAME = l_item_catset3_name
AND LSTG.COLLECTION_STATUS = 'READY')
AND EXISTS /* Bug# 2559696 Removed the use of EDW_ITEMS_CATEGORY_FKV by dsakalle */
(SELECT NULL
FROM
MTL_ITEM_CATEGORIES cat,
MTL_CATEGORY_SETS_TL tl
WHERE tl.CATEGORY_SET_NAME = l_item_catset3_name
AND cat.CATEGORY_ID = LCV.CATEGORY_ID
AND cat.CATEGORY_SET_ID = tl.CATEGORY_SET_ID);
SELECT 1 into l_column_exists
FROM all_tab_columns
WHERE table_name = 'MTL_CATEGORY_SETS_B'
AND column_name = 'HIERARCHY_ENABLED'
AND owner = l_inv_schema;
l_rows_inserted := 0;
l_hierarchy_stmt := 'SELECT HIERARCHY_ENABLED, CATEGORY_SET_ID'||
' FROM MTL_CATEGORY_SETS ' ||
' WHERE CATEGORY_SET_NAME = :l_catset3_name';
l_rows_inserted := dbms_sql.execute_and_fetch(l_cursor, true);
edw_log.put_line('rows inserted ' || l_rows_inserted);
if l_rows_inserted > 0 then
dbms_sql.column_value(l_cursor, 1, l_hrchy_enabled);
INSERT_CATEGORY_HIERARCHY(
p_from_date => l_push_date_range1,
p_to_date => l_push_date_range2,
p_view_name => l_view_name,
p_category_set_name => l_item_catset3_name,
p_category_set_id => l_catset3_id,
p_no_of_catset_lvls => 10,
p_control_level => l_control_level);
Insert_Category(
p_from_date => l_push_date_range1,
p_to_date => l_push_date_range2,
p_staging_table_name => l_staging_table_name,
p_view_name => l_prior_staging_table_name,
p_category_set_name => l_item_catset3_name,
p_control_level => l_control_level);
INSERT INTO EDW_ITEM_CATSETI3_C1_LSTG
(CATEGORY_FK,
CATEGORY_FK_KEY,
CATEGORY_NAME,
CATEGORY_SET_NAME,
CREATION_DATE,
DESCRIPTION,
ERROR_CODE,
INSTANCE,
CATEGORY_DP,
CATEGORY_PK,
CATEGORY_ID,
CATEGORY_SET_ID,
LAST_UPDATE_DATE,
NAME,
REQUEST_ID,
ROW_ID,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
OPERATION_CODE,
COLLECTION_STATUS )
SELECT
'NA_EDW',
NULL,
NULL,
CATEGORY_SET_NAME,
CREATION_DATE,
DESCRIPTION,
NULL,
l_instance, /* Bug# 2558245 */
CATEGORY_DP,
TO_CHAR(l_control_level) || '-' || CATEGORY_PK || '-' || l_instance, /* Bug# 2558245 */
CATEGORY_ID,
CATEGORY_SET_ID,
LAST_UPDATE_DATE,
SUBSTRB(NAME, 1, 320),
NULL,
NULL,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
NULL,
'READY'
FROM EDW_ITEM_ITEM_CAT_LCV LCV
WHERE LCV.LAST_UPDATE_DATE BETWEEN l_push_date_range1 AND l_push_date_range2
AND LCV.CATEGORY_SET_NAME = l_item_catset3_name
AND NOT EXISTS
(SELECT NULL
FROM EDW_ITEM_CATSETI3_C1_LSTG LSTG
WHERE SUBSTRB(LSTG.CATEGORY_PK, 3) = LCV.CATEGORY_PK || '-' || l_instance /* Bug 2558245 */
AND LSTG.CATEGORY_SET_NAME = l_item_catset3_name
AND LSTG.COLLECTION_STATUS = 'READY')
AND EXISTS /* Bug# 2559696 Removed the use of EDW_ITEMS_CATEGORY_FKV by dsakalle */
(SELECT NULL
FROM
MTL_ITEM_CATEGORIES cat,
MTL_CATEGORY_SETS_TL tl
WHERE tl.CATEGORY_SET_NAME = l_item_catset3_name
AND cat.CATEGORY_ID = LCV.CATEGORY_ID
AND cat.CATEGORY_SET_ID = tl.CATEGORY_SET_ID);
Insert_Category(
p_from_date => l_push_date_range1,
p_to_date => l_push_date_range2,
p_staging_table_name => l_staging_table_name,
p_view_name => l_view_name,
p_category_set_name => l_item_catset3_name,
p_control_level => l_control_level);
l_rows_inserted := 0;
INSERT INTO EDW_ITEM_ITEMORG_LSTG(
ITEM_ORG_PK,
CATSET_CATEGORY_FK,
NAME,
INSTANCE,
COLLECTION_STATUS,
ITEM_NUMBER_FK,
PROD_FAMILY_FK,
CATEGORY_ID,
CATEGORY_SET_ID,
LAST_UPDATE_DATE)
VALUES (
SUBSTRB(l_catset_category_fk || l_level_name, 1, 1000),
l_catset_category_fk,
SUBSTRB(l_all_item_orgs||'('||l_itemrev_rec.NAME||')', 1, 320),
SUBSTRB(l_itemrev_rec.INSTANCE, 1, 30),
'READY',
'NA_EDW',
'NA_EDW',
l_itemrev_rec.CATEGORY_ID,
l_itemrev_rec.CATEGORY_SET_ID,
l_itemrev_rec.last_update_date
);
l_rows_inserted := l_rows_inserted + 1;
INSERT INTO EDW_ITEM_ITEMREV_LSTG(
ITEM_REVISION_PK,
ITEM_ORG_FK,
INSTANCE,
NAME,
CATEGORY_ID,
CATEGORY_SET_ID,
COLLECTION_STATUS,
LAST_UPDATE_DATE)
VALUES (
SUBSTRB(l_catset_category_fk || l_level_name, 1, 320),
SUBSTRB(l_catset_category_fk || l_level_name, 1, 320),
SUBSTRB(l_itemrev_rec.INSTANCE, 1, 30),
SUBSTRB(l_all_item_revs||'('||l_itemrev_rec.Name||')', 1, 320),
l_itemrev_rec.CATEGORY_ID,
l_itemrev_rec.CATEGORY_SET_ID,
'READY',
l_itemrev_rec.last_update_date);
l_rows_inserted := l_rows_inserted + 1;
edw_log.put_line('Inserted '||TO_CHAR(NVL(l_rows_inserted,0))||
' rows into the lower level staging tables');
EDW_ITEMS_M_C.g_row_count:=EDW_ITEMS_M_C.g_row_count+l_rows_inserted;
l_rows_inserted := 0;
INSERT INTO EDW_ITEM_ITEM_LSTG(
ITEM_NUMBER_PK,
INSTANCE,
NAME,
COLLECTION_STATUS,
CATSET1_CATEGORY_FK,
CATSET2_CATEGORY_FK,
CATSET3_CATEGORY_FK,
PRODUCT_GROUP_FK,
CATEGORY_ID,
CATEGORY_SET_ID,
LAST_UPDATE_DATE)
VALUES (
TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK || l_level_name,
l_itemrev_rec.INSTANCE,
SUBSTRB(l_all_items||'('||l_itemrev_rec.NAME||')', 1, 320),
'READY',
l_catset1_category_fk,
l_catset2_category_fk,
l_catset3_category_fk,
'NA_EDW',
l_itemrev_rec.CATEGORY_ID,
l_itemrev_rec.CATEGORY_SET_ID,
l_itemrev_rec.last_update_date
);
INSERT INTO EDW_ITEM_ITEMORG_LSTG(
ITEM_ORG_PK,
ITEM_NUMBER_FK,
INSTANCE,
NAME,
COLLECTION_STATUS,
CATSET_CATEGORY_FK,
PROD_FAMILY_FK,
CATEGORY_ID,
CATEGORY_SET_ID,
LAST_UPDATE_DATE)
VALUES (
TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK || l_level_name,
TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK || l_level_name,
l_itemrev_rec.INSTANCE,
SUBSTRB(l_all_item_orgs||'('||l_itemrev_rec.NAME||')', 1, 320),
'READY',
'NA_EDW',
'NA_EDW',
l_itemrev_rec.CATEGORY_ID,
l_itemrev_rec.CATEGORY_SET_ID,
l_itemrev_rec.last_update_date
);
INSERT INTO EDW_ITEM_ITEMREV_LSTG(
ITEM_REVISION_PK,
ITEM_ORG_FK,
NAME,
CATEGORY_ID,
CATEGORY_SET_ID,
INSTANCE,
COLLECTION_STATUS,
LAST_UPDATE_DATE)
VALUES (
SUBSTRB(TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK || l_level_name, 1, 320),
SUBSTRB(TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK || l_level_name, 1, 320),
SUBSTRB(l_all_item_revs||'('||l_itemrev_rec.NAME||')', 1, 320),
l_itemrev_rec.CATEGORY_ID,
l_itemrev_rec.CATEGORY_SET_ID,
l_itemrev_rec.INSTANCE,
'READY',
l_itemrev_rec.last_update_date);
l_rows_inserted := l_rows_inserted + 1;
INSERT INTO EDW_ITEM_ITEM_LSTG(
ITEM_NUMBER_PK,
INSTANCE,
NAME,
COLLECTION_STATUS,
CATSET1_CATEGORY_FK,
CATSET2_CATEGORY_FK,
CATSET3_CATEGORY_FK,
PRODUCT_GROUP_FK,
CATEGORY_ID,
CATEGORY_SET_ID,
LAST_UPDATE_DATE)
VALUES (
TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK || l_level_name,
l_itemrev_rec.INSTANCE,
SUBSTRB(l_all_items||'('||l_itemrev_rec.NAME||')', 1, 320),
'READY',
l_catset1_category_fk,
l_catset2_category_fk,
l_catset3_category_fk,
'NA_EDW',
l_itemrev_rec.CATEGORY_ID,
l_itemrev_rec.CATEGORY_SET_ID,
l_itemrev_rec.last_update_date
);
INSERT INTO EDW_ITEM_ITEMORG_LSTG(
ITEM_ORG_PK,
ITEM_NUMBER_FK,
INSTANCE,
NAME,
COLLECTION_STATUS,
CATSET_CATEGORY_FK,
PROD_FAMILY_FK,
CATEGORY_ID,
CATEGORY_SET_ID,
LAST_UPDATE_DATE)
VALUES (
TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK || l_level_name,
TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK || l_level_name,
l_itemrev_rec.INSTANCE,
SUBSTRB(l_all_item_orgs||'('||l_itemrev_rec.NAME||')', 1, 320),
'READY',
'NA_EDW',
'NA_EDW',
l_itemrev_rec.CATEGORY_ID,
l_itemrev_rec.CATEGORY_SET_ID,
l_itemrev_rec.last_update_date
);
INSERT INTO EDW_ITEM_ITEMREV_LSTG(
ITEM_REVISION_PK,
ITEM_ORG_FK,
NAME,
CATEGORY_ID,
CATEGORY_SET_ID,
INSTANCE,
COLLECTION_STATUS,
LAST_UPDATE_DATE)
VALUES (
SUBSTRB(TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK || l_level_name, 1, 320),
SUBSTRB(TO_CHAR(l_control_level)||'-'||l_itemrev_rec.CATEGORY_PK || l_level_name, 1, 320),
SUBSTRB(l_all_item_revs||'('||l_itemrev_rec.NAME||')', 1, 320),
l_itemrev_rec.CATEGORY_ID,
l_itemrev_rec.CATEGORY_SET_ID,
l_itemrev_rec.INSTANCE,
'READY',
l_itemrev_rec.last_update_date);
l_rows_inserted := l_rows_inserted + 1;
edw_log.put_line('Inserted '||TO_CHAR(NVL(l_rows_inserted,0))||
' rows into the lower level staging tables');
EDW_ITEMS_M_C.g_row_count:=EDW_ITEMS_M_C.g_row_count+l_rows_inserted;
INSERT INTO EDW_ITEM_PROD_LINE_LSTG(
ALL_FK_KEY,
INTEREST_TYPE_ID,
REQUEST_ID,
ALL_FK,
COLLECTION_STATUS,
DESCRIPTION,
ENABLED_FLAG,
ERROR_CODE,
INSTANCE_CODE,
NAME,
OPERATION_CODE,
PRODUCT_LINE_DP,
PRODUCT_LINE_PK,
ROW_ID,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
CREATION_DATE,
DELETION_DATE,
LAST_UPDATE_DATE)
SELECT
NULL ALL_FK_KEY,
INTEREST_TYPE_ID INTEREST_TYPE_ID,
NULL REQUEST_ID,
ALL_FK ALL_FK,
'READY' COLLECTION_STATUS,
DESCRIPTION DESCRIPTION,
ENABLED_FLAG ENABLED_FLAG,
NULL ERROR_CODE,
l_instance INSTANCE_CODE, -- INSTANCE_CODE INSTANCE_CODE, /* Bug# 2558245 */
NAME NAME,
NULL OPERATION_CODE,
PRODUCT_LINE_DP PRODUCT_LINE_DP,
PRODUCT_LINE_PK || '-' || l_instance || '-INTR_TYPE' PRODUCT_LINE_PK,-- PRODUCT_LINE_PK PRODUCT_LINE_PK,/* Bug# 2558245 */
NULL ROW_ID,
USER_ATTRIBUTE1 USER_ATTRIBUTE1,
USER_ATTRIBUTE2 USER_ATTRIBUTE2,
USER_ATTRIBUTE3 USER_ATTRIBUTE3,
USER_ATTRIBUTE4 USER_ATTRIBUTE4,
USER_ATTRIBUTE5 USER_ATTRIBUTE5,
CREATION_DATE CREATION_DATE,
DELETION_DATE DELETION_DATE,
LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM EDW_ITEM_PROD_LINE_LCV
WHERE last_update_date BETWEEN p_from_date AND p_to_date;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0)) ||' rows into the staging table');
INSERT INTO EDW_ITEM_PROD_CATG_LSTG(
PRODUCT_CATEG_PK,
PRODUCT_LINE_FK,
NAME,
INSTANCE_CODE,
COLLECTION_STATUS,
LAST_UPDATE_DATE)
SELECT
PRODUCT_LINE_PK || '-' || l_instance || '-INTR_TYPE' ||'-PLIN' PRODUCT_CATEG_PK, /* Bug# 2558245 */
PRODUCT_LINE_PK || '-' || l_instance || '-INTR_TYPE' PRODUCT_LINE_FK, /* Bug# 2558245 */
SUBSTRB(l_all_prod_cats||'('||NAME||')', 1, 320) NAME,
l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
'READY',
LAST_UPDATE_DATE
FROM EDW_ITEM_PROD_LINE_LCV
WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
INSERT INTO EDW_ITEM_PROD_GRP_LSTG(
PRODUCT_GROUP_PK,
PRODUCT_CATEG_FK,
NAME,
INSTANCE_CODE,
COLLECTION_STATUS,
LAST_UPDATE_DATE)
SELECT
PRODUCT_LINE_PK || '-' || l_instance || '-INTR_TYPE' ||'-PLIN' PRODUCT_GROUP_PK, /* Bug# 2558245 */
PRODUCT_LINE_PK || '-' || l_instance || '-INTR_TYPE' ||'-PLIN' PRODUCT_CATEG_FK, /* Bug# 2558245 */
SUBSTRB(l_all_prod_grps||'('||NAME||')', 1, 320) NAME,
l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
'READY',
LAST_UPDATE_DATE
FROM EDW_ITEM_PROD_LINE_LCV
WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
INSERT INTO EDW_ITEM_ITEM_LSTG(
ITEM_NUMBER_PK,
PRODUCT_GROUP_FK,
NAME,
INSTANCE,
COLLECTION_STATUS,
CATSET1_CATEGORY_FK,
CATSET2_CATEGORY_FK,
CATSET3_CATEGORY_FK,
LAST_UPDATE_DATE)
SELECT
PRODUCT_LINE_PK || '-' || l_instance || '-INTR_TYPE' ||'-PLIN' ITEM_NUMBER_PK, /* Bug# 2558245 */
PRODUCT_LINE_PK || '-' || l_instance || '-INTR_TYPE' ||'-PLIN' PRODUCT_GROUP_FK, /* Bug# 2558245 */
SUBSTRB(l_all_items||'('||NAME||')', 1, 320) NAME,
l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
'READY',
'NA_EDW',
'NA_EDW',
'NA_EDW',
LAST_UPDATE_DATE
FROM EDW_ITEM_PROD_LINE_LCV
WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
' rows into the staging table');
INSERT INTO EDW_ITEM_ITEMORG_LSTG(
ITEM_ORG_PK,
ITEM_NUMBER_FK,
NAME,
INSTANCE,
COLLECTION_STATUS,
CATSET_CATEGORY_FK,
PROD_FAMILY_FK,
LAST_UPDATE_DATE)
SELECT
PRODUCT_LINE_PK || '-' || l_instance || '-INTR_TYPE' ||'-PLIN' ITEM_ORG_PK, /* Bug# 2558245 */
PRODUCT_LINE_PK || '-' || l_instance || '-INTR_TYPE' ||'-PLIN' ITEM_NUMBER_FK, /* Bug# 2558245 */
SUBSTRB(l_all_item_orgs||'('||NAME||')', 1, 320) NAME,
l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
'READY',
'NA_EDW',
'NA_EDW',
LAST_UPDATE_DATE
FROM EDW_ITEM_PROD_LINE_LCV
WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT, 0)) ||
'rows into the staging table');
INSERT INTO EDW_ITEM_ITEMREV_LSTG(
ITEM_REVISION_PK,
ITEM_ORG_FK,
NAME,
INSTANCE,
COLLECTION_STATUS,
LAST_UPDATE_DATE)
SELECT
PRODUCT_LINE_PK || '-' || l_instance || '-INTR_TYPE' ||'-PLIN' ITEM_REVISION_PK, /* Bug# 2558245 */
PRODUCT_LINE_PK || '-' || l_instance || '-INTR_TYPE' ||'-PLIN' ITEM_ORG_FK, /* Bug# 2558245 */
SUBSTRB(l_all_item_revs||'('||NAME||')', 1, 320) NAME,
l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
'READY',
LAST_UPDATE_DATE
FROM EDW_ITEM_PROD_LINE_LCV
WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT, 0)) ||
' rows into the staging table');
INSERT INTO EDW_ITEM_PROD_CATG_LSTG(
PRIMARY_CODE_ID,
PRODUCT_LINE_FK_KEY,
REQUEST_ID,
COLLECTION_STATUS,
DESCRIPTION,
ENABLED_FLAG,
ERROR_CODE,
INSTANCE_CODE,
NAME,
OPERATION_CODE,
PRODUCT_CATEG_DP,
PRODUCT_CATEG_PK,
PRODUCT_LINE_FK,
ROW_ID,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
CREATION_DATE,
DELETION_DATE,
LAST_UPDATE_DATE)
SELECT
primary_code_id PRIMARY_CODE_ID,
NULL PRODUCT_LINE_FK_KEY,
NULL REQUEST_ID,
'READY' COLLECTION_STATUS,
description DESCRIPTION,
enabled_flag ENABLED_FLAG,
NULL ERROR_CODE,
l_instance INSTANCE_CODE, -- instance_code INSTANCE_CODE, /* Bug# 2558245 */
name NAME,
NULL OPERATION_CODE,
product_categ_dp PRODUCT_CATEG_DP,
product_categ_pk || '-' || l_instance || '-PRIM_CODE' PRODUCT_CATEG_PK, -- product_categ_pk PRODUCT_CATEG_PK, /* Bug# 2558245 */
product_line_fk || '-' || l_instance || '-INTR_TYPE' PRODUCT_LINE_FK, -- product_line_fk PRODUCT_LINE_FK, /* Bug# 2558245 */
NULL ROW_ID,
user_attribute1 USER_ATTRIBUTE1,
user_attribute2 USER_ATTRIBUTE2,
user_attribute3 USER_ATTRIBUTE3,
user_attribute4 USER_ATTRIBUTE4,
user_attribute5 USER_ATTRIBUTE5,
creation_date CREATION_DATE,
deletion_date DELETION_DATE,
last_update_date LAST_UPDATE_DATE
FROM edw_item_prod_catg_lcv
WHERE last_update_date BETWEEN p_from_date AND p_to_date;
INSERT INTO EDW_ITEM_PROD_GRP_LSTG(
PRODUCT_GROUP_PK,
PRODUCT_CATEG_FK,
NAME,
INSTANCE_CODE,
COLLECTION_STATUS,
LAST_UPDATE_DATE)
SELECT
PRODUCT_CATEG_PK || '-' || l_instance || '-PRIM_CODE' ||'-PCTG' PRODUCT_GROUP_PK, /* Bug# 2558245 */
PRODUCT_CATEG_PK || '-' || l_instance || '-PRIM_CODE' PRODUCT_CATEG_FK, /* Bug# 2558245 */
SUBSTRB(l_all_prod_grps||'('||NAME||')', 1, 320) NAME,
l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
'READY',
LAST_UPDATE_DATE
FROM EDW_ITEM_PROD_CATG_LCV
WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
INSERT INTO EDW_ITEM_ITEM_LSTG(
ITEM_NUMBER_PK,
PRODUCT_GROUP_FK,
NAME,
INSTANCE,
COLLECTION_STATUS,
CATSET1_CATEGORY_FK,
CATSET2_CATEGORY_FK,
CATSET3_CATEGORY_FK,
LAST_UPDATE_DATE)
SELECT
PRODUCT_CATEG_PK || '-' || l_instance || '-PRIM_CODE' ||'-PCTG' ITEM_NUMBER_PK, /* Bug# 2558245 */
PRODUCT_CATEG_PK || '-' || l_instance || '-PRIM_CODE' ||'-PCTG' PRODUCT_GROUP_FK, /* Bug# 2558245 */
SUBSTRB(l_all_items||'('||NAME||')', 1, 320) NAME,
l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
'READY',
'NA_EDW',
'NA_EDW',
'NA_EDW',
LAST_UPDATE_DATE
FROM EDW_ITEM_PROD_CATG_LCV
WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0))||
' rows into the staging table');
INSERT INTO EDW_ITEM_ITEMORG_LSTG(
ITEM_ORG_PK,
ITEM_NUMBER_FK,
NAME,
INSTANCE,
COLLECTION_STATUS,
CATSET_CATEGORY_FK,
PROD_FAMILY_FK,
LAST_UPDATE_DATE)
SELECT
PRODUCT_CATEG_PK || '-' || l_instance || '-PRIM_CODE' ||'-PCTG' ITEM_ORG_PK, /* Bug# 2558245 */
PRODUCT_CATEG_PK || '-' || l_instance || '-PRIM_CODE' ||'-PCTG' ITEM_NUMBER_FK, /* Bug# 2558245 */
SUBSTRB(l_all_item_orgs||'('||NAME||')', 1, 320) NAME,
l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
'READY',
'NA_EDW',
'NA_EDW',
LAST_UPDATE_DATE
FROM EDW_ITEM_PROD_CATG_LCV
WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT, 0)) ||
'rows into the staging table');
INSERT INTO EDW_ITEM_ITEMREV_LSTG(
ITEM_REVISION_PK,
ITEM_ORG_FK,
INSTANCE,
NAME,
COLLECTION_STATUS,
LAST_UPDATE_DATE)
SELECT
PRODUCT_CATEG_PK || '-' || l_instance || '-PRIM_CODE' ||'-PCTG' ITEM_REVISION_PK, /* Bug# 2558245 */
PRODUCT_CATEG_PK || '-' || l_instance || '-PRIM_CODE' ||'-PCTG' ITEM_ORG_FK, /* Bug# 2558245 */
l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
SUBSTRB(l_all_item_revs||'('||NAME||')', 1, 320) NAME,
'READY',
LAST_UPDATE_DATE
FROM EDW_ITEM_PROD_CATG_LCV
WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT, 0)) ||
' rows into the staging table');
INSERT INTO EDW_ITEM_PROD_GRP_LSTG(
PRODUCT_CATEG_FK_KEY,
REQUEST_ID,
SECONDARY_CODE_ID,
COLLECTION_STATUS,
DESCRIPTION,
ENABLED_FLAG,
ERROR_CODE,
INSTANCE_CODE,
NAME,
OPERATION_CODE,
PRODUCT_CATEG_FK,
PRODUCT_GROUP_DP,
PRODUCT_GROUP_PK,
ROW_ID,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
CREATION_DATE,
DELETION_DATE,
LAST_UPDATE_DATE)
SELECT
NULL PRODUCT_CATEG_FK_KEY,
NULL REQUEST_ID,
SECONDARY_CODE_ID SECONDARY_CODE_ID,
'READY' COLLECTION_STATUS,
DESCRIPTION DESCRIPTION,
ENABLED_FLAG ENABLED_FLAG,
NULL ERROR_CODE,
l_instance INSTANCE_CODE, -- INSTANCE_CODE INSTANCE_CODE, /* Bug# 2558245 */
NAME NAME,
NULL OPERATION_CODE,
PRODUCT_CATEG_FK || '-' || l_instance || '-PRIM_CODE' PRODUCT_CATEG_FK, -- PRODUCT_CATEG_FK PRODUCT_CATEG_FK, /* Bug# 2558245 */
PRODUCT_GROUP_DP PRODUCT_GROUP_DP,
PRODUCT_GROUP_PK || '-' || l_instance || '-SECN_CODE' PRODUCT_GROUP_PK, -- PRODUCT_GROUP_PK PRODUCT_GROUP_PK, /* Bug# 2558245 */
NULL ROW_ID,
USER_ATTRIBUTE1 USER_ATTRIBUTE1,
USER_ATTRIBUTE2 USER_ATTRIBUTE2,
USER_ATTRIBUTE3 USER_ATTRIBUTE3,
USER_ATTRIBUTE4 USER_ATTRIBUTE4,
USER_ATTRIBUTE5 USER_ATTRIBUTE5,
CREATION_DATE CREATION_DATE,
DELETION_DATE DELETION_DATE,
LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM edw_item_prod_grp_lcv
WHERE last_update_date BETWEEN p_from_date AND p_to_date;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT, 0)) ||
' rows into the staging table');
INSERT INTO EDW_ITEM_ITEMREV_LSTG(
ITEM_REVISION_PK,
ITEM_ORG_FK,
NAME,
INSTANCE,
COLLECTION_STATUS,
LAST_UPDATE_DATE)
SELECT
PRODUCT_GROUP_PK || '-' || l_instance || '-SECN_CODE'||'-PGRP' ITEM_REVISION_PK, /* Bug# 2558245 */
PRODUCT_GROUP_PK || '-' || l_instance || '-SECN_CODE'||'-PGRP' ITEM_ORG_FK, /* Bug# 2558245 */
SUBSTRB(l_all_item_revs||'('||NAME||')', 1, 320) NAME,
l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
'READY',
LAST_UPDATE_DATE
FROM EDW_ITEM_PROD_GRP_LCV
WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT, 0)) ||
' rows into the staging table');
INSERT INTO EDW_ITEM_ITEMORG_LSTG(
ITEM_ORG_PK,
ITEM_NUMBER_FK,
NAME,
INSTANCE,
COLLECTION_STATUS,
CATSET_CATEGORY_FK,
PROD_FAMILY_FK,
LAST_UPDATE_DATE)
SELECT
PRODUCT_GROUP_PK || '-' || l_instance || '-SECN_CODE' ||'-PGRP' ITEM_ORG_PK, /* Bug# 2558245 */
PRODUCT_GROUP_PK || '-' || l_instance || '-SECN_CODE' ||'-PGRP' ITEM_NUMBER_FK, /* Bug# 2558245 */
SUBSTRB(l_all_item_orgs||'('||NAME||')', 1, 320) NAME,
l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
'READY',
'NA_EDW',
'NA_EDW',
LAST_UPDATE_DATE
FROM EDW_ITEM_PROD_GRP_LCV
WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT, 0)) ||
'rows into the staging table');
INSERT INTO EDW_ITEM_ITEM_LSTG(
ITEM_NUMBER_PK,
PRODUCT_GROUP_FK,
NAME,
INSTANCE,
COLLECTION_STATUS,
CATSET1_CATEGORY_FK,
CATSET2_CATEGORY_FK,
CATSET3_CATEGORY_FK,
LAST_UPDATE_DATE)
SELECT
PRODUCT_GROUP_PK || '-' || l_instance || '-SECN_CODE' ||'-PGRP' ITEM_NUMBER_PK, /* Bug# 2558245 */
PRODUCT_GROUP_PK || '-' || l_instance || '-SECN_CODE' PRODUCT_GROUP_FK, /* Bug# 2558245 */
SUBSTRB(l_all_items||'('||NAME||')', 1, 320) NAME,
l_instance, -- INSTANCE_CODE, /* Bug# 2558245 */
'READY',
'NA_EDW',
'NA_EDW',
'NA_EDW',
LAST_UPDATE_DATE
FROM EDW_ITEM_PROD_GRP_LCV
WHERE LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
edw_log.put_line('Inserted '||TO_CHAR(NVL(SQL%ROWCOUNT,0)) ||
' rows into the stagint table');