[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT category_set_id
FROM mtl_default_category_sets
WHERE functional_area_id = 2;
SELECT category_set_id
FROM mtl_default_category_sets
WHERE functional_area_id = 1;
SELECT
cat.inventory_item_id,
cat.organization_id,
cat.category_set_id,
mti.concatenated_segments,
-- mtp.organization_code,
COUNT(category_id)
FROM
mtl_system_items_kfv mti,
-- mtl_parameters mtp,
mtl_item_categories cat
WHERE
mti.inventory_item_id = cat.inventory_item_id AND
mti.organization_id = cat.organization_id AND
-- mtp.organization_id = mti.organization_id AND
category_set_id IN (l_vbh_category_set, l_inv_category_set, l_po_category_set)
GROUP BY
cat.category_set_id,
cat.inventory_item_id,
cat.organization_id,
mti.concatenated_segments
-- mtp.organization_code
HAVING COUNT(category_id) > 1;
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;
SELECT
count(mti.inventory_item_id),
mti.organization_id,
mti.concatenated_segments
FROM
mtl_system_items_kfv mti
WHERE
mti.concatenated_segments = 'X'
GROUP BY mti.concatenated_segments, mti.organization_id
HAVING count(inventory_item_id) > 1;
l_rows_inserted NUMBER; -- Bug#2662318 --
l_rows_inserted := 0;
INSERT INTO ENI_ITEM_STAR_VALID_ERR(
inventory_item_id,
organization_id,
item_name,
category_set_id,
error_message)
VALUES(
c2.inventory_item_id,
c2.organization_id,
c2.concatenated_segments,
-- c2.organization_code,
c2.category_set_id,
'ITEMS WITH MULTIPLE CATEGORY ASSIGNMENT'
);
INSERT INTO ENI_ITEM_STAR_VALID_ERR(
inventory_item_id,
organization_id,
item_name,
error_message)
VALUES(
c5.inventory_item_id,
c5.organization_id,
c4.concatenated_segments,
'MULTIPLE ITEMS WITH SAME NAME'
);
SELECT vbh_category_set_id, inv_category_set_id, po_category_set_id
INTO l_prev_vbh_catset, l_prev_inv_catset, l_prev_po_catset
FROM eni_oltp_item_star
WHERE inventory_item_id = -1
AND organization_id = -99
AND rownum = 1;
SELECT vbh_category_set_id, inv_category_set_id, po_category_set_id
INTO l_prev_vbh_catset, l_prev_inv_catset, l_prev_po_catset
FROM eni_oltp_item_star
WHERE inventory_item_id <> -1
AND organization_id <> -99
AND rownum = 1;
BIS_COLLECTION_UTILITIES.log('Inserting UNASSIGNED row into STAR table');
INSERT INTO ENI_OLTP_ITEM_STAR (
id
, value
, organization_code
, inventory_item_id
, organization_id
, po_category_id
, po_category_set_id
, po_concat_seg
, inv_category_id
, inv_category_set_id
, inv_concat_seg
, vbh_category_id
, vbh_category_set_id
, vbh_concat_seg
, master_id
, creation_date
, last_update_date
, item_catalog_group_id
, primary_uom_code
, unit_weight
, unit_volume
, weight_uom_code
, volume_uom_code
, eam_item_type
)
VALUES ('-1--99',
'Product not specified',
NULL,
-1,
-99,
-1,
l_po_category_set,
'Unassigned',
-1,
l_inv_category_set,
'Unassigned',
-1,
l_vbh_category_set,
'Unassigned',
NULL,
SYSDATE,
SYSDATE,
-1,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
);
BIS_COLLECTION_UTILITIES.log('Inserted UNASSIGNED item row');
BIS_COLLECTION_UTILITIES.log('Inserting all ITEM MASTER items into STAR table');
INSERT /*+ append parallel */ INTO ENI_OLTP_ITEM_STAR (
id
, value
, organization_code
, inventory_item_id
, organization_id
, po_category_id
, po_category_set_id
, po_concat_seg
, inv_category_id
, inv_category_set_id
, inv_concat_seg
, vbh_category_id
, vbh_category_set_id
, vbh_concat_seg
, master_id
, creation_date
, last_update_date
, item_catalog_group_id
, primary_uom_code
, unit_weight
, unit_volume
, weight_uom_code
, volume_uom_code
, eam_item_type
)
SELECT /*+ ordered parallel(mti) parallel(mic) parallel(mic1) */
mti.inventory_item_id || '-' || mti.organization_id id,
mti.CONCATENATED_SEGMENTS || ' (' || mtp.organization_code || ')' value,
null organization_code,
mti.inventory_item_id inventory_item_id,
mti.organization_id organization_id,
Nvl(mic2.category_id,-1) po_category_id,
Nvl(mic2.category_set_id, l_po_category_set) po_category_set_id,
Nvl(kfv2.concatenated_segments,'Unassigned') po_concat_seg,
nvl(mic.category_id,-1) inv_category_id,
nvl(mic.category_Set_id,l_inv_category_set) inv_category_Set_id,
nvl(kfv.concatenated_segments,'Unassigned') inv_concat_seg,
nvl(mic1.category_id, -1) vbh_category_id,
nvl(mic1.category_set_id, l_vbh_category_set) vbh_category_set_id,
nvl(kfv1.concatenated_segments, 'Unassigned') vbh_concat_seg,
decode(mti.organization_id,mtp.master_organization_id,null,
mti.inventory_item_id || '-' || mtp.master_organization_id)
master_id,
mti.creation_date creation_date,
mti.last_update_date last_update_date,
nvl(mti.item_catalog_group_id,-1) item_catalog_group_id,
mti.primary_uom_code,
mti.unit_weight,
mti.unit_volume,
mti.weight_uom_code,
mti.volume_uom_code,
mti.eam_item_type
FROM mtl_system_items_b_kfv mti,
mtl_parameters mtp,
mtl_item_categories mic ,
mtl_item_categories mic1 ,
mtl_item_categories mic2 ,
mtl_categories_b_kfv kfv ,
mtl_categories_b_kfv kfv1,
mtl_categories_b_kfv kfv2
WHERE mtp.organization_id=mti.organization_id
AND mic.organization_id(+) = mti.organization_id
AND mic.inventory_item_id(+) = mti.inventory_item_id
AND mic.category_id = kfv.category_id (+)
and mic.category_set_id(+) = l_inv_category_set
AND mic1.organization_id(+) = mti.organization_id
AND mic1.inventory_item_id(+) = mti.inventory_item_id
AND mic1.category_id = kfv1.category_id (+)
and mic1.category_set_id(+) = l_vbh_category_set
AND mic2.organization_id(+) = mti.organization_id
AND mic2.inventory_item_id(+) = mti.inventory_item_id
AND mic2.category_id = kfv2.category_id (+)
and mic2.category_set_id(+) = l_po_category_set
AND NOT EXISTS(select 'X' from eni_item_star_valid_err
WHERE inventory_item_id = mti.inventory_item_id
AND organization_id = mti.organization_id);
l_rows_inserted := sql%rowcount;
BIS_COLLECTION_UTILITIES.log('Rows inserted into table:'||l_rows_inserted);
DELETE FROM ENI_OLTP_ITEM_STAR WHERE inventory_item_id = -1 AND organization_id = -99;
BIS_COLLECTION_UTILITIES.log('Removed UNASSIGNED row as main insert of items was not successful');
select 1 into l_exist_flag from eni_item_star_temp
where rownum = 1;
UPDATE eni_oltp_item_star a
SET ( value
, last_update_date
, po_category_set_id
, po_category_id
, po_concat_seg
, inv_category_set_id
, inv_category_id
, inv_concat_seg
, vbh_category_set_id
, vbh_category_id
, vbh_concat_seg
, item_catalog_group_id
, primary_uom_code
, unit_weight
, unit_volume
, weight_uom_code
, volume_uom_code
, eam_item_type
)=
( SELECT
nvl(value, a.value)
, nvl(last_update_date, a.last_update_date)
, nvl(po_category_set_id, a.po_category_set_id)
, nvl(po_category_id, a.po_category_id)
, nvl(po_concat_seg, a.po_concat_seg)
, nvl(inv_category_set_id, a.inv_category_set_id)
, nvl(inv_category_id, a.inv_category_id)
, nvl(inv_concat_seg, a.inv_concat_seg)
, nvl(vbh_category_set_id, a.vbh_category_set_id)
, nvl(vbh_category_id, a.vbh_category_id)
, nvl(vbh_concat_seg, a.vbh_concat_seg)
, nvl(item_catalog_group_id, a.item_catalog_group_id)
, nvl(primary_uom_code, a.primary_uom_code)
, nvl(unit_weight, a.unit_weight)
, nvl(unit_volume, a.unit_volume)
, nvl(weight_uom_code, a.weight_uom_code)
, nvl(volume_uom_code, a.volume_uom_code)
, nvl(eam_item_type, a.eam_item_type)
FROM eni_item_star_temp
WHERE a.inventory_item_id = inventory_item_id
AND a.organization_id = organization_id )
WHERE EXISTS( SELECT 'X' from eni_item_star_temp
WHERE a.inventory_item_id = inventory_item_id
AND a.organization_id = organization_id );
BIS_COLLECTION_UTILITIES.log('Rows updated from temp table:'||sql%rowcount);
DELETE FROM eni_item_star_temp;
INSERT INTO eni_oltp_item_star (
id
, value
, inventory_item_id
, organization_id
, po_category_id
, po_category_set_id
, po_concat_seg
, inv_category_id
, inv_category_set_id
, inv_concat_seg
, vbh_category_id
, vbh_category_set_id
, vbh_concat_seg
, master_id
, item_catalog_group_id
, primary_uom_code
, unit_weight
, unit_volume
, weight_uom_code
, volume_uom_code
, eam_item_type
, creation_date
, last_update_date
)
SELECT
mti.inventory_item_id || '-' || mti.organization_id,
mti.concatenated_segments || '(' || mtp.organization_code || ')',
mti.inventory_item_id,
mti.organization_id,
nvl(mic2.category_id, -1) po_category_id,
nvl(mic2.category_set_id, l_po_category_set) po_category_set_id,
nvl(kfv2.concatenated_segments, 'Unassigned') po_concat_seg,
nvl(mic.category_id,-1) inv_category_id,
nvl(mic.category_set_id, l_inv_category_set) inv_category_set_id,
nvl(kfv.concatenated_segments,'Unassigned') inv_concat_seg,
nvl(mic1.category_id, -1) vbh_category_id,
nvl(mic1.category_set_id, l_vbh_category_set) vbh_category_set_id,
nvl(kfv1.concatenated_segments, 'Unassigned') vbh_concat_seg,
decode(mti.organization_id,mtp.master_organization_id,null,
mti.inventory_item_id || '-' || mtp.master_organization_id)
master_id,
nvl(item_catalog_group_id,-1) item_catalog_group_id,
mti.primary_uom_code,
mti.unit_weight,
mti.unit_volume,
mti.weight_uom_code,
mti.volume_uom_code,
mti.eam_item_type,
mti.creation_date,
mti.last_update_date
FROM
mtl_system_items_b_kfv mti,
mtl_parameters mtp,
mtl_item_categories mic,
mtl_item_categories mic1,
mtl_item_categories mic2,
mtl_categories_b_kfv kfv,
mtl_categories_b_kfv kfv1,
mtl_categories_b_kfv kfv2
WHERE
mtp.organization_id = mti.organization_id AND
mic.organization_id(+) = mti.organization_id AND
mic.inventory_item_id(+) = mti.inventory_item_id AND
mic.category_id = kfv.category_id(+) AND
mic.category_set_id(+) = l_inv_category_set AND
mic1.organization_id(+) = mti.organization_id AND
mic1.inventory_item_id(+) = mti.inventory_item_id AND
mic1.category_id = kfv1.category_id(+) AND
mic1.category_set_id(+) = l_vbh_category_set AND
mic2.organization_id(+) = mti.organization_id AND
mic2.inventory_item_id(+) = mti.inventory_item_id AND
mic2.category_id = kfv2.category_id (+) AND
mic2.category_set_id(+) = l_po_category_set AND
NOT EXISTS(SELECT 'X' FROM eni_oltp_item_star eni
WHERE mti.inventory_item_id = eni.inventory_item_id
AND mti.organization_id = eni.organization_id) AND
NOT EXISTS(SELECT 'X' FROM eni_item_star_valid_err err
WHERE mti.inventory_item_id = err.inventory_item_id
AND mti.organization_id = err.organization_id
);
l_rows_inserted := SQL%ROWCOUNT;
BIS_COLLECTION_UTILITIES.log('Records inserted into STAR table: '|| l_rows_inserted);
SELECT 'SYNC'
INTO l_sync_star_items
FROM eni_oltp_item_star
WHERE inventory_item_id = -1
AND organization_id = -99;
PROCEDURE Insert_Items_In_Star( p_api_version NUMBER
, p_init_msg_list VARCHAR2 := 'F'
, p_inventory_item_id NUMBER
, p_organization_id NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2 )
IS
CURSOR get_po_catset IS
SELECT category_set_id
FROM mtl_default_category_sets
WHERE functional_area_id = 2;
SELECT category_set_id
FROM mtl_default_category_sets
WHERE functional_area_id = 1;
INSERT INTO ENI_OLTP_ITEM_STAR (
id
, value
, inventory_item_id
, organization_id
, master_id
, item_catalog_group_id
, primary_uom_code
, unit_weight
, unit_volume
, weight_uom_code
, volume_uom_code
, eam_item_type
, po_category_id
, po_category_set_id
, po_concat_seg
, inv_category_id
, inv_category_set_id
, inv_concat_seg
, vbh_category_id
, vbh_category_set_id
, vbh_concat_seg
, creation_date
, last_update_date
)
SELECT
mti.inventory_item_id || '-' || mti.organization_id,
DECODE(TO_CHAR(G_INSTALL_PHASE),'0',l_item_number || ' (' || mtp.organization_code || ')',mti.concatenated_segments || ' (' || mtp.organization_code || ')'),
mti.inventory_item_id,
mti.organization_id,
decode( mti.organization_id,mtp.master_organization_id, null,
mti.inventory_item_id || '-' || mtp.master_organization_id ),
nvl(mti.item_catalog_group_id,-1),
mti.primary_uom_code,
mti.unit_weight,
mti.unit_volume,
mti.weight_uom_code,
mti.volume_uom_code,
mti.eam_item_type,
-1,
l_po_category_set,
'Unassigned',
-1,
l_inv_category_set,
'Unassigned',
-1,
l_vbh_category_set,
'Unassigned',
mti.creation_date,
mti.last_update_date
FROM mtl_system_items_b_kfv mti,
mtl_parameters mtp
WHERE mti.inventory_item_id = p_inventory_item_id
AND mti.organization_id = p_organization_id
AND mti.organization_id= mtp.organization_id;
FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'INSERT_ITEMS_IN_STAR', SQLERRM);
END Insert_Items_In_Star;
PROCEDURE Delete_Items_In_Star( p_api_version NUMBER
, p_init_msg_list VARCHAR2 := 'F'
, p_inventory_item_id NUMBER
, p_organization_id NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2 )
IS
BEGIN
-- Delete Item
DELETE FROM ENI_OLTP_ITEM_STAR
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'DELETE_ITEMS_IN_STAR', SQLERRM);
END Delete_Items_In_Star;
/* If organization is master then update items in master org & child org
with entries from MSIB. If the organization is child org then
update all the attributes in Items_star with entries in MISB.
We don't have to find out if the attributes are master controlled or
org controlled as Items takes care of this.
*/
PROCEDURE Update_Items_In_Star( p_api_version NUMBER
, p_init_msg_list VARCHAR2 := 'F'
, p_inventory_item_id NUMBER
, p_organization_id NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2 )
IS
-- updates to master-level attributes must capture the resulting propagations to child orgs
CURSOR c_items_in_master IS
SELECT mti.concatenated_segments || ' (' || b.organization_code || ')' value
, b.organization_code
, b.organization_id
, mti.last_update_date
, nvl(mti.item_catalog_group_id,-1) item_catalog_group_id
, mti.primary_uom_code
,mti.unit_weight
,mti.unit_volume
,mti.weight_uom_code
,mti.volume_uom_code
,mti.eam_item_type
FROM mtl_system_items_b_kfv mti
, mtl_parameters b
WHERE mti.inventory_item_id = p_inventory_item_id
AND mti.organization_id = b.organization_id
AND b.master_organization_id = p_organization_id;
SELECT mti.organization_id
, mti.unit_weight
, mti.unit_volume
, mti.weight_uom_code
, mti.volume_uom_code
, primary_uom_code
, eam_item_type
, mti.last_update_date
FROM mtl_system_items_b mti
WHERE mti.inventory_item_id = p_inventory_item_id
AND mti.organization_id = p_organization_id;
SELECT COUNT(master_organization_id) INTO isMasterOrg
FROM mtl_parameters
WHERE master_organization_id = p_organization_id AND ROWNUM < 2;
UPDATE ENI_OLTP_ITEM_STAR
SET VALUE = DECODE(TO_CHAR(G_INSTALL_PHASE),'0',l_item_number,c_items_in_master_rec.value)
, ITEM_CATALOG_GROUP_ID = c_items_in_master_rec.item_catalog_group_id
, PRIMARY_UOM_CODE = c_items_in_master_rec.primary_uom_code
, LAST_UPDATE_DATE = c_items_in_master_rec.last_update_date
, UNIT_WEIGHT = c_items_in_master_rec.unit_weight
, UNIT_VOLUME = c_items_in_master_rec.unit_volume
, WEIGHT_UOM_CODE = c_items_in_master_rec.weight_uom_code
, VOLUME_UOM_CODE = c_items_in_master_rec.volume_uom_code
, EAM_ITEM_TYPE = c_items_in_master_rec.eam_item_type
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = c_items_in_master_rec.organization_id;
UPDATE eni_item_star_temp
SET VALUE = DECODE(TO_CHAR(G_INSTALL_PHASE),'0',l_item_number,c_items_in_master_rec.value)
, LAST_UPDATE_DATE = c_items_in_master_rec.last_update_date
, ITEM_CATALOG_GROUP_ID = c_items_in_master_rec.item_catalog_group_id
, PRIMARY_UOM_CODE = c_items_in_master_rec.primary_uom_code
, UNIT_WEIGHT = c_items_in_master_rec.unit_weight
, UNIT_VOLUME = c_items_in_master_rec.unit_volume
, WEIGHT_UOM_CODE = c_items_in_master_rec.weight_uom_code
, VOLUME_UOM_CODE = c_items_in_master_rec.volume_uom_code
, EAM_ITEM_TYPE = c_items_in_master_rec.eam_item_type
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = c_items_in_master_rec.organization_id;
INSERT INTO eni_item_star_temp(
inventory_item_id
, organization_id
, value
, last_update_date
, item_catalog_group_id
, primary_uom_code
, unit_weight
, unit_volume
,weight_uom_code
,volume_uom_code
,eam_item_type)
VALUES(
p_inventory_item_id
, c_items_in_master_rec.organization_id
, DECODE(TO_CHAR(G_INSTALL_PHASE),'0',l_item_number,c_items_in_master_rec.value)
, c_items_in_master_rec.last_update_date
, c_items_in_master_rec.item_catalog_group_id
, c_items_in_master_rec.primary_uom_code
, c_items_in_master_rec.unit_weight
, c_items_in_master_rec.unit_volume
, c_items_in_master_rec.weight_uom_code
, c_items_in_master_rec.volume_uom_code
, c_items_in_master_rec.eam_item_type);
ELSE --- Update done in Child Org
FOR c_items_in_child_rec IN c_items_in_child
LOOP
UPDATE eni_oltp_item_star
SET UNIT_WEIGHT = c_items_in_child_rec.unit_weight
, UNIT_VOLUME = c_items_in_child_rec.unit_volume
, WEIGHT_UOM_CODE = c_items_in_child_rec.weight_uom_code
, VOLUME_UOM_CODE = c_items_in_child_rec.volume_uom_code
, LAST_UPDATE_DATE = c_items_in_child_rec.last_update_date
, PRIMARY_UOM_CODE = c_items_in_child_rec.primary_uom_code
, EAM_ITEM_TYPE = c_items_in_child_rec.eam_item_type
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = c_items_in_child_rec.organization_id;
UPDATE eni_item_star_temp
SET UNIT_WEIGHT = c_items_in_child_rec.unit_weight
, UNIT_VOLUME = c_items_in_child_rec.unit_volume
, WEIGHT_UOM_CODE = c_items_in_child_rec.weight_uom_code
, VOLUME_UOM_CODE = c_items_in_child_rec.volume_uom_code
, LAST_UPDATE_DATE = c_items_in_child_rec.last_update_date
, PRIMARY_UOM_CODE = c_items_in_child_rec.primary_uom_code
, EAM_ITEM_TYPE = c_items_in_child_rec.eam_item_type
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = c_items_in_child_rec.organization_id;
INSERT INTO eni_item_star_temp(
inventory_item_id
, organization_id
, last_update_date
, unit_weight
, unit_volume
, weight_uom_code
, volume_uom_code
, primary_uom_code
, eam_item_type)
VALUES(
p_inventory_item_id
, c_items_in_child_rec.organization_id
, c_items_in_child_rec.last_update_date
, c_items_in_child_rec.unit_weight
, c_items_in_child_rec.unit_volume
, c_items_in_child_rec.weight_uom_code
, c_items_in_child_rec.volume_uom_code
, c_items_in_child_rec.primary_uom_code
, c_items_in_child_rec.eam_item_type);
FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'UPDATE_ITEMS_IN_STAR', SQLERRM);
END Update_Items_In_Star;
PROCEDURE Update_Categories( p_api_version NUMBER
, p_init_msg_list VARCHAR2 := 'F'
, p_category_id NUMBER
, p_structure_id NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2 )
IS
which_category_set VARCHAR2(15);
SELECT 'INV_CATEGORY' INTO which_category_set
FROM mtl_default_category_sets a, mtl_category_sets_b b
WHERE a.functional_area_id = 1
AND a.category_set_id = b.category_set_id
AND b.structure_id = p_structure_id;
select 'VBH_CATEGORY' into which_category_set
from mtl_category_Sets_b
where structure_id = p_structure_id
and category_Set_id = l_category_set_id;
UPDATE ENI_OLTP_ITEM_STAR
SET VBH_CATEGORY_ID = -1
,VBH_CONCAT_SEG = 'Unassigned'
WHERE vbh_category_id = p_category_id
AND VBH_CONCAT_SEG <> (SELECT CONCATENATED_SEGMENTS
FROM MTL_CATEGORIES_KFV
WHERE CATEGORY_ID = p_category_id);
UPDATE ENI_OLTP_ITEM_STAR
SET VBH_CONCAT_SEG =
(select concatenated_segments
from mtl_categories_b_kfv
where category_id = p_category_id)
WHERE vbh_category_id = p_category_id;
UPDATE ENI_OLTP_ITEM_STAR
SET INV_CONCAT_SEG =
(select concatenated_segments
from mtl_categories_b_kfv
where category_id = p_category_id)
WHERE inv_category_id = p_category_id;
FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'UPDATE_CATEGORIES', SQLERRM);
END Update_Categories;
SELECT msi.organization_id,
nvl(mic.category_id, -1) inv_category_id,
nvl(kfv.concatenated_segments, 'Unassigned') inv_concat_seg,
nvl(mic.category_Set_id, l_INV_category_set_id) inv_category_Set_id,
nvl(mic1.category_id, -1) vbh_category_id,
nvl(kfv1.concatenated_segments, 'Unassigned') vbh_concat_seg,
nvl(mic1.category_Set_id, l_VBH_category_set_id) vbh_category_set_id,
nvl(mic2.category_id, -1) po_category_id,
nvl(kfv2.concatenated_segments, 'Unassigned') po_concat_seg,
nvl(mic2.category_Set_id, l_PO_category_set_id) po_category_set_id
FROM
mtl_system_items_b msi
, mtl_item_categories mic
, mtl_categories_b_kfv kfv
, mtl_item_categories mic1
, mtl_categories_b_kfv kfv1
, mtl_item_categories mic2
, mtl_categories_b_kfv kfv2
WHERE
msi.inventory_item_id = p_inventory_item_id
AND (msi.organization_id = p_organization_id
or msi.organization_id in (SELECT mp.organization_id
FROM mtl_parameters mp
WHERE
mp.master_organization_id = p_organization_id))
AND mic.inventory_item_id (+) = msi.inventory_item_id
AND mic.organization_id (+) = msi.organization_id
AND mic.category_id = kfv.category_id (+)
AND mic.category_set_id (+) = l_INV_category_set_id
AND mic1.inventory_item_id (+) = msi.inventory_item_id
AND mic1.organization_id (+) = msi.organization_id
AND mic1.category_id = kfv1.category_id (+)
AND mic1.category_set_id (+) = l_VBH_category_set_id
AND mic2.inventory_item_id (+) = msi.inventory_item_id
AND mic2.organization_id (+) = msi.organization_id
AND mic2.category_id = kfv2.category_id (+)
AND mic2.category_set_id (+) = l_PO_category_set_id;
SELECT category_set_id
FROM mtl_default_category_sets
WHERE functional_area_id = 2;
SELECT category_set_id
INTO l_INV_category_set_id
FROM mtl_default_category_sets
WHERE functional_area_id = 1;
UPDATE eni_oltp_item_star
SET
INV_CATEGORY_ID = sync_c1.inv_category_id,
INV_CONCAT_SEG = sync_c1.inv_concat_seg,
INV_CATEGORY_SET_ID = sync_c1.inv_category_set_id,
VBH_CATEGORY_ID = sync_c1.vbh_category_id,
VBH_CONCAT_SEG = sync_c1.vbh_concat_seg,
VBH_CATEGORY_SET_ID = sync_c1.vbh_category_set_id,
PO_CATEGORY_ID = sync_c1.po_category_id,
PO_CONCAT_SEG = sync_c1.po_concat_seg,
PO_CATEGORY_SET_ID = sync_c1.po_category_set_id
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = sync_c1.organization_id;
UPDATE ENI_ITEM_STAR_TEMP
set inv_category_set_id = sync_c1.inv_category_set_id,
inv_category_id = sync_c1.inv_category_id,
inv_concat_seg = sync_c1.inv_concat_seg,
vbh_category_set_id = sync_c1.vbh_category_set_id,
vbh_category_id = sync_c1.vbh_category_id,
vbh_concat_seg = sync_c1.vbh_concat_seg,
po_category_set_id = sync_c1.po_category_set_id,
po_category_id = sync_c1.po_category_id,
po_concat_seg = sync_c1.po_concat_seg
where inventory_item_id = p_inventory_item_id
and organization_id = sync_c1.organization_id;
INSERT into ENI_ITEM_STAR_TEMP
(inventory_item_id,
organization_id,
inv_category_set_id,
inv_category_id,
inv_concat_seg,
vbh_category_set_id,
vbh_category_id,
vbh_concat_seg,
po_category_set_id,
po_category_id,
po_concat_seg)
VALUES (p_inventory_item_id,
sync_c1.organization_id,
sync_c1.inv_category_set_id,
sync_c1.inv_category_id,
sync_c1.inv_concat_seg,
sync_c1.vbh_category_set_id,
sync_c1.vbh_category_id,
sync_c1.vbh_concat_seg,
sync_c1.po_category_set_id,
sync_c1.po_category_id,
sync_c1.po_concat_seg);
SELECT category_set_id
FROM mtl_default_category_sets
WHERE functional_area_id = 2;
SELECT category_set_id INTO l_inv_category_set
FROM mtl_default_category_sets
WHERE functional_area_id = 1;
USING (SELECT item.inventory_item_id inventory_item_id,
item.organization_id organization_id,
item.CONCATENATED_SEGMENTS|| '' ('' || mtp.organization_code || '')''value,
decode(item.organization_id,mtp.master_organization_id,null,
item.inventory_item_id || ''-'' || mtp.master_organization_id)
master_id,
nvl(mic.category_id,-1) inv_category_id,
nvl(mic.category_set_id, :l_inv_category_set) inv_category_set_id,
nvl(kfv.concatenated_segments,''Unassigned'') inv_concat_seg,
nvl(mic1.category_id,-1) vbh_category_id,
nvl(mic1.category_set_id, :l_vbh_category_set) vbh_category_set_id,
nvl(kfv1.concatenated_segments,''Unassigned'') vbh_concat_seg,
nvl(mic2.category_id,-1) po_category_id,
nvl(mic2.category_set_id, :l_po_category_set) po_category_set_id,
nvl(kfv2.concatenated_segments,''Unassigned'') po_concat_seg,
nvl(item.item_catalog_group_id,-1) item_catalog_group_id,
item.primary_uom_code
, item.unit_weight
, item.unit_volume
, item.weight_uom_code
, item.volume_uom_code
, item.eam_item_type
, item.creation_date
, item.last_update_date
FROM mtl_system_items_interface interface
, mtl_system_items_b_kfv item
, mtl_parameters mtp
, mtl_item_categories mic
, mtl_categories_b_kfv kfv
, mtl_item_categories mic1
, mtl_categories_b_kfv kfv1
, mtl_item_categories mic2
, mtl_categories_b_kfv kfv2
WHERE item.inventory_item_id = interface.inventory_item_id
AND interface.set_process_id = :p_set_process_id
AND interface.process_flag = 7
AND item.organization_id = interface.organization_id
AND item.organization_id= mtp.organization_id
AND mic.organization_id(+) = item.organization_id
AND mic.inventory_item_id(+) = item.inventory_item_id
AND mic.category_id = kfv.category_id (+)
and mic.category_set_id(+) = :l_inv_category_set
AND mic1.organization_id(+) = item.organization_id
AND mic1.inventory_item_id(+) = item.inventory_item_id
AND mic1.category_id = kfv1.category_id (+)
and mic1.category_set_id(+) = :l_vbh_category_set
AND mic2.organization_id(+) = item.organization_id
AND mic2.inventory_item_id(+) = item.inventory_item_id
AND mic2.category_id = kfv2.category_id (+)
and mic2.category_set_id(+) = :l_po_category_set) mti
ON (STAR.inventory_item_id = mti.inventory_item_id
AND STAR.organization_id = mti.organization_id)
WHEN MATCHED THEN
UPDATE SET STAR.value = mti.value
, STAR.po_category_id = mti.po_category_id
, STAR.po_category_set_id = mti.po_category_set_id
, STAR.po_concat_seg = mti.po_concat_seg
, STAR.inv_category_id = mti.inv_category_id
, STAR.inv_category_set_id = mti.inv_category_set_id
, STAR.inv_concat_seg = mti.inv_concat_seg
, STAR.vbh_category_id = mti.vbh_category_id
, STAR.vbh_category_set_id = mti.vbh_category_set_id
, STAR.vbh_concat_seg = mti.vbh_concat_seg
, STAR.master_id = mti.master_id
, STAR.item_catalog_group_id = mti.item_catalog_group_id
, STAR.primary_uom_code = mti.primary_uom_code
, STAR.unit_weight = mti.unit_weight
, STAR.unit_volume = mti.unit_volume
, STAR.weight_uom_code = mti.weight_uom_code
, STAR.volume_uom_code = mti.volume_uom_code
, STAR.eam_item_type = mti.eam_item_type
, STAR.last_update_date = mti.last_update_date
WHEN NOT MATCHED THEN
INSERT (
id,
value,
inventory_item_id,
organization_id,
po_category_id,
po_category_set_id,
po_concat_seg,
inv_category_id,
inv_category_set_id,
inv_concat_seg,
vbh_category_id,
vbh_category_set_id,
vbh_concat_seg,
master_id,
item_catalog_group_id,
primary_uom_code,
unit_weight,
unit_volume,
weight_uom_code,
volume_uom_code,
eam_item_type,
creation_date,
last_update_date)
VALUES(
mti.inventory_item_id || ''-'' || mti.organization_id,
mti.value,
mti.inventory_item_id,
mti.organization_id,
mti.po_category_id,
mti.po_category_set_id,
mti.po_concat_seg,
mti.inv_category_id,
mti.inv_category_set_id,
mti.inv_concat_seg,
mti.vbh_category_id,
mti.vbh_category_set_id,
mti.vbh_concat_seg,
mti.master_id,
mti.item_catalog_group_id,
mti.primary_uom_code,
mti.unit_weight,
mti.unit_volume,
mti.weight_uom_code,
mti.volume_uom_code,
mti.eam_item_type,
mti.creation_date,
mti.last_update_date)';
USING (SELECT item.inventory_item_id inventory_item_id,
item.organization_id organization_id,
item.CONCATENATED_SEGMENTS|| '' ('' || mtp.organization_code || '')''value,
decode(item.organization_id,mtp.master_organization_id,null,
item.inventory_item_id || ''-'' || mtp.master_organization_id)
master_id,
nvl(mic.category_id,-1) inv_category_id,
nvl(mic.category_set_id, :l_inv_category_set) inv_category_set_id,
nvl(kfv.concatenated_segments,''Unassigned'') inv_concat_seg,
nvl(mic1.category_id,-1) vbh_category_id,
nvl(mic1.category_set_id, :l_vbh_category_set) vbh_category_set_id,
nvl(kfv1.concatenated_segments,''Unassigned'') vbh_concat_seg,
nvl(mic2.category_id,-1) po_category_id,
nvl(mic2.category_set_id, :l_po_category_set) po_category_set_id,
nvl(kfv2.concatenated_segments,''Unassigned'') po_concat_seg,
nvl(item.item_catalog_group_id,-1) item_catalog_group_id,
item.primary_uom_code
, item.unit_weight
, item.unit_volume
, item.weight_uom_code
, item.volume_uom_code
, item.eam_item_type
, item.creation_date
, item.last_update_date
FROM mtl_system_items_interface interface
, mtl_system_items_b_kfv item
, mtl_parameters mtp
, mtl_item_categories mic
, mtl_categories_b_kfv kfv
, mtl_item_categories mic1
, mtl_categories_b_kfv kfv1
, mtl_item_categories mic2
, mtl_categories_b_kfv kfv2
WHERE item.inventory_item_id = interface.inventory_item_id
AND interface.set_process_id =
:p_set_process_id
AND interface.process_flag = 7
AND item.organization_id = interface.organization_id
AND item.organization_id= mtp.organization_id
AND mic.organization_id(+) = item.organization_id
AND mic.inventory_item_id(+) = item.inventory_item_id
AND mic.category_id = kfv.category_id (+)
and mic.category_set_id(+) = :l_inv_category_set
AND mic1.organization_id(+) = item.organization_id
AND mic1.inventory_item_id(+) = item.inventory_item_id
AND mic1.category_id = kfv1.category_id (+)
and mic1.category_set_id(+) = :l_vbh_category_set
AND mic2.organization_id(+) = item.organization_id
AND mic2.inventory_item_id(+) = item.inventory_item_id
AND mic2.category_id = kfv2.category_id (+)
and mic2.category_set_id(+) = :l_po_category_set) mti
ON (STAR.inventory_item_id = mti.inventory_item_id
AND STAR.organization_id = mti.organization_id)
WHEN MATCHED THEN
UPDATE SET STAR.value = mti.value
, STAR.po_category_id = mti.po_category_id
, STAR.po_category_set_id = mti.po_category_set_id
, STAR.po_concat_seg = mti.po_concat_seg
, STAR.inv_category_id = mti.inv_category_id
, STAR.inv_category_set_id = mti.inv_category_set_id
, STAR.inv_concat_seg = mti.inv_concat_seg
, STAR.vbh_category_id = mti.vbh_category_id
, STAR.vbh_category_set_id = mti.vbh_category_set_id
, STAR.vbh_concat_seg = mti.vbh_concat_seg
, STAR.master_id = mti.master_id
, STAR.item_catalog_group_id = mti.item_catalog_group_id
, STAR.primary_uom_code = mti.primary_uom_code
, STAR.unit_weight = mti.unit_weight
, STAR.unit_volume = mti.unit_volume
, STAR.weight_uom_code = mti.weight_uom_code
, STAR.volume_uom_code = mti.volume_uom_code
, STAR.eam_item_type = mti.eam_item_type
, STAR.last_update_date = mti.last_update_date
WHEN NOT MATCHED THEN
INSERT (
id,
value,
inventory_item_id,
organization_id,
po_category_id,
po_category_set_id,
po_concat_seg,
inv_category_id,
inv_category_set_id,
inv_concat_seg,
vbh_category_id,
vbh_category_set_id,
vbh_concat_seg,
master_id,
item_catalog_group_id,
primary_uom_code,
unit_weight,
unit_volume,
weight_uom_code,
volume_uom_code,
eam_item_type,
creation_date,
last_update_date)
VALUES(
mti.inventory_item_id || ''-'' || mti.organization_id,
mti.value,
mti.inventory_item_id,
mti.organization_id,
mti.po_category_id,
mti.po_category_set_id,
mti.po_concat_seg,
mti.inv_category_id,
mti.inv_category_set_id,
mti.inv_concat_seg,
mti.vbh_category_id,
mti.vbh_category_set_id,
mti.vbh_concat_seg,
mti.master_id,
mti.item_catalog_group_id,
mti.primary_uom_code,
mti.unit_weight,
mti.unit_volume,
mti.weight_uom_code,
mti.volume_uom_code,
mti.eam_item_type,
mti.creation_date,
mti.last_update_date)';
UPDATE eni_denorm_hierarchies B
SET
item_assgn_flag = 'Y',
last_update_date = sysdate,
last_updated_by = l_user_id,
last_update_login = l_user_id,
request_id = l_conc_request_id,
program_application_id = l_prog_appl_id,
program_update_date = sysdate,
program_id = l_conc_program_id
WHERE b.object_type = 'CATEGORY_SET'
AND b.object_id = l_vbh_category_set
AND b.item_assgn_flag = 'N'
AND b.child_id = (SELECT DEFAULT_CATEGORY_ID
FROM mtl_category_sets_b
WHERE category_set_id=l_vbh_category_set)
AND EXISTS (SELECT NULL
FROM mtl_item_categories C
WHERE c.category_set_id = l_vbh_category_set
AND c.category_id = b.child_id);
commenting this update as IOI(Item Create) can only result in creation of item assignment
This update statement will always fetch zero rows.
-- updating Item Assignment flag for all categories, which does not have items attached to it
UPDATE eni_denorm_hierarchies b
SET
item_assgn_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = l_user_id,
last_update_login = l_user_id,
request_id = l_conc_request_id,
program_application_id = l_prog_appl_id,
program_update_date = SYSDATE,
program_id = l_conc_program_id
WHERE b.object_type = 'CATEGORY_SET'
AND b.object_id = l_vbh_category_set
AND b.item_assgn_flag = 'Y'
AND b.child_id <> -1
AND NOT EXISTS (SELECT NULL
FROM mtl_item_categories C
WHERE c.category_set_id = l_vbh_category_set
AND c.category_id = b.child_id);
SELECT 1 INTO l_count
FROM ENI_OLTP_ITEM_STAR star
WHERE star.vbh_category_id = -1
AND rownum = 1;
SELECT 1 INTO l_count
FROM mtl_system_items_b IT
WHERE ROWNUM = 1
AND NOT EXISTS (SELECT NULL FROM mtl_item_categories C
WHERE c.category_set_id = l_vbh_category_set
AND c.inventory_item_id = it.inventory_item_id
AND c.organization_id = it.organization_id);
UPDATE eni_denorm_hierarchies b
SET
item_assgn_flag = decode(l_count, 0, 'N', 'Y'),
last_update_date = sysdate,
last_updated_by = l_user_id,
last_update_login = l_user_id,
request_id = l_conc_request_id,
program_application_id = l_prog_appl_id,
program_update_date = sysdate,
program_id = l_conc_program_id
WHERE b.object_type = 'CATEGORY_SET'
AND b.object_id = l_vbh_category_set
AND b.item_assgn_flag = DECODE(l_count, 0, 'Y', 'N')
AND b.child_id = -1
AND b.parent_id = -1;
SELECT category_set_id
FROM mtl_default_category_sets
WHERE functional_area_id = 2;
l_num_updates NUMBER := 0;
SELECT mici.inventory_item_id
,mp.organization_id
FROM mtl_item_categories_interface mici
,mtl_parameters mp
WHERE mici.set_process_id = p_set_process_id
AND mici.request_id = l_conc_request_id
AND mici.process_flag = l_process_flag
AND ( mici.category_set_id = l_INV_category_set_id
OR mici.category_set_id = l_VBH_category_set_id
OR mici.category_set_id = l_PO_category_set_id)
AND ( mici.organization_id = mp.organization_id
OR mici.organization_id = mp.master_organization_id);
SELECT category_set_id INTO l_inv_category_set_id
FROM mtl_default_category_sets
WHERE functional_area_id = 1;
UPDATE eni_oltp_item_star star
SET (
star.INV_CATEGORY_ID
,star.INV_CONCAT_SEG
,star.INV_CATEGORY_SET_ID
,star.VBH_CATEGORY_ID
,star.VBH_CONCAT_SEG
,star.VBH_CATEGORY_SET_ID
,star.PO_CATEGORY_ID
,star.PO_CONCAT_SEG
,star.PO_CATEGORY_SET_ID)
=
( SELECT
nvl(mic.category_id, -1) inv_category_id
,nvl(kfv.concatenated_segments, 'Unassigned') inv_concat_seg
,nvl(mic.category_Set_id, l_INV_category_set_id) inv_category_Set_id
,nvl(mic1.category_id, -1) vbh_category_id
,nvl(kfv1.concatenated_segments, 'Unassigned') vbh_concat_seg
,nvl(mic1.category_Set_id, l_VBH_category_set_id) vbh_category_set_id
,nvl(mic2.category_id, -1) po_category_id
,nvl(kfv2.concatenated_segments, 'Unassigned') po_concat_seg
,nvl(mic2.category_Set_id, l_PO_category_set_id) po_category_set_id
FROM
mtl_system_items_b msi
, mtl_item_categories mic
, mtl_categories_b_kfv kfv
, mtl_item_categories mic1
, mtl_categories_b_kfv kfv1
, mtl_item_categories mic2
, mtl_categories_b_kfv kfv2
WHERE
msi.inventory_item_id = star.inventory_item_id
AND msi.organization_id = star.organization_id
AND mic.inventory_item_id (+) = msi.inventory_item_id
AND mic.organization_id (+) = msi.organization_id
AND mic.category_id = kfv.category_id (+)
AND mic.category_set_id (+) = l_INV_category_set_id
AND mic1.inventory_item_id (+) = msi.inventory_item_id
AND mic1.organization_id (+) = msi.organization_id
AND mic1.category_id = kfv1.category_id (+)
AND mic1.category_set_id (+) = l_VBH_category_set_id
AND mic2.inventory_item_id (+) = msi.inventory_item_id
AND mic2.organization_id (+) = msi.organization_id
AND mic2.category_id = kfv2.category_id (+)
AND mic2.category_set_id (+) = l_PO_category_set_id)
WHERE star.inventory_item_id = sync_itmcatg.inventory_item_id
AND star.organization_id = sync_itmcatg.organization_id;
Only update the categories which are modified in this run
Only Create, update can cause an assigment creation
**/
-- updating Item Assignment flag for all categories,
-- which have items attached to it
FOR intf_categories_add IN (SELECT DISTINCT CATEGORY_ID
FROM mtl_item_categories_interface
WHERE process_flag = 7
AND transaction_type IN ('CREATE','UPDATE')
AND set_process_id = p_set_process_id
AND category_set_id = l_vbh_category_set_id)
LOOP
UPDATE eni_denorm_hierarchies B
SET
item_assgn_flag = 'Y',
last_update_date = sysdate,
last_updated_by = l_user_id,
last_update_login = l_user_id,
request_id = l_conc_request_id,
program_application_id = l_prog_appl_id,
program_update_date = sysdate,
program_id = l_conc_program_id
WHERE b.object_type = 'CATEGORY_SET'
AND b.object_id = l_vbh_category_set_id
AND b.item_assgn_flag = 'N'
AND b.child_id = intf_categories_add.category_id
AND EXISTS (SELECT NULL
FROM mtl_item_categories C
WHERE c.category_set_id = l_vbh_category_set_id
AND c.category_id = b.child_id);
l_num_updates := l_num_updates + SQL%ROWCOUNT;
(SELECT DISTINCT
Decode(TRANSACTION_TYPE,
'UPDATE',OLD_CATEGORY_ID,
CATEGORY_ID) AS CATEGORY_ID
FROM mtl_item_categories_interface
WHERE process_flag = 7
AND TRANSACTION_TYPE IN ('DELETE','UPDATE')
AND set_process_id = p_set_process_id
AND category_set_id = l_vbh_category_set_id)
LOOP
UPDATE eni_denorm_hierarchies b
SET
item_assgn_flag = 'N',
last_update_date = SYSDATE,
last_updated_by = l_user_id,
last_update_login = l_user_id,
request_id = l_conc_request_id,
program_application_id = l_prog_appl_id,
program_update_date = SYSDATE,
program_id = l_conc_program_id
WHERE b.object_type = 'CATEGORY_SET'
AND b.object_id = l_vbh_category_set_id
AND b.item_assgn_flag = 'Y'
AND b.child_id = intf_categories_del.category_id
AND NOT EXISTS (SELECT NULL
FROM mtl_item_categories C
WHERE c.category_set_id = l_vbh_category_set_id
AND c.category_id = b.child_id);
l_num_updates := l_num_updates + SQL%ROWCOUNT;
We need to update UNSASSIGNED category only if there is
any upate on ENI_DENORM table in the above two SQLs
**/
IF l_num_updates <> 0 THEN
l_count := 0;
SELECT 1 INTO l_count
FROM ENI_OLTP_ITEM_STAR star
WHERE star.vbh_category_id = -1
AND rownum = 1;
SELECT 1 INTO l_count
FROM mtl_system_items_b IT
WHERE ROWNUM = 1
AND NOT EXISTS (SELECT NULL FROM mtl_item_categories C
WHERE c.category_set_id = l_vbh_category_set_id
AND c.inventory_item_id = it.inventory_item_id
AND c.organization_id = it.organization_id);
UPDATE eni_denorm_hierarchies b
SET
item_assgn_flag = decode(l_count, 0, 'N', 'Y'),
last_update_date = sysdate,
last_updated_by = l_user_id,
last_update_login = l_user_id,
request_id = l_conc_request_id,
program_application_id = l_prog_appl_id,
program_update_date = sysdate,
program_id = l_conc_program_id
WHERE b.object_type = 'CATEGORY_SET'
AND b.object_id = l_vbh_category_set_id
AND b.item_assgn_flag = DECODE(l_count, 0, 'Y', 'N')
AND b.child_id = -1
AND b.parent_id = -1;
SELECT NVL(LENGTH(CONCATENATED_SEGMENTS),0) INTO G_INSTALL_PHASE
FROM MTL_SYSTEM_ITEMS_B_KFV
WHERE ROWNUM = 1;