[Home] [Help]
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 NOT EXISTS
( SELECT 1
FROM csm_mtl_system_items_acc acc
WHERE ((b_changed ='Y' AND USER_ID=-1) OR (b_changed ='N' AND 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, itemcat.inventory_item_id,
itemcat.organization_id, b_user_id
FROM mtl_item_categories itemcat
WHERE itemcat.category_id = b_category_id
AND itemcat.organization_id = b_organization_id
AND NOT EXISTS
(SELECT 1
FROM csm_mtl_system_items_acc acc
WHERE ((b_changed ='Y' AND USER_ID=-1) OR (b_changed ='N' AND user_id = b_user_id))
AND acc.inventory_item_id = itemcat.inventory_item_id
AND acc.organization_id = itemcat.organization_id
);
SELECT csm_mtl_system_items_acc_s.NEXTVAL, itemcat.inventory_item_id,
itemcat.organization_id, b_user_id
FROM mtl_item_categories itemcat
WHERE itemcat.category_set_id = b_category_set_id
AND itemcat.organization_id = b_organization_id
AND NOT EXISTS
(SELECT 1
FROM csm_mtl_system_items_acc acc
WHERE ((b_changed ='Y' AND USER_ID=-1) OR (b_changed ='N' AND user_id = b_user_id))
AND acc.inventory_item_id = itemcat.inventory_item_id
AND acc.organization_id = itemcat.organization_id
);
SELECT csm_mtl_system_items_acc_s.NEXTVAL, itemcat.inventory_item_id,
itemcat.organization_id, b_user_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 NOT EXISTS
(SELECT 1
FROM csm_mtl_system_items_acc acc
WHERE ((b_changed ='Y' AND USER_ID=-1) OR (b_changed ='N' AND user_id = b_user_id))
AND acc.inventory_item_id = itemcat.inventory_item_id
AND acc.organization_id = itemcat.organization_id
);
SELECT 'Y' FROM CSM_USER_INVENTORY_ORG
WHERE ORGANIZATION_ID = b_org_id
AND CATEGORY_SET_ID IS NULL
AND CATEGORY_ID IS NULL
AND TYPE = 'C'
AND USER_ID NOT IN(b_user_id);
SELECT ACCESS_ID,b_USER_ID
FROM csm_mtl_system_items_acc acc
WHERE acc.organization_id = b_organization_id
AND acc.USER_ID = -1
AND NOT EXISTS (SELECT 1 FROM
csm_mtl_system_items_acc acci
WHERE acci.organization_id = b_organization_id
AND acci.USER_ID = b_USER_ID);
SELECT csm_mtl_system_items_acc_s.NEXTVAL,inventory_item_id, organization_id
FROM MTL_SYSTEM_ITEMS_B msi
WHERE msi.organization_id = b_organization_id;
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_tab_access_id.DELETE;
l_tab_user_id.DELETE;
ELSE--IF Org Items are not present then insert it freshly.
--Do a direct into into the ACC table for user -1
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)
SELECT csm_mtl_system_items_acc_s.NEXTVAL,l_number, b.inventory_item_id, b.organization_id, 1,
fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id
FROM MTL_SYSTEM_ITEMS_B b
WHERE b.ORGANIZATION_ID = p_organization_id
AND b.enabled_flag = 'Y'
AND SYSDATE BETWEEN nvl(b.start_date_active, SYSDATE)
AND nvl(b.end_date_active, SYSDATE);
l_tab_access_id.DELETE;
l_tab_user_id.DELETE;
UPDATE CSM_USER_INVENTORY_ORG
SET TYPE = 'C'
WHERE CATEGORY_SET_ID IS NULL
AND CATEGORY_ID IS NULL
AND USER_ID = p_user_id;
CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS after common processing',
'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), l_number, 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), l_number, 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), l_number, 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), l_number, 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);
UPDATE CSM_USER_INVENTORY_ORG
SET TYPE = 'I'
WHERE USER_ID = p_user_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 acc.access_id, acc.user_id
FROM csm_mtl_system_items_acc acc, mtl_system_items_b msi,asg_user au
WHERE msi.inventory_item_id = acc.inventory_item_id
AND msi.organization_id = acc.organization_id
AND acc.USER_ID > 0
AND au.user_id=acc.user_id
AND nvl(au.MULTI_PLATFORM,'N')='N'
AND msi.last_update_date >= b_last_date;
SELECT acc.access_id, uorg.user_id
FROM csm_mtl_system_items_acc acc,
csm_user_inventory_org uorg
WHERE acc.organization_id = uorg.organization_id
AND uorg.category_set_id IS NULL
AND uorg.category_id IS NULL
AND acc.USER_ID = -1
AND EXISTS ( SELECT 1 FROM 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))
UNION
SELECT acc.access_id, uorg.user_id
FROM csm_mtl_system_items_acc acc,
csm_user_inventory_org uorg
WHERE acc.organization_id = uorg.organization_id
AND uorg.category_set_id IS NULL
AND uorg.category_id IS NULL
AND acc.USER_ID = -1
AND EXISTS ( SELECT 1 FROM mtl_system_items_tl tl
WHERE tl.inventory_item_id = acc.inventory_item_id
AND tl.organization_id = acc.organization_id
AND (tl.last_update_date >= b_last_date));
SELECT acc.access_id, uorg.user_id
FROM csm_mtl_system_items_acc acc,
csm_user_inventory_org uorg
WHERE acc.organization_id = uorg.organization_id
AND uorg.category_set_id IS NULL
AND uorg.category_id IS NULL
AND acc.USER_ID = -2;
SELECT acc.access_id, uorg.user_id
FROM csm_mtl_system_items_acc acc,
csm_user_inventory_org uorg
WHERE acc.organization_id = uorg.organization_id
AND uorg.category_set_id IS NULL
AND uorg.category_id IS NULL
AND acc.USER_ID = -1
AND EXISTS ( SELECT 1 FROM mtl_system_items_b msi
WHERE msi.inventory_item_id = acc.inventory_item_id
AND msi.organization_id = acc.organization_id
AND (msi.enabled_flag = 'N' OR msi.end_date_active < 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);
DELETE FROM csm_mtl_system_items_acc acc
WHERE USER_ID = -1
AND EXISTS ( SELECT 1 FROM mtl_system_items_b msi
WHERE msi.inventory_item_id = acc.inventory_item_id
AND msi.organization_id = acc.organization_id
AND msi.enabled_flag = 'N' OR msi.end_date_active < l_run_date);
l_tab_access_id.DELETE;
l_tab_user_id.DELETE;
'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_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)
SELECT csm_mtl_system_items_acc_s.NEXTVAL, -2, msi.inventory_item_id, msi.organization_id, 1,
fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id
FROM MTL_SYSTEM_ITEMS_B msi
WHERE msi.enabled_flag = 'Y'
AND SYSDATE BETWEEN nvl(msi.start_date_active, SYSDATE) AND nvl(msi.end_date_active, SYSDATE)
AND msi.ORGANIZATION_ID
IN(SELECT DISTINCT uorg.ORGANIZATION_ID
FROM CSM_USER_INVENTORY_ORG uorg
WHERE uorg.category_set_id IS NULL
AND uorg.category_id IS NULL)
AND NOT EXISTS
( SELECT 1
FROM csm_mtl_system_items_acc acc
WHERE acc.user_id = -1
AND acc.inventory_item_id = msi.inventory_item_id
AND acc.organization_id = msi.organization_id);
l_tab_access_id.DELETE;
l_tab_user_id.DELETE;
'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
UPDATE csm_mtl_system_items_acc
SET USER_ID = -1
WHERE USER_ID = -2;
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);
CURSOR c_Delete_items(b_org_id NUMBER, b_user_id NUMBER)
IS
SELECT b_user_id, ACCESS_ID
FROM csm_mtl_system_items_acc
WHERE USER_ID =-1
AND ORGANIZATION_ID = b_org_id;
SELECT count(*)
FROM csm_user_inventory_org
WHERE USER_ID <> b_user_id
AND ORGANIZATION_ID = b_org_id;
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;
OPEN c_Delete_items(p_organization_id, p_user_id);
FETCH c_Delete_items BULK COLLECT INTO l_tab_user_id, l_tab_access_id LIMIT 1000;
l_tab_access_id.DELETE;
l_tab_user_id.DELETE;
DELETE FROM csm_mtl_system_items_acc WHERE USER_ID = -1
AND organization_id =p_organization_id;
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');
SELECT csm_mtl_system_items_acc_s.NEXTVAL,inventory_item_id,org_id ,user_id
FROM ( select c.inventory_item_id,d.destination_organization_id org_id ,a.user_id
from csm_req_lines_acc a, csp_req_line_details b ,
oe_order_lines_all c,csp_requirement_headers d
,csp_requirement_lines e ,asg_user au
where a.requirement_line_id=b.requirement_line_id
and b.source_type= 'IO' and b.source_id=c.line_id
and au.user_id=a.user_id and au.multi_platform='Y'
and d.requirement_header_id=e.requirement_header_id
and b.requirement_line_id=e.requirement_line_id
UNION
select c.inventory_item_id,c.organization_id as org_id ,a.user_id
from csm_req_lines_acc a, csp_req_line_details b , mtl_reservations c,asg_user au
where a.requirement_line_id=b.requirement_line_id
and b.source_type= 'RES' and b.source_id=c.reservation_id
and au.user_id=a.user_id and au.multi_platform='Y') d
where not exists(select 1 from csm_mtl_system_items_acc acc
where acc.inventory_item_id=d.inventory_item_id
and acc.organization_id=d.org_id
and acc.user_id=d.user_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 of requirement lines' ,
'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.process_htm5_ordered_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), l_tab_user_id(i), l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id);
select a.access_id,au.user_id
from csm_mtl_system_items_acc a,asg_user au
where au.user_id=a.user_id
and NVL(au.MULTI_PLATFORM,'N')='Y'
and not exists (select 1
from csf_debrief_lines b
where transaction_type_id in (select transaction_type_id
from cs_transaction_types_b
where line_order_category_code='RETURN')
and b.last_update_date >= b_ret_acty_date
and a.inventory_item_id=b.inventory_item_id
and a.organization_id=nvl(b.receiving_inventory_org_id,b.issuing_inventory_org_id)) --return debrief-ed items
and not exists(select 1
from CSM_REQ_LINE_DETAILS_ACC req_acc
where req_acc.user_id=au.user_id
and req_acc.item_id=a.inventory_item_id
and req_acc.org_id =a.organization_id) --htm5 user ordered/reserved item
and not exists(select 1
from csm_item_instances_acc acc ,csi_item_instances ii
where acc.instance_id=ii.instance_id
and acc.user_id=au.user_id
and ii.inventory_item_id=a.inventory_item_id
and a.organization_id=NVL(ii.inv_organization_id,ii.LAST_VLD_ORGANIZATION_ID)) --sr instance and its children/parent
and not exists(select 1
from csm_incidents_all_acc inc_a,cs_incidents_all_b inc
where inc_a.incident_id=inc.incident_id
and inc_a.user_id=au.user_id
and NVL(inc.customer_product_id,0)=0
and inc.inventory_item_id=a.inventory_item_id
and inc.org_id=a.organization_id); --sr item
select csm_mtl_system_items_acc_s.NEXTVAL,item_id,msi.org_id,au.user_id
from (select item_id,org_id from
(select inventory_item_id as item_id,
nvl(receiving_inventory_org_id,issuing_inventory_org_id) as org_id,
sum(quantity) tot from csf_debrief_lines b
where transaction_type_id in (select transaction_type_id
from cs_transaction_types_b
where line_order_category_code='RETURN')
and inventory_item_id is not null
and nvl(receiving_inventory_org_id,issuing_inventory_org_id) is not null
and b.lasT_update_date > b_ret_acty_date
group by inventory_item_id ,nvl(receiving_inventory_org_id,issuing_inventory_org_id)
order by tot desc)
where rownum <= b_max_mtl_items) msi,
asg_user au
where not exists(select 1 from csm_mtl_system_items_acc
WHERE msi.item_id = inventory_item_id
AND msi.org_id = organization_id
AND user_id =au.user_id);
SELECT 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 EXISTS(SELECT 1 FROM ASG_USER au WHERE AU.USER_ID=acc.USER_ID and NVL(MULTI_PLATFORM,'N')='Y')
AND acc.CREATION_DATE <=p_run_date
AND msi.last_update_date > p_run_date;
SELECT RESPONSIBILITY_ID INTO l_respId FROM FND_RESPONSIBILITY WHERE RESPONSIBILITY_KEY='OMFS_PALM';
l_tab_access_id.DELETE;
l_tab_user_id.DELETE;
CSM_UTIL_PKG.LOG('Bulk deleted ' || l_tab_access_id.count || ' records from csm_mtl_system_items_acc','CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.process_htm5_user_items',FND_LOG.LEVEL_EVENT);
DELETE FROM csm_mtl_system_items_acc WHERE access_id = l_tab_access_id(i);
l_tab_access_id.DELETE;
l_tab_user_id.DELETE;
CSM_UTIL_PKG.LOG('Bulk updated ' || l_tab_access_id.count || ' records from csm_mtl_system_items_acc' ,'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.process_htm5_user_items',FND_LOG.LEVEL_EVENT);
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 ' ,
'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.process_htm5_user_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), l_tab_user_id(i), l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id);
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;
process_htm5_user_items(l_prog_update_date);
select count(*) into l_num_non_multi_users
from asg_user
where enabled = 'Y'
and nvl(multi_platform,'N') = 'N';
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=>l_changed); -- 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
);