1 package body CTO_WORKBENCH_UTIL_PK as
2 /* $Header: CTOWBUTB.pls 120.11.12010000.2 2008/08/20 15:42:46 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 if (p_item_type in ('OPTION','STANDARD')) then
148
149 l_demand_line_id := p_line_id;
150
151 elsif (p_ato_line_id = p_line_id and p_item_type in ('MODEL', 'CLASS')) then
152
153 select line_id
154 into l_demand_line_id
155 from oe_order_lines_all
156 where ato_line_id = p_ato_line_id
157 and item_type_code = 'CONFIG';
158
159 else
160 return null;
161 end if;
162 else
163 l_demand_line_id := p_line_id;
164 end if;
165
166 --
167 -- Dropship line
168 --
169 IF nvl(p_source_type, 'INTERNAL') = 'EXTERNAL' THEN
170
171 select count(*)
172 into v_ds_po_quantity
173 from oe_drop_ship_sources ods
174 where ods.line_id = l_demand_line_id
175 --and ods.drop_ship_source_id = 2
176 and ods.po_header_id is not null;
177
178 IF (v_ds_po_quantity > 0) THEN
179 l_result := 5;
180 END IF;
181
182 select count(*)
183 into v_ds_req_quantity
184 from oe_drop_ship_sources ods
185 where ods.line_id = l_demand_line_id
186 --and ods.drop_ship_source_id = 2
187 and ods.po_header_id is null;
188
189 IF (v_ds_req_quantity > 0) THEN
190 IF (l_result <> 0) THEN
191 l_result := 7;
192 select meaning
193 into x_result
194 from mfg_lookups
195 where lookup_type = 'CTO_WB_SUPPLY_TYPE'
196 and lookup_code = l_result;
197
198 return x_result;
199 ELSE
200 l_result := 6;
201 END IF;
202 END IF;
203
204 select meaning
205 into x_result
206 from mfg_lookups
207 where lookup_type = 'CTO_WB_SUPPLY_TYPE'
208 and lookup_code = l_result;
209
210 --
211 -- Internal line
212 --
213 ELSE
214
215 --
216 -- Get source document id
217 --
218 l_source_document_type_id := CTO_WORKBENCH_UTIL_PK.get_source_document_id ( pLineId => l_demand_line_id );
219
220
221 select nvl(sum(reservation_quantity),0)
222 into v_wip_quantity
223 from mtl_reservations
224 where demand_source_type_id = decode (l_source_document_type_id, 10,
225 inv_reservation_global.g_source_type_internal_ord,
226 inv_reservation_global.g_source_type_oe )
227 and demand_source_line_id = l_demand_line_id
228 and supply_source_type_id = inv_reservation_global.g_source_type_wip;
229
230 IF (v_wip_quantity > 0) THEN
231 l_result := 1;
232 END IF;
233
234 select count(*)
235 into v_flow_count
236 from wip_flow_schedules
237 where demand_source_type = inv_reservation_global.g_source_type_oe
238 and demand_source_line =to_char(l_demand_line_id)
239 and status = 1;
240
241 IF (v_flow_count > 0) THEN
242 IF (l_result <> 0) THEN
243 l_result := 7;
244 select meaning
245 into x_result
246 from mfg_lookups
247 where lookup_type = 'CTO_WB_SUPPLY_TYPE'
248 and lookup_code = l_result;
249
250 return x_result;
251 ELSE
252 l_result := 2;
253 END IF;
254 END IF;
255
256 select nvl(sum(reservation_quantity),0)
257 into v_po_quantity
258 from mtl_reservations
259 where demand_source_type_id = decode (l_source_document_type_id,
260 10, inv_reservation_global.g_source_type_internal_ord,
261 inv_reservation_global.g_source_type_oe )
262 and demand_source_line_id = l_demand_line_id
263 and supply_source_type_id = inv_reservation_global.g_source_type_po;
264
265 IF (v_po_quantity > 0) THEN
266 IF (l_result <> 0) THEN
267 l_result := 7;
268 select meaning
269 into x_result
270 from mfg_lookups
271 where lookup_type = 'CTO_WB_SUPPLY_TYPE'
272 and lookup_code = l_result;
273
274 return x_result;
275 ELSE
276 l_result := 3;
277 END IF;
278 END IF;
279
280 select nvl(sum(reservation_quantity), 0)
281 into v_req_quantity
282 from mtl_reservations
283 where demand_source_type_id = decode (l_source_document_type_id,
284 10, inv_reservation_global.g_source_type_internal_ord,
285 inv_reservation_global.g_source_type_oe )
286 and demand_source_line_id = l_demand_line_id
287 and supply_source_type_id = inv_reservation_global.g_source_type_req;
288
289 IF (v_req_quantity > 0) THEN
290 IF (l_result <> 0) THEN
291 l_result := 7;
292 select meaning
293 into x_result
294 from mfg_lookups
295 where lookup_type = 'CTO_WB_SUPPLY_TYPE'
296 and lookup_code = l_result;
297
298 return x_result;
299 ELSE
300 l_result := 4;
301 END IF;
302 END IF;
303
304 -- rkaza. 05/19/2005. ireq project.
305 select nvl(sum(reservation_quantity), 0)
306 into v_ireq_quantity
307 from mtl_reservations
308 where demand_source_type_id = decode (l_source_document_type_id,
309 10, inv_reservation_global.g_source_type_internal_ord,
310 inv_reservation_global.g_source_type_oe )
311 and demand_source_line_id = l_demand_line_id
312 and supply_source_type_id = inv_reservation_global.g_source_type_internal_req;
313
314 IF (v_ireq_quantity > 0) THEN
315 IF (l_result <> 0) THEN
316 l_result := 7;
317 select meaning
318 into x_result
319 from mfg_lookups
320 where lookup_type = 'CTO_WB_SUPPLY_TYPE'
321 and lookup_code = l_result;
322
323 return x_result;
324 ELSE
325 l_result := 8; -- IR
326 END IF;
327 END IF;
328 -- Added by Renga Kannan on 30-Jun-2005 for Cross docking project
329
330 select nvl(sum(reservation_quantity), 0)
331 into v_asn_quantity
332 from mtl_reservations
333 where demand_source_type_id = decode (l_source_document_type_id,
334 10, inv_reservation_global.g_source_type_internal_ord,
335 inv_reservation_global.g_source_type_oe )
336 and demand_source_line_id = l_demand_line_id
337 and supply_source_type_id = inv_reservation_global.g_source_type_asn;
338
339 IF (v_asn_quantity > 0) THEN
340 IF (l_result <> 0) THEN
341 l_result := 7;
342 select meaning
343 into x_result
344 from mfg_lookups
345 where lookup_type = 'CTO_WB_SUPPLY_TYPE'
346 and lookup_code = l_result;
347
348 return x_result;
349 ELSE
350 l_result := 9; -- ASN
351 END IF;
352 END IF;
353
354 select meaning
355 into x_result
356 from mfg_lookups
357 where lookup_type = 'CTO_WB_SUPPLY_TYPE'
358 and lookup_code = l_result;
359
360 END IF; /* internal source type */
361
362 return x_result;
363
364 End Get_supply_type;
365
366
367 Function Get_config_line_id (P_ato_line_id IN Number,
368 p_line_id IN Number,
369 p_item_type IN Varchar2) return number as
370
371 l_config_line_id oe_order_lines_all.line_id%type;
372
373 Begin
374 l_config_line_id := p_line_id;
375
376 if p_ato_line_id = p_line_id and p_item_type in ('MODEL', 'CLASS') then
377
378 if config_line_id_tbl.exists(p_ato_line_id) then
379 l_config_line_id := config_line_id_tbl(p_ato_line_id);
380 else
381
382 select line_id into l_config_line_id
383 from oe_order_lines_all
384 where ato_line_id = p_ato_line_id
385 and item_type_code = 'CONFIG';
386
387 config_line_id_tbl(p_ato_line_id) := l_config_line_id;
388
389 end if;
390
391 end if;
392
393 return l_config_line_id;
394
395 Exception When Others then
396 return p_line_id;
397
398 End Get_Config_Line_id;
399
400
401 Function Get_Item_Name (P_ato_line_id IN Number,
402 p_line_id IN Number,
403 p_item_type IN Varchar2,
404 p_item_name IN Varchar2,
405 p_config_item IN Number,
406 p_ship_org_id IN Number) return Varchar2 as
407 l_item_name varchar2(1000);
408 Begin
409 l_item_name := p_item_name;
410 If p_ato_line_id is not null and p_config_item is not null and p_item_type in ('MODEL','CLASS') then
411 -- Fixed bug 5447062
412 -- replaced org condition with rownum
413
414 select concatenated_segments
415 into l_item_name
416 from mtl_system_items_kfv
417 where inventory_item_id = p_config_item
418 and rownum =1;
419
420 elsif p_ato_line_id is not null and p_config_item is null and p_item_type in ('MODEL','CLASS') then
421
422 select concatenated_segments
423 into l_item_name
424 from mtl_system_items_kfv mtl,
425 oe_order_lines_all oel
426 where oel.ato_line_id = p_ato_line_id
427 and oel.item_type_code = 'CONFIG'
428 and oel.inventory_item_id = mtl.inventory_item_id
429 and mtl.organization_id = p_ship_org_id;
430
431 end if;
432
433 return l_item_name;
434 End Get_item_name;
435
436
437 Function Get_Item_Desc (P_ato_line_id IN Number,
438 p_line_id IN Number,
439 p_item_type IN Varchar2,
440 p_item_desc IN Varchar2,
441 p_config_item IN Number,
442 p_ship_org_id IN Number) return Varchar2 as
443 l_item_desc mtl_system_items_kfv.description%type;
444 Begin
445 l_item_desc := p_item_desc;
446 If p_ato_line_id is not null and p_config_item is not null and p_item_type in ('MODEL','CLASS') then
447
448 -- Fixed bug 5447062
449 -- replaced org condition with rownum
450 select description
451 into l_item_desc
452 from mtl_system_items_kfv
453 where inventory_item_id = p_config_item
454 and rownum = 1;
455
456 elsif p_ato_line_id is not null and p_config_item is null and p_item_type in ('MODEL','CLASS') then
457
458 select description
459 into l_item_desc
460 from mtl_system_items_kfv mtl,
461 oe_order_lines_all oel
462 where mtl.inventory_item_id = oel.inventory_item_id
463 and mtl.organization_id = p_ship_org_id
464 and oel.ato_line_id = p_ato_line_id
465 and oel.item_type_code = 'CONFIG';
466
467 end if;
468
469 return l_item_desc;
470 End Get_item_desc;
471
472
473 FUNCTION get_source_document_id (pLineId in number) RETURN NUMBER
474 IS
475 l_source_document_type_id number;
476 BEGIN
477
478 select h.source_document_type_id
479 into l_source_document_type_id
480 from oe_order_headers_all h, oe_order_lines_all l
481 where h.header_id = l.header_id
482 and l.line_id = pLineId
483 and rownum = 1;
484
485 return (l_source_document_type_id);
486
487 END get_source_document_id;
488
489
490 FUNCTION convert_uom(from_uom IN VARCHAR2,
491 to_uom IN VARCHAR2,
492 quantity IN NUMBER,
493 item_id IN NUMBER )
494 RETURN NUMBER
495 IS
496 this_item NUMBER;
497 to_rate NUMBER;
498 from_rate NUMBER;
499 result NUMBER;
500
501 BEGIN
502 IF from_uom = to_uom THEN
503 result := quantity;
504 ELSIF from_uom IS NULL
505 OR to_uom IS NULL THEN
506 result := 0;
507 ELSE
508 result := INV_CONVERT.inv_um_convert(item_id,
509 5,
510 quantity,
511 from_uom,
512 to_uom,
513 NULL,
514 NULL);
515
516 if result = -99999 then
517 result := 0;
518 end if;
519 END IF;
520 RETURN result;
521
522 END convert_uom;
523
524 FUNCTION Get_Buyer_Name (P_suggested_buyer_id IN Varchar2)
525 RETURN Varchar2 IS
526
527 l_buyer_name Varchar2(2000);
528
529 BEGIN
530
531 l_buyer_name := null;
532
533 select full_name
534 into l_buyer_name
535 from per_people_f
536 where person_id = P_suggested_buyer_id;
537
538 return l_buyer_name;
539
540 EXCEPTION
541 WHEN NO_DATA_FOUND THEN
542 return l_buyer_name;
543
544 WHEN OTHERS THEN
545 return l_buyer_name;
546
547 END Get_Buyer_Name;
548
549 /* fp-J project: Added a new function Get_Workbench_Item_Type */
550
551 FUNCTION Get_WorkBench_Item_Type
552 ( p_header_id IN NUMBER
553 ,p_top_model_line_id IN NUMBER
554 ,p_ato_line_id IN NUMBER
555 ,p_line_id IN NUMBER
556 ,p_item_type_code IN VARCHAR2
557 ) RETURN varchar2
558 IS
559 x_wb_item_type VARCHAR2(20) := null;
560 BEGIN
561
562
563 IF p_header_id is not null AND
564 p_line_id is null THEN
565
566 x_wb_item_type := 'HEAD';
567
568 ElSIF p_top_model_line_id is not null AND
569 p_ato_line_id is null AND
570 p_item_type_code = 'MODEL' THEN
571
572 x_wb_item_type := 'PTO';
573
574 ElSIF p_ato_line_id = p_line_id AND
575 (p_item_type_code = 'MODEL' OR
576 p_item_type_code = 'CLASS') THEN
577
578 x_wb_item_type := 'MDL';
579
580 ELSE
581
582 IF p_ato_line_id = p_line_id AND
583 (p_item_type_code = 'STANDARD' OR
584 p_item_type_code = 'OPTION') THEN
585
586 x_wb_item_type := 'ATO';
587
588 ELSIF p_item_type_code = 'CONFIG' THEN
589
590 x_wb_item_type := 'CFG';
591
592 ELSE
593
594 x_wb_item_type := 'STD';
595
596 END IF;
597
598 END IF;
599
600 RETURN x_wb_item_type;
601
602 END Get_WorkBench_Item_Type;
603
604 FUNCTION Get_Rsvd_on_hand_qty(
605 p_line_id IN Number,
606 p_ato_line_id IN Number,
607 p_item_type_code IN varchar2) RETURN Number is
608 l_prim_rsv_qty Number;
609 l_rsv_qty Number;
610 l_prim_uom_code mtl_reservations.primary_uom_code%type;
611 l_line_id Number;
612
613 Begin
614 -- -- Fixed bug 5199341
615 -- Added code to derive the config line id incase of ato model order lines
616 If p_ato_line_id is not null then
617 if p_ato_line_id = p_line_id and p_item_type_code in ('MODEL','CLASS') then
618 select line_id
619 into l_line_id
620 from oe_order_lines_all
621 where ato_line_id = p_ato_line_id
622 and item_type_code = 'CONFIG';
623 End if;
624 end if;
625 If l_line_id is null then
626 l_line_id := p_line_id;
627 End if;
628
629 select sum(primary_reservation_quantity),primary_uom_code
630 into l_prim_rsv_qty, l_prim_uom_code
631 from mtl_reservations
632 where demand_source_line_id = l_line_id
633 and supply_source_TYpe_id = 13
634 group by primary_uom_code;
635
636 If l_prim_rsv_qty <> 0 then
637 select CTO_WORKBENCH_UTIL_PK.convert_uom(l_prim_uom_code,
638 oel.order_quantity_uom,
639 nvl(l_prim_rsv_qty,0),
640 oel.inventory_item_id)
641 into l_rsv_qty
642 from oe_order_lines_all oel
643 where oel.line_id = l_line_id;
644 else
645 l_rsv_qty := 0;
646 End if;
647 return l_rsv_qty;
648 Exception when no_data_found then --4752854,code review bug
649 --return 0 when no on-hand qty
650 return 0;
651 End Get_Rsvd_on_hand_qty;
652
653
654
655 FUNCTION get_last_available_date(p_ato_line_id IN number,
656 p_line_id IN Number,
657 p_item_type IN varchar2) RETURN date is
658
659 l_date date := null;
660 l_config_line_id number;
661 l_line_level varchar2(10);
662 l_return_status varchar2(20);
663
664 Begin
665
666 find_config_line_and_level(p_ato_line_id => p_ato_line_id,
667 p_line_id => p_line_id,
668 p_item_type => p_item_type,
669 x_config_line_id => l_config_line_id,
670 x_line_level => l_line_level,
671 x_return_status => l_return_status);
672
673 -- no config line id, return null.
674 if l_return_status in (fnd_api.g_ret_sts_error,
675 fnd_api.g_ret_sts_unexp_error) then
676 return null;
677 end if;
678
679 -- No need to process for lower level items.
680 if l_line_level = 'Lower' then
681 return null;
682 end if;
683
684 -- process for Top, Ato, Std items.
685 --
686 -- bug 6833994
687 -- Added a to_char() clause on the l_config_line_id
688 -- while querying from wip_flow_schedules
689 -- ntungare
690 --
691 Select max(exp_comp_date) into l_date
692 from
693 (SELECT wdj.scheduled_completion_date exp_comp_date
694 FROM mtl_reservations mr, wip_discrete_jobs wdj
695 WHERE mr.demand_source_type_id = decode(CTO_WORKBENCH_UTIL_PK.get_source_document_id(l_config_line_id), 10,8,2)
696 AND mr.demand_source_line_id = l_config_line_id
697 AND mr.supply_source_type_id = 5
698 AND wdj.wip_entity_id = mr.supply_source_header_id
699 AND wdj.organization_id = mr.organization_id
700
701 UNION
702
703 SELECT wfs.scheduled_completion_date exp_comp_date
704 FROM wip_flow_schedules wfs
705 WHERE wfs.demand_source_line = to_char(l_config_line_id)
706 AND wfs.status = 1
707
708 UNION
709
710 select nvl(poll.promised_date,poll.need_by_date) exp_comp_date
711 from mtl_reservations mr, po_line_locations_all poll
712 where mr.demand_source_type_id = 2
713 and mr.demand_source_line_id = l_config_line_id
714 and mr.supply_source_type_id = 1
715 and mr.supply_source_header_id = poll.po_header_id
716 and mr.supply_source_line_id = poll.line_location_id
717
718 UNION
719
720 select porl.need_by_date exp_comp_date
721 from mtl_reservations mr, po_requisition_lines_all porl
722 where mr.demand_source_type_id = 2
723 and mr.demand_source_line_id = l_config_line_id
724 and mr.supply_source_type_id in (7, 17)
725 and mr.supply_source_header_id = porl.requisition_header_id
726 and mr.supply_source_line_id = porl.requisition_line_id
727
728 UNION
729
730 select nvl(poll.promised_date,poll.need_by_date) exp_comp_date
731 from oe_drop_ship_sources ods, po_line_locations_all poll
732 where ods.line_id = l_config_line_id
733 and ods.po_header_id = poll.po_header_id
734 and ods.line_location_id = poll.line_location_id
735
736 UNION
737
738 select porl.need_by_date exp_comp_date
739 from oe_drop_ship_sources ods, po_requisition_lines_all porl
740 where ods.line_id = l_config_line_id
741 and ods.po_header_id is null
742 and ods.requisition_header_id = porl.requisition_header_id
743 and ods.requisition_line_id = porl.requisition_line_id
744
745 UNION
746
747 select asn_headers.expected_receipt_date exp_comp_date
748 from mtl_reservations mr,
749 rcv_shipment_lines ASN_LINES,
750 rcv_shipment_headers asn_headers
751 where mr.demand_source_type_id = 2
752 and mr.demand_source_line_id = l_config_line_id
753 and mr.supply_source_type_id = 25
754 and mr.supply_source_line_detail = ASN_LINES.shipment_line_id
755 and asn_headers.shipment_header_id = asn_lines.shipment_header_id
756 and ASN_LINES.asn_line_flag = 'Y');
757
758 return l_date;
759
760 Exception
761
762 When Others then
763 return null;
764
765 End get_last_available_date;
766
767
768 /*******************************************************************************************
769 -- API name : get_rsvd_inrcv_qty
770 -- Type : Public
771 -- Pre-reqs : INVRSVGS.pls
772 -- Function : Given config/ato item line id it returns
773 -- the qty reserved to in receiving supply
774 -- Parameters:
775 -- IN : p_line_id Expects the config/ato item order line id Required
776 --
777 -- Version :
778 --
779 --
780 ******************************************************************************************/
781
782
783 FUNCTION Get_Rsvd_inrcv_qty(
784 p_line_id IN Number,
785 p_ato_line_id IN Number,
786 p_item_type_code IN varchar2) RETURN Number is
787 l_prim_rsv_qty Number;
788 l_rsv_qty Number;
789 l_prim_uom_code mtl_reservations.primary_uom_code%type;
790 l_line_id Number;
791
792 Begin
793
794 -- -- Fixed bug 5199341
795 -- Added code to derive the config line id incase of ato model order lines
796
797 If p_ato_line_id is not null then
798 if p_ato_line_id = p_line_id and p_item_type_code in ('MODEL','CLASS') then
799 select line_id
800 into l_line_id
801 from oe_order_lines_all
802 where ato_line_id = p_ato_line_id
803 and item_type_code = 'CONFIG';
804 End if;
805 end if;
806 If l_line_id is null then
807 l_line_id := p_line_id;
808 End if;
809
810 select sum(primary_reservation_quantity),primary_uom_code
811 into l_prim_rsv_qty, l_prim_uom_code
812 from mtl_reservations
813 where demand_source_line_id = l_line_id
814 and supply_source_TYpe_id = 27
815 group by primary_uom_code;
816
817 If l_prim_rsv_qty <> 0 then
818 select CTO_WORKBENCH_UTIL_PK.convert_uom(l_prim_uom_code,
819 oel.order_quantity_uom,
820 nvl(l_prim_rsv_qty,0),
821 oel.inventory_item_id)
822 into l_rsv_qty
823 from oe_order_lines_all oel
824 where oel.line_id = l_line_id;
825 else
826 l_rsv_qty := 0;
827 End if;
828 return l_rsv_qty;
829 Exception when no_data_found then
830 return 0;
831 End Get_Rsvd_inrcv_qty;
832
833
834
835 Procedure find_config_line_and_level(P_ato_line_id IN Number,
836 p_line_id IN Number,
837 p_item_type IN Varchar2,
838 x_config_line_id OUT NOCOPY number,
839 x_line_level OUT NOCOPY varchar2,
840 x_return_status OUT NOCOPY varchar2) IS
841
842 Begin
843
844 x_return_status := fnd_api.g_ret_sts_success;
845
846 -- Std item
847 If p_ato_line_id is null then
848 x_config_line_id := p_line_id;
849 x_line_level := 'Std';
850 return;
851 end if;
852
853 -- Lower level. p_ato_line_id not null
854 if p_ato_line_id <> p_line_id then
855 x_config_line_id := p_line_id;
856 x_line_level := 'Lower';
857 return;
858 end if;
859
860 -- Top level or Ato case. p_ato_line_id not null and equal to p_line_id
861 if p_item_type in ('MODEL', 'CLASS') then
862
863 if config_line_id_tbl.exists(p_ato_line_id) then
864 x_config_line_id := config_line_id_tbl(p_ato_line_id);
865 else
866 select line_id into x_config_line_id
867 from oe_order_lines_all
868 where ato_line_id = p_ato_line_id
869 and item_type_code = 'CONFIG';
870
871 config_line_id_tbl(p_ato_line_id) := x_config_line_id;
872 end if;
873
874 x_line_level := 'Top';
875
876 else -- Ato item
877
878 x_config_line_id := p_line_id;
879 x_line_level := 'Ato';
880
881 end if; -- if item_type in (model, class)
882
883 Exception
884
885 when FND_API.G_EXC_ERROR THEN
886 x_return_status := FND_API.G_RET_STS_ERROR;
887 x_config_line_id := null;
888 x_line_level := null;
889
890 when FND_API.G_EXC_UNEXPECTED_ERROR then
891 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
892 x_config_line_id := null;
893 x_line_level := null;
894
895 when others then
896 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
897 x_config_line_id := null;
898 x_line_level := null;
899
900 End find_config_line_and_level;
901
902
903 /* Added by Renga Kannan for bug 5348842 */
904
905 Function get_order_line_number(p_line_number Number,
906 p_service_number Number,
907 p_option_number Number,
908 p_component_number Number,
909 p_shipment_number Number) return varchar2 is
910 l_concat_value Varchar2(100);
911 Begin
912 --=========================================
913 -- Added for identifying Service Lines
914 --=========================================
915 IF P_service_number is not null then
916 IF p_option_number is not null then
917 IF p_component_number is not null then
918 l_concat_value := p_line_number||'.'||p_shipment_number||'.'||
919 p_option_number||'.'||p_component_number||'.'||
920 p_service_number;
921 ELSE
922 l_concat_value := p_line_number||'.'||p_shipment_number||'.'||
923 p_option_number||'..'||p_service_number;
924 END IF;
925
926 --- if a option is not attached
927 ELSE
928 IF p_component_number is not null then
929 l_concat_value := p_line_number||'.'||p_shipment_number||'..'||
930 p_component_number||'.'||p_service_number;
931 ELSE
932 l_concat_value := p_line_number||'.'||p_shipment_number||
933 '...'||p_service_number;
934 END IF;
935
936 END IF; /* if option number is not null */
937
938 -- if the service number is null
939 ELSE
940 IF p_option_number is not null then
941 IF p_component_number is not null then
942 l_concat_value := p_line_number||'.'||p_shipment_number||'.'||
943 p_option_number||'.'||p_component_number;
944 ELSE
945 l_concat_value := p_line_number||'.'||p_shipment_number||'.'||
946 p_option_number;
947 END IF;
948
949 --- if a option is not attached
950 ELSE
951 IF p_component_number is not null then
952 l_concat_value := p_line_number||'.'||p_shipment_number||'..'||
953 p_component_number;
954 ELSE
955 l_concat_value := p_line_number||'.'||p_shipment_number;
956 END IF;
957
958 END IF; /* if option number is not null */
959
960 END IF; /* if service number is not null */
961 return l_concat_value;
962 End Get_order_line_number;
963 End CTO_WORKBENCH_UTIL_PK;
[Home] [Help]