[Home] [Help]
PACKAGE BODY: APPS.CSM_MTL_PARAMETERS_EVENT_PKG
Source
1 PACKAGE BODY CSM_MTL_PARAMETERS_EVENT_PKG AS
2 /* $Header: csmemtpb.pls 120.1.12020000.2 2013/04/09 10:56:58 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 --
12 -- Enter procedure, function bodies as shown below
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
13
14 g_table_name1 CONSTANT VARCHAR2(30) := 'MTL_PARAMETERS';
15 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_PARAMETERS_ACC';
16 g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_MTL_PARAMETERS_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_PARAMETERS');
19 g_pk1_name1 CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
20 g_pub_item CONSTANT VARCHAR2(30) := 'CSM_MTL_PARAMETERS';
21
22 g_table_name2 CONSTANT VARCHAR2(30) := 'MTL_INTERORG_PARAMETERS';
23 g_acc_table_name2 CONSTANT VARCHAR2(30) := 'CSM_INTERORG_PARAMETERS_ACC';
24 g_publication_item_name2 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
25 CSM_ACC_PKG.t_publication_item_list('CSM_MTL_INTERORG_PARAMS');
26 g_pk1_name2 CONSTANT VARCHAR2(30) := 'FROM_ORGANIZATION_ID';
27 g_pk2_name2 CONSTANT VARCHAR2(30) := 'TO_ORGANIZATION_ID';
28 g_pub_item2 CONSTANT VARCHAR2(30) := 'CSM_MTL_INTERORG_PARAMS';
29
30 g_acc_table_name4 CONSTANT VARCHAR2(30) := 'CSM_INV_ORG_FREIGHTS_ACC';
31 g_publication_item_name4 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
32 CSM_ACC_PKG.t_publication_item_list('CSM_INV_ORG_FREIGHTS');
33 g_pk1_name4 CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
34 g_pk2_name4 CONSTANT VARCHAR2(30) := 'FREIGHT_CODE';
35 g_pub_item4 CONSTANT VARCHAR2(30) := 'CSM_INV_ORG_FREIGHTS';
36
37 TYPE l_num_tab_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
38 TYPE l_char_tab_type IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
39
40 PROCEDURE Refresh_Acc (p_status OUT NOCOPY VARCHAR2,
41 p_message OUT NOCOPY VARCHAR2)
42 IS
43 PRAGMA AUTONOMOUS_TRANSACTION;
44 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
45 l_access_id jtm_fnd_lookups_acc.access_id%TYPE;
46 l_mark_dirty boolean;
47 l_all_omfs_palm_resource_list asg_download.user_list;
48 l_null_resource_list asg_download.user_list;
49 l_single_access_id_list asg_download.access_list;
50 l_null_access_list asg_download.access_list;
51 l_sqlerrno varchar2(20);
52 l_sqlerrmsg varchar2(4000);
53
54 CURSOR l_last_run_date_csr
55 IS
56 SELECT nvl(last_run_date, (sysdate - 365*50))
57 FROM jtm_con_request_data
58 WHERE package_name = 'CSM_MTL_PARAMETERS_EVENT_PKG'
59 AND procedure_name = 'REFRESH_ACC';
60
61 -- inserts cur
62 CURSOR l_mtl_parameters_ins_csr
63 IS
64 SELECT mtlp.organization_id
65 FROM mtl_parameters mtlp
66 WHERE NOT EXISTS
67 (SELECT 1
68 FROM csm_mtl_parameters_acc acc
69 WHERE acc.organization_id = mtlp.organization_id
70 );
71
72 -- updates cur
73 CURSOR l_mtl_parameters_upd_csr(p_last_upd_date date)
74 IS
75 SELECT acc.access_id, mtlp.organization_id
76 FROM mtl_parameters mtlp,
77 csm_mtl_parameters_acc acc
78 WHERE (mtlp.creation_date < p_last_upd_date AND mtlp.last_update_date > p_last_upd_date)
79 AND acc.organization_id = mtlp.organization_id;
80
81 -- deletes cur
82 CURSOR l_mtl_parameters_del_csr
83 IS
84 SELECT acc.access_id, acc.organization_id
85 FROM csm_mtl_parameters_acc acc
86 WHERE NOT EXISTS
87 (SELECT 1
88 FROM mtl_parameters mtlp
89 WHERE mtlp.organization_id = acc.organization_id
90 );
91
92 BEGIN
93
94 -- get last conc program update date
95 OPEN l_last_run_date_csr;
96 FETCH l_last_run_date_csr INTO l_prog_update_date;
97 CLOSE l_last_run_date_csr;
98
99 -- process all deletes
100 FOR r_mtl_parameters_del_rec IN l_mtl_parameters_del_csr LOOP
101
102 --get the users with this language
103 l_all_omfs_palm_resource_list := l_null_resource_list;
104 l_all_omfs_palm_resource_list := csm_util_pkg.get_all_omfs_palm_res_list;
105
106 l_access_id := r_mtl_parameters_del_rec.access_id;
107
108 --nullify the access list
109 l_single_access_id_list := l_null_access_list;
110 FOR i in 1 .. l_all_omfs_palm_resource_list.COUNT LOOP
111 l_single_access_id_list(i) := l_access_id;
112 END LOOP;
113
114 --mark dirty the SDQ for all users
115 IF l_single_access_id_list.count > 0 THEN
116 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSM_MTL_PARAMETERS',
117 l_single_access_id_list, l_all_omfs_palm_resource_list,
118 ASG_DOWNLOAD.DEL, sysdate);
119 END IF;
120
121 -- delete from acc table
122 DELETE FROM csm_mtl_parameters_acc WHERE access_id = l_access_id;
123 END LOOP;
124
125 -- process all updates
126 FOR r_mtl_parameters_upd_rec IN l_mtl_parameters_upd_csr(l_prog_update_date) LOOP
127
128 --get the users with this language
129 l_all_omfs_palm_resource_list := l_null_resource_list;
130 l_all_omfs_palm_resource_list := csm_util_pkg.get_all_omfs_palm_res_list;
131
132 l_access_id := r_mtl_parameters_upd_rec.access_id;
133
134 --nullify the access list
135 l_single_access_id_list := l_null_access_list;
136 FOR i in 1 .. l_all_omfs_palm_resource_list.COUNT LOOP
137 l_single_access_id_list(i) := l_access_id;
138 END LOOP;
139
140 --mark dirty the SDQ for all users
141 IF l_single_access_id_list.count > 0 THEN
142 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSM_MTL_PARAMETERS',
143 l_single_access_id_list, l_all_omfs_palm_resource_list,
144 ASG_DOWNLOAD.UPD, sysdate);
145 END IF;
146
147 END LOOP;
148
149 -- process all inserts
150 FOR r_mtl_parameters_ins_rec IN l_mtl_parameters_ins_csr LOOP
151
152 --get the users with this language
153 l_all_omfs_palm_resource_list := l_null_resource_list;
154 l_all_omfs_palm_resource_list := csm_util_pkg.get_all_omfs_palm_res_list;
155
156 SELECT csm_mtl_parameters_acc_s.nextval
157 INTO l_access_id
158 FROM dual;
159
160 --nullify the access list
161 l_single_access_id_list := l_null_access_list;
162 FOR i in 1 .. l_all_omfs_palm_resource_list.COUNT LOOP
163 l_single_access_id_list(i) := l_access_id;
164 END LOOP;
165
166 --mark dirty the SDQ for all users
167 IF l_single_access_id_list.count > 0 THEN
168 l_mark_dirty := CSM_UTIL_PKG.MakeDirtyForResource('CSM_MTL_PARAMETERS',
169 l_single_access_id_list, l_all_omfs_palm_resource_list,
170 ASG_DOWNLOAD.INS, sysdate);
171 END IF;
172
173 INSERT INTO csm_mtl_parameters_acc (access_id,
174 organization_id,
175 counter,
176 created_by,
177 creation_date,
178 last_updated_by,
179 last_update_date,
180 last_update_login
181 )
182 VALUES (l_access_id,
183 r_mtl_parameters_ins_rec.organization_id,
184 1,
185 fnd_global.user_id,
186 sysdate,
187 fnd_global.user_id,
188 sysdate,
189 fnd_global.login_id
190 );
191
192 END LOOP;
193
194 -- set the program update date in jtm_con_request_data to sysdate
195 UPDATE jtm_con_request_data
196 SET last_run_date = sysdate
197 WHERE package_name = 'CSM_MTL_PARAMETERS_EVENT_PKG'
198 AND procedure_name = 'REFRESH_ACC';
199
200 COMMIT;
201
202 p_status := 'FINE';
203 p_message := 'CSM_MTL_PARAMETERS_EVENT_PKG.Refresh_Acc Executed successfully';
204
205 EXCEPTION
206 WHEN others THEN
207 l_sqlerrno := to_char(SQLCODE);
208 l_sqlerrmsg := substr(SQLERRM, 1,3000);
209 p_status := 'ERROR';
210 p_message := 'Error in CSM_MTL_PARAMETERS_EVENT_PKG.Refresh_Acc: ' || l_sqlerrno || ':' || l_sqlerrmsg;
211 ROLLBACK;
212 fnd_file.put_line(fnd_file.log, 'CSM_MTL_PARAMETERS_EVENT_PKG ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
213 END Refresh_Acc;
214
215 PROCEDURE Refresh_InterOrg_Acc (p_status OUT NOCOPY VARCHAR2,p_message OUT NOCOPY VARCHAR2)
216 IS
217 PRAGMA AUTONOMOUS_TRANSACTION;
218 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
219 l_markdirty boolean;
220 l_sqlerrno varchar2(20);
221 l_sqlerrmsg varchar2(4000);
222
223 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
224 l_tab_user_id ASG_DOWNLOAD.USER_LIST;
225 l_tab_from_org l_num_tab_type;
226 l_tab_to_org l_num_tab_type;
227 l_tab_io_req l_num_tab_type;
228 l_tab_in_trans l_num_tab_type;
229
230
231 CURSOR l_last_run_date_csr
232 IS
233 SELECT nvl(last_run_date, (sysdate - 365*50))
234 FROM jtm_con_request_data
235 WHERE package_name = 'CSM_MTL_PARAMETERS_EVENT_PKG'
236 AND procedure_name = 'REFRESH_INTERORG_ACC';
237
238 -- inserts cur
239 CURSOR c_mtl_parameters_ins_csr(b_user_id NUMBER,b_res_id NUMBER)
240 IS
241 SELECT csm_mtl_parameters_acc_s.nextval as ACCESS_ID,b_user_id as USER_ID, from_organization_id,to_organization_id
242 FROM ( SELECT distinct mtsn.from_organization_id,mtsn.to_organization_id
243 FROM MTL_INTERORG_PARAMETERS mtsn,csp_inv_loc_assignments inv
244 WHERE b_res_id =inv.resource_id
245 AND inv.organization_id in (mtsn.from_organization_id,mtsn.to_organization_id)
246 AND NOT EXISTS (SELECT 1 FROM CSM_INTERORG_PARAMETERS_ACC acc
247 WHERE acc.from_organization_id = mtsn.from_organization_id
248 AND acc.to_organization_id = mtsn.to_organization_id
249 AND acc.user_id=b_user_id ));
250
251 -- updates cur
252 CURSOR c_mtl_parameters_upd_csr(p_lrd date)
253 IS
254 SELECT acc.access_id,acc.user_id
255 FROM CSM_INTERORG_PARAMETERS_ACC acc,MTL_INTERORG_PARAMETERS mtsn
256 WHERE acc.from_organization_id = mtsn.from_organization_id
257 AND acc.to_organization_id = mtsn.to_organization_id
258 AND p_lrd < mtsn.last_update_date ;
259
260 -- deletes cur
261 CURSOR c_mtl_parameters_del_csr
262 IS
263 SELECT acc.access_id,acc.user_id
264 FROM CSM_INTERORG_PARAMETERS_ACC acc
265 WHERE NOT EXISTS
266 (SELECT 1 FROM MTL_INTERORG_PARAMETERS mtsn
267 WHERE mtsn.from_organization_id = acc.from_organization_id
268 AND mtsn.to_organization_id = acc.to_organization_id
269 )
270 UNION
271 SELECT acc.access_id,acc.user_id FROM CSM_INTERORG_PARAMETERS_ACC acc
272 WHERE NOT EXISTS
273 (SELECT 1 FROM csp_inv_loc_assignments inv,asg_user au
274 WHERE au.resource_id =inv.resource_id AND au.user_id=acc.user_id AND inv.organization_id in (acc.from_organization_id,acc.to_organization_id));
275
276 BEGIN
277
278 CSM_UTIL_PKG.LOG('Entered Refresh InterOrg Acc', 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_INTERORG_ACC', FND_LOG.LEVEL_PROCEDURE);
279
280 -- get last conc program update date
281 OPEN l_last_run_date_csr;
282 FETCH l_last_run_date_csr INTO l_prog_update_date;
283 CLOSE l_last_run_date_csr;
284
285
286 CSM_UTIL_PKG.LOG('Processing deletes', 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_INTERORG_ACC', FND_LOG.LEVEL_PROCEDURE);
287 -- process all deletes
288
289 OPEN c_mtl_parameters_del_csr;
290 LOOP
291 l_tab_access_id.DELETE;
292 l_tab_user_id.DELETE;
293
294 FETCH c_mtl_parameters_del_csr BULK COLLECT INTO l_tab_access_id,l_tab_user_id LIMIT 1000;
295 EXIT WHEN l_tab_access_id.COUNT = 0;
296
297 CSM_UTIL_PKG.LOG('Bulk deleted ' || l_tab_access_id.count || ' records from CSM_INTERORG_PARAMETERS_ACC' ,
298 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_INTERORG_ACC',FND_LOG.LEVEL_EVENT);
299
300 l_markdirty := asg_download.mark_dirty(
301 P_PUB_ITEM => 'CSM_MTL_INTERORG_PARAMS'
302 , p_accessList => l_tab_access_id
303 , p_userid_list => l_tab_user_id
304 , p_dml_type => 'D'
305 , P_TIMESTAMP => sysdate
306 );
307
308 FORALL i IN 1..l_tab_access_id.COUNT
309 DELETE FROM CSM_INTERORG_PARAMETERS_ACC WHERE access_id = l_tab_access_id(i);
310
311 COMMIT;
312 END LOOP;
313 CLOSE c_mtl_parameters_del_csr;
314
315
316 CSM_UTIL_PKG.LOG('Processing Updates', 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_INTERORG_ACC', FND_LOG.LEVEL_PROCEDURE);
317 -- process all updates
318
319 OPEN c_mtl_parameters_upd_csr(l_prog_update_date);
320 LOOP
321 l_tab_access_id.DELETE;
322 l_tab_user_id.DELETE;
323
324 FETCH c_mtl_parameters_upd_csr BULK COLLECT INTO l_tab_access_id,l_tab_user_id LIMIT 1000;
325 EXIT WHEN l_tab_access_id.COUNT = 0;
326
327 CSM_UTIL_PKG.LOG('Bulk updated ' || l_tab_access_id.count || ' records in CSM_INTERORG_PARAMETERS_ACC' ,
328 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_INTERORG_ACC',FND_LOG.LEVEL_EVENT);
329
330 l_markdirty := asg_download.mark_dirty(
331 P_PUB_ITEM => 'CSM_MTL_INTERORG_PARAMS'
332 , p_accessList => l_tab_access_id
333 , p_userid_list => l_tab_user_id
334 , p_dml_type => 'U'
335 , P_TIMESTAMP => sysdate
336 );
337 COMMIT;
338 END LOOP;
339 CLOSE c_mtl_parameters_upd_csr;
340
341 CSM_UTIL_PKG.LOG('Processing inserts', 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_INTERORG_ACC', FND_LOG.LEVEL_PROCEDURE);
342 -- process all inserts
343
344
345 FOR rec IN (SELECT user_id,resource_id from asg_user where MULTI_PLATFORM='Y' and enabled='Y')
346 LOOP
347 OPEN c_mtl_parameters_ins_csr(rec.USER_ID,rec.RESOURCE_ID);
348 LOOP
349 l_tab_access_id.DELETE;
350 l_tab_user_id.DELETE;
351 l_tab_from_org.DELETE;
352 l_tab_to_org.DELETE;
353
354 FETCH c_mtl_parameters_ins_csr BULK COLLECT INTO l_tab_access_id, l_tab_user_id,l_tab_from_org,l_tab_to_org LIMIT 1000;
355 EXIT WHEN l_tab_access_id.COUNT = 0;
356
357 CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count || ' records into CSM_INTERORG_PARAMETERS_ACC for user:'||rec.USER_ID ,
358 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_INTERORG_ACC',FND_LOG.LEVEL_EVENT);
359
360 FORALL i IN 1..l_tab_access_id.COUNT
361 INSERT INTO CSM_INTERORG_PARAMETERS_ACC (access_id, user_id,from_organization_id, to_organization_id,
362 counter, created_by, creation_date, last_updated_by, last_update_date )
363 VALUES (l_tab_access_id(i), l_tab_user_id(i),l_tab_from_org(i),l_tab_to_org(i),
364 1,fnd_global.user_id, sysdate, fnd_global.user_id, sysdate);
365
366 /*** push to oLite using asg_download ***/
367 -- do bulk makedirty
368 l_markdirty := asg_download.mark_dirty(
369 P_PUB_ITEM => 'CSM_MTL_INTERORG_PARAMS'
370 , p_accessList => l_tab_access_id
371 , p_userid_list => l_tab_user_id
372 , p_dml_type => 'I'
373 , P_TIMESTAMP => sysdate
374 );
375
376 COMMIT;
377 END LOOP;
378 CLOSE c_mtl_parameters_ins_csr;
379 END LOOP;
380
381 -- set the program update date in jtm_con_request_data to sysdate
382 UPDATE jtm_con_request_data
383 SET last_run_date = sysdate
384 WHERE package_name = 'CSM_MTL_PARAMETERS_EVENT_PKG'
385 AND procedure_name = 'REFRESH_INTERORG_ACC';
386
387 COMMIT;
388
389 p_status := 'FINE';
390 p_message := 'CSM_MTL_PARAMETERS_EVENT_PKG.Refresh_InterOrg_Acc Executed successfully';
391 CSM_UTIL_PKG.LOG('Leaving InterOrg refresh acc', 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_INTERORG_ACC', FND_LOG.LEVEL_PROCEDURE);
392
393 EXCEPTION
394 WHEN others THEN
395 l_sqlerrno := to_char(SQLCODE);
396 l_sqlerrmsg := substr(SQLERRM, 1,3000);
397 p_status := 'ERROR';
398 p_message := 'Error in CSM_MTL_PARAMETERS_EVENT_PKG.Refresh_InterOrg_Acc: ' || l_sqlerrno || ':' || l_sqlerrmsg;
399 CSM_UTIL_PKG.LOG('Exception: '||p_message, 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_INTERORG_ACC', FND_LOG.LEVEL_PROCEDURE);
400 ROLLBACK;
401 fnd_file.put_line(fnd_file.log, 'CSM_MTL_PARAMETERS_EVENT_PKG Refresh_InterOrg_Acc ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
402 END Refresh_InterOrg_Acc;
403
404 PROCEDURE REFRESH_FREIGHTS_ACC (p_status OUT NOCOPY VARCHAR2,p_message OUT NOCOPY VARCHAR2)
405 IS
406 PRAGMA AUTONOMOUS_TRANSACTION;
407 l_prog_update_date jtm_con_request_data.last_run_date%TYPE;
408 l_sqlerrno varchar2(20);
409 l_sqlerrmsg varchar2(4000);
410
411 CURSOR l_last_run_date_csr
412 IS
413 SELECT nvl(last_run_date, (sysdate - 365*50))
414 FROM jtm_con_request_data
415 WHERE package_name = 'CSM_MTL_PARAMETERS_EVENT_PKG'
416 AND procedure_name = 'REFRESH_FREIGHTS_ACC';
417
418 l_tab_access_id ASG_DOWNLOAD.ACCESS_LIST;
419 l_tab_user_id ASG_DOWNLOAD.USER_LIST;
420 l_tab_org l_num_tab_type;
421 l_tab_freight l_char_tab_type;
422 l_tab_shipM l_char_tab_type;
423 l_markdirty boolean;
424
425 -- inserts cur
426 CURSOR c_inv_org_freights_ins_csr
427 IS
428 SELECT csm_mtl_parameters_acc_s.nextval as ACCESS_ID,au.user_id, oft.organization_id,oft.freight_code,sm.ship_method_code
429 FROM ORG_FREIGHT_TL oft,asg_user au,WSH_CARRIER_SHIP_METHODS_V sm
430 WHERE oft.LANGUAGE=au.language
431 and nvl(oft.disable_date,sysdate+1) > sysdate
432 AND oft.organization_id=sm.organization_id(+)
433 AND oft.freight_code=sm.freight_code(+)
434 AND sm.enabled_flag(+) = 'Y'
435 AND sysdate between sm.start_date_active(+) AND nvl(sm.end_date_active(+), sysdate+1)
436 AND EXISTS(SELECT 1 FROM CSP_INV_LOC_ASSIGNMENTS ass
437 WHERE ass.organization_id = oft.organization_id
438 AND ass.RESOURCE_ID=au.resource_id)
439 AND NOT EXISTS(SELECT 1 FROM CSM_INV_ORG_FREIGHTS_ACC acc
440 WHERE acc.organization_id = oft.organization_id
441 AND acc.freight_code=oft.freight_code
442 AND NVL(acc.ship_method_code,'X') = NVL(sm.ship_method_code,'X')
443 AND acc.user_id=au.user_id);
444
445 -- updates cur
446 CURSOR c_inv_org_freights_upd_csr(b_lrd DATE)
447 IS
448 SELECT acc.access_id,acc.user_id
449 FROM CSM_INV_ORG_FREIGHTS_ACC acc,ORG_FREIGHT_TL oft,asg_user au
450 WHERE acc.organization_id = oft.organization_id
451 AND acc.freight_code=oft.freight_code
452 AND au.user_id=acc.user_id
453 AND oft.language=au.language
454 AND oft.last_update_date > b_lrd
455 UNION
456 SELECT acc.access_id,acc.user_id
457 FROM CSM_INV_ORG_FREIGHTS_ACC acc
458 WHERE acc.SHIP_METHOD_CODE IN (
459 SELECT FL.LOOKUP_CODE FROM FND_LOOKUP_VALUES_VL fl
460 WHERE FL.LOOKUP_TYPE = 'SHIP_METHOD'
461 AND FL.VIEW_APPLICATION_ID = 3
462 AND FL.last_update_date > b_lrd);
463
464 -- deletes cur
465 CURSOR c_inv_org_freights_del_csr
466 IS
467 SELECT acc.access_id,acc.user_id
468 FROM CSM_INV_ORG_FREIGHTS_ACC acc
469 WHERE NOT EXISTS (SELECT 1 FROM ORG_FREIGHT_TL oft,CSP_INV_LOC_ASSIGNMENTS ass,asg_user au
470 WHERE acc.organization_id = oft.organization_id
471 AND acc.freight_code=oft.freight_code
472 AND acc.user_id=au.user_id
473 AND nvl(oft.disable_date,sysdate+1) > sysdate
474 AND ass.organization_id = oft.organization_id
475 AND ass.RESOURCE_ID=au.resource_id)
476 UNION
477 (SELECT acc.access_id,acc.user_id
478 FROM CSM_INV_ORG_FREIGHTS_ACC acc
479 WHERE acc.ship_method_code IS NOT NULL
480 AND NOT EXISTS (SELECT 1 FROM WSH_CARRIER_SHIP_METHODS_V sm
481 WHERE acc.organization_id = sm.organization_id
482 AND acc.ship_method_code=sm.ship_method_code
483 AND acc.freight_code=sm.freight_code
484 AND SM.ENABLED_FLAG='Y'
485 AND sysdate between sm.start_date_active AND nvl(sm.end_date_active, sysdate+1))
486 UNION ALL
487 SELECT acc.access_id,acc.user_id
488 FROM CSM_INV_ORG_FREIGHTS_ACC acc
489 WHERE acc.ship_method_code IS NULL
490 AND EXISTS (SELECT 1 FROM WSH_CARRIER_SHIP_METHODS_V sm
491 WHERE acc.organization_id = sm.organization_id
492 AND acc.freight_code=sm.freight_code
493 AND SM.ENABLED_FLAG='Y'
494 AND sysdate between sm.start_date_active AND nvl(sm.end_date_active, sysdate+1)));
495
496 BEGIN
497 CSM_UTIL_PKG.LOG('Entered Refresh Inventory Org Freights Acc', 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_FREIGHTS_ACC', FND_LOG.LEVEL_PROCEDURE);
498
499 -- get last conc program update date
500 OPEN l_last_run_date_csr;
501 FETCH l_last_run_date_csr INTO l_prog_update_date;
502 CLOSE l_last_run_date_csr;
503
504 CSM_UTIL_PKG.LOG('Processing deletes', 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_FREIGHTS_ACC', FND_LOG.LEVEL_PROCEDURE);
505 -- process all deletes
506
507 OPEN c_inv_org_freights_del_csr;
508 LOOP
509 l_tab_access_id.DELETE;
510 l_tab_user_id.DELETE;
511
512 FETCH c_inv_org_freights_del_csr BULK COLLECT INTO l_tab_access_id,l_tab_user_id LIMIT 1000;
513 EXIT WHEN l_tab_access_id.COUNT = 0;
514
515 CSM_UTIL_PKG.LOG('Bulk deleted ' || l_tab_access_id.count || ' records from CSM_INTERORG_FREIGHTS_ACC' ,
516 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_FREIGHTS_ACC',FND_LOG.LEVEL_EVENT);
517
518 l_markdirty := asg_download.mark_dirty(
519 P_PUB_ITEM => 'CSM_INV_ORG_FREIGHTS'
520 , p_accessList => l_tab_access_id
521 , p_userid_list => l_tab_user_id
522 , p_dml_type => 'D'
523 , P_TIMESTAMP => sysdate
524 );
525
526 FORALL i IN 1..l_tab_access_id.COUNT
527 DELETE FROM CSM_INV_ORG_FREIGHTS_ACC WHERE access_id = l_tab_access_id(i);
528
529 COMMIT;
530 END LOOP;
531 CLOSE c_inv_org_freights_del_csr;
532
533 CSM_UTIL_PKG.LOG('Processing Updates', 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_FREIGHTS_ACC', FND_LOG.LEVEL_PROCEDURE);
534 -- process all updates
535
536 OPEN c_inv_org_freights_upd_csr(l_prog_update_date);
537 LOOP
538 l_tab_access_id.DELETE;
539 l_tab_user_id.DELETE;
540
541 FETCH c_inv_org_freights_upd_csr BULK COLLECT INTO l_tab_access_id,l_tab_user_id LIMIT 1000;
542 EXIT WHEN l_tab_access_id.COUNT = 0;
543
544 CSM_UTIL_PKG.LOG('Bulk updated ' || l_tab_access_id.count || ' records in CSM_INTERORG_FREIGHTS_ACC' ,
545 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_FREIGHTS_ACC',FND_LOG.LEVEL_EVENT);
546
547 l_markdirty := asg_download.mark_dirty(
548 P_PUB_ITEM => 'CSM_INV_ORG_FREIGHTS'
549 , p_accessList => l_tab_access_id
550 , p_userid_list => l_tab_user_id
551 , p_dml_type => 'U'
552 , P_TIMESTAMP => sysdate
553 );
554 COMMIT;
555 END LOOP;
556 CLOSE c_inv_org_freights_upd_csr;
557
558 CSM_UTIL_PKG.LOG('Processing inserts', 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_FREIGHTS_ACC', FND_LOG.LEVEL_PROCEDURE);
559 -- process all inserts
560
561 OPEN c_inv_org_freights_ins_csr;
562 LOOP
563 l_tab_access_id.DELETE;
564 l_tab_user_id.DELETE;
565 l_tab_org.DELETE;
566 l_tab_freight.DELETE;
567 l_tab_shipM.DELETE;
568
569
570 FETCH c_inv_org_freights_ins_csr BULK COLLECT INTO l_tab_access_id, l_tab_user_id,l_tab_org,l_tab_freight,l_tab_shipM LIMIT 1000;
571 EXIT WHEN l_tab_access_id.COUNT = 0;
572
573 CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count || ' records into CSM_INTERORG_FREIGHTS_ACC' ,
574 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_FREIGHTS_ACC',FND_LOG.LEVEL_EVENT);
575
576 FORALL i IN 1..l_tab_access_id.COUNT
577 INSERT INTO CSM_INV_ORG_FREIGHTS_ACC (access_id, user_id,organization_id, freight_code,ship_method_code,
578 created_by, creation_date, last_updated_by, last_update_date )
579 VALUES (l_tab_access_id(i), l_tab_user_id(i),l_tab_org(i),l_tab_freight(i),l_tab_shipM(i),
580 fnd_global.user_id, sysdate, fnd_global.user_id, sysdate);
581
582 /*** push to oLite using asg_download ***/
583 -- do bulk makedirty
584 l_markdirty := asg_download.mark_dirty(
585 P_PUB_ITEM => 'CSM_INV_ORG_FREIGHTS'
586 , p_accessList => l_tab_access_id
587 , p_userid_list => l_tab_user_id
588 , p_dml_type => 'I'
589 , P_TIMESTAMP => sysdate
590 );
591
592 COMMIT;
593 END LOOP;
594 CLOSE c_inv_org_freights_ins_csr;
595
596 -- set the program update date in jtm_con_request_data to sysdate
597 UPDATE jtm_con_request_data
598 SET last_run_date = sysdate
599 WHERE package_name = 'CSM_MTL_PARAMETERS_EVENT_PKG'
600 AND procedure_name = 'REFRESH_FREIGHTS_ACC';
601
602 COMMIT;
603
604 p_status := 'FINE';
605 p_message := 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_FREIGHTS_ACC Executed successfully';
606 CSM_UTIL_PKG.LOG('Leaving InterOrg Freights refresh acc', 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_FREIGHTS_ACC', FND_LOG.LEVEL_PROCEDURE);
607
608 EXCEPTION
609 WHEN others THEN
610 l_sqlerrno := to_char(SQLCODE);
611 l_sqlerrmsg := substr(SQLERRM, 1,3000);
612 p_status := 'ERROR';
613 p_message := 'Error in CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_FREIGHTS_ACC: ' || l_sqlerrno || ':' || l_sqlerrmsg;
614 CSM_UTIL_PKG.LOG('Exception: '||p_message, 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_FREIGHTS_ACC', FND_LOG.LEVEL_PROCEDURE);
615 ROLLBACK;
616 fnd_file.put_line(fnd_file.log, 'CSM_MTL_PARAMETERS_EVENT_PKG REFRESH_FREIGHTS_ACC ERROR : ' || l_sqlerrno || ':' || l_sqlerrmsg);
617 END REFRESH_FREIGHTS_ACC;
618
619 END CSM_MTL_PARAMETERS_EVENT_PKG;