The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_mtl_system_items( p_user_id IN NUMBER,
p_organization_id IN NUMBER,
p_category_set_id IN NUMBER,
p_category_id IN NUMBER,
p_last_run_date IN DATE,
p_changed IN VARCHAR2)
IS
l_run_date DATE;
SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
FROM MTL_SYSTEM_ITEMS_B msi
WHERE msi.organization_id = b_organization_id
AND (b_changed = 'Y'
OR msi.creation_date >= NVL(b_last_run_date, msi.creation_date))
AND NOT EXISTS
( SELECT 1
FROM csm_mtl_system_items_acc acc
WHERE user_id = b_user_id
AND acc.inventory_item_id = msi.inventory_item_id
AND acc.organization_id = msi.organization_id);
SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
FROM mtl_system_items_b msi
WHERE NOT EXISTS
(SELECT 1
FROM csm_mtl_system_items_acc acc
WHERE user_id = b_user_id
AND acc.inventory_item_id = msi.inventory_item_id
AND acc.organization_id = msi.organization_id
)
AND msi.organization_id = b_organization_id
AND msi.inventory_item_id IN
(SELECT itemcat.inventory_item_id
FROM mtl_item_categories itemcat
WHERE itemcat.category_id = b_category_id
AND itemcat.organization_id = b_organization_id
AND (b_changed = 'Y'
OR itemcat.creation_date >= NVL(b_last_run_date, itemcat.CREATION_DATE)
)
);
SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
FROM mtl_system_items_b msi
WHERE NOT EXISTS
(SELECT 1
FROM csm_mtl_system_items_acc acc
WHERE user_id = b_user_id
AND acc.inventory_item_id = msi.inventory_item_id
AND acc.organization_id = msi.organization_id
)
AND msi.organization_id = b_organization_id
AND msi.inventory_item_id IN
(SELECT itemcat.inventory_item_id
FROM mtl_item_categories itemcat
WHERE itemcat.category_set_id = b_category_set_id
AND itemcat.organization_id = b_organization_id
AND (b_changed = 'Y'
OR itemcat.creation_date >= NVL(b_last_run_date, itemcat.CREATION_DATE)
)
);
SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
FROM mtl_system_items_b msi
WHERE NOT EXISTS
(SELECT 1
FROM csm_mtl_system_items_acc acc
WHERE user_id = b_user_id
AND acc.inventory_item_id = msi.inventory_item_id
AND acc.organization_id = msi.organization_id
)
AND msi.organization_id = b_organization_id
AND msi.inventory_item_id IN
(SELECT itemcat.inventory_item_id
FROM mtl_item_categories itemcat
WHERE itemcat.category_set_id = b_category_set_id
AND itemcat.category_id = b_category_id
AND itemcat.organization_id = b_organization_id
AND (b_changed = 'Y'
OR itemcat.creation_date >= NVL(b_last_run_date, itemcat.CREATION_DATE)
)
);
CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS ',
'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
l_stmt := 'UPDATE csm_mtl_system_items_acc';
l_stmt := l_stmt || ' , last_update_date = SYSDATE';
l_stmt := l_stmt || ' , last_updated_by = 1';
l_stmt := l_stmt || ' (SELECT inventory_item_id';
ELSE -- if p_changed = 'N', delete items that are no longer assigned to the category/category set
-- changed to dynamic sql to support either category or category set
l_stmt := NULL;
l_stmt := 'UPDATE csm_mtl_system_items_acc acc';
l_stmt := l_stmt || ' , last_update_date = SYSDATE';
l_stmt := l_stmt || ' , last_updated_by = 1';
l_stmt := l_stmt || ' (SELECT 1';
l_stmt := l_stmt || ' SELECT 1';
l_tab_access_id.DELETE;
l_tab_user_id.DELETE;
SELECT access_id, user_id
BULK COLLECT INTO l_tab_access_id, l_tab_user_id
FROM csm_mtl_system_items_acc acc
WHERE acc.counter = 0;
DELETE FROM csm_mtl_system_items_acc WHERE access_id = l_tab_access_id(i);
l_tab_access_id.DELETE;
l_inventory_item_id_tbl.DELETE;
l_organization_id_tbl.DELETE;
l_tab_user_id.DELETE;
CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
|| ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
created_by, creation_date, last_updated_by, last_update_date, last_update_login)
VALUES (l_tab_access_id(i), p_user_id, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
l_tab_access_id.DELETE;
l_inventory_item_id_tbl.DELETE;
l_organization_id_tbl.DELETE;
l_tab_user_id.DELETE;
CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
|| ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
created_by, creation_date, last_updated_by, last_update_date, last_update_login)
VALUES (l_tab_access_id(i), p_user_id, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
l_tab_access_id.DELETE;
l_inventory_item_id_tbl.DELETE;
l_organization_id_tbl.DELETE;
l_tab_user_id.DELETE;
CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
|| ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
created_by, creation_date, last_updated_by, last_update_date, last_update_login)
VALUES (l_tab_access_id(i), p_user_id, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
l_tab_access_id.DELETE;
l_inventory_item_id_tbl.DELETE;
l_organization_id_tbl.DELETE;
l_tab_user_id.DELETE;
CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
|| ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
created_by, creation_date, last_updated_by, last_update_date, last_update_login)
VALUES (l_tab_access_id(i), p_user_id, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS ',
'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
l_error_msg := ' Exception in INSERT_MTL_SYSTEM_ITEMS for user_id :'
|| to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
END INSERT_MTL_SYSTEM_ITEMS;
PROCEDURE update_mtl_system_items(p_last_run_date IN DATE)
IS
l_sqlerrno VARCHAR2(20);
l_max_last_update_date_b DATE;
l_max_last_update_date_tl DATE;
SELECT /*+ INDEX (acc CSM_MTL_SYSTEM_ITEMS_ACC_U1) INDEX(msi MTL_SYSTEM_ITEMS_B_U1) */
acc.access_id, acc.user_id
FROM csm_mtl_system_items_acc acc, mtl_system_items_b msi
WHERE msi.inventory_item_id = acc.inventory_item_id
AND msi.organization_id = acc.organization_id
AND (msi.last_update_date >= b_last_date);
CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS ',
'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
/* This portion of code assumes indexes on last_update_date on MTL_SYSTEM_ITEMS_B */
/* , MTL_SYSTEM_ITEMS_TL which were custom created */
SELECT MAX(last_update_date) INTO l_max_last_update_date_b
FROM mtl_system_items_b;
IF( l_max_last_update_date_b < p_last_run_date) THEN
SELECT MAX(last_update_date) INTO l_max_last_update_date_tl
FROM mtl_system_items_tl;
IF(l_max_last_update_date_tl < p_last_run_date) THEN
-- No updates
CSM_UTIL_PKG.LOG('Leaving UPDATE_MTL_SYSTEM_ITEMS - No Updates ',
'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
l_tab_access_id.DELETE;
l_tab_user_id.DELETE;
'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS ',
'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
l_error_msg := ' Exception in UPDATE_MTL_SYSTEM_ITEMS :' || l_sqlerrno || ':' || l_sqlerrmsg;
CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
END UPDATE_MTL_SYSTEM_ITEMS;
PROCEDURE delete_mtl_system_items(p_user_id IN NUMBER,
p_organization_id IN NUMBER,
p_category_set_id IN NUMBER,
p_category_id IN NUMBER)
IS
l_sqlerrno VARCHAR2(20);
CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS ',
'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
l_tab_access_id.DELETE;
l_stmt := 'UPDATE csm_mtl_system_items_acc acc';
l_stmt := l_stmt || ' , LAST_UPDATE_DATE = SYSDATE';
l_stmt := l_stmt || ' , last_updated_by = nvl(fnd_global.user_id, 1)';
l_stmt := l_stmt || ' SELECT 1 ';
l_tab_access_id.DELETE;
l_tab_user_id.DELETE;
SELECT access_id, user_id
BULK COLLECT INTO l_tab_access_id, l_tab_user_id
FROM csm_mtl_system_items_acc acc
WHERE acc.counter = 0;
DELETE FROM csm_mtl_system_items_acc WHERE access_id = l_tab_access_id(i);
CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS ',
'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
END DELETE_MTL_SYSTEM_ITEMS;
SELECT organization_id, category_set_id, category_id
FROM csm_user_inventory_org
WHERE user_id = b_user_id
FOR UPDATE;
|| ' - Inserting all mtl_system_items',
'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
INSERT INTO csm_user_inventory_org (
user_id, organization_id, last_update_date, last_updated_by,
creation_date, created_by, category_set_id, category_id )
VALUES (
p_user_id, p_organization_id, SYSDATE, 1, SYSDATE, 1,
p_category_set_id, p_category_id );
csm_system_item_event_pkg.delete_system_items(p_user_id=>p_user_id,
p_organization_id=>r_org.organization_id);
delete_mtl_system_items(p_user_id=>p_user_id,
p_organization_id=>r_org.organization_id,
p_category_set_id=>r_org.category_set_id,
p_category_id=>r_org.category_id);
CSM_UTIL_PKG.LOG('Inserting records for new profile settings for user_id: ' || TO_CHAR(p_user_id),
'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
insert_mtl_system_items(p_user_id=>p_user_id,
p_organization_id=>p_organization_id,
p_category_set_id=>p_category_set_id,
p_category_id=>p_category_id,
p_last_run_date=>NULL,
p_changed=>'Y');
CSM_UTIL_PKG.LOG('Update csm_user_inventory_org with new profile settings for user_id: ' || TO_CHAR(p_user_id),
'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
UPDATE csm_user_inventory_org
SET organization_id = p_organization_id
, category_set_id = p_category_set_id
, category_id = p_category_id
, last_update_date = SYSDATE
WHERE CURRENT OF c_org;
insert_mtl_system_items(p_user_id=>p_user_id,
p_organization_id=>p_organization_id,
p_category_set_id=>p_category_set_id,
p_category_id=>p_category_id,
p_last_run_date=>p_last_run_date,
p_changed=>'N');
l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
SELECT NVL(last_run_date, TO_DATE('1','J'))
FROM jtm_con_request_data
WHERE package_name = 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG'
AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';
FETCH l_last_run_date_csr INTO l_prog_update_date;
update_mtl_system_items(p_last_run_date => l_prog_update_date);
p_last_run_date=>l_prog_update_date);
UPDATE jtm_con_request_data
SET last_run_date = l_run_date
WHERE package_name = 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG'
AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';
insert_mtl_system_items(p_user_id=>p_user_id,
p_organization_id=>p_organization_id,
p_category_set_id=>p_category_set_id,
p_category_id=>p_category_id,
p_last_run_date=>NULL,
p_changed=>'N'); -- new user, no profiles are changed
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_mtl_sys_items_pubi_name
,P_ACC_TABLE_NAME => g_mtl_sys_items_acc_table_name
,P_SEQ_NAME => g_mtl_sys_items_seq_name
,P_PK1_NAME => g_mtl_sys_items_pk1_name
,P_PK1_NUM_VALUE => p_inventory_item_id
,P_PK2_NAME => g_mtl_sys_items_pk2_name
,P_PK2_NUM_VALUE => p_organization_id
,P_USER_ID => p_user_id
);
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_mtl_sys_items_pubi_name
,P_ACC_TABLE_NAME => g_mtl_sys_items_acc_table_name
,P_PK1_NAME => g_mtl_sys_items_pk1_name
,P_PK1_NUM_VALUE => p_inventory_item_id
,P_PK2_NAME => g_mtl_sys_items_pk2_name
,P_PK2_NUM_VALUE => p_organization_id
,P_USER_ID => p_user_id
);