[Home] [Help]
PACKAGE BODY: APPS.ZPB_AC_OPS
Source
1 PACKAGE BODY ZPB_AC_OPS AS
2 /* $Header: zpbac.plb 120.30 2008/01/25 06:49:55 maniskum ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(15) := 'zpb_ac_ops';
5
6 /*
7 * Internal procedures
8 */
9
10 -- ABUDNIK B4558985 09Oct2005
11 --ABUDNIK B5046249 17Apr2006
12 procedure CLEAN_ACTIVE_INSTANCE(p_acid in number)
13 is
14
15 l_wfprocess varchar2(30);
16 l_instanceID number;
17 l_item_key varchar2(240);
18 l_itemtype varchar2(8);
19 l_ownerID number;
20 l_reqID number;
21 l_count number;
22 currStatus varchar2(20);
23 result varchar2(100);
24 l_business_area_id number;
25
26 --Cursor modified for missing objects
27 CURSOR c_active_instance IS
28 SELECT zaci.instance_ac_id, zac.current_instance_id, t.wf_process_name, t.item_key, t.task_id
29 FROM ZPB_ANALYSIS_CYCLE_INSTANCES zaci,
30 ZPB_ANALYSIS_CYCLES zac,
31 zpb_analysis_cycle_tasks t,
32 ZPB_ANALYSIS_CYCLES publishedac
33 WHERE publishedac.analysis_cycle_id = p_acid and
34 publishedac.current_instance_id=zac.current_instance_id and
35 zaci.instance_ac_id = zac.analysis_cycle_id and
36 zac.status_code NOT in('COMPLETE', 'COMPLETE_WITH_WARNING', 'ERROR')
37 AND zaci.instance_ac_id = t.ANALYSIS_CYCLE_ID
38 and t.staTus_code = 'ACTIVE';
39
40 v_active_instance c_active_instance%ROWTYPE;
41
42
43 zpb_reqID_err EXCEPTION;
44
45 begin
46
47
48 -- abudnik 07DEC2005 BUSINESS AREA ID to call for ZPB_WF_DELAWINST
49 select BUSINESS_AREA_ID
50 into l_business_area_id
51 from ZPB_ANALYSIS_CYCLES
52 where ANALYSIS_CYCLE_ID = p_acid;
53
54
55 for v_active_instance in c_active_instance loop
56
57 l_wfprocess :=v_active_instance.wf_process_name;
58 l_instanceID := v_active_instance.instance_ac_id;
59 l_item_key := v_active_instance.item_key;
60
61 if l_wfprocess in ('EXCEPTION', 'GENERATE_TEMPLATE', 'MANAGE_SUBMISSION', 'REVIEW_FWK', 'WAIT_TASK') then
62
63 select count(*) into l_count
64 from wf_items_v
65 where item_key = l_item_key;
66
67 if l_count = 1 then
68 -- get this owner
69 l_ownerID := wf_engine.GetItemAttrNumber(Itemtype => 'EPBCYCLE',
70 Itemkey => l_Item_Key,
71 aname => 'OWNERID');
72
73 -- bug 5046249: a tempoary suppression of the error becuase this is just aborting and not purging
74 -- if this rasies an error here this WF is corrupt and not running anyway.
75 -- clean_active_instance should be redesigned to be a conc request so we can report these.
76 -- Examples pf types of errors suppressed for this:
77 -- ORA-20002: 3116: Actvity 'MANAGE_SUBMISSION' for item 'EPBCYCLE/Test run pqr' is not a runnable process.
78 -- ORA-20002: 3106: Root process 'MANAGE_SUBMISSION' for item 'EPBCYCLE/Test run xyz' does not exist.
79 -- ORA-20002: 3124: Process 'MANAGE_SUBMISSION' for item 'EPBCYCLE/Test run abc' is not active.
80
81 BEGIN
82
83 -- abort this WF if it is active or in error
84 wf_engine.ItemStatus('EPBCYCLE', l_item_key, currStatus, result);
85 if UPPER(RTRIM(currStatus)) = 'ERROR' or UPPER(RTRIM(currStatus)) = 'ACTIVE' then
86 WF_ENGINE.AbortProcess('EPBCYCLE', l_item_key);
87 end if;
88
89 exception
90 WHEN OTHERS THEN
91 if instr(sqlerrm, 'ORA-20002') > 0 then
92 Null;
93 else
94 raise;
95 end if;
96 end;
97
98
99 -- submit the ZPB AW DELETE
100 -- abudnik 07DEC2005 BUSINESS AREA ID to call for ZPB_WF_DELAWINST
101 l_REQID := FND_REQUEST.SUBMIT_REQUEST ('ZPB', 'ZPB_WF_DELAWINST', NULL, NULL, FALSE, l_instanceID, l_ownerid, l_business_area_id);
102
103 -- now that the instance has been cleaned, set its last task to completed
104 -- in order to allow cleaning of the current instance of the BP
105 update zpb_analysis_cycle_tasks
106 set status_code='COMPLETE'
107 where task_id = v_active_instance.task_id;
108
109 if l_REQID <= 0 then
110 RAISE zpb_reqID_err;
111 end if;
112 -- dbms_output.put_line(' l_reqid= ' || l_reqId);
113
114 end if;
115 end if;
116
117 end loop;
118 return;
119
120
121 exception
122
123 WHEN zpb_reqID_err THEN
124 zpb_log.write_event(G_PKG_NAME||'.clean_active_instance', ' - l_REQID= ' || l_REQID ||': l_instanceID= '|| l_instanceID ||':'|| substr(sqlerrm,1,90));
125 raise;
126
127 wHEN others then
128 raise;
129
130 end CLEAN_ACTIVE_INSTANCE;
131
132
133
134 PROCEDURE copy_ac_table_rec (
135 source_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
136 target_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE)
137 IS
138 BEGIN
139
140 --if the calling procedure is publish_cycle, then
141 --transfer the ownership to the Current User
142 INSERT INTO zpb_analysis_cycles
143 (ANALYSIS_CYCLE_ID,
144 STATUS_CODE,
145 NAME,
146 DESCRIPTION,
147 LOCKED_BY,
148 VALIDATE_STATUS,
149 CURRENT_INSTANCE_ID,
150 PUBLISHED_DATE,
151 PUBLISHED_BY,
152 PREV_STATUS_CODE,
153 OWNER_ID,
154 BUSINESS_AREA_ID,
155 CREATED_BY,
156 CREATION_DATE,
157 LAST_UPDATED_BY,
158 LAST_UPDATE_DATE,
159 LAST_UPDATE_LOGIN)
160 SELECT target_ac_id_in,
161 STATUS_CODE,
162 NAME,
163 DESCRIPTION,
164 LOCKED_BY,
165 VALIDATE_STATUS,
166 CURRENT_INSTANCE_ID,
167 PUBLISHED_DATE,
168 PUBLISHED_BY,
169 PREV_STATUS_CODE,
170 OWNER_ID,
171 BUSINESS_AREA_ID,
172 fnd_global.USER_ID,
173 SYSDATE,
174 fnd_global.USER_ID,
175 SYSDATE,
176 fnd_global.LOGIN_ID
177 FROM zpb_analysis_cycles
178 WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
179
180 END copy_ac_table_rec;
181
182
183 PROCEDURE copy_ac_param_values_recs (
184 source_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
185 target_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE)
186 IS
187 BEGIN
188 INSERT INTO zpb_ac_param_values
189 (ANALYSIS_CYCLE_ID,
190 PARAM_ID,
191 VALUE,
192 CREATED_BY,
193 CREATION_DATE,
194 LAST_UPDATED_BY,
195 LAST_UPDATE_DATE,
196 LAST_UPDATE_LOGIN)
197 SELECT target_ac_id_in,
198 PARAM_ID,
199 VALUE,
200 fnd_global.USER_ID,
201 SYSDATE,
202 fnd_global.USER_ID,
203 SYSDATE,
204 fnd_global.LOGIN_ID
205 FROM zpb_ac_param_values
206 WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
207 END copy_ac_param_values_recs;
208
209 PROCEDURE copy_cycle_currency_recs (
210 source_ac_id_in IN zpb_cycle_currencies.analysis_cycle_id%TYPE,
211 target_ac_id_in IN zpb_cycle_currencies.analysis_cycle_id%TYPE)
212 IS
213 BEGIN
214 INSERT INTO zpb_cycle_currencies
215 (ANALYSIS_CYCLE_ID,
216 CURRENCY_CODE,
217 CREATED_BY,
218 CREATION_DATE,
219 LAST_UPDATED_BY,
220 LAST_UPDATE_DATE,
221 LAST_UPDATE_LOGIN)
222 SELECT target_ac_id_in,
223 CURRENCY_CODE,
224 fnd_global.USER_ID,
225 SYSDATE,
226 fnd_global.USER_ID,
227 SYSDATE,
228 fnd_global.LOGIN_ID
229 FROM zpb_cycle_currencies
230 WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
231
232 END copy_cycle_currency_recs;
233
234 PROCEDURE copy_external_user_recs (
235 source_ac_id_in IN zpb_bp_external_users.analysis_cycle_id%TYPE,
236 target_ac_id_in IN zpb_bp_external_users.analysis_cycle_id%TYPE)
237 IS
238 BEGIN
239 INSERT INTO zpb_bp_external_users
240 (ANALYSIS_CYCLE_ID,
241 USER_ID,
242 CREATED_BY,
243 CREATION_DATE,
244 LAST_UPDATED_BY,
245 LAST_UPDATE_DATE,
246 LAST_UPDATE_LOGIN)
247 SELECT target_ac_id_in,
248 USER_ID,
249 fnd_global.USER_ID,
250 SYSDATE,
251 fnd_global.USER_ID,
252 SYSDATE,
253 fnd_global.LOGIN_ID
254 FROM zpb_bp_external_users
255 WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
256
257 END copy_external_user_recs;
258
259 PROCEDURE copy_cycle_datasets_recs (
260 source_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
261 target_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE)
262 IS
263 BEGIN
264 INSERT INTO zpb_cycle_datasets
265 (ANALYSIS_CYCLE_ID,
266 DATASET_CODE,
267 ORDER_ID,
268 CREATED_BY,
269 CREATION_DATE,
270 LAST_UPDATED_BY,
271 LAST_UPDATE_DATE,
272 LAST_UPDATE_LOGIN)
273 SELECT target_ac_id_in,
274 DATASET_CODE,
275 ORDER_ID,
276 fnd_global.USER_ID,
277 SYSDATE,
278 fnd_global.USER_ID,
279 SYSDATE,
280 fnd_global.LOGIN_ID
281 FROM zpb_cycle_datasets
282 WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
283
284 END copy_cycle_datasets_recs;
285
286
287
288 PROCEDURE copy_cycle_model_dim_recs (
289 source_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
290 target_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE)
291 IS
292 BEGIN
293 INSERT INTO zpb_cycle_model_dimensions
294 (ANALYSIS_CYCLE_ID,
295 DIMENSION_NAME,
296 QUERY_OBJECT_NAME,
297 QUERY_OBJECT_PATH,
298 DATASET_DIMENSION_FLAG,
299 REMOVE_DIMENSION_FLAG,
300 SUM_MEMBERS_NUMBER,
301 SUM_SELECTION_NAME,
302 SUM_SELECTION_PATH,
303 LAST_UPDATE_LOGIN,
304 LAST_UPDATE_DATE,
305 LAST_UPDATED_BY,
306 CREATION_DATE,
307 CREATED_BY)
308 SELECT target_ac_id_in,
309 DIMENSION_NAME,
310 QUERY_OBJECT_NAME,
311 QUERY_OBJECT_PATH,
312 DATASET_DIMENSION_FLAG,
313 REMOVE_DIMENSION_FLAG,
314 SUM_MEMBERS_NUMBER,
315 SUM_SELECTION_NAME,
316 SUM_SELECTION_PATH,
317 fnd_global.LOGIN_ID,
318 SYSDATE,
319 fnd_global.USER_ID,
320 SYSDATE,
321 fnd_global.USER_ID
322 FROM zpb_cycle_model_dimensions
323 WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
324
325 END copy_cycle_model_dim_recs;
326
327 -- Bug 4587184: Add source_task_id and target_task_id to the signature
328 PROCEDURE copy_bp_measure_scope_recs (
329 source_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE ,
330 source_task_id IN zpb_measure_scope_exempt_users.task_id%TYPE,
331 target_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE ,
332 target_task_id IN zpb_measure_scope_exempt_users.task_id%TYPE)
333 IS
334 BEGIN
335
336 -- Bug 4587184: Modified the query to consider the task id
337 INSERT INTO zpb_measure_scope_exempt_users
338 (BUSINESS_PROCESS_ENTITY_ID,
339 USER_ID,
340 EXEMPTION_ID,
341 TASK_ID,
342 BUSINESS_PROCESS_ENTITY_TYPE,
343 CREATED_BY,
344 CREATION_DATE,
345 LAST_UPDATED_BY,
346 LAST_UPDATE_DATE,
347 LAST_UPDATE_LOGIN)
348 SELECT target_ac_id_in,
349 USER_ID,
350 EXEMPTION_ID,
351 target_task_id,
352 BUSINESS_PROCESS_ENTITY_TYPE,
353 fnd_global.USER_ID,
354 SYSDATE,
355 fnd_global.USER_ID,
356 SYSDATE,
357 fnd_global.LOGIN_ID
358 FROM zpb_measure_scope_exempt_users
359 WHERE BUSINESS_PROCESS_ENTITY_ID = source_ac_id_in
360 AND BUSINESS_PROCESS_ENTITY_TYPE = 'A'
361 AND TASK_ID = source_task_id;
362
363 END copy_bp_measure_scope_recs;
364
365 -- Bug 4587184: Add source_task_id and target_task_id to the signature
366 PROCEDURE copy_bp_scope_access_recs (
367 source_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
368 source_task_id IN zpb_business_process_scope.task_id%TYPE ,
369 target_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
370 target_task_id IN zpb_business_process_scope.task_id%TYPE )
371 IS
372
373 BEGIN
374 -- Bug 4587184: Consider task_id in the query
375 INSERT INTO zpb_business_process_scope
376 (ANALYSIS_CYCLE_ID,
377 TASK_ID,
378 RESTRICTION_TYPE,
379 START_TIME_TYPE,
380 START_TIME_MEMBER_ID,
381 START_RELATIVE_TYPE_CODE,
382 START_PERIODS,
383 START_TIME_LEVEL_ID,
384 END_TIME_TYPE,
385 END_TIME_MEMBER_ID,
386 END_RELATIVE_TYPE_CODE,
387 END_PERIODS,
388 END_TIME_LEVEL_ID,
389 TIME_HIERARCHY_ID,
390 CREATED_BY,
391 CREATION_DATE,
392 LAST_UPDATED_BY,
393 LAST_UPDATE_DATE,
394 LAST_UPDATE_LOGIN)
395 SELECT target_ac_id_in,
396 target_task_id,
397 RESTRICTION_TYPE,
398 START_TIME_TYPE,
399 START_TIME_MEMBER_ID,
400 START_RELATIVE_TYPE_CODE,
401 START_PERIODS,
402 START_TIME_LEVEL_ID,
403 END_TIME_TYPE,
404 END_TIME_MEMBER_ID,
405 END_RELATIVE_TYPE_CODE,
406 END_PERIODS,
407 END_TIME_LEVEL_ID,
408 TIME_HIERARCHY_ID,
409 fnd_global.USER_ID,
410 SYSDATE,
411 fnd_global.USER_ID,
412 SYSDATE,
413 fnd_global.LOGIN_ID
414 FROM zpb_business_process_scope
415 WHERE ANALYSIS_CYCLE_ID = source_ac_id_in
416 AND TASK_ID = source_task_id;
417
418 END copy_bp_scope_access_recs;
419
420 PROCEDURE copy_cycle_comments_recs (
421 source_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
422 target_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE)
423 IS
424 BEGIN
425 INSERT INTO zpb_cycle_comments
426 (COMMENT_ID,
427 ANALYSIS_CYCLE_ID,
428 COMMENTS,
429 OWNER_ID,
430 CREATED_BY,
431 CREATION_DATE,
432 LAST_UPDATED_BY,
433 LAST_UPDATE_DATE,
434 LAST_UPDATE_LOGIN)
435 SELECT zpb_cycle_comments_id_seq.NEXTVAL,
436 target_ac_id_in,
437 COMMENTS,
438 OWNER_ID,
439 fnd_global.USER_ID,
440 SYSDATE,
441 fnd_global.USER_ID,
442 SYSDATE,
443 fnd_global.LOGIN_ID
447 END copy_cycle_comments_recs;
444 FROM zpb_cycle_comments
445 WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
446
448
449
450 PROCEDURE copy_task_param_recs (
451 source_task_id_in IN zpb_analysis_cycle_tasks.task_id%TYPE,
452 target_task_id_in IN zpb_analysis_cycle_tasks.task_id%TYPE)
453 IS
454 BEGIN
455 INSERT INTO zpb_task_parameters
456 ( NAME,
457 TASK_ID,
458 VALUE,
459 PARAM_ID,
460 CREATED_BY,
461 CREATION_DATE,
462 LAST_UPDATED_BY,
463 LAST_UPDATE_DATE,
464 LAST_UPDATE_LOGIN)
465 SELECT NAME,
466 target_task_id_in,
467 decode(name, 'OWNER_ID', to_char( fnd_global.USER_ID ),VALUE),
468 zpb_task_param_id_seq.NEXTVAL,
469 fnd_global.USER_ID,
470 SYSDATE,
471 fnd_global.USER_ID,
472 SYSDATE,
473 fnd_global.LOGIN_ID
474 FROM zpb_task_parameters
475 WHERE TASK_ID = source_task_id_in;
476
477 exception
478 WHEN OTHERS THEN
479 ZPB_ERROR_HANDLER.RAISE_EXCEPTION (G_PKG_NAME, 'copy_task_param_recs');
480
481 END copy_task_param_recs;
482
483
484 PROCEDURE copy_ac_task_recs (
485 source_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
486 target_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
487 is_anal_excep_copied IN boolean default true)
488 IS
489 CURSOR ac_task_cur IS
490 SELECT *
491 FROM zpb_analysis_cycle_tasks
492 WHERE analysis_cycle_id = source_ac_id_in;
493
494 cursor analy_excep_cur(l_taskId number) is
495 select 1 FROM zpb_task_parameters where name = 'EXCEPTION_TYPE'
496 and value = 'A' and task_id = l_taskId;
497
498 target_task_id zpb_analysis_cycle_tasks.task_id%TYPE;
499 source_task_id zpb_analysis_cycle_tasks.task_id%TYPE;
500
501 excep_count number;
502 copy_task boolean :=true;
503 BEGIN
504 FOR ac_task_rec IN ac_task_cur LOOP
505 if (ac_task_rec.WF_PROCESS_NAME = 'EXCEPTION' and is_anal_excep_copied = false)
506 then
507 open analy_excep_cur(ac_task_rec.task_id);
508 fetch analy_excep_cur into excep_count;
509 if analy_excep_cur%FOUND
510 then
511 copy_task := false;
512 end if;
513 close analy_excep_cur;
514 else
515 copy_task := true;
516 end if;
517 if copy_task = true
518 then
519 SELECT zpb_task_id_seq.NEXTVAL INTO target_task_id FROM DUAL;
520 source_task_id := ac_task_rec.task_id;
521 ac_task_rec.analysis_cycle_id := target_ac_id_in;
522 ac_task_rec.task_id := target_task_id;
523 ac_task_rec.CREATED_BY := fnd_global.USER_ID;
524 ac_task_rec.CREATION_DATE := SYSDATE;
525 ac_task_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
526 ac_task_rec.LAST_UPDATE_DATE := SYSDATE;
527 ac_task_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
528 INSERT INTO zpb_analysis_cycle_tasks(ANALYSIS_CYCLE_ID,
529 TASK_ID,
530 SEQUENCE,
531 TASK_NAME,
532 STATUS_CODE,
533 ITEM_TYPE,
534 WF_PROCESS_NAME,
535 ITEM_KEY,
536 START_DATE,
537 HIDE_SHOW,
538 CREATION_DATE,
539 CREATED_BY,
540 LAST_UPDATED_BY,
541 LAST_UPDATE_DATE,
542 LAST_UPDATE_LOGIN,
543 OWNER_ID)
544 VALUES (ac_task_rec.ANALYSIS_CYCLE_ID,
545 ac_task_rec.TASK_ID,
546 ac_task_rec.SEQUENCE,
547 ac_task_rec.TASK_NAME,
548 ac_task_rec.STATUS_CODE,
549 ac_task_rec.ITEM_TYPE,
550 ac_task_rec.WF_PROCESS_NAME,
551 ac_task_rec.ITEM_KEY,
552 ac_task_rec.START_DATE,
553 ac_task_rec.HIDE_SHOW,
554 ac_task_rec.CREATION_DATE,
555 ac_task_rec.CREATED_BY,
556 ac_task_rec.LAST_UPDATED_BY,
557 ac_task_rec.LAST_UPDATE_DATE,
558 ac_task_rec.LAST_UPDATE_LOGIN,
559 fnd_global.USER_ID);
560 copy_task_param_recs(source_task_id, target_task_id);
561
562 -- Bug 4587184: Add the following condition to copy the tasks
563 IF (ac_task_rec.WF_PROCESS_NAME = 'SET_VIEW_RESTRICTION') THEN
564 copy_bp_scope_access_recs
565 ( source_ac_id_in
566 , source_task_id
567 , target_ac_id_in
568 , target_task_id);
569
570 copy_bp_measure_scope_recs
571 ( source_ac_id_in
572 , source_task_id
573 , target_ac_id_in
574 , target_task_id);
575 END IF;
576
577 end if;
578 END LOOP;
579 EXCEPTION
580 when others then
581 if analy_excep_cur%isopen
582 then
583 close analy_excep_cur;
584 end if;
585 ZPB_ERROR_HANDLER.RAISE_EXCEPTION(G_PKG_NAME, 'copy_ac_task_recs');
586
587 END copy_ac_task_recs;
588
589 PROCEDURE copy_solve_member_defs_recs (
593 BEGIN
590 source_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
591 target_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE)
592 IS
594
595 INSERT INTO zpb_solve_member_defs
596 (ANALYSIS_CYCLE_ID,
597 MEMBER,
598 SOURCE_TYPE,
599 MEMBER_ORDER,
600 CALCSTEP_PATH,
601 CALC_DESCRIPTION,
602 CALC_TYPE,
603 CALC_PARAMETERS,
604 MODEL_EQUATION,
605 PROPAGATE_TARGET,
606 DATA_SOURCE,
607 CURRENT_MODIFIED,
608 CREATED_BY,
609 CREATION_DATE,
610 LAST_UPDATED_BY,
611 LAST_UPDATE_DATE,
612 LAST_UPDATE_LOGIN)
613 SELECT target_ac_id_in,
614 MEMBER,
615 SOURCE_TYPE,
616 MEMBER_ORDER,
617 CALCSTEP_PATH,
618 CALC_DESCRIPTION,
619 CALC_TYPE,
620 CALC_PARAMETERS,
621 MODEL_EQUATION,
622 PROPAGATE_TARGET,
623 DATA_SOURCE,
624 CURRENT_MODIFIED,
625 fnd_global.USER_ID,
626 SYSDATE,
627 fnd_global.USER_ID,
628 SYSDATE,
629 fnd_global.LOGIN_ID
630 FROM zpb_solve_member_defs
631 WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
632 END copy_solve_member_defs_recs;
633
634
635 PROCEDURE copy_copy_dim_members_recs (
636 source_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
637 target_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE)
638 IS
639 BEGIN
640
641 INSERT INTO zpb_copy_dim_members
642 (DIM,
643 ANALYSIS_CYCLE_ID,
644 SOURCE_NUM_MEMBERS,
645 TARGET_NUM_MEMBERS,
646 CREATED_BY,
647 CREATION_DATE,
648 LAST_UPDATED_BY,
649 LAST_UPDATE_DATE,
650 LAST_UPDATE_LOGIN,
651 SAME_SELECTION,
652 LINE_MEMBER_ID)
653 SELECT DIM,
654 target_ac_id_in,
655 SOURCE_NUM_MEMBERS,
656 TARGET_NUM_MEMBERS,
657 fnd_global.USER_ID,
658 SYSDATE,
659 fnd_global.USER_ID,
660 SYSDATE,
661 fnd_global.LOGIN_ID,
662 SAME_SELECTION,
663 LINE_MEMBER_ID
664 FROM zpb_copy_dim_members
665 WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
666
667 END copy_copy_dim_members_recs;
668
669 PROCEDURE copy_data_init_defs_recs (
670 source_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
671 target_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE)
672 IS
673 BEGIN
674
675 /* Bug#5092815, Added propagated_flag */
676
677 INSERT INTO zpb_data_initialization_defs
678 (ANALYSIS_CYCLE_ID,
679 MEMBER,
680 SOURCE_VIEW,
681 LAG_TIME_PERIODS,
682 LAG_TIME_LEVEL,
683 CHANGE_NUMBER,
684 PERCENTAGE_FLAG,
685 CREATED_BY,
686 CREATION_DATE,
687 LAST_UPDATED_BY,
688 LAST_UPDATE_DATE,
689 LAST_UPDATE_LOGIN,
690 QUERY_PATH,
691 SOURCE_QUERY_NAME,
692 TARGET_QUERY_NAME,
693 PROPAGATED_FLAG)
694 SELECT target_ac_id_in,
695 MEMBER,
696 SOURCE_VIEW,
697 LAG_TIME_PERIODS,
698 LAG_TIME_LEVEL,
699 CHANGE_NUMBER,
700 PERCENTAGE_FLAG,
701 fnd_global.USER_ID,
702 SYSDATE,
703 fnd_global.USER_ID,
704 SYSDATE,
705 fnd_global.LOGIN_ID,
706 QUERY_PATH,
707 SOURCE_QUERY_NAME,
708 TARGET_QUERY_NAME,
709 PROPAGATED_FLAG
710 FROM zpb_data_initialization_defs
711 WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
712
713 END copy_data_init_defs_recs;
714
715
716 PROCEDURE copy_solve_input_level_recs (
717 source_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
718 target_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE)
719 IS
720 BEGIN
721 INSERT INTO zpb_solve_input_selections
722 (ANALYSIS_CYCLE_ID,
723 MEMBER,
724 MEMBER_ORDER,
725 DIMENSION,
726 HIERARCHY,
727 SELECTION_NAME,
728 PROPAGATED_FLAG,
729 SELECTION_PATH,
730 CREATED_BY,
731 CREATION_DATE,
732 LAST_UPDATED_BY,
733 LAST_UPDATE_DATE,
734 LAST_UPDATE_LOGIN)
735 SELECT target_ac_id_in,
736 MEMBER,
737 MEMBER_ORDER,
738 DIMENSION,
739 HIERARCHY,
740 SELECTION_NAME,
741 PROPAGATED_FLAG,
742 SELECTION_PATH,
743 fnd_global.USER_ID,
744 SYSDATE,
745 fnd_global.USER_ID,
746 SYSDATE,
747 fnd_global.LOGIN_ID
748 FROM zpb_solve_input_selections
749 WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
750
751 END copy_solve_input_level_recs;
752
753
754 PROCEDURE copy_solve_output_level_recs (
755 source_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
759
756 target_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE)
757 IS
758 BEGIN
760 INSERT INTO zpb_solve_output_selections
761 (ANALYSIS_CYCLE_ID,
762 MEMBER,
763 MEMBER_ORDER,
764 DIMENSION,
765 HIERARCHY,
766 SELECTION_NAME,
767 PROPAGATED_FLAG,
768 MATCH_INPUT_FLAG,
769 SELECTION_PATH,
770 CREATED_BY,
771 CREATION_DATE,
772 LAST_UPDATED_BY,
773 LAST_UPDATE_DATE,
774 LAST_UPDATE_LOGIN)
775 SELECT target_ac_id_in,
776 MEMBER,
777 MEMBER_ORDER,
778 DIMENSION,
779 HIERARCHY,
780 SELECTION_NAME,
781 PROPAGATED_FLAG,
782 MATCH_INPUT_FLAG,
783 SELECTION_PATH,
784 fnd_global.USER_ID,
785 SYSDATE,
786 fnd_global.USER_ID,
787 SYSDATE,
788 fnd_global.LOGIN_ID
789 FROM zpb_solve_output_selections
790 WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
791
792 END copy_solve_output_level_recs;
793
794
795 PROCEDURE copy_solve_alloc_defs_recs (
796 source_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
797 target_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE)
798 IS
799 BEGIN
800
801 INSERT INTO zpb_solve_allocation_defs
802 (ANALYSIS_CYCLE_ID,
803 MEMBER,
804 MEMBER_ORDER,
805 RULE_NAME,
806 METHOD,
807 BASIS,
808 QUALIFIER,
809 EVALUATION_OPTION,
810 ROUND_DECIMALS,
811 ROUND_ENABLED,
812 CREATED_BY,
813 CREATION_DATE,
814 LAST_UPDATED_BY,
815 LAST_UPDATE_DATE,
816 LAST_UPDATE_LOGIN)
817 SELECT target_ac_id_in,
818 MEMBER,
819 MEMBER_ORDER,
820 RULE_NAME,
821 METHOD,
822 BASIS,
823 QUALIFIER,
824 EVALUATION_OPTION,
825 ROUND_DECIMALS,
826 ROUND_ENABLED,
827 fnd_global.USER_ID,
828 SYSDATE,
829 fnd_global.USER_ID,
830 SYSDATE,
831 fnd_global.LOGIN_ID
832 FROM zpb_solve_allocation_defs
833 WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
834
835
836 END copy_solve_alloc_defs_recs;
837
838 PROCEDURE copy_line_dimensionality_recs (
839 source_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
840 target_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE)
841 IS
842 BEGIN
843
844 INSERT INTO zpb_line_dimensionality
845 (ANALYSIS_CYCLE_ID,
846 MEMBER,
847 MEMBER_ORDER,
848 DIMENSION,
849 SUM_MEMBERS_NUMBER,
850 SUM_MEMBERS_FLAG,
851 EXCLUDE_FROM_SOLVE_FLAG,
852 FORCE_INPUT_FLAG,
853 SUM_SELECTION_NAME,
854 PROPAGATED_FLAG,
855 SUM_SELECTION_PATH,
856 CREATED_BY,
857 CREATION_DATE,
858 LAST_UPDATED_BY,
859 LAST_UPDATE_DATE,
860 LAST_UPDATE_LOGIN)
861 SELECT target_ac_id_in,
862 MEMBER,
863 MEMBER_ORDER,
864 DIMENSION,
865 SUM_MEMBERS_NUMBER,
866 SUM_MEMBERS_FLAG,
867 EXCLUDE_FROM_SOLVE_FLAG,
868 FORCE_INPUT_FLAG,
869 SUM_SELECTION_NAME,
870 PROPAGATED_FLAG,
871 SUM_SELECTION_PATH,
872 fnd_global.USER_ID,
873 SYSDATE,
874 fnd_global.USER_ID,
875 SYSDATE,
876 fnd_global.LOGIN_ID
877 FROM zpb_line_dimensionality
878 WHERE ANALYSIS_CYCLE_ID = source_ac_id_in;
879
880 END copy_line_dimensionality_recs;
881
882 PROCEDURE copy_solve_hier_order_recs (
883 source_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
884 target_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE)
885 IS
886 BEGIN
887 INSERT INTO zpb_solve_hier_order
888 (solve_hier_order_id,
889 analysis_cycle_id,
890 dimension,
891 hierarchy,
892 hierarchy_order,
893 first_last_flag,
894 object_version_number,
895 creation_date,
896 created_by,
897 last_updated_by,
898 last_update_date,
899 last_update_login
900 )
901 SELECT zpb_solve_hier_order_s.NEXTVAL,
902 target_ac_id_in,
903 dimension,
904 hierarchy,
905 hierarchy_order,
906 first_last_flag,
907 object_version_number,
908 sysdate,
909 fnd_global.user_id,
910 fnd_global.user_id,
911 sysdate,
912 fnd_global.login_id
913 FROM zpb_solve_hier_order
917
914 WHERE analysis_cycle_id = source_ac_id_in;
915
916 END copy_solve_hier_order_recs;
918 PROCEDURE create_ac_copy (
919 source_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
920 target_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
921 is_comments_copied IN boolean default true,
922 is_anal_excep_copied IN boolean default true)
923 IS
924 BEGIN
925 copy_ac_table_rec(source_ac_id_in, target_ac_id_in);
926 copy_ac_param_values_recs(source_ac_id_in, target_ac_id_in);
927 copy_cycle_currency_recs(source_ac_id_in, target_ac_id_in);
928 copy_external_user_recs(source_ac_id_in, target_ac_id_in);
929 copy_cycle_datasets_recs(source_ac_id_in, target_ac_id_in);
930 copy_ac_task_recs(source_ac_id_in, target_ac_id_in,is_anal_excep_copied);
931 copy_cycle_model_dim_recs(source_ac_id_in, target_ac_id_in);
932 if is_comments_copied = true then
933 copy_cycle_comments_recs(source_ac_id_in, target_ac_id_in);
934 end if;
935 copy_solve_member_defs_recs(source_ac_id_in, target_ac_id_in);
936 copy_copy_dim_members_recs(source_ac_id_in, target_ac_id_in);
937 copy_data_init_defs_recs(source_ac_id_in, target_ac_id_in);
938 copy_solve_input_level_recs(source_ac_id_in, target_ac_id_in);
939 copy_solve_output_level_recs(source_ac_id_in, target_ac_id_in);
940 copy_solve_alloc_defs_recs(source_ac_id_in, target_ac_id_in);
941 copy_line_dimensionality_recs(source_ac_id_in, target_ac_id_in);
942 -- Bug 4587184: Remove the following calls, because they are invoked from
943 -- copy_ac_task_recs now.
944 -- copy_bp_scope_access_recs(source_ac_id_in, target_ac_id_in);
945 -- copy_bp_measure_scope_recs(source_ac_id_in, target_ac_id_in);
946 copy_solve_hier_order_recs(source_ac_id_in, target_ac_id_in);
947 END create_ac_copy;
948
949
950
951 /*
952 * This internal procedure copes only those tasks that have not yet started in
953 * instance target_ac_id_in. This is used when republishing a cycle
954 */
955 PROCEDURE copy_nonstarted_tasks (
956 source_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
957 target_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE)
958 IS
959
960 -- cursor ac_task_cur contains all tasks from source_ac_id_in whose
961 -- corresponding tasks in target_ac_id_in have not yet started
962 CURSOR ac_task_cur IS
963 SELECT *
964 FROM zpb_analysis_cycle_tasks
965 WHERE analysis_cycle_id = source_ac_id_in and
966 sequence >=
967
968 ((SELECT min(sequence)
969 FROM zpb_analysis_cycle_tasks
970 WHERE analysis_cycle_id = target_ac_id_in and
971 status_code is null));
972
973 target_task_id zpb_analysis_cycle_tasks.task_id%TYPE;
974 source_task_id zpb_analysis_cycle_tasks.task_id%TYPE;
975 todelete_task_id zpb_analysis_cycle_tasks.task_id%TYPE;
976
977 BEGIN
978
979 -- loop over all tasks that need to be updated
980 FOR ac_task_rec IN ac_task_cur LOOP
981 begin
982
983 -- First delete task from target_ac_id_in
984 -- For newly created tasks in the source, no deletion is necessary
985 SELECT task_id into todelete_task_id
986 FROM zpb_analysis_cycle_tasks
987 WHERE analysis_cycle_id = target_ac_id_in and
988 sequence = ac_task_rec.sequence;
989
990 exception
991 when no_data_found then
992 todelete_task_id := null;
993 end;
994
995 DELETE FROM zpb_task_parameters
996 WHERE task_id = todelete_task_id;
997
998 DELETE FROM zpb_analysis_cycle_tasks
999 WHERE task_id = todelete_task_id;
1000
1001 SELECT zpb_task_id_seq.NEXTVAL INTO target_task_id FROM DUAL;
1002 source_task_id := ac_task_rec.task_id;
1003 ac_task_rec.analysis_cycle_id := target_ac_id_in;
1004 ac_task_rec.task_id := target_task_id;
1005 ac_task_rec.CREATED_BY := fnd_global.USER_ID;
1006 ac_task_rec.CREATION_DATE := SYSDATE;
1007 ac_task_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
1008 ac_task_rec.LAST_UPDATE_DATE := SYSDATE;
1009 ac_task_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
1010 INSERT INTO zpb_analysis_cycle_tasks(ANALYSIS_CYCLE_ID,
1011 TASK_ID,
1012 SEQUENCE,
1013 TASK_NAME,
1014 STATUS_CODE,
1015 ITEM_TYPE,
1016 WF_PROCESS_NAME,
1017 ITEM_KEY,
1018 START_DATE,
1019 HIDE_SHOW,
1020 CREATION_DATE,
1021 CREATED_BY,
1022 LAST_UPDATED_BY,
1023 LAST_UPDATE_DATE,
1024 LAST_UPDATE_LOGIN,
1025 OWNER_ID)
1026 VALUES (ac_task_rec.ANALYSIS_CYCLE_ID,
1027 ac_task_rec.TASK_ID,
1028 ac_task_rec.SEQUENCE,
1029 ac_task_rec.TASK_NAME,
1030 ac_task_rec.STATUS_CODE,
1031 ac_task_rec.ITEM_TYPE,
1032 ac_task_rec.WF_PROCESS_NAME,
1033 ac_task_rec.ITEM_KEY,
1034 ac_task_rec.START_DATE,
1035 ac_task_rec.HIDE_SHOW,
1036 ac_task_rec.CREATION_DATE,
1037 ac_task_rec.CREATED_BY,
1041 ac_task_rec.OWNER_ID);
1038 ac_task_rec.LAST_UPDATED_BY,
1039 ac_task_rec.LAST_UPDATE_DATE,
1040 ac_task_rec.LAST_UPDATE_LOGIN,
1042
1043 copy_task_param_recs(source_task_id, target_task_id);
1044
1045 -- Bug 4587184: Add the following condition to copy the tasks
1046 IF (ac_task_rec.WF_PROCESS_NAME = 'SET_VIEW_RESTRICTION') THEN
1047 copy_bp_scope_access_recs
1048 ( source_ac_id_in
1049 , source_task_id
1050 , target_ac_id_in
1051 , target_task_id);
1052
1053 copy_bp_measure_scope_recs
1054 ( source_ac_id_in
1055 , source_task_id
1056 , target_ac_id_in
1057 , target_task_id);
1058 END IF;
1059
1060 END LOOP;
1061 END copy_nonstarted_tasks;
1062
1063 --BPEXT
1064 PROCEDURE updateHorizonParams(p_start_mem IN VARCHAR2
1065 ,p_end_mem IN VARCHAR2
1066 ,new_ac_id IN NUMBER) AS
1067 BEGIN
1068 IF (p_start_mem IS NOT NULL ) THEN
1069 UPDATE zpb_ac_param_values SET value = p_start_mem WHERE
1070 analysis_cycle_id = new_ac_id AND param_id =
1071 ( SELECT tag FROM fnd_lookup_values_vl WHERE lookup_type = 'ZPB_PARAMS' AND
1072 lookup_code = 'CAL_HS_TIME_MEMBER');
1073 END IF;
1074
1075 IF (p_end_mem IS NOT NULL ) THEN
1076 UPDATE zpb_ac_param_values SET value = p_end_mem WHERE
1077 analysis_cycle_id = new_ac_id AND param_id =
1078 ( SELECT tag FROM fnd_lookup_values_vl WHERE lookup_type = 'ZPB_PARAMS' AND
1079 lookup_code = 'CAL_HE_TIME_MEMBER');
1080 END IF;
1081 END updateHorizonParams;
1082 --BPEXT
1083
1084 /*
1085 * Public */
1086 PROCEDURE delete_ac (
1087 ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
1088 delete_tasks IN VARCHAR2 default FND_API.G_TRUE)
1089 IS
1090 CURSOR ac_task_cur IS
1091 SELECT *
1092 FROM zpb_analysis_cycle_tasks
1093 WHERE analysis_cycle_id = ac_id_in;
1094
1095 pet_rec zpb_cycle_relationships%ROWTYPE;
1096 delete_task_id zpb_analysis_cycle_tasks.task_id%TYPE;
1097 l_return_status VARCHAR2(1);
1098 l_msg_count NUMBER;
1099 l_msg_data VARCHAR2(2000);
1100 BEGIN
1101 BEGIN
1102 SELECT *
1103 INTO pet_rec
1104 FROM zpb_cycle_relationships
1105 WHERE published_ac_id = ac_id_in OR
1106 editable_ac_id = ac_id_in OR
1107 tmp_ac_id = ac_id_in;
1108
1109 IF pet_rec.published_ac_id = ac_id_in THEN
1110 UPDATE zpb_cycle_relationships
1111 SET published_ac_id = NULL,
1112 LAST_UPDATED_BY = fnd_global.USER_ID,
1113 LAST_UPDATE_DATE = SYSDATE,
1114 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1115 WHERE relationship_id = pet_rec.relationship_id;
1116 ELSIF pet_rec.editable_ac_id = ac_id_in THEN
1117 UPDATE zpb_cycle_relationships
1118 SET editable_ac_id = NULL,
1119 LAST_UPDATED_BY = fnd_global.USER_ID,
1120 LAST_UPDATE_DATE = SYSDATE,
1121 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1122 WHERE relationship_id = pet_rec.relationship_id;
1123 ELSIF pet_rec.tmp_ac_id = ac_id_in THEN
1124 UPDATE zpb_cycle_relationships
1125 SET tmp_ac_id = NULL,
1126 LAST_UPDATED_BY = fnd_global.USER_ID,
1127 LAST_UPDATE_DATE = SYSDATE,
1128 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1129 WHERE relationship_id = pet_rec.relationship_id;
1130 END IF;
1131
1132 DELETE FROM zpb_cycle_relationships
1133 WHERE published_ac_id IS NULL AND
1134 editable_ac_id IS NULL AND
1135 tmp_ac_id IS NULL;
1136 EXCEPTION
1137 WHEN NO_DATA_FOUND THEN
1138 NULL;
1139 END;
1140
1141 IF FND_API.To_Boolean(delete_tasks) THEN
1142
1143 FOR ac_task_rec IN ac_task_cur LOOP
1144 DELETE FROM zpb_task_parameters
1145 WHERE task_id = ac_task_rec.task_id;
1146 END LOOP;
1147
1148 DELETE FROM zpb_analysis_cycle_tasks
1149 WHERE analysis_cycle_id = ac_id_in;
1150 END IF;
1151
1152 DELETE FROM zpb_business_process_scope
1153 WHERE analysis_cycle_id = ac_id_in;
1154
1155 DELETE FROM zpb_copy_dim_members
1156 WHERE analysis_cycle_id = ac_id_in;
1157
1158 DELETE FROM zpb_measure_scope_exempt_users
1159 WHERE BUSINESS_PROCESS_ENTITY_ID = ac_id_in
1160 AND BUSINESS_PROCESS_ENTITY_TYPE = 'A';
1161
1162 DELETE FROM zpb_cycle_comments
1163 WHERE analysis_cycle_id = ac_id_in;
1164
1165 DELETE FROM zpb_cycle_model_dimensions
1166 WHERE analysis_cycle_id = ac_id_in;
1167
1168 DELETE FROM zpb_ac_param_values
1169 WHERE analysis_cycle_id = ac_id_in;
1170
1171 DELETE FROM zpb_cycle_datasets
1172 WHERE analysis_cycle_id = ac_id_in;
1173
1174 DELETE FROM zpb_analysis_cycles
1175 WHERE analysis_cycle_id = ac_id_in;
1176
1177 DELETE FROM zpb_solve_member_defs
1178 WHERE analysis_cycle_id = ac_id_in;
1179
1183 DELETE FROM zpb_solve_input_levels
1180 DELETE FROM zpb_data_initialization_defs
1181 WHERE analysis_cycle_id = ac_id_in;
1182
1184 WHERE analysis_cycle_id = ac_id_in;
1185
1186 DELETE FROM zpb_solve_output_levels
1187 WHERE analysis_cycle_id = ac_id_in;
1188
1189 DELETE FROM zpb_solve_allocation_defs
1190 WHERE analysis_cycle_id = ac_id_in;
1191
1192 DELETE FROM zpb_solve_output_selections
1193 WHERE analysis_cycle_id = ac_id_in;
1194
1195 DELETE FROM zpb_solve_input_selections
1196 WHERE analysis_cycle_id = ac_id_in;
1197
1198 DELETE FROM zpb_line_dimensionality
1199 WHERE analysis_cycle_id = ac_id_in;
1200
1201 DELETE FROM zpb_cycle_currencies
1202 WHERE analysis_cycle_id = ac_id_in;
1203
1204 DELETE FROM zpb_bp_external_users
1205 WHERE analysis_cycle_id = ac_id_in;
1206
1207 DELETE FROM ZPB_BP_VALIDATION_RESULTS
1208 WHERE BUS_PROC_ID = ac_id_in;
1209
1210 DELETE FROM zpb_solve_hier_order
1211 WHERE analysis_cycle_id = ac_id_in;
1212
1213 END delete_ac;
1214
1215 /* Haven't found a place where this api is called, but this
1216 api has cursor previous_instance_cur that does not confirm to
1217 missing obj changes */
1218
1219 PROCEDURE delete_published_ac (
1220 ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
1221 prev_instance_options_in IN VARCHAR2,
1222 curr_instance_options_in IN VARCHAR2)
1223 IS
1224 CURSOR previous_instance_cur IS
1225 SELECT zaci.instance_ac_id
1226 FROM zpb_analysis_cycle_instances zaci,
1227 zpb_analysis_cycles zac
1228 WHERE zaci.analysis_cycle_id = ac_id_in and
1229 zac.analysis_cycle_id = zaci.analysis_cycle_id and
1230 zaci.instance_ac_id <> zac.current_instance_id;
1231
1232 curr_instance_ac_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
1233 l_count NUMBER;
1234 BEGIN
1235
1236 IF prev_instance_options_in = 'DELETE_PREVIOUS_INSTANCE' THEN
1237 FOR instance_rec IN previous_instance_cur LOOP
1238 DELETE FROM zpb_analysis_cycle_instances
1239 WHERE instance_ac_id = instance_rec.instance_ac_id;
1240
1241 delete_ac(instance_rec.instance_ac_id);
1242 END LOOP;
1243 END IF;
1244
1245 IF curr_instance_options_in = 'DELETE_CURR_INSTANCE' THEN
1246 SELECT current_instance_id
1247 INTO curr_instance_ac_id
1248 FROM zpb_analysis_cycles
1249 WHERE analysis_cycle_id = ac_id_in;
1250
1251 --DELETE FROM zpb_current_instances
1252 -- WHERE current_instance_ac_id = curr_instance_ac_id;
1253
1254 DELETE FROM zpb_analysis_cycle_instances
1255 WHERE instance_ac_id = curr_instance_ac_id;
1256
1257 delete_ac(curr_instance_ac_id);
1258
1259 END IF;
1260
1261 /*
1262 * The instances can no longer refer to their definition
1263 * AC_ID because it has been deleted from the tables.
1264 */
1265 UPDATE zpb_analysis_cycle_instances
1266 SET analysis_cycle_id = NULL,
1267 LAST_UPDATED_BY = fnd_global.USER_ID,
1268 LAST_UPDATE_DATE = SYSDATE,
1269 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1270 WHERE analysis_cycle_id = ac_id_in;
1271
1272 delete_ac(ac_id_in);
1273
1274 END delete_published_ac;
1275
1276 PROCEDURE getEditableCopyID (
1277 published_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
1278 editable_ac_id_out OUT NOCOPY zpb_analysis_cycles.analysis_cycle_id%TYPE)
1279 IS
1280 return_ac_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
1281 BEGIN
1282
1283 SELECT editable_ac_id
1284 INTO return_ac_id
1285 FROM zpb_cycle_relationships
1286 WHERE published_ac_id = published_ac_id_in;
1287
1288 editable_ac_id_out := return_ac_id;
1289
1290 END getEditableCopyID;
1291
1292 PROCEDURE recoverCycleObjects (
1293 editable_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
1294 is_published_out OUT NOCOPY VARCHAR2)
1295 IS
1296 published_ac_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
1297 BEGIN
1298
1299 is_published_out := 'N';
1300 getPubIdFromEditId(editable_ac_id_in, published_ac_id);
1301
1302 if published_ac_id is not null then
1303 delete_ac(editable_ac_id_in);
1304 is_published_out := 'Y';
1305 end if;
1306
1307 END recoverCycleObjects;
1308
1309 PROCEDURE getPubIdFromEditId (
1310 editable_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
1311 published_ac_id_out OUT NOCOPY zpb_analysis_cycles.analysis_cycle_id%TYPE)
1312 IS
1313 return_ac_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
1314 BEGIN
1315
1316 SELECT published_ac_id
1317 INTO return_ac_id
1318 FROM zpb_cycle_relationships
1319 WHERE editable_ac_id = editable_ac_id_in;
1320
1321 published_ac_id_out := return_ac_id;
1322
1323 END getPubIdFromEditId;
1324
1325 /* This procedure copies the defintion of an existing Business Process
1326 to a new business process.
1327 */
1328 procedure create_duplicate_copy (
1329 published_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
1333 is_comments_copied IN VARCHAR2 default 'true',
1330 editable_ac_name_in IN zpb_analysis_cycles.name%TYPE,
1331 last_updated_by_in IN zpb_analysis_cycles.last_updated_by%TYPE,
1332 ac_business_area_in IN zpb_analysis_cycles.business_area_id%TYPE,
1334 is_analy_excep_copied IN VARCHAR2 default 'true',
1335 editable_ac_id_out OUT NOCOPY zpb_analysis_cycles.analysis_cycle_id%TYPE)
1336
1337 IS
1338 new_ac_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
1339 current_inst_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
1340 relationship_id zpb_cycle_relationships.relationship_id%TYPE;
1341 pet_rec zpb_cycle_relationships%ROWTYPE;
1342 ac_rec zpb_analysis_cycles%ROWTYPE;
1343
1344 comments boolean := true;
1345 analy_excep boolean := true;
1346
1347 BEGIN
1348 SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO new_ac_id FROM DUAL;
1349 SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO current_inst_id FROM DUAL;
1350 SELECT zpb_relationship_id_seq.NEXTVAL INTO relationship_id FROM DUAL;
1351
1352 if lower(is_comments_copied) = 'false' then comments := false; end if;
1353 if lower(is_analy_excep_copied) = 'false' then analy_excep := false; end if;
1354
1355 create_ac_copy(published_ac_id_in, new_ac_id,comments,analy_excep);
1356
1357 -- Bug 5173164: Added current_instance_id, owner_id also
1358 -- in the below given update statement
1359 UPDATE zpb_analysis_cycles
1360 SET name = editable_ac_name_in,
1361 status_code = 'DISABLE_ASAP',
1362 validate_status = 'INVALID',
1363 locked_by = 1,
1364 published_date = NULL,
1365 published_by = NULL,
1366 current_instance_id = current_inst_id,
1367 owner_id = fnd_global.USER_ID,
1368 LAST_UPDATED_BY = fnd_global.USER_ID,
1369 LAST_UPDATE_DATE = SYSDATE,
1370 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1371 WHERE analysis_cycle_id = new_ac_id;
1372
1373 pet_rec.relationship_id := relationship_id;
1374 pet_rec.tmp_ac_id := new_ac_id;
1375 pet_rec.CREATED_BY := fnd_global.USER_ID;
1376 pet_rec.CREATION_DATE := SYSDATE;
1377 pet_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
1378 pet_rec.LAST_UPDATE_DATE := SYSDATE;
1379 pet_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
1380
1381 INSERT INTO zpb_cycle_relationships(RELATIONSHIP_ID,
1382 PUBLISHED_AC_ID,
1383 EDITABLE_AC_ID,
1384 TMP_AC_ID,
1385 LAST_UPDATE_LOGIN,
1386 LAST_UPDATE_DATE,
1387 LAST_UPDATED_BY,
1388 CREATION_DATE,
1389 CREATED_BY)
1390 VALUES (pet_rec.RELATIONSHIP_ID,
1391 pet_rec.PUBLISHED_AC_ID,
1392 pet_rec.EDITABLE_AC_ID,
1393 pet_rec.TMP_AC_ID,
1394 pet_rec.LAST_UPDATE_LOGIN,
1395 pet_rec.LAST_UPDATE_DATE,
1396 pet_rec.LAST_UPDATED_BY,
1397 pet_rec.CREATION_DATE,
1398 pet_rec.CREATED_BY);
1399
1400 editable_ac_id_out := new_ac_id;
1401
1402 end create_duplicate_copy;
1403
1404 PROCEDURE create_editable_copy (
1405 published_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
1406 editable_ac_name_in IN zpb_analysis_cycles.name%TYPE,
1407 last_updated_by_in IN zpb_analysis_cycles.last_updated_by%TYPE,
1408 editable_ac_id_out OUT NOCOPY zpb_analysis_cycles.analysis_cycle_id%TYPE)
1409 IS
1410 new_ac_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
1411 pet_row_rec zpb_cycle_relationships%ROWTYPE;
1412 BEGIN
1413 SELECT *
1414 INTO pet_row_rec
1415 FROM zpb_cycle_relationships
1416 WHERE published_ac_id = published_ac_id_in;
1417
1418 SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO new_ac_id FROM DUAL;
1419
1420 create_ac_copy(published_ac_id_in, new_ac_id);
1421
1422 UPDATE zpb_analysis_cycles
1423 SET name = editable_ac_name_in,
1424 status_code = 'DISABLE_ASAP',
1425 published_date = NULL,
1426 published_by = NULL,
1427 LAST_UPDATED_BY = fnd_global.USER_ID,
1428 LAST_UPDATE_DATE = SYSDATE,
1429 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1430 WHERE analysis_cycle_id = new_ac_id;
1431
1432 UPDATE zpb_cycle_relationships
1433 SET editable_ac_id = new_ac_id,
1434 LAST_UPDATED_BY = fnd_global.USER_ID,
1435 LAST_UPDATE_DATE = SYSDATE,
1436 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1437 WHERE published_ac_id = published_ac_id_in;
1438
1439 editable_ac_id_out := new_ac_id;
1440
1441 END create_editable_copy;
1442
1443
1444 PROCEDURE create_new_cycle (
1445 ac_name_in IN zpb_analysis_cycles.name%TYPE,
1446 ac_owner_id_in IN zpb_analysis_cycles.owner_id%TYPE,
1447 ac_business_area_in IN zpb_business_areas.business_area_id%TYPE,
1448 tmp_ac_id_out OUT NOCOPY zpb_analysis_cycles.analysis_cycle_id%TYPE)
1449 IS
1450 new_ac_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
1451 current_inst_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
1452 relationship_id zpb_cycle_relationships.relationship_id%TYPE;
1453 pet_rec zpb_cycle_relationships%ROWTYPE;
1457 SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO current_inst_id FROM DUAL;
1454 ac_rec zpb_analysis_cycles%ROWTYPE;
1455 BEGIN
1456 SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO new_ac_id FROM DUAL;
1458 SELECT zpb_relationship_id_seq.NEXTVAL INTO relationship_id FROM DUAL;
1459
1460 ac_rec.analysis_cycle_id := new_ac_id;
1461 ac_rec.name := ac_name_in;
1462 ac_rec.validate_status := 'INVALID';
1463 ac_rec.status_code := 'DISABLE_ASAP';
1464 ac_rec.locked_by := 1;
1465 ac_rec.CREATED_BY := fnd_global.USER_ID;
1466 ac_rec.CREATION_DATE := SYSDATE;
1467 ac_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
1468 ac_rec.LAST_UPDATE_DATE := SYSDATE;
1469 ac_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
1470 --ac_rec.PUBLISHED_BY := sys_context('ZPB_CONTEXT', 'shadow_id');
1471 ac_rec.OWNER_ID := ac_owner_id_in;
1472 ac_rec.BUSINESS_AREA_ID := ac_business_area_in;
1473 ac_rec.CURRENT_INSTANCE_ID :=current_inst_id;
1474 INSERT INTO zpb_analysis_cycles(ANALYSIS_CYCLE_ID,
1475 STATUS_CODE,
1476 NAME,
1477 DESCRIPTION,
1478 LOCKED_BY,
1479 VALIDATE_STATUS,
1480 CURRENT_INSTANCE_ID,
1481 PUBLISHED_DATE,
1482 PUBLISHED_BY,
1483 LAST_UPDATE_DATE,
1484 LAST_UPDATED_BY,
1485 CREATION_DATE,
1486 CREATED_BY,
1487 PREV_STATUS_CODE,
1488 LAST_UPDATE_LOGIN,
1489 BUSINESS_AREA_ID,
1490 OWNER_ID)
1491 VALUES (ac_rec.ANALYSIS_CYCLE_ID,
1492 ac_rec.STATUS_CODE,
1493 ac_rec.NAME,
1494 ac_rec.DESCRIPTION,
1495 ac_rec.LOCKED_BY,
1496 ac_rec.VALIDATE_STATUS,
1497 ac_rec.CURRENT_INSTANCE_ID,
1498 ac_rec.PUBLISHED_DATE,
1499 ac_rec.PUBLISHED_BY,
1500 ac_rec.LAST_UPDATE_DATE,
1501 ac_rec.LAST_UPDATED_BY,
1502 ac_rec.CREATION_DATE,
1503 ac_rec.CREATED_BY,
1504 ac_rec.PREV_STATUS_CODE,
1505 ac_rec.LAST_UPDATE_LOGIN,
1506 ac_rec.BUSINESS_AREA_ID,
1507 ac_rec.OWNER_ID);
1508
1509 pet_rec.relationship_id := relationship_id;
1510 pet_rec.tmp_ac_id := new_ac_id;
1511 pet_rec.CREATED_BY := fnd_global.USER_ID;
1512 pet_rec.CREATION_DATE := SYSDATE;
1513 pet_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
1514 pet_rec.LAST_UPDATE_DATE := SYSDATE;
1515 pet_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
1516 INSERT INTO zpb_cycle_relationships(RELATIONSHIP_ID,
1517 PUBLISHED_AC_ID,
1518 EDITABLE_AC_ID,
1519 TMP_AC_ID,
1520 LAST_UPDATE_LOGIN,
1521 LAST_UPDATE_DATE,
1522 LAST_UPDATED_BY,
1523 CREATION_DATE,
1524 CREATED_BY)
1525 VALUES (pet_rec.RELATIONSHIP_ID,
1526 pet_rec.PUBLISHED_AC_ID,
1527 pet_rec.EDITABLE_AC_ID,
1528 pet_rec.TMP_AC_ID,
1529 pet_rec.LAST_UPDATE_LOGIN,
1530 pet_rec.LAST_UPDATE_DATE,
1531 pet_rec.LAST_UPDATED_BY,
1532 pet_rec.CREATION_DATE,
1533 pet_rec.CREATED_BY);
1534 tmp_ac_id_out := new_ac_id;
1535
1536 END create_new_cycle;
1537
1538
1539 PROCEDURE create_new_instance (
1540 ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
1541 instance_ac_id_out OUT NOCOPY zpb_analysis_cycles.analysis_cycle_id%TYPE)
1542 IS
1543 new_ac_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
1544 ac_rec zpb_analysis_cycles%ROWTYPE;
1545 instance_rec zpb_analysis_cycle_instances%ROWTYPE;
1546 instance_desc zpb_analysis_cycle_instances.instance_description%TYPE;
1547 curr_count INTEGER;
1548 curr_count_str VARCHAR2(5);
1549 l_app_view_status zpb_ac_param_values.value%TYPE;
1550 l_appview_param_id zpb_ac_param_values.param_id%TYPE;
1551
1552 --Missing Obj modified the cur to work based on current instance id
1553 cursor latest_instance_desc_cur is
1554 select to_number(substr(instance_description,length(instance_description) -2))
1555 from zpb_analysis_cycle_instances
1556 where instance_ac_id = (select max(instance_ac_id)
1557 from zpb_analysis_cycle_instances aci,
1558 zpb_analysis_cycles pubac,
1559 zpb_analysis_cycles runac
1560 where pubac.analysis_cycle_id = ac_id_in
1561 and pubac.current_instance_id =
1562 runac.current_instance_id
1563 and runac.analysis_cycle_id = aci.instance_ac_id);
1564
1565 CURSOR c_append_view IS
1566 SELECT VALUE FROM ZPB_AC_PARAM_VALUES
1567 WHERE ANALYSIS_CYCLE_ID = ac_id_in AND PARAM_ID = l_appview_param_id ;
1568
1569 BEGIN
1570 SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO new_ac_id FROM DUAL;
1571
1575 INTO ac_rec
1572 create_ac_copy(ac_id_in, new_ac_id);
1573
1574 SELECT *
1576 FROM zpb_analysis_cycles
1577 WHERE analysis_cycle_id = new_ac_id;
1578
1579 SELECT tag INTO l_appview_param_id
1580 FROM fnd_lookup_values_vl
1581 WHERE LOOKUP_CODE = 'APPEND_VIEW'
1582 and LOOKUP_TYPE = 'ZPB_PARAMS';
1583
1584 UPDATE zpb_analysis_cycles
1585 SET status_code = 'PUBLISHED',
1586 LAST_UPDATED_BY = fnd_global.USER_ID,
1587 LAST_UPDATE_DATE = SYSDATE,
1588 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1589 WHERE analysis_cycle_id = new_ac_id;
1590
1591 begin
1592 -- find if its an append view BP, do not show ID in that case
1593 open c_append_view;
1594 fetch c_append_view into l_app_view_status;
1595 if l_app_view_status = 'DO_NOT_APPEND_VIEW' then
1596
1597 -- find the counter for the last instance created
1598 open latest_instance_desc_cur;
1599 fetch latest_instance_desc_cur into curr_count;
1600
1601 if latest_instance_desc_cur%notfound then
1602 -- this is the first instance
1603 curr_count_str := '001';
1604 else
1605 curr_count := curr_count + 1;
1606 if curr_count > 999 then
1607 -- recycle counter
1608 curr_count_str := '00' || to_char(curr_count - 999);
1609 end if;
1610
1611 -- now prepend proper # of zeroes to make the length 3
1612 if curr_count > 99 and curr_count <= 999 then
1613 curr_count_str := to_char(curr_count);
1614 end if;
1615
1616 if curr_count > 9 and curr_count <= 99 then
1617 curr_count_str := '0' || to_char(curr_count);
1618 end if;
1619
1620 if curr_count <= 9 then
1621 curr_count_str := '00' || to_char(curr_count);
1622 end if;
1623 end if;
1624 close c_append_view ;
1625 close latest_instance_desc_cur;
1626 else
1627 curr_count_str := curr_count_str;
1628 close c_append_view ;
1629 end if;
1630
1631 exception
1632 -- pre-existing instances may not be following
1633 -- the same naming convention and may fail.
1634 -- Just start with 001 for these BPs
1635 when others then
1636 curr_count_str := '001';
1637 end;
1638
1639 instance_desc := ac_rec.name || ' ' || curr_count_str;
1640
1641 instance_rec.analysis_cycle_id := ac_id_in;
1642 instance_rec.instance_ac_id := new_ac_id;
1643 instance_rec.instance_description := instance_desc;
1644 instance_rec.CREATED_BY := fnd_global.USER_ID;
1645 instance_rec.CREATION_DATE := SYSDATE;
1646 instance_rec.LAST_UPDATED_BY := fnd_global.USER_ID;
1647 instance_rec.LAST_UPDATE_DATE := SYSDATE;
1648 instance_rec.LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
1649 INSERT INTO zpb_analysis_cycle_instances(ANALYSIS_CYCLE_ID,
1650 INSTANCE_AC_ID,
1651 INSTANCE_DESCRIPTION,
1652 CREATION_DATE,
1653 CREATED_BY,
1654 LAST_UPDATED_BY,
1655 LAST_UPDATE_DATE,
1656 LAST_UPDATE_LOGIN,
1657 STATUS_CODE)
1658 VALUES (instance_rec.ANALYSIS_CYCLE_ID,
1659 instance_rec.INSTANCE_AC_ID,
1660 instance_rec.INSTANCE_DESCRIPTION,
1661 instance_rec.CREATION_DATE,
1662 instance_rec.CREATED_BY,
1663 instance_rec.LAST_UPDATED_BY,
1664 instance_rec.LAST_UPDATE_DATE,
1665 instance_rec.LAST_UPDATE_LOGIN,
1666 instance_rec.STATUS_CODE);
1667 instance_ac_id_out := new_ac_id;
1668 END create_new_instance;
1669
1670
1671 PROCEDURE create_tmp_cycle (
1672 editable_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
1673 tmp_ac_id_out OUT NOCOPY zpb_analysis_cycles.analysis_cycle_id%TYPE)
1674 IS
1675 new_ac_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
1676 pet_row_rec zpb_cycle_relationships%ROWTYPE;
1677 BEGIN
1678 SELECT *
1679 INTO pet_row_rec
1680 FROM zpb_cycle_relationships
1681 WHERE editable_ac_id = editable_ac_id_in;
1682
1683 IF pet_row_rec.tmp_ac_id IS NULL THEN
1684 SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO new_ac_id FROM DUAL;
1685 ELSE
1686 delete_ac(pet_row_rec.tmp_ac_id);
1687 new_ac_id := pet_row_rec.tmp_ac_id;
1688 END IF;
1689
1690 create_ac_copy(editable_ac_id_in, new_ac_id);
1691
1692 UPDATE zpb_cycle_relationships
1693 SET tmp_ac_id = new_ac_id,
1694 LAST_UPDATED_BY = fnd_global.USER_ID,
1695 LAST_UPDATE_DATE = SYSDATE,
1696 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1697 WHERE editable_ac_id = editable_ac_id_in;
1698
1699 tmp_ac_id_out := new_ac_id;
1700
1701 END create_tmp_cycle;
1702
1703
1704 PROCEDURE delete_tmp_ac (
1705 tmp_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE)
1706 IS
1707 editable_ac_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
1708 BEGIN
1709 SELECT editable_ac_id
1710 INTO editable_ac_id
1711 FROM zpb_cycle_relationships
1712 WHERE tmp_ac_id = tmp_ac_id_in;
1713
1714 UPDATE zpb_analysis_cycles
1715 SET
1716 LAST_UPDATED_BY = fnd_global.USER_ID,
1720
1717 LAST_UPDATE_DATE = SYSDATE,
1718 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1719 WHERE analysis_cycle_id = editable_ac_id;
1721 delete_ac(tmp_ac_id_in);
1722 END delete_tmp_ac;
1723
1724
1725
1726 PROCEDURE lock_cycle (
1727 editable_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
1728 user_id_in IN zpb_analysis_cycles.locked_by%TYPE,
1729 locked_by_id_out OUT NOCOPY zpb_analysis_cycles.locked_by%TYPE)
1730 IS
1731 locked_by_id zpb_analysis_cycles.locked_by%TYPE;
1732 BEGIN
1733 SELECT locked_by
1734 INTO locked_by_id
1735 FROM zpb_analysis_cycles
1736 WHERE analysis_cycle_id = editable_ac_id_in;
1737
1738 locked_by_id_out := locked_by_id;
1739
1740 IF locked_by_id IS NULL THEN
1741 UPDATE zpb_analysis_cycles
1742 SET locked_by = user_id_in,
1743 LAST_UPDATED_BY = fnd_global.USER_ID,
1744 LAST_UPDATE_DATE = SYSDATE,
1745 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1746 WHERE analysis_cycle_id = editable_ac_id_in;
1747 locked_by_id_out := user_id_in;
1748 END IF;
1749
1750 END lock_cycle;
1751
1752
1753 PROCEDURE mark_cycle_for_delete (
1754 ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
1755 prev_instance_options_in IN VARCHAR2,
1756 curr_instance_options_in IN VARCHAR2)
1757 IS
1758 CURSOR previous_instance_cur IS
1759 SELECT aci.instance_ac_id, ac.current_instance_id
1760 from zpb_analysis_cycle_instances aci,zpb_analysis_cycles ac,
1761 zpb_analysis_cycles currinst
1762 where currinst.ANALYSIS_CYCLE_ID = ac_id_in
1763 and currinst.current_instance_id=ac.current_instance_id
1764 and ac.analysis_cycle_id=aci.instance_ac_id
1765 and ac.status_code in ('COMPLETE','ERROR','COMPLETE_WITH_WARNING');
1766
1767 CURSOR c_wfItemKey is
1768 select /*+ FIRST_ROWS */ item_key
1769 from WF_ITEM_ATTRIBUTE_VALUES
1770 where item_type = 'ZPBSCHED'
1771 and name = 'ACID'
1772 and number_value = ac_id_in;
1773 v_wfItemKey c_wfItemKey%ROWTYPE;
1774
1775 curr_instance_ac_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
1776 l_count NUMBER;
1777 cycle_type VARCHAR2(30);
1778 tmp_ac_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
1779 edit_ac_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
1780 ownerid NUMBER;
1781 l_REQID NUMBER;
1782 l_REQID2 NUMBER;
1783 respID number := fnd_global.RESP_ID;
1784 respAppID number := fnd_global.RESP_APPL_ID;
1785 ItemType varchar2(8) := 'ZPBSCHED';
1786 ItemKey varchar2(240):='UNINITIALIZED';
1787 l_return_status VARCHAR2(1);
1788 l_msg_count NUMBER;
1789 l_msg_data VARCHAR2(2000);
1790 l_business_area_id number;
1791
1792 BEGIN
1793
1794 -- abudnik 07DEC2005 BUSINESS AREA ID added for ZPB_WF_DELAWINST
1795 select published_by, BUSINESS_AREA_ID into ownerid, l_business_area_id
1796 from zpb_analysis_cycles
1797 where analysis_cycle_id = ac_id_in;
1798
1799 for v_wfItemKey in c_wfItemKey loop
1800 ItemKey:=v_wfItemKey.item_key;
1801 end loop;
1802
1803 -- if ItemKey is not found then this is an unpublished BP
1804 -- and we do not need to initialize the apps context as we will
1805 -- not be submitting any CM requests
1806 if ItemKey <> 'UNINITIALIZED' then
1807 ownerID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
1808 Itemkey => ItemKey,
1809 aname => 'OWNERID');
1810
1811 respID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
1812 Itemkey => ItemKey,
1813 aname => 'RESPID');
1814
1815 respAppID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
1816 Itemkey => ItemKey,
1817 aname => 'RESPAPPID');
1818
1819 -- Set the context before calling submit_request
1820 fnd_global.apps_initialize(ownerID, respID, RespAppId);
1821
1822 end if;
1823
1824 get_cycle_type(ac_id_in, cycle_type);
1825
1826 /*
1827 * If the cycle is a Published cycle, then check whether there were
1828 * any events on which other cycles were dependent. If so, then
1829 * notify the users who are the owners of the dependent cycles.
1830 */
1831 IF cycle_type = 'PUBLISHED' THEN
1832 zpb_wf_ntf.notify_on_delete(ac_id_in, 'ACID');
1833
1834 SELECT tmp_ac_id into tmp_ac_id
1835 FROM zpb_cycle_relationships
1836 WHERE published_ac_id = ac_id_in;
1837
1838 SELECT editable_ac_id into edit_ac_id
1839 FROM zpb_cycle_relationships
1840 WHERE published_ac_id = ac_id_in;
1841
1842 ZPB_WF.CallWFAbort(ac_id_in);
1843
1844 END IF;
1845
1846 /*
1847 * If the cycle is an editable copy, then also update
1848 * the ZPB_CYCLE_RELATIONSHIPS table
1849 */
1850 IF cycle_type = 'EDITABLE_COPY' THEN
1851
1852 SELECT tmp_ac_id into tmp_ac_id
1853 FROM zpb_cycle_relationships
1854 WHERE editable_ac_id = ac_id_in;
1855
1859 LAST_UPDATE_DATE = SYSDATE,
1856 UPDATE zpb_cycle_relationships
1857 set editable_ac_id = null,
1858 LAST_UPDATED_BY = fnd_global.USER_ID,
1860 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1861 where editable_ac_id = ac_id_in;
1862 END IF;
1863
1864 IF cycle_type = 'UNPUBLISHED' THEN
1865
1866 SELECT tmp_ac_id into tmp_ac_id
1867 FROM zpb_cycle_relationships
1868 WHERE editable_ac_id = ac_id_in;
1869
1870 END IF;
1871
1872 -- Now mark the cycle its editable copy, and its temp copy for deletion
1873 UPDATE zpb_analysis_cycles
1874 SET status_code='MARKED_FOR_DELETION',
1875 LAST_UPDATED_BY = fnd_global.USER_ID,
1876 LAST_UPDATE_DATE = SYSDATE,
1877 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1878 WHERE analysis_cycle_id in (ac_id_in, tmp_ac_id, edit_ac_id);
1879
1880 DELETE zpb_dc_objects
1881 WHERE delete_instance_measures_flag = 'D' and
1882 analysis_cycle_id = ac_id_in;
1883
1884 -- now delete any Data Collection templates
1885 -- associated with this cycle
1886 zpb_dc_objects_pvt.delete_template(
1887 1.0, FND_API.G_TRUE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL,
1888 l_return_status, l_msg_count, l_msg_data, ac_id_in);
1889
1890 zpb_dc_objects_pvt.delete_template(
1891 1.0, FND_API.G_TRUE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL,
1892 l_return_status, l_msg_count, l_msg_data, tmp_ac_id);
1893
1894 zpb_dc_objects_pvt.delete_template(
1895 1.0, FND_API.G_TRUE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL,
1896 l_return_status, l_msg_count, l_msg_data, edit_ac_id);
1897
1898 --Loop over all completed/errored instances and delete them
1899 IF prev_instance_options_in = 'DELETE_PREVIOUS_INSTANCE' THEN
1900
1901 FOR instance_rec IN previous_instance_cur LOOP
1902
1903 UPDATE zpb_analysis_cycles
1904 SET status_code='MARKED_FOR_DELETION',
1905 LAST_UPDATED_BY = fnd_global.USER_ID,
1906 LAST_UPDATE_DATE = SYSDATE,
1907 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1908 WHERE analysis_cycle_id = instance_rec.instance_ac_id;
1909
1910 -- Clean up the measure for
1911 -- abudnik 07DEC2005 BUSINESS AREA ID added for ZPB_WF_DELAWINST
1912 l_REQID := FND_REQUEST.SUBMIT_REQUEST ('ZPB', 'ZPB_WF_DELAWINST', NULL, NULL, FALSE, instance_rec.instance_ac_id, ownerid, l_business_area_id);
1913
1914 /*
1915 IF instance_rec.current_instance_id IS NOT NULL THEN
1916 l_REQID2 := FND_REQUEST.SUBMIT_REQUEST ('ZPB', 'ZPB_WF_DELAWINST', NULL, NULL, FALSE, instance_rec.current_instance_id, ownerid);
1917 END IF;
1918 */
1919
1920 -- now delete any Data Collection templates
1921 -- associated with this cycle
1922 zpb_dc_objects_pvt.delete_template(
1923 1.0, FND_API.G_TRUE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL,
1924 l_return_status, l_msg_count, l_msg_data, instance_rec.instance_ac_id);
1925 END LOOP;
1926 END IF;
1927
1928 -- Mark for delete all instances that are still active,
1929 IF curr_instance_options_in = 'DELETE_CURR_INSTANCE' THEN
1930
1931 -- ABUDNIK B4558985 09Oct2005
1932 CLEAN_ACTIVE_INSTANCE(ac_id_in);
1933
1934 UPDATE zpb_analysis_cycles
1935 SET status_code = 'MARKED_FOR_DELETION',
1936 LAST_UPDATED_BY = fnd_global.USER_ID,
1937 LAST_UPDATE_DATE = SYSDATE,
1938 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1939 WHERE analysis_cycle_id in
1940 (select instance_ac_id
1941 from zpb_analysis_cycle_instances aci,zpb_analysis_cycles ac,
1942 zpb_analysis_cycles currinst
1943 where currinst.ANALYSIS_CYCLE_ID =ac_id_in
1944 and currinst.current_instance_id=ac.current_instance_id
1945 and ac.analysis_cycle_id=aci.instance_ac_id
1946 and ac.status_code NOT IN ('COMPLETE','ERROR','COMPLETE_WITH_WARNING'));
1947
1948 -- Clean up Current Instance Measure if Appropriate
1949 ZPB_WF.DeleteCurrInstMeas(ac_id_in, ownerid);
1950
1951 -- now delete any Data Collection templates
1952 -- associated with this cycle
1953 zpb_dc_objects_pvt.delete_template(
1954 1.0, FND_API.G_TRUE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL,
1955 l_return_status, l_msg_count, l_msg_data, ac_id_in);
1956
1957 END IF;
1958
1959 END mark_cycle_for_delete;
1960
1961 --
1962 -- This procedure updates the current_modified flag in zpb_solve_member_defs.
1963 -- The flag is used to indicate that the input_levels OR the
1964 -- calc definition was changed when the BP was made effective again
1965 -- The flag will be used by the Solve task of an active instance to
1966 -- determine if it should start the Solve from the very beginning or not
1967 --
1968 PROCEDURE update_solve_definition_flag(
1969 editable_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
1970 published_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE)
1971 IS
1972 cursor input_levels_cur(published_ac_id_in IN integer ,
1973 editable_ac_id_in in integer) IS
1974 select edt.member, edt.dimension, edt.input_level
1975 from zpb_solve_input_levels edt
1976 where edt.analysis_cycle_id = editable_ac_id_in
1977 MINUS
1981
1978 select pub.member, pub.dimension, pub.input_level
1979 from zpb_solve_input_levels pub
1980 where pub.analysis_cycle_id= published_ac_id_in;
1982 begin
1983
1984 --
1985 -- find all members whose calc definition was changed.
1986 --
1987 update zpb_solve_member_defs edt
1988 set current_modified = 'Y'
1989 where edt.analysis_cycle_id = editable_ac_id_in
1990 and edt.source_type = 1200
1991 and 0 <> (select dbms_lob.compare(edt.model_equation, pub.model_equation)
1992 from zpb_solve_member_defs pub
1993 where pub.analysis_cycle_id = published_ac_id_in
1994 and pub.member = edt.member
1995 and pub.source_type = 1200);
1996
1997 --
1998 -- now find all members whose input levels were changed
1999 --
2000 for each in input_levels_cur(published_ac_id_in,editable_ac_id_in) loop
2001 update zpb_solve_member_defs
2002 set current_modified = 'Y'
2003 where analysis_cycle_id = editable_ac_id_in
2004 and member = each.member;
2005 end loop;
2006
2007 end update_solve_definition_flag;
2008
2009 PROCEDURE publish_cycle (
2010 editable_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
2011 published_by_in IN zpb_analysis_cycles.published_by%TYPE,
2012 publish_options_in IN VARCHAR2,
2013 published_ac_id_out OUT NOCOPY zpb_analysis_cycles.analysis_cycle_id%TYPE)
2014 IS
2015 l_dummy VARCHAR2(4000);
2016 BEGIN
2017 -- call the overloaded procedure marking it as a non external event.
2018 publish_cycle(
2019 editable_ac_id_in => editable_ac_id_in
2020 ,published_by_in => published_by_in
2021 ,publish_options_in => publish_options_in
2022 ,p_external => 'N'
2023 ,p_bp_name_in => null
2024 ,p_start_mem_in => null
2025 ,p_end_mem_in => null
2026 ,p_send_date_in => null
2027 ,published_ac_id_out => published_ac_id_out
2028 ,x_item_key_out => l_dummy
2029 );
2030 END publish_cycle;
2031
2032 -- This procedure will convert the EDITABLE_AC_ID into PUBLISHED_AC_ID.
2033 -- Old published_ac_id will be deleted. All the related ACTIVE
2034 -- instances in ZPB_ANALYSIS_CYCLE_INSTANCES will also be updated
2035 -- with the latest definition of the BP incase user chooses to update
2036 -- current runs.
2037
2038
2039 PROCEDURE publish_cycle (
2040 editable_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
2041 published_by_in IN zpb_analysis_cycles.published_by%TYPE,
2042 publish_options_in IN VARCHAR2,
2043 p_bp_name_in IN VARCHAR2,
2044 p_external IN VARCHAR2,
2045 p_start_mem_in IN VARCHAR2,
2046 p_end_mem_in IN VARCHAR2,
2047 p_send_date_in IN DATE,
2048 published_ac_id_out OUT NOCOPY zpb_analysis_cycles.analysis_cycle_id%TYPE,
2049 x_item_key_out OUT NOCOPY VARCHAR2)
2050 IS
2051 old_published_ac_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
2052 ac_rec zpb_analysis_cycles%ROWTYPE;
2053 pet_row_rec zpb_cycle_relationships%ROWTYPE;
2054 published_before VARCHAR2(1);
2055 published_before_status VARCHAR2(30);
2056 enable_option varchar2(30);
2057
2058
2059 /* select the instances of this BP that are still active
2060 these will be updated with the new definition below */
2061
2062 CURSOR instance_cur IS
2063 SELECT zaci.instance_ac_id, zaci.analysis_cycle_id
2064 FROM ZPB_ANALYSIS_CYCLE_INSTANCES zaci,
2065 ZPB_ANALYSIS_CYCLES zac
2066 WHERE zaci.analysis_cycle_id = editable_ac_id_in and
2067 zaci.instance_ac_id = zac.analysis_cycle_id and
2068 zac.status_code not in('COMPLETE', 'COMPLETE_WITH_WARNING', 'DISABLE_ASAP', 'ERROR', 'MARKED_FOR_DELETION');
2069
2070 BEGIN
2071
2072 IF (p_external = 'Y')
2073 THEN
2074 old_published_ac_id := editable_ac_id_in;
2075
2076 ELSE
2077
2078 SELECT *
2079 INTO pet_row_rec
2080 FROM zpb_cycle_relationships
2081 WHERE editable_ac_id = editable_ac_id_in;
2082
2083 IF pet_row_rec.published_ac_id IS NULL THEN
2084 -- SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO new_ac_id FROM DUAL;
2085 old_published_ac_id := -1;
2086 published_before := 'N';
2087
2088 ELSE
2089
2090 -- If previously published BP was in disable status, enable it here
2091
2092 begin
2093
2094 select STATUS_CODE into published_before_status
2095 from ZPB_ANALYSIS_CYCLES
2096 where analysis_cycle_id=pet_row_rec.published_ac_id;
2097
2098 exception
2099 when NO_DATA_FOUND then
2100 published_before_status:='NO DEF FOUND';
2101 end;
2102
2103
2104 if published_before_status = 'DISABLE_ASAP' then
2105
2106 -- "Translate" the make effective option to analogous enable option
2107 if publish_options_in = 'UPDATE_FOR_CURRENT' then
2108 enable_option:= 'ENABLE_TASK';
2109 end if;
2110
2111 if publish_options_in = 'UPDATE_FOR_FUTURE' then
2112 enable_option:= 'ENABLE_NEXT';
2113 end if;
2114
2118
2115 zpb_wf.enable_cycle(pet_row_rec.published_ac_id, enable_option);
2116 end if;
2117
2119 --
2120 -- bug 3773258 - sk
2121 -- If the user is trying to update current runs then we have to check
2122 -- if he changed the calc definition or the input levels. If so then
2123 -- a solve flag has to be set.
2124 -- The simplest way to propagate this flag to older instances is to
2125 -- set them in the published_ac_id rows.
2126 --
2127 --
2128 -- The procedure below updates the flag in the EDITABLE_AC_ID rows !!!
2129 -- This is done because we are going to delete the original rows of
2130 -- published_ac_id and replacing them with rows of editable_ac_id.
2131 --
2132
2133 IF publish_options_in = 'UPDATE_FOR_CURRENT' THEN
2134 update_solve_definition_flag(editable_ac_id_in, pet_row_rec.published_ac_id);
2135 END IF;
2136
2137 -- delete_ac(pet_row_rec.published_ac_id);
2138
2139 old_published_ac_id := pet_row_rec.published_ac_id;
2140
2141 Update ZPB_ANALYSIS_CYCLES
2142 set STATUS_CODE = 'ENABLE_TASK_OLD'
2143 where ANALYSIS_CYCLE_ID = old_published_ac_id
2144 and STATUS_CODE = 'ENABLE_TASK';
2145
2146 published_before := 'Y';
2147 END IF;
2148
2149 /* UPDATE ZPB_ANALYSIS_CYCLE_INSTANCES
2150 SET Analysis_Cycle_Id = editable_ac_id_in
2151 WHERE Analysis_Cycle_Id = old_published_ac_id; */
2152
2153 -- UPDATE ZPB_DC_OBJECTS
2154 -- SET Analysis_Cycle_Id = editable_ac_id_in
2155 -- WHERE Analysis_Cycle_Id = old_published_ac_id;
2156
2157 END IF;
2158
2159 UPDATE zpb_cycle_relationships
2160 SET published_ac_id = editable_ac_id_in,
2161 editable_ac_id = null,
2162 LAST_UPDATED_BY = fnd_global.USER_ID,
2163 LAST_UPDATE_DATE = SYSDATE,
2164 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
2165 WHERE editable_ac_id = editable_ac_id_in;
2166
2167 /*
2168 * must set the cycle status to 'ENABLE_TASK' by default when
2169 * publishing the cycle
2170 */
2171
2172 UPDATE zpb_analysis_cycles
2173 SET published_by = published_by_in,
2174 published_date = sysdate,
2175 status_code = 'ENABLE_TASK',
2176 LAST_UPDATED_BY = fnd_global.USER_ID,
2177 LAST_UPDATE_DATE = SYSDATE,
2178 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
2179 WHERE analysis_cycle_id = editable_ac_id_in;
2180
2181 /*
2182 * If user chooses to apply publish changes already for current
2183 * cycles, then we must copy the cycle defition to all the
2184 * instance definitions.
2185 */
2186
2187 IF publish_options_in = 'UPDATE_FOR_CURRENT' THEN
2188
2189 FOR instance_rec IN instance_cur LOOP
2190
2191 SELECT *
2192 INTO ac_rec
2193 FROM zpb_analysis_cycles
2194 WHERE analysis_cycle_id = instance_rec.instance_ac_id;
2195
2196 delete_ac(instance_rec.instance_ac_id, FND_API.G_FALSE);
2197
2198 -- copy all parts - but only those tasks that have not yet started
2199
2200 copy_ac_table_rec(instance_rec.analysis_cycle_id, instance_rec.instance_ac_id);
2201 copy_ac_param_values_recs(instance_rec.analysis_cycle_id, instance_rec.instance_ac_id);
2202 copy_cycle_currency_recs(instance_rec.analysis_cycle_id, instance_rec.instance_ac_id);
2203 copy_external_user_recs(instance_rec.analysis_cycle_id, instance_rec.instance_ac_id);
2204 copy_cycle_datasets_recs(instance_rec.analysis_cycle_id, instance_rec.instance_ac_id);
2205
2206 copy_nonstarted_tasks(instance_rec.analysis_cycle_id, instance_rec.instance_ac_id);
2207
2208
2209 copy_cycle_model_dim_recs(instance_rec.analysis_cycle_id, instance_rec.instance_ac_id);
2210 copy_cycle_comments_recs(instance_rec.analysis_cycle_id, instance_rec.instance_ac_id);
2211 copy_solve_member_defs_recs(instance_rec.analysis_cycle_id, instance_rec.instance_ac_id);
2212 copy_data_init_defs_recs(instance_rec.analysis_cycle_id, instance_rec.instance_ac_id);
2213 copy_solve_input_level_recs(instance_rec.analysis_cycle_id, instance_rec.instance_ac_id);
2214 copy_solve_output_level_recs(instance_rec.analysis_cycle_id, instance_rec.instance_ac_id);
2215 copy_solve_alloc_defs_recs(instance_rec.analysis_cycle_id, instance_rec.instance_ac_id);
2216 copy_line_dimensionality_recs(instance_rec.analysis_cycle_id, instance_rec.instance_ac_id);
2217 -- Bug 4587184: Remove the following code, because they are invoked from
2218 -- copy_nonstarted_tasks now.
2219 -- copy_bp_scope_access_recs(instance_rec.analysis_cycle_id, instance_rec.instance_ac_id);
2220 -- copy_bp_measure_scope_recs(instance_rec.analysis_cycle_id, instance_rec.instance_ac_id);
2221 copy_solve_hier_order_recs(instance_rec.analysis_cycle_id, instance_rec.instance_ac_id);
2222
2223 -- Set the status and name of the instance back to what it was before copy
2224 -- most likely ACTIVE
2225
2226 UPDATE zpb_analysis_cycles
2227 SET LAST_UPDATED_BY = fnd_global.USER_ID,
2228 LAST_UPDATE_DATE = SYSDATE,
2229 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID,
2230 STATUS_CODE = ac_rec.status_code,
2231 PREV_STATUS_CODE = ac_rec.prev_status_code,
2232 NAME = ac_rec.name
2233 WHERE analysis_cycle_id = instance_rec.instance_ac_id;
2234 END LOOP;
2235 END IF;
2236
2237 --BPEXT
2241 , p_start_mem => p_start_mem_in
2238 --update the horizon params if this is a ext published
2239 IF (p_external = 'Y') THEN
2240 zpb_wf_event.acstart_event( acid => editable_ac_id_in
2242 , p_end_mem => p_end_mem_in
2243 , p_send_date => p_send_date_in
2244 , x_event_key => x_item_key_out);
2245 ELSE
2246
2247 zpb_wf.acstart(editable_ac_id_in, published_before);
2248 END IF;
2249
2250 published_ac_id_out := editable_ac_id_in;
2251 END publish_cycle;
2252
2253 PROCEDURE save_tmp_cycle (
2254 tmp_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
2255 last_updated_by_in IN zpb_analysis_cycles.last_updated_by%TYPE,
2256 lock_val_in IN zpb_analysis_cycles.locked_by%TYPE,
2257 lock_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
2258 x_return_status OUT NOCOPY VARCHAR2 ,
2259 x_msg_count OUT NOCOPY NUMBER,
2260 x_msg_data OUT NOCOPY VARCHAR2,
2261 editable_ac_id_out OUT NOCOPY zpb_analysis_cycles.analysis_cycle_id%TYPE)
2262
2263 IS
2264 new_ac_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
2265 pet_row_rec zpb_cycle_relationships%ROWTYPE;
2266 lock_val zpb_analysis_cycles.locked_by%TYPE;
2267 msg_data VARCHAR2(100);
2268 CURSOR lock_cursor is SELECT locked_by FROM zpb_analysis_cycles
2269 where analysis_cycle_id = lock_ac_id_in FOR UPDATE;
2270
2271 BEGIN
2272
2273 IF lock_ac_id_in IS NOT NULL THEN
2274 OPEN lock_cursor;
2275 FETCH lock_cursor into lock_val;
2276
2277 IF lock_val <> lock_val_in THEN
2278 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_BUS_PROC_LOCKED');
2279 x_msg_data := 'ZPB_BUS_PROC_LOCKED';
2280 FND_MSG_PUB.ADD;
2281 RAISE FND_API.G_EXC_ERROR;
2282 END IF;
2283 END IF;
2284 SELECT *
2285 INTO pet_row_rec
2286 FROM zpb_cycle_relationships
2287 WHERE tmp_ac_id = tmp_ac_id_in;
2288
2289 IF pet_row_rec.editable_ac_id IS NULL THEN
2290 SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO new_ac_id FROM DUAL;
2291 ELSE
2292 delete_ac(pet_row_rec.editable_ac_id);
2293 new_ac_id := pet_row_rec.editable_ac_id;
2294 END IF;
2295
2296 create_ac_copy(tmp_ac_id_in, new_ac_id);
2297
2298 UPDATE zpb_cycle_relationships
2299 SET editable_ac_id = new_ac_id,
2300 LAST_UPDATED_BY = fnd_global.USER_ID,
2301 LAST_UPDATE_DATE = SYSDATE,
2302 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
2303 WHERE tmp_ac_id = tmp_ac_id_in;
2304
2305 delete_ac(tmp_ac_id_in);
2306
2307 UPDATE zpb_analysis_cycles
2308 SET locked_by = nvl(locked_by,0)+1,
2309 LAST_UPDATED_BY = fnd_global.USER_ID,
2310 LAST_UPDATE_DATE = SYSDATE,
2311 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
2312 where analysis_cycle_id = new_ac_id;
2313
2314 IF lock_cursor%ISOPEN THEN
2315 close lock_cursor;
2316 END IF;
2317
2318 editable_ac_id_out := new_ac_id;
2319
2320 EXCEPTION
2321 WHEN FND_API.G_EXC_ERROR THEN
2322 x_return_status := FND_API.G_RET_STS_ERROR;
2323 editable_ac_id_out := null;
2324 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2325 p_data => msg_data );
2326 IF lock_cursor%ISOPEN THEN
2327 CLOSE lock_cursor;
2328 END IF;
2329 END save_tmp_cycle;
2330
2331
2332 /*
2333 * This procedure returns the type of the cycle
2334 * (i.e. PUBLISHED, EDITABLE_COPY, UNPUBLISHED)
2335 * based on its cycle ID
2336 */
2337
2338 PROCEDURE get_cycle_type (
2339 ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
2340 cycle_type_out OUT NOCOPY VARCHAR2)
2341 IS
2342 pet_row_rec zpb_cycle_relationships%ROWTYPE;
2343 BEGIN
2344 /*
2345 * We are guaranteed to have only one row returned because of
2346 * the design of the ZPB_CYCLE_RELATIONSHIPS table.
2347 */
2348 SELECT *
2349 INTO pet_row_rec
2350 FROM zpb_cycle_relationships
2351 WHERE tmp_ac_id = ac_id_in or
2352 published_ac_id = ac_id_in or
2353 editable_ac_id = ac_id_in;
2354
2355 IF pet_row_rec.published_ac_id = ac_id_in THEN
2356 cycle_type_out := 'PUBLISHED';
2357 ELSE
2358 IF pet_row_rec.editable_ac_id = ac_id_in THEN
2359 IF pet_row_rec.published_ac_id IS NULL THEN
2360 cycle_type_out := 'UNPUBLISHED';
2361 ELSE
2362 cycle_type_out := 'EDITABLE_COPY';
2363 END IF;
2364 END IF;
2365 END IF;
2366
2367 END get_cycle_type;
2368
2369 /*
2370 * This procedure returns the status of the cycle
2371 * based on its cycle ID
2372 */
2373
2374 PROCEDURE get_cycle_status (
2375 ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
2376 cycle_status_out OUT NOCOPY VARCHAR2)
2377 IS
2378
2379 BEGIN
2380
2381 begin
2382
2383 SELECT status_code
2384 INTO cycle_status_out
2385 FROM zpb_analysis_cycles
2386 WHERE analysis_cycle_id=ac_id_in;
2387
2391 end;
2388 exception
2389 when no_data_found then
2390 cycle_status_out:='CYCLENOTFOUND';
2392
2393 END get_cycle_status;
2394
2395 PROCEDURE get_lock_value (
2396 ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
2397 lock_value_out OUT NOCOPY NUMBER)
2398 IS
2399
2400 BEGIN
2401
2402 begin
2403
2404 SELECT locked_by
2405 INTO lock_value_out
2406 FROM zpb_analysis_cycles
2407 WHERE analysis_cycle_id=ac_id_in;
2408
2409 exception
2410 when no_data_found then
2411 lock_value_out:= -1;
2412 end;
2413
2414 END get_lock_value;
2415
2416 /*
2417 * This procedure enables the cycle identified by
2418 * <ac_id_in> based on the enable status passed into the UI.
2419 */
2420 PROCEDURE enable_cycle (
2421 ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
2422 enable_status_in IN VARCHAR2)
2423 IS
2424 BEGIN
2425
2426 -- simple wrapper around the zpb_wf procedure of the same name
2427 zpb_wf.enable_cycle(ac_id_in, enable_status_in);
2428
2429 END enable_cycle;
2430
2431 /*
2432 * This function returns 1 if this temp ac id is a draft of a published cycle
2433 * <p_tmp_ac_id>
2434 */
2435 FUNCTION isTmpDraftOfPublishedBP(p_tmp_ac_id IN zpb_analysis_cycles.analysis_cycle_id%TYPE) RETURN NUMBER IS
2436 CURSOR c_draft(cp_tmp_ac_id IN zpb_analysis_cycles.analysis_cycle_id%TYPE) IS
2437 SELECT 1 FROM zpb_cycle_relationships
2438 WHERE TMP_AC_ID = cp_tmp_ac_id AND published_ac_id IS NOT NULL;
2439 l_ret NUMBER(2);
2440 BEGIN
2441 OPEN c_draft(cp_tmp_ac_id => p_tmp_ac_id);
2442 FETCH c_draft INTO l_ret;
2443 CLOSE c_draft;
2444 RETURN NVL(l_ret,0);
2445 EXCEPTION
2446 WHEN OTHERS THEN
2447 IF (c_draft%ISOPEN) THEN
2448 CLOSE c_draft;
2449 END IF;
2450 RETURN 0;
2451 END isTmpDraftOfPublishedBP;
2452
2453 /*
2454 This function will return a unique default BP Name when the
2455 duplication of business process is invoked.
2456 Assumption(s):
2457 Maximum length of the name of a BP is 300.
2458 */
2459 FUNCTION getUniqueName(p_bus_area_id IN zpb_analysis_cycles.business_area_id%TYPE,
2460 p_cycle_name IN varchar2)RETURN VARCHAR IS
2461
2462 cursor unique_name_cur(cycle_name zpb_analysis_cycles.name%type)
2463 is
2464 select 1 from zpb_analysis_cycles where
2465 lower(name) = lower(cycle_name)
2466 and status_code <> 'MARKED_FOR_DELETION'
2467 and business_area_id = p_bus_area_id;
2468
2469 orig_name varchar2(500);
2470 new_bp_name zpb_analysis_cycles.name%TYPE;
2471 old_name zpb_analysis_cycles.name%type;
2472 temp number := null;
2473 mycount number := 1;
2474
2475 BEGIN
2476 orig_name := p_cycle_name;
2477 if length(p_cycle_name) > 300 then
2478 orig_name := substr(p_cycle_name,1,300);
2479 end if;
2480
2481 new_bp_name := orig_name;
2482 old_name := orig_name;
2483 loop
2484 if unique_name_cur%isopen then
2485 close unique_name_cur;
2486 end if;
2487 open unique_name_cur(new_bp_name);
2488 fetch unique_name_cur into temp;
2489 exit when unique_name_cur%notfound;
2490 if length(old_name) > 300 then
2491 new_bp_name := substr(old_name,1, length(old_name)- (length(mycount) + 1)) || '_' || mycount;
2492 elsif (length(old_name) + length(mycount) + 1) > 300 then
2493 new_bp_name := substr(old_name,1, 300 - (length(mycount) + 1)) || '_' || mycount;
2494 else
2495 new_bp_name := old_name || '_' || mycount;
2496 end if;
2497 mycount := mycount + 1;
2498 end loop;
2499 if unique_name_cur%isopen then
2500 close unique_name_cur;
2501 end if;
2502
2503 return new_bp_name;
2504 EXCEPTION
2505 when others then
2506 if unique_name_cur%isopen
2507 then
2508 close unique_name_cur;
2509 end if;
2510 return null;
2511 END getUniqueName;
2512
2513 procedure create_ac_param_values(p_ac_id in number,
2514 p_param_id in number,
2515 p_value in varchar2,
2516 p_apps_user_id in number) AS
2517 ac_param_rec zpb_ac_param_values%rowtype;
2518 begin
2519 ac_param_rec.analysis_cycle_id := p_ac_id;
2520 ac_param_rec.param_id := p_param_id;
2521 ac_param_rec.value := p_value;
2522 ac_param_rec.CREATED_BY := p_apps_user_id;
2523 ac_param_rec.CREATION_DATE := SYSDATE;
2524 ac_param_rec.LAST_UPDATED_BY := p_apps_user_id;
2525 ac_param_rec.LAST_UPDATE_DATE := SYSDATE;
2526 ac_param_rec.LAST_UPDATE_LOGIN := p_apps_user_id;
2527
2528 INSERT INTO zpb_ac_param_values(ANALYSIS_CYCLE_ID,
2529 PARAM_ID,
2530 VALUE,
2531 LAST_UPDATE_LOGIN,
2532 LAST_UPDATE_DATE,
2533 LAST_UPDATED_BY,
2534 CREATION_DATE,
2535 CREATED_BY)
2536 VALUES (ac_param_rec.ANALYSIS_CYCLE_ID,
2537 ac_param_rec.PARAM_ID,
2538 ac_param_rec.VALUE,
2539 ac_param_rec.LAST_UPDATE_LOGIN,
2540 ac_param_rec.LAST_UPDATE_DATE,
2541 ac_param_rec.LAST_UPDATED_BY,
2545
2542 ac_param_rec.CREATION_DATE,
2543 ac_param_rec.CREATED_BY);
2544 end create_ac_param_values;
2546
2547 procedure create_cycle_model_dimensions( p_ac_id in number,
2548 dimension_list in varchar2,
2549 p_apps_user_id in number) AS
2550
2551 md_rec zpb_cycle_model_dimensions%rowtype;
2552 dimension varchar2(100);
2553 i integer;
2554 j integer;
2555
2556 begin
2557 md_rec.analysis_cycle_id := p_ac_id;
2558 md_rec.CREATED_BY := p_apps_user_id;
2559 md_rec.CREATION_DATE := SYSDATE;
2560 md_rec.LAST_UPDATED_BY := p_apps_user_id;
2561 md_rec.LAST_UPDATE_DATE := SYSDATE;
2562 md_rec.LAST_UPDATE_LOGIN := p_apps_user_id;
2563
2564 i:= 1;
2565 loop
2566 j := instr(dimension_list, ':',i);
2567 if (j = 0) then
2568 dimension := substr(dimension_list, i);
2569 else
2570 dimension := substr(dimension_list, i,j - i);
2571 i := j + 1;
2572 end if;
2573 md_rec.dimension_name := dimension;
2574 INSERT INTO zpb_cycle_model_dimensions(ANALYSIS_CYCLE_ID,
2575 DIMENSION_NAME,
2576 QUERY_OBJECT_NAME,
2577 QUERY_OBJECT_PATH,
2578 LAST_UPDATE_LOGIN,
2579 LAST_UPDATE_DATE,
2580 LAST_UPDATED_BY,
2581 CREATION_DATE,
2582 CREATED_BY,
2583 DATASET_DIMENSION_FLAG,
2584 REMOVE_DIMENSION_FLAG,
2585 SUM_MEMBERS_NUMBER,
2586 SUM_SELECTION_NAME,
2587 SUM_SELECTION_PATH)
2588 VALUES (md_rec.ANALYSIS_CYCLE_ID,
2589 md_rec.DIMENSION_NAME,
2590 md_rec.QUERY_OBJECT_NAME,
2591 md_rec.QUERY_OBJECT_PATH,
2592 md_rec.LAST_UPDATE_LOGIN,
2593 md_rec.LAST_UPDATE_DATE,
2594 md_rec.LAST_UPDATED_BY,
2595 md_rec.CREATION_DATE,
2596 md_rec.CREATED_BY,
2597 md_rec.DATASET_DIMENSION_FLAG,
2598 md_rec.REMOVE_DIMENSION_FLAG,
2599 md_rec.SUM_MEMBERS_NUMBER,
2600 md_rec.SUM_SELECTION_NAME,
2601 md_rec.SUM_SELECTION_PATH);
2602 exit when j = 0;
2603 end loop;
2604 exception
2605 when others then
2606 raise;
2607 end create_cycle_model_dimensions;
2608
2609 procedure create_datasets( p_ac_id in number,
2610 dataset_list in varchar2,
2611 p_apps_user_id in number) AS
2612
2613 ds_rec zpb_cycle_datasets%rowtype;
2614 order_id zpb_cycle_datasets.order_id%type;
2615 dataset varchar2(100);
2616 i integer;
2617 j integer;
2618
2619 begin
2620 ds_rec.analysis_cycle_id := p_ac_id;
2621 ds_rec.CREATED_BY := p_apps_user_id ;
2622 ds_rec.CREATION_DATE := SYSDATE;
2623 ds_rec.LAST_UPDATED_BY := p_apps_user_id ;
2624 ds_rec.LAST_UPDATE_DATE := SYSDATE;
2625 ds_rec.LAST_UPDATE_LOGIN := p_apps_user_id ;
2626 order_id := 0;
2627
2628 i:= 1;
2629 loop
2630 j := instr(dataset_list, ':',i);
2631 if (j = 0) then
2632 dataset := substr(dataset_list, i);
2633 else
2634 dataset := substr(dataset_list, i, j - i);
2635 i := j + 1;
2636 end if;
2637 ds_rec.dataset_code := dataset;
2638 ds_rec.order_id := order_id;
2639 order_id := order_id + 1;
2640
2641 INSERT INTO zpb_cycle_datasets(ANALYSIS_CYCLE_ID,
2642 DATASET_CODE,
2643 ORDER_ID,
2644 LAST_UPDATE_LOGIN,
2645 LAST_UPDATE_DATE,
2646 LAST_UPDATED_BY,
2647 CREATION_DATE,
2648 CREATED_BY)
2649 VALUES (ds_rec.ANALYSIS_CYCLE_ID,
2650 ds_rec.DATASET_CODE,
2651 ds_rec.ORDER_ID,
2652 ds_rec.LAST_UPDATE_LOGIN,
2653 ds_rec.LAST_UPDATE_DATE,
2654 ds_rec.LAST_UPDATED_BY,
2655 ds_rec.CREATION_DATE,
2656 ds_rec.CREATED_BY);
2657 exit when j = 0;
2658 end loop;
2659 exception
2660 when others then
2661 raise;
2662 end create_datasets;
2663
2664
2665 procedure create_partial_cycle (
2666 p_api_version IN NUMBER,
2667 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2668 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2669 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2670 x_return_status OUT NOCOPY VARCHAR2 ,
2671 x_msg_count OUT NOCOPY NUMBER,
2672 x_msg_data OUT NOCOPY VARCHAR2,
2673 p_apps_user_id in number,
2674 p_cycle_name in varchar2,
2675 p_description in varchar2,
2676 p_appended in varchar2,
2677 p_calendar_start_type in varchar2,
2678 p_calendar_start_member in varchar2,
2679 p_calendar_start_periods in number,
2680 p_calendar_start_level in varchar2,
2684 p_calendar_end_periods in number,
2681 p_calendar_start_pf in varchar2,
2682 p_calendar_end_type in varchar2,
2683 p_calendar_end_member in varchar2,
2685 p_calendar_end_level in varchar2,
2686 p_calendar_end_pf in varchar2,
2687 p_model_dimensions in varchar2,
2688 p_versions in number,
2689 x_ac_id out nocopy number) as
2690
2691 l_api_name CONSTANT VARCHAR2(30) := 'create_partial_cycle';
2692 l_api_version CONSTANT NUMBER := 1.0;
2693 new_ac_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
2694 current_inst_id zpb_analysis_cycles.current_instance_id%TYPE;
2695 ac_rec zpb_analysis_cycles%ROWTYPE;
2696 pet_rec zpb_cycle_relationships%ROWTYPE;
2697 relationship_id zpb_cycle_relationships.relationship_id%TYPE;
2698 ac_param_rec zpb_ac_param_values%ROWTYPE;
2699 invalid_versions exception;
2700 invalid_calendar_start_type exception;
2701 invalid_calendar_end_type exception;
2702 invalid_calendar_start_pf exception;
2703 invalid_calendar_end_pf exception;
2704 invalid_appended exception;
2705
2706 begin
2707 -- Standard Start of API savepoint
2708 SAVEPOINT create_partial_cycle;
2709 -- Standard call to check for call compatibility.
2710 IF NOT FND_API.Compatible_API_Call( l_api_version,
2711 p_api_version,
2712 l_api_name,
2713 G_PKG_NAME)
2714 THEN
2715 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2716 END IF;
2717
2718 -- Initialize message list if p_init_msg_list is set to TRUE.
2719 IF FND_API.to_Boolean(p_init_msg_list) THEN
2720 FND_MSG_PUB.initialize;
2721 END IF;
2722
2723 -- Initialize API return status to success
2724 x_return_status := FND_API.G_RET_STS_SUCCESS;
2725 SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO new_ac_id FROM DUAL;
2726 SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO current_inst_id FROM DUAL;
2727 SELECT zpb_relationship_id_seq.NEXTVAL INTO relationship_id FROM DUAL;
2728
2729 ac_rec.analysis_cycle_id := new_ac_id;
2730 ac_rec.name := p_cycle_name;
2731 ac_rec.description := p_description;
2732 ac_rec.validate_status := 'INVALID';
2733 ac_rec.status_code := 'DISABLE_ASAP';
2734 ac_rec.current_instance_id := current_inst_id;
2735 ac_rec.CREATED_BY := p_apps_user_id;
2736 ac_rec.CREATION_DATE := SYSDATE;
2737 ac_rec.LAST_UPDATED_BY := p_apps_user_id;
2738 ac_rec.LAST_UPDATE_DATE := SYSDATE;
2739 ac_rec.LAST_UPDATE_LOGIN := p_apps_user_id;
2740 ac_rec.PUBLISHED_BY := p_apps_user_id;
2741
2742 INSERT INTO zpb_analysis_cycles(ANALYSIS_CYCLE_ID,
2743 STATUS_CODE,
2744 NAME,
2745 DESCRIPTION,
2746 LOCKED_BY,
2747 VALIDATE_STATUS,
2748 CURRENT_INSTANCE_ID,
2749 PUBLISHED_DATE,
2750 PUBLISHED_BY,
2751 LAST_UPDATE_DATE,
2752 LAST_UPDATED_BY,
2753 CREATION_DATE,
2754 CREATED_BY,
2755 PREV_STATUS_CODE,
2756 LAST_UPDATE_LOGIN,
2757 BUSINESS_AREA_ID,
2758 OWNER_ID)
2759 VALUES (ac_rec.ANALYSIS_CYCLE_ID,
2760 ac_rec.STATUS_CODE,
2761 ac_rec.NAME,
2762 ac_rec.DESCRIPTION,
2763 ac_rec.LOCKED_BY,
2764 ac_rec.VALIDATE_STATUS,
2765 ac_rec.CURRENT_INSTANCE_ID,
2766 ac_rec.PUBLISHED_DATE,
2767 ac_rec.PUBLISHED_BY,
2768 ac_rec.LAST_UPDATE_DATE,
2769 ac_rec.LAST_UPDATED_BY,
2770 ac_rec.CREATION_DATE,
2771 ac_rec.CREATED_BY,
2772 ac_rec.PREV_STATUS_CODE,
2773 ac_rec.LAST_UPDATE_LOGIN,
2774 ac_rec.BUSINESS_AREA_ID,
2775 ac_rec.OWNER_ID);
2776 pet_rec.relationship_id := relationship_id;
2777 pet_rec.editable_ac_id := new_ac_id;
2778 pet_rec.CREATED_BY := p_apps_user_id;
2779 pet_rec.CREATION_DATE := SYSDATE;
2780 pet_rec.LAST_UPDATED_BY := p_apps_user_id;
2781 pet_rec.LAST_UPDATE_DATE := SYSDATE;
2782 pet_rec.LAST_UPDATE_LOGIN := p_apps_user_id;
2783 INSERT INTO zpb_cycle_relationships(RELATIONSHIP_ID,
2784 PUBLISHED_AC_ID,
2785 EDITABLE_AC_ID,
2786 TMP_AC_ID,
2787 LAST_UPDATE_LOGIN,
2788 LAST_UPDATE_DATE,
2789 LAST_UPDATED_BY,
2790 CREATION_DATE,
2791 CREATED_BY)
2792 VALUES (pet_rec.RELATIONSHIP_ID,
2793 pet_rec.PUBLISHED_AC_ID,
2794 pet_rec.EDITABLE_AC_ID,
2795 pet_rec.TMP_AC_ID,
2796 pet_rec.LAST_UPDATE_LOGIN,
2797 pet_rec.LAST_UPDATE_DATE,
2798 pet_rec.LAST_UPDATED_BY,
2799 pet_rec.CREATION_DATE,
2803 -- now populate the AC param values
2800 pet_rec.CREATED_BY);
2801 ac_param_rec.analysis_cycle_id := new_ac_id;
2802
2804 if p_versions is not null then
2805 create_ac_param_values(new_ac_id,2,to_char(p_versions),p_apps_user_id);
2806 else
2807 raise invalid_versions;
2808 end if;
2809 create_ac_param_values(new_ac_id,3,to_char(sysdate),p_apps_user_id);
2810
2811 if p_calendar_start_type is null
2812 OR (p_calendar_start_type <> 'FIXED'
2813 AND p_calendar_start_type <> 'RELATIVE') then
2814 raise invalid_calendar_start_type;
2815 end if;
2816
2817 if (p_calendar_start_type = 'FIXED') then
2818 create_ac_param_values(new_ac_id,4,'FIXED_TIME',p_apps_user_id);
2819 else
2820 create_ac_param_values(new_ac_id,4,'NUMBER_OF_PERIODS',p_apps_user_id);
2821 end if;
2822
2823
2824 if p_calendar_start_member is not null then
2825 create_ac_param_values(new_ac_id,5,p_calendar_start_member,p_apps_user_id);
2826 end if;
2827
2828 if p_calendar_start_periods is not null then
2829 create_ac_param_values(new_ac_id,9,p_calendar_start_periods,p_apps_user_id);
2830 end if;
2831
2832 if p_calendar_start_level is not null then
2833 create_ac_param_values(new_ac_id,8,p_calendar_start_level,p_apps_user_id);
2834 end if;
2835
2836 if p_calendar_start_pf is not null then
2837 if (p_calendar_start_pf = 'PRIOR')
2838 OR (p_calendar_start_pf = 'FUTURE')
2839 OR (p_calendar_start_pf = 'CURRENT') then
2840 create_ac_param_values(new_ac_id,10,p_calendar_start_pf,p_apps_user_id);
2841 else
2842 raise invalid_calendar_start_pf;
2843 end if;
2844 end if;
2845
2846 if p_calendar_end_type is null
2847 OR (p_calendar_end_type <> 'FIXED'
2848 AND p_calendar_end_type <> 'RELATIVE') then
2849 raise invalid_calendar_end_type;
2850 end if;
2851
2852 if (p_calendar_end_type = 'FIXED') then
2853 create_ac_param_values(new_ac_id,11,'FIXED_TIME',p_apps_user_id);
2854 else
2855 create_ac_param_values(new_ac_id,11,'NUMBER_OF_PERIODS',p_apps_user_id);
2856 end if;
2857
2858 if p_calendar_end_member is not null then
2859 create_ac_param_values(new_ac_id,12,p_calendar_end_member,p_apps_user_id);
2860 end if;
2861
2862 if p_calendar_end_periods is not null then
2863 create_ac_param_values(new_ac_id,16,p_calendar_end_periods,p_apps_user_id);
2864 end if;
2865
2866 if p_calendar_end_level is not null then
2867 create_ac_param_values(new_ac_id,15,p_calendar_end_level,p_apps_user_id);
2868 end if;
2869
2870 if p_calendar_end_pf is not null then
2871 if (p_calendar_end_pf = 'PRIOR')
2872 OR (p_calendar_end_pf = 'FUTURE')
2873 OR (p_calendar_end_pf = 'CURRENT') then
2874 create_ac_param_values(new_ac_id,17,p_calendar_end_pf,p_apps_user_id);
2875 else
2876 raise invalid_calendar_end_pf;
2877 end if;
2878 end if;
2879 if p_appended is null OR (p_appended <> 'Y' AND p_appended <> 'N') then
2880 raise invalid_appended;
2881 else
2882 if p_appended = 'N' then
2883 create_ac_param_values(new_ac_id, 26,'DO_NOT_APPEND_VIEW',p_apps_user_id);
2884 else
2885 create_ac_param_values(new_ac_id, 26,'APPEND_VIEW',p_apps_user_id);
2886 end if;
2887 end if;
2888
2889
2890 create_cycle_model_dimensions(new_ac_id, p_model_dimensions,p_apps_user_id);
2891 -- Standard check of p_commit.
2892 IF FND_API.To_Boolean( p_commit ) THEN
2893 COMMIT WORK;
2894 END IF;
2895 -- Standard call to get message count and if count is 1, get message info.
2896 FND_MSG_PUB.Count_And_Get(
2897 p_count => x_msg_count,
2898 p_data => x_msg_data
2899 );
2900
2901 x_ac_id := new_ac_id;
2902 exception
2903 WHEN invalid_calendar_start_type then
2904 ROLLBACK TO create_partial_cycle;
2905 x_msg_count := 1;
2906 x_msg_data := 'Invalid calendar start type';
2907 x_return_status := FND_API.G_RET_STS_ERROR;
2908 WHEN invalid_calendar_end_type then
2909 ROLLBACK TO create_partial_cycle;
2910 x_return_status := FND_API.G_RET_STS_ERROR;
2911 x_msg_count := 1;
2912 x_msg_data := 'Invalid calendar end type';
2913 WHEN invalid_calendar_end_pf then
2914 ROLLBACK TO create_partial_cycle;
2915 x_msg_count := 1;
2916 x_msg_data := 'Invalid calendar end pf';
2917 x_return_status := FND_API.G_RET_STS_ERROR;
2918 WHEN invalid_calendar_start_pf then
2919 ROLLBACK TO create_partial_cycle;
2920 x_msg_count := 1;
2921 x_msg_data := 'Invalid calendar start pf';
2922 x_return_status := FND_API.G_RET_STS_ERROR;
2923 WHEN invalid_appended then
2924 ROLLBACK TO create_partial_cycle;
2925 x_msg_count := 1;
2926 x_msg_data := 'Invalid appended';
2927 x_return_status := FND_API.G_RET_STS_ERROR;
2928 WHEN invalid_versions then
2929 ROLLBACK TO create_partial_cycle;
2930 x_msg_count := 1;
2931 x_msg_data := 'Invalid versions';
2932 x_return_status := FND_API.G_RET_STS_ERROR;
2933 WHEN FND_API.G_EXC_ERROR THEN
2934 ROLLBACK TO create_partial_cycle;
2935 x_return_status := FND_API.G_RET_STS_ERROR;
2936 FND_MSG_PUB.Count_And_Get(
2940 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2937 p_count => x_msg_count,
2938 p_data => x_msg_data
2939 );
2941 ROLLBACK TO create_partial_cycle;
2942 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2943 FND_MSG_PUB.Count_And_Get(
2944 p_count => x_msg_count,
2945 p_data => x_msg_data
2946 );
2947 WHEN OTHERS THEN
2948 ROLLBACK TO create_partial_cycle;
2949 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2950 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
2951 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2952 FND_MSG_PUB.Add_Exc_Msg(
2953 G_PKG_NAME,
2954 l_api_name
2955 );
2956 END IF;
2957 FND_MSG_PUB.Count_And_Get(
2958 p_count => x_msg_count,
2959 p_data => x_msg_data
2960 );
2961 end create_partial_cycle;
2962
2963
2964 procedure create_task_parameters( task_id in number,
2965 name in varchar2,
2966 value in varchar,
2967 p_apps_user_id in number) AS
2968
2969 ac_task_param_rec zpb_task_parameters%rowtype;
2970 task_param_id zpb_task_parameters.param_id%type;
2971 begin
2972
2973 SELECT zpb_task_param_id_seq.NEXTVAL INTO task_param_id FROM DUAL;
2974
2975 ac_task_param_rec.task_id := task_id;
2976 ac_task_param_rec.param_id := task_param_id;
2977 ac_task_param_rec.name := name;
2978 ac_task_param_rec.value := value;
2979 ac_task_param_rec.CREATED_BY := p_apps_user_id;
2980 ac_task_param_rec.CREATION_DATE := SYSDATE;
2981 ac_task_param_rec.LAST_UPDATED_BY := p_apps_user_id;
2982 ac_task_param_rec.LAST_UPDATE_DATE := SYSDATE;
2983 ac_task_param_rec.LAST_UPDATE_LOGIN := p_apps_user_id;
2984 INSERT INTO zpb_task_parameters(NAME,
2985 TASK_ID,
2986 VALUE,
2987 PARAM_ID,
2988 LAST_UPDATE_LOGIN,
2989 LAST_UPDATE_DATE,
2990 LAST_UPDATED_BY,
2991 CREATION_DATE,
2992 CREATED_BY)
2993 VALUES (ac_task_param_rec.NAME,
2994 ac_task_param_rec.TASK_ID,
2995 ac_task_param_rec.VALUE,
2996 ac_task_param_rec.PARAM_ID,
2997 ac_task_param_rec.LAST_UPDATE_LOGIN,
2998 ac_task_param_rec.LAST_UPDATE_DATE,
2999 ac_task_param_rec.LAST_UPDATED_BY,
3000 ac_task_param_rec.CREATION_DATE,
3001 ac_task_param_rec.CREATED_BY);
3002 end create_task_parameters;
3003 procedure create_cycle_load_task( p_ac_id in number,
3004 p_apps_user_id in number) AS
3005
3006 load_task_id zpb_analysis_cycle_tasks.task_id%TYPE;
3007 task_param_id zpb_task_parameters.param_id%TYPE;
3008 ac_task_rec zpb_analysis_cycle_tasks%rowtype;
3009 begin
3010
3011 SELECT zpb_task_id_seq.NEXTVAL INTO load_task_id from dual;
3012
3013 ac_task_rec.analysis_cycle_id := p_ac_id;
3014 ac_task_rec.task_id := load_task_id;
3015 ac_task_rec.sequence := 1;
3016 ac_task_rec.ITEM_TYPE := 'EPBCYCLE';
3017 ac_task_rec.WF_PROCESS_NAME := 'LOAD_DATA';
3018 ac_task_rec.TASK_NAME := 'LOAD_DATA';
3019 ac_task_rec.CREATED_BY := p_apps_user_id;
3020 ac_task_rec.CREATION_DATE := SYSDATE;
3021 ac_task_rec.LAST_UPDATED_BY := p_apps_user_id;
3022 ac_task_rec.LAST_UPDATE_DATE := SYSDATE;
3023 ac_task_rec.LAST_UPDATE_LOGIN := p_apps_user_id;
3024
3025 INSERT INTO zpb_analysis_cycle_tasks(ANALYSIS_CYCLE_ID,
3026 TASK_ID,
3027 SEQUENCE,
3028 TASK_NAME,
3029 STATUS_CODE,
3030 ITEM_TYPE,
3031 WF_PROCESS_NAME,
3032 ITEM_KEY,
3033 START_DATE,
3034 HIDE_SHOW,
3035 CREATION_DATE,
3036 CREATED_BY,
3037 LAST_UPDATED_BY,
3038 LAST_UPDATE_DATE,
3039 LAST_UPDATE_LOGIN,
3040 OWNER_ID)
3041 VALUES (ac_task_rec.ANALYSIS_CYCLE_ID,
3042 ac_task_rec.TASK_ID,
3043 ac_task_rec.SEQUENCE,
3044 ac_task_rec.TASK_NAME,
3045 ac_task_rec.STATUS_CODE,
3046 ac_task_rec.ITEM_TYPE,
3047 ac_task_rec.WF_PROCESS_NAME,
3048 ac_task_rec.ITEM_KEY,
3049 ac_task_rec.START_DATE,
3050 ac_task_rec.HIDE_SHOW,
3051 ac_task_rec.CREATION_DATE,
3052 ac_task_rec.CREATED_BY,
3053 ac_task_rec.LAST_UPDATED_BY,
3054 ac_task_rec.LAST_UPDATE_DATE,
3055 ac_task_rec.LAST_UPDATE_LOGIN,
3056 ac_task_rec.OWNER_ID);
3057
3058
3059 create_task_parameters(load_task_id, 'DATA_SELECTION_TYPE', 'ALL_LINE_ITEMS_SELECTION_TYPE',p_apps_user_id);
3060 create_task_parameters(load_task_id, 'DATA_VALIDATION', 'DATA_VALIDATION',p_apps_user_id);
3064 create_task_parameters(load_task_id, 'OWNER_ID', to_char(p_apps_user_id),p_apps_user_id);
3061 create_task_parameters(load_task_id, 'LOAD_CHECK_INSIDE_INPUT_LEVELS', 'false',p_apps_user_id);
3062 create_task_parameters(load_task_id, 'LOAD_CHECK_OUTSIDE_INPUT_LEVELS', 'false',p_apps_user_id);
3063 create_task_parameters(load_task_id, 'NOTIFICATION_RECIPIENT_TYPE', 'OWNER_OF_AC',p_apps_user_id);
3065
3066 end create_cycle_load_task;
3067
3068
3069 procedure create_cycle_curinst_task( p_ac_id in number,
3070 p_apps_user_id in number) AS
3071
3072 curinst_task_id zpb_analysis_cycle_tasks.task_id%TYPE;
3073 task_param_id zpb_task_parameters.param_id%TYPE;
3074 ac_task_rec zpb_analysis_cycle_tasks%rowtype;
3075 begin
3076
3077 SELECT zpb_task_id_seq.NEXTVAL INTO curinst_task_id from dual;
3078
3079 ac_task_rec.analysis_cycle_id := p_ac_id;
3080 ac_task_rec.task_id := curinst_task_id;
3081 ac_task_rec.sequence := 2;
3082 ac_task_rec.ITEM_TYPE := 'EPBCYCLE';
3083 ac_task_rec.WF_PROCESS_NAME := 'SET_CURRENT_INSTANCE';
3084 ac_task_rec.TASK_NAME := 'Set Current Process Run';
3085 ac_task_rec.CREATED_BY := p_apps_user_id;
3086 ac_task_rec.CREATION_DATE := SYSDATE;
3087 ac_task_rec.LAST_UPDATED_BY := p_apps_user_id;
3088 ac_task_rec.LAST_UPDATE_DATE := SYSDATE;
3089 ac_task_rec.LAST_UPDATE_LOGIN := p_apps_user_id;
3090
3091 INSERT INTO zpb_analysis_cycle_tasks(ANALYSIS_CYCLE_ID,
3092 TASK_ID,
3093 SEQUENCE,
3094 TASK_NAME,
3095 STATUS_CODE,
3096 ITEM_TYPE,
3097 WF_PROCESS_NAME,
3098 ITEM_KEY,
3099 START_DATE,
3100 HIDE_SHOW,
3101 CREATION_DATE,
3102 CREATED_BY,
3103 LAST_UPDATED_BY,
3104 LAST_UPDATE_DATE,
3105 LAST_UPDATE_LOGIN,
3106 OWNER_ID)
3107 VALUES (ac_task_rec.ANALYSIS_CYCLE_ID,
3108 ac_task_rec.TASK_ID,
3109 ac_task_rec.SEQUENCE,
3110 ac_task_rec.TASK_NAME,
3111 ac_task_rec.STATUS_CODE,
3112 ac_task_rec.ITEM_TYPE,
3113 ac_task_rec.WF_PROCESS_NAME,
3114 ac_task_rec.ITEM_KEY,
3115 ac_task_rec.START_DATE,
3116 ac_task_rec.HIDE_SHOW,
3117 ac_task_rec.CREATION_DATE,
3118 ac_task_rec.CREATED_BY,
3119 ac_task_rec.LAST_UPDATED_BY,
3120 ac_task_rec.LAST_UPDATE_DATE,
3121 ac_task_rec.LAST_UPDATE_LOGIN,
3122 ac_task_rec.OWNER_ID);
3123 create_task_parameters(curinst_task_id, 'NOTIFICATION_RECIPIENT_TYPE', 'OWNER_OF_AC',p_apps_user_id);
3124 create_task_parameters(curinst_task_id, 'OWNER_ID', to_char(p_apps_user_id),p_apps_user_id);
3125
3126 end create_cycle_curinst_task;
3127
3128 procedure create_migrate_inst(
3129 p_api_version IN NUMBER,
3130 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3131 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3132 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3133 x_return_status OUT NOCOPY VARCHAR2 ,
3134 x_msg_count OUT NOCOPY NUMBER,
3135 x_msg_data OUT NOCOPY VARCHAR2,
3136 p_apps_user_id in NUMBER,
3137 p_analysis_cycle_id in NUMBER,
3138 p_view_name in varchar2,
3139 p_calendar_start_member in varchar2,
3140 p_calendar_end_member in varchar2,
3141 p_dataset in varchar2,
3142 p_current_instance in varchar2) AS
3143
3144 l_api_name CONSTANT VARCHAR2(30) := 'create_migrate_inst';
3145 l_api_version CONSTANT NUMBER := 1.0;
3146 instance_rec zpb_analysis_cycle_instances%ROWTYPE;
3147 new_ac_id zpb_analysis_cycles.analysis_cycle_id%TYPE;
3148 invalid_calendar_start_member exception;
3149 invalid_calendar_end_member exception;
3150 begin
3151
3152 -- Standard Start of API savepoint
3153 SAVEPOINT create_migrate_inst;
3154 -- Standard call to check for call compatibility.
3155 IF NOT FND_API.Compatible_API_Call( l_api_version,
3156 p_api_version,
3157 l_api_name,
3158 G_PKG_NAME)
3159 THEN
3160 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3161 END IF;
3162
3163 -- Initialize message list if p_init_msg_list is set to TRUE.
3164 IF FND_API.to_Boolean(p_init_msg_list) THEN
3165 FND_MSG_PUB.initialize;
3166 END IF;
3167
3168 -- Initialize API return status to success
3169 x_return_status := FND_API.G_RET_STS_SUCCESS;
3170
3171 SELECT zpb_analysis_cycle_id_seq.NEXTVAL INTO new_ac_id FROM DUAL;
3175
3172
3173 copy_ac_table_rec(p_analysis_cycle_id, new_ac_id);
3174 copy_cycle_model_dim_recs(p_analysis_cycle_id, new_ac_id);
3176 update zpb_cycle_model_dimensions set last_updated_by = p_apps_user_id,
3177 created_by = p_apps_user_id,
3178 last_update_login = p_apps_user_id
3179 where analysis_cycle_id = new_ac_id;
3180
3181 update zpb_analysis_cycles set status_code = 'PUBLISHED',
3182 validate_status = 'VALID',
3183 last_updated_by = p_apps_user_id,
3184 created_by = p_apps_user_id,
3185 last_update_login = p_apps_user_id
3186 where analysis_cycle_id = new_ac_id;
3187
3188 create_ac_param_values(new_ac_id,4,'FIXED_TIME',p_apps_user_id);
3189
3190 if p_calendar_start_member is not null then
3191 create_ac_param_values(new_ac_id,5,p_calendar_start_member,p_apps_user_id);
3192 else
3193 raise invalid_calendar_start_member;
3194 end if;
3195
3196 create_ac_param_values(new_ac_id,11,'FIXED_TIME',p_apps_user_id);
3197 create_ac_param_values(new_ac_id,25,'1',p_apps_user_id);
3198
3199 if p_calendar_end_member is not null then
3200 create_ac_param_values(new_ac_id,12,p_calendar_end_member,p_apps_user_id);
3201 else
3202 raise invalid_calendar_end_member;
3203 end if;
3204
3205 create_ac_param_values(new_ac_id,28,'Y',p_apps_user_id);
3206 -- note the dependency on the new dataset table
3207 create_datasets(new_ac_id,p_dataset,p_apps_user_id);
3208
3209 instance_rec.analysis_cycle_id := p_analysis_cycle_id;
3210 instance_rec.instance_ac_id := new_ac_id;
3211 instance_rec.instance_description := p_view_name;
3212 instance_rec.CREATED_BY := p_apps_user_id;
3213 instance_rec.CREATION_DATE := SYSDATE;
3214 instance_rec.LAST_UPDATED_BY := p_apps_user_id;
3215 instance_rec.LAST_UPDATE_DATE := SYSDATE;
3216 instance_rec.LAST_UPDATE_LOGIN := p_apps_user_id;
3217
3218 INSERT INTO zpb_analysis_cycle_instances(ANALYSIS_CYCLE_ID,
3219 INSTANCE_AC_ID,
3220 INSTANCE_DESCRIPTION,
3221 CREATION_DATE,
3222 CREATED_BY,
3223 LAST_UPDATED_BY,
3224 LAST_UPDATE_DATE,
3225 LAST_UPDATE_LOGIN,
3226 STATUS_CODE)
3227 VALUES (instance_rec.ANALYSIS_CYCLE_ID,
3228 instance_rec.INSTANCE_AC_ID,
3229 instance_rec.INSTANCE_DESCRIPTION,
3230 instance_rec.CREATION_DATE,
3231 instance_rec.CREATED_BY,
3232 instance_rec.LAST_UPDATED_BY,
3233 instance_rec.LAST_UPDATE_DATE,
3234 instance_rec.LAST_UPDATE_LOGIN,
3235 instance_rec.STATUS_CODE);
3236
3237 create_cycle_load_task(new_ac_id,p_apps_user_id);
3238
3239 if p_current_instance = 'Y' then
3240 create_cycle_curinst_task(new_ac_id,p_apps_user_id);
3241 end if;
3242
3243 -- Standard check of p_commit.
3244 IF FND_API.To_Boolean( p_commit ) THEN
3245 COMMIT WORK;
3246 END IF;
3247 -- Standard call to get message count and if count is 1, get message info.
3248 FND_MSG_PUB.Count_And_Get(
3249 p_count => x_msg_count,
3250 p_data => x_msg_data
3251 );
3252
3253 EXCEPTION
3254 WHEN invalid_calendar_start_member then
3255 ROLLBACK TO create_migrate_inst;
3256 x_msg_count := 1;
3257 x_msg_data := 'Invalid calendar start member';
3258 x_return_status := FND_API.G_RET_STS_ERROR;
3259 WHEN invalid_calendar_end_member then
3260 ROLLBACK TO create_migrate_inst;
3261 x_msg_count := 1;
3262 x_msg_data := 'Invalid calendar end member';
3263 x_return_status := FND_API.G_RET_STS_ERROR;
3264 WHEN FND_API.G_EXC_ERROR THEN
3265 ROLLBACK TO create_migrate_inst;
3266 x_return_status := FND_API.G_RET_STS_ERROR;
3267 FND_MSG_PUB.Count_And_Get(
3268 p_count => x_msg_count,
3269 p_data => x_msg_data
3270 );
3271 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3272 ROLLBACK TO create_migrate_inst;
3273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3274 FND_MSG_PUB.Count_And_Get(
3275 p_count => x_msg_count,
3276 p_data => x_msg_data
3277 );
3278 WHEN OTHERS THEN
3279 ROLLBACK TO create_migrate_inst;
3280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3281 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
3282 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3283 FND_MSG_PUB.Add_Exc_Msg(
3284 G_PKG_NAME,
3285 l_api_name
3286 );
3287 END IF;
3288 FND_MSG_PUB.Count_And_Get(
3289 p_count => x_msg_count,
3290 p_data => x_msg_data
3291 );
3292 end create_migrate_inst;
3293
3294 --
3295 -- The procedure is called to create hierarchy order for an analysis cycle.
3296 --
3297 PROCEDURE Create_Hier_Order
3298 (
3299 p_api_version IN NUMBER,
3303 x_return_status OUT NOCOPY VARCHAR2,
3300 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3301 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3302 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3304 x_msg_count OUT NOCOPY NUMBER,
3305 x_msg_data OUT NOCOPY VARCHAR2,
3306 p_analysis_cycle_id IN NUMBER
3307 )
3308
3309 AS
3310 l_api_name CONSTANT VARCHAR2(30) := 'Create_Hier_Order';
3311 l_api_version CONSTANT NUMBER := 1.0;
3312
3313 l_solve_hier_order_id NUMBER;
3314 l_dimension VARCHAR2(50);
3315 l_hierarchy VARCHAR2(50);
3316 l_last_order NUMBER;
3317 l_order_num NUMBER;
3318 l_prev_dim VARCHAR2(50);
3319 l_new_dim VARCHAR2(50);
3320 l_hierarchy_exists NUMBER;
3321 l_user_id NUMBER;
3322 l_login_id NUMBER;
3323 l_first_last_flag VARCHAR2(1);
3324 l_first_flag VARCHAR2(1);
3325 l_last_flag VARCHAR2(1);
3326 l_object_version_number NUMBER;
3327 l_hier_count NUMBER;
3328
3329 -- This cursor will get all the distinct Dim-Hier pairs having more that one
3330 -- output selection from table: zpb_solve_output_selections
3331 CURSOR l_get_hierarchies_csr IS
3332 SELECT DISTINCT b.dimension,
3333 b.hierarchy
3334 FROM zpb_solve_output_selections b,
3335 zpb_lab_hierarchies_v lab
3336 WHERE b.analysis_cycle_id = p_analysis_cycle_id
3337 AND b.hierarchy = lab.object_aw_name
3338 AND b.dimension = lab.dimension
3339 AND EXISTS
3340 (SELECT a.dimension,
3341 a.member,
3342 COUNT(a.hierarchy)
3343 FROM zpb_solve_output_selections a
3344 WHERE a.analysis_cycle_id = p_analysis_cycle_id
3345 AND a.dimension = b.dimension
3346 GROUP BY a.dimension, a.member
3347 HAVING COUNT(a.hierarchy) > l_hier_count )
3348 ORDER BY b.dimension, b.hierarchy;
3349
3350 -- This cursor will return 1 if the "ac_id - dim - hier" combination already
3351 -- exists in the table: zpb_solve_hier_order
3352 CURSOR l_hierarchy_exists_csr IS
3353 SELECT 1 hier_exists
3354 FROM zpb_solve_hier_order
3355 WHERE analysis_cycle_id = p_analysis_cycle_id
3356 AND dimension = l_dimension
3357 AND hierarchy = l_hierarchy;
3358
3359 -- This cursor will return the last used hier order for a "ac_id - Dim"
3360 -- combination in the table: zpb_solve_hier_order
3361 CURSOR l_get_last_order_csr is
3362 SELECT NVL(MAX(hierarchy_order),-1) max_order
3363 FROM zpb_solve_hier_order
3364 WHERE analysis_cycle_id = p_analysis_cycle_id
3365 AND dimension = l_dimension;
3366
3367 -- This cursor is used to cleanup the table: zpb_solve_hier_order
3368 -- by setting the first_last_flag correctly
3369 CURSOR l_final_csr IS
3370 SELECT dimension,
3371 MAX(hierarchy_order) max,
3372 MIN(hierarchy_order) min,
3373 COUNT(hierarchy) count
3374 FROM zpb_solve_hier_order
3375 WHERE analysis_cycle_id = p_analysis_cycle_id
3376 GROUP BY dimension;
3377
3378 BEGIN
3379
3380 SAVEPOINT Create_Hier_Order;
3381
3382 -- Standard call to check for call compatibility.
3383 IF NOT FND_API.Compatible_API_Call(l_api_version,
3384 p_api_version,
3385 l_api_name,
3386 G_PKG_NAME)
3387 THEN
3388 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3389 END IF;
3390
3391 -- Initialize message list if p_init_msg_list is set to TRUE.
3392 IF FND_API.to_Boolean(p_init_msg_list) THEN
3393 FND_MSG_PUB.initialize;
3394 END IF;
3395
3396 -- Initialize API return status to success
3397 x_return_status := FND_API.G_RET_STS_SUCCESS;
3398
3399 l_user_id := fnd_global.user_id;
3400 l_login_id := fnd_global.user_id;
3401 l_object_version_number := 1;
3402 l_hier_count := 1;
3403 l_first_last_flag := null;
3404
3405 -- set the first_last_flag to null initially, this cloumn will be updated
3406 -- again at the end of this function.
3407 UPDATE zpb_solve_hier_order
3408 SET first_last_flag = l_first_last_flag
3409 WHERE analysis_cycle_id = p_analysis_cycle_id;
3410
3411 -- set the previous dimension variable to -1 initially
3412 l_prev_dim := '-1';
3413
3414 FOR l_get_hierarchies_rec IN l_get_hierarchies_csr
3415 LOOP
3416 l_dimension := l_get_hierarchies_rec.dimension;
3417 l_hierarchy := l_get_hierarchies_rec.hierarchy;
3418
3419 -- set the new dimension variable
3420 l_new_dim := l_dimension;
3421
3422 -- check whether "ac_id - dim - hier" combination already exists.
3423 -- Value of l_hierarchy_exists = 0 if it does not exist
3424 -- Value of l_hierarchy_exists = 1 if it does exist
3425 l_hierarchy_exists := 0;
3426
3427 FOR l_hierarchy_exists_rec IN l_hierarchy_exists_csr
3428 LOOP
3429 l_hierarchy_exists := l_hierarchy_exists_rec.hier_exists;
3430 END LOOP;
3431
3432 -- insert the hierarchy in table: ZPB_SOLVE_HIER_ORDER only if it does not
3433 -- exist already in the table
3434 IF l_hierarchy_exists = 0
3435 THEN
3436 SELECT zpb_solve_hier_order_s.nextval INTO l_solve_hier_order_id
3437 FROM dual;
3438
3439 IF l_new_dim <> l_prev_dim
3440 THEN
3441 l_order_num := 0;
3442 l_last_order:= 0;
3443
3444 FOR l_get_last_order_rec in l_get_last_order_csr
3445 LOOP
3446 l_last_order := l_get_last_order_rec.max_order;
3447 END LOOP;
3448
3449 IF l_last_order = -1
3450 THEN
3451 l_order_num := 1;
3452 ELSE
3453 l_order_num := l_last_order + 1;
3454 END IF;
3455
3456 l_prev_dim := l_new_dim;
3457 ELSE
3458 l_order_num := l_order_num + 1;
3459 END IF; -- if l_new_dim <> l_prev_dim then
3460
3461 INSERT INTO ZPB_SOLVE_HIER_ORDER
3462 (SOLVE_HIER_ORDER_ID
3463 ,ANALYSIS_CYCLE_ID
3464 ,DIMENSION
3465 ,HIERARCHY
3466 ,HIERARCHY_ORDER
3467 ,FIRST_LAST_FLAG
3468 ,OBJECT_VERSION_NUMBER
3469 ,LAST_UPDATE_LOGIN
3470 ,LAST_UPDATE_DATE
3471 ,LAST_UPDATED_BY
3472 ,CREATION_DATE
3473 ,CREATED_BY)
3474 VALUES
3475 (l_solve_hier_order_id
3476 ,p_analysis_cycle_id
3477 ,l_dimension
3478 ,l_hierarchy
3479 ,l_order_num
3480 ,l_first_last_flag
3481 ,l_object_version_number
3482 ,l_login_id
3483 ,sysdate
3484 ,l_user_id
3485 ,sysdate
3486 ,l_user_id);
3487
3488 END IF; -- if l_hierarchy_exists = 0 then
3489 END LOOP; -- for l_get_hierarchies_rec in l_get_hierarchies_csr loop
3490
3491 -- Sync the o/p selections table with zpb_solve_hier_order table by deleting
3492 -- those hierarchies in zpb_solve_hier_order for which Hierarchy Order is
3493 -- not applicable
3494 DELETE FROM zpb_solve_hier_order
3495 WHERE analysis_cycle_id = p_analysis_cycle_id
3496 AND hierarchy NOT IN
3497 (SELECT b.hierarchy
3498 FROM zpb_solve_output_selections b,
3499 zpb_lab_hierarchies_v lab
3500 WHERE b.analysis_cycle_id = p_analysis_cycle_id
3501 AND b.hierarchy = lab.object_aw_name
3502 AND b.dimension = lab.dimension
3503 AND EXISTS
3504 (SELECT a.dimension,
3505 a.member,
3506 COUNT(a.hierarchy)
3507 FROM zpb_solve_output_selections a
3508 WHERE a.analysis_cycle_id = p_analysis_cycle_id
3509 AND a.dimension = b.dimension
3510 GROUP BY a.dimension, a.member
3511 HAVING COUNT(a.hierarchy) > l_hier_count
3512 )
3513 );
3514
3518 -- update first_last_flag of the max. order num with 'L'
3515 -- get the minimum and maximum order number for every "ac_id - dim"
3516 -- combination.
3517 -- update first_last_flag of the min. order num with 'F'
3519 l_first_flag := 'F';
3520 l_last_flag := 'L';
3521
3522 FOR l_final_rec in l_final_csr
3523 LOOP
3524 UPDATE zpb_solve_hier_order
3525 SET first_last_flag = l_first_flag
3526 WHERE analysis_cycle_id = p_analysis_cycle_id
3527 AND dimension = l_final_rec.dimension
3528 AND hierarchy_order = l_final_rec.min;
3529
3530 UPDATE zpb_solve_hier_order
3531 SET first_last_flag = l_last_flag
3532 WHERE analysis_cycle_id = p_analysis_cycle_id
3533 AND dimension = l_final_rec.dimension
3534 AND hierarchy_order = l_final_rec.max;
3535 END LOOP; -- for l_final_rec in l_final_csr loop
3536
3537 IF p_commit = FND_API.G_TRUE
3538 THEN
3539 COMMIT;
3540 END IF;
3541
3542 EXCEPTION
3543
3544 WHEN FND_API.G_EXC_ERROR
3545 THEN
3546 NULL;
3547 ROLLBACK TO Create_Hier_Order;
3548 x_return_status := FND_API.G_RET_STS_ERROR;
3549 FND_MSG_PUB.Count_And_Get(
3550 p_count => x_msg_count,
3551 p_data => x_msg_data
3552 );
3553
3554 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3555 THEN
3556 ROLLBACK TO Create_Hier_Order;
3557 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3558 FND_MSG_PUB.Count_And_Get(
3559 p_count => x_msg_count,
3560 p_data => x_msg_data
3561 );
3562
3563 WHEN OTHERS
3564 THEN
3565 ROLLBACK TO Create_Hier_Order;
3566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3567 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'
3568 || substr(sqlerrm,1,90));
3569 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3570 THEN
3571 FND_MSG_PUB.Add_Exc_Msg(
3572 G_PKG_NAME,
3573 l_api_name
3574 );
3575 END IF;
3576 FND_MSG_PUB.Count_And_Get(
3577 p_count => x_msg_count,
3578 p_data => x_msg_data
3579 );
3580 END Create_Hier_Order;
3581
3582 --
3583 -- This procedure is to retrieve instance_id based on the value of APPEND_VIEW
3584 -- parameter value.
3585 --
3586 -- added for bug 5436923
3587 PROCEDURE Get_VM_instance_id
3588 (
3589 p_api_version IN NUMBER,
3590 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3591 x_return_status OUT NOCOPY VARCHAR2,
3592 x_msg_count OUT NOCOPY NUMBER,
3593 x_msg_data OUT NOCOPY VARCHAR2,
3597 IS
3594 p_ac_id_in IN zpb_analysis_cycles.analysis_cycle_id%TYPE,
3595 x_vm_instance_id OUT NOCOPY NUMBER
3596 )
3598 l_api_name CONSTANT VARCHAR2(30) := 'Get_VM_instance_id';
3599 l_api_version CONSTANT NUMBER := 1.0;
3600
3601 l_param_value ZPB_AC_PARAM_VALUES.VALUE%TYPE;
3602
3603 BEGIN
3604
3605 -- Standard call to check for call compatibility.
3606 IF NOT FND_API.Compatible_API_Call(l_api_version,
3607 p_api_version,
3608 l_api_name,
3609 G_PKG_NAME)
3610 THEN
3611 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3612 END IF;
3613
3614 -- Initialize message list if p_init_msg_list is set to TRUE.
3615 IF FND_API.to_Boolean(p_init_msg_list) THEN
3616 FND_MSG_PUB.initialize;
3617 END IF;
3618
3619 -- Initialize API return status to success
3620 x_return_status := FND_API.G_RET_STS_SUCCESS;
3621
3622 SELECT value
3623 INTO l_param_value
3624 FROM ZPB_AC_PARAM_VALUES
3625 WHERE analysis_cycle_id = p_ac_id_in
3626 AND param_id =
3627 (SELECT tag
3628 FROM fnd_lookup_values_vl
3629 WHERE LOOKUP_TYPE = 'ZPB_PARAMS'
3630 AND LOOKUP_CODE = 'APPEND_VIEW');
3631
3632 IF (l_param_value= 'APPEND_VIEW')
3633 THEN
3634 SELECT CURRENT_INSTANCE_ID
3635 INTO x_vm_instance_id
3636 FROM ZPB_ANALYSIS_CYCLES
3637 WHERE ANALYSIS_CYCLE_ID = p_ac_id_in;
3638 ELSE
3639 x_vm_instance_id := p_ac_id_in;
3640 END IF;
3641
3642 EXCEPTION
3643
3644 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
3645 THEN
3646 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3647 FND_MSG_PUB.Count_And_Get(
3648 p_count => x_msg_count,
3649 p_data => x_msg_data
3650 );
3651
3652 WHEN others
3653 THEN
3654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3655 zpb_log.write_event(G_PKG_NAME||'.'||l_api_name,to_char(sqlcode) ||':'
3656 || substr(sqlerrm,1,90));
3657 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3661 l_api_name
3658 THEN
3659 FND_MSG_PUB.Add_Exc_Msg(
3660 G_PKG_NAME,
3662 );
3663 END IF;
3664 FND_MSG_PUB.Count_And_Get(
3665 p_count => x_msg_count,
3666 p_data => x_msg_data
3667 );
3668
3669 END Get_VM_instance_id;
3670
3671
3672 END ZPB_AC_OPS;