[Home] [Help]
PACKAGE BODY: APPS.WSH_CONC_UTIL_PKG
Source
1 PACKAGE BODY WSH_CONC_UTIL_PKG AS
2 /* $Header: WSHCPUTB.pls 120.8 2011/08/18 12:26:31 suppal ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_CONC_UTIL_PKG';
6
7 G_BULK_SIZE CONSTANT NUMBER := 1000; -- ECO 5069719
8 G_ENTITY_TRIP CONSTANT NUMBER := 1;
9 G_ENTITY_DEL CONSTANT NUMBER := 2;
10 G_ENTITY_DETAIL CONSTANT NUMBER := 3;
11
12 -- DESCRIPTION: This procedure updates transactions with the new/valid |
13 -- Freight Code/Carrier id after upgrade. It updates the |
14 -- Old/Invalid Fgt.Code with the new/valid Fgt. Code, where |
15 -- ever the Old Fgt.Code-Ship.Method Combination is non-exis |
16 -- -tant after the upgrade. |
17
18 Procedure Worker_Upgrade_Closed_Orders(
19 errbuf OUT NOCOPY VARCHAR2,
20 retcode OUT NOCOPY VARCHAR2,
21 p_batch_commit_size IN NUMBER,
22 p_logical_worker_id IN NUMBER,
23 p_numworkers IN NUMBER) IS
24
25
26
27 cursor c_get_new_fgt_code (p_ship_method_code varchar2) is
28 select wc.freight_code, wc.carrier_id
29 from wsh_carriers wc, wsh_carrier_services wcs
30 where
31 wcs.ship_method_code = p_ship_method_code
32 and wcs.carrier_id = wc.carrier_id;
33
34 -- II. Get All the Fgt.Code - S.Method not upgraded and thus would be having problem
35 --
36 cursor c_get_comb_notupg is
37 select wcsm.ship_method_code , wcsm.organization_id , wcsm.freight_code
38 from wsh_carrier_ship_methods wcsm
39 where
40 not exists (select 'x' from wsh_carriers wc,
41 wsh_carrier_services wcs
42 where
43 wc.freight_code = wcsm.freight_code
44 and wc.carrier_id = wcs.carrier_id
45 and wcs.ship_method_code = wcsm.ship_method_code);
46
47
48 l_new_freight_code VARCHAR2(30);
49 l_new_carrier_id NUMBER;
50 l_tot_lin_upd NUMBER;
51 l_tot_hdr_upd NUMBER;
52
53
54
55 l_script_name varchar2(30);
56 l_table_owner varchar2(3) ;
57 l_table_name varchar2(30) ;
58 l_worker_id NUMBER;
59 l_num_workers NUMBER;
60 l_batch_size NUMBER;
61 l_start_rowid rowid;
62 l_end_rowid rowid;
63 l_rows_processed number;
64 l_any_rows_to_process boolean;
65 --BUG#12353783
66 l_Prod_Schema VARCHAR2(30);
67 l_Prod_Short_Name CONSTANT VARCHAR2(30) := 'ONT';
68 l_status VARCHAR2(1);
69 l_industry VARCHAR2(1);
70
71 --
72 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled ;
73 --
74 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Worker_Upgrade_Closed_Orders';
75
76 BEGIN
77 --
78
79
80 IF l_debug_on THEN
81 WSH_DEBUG_SV.push(l_module_name);
82 END IF;
83
84 l_script_name := 'wshupoe_01';
85 --l_table_owner := 'ONT';
86 l_table_name := 'OE_ORDER_LINES_ALL';
87 l_worker_id := p_logical_worker_id;
88 l_num_workers := p_numworkers;
89 l_batch_size := p_batch_commit_size;
90 l_rows_processed := 0;
91 --BUG#12353783
92 IF NOT FND_INSTALLATION.GET_APP_INFO(l_Prod_Short_Name, l_status, l_industry,
93 l_Prod_Schema)
94 THEN
95 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
96 END IF;
97
98 l_table_owner := l_Prod_Schema;
99
100 IF l_debug_on THEN
101 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit ad_parallel_updates_pkg.initialize_rowid_range',WSH_DEBUG_SV.C_PROC_LEVEL);
102 END IF;
103 ad_parallel_updates_pkg.initialize_rowid_range(
104 ad_parallel_updates_pkg.ROWID_RANGE,
105 l_table_owner,
106 l_table_name,
107 l_script_name,
108 l_worker_id,
109 l_num_workers,
110 l_batch_size, 0);
111
112 IF l_debug_on THEN
113 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit ad_parallel_updates_pkg.get_rowid_range',WSH_DEBUG_SV.C_PROC_LEVEL);
114 END IF;
115 ad_parallel_updates_pkg.get_rowid_range(
116 l_start_rowid,
117 l_end_rowid,
118 l_any_rows_to_process,
119 l_batch_size,
120 TRUE);
121 ----------------------------------------------------------------
122 l_tot_lin_upd := 0;
123 l_tot_hdr_upd := 0;
124 ------------------------------------------------------------------
125
126 WHILE (l_any_rows_to_process = TRUE)
127 LOOP
128
129
130 FOR v_notupg IN c_get_comb_notupg
131 LOOP
132
133 IF l_debug_on THEN
134 WSH_DEBUG_SV.log(l_module_name,'v_notupg.ship_method_code',v_notupg.ship_method_code);
135 WSH_DEBUG_SV.log(l_module_name,'v_notupg.freight_code',v_notupg.freight_code);
136 END IF;
137 l_new_freight_code := null;
138 l_new_carrier_id := null;
139
140 --For Every Comb. not existing
141 -- Getting new Fgt. Code , carrier id
142 OPEN c_get_new_fgt_code (v_notupg.ship_method_code);
143 FETCH c_get_new_fgt_code INTO l_new_freight_code, l_new_carrier_id;
144 CLOSE c_get_new_fgt_code;
145
146 IF l_debug_on THEN
147 WSH_DEBUG_SV.log(l_module_name,'l_new_freight_code',l_new_freight_code);
148 WSH_DEBUG_SV.log(l_module_name,'l_new_carrier_id',l_new_carrier_id);
149 END IF;
150
151 -- Update Closed Order Lines, Interfaced to Shpg. and Not cancelled
152 update /*+ ROWID (oel) */ oe_order_lines_all oel
153 set oel.freight_carrier_code = l_new_freight_code
154 , oel.last_updated_by = -2950631
155 , oel.last_update_date = sysdate
156 where oel.open_flag = 'N'
157 and oel.rowid BETWEEN l_start_rowid AND l_end_rowid
158 and nvl(oel.cancelled_flag, 'N') = 'N'
159 and oel.shipping_interfaced_flag = 'Y'
160 and oel.freight_carrier_code = v_notupg.freight_code
161 and oel.shipping_method_code = v_notupg.ship_method_code;
162
163 IF SQL%NOTFOUND THEN
164 null;
165 END IF;
166
167 -- Update Closed Headers, whose lines are closed, and Not cancelled
168 update oe_order_headers_all oeh
169 set oeh.freight_carrier_code = l_new_freight_code
170 , oeh.last_updated_by = -2950631
171 , oeh.last_update_date = sysdate
172 where oeh.open_flag = 'N'
173 and nvl(oeh.cancelled_flag, 'N') = 'N'
174 and oeh.freight_carrier_code = v_notupg.freight_code
175 and oeh.shipping_method_code = v_notupg.ship_method_code
176 and exists (select /*+ ROWID (oel) */ 'x' from oe_order_lines_all oel -- Getting Only Shpg. Interfaced Lines
177 where oel.header_id = oeh.header_id
178 and oel.rowid BETWEEN l_start_rowid AND l_end_rowid
179 and oel.open_flag = 'N'
180 and nvl(oel.cancelled_flag, 'N') = 'N'
181 and oel.SHIPPING_INTERFACED_FLAG = 'Y');
182
183 IF SQL%NOTFOUND THEN
184 null;
185 END IF;
186
187 END LOOP; -- FOR v_notupg in c_fgt_not_upg
188
189 ad_parallel_updates_pkg.processed_rowid_range(
190 l_rows_processed,
191 l_end_rowid);
192
193 --
194 -- commit transaction here
195 --
196
197 commit;
198
199 --
200 -- get new range of rowids
201 --
202 ad_parallel_updates_pkg.get_rowid_range(
203 l_start_rowid,
204 l_end_rowid,
205 l_any_rows_to_process,
206 l_batch_size,
207 FALSE);
208
209 END LOOP;
210
211 IF l_debug_on THEN
212 WSH_DEBUG_SV.pop(l_module_name);
213 END IF;
214 EXCEPTION
215 WHEN OTHERS THEN
216 errbuf := 'Exception occurred in Worker_Upgrade_Closed_Orders: ' ||
217 'SQLCODE: '||sqlcode||' SQLERRM: '||sqlerrm;
218 retcode := '2';
219 --
220 IF l_debug_on THEN
221 WSH_DEBUG_SV.pop(l_module_name);
222 END IF;
223 END Worker_Upgrade_Closed_Orders;
224
225 Procedure Master_Conc_Parallel_Upgrade(
226 errbuf OUT NOCOPY VARCHAR2,
227 retcode OUT NOCOPY VARCHAR2,
228 p_worker_conc_appsshortname IN VARCHAR2,
229 p_worker_conc_program IN VARCHAR2,
230 p_batch_commit_size IN NUMBER,
231 p_numworkers IN NUMBER) IS
232
233 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
234 --
235 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Worker_Upgrade_Closed_Orders';
236 BEGIN
237
238 IF l_debug_on THEN
239 WSH_DEBUG_SV.push(l_module_name);
240 END IF;
241 AD_CONC_UTILS_PKG.submit_subrequests(
242 X_errbuf => errbuf,
243 X_retcode => retcode,
244 X_WorkerConc_app_shortname => p_worker_conc_appsshortname,
245 X_WorkerConc_progname => p_worker_conc_program,
246 X_Batch_Size => p_batch_commit_size,
247 X_Num_Workers => p_numworkers,
248 X_Argument4 => NULL,
249 X_Argument5 => NULL,
250 X_Argument6 => NULL,
251 X_Argument7 => NULL,
252 X_Argument8 => NULL,
253 X_Argument9 => NULL,
254 X_Argument10 => NULL);
255
256 IF l_debug_on THEN
257 WSH_DEBUG_SV.pop(l_module_name);
258 END IF;
259 END Master_Conc_Parallel_Upgrade;
260
261
262 -- Description: utility to lock/update entities for ECO 5069719
263 -- so that service level and mode of transport will be updated.
264 --
265 -- exceptions will be raised to the caller.
266 --
267 -- Parameters:
268 -- p_entity = G_ENTITY_TRIP, G_ENTITY_DEL, G_ENTITY_DETAIL
269 -- p_ship_method_code, p_carrier_id = identify records that need update
270 -- p_service_level, p_mode_of_transport = values to update in table
271 -- x_count = count of records updated
272 -- x_count_nu = count of records not updated (because of locks)
273 -- x_sm_state = state to update UPDATE_MOT_SL:
274 -- NULL -- fully processed
275 -- 'Y' -- at least one record was not updated
276 PROCEDURE process_entity_sm(p_entity IN NUMBER,
277 p_ship_method_code IN VARCHAR2,
278 p_carrier_id IN NUMBER,
279 p_service_level IN VARCHAR2,
280 p_mode_of_transport IN VARCHAR2,
281 x_count IN OUT NOCOPY NUMBER,
282 x_count_nu IN OUT NOCOPY NUMBER,
283 x_sm_state IN OUT NOCOPY VARCHAR2) IS
284
285 CURSOR c_current_trips(x_ship_method_code IN VARCHAR2,
286 x_carrier_id IN NUMBER) IS
287 SELECT trip_id
288 FROM WSH_TRIPS
289 WHERE status_code = 'OP'
290 AND ship_method_code = x_ship_method_code
291 AND carrier_id = x_carrier_id
292 AND mode_of_transport IS NULL
293 AND service_level IS NULL;
294
295 CURSOR c_current_dels(x_ship_method_code IN VARCHAR2,
296 x_carrier_id IN NUMBER) IS
297 SELECT delivery_id
298 FROM WSH_NEW_DELIVERIES
299 WHERE status_code IN ('OP', 'CO', 'SA', 'SR', 'SC')
300 AND ship_method_code = x_ship_method_code
301 AND carrier_id = x_carrier_id
302 AND mode_of_transport IS NULL
303 AND service_level IS NULL;
304
305 CURSOR c_current_details(x_ship_method_code IN VARCHAR2,
306 x_carrier_id IN NUMBER) IS
307 SELECT delivery_detail_id
308 FROM WSH_DELIVERY_DETAILS
309 WHERE released_status IN ('N', 'R', 'B', 'X', 'S', 'Y')
310 AND ship_method_code = x_ship_method_code
311 AND carrier_id = x_carrier_id
312 AND mode_of_transport IS NULL
313 AND service_level IS NULL;
314
315 CURSOR c_lock_trip(x_id IN NUMBER) IS
316 SELECT trip_id
317 FROM WSH_TRIPS
318 WHERE trip_id = x_id
319 FOR UPDATE NOWAIT;
320
321 CURSOR c_lock_del(x_id IN NUMBER) IS
322 SELECT delivery_id
323 FROM WSH_NEW_DELIVERIES
324 WHERE delivery_id = x_id
325 FOR UPDATE NOWAIT;
326
327 CURSOR c_lock_detail(x_id IN NUMBER) IS
328 SELECT delivery_detail_id
329 FROM WSH_DELIVERY_DETAILS
330 WHERE delivery_detail_id = x_id
331 FOR UPDATE NOWAIT;
332
333 l_debug_on BOOLEAN;
334 --
335 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_ENTITY_SM';
336
337 l_continue BOOLEAN;
338 l_work_ids WSH_UTIL_CORE.ID_TAB_TYPE;
339 l_update_ids WSH_UTIL_CORE.ID_TAB_TYPE;
340
341 l_work_index NUMBER;
342 l_update_index NUMBER;
343
344 l_user_id NUMBER;
345 l_login NUMBER;
346
347 l_dummy_id NUMBER;
348
349 BEGIN
350 --
351 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
352 --
353 IF l_debug_on IS NULL
354 THEN
355 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
356 END IF;
357 --
358 IF l_debug_on THEN
359 WSH_DEBUG_SV.push(l_module_name);
360 WSH_DEBUG_SV.log(l_module_name, 'p_entity', p_entity);
361 WSH_DEBUG_SV.log(l_module_name, 'in x_count', x_count);
362 WSH_DEBUG_SV.log(l_module_name, 'in x_count_nu', x_count_nu);
363 WSH_DEBUG_SV.log(l_module_name, 'in x_sm_state', x_sm_state);
364 END IF;
365
366 IF (p_entity IS NULL)
367 OR (p_entity NOT IN (G_ENTITY_TRIP, G_ENTITY_DEL,G_ENTITY_DETAIL)) THEN
368 IF l_debug_on THEN
369 WSH_DEBUG_SV.log(l_module_name, 'unrecognized p_entity', p_entity);
370 WSH_DEBUG_SV.pop(l_module_name);
371 END IF;
372 RETURN;
373 END IF;
374
375 IF p_entity = G_ENTITY_TRIP THEN
376 OPEN c_current_trips(p_ship_method_code, p_carrier_id);
377 ELSIF p_entity = G_ENTITY_DEL THEN
378 OPEN c_current_dels (p_ship_method_code, p_carrier_id);
379 ELSE
380 OPEN c_current_details(p_ship_method_code, p_carrier_id);
381 END IF;
382
383 l_user_id := FND_GLOBAL.USER_ID;
384 l_login := FND_GLOBAL.LOGIN_ID;
385
386 l_continue := TRUE;
387 l_update_index := 0;
388
389 WHILE l_continue LOOP
390
391 IF p_entity = G_ENTITY_TRIP THEN
392 FETCH c_current_trips BULK COLLECT INTO l_work_ids LIMIT G_BULK_SIZE;
393 l_continue := c_current_trips%FOUND;
394 ELSIF p_entity = G_ENTITY_DEL THEN
395 FETCH c_current_dels BULK COLLECT INTO l_work_ids LIMIT G_BULK_SIZE;
396 l_continue := c_current_dels%FOUND;
397 ELSE
398 FETCH c_current_details BULK COLLECT INTO l_work_ids LIMIT G_BULK_SIZE;
399 l_continue := c_current_details%FOUND;
400 END IF;
401
402 IF l_debug_on THEN
403 WSH_DEBUG_SV.log(l_module_name, 'l_work_ids.COUNT', l_work_ids.COUNT);
404 WSH_DEBUG_SV.log(l_module_name, 'l_continue', l_continue);
405 END IF;
406
407 IF (l_work_ids.COUNT > 0) THEN
408 FOR l_work_index IN 1 .. l_work_ids.COUNT LOOP
409 BEGIN
410 IF p_entity = G_ENTITY_TRIP THEN
411 OPEN c_lock_trip(l_work_ids(l_work_index));
412 FETCH c_lock_trip INTO l_dummy_id;
413 CLOSE c_lock_trip;
414 ELSIF p_entity = G_ENTITY_DEL THEN
415 OPEN c_lock_del(l_work_ids(l_work_index));
416 FETCH c_lock_del INTO l_dummy_id;
417 CLOSE c_lock_del;
418 ELSE
419 OPEN c_lock_detail(l_work_ids(l_work_index));
420 FETCH c_lock_detail INTO l_dummy_id;
421 CLOSE c_lock_detail;
422 END IF;
423
424 l_update_index := l_update_index + 1;
425 l_update_ids(l_update_index) := l_work_ids(l_work_index);
426 EXCEPTION
427 WHEN OTHERS THEN -- lock will raise exception
428 IF c_lock_trip%ISOPEN THEN CLOSE c_lock_trip; END IF;
429 IF c_lock_del%ISOPEN THEN CLOSE c_lock_del; END IF;
430 IF c_lock_detail%ISOPEN THEN CLOSE c_lock_detail; END IF;
431
432 x_count_nu := x_count_nu + 1;
433 -- this service needs to be processed in the next request.
434 x_sm_state := 'Y';
435 END;
436 END LOOP;
437 END IF;
438
439 IF (l_update_index >= G_BULK_SIZE)
440 OR ( (NOT l_continue) AND (l_update_index > 0) ) THEN
441 IF l_debug_on THEN
442 WSH_DEBUG_SV.log(l_module_name, 'l_update_index', l_update_index);
443 END IF;
444
445 IF p_entity = G_ENTITY_TRIP THEN
446 FORALL i IN 1 .. l_update_index
447 UPDATE WSH_TRIPS
448 SET service_level = p_service_level,
449 mode_of_transport = p_mode_of_transport,
450 last_updated_by = l_user_id,
451 last_update_date = sysdate,
452 last_update_login = l_login
453 WHERE trip_id = l_update_ids(i)
454 AND ship_method_code = p_ship_method_code
455 AND carrier_id = p_carrier_id;
456 ELSIF p_entity = G_ENTITY_DEL THEN
457 FORALL i IN 1 .. l_update_index
458 UPDATE WSH_NEW_DELIVERIES
459 SET service_level = p_service_level,
460 mode_of_transport = p_mode_of_transport,
461 last_updated_by = l_user_id,
462 last_update_date = sysdate,
463 last_update_login = l_login
464 WHERE delivery_id = l_update_ids(i)
465 AND ship_method_code = p_ship_method_code
466 AND carrier_id = p_carrier_id;
467 ELSE
468 FORALL i IN 1 .. l_update_index
469 UPDATE WSH_DELIVERY_DETAILS
470 SET service_level = p_service_level,
471 mode_of_transport = p_mode_of_transport,
472 last_updated_by = l_user_id,
473 last_update_date = sysdate,
474 last_update_login = l_login
475 WHERE delivery_detail_id = l_update_ids(i)
476 AND ship_method_code = p_ship_method_code
477 AND carrier_id = p_carrier_id;
478 END IF;
479 COMMIT;
480 x_count := x_count + l_update_index;
481 l_update_index := 0; -- instead of l_update_ids.DELETE.
482 END IF;
483
484 END LOOP;
485
486 IF c_current_trips%ISOPEN THEN CLOSE c_current_trips; END IF;
487 IF c_current_dels%ISOPEN THEN CLOSE c_current_dels; END IF;
488 IF c_current_details%ISOPEN THEN CLOSE c_current_details; END IF;
489
490 IF l_debug_on THEN
491 WSH_DEBUG_SV.log(l_module_name, 'out x_count', x_count);
492 WSH_DEBUG_SV.log(l_module_name, 'out x_count_nu', x_count_nu);
493 WSH_DEBUG_SV.log(l_module_name, 'out x_sm_state', x_sm_state);
494 WSH_DEBUG_SV.pop(l_module_name);
495 END IF;
496
497 EXCEPTION
498 WHEN OTHERS THEN
499 IF c_current_trips%ISOPEN THEN CLOSE c_current_trips; END IF;
500 IF c_current_dels%ISOPEN THEN CLOSE c_current_dels; END IF;
501 IF c_current_details%ISOPEN THEN CLOSE c_current_details; END IF;
502 WSH_UTIL_CORE.DEFAULT_HANDLER(l_module_name);
503 IF l_debug_on THEN
504 WSH_DEBUG_SV.logmsg(l_module_name,'OTHERS exception has occured.'
505 ,WSH_DEBUG_SV.C_EXCEP_LEVEL);
506 WSH_DEBUG_SV.log(l_module_name, 'out x_count', x_count);
507 WSH_DEBUG_SV.log(l_module_name, 'out x_count_nu', x_count_nu);
508 WSH_DEBUG_SV.log(l_module_name, 'out x_sm_state', x_sm_state);
509 WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION:OTHERS');
510 END IF;
511 RAISE;
512
513 END process_entity_sm;
514
515
516
517 -- Description: utility to lock/stamp WSH_CARRIER_SERVICES for ECO 5069719
518 --
519 -- if ship method is pending in an active record, this
520 -- will be treated as locked.
521 --
522 -- if the service record is found and can be locked,
523 -- update this service as pending this request.
524 --
525 -- non-lock exceptions will be raised to callers.
526 -- Parameters:
527 -- p_carrier_service_id = WSH_CARRIER_SERVICES record to update.
528 -- p_new_state = value to set UPDATE_MOT_SL:
529 -- 'P' -- to be processed by this request
530 -- 'Y' -- needs update, available for next request
531 -- NULL -- completely processed
532 FUNCTION lock_stamp_service(p_carrier_service_id IN NUMBER,
533 p_new_state IN VARCHAR2)
534 RETURN BOOLEAN IS
535
536 CURSOR c_lock_service(p_id NUMBER) IS
537 SELECT update_mot_sl,
538 request_id
539 FROM WSH_CARRIER_SERVICES
540 WHERE carrier_service_id = p_id
541 AND update_mot_sl IN ('Y', 'P')
542 FOR UPDATE NOWAIT;
543
544 CURSOR c_request_completed(p_id NUMBER) IS
545 SELECT 1
546 FROM fnd_concurrent_requests fcr
547 WHERE fcr.request_id = p_id
548 AND fcr.phase_code = 'C'
549 AND rownum = 1;
550
551 l_rec c_lock_service%ROWTYPE;
552 l_req_c c_request_completed%ROWTYPE;
553 l_found BOOLEAN;
554 --
555 l_debug_on BOOLEAN;
556 --
557 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_STAMP_SERVICE';
558
559 BEGIN
560 --
561 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
562 --
563 IF l_debug_on IS NULL
564 THEN
565 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
566 END IF;
567 --
568 IF l_debug_on THEN
569 WSH_DEBUG_SV.push(l_module_name);
570 WSH_DEBUG_SV.log(l_module_name,'p_carrier_service_id',
571 p_carrier_service_id);
572 WSH_DEBUG_SV.log(l_module_name,'p_new_state', p_new_state);
573 END IF;
574 --
575 SAVEPOINT sp_before_lock;
576 --
577 OPEN c_lock_service(p_carrier_service_id);
578 FETCH c_lock_service INTO l_rec;
579 l_found := c_lock_service%FOUND;
580 CLOSE c_lock_service;
581
582 IF l_found
583 AND l_rec.update_mot_sl = 'P'
584 AND l_rec.request_id <> fnd_global.conc_request_id THEN
585 -- if this service is pending, check whether its request is still running.
586
587 OPEN c_request_completed(l_rec.request_id);
588 FETCH c_request_completed INTO l_req_c;
589 l_found := c_request_completed%NOTFOUND;
590 CLOSE c_request_completed;
591
592 IF NOT l_found THEN
593 -- since this request is currently not completed; release lock.
594 ROLLBACK TO sp_before_lock;
595 END IF;
596 END IF;
597
598 IF l_found THEN
599 -- update this service for this request.
600 UPDATE WSH_CARRIER_SERVICES
601 SET update_mot_sl = p_new_state,
602 request_id = fnd_global.conc_request_id,
603 last_updated_by = fnd_global.user_id,
604 last_update_date = sysdate
605 WHERE carrier_service_id = p_carrier_service_id;
606 END IF;
607
608 IF l_debug_on THEN
609 WSH_DEBUG_SV.log(l_module_name, 'return value', l_found);
610 WSH_DEBUG_SV.pop(l_module_name);
611 END IF;
612 RETURN l_found;
613
614 EXCEPTION
615 WHEN app_exception.record_lock_exception THEN
616 IF c_lock_service%ISOPEN THEN CLOSE c_lock_service; END IF;
617 IF c_request_completed%ISOPEN THEN CLOSE c_request_completed; END IF;
618 IF l_debug_on THEN
619 WSH_DEBUG_SV.logmsg(l_module_name,
620 'APP_EXCEPTION.RECORD_LOCK_EXCEPTION exception has occured.',
621 WSH_DEBUG_SV.C_EXCEP_LEVEL);
622 WSH_DEBUG_SV.pop(l_module_name,
623 'EXCEPTION:APP_EXCEPTION.RECORD_LOCK_EXCEPTION');
624 END IF;
625 RETURN FALSE;
626
627 WHEN OTHERS THEN
628 IF c_lock_service%ISOPEN THEN CLOSE c_lock_service; END IF;
629 IF c_request_completed%ISOPEN THEN CLOSE c_request_completed; END IF;
630 WSH_UTIL_CORE.DEFAULT_HANDLER(l_module_name);
631 IF l_debug_on THEN
632 WSH_DEBUG_SV.logmsg(l_module_name,'OTHERS exception has occured.'
633 ,WSH_DEBUG_SV.C_EXCEP_LEVEL);
634 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
635 END IF;
636 RAISE;
637
638 END lock_stamp_service;
639
640
641 -- Description: Update Ship Method SRS for ECO 5069719
642 -- This will upgrade open shipping data with mode of transport
643 -- and service level that are entered on ship methods upgraded
644 -- from 11.0 or 10.7
645 -- PARAMETERS: errbuf Used by the concurrent program for error
646 -- messages.
647 -- retcode Used by the concurrent program for
648 -- return code.
649 PROCEDURE update_ship_method_SRS(
650 errbuf OUT NOCOPY VARCHAR2,
651 retcode OUT NOCOPY VARCHAR2)
652 IS
653
654 l_debug_on BOOLEAN;
655 l_module_name CONSTANT VARCHAR2(100) :=
656 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_SHIP_METHOD_SRS';
657
658 l_rc BOOLEAN;
659 l_completion_status VARCHAR2(30);
660 l_errbuf VARCHAR2(2000);
661 l_retcode VARCHAR2(2000);
662
663 -- internal procedure to write to the output file
664 -- if parameter is NULL, write the message from stack.
665 -- otherwise, if there is no message, a new line will be output.
666 PROCEDURE print_output(p_text IN VARCHAR2 DEFAULT NULL) IS
667 l_text VARCHAR2(2000);
668 BEGIN
669 l_text := p_text;
670
671 IF l_text IS NULL THEN
672 l_text := FND_MESSAGE.GET;
673 END IF;
674
675 IF l_text IS NOT NULL THEN
676 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_text);
677 ELSE
678 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
679 END IF;
680 END print_output;
681
682 BEGIN
683
684 WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
685
686 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
687 --
688 IF l_debug_on IS NULL
689 THEN
690 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
691 END IF;
692
693 IF l_debug_on THEN
694 WSH_DEBUG_SV.push(l_module_name);
695 END IF;
696
697 l_completion_status := 'NORMAL';
698 l_errbuf := '';
699 l_retcode := '';
700
701
702 -- this exception handling block contains the update SM logic
703 DECLARE
704 CURSOR c_ship_methods IS
705 SELECT carrier_service_id,
706 ship_method_code,
707 carrier_id,
708 service_level,
709 mode_of_transport
710 FROM WSH_CARRIER_SERVICES
711 WHERE UPDATE_MOT_SL IN ('Y', 'P');
712
713 -- inc = incompletely updated
714 -- nu = not updated because of locks
715
716 l_count_sm NUMBER;
717 l_count_sm_inc NUMBER;
718
719 l_count_trips NUMBER;
720 l_count_trips_nu NUMBER;
721
722 l_count_dels NUMBER;
723 l_count_dels_nu NUMBER;
724
725 l_count_dds NUMBER;
726 l_count_dds_nu NUMBER;
727
728 l_sm_state WSH_CARRIER_SERVICES.UPDATE_MOT_SL%TYPE;
729
730 BEGIN
731 l_count_sm := 0;
732 l_count_sm_inc := 0;
733 l_count_trips := 0;
734 l_count_trips_nu := 0;
735 l_count_dels := 0;
736 l_count_dels_nu := 0;
737 l_count_dds := 0;
738 l_count_dds_nu := 0;
739
740 FOR l_sm_rec IN c_ship_methods LOOP
741 IF lock_stamp_service(l_sm_rec.carrier_service_id, 'P') THEN
742 l_sm_state := NULL;
743
744 process_entity_sm(p_entity => G_ENTITY_TRIP,
745 p_ship_method_code => l_sm_rec.ship_method_code,
746 p_carrier_id => l_sm_rec.carrier_id,
747 p_service_level => l_sm_rec.service_level,
748 p_mode_of_transport => l_sm_rec.mode_of_transport,
749 x_count => l_count_trips,
750 x_count_nu => l_count_trips_nu,
751 x_sm_state => l_sm_state);
752
753 process_entity_sm(p_entity => G_ENTITY_DEL,
754 p_ship_method_code => l_sm_rec.ship_method_code,
755 p_carrier_id => l_sm_rec.carrier_id,
756 p_service_level => l_sm_rec.service_level,
757 p_mode_of_transport => l_sm_rec.mode_of_transport,
758 x_count => l_count_dels,
759 x_count_nu => l_count_dels_nu,
760 x_sm_state => l_sm_state);
761
762 process_entity_sm(p_entity => G_ENTITY_DETAIL,
763 p_ship_method_code => l_sm_rec.ship_method_code,
764 p_carrier_id => l_sm_rec.carrier_id,
765 p_service_level => l_sm_rec.service_level,
766 p_mode_of_transport => l_sm_rec.mode_of_transport,
767 x_count => l_count_dds,
768 x_count_nu => l_count_dds_nu,
769 x_sm_state => l_sm_state);
770
771 IF l_sm_state IS NULL THEN
772 -- service is fully updated
773 l_count_sm := l_count_sm + 1;
774 ELSE
775 -- at least one record could not be updated.
776 l_count_sm_inc := l_count_sm_inc + 1;
777 END IF;
778
779 -- ignore outcome; if this record cannot be updated,
780 -- the next request will process and update it.
781 IF lock_stamp_service(l_sm_rec.carrier_service_id, l_sm_state) THEN
782 COMMIT;
783 END IF;
784 END IF;
785 END LOOP;
786
787 IF l_count_sm_inc > 0 THEN
788 l_completion_status := 'WARNING';
789 END IF;
790
791 FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_SM_COUNTS');
792 FND_MESSAGE.SET_TOKEN('COUNT_PROCESSED', l_count_sm);
793 FND_MESSAGE.SET_TOKEN('COUNT_INCOMPLETE', l_count_sm_inc);
794 print_output;
795
796 FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_SM_TRIP_COUNTS');
797 FND_MESSAGE.SET_TOKEN('COUNT_UPDATED', l_count_trips);
798 FND_MESSAGE.SET_TOKEN('COUNT_NOT_UPDATED', l_count_trips_nu);
799 print_output;
800
801 FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_SM_DEL_COUNTS');
802 FND_MESSAGE.SET_TOKEN('COUNT_UPDATED', l_count_dels);
803 FND_MESSAGE.SET_TOKEN('COUNT_NOT_UPDATED', l_count_dels_nu);
804 print_output;
805
806 FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_SM_DD_COUNTS');
807 FND_MESSAGE.SET_TOKEN('COUNT_UPDATED', l_count_dds);
808 FND_MESSAGE.SET_TOKEN('COUNT_NOT_UPDATED', l_count_dds_nu);
809 print_output;
810
811 EXCEPTION
812 WHEN OTHERS THEN
813 WSH_UTIL_CORE.DEFAULT_HANDLER(l_module_name);
814
815 l_completion_status := 'ERROR';
816 l_errbuf := SQLERRM;
817 IF l_debug_on THEN
818 WSH_DEBUG_SV.log(l_module_name,
819 'Internal unexpected error has occured. Oracle error message',
820 l_errbuf,
821 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
822 END IF;
823 WSH_UTIL_CORE.printmsg(l_errbuf);
824
825 IF c_ship_methods%ISOPEN THEN CLOSE c_ship_methods; END IF;
826 ROLLBACK;
827 END;
828
829 IF l_completion_status IN ('WARNING', 'ERROR') THEN
830 FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_SM_TRY_AGAIN');
831 print_output;
832 END IF;
833
834 l_rc := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,
835 'update_ship_method_SRS');
836 errbuf := l_errbuf;
837 retcode := l_retcode;
838
839 IF l_debug_on THEN
840 WSH_DEBUG_SV.log(l_module_name,'COMPLETION STATUS', l_completion_status);
841 WSH_DEBUG_SV.log(l_module_name,'ERRBUF', l_errbuf);
842 WSH_DEBUG_SV.log(l_module_name,'RETCODE', l_retcode);
843 WSH_DEBUG_SV.pop(l_module_name);
844 END IF;
845
846
847 EXCEPTION
848 WHEN OTHERS THEN
849 WSH_UTIL_CORE.DEFAULT_HANDLER(l_module_name);
850
851 l_completion_status := 'ERROR';
852 l_errbuf := SQLERRM;
853 IF l_debug_on THEN
854 WSH_DEBUG_SV.log(l_module_name,
855 'API unexpected error has occured. Oracle error message',
856 l_errbuf,
857 WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
858 END IF;
859 WSH_UTIL_CORE.printmsg(l_errbuf);
860
861 l_rc := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,
862 'update_ship_method_SRS');
863 errbuf := l_errbuf;
864 retcode := l_retcode;
865
866 IF l_debug_on THEN
867 WSH_DEBUG_SV.log(l_module_name,'COMPLETION STATUS',
868 l_completion_status);
869 WSH_DEBUG_SV.log(l_module_name,'ERRBUF', l_errbuf);
870 WSH_DEBUG_SV.log(l_module_name,'RETCODE', l_retcode);
871 WSH_DEBUG_SV.pop(l_module_name, 'EXCEPTION:OTHERS');
872 END IF;
873 ROLLBACK;
874
875 END update_ship_method_SRS;
876
877
878
879 END WSH_CONC_UTIL_PKG;
880