[Home] [Help]
PACKAGE BODY: APPS.WSH_CREATE_CARRIERS_PKG
Source
1 PACKAGE BODY WSH_CREATE_CARRIERS_PKG as
2 /* $Header: WSHCATHB.pls 120.10.12010000.2 2008/09/18 08:52:39 sankarun ship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_CREATE_CARRIERS_PKG';
6 --
7
8 PROCEDURE CREATE_CARRIERINFO (
9 P_Carrier_info IN CARecType,
10 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
11 X_ROWID OUT NOCOPY VARCHAR2,
12 X_CARRIER_PARTY_ID OUT NOCOPY NUMBER,
13 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
14 X_EXCEPTION_MSG OUT NOCOPY VARCHAR2,
15 X_POSITION OUT NOCOPY NUMBER,
16 X_PROCEDURE OUT NOCOPY VARCHAR2,
17 X_SQLERR OUT NOCOPY VARCHAR2,
18 X_SQL_CODE OUT NOCOPY VARCHAR2 ) IS
19
20 -- General Declarations.
21 l_return_status varchar2(100);
22 l_msg_count number;
23 l_msg_data varchar2(2000);
24 l_party_number varchar2(100);
25 l_profile_id number;
26 l_code_assignment_id number;
27 l_exception_msg varchar2(1000);
28 HZ_FAIL_EXCEPTION exception;
29 insert_failed exception;
30 OTHERS exception;
31 l_position number;
32 l_call_procedure varchar2(100);
33 -- Bug 7391414 variables to hold profile option value 'HZ GENERATE PARTY NUMBER'
34 l_hz_profile_option varchar2(2);
35 l_hz_profile_set boolean;
36
37 -- Declarations for Party 'ORGANIZATION' Creation.
38 l_org_rec HZ_PARTY_V2PUB.organization_rec_type;
39 l_carrier_party_id number;
40
41 -- Declaration for Code Assignment.
42 l_code_assignment_rec_type HZ_CLASSIFICATION_V2PUB.Code_Assignment_Rec_Type;
43
44 -- Party Usage
45 l_party_usg_assignment_rec HZ_PARTY_USG_ASSIGNMENT_PVT.party_usg_assignment_rec_type;
46 l_party_usg_end_date DATE;
47
48 l_sql_code number;
49 l_sqlcode number;
50 l_sqlerr varchar2(2000);
51
52 CURSOR Get_Rowid(p_carrier_id NUMBER) IS
53 SELECT rowid
54 FROM wsh_carriers
55 WHERE carrier_id = p_carrier_id;
56
57 --
58 l_debug_on BOOLEAN;
59 --
60 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_CARRIERINFO';
61 --
62
63 BEGIN
64 -- Initialize the status to SUCCESS.
65
66 --
67 -- Debug Statements
68 --
69 --
70 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
71 --
72 IF l_debug_on IS NULL
73 THEN
74 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
75 END IF;
76 --
77 IF l_debug_on THEN
78 WSH_DEBUG_SV.push(l_module_name);
79 --
80 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.CARRIER_NAME',P_Carrier_info.CARRIER_NAME);
81 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.FREIGHT_CODE',P_Carrier_info.FREIGHT_CODE);
82 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.STATUS',P_Carrier_info.STATUS);
83 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.SCAC_CODE',P_Carrier_info.SCAC_CODE);
84 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MANIFESTING_ENABLED',P_Carrier_info.MANIFESTING_ENABLED);
85 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.CURRENCY_CODE',P_Carrier_info.CURRENCY_CODE);
86 -- Pack J
87 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_NUM_STOPS_PERMITTED',P_Carrier_info.MAX_NUM_STOPS_PERMITTED);
88 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_TOTAL_DISTANCE',P_Carrier_info.MAX_TOTAL_DISTANCE);
89 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_TOTAL_TIME',P_Carrier_info.MAX_TOTAL_TIME);
90 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.ALLOW_INTERSPERSE_LOAD',P_Carrier_info.ALLOW_INTERSPERSE_LOAD);
91 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_LAYOVER_TIME',P_Carrier_info.MAX_LAYOVER_TIME);
92 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MIN_LAYOVER_TIME',P_Carrier_info.MIN_LAYOVER_TIME);
93 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_TOTAL_DISTANCE_IN_24HR',P_Carrier_info.MAX_TOTAL_DISTANCE_IN_24HR);
94 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_DRIVING_TIME_IN_24HR',P_Carrier_info.MAX_DRIVING_TIME_IN_24HR);
95 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_DUTY_TIME_IN_24HR',P_Carrier_info.MAX_DUTY_TIME_IN_24HR);
96 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_CM_DISTANCE',P_Carrier_info.MAX_CM_DISTANCE);
97 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_CM_TIME',P_Carrier_info.MAX_CM_TIME);
98 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_CM_DH_DISTANCE',P_Carrier_info.MAX_CM_DH_DISTANCE);
99 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_SIZE_WIDTH',P_Carrier_info.MAX_SIZE_WIDTH);
100 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_SIZE_HEIGHT',P_Carrier_info.MAX_SIZE_HEIGHT);
101 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_SIZE_LENGTH',P_Carrier_info.MAX_SIZE_LENGTH);
102 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MIN_SIZE_WIDTH',P_Carrier_info.MIN_SIZE_WIDTH);
103 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MIN_SIZE_HEIGHT',P_Carrier_info.MIN_SIZE_HEIGHT);
104 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MIN_SIZE_LENGTH',P_Carrier_info.MIN_SIZE_LENGTH);
105 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.TIME_UOM',P_Carrier_info.TIME_UOM);
106 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DIMENSION_UOM',P_Carrier_info.DIMENSION_UOM);
107 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DISTANCE_UOM',P_Carrier_info.DISTANCE_UOM);
108 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_OUT_OF_ROUTE',P_Carrier_info.MAX_OUT_OF_ROUTE);
109 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.CM_FREE_DH_MILEAGE',P_Carrier_info.CM_FREE_DH_MILEAGE);
110 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MIN_CM_DISTANCE',P_Carrier_info.MIN_CM_DISTANCE);
111 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.CM_FIRST_LOAD_DISCOUNT',P_Carrier_info.CM_FIRST_LOAD_DISCOUNT);
112 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MIN_CM_TIME',P_Carrier_info.MIN_CM_TIME);
113 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.UNIT_RATE_BASIS',P_Carrier_info.UNIT_RATE_BASIS);
114 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.WEIGHT_UOM',P_Carrier_info.WEIGHT_UOM);
115 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.VOLUME_UOM',P_Carrier_info.VOLUME_UOM);
116 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.GENERIC_FLAG',P_Carrier_info.GENERIC_FLAG);
117 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.FREIGHT_BILL_AUTO_APPROVAL',P_Carrier_info.FREIGHT_BILL_AUTO_APPROVAL);
118 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.FREIGHT_AUDIT_LINE_LEVEL',P_Carrier_info.FREIGHT_AUDIT_LINE_LEVEL);
119 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.SUPPLIER_ID',P_Carrier_info.SUPPLIER_ID);
120 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.SUPPLIER_SITE_ID',P_Carrier_info.SUPPLIER_SITE_ID);
121 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.CM_RATE_VARIANT',P_Carrier_info.CM_RATE_VARIANT);
122 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DISTANCE_CALCULATION_METHOD',P_Carrier_info.DISTANCE_CALCULATION_METHOD);
123 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.ALLOW_CONTINUOUS_MOVE',P_Carrier_info.ALLOW_CONTINUOUS_MOVE);
124 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_CM_DH_TIME',P_Carrier_info.MAX_CM_DH_TIME);
125 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.ORIGIN_DSTN_SURCHARGE_LEVEL',P_Carrier_info.ORIGIN_DSTN_SURCHARGE_LEVEL);
126 -- R12 Code changes
127 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DIM_DIMENSIONAL_FACTOR',P_Carrier_info.DIM_DIMENSIONAL_FACTOR);
128 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DIM_WEIGHT_UOM',P_Carrier_info.DIM_WEIGHT_UOM);
129 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DIM_VOLUME_UOM',P_Carrier_info.DIM_VOLUME_UOM);
130 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DIM_DIMENSION_UOM',P_Carrier_info.DIM_DIMENSION_UOM);
131 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DIM_MIN_PACK_VOL',P_Carrier_info.DIM_MIN_PACK_VOL);
132 -- R12 Code changes
133 END IF;
134 --
135
136 l_return_status := 'S';
137
138 -- Initialize Messages.
139 fnd_msg_pub.initialize();
140
141 -- Put Information into Org_rec.
142
143 l_org_rec.organization_name := P_Carrier_info.CARRIER_NAME;
144 l_org_rec.created_by_module := 'ORACLE_SHIPPING';
145 -- l_org_rec.party_rec.status := P_Carrier_info.STATUS;
146 l_org_rec.party_rec.status := 'A';
147
148 -- Set the Autogenerate Party Number to 'Yes'.
149 -- Bug 7391414 Setting the profile option 'HZ GENERATE PARTY NUMBER' to Yes if it is No or Null
150 l_hz_profile_set := false;
151 l_hz_profile_option := fnd_profile.value('HZ_GENERATE_PARTY_NUMBER');
152
153 IF (l_hz_profile_option = 'N' or l_hz_profile_option is null ) THEN
154 IF l_debug_on THEN
155 WSH_DEBUG_SV.logmsg(l_module_name,'Setting profile option HZ_GENERATE_PARTY_NUMBER to Yes');
156 END IF;
157 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER','Y');
158 l_hz_profile_set := true;
159 END IF;
160
161
162 -- Create Carrier Organization.
163
164 l_position := 10;
165 l_call_procedure := 'Calling TCA API Create_Organization';
166
167 --
168 -- Debug Statements
169 --
170 IF l_debug_on THEN
171 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_PARTY_PUB.CREATE_ORGANIZATION',WSH_DEBUG_SV.C_PROC_LEVEL);
172 END IF;
173 --
174
175 HZ_PARTY_V2PUB.Create_Organization
176 (
177 p_init_msg_list => FND_API.G_TRUE,
178 p_organization_rec => l_org_rec,
179 x_return_status => l_return_status,
180 x_msg_count => l_msg_count,
181 x_msg_data => l_msg_data,
182 x_party_id => l_carrier_party_id,
183 x_party_number => l_party_number,
184 x_profile_id => l_profile_id
185 );
186
187 -- Bug 7391414 Setting the profile option 'HZ GENERATE PARTY NUMBER' to previous value
188 IF l_hz_profile_set THEN
189 IF l_debug_on THEN
190 WSH_DEBUG_SV.logmsg(l_module_name,'Reverting the value of profile option HZ_GENERATE_PARTY_NUMBER');
191 END IF;
192 fnd_profile.put('HZ_GENERATE_PARTY_NUMBER',l_hz_profile_option);
193 END IF;
194
195 IF (l_return_status <> 'S') THEN
196 x_return_status := l_return_status;
197 RAISE HZ_FAIL_EXCEPTION;
198 END IF;
199
200 x_carrier_party_id := l_carrier_party_id;
201
202 -- Party Usage
203 IF (P_Carrier_info.STATUS = 'A') THEN
204 l_party_usg_end_date := null;
205
206 ELSE
207 l_party_usg_end_date := sysdate;
208 END IF;
209
210 l_party_usg_assignment_rec.party_id := l_carrier_party_id;
211 l_party_usg_assignment_rec.party_usage_code := 'TRANSPORTATION_PROVIDER';
212 l_party_usg_assignment_rec.effective_start_date := sysdate;
213 l_party_usg_assignment_rec.effective_end_date := l_party_usg_end_date;
214 l_party_usg_assignment_rec.created_by_module := 'WSH';
215 l_position := 20;
216 l_call_procedure := 'Calling assign_party_usage ';
217
218 HZ_PARTY_USG_ASSIGNMENT_PVT.assign_party_usage (
219 p_init_msg_list => FND_API.G_TRUE,
220 p_validation_level => HZ_PARTY_USG_ASSIGNMENT_PVT.G_VALID_LEVEL_LOW,
221 p_party_usg_assignment_rec => l_party_usg_assignment_rec,
222 x_return_status => l_return_status,
223 x_msg_count => l_msg_count,
224 x_msg_data => l_msg_data);
225 IF (l_return_status <> 'S') THEN
226 x_return_status := l_return_status;
227 RAISE HZ_FAIL_EXCEPTION;
228 END IF;
229
230 -- End Party Usage
231
232 l_position := 30;
233 l_call_procedure := 'Inserting into WSH_CARRIERS table';
234
235 INSERT INTO WSH_CARRIERS
236 (
237 CARRIER_ID,
238 --Bug2313801 NAME,
239 FREIGHT_CODE,
240 SCAC_CODE,
241 MANIFESTING_ENABLED_FLAG,
242 CURRENCY_CODE,
243 attribute_category,
244 attribute1,
245 attribute2,
246 attribute3,
247 attribute4,
248 attribute5,
249 attribute6,
250 attribute7,
251 attribute8,
252 attribute9,
253 attribute10,
254 attribute11,
255 attribute12,
256 attribute13,
257 attribute14,
258 attribute15,
259 last_update_date,
260 last_updated_by,
261 creation_date,
262 created_by,
263 last_update_login,
264 -- Pack J
265 MAX_NUM_STOPS_PERMITTED,
266 MAX_TOTAL_DISTANCE,
267 MAX_TOTAL_TIME,
268 ALLOW_INTERSPERSE_LOAD,
269 MAX_LAYOVER_TIME,
270 MIN_LAYOVER_TIME,
271 MAX_TOTAL_DISTANCE_IN_24HR,
272 MAX_DRIVING_TIME_IN_24HR,
273 MAX_DUTY_TIME_IN_24HR,
274 MAX_CM_DISTANCE,
275 MAX_CM_TIME,
276 MAX_CM_DH_DISTANCE,
277 MAX_SIZE_WIDTH,
278 MAX_SIZE_HEIGHT,
279 MAX_SIZE_LENGTH,
280 MIN_SIZE_WIDTH,
281 MIN_SIZE_HEIGHT,
282 MIN_SIZE_LENGTH,
283 TIME_UOM,
284 DIMENSION_UOM,
285 DISTANCE_UOM,
286 MAX_OUT_OF_ROUTE,
287 CM_FREE_DH_MILEAGE,
288 MIN_CM_DISTANCE,
289 CM_FIRST_LOAD_DISCOUNT,
290 MIN_CM_TIME,
291 UNIT_RATE_BASIS,
292 WEIGHT_UOM,
293 VOLUME_UOM,
294 GENERIC_FLAG,
295 FREIGHT_BILL_AUTO_APPROVAL,
296 FREIGHT_AUDIT_LINE_LEVEL,
297 SUPPLIER_ID,
298 SUPPLIER_SITE_ID,
299 CM_RATE_VARIANT,
300 DISTANCE_CALCULATION_METHOD,
301 ALLOW_CONTINUOUS_MOVE,
302 MAX_CM_DH_TIME,
303 ORIGIN_DSTN_SURCHARGE_LEVEL)
304 VALUES (
305 l_carrier_party_id,
306 --Bug2313801 p_carrier_name,
307 P_Carrier_info.FREIGHT_CODE,
308 P_Carrier_info.SCAC_CODE,
309 P_Carrier_info.MANIFESTING_ENABLED,
310 P_Carrier_info.CURRENCY_CODE,
311 P_Carrier_info.Attribute_Category,
312 P_Carrier_info.Attribute1,
313 P_Carrier_info.Attribute2,
314 P_Carrier_info.Attribute3,
315 P_Carrier_info.Attribute4,
316 P_Carrier_info.Attribute5,
317 P_Carrier_info.Attribute6,
318 P_Carrier_info.Attribute7,
319 P_Carrier_info.Attribute8,
320 P_Carrier_info.Attribute9,
321 P_Carrier_info.Attribute10,
322 P_Carrier_info.Attribute11,
323 P_Carrier_info.Attribute12,
324 P_Carrier_info.Attribute13,
325 P_Carrier_info.Attribute14,
326 P_Carrier_info.Attribute15,
327 sysdate,
328 FND_GLOBAL.USER_ID,
329 sysdate,
330 FND_GLOBAL.USER_ID,
331 FND_GLOBAL.LOGIN_ID,
332 -- Pack J
333 P_Carrier_info.MAX_NUM_STOPS_PERMITTED,
334 P_Carrier_info.MAX_TOTAL_DISTANCE,
335 P_Carrier_info.MAX_TOTAL_TIME,
336 P_Carrier_info.ALLOW_INTERSPERSE_LOAD,
337 P_Carrier_info.MAX_LAYOVER_TIME,
338 P_Carrier_info.MIN_LAYOVER_TIME,
339 P_Carrier_info.MAX_TOTAL_DISTANCE_IN_24HR,
340 P_Carrier_info.MAX_DRIVING_TIME_IN_24HR,
341 P_Carrier_info.MAX_DUTY_TIME_IN_24HR,
342 P_Carrier_info.MAX_CM_DISTANCE,
343 P_Carrier_info.MAX_CM_TIME,
347 P_Carrier_info.MAX_SIZE_LENGTH,
344 P_Carrier_info.MAX_CM_DH_DISTANCE,
345 P_Carrier_info.MAX_SIZE_WIDTH,
346 P_Carrier_info.MAX_SIZE_HEIGHT,
348 P_Carrier_info.MIN_SIZE_WIDTH,
349 P_Carrier_info.MIN_SIZE_HEIGHT,
350 P_Carrier_info.MIN_SIZE_LENGTH,
351 P_Carrier_info.TIME_UOM,
352 P_Carrier_info.DIMENSION_UOM,
353 P_Carrier_info.DISTANCE_UOM,
354 P_Carrier_info.MAX_OUT_OF_ROUTE,
355 P_Carrier_info.CM_FREE_DH_MILEAGE,
356 P_Carrier_info.MIN_CM_DISTANCE,
357 P_Carrier_info.CM_FIRST_LOAD_DISCOUNT,
358 P_Carrier_info.MIN_CM_TIME,
359 P_Carrier_info.UNIT_RATE_BASIS,
360 P_Carrier_info.WEIGHT_UOM,
361 P_Carrier_info.VOLUME_UOM,
362 P_Carrier_info.GENERIC_FLAG,
363 P_Carrier_info.FREIGHT_BILL_AUTO_APPROVAL,
364 P_Carrier_info.FREIGHT_AUDIT_LINE_LEVEL,
365 P_Carrier_info.SUPPLIER_ID,
366 P_Carrier_info.SUPPLIER_SITE_ID,
367 P_Carrier_info.CM_RATE_VARIANT,
368 P_Carrier_info.DISTANCE_CALCULATION_METHOD,
369 P_Carrier_info.ALLOW_CONTINUOUS_MOVE,
370 P_Carrier_info.MAX_CM_DH_TIME,
371 P_Carrier_info.ORIGIN_DSTN_SURCHARGE_LEVEL);
372
373 OPEN Get_rowid(l_carrier_party_id);
374 FETCH Get_Rowid INTO x_rowid;
375
376 IF Get_Rowid%NOTFOUND THEN
377 l_return_status := 'E';
378 x_return_status := l_return_status;
379 RAISE Insert_Failed;
380 END IF;
381
382 CLOSE Get_Rowid;
383
384 x_return_status := l_return_status;
385
386 IF FND_API.To_Boolean(p_commit) THEN
387 COMMIT;
388 END IF;
389
390 --
391 -- Debug Statements
392 --
393 IF l_debug_on THEN
394 WSH_DEBUG_SV.pop(l_module_name);
395 END IF;
396 --
397
398 EXCEPTION
399
400 WHEN NO_DATA_FOUND THEN
401 x_exception_msg := 'NO_DATA_FOUND Exception Raised';
402 x_position := l_position;
403 x_procedure := l_call_procedure;
404 x_sqlerr := sqlerrm;
405 x_sql_code := sqlcode;
406 x_return_status := 'E';
407 --
408 -- Debug Statements
409 --
410 IF l_debug_on THEN
411 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
412 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
413 END IF;
414 --
415
416 WHEN HZ_FAIL_EXCEPTION THEN
417 x_exception_msg := l_msg_data;
418 x_position := l_position;
419 x_procedure := l_call_procedure;
420 x_sqlerr := sqlerrm;
421 x_sql_code := sqlcode;
422
423 --
424 -- Debug Statements
425 --
426 IF l_debug_on THEN
427 WSH_DEBUG_SV.logmsg(l_module_name,'HZ_FAIL_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
428 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:HZ_FAIL_EXCEPTION');
429 END IF;
430 --
431
432 WHEN INSERT_FAILED THEN
433 x_exception_msg := 'Insert Failed Exception';
434 x_position := l_position;
435 x_procedure := l_call_procedure;
436 x_sqlerr := sqlerrm;
437 x_sql_code := sqlcode;
438
439 --
440 -- Debug Statements
441 --
442 IF l_debug_on THEN
443 WSH_DEBUG_SV.logmsg(l_module_name,'INSERT_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
444 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INSERT_FAILED');
445 END IF;
446 --
447
448 WHEN OTHERS THEN
449 --
450 -- Debug Statements
451 --
452 IF l_debug_on THEN
453 WSH_DEBUG_SV.pop(l_module_name);
454 END IF;
455 --
456 x_return_status := 'E';
457 x_exception_msg := 'WHEN OTHERS Exception Raise';
458 x_position := l_position;
459 x_procedure := l_call_procedure;
460 x_sqlerr := sqlerrm;
461 x_sql_code := sqlcode;
462
463 --
464 -- Debug Statements
465 --
466 IF l_debug_on THEN
467 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
468 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
469 END IF;
470 --
471
472 END CREATE_CARRIERINFO;
473
474
475 PROCEDURE UPDATE_CARRIERINFO
476 (
477 P_Carrier_info IN CARecType,
478 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
479 x_return_status OUT NOCOPY VARCHAR2,
480 x_exception_msg OUT NOCOPY VARCHAR2,
481 x_procedure OUT NOCOPY VARCHAR2,
482 x_position OUT NOCOPY NUMBER,
483 x_sqlerr OUT NOCOPY VARCHAR2,
484 x_sql_code OUT NOCOPY VARCHAR2 ) IS
485
486 l_org_rec hz_party_v2pub.organization_rec_type;
487 l_return_status varchar2(100);
488 l_msg_count number;
492 l_profile_id number;
489 l_msg_data varchar2(2000);
490 l_party_id number;
491 l_party_number varchar2(100);
493 l_exception_msg varchar2(1000);
494 HZ_FAIL_EXCEPTION exception;
495 OTHERS exception;
496 l_status varchar2(100);
497 l_object_version_number number;
498 l_position number;
499 l_call_procedure varchar2(100);
500
501 CURSOR Get_Object_Version_Number(l_carrier_party_id NUMBER) IS
502 SELECT object_version_number
503 FROM hz_parties
504 WHERE party_id = l_carrier_party_id;
505 -- Party Usage
506 CURSOR Get_Carrier_Status(l_carrier_party_id NUMBER) IS
507 SELECT active, party_usg_assignment_id
508 FROM wsh_carriers_v
509 WHERE carrier_id = l_carrier_party_id;
510
511 l_party_usg_assignment_rec HZ_PARTY_USG_ASSIGNMENT_PVT.party_usg_assignment_rec_type;
512 l_party_usg_assignment_id NUMBER;
513 -- Party Usage
514
515 --
516 l_debug_on BOOLEAN;
517 --
518 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_CARRIERINFO';
519 --
520
521 BEGIN
522
523 -- Initialize the status to SUCCESS.
524
525 --
526 -- Debug Statements
527 --
528 --
529 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
530 --
531 IF l_debug_on IS NULL
532 THEN
533 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
534 END IF;
535 --
536 IF l_debug_on THEN
537 WSH_DEBUG_SV.push(l_module_name);
538 --
539 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.CARRIER_ID',P_Carrier_info.CARRIER_ID);
540 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.FREIGHT_CODE',P_Carrier_info.FREIGHT_CODE);
541 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.CARRIER_NAME',P_Carrier_info.CARRIER_NAME);
542 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.STATUS',P_Carrier_info.STATUS);
543 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.SCAC_CODE',P_Carrier_info.SCAC_CODE);
544 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MANIFESTING_ENABLED',P_Carrier_info.MANIFESTING_ENABLED);
545 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.CURRENCY_CODE',P_Carrier_info.CURRENCY_CODE);
546 -- Pack J
547 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_NUM_STOPS_PERMITTED',P_Carrier_info.MAX_NUM_STOPS_PERMITTED);
548 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_TOTAL_DISTANCE',P_Carrier_info.MAX_TOTAL_DISTANCE);
549 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_TOTAL_TIME',P_Carrier_info.MAX_TOTAL_TIME);
550 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.ALLOW_INTERSPERSE_LOAD',P_Carrier_info.ALLOW_INTERSPERSE_LOAD);
551 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_LAYOVER_TIME',P_Carrier_info.MAX_LAYOVER_TIME);
552 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MIN_LAYOVER_TIME',P_Carrier_info.MIN_LAYOVER_TIME);
553 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_TOTAL_DISTANCE_IN_24HR',P_Carrier_info.MAX_TOTAL_DISTANCE_IN_24HR);
554 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_DRIVING_TIME_IN_24HR',P_Carrier_info.MAX_DRIVING_TIME_IN_24HR);
555 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_DUTY_TIME_IN_24HR',P_Carrier_info.MAX_DUTY_TIME_IN_24HR);
556 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_CM_DISTANCE',P_Carrier_info.MAX_CM_DISTANCE);
557 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_CM_TIME',P_Carrier_info.MAX_CM_TIME);
558 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_CM_DH_DISTANCE',P_Carrier_info.MAX_CM_DH_DISTANCE);
559 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_SIZE_WIDTH',P_Carrier_info.MAX_SIZE_WIDTH);
560 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_SIZE_HEIGHT',P_Carrier_info.MAX_SIZE_HEIGHT);
561 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_SIZE_LENGTH',P_Carrier_info.MAX_SIZE_LENGTH);
562 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MIN_SIZE_WIDTH',P_Carrier_info.MIN_SIZE_WIDTH);
563 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MIN_SIZE_HEIGHT',P_Carrier_info.MIN_SIZE_HEIGHT);
564 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MIN_SIZE_LENGTH',P_Carrier_info.MIN_SIZE_LENGTH);
565 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.TIME_UOM',P_Carrier_info.TIME_UOM);
566 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DIMENSION_UOM',P_Carrier_info.DIMENSION_UOM);
567 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DISTANCE_UOM',P_Carrier_info.DISTANCE_UOM);
568 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_OUT_OF_ROUTE',P_Carrier_info.MAX_OUT_OF_ROUTE);
569 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.CM_FREE_DH_MILEAGE',P_Carrier_info.CM_FREE_DH_MILEAGE);
570 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MIN_CM_DISTANCE',P_Carrier_info.MIN_CM_DISTANCE);
571 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.CM_FIRST_LOAD_DISCOUNT',P_Carrier_info.CM_FIRST_LOAD_DISCOUNT);
572 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MIN_CM_TIME',P_Carrier_info.MIN_CM_TIME);
573 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.UNIT_RATE_BASIS',P_Carrier_info.UNIT_RATE_BASIS);
574 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.WEIGHT_UOM',P_Carrier_info.WEIGHT_UOM);
575 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.VOLUME_UOM',P_Carrier_info.VOLUME_UOM);
576 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.GENERIC_FLAG',P_Carrier_info.GENERIC_FLAG);
580 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.SUPPLIER_SITE_ID',P_Carrier_info.SUPPLIER_SITE_ID);
577 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.FREIGHT_BILL_AUTO_APPROVAL',P_Carrier_info.FREIGHT_BILL_AUTO_APPROVAL);
578 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.FREIGHT_AUDIT_LINE_LEVEL',P_Carrier_info.FREIGHT_AUDIT_LINE_LEVEL);
579 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.SUPPLIER_ID',P_Carrier_info.SUPPLIER_ID);
581 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.CM_RATE_VARIANT',P_Carrier_info.CM_RATE_VARIANT);
582 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DISTANCE_CALCULATION_METHOD',P_Carrier_info.DISTANCE_CALCULATION_METHOD);
583 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.ALLOW_CONTINUOUS_MOVE',P_Carrier_info.ALLOW_CONTINUOUS_MOVE);
584 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_CM_DH_TIME',P_Carrier_info.MAX_CM_DH_TIME);
585 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.ORIGIN_DSTN_SURCHARGE_LEVEL',P_Carrier_info.ORIGIN_DSTN_SURCHARGE_LEVEL);
586 -- R12 Code changes
587 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DIM_DIMENSIONAL_FACTOR',P_Carrier_info.DIM_DIMENSIONAL_FACTOR);
588 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DIM_WEIGHT_UOM',P_Carrier_info.DIM_WEIGHT_UOM);
589 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DIM_VOLUME_UOM',P_Carrier_info.DIM_VOLUME_UOM);
590 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DIM_DIMENSION_UOM',P_Carrier_info.DIM_DIMENSION_UOM);
591 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DIM_MIN_PACK_VOL',P_Carrier_info.DIM_MIN_PACK_VOL);
592 -- R12 Code changes
593 END IF;
594 --
595 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
596 x_return_status := l_return_status;
597 -- Initialize Messages.
598 fnd_msg_pub.initialize();
599
600 -- Put Information into Org_rec.
601
602 l_org_rec.organization_name := P_Carrier_info.CARRIER_NAME;
603 l_org_rec.party_rec.party_id := P_Carrier_info.CARRIER_ID;
604 --l_org_rec.party_rec.status := P_Carrier_info.STATUS;
605
606 -- Get last_update_date for the Carrier.
607
608 OPEN Get_Object_Version_Number(P_Carrier_info.CARRIER_ID);
609 FETCH Get_Object_Version_Number INTO l_object_version_number;
610 CLOSE Get_Object_Version_Number;
611
612 -- Update the Organization information i.e. the Carrier Name.
613
614 l_position := 10;
615 l_call_procedure := 'Calling TCA API Update_Organization';
616
617 --
618 -- Debug Statements
619 --
620 IF l_debug_on THEN
621 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit HZ_PARTY_PUB.UPDATE_ORGANIZATION',WSH_DEBUG_SV.C_PROC_LEVEL);
622 END IF;
623 --
624
625 HZ_PARTY_V2PUB.Update_Organization
626 (
627 p_init_msg_list => FND_API.G_TRUE,
628 p_organization_rec => l_org_rec,
629 p_party_object_version_number => l_object_version_number,
630 x_profile_id => l_profile_id,
631 x_return_status => l_return_status,
632 x_msg_count => l_msg_count,
633 x_msg_data => l_msg_data
634 );
635
636 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
637 x_return_status := l_return_status;
638 RAISE HZ_FAIL_EXCEPTION;
639 END IF;
640 -- Party Usage
641 OPEN get_carrier_status(P_Carrier_info.CARRIER_ID);
642 FETCH get_carrier_status INTO l_status,l_party_usg_assignment_id;
643 CLOSE get_carrier_status;
644
645 IF(l_status <> P_Carrier_info.STATUS) THEN
646 l_position := 20;
647 l_call_procedure := 'Updating party usage status';
648 IF(P_Carrier_info.STATUS = 'A') THEN
649
650 l_party_usg_assignment_rec.party_id := P_Carrier_info.CARRIER_ID;
651 l_party_usg_assignment_rec.party_usage_code := 'TRANSPORTATION_PROVIDER';
652 l_party_usg_assignment_rec.effective_start_date := sysdate;
653 l_party_usg_assignment_rec.effective_end_date := null;
654 l_party_usg_assignment_rec.created_by_module := 'WSH';
655
656 HZ_PARTY_USG_ASSIGNMENT_PVT.assign_party_usage (
657 p_init_msg_list => FND_API.G_TRUE,
658 p_party_usg_assignment_rec => l_party_usg_assignment_rec,
659 x_return_status => l_return_status,
660 x_msg_count => l_msg_count,
661 x_msg_data=> l_msg_data);
662 ELSIF(P_Carrier_info.STATUS = 'I') THEN
663
664 HZ_PARTY_USG_ASSIGNMENT_PVT.inactivate_usg_assignment (
665 p_init_msg_list => FND_API.G_TRUE,
666 p_validation_level => HZ_PARTY_USG_ASSIGNMENT_PVT.G_VALID_LEVEL_MEDIUM,
667 p_party_usg_assignment_id => l_party_usg_assignment_id,
668 p_party_id=> P_Carrier_info.CARRIER_ID,
669 p_party_usage_code=> 'TRANSPORTATION_PROVIDER',
670 x_return_status => l_return_status,
671 x_msg_count => l_msg_count,
672 x_msg_data=>l_msg_data);
673
674 END IF;
675 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
676 x_return_status := l_return_status;
677 RAISE HZ_FAIL_EXCEPTION;
678 END IF;
679 END IF;
680
681 -- END Party Usage
682 l_position := 30;
683 l_call_procedure := 'Updating WSH_CARRIERS table';
684 --bug 5598102 creation_date and created_by removed from update statement
688 manifesting_enabled_flag = P_Carrier_info.MANIFESTING_ENABLED,
685 UPDATE WSH_CARRIERS
686 SET scac_code = P_Carrier_info.SCAC_CODE,
687 currency_code = P_Carrier_info.CURRENCY_CODE,
689 attribute_category = P_Carrier_info.Attribute_Category,
690 attribute1 = P_Carrier_info.Attribute1,
691 attribute2 = P_Carrier_info.Attribute2,
692 attribute3 = P_Carrier_info.Attribute3,
693 attribute4 = P_Carrier_info.Attribute4,
694 attribute5 = P_Carrier_info.Attribute5,
695 attribute6 = P_Carrier_info.Attribute6,
696 attribute7 = P_Carrier_info.Attribute7,
697 attribute8 = P_Carrier_info.Attribute8,
698 attribute9 = P_Carrier_info.Attribute9,
699 attribute10 = P_Carrier_info.Attribute10,
700 attribute11 = P_Carrier_info.Attribute11,
701 attribute12 = P_Carrier_info.Attribute12,
702 attribute13 = P_Carrier_info.Attribute13,
703 attribute14 = P_Carrier_info.Attribute14,
704 attribute15 = P_Carrier_info.Attribute15,
705 last_update_date = sysdate,
706 last_updated_by = FND_GLOBAL.USER_ID,
707 last_update_login = FND_GLOBAL.login_id,
708 -- Pack J
709 MAX_NUM_STOPS_PERMITTED = P_Carrier_info.MAX_NUM_STOPS_PERMITTED,
710 MAX_TOTAL_DISTANCE = P_Carrier_info.MAX_TOTAL_DISTANCE,
711 MAX_TOTAL_TIME = P_Carrier_info.MAX_TOTAL_TIME,
712 ALLOW_INTERSPERSE_LOAD = P_Carrier_info.ALLOW_INTERSPERSE_LOAD,
713 MAX_LAYOVER_TIME = P_Carrier_info.MAX_LAYOVER_TIME,
714 MIN_LAYOVER_TIME = P_Carrier_info.MIN_LAYOVER_TIME,
715 MAX_TOTAL_DISTANCE_IN_24HR = P_Carrier_info.MAX_TOTAL_DISTANCE_IN_24HR,
716 MAX_DRIVING_TIME_IN_24HR = P_Carrier_info.MAX_DRIVING_TIME_IN_24HR,
717 MAX_DUTY_TIME_IN_24HR = P_Carrier_info.MAX_DUTY_TIME_IN_24HR,
718 MAX_CM_DISTANCE = P_Carrier_info.MAX_CM_DISTANCE,
719 MAX_CM_TIME = P_Carrier_info.MAX_CM_TIME,
720 MAX_CM_DH_DISTANCE = P_Carrier_info.MAX_CM_DH_DISTANCE,
721 MAX_SIZE_WIDTH = P_Carrier_info.MAX_SIZE_WIDTH,
722 MAX_SIZE_HEIGHT = P_Carrier_info.MAX_SIZE_HEIGHT,
723 MAX_SIZE_LENGTH = P_Carrier_info.MAX_SIZE_LENGTH,
724 MIN_SIZE_WIDTH = P_Carrier_info.MIN_SIZE_WIDTH,
725 MIN_SIZE_HEIGHT = P_Carrier_info.MIN_SIZE_HEIGHT,
726 MIN_SIZE_LENGTH = P_Carrier_info.MIN_SIZE_LENGTH,
727 TIME_UOM = P_Carrier_info.TIME_UOM,
728 DIMENSION_UOM = P_Carrier_info.DIMENSION_UOM,
729 DISTANCE_UOM = P_Carrier_info.DISTANCE_UOM,
730 MAX_OUT_OF_ROUTE = P_Carrier_info.MAX_OUT_OF_ROUTE,
731 CM_FREE_DH_MILEAGE = P_Carrier_info.CM_FREE_DH_MILEAGE,
732 MIN_CM_DISTANCE = P_Carrier_info.MIN_CM_DISTANCE,
733 CM_FIRST_LOAD_DISCOUNT = P_Carrier_info.CM_FIRST_LOAD_DISCOUNT,
734 MIN_CM_TIME = P_Carrier_info.MIN_CM_TIME,
735 UNIT_RATE_BASIS = P_Carrier_info.UNIT_RATE_BASIS,
736 WEIGHT_UOM = P_Carrier_info.WEIGHT_UOM,
737 VOLUME_UOM = P_Carrier_info.VOLUME_UOM,
738 GENERIC_FLAG = P_Carrier_info.GENERIC_FLAG,
739 FREIGHT_BILL_AUTO_APPROVAL = P_Carrier_info.FREIGHT_BILL_AUTO_APPROVAL,
740 FREIGHT_AUDIT_LINE_LEVEL = P_Carrier_info.FREIGHT_AUDIT_LINE_LEVEL,
741 SUPPLIER_ID = P_Carrier_info.SUPPLIER_ID,
742 SUPPLIER_SITE_ID = P_Carrier_info.SUPPLIER_SITE_ID,
743 CM_RATE_VARIANT = P_Carrier_info.CM_RATE_VARIANT,
744 DISTANCE_CALCULATION_METHOD = P_Carrier_info.DISTANCE_CALCULATION_METHOD,
745 ALLOW_CONTINUOUS_MOVE = P_Carrier_info.ALLOW_CONTINUOUS_MOVE,
746 MAX_CM_DH_TIME = P_Carrier_info.MAX_CM_DH_TIME,
747 ORIGIN_DSTN_SURCHARGE_LEVEL = P_Carrier_info.ORIGIN_DSTN_SURCHARGE_LEVEL
748 WHERE carrier_id = P_Carrier_info.CARRIER_ID;
749
750 IF SQL%NOTFOUND THEN
751 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
752 RAISE OTHERS;
753 END IF;
754
755 --Inserted hint for performance bug 3639958
756 --bug 5598102 creation_date and created_by removed from update statement
757 UPDATE /*+ index(ORG_FREIGHT_TL ORG_FREIGHT_TL_U1) */ ORG_FREIGHT_TL
758 SET description = SUBSTR(P_Carrier_info.CARRIER_NAME,1,80),
759 attribute_category = P_Carrier_info.Attribute_Category,
760 attribute1 = P_Carrier_info.Attribute1,
761 attribute2 = P_Carrier_info.Attribute2,
762 attribute3 = P_Carrier_info.Attribute3,
763 attribute4 = P_Carrier_info.Attribute4,
764 attribute5 = P_Carrier_info.Attribute5,
765 attribute6 = P_Carrier_info.Attribute6,
769 attribute10 = P_Carrier_info.Attribute10,
766 attribute7 = P_Carrier_info.Attribute7,
767 attribute8 = P_Carrier_info.Attribute8,
768 attribute9 = P_Carrier_info.Attribute9,
770 attribute11 = P_Carrier_info.Attribute11,
771 attribute12 = P_Carrier_info.Attribute12,
772 attribute13 = P_Carrier_info.Attribute13,
773 attribute14 = P_Carrier_info.Attribute14,
774 attribute15 = P_Carrier_info.Attribute15,
775 last_update_date = sysdate,
776 last_updated_by = FND_GLOBAL.USER_ID,
777 last_update_login = FND_GLOBAL.login_id
778 WHERE freight_code = P_Carrier_info.FREIGHT_CODE;
779
780 IF FND_API.To_Boolean(p_commit) THEN
781 COMMIT;
782 END IF;
783
784
785 --
786 -- Debug Statements
787 --
788 IF l_debug_on THEN
789 WSH_DEBUG_SV.pop(l_module_name);
790 END IF;
791 --
792
793
794 EXCEPTION
795
796 WHEN NO_DATA_FOUND THEN
797 x_exception_msg := 'Exception: No Data Found';
798 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
799 x_procedure := l_call_procedure;
800 x_position := l_position;
801 x_sqlerr := sqlerrm;
802 x_sql_code := sqlcode;
803
804 --
805 -- Debug Statements
806 --
807 IF l_debug_on THEN
808 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
809 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
810 END IF;
811 --
812
813 WHEN HZ_FAIL_EXCEPTION THEN
814 x_exception_msg := l_msg_data;
815 x_procedure := l_call_procedure;
816 x_position := l_position;
817 x_sqlerr := sqlerrm;
818 x_sql_code := sqlcode;
819
820 --
821 -- Debug Statements
822 --
823 IF l_debug_on THEN
824 WSH_DEBUG_SV.logmsg(l_module_name,'HZ_FAIL_EXCEPTION exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
825 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:HZ_FAIL_EXCEPTION');
826 END IF;
827 --
828
829 WHEN OTHERS THEN
830 x_exception_msg := 'Exception: Others';
831 x_procedure := l_call_procedure;
832 x_position := l_position;
833 x_sqlerr := sqlerrm;
834 x_sql_code := sqlcode;
835 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
836
837 --
838 -- Debug Statements
839 --
840 IF l_debug_on THEN
841 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
842 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
843 END IF;
844 --
845
846 END UPDATE_CARRIERINFO;
847
848 PROCEDURE Lock_Carriers (
849 P_Carrier_info IN CARecType,
850 p_rowid IN VARCHAR2,
851 x_return_status OUT NOCOPY VARCHAR2
852 )
853 IS
854
855 CURSOR C_lock_row IS
856 SELECT *
857 FROM wsh_carriers
858 WHERE rowid = p_rowid
859 FOR UPDATE of Carrier_id NOWAIT;
860 RecInfo C_Lock_Row%ROWTYPE;
861
862 --4708730
863 CURSOR get_carrier_details is
864 SELECT carrier_name,active
865 FROM wsh_carriers_v
866 WHERE carrier_id =P_Carrier_info.CARRIER_id;
867 Rec_carrier_details get_carrier_details%ROWTYPE;
868
869 record_locked EXCEPTION;
870 PRAGMA EXCEPTION_INIT(record_locked, -54);
871 others Exception;
872
873 --
874 l_debug_on BOOLEAN;
875 --
876 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_CARRIERS';
877 --
878
879 BEGIN
880
881 --
882 -- Debug Statements
883 --
884 --
885 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
886 --
887 IF l_debug_on IS NULL
888 THEN
889 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
890 END IF;
891 --
892 IF l_debug_on THEN
893 WSH_DEBUG_SV.push(l_module_name);
894 --
895 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
896 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.CARRIER_ID',P_Carrier_info.CARRIER_ID);
897 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.FREIGHT_CODE',P_Carrier_info.FREIGHT_CODE);
898 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.CARRIER_NAME',P_Carrier_info.CARRIER_NAME);
899 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.STATUS',P_Carrier_info.STATUS);
900 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.SCAC_CODE',P_Carrier_info.SCAC_CODE);
901 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MANIFESTING_ENABLED',P_Carrier_info.MANIFESTING_ENABLED);
902 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.CURRENCY_CODE',P_Carrier_info.CURRENCY_CODE);
903 -- Pack J
904 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_NUM_STOPS_PERMITTED',P_Carrier_info.MAX_NUM_STOPS_PERMITTED);
905 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_TOTAL_DISTANCE',P_Carrier_info.MAX_TOTAL_DISTANCE);
909 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MIN_LAYOVER_TIME',P_Carrier_info.MIN_LAYOVER_TIME);
906 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_TOTAL_TIME',P_Carrier_info.MAX_TOTAL_TIME);
907 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.ALLOW_INTERSPERSE_LOAD',P_Carrier_info.ALLOW_INTERSPERSE_LOAD);
908 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_LAYOVER_TIME',P_Carrier_info.MAX_LAYOVER_TIME);
910 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_TOTAL_DISTANCE_IN_24HR',P_Carrier_info.MAX_TOTAL_DISTANCE_IN_24HR);
911 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_DRIVING_TIME_IN_24HR',P_Carrier_info.MAX_DRIVING_TIME_IN_24HR);
912 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_DUTY_TIME_IN_24HR',P_Carrier_info.MAX_DUTY_TIME_IN_24HR);
913 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_CM_DISTANCE',P_Carrier_info.MAX_CM_DISTANCE);
914 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_CM_TIME',P_Carrier_info.MAX_CM_TIME);
915 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_CM_DH_DISTANCE',P_Carrier_info.MAX_CM_DH_DISTANCE);
916 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_SIZE_WIDTH',P_Carrier_info.MAX_SIZE_WIDTH);
917 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_SIZE_HEIGHT',P_Carrier_info.MAX_SIZE_HEIGHT);
918 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_SIZE_LENGTH',P_Carrier_info.MAX_SIZE_LENGTH);
919 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MIN_SIZE_WIDTH',P_Carrier_info.MIN_SIZE_WIDTH);
920 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MIN_SIZE_HEIGHT',P_Carrier_info.MIN_SIZE_HEIGHT);
921 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MIN_SIZE_LENGTH',P_Carrier_info.MIN_SIZE_LENGTH);
922 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.TIME_UOM',P_Carrier_info.TIME_UOM);
923 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DIMENSION_UOM',P_Carrier_info.DIMENSION_UOM);
924 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DISTANCE_UOM',P_Carrier_info.DISTANCE_UOM);
925 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_OUT_OF_ROUTE',P_Carrier_info.MAX_OUT_OF_ROUTE);
926 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.CM_FREE_DH_MILEAGE',P_Carrier_info.CM_FREE_DH_MILEAGE);
927 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MIN_CM_DISTANCE',P_Carrier_info.MIN_CM_DISTANCE);
928 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.CM_FIRST_LOAD_DISCOUNT',P_Carrier_info.CM_FIRST_LOAD_DISCOUNT);
929 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MIN_CM_TIME',P_Carrier_info.MIN_CM_TIME);
930 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.UNIT_RATE_BASIS',P_Carrier_info.UNIT_RATE_BASIS);
931 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.WEIGHT_UOM',P_Carrier_info.WEIGHT_UOM);
932 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.VOLUME_UOM',P_Carrier_info.VOLUME_UOM);
933 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.GENERIC_FLAG',P_Carrier_info.GENERIC_FLAG);
934 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.FREIGHT_BILL_AUTO_APPROVAL',P_Carrier_info.FREIGHT_BILL_AUTO_APPROVAL);
935 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.FREIGHT_AUDIT_LINE_LEVEL',P_Carrier_info.FREIGHT_AUDIT_LINE_LEVEL);
936 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.SUPPLIER_ID',P_Carrier_info.SUPPLIER_ID);
937 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.SUPPLIER_SITE_ID',P_Carrier_info.SUPPLIER_SITE_ID);
938 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.CM_RATE_VARIANT',P_Carrier_info.CM_RATE_VARIANT);
939 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DISTANCE_CALCULATION_METHOD',P_Carrier_info.DISTANCE_CALCULATION_METHOD);
940 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.ALLOW_CONTINUOUS_MOVE',P_Carrier_info.ALLOW_CONTINUOUS_MOVE);
941 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.MAX_CM_DH_TIME',P_Carrier_info.MAX_CM_DH_TIME);
942 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.ORIGIN_DSTN_SURCHARGE_LEVEL',P_Carrier_info.ORIGIN_DSTN_SURCHARGE_LEVEL);
943 -- R12 Code changes
944 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DIM_DIMENSIONAL_FACTOR',P_Carrier_info.DIM_DIMENSIONAL_FACTOR);
945 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DIM_WEIGHT_UOM',P_Carrier_info.DIM_WEIGHT_UOM);
946 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DIM_VOLUME_UOM',P_Carrier_info.DIM_VOLUME_UOM);
947 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DIM_DIMENSION_UOM',P_Carrier_info.DIM_DIMENSION_UOM);
948 WSH_DEBUG_SV.log(l_module_name,'P_Carrier_info.DIM_MIN_PACK_VOL',P_Carrier_info.DIM_MIN_PACK_VOL);
949 -- R12 Code changes
950 END IF;
951 --
952
953 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
954
955 OPEN C_lock_row;
956 FETCH C_lock_row INTO Recinfo;
957
958 IF (C_lock_row%NOTFOUND) THEN
959 CLOSE C_lock_row;
960 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
961 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
962 WSH_UTIL_CORE.Add_Message(x_return_status);
963 RETURN;
964 END IF;
965
966 CLOSE C_lock_row;
967
968 OPEN get_carrier_details;
969 FETCH get_carrier_details INTO Rec_carrier_details;
970 CLOSE get_carrier_details;
971
972 IF ( (Recinfo.Carrier_Id = P_Carrier_info.CARRIER_ID)
973 AND (Recinfo.Freight_code = P_Carrier_info.FREIGHT_CODE)
974 --Bug2313801 bug 4708730 party_name and status included
975 AND (Rec_carrier_details.carrier_name = P_Carrier_info.CARRIER_NAME)
976 AND ( (Rec_carrier_details.active = P_Carrier_info.STATUS)
977 OR ( (Rec_carrier_details.active is NULL)
978 AND (P_Carrier_info.STATUS IS NULL)))
979 AND ( (Recinfo.scac_code = P_Carrier_info.SCAC_CODE)
980 OR ( (Recinfo.scac_code is NULL)
981 AND (P_Carrier_info.SCAC_CODE IS NULL)))
982 AND ( (Recinfo.currency_code = P_Carrier_info.CURRENCY_CODE)
986 OR ( (Recinfo.manifesting_enabled_flag IS NULL)
983 OR ( (Recinfo.currency_code is NULL)
984 AND (P_Carrier_info.CURRENCY_CODE IS NULL)))
985 AND ( (Recinfo.manifesting_enabled_flag = P_Carrier_info.MANIFESTING_ENABLED)
987 AND (P_Carrier_info.MANIFESTING_ENABLED is NULL)))
988 AND ( (Recinfo.Attribute_Category = P_Carrier_info.Attribute_Category)
989 OR ( (Recinfo.Attribute_Category is NULL)
990 AND (P_Carrier_info.Attribute_Category IS NULL)))
991 AND ( (Recinfo.Attribute1 = P_Carrier_info.Attribute1)
992 OR ( (Recinfo.Attribute1 IS NULL)
993 AND (P_Carrier_info.Attribute1 is NULL)))
994 AND ( (Recinfo.Attribute2 = P_Carrier_info.Attribute2)
995 OR ( (Recinfo.Attribute2 IS NULL)
996 AND (P_Carrier_info.Attribute2 is NULL)))
997 AND ( (Recinfo.Attribute3 = P_Carrier_info.Attribute3)
998 OR ( (Recinfo.Attribute3 IS NULL)
999 AND (P_Carrier_info.Attribute3 is NULL)))
1000 AND ( (Recinfo.Attribute4 = P_Carrier_info.Attribute4)
1001 OR ( (Recinfo.Attribute4 IS NULL)
1002 AND (P_Carrier_info.Attribute4 is NULL)))
1003 AND ( (Recinfo.Attribute5 = P_Carrier_info.Attribute5)
1004 OR ( (Recinfo.Attribute5 IS NULL)
1005 AND (P_Carrier_info.Attribute5 is NULL)))
1006 AND ( (Recinfo.Attribute6 = P_Carrier_info.Attribute6)
1007 OR ( (Recinfo.Attribute6 IS NULL)
1008 AND (P_Carrier_info.Attribute6 is NULL)))
1009 AND ( (Recinfo.Attribute7 = P_Carrier_info.Attribute7)
1010 OR ( (Recinfo.Attribute7 IS NULL)
1011 AND (P_Carrier_info.Attribute7 is NULL)))
1012 AND ( (Recinfo.Attribute8 = P_Carrier_info.Attribute8)
1013 OR ( (Recinfo.Attribute8 IS NULL)
1014 AND (P_Carrier_info.Attribute8 is NULL)))
1015 AND ( (Recinfo.Attribute9 = P_Carrier_info.Attribute9)
1016 OR ( (Recinfo.Attribute9 IS NULL)
1017 AND (P_Carrier_info.Attribute9 is NULL)))
1018 AND ( (Recinfo.Attribute10 = P_Carrier_info.Attribute10)
1019 OR ( (Recinfo.Attribute10 IS NULL)
1020 AND (P_Carrier_info.Attribute10 is NULL)))
1021 AND ( (Recinfo.Attribute11 = P_Carrier_info.Attribute11)
1022 OR ( (Recinfo.Attribute11 IS NULL)
1023 AND (P_Carrier_info.Attribute11 is NULL)))
1024 AND ( (Recinfo.Attribute12 = P_Carrier_info.Attribute12)
1025 OR ( (Recinfo.Attribute12 IS NULL)
1026 AND (P_Carrier_info.Attribute12 is NULL)))
1027 AND ( (Recinfo.Attribute13 = P_Carrier_info.Attribute13)
1028 OR ( (Recinfo.Attribute13 IS NULL)
1029 AND (P_Carrier_info.Attribute13 is NULL)))
1030 AND ( (Recinfo.Attribute14 = P_Carrier_info.Attribute14)
1031 OR ( (Recinfo.Attribute14 IS NULL)
1032 AND (P_Carrier_info.Attribute14 is NULL)))
1033 AND ( (Recinfo.Attribute15 = P_Carrier_info.Attribute15)
1034 OR ( (Recinfo.Attribute15 IS NULL)
1035 AND (P_Carrier_info.Attribute15 is NULL)))
1036 -- Pack J
1037 AND ( (Recinfo.MAX_NUM_STOPS_PERMITTED = P_Carrier_info.MAX_NUM_STOPS_PERMITTED)
1038 OR ( (Recinfo.MAX_NUM_STOPS_PERMITTED IS NULL)
1039 AND (P_Carrier_info.MAX_NUM_STOPS_PERMITTED is NULL)))
1040 AND ( (Recinfo.MAX_TOTAL_DISTANCE = P_Carrier_info.MAX_TOTAL_DISTANCE)
1041 OR ( (Recinfo.MAX_TOTAL_DISTANCE IS NULL)
1042 AND (P_Carrier_info.MAX_TOTAL_DISTANCE is NULL)))
1043 AND ( (Recinfo.MAX_TOTAL_TIME = P_Carrier_info.MAX_TOTAL_TIME)
1044 OR ( (Recinfo.MAX_TOTAL_TIME IS NULL)
1045 AND (P_Carrier_info.MAX_TOTAL_TIME is NULL)))
1046 AND ( (Recinfo.ALLOW_INTERSPERSE_LOAD = P_Carrier_info.ALLOW_INTERSPERSE_LOAD)
1047 OR ( (Recinfo.ALLOW_INTERSPERSE_LOAD IS NULL)
1048 AND (P_Carrier_info.ALLOW_INTERSPERSE_LOAD is NULL)))
1049 AND ( (Recinfo.MAX_LAYOVER_TIME = P_Carrier_info.MAX_LAYOVER_TIME)
1050 OR ( (Recinfo.MAX_LAYOVER_TIME IS NULL)
1051 AND (P_Carrier_info.MAX_LAYOVER_TIME is NULL)))
1052 AND ( (Recinfo.MIN_LAYOVER_TIME = P_Carrier_info.MIN_LAYOVER_TIME)
1053 OR ( (Recinfo.MIN_LAYOVER_TIME IS NULL)
1054 AND (P_Carrier_info.MIN_LAYOVER_TIME is NULL)))
1055 AND ( (Recinfo.MAX_TOTAL_DISTANCE_IN_24HR = P_Carrier_info.MAX_TOTAL_DISTANCE_IN_24HR)
1056 OR ( (Recinfo.MAX_TOTAL_DISTANCE_IN_24HR IS NULL)
1057 AND (P_Carrier_info.MAX_TOTAL_DISTANCE_IN_24HR is NULL)))
1058 AND ( (Recinfo.MAX_DRIVING_TIME_IN_24HR = P_Carrier_info.MAX_DRIVING_TIME_IN_24HR)
1059 OR ( (Recinfo.MAX_DRIVING_TIME_IN_24HR IS NULL)
1060 AND (P_Carrier_info.MAX_DRIVING_TIME_IN_24HR is NULL)))
1061 AND ( (Recinfo.MAX_DUTY_TIME_IN_24HR = P_Carrier_info.MAX_DUTY_TIME_IN_24HR)
1062 OR ( (Recinfo.MAX_DUTY_TIME_IN_24HR IS NULL)
1063 AND (P_Carrier_info.MAX_DUTY_TIME_IN_24HR is NULL)))
1064 AND ( (Recinfo.MAX_CM_DISTANCE = P_Carrier_info.MAX_CM_DISTANCE)
1065 OR ( (Recinfo.MAX_CM_DISTANCE IS NULL)
1066 AND (P_Carrier_info.MAX_CM_DISTANCE is NULL)))
1067 AND ( (Recinfo.MAX_CM_TIME = P_Carrier_info.MAX_CM_TIME)
1068 OR ( (Recinfo.MAX_CM_TIME IS NULL)
1069 AND (P_Carrier_info.MAX_CM_TIME is NULL)))
1070 AND ( (Recinfo.MAX_CM_DH_DISTANCE = P_Carrier_info.MAX_CM_DH_DISTANCE )
1071 OR ( (Recinfo.MAX_CM_DH_DISTANCE IS NULL)
1072 AND (P_Carrier_info.MAX_CM_DH_DISTANCE is NULL)))
1073 AND ( (Recinfo.MAX_SIZE_WIDTH = P_Carrier_info.MAX_SIZE_WIDTH )
1074 OR ( (Recinfo.MAX_SIZE_WIDTH IS NULL)
1075 AND (P_Carrier_info.MAX_SIZE_WIDTH is NULL)))
1076 AND ( (Recinfo.MAX_SIZE_HEIGHT = P_Carrier_info.MAX_SIZE_HEIGHT )
1080 OR ( (Recinfo.MAX_SIZE_LENGTH IS NULL)
1077 OR ( (Recinfo.MAX_SIZE_HEIGHT IS NULL)
1078 AND (P_Carrier_info.MAX_SIZE_HEIGHT is NULL)))
1079 AND ( (Recinfo.MAX_SIZE_LENGTH = P_Carrier_info.MAX_SIZE_LENGTH )
1081 AND (P_Carrier_info.MAX_SIZE_LENGTH is NULL)))
1082 AND ( (Recinfo.MIN_SIZE_WIDTH = P_Carrier_info.MIN_SIZE_WIDTH )
1083 OR ( (Recinfo.MIN_SIZE_WIDTH IS NULL)
1084 AND (P_Carrier_info.MIN_SIZE_WIDTH is NULL)))
1085 AND ( (Recinfo.MIN_SIZE_HEIGHT = P_Carrier_info.MIN_SIZE_HEIGHT )
1086 OR ( (Recinfo.MIN_SIZE_HEIGHT IS NULL)
1087 AND (P_Carrier_info.MIN_SIZE_HEIGHT is NULL)))
1088 AND ( (Recinfo.MIN_SIZE_LENGTH = P_Carrier_info.MIN_SIZE_LENGTH )
1089 OR ( (Recinfo.MIN_SIZE_LENGTH IS NULL)
1090 AND (P_Carrier_info.MIN_SIZE_LENGTH is NULL)))
1091 AND ( (Recinfo.TIME_UOM = P_Carrier_info.TIME_UOM )
1092 OR ( (Recinfo.TIME_UOM IS NULL)
1093 AND (P_Carrier_info.TIME_UOM is NULL)))
1094 AND ( (Recinfo.DIMENSION_UOM = P_Carrier_info.DIMENSION_UOM )
1095 OR ( (Recinfo.DIMENSION_UOM IS NULL)
1096 AND (P_Carrier_info.DIMENSION_UOM is NULL)))
1097 AND ( (Recinfo.DISTANCE_UOM = P_Carrier_info.DISTANCE_UOM )
1098 OR ( (Recinfo.DISTANCE_UOM IS NULL)
1099 AND (P_Carrier_info.DISTANCE_UOM is NULL)))
1100 AND ( (Recinfo.MAX_OUT_OF_ROUTE = P_Carrier_info.MAX_OUT_OF_ROUTE)
1101 OR ( (Recinfo.MAX_OUT_OF_ROUTE IS NULL)
1102 AND (P_Carrier_info.MAX_OUT_OF_ROUTE is NULL)))
1103 AND ( (Recinfo.CM_FREE_DH_MILEAGE = P_Carrier_info.CM_FREE_DH_MILEAGE)
1104 OR ( (Recinfo.CM_FREE_DH_MILEAGE IS NULL)
1105 AND (P_Carrier_info.CM_FREE_DH_MILEAGE is NULL)))
1106 AND ( (Recinfo.MIN_CM_DISTANCE = P_Carrier_info.MIN_CM_DISTANCE )
1107 OR ( (Recinfo.MIN_CM_DISTANCE IS NULL)
1108 AND (P_Carrier_info.MIN_CM_DISTANCE is NULL)))
1109 AND ( (Recinfo.CM_FIRST_LOAD_DISCOUNT = P_Carrier_info.CM_FIRST_LOAD_DISCOUNT )
1110 OR ( (Recinfo.CM_FIRST_LOAD_DISCOUNT IS NULL)
1111 AND (P_Carrier_info.CM_FIRST_LOAD_DISCOUNT is NULL)))
1112 AND ( (Recinfo.MIN_CM_TIME = P_Carrier_info.MIN_CM_TIME )
1113 OR ( (Recinfo.MIN_CM_TIME IS NULL)
1114 AND (P_Carrier_info.MIN_CM_TIME is NULL)))
1115 AND ( (Recinfo.UNIT_RATE_BASIS = P_Carrier_info.UNIT_RATE_BASIS )
1116 OR ( (Recinfo.UNIT_RATE_BASIS IS NULL)
1117 AND (P_Carrier_info.UNIT_RATE_BASIS is NULL)))
1118 AND ( (Recinfo.WEIGHT_UOM = P_Carrier_info.WEIGHT_UOM )
1119 OR ( (Recinfo.WEIGHT_UOM IS NULL)
1120 AND (P_Carrier_info.WEIGHT_UOM is NULL)))
1121 AND ( (Recinfo.VOLUME_UOM = P_Carrier_info.VOLUME_UOM )
1122 OR ( (Recinfo.VOLUME_UOM IS NULL)
1123 AND (P_Carrier_info.VOLUME_UOM is NULL)))
1124 AND ( (Recinfo.GENERIC_FLAG = P_Carrier_info.GENERIC_FLAG )
1125 OR ( (Recinfo.GENERIC_FLAG IS NULL)
1126 AND (P_Carrier_info.GENERIC_FLAG is NULL)))
1127 AND ( (Recinfo.FREIGHT_BILL_AUTO_APPROVAL = P_Carrier_info.FREIGHT_BILL_AUTO_APPROVAL)
1128 OR ( (Recinfo.FREIGHT_BILL_AUTO_APPROVAL IS NULL)
1129 AND (P_Carrier_info.FREIGHT_BILL_AUTO_APPROVAL is NULL)))
1130 AND ( (Recinfo.FREIGHT_AUDIT_LINE_LEVEL = P_Carrier_info.FREIGHT_AUDIT_LINE_LEVEL)
1131 OR ( (Recinfo.FREIGHT_AUDIT_LINE_LEVEL IS NULL)
1132 AND (P_Carrier_info.FREIGHT_AUDIT_LINE_LEVEL is NULL)))
1133 AND ( (Recinfo.SUPPLIER_ID = P_Carrier_info.SUPPLIER_ID )
1134 OR ( (Recinfo.SUPPLIER_ID IS NULL)
1135 AND (P_Carrier_info.SUPPLIER_ID is NULL)))
1136 AND ( (Recinfo.SUPPLIER_SITE_ID = P_Carrier_info.SUPPLIER_SITE_ID)
1137 OR ( (Recinfo.SUPPLIER_SITE_ID IS NULL)
1138 AND (P_Carrier_info.SUPPLIER_SITE_ID is NULL)))
1139 AND ( (Recinfo.CM_RATE_VARIANT = P_Carrier_info.CM_RATE_VARIANT)
1140 OR ( (Recinfo.CM_RATE_VARIANT IS NULL)
1141 AND (P_Carrier_info.CM_RATE_VARIANT is NULL)))
1142 AND ( (Recinfo.DISTANCE_CALCULATION_METHOD = P_Carrier_info.DISTANCE_CALCULATION_METHOD)
1143 OR ( (Recinfo.DISTANCE_CALCULATION_METHOD IS NULL)
1144 AND (P_Carrier_info.DISTANCE_CALCULATION_METHOD is NULL)))
1145 AND ((Recinfo.ALLOW_CONTINUOUS_MOVE = P_Carrier_info.ALLOW_CONTINUOUS_MOVE)
1146 OR ((Recinfo.ALLOW_CONTINUOUS_MOVE IS NULL)
1147 AND (P_Carrier_info.ALLOW_CONTINUOUS_MOVE is NULL)))
1148 AND ((Recinfo.MAX_CM_DH_TIME = P_Carrier_info.MAX_CM_DH_TIME)
1149 OR ((Recinfo.MAX_CM_DH_TIME IS NULL)
1150 AND (P_Carrier_info.MAX_CM_DH_TIME is NULL)))
1151 AND ((Recinfo.ORIGIN_DSTN_SURCHARGE_LEVEL = P_Carrier_info.ORIGIN_DSTN_SURCHARGE_LEVEL)
1152 OR ((Recinfo.ORIGIN_DSTN_SURCHARGE_LEVEL IS NULL)
1153 AND (P_Carrier_info.ORIGIN_DSTN_SURCHARGE_LEVEL is NULL)))
1154 ) THEN
1155 RETURN;
1156 ELSE
1157 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1158 APP_EXCEPTION.Raise_Exception;
1159 END IF;
1160
1161 --
1162 -- Debug Statements
1163 --
1164 IF l_debug_on THEN
1165 WSH_DEBUG_SV.pop(l_module_name);
1166 END IF;
1167 --
1168
1169 EXCEPTION
1170 WHEN RECORD_LOCKED THEN
1171
1172 IF (C_Lock_Row%ISOPEN) then
1173 CLOSE C_Lock_Row;
1174 END IF;
1175 IF (get_carrier_details%ISOPEN) then
1176 CLOSE get_carrier_details;
1177 END IF;
1178 FND_MESSAGE.Set_Name('WSH', 'WSH_FORM_RECORD_IS_CHANGED');
1179 app_exception.raise_exception;
1180 WHEN others THEN
1181 IF (C_Lock_Row%ISOPEN) then
1182 CLOSE C_Lock_Row;
1183 END IF;
1184 IF (get_carrier_details%ISOPEN) then
1185 CLOSE get_carrier_details;
1186 END IF;
1187 x_Return_Status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1191 -- Debug Statements
1188 WSH_UTIL_CORE.Default_Handler('WSH_CREATE_CARRIERS_PKG.Lock_Carrier');
1189
1190 --
1192 --
1193 IF l_debug_on THEN
1194 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1195 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1196 END IF;
1197 --
1198
1199 END Lock_Carriers;
1200
1201
1202 PROCEDURE Create_Code_Assgn(
1203 p_carrier_id IN NUMBER,
1204 p_class_code IN VARCHAR2,
1205 p_enabled IN VARCHAR2,
1206 x_code_assignment_id OUT NOCOPY NUMBER,
1207 x_return_status OUT NOCOPY VARCHAR2,
1208 x_position OUT NOCOPY NUMBER,
1209 x_procedure OUT NOCOPY VARCHAR2,
1210 x_exception_msg OUT NOCOPY VARCHAR2,
1211 x_sql_code OUT NOCOPY NUMBER,
1212 x_sqlerr OUT NOCOPY VARCHAR2 ) IS
1213
1214 -- General Declarations
1215 l_return_status varchar2(100);
1216 l_msg_count number;
1217 l_msg_data varchar2(2000);
1218 l_party_number varchar2(100);
1219 HZ_FAIL_EXCEPTION exception;
1220 OTHERS exception;
1221 l_position number;
1222 l_code_assignment_id number;
1223 l_call_procedure varchar2(200);
1224
1225 l_code_assignment_rec_type HZ_CLASSIFICATION_V2PUB.Code_Assignment_Rec_Type;
1226
1227 BEGIN
1228
1229 l_code_assignment_rec_type.owner_table_name := 'HZ_PARTIES';
1230 l_code_assignment_rec_type.owner_table_id := p_carrier_id;
1231 l_code_assignment_rec_type.created_by_module := 'ORACLE_SHIPPING';
1232 l_code_assignment_rec_type.class_category := 'TRANSPORTATION_PROVIDERS';
1233 l_code_assignment_rec_type.class_code := p_class_code;
1234 l_code_assignment_Rec_type.primary_flag := 'Y';
1235 l_code_assignment_rec_type.status := p_enabled;
1236 l_code_assignment_rec_type.content_source_type := 'USER_ENTERED';
1237 l_code_assignment_rec_type.start_date_active := sysdate;
1238
1239 l_position := 10;
1240 l_call_procedure := 'Calling TCA API Create_Code_Assignment';
1241
1242 HZ_CLASSIFICATION_V2PUB.Create_Code_Assignment
1243 ( p_init_msg_list => FND_API.G_TRUE,
1244 p_code_assignment_rec => l_code_assignment_rec_type,
1245 x_return_status => l_return_status,
1246 x_msg_count => l_msg_count,
1247 x_msg_data => l_msg_data,
1248 x_code_assignment_id => l_code_assignment_id );
1249
1250 IF l_return_status <> 'S' THEN
1251 x_return_status := l_return_status;
1252 Raise Hz_Fail_Exception;
1253 END IF;
1254
1255 x_code_assignment_id := l_code_assignment_id;
1256
1257 EXCEPTION
1258
1259 WHEN NO_DATA_FOUND THEN
1260 x_exception_msg := 'NO_DATA_FOUND Exception Raised';
1261 x_position := l_position;
1262 x_procedure := l_call_procedure;
1263 x_sqlerr := sqlerrm;
1264 x_sql_code := sqlcode;
1265 x_return_status := 'E';
1266
1267 WHEN HZ_FAIL_EXCEPTION THEN
1268 x_exception_msg := l_msg_data;
1269 x_position := l_position;
1270 x_procedure := l_call_procedure;
1271 x_sqlerr := sqlerrm;
1272 x_sql_code := sqlcode;
1273
1274 WHEN OTHERS THEN
1275 l_position := 11;
1276 x_exception_msg := 'WHEN OTHERS Exception Raise';
1277 x_position := l_position;
1278 x_procedure := l_call_procedure;
1279 x_sqlerr := sqlerrm;
1280 x_sql_code := sqlcode;
1281 x_return_status := 'E';
1282
1283 END Create_Code_Assgn;
1284
1285
1286 PROCEDURE Update_Code_Assgn(
1287 p_class_code IN VARCHAR2,
1288 p_enabled IN VARCHAR2,
1289 p_code_assignment_id IN NUMBER,
1290 x_return_status OUT NOCOPY VARCHAR2,
1291 x_position OUT NOCOPY NUMBER,
1292 x_procedure OUT NOCOPY VARCHAR2,
1293 x_exception_msg OUT NOCOPY VARCHAR2,
1294 x_sql_code OUT NOCOPY NUMBER,
1295 x_sqlerr OUT NOCOPY VARCHAR2 ) IS
1296
1297 -- General Declarations
1298
1299 l_return_status varchar2(100);
1300 l_msg_count number;
1301 l_msg_data varchar2(2000);
1302 l_party_number varchar2(100);
1303 HZ_FAIL_EXCEPTION exception;
1304 OTHERS exception;
1305 l_position number;
1306 l_code_assignment_id number;
1307 l_object_version_number number;
1308 l_call_procedure varchar2(200);
1309
1310 l_code_assignment_rec_type HZ_CLASSIFICATION_V2PUB.Code_Assignment_Rec_Type;
1311
1312 CURSOR Get_Object_Version_Number IS
1313 SELECT object_version_number
1314 FROM hz_code_assignments
1315 WHERE code_assignment_id = p_code_assignment_id;
1316
1317
1318 BEGIN
1319
1323
1320 OPEN Get_Object_Version_Number;
1321 FETCH Get_Object_Version_Number INTO l_object_version_number;
1322 CLOSE Get_Object_Version_Number;
1324 l_code_assignment_rec_type.code_assignment_id := p_code_assignment_id;
1325 l_code_assignment_rec_type.class_category := 'TRANSPORTATION_PROVIDERS';
1326 l_code_assignment_rec_type.primary_flag := 'N';
1327 l_code_assignment_rec_type.status := p_enabled;
1328
1329 HZ_CLASSIFICATION_V2PUB.Update_Code_Assignment(
1330 p_init_msg_list => FND_API.G_TRUE,
1331 p_code_assignment_rec => l_code_assignment_rec_type,
1332 p_object_version_number => l_object_version_number,
1333 x_return_status => l_return_status,
1334 x_msg_count => l_msg_count,
1335 x_msg_data => l_msg_data );
1336
1337 IF l_return_status <> 'S' THEN
1338 x_return_status := l_return_status;
1339 Raise Hz_Fail_Exception;
1340 END IF;
1341
1342 EXCEPTION
1343
1344
1345
1346 WHEN NO_DATA_FOUND THEN
1347 x_exception_msg := 'NO_DATA_FOUND Exception Raised';
1348 x_position := l_position;
1349 x_procedure := l_call_procedure;
1350 x_sqlerr := sqlerrm;
1351 x_sql_code := sqlcode;
1352 x_return_status := 'E';
1353
1354 WHEN HZ_FAIL_EXCEPTION THEN
1355 x_exception_msg := l_msg_data;
1356 x_position := l_position;
1357 x_procedure := l_call_procedure;
1358 x_sqlerr := sqlerrm;
1359 x_sql_code := sqlcode;
1360
1361 WHEN OTHERS THEN
1362 x_exception_msg := 'WHEN OTHERS Exception Raise';
1363 x_position := l_position;
1364 x_procedure := l_call_procedure;
1365 x_sqlerr := sqlerrm;
1366 x_sql_code := sqlcode;
1367 x_return_status := 'E';
1368
1369 END Update_Code_Assgn;
1370
1371 PROCEDURE Get_Site_Trans_Details(
1372 p_carrier_id IN NUMBER
1373 , p_organization_id IN NUMBER
1374 , x_site_trans_rec OUT NOCOPY Site_Rec_Type
1375 , x_return_status OUT NOCOPY VARCHAR2 ) IS
1376
1377 CURSOR Get_Site_Details IS
1378 SELECT
1379 WCS.CARRIER_ID,
1380 WCS.CARRIER_SITE_ID,
1381 WCS.EMAIL_ADDRESS,
1382 WCS.AUTO_ACCEPT_LOAD_TENDER,
1383 WCS.TENDER_WAIT_TIME,
1384 WCS.WAIT_TIME_UOM,
1385 WCS.WEIGHT_THRESHOLD_UPPER,
1386 WCS.WEIGHT_THRESHOLD_LOWER,
1387 WCS.VOLUME_THRESHOLD_UPPER,
1388 WCS.VOLUME_THRESHOLD_LOWER,
1389 WCS.ENABLE_AUTO_TENDER, -- R12 Code changes
1390 WCS.TENDER_TRANSMISSION_METHOD
1391 FROM
1392 WSH_CARRIER_SITES WCS,
1393 WSH_ORG_CARRIER_SITES WOCS
1394 WHERE
1395 WCS.CARRIER_ID = p_carrier_id AND
1396 WOCS.ORGANIZATION_ID = p_organization_id AND
1397 WOCS.CARRIER_SITE_ID = WCS.CARRIER_SITE_ID AND
1398 WOCS.ENABLED_FLAG = 'Y';
1399
1400 l_debug_on BOOLEAN;
1401 --
1402 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Site_Details';
1403 --
1404
1405 BEGIN
1406
1407 --
1408 -- Debug Statements
1409 --
1410
1411 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1412 --
1413 IF l_debug_on IS NULL
1414 THEN
1415 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1416 END IF;
1417 --
1418
1419 IF l_debug_on THEN
1420 WSH_DEBUG_SV.push(l_module_name);
1421 --
1422 WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_ID',P_CARRIER_ID);
1423 WSH_DEBUG_SV.log(l_module_name,'P_ORGANIZATION_ID',P_ORGANIZATION_ID);
1424
1425 END IF;
1426 --
1427
1428 x_Return_Status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1429
1430 OPEN Get_Site_Details;
1431 FETCH Get_Site_Details INTO
1432 x_site_trans_rec.carrier_id,
1433 x_site_trans_rec.carrier_site_id,
1434 x_site_trans_rec.email_address,
1435 x_site_trans_rec.auto_accept_load_tender,
1436 x_site_trans_rec.tender_wait_time,
1437 x_site_trans_rec.wait_time_uom,
1438 x_site_trans_rec.weight_threshold_upper,
1439 x_site_trans_rec.weight_threshold_lower,
1440 x_site_trans_rec.volume_threshold_upper,
1441 x_site_trans_rec.volume_threshold_lower,
1442 x_site_trans_rec.enable_auto_tender,
1443 x_site_trans_rec.TENDER_TRANSMISSION_METHOD;
1444 CLOSE Get_Site_Details;
1445
1446 IF x_site_trans_rec.carrier_id IS NULL THEN
1447 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1448 END IF;
1449
1450 --
1451 -- Debug Statements
1452 --
1453 IF l_debug_on THEN
1454 WSH_DEBUG_SV.pop(l_module_name);
1455 END IF;
1456 --
1457
1458 EXCEPTION
1459
1460 WHEN OTHERS THEN
1461
1462 x_Return_Status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1463
1464 --
1465 -- Debug Statements
1466 --
1467 IF l_debug_on THEN
1468 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1469 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1470 END IF;
1471 --
1472
1476 p_carrier_id IN NUMBER
1473 END Get_Site_Trans_Details;
1474
1475 PROCEDURE Carrier_Deactivate(
1477 ,x_return_status OUT NOCOPY VARCHAR2
1478 ,x_position OUT NOCOPY NUMBER
1479 ,x_exception_msg OUT NOCOPY VARCHAR2
1480 ,x_procedure OUT NOCOPY VARCHAR2
1481 ,x_sqlerr OUT NOCOPY VARCHAR2
1482 ,x_sql_code OUT NOCOPY VARCHAR2
1483 ) IS
1484
1485 CURSOR C_get_carrier_services(p_carrier_id NUMBER) IS
1486 SELECT carrier_service_id
1487 FROM wsh_carrier_services
1488 WHERE carrier_id = p_carrier_id;
1489
1490 CURSOR C_get_carrier_sites(p_carrier_id NUMBER) IS
1491 SELECT party_site_id
1492 FROM hz_party_sites
1493 WHERE party_id=p_carrier_id;
1494
1495
1496 CURSOR Get_Site_Object_Number(p_party_site_id NUMBER) IS
1497 select object_version_number
1498 from hz_party_sites
1499 where party_site_id = p_party_site_id;
1500
1501
1502 CURSOR C_lookup_row(p_ship_method_code VARCHAR2) IS
1503 --Bug3330869 SELECT *
1504 SELECT LOOKUP_CODE,
1505 TAG,
1506 ATTRIBUTE_CATEGORY,
1507 ATTRIBUTE1,
1508 ATTRIBUTE2,
1509 ATTRIBUTE3,
1510 ATTRIBUTE4,
1511 START_DATE_ACTIVE,
1512 END_DATE_ACTIVE,
1513 TERRITORY_CODE,
1514 ATTRIBUTE5,
1515 ATTRIBUTE6,
1516 ATTRIBUTE7,
1517 ATTRIBUTE8,
1518 ATTRIBUTE9,
1519 ATTRIBUTE10,
1520 ATTRIBUTE11,
1521 ATTRIBUTE12,
1522 ATTRIBUTE13,
1523 ATTRIBUTE14,
1524 ATTRIBUTE15,
1525 MEANING,
1526 DESCRIPTION
1527 FROM fnd_lookup_values
1528 where lookup_type = 'SHIP_METHOD'
1529 and security_group_id = 0
1530 and view_application_id = 3
1531 and lookup_code = p_ship_method_code;
1532
1533 lookupinfo C_lookup_row%ROWTYPE;
1534
1535 CURSOR C_get_FREIGHT_code(p_carrier_id NUMBER) IS
1536 SELECT freight_code
1537 FROM wsh_carriers
1538 WHERE carrier_id = p_carrier_id;
1539
1540 CURSOR C_get_ship_method_code(p_carrier_service_id NUMBER) IS
1541 SELECT ship_method_code
1542 FROM wsh_carrier_services
1543 WHERE carrier_service_id = p_carrier_service_id;
1544
1545 hz_fail_exception EXCEPTION;
1546 unassignall_fail_exception EXCEPTION;
1547 l_carrier_service_id NUMBER;
1548 l_ship_method_code VARCHAR(200);
1549 l_return_status VARCHAR2(200);
1550
1551 l_position NUMBER;
1552 l_procedure VARCHAR2(50);
1553 l_sqlerr VARCHAR2(150);
1554 l_sql_code VARCHAR2(50);
1555 l_exception_msg VARCHAR(150);
1556
1557 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1558 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Carrier_Deactivate';
1559
1560 l_site_rec HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE;
1561 l_loc_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
1562
1563 l_site_object_number NUMBER;
1564 l_carrier_site_id NUMBER;
1565
1566 l_msg_count NUMBER;
1567 l_msg_data VARCHAR2(2000);
1568 l_freight_code VARCHAR(200);
1569
1570 BEGIN
1571 IF l_debug_on THEN
1572 WSH_DEBUG_SV.push(l_module_name);
1573 WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_ID',p_carrier_id);
1574 END IF;
1575 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1576
1577 -- Deactivation of Carrier Services
1578
1579 OPEN C_get_freight_code(p_carrier_id);
1580 FETCH C_get_freight_code INTO l_freight_code;
1581 CLOSE C_get_freight_code;
1582
1583 IF l_debug_on THEN
1584 WSH_DEBUG_SV.log(l_module_name,'Deactivating Carrier Services of',p_carrier_id);
1585 WSH_DEBUG_SV.log(l_module_name,'L_FREIGHT_CODE',l_freight_code);
1586 END IF;
1587
1588 OPEN C_get_carrier_services(p_carrier_id);
1589 FETCH C_get_carrier_services INTO l_carrier_service_id;
1590 -- Looping for each carrier service ID
1591 WHILE C_get_carrier_services%FOUND
1592 LOOP
1593 OPEN C_get_ship_method_code(l_carrier_service_id);
1594 FETCH C_get_ship_method_code INTO l_ship_method_code;
1595 CLOSE C_get_ship_method_code;
1596 IF l_debug_on THEN
1597 WSH_DEBUG_SV.log(l_module_name,'L_CARRIER_SERVICE_ID',l_carrier_service_id);
1598 WSH_DEBUG_SV.log(l_module_name,'L_SHIP_METHOD_CODE',l_ship_method_code);
1599 END IF;
1600
1601 IF l_ship_method_code IS NOT NULL THEN
1602 l_position := 10;
1603 l_procedure := 'Calling Car_Ser_Unassign_AllOrg';
1604
1605 -- Unassigning Organization from Carrier service
1606 WSH_CARRIER_SERVICES_PKG.Car_Ser_Unassign_AllOrg(
1607 p_carrier_service_id => l_carrier_service_id,
1608 p_ship_method_code => l_ship_method_code,
1609 p_freight_code => l_freight_code,
1610 x_exception_msg => l_exception_msg,
1611 x_return_status => l_return_status,
1612 x_position => l_position,
1613 x_procedure => l_procedure,
1614 x_sqlerr => l_sqlerr,
1615 x_sql_code => l_sql_code
1616 );
1617
1618 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1619 x_return_status := l_return_status;
1623 l_position := 20;
1620 RAISE UNASSIGNALL_FAIL_EXCEPTION;
1621 END IF;
1622
1624 l_procedure := 'Fetching from cursor';
1625 -- Disable SHIP_METHOD Look Up value.
1626 OPEN C_lookup_row(l_ship_method_code);
1627 FETCH C_lookup_row INTO lookupinfo;
1628
1629 IF (C_lookup_row%NOTFOUND) THEN
1630 CLOSE C_lookup_row;
1631 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
1632 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1633 IF l_debug_on THEN
1634 WSH_DEBUG_SV.log(l_module_name,'C_lookup_row%NOTFOUND');
1635 END IF;
1636 RAISE NO_DATA_FOUND;
1637 END IF;
1638 CLOSE C_lookup_row;
1639
1640 l_position := 30;
1641 l_procedure := 'Updating FND_LOOKUP_VALUES';
1642
1643 FND_LOOKUP_VALUES_PKG.UPDATE_ROW
1644 (
1645 X_LOOKUP_TYPE => 'SHIP_METHOD',
1646 X_SECURITY_GROUP_ID => 0,
1647 X_VIEW_APPLICATION_ID => 3,
1648 X_LOOKUP_CODE => lookupinfo.LOOKUP_CODE,
1649 X_TAG => lookupinfo.TAG,
1650 X_ATTRIBUTE_CATEGORY => lookupinfo.ATTRIBUTE_CATEGORY,
1651 X_ATTRIBUTE1 => lookupinfo.ATTRIBUTE1,
1652 X_ATTRIBUTE2 => lookupinfo.ATTRIBUTE2,
1653 X_ATTRIBUTE3 => lookupinfo.ATTRIBUTE3,
1654 X_ATTRIBUTE4 => lookupinfo.ATTRIBUTE4,
1655 X_ENABLED_FLAG => 'N',
1656 X_START_DATE_ACTIVE => lookupinfo.START_DATE_ACTIVE,
1657 X_END_DATE_ACTIVE => lookupinfo.END_DATE_ACTIVE,
1658 X_TERRITORY_CODE => lookupinfo.TERRITORY_CODE,
1659 X_ATTRIBUTE5 => lookupinfo.ATTRIBUTE5,
1660 X_ATTRIBUTE6 => lookupinfo.ATTRIBUTE6,
1661 X_ATTRIBUTE7 => lookupinfo.ATTRIBUTE7,
1662 X_ATTRIBUTE8 => lookupinfo.ATTRIBUTE8,
1663 X_ATTRIBUTE9 => lookupinfo.ATTRIBUTE9,
1664 X_ATTRIBUTE10 => lookupinfo.ATTRIBUTE10,
1665 X_ATTRIBUTE11 => lookupinfo.ATTRIBUTE11,
1666 X_ATTRIBUTE12 => lookupinfo.ATTRIBUTE12,
1667 X_ATTRIBUTE13 => lookupinfo.ATTRIBUTE13,
1668 X_ATTRIBUTE14 => lookupinfo.ATTRIBUTE14,
1669 X_ATTRIBUTE15 => lookupinfo.ATTRIBUTE15,
1670 X_MEANING => lookupinfo.MEANING,
1671 X_DESCRIPTION => lookupinfo.DESCRIPTION,
1672 X_LAST_UPDATE_DATE => sysdate,
1673 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1674 X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID
1675 );
1676 END IF;
1677 FETCH C_get_carrier_services INTO l_carrier_service_id;
1678 END LOOP;
1679 CLOSE C_get_carrier_services;
1680
1681 -- Update to Deactivate All Carrier Services.
1682 UPDATE WSH_CARRIER_SERVICES
1683 SET ENABLED_FLAG = 'N'
1684 WHERE carrier_id= p_carrier_id;
1685
1686 -- Deactivation of Carrier Services
1687 IF l_debug_on THEN
1688 WSH_DEBUG_SV.log(l_module_name,'De-Activating Carrier Sites of',p_carrier_id);
1689 END IF;
1690
1691 OPEN C_get_carrier_sites(p_carrier_id);
1692 FETCH C_get_carrier_sites INTO l_carrier_site_id;
1693 -- Looping for each carrier site ID
1694
1695 WHILE C_get_carrier_sites%FOUND
1696 LOOP
1697 IF l_debug_on THEN
1698 WSH_DEBUG_SV.log(l_module_name,'l_carrier_site_id',l_carrier_site_id);
1699 END IF;
1700 l_site_rec.party_site_id := l_carrier_site_id;
1701 l_site_rec.status := 'I';
1702
1703 OPEN Get_Site_Object_Number(l_carrier_site_id);
1704 FETCH Get_Site_Object_Number INTO l_site_object_number;
1705 CLOSE Get_Site_Object_Number;
1706
1707 l_position := 40;
1708 l_procedure := 'Unassigning Org from Carrier Sites';
1709 -- Unassigning Org from Carrier Sites
1710 UPDATE WSH_ORG_CARRIER_SITES
1711 SET ENABLED_FLAG = 'N'
1712 WHERE CARRIER_SITE_ID = l_carrier_site_id;
1713
1714 l_position := 50;
1715 l_procedure := 'Deactivation of Carrier Sites';
1716
1717 -- Deactivating Party Site.
1718 HZ_PARTY_SITE_V2PUB.Update_Party_Site
1719 (
1720 p_init_msg_list => FND_API.G_TRUE,
1721 p_party_site_rec => l_site_rec,
1722 p_object_version_number => l_site_object_number,
1723 x_return_status => l_return_status,
1724 x_msg_count => l_msg_count,
1725 x_msg_data => l_msg_data
1726 );
1727
1728 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1729 x_return_status := l_return_status;
1730 RAISE HZ_FAIL_EXCEPTION;
1731 END IF;
1732 FETCH C_get_carrier_sites INTO l_carrier_site_id;
1733 END LOOP;
1734 CLOSE C_get_carrier_sites;
1735
1736 -- Unassign Vehicle Types
1737 UPDATE WSH_CARRIER_VEHICLE_TYPES
1738 SET ASSIGNED_FLAG = 'N'
1739 WHERE CARRIER_ID = p_carrier_id;
1740
1741 UPDATE ORG_FREIGHT_TL
1742 SET DISABLE_DATE = SYSDATE
1743 WHERE party_id=p_carrier_id and DISABLE_DATE is NULL;
1744 --
1745 IF l_debug_on THEN
1746 WSH_DEBUG_SV.pop(l_module_name);
1747 END IF;
1748 --
1749 EXCEPTION
1750 WHEN UNASSIGNALL_FAIL_EXCEPTION THEN
1751
1752 x_exception_msg := 'EXCEPTION : UNASSIGNALL_FAIL_EXCEPTION' || l_exception_msg;
1753 x_position := l_position;
1754 x_procedure := l_procedure;
1755 x_sqlerr := sqlerrm;
1756 x_sql_code := sqlcode;
1757 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1758 IF l_debug_on THEN
1759 WSH_DEBUG_SV.logmsg(l_module_name,'UNASSIGNALL_FAIL_EXCEPTION error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1760 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:UNASSIGNALL_FAIL_EXCEPTION');
1761 END IF;
1762 --
1763 WHEN HZ_FAIL_EXCEPTION THEN
1764 x_exception_msg := l_msg_data;
1765 x_position := l_position;
1766 x_procedure := l_procedure;
1767 x_sqlerr := sqlerrm;
1768 x_sql_code := sqlcode;
1769 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1770 IF l_debug_on THEN
1771 WSH_DEBUG_SV.logmsg(l_module_name,'HZ_FAIL_EXCEPTION error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1772 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:HZ_FAIL_EXCEPTION');
1773 END IF;
1774 --
1775 WHEN NO_DATA_FOUND THEN
1776 x_exception_msg := 'EXCEPTION : NO_DATA_FOUND';
1777 x_position := l_position;
1778 x_procedure := l_procedure;
1779 x_sqlerr := sqlerrm;
1780 x_sql_code := sqlcode;
1781 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1782 --
1783 IF l_debug_on THEN
1784 WSH_DEBUG_SV.logmsg(l_module_name,' No Data Found Exception occurred. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1785 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
1786 END IF;
1787
1788 WHEN OTHERS THEN
1789 x_exception_msg := 'EXCEPTION : OTHERS';
1790 x_position := l_position;
1791 x_procedure := l_procedure;
1792 x_sqlerr := sqlerrm;
1793 x_sql_code := sqlcode;
1794 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1795 --
1796 IF l_debug_on THEN
1797 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1798 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1799 END IF;
1800 END Carrier_Deactivate;
1801
1802
1803 END WSH_CREATE_CARRIERS_PKG;