[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;
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
);
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;
DELETE FROM ENI_OLTP_ITEM_STAR WHERE inventory_item_id = -1 AND organization_id = -99;
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 );
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;
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;
SELECT msik.concatenated_segments || ' (' || mp.organization_code || ')' Value INTO l_pres_item_number
FROM mtl_system_items_b_kfv msik,
mtl_parameters mp
WHERE msik.inventory_item_id=p_inventory_item_id
AND msik.organization_id=mp.organization_id
AND mp.organization_id=p_organization_id;
SELECT Value INTO l_prev_item_number FROM eni_oltp_item_star
WHERE inventory_item_id=p_inventory_item_id
AND organization_id=p_organization_id;
It is safe to update table ENI_OLTP_ITEM_STAR with values from MTL_SYSTEM_ITEMS_B_KFV
*/
RETURN TRUE ;
/*This procedure will update the Item number for all Remaining master orgs and their hierarchies
in which item exists. */
PROCEDURE Update_Items_In_Rem_Mstr_Orgs( 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
/* change in item_number should be reflected for items in all orgs.
Below logic will update only value field for the items of remaining master orgs and thier hierarchy.
*/
CURSOR c_items_in_master IS
SELECT mti.concatenated_segments || ' (' || b.organization_code || ')' value
, 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
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;
UPDATE ENI_OLTP_ITEM_STAR
SET VALUE = c_items_in_master_rec.value
, LAST_UPDATE_DATE = c_items_in_master_rec.last_update_date
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 = c_items_in_master_rec.value
, LAST_UPDATE_DATE = c_items_in_master_rec.last_update_date
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)
VALUES(
p_inventory_item_id
, c_items_in_master_rec.organization_id
, 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
);
FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'UPDATE_ITEMS_IN_REM_MSTR_ORGS', SQLERRM);
END Update_Items_In_Rem_Mstr_Orgs;
/* 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;
then call the procedure Update_Items_In_Rem_Mstr_Orgs() to change the item
in all remaining Master orgs and their hierarchies where it is assigned
Eg:
Item123 created in two master orgs V1 and PM.
Item assigned to child orgs M1(child of V1) and P2(child of PM).
If item number changed in V1, then it also need to be changed in V1 hierarchy (i.e M1) and PM hierarchy (i.e P2). */
IF is_item_number_changed(p_inventory_item_id,p_organization_id)=TRUE THEN
Update_Items_In_Rem_Mstr_Orgs(p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_inventory_item_id => p_inventory_item_id
,p_organization_id => p_organization_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
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 /*+ first_rows index(interface, MTL_SYSTEM_ITEMS_INTERFACE_N3) */
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 /*+ first_rows index(interface, MTL_SYSTEM_ITEMS_INTERFACE_N3) */
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;