[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UPDATE_SYNC_RECORDS(p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_category_set_id IN NUMBER,
p_transaction_id IN NUMBER,
p_row_id IN ROWID,
x_old_category_id OUT NOCOPY NUMBER,
x_transaction_type OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY NUMBER)
IS
CURSOR c_cat_assign_exists (cp_item_id NUMBER,
cp_org_id NUMBER,
cp_cat_set_id NUMBER)
IS
SELECT category_id FROM mtl_item_categories
WHERE inventory_item_id = cp_item_id
AND organization_id = cp_org_id
AND category_set_id = cp_cat_set_id;
SELECT mult_item_cat_assign_flag
FROM mtl_category_sets_b
WHERE category_set_id = cp_cat_set_id;
UPDATE mtl_item_categories_interface
SET process_flag = 3
WHERE rowid = p_row_id;
UPDATE mtl_item_categories_interface
SET old_category_id = l_category_id,
transaction_type = 'UPDATE'
WHERE rowid = p_row_id;
l_transaction_type := 'UPDATE';
UPDATE mtl_item_categories_interface
SET transaction_type = 'CREATE'
WHERE rowid = p_row_id;
END UPDATE_SYNC_RECORDS;
, p_delete_rec_flag IN NUMBER := 1
, p_commit_flag IN NUMBER := 1
, p_prog_appid IN NUMBER := NULL
, p_prog_id IN NUMBER := NULL
, p_request_id IN NUMBER := NULL
, p_user_id IN NUMBER := NULL
, p_login_id IN NUMBER := NULL
, p_gather_stats IN NUMBER := 1 /* Added for Bug 8532728 */
, p_validate_rec_flag IN NUMBER DEFAULT 1 /*Fix for bug 9714783 - moved p_validate_rec_flag parameter to the end*/
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'process_Item_Category_records';
SELECT
mici.rowid, mici.transaction_id
, mici.transaction_type
, mici.organization_id, mici.inventory_item_id
, mici.category_set_id, mici.category_id
, mici.organization_code, mici.item_number
, mici.category_set_name, mici.category_name
FROM
mtl_item_categories_interface mici
WHERE
set_process_id = g_xset_id
AND process_flag = 1
AND ( organization_id IS NULL
OR ( organization_id IS NOT NULL
AND NOT EXISTS
( SELECT mp.organization_id
FROM mtl_parameters mp
WHERE mp.organization_id = mici.organization_id
)
)
)
FOR UPDATE OF mici.transaction_id;
SELECT
mici.rowid, mici.transaction_id
, mici.transaction_type, mici.process_flag
, mici.organization_id, mici.inventory_item_id
, mici.category_set_id, mici.category_id
, mici.organization_code, mici.item_number
, mici.category_set_name, mici.category_name
, mici.old_category_id, mici.old_category_name --* Added for Bug #3991044
, mici.created_by
, mici.set_process_id,mici.source_system_reference -- Added for Bug 9305193 Fix
, mici.source_system_id
FROM
mtl_item_categories_interface mici
, mtl_parameters mp
WHERE
mici.set_process_id = g_xset_id
AND mici.organization_id = mp.organization_id
AND mici.process_flag IN (1, 2, 4) --R12C
ORDER BY
mp.master_organization_id ASC
, DECODE(mici.transaction_type, 'DELETE', 1, 'UPDATE', 2, 'CREATE', 3, 4) ASC
, DECODE(mp.organization_id, mp.master_organization_id, 1, 2) ASC
, mp.organization_id ASC
FOR UPDATE OF mici.transaction_id;
SELECT category_set_id, structure_id
FROM mtl_category_sets_vl
WHERE category_set_name = p_category_set_name;
SELECT inventory_item_id
FROM mtl_system_items_b_kfv
WHERE concatenated_segments = cp_item_number
AND organization_id = cp_organization_id;
SELECT inventory_item_id
FROM mtl_system_items_interface
WHERE item_number = cp_item_number
AND organization_id = cp_organization_id
AND set_process_id = cp_xset_id
AND process_flag IN (1,2,4);
SELECT rowid, transaction_id
, item_number, organization_id
FROM mtl_item_categories_interface
WHERE set_process_id = g_xset_id
AND inventory_item_id IS NULL
AND item_number IS NOT NULL
AND category_set_id IS NOT NULL
AND organization_id IS NOT NULL;
SELECT rowid, transaction_id
, category_name, organization_id, category_set_id
FROM mtl_item_categories_interface
WHERE set_process_id = g_xset_id
AND category_id IS NULL
AND category_name IS NOT NULL
AND category_set_id IS NOT NULL
AND organization_id IS NOT NULL
AND process_flag = l_process_flag_1;
SELECT i.transaction_id, i.organization_id
FROM mtl_item_categories_interface i
WHERE i.process_flag = l_process_flag_2
AND set_process_id = g_xset_id
AND ( i.organization_id = org_id OR all_org = l_All_Org )
AND (
(NOT EXISTS (select m.category_set_id
from mtl_category_sets_b m
where m.category_set_id = i.category_set_id )
)
OR
(NOT EXISTS (select m.category_id
from mtl_categories_b m,
mtl_category_sets_b ms
where m.category_id = i.category_id
and m.structure_id = ms.structure_id
and i.category_set_id = ms.category_set_id)
)
OR
(NOT EXISTS (select organization_id
from ORG_ORGANIZATION_DEFINITIONS OOD
where OOD.organization_id = i.organization_id)
)
);
SELECT mic.REQUEST_ID
,mic.INVENTORY_ITEM_ID
,mic.ORGANIZATION_ID
,mic.CATEGORY_SET_ID
,mic.CATEGORY_ID
,mic.TRANSACTION_TYPE
,mic.CREATION_DATE
,mic.CREATED_BY
,mic.LAST_UPDATE_DATE
,mic.LAST_UPDATED_BY
,mic.LAST_UPDATE_LOGIN
FROM MTL_ITEM_CATEGORIES_INTERFACE mic
WHERE REQUEST_ID = cp_request_id
AND set_process_id = cp_set_id
AND process_flag = 7;
l_records_updated VARCHAR2(1); --bUG 4527222
SELECT count(*) INTO l_records
FROM mtl_item_categories_interface
WHERE set_process_id = p_rec_set_id
AND process_flag IN (1,2,4); --R12C
SELECT SUM(
DECODE(FUNCTIONAL_AREA_ID,12,CATEGORY_SET_ID,0)) udex_catalog
,SUM(
DECODE(FUNCTIONAL_AREA_ID,21,CATEGORY_SET_ID,0)) gpc_catalog
INTO l_udex_catalog_id , l_gpc_catalog_id
FROM MTL_DEFAULT_CATEGORY_SETS
WHERE FUNCTIONAL_AREA_ID IN (12,21); --Bug 5517473 added functional area 21
UPDATE mtl_item_categories_interface mici
SET
( mici.organization_id
, process_flag
) =
( SELECT mp.organization_id, DECODE(p_validate_rec_flag, 2, 1, 2)
FROM mtl_parameters mp
WHERE mp.organization_code = mici.organization_code
)
WHERE
mici.set_process_id = g_xset_id
AND mici.process_flag = 1
AND mici.organization_id IS NULL
AND mici.organization_code IS NOT NULL
AND EXISTS
( SELECT mp2.organization_id
FROM mtl_parameters mp2
WHERE mp2.organization_code = mici.organization_code
);
SELECT mtl_system_items_interface_s.NEXTVAL
INTO l_transaction_id
FROM dual;
UPDATE mtl_item_categories_interface
SET
-- transaction_id = mtl_system_items_interface_s.NEXTVAL
transaction_id = l_transaction_id
, request_id = g_request_id
, process_flag = 3
WHERE CURRENT OF miss_org_id_csr;
INV_ITEM_MSG.Write_List (p_delete => TRUE);
SELECT mtl_system_items_interface_s.NEXTVAL
INTO l_transaction_id
FROM dual;
IF ( l_transaction_type NOT IN ('CREATE', 'DELETE','UPDATE', 'SYNC') ) THEN
l_return_status := fnd_api.g_RET_STS_ERROR;
IF ( l_transaction_type IN ('UPDATE', 'SYNC') AND (l_category_set_id IS NOT NULL)
AND (l_old_category_id IS NULL) ) THEN
IF (l_debug = 1) THEN
INV_ITEM_MSG.Debug(Mctx, 'assign missing old category_id');
SELECT Category_id
INTO l_old_category_id
FROM Mtl_Categories_B_Kfv
WHERE Structure_Id = ( SELECT Structure_Id
FROM mtl_category_sets_vl
WHERE category_set_id = l_category_set_id )
AND Concatenated_Segments = icoi_rec.old_category_name
AND NVL(disable_date,SYSDATE+1) > SYSDATE; -- fix bug 15949266
IF l_transaction_type = 'UPDATE' THEN
l_return_status := fnd_api.g_ret_sts_error;
SELECT structure_id INTO l_structure_id
FROM mtl_category_sets_b
WHERE category_set_id = l_category_set_id;
SELECT category_id INTO l_category_id
FROM mtl_categories_b_kfv
WHERE concatenated_segments = icoi_rec.category_name
-- bug 3500492
AND structure_id = l_structure_id
AND NVL(disable_date,SYSDATE+1) > SYSDATE;
SELECT item_num_gen_method INTO l_ItemNum_GenMethod
FROM mtl_item_Catalog_groups_b
WHERE item_catalog_group_id=
(SELECT DISTINCT(item_catalog_group_id)
FROM mtl_system_items_interface
WHERE set_process_id = icoi_rec.set_process_id
AND source_system_id = icoi_rec.source_system_id
AND source_system_reference = icoi_rec.source_system_reference
AND organization_code = icoi_rec.organization_code
AND process_flag IN (1));
SELECT count(1) INTO l_item_count
FROM mtl_system_items_b
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id;
UPDATE_SYNC_RECORDS(p_inventory_item_id => l_inventory_item_id,
p_organization_id => l_organization_id,
p_category_set_id => l_category_set_id,
p_transaction_id => l_transaction_id,
p_row_id => icoi_rec.rowid,
x_old_category_id => l_ret_old_category_id,
x_transaction_type => l_transaction_type,
x_return_status => l_return_status_flag);
IF l_transaction_type = 'UPDATE' AND l_ret_old_category_id IS NOT NULL THEN
l_old_category_id := l_ret_old_category_id;
l_msg_name := 'INV_IOI_ITEM_UPDATE_PRIV';
Select concatenated_segments into l_item_number
From mtl_system_items_b_kfv
where INVENTORY_ITEM_ID = l_inventory_item_id
AND organization_id = l_organization_id; -- org
INV_ITEM_MSG.Write_List (p_delete => TRUE);
IF ( l_transaction_type = 'DELETE' ) THEN
IF (l_debug = 1) THEN
INV_ITEM_MSG.Debug(Mctx, 'calling INV_ITEM_CATEGORY_PVT.Delete_Category_Assignment');
INV_ITEM_CATEGORY_PVT.Delete_Category_Assignment
(
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_TRUE
, p_commit => fnd_api.g_FALSE
, p_inventory_item_id => l_inventory_item_id
, p_organization_id => l_organization_id
, p_category_set_id => l_category_set_id
, p_category_id => l_category_id
, p_transaction_id => l_transaction_id
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
INV_ITEM_MSG.Debug(Mctx, 'error in Delete_Category_Assignment. Msg count=' || TO_CHAR(INV_ITEM_MSG.Count_Msg));
-- Bug 5517473 removing the call to process_cat_assignment it is same as update_reg_pub_update_dates
BEGIN
EGO_GTIN_PVT.UPDATE_REG_PUB_UPDATE_DATES
(p_inventory_item_id => l_inventory_item_id,
p_organization_id => l_organization_id,
p_update_reg => 'Y',
p_commit => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
INV_ITEM_MSG.Debug(Mctx, 'Value for l_transaction_type before update'|| l_transaction_type);
ELSIF ( l_transaction_type = 'UPDATE' ) THEN
INV_ITEM_MSG.Debug(Mctx, 'Value for l_transaction_type inside update'|| l_transaction_type);
INV_ITEM_MSG.Debug(Mctx, 'calling INV_ITEM_CATEGORY_PVT.Update_Category_Assignment');
INV_ITEM_CATEGORY_PVT.Update_Category_Assignment
( p_api_version => 1.0
, p_init_msg_list => fnd_api.g_TRUE
, p_commit => fnd_api.g_FALSE
, p_inventory_item_id => l_inventory_item_id
, p_organization_id => l_organization_id
, p_category_set_id => l_category_set_id
, p_category_id => l_category_id
, p_old_category_id => l_old_category_id
, p_transaction_id => l_transaction_id
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
-- Bug 5517473 removing the call to process_cat_assignment it is same as update_reg_pub_update_dates
/*BEGIN
EXECUTE IMMEDIATE 'BEGIN EGO_GTIN_PVT.PROCESS_CAT_ASSIGNMENT( :1, :2); END;' USING l_inventory_item_id, l_organization_id;
/* Bug 5517473 - Submit for Re-Registration of GDSN attrs when GDSN/GPC category set updated */
BEGIN
EGO_GTIN_PVT.UPDATE_REG_PUB_UPDATE_DATES
(p_inventory_item_id => l_inventory_item_id,
p_organization_id => l_organization_id,
p_update_reg => 'Y',
p_commit => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
INV_ITEM_MSG.Debug(Mctx, 'error in Update_Category_Assignment ' || l_msg_data);
INV_ITEM_MSG.Debug(Mctx, 'error in Update_Category_Assignment. Msg count=' || TO_CHAR(INV_ITEM_MSG.Count_Msg));
ELSIF( l_transaction_type = 'DELETE' ) THEN
INV_ENI_ITEMS_STAR_PKG.Sync_Category_Assignments(
p_api_version => 1.0
,p_init_msg_list => FND_API.g_TRUE
,p_inventory_item_id => l_inventory_item_id
,p_organization_id => l_organization_id
,p_category_set_id => l_category_set_id
,p_old_category_id => l_category_id
,p_new_category_id => NULL
,x_return_status => l_return_Status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
INV_ITEM_MSG.Write_List (p_delete => TRUE);
INV_ITEM_MSG.Debug(Mctx, 'update interface record');
UPDATE mtl_item_categories_interface
SET
transaction_id = l_transaction_id
, transaction_type = l_transaction_type
, process_flag = l_process_flag
, inventory_item_id = NVL(l_inventory_item_id, inventory_item_id)
, category_set_id = NVL(l_category_set_id, category_set_id)
, category_id = NVL(l_category_id, category_id)
, program_application_id = g_prog_appid
, program_id = g_prog_id
, program_update_date = SYSDATE
, request_id = g_request_id
, last_update_date = SYSDATE
, last_updated_by = g_user_id
, last_update_login = g_login_id
WHERE
CURRENT OF icoi_csr;
SELECT 'Y'
INTO l_records_updated
FROM mtl_item_categories_interface mici
WHERE mici.set_process_id = g_xset_id
AND mici.process_flag = 7
AND ROWNUM = 1;
l_records_updated := 'N';
l_records_updated := 'Y';
IF l_records_updated = 'Y' THEN
BEGIN
INV_ENI_ITEMS_STAR_PKG.Sync_Star_ItemCatg_From_COI(
p_api_version => 1.0
,p_init_msg_list => FND_API.g_TRUE
,p_set_process_id => g_xset_id
,x_return_status => l_return_Status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
INSERT INTO MTL_ITEM_BULKLOAD_RECS(
REQUEST_ID
,ENTITY_TYPE
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,CATEGORY_SET_ID
,CATEGORY_ID
,TRANSACTION_TYPE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN)
VALUES(
cr.REQUEST_ID
,'ITEM_CATEGORY'
,cr.INVENTORY_ITEM_ID
,cr.ORGANIZATION_ID
,cr.CATEGORY_SET_ID
,cr.CATEGORY_ID
,cr.TRANSACTION_TYPE
,NVL(cr.CREATION_DATE, SYSDATE)
,decode(cr.CREATED_BY, -99, g_user_id, NULL, g_user_id, cr.CREATED_BY)
,NVL(cr.LAST_UPDATE_DATE, SYSDATE)
,NVL(cr.LAST_UPDATED_BY, g_user_id)
,cr.LAST_UPDATE_LOGIN);
IF ( p_delete_rec_flag = 1 ) THEN
IF (l_debug = 1) THEN
INV_ITEM_MSG.Debug(Mctx, 'calling delete_OI_records');
INV_ITEM_CATEGORY_OI.delete_OI_records
( p_commit => l_commit
, p_rec_set_id => g_xset_id
, x_return_status => l_return_status
);
INV_ITEM_MSG.Debug(Mctx, 'done delete_OI_records: return_status=' || l_return_status);
INV_ITEM_MSG.Write_List (p_delete => TRUE);
END IF; -- p_delete_rec_flag = 1
INV_ITEM_MSG.Write_List (p_delete => TRUE);
INV_ITEM_MSG.Write_List (p_delete => TRUE);
PROCEDURE delete_OI_records
(
p_commit IN VARCHAR2 DEFAULT fnd_api.g_FALSE
, p_rec_set_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_OI_records';
l_del_process_flag NUMBER := 7; -- process_flag value for records to be deleted
DELETE FROM mtl_item_categories_interface
WHERE set_process_id = p_rec_set_id
AND process_flag = l_del_process_flag
AND rownum < G_ROWS_TO_COMMIT;
END delete_OI_records;