[Home] [Help]
PACKAGE BODY: APPS.OE_LIN
Source
1 PACKAGE BODY OE_LIN AS
2 /* $Header: OEXLINSB.pls 115.2 99/07/16 08:13:11 porting shi $ */
3
4 OE_SUCCESS CONSTANT VARCHAR2(1) := 'Y';
5 OE_FAILURE CONSTANT VARCHAR2(1) := 'N';
6 OE_BOOKED CONSTANT INTEGER:= 1;
7
8
9 PROCEDURE Check_Allow_Manual_Discount
10 (
11 P_Price_List_Id IN NUMBER,
12 P_Order_Price_List_Id IN NUMBER,
13 P_Order_Order_Type_Id IN NUMBER,
14 P_Header_Id IN NUMBER,
15 P_Line_Id IN NUMBER,
16 P_List_Price IN NUMBER,
17 P_Discounting_Privilage IN VARCHAR2,
18 P_Global_Result OUT VARCHAR2,
19 P_Check_Multiple_Adj_Flag IN VARCHAR2 DEFAULT 'Y'
20
21 )
22 is
23 P_REASON varchar2(100);
24 begin
25 OE_LIN.Check_Manual_Discount_Priv(
26 X_Price_List_Id=>P_Price_List_Id,
27 X_Order_Price_List_Id=>P_Order_Price_List_Id,
28 X_Order_Order_Type_Id=>P_Order_Order_Type_Id,
29 X_Header_Id=>P_Header_Id,
30 X_Line_Id=>P_Line_Id,
31 X_List_Price=>P_List_Price,
32 X_Discounting_Privilage=>P_Discounting_Privilage,
33 X_Global_Result=>P_Global_Result,
34 X_Reason=>P_Reason,
35 X_Check_Multiple_Adj_Flag=>P_Check_Multiple_Adj_Flag
36 );
37 end;
38
39 PROCEDURE Check_Manual_Discount_Priv
40 (
41 X_Price_List_Id IN NUMBER,
42 X_Order_Price_List_Id IN NUMBER,
43 X_Order_Order_Type_Id IN NUMBER,
44 X_Header_Id IN NUMBER,
45 X_Line_Id IN NUMBER,
46 X_List_Price IN NUMBER,
47 X_Discounting_Privilage IN VARCHAR2,
48 X_Global_Result OUT VARCHAR2,
49 X_Reason OUT VARCHAR2,
50 X_Check_Multiple_Adj_Flag IN VARCHAR2 DEFAULT 'Y'
51 )
52 is
53 L_Dummy NUMBER;
54 begin
55
56 X_Global_Result:=OE_SUCCESS;
57
58 if (X_Discounting_Privilage='NONE') then
59 OE_MSG.Set_Buffer_Message('OE_MANDIS_DISALLOWED',
60 'REASON','OE_MANDIS_NO_PRIVILEGE');
61 X_Global_Result:=OE_FAILURE;
62 X_Reason := 'OE_MANDIS_NO_PRIVILEGE';
63 Return;
64 ELSif ( X_List_Price > 0 ) then
65 begin
66 SELECT NULL INTO L_Dummy
67 FROM SO_DisCOUNTS
68 WHERE PRICE_LisT_ID = NVL(X_PRICE_LIST_ID,X_Order_PRICE_LIST_ID)
69 /* Bug 524620 - do not check manual discount condition unless seling price
70 is being changed, in which case X_Check_Multiple_Adj_Flag is
71 passed as 'Y'. */
72 /* AND NVL(AUTOMATIC_DisCOUNT_FLAG,'N') = 'N' AND ROWNUM = 1; */
73 AND (NVL(AUTOMATIC_DisCOUNT_FLAG,'N') = 'N'
74 OR X_Check_Multiple_Adj_Flag = 'N')
75 AND ROWNUM = 1;
76 begin
77 SELECT NULL INTO L_Dummy
78 FROM SO_ORDER_TYPES
79 WHERE ORDER_TYPE_ID = X_Order_ORDER_TYPE_ID
80 AND ((ENFORCE_LINE_PRICES_FLAG = 'Y'
81 AND X_Discounting_Privilage = 'UNLIMITED')
82 OR ENFORCE_LINE_PRICES_FLAG = 'N') AND ROWNUM = 1;
83 if (X_Check_Multiple_Adj_Flag = 'Y') then
84 begin
85 SELECT NULL INTO L_Dummy
86 FROM SO_PRICE_ADJUSTMENTS
87 WHERE HEADER_ID = X_HEADER_ID
88 AND LINE_ID = X_LINE_ID
89 AND NVL( AUTOMATIC_FLAG, 'N' ) = 'N'
90 HAVING COUNT(*) > 1;
91 OE_MSG.Set_Buffer_Message('OE_MANDIS_DISALLOWED',
92 'REASON','OE_MANDIS_TOO_MANY');
93 X_Global_Result:=OE_FAILURE;
94 X_Reason := 'OE_MANDIS_TOO_MANY';
95 Return;
96 exception
97 when no_data_found then
98 X_Global_Result:=OE_SUCCESS;
99 return;
100 end;
101 end if;
102 exception
103 when no_data_found then
104 OE_MSG.Set_Buffer_Message('OE_MANDIS_DISALLOWED',
105 'REASON','OE_MANDIS_PRICES_ENFORCED');
106 X_Global_Result:=OE_FAILURE;
107 X_Reason := 'OE_MANDIS_PRICES_ENFORCED';
108 Return;
109 end;
110
111 exception
112 when no_data_found then
113 OE_MSG.Set_Buffer_Message('OE_MANDIS_DISALLOWED',
114 'REASON','OE_MANDIS_NO_DISCOUNT');
115 X_Global_Result:=OE_FAILURE;
116 X_Reason := 'OE_MANDIS_NO_DISCOUNT';
117 Return;
118 end;
119 /*else no else is required as this condition is tetsted at the time of
120 selling_price validation too
121 441551
122 OE_MSG.Set_Buffer_Message('OE_MANDIS_DISALLOWED',
123 'REASON','OE_MANDIS_NO_LIST_PRICE');
124 X_Global_Result:=OE_FAILURE;
125 X_Reason := 'OE_MANDIS_NO_LIST_PRICE';
126 Return;
127 */
128 end if;
129
130 exception
131
132 when others then
133 X_Global_Result:=OE_FAILURE;
134 OE_MSG.Internal_Exception(Routine=>
135 'OE_LIN.Check_Allow_Manual_Discount',
136 Operation=>'Pricing',
137 Object=>'LINE',
138 Message=>' When Others');
139
140
141 end Check_Manual_Discount_Priv;
142
143 PROCEDURE Apply_Manual_Discount
144 (
145 P_Manual_Discount_Id IN NUMBER,
146 P_List_Price IN NUMBER,
147 P_List_Percent IN NUMBER,
148 P_Selling_Price IN NUMBER,
149 P_Manual_Discount_Percent IN NUMBER,
150 P_Pricing_Method_Code IN VARCHAR2,
151 P_Selling_Percent OUT NUMBER,
152 P_Header_Id IN NUMBER,
153 P_Line_Id IN NUMBER,
154 P_User_Id IN NUMBER,
155 P_Login_Id IN NUMBER,
156 P_Manual_Discount_Line_Id IN NUMBER,
157 P_Price_List_Id IN NUMBER,
158 P_Order_Price_List_Id IN NUMBER,
159 P_Order_Order_Type_Id IN NUMBER,
160 P_Discounting_Privilage IN VARCHAR2,
161 P_Adjustment_Total OUT NUMBER,
162 P_Global_Result OUT VARCHAR2
163 )
164
165 is
166
167 L_Global_Result VARCHAR2(1);
168 dummy NUMBER;
169
170 begin
171
172 P_Global_Result:=OE_SUCCESS;
173
174 OE_LIN.Check_Allow_Manual_Discount(
175 P_Price_List_Id=>P_Price_List_Id,
176 P_Order_Price_List_Id=>P_Order_Price_List_Id,
177 P_Order_Order_Type_Id=>P_Order_Order_Type_Id,
178 P_Header_Id=>P_Header_Id ,
179 P_Line_Id=>P_Line_Id ,
180 P_List_Price=>P_List_Price ,
181 P_Discounting_Privilage=>P_Discounting_Privilage,
182 P_Global_Result=>L_Global_Result
183 );
184 P_Global_Result:=L_Global_Result;
185
186
187 if ( L_Global_Result <> 'Y' ) then
188 return;
189 elsif (NVL(P_LisT_PRICE,0) = 0 ) then
190 return;
191 end if;
192
193 if (P_MANUAL_DisCOUNT_PERCENT is null ) then
194 return;
195 end if;
196
197
198 if P_MANUAL_DisCOUNT_PERCENT <> 0 then
202 end if;
199 if P_PRICING_METHOD_CODE = 'PERC' then
200 P_SELLING_PERCENT := P_LisT_PERCENT * P_SELLING_PRICE
201 / P_LisT_PRICE;
203 begin
204
205 SELECT NULL INTO dummy FROM
206 SO_PRICE_ADJUSTMENTS
207 WHERE HEADER_ID = P_HEADER_ID
208 AND LINE_ID = P_LINE_ID
209 AND AUTOMATIC_FLAG = 'N';
210
211 UPDATE SO_PRICE_ADJUSTMENTS
212 SET PERCENT =
213 P_MANUAL_DisCOUNT_PERCENT
214 , DisCOUNT_ID =
215 P_MANUAL_DisCOUNT_ID
216 , LAST_UPDATE_DATE =
217 SYSDATE
218 , LAST_UPDATED_BY =
219 P_USER_ID
220 , LAST_UPDATE_LOGIN =
221 P_LOGIN_ID
222 WHERE HEADER_ID = P_HEADER_ID
223 AND LINE_ID = P_LINE_ID
224 AND AUTOMATIC_FLAG = 'N';
225 P_Adjustment_Total := null;
226 return;
227 exception
228
229 when no_data_found then
230
231 INSERT INTO SO_PRICE_ADJUSTMENTS
232 ( PRICE_ADJUSTMENT_ID
233 , CREATION_DATE
234 , CREATED_BY
235 , LAST_UPDATE_DATE
236 , LAST_UPDATED_BY
237 , LAST_UPDATE_LOGIN
238 , HEADER_ID
239 , LINE_ID
240 , DisCOUNT_ID
241 , DisCOUNT_LINE_ID
242 , AUTOMATIC_FLAG
243 , PERCENT)
244 VALUES
245 ( SO_PRICE_ADJUSTMENTS_S.NEXTVAL
246 , SYSDATE
247 , P_USER_ID
248 , SYSDATE
249 , P_USER_ID
250 , P_LOGIN_ID
251 , P_HEADER_ID
252 , P_LINE_ID
253 , P_MANUAL_DisCOUNT_ID
254 , P_MANUAL_DisCOUNT_LINE_ID
255 , 'N'
256 , P_MANUAL_DisCOUNT_PERCENT);
257
258 P_Adjustment_Total := null;
259 end;
260
261 else
265 AND AUTOMATIC_FLAG = 'N';
262 DELETE FROM SO_PRICE_ADJUSTMENTS
263 WHERE HEADER_ID = P_HEADER_ID
264 AND LINE_ID = P_LINE_ID
266
267 end if;
268
269 exception
270 when no_data_found then
271 P_Global_Result:=L_Global_Result;
272
273 when others then
274 P_Global_Result:=OE_FAILURE;
275 OE_MSG.Internal_Exception(Routine=>
276 'OE_LIN.Apply_Manual_Discount',
277 Operation=>'Pricing',
278 Object=>'LINE',
279 Message=>' When Others');
280
281
282
283 end Apply_Manual_Discount;
284
285
286 PROCEDURE Get_Line_Object_Adj_Total
287 (
288 Order_Header_Id IN NUMBER,
289 Lin_Obj_Line_Id IN NUMBER,
290 Lin_Obj_Apply_Order_Adjs_Flag IN VARCHAR2,
291 P_Automatic_Flag IN VARCHAR2,
292 Lin_Obj_Adjustment_Total OUT NUMBER,
293 P_Return_Status OUT VARCHAR2
294 )
295 is
296
297 begin
298
299 P_Return_Status:=OE_SUCCESS;
300
301 SELECT NVL( SUM( NVL( PERCENT, 0 ) ), 0 )
302 INTO Lin_Obj_Adjustment_Total
303 FROM SO_PRICE_ADJUSTMENTS
304 WHERE HEADER_ID = Order_Header_Id
305 AND (( Lin_Obj_Apply_Order_Adjs_Flag = 'Y'
306 AND LINE_ID is NULL )
307 OR ( LINE_ID = Lin_Obj_Line_Id
308 AND AUTOMATIC_FLAG = NVL(P_Automatic_Flag,AUTOMATIC_FLAG)));
309
310
311
312 exception
313
314 when no_data_found then
315 null;
316
317 when others then
318 P_Return_Status:=OE_FAILURE;
319 OE_MSG.Internal_Exception(Routine=>
320 'OE_LIN.Get_Line_Object_Adj_Total',
321 Operation=>'Pricing',
322 Object=>'LINE',
323 Message=>' When Others');
324
325
326
327 end;
328
329
330 PROCEDURE ATO_Model
331 (
332 ATO_Model_Flag IN VARCHAR2,
333 Return_Status OUT VARCHAR2
334 )
335 is
336
337
338 begin
339
340 if (ATO_Model_Flag = 'Y') then
341 OE_MSG.Set_Buffer_Message('OE_SCH_ATO_MODEL','','');
342 Return_Status:='N';
343 else
344 Return_Status:='Y';
345 end if;
346
347 exception
348
352 'OE_LIN_Validate.ATO_Model',
349 when others then
350 Return_Status:=OE_FAILURE;
351 OE_MSG.Internal_Exception(Routine=>
353 Operation=>'',
354 Object=>'LINE',
355 Message=>' When Others');
356
357 end ATO_Model;
358
359 PROCEDURE ATO_Configuration
360 (
361 ATO_Line_Id IN NUMBER,
362 Return_Status OUT VARCHAR2
363 )
364 is
365
366
367 begin
368
369 if (ATO_Line_Id is not null) then
370 OE_MSG.Set_Buffer_Message('OE_SCH_LINE_PART_OF_ATO_CONFIG','','');
371 Return_Status:='N';
372 else
373 Return_Status:='Y';
374 end if;
375
376 exception
377
378 when others then
379 Return_Status:=OE_FAILURE;
380 OE_MSG.Internal_Exception(Routine=>
381 'OE_LIN_Validate.ATO_Configuration',
382 Operation=>'',
383 Object=>'LINE',
384 Message=>' When Others');
385
386 end ATO_Configuration;
387
388 PROCEDURE Supply_Reserved
389 (
390 Supply_Reservation_Exists IN VARCHAR2,
391 Return_Status OUT VARCHAR2
392 )
393 is
394
395
396 begin
397
398 if (Supply_Reservation_Exists='Y') then
399 OE_MSG.Set_Buffer_Message('OE_SCH_LINE_HAS_SUPP_RES','','');
400 Return_Status:='N';
401 else
402 Return_Status:='Y';
403 end if;
404
405 exception
406
407 when others then
408 Return_Status:=OE_FAILURE;
409 OE_MSG.Internal_Exception(Routine=>
410 'OE_LIN.Supply_Reserved',
411 Operation=>'',
412 Object=>'LINE',
413 Message=>' When Others');
414
415 end Supply_Reserved;
416
417
418 PROCEDURE Check_Schedule_Group
419 (
420 DB_Record_Flag IN VARCHAR2,
421 Source_Object IN VARCHAR2,
422 Return_Status OUT VARCHAR2
423 )
424 is
425
426
427 begin
428
432 else
429 if (DB_Record_Flag='Y' and Source_Object is not null) then
430 OE_MSG.Set_Buffer_Message('OE_SCH_LINE_GROUP_MEMBER','','');
431 Return_Status:='N';
433 Return_Status:='Y';
434 end if;
435 exception
436
437 when others then
438 Return_Status:=OE_FAILURE;
439 OE_MSG.Internal_Exception(Routine=>
440 'OE_LIN.Check_Schedule_Group',
441 Operation=>'',
442 Object=>'LINE',
443 Message=>' When Others');
444
445
446 end Check_Schedule_Group;
447
448
449 PROCEDURE Internal_Order
450 (
451 Order_Category IN VARCHAR2,
452 Return_Status OUT VARCHAR2
453 )
454 is
455
456
457 begin
458
459 if (Order_Category='P') then
460 OE_MSG.Set_Buffer_Message('OE_SCH_INT_ORDER_UPD','','');
461 Return_Status:='N';
462 else
463 Return_Status:='Y';
464 end if;
465
466 exception
467
468 when others then
469 Return_Status:=OE_FAILURE;
470 OE_MSG.Internal_Exception(Routine=>
471 'OE_LIN.Internal_Order',
472 Operation=>'',
473 Object=>'LINE',
474 Message=>' When Others');
475
476
477 end Internal_Order;
478
479
480 PROCEDURE Fully_Released
481 (
482 Row_Id IN VARCHAR2,
483 Return_Status OUT VARCHAR2
484 )
485
486 is
487
488 L_Count NUMBER;
489
490 begin
491
492 SELECT COUNT(*)
493 INTO L_Count
494 FROM SO_LINES
495 WHERE ROWID = Row_Id
496 AND S2 = 4;
497
498 if L_Count = 1 then
499 OE_MSG.Set_Buffer_Message('OE_SCH_LINE_FULLY_RELEASED','','');
500 Return_Status:='N';
501 else
502 Return_Status:='Y';
503 end if;
504
505 exception
506
507 when others then
508 Return_Status:=OE_FAILURE;
509 OE_MSG.Internal_Exception(Routine=>
510 'OE_LIN.Fully_Released',
511 Operation=>'',
512 Object=>'LINE',
513 Message=>' When Others');
514
518
515 end Fully_Released;
516
517
519 PROCEDURE Fully_Cancelled
520 (
521 Row_Id IN VARCHAR2,
522 Return_Status OUT VARCHAR2
523 )
524
525 is
526
527 L_Count NUMBER;
528
529 begin
530
531
532 SELECT COUNT(*)
533 INTO L_Count
534 FROM SO_LINES
535 WHERE ROWID = Row_Id
536 AND ORDERED_QUANTITY = NVL(CANCELLED_QUANTITY,0);
537
538 if L_Count = 1 then
539 OE_MSG.Set_Buffer_Message('OE_SCH_LINE_FULLY_CANCELLED','','');
540 Return_Status:='N';
541 else
542 Return_Status:='Y';
543 end if;
544
545 exception
546
547 when others then
548 Return_Status:=OE_FAILURE;
549 OE_MSG.Internal_Exception(Routine=>
550 'OE_LIN.Fully_Cancelled',
551 Operation=>'',
552 Object=>'LINE',
553 Message=>' When Others');
554
555 end Fully_Cancelled;
556
557 PROCEDURE Calc_Lin_Obj_Open_Quantity
558 (
559 Lin_Obj_Ordered_Quantity IN NUMBER,
560 Lin_Obj_Open_Quantity OUT NUMBER,
561 Lin_Obj_Cancelled_Quantity IN NUMBER,
562 P_return_Status OUT VARCHAR2
563
564 )
565 is
566
567 begin
568
569 P_Return_Status:=OE_SUCCESS;
570
571 if Lin_Obj_Ordered_Quantity is null then
572 Lin_Obj_Open_Quantity:=0;
573 ELSif Lin_Obj_Cancelled_Quantity is null then
574 Lin_Obj_Open_Quantity:=Lin_Obj_Ordered_Quantity;
575 else
576 Lin_Obj_Open_Quantity:=Lin_Obj_Ordered_Quantity -
577 Lin_Obj_Cancelled_Quantity;
578 end if;
579
580 exception
581
582 when others then
583 P_Return_Status:=OE_FAILURE;
584 OE_MSG.Internal_Exception(Routine=>
585 'OE_LIN.Calc_Lin_Obj_Open_Quantity',
586 Operation=>'',
587 Object=>'LINE',
588 Message=>' When Others');
589
590
591 end Calc_Lin_Obj_Open_Quantity;
592
593
594 PROCEDURE Load_ATO_Flag
595 (
596 P_Lin_Obj_Line_Id IN NUMBER,
597 P_Lin_Obj_Item_Type_Code IN VARCHAR2,
601 P_Lin_Obj_Supply_Reserv_Exists OUT VARCHAR2,
598 P_Lin_Obj_ATO_Line_Id IN NUMBER,
599 P_Lin_Obj_ATO_Flag IN VARCHAR2,
600 P_Lin_Obj_ATO_Model_Flag IN OUT VARCHAR2,
602 P_Lin_Obj_Config_Item_Exists OUT VARCHAR2,
603 P_Return_Status OUT VARCHAR2
604 )
605
606 is
607
608 L_Dummy NUMBER;
609
610 begin
611
612 P_Lin_Obj_ATO_Model_Flag:='N';
613 P_Lin_Obj_Supply_Reserv_Exists:='N';
614 P_Lin_Obj_Config_Item_Exists :='N';
615
616
617 if (P_Lin_Obj_ATO_Flag = 'Y') then
618 if (P_Lin_Obj_ATO_Line_Id is NULL ) then
619 if (P_Lin_Obj_Item_Type_Code IN
620 ( 'MODEL', 'KIT' ) ) then
621 P_Lin_Obj_ATO_Model_Flag:='Y';
622 end if;
623 else
624 return;
625 end if;
626 else
627 return;
628 end if;
629
630 SELECT NULL INTO L_Dummy
631 FROM SO_LINE_DETAILS
632 WHERE LINE_ID = P_Lin_Obj_Line_Id
633 AND SCHEDULE_STATUS_CODE = 'SUPPLY RESERVED';
634
635 P_Lin_Obj_Supply_Reserv_Exists:='Y';
636
637 exception
638
639 when no_data_found then
640
641 if (P_Lin_Obj_ATO_Model_Flag='Y') then
642
643 begin
644
645 SELECT NULL INTO L_Dummy
646 FROM SO_LINE_DETAILS
647 WHERE NVL ( CONFIGURATION_ITEM_FLAG , 'N' ) = 'Y'
648 AND LINE_ID = P_Lin_Obj_Line_Id;
649
650 P_Lin_Obj_Config_Item_Exists:='Y';
651
652 exception
653
654 when no_data_found then
655 null;
656 end;
657
658 end if;
659
660 when others then
661 P_Return_Status:=OE_FAILURE;
662 OE_MSG.Internal_Exception(Routine=>
663 'OE_LIN.Load_ATO_Flag',
664 Operation=>'',
665 Object=>'LINE',
666 Message=>' When Others');
667
668
669 end Load_ATO_Flag;
670
671 PROCEDURE Check_Navigate_Shipments
672 (
673 P_Header_Id IN NUMBER,
674 P_Line_Id IN NUMBER,
675 P_Order_S1 IN NUMBER,
676 P_Config_Item_Exists IN VARCHAR2,
677 P_Return_Status OUT VARCHAR2,
678 P_ITEM_TYPE_CODE IN VARCHAR2 default null,
679 P_SERVICE_INSTALLED IN VARCHAR2 default 'N'
680 )
681
685
682 is
683
684 L_Dummy NUMBER;
686 begin
687
688 P_Return_Status:=OE_SUCCESS;
689
690 if p_service_installed = 'Y' then
691 l_dummy := null;
692 if p_item_type_code = 'MODEL' then
693 declare cursor service_exists(x_line_id number) is
694 select line_id
695 from so_lines
696 where service_parent_line_id = x_line_id
697 and nvl(ordered_quantity,0) - nvl(cancelled_quantity,0) > 0
698 union
699 select lin.line_id
700 from so_lines serv, so_lines lin
701 where serv.service_parent_line_id = lin.line_id
702 and nvl(serv.ordered_quantity,0) -
703 nvl(serv.cancelled_quantity,0) > 0
704 and lin.parent_line_id = x_line_id;
705 cursor installation_exists(x_line_id number) is
706 select line_id
707 from SO_LINE_SERVICE_DETAILS
708 where line_id = x_line_id
709 union
710 select lin.line_id
711 from SO_LINE_SERVICE_DETAILS isd, so_lines lin
712 where isd.line_id = lin.line_id
713 and lin.parent_line_id = x_line_id;
714 begin
715 open service_exists(p_line_id);
716 fetch service_exists into l_dummy;
717 close service_exists;
718 if l_dummy is not null then
719 P_Return_Status:=OE_FAILURE;
720 OE_MSG.Set_Buffer_Message('OE_LIN_SERVICES_EXISTS','','');
721 return;
722 end if;
723 open installation_exists(p_line_id);
724 fetch installation_exists into l_dummy;
725 close installation_exists;
726 if l_dummy is not null then
727 P_Return_Status:=OE_FAILURE;
728 OE_MSG.Set_Buffer_Message('OE_LIN_ISD_EXISTS','','');
729 return;
730 end if;
731 end;
732 else -- not a model
733 declare cursor service_exists(x_line_id number) is
734 select line_id
735 from so_lines
736 where service_parent_line_id = x_line_id
737 and nvl(ordered_quantity,0) - nvl(cancelled_quantity,0) > 0;
738
739 cursor installation_exists(x_line_id number) is
740 select line_id
741 from SO_LINE_SERVICE_DETAILS
742 where line_id = x_line_id;
743 begin
744 open service_exists(p_line_id);
745 fetch service_exists into l_dummy;
746 close service_exists;
747 if l_dummy is not null then
748 P_Return_Status:=OE_FAILURE;
749 OE_MSG.Set_Buffer_Message('OE_LIN_SERVICES_EXISTS','','');
750 return;
751 end if;
752 open installation_exists(p_line_id);
753 fetch installation_exists into l_dummy;
754 close installation_exists;
755 if l_dummy is not null then
756 P_Return_Status:=OE_FAILURE;
757 OE_MSG.Set_Buffer_Message('OE_LIN_ISD_EXISTS','','');
758 return;
759 end if;
760 end;
761 end if; -- item_type = MODEL
765 FROM SO_LINE_DETAILS
762 end if; -- service installed
763
764 SELECT NULL into L_Dummy
766 WHERE SCHEDULE_STATUS_CODE IS NOT NULL
767 AND LINE_ID IN
768 (SELECT LINE_ID
769 FROM SO_LINES
770 WHERE HEADER_ID = P_Header_Id
771 AND (LINE_ID = P_Line_Id
772 OR PARENT_LINE_ID = P_Line_Id))
773 AND ROWNUM = 1;
774
775 OE_MSG.Set_Buffer_Message('OE_OE_SCHEDULING_EXISTS','','');
776
777 P_Return_Status:=OE_FAILURE;
778
779 exception
780
781 when no_data_found then
782
783 if (P_Order_S1 = OE_BOOKED) then
784 begin
785 SELECT NULL into L_Dummy
786 FROM SO_LINES
787 WHERE HEADER_ID = P_Header_Id
788 AND (LINE_ID = P_Line_Id
789 OR PARENT_LINE_ID = P_Line_Id)
790 AND (S2 NOT IN (8,18)
791 OR S5 NOT IN (8,18)
792 OR S8 NOT IN (8,18)
793 OR S25 NOT IN (8,18)
794 OR P_Config_Item_Exists = 'Y'
795 OR OPEN_FLAG IS NULL)
796 AND ROWNUM = 1;
797
798 P_Return_Status:=OE_FAILURE;
799 OE_MSG.Set_Buffer_Message('OE_OE_LINE_CONFIG_PROCESSED','','');
800 exception
801 when no_data_found then
802 null;
803 end;
804 end if;
805
806
807 when others then
808 P_Return_Status:=OE_FAILURE;
809 OE_MSG.Internal_Exception(Routine=>
810 'OE_LIN.Check_Navigate_Shipments',
811 Operation=>'',
812 Object=>'LINE',
813 Message=>' When Others');
814
815
816 end Check_Navigate_Shipments;
817
818 PROCEDURE Update_Shippable_Flag
819 (
820 P_ATO_Option_Parent_Line IN NUMBER,
821 P_Return_Status OUT VARCHAR2
822 )
823
824 is
825
826 L_Dummy NUMBER;
827
828 begin
829
830 P_Return_Status:=OE_SUCCESS;
831
832 UPDATE SO_LINE_DETAILS
833 SET SHIPPABLE_FLAG = 'N'
834 WHERE LINE_ID IN ( SELECT L.LINE_ID
835 FROM SO_LINES L
836 WHERE ( PARENT_LINE_ID = P_ATO_Option_Parent_Line
837 AND ATO_LINE_ID IS NOT NULL)
838 OR ( PARENT_LINE_ID = P_ATO_Option_Parent_Line
839 AND ATO_LINE_ID IS NULL
840 AND ATO_FLAG = 'Y'
841 AND ITEM_TYPE_CODE = 'MODEL')
842 );
843
844 exception
845
846 when others then
847 P_Return_Status:=OE_FAILURE;
848 OE_MSG.Internal_Exception(Routine=>
849 'OE_LIN.Update_Shippable_Flag',
850 Operation=>'',
851 Object=>'LINE',
852 Message=>' When Others');
853
854
855 end Update_Shippable_Flag;
856
857 end OE_LIN;