[Home] [Help]
PACKAGE BODY: APPS.MRP_PLANNER_PK
Source
1 PACKAGE BODY mrp_planner_pk AS
2 /* $Header: MRPPPLNB.pls 115.14 2004/01/22 02:29:56 schaudha ship $ */
3
4 BUFFER_SIZE_LEN CONSTANT INTEGER := 1000000;
5 TYPE col_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
6 TYPE col_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
7 TYPE column_rowid IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
8 TYPE col_bool IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
9 var_debug BOOLEAN;
10
11
12 -- ********************** mb_delete_ms_outside_tf *************************
13 PROCEDURE mb_delete_ms_outside_tf(
14 arg_compile_desig IN VARCHAR2,
15 arg_org_id IN NUMBER,
16 arg_jcurr_date IN NUMBER) IS
17 BEGIN
18 mb_delete_ms_outside_tf(arg_compile_desig,
19 arg_org_id,
20 arg_jcurr_date,
21 NULL_VALUE);
22 END mb_delete_ms_outside_tf;
23
24 -- ********************** mb_delete_ms_outside_tf *************************
25 PROCEDURE mb_delete_ms_outside_tf(
26 arg_compile_desig IN VARCHAR2,
27 arg_org_id IN NUMBER,
28 arg_jcurr_date IN NUMBER,
29 arg_query_id IN NUMBER) IS
30 --1851794--
31 var_bkwd_compat VARCHAR2(2) := FND_PROFILE.VALUE('MRP_NEW_PLANNER_BACK_COMPATIBILITY');
32
33 BEGIN
34 /*-------------------------------------------------------------+
35 | We need to go to mtl_system_items because mrp_system_items |
36 | may not have been populated at that point |
37 +-------------------------------------------------------------*/
38 --1851794 For phantoms if backward compatibility is yes treat them like standard items
39 DELETE mrp_schedule_dates dates
40 WHERE EXISTS
41 (SELECT NULL
42 FROM bom_calendar_dates cal1,
43 bom_calendar_dates cal2,
44 mtl_parameters param,
45 mtl_system_items sys
46 WHERE dates.schedule_date > cal1.calendar_date
47 AND cal1.exception_set_id = cal2.exception_set_id
48 AND cal1.calendar_code = cal2.calendar_code
49 AND cal1.seq_num = cal2.prior_seq_num +
50 NVL(DECODE(sys.wip_supply_type,
51 PHANTOM_ASSY,DECODE(var_bkwd_compat,'N',0,'Y',
52 DECODE(sys.planning_time_fence_code, USER_TF,
53 CEIL(sys.planning_time_fence_days),
54 CUM_TOTAL_LT,
55 CEIL(sys.cumulative_total_lead_time),
56 CUM_MFG_LT,
57 CEIL(sys.cum_manufacturing_lead_time),
58 TOTAL_LT, CEIL(sys.full_lead_time))),
59 DECODE(sys.planning_time_fence_code, USER_TF,
60 CEIL(sys.planning_time_fence_days),
61 CUM_TOTAL_LT,
62 CEIL(sys.cumulative_total_lead_time),
63 CUM_MFG_LT,
64 CEIL(sys.cum_manufacturing_lead_time),
65 TOTAL_LT, CEIL(sys.full_lead_time))), 0)
66 AND cal2.exception_set_id = param.calendar_exception_set_id
67 AND cal2.calendar_code = param.calendar_code
68 AND cal2.calendar_date = TO_DATE(arg_jcurr_date, 'J')
69 AND param.organization_id = sys.organization_id
70 AND sys.organization_id = dates.organization_id
71 AND sys.inventory_item_id = dates.inventory_item_id)
72 AND dates.supply_demand_type = SCHEDULE_SUPPLY
73 AND dates.schedule_level IN (ORIG_SCHEDULE, UPDATED_SCHEDULE)
74 AND (arg_query_id = NULL_VALUE
75 OR
76 (dates.inventory_item_id, dates.organization_id) in
77 (SELECT number1, number2
78 from mrp_form_query
79 WHERE query_id = arg_query_id))
80 AND organization_id in
81 (select planned_organization
82 from mrp_plan_organizations_v
83 where organization_id = arg_org_id
84 and compile_designator = arg_compile_desig)
85 AND schedule_designator = arg_compile_desig;
86
87 /*-------------------------------------------------------------+
88 | We need to delete the past due planned orders where the |
89 | planning time fence is 0. |
90 | Only the planned orders with schedule_quantity OR |
91 | repetitive_daily_rate > 0 are deleted. This takes care of |
92 | The schedule consumption information. |
93 +-------------------------------------------------------------*/
94
95 DELETE mrp_schedule_dates dates
96 WHERE EXISTS
97 (SELECT NULL
98 FROM mtl_system_items sys
99 WHERE
100 NVL(DECODE(sys.wip_supply_type,
101 PHANTOM_ASSY, 0,
102 DECODE(sys.planning_time_fence_code, USER_TF,
103 CEIL(sys.planning_time_fence_days),
104 CUM_TOTAL_LT,
105 CEIL(sys.cumulative_total_lead_time),
106 CUM_MFG_LT,
107 CEIL(sys.cum_manufacturing_lead_time),
108 TOTAL_LT, CEIL(sys.full_lead_time))), 0) = 0
109 AND sys.organization_id = dates.organization_id
110 AND sys.inventory_item_id = dates.inventory_item_id)
111 AND dates.supply_demand_type = SCHEDULE_SUPPLY
112 AND dates.schedule_level IN (ORIG_SCHEDULE, UPDATED_SCHEDULE)
113 AND DECODE(dates.rate_end_date, NULL,
114 dates.schedule_quantity, dates.repetitive_daily_rate) > 0
115 AND (arg_query_id = NULL_VALUE
116 OR
117 (dates.inventory_item_id, dates.organization_id) in
118 (SELECT number1, number2
119 from mrp_form_query
120 WHERE query_id = arg_query_id))
121 AND organization_id in
122 (select planned_organization
123 from mrp_plan_organizations_v
124 where organization_id = arg_org_id
125 and compile_designator = arg_compile_desig)
126 AND schedule_designator = arg_compile_desig;
127
128 COMMIT;
129
130 UPDATE mrp_schedule_dates dates
131 SET rate_end_date =
132 (SELECT cal1.calendar_date
133 FROM bom_calendar_dates cal1,
134 bom_calendar_dates cal2,
135 mtl_parameters param,
136 mtl_system_items sys
137 WHERE dates.rate_end_date > cal1.calendar_date
138 AND dates.schedule_date <= cal1.calendar_date
139 AND cal1.exception_set_id = cal2.exception_set_id
140 AND cal1.calendar_code = cal2.calendar_code
141 AND cal1.seq_num = cal2.next_seq_num +
142 NVL(DECODE(sys.wip_supply_type,
143 PHANTOM_ASSY, 0,
144 DECODE(sys.planning_time_fence_code, USER_TF,
145 CEIL(sys.planning_time_fence_days),
146 CUM_TOTAL_LT,
147 CEIL(sys.cumulative_total_lead_time),
148 CUM_MFG_LT,
149 CEIL(sys.cum_manufacturing_lead_time),
150 TOTAL_LT, CEIL(sys.full_lead_time))), 0)
151 AND cal2.exception_set_id = param.calendar_exception_set_id
152 AND cal2.calendar_code = param.calendar_code
153 AND cal2.calendar_date = TO_DATE(arg_jcurr_date, 'J')
154 AND param.organization_id = sys.organization_id
155 AND sys.organization_id = dates.organization_id
156 AND sys.inventory_item_id = dates.inventory_item_id)
157 WHERE EXISTS
158 (SELECT NULL
159 FROM bom_calendar_dates cal1,
160 bom_calendar_dates cal2,
161 mtl_parameters param,
162 mtl_system_items sys
163 WHERE dates.rate_end_date > cal1.calendar_date
164 AND dates.schedule_date <= cal1.calendar_date
165 AND cal1.exception_set_id = cal2.exception_set_id
166 AND cal1.calendar_code = cal2.calendar_code
167 AND cal1.seq_num = cal2.next_seq_num +
168 NVL(DECODE(sys.wip_supply_type,
169 PHANTOM_ASSY, 0,
170 DECODE(sys.planning_time_fence_code, USER_TF,
171 CEIL(sys.planning_time_fence_days),
172 CUM_TOTAL_LT,
173 CEIL(sys.cumulative_total_lead_time),
174 CUM_MFG_LT,
175 CEIL(sys.cum_manufacturing_lead_time),
176 TOTAL_LT, CEIL(sys.full_lead_time))), 0)
177 AND cal2.exception_set_id = param.calendar_exception_set_id
178 AND cal2.calendar_code = param.calendar_code
179 AND cal2.calendar_date = TO_DATE(arg_jcurr_date, 'J')
180 AND param.organization_id = sys.organization_id
181 AND sys.organization_id = dates.organization_id
182 AND sys.inventory_item_id = dates.inventory_item_id)
183 AND dates.supply_demand_type = SCHEDULE_SUPPLY
184 AND dates.rate_end_date is NOT NULL
185 AND dates.schedule_level IN (ORIG_SCHEDULE, UPDATED_SCHEDULE)
186 AND (arg_query_id = NULL_VALUE
187 OR
188 (dates.inventory_item_id, dates.organization_id) in
189 (SELECT number1, number2
190 from mrp_form_query
191 WHERE query_id = arg_query_id))
192 AND organization_id in
193 (select planned_organization
194 from mrp_plan_organizations_v
195 where organization_id = arg_org_id
196 and compile_designator = arg_compile_desig)
197 AND schedule_designator = arg_compile_desig;
198
199 COMMIT;
200
201 END mb_delete_ms_outside_tf;
202
203 -- ********************** create_new_planner_mps_entries ********************
204 PROCEDURE create_new_planner_mps_entries(
205 arg_compile_desig IN VARCHAR2,
206 arg_sched_desig IN VARCHAR2,
207 arg_org_id IN NUMBER) IS
208
209 MPS_AND_DRP_PLANNING CONSTANT INTEGER := 8;
210
211 BEGIN
212 INSERT INTO mrp_schedule_dates
213 (inventory_item_id,
214 reference_schedule_id,
215 organization_id,
216 schedule_designator,
217 schedule_level,
218 schedule_date,
219 last_update_date,
220 last_updated_by,
221 creation_date,
222 created_by,
223 last_update_login,
224 schedule_quantity,
225 schedule_origination_type,
226 source_forecast_designator,
227 source_organization_id,
228 source_schedule_designator,
229 mps_transaction_id,
230 repetitive_daily_rate,
231 rate_end_date,
232 schedule_workdate,
233 original_schedule_quantity,
234 supply_demand_type,
235 project_id,
236 task_id,
237 line_id,
238 end_item_unit_number)
239 SELECT changes.inventory_item_id,
240 changes.transaction_id,
241 changes.organization_id,
242 changes.compile_designator,
243 UPDATED_SCHEDULE,
244 changes.new_schedule_date,
245 SYSDATE,
246 changes.last_updated_by,
247 SYSDATE,
248 changes.last_updated_by,
249 -1,
250 DECODE(changes.order_type,
251 PLANNED_ORDER, changes.new_order_quantity,
252 NULL),
253 SCHED_MPS_PLAN,
254 NULL,
255 changes.source_organization_id,
256 arg_sched_desig,
257 mrp_schedule_dates_s.nextval,
258 daily_rate,
259 last_unit_completion_date,
260 new_schedule_date,
261 changes.new_order_quantity,
262 SCHEDULE_SUPPLY,
263 changes.project_id,
264 changes.task_id,
265 changes.line_id,
266 changes.end_item_unit_number
267 FROM mrp_recommendations changes,
268 mrp_system_items data
269 WHERE NOT EXISTS
270 (SELECT NULL
271 FROM mrp_schedule_dates dates
272 WHERE dates.schedule_level = UPDATED_SCHEDULE
273 AND dates.organization_id = changes.organization_id
274 AND dates.inventory_item_id = changes.inventory_item_id
275 AND dates.schedule_designator = changes.compile_designator
276 AND dates.reference_schedule_id = changes.transaction_id)
277 AND changes.organization_id = data.organization_id
278 AND changes.compile_designator = data.compile_designator
279 AND changes.inventory_item_id = data.inventory_item_id
280 AND changes.order_type IN (PLANNED_ORDER, REPETITVE_SCHEDULE)
281 AND data.mrp_planning_code IN (MPS_PLANNING, MPS_AND_DRP_PLANNING)
282 AND data.organization_id IN (select planned_organization
283 from mrp_plan_organizations_v
284 where organization_id = arg_org_id
285 and compile_designator = arg_compile_desig)
286 AND data.compile_designator = arg_compile_desig;
287
288 COMMIT;
289 END create_new_planner_mps_entries;
290
291
292 -- ********************** create_orig_mps_entries *************************
293 PROCEDURE create_orig_mps_entries(
294 arg_sched_desig IN VARCHAR2,
295 arg_org_id IN NUMBER) IS
296 BEGIN
297 INSERT INTO mrp_schedule_dates
298 (inventory_item_id,
299 reference_schedule_id,
300 organization_id,
301 schedule_designator,
302 schedule_level,
303 schedule_date,
304 last_update_date,
305 last_updated_by,
306 creation_date,
307 created_by,
308 last_update_login,
309 schedule_quantity,
310 schedule_origination_type,
311 repetitive_daily_rate,
312 source_forecast_designator,
313 rate_end_date,
314 mps_transaction_id,
315 schedule_comments,
316 source_organization_id,
317 source_schedule_designator,
318 schedule_workdate,
319 original_schedule_quantity,
320 supply_demand_type)
321 SELECT inventory_item_id,
322 reference_schedule_id,
323 organization_id,
324 schedule_designator,
325 ORIG_SCHEDULE,
326 schedule_date,
327 last_update_date,
328 last_updated_by,
329 SYSDATE,
330 last_updated_by,
331 -1,
332 schedule_quantity,
333 schedule_origination_type,
334 repetitive_daily_rate,
335 source_forecast_designator,
336 rate_end_date,
337 mps_transaction_id,
338 schedule_comments,
339 source_organization_id,
340 source_schedule_designator,
341 schedule_date,
342 original_schedule_quantity,
343 supply_demand_type
344 FROM mrp_schedule_dates dates
345 WHERE NOT EXISTS
346 (SELECT NULL
347 FROM mrp_schedule_dates
348 WHERE mps_transaction_id =
349 dates.mps_transaction_id
350 AND schedule_level = ORIG_SCHEDULE)
351 AND schedule_level = UPDATED_SCHEDULE
352 AND (organization_id = arg_org_id OR arg_org_id IS NULL)
353 AND (schedule_designator = arg_sched_desig OR
354 arg_sched_desig IS NULL);
355
356 COMMIT;
357 END create_orig_mps_entries;
358
359 -- ********************** create_mbp_orig_mps_entries *************************
360 PROCEDURE create_mbp_orig_mps_entries(
361 arg_sched_desig IN VARCHAR2,
362 arg_org_id IN NUMBER) IS
363 BEGIN
364 INSERT INTO mrp_schedule_dates
365 (inventory_item_id,
366 reference_schedule_id,
367 organization_id,
368 schedule_designator,
369 schedule_level,
370 schedule_date,
371 last_update_date,
372 last_updated_by,
373 creation_date,
374 created_by,
375 last_update_login,
376 schedule_quantity,
377 schedule_origination_type,
378 repetitive_daily_rate,
379 source_forecast_designator,
380 rate_end_date,
381 mps_transaction_id,
382 schedule_comments,
383 source_organization_id,
384 source_schedule_designator,
385 schedule_workdate,
386 original_schedule_quantity,
387 supply_demand_type,
388 project_id,
389 task_id,
390 line_id)
391 SELECT inventory_item_id,
392 reference_schedule_id,
393 organization_id,
394 schedule_designator,
395 ORIG_SCHEDULE,
396 schedule_date,
397 last_update_date,
398 last_updated_by,
399 SYSDATE,
400 last_updated_by,
401 -1,
402 schedule_quantity,
403 schedule_origination_type,
404 repetitive_daily_rate,
405 source_forecast_designator,
406 rate_end_date,
407 mps_transaction_id,
408 schedule_comments,
409 source_organization_id,
410 source_schedule_designator,
411 schedule_date,
412 original_schedule_quantity,
413 supply_demand_type,
414 project_id,
415 task_id,
416 line_id
417 FROM mrp_schedule_dates dates
418 WHERE NOT EXISTS
419 (SELECT NULL
420 FROM mrp_schedule_dates
421 WHERE mps_transaction_id =
422 dates.mps_transaction_id
423 AND schedule_level = ORIG_SCHEDULE)
424 AND schedule_level = UPDATED_SCHEDULE
425 AND organization_id IN (select planned_organization
426 from mrp_plan_organizations_v
427 where organization_id = arg_org_id
428 and compile_designator = arg_sched_desig)
429 AND schedule_designator = arg_sched_desig;
430
431 END create_mbp_orig_mps_entries;
432
433 END; -- package