[Home] [Help]
PACKAGE BODY: APPS.WSH_ORG_CARRIER_SERVICES_PKG
Source
1 PACKAGE BODY WSH_ORG_CARRIER_SERVICES_PKG as
2 /* $Header: WSHOCTHB.pls 120.1.12000000.2 2007/07/20 06:08:51 jnpinto ship $ */
3
4 --- Package Name: WSH_ORG_CARRIER_SERVICES_PKG
5 --- Pupose: Table Handlers for table WSH_ORG_CARRIER_SERVICES
6 --- Note: Please set tabstop=3 to read file with proper alignment
7
8 --
9 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_ORG_CARRIER_SERVICES_PKG';
10 --
11 PROCEDURE Assign_Org_Carrier_Service
12 (
13 p_org_Carrier_Service_Info IN OCSRecType
14 , p_carrier_info IN CarRecType
15 , p_csm_info IN WSH_CARRIER_SHIP_METHODS_PKG.CSMRecType
16 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
17 , x_Rowid IN OUT NOCOPY VARCHAR2
18 , x_Org_Carrier_Service_id IN OUT NOCOPY NUMBER
19 , x_Return_Status OUT NOCOPY VARCHAR2
20 , x_position OUT NOCOPY VARCHAR2
21 , x_procedure OUT NOCOPY VARCHAR2
22 , x_sqlerr OUT NOCOPY VARCHAR2
23 , x_sql_code OUT NOCOPY VARCHAR2
24 , x_exception_msg OUT NOCOPY VARCHAR2
25 )
26 IS
27
28 CURSOR C_Next_id
29 IS
30 SELECT wsh_org_Carrier_Services_s.nextval
31 FROM sys.dual;
32
33
34 CURSOR C_New_Rowid(p_org_carrier_service_id NUMBER)
35 IS
36 SELECT rowid
37 FROM WSH_org_Carrier_ServiceS
38 WHERE org_Carrier_Service_id = p_org_Carrier_Service_id;
39
40 --- Bug 2796816
41 CURSOR c_distribution_account(p_freight_code varchar2,p_organization_id number) is
42 SELECT distribution_account
43 FROM ORG_FREIGHT_TL
44 WHERE freight_code = p_freight_code
45 and organization_id = p_organization_id;
46
47 CURSOR c_service_exists
48 (p_carrier_service_id number,p_freight_code varchar2,p_organization_id number) is
49 SELECT 'Y'
50 FROM wsh_org_carrier_services wocs,
51 wsh_carrier_services_v wcs,
52 wsh_carriers_v wc
53 WHERE wc.carrier_id = wcs.carrier_id
54 and wocs.carrier_service_id = wcs.carrier_service_id
55 and wocs.carrier_service_id <> p_carrier_service_id
56 and wc.freight_code = p_freight_code
57 and wocs.organization_id = p_organization_id
58 and wocs.enabled_flag = 'Y'
59 and rownum =1;
60
61 --bug 6126916: cursor to fetch org_freight_tl details
62 CURSOR c_get_org_freight_tl( p_freight_code varchar2, p_organization_id number) is
63 select * from org_freight_tl
64 where freight_code = p_freight_code
65 and organization_id = p_organization_id
66 and language = userenv('LANG');
67
68 l_org_carrier_service_id NUMBER;
69 l_rowid rowid;
70 l_disable_date DATE;
71 l_oft_rowid rowid;
72 l_csm_rowid VARCHAR2(40);
73 l_procedure VARCHAR2(500);
74 l_position NUMBER;
75 l_carrier_ship_method_id NUMBER;
76 l_csm_info WSH_CARRIER_SHIP_METHODS_PKG.CSMRecType;
77
78 --Variable added for bug 6126916
79 l_org_freight_tl_info c_get_org_freight_tl%rowtype;
80
81 NO_DATA_FOUND EXCEPTION;
82 OTHERS EXCEPTION;
83 Failed_in_CSM EXCEPTION;
84 l_return_status VARCHAR2(10);
85 l_service_exists varchar2(2) := 'N'; --- 2796816
86 l_distribution_account ORG_FREIGHT_TL.distribution_account%type;
87 l_orgfgt_update_allowed VARCHAR2(1);
88 --
89 l_debug_on BOOLEAN;
90 --
91 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ASSIGN_ORG_CARRIER_SERVICE';
92 --
93 BEGIN
94 --
95 --
96 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
97 --
98 IF l_debug_on IS NULL
99 THEN
100 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
101 END IF;
102 --
103 IF l_debug_on THEN
104 WSH_DEBUG_SV.push(l_module_name);
105 --
106 WSH_DEBUG_SV.log(l_module_name,'X_ROWID',X_ROWID);
107 WSH_DEBUG_SV.log(l_module_name,'X_ORG_CARRIER_SERVICE_ID',X_ORG_CARRIER_SERVICE_ID);
108 END IF;
109 --
110 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
111
112 IF p_org_Carrier_Service_Info.Enabled_Flag = 'N' THEN
113 l_disable_date := sysdate;
114 ELSE
115 l_disable_date := NULL;
116 END IF;
117
118 IF (X_Org_Carrier_Service_id is NULL) THEN
119 OPEN C_Next_id;
120 FETCH C_Next_id INTO l_Org_Carrier_Service_Id;
121 CLOSE C_Next_id;
122
123 l_position := 10;
124 l_procedure := 'Inserting into Wsh_org_carrier_services';
125
126 --
127 IF l_debug_on THEN
128 WSH_DEBUG_SV.logmsg(l_module_name,'Inserting into WSH_ORG_CARRIER_SERVICES',WSH_DEBUG_SV.C_PROC_LEVEL);
129 END IF;
130 --
131
132 INSERT INTO wsh_org_Carrier_Services
133 ( org_Carrier_Service_id,
134 carrier_service_id,
135 organization_id,
136 enabled_flag,
137 attribute_category,
138 attribute1,
139 attribute2,
140 attribute3,
141 attribute4,
142 attribute5,
143 attribute6,
144 attribute7,
145 attribute8,
146 attribute9,
147 attribute10,
148 attribute11,
149 attribute12,
150 attribute13,
151 attribute14,
152 attribute15,
153 creation_date,
154 created_by,
155 last_update_date,
156 last_updated_by,
157 last_update_login
158 ) VALUES (
159 l_org_Carrier_Service_id,
160 p_org_Carrier_Service_Info.carrier_service_id,
161 p_org_Carrier_Service_Info.organization_id,
162 p_org_Carrier_Service_Info.Enabled_Flag,
163 p_org_Carrier_Service_Info.Attribute_Category,
164 p_org_Carrier_Service_Info.Attribute1,
165 p_org_Carrier_Service_Info.Attribute2,
166 p_org_Carrier_Service_Info.Attribute3,
167 p_org_Carrier_Service_Info.Attribute4,
168 p_org_Carrier_Service_Info.Attribute5,
169 p_org_Carrier_Service_Info.Attribute6,
170 p_org_Carrier_Service_Info.Attribute7,
171 p_org_Carrier_Service_Info.Attribute8,
172 p_org_Carrier_Service_Info.Attribute9,
173 p_org_Carrier_Service_Info.Attribute10,
174 p_org_Carrier_Service_Info.Attribute11,
175 p_org_Carrier_Service_Info.Attribute12,
176 p_org_Carrier_Service_Info.Attribute13,
177 p_org_Carrier_Service_Info.Attribute14,
178 p_org_Carrier_Service_Info.Attribute15,
179 sysdate,
180 fnd_global.user_id,
181 sysdate,
182 fnd_global.user_id,
183 fnd_global.login_id);
184
185 IF l_debug_on THEN
186 WSH_DEBUG_SV.log(l_module_name,'Rows inserted',SQL%ROWCOUNT);
187 END IF;
188 OPEN C_New_Rowid(l_org_carrier_service_id);
189 FETCH C_New_Rowid INTO l_rowid;
190 IF (C_New_Rowid%NOTFOUND) THEN
191 CLOSE C_New_Rowid;
192 RAISE others;
193 END IF;
194
195 l_position := 20;
196 l_procedure := 'Inserting into ORG_FREIGHT_TL';
197 --
198 IF l_debug_on THEN
199 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit ORG_FREIGHT_TL_PKG.INSERT_ROW',WSH_DEBUG_SV.C_PROC_LEVEL);
200 END IF;
201 --
202 ORG_FREIGHT_TL_PKG.INSERT_ROW(
203 X_ROWID => l_oft_rowid,
204 X_FREIGHT_CODE => p_carrier_info.P_FREIGHT_CODE,
205 X_FREIGHT_CODE_TL => p_carrier_info.P_FREIGHT_CODE,
206 X_ORGANIZATION_ID => p_org_carrier_service_info.ORGANIZATION_ID,
207 X_DISABLE_DATE => l_disable_date,
208 X_DISTRIBUTION_ACCOUNT => p_org_Carrier_Service_Info.DISTRIBUTION_ACCOUNT, -- BugFix#3296461
209 --bug 6126916: While inserting, populate NULL values for DFF attributes
210 --(similar to GDF attributes)
211 X_ATTRIBUTE_CATEGORY => NULL,
212 X_ATTRIBUTE1 => NULL,
213 X_ATTRIBUTE2 => NULL,
214 X_ATTRIBUTE3 => NULL,
215 X_ATTRIBUTE4 => NULL,
216 X_ATTRIBUTE5 => NULL,
217 X_ATTRIBUTE6 => NULL,
218 X_ATTRIBUTE7 => NULL,
219 X_ATTRIBUTE8 => NULL,
220 X_ATTRIBUTE9 => NULL,
221 X_ATTRIBUTE10 => NULL,
222 X_ATTRIBUTE11 => NULL,
223 X_ATTRIBUTE12 => NULL,
224 X_ATTRIBUTE13 => NULL,
225 X_ATTRIBUTE14 => NULL,
226 X_ATTRIBUTE15 => NULL,
227 X_GLOBAL_ATTRIBUTE1 => NULL,
228 X_GLOBAL_ATTRIBUTE2 => NULL,
229 X_GLOBAL_ATTRIBUTE3 => NULL,
230 X_GLOBAL_ATTRIBUTE4 => NULL,
231 X_GLOBAL_ATTRIBUTE5 => NULL,
232 X_GLOBAL_ATTRIBUTE6 => NULL,
233 X_GLOBAL_ATTRIBUTE7 => NULL,
234 X_GLOBAL_ATTRIBUTE8 => NULL,
235 X_GLOBAL_ATTRIBUTE9 => NULL,
236 X_GLOBAL_ATTRIBUTE10 => NULL,
237 X_GLOBAL_ATTRIBUTE11 => NULL,
238 X_GLOBAL_ATTRIBUTE12 => NULL,
239 X_GLOBAL_ATTRIBUTE13 => NULL,
240 X_GLOBAL_ATTRIBUTE14 => NULL,
241 X_GLOBAL_ATTRIBUTE15 => NULL,
242 X_GLOBAL_ATTRIBUTE16 => NULL,
243 X_GLOBAL_ATTRIBUTE17 => NULL,
244 X_GLOBAL_ATTRIBUTE18 => NULL,
245 X_GLOBAL_ATTRIBUTE19 => NULL,
246 X_GLOBAL_ATTRIBUTE20 => NULL,
247 X_GLOBAL_ATTRIBUTE_CATEGORY => NULL,
248 X_DESCRIPTION => SUBSTRB(p_carrier_info.P_CARRIER_NAME,1,80),
249 X_CREATION_DATE => SYSDATE, -- Bug 5478419
250 X_CREATED_BY => p_carrier_info.CREATED_BY,
251 X_LAST_UPDATE_DATE => SYSDATE, -- Bug 5478419
252 X_LAST_UPDATED_BY => p_carrier_info.LAST_UPDATED_BY,
253 X_LAST_UPDATE_LOGIN => p_carrier_info.LAST_UPDATE_LOGIN);
254
255 l_position := 30;
256 l_procedure := 'Updating Org_Freight_TL';
257
258 --
259 IF l_debug_on THEN
260 WSH_DEBUG_SV.logmsg(l_module_name,'Updating ORG_FREIGHT_TL',WSH_DEBUG_SV.C_PROC_LEVEL);
261 END IF;
262 --
263
264 UPDATE ORG_FREIGHT_TL
265 SET party_id = p_csm_info.carrier_id,
266 disable_date = l_disable_date
267 WHERE freight_code = p_carrier_info.p_freight_code
268 and organization_id = p_org_carrier_service_info.organization_id;
269 IF l_debug_on THEN
270 WSH_DEBUG_SV.log(l_module_name,'Rows updated',SQL%ROWCOUNT);
271 END IF;
272 IF SQL%NOTFOUND THEN
273 RAISE NO_DATA_FOUND;
274 END IF;
275
276 l_position := 40;
277 l_procedure := 'Calling Create_Carrier_Ship_Method';
278
279 --
280 IF l_debug_on THEN
281 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_CARRIER_SHIP_METHODS_PKG.Create_Carrier_Ship_Method',WSH_DEBUG_SV.C_PROC_LEVEL);
282 END IF;
283 --
284
285 WSH_CARRIER_SHIP_METHODS_PKG.Create_Carrier_Ship_Method
286 (
287 p_carrier_ship_method_info => p_CSM_info,
288 x_rowid => l_csm_rowid,
289 x_carrier_ship_method_id => l_carrier_ship_method_id,
290 x_return_status => l_return_status
291 );
292
293 IF (l_return_status <> 'S') THEN
294 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
295 -- RAISE Failed_In_CSM;
296 END IF;
297 IF l_debug_on THEN
298 WSH_DEBUG_SV.log(l_module_name,'l_return_status',l_return_status);
299 END IF;
300 x_org_carrier_service_id := l_org_carrier_service_id;
301 x_rowid := l_rowid;
302
303 ELSE
304
305 --
306 IF l_debug_on THEN
307 WSH_DEBUG_SV.logmsg(l_module_name,'Updating WSH_ORG_CARRIER_SERVICES',WSH_DEBUG_SV.C_PROC_LEVEL);
308 END IF;
309 --
310 L_POSITION := 20;
311 L_PROCEDURE := 'Updating WSH_ORG_CARRIER_SERVICES';
312
313 UPDATE wsh_Org_Carrier_Services
314 SET
315 enabled_flag = p_org_Carrier_Service_Info.Enabled_Flag,
316 attribute_category = p_org_Carrier_Service_Info.Attribute_Category,
317 attribute1 = p_org_Carrier_Service_Info.Attribute1,
318 attribute2 = p_org_Carrier_Service_Info.Attribute2,
319 attribute3 = p_org_Carrier_Service_Info.Attribute3,
320 attribute4 = p_org_Carrier_Service_Info.Attribute4,
321 attribute5 = p_org_Carrier_Service_Info.Attribute5,
322 attribute6 = p_org_Carrier_Service_Info.Attribute6,
323 attribute7 = p_org_Carrier_Service_Info.Attribute7,
324 attribute8 = p_org_Carrier_Service_Info.Attribute8,
325 attribute9 = p_org_Carrier_Service_Info.Attribute9,
326 attribute10 = p_org_Carrier_Service_Info.Attribute10,
327 attribute11 = p_org_Carrier_Service_Info.Attribute11,
328 attribute12 = p_org_Carrier_Service_Info.Attribute12,
329 attribute13 = p_org_Carrier_Service_Info.Attribute13,
330 attribute14 = p_org_Carrier_Service_Info.Attribute14,
331 attribute15 = p_org_Carrier_Service_Info.Attribute15,
332 last_update_date = p_org_Carrier_Service_Info.Last_Update_Date,
333 last_updated_by = p_org_Carrier_Service_Info.Last_Updated_By,
334 last_update_login = p_org_Carrier_Service_Info.Last_Update_Login
335 WHERE rowid = x_rowid;
336
337 IF (SQL%NOTFOUND) THEN
338 RAISE NO_DATA_FOUND;
339 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
340 END IF;
341
342 L_POSITION := 30;
343 L_PROCEDURE := 'Updating WSH_CARRIER_SHIP_METHODS';
344
345 UPDATE WSH_CARRIER_SHIP_METHODS
346 SET ENABLED_FLAG = p_org_Carrier_Service_Info.Enabled_Flag,
347 WEB_ENABLED = p_csm_info.web_enabled
348 WHERE ORGANIZATION_ID = p_csm_info.organization_id
349 AND SHIP_METHOD_CODE = p_csm_info.ship_method_code;
350
351 IF (SQL%NOTFOUND) THEN
352 RAISE NO_DATA_FOUND;
353 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
354 END IF;
355
356 -- Bug 2796816 Start
357 OPEN c_distribution_account(p_carrier_info.p_freight_code,p_org_carrier_service_info.organization_id);
358 FETCH c_distribution_account into l_distribution_account;
359 CLOSE c_distribution_account;
360
361 IF p_org_Carrier_Service_Info.Enabled_Flag = 'N' THEN
362 OPEN c_service_exists(p_org_Carrier_Service_Info.carrier_service_id,p_carrier_info.p_freight_code,
363 p_org_carrier_service_info.organization_id);
364 FETCH c_service_exists into l_service_exists;
365 CLOSE c_service_exists;
366 END IF;
367
368 IF p_org_Carrier_Service_Info.Enabled_Flag = 'N' and l_service_exists = 'Y' THEN
369 l_disable_date := NULL;
370 END IF;
371 -- Bug 2796816 End
372
373
374 -- Bug 3537378 : Do not call ORG_FREIGHT_TL.Update_Row if Enabled Flag is N and SERVICE exist is N
375 -- Should not Update the Org. Fgt. Record, Nothing to Update in Org. Freight
376 -- Functionality: Inactivating a Org. Car. Svc. SHOULD NOT Disable/Inactivate an Org.Fgt.tl record
377 IF ( p_org_Carrier_Service_Info.Enabled_Flag = 'N' and l_service_exists = 'N' ) THEN
378 l_orgfgt_update_allowed := 'N';
379 ELSE
380 l_orgfgt_update_allowed := 'Y';
381 END IF;
382
383
384 L_POSITION := 40;
385 L_PROCEDURE := 'Calling ORG_FREIGHT_TL.Update_Row';
386 --
387 IF l_debug_on THEN
388 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit ORG_FREIGHT_TL_PKG.UPDATE_ROW',WSH_DEBUG_SV.C_PROC_LEVEL);
389 END IF;
390
391 -- Bug 3537378
392 IF (l_orgfgt_update_allowed = 'Y') THEN -- Update Allowled
393 --
394 --Start of fix for bug 6126916: Get Org_Freight_Tl details
395 OPEN c_get_org_freight_tl( p_carrier_info.P_FREIGHT_CODE, p_org_carrier_service_info.ORGANIZATION_ID );
396 FETCH c_get_org_freight_tl into l_org_freight_tl_info;
397 --
398 IF l_debug_on THEN
399 WSH_DEBUG_SV.logmsg(l_module_name,'No of rows fetched from cursor c_get_org_freight_tl: ' || c_get_org_freight_tl%ROWCOUNT);
400 END IF;
401 --
402 CLOSE c_get_org_freight_tl;
403 --End of fix for bug 6126916
404
405 ORG_FREIGHT_TL_PKG.UPDATE_ROW(
406 X_FREIGHT_CODE => p_carrier_info.P_FREIGHT_CODE,
407 X_FREIGHT_CODE_TL => p_carrier_info.P_FREIGHT_CODE,
408 X_ORGANIZATION_ID => p_org_carrier_service_info.ORGANIZATION_ID,
409 X_DISABLE_DATE => l_disable_date,
410 X_DISTRIBUTION_ACCOUNT => nvl(p_org_Carrier_Service_Info.DISTRIBUTION_ACCOUNT, l_distribution_account),
411 --Bug 6126916: While updating, populate DFF and GDF values from
412 -- Org_Freight_Tl table
413 X_ATTRIBUTE_CATEGORY => l_org_freight_tl_info.ATTRIBUTE_CATEGORY,
414 X_ATTRIBUTE1 => l_org_freight_tl_info.ATTRIBUTE1,
415 X_ATTRIBUTE2 => l_org_freight_tl_info.ATTRIBUTE2,
416 X_ATTRIBUTE3 => l_org_freight_tl_info.ATTRIBUTE3,
417 X_ATTRIBUTE4 => l_org_freight_tl_info.ATTRIBUTE4,
418 X_ATTRIBUTE5 => l_org_freight_tl_info.ATTRIBUTE5,
419 X_ATTRIBUTE6 => l_org_freight_tl_info.ATTRIBUTE6,
420 X_ATTRIBUTE7 => l_org_freight_tl_info.ATTRIBUTE7,
421 X_ATTRIBUTE8 => l_org_freight_tl_info.ATTRIBUTE8,
422 X_ATTRIBUTE9 => l_org_freight_tl_info.ATTRIBUTE9,
423 X_ATTRIBUTE10 => l_org_freight_tl_info.ATTRIBUTE10,
424 X_ATTRIBUTE11 => l_org_freight_tl_info.ATTRIBUTE11,
425 X_ATTRIBUTE12 => l_org_freight_tl_info.ATTRIBUTE12,
426 X_ATTRIBUTE13 => l_org_freight_tl_info.ATTRIBUTE13,
427 X_ATTRIBUTE14 => l_org_freight_tl_info.ATTRIBUTE14,
428 X_ATTRIBUTE15 => l_org_freight_tl_info.ATTRIBUTE15,
429 X_GLOBAL_ATTRIBUTE1 => l_org_freight_tl_info.GLOBAL_ATTRIBUTE1,
430 X_GLOBAL_ATTRIBUTE2 => l_org_freight_tl_info.GLOBAL_ATTRIBUTE2,
431 X_GLOBAL_ATTRIBUTE3 => l_org_freight_tl_info.GLOBAL_ATTRIBUTE3,
432 X_GLOBAL_ATTRIBUTE4 => l_org_freight_tl_info.GLOBAL_ATTRIBUTE4,
433 X_GLOBAL_ATTRIBUTE5 => l_org_freight_tl_info.GLOBAL_ATTRIBUTE5,
434 X_GLOBAL_ATTRIBUTE6 => l_org_freight_tl_info.GLOBAL_ATTRIBUTE6,
435 X_GLOBAL_ATTRIBUTE7 => l_org_freight_tl_info.GLOBAL_ATTRIBUTE7,
436 X_GLOBAL_ATTRIBUTE8 => l_org_freight_tl_info.GLOBAL_ATTRIBUTE8,
437 X_GLOBAL_ATTRIBUTE9 => l_org_freight_tl_info.GLOBAL_ATTRIBUTE9,
438 X_GLOBAL_ATTRIBUTE10 => l_org_freight_tl_info.GLOBAL_ATTRIBUTE10,
439 X_GLOBAL_ATTRIBUTE11 => l_org_freight_tl_info.GLOBAL_ATTRIBUTE11,
440 X_GLOBAL_ATTRIBUTE12 => l_org_freight_tl_info.GLOBAL_ATTRIBUTE12,
441 X_GLOBAL_ATTRIBUTE13 => l_org_freight_tl_info.GLOBAL_ATTRIBUTE13,
442 X_GLOBAL_ATTRIBUTE14 => l_org_freight_tl_info.GLOBAL_ATTRIBUTE14,
443 X_GLOBAL_ATTRIBUTE15 => l_org_freight_tl_info.GLOBAL_ATTRIBUTE15,
444 X_GLOBAL_ATTRIBUTE16 => l_org_freight_tl_info.GLOBAL_ATTRIBUTE16,
445 X_GLOBAL_ATTRIBUTE17 => l_org_freight_tl_info.GLOBAL_ATTRIBUTE17,
446 X_GLOBAL_ATTRIBUTE18 => l_org_freight_tl_info.GLOBAL_ATTRIBUTE18,
447 X_GLOBAL_ATTRIBUTE19 => l_org_freight_tl_info.GLOBAL_ATTRIBUTE19,
448 X_GLOBAL_ATTRIBUTE20 => l_org_freight_tl_info.GLOBAL_ATTRIBUTE20,
449 X_GLOBAL_ATTRIBUTE_CATEGORY => l_org_freight_tl_info.GLOBAL_ATTRIBUTE_CATEGORY,
450 X_DESCRIPTION =>substrb(p_carrier_info.P_CARRIER_NAME,1,80),
451 X_LAST_UPDATE_DATE => SYSDATE, -- Bug 5478419
452 X_LAST_UPDATED_BY => p_carrier_info.LAST_UPDATED_BY,
453 X_LAST_UPDATE_LOGIN => p_carrier_info.LAST_UPDATE_LOGIN);
454
455 L_POSITION := 50;
456 L_PROCEDURE := 'Updating ORG_FREIGHT_TL';
457
458 --
459 IF l_debug_on THEN
460 WSH_DEBUG_SV.logmsg(l_module_name,'Updating ORG_FREIGHT_TL',WSH_DEBUG_SV.C_PROC_LEVEL);
461 END IF;
462 --
463
464 UPDATE ORG_FREIGHT_TL
465 SET party_id = p_csm_info.carrier_id
466 WHERE freight_code = p_carrier_info.p_freight_code
467 and organization_id = p_org_carrier_service_info.organization_id;
468 IF l_debug_on THEN
469 WSH_DEBUG_SV.log(l_module_name,'Update with carrier_id '||p_csm_info.carrier_id,SQL%ROWCOUNT);
470 END IF;
471
472 IF (SQL%NOTFOUND) THEN
473 RAISE NO_DATA_FOUND;
474 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
475 END IF;
476
477 END IF; -- End Update Allowled
478
479 END IF;
480 IF FND_API.To_Boolean(p_commit) THEN
481 COMMIT;
482 END IF;
483
484 IF l_debug_on THEN
485 WSH_DEBUG_SV.log(l_module_name,'COMMIT');
486 WSH_DEBUG_SV.pop(l_module_name);
487 END IF;
488 --
489 EXCEPTION
490 WHEN NO_DATA_FOUND THEN
491 x_exception_msg := 'EXCEPTION : No Data Found';
492 x_position := l_position;
493 x_procedure := l_procedure;
494 x_sqlerr := sqlerrm;
495 x_sql_code := sqlcode;
496 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
497 --
498 IF l_debug_on THEN
499 WSH_DEBUG_SV.logmsg(l_module_name,'NO_DATA_FOUND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
500 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
501 END IF;
502 --
503 WHEN Failed_In_CSM THEN
504 x_exception_msg := 'EXCEPTION: Failed in CSM';
505 x_position := l_position;
506 x_procedure := l_procedure;
507 x_sqlerr := sqlerrm;
508 x_sql_code := sqlcode;
509 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
510 --
511 IF l_debug_on THEN
512 WSH_DEBUG_SV.logmsg(l_module_name,'FAILED_IN_CSM exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
513 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FAILED_IN_CSM');
514 END IF;
515 --
516 WHEN OTHERS THEN
517 x_exception_msg := 'EXCEPTION : Others';
518 x_position := l_position;
519 x_procedure := l_procedure;
520 x_sqlerr := sqlerrm;
521 x_sql_code := sqlcode;
522 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
523 --
524 IF l_debug_on THEN
525 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
526 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
527 END IF;
528 --
529 END Assign_Org_Carrier_Service;
530
531 PROCEDURE Lock_Org_Carrier_Service (
532 p_rowid IN VARCHAR2
533 , p_org_Carrier_Service_Info IN OCSRecType
534 , x_Return_Status OUT NOCOPY VARCHAR2
535 )
536 IS
537
538 CURSOR C_lock_row IS
539 SELECT *
540 FROM wsh_Org_Carrier_Services
541 WHERE rowid = p_rowid
542 FOR UPDATE of Org_Carrier_Service_id NOWAIT;
543
544 Recinfo C_lock_row%ROWTYPE;
545
546 others Exception;
547
548 --
549 l_debug_on BOOLEAN;
550 --
551 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_ORG_CARRIER_SERVICE';
552 --
553 BEGIN
554 --
555 --
556 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
557 --
558 IF l_debug_on IS NULL
559 THEN
560 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
561 END IF;
562 --
563 IF l_debug_on THEN
564 WSH_DEBUG_SV.push(l_module_name);
565 --
566 WSH_DEBUG_SV.log(l_module_name,'P_ROWID',P_ROWID);
567 END IF;
568 --
569 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
570 IF (p_rowid is not null) THEN
571 OPEN C_lock_row;
572 FETCH C_lock_row INTO Recinfo;
573
574 IF (C_lock_row%NOTFOUND) THEN
575 CLOSE C_lock_row;
576 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
577 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
578 WSH_UTIL_CORE.Add_Message(x_return_status,l_module_name);
579 --
580 IF l_debug_on THEN
581 WSH_DEBUG_SV.pop(l_module_name,'x_return_status');
582 END IF;
583 --
584 RETURN;
585 END IF;
586 CLOSE C_lock_row;
587
588 IF ( (Recinfo.Enabled_Flag = p_org_Carrier_Service_Info.Enabled_Flag)
589 AND ( (Recinfo.Attribute_Category = p_org_Carrier_Service_Info.Attribute_Category)
590 OR ( (Recinfo.Attribute_Category is NULL)
591 AND (p_org_Carrier_Service_Info.Attribute_Category IS NULL)))
592 AND ( (Recinfo.Attribute1 = p_org_Carrier_Service_Info.Attribute1)
593 OR ( (Recinfo.Attribute1 IS NULL)
594 AND (p_org_Carrier_Service_Info.Attribute1 is NULL)))
595 AND ( (Recinfo.Attribute2 = p_org_Carrier_Service_Info.Attribute2)
596 OR ( (Recinfo.Attribute2 IS NULL)
597 AND (p_org_Carrier_Service_Info.Attribute2 is NULL)))
598 AND ( (Recinfo.Attribute3 = p_org_Carrier_Service_Info.Attribute3)
599 OR ( (Recinfo.Attribute3 IS NULL)
600 AND (p_org_Carrier_Service_Info.Attribute3 is NULL)))
601 AND ( (Recinfo.Attribute4 = p_org_Carrier_Service_Info.Attribute4)
602 OR ( (Recinfo.Attribute4 IS NULL)
603 AND (p_org_Carrier_Service_Info.Attribute4 is NULL)))
604 AND ( (Recinfo.Attribute5 = p_org_Carrier_Service_Info.Attribute5)
605 OR ( (Recinfo.Attribute5 IS NULL)
606 AND (p_org_Carrier_Service_Info.Attribute5 is NULL)))
607 AND ( (Recinfo.Attribute6 = p_org_Carrier_Service_Info.Attribute6)
608 OR ( (Recinfo.Attribute6 IS NULL)
609 AND (p_org_Carrier_Service_Info.Attribute6 is NULL)))
610 AND ( (Recinfo.Attribute7 = p_org_Carrier_Service_Info.Attribute7)
611 OR ( (Recinfo.Attribute7 IS NULL)
612 AND (p_org_Carrier_Service_Info.Attribute7 is NULL)))
613 AND ( (Recinfo.Attribute8 = p_org_Carrier_Service_Info.Attribute8)
614 OR ( (Recinfo.Attribute8 IS NULL)
615 AND (p_org_Carrier_Service_Info.Attribute8 is NULL)))
616 AND ( (Recinfo.Attribute9 = p_org_Carrier_Service_Info.Attribute9)
617 OR ( (Recinfo.Attribute9 IS NULL)
618 AND (p_org_Carrier_Service_Info.Attribute9 is NULL)))
619 AND ( (Recinfo.Attribute10 = p_org_Carrier_Service_Info.Attribute10)
620 OR ( (Recinfo.Attribute10 IS NULL)
621 AND (p_org_Carrier_Service_Info.Attribute10 is NULL)))
622 AND ( (Recinfo.Attribute11 = p_org_Carrier_Service_Info.Attribute11)
623 OR ( (Recinfo.Attribute11 IS NULL)
624 AND (p_org_Carrier_Service_Info.Attribute11 is NULL)))
625 AND ( (Recinfo.Attribute12 = p_org_Carrier_Service_Info.Attribute12)
626 OR ( (Recinfo.Attribute12 IS NULL)
627 AND (p_org_Carrier_Service_Info.Attribute12 is NULL)))
628 AND ( (Recinfo.Attribute13 = p_org_Carrier_Service_Info.Attribute13)
629 OR ( (Recinfo.Attribute13 IS NULL)
630 AND (p_org_Carrier_Service_Info.Attribute13 is NULL)))
631 AND ( (Recinfo.Attribute14 = p_org_Carrier_Service_Info.Attribute14)
632 OR ( (Recinfo.Attribute14 IS NULL)
633 AND (p_org_Carrier_Service_Info.Attribute14 is NULL)))
634 AND ( (Recinfo.Attribute15 = p_org_Carrier_Service_Info.Attribute15)
635 OR ( (Recinfo.Attribute15 IS NULL)
636 AND (p_org_Carrier_Service_Info.Attribute15 is NULL)))
637 ) THEN
638 --
639 IF l_debug_on THEN
640 WSH_DEBUG_SV.pop(l_module_name,'Nothing changed');
641 END IF;
642 --
643 RETURN;
644 ELSE
645 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
646 IF l_debug_on THEN
647 WSH_DEBUG_SV.log(l_module_name,'FORM_RECORD_CHANGED');
648 END IF;
649 APP_EXCEPTION.Raise_Exception;
650 END IF;
651 END IF;
652 --
653 IF l_debug_on THEN
654 WSH_DEBUG_SV.pop(l_module_name);
655 END IF;
656 --
657 EXCEPTION
658 WHEN others THEN
659 x_Return_Status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
660 WSH_UTIL_CORE.Default_Handler('WSH_org_Carrier_ServiceS_PKG.LockOrg_Carrier_Service',l_module_name);
661 --
662 IF l_debug_on THEN
663 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
664 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
665 END IF;
666 --
667 END Lock_Org_Carrier_Service;
668
669 END WSH_ORG_CARRIER_SERVICES_PKG;