[Home] [Help]
PACKAGE BODY: APPS.OE_SO_LIN_VALIDATE
Source
1 PACKAGE BODY OE_SO_LIN_VALIDATE AS
2 /* $Header: oesolinb.pls 115.3 99/07/16 08:28:20 porting ship $ */
3
4 FUNCTION DB_Reserved_Quantity (X_Line_Id IN NUMBER) return NUMBER is
5 DB_Res_Qty NUMBER := NULL;
6 begin
7
8 SELECT SUM ( NVL ( QUANTITY , 0 ) )
9 INTO DB_Res_Qty
10 FROM SO_LINE_DETAILS
11 WHERE SCHEDULE_STATUS_CODE = 'RESERVED'
12 AND LINE_ID = X_Line_Id
13 AND NVL ( INCLUDED_ITEM_FLAG , 'N' ) = 'N';
14
15 Return (DB_Res_Qty);
16
17 exception
18
19 when NO_DATA_FOUND then return(DB_Res_Qty);
20 when OTHERS then RAISE;
21
22 end DB_Reserved_Quantity;
23
24 PROCEDURE Validate_Reserved_Qty (
25 P_Line_Id IN NUMBER,
26 P_Lines_Reserved_Qty IN NUMBER,
27 P_Lines_Ordered_Qty IN NUMBER,
28 P_Lines_Cancelled_Qty IN NUMBER,
29 P_Lines_Released_Qty IN NUMBER,
30 P_Result OUT VARCHAR2
31 ) is
32 begin
33
34 P_Result := 'Y';
35
36 if (P_Lines_Reserved_Qty = OE_SO_LIN_VALIDATE.DB_Reserved_Quantity(P_Line_Id)) then
37 Return;
38 end if;
39
40 if (P_Lines_Reserved_Qty > (NVL(P_Lines_Ordered_Qty, 0)
41 - NVL(P_Lines_Cancelled_Qty, 0) )) then
42 OE_MSG.Set_Buffer_Message('OE_SCH_RES_MORE_ORD_QTY');
43 P_Result := 'N';
44 Return;
45 elsif (P_Lines_Reserved_Qty < NVL(P_Lines_Released_Qty, 0) ) then
46 OE_MSG.Set_Buffer_Message('OE_SCH_RES_LESS_REL_QTY','RELEASED_QUANTITY',to_char(P_Lines_Released_Qty));
47 P_Result := 'N';
48 Return;
49 else
50 if (OE_SO_LIN_VALIDATE.Complex_Details(P_Line_Id)) then
51 OE_MSG.Set_Buffer_Message('OE_SCH_COMPLEX_DETAILS');
52 P_Result := 'N';
53 Return;
54 end if;
55 end if;
56
57 Return;
58
59 end Validate_Reserved_Qty;
60
61
62
63 PROCEDURE Load_Item_Warehouse_Attributes(
64 P_Inventory_Item_Id IN NUMBER,
65 P_Organization_id IN NUMBER,
66 P_Item_Desc OUT VARCHAR2,
67 P_SO_Xactions_Flag OUT VARCHAR2,
68 P_Reservable_Type OUT NUMBER,
69 P_ATP_Flag OUT VARCHAR2,
70 P_Result OUT VARCHAR2
71 )
72 is
73 begin
74
75 P_Result := 'Y';
76
77 SELECT msi.description,
78 msi.so_transactions_flag,
79 msi.reservable_type,
80 msi.atp_flag
81 INTO P_Item_Desc,
82 P_SO_Xactions_Flag,
83 P_Reservable_Type,
84 P_ATP_Flag
85 FROM mtl_system_items msi
86 WHERE msi.inventory_item_id = P_Inventory_Item_Id
87 AND msi.organization_id = P_Organization_Id;
88
89 exception
90 when NO_DATA_FOUND then NULL;
91 when OTHERS then P_Result := 'N';
92 end Load_Item_Warehouse_Attributes;
93
94
95
96
97 FUNCTION complex_details (x_line_id IN NUMBER) RETURN BOOLEAN IS
98 complexDetails NUMBER := 0;
99 BEGIN
100
101 SELECT min(1)
102 INTO complexDetails
103 FROM so_line_details
104 WHERE line_id = x_line_id
105 AND NVL( released_flag, 'N') = 'N'
106 GROUP BY component_code
107 HAVING COUNT( DISTINCT warehouse_id) > 1
108 OR COUNT( DISTINCT schedule_date) > 1
109 OR COUNT( DISTINCT revision) > 1
110 OR COUNT( DISTINCT lot_number) > 1
111 OR COUNT( DISTINCT subinventory) > 1
112 OR COUNT( DISTINCT demand_class_code) > 1
113 OR ( COUNT( subinventory) > 0
114 AND COUNT( subinventory) <> COUNT(1))
115 OR ( COUNT( warehouse_id) > 0
116 AND COUNT( warehouse_id) <> COUNT(1))
117 OR ( COUNT( schedule_date) > 0
118 AND COUNT( schedule_date) <> COUNT(1))
119 OR ( COUNT( lot_number) > 0
120 AND COUNT( lot_number) <> COUNT(1))
121 OR ( COUNT( revision) > 0
122 AND COUNT( revision) <> COUNT(1))
123 OR ( COUNT( demand_class_code) > 0
124 AND COUNT( demand_class_code) <> COUNT(1));
125
126 IF complexDetails = 1 THEN
127 RETURN TRUE;
128 ELSE
129 RETURN FALSE;
130 END IF;
131
132 EXCEPTION
133
134 WHEN NO_DATA_FOUND THEN RETURN FALSE;
135
136 END complex_details;
137
138
139 PROCEDURE Get_Schedule_DB_Values(
140 X_Row_Id IN VARCHAR2
141 , X_Line_Id IN NUMBER
142 , P_Db_Schedule_Date OUT DATE
143 , P_Db_Demand_Class_Code OUT VARCHAR2
144 , P_Db_Ship_To_Site_Use_Id OUT NUMBER
145 , P_Db_Warehouse_Id OUT NUMBER
146 , P_Db_Ship_To_Contact_Id OUT NUMBER
147 , P_Db_Shipment_Priority_Code OUT VARCHAR2
148 , P_Db_Ship_Method_Code OUT VARCHAR2
149 , P_Db_Schedule_Date_Svrid OUT NUMBER
150 , P_Db_Demand_Class_Svrid OUT NUMBER
151 , P_Db_Ship_To_Svrid OUT NUMBER
152 , P_Db_Warehouse_Svrid OUT NUMBER
153 , P_Db_Ship_Set_Number OUT NUMBER
154 , P_Db_Ship_Set_Number_Svrid OUT NUMBER
155 , P_Db_Reserved_Quantity OUT NUMBER
156 , Result OUT VARCHAR2
157 ) is
158 begin
159
160 Result := 'Y';
161
162 SELECT schedule_date
163 , demand_class_code
164 , ship_to_site_use_id
165 , warehouse_id
166 , ship_to_contact_id
167 , shipment_priority_code
168 , ship_method_code
169 , schedule_date_svrid
170 , demand_class_svrid
171 , ship_to_svrid
172 , warehouse_svrid
173 , ship_set_number
174 , ship_set_number_svrid
175 INTO P_Db_Schedule_Date
176 , P_Db_Demand_Class_Code
177 , P_Db_Ship_To_Site_Use_Id
178 , P_Db_Warehouse_Id
179 , P_Db_Ship_To_Contact_Id
180 , P_Db_Shipment_Priority_Code
181 , P_Db_Ship_Method_Code
182 , P_Db_Schedule_Date_Svrid
183 , P_Db_Demand_Class_Svrid
184 , P_Db_Ship_To_Svrid
185 , P_Db_Warehouse_Svrid
186 , P_Db_Ship_Set_Number
187 , P_Db_Ship_Set_Number_Svrid
188 FROM SO_LINES
189 WHERE rowid = X_Row_Id;
190
191 P_Db_Reserved_Quantity :=
192 OE_SO_LIN_VALIDATE.DB_Reserved_Quantity(X_Line_Id);
193
194 exception
195 when OTHERS then
196 OE_MSG.Internal_Exception('OE_SO_LIN_VALIDATE.Get_Schedule_DB_Values',
197 'Get_DB_Values', 'LINE');
198 Result := 'N';
199 end Get_Schedule_DB_Values;
200
201
202
203 PROCEDURE Query_Time_Stamps(
204 X_Row_Id IN VARCHAR2,
205 X_Creation_Date OUT DATE,
206 X_Creation_Date_Time OUT DATE,
207 X_Std_Component_Freeze_Date OUT DATE,
208 X_Tax_Code OUT VARCHAR2,
209 X_Tax_Code_SVRID OUT NUMBER,
210 Result OUT VARCHAR2
211 ) is
212 begin
213
214 Result := 'Y';
215
216 -- if (P_Override_Tax_Code_Flag IS NOT NULL) then
217
218 SELECT creation_date
219 , To_Date(To_Char( creation_date, 'YYYY/MM/DD HH24:MI' ),
220 'YYYY/MM/DD HH24:MI')
221 , To_Date(To_Char( standard_component_freeze_date,
222 'YYYY/MM/DD HH24:MI' ),'YYYY/MM/DD HH24:MI')
223 , tax_code
224 , tax_code_svrid
225 INTO X_Creation_Date
226 , X_Creation_Date_Time
227 , X_Std_Component_Freeze_Date
228 , X_Tax_Code
229 , X_Tax_Code_Svrid
230 FROM so_lines
231 WHERE rowid = X_Row_id;
232
233 -- else
234
235 -- SELECT creation_date
236 -- , To_Date(To_Char( creation_date, 'YYYY/MM/DD HH24:MI' ),
237 -- 'YYYY/MM/DD HH24:MI')
238 -- , To_Date(To_Char( standard_component_freeze_date,
239 -- 'YYYY/MM/DD HH24:MI' ),'YYYY/MM/DD HH24:MI')
240 -- INTO X_Creation_Date
241 -- , X_Creation_Date_Time
242 -- , X_Standard_Component_Freeze_Date
243 -- FROM so_lines
244 -- WHERE rowid = X_Row_id;
245
246 -- end if;
247
248 exception
249
250 when OTHERS then
251 OE_MSG.Internal_Exception('OE_SO_LIN_VALIDATE.Query_Time_Stamps',
252 'Query Time Stamps' , 'LINE');
253 Result := 'N';
254 end Query_Time_Stamps;
255
256
257 PROCEDURE Load_ATO_Model(
258 X_Line_Id IN NUMBER,
259 X_ATO_Model OUT VARCHAR2,
260 X_ATO_Flag IN VARCHAR2,
261 X_ATO_Line_Id IN NUMBER,
262 X_Item_Type_Code IN VARCHAR2,
263 X_Configuration_Item_Exists OUT VARCHAR2,
264 Result OUT VARCHAR2
265 )
266 is
267 begin
268
269 Result := 'Y';
270
271 if ((X_AtO_Flag = 'Y') and
272 (X_ATO_Line_Id is NULL) and
273 (X_Item_Type_Code in ('MODEL', 'KIT'))) then
274 X_ATO_Model := 'Y';
275
276 X_Configuration_Item_Exists := 'N';
277
278 SELECT 'Y'
279 INTO X_Configuration_Item_Exists
280 FROM dual
281 WHERE exists
282 (SELECT 'CONFIG_ITEM'
283 FROM so_line_details
284 WHERE line_id = X_Line_Id
285 AND NVl(configuration_item_flag, 'N') = 'Y');
286
287 else
288 X_ATO_Model := 'N';
289 end if;
290
291
292 exception
293
294 when NO_DATA_FOUND then
295 NULL;
296
297 when OTHERS then
298 OE_MSG.Internal_Exception('OE_SO_LIN_VALIDATE.Load_ATO_Model',
299 'Load_ATO_Model' , 'LINE');
300 Result := 'N';
301 end Load_ATO_Model;
302
303
304 PROCEDURE Load_Supply_Reserved(
305 X_Line_Id IN NUMBER,
306 X_Supply_Res_Details OUT VARCHAR2,
307 Result OUT VARCHAR2
308 )
309 is
310 begin
311
312 Result := 'Y';
313 X_Supply_Res_Details := 'N';
314
315 SELECT 'Y'
316 INTO X_Supply_Res_Details
317 FROM dual
318 WHERE exists
319 (SELECT 'SUPPLY_RESERVED'
320 FROM so_line_details
321 WHERE line_id = X_Line_id
322 AND schedule_status_code = 'SUPPLY RESERVED');
323
324
325 exception
326
327 when NO_DATA_FOUND then
328 NULL;
329
330 when OTHERS then
331 OE_MSG.Internal_Exception('OE_SO_LIN_VALIDATE.Load_Supply_Reserved',
332 'Load Supply Reserved' , 'LINE');
333 Result := 'N';
334 end Load_Supply_Reserved;
335
336
337
338 PROCEDURE Validate_Scheduling_Attributes
339
340 (
341 P_Row_Id VARCHAR2,
342 P_Line_Id NUMBER,
343 P_Ship_Set_Number NUMBER,
344 P_Reserved_Quantity NUMBER,
345 P_Warehouse_Id NUMBER,
346 P_Ship_To_Site_Use_Id NUMBER,
347 P_Schedule_Date DATE,
348 P_Demand_Class_Code VARCHAR2,
349 P_Ship_Method_Code VARCHAR2,
350 P_Shipment_Priority_Code VARCHAR2,
351 P_Schedule_Action_Code VARCHAR2,
352 P_Navigation_Context VARCHAR2,
353 P_Result IN OUT VARCHAR2
354 ) IS
355
356
357 DB_Reserved_Quantity NUMBER := NULL;
358 DB_Warehouse_Id NUMBER := NULL;
359 DB_Ship_To_Site_Use_Id NUMBER := NULL;
360 DB_Ship_Set_Number NUMBER := NULL;
361 DB_Schedule_Date DATE := NULL;
362 DB_Demand_Class_Code VARCHAR2(30) := NULL;
363 DB_Shipment_Priority_Code VARCHAR2(30) := NULL;
364 DB_Ship_Method_Code VARCHAR2(30) := NULL;
365
366 Dummy_Svrid NUMBER := NULL;
367
368 Result VARCHAR2(30) := 'N';
369
370
371 FUNCTION Warehouse_Changed RETURN BOOLEAN is
372 begin
373
374 if ((P_Warehouse_Id = DB_Warehouse_Id) or
375 (P_Warehouse_Id is NULL and
376 DB_Warehouse_Id is NULL)) then
377 Return(FALSE);
378 end if;
379
380 return (TRUE);
381
382 end Warehouse_Changed;
383
384 FUNCTION Demand_Class_Code_Changed RETURN BOOLEAN is
385 begin
386
387 if ((P_Demand_Class_Code = DB_Demand_Class_Code) or
388 (P_Demand_Class_Code is NULL and
389 DB_Demand_Class_Code is NULL)) then
390 Return(FALSE);
391 end if;
392
393 return (TRUE);
394
398 begin
395 end Demand_Class_Code_Changed;
396
397 FUNCTION Ship_Method_Code_Changed RETURN BOOLEAN is
399
400 if ((P_Ship_Method_Code = DB_Ship_Method_Code) or
401 (P_Ship_Method_Code is NULL and
402 DB_Ship_Method_Code is NULL)) then
403 Return(FALSE);
404 end if;
405
406 return (TRUE);
407
408 end Ship_Method_Code_Changed;
409
410 FUNCTION Shipment_Priority_Code_Changed RETURN BOOLEAN is
411 begin
412
413 if ((P_Shipment_Priority_Code = DB_Shipment_Priority_Code) or
414 (P_Shipment_Priority_Code is NULL and
415 DB_Shipment_Priority_Code is NULL)) then
416 Return(FALSE);
417 end if;
418
419 return (TRUE);
420
421 end Shipment_Priority_Code_Changed;
422
423
424 FUNCTION Schedule_Date_Changed RETURN BOOLEAN is
425 begin
426
427 if ((P_Schedule_Date = DB_Schedule_Date) or
428 (P_Schedule_Date is NULL and
429 DB_Schedule_Date is NULL)) then
430 Return(FALSE);
431 end if;
432
433 return (TRUE);
434
435 end Schedule_Date_Changed;
436
437 FUNCTION Reserved_Quantity_Changed RETURN BOOLEAN is
438 begin
439
440 if (nvl(P_Reserved_Quantity, 0) = nvl(DB_Reserved_Quantity,0)) then
441 Return(FALSE);
442 end if;
443
444 return (TRUE);
445
446 end Reserved_Quantity_Changed;
447
448 FUNCTION Ship_To_Site_Use_Changed RETURN BOOLEAN is
449 begin
450
451 if ((P_Ship_To_Site_Use_Id = DB_Ship_To_Site_Use_Id) or
452 (P_Ship_To_Site_Use_Id is NULL and
453 DB_Ship_To_Site_Use_Id is NULL)) then
454 Return(FALSE);
455 end if;
456
457 return (TRUE);
458
459 end Ship_To_Site_Use_Changed;
460
461
462 FUNCTION Any_Group_Attribute_Changed RETURN BOOLEAN is
463 begin
464
465 if (Warehouse_Changed or
466 Ship_To_Site_Use_Changed or
467 Schedule_Date_Changed or
468 Demand_Class_Code_Changed or
469 Shipment_Priority_Code_Changed or
470 Ship_Method_Code_Changed) then
471
472 return TRUE;
473
474 end if;
475
476 return (FALSE);
477
478 end Any_Group_Attribute_Changed;
479
480
481 begin
482
483 P_Result := 'Y';
484
485 Get_Schedule_DB_Values(P_Row_Id,
486 P_Line_Id,
487 DB_Schedule_Date,
488 DB_Demand_Class_Code,
489 DB_Ship_To_Site_Use_Id,
490 DB_Warehouse_Id,
491 Dummy_Svrid,
492 DB_Shipment_Priority_Code,
493 DB_Ship_Method_Code,
494 Dummy_Svrid,
495 Dummy_Svrid,
496 Dummy_Svrid,
497 Dummy_Svrid,
498 DB_Ship_Set_Number,
499 Dummy_Svrid,
500 DB_Reserved_Quantity,
501 Result);
502
503 if (Result = 'N') then
504 P_Result := 'N';
505 Return;
506 end if;
507
508 if (P_Navigation_Context = 'ORDER') then
509
510 if (nvl(P_Ship_Set_Number, -1) <> nvl(DB_Ship_Set_Number, -1)) then
511 if (Any_Group_Attribute_Changed) then
512 OE_MSG.Set_Buffer_Message('OE_SCH_SHIPSET_CHG_NOT_ALLOWED');
513 P_Result := 'N';
514 Return;
515 end if;
516 end if;
517
518 end if;
519
520 if (P_Schedule_Action_Code in ('ATP CHECK')) then
521
522 if (Ship_To_Site_Use_Changed) then
523 OE_MSG.Set_Buffer_Message('OE_SCH_SHIP_TO_CHG_NOT_ALLOWED');
524 P_Result := 'N';
525 Return;
526 end if;
527
528 end if;
529
530 if (P_Schedule_Action_Code in ('ATP CHECK', 'DEMAND')) then
531
532 if (Reserved_Quantity_Changed) then
533 OE_MSG.Set_Buffer_Message('OE_SCH_NO_RES_QTY_REQUIRED');
534 P_Result := 'N';
535 Return;
536 end if;
537 end if;
538
539 if (P_Schedule_Action_Code in ('UNRESERVE', 'UNDEMAND', 'UNSCHEDULE')) then
540
541 if (Reserved_Quantity_Changed) then
542 OE_MSG.Set_Buffer_Message('OE_SCH_RES_QTY_CHG_NOT_ALLOWED');
543 P_Result := 'N';
544 Return;
545 end if;
546
547 if (P_Navigation_Context = 'ORDER') then
548
549 if (Warehouse_Changed) then
550 OE_MSG.Set_Buffer_Message('OE_SCH_WH_CHG_NOT_ALLOWED');
551 P_Result := 'N';
552 Return;
553 end if;
554
555 end if;
556
557 if (Schedule_Date_Changed) then
558 OE_MSG.Set_Buffer_Message('OE_SCH_DATE_CHG_NOT_ALLOWED');
559 P_Result := 'N';
560 Return;
561 end if;
562
563 if (Demand_Class_Code_Changed) then
564 OE_MSG.Set_Buffer_Message('OE_SCH_DEM_CL_CHG_NOT_ALLOWED');
565 P_Result := 'N';
566 Return;
567 end if;
568 end if;
569
570 if (P_Schedule_Action_Code in ('RESERVE') ) then
571 if (Reserved_Quantity_Changed) then
572 OE_MSG.Set_Buffer_Message('OE_SCH_RES_QTY_CHG_NOT_ALLOWED');
573 P_Result := 'N';
574 Return;
575 end if;
576 end if;
577
578 Return;
579
580 EXCEPTION
581
582 when OTHERS then
583 OE_MSG.Internal_Exception('OE_SO_LIN_VALIDATE.Validate_Scheduling_Attributes',
584 'Validating Scheduling Attributes' , 'LINE');
585
586 end Validate_Scheduling_Attributes;
587
588 END OE_SO_LIN_VALIDATE;