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