[Home] [Help]
PACKAGE BODY: APPS.WSH_DEPARTURES_PKG
Source
1 PACKAGE BODY WSH_DEPARTURES_PKG as
2 /* $Header: WSHDEPHB.pls 115.2 99/07/16 08:18:42 porting ship $ */
3
4 -- The procedure insert_row can possibly return 2 kinds of exceptions
5 -- 1. WSH_DEP_DUP_NAME : This the for the case that default departure name
6 -- generated by wsh_external_custom.departure_name
7 -- is duplicate in table wsh_departures
8 -- 2. WSH_DEP_NULL_NAME : This is for the case that default departure name
9 -- generated by wsh_external_custom.departure_name
10 -- is NULL.
11 PROCEDURE Insert_Row(X_Rowid IN OUT VARCHAR2,
12 X_Organization_Id NUMBER,
13 X_Departure_Id IN OUT NUMBER,
14 X_Name IN OUT VARCHAR2,
15 X_Source_Code VARCHAR2,
16 X_Arrive_After_Departure_Id NUMBER,
17 X_Status_Code VARCHAR2,
18 X_Report_Set_Id NUMBER,
19 X_Date_Closed DATE,
20 X_Vehicle_Item_Id NUMBER,
21 X_Vehicle_Number VARCHAR2,
22 X_Freight_Carrier_Code VARCHAR2,
23 X_Planned_Departure_Date DATE,
24 X_Actual_Departure_Date DATE,
25 X_Bill_Of_Lading VARCHAR2,
26 X_Gross_Weight NUMBER,
27 X_Net_Weight NUMBER,
28 X_Weight_Uom_Code VARCHAR2,
29 X_Volume NUMBER,
30 X_Volume_Uom_Code VARCHAR2,
31 X_Fill_Percent NUMBER,
32 X_Seal_Code VARCHAR2,
33 X_Routing_Instructions VARCHAR2,
34 X_Attribute_Category VARCHAR2,
35 X_Attribute1 VARCHAR2,
36 X_Attribute2 VARCHAR2,
37 X_Attribute3 VARCHAR2,
38 X_Attribute4 VARCHAR2,
39 X_Attribute5 VARCHAR2,
40 X_Attribute6 VARCHAR2,
41 X_Attribute7 VARCHAR2,
42 X_Attribute8 VARCHAR2,
43 X_Attribute9 VARCHAR2,
44 X_Attribute10 VARCHAR2,
45 X_Attribute11 VARCHAR2,
46 X_Attribute12 VARCHAR2,
47 X_Attribute13 VARCHAR2,
48 X_Attribute14 VARCHAR2,
49 X_Attribute15 VARCHAR2,
50 X_Creation_Date DATE,
51 X_Created_By NUMBER,
52 X_Last_Update_Date DATE,
53 X_Last_Updated_By NUMBER,
54 X_Last_Update_Login NUMBER
55 ) IS
56 CURSOR C IS SELECT rowid FROM wsh_departures
57 WHERE departure_id = X_Departure_Id;
58 CURSOR C2 IS SELECT wsh_departures_s.nextval FROM sys.dual;
59 dname wsh_departures.name%TYPE;
60 CURSOR C3 (dep_name VARCHAR2) IS
61 SELECT COUNT(*) FROM wsh_departures
62 WHERE name = dep_name;
63 temp NUMBER;
64 temp_id NUMBER;
65 duplicate_name EXCEPTION;
66 null_name EXCEPTION;
67 BEGIN
68 if (X_Departure_Id is NULL) then
69 OPEN C2;
70 FETCH C2 INTO X_Departure_Id;
71 CLOSE C2;
72 end if;
73
74 IF (X_Name IS NULL) THEN
75 dname := wsh_external_custom.departure_name(X_Departure_Id);
76
77 IF ( dname IS NULL ) THEN
78 RAISE null_name;
79 -- shipping default make sure the departure name is not duplicate
80 ELSIF ( dname = TO_CHAR(X_Departure_Id) ) THEN
81 temp_id := X_Departure_Id;
82 OPEN C2;
83
84 LOOP
85 dname := TO_CHAR(temp_id);
86 OPEN C3( dname);
87 FETCH C3 INTO temp;
88 CLOSE C3;
89 IF ( temp = 0 ) THEN
90 EXIT;
91 END IF;
92
93 FETCH C2 INTO temp_id;
94 END LOOP;
95 CLOSE C2;
96 X_Departure_Id := temp_id;
97 ELSE -- name is generated from wsh_external_custom.departure_name
98 OPEN C3( dname);
99 FETCH C3 INTO temp;
100 CLOSE C3;
101 IF ( temp > 0 ) THEN
102 RAISE duplicate_name;
103 END IF;
104
105 END IF;
106 X_Name := dname;
107 END IF;
108
109 INSERT INTO wsh_departures(
110 organization_id,
111 departure_id,
112 name,
113 source_code,
114 arrive_after_departure_id,
115 status_code,
116 report_set_id,
117 date_closed,
118 vehicle_item_id,
119 vehicle_number,
120 freight_carrier_code,
121 planned_departure_date,
122 actual_departure_date,
123 bill_of_lading,
124 gross_weight,
125 net_weight,
126 weight_uom_code,
127 volume,
128 volume_uom_code,
129 fill_percent,
130 seal_code,
131 routing_instructions,
132 attribute_category,
133 attribute1,
134 attribute2,
135 attribute3,
136 attribute4,
137 attribute5,
138 attribute6,
139 attribute7,
140 attribute8,
141 attribute9,
142 attribute10,
143 attribute11,
144 attribute12,
145 attribute13,
146 attribute14,
147 attribute15,
148 creation_date,
149 created_by,
150 last_update_date,
151 last_updated_by,
152 last_update_login
153 ) VALUES (
154 X_Organization_Id,
155 X_Departure_Id,
156 X_Name,
157 X_Source_Code,
158 X_Arrive_After_Departure_Id,
159 X_Status_Code,
160 X_Report_Set_Id,
161 X_Date_Closed,
162 X_Vehicle_Item_Id,
163 X_Vehicle_Number,
164 X_Freight_Carrier_Code,
165 X_Planned_Departure_Date,
166 X_Actual_Departure_Date,
167 X_Bill_Of_Lading,
168 X_Gross_Weight,
169 X_Net_Weight,
170 X_Weight_Uom_Code,
171 X_Volume,
172 X_Volume_Uom_Code,
173 X_Fill_Percent,
174 X_Seal_Code,
175 X_Routing_Instructions,
176 X_Attribute_Category,
177 X_Attribute1,
178 X_Attribute2,
179 X_Attribute3,
180 X_Attribute4,
181 X_Attribute5,
182 X_Attribute6,
183 X_Attribute7,
184 X_Attribute8,
185 X_Attribute9,
186 X_Attribute10,
187 X_Attribute11,
188 X_Attribute12,
189 X_Attribute13,
190 X_Attribute14,
191 X_Attribute15,
192 X_Creation_Date,
193 X_Created_By,
194 X_Last_Update_Date,
195 X_Last_Updated_By,
196 X_Last_Update_Login
197 );
198
199 OPEN C;
200 FETCH C INTO X_Rowid;
201 if (C%NOTFOUND) then
202 CLOSE C;
203 Raise NO_DATA_FOUND;
204 end if;
205 CLOSE C;
206 EXCEPTION
207 WHEN duplicate_name THEN
208 FND_MESSAGE.Set_Name('OE', 'WSH_DEP_DUP_NAME');
209 APP_EXCEPTION.Raise_Exception;
210 WHEN null_name THEN
211 FND_MESSAGE.Set_Name('OE', 'WSH_DEP_NULL_NAME');
212 APP_EXCEPTION.Raise_Exception;
213 END Insert_Row;
214
215
216 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
217 X_Organization_Id NUMBER,
218 X_Departure_Id NUMBER,
219 X_Name VARCHAR2,
220 X_Source_Code VARCHAR2,
221 X_Arrive_After_Departure_Id NUMBER,
222 X_Status_Code VARCHAR2,
223 X_Report_Set_Id NUMBER,
224 X_Date_Closed DATE,
225 X_Vehicle_Item_Id NUMBER,
226 X_Vehicle_Number VARCHAR2,
227 X_Freight_Carrier_Code VARCHAR2,
228 X_Planned_Departure_Date DATE,
229 X_Actual_Departure_Date DATE,
230 X_Bill_Of_Lading VARCHAR2,
231 X_Gross_Weight NUMBER,
232 X_Net_Weight NUMBER,
233 X_Weight_Uom_Code VARCHAR2,
234 X_Volume NUMBER,
235 X_Volume_Uom_Code VARCHAR2,
236 X_Fill_Percent NUMBER,
237 X_Seal_Code VARCHAR2,
238 X_Routing_Instructions VARCHAR2,
239 X_Attribute_Category VARCHAR2,
240 X_Attribute1 VARCHAR2,
241 X_Attribute2 VARCHAR2,
242 X_Attribute3 VARCHAR2,
243 X_Attribute4 VARCHAR2,
244 X_Attribute5 VARCHAR2,
245 X_Attribute6 VARCHAR2,
246 X_Attribute7 VARCHAR2,
247 X_Attribute8 VARCHAR2,
248 X_Attribute9 VARCHAR2,
249 X_Attribute10 VARCHAR2,
250 X_Attribute11 VARCHAR2,
251 X_Attribute12 VARCHAR2,
252 X_Attribute13 VARCHAR2,
253 X_Attribute14 VARCHAR2,
254 X_Attribute15 VARCHAR2
255 ) IS
256 CURSOR C IS
257 SELECT *
258 FROM wsh_departures
259 WHERE rowid = X_Rowid
260 FOR UPDATE of Departure_Id NOWAIT;
261 Recinfo C%ROWTYPE;
262
263
264 BEGIN
265 OPEN C;
266 FETCH C INTO Recinfo;
267 if (C%NOTFOUND) then
268 CLOSE C;
269 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
270 APP_EXCEPTION.Raise_Exception;
271 end if;
272 CLOSE C;
273 if (
274 (Recinfo.organization_id = X_Organization_Id)
275 AND (Recinfo.departure_id = X_Departure_Id)
276 AND (Recinfo.name = X_Name)
277 AND (Recinfo.source_code = X_Source_Code)
278 AND ( (Recinfo.arrive_after_departure_id = X_Arrive_After_Departure_Id)
279 OR ( (Recinfo.arrive_after_departure_id IS NULL)
280 AND (X_Arrive_After_Departure_Id IS NULL)))
281 AND (Recinfo.status_code = X_Status_Code)
282 AND ( (Recinfo.report_set_id = X_Report_Set_Id)
283 OR ( (Recinfo.report_set_id IS NULL)
284 AND (X_Report_Set_Id IS NULL)))
285 AND ( (Recinfo.date_closed = X_Date_Closed)
286 OR ( (Recinfo.date_closed IS NULL)
287 AND (X_Date_Closed IS NULL)))
288 AND ( (Recinfo.vehicle_item_id = X_Vehicle_Item_Id)
289 OR ( (Recinfo.vehicle_item_id IS NULL)
290 AND (X_Vehicle_Item_Id IS NULL)))
291 AND ( (Recinfo.vehicle_number = X_Vehicle_Number)
292 OR ( (Recinfo.vehicle_number IS NULL)
293 AND (X_Vehicle_Number IS NULL)))
294 AND ( (Recinfo.freight_carrier_code = X_Freight_Carrier_Code)
295 OR ( (Recinfo.freight_carrier_code IS NULL)
296 AND (X_Freight_Carrier_Code IS NULL)))
297 AND ( (Recinfo.planned_departure_date = X_Planned_Departure_Date)
298 OR ( (Recinfo.planned_departure_date IS NULL)
299 AND (X_Planned_Departure_Date IS NULL)))
300 AND ( (Recinfo.actual_departure_date = X_Actual_Departure_Date)
301 OR ( (Recinfo.actual_departure_date IS NULL)
302 AND (X_Actual_Departure_Date IS NULL)))
303 AND ( (Recinfo.bill_of_lading = X_Bill_Of_Lading)
304 OR ( (Recinfo.bill_of_lading IS NULL)
305 AND (X_Bill_Of_Lading IS NULL)))
306 AND ( (Recinfo.gross_weight = X_Gross_Weight)
307 OR ( (Recinfo.gross_weight IS NULL)
308 AND (X_Gross_Weight IS NULL)))
309 AND ( (Recinfo.net_weight = X_Net_Weight)
310 OR ( (Recinfo.net_weight IS NULL)
311 AND (X_Net_Weight IS NULL)))
312 AND ( (Recinfo.weight_uom_code = X_Weight_Uom_Code)
313 OR ( (Recinfo.weight_uom_code IS NULL)
314 AND (X_Weight_Uom_Code IS NULL)))
315 AND ( (Recinfo.volume = X_Volume)
316 OR ( (Recinfo.volume IS NULL)
317 AND (X_Volume IS NULL)))
318 AND ( (Recinfo.volume_uom_code = X_Volume_Uom_Code)
319 OR ( (Recinfo.volume_uom_code IS NULL)
320 AND (X_Volume_Uom_Code IS NULL)))
321 AND ( (Recinfo.fill_percent = X_Fill_Percent)
322 OR ( (Recinfo.fill_percent IS NULL)
323 AND (X_Fill_Percent IS NULL)))
324 AND ( (Recinfo.seal_code = X_Seal_Code)
325 OR ( (Recinfo.seal_code IS NULL)
326 AND (X_Seal_Code IS NULL)))
327 AND ( (Recinfo.routing_instructions = X_Routing_Instructions)
328 OR ( (Recinfo.routing_instructions IS NULL)
329 AND (X_Routing_Instructions IS NULL)))
330 AND ( (Recinfo.attribute_category = X_Attribute_Category)
331 OR ( (Recinfo.attribute_category IS NULL)
332 AND (X_Attribute_Category IS NULL)))
336 AND ( (Recinfo.attribute2 = X_Attribute2)
333 AND ( (Recinfo.attribute1 = X_Attribute1)
334 OR ( (Recinfo.attribute1 IS NULL)
335 AND (X_Attribute1 IS NULL)))
337 OR ( (Recinfo.attribute2 IS NULL)
338 AND (X_Attribute2 IS NULL)))
339 AND ( (Recinfo.attribute3 = X_Attribute3)
340 OR ( (Recinfo.attribute3 IS NULL)
341 AND (X_Attribute3 IS NULL)))
342 AND ( (Recinfo.attribute4 = X_Attribute4)
343 OR ( (Recinfo.attribute4 IS NULL)
344 AND (X_Attribute4 IS NULL)))
345 AND ( (Recinfo.attribute5 = X_Attribute5)
346 OR ( (Recinfo.attribute5 IS NULL)
347 AND (X_Attribute5 IS NULL)))
348 AND ( (Recinfo.attribute6 = X_Attribute6)
349 OR ( (Recinfo.attribute6 IS NULL)
350 AND (X_Attribute6 IS NULL)))
351 AND ( (Recinfo.attribute7 = X_Attribute7)
352 OR ( (Recinfo.attribute7 IS NULL)
353 AND (X_Attribute7 IS NULL)))
354 AND ( (Recinfo.attribute8 = X_Attribute8)
355 OR ( (Recinfo.attribute8 IS NULL)
356 AND (X_Attribute8 IS NULL)))
357 AND ( (Recinfo.attribute9 = X_Attribute9)
358 OR ( (Recinfo.attribute9 IS NULL)
359 AND (X_Attribute9 IS NULL)))
360 AND ( (Recinfo.attribute10 = X_Attribute10)
361 OR ( (Recinfo.attribute10 IS NULL)
362 AND (X_Attribute10 IS NULL)))
363 AND ( (Recinfo.attribute11 = X_Attribute11)
364 OR ( (Recinfo.attribute11 IS NULL)
365 AND (X_Attribute11 IS NULL)))
366 AND ( (Recinfo.attribute12 = X_Attribute12)
367 OR ( (Recinfo.attribute12 IS NULL)
368 AND (X_Attribute12 IS NULL)))
369 AND ( (Recinfo.attribute13 = X_Attribute13)
370 OR ( (Recinfo.attribute13 IS NULL)
371 AND (X_Attribute13 IS NULL)))
372 AND ( (Recinfo.attribute14 = X_Attribute14)
373 OR ( (Recinfo.attribute14 IS NULL)
374 AND (X_Attribute14 IS NULL)))
375 AND ( (Recinfo.attribute15 = X_Attribute15)
376 OR ( (Recinfo.attribute15 IS NULL)
377 AND (X_Attribute15 IS NULL)))
378 ) then
379 return;
380 else
381 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
382 APP_EXCEPTION.Raise_Exception;
383 end if;
384 END Lock_Row;
385
386
387
388 PROCEDURE Update_Row(X_Rowid VARCHAR2,
389 X_Organization_Id NUMBER,
390 X_Departure_Id NUMBER,
391 X_Name IN OUT VARCHAR2,
392 X_Source_Code VARCHAR2,
393 X_Arrive_After_Departure_Id NUMBER,
394 X_Status_Code VARCHAR2,
395 X_Report_Set_Id NUMBER,
396 X_Date_Closed DATE,
397 X_Vehicle_Item_Id NUMBER,
398 X_Vehicle_Number VARCHAR2,
399 X_Freight_Carrier_Code VARCHAR2,
400 X_Planned_Departure_Date DATE,
401 X_Actual_Departure_Date DATE,
402 X_Bill_Of_Lading VARCHAR2,
403 X_Gross_Weight NUMBER,
404 X_Net_Weight NUMBER,
405 X_Weight_Uom_Code VARCHAR2,
406 X_Volume NUMBER,
407 X_Volume_Uom_Code VARCHAR2,
408 X_Fill_Percent NUMBER,
409 X_Seal_Code VARCHAR2,
410 X_Routing_Instructions VARCHAR2,
411 X_Attribute_Category VARCHAR2,
412 X_Attribute1 VARCHAR2,
413 X_Attribute2 VARCHAR2,
414 X_Attribute3 VARCHAR2,
415 X_Attribute4 VARCHAR2,
416 X_Attribute5 VARCHAR2,
417 X_Attribute6 VARCHAR2,
418 X_Attribute7 VARCHAR2,
419 X_Attribute8 VARCHAR2,
420 X_Attribute9 VARCHAR2,
421 X_Attribute10 VARCHAR2,
422 X_Attribute11 VARCHAR2,
423 X_Attribute12 VARCHAR2,
424 X_Attribute13 VARCHAR2,
425 X_Attribute14 VARCHAR2,
426 X_Attribute15 VARCHAR2,
427 X_Last_Update_Date DATE,
428 X_Last_Updated_By NUMBER,
429 X_Last_Update_Login NUMBER
430 ) IS
431 dname wsh_departures.name%TYPE;
432 CURSOR C2 IS SELECT wsh_departures_s.nextval FROM sys.dual;
433 CURSOR C3 (dep_name VARCHAR2) IS
434 SELECT COUNT(*) FROM wsh_departures
435 WHERE name = dep_name;
436 temp NUMBER;
437 temp_id NUMBER;
438 duplicate_name EXCEPTION;
439 null_name EXCEPTION;
440
441 BEGIN
442
446 IF ( dname IS NULL ) THEN
443 IF (X_Name IS NULL) THEN
444 dname := wsh_external_custom.departure_name(X_Departure_Id);
445
447 RAISE null_name;
448 -- shipping default make sure the departure name is not duplicate
449 ELSIF ( dname = TO_CHAR(X_Departure_Id) ) THEN
450 temp_id := X_Departure_Id;
451 OPEN C2;
452
453 LOOP
454 dname := TO_CHAR(temp_id);
455 OPEN C3( dname);
456 FETCH C3 INTO temp;
457 CLOSE C3;
458 IF ( temp = 0 ) THEN
459 EXIT;
460 END IF;
461
462 FETCH C2 INTO temp_id;
463 END LOOP;
464 CLOSE C2;
465 ELSE -- name is generated from wsh_external_custom.departure_name
466 OPEN C3( dname);
467 FETCH C3 INTO temp;
468 CLOSE C3;
469 IF ( temp > 0 ) THEN
470 RAISE duplicate_name;
471 END IF;
472
473 END IF;
474 X_Name := NULL;
475 END IF;
476
477 UPDATE wsh_departures
478 SET
479 organization_id = X_Organization_Id,
480 departure_id = X_Departure_Id,
481 name = X_Name,
482 source_code = X_Source_Code,
483 arrive_after_departure_id = X_Arrive_After_Departure_Id,
484 status_code = X_Status_Code,
485 report_set_id = X_Report_Set_Id,
486 date_closed = X_Date_Closed,
487 vehicle_item_id = X_Vehicle_Item_Id,
488 vehicle_number = X_Vehicle_Number,
489 freight_carrier_code = X_Freight_Carrier_Code,
490 planned_departure_date = X_Planned_Departure_Date,
491 actual_departure_date = X_Actual_Departure_Date,
492 bill_of_lading = X_Bill_Of_Lading,
493 gross_weight = X_Gross_Weight,
494 net_weight = X_Net_Weight,
495 weight_uom_code = X_Weight_Uom_Code,
496 volume = X_Volume,
497 volume_uom_code = X_Volume_Uom_Code,
498 fill_percent = X_Fill_Percent,
499 seal_code = X_Seal_Code,
500 routing_instructions = X_Routing_Instructions,
501 attribute_category = X_Attribute_Category,
502 attribute1 = X_Attribute1,
503 attribute2 = X_Attribute2,
504 attribute3 = X_Attribute3,
505 attribute4 = X_Attribute4,
506 attribute5 = X_Attribute5,
507 attribute6 = X_Attribute6,
508 attribute7 = X_Attribute7,
509 attribute8 = X_Attribute8,
510 attribute9 = X_Attribute9,
511 attribute10 = X_Attribute10,
512 attribute11 = X_Attribute11,
513 attribute12 = X_Attribute12,
514 attribute13 = X_Attribute13,
515 attribute14 = X_Attribute14,
516 attribute15 = X_Attribute15,
517 last_update_date = X_Last_Update_Date,
518 last_updated_by = X_Last_Updated_By,
519 last_update_login = X_Last_Update_Login
520 WHERE rowid = X_Rowid;
521
522 if (SQL%NOTFOUND) then
523 Raise NO_DATA_FOUND;
524 end if;
525
526 EXCEPTION
527 WHEN duplicate_name THEN
528 FND_MESSAGE.Set_Name('OE', 'WSH_DEP_DUP_NAME');
529 APP_EXCEPTION.Raise_Exception;
530 WHEN null_name THEN
531 FND_MESSAGE.Set_Name('OE', 'WSH_DEP_NULL_NAME');
532 APP_EXCEPTION.Raise_Exception;
533 END Update_Row;
534
535 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
536 BEGIN
537 DELETE FROM wsh_departures
538 WHERE rowid = X_Rowid;
539
540 if (SQL%NOTFOUND) then
541 Raise NO_DATA_FOUND;
542 end if;
543 END Delete_Row;
544
545
546 END WSH_DEPARTURES_PKG;