1 PACKAGE BODY WIP_CALENDAR AS
2 /* $Header: wipltesb.pls 115.13 2004/02/17 13:45:48 panagara ship $ */
3
4 PROCEDURE ESTIMATE_LEADTIME
5 (x_org_id in number,
6 x_fixed_lead in number DEFAULT 0,
7 x_var_lead in number DEFAULT 0,
8 x_quantity in number,
9 x_proc_days in number,
10 x_entity_type in number,
11 x_fusd in date,
12 x_fucd in date,
13 x_lusd in date,
14 x_lucd in date,
15 x_sched_dir in number,
16 x_est_date out nocopy date ) IS
17 new_date date;
18 lt number;
19 new_quantity number;
20 new_fixed_lead number;
21 new_proc_days number;
22 x_sched_date date;
23
24 x_working_day number; /* Fix for bug 3410450.*/
25
26 cursor cursor_forward is
27 SELECT BCD1.CALENDAR_DATE
28 FROM BOM_CALENDAR_DATES BCD1,
29 BOM_CALENDAR_DATES BCD2,
30 MTL_PARAMETERS MP
31 WHERE MP.ORGANIZATION_ID = x_org_id
32 AND BCD1.CALENDAR_CODE = MP.CALENDAR_CODE
33 AND BCD2.CALENDAR_CODE = MP.CALENDAR_CODE
34 AND BCD1.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
35 AND BCD2.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
36 AND BCD2.CALENDAR_DATE = TRUNC(x_sched_date)
37 AND BCD1.SEQ_NUM = NVL(BCD2.SEQ_NUM, BCD2.NEXT_SEQ_NUM) + CEIL(lt);
38
39 cursor cursor_backward is
40 SELECT BCD1.CALENDAR_DATE
41 FROM BOM_CALENDAR_DATES BCD1,
42 BOM_CALENDAR_DATES BCD2,
43 MTL_PARAMETERS MP
44 WHERE MP.ORGANIZATION_ID = x_org_id
45 AND BCD1.CALENDAR_CODE = MP.CALENDAR_CODE
46 AND BCD2.CALENDAR_CODE = MP.CALENDAR_CODE
47 AND BCD1.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
48 AND BCD2.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
49 AND BCD2.CALENDAR_DATE = TRUNC(x_sched_date)
50 AND BCD1.SEQ_NUM = NVL(BCD2.SEQ_NUM, BCD2.PRIOR_SEQ_NUM) -
51 --(new_proc_days) + 1 - CEIL(lt); --Bug2331915
52 (new_proc_days) - FLOOR(lt);
53
54 /* Fix for bug 3410450. Added the following cursor 'cursor_working_day' to determine
55 if the scheduled completion date is a working/non-working day.
56 */
57 cursor cursor_working_day is
58 SELECT nvl(BCD.SEQ_NUM,-1)
59 FROM BOM_CALENDAR_DATES BCD,
60 MTL_PARAMETERS MP
61 WHERE MP.ORGANIZATION_ID = x_org_id
62 AND BCD.CALENDAR_CODE = MP.CALENDAR_CODE
63 AND BCD.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
64 AND BCD.CALENDAR_DATE = TRUNC(x_sched_date);
65 BEGIN
66 new_quantity := x_quantity;
67 new_fixed_lead := NVL(x_fixed_lead,0);
68 new_proc_days := x_proc_days;
69 IF (x_sched_dir = WIP_CONSTANTS.FUSD) THEN
70 x_sched_date := x_fusd;
71 ELSIF(x_sched_dir = WIP_CONSTANTS.FUCD) THEN
72 x_sched_date := x_fucd;
73 ELSIF(x_sched_dir = WIP_CONSTANTS.LUSD) THEN
74 x_sched_date := x_lusd;
75 ELSIF(x_sched_dir = WIP_CONSTANTS.LUCD) THEN
76 x_sched_date := x_lucd;
77 END IF;
78
79 IF (x_entity_type = WIP_CONSTANTS.REPETITIVE) THEN
80 IF (x_sched_dir = WIP_CONSTANTS.LUSD) /* LUSD = 3 */ THEN
81 new_quantity := 0.0;
82 new_fixed_lead := 0.0;
83 ELSE
84 new_quantity := 1.0;
85 END IF;
86 END IF;
87
88 IF (((x_sched_dir = WIP_CONSTANTS.FUCD) /* FUCD = 2 */ AND
89 (x_entity_type = WIP_CONSTANTS.REPETITIVE)) OR
90 -- anything other than repetitive is scheduled the same
91 (nvl(x_entity_type, WIP_CONSTANTS.DISCRETE)
92 <> WIP_CONSTANTS.REPETITIVE)) THEN
93 new_proc_days := 1.0;
94 END IF;
95
96 lt := new_fixed_lead + NVL(x_var_lead,0) * new_quantity;
97
98 /* Fix for bug 3410450. The following piece of code is added for the specific case of
99 LUCD for all entity types other than 'REPETITIVE'.
100 */
101
102 IF((x_sched_dir = WIP_CONSTANTS.LUCD) AND
103 (nvl(x_entity_type, WIP_CONSTANTS.DISCRETE)
104 <> WIP_CONSTANTS.REPETITIVE)) THEN
105
106 /* To check if total lead time is an integer.
107 If the total lead time is integral, new_proc_days is set to 0 otherwise
108 it retains the prior value 1.
109 */
110 IF(mod(lt,1)=0) THEN
111 new_proc_days :=0;
112 END IF;
113
114 /* To check if scheduled completion date is a working or non working day.
115 If its a non working day, new_proc_days is reduced by 1. The exception is
116 when total lead time is 0.
117 */
118 open cursor_working_day;
119 fetch cursor_working_day into x_working_day;
120
121 IF(x_working_day = -1 and lt<>0) THEN
122 new_proc_days := new_proc_days - 1;
123 END IF;
124
125 close cursor_working_day;
126 END IF;
127
128 /*End of fix for bug 3410450 */
129 IF (x_sched_dir = WIP_CONSTANTS.FUSD) /* FUSD = 1 */ THEN
130 new_date := x_sched_date;
131 open cursor_forward;
132 fetch cursor_forward into new_date;
133 IF (cursor_forward%NOTFOUND) THEN
134 x_est_date := NULL;
135 close cursor_forward;
136 return;
137 END IF;
138 close cursor_forward;
139 ELSE /* x_sched_dir <> FUSD */
140 new_date := x_sched_date;
141
142 open cursor_backward;
143 fetch cursor_backward into new_date;
144 IF (cursor_backward%NOTFOUND) THEN
145 x_est_date := NULL;
146 close cursor_backward;
147 return;
148 END IF;
149 close cursor_backward;
150 END IF;
151 x_est_date := new_date + (x_sched_date - TRUNC(x_sched_date));
152
153 END ESTIMATE_LEADTIME;
154
155 END WIP_CALENDAR;
[Home] [Help]