[Home] [Help]
PACKAGE BODY: APPS.CSM_MTL_ITEM_SUBINV_EVENT_PKG
Source
1 PACKAGE BODY CSM_MTL_ITEM_SUBINV_EVENT_PKG AS
2 /* $Header: csmemisb.pls 120.1.12020000.2 2013/04/09 10:56:02 saradhak ship $*/
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Enter procedure, function bodies as shown below
13
14 g_table_name1 CONSTANT VARCHAR2(30) := 'MTL_ITEM_SUB_INVENTORIES';
15 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_SUBINV_ACC';
16 g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_SUBINV_ACC_S';
17 g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
18 CSM_ACC_PKG.t_publication_item_list('CSM_MTL_ITEM_SUBINV');
19 g_pk1_name1 CONSTANT VARCHAR2(30) := 'INVENTORY_ITEM_ID';
20 g_pk2_name1 CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
21 g_pk3_name1 CONSTANT VARCHAR2(30) := 'SECONDARY_INVENTORY';
22 g_pub_item CONSTANT VARCHAR2(30) := 'CSM_MTL_ITEM_SUBINV';
23
24 --NOTE---------------------------
25 --Changes related to Bug 12726193 is not done in the following procedures
26 --INSERT_MTL_ITEM_SUBINV
27 --DELETE_MTL_ITEM_SUBINV
28 --As these procedures are not used anywhere..only refresh_acc is touched for bug.
29 -----------------------------------------------
30 PROCEDURE INSERT_MTL_ITEM_SUBINV( p_organization_id IN number
31 , p_user_id IN number
32 , p_last_run_date IN date)
33 IS
34 TYPE inventory_item_tbl_typ IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
35 TYPE organization_tbl_typ IS TABLE OF mtl_system_items_b.organization_id%TYPE
36 INDEX BY BINARY_INTEGER;
37 TYPE secondary_inventory_typ IS TABLE OF mtl_item_sub_inventories.secondary_inventory%TYPE INDEX BY BINARY_INTEGER;
38 TYPE access_id_tbl_typ IS TABLE OF number INDEX BY binary_integer;
39 TYPE user_id_tbl_typ IS TABLE OF number INDEX BY binary_integer;
40
41 l_inventory_items_tbl inventory_item_tbl_typ;
42 l_organizations_tbl organization_tbl_typ;
43 l_sec_inventory_tbl secondary_inventory_typ;
44 l_access_id_tbl access_id_tbl_typ;
45 l_mark_dirty boolean;
46 l_run_date date;
47 l_sqlerrno varchar2(20);
48 l_sqlerrmsg varchar2(2000);
49
50 CURSOR l_ins_mtl_item_subinv_csr(p_organizationid IN number,
51 p_userid IN number,
52 p_lastrundate IN date)
53 IS
54 SELECT cila_acc.user_id, mis.inventory_item_id, mis.organization_id, mis.secondary_inventory
55 FROM csm_mtl_system_items_acc msi_acc,
56 csm_inv_loc_ass_acc cila_acc,
57 csp_inv_loc_assignments cila,
58 mtl_item_sub_inventories mis
59 WHERE msi_acc.inventory_item_id = mis.inventory_item_id
60 AND msi_acc.organization_id = mis.organization_id
61 AND cila.csp_inv_loc_assignment_id = cila_acc.csp_inv_loc_assignment_id
62 AND cila.subinventory_code = mis.secondary_inventory
63 AND cila_acc.user_id = msi_acc.user_id
64 AND NOT EXISTS
65 (SELECT 1
66 FROM csm_mtl_item_subinv_acc acc
67 WHERE acc.user_id = msi_acc.user_id
68 AND acc.inventory_item_id = mis.inventory_item_id
69 AND acc.organization_id = mis.organization_id
70 AND acc.secondary_inventory = mis.secondary_inventory
71 );
72
73 BEGIN
74 CSM_UTIL_PKG.LOG('Entering CSM_MTL_ITEM_SUBINV_EVENT_PKG.INSERT_MTL_ITEM_SUBINV ',
75 'CSM_MTL_ITEM_SUBINV_EVENT_PKG.INSERT_MTL_ITEM_SUBINV',FND_LOG.LEVEL_PROCEDURE);
76
77 l_run_date := SYSDATE;
78
79 -- process inserts
80 OPEN l_ins_mtl_item_subinv_csr(p_organization_id, p_user_id, p_last_run_date);
81 FETCH l_ins_mtl_item_subinv_csr BULK COLLECT INTO l_access_id_tbl, l_inventory_items_tbl, l_organizations_tbl, l_sec_inventory_tbl;
82 CLOSE l_ins_mtl_item_subinv_csr;
83
84 IF l_access_id_tbl.count > 0 THEN
85 FORALL i IN 1..l_access_id_tbl.count
86 INSERT INTO csm_mtl_item_subinv_acc(access_id, user_id, inventory_item_id, organization_id, secondary_inventory, counter,
87 created_by, creation_date, last_updated_by, last_update_date, last_update_login)
88 VALUES (l_access_id_tbl(i), p_user_id, l_inventory_items_tbl(i), l_organizations_tbl(i),l_sec_inventory_tbl(i), 1,
89 fnd_global.user_id, l_run_date, fnd_global.user_id, l_run_date, fnd_global.login_id);
90
91 CSM_UTIL_PKG.LOG('Bulk inserted ' || l_access_id_tbl.count || ' records into csm_mtl_item_subinv_acc for user ' || p_user_id ,
92 'CSM_MTL_ITEM_SUBINV_EVENT_PKG.INSERT_MTL_ITEM_SUBINV',FND_LOG.LEVEL_STATEMENT);
93
94 -- make dirty calls
95 FOR i IN 1..l_access_id_tbl.count LOOP
96 l_mark_dirty := csm_util_pkg.MakeDirtyForUser(g_pub_item,
97 l_access_id_tbl(i),
98 p_user_id,
99 asg_download.ins,
100 l_run_date);
101 END LOOP;
102
103 l_access_id_tbl.delete;
104
105 END IF;
106
107 /* --insert into acc
108 FOR i IN 1..l_inventory_items_tbl.count LOOP
109 CSM_ACC_PKG.Insert_Acc
110 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
111 ,P_ACC_TABLE_NAME => g_acc_table_name1
112 ,P_SEQ_NAME => g_acc_sequence_name1
113 ,P_PK1_NAME => g_pk1_name1
114 ,P_PK1_NUM_VALUE => l_inventory_items_tbl(i)
115 ,P_PK2_NAME => g_pk2_name1
116 ,P_PK2_NUM_VALUE => l_organizations_tbl(i)
117 ,P_PK3_NAME => g_pk3_name1
118 ,P_PK3_CHAR_VALUE => l_sec_inventory_tbl(i)
119 ,P_USER_ID => p_user_id
120 );
121 END LOOP;
122 */
123
124 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_ITEM_SUBINV_EVENT_PKG.INSERT_MTL_ITEM_SUBINV ',
125 'CSM_MTL_ITEM_SUBINV_EVENT_PKG.INSERT_MTL_ITEM_SUBINV',FND_LOG.LEVEL_PROCEDURE);
126
127 EXCEPTION
128 WHEN others THEN
129 l_sqlerrno := to_char(SQLCODE);
130 l_sqlerrmsg := substr(SQLERRM, 1,2000);
131 -- ROLLBACK;
132 CSM_UTIL_PKG.LOG('Exception in CSM_MTL_ITEM_SUBINV_EVENT_PKG.INSERT_MTL_ITEM_SUBINV: ' || l_sqlerrno || ':' || l_sqlerrmsg,
133 'CSM_MTL_ITEM_SUBINV_EVENT_PKG.INSERT_MTL_ITEM_SUBINV',FND_LOG.LEVEL_EXCEPTION);
134 END INSERT_MTL_ITEM_SUBINV;
135
136
137 PROCEDURE UPDATE_MTL_ITEM_SUBINV( p_organization_id IN number
138 , p_user_id IN number
139 , p_last_run_date IN date)
140 IS
141 TYPE access_id_tbl_typ IS TABLE OF csm_mtl_system_items_acc.access_id%TYPE INDEX BY BINARY_INTEGER;
142 l_access_id_tbl access_id_tbl_typ;
143 l_mark_dirty boolean;
144 l_run_date date;
145 l_sqlerrno varchar2(20);
146 l_sqlerrmsg varchar2(2000);
147
148 CURSOR l_upd_mtl_item_subinv_csr( p_organizationid IN number
149 , p_lastrundate IN DATE
150 , p_userid IN NUMBER) IS
151 SELECT access_id
152 FROM csm_mtl_item_subinv_acc acc
153 , mtl_item_sub_inventories mis
154 WHERE mis.inventory_item_id = acc.inventory_item_id
155 AND mis.organization_id = acc.organization_id
156 AND mis.secondary_inventory = acc.secondary_inventory
157 AND mis.last_update_date >= p_last_run_date
158 AND acc.organization_id = p_organizationid
159 AND acc.user_id = p_userid;
160
161 BEGIN
162 CSM_UTIL_PKG.LOG('Entering CSM_MTL_ITEM_SUBINV_EVENT_PKG.UPDATE_MTL_ITEM_SUBINV ',
163 'CSM_MTL_ITEM_SUBINV_EVENT_PKG.UPDATE_MTL_ITEM_SUBINV',FND_LOG.LEVEL_PROCEDURE);
164
165 l_run_date := SYSDATE;
166
167 IF l_access_id_tbl.count > 0 THEN
168 l_access_id_tbl.DELETE;
169 END IF;
170
171 OPEN l_upd_mtl_item_subinv_csr(p_organization_id, p_last_run_date, p_user_id);
172 FETCH l_upd_mtl_item_subinv_csr BULK COLLECT INTO l_access_id_tbl;
173 CLOSE l_upd_mtl_item_subinv_csr;
174
175 IF l_access_id_tbl.count > 0 THEN
176 -- make dirty calls
177 FOR i IN 1..l_access_id_tbl.count LOOP
178 CSM_ACC_PKG.Update_Acc
179 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
180 ,P_ACC_TABLE_NAME => g_acc_table_name1
181 ,P_ACCESS_ID => l_access_id_tbl(i)
182 ,P_USER_ID => p_user_id
183 );
184 END LOOP;
185
186 l_access_id_tbl.DELETE;
187 END IF;
188
189 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_ITEM_SUBINV_EVENT_PKG.UPDATE_MTL_ITEM_SUBINV ',
190 'CSM_MTL_ITEM_SUBINV_EVENT_PKG.UPDATE_MTL_ITEM_SUBINV',FND_LOG.LEVEL_PROCEDURE);
191
192 EXCEPTION
193 WHEN others THEN
194 l_sqlerrno := to_char(SQLCODE);
195 l_sqlerrmsg := substr(SQLERRM, 1,2000);
196 ROLLBACK;
197 CSM_UTIL_PKG.LOG('Exception in CSM_MTL_ITEM_SUBINV_EVENT_PKG.UPDATE_MTL_ITEM_SUBINV: ' || l_sqlerrno || ':' || l_sqlerrmsg,
198 'CSM_MTL_ITEM_SUBINV_EVENT_PKG.UPDATE_MTL_ITEM_SUBINV',FND_LOG.LEVEL_EXCEPTION);
199 END UPDATE_MTL_ITEM_SUBINV;
200
201
202 PROCEDURE DELETE_MTL_ITEM_SUBINV( p_organization_id IN number
203 , p_user_id IN number
204 , p_last_run_date IN date)
205 IS
206 TYPE inventory_item_tbl_typ IS TABLE OF mtl_system_items_b.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
207 TYPE organization_tbl_typ IS TABLE OF mtl_system_items_b.organization_id%TYPE INDEX BY BINARY_INTEGER;
208 TYPE secondary_inventory_typ IS TABLE OF mtl_item_sub_inventories.secondary_inventory%TYPE INDEX BY BINARY_INTEGER;
209 l_inventory_items_tbl inventory_item_tbl_typ;
210 l_organizations_tbl organization_tbl_typ;
211 l_sec_inventory_tbl secondary_inventory_typ;
212 l_sqlerrno varchar2(20);
213 l_sqlerrmsg varchar2(2000);
214
215 CURSOR l_del_mtl_item_subinv_csr( p_organizationid IN number
216 , p_userid IN NUMBER)
217 IS
218 SELECT acc.inventory_item_id, acc.organization_id, acc.secondary_inventory
219 FROM csm_mtl_item_subinv_acc acc
220 WHERE acc.user_id = p_userid
221 AND acc.organization_id = p_organizationid
222 AND NOT EXISTS
223 (SELECT 1
224 FROM mtl_item_sub_inventories mis
225 WHERE mis.inventory_item_id = acc.inventory_item_id
226 AND mis.organization_id = acc.organization_id
227 AND mis.secondary_inventory = acc.secondary_inventory
228 );
229
230 BEGIN
231 CSM_UTIL_PKG.LOG('Entering CSM_MTL_ITEM_SUBINV_EVENT_PKG.DELETE_MTL_ITEM_SUBINV ',
232 'CSM_MTL_ITEM_SUBINV_EVENT_PKG.DELETE_MTL_ITEM_SUBINV',FND_LOG.LEVEL_PROCEDURE);
233
234 IF l_inventory_items_tbl.count > 0 THEN
235 l_inventory_items_tbl.delete;
236 END IF;
237
238 IF l_organizations_tbl.count > 0 THEN
239 l_organizations_tbl.delete;
240 END IF;
241
242 IF l_sec_inventory_tbl.count > 0 THEN
243 l_sec_inventory_tbl.delete;
244 END IF;
245
246 -- process deletes
247 OPEN l_del_mtl_item_subinv_csr(p_organization_id, p_user_id);
248 FETCH l_del_mtl_item_subinv_csr BULK COLLECT INTO l_inventory_items_tbl, l_organizations_tbl, l_sec_inventory_tbl;
249 CLOSE l_del_mtl_item_subinv_csr;
250
251 IF l_inventory_items_tbl.count > 0 THEN
252 -- make dirty calls
253 FOR i IN 1..l_inventory_items_tbl.count LOOP
254 CSM_ACC_PKG.Delete_Acc
255 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
256 ,P_ACC_TABLE_NAME => g_acc_table_name1
257 ,P_PK1_NAME => g_pk1_name1
258 ,P_PK1_NUM_VALUE => l_inventory_items_tbl(i)
259 ,P_PK2_NAME => g_pk2_name1
260 ,P_PK2_NUM_VALUE => l_organizations_tbl(i)
261 ,P_PK3_NAME => g_pk3_name1
262 ,P_PK3_CHAR_VALUE => l_sec_inventory_tbl(i)
263 ,P_USER_ID => p_user_id
264 );
265 END LOOP;
266
267 END IF;
268
269 CSM_UTIL_PKG.LOG('Leaving CSM_MTL_ITEM_SUBINV_EVENT_PKG.DELETE_MTL_ITEM_SUBINV ',
270 'CSM_MTL_ITEM_SUBINV_EVENT_PKG.DELETE_MTL_ITEM_SUBINV',FND_LOG.LEVEL_PROCEDURE);
271
272 EXCEPTION
273 WHEN others THEN
274 l_sqlerrno := to_char(SQLCODE);
275 l_sqlerrmsg := substr(SQLERRM, 1,2000);
276 ROLLBACK;
277 CSM_UTIL_PKG.LOG('Exception in CSM_MTL_ITEM_SUBINV_EVENT_PKG.DELETE_MTL_ITEM_SUBINV: ' || l_sqlerrno || ':' || l_sqlerrmsg,
278 'CSM_MTL_ITEM_SUBINV_EVENT_PKG.DELETE_MTL_ITEM_SUBINV',FND_LOG.LEVEL_EXCEPTION);
279 END DELETE_MTL_ITEM_SUBINV;
280
281
282 PROCEDURE Refresh_acc(p_status OUT NOCOPY VARCHAR2,
283 p_message OUT NOCOPY VARCHAR2)
284 IS
285 PRAGMA AUTONOMOUS_TRANSACTION;
286 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
287 l_access_id jtm_fnd_lookups_acc.access_id%TYPE;
288 l_all_omfs_palm_resource_list asg_download.user_list;
289 l_valid_omfs_resource_list asg_download.user_list;
290 l_null_palm_omfs_resource_list asg_download.user_list;
291 l_user_id fnd_user.user_id%TYPE;
292 l_user_palm_organization_id mtl_system_items.organization_id%TYPE;
293 l_user_language mtl_system_items_tl.language%TYPE;
294 l_run_date date;
295 l_sqlerrno varchar2(20);
296 l_sqlerrmsg varchar2(2000);
297
298 CURSOR l_last_run_date_csr
299 IS
300 SELECT nvl(last_run_date, (sysdate - 365*50))
301 FROM jtm_con_request_data
302 WHERE package_name = 'CSM_MTL_ITEM_SUBINV_EVENT_PKG'
303 AND procedure_name = 'REFRESH_ACC';
304
305 --process inserts
306 CURSOR l_ins_mtl_item_subinv_csr
307 IS
308 SELECT cila_acc.user_id, mis.inventory_item_id, mis.organization_id, mis.secondary_inventory
309 FROM csm_mtl_system_items_acc msi_acc,
310 csm_inv_loc_ass_acc cila_acc,
311 csp_inv_loc_assignments cila,
312 mtl_item_sub_inventories mis
313 WHERE msi_acc.inventory_item_id = mis.inventory_item_id
314 AND msi_acc.organization_id = mis.organization_id
315 AND cila.csp_inv_loc_assignment_id = cila_acc.csp_inv_loc_assignment_id
316 AND cila.subinventory_code = mis.secondary_inventory
317 AND cila_acc.user_id = msi_acc.user_id
318 AND NOT EXISTS
319 (SELECT 1
320 FROM csm_mtl_item_subinv_acc acc
321 WHERE acc.user_id = msi_acc.user_id
322 AND acc.inventory_item_id = mis.inventory_item_id
323 AND acc.organization_id = mis.organization_id
324 AND acc.secondary_inventory = mis.secondary_inventory
325 )
326 UNION ALL
327 SELECT cila_acc.user_id, mis.inventory_item_id, mis.organization_id, mis.secondary_inventory
328 FROM csm_mtl_system_items_acc msi_acc,
329 csm_inv_loc_ass_acc cila_acc,
330 csp_inv_loc_assignments cila,
331 mtl_item_sub_inventories mis,
332 csm_user_inventory_org uorg
333 WHERE msi_acc.inventory_item_id = mis.inventory_item_id
334 AND msi_acc.organization_id = mis.organization_id
335 AND cila.csp_inv_loc_assignment_id = cila_acc.csp_inv_loc_assignment_id
336 AND cila.subinventory_code = mis.secondary_inventory
337 AND cila_acc.user_id = uorg.user_id
338 AND msi_acc.user_id = -1
339 AND uorg.organization_id = msi_acc.organization_id
340 AND NOT EXISTS
341 (SELECT 1
342 FROM csm_mtl_item_subinv_acc acc
343 WHERE acc.user_id = cila_acc.user_id
344 AND acc.inventory_item_id = mis.inventory_item_id
345 AND acc.organization_id = mis.organization_id
346 AND acc.secondary_inventory = mis.secondary_inventory
347 );
348
349 -- process updates
350 CURSOR l_upd_mtl_item_subinv_csr(p_last_upd_date DATE)
351 IS
352 SELECT acc.access_id, acc.secondary_inventory, acc.organization_id,
353 acc.inventory_item_id, acc.user_id
354 FROM csm_mtl_item_subinv_acc acc,
355 mtl_item_sub_inventories mis
356 WHERE mis.inventory_item_id = acc.inventory_item_id
357 AND mis.secondary_inventory = acc.secondary_inventory
358 AND mis.organization_id = acc.organization_id
359 AND mis.last_update_date >= p_last_upd_date;
360
361 -- process deletes
362 CURSOR l_del_mtl_item_subinv_csr
363 IS
364 SELECT acc.access_id, acc.secondary_inventory, acc.organization_id,
365 acc.inventory_item_id, acc.user_id
366 FROM csm_mtl_item_subinv_acc acc
367 WHERE NOT EXISTS
368 (SELECT 1
369 FROM csm_mtl_system_items_acc msi_acc,
370 csm_inv_loc_ass_acc cila_acc,
371 csp_inv_loc_assignments cila,
372 mtl_item_sub_inventories mis
373 WHERE msi_acc.inventory_item_id = mis.inventory_item_id
374 AND msi_acc.organization_id = mis.organization_id
375 AND cila.csp_inv_loc_assignment_id = cila_acc.csp_inv_loc_assignment_id
376 AND cila.subinventory_code = mis.secondary_inventory
377 AND cila_acc.user_id = msi_acc.user_id
378 AND msi_acc.user_id = acc.user_id
379 AND msi_acc.inventory_item_id = acc.inventory_item_id
380 AND msi_acc.organization_id = acc.organization_id
381 AND acc.secondary_inventory = cila.subinventory_code
382 UNION ALL
383 SELECT 1
384 FROM csm_mtl_system_items_acc msi_acc,
385 csm_inv_loc_ass_acc cila_acc,
386 csp_inv_loc_assignments cila,
387 mtl_item_sub_inventories mis,
388 csm_user_inventory_org uorg
389 WHERE msi_acc.inventory_item_id = mis.inventory_item_id
390 AND msi_acc.organization_id = mis.organization_id
391 AND cila.csp_inv_loc_assignment_id = cila_acc.csp_inv_loc_assignment_id
392 AND cila.subinventory_code = mis.secondary_inventory
393 AND cila_acc.user_id = uorg.user_id
394 AND uorg.user_id = acc.user_id
395 AND msi_acc.inventory_item_id = acc.inventory_item_id
396 AND uorg.organization_id = acc.organization_id
397 AND uorg.organization_id = mis.organization_id
398 AND acc.secondary_inventory = cila.subinventory_code
399 AND msi_acc.user_id = -1
400 );
401
402 CURSOR l_user_id_csr (p_resourceid IN number)
403 IS
404 SELECT user_id
405 FROM asg_user
406 WHERE resource_id = p_resourceid;
407
408 BEGIN
409 CSM_UTIL_PKG.LOG('Entering CSM_MTL_ITEM_SUBINV_EVENT_PKG.Refresh_acc ',
410 'CSM_MTL_ITEM_SUBINV_EVENT_PKG.Refresh_acc',FND_LOG.LEVEL_PROCEDURE);
411
412 l_run_date := SYSDATE;
413
414 -- get last conc program update date
415 OPEN l_last_run_date_csr;
416 FETCH l_last_run_date_csr INTO l_prog_update_date;
417 CLOSE l_last_run_date_csr;
418
419 -- process deletes
420 FOR r_del_mtl_item_subinv_rec IN l_del_mtl_item_subinv_csr LOOP
421 CSM_ACC_PKG.Delete_acc
422 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
423 ,P_ACC_TABLE_NAME => g_acc_table_name1
424 ,P_PK1_NAME => g_pk1_name1
425 ,P_PK1_NUM_VALUE => r_del_mtl_item_subinv_rec.inventory_item_id
426 ,P_PK2_NAME => g_pk2_name1
427 ,P_PK2_NUM_VALUE => r_del_mtl_item_subinv_rec.organization_id
428 ,P_PK3_NAME => g_pk3_name1
429 ,P_PK3_CHAR_VALUE => r_del_mtl_item_subinv_rec.secondary_inventory
430 ,P_USER_ID => r_del_mtl_item_subinv_rec.user_id
431 );
432 END LOOP;
433
434 -- process updates
435 FOR r_upd_mtl_item_subinv_rec IN l_upd_mtl_item_subinv_csr(l_prog_update_date) LOOP
436 CSM_ACC_PKG.Update_acc
437 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
438 ,P_ACC_TABLE_NAME => g_acc_table_name1
439 ,P_ACCESS_ID => r_upd_mtl_item_subinv_rec.access_id
440 ,P_USER_ID => r_upd_mtl_item_subinv_rec.user_id
441 );
442 END LOOP;
443
444 -- process inserts
445 FOR r_ins_mtl_item_subinv_rec IN l_ins_mtl_item_subinv_csr LOOP
446 CSM_ACC_PKG.Insert_Acc
447 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
448 ,P_ACC_TABLE_NAME => g_acc_table_name1
449 ,P_SEQ_NAME => g_acc_sequence_name1
450 ,P_PK1_NAME => g_pk1_name1
451 ,P_PK1_NUM_VALUE => r_ins_mtl_item_subinv_rec.inventory_item_id
452 ,P_PK2_NAME => g_pk2_name1
453 ,P_PK2_NUM_VALUE => r_ins_mtl_item_subinv_rec.organization_id
454 ,P_PK3_NAME => g_pk3_name1
455 ,P_PK3_CHAR_VALUE => r_ins_mtl_item_subinv_rec.secondary_inventory
456 ,P_USER_ID => r_ins_mtl_item_subinv_rec.user_id
457 );
458 END LOOP;
459
460 -- update last_run_date
461 UPDATE jtm_con_request_data
462 SET last_run_date = l_run_date
463 WHERE package_name = 'CSM_MTL_ITEM_SUBINV_EVENT_PKG'
464 AND procedure_name = 'REFRESH_ACC';
465
466 COMMIT;
467
468 p_status := 'FINE';
469 p_message := 'CSM_MTL_ITEM_SUBINV_EVENT_PKG.Refresh_Acc Executed successfully';
470
471 EXCEPTION
472 WHEN others THEN
473 l_sqlerrno := to_char(SQLCODE);
474 l_sqlerrmsg := substr(SQLERRM, 1,2000);
475 p_status := 'ERROR';
476 p_message := 'Error in CSM_MTL_ITEM_SUBINV_EVENT_PKG.Refresh_Acc: ' || l_sqlerrno || ':' || l_sqlerrmsg;
477 ROLLBACK;
478 CSM_UTIL_PKG.LOG('Exception in CSM_MTL_ITEM_SUBINV_EVENT_PKG.refresh_acc: ' || l_sqlerrno || ':' || l_sqlerrmsg,
479 'CSM_MTL_ITEM_SUBINV_EVENT_PKG.refresh_acc',FND_LOG.LEVEL_EXCEPTION);
480 END Refresh_acc;
481
482 END CSM_MTL_ITEM_SUBINV_EVENT_PKG;