eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.CSM_MTL_SYSTEM_ITEMS_EVENT_PKG dependencies on CSM_MTL_SYSTEM_ITEMS_ACC

Line 17: g_mtl_sys_items_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_MTL_SYSTEM_ITEMS_ACC';

13:
14: g_pub_item varchar2(30) := 'CSM_MTL_SYSTEM_ITEMS';
15:
16: /*** Globals ***/
17: g_mtl_sys_items_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_MTL_SYSTEM_ITEMS_ACC';
18: g_mtl_sys_items_table_name CONSTANT VARCHAR2(30) := 'CS_MTL_SYSTEM_ITEMS';
19: g_mtl_sys_items_seq_name CONSTANT VARCHAR2(30) := 'CSM_MTL_SYSTEM_ITEMS_ACC_S' ;
20: g_mtl_sys_items_pk1_name CONSTANT VARCHAR2(30) := 'INVENTORY_ITEM_ID';
21: g_mtl_sys_items_pk2_name CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';

Line 19: g_mtl_sys_items_seq_name CONSTANT VARCHAR2(30) := 'CSM_MTL_SYSTEM_ITEMS_ACC_S' ;

15:
16: /*** Globals ***/
17: g_mtl_sys_items_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_MTL_SYSTEM_ITEMS_ACC';
18: g_mtl_sys_items_table_name CONSTANT VARCHAR2(30) := 'CS_MTL_SYSTEM_ITEMS';
19: g_mtl_sys_items_seq_name CONSTANT VARCHAR2(30) := 'CSM_MTL_SYSTEM_ITEMS_ACC_S' ;
20: g_mtl_sys_items_pk1_name CONSTANT VARCHAR2(30) := 'INVENTORY_ITEM_ID';
21: g_mtl_sys_items_pk2_name CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
22: g_mtl_sys_items_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
23: CSM_ACC_PKG.t_publication_item_list('CSM_MTL_SYSTEM_ITEMS');

Line 55: SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id

51: -- Both category and cat set are null
52: CURSOR c_items (b_user_id NUMBER, b_organization_id NUMBER,
53: b_changed VARCHAR2, b_last_run_date DATE)
54: IS
55: SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
56: FROM MTL_SYSTEM_ITEMS_B msi
57: WHERE msi.organization_id = b_organization_id
58: AND NOT EXISTS
59: ( SELECT 1

Line 60: FROM csm_mtl_system_items_acc acc

56: FROM MTL_SYSTEM_ITEMS_B msi
57: WHERE msi.organization_id = b_organization_id
58: AND NOT EXISTS
59: ( SELECT 1
60: FROM csm_mtl_system_items_acc acc
61: WHERE ((b_changed ='Y' AND USER_ID=-1) OR (b_changed ='N' AND user_id = b_user_id))
62: AND acc.inventory_item_id = msi.inventory_item_id
63: AND acc.organization_id = msi.organization_id);
64:

Line 69: SELECT csm_mtl_system_items_acc_s.NEXTVAL, itemcat.inventory_item_id,

65: -- Category is not null and Cat set is null
66: CURSOR c_items_Cat (b_user_id NUMBER, b_organization_id NUMBER,
67: b_changed VARCHAR2, b_last_run_date DATE, b_category_id NUMBER)
68: IS
69: SELECT csm_mtl_system_items_acc_s.NEXTVAL, itemcat.inventory_item_id,
70: itemcat.organization_id, b_user_id
71: FROM mtl_item_categories itemcat
72: WHERE itemcat.category_id = b_category_id
73: AND itemcat.organization_id = b_organization_id

Line 76: FROM csm_mtl_system_items_acc acc

72: WHERE itemcat.category_id = b_category_id
73: AND itemcat.organization_id = b_organization_id
74: AND NOT EXISTS
75: (SELECT 1
76: FROM csm_mtl_system_items_acc acc
77: WHERE ((b_changed ='Y' AND USER_ID=-1) OR (b_changed ='N' AND user_id = b_user_id))
78: AND acc.inventory_item_id = itemcat.inventory_item_id
79: AND acc.organization_id = itemcat.organization_id
80: );

Line 86: SELECT csm_mtl_system_items_acc_s.NEXTVAL, itemcat.inventory_item_id,

82: -- Category is null and Cat Set is not null
83: CURSOR c_items_Cat_Set (b_user_id NUMBER, b_organization_id NUMBER,
84: b_changed VARCHAR2, b_last_run_date DATE, b_category_set_id NUMBER)
85: IS
86: SELECT csm_mtl_system_items_acc_s.NEXTVAL, itemcat.inventory_item_id,
87: itemcat.organization_id, b_user_id
88: FROM mtl_item_categories itemcat
89: WHERE itemcat.category_set_id = b_category_set_id
90: AND itemcat.organization_id = b_organization_id

Line 93: FROM csm_mtl_system_items_acc acc

89: WHERE itemcat.category_set_id = b_category_set_id
90: AND itemcat.organization_id = b_organization_id
91: AND NOT EXISTS
92: (SELECT 1
93: FROM csm_mtl_system_items_acc acc
94: WHERE ((b_changed ='Y' AND USER_ID=-1) OR (b_changed ='N' AND user_id = b_user_id))
95: AND acc.inventory_item_id = itemcat.inventory_item_id
96: AND acc.organization_id = itemcat.organization_id
97: );

Line 103: SELECT csm_mtl_system_items_acc_s.NEXTVAL, itemcat.inventory_item_id,

99: -- Both Category and Category set are not null
100: CURSOR c_items_Cat_Set_Cat (b_user_id NUMBER, b_organization_id NUMBER,
101: b_changed VARCHAR2, b_last_run_date DATE, b_category_id NUMBER, b_category_set_id NUMBER)
102: IS
103: SELECT csm_mtl_system_items_acc_s.NEXTVAL, itemcat.inventory_item_id,
104: itemcat.organization_id, b_user_id
105: FROM mtl_item_categories itemcat
106: WHERE itemcat.category_set_id = b_category_set_id
107: AND itemcat.category_id = b_category_id

Line 111: FROM csm_mtl_system_items_acc acc

107: AND itemcat.category_id = b_category_id
108: AND itemcat.organization_id = b_organization_id
109: AND NOT EXISTS
110: (SELECT 1
111: FROM csm_mtl_system_items_acc acc
112: WHERE ((b_changed ='Y' AND USER_ID=-1) OR (b_changed ='N' AND user_id = b_user_id))
113: AND acc.inventory_item_id = itemcat.inventory_item_id
114: AND acc.organization_id = itemcat.organization_id
115: );

Line 129: FROM csm_mtl_system_items_acc acc

125:
126: CURSOR c_get_all_items_acc (b_organization_id NUMBER, b_USER_ID NUMBER )
127: IS
128: SELECT ACCESS_ID,b_USER_ID
129: FROM csm_mtl_system_items_acc acc
130: WHERE acc.organization_id = b_organization_id
131: AND acc.USER_ID = -1
132: AND NOT EXISTS (SELECT 1 FROM
133: csm_mtl_system_items_acc acci

Line 133: csm_mtl_system_items_acc acci

129: FROM csm_mtl_system_items_acc acc
130: WHERE acc.organization_id = b_organization_id
131: AND acc.USER_ID = -1
132: AND NOT EXISTS (SELECT 1 FROM
133: csm_mtl_system_items_acc acci
134: WHERE acci.organization_id = b_organization_id
135: AND acci.USER_ID = b_USER_ID);
136:
137: ---items to download for insert

Line 140: SELECT csm_mtl_system_items_acc_s.NEXTVAL,inventory_item_id, organization_id

136:
137: ---items to download for insert
138: CURSOR c_all_items_for_org (b_organization_id NUMBER)
139: IS
140: SELECT csm_mtl_system_items_acc_s.NEXTVAL,inventory_item_id, organization_id
141: FROM MTL_SYSTEM_ITEMS_B msi
142: WHERE msi.organization_id = b_organization_id;
143:
144:

Line 145: --csm_mtl_system_items_acc_s.NEXTVAL,

141: FROM MTL_SYSTEM_ITEMS_B msi
142: WHERE msi.organization_id = b_organization_id;
143:
144:
145: --csm_mtl_system_items_acc_s.NEXTVAL,
146:
147: l_check_org VARCHAR2(10) := 'N';
148: l_profile_value VARCHAR2(10);
149: l_number NUMBER := -1;

Line 189: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,

185: CLOSE c_get_all_items_acc;
186: ELSE--IF Org Items are not present then insert it freshly.
187:
188: --Do a direct into into the ACC table for user -1
189: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
190: created_by, creation_date, last_updated_by, last_update_date, last_update_login)
191: SELECT csm_mtl_system_items_acc_s.NEXTVAL,l_number, b.inventory_item_id, b.organization_id, 1,
192: fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id
193: FROM MTL_SYSTEM_ITEMS_B b

Line 191: SELECT csm_mtl_system_items_acc_s.NEXTVAL,l_number, b.inventory_item_id, b.organization_id, 1,

187:
188: --Do a direct into into the ACC table for user -1
189: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
190: created_by, creation_date, last_updated_by, last_update_date, last_update_login)
191: SELECT csm_mtl_system_items_acc_s.NEXTVAL,l_number, b.inventory_item_id, b.organization_id, 1,
192: fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id
193: FROM MTL_SYSTEM_ITEMS_B b
194: WHERE b.ORGANIZATION_ID = p_organization_id
195: AND b.enabled_flag = 'Y'

Line 239: l_stmt := 'UPDATE csm_mtl_system_items_acc';

235: -- acc due to current SR's and increment counter
236: IF p_changed = 'Y' THEN
237:
238: -- changed to dynamic sql to support either category or category set
239: l_stmt := 'UPDATE csm_mtl_system_items_acc';
240: l_stmt := l_stmt || ' SET counter = counter + 1';
241: l_stmt := l_stmt || ' , last_update_date = SYSDATE';
242: l_stmt := l_stmt || ' , last_updated_by = 1';
243: l_stmt := l_stmt || ' WHERE user_id = :1';

Line 277: l_stmt := 'UPDATE csm_mtl_system_items_acc acc';

273:
274: ELSE -- if p_changed = 'N', delete items that are no longer assigned to the category/category set
275: -- changed to dynamic sql to support either category or category set
276: l_stmt := NULL;
277: l_stmt := 'UPDATE csm_mtl_system_items_acc acc';
278: l_stmt := l_stmt || ' SET counter = counter - 1';
279: l_stmt := l_stmt || ' , last_update_date = SYSDATE';
280: l_stmt := l_stmt || ' , last_updated_by = 1';
281: l_stmt := l_stmt || ' WHERE user_id = :1';

Line 320: FROM csm_mtl_system_items_acc acc

316: l_tab_user_id.DELETE;
317:
318: SELECT access_id, user_id
319: BULK COLLECT INTO l_tab_access_id, l_tab_user_id
320: FROM csm_mtl_system_items_acc acc
321: WHERE acc.counter = 0;
322:
323: IF l_tab_access_id.COUNT > 0 THEN
324: -- do bulk makedirty

Line 334: DELETE FROM csm_mtl_system_items_acc WHERE access_id = l_tab_access_id(i);

330: , P_TIMESTAMP => l_run_date
331: );
332:
333: FORALL i IN 1..l_tab_access_id.COUNT
334: DELETE FROM csm_mtl_system_items_acc WHERE access_id = l_tab_access_id(i);
335: END IF;
336: ELSE
337: -- do not decrement as category/category set profile is not changed
338: l_stmt := NULL;

Line 364: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,

360: EXIT WHEN l_tab_access_id.COUNT = 0;
361:
362: IF l_tab_access_id.COUNT > 0 THEN
363: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
364: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
365: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
366:
367: FORALL i IN 1..l_tab_access_id.COUNT
368: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,

Line 368: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,

364: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
365: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
366:
367: FORALL i IN 1..l_tab_access_id.COUNT
368: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
369: created_by, creation_date, last_updated_by, last_update_date, last_update_login)
370: VALUES (l_tab_access_id(i), l_number, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
371: fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
372:

Line 405: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,

401: EXIT WHEN l_tab_access_id.COUNT = 0;
402:
403: IF l_tab_access_id.COUNT > 0 THEN
404: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
405: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
406: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
407:
408: FORALL i IN 1..l_tab_access_id.COUNT
409: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,

Line 409: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,

405: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
406: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
407:
408: FORALL i IN 1..l_tab_access_id.COUNT
409: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
410: created_by, creation_date, last_updated_by, last_update_date, last_update_login)
411: VALUES (l_tab_access_id(i), l_number, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
412: fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
413:

Line 446: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,

442: EXIT WHEN l_tab_access_id.COUNT = 0;
443:
444: IF l_tab_access_id.COUNT > 0 THEN
445: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
446: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
447: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
448:
449: FORALL i IN 1..l_tab_access_id.COUNT
450: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,

Line 450: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,

446: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
447: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
448:
449: FORALL i IN 1..l_tab_access_id.COUNT
450: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
451: created_by, creation_date, last_updated_by, last_update_date, last_update_login)
452: VALUES (l_tab_access_id(i), l_number, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
453: fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
454:

Line 487: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,

483: EXIT WHEN l_tab_access_id.COUNT = 0;
484:
485: IF l_tab_access_id.COUNT > 0 THEN
486: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
487: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
488: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
489:
490: FORALL i IN 1..l_tab_access_id.COUNT
491: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,

Line 491: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,

487: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
488: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
489:
490: FORALL i IN 1..l_tab_access_id.COUNT
491: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
492: created_by, creation_date, last_updated_by, last_update_date, last_update_login)
493: VALUES (l_tab_access_id(i), l_number, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
494: fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
495:

Line 547: FROM csm_mtl_system_items_acc acc, mtl_system_items_b msi,asg_user au

543:
544: CURSOR c_changed( b_last_date DATE)
545: IS
546: SELECT acc.access_id, acc.user_id
547: FROM csm_mtl_system_items_acc acc, mtl_system_items_b msi,asg_user au
548: WHERE msi.inventory_item_id = acc.inventory_item_id
549: AND msi.organization_id = acc.organization_id
550: AND acc.USER_ID > 0
551: AND au.user_id=acc.user_id

Line 559: FROM csm_mtl_system_items_acc acc,

555: ---check if user org can be separated.
556: CURSOR c_changed_org_based( b_last_date DATE)
557: IS
558: SELECT acc.access_id, uorg.user_id
559: FROM csm_mtl_system_items_acc acc,
560: csm_user_inventory_org uorg
561: WHERE acc.organization_id = uorg.organization_id
562: AND uorg.category_set_id IS NULL
563: AND uorg.category_id IS NULL

Line 571: FROM csm_mtl_system_items_acc acc,

567: AND msi.organization_id = acc.organization_id
568: AND (msi.last_update_date >= b_last_date))
569: UNION
570: SELECT acc.access_id, uorg.user_id
571: FROM csm_mtl_system_items_acc acc,
572: csm_user_inventory_org uorg
573: WHERE acc.organization_id = uorg.organization_id
574: AND uorg.category_set_id IS NULL
575: AND uorg.category_id IS NULL

Line 587: FROM csm_mtl_system_items_acc acc,

583: ---New items inserted for orgs in user org table
584: CURSOR c_New_items_for_org
585: IS
586: SELECT acc.access_id, uorg.user_id
587: FROM csm_mtl_system_items_acc acc,
588: csm_user_inventory_org uorg
589: WHERE acc.organization_id = uorg.organization_id
590: AND uorg.category_set_id IS NULL
591: AND uorg.category_id IS NULL

Line 599: FROM csm_mtl_system_items_acc acc,

595:
596: CURSOR c_del_org_based( b_last_date DATE)
597: IS
598: SELECT acc.access_id, uorg.user_id
599: FROM csm_mtl_system_items_acc acc,
600: csm_user_inventory_org uorg
601: WHERE acc.organization_id = uorg.organization_id
602: AND uorg.category_set_id IS NULL
603: AND uorg.category_id IS NULL

Line 665: DELETE FROM csm_mtl_system_items_acc acc

661:
662: IF l_transaction_flag ='Y' THEN
663: l_transaction_flag := 'N';
664: --DELETE the items from the acc table
665: DELETE FROM csm_mtl_system_items_acc acc
666: WHERE USER_ID = -1
667: AND EXISTS ( SELECT 1 FROM mtl_system_items_b msi
668: WHERE msi.inventory_item_id = acc.inventory_item_id
669: AND msi.organization_id = acc.organization_id

Line 702: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,

698: CLOSE c_changed_org_based;
699: --Insert newly created Items for allthe user in csm_user_inventory_org
700:
701: --Do a direct into into the ACC table for user -2
702: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
703: created_by, creation_date, last_updated_by, last_update_date, last_update_login)
704: SELECT csm_mtl_system_items_acc_s.NEXTVAL, -2, msi.inventory_item_id, msi.organization_id, 1,
705: fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id
706: FROM MTL_SYSTEM_ITEMS_B msi

Line 704: SELECT csm_mtl_system_items_acc_s.NEXTVAL, -2, msi.inventory_item_id, msi.organization_id, 1,

700:
701: --Do a direct into into the ACC table for user -2
702: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
703: created_by, creation_date, last_updated_by, last_update_date, last_update_login)
704: SELECT csm_mtl_system_items_acc_s.NEXTVAL, -2, msi.inventory_item_id, msi.organization_id, 1,
705: fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id
706: FROM MTL_SYSTEM_ITEMS_B msi
707: WHERE msi.enabled_flag = 'Y'
708: AND SYSDATE BETWEEN nvl(msi.start_date_active, SYSDATE) AND nvl(msi.end_date_active, SYSDATE)

Line 716: FROM csm_mtl_system_items_acc acc

712: WHERE uorg.category_set_id IS NULL
713: AND uorg.category_id IS NULL)
714: AND NOT EXISTS
715: ( SELECT 1
716: FROM csm_mtl_system_items_acc acc
717: WHERE acc.user_id = -1
718: AND acc.inventory_item_id = msi.inventory_item_id
719: AND acc.organization_id = msi.organization_id);
720:

Line 750: UPDATE csm_mtl_system_items_acc

746: CLOSE c_New_items_for_org;
747:
748: IF l_transaction_flag = 'Y' THEN
749: --Update the user_id to -1;
750: UPDATE csm_mtl_system_items_acc
751: SET USER_ID = -1
752: WHERE USER_ID = -2;
753: COMMIT;
754: END IF;

Line 815: FROM csm_mtl_system_items_acc

811:
812: CURSOR c_Delete_items(b_org_id NUMBER, b_user_id NUMBER)
813: IS
814: SELECT b_user_id, ACCESS_ID
815: FROM csm_mtl_system_items_acc
816: WHERE USER_ID =-1
817: AND ORGANIZATION_ID = b_org_id;
818:
819: CURSOR c_get_org_count(b_org_id NUMBER, b_user_id NUMBER)

Line 863: DELETE FROM csm_mtl_system_items_acc WHERE USER_ID = -1

859: OPEN c_get_org_count(p_organization_id, p_user_id);
860: FETCH c_get_org_count INTO l_org_count;
861: CLOSE c_get_org_count;
862: IF l_org_count = 0 THEN
863: DELETE FROM csm_mtl_system_items_acc WHERE USER_ID = -1
864: AND organization_id =p_organization_id;
865: END IF;
866: ELSE
867:

Line 868: l_stmt := 'UPDATE csm_mtl_system_items_acc acc';

864: AND organization_id =p_organization_id;
865: END IF;
866: ELSE
867:
868: l_stmt := 'UPDATE csm_mtl_system_items_acc acc';
869: l_stmt := l_stmt || ' SET COUNTER = COUNTER - 1';
870: l_stmt := l_stmt || ' , LAST_UPDATE_DATE = SYSDATE';
871: l_stmt := l_stmt || ' , last_updated_by = nvl(fnd_global.user_id, 1)';
872: l_stmt := l_stmt || ' WHERE USER_ID = :1';

Line 909: FROM csm_mtl_system_items_acc acc

905: l_tab_user_id.DELETE;
906:
907: SELECT access_id, user_id
908: BULK COLLECT INTO l_tab_access_id, l_tab_user_id
909: FROM csm_mtl_system_items_acc acc
910: WHERE acc.counter = 0;
911:
912: IF l_tab_access_id.COUNT > 0 THEN
913: -- do bulk makedirty

Line 923: DELETE FROM csm_mtl_system_items_acc WHERE access_id = l_tab_access_id(i);

919: , P_TIMESTAMP => l_run_date
920: );
921:
922: FORALL i IN 1..l_tab_access_id.COUNT
923: DELETE FROM csm_mtl_system_items_acc WHERE access_id = l_tab_access_id(i);
924: END IF;
925: END IF;
926:
927: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS ',

Line 1090: SELECT csm_mtl_system_items_acc_s.NEXTVAL,inventory_item_id,org_id ,user_id

1086: IS
1087:
1088: CURSOR c_htm5_mtl_items
1089: IS
1090: SELECT csm_mtl_system_items_acc_s.NEXTVAL,inventory_item_id,org_id ,user_id
1091: FROM ( select c.inventory_item_id,d.destination_organization_id org_id ,a.user_id
1092: from csm_req_lines_acc a, csp_req_line_details b ,
1093: oe_order_lines_all c,csp_requirement_headers d
1094: ,csp_requirement_lines e ,asg_user au

Line 1106: where not exists(select 1 from csm_mtl_system_items_acc acc

1102: from csm_req_lines_acc a, csp_req_line_details b , mtl_reservations c,asg_user au
1103: where a.requirement_line_id=b.requirement_line_id
1104: and b.source_type= 'RES' and b.source_id=c.reservation_id
1105: and au.user_id=a.user_id and au.multi_platform='Y') d
1106: where not exists(select 1 from csm_mtl_system_items_acc acc
1107: where acc.inventory_item_id=d.inventory_item_id
1108: and acc.organization_id=d.org_id
1109: and acc.user_id=d.user_id);
1110:

Line 1133: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count || ' records into csm_mtl_system_items_acc of requirement lines' ,

1129: FETCH c_htm5_mtl_items BULK COLLECT INTO l_tab_access_id,l_inventory_item_id_tbl,
1130: l_organization_id_tbl, l_tab_user_id LIMIT 1000;
1131: EXIT WHEN l_tab_access_id.COUNT = 0;
1132:
1133: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count || ' records into csm_mtl_system_items_acc of requirement lines' ,
1134: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.process_htm5_ordered_items',FND_LOG.LEVEL_EVENT);
1135:
1136: FORALL i IN 1..l_tab_access_id.COUNT
1137: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,

Line 1137: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,

1133: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count || ' records into csm_mtl_system_items_acc of requirement lines' ,
1134: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.process_htm5_ordered_items',FND_LOG.LEVEL_EVENT);
1135:
1136: FORALL i IN 1..l_tab_access_id.COUNT
1137: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
1138: created_by, creation_date, last_updated_by, last_update_date, last_update_login)
1139: VALUES (l_tab_access_id(i), l_tab_user_id(i), l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
1140: fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id);
1141:

Line 1161: from csm_mtl_system_items_acc a,asg_user au

1157: PROCEDURE process_htm5_user_items(p_run_date DATE)
1158: IS
1159: CURSOR c_get_del_items(b_ret_acty_date DATE) IS
1160: select a.access_id,au.user_id
1161: from csm_mtl_system_items_acc a,asg_user au
1162: where au.user_id=a.user_id
1163: and NVL(au.MULTI_PLATFORM,'N')='Y'
1164: and not exists (select 1
1165: from csf_debrief_lines b

Line 1193: select csm_mtl_system_items_acc_s.NEXTVAL,item_id,msi.org_id,au.user_id

1189: and inc.org_id=a.organization_id); --sr item
1190:
1191: CURSOR c_get_ins_items(b_ret_acty_date DATE,
1192: b_max_mtl_items NUMBER) IS
1193: select csm_mtl_system_items_acc_s.NEXTVAL,item_id,msi.org_id,au.user_id
1194: from (select item_id,org_id from
1195: (select inventory_item_id as item_id,
1196: nvl(receiving_inventory_org_id,issuing_inventory_org_id) as org_id,
1197: sum(quantity) tot from csf_debrief_lines b

Line 1208: where not exists(select 1 from csm_mtl_system_items_acc

1204: group by inventory_item_id ,nvl(receiving_inventory_org_id,issuing_inventory_org_id)
1205: order by tot desc)
1206: where rownum <= b_max_mtl_items) msi,
1207: asg_user au
1208: where not exists(select 1 from csm_mtl_system_items_acc
1209: WHERE msi.item_id = inventory_item_id
1210: AND msi.org_id = organization_id
1211: AND user_id =au.user_id);
1212:

Line 1215: FROM csm_mtl_system_items_acc acc, mtl_system_items_b msi

1211: AND user_id =au.user_id);
1212:
1213: CURSOR c_get_upd_items IS
1214: SELECT acc.access_id, acc.user_id
1215: FROM csm_mtl_system_items_acc acc, mtl_system_items_b msi
1216: WHERE msi.inventory_item_id = acc.inventory_item_id
1217: AND msi.organization_id = acc.organization_id
1218: AND EXISTS(SELECT 1 FROM ASG_USER au WHERE AU.USER_ID=acc.USER_ID and NVL(MULTI_PLATFORM,'N')='Y')
1219: AND acc.CREATION_DATE <=p_run_date

Line 1250: 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);

1246: l_tab_user_id.DELETE;
1247: FETCH c_get_del_items BULK COLLECT INTO l_tab_access_id,l_tab_user_id LIMIT 1000;
1248: EXIT WHEN l_tab_access_id.COUNT =0;
1249:
1250: 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);
1251:
1252: l_markdirty := asg_download.mark_dirty(
1253: P_PUB_ITEM => g_pub_item
1254: , p_accessList => l_tab_access_id

Line 1261: DELETE FROM csm_mtl_system_items_acc WHERE access_id = l_tab_access_id(i);

1257: , P_TIMESTAMP => sysdate
1258: );
1259:
1260: FORALL i IN 1..l_tab_access_id.COUNT
1261: DELETE FROM csm_mtl_system_items_acc WHERE access_id = l_tab_access_id(i);
1262: COMMIT;
1263: END LOOP;
1264: CLOSE c_get_del_items;
1265: END IF;

Line 1275: 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);

1271: l_tab_access_id.DELETE;
1272: l_tab_user_id.DELETE;
1273: FETCH c_get_upd_items BULK COLLECT INTO l_tab_access_id,l_tab_user_id LIMIT 1000;
1274: EXIT WHEN l_tab_access_id.COUNT =0;
1275: 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);
1276: l_markdirty := asg_download.mark_dirty(
1277: P_PUB_ITEM => g_pub_item
1278: , p_accessList => l_tab_access_id
1279: , p_userid_list => l_tab_user_id

Line 1302: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count || ' records into csm_mtl_system_items_acc ' ,

1298: FETCH c_get_ins_items BULK COLLECT INTO l_tab_access_id, l_inventory_item_id_tbl,
1299: l_organization_id_tbl, l_tab_user_id LIMIT 2000;
1300: EXIT WHEN l_tab_access_id.COUNT = 0;
1301:
1302: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count || ' records into csm_mtl_system_items_acc ' ,
1303: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.process_htm5_user_items',FND_LOG.LEVEL_EVENT);
1304:
1305: FORALL i IN 1..l_tab_access_id.COUNT
1306: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,

Line 1306: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,

1302: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count || ' records into csm_mtl_system_items_acc ' ,
1303: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.process_htm5_user_items',FND_LOG.LEVEL_EVENT);
1304:
1305: FORALL i IN 1..l_tab_access_id.COUNT
1306: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
1307: created_by, creation_date, last_updated_by, last_update_date, last_update_login)
1308: VALUES (l_tab_access_id(i), l_tab_user_id(i), l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
1309: fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id);
1310: