[Home] [Help]
PACKAGE BODY: APPS.SHP_PICKING_BATCHES_PKG
Source
1 PACKAGE BODY SHP_PICKING_BATCHES_PKG as
2 /* $Header: WSHFPKBB.pls 115.0 99/07/16 08:19:05 porting ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT VARCHAR2,
5 X_Batch_Id IN OUT NUMBER,
6 X_Creation_Date DATE,
7 X_Created_By NUMBER,
8 X_Last_Update_Date DATE,
9 X_Last_Updated_By NUMBER,
10 X_Last_Update_Login NUMBER,
11 X_Name IN OUT VARCHAR2,
12 X_Backorders_Only_Flag VARCHAR2,
13 X_Print_Flag VARCHAR2,
14 X_Existing_Rsvs_Only_Flag VARCHAR2,
15 X_Shipment_Priority_Code VARCHAR2,
16 X_Ship_Method_Code VARCHAR2,
17 X_Customer_Id NUMBER,
18 X_Group_Id NUMBER,
19 X_Header_Count NUMBER,
20 X_Header_Id NUMBER,
21 X_Ship_Set_Number NUMBER,
22 X_Inventory_Item_Id NUMBER,
23 X_Order_Type_Id NUMBER,
24 X_Date_Requested_From DATE,
25 X_Date_Requested_To DATE,
26 X_Scheduled_Shipment_Date_From DATE,
27 X_Scheduled_Shipment_Date_To DATE,
28 X_Site_Use_Id NUMBER,
29 X_Warehouse_Id NUMBER,
30 X_Subinventory VARCHAR2,
31 X_Date_Completed DATE,
32 X_Date_Confirmed DATE,
33 X_Date_Last_Printed DATE,
34 X_Date_Released DATE,
35 X_Date_Unreleased DATE,
36 X_Departure_Id NUMBER,
37 X_Delivery_Id NUMBER,
38 X_Include_Planned_Lines VARCHAR2,
39 X_Partial_Allowed_Flag VARCHAR2,
40 X_Pick_Slip_Rule_Id NUMBER,
41 X_Release_Seq_Rule_Id NUMBER,
42 X_Autocreate_Delivery_Flag VARCHAR2,
43 X_Context VARCHAR2,
44 X_Attribute1 VARCHAR2,
45 X_Attribute2 VARCHAR2,
46 X_Attribute3 VARCHAR2,
47 X_Attribute4 VARCHAR2,
48 X_Attribute5 VARCHAR2,
49 X_Attribute6 VARCHAR2,
50 X_Attribute7 VARCHAR2,
51 X_Attribute8 VARCHAR2,
52 X_Attribute9 VARCHAR2,
53 X_Attribute10 VARCHAR2,
54 X_Attribute11 VARCHAR2,
55 X_Attribute12 VARCHAR2,
56 X_Attribute13 VARCHAR2,
57 X_Attribute14 VARCHAR2,
58 X_Attribute15 VARCHAR2,
59 X_Error_Report_Flag VARCHAR2,
60 X_Org_Id NUMBER
61 ) IS
62 CURSOR C IS SELECT rowid FROM SO_PICKING_BATCHES_ALL
63 WHERE batch_id = X_Batch_Id;
64 CURSOR NEXTID IS SELECT so_picking_batches_s.nextval FROM sys.dual;
65 CURSOR Batch (batch_name VARCHAR2) IS
66 Select count(*) From SO_PICKING_BATCHES_ALL
67 Where NAME = batch_name;
68 userid NUMBER;
69 loginid NUMBER;
70 temp NUMBER;
71 BEGIN
72
73 userid := FND_GLOBAL.USER_ID;
74 loginid := FND_GLOBAL.LOGIN_ID;
75
76 IF (X_Batch_Id is NULL) THEN
77 OPEN NEXTID;
78 FETCH NEXTID INTO X_Batch_Id;
79 CLOSE NEXTID;
80 END IF;
81
82 -- Default Batch Name
83 If (X_Name is NULL) Then
84 X_Name := TO_CHAR(X_Batch_Id);
85 OPEN NEXTID;
86
87 Loop
88 OPEN Batch( X_Name);
89 FETCH Batch INTO temp;
90 IF (temp = 0) Then
91 CLOSE Batch;
92 EXIT;
93 End if;
94
95 FETCH NEXTID INTO X_Batch_Id;
96 X_Name := TO_CHAR(X_Batch_Id);
97 CLOSE Batch;
98 End Loop;
99
100 CLOSE NEXTID;
101 End If;
102
103 INSERT INTO SO_PICKING_BATCHES_ALL(
104 batch_id,
105 creation_date,
106 created_by,
107 last_update_date,
108 last_updated_by,
109 last_update_login,
110 name,
111 backorders_only_flag,
112 print_flag,
113 existing_rsvs_only_flag,
114 shipment_priority_code,
115 ship_method_code,
116 customer_id,
117 group_id,
118 header_count,
119 header_id,
120 ship_set_number,
121 inventory_item_id,
122 order_type_id,
123 date_requested_from,
124 date_requested_to,
125 scheduled_shipment_date_from,
126 scheduled_shipment_date_to,
127 site_use_id,
128 warehouse_id,
129 subinventory,
130 date_completed,
131 date_confirmed,
132 date_last_printed,
133 date_released,
134 date_unreleased,
135 departure_id,
136 delivery_id,
137 include_planned_lines,
138 partial_allowed_flag,
139 pick_slip_rule_id,
140 release_seq_rule_id,
141 autocreate_delivery_flag,
142 context,
143 attribute1,
144 attribute2,
145 attribute3,
146 attribute4,
147 attribute5,
148 attribute6,
149 attribute7,
150 attribute8,
151 attribute9,
152 attribute10,
153 attribute11,
154 attribute12,
155 attribute13,
156 attribute14,
157 attribute15,
158 error_report_flag,
159 org_id
160 ) VALUES (
161
162 X_Batch_Id,
163 SYSDATE,
164 userid,
165 SYSDATE,
166 userid,
167 loginid,
168 X_Name,
169 X_Backorders_Only_Flag,
170 X_Print_Flag,
171 X_Existing_Rsvs_Only_Flag,
172 X_Shipment_Priority_Code,
173 X_Ship_Method_Code,
174 X_Customer_Id,
175 X_Group_Id,
176 X_Header_Count,
177 X_Header_Id,
178 X_Ship_Set_Number,
179 X_Inventory_Item_Id,
180 X_Order_Type_Id,
181 X_Date_Requested_From,
182 X_Date_Requested_To,
183 X_Scheduled_Shipment_Date_From,
184 X_Scheduled_Shipment_Date_To,
185 X_Site_Use_Id,
186 X_Warehouse_Id,
187 X_Subinventory,
188 X_Date_Completed,
189 X_Date_Confirmed,
190 X_Date_Last_Printed,
191 X_Date_Released,
192 X_Date_Unreleased,
193 X_Departure_Id,
194 X_Delivery_Id,
195 X_Include_Planned_Lines,
196 X_Partial_Allowed_Flag,
197 X_Pick_Slip_Rule_Id,
198 X_Release_Seq_Rule_Id,
199 X_Autocreate_Delivery_Flag,
200 X_Context,
201 X_Attribute1,
202 X_Attribute2,
203 X_Attribute3,
204 X_Attribute4,
205 X_Attribute5,
206 X_Attribute6,
207 X_Attribute7,
208 X_Attribute8,
209 X_Attribute9,
210 X_Attribute10,
211 X_Attribute11,
212 X_Attribute12,
213 X_Attribute13,
214 X_Attribute14,
215 X_Attribute15,
216 X_Error_Report_Flag,
217 X_Org_Id
218 );
219
220 OPEN C;
221 FETCH C INTO X_Rowid;
222 if (C%NOTFOUND) then
223 CLOSE C;
224 Raise NO_DATA_FOUND;
225 end if;
226 CLOSE C;
227 END Insert_Row;
228
229
230 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
231 X_Batch_Id NUMBER,
232 X_Name VARCHAR2,
233 X_Backorders_Only_Flag VARCHAR2,
234 X_Print_Flag VARCHAR2,
235 X_Existing_Rsvs_Only_Flag VARCHAR2,
236 X_Shipment_Priority_Code VARCHAR2,
237 X_Ship_Method_Code VARCHAR2,
238 X_Customer_Id NUMBER,
239 X_Group_Id NUMBER,
240 X_Header_Count NUMBER,
241 X_Header_Id NUMBER,
242 X_Ship_Set_Number NUMBER,
243 X_Inventory_Item_Id NUMBER,
244 X_Order_Type_Id NUMBER,
245 X_Date_Requested_From DATE,
246 X_Date_Requested_To DATE,
247 X_Scheduled_Shipment_Date_From DATE,
248 X_Scheduled_Shipment_Date_To DATE,
249 X_Site_Use_Id NUMBER,
250 X_Warehouse_Id NUMBER,
251 X_Subinventory VARCHAR2,
252 X_Date_Completed DATE,
253 X_Date_Confirmed DATE,
254 X_Date_Last_Printed DATE,
255 X_Date_Released DATE,
256 X_Date_Unreleased DATE,
257 X_Departure_Id NUMBER,
258 X_Delivery_Id NUMBER,
259 X_Include_Planned_Lines VARCHAR2,
260 X_Partial_Allowed_Flag VARCHAR2,
261 X_Pick_Slip_Rule_Id NUMBER,
262 X_Release_Seq_Rule_Id NUMBER,
263 X_Autocreate_Delivery_Flag VARCHAR2,
264 X_Context VARCHAR2,
265 X_Attribute1 VARCHAR2,
266 X_Attribute2 VARCHAR2,
267 X_Attribute3 VARCHAR2,
268 X_Attribute4 VARCHAR2,
269 X_Attribute5 VARCHAR2,
270 X_Attribute6 VARCHAR2,
271 X_Attribute7 VARCHAR2,
272 X_Attribute8 VARCHAR2,
273 X_Attribute9 VARCHAR2,
274 X_Attribute10 VARCHAR2,
275 X_Attribute11 VARCHAR2,
276 X_Attribute12 VARCHAR2,
277 X_Attribute13 VARCHAR2,
278 X_Attribute14 VARCHAR2,
279 X_Attribute15 VARCHAR2,
280 X_Error_Report_Flag VARCHAR2,
281 X_Org_Id NUMBER
282 ) IS
283 CURSOR C IS
284 SELECT *
285 FROM SO_PICKING_BATCHES_ALL
286 WHERE rowid = X_Rowid
287 FOR UPDATE of Batch_Id NOWAIT;
288 Recinfo C%ROWTYPE;
289
290
291 BEGIN
292 OPEN C;
293 FETCH C INTO Recinfo;
294 if (C%NOTFOUND) then
295 CLOSE C;
296 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
297 APP_EXCEPTION.Raise_Exception;
298 end if;
299 CLOSE C;
300 if (
301
302 (Recinfo.batch_id = X_Batch_Id)
303 AND (Recinfo.name = X_Name)
304 AND (Recinfo.backorders_only_flag = X_Backorders_Only_Flag)
305 AND ( (Recinfo.print_flag = X_Print_Flag)
306 OR ( (Recinfo.print_flag IS NULL)
307 AND (X_Print_Flag IS NULL)))
308 AND ( (Recinfo.existing_rsvs_only_flag = X_Existing_Rsvs_Only_Flag)
309 OR ( (Recinfo.existing_rsvs_only_flag IS NULL)
310 AND (X_Existing_Rsvs_Only_Flag IS NULL)))
311 AND ( (Recinfo.shipment_priority_code = X_Shipment_Priority_Code)
312 OR ( (Recinfo.shipment_priority_code IS NULL)
313 AND (X_Shipment_Priority_Code IS NULL)))
314 AND ( (Recinfo.ship_method_code = X_Ship_Method_Code)
315 OR ( (Recinfo.ship_method_code IS NULL)
316 AND (X_Ship_Method_Code IS NULL)))
317 AND ( (Recinfo.customer_id = X_Customer_Id)
318 OR ( (Recinfo.customer_id IS NULL)
319 AND (X_Customer_Id IS NULL)))
320 AND ( (Recinfo.group_id = X_Group_Id)
321 OR ( (Recinfo.group_id IS NULL)
322 AND (X_Group_Id IS NULL)))
323 AND ( (Recinfo.header_count = X_Header_Count)
324 OR ( (Recinfo.header_count IS NULL)
325 AND (X_Header_Count IS NULL)))
326 AND ( (Recinfo.header_id = X_Header_Id)
327 OR ( (Recinfo.header_id IS NULL)
328 AND (X_Header_Id IS NULL)))
329 AND ( (Recinfo.ship_set_number = X_Ship_Set_Number)
330 OR ( (Recinfo.ship_set_number IS NULL)
331 AND (X_Ship_Set_Number IS NULL)))
332 AND ( (Recinfo.inventory_item_id = X_Inventory_Item_Id)
333 OR ( (Recinfo.inventory_item_id IS NULL)
334 AND (X_Inventory_Item_Id IS NULL)))
335 AND ( (Recinfo.order_type_id = X_Order_Type_Id)
336 OR ( (Recinfo.order_type_id IS NULL)
337 AND (X_Order_Type_Id IS NULL)))
338 AND ( (Recinfo.date_requested_from = X_Date_Requested_From)
339 OR ( (Recinfo.date_requested_from IS NULL)
340 AND (X_Date_Requested_From IS NULL)))
341 AND ( (Recinfo.date_requested_to = X_Date_Requested_To)
342 OR ( (Recinfo.date_requested_to IS NULL)
343 AND (X_Date_Requested_To IS NULL)))
344 AND ( (Recinfo.scheduled_shipment_date_from = X_Scheduled_Shipment_Date_From)
345 OR ( (Recinfo.scheduled_shipment_date_from IS NULL)
346 AND (X_Scheduled_Shipment_Date_From IS NULL)))
347 AND ( (Recinfo.scheduled_shipment_date_to = X_Scheduled_Shipment_Date_To)
348 OR ( (Recinfo.scheduled_shipment_date_to IS NULL)
349 AND (X_Scheduled_Shipment_Date_To IS NULL)))
350 AND ( (Recinfo.site_use_id = X_Site_Use_Id)
351 OR ( (Recinfo.site_use_id IS NULL)
352 AND (X_Site_Use_Id IS NULL)))
353 AND ( (Recinfo.warehouse_id = X_Warehouse_Id)
354 OR ( (Recinfo.warehouse_id IS NULL)
355 AND (X_Warehouse_Id IS NULL)))
356 AND ( (Recinfo.subinventory = X_Subinventory)
357 OR ( (Recinfo.subinventory IS NULL)
358 AND (X_Subinventory IS NULL)))
359 AND ( (Recinfo.date_completed = X_Date_Completed)
360 OR ( (Recinfo.date_completed IS NULL)
361 AND (X_Date_Completed IS NULL)))
362 AND ( (Recinfo.date_confirmed = X_Date_Confirmed)
363 OR ( (Recinfo.date_confirmed IS NULL)
364 AND (X_Date_Confirmed IS NULL)))
365 AND ( (Recinfo.date_last_printed = X_Date_Last_Printed)
366 OR ( (Recinfo.date_last_printed IS NULL)
367 AND (X_Date_Last_Printed IS NULL)))
368 AND ( (Recinfo.date_released = X_Date_Released)
369 OR ( (Recinfo.date_released IS NULL)
370 AND (X_Date_Released IS NULL)))
371 AND ( (Recinfo.date_unreleased = X_Date_Unreleased)
372 OR ( (Recinfo.date_unreleased IS NULL)
373 AND (X_Date_Unreleased IS NULL)))
374 AND ( (Recinfo.departure_id = X_Departure_Id)
375 OR ( (Recinfo.departure_id IS NULL)
376 AND (X_Departure_Id IS NULL)))
377 AND ( (Recinfo.delivery_id = X_Delivery_Id)
378 OR ( (Recinfo.delivery_id IS NULL)
379 AND (X_Delivery_Id IS NULL)))
380 AND ( (Recinfo.include_planned_lines = X_Include_Planned_Lines)
381 OR ( (Recinfo.include_planned_lines IS NULL)
382 AND (X_Include_Planned_Lines IS NULL)))
383 AND ( (Recinfo.partial_allowed_flag = X_Partial_Allowed_Flag)
384 OR ( (Recinfo.partial_allowed_flag IS NULL)
385 AND (X_Partial_Allowed_Flag IS NULL)))
386 AND ( (Recinfo.pick_slip_rule_id = X_Pick_Slip_Rule_Id)
387 OR ( (Recinfo.pick_slip_rule_id IS NULL)
388 AND (X_Pick_Slip_Rule_Id IS NULL)))
389 AND ( (Recinfo.release_seq_rule_id = X_Release_Seq_Rule_Id)
390 OR ( (Recinfo.release_seq_rule_id IS NULL)
391 AND (X_Release_Seq_Rule_Id IS NULL)))
392 AND ( (Recinfo.release_seq_rule_id = X_Autocreate_Delivery_Flag)
393 OR ( (Recinfo.autocreate_delivery_flag IS NULL)
394 AND (X_Autocreate_Delivery_Flag IS NULL)))
395 AND ( (Recinfo.context = X_Context)
396 OR ( (Recinfo.context IS NULL)
397 AND (X_Context IS NULL)))
398 AND ( (Recinfo.attribute1 = X_Attribute1)
399 OR ( (Recinfo.attribute1 IS NULL)
400 AND (X_Attribute1 IS NULL)))
401 AND ( (Recinfo.attribute2 = X_Attribute2)
402 OR ( (Recinfo.attribute2 IS NULL)
403 AND (X_Attribute2 IS NULL)))
404 AND ( (Recinfo.attribute3 = X_Attribute3)
405 OR ( (Recinfo.attribute3 IS NULL)
406 AND (X_Attribute3 IS NULL)))
407 AND ( (Recinfo.attribute4 = X_Attribute4)
408 OR ( (Recinfo.attribute4 IS NULL)
409 AND (X_Attribute4 IS NULL)))
410 AND ( (Recinfo.attribute5 = X_Attribute5)
411 OR ( (Recinfo.attribute5 IS NULL)
412 AND (X_Attribute5 IS NULL)))
413 AND ( (Recinfo.attribute6 = X_Attribute6)
414 OR ( (Recinfo.attribute6 IS NULL)
415 AND (X_Attribute6 IS NULL)))
416 AND ( (Recinfo.attribute7 = X_Attribute7)
417 OR ( (Recinfo.attribute7 IS NULL)
418 AND (X_Attribute7 IS NULL)))
419 AND ( (Recinfo.attribute8 = X_Attribute8)
420 OR ( (Recinfo.attribute8 IS NULL)
421 AND (X_Attribute8 IS NULL)))
422 AND ( (Recinfo.attribute9 = X_Attribute9)
423 OR ( (Recinfo.attribute9 IS NULL)
424 AND (X_Attribute9 IS NULL)))
425 AND ( (Recinfo.attribute10 = X_Attribute10)
426 OR ( (Recinfo.attribute10 IS NULL)
427 AND (X_Attribute10 IS NULL)))
428 AND ( (Recinfo.attribute11 = X_Attribute11)
429 OR ( (Recinfo.attribute11 IS NULL)
430 AND (X_Attribute11 IS NULL)))
431 AND ( (Recinfo.attribute12 = X_Attribute12)
432 OR ( (Recinfo.attribute12 IS NULL)
433 AND (X_Attribute12 IS NULL)))
434 AND ( (Recinfo.attribute13 = X_Attribute13)
435 OR ( (Recinfo.attribute13 IS NULL)
436 AND (X_Attribute13 IS NULL)))
437 AND ( (Recinfo.attribute14 = X_Attribute14)
438 OR ( (Recinfo.attribute14 IS NULL)
439 AND (X_Attribute14 IS NULL)))
440 AND ( (Recinfo.attribute15 = X_Attribute15)
441 OR ( (Recinfo.attribute15 IS NULL)
442 AND (X_Attribute15 IS NULL)))
443 AND ( (Recinfo.error_report_flag = X_Error_Report_Flag)
444 OR ( (Recinfo.error_report_flag IS NULL)
445 AND (X_Error_Report_Flag IS NULL)))
446 ) then
447 return;
448 else
449 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
450 APP_EXCEPTION.Raise_Exception;
451 end if;
452 END Lock_Row;
453
454
455
456 PROCEDURE Update_Row(X_Rowid VARCHAR2,
457 X_Batch_Id NUMBER,
458 X_Last_Update_Date DATE,
459 X_Last_Updated_By NUMBER,
460 X_Last_Update_Login NUMBER,
461 X_Name VARCHAR2,
462 X_Backorders_Only_Flag VARCHAR2,
463 X_Print_Flag VARCHAR2,
464 X_Existing_Rsvs_Only_Flag VARCHAR2,
465 X_Shipment_Priority_Code VARCHAR2,
466 X_Ship_Method_Code VARCHAR2,
467 X_Customer_Id NUMBER,
468 X_Group_Id NUMBER,
469 X_Header_Count NUMBER,
470 X_Header_Id NUMBER,
471 X_Ship_Set_Number NUMBER,
472 X_Inventory_Item_Id NUMBER,
473 X_Order_Type_Id NUMBER,
474 X_Date_Requested_From DATE,
475 X_Date_Requested_To DATE,
476 X_Scheduled_Shipment_Date_From DATE,
477 X_Scheduled_Shipment_Date_To DATE,
478 X_Site_Use_Id NUMBER,
479 X_Warehouse_Id NUMBER,
480 X_Subinventory VARCHAR2,
481 X_Date_Completed DATE,
482 X_Date_Confirmed DATE,
483 X_Date_Last_Printed DATE,
484 X_Date_Released DATE,
485 X_Date_Unreleased DATE,
486 X_Context VARCHAR2,
487 X_Attribute1 VARCHAR2,
488 X_Attribute2 VARCHAR2,
489 X_Attribute3 VARCHAR2,
490 X_Attribute4 VARCHAR2,
491 X_Attribute5 VARCHAR2,
492 X_Attribute6 VARCHAR2,
493 X_Attribute7 VARCHAR2,
494 X_Attribute8 VARCHAR2,
495 X_Attribute9 VARCHAR2,
496 X_Attribute10 VARCHAR2,
497 X_Attribute11 VARCHAR2,
498 X_Attribute12 VARCHAR2,
499 X_Attribute13 VARCHAR2,
500 X_Attribute14 VARCHAR2,
501 X_Attribute15 VARCHAR2,
502 X_Error_Report_Flag VARCHAR2,
503 X_Org_Id NUMBER
504
505 ) IS
506 userid NUMBER;
507 loginid NUMBER;
508 BEGIN
509
510 userid := FND_GLOBAL.USER_ID;
511 loginid := FND_GLOBAL.LOGIN_ID;
512 UPDATE SO_PICKING_BATCHES
513 SET
514 batch_id = X_Batch_Id,
515 last_update_date = SYSDATE,
516 last_updated_by = userid,
517 last_update_login = loginid,
518 name = X_Name,
519 backorders_only_flag = X_Backorders_Only_Flag,
520 print_flag = X_Print_Flag,
521 existing_rsvs_only_flag = X_Existing_Rsvs_Only_Flag,
522 shipment_priority_code = X_Shipment_Priority_Code,
523 ship_method_code = X_Ship_Method_Code,
524 customer_id = X_Customer_Id,
525 group_id = X_Group_Id,
526 header_count = X_Header_Count,
527 header_id = X_Header_Id,
528 ship_set_number = X_Ship_Set_Number,
529 inventory_item_id = X_Inventory_Item_Id,
530 order_type_id = X_Order_Type_Id,
531 date_requested_from = X_Date_Requested_From,
532 date_requested_to = X_Date_Requested_To,
533 scheduled_shipment_date_from = X_Scheduled_Shipment_Date_From,
534 scheduled_shipment_date_to = X_Scheduled_Shipment_Date_To,
535 site_use_id = X_Site_Use_Id,
536 warehouse_id = X_Warehouse_Id,
537 subinventory = X_Subinventory,
538 date_completed = X_Date_Completed,
539 date_confirmed = X_Date_Confirmed,
540 date_last_printed = X_Date_Last_Printed,
541 date_released = X_Date_Released,
542 date_unreleased = X_Date_Unreleased,
543 context = X_Context,
544 attribute1 = X_Attribute1,
545 attribute2 = X_Attribute2,
546 attribute3 = X_Attribute3,
547 attribute4 = X_Attribute4,
548 attribute5 = X_Attribute5,
549 attribute6 = X_Attribute6,
550 attribute7 = X_Attribute7,
551 attribute8 = X_Attribute8,
552 attribute9 = X_Attribute9,
553 attribute10 = X_Attribute10,
554 attribute11 = X_Attribute11,
555 attribute12 = X_Attribute12,
556 attribute13 = X_Attribute13,
557 attribute14 = X_Attribute14,
558 attribute15 = X_Attribute15,
559 error_report_flag = X_Error_Report_Flag
560 WHERE rowid = X_Rowid;
561
562 if (SQL%NOTFOUND) then
563 Raise NO_DATA_FOUND;
564 end if;
565 END Update_Row;
566
567 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
568 BEGIN
569 DELETE FROM SO_PICKING_BATCHES
570 WHERE rowid = X_Rowid;
571
572 if (SQL%NOTFOUND) then
573 Raise NO_DATA_FOUND;
574 end if;
575 END Delete_Row;
576
577 FUNCTION Submit_Release_Request(Batch_Id NUMBER) RETURN NUMBER IS
578 request_id NUMBER;
579 BEGIN
580
581 request_id := FND_REQUEST.Submit_Request('OE', 'WSHREL','','',FALSE,
582 to_char(Batch_Id),'Y');
583
584 if (request_id > 0) then
585 COMMIT WORK;
586 end if;
587 return request_id;
588 END Submit_Release_Request;
589
590 PROCEDURE Delete_And_Commit(X_Rowid VARCHAR2) IS
591 BEGIN
592 Delete_Row(X_Rowid);
593 COMMIT WORK;
594 END Delete_And_Commit;
595
596 PROCEDURE Commit_Work IS
597 BEGIN
598 COMMIT WORK;
599 END Commit_Work;
600
601
602 PROCEDURE Get_Printer ( report IN VARCHAR2,
603 report_printer OUT VARCHAR2,
604 default_report IN VARCHAR2 default 'OEXSHPIK' ) IS
605 level_type_id NUMBER;
606 app_id NUMBER;
607 respid NUMBER;
608 userid NUMBER;
609 printer varchar2(32);
610 BEGIN
611 -- get the applications, responsibility, and user ID
612 app_id := FND_GLOBAL.RESP_APPL_ID;
613 respid := FND_GLOBAL.RESP_ID;
614 userid := FND_GLOBAL.USER_ID;
615
616 -- get pick slip printer
617 SELECT MAX(P.LEVEL_TYPE_ID)
618 INTO level_type_id
619 FROM SO_REPORT_PRINTERS P,
620 SO_REPORTS R
621 WHERE P.REPORT_ID = R.REPORT_ID
622 AND R.NAME =
623 NVL(report, default_report)
624 AND P.LEVEL_VALUE_ID = DECODE(P.LEVEL_TYPE_ID,
625 10001,0,
626 10002,app_id,
627 10003,respid,
628 10004,userid)
629 AND ENABLE_FLAG = 'Y';
630
631 SELECT P.PRINTER_NAME
632 INTO printer
633 FROM SO_REPORT_PRINTERS P,
634 SO_REPORTS R
635 WHERE P.REPORT_ID = R.REPORT_ID
636 AND R.NAME =
637 NVL(report, default_report)
638 AND P.LEVEL_TYPE_ID = level_type_id
639 AND P.LEVEL_VALUE_ID = DECODE(level_type_id,
640 10001,0,
641 10002,app_id,
642 10003,respid,
643 10004,userid);
644
645 report_printer := printer;
646 EXCEPTION
647 WHEN OTHERS THEN report_printer := NULL;
648 END get_printer;
649
650
651 FUNCTION Open_Batch( X_batch_id IN NUMBER)
652 RETURN VARCHAR2 IS
653 x_num_open NUMBER;
654 BEGIN
655 x_num_open := 0;
656
657 SELECT count(*)
658 INTO x_num_open
659 FROM so_picking_headers_all
660 WHERE status_code||'' not in ('PENDING','CLOSED','IN PROGRESS')
661 AND batch_id = X_batch_id;
662
663 IF (x_num_open > 0) THEN
664 RETURN ('Y');
665 ELSE
666 RETURN ('N');
667 END IF;
668
669 EXCEPTION
670 WHEN NO_DATA_FOUND THEN
671 RETURN ('N');
672 END Open_Batch;
673
674 END SHP_PICKING_BATCHES_PKG;