1 package body CTO_WORKBENCH_UTIL_PK as
2 /* $Header: CTOWBUTB.pls 120.15.12020000.2 2012/07/05 09:41:41 ntungare ship $ */
3 /********************************************************************************************************
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved, |
6 | Oracle Manufacturing |
7 | File Name : CTOWBUTB.pls |
8 | |
9 | Description : This is the Utility pkg for CTO workbench. CTO work bench is the |
10 | Self service application and we need lot of funtions to call in the sql |
11 | This file is not having any other product dependency. |
12 | History : Created on 11-NOV-2002 by Renga Kannan |
13 |
14 |
15 | 28-Jan-2003 Renga Kannan
16 | Modified the code for numeric value error.
17 | 30-Jun-2005 Renga Kannan
18 | Modified code for Cross docking project.
19 ********************************************************************************************************/
20
21
22 FUNCTION get_line_number
23 (
24 p_ato_line_id IN Number,
25 p_line_id IN NUMBER,
26 p_item_type_code IN VARCHAR2,
27 p_Line_Number IN NUMBER,
28 p_Shipment_Number IN NUMBER,
29 p_Option_Number IN NUMBER,
30 p_Component_Number IN NUMBER ,
31 p_Service_Number IN NUMBER
32 ) Return varchar2 IS
33 l_concat_value Varchar2(100);
34 line_number oe_order_lines_all.line_number%type;
35 shipment_number oe_order_lines_all.shipment_number%type;
36 option_number oe_order_lines_all.option_number%type;
37 component_number oe_order_lines_all.component_number%type;
38 service_number oe_order_lines_all.service_number%type;
39
40 BEGIN
41
42 If p_ato_line_id = p_line_id and p_item_type_code in ('MODEL','CLASS') then
43
44 BEGIN -- fp-J : added BEGIN/END block
45 Select Line_number,
46 shipment_number,
47 option_number,
48 Component_number,
49 Service_number
50 into line_number,
51 shipment_number,
52 option_number,
53 component_number,
54 service_number
55 from oe_order_lines_all
56 where ato_line_id = p_ato_line_id
57 and item_type_code = 'CONFIG';
58
59 EXCEPTION --- fp-J: added EXCEPTION block
60 when no_data_found then -- if config does not exist.
61 line_number := p_line_number;
62 shipment_number := p_shipment_number;
63 option_number := p_option_number;
64 component_number := p_component_number;
65 service_number := p_service_number;
66 END;
67
68 else
69
70 line_number := p_line_number;
71 shipment_number := p_shipment_number;
72 option_number := p_option_number;
73 component_number := p_component_number;
74 service_number := p_service_number;
75
76 end if;
77
78 return get_order_line_number(p_line_number =>line_number,
79 p_service_number => service_number,
80 p_option_number => option_number,
81 p_component_number => component_number,
82 p_shipment_number => shipment_number);
83
84 end get_line_number;
85
86
87 --Modified by Renga Kannan on 02/12/03. Calling OM API to get the flow status. We have dependency
88 -- With OM for this and OM gave a pre-req ARU on this. The Prereq ARU no is 2748513
89
90 FUNCTION get_line_status (
91 p_Line_Id IN NUMBER,
92 p_Ato_Line_Id IN NUMBER,
93 p_Item_Type_code IN Varchar2,
94 p_flow_status IN Varchar2) Return Varchar2 is
95 l_flow_status Varchar2(100):=null;
96 l_line_id oe_order_lines_all.line_id%type;
97 l_flow_status_code oe_order_lines_all.flow_status_code%type;
98 Begin
99
100 l_flow_status := p_flow_status;
101 if (p_Line_id = p_ato_line_id and p_item_type_code in ('MODEL', 'CLASS')) then
102
103 select line_id,
104 flow_status_code
105 into l_line_id,
106 l_flow_status_code
107 from oe_order_lines_all oel
108 where oel.ato_line_id = p_ato_line_id
109 and oel.item_type_code = 'CONFIG';
110 else
111
112 l_line_id := p_line_id;
113 l_flow_status_code := p_flow_status;
114 end if;
115 l_flow_status := oe_line_status_pub.get_line_status(p_line_id => l_line_id,
116 p_flow_status_code => l_flow_status_code);
117 return l_flow_status;
118 End Get_line_status;
119
120
121 Function Get_supply_type (P_line_id IN Number,
122 p_ato_line_id IN Number,
123 p_item_type IN Varchar2,
124 p_source_type IN Varchar2) return Varchar2 is
125 l_demand_line_id Number;
126 l_source_type_id Number;
127 l_supply_type Number := 0;
128 l_source_document_type_id Number;
129 v_wip_quantity Number;
130 v_flow_count Number;
131 v_po_quantity Number;
132 v_req_quantity Number;
133 v_ireq_quantity Number;
134 v_ds_po_quantity Number;
135 v_ds_req_quantity Number;
136 v_asn_quantity Number;
137 l_result Number;
138 x_result Varchar2(200);
139
140
141 Begin
142
143 --
144 -- Get demand line
145 --
146 If (p_ato_line_id is not null) then
147
148 --Adding INCLUDED item type code for SUN ER#9793792
149 --if (p_item_type in ('OPTION','STANDARD')) then
150 if (p_item_type in ('OPTION','STANDARD','INCLUDED')) then
151
152 l_demand_line_id := p_line_id;
153
154 elsif (p_ato_line_id = p_line_id and p_item_type in ('MODEL', 'CLASS')) then
155
156 select line_id
157 into l_demand_line_id
158 from oe_order_lines_all
159 where ato_line_id = p_ato_line_id
160 and item_type_code = 'CONFIG';
161
162 else
163 return null;
164 end if;
165 else
166 l_demand_line_id := p_line_id;
167 end if;
168
169 --
170 -- Dropship line
171 --
172 IF nvl(p_source_type, 'INTERNAL') = 'EXTERNAL' THEN
173
174 select count(*)
175 into v_ds_po_quantity
176 from oe_drop_ship_sources ods
177 where ods.line_id = l_demand_line_id
178 --and ods.drop_ship_source_id = 2
179 and ods.po_header_id is not null;
180
181 IF (v_ds_po_quantity > 0) THEN
182 l_result := 5;
183 END IF;
184
185 select count(*)
186 into v_ds_req_quantity
187 from oe_drop_ship_sources ods
188 where ods.line_id = l_demand_line_id
189 --and ods.drop_ship_source_id = 2
190 and ods.po_header_id is null;
191
192 IF (v_ds_req_quantity > 0) THEN
193 IF (l_result <> 0) THEN
194 l_result := 7;
195 select meaning
196 into x_result
197 from mfg_lookups
198 where lookup_type = 'CTO_WB_SUPPLY_TYPE'
199 and lookup_code = l_result;
200
201 return x_result;
202 ELSE
203 l_result := 6;
204 END IF;
205 END IF;
206
207 select meaning
208 into x_result
209 from mfg_lookups
210 where lookup_type = 'CTO_WB_SUPPLY_TYPE'
211 and lookup_code = l_result;
212
213 --
214 -- Internal line
215 --
216 ELSE
217
218 --
219 -- Get source document id
220 --
221 l_source_document_type_id := CTO_WORKBENCH_UTIL_PK.get_source_document_id ( pLineId => l_demand_line_id );
222
223
224 select nvl(sum(reservation_quantity),0)
225 into v_wip_quantity
226 from mtl_reservations
227 where demand_source_type_id = decode (l_source_document_type_id, 10,
228 inv_reservation_global.g_source_type_internal_ord,
229 inv_reservation_global.g_source_type_oe )
230 and demand_source_line_id = l_demand_line_id
231 and supply_source_type_id = inv_reservation_global.g_source_type_wip;
232
233 IF (v_wip_quantity > 0) THEN
234 l_result := 1;
235 END IF;
236
237 select count(*)
238 into v_flow_count
239 from wip_flow_schedules
240 where demand_source_type = inv_reservation_global.g_source_type_oe
241 and demand_source_line =to_char(l_demand_line_id)
242 and status = 1;
243
244 IF (v_flow_count > 0) THEN
245 IF (l_result <> 0) THEN
246 l_result := 7;
247 select meaning
248 into x_result
249 from mfg_lookups
250 where lookup_type = 'CTO_WB_SUPPLY_TYPE'
251 and lookup_code = l_result;
252
253 return x_result;
254 ELSE
255 l_result := 2;
256 END IF;
257 END IF;
258
259 select nvl(sum(reservation_quantity),0)
260 into v_po_quantity
261 from mtl_reservations
262 where demand_source_type_id = decode (l_source_document_type_id,
263 10, inv_reservation_global.g_source_type_internal_ord,
264 inv_reservation_global.g_source_type_oe )
265 and demand_source_line_id = l_demand_line_id
266 and supply_source_type_id = inv_reservation_global.g_source_type_po;
267
268 IF (v_po_quantity > 0) THEN
269 IF (l_result <> 0) THEN
270 l_result := 7;
271 select meaning
272 into x_result
273 from mfg_lookups
274 where lookup_type = 'CTO_WB_SUPPLY_TYPE'
275 and lookup_code = l_result;
276
277 return x_result;
278 ELSE
279 l_result := 3;
280 END IF;
281 END IF;
282
283 select nvl(sum(reservation_quantity), 0)
284 into v_req_quantity
285 from mtl_reservations
286 where demand_source_type_id = decode (l_source_document_type_id,
287 10, inv_reservation_global.g_source_type_internal_ord,
288 inv_reservation_global.g_source_type_oe )
289 and demand_source_line_id = l_demand_line_id
290 and supply_source_type_id = inv_reservation_global.g_source_type_req;
291
292 IF (v_req_quantity > 0) THEN
293 IF (l_result <> 0) THEN
294 l_result := 7;
295 select meaning
296 into x_result
297 from mfg_lookups
298 where lookup_type = 'CTO_WB_SUPPLY_TYPE'
299 and lookup_code = l_result;
300
301 return x_result;
302 ELSE
303 l_result := 4;
304 END IF;
305 END IF;
306
307 -- rkaza. 05/19/2005. ireq project.
308 select nvl(sum(reservation_quantity), 0)
309 into v_ireq_quantity
310 from mtl_reservations
311 where demand_source_type_id = decode (l_source_document_type_id,
312 10, inv_reservation_global.g_source_type_internal_ord,
313 inv_reservation_global.g_source_type_oe )
314 and demand_source_line_id = l_demand_line_id
315 and supply_source_type_id = inv_reservation_global.g_source_type_internal_req;
316
317 IF (v_ireq_quantity > 0) THEN
318 IF (l_result <> 0) THEN
319 l_result := 7;
320 select meaning
321 into x_result
322 from mfg_lookups
323 where lookup_type = 'CTO_WB_SUPPLY_TYPE'
324 and lookup_code = l_result;
325
326 return x_result;
327 ELSE
328 l_result := 8; -- IR
329 END IF;
330 END IF;
331 -- Added by Renga Kannan on 30-Jun-2005 for Cross docking project
332
333 select nvl(sum(reservation_quantity), 0)
334 into v_asn_quantity
335 from mtl_reservations
336 where demand_source_type_id = decode (l_source_document_type_id,
337 10, inv_reservation_global.g_source_type_internal_ord,
338 inv_reservation_global.g_source_type_oe )
339 and demand_source_line_id = l_demand_line_id
340 and supply_source_type_id = inv_reservation_global.g_source_type_asn;
341
342 IF (v_asn_quantity > 0) THEN
343 IF (l_result <> 0) THEN
344 l_result := 7;
345 select meaning
346 into x_result
347 from mfg_lookups
348 where lookup_type = 'CTO_WB_SUPPLY_TYPE'
349 and lookup_code = l_result;
350
351 return x_result;
352 ELSE
353 l_result := 9; -- ASN
354 END IF;
355 END IF;
356
357 select meaning
358 into x_result
359 from mfg_lookups
360 where lookup_type = 'CTO_WB_SUPPLY_TYPE'
361 and lookup_code = l_result;
362
363 END IF; /* internal source type */
364
365 return x_result;
366
367 End Get_supply_type;
368
369
370 Function Get_config_line_id (P_ato_line_id IN Number,
371 p_line_id IN Number,
372 p_item_type IN Varchar2) return number as
373
374 l_config_line_id oe_order_lines_all.line_id%type;
375
376 Begin
377 l_config_line_id := p_line_id;
378
379 if p_ato_line_id = p_line_id and p_item_type in ('MODEL', 'CLASS') then
380
381 /*Bugfix 11840896: Not populating the data in the table. Consider the following
382 scenario:
383 A SO was progressed to create star item C1 with line_id = 1. This line_id
384 value is stored in the pl/sql table. Without closing the form, the SO is
385 reconfigured. This causes C1 to get delinked. When the SO is progressed again,
386 it creates C2 with line_id = 2. This API was still returning config_line_id as
387 1 because the table was not being cleared.
388
389 if config_line_id_tbl.exists(p_ato_line_id) then
390 l_config_line_id := config_line_id_tbl(p_ato_line_id);
391 else
392 */
393 select line_id into l_config_line_id
394 from oe_order_lines_all
395 where ato_line_id = p_ato_line_id
396 and item_type_code = 'CONFIG';
397
398 /*
399 config_line_id_tbl(p_ato_line_id) := l_config_line_id;
400
401 end if;
402 */
403
404 end if;
405
406 return l_config_line_id;
407
408 Exception When Others then
409 return p_line_id;
410
411 End Get_Config_Line_id;
412
413
414 Function Get_Item_Name (P_ato_line_id IN Number,
415 p_line_id IN Number,
416 p_item_type IN Varchar2,
417 p_item_name IN Varchar2,
418 p_config_item IN Number,
419 p_ship_org_id IN Number) return Varchar2 as
420 l_item_name varchar2(1000);
421 Begin
422 l_item_name := p_item_name;
423 If p_ato_line_id is not null and p_config_item is not null and p_item_type in ('MODEL','CLASS') then
424 -- Fixed bug 5447062
425 -- replaced org condition with rownum
426
427 select concatenated_segments
428 into l_item_name
429 from mtl_system_items_kfv
430 where inventory_item_id = p_config_item
431 and rownum =1;
432
433 elsif p_ato_line_id is not null and p_config_item is null and p_item_type in ('MODEL','CLASS') then
434
435 select concatenated_segments
436 into l_item_name
437 from mtl_system_items_kfv mtl,
438 oe_order_lines_all oel
439 where oel.ato_line_id = p_ato_line_id
440 and oel.item_type_code = 'CONFIG'
441 and oel.inventory_item_id = mtl.inventory_item_id
442 and mtl.organization_id = p_ship_org_id;
443
444 end if;
445
446 return l_item_name;
447 End Get_item_name;
448
449
450 Function Get_Item_Desc (P_ato_line_id IN Number,
451 p_line_id IN Number,
452 p_item_type IN Varchar2,
453 p_item_desc IN Varchar2,
454 p_config_item IN Number,
455 p_ship_org_id IN Number) return Varchar2 as
456 l_item_desc mtl_system_items_kfv.description%type;
457 Begin
458
459 -- Commenting as part of bugfix 8453372
460 -- l_item_desc := p_item_desc;
461
462 If p_ato_line_id is not null and p_config_item is not null and p_item_type in ('MODEL','CLASS') then
463
464 -- Changed this sql as part of bugfix 8453372. The sql now picks up data from tl table.
465 -- Fixed bug 5447062
466 -- replaced org condition with rownum
467 /*select description
468 into l_item_desc
469 from mtl_system_items_kfv
470 where inventory_item_id = p_config_item
471 and rownum = 1;*/
472
473 select description
474 into l_item_desc
475 from mtl_system_items_tl
476 where inventory_item_id = p_config_item
477 and language = userenv('LANG')
478 and rownum = 1;
479
480 elsif p_ato_line_id is not null and p_config_item is null and p_item_type in ('MODEL','CLASS') then
481
482 -- Changed this sql as part of bugfix 8453372. The sql now picks up data from tl table.
483 /*select description
484 into l_item_desc
485 from mtl_system_items_kfv mtl,
486 oe_order_lines_all oel
487 where mtl.inventory_item_id = oel.inventory_item_id
488 and mtl.organization_id = p_ship_org_id
489 and oel.ato_line_id = p_ato_line_id
490 and oel.item_type_code = 'CONFIG';*/
491
492 select description
493 into l_item_desc
494 from mtl_system_items_tl mtl,
495 oe_order_lines_all oel
496 where mtl.inventory_item_id = oel.inventory_item_id
497 and mtl.organization_id = p_ship_org_id
498 and mtl.language = userenv('LANG')
499 and oel.ato_line_id = p_ato_line_id
500 and oel.item_type_code = 'CONFIG';
501
502 -- Added this condition for ATO Item ordered independently. Done as part of bugfix 8453372.
503 --elsif p_ato_line_id is not null and p_item_type in ('STANDARD', 'OPTION') then
504 --Adding INCLUDED item type code for SUN ER#9793792
505 elsif p_ato_line_id is not null and p_item_type in ('STANDARD', 'OPTION', 'INCLUDED') then
506 select description
507 into l_item_desc
508 from mtl_system_items_tl mtl,
509 oe_order_lines_all oel
510 where mtl.inventory_item_id = oel.inventory_item_id
511 and mtl.organization_id = p_ship_org_id
512 and oel.ato_line_id = p_ato_line_id
513 and mtl.language = userenv('LANG');
514
515 end if;
516
517 return l_item_desc;
518 End Get_item_desc;
519
520
521 FUNCTION get_source_document_id (pLineId in number) RETURN NUMBER
522 IS
523 l_source_document_type_id number;
524 BEGIN
525
526 select h.source_document_type_id
527 into l_source_document_type_id
528 from oe_order_headers_all h, oe_order_lines_all l
529 where h.header_id = l.header_id
530 and l.line_id = pLineId
531 and rownum = 1;
532
533 return (l_source_document_type_id);
534
535 END get_source_document_id;
536
537
538 FUNCTION convert_uom(from_uom IN VARCHAR2,
539 to_uom IN VARCHAR2,
540 quantity IN NUMBER,
541 item_id IN NUMBER )
542 RETURN NUMBER
543 IS
544 this_item NUMBER;
545 to_rate NUMBER;
546 from_rate NUMBER;
547 result NUMBER;
548
549 BEGIN
550 IF from_uom = to_uom THEN
551 result := quantity;
552 ELSIF from_uom IS NULL
553 OR to_uom IS NULL THEN
554 result := 0;
555 ELSE
556 result := INV_CONVERT.inv_um_convert(item_id,
557 5,
558 quantity,
559 from_uom,
560 to_uom,
561 NULL,
562 NULL);
563
564 if result = -99999 then
565 result := 0;
566 end if;
567 END IF;
568 RETURN result;
569
570 END convert_uom;
571
572 FUNCTION Get_Buyer_Name (P_suggested_buyer_id IN Varchar2)
573 RETURN Varchar2 IS
574
575 l_buyer_name Varchar2(2000);
576
577 BEGIN
578
579 l_buyer_name := null;
580
581 select full_name
582 into l_buyer_name
583 from per_people_f
584 where person_id = P_suggested_buyer_id;
585
586 return l_buyer_name;
587
588 EXCEPTION
589 WHEN NO_DATA_FOUND THEN
590 return l_buyer_name;
591
592 WHEN OTHERS THEN
593 return l_buyer_name;
594
595 END Get_Buyer_Name;
596
597 /* fp-J project: Added a new function Get_Workbench_Item_Type */
598
599 FUNCTION Get_WorkBench_Item_Type
600 ( p_header_id IN NUMBER
601 ,p_top_model_line_id IN NUMBER
602 ,p_ato_line_id IN NUMBER
603 ,p_line_id IN NUMBER
604 ,p_item_type_code IN VARCHAR2
605 ) RETURN varchar2
606 IS
607 x_wb_item_type VARCHAR2(20) := null;
608 BEGIN
609
610
611 IF p_header_id is not null AND
612 p_line_id is null THEN
613
614 x_wb_item_type := 'HEAD';
615
616 ElSIF p_top_model_line_id is not null AND
617 p_ato_line_id is null AND
618 p_item_type_code = 'MODEL' THEN
619
620 x_wb_item_type := 'PTO';
621
622 ElSIF p_ato_line_id = p_line_id AND
623 (p_item_type_code = 'MODEL' OR
624 p_item_type_code = 'CLASS') THEN
625
626 x_wb_item_type := 'MDL';
627
628 ELSE
629
630 IF p_ato_line_id = p_line_id AND
631 (p_item_type_code = 'STANDARD' OR
632 --Adding INCLUDED item type code for SUN ER#9793792
633 p_item_type_code = 'OPTION' OR
634 p_item_type_code = 'INCLUDED') THEN
635
636 x_wb_item_type := 'ATO';
637
638 ELSIF p_item_type_code = 'CONFIG' THEN
639
640 x_wb_item_type := 'CFG';
641
642 ELSE
643
644 x_wb_item_type := 'STD';
645
646 END IF;
647
648 END IF;
649
650 RETURN x_wb_item_type;
651
652 END Get_WorkBench_Item_Type;
653
654 FUNCTION Get_Rsvd_on_hand_qty(
655 p_line_id IN Number,
656 p_ato_line_id IN Number,
657 p_item_type_code IN varchar2) RETURN Number is
658 --l_prim_rsv_qty Number;
659 l_rsv_qty Number;
660 --l_prim_uom_code mtl_reservations.primary_uom_code%type;
661 l_line_id Number;
662
663 --Bugfix 13554996: New variable and cursor.
664 l_order_uom oe_order_lines_all.order_quantity_uom%type;
665 l_item_id mtl_system_items_kfv.inventory_item_id%type;
666
667 cursor c_on_hand_qty(x_line_id number, x_order_uom varchar2, x_item_id number) is
668 select CTO_UTILITY_PK.convert_uom
669 (primary_uom_code,
670 x_order_uom,
671 nvl(sum(primary_reservation_quantity), 0),
672 x_item_id,
673 lot_number,
674 max(organization_id)) primary_reservation_quantity
675 from mtl_reservations
676 where demand_source_line_id = x_line_id
677 and supply_source_TYpe_id = 13
678 group by primary_uom_code, lot_number;
679
680 Begin
681 -- -- Fixed bug 5199341
682 -- Added code to derive the config line id incase of ato model order lines
683 If p_ato_line_id is not null then
684 if p_ato_line_id = p_line_id and p_item_type_code in ('MODEL','CLASS') then
685 select line_id
686 into l_line_id
687 from oe_order_lines_all
688 where ato_line_id = p_ato_line_id
689 and item_type_code = 'CONFIG';
690 End if;
691 end if;
692
693 If l_line_id is null then
694 l_line_id := p_line_id;
695 End if;
696
697 /*Begin Bugfix 13554996: Converted this sql to a cursor
698 select sum(primary_reservation_quantity),primary_uom_code
699 into l_prim_rsv_qty, l_prim_uom_code
700 from mtl_reservations
701 where demand_source_line_id = l_line_id
702 and supply_source_TYpe_id = 13
703 group by primary_uom_code;
704
705 If l_prim_rsv_qty <> 0 then
706 select CTO_WORKBENCH_UTIL_PK.convert_uom(l_prim_uom_code,
707 oel.order_quantity_uom,
708 nvl(l_prim_rsv_qty,0),
709 oel.inventory_item_id)
710 into l_rsv_qty
711 from oe_order_lines_all oel
712 where oel.line_id = l_line_id;
713 else
714 l_rsv_qty := 0;
715 End if;
716 */
717
718 --Get order_quantity_uom and item_id from oel.
719 select oel.order_quantity_uom,
720 oel.inventory_item_id
721 into l_order_uom,
722 l_item_id
723 from oe_order_lines_all oel
724 where oel.line_id = l_line_id;
725
726 l_rsv_qty := 0;
727
728 for c_on_hand_qty_var in c_on_hand_qty(l_line_id, l_order_uom, l_item_id) loop
729 l_rsv_qty := l_rsv_qty + c_on_hand_qty_var.primary_reservation_quantity;
730 end loop;
731
732 return l_rsv_qty;
733
734 Exception
735 when no_data_found then --4752854,code review bug
736 --return 0 when no on-hand qty
737 return 0;
738 --Bugfix 13554996
739 when others then
740 return 0;
741 End Get_Rsvd_on_hand_qty;
742
743
744
745 FUNCTION get_last_available_date(p_ato_line_id IN number,
746 p_line_id IN Number,
747 p_item_type IN varchar2) RETURN date is
748
749 l_date date := null;
750 l_config_line_id number;
751 l_line_level varchar2(10);
752 l_return_status varchar2(20);
753
754 Begin
755
756 find_config_line_and_level(p_ato_line_id => p_ato_line_id,
757 p_line_id => p_line_id,
758 p_item_type => p_item_type,
759 x_config_line_id => l_config_line_id,
760 x_line_level => l_line_level,
761 x_return_status => l_return_status);
762
763 -- no config line id, return null.
764 if l_return_status in (fnd_api.g_ret_sts_error,
765 fnd_api.g_ret_sts_unexp_error) then
766 return null;
767 end if;
768
769 -- No need to process for lower level items.
770 if l_line_level = 'Lower' then
771 return null;
772 end if;
773
774 -- process for Top, Ato, Std items.
775 --
776 -- bug 6833994
777 -- Added a to_char() clause on the l_config_line_id
778 -- while querying from wip_flow_schedules
779 -- ntungare
780 --
781 Select max(exp_comp_date) into l_date
782 from
783 (SELECT wdj.scheduled_completion_date exp_comp_date
784 FROM mtl_reservations mr, wip_discrete_jobs wdj
785 WHERE mr.demand_source_type_id = decode(CTO_WORKBENCH_UTIL_PK.get_source_document_id(l_config_line_id), 10,8,2)
786 AND mr.demand_source_line_id = l_config_line_id
787 AND mr.supply_source_type_id = 5
788 AND wdj.wip_entity_id = mr.supply_source_header_id
789 AND wdj.organization_id = mr.organization_id
790
791 UNION
792
793 SELECT wfs.scheduled_completion_date exp_comp_date
794 FROM wip_flow_schedules wfs
795 WHERE wfs.demand_source_line = to_char(l_config_line_id)
796 AND wfs.status = 1
797
798 UNION
799
800 select nvl(poll.promised_date,poll.need_by_date) exp_comp_date
801 from mtl_reservations mr, po_line_locations_all poll
802 where mr.demand_source_type_id = 2
803 and mr.demand_source_line_id = l_config_line_id
804 and mr.supply_source_type_id = 1
805 and mr.supply_source_header_id = poll.po_header_id
806 and mr.supply_source_line_id = poll.line_location_id
807
808 UNION
809
810 select porl.need_by_date exp_comp_date
811 from mtl_reservations mr, po_requisition_lines_all porl
812 where mr.demand_source_type_id = 2
813 and mr.demand_source_line_id = l_config_line_id
814 and mr.supply_source_type_id in (7, 17)
815 and mr.supply_source_header_id = porl.requisition_header_id
816 and mr.supply_source_line_id = porl.requisition_line_id
817
818 UNION
819
820 select nvl(poll.promised_date,poll.need_by_date) exp_comp_date
821 from oe_drop_ship_sources ods, po_line_locations_all poll
822 where ods.line_id = l_config_line_id
823 and ods.po_header_id = poll.po_header_id
824 and ods.line_location_id = poll.line_location_id
825
826 UNION
827
828 select porl.need_by_date exp_comp_date
829 from oe_drop_ship_sources ods, po_requisition_lines_all porl
830 where ods.line_id = l_config_line_id
831 and ods.po_header_id is null
832 and ods.requisition_header_id = porl.requisition_header_id
833 and ods.requisition_line_id = porl.requisition_line_id
834
835 UNION
836
837 select asn_headers.expected_receipt_date exp_comp_date
838 from mtl_reservations mr,
839 rcv_shipment_lines ASN_LINES,
840 rcv_shipment_headers asn_headers
841 where mr.demand_source_type_id = 2
842 and mr.demand_source_line_id = l_config_line_id
843 and mr.supply_source_type_id = 25
844 and mr.supply_source_line_detail = ASN_LINES.shipment_line_id
845 and asn_headers.shipment_header_id = asn_lines.shipment_header_id
846 and ASN_LINES.asn_line_flag = 'Y');
847
848 return l_date;
849
850 Exception
851
852 When Others then
853 return null;
854
855 End get_last_available_date;
856
857
858 /*******************************************************************************************
859 -- API name : get_rsvd_inrcv_qty
860 -- Type : Public
861 -- Pre-reqs : INVRSVGS.pls
862 -- Function : Given config/ato item line id it returns
863 -- the qty reserved to in receiving supply
864 -- Parameters:
865 -- IN : p_line_id Expects the config/ato item order line id Required
866 --
867 -- Version :
868 --
869 --
870 ******************************************************************************************/
871
872
873 FUNCTION Get_Rsvd_inrcv_qty(
874 p_line_id IN Number,
875 p_ato_line_id IN Number,
876 p_item_type_code IN varchar2) RETURN Number is
877 l_prim_rsv_qty Number;
878 l_rsv_qty Number;
879 l_prim_uom_code mtl_reservations.primary_uom_code%type;
880 l_line_id Number;
881
882 Begin
883
884 -- -- Fixed bug 5199341
885 -- Added code to derive the config line id incase of ato model order lines
886
887 If p_ato_line_id is not null then
888 if p_ato_line_id = p_line_id and p_item_type_code in ('MODEL','CLASS') then
889 select line_id
890 into l_line_id
891 from oe_order_lines_all
892 where ato_line_id = p_ato_line_id
893 and item_type_code = 'CONFIG';
894 End if;
895 end if;
896 If l_line_id is null then
897 l_line_id := p_line_id;
898 End if;
899
900 select sum(primary_reservation_quantity),primary_uom_code
901 into l_prim_rsv_qty, l_prim_uom_code
902 from mtl_reservations
903 where demand_source_line_id = l_line_id
904 and supply_source_TYpe_id = 27
905 group by primary_uom_code;
906
907 If l_prim_rsv_qty <> 0 then
908 select CTO_WORKBENCH_UTIL_PK.convert_uom(l_prim_uom_code,
909 oel.order_quantity_uom,
910 nvl(l_prim_rsv_qty,0),
911 oel.inventory_item_id)
912 into l_rsv_qty
913 from oe_order_lines_all oel
914 where oel.line_id = l_line_id;
915 else
916 l_rsv_qty := 0;
917 End if;
918 return l_rsv_qty;
919 Exception when no_data_found then
920 return 0;
921 End Get_Rsvd_inrcv_qty;
922
923
924
925 Procedure find_config_line_and_level(P_ato_line_id IN Number,
926 p_line_id IN Number,
927 p_item_type IN Varchar2,
928 x_config_line_id OUT NOCOPY number,
929 x_line_level OUT NOCOPY varchar2,
930 x_return_status OUT NOCOPY varchar2) IS
931
932 Begin
933
934 x_return_status := fnd_api.g_ret_sts_success;
935
936 -- Std item
937 If p_ato_line_id is null then
938 x_config_line_id := p_line_id;
939 x_line_level := 'Std';
940 return;
941 end if;
942
943 -- Lower level. p_ato_line_id not null
944 if p_ato_line_id <> p_line_id then
945 x_config_line_id := p_line_id;
946 x_line_level := 'Lower';
947 return;
948 end if;
949
950 -- Top level or Ato case. p_ato_line_id not null and equal to p_line_id
951 if p_item_type in ('MODEL', 'CLASS') then
952
953 /*Bugfix 11840896: Not populating the data in the table. Consider the following
954 scenario:
955 A SO was progressed to create star item C1 with line_id = 1. This line_id
956 value is stored in the pl/sql table. Without closing the form, the SO is
957 reconfigured. This causes C1 to get delinked. When the SO is progressed again,
958 it creates C2 with line_id = 2. This API was still returning config_line_id as
959 1 because the table was not being cleared.
960
961 if config_line_id_tbl.exists(p_ato_line_id) then
962 x_config_line_id := config_line_id_tbl(p_ato_line_id);
963 else
964 */
965 select line_id into x_config_line_id
966 from oe_order_lines_all
967 where ato_line_id = p_ato_line_id
968 and item_type_code = 'CONFIG';
969
970 /*
971 config_line_id_tbl(p_ato_line_id) := x_config_line_id;
972 end if;
973 */
974
975 x_line_level := 'Top';
976
977 else -- Ato item
978
979 x_config_line_id := p_line_id;
980 x_line_level := 'Ato';
981
982 end if; -- if item_type in (model, class)
983
984 Exception
985
986 when FND_API.G_EXC_ERROR THEN
987 x_return_status := FND_API.G_RET_STS_ERROR;
988 x_config_line_id := null;
989 x_line_level := null;
990
991 when FND_API.G_EXC_UNEXPECTED_ERROR then
992 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
993 x_config_line_id := null;
994 x_line_level := null;
995
996 when others then
997 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
998 x_config_line_id := null;
999 x_line_level := null;
1000
1001 End find_config_line_and_level;
1002
1003
1004 /* Added by Renga Kannan for bug 5348842 */
1005
1006 Function get_order_line_number(p_line_number Number,
1007 p_service_number Number,
1008 p_option_number Number,
1009 p_component_number Number,
1010 p_shipment_number Number) return varchar2 is
1011 l_concat_value Varchar2(100);
1012 Begin
1013 --=========================================
1014 -- Added for identifying Service Lines
1015 --=========================================
1016 IF P_service_number is not null then
1017 IF p_option_number is not null then
1018 IF p_component_number is not null then
1019 l_concat_value := p_line_number||'.'||p_shipment_number||'.'||
1020 p_option_number||'.'||p_component_number||'.'||
1021 p_service_number;
1022 ELSE
1023 l_concat_value := p_line_number||'.'||p_shipment_number||'.'||
1024 p_option_number||'..'||p_service_number;
1025 END IF;
1026
1027 --- if a option is not attached
1028 ELSE
1029 IF p_component_number is not null then
1030 l_concat_value := p_line_number||'.'||p_shipment_number||'..'||
1031 p_component_number||'.'||p_service_number;
1032 ELSE
1033 l_concat_value := p_line_number||'.'||p_shipment_number||
1034 '...'||p_service_number;
1035 END IF;
1036
1037 END IF; /* if option number is not null */
1038
1039 -- if the service number is null
1040 ELSE
1041 IF p_option_number is not null then
1042 IF p_component_number is not null then
1043 l_concat_value := p_line_number||'.'||p_shipment_number||'.'||
1044 p_option_number||'.'||p_component_number;
1045 ELSE
1046 l_concat_value := p_line_number||'.'||p_shipment_number||'.'||
1047 p_option_number;
1048 END IF;
1049
1050 --- if a option is not attached
1051 ELSE
1052 IF p_component_number is not null then
1053 l_concat_value := p_line_number||'.'||p_shipment_number||'..'||
1054 p_component_number;
1055 ELSE
1056 l_concat_value := p_line_number||'.'||p_shipment_number;
1057 END IF;
1058
1059 END IF; /* if option number is not null */
1060
1061 END IF; /* if service number is not null */
1062 return l_concat_value;
1063 End Get_order_line_number;
1064 End CTO_WORKBENCH_UTIL_PK;
[Home] [Help]