[Home] [Help]
PACKAGE BODY: APPS.WSH_DELIVERIES_PKG1
Source
1 PACKAGE BODY WSH_DELIVERIES_PKG1 as
2 /* $Header: WSHDELXB.pls 115.3 99/08/11 19:22:58 porting s $ */
3
4 -- The procedure insert_row can possibly return 2 kinds of exceptions
5 -- 1. WSH_DEL_DUP_NAME : This the for the case that default delivery name
6 -- generated by wsh_external_custom.delivery_name
7 -- is duplicate in table wsh_deliveries
8 -- 2. WSH_DEL_NULL_NAME : This is for the case that default delivery name
9 -- generated by wsh_external_custom.delivery_name
10 -- is NULL.
11
12 PROCEDURE Insert_Row(X_Rowid IN OUT VARCHAR2,
13 X_Organization_Id NUMBER,
14 X_Delivery_Id IN OUT NUMBER,
15 X_Name IN OUT VARCHAR2,
16 X_Source_Code VARCHAR2,
17 X_Planned_Departure_Id NUMBER,
18 X_Actual_Departure_Id NUMBER,
19 X_Status_Code VARCHAR2,
20 X_Loading_Order_Flag VARCHAR2,
21 X_Date_Closed DATE,
22 X_Report_Set_Id NUMBER,
23 X_Sequence_Number NUMBER,
24 X_Customer_Id NUMBER,
25 X_Ultimate_Ship_To_Id NUMBER,
26 X_Intermediate_Ship_To_Id NUMBER,
27 X_Pooled_Ship_To_Id NUMBER,
28 X_Waybill VARCHAR2,
29 X_Gross_Weight NUMBER,
30 X_Weight_Uom_Code VARCHAR2,
31 X_Volume NUMBER,
32 X_Volume_Uom_Code VARCHAR2,
33 X_Picked_By_Id NUMBER,
34 X_Packed_By_Id NUMBER,
35 X_Expected_Arrival_Date DATE,
36 X_Asn_Date_Sent DATE,
37 X_Asn_Seq_Number NUMBER,
38 X_Freight_Carrier_Code VARCHAR2,
39 X_Freight_Terms_Code VARCHAR2,
40 X_Currency_Code VARCHAR2,
41 X_Fob_Code VARCHAR2,
42 X_Attribute_Category VARCHAR2,
43 X_Attribute1 VARCHAR2,
44 X_Attribute2 VARCHAR2,
45 X_Attribute3 VARCHAR2,
46 X_Attribute4 VARCHAR2,
47 X_Attribute5 VARCHAR2,
48 X_Attribute6 VARCHAR2,
49 X_Attribute7 VARCHAR2,
50 X_Attribute8 VARCHAR2,
51 X_Attribute9 VARCHAR2,
52 X_Attribute10 VARCHAR2,
53 X_Attribute11 VARCHAR2,
54 X_Attribute12 VARCHAR2,
55 X_Attribute13 VARCHAR2,
56 X_Attribute14 VARCHAR2,
57 X_Attribute15 VARCHAR2,
58 X_Global_Attribute_Category VARCHAR2 default null,
59 X_Global_Attribute1 VARCHAR2 default null,
60 X_Global_Attribute2 VARCHAR2 default null,
61 X_Global_Attribute3 VARCHAR2 default null,
62 X_Global_Attribute4 VARCHAR2 default null,
63 X_Global_Attribute5 VARCHAR2 default null,
64 X_Global_Attribute6 VARCHAR2 default null,
65 X_Global_Attribute7 VARCHAR2 default null,
66 X_Global_Attribute8 VARCHAR2 default null,
67 X_Global_Attribute9 VARCHAR2 default null,
68 X_Global_Attribute10 VARCHAR2 default null,
69 X_Global_Attribute11 VARCHAR2 default null,
70 X_Global_Attribute12 VARCHAR2 default null,
71 X_Global_Attribute13 VARCHAR2 default null,
72 X_Global_Attribute14 VARCHAR2 default null,
73 X_Global_Attribute15 VARCHAR2 default null,
74 X_Global_Attribute16 VARCHAR2 default null,
75 X_Global_Attribute17 VARCHAR2 default null,
76 X_Global_Attribute18 VARCHAR2 default null,
77 X_Global_Attribute19 VARCHAR2 default null,
78 X_Global_Attribute20 VARCHAR2 default null,
79 X_Creation_Date DATE,
80 X_Created_By NUMBER,
81 X_Last_Update_Date DATE,
82 X_Last_Updated_By NUMBER,
83 X_Last_Update_Login NUMBER
84 ) IS
85 CURSOR C IS SELECT rowid FROM wsh_deliveries
86 WHERE delivery_id = X_Delivery_Id;
87 CURSOR C2 IS SELECT wsh_deliveries_s.nextval FROM sys.dual;
88 dname wsh_deliveries.name%TYPE;
89 CURSOR C3 (del_name VARCHAR2) IS
90 SELECT COUNT(*) FROM wsh_deliveries
91 WHERE name = del_name;
92 temp NUMBER;
93 temp_id NUMBER;
94 duplicate_name EXCEPTION;
95 null_name EXCEPTION;
96 BEGIN
97 if (X_Delivery_Id is NULL) then
98 OPEN C2;
99 FETCH C2 INTO X_Delivery_Id;
100 CLOSE C2;
101 end if;
102
103 IF (X_Name IS NULL) THEN
104 dname := wsh_external_custom.delivery_name(X_Delivery_Id);
105
106 IF ( dname IS NULL ) THEN
107 RAISE null_name;
108 -- shipping default make sure the delivery name is not duplicate
109 ELSIF ( dname = TO_CHAR(X_Delivery_Id) ) THEN
110 temp_id := X_Delivery_Id;
111 OPEN C2;
112
113 LOOP
114 dname := TO_CHAR(temp_id);
115 OPEN C3( dname);
116 FETCH C3 INTO temp;
117 CLOSE C3;
118 IF ( temp = 0 ) THEN
119 EXIT;
120 END IF;
121
122 FETCH C2 INTO temp_id;
123 END LOOP;
124 IF C2%ISOPEN THEN
125 CLOSE C2;
126 END IF;
127
128 X_Delivery_Id := temp_id;
129 ELSE -- name is generated from wsh_external_custom.delivery_name
130 OPEN C3( dname);
131 FETCH C3 INTO temp;
132 CLOSE C3;
133 IF ( temp > 0 ) THEN
134 RAISE duplicate_name;
135 END IF;
136
137 END IF;
138 X_Name := dname;
139 END IF;
140
141 INSERT INTO wsh_deliveries(
142 organization_id,
143 delivery_id,
144 name,
145 source_code,
146 planned_departure_id,
147 actual_departure_id,
148 status_code,
149 loading_order_flag,
150 date_closed,
151 report_set_id,
152 sequence_number,
153 customer_id,
154 ultimate_ship_to_id,
155 intermediate_ship_to_id,
156 pooled_ship_to_id,
157 waybill,
158 gross_weight,
159 weight_uom_code,
160 volume,
161 volume_uom_code,
162 picked_by_id,
163 packed_by_id,
164 expected_arrival_date,
165 asn_date_sent,
166 asn_seq_number,
167 freight_carrier_code,
168 freight_terms_code,
169 currency_code,
170 fob_code,
171 attribute_category,
172 attribute1,
173 attribute2,
174 attribute3,
175 attribute4,
176 attribute5,
177 attribute6,
178 attribute7,
179 attribute8,
180 attribute9,
181 attribute10,
182 attribute11,
183 attribute12,
184 attribute13,
185 attribute14,
186 attribute15,
187 global_attribute_category,
188 global_attribute1,
189 global_attribute2,
190 global_attribute3,
191 global_attribute4,
192 global_attribute5,
193 global_attribute6,
194 global_attribute7,
195 global_attribute8,
196 global_attribute9,
197 global_attribute10,
198 global_attribute11,
199 global_attribute12,
200 global_attribute13,
201 global_attribute14,
202 global_attribute15,
203 global_attribute16,
204 global_attribute17,
205 global_attribute18,
206 global_attribute19,
207 global_attribute20,
208 creation_date,
209 created_by,
210 last_update_date,
211 last_updated_by,
212 last_update_login
213 ) VALUES (
214 X_Organization_Id,
215 X_Delivery_Id,
216 X_Name,
217 X_Source_Code,
218 X_Planned_Departure_Id,
219 X_Actual_Departure_Id,
220 X_Status_Code,
221 X_Loading_Order_Flag,
222 X_Date_Closed,
223 X_Report_Set_Id,
224 X_Sequence_Number,
225 X_Customer_Id,
226 X_Ultimate_Ship_To_Id,
227 X_Intermediate_Ship_To_Id,
228 X_Pooled_Ship_To_Id,
229 X_Waybill,
230 X_Gross_Weight,
231 X_Weight_Uom_Code,
232 X_Volume,
233 X_Volume_Uom_Code,
234 X_Picked_By_Id,
235 X_Packed_By_Id,
236 X_Expected_Arrival_Date,
237 X_Asn_Date_Sent,
238 X_Asn_Seq_Number,
239 X_Freight_Carrier_Code,
240 X_Freight_Terms_Code,
241 X_Currency_Code,
242 X_Fob_Code,
243 X_Attribute_Category,
244 X_Attribute1,
245 X_Attribute2,
246 X_Attribute3,
247 X_Attribute4,
248 X_Attribute5,
249 X_Attribute6,
250 X_Attribute7,
251 X_Attribute8,
252 X_Attribute9,
253 X_Attribute10,
254 X_Attribute11,
255 X_Attribute12,
256 X_Attribute13,
257 X_Attribute14,
258 X_Attribute15,
259 X_Global_Attribute_Category,
260 X_Global_Attribute1,
261 X_Global_Attribute2,
262 X_Global_Attribute3,
263 X_Global_Attribute4,
264 X_Global_Attribute5,
265 X_Global_Attribute6,
266 X_Global_Attribute7,
267 X_Global_Attribute8,
268 X_Global_Attribute9,
269 X_Global_Attribute10,
270 X_Global_Attribute11,
271 X_Global_Attribute12,
272 X_Global_Attribute13,
273 X_Global_Attribute14,
274 X_Global_Attribute15,
275 X_Global_Attribute16,
276 X_Global_Attribute17,
277 X_Global_Attribute18,
278 X_Global_Attribute19,
279 X_Global_Attribute20,
280 X_Creation_Date,
281 X_Created_By,
282 X_Last_Update_Date,
283 X_Last_Updated_By,
284 X_Last_Update_Login
285 );
286
287 OPEN C;
288 FETCH C INTO X_Rowid;
289 if (C%NOTFOUND) then
290 CLOSE C;
291 Raise NO_DATA_FOUND;
292 end if;
293 CLOSE C;
294 EXCEPTION
295 WHEN duplicate_name THEN
296 FND_MESSAGE.Set_Name('OE', 'WSH_DEL_DUP_NAME');
297 APP_EXCEPTION.Raise_Exception;
298 WHEN null_name THEN
299 FND_MESSAGE.Set_Name('OE', 'WSH_DEL_NULL_NAME');
300 APP_EXCEPTION.Raise_Exception;
301 END Insert_Row;
302
303
304 PROCEDURE Insert_Row_new(X_Rowid IN OUT VARCHAR2,
305 X_Organization_Id NUMBER,
306 X_Delivery_Id IN OUT NUMBER,
307 X_Name IN OUT VARCHAR2,
308 X_Source_Code VARCHAR2,
309 X_Planned_Departure_Id NUMBER,
310 X_Actual_Departure_Id NUMBER,
311 X_Status_Code VARCHAR2,
312 X_Loading_Order_Flag VARCHAR2,
313 X_Date_Closed DATE,
314 X_Report_Set_Id NUMBER,
315 X_Sequence_Number NUMBER,
316 X_Customer_Id NUMBER,
317 X_Ultimate_Ship_To_Id NUMBER,
318 X_Intermediate_Ship_To_Id NUMBER,
319 X_Pooled_Ship_To_Id NUMBER,
320 X_Waybill VARCHAR2,
321 X_Gross_Weight NUMBER,
322 X_Net_Weight NUMBER,
323 X_Weight_Uom_Code VARCHAR2,
324 X_Volume NUMBER,
325 X_Volume_Uom_Code VARCHAR2,
326 X_Picked_By_Id NUMBER,
327 X_Packed_By_Id NUMBER,
331 X_Freight_Carrier_Code VARCHAR2,
328 X_Expected_Arrival_Date DATE,
329 X_Asn_Date_Sent DATE,
330 X_Asn_Seq_Number NUMBER,
332 X_Freight_Terms_Code VARCHAR2,
333 X_Currency_Code VARCHAR2,
334 X_Fob_Code VARCHAR2,
335 X_Attribute_Category VARCHAR2,
336 X_Attribute1 VARCHAR2,
337 X_Attribute2 VARCHAR2,
338 X_Attribute3 VARCHAR2,
339 X_Attribute4 VARCHAR2,
340 X_Attribute5 VARCHAR2,
341 X_Attribute6 VARCHAR2,
342 X_Attribute7 VARCHAR2,
343 X_Attribute8 VARCHAR2,
344 X_Attribute9 VARCHAR2,
345 X_Attribute10 VARCHAR2,
346 X_Attribute11 VARCHAR2,
347 X_Attribute12 VARCHAR2,
348 X_Attribute13 VARCHAR2,
349 X_Attribute14 VARCHAR2,
350 X_Attribute15 VARCHAR2,
351 X_Global_Attribute_Category VARCHAR2 default null,
352 X_Global_Attribute1 VARCHAR2 default null,
353 X_Global_Attribute2 VARCHAR2 default null,
354 X_Global_Attribute3 VARCHAR2 default null,
355 X_Global_Attribute4 VARCHAR2 default null,
356 X_Global_Attribute5 VARCHAR2 default null,
357 X_Global_Attribute6 VARCHAR2 default null,
358 X_Global_Attribute7 VARCHAR2 default null,
359 X_Global_Attribute8 VARCHAR2 default null,
360 X_Global_Attribute9 VARCHAR2 default null,
361 X_Global_Attribute10 VARCHAR2 default null,
362 X_Global_Attribute11 VARCHAR2 default null,
363 X_Global_Attribute12 VARCHAR2 default null,
364 X_Global_Attribute13 VARCHAR2 default null,
365 X_Global_Attribute14 VARCHAR2 default null,
366 X_Global_Attribute15 VARCHAR2 default null,
367 X_Global_Attribute16 VARCHAR2 default null,
368 X_Global_Attribute17 VARCHAR2 default null,
369 X_Global_Attribute18 VARCHAR2 default null,
370 X_Global_Attribute19 VARCHAR2 default null,
371 X_Global_Attribute20 VARCHAR2 default null,
372 X_Creation_Date DATE,
373 X_Created_By NUMBER,
374 X_Last_Update_Date DATE,
375 X_Last_Updated_By NUMBER,
376 X_Last_Update_Login NUMBER
377 ) IS
378 CURSOR C IS SELECT rowid FROM wsh_deliveries
379 WHERE delivery_id = X_Delivery_Id;
380 CURSOR C2 IS SELECT wsh_deliveries_s.nextval FROM sys.dual;
381 dname wsh_deliveries.name%TYPE;
382 CURSOR C3 (del_name VARCHAR2) IS
383 SELECT COUNT(*) FROM wsh_deliveries
384 WHERE name = del_name;
385 temp NUMBER;
386 temp_id NUMBER;
387 duplicate_name EXCEPTION;
388 null_name EXCEPTION;
389 BEGIN
390 if (X_Delivery_Id is NULL) then
391 OPEN C2;
392 FETCH C2 INTO X_Delivery_Id;
393 CLOSE C2;
394 end if;
395
396 IF (X_Name IS NULL) THEN
397 dname := wsh_external_custom.delivery_name(X_Delivery_Id);
398
399 IF ( dname IS NULL ) THEN
400 RAISE null_name;
401 -- shipping default make sure the delivery name is not duplicate
402 ELSIF ( dname = TO_CHAR(X_Delivery_Id) ) THEN
403 temp_id := X_Delivery_Id;
404 OPEN C2;
405
406 LOOP
407 dname := TO_CHAR(temp_id);
408 OPEN C3( dname);
409 FETCH C3 INTO temp;
410 CLOSE C3;
411 IF ( temp = 0 ) THEN
412 EXIT;
413 END IF;
414
415 FETCH C2 INTO temp_id;
416 END LOOP;
417 IF C2%ISOPEN THEN
418 CLOSE C2;
419 END IF;
420
421 X_Delivery_Id := temp_id;
422 ELSE -- name is generated from wsh_external_custom.delivery_name
423 OPEN C3( dname);
424 FETCH C3 INTO temp;
425 CLOSE C3;
426 IF ( temp > 0 ) THEN
427 RAISE duplicate_name;
428 END IF;
429
430 END IF;
431 X_Name := dname;
432 END IF;
433
434 INSERT INTO wsh_deliveries(
435 organization_id,
436 delivery_id,
437 name,
438 source_code,
439 planned_departure_id,
440 actual_departure_id,
441 status_code,
442 loading_order_flag,
443 date_closed,
444 report_set_id,
445 sequence_number,
446 customer_id,
447 ultimate_ship_to_id,
451 gross_weight,
448 intermediate_ship_to_id,
449 pooled_ship_to_id,
450 waybill,
452 net_weight,
453 weight_uom_code,
454 volume,
455 volume_uom_code,
456 picked_by_id,
457 packed_by_id,
458 expected_arrival_date,
459 asn_date_sent,
460 asn_seq_number,
461 freight_carrier_code,
462 freight_terms_code,
463 currency_code,
464 fob_code,
465 attribute_category,
466 attribute1,
467 attribute2,
468 attribute3,
469 attribute4,
470 attribute5,
471 attribute6,
472 attribute7,
473 attribute8,
474 attribute9,
475 attribute10,
476 attribute11,
477 attribute12,
478 attribute13,
479 attribute14,
480 attribute15,
481 global_attribute_category,
482 global_attribute1,
483 global_attribute2,
484 global_attribute3,
485 global_attribute4,
486 global_attribute5,
487 global_attribute6,
488 global_attribute7,
489 global_attribute8,
490 global_attribute9,
491 global_attribute10,
492 global_attribute11,
493 global_attribute12,
494 global_attribute13,
495 global_attribute14,
496 global_attribute15,
497 global_attribute16,
498 global_attribute17,
499 global_attribute18,
500 global_attribute19,
501 global_attribute20,
502 creation_date,
503 created_by,
504 last_update_date,
505 last_updated_by,
506 last_update_login
507 ) VALUES (
508 X_Organization_Id,
509 X_Delivery_Id,
510 X_Name,
511 X_Source_Code,
512 X_Planned_Departure_Id,
513 X_Actual_Departure_Id,
514 X_Status_Code,
515 X_Loading_Order_Flag,
516 X_Date_Closed,
517 X_Report_Set_Id,
518 X_Sequence_Number,
519 X_Customer_Id,
520 X_Ultimate_Ship_To_Id,
521 X_Intermediate_Ship_To_Id,
522 X_Pooled_Ship_To_Id,
523 X_Waybill,
524 X_Gross_Weight,
525 X_Net_Weight,
526 X_Weight_Uom_Code,
527 X_Volume,
528 X_Volume_Uom_Code,
529 X_Picked_By_Id,
530 X_Packed_By_Id,
531 X_Expected_Arrival_Date,
532 X_Asn_Date_Sent,
533 X_Asn_Seq_Number,
534 X_Freight_Carrier_Code,
535 X_Freight_Terms_Code,
536 X_Currency_Code,
537 X_Fob_Code,
538 X_Attribute_Category,
539 X_Attribute1,
540 X_Attribute2,
541 X_Attribute3,
542 X_Attribute4,
543 X_Attribute5,
544 X_Attribute6,
545 X_Attribute7,
546 X_Attribute8,
547 X_Attribute9,
548 X_Attribute10,
549 X_Attribute11,
550 X_Attribute12,
551 X_Attribute13,
552 X_Attribute14,
553 X_Attribute15,
554 X_Global_Attribute_Category,
555 X_Global_Attribute1,
556 X_Global_Attribute2,
557 X_Global_Attribute3,
558 X_Global_Attribute4,
559 X_Global_Attribute5,
560 X_Global_Attribute6,
561 X_Global_Attribute7,
562 X_Global_Attribute8,
563 X_Global_Attribute9,
564 X_Global_Attribute10,
565 X_Global_Attribute11,
566 X_Global_Attribute12,
567 X_Global_Attribute13,
568 X_Global_Attribute14,
569 X_Global_Attribute15,
570 X_Global_Attribute16,
571 X_Global_Attribute17,
572 X_Global_Attribute18,
573 X_Global_Attribute19,
574 X_Global_Attribute20,
575 X_Creation_Date,
576 X_Created_By,
577 X_Last_Update_Date,
578 X_Last_Updated_By,
579 X_Last_Update_Login
580 );
581
582 OPEN C;
583 FETCH C INTO X_Rowid;
584 if (C%NOTFOUND) then
585 CLOSE C;
586 Raise NO_DATA_FOUND;
587 end if;
588 CLOSE C;
589 EXCEPTION
590 WHEN duplicate_name THEN
591 FND_MESSAGE.Set_Name('OE', 'WSH_DEL_DUP_NAME');
592 APP_EXCEPTION.Raise_Exception;
593 WHEN null_name THEN
594 FND_MESSAGE.Set_Name('OE', 'WSH_DEL_NULL_NAME');
595 APP_EXCEPTION.Raise_Exception;
596 END Insert_Row_new;
597
598
599
600 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
601 BEGIN
602 DELETE FROM wsh_deliveries
603 WHERE rowid = X_Rowid;
604
605 if (SQL%NOTFOUND) then
606 Raise NO_DATA_FOUND;
607 end if;
608 END Delete_Row;
609
610
611 END WSH_DELIVERIES_PKG1;