1 PACKAGE BODY mrp_adjust_dates_pk AS
2 /* $Header: MRPPADTB.pls 120.1 2005/10/09 23:59:01 rgurugub noship $ */
3 -- ******************* mrp_adjust_dates_by_calendar *********************
4 PROCEDURE mrp_adjust_dates_by_calendar(
5 cal_code IN VARCHAR2,
6 except_set_id IN NUMBER,
7 user_id IN NUMBER,
8 error_msg IN OUT NOCOPY VARCHAR2) IS
9 org_id NUMBER;
10 CURSOR organizations_cur IS
11 SELECT organization_id
12 FROM mtl_parameters mtl
13 WHERE mtl.calendar_code = cal_code
14 AND mtl.calendar_exception_set_id = except_set_id;
15
16 l_error_msg VARCHAR2(240);
17
18 BEGIN
19
20 l_error_msg := error_msg;
21
22 OPEN organizations_cur;
23 LOOP
24 FETCH organizations_cur INTO
25 org_id;
26
27 EXIT WHEN organizations_cur%NOTFOUND;
28 mrp_adjust_dates_by_org(cal_code, except_set_id,
29 org_id, user_id, error_msg);
30
31 IF error_msg is not null then
32 EXIT;
33 END IF;
34
35 END LOOP;
36
37 EXCEPTION WHEN OTHERS THEN
38 error_msg := l_error_msg;
39
40 END mrp_adjust_dates_by_calendar; -- END mrp_adjust_dates_by_calendar
41
42 -- *********************** mrp_adjust_dates_by_org ********************
43
44 PROCEDURE mrp_adjust_dates_by_org(
45 cal_code IN VARCHAR2,
46 except_set_id IN NUMBER,
47 org_id IN NUMBER,
48 user_id IN NUMBER,
49 error_msg IN OUT NOCOPY VARCHAR2) IS
50 min_date DATE;
51 max_date DATE;
52 statement INTEGER := 0;
53 string_buffer CHAR(1);
54 forecast_exists BOOLEAN := FALSE;
55 demand_exists BOOLEAN := FALSE;
56
57 l_error_msg VARCHAR2(240);
58 BEGIN
59
60 l_error_msg := error_msg;
61
62 BEGIN
63 SELECT 'x' into string_buffer
64 FROM dual
65 WHERE exists
66 (SELECT schedule_date
67 FROM mrp_schedule_dates
68 WHERE organization_id = org_id);
69 demand_exists := TRUE;
70 EXCEPTION
71 WHEN NO_DATA_FOUND THEN
72 demand_exists := FALSE;
73 END;
74
75 BEGIN
76 SELECT 'x' into string_buffer
77 FROM dual
78 WHERE exists
79 (SELECT forecast_date
80 FROM mrp_forecast_dates
81 WHERE organization_id = org_id);
82 forecast_exists := TRUE;
83 EXCEPTION
84 WHEN NO_DATA_FOUND THEN
85 forecast_exists := FALSE;
86 END;
87
88
89
90 -- Select the minimum and maximum calendar_dates if no rows exist then raise
91 -- exception
92 IF forecast_exists or demand_exists THEN
93 SELECT min(calendar_date), max(calendar_date)
94 INTO min_date, max_date
95 FROM bom_calendar_dates
96 WHERE exception_set_id = except_set_id
97 AND calendar_code = cal_code;
98
99 IF min_date IS NULL THEN
100 raise no_data_found;
101 END IF;
102 statement := 1;
103
104 END IF;
105 -- Check if enough data exists in the calendar to cover the current dates
106 -- in mrp_schedule_dates
107 IF demand_exists THEN
108 SELECT 'x' INTO string_buffer
109 FROM dual
110 WHERE NOT EXISTS
111 (SELECT schedule_date
112 FROM mrp_schedule_dates
113 WHERE organization_id = org_id
114 AND schedule_date < min_date
115 AND schedule_level=2)
116 AND NOT EXISTS
117 (SELECT schedule_date
118 FROM mrp_schedule_dates
119 WHERE organization_id = org_id
120 AND DECODE(rate_end_date, NULL, schedule_date,
121 rate_end_date) > max_date
122 AND schedule_level=2);
123 statement := 2;
124 END IF;
125 -- Check if enough data exists in the calendar to cover the current dates
126 -- in mrp_forecast_dates
127 IF forecast_exists THEN
128
129 SELECT 'x' INTO string_buffer
130 FROM dual
131 WHERE NOT EXISTS
132 (SELECT forecast_date
133 FROM mrp_forecast_dates
134 WHERE organization_id = org_id
135 AND forecast_date < min_date)
136 AND NOT EXISTS
137 (SELECT forecast_date
138 FROM mrp_forecast_dates
139 WHERE organization_id = org_id
140 AND DECODE(rate_end_date, NULL, forecast_date,
141 rate_end_date) > max_date);
142 END IF;
143
144 -- Update the schedule workdate to be the next valid work date for MPS,
145 -- and the previous valid workdate for MDS.
146
147 -- For multi-org schedules we check the schedule type in the owning
148 -- org for entries in the current org
149
150 IF demand_exists THEN
151 UPDATE mrp_schedule_dates dates
152 SET /* dates.last_update_date = SYSDATE,
153 dates.last_updated_by = user_id, */
154 dates.schedule_workdate =
155 (SELECT DECODE(desig.schedule_type, 1, prior_date,
156 next_date)
157 FROM bom_calendar_dates cal,
158 mrp_schedule_designators desig
159 WHERE cal.calendar_code = cal_code
160 AND cal.exception_set_id = except_set_id
161 /*2285842 AND cal.calendar_date = dates.schedule_workdate*/
162 AND cal.calendar_date = dates.schedule_date
163 AND desig.organization_id
164 in (select organization_id
165 from mrp_plan_organizations_v orgs
166 where orgs.compile_designator =
167 dates.schedule_designator
168 and orgs.planned_organization =
169 dates.organization_id
170 union all
171 select org_id
172 from dual)
173 AND desig.schedule_designator =
174 dates.schedule_designator)
175 WHERE dates.organization_id = org_id
176 AND dates.schedule_level = 2;
177
178 -- Set the schedule date to be a valid workdate if it's repetitive or if
179 -- it's an MPS
180
181 -- We check the schedule_type from the owning org for multi-org schedules
182
183 UPDATE mrp_schedule_dates dates
184 SET dates.last_update_date = SYSDATE,
185 dates.last_updated_by = user_id,
186 dates.schedule_date = schedule_workdate
187 WHERE ((organization_id, schedule_designator) IN
188 (SELECT org_id, desig.schedule_designator
189 FROM mrp_schedule_designators desig,
190 mrp_plan_organizations_v orgs
191 WHERE orgs.planned_organization (+) = org_id
192 AND desig.organization_id = NVL(orgs.organization_id,
193 org_id)
194 AND desig.schedule_designator =
195 orgs.compile_designator (+)
196 AND desig.schedule_type = 2) OR
197 dates.rate_end_date IS NOT NULL)
198 AND dates.organization_id = org_id;
199
200 -- Always set the rate end date to be the previous valid date for repetitive
201 -- entries
202
203 UPDATE mrp_schedule_dates dates
204 SET /* dates.last_update_date = SYSDATE,
205 dates.last_updated_by = user_id, */
206 dates.rate_end_date =
207 (SELECT GREATEST(prior_date, dates.schedule_date)
208 FROM bom_calendar_dates cal
209 WHERE cal.calendar_code = cal_code
210 AND cal.exception_set_id = except_set_id
211 AND cal.calendar_date = dates.rate_end_date)
212 WHERE dates.rate_end_date IS NOT NULL
213 AND dates.schedule_level = 2
214 AND dates.organization_id = org_id;
215 END IF;
216 -- Set daily forecast entries to be valid dates
217 IF forecast_exists THEN
218
219 UPDATE mrp_forecast_dates dates
220 SET /* dates.last_update_date = SYSDATE,
221 dates.last_updated_by = user_id, */
222 dates.forecast_date =
223 (SELECT prior_date
224 FROM bom_calendar_dates cal
225 WHERE cal.calendar_code = cal_code
226 AND cal.exception_set_id = except_set_id
227 AND cal.calendar_date = dates.forecast_date)
228 WHERE dates.bucket_type = 1
229 AND dates.organization_id = org_id;
230
231 -- Set end dates for daily forecast entries to be valid dates
232
233 UPDATE mrp_forecast_dates dates
234 SET /* dates.last_update_date = SYSDATE,
235 dates.last_updated_by = user_id, */
239 WHERE cal.calendar_code = cal_code
236 dates.rate_end_date =
237 (SELECT prior_date
238 FROM bom_calendar_dates cal
240 AND cal.exception_set_id = except_set_id
241 AND cal.calendar_date = dates.rate_end_date)
242 WHERE dates.rate_end_date IS NOT NULL
243 AND dates.bucket_type = 1
244 AND organization_id = org_id;
245
246 -- Set weekly forecast entries to be valid dates
247
248 UPDATE mrp_forecast_dates dates
249 SET /* dates.last_update_date = SYSDATE,
250 dates.last_updated_by = user_id, */
251 dates.forecast_date =
252 (SELECT MAX(week_start_date)
253 FROM bom_cal_week_start_dates cal
254 WHERE cal.calendar_code = cal_code
255 AND cal.exception_set_id = except_set_id
256 AND cal.week_start_date <= dates.forecast_date)
257 WHERE dates.bucket_type = 2
258 AND dates.organization_id = org_id;
259
260
261 -- Set weekly end dates to be valid dates
262
263 UPDATE mrp_forecast_dates dates
264 SET /* dates.last_update_date = SYSDATE,
265 dates.last_updated_by = user_id, */
266 dates.rate_end_date =
267 (SELECT MAX(week_start_date)
268 FROM bom_cal_week_start_dates cal
269 WHERE cal.calendar_code = cal_code
270 AND cal.exception_set_id = except_set_id
271 AND cal.week_start_date <= dates.rate_end_date)
272 WHERE dates.rate_end_date IS NOT NULL
273 AND dates.bucket_type = 2
274 AND dates.organization_id = org_id;
275
276 -- Set monthly forecast entries to be valid dates
277
278 UPDATE mrp_forecast_dates dates
279 SET /* dates.last_update_date = SYSDATE,
280 dates.last_updated_by = user_id, */
281 dates.forecast_date =
282 (SELECT MAX(period_start_date)
283 FROM bom_period_start_dates cal
284 WHERE cal.calendar_code = cal_code
285 AND cal.exception_set_id = except_set_id
286 AND cal.period_start_date <= dates.forecast_date)
287 WHERE dates.bucket_type = 3
288 AND dates.organization_id = org_id;
289
290 -- Set monthly end dates to be valid dates
291
292 UPDATE mrp_forecast_dates dates
293 SET /* dates.last_update_date = SYSDATE,
294 dates.last_updated_by = user_id, */
295 dates.rate_end_date =
296 (SELECT MAX(period_start_date)
297 FROM bom_period_start_dates cal
298 WHERE cal.calendar_code = cal_code
299 AND cal.exception_set_id = except_set_id
300 AND cal.period_start_date <= dates.rate_end_date)
301 WHERE dates.rate_end_date IS NOT NULL
302 AND dates.bucket_type = 3
303 AND dates.organization_id = org_id;
304 END IF;
305 -- Set the snapshot and plan completion date to NULL
306 IF demand_exists or forecast_exists then
307 UPDATE mrp_plans
308 SET data_completion_date = NULL,
309 plan_completion_date = NULL
310 WHERE (compile_designator , organization_id) in
311 (SELECT compile_designator , organization_id
312 FROM mrp_plan_organizations_v
313 WHERE
314 planned_organization = org_id);
315
316 COMMIT;
317 end if;
318 error_msg := NULL;
319
320 EXCEPTION
321 WHEN no_data_found THEN
322 IF statement = 0 THEN
323 error_msg := 'GEN-calendar not compiled';
324 ELSIF statement = 1 THEN
325 error_msg := 'GEN-invalid schedules';
326 ELSE
327 error_msg := 'GEN-invalid forecasts';
328 END IF;
329 WHEN OTHERS THEN
330 error_msg := l_error_msg;
331
332 END mrp_adjust_dates_by_org; -- END mrp_adjust_dates_by_org
333 END mrp_adjust_dates_pk; -- END PACKAGE
[Home] [Help]