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