[Home] [Help]
PACKAGE BODY: APPS.WSH_CARRIER_SERVICES_PKG
Source
1 PACKAGE BODY WSH_CARRIER_SERVICES_PKG as
2 /* $Header: WSHCVTHB.pls 120.4 2006/05/29 07:10:47 jnpinto noship $ */
3
4 --- Package Name: WSH_CARRIER_SERVICES_PKG
5 --- Pupose: Table Handlers for table WSH_CARRIER_SERVICES
6 --- Note: Please set tabstop=3 to read file with proper alignment
7
8 --
9 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_CARRIER_SERVICES_PKG';
10 --
11 PROCEDURE Create_Carrier_Service
12 (
13 p_Carrier_Service_Info IN CSRecType DEFAULT NULL
14 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
15 , x_Rowid OUT NOCOPY VARCHAR2
16 , x_Carrier_Service_id OUT NOCOPY NUMBER
17 , x_Return_Status OUT NOCOPY VARCHAR2
18 , x_position OUT NOCOPY NUMBER
19 , x_procedure OUT NOCOPY VARCHAR2
20 , x_sqlerr OUT NOCOPY VARCHAR2
21 , x_sql_code OUT NOCOPY VARCHAR2
22 )
23 IS
24
25 CURSOR C_Next_id
26 IS
27 SELECT wsh_carrier_services_s.nextval
28 FROM sys.dual;
29
30
31 CURSOR C_New_Rowid(p_carrier_service_id NUMBER)
32 IS
33 SELECT rowid
34 FROM WSH_CARRIER_SERVICES
35 WHERE carrier_service_id = p_carrier_service_id;
36
37 l_rowid rowid;
38 l_smc NUMBER;
39 no_data_found EXCEPTION;
40 l_err varchar2(100);
41
42 others EXCEPTION;
43 l_carrier_service_id NUMBER;
44
45 l_position NUMBER;
46 l_procedure VARCHAR2(50);
47
48
49 --
50 l_debug_on BOOLEAN;
51 --
52 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_CARRIER_SERVICE';
53 --
54 BEGIN
55 --
56 --
57 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
58 --
59 IF l_debug_on IS NULL
60 THEN
61 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
62 END IF;
63 --
64 IF l_debug_on THEN
65 WSH_DEBUG_SV.push(l_module_name);
66 WSH_DEBUG_SV.log(l_module_name,'Carrier_Service_ID',
67 p_Carrier_Service_Info.Carrier_Service_ID);
68 WSH_DEBUG_SV.log(l_module_name,'Ship_Method_Code',
69 p_Carrier_Service_Info.Ship_Method_Code);
70 WSH_DEBUG_SV.log(l_module_name,'Ship_Method_Meaning',
71 p_Carrier_Service_Info.Ship_Method_Meaning);
72 WSH_DEBUG_SV.log(l_module_name,'Service_level',
73 p_Carrier_Service_Info.Service_level);
74 WSH_DEBUG_SV.log(l_module_name,'min_sl_time',
75 p_Carrier_Service_Info.min_sl_time);
76 WSH_DEBUG_SV.log(l_module_name,'max_sl_time',
77 p_Carrier_Service_Info.max_sl_time);
78 WSH_DEBUG_SV.log(l_module_name,'sl_time_uom',
79 p_Carrier_Service_Info.sl_time_uom);
80 WSH_DEBUG_SV.log(l_module_name,'Attribute_Category',
81 p_Carrier_Service_Info.Attribute_Category);
82 WSH_DEBUG_SV.log(l_module_name,'attribute1',
83 p_Carrier_Service_Info.attribute1);
84 WSH_DEBUG_SV.log(l_module_name,'attribute2',
85 p_Carrier_Service_Info.attribute2);
86 WSH_DEBUG_SV.log(l_module_name,'attribute3',
87 p_Carrier_Service_Info.attribute3);
88 WSH_DEBUG_SV.log(l_module_name,'attribute4',
89 p_Carrier_Service_Info.attribute4);
90 WSH_DEBUG_SV.log(l_module_name,'attribute5',
91 p_Carrier_Service_Info.attribute5);
92 WSH_DEBUG_SV.log(l_module_name,'attribute6',
93 p_Carrier_Service_Info.attribute6);
94 WSH_DEBUG_SV.log(l_module_name,'attribute7',
95 p_Carrier_Service_Info.attribute7);
96 WSH_DEBUG_SV.log(l_module_name,'attribute8',
97 p_Carrier_Service_Info.attribute8);
98 WSH_DEBUG_SV.log(l_module_name,'attribute9',
99 p_Carrier_Service_Info.attribute9);
100 WSH_DEBUG_SV.log(l_module_name,'attribute10',
101 p_Carrier_Service_Info.attribute10);
102 WSH_DEBUG_SV.log(l_module_name,'attribute11',
103 p_Carrier_Service_Info.attribute11);
104 WSH_DEBUG_SV.log(l_module_name,'attribute12',
105 p_Carrier_Service_Info.attribute12);
106 WSH_DEBUG_SV.log(l_module_name,'attribute13',
107 p_Carrier_Service_Info.attribute13);
108 WSH_DEBUG_SV.log(l_module_name,'attribute14',
109 p_Carrier_Service_Info.attribute14);
110 WSH_DEBUG_SV.log(l_module_name,'attribute15',
111 p_Carrier_Service_Info.attribute15);
112 -- Pack J Enhancement
113
114 WSH_DEBUG_SV.log(l_module_name, 'MAX_NUM_STOPS_PERMITTED',
115 p_Carrier_Service_Info.MAX_NUM_STOPS_PERMITTED);
116 WSH_DEBUG_SV.log(l_module_name, 'MAX_TOTAL_DISTANCE',
117 p_Carrier_Service_Info.MAX_TOTAL_DISTANCE);
118 WSH_DEBUG_SV.log(l_module_name, 'MAX_TOTAL_TIME',
119 p_Carrier_Service_Info.MAX_TOTAL_TIME);
120 WSH_DEBUG_SV.log(l_module_name, 'ALLOW_INTERSPERSE_LOAD',
121 p_Carrier_Service_Info.ALLOW_INTERSPERSE_LOAD);
122 WSH_DEBUG_SV.log(l_module_name, 'MAX_LAYOVER_TIME',
123 p_Carrier_Service_Info.MAX_LAYOVER_TIME);
124 WSH_DEBUG_SV.log(l_module_name, 'MIN_LAYOVER_TIME',
125 p_Carrier_Service_Info.MIN_LAYOVER_TIME);
126 WSH_DEBUG_SV.log(l_module_name, 'MAX_TOTAL_DISTANCE_IN_24HR',
127 p_Carrier_Service_Info.MAX_TOTAL_DISTANCE_IN_24HR);
128 WSH_DEBUG_SV.log(l_module_name, 'MAX_DRIVING_TIME_IN_24HR',
129 p_Carrier_Service_Info.MAX_DRIVING_TIME_IN_24HR);
130 WSH_DEBUG_SV.log(l_module_name, 'MAX_DUTY_TIME_IN_24HR',
131 p_Carrier_Service_Info.MAX_DUTY_TIME_IN_24HR);
132 WSH_DEBUG_SV.log(l_module_name, 'MAX_CM_DISTANCE',
133 p_Carrier_Service_Info.MAX_CM_DISTANCE);
134 WSH_DEBUG_SV.log(l_module_name, 'MAX_CM_TIME',
135 p_Carrier_Service_Info.MAX_CM_TIME);
136 WSH_DEBUG_SV.log(l_module_name, 'MAX_CM_DH_DISTANCE',
137 p_Carrier_Service_Info.MAX_CM_DH_DISTANCE);
138 WSH_DEBUG_SV.log(l_module_name, 'MAX_SIZE_WIDTH',
139 p_Carrier_Service_Info.MAX_SIZE_WIDTH);
140 WSH_DEBUG_SV.log(l_module_name, 'MAX_SIZE_HEIGHT',
141 p_Carrier_Service_Info.MAX_SIZE_HEIGHT);
142 WSH_DEBUG_SV.log(l_module_name, 'MAX_SIZE_LENGTH',
143 p_Carrier_Service_Info.MAX_SIZE_LENGTH);
144 WSH_DEBUG_SV.log(l_module_name, 'MIN_SIZE_WIDTH',
145 p_Carrier_Service_Info.MIN_SIZE_WIDTH);
146 WSH_DEBUG_SV.log(l_module_name, 'MIN_SIZE_HEIGHT',
147 p_Carrier_Service_Info.MIN_SIZE_HEIGHT);
148 WSH_DEBUG_SV.log(l_module_name, 'MIN_SIZE_LENGTH',
149 p_Carrier_Service_Info.MIN_SIZE_LENGTH);
150 WSH_DEBUG_SV.log(l_module_name, 'MAX_OUT_OF_ROUTE',
151 p_Carrier_Service_Info.MAX_OUT_OF_ROUTE);
152 WSH_DEBUG_SV.log(l_module_name, 'CM_FREE_DH_MILEAGE',
153 p_Carrier_Service_Info.CM_FREE_DH_MILEAGE);
154 WSH_DEBUG_SV.log(l_module_name, 'MIN_CM_DISTANCE',
155 p_Carrier_Service_Info.MIN_CM_DISTANCE);
156 WSH_DEBUG_SV.log(l_module_name, 'CM_FIRST_LOAD_DISCOUNT',
157 p_Carrier_Service_Info.CM_FIRST_LOAD_DISCOUNT);
158 WSH_DEBUG_SV.log(l_module_name, 'MIN_CM_TIME',
159 p_Carrier_Service_Info.MIN_CM_TIME);
160 WSH_DEBUG_SV.log(l_module_name, 'UNIT_RATE_BASIS',
161 p_Carrier_Service_Info.UNIT_RATE_BASIS);
162 WSH_DEBUG_SV.log(l_module_name, 'CM_RATE_VARIANT',
163 p_Carrier_Service_Info.CM_RATE_VARIANT);
164 WSH_DEBUG_SV.log(l_module_name, 'DISTANCE_CALCULATION_METHOD',
165 p_Carrier_Service_Info.DISTANCE_CALCULATION_METHOD);
166 WSH_DEBUG_SV.log(l_module_name, 'ORIGIN_DSTN_SURCHARGE_LEVEL',
167 p_Carrier_Service_Info.ORIGIN_DSTN_SURCHARGE_LEVEL);
168 WSH_DEBUG_SV.log(l_module_name, 'ALLOW_CONTINUOUS_MOVE',
169 p_Carrier_Service_Info.ALLOW_CONTINUOUS_MOVE);
170 WSH_DEBUG_SV.log(l_module_name, 'MAX_CM_DH_TIME',
171 p_Carrier_Service_Info.MAX_CM_DH_TIME);
172 -- R12 Code changes
173 WSH_DEBUG_SV.log(l_module_name, 'DIM_DIMENSIONA_FACTOR',
174 p_Carrier_Service_Info.DIM_DIMENSIONAL_FACTOR);
175 WSH_DEBUG_SV.log(l_module_name, 'DIM_WEIGHT_UOM',
176 p_Carrier_Service_Info.DIM_WEIGHT_UOM);
177 WSH_DEBUG_SV.log(l_module_name, 'DIM_VOLUME_UOM',
178 p_Carrier_Service_Info.DIM_VOLUME_UOM);
179 WSH_DEBUG_SV.log(l_module_name, 'DIM_DIMENSION_UOM',
180 p_Carrier_Service_Info.DIM_DIMENSION_UOM);
181 WSH_DEBUG_SV.log(l_module_name, 'DIM_MIN_PACK_VOL',
182 p_Carrier_Service_Info.DIM_MIN_PACK_VOL);
183 WSH_DEBUG_SV.log(l_module_name, 'DEFAULT_VEHICLE_TYPE_ID',
184 p_Carrier_Service_Info.DEFAULT_VEHICLE_TYPE_ID);
185 -- R12 Code changes
186 WSH_DEBUG_SV.log(l_module_name, 'UPDATE_MOT_SL',
187 p_Carrier_Service_Info.UPDATE_MOT_SL);
188 END IF;
189 --
190 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
191 IF (l_Carrier_service_id is NULL) THEN
192 OPEN C_Next_id;
193 FETCH C_Next_id INTO l_Carrier_Service_Id;
194 CLOSE C_Next_id;
195 END IF;
196
197 Select count(*)
198 into l_smc
199 From fnd_lookup_values
203 WSH_DEBUG_SV.log(l_module_name,'l_Carrier_Service_Id',
200 Where lookup_type like 'SHIP_METHOD' and
201 lookup_code like p_Carrier_Service_Info.ship_method_code;
202 IF l_debug_on THEN
204 l_Carrier_Service_Id);
205 WSH_DEBUG_SV.log(l_module_name,'l_smc',l_smc);
206 END IF;
207
208 IF (l_smc = 0) then
209 l_position := 10;
210 l_procedure := 'Calling FND_LOOKUP_VALUE_PKG.INSERT_ROW';
211 FND_LOOKUP_VALUES_PKG.INSERT_ROW(
212 X_ROWID => l_rowid,
213 X_LOOKUP_TYPE => 'SHIP_METHOD',
214 X_SECURITY_GROUP_ID => 0,
215 X_VIEW_APPLICATION_ID =>3,
216 X_LOOKUP_CODE => p_Carrier_Service_Info.ship_method_code,
217 X_TAG => NULL,
218 X_ATTRIBUTE_CATEGORY => NULL,
219 X_ATTRIBUTE1 => NULL,
220 X_ATTRIBUTE2 => NULL,
221 X_ATTRIBUTE3 => NULL,
222 X_ATTRIBUTE4 => NULL,
223 X_ENABLED_FLAG => 'Y',
224 X_START_DATE_ACTIVE => SYSDATE,
225 X_END_DATE_ACTIVE => NULL,
226 X_TERRITORY_CODE => NULL,
227 X_ATTRIBUTE5 => NULL,
228 X_ATTRIBUTE6 => NULL,
229 X_ATTRIBUTE7 => NULL,
230 X_ATTRIBUTE8 => NULL,
231 X_ATTRIBUTE9 => NULL,
232 X_ATTRIBUTE10 => NULL,
233 X_ATTRIBUTE11 => NULL,
234 X_ATTRIBUTE12 => NULL,
235 X_ATTRIBUTE13 => NULL,
236 X_ATTRIBUTE14 => NULL,
237 X_ATTRIBUTE15 => NULL,
238 X_MEANING => p_Carrier_Service_Info.ship_method_meaning,
239 X_DESCRIPTION => p_Carrier_Service_Info.ship_method_meaning,
240 X_CREATION_DATE => sysdate,
241 X_CREATED_BY => FND_GLOBAL.USER_ID,
242 X_LAST_UPDATE_DATE => SYSDATE,
243 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
244 X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
245
246 END IF;
247
248 l_position := 20;
249 l_procedure := 'Inserting Into Wsh_Carrier_Services';
250
251 IF l_debug_on THEN
252 WSH_DEBUG_SV.log(l_module_name,'Inserting into wsh_carrier_services');
253 END IF;
254
255 INSERT INTO wsh_carrier_services(
256 carrier_service_id,
257 carrier_id,
258 mode_of_transport,
259 enabled_flag,
260 web_enabled,
261 service_level,
262 min_sl_time,
263 max_sl_time,
264 sl_time_uom,
265 ship_method_code,
266 ship_method_meaning,
267 attribute_category,
268 attribute1,
269 attribute2,
270 attribute3,
271 attribute4,
272 attribute5,
273 attribute6,
274 attribute7,
275 attribute8,
276 attribute9,
277 attribute10,
278 attribute11,
279 attribute12,
280 attribute13,
281 attribute14,
282 attribute15,
283 creation_date,
284 created_by,
285 last_update_date,
286 last_updated_by,
287 last_update_login,
288 -- Pack J Enhancement
289 MAX_NUM_STOPS_PERMITTED,
290 MAX_TOTAL_DISTANCE,
291 MAX_TOTAL_TIME,
292 ALLOW_INTERSPERSE_LOAD,
293 MAX_LAYOVER_TIME,
294 MIN_LAYOVER_TIME,
295 MAX_TOTAL_DISTANCE_IN_24HR,
296 MAX_DRIVING_TIME_IN_24HR,
297 MAX_DUTY_TIME_IN_24HR,
298 MAX_CM_DISTANCE,
299 MAX_CM_TIME,
300 MAX_CM_DH_DISTANCE,
301 MAX_SIZE_WIDTH,
302 MAX_SIZE_HEIGHT,
303 MAX_SIZE_LENGTH,
304 MIN_SIZE_WIDTH,
305 MIN_SIZE_HEIGHT,
306 MIN_SIZE_LENGTH,
307 MAX_OUT_OF_ROUTE,
308 CM_FREE_DH_MILEAGE,
309 MIN_CM_DISTANCE,
310 CM_FIRST_LOAD_DISCOUNT,
311 MIN_CM_TIME,
312 UNIT_RATE_BASIS,
313 CM_RATE_VARIANT,
314 DISTANCE_CALCULATION_METHOD,
315 ALLOW_CONTINUOUS_MOVE,
316 MAX_CM_DH_TIME,
317 ORIGIN_DSTN_SURCHARGE_LEVEL,
318 UPDATE_MOT_SL
319 )
320 VALUES (
321 l_carrier_service_id,
322 p_Carrier_Service_Info.Carrier_Id,
323 p_Carrier_Service_Info.mode_of_transport,
324 p_Carrier_Service_Info.Enabled_Flag,
325 p_Carrier_Service_Info.web_enabled,
326 p_Carrier_Service_Info.service_level,
327 p_Carrier_Service_Info.min_sl_time,
328 p_Carrier_Service_Info.max_sl_time,
329 p_Carrier_Service_Info.sl_time_uom,
330 p_Carrier_Service_Info.Ship_Method_Code,
331 p_Carrier_Service_Info.Ship_Method_Meaning,
332 p_Carrier_Service_Info.Attribute_Category,
333 p_Carrier_Service_Info.Attribute1,
334 p_Carrier_Service_Info.Attribute2,
338 p_Carrier_Service_Info.Attribute6,
335 p_Carrier_Service_Info.Attribute3,
336 p_Carrier_Service_Info.Attribute4,
337 p_Carrier_Service_Info.Attribute5,
339 p_Carrier_Service_Info.Attribute7,
340 p_Carrier_Service_Info.Attribute8,
341 p_Carrier_Service_Info.Attribute9,
342 p_Carrier_Service_Info.Attribute10,
343 p_Carrier_Service_Info.Attribute11,
344 p_Carrier_Service_Info.Attribute12,
345 p_Carrier_Service_Info.Attribute13,
346 p_Carrier_Service_Info.Attribute14,
347 p_Carrier_Service_Info.Attribute15,
348 p_Carrier_Service_Info.Creation_date,
349 p_Carrier_Service_Info.Created_By,
350 p_Carrier_Service_Info.Last_Update_Date,
351 p_Carrier_Service_Info.Last_Updated_By,
352 p_Carrier_Service_Info.Last_Update_Login,
353 -- Pack J Enhancement
354 p_Carrier_Service_Info.MAX_NUM_STOPS_PERMITTED,
355 p_Carrier_Service_Info.MAX_TOTAL_DISTANCE,
356 p_Carrier_Service_Info.MAX_TOTAL_TIME,
357 p_Carrier_Service_Info.ALLOW_INTERSPERSE_LOAD,
358 p_Carrier_Service_Info.MAX_LAYOVER_TIME,
359 p_Carrier_Service_Info.MIN_LAYOVER_TIME,
360 p_Carrier_Service_Info.MAX_TOTAL_DISTANCE_IN_24HR,
361 p_Carrier_Service_Info.MAX_DRIVING_TIME_IN_24HR,
362 p_Carrier_Service_Info.MAX_DUTY_TIME_IN_24HR,
363 p_Carrier_Service_Info.MAX_CM_DISTANCE,
364 p_Carrier_Service_Info.MAX_CM_TIME,
365 p_Carrier_Service_Info.MAX_CM_DH_DISTANCE,
366 p_Carrier_Service_Info.MAX_SIZE_WIDTH,
367 p_Carrier_Service_Info.MAX_SIZE_HEIGHT,
368 p_Carrier_Service_Info.MAX_SIZE_LENGTH,
369 p_Carrier_Service_Info.MIN_SIZE_WIDTH,
370 p_Carrier_Service_Info.MIN_SIZE_HEIGHT,
371 p_Carrier_Service_Info.MIN_SIZE_LENGTH,
372 p_Carrier_Service_Info.MAX_OUT_OF_ROUTE,
373 p_Carrier_Service_Info.CM_FREE_DH_MILEAGE,
374 p_Carrier_Service_Info.MIN_CM_DISTANCE,
375 p_Carrier_Service_Info.CM_FIRST_LOAD_DISCOUNT,
376 p_Carrier_Service_Info.MIN_CM_TIME,
377 p_Carrier_Service_Info.UNIT_RATE_BASIS,
378 p_Carrier_Service_Info.CM_RATE_VARIANT,
379 p_Carrier_Service_Info.DISTANCE_CALCULATION_METHOD,
380 p_Carrier_Service_Info.ALLOW_CONTINUOUS_MOVE,
381 p_Carrier_Service_Info.MAX_CM_DH_TIME,
382 p_Carrier_Service_Info.ORIGIN_DSTN_SURCHARGE_LEVEL,
383 p_Carrier_Service_Info.UPDATE_MOT_SL
384 );
385
386 l_position := 30;
387 l_procedure := 'Checking rowid Into Wsh_Carrier_Services';
388
389 OPEN C_New_Rowid(l_carrier_service_id);
390 FETCH C_New_Rowid INTO x_rowid;
391 IF (C_New_Rowid%NOTFOUND) THEN
392 CLOSE C_New_Rowid;
393 IF l_debug_on THEN
394 WSH_DEBUG_SV.log(l_module_name,'C_New_Rowid%NOTFOUND');
395 END IF;
396 RAISE others;
397 END IF;
398
399 x_carrier_service_id := l_carrier_service_id;
400
401 IF FND_API.To_Boolean(p_commit) THEN
402 COMMIT;
403 END IF;
404
405 IF l_debug_on THEN
406 WSH_DEBUG_SV.pop(l_module_name);
407 END IF;
408 --
412 X_position := l_position;
409 EXCEPTION
410 WHEN others THEN
411 X_Return_Status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
413 X_procedure := l_procedure;
414 X_SQLERR := SQLERRM;
415 X_sql_code := SQLCODE;
416 --
417 IF l_debug_on THEN
418 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
419 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
420 END IF;
421 --
422 END Create_Carrier_Service;
423
424 PROCEDURE Lock_Carrier_Service (
425 p_rowid IN VARCHAR2
426 , p_Carrier_Service_Info IN CSRecType DEFAULT NULL
427 , x_Return_Status OUT NOCOPY VARCHAR2
428 )
429 IS
430
431 CURSOR C_lock_row IS
432 -- SELECT *
433 SELECT
434 carrier_service_id,
435 carrier_id,
436 mode_of_transport,
437 enabled_flag,
438 web_enabled,
439 service_level,
440 min_sl_time,
441 max_sl_time,
442 sl_time_uom,
443 ship_method_code,
444 ship_method_meaning,
445 attribute_category,
446 attribute1,
447 attribute2,
448 attribute3,
449 attribute4,
450 attribute5,
451 attribute6,
452 attribute7,
453 attribute8,
454 attribute9,
455 attribute10,
456 attribute11,
457 attribute12,
458 attribute13,
459 attribute14,
460 attribute15,
461 creation_date,
462 created_by,
463 last_update_date,
464 last_updated_by,
465 last_update_login,
466 -- Pack J Enhancement
467 MAX_NUM_STOPS_PERMITTED,
468 MAX_TOTAL_DISTANCE,
469 MAX_TOTAL_TIME,
470 ALLOW_INTERSPERSE_LOAD,
471 MAX_LAYOVER_TIME,
472 MIN_LAYOVER_TIME,
473 MAX_TOTAL_DISTANCE_IN_24HR,
474 MAX_DRIVING_TIME_IN_24HR,
475 MAX_DUTY_TIME_IN_24HR,
476 MAX_CM_DISTANCE,
477 MAX_CM_TIME,
478 MAX_CM_DH_DISTANCE,
479 MAX_SIZE_WIDTH,
480 MAX_SIZE_HEIGHT,
481 MAX_SIZE_LENGTH,
482 MIN_SIZE_WIDTH,
483 MIN_SIZE_HEIGHT,
484 MIN_SIZE_LENGTH,
485 MAX_OUT_OF_ROUTE,
486 CM_FREE_DH_MILEAGE,
487 MIN_CM_DISTANCE,
488 CM_FIRST_LOAD_DISCOUNT,
489 MIN_CM_TIME,
490 UNIT_RATE_BASIS,
491 CM_RATE_VARIANT,
492 DISTANCE_CALCULATION_METHOD,
493 ALLOW_CONTINUOUS_MOVE,
494 MAX_CM_DH_TIME,
495 ORIGIN_DSTN_SURCHARGE_LEVEL,
496 UPDATE_MOT_SL
497 FROM wsh_carrier_services
498 WHERE rowid = p_rowid
499 FOR UPDATE of Carrier_Service_id NOWAIT;
500
501 CURSOR C_lookup_row IS
502 -- Bug#3330869
503 -- SELECT *
504 SELECT LOOKUP_CODE,
505 TAG,
506 ATTRIBUTE_CATEGORY,
507 ATTRIBUTE1,
508 ATTRIBUTE2,
509 ATTRIBUTE3,
510 ATTRIBUTE4,
511 START_DATE_ACTIVE,
512 END_DATE_ACTIVE,
513 TERRITORY_CODE,
514 ATTRIBUTE5,
515 ATTRIBUTE6,
516 ATTRIBUTE7,
517 ATTRIBUTE8,
518 ATTRIBUTE9,
519 ATTRIBUTE10,
520 ATTRIBUTE11,
521 ATTRIBUTE12,
522 ATTRIBUTE13,
523 ATTRIBUTE14,
524 ATTRIBUTE15,
525 MEANING,
526 DESCRIPTION
527 FROM FND_LOOKUP_VALUES
528 where LOOKUP_TYPE = 'SHIP_METHOD'
529 and SECURITY_GROUP_ID = 0
530 and VIEW_APPLICATION_ID = 3
531 and LOOKUP_CODE = p_Carrier_Service_Info.SHIP_METHOD_CODE;
532
533 Recinfo C_lock_row%ROWTYPE;
534 lookupinfo C_lookup_row%ROWTYPE;
535
536 record_locked EXCEPTION;
537 PRAGMA EXCEPTION_INIT(record_locked, -54);
538 others Exception;
539
540 --
541 l_debug_on BOOLEAN;
542 --
543 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_CARRIER_SERVICE';
544 --
545 BEGIN
546 --
547 --
548 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
549 --
550 IF l_debug_on IS NULL
551 THEN
552 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
553 END IF;
554 --
555 IF l_debug_on THEN
556 WSH_DEBUG_SV.push(l_module_name);
557 --
558 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
559 WSH_DEBUG_SV.log(l_module_name,'Carrier_Service_ID',
560 p_Carrier_Service_Info.Carrier_Service_ID);
561 WSH_DEBUG_SV.log(l_module_name,'Ship_Method_Code',
562 p_Carrier_Service_Info.Ship_Method_Code);
563 WSH_DEBUG_SV.log(l_module_name,'Ship_Method_Meaning',
564 p_Carrier_Service_Info.Ship_Method_Meaning);
565 WSH_DEBUG_SV.log(l_module_name,'Service_level',
566 p_Carrier_Service_Info.Service_level);
567 WSH_DEBUG_SV.log(l_module_name,'min_sl_time',
568 p_Carrier_Service_Info.min_sl_time);
569 WSH_DEBUG_SV.log(l_module_name,'max_sl_time',
570 p_Carrier_Service_Info.max_sl_time);
571 WSH_DEBUG_SV.log(l_module_name,'sl_time_uom',
575 WSH_DEBUG_SV.log(l_module_name,'attribute1',
572 p_Carrier_Service_Info.sl_time_uom);
573 WSH_DEBUG_SV.log(l_module_name,'Attribute_Category',
574 p_Carrier_Service_Info.Attribute_Category);
576 p_Carrier_Service_Info.attribute1);
577 WSH_DEBUG_SV.log(l_module_name,'attribute2',
578 p_Carrier_Service_Info.attribute2);
579 WSH_DEBUG_SV.log(l_module_name,'attribute3',
580 p_Carrier_Service_Info.attribute3);
581 WSH_DEBUG_SV.log(l_module_name,'attribute4',
582 p_Carrier_Service_Info.attribute4);
583 WSH_DEBUG_SV.log(l_module_name,'attribute5',
584 p_Carrier_Service_Info.attribute5);
585 WSH_DEBUG_SV.log(l_module_name,'attribute6',
586 p_Carrier_Service_Info.attribute6);
587 WSH_DEBUG_SV.log(l_module_name,'attribute7',
588 p_Carrier_Service_Info.attribute7);
589 WSH_DEBUG_SV.log(l_module_name,'attribute8',
590 p_Carrier_Service_Info.attribute8);
591 WSH_DEBUG_SV.log(l_module_name,'attribute9',
592 p_Carrier_Service_Info.attribute9);
593 WSH_DEBUG_SV.log(l_module_name,'attribute10',
594 p_Carrier_Service_Info.attribute10);
595 WSH_DEBUG_SV.log(l_module_name,'attribute11',
596 p_Carrier_Service_Info.attribute11);
597 WSH_DEBUG_SV.log(l_module_name,'attribute12',
598 p_Carrier_Service_Info.attribute12);
599 WSH_DEBUG_SV.log(l_module_name,'attribute13',
600 p_Carrier_Service_Info.attribute13);
601 WSH_DEBUG_SV.log(l_module_name,'attribute14',
602 p_Carrier_Service_Info.attribute14);
603 WSH_DEBUG_SV.log(l_module_name,'attribute15',
604 p_Carrier_Service_Info.attribute15);
605
606 -- Pack J Enhancement
607 WSH_DEBUG_SV.log(l_module_name, 'MAX_NUM_STOPS_PERMITTED',
608 p_Carrier_Service_Info.MAX_NUM_STOPS_PERMITTED);
609 WSH_DEBUG_SV.log(l_module_name, 'MAX_TOTAL_DISTANCE',
610 p_Carrier_Service_Info.MAX_TOTAL_DISTANCE);
611 WSH_DEBUG_SV.log(l_module_name, 'MAX_TOTAL_TIME',
612 p_Carrier_Service_Info.MAX_TOTAL_TIME);
613 WSH_DEBUG_SV.log(l_module_name, 'ALLOW_INTERSPERSE_LOAD',
614 p_Carrier_Service_Info.ALLOW_INTERSPERSE_LOAD);
615 WSH_DEBUG_SV.log(l_module_name, 'MAX_LAYOVER_TIME',
616 p_Carrier_Service_Info.MAX_LAYOVER_TIME);
617 WSH_DEBUG_SV.log(l_module_name, 'MIN_LAYOVER_TIME',
618 p_Carrier_Service_Info.MIN_LAYOVER_TIME);
619 WSH_DEBUG_SV.log(l_module_name, 'MAX_TOTAL_DISTANCE_IN_24HR',
620 p_Carrier_Service_Info.MAX_TOTAL_DISTANCE_IN_24HR);
621 WSH_DEBUG_SV.log(l_module_name, 'MAX_DRIVING_TIME_IN_24HR',
622 p_Carrier_Service_Info.MAX_DRIVING_TIME_IN_24HR);
623 WSH_DEBUG_SV.log(l_module_name, 'MAX_DUTY_TIME_IN_24HR',
624 p_Carrier_Service_Info.MAX_DUTY_TIME_IN_24HR);
625 WSH_DEBUG_SV.log(l_module_name, 'MAX_CM_DISTANCE',
626 p_Carrier_Service_Info.MAX_CM_DISTANCE);
627 WSH_DEBUG_SV.log(l_module_name, 'MAX_CM_TIME',
628 p_Carrier_Service_Info.MAX_CM_TIME);
629 WSH_DEBUG_SV.log(l_module_name, 'MAX_CM_DH_DISTANCE',
630 p_Carrier_Service_Info.MAX_CM_DH_DISTANCE);
631 WSH_DEBUG_SV.log(l_module_name, 'MAX_SIZE_WIDTH',
632 p_Carrier_Service_Info.MAX_SIZE_WIDTH);
633 WSH_DEBUG_SV.log(l_module_name, 'MAX_SIZE_HEIGHT',
634 p_Carrier_Service_Info.MAX_SIZE_HEIGHT);
635 WSH_DEBUG_SV.log(l_module_name, 'MAX_SIZE_LENGTH',
636 p_Carrier_Service_Info.MAX_SIZE_LENGTH);
637 WSH_DEBUG_SV.log(l_module_name, 'MIN_SIZE_WIDTH',
638 p_Carrier_Service_Info.MIN_SIZE_WIDTH);
639 WSH_DEBUG_SV.log(l_module_name, 'MIN_SIZE_HEIGHT',
640 p_Carrier_Service_Info.MIN_SIZE_HEIGHT);
641 WSH_DEBUG_SV.log(l_module_name, 'MIN_SIZE_LENGTH',
642 p_Carrier_Service_Info.MIN_SIZE_LENGTH);
643 WSH_DEBUG_SV.log(l_module_name, 'MAX_OUT_OF_ROUTE',
644 p_Carrier_Service_Info.MAX_OUT_OF_ROUTE);
645 WSH_DEBUG_SV.log(l_module_name, 'CM_FREE_DH_MILEAGE',
646 p_Carrier_Service_Info.CM_FREE_DH_MILEAGE);
647 WSH_DEBUG_SV.log(l_module_name, 'MIN_CM_DISTANCE',
648 p_Carrier_Service_Info.MIN_CM_DISTANCE);
649 WSH_DEBUG_SV.log(l_module_name, 'CM_FIRST_LOAD_DISCOUNT',
650 p_Carrier_Service_Info.CM_FIRST_LOAD_DISCOUNT);
651 WSH_DEBUG_SV.log(l_module_name, 'MIN_CM_TIME',
652 p_Carrier_Service_Info.MIN_CM_TIME);
653 WSH_DEBUG_SV.log(l_module_name, 'UNIT_RATE_BASIS',
654 p_Carrier_Service_Info.UNIT_RATE_BASIS);
655 WSH_DEBUG_SV.log(l_module_name, 'CM_RATE_VARIANT',
656 p_Carrier_Service_Info.CM_RATE_VARIANT);
657 WSH_DEBUG_SV.log(l_module_name, 'DISTANCE_CALCULATION_METHOD',
661 WSH_DEBUG_SV.log(l_module_name, 'ALLOW_CONTINUOUS_MOVE',
658 p_Carrier_Service_Info.DISTANCE_CALCULATION_METHOD);
659 WSH_DEBUG_SV.log(l_module_name, 'ORIGIN_DSTN_SURCHARGE_LEVEL',
660 p_Carrier_Service_Info.ORIGIN_DSTN_SURCHARGE_LEVEL);
662 p_Carrier_Service_Info.ALLOW_CONTINUOUS_MOVE);
663 WSH_DEBUG_SV.log(l_module_name, 'MAX_CM_DH_TIME',
664 p_Carrier_Service_Info.MAX_CM_DH_TIME);
665 -- R12 Code changes
666 WSH_DEBUG_SV.log(l_module_name, 'DIM_DIMENSIONA_FACTOR',
667 p_Carrier_Service_Info.DIM_DIMENSIONAL_FACTOR);
668 WSH_DEBUG_SV.log(l_module_name, 'DIM_WEIGHT_UOM',
669 p_Carrier_Service_Info.DIM_WEIGHT_UOM);
670 WSH_DEBUG_SV.log(l_module_name, 'DIM_VOLUME_UOM',
671 p_Carrier_Service_Info.DIM_VOLUME_UOM);
672 WSH_DEBUG_SV.log(l_module_name, 'DIM_DIMENSION_UOM',
673 p_Carrier_Service_Info.DIM_DIMENSION_UOM);
674 WSH_DEBUG_SV.log(l_module_name, 'DIM_MIN_PACK_VOL',
675 p_Carrier_Service_Info.DIM_MIN_PACK_VOL);
676 WSH_DEBUG_SV.log(l_module_name, 'DEFAULT_VEHICLE_TYPE_ID',
677 p_Carrier_Service_Info.DEFAULT_VEHICLE_TYPE_ID);
678 WSH_DEBUG_SV.log(l_module_name, 'UPDATE_MOT_SL',
679 p_Carrier_Service_Info.UPDATE_MOT_SL);
680 -- R12 Code changes
681
682 END IF;
683 --
684 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
685
686 OPEN C_lock_row;
687 FETCH C_lock_row INTO Recinfo;
688
689 IF (C_lock_row%NOTFOUND) THEN
690 CLOSE C_lock_row;
691 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
692 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
693 WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
694 --
695 IF l_debug_on THEN
696 WSH_DEBUG_SV.log(l_module_name,'FORM_RECORD_DELETED');
697 WSH_DEBUG_SV.pop(l_module_name);
698 END IF;
699 --
700 RETURN;
701 END IF;
702 CLOSE C_lock_row;
703 IF l_debug_on THEN
704 WSH_DEBUG_SV.log(l_module_name,'Carrier_Service_ID',
705 Recinfo.Carrier_Service_ID);
706 WSH_DEBUG_SV.log(l_module_name,'Ship_Method_Code',
707 Recinfo.Ship_Method_Code);
708 WSH_DEBUG_SV.log(l_module_name,'Ship_Method_Meaning',
709 Recinfo.Ship_Method_Meaning);
710 WSH_DEBUG_SV.log(l_module_name,'Service_level',
711 Recinfo.Service_level);
712 WSH_DEBUG_SV.log(l_module_name,'min_sl_time',
713 Recinfo.min_sl_time);
714 WSH_DEBUG_SV.log(l_module_name,'max_sl_time',
715 Recinfo.max_sl_time);
716 WSH_DEBUG_SV.log(l_module_name,'sl_time_uom',
717 Recinfo.sl_time_uom);
718 WSH_DEBUG_SV.log(l_module_name,'Attribute_Category',
719 Recinfo.Attribute_Category);
720 WSH_DEBUG_SV.log(l_module_name,'attribute1',
721 Recinfo.attribute1);
722 WSH_DEBUG_SV.log(l_module_name,'attribute2',
723 Recinfo.attribute2);
724 WSH_DEBUG_SV.log(l_module_name,'attribute3',
725 Recinfo.attribute3);
726 WSH_DEBUG_SV.log(l_module_name,'attribute4',
727 Recinfo.attribute4);
728 WSH_DEBUG_SV.log(l_module_name,'attribute5',
729 Recinfo.attribute5);
730 WSH_DEBUG_SV.log(l_module_name,'attribute6',
731 Recinfo.attribute6);
732 WSH_DEBUG_SV.log(l_module_name,'attribute7',
733 Recinfo.attribute7);
734 WSH_DEBUG_SV.log(l_module_name,'attribute8',
735 Recinfo.attribute8);
736 WSH_DEBUG_SV.log(l_module_name,'attribute9',
737 Recinfo.attribute9);
738 WSH_DEBUG_SV.log(l_module_name,'attribute10',
739 Recinfo.attribute10);
740 WSH_DEBUG_SV.log(l_module_name,'attribute11',
741 Recinfo.attribute11);
742 WSH_DEBUG_SV.log(l_module_name,'attribute12',
743 Recinfo.attribute12);
744 WSH_DEBUG_SV.log(l_module_name,'attribute13',
745 Recinfo.attribute13);
746 WSH_DEBUG_SV.log(l_module_name,'attribute14',
747 Recinfo.attribute14);
748 WSH_DEBUG_SV.log(l_module_name,'attribute15',
749 Recinfo.attribute15);
750 -- Pack J Enhancement
751 WSH_DEBUG_SV.log(l_module_name, 'MAX_NUM_STOPS_PERMITTED',
752 Recinfo.MAX_NUM_STOPS_PERMITTED);
753 WSH_DEBUG_SV.log(l_module_name, 'MAX_TOTAL_DISTANCE',
754 Recinfo.MAX_TOTAL_DISTANCE);
755 WSH_DEBUG_SV.log(l_module_name, 'MAX_TOTAL_TIME',
756 Recinfo.MAX_TOTAL_TIME);
757 WSH_DEBUG_SV.log(l_module_name, 'ALLOW_INTERSPERSE_LOAD',
758 Recinfo.ALLOW_INTERSPERSE_LOAD);
759 WSH_DEBUG_SV.log(l_module_name, 'MAX_LAYOVER_TIME',
760 Recinfo.MAX_LAYOVER_TIME);
764 Recinfo.MAX_TOTAL_DISTANCE_IN_24HR);
761 WSH_DEBUG_SV.log(l_module_name, 'MIN_LAYOVER_TIME',
762 Recinfo.MIN_LAYOVER_TIME);
763 WSH_DEBUG_SV.log(l_module_name, 'MAX_TOTAL_DISTANCE_IN_24HR',
765 WSH_DEBUG_SV.log(l_module_name, 'MAX_DRIVING_TIME_IN_24HR',
766 Recinfo.MAX_DRIVING_TIME_IN_24HR);
767 WSH_DEBUG_SV.log(l_module_name, 'MAX_DUTY_TIME_IN_24HR',
768 Recinfo.MAX_DUTY_TIME_IN_24HR);
769 WSH_DEBUG_SV.log(l_module_name, 'MAX_CM_DISTANCE',
770 Recinfo.MAX_CM_DISTANCE);
771 WSH_DEBUG_SV.log(l_module_name, 'MAX_CM_TIME',
772 Recinfo.MAX_CM_TIME);
773 WSH_DEBUG_SV.log(l_module_name, 'MAX_CM_DH_DISTANCE',
774 Recinfo.MAX_CM_DH_DISTANCE);
775 WSH_DEBUG_SV.log(l_module_name, 'MAX_SIZE_WIDTH',
776 Recinfo.MAX_SIZE_WIDTH);
777 WSH_DEBUG_SV.log(l_module_name, 'MAX_SIZE_HEIGHT',
778 Recinfo.MAX_SIZE_HEIGHT);
779 WSH_DEBUG_SV.log(l_module_name, 'MAX_SIZE_LENGTH',
780 Recinfo.MAX_SIZE_LENGTH);
781 WSH_DEBUG_SV.log(l_module_name, 'MIN_SIZE_WIDTH',
782 Recinfo.MIN_SIZE_WIDTH);
783 WSH_DEBUG_SV.log(l_module_name, 'MIN_SIZE_HEIGHT',
784 Recinfo.MIN_SIZE_HEIGHT);
785 WSH_DEBUG_SV.log(l_module_name, 'MIN_SIZE_LENGTH',
786 Recinfo.MIN_SIZE_LENGTH);
787 WSH_DEBUG_SV.log(l_module_name, 'MAX_OUT_OF_ROUTE',
788 Recinfo.MAX_OUT_OF_ROUTE);
789 WSH_DEBUG_SV.log(l_module_name, 'CM_FREE_DH_MILEAGE',
790 Recinfo.CM_FREE_DH_MILEAGE);
791 WSH_DEBUG_SV.log(l_module_name, 'MIN_CM_DISTANCE',
792 Recinfo.MIN_CM_DISTANCE);
793 WSH_DEBUG_SV.log(l_module_name, 'CM_FIRST_LOAD_DISCOUNT',
794 Recinfo.CM_FIRST_LOAD_DISCOUNT);
795 WSH_DEBUG_SV.log(l_module_name, 'MIN_CM_TIME',
796 Recinfo.MIN_CM_TIME);
797 WSH_DEBUG_SV.log(l_module_name, 'UNIT_RATE_BASIS',
798 Recinfo.UNIT_RATE_BASIS);
799 WSH_DEBUG_SV.log(l_module_name, 'CM_RATE_VARIANT',
800 Recinfo.CM_RATE_VARIANT);
801 WSH_DEBUG_SV.log(l_module_name, 'DISTANCE_CALCULATION_METHOD',
802 Recinfo.DISTANCE_CALCULATION_METHOD);
803 WSH_DEBUG_SV.log(l_module_name, 'ORIGIN_DSTN_SURCHARGE_LEVEL',
804 Recinfo.ORIGIN_DSTN_SURCHARGE_LEVEL);
805 WSH_DEBUG_SV.log(l_module_name, 'MAX_CM_DH_TIME',
806 Recinfo.MAX_CM_DH_TIME);
807 WSH_DEBUG_SV.log(l_module_name, 'ALLOW_CONTINUOUS_MOVE',
808 Recinfo.ALLOW_CONTINUOUS_MOVE);
809 END IF;
810 --
811 IF ( (Recinfo.Carrier_Service_Id = p_Carrier_Service_Info.Carrier_Service_Id)
812 AND (Recinfo.Ship_Method_Code = p_Carrier_Service_Info.Ship_Method_Code)
813 AND ( (Recinfo.Service_level = p_Carrier_Service_Info.Service_level)
814 OR ( (Recinfo.Service_level is NULL)
815 AND (p_Carrier_Service_Info.Service_level IS NULL)))
816 AND ( (Recinfo.min_sl_time = p_Carrier_Service_Info.min_sl_time)
817 OR ( (Recinfo.min_sl_time is NULL)
818 AND (p_Carrier_Service_Info.min_sl_time IS NULL)))
819 AND ( (Recinfo.max_sl_time = p_Carrier_Service_Info.max_sl_time)
820 OR ( (Recinfo.max_sl_time is NULL)
821 AND (p_Carrier_Service_Info.max_sl_time IS NULL)))
822 AND ( (Recinfo.sl_time_uom = p_Carrier_Service_Info.sl_time_uom)
823 OR ( (Recinfo.sl_time_uom is NULL)
824 AND (p_Carrier_Service_Info.sl_time_uom IS NULL)))
825 AND (Recinfo.Enabled_Flag = p_Carrier_Service_Info.Enabled_Flag)
826 AND ( (Recinfo.Attribute_Category = p_Carrier_Service_Info.Attribute_Category)
827 OR ( (Recinfo.Attribute_Category is NULL)
828 AND (p_Carrier_Service_Info.Attribute_Category IS NULL)))
829 AND ( (Recinfo.Attribute1 = p_Carrier_Service_Info.Attribute1)
830 OR ( (Recinfo.Attribute1 IS NULL)
831 AND (p_Carrier_Service_Info.Attribute1 is NULL)))
832 AND ( (Recinfo.Attribute2 = p_Carrier_Service_Info.Attribute2)
833 OR ( (Recinfo.Attribute2 IS NULL)
834 AND (p_Carrier_Service_Info.Attribute2 is NULL)))
835 AND ( (Recinfo.Attribute3 = p_Carrier_Service_Info.Attribute3)
836 OR ( (Recinfo.Attribute3 IS NULL)
837 AND (p_Carrier_Service_Info.Attribute3 is NULL)))
838 AND ( (Recinfo.Attribute4 = p_Carrier_Service_Info.Attribute4)
839 OR ( (Recinfo.Attribute4 IS NULL)
840 AND (p_Carrier_Service_Info.Attribute4 is NULL)))
841 AND ( (Recinfo.Attribute5 = p_Carrier_Service_Info.Attribute5)
842 OR ( (Recinfo.Attribute5 IS NULL)
843 AND (p_Carrier_Service_Info.Attribute5 is NULL)))
844 AND ( (Recinfo.Attribute6 = p_Carrier_Service_Info.Attribute6)
845 OR ( (Recinfo.Attribute6 IS NULL)
846 AND (p_Carrier_Service_Info.Attribute6 is NULL)))
847 AND ( (Recinfo.Attribute7 = p_Carrier_Service_Info.Attribute7)
848 OR ( (Recinfo.Attribute7 IS NULL)
849 AND (p_Carrier_Service_Info.Attribute7 is NULL)))
853 AND ( (Recinfo.Attribute9 = p_Carrier_Service_Info.Attribute9)
850 AND ( (Recinfo.Attribute8 = p_Carrier_Service_Info.Attribute8)
851 OR ( (Recinfo.Attribute8 IS NULL)
852 AND (p_Carrier_Service_Info.Attribute8 is NULL)))
854 OR ( (Recinfo.Attribute9 IS NULL)
855 AND (p_Carrier_Service_Info.Attribute9 is NULL)))
856 AND ( (Recinfo.Attribute10 = p_Carrier_Service_Info.Attribute10)
857 OR ( (Recinfo.Attribute10 IS NULL)
858 AND (p_Carrier_Service_Info.Attribute10 is NULL)))
859 AND ( (Recinfo.Attribute11 = p_Carrier_Service_Info.Attribute11)
860 OR ( (Recinfo.Attribute11 IS NULL)
861 AND (p_Carrier_Service_Info.Attribute11 is NULL)))
862 AND ( (Recinfo.Attribute12 = p_Carrier_Service_Info.Attribute12)
863 OR ( (Recinfo.Attribute12 IS NULL)
864 AND (p_Carrier_Service_Info.Attribute12 is NULL)))
865 AND ( (Recinfo.Attribute13 = p_Carrier_Service_Info.Attribute13)
866 OR ( (Recinfo.Attribute13 IS NULL)
867 AND (p_Carrier_Service_Info.Attribute13 is NULL)))
868 AND ( (Recinfo.Attribute14 = p_Carrier_Service_Info.Attribute14)
869 OR ( (Recinfo.Attribute14 IS NULL)
870 AND (p_Carrier_Service_Info.Attribute14 is NULL)))
871 AND ( (Recinfo.Attribute15 = p_Carrier_Service_Info.Attribute15)
872 OR ( (Recinfo.Attribute15 IS NULL)
873 AND (p_Carrier_Service_Info.Attribute15 is NULL)))
874 AND (Recinfo.Web_Enabled = p_Carrier_Service_Info.Web_Enabled)
875 AND ( (Recinfo.Mode_of_Transport = p_Carrier_Service_Info.Mode_of_Transport)
876 OR ( (Recinfo.Mode_of_Transport is NULL)
877 AND (p_Carrier_Service_Info.Mode_of_Transport IS NULL)))
878 -- Pack J enhancement
879 AND ((Recinfo.MAX_NUM_STOPS_PERMITTED = p_Carrier_Service_Info.MAX_NUM_STOPS_PERMITTED)
880 OR ((Recinfo.MAX_NUM_STOPS_PERMITTED IS NULL)
881 AND (p_Carrier_Service_Info.MAX_NUM_STOPS_PERMITTED is NULL)))
882 AND ((Recinfo.MAX_TOTAL_DISTANCE = p_Carrier_Service_Info.MAX_TOTAL_DISTANCE)
883 OR ((Recinfo.MAX_TOTAL_DISTANCE IS NULL)
884 AND (p_Carrier_Service_Info.MAX_TOTAL_DISTANCE is NULL)))
885 AND ((Recinfo.MAX_TOTAL_TIME = p_Carrier_Service_Info.MAX_TOTAL_TIME)
886 OR ((Recinfo.MAX_TOTAL_TIME IS NULL)
887 AND (p_Carrier_Service_Info.MAX_TOTAL_TIME is NULL)))
888 AND ((Recinfo.ALLOW_INTERSPERSE_LOAD = p_Carrier_Service_Info.ALLOW_INTERSPERSE_LOAD)
889 OR ((Recinfo.ALLOW_INTERSPERSE_LOAD IS NULL)
890 AND (p_Carrier_Service_Info.ALLOW_INTERSPERSE_LOAD is NULL)))
891 AND ((Recinfo.MAX_LAYOVER_TIME = p_Carrier_Service_Info.MAX_LAYOVER_TIME )
892 OR ((Recinfo.MAX_LAYOVER_TIME IS NULL)
893 AND (p_Carrier_Service_Info.MAX_LAYOVER_TIME is NULL)))
894 AND ((Recinfo.MIN_LAYOVER_TIME = p_Carrier_Service_Info.MIN_LAYOVER_TIME )
895 OR ((Recinfo.MIN_LAYOVER_TIME IS NULL)
896 AND (p_Carrier_Service_Info.MIN_LAYOVER_TIME is NULL)))
897 AND ((Recinfo.MAX_TOTAL_DISTANCE_IN_24HR = p_Carrier_Service_Info.MAX_TOTAL_DISTANCE_IN_24HR)
898 OR ((Recinfo.MAX_TOTAL_DISTANCE_IN_24HR IS NULL)
899 AND (p_Carrier_Service_Info.MAX_TOTAL_DISTANCE_IN_24HR is NULL)))
900 AND ((Recinfo.MAX_DRIVING_TIME_IN_24HR = p_Carrier_Service_Info.MAX_DRIVING_TIME_IN_24HR)
901 OR ((Recinfo.MAX_DRIVING_TIME_IN_24HR IS NULL)
902 AND (p_Carrier_Service_Info.MAX_DRIVING_TIME_IN_24HR is NULL)))
903 AND ((Recinfo.MAX_DUTY_TIME_IN_24HR = p_Carrier_Service_Info.MAX_DUTY_TIME_IN_24HR )
904 OR ((Recinfo.MAX_DUTY_TIME_IN_24HR IS NULL)
905 AND (p_Carrier_Service_Info.MAX_DUTY_TIME_IN_24HR is NULL)))
906 AND ((Recinfo.MAX_CM_DISTANCE = p_Carrier_Service_Info.MAX_CM_DISTANCE )
907 OR ((Recinfo.MAX_CM_DISTANCE IS NULL)
908 AND (p_Carrier_Service_Info.MAX_CM_DISTANCE is NULL)))
909 AND ((Recinfo.MAX_CM_TIME = p_Carrier_Service_Info.MAX_CM_TIME)
910 OR ((Recinfo.MAX_CM_TIME IS NULL)
911 AND (p_Carrier_Service_Info.MAX_CM_TIME is NULL)))
912 AND ((Recinfo.MAX_CM_DH_DISTANCE = p_Carrier_Service_Info.MAX_CM_DH_DISTANCE)
913 OR ((Recinfo.MAX_CM_DH_DISTANCE IS NULL)
914 AND (p_Carrier_Service_Info.MAX_CM_DH_DISTANCE is NULL)))
915 AND ((Recinfo.MAX_SIZE_WIDTH = p_Carrier_Service_Info.MAX_SIZE_WIDTH )
916 OR ((Recinfo.MAX_SIZE_WIDTH IS NULL)
917 AND (p_Carrier_Service_Info.MAX_SIZE_WIDTH is NULL)))
918 AND ((Recinfo.MAX_SIZE_HEIGHT = p_Carrier_Service_Info.MAX_SIZE_HEIGHT)
919 OR ((Recinfo.MAX_SIZE_HEIGHT IS NULL)
920 AND (p_Carrier_Service_Info.MAX_SIZE_HEIGHT is NULL)))
921 AND ((Recinfo.MAX_SIZE_LENGTH = p_Carrier_Service_Info.MAX_SIZE_LENGTH )
922 OR ((Recinfo.MAX_SIZE_LENGTH IS NULL)
923 AND (p_Carrier_Service_Info.MAX_SIZE_LENGTH is NULL)))
924 AND ((Recinfo.MIN_SIZE_WIDTH = p_Carrier_Service_Info.MIN_SIZE_WIDTH)
925 OR ((Recinfo.MIN_SIZE_WIDTH IS NULL)
926 AND (p_Carrier_Service_Info.MIN_SIZE_WIDTH is NULL)))
927 AND ((Recinfo.MIN_SIZE_HEIGHT = p_Carrier_Service_Info.MIN_SIZE_HEIGHT)
928 OR ((Recinfo.MIN_SIZE_HEIGHT IS NULL)
929 AND (p_Carrier_Service_Info.MIN_SIZE_HEIGHT is NULL)))
930 AND ((Recinfo.MIN_SIZE_LENGTH = p_Carrier_Service_Info.MIN_SIZE_LENGTH)
931 OR ((Recinfo.MIN_SIZE_LENGTH IS NULL)
932 AND (p_Carrier_Service_Info.MIN_SIZE_LENGTH is NULL)))
936 AND ((Recinfo.CM_FREE_DH_MILEAGE = p_Carrier_Service_Info.CM_FREE_DH_MILEAGE)
933 AND ((Recinfo.MAX_OUT_OF_ROUTE = p_Carrier_Service_Info.MAX_OUT_OF_ROUTE)
934 OR ((Recinfo.MAX_OUT_OF_ROUTE IS NULL)
935 AND (p_Carrier_Service_Info.MAX_OUT_OF_ROUTE is NULL)))
937 OR ((Recinfo.CM_FREE_DH_MILEAGE IS NULL)
938 AND (p_Carrier_Service_Info.CM_FREE_DH_MILEAGE is NULL)))
939 AND ((Recinfo.MIN_CM_DISTANCE = p_Carrier_Service_Info.MIN_CM_DISTANCE)
940 OR ((Recinfo.MIN_CM_DISTANCE IS NULL)
941 AND (p_Carrier_Service_Info.MIN_CM_DISTANCE is NULL)))
942 AND ((Recinfo.CM_FIRST_LOAD_DISCOUNT = p_Carrier_Service_Info.CM_FIRST_LOAD_DISCOUNT)
943 OR ((Recinfo.CM_FIRST_LOAD_DISCOUNT IS NULL)
944 AND (p_Carrier_Service_Info.CM_FIRST_LOAD_DISCOUNT is NULL)))
945 AND ((Recinfo.MIN_CM_TIME = p_Carrier_Service_Info.MIN_CM_TIME)
946 OR ((Recinfo.MIN_CM_TIME IS NULL)
947 AND (p_Carrier_Service_Info.MIN_CM_TIME is NULL)))
948 AND ((Recinfo.UNIT_RATE_BASIS = p_Carrier_Service_Info.UNIT_RATE_BASIS)
949 OR ((Recinfo.UNIT_RATE_BASIS IS NULL)
950 AND (p_Carrier_Service_Info.UNIT_RATE_BASIS is NULL)))
951 AND ((Recinfo.DISTANCE_CALCULATION_METHOD = p_Carrier_Service_Info.DISTANCE_CALCULATION_METHOD)
952 OR ((Recinfo.DISTANCE_CALCULATION_METHOD IS NULL)
953 AND (p_Carrier_Service_Info.DISTANCE_CALCULATION_METHOD is NULL)))
954 AND ((Recinfo.CM_RATE_VARIANT = p_Carrier_Service_Info.CM_RATE_VARIANT)
955 OR ((Recinfo.CM_RATE_VARIANT IS NULL)
956 AND (p_Carrier_Service_Info.CM_RATE_VARIANT is NULL)))
957 AND ((Recinfo.ALLOW_CONTINUOUS_MOVE = p_Carrier_Service_Info.ALLOW_CONTINUOUS_MOVE)
958 OR ((Recinfo.ALLOW_CONTINUOUS_MOVE IS NULL)
959 AND (p_Carrier_Service_Info.ALLOW_CONTINUOUS_MOVE is NULL)))
960 AND ((Recinfo.MAX_CM_DH_TIME = p_Carrier_Service_Info.MAX_CM_DH_TIME)
961 OR ((Recinfo.MAX_CM_DH_TIME IS NULL)
962 AND (p_Carrier_Service_Info.MAX_CM_DH_TIME is NULL)))
963 AND ((Recinfo.ORIGIN_DSTN_SURCHARGE_LEVEL = p_Carrier_Service_Info.ORIGIN_DSTN_SURCHARGE_LEVEL)
964 OR ((Recinfo.ORIGIN_DSTN_SURCHARGE_LEVEL IS NULL)
965 AND (p_Carrier_Service_Info.ORIGIN_DSTN_SURCHARGE_LEVEL is NULL)))
966 )
967 AND ((Recinfo.UPDATE_MOT_SL = p_Carrier_Service_Info.UPDATE_MOT_SL)
968 OR ((Recinfo.UPDATE_MOT_SL IS NULL)
969 AND (p_Carrier_Service_Info.UPDATE_MOT_SL is NULL)))
970 THEN
971 --
972 IF l_debug_on THEN
973 WSH_DEBUG_SV.log(l_module_name,'All matched');
974 WSH_DEBUG_SV.pop(l_module_name);
975 END IF;
976 --
977 RETURN;
978 ELSE
979 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
980 IF l_debug_on THEN
981 WSH_DEBUG_SV.log(l_module_name,'FORM_RECORD_CHANGED');
982 END IF;
983 APP_EXCEPTION.Raise_Exception;
984 END IF;
985
986 OPEN C_lookup_row;
987 FETCH C_lookup_row INTO lookupinfo;
988
989 IF (C_lookup_row%NOTFOUND) THEN
990 CLOSE C_lookup_row;
991 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
992 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
993 WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
994 --
995 IF l_debug_on THEN
996 WSH_DEBUG_SV.log(l_module_name,'FORM_RECORD_DELETED');
997 WSH_DEBUG_SV.pop(l_module_name);
998 END IF;
999 RETURN;
1000 END IF;
1001 CLOSE C_lookup_row;
1002 IF l_debug_on THEN
1003 WSH_DEBUG_SV.log(l_module_name,'Before FND_LOOKUP_VALUES_PKG.LOCK_ROW');
1004 END IF;
1005
1006 FND_LOOKUP_VALUES_PKG.LOCK_ROW(
1007 X_LOOKUP_TYPE => 'SHIP_METHOD',
1008 X_SECURITY_GROUP_ID => 0,
1009 X_VIEW_APPLICATION_ID => 3,
1010 X_LOOKUP_CODE => lookupinfo.LOOKUP_CODE,
1011 X_TAG => lookupinfo.TAG,
1012 X_ATTRIBUTE_CATEGORY => lookupinfo.ATTRIBUTE_CATEGORY,
1013 X_ATTRIBUTE1 => lookupinfo.ATTRIBUTE1,
1014 X_ATTRIBUTE2 => lookupinfo.ATTRIBUTE2,
1015 X_ATTRIBUTE3 => lookupinfo.ATTRIBUTE3,
1016 X_ATTRIBUTE4 => lookupinfo.ATTRIBUTE4,
1017 X_ENABLED_FLAG => p_Carrier_Service_Info.Enabled_Flag,
1018 X_START_DATE_ACTIVE => lookupinfo.START_DATE_ACTIVE,
1019 X_END_DATE_ACTIVE => lookupinfo.END_DATE_ACTIVE,
1020 X_TERRITORY_CODE => lookupinfo.TERRITORY_CODE,
1021 X_ATTRIBUTE5 => lookupinfo.ATTRIBUTE5,
1022 X_ATTRIBUTE6 => lookupinfo.ATTRIBUTE6,
1023 X_ATTRIBUTE7 => lookupinfo.ATTRIBUTE7,
1024 X_ATTRIBUTE8 => lookupinfo.ATTRIBUTE8,
1025 X_ATTRIBUTE9 => lookupinfo.ATTRIBUTE9,
1026 X_ATTRIBUTE10 => lookupinfo.ATTRIBUTE10,
1027 X_ATTRIBUTE11 => lookupinfo.ATTRIBUTE11,
1028 X_ATTRIBUTE12 => lookupinfo.ATTRIBUTE12,
1029 X_ATTRIBUTE13 => lookupinfo.ATTRIBUTE13,
1030 X_ATTRIBUTE14 => lookupinfo.ATTRIBUTE14,
1031 X_ATTRIBUTE15 => lookupinfo.ATTRIBUTE15,
1032 X_MEANING => lookupinfo.MEANING,
1033 X_DESCRIPTION => lookupinfo.DESCRIPTION);
1034
1035
1036 IF l_debug_on THEN
1037 WSH_DEBUG_SV.pop(l_module_name);
1038 END IF;
1039 --
1040 EXCEPTION
1041
1042 WHEN RECORD_LOCKED THEN
1043
1044 IF (C_Lock_Row%ISOPEN) THEN
1045 CLOSE C_Lock_Row;
1046 END IF;
1047
1048 IF (C_lookup_row%ISOPEN) THEN
1049 CLOSE C_lookup_row;
1050 END IF;
1051
1052 FND_MESSAGE.Set_Name('WSH', 'WSH_FORM_RECORD_IS_CHANGED');
1053 app_exception.raise_exception;
1057 IF (C_Lock_Row%ISOPEN) THEN
1054
1055 WHEN others THEN
1056
1058 CLOSE C_Lock_Row;
1059 END IF;
1060
1061 IF (C_lookup_row%ISOPEN) THEN
1062 CLOSE C_lookup_row;
1063 END IF;
1064
1065 x_Return_Status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1066 WSH_UTIL_CORE.Default_Handler('WSH_CARRIER_SERVICES_PKG.Lock_Carrier_Service',l_module_name);
1067 --
1068 IF l_debug_on THEN
1069 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1070 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1071 END IF;
1072 --
1073 END Lock_Carrier_Service;
1074
1075 PROCEDURE Update_Carrier_Service
1076 (
1077 p_rowid IN VARCHAR2
1078 , p_Carrier_Service_Info IN CSRecType DEFAULT NULL
1079 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
1080 , x_Return_Status OUT NOCOPY VARCHAR2
1081 , x_position OUT NOCOPY NUMBER
1082 , x_procedure OUT NOCOPY VARCHAR2
1083 , x_sqlerr OUT NOCOPY VARCHAR2
1084 , x_sql_code OUT NOCOPY VARCHAR2
1085 , x_exception_msg OUT NOCOPY VARCHAR2
1086 )
1087 IS
1088
1089 CURSOR C_lookup_row(p_ship_method_code VARCHAR2) IS
1090 --Bug3330869 SELECT *
1091 SELECT LOOKUP_CODE,
1092 TAG,
1093 ATTRIBUTE_CATEGORY,
1094 ATTRIBUTE1,
1095 ATTRIBUTE2,
1096 ATTRIBUTE3,
1097 ATTRIBUTE4,
1098 START_DATE_ACTIVE,
1099 END_DATE_ACTIVE,
1100 TERRITORY_CODE,
1101 ATTRIBUTE5,
1102 ATTRIBUTE6,
1103 ATTRIBUTE7,
1104 ATTRIBUTE8,
1105 ATTRIBUTE9,
1106 ATTRIBUTE10,
1107 ATTRIBUTE11,
1108 ATTRIBUTE12,
1109 ATTRIBUTE13,
1110 ATTRIBUTE14,
1111 ATTRIBUTE15,
1112 MEANING,
1113 DESCRIPTION
1114 FROM FND_LOOKUP_VALUES
1115 where LOOKUP_TYPE = 'SHIP_METHOD'
1116 and SECURITY_GROUP_ID = 0
1117 and VIEW_APPLICATION_ID = 3
1118 and LOOKUP_CODE = p_ship_method_code;
1119
1120 CURSOR C_freight_code(x_rowid VARCHAR2) IS
1121 SELECT freight_code
1122 FROM WSH_CARRIERS car,WSH_CARRIER_SERVICES carser
1123 WHERE carser.carrier_id = car.carrier_id
1124 and carser.rowid=x_rowid;
1125
1126 l_freight_code VARCHAR(100);
1127
1128 lookupinfo C_lookup_row%ROWTYPE;
1129 others EXCEPTION;
1130 l_position NUMBER;
1131 l_procedure VARCHAR2(100);
1132
1133 --
1134 l_debug_on BOOLEAN;
1135 --
1136 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_CARRIER_SERVICE';
1137 --
1138 BEGIN
1139 --
1140 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1141 --
1142 IF l_debug_on IS NULL
1143 THEN
1144 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1145 END IF;
1146 --
1147 IF l_debug_on THEN
1148 WSH_DEBUG_SV.push(l_module_name);
1149 --
1150 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
1151 END IF;
1152 --
1153 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1154
1155 l_position := 10;
1156 l_procedure := 'Updating WSH_CARRIER_SERVICES';
1157
1158 UPDATE wsh_carrier_services
1159 SET
1160 service_level = p_Carrier_Service_Info.Service_level,
1161 mode_of_transport = p_Carrier_Service_Info.mode_of_transport,
1162 min_sl_time = p_Carrier_Service_Info.min_sl_time,
1163 max_sl_time = p_Carrier_Service_Info.max_sl_time,
1164 sl_time_uom = p_Carrier_Service_Info.sl_time_uom,
1165 enabled_flag = p_Carrier_Service_Info.Enabled_Flag,
1166 web_enabled = p_Carrier_Service_Info.Web_Enabled,
1167 ship_method_code = p_Carrier_Service_Info.Ship_Method_Code,
1168 ship_method_meaning = p_Carrier_Service_Info.Ship_Method_Meaning,
1169 attribute_category = p_Carrier_Service_Info.Attribute_Category,
1170 attribute1 = p_Carrier_Service_Info.Attribute1,
1171 attribute2 = p_Carrier_Service_Info.Attribute2,
1172 attribute3 = p_Carrier_Service_Info.Attribute3,
1173 attribute4 = p_Carrier_Service_Info.Attribute4,
1174 attribute5 = p_Carrier_Service_Info.Attribute5,
1175 attribute6 = p_Carrier_Service_Info.Attribute6,
1176 attribute7 = p_Carrier_Service_Info.Attribute7,
1177 attribute8 = p_Carrier_Service_Info.Attribute8,
1178 attribute9 = p_Carrier_Service_Info.Attribute9,
1179 attribute10 = p_Carrier_Service_Info.Attribute10,
1180 attribute11 = p_Carrier_Service_Info.Attribute11,
1181 attribute12 = p_Carrier_Service_Info.Attribute12,
1182 attribute13 = p_Carrier_Service_Info.Attribute13,
1183 attribute14 = p_Carrier_Service_Info.Attribute14,
1184 attribute15 = p_Carrier_Service_Info.Attribute15,
1185 last_update_date = p_Carrier_Service_Info.Last_Update_Date,
1186 last_updated_by = p_Carrier_Service_Info.Last_Updated_By,
1187 last_update_login = p_Carrier_Service_Info.Last_Update_Login,
1188 -- Pack J Enhancement
1189 MAX_NUM_STOPS_PERMITTED = p_Carrier_Service_Info.MAX_NUM_STOPS_PERMITTED,
1193 MAX_LAYOVER_TIME = p_Carrier_Service_Info.MAX_LAYOVER_TIME,
1190 MAX_TOTAL_DISTANCE = p_Carrier_Service_Info.MAX_TOTAL_DISTANCE,
1191 MAX_TOTAL_TIME = p_Carrier_Service_Info.MAX_TOTAL_TIME,
1192 ALLOW_INTERSPERSE_LOAD = p_Carrier_Service_Info.ALLOW_INTERSPERSE_LOAD,
1194 MIN_LAYOVER_TIME = p_Carrier_Service_Info.MIN_LAYOVER_TIME,
1195 MAX_TOTAL_DISTANCE_IN_24HR = p_Carrier_Service_Info.MAX_TOTAL_DISTANCE_IN_24HR,
1196 MAX_DRIVING_TIME_IN_24HR = p_Carrier_Service_Info.MAX_DRIVING_TIME_IN_24HR,
1197 MAX_DUTY_TIME_IN_24HR = p_Carrier_Service_Info.MAX_DUTY_TIME_IN_24HR,
1198 MAX_CM_DISTANCE = p_Carrier_Service_Info.MAX_CM_DISTANCE,
1199 MAX_CM_TIME = p_Carrier_Service_Info.MAX_CM_TIME,
1200 MAX_CM_DH_DISTANCE = p_Carrier_Service_Info.MAX_CM_DH_DISTANCE,
1201 MAX_SIZE_WIDTH = p_Carrier_Service_Info.MAX_SIZE_WIDTH,
1202 MAX_SIZE_HEIGHT = p_Carrier_Service_Info.MAX_SIZE_HEIGHT,
1203 MAX_SIZE_LENGTH = p_Carrier_Service_Info.MAX_SIZE_LENGTH,
1204 MIN_SIZE_WIDTH = p_Carrier_Service_Info.MIN_SIZE_WIDTH,
1205 MIN_SIZE_HEIGHT = p_Carrier_Service_Info.MIN_SIZE_HEIGHT,
1206 MIN_SIZE_LENGTH = p_Carrier_Service_Info.MIN_SIZE_LENGTH,
1207 MAX_OUT_OF_ROUTE = p_Carrier_Service_Info.MAX_OUT_OF_ROUTE,
1208 CM_FREE_DH_MILEAGE = p_Carrier_Service_Info.CM_FREE_DH_MILEAGE,
1209 MIN_CM_DISTANCE = p_Carrier_Service_Info.MIN_CM_DISTANCE,
1210 CM_FIRST_LOAD_DISCOUNT = p_Carrier_Service_Info.CM_FIRST_LOAD_DISCOUNT,
1211 MIN_CM_TIME = p_Carrier_Service_Info.MIN_CM_TIME,
1212 UNIT_RATE_BASIS = p_Carrier_Service_Info.UNIT_RATE_BASIS,
1213 CM_RATE_VARIANT = p_Carrier_Service_Info.CM_RATE_VARIANT,
1214 DISTANCE_CALCULATION_METHOD = p_Carrier_Service_Info.DISTANCE_CALCULATION_METHOD,
1215 ALLOW_CONTINUOUS_MOVE = p_Carrier_Service_Info.ALLOW_CONTINUOUS_MOVE,
1216 MAX_CM_DH_TIME = p_Carrier_Service_Info.MAX_CM_DH_TIME,
1217 ORIGIN_DSTN_SURCHARGE_LEVEL = p_Carrier_Service_Info.ORIGIN_DSTN_SURCHARGE_LEVEL,
1218 UPDATE_MOT_SL = p_Carrier_Service_Info.UPDATE_MOT_SL
1219 WHERE rowid = p_rowid;
1220
1221 IF (SQL%NOTFOUND) THEN
1222 IF l_debug_on THEN
1223 WSH_DEBUG_SV.log(l_module_name,'SQL%NOTFOUND');
1224 END IF;
1225 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1226 RAISE NO_DATA_FOUND;
1227 END IF;
1228
1229 l_position := 20;
1230 l_procedure := 'Fetching from cursor';
1231
1232 l_procedure := p_carrier_service_info.ship_method_code;
1233 OPEN C_lookup_row(p_carrier_service_info.ship_method_code);
1237 CLOSE C_lookup_row;
1234 FETCH C_lookup_row INTO lookupinfo;
1235
1236 IF (C_lookup_row%NOTFOUND) THEN
1238 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
1239 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1240 IF l_debug_on THEN
1241 WSH_DEBUG_SV.log(l_module_name,'C_lookup_row%NOTFOUND');
1242 WSH_DEBUG_SV.log(l_module_name, 'Cursor opened successfully called from update_carrier_service procedure'); --Bug3330869
1243 END IF;
1244 RAISE NO_DATA_FOUND;
1245 END IF;
1246
1247 CLOSE C_lookup_row;
1248
1249 l_position := 30;
1250 l_procedure := 'Updating FND_LOOKUP_VALUES';
1251
1252 -----------------------------------------------------
1253 -- Bug 2361153 : Ship_Method_Meaning is passed to the
1254 -- X_DESCRIPTION parameter.
1255 -----------------------------------------------------
1256
1257 FND_LOOKUP_VALUES_PKG.UPDATE_ROW
1258 (
1259 X_LOOKUP_TYPE => 'SHIP_METHOD',
1260 X_SECURITY_GROUP_ID => 0,
1261 X_VIEW_APPLICATION_ID => 3,
1262 X_LOOKUP_CODE => lookupinfo.LOOKUP_CODE,
1263 X_TAG => lookupinfo.TAG,
1264 X_ATTRIBUTE_CATEGORY => lookupinfo.ATTRIBUTE_CATEGORY,
1265 X_ATTRIBUTE1 => lookupinfo.ATTRIBUTE1,
1266 X_ATTRIBUTE2 => lookupinfo.ATTRIBUTE2,
1267 X_ATTRIBUTE3 => lookupinfo.ATTRIBUTE3,
1268 X_ATTRIBUTE4 => lookupinfo.ATTRIBUTE4,
1269 X_ENABLED_FLAG => p_Carrier_Service_Info.ENABLED_FLAG,
1270 X_START_DATE_ACTIVE => lookupinfo.START_DATE_ACTIVE,
1271 X_END_DATE_ACTIVE => lookupinfo.END_DATE_ACTIVE,
1272 X_TERRITORY_CODE => lookupinfo.TERRITORY_CODE,
1273 X_ATTRIBUTE5 => lookupinfo.ATTRIBUTE5,
1274 X_ATTRIBUTE6 => lookupinfo.ATTRIBUTE6,
1275 X_ATTRIBUTE7 => lookupinfo.ATTRIBUTE7,
1276 X_ATTRIBUTE8 => lookupinfo.ATTRIBUTE8,
1277 X_ATTRIBUTE9 => lookupinfo.ATTRIBUTE9,
1278 X_ATTRIBUTE10 => lookupinfo.ATTRIBUTE10,
1279 X_ATTRIBUTE11 => lookupinfo.ATTRIBUTE11,
1280 X_ATTRIBUTE12 => lookupinfo.ATTRIBUTE12,
1281 X_ATTRIBUTE13 => lookupinfo.ATTRIBUTE13,
1282 X_ATTRIBUTE14 => lookupinfo.ATTRIBUTE14,
1283 X_ATTRIBUTE15 => lookupinfo.ATTRIBUTE15,
1284 X_MEANING => p_Carrier_Service_Info.SHIP_METHOD_MEANING,
1285 X_DESCRIPTION => p_Carrier_Service_Info.SHIP_METHOD_MEANING,
1286 X_LAST_UPDATE_DATE => sysdate,
1287 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1288 X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID
1289 );
1290
1291 l_position := 40;
1292 l_procedure := 'Updating WSH_CARRIER_SHIP_METHODS';
1293 OPEN C_freight_code(p_rowid);
1294 FETCH C_freight_code INTO l_freight_code;
1295 IF C_freight_code%found THEN
1296 UPDATE WSH_CARRIER_SHIP_METHODS
1297 SET WEB_ENABLED = p_Carrier_Service_Info.Web_Enabled,
1298 LAST_UPDATE_DATE =sysdate,
1299 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1300 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
1301 ATTRIBUTE1 = p_Carrier_Service_Info.Attribute1,
1302 ATTRIBUTE2 = p_Carrier_Service_Info.Attribute2,
1303 ATTRIBUTE3 = p_Carrier_Service_Info.Attribute3,
1304 ATTRIBUTE4 = p_Carrier_Service_Info.Attribute4,
1305 ATTRIBUTE5 = p_Carrier_Service_Info.Attribute5,
1306 ATTRIBUTE6 = p_Carrier_Service_Info.Attribute6,
1307 ATTRIBUTE7 = p_Carrier_Service_Info.Attribute7,
1308 ATTRIBUTE8 = p_Carrier_Service_Info.Attribute8,
1309 ATTRIBUTE9 = p_Carrier_Service_Info.Attribute9,
1310 ATTRIBUTE10 = p_Carrier_Service_Info.Attribute10,
1311 ATTRIBUTE11 = p_Carrier_Service_Info.Attribute11,
1312 ATTRIBUTE12 = p_Carrier_Service_Info.Attribute12,
1313 ATTRIBUTE13 = p_Carrier_Service_Info.Attribute13,
1314 ATTRIBUTE14 = p_Carrier_Service_Info.Attribute14,
1315 ATTRIBUTE15 = p_Carrier_Service_Info.Attribute15
1316 WHERE FREIGHT_CODE = l_freight_code
1317 AND SHIP_METHOD_CODE = p_Carrier_Service_Info.Ship_Method_Code;
1318 END IF;
1319 CLOSE C_freight_code;
1320
1321 IF FND_API.To_Boolean(p_commit) THEN
1322 COMMIT;
1323 END IF;
1324 --
1325
1326 IF l_debug_on THEN
1327 WSH_DEBUG_SV.log(l_module_name,'LOOKUP_CODE',lookupinfo.LOOKUP_CODE);
1328 WSH_DEBUG_SV.log(l_module_name,'TAG',lookupinfo.TAG);
1329 WSH_DEBUG_SV.log(l_module_name,'ATTRIBUTE_CATEGORY',
1330 lookupinfo.ATTRIBUTE_CATEGORY);
1331 WSH_DEBUG_SV.pop(l_module_name);
1332 END IF;
1333 --
1334 EXCEPTION
1335 WHEN NO_DATA_FOUND THEN
1336 x_exception_msg := 'EXCEPTION : No Data Found';
1337 x_position := l_position;
1338 x_procedure := l_procedure;
1339 x_sqlerr := sqlerrm;
1340 x_sql_code := sqlcode;
1341 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1342 --
1343 IF l_debug_on THEN
1344 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1345 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
1346 END IF;
1347 --
1348 WHEN OTHERS THEN
1349 x_exception_msg := 'EXCEPTION : Others';
1350 x_position := l_position;
1351 x_procedure := l_procedure;
1352 x_sqlerr := sqlerrm;
1353 x_sql_code := sqlcode;
1354 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1355 IF l_debug_on THEN
1356 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1357 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1358 END IF;
1359 --
1360 END Update_Carrier_Service;
1361
1362
1363 PROCEDURE Car_Ser_Unassign_AllOrg(
1364 p_carrier_service_id IN NUMBER
1365 , p_ship_method_code IN VARCHAR2
1366 , p_freight_code IN VARCHAR2
1367 , x_return_status OUT NOCOPY VARCHAR2
1368 , x_exception_msg OUT NOCOPY VARCHAR2
1369 , x_position OUT NOCOPY NUMBER
1370 , x_procedure OUT NOCOPY VARCHAR2
1371 , x_sqlerr OUT NOCOPY VARCHAR2
1372 , x_sql_code OUT NOCOPY VARCHAR2
1373 ) IS
1374
1375 l_position NUMBER;
1376 l_procedure VARCHAR2(50);
1377 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1378 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CAR_SER_UNASSIGN_ALLORG';
1379
1380 BEGIN
1381 --
1382 IF l_debug_on THEN
1383 WSH_DEBUG_SV.push(l_module_name);
1384 WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_SERVICE_ID',p_carrier_service_id);
1385 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_METHOD_CODE',p_ship_method_code);
1386 WSH_DEBUG_SV.log(l_module_name,'P_FREIGHT_CODE',p_freight_code);
1387 END IF;
1388 --
1389 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1390
1391 l_position := 10;
1392 l_procedure := 'Updating WSH_CARRIER_SHIP_METHODS';
1393
1394 UPDATE wsh_carrier_ship_methods
1395 SET enabled_flag = 'N'
1396 WHERE ship_method_code = p_ship_method_code
1397 AND freight_code = p_freight_code;
1398
1399 l_position := 20;
1400 l_procedure := 'Updating WSH_ORG_CARRIER_SERVICES';
1401
1402 UPDATE wsh_org_carrier_services
1403 SET enabled_flag = 'N'
1404 WHERE carrier_service_id= p_carrier_service_id;
1405
1406 --
1407 IF l_debug_on THEN
1408 WSH_DEBUG_SV.pop(l_module_name);
1409 END IF;
1410 --
1411 EXCEPTION
1412 WHEN OTHERS THEN
1413 x_exception_msg := 'EXCEPTION : Others';
1414 x_position := l_position;
1415 x_procedure := l_procedure;
1416 x_sqlerr := sqlerrm;
1417 x_sql_code := sqlcode;
1418 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1419 --
1420 IF l_debug_on THEN
1421 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1422 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1423 END IF;
1424
1425 END Car_Ser_Unassign_AllOrg;
1426
1427
1428 END WSH_CARRIER_SERVICES_PKG;