[Home] [Help]
PACKAGE BODY: APPS.MSD_ASCP_FLOW
Source
1 PACKAGE BODY MSD_ASCP_FLOW AS
2 /* $Header: msdxscpb.pls 120.2 2006/05/26 10:49:36 sjagathe noship $ */
3
4 PROCEDURE LAUNCH_ASCP_PLAN
5 ( itemtype in varchar2
6 , itemkey in varchar2
7 , actid in number
8 , funcmode in varchar2
9 , resultout out NOCOPY varchar2
10 ) IS
11
12 l_default_scenario_id number;
13 l_default_plan_name varchar2(200);
14 l_plan_id number;
15 PlanID varchar2(200);
16 g_owner varchar2(50) := null;
17 l_dp_plan_id number;
18 l_org_id number;
19 l_instance_id number;
20 l_scn_count number;
21 l_sch_count number;
22 l_attach_scn_count number;
23 l_plan_launched varchar2(10);
24
25 CURSOR c_plan_id (l_default_plan_name IN VARCHAR2) IS
26 SELECT plan_id
27 FROM msc_plans
28 WHERE compile_designator = l_default_plan_name;
29
30 CURSOR c_scn_count (l_dp_plan_id IN NUMBER, l_default_scenario_id IN NUMBER) IS
31 SELECT count(*)
32 FROM msd_dp_ascp_scenarios_v
33 WHERE demand_plan_id = l_dp_plan_id
34 AND scenario_id = l_default_scenario_id;
35
36 CURSOR c_org_id (l_plan_id IN NUMBER) IS
37 SELECT organization_id, sr_instance_id
38 FROM msc_plan_organizations
39 WHERE plan_id = l_plan_id;
40
41 CURSOR c_sch_count (l_plan_id IN NUMBER,l_instance_id IN NUMBER, l_org_id IN NUMBER, l_default_scenario_iD IN NUMBER) IS
42 SELECT count(*)
43 FROM msc_plan_schedules
44 WHERE plan_id = l_plan_id
45 AND organization_id = l_org_id
46 AND sr_instance_id = l_instance_id
47 AND input_schedule_id = l_default_scenario_id
48 AND designator_type = 7;
49
50 CURSOR c_attach_scenario (l_plan_id IN NUMBER, l_dp_plan_id IN NUMBER) IS
51 SELECT count(*)
52 FROM msc_plan_schedules
53 WHERE plan_id = l_plan_id
54 AND input_schedule_id in ( SELECT scenario_id
55 FROM msd_dp_ascp_scenarios_v
56 WHERE demand_plan_id = l_dp_plan_id)
57 AND designator_type = 7;
58
59
60 BEGIN
61 l_plan_launched :='N';
62 resultout :='COMPLETE:Y';
63
64 g_owner:=wf_engine.GetItemAttrText(Itemtype => ItemType,
65 Itemkey => ItemKey,
66 aname => 'DPADMIN');
67
68 msd_wf.setowner(g_owner);
69
70 -- msd_wf.selector(null, null, null, 'TEST_CTX', resultout);
71
72 /* Get Demand Plan Id */
73 PlanID:=wf_engine.GetItemAttrText(Itemtype => ItemType,
74 Itemkey => ItemKey,
75 aname => 'ODPPLAN');
76
77 l_dp_plan_id := to_number(PlanID);
78
79 /* Bug# 5248221 Analyze tables MSD_DP_SCENARIO_ENTRIES, MSD_DP_SCENARIO_REVISIONS and
80 MSD_DP_PLANNING_PERCENTAGES before populating the denorm tables
81 */
82 MSD_ANALYZE_TABLES.analyze_table('MSD_DP_SCENARIO_ENTRIES',null);
83 MSD_ANALYZE_TABLES.analyze_table('MSD_DP_SCENARIO_REVISIONS',null);
84 MSD_ANALYZE_TABLES.analyze_table('MSD_DP_PLANNING_PERCENTAGES',null);
85
86 /* Populate denormalized msd_planning_percentage table and
87 msd_dp_ascp_scn_entries tables */
88
89 Populate_denorm_tables(l_dp_plan_id);
90
91
92
93
94 /* Get Default ASCP Unconstrained Plan Name */
95 l_default_plan_name := NULL;
96 l_default_plan_name := FND_PROFILE.VALUE('MSC_DEFAULT_UNCONST_PLAN');
97
98 IF l_default_plan_name is not null THEN
99
100 l_plan_id := NULL;
101
102 OPEN c_plan_id(l_default_plan_name);
103 FETCH c_plan_id INTO l_plan_id;
104 CLOSE c_plan_id;
105
106 /* Get Default DP scenario Id */
107 l_default_scenario_id := NULL;
108 l_default_scenario_id := FND_PROFILE.VALUE('MSD_DEFAULT_DP_SCENARIO');
109
110 IF l_default_scenario_id is not null THEN
111
112 l_scn_count := 0;
113
114 /* Check If default scenario has been defined in Demand Plan */
115 OPEN c_scn_count(l_dp_plan_id, l_default_scenario_id);
116 FETCH c_scn_count INTO l_scn_count;
117 CLOSE c_scn_count;
118
119 IF (l_scn_count > 0) then
120
121 /* Get Organization Id, Instance Id for default ASCP unconstrained plan */
122 OPEN c_org_id(l_plan_id);
123 LOOP
124 l_org_id := NULL;
125 l_instance_id := NULL;
126
127 FETCH c_org_id INTO l_org_id, l_instance_id;
128 EXIT WHEN c_org_id%NOTFOUND;
129
130 /* Check if default scenario has been attached to ASCP Plan Schedules */
131 l_sch_count := 0;
132
133 OPEN c_sch_count(l_plan_id, l_instance_id, l_org_id, l_default_scenario_id);
134 FETCH c_sch_count INTO l_sch_count;
135 CLOSE c_sch_count;
136
137 /* If default scenario is not attached to ASCP Plan Schedules, Attach the default scenario to ASCP plan */
138 IF (l_sch_count = 0) then
139
140 insert into msc_plan_schedules (
141 PLAN_ID,
142 ORGANIZATION_ID,
143 INPUT_SCHEDULE_ID,
144 SR_INSTANCE_ID,
145 INPUT_TYPE,
146 DESIGNATOR_TYPE,
147 LAST_UPDATE_DATE,
148 LAST_UPDATED_BY,
149 CREATION_DATE,
150 CREATED_BY )
151 values (
152 l_plan_id,
153 l_org_id,
154 l_default_scenario_id,
155 l_instance_id,
156 1,
157 7,
158 sysdate,
159 FND_GLOBAL.USER_ID,
160 sysdate,
161 FND_GLOBAL.USER_ID );
162
163 END IF;
164
165 END LOOP;
166 CLOSE c_org_id;
167
168 COMMIT;
169
170 fnd_file.put_line(fnd_file.log, 'Launching ASCP engine with default unconstrained plan');
171
172 -- launch ASCP engine with default unconstrained plan
173 MSC_X_CP_FLOW.Start_ASCP_Engine_WF ( p_constrained_plan_flag => 2 );
174
175 l_plan_launched := 'Y';
176
177 ELSE
178 l_plan_launched := 'N';
179 END IF;
180
181 END IF;
182
183 IF l_plan_launched = 'N' THEN
184
185 OPEN c_attach_scenario(l_plan_id, l_dp_plan_id);
186 FETCH c_attach_scenario INTO l_attach_scn_count;
187 CLOSE c_attach_scenario;
188
189 IF l_attach_scn_count > 0 then
190
191 fnd_file.put_line(fnd_file.log, 'Launching ASCP engine with default unconstrained plan');
192
193 -- launch ASCP engine with default unconstrained plan
194 MSC_X_CP_FLOW.Start_ASCP_Engine_WF ( p_constrained_plan_flag => 2 );
195
196 END IF;
197 END IF;
198 END IF;
199
200 EXCEPTION
201 WHEN OTHERS THEN
202 fnd_file.put_line(fnd_file.log, 'Errors in Launching ASCP Plan from DP');
203 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
204
205 END;
206
207 function get_priority(p_demand_plan_id in number,
208 p_scenario_id in number,
209 p_sr_instance_id in number,
210 p_bucket_type in number,
211 p_start_time in date,
212 p_end_time in date,
213 p_inventory_item_id in number,
214 p_demand_class in varchar2)
215 return number
216 is
217
218 l_priority number :=to_number(NULL);
219 l_dmd_prty_scen_id number := -999;
220 l_sr_inventory_item_id number;
221
222 begin
223
224
225 select nvl(dmd_priority_scenario_id,-999) into l_dmd_prty_scen_id
226 from msd_dp_scenarios
227 where demand_plan_id = p_demand_plan_id
228 and scenario_id = p_scenario_id;
229
230 if (l_dmd_prty_scen_id = -999) then
231 return l_priority;
232 else
233
234 select sr_inventory_item_id into l_sr_inventory_item_id
235 from msc_apps_instances mai, msc_system_items msi
236 where msi.plan_id = -1
237 and msi.sr_instance_id = p_sr_instance_id
238 and msi.organization_id = mai.validation_org_id
239 and msi.inventory_item_id = p_inventory_item_id
240 and mai.instance_id = p_sr_instance_id;
241
242 begin
243
244 select quantity into l_priority
245 from msd_dp_scenario_entries
246 where demand_plan_id = p_demand_plan_id
247 and scenario_id = l_dmd_prty_scen_id
248 and decode(time_lvl_id, 9, 1, 1, 2, 3) = p_bucket_type
249 and time_lvl_val_from = p_start_time
250 and TIME_LVL_VAL_TO = p_end_time
251 and to_number(INSTANCE) = p_sr_instance_id
252 and to_number(decode(ltrim(sr_product_lvl_pk, '.0123456789'), null, sr_product_lvl_pk, -1)) = l_sr_inventory_item_id
253 and decode(demand_class_lvl_id,40, null,34, decode(demand_class,'-777', null,demand_class),demand_class) = p_demand_class
254 and rownum < 2;
255
256 return l_priority;
257
258 exception
259 when no_data_found then
260 return l_priority;
261 when others then
262 return l_priority;
263 end;
264
265
266 end if;
267
268 exception
269 when others then
270 return l_priority;
271 end get_priority;
272
273
274
275 PROCEDURE populate_denorm_tables(p_demand_plan_id number) IS
276
277 /* Bug# 5248221 - Get the scenario ids which should
278 * be published to ASCP
279 */
280 CURSOR c_get_scenario_ids
281 IS
282 SELECT scenario_id
283 FROM msd_dp_scenarios a
284 WHERE
285 a.demand_plan_id = p_demand_plan_id
286 AND NOT EXISTS (SELECT 1
287 FROM msd_dp_scenarios b
288 WHERE
289 b.demand_plan_id = a.demand_plan_id
290 AND b.dmd_priority_scenario_id = a.scenario_id);
291
292 /* Bug# 5248221 - Get the demand priority scenario ids
293 */
294 CURSOR c_get_dmd_priority_scn_ids
295 IS
296 SELECT dmd_priority_scenario_id
297 FROM msd_dp_scenarios
298 WHERE
299 demand_plan_id = p_demand_plan_id
300 AND dmd_priority_scenario_id is not null;
301
302 /* Bug# 5248221 - Variables to store the list of scenario ids
303 */
304 x_scenario_id_list VARCHAR2(1000) := '';
305 x_dmd_pri_scenario_id_list VARCHAR2(1000) := '';
306 x_sql_stmt VARCHAR2(20000);
307
308 x_first_time NUMBER := -1;
309
310 BEGIN
311
312 /* Bug# 5248221 - Get the scenarios ids which should
313 * be published to ASCP
314 */
315 x_first_time := 1;
316 FOR x_scenario_id_rec IN c_get_scenario_ids
317 LOOP
318
319 IF x_first_time = 1 THEN
320 x_scenario_id_list := ' IN (' || x_scenario_id_rec.scenario_id;
321 x_first_time := 0;
322 ELSE
323 x_scenario_id_list := x_scenario_id_list || ',' || x_scenario_id_rec.scenario_id;
324 END IF;
325
326 END LOOP;
327
328 IF x_first_time = 0 THEN
329 x_scenario_id_list := x_scenario_id_list || ')';
330 ELSE
331 x_scenario_id_list := ' IN (null)';
332 END IF;
333
334 /* Bug# 5248221 - Get the demand priority scenarios ids
335 */
336 x_first_time := 1;
337 FOR x_dmd_priority_scn_id_rec IN c_get_dmd_priority_scn_ids
338 LOOP
339
340 IF x_first_time = 1 THEN
341 x_dmd_pri_scenario_id_list := ' IN (' || x_dmd_priority_scn_id_rec.dmd_priority_scenario_id;
342 x_first_time := 0;
343 ELSE
344 x_dmd_pri_scenario_id_list := x_dmd_pri_scenario_id_list || ',' || x_dmd_priority_scn_id_rec.dmd_priority_scenario_id;
345 END IF;
346
347 END LOOP;
348
349 IF x_first_time = 0 THEN
350 x_dmd_pri_scenario_id_list := x_dmd_pri_scenario_id_list || ')';
351 ELSE
352 x_dmd_pri_scenario_id_list := ' IN (null)';
353 END IF;
354
355 /* Bug# 5248221 */
356 /* For Scenario Entries */
357 x_sql_stmt := 'DELETE from msd_dp_scn_entries_denorm ' ||
358 'WHERE demand_plan_id = ' || p_demand_plan_id || ' ' ||
359 'AND scenario_id ' || x_scenario_id_list;
360
361 EXECUTE IMMEDIATE x_sql_stmt;
362
363 /* Bug# 5181742
364 * Removed the function call MSD_ASCP_FLOW.get_priority
365 */
366 x_sql_stmt := 'INSERT INTO msd_dp_scn_entries_denorm( ' ||
367 'demand_plan_id, ' ||
368 'scenario_id, ' ||
369 'demand_id, ' ||
370 'bucket_type, ' ||
371 'start_time, ' ||
372 'end_time, ' ||
373 'quantity, ' ||
374 'sr_organization_id, ' ||
375 'sr_instance_id, ' ||
376 'sr_inventory_item_id, ' ||
377 'error_type, ' ||
378 'forecast_error, ' ||
379 'inventory_item_id, ' ||
380 'sr_ship_to_loc_id, ' ||
381 'sr_customer_id, ' ||
382 'sr_zone_id, ' ||
383 'priority, ' ||
384 'dp_uom_code, ' ||
385 'ascp_uom_code, ' ||
386 'demand_class, ' ||
387 'unit_price, ' ||
388 'creation_date, ' ||
389 'created_by, ' ||
390 'last_update_login ) ' ||
391 'SELECT ' || p_demand_plan_id || ', ' ||
392 'fcst_sce.scenario_id, ' ||
393 'fcst_sce.demand_id, ' ||
394 'fcst_sce.bucket_type, ' ||
395 'fcst_sce.start_time, ' ||
396 'fcst_sce.end_time, ' ||
397 'fcst_sce.quantity, ' ||
398 'fcst_sce.sr_organization_id, ' ||
399 'fcst_sce.sr_instance_id, ' ||
400 'fcst_sce.sr_inventory_item_id, ' ||
401 'fcst_sce.error_type, ' ||
402 'fcst_sce.forecast_error, ' ||
403 'fcst_sce.inventory_item_id, ' ||
404 'fcst_sce.sr_ship_to_loc_id, ' ||
405 'fcst_sce.sr_customer_id, ' ||
406 'fcst_sce.sr_zone_id, ' ||
407 'dmpr_sce.quantity, ' ||
408 'fcst_sce.dp_uom_code, ' ||
409 'fcst_sce.ascp_uom_code, ' ||
410 'decode (fcst_sce.demand_class,''-100'', null, fcst_sce.demand_class), ' ||
411 'fcst_sce.unit_price, ' ||
412 '''' || sysdate || ''',' ||
413 FND_GLOBAL.USER_ID || ',' ||
414 FND_GLOBAL.LOGIN_ID || ' ' ||
415 'FROM ' ||
416 '(SELECT mdas.scenario_id SCENARIO_ID, ' ||
417 'mdas.demand_id DEMAND_ID, ' ||
418 'mdas.bucket_type BUCKET_TYPE, ' ||
419 'mdas.start_time START_TIME, ' ||
420 'mdas.end_time END_TIME, ' ||
421 'mdas.quantity QUANTITY, ' ||
422 'mdas.sr_organization_id SR_ORGANIZATION_ID, ' ||
423 'mdas.sr_instance_id SR_INSTANCE_ID, ' ||
424 'mdas.sr_inventory_item_id SR_INVENTORY_ITEM_ID, ' ||
425 'mdas.error_type ERROR_TYPE, ' ||
426 'mdas.forecast_error FORECAST_ERROR, ' ||
427 'mdas.inventory_item_id INVENTORY_ITEM_ID, ' ||
428 'mdas.sr_ship_to_loc_id SR_SHIP_TO_LOC_ID, ' ||
429 'mdas.sr_customer_id SR_CUSTOMER_ID, ' ||
430 'mdas.sr_zone_id SR_ZONE_ID, ' ||
431 'mdas.dp_uom_code DP_UOM_CODE, ' ||
432 'mdas.ascp_uom_code ASCP_UOM_CODE, ' ||
433 'nvl(mdas.demand_class,''-100'') DEMAND_CLASS, ' ||
434 'mdas.unit_price UNIT_PRICE, ' ||
435 'mdas.dmd_priority_scenario_id DMD_PRIORITY_SCENARIO_ID, ' ||
436 'mdas.time_lvl_id TIME_LVL_ID ' ||
437 'FROM msd_dp_ascp_scn_entries_v mdas ' ||
438 'WHERE mdas.demand_plan_id = ' || p_demand_plan_id || ' ' ||
439 'AND mdas.scenario_id ' || x_scenario_id_list || ') fcst_sce, ' ||
440 '(SELECT mdse.scenario_id SCENARIO_ID, ' ||
441 'mdse.time_lvl_id TIME_LVL_ID, ' ||
442 'mdse.time_lvl_val_from START_TIME, ' ||
443 'mdse.time_lvl_val_to END_TIME, ' ||
444 'max(mdse.quantity) QUANTITY, ' ||
445 'to_number(mdse.instance) SR_INSTANCE_ID, ' ||
446 'to_number(decode(ltrim(sr_product_lvl_pk, ''.0123456789''), ' ||
447 'null, ' ||
448 'sr_product_lvl_pk, ' ||
449 '-1)) SR_INVENTORY_ITEM_ID, ' ||
450 'nvl(decode(mdse.demand_class_lvl_id, ' ||
451 '40, ' ||
452 'null, ' ||
453 '34, ' ||
454 'decode(mdse.demand_class, ' ||
455 '''-777'', ' ||
456 'null, ' ||
457 'mdse.demand_class), ' ||
458 'mdse.demand_class), ' ||
459 '''-100'') DEMAND_CLASS ' ||
460 'from msd_dp_scenarios mds, ' ||
461 'msd_dp_scenario_entries mdse ' ||
462 'WHERE mds.demand_plan_id = ' || p_demand_plan_id || ' ' ||
463 'AND mds.scenario_id ' || x_dmd_pri_scenario_id_list ||
464 'AND mds.demand_plan_id = mdse.demand_plan_id ' ||
465 'AND mds.scenario_id = mdse.scenario_id ' ||
466 'AND mds.last_revision = mdse.revision ' ||
467 'GROUP BY mdse.scenario_id, ' ||
468 'mdse.time_lvl_id, ' ||
469 'mdse.time_lvl_val_from, ' ||
470 'mdse.time_lvl_val_to, ' ||
471 'mdse.instance, ' ||
472 'mdse.SR_PRODUCT_LVL_PK, ' ||
473 'mdse.demand_class_lvl_id, ' ||
474 'mdse.demand_class) dmpr_sce ' ||
475 'WHERE fcst_sce.dmd_priority_scenario_id = dmpr_sce.scenario_id (+) ' ||
476 'AND fcst_sce.time_lvl_id = dmpr_sce.time_lvl_id (+) ' ||
477 'AND fcst_sce.start_time = dmpr_sce.start_time (+) ' ||
478 'AND fcst_sce.end_time = dmpr_sce.end_time (+) ' ||
479 'AND fcst_sce.sr_instance_id = dmpr_sce.sr_instance_id (+) ' ||
480 'AND fcst_sce.sr_inventory_item_id = dmpr_sce.sr_inventory_item_id (+) ' ||
481 'AND fcst_sce.demand_class = dmpr_sce.demand_class (+) ';
482
483 EXECUTE IMMEDIATE x_sql_stmt;
484
485
486 /* For Planning Percentage */
487 /* Bug# 5181742 */
488 x_sql_stmt := 'DELETE from msd_dp_planning_pct_denorm ' ||
489 'WHERE demand_plan_id = ' || p_demand_plan_id || ' ' ||
490 'AND dp_scenario_id ' || x_scenario_id_list;
491
492 EXECUTE IMMEDIATE x_sql_stmt;
493
494 x_sql_stmt := 'INSERT INTO msd_dp_planning_pct_denorm( ' ||
495 'demand_plan_id , ' ||
496 'dp_scenario_id , ' ||
497 'component_sequence_id , ' ||
498 'orig_component_sequence_id , ' ||
499 'bill_sequence_id , ' ||
500 'sr_instance_id , ' ||
501 'organization_id , ' ||
502 'inventory_item_id , ' ||
503 'assembly_item_id , ' ||
504 'date_to , ' ||
505 'date_from , ' ||
506 'planning_factor , ' ||
507 'plan_percentage_type , ' ||
508 'creation_date , ' ||
509 'created_by , ' ||
510 'last_update_login ' ||
511 ') ' ||
512 'SELECT ' ||
513 'demand_plan_id , ' ||
514 'dp_scenario_id , ' ||
515 'component_sequence_id , ' ||
516 'orig_component_sequence_id , ' ||
517 'bill_sequence_id , ' ||
518 'sr_instance_id , ' ||
519 'organization_id , ' ||
520 'inventory_item_id , ' ||
521 'assembly_item_id , ' ||
522 'date_to , ' ||
523 'date_from , ' ||
524 'planning_factor , ' ||
525 'plan_percentage_type , ' ||
526 '''' || sysdate || ''',' ||
527 FND_GLOBAL.USER_ID || ',' ||
528 FND_GLOBAL.LOGIN_ID ||
529 ' FROM msd_dp_planning_percentages_v ' ||
530 'WHERE demand_plan_id = ' || p_demand_plan_id || ' ' ||
531 'AND dp_scenario_id ' || x_scenario_id_list;
532
533 EXECUTE IMMEDIATE x_sql_stmt;
534
535
536 /* Bug# 5248221 Analyze tables MSD_DP_SCN_ENTRIES_DENORM and
537 MSD_DP_PLANNING_PCT_DENORM after populating them to update statistics
538 */
539 commit;
540
541 MSD_ANALYZE_TABLES.analyze_table('MSD_DP_SCN_ENTRIES_DENORM',null);
542 MSD_ANALYZE_TABLES.analyze_table('MSD_DP_PLANNING_PCT_DENORM',null);
543
544
545 EXCEPTION
546 WHEN OTHERS THEN
547 fnd_file.put_line(fnd_file.log, 'Errors in populating denormalized tables');
548 fnd_file.put_line(fnd_file.log, substr(SQLERRM, 1, 1000));
549 raise;
550
551 END populate_denorm_tables;
552
553
554 END MSD_ASCP_FLOW;