1 PACKAGE BODY WSH_DEL_OI_CORE as
2 /* $Header: WSHSDVAB.pls 115.8 99/08/09 15:28:53 porting sh $ */
3
4 -- Name Get_order_info
5 -- Purpose Obtain order info for the picking_header_id
6 -- Notes
7
8 PROCEDURE GET_ORDER_INFO (X_picking_header_id in number,
9 X_Order_currency in out varchar2,
10 X_Order_category in out varchar2) is
11 BEGIN
12 declare
13 cursor order_info is
14 SELECT soh.currency_code ,
15 soh.order_category
16 FROM so_headers_all soh,
17 so_picking_headers_all soph
18 WHERE soph.picking_header_id = X_picking_header_id
19 AND soh.header_id = soph.order_header_id;
20 begin
21 open order_info;
22 fetch order_info
23 into X_order_currency, X_order_category;
24 close order_info;
25 exception when others then
26 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
27 FND_MESSAGE.Set_Token('PACKAGE','WSH_DEL_OI_CORE.get_order_info');
28 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
29 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
30 APP_EXCEPTION.Raise_Exception;
31
32 end;
33 END GET_ORDER_INFO;
34
35
36
37 -- Name : PICKSLIP_OPEN - returns true if its closed
38 --
39 -- Assumptions
40 -- actually works on picking header id not pick slip number
41 -- calling routine should raise error OE_SH_PICK_SLIP_CLOSED
42 FUNCTION PICKSLIP_CLOSED (X_PICKING_HEADER_ID IN NUMBER) RETURN BOOLEAN
43 IS
44 CURSOR open_hdr IS
45 SELECT pick_slip_number
46 FROM so_picking_headers_all
47 WHERE picking_header_id = X_Picking_Header_id
48 AND status_code = 'OPEN';
49 sql_dummy number;
50
51 BEGIN
52 OPEN open_hdr;
53 FETCH open_hdr INTO sql_dummy;
54 IF open_hdr%NOTFOUND THEN
55 CLOSE open_hdr;
56 RETURN (TRUE);
57 ELSE
58 CLOSE open_hdr;
59 RETURN (FALSE);
60 END IF;
61 EXCEPTION WHEN OTHERS THEN
62 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
63 FND_MESSAGE.Set_Token('PACKAGE','WSH_DEL_OI_CORE.pickslip_closed');
64 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
65 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
66 APP_EXCEPTION.Raise_Exception;
67
68 END;
69
70
71
72
73
74 -- Name GET_ITEM_CONTROL_CODES
75 -- Purpose Gets control code flags for a given org/item
76 -- Notes
77 --
78 PROCEDURE GET_ITEM_CONTROL_CODES
79 (X_warehouse_id in number,
80 X_item_id in number,
81 X_order_category in varchar2,
82 X_subinv_restricted_flag in out varchar2,
83 X_revision_control_flag in out varchar2,
84 X_lot_control_flag in out varchar2,
85 X_serial_number_control_flag in out varchar2,
86 error_code in out varchar2) is
87 BEGIN
88 declare
89 cursor control_codes is
90 SELECT
91 decode(msi.restrict_subinventories_code,1,'Y','N') ,
92 decode(msi.revision_qty_control_code,2,'Y','N') ,
93 decode(msi.lot_control_code,2,'Y',3,'Y','N'),
94 decode(msi.serial_number_control_code,
95 -- 2=Predefined serial# 5=Dynamic at inv. receipt 6=Dynamic at sales issue
96 2,'Y', 5,'Y', 6,decode(X_order_category,'P','N','D'),'N')
97 from mtl_system_items msi
98 where msi.inventory_item_id = X_item_id
99 and msi.organization_id = X_warehouse_id;
100 begin
101 open control_codes;
102 fetch control_codes
103 into X_subinv_restricted_flag,
104 X_revision_control_flag,
105 X_lot_control_flag,
106 X_serial_number_control_flag;
107 if control_codes%notfound then
108 error_code := 'WSH_OI_INVALID_ITEM';
109 end if;
110 close control_codes;
111
112
113 exception when others then
114 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
115 FND_MESSAGE.Set_Token('PACKAGE','WSH_DEL_OI_CORE.get_item_control_codes');
116 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
117 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
118 APP_EXCEPTION.Raise_Exception;
119 end;
120 END get_item_control_codes;
121
122
123 -- Name : VALID_SUBINVENTORY
124 --
125 -- Purpose: Validate Sub for this item/warehouse
126 -- Arguments
127 -- x_warehouse_id input org id
128 -- x_item_id input item_id
129 -- x_subinventory input sub for validation
130 -- x_order_category the order category
131 -- x_subinv_restricted_flag either Y or N. If Y then sub is validated
132 -- against predefined list
133 -- error_code error_code: 0 = success all others = error
134 -- Notes
135 -- Sub is input only because it is validated only: nothing is derived
136
137 FUNCTION VALID_SUBINVENTORY
138 (X_warehouse_id in number,
139 X_item_id in number,
140 X_subinventory in varchar2,
141 X_subinv_restricted_flag in varchar2)
142 RETURN BOOLEAN IS
143 BEGIN
144 declare
145 X_description varchar2(50);
146
147 cursor valid_sub is
148 select description
149 from mtl_secondary_inventories
150 where organization_id = X_warehouse_id
151 and quantity_tracked = 1
152 and trunc(sysdate) <= nvl( disable_date, trunc(sysdate) )
153 and secondary_inventory_name = x_subinventory;
154
155 cursor valid_sub_restricted is
156 select mtlsub.description
157 from mtl_item_sub_inventories mtlisi
158 , mtl_secondary_inventories mtlsub
159 where mtlisi.organization_id = X_warehouse_id
160 and mtlisi.inventory_item_id = X_item_id
161 and mtlsub.organization_id = X_warehouse_id
162 and mtlsub.secondary_inventory_name = mtlisi.secondary_inventory
163 and mtlsub.quantity_tracked = 1
164 and trunc(sysdate) <= nvl( mtlsub.disable_date, trunc(sysdate) )
165 and mtlsub.secondary_inventory_name = x_subinventory;
166
167
168 begin
169
170 x_description := null;
171 if X_subinv_restricted_flag = 'N' then
172 OPEN valid_sub;
173 FETCH valid_sub into X_description;
174 CLOSE valid_sub;
175 else
176 OPEN valid_sub_restricted;
177 FETCH valid_sub_restricted into X_description;
178 CLOSE valid_sub_restricted;
179 end if;
180
181 IF x_description is null then
182 return (FALSE);
183 END IF;
184
185 return (TRUE);
186 exception when others then
187 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
188 FND_MESSAGE.Set_Token('PACKAGE','WSH_DEL_OI_CORE.valid_subinventory');
189 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
190 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
191 APP_EXCEPTION.Raise_Exception;
192 end;
193 END valid_subinventory;
194
195
196
197
198 -- Name : DEFAULT_SUBINVENTORY
199 --
200 -- Purpose: Get Default Sub for this item/warehouse
201 --
205 -- Returns default sub or null
202 -- Arguments
203 -- x_warehouse_id input org id
204 -- x_item_id input item_id
206 -- Notes
207 -- Sub gets populated with the default for this org/item if it is defined
208 -- else it returns null
209 -- The query should always succeed. Any error is a system error which will
210 -- terminate the program. Therefore no error_code parameter required
211
212 FUNCTION DEFAULT_SUBINVENTORY
213 (X_warehouse_id in number,
214 X_item_id in number) RETURN varchar2 is
215 BEGIN
216 declare
217 cursor default_sub is
218 select mtlsub.secondary_inventory_name
219 from mtl_item_sub_defaults mtlisd,
220 mtl_secondary_inventories mtlsub
221 where mtlisd.inventory_item_id = X_item_id
222 and mtlisd.organization_id = X_warehouse_id
223 and mtlisd.default_type = 1
224 and mtlsub.organization_id = mtlisd.organization_id
225 and mtlsub.secondary_inventory_name = mtlisd.subinventory_code
226 and mtlsub.quantity_tracked = 1
227 and trunc(sysdate) <= nvl( mtlsub.disable_date, trunc(sysdate));
228
229 dflt_subinventory varchar2(30);
230 begin
231
232 open default_sub;
233 fetch default_sub into dflt_subinventory;
234 close default_sub;
235
236 RETURN (dflt_subinventory);
237
238 exception when others then
239 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
240 FND_MESSAGE.Set_Token('PACKAGE','WSH_DEL_OI_CORE.default_subinventory');
241 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
242 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
243 APP_EXCEPTION.Raise_Exception;
244
245 end;
246 END DEFAULT_SUBINVENTORY;
247
248
249 -- Name : GET_LOCATOR_CONTROLS
250 --
251 -- Purpose: Get Locator Controls for this Org/Item/Sub
252 --
253 -- Arguments
254 -- x_warehouse_id input org id
255 -- x_item_id input item_id
256 -- x_subinventory input sub id
257 -- x_location_control_flag output
258 -- x_location_restricted_flag output
259 -- error_code error_code: 0 = success all others = error
260 -- Notes
261 -- This is seperate from get_item_controls because sub is required
262
263 PROCEDURE GET_LOCATOR_CONTROLS
264 (X_warehouse_id in number,
265 X_item_id in number,
266 X_subinventory in varchar2,
267 X_location_control_flag in out varchar2,
268 X_location_restricted_flag in out varchar2,
272 cursor locator_controls is
269 error_code in out varchar2) is
270 BEGIN
271 declare
273 select decode( nvl( mtlpar.stock_locator_control_code, 1 ),
274 1, 'N', 2, 'Y', 3, 'D',
275 4, DECODE( NVL( mtlsin.locator_type, 1 ),
276 1, 'N', 2, 'Y', 3, 'D', 4, 'N',
277 5, DECODE( NVL( mtlsis.location_control_code, 1 ),
278 1, 'N', 2, 'Y', 3, 'D', 'N' ),
279 'N' ),
280 5, DECODE( NVL( mtlsis.location_control_code, 1 ),
281 1, 'N', 2, 'Y', 3, 'D', 'N' ),
282 'N' )
283 , DECODE( mtlsis.restrict_locators_code, 1, 'Y', 'N' )
284 from mtl_parameters mtlpar
285 , mtl_secondary_inventories mtlsin
286 , mtl_system_items mtlsis
287 where mtlpar.organization_id = X_warehouse_id
288 and mtlsin.organization_id = mtlpar.organization_id
289 and mtlsin.secondary_inventory_name = X_subinventory
290 and mtlsis.organization_id = mtlpar.organization_id
291 and mtlsis.inventory_item_id = X_item_id;
292
293
294 begin
295 open locator_controls;
296 fetch locator_controls
297 into X_location_control_flag,
298 X_location_restricted_flag;
299 if locator_controls%notfound then
300 error_code := 'WSH_OI_MISSING_LOC_CNTRL';
301 end if;
302 close locator_controls;
303 exception when others then
304 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
305 FND_MESSAGE.Set_Token('PACKAGE','WSH_DEL_OI_CORE.get_locator_controls');
306 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
307 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
308 APP_EXCEPTION.Raise_Exception;
309 end;
310 END get_locator_controls;
311
312
313
314 -- Name : VALID_LOT_NUMBER
315 -- Purpose: Validates the lot number
316 -- Arguments
317 -- x_warehouse_id input org id
318 -- x_item_id input item_id
319 -- x_subinventory input sub id
320 -- x_lot_number input lot number for validation
321 -- error_code error_code: 0 = success all others = error
322 -- Notes
323 -- This validates the lot number: it does not populate it therefore
324 -- the only out parameter is error_code
325 --
326 FUNCTION VALID_LOT_NUMBER
327 (X_warehouse_id in number,
328 X_item_id in number,
329 X_subinventory in varchar2,
330 X_lot_number in varchar2) return BOOLEAN is
331 BEGIN
332 declare
333 cursor validate_lot is
334 select 'lot number valid'
335 from mtl_onhand_quantities
339 and lot_number = X_lot_number;
336 where inventory_item_id = X_item_id
337 and organization_id = X_warehouse_id
338 and nvl(subinventory_code,'X') = nvl(X_subinventory,'X')
340
341 dummy varchar2(40);
342 begin
343 open validate_lot;
344 fetch validate_lot into dummy;
345 close validate_lot;
346
347 if dummy is null then
348 return (FALSE);
349 end if;
350 return (TRUE);
351
352 exception when others then
353 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
354 FND_MESSAGE.Set_Token('PACKAGE','WSH_DEL_OI_CORE.valid_lot_number');
355 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
356 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
357 APP_EXCEPTION.Raise_Exception;
358
359 end;
360 END valid_lot_number;
361
362
363
364 -- Name : VALID_REVISION
365 -- Purpose: Validates the lot number
366 -- Arguments
367 -- x_warehouse_id input org id
368 -- x_item_id input item_id
369 -- x_revision input revision number for validation
370 -- error_code error_code: 0 = success all others = error
371 -- Notes
372 -- This validates the revision number: it does not populate it therefore
373 -- the only out parameter is error_code
374 --
375 FUNCTION VALID_REVISION
376 (X_warehouse_id in number,
377 X_item_id in number,
378 X_revision in varchar2) return BOOLEAN is
379
380 BEGIN
381 declare
382 cursor validate_revision is
383 select 'revision is valid'
384 from mtl_item_revisions
385 where organization_id = X_warehouse_id
386 and inventory_item_id = X_item_id
387 and revision = X_revision;
388 dummy varchar2(40);
389 begin
390 open validate_revision;
391 fetch validate_revision into dummy;
392 close validate_revision;
393
394 if dummy is null then
395 return (FALSE);
396 end if;
397 return (TRUE);
398
399 exception when others then
400 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
401 FND_MESSAGE.Set_Token('PACKAGE','WSH_DEL_OI_CORE.valid_revision');
402 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
403 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
404 APP_EXCEPTION.Raise_Exception;
405 end;
406 END VALID_REVISION;
407
408
412 -- x_warehouse_id input org id
409 -- Name : VALIDATE_LOCATOR_ID
410 -- Purpose: Validates Locator Id for this item/org/sub
411 -- Arguments
413 -- x_item_id input item_id
414 -- x_subinventory input sub id
415 -- x_location_restricted_flag either Y or N. If Y will ensure
416 -- location is in predefined list
417 -- X_locator_id locator_id
418 -- Notes
419 -- either locator segment or id must be supplied. If both are supplied then
420 -- segments are ignored and priority given to the id.
421 --
422 FUNCTION VALID_LOCATOR_ID
423 (X_warehouse_id in number,
424 X_item_id in number,
425 X_subinventory in varchar2,
426 X_location_restricted_flag in varchar2,
427 X_locator_id in number) return BOOLEAN is
428 BEGIN
429 declare
430 sql_string varchar2(30);
431 cursor validate_locator is
432 select 'valid locator id'
433 from mtl_item_locations mtlloc
434 where organization_id = X_warehouse_id
435 and mtlloc.inventory_location_id = X_locator_id
436 and ( nvl(X_location_restricted_flag, 'N') = 'N'
437 or
438 (nvl(X_location_restricted_flag, 'N') = 'Y'
439 and nvl(mtlloc.inventory_location_id, -1) in (
440 select mtlsls.secondary_locator
441 from mtl_secondary_locators mtlsls
442 where mtlsls.organization_id = X_warehouse_id
443 and mtlsls.inventory_item_id = X_item_id
444 and mtlsls.subinventory_code = X_subinventory)));
445 begin
446
447 sql_string := null;
448 open validate_locator;
449 fetch validate_locator into sql_string;
450 close validate_locator;
451
452
453 if sql_string is not null then
454 RETURN(TRUE);
455 else
456 RETURN(FALSE);
457 end if;
458
459 exception when others then
460 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
461 FND_MESSAGE.Set_Token('PACKAGE','WSH_DEL_OI_CORE.valid_locator_id');
462 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
463 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
464 APP_EXCEPTION.Raise_Exception;
465
466 end;
467 END valid_locator_id;
468
472 -- Purpose: Lookup and retrieve Default Location Id
469
470
471 -- Name : DEFAULT_LOCATOR
473 -- Arguments
474 -- x_warehouse_id input org id
475 -- x_item_id input item_id
476 -- x_subinventory input sub id
477 -- x_location_restricted_flag either Y or N. If Y will ensure
478 -- location is in predefined list
479 -- deflt_locator_id output
480 -- Notes
481 -- retieves default locator. If none exists then it returns null.
482 -- Any other error is a system error so program will terminate.
483 -- Therfore there is no error_code paramter.
484 --
485 FUNCTION DEFAULT_LOCATOR
486 (X_warehouse_id in number,
487 X_item_id in number,
488 X_subinventory in varchar2,
489 X_location_restricted_flag in varchar2) return number is
490
491 BEGIN
492
493 wsh_del_oi_core.println('inside DEFAULT_LOCATOR');
494
495 declare
496 cursor default_locator is
497 select mtldl.locator_id
498 from mtl_item_loc_defaults mtldl
499 where mtldl.inventory_item_id = X_item_id
500 and mtldl.organization_id = X_warehouse_id
501 and mtldl.default_type = 1
502 and mtldl.subinventory_code = X_subinventory
503 and ( nvl(X_location_restricted_flag, 'N') = 'N'
504 OR
505 (nvl(X_location_restricted_flag, 'N') = 'Y'
506 and nvl(mtldl.locator_id, -1) in
507 (select mtlsls.secondary_locator
508 from mtl_secondary_locators mtlsls
509 where mtlsls.organization_id = X_warehouse_id
510 and mtlsls.inventory_item_id = X_item_id
511 and mtlsls.subinventory_code = X_subinventory)));
512 dflt_locator_id number;
513
514 begin
515
516 wsh_del_oi_core.println('inside DEFAULT_LOCATOR');
517 open default_locator;
518 fetch default_locator into dflt_locator_id;
519 close default_locator;
520
521 return (dflt_locator_id);
522
523 exception when others then
524 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
525 FND_MESSAGE.Set_Token('PACKAGE','WSH_DEL_OI_CORE.default_locator');
526 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
527 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
528 APP_EXCEPTION.Raise_Exception;
529 end;
530 END default_locator;
531
532
533
534 -- Name: Valid_Serial_Number
535 -- Notes : validates serial# is OK for this combination of inv controls,
536 -- the sub permits serial number and loc is valid when under restricted locs
537 --
541 (X_warehouse_id in number,
538 --
539
540 FUNCTION VALID_SERIAL_NUMBER
542 X_item_id in number,
543 X_subinventory in varchar2,
544 X_revision in varchar2,
545 X_lot_number in varchar2,
546 X_locator_id in number,
547 X_serial_number in varchar2,
548 X_location_restricted_flag in varchar2,
549 X_location_control_flag in varchar2,
550 X_serial_number_control_flag in varchar2) return BOOLEAN is
551
552 BEGIN
553 declare
554 sql_string varchar2(30);
555 cur_stat number;
556 msg_string varchar2(200);
557 cursor validate_ser_num is
558 SELECT
559 'valid serial number', S.CURRENT_STATUS
560 FROM MTL_SERIAL_NUMBERS S,
561 MTL_ITEM_LOCATIONS LOC
562 WHERE S.CURRENT_ORGANIZATION_ID = x_warehouse_id
563 AND S.INVENTORY_ITEM_ID = x_item_id
564 AND NVL( S.REVISION, '~' ) = NVL( x_revision, '~' )
565 AND NVL( S.LOT_NUMBER, '~' ) = NVL( x_lot_number, '~' )
566 AND S.SERIAL_NUMBER = x_serial_number
567 AND S.CURRENT_ORGANIZATION_ID = LOC.ORGANIZATION_ID(+)
568 -- if not under locator control then -1 = -1 else s.current_loc=x_loc
569 AND DECODE(x_location_control_flag,
570 'Y', NVL(S.CURRENT_LOCATOR_ID, -1),
571 'D', NVL(S.CURRENT_LOCATOR_ID, -1),
572 -1) =
573 DECODE(x_locator_id,
574 '', DECODE(x_location_control_flag,
575 'Y', LOC.INVENTORY_LOCATION_ID,
576 'D', LOC.INVENTORY_LOCATION_ID, -1),
577 x_locator_id)
578 AND NVL(S.CURRENT_LOCATOR_ID, -1) = LOC.INVENTORY_LOCATION_ID(+)
579 -- if restricted loc then check in mtl_sec_loc
580 AND (NVL(x_location_restricted_flag, 'N') = 'N' OR
581 (NVL(x_location_restricted_flag, 'N') = 'Y'
582 AND
583 NVL(LOC.INVENTORY_LOCATION_ID, -1) IN (
584 SELECT NVL(MAX(MTLSLS.SECONDARY_LOCATOR),-1)
585 FROM MTL_SECONDARY_LOCATORS MTLSLS
586 WHERE MTLSLS.ORGANIZATION_ID = x_warehouse_id
587 AND MTLSLS.INVENTORY_ITEM_ID = x_item_id
588 AND MTLSLS.SUBINVENTORY_CODE = x_subinventory
589 -- Bug 842175
590 AND MTLSLS.SECONDARY_LOCATOR = x_locator_id )))
591 AND S.CURRENT_SUBINVENTORY_CODE = x_subinventory
592 AND S.CURRENT_STATUS IN (
593 DECODE( x_serial_number_control_flag, 'Y', 3, -1 ),
594 DECODE( x_serial_number_control_flag, 'D', 3, -1 ),
595 DECODE( x_serial_number_control_flag, 'D', 1, -1 ),
596 DECODE( x_serial_number_control_flag, 'D', 4, -1 ),
597 DECODE( x_serial_number_control_flag, 'D', 5, -1 ));
598
602 ',sub:'||x_subinventory||
599 begin
600 msg_string := ('Ware:'||to_char(x_warehouse_id)||
601 ',item:'||to_char(x_item_id)||
603 ',rev:'||x_revision||
604 ',lot:'||x_lot_number||
605 ',loc:'||to_char(x_locator_id)||
606 ',srl:'||x_serial_number||
607 ',l_res:'||X_location_restricted_flag||
608 ',l_ctl:'||X_location_control_flag||
609 ',s_ctl:'||X_serial_number_control_flag );
610
611 open validate_ser_num;
612 fetch validate_ser_num into sql_string, cur_stat;
613 close validate_ser_num;
614
615 if ( x_serial_number_control_flag = 'D' ) Then
616 if ( sql_string is NULL ) Then
617 return TRUE ;
618 elsif (( cur_stat = 1 ) or ( cur_stat = 3 )) Then
619 return TRUE;
620 else
621 wsh_del_oi_core.println('Valid_srl_no:'||msg_string);
622 wsh_del_oi_core.println('Dynamic Srl controlled but status !=(1 or 3)');
623 return FALSE;
624 end if;
625 elsif sql_string is null then
626 wsh_del_oi_core.println('valid_srl_no:'||msg_string);
627 wsh_del_oi_core.println('Predefined Srl nos but srlno does not exist.');
628 return (FALSE);
629 end if;
630 return (TRUE);
631
632 exception when others then
633 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
634 FND_MESSAGE.Set_Token('PACKAGE','WSH_DEL_OI_CORE.valid_serial_number');
635 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
636 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
637 APP_EXCEPTION.Raise_Exception;
638
639 end;
640 END valid_serial_number;
641
642 -- Name: AR_INTERFACED
643 -- Notes
644 -- returns TRUE if order has been interfaced to AR
645 --
646 FUNCTION AR_Interfaced (X_delivery_id in number)
647 RETURN BOOLEAN IS
648 BEGIN
649 declare
650 -- s5 = Receivables Interface flag: 5= Partial, 9= Interfaced
651 cursor ar_valid (del_id number) is
652 select 'exists'
653 from so_picking_lines_all pl, so_picking_line_details pld
654 where pl.picking_line_id = pld.picking_line_id
655 and pld.delivery_id = del_id
656 and pl.ra_interface_status is not null
657 and exists( select 'interfaced lines exist'
658 from so_lines_all l
659 where l.s5 in (5,8,9)
660 and l.line_id = pl.order_line_id);
661 dummy varchar2(30);
662
663 begin
664 open ar_valid(x_delivery_id);
668 RETURN (TRUE);
665 fetch ar_valid into dummy;
666 if ar_valid%found then
667 close ar_valid;
669 else
670 close ar_valid;
671 wsh_del_oi_core.println('Its not AR_interfaced');
672 RETURN (FALSE);
673 end if;
674 exception when others then
675 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
676 FND_MESSAGE.Set_Token('PACKAGE','WSH_DEL_OI_CORE.AR_INTERFACED');
677 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
678 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
679 APP_EXCEPTION.Raise_Exception;
680 end;
681
682 END AR_INTERFACED;
683
684
685
686 -- Name : Valid_freight_type
687 --
688 -- Notes
689 -- validates type id/code. If both are supplied for validation,
690 -- code is ignored and preference is given to id.
691 --
692 FUNCTION Valid_freight_type
693 (X_in_id in number,
694 X_in_code in varchar2) RETURN number is
695 BEGIN
696 declare
697 cursor sofct is
698 SELECT freight_charge_type_id
699 FROM so_freight_charge_types
700 WHERE freight_charge_type_id = nvl(X_in_id, freight_charge_type_id)
701 AND freight_charge_type_code = decode(X_in_id,null,X_in_code,freight_charge_type_code )
702 AND nvl(start_date_active,sysdate) <= sysdate
703 AND nvl(end_date_active,sysdate) >= sysdate;
704 x_out_id number;
705 begin
706 open sofct;
707 fetch sofct into X_out_id;
708 close sofct;
709
710
711
712 return (x_out_id);
713
714 exception when others then
715 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
716 FND_MESSAGE.Set_Token('PACKAGE','WSH_DEL_OI_CORE.valid_freight_type');
717 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
718 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
719 APP_EXCEPTION.Raise_Exception;
720 end;
721 END Valid_freight_type;
722
723
724
725 -- Name : Valid_carrier_code
726 --
727 -- Notes
728 -- validates carrier code /ship_method_code
729 --
730 FUNCTION Valid_carrier_code
731 (X_organization_id in number,
732 X_carrier_code in varchar2) RETURN BOOLEAN is
733 BEGIN
734 declare
735 cursor car_code is
736 SELECT 'valid'
737 FROM org_freight
738 WHERE organization_id = x_organization_id
739 AND freight_code = x_carrier_code
740 AND nvl(disable_date, sysdate) >= sysdate;
741
745 fetch car_code into sql_dummy;
742 sql_dummy varchar2(30);
743 begin
744 open car_code;
746 close car_code;
747
748 if sql_dummy = 'valid' then
749 return (TRUE);
750 else
751 wsh_del_oi_core.println('Invalid Carrier code.');
752 return(FALSE);
753 end if;
754
755 exception when others then
756 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
757 FND_MESSAGE.Set_Token('PACKAGE','WSH_DEL_OI_CORE.valid_freight_type');
758 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
759 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
760 APP_EXCEPTION.Raise_Exception;
761 end;
762 END Valid_Carrier_Code;
763
764
765 -- Name : Validate_container_id
766 --
767 -- Notes
768 -- validates container_id / sequence number is valid for this
769 -- delivery
770 --
771
772 FUNCTION Validate_Container_Id
773 (X_container_id in number,
774 X_sequence_number in number,
775 x_delivery_id in number,
776 error_code in out varchar2) return NUMBER is
777 BEGIN
778 declare
779
780 -- use either (1) container_id - the PK
781 -- or (2) delivery_id,sequence_number - concatenated key
782
783 cursor container_lookup is
784 SELECT container_id
785 FROM wsh_packed_containers
786 WHERE container_id = nvl(x_container_id, container_id)
787 AND sequence_number = decode(x_container_id, null, x_sequence_number, sequence_number)
788 AND delivery_id = x_delivery_id;
789
790 container_id number;
791
792 begin
793
794 open container_lookup;
795 fetch container_lookup into container_id;
796
797 if container_lookup%NOTFOUND THEN
798 wsh_del_oi_core.println('Validate_container_id - container_lookup Cursor not found');
799
800 close container_lookup;
801
802 if x_container_id is not null then
803 wsh_del_oi_core.println('Validate_container_id container_id is not null');
804 error_code := 'WSH_OI_INVALID_CONTAINER';
805 return (container_id);
806 end if;
807
808 container_id := null;
809
810 else
811 close container_lookup;
812 end if;
813
814 return (container_id);
815
816 exception
820 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
817 when others then
818 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
819 FND_MESSAGE.Set_Token('PACKAGE','WSH_DEL_OI_CORE.validate_container_id');
821 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
822 APP_EXCEPTION.Raise_Exception;
823 end;
824 END Validate_container_id;
825
826
827 -- Name : Validate_currency
828 --
829 -- Notes
830 -- validates currency code/name. If both are supplied,
831 -- name is ignored and preference is given to code.
832 --
833 PROCEDURE VALIDATE_CURRENCY
834 (X_in_code in varchar2,
835 X_in_name in varchar2,
836 X_amount in number,
837 X_out_code out varchar2,
838 X_out_name out varchar2,
839 error_code in out varchar2) is
840 BEGIN
841
842 declare
843 cursor currency_cursor is
844 SELECT currency_code,
845 name,
846 nvl(precision,0),
847 decode(instr(to_char(nvl(x_amount,0)),'.'),0,0,
848 length(to_char(nvl(x_amount,0)))-
849 instr(to_char(nvl(x_amount,0)),'.'))
850 FROM fnd_currencies_VL
851 WHERE enabled_flag = 'Y'
852 AND name = decode( X_in_code, null, X_in_name, name)
853 AND currency_code = nvl( X_in_code, currency_code)
854 AND trunc(sysdate) between nvl( start_date_active, trunc(sysdate) )
855 and nvl( end_date_active, trunc(sysdate) );
856 x_precision number;
857 x_in_precision number;
858
859 begin
860 if x_in_code is null and x_in_name is null then
861 error_code := 'CURR-No code';
862 FND_MESSAGE.set_name('FND', error_code);
863 else
864 open currency_cursor;
865 fetch currency_cursor
866 into X_out_code, X_out_name, X_precision, X_in_precision;
867 if currency_cursor%notfound then
868 if x_in_code is not null then
869 error_code := 'CURR-Invalid code';
870 FND_MESSAGE.set_name('FND', error_code);
871 FND_MESSAGE.set_token('CODE',x_in_code);
872 else
873 error_code := 'CURR-Invalid currency value';
874 FND_MESSAGE.set_name('FND', error_code);
875 end if;
876 end if;
877 close currency_cursor;
878 if x_in_precision > x_precision then
879 error_code := 'CURR-Precision';
883 end if;
880 FND_MESSAGE.set_name('FND', error_code);
881 FND_MESSAGE.set_token('PRECISON',to_char(x_precision));
882 end if;
884 exception when others then
885 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
886 FND_MESSAGE.Set_Token('PACKAGE','WSH_DEL_OI_CORE.validate_currency');
887 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
888 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
889 APP_EXCEPTION.Raise_Exception;
890 end;
891
892 END validate_currency;
893
894 -- Name : Validate_uom
895 --
896 -- Notes
897 -- validates uom code/desc. If both are supplied,
898 -- desc is ignored and preference is given to code.
899 --
900 PROCEDURE VALIDATE_UOM
901 (X_class in varchar2,
902 X_uom_code in out varchar2,
903 X_uom_desc in varchar2,
904 error_code in out varchar2) is
905 BEGIN
906 declare
907 cursor uom_cursor (class varchar2, uomcode varchar2, uomdesc varchar2) is
908 SELECT uom_code
909 FROM mtl_units_of_measure
910 WHERE unit_of_measure = decode(uomcode,'',uomdesc,unit_of_measure)
911 AND uom_code = nvl(uomcode,uom_code)
912 AND uom_class = NVL(class,uom_class)
913 AND nvl(disable_date,sysdate) >= sysdate;
914 valid_uom_code varchar2(3);
915
916 begin
917 if (x_uom_code is not null)
918 or (x_uom_desc is not null) then
919 open uom_cursor(x_class, x_uom_code, x_uom_desc);
920 fetch uom_cursor into valid_uom_code;
921 if uom_cursor%notfound then
922 error_code := 'WSH_OI_INVALID_UOM';
923 else
924 x_uom_code := valid_uom_code;
925 end if;
926 close uom_cursor;
927 end if;
928 exception when others then
929 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
930 FND_MESSAGE.Set_Token('PACKAGE','WSH_DEL_OI_CORE.validate_uom');
931 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
932 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
933 APP_EXCEPTION.Raise_Exception;
934 end;
935
936 END validate_uom;
937
938 -- Name : Validate_User
939 --
940 -- Notes
941 -- validates User Id/Name. If both are supplied,
942 -- Name is ignored and preference is given to Id.
943 --
944 PROCEDURE VALIDATE_USER
945 (X_user_id in out number,
946 X_user_name in varchar2,
947 error_code in out varchar2) is
948 BEGIN
949 declare
953 WHERE user_id = NVL(userid,user_id)
950 cursor user_lookup (userid in number, username in varchar2) is
951 SELECT user_id
952 FROM fnd_user
954 AND user_name = DECODE(userid,null,username,user_name)
955 AND nvl(start_date , sysdate) <= sysdate
956 AND nvl(end_date,sysdate) >= sysdate;
957 valid_user_id number;
958
959 begin
960 if (x_user_id is not null)
961 or (x_user_name is not null) then
962 open user_lookup(x_user_id, x_user_name);
963 fetch user_lookup into valid_user_id;
964 if user_lookup%notfound then
965 error_code := 'WSH_OI_INVALID_USER';
966 else
967 x_user_id := valid_user_id;
968 end if;
969 close user_lookup;
970 end if;
971 exception when others then
972 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
973 FND_MESSAGE.Set_Token('PACKAGE','WSH_DEL_OI_CORE.validate_user');
974 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
975 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
976 APP_EXCEPTION.Raise_Exception;
977 end;
978
979 END validate_user;
980
981 -- Name : Update_shipping_online
982 --
983 -- Notes : Must be called after a commit.
984 PROCEDURE UPDATE_SHIPPING_ONLINE
985 (x_picking_header_id in number,
986 x_batch_id in number) IS
987 BEGIN
988 declare x_dummy varchar2(250);
989 x_return_msg varchar2(50);
990 error_code_num number;
991 begin
992 -- Call the Shipping TM to run Update Shipping Program (includes inv interface)
993 error_code_num:= FND_TRANSACTION.Synchronous
994 (timeout => 1000,
995 outcome => x_dummy,
996 message => x_return_msg,
997 application => 'OE',
998 program => 'OEBSCO',
999 arg_1 => '0',
1000 arg_2 => to_char(x_picking_header_id),
1001 arg_3 => fnd_profile.value( 'SO_RESERVATIONS'),
1002 arg_4 => to_char(fnd_global.user_id),
1003 arg_5 => to_char(fnd_global.login_id),
1004 arg_6 => to_char(x_batch_id),
1005 arg_7 => chr(0));
1006
1007
1008 wsh_del_oi_core.println('OEBSCO Returned with status '||x_dummy||' msg '||x_return_msg
1009 ||' and code = '||to_char(error_code_num));
1010
1011 IF (error_code_num = 2) THEN
1012 FND_MESSAGE.Set_Name('OE','OE_SH_PROCESS_ONLINE_NO_MGR');
1013
1014 ELSIF (error_code_num <> 0) THEN
1015 FND_MESSAGE.Set_Name('OE','OE_SH_PROCESS_ONLINE_ERROR');
1019 error_code_num := FND_TRANSACTION.get_values(x_return_msg,
1016
1017 ELSE
1018 IF (x_return_msg = 'FAILURE') THEN
1020 x_dummy, x_dummy, x_dummy, x_dummy, x_dummy,
1021 x_dummy, x_dummy, x_dummy, x_dummy, x_dummy,
1022 x_dummy, x_dummy, x_dummy, x_dummy, x_dummy,
1023 x_dummy, x_dummy, x_dummy, x_dummy);
1024
1025 IF (x_return_msg = 'SUCCESS') THEN
1026 FND_MESSAGE.Set_Name('OE','OE_SH_PROCESS_ONLINE_FAILED');
1027 FND_MESSAGE.Set_Token('PROCESS','OE_SH_INVENTORY_INTERFACE');
1028
1029 ELSE
1030 FND_MESSAGE.Set_Name('OE','OE_SH_PROCESS_ONLINE_FAILED');
1031 FND_MESSAGE.Set_Token('PROCESS','OE_SH_UPDATE_SHIPPING_INFO');
1032
1033 END IF;
1034 END IF;
1035 END IF;
1036
1037
1038 end;
1039 END UPDATE_SHIPPING_ONLINE;
1040
1041
1042
1043 -- Name Ship_multi_org
1044 -- Purpose:
1045 -- used when checking for multi-org. Ensures order's org is same as current org
1046 -- where current org is the org on any/all orders in the so_headers view
1047
1048 FUNCTION Ship_multi_org (X_picking_header_id in number ) return BOOLEAN is
1049 BEGIN
1050 declare
1051 CURSOR multi_org_check is
1052 SELECT 'SHIP_DIFF_ORG'
1053 FROM so_headers_all h,
1054 so_picking_headers_all ph
1055 WHERE h.header_id = ph.order_header_id
1056 AND ph.picking_header_id = X_picking_header_id
1057 AND nvl(h.org_id,-99) <>
1058 (SELECT nvl(h2.org_id,-99)
1059 FROM so_headers h2
1060 WHERE rownum = 1);
1061
1062 x_msg_name VARCHAR2(40);
1063
1064 begin
1065 -- ensure org is in the same org as we are in
1066 OPEN multi_org_check;
1067 FETCH multi_org_check INTO x_msg_name;
1068 IF multi_org_check%notfound then
1069 CLOSE multi_org_check;
1070 return(FALSE);
1071 ELSE
1072 CLOSE multi_org_check;
1073 wsh_del_oi_core.println('Multi_org_check returns FALSE');
1074 return(TRUE);
1075 END IF;
1076 end;
1077 END SHIP_MULTI_ORG;
1078
1079
1080 ---- write routines for debug purpose ---------------*/
1081 ---- personal profile oe_debug_level = 1 - 3. This governs the detail
1082 ---- of the messages in debug, 1 is most general and 3 is very detailed.
1083 PROCEDURE print(msg IN VARCHAR2) IS
1084 BEGIN
1085 if not suppress_print then
1086 fnd_file.put(FND_FILE.LOG, substr(msg,1,255));
1087 end if;
1088 END;
1089
1090 PROCEDURE println IS
1091 BEGIN
1092 if not suppress_print then
1093 IF NVL(FND_PROFILE.Value('OE_DEBUG_LEVEL'),0) in ('2','3') THEN
1094 fnd_file.new_line(FND_FILE.LOG, 1);
1095 END IF;
1096 end if;
1097 END;
1098
1099 PROCEDURE println(msg IN VARCHAR2) IS
1100 BEGIN
1101 if not suppress_print then
1102 IF NVL(FND_PROFILE.Value('OE_DEBUG_LEVEL'),0) in ('2','3') THEN
1103 fnd_file.put_line(FND_FILE.LOG, substr(msg,1,255));
1104 END IF;
1105 end if;
1106 END;
1107
1108 -- Name : Validate_SO_Code
1109 --
1110 -- Notes
1111 -- validates a code in so_lookups.
1112 -- If both code/meaning are supplied for validation,
1113 -- meaing is ignored and preference is given to internal code.
1114 --
1115 PROCEDURE Validate_so_code
1116 (X_lookup_type in varchar2,
1117 X_code in out varchar2,
1118 X_meaning in varchar2,
1119 X_error_code in out varchar2) is
1120
1121 BEGIN
1122 declare
1123 cursor so_code_lookup is
1124 SELECT lookup_code
1125 FROM so_lookups
1126 WHERE lookup_type = X_LOOKUP_TYPE
1127 AND lookup_code = nvl(X_code,lookup_code)
1128 AND meaning = decode(X_code,null,X_meaning,meaning)
1129 AND nvl(start_date_active,sysdate) <= sysdate
1130 AND nvl(end_date_active,sysdate) >= sysdate
1131 AND enabled_flag = 'Y';
1132
1133 valid_code varchar2(30);
1134 begin
1135 open so_code_lookup;
1136 fetch so_code_lookup into valid_code;
1137
1138 if so_code_lookup%notfound then
1139 x_error_code := '1';
1140 else
1141 x_error_code := '0';
1142 x_code := valid_code;
1146
1143 end if;
1144
1145 close so_code_lookup;
1147 exception when others then
1148 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
1149 FND_MESSAGE.Set_Token('PACKAGE','WSH_DEL_OI_CORE.validate_so_code');
1150 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
1151 FND_MESSAGE.Set_Token('ORA_TEXT',SQLERRM);
1152 APP_EXCEPTION.Raise_Exception;
1153 end;
1154 END Validate_so_code;
1155
1156
1157 end WSH_DEL_OI_CORE;
[Home] [Help]