1 PACKAGE BODY OE_LINE_DETAILS_PKG AS
2 /* $Header: oexdetb.pls 115.2 99/07/16 08:28:53 porting shi $ */
3 PROCEDURE Raise_Exception
4 (
5 Routine IN VARCHAR2,
6 Operation IN VARCHAR2,
7 Message IN VARCHAR2
8 ) IS
9
10 x BOOLEAN;
11
12 BEGIN
13 x :=OE_MSG.Set_Buffer_Message('OE_EXC_INTERNAL_EXCEPTION',
14 'ROUTINE', Routine);
15 x :=OE_MSG.Set_Buffer_Message('OE_EXC_INTERNAL_EXCEPTION',
16 'OPERATION', Operation);
17 x :=OE_MSG.Set_Buffer_Message('OE_EXC_INTERNAL_EXCEPTION',
18 'MESSAGE',Message|| ' sqlcode:'||SQLCODE);
19
20 END Raise_Exception;
21
22
23 PROCEDURE Manually_Insert_Detail
24 ( P_Line_Detail_Id NUMBER,
25 P_Creation_Date IN OUT DATE,
26 P_Created_By NUMBER,
27 P_Last_Update_Date IN OUT DATE,
28 P_Last_Updated_By NUMBER,
29 P_Last_Update_Login NUMBER,
30 P_Line_Id NUMBER,
31 P_Inventory_Item_Id NUMBER,
32 P_Component_Sequence_Id NUMBER,
33 P_Component_Code VARCHAR2,
34 P_Quantity NUMBER,
35 P_Schedule_Date DATE,
36 P_Lot_Number VARCHAR2,
37 P_Subinventory VARCHAR2,
38 P_Warehouse_Id NUMBER,
39 P_Revision VARCHAR2,
40 P_Customer_Requested_Lot_Flag VARCHAR2,
41 P_Schedule_Status_Code VARCHAR2,
42 P_Context VARCHAR2,
43 P_Attribute1 VARCHAR2,
44 P_Attribute2 VARCHAR2,
45 P_Attribute3 VARCHAR2,
46 P_Attribute4 VARCHAR2,
47 P_Attribute5 VARCHAR2,
48 P_Attribute6 VARCHAR2,
49 P_Attribute7 VARCHAR2,
50 P_Attribute8 VARCHAR2,
51 P_Attribute9 VARCHAR2,
52 P_Attribute10 VARCHAR2,
53 P_Attribute11 VARCHAR2,
54 P_Attribute12 VARCHAR2,
55 P_Attribute13 VARCHAR2,
56 P_Attribute14 VARCHAR2,
57 P_Attribute15 VARCHAR2,
58 P_Included_Item_Flag VARCHAR2,
59 P_Component_Ratio NUMBER,
60 P_Shippable_Flag VARCHAR2,
61 P_Transactable_Flag VARCHAR2,
62 P_Reservable_Flag VARCHAR2,
63 P_Released_Flag VARCHAR2,
64 P_Demand_Class_Code VARCHAR2,
65 P_Unit_Code VARCHAR2,
66 P_Required_For_Revenue_Flag IN OUT VARCHAR2,
67 P_Quantity_Svrid NUMBER,
68 P_Warehouse_Svrid NUMBER,
69 P_Demand_Class_Svrid NUMBER,
70 P_Date_Svrid NUMBER,
71 P_Customer_Requested_Svrid NUMBER,
72 P_Df_Svrid NUMBER,
73 P_Delivery NUMBER,
74 P_Update_Flag VARCHAR2,
75 P_Configuration_Item_Flag VARCHAR2,
76 P_Result IN OUT VARCHAR2,
77 P_Dpw_Assigned_Flag VARCHAR2 DEFAULT 'N'
78 ) IS
79 temp_sysdate DATE;
80 is_option VARCHAR2(1);
81 Cursor chk_for_option is
82 SELECT 'Y'
83 FROM SO_LINES L, SO_LINE_DETAILS D
84 WHERE L.LINE_ID = D.LINE_ID
85 AND D.LINE_DETAIL_ID = P_Line_Detail_Id
86 AND L.OPTION_FLAG = 'Y';
87 BEGIN
88
89 SELECT SYSDATE
90 INTO temp_sysdate
91 FROM DUAL;
92
93 P_Creation_Date := temp_sysdate;
94 P_Last_Update_Date := temp_sysdate;
95
96 OPEN chk_for_option;
97 FETCH chk_for_option into is_option;
98 if is_option = 'Y' OR
99 P_Included_Item_Flag = 'Y' THEN
100 SELECT DECODE( REQUIRED_FOR_REVENUE, 1, 'Y', 'N' )
101 INTO P_Required_For_Revenue_Flag
102 FROM BOM_INVENTORY_COMPONENTS
103 WHERE COMPONENT_SEQUENCE_ID = P_Component_Sequence_Id;
104 else
105 P_Required_For_Revenue_Flag := 'Y';
106 end if;
107
108
109
110 INSERT INTO SO_LINE_DETAILS
111 ( LINE_DETAIL_ID,
112 CREATION_DATE,
113 CREATED_BY,
114 LAST_UPDATE_DATE,
115 LAST_UPDATED_BY,
116 LAST_UPDATE_LOGIN,
117 LINE_ID,
118 INVENTORY_ITEM_ID,
119 COMPONENT_SEQUENCE_ID,
120 COMPONENT_CODE,
121 QUANTITY,
122 SCHEDULE_DATE,
123 LOT_NUMBER,
124 SUBINVENTORY,
125 WAREHOUSE_ID,
126 REVISION,
127 CUSTOMER_REQUESTED_LOT_FLAG,
128 SCHEDULE_STATUS_CODE,
129 CONTEXT,
130 ATTRIBUTE1,
131 ATTRIBUTE2,
132 ATTRIBUTE3,
133 ATTRIBUTE4,
134 ATTRIBUTE5,
135 ATTRIBUTE6,
136 ATTRIBUTE7,
137 ATTRIBUTE8,
138 ATTRIBUTE9,
139 ATTRIBUTE10,
140 ATTRIBUTE11,
141 ATTRIBUTE12,
142 ATTRIBUTE13,
143 ATTRIBUTE14,
144 ATTRIBUTE15,
145 INCLUDED_ITEM_FLAG,
146 COMPONENT_RATIO,
147 SHIPPABLE_FLAG,
148 TRANSACTABLE_FLAG,
149 RESERVABLE_FLAG,
150 RELEASED_FLAG,
151 DEMAND_CLASS_CODE,
152 UNIT_CODE,
153 REQUIRED_FOR_REVENUE_FLAG,
154 QUANTITY_SVRID,
155 WAREHOUSE_SVRID,
156 DEMAND_CLASS_SVRID,
157 DATE_SVRID,
158 CUSTOMER_REQUESTED_SVRID,
159 DF_SVRID,
160 DELIVERY,
161 UPDATE_FLAG,
162 CONFIGURATION_ITEM_FLAG,
163 DPW_ASSIGNED_FLAG)
164 VALUES
165 ( P_Line_Detail_Id,
166 P_Creation_Date,
167 P_Created_By,
168 P_Last_Update_Date,
169 P_Last_Updated_By,
170 P_Last_Update_Login,
171 P_Line_Id,
172 P_Inventory_Item_Id,
173 P_Component_Sequence_Id,
174 P_Component_Code,
175 P_Quantity,
176 P_Schedule_Date,
177 P_Lot_Number,
178 P_Subinventory,
179 P_Warehouse_Id,
180 P_Revision,
181 P_Customer_Requested_Lot_Flag,
182 P_Schedule_Status_Code,
183 P_Context,
184 P_Attribute1,
185 P_Attribute2,
186 P_Attribute3,
187 P_Attribute4,
188 P_Attribute5,
189 P_Attribute6,
190 P_Attribute7,
191 P_Attribute8,
192 P_Attribute9,
193 P_Attribute10,
194 P_Attribute11,
195 P_Attribute12,
196 P_Attribute13,
197 P_Attribute14,
198 P_Attribute15,
199 P_Included_Item_Flag,
200 P_Component_Ratio,
201 P_Shippable_Flag,
202 P_Transactable_Flag,
203 P_Reservable_Flag,
204 P_Released_Flag,
205 P_Demand_Class_Code,
206 P_Unit_Code,
207 P_Required_For_Revenue_Flag,
208 P_Quantity_Svrid,
209 P_Warehouse_Svrid,
210 P_Demand_Class_Svrid,
211 P_Date_Svrid,
212 P_Customer_Requested_Svrid,
213 P_Df_Svrid,
214 P_Delivery,
215 P_Update_Flag,
216 P_Configuration_Item_Flag,
217 P_Dpw_Assigned_Flag);
218
219 P_Result := 'Y';
220
221 EXCEPTION
222 WHEN OTHERS THEN
223 P_Result := 'N';
224
225 Raise_Exception ('OE_LINE_DETAILS_PKG.Insert_Manual_Detail',
226 '',
227 SQLERRM);
228
229 END Manually_Insert_Detail;
230
231
232 /*
233 ----------------------------------------------------------------------
234 This procedure validates the scheduling attributes for a line
235 detail.
236
237 Here are the rules for this function:
238
239 * You cannot specify lot or revision unless placing a reservation.
240
241 * For placing reservations or reserved lines, the following rules
242 must be obeyed:
243
244 * If Revision Control is turned ON, then if a revision is
245 specified, then the Lot and Subinventory must also be
246 specified.
247
248 * If Lot Control is turned ON, then if a Lot Number is
249 specified, then so must be a Subinventory.
250
251
252
253 * You cannot undemand, unreserve or unschedule while changing the
254 warehouse, schedule date or demand class.
255
256 * You cannot undemand, unreserve, unschedule or ATP inquiry while
257 changing the quantity, subinventory, lot or revision.
258
259
260 ----------------------------------------------------------------------
261 */
262
263 PROCEDURE Validate_Scheduling_Attributes
264 (
265 P_DB_RECORD_FLAG VARCHAR2,
266 P_DB_QUANTITY NUMBER,
267 P_DB_WAREHOUSE_ID NUMBER,
268 P_DB_SCHEDULE_DATE DATE,
269 P_DB_SUBINVENTORY VARCHAR2,
270 P_DB_REVISION VARCHAR2,
271 P_DB_LOT_NUMBER VARCHAR2,
272 P_DB_DEMAND_CLASS_CODE VARCHAR2,
273 P_QUANTITY NUMBER,
274 P_WAREHOUSE_ID NUMBER,
275 P_SCHEDULE_DATE DATE,
276 P_SUBINVENTORY VARCHAR2,
277 P_REVISION VARCHAR2,
278 P_LOT_NUMBER VARCHAR2,
279 P_DEMAND_CLASS_CODE VARCHAR2,
280 P_REVISION_CONTROL_FLAG VARCHAR2,
281 P_LOT_CONTROL_FLAG VARCHAR2,
282 P_SCHEDULE_ACTION_CODE VARCHAR2,
283 P_SCHEDULE_STATUS_CODE VARCHAR2,
284 P_RESULT IN OUT VARCHAR2
285 ) IS
286
287
288 Success_Flag BOOLEAN := TRUE;
289
290 /*
291 ----------------------------------------------------------------------
292
293 This local function returns TRUE if both lot and revision are null
294 Otherwise, it sets the message 'OE_SCH_LOT_REV_EXIST' and returns
295 FALSE
296
297 ----------------------------------------------------------------------
298 */
299 FUNCTION Check_Rev_Lot_Null RETURN BOOLEAN IS
300 BEGIN
301
302
303 IF (P_Lot_Number IS NULL) and (P_Revision IS NULL) THEN
304
305 Return (TRUE);
306
307 END IF;
308
309 OE_MSG.Set_Buffer_Message('OE_SCH_LOT_REV_EXIST');
310 Return (FALSE);
311
312 END Check_Rev_Lot_Null;
313
314 /*
315 ----------------------------------------------------------------------
316
317 This local function checks for revision control. If revision control
318 flag is turned on, then if a revision is specified, so must be a lot,
319 and a subinventory.
320
321 ----------------------------------------------------------------------
322 */
323 FUNCTION Check_Revision_Control RETURN BOOLEAN IS
324 BEGIN
325
326 IF (P_Revision_Control_Flag = 'Y') THEN
327
328 IF (P_Revision IS NULL) AND
329 ((P_Lot_Number IS NOT NULL) OR (P_Subinventory IS NOT NULL)) THEN
330
331 OE_MSG.Set_Buffer_Message('OE_SCH_ENTER_REVISION');
332 Return (FALSE);
333
334 END IF;
335
336 END IF;
337
338 RETURN (TRUE);
339
340 END Check_Revision_Control;
341
342
343
344 /*
345 ----------------------------------------------------------------------
346 This function checks for Lot Control. If a lot is specified while
347 the lot control is turned on, then a subinventory must also be
348 specified.
349 ----------------------------------------------------------------------
350 */
351 FUNCTION Check_Lot_Control RETURN BOOLEAN IS
352 BEGIN
353
354 IF (P_Lot_Control_Flag = 'Y') THEN
355
356 IF (P_Lot_Number IS NULL) AND
357 (P_Subinventory IS NOT NULL) THEN
358
359 OE_MSG.Set_Buffer_Message('OE_SCH_ENTER_LOT');
360 Return(FALSE);
361
362 END IF;
363
364 END IF;
365
366 Return (TRUE);
367
368 END Check_Lot_Control;
369
370
371 /*
372 ----------------------------------------------------------------------
373 This function makes sure that Revision and Lot controls are not
374 violated.
375 ----------------------------------------------------------------------
376 */
377 FUNCTION Check_Controls RETURN BOOLEAN IS
378 BEGIN
379
380
381 Return (Check_Revision_Control) AND
382 (Check_Lot_Control);
383
384
385 END Check_Controls;
386
387
388
389 /*
390 ----------------------------------------------------------------------
391 Logic for if schedule action is NULL.
392 ----------------------------------------------------------------------
393 */
394 FUNCTION Check_Null_Action RETURN BOOLEAN IS
395 BEGIN
396
397
398 /*
399 If the line is already demanded, then make sure that you have not
400 specified any lot or revision.
401 */
402 IF (P_Schedule_Status_Code IS NULL) OR
403 (P_Schedule_Status_Code = 'DEMANDED') THEN
404
405 Return (Check_Rev_Lot_Null);
406
407
408 /*
409 If the line is reserved, make sure that revision and lot control are
410 not violated.
411 */
412 ELSIF (P_Schedule_Status_Code = 'RESERVED') THEN
413
414 Return (Check_Controls);
415
416 END IF;
417
418 Return (TRUE);
419
420 END Check_Null_Action;
421
422
423
424 /*
425 ----------------------------------------------------------------------
426 This function makes sure that revision and lot do not conflict with
427 the schedule action or the existing schedule status.
428 ----------------------------------------------------------------------
429 */
430 FUNCTION Check_Revision_Lot RETURN BOOLEAN IS
431
435
432 Rev_Lot_Passed_Flag BOOLEAN := TRUE;
433
434 BEGIN
436 IF (P_Schedule_Action_Code) IN ('DEMAND', 'ATP CHECK') THEN
437
438 Rev_Lot_Passed_Flag := Check_Rev_Lot_Null;
439
440 ELSIF (P_Schedule_Action_Code = 'RESERVE') THEN
441
442 Rev_Lot_Passed_Flag := Check_Controls;
443
444 ELSIF (P_Schedule_Action_Code IS NULL) THEN
445
446 Rev_Lot_Passed_Flag := Check_Null_Action;
447
448 END IF;
449
450
451 RETURN Rev_Lot_Passed_Flag;
452
453
454 END Check_Revision_Lot;
455
456
457 FUNCTION Warehouse_Not_Changed RETURN BOOLEAN IS
458 BEGIN
459
460 IF (P_Warehouse_ID <> P_DB_Warehouse_ID) THEN
461
462 OE_MSG.Set_Buffer_Message('OE_SCH_WH_CHG_NOT_ALLOWED');
463 Return (FALSE);
464
465 END IF;
466
467 Return (TRUE);
468
469 END Warehouse_Not_Changed;
470
471
472 FUNCTION Schedule_Date_Not_Changed RETURN BOOLEAN IS
473 BEGIN
474
475 IF (P_Schedule_Date <> P_DB_Schedule_Date) THEN
476
477 OE_MSG.Set_Buffer_Message('OE_SCH_DATE_CHG_NOT_ALLOWED');
478 Return (FALSE);
479
480 END IF;
481
482 Return (TRUE);
483
484 END Schedule_Date_Not_Changed;
485
486 FUNCTION Demand_Class_Not_Changed RETURN BOOLEAN IS
487 BEGIN
488
489 IF (P_Demand_Class_Code <> P_DB_Demand_Class_Code) THEN
490
491 OE_MSG.Set_Buffer_Message('OE_SCH_DEM_CL_CHG_NOT_ALLOWED');
492 Return (FALSE);
493
494 END IF;
495
496 Return (TRUE);
497
498 END Demand_Class_Not_Changed;
499
500
501 FUNCTION Quantity_Not_Changed RETURN BOOLEAN IS
502 BEGIN
503
504 IF (P_Quantity <> P_DB_Quantity) THEN
505
506 OE_MSG.Set_Buffer_Message('OE_SCH_RES_QTY_CHG_NOT_ALLOWED');
507 Return (FALSE);
508
509 END IF;
510
511 Return (TRUE);
512
513 END Quantity_Not_Changed;
514
515
516
517 FUNCTION Subinventory_Not_Changed RETURN BOOLEAN IS
518 BEGIN
519
520 IF (P_Subinventory <> P_DB_Subinventory) THEN
521
522 OE_MSG.Set_Buffer_Message('OE_SCH_SUBINV_CHG_NOT_ALLOWED');
523 Return (FALSE);
524
525 END IF;
526
527 Return (TRUE);
528
529 END Subinventory_Not_Changed;
530
531
532 FUNCTION Lot_Not_Changed RETURN BOOLEAN IS
533 BEGIN
534
535 IF (P_Lot_Number <> P_DB_Lot_Number) THEN
536
537 OE_MSG.Set_Buffer_Message('OE_SCH_LOT_CHG_NOT_ALLOWED');
538 Return (FALSE);
539
540 END IF;
541
542 Return (TRUE);
543
544 END Lot_Not_Changed;
545
546
547 FUNCTION Revision_Not_Changed RETURN BOOLEAN IS
548 BEGIN
549
550 IF (P_Revision <> P_DB_Revision) THEN
551
552 OE_MSG.Set_Buffer_Message('OE_SCH_REV_CHG_NOT_ALLOWED');
553 Return (FALSE);
554
555 END IF;
556
557 Return (TRUE);
558
559 END Revision_Not_Changed;
560
561
562 /*
563 Beginning of validate_scheduling_attributes
564 */
565
566 BEGIN
567
568 Success_Flag := Check_Revision_Lot;
569
570 IF (Success_Flag) THEN
571
572 IF (P_Schedule_Action_Code IS NOT NULL) AND
573 (P_DB_Record_Flag = 'Y') THEN
574
575 IF (P_Schedule_Action_Code IN
576 ('UNDEMAND', 'UNRESERVE', 'UNSCHEDULE', 'ATP CHECK')) THEN
577
578 /*
579 The following statement is interesting. The order of the clauses is
580 very important. If user is undemanding or unreserving, it cannot
581 be combined with a change of warehouse, schedule_date, or demand_class.
582 */
583
584 IF (P_Schedule_Action_Code <> 'ATP CHECK') THEN
585
586 Success_Flag := (Warehouse_Not_Changed) AND
587 (Schedule_Date_Not_Changed) AND
588 (Demand_Class_Not_Changed);
589
590 END IF;
591
592 /*
593 Similar to above, the order of the and clauses is important.
594 If the action is undo something, or ATP inquiry, then users
595 cannot modify quantity, subinventory, lot or revision either!
596 */
597
598 IF (Success_Flag) THEN
599
600 Success_Flag := (Quantity_Not_Changed) AND
601 (Subinventory_Not_Changed) AND
602 (Lot_Not_Changed) AND
603 (Revision_Not_Changed);
604
605 END IF;
606
607 END IF; -- UN Actions or ATP Check
608
609 END IF; -- DB_Record_Flag
610
611 END IF; -- IF Success_Flag
612
613
614 IF (Success_Flag) THEN
615 P_Result := 'Y';
616 ELSE
617 P_Result := 'N';
618 END IF;
619
620 RETURN;
621
622 END Validate_Scheduling_Attributes;
623
624
625 END OE_LINE_DETAILS_PKG;
[Home] [Help]