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 MTL_SYSTEM_ITEMS

Line 1: PACKAGE BODY CSM_MTL_SYSTEM_ITEMS_EVENT_PKG AS

1: PACKAGE BODY CSM_MTL_SYSTEM_ITEMS_EVENT_PKG AS
2: /* $Header: csmesib.pls 120.3.12020000.2 2013/04/09 10:59:05 saradhak ship $ */
3: --
4: -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5: -- directory of SQL Navigator

Line 14: g_pub_item varchar2(30) := 'CSM_MTL_SYSTEM_ITEMS';

10: -- Person Date Comments
11: -- --------- ------ ------------------------------------------
12: -- Enter procedure, function bodies as shown below
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';

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 18: g_mtl_sys_items_table_name CONSTANT VARCHAR2(30) := 'CS_MTL_SYSTEM_ITEMS';

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';
22: g_mtl_sys_items_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=

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 23: CSM_ACC_PKG.t_publication_item_list('CSM_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');
24: g_download_inv_flag VARCHAR2(10) := NULL;
25:
26: PROCEDURE insert_mtl_system_items( p_user_id IN NUMBER,
27: p_organization_id IN NUMBER,

Line 26: PROCEDURE insert_mtl_system_items( p_user_id IN NUMBER,

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');
24: g_download_inv_flag VARCHAR2(10) := NULL;
25:
26: PROCEDURE insert_mtl_system_items( p_user_id IN NUMBER,
27: p_organization_id IN NUMBER,
28: p_category_set_id IN NUMBER,
29: p_category_id IN NUMBER,
30: p_last_run_date IN DATE,

Line 43: TYPE inventory_item_id_tbl_typ IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;

39: l_stmt1 VARCHAR2(4000);
40: l_markdirty BOOLEAN;
41:
42:
43: TYPE inventory_item_id_tbl_typ IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
44: TYPE organization_id_tbl_typ IS TABLE OF mtl_system_items_b.organization_id%TYPE INDEX BY BINARY_INTEGER;
45:
46: l_inventory_item_id_tbl inventory_item_id_tbl_typ;
47: l_organization_id_tbl organization_id_tbl_typ;

Line 44: TYPE organization_id_tbl_typ IS TABLE OF mtl_system_items_b.organization_id%TYPE INDEX BY BINARY_INTEGER;

40: l_markdirty BOOLEAN;
41:
42:
43: TYPE inventory_item_id_tbl_typ IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
44: TYPE organization_id_tbl_typ IS TABLE OF mtl_system_items_b.organization_id%TYPE INDEX BY BINARY_INTEGER;
45:
46: l_inventory_item_id_tbl inventory_item_id_tbl_typ;
47: l_organization_id_tbl organization_id_tbl_typ;
48: l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;

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 56: FROM MTL_SYSTEM_ITEMS_B msi

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
60: FROM csm_mtl_system_items_acc acc

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 141: FROM MTL_SYSTEM_ITEMS_B msi

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:
145: --csm_mtl_system_items_acc_s.NEXTVAL,

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 151: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS ',

147: l_check_org VARCHAR2(10) := 'N';
148: l_profile_value VARCHAR2(10);
149: l_number NUMBER := -1;
150: BEGIN
151: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS ',
152: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
153:
154: l_run_date := SYSDATE;
155: --GEt the value for CSM: Download Organization Level Inventory

Line 152: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);

148: l_profile_value VARCHAR2(10);
149: l_number NUMBER := -1;
150: BEGIN
151: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS ',
152: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
153:
154: l_run_date := SYSDATE;
155: --GEt the value for CSM: Download Organization Level Inventory
156: l_profile_value := NVL(g_download_inv_flag,'N');

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 193: FROM MTL_SYSTEM_ITEMS_B b

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'
196: AND SYSDATE BETWEEN nvl(b.start_date_active, SYSDATE)
197: AND nvl(b.end_date_active, SYSDATE);

Line 224: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS after common processing',

220: AND CATEGORY_ID IS NULL
221: AND USER_ID = p_user_id;
222: COMMIT;
223: END IF;--IF org has changed for the user.
224: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS after common processing',
225: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
226:
227: RETURN;
228: END IF;--Profile to process only org

Line 225: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);

221: AND USER_ID = p_user_id;
222: COMMIT;
223: END IF;--IF org has changed for the user.
224: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS after common processing',
225: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
226:
227: RETURN;
228: END IF;--Profile to process only org
229:

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 365: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);

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,
369: created_by, creation_date, last_updated_by, last_update_date, last_update_login)

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 406: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);

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,
410: created_by, creation_date, last_updated_by, last_update_date, last_update_login)

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 447: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);

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,
451: created_by, creation_date, last_updated_by, last_update_date, last_update_login)

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 488: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);

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,
492: created_by, creation_date, last_updated_by, last_update_date, last_update_login)

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 517: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS ',

513: SET TYPE = 'I'
514: WHERE USER_ID = p_user_id;
515: COMMIT;
516:
517: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS ',
518: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
519: EXCEPTION
520: WHEN others THEN
521: l_sqlerrno := TO_CHAR(SQLCODE);

Line 518: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);

514: WHERE USER_ID = p_user_id;
515: COMMIT;
516:
517: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS ',
518: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
519: EXCEPTION
520: WHEN others THEN
521: l_sqlerrno := TO_CHAR(SQLCODE);
522: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);

Line 523: l_error_msg := ' Exception in INSERT_MTL_SYSTEM_ITEMS for user_id :'

519: EXCEPTION
520: WHEN others THEN
521: l_sqlerrno := TO_CHAR(SQLCODE);
522: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
523: l_error_msg := ' Exception in INSERT_MTL_SYSTEM_ITEMS for user_id :'
524: || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
525: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
526: RAISE;
527: END INSERT_MTL_SYSTEM_ITEMS;

Line 525: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);

521: l_sqlerrno := TO_CHAR(SQLCODE);
522: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
523: l_error_msg := ' Exception in INSERT_MTL_SYSTEM_ITEMS for user_id :'
524: || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
525: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
526: RAISE;
527: END INSERT_MTL_SYSTEM_ITEMS;
528:
529: PROCEDURE update_mtl_system_items(p_last_run_date IN DATE)

Line 527: END INSERT_MTL_SYSTEM_ITEMS;

523: l_error_msg := ' Exception in INSERT_MTL_SYSTEM_ITEMS for user_id :'
524: || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
525: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
526: RAISE;
527: END INSERT_MTL_SYSTEM_ITEMS;
528:
529: PROCEDURE update_mtl_system_items(p_last_run_date IN DATE)
530: IS
531: l_sqlerrno VARCHAR2(20);

Line 529: PROCEDURE update_mtl_system_items(p_last_run_date IN DATE)

525: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
526: RAISE;
527: END INSERT_MTL_SYSTEM_ITEMS;
528:
529: PROCEDURE update_mtl_system_items(p_last_run_date IN DATE)
530: IS
531: l_sqlerrno VARCHAR2(20);
532: l_sqlerrmsg VARCHAR2(4000);
533: l_error_msg VARCHAR2(4000);

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 565: AND EXISTS ( SELECT 1 FROM mtl_system_items_b msi

561: WHERE acc.organization_id = uorg.organization_id
562: AND uorg.category_set_id IS NULL
563: AND uorg.category_id IS NULL
564: AND acc.USER_ID = -1
565: AND EXISTS ( SELECT 1 FROM mtl_system_items_b msi
566: WHERE msi.inventory_item_id = acc.inventory_item_id
567: AND msi.organization_id = acc.organization_id
568: AND (msi.last_update_date >= b_last_date))
569: UNION

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 577: AND EXISTS ( SELECT 1 FROM mtl_system_items_tl tl

573: WHERE acc.organization_id = uorg.organization_id
574: AND uorg.category_set_id IS NULL
575: AND uorg.category_id IS NULL
576: AND acc.USER_ID = -1
577: AND EXISTS ( SELECT 1 FROM mtl_system_items_tl tl
578: WHERE tl.inventory_item_id = acc.inventory_item_id
579: AND tl.organization_id = acc.organization_id
580: AND (tl.last_update_date >= b_last_date));
581:

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 605: AND EXISTS ( SELECT 1 FROM mtl_system_items_b msi

601: WHERE acc.organization_id = uorg.organization_id
602: AND uorg.category_set_id IS NULL
603: AND uorg.category_id IS NULL
604: AND acc.USER_ID = -1
605: AND EXISTS ( SELECT 1 FROM mtl_system_items_b msi
606: WHERE msi.inventory_item_id = acc.inventory_item_id
607: AND msi.organization_id = acc.organization_id
608: AND (msi.enabled_flag = 'N' OR msi.end_date_active < b_last_date));
609:

Line 611: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS ',

607: AND msi.organization_id = acc.organization_id
608: AND (msi.enabled_flag = 'N' OR msi.end_date_active < b_last_date));
609:
610: BEGIN
611: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS ',
612: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
613:
614: l_run_date := SYSDATE;
615:

Line 612: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);

608: AND (msi.enabled_flag = 'N' OR msi.end_date_active < b_last_date));
609:
610: BEGIN
611: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS ',
612: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
613:
614: l_run_date := SYSDATE;
615:
616: /* This portion of code assumes indexes on last_update_date on MTL_SYSTEM_ITEMS_B */

Line 616: /* This portion of code assumes indexes on last_update_date on MTL_SYSTEM_ITEMS_B */

612: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
613:
614: l_run_date := SYSDATE;
615:
616: /* This portion of code assumes indexes on last_update_date on MTL_SYSTEM_ITEMS_B */
617: /* , MTL_SYSTEM_ITEMS_TL which were custom created */
618: SELECT MAX(last_update_date) INTO l_max_last_update_date_b
619: FROM mtl_system_items_b;
620: IF( l_max_last_update_date_b < p_last_run_date) THEN

Line 617: /* , MTL_SYSTEM_ITEMS_TL which were custom created */

613:
614: l_run_date := SYSDATE;
615:
616: /* This portion of code assumes indexes on last_update_date on MTL_SYSTEM_ITEMS_B */
617: /* , MTL_SYSTEM_ITEMS_TL which were custom created */
618: SELECT MAX(last_update_date) INTO l_max_last_update_date_b
619: FROM mtl_system_items_b;
620: IF( l_max_last_update_date_b < p_last_run_date) THEN
621: SELECT MAX(last_update_date) INTO l_max_last_update_date_tl

Line 619: FROM mtl_system_items_b;

615:
616: /* This portion of code assumes indexes on last_update_date on MTL_SYSTEM_ITEMS_B */
617: /* , MTL_SYSTEM_ITEMS_TL which were custom created */
618: SELECT MAX(last_update_date) INTO l_max_last_update_date_b
619: FROM mtl_system_items_b;
620: IF( l_max_last_update_date_b < p_last_run_date) THEN
621: SELECT MAX(last_update_date) INTO l_max_last_update_date_tl
622: FROM mtl_system_items_tl;
623: IF(l_max_last_update_date_tl < p_last_run_date) THEN

Line 622: FROM mtl_system_items_tl;

618: SELECT MAX(last_update_date) INTO l_max_last_update_date_b
619: FROM mtl_system_items_b;
620: IF( l_max_last_update_date_b < p_last_run_date) THEN
621: SELECT MAX(last_update_date) INTO l_max_last_update_date_tl
622: FROM mtl_system_items_tl;
623: IF(l_max_last_update_date_tl < p_last_run_date) THEN
624: -- No updates
625: CSM_UTIL_PKG.LOG('Leaving UPDATE_MTL_SYSTEM_ITEMS - No Updates ',
626: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);

Line 625: CSM_UTIL_PKG.LOG('Leaving UPDATE_MTL_SYSTEM_ITEMS - No Updates ',

621: SELECT MAX(last_update_date) INTO l_max_last_update_date_tl
622: FROM mtl_system_items_tl;
623: IF(l_max_last_update_date_tl < p_last_run_date) THEN
624: -- No updates
625: CSM_UTIL_PKG.LOG('Leaving UPDATE_MTL_SYSTEM_ITEMS - No Updates ',
626: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
627: RETURN;
628: END IF;
629: END IF;

Line 626: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);

622: FROM mtl_system_items_tl;
623: IF(l_max_last_update_date_tl < p_last_run_date) THEN
624: -- No updates
625: CSM_UTIL_PKG.LOG('Leaving UPDATE_MTL_SYSTEM_ITEMS - No Updates ',
626: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
627: RETURN;
628: END IF;
629: END IF;
630:

Line 646: CSM_UTIL_PKG.LOG(l_tab_access_id.COUNT || ' records sent to olite for updating csm_mtl_system_items',

642:
643: FETCH c_del_org_based BULK COLLECT INTO l_tab_access_id, l_tab_user_id LIMIT 1000;
644: EXIT WHEN l_tab_access_id.COUNT = 0;
645:
646: CSM_UTIL_PKG.LOG(l_tab_access_id.COUNT || ' records sent to olite for updating csm_mtl_system_items',
647: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
648: l_transaction_flag :='Y'; --this flag is to save db access
649: -- do bulk makedirty
650: l_markdirty := asg_download.mark_dirty(

Line 647: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);

643: FETCH c_del_org_based BULK COLLECT INTO l_tab_access_id, l_tab_user_id LIMIT 1000;
644: EXIT WHEN l_tab_access_id.COUNT = 0;
645:
646: CSM_UTIL_PKG.LOG(l_tab_access_id.COUNT || ' records sent to olite for updating csm_mtl_system_items',
647: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
648: l_transaction_flag :='Y'; --this flag is to save db access
649: -- do bulk makedirty
650: l_markdirty := asg_download.mark_dirty(
651: P_PUB_ITEM => g_pub_item

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 667: AND EXISTS ( SELECT 1 FROM mtl_system_items_b msi

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
670: AND msi.enabled_flag = 'N' OR msi.end_date_active < l_run_date);
671: COMMIT;

Line 684: CSM_UTIL_PKG.LOG(l_tab_access_id.COUNT || ' records sent to olite for updating csm_mtl_system_items',

680: FETCH c_changed_org_based BULK COLLECT INTO l_tab_access_id, l_tab_user_id LIMIT 1000;
681: EXIT WHEN l_tab_access_id.COUNT = 0;
682:
683: IF l_tab_access_id.COUNT > 0 THEN
684: CSM_UTIL_PKG.LOG(l_tab_access_id.COUNT || ' records sent to olite for updating csm_mtl_system_items',
685: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
686:
687: -- do bulk makedirty
688: l_markdirty := asg_download.mark_dirty(

Line 685: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);

681: EXIT WHEN l_tab_access_id.COUNT = 0;
682:
683: IF l_tab_access_id.COUNT > 0 THEN
684: CSM_UTIL_PKG.LOG(l_tab_access_id.COUNT || ' records sent to olite for updating csm_mtl_system_items',
685: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
686:
687: -- do bulk makedirty
688: l_markdirty := asg_download.mark_dirty(
689: P_PUB_ITEM => g_pub_item

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 706: FROM MTL_SYSTEM_ITEMS_B msi

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)
709: AND msi.ORGANIZATION_ID
710: IN(SELECT DISTINCT uorg.ORGANIZATION_ID

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 732: CSM_UTIL_PKG.LOG(l_tab_access_id.COUNT || ' records sent to olite for Inerting csm_mtl_system_items',

728: FETCH c_New_items_for_org BULK COLLECT INTO l_tab_access_id, l_tab_user_id LIMIT 1000;
729: EXIT WHEN l_tab_access_id.COUNT = 0;
730:
731: IF l_tab_access_id.COUNT > 0 THEN
732: CSM_UTIL_PKG.LOG(l_tab_access_id.COUNT || ' records sent to olite for Inerting csm_mtl_system_items',
733: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
734: l_transaction_flag := 'Y';
735: -- do bulk makedirty
736: l_markdirty := asg_download.mark_dirty(

Line 733: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);

729: EXIT WHEN l_tab_access_id.COUNT = 0;
730:
731: IF l_tab_access_id.COUNT > 0 THEN
732: CSM_UTIL_PKG.LOG(l_tab_access_id.COUNT || ' records sent to olite for Inerting csm_mtl_system_items',
733: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
734: l_transaction_flag := 'Y';
735: -- do bulk makedirty
736: l_markdirty := asg_download.mark_dirty(
737: P_PUB_ITEM => g_pub_item

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 766: CSM_UTIL_PKG.LOG(l_tab_access_id.COUNT || ' records sent to olite for updating csm_mtl_system_items',

762: FETCH c_changed BULK COLLECT INTO l_tab_access_id, l_tab_user_id LIMIT 1000;
763: EXIT WHEN l_tab_access_id.COUNT = 0;
764:
765: IF l_tab_access_id.COUNT > 0 THEN
766: CSM_UTIL_PKG.LOG(l_tab_access_id.COUNT || ' records sent to olite for updating csm_mtl_system_items',
767: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
768:
769: -- do bulk makedirty
770: l_markdirty := asg_download.mark_dirty(

Line 767: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);

763: EXIT WHEN l_tab_access_id.COUNT = 0;
764:
765: IF l_tab_access_id.COUNT > 0 THEN
766: CSM_UTIL_PKG.LOG(l_tab_access_id.COUNT || ' records sent to olite for updating csm_mtl_system_items',
767: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
768:
769: -- do bulk makedirty
770: l_markdirty := asg_download.mark_dirty(
771: P_PUB_ITEM => g_pub_item

Line 784: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS ',

780: CLOSE c_changed;
781:
782: END IF;
783:
784: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS ',
785: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
786:
787: EXCEPTION
788: WHEN OTHERS THEN

Line 785: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);

781:
782: END IF;
783:
784: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS ',
785: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
786:
787: EXCEPTION
788: WHEN OTHERS THEN
789: l_sqlerrno := TO_CHAR(SQLCODE);

Line 791: l_error_msg := ' Exception in UPDATE_MTL_SYSTEM_ITEMS :' || l_sqlerrno || ':' || l_sqlerrmsg;

787: EXCEPTION
788: WHEN OTHERS THEN
789: l_sqlerrno := TO_CHAR(SQLCODE);
790: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
791: l_error_msg := ' Exception in UPDATE_MTL_SYSTEM_ITEMS :' || l_sqlerrno || ':' || l_sqlerrmsg;
792: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
793: RAISE;
794: END UPDATE_MTL_SYSTEM_ITEMS;
795:

Line 792: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);

788: WHEN OTHERS THEN
789: l_sqlerrno := TO_CHAR(SQLCODE);
790: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
791: l_error_msg := ' Exception in UPDATE_MTL_SYSTEM_ITEMS :' || l_sqlerrno || ':' || l_sqlerrmsg;
792: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
793: RAISE;
794: END UPDATE_MTL_SYSTEM_ITEMS;
795:
796: PROCEDURE delete_mtl_system_items(p_user_id IN NUMBER,

Line 794: END UPDATE_MTL_SYSTEM_ITEMS;

790: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
791: l_error_msg := ' Exception in UPDATE_MTL_SYSTEM_ITEMS :' || l_sqlerrno || ':' || l_sqlerrmsg;
792: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
793: RAISE;
794: END UPDATE_MTL_SYSTEM_ITEMS;
795:
796: PROCEDURE delete_mtl_system_items(p_user_id IN NUMBER,
797: p_organization_id IN NUMBER,
798: p_category_set_id IN NUMBER,

Line 796: PROCEDURE delete_mtl_system_items(p_user_id IN NUMBER,

792: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
793: RAISE;
794: END UPDATE_MTL_SYSTEM_ITEMS;
795:
796: PROCEDURE delete_mtl_system_items(p_user_id IN NUMBER,
797: p_organization_id IN NUMBER,
798: p_category_set_id IN NUMBER,
799: p_category_id IN NUMBER)
800: IS

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 829: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS ',

825:
826: l_profile_value VARCHAR2(100);
827: l_org_count NUMBER;
828: BEGIN
829: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS ',
830: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
831:
832: l_run_date := SYSDATE;
833: l_tab_access_id.DELETE;

Line 830: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);

826: l_profile_value VARCHAR2(100);
827: l_org_count NUMBER;
828: BEGIN
829: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS ',
830: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
831:
832: l_run_date := SYSDATE;
833: l_tab_access_id.DELETE;
834:

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 927: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS ',

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 ',
928: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
929: EXCEPTION
930: WHEN others THEN
931: l_sqlerrno := TO_CHAR(SQLCODE);

Line 928: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);

924: END IF;
925: END IF;
926:
927: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS ',
928: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
929: EXCEPTION
930: WHEN others THEN
931: l_sqlerrno := TO_CHAR(SQLCODE);
932: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);

Line 933: CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS: ' || l_sqlerrno || ':' || l_sqlerrmsg,

929: EXCEPTION
930: WHEN others THEN
931: l_sqlerrno := TO_CHAR(SQLCODE);
932: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
933: CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
934: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
935: RAISE;
936: END DELETE_MTL_SYSTEM_ITEMS;
937:

Line 934: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);

930: WHEN others THEN
931: l_sqlerrno := TO_CHAR(SQLCODE);
932: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
933: CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
934: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
935: RAISE;
936: END DELETE_MTL_SYSTEM_ITEMS;
937:
938: PROCEDURE concurrent_process_user(p_user_id IN NUMBER,

Line 936: END DELETE_MTL_SYSTEM_ITEMS;

932: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
933: CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
934: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
935: RAISE;
936: END DELETE_MTL_SYSTEM_ITEMS;
937:
938: PROCEDURE concurrent_process_user(p_user_id IN NUMBER,
939: p_organization_id IN NUMBER,
940: p_category_set_id IN NUMBER,

Line 963: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);

959: r_org c_org%ROWTYPE;
960: l_profile_value VARCHAR2(10);
961: BEGIN
962: CSM_UTIL_PKG.LOG('Entering CONCURRENT_PROCESS_USER for user_id: ' || TO_CHAR(p_user_id),
963: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
964:
965: /* Get old profile settings */
966: g_download_inv_flag := NULL;
967: g_download_inv_flag := CSM_PROFILE_PKG.Get_download_inventory_flag

Line 974: || ' - Inserting all mtl_system_items',

970: OPEN c_org( p_user_id );
971: FETCH c_org INTO r_org;
972: IF c_org%NOTFOUND THEN -- should not occur
973: CSM_UTIL_PKG.LOG('Profile record not found in csm_user_inventory_org for user_id: ' || TO_CHAR(p_user_id)
974: || ' - Inserting all mtl_system_items',
975: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
976: INSERT INTO csm_user_inventory_org (
977: user_id, organization_id, last_update_date, last_updated_by,
978: creation_date, created_by, category_set_id, category_id )

Line 975: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);

971: FETCH c_org INTO r_org;
972: IF c_org%NOTFOUND THEN -- should not occur
973: CSM_UTIL_PKG.LOG('Profile record not found in csm_user_inventory_org for user_id: ' || TO_CHAR(p_user_id)
974: || ' - Inserting all mtl_system_items',
975: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
976: INSERT INTO csm_user_inventory_org (
977: user_id, organization_id, last_update_date, last_updated_by,
978: creation_date, created_by, category_set_id, category_id )
979: VALUES (

Line 983: get_new_user_mtl_system_items(p_user_id=>p_user_id, p_organization_id=>p_organization_id,

979: VALUES (
980: p_user_id, p_organization_id, SYSDATE, 1, SYSDATE, 1,
981: p_category_set_id, p_category_id );
982:
983: get_new_user_mtl_system_items(p_user_id=>p_user_id, p_organization_id=>p_organization_id,
984: p_category_set_id=>p_category_set_id, p_category_id=>p_category_id);
985:
986:
987: ELSE

Line 1020: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);

1016:
1017: IF NVL(p_organization_id, -1) <> NVL(r_org.organization_id, -1)
1018: OR l_cat_filter_changed THEN
1019: CSM_UTIL_PKG.LOG('Deleting records for old profile settings for user_id: ' || TO_CHAR(p_user_id),
1020: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
1021:
1022: -- delete labor/expense items only and then re-insert for the new orgif org changes
1023: IF NVL(p_organization_id, -1) <> NVL(r_org.organization_id,-1) THEN
1024: csm_system_item_event_pkg.delete_system_items(p_user_id=>p_user_id,

Line 1031: delete_mtl_system_items(p_user_id=>p_user_id,

1027: -- download new labor/expense items for the new org
1028: csm_system_item_event_pkg.get_new_user_system_items(p_user_id=>p_user_id);
1029: END IF;
1030:
1031: delete_mtl_system_items(p_user_id=>p_user_id,
1032: p_organization_id=>r_org.organization_id,
1033: p_category_set_id=>r_org.category_set_id,
1034: p_category_id=>r_org.category_id);
1035:

Line 1037: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);

1033: p_category_set_id=>r_org.category_set_id,
1034: p_category_id=>r_org.category_id);
1035:
1036: CSM_UTIL_PKG.LOG('Inserting records for new profile settings for user_id: ' || TO_CHAR(p_user_id),
1037: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
1038:
1039: insert_mtl_system_items(p_user_id=>p_user_id,
1040: p_organization_id=>p_organization_id,
1041: p_category_set_id=>p_category_set_id,

Line 1039: insert_mtl_system_items(p_user_id=>p_user_id,

1035:
1036: CSM_UTIL_PKG.LOG('Inserting records for new profile settings for user_id: ' || TO_CHAR(p_user_id),
1037: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
1038:
1039: insert_mtl_system_items(p_user_id=>p_user_id,
1040: p_organization_id=>p_organization_id,
1041: p_category_set_id=>p_category_set_id,
1042: p_category_id=>p_category_id,
1043: p_last_run_date=>NULL,

Line 1047: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);

1043: p_last_run_date=>NULL,
1044: p_changed=>'Y');
1045:
1046: CSM_UTIL_PKG.LOG('Update csm_user_inventory_org with new profile settings for user_id: ' || TO_CHAR(p_user_id),
1047: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
1048:
1049: UPDATE csm_user_inventory_org
1050: SET organization_id = p_organization_id
1051: , category_set_id = p_category_set_id

Line 1060: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);

1056: ELSE
1057: -- profiles are the same
1058: -- get any new changes
1059: CSM_UTIL_PKG.LOG('Getting new items for same profile settings for user_id: ' || TO_CHAR(p_user_id),
1060: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
1061:
1062: insert_mtl_system_items(p_user_id=>p_user_id,
1063: p_organization_id=>p_organization_id,
1064: p_category_set_id=>p_category_set_id,

Line 1062: insert_mtl_system_items(p_user_id=>p_user_id,

1058: -- get any new changes
1059: CSM_UTIL_PKG.LOG('Getting new items for same profile settings for user_id: ' || TO_CHAR(p_user_id),
1060: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
1061:
1062: insert_mtl_system_items(p_user_id=>p_user_id,
1063: p_organization_id=>p_organization_id,
1064: p_category_set_id=>p_category_set_id,
1065: p_category_id=>p_category_id,
1066: p_last_run_date=>p_last_run_date,

Line 1073: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);

1069: END IF;
1070: CLOSE c_org;
1071:
1072: CSM_UTIL_PKG.LOG('Leaving CONCURRENT_PROCESS_USER for user_id: ' || TO_CHAR(p_user_id),
1073: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
1074: EXCEPTION
1075: WHEN OTHERS THEN
1076: l_sqlerrno := TO_CHAR(SQLCODE);
1077: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);

Line 1080: CSM_UTIL_PKG.LOG(l_error_msg, 'csm_mtl_system_items_event_pkg.concurrent_process_user',FND_LOG.LEVEL_EXCEPTION);

1076: l_sqlerrno := TO_CHAR(SQLCODE);
1077: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
1078: l_error_msg := ' Exception in concurrent_process_user for user_id :'
1079: || TO_CHAR(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1080: CSM_UTIL_PKG.LOG(l_error_msg, 'csm_mtl_system_items_event_pkg.concurrent_process_user',FND_LOG.LEVEL_EXCEPTION);
1081: RAISE;
1082: END CONCURRENT_PROCESS_USER;
1083:
1084: /*--NO LONGER USING THIS API */

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 1112: TYPE inventory_item_id_tbl_typ IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;

1108: and acc.organization_id=d.org_id
1109: and acc.user_id=d.user_id);
1110:
1111:
1112: TYPE inventory_item_id_tbl_typ IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
1113: TYPE organization_id_tbl_typ IS TABLE OF mtl_system_items_b.organization_id%TYPE INDEX BY BINARY_INTEGER;
1114: l_inventory_item_id_tbl inventory_item_id_tbl_typ;
1115: l_organization_id_tbl organization_id_tbl_typ;
1116: l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;

Line 1113: TYPE organization_id_tbl_typ IS TABLE OF mtl_system_items_b.organization_id%TYPE INDEX BY BINARY_INTEGER;

1109: and acc.user_id=d.user_id);
1110:
1111:
1112: TYPE inventory_item_id_tbl_typ IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
1113: TYPE organization_id_tbl_typ IS TABLE OF mtl_system_items_b.organization_id%TYPE INDEX BY BINARY_INTEGER;
1114: l_inventory_item_id_tbl inventory_item_id_tbl_typ;
1115: l_organization_id_tbl organization_id_tbl_typ;
1116: l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
1117: l_tab_user_id ASG_DOWNLOAD.USER_LIST;

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 1134: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.process_htm5_ordered_items',FND_LOG.LEVEL_EVENT);

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,
1138: created_by, creation_date, last_updated_by, last_update_date, last_update_login)

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 1222: TYPE inventory_item_id_tbl_typ IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;

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
1220: AND msi.last_update_date > p_run_date;
1221:
1222: TYPE inventory_item_id_tbl_typ IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
1223: TYPE organization_id_tbl_typ IS TABLE OF mtl_system_items_b.organization_id%TYPE INDEX BY BINARY_INTEGER;
1224: l_inventory_item_id_tbl inventory_item_id_tbl_typ;
1225: l_organization_id_tbl organization_id_tbl_typ;
1226: l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;

Line 1223: TYPE organization_id_tbl_typ IS TABLE OF mtl_system_items_b.organization_id%TYPE INDEX BY BINARY_INTEGER;

1219: AND acc.CREATION_DATE <=p_run_date
1220: AND msi.last_update_date > p_run_date;
1221:
1222: TYPE inventory_item_id_tbl_typ IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
1223: TYPE organization_id_tbl_typ IS TABLE OF mtl_system_items_b.organization_id%TYPE INDEX BY BINARY_INTEGER;
1224: l_inventory_item_id_tbl inventory_item_id_tbl_typ;
1225: l_organization_id_tbl organization_id_tbl_typ;
1226: l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
1227: l_tab_user_id ASG_DOWNLOAD.USER_LIST;

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 1303: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.process_htm5_user_items',FND_LOG.LEVEL_EVENT);

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,
1307: created_by, creation_date, last_updated_by, last_update_date, last_update_login)

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:

Line 1329: PROCEDURE Refresh_mtl_system_items_acc(p_status OUT NOCOPY VARCHAR2,

1325: --process_htm5_ordered_items; --will be done synchronously
1326:
1327: END process_htm5_user_items;
1328:
1329: PROCEDURE Refresh_mtl_system_items_acc(p_status OUT NOCOPY VARCHAR2,
1330: p_message OUT NOCOPY VARCHAR2)
1331: IS
1332: PRAGMA AUTONOMOUS_TRANSACTION;
1333: l_prog_update_date jtm_con_request_data.last_run_date%TYPE;

Line 1337: l_user_organization_id mtl_system_items.organization_id%TYPE;

1333: l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
1334: l_all_omfs_user_list asg_download.user_list;
1335: l_null_omfs_user_list asg_download.user_list;
1336: l_user_id fnd_user.user_id%TYPE;
1337: l_user_organization_id mtl_system_items.organization_id%TYPE;
1338: l_user_category_set_id mtl_category_sets.category_set_id%TYPE;
1339: l_user_category_id mtl_categories.category_id%TYPE;
1340: l_run_date DATE;
1341: l_sqlerrno VARCHAR2(20);

Line 1349: WHERE package_name = 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG'

1345: CURSOR l_last_run_date_csr
1346: IS
1347: SELECT NVL(last_run_date, TO_DATE('1','J'))
1348: FROM jtm_con_request_data
1349: WHERE package_name = 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG'
1350: AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';
1351:
1352: BEGIN
1353: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.Refresh_mtl_system_items_acc ',

Line 1350: AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';

1346: IS
1347: SELECT NVL(last_run_date, TO_DATE('1','J'))
1348: FROM jtm_con_request_data
1349: WHERE package_name = 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG'
1350: AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';
1351:
1352: BEGIN
1353: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.Refresh_mtl_system_items_acc ',
1354: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.Refresh_mtl_system_items_acc',FND_LOG.LEVEL_PROCEDURE);

Line 1353: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.Refresh_mtl_system_items_acc ',

1349: WHERE package_name = 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG'
1350: AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';
1351:
1352: BEGIN
1353: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.Refresh_mtl_system_items_acc ',
1354: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.Refresh_mtl_system_items_acc',FND_LOG.LEVEL_PROCEDURE);
1355:
1356: l_run_date := SYSDATE;
1357:

Line 1354: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.Refresh_mtl_system_items_acc',FND_LOG.LEVEL_PROCEDURE);

1350: AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';
1351:
1352: BEGIN
1353: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.Refresh_mtl_system_items_acc ',
1354: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.Refresh_mtl_system_items_acc',FND_LOG.LEVEL_PROCEDURE);
1355:
1356: l_run_date := SYSDATE;
1357:
1358: -- get last conc program update date

Line 1375: update_mtl_system_items(p_last_run_date => l_prog_update_date);

1371: and nvl(multi_platform,'N') = 'N';
1372:
1373: IF(l_num_non_multi_users > 0) THEN
1374: -- do an update for existing records
1375: update_mtl_system_items(p_last_run_date => l_prog_update_date);
1376:
1377: COMMIT;
1378:
1379: -- get user list of all omfs users

Line 1407: WHERE package_name = 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG'

1403:
1404: -- update last_run_date
1405: UPDATE jtm_con_request_data
1406: SET last_run_date = l_run_date
1407: WHERE package_name = 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG'
1408: AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';
1409:
1410: COMMIT;
1411:

Line 1408: AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';

1404: -- update last_run_date
1405: UPDATE jtm_con_request_data
1406: SET last_run_date = l_run_date
1407: WHERE package_name = 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG'
1408: AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';
1409:
1410: COMMIT;
1411:
1412: p_status := 'FINE';

Line 1413: p_message := 'csm_mtl_system_items_event_pkg.refresh_system_items executed successfully';

1409:
1410: COMMIT;
1411:
1412: p_status := 'FINE';
1413: p_message := 'csm_mtl_system_items_event_pkg.refresh_system_items executed successfully';
1414:
1415: CSM_UTIL_PKG.LOG('Leaving csm_mtl_system_items_event_pkg.Refresh_mtl_system_items_acc ',
1416: 'csm_mtl_system_items_event_pkg.Refresh_mtl_system_items_acc',FND_LOG.LEVEL_PROCEDURE);
1417: EXCEPTION

Line 1415: CSM_UTIL_PKG.LOG('Leaving csm_mtl_system_items_event_pkg.Refresh_mtl_system_items_acc ',

1411:
1412: p_status := 'FINE';
1413: p_message := 'csm_mtl_system_items_event_pkg.refresh_system_items executed successfully';
1414:
1415: CSM_UTIL_PKG.LOG('Leaving csm_mtl_system_items_event_pkg.Refresh_mtl_system_items_acc ',
1416: 'csm_mtl_system_items_event_pkg.Refresh_mtl_system_items_acc',FND_LOG.LEVEL_PROCEDURE);
1417: EXCEPTION
1418: WHEN others THEN
1419: l_sqlerrno := TO_CHAR(SQLCODE);

Line 1416: 'csm_mtl_system_items_event_pkg.Refresh_mtl_system_items_acc',FND_LOG.LEVEL_PROCEDURE);

1412: p_status := 'FINE';
1413: p_message := 'csm_mtl_system_items_event_pkg.refresh_system_items executed successfully';
1414:
1415: CSM_UTIL_PKG.LOG('Leaving csm_mtl_system_items_event_pkg.Refresh_mtl_system_items_acc ',
1416: 'csm_mtl_system_items_event_pkg.Refresh_mtl_system_items_acc',FND_LOG.LEVEL_PROCEDURE);
1417: EXCEPTION
1418: WHEN others THEN
1419: l_sqlerrno := TO_CHAR(SQLCODE);
1420: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);

Line 1422: p_message := 'Error in CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.REFRESH_MTL_SYSTEM_ITEMS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg;

1418: WHEN others THEN
1419: l_sqlerrno := TO_CHAR(SQLCODE);
1420: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
1421: p_status := 'ERROR';
1422: p_message := 'Error in CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.REFRESH_MTL_SYSTEM_ITEMS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg;
1423: ROLLBACK;
1424: csm_util_pkg.log('CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.REFRESH_MTL_SYSTEM_ITEMS_ACC ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
1425: END REFRESH_MTL_SYSTEM_ITEMS_ACC;
1426:

Line 1424: csm_util_pkg.log('CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.REFRESH_MTL_SYSTEM_ITEMS_ACC ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);

1420: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
1421: p_status := 'ERROR';
1422: p_message := 'Error in CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.REFRESH_MTL_SYSTEM_ITEMS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg;
1423: ROLLBACK;
1424: csm_util_pkg.log('CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.REFRESH_MTL_SYSTEM_ITEMS_ACC ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
1425: END REFRESH_MTL_SYSTEM_ITEMS_ACC;
1426:
1427: PROCEDURE get_new_user_mtl_system_items(p_user_id IN NUMBER, p_organization_id IN NUMBER,
1428: p_category_set_id IN NUMBER, p_category_id IN NUMBER)

Line 1425: END REFRESH_MTL_SYSTEM_ITEMS_ACC;

1421: p_status := 'ERROR';
1422: p_message := 'Error in CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.REFRESH_MTL_SYSTEM_ITEMS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg;
1423: ROLLBACK;
1424: csm_util_pkg.log('CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.REFRESH_MTL_SYSTEM_ITEMS_ACC ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
1425: END REFRESH_MTL_SYSTEM_ITEMS_ACC;
1426:
1427: PROCEDURE get_new_user_mtl_system_items(p_user_id IN NUMBER, p_organization_id IN NUMBER,
1428: p_category_set_id IN NUMBER, p_category_id IN NUMBER)
1429: IS

Line 1427: PROCEDURE get_new_user_mtl_system_items(p_user_id IN NUMBER, p_organization_id IN NUMBER,

1423: ROLLBACK;
1424: csm_util_pkg.log('CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.REFRESH_MTL_SYSTEM_ITEMS_ACC ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
1425: END REFRESH_MTL_SYSTEM_ITEMS_ACC;
1426:
1427: PROCEDURE get_new_user_mtl_system_items(p_user_id IN NUMBER, p_organization_id IN NUMBER,
1428: p_category_set_id IN NUMBER, p_category_id IN NUMBER)
1429: IS
1430: l_sqlerrno VARCHAR2(20);
1431: l_sqlerrmsg VARCHAR2(4000);

Line 1437: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items ',

1433: l_return_status VARCHAR2(2000);
1434: l_profile_value VARCHAR2(10);
1435: l_changed varchar2(10);
1436: BEGIN
1437: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items ',
1438: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_PROCEDURE);
1439: --The flag can be null only when is called from csm_user_event_pkg during
1440: --User creation
1441: IF g_download_inv_flag IS NULL THEN

Line 1438: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_PROCEDURE);

1434: l_profile_value VARCHAR2(10);
1435: l_changed varchar2(10);
1436: BEGIN
1437: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items ',
1438: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_PROCEDURE);
1439: --The flag can be null only when is called from csm_user_event_pkg during
1440: --User creation
1441: IF g_download_inv_flag IS NULL THEN
1442: --GEt the value for CSM: Download Organization Level Inventory

Line 1453: insert_mtl_system_items(p_user_id=>p_user_id,

1449: ELSE
1450: l_changed := 'N';
1451: END IF;
1452:
1453: insert_mtl_system_items(p_user_id=>p_user_id,
1454: p_organization_id=>p_organization_id,
1455: p_category_set_id=>p_category_set_id,
1456: p_category_id=>p_category_id,
1457: p_last_run_date=>NULL,

Line 1460: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items ',

1456: p_category_id=>p_category_id,
1457: p_last_run_date=>NULL,
1458: p_changed=>l_changed); -- new user, no profiles are changed
1459:
1460: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items ',
1461: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_PROCEDURE);
1462:
1463: EXCEPTION
1464: WHEN others THEN

Line 1461: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_PROCEDURE);

1457: p_last_run_date=>NULL,
1458: p_changed=>l_changed); -- new user, no profiles are changed
1459:
1460: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items ',
1461: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_PROCEDURE);
1462:
1463: EXCEPTION
1464: WHEN others THEN
1465: l_sqlerrno := TO_CHAR(SQLCODE);

Line 1467: l_error_msg := ' Exception in get_new_user_mtl_system_items for user_id :'

1463: EXCEPTION
1464: WHEN others THEN
1465: l_sqlerrno := TO_CHAR(SQLCODE);
1466: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
1467: l_error_msg := ' Exception in get_new_user_mtl_system_items for user_id :'
1468: || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1469: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_EXCEPTION);
1470: RAISE;
1471: END get_new_user_mtl_system_items;

Line 1469: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_EXCEPTION);

1465: l_sqlerrno := TO_CHAR(SQLCODE);
1466: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
1467: l_error_msg := ' Exception in get_new_user_mtl_system_items for user_id :'
1468: || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1469: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_EXCEPTION);
1470: RAISE;
1471: END get_new_user_mtl_system_items;
1472:
1473: PROCEDURE mtl_system_items_acc_i(p_inventory_item_id IN NUMBER,

Line 1471: END get_new_user_mtl_system_items;

1467: l_error_msg := ' Exception in get_new_user_mtl_system_items for user_id :'
1468: || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1469: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_EXCEPTION);
1470: RAISE;
1471: END get_new_user_mtl_system_items;
1472:
1473: PROCEDURE mtl_system_items_acc_i(p_inventory_item_id IN NUMBER,
1474: p_organization_id IN NUMBER,
1475: p_user_id IN NUMBER,

Line 1473: PROCEDURE mtl_system_items_acc_i(p_inventory_item_id IN NUMBER,

1469: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_EXCEPTION);
1470: RAISE;
1471: END get_new_user_mtl_system_items;
1472:
1473: PROCEDURE mtl_system_items_acc_i(p_inventory_item_id IN NUMBER,
1474: p_organization_id IN NUMBER,
1475: p_user_id IN NUMBER,
1476: p_error_msg OUT NOCOPY VARCHAR2,
1477: x_return_status IN OUT NOCOPY VARCHAR2)

Line 1481: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I ',

1477: x_return_status IN OUT NOCOPY VARCHAR2)
1478: IS
1479: BEGIN
1480: x_return_status := FND_API.G_RET_STS_SUCCESS;
1481: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I ',
1482: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
1483:
1484: IF p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL THEN
1485: CSM_ACC_PKG.Insert_Acc

Line 1482: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_PROCEDURE);

1478: IS
1479: BEGIN
1480: x_return_status := FND_API.G_RET_STS_SUCCESS;
1481: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I ',
1482: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
1483:
1484: IF p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL THEN
1485: CSM_ACC_PKG.Insert_Acc
1486: ( P_PUBLICATION_ITEM_NAMES => g_mtl_sys_items_pubi_name

Line 1499: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I ',

1495:
1496: END IF;
1497:
1498: p_error_msg := 'SUCCESS';
1499: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I ',
1500: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
1501: EXCEPTION
1502: WHEN others THEN
1503: x_return_status := FND_API.G_RET_STS_ERROR;

Line 1500: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_PROCEDURE);

1496: END IF;
1497:
1498: p_error_msg := 'SUCCESS';
1499: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I ',
1500: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
1501: EXCEPTION
1502: WHEN others THEN
1503: x_return_status := FND_API.G_RET_STS_ERROR;
1504: p_error_msg := ' FAILED MTL_SYSTEM_ITEMS_ACC_I INVENTORY_ITEM_ID: ' || to_char(p_inventory_item_id) || SUBSTR(SQLERRM,1,2000);

Line 1504: p_error_msg := ' FAILED MTL_SYSTEM_ITEMS_ACC_I INVENTORY_ITEM_ID: ' || to_char(p_inventory_item_id) || SUBSTR(SQLERRM,1,2000);

1500: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
1501: EXCEPTION
1502: WHEN others THEN
1503: x_return_status := FND_API.G_RET_STS_ERROR;
1504: p_error_msg := ' FAILED MTL_SYSTEM_ITEMS_ACC_I INVENTORY_ITEM_ID: ' || to_char(p_inventory_item_id) || SUBSTR(SQLERRM,1,2000);
1505: CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_EXCEPTION);
1506: RAISE;
1507: END MTL_SYSTEM_ITEMS_ACC_I;
1508:

Line 1505: CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_EXCEPTION);

1501: EXCEPTION
1502: WHEN others THEN
1503: x_return_status := FND_API.G_RET_STS_ERROR;
1504: p_error_msg := ' FAILED MTL_SYSTEM_ITEMS_ACC_I INVENTORY_ITEM_ID: ' || to_char(p_inventory_item_id) || SUBSTR(SQLERRM,1,2000);
1505: CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_EXCEPTION);
1506: RAISE;
1507: END MTL_SYSTEM_ITEMS_ACC_I;
1508:
1509: PROCEDURE mtl_system_items_acc_d(p_inventory_item_id IN NUMBER,

Line 1507: END MTL_SYSTEM_ITEMS_ACC_I;

1503: x_return_status := FND_API.G_RET_STS_ERROR;
1504: p_error_msg := ' FAILED MTL_SYSTEM_ITEMS_ACC_I INVENTORY_ITEM_ID: ' || to_char(p_inventory_item_id) || SUBSTR(SQLERRM,1,2000);
1505: CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_EXCEPTION);
1506: RAISE;
1507: END MTL_SYSTEM_ITEMS_ACC_I;
1508:
1509: PROCEDURE mtl_system_items_acc_d(p_inventory_item_id IN NUMBER,
1510: p_organization_id IN NUMBER,
1511: p_user_id IN NUMBER,

Line 1509: PROCEDURE mtl_system_items_acc_d(p_inventory_item_id IN NUMBER,

1505: CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_EXCEPTION);
1506: RAISE;
1507: END MTL_SYSTEM_ITEMS_ACC_I;
1508:
1509: PROCEDURE mtl_system_items_acc_d(p_inventory_item_id IN NUMBER,
1510: p_organization_id IN NUMBER,
1511: p_user_id IN NUMBER,
1512: p_error_msg OUT NOCOPY VARCHAR2,
1513: x_return_status IN OUT NOCOPY VARCHAR2)

Line 1517: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',

1513: x_return_status IN OUT NOCOPY VARCHAR2)
1514: IS
1515: BEGIN
1516: x_return_status := FND_API.G_RET_STS_SUCCESS;
1517: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',
1518: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
1519:
1520: IF p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL THEN
1521: CSM_ACC_PKG.Delete_Acc

Line 1518: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_PROCEDURE);

1514: IS
1515: BEGIN
1516: x_return_status := FND_API.G_RET_STS_SUCCESS;
1517: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',
1518: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
1519:
1520: IF p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL THEN
1521: CSM_ACC_PKG.Delete_Acc
1522: ( P_PUBLICATION_ITEM_NAMES => g_mtl_sys_items_pubi_name

Line 1533: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D ',

1529: );
1530: END IF;
1531:
1532: p_error_msg := 'SUCCESS';
1533: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D ',
1534: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
1535: EXCEPTION
1536: WHEN others THEN
1537: x_return_status := FND_API.G_RET_STS_ERROR;

Line 1534: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_PROCEDURE);

1530: END IF;
1531:
1532: p_error_msg := 'SUCCESS';
1533: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D ',
1534: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
1535: EXCEPTION
1536: WHEN others THEN
1537: x_return_status := FND_API.G_RET_STS_ERROR;
1538: p_error_msg := ' FAILED MTL_SYSTEM_ITEMS_ACC_D INVENTORY_ITEM_ID: ' || to_char(p_inventory_item_id) || SUBSTR(SQLERRM,1,2000);

Line 1538: p_error_msg := ' FAILED MTL_SYSTEM_ITEMS_ACC_D INVENTORY_ITEM_ID: ' || to_char(p_inventory_item_id) || SUBSTR(SQLERRM,1,2000);

1534: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
1535: EXCEPTION
1536: WHEN others THEN
1537: x_return_status := FND_API.G_RET_STS_ERROR;
1538: p_error_msg := ' FAILED MTL_SYSTEM_ITEMS_ACC_D INVENTORY_ITEM_ID: ' || to_char(p_inventory_item_id) || SUBSTR(SQLERRM,1,2000);
1539: CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_EXCEPTION);
1540: RAISE;
1541: END MTL_SYSTEM_ITEMS_ACC_D;
1542:

Line 1539: CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_EXCEPTION);

1535: EXCEPTION
1536: WHEN others THEN
1537: x_return_status := FND_API.G_RET_STS_ERROR;
1538: p_error_msg := ' FAILED MTL_SYSTEM_ITEMS_ACC_D INVENTORY_ITEM_ID: ' || to_char(p_inventory_item_id) || SUBSTR(SQLERRM,1,2000);
1539: CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_EXCEPTION);
1540: RAISE;
1541: END MTL_SYSTEM_ITEMS_ACC_D;
1542:
1543: END CSM_MTL_SYSTEM_ITEMS_EVENT_PKG;

Line 1541: END MTL_SYSTEM_ITEMS_ACC_D;

1537: x_return_status := FND_API.G_RET_STS_ERROR;
1538: p_error_msg := ' FAILED MTL_SYSTEM_ITEMS_ACC_D INVENTORY_ITEM_ID: ' || to_char(p_inventory_item_id) || SUBSTR(SQLERRM,1,2000);
1539: CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_EXCEPTION);
1540: RAISE;
1541: END MTL_SYSTEM_ITEMS_ACC_D;
1542:
1543: END CSM_MTL_SYSTEM_ITEMS_EVENT_PKG;

Line 1543: END CSM_MTL_SYSTEM_ITEMS_EVENT_PKG;

1539: CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_EXCEPTION);
1540: RAISE;
1541: END MTL_SYSTEM_ITEMS_ACC_D;
1542:
1543: END CSM_MTL_SYSTEM_ITEMS_EVENT_PKG;