1 PACKAGE BODY ZPB_SOLVE AS
2 /* $Header: zpbsolve.plb 120.14 2007/12/05 12:51:14 mbhat ship $ */
3
4 /*****************************************************************
5 * PROPAGATE INPUT SELECTIONS *
6 * *
7 * *
8 * *
9 ****************************************************************/
10 PROCEDURE propagateInput(p_ac_id IN ZPB_SOLVE_MEMBER_DEFS.ANALYSIS_CYCLE_ID%TYPE,
11 p_from_member IN ZPB_SOLVE_MEMBER_DEFS.MEMBER%TYPE)
12
13 IS
14 fromSource NUMBER;
15 ipRec ZPB_SOLVE_INPUT_SELECTIONS%ROWTYPE;
16 isSetSrcPropFlag boolean := FALSE;
17
18 CURSOR c1 IS
19 SELECT * FROM ZPB_SOLVE_INPUT_SELECTIONS
20 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
21 BEGIN
22 SELECT SOURCE_TYPE INTO fromSource
23 FROM ZPB_SOLVE_MEMBER_DEFS
24 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
25
26 IF(fromSource <> CALCULATED_SOURCE AND fromSource <> AGGREGATED_SOURCE) THEN
27 FOR i IN propagateList.FIRST..propagateList.LAST
28 LOOP
29 IF(propagateSourceType(i) = WS_INPUT_SOURCE OR propagateSourceType(i) = INIT_WS_INPUT_SOURCE OR
30 (propagateSourceType(i) = LOADED_SOURCE AND fromSource = LOADED_SOURCE))THEN
31 isSetSrcPropFlag := TRUE;
32 --delete all i/p selections if target's source type is =INPUT
33 -- For CASES:C1-a,C1-b,C2-b
34 DELETE FROM ZPB_SOLVE_INPUT_SELECTIONS
35 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(i);
36 --insert
37 ELSE
38 isSetSrcPropFlag := TRUE;
39 DELETE ZPB_SOLVE_INPUT_SELECTIONS
40 where member = propagateList(i)
41 and analysis_cycle_id = p_ac_id
42 and dimension in ( select dimension from zpb_solve_input_selections
43 where member = p_from_member
44 and analysis_cycle_id = p_ac_id);
45
46 END IF;
47 INSERT INTO ZPB_SOLVE_INPUT_SELECTIONS
48 (ANALYSIS_CYCLE_ID,
49 MEMBER,
50 MEMBER_ORDER,
51 DIMENSION,
52 HIERARCHY,
53 SELECTION_NAME,
54 SELECTION_PATH,
55 PROPAGATED_FLAG,
56 CREATED_BY,
57 CREATION_DATE,
58 LAST_UPDATED_BY,
59 LAST_UPDATE_DATE,
60 LAST_UPDATE_LOGIN)
61 SELECT ANALYSIS_CYCLE_ID,
62 propagateList(i),
63 propagateOrder(i),
64 DIMENSION,
65 HIERARCHY,
66 SELECTION_NAME,
67 SELECTION_PATH,
68 'Y',
69 fnd_global.USER_ID,
70 SYSDATE,
71 fnd_global.USER_ID,
72 SYSDATE,
73 fnd_global.LOGIN_ID
74 FROM ZPB_SOLVE_INPUT_SELECTIONS
75 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
76
77 END LOOP;
78 END IF;
79
80 --Update PROPAGATED_FLAG of source member.
81 IF isSetSrcPropFlag = TRUE THEN
82
83 UPDATE ZPB_SOLVE_INPUT_SELECTIONS
84 SET PROPAGATED_FLAG ='Y'
85 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
86 END IF;
87
88 EXCEPTION
89 WHEN OTHERS THEN
90 IF c1%ISOPEN THEN
91 CLOSE c1;
92 END IF;
93 ROLLBACK;
94
95 END propagateInput;
96
97
98
99
100 /****************************************************************
101 * COPY DIMENSION HANDLING INFO *
102 * *
103 * *
104 *****************************************************************/
105 PROCEDURE copyDimHandlingInfo(p_ac_id IN ZPB_SOLVE_MEMBER_DEFS.ANALYSIS_CYCLE_ID%TYPE,
106 p_from_member IN ZPB_SOLVE_MEMBER_DEFS.MEMBER%TYPE,
107 p_to_index IN INTEGER,
108 p_dimensionality_flag IN VARCHAR2 DEFAULT 'NO')
109 IS
110
111 dimRec ZPB_LINE_DIMENSIONALITY%ROWTYPE;
112
113 CURSOR c1 IS
114 SELECT * FROM ZPB_LINE_DIMENSIONALITY
115 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
116 BEGIN
117 --Update PROPAGATED_FLAG of source member.
118 UPDATE ZPB_LINE_DIMENSIONALITY
119 SET PROPAGATED_FLAG ='Y'
120 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
121 --Copy dim handling info.
122 FOR dimRec IN c1
123 LOOP
124 IF p_dimensionality_flag = 'RECREATE' THEN
125
126 DELETE FROM ZPB_LINE_DIMENSIONALITY
127 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(p_to_index);
128
129 INSERT INTO ZPB_LINE_DIMENSIONALITY
130 (ANALYSIS_CYCLE_ID,
131 MEMBER,
132 MEMBER_ORDER,
133 DIMENSION,
134 SUM_MEMBERS_NUMBER,
135 SUM_MEMBERS_FLAG ,
136 EXCLUDE_FROM_SOLVE_FLAG,
137 FORCE_INPUT_FLAG,
138 SUM_SELECTION_NAME,
139 SUM_SELECTION_PATH,
140 PROPAGATED_FLAG,
141 CREATED_BY,
142 CREATION_DATE,
143 LAST_UPDATED_BY,
144 LAST_UPDATE_DATE,
145 LAST_UPDATE_LOGIN)
146 SELECT
147 ANALYSIS_CYCLE_ID,
148 propagateList(p_to_index),
149 propagateOrder(p_to_index),
150 DIMENSION,
151 SUM_MEMBERS_NUMBER,
152 SUM_MEMBERS_FLAG ,
153 EXCLUDE_FROM_SOLVE_FLAG,
154 FORCE_INPUT_FLAG,
155 SUM_SELECTION_NAME,
156 SUM_SELECTION_PATH,
157 'Y',
158 fnd_global.USER_ID,
159 SYSDATE,
160 fnd_global.USER_ID,
161 SYSDATE,
162 fnd_global.LOGIN_ID
163 FROM ZPB_LINE_DIMENSIONALITY
164 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
165
166 ELSE
167 UPDATE ZPB_LINE_DIMENSIONALITY
168 SET SUM_MEMBERS_NUMBER = dimRec.SUM_MEMBERS_NUMBER,
169 SUM_MEMBERS_FLAG = dimRec.SUM_MEMBERS_FLAG,
170 EXCLUDE_FROM_SOLVE_FLAG = dimRec.EXCLUDE_FROM_SOLVE_FLAG,
171 FORCE_INPUT_FLAG = dimRec.FORCE_INPUT_FLAG,
172 SUM_SELECTION_NAME = dimRec.SUM_SELECTION_NAME,
173 SUM_SELECTION_PATH = dimRec.SUM_SELECTION_PATH,
174 PROPAGATED_FLAG = 'Y',
175 LAST_UPDATED_BY = fnd_global.USER_ID,
176 LAST_UPDATE_DATE = SYSDATE,
177 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
178 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(p_to_index) AND
179 DIMENSION =dimRec.DIMENSION;
180 END IF;
181 END LOOP;
182
183 EXCEPTION
184 WHEN OTHERS THEN
185 IF c1%ISOPEN THEN
186 CLOSE c1;
187 END IF;
188 ROLLBACK;
189
190 END copyDimHandlingInfo;
191
192
193 /****************************************************************
194 * COPY INPUT SELECTIONS *
195 * *
196 * *
197 *****************************************************************/
198
199 PROCEDURE copyInputSelections(p_ac_id IN ZPB_SOLVE_MEMBER_DEFS.ANALYSIS_CYCLE_ID%TYPE,
200 p_from_member IN ZPB_SOLVE_MEMBER_DEFS.MEMBER%TYPE,
201 p_to_index IN INTEGER,
202 p_dimensionality_flag IN VARCHAR2 DEFAULT 'NO')
203 IS
204 ipRec ZPB_SOLVE_INPUT_SELECTIONS%ROWTYPE;
205 CURSOR c1 IS
206
207
208 SELECT * FROM ZPB_SOLVE_INPUT_SELECTIONS
209 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
210 BEGIN
211 --Update PROPAGATED_FLAG of source member.
212 UPDATE ZPB_SOLVE_INPUT_SELECTIONS
213 SET PROPAGATED_FLAG ='Y'
214 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
215
216 IF p_dimensionality_flag = 'RECREATE' THEN
217
218 DELETE FROM ZPB_SOLVE_INPUT_SELECTIONS
219 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(p_to_index);
220
221
222 INSERT INTO ZPB_SOLVE_INPUT_SELECTIONS
223 (ANALYSIS_CYCLE_ID,
224 MEMBER,
225 MEMBER_ORDER,
226 DIMENSION,
227 HIERARCHY,
228 SELECTION_NAME,
229 SELECTION_PATH,
230 PROPAGATED_FLAG,
231 CREATED_BY,
232 CREATION_DATE,
233 LAST_UPDATED_BY,
234 LAST_UPDATE_DATE,
235 LAST_UPDATE_LOGIN)
236 SELECT
237 ANALYSIS_CYCLE_ID,
238 propagateList(p_to_index),
239 propagateOrder(p_to_index),
240 DIMENSION,
241 HIERARCHY,
242 SELECTION_NAME,
243 SELECTION_PATH,
244 'Y',
245 fnd_global.USER_ID,
246 SYSDATE,
247 fnd_global.USER_ID,
248 SYSDATE,
249 fnd_global.LOGIN_ID
250 FROM ZPB_SOLVE_INPUT_SELECTIONS
251 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
252 ELSE
253 FOR ipRec IN c1 LOOP
254 UPDATE ZPB_SOLVE_INPUT_SELECTIONS
255 SET
256 SELECTION_NAME = ipRec.SELECTION_NAME,
257 SELECTION_PATH = ipRec.SELECTION_PATH,
258 PROPAGATED_FLAG = 'Y',
259 LAST_UPDATED_BY = fnd_global.USER_ID,
260 LAST_UPDATE_DATE = SYSDATE,
261 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
262 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(p_to_index) AND
263 DIMENSION =ipRec.DIMENSION AND HIERARCHY = ipRec.HIERARCHY;
264 END LOOP;
265 END IF;
266
267 EXCEPTION
268 WHEN OTHERS THEN
269 IF c1%ISOPEN THEN
270 CLOSE c1;
271 END IF;
272 ROLLBACK;
273
274
275
276 END copyInputSelections;
277
278
279 PROCEDURE copyOutputSelections(p_ac_id IN ZPB_SOLVE_MEMBER_DEFS.ANALYSIS_CYCLE_ID%TYPE,
280 p_from_member IN ZPB_SOLVE_MEMBER_DEFS.MEMBER%TYPE,
281 p_to_index IN INTEGER,
282 p_dimensionality_flag IN VARCHAR2)
283 IS
284 opRec ZPB_SOLVE_OUTPUT_SELECTIONS%ROWTYPE;
285 CURSOR c1 IS
286 SELECT * FROM ZPB_SOLVE_OUTPUT_SELECTIONS
287 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
288 BEGIN
289 --Update PROPAGATED_FLAG of source member.
290 UPDATE ZPB_SOLVE_OUTPUT_SELECTIONS
291 SET PROPAGATED_FLAG ='Y'
292 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
293
294 IF p_dimensionality_flag = 'RECREATE' THEN
295
296 DELETE FROM ZPB_SOLVE_OUTPUT_SELECTIONS
297 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(p_to_index);
298
299
300 INSERT INTO ZPB_SOLVE_OUTPUT_SELECTIONS
301 (ANALYSIS_CYCLE_ID,
302 MEMBER,
303 MEMBER_ORDER,
304 DIMENSION,
305 HIERARCHY,
306 SELECTION_NAME,
307 SELECTION_PATH,
308 PROPAGATED_FLAG,
309 MATCH_INPUT_FLAG,
310 CREATED_BY,
311 CREATION_DATE,
312 LAST_UPDATED_BY,
313 LAST_UPDATE_DATE,
314 LAST_UPDATE_LOGIN)
315 SELECT
316 ANALYSIS_CYCLE_ID,
317 propagateList(p_to_index),
318 propagateOrder(p_to_index),
319 DIMENSION,
320 HIERARCHY,
321 SELECTION_NAME,
322 SELECTION_PATH,
323 'Y',
324 MATCH_INPUT_FLAG,
325 fnd_global.USER_ID,
326 SYSDATE,
327 fnd_global.USER_ID,
328 SYSDATE,
329 fnd_global.LOGIN_ID
330 FROM ZPB_SOLVE_OUTPUT_SELECTIONS
331 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
332 ELSIF p_dimensionality_flag = 'COPY' THEN
333 FOR opRec IN c1 LOOP
334 UPDATE ZPB_SOLVE_OUTPUT_SELECTIONS
335 SET
336 SELECTION_NAME = opRec.SELECTION_NAME,
337 SELECTION_PATH = opRec.SELECTION_PATH,
338 PROPAGATED_FLAG = 'Y',
339 LAST_UPDATED_BY = fnd_global.USER_ID,
340 LAST_UPDATE_DATE = SYSDATE,
341 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
342 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(p_to_index) AND
343 DIMENSION =opRec.DIMENSION AND HIERARCHY = opRec.HIERARCHY;
344 END LOOP;
345 END IF;
346
347 EXCEPTION
348 WHEN OTHERS THEN
349 IF c1%ISOPEN THEN
350 CLOSE c1;
351 END IF;
352 ROLLBACK;
353
354
355 END copyOutputSelections;
356
357
358 PROCEDURE removeCalcObjectInfo(p_ac_id IN ZPB_SOLVE_MEMBER_DEFS.ANALYSIS_CYCLE_ID%TYPE,
359 p_to_member IN ZPB_SOLVE_MEMBER_DEFS.MEMBER%TYPE)
360 IS
361 BEGIN
362 UPDATE ZPB_SOLVE_MEMBER_DEFS
363 SET CALCSTEP_PATH = NULL,
364 CALC_TYPE = NULL,
365 CALC_DESCRIPTION = NULL,
366 CALC_PARAMETERS = NULL,
367 MODEL_EQUATION = NULL,
368 LAST_UPDATED_BY = fnd_global.USER_ID,
369 LAST_UPDATE_DATE = SYSDATE,
370 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
371 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_to_member;
372 END removeCalcObjectInfo;
373
374 PROCEDURE deleteInializedInputSettings(p_ac_id IN ZPB_DATA_INITIALIZATION_DEFS.ANALYSIS_CYCLE_ID%TYPE,
375 p_to_member IN ZPB_DATA_INITIALIZATION_DEFS.MEMBER%TYPE)
376 IS
377 BEGIN
378 DELETE ZPB_DATA_INITIALIZATION_DEFS
379 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_to_member;
380
381
382 DELETE ZPB_COPY_DIM_MEMBERS
383 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND LINE_MEMBER_ID = p_to_member;
384 END deleteInializedInputSettings;
385
386
387 PROCEDURE copyInializedInputSettings(p_ac_id IN ZPB_DATA_INITIALIZATION_DEFS.ANALYSIS_CYCLE_ID%TYPE,
388 p_from_member IN ZPB_DATA_INITIALIZATION_DEFS.MEMBER%TYPE,
389 p_to_member IN ZPB_DATA_INITIALIZATION_DEFS.MEMBER%TYPE)
390 IS
391
392 l_source_query_name ZPB_DATA_INITIALIZATION_DEFS.SOURCE_QUERY_NAME%TYPE;
393 l_target_query_name ZPB_DATA_INITIALIZATION_DEFS.TARGET_QUERY_NAME%TYPE;
394
395 CURSOR src_trg_qry_names_cur(p_ac_id IN ZPB_DATA_INITIALIZATION_DEFS.ANALYSIS_CYCLE_ID%TYPE,
396 p_from_member IN ZPB_DATA_INITIALIZATION_DEFS.MEMBER%TYPE) IS
397 SELECT SOURCE_QUERY_NAME,TARGET_QUERY_NAME
398 FROM ZPB_DATA_INITIALIZATION_DEFS
399 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
400
401 BEGIN
402 /* Bug#5092815, Commented for because we copy the initialize settings
403 from source line item.
404 OPEN src_trg_qry_names_cur(p_ac_id,p_from_member);
405 FETCH src_trg_qry_names_cur INTO l_source_query_name,l_target_query_name;
406 CLOSE src_trg_qry_names_cur;
407
408 IF(l_source_query_name is NOT NULL) THEN
409 l_source_query_name := 'CD_SOURCE_'||p_to_member;
410 END IF;
411
412 IF(l_target_query_name is NOT NULL) THEN
413 l_target_query_name := 'CD_TARGET_'||p_to_member;
414 END IF;
415 */
416
417 --INSERT into ZPB_DATA_INITIALIZATION_DEFS table.
418 INSERT INTO ZPB_DATA_INITIALIZATION_DEFS
419 (ANALYSIS_CYCLE_ID,
420 MEMBER,
421 SOURCE_VIEW,
422 LAG_TIME_PERIODS,
423 LAG_TIME_LEVEL,
424 CHANGE_NUMBER,
425 PERCENTAGE_FLAG,
426 QUERY_PATH,
427 SOURCE_QUERY_NAME,
428 TARGET_QUERY_NAME,
429 CREATED_BY,
430 CREATION_DATE,
431 LAST_UPDATED_BY,
432 LAST_UPDATE_DATE,
433 LAST_UPDATE_LOGIN,
434 PROPAGATED_FLAG)
435 SELECT ANALYSIS_CYCLE_ID,
436 p_to_member,
437 SOURCE_VIEW,
438 LAG_TIME_PERIODS,
439 LAG_TIME_LEVEL,
440 CHANGE_NUMBER,
441 PERCENTAGE_FLAG,
442 QUERY_PATH,
443 source_query_name,
444 target_query_name,
445 fnd_global.USER_ID,
446 SYSDATE,
447 fnd_global.USER_ID,
448 SYSDATE,
449 fnd_global.LOGIN_ID,
450 PROPAGATED_FLAG
451 FROM ZPB_DATA_INITIALIZATION_DEFS
452 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
453
454 INSERT INTO zpb_copy_dim_members
455 (DIM,
456 ANALYSIS_CYCLE_ID,
457 SOURCE_NUM_MEMBERS,
458 TARGET_NUM_MEMBERS,
459 CREATED_BY,
460 CREATION_DATE,
461 LAST_UPDATED_BY,
462 LAST_UPDATE_DATE,
463 LAST_UPDATE_LOGIN,
464 SAME_SELECTION,
465 LINE_MEMBER_ID)
466 SELECT DIM,
467 ANALYSIS_CYCLE_ID,
468 SOURCE_NUM_MEMBERS,
469 TARGET_NUM_MEMBERS,
470 fnd_global.USER_ID,
471 SYSDATE,
472 fnd_global.USER_ID,
473 SYSDATE,
474 fnd_global.LOGIN_ID,
475 SAME_SELECTION,
476 p_to_member
477 FROM zpb_copy_dim_members
478 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND LINE_MEMBER_ID = p_from_member;
479
480 END copyInializedInputSettings;
481
482
483
484 /***********************************************************************
485 * CHECK MATCH_INPUT_FLAG *
486 * If ZPB_SOLVE_OUTPUT_SELECTIONS.MATCH_INPUT_FLAG = 'Y' for *
487 * Loaded,i/p or worksheet i/p line member *
488 * then before changing the source type to calc or hier total *
489 * copy the input selection to output selection and set *
490 * MATCH_INPUT_FLAG to 'N' *
491 ***********************************************************************/
492
493 PROCEDURE checkMatchInputToOutputFlag(p_ac_id IN ZPB_SOLVE_OUTPUT_SELECTIONS.ANALYSIS_CYCLE_ID%TYPE,
494 p_from_member IN ZPB_SOLVE_OUTPUT_SELECTIONS.MEMBER%TYPE)
495 IS
496 l_selectionPath ZPB_SOLVE_INPUT_SELECTIONS.SELECTION_PATH%TYPE;
497 l_selectionName ZPB_SOLVE_INPUT_SELECTIONS.SELECTION_NAME%TYPE;
498
499 CURSOR c1 IS
500 SELECT MEMBER_ORDER,DIMENSION,HIERARCHY,
501 SELECTION_PATH,SELECTION_NAME,MATCH_INPUT_FLAG
502 FROM ZPB_SOLVE_OUTPUT_SELECTIONS
503 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member
504 FOR UPDATE;
505
506 CURSOR c2(p_ac_id ZPB_SOLVE_INPUT_SELECTIONS.ANALYSIS_CYCLE_ID%TYPE,
507 p_from_member ZPB_SOLVE_INPUT_SELECTIONS.MEMBER%TYPE,
508 p_member_order ZPB_SOLVE_INPUT_SELECTIONS.MEMBER_ORDER%TYPE,
509 p_dimension ZPB_SOLVE_INPUT_SELECTIONS.DIMENSION%TYPE,
510 p_hierarchy ZPB_SOLVE_INPUT_SELECTIONS.HIERARCHY%TYPE)IS
511 SELECT SELECTION_PATH,SELECTION_NAME
512 FROM ZPB_SOLVE_INPUT_SELECTIONS
513 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member AND
514 MEMBER_ORDER = p_member_order AND DIMENSION = p_dimension AND
515 HIERARCHY = p_hierarchy;
516 BEGIN
517 FOR opRec IN c1 LOOP
518 IF opRec.MATCH_INPUT_FLAG = 'Y' THEN
519
520 OPEN c2(p_ac_id,p_from_member,opRec.MEMBER_ORDER,opRec.DIMENSION,opRec.HIERARCHY);
521 FETCH c2 INTO l_selectionPath,l_selectionName;
522 CLOSE c2;
523
524 UPDATE ZPB_SOLVE_OUTPUT_SELECTIONS
525 SET SELECTION_PATH = l_selectionPath,
526 SELECTION_NAME = l_selectionName,
527 MATCH_INPUT_FLAG = 'N'
528 WHERE CURRENT OF c1;
529
530 END IF;
531 END LOOP;
532
533 EXCEPTION
534 WHEN OTHERS THEN
535 IF c1%ISOPEN THEN
536 CLOSE c1;
537 END IF;
538 IF c2%ISOPEN THEN
539 CLOSE c2;
540 END IF;
541 ROLLBACK;
542 END;
543
544
545
546
547 /****************************************************************
548 * PROPAGATE SOURCE TYPE&(DIM INFO or I/P SELECTIONS *
549 * *
550 * *
551 *****************************************************************/
552 PROCEDURE propagateCalc(p_ac_id IN ZPB_SOLVE_MEMBER_DEFS.ANALYSIS_CYCLE_ID%TYPE,
553 p_from_member IN ZPB_SOLVE_MEMBER_DEFS.MEMBER%TYPE,
554 p_prop_dimhandling IN INTEGER,
555 p_prop_input IN INTEGER,
556 p_prop_output IN INTEGER)
557
558
559 IS
560 fromSource ZPB_SOLVE_MEMBER_DEFS.SOURCE_TYPE%TYPE;
561 updateSolve BOOLEAN;
562 l_calcDesc ZPB_SOLVE_MEMBER_DEFS.CALC_DESCRIPTION%TYPE;
563 l_calcType ZPB_SOLVE_MEMBER_DEFS.CALC_TYPE%TYPE;
564 l_calcParams ZPB_SOLVE_MEMBER_DEFS.CALC_PARAMETERS%TYPE;
565 l_modelEquation ZPB_SOLVE_MEMBER_DEFS.MODEL_EQUATION%TYPE;
566 BEGIN
567 SELECT SOURCE_TYPE INTO fromSource
568 FROM ZPB_SOLVE_MEMBER_DEFS
569 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
570
571 --Check whether Source Type of Target Member needs updation
572 --Bug#5092815, changed into bulk operation
573 FORALL i IN propagateList.FIRST..propagateList.LAST
574 UPDATE ZPB_SOLVE_MEMBER_DEFS
575 SET SOURCE_TYPE = fromSource,
576 LAST_UPDATED_BY = fnd_global.USER_ID,
577 LAST_UPDATE_DATE = SYSDATE,
578 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
579 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(i);
580
581 --Bug#5092815, mark the source line as propagated, so that
582 --the same settings can be copied to target line
583 IF fromsource=INIT_WS_INPUT_SOURCE THEN
584
585 UPDATE ZPB_DATA_INITIALIZATION_DEFS
586 SET propagated_flag='Y'
587 WHERE ANALYSIS_CYCLE_ID=p_ac_id
588 AND MEMBER=p_from_member;
589
590 END IF;
591
592 FOR i IN propagateList.FIRST..propagateList.LAST
593 LOOP
594 UPDATE ZPB_SOLVE_MEMBER_DEFS
595 SET
596 SOURCE_TYPE = fromSource,
597 LAST_UPDATED_BY = fnd_global.USER_ID,
598 LAST_UPDATE_DATE = SYSDATE,
599 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
600 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(i);
601 END LOOP;
602
603 FOR i IN propagateList.FIRST..propagateList.LAST
604 LOOP
605
606 IF fromSource = LOADED_SOURCE THEN
607 --For CASE:D1-a,D1-b,D1-c
608 IF p_prop_dimhandling = iTrueValue THEN
609 copyDimHandlingInfo(p_ac_id,p_from_member,i,'RECREATE');
610 END IF;
611 IF p_prop_input = iTrueValue THEN
612 copyInputSelections(p_ac_id,p_from_member,i,'RECREATE');
613 END IF;
614
615 --Patch B
616 IF p_prop_output = iTrueValue THEN
617 copyOutputSelections(p_ac_id,p_from_member,i,'RECREATE');
618
619 ELSE -- delete the o/p selections for non hier dims
620 deleteOutputSelections(p_ac_id,i);
621 END IF;
622
623
624
625 --Remove intialized settings if the source member's source type is INIT_WS_INPUT_SOURCE
626 IF propagateSourceType(i) = INIT_WS_INPUT_SOURCE THEN
627 deleteInializedInputSettings(p_ac_id,propagateList(i));
628 END IF;
629 --Remove CalcStep Obj info if the source member's source type is CALCULATED_SOURCE
630
631 IF propagateSourceType(i) = CALCULATED_SOURCE THEN
632 removeCalcObjectInfo(p_ac_id,propagateList(i));
633 END IF;
634 ELSIF fromSource = WS_INPUT_SOURCE THEN
635 IF propagateSourceType(i) = LOADED_SOURCE THEN
636 DELETE ZPB_LINE_DIMENSIONALITY WHERE
637 ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER=propagateList(i);
638 END IF;
639 IF p_prop_input = iTrueValue THEN
640 copyInputSelections(p_ac_id,p_from_member,i,'RECREATE');
641 END IF;
642
643 --Patch B
644
645 IF p_prop_output = iTrueValue THEN
646 copyOutputSelections(p_ac_id,p_from_member,i,'RECREATE');
647
648 ELSE -- delete the o/p selections for non hier dims
649 deleteOutputSelections(p_ac_id,i);
650 END IF;
651
652
653 --Remove intialized settings if the source member's source type is INIT_WS_INPUT_SOURCE
654 IF propagateSourceType(i) = INIT_WS_INPUT_SOURCE THEN
655
656 deleteInializedInputSettings(p_ac_id,propagateList(i));
657 END IF;
658 --Remove CalcStep Obj info if the source member's source type is CALCULATED_SOURCE
659 IF propagateSourceType(i) = CALCULATED_SOURCE THEN
660 removeCalcObjectInfo(p_ac_id,propagateList(i));
661 END IF;
662
663 ELSIF fromSource = INIT_WS_INPUT_SOURCE THEN
664 IF propagateSourceType(i) = LOADED_SOURCE THEN
665 DELETE ZPB_LINE_DIMENSIONALITY WHERE
666 ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER=propagateList(i);
667 END IF;
668 IF p_prop_input = iTrueValue THEN
669 copyInputSelections(p_ac_id,p_from_member,i,'RECREATE');
670 END IF;
671 IF propagateSourceType(i) = INIT_WS_INPUT_SOURCE THEN
672 deleteInializedInputSettings(p_ac_id,propagateList(i));
673 END IF;
674
675 --Patch B
676 IF p_prop_output = iTrueValue THEN
677 copyOutputSelections(p_ac_id,p_from_member,i,'RECREATE');
678
679 ELSE -- delete the o/p selections for non hier dims
680 deleteOutputSelections(p_ac_id,i);
681 END IF;
682
683 --Remove CalcStep Obj info if the source member's source type is CALCULATED_SOURCE
684 IF propagateSourceType(i) = CALCULATED_SOURCE THEN
685 removeCalcObjectInfo(p_ac_id,propagateList(i));
686 END IF;
687 --For all source types of source member copy the initilized input settings from source member.
688 copyInializedInputSettings(p_ac_id,p_from_member,propagateList(i));
689
690 ELSIF(fromSource = CALCULATED_SOURCE OR fromSource = AGGREGATED_SOURCE )THEN
691
692 IF(propagateSourceType(i) = LOADED_SOURCE) THEN
693 --For CASES:A3-a
694 checkMatchInputToOutputFlag(p_ac_id,propagateList(i));
695
696 DELETE ZPB_LINE_DIMENSIONALITY WHERE
697 ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER=propagateList(i);
698
699 DELETE ZPB_SOLVE_INPUT_SELECTIONS WHERE
700 ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER=propagateList(i);
701
702 ELSIF(propagateSourceType(i) = WS_INPUT_SOURCE) THEN
703 --For CASES:A3-b
704 checkMatchInputToOutputFlag(p_ac_id,propagateList(i));
705
706 DELETE ZPB_SOLVE_INPUT_SELECTIONS WHERE
707 ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER=propagateList(i);
708
709 ELSIF(propagateSourceType(i) = INIT_WS_INPUT_SOURCE) THEN
710 --For CASES:A3-b
711 checkMatchInputToOutputFlag(p_ac_id,propagateList(i));
712
713 DELETE ZPB_SOLVE_INPUT_SELECTIONS WHERE
714 ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER=propagateList(i);
715
716 deleteInializedInputSettings(p_ac_id,propagateList(i));
717 ELSIF(propagateSourceType(i) = CALCULATED_SOURCE AND fromSource = AGGREGATED_SOURCE )THEN
718 removeCalcObjectInfo(p_ac_id,propagateList(i));
719 END IF;
720
721 --Patch B
722 IF p_prop_output = iTrueValue THEN
723 copyOutputSelections(p_ac_id,p_from_member,i,'RECREATE');
724 ELSIF fromSource = AGGREGATED_SOURCE THEN -- delete the o/p selections for non hier dims
725 deleteOutputSelections(p_ac_id,i);
726 /*IF propagateSourceType(i) <> CALCULATED_SOURCE THEN
727 copyOutputSelections(p_ac_id,p_from_member,i,'COPY');
728 ELSIF propagateSourceType(i) = CALCULATED_SOURCE THEN
729 copyOutputSelections(p_ac_id,p_from_member,i,'RECREATE');
730 END IF;*/
731 END IF;
732
733 --Copy Calc related columns
734 IF(fromSource = CALCULATED_SOURCE) THEN
735
736 SELECT CALC_DESCRIPTION,CALC_TYPE,CALC_PARAMETERS,MODEL_EQUATION
737 INTO l_calcDesc,l_calcType,l_calcParams,l_modelEquation
738
739 FROM ZPB_SOLVE_MEMBER_DEFS
740 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
741
742 UPDATE ZPB_SOLVE_MEMBER_DEFS
743 SET CALCSTEP_PATH = propagateList(i),
744 CALC_TYPE = l_calcType,
745 CALC_DESCRIPTION = l_calcDesc,
746 CALC_PARAMETERS = l_calcParams,
747 MODEL_EQUATION = l_modelEquation,
748 LAST_UPDATED_BY = fnd_global.USER_ID,
749 LAST_UPDATE_DATE = SYSDATE,
750 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
751 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(i);
752
753 END IF;
754
755 END IF;
756 END LOOP;
757 END propagateCalc;
758
759 /****************************************************************
760 * PROPAGATE ALLOCATIONS *
761 * *
762 * *
763 *****************************************************************/
764
765 PROCEDURE propagateAlloc(p_ac_id IN ZPB_SOLVE_MEMBER_DEFS.ANALYSIS_CYCLE_ID%TYPE,
766
767 p_from_member IN ZPB_SOLVE_MEMBER_DEFS.MEMBER%TYPE)
768
769 IS
770 BEGIN
771 --delete existing rows
772 FORALL i IN propagateList.FIRST..propagateList.LAST
773 DELETE FROM ZPB_SOLVE_ALLOCATION_DEFS
774 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(i);
775 --add new rows
776 FORALL i IN propagateList.FIRST..propagateList.LAST
777 INSERT INTO ZPB_SOLVE_ALLOCATION_DEFS
778 (ANALYSIS_CYCLE_ID,
779 MEMBER,
780 MEMBER_ORDER,
781 RULE_NAME,
782 METHOD,
783 EVALUATION_OPTION,
784 ROUND_DECIMALS,
785 ROUND_ENABLED,
786 BASIS,
787 QUALIFIER,
788 CREATED_BY,
789 CREATION_DATE,
790 LAST_UPDATED_BY,
791 LAST_UPDATE_DATE,
792 LAST_UPDATE_LOGIN)
793 SELECT ANALYSIS_CYCLE_ID, propagateList(i), propagateOrder(i), RULE_NAME,
794 METHOD, EVALUATION_OPTION, ROUND_DECIMALS, ROUND_ENABLED,
795 BASIS, QUALIFIER, fnd_global.USER_ID, SYSDATE,
796
797 fnd_global.USER_ID, SYSDATE, fnd_global.LOGIN_ID
798 FROM ZPB_SOLVE_ALLOCATION_DEFS
799 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
800 END propagateAlloc;
801
802
803 /****************************************************************
804 * PROPAGATE OUTPUT SELECTIONS *
805 * *
806 * *
807 *****************************************************************/
808
809 PROCEDURE propagateOutput(p_ac_id IN ZPB_SOLVE_MEMBER_DEFS.ANALYSIS_CYCLE_ID%TYPE,
810 p_from_member IN ZPB_SOLVE_MEMBER_DEFS.MEMBER%TYPE)
811
812 IS
813 opRec ZPB_SOLVE_OUTPUT_SELECTIONS%ROWTYPE;
814
815 CURSOR c1 IS
816 SELECT * FROM ZPB_SOLVE_OUTPUT_SELECTIONS
817 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
818
819 fromSource ZPB_SOLVE_MEMBER_DEFS.SOURCE_TYPE%TYPE;
820 BEGIN
821 SELECT SOURCE_TYPE INTO fromSource
822 FROM ZPB_SOLVE_MEMBER_DEFS
823 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
824
825 --For all cases update PROPAGATED_FLAG of source member.
826 UPDATE ZPB_SOLVE_OUTPUT_SELECTIONS
827 SET PROPAGATED_FLAG ='Y'
828 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
829
830 FOR i IN propagateList.FIRST..propagateList.LAST LOOP
831
832 --IF propagateList(i) = CALCULATED_SOURCE OR propagateList(i) = AGGREGATED_SOURCE THEN
833 IF propagateSourceType(i) = CALCULATED_SOURCE THEN
834 IF fromSource = CALCULATED_SOURCE THEN
835 DELETE ZPB_SOLVE_OUTPUT_SELECTIONS
836 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(i);
837 ELSE
838 DELETE ZPB_SOLVE_OUTPUT_SELECTIONS
839 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(i) AND
840 HIERARCHY <> 'NONE' ;
841 END IF ;
842 FOR opRec IN c1 LOOP
843 INSERT INTO ZPB_SOLVE_OUTPUT_SELECTIONS
844 (ANALYSIS_CYCLE_ID,
845 MEMBER,
846 MEMBER_ORDER,
847 DIMENSION,
848 HIERARCHY,
849 SELECTION_NAME,
850 SELECTION_PATH,
851 PROPAGATED_FLAG,
852 MATCH_INPUT_FLAG,
853 CREATED_BY,
854 CREATION_DATE,
855 LAST_UPDATED_BY,
856 LAST_UPDATE_DATE,
857 LAST_UPDATE_LOGIN)
858 VALUES(opRec.ANALYSIS_CYCLE_ID,
859 propagateList(i),
860 propagateOrder(i),
861 opRec.DIMENSION,
862 opRec.HIERARCHY,
863 opRec.SELECTION_NAME,
864 opRec.SELECTION_PATH,
865 'Y',
866 opRec.MATCH_INPUT_FLAG,
867 fnd_global.USER_ID,
868 SYSDATE,
869 fnd_global.USER_ID,
870 SYSDATE,
871 fnd_global.LOGIN_ID);
872 END LOOP;
873
874
875
876 -- ELSIF ( fromSource = CALCULATED_SOURCE OR fromSource = AGGREGATED_SOURCE ) AND
877 -- (propagateList(i) <> CALCULATED_SOURCE AND propagateList(i) <> AGGREGATED_SOURCE)THEN
878 ELSIF fromSource = CALCULATED_SOURCE AND propagateSourceType(i) <> CALCULATED_SOURCE THEN
879
880 FOR opRec IN c1 LOOP
881 DELETE ZPB_SOLVE_OUTPUT_SELECTIONS
882 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND
883 DIMENSION in (select dimension from ZPB_SOLVE_OUTPUT_SELECTIONS
884 where member = opRec.member
885 and dimension = opRec.dimension
886 and analysis_cycle_id = p_ac_id)
887 and member = propagateList(i);
888 END LOOP;
889 FOR opRec IN c1 LOOP
890 if opRec.hierarchy <> 'NONE' then
891 INSERT INTO ZPB_SOLVE_OUTPUT_SELECTIONS
892 (ANALYSIS_CYCLE_ID,
893 MEMBER,
894 MEMBER_ORDER,
895 DIMENSION,
896 HIERARCHY,
897 SELECTION_NAME,
898 SELECTION_PATH,
899 PROPAGATED_FLAG,
900 MATCH_INPUT_FLAG,
901 CREATED_BY,
902 CREATION_DATE,
903 LAST_UPDATED_BY,
904 LAST_UPDATE_DATE,
905 LAST_UPDATE_LOGIN)
906 VALUES(opRec.ANALYSIS_CYCLE_ID,
907 propagateList(i),
908 propagateOrder(i),
909 opRec.DIMENSION,
910 opRec.HIERARCHY,
911 opRec.SELECTION_NAME,
912 opRec.SELECTION_PATH,
913 'Y',
914 opRec.MATCH_INPUT_FLAG,
915 fnd_global.USER_ID,
916 SYSDATE,
917 fnd_global.USER_ID,
918 SYSDATE,
919 fnd_global.LOGIN_ID);
920 end if;
921 END LOOP;
922
923
924
925 ELSE
926 DELETE ZPB_SOLVE_OUTPUT_SELECTIONS
927 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND
928 MEMBER = propagateList(i) ;
929
930 FOR opRec IN c1 LOOP
931 INSERT INTO ZPB_SOLVE_OUTPUT_SELECTIONS
932 (ANALYSIS_CYCLE_ID,
933 MEMBER,
934 MEMBER_ORDER,
935 DIMENSION,
936 HIERARCHY,
937 SELECTION_NAME,
938 SELECTION_PATH,
939 PROPAGATED_FLAG,
940 MATCH_INPUT_FLAG,
941 CREATED_BY,
942 CREATION_DATE,
943 LAST_UPDATED_BY,
944 LAST_UPDATE_DATE,
945 LAST_UPDATE_LOGIN)
946 VALUES(opRec.ANALYSIS_CYCLE_ID,
947 propagateList(i),
948 propagateOrder(i),
949 opRec.DIMENSION,
950 opRec.HIERARCHY,
951 opRec.SELECTION_NAME,
952 opRec.SELECTION_PATH,
953 'Y',
954 opRec.MATCH_INPUT_FLAG,
955 fnd_global.USER_ID,
956 SYSDATE,
957 fnd_global.USER_ID,
958 SYSDATE,
959 fnd_global.LOGIN_ID);
960 END LOOP;
961
962 End if;
963
964
965 END LOOP;
966
967
968 EXCEPTION
969 WHEN OTHERS THEN
970 IF c1%ISOPEN THEN
971 CLOSE c1;
972 END IF;
973 ROLLBACK;
974 END propagateOutput;
975
976 /****************************************************************
977 * PROPAGATE DIMENSION INFO *
978 * *
979 * *
980
981 *****************************************************************/
982
983 PROCEDURE propagateDimhandling(p_ac_id IN ZPB_SOLVE_MEMBER_DEFS.ANALYSIS_CYCLE_ID%TYPE,
984 p_from_member IN ZPB_SOLVE_MEMBER_DEFS.MEMBER%TYPE)
985 IS
986 fromSource NUMBER;
987 dimRec ZPB_LINE_DIMENSIONALITY%ROWTYPE;
988 isSetSrcPropFlag boolean := FALSE;
989 CURSOR c1 IS
990 SELECT * FROM ZPB_LINE_DIMENSIONALITY
991 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
992 BEGIN
993 SELECT SOURCE_TYPE INTO fromSource
994
995 FROM ZPB_SOLVE_MEMBER_DEFS
996 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
997
998 IF fromSource = LOADED_SOURCE THEN
999 FOR i IN propagateList.FIRST..propagateList.LAST
1000 LOOP
1001 IF propagateSourceType(i) = LOADED_SOURCE THEN
1002 isSetSrcPropFlag := TRUE;
1003
1004 DELETE ZPB_LINE_DIMENSIONALITY
1005 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(i);
1006 --For CASE: B1-a
1007 FOR dimRec IN c1 LOOP
1008
1009 INSERT INTO ZPB_LINE_DIMENSIONALITY
1010 (ANALYSIS_CYCLE_ID,
1011 MEMBER,
1012 MEMBER_ORDER,
1013 DIMENSION,
1014 SUM_MEMBERS_NUMBER,
1015 SUM_MEMBERS_FLAG ,
1016 EXCLUDE_FROM_SOLVE_FLAG,
1017 FORCE_INPUT_FLAG,
1018 SUM_SELECTION_NAME,
1019 SUM_SELECTION_PATH,
1020 PROPAGATED_FLAG,
1021 CREATED_BY,
1022 CREATION_DATE,
1023 LAST_UPDATED_BY,
1024 LAST_UPDATE_DATE,
1025 LAST_UPDATE_LOGIN)
1026 VALUES (dimRec.ANALYSIS_CYCLE_ID,
1027 propagateList(i),
1028 propagateOrder(i),
1029 dimRec.DIMENSION,
1030 dimRec.SUM_MEMBERS_NUMBER,
1031 dimRec.SUM_MEMBERS_FLAG ,
1032 dimRec.EXCLUDE_FROM_SOLVE_FLAG,
1033 dimRec.FORCE_INPUT_FLAG,
1034 dimRec.SUM_SELECTION_NAME,
1035 dimRec.SUM_SELECTION_PATH,
1036 'Y',
1037 fnd_global.USER_ID,
1038 SYSDATE,
1039 fnd_global.USER_ID,
1040 SYSDATE,
1041 fnd_global.LOGIN_ID);
1042 END LOOP;
1043 END IF;
1044 END LOOP;
1045 END IF;
1046 --Update PROPAGATED_FLAG of source member.
1047 IF isSetSrcPropFlag = TRUE THEN
1048
1049 UPDATE ZPB_LINE_DIMENSIONALITY
1050 SET PROPAGATED_FLAG ='Y'
1051 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
1052 END IF;
1053
1054 EXCEPTION
1055 WHEN OTHERS THEN
1056 IF c1%ISOPEN THEN
1057 CLOSE c1;
1058 END IF;
1059 ROLLBACK;
1060 END propagateDimhandling;
1061
1062
1063
1064 /****************************************************************
1065 * PROPAGATE SETTINGS *
1066 * *
1067 * *
1068 * *
1069 ****************************************************************/
1070
1071 PROCEDURE propagateSolve (
1072 p_ac_id IN ZPB_SOLVE_MEMBER_DEFS.ANALYSIS_CYCLE_ID%TYPE,
1073 p_from_member IN ZPB_SOLVE_MEMBER_DEFS.MEMBER%TYPE,
1074 p_view_dim_name IN VARCHAR2,
1075 p_view_member_column IN VARCHAR2,
1076 p_view_short_lbl_column IN VARCHAR2,
1077 p_prop_calc IN INTEGER, -- source flag
1078 p_prop_alloc IN INTEGER,
1079 p_prop_input IN INTEGER,
1080 p_prop_output IN INTEGER, -- o/p flag
1081 p_prop_dimhandling IN INTEGER)
1082 IS
1083 tableselect varchar(5000);
1084 fromSource NUMBER;
1085
1086 c4 ref_cursor;
1087 BEGIN
1088 --get all the members that have the propagate flag set
1089
1090 tableselect := 'Select member, member_order, memberlookup.';
1091 tableselect := tableselect || p_view_short_lbl_column || ' as MemberName,';
1092 tableselect := tableselect || ' SOURCE_TYPE';
1093 tableselect := tableselect || ' FROM ZPB_SOLVE_MEMBER_DEFS defs, ';
1094 tableselect := tableselect || p_view_dim_name || ' memberlookup';
1095 tableselect := tableselect || ' WHERE defs.ANALYSIS_CYCLE_ID = ' || p_ac_id;
1096 tableselect := tableselect || ' AND defs.PROPAGATE_TARGET = ' || iTrueValue;
1097 tableselect := tableselect || ' AND defs.member = memberlookup.' || p_view_member_column;
1098
1099 OPEN c4 FOR tableSelect;
1100 Fetch c4 BULK COLLECT into propagateList, propagateOrder, propagateName, propagateSourceType;
1101
1102 SELECT SOURCE_TYPE INTO fromSource
1103
1104 FROM ZPB_SOLVE_MEMBER_DEFS
1105 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = p_from_member;
1106
1107 if p_prop_calc = iTrueValue THEN
1108 propagateCalc(p_ac_id, p_from_member,p_prop_dimhandling,p_prop_input,p_prop_output);
1109 END IF;
1110 if p_prop_alloc = iTrueValue THEN
1111 propagateAlloc(p_ac_id, p_from_member);
1112 END IF;
1113 if p_prop_input = iTrueValue and p_prop_calc = iFalseValue THEN
1114 propagateInput(p_ac_id, p_from_member);
1115 END IF;
1116 if p_prop_output = iTrueValue and p_prop_calc = iFalseValue THEN
1117 propagateOutput(p_ac_id, p_from_member);
1118 END IF;
1119 if p_prop_dimhandling = iTrueValue and p_prop_calc = iFalseValue THEN
1120 propagateDimhandling(p_ac_id, p_from_member);
1121 END IF;
1122 --reset propagate flag
1123 UPDATE ZPB_SOLVE_MEMBER_DEFS
1124 SET PROPAGATE_TARGET = iFalseValue,
1125 LAST_UPDATED_BY = fnd_global.USER_ID,
1126 LAST_UPDATE_DATE = SYSDATE,
1127 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1128 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND PROPAGATE_TARGET = iTrueValue;
1129 END propagateSolve;
1130
1131
1132
1133 FUNCTION getDimSettingMeaning(p_lookup_code IN FND_LOOKUP_VALUES_VL.LOOKUP_CODE%TYPE)
1134 RETURN VARCHAR2 IS
1135 ret_meaning FND_LOOKUP_VALUES_VL.MEANING%TYPE;
1136 BEGIN
1137 SELECT MEANING||',' INTO ret_meaning
1138 FROM FND_LOOKUP_VALUES_VL
1139 WHERE LOOKUP_TYPE = 'ZPB_SOLVE_DIMENSIONLIST_SELECT' AND LOOKUP_CODE = p_lookup_code;
1140 RETURN ret_meaning;
1141 END getDimSettingMeaning;
1142
1143 PROCEDURE updateCleanup(p_ac_id IN ZPB_SOLVE_MEMBER_DEFS.ANALYSIS_CYCLE_ID%TYPE,
1144 p_line_member IN ZPB_SOLVE_MEMBER_DEFS.MEMBER%TYPE,
1145 p_src_type IN ZPB_SOLVE_MEMBER_DEFS.SOURCE_TYPE%TYPE)
1146 IS
1147
1148 BEGIN
1149 IF p_src_type <> LOADED_SOURCE THEN
1150 delete from zpb_line_dimensionality where analysis_cycle_id = p_ac_id and member = p_line_member;
1151 END IF;
1152 IF p_src_type = CALCULATED_SOURCE OR p_src_type = AGGREGATED_SOURCE THEN
1153 delete from zpb_solve_input_selections where analysis_cycle_id = p_ac_id and member = p_line_member;
1154 update ZPB_SOLVE_OUTPUT_SELECTIONS
1155 set MATCH_INPUT_FLAG = 'N'
1156 where analysis_cycle_id = p_ac_id and member = p_line_member;
1157 END IF;
1158 IF p_src_type <> CALCULATED_SOURCE THEN
1159 delete from zpb_solve_output_selections where analysis_cycle_id = p_ac_id and member = p_line_member
1160 AND hierarchy = 'NONE';
1161 END IF;
1162 IF p_src_type <> INIT_WS_INPUT_SOURCE THEN
1163 -- for bug 5001437
1164 delete from zpb_status_sql
1165 where query_path in (select query_path||'/'||target_query_name
1166 from zpb_data_initialization_defs
1167 where analysis_cycle_id = p_ac_id
1168 and member = p_line_member);
1169
1170 delete from zpb_status_sql
1171 where query_path in (select query_path||'/'||source_query_name
1172 from zpb_data_initialization_defs
1173 where analysis_cycle_id = p_ac_id
1174 and member = p_line_member);
1175
1176 delete from ZPB_DATA_INITIALIZATION_DEFS where analysis_cycle_id = p_ac_id and member = p_line_member;
1177 delete from ZPB_COPY_DIM_MEMBERS where analysis_cycle_id = p_ac_id and line_member_id = p_line_member;
1178 END IF;
1179 IF p_src_type <> CALCULATED_SOURCE then
1180 removeCalcObjectInfo(p_ac_id,p_line_member);
1181 END IF;
1182 END updateCleanup;
1183
1184
1185 PROCEDURE deleteOutputSelections(p_ac_id IN ZPB_SOLVE_MEMBER_DEFS.ANALYSIS_CYCLE_ID%TYPE,
1186 p_targetIndex IN INTEGER )
1187 IS
1188 BEGIN
1189 DELETE ZPB_SOLVE_OUTPUT_SELECTIONS
1190 WHERE ANALYSIS_CYCLE_ID = p_ac_id AND MEMBER = propagateList(p_targetIndex)
1191 AND HIERARCHY = 'NONE';
1192
1193 END deleteOutputSelections;
1194
1195 PROCEDURE insertDefaultOutput(p_ac_id IN ZPB_SOLVE_OUTPUT_SELECTIONS.ANALYSIS_CYCLE_ID%TYPE,
1196 p_line_member IN ZPB_SOLVE_OUTPUT_SELECTIONS.MEMBER%TYPE,
1197 p_memberOrder IN ZPB_SOLVE_OUTPUT_SELECTIONS.MEMBER_ORDER%TYPE,
1198 p_dimension IN ZPB_SOLVE_OUTPUT_SELECTIONS.DIMENSION%TYPE)
1199 IS
1200 BEGIN
1201 INSERT INTO ZPB_SOLVE_OUTPUT_SELECTIONS
1202 (ANALYSIS_CYCLE_ID,
1203 MEMBER,
1204 MEMBER_ORDER,
1205 DIMENSION,
1206 HIERARCHY,
1207 SELECTION_NAME,
1208 MATCH_INPUT_FLAG,
1209 PROPAGATED_FLAG,
1210 CREATED_BY,
1211 CREATION_DATE,
1212 LAST_UPDATED_BY,
1213 LAST_UPDATE_DATE,
1214 LAST_UPDATE_LOGIN)
1215 VALUES (p_ac_id,
1216 p_line_member,
1217 p_memberOrder,
1218 p_dimension,
1219 'NONE',
1220 'DEFAULT',
1221 'N',
1222 'N',
1223 fnd_global.USER_ID,
1224 SYSDATE,
1225 fnd_global.USER_ID,
1226 SYSDATE,
1227 fnd_global.LOGIN_ID);
1228
1229
1230 END insertDefaultOutput;
1231
1232 --
1233 -- Looks for removed hierarchies in zpb_solve_in/output_selections
1234 -- and removes them, and defaults to other hiers if necessary (4705541)
1235 --
1236 PROCEDURE initialize_solve_selections
1237 (p_ac_id IN zpb_analysis_cycles.analysis_cycle_id%TYPE) is
1238 l_count number;
1239 l_ba_id number;
1240 l_shadow number;
1241 l_new_hier ZPB_SOLVE_INPUT_SELECTIONS.HIERARCHY%type;
1242 l_dimension ZPB_SOLVE_INPUT_SELECTIONS.DIMENSION%type;
1243
1244 cursor input_hiers is
1245 select distinct hierarchy, dimension
1246 from zpb_solve_input_selections
1247 where analysis_cycle_id = p_ac_id
1248 and hierarchy <> 'NONE'
1249 MINUS
1250 select distinct a.epb_id, b.aw_name
1251 from zpb_hierarchies a,
1252 zpb_dimensions b,
1253 zpb_hier_scope c
1254 where a.dimension_id = b.dimension_id
1255 and b.bus_area_id = l_ba_id
1256 and b.is_data_dim = 'YES'
1257 and a.hierarchy_id = c.hierarchy_id
1258 and c.user_id = l_shadow;
1259
1260 cursor output_hiers is
1261 select distinct hierarchy, dimension
1262 from zpb_solve_output_selections
1263 where analysis_cycle_id = p_ac_id
1264 and hierarchy <> 'NONE'
1265 MINUS
1266 select distinct a.EPB_ID, b.aw_name
1267 from zpb_hierarchies a,
1268 zpb_dimensions b,
1269 zpb_hier_scope c
1270 where a.dimension_id = b.dimension_id
1271 and b.bus_area_id = l_ba_id
1272 and b.is_data_dim = 'YES'
1273 and a.hierarchy_id = c.hierarchy_id
1274 and c.user_id = l_shadow;
1275 begin
1276 l_ba_id := sys_context('ZPB_CONTEXT', 'business_area_id');
1277 l_shadow := sys_context('ZPB_CONTEXT', 'shadow_id');
1278 for each in input_hiers loop
1279 --
1280 -- First check how many hiers are left, if none, we need to reset to NONE
1281 --
1282 select count(*)
1283 into l_count
1284 from zpb_dimensions a,
1285 zpb_hierarchies b,
1286 zpb_hier_scope c
1287 where a.aw_name = each.dimension
1288 and a.bus_area_id = l_ba_id
1289 and a.dimension_id = b.dimension_id
1290 and b.epb_id <> 'NULL_GID'
1291 and b.hierarchy_id = c.hierarchy_id
1292 and c.user_id = l_shadow;
1293
1294 if (l_count > 0) then
1295 --
1296 -- First delete any selections where there is another selection
1297 -- on another hierarchy already
1298 --
1299 delete from zpb_solve_input_selections
1300 where analysis_cycle_id = p_ac_id
1301 and dimension = each.dimension
1302 and hierarchy = each.hierarchy
1303 and member in
1304 (select member
1305 from zpb_solve_input_selections
1306 where analysis_cycle_id = p_ac_id
1307 and dimension = each.dimension
1308 and hierarchy <> each.hierarchy);
1309
1310 --
1311 -- Now update the rest to be the default on another hierarchy
1312 -- First find what that other hierarchy will be
1313 --
1314 begin
1315 select a.DEFAULT_HIER
1316 into l_new_hier
1317 from zpb_dimensions a,
1318 zpb_hierarchies b,
1319 zpb_hier_scope c
1320 where a.aw_name = each.dimension
1321 and a.bus_area_id = l_ba_id
1322 and a.dimension_id = b.dimension_id
1323 and b.epb_id = a.default_hier
1324 and b.hierarchy_id = c.hierarchy_id
1325 and c.user_id = l_shadow;
1326 exception
1327 when no_data_found then
1328 --
1329 -- User doesnt have access to the default hier, so pick the
1330 -- first one
1331 --
1332 select min(b.epb_id)
1333 into l_new_hier
1334 from zpb_dimensions a,
1335 zpb_hierarchies b,
1336 zpb_hier_scope c
1337 where a.aw_name = each.dimension
1338 and a.bus_area_id = l_ba_id
1339 and a.dimension_id = b.dimension_id
1340 and b.hierarchy_id = c.hierarchy_id
1341 and c.user_id = l_shadow;
1342 end;
1343 update zpb_solve_input_selections
1344 set selection_name = 'DEFAULT',
1345 selection_path = null,
1346 hierarchy = l_new_hier,
1347 last_update_date = sysdate,
1348 last_updated_by = FND_GLOBAL.USER_ID,
1349 last_update_login = FND_GLOBAL.LOGIN_ID
1350 where analysis_cycle_id = p_ac_id
1351 and dimension = each.dimension
1352 and hierarchy = each.hierarchy;
1353 else
1354 --
1355 -- The case when there are no hierarchies left on the dimension.
1356 -- First delete any input selections where we have already set the
1357 -- HIERARCHY to NONE... case when mutliple hiers have been removed
1358 --
1359 delete from zpb_solve_input_selections
1360 where analysis_cycle_id = p_ac_id
1361 and dimension = each.dimension
1362 and hierarchy = each.hierarchy
1363 and member in
1364 (select member
1365 from zpb_solve_input_selections
1366 where analysis_cycle_id = p_ac_id
1367 and dimension = each.dimension
1368 and hierarchy = 'NONE');
1369
1370 --
1371 -- Now update the rest to be set to NONE
1372 --
1373 update zpb_solve_input_selections
1374 set hierarchy = 'NONE',
1375 selection_path = null,
1376 selection_name = 'DEFAULT',
1377 last_update_date = sysdate,
1378 last_updated_by = FND_GLOBAL.USER_ID,
1379 last_update_login = FND_GLOBAL.LOGIN_ID
1380 where analysis_cycle_id = p_ac_id
1381 and dimension = each.dimension
1382 and hierarchy = each.hierarchy;
1383 end if;
1384 end loop;
1385
1386 for each in output_hiers loop
1387 --
1388 -- First check how many hiers are left, if none, we need to reset to NONE
1389 --
1390 select count(*)
1391 into l_count
1392 from zpb_dimensions a,
1393 zpb_hierarchies b,
1394 zpb_hier_scope c
1395 where a.aw_name = each.dimension
1396 and a.bus_area_id = l_ba_id
1397 and a.dimension_id = b.dimension_id
1398 and b.epb_id <> 'NULL_GID'
1399 and b.hierarchy_id = c.hierarchy_id
1400 and c.user_id = l_shadow;
1401
1402 if (l_count > 0) then
1403 --
1404 -- First delete any selections where there is another selection
1405 -- on another hierarchy already
1406 --
1407 delete from zpb_solve_output_selections
1408 where analysis_cycle_id = p_ac_id
1409 and dimension = each.dimension
1410 and hierarchy = each.hierarchy
1411 and member in
1412 (select member
1413 from zpb_solve_output_selections
1414 where analysis_cycle_id = p_ac_id
1415 and dimension = each.dimension
1416 and hierarchy <> each.hierarchy);
1417
1418 --
1419 -- Now update the rest to be the default on another hierarchy
1420 -- First find what that other hierarchy will be
1421 --
1422 begin
1423 select a.DEFAULT_HIER
1424 into l_new_hier
1425 from zpb_dimensions a,
1426 zpb_hierarchies b,
1427 zpb_hier_scope c
1428 where a.aw_name = each.dimension
1429 and a.bus_area_id = l_ba_id
1430 and a.dimension_id = b.dimension_id
1431 and b.epb_id = a.default_hier
1432 and b.hierarchy_id = c.hierarchy_id
1433 and c.user_id = l_shadow;
1434 exception
1435 when no_data_found then
1436 --
1437 -- User doesnt have access to the default hier, so pick the
1438 -- first one
1439 --
1440 select min(b.epb_id)
1441 into l_new_hier
1442 from zpb_dimensions a,
1443 zpb_hierarchies b,
1444 zpb_hier_scope c
1445 where a.aw_name = each.dimension
1446 and a.bus_area_id = l_ba_id
1447 and a.dimension_id = b.dimension_id
1448 and b.hierarchy_id = c.hierarchy_id
1449 and c.user_id = l_shadow;
1450 end;
1451 update zpb_solve_output_selections
1452 set selection_name = 'DEFAULT',
1453 selection_path = null,
1454 hierarchy = l_new_hier,
1455 last_update_date = sysdate,
1456 last_updated_by = FND_GLOBAL.USER_ID,
1457 last_update_login = FND_GLOBAL.LOGIN_ID
1458 where analysis_cycle_id = p_ac_id
1459 and dimension = each.dimension
1460 and hierarchy = each.hierarchy;
1461 else
1462 --
1463 -- The case when there are no hierarchies left on the dimension.
1464 -- First delete any output selections where we have already set the
1465 -- HIERARCHY to NONE... case when mutliple hiers have been removed
1466 --
1467 delete from zpb_solve_output_selections
1468 where analysis_cycle_id = p_ac_id
1469 and dimension = each.dimension
1470 and hierarchy = each.hierarchy
1471 and member in
1472 (select member
1473 from zpb_solve_output_selections
1474 where analysis_cycle_id = p_ac_id
1475 and dimension = each.dimension
1476 and hierarchy = 'NONE');
1477
1478 --
1479 -- Now update the rest to be set to NONE
1480 --
1481 update zpb_solve_output_selections
1482 set hierarchy = 'NONE',
1483 selection_path = null,
1484 selection_name = 'DEFAULT',
1485 last_update_date = sysdate,
1486 last_updated_by = FND_GLOBAL.USER_ID,
1487 last_update_login = FND_GLOBAL.LOGIN_ID
1488 where analysis_cycle_id = p_ac_id
1489 and dimension = each.dimension
1490 and hierarchy = each.hierarchy;
1491 end if;
1492 end loop;
1493
1494 end initialize_solve_selections;
1495
1496 /*=========================================================================+
1497 | PROCEDURE RUN_SOLVE
1498 |
1499 | DESCRIPTION
1500 | Runs Solve in shared
1501 |
1502 |
1503 |
1504 +=========================================================================*/
1505 procedure RUN_SOLVE (errbuf out nocopy varchar2,
1506 retcode out nocopy varchar2,
1507 p_business_area_id in number,
1508 p_instance_id in number)
1509 IS
1510
1511 attached varchar2(1) := 'N';
1512 l_dbname varchar2(150);
1513 l_count number;
1514 l_userid number := fnd_global.USER_ID;
1515 l_taskid number;
1516
1517
1518 BEGIN
1519 retcode := '0';
1520
1521 --Log
1522 FND_FILE.put_line(FND_FILE.LOG,'p_business_area_id=' ||p_business_area_id );
1523 FND_FILE.put_line(FND_FILE.LOG,'p_instance_id=' ||p_instance_id);
1524
1525 -- Get the task id to pass on to the solve program
1526 SELECT task_id INTO l_taskid
1527 FROM zpb_analysis_cycle_tasks
1528 WHERE sequence =
1529 ( SELECT max(sequence)
1530 FROM zpb_analysis_cycle_tasks
1531 WHERE analysis_cycle_id = p_instance_id
1532 AND status_code IN ('COMPLETE')
1533 ) + 1
1534 AND analysis_cycle_id = p_instance_id;
1535
1536 -- Log
1537 FND_FILE.put_line(FND_FILE.LOG,'l_taskid=' ||l_taskid);
1538
1539
1540 -- Test run of solve
1541 ZPB_AW.INITIALIZE_FOR_AC (p_api_version => 1.0,
1542 p_init_msg_list => FND_API.G_TRUE,
1543 x_return_status => retcode,
1544 x_msg_count => l_count,
1545 x_msg_data => errbuf,
1546 p_analysis_cycle_id => p_instance_id,
1547 p_shared_rw => FND_API.G_TRUE);
1548 attached := 'Y';
1549
1550 l_dbname := ZPB_AW.GET_SCHEMA || '.' || ZPB_AW.GET_SHARED_AW;
1551 ZPB_AW.EXECUTE('APPS_USER_ID = ''' || TO_CHAR(l_userid) || '''');
1552
1553 -- intializes the pv_status variable
1554 ZPB_ERROR_HANDLER.INIT_CONC_REQ_STATUS;
1555 ZPB_AW.EXECUTE('call SV.RUN.SOLVE(''' || l_dbname || ''', ''' || TO_CHAR(p_instance_id) || ''', ''' || TO_CHAR(l_taskid) || ''')');
1556 -- retcode is an OUT parameter conc program standard - 0=success, 1=warning or 2=error.
1557 retcode := ZPB_ERROR_HANDLER.GET_CONC_REQ_STATUS;
1558
1559 -- update
1560 --ZPB_AW.EXECUTE('upd');
1561 ZPB_AW.EXECUTE('pa.commit');
1562
1563 commit;
1564
1565 ZPB_AW.DETACH_ALL;
1566 attached := 'N';
1567
1568 --log solve OK
1569 FND_FILE.put_line(FND_FILE.LOG,'Solve ok');
1570 return;
1571
1572 exception
1573 when no_data_found then
1574 -- There are no active tasks
1575 retcode :='2';
1576
1577 if attached = 'Y' then
1578 ZPB_AW.DETACH_ALL;
1579 end if;
1580
1581 FND_FILE.put_line(FND_FILE.LOG, 'Solve not ok');
1582 errbuf := 'No task found';
1583
1584
1585 when others then
1586 retcode :='2';
1587
1588 if attached = 'Y' then
1589 ZPB_AW.DETACH_ALL;
1590 end if;
1591
1592 --log solve not OK
1593 FND_FILE.put_line(FND_FILE.LOG, 'Solve not ok');
1594 errbuf:=substr(sqlerrm, 1, 255);
1595
1596 end run_solve;
1597
1598 END zpb_solve;
[Home] [Help]