[Home] [Help]
PACKAGE BODY: APPS.MRP_AUTO_REDUCE_PK
Source
1 PACKAGE BODY MRP_AUTO_REDUCE_PK AS
2 /* $Header: MRPPARPB.pls 115.1 99/07/16 12:31:23 porting ship $'*/
3
4 PROCEDURE mrp_auto_reduce_mps(
5 arg_sched_mgr IN NUMBER,
6 arg_org_id IN NUMBER,
7 arg_user_id IN NUMBER,
8 arg_sched_desig IN VARCHAR2,
9 arg_request_id IN NUMBER) IS
10 -- Constant declarations
11 MPS_AUTO_NONE CONSTANT INTEGER := 1;
12 MPS_AUTO_PAST_DUE CONSTANT INTEGER := 2;
13 MPS_AUTO_DEMAND_TF CONSTANT INTEGER := 3;
14 MPS_AUTO_PLANNING_TF CONSTANT INTEGER := 4;
15 CUM_TOTAL_LT CONSTANT INTEGER := 1;
16 CUM_MFG_LT CONSTANT INTEGER := 2;
17 TOTAL_LT CONSTANT INTEGER := 3;
18 USER_TF CONSTANT INTEGER := 4;
19 UPDATED_SCHEDULE CONSTANT INTEGER := 2;
20 MTL_SUPPLY_TYPE CONSTANT INTEGER := 2;
21 SYS_YES CONSTANT INTEGER := 1;
22 MPS_RELIEF CONSTANT INTEGER := 2;
23 R_AUTO_REDUCE CONSTANT INTEGER := 4;
24
25 transaction_id mrp_schedule_dates.mps_transaction_id%TYPE;
26 sched_quantity mrp_schedule_dates.schedule_quantity%TYPE;
27 sched_date mrp_schedule_dates.schedule_date%TYPE;
28 sched_rowid ROWID;
29 var_watch_id NUMBER;
30 rows_processed NUMBER := 0;
31 VERSION CONSTANT CHAR(80) :=
32 '$Header: MRPPARPB.pls 115.1 99/07/16 12:31:23 porting ship $';
33
34 -- Declare the cursor for selecting discrete MPS entries that past the
35 -- user-defined auto-reduction date
36
37 CURSOR DISCRETE_SCHEDULES_CUR IS
38 SELECT mps_transaction_id,
39 schedule_quantity,
40 schedule_date,
41 rowid
42 FROM mrp_schedule_dates dates
43 WHERE exists
44 (SELECT NULL
45 FROM bom_calendar_dates cal1,
46 bom_calendar_dates cal2,
47 mtl_parameters param,
48 mtl_system_items sys
49 WHERE cal1.calendar_code = param.calendar_code
50 AND cal1.exception_set_id =
51 param.calendar_exception_set_id
52 AND cal1.calendar_date = TRUNC(SYSDATE)
53 AND cal2.calendar_code = param.calendar_code
54 AND cal2.exception_set_id =
55 param.calendar_exception_set_id
56 AND cal2.seq_num = cal1.next_seq_num +
57 (DECODE(sys.auto_reduce_mps,
58 MPS_AUTO_PAST_DUE,
59 0,
60 MPS_AUTO_DEMAND_TF,
61 DECODE(sys.demand_time_fence_code,
62 USER_TF,
63 CEIL(
64 NVL(sys.demand_time_fence_days, 0)),
65 CUM_TOTAL_LT,
66 CEIL(
67 NVL(sys.cumulative_total_lead_time,
68 0)),
69 CUM_MFG_LT,
70 CEIL(
71 NVL(sys.cum_manufacturing_lead_time,
72 0)),
73 TOTAL_LT,
74 CEIL(NVL(sys.full_lead_time, 0))),
75 MPS_AUTO_PLANNING_TF,
76 DECODE(sys.planning_time_fence_code,
77 USER_TF,
78 CEIL(
79 NVL(sys.planning_time_fence_days,
80 0)),
81 CUM_TOTAL_LT,
82 CEIL(
83 NVL(sys.cumulative_total_lead_time,
84 0)),
85 CUM_MFG_LT,
86 CEIL(NVL(
87 sys.cum_manufacturing_lead_time,0)),
88 TOTAL_LT,
89 CEIL(NVL(sys.full_lead_time, 0)))))
90 AND dates.schedule_date < cal2.calendar_date
91 AND param.organization_id = dates.organization_id
92 AND sys.organization_id = dates.organization_id
93 AND sys.auto_reduce_mps <> MPS_AUTO_NONE
94 AND sys.auto_reduce_mps is not null
95 AND sys.inventory_item_id = dates.inventory_item_id)
96 AND dates.schedule_level = UPDATED_SCHEDULE
97 AND dates.supply_demand_type = MTL_SUPPLY_TYPE
98 AND dates.schedule_quantity <> 0
99 AND dates.rate_end_date is NULL
100 AND dates.organization_id = DECODE(arg_sched_mgr, SYS_YES,
101 dates.organization_id, arg_org_id)
102 AND dates.schedule_designator = DECODE(arg_sched_mgr,
103 SYS_YES, dates.schedule_designator,
104 arg_sched_desig);
105
106 BEGIN
107
108 -- If called for discrete schedules select all the discrete entries that
109 -- are past the auto-reduction date, insert a row into
110 -- mrp_schedule_consumptions, and then reduce the MPS entry to zero.
111
112 var_watch_id := mrp_print_pk.start_watch('GEN-updated',
113 arg_request_id,
114 arg_user_id,
115 'ENTITY',
116 'mrp_schedule_dates',
117 'N');
118 OPEN DISCRETE_SCHEDULES_CUR;
119 LOOP
120 FETCH DISCRETE_SCHEDULES_CUR INTO
121 transaction_id,
122 sched_quantity,
123 sched_date,
124 sched_rowid;
125
126 EXIT WHEN DISCRETE_SCHEDULES_CUR%NOTFOUND;
127
128 rows_processed := rows_processed + 1;
129
130 INSERT INTO mrp_schedule_consumptions(
131 transaction_id,
132 relief_type,
133 disposition_type,
134 disposition_id,
135 line_num,
136 last_update_date,
137 last_updated_by,
138 creation_date,
139 created_by,
140 last_update_login,
141 order_date,
142 order_quantity,
143 relief_quantity,
144 schedule_date,
145 program_application_id,
146 program_id,
147 program_update_date)
148 VALUES (
152 NULL,
149 transaction_id,
150 MPS_RELIEF,
151 R_AUTO_REDUCE,
153 NULL,
154 SYSDATE,
155 arg_user_id,
156 SYSDATE,
157 arg_user_id,
158 arg_user_id,
159 sched_date,
160 sched_quantity,
161 sched_quantity,
162 sched_date,
163 NULL,
164 NULL,
165 NULL);
166
167 UPDATE mrp_schedule_dates
168 SET schedule_quantity = 0,
169 last_updated_by = arg_user_id,
170 last_update_date = SYSDATE
171 WHERE rowid = sched_rowid;
172
173 END LOOP;
174
175 mrp_print_pk.stop_watch(arg_request_id,
176 var_watch_id,
177 rows_processed);
178
179 -- Delete all the repetitive entries that are past the auto-reduce date
180
181 var_watch_id := mrp_print_pk.start_watch('GEN-deleting from table',
182 arg_request_id,
183 arg_user_id,
184 'ROUTINE',
185 'mrarmps_auto_reduce_mps',
186 'N',
187 'TABLE',
188 'mrp_schedule_dates',
189 'N');
190 DELETE mrp_schedule_dates dates
191 WHERE exists
192 (SELECT NULL
193 FROM bom_calendar_dates cal1,
194 bom_calendar_dates cal2,
195 mtl_parameters param,
196 mtl_system_items sys
197 WHERE cal1.calendar_code = param.calendar_code
198 AND cal1.exception_set_id =
199 param.calendar_exception_set_id
200 AND cal1.calendar_date = TRUNC(SYSDATE)
201 AND cal2.calendar_code = param.calendar_code
202 AND cal2.exception_set_id =
203 param.calendar_exception_set_id
204 AND cal2.seq_num = cal1.next_seq_num +
205 (DECODE(sys.auto_reduce_mps,
206 MPS_AUTO_PAST_DUE,
207 0,
208 MPS_AUTO_DEMAND_TF,
209 DECODE(sys.demand_time_fence_code,
210 USER_TF,
211 CEIL(
212 NVL(sys.demand_time_fence_days, 0)),
213 CUM_TOTAL_LT,
214 CEIL(
218 CEIL(NVL(
215 NVL(sys.cumulative_total_lead_time,
216 0)),
217 CUM_MFG_LT,
219 sys.cum_manufacturing_lead_time,0)),
220 TOTAL_LT,
221 CEIL(NVL(sys.full_lead_time, 0))),
222 MPS_AUTO_PLANNING_TF,
223 DECODE(sys.planning_time_fence_code,
224 USER_TF,
225 CEIL(NVL(
226 sys.planning_time_fence_days, 0)),
227 CUM_TOTAL_LT,
228 CEIL(NVL(
229 sys.cumulative_total_lead_time, 0)),
230 CUM_MFG_LT,
231 CEIL(NVL(
232 sys.cum_manufacturing_lead_time,0)),
233 TOTAL_LT,
234 CEIL(NVL(sys.full_lead_time, 0)))))
235 AND dates.rate_end_date < cal2.calendar_date
236 AND param.organization_id = dates.organization_id
237 AND sys.organization_id = dates.organization_id
238 AND sys.auto_reduce_mps <> MPS_AUTO_NONE
239 AND sys.auto_reduce_mps is not null
240 AND sys.inventory_item_id = dates.inventory_item_id)
241 AND dates.schedule_level = UPDATED_SCHEDULE
242 AND dates.supply_demand_type = MTL_SUPPLY_TYPE
243 AND dates.rate_end_date is NOT NULL
244 AND dates.organization_id = DECODE(arg_sched_mgr, SYS_YES,
245 dates.organization_id, arg_org_id)
246 AND dates.schedule_designator = DECODE(arg_sched_mgr,
247 SYS_YES, dates.schedule_designator,
248 arg_sched_desig);
249
250 rows_processed := SQL%ROWCOUNT;
251
252 mrp_print_pk.stop_watch(arg_request_id,
253 var_watch_id,
254 rows_processed);
255 -- Truncate all the entries that start before the auto-reduce date but end
256 -- after the auto-reduce date
257 var_watch_id := mrp_print_pk.start_watch('GEN-updated',
258 arg_request_id,
259 arg_user_id,
260 'ENTITY',
261 'mrp_schedule_dates',
262 'N');
263
264 UPDATE mrp_schedule_dates dates
265 SET last_update_date = SYSDATE,
266 last_updated_by = arg_user_id,
267 dates.schedule_date =
268 (SELECT cal2.calendar_date
269 FROM bom_calendar_dates cal1,
270 bom_calendar_dates cal2,
271 mtl_parameters param,
272 mtl_system_items sys
273 WHERE cal1.calendar_code = param.calendar_code
277 AND cal2.calendar_code = param.calendar_code
274 AND cal1.exception_set_id =
275 param.calendar_exception_set_id
276 AND cal1.calendar_date = TRUNC(SYSDATE)
278 AND cal2.exception_set_id =
279 param.calendar_exception_set_id
280 AND cal2.seq_num = cal1.next_seq_num +
281 (DECODE(sys.auto_reduce_mps,
282 MPS_AUTO_PAST_DUE,
283 0,
284 MPS_AUTO_DEMAND_TF,
285 DECODE(sys.demand_time_fence_code,
286 USER_TF,
287 CEIL(NVL(
288 sys.demand_time_fence_days, 0)),
289 CUM_TOTAL_LT,
290 CEIL(NVL(
291 sys.cumulative_total_lead_time, 0)),
292 CUM_MFG_LT,
293 CEIL(NVL(
294 sys.cum_manufacturing_lead_time,
295 0)),
296 TOTAL_LT,
297 CEIL(NVL(sys.full_lead_time, 0))),
298 MPS_AUTO_PLANNING_TF,
299 DECODE(sys.planning_time_fence_code,
300 USER_TF,
301 CEIL(NVL(
302 sys.planning_time_fence_days,
303 0)),
304 CUM_TOTAL_LT,
305 CEIL(NVL(
306 sys.cumulative_total_lead_time,
307 0)),
308 CUM_MFG_LT,
309 CEIL(NVL(
310 sys.cum_manufacturing_lead_time,
311 0)),
312 TOTAL_LT,
313 CEIL(NVL(sys.full_lead_time, 0)))))
314 AND dates.rate_end_date >= cal2.calendar_date
315 AND dates.schedule_date < cal2.calendar_date
316 AND param.organization_id = dates.organization_id
317 AND sys.organization_id = dates.organization_id
318 AND sys.auto_reduce_mps <> MPS_AUTO_NONE
319 AND sys.auto_reduce_mps is not null
320 AND sys.inventory_item_id = dates.inventory_item_id)
321 WHERE exists
322 (SELECT NULL
323 FROM bom_calendar_dates cal1,
324 bom_calendar_dates cal2,
325 mtl_parameters param,
326 mtl_system_items sys
327 WHERE cal1.calendar_code = param.calendar_code
328 AND cal1.exception_set_id =
329 param.calendar_exception_set_id
330 AND cal1.calendar_date = TRUNC(SYSDATE)
331 AND cal2.calendar_code = param.calendar_code
332 AND cal2.exception_set_id =
333 param.calendar_exception_set_id
337 0,
334 AND cal2.seq_num = cal1.next_seq_num +
335 (DECODE(sys.auto_reduce_mps,
336 MPS_AUTO_PAST_DUE,
338 MPS_AUTO_DEMAND_TF,
339 DECODE(sys.demand_time_fence_code,
340 USER_TF,
341 CEIL(NVL(
342 sys.demand_time_fence_days, 0)),
343 CUM_TOTAL_LT,
344 CEIL(NVL(
345 sys.cumulative_total_lead_time, 0)),
346 CUM_MFG_LT,
347 CEIL(NVL(
348 sys.cum_manufacturing_lead_time,
349 0)),
350 TOTAL_LT,
351 CEIL(NVL(sys.full_lead_time, 0))),
352 MPS_AUTO_PLANNING_TF,
353 DECODE(sys.planning_time_fence_code,
354 USER_TF,
355 CEIL(NVL(
356 sys.planning_time_fence_days,
357 0)),
358 CUM_TOTAL_LT,
359 CEIL(NVL(
360 sys.cumulative_total_lead_time,
361 0)),
362 CUM_MFG_LT,
363 CEIL(NVL(
364 sys.cum_manufacturing_lead_time,
365 0)),
366 TOTAL_LT,
367 CEIL(NVL(sys.full_lead_time, 0)))))
368 AND dates.rate_end_date >= cal2.calendar_date
369 AND dates.schedule_date < cal2.calendar_date
370 AND param.organization_id = dates.organization_id
371 AND sys.organization_id = dates.organization_id
372 AND sys.auto_reduce_mps <> MPS_AUTO_NONE
373 AND sys.auto_reduce_mps is not null
374 AND sys.inventory_item_id = dates.inventory_item_id)
375 AND dates.schedule_level = UPDATED_SCHEDULE
376 AND dates.supply_demand_type = MTL_SUPPLY_TYPE
377 AND dates.rate_end_date is NOT NULL
378 AND dates.organization_id = DECODE(arg_sched_mgr, SYS_YES,
379 dates.organization_id, arg_org_id)
380 AND dates.schedule_designator = DECODE(arg_sched_mgr,
381 SYS_YES, dates.schedule_designator,
382 arg_sched_desig);
383 rows_processed := SQL%ROWCOUNT;
384
385 mrp_print_pk.stop_watch(arg_request_id,
386 var_watch_id,
387 rows_processed);
388 END mrp_auto_reduce_mps;
389
390 END MRP_AUTO_REDUCE_PK;