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.2 2006/04/06 21:37:52 trajasek noship $ */
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:
25: PROCEDURE insert_mtl_system_items( p_user_id IN NUMBER,
26: p_organization_id IN NUMBER,
27: p_category_set_id IN NUMBER,

Line 25: PROCEDURE insert_mtl_system_items( p_user_id IN NUMBER,

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:
25: PROCEDURE insert_mtl_system_items( p_user_id IN NUMBER,
26: p_organization_id IN NUMBER,
27: p_category_set_id IN NUMBER,
28: p_category_id IN NUMBER,
29: p_last_run_date IN DATE,

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

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

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

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

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

50: -- Both category and cat set are null
51: CURSOR c_items (b_user_id NUMBER, b_organization_id NUMBER,
52: b_changed VARCHAR2, b_last_run_date DATE)
53: IS
54: SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
55: FROM MTL_SYSTEM_ITEMS_B msi
56: WHERE msi.organization_id = b_organization_id
57: AND (b_changed = 'Y'
58: OR msi.creation_date >= NVL(b_last_run_date, msi.creation_date))

Line 55: FROM MTL_SYSTEM_ITEMS_B msi

51: CURSOR c_items (b_user_id NUMBER, b_organization_id NUMBER,
52: b_changed VARCHAR2, b_last_run_date DATE)
53: IS
54: SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
55: FROM MTL_SYSTEM_ITEMS_B msi
56: WHERE msi.organization_id = b_organization_id
57: AND (b_changed = 'Y'
58: OR msi.creation_date >= NVL(b_last_run_date, msi.creation_date))
59: AND NOT EXISTS

Line 61: FROM csm_mtl_system_items_acc acc

57: AND (b_changed = 'Y'
58: OR msi.creation_date >= NVL(b_last_run_date, msi.creation_date))
59: AND NOT EXISTS
60: ( SELECT 1
61: FROM csm_mtl_system_items_acc acc
62: WHERE user_id = b_user_id
63: AND acc.inventory_item_id = msi.inventory_item_id
64: AND acc.organization_id = msi.organization_id);
65:

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

66: -- Category is not null and Cat set is null
67: CURSOR c_items_Cat (b_user_id NUMBER, b_organization_id NUMBER,
68: b_changed VARCHAR2, b_last_run_date DATE, b_category_id NUMBER)
69: IS
70: SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
71: FROM mtl_system_items_b msi
72: WHERE NOT EXISTS
73: (SELECT 1
74: FROM csm_mtl_system_items_acc acc

Line 71: FROM mtl_system_items_b msi

67: CURSOR c_items_Cat (b_user_id NUMBER, b_organization_id NUMBER,
68: b_changed VARCHAR2, b_last_run_date DATE, b_category_id NUMBER)
69: IS
70: SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
71: FROM mtl_system_items_b msi
72: WHERE NOT EXISTS
73: (SELECT 1
74: FROM csm_mtl_system_items_acc acc
75: WHERE user_id = b_user_id

Line 74: FROM csm_mtl_system_items_acc acc

70: SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
71: FROM mtl_system_items_b msi
72: WHERE NOT EXISTS
73: (SELECT 1
74: FROM csm_mtl_system_items_acc acc
75: WHERE user_id = b_user_id
76: AND acc.inventory_item_id = msi.inventory_item_id
77: AND acc.organization_id = msi.organization_id
78: )

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

90: -- Category is null and Cat Set is not null
91: CURSOR c_items_Cat_Set (b_user_id NUMBER, b_organization_id NUMBER,
92: b_changed VARCHAR2, b_last_run_date DATE, b_category_set_id NUMBER)
93: IS
94: SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
95: FROM mtl_system_items_b msi
96: WHERE NOT EXISTS
97: (SELECT 1
98: FROM csm_mtl_system_items_acc acc

Line 95: FROM mtl_system_items_b msi

91: CURSOR c_items_Cat_Set (b_user_id NUMBER, b_organization_id NUMBER,
92: b_changed VARCHAR2, b_last_run_date DATE, b_category_set_id NUMBER)
93: IS
94: SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
95: FROM mtl_system_items_b msi
96: WHERE NOT EXISTS
97: (SELECT 1
98: FROM csm_mtl_system_items_acc acc
99: WHERE user_id = b_user_id

Line 98: FROM csm_mtl_system_items_acc acc

94: SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
95: FROM mtl_system_items_b msi
96: WHERE NOT EXISTS
97: (SELECT 1
98: FROM csm_mtl_system_items_acc acc
99: WHERE user_id = b_user_id
100: AND acc.inventory_item_id = msi.inventory_item_id
101: AND acc.organization_id = msi.organization_id
102: )

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

114: -- Both Category and Category set are not null
115: CURSOR c_items_Cat_Set_Cat (b_user_id NUMBER, b_organization_id NUMBER,
116: b_changed VARCHAR2, b_last_run_date DATE, b_category_id NUMBER, b_category_set_id NUMBER)
117: IS
118: SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
119: FROM mtl_system_items_b msi
120: WHERE NOT EXISTS
121: (SELECT 1
122: FROM csm_mtl_system_items_acc acc

Line 119: FROM mtl_system_items_b msi

115: CURSOR c_items_Cat_Set_Cat (b_user_id NUMBER, b_organization_id NUMBER,
116: b_changed VARCHAR2, b_last_run_date DATE, b_category_id NUMBER, b_category_set_id NUMBER)
117: IS
118: SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
119: FROM mtl_system_items_b msi
120: WHERE NOT EXISTS
121: (SELECT 1
122: FROM csm_mtl_system_items_acc acc
123: WHERE user_id = b_user_id

Line 122: FROM csm_mtl_system_items_acc acc

118: SELECT csm_mtl_system_items_acc_s.NEXTVAL, inventory_item_id, organization_id, b_user_id
119: FROM mtl_system_items_b msi
120: WHERE NOT EXISTS
121: (SELECT 1
122: FROM csm_mtl_system_items_acc acc
123: WHERE user_id = b_user_id
124: AND acc.inventory_item_id = msi.inventory_item_id
125: AND acc.organization_id = msi.organization_id
126: )

Line 140: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS ',

136: )
137: );
138:
139: BEGIN
140: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS ',
141: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
142:
143: l_run_date := SYSDATE;
144:

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

137: );
138:
139: BEGIN
140: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS ',
141: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
142:
143: l_run_date := SYSDATE;
144:
145: -- since profiles are changed get all inventory items that may already exist in

Line 150: l_stmt := 'UPDATE csm_mtl_system_items_acc';

146: -- acc due to current SR's and increment counter
147: IF p_changed = 'Y' THEN
148:
149: -- changed to dynamic sql to support either category or category set
150: l_stmt := 'UPDATE csm_mtl_system_items_acc';
151: l_stmt := l_stmt || ' SET counter = counter + 1';
152: l_stmt := l_stmt || ' , last_update_date = SYSDATE';
153: l_stmt := l_stmt || ' , last_updated_by = 1';
154: l_stmt := l_stmt || ' WHERE user_id = :1';

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

184:
185: ELSE -- if p_changed = 'N', delete items that are no longer assigned to the category/category set
186: -- changed to dynamic sql to support either category or category set
187: l_stmt := NULL;
188: l_stmt := 'UPDATE csm_mtl_system_items_acc acc';
189: l_stmt := l_stmt || ' SET counter = counter - 1';
190: l_stmt := l_stmt || ' , last_update_date = SYSDATE';
191: l_stmt := l_stmt || ' , last_updated_by = 1';
192: l_stmt := l_stmt || ' WHERE user_id = :1';

Line 231: FROM csm_mtl_system_items_acc acc

227: l_tab_user_id.DELETE;
228:
229: SELECT access_id, user_id
230: BULK COLLECT INTO l_tab_access_id, l_tab_user_id
231: FROM csm_mtl_system_items_acc acc
232: WHERE acc.counter = 0;
233:
234: IF l_tab_access_id.COUNT > 0 THEN
235: -- do bulk makedirty

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

241: , P_TIMESTAMP => l_run_date
242: );
243:
244: FORALL i IN 1..l_tab_access_id.COUNT
245: DELETE FROM csm_mtl_system_items_acc WHERE access_id = l_tab_access_id(i);
246: END IF;
247: ELSE
248: -- do not decrement as category/category set profile is not changed
249: l_stmt := NULL;

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

265: EXIT WHEN l_tab_access_id.COUNT = 0;
266:
267: IF l_tab_access_id.COUNT > 0 THEN
268: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
269: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
270: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
271:
272: FORALL i IN 1..l_tab_access_id.COUNT
273: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,

Line 270: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);

266:
267: IF l_tab_access_id.COUNT > 0 THEN
268: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
269: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
270: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
271:
272: FORALL i IN 1..l_tab_access_id.COUNT
273: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
274: created_by, creation_date, last_updated_by, last_update_date, last_update_login)

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

269: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
270: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
271:
272: FORALL i IN 1..l_tab_access_id.COUNT
273: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
274: created_by, creation_date, last_updated_by, last_update_date, last_update_login)
275: VALUES (l_tab_access_id(i), p_user_id, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
276: fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
277:

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

306: EXIT WHEN l_tab_access_id.COUNT = 0;
307:
308: IF l_tab_access_id.COUNT > 0 THEN
309: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
310: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
311: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
312:
313: FORALL i IN 1..l_tab_access_id.COUNT
314: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,

Line 311: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);

307:
308: IF l_tab_access_id.COUNT > 0 THEN
309: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
310: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
311: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
312:
313: FORALL i IN 1..l_tab_access_id.COUNT
314: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
315: created_by, creation_date, last_updated_by, last_update_date, last_update_login)

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

310: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
311: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
312:
313: FORALL i IN 1..l_tab_access_id.COUNT
314: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
315: created_by, creation_date, last_updated_by, last_update_date, last_update_login)
316: VALUES (l_tab_access_id(i), p_user_id, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
317: fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
318:

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

347: EXIT WHEN l_tab_access_id.COUNT = 0;
348:
349: IF l_tab_access_id.COUNT > 0 THEN
350: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
351: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
352: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
353:
354: FORALL i IN 1..l_tab_access_id.COUNT
355: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,

Line 352: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);

348:
349: IF l_tab_access_id.COUNT > 0 THEN
350: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
351: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
352: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
353:
354: FORALL i IN 1..l_tab_access_id.COUNT
355: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
356: created_by, creation_date, last_updated_by, last_update_date, last_update_login)

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

351: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
352: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
353:
354: FORALL i IN 1..l_tab_access_id.COUNT
355: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
356: created_by, creation_date, last_updated_by, last_update_date, last_update_login)
357: VALUES (l_tab_access_id(i), p_user_id, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
358: fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
359:

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

388: EXIT WHEN l_tab_access_id.COUNT = 0;
389:
390: IF l_tab_access_id.COUNT > 0 THEN
391: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
392: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
393: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
394:
395: FORALL i IN 1..l_tab_access_id.COUNT
396: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,

Line 393: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);

389:
390: IF l_tab_access_id.COUNT > 0 THEN
391: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count
392: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
393: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
394:
395: FORALL i IN 1..l_tab_access_id.COUNT
396: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
397: created_by, creation_date, last_updated_by, last_update_date, last_update_login)

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

392: || ' records into csm_mtl_system_items_acc for user ' || p_user_id ,
393: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
394:
395: FORALL i IN 1..l_tab_access_id.COUNT
396: INSERT INTO csm_mtl_system_items_acc(access_id, user_id, inventory_item_id, organization_id, counter,
397: created_by, creation_date, last_updated_by, last_update_date, last_update_login)
398: VALUES (l_tab_access_id(i), p_user_id, l_inventory_item_id_tbl(i), l_organization_id_tbl(i), 1,
399: fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
400:

Line 418: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS ',

414: CLOSE c_items_Cat_Set_Cat;
415:
416: END IF;
417:
418: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS ',
419: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
420: EXCEPTION
421: WHEN others THEN
422: l_sqlerrno := TO_CHAR(SQLCODE);

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

415:
416: END IF;
417:
418: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS ',
419: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
420: EXCEPTION
421: WHEN others THEN
422: l_sqlerrno := TO_CHAR(SQLCODE);
423: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);

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

420: EXCEPTION
421: WHEN others THEN
422: l_sqlerrno := TO_CHAR(SQLCODE);
423: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
424: l_error_msg := ' Exception in INSERT_MTL_SYSTEM_ITEMS for user_id :'
425: || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
426: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
427: RAISE;
428: END INSERT_MTL_SYSTEM_ITEMS;

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

422: l_sqlerrno := TO_CHAR(SQLCODE);
423: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
424: l_error_msg := ' Exception in INSERT_MTL_SYSTEM_ITEMS for user_id :'
425: || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
426: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
427: RAISE;
428: END INSERT_MTL_SYSTEM_ITEMS;
429:
430: PROCEDURE update_mtl_system_items(p_last_run_date IN DATE)

Line 428: END INSERT_MTL_SYSTEM_ITEMS;

424: l_error_msg := ' Exception in INSERT_MTL_SYSTEM_ITEMS for user_id :'
425: || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
426: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
427: RAISE;
428: END INSERT_MTL_SYSTEM_ITEMS;
429:
430: PROCEDURE update_mtl_system_items(p_last_run_date IN DATE)
431: IS
432: l_sqlerrno VARCHAR2(20);

Line 430: PROCEDURE update_mtl_system_items(p_last_run_date IN DATE)

426: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.INSERT_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
427: RAISE;
428: END INSERT_MTL_SYSTEM_ITEMS;
429:
430: PROCEDURE update_mtl_system_items(p_last_run_date IN DATE)
431: IS
432: l_sqlerrno VARCHAR2(20);
433: l_sqlerrmsg VARCHAR2(4000);
434: l_error_msg VARCHAR2(4000);

Line 445: SELECT /+ INDEX (acc CSM_MTL_SYSTEM_ITEMS_ACC_U1) INDEX(msi MTL_SYSTEM_ITEMS_B_U1) /

441: l_max_last_update_date_tl DATE;
442:
443: CURSOR c_changed( b_last_date DATE)
444: IS
445: SELECT /*+ INDEX (acc CSM_MTL_SYSTEM_ITEMS_ACC_U1) INDEX(msi MTL_SYSTEM_ITEMS_B_U1) */
446: acc.access_id, acc.user_id
447: FROM csm_mtl_system_items_acc acc, mtl_system_items_b msi
448: WHERE msi.inventory_item_id = acc.inventory_item_id
449: AND msi.organization_id = acc.organization_id

Line 447: FROM csm_mtl_system_items_acc acc, mtl_system_items_b msi

443: CURSOR c_changed( b_last_date DATE)
444: IS
445: SELECT /*+ INDEX (acc CSM_MTL_SYSTEM_ITEMS_ACC_U1) INDEX(msi MTL_SYSTEM_ITEMS_B_U1) */
446: acc.access_id, acc.user_id
447: FROM csm_mtl_system_items_acc acc, mtl_system_items_b msi
448: WHERE msi.inventory_item_id = acc.inventory_item_id
449: AND msi.organization_id = acc.organization_id
450: AND (msi.last_update_date >= b_last_date);
451:

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

449: AND msi.organization_id = acc.organization_id
450: AND (msi.last_update_date >= b_last_date);
451:
452: BEGIN
453: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS ',
454: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
455:
456: l_run_date := SYSDATE;
457:

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

450: AND (msi.last_update_date >= b_last_date);
451:
452: BEGIN
453: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS ',
454: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
455:
456: l_run_date := SYSDATE;
457:
458: /* This portion of code assumes indexes on last_update_date on MTL_SYSTEM_ITEMS_B */

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

454: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
455:
456: l_run_date := SYSDATE;
457:
458: /* This portion of code assumes indexes on last_update_date on MTL_SYSTEM_ITEMS_B */
459: /* , MTL_SYSTEM_ITEMS_TL which were custom created */
460: SELECT MAX(last_update_date) INTO l_max_last_update_date_b
461: FROM mtl_system_items_b;
462: IF( l_max_last_update_date_b < p_last_run_date) THEN

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

455:
456: l_run_date := SYSDATE;
457:
458: /* This portion of code assumes indexes on last_update_date on MTL_SYSTEM_ITEMS_B */
459: /* , MTL_SYSTEM_ITEMS_TL which were custom created */
460: SELECT MAX(last_update_date) INTO l_max_last_update_date_b
461: FROM mtl_system_items_b;
462: IF( l_max_last_update_date_b < p_last_run_date) THEN
463: SELECT MAX(last_update_date) INTO l_max_last_update_date_tl

Line 461: FROM mtl_system_items_b;

457:
458: /* This portion of code assumes indexes on last_update_date on MTL_SYSTEM_ITEMS_B */
459: /* , MTL_SYSTEM_ITEMS_TL which were custom created */
460: SELECT MAX(last_update_date) INTO l_max_last_update_date_b
461: FROM mtl_system_items_b;
462: IF( l_max_last_update_date_b < p_last_run_date) THEN
463: SELECT MAX(last_update_date) INTO l_max_last_update_date_tl
464: FROM mtl_system_items_tl;
465: IF(l_max_last_update_date_tl < p_last_run_date) THEN

Line 464: FROM mtl_system_items_tl;

460: SELECT MAX(last_update_date) INTO l_max_last_update_date_b
461: FROM mtl_system_items_b;
462: IF( l_max_last_update_date_b < p_last_run_date) THEN
463: SELECT MAX(last_update_date) INTO l_max_last_update_date_tl
464: FROM mtl_system_items_tl;
465: IF(l_max_last_update_date_tl < p_last_run_date) THEN
466: -- No updates
467: CSM_UTIL_PKG.LOG('Leaving UPDATE_MTL_SYSTEM_ITEMS - No Updates ',
468: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);

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

463: SELECT MAX(last_update_date) INTO l_max_last_update_date_tl
464: FROM mtl_system_items_tl;
465: IF(l_max_last_update_date_tl < p_last_run_date) THEN
466: -- No updates
467: CSM_UTIL_PKG.LOG('Leaving UPDATE_MTL_SYSTEM_ITEMS - No Updates ',
468: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
469: RETURN;
470: END IF;
471: END IF;

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

464: FROM mtl_system_items_tl;
465: IF(l_max_last_update_date_tl < p_last_run_date) THEN
466: -- No updates
467: CSM_UTIL_PKG.LOG('Leaving UPDATE_MTL_SYSTEM_ITEMS - No Updates ',
468: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
469: RETURN;
470: END IF;
471: END IF;
472:

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

478: FETCH c_changed BULK COLLECT INTO l_tab_access_id, l_tab_user_id LIMIT 200;
479: EXIT WHEN l_tab_access_id.COUNT = 0;
480:
481: IF l_tab_access_id.COUNT > 0 THEN
482: CSM_UTIL_PKG.LOG(l_tab_access_id.COUNT || ' records sent to olite for updating csm_mtl_system_items',
483: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
484:
485: -- do bulk makedirty
486: l_markdirty := asg_download.mark_dirty(

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

479: EXIT WHEN l_tab_access_id.COUNT = 0;
480:
481: IF l_tab_access_id.COUNT > 0 THEN
482: CSM_UTIL_PKG.LOG(l_tab_access_id.COUNT || ' records sent to olite for updating csm_mtl_system_items',
483: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EVENT);
484:
485: -- do bulk makedirty
486: l_markdirty := asg_download.mark_dirty(
487: P_PUB_ITEM => g_pub_item

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

493: END IF;
494: END LOOP;
495: CLOSE c_changed;
496:
497: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS ',
498: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
499: EXCEPTION
500: WHEN OTHERS THEN
501: l_sqlerrno := TO_CHAR(SQLCODE);

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

494: END LOOP;
495: CLOSE c_changed;
496:
497: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS ',
498: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
499: EXCEPTION
500: WHEN OTHERS THEN
501: l_sqlerrno := TO_CHAR(SQLCODE);
502: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);

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

499: EXCEPTION
500: WHEN OTHERS THEN
501: l_sqlerrno := TO_CHAR(SQLCODE);
502: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
503: l_error_msg := ' Exception in UPDATE_MTL_SYSTEM_ITEMS :' || l_sqlerrno || ':' || l_sqlerrmsg;
504: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
505: RAISE;
506: END UPDATE_MTL_SYSTEM_ITEMS;
507:

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

500: WHEN OTHERS THEN
501: l_sqlerrno := TO_CHAR(SQLCODE);
502: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
503: l_error_msg := ' Exception in UPDATE_MTL_SYSTEM_ITEMS :' || l_sqlerrno || ':' || l_sqlerrmsg;
504: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
505: RAISE;
506: END UPDATE_MTL_SYSTEM_ITEMS;
507:
508: PROCEDURE delete_mtl_system_items(p_user_id IN NUMBER,

Line 506: END UPDATE_MTL_SYSTEM_ITEMS;

502: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
503: l_error_msg := ' Exception in UPDATE_MTL_SYSTEM_ITEMS :' || l_sqlerrno || ':' || l_sqlerrmsg;
504: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
505: RAISE;
506: END UPDATE_MTL_SYSTEM_ITEMS;
507:
508: PROCEDURE delete_mtl_system_items(p_user_id IN NUMBER,
509: p_organization_id IN NUMBER,
510: p_category_set_id IN NUMBER,

Line 508: PROCEDURE delete_mtl_system_items(p_user_id IN NUMBER,

504: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.UPDATE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
505: RAISE;
506: END UPDATE_MTL_SYSTEM_ITEMS;
507:
508: PROCEDURE delete_mtl_system_items(p_user_id IN NUMBER,
509: p_organization_id IN NUMBER,
510: p_category_set_id IN NUMBER,
511: p_category_id IN NUMBER)
512: IS

Line 525: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS ',

521: l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
522: l_tab_user_id ASG_DOWNLOAD.USER_LIST;
523:
524: BEGIN
525: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS ',
526: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
527:
528: l_run_date := SYSDATE;
529:

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

522: l_tab_user_id ASG_DOWNLOAD.USER_LIST;
523:
524: BEGIN
525: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS ',
526: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
527:
528: l_run_date := SYSDATE;
529:
530: l_tab_access_id.DELETE;

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

528: l_run_date := SYSDATE;
529:
530: l_tab_access_id.DELETE;
531:
532: l_stmt := 'UPDATE csm_mtl_system_items_acc acc';
533: l_stmt := l_stmt || ' SET COUNTER = COUNTER - 1';
534: l_stmt := l_stmt || ' , LAST_UPDATE_DATE = SYSDATE';
535: l_stmt := l_stmt || ' , last_updated_by = nvl(fnd_global.user_id, 1)';
536: l_stmt := l_stmt || ' WHERE USER_ID = :1';

Line 573: FROM csm_mtl_system_items_acc acc

569: l_tab_user_id.DELETE;
570:
571: SELECT access_id, user_id
572: BULK COLLECT INTO l_tab_access_id, l_tab_user_id
573: FROM csm_mtl_system_items_acc acc
574: WHERE acc.counter = 0;
575:
576: IF l_tab_access_id.COUNT > 0 THEN
577: -- do bulk makedirty

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

583: , P_TIMESTAMP => l_run_date
584: );
585:
586: FORALL i IN 1..l_tab_access_id.COUNT
587: DELETE FROM csm_mtl_system_items_acc WHERE access_id = l_tab_access_id(i);
588: END IF;
589:
590: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS ',
591: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);

Line 590: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS ',

586: FORALL i IN 1..l_tab_access_id.COUNT
587: DELETE FROM csm_mtl_system_items_acc WHERE access_id = l_tab_access_id(i);
588: END IF;
589:
590: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS ',
591: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
592: EXCEPTION
593: WHEN others THEN
594: l_sqlerrno := TO_CHAR(SQLCODE);

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

587: DELETE FROM csm_mtl_system_items_acc WHERE access_id = l_tab_access_id(i);
588: END IF;
589:
590: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS ',
591: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_PROCEDURE);
592: EXCEPTION
593: WHEN others THEN
594: l_sqlerrno := TO_CHAR(SQLCODE);
595: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);

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

592: EXCEPTION
593: WHEN others THEN
594: l_sqlerrno := TO_CHAR(SQLCODE);
595: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
596: CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
597: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
598: RAISE;
599: END DELETE_MTL_SYSTEM_ITEMS;
600:

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

593: WHEN others THEN
594: l_sqlerrno := TO_CHAR(SQLCODE);
595: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
596: CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
597: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
598: RAISE;
599: END DELETE_MTL_SYSTEM_ITEMS;
600:
601: PROCEDURE concurrent_process_user(p_user_id IN NUMBER,

Line 599: END DELETE_MTL_SYSTEM_ITEMS;

595: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
596: CSM_UTIL_PKG.LOG('Exception in CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS: ' || l_sqlerrno || ':' || l_sqlerrmsg,
597: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.DELETE_MTL_SYSTEM_ITEMS',FND_LOG.LEVEL_EXCEPTION);
598: RAISE;
599: END DELETE_MTL_SYSTEM_ITEMS;
600:
601: PROCEDURE concurrent_process_user(p_user_id IN NUMBER,
602: p_organization_id IN NUMBER,
603: p_category_set_id IN NUMBER,

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

622: r_org c_org%ROWTYPE;
623:
624: BEGIN
625: CSM_UTIL_PKG.LOG('Entering CONCURRENT_PROCESS_USER for user_id: ' || TO_CHAR(p_user_id),
626: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
627:
628: /* Get old profile settings */
629: OPEN c_org( p_user_id );
630: FETCH c_org INTO r_org;

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

629: OPEN c_org( p_user_id );
630: FETCH c_org INTO r_org;
631: IF c_org%NOTFOUND THEN -- should not occur
632: CSM_UTIL_PKG.LOG('Profile record not found in csm_user_inventory_org for user_id: ' || TO_CHAR(p_user_id)
633: || ' - Inserting all mtl_system_items',
634: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
635: get_new_user_mtl_system_items(p_user_id=>p_user_id, p_organization_id=>p_organization_id,
636: p_category_set_id=>p_category_set_id, p_category_id=>p_category_id);
637:

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

630: FETCH c_org INTO r_org;
631: IF c_org%NOTFOUND THEN -- should not occur
632: CSM_UTIL_PKG.LOG('Profile record not found in csm_user_inventory_org for user_id: ' || TO_CHAR(p_user_id)
633: || ' - Inserting all mtl_system_items',
634: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
635: get_new_user_mtl_system_items(p_user_id=>p_user_id, p_organization_id=>p_organization_id,
636: p_category_set_id=>p_category_set_id, p_category_id=>p_category_id);
637:
638: INSERT INTO csm_user_inventory_org (

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

631: IF c_org%NOTFOUND THEN -- should not occur
632: CSM_UTIL_PKG.LOG('Profile record not found in csm_user_inventory_org for user_id: ' || TO_CHAR(p_user_id)
633: || ' - Inserting all mtl_system_items',
634: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
635: get_new_user_mtl_system_items(p_user_id=>p_user_id, p_organization_id=>p_organization_id,
636: p_category_set_id=>p_category_set_id, p_category_id=>p_category_id);
637:
638: INSERT INTO csm_user_inventory_org (
639: user_id, organization_id, last_update_date, last_updated_by,

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

674:
675: IF NVL(p_organization_id, -1) <> NVL(r_org.organization_id, -1)
676: OR l_cat_filter_changed THEN
677: CSM_UTIL_PKG.LOG('Deleting records for old profile settings for user_id: ' || TO_CHAR(p_user_id),
678: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
679:
680: -- delete labor/expense items only and then re-insert for the new orgif org changes
681: IF NVL(p_organization_id, -1) <> NVL(r_org.organization_id,-1) THEN
682: csm_system_item_event_pkg.delete_system_items(p_user_id=>p_user_id,

Line 689: delete_mtl_system_items(p_user_id=>p_user_id,

685: -- download new labor/expense items for the new org
686: csm_system_item_event_pkg.get_new_user_system_items(p_user_id=>p_user_id);
687: END IF;
688:
689: delete_mtl_system_items(p_user_id=>p_user_id,
690: p_organization_id=>r_org.organization_id,
691: p_category_set_id=>r_org.category_set_id,
692: p_category_id=>r_org.category_id);
693:

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

691: p_category_set_id=>r_org.category_set_id,
692: p_category_id=>r_org.category_id);
693:
694: CSM_UTIL_PKG.LOG('Inserting records for new profile settings for user_id: ' || TO_CHAR(p_user_id),
695: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
696:
697: insert_mtl_system_items(p_user_id=>p_user_id,
698: p_organization_id=>p_organization_id,
699: p_category_set_id=>p_category_set_id,

Line 697: insert_mtl_system_items(p_user_id=>p_user_id,

693:
694: CSM_UTIL_PKG.LOG('Inserting records for new profile settings for user_id: ' || TO_CHAR(p_user_id),
695: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
696:
697: insert_mtl_system_items(p_user_id=>p_user_id,
698: p_organization_id=>p_organization_id,
699: p_category_set_id=>p_category_set_id,
700: p_category_id=>p_category_id,
701: p_last_run_date=>NULL,

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

701: p_last_run_date=>NULL,
702: p_changed=>'Y');
703:
704: CSM_UTIL_PKG.LOG('Update csm_user_inventory_org with new profile settings for user_id: ' || TO_CHAR(p_user_id),
705: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
706:
707: UPDATE csm_user_inventory_org
708: SET organization_id = p_organization_id
709: , category_set_id = p_category_set_id

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

714: ELSE
715: -- profiles are the same
716: -- get any new changes
717: CSM_UTIL_PKG.LOG('Getting new items for same profile settings for user_id: ' || TO_CHAR(p_user_id),
718: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
719:
720: insert_mtl_system_items(p_user_id=>p_user_id,
721: p_organization_id=>p_organization_id,
722: p_category_set_id=>p_category_set_id,

Line 720: insert_mtl_system_items(p_user_id=>p_user_id,

716: -- get any new changes
717: CSM_UTIL_PKG.LOG('Getting new items for same profile settings for user_id: ' || TO_CHAR(p_user_id),
718: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
719:
720: insert_mtl_system_items(p_user_id=>p_user_id,
721: p_organization_id=>p_organization_id,
722: p_category_set_id=>p_category_set_id,
723: p_category_id=>p_category_id,
724: p_last_run_date=>p_last_run_date,

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

727: END IF;
728: CLOSE c_org;
729:
730: CSM_UTIL_PKG.LOG('Leaving CONCURRENT_PROCESS_USER for user_id: ' || TO_CHAR(p_user_id),
731: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.CONCURRENT_PROCESS_USER',FND_LOG.LEVEL_PROCEDURE);
732: EXCEPTION
733: WHEN OTHERS THEN
734: l_sqlerrno := TO_CHAR(SQLCODE);
735: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);

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

734: l_sqlerrno := TO_CHAR(SQLCODE);
735: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
736: l_error_msg := ' Exception in concurrent_process_user for user_id :'
737: || TO_CHAR(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
738: CSM_UTIL_PKG.LOG(l_error_msg, 'csm_mtl_system_items_event_pkg.concurrent_process_user',FND_LOG.LEVEL_EXCEPTION);
739: RAISE;
740: END CONCURRENT_PROCESS_USER;
741:
742: PROCEDURE Refresh_mtl_system_items_acc(p_status OUT NOCOPY VARCHAR2,

Line 742: PROCEDURE Refresh_mtl_system_items_acc(p_status OUT NOCOPY VARCHAR2,

738: CSM_UTIL_PKG.LOG(l_error_msg, 'csm_mtl_system_items_event_pkg.concurrent_process_user',FND_LOG.LEVEL_EXCEPTION);
739: RAISE;
740: END CONCURRENT_PROCESS_USER;
741:
742: PROCEDURE Refresh_mtl_system_items_acc(p_status OUT NOCOPY VARCHAR2,
743: p_message OUT NOCOPY VARCHAR2)
744: IS
745: PRAGMA AUTONOMOUS_TRANSACTION;
746: l_prog_update_date jtm_con_request_data.last_run_date%TYPE;

Line 750: l_user_organization_id mtl_system_items.organization_id%TYPE;

746: l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
747: l_all_omfs_user_list asg_download.user_list;
748: l_null_omfs_user_list asg_download.user_list;
749: l_user_id fnd_user.user_id%TYPE;
750: l_user_organization_id mtl_system_items.organization_id%TYPE;
751: l_user_category_set_id mtl_category_sets.category_set_id%TYPE;
752: l_user_category_id mtl_categories.category_id%TYPE;
753: l_run_date DATE;
754: l_sqlerrno VARCHAR2(20);

Line 761: WHERE package_name = 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG'

757: CURSOR l_last_run_date_csr
758: IS
759: SELECT NVL(last_run_date, TO_DATE('1','J'))
760: FROM jtm_con_request_data
761: WHERE package_name = 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG'
762: AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';
763:
764: BEGIN
765: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.Refresh_mtl_system_items_acc ',

Line 762: AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';

758: IS
759: SELECT NVL(last_run_date, TO_DATE('1','J'))
760: FROM jtm_con_request_data
761: WHERE package_name = 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG'
762: AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';
763:
764: BEGIN
765: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.Refresh_mtl_system_items_acc ',
766: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.Refresh_mtl_system_items_acc',FND_LOG.LEVEL_PROCEDURE);

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

761: WHERE package_name = 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG'
762: AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';
763:
764: BEGIN
765: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.Refresh_mtl_system_items_acc ',
766: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.Refresh_mtl_system_items_acc',FND_LOG.LEVEL_PROCEDURE);
767:
768: l_run_date := SYSDATE;
769:

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

762: AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';
763:
764: BEGIN
765: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.Refresh_mtl_system_items_acc ',
766: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.Refresh_mtl_system_items_acc',FND_LOG.LEVEL_PROCEDURE);
767:
768: l_run_date := SYSDATE;
769:
770: -- get last conc program update date

Line 776: update_mtl_system_items(p_last_run_date => l_prog_update_date);

772: FETCH l_last_run_date_csr INTO l_prog_update_date;
773: CLOSE l_last_run_date_csr;
774:
775: -- do an update for existing records
776: update_mtl_system_items(p_last_run_date => l_prog_update_date);
777: COMMIT;
778:
779: -- get user list of all omfs users
780: l_all_omfs_user_list := l_null_omfs_user_list;

Line 800: WHERE package_name = 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG'

796:
797: -- update last_run_date
798: UPDATE jtm_con_request_data
799: SET last_run_date = l_run_date
800: WHERE package_name = 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG'
801: AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';
802:
803: COMMIT;
804:

Line 801: AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';

797: -- update last_run_date
798: UPDATE jtm_con_request_data
799: SET last_run_date = l_run_date
800: WHERE package_name = 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG'
801: AND procedure_name = 'REFRESH_MTL_SYSTEM_ITEMS_ACC';
802:
803: COMMIT;
804:
805: p_status := 'FINE';

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

802:
803: COMMIT;
804:
805: p_status := 'FINE';
806: p_message := 'csm_mtl_system_items_event_pkg.refresh_system_items executed successfully';
807:
808: CSM_UTIL_PKG.LOG('Leaving csm_mtl_system_items_event_pkg.Refresh_mtl_system_items_acc ',
809: 'csm_mtl_system_items_event_pkg.Refresh_mtl_system_items_acc',FND_LOG.LEVEL_PROCEDURE);
810: EXCEPTION

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

804:
805: p_status := 'FINE';
806: p_message := 'csm_mtl_system_items_event_pkg.refresh_system_items executed successfully';
807:
808: CSM_UTIL_PKG.LOG('Leaving csm_mtl_system_items_event_pkg.Refresh_mtl_system_items_acc ',
809: 'csm_mtl_system_items_event_pkg.Refresh_mtl_system_items_acc',FND_LOG.LEVEL_PROCEDURE);
810: EXCEPTION
811: WHEN others THEN
812: l_sqlerrno := TO_CHAR(SQLCODE);

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

805: p_status := 'FINE';
806: p_message := 'csm_mtl_system_items_event_pkg.refresh_system_items executed successfully';
807:
808: CSM_UTIL_PKG.LOG('Leaving csm_mtl_system_items_event_pkg.Refresh_mtl_system_items_acc ',
809: 'csm_mtl_system_items_event_pkg.Refresh_mtl_system_items_acc',FND_LOG.LEVEL_PROCEDURE);
810: EXCEPTION
811: WHEN others THEN
812: l_sqlerrno := TO_CHAR(SQLCODE);
813: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);

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

811: WHEN others THEN
812: l_sqlerrno := TO_CHAR(SQLCODE);
813: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
814: p_status := 'ERROR';
815: p_message := 'Error in CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.REFRESH_MTL_SYSTEM_ITEMS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg;
816: ROLLBACK;
817: csm_util_pkg.log('CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.REFRESH_MTL_SYSTEM_ITEMS_ACC ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
818: END REFRESH_MTL_SYSTEM_ITEMS_ACC;
819:

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

813: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
814: p_status := 'ERROR';
815: p_message := 'Error in CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.REFRESH_MTL_SYSTEM_ITEMS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg;
816: ROLLBACK;
817: csm_util_pkg.log('CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.REFRESH_MTL_SYSTEM_ITEMS_ACC ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
818: END REFRESH_MTL_SYSTEM_ITEMS_ACC;
819:
820: PROCEDURE get_new_user_mtl_system_items(p_user_id IN NUMBER, p_organization_id IN NUMBER,
821: p_category_set_id IN NUMBER, p_category_id IN NUMBER)

Line 818: END REFRESH_MTL_SYSTEM_ITEMS_ACC;

814: p_status := 'ERROR';
815: p_message := 'Error in CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.REFRESH_MTL_SYSTEM_ITEMS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg;
816: ROLLBACK;
817: csm_util_pkg.log('CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.REFRESH_MTL_SYSTEM_ITEMS_ACC ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
818: END REFRESH_MTL_SYSTEM_ITEMS_ACC;
819:
820: PROCEDURE get_new_user_mtl_system_items(p_user_id IN NUMBER, p_organization_id IN NUMBER,
821: p_category_set_id IN NUMBER, p_category_id IN NUMBER)
822: IS

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

816: ROLLBACK;
817: csm_util_pkg.log('CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.REFRESH_MTL_SYSTEM_ITEMS_ACC ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
818: END REFRESH_MTL_SYSTEM_ITEMS_ACC;
819:
820: PROCEDURE get_new_user_mtl_system_items(p_user_id IN NUMBER, p_organization_id IN NUMBER,
821: p_category_set_id IN NUMBER, p_category_id IN NUMBER)
822: IS
823: l_sqlerrno VARCHAR2(20);
824: l_sqlerrmsg VARCHAR2(4000);

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

825: l_error_msg VARCHAR2(4000);
826: l_return_status VARCHAR2(2000);
827:
828: BEGIN
829: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items ',
830: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_PROCEDURE);
831:
832: insert_mtl_system_items(p_user_id=>p_user_id,
833: p_organization_id=>p_organization_id,

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

826: l_return_status VARCHAR2(2000);
827:
828: BEGIN
829: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items ',
830: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_PROCEDURE);
831:
832: insert_mtl_system_items(p_user_id=>p_user_id,
833: p_organization_id=>p_organization_id,
834: p_category_set_id=>p_category_set_id,

Line 832: insert_mtl_system_items(p_user_id=>p_user_id,

828: BEGIN
829: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items ',
830: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_PROCEDURE);
831:
832: insert_mtl_system_items(p_user_id=>p_user_id,
833: p_organization_id=>p_organization_id,
834: p_category_set_id=>p_category_set_id,
835: p_category_id=>p_category_id,
836: p_last_run_date=>NULL,

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

835: p_category_id=>p_category_id,
836: p_last_run_date=>NULL,
837: p_changed=>'N'); -- new user, no profiles are changed
838:
839: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items ',
840: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_PROCEDURE);
841:
842: EXCEPTION
843: WHEN others THEN

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

836: p_last_run_date=>NULL,
837: p_changed=>'N'); -- new user, no profiles are changed
838:
839: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items ',
840: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_PROCEDURE);
841:
842: EXCEPTION
843: WHEN others THEN
844: l_sqlerrno := TO_CHAR(SQLCODE);

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

842: EXCEPTION
843: WHEN others THEN
844: l_sqlerrno := TO_CHAR(SQLCODE);
845: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
846: l_error_msg := ' Exception in get_new_user_mtl_system_items for user_id :'
847: || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
848: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_EXCEPTION);
849: RAISE;
850: END get_new_user_mtl_system_items;

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

844: l_sqlerrno := TO_CHAR(SQLCODE);
845: l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
846: l_error_msg := ' Exception in get_new_user_mtl_system_items for user_id :'
847: || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
848: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_EXCEPTION);
849: RAISE;
850: END get_new_user_mtl_system_items;
851:
852: PROCEDURE mtl_system_items_acc_i(p_inventory_item_id IN NUMBER,

Line 850: END get_new_user_mtl_system_items;

846: l_error_msg := ' Exception in get_new_user_mtl_system_items for user_id :'
847: || to_char(p_user_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
848: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_EXCEPTION);
849: RAISE;
850: END get_new_user_mtl_system_items;
851:
852: PROCEDURE mtl_system_items_acc_i(p_inventory_item_id IN NUMBER,
853: p_organization_id IN NUMBER,
854: p_user_id IN NUMBER,

Line 852: PROCEDURE mtl_system_items_acc_i(p_inventory_item_id IN NUMBER,

848: CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.get_new_user_mtl_system_items',FND_LOG.LEVEL_EXCEPTION);
849: RAISE;
850: END get_new_user_mtl_system_items;
851:
852: PROCEDURE mtl_system_items_acc_i(p_inventory_item_id IN NUMBER,
853: p_organization_id IN NUMBER,
854: p_user_id IN NUMBER,
855: p_error_msg OUT NOCOPY VARCHAR2,
856: x_return_status IN OUT NOCOPY VARCHAR2)

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

856: x_return_status IN OUT NOCOPY VARCHAR2)
857: IS
858: BEGIN
859: x_return_status := FND_API.G_RET_STS_SUCCESS;
860: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I ',
861: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
862:
863: IF p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL THEN
864: CSM_ACC_PKG.Insert_Acc

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

857: IS
858: BEGIN
859: x_return_status := FND_API.G_RET_STS_SUCCESS;
860: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I ',
861: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
862:
863: IF p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL THEN
864: CSM_ACC_PKG.Insert_Acc
865: ( P_PUBLICATION_ITEM_NAMES => g_mtl_sys_items_pubi_name

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

874:
875: END IF;
876:
877: p_error_msg := 'SUCCESS';
878: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I ',
879: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
880: EXCEPTION
881: WHEN others THEN
882: x_return_status := FND_API.G_RET_STS_ERROR;

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

875: END IF;
876:
877: p_error_msg := 'SUCCESS';
878: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I ',
879: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
880: EXCEPTION
881: WHEN others THEN
882: x_return_status := FND_API.G_RET_STS_ERROR;
883: p_error_msg := ' FAILED MTL_SYSTEM_ITEMS_ACC_I INVENTORY_ITEM_ID: ' || to_char(p_inventory_item_id) || SUBSTR(SQLERRM,1,2000);

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

879: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
880: EXCEPTION
881: WHEN others THEN
882: x_return_status := FND_API.G_RET_STS_ERROR;
883: p_error_msg := ' FAILED MTL_SYSTEM_ITEMS_ACC_I INVENTORY_ITEM_ID: ' || to_char(p_inventory_item_id) || SUBSTR(SQLERRM,1,2000);
884: CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_EXCEPTION);
885: RAISE;
886: END MTL_SYSTEM_ITEMS_ACC_I;
887:

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

880: EXCEPTION
881: WHEN others THEN
882: x_return_status := FND_API.G_RET_STS_ERROR;
883: p_error_msg := ' FAILED MTL_SYSTEM_ITEMS_ACC_I INVENTORY_ITEM_ID: ' || to_char(p_inventory_item_id) || SUBSTR(SQLERRM,1,2000);
884: CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_EXCEPTION);
885: RAISE;
886: END MTL_SYSTEM_ITEMS_ACC_I;
887:
888: PROCEDURE mtl_system_items_acc_d(p_inventory_item_id IN NUMBER,

Line 886: END MTL_SYSTEM_ITEMS_ACC_I;

882: x_return_status := FND_API.G_RET_STS_ERROR;
883: p_error_msg := ' FAILED MTL_SYSTEM_ITEMS_ACC_I INVENTORY_ITEM_ID: ' || to_char(p_inventory_item_id) || SUBSTR(SQLERRM,1,2000);
884: CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_EXCEPTION);
885: RAISE;
886: END MTL_SYSTEM_ITEMS_ACC_I;
887:
888: PROCEDURE mtl_system_items_acc_d(p_inventory_item_id IN NUMBER,
889: p_organization_id IN NUMBER,
890: p_user_id IN NUMBER,

Line 888: PROCEDURE mtl_system_items_acc_d(p_inventory_item_id IN NUMBER,

884: CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_I',FND_LOG.LEVEL_EXCEPTION);
885: RAISE;
886: END MTL_SYSTEM_ITEMS_ACC_I;
887:
888: PROCEDURE mtl_system_items_acc_d(p_inventory_item_id IN NUMBER,
889: p_organization_id IN NUMBER,
890: p_user_id IN NUMBER,
891: p_error_msg OUT NOCOPY VARCHAR2,
892: x_return_status IN OUT NOCOPY VARCHAR2)

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

892: x_return_status IN OUT NOCOPY VARCHAR2)
893: IS
894: BEGIN
895: x_return_status := FND_API.G_RET_STS_SUCCESS;
896: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',
897: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
898:
899: IF p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL THEN
900: CSM_ACC_PKG.Delete_Acc

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

893: IS
894: BEGIN
895: x_return_status := FND_API.G_RET_STS_SUCCESS;
896: CSM_UTIL_PKG.LOG('Entering CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',
897: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
898:
899: IF p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL THEN
900: CSM_ACC_PKG.Delete_Acc
901: ( P_PUBLICATION_ITEM_NAMES => g_mtl_sys_items_pubi_name

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

908: );
909: END IF;
910:
911: p_error_msg := 'SUCCESS';
912: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D ',
913: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
914: EXCEPTION
915: WHEN others THEN
916: x_return_status := FND_API.G_RET_STS_ERROR;

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

909: END IF;
910:
911: p_error_msg := 'SUCCESS';
912: CSM_UTIL_PKG.LOG('Leaving CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D ',
913: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
914: EXCEPTION
915: WHEN others THEN
916: x_return_status := FND_API.G_RET_STS_ERROR;
917: p_error_msg := ' FAILED MTL_SYSTEM_ITEMS_ACC_D INVENTORY_ITEM_ID: ' || to_char(p_inventory_item_id) || SUBSTR(SQLERRM,1,2000);

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

913: 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
914: EXCEPTION
915: WHEN others THEN
916: x_return_status := FND_API.G_RET_STS_ERROR;
917: p_error_msg := ' FAILED MTL_SYSTEM_ITEMS_ACC_D INVENTORY_ITEM_ID: ' || to_char(p_inventory_item_id) || SUBSTR(SQLERRM,1,2000);
918: CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_EXCEPTION);
919: RAISE;
920: END MTL_SYSTEM_ITEMS_ACC_D;
921:

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

914: EXCEPTION
915: WHEN others THEN
916: x_return_status := FND_API.G_RET_STS_ERROR;
917: p_error_msg := ' FAILED MTL_SYSTEM_ITEMS_ACC_D INVENTORY_ITEM_ID: ' || to_char(p_inventory_item_id) || SUBSTR(SQLERRM,1,2000);
918: CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_EXCEPTION);
919: RAISE;
920: END MTL_SYSTEM_ITEMS_ACC_D;
921:
922: END CSM_MTL_SYSTEM_ITEMS_EVENT_PKG;

Line 920: END MTL_SYSTEM_ITEMS_ACC_D;

916: x_return_status := FND_API.G_RET_STS_ERROR;
917: p_error_msg := ' FAILED MTL_SYSTEM_ITEMS_ACC_D INVENTORY_ITEM_ID: ' || to_char(p_inventory_item_id) || SUBSTR(SQLERRM,1,2000);
918: CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_EXCEPTION);
919: RAISE;
920: END MTL_SYSTEM_ITEMS_ACC_D;
921:
922: END CSM_MTL_SYSTEM_ITEMS_EVENT_PKG;

Line 922: END CSM_MTL_SYSTEM_ITEMS_EVENT_PKG;

918: CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_MTL_SYSTEM_ITEMS_EVENT_PKG.MTL_SYSTEM_ITEMS_ACC_D',FND_LOG.LEVEL_EXCEPTION);
919: RAISE;
920: END MTL_SYSTEM_ITEMS_ACC_D;
921:
922: END CSM_MTL_SYSTEM_ITEMS_EVENT_PKG;