[Home] [Help]
PACKAGE BODY: APPS.WSH_CARRIERS_GRP
Source
1 PACKAGE BODY WSH_CARRIERS_GRP AS
2 /* $Header: WSHCAGPB.pls 120.5 2005/10/28 17:47:54 somanaam noship $ */
3 --===================
4 -- CONSTANTS
5 --===================
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_CARRIERS_GRP';
7
8 --========================================================================
9 -- PROCEDURE : Create_Update_Carrier
10 --
11 --
12 -- PARAMETERS: p_api_version known api version error buffer
13 -- p_init_msg_list FND_API.G_TRUE to reset list
14 -- x_return_status return status
15 -- x_msg_count number of messages in the list
16 -- x_msg_data text of messages
17 -- p_action_code action_code ( CREATE,UPDATE and CREATE_UPDATE )
18 -- p_rec_attr_tab Table of attributes for the carrier entity
19 -- p_carrier_name carrier Name
20 -- p_status status
21 -- x_car_out_rec_tab Table of carrier_id
22 -- VERSION : current version 1.0
23 -- initial version 1.0
24 -- COMMENT : Creates or updates a record in wsh_carriers
25 --========================================================================
26 PROCEDURE Create_Update_Carrier
27 ( p_api_version_number IN NUMBER,
28 p_init_msg_list IN VARCHAR2,
29 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
30 p_action_code IN VARCHAR2,
31 p_rec_attr_tab IN Carrier_Rec_Type,
32 p_carrier_name IN VARCHAR2,
33 p_status IN VARCHAR2,
34 x_car_out_rec_tab OUT NOCOPY Carrier_Out_Rec_Type,
35 x_return_status OUT NOCOPY VARCHAR2,
36 x_msg_count OUT NOCOPY NUMBER,
37 x_msg_data OUT NOCOPY VARCHAR2)
38 IS
39
40 CURSOR get_carrier_id(p_carrier_name VARCHAR2,fr_code VARCHAR2) is
41 SELECT carrier_id
42 FROM WSH_CARRIERS car, HZ_PARTIES par
43 WHERE car.carrier_id = par.PARTY_ID
44 AND par.PARTY_NAME = p_carrier_name
45 AND car.FREIGHT_CODE = fr_code;
46
47 CURSOR get_ftcode_carrier_id(fr_code VARCHAR2) is
48 SELECT carrier_id
49 FROM WSH_CARRIERS
50 WHERE FREIGHT_CODE = fr_code;
51
52
53 l_debug_on BOOLEAN;
54
55 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_CARRIER';
56
57 l_index NUMBER;
58 l_num_warnings NUMBER := 0;
59 l_num_errors NUMBER := 0;
60 l_return_status VARCHAR2(1) := 'S';
61 l_exception_msg VARCHAR2(1000);
62 l_position NUMBER;
63 l_action_code VARCHAR2(200);
64 l_call_procedure VARCHAR2(100);
65 l_sql_code NUMBER;
66 l_sqlerr VARCHAR2(2000);
67
68
69 l_api_version_number CONSTANT NUMBER := 1.0;
70 l_api_name CONSTANT VARCHAR2(30):= 'Create_Update_Carrier';
71 l_msg_count NUMBER;
72 l_msg_data VARCHAR2(32767);
73
74 l_input_param_flag BOOLEAN := TRUE;
75 l_param_name VARCHAR2(100);
76
77 l_Carrier_Info WSH_CREATE_CARRIERS_PKG.CARecType;
78 l_carrier_id NUMBER := 0;
79 l_rowid VARCHAR2(4000);
80
81 --- Bug 3392826 Start: Validation
82
83 -- Cursors for Carrier Validation
84
85 CURSOR Get_SCAC_Code_Create(p_scac_code VARCHAR2) IS
86 SELECT carrier_id
87 FROM wsh_carriers
88 WHERE SCAC_CODE = p_scac_code;
89
90 CURSOR Get_SCAC_Code_Update(p_carrier_id NUMBER, p_scac_code VARCHAR2) IS
91 SELECT carrier_id
92 FROM wsh_carriers
93 WHERE SCAC_CODE = p_scac_code
94 AND carrier_id <> p_carrier_id;
95
96
97 CURSOR Get_Freight_Code_Create(p_freight_code VARCHAR2) IS
98 SELECT carrier_id
99 FROM wsh_carriers
100 WHERE upper(FREIGHT_CODE) = upper(p_freight_code);
101
102 CURSOR Get_Freight_Code_Update(p_carrier_id NUMBER, p_freight_code VARCHAR2) IS
103 SELECT carrier_id
104 FROM wsh_carriers
105 WHERE upper(FREIGHT_CODE) = upper(p_freight_code)
106 AND carrier_id <> p_carrier_id;
107
108 CURSOR Get_Carrier_Name_Create(p_carrier_name VARCHAR2) IS
109 SELECT carrier_id
110 FROM wsh_carriers_v wc
111 WHERE wc.active= 'A' AND
112 upper(wc.carrier_name) = upper(p_carrier_name);
113
114 CURSOR Get_Carrier_Name_Update(p_carrier_id NUMBER,p_carrier_name VARCHAR2) IS
115 SELECT carrier_id
116 FROM wsh_carriers_v wc
117 WHERE wc.active = 'A'
118 AND upper(wc.carrier_name) = upper(p_carrier_name)
119 AND wc.carrier_id <> p_carrier_id;
120
121 CURSOR Check_Generic_Carr(p_freight_code VARCHAR2) IS
122 SELECT carrier_name
123 FROM wsh_carriers_v
124 WHERE nvl(generic_flag, 'N') = 'Y' AND
125 freight_code <> nvl(p_freight_code, ' ') AND
126 active = 'A';
127
128 l_carrier_name VARCHAR2(360) := null;
129 l_car_id NUMBER;
130 l_count NUMBER;
131 l_supplier_id NUMBER;
132
133 --- Bug 3392826 End
134
135 BEGIN
136
137 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
138 IF l_debug_on IS NULL
139 THEN
140 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
141 END IF;
142 IF NOT FND_API.Compatible_API_Call
143 ( l_api_version_number,
144 p_api_version_number,
145 l_api_name,
146 G_PKG_NAME
147 )
148 THEN
149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
150 END IF;
151
152 IF FND_API.to_Boolean(p_init_msg_list) THEN
153 FND_MSG_PUB.initialize;
154 END IF;
155
156 IF l_debug_on THEN
157 wsh_debug_sv.push (l_module_name);
158 wsh_debug_sv.log (l_module_name,'action_code',p_action_code);
159 END IF;
160 IF p_action_code IS NULL THEN
161 l_param_name := 'action_code';
162 l_input_param_flag := FALSE;
163 ELSIF p_carrier_name IS NULL THEN
164 l_param_name := 'p_carrier_name';
165 l_input_param_flag := FALSE;
166 ELSIF p_rec_attr_tab.FREIGHT_CODE IS NULL THEN
167 l_param_name := 'Carrier_Rec_Type.freight_code';
168 l_input_param_flag := FALSE;
169 END IF;
170
171 IF not l_input_param_flag THEN
172 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
173 FND_MESSAGE.SET_TOKEN('FIELD_NAME',l_param_name);
174 x_return_status := wsh_util_core.g_ret_sts_error;
175 wsh_util_core.add_message(x_return_status,l_module_name);
176 RAISE FND_API.G_EXC_ERROR;
177 END IF;
178 --
179 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
180 --
181 OPEN get_carrier_id(p_carrier_name,p_rec_attr_tab.FREIGHT_CODE);
182 FETCH get_carrier_id INTO l_carrier_id;
183
184 IF get_carrier_id%NOTFOUND THEN
185
186 l_carrier_id :=0;
187 OPEN get_ftcode_carrier_id(p_rec_attr_tab.FREIGHT_CODE);
188
189 FETCH get_ftcode_carrier_id INTO l_carrier_id;
190 IF get_ftcode_carrier_id%NOTFOUND THEN
191 l_carrier_id := 0;
192 END IF;
193
194 close get_ftcode_carrier_id;
195 END IF;
196 close get_carrier_id;
197
198 IF p_action_code = 'CREATE_UPDATE' THEN
199 IF l_carrier_id = 0 THEN
200 l_action_code := 'CREATE';
201 ELSE
202 l_action_code := 'UPDATE';
203 END IF;
204 END IF;
205
206
207 -- DFF Assignment
208 l_Carrier_Info.ATTRIBUTE_CATEGORY := p_rec_attr_tab.ATTRIBUTE_CATEGORY;
209 l_Carrier_Info.ATTRIBUTE1 := p_rec_attr_tab.ATTRIBUTE1;
210 l_Carrier_Info.ATTRIBUTE2 := p_rec_attr_tab.ATTRIBUTE2;
211 l_Carrier_Info.ATTRIBUTE3 := p_rec_attr_tab.ATTRIBUTE3;
212 l_Carrier_Info.ATTRIBUTE4 := p_rec_attr_tab.ATTRIBUTE4;
213 l_Carrier_Info.ATTRIBUTE5 := p_rec_attr_tab.ATTRIBUTE5;
214 l_Carrier_Info.ATTRIBUTE6 := p_rec_attr_tab.ATTRIBUTE6;
215 l_Carrier_Info.ATTRIBUTE7 := p_rec_attr_tab.ATTRIBUTE7;
216 l_Carrier_Info.ATTRIBUTE8 := p_rec_attr_tab.ATTRIBUTE8;
217 l_Carrier_Info.ATTRIBUTE9 := p_rec_attr_tab.ATTRIBUTE9;
218 l_Carrier_Info.ATTRIBUTE10 := p_rec_attr_tab.ATTRIBUTE10;
219 l_Carrier_Info.ATTRIBUTE11 := p_rec_attr_tab.ATTRIBUTE11;
220 l_Carrier_Info.ATTRIBUTE12 := p_rec_attr_tab.ATTRIBUTE12;
221 l_Carrier_Info.ATTRIBUTE13 := p_rec_attr_tab.ATTRIBUTE13;
222 l_Carrier_Info.ATTRIBUTE14 := p_rec_attr_tab.ATTRIBUTE14;
223 l_Carrier_Info.ATTRIBUTE15 := p_rec_attr_tab.ATTRIBUTE15;
224
225 -- Pack J
226 l_Carrier_Info.CARRIER_NAME := p_carrier_name;
227 l_Carrier_Info.FREIGHT_CODE := p_rec_attr_tab.FREIGHT_CODE;
228 l_Carrier_Info.STATUS := p_status;
229 l_Carrier_Info.SCAC_CODE := p_rec_attr_tab.SCAC_CODE;
230 l_Carrier_Info.MANIFESTING_ENABLED := p_rec_attr_tab.MANIFESTING_ENABLED_FLAG;
231 l_Carrier_Info.CURRENCY_CODE := p_rec_attr_tab.CURRENCY_CODE;
232
233 l_Carrier_info.GENERIC_FLAG := p_rec_attr_tab.GENERIC_FLAG;
234 l_Carrier_info.WEIGHT_UOM := p_rec_attr_tab.WEIGHT_UOM;
235 l_Carrier_info.TIME_UOM := p_rec_attr_tab.TIME_UOM;
236 l_Carrier_info.DIMENSION_UOM := p_rec_attr_tab.DIMENSION_UOM;
237 l_Carrier_info.VOLUME_UOM := p_rec_attr_tab.VOLUME_UOM;
238 l_Carrier_info.DISTANCE_UOM := p_rec_attr_tab.DISTANCE_UOM;
239 l_Carrier_info.SUPPLIER_ID := p_rec_attr_tab.SUPPLIER_ID;
240 l_Carrier_info.SUPPLIER_SITE_ID := p_rec_attr_tab.SUPPLIER_SITE_ID;
241 l_Carrier_info.FREIGHT_BILL_AUTO_APPROVAL := p_rec_attr_tab.FREIGHT_BILL_AUTO_APPROVAL;
242 l_Carrier_info.FREIGHT_AUDIT_LINE_LEVEL := p_rec_attr_tab.FREIGHT_AUDIT_LINE_LEVEL;
243 l_Carrier_info.DISTANCE_CALCULATION_METHOD := p_rec_attr_tab.DISTANCE_CALCULATION_METHOD;
244 l_Carrier_info.CM_RATE_VARIANT := p_rec_attr_tab.CM_RATE_VARIANT;
245 l_Carrier_info.UNIT_RATE_BASIS := p_rec_attr_tab.UNIT_RATE_BASIS;
246 l_Carrier_info.MAX_OUT_OF_ROUTE := p_rec_attr_tab.MAX_OUT_OF_ROUTE;
247 l_Carrier_info.CM_FIRST_LOAD_DISCOUNT := p_rec_attr_tab.CM_FIRST_LOAD_DISCOUNT;
248 l_Carrier_info.CM_FREE_DH_MILEAGE := p_rec_attr_tab.CM_FREE_DH_MILEAGE;
249 l_Carrier_info.MIN_CM_TIME := p_rec_attr_tab.MIN_CM_TIME;
250 l_Carrier_info.MIN_CM_DISTANCE := p_rec_attr_tab.MIN_CM_DISTANCE;
251 l_Carrier_info.ALLOW_INTERSPERSE_LOAD := p_rec_attr_tab.ALLOW_INTERSPERSE_LOAD;
252 l_Carrier_info.MAX_NUM_STOPS_PERMITTED := p_rec_attr_tab.MAX_NUM_STOPS_PERMITTED;
253 l_Carrier_info.MAX_TOTAL_DISTANCE := p_rec_attr_tab.MAX_TOTAL_DISTANCE;
254 l_Carrier_info.MAX_TOTAL_TIME := p_rec_attr_tab.MAX_TOTAL_TIME;
255 l_Carrier_info.MAX_CM_TIME := p_rec_attr_tab.MAX_CM_TIME;
256 l_Carrier_info.MAX_CM_DISTANCE := p_rec_attr_tab.MAX_CM_DISTANCE;
257 l_Carrier_info.MAX_CM_DH_DISTANCE := p_rec_attr_tab.MAX_CM_DH_DISTANCE;
258 l_Carrier_info.MAX_LAYOVER_TIME := p_rec_attr_tab.MAX_LAYOVER_TIME;
259 l_Carrier_info.MIN_LAYOVER_TIME := p_rec_attr_tab.MIN_LAYOVER_TIME;
260 l_Carrier_info.MAX_TOTAL_DISTANCE_IN_24HR := p_rec_attr_tab.MAX_TOTAL_DISTANCE_IN_24HR;
261 l_Carrier_info.MAX_DRIVING_TIME_IN_24HR := p_rec_attr_tab.MAX_DRIVING_TIME_IN_24HR;
262 l_Carrier_info.MAX_DUTY_TIME_IN_24HR := p_rec_attr_tab.MAX_DUTY_TIME_IN_24HR;
263 l_Carrier_info.MIN_SIZE_LENGTH := p_rec_attr_tab.MIN_SIZE_LENGTH;
264 l_Carrier_info.MAX_SIZE_LENGTH := p_rec_attr_tab.MAX_SIZE_LENGTH;
265 l_Carrier_info.MIN_SIZE_HEIGHT := p_rec_attr_tab.MIN_SIZE_HEIGHT;
266 l_Carrier_info.MAX_SIZE_HEIGHT := p_rec_attr_tab.MAX_SIZE_HEIGHT;
267 l_Carrier_info.MIN_SIZE_WIDTH := p_rec_attr_tab.MIN_SIZE_WIDTH;
268 l_Carrier_info.MAX_SIZE_WIDTH := p_rec_attr_tab.MAX_SIZE_WIDTH;
269 l_Carrier_info.ALLOW_INTERSPERSE_LOAD := p_rec_attr_tab.ALLOW_INTERSPERSE_LOAD;
270 l_Carrier_info.MAX_CM_DH_TIME := p_rec_attr_tab.MAX_CM_DH_TIME;
271 l_Carrier_info.ORIGIN_DSTN_SURCHARGE_LEVEL := p_rec_attr_tab.ORIGIN_DSTN_SURCHARGE_LEVEL;
272
273 IF p_action_code = 'CREATE' OR l_action_code = 'CREATE' THEN
274
275 --- Bug 3392826 Start: Validation for carriers
276
277 -- SCAC code
278 OPEN Get_SCAC_Code_Create(l_Carrier_Info.SCAC_CODE);
279 FETCH Get_SCAC_Code_Create INTO l_car_id;
280 IF Get_SCAC_Code_Create%FOUND THEN
281 FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_SCAC_CODE_EXISTS');
282 x_return_status := wsh_util_core.g_ret_sts_error;
283 wsh_util_core.add_message(x_return_status,l_module_name);
284 RAISE FND_API.G_EXC_ERROR;
285 END IF;
286 CLOSE Get_SCAC_Code_Create;
287
288 -- freight code
289 OPEN Get_Freight_Code_Create(l_Carrier_Info.FREIGHT_CODE);
290 FETCH Get_Freight_Code_Create INTO l_car_id;
291 IF Get_Freight_Code_Create%FOUND THEN
292 FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_FRG_CODE_EXISTS');
293 x_return_status := wsh_util_core.g_ret_sts_error;
294 wsh_util_core.add_message(x_return_status,l_module_name);
295 RAISE FND_API.G_EXC_ERROR;
296 END IF;
297 CLOSE Get_Freight_Code_Create;
298
299 -- Carrier name
300 OPEN Get_Carrier_Name_Create(l_Carrier_Info.CARRIER_NAME);
301 FETCH Get_Carrier_Name_Create INTO l_car_id;
302
303 IF Get_Carrier_Name_Create%FOUND THEN
304 FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_NAME_EXISTS');
305 x_return_status := wsh_util_core.g_ret_sts_error;
306 wsh_util_core.add_message(x_return_status,l_module_name);
307 RAISE FND_API.G_EXC_ERROR;
308 END IF;
309 CLOSE Get_Carrier_Name_Create;
310
311
312 -- Carrier already designated as Generic Carrier
313 IF l_Carrier_info.GENERIC_FLAG = 'Y' THEN
314
315 OPEN Check_Generic_Carr(l_Carrier_Info.FREIGHT_CODE);
316 FETCH Check_Generic_Carr INTO l_carrier_name;
317 CLOSE Check_Generic_Carr;
318
319 IF l_carrier_name IS NOT NULL THEN
320 FND_MESSAGE.SET_NAME('WSH','WSH_GENERIC_CARRIER_EXISTS');
321 FND_MESSAGE.SET_TOKEN('CARRIER_NAME', l_carrier_name);
322 x_return_status := wsh_util_core.g_ret_sts_error;
323 wsh_util_core.add_message(x_return_status,l_module_name);
324 RAISE FND_API.G_EXC_ERROR;
325 END IF;
326
327 END IF;
328 --- Bug 3392826 End
329
330 WSH_CREATE_CARRIERS_PKG.CREATE_CARRIERINFO
331 (
332 P_CARRIER_INFO => l_Carrier_Info,
333 P_COMMIT => p_commit,
334 X_ROWID => l_rowid,
335 X_CARRIER_PARTY_ID => l_carrier_id,
339 X_PROCEDURE => l_call_procedure,
336 X_RETURN_STATUS => l_return_status,
337 X_EXCEPTION_MSG => l_exception_msg,
338 X_POSITION => l_position,
340 X_SQLERR => l_sqlerr,
341 X_SQL_CODE => l_sql_code
342 );
343
344 WSH_UTIL_CORE.api_post_call
345 (
346 p_return_status =>l_return_status,
347 x_num_warnings =>l_num_warnings,
348 x_num_errors =>l_num_errors
349 );
350 IF l_debug_on THEN
351 wsh_debug_sv.log(l_module_name,'return status WSH_CREATE_CARRIERS_PKG.CREATE_CARRIERINFO',l_return_status);
352 END IF;
353
354 ELSIF p_action_code = 'UPDATE' OR l_action_code = 'UPDATE' THEN
355
356 -- Pack J
357 l_Carrier_info.CARRIER_ID := l_carrier_id;
358
359 --- Bug 3392826 Start: Validation for carriers
360
361 -- SCAC code
362 OPEN Get_SCAC_Code_Update(l_Carrier_Info.carrier_id, l_Carrier_Info.SCAC_CODE);
363 FETCH Get_SCAC_Code_Update INTO l_car_id;
364 IF Get_SCAC_Code_Update%FOUND THEN
365 FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_SCAC_CODE_EXISTS');
366 x_return_status := wsh_util_core.g_ret_sts_error;
367 wsh_util_core.add_message(x_return_status,l_module_name);
368 RAISE FND_API.G_EXC_ERROR;
369 END IF;
370 CLOSE Get_SCAC_Code_Update;
371
372 -- Carrier name
373 OPEN Get_Carrier_Name_Update(l_Carrier_Info.carrier_id, l_Carrier_Info.CARRIER_NAME);
374 FETCH Get_Carrier_Name_Update INTO l_car_id;
375
376 IF Get_Carrier_Name_Update%FOUND THEN
377 FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_NAME_EXISTS');
378 x_return_status := wsh_util_core.g_ret_sts_error;
379 wsh_util_core.add_message(x_return_status,l_module_name);
380 RAISE FND_API.G_EXC_ERROR;
381 END IF;
382 CLOSE Get_Carrier_Name_Update;
383
384
385 -- Carrier already designated as Generic Carrier
386 IF l_Carrier_info.GENERIC_FLAG = 'Y' THEN
387 l_Carrier_info.GENERIC_FLAG := 'N';
388 END IF;
389
390 --- Bug 3392826 End
391
392 -- If carrier is deactivated,
393 -- deactivate Services and Organization Assignments.
394 IF p_status = 'I' THEN
395 WSH_CREATE_CARRIERS_PKG.CARRIER_DEACTIVATE
396 (
397 p_carrier_id => l_carrier_id,
398 X_RETURN_STATUS => l_return_status,
399 X_EXCEPTION_MSG => l_exception_msg,
400 X_POSITION => l_position,
401 X_PROCEDURE => l_call_procedure,
402 X_SQLERR => l_sqlerr,
403 X_SQL_CODE => l_sql_code
404 );
405 END IF;
406
407 WSH_UTIL_CORE.api_post_call
408 (
409 p_return_status =>l_return_status,
410 x_num_warnings =>l_num_warnings,
411 x_num_errors =>l_num_errors
412 );
413
414
415 WSH_CREATE_CARRIERS_PKG.UPDATE_CARRIERINFO
416 (
417 P_CARRIER_INFO => l_Carrier_Info,
418 P_COMMIT => p_commit,
419 X_RETURN_STATUS => l_return_status,
420 X_EXCEPTION_MSG => l_exception_msg,
421 X_POSITION => l_position,
422 X_PROCEDURE => l_call_procedure,
423 X_SQLERR => l_sqlerr,
424 X_SQL_CODE => l_sql_code
425 );
426
427 IF l_debug_on THEN
428 wsh_debug_sv.log(l_module_name,'return status WSH_CREATE_CARRIERS_PKG.UPDATE_CARRIERINFO',l_return_status);
429 END IF;
430
431 END IF;
432 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
433 x_num_warnings =>l_num_warnings,
434 x_num_errors =>l_num_errors
435 );
436 x_car_out_rec_tab.rowid := l_rowid;
437 x_car_out_rec_tab.carrier_id := l_carrier_id;
438 x_return_status := l_return_status;
439 IF FND_API.To_Boolean(p_commit) THEN
440 COMMIT WORK;
441 END IF;
442 EXCEPTION
443 --
444 --
445 WHEN FND_API.G_EXC_ERROR THEN
446 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
447 x_msg_data := l_sqlerr;
448 FND_MSG_PUB.Count_And_Get
449 (
450 p_count => x_msg_count,
451 p_data => x_msg_data,
452 p_encoded => FND_API.G_FALSE
453 );
454
455 IF l_debug_on THEN
456 wsh_debug_sv.log (l_module_name,'l_exception_msg',l_exception_msg);
457 wsh_debug_sv.log (l_module_name,'l_position',l_position);
458 wsh_debug_sv.log (l_module_name,'l_procedure',l_call_procedure);
459 wsh_debug_sv.log (l_module_name,'l_sqlerr',l_sqlerr);
460 wsh_debug_sv.log (l_module_name,'l_sql_code',l_sql_code);
461 END IF;
465 FND_MSG_PUB.Count_And_Get
462 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
463 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
464 x_msg_data := l_sqlerr;
466 (
467 p_count => x_msg_count,
468 p_data => x_msg_data,
469 p_encoded => FND_API.G_FALSE
470 );
471
472 IF x_msg_data IS NULL THEN
473 x_msg_count := 1;
474 x_msg_data := l_sqlerr;
475 END IF;
476
477 IF l_debug_on THEN
478 wsh_debug_sv.log (l_module_name,'l_exception_msg',l_exception_msg);
479 wsh_debug_sv.log (l_module_name,'l_position',l_position);
480 wsh_debug_sv.log (l_module_name,'l_procedure',l_call_procedure);
481 wsh_debug_sv.log (l_module_name,'l_sqlerr',l_sqlerr);
482 wsh_debug_sv.log (l_module_name,'l_sql_code',l_sql_code);
483 END IF;
484
485 WHEN OTHERS THEN
486
487 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
488 x_msg_data := l_sqlerr;
489 FND_MSG_PUB.Count_And_Get
490 (
491 p_count => x_msg_count,
492 p_data => x_msg_data,
493 p_encoded => FND_API.G_FALSE
494 );
495
496 IF x_msg_data IS NULL THEN
497 x_msg_count := 1;
498 x_msg_data := l_sqlerr;
499 END IF;
500
501 IF l_debug_on THEN
502 wsh_debug_sv.log (l_module_name,'l_exception_msg',l_exception_msg);
503 wsh_debug_sv.log (l_module_name,'l_position',l_position);
504 wsh_debug_sv.log (l_module_name,'l_procedure',l_call_procedure);
505 wsh_debug_sv.log (l_module_name,'l_sqlerr',l_sqlerr);
506 wsh_debug_sv.log (l_module_name,'l_sql_code',l_sql_code);
507 END IF;
508
509 END Create_Update_Carrier;
510
511 --========================================================================
512 -- PROCEDURE : Create_Update_Carrier_Service
513 --
514 --
515 -- PARAMETERS: p_api_version known api version error buffer
516 -- p_init_msg_list FND_API.G_TRUE to reset list
517 -- x_return_status return status
518 -- x_msg_count number of messages in the list
519 -- x_msg_data text of messages
520 -- p_action_code action_code ( CREATE,UPDATE and CREATE_UPDATE )
521 -- p_rec_attr_tab Table of attributes for the carrier service entity
522 -- x_car_ser_out_rec_tab Table of carrier_service_id, and ship_method_code.
523 -- VERSION : current version 1.0
524 -- initial version 1.0
525 -- COMMENT : Creates or updates a record in wsh_carrier_services
526 --========================================================================
527 PROCEDURE Create_Update_Carrier_Service
528 ( p_api_version_number IN NUMBER,
529 p_init_msg_list IN VARCHAR2,
530 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
531 p_action_code IN VARCHAR2,
532 p_rec_attr_tab IN Carrier_Service_Rec_Type,
533 x_car_ser_out_rec_tab OUT NOCOPY Carrier_Ser_Out_Rec_Type,
534 x_return_status OUT NOCOPY VARCHAR2,
535 x_msg_count OUT NOCOPY NUMBER,
536 x_msg_data OUT NOCOPY VARCHAR2)
537 IS
538 l_debug_on BOOLEAN;
539
540 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_CARRIER_SERVICE';
541
542 l_index NUMBER;
543 l_num_warnings NUMBER := 0;
544 l_num_errors NUMBER := 0;
545 l_return_status VARCHAR2(1) := 'S';
546 l_exception_msg VARCHAR2(1000);
547 l_position NUMBER;
548 l_action_code VARCHAR2(200);
549 l_call_procedure VARCHAR2(100);
550 l_sql_code NUMBER;
551 l_sqlerr VARCHAR2(2000);
552
553
554 l_api_version_number CONSTANT NUMBER := 1.0;
555 l_api_name CONSTANT VARCHAR2(30):= 'Create_Update_Carrier_Service';
556 l_msg_count NUMBER;
557 l_msg_data VARCHAR2(32767);
558
559 l_input_param_flag BOOLEAN := TRUE;
560 l_param_name VARCHAR2(100);
561
562 l_carrier_ser_tab WSH_CARRIER_SERVICES_PKG.CSRecType;
563 l_carrier_service_id NUMBER;
564 l_rowid VARCHAR2(4000) := NULL;
565
566 l_service_level_code VARCHAR2(4000);
567 l_sl_time_uom_desc VARCHAR2(4000);
568 l_mode_of_trans_code VARCHAR2(4000);
569 l_freight_code VARCHAR2(4000);
570 l_ship_method_code VARCHAR2(4000);
571 l_ship_method_meaning VARCHAR2(4000);
572
573 CURSOR get_carrier_ser_id(p_carrier_id INTEGER,p_service_level_code VARCHAR2, p_mode_of_transport VARCHAR2) is
574 SELECT carrier_id
575 FROM WSH_CARRIER_SERVICES
576 WHERE carrier_id = p_carrier_id
577 AND service_level = p_service_level_code
578 AND MODE_OF_TRANSPORT = p_mode_of_transport;
579
580
581 CURSOR get_freight_code(p_carrier_id INTEGER) is
582 SELECT freight_code
583 FROM WSH_CARRIERS
584 WHERE carrier_id = p_carrier_id;
585
586
587
591 WHERE carrier_id= p_carrier_id
588 CURSOR get_rowid_shpcode(p_carrier_id INTEGER, p_service_level VARCHAR2, p_mode_of_transport VARCHAR2) is
589 SELECT rowid,ship_method_code
590 FROM wsh_carrier_services
592 AND service_level = p_service_level
593 AND mode_of_transport = p_mode_of_transport;
594
595 --- Bug 3392826 Start: Validation for Services
596 -- Cursors for Services Validation
597
598 CURSOR Check_Duplicate(p_mode_of_transport VARCHAR2, p_service_level VARCHAR2, p_carrier_id NUMBER) IS
599 SELECT count(*)
600 FROM wsh_carrier_services
601 WHERE mode_of_transport = p_mode_of_transport AND
602 service_level = p_service_level AND
603 carrier_id = p_carrier_id;
604
605 CURSOR Check_Duplicate_SMM_Create(p_ship_method_meaning VARCHAR2) IS
606 SELECT rowid
607 FROM wsh_carrier_services
608 WHERE SHIP_METHOD_MEANING = p_ship_method_meaning;
609
610 CURSOR Check_Duplicate_SMM_Update(p_rowid VARCHAR2, p_ship_method_meaning VARCHAR2) IS
611 SELECT rowid
612 FROM wsh_carrier_services
613 WHERE SHIP_METHOD_MEANING = p_ship_method_meaning
614 AND rowid <> p_rowid;
615
616 l_count NUMBER := 0;
617 l_serv_rowid VARCHAR2(4000) := NULL;
618
619 --- Bug 3392826 End
620
621 BEGIN
622
623 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
624
625
626 IF l_debug_on IS NULL
627 THEN
628 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
629 END IF;
630 IF NOT FND_API.Compatible_API_Call
631 (
632 l_api_version_number,
633 p_api_version_number,
634 l_api_name,
635 G_PKG_NAME
636 )
637 THEN
638 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
639 END IF;
640
641 IF FND_API.to_Boolean(p_init_msg_list) THEN
642 FND_MSG_PUB.initialize;
643 END IF;
644
645 IF l_debug_on THEN
646 wsh_debug_sv.push (l_module_name);
647 wsh_debug_sv.log (l_module_name,'action_code',p_action_code);
648 END IF;
649 IF p_action_code IS NULL THEN
650 l_param_name := 'action_code';
651 l_input_param_flag := FALSE;
652 ELSIF p_rec_attr_tab.carrier_id IS NULL THEN
653 l_param_name := 'carrier_id';
654 l_input_param_flag := FALSE;
655 ELSIF p_rec_attr_tab.SERVICE_LEVEL IS NULL THEN
656 l_param_name := 'SERVICE_LEVEL(CODE)';
657 l_input_param_flag := FALSE;
658 ELSIF p_rec_attr_tab.MODE_OF_TRANSPORT IS NULL THEN
659 l_param_name := 'MODE_OF_TRANSPORT(CODE)';
660 l_input_param_flag := FALSE;
661 END IF;
662
663 IF not l_input_param_flag THEN
664 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
665 FND_MESSAGE.SET_TOKEN('FIELD_NAME',l_param_name);
666 x_return_status := wsh_util_core.g_ret_sts_error;
667 wsh_util_core.add_message(x_return_status,l_module_name);
668 RAISE FND_API.G_EXC_ERROR;
669 END IF;
670 --
671 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
672 --
673
674 IF p_action_code = 'CREATE_UPDATE' THEN
675 OPEN get_carrier_ser_id(p_rec_attr_tab.carrier_id,p_rec_attr_tab.SERVICE_LEVEL,p_rec_attr_tab.MODE_OF_TRANSPORT);
676 FETCH get_carrier_ser_id INTO l_carrier_service_id;
677 IF get_carrier_ser_id%NOTFOUND THEN
678 l_action_code := 'CREATE';
679 ELSE
680 l_action_code := 'UPDATE';
681
682 END IF;
683 close get_carrier_ser_id;
684 END IF;
685
686 OPEN get_freight_code(p_rec_attr_tab.carrier_id);
687 FETCH get_freight_code INTO l_freight_code;
688 close get_freight_code;
689 Get_Meanings
690 (
691 x_service_level => l_service_level_code,
692 service_level_code => p_rec_attr_tab.SERVICE_LEVEL,
693 x_mode_of_transport => l_mode_of_trans_code,
694 mode_of_trans_code => p_rec_attr_tab.MODE_OF_TRANSPORT,
695 sl_time_uom => p_rec_attr_tab.SL_TIME_UOM,
696 x_sl_time_uom_desc => l_sl_time_uom_desc
697 );
698 l_ship_method_meaning := p_rec_attr_tab.SHIP_METHOD_MEANING;
699 IF l_ship_method_meaning IS NULL THEN
700 l_ship_method_meaning := l_freight_code || '-' || p_rec_attr_tab.MODE_OF_TRANSPORT || '-' || substr(l_service_level_code,1,48);
701 END IF;
702
703 l_carrier_ser_tab.Carrier_Service_id := p_rec_attr_tab.CARRIER_SERVICE_ID;
704 l_carrier_ser_tab.Carrier_Id := p_rec_attr_tab.CARRIER_ID;
705 l_carrier_ser_tab.mode_of_transport := p_rec_attr_tab.MODE_OF_TRANSPORT;
706 l_carrier_ser_tab.Enabled_Flag := p_rec_attr_tab.ENABLED_FLAG;
707 l_carrier_ser_tab.sl_time_uom := p_rec_attr_tab.SL_TIME_UOM;
708 l_carrier_ser_tab.service_level := p_rec_attr_tab.SERVICE_LEVEL;
709 l_carrier_ser_tab.min_sl_time := p_rec_attr_tab.MIN_SL_TIME;
710 l_carrier_ser_tab.max_sl_time := p_rec_attr_tab.MAX_SL_TIME;
711 l_carrier_ser_tab.Web_Enabled := p_rec_attr_tab.WEB_ENABLED;
712 l_carrier_ser_tab.ship_method_meaning:= l_ship_method_meaning;
713 l_carrier_ser_tab.ATTRIBUTE_CATEGORY := p_rec_attr_tab.ATTRIBUTE_CATEGORY;
717 l_carrier_ser_tab.ATTRIBUTE4 := p_rec_attr_tab.ATTRIBUTE4;
714 l_carrier_ser_tab.ATTRIBUTE1 := p_rec_attr_tab.ATTRIBUTE1;
715 l_carrier_ser_tab.ATTRIBUTE2 := p_rec_attr_tab.ATTRIBUTE2;
716 l_carrier_ser_tab.ATTRIBUTE3 := p_rec_attr_tab.ATTRIBUTE3;
718 l_carrier_ser_tab.ATTRIBUTE5 := p_rec_attr_tab.ATTRIBUTE5;
719 l_carrier_ser_tab.ATTRIBUTE6 := p_rec_attr_tab.ATTRIBUTE6;
720 l_carrier_ser_tab.ATTRIBUTE7 := p_rec_attr_tab.ATTRIBUTE7;
721 l_carrier_ser_tab.ATTRIBUTE8 := p_rec_attr_tab.ATTRIBUTE8;
722 l_carrier_ser_tab.ATTRIBUTE9 := p_rec_attr_tab.ATTRIBUTE9;
723 l_carrier_ser_tab.ATTRIBUTE10 := p_rec_attr_tab.ATTRIBUTE10;
724 l_carrier_ser_tab.ATTRIBUTE11 := p_rec_attr_tab.ATTRIBUTE11;
725 l_carrier_ser_tab.ATTRIBUTE12 := p_rec_attr_tab.ATTRIBUTE12;
726 l_carrier_ser_tab.ATTRIBUTE13 := p_rec_attr_tab.ATTRIBUTE13;
727 l_carrier_ser_tab.ATTRIBUTE14 := p_rec_attr_tab.ATTRIBUTE14;
728 l_carrier_ser_tab.ATTRIBUTE15 := p_rec_attr_tab.ATTRIBUTE15;
729 l_carrier_ser_tab.Creation_Date := SYSDATE;
730 l_carrier_ser_tab.Created_By := fnd_global.user_id;
731 l_carrier_ser_tab.Last_Update_Date := SYSDATE;
732 l_carrier_ser_tab.Last_Updated_By := fnd_global.user_id;
733
734 -- Pack J
735 l_carrier_ser_tab.MAX_NUM_STOPS_PERMITTED := p_rec_attr_tab.MAX_NUM_STOPS_PERMITTED;
736 l_carrier_ser_tab.MAX_TOTAL_DISTANCE := p_rec_attr_tab.MAX_TOTAL_DISTANCE;
737 l_carrier_ser_tab.MAX_TOTAL_TIME := p_rec_attr_tab.MAX_TOTAL_TIME;
738 l_carrier_ser_tab.ALLOW_INTERSPERSE_LOAD := p_rec_attr_tab.ALLOW_INTERSPERSE_LOAD;
739 l_carrier_ser_tab.MAX_LAYOVER_TIME := p_rec_attr_tab.MAX_LAYOVER_TIME;
740 l_carrier_ser_tab.MIN_LAYOVER_TIME := p_rec_attr_tab.MIN_LAYOVER_TIME;
741 l_carrier_ser_tab.MAX_TOTAL_DISTANCE_IN_24HR := p_rec_attr_tab.MAX_TOTAL_DISTANCE_IN_24HR;
742 l_carrier_ser_tab.MAX_DRIVING_TIME_IN_24HR := p_rec_attr_tab.MAX_DRIVING_TIME_IN_24HR;
743 l_carrier_ser_tab.MAX_DUTY_TIME_IN_24HR := p_rec_attr_tab.MAX_DUTY_TIME_IN_24HR;
744 l_carrier_ser_tab.MAX_CM_DISTANCE := p_rec_attr_tab.MAX_CM_DISTANCE;
745 l_carrier_ser_tab.MAX_CM_TIME := p_rec_attr_tab.MAX_CM_TIME;
746 l_carrier_ser_tab.MAX_CM_DH_DISTANCE := p_rec_attr_tab.MAX_CM_DH_DISTANCE;
747 l_carrier_ser_tab.MAX_SIZE_WIDTH := p_rec_attr_tab.MAX_SIZE_WIDTH;
748 l_carrier_ser_tab.MAX_SIZE_HEIGHT := p_rec_attr_tab.MAX_SIZE_HEIGHT;
749 l_carrier_ser_tab.MAX_SIZE_LENGTH := p_rec_attr_tab.MAX_SIZE_LENGTH;
750 l_carrier_ser_tab.MIN_SIZE_WIDTH := p_rec_attr_tab.MIN_SIZE_WIDTH;
751 l_carrier_ser_tab.MIN_SIZE_HEIGHT := p_rec_attr_tab.MIN_SIZE_HEIGHT;
752 l_carrier_ser_tab.MIN_SIZE_LENGTH := p_rec_attr_tab.MIN_SIZE_LENGTH;
753 l_carrier_ser_tab.MAX_OUT_OF_ROUTE := p_rec_attr_tab.MAX_OUT_OF_ROUTE;
754 l_carrier_ser_tab.CM_FREE_DH_MILEAGE := p_rec_attr_tab.CM_FREE_DH_MILEAGE;
755 l_carrier_ser_tab.MIN_CM_DISTANCE := p_rec_attr_tab.MIN_CM_DISTANCE;
756 l_carrier_ser_tab.CM_FIRST_LOAD_DISCOUNT := p_rec_attr_tab.CM_FIRST_LOAD_DISCOUNT;
757 l_carrier_ser_tab.MIN_CM_TIME := p_rec_attr_tab.MIN_CM_TIME;
758 l_carrier_ser_tab.UNIT_RATE_BASIS := p_rec_attr_tab.UNIT_RATE_BASIS;
759 l_carrier_ser_tab.CM_RATE_VARIANT := p_rec_attr_tab.CM_RATE_VARIANT;
760 l_carrier_ser_tab.DISTANCE_CALCULATION_METHOD := p_rec_attr_tab.DISTANCE_CALCULATION_METHOD;
761 l_carrier_ser_tab.ALLOW_INTERSPERSE_LOAD := p_rec_attr_tab.ALLOW_INTERSPERSE_LOAD;
762 l_carrier_ser_tab.MAX_CM_DH_TIME := p_rec_attr_tab.MAX_CM_DH_TIME;
763 l_carrier_ser_tab.ORIGIN_DSTN_SURCHARGE_LEVEL := p_rec_attr_tab.ORIGIN_DSTN_SURCHARGE_LEVEL;
764
765 IF p_action_code = 'CREATE' OR l_action_code = 'CREATE' THEN
766 Generate_Ship_Method
767 (
768 service_level_code => p_rec_attr_tab.SERVICE_LEVEL,
769 freight_code => l_freight_code,
770 mode_of_trans_code => l_mode_of_trans_code,
771 ship_method_meaning => l_ship_method_meaning,
772 x_ship_method_code => l_ship_method_code
773 );
774 l_carrier_ser_tab.ship_method_code := l_ship_method_code;
775
776 --- Bug 3392826 Start: Validation for Services
777 -- Record with this combination already exists.
778 IF ((l_carrier_ser_tab.mode_of_transport IS NOT NULL) AND
779 (l_carrier_ser_tab.service_level IS NOT NULL)) THEN
780 OPEN Check_Duplicate(l_carrier_ser_tab.mode_of_transport, l_carrier_ser_tab.service_level, l_carrier_ser_tab.Carrier_Id);
781 FETCH Check_Duplicate INTO l_count;
782 CLOSE Check_Duplicate;
783
784 IF l_count >0 THEN
785 fnd_message.set_name('WSH','WSH_DUPLICATE_RECORD');
786 x_return_status := wsh_util_core.g_ret_sts_error;
787 wsh_util_core.add_message(x_return_status,l_module_name);
788 RAISE FND_API.G_EXC_ERROR;
789 END IF;
790 END IF;
791
792 --- This Ship Method Meaning is already existing.
793 OPEN Check_Duplicate_SMM_Create(l_carrier_ser_tab.ship_method_meaning);
794 FETCH Check_Duplicate_SMM_Create INTO l_serv_rowid;
795 IF Check_Duplicate_SMM_Create%FOUND THEN
799 RAISE FND_API.G_EXC_ERROR;
796 fnd_message.set_name('WSH','WSH_SHIP_METHOD_EXISTS');
797 x_return_status := wsh_util_core.g_ret_sts_error;
798 wsh_util_core.add_message(x_return_status,l_module_name);
800 END IF;
801 CLOSE Check_Duplicate_SMM_Create;
802 --- Bug 3392826 End: Validation for Services
803
804
805 WSH_CARRIER_SERVICES_PKG.Create_Carrier_Service
806 (
807 p_Carrier_Service_Info => l_carrier_ser_tab,
808 P_COMMIT => p_commit,
809 X_ROWID => l_rowid,
810 X_CARRIER_SERVICE_ID => l_carrier_service_id,
811 X_RETURN_STATUS => l_return_status,
812 X_POSITION => l_position,
813 X_PROCEDURE => l_call_procedure,
814 X_SQLERR => l_sqlerr,
815 X_SQL_CODE => l_sql_code
816 );
817 IF l_debug_on THEN
818 wsh_debug_sv.log(l_module_name,'return status WSH_CREATE_CARRIERS_PKG.Create_Carrier_Service',l_return_status);
819 END IF;
820
821 ELSIF p_action_code = 'UPDATE' OR l_action_code = 'UPDATE' THEN
822
823 OPEN get_rowid_shpcode(p_rec_attr_tab.CARRIER_ID, p_rec_attr_tab.SERVICE_LEVEL, p_rec_attr_tab.MODE_OF_TRANSPORT);
824 fetch get_rowid_shpcode INTO l_rowid,l_ship_method_code;
825 close get_rowid_shpcode;
826
827 --- Bug 3392826 Start: Validation for Services
828 --- This Ship Method is already existing.
829 OPEN Check_Duplicate_SMM_Update(l_rowid, l_carrier_ser_tab.ship_method_meaning);
830 FETCH Check_Duplicate_SMM_Update INTO l_serv_rowid;
831 IF Check_Duplicate_SMM_Update%FOUND THEN
832 fnd_message.set_name('WSH','WSH_SHIP_METHOD_EXISTS');
833 x_return_status := wsh_util_core.g_ret_sts_error;
834 wsh_util_core.add_message(x_return_status,l_module_name);
835 RAISE FND_API.G_EXC_ERROR;
836 END IF;
837 CLOSE Check_Duplicate_SMM_Update;
838 --- Bug 3392826 End: Validation for Services
839
840
841 IF l_rowid IS NOT NULL THEN
842 l_carrier_ser_tab.ship_method_code := l_ship_method_code;
843 WSH_CARRIER_SERVICES_PKG.Update_Carrier_Service
844 (
845 p_Carrier_Service_Info => l_carrier_ser_tab,
846 P_ROWID => l_rowid,
847 P_COMMIT => p_commit,
848 X_RETURN_STATUS => l_return_status,
849 X_POSITION => l_position,
850 X_PROCEDURE => l_call_procedure,
851 X_SQLERR => l_sqlerr,
852 X_SQL_CODE => l_sql_code,
853 X_EXCEPTION_MSG => l_exception_msg
854 );
855 END IF;
856
857 IF l_debug_on THEN
858 wsh_debug_sv.log(l_module_name,'return status WSH_CREATE_CARRIERS_PKG.Update_Carrier_Service',l_return_status);
859 END IF;
860
861 END IF;
862 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
863 x_num_warnings =>l_num_warnings,
864 x_num_errors =>l_num_errors
865 );
866 x_car_ser_out_rec_tab.rowid := l_rowid;
867 x_car_ser_out_rec_tab.carrier_service_id := l_carrier_service_id;
868 x_car_ser_out_rec_tab.ship_method_code := l_ship_method_code;
869 x_return_status := l_return_status;
870
871 IF FND_API.To_Boolean(p_commit) THEN
872 COMMIT WORK;
873 END IF;
874 EXCEPTION
875 --
876 --
877 WHEN FND_API.G_EXC_ERROR THEN
878 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
879 x_msg_data := l_sqlerr;
880 FND_MSG_PUB.Count_And_Get
881 (
882 p_count => x_msg_count,
883 p_data => x_msg_data,
884 p_encoded => FND_API.G_FALSE
885 );
886
887 IF l_debug_on THEN
888 wsh_debug_sv.log (l_module_name,'l_exception_msg',l_exception_msg);
889 wsh_debug_sv.log (l_module_name,'l_position',l_position);
890 wsh_debug_sv.log (l_module_name,'l_procedure',l_call_procedure);
891 wsh_debug_sv.log (l_module_name,'l_sqlerr',l_sqlerr);
892 wsh_debug_sv.log (l_module_name,'l_sql_code',l_sql_code);
893 END IF;
894 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
895 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
896 x_msg_data := l_sqlerr;
897 FND_MSG_PUB.Count_And_Get
898 (
899 p_count => x_msg_count,
900 p_data => x_msg_data,
901 p_encoded => FND_API.G_FALSE
902 );
903
904 IF x_msg_data IS NULL THEN
905 x_msg_count := 1;
906 x_msg_data := l_sqlerr;
907 END IF;
908
909 IF l_debug_on THEN
910 wsh_debug_sv.log (l_module_name,'l_exception_msg',l_exception_msg);
911 wsh_debug_sv.log (l_module_name,'l_position',l_position);
912 wsh_debug_sv.log (l_module_name,'l_procedure',l_call_procedure);
913 wsh_debug_sv.log (l_module_name,'l_sqlerr',l_sqlerr);
914 wsh_debug_sv.log (l_module_name,'l_sql_code',l_sql_code);
915 END IF;
916
917 WHEN OTHERS THEN
918
922 (
919 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
920 x_msg_data := l_sqlerr;
921 FND_MSG_PUB.Count_And_Get
923 p_count => x_msg_count,
924 p_data => x_msg_data,
925 p_encoded => FND_API.G_FALSE
926 );
927
928 IF x_msg_data IS NULL THEN
929 x_msg_count := 1;
930 x_msg_data := l_sqlerr;
931 END IF;
932
933 IF l_debug_on THEN
934 wsh_debug_sv.log (l_module_name,'l_exception_msg',l_exception_msg);
935 wsh_debug_sv.log (l_module_name,'l_position',l_position);
936 wsh_debug_sv.log (l_module_name,'l_procedure',l_call_procedure);
937 wsh_debug_sv.log (l_module_name,'l_sqlerr',l_sqlerr);
938 wsh_debug_sv.log (l_module_name,'l_sql_code',l_sql_code);
939 END IF;
940
941 END Create_Update_Carrier_Service;
942
943 --========================================================================
944 -- PROCEDURE : Assign_Org_Carrier_Service
945 --
946 --
947 -- PARAMETERS: p_api_version known api version error buffer
948 -- p_init_msg_list FND_API.G_TRUE to reset list
949 -- x_return_status return status
950 -- x_msg_count number of messages in the list
951 -- x_msg_data text of messages
952 -- p_action_code action_code (ASSIGN/UNASSIGN)
953 -- p_rec_attr_tab Table of attributes for the organization carrier service entity
954 -- x_orgcar_ser_out_rec_tab Table of orgcarrier_service_id and ship_method_code.
955 -- VERSION : current version 1.0
956 -- initial version 1.0
957 -- COMMENT : Creates or updates a record in wsh_org_carrier_services
958 --========================================================================
959 PROCEDURE Assign_Org_Carrier_Service
960 ( p_api_version_number IN NUMBER,
961 p_init_msg_list IN VARCHAR2,
962 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
963 p_action_code IN VARCHAR2,
964 p_rec_org_car_ser_tab IN Org_Carrier_Service_Rec_Type,
965 p_rec_car_dff_tab IN Carrier_Info_Dff_Type,
966 p_shp_methods_dff IN Ship_Method_Dff_Type,
967 x_orgcar_ser_out_rec_tab OUT NOCOPY Org_Carrier_Ser_Out_Rec_Type,
968 x_return_status OUT NOCOPY VARCHAR2,
969 x_msg_count OUT NOCOPY NUMBER,
970 x_msg_data OUT NOCOPY VARCHAR2)
971 IS
972 l_debug_on BOOLEAN;
973
974 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ASSIGN_ORG_CARRIER_SERVICE';
975
976 l_index NUMBER;
977 l_num_warnings NUMBER := 0;
978 l_num_errors NUMBER := 0;
979 l_return_status VARCHAR2(1) := 'S';
980 l_exception_msg VARCHAR2(1000);
981 l_position NUMBER;
982 l_action_code VARCHAR2(200);
983 l_call_procedure VARCHAR2(100);
984 l_sql_code NUMBER;
985 l_sqlerr VARCHAR2(2000);
986
987
988 l_api_version_number CONSTANT NUMBER := 1.0;
989 l_api_name CONSTANT VARCHAR2(30):= 'Assign_Org_Carrier_Service';
990 l_msg_count NUMBER;
991 l_msg_data VARCHAR2(32767);
992
993 l_input_param_flag BOOLEAN := TRUE;
994 l_param_name VARCHAR2(100);
995
996 l_org_car_ser_tab WSH_ORG_CARRIER_SERVICES_PKG.OCSRecType;
997 l_car_info_tab WSH_ORG_CARRIER_SERVICES_PKG.CarRecType;
998 l_shp_method_rec WSH_CARRIER_SHIP_METHODS_PKG.CSMRecType;
999
1000 l_org_carrier_service_id NUMBER;
1001 l_rowid VARCHAR2(4000);
1002
1003 l_carrier_id NUMBER;
1004 l_service_level VARCHAR2(200);
1005 l_shp_method_code VARCHAR2(4000);
1006
1007 l_freight_code VARCHAR2(4000);
1008 l_carrier_name VARCHAR2(4000);
1009
1010 CURSOR get_carrier_ser_info(p_carrier_service_id NUMBER) IS
1011 SELECT CARRIER_ID,SERVICE_LEVEL,SHIP_METHOD_CODE
1012 FROM WSH_CARRIER_SERVICES
1013 WHERE carrier_service_id = p_carrier_service_id
1014 AND enabled_flag = 'Y';
1015
1016 CURSOR get_carrier_info(p_carrier_id NUMBER) IS
1017 SELECT car.freight_code,par.PARTY_NAME
1018 FROM WSH_CARRIERS car, HZ_PARTIES par
1019 WHERE car.carrier_id = p_carrier_id
1020 AND car.carrier_id = par.PARTY_ID;
1021
1022 CURSOR get_org_carser_info(p_carrier_service_id NUMBER,p_org_id NUMBER) IS
1023 SELECT org_carrier_service_id,rowid
1024 FROM wsh_Org_Carrier_Services
1025 WHERE carrier_service_id = p_carrier_service_id
1026 AND organization_id = p_org_id;
1027
1028 BEGIN
1029 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1030 IF l_debug_on IS NULL
1031 THEN
1032 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1033 END IF;
1034 IF NOT FND_API.Compatible_API_Call
1035 ( l_api_version_number,
1036 p_api_version_number,
1037 l_api_name,
1038 G_PKG_NAME
1039 )
1040 THEN
1041 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1042 END IF;
1043
1047
1044 IF FND_API.to_Boolean(p_init_msg_list) THEN
1045 FND_MSG_PUB.initialize;
1046 END IF;
1048 IF l_debug_on THEN
1049 wsh_debug_sv.push (l_module_name);
1050 wsh_debug_sv.log (l_module_name,'action_code',p_action_code);
1051 END IF;
1052 IF p_action_code IS NULL THEN
1053 l_param_name := 'action_code';
1054 l_input_param_flag := FALSE;
1055 ELSIF p_rec_org_car_ser_tab.Carrier_Service_id IS NULL THEN
1056 l_param_name := 'carrier_service_id';
1057 l_input_param_flag := FALSE;
1058 ELSIF p_rec_org_car_ser_tab.ORGANIZATION_ID IS NULL THEN
1059 l_param_name := 'organization_id';
1060 l_input_param_flag := FALSE;
1061 END IF;
1062
1063 IF not l_input_param_flag THEN
1064 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
1065 FND_MESSAGE.SET_TOKEN('FIELD_NAME',l_param_name);
1066 x_return_status := wsh_util_core.g_ret_sts_error;
1067 wsh_util_core.add_message(x_return_status,l_module_name);
1068 RAISE FND_API.G_EXC_ERROR;
1069 END IF;
1070 --
1071 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1072 --
1073 OPEN get_carrier_ser_info(p_rec_org_car_ser_tab.Carrier_Service_id);
1074 FETCH get_carrier_ser_info INTO l_carrier_id,l_service_level,l_shp_method_code;
1075 if(get_carrier_ser_info%FOUND) THEN
1076 OPEN get_carrier_info(l_carrier_id);
1077 FETCH get_carrier_info INTO l_freight_code,l_carrier_name;
1078 close get_carrier_info;
1079
1080 OPEN get_org_carser_info(p_rec_org_car_ser_tab.Carrier_Service_id,p_rec_org_car_ser_tab.ORGANIZATION_ID);
1081 FETCh get_org_carser_info INTO l_org_carrier_service_id,l_rowid;
1082 close get_org_carser_info;
1083
1084 l_org_car_ser_tab.CARRIER_SERVICE_ID := p_rec_org_car_ser_tab.Carrier_Service_id;
1085 -- Can't update enabled flag of carrier service while assigning.
1086 --l_org_car_ser_tab.ENABLED_FLAG := p_rec_org_car_ser_tab.Enabled_FLAG;
1087 l_org_car_ser_tab.ORGANIZATION_ID := p_rec_org_car_ser_tab.ORGANIZATION_ID;
1088 l_org_car_ser_tab.ATTRIBUTE_CATEGORY := p_rec_org_car_ser_tab.ATTRIBUTE_CATEGORY;
1089 l_org_car_ser_tab.ATTRIBUTE1 := p_rec_org_car_ser_tab.ATTRIBUTE1;
1090 l_org_car_ser_tab.ATTRIBUTE2 := p_rec_org_car_ser_tab.ATTRIBUTE2;
1091 l_org_car_ser_tab.ATTRIBUTE3 := p_rec_org_car_ser_tab.ATTRIBUTE3;
1092 l_org_car_ser_tab.ATTRIBUTE4 := p_rec_org_car_ser_tab.ATTRIBUTE4;
1093 l_org_car_ser_tab.ATTRIBUTE5 := p_rec_org_car_ser_tab.ATTRIBUTE5;
1094 l_org_car_ser_tab.ATTRIBUTE6 := p_rec_org_car_ser_tab.ATTRIBUTE6;
1095 l_org_car_ser_tab.ATTRIBUTE7 := p_rec_org_car_ser_tab.ATTRIBUTE7;
1096 l_org_car_ser_tab.ATTRIBUTE8 := p_rec_org_car_ser_tab.ATTRIBUTE8;
1097 l_org_car_ser_tab.ATTRIBUTE9 := p_rec_org_car_ser_tab.ATTRIBUTE9;
1098 l_org_car_ser_tab.ATTRIBUTE10 := p_rec_org_car_ser_tab.ATTRIBUTE10;
1099 l_org_car_ser_tab.ATTRIBUTE11 := p_rec_org_car_ser_tab.ATTRIBUTE11;
1100 l_org_car_ser_tab.ATTRIBUTE12 := p_rec_org_car_ser_tab.ATTRIBUTE12;
1101 l_org_car_ser_tab.ATTRIBUTE13 := p_rec_org_car_ser_tab.ATTRIBUTE13;
1102 l_org_car_ser_tab.ATTRIBUTE14 := p_rec_org_car_ser_tab.ATTRIBUTE14;
1103 l_org_car_ser_tab.ATTRIBUTE15 := p_rec_org_car_ser_tab.ATTRIBUTE15;
1104 l_org_car_ser_tab.CREATION_DATE := SYSDATE;
1105 l_org_car_ser_tab.CREATED_BY := fnd_global.user_id;
1106 l_org_car_ser_tab.LAST_UPDATE_DATE := SYSDATE;
1107 l_org_car_ser_tab.LAST_UPDATED_BY := fnd_global.user_id;
1108
1109 -- BugFix#3296461
1110 l_org_car_ser_tab.DISTRIBUTION_ACCOUNT := p_rec_org_car_ser_tab.DISTRIBUTION_ACCOUNT;
1111
1112 l_car_info_tab.P_FREIGHT_CODE := l_freight_code;
1113 l_car_info_tab.P_CARRIER_NAME := substrb(l_carrier_name, 1, 80);
1114 l_car_info_tab.ATTRIBUTE_CATEGORY := p_rec_car_dff_tab.ATTRIBUTE_CATEGORY;
1115 l_car_info_tab.ATTRIBUTE1 := p_rec_car_dff_tab.ATTRIBUTE1;
1116 l_car_info_tab.ATTRIBUTE2 := p_rec_car_dff_tab.ATTRIBUTE2;
1117 l_car_info_tab.ATTRIBUTE3 := p_rec_car_dff_tab.ATTRIBUTE3;
1118 l_car_info_tab.ATTRIBUTE4 := p_rec_car_dff_tab.ATTRIBUTE4;
1119 l_car_info_tab.ATTRIBUTE5 := p_rec_car_dff_tab.ATTRIBUTE5;
1120 l_car_info_tab.ATTRIBUTE6 := p_rec_car_dff_tab.ATTRIBUTE6;
1121 l_car_info_tab.ATTRIBUTE7 := p_rec_car_dff_tab.ATTRIBUTE7;
1122 l_car_info_tab.ATTRIBUTE8 := p_rec_car_dff_tab.ATTRIBUTE8;
1123 l_car_info_tab.ATTRIBUTE9 := p_rec_car_dff_tab.ATTRIBUTE9;
1124 l_car_info_tab.ATTRIBUTE10 := p_rec_car_dff_tab.ATTRIBUTE10;
1125 l_car_info_tab.ATTRIBUTE11 := p_rec_car_dff_tab.ATTRIBUTE11;
1126 l_car_info_tab.ATTRIBUTE12 := p_rec_car_dff_tab.ATTRIBUTE12;
1127 l_car_info_tab.ATTRIBUTE13 := p_rec_car_dff_tab.ATTRIBUTE13;
1128 l_car_info_tab.ATTRIBUTE14 := p_rec_car_dff_tab.ATTRIBUTE14;
1129 l_car_info_tab.ATTRIBUTE15 := p_rec_car_dff_tab.ATTRIBUTE15;
1130 l_car_info_tab.CREATION_DATE := SYSDATE;
1131 l_car_info_tab.CREATED_BY := fnd_global.user_id;
1132 l_car_info_tab.LAST_UPDATE_DATE := SYSDATE;
1136
1133 l_car_info_tab.LAST_UPDATED_BY := fnd_global.user_id;
1134
1135
1137 l_shp_method_rec.Carrier_Id := l_carrier_id;
1138 l_shp_method_rec.ship_method_code := l_shp_method_code;
1139 l_shp_method_rec.freight_code := l_freight_code;
1140 l_shp_method_rec.service_level := l_service_level;
1141 l_shp_method_rec.organization_id := p_rec_org_car_ser_tab.ORGANIZATION_ID;
1142 l_shp_method_rec.ATTRIBUTE_CATEGORY := p_shp_methods_dff.ATTRIBUTE_CATEGORY;
1143 l_shp_method_rec.ATTRIBUTE1 := p_shp_methods_dff.ATTRIBUTE1;
1144 l_shp_method_rec.ATTRIBUTE2 := p_shp_methods_dff.ATTRIBUTE2;
1145 l_shp_method_rec.ATTRIBUTE3 := p_shp_methods_dff.ATTRIBUTE3;
1146 l_shp_method_rec.ATTRIBUTE4 := p_shp_methods_dff.ATTRIBUTE4;
1147 l_shp_method_rec.ATTRIBUTE5 := p_shp_methods_dff.ATTRIBUTE5;
1148 l_shp_method_rec.ATTRIBUTE6 := p_shp_methods_dff.ATTRIBUTE6;
1149 l_shp_method_rec.ATTRIBUTE7 := p_shp_methods_dff.ATTRIBUTE7;
1150 l_shp_method_rec.ATTRIBUTE8 := p_shp_methods_dff.ATTRIBUTE8;
1151 l_shp_method_rec.ATTRIBUTE9 := p_shp_methods_dff.ATTRIBUTE9;
1152 l_shp_method_rec.ATTRIBUTE10 := p_shp_methods_dff.ATTRIBUTE10;
1153 l_shp_method_rec.ATTRIBUTE11 := p_shp_methods_dff.ATTRIBUTE11;
1154 l_shp_method_rec.ATTRIBUTE12 := p_shp_methods_dff.ATTRIBUTE12;
1155 l_shp_method_rec.ATTRIBUTE13 := p_shp_methods_dff.ATTRIBUTE13;
1156 l_shp_method_rec.ATTRIBUTE14 := p_shp_methods_dff.ATTRIBUTE14;
1157 l_shp_method_rec.ATTRIBUTE15 := p_shp_methods_dff.ATTRIBUTE15;
1158 l_shp_method_rec.CREATION_DATE := SYSDATE;
1159 l_shp_method_rec.CREATED_BY := fnd_global.user_id;
1160 l_shp_method_rec.LAST_UPDATE_DATE := SYSDATE;
1161 l_shp_method_rec.LAST_UPDATED_BY := fnd_global.user_id;
1162 IF p_action_code = 'ASSIGN' THEN
1163 l_shp_method_rec.Enabled_Flag := 'Y';
1164 l_org_car_ser_tab.ENABLED_FLAG := 'Y';
1165 ELSIF p_action_code = 'UNASSIGN' THEN
1166 l_shp_method_rec.Enabled_Flag := 'N';
1167 l_org_car_ser_tab.ENABLED_FLAG := 'N';
1168 END IF;
1169 IF p_action_code = 'ASSIGN' OR p_action_code = 'UNASSIGN' THEN
1170
1171 WSH_ORG_CARRIER_SERVICES_PKG.assign_org_carrier_service
1172 (
1173 p_Org_Carrier_Service_info => l_org_car_ser_tab,
1174 p_carrier_info => l_car_info_tab,
1175 p_csm_info => l_shp_method_rec,
1176 P_COMMIT => p_commit,
1177 X_ROWID => l_rowid,
1178 X_ORG_CARRIER_SERVICE_ID => l_org_carrier_service_id,
1179 X_RETURN_STATUS => l_return_status,
1180 X_POSITION => l_position,
1181 X_PROCEDURE => l_call_procedure,
1182 X_SQLERR => l_sqlerr,
1183 X_SQL_CODE => l_sql_code,
1184 x_exception_msg => l_exception_msg
1185 );
1186 IF l_debug_on THEN
1187 wsh_debug_sv.log(l_module_name,'return status WSH_CREATE_CARRIERS_PKG.assign_org_carrier_service',l_return_status);
1188 END IF;
1189 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
1190 x_num_warnings =>l_num_warnings,
1191 x_num_errors =>l_num_errors
1192 );
1193 END IF;
1194 x_orgcar_ser_out_rec_tab.rowid := l_rowid;
1195 x_orgcar_ser_out_rec_tab.org_carrier_service_id := l_org_carrier_service_id;
1196 x_orgcar_ser_out_rec_tab.carrier_service_id := p_rec_org_car_ser_tab.Carrier_Service_id;
1197 x_return_status := l_return_status;
1198 IF FND_API.To_Boolean(p_commit) THEN
1199 COMMIT WORK;
1200 END IF;
1201 END IF;
1202 close get_carrier_ser_info;
1203
1204 EXCEPTION
1205 --
1206 --
1207 WHEN FND_API.G_EXC_ERROR THEN
1208 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1209 x_msg_data := l_sqlerr;
1210 FND_MSG_PUB.Count_And_Get
1211 (
1212 p_count => x_msg_count,
1213 p_data => x_msg_data,
1214 p_encoded => FND_API.G_FALSE
1215 );
1216 IF l_debug_on THEN
1217 wsh_debug_sv.log (l_module_name,'l_exception_msg',l_exception_msg);
1218 wsh_debug_sv.log (l_module_name,'l_position',l_position);
1219 wsh_debug_sv.log (l_module_name,'l_procedure',l_call_procedure);
1220 wsh_debug_sv.log (l_module_name,'l_sqlerr',l_sqlerr);
1221 wsh_debug_sv.log (l_module_name,'l_sql_code',l_sql_code);
1222 END IF;
1223 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1224 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1225 x_msg_data := l_sqlerr;
1226 FND_MSG_PUB.Count_And_Get
1227 (
1228 p_count => x_msg_count,
1229 p_data => x_msg_data,
1230 p_encoded => FND_API.G_FALSE
1231 );
1235 x_msg_data := l_sqlerr;
1232
1233 IF x_msg_data IS NULL THEN
1234 x_msg_count := 1;
1236 END IF;
1237
1238 IF l_debug_on THEN
1239 wsh_debug_sv.log (l_module_name,'l_exception_msg',l_exception_msg);
1240 wsh_debug_sv.log (l_module_name,'l_position',l_position);
1241 wsh_debug_sv.log (l_module_name,'l_procedure',l_call_procedure);
1242 wsh_debug_sv.log (l_module_name,'l_sqlerr',l_sqlerr);
1243 wsh_debug_sv.log (l_module_name,'l_sql_code',l_sql_code);
1244 END IF;
1245
1246 WHEN OTHERS THEN
1247
1248 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1249 x_msg_data := l_sqlerr;
1250 FND_MSG_PUB.Count_And_Get
1251 (
1252 p_count => x_msg_count,
1253 p_data => x_msg_data,
1254 p_encoded => FND_API.G_FALSE
1255 );
1256
1257 IF x_msg_data IS NULL THEN
1258 x_msg_count := 1;
1259 x_msg_data := l_sqlerr;
1260 END IF;
1261
1262 IF l_debug_on THEN
1263 wsh_debug_sv.log (l_module_name,'l_exception_msg',l_exception_msg);
1264 wsh_debug_sv.log (l_module_name,'l_position',l_position);
1265 wsh_debug_sv.log (l_module_name,'l_procedure',l_call_procedure);
1266 wsh_debug_sv.log (l_module_name,'l_sqlerr',l_sqlerr);
1267 wsh_debug_sv.log (l_module_name,'l_sql_code',l_sql_code);
1268 END IF;
1269
1270 END Assign_Org_Carrier_Service;
1271
1272 --========================================================================
1273 -- PROCEDURE : Assign_Org_Carrier
1274 --
1275 --
1276 -- PARAMETERS: p_api_version known api version error buffer
1277 -- p_init_msg_list FND_API.G_TRUE to reset list
1278 -- x_return_status return status
1279 -- x_msg_count number of messages in the list
1280 -- x_msg_data text of messages
1281 -- p_action_code action_code (ASSIGN/UNASSIGN)
1282 -- p_rec_attr_tab Table of attributes for the organization carrier service entity
1283 -- x_orgcar_ser_out_tab Table of orgcarrier_service_id and ship_method_code.
1284 -- VERSION : current version 1.0
1285 -- initial version 1.0
1286 -- COMMENT : The Organization is assigned to all present carrier services the carrier at that poing of time
1287 -- Creates or updates a record in wsh_org_carrier_services,org_freight_tl,wsh_carrier_ship_methods
1288 --========================================================================
1289 PROCEDURE Assign_Org_Carrier
1290 ( p_api_version_number IN NUMBER,
1291 p_init_msg_list IN VARCHAR2,
1292 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1293 p_action_code IN VARCHAR2,
1294 p_carrier_id IN NUMBER,
1295 p_organization_id IN NUMBER,
1296 x_orgcar_ser_out_tab OUT NOCOPY Org_Carrier_Ser_Out_Tab_Type,
1297 x_return_status OUT NOCOPY VARCHAR2,
1298 x_msg_count OUT NOCOPY NUMBER,
1299 x_msg_data OUT NOCOPY VARCHAR2) IS
1300
1301
1302 l_debug_on BOOLEAN;
1303
1304 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ASSIGN_ORG_CARRIER_SERVICE';
1305
1306 l_index NUMBER :=0;
1307 l_num_warnings NUMBER := 0;
1308 l_num_errors NUMBER := 0;
1309 l_return_status VARCHAR2(1) := 'S';
1310 l_exception_msg VARCHAR2(1000);
1311 l_position NUMBER;
1312 l_action_code VARCHAR2(200);
1313 l_call_procedure VARCHAR2(100);
1314 l_sql_code NUMBER;
1315 l_sqlerr VARCHAR2(2000);
1316
1317
1318 l_api_version_number CONSTANT NUMBER := 1.0;
1319 l_api_name CONSTANT VARCHAR2(30):= 'Assign_Org_Carrier_Service';
1320 l_msg_count NUMBER;
1321 l_msg_data VARCHAR2(32767);
1322
1323 l_input_param_flag BOOLEAN := TRUE;
1324 l_param_name VARCHAR2(100);
1325 l_carrier_service_id NUMBER;
1326
1327 p_rec_org_car_ser_tab Org_Carrier_Service_Rec_Type;
1328 p_rec_car_info_tab Carrier_Info_Dff_Type;
1329 p_car_shp_methods Ship_Method_Dff_Type;
1330 p_car_ser_out_rec WSH_CARRIERS_GRP.Org_Carrier_Ser_Out_Rec_Type;
1331 p_car_ser_out_rec_tab Org_Carrier_Ser_Out_Tab_Type;
1332
1333 CURSOR get_carrier_service_ids(p_carrier_id NUMBER) IS
1334 SELECT carrier_service_id
1335 FROM WSH_CARRIER_SERVICES
1336 WHERE carrier_id = p_carrier_id
1337 AND enabled_flag = 'Y';
1338
1339 BEGIN
1340 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1341 IF l_debug_on IS NULL
1342 THEN
1343 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1344 END IF;
1345 IF NOT FND_API.Compatible_API_Call
1346 ( l_api_version_number,
1347 p_api_version_number,
1348 l_api_name,
1349 G_PKG_NAME
1350 )
1351 THEN
1352 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1353 END IF;
1354
1355 IF FND_API.to_Boolean(p_init_msg_list) THEN
1356 FND_MSG_PUB.initialize;
1357 END IF;
1358
1359 IF l_debug_on THEN
1363 IF p_action_code IS NULL THEN
1360 wsh_debug_sv.push (l_module_name);
1361 wsh_debug_sv.log (l_module_name,'action_code',p_action_code);
1362 END IF;
1364 l_param_name := 'action_code';
1365 l_input_param_flag := FALSE;
1366 ELSIF p_carrier_id IS NULL THEN
1367 l_param_name := 'p_carrier_id';
1368 l_input_param_flag := FALSE;
1369 ELSIF p_organization_id IS NULL THEN
1370 l_param_name := 'p_organization_id';
1371 l_input_param_flag := FALSE;
1372
1373 END IF;
1374
1375 IF not l_input_param_flag THEN
1376 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
1377 FND_MESSAGE.SET_TOKEN('FIELD_NAME',l_param_name);
1378 x_return_status := wsh_util_core.g_ret_sts_error;
1379 wsh_util_core.add_message(x_return_status,l_module_name);
1380 RAISE FND_API.G_EXC_ERROR;
1381 END IF;
1382 --
1383 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1384 --
1385 OPEN get_carrier_service_ids(p_carrier_id);
1386 LOOP
1387 FETCH get_carrier_service_ids INTO l_carrier_service_id;
1388 EXIT WHEN (get_carrier_service_ids%NOTFOUND);
1389 p_rec_org_car_ser_tab.CARRIER_SERVICE_ID := l_carrier_service_id;
1390 p_rec_org_car_ser_tab.ORGANIZATION_ID := p_organization_id;
1391
1392 p_rec_org_car_ser_tab.CREATION_DATE := SYSDATE;
1393 p_rec_org_car_ser_tab.CREATED_BY := fnd_global.user_id;
1394 p_rec_org_car_ser_tab.LAST_UPDATE_DATE := SYSDATE;
1395 p_rec_org_car_ser_tab.LAST_UPDATED_BY :=fnd_global.user_id;
1396
1397 p_rec_car_info_tab.CREATION_DATE := SYSDATE;
1398 p_rec_car_info_tab.CREATED_BY := fnd_global.user_id;
1399 p_rec_car_info_tab.LAST_UPDATE_DATE := SYSDATE;
1400 p_rec_car_info_tab.LAST_UPDATED_BY :=fnd_global.user_id;
1401
1402 p_car_shp_methods.CREATION_DATE := SYSDATE;
1403 p_car_shp_methods.CREATED_BY := fnd_global.user_id;
1404 p_car_shp_methods.LAST_UPDATE_DATE := SYSDATE;
1405 p_car_shp_methods.LAST_UPDATED_BY := fnd_global.user_id;
1406
1407 Assign_Org_Carrier_Service(
1408 p_action_code =>p_action_code,
1409 p_rec_org_car_ser_tab =>p_rec_org_car_ser_tab,
1410 p_rec_car_dff_tab =>p_rec_car_info_tab,
1411 p_shp_methods_dff =>p_car_shp_methods,
1412 x_orgcar_ser_out_rec_tab =>p_car_ser_out_rec,
1413 p_api_version_number => 1.0,
1414 p_init_msg_list => p_init_msg_list,
1415 p_commit => p_commit,
1416 x_return_status => x_return_status,
1417 x_msg_count => x_msg_count,
1418 x_msg_data => x_msg_data
1419 );
1420 IF l_debug_on THEN
1421 wsh_debug_sv.log(l_module_name,'return status WSH_CREATE_CARRIERS_PKG.Assign_Org_Carrier_Service',l_return_status);
1422 END IF;
1423 WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
1424 x_num_warnings =>l_num_warnings,
1425 x_num_errors =>l_num_errors
1426 );
1427 x_orgcar_ser_out_tab(l_index) := p_car_ser_out_rec;
1428 l_index := l_index+1;
1429 END LOOP;
1430 CLOSE get_carrier_service_ids;
1431 IF FND_API.To_Boolean(p_commit) THEN
1432 COMMIT WORK;
1433 END IF;
1434 EXCEPTION
1435 --
1436 --
1437 WHEN FND_API.G_EXC_ERROR THEN
1438 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1439 x_msg_data := l_sqlerr;
1440 FND_MSG_PUB.Count_And_Get
1441 (
1442 p_count => x_msg_count,
1443 p_data => x_msg_data,
1444 p_encoded => FND_API.G_FALSE
1445 );
1446 IF l_debug_on THEN
1447 wsh_debug_sv.log (l_module_name,'l_exception_msg',l_exception_msg);
1448 wsh_debug_sv.log (l_module_name,'l_position',l_position);
1449 wsh_debug_sv.log (l_module_name,'l_procedure',l_call_procedure);
1450 wsh_debug_sv.log (l_module_name,'l_sqlerr',l_sqlerr);
1451 wsh_debug_sv.log (l_module_name,'l_sql_code',l_sql_code);
1452 END IF;
1453 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1454 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1455 x_msg_data := l_sqlerr;
1456 FND_MSG_PUB.Count_And_Get
1457 (
1458 p_count => x_msg_count,
1459 p_data => x_msg_data,
1460 p_encoded => FND_API.G_FALSE
1461 );
1462
1463 IF x_msg_data IS NULL THEN
1464 x_msg_count := 1;
1465 x_msg_data := l_sqlerr;
1466 END IF;
1467
1468 IF l_debug_on THEN
1469 wsh_debug_sv.log (l_module_name,'l_exception_msg',l_exception_msg);
1470 wsh_debug_sv.log (l_module_name,'l_position',l_position);
1471 wsh_debug_sv.log (l_module_name,'l_procedure',l_call_procedure);
1472 wsh_debug_sv.log (l_module_name,'l_sqlerr',l_sqlerr);
1473 wsh_debug_sv.log (l_module_name,'l_sql_code',l_sql_code);
1474 END IF;
1475
1476 WHEN OTHERS THEN
1477
1478 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1479 x_msg_data := l_sqlerr;
1480 FND_MSG_PUB.Count_And_Get
1481 (
1482 p_count => x_msg_count,
1483 p_data => x_msg_data,
1484 p_encoded => FND_API.G_FALSE
1485 );
1486
1487 IF x_msg_data IS NULL THEN
1488 x_msg_count := 1;
1492 IF l_debug_on THEN
1489 x_msg_data := l_sqlerr;
1490 END IF;
1491
1493 wsh_debug_sv.log (l_module_name,'l_exception_msg',l_exception_msg);
1494 wsh_debug_sv.log (l_module_name,'l_position',l_position);
1495 wsh_debug_sv.log (l_module_name,'l_procedure',l_call_procedure);
1496 wsh_debug_sv.log (l_module_name,'l_sqlerr',l_sqlerr);
1497 wsh_debug_sv.log (l_module_name,'l_sql_code',l_sql_code);
1498 END IF;
1499
1500 END Assign_Org_Carrier;
1501
1502 PROCEDURE Get_Meanings
1503 (
1504
1505 sl_time_uom IN VARCHAR2,
1506 service_level_code IN VARCHAR2,
1507 mode_of_trans_code IN VARCHAR2,
1508 x_service_level OUT NOCOPY VARCHAR2,
1509 x_mode_of_transport OUT NOCOPY VARCHAR2,
1510 x_sl_time_uom_desc OUT NOCOPY VARCHAR2
1511 )
1512
1513
1514 IS
1515
1516 BEGIN
1517
1518 IF (SERVICE_LEVEL_CODE IS NOT NULL) THEN
1519 SELECT meaning
1520 INTO x_service_level
1521 FROM WSH_LOOKUPS
1522 WHERE LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'
1523 AND LOOKUP_CODE = SERVICE_LEVEL_CODE;
1524 END IF;
1525 IF (MODE_OF_TRANS_CODE IS NOT NULL) THEN
1526 SELECT meaning
1527 INTO x_mode_of_transport
1528 FROM WSH_LOOKUPS
1529 WHERE LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
1530 AND LOOKUP_CODE = MODE_OF_TRANS_CODE;
1531 END IF;
1532 IF (SL_TIME_UOM IS NOT NULL) THEN
1533 select UNIT_OF_MEASURE
1534 INTO x_sl_time_uom_desc
1535 FROM MTL_UNITS_OF_MEASURE_VL
1536 WHERE UOM_CLASS LIKE 'Time'
1537 AND UOM_CODE = SL_TIME_UOM;
1538 END IF;
1539 EXCEPTION
1540 WHEN NO_DATA_FOUND THEN
1541 null;
1542 END Get_Meanings;
1543
1544
1545 PROCEDURE Generate_Ship_Method
1546 (
1547 service_level_code IN VARCHAR2,
1548 freight_code IN VARCHAR2,
1549 mode_of_trans_code IN VARCHAR2,
1550 ship_method_meaning IN VARCHAR2 DEFAULT NULL,
1551 x_ship_method_code OUT NOCOPY VARCHAR2
1552 )
1553 IS
1554
1555 l_initial VARCHAR2(6);
1556 l_MOT VARCHAR2(1);
1557 l_carrier VARCHAR2(10);
1558 l_service_level VARCHAR2(10);
1559 l_code VARCHAR2(30);
1560 l_code1 VARCHAR2(8);
1561 l_count NUMBER;
1562 l_maxnum NUMBER;
1563 l_sm VARCHAR2(80);
1564
1565
1566 CURSOR Check_Ship_Method_Exists(p_ship_method_code VARCHAR2) IS
1567 SELECT count(*)
1568 FROM fnd_lookup_values_vl
1569 WHERE lookup_code = p_ship_method_code
1570 AND lookup_type = 'SHIP_METHOD';
1571
1572
1573
1574 CURSOR Get_Ship_Method_By_Meaning(p_ship_method_meaning VARCHAR2) IS
1575 SELECT lookup_code
1576 FROM fnd_lookup_values_vl
1577 WHERE meaning = p_ship_method_meaning
1578 AND lookup_type = 'SHIP_METHOD';
1579
1580
1581 BEGIN
1582
1583 -- iSetup bug 3924555
1584 -- return the existing Ship_Method for the given Ship_Method_Meaning
1585 -- if this Ship_Method_Meaning exists in WSH_CARRIER_SERVICES
1586 -- error will be caught by Check_Duplicate_SMM_Create cursor
1587 -- otherwise, it means the lookup value exists but no Carrier Service
1588 -- exists in WSH_CARRIER_SERVICES, which is the bug case.
1589
1590 IF (ship_method_meaning IS NOT NULL) THEN
1591
1592 OPEN Get_Ship_Method_By_Meaning(ship_method_meaning);
1593 FETCH Get_Ship_Method_By_Meaning INTO l_code;
1594 CLOSE Get_Ship_Method_By_Meaning;
1595
1596 IF (l_code IS NOT NULL) THEN
1597 x_ship_method_code := l_code;
1598 return;
1599 END IF;
1600 END IF;
1601
1602 --------------------------------------------------------------
1603 -- The following code is to generate the SHIP_METHOD_CODE.
1604 --------------------------------------------------------------
1605
1606 l_initial := '000001';
1607 l_MOT := SUBSTR(mode_of_trans_code,1,1);
1608 l_carrier := RTRIM(SUBSTR(freight_code,1,10),' ');
1609 l_service_level := RTRIM(SUBSTR(service_level_code,1,10),' ');
1610
1611 l_code := l_initial || '_' || l_carrier || '_' || l_MOT || '_' || l_service_level;
1612
1613
1614
1615 OPEN Check_Ship_Method_Exists(l_code);
1616 FETCH Check_Ship_Method_Exists INTO l_count;
1617 CLOSE Check_Ship_Method_Exists;
1618
1619 IF l_count > 0 THEN
1620
1621 SELECT max(to_number(substr(lookup_code,1,6)))
1622 INTO l_maxnum
1623 FROM fnd_lookup_values_vl
1624 WHERE substr(lookup_code,7, length(lookup_code)) = substr(l_code,7,length(l_code))
1625 AND lookup_type = 'SHIP_METHOD';
1626
1627 SELECT lpad(to_char(l_maxnum),6,'0') into l_code1 from dual;
1628
1629 l_code := lpad(to_char(to_number(substr(l_code1,1,6))+1),6,'0')||substr(l_code,7,length(l_code));
1630
1631 END IF;
1632
1633
1634 x_ship_method_code:= l_code;
1635
1636 EXCEPTION
1640
1637 WHEN NO_DATA_FOUND THEN
1638 null;
1639 END Generate_Ship_Method;
1641 --========================================================================
1642 -- PROCEDURE : Get_Carrier_Name
1643 --
1644 --
1645 -- PARAMETERS: p_carrier_id carrier_id for which name is desired
1646 -- x_carrier_name carrier name given id
1647 -- x_freight_code carrier freight code given id from wsh_carriers
1648 --
1649 --========================================================================
1650 PROCEDURE Get_Carrier_Name
1651 (
1652 p_carrier_id IN NUMBER,
1653 x_carrier_name OUT NOCOPY VARCHAR2,
1654 x_freight_code OUT NOCOPY VARCHAR2
1655 ) IS
1656
1657 CURSOR get_carrier_name IS
1658 SELECT party_name, freight_code
1659 FROM wsh_carriers, hz_parties
1660 WHERE carrier_id =party_id
1661 AND carrier_id= p_carrier_id;
1662
1663 --
1664 l_debug_on BOOLEAN;
1665 --
1666 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CARRIER_NAME';
1667 --
1668
1669 BEGIN
1670
1671 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1672 IF l_debug_on IS NULL
1673 THEN
1674 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1675 END IF;
1676
1677 IF l_debug_on THEN
1678 WSH_DEBUG_SV.push(l_module_name);
1679 --
1680 WSH_DEBUG_SV.log(l_module_name,'P_CARRIER_ID',P_CARRIER_ID);
1681 END IF;
1682
1683 OPEN get_carrier_name;
1684 FETCH get_carrier_name INTO x_carrier_name, x_freight_code;
1685 CLOSE get_carrier_name;
1686
1687 IF l_debug_on THEN
1688 WSH_DEBUG_SV.pop(l_module_name);
1689 END IF;
1690
1691 END Get_Carrier_Name;
1692
1693 --========================================================================
1694 -- PROCEDURE : get_carrier_service_mode
1695 --
1696 --========================================================================
1697 PROCEDURE get_carrier_service_mode
1698 (
1699 p_carrier_service_inout_rec IN OUT NOCOPY Carrier_Service_InOut_Rec_Type,
1700 x_return_status OUT NOCOPY VARCHAR2
1701 ) IS
1702
1703 CURSOR get_carrier_service_mode IS
1704 SELECT wc.carrier_id,
1705 wc.freight_code,
1706 wc.scac_code,
1707 wc.manifesting_enabled_flag,
1708 wc.currency_code,
1709 nvl(wc.generic_flag, 'N'),
1710 wcs.carrier_service_id,
1711 wcs.service_level,
1712 wcs.mode_of_transport,
1713 wcs.ship_method_code
1714 FROM wsh_carriers wc,
1715 wsh_carrier_services wcs
1716 WHERE wc.carrier_id = wcs.carrier_id
1717 AND ( wcs.ship_method_code = p_carrier_service_inout_rec.ship_method_code OR
1718 wcs.carrier_service_id = p_carrier_service_inout_rec.carrier_service_id);
1719
1720 Invalid_ship_method exception;
1721
1722 --
1723 l_debug_on BOOLEAN;
1724 --
1725 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_carrier_service_mode';
1726 --
1727 BEGIN
1728 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1729
1730 IF l_debug_on IS NULL THEN
1731 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1732 END IF;
1733
1734 IF l_debug_on THEN
1735 WSH_DEBUG_SV.push(l_module_name);
1736 WSH_DEBUG_SV.log(l_module_name,'p_carrier_service_inout_rec.ship_method_code',p_carrier_service_inout_rec.ship_method_code);
1737 WSH_DEBUG_SV.log(l_module_name,'p_carrier_service_inout_rec.carrier_service_id',p_carrier_service_inout_rec.carrier_service_id);
1738 END IF;
1739
1740 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1741
1742 OPEN get_carrier_service_mode;
1743 FETCH get_carrier_service_mode INTO p_carrier_service_inout_rec;
1744 IF get_carrier_service_mode%NOTFOUND THEN
1745 raise Invalid_ship_method;
1746 END IF;
1747 CLOSE get_carrier_service_mode;
1748
1749 IF l_debug_on THEN
1750 WSH_DEBUG_SV.pop(l_module_name);
1751 END IF;
1752
1753 EXCEPTION
1754 WHEN Invalid_ship_method THEN
1755 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1756 fnd_message.set_name('WSH', 'WSH_GET_SERVICE_MODE_ERROR');
1757 wsh_util_core.add_message(wsh_util_core.g_ret_sts_success);
1758 --
1759 -- Debug Statements
1760 --
1761 IF l_debug_on THEN
1762 WSH_DEBUG_SV.logmsg(l_module_name,'Ship Method Code or Carrier Service ID is not valid');
1763 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_ship_method');
1764 END IF;
1765 --
1766 WHEN OTHERS THEN
1767 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1768 --
1769 -- Debug Statements
1770 --
1771 IF l_debug_on THEN
1772 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1773 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1774 END IF;
1775 --
1776 END get_carrier_service_mode;
1777
1778 END WSH_CARRIERS_GRP;
1779