[Home] [Help]
PACKAGE BODY: APPS.FPA_MAIN_PROCESS_PVT
Source
1 PACKAGE BODY FPA_MAIN_PROCESS_PVT AS
2 /* $Header: FPAXWFMB.pls 120.11.12020000.3 2012/09/26 11:52:44 svmohamm ship $ */
3
4 -- Global variables defined for logic based on called from place parameter
5 -- Please update values assigned to this variables whenever any of these
6 -- procedure names are modified : GET_ALIST/GET_DLIST/GET_APPROVER/GET_ANALYST/GET_PC_MANAGERS
7
8 g_get_port_users VARCHAR2(30) := 'GET_ALIST';
9 g_get_pc_dlist VARCHAR2(30) := 'GET_DLIST';
10 g_get_port_apprv VARCHAR2(30) := 'GET_APPROVER';
11 g_get_port_analyst VARCHAR2(30) := 'GET_ANALYST';
12 g_get_pc_manager VARCHAR2(30) := 'GET_PC_MANAGERS';
13
14
15
16 TYPE PC_ATTRIBUTES_REC_TYPE is RECORD
17 (portfolio_name FPA_OBJECTS_TL.NAME%TYPE,
18 pc_name FPA_OBJECTS_TL.NAME%TYPE,
19 portfolio_type PA_CLASS_CODES.CLASS_CODE%TYPE,
20 inv_class_category PA_CLASS_CATEGORIES.CLASS_CATEGORY%TYPE,
21 cost_fin_plan PA_FIN_PLAN_TYPES_TL.NAME%TYPE,
22 benefit_fin_plan PA_FIN_PLAN_TYPES_TL.NAME%TYPE);
23
24
25
26 PROCEDURE GET_WF_ATTRIBUTES(
27 p_pc_id IN NUMBER,
28 x_pc_attributes_rec OUT NOCOPY PC_ATTRIBUTES_REC_TYPE,
29 x_return_status OUT NOCOPY VARCHAR2,
30 x_msg_count OUT NOCOPY NUMBER,
31 x_msg_data OUT NOCOPY VARCHAR2)
32 IS
33
34 l_return_status VARCHAR2(1) := null;
35 l_msg_count NUMBER := null;
36 l_msg_data VARCHAR2(2000) := null;
37
38 l_pc_attributes_rec PC_ATTRIBUTES_REC_TYPE;
39
40 CURSOR PC_ATTRIBUTES_CSR (C_PC_ID IN NUMBER) IS
41 SELECT
42 PRTL.NAME,
43 PCTL.NAME,
44 CC.CLASS_CODE,
45 PCC.CLASS_CATEGORY
46 FROM
47 FPA_AW_PC_INFO_V PCS,
48 FPA_AW_PORTF_HEADERS_V PRTF,
49 FPA_OBJECTS_TL PRTL,
50 FPA_OBJECTS_TL PCTL,
51 PA_CLASS_CATEGORIES PCC,
52 PA_CLASS_CODES CC
53 WHERE
54 PCTL.ID = PCS.PLANNING_CYCLE AND
55 PCTL.OBJECT = 'PLANNING_CYCLE' AND
56 PRTL.ID = PCS.PORTFOLIO AND
57 PRTL.OBJECT = 'PORTFOLIO' AND
58 PCS.PC_CATEGORY = PCC.CLASS_CATEGORY_ID AND
59 PCS.PORTFOLIO = PRTF.PORTFOLIO AND
60 PRTF.PORTFOLIO_CLASS_CODE = CC.CLASS_CODE_ID AND
61 PCS.PLANNING_CYCLE = C_PC_ID AND
62 PRTL.LANGUAGE = USERENV('LANG') AND -- Bug Ref # 6450301
63 PCTL.LANGUAGE = USERENV('LANG');
64
65 BEGIN
66
67 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
68 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
69 'fpa.sql.fpa_main_process_pvt.get_wf_attributes.begin',
70 'Entering fpa_main_process_pvt.get_wf_attributes');
71 END IF;
72
73 OPEN PC_ATTRIBUTES_CSR(P_PC_ID);
74 FETCH PC_ATTRIBUTES_CSR
75 INTO L_PC_ATTRIBUTES_REC.PORTFOLIO_NAME,
76 L_PC_ATTRIBUTES_REC.PC_NAME,
77 L_PC_ATTRIBUTES_REC.PORTFOLIO_TYPE,
78 L_PC_ATTRIBUTES_REC.INV_CLASS_CATEGORY;
79 CLOSE PC_ATTRIBUTES_CSR;
80
81
82 BEGIN
83 SELECT PLC.NAME, PLB.NAME
84 INTO L_PC_ATTRIBUTES_REC.COST_FIN_PLAN, L_PC_ATTRIBUTES_REC.BENEFIT_FIN_PLAN
85 FROM PA_FIN_PLAN_TYPES_TL PLC, PA_FIN_PLAN_TYPES_TL PLB
86 WHERE
87 FND_PROFILE.VALUE('PJP_FINANCIAL_PLAN_TYPE_COST') = PLC.FIN_PLAN_TYPE_ID AND
88 FND_PROFILE.VALUE('PJP_FINANCIAL_PLAN_TYPE_BENEFIT') = PLB.FIN_PLAN_TYPE_ID AND
89 PLC.LANGUAGE = USERENV('LANG') AND -- Bug Ref # 6450301
90 PLB.LANGUAGE = USERENV('LANG');
91 EXCEPTION
92 WHEN OTHERS THEN
93 NULL;
94 END;
95
96 x_pc_attributes_rec := l_pc_attributes_rec;
97
98 EXCEPTION
99 WHEN OTHERS THEN
100 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
101 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
102 'fpa.sql.fpa_main_process_pvt.get_wf_attributes.end',
103 SQLERRM);
104 END IF;
105 wf_core.context('FPA_MAIN_PROCESS_PVT', 'GET_WF_ATTRIBUTES', null);
106 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
107 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
108 p_data => x_msg_data);
109 RAISE;
110 END GET_WF_ATTRIBUTES;
111
112
113 -------------------------------------------------------------
114 --Start of Comments
115 --Name : INITIATE_WORKFLOW
116 --
117 --Pre-reqs : IN parameters need to be passed in with valid values
118 --
119 --Modifies : None
120 --
121 --Locks : None
122 --
123 --Function : This procedure sets up all necessary workflow
124 -- attributes needed before starting the workflow
125 -- process.
126 --
127 --Parameter(s):
128 --
129 --IN : p_pc_name IN VARCHAR2,
130 -- p_pc_id IN NUMBER,
131 -- p_pc_description IN VARCHAR2,
132 -- p_pc_date_initiated IN DATE,
133 -- p_due_date IN DATE
134 --
135 --IN OUT: : None
136 --
137 --OUT : x_return_status OUT NOCOPY VARCHAR2,
138 -- x_msg_count OUT NOCOPY NUMBER,
139 -- x_msg_data OUT NOCOPY VARCHAR2
140 --
141 --Returns : None
142 --
143 --Notes : None
144 --
145 --Testing : None
146 --
147 --End of Comments
148 -------------------------------------------------------------
149 PROCEDURE INITIATE_WORKFLOW(p_pc_name IN VARCHAR2,
150 p_pc_id IN NUMBER,
151 p_last_pc_id IN NUMBER,
152 p_pc_description IN VARCHAR2,
153 p_pc_date_initiated IN DATE,
154 p_due_date IN DATE,
155 x_return_status OUT NOCOPY VARCHAR2,
156 x_msg_count OUT NOCOPY NUMBER,
157 x_msg_data OUT NOCOPY VARCHAR2
158 )
159 IS
160
161 l_itemtype VARCHAR2(30) := null;
162 l_itemkey VARCHAR2(30) := null;
163 l_return_status VARCHAR2(1) := null;
164 l_msg_count NUMBER := null;
165 l_msg_data VARCHAR2(2000) := null;
166 l_WFProcess VARCHAR2(30) := 'FPA_INITIATE_PLANNING_CYCLE';
167
168 l_pc_attributes_rec PC_ATTRIBUTES_REC_TYPE;
169
170 BEGIN
171
172 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
173 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
174 'fpa.sql.fpa_main_process_pvt.initiate_workflow.begin',
175 'Entering fpa_main_process_pvt.initiate_workflow');
176 END IF;
177
178 l_itemtype := 'FPAPJP';
179
180 l_itemkey := p_pc_id;
181
182 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
183 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
184 'fpa.sql.fpa_main_process_pvt.initiate_workflow',
185 'Starting the master workflow process');
186 END IF;
187
188 -- wf_purge.items(l_itemtype,l_itemkey,sysdate,false,true);
189
190 -- Creates the workflow process
191 wf_engine.CreateProcess(itemtype => l_itemtype,
192 itemkey => l_itemkey,
193 process => l_WFProcess);
194
195 -- Sets the Planning Cycle ID
196 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
197 itemkey => l_itemkey,
198 aname => 'FPA_PC_ID',
199 avalue => p_pc_id);
200
201 -- Sets the Last Planning Cycle ID
202 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
203 itemkey => l_itemkey,
204 aname => 'FPA_LAST_PC_ID',
205 avalue => p_last_pc_id);
206
207 -- Sets the Due Date
208 wf_engine.SetItemAttrDate(itemtype => l_itemtype,
209 itemkey => l_itemkey,
210 aname => 'FPA_DUE_DATE',
211 avalue => p_due_date);
212
213
214 -- get and set additional attributes
215 GET_WF_ATTRIBUTES(
216 p_pc_id => p_pc_id,
217 x_pc_attributes_rec => l_pc_attributes_rec,
218 x_return_status => l_return_status,
219 x_msg_count => l_msg_count,
220 x_msg_data => l_msg_data);
221
222
223 -- Sets the attributes
224 wf_engine.SetItemAttrText(itemtype => l_itemtype,
225 itemkey => l_itemkey,
226 aname => 'FPA_PORTFOLIO_NAME',
227 avalue => l_pc_attributes_rec.portfolio_name);
228
229 wf_engine.SetItemAttrText(itemtype => l_itemtype,
230 itemkey => l_itemkey,
231 aname => 'FPA_PC_NAME',
232 avalue => l_pc_attributes_rec.pc_name);
233
234 wf_engine.SetItemAttrText(itemtype => l_itemtype,
235 itemkey => l_itemkey,
236 aname => 'FPA_PRTF_CLASS_CODE',
237 avalue => l_pc_attributes_rec.portfolio_type);
238
239 wf_engine.SetItemAttrText(itemtype => l_itemtype,
240 itemkey => l_itemkey,
241 aname => 'FPA_INV_CLASS_CATEGORY',
242 avalue => l_pc_attributes_rec.inv_class_category);
243
244 wf_engine.SetItemAttrText(itemtype => l_itemtype,
245 itemkey => l_itemkey,
246 aname => 'FPA_PRTF_SELECTION_CATEGORY',
247 avalue => fnd_profile.value('PJP_PORTFOLIO_CLASS_CATEGORY'));
248
249 wf_engine.SetItemAttrText(itemtype => l_itemtype,
250 itemkey => l_itemkey,
251 aname => 'FPA_COST_FIN_PLAN_TYPE',
252 avalue => l_pc_attributes_rec.cost_fin_plan);
253
254 wf_engine.SetItemAttrText(itemtype => l_itemtype,
255 itemkey => l_itemkey,
256 aname => 'FPA_BENEFIT_FIN_PLAN_TYPE',
257 avalue => l_pc_attributes_rec.benefit_fin_plan);
258
259
260 -- Starts the workflow process
261 wf_engine.StartProcess(itemtype => l_itemtype,
262 itemkey => l_itemkey);
263
264 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
265 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
266 'fpa.sql.fpa_main_process_pvt.initiate_workflow.end',
267 'Exiting fpa_main_process_pvt.initiate_workflow');
268 END IF;
269
270 EXCEPTION
271 WHEN OTHERS THEN
272 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
273 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
274 'fpa.sql.fpa_main_process_pvt.initiate_workflow.end',
275 SQLERRM);
276 END IF;
277 wf_core.context('FPA_MAIN_PROCESS_PVT', 'INITIATE_WORKFLOW', null);
278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
279 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
280 p_data => x_msg_data
281 );
282 RAISE;
283 END INITIATE_WORKFLOW;
284
285
286
287 -- Cancels the main workflow process and starts an alternate process
288 PROCEDURE CANCEL_WORKFLOW(p_pc_name IN VARCHAR2,
289 p_pc_id IN NUMBER,
290 p_pc_description IN VARCHAR2,
291 p_pc_date_initiated IN DATE,
292 p_due_date IN DATE,
293 x_return_status OUT NOCOPY VARCHAR2,
294 x_msg_count OUT NOCOPY NUMBER,
295 x_msg_data OUT NOCOPY VARCHAR2
296 )
297 IS
298
299 l_itemtype VARCHAR2(30) := 'FPAPJP';
300 l_itemkey VARCHAR2(30) := p_pc_id;
301 l_return_status VARCHAR2(1) := null;
302 l_msg_count NUMBER := null;
303 l_msg_data VARCHAR2(2000) := null;
304 l_WFProcess VARCHAR2(30) := 'FPA_INITIATE_PLANNING_CYCLE';
305 l_process VARCHAR2(30) := 'FPA_USER_FORCE';
306 l_nextval NUMBER := null;
307
308 BEGIN
309
310 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
311 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
312 'fpa.sql.fpa_main_process_pvt.cancel_workflow.begin',
313 'Entering fpa_main_process_pvt.cancel_workflow');
314 END IF;
315
316 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
317 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
318 'fpa.sql.fpa_main_process_pvt.cancel_workflow',
319 'Cancelling the master WF process; Call wf_engine.AbortProcess');
320 END IF;
321
322 -- Cancels the main process
323 wf_engine.AbortProcess(itemtype => l_itemtype,
324 itemkey => l_itemkey,
325 process => l_WFProcess);
326
327 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
328 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
329 'fpa.sql.fpa_main_process_pvt.cancel_workflow',
330 'Canceled the master WF process; wf_engine.AbortProcess executed');
331 END IF;
332
333 -- Reassign a new item key to the new process
334 l_itemkey := 'FPAF' || p_pc_id;
335
336 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
337 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
338 'fpa.sql.fpa_main_process_pvt.cancel_workflow',
339 'l_item_key = '||l_itemkey||'; l_itemtype = '||l_itemtype);
340 END IF;
341
342 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
343 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
344 'fpa.sql.fpa_main_process_pvt.cancel_workflow',
345 'l_process = '||l_process||'; p_pc_id = '||p_pc_id);
346 END IF;
347
348
349 -- Creates the workflow process
350 wf_engine.CreateProcess(itemtype => l_itemtype,
351 itemkey => l_itemkey,
352 process => l_process);
353
354 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
355 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
356 'fpa.sql.fpa_main_process_pvt.cancel_workflow',
357 'executed wf_engine.createProcess API');
358 END IF;
359
360 -- Sets the Planning Cycle ID
361 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
362 itemkey => l_itemkey,
363 aname => 'FPA_PC_ID',
364 avalue => p_pc_id);
365
366
367 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
368 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
369 'fpa.sql.fpa_main_process_pvt.cancel_workflow',
370 'executed wf_engine.SetItemAttrNumber API');
371 END IF;
372
373 -- Sets the Due Date
374 wf_engine.SetItemAttrDate(itemtype => l_itemtype,
375 itemkey => l_itemkey,
376 aname => 'FPA_DUE_DATE',
377 avalue => p_due_date);
378
379 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
380 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
381 'fpa.sql.fpa_main_process_pvt.cancel_workflow',
382 'executed wf_engine.SetItemAttrDate API');
383 END IF;
384
385 -- Starts the workflow process
386 wf_engine.StartProcess(itemtype => l_itemtype,
387 itemkey => l_itemkey);
388
389 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
390 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
391 'fpa.sql.fpa_main_process_pvt.cancel_workflow',
392 'executed wf_engine.StartProcess API');
393 END IF;
394
395
396 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
397 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
398 'fpa.sql.fpa_main_process_pvt.cancel_workflow.end',
399 'Exiting fpa_main_process_pvt.cancel_workflow');
400 END IF;
401
402 EXCEPTION
403 WHEN OTHERS THEN
404 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
405 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
406 'fpa.sql.fpa_main_process_pvt.cancel_workflow.end',
407 SQLERRM);
408 END IF;
409 wf_core.context('FPA_MAIN_PROCESS_PVT', 'CANCEL_WORKFLOW', null);
410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
411 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
412 p_data => x_msg_data
413 );
414 RAISE;
415 END CANCEL_WORKFLOW;
416
417
418
419 -- Launch the main workflow process
420 PROCEDURE LAUNCH_PROCESS(p_itemtype IN VARCHAR2,
421 p_itemkey IN VARCHAR2,
422 p_actid IN NUMBER,
423 p_funcmode IN VARCHAR2,
424 x_resultout OUT NOCOPY VARCHAR2)
425 IS
426
427 l_itemtype VARCHAR2(30) := 'FPAPJP';
428 l_itemkey VARCHAR2(30) := null;
429 l_process VARCHAR2(30) := 'FPA_MAIN_PROCESS';
430 l_pc_id NUMBER := null;
431 l_scenario_id NUMBER := null;
432 l_due_date DATE := null;
433 l_nextval NUMBER := null;
434
435 l_return_status VARCHAR2(1) := null;
436 l_msg_count NUMBER := null;
437 l_msg_data VARCHAR2(2000) := null;
438
439 l_pc_attributes_rec PC_ATTRIBUTES_REC_TYPE;
440
441 BEGIN
442
443 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
444 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
445 'fpa.sql.fpa_main_process_pvt.launch_process.begin',
446 'Entering fpa_main_process_pvt.launch_process');
447 END IF;
448
449 l_pc_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
450 itemkey => p_itemkey,
451 aname => 'FPA_PC_ID');
452 l_itemtype := 'FPAPJP';
453 l_itemkey := 'FPAL' ||l_pc_id;
454
455 l_due_date := wf_engine.GetItemAttrDate(itemtype => p_itemtype,
456 itemkey => p_itemkey,
457 aname => 'FPA_DUE_DATE');
458
459 l_scenario_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
460 itemkey => p_itemkey,
461 aname => 'FPA_SCENARIO_ID');
462
463 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
464 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
465 'fpa.sql.fpa_main_process_pvt.launch_process',
466 'Starting the main workflow process');
467 END IF;
468
469 -- Creates the workflow process
470 wf_engine.CreateProcess(itemtype => l_itemtype,
471 itemkey => l_itemkey,
472 process => l_process);
473
474 -- Sets the Planning Cycle ID
475 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
476 itemkey => l_itemkey,
477 aname => 'FPA_PC_ID',
478 avalue => l_pc_id);
479
480 -- Sets the Scenario ID
481 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
482 itemkey => l_itemkey,
483 aname => 'FPA_SCENARIO_ID',
484 avalue => l_scenario_id);
485
486 -- Sets the Due Date
487 wf_engine.SetItemAttrDate(itemtype => l_itemtype,
488 itemkey => l_itemkey,
489 aname => 'FPA_DUE_DATE',
490 avalue => l_due_date);
491
492 -- get and set additional attributes
493 GET_WF_ATTRIBUTES(
494 p_pc_id => l_pc_id,
495 x_pc_attributes_rec => l_pc_attributes_rec,
496 x_return_status => l_return_status,
497 x_msg_count => l_msg_count,
498 x_msg_data => l_msg_data);
499
500
501 -- Set the attributes
502
503 wf_engine.SetItemAttrText(itemtype => l_itemtype,
504 itemkey => l_itemkey,
505 aname => 'FPA_PORTFOLIO_NAME',
506 avalue => l_pc_attributes_rec.portfolio_name);
507
508 wf_engine.SetItemAttrText(itemtype => l_itemtype,
509 itemkey => l_itemkey,
510 aname => 'FPA_PC_NAME',
511 avalue => l_pc_attributes_rec.pc_name);
512
513
514 -- Starts the workflow process
515 wf_engine.StartProcess(itemtype => l_itemtype,
516 itemkey => l_itemkey);
517
518 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
519 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
520 'fpa.sql.fpa_main_process_pvt.launch_process.end',
521 'Exiting fpa_main_process_pvt.launch_process');
522 END IF;
523
524 EXCEPTION
525 WHEN OTHERS THEN
526 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
527 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
528 'fpa.sql.fpa_main_process_pvt.launch_process.end',
529 SQLERRM);
530 END IF;
531 wf_core.context('FPA_MAIN_PROCESS_PVT', 'LAUNCH_PROCESS', null);
532 RAISE;
533 END LAUNCH_PROCESS;
534
535 PROCEDURE RAISE_CLOSEPC_EVENT(p_pc_id IN NUMBER,
536 x_return_status OUT NOCOPY VARCHAR2,
537 x_msg_count OUT NOCOPY NUMBER,
538 x_msg_data OUT NOCOPY VARCHAR2
539 ) IS
540
541 l_parameter_list wf_parameter_list_t;
542 l_itemkey VARCHAR2(30);
543
544 CURSOR C_abort_activepcwf IS
545 SELECT item_type,item_key,root_activity
546 FROM wf_items
547 WHERE item_type = 'FPAPJP'
548 AND (item_key = 'FPAL'|| p_pc_id OR item_key = 'FPAF'|| p_pc_id OR item_key = to_char(p_pc_id))
549 AND end_date IS NULL;
550
551 BEGIN
552
553 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
554 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
555 'fpa.sql.fpa_main_process_pvt.raise_closepc_event.begin',
556 'Entering fpa_main_process_pvt.raise_closepc_event for p_pc_id'||p_pc_id);
557 END IF;
558
559 FOR c_rec IN C_abort_activepcwf LOOP
560
561 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
562 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
563 'fpa.sql.fpa_main_process_pvt.raise_closepc_event',
564 'Aborting workflow process '||c_rec.item_key);
565 END IF;
566
567 -- Cancels all the workflow process associated with this Planning cycle
568 wf_engine.AbortProcess(itemtype => c_rec.item_type,
569 itemkey => c_rec.item_key,
570 process => c_rec.root_activity);
571
572 END LOOP;
573
574 -- Code to start new workflow process for Closing Planning cycle
575 l_itemkey := 'FPAC'||p_pc_id;
576
577 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
578 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
579 'fpa.sql.fpa_main_process_pvt.raise_closepc_event',
580 'Calling wf_event.AddParameterToList ');
581 END IF;
582
583 wf_event.AddParameterToList('FPA_PC_ID',p_pc_id,l_parameter_list);
584
585 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
586 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
587 'fpa.sql.fpa_main_process_pvt.raise_closepc_event',
588 'Raising event oracle.apps.fpa.event.planningcycle.closed');
589 END IF;
590
591 -- Raise Event
592 wf_event.RAISE(p_event_name => 'oracle.apps.fpa.event.planningcycle.closed',
593 p_event_key => l_itemkey,
594 p_parameters => l_parameter_list);
595
596 l_parameter_list.DELETE;
597
598 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
599 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
600 'fpa.sql.fpa_main_process_pvt.raise_closepc_event.end',
601 'Entering fpa_main_process_pvt.raise_closepc_event');
602 END IF;
603
604 EXCEPTION
605 WHEN OTHERS THEN
606 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
607 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
608 'fpa.sql.fpa_main_process_pvt.raise_closepc_event.exception',
609 SQLERRM);
610 END IF;
611 RAISE;
612 END RAISE_CLOSEPC_EVENT;
613
614
615
616 PROCEDURE GET_USERS (p_pcid IN VARCHAR2,
617 p_notify_role IN VARCHAR2,
618 p_approver IN VARCHAR2,
619 p_analyst IN VARCHAR2,
620 p_pcdlist IN VARCHAR2,
621 p_projmg IN VARCHAR2,
622 p_user_exists OUT NOCOPY VARCHAR2 )
623 IS
624
625 l_user fnd_user.user_name%type := null;
626 l_user_org_id NUMBER;
627 l_user_exists varchar2(1) := 'N';
628
629 l_pjp_org_version_id NUMBER;
630 l_portfolio_org_id NUMBER;
631 l_flag VARCHAR2(1);
632
633 -- Select the list of users defined in the portfolio access list who has to be notified.
634 -- Also verify the user has an active assignment as of sysdate .
635
636 CURSOR c_get_port_users IS
637 SELECT DISTINCT u.user_name, prd.organization_id
638 FROM pa_project_parties ppp,
639 pa_project_role_types pprt,
640 per_all_people_f pe,
641 per_all_assignments_f prd,
642 fnd_user u ,
643 FPA_AW_PC_INFO_V pc
644 WHERE ppp.resource_type_id = 101
645 AND ppp.project_role_id = pprt.project_role_id
646 AND ppp.resource_source_id = pe.person_id
647 AND trunc(sysdate) BETWEEN trunc(ppp.start_date_active) AND trunc(NVL(ppp.end_date_active,sysdate))
648 AND trunc(sysdate) BETWEEN trunc(pe.effective_start_date) AND trunc(pe.effective_end_date)
649 AND ppp.resource_source_id = prd.person_id
650 AND prd.primary_flag = 'Y'
651 AND prd.assignment_type = 'E'
652 AND trunc(sysdate) BETWEEN trunc(prd.effective_start_date) AND trunc(prd.effective_end_date)
653 AND u.employee_id = ppp.resource_source_id
654 and trunc(sysdate) BETWEEN trunc(u.start_date) AND trunc(NVL(u.end_date,sysdate))
655 AND ppp.object_type = 'PJP_PORTFOLIO'
656 AND ppp.object_id = pc.portfolio
657 AND ((p_approver = 'Y' and p_analyst = 'Y' and pprt.project_role_type IN ('PORTFOLIO_APPROVER', 'PORTFOLIO_ANALYST')) OR
658 (p_approver = 'Y' and p_analyst = 'N' and pprt.project_role_type = 'PORTFOLIO_APPROVER') OR
659 (p_approver = 'N' and p_analyst = 'Y' and pprt.project_role_type = 'PORTFOLIO_ANALYST'))
660 AND pc.planning_cycle = p_pcid;
661
662 Cursor c_get_dlist_users IS
663 SELECT DISTINCT ppp.user_name, ppp.organization_id -- select to fetch persons attached to the role defined in the access list
664 FROM pa_dist_list_items i,
665 pa_project_parties_v ppp,
666 pa_object_dist_lists podl,
667 fnd_user u
668 WHERE i.recipient_type = 'PROJECT_ROLE'
669 AND i.list_id = podl.list_id
670 AND podl.object_id = p_pcid
671 AND podl.object_type = 'PJP_PLANNING_CYCLE'
672 AND ppp.resource_type_id = 101
673 AND ppp.project_role_id = i.recipient_id
674 and ppp.user_id IS NOT NULL -- To prevent invalid user name error during workflow
675 and ppp.user_id = u.user_id
676 and trunc(sysdate) BETWEEN trunc(u.start_date) AND trunc(NVL(u.end_date,sysdate))
677 UNION
678 SELECT DISTINCT u.user_name, a.organization_id -- select to fetch persons defined in the access list
679 FROM pa_dist_list_items i,
680 pa_object_dist_lists podl,
681 hz_parties hzp,
682 fnd_user u,
683 per_all_assignments_f a, per_all_people_f p
684 WHERE i.recipient_type = 'HZ_PARTY'
685 AND i.list_id = podl.list_id
686 AND podl.object_id = p_pcid
687 AND podl.object_type = 'PJP_PLANNING_CYCLE'
688 AND hzp.party_id = i.recipient_id
689 AND hzp.party_type = 'PERSON'
690 AND u.person_party_id = hzp.party_id
691 and trunc(sysdate) BETWEEN trunc(u.start_date) AND trunc(NVL(u.end_date,sysdate))
692 AND p.person_id = a.person_id
693 AND hzp.party_id = p.party_id (+);
694
695
696 Cursor c_get_projmg_users IS
697 select distinct u.user_name
698 from fpa_aw_sce_info_v sc,
699 fpa_aw_proj_info_v proj,
700 pa_project_players pp,
701 fnd_user u
702 where proj.scenario = sc.scenario
703 and sc.planning_cycle = p_pcid
704 and sc.approved_flag = 1
705 and pp.project_id = proj.project
706 and pp.resource_type_id =101
707 and pp.project_role_type = 'PROJECT MANAGER'
708 and u.employee_id = pp.person_id
709 and trunc(sysdate) BETWEEN trunc(u.start_date) AND trunc(NVL(u.end_date,sysdate))
710 AND trunc(sysdate) BETWEEN trunc(pp.start_date_active) AND trunc(NVL(pp.end_date_active,sysdate));
711
712 CURSOR verify_user_org_csr(
713 p_hier_version_id IN NUMBER,
714 p_portfolio_org_id IN NUMBER,
715 p_user_org_id IN NUMBER) is
716 SELECT 'T' FROM (
717 SELECT
718 organization_id_child
719 FROM
720 per_org_structure_elements
721 WHERE
722 org_structure_version_id = p_hier_version_id
723 CONNECT BY PRIOR organization_id_child = organization_id_parent
724 AND PRIOR org_structure_version_id = p_hier_version_id
725 START WITH organization_id_parent = p_portfolio_org_id )
726 WHERE organization_id_child = p_user_org_id;
727
728 BEGIN
729
730 -- Get the list of users to be notified
731 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
732 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
733 'fpa.sql.fpa_main_process_pvt.GET_USERS.begin',
734 'Entering fpa_main_process_pvt.GET_USERS for p_pcid '||p_pcid);
735 END IF;
736
737 select
738 org_structure_version_id into l_pjp_org_version_id
739 from
740 per_org_structure_versions
741 where
742 organization_structure_id = FND_PROFILE.VALUE('PJP_ORGANIZATION_HIERARCHY')
743 and (trunc(sysdate) between trunc(date_from) and trunc(nvl(date_to,
744 sysdate)));
745
746 select
747 portfolio_organization into l_portfolio_org_id
748 from
749 fpa_aw_pcs_v pcp, fpa_aw_portf_headers_v ph
750 where
751 pcp.portfolio = ph.portfolio
752 and pcp.planning_cycle = p_pcid;
753
754 IF p_approver = 'Y' OR p_analyst = 'Y' THEN
755
756 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
757 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
758 'fpa.sql.fpa_main_process_pvt.GET_USERS.begin',
759 'Fetching users associated with portfolio access list');
760 END IF;
761
762 OPEN c_get_port_users;
763 LOOP
764 FETCH c_get_port_users INTO l_user, l_user_org_id;
765 EXIT WHEN c_get_port_users%NOTFOUND;
766 -- Bug#13514467 modifications start.
767 -- if(l_portfolio_org_id is not null and l_portfolio_org_id = l_user_org_id) then
768 -- l_flag := FND_API.G_TRUE;
769 -- elsif(l_portfolio_org_id is not null) then
770 -- l_flag := null;
771 -- open verify_user_org_csr(l_pjp_org_version_id,
772 -- l_portfolio_org_id,
773 -- l_user_org_id);
774 -- fetch verify_user_org_csr into l_flag;
775 -- close verify_user_org_csr;
776 -- else
777 -- l_flag := FND_API.G_TRUE;
778 -- end if;
779
780 -- if(l_flag = FND_API.G_TRUE) then
781 -- Add users to the role.
782 if wf_directory.IsPerformer (l_user, p_notify_role)= false then -- Bug#14558240
783 wf_directory.AddUsersToAdHocRole(role_name => p_notify_role,
784 role_users => l_user);
785 end if; --Bug#14558240
786 IF l_user_exists ='N' then
787 l_user_exists := 'Y';
788 END If;
789
790 -- end if;
791 -- Bug#13514467 modifications end.
792 END LOOP;
793 CLOSE c_get_port_users ;
794
795 END IF;
796
797 IF p_pcdlist = 'Y' THEN
798
799 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
800 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
801 'fpa.sql.fpa_main_process_pvt.GET_USERS.begin',
802 'Fetching users associated with planning cycle distribution list');
803 END IF;
804
805 OPEN c_get_dlist_users;
806 LOOP
807 FETCH c_get_dlist_users INTO l_user, l_user_org_id;
808 EXIT WHEN c_get_dlist_users%NOTFOUND;
809 -- Bug#13514467 modifications start.
810 -- if(l_portfolio_org_id is not null and l_portfolio_org_id = l_user_org_id) then
811 -- l_flag := FND_API.G_TRUE;
812 -- elsif(l_portfolio_org_id is not null) then
813 -- l_flag := null;
814 -- open verify_user_org_csr(l_pjp_org_version_id,
815 -- l_portfolio_org_id,
816 -- l_user_org_id);
817 -- fetch verify_user_org_csr into l_flag;
818 -- close verify_user_org_csr;
819 -- else
820 -- l_flag := FND_API.G_TRUE;
821 -- end if;
822 -- if(l_flag = FND_API.G_TRUE) then
823 -- Add users to the role.
824 if wf_directory.IsPerformer (l_user, p_notify_role)= false then -- Bug#14558240
825 wf_directory.AddUsersToAdHocRole(role_name => p_notify_role,
826 role_users => l_user);
827 end if;
828 IF l_user_exists ='N' then
829 l_user_exists := 'Y';
830 END If;
831 -- end if;
832 -- Bug#13514467 modifications end.
833 END LOOP;
834 CLOSE c_get_dlist_users ;
835
836 END IF;
837
838 IF p_projmg = 'Y' THEN
839
840 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
841 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
842 'fpa.sql.fpa_main_process_pvt.GET_USERS.begin',
843 'Fetching users associated with project managers');
844 END IF;
845
846 OPEN c_get_projmg_users;
847 LOOP
848 FETCH c_get_projmg_users INTO l_user;
849 EXIT WHEN c_get_projmg_users%NOTFOUND;
850 -- Add users to the role.
851 if wf_directory.IsPerformer (l_user, p_notify_role)= false then -- Bug#14558240
852 wf_directory.AddUsersToAdHocRole(role_name => p_notify_role,
853 role_users => l_user);
854 end if;
855 IF l_user_exists ='N' then
856 l_user_exists := 'Y';
857 END If;
858
859 END LOOP;
860 CLOSE c_get_projmg_users ;
861
862 END IF;
863
864 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
865 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
866 'fpa.sql.fpa_main_process_pvt.GET_USERS.end',
867 'Exiting fpa_main_process_pvt.GET_USERS l_user_exists value '||l_user_exists);
868 END IF;
869
870 p_user_exists := l_user_exists;
871
872 EXCEPTION
873 WHEN OTHERS THEN
874 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
875 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
876 'fpa.sql.fpa_main_process_pvt.GET_USERS.end',
877 SQLERRM);
878 END IF;
879
880 IF c_get_port_users%ISOPEN THEN
881 close c_get_port_users;
882 END IF;
883
884 IF c_get_dlist_users%ISOPEN THEN
885 close c_get_dlist_users;
886 END IF;
887
888 IF c_get_projmg_users%ISOPEN THEN
889 close c_get_projmg_users;
890 END IF;
891
892 p_user_exists := 'N';
893 RAISE;
894 END GET_USERS;
895
896 PROCEDURE CREATE_ROLE ( p_item_type IN VARCHAR2,
897 p_item_key IN VARCHAR2,
898 p_calledfrom IN VARCHAR2,
899 p_user_exists OUT NOCOPY VARCHAR2) IS
900
901 l_pc_id NUMBER := null;
902 l_user_exists VARCHAR2(1) := 'N';
903 l_approver VARCHAR2(1) := 'N';
904 l_analyst VARCHAR2(1) := 'N';
905 l_pcdlist VARCHAR2(1) := 'N';
906 l_projmg VARCHAR2(1) := 'N';
907 l_notify_role wf_roles.name%TYPE := NULL;
908
909 WF_API_EXCEPTION exception;
910 pragma exception_init(WF_API_EXCEPTION, -20002);
911
912 BEGIN
913
914 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
915 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
916 'fpa.sql.fpa_main_process_pvt.create_role.begin',
917 'Entering fpa_main_process_pvt.create_role');
918 END IF;
919
920 -- Get the planning cycle ID
921 l_pc_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type,
922 itemkey => p_item_key,
923 aname => 'FPA_PC_ID');
924
925 IF p_calledfrom = g_get_port_users THEN
926 l_notify_role := 'FPA_APPR_ANALY_' || l_pc_id;
927 l_approver := 'Y';
928 l_analyst := 'Y';
929 ELSIF p_calledfrom = g_get_pc_dlist THEN
930 l_notify_role := 'FPA_PC_APPROVERS_' || l_pc_id;
931 l_pcdlist := 'Y';
932 ELSIF p_calledfrom = g_get_port_apprv THEN
933 l_notify_role := 'FPA_APPROVER_' || l_pc_id;
934 l_approver := 'Y';
935 ELSIF p_calledfrom = g_get_port_analyst THEN
936 l_notify_role := 'FPA_ANALYST_' || l_pc_id;
937 l_analyst := 'Y';
938 ELSIF p_calledfrom = g_get_pc_manager THEN
939 l_notify_role := 'FPA_PC_MANAGERS_' || l_pc_id;
940 l_approver := 'Y';
941 l_analyst := 'Y';
942 l_projmg := 'Y';
943 END IF;
944
945 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
946 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
947 'fpa.sql.fpa_main_process_pvt.create_role.begin',
948 'value of l_notify_role='||l_notify_role||'l_approver='||l_approver||'l_analyst='||l_analyst||'l_pcdlist='||l_pcdlist||'l_projmg='||l_projmg);
949 END IF;
950
951 -- Create adhoc role. This will be used to send the notifications
952 -- to members of this role.
953 -- Create role only when not present else remove users from the role Bug 12709972
954 IF (NOT Wf_Directory.RoleActive(l_notify_role)) THEN
955 BEGIN
956 wf_directory.CreateAdHocRole(role_name => l_notify_role,
957 role_display_name => l_notify_role,
958 role_users => null,
959 email_address => null);
960 EXCEPTION
961 WHEN WF_API_EXCEPTION THEN
962 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
963 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
964 'fpa.sql.fpa_main_process_pvt.CREATE_ROLE.exception',
965 SQLERRM);
966 END IF;
967
968 END;
969 ELSE
970 -- Delete the users if already existing from the above role :
971 wf_directory.RemoveUsersFromAdhocRole(role_name => l_notify_role);
972 END IF;
973
974 -- Need to notify Project Managers
975 wf_engine.SetItemAttrText(itemtype => p_item_type,
976 itemkey => p_item_key,
977 aname => 'FPA_PROJ_MANAGERS',
978 avalue => l_notify_role);
979
980 GET_USERS (p_pcid => l_pc_id,
981 p_notify_role => l_notify_role,
982 p_approver => l_approver,
983 p_analyst => l_analyst,
984 p_pcdlist => l_pcdlist,
985 p_projmg => l_projmg,
986 p_user_exists => l_user_exists ) ;
987
988 p_user_exists := l_user_exists;
989
990 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
991 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
992 'fpa.sql.fpa_main_process_pvt.create_role.end',
993 'Entering fpa_main_process_pvt.create_role,value of p_user_exists'||p_user_exists);
994 END IF;
995
996 EXCEPTION
997 WHEN OTHERS THEN
998 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
999 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1000 'fpa.sql.fpa_main_process_pvt.CREATE_ROLE.end',
1001 SQLERRM);
1002 END IF;
1003 wf_core.context('FPA_MAIN_PROCESS_PVT', 'CREATE_ROLE', SQLERRM);
1004 RAISE;
1005 END CREATE_ROLE;
1006
1007 -- Workflow Procedure to fetch all the users defined in the distribution list of the
1008 -- planning cycle.
1009
1010 PROCEDURE GET_DLIST(p_itemtype IN VARCHAR2,
1011 p_itemkey IN VARCHAR2,
1012 p_actid IN NUMBER,
1013 p_funcmode IN VARCHAR2,
1014 x_resultout OUT NOCOPY VARCHAR2)
1015 IS
1016
1017 l_user_exists VARCHAR2(1) := 'N';
1018
1019 BEGIN
1020
1021 /* Get the list of users having project manager role */
1022 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1023 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1024 'fpa.sql.fpa_main_process_pvt.get_dlist.begin',
1025 'Entering fpa_main_process_pvt.get_dlist');
1026 END IF;
1027
1028 -- Do not perform anything in CANCEL or TIMEOUT mode
1029 IF (p_funcmode <> wf_engine.eng_run) THEN
1030 x_resultout := wf_engine.eng_null;
1031 RETURN;
1032 END IF;
1033
1034 CREATE_ROLE ( p_item_type => p_itemtype,
1035 p_item_key => p_itemkey ,
1036 p_calledfrom => g_get_pc_dlist,
1037 p_user_exists => l_user_exists);
1038
1039 IF l_user_exists = 'Y' then
1040 x_resultout := wf_engine.eng_completed||':'||'T';
1041 ELSE
1042 x_resultout := wf_engine.eng_completed||':'||'F';
1043 END IF;
1044
1045
1046 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1047 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1048 'fpa.sql.fpa_main_process_pvt.get_dlist.end',
1049 'Exiting fpa_main_process_pvt.get_dlist');
1050 END IF;
1051
1052 EXCEPTION
1053 WHEN OTHERS THEN
1054 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1055 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1056 'fpa.sql.fpa_main_process_pvt.get_dlist.end',
1057 SQLERRM);
1058 END IF;
1059 wf_core.context('FPA_MAIN_PROCESS_PVT', g_get_pc_dlist, SQLERRM);
1060 RAISE;
1061 END GET_DLIST;
1062
1063
1064 -- Workflow Procedure to fetch all the users defined in the access list of the
1065 -- portfolio.
1066 PROCEDURE GET_ALIST(p_itemtype IN VARCHAR2,
1067 p_itemkey IN VARCHAR2,
1068 p_actid IN NUMBER,
1069 p_funcmode IN VARCHAR2,
1070 x_resultout OUT NOCOPY VARCHAR2)
1071 IS
1072
1073 l_user_exists VARCHAR2(1) := 'N';
1074
1075 BEGIN
1076
1077 -- Get the list of users to be notified
1078 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1079 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1080 'fpa.sql.fpa_main_process_pvt.get_alist.begin',
1081 'Entering fpa_main_process_pvt.get_alist');
1082 END IF;
1083
1084 -- Do not perform anything in CANCEL or TIMEOUT mode
1085 IF (p_funcmode <> wf_engine.eng_run) THEN
1086 x_resultout := wf_engine.eng_null;
1087 RETURN;
1088 END IF;
1089
1090 CREATE_ROLE ( p_item_type => p_itemtype,
1091 p_item_key => p_itemkey ,
1092 p_calledfrom => g_get_port_users,
1093 p_user_exists => l_user_exists);
1094
1095 IF l_user_exists = 'Y' then
1096 x_resultout := wf_engine.eng_completed||':'||'T';
1097 ELSE
1098 x_resultout := wf_engine.eng_completed||':'||'F';
1099 END IF;
1100
1101 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1102 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1103 'fpa.sql.fpa_main_process_pvt.get_alist.end',
1104 'Exiting fpa_main_process_pvt.get_alist');
1105 END IF;
1106 EXCEPTION
1107 WHEN OTHERS THEN
1108 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1109 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1110 'fpa.sql.fpa_main_process_pvt.get_alist.end',
1111 SQLERRM);
1112 END IF;
1113 wf_core.context('FPA_MAIN_PROCESS_PVT', g_get_port_users, SQLERRM);
1114 RAISE;
1115 END GET_ALIST;
1116
1117 -- Workflow Procedure to fetch all the users defined as portfolio approvers
1118
1119 PROCEDURE GET_APPROVER(p_itemtype IN VARCHAR2,
1120 p_itemkey IN VARCHAR2,
1121 p_actid IN NUMBER,
1122 p_funcmode IN VARCHAR2,
1123 x_resultout OUT NOCOPY VARCHAR2)
1124 IS
1125
1126 l_user_exists VARCHAR2(1) := 'N';
1127
1128 BEGIN
1129
1130 /* Get the list of users having project manager role */
1131 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1132 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1133 'fpa.sql.fpa_main_process_pvt.get_approver.begin',
1134 'Entering fpa_main_process_pvt.get_approver');
1135 END IF;
1136
1137 -- Do not perform anything in CANCEL or TIMEOUT mode
1138 IF (p_funcmode <> wf_engine.eng_run) THEN
1139 x_resultout := wf_engine.eng_null;
1140 RETURN;
1141 END IF;
1142
1143 CREATE_ROLE ( p_item_type => p_itemtype,
1144 p_item_key => p_itemkey ,
1145 p_calledfrom => g_get_port_apprv,
1146 p_user_exists => l_user_exists);
1147
1148 IF l_user_exists = 'Y' then
1149 x_resultout := wf_engine.eng_completed||':'||'T';
1150 ELSE
1151 x_resultout := wf_engine.eng_completed||':'||'F';
1152 END IF;
1153
1154
1155 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1156 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1157 'fpa.sql.fpa_main_process_pvt.get_approver.end',
1158 'Exiting fpa_main_process_pvt.get_approver');
1159 END IF;
1160
1161 EXCEPTION
1162 WHEN OTHERS THEN
1163 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1164 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1165 'fpa.sql.fpa_main_process_pvt.get_approver.end',
1166 SQLERRM);
1167 END IF;
1168 wf_core.context('FPA_MAIN_PROCESS_PVT', 'GET_DLIST', SQLERRM);
1169 RAISE;
1170 END GET_APPROVER;
1171
1172
1173 -- Workflow Procedure to fetch all the users defined as portfolio analyst
1174 PROCEDURE GET_ANALYST(p_itemtype IN VARCHAR2,
1175 p_itemkey IN VARCHAR2,
1176 p_actid IN NUMBER,
1177 p_funcmode IN VARCHAR2,
1178 x_resultout OUT NOCOPY VARCHAR2)
1179 IS
1180
1181 l_user_exists VARCHAR2(1) := 'N';
1182
1183 BEGIN
1184
1185 /* Get the list of users having project manager role */
1186 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1187 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1188 'fpa.sql.fpa_main_process_pvt.get_analyst.begin',
1189 'Entering fpa_main_process_pvt.get_analyst');
1190 END IF;
1191
1192 -- Do not perform anything in CANCEL or TIMEOUT mode
1193 IF (p_funcmode <> wf_engine.eng_run) THEN
1194 x_resultout := wf_engine.eng_null;
1195 RETURN;
1196 END IF;
1197
1198 CREATE_ROLE ( p_item_type => p_itemtype,
1199 p_item_key => p_itemkey ,
1200 p_calledfrom => g_get_port_analyst,
1201 p_user_exists => l_user_exists);
1202
1203 IF l_user_exists = 'Y' then
1204 x_resultout := wf_engine.eng_completed||':'||'T';
1205 ELSE
1206 x_resultout := wf_engine.eng_completed||':'||'F';
1207 END IF;
1208
1209
1210 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1211 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1212 'fpa.sql.fpa_main_process_pvt.get_analyst.end',
1213 'Exiting fpa_main_process_pvt.get_analyst');
1214 END IF;
1215
1216 EXCEPTION
1217 WHEN OTHERS THEN
1218 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1219 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1220 'fpa.sql.fpa_main_process_pvt.get_analyst.end',
1221 SQLERRM);
1222 END IF;
1223 wf_core.context('FPA_MAIN_PROCESS_PVT', g_get_port_analyst, SQLERRM);
1224 RAISE;
1225 END GET_ANALYST;
1226
1227
1228
1229 -- Workflow Procedure to fetch all the users defined as portfolio analyst ,
1230 -- portfolio approver and proget managers associated with projects within a approved scenario
1231
1232 PROCEDURE GET_PC_MANAGERS(p_itemtype IN VARCHAR2,
1233 p_itemkey IN VARCHAR2,
1234 p_actid IN NUMBER,
1235 p_funcmode IN VARCHAR2,
1236 x_resultout OUT NOCOPY VARCHAR2)
1237 IS
1238
1239 l_user_exists VARCHAR2(1) := 'N';
1240
1241 BEGIN
1242
1243 /* Get the list of users having project manager role */
1244 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1245 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1246 'fpa.sql.fpa_main_process_pvt.get_pc_managers.begin',
1247 'Entering fpa_main_process_pvt.get_pc_managers');
1248 END IF;
1249
1250 -- Do not perform anything in CANCEL or TIMEOUT mode
1251 IF (p_funcmode <> wf_engine.eng_run) THEN
1252 x_resultout := wf_engine.eng_null;
1253 RETURN;
1254 END IF;
1255
1256 CREATE_ROLE ( p_item_type => p_itemtype,
1257 p_item_key => p_itemkey ,
1258 p_calledfrom => g_get_pc_manager,
1259 p_user_exists => l_user_exists);
1260
1261 IF l_user_exists = 'Y' then
1262 x_resultout := wf_engine.eng_completed||':'||'T';
1263 ELSE
1264 x_resultout := wf_engine.eng_completed||':'||'F';
1265 END IF;
1266
1267
1268 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1269 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1270 'fpa.sql.fpa_main_process_pvt.get_pc_managers.end',
1271 'Exiting fpa_main_process_pvt.get_pc_managers');
1272 END IF;
1273
1274 EXCEPTION
1275 WHEN OTHERS THEN
1276 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1277 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1278 'fpa.sql.fpa_main_process_pvt.get_pc_managers.end',
1279 SQLERRM);
1280 END IF;
1281 wf_core.context('FPA_MAIN_PROCESS_PVT', g_get_pc_manager, SQLERRM);
1282 RAISE;
1283 END GET_PC_MANAGERS;
1284
1285 /* Wrapper calls */
1286 /* Project load */
1287 -- calls Project Load api
1288 PROCEDURE CALL_PROJ_LOAD(p_itemtype IN VARCHAR2,
1289 p_itemkey IN VARCHAR2,
1290 p_actid IN NUMBER,
1291 p_funcmode IN VARCHAR2,
1292 x_resultout OUT NOCOPY VARCHAR2)
1293 IS
1294
1295 l_return_status VARCHAR2(1) := null;
1296 l_msg_count NUMBER := null;
1297 l_msg_data VARCHAR2(2000) := null;
1298
1299 BEGIN
1300 -- Not used anywhere
1301 x_resultout := null;
1302
1303 EXCEPTION
1304 WHEN OTHERS THEN
1305 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1306 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1307 'fpa.sql.fpa_main_process_pvt.call_proj_load.end',
1308 SQLERRM);
1309 END IF;
1310 wf_core.context('FPA_MAIN_PROCESS_PVT', 'CALL_PROJ_LOAD', SQLERRM);
1311 RAISE;
1312 END CALL_PROJ_LOAD;
1313
1314
1315
1316 /* Create Initial Scenario */
1317 -- calls Create Initial Scenario api
1318 PROCEDURE CALL_CREATE_INITIAL_SCENARIO(p_itemtype IN VARCHAR2,
1319 p_itemkey IN VARCHAR2,
1320 p_actid IN NUMBER,
1321 p_funcmode IN VARCHAR2,
1322 x_resultout OUT NOCOPY VARCHAR2)
1323 IS
1324
1325 l_pc_id NUMBER := null;
1326 l_scenario_id NUMBER := null;
1327 l_scenario_funds NUMBER := null;
1328 l_discount_rate NUMBER := null;
1329 l_return_status VARCHAR2(1) := null;
1330 l_msg_count NUMBER := null;
1331 l_msg_data VARCHAR2(2000) := null;
1332 l_scenario_name fpa_lookups_v.meaning%TYPE := null;
1333 l_scenario_desc fpa_lookups_v.meaning%TYPE := null;
1334
1335 BEGIN
1336
1337 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1338 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1339 'fpa.sql.fpa_main_process_pvt.call_create_initial_scenario.begin',
1340 'Entering fpa_main_process_pvt.call_create_initial_scenario');
1341 END IF;
1342
1343 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1344 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1345 'fpa.sql.fpa_main_process_pvt.call_create_initial_scenario.begin',
1346 'Getting Planning Cycle ID.');
1347 END IF;
1348
1349
1350 -- Get the Planning Cycle ID
1351 l_pc_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
1352 itemkey => p_itemkey,
1353 aname => 'FPA_PC_ID');
1354
1355 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1356 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1357 'fpa.sql.fpa_main_process_pvt.call_create_initial_scenario.begin',
1358 'Getting Initial scenario name.');
1359 END IF;
1360
1361 -- Get the Initial Scenario name and description
1362 BEGIN
1363 SELECT meaning
1364 INTO l_scenario_name
1365 FROM fpa_lookups_v
1366 WHERE lookup_code = 'FPA_INITIAL_SCENARIO_NAME';
1367 EXCEPTION
1368 WHEN OTHERS THEN
1369 null;
1370 END;
1371
1372 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1373 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1374 'fpa.sql.fpa_main_process_pvt.call_create_initial_scenario.begin',
1375 'Getting discount rate and funds available.');
1376 END IF;
1377
1378 -- Get the discount rate and funds available
1379 -- We need to multiply the PC rate by 100 because the scenario discount rate
1380 -- assume the number is a whole number, however the PC is a decimal number
1381 BEGIN
1382 SELECT pc_discount_rate * 100,
1383 pc_funding
1384 INTO l_discount_rate,
1385 l_scenario_funds
1386 FROM fpa_aw_pc_disc_funds_v
1387 WHERE planning_cycle = l_pc_id;
1388 EXCEPTION
1389 WHEN OTHERS THEN
1390 null;
1391 END;
1392
1393 -- Initial Scenario description is the same as name
1394 l_scenario_desc := l_scenario_name;
1395
1396 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1397 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1398 'fpa.sql.fpa_main_process_pvt.call_create_initial_scenario.begin',
1399 'Calling fpa_scenario_pvt.create_scenario.');
1400 END IF;
1401
1402 -- Create a scenario
1403 fpa_scenario_pvt.create_scenario(
1404 p_api_version => 1.0,
1405 p_scenario_name => l_scenario_name,
1406 p_scenario_desc => l_scenario_desc,
1407 p_pc_id => l_pc_id,
1408 x_scenario_id => l_scenario_id,
1409 x_return_status => l_return_status,
1410 x_msg_count => l_msg_count,
1411 x_msg_data => l_msg_data);
1412
1413 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1414 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1415 'fpa.sql.fpa_main_process_pvt.call_create_initial_scenario.begin',
1416 'Calling fpa_scenario_pvt.update_scenario_disc_rate.');
1417 END IF;
1418
1419 -- Set the discount rate
1420 fpa_scenario_pvt.update_scenario_disc_rate(
1421 p_api_version => 1.0,
1422 p_scenario_id => l_scenario_id,
1423 p_discount_rate => l_discount_rate,
1424 x_return_status => l_return_status,
1425 x_msg_count => l_msg_count,
1426 x_msg_data => l_msg_data);
1427
1428 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1429 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1430 'fpa.sql.fpa_main_process_pvt.call_create_initial_scenario.',
1431 'Calling fpa_scenario_pvt.update_scenario_funds_avail.');
1432 END IF;
1433
1434 -- Set the funds available
1435 fpa_scenario_pvt.update_scenario_funds_avail(
1436 p_api_version => 1.0,
1437 p_scenario_id => l_scenario_id,
1438 p_scenario_funds => l_scenario_funds,
1439 x_return_status => l_return_status,
1440 x_msg_count => l_msg_count,
1441 x_msg_data => l_msg_data);
1442
1443
1444 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1445 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1446 'fpa.sql.fpa_main_process_pvt.call_create_initial_scenario.',
1447 'Calling fpa_scenario_pvt.update_scenario_initial_flag.');
1448 END IF;
1449
1450 -- Set scenario just created as the Initial Scenario
1451 fpa_scenario_pvt.update_scenario_initial_flag(
1452 p_api_version => 1.0,
1453 p_scenario_id => l_scenario_id,
1454 x_return_status => l_return_status,
1455 x_msg_count => l_msg_count,
1456 x_msg_data => l_msg_data);
1457
1458 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1459 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1460 'fpa.sql.fpa_main_process_pvt.call_create_initial_scenario.',
1461 'Calling fpa_scenario_pvt.update_scenario_working_flag.');
1462 END IF;
1463
1464 -- Set scenario just created as the Working Scenario.
1465 fpa_scenario_pvt.update_scenario_working_flag(
1466 p_api_version => 1.0,
1467 p_scenario_id => l_scenario_id,
1468 x_return_status => l_return_status,
1469 x_msg_count => l_msg_count,
1470 x_msg_data => l_msg_data);
1471
1472 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1473 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1474 'fpa.sql.fpa_main_process_pvt.call_create_initial_scenario.',
1475 'Calling Fpa_Project_Pvt.Load_Project_Details_Aw.');
1476 END IF;
1477
1478 Fpa_Project_Pvt.Load_Project_Details_Aw(
1479 p_api_version => 1.0,
1480 p_init_msg_list => FND_API.G_FALSE,
1481 p_type => 'COLLECT',
1482 p_scenario_id => l_scenario_id,
1483 p_projects => null,
1484 x_return_status => l_return_status,
1485 x_msg_count => l_msg_count,
1486 x_msg_data => l_msg_data
1487 );
1488
1489 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1490 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1491 'fpa.sql.fpa_main_process_pvt.call_create_initial_scenario.',
1492 'Calling fpa_scenario_pvt.calc_scenario_data.');
1493 END IF;
1494
1495 fpa_scenario_pvt.calc_scenario_data
1496 (
1497 p_api_version => 1.0,
1498 p_scenario_id => l_scenario_id,
1499 p_project_id => null,
1500 p_class_code_id => null,
1501 p_data_to_calc => 'ALL',
1502 x_return_status => l_return_status,
1503 x_msg_count => l_msg_count,
1504 x_msg_data => l_msg_data
1505 );
1506
1507 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1508 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1509 'fpa.sql.fpa_main_process_pvt.call_create_initial_scenario.',
1510 'Calling fpa_scorecard_pvt.calc_scenario_data.');
1511 END IF;
1512
1513 fpa_scorecards_pvt.Calc_Scenario_Wscores_Aw
1514 (
1515 p_api_version => 1.0,
1516 p_init_msg_list => FND_API.G_FALSE,
1517 p_scenario_id => l_scenario_id,
1518 x_return_status => l_return_status,
1519 x_msg_count => l_msg_count,
1520 x_msg_data => l_msg_data
1521 );
1522
1523
1524 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1525 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1526 'fpa.sql.fpa_main_process_pvt.call_create_initial_scenario.begin',
1527 'Calling wf_engine.SetItemAttrNumber.');
1528 END IF;
1529
1530 wf_engine.SetItemAttrNumber(itemtype => p_itemtype,
1531 itemkey => p_itemkey,
1532 aname => 'FPA_SCENARIO_ID',
1533 avalue => l_scenario_id);
1534
1535 x_resultout := null;
1536
1537 EXCEPTION
1538 WHEN OTHERS THEN
1539 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1540 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1541 'fpa.sql.fpa_main_process_pvt.call_create_initial_scenario.end',
1542 SQLERRM);
1543 END IF;
1544
1545 wf_core.context('FPA_MAIN_PROCESS_PVT',
1546 'CALL_CREATE_INITIAL_SCENARIO',
1547 SQLERRM);
1548
1549 /*
1550 -- Detach AW Workspace
1551 fpa_utilities_pvt.detach_AW(p_api_version => 1.0,
1552 x_return_status => l_return_status,
1553 x_msg_count => l_msg_count,
1554 x_msg_data => l_msg_data);
1555 */
1556 RAISE;
1557 END CALL_CREATE_INITIAL_SCENARIO;
1558
1559
1560
1561 /* Set Status */
1562 PROCEDURE CALL_SET_STATUS(p_itemtype IN VARCHAR2,
1563 p_itemkey IN VARCHAR2,
1564 p_actid IN NUMBER,
1565 p_funcmode IN VARCHAR2,
1566 x_resultout OUT NOCOPY VARCHAR2)
1567 IS
1568
1569 l_return_status VARCHAR2(1) := null;
1570 l_msg_count NUMBER := null;
1571 l_msg_data VARCHAR2(2000) := null;
1572 l_pc_id NUMBER := null;
1573 l_pc_status fpa_aw_pc_info_v.pc_status%TYPE := null;
1574
1575 BEGIN
1576
1577 -- Get the Planning Cycle ID
1578 l_pc_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
1579 itemkey => p_itemkey,
1580 aname => 'FPA_PC_ID');
1581
1582 l_pc_status := 'CREATED';
1583
1584 -- Call the Set Status API to set the Planning Cycle
1585 fpa_planningcycle_pvt.set_pc_status(p_api_version => 1.0,
1586 p_pc_id => l_pc_id,
1587 p_pc_status_code => l_pc_status,
1588 x_return_status => l_return_status,
1589 x_msg_count => l_msg_count,
1590 x_msg_data => l_msg_data);
1591
1592 x_resultout := wf_engine.eng_null;
1593
1594 EXCEPTION
1595 WHEN OTHERS THEN
1596 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1597 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1598 'fpa.sql.fpa_main_process_pvt.call_set_status.end',
1599 SQLERRM);
1600 END IF;
1601 wf_core.context('FPA_MAIN_PROCESS_PVT', 'CALL_SET_STATUS', SQLERRM);
1602 RAISE;
1603 END CALL_SET_STATUS;
1604
1605
1606
1607 /* Call Project Sets */
1608 -- calls the Project Sets API
1609 PROCEDURE CALL_PROJECT_SETS(p_itemtype IN VARCHAR2,
1610 p_itemkey IN VARCHAR2,
1611 p_actid IN NUMBER,
1612 p_funcmode IN VARCHAR2,
1613 x_resultout OUT NOCOPY VARCHAR2)
1614 IS
1615
1616 l_return_status VARCHAR2(1) := null;
1617 l_msg_count NUMBER := null;
1618 l_msg_data VARCHAR2(2000) := null;
1619 l_scen_id NUMBER := null;
1620
1621 BEGIN
1622
1623 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1624 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1625 'fpa.sql.fpa_main_process_pvt.call_project_sets.begin',
1626 'Entering fpa_main_process_pvt.call_project_sets');
1627 END IF;
1628
1629 -- Gets the Scenario ID
1630 l_scen_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
1631 itemkey => p_itemkey,
1632 aname => 'FPA_SCENARIO_ID');
1633
1634
1635 -- Call the Project Sets API
1636 fpa_portfolio_project_sets_pvt.add_project_set_lines(
1637 p_api_version => 1.0,
1638 p_scen_id => l_scen_id,
1639 x_return_status => l_return_status,
1640 x_msg_data => l_msg_data,
1641 x_msg_count => l_msg_count);
1642
1643 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1644 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1645 'fpa.sql.fpa_main_process_pvt.call_project_sets.end',
1646 'Exiting fpa_main_process_pvt.call_project_sets');
1647 END IF;
1648
1649 x_resultout := wf_engine.eng_null;
1650
1651 EXCEPTION
1652 WHEN OTHERS THEN
1653 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1654 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1655 'fpa.sql.fpa_main_process_pvt.call_project_sets.end',
1656 SQLERRM);
1657 END IF;
1658 wf_core.context('FPA_MAIN_PROCESS_PVT', 'CALL_PROJECT_SETS', SQLERRM);
1659
1660 /*
1661 -- Detach AW Workspace
1662 fpa_utilities_pvt.detach_AW(p_api_version => 1.0,
1663 x_return_status => l_return_status,
1664 x_msg_count => l_msg_count,
1665 x_msg_data => l_msg_data);
1666 */
1667 RAISE;
1668 END CALL_PROJECT_SETS;
1669
1670
1671
1672 /* Is Plan Approved */
1673 -- Checks if the Plan is approved
1674 PROCEDURE IS_PLAN_APPROVED(p_itemtype IN VARCHAR2,
1675 p_itemkey IN VARCHAR2,
1676 p_actid IN NUMBER,
1677 p_funcmode IN VARCHAR2,
1678 x_resultout OUT NOCOPY VARCHAR2)
1679 IS
1680
1681 l_is_approved VARCHAR2(1) := 'N';
1682
1683 BEGIN
1684
1685 l_is_approved := wf_engine.GetItemAttrText(itemtype => p_itemtype,
1686 itemkey => p_itemkey,
1687 aname => 'FPA_APPROVE_PC');
1688
1689 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1690 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1691 'fpa.sql.fpa_main_process_pvt.is_plan_approved.begin',
1692 'value of l_is_approved'|| l_is_approved||'p_itemkey '||p_itemkey||'p_funcmode '||p_funcmode);
1693 END IF;
1694
1695 IF (l_is_approved = 'Y') THEN
1696 x_resultout := 'Y';
1697 ELSE
1698 x_resultout := 'N';
1699 END IF;
1700
1701 EXCEPTION
1702 WHEN OTHERS THEN
1703 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1704 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1705 'fpa.sql.fpa_main_process_pvt.is_plan_approved.end',
1706 SQLERRM);
1707 END IF;
1708 wf_core.context('FPA_MAIN_PROCESS_PVT', 'IS_PLAN_APPROVED', SQLERRM);
1709 RAISE;
1710 END IS_PLAN_APPROVED;
1711
1712
1713
1714 -- Sets the Planning Cycle Status to ANALYSIS
1715 PROCEDURE SET_STATUS_ANALYSIS(p_itemtype IN VARCHAR2,
1716 p_itemkey IN VARCHAR2,
1717 p_actid IN NUMBER,
1718 p_funcmode IN VARCHAR2,
1719 x_resultout OUT NOCOPY VARCHAR2)
1720 IS
1721
1722 l_return_status VARCHAR2(1) := null;
1723 l_msg_count NUMBER := null;
1724 l_msg_data VARCHAR2(2000) := null;
1725 l_pc_id NUMBER := null;
1726 l_pc_status fpa_aw_pc_info_v.pc_status%TYPE := null;
1727
1728 BEGIN
1729
1730 -- Get the Planning Cycle ID
1731 l_pc_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
1732 itemkey => p_itemkey,
1733 aname => 'FPA_PC_ID');
1734
1735 l_pc_status := 'ANALYSIS';
1736
1737
1738 -- Call the Set Status API to set the Planning Cycle
1739 fpa_planningcycle_pvt.set_pc_status(p_api_version => 1.0,
1740 p_pc_id => l_pc_id,
1741 p_pc_status_code => l_pc_status,
1742 x_return_status => l_return_status,
1743 x_msg_count => l_msg_count,
1744 x_msg_data => l_msg_data);
1745
1746 x_resultout := wf_engine.eng_null;
1747
1748 EXCEPTION
1749 WHEN OTHERS THEN
1750 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1751 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1752 'fpa.sql.fpa_main_process_pvt.set_status_analysis.end',
1753 SQLERRM);
1754 END IF;
1755 wf_core.context('FPA_MAIN_PROCESS_PVT', 'SET_STATUS_ANALYSIS', SQLERRM);
1756
1757 RAISE;
1758 END SET_STATUS_ANALYSIS;
1759
1760 -- Sets the Planning Cycle Status to APPROVED
1761 PROCEDURE SET_STATUS_APPROVED(p_itemtype IN VARCHAR2,
1762 p_itemkey IN VARCHAR2,
1763 p_actid IN NUMBER,
1764 p_funcmode IN VARCHAR2,
1765 x_resultout OUT NOCOPY VARCHAR2)
1766 IS
1767
1768 l_return_status VARCHAR2(1) := null;
1769 l_msg_count NUMBER := null;
1770 l_msg_data VARCHAR2(2000) := null;
1771 l_pc_id NUMBER := null;
1772 l_pc_status fpa_aw_pc_info_v.pc_status%TYPE := null;
1773 l_pc_approve VARCHAR2(1) := null;
1774
1775 BEGIN
1776
1777 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1778 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1779 'fpa.sql.fpa_main_process_pvt.set_status_approved.begin',
1780 'Entering fpa_main_process_pvt.set_status_approved p_itemkey'||p_itemkey);
1781 END IF;
1782
1783 -- Get the Planning Cycle ID
1784 l_pc_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
1785 itemkey => p_itemkey,
1786 aname => 'FPA_PC_ID');
1787
1788 l_pc_approve := wf_engine.GetItemAttrText(itemtype => p_itemtype,
1789 itemkey => p_itemkey,
1790 aname => 'FPA_APPROVE_PC');
1791
1792 IF l_pc_approve = 'Y' THEN
1793 l_pc_status := 'APPROVED';
1794 ELSE
1795 l_pc_status := 'ANALYSIS';
1796 END IF;
1797
1798
1799 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1800 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1801 'fpa.sql.fpa_main_process_pvt.set_status_approved',
1802 'Value of l_pc_id ='||l_pc_id||' Value of l_pc_status='||l_pc_status||' Value of l_pc_approve ='||l_pc_approve);
1803 END IF;
1804
1805
1806 -- Call the Set Status API to set the Planning Cycle
1807 fpa_planningcycle_pvt.set_pc_status(p_api_version => 1.0,
1808 p_pc_id => l_pc_id,
1809 p_pc_status_code => l_pc_status,
1810 x_return_status => l_return_status,
1811 x_msg_count => l_msg_count,
1812 x_msg_data => l_msg_data);
1813
1814 x_resultout := wf_engine.eng_null;
1815
1816 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1817 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1818 'fpa.sql.fpa_main_process_pvt.set_status_approved.end',
1819 'Exiting fpa_main_process_pvt.set_status_approved');
1820 END IF;
1821
1822 EXCEPTION
1823 WHEN OTHERS THEN
1824 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1825 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1826 'fpa.sql.fpa_main_process_pvt.set_status_approved.end',
1827 SQLERRM);
1828 END IF;
1829 wf_core.context('FPA_MAIN_PROCESS_PVT', 'SET_STATUS_APPROVED', SQLERRM);
1830
1831 RAISE;
1832 END SET_STATUS_APPROVED;
1833
1834 -- Sets the Planning Cycle Status to CLOSED
1835 PROCEDURE SET_STATUS_CLOSED(p_itemtype IN VARCHAR2,
1836 p_itemkey IN VARCHAR2,
1837 p_actid IN NUMBER,
1838 p_funcmode IN VARCHAR2,
1839 x_resultout OUT NOCOPY VARCHAR2)
1840 IS
1841
1842 l_return_status VARCHAR2(1) := null;
1843 l_msg_count NUMBER := null;
1844 l_msg_data VARCHAR2(2000) := null;
1845 l_pc_id NUMBER := null;
1846 l_pc_status fpa_aw_pc_info_v.pc_status%TYPE := null;
1847
1848 BEGIN
1849
1850 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1851 FND_LOG.String
1852 ( FND_LOG.LEVEL_PROCEDURE,
1853 'fpa.sql.FPA_main_process_pvt.set_status_closed.begin',
1854 'Calling FPA_PlanningCycle_Pvt.set_pc_status'
1855 );
1856 END IF;
1857
1858 -- Get the Planning Cycle ID
1859 l_pc_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
1860 itemkey => p_itemkey,
1861 aname => 'FPA_PC_ID');
1862
1863 l_pc_status := 'CLOSED';
1864
1865 -- Call the Set Status API to set the Planning Cycle
1866 fpa_planningcycle_pvt.set_pc_status(p_api_version => 1.0,
1867 p_pc_id => l_pc_id,
1868 p_pc_status_code => l_pc_status,
1869 x_return_status => l_return_status,
1870 x_msg_count => l_msg_count,
1871 x_msg_data => l_msg_data);
1872
1873 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1874 FND_LOG.String
1875 ( FND_LOG.LEVEL_PROCEDURE,
1876 'FPA_main_process_pvt.set_status_closed',
1877 'Calling FPA_PlanningCycle_Pvt.Set_Pc_Approved_Flag.'
1878 );
1879 END IF;
1880
1881 FPA_PlanningCycle_Pvt.Set_Pc_Last_Flag
1882 (
1883 p_api_version => 1.0,
1884 p_pc_id => l_pc_id,
1885 x_return_status => l_return_status,
1886 x_msg_data => l_msg_data,
1887 x_msg_count => l_msg_count
1888 );
1889
1890 x_resultout := wf_engine.eng_null;
1891
1892 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1893 FND_LOG.String
1894 ( FND_LOG.LEVEL_PROCEDURE,
1895 'fpa.sql.FPA_main_process_pvt.set_status_closed.end',
1896 'FPA_main_process_pvt.set_status_closed.end'
1897 );
1898 END IF;
1899
1900 EXCEPTION
1901 WHEN OTHERS THEN
1902 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1903 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
1904 'fpa.sql.fpa_main_process_pvt.set_status_closed.end',
1905 SQLERRM);
1906 END IF;
1907 wf_core.context('FPA_MAIN_PROCESS_PVT', 'SET_STATUS_CLOSED', SQLERRM);
1908
1909 RAISE;
1910 END SET_STATUS_CLOSED;
1911
1912
1913
1914 -- Sets the Planning Cycle Status to COLLECTING
1915 PROCEDURE SET_STATUS_COLLECTING(p_itemtype IN VARCHAR2,
1916 p_itemkey IN VARCHAR2,
1917 p_actid IN NUMBER,
1918 p_funcmode IN VARCHAR2,
1919 x_resultout OUT NOCOPY VARCHAR2)
1920 IS
1921
1922 l_return_status VARCHAR2(1) := null;
1923 l_msg_count NUMBER := null;
1924 l_msg_data VARCHAR2(2000) := null;
1925 l_pc_id NUMBER := null;
1926 l_pc_status fpa_aw_pc_info_v.pc_status%TYPE := null;
1927
1928 BEGIN
1929
1930 -- Get the Planning Cycle ID
1931 l_pc_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
1932 itemkey => p_itemkey,
1933 aname => 'FPA_PC_ID');
1934
1935 l_pc_status := 'COLLECTING';
1936
1937 -- Call the Set Status API to set the Planning Cycle
1938 fpa_planningcycle_pvt.set_pc_status(p_api_version => 1.0,
1939 p_pc_id => l_pc_id,
1940 p_pc_status_code => l_pc_status,
1941 x_return_status => l_return_status,
1942 x_msg_count => l_msg_count,
1943 x_msg_data => l_msg_data);
1944
1945 x_resultout := wf_engine.eng_null;
1946
1947 EXCEPTION
1948 WHEN OTHERS THEN
1949 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1950 fnd_log.string(
1951 FND_LOG.LEVEL_PROCEDURE,
1952 'fpa.sql.fpa_main_process_pvt.set_status_collecting.end',
1953 SQLERRM);
1954 END IF;
1955 wf_core.context('FPA_MAIN_PROCESS_PVT',
1956 'SET_STATUS_COLLECTING',
1957 SQLERRM);
1958
1959 RAISE;
1960 END SET_STATUS_COLLECTING;
1961
1962
1963
1964 -- Sets the Planning Cycle Status to SUBMITTED
1965 PROCEDURE SET_STATUS_SUBMITTED(p_itemtype IN VARCHAR2,
1966 p_itemkey IN VARCHAR2,
1967 p_actid IN NUMBER,
1968 p_funcmode IN VARCHAR2,
1969 x_resultout OUT NOCOPY VARCHAR2)
1970 IS
1971
1972 l_return_status VARCHAR2(1) := null;
1973 l_msg_count NUMBER := null;
1974 l_msg_data VARCHAR2(2000) := null;
1975 l_pc_id NUMBER := null;
1976 l_pc_status fpa_aw_pc_info_v.pc_status%TYPE := null;
1977
1978 BEGIN
1979
1980 -- Get the Planning Cycle ID
1981 l_pc_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
1982 itemkey => p_itemkey,
1983 aname => 'FPA_PC_ID');
1984
1985 l_pc_status := 'SUBMITTED';
1986
1987
1988 -- Call the Set Status API to set the Planning Cycle
1989 fpa_planningcycle_pvt.set_pc_status(p_api_version => 1.0,
1990 p_pc_id => l_pc_id,
1991 p_pc_status_code => l_pc_status,
1992 x_return_status => l_return_status,
1993 x_msg_count => l_msg_count,
1994 x_msg_data => l_msg_data);
1995
1996 x_resultout := wf_engine.eng_null;
1997
1998 EXCEPTION
1999 WHEN OTHERS THEN
2000 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2001 fnd_log.string(
2002 FND_LOG.LEVEL_PROCEDURE,
2003 'fpa.sql.fpa_main_process_pvt.set_status_submitted.end',
2004 SQLERRM);
2005 END IF;
2006 wf_core.context('FPA_MAIN_PROCESS_PVT', 'SET_STATUS_SUBMITTED', SQLERRM);
2007
2008 /*
2009 -- Detach AW Workspace
2010 fpa_utilities_pvt.detach_AW(p_api_version => 1.0,
2011 x_return_status => l_return_status,
2012 x_msg_count => l_msg_count,
2013 x_msg_data => l_msg_data);
2014 */
2015 RAISE;
2016 END SET_STATUS_SUBMITTED;
2017
2018
2019 /* Workflow business events */
2020 /* User force action */
2021 -- pings the User Action business event
2022 PROCEDURE FORCE_USER_ACTION(p_itemkey IN VARCHAR2,
2023 p_event_name IN VARCHAR2,
2024 x_return_status OUT NOCOPY VARCHAR2,
2025 x_msg_count OUT NOCOPY NUMBER,
2026 x_msg_data OUT NOCOPY VARCHAR2)
2027 IS
2028 BEGIN
2029
2030 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2031 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2032 'fpa.sql.fpa_main_process_pvt.force_user_action.begin',
2033 'Entering fpa_main_process_pvt.force_user_action');
2034 END IF;
2035
2036 -- Raise the user forced event
2037 wf_event.raise(p_event_name, p_itemkey, null, null);
2038
2039 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2040 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2041 'fpa.sql.fpa_main_process_pvt.force_user_action.end',
2042 'Exiting fpa_main_process_pvt.force_user_action');
2043 END IF;
2044
2045 EXCEPTION
2046 WHEN OTHERS THEN
2047 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2048 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2049 'fpa.sql.fpa_main_process_pvt.force_user_action.end',
2050 SQLERRM);
2051 END IF;
2052 wf_core.context('FPA_MAIN_PROCESS_PVT', 'FORCE_USER_ACTION', SQLERRM);
2053 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2054 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
2055 p_data => x_msg_data
2056 );
2057 RAISE;
2058 END FORCE_USER_ACTION;
2059
2060
2061
2062 /* Workflow business events */
2063 /* Submit plan */
2064 -- pings the Submit Plan business event
2065 PROCEDURE SUBMIT_PLAN(p_itemkey IN VARCHAR2,
2066 p_event_name IN VARCHAR2,
2067 x_return_status OUT NOCOPY VARCHAR2,
2068 x_msg_count OUT NOCOPY NUMBER,
2069 x_msg_data OUT NOCOPY VARCHAR2)
2070 IS
2071 l_eventkey VARCHAR2(30) := null;
2072
2073 l_return_status VARCHAR2(1) ;
2074 l_msg_count NUMBER ;
2075 l_msg_data VARCHAR2(2000);
2076
2077 BEGIN
2078
2079 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2080 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2081 'fpa.sql.fpa_main_process_pvt.submit_plan.begin',
2082 'Entering fpa_main_process_pvt.submit_plan value of p_itemkey'||p_itemkey);
2083 END IF;
2084
2085 l_eventkey := 'FPAL' ||p_itemkey;
2086
2087 -- Attach AW Workspace
2088 fpa_utilities_pvt.attach_AW(p_api_version => 1.0,
2089 p_attach_mode => 'rw',
2090 x_return_status => l_return_status,
2091 x_msg_count => l_msg_count,
2092 x_msg_data => l_msg_data);
2093
2094
2095 -- Raise the submit plan event
2096 wf_event.raise(p_event_name => 'oracle.apps.fpa.event.submit.submitplan',
2097 p_event_key => l_eventkey );
2098
2099 dbms_aw.execute('UPDATE');
2100 COMMIT;
2101
2102 -- Detach AW Workspace
2103 fpa_utilities_pvt.detach_AW(p_api_version => 1.0,
2104 x_return_status => l_return_status,
2105 x_msg_count => l_msg_count,
2106 x_msg_data => l_msg_data);
2107
2108 -- COMMIT;
2109
2110 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2111 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2112 'fpa.sql.fpa_main_process_pvt.submit_plan.end',
2113 'Exiting fpa_main_process_pvt.submit_plan');
2114 END IF;
2115
2116 EXCEPTION
2117 WHEN OTHERS THEN
2118 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2119 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2120 'fpa.sql.fpa_main_process_pvt.submit_plan.end',
2121 SQLERRM);
2122 END IF;
2123 wf_core.context('FPA_MAIN_PROCESS_PVT', 'SUBMIT_PLAN', SQLERRM);
2124 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2125 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
2126 p_data => x_msg_data
2127 );
2128 RAISE;
2129 END SUBMIT_PLAN;
2130
2131
2132 /* Workflow business events */
2133 /* Approve or Reject a plan */
2134 -- pings the Approve Reject Plan business event
2135 PROCEDURE APPROVE_REJECT_PLAN(p_itemkey IN VARCHAR2,
2136 p_event_name IN VARCHAR2,
2137 x_return_status OUT NOCOPY VARCHAR2,
2138 x_msg_count OUT NOCOPY NUMBER,
2139 x_msg_data OUT NOCOPY VARCHAR2)
2140 IS
2141 l_eventkey VARCHAR2(30);
2142 l_return_status VARCHAR2(1) ;
2143 l_msg_count NUMBER ;
2144 l_msg_data VARCHAR2(2000);
2145 l_sce_id number;
2146
2147 BEGIN
2148
2149 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2150 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2151 'fpa.sql.fpa_main_process_pvt.approve_reject_plan.begin',
2152 'Entering fpa_main_process_pvt.approve_reject_plan ACTION : '||p_event_name);
2153 END IF;
2154
2155 l_eventkey := 'FPAL' ||p_itemkey;
2156
2157 IF p_event_name = 'FPA_APPROVE_PLAN' THEN
2158 wf_engine.SetItemAttrText(itemtype => 'FPAPJP',
2159 itemkey => l_eventkey ,
2160 aname => 'FPA_APPROVE_PC',
2161 avalue => 'Y');
2162 ELSIF p_event_name = 'FPA_REJECT_PLAN' or p_event_name = 'FPA_WITHDRAW_PLAN' THEN
2163 wf_engine.SetItemAttrText(itemtype => 'FPAPJP',
2164 itemkey => l_eventkey ,
2165 aname => 'FPA_APPROVE_PC',
2166 avalue => 'N');
2167 END IF;
2168
2169 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2170 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2171 'fpa.sql.fpa_main_process_pvt.approve_reject_plan.',
2172 'Calling fpa_utilities_pvt.attach_AW');
2173 END IF;
2174
2175 -- Attach AW Workspace
2176 fpa_utilities_pvt.attach_AW(p_api_version => 1.0,
2177 p_attach_mode => 'rw',
2178 x_return_status => l_return_status,
2179 x_msg_count => l_msg_count,
2180 x_msg_data => l_msg_data);
2181
2182
2183 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2184 FND_LOG.String
2185 ( FND_LOG.LEVEL_PROCEDURE,
2186 'FPA_Main_Process_Pvt.approve_reject_plan.',
2187 'Obtaining Approved Scenario Id for current Planning Cycle.'
2188 );
2189 END IF;
2190
2191 -- For the passed planning cycle get the scenario approved
2192 select scenario
2193 into l_sce_id
2194 from fpa_aw_sce_info_v
2195 where approved_flag = 1
2196 and planning_cycle = p_itemkey;
2197
2198 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2199 FND_LOG.String
2200 ( FND_LOG.LEVEL_PROCEDURE,
2201 'FPA_Main_Process_Pvt.approve_reject_plan.',
2202 'Approved Scenario Id '||l_sce_id
2203 );
2204 END IF;
2205
2206
2207 IF p_event_name = 'FPA_APPROVE_PLAN' THEN
2208 -- Call API to update approved Scores
2209 if l_sce_id is not null then
2210
2211 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2212 FND_LOG.String
2213 ( FND_LOG.LEVEL_PROCEDURE,
2214 'FPA_Main_Process_Pvt.approve_reject_plan.',
2215 'Calling FPA_SCORECARDS_PVT.Update_Scenario_App_Scores.'
2216 );
2217 END IF;
2218
2219 FPA_SCORECARDS_PVT.Update_Scenario_App_Scores
2220 (p_api_version => 1.0,
2221 p_init_msg_list => FND_API.G_FALSE,
2222 p_scenario_id => l_sce_id,
2223 x_return_status => l_return_status,
2224 x_msg_count => l_msg_count,
2225 x_msg_data => l_msg_data
2226 );
2227
2228 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2229 FND_LOG.String
2230 ( FND_LOG.LEVEL_PROCEDURE,
2231 'FPA_Main_Process_Pvt.approve_reject_plan.',
2232 'Calling FPA_Project_PVT.UPDATE_PROJ_FUNDING_STATUS.'
2233 );
2234 END IF;
2235
2236 FPA_Project_PVT.UPDATE_PROJ_FUNDING_STATUS
2237 (
2238 p_api_version => 1.0,
2239 p_init_msg_list => FND_API.G_FALSE,
2240 p_commit => FND_API.G_FALSE,
2241 p_appr_scenario_id => l_sce_id,
2242 x_return_status => l_return_status,
2243 x_msg_count => l_msg_count,
2244 x_msg_data => l_msg_data);
2245
2246 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2247 FND_LOG.String
2248 ( FND_LOG.LEVEL_PROCEDURE,
2249 'FPA_Main_Process_Pvt.approve_reject_plan.',
2250 'Calling FPA_Portfolio_Project_sets_Pvt.add_project_set_lines'
2251 );
2252 END IF;
2253
2254 FPA_PORTFOLIO_PROJECT_SETS_PVT.ADD_PROJECT_SET_LINES
2255 (p_api_version => 1.0,
2256 p_scen_id => l_sce_id,
2257 x_return_status => l_return_status,
2258 x_msg_count => l_msg_count,
2259 x_msg_data => l_msg_data);
2260
2261 end if;
2262 elsif p_event_name = 'FPA_REJECT_PLAN' or p_event_name = 'FPA_WITHDRAW_PLAN' THEN
2263 -- Bug 4324881
2264 -- Reject Plan should reset the recommended and approved flags for all scenarios in the plan
2265 -- A/w is already attached in R/w mode
2266 -- As a quick fix, we are executing OLAP dml commands to reset the flags.
2267 -- fpa_scenario_pvt will be updated with new procedures
2268 -- that may be required to reset the recomm/appr flags for all scenarios
2269
2270 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2271 FND_LOG.String
2272 ( FND_LOG.LEVEL_PROCEDURE,
2273 'FPA_Main_Process_Pvt.approve_reject_plan.',
2274 'Reject Plan: Approved Scenario Id '||l_sce_id
2275 );
2276 END IF;
2277 -- dbms_aw.execute('ALLSTAT');
2278 dbms_aw.execute('LMT scenario_d to '|| l_sce_id );
2279 -- get the PC
2280 dbms_aw.execute('LMT planning_cycle_d TO scenario_d' );
2281 -- get all scenarios that belong to the PC
2282 dbms_aw.execute('LMT scenario_d to planning_cycle_d' );
2283 -- reset flags to na for all scenarios
2284 dbms_aw.execute('scenario_approved_flag_m = na');
2285 dbms_aw.execute('scenario_recommended_flag_m = na');
2286
2287 end if;
2288
2289
2290 -- dbms_aw.execute('ALLSTAT');
2291
2292 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2293 FND_LOG.String
2294 ( FND_LOG.LEVEL_PROCEDURE,
2295 'FPA_Main_Process_Pvt.approve_reject_plan.',
2296 'Approve/ Reject Plan procedure calls ');
2297 END IF;
2298
2299 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2300 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2301 'fpa.sql.fpa_main_process_pvt.approve_reject_plan.',
2302 'Calling wf_event.raise');
2303 END IF;
2304
2305 -- Raise the submit plan event
2306 wf_event.raise(p_event_name => 'oracle.apps.fpa.event.approve.approvepushdown',
2307 p_event_key => l_eventkey );
2308
2309 -- Update AW
2310 dbms_aw.execute('UPDATE');
2311 COMMIT;
2312
2313
2314 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2315 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2316 'fpa.sql.fpa_main_process_pvt.approve_reject_plan.',
2317 'Calling fpa_utilities_pvt.detach_AW');
2318 END IF;
2319
2320 -- Detach AW Workspace
2321 fpa_utilities_pvt.detach_AW(p_api_version => 1.0,
2322 x_return_status => l_return_status,
2323 x_msg_count => l_msg_count,
2324 x_msg_data => l_msg_data);
2325
2326
2327
2328 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2329 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2330 'fpa.sql.fpa_main_process_pvt.approve_reject_plan.end',
2331 'Exiting fpa_main_process_pvt.approve_reject_plan');
2332 END IF;
2333
2334 EXCEPTION
2335 WHEN OTHERS THEN
2336 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2337 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2338 'fpa.sql.fpa_main_process_pvt.approve_reject_plan.end',
2339 SQLERRM);
2340 END IF;
2341 -- Bug 4331948 If Exception raised during Approve/Reject Plan, A/w should be detached
2342 -- Detach AW Workspace
2343 Fpa_Utilities_Pvt.detach_AW
2344 (
2345 p_api_version => 1.0,
2346 x_return_status => x_return_status,
2347 x_msg_count => x_msg_count,
2348 x_msg_data => x_msg_data
2349 );
2350
2351 wf_core.context('FPA_MAIN_PROCESS_PVT', 'APPROVE_REJECT_PLAN', SQLERRM);
2352 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2353 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
2354 p_data => x_msg_data
2355 );
2356 RAISE;
2357 END APPROVE_REJECT_PLAN;
2358
2359 --Procedure to copy Projects from last planning cycle of current portfolio
2360
2361 PROCEDURE COPY_PROJ_FROM_PREV_PC(p_itemtype IN VARCHAR2,
2362 p_itemkey IN VARCHAR2,
2363 p_actid IN NUMBER,
2364 p_funcmode IN VARCHAR2,
2365 x_resultout OUT NOCOPY VARCHAR2)
2366 IS
2367
2368 l_return_status VARCHAR2(1);
2369 l_msg_count NUMBER ;
2370 l_msg_data VARCHAR2(2000);
2371 l_last_pc_id NUMBER;
2372 l_count NUMBER;
2373 l_project_id_tbl SYSTEM.pa_num_tbl_type;
2374
2375 BEGIN
2376
2377 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2378 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2379 'fpa.sql.fpa_main_process_pvt.copy_proj_from_prev_pc.begin',
2380 'Entering fpa_main_process_pvt.copy_proj_from_prev_pc ');
2381 END IF;
2382
2383 -- Get the Planning Cycle ID
2384 l_last_pc_id := NULL;
2385 l_last_pc_id := wf_engine.GetItemAttrNumber(itemtype => p_itemtype,
2386 itemkey => p_itemkey,
2387 aname => 'FPA_LAST_PC_ID');
2388
2389 IF NVL(l_last_pc_id,-1) <> -1 THEN --Bug 4237493 : Introduced for scenario where there is no last PC
2390
2391 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2392 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2393 'fpa.sql.fpa_main_process_pvt.copy_proj_from_prev_pc.begin',
2394 'Setting Last Planning cycle ID to the planning_cycle_d');
2395 END IF;
2396
2397 dbms_aw.execute('PUSH planning_cycle_d');
2398 dbms_aw.execute('LMT planning_cycle_d TO ' || l_last_pc_id );
2399
2400 SELECT proj.project BULK COLLECT
2401 INTO l_project_id_tbl
2402 From fpa_aw_projs_v proj
2403 WHERE proj.planning_cycle = l_last_pc_id;
2404
2405 dbms_aw.execute('POP planning_cycle_d');
2406
2407 l_count :=0;
2408 l_count := l_project_id_tbl.COUNT;
2409
2410 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2411 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2412 'fpa.sql.fpa_main_process_pvt.copy_proj_from_prev_pc.begin',
2413 'NUmber of projects fetched from last PC '||l_count);
2414 END IF;
2415
2416 FOR i in 1 .. l_count
2417 LOOP
2418
2419 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2420 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2421 'fpa.sql.fpa_main_process_pvt.copy_proj_from_prev_pc.begin',
2422 'Looping for l_project_id_tbl(i) = '||l_project_id_tbl(i));
2423 END IF;
2424
2425 FPA_PROJECT_PVT.Submit_Project_Aw(
2426 p_api_version => 1.0,
2427 p_init_msg_list => FND_API.G_FALSE,
2428 p_commit => FND_API.G_FALSE,
2429 p_project_id => l_project_id_tbl(i),
2430 x_return_status => l_return_status,
2431 x_msg_count => l_msg_count,
2432 x_msg_data => l_msg_data);
2433 END LOOP;
2434
2435 END IF; --Bug 4237493
2436
2437 x_resultout := wf_engine.eng_null;
2438
2439 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2440 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2441 'fpa.sql.fpa_main_process_pvt.copy_proj_from_prev_pc.end',
2442 'Exiting fpa_main_process_pvt.copy_proj_from_prev_pc');
2443 END IF;
2444
2445
2446 EXCEPTION
2447 WHEN OTHERS THEN
2448 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2449 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2450 'fpa.sql.fpa_main_process_pvt.copy_proj_from_prev_pc.end',
2451 SQLERRM);
2452 END IF;
2453 wf_core.context('FPA_MAIN_PROCESS_PVT', 'COPY_PROJ_FROM_PREV_PC', SQLERRM);
2454 RAISE;
2455 END COPY_PROJ_FROM_PREV_PC;
2456
2457 --Procedure to attach AW for workflow
2458
2459 PROCEDURE WF_ATTACH_AW (p_itemtype IN VARCHAR2,
2460 p_itemkey IN VARCHAR2,
2461 p_actid IN NUMBER,
2462 p_funcmode IN VARCHAR2,
2463 x_resultout OUT NOCOPY VARCHAR2) IS
2464
2465 l_return_status VARCHAR2(1);
2466 l_msg_count NUMBER;
2467 l_msg_data VARCHAR2(2000);
2468 l_wf_aw_attached VARCHAR2(1);
2469
2470 BEGIN
2471
2472 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2473 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2474 'fpa.sql.fpa_main_process_pvt.wf_attach_aw.begin',
2475 'Entering fpa_main_process_pvt.wf_attach_aw ');
2476 END IF;
2477
2478 Fpa_Utilities_Pvt.attach_AW
2479 (
2480 p_api_version => 1.0,
2481 p_attach_mode => 'rw',
2482 x_return_status => l_return_status,
2483 x_msg_count => l_msg_count,
2484 x_msg_data => l_msg_data
2485 );
2486
2487 l_wf_aw_attached := 'Y' ;
2488
2489 -- Sets the Planning Cycle ID
2490 wf_engine.SetItemAttrtext(itemtype => p_itemtype,
2491 itemkey => p_itemkey,
2492 aname => 'FPA_WF_AW_ATTACHED',
2493 avalue => l_wf_aw_attached);
2494
2495 x_resultout := wf_engine.eng_null;
2496
2497 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2498 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2499 'fpa.sql.fpa_main_process_pvt.wf_attach_aw.end',
2500 'Entering fpa_main_process_pvt.wf_attach_aw');
2501 END IF;
2502
2503 EXCEPTION
2504 WHEN OTHERS THEN
2505 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2506 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2507 'fpa.sql.fpa_main_process_pvt.wf_attach_aw.end',
2508 SQLERRM);
2509 END IF;
2510 wf_core.context('FPA_MAIN_PROCESS_PVT', ' WF_ATTACH_AW', SQLERRM);
2511 RAISE;
2512
2513 END;
2514
2515 --Procedure to detach AW for workflow
2516
2517 PROCEDURE WF_DETACH_AW (p_itemtype IN VARCHAR2,
2518 p_itemkey IN VARCHAR2,
2519 p_actid IN NUMBER,
2520 p_funcmode IN VARCHAR2,
2521 x_resultout OUT NOCOPY VARCHAR2) IS
2522
2523 l_return_status VARCHAR2(1);
2524 l_msg_count NUMBER;
2525 l_msg_data VARCHAR2(2000);
2526 l_wf_aw_attached VARCHAR2(1);
2527 BEGIN
2528
2529 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2530 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2531 'fpa.sql.fpa_main_process_pvt.wf_detach_aw.begin',
2532 'Entering fpa_main_process_pvt.wf_detach_aw ');
2533 END IF;
2534
2535 -- Sets the Planning Cycle ID
2536 l_wf_aw_attached := 'N';
2537 l_wf_aw_attached := wf_engine.getItemAttrtext(itemtype => p_itemtype,
2538 itemkey => p_itemkey,
2539 aname => 'FPA_WF_AW_ATTACHED');
2540
2541 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2542 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2543 'fpa.sql.fpa_main_process_pvt.wf_detach_aw.begin',
2544 'Value of l_wf_aw_attached '||l_wf_aw_attached);
2545 END IF;
2546
2547 IF NVL(l_wf_aw_attached,'N') = 'Y' THEN
2548
2549 -- Update AW
2550 dbms_aw.execute('UPDATE');
2551 COMMIT;
2552
2553 Fpa_Utilities_Pvt.detach_AW
2554 (
2555 p_api_version => 1.0,
2556 x_return_status => l_return_status,
2557 x_msg_count =>l_msg_count,
2558 x_msg_data => l_msg_data
2559 );
2560 END IF;
2561
2562 x_resultout := wf_engine.eng_null;
2563
2564 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2565 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2566 'fpa.sql.fpa_main_process_pvt.wf_detach_aw.end',
2567 'Entering fpa_main_process_pvt.wf_detach_aw');
2568 END IF;
2569
2570 EXCEPTION
2571 WHEN OTHERS THEN
2572 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2573 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
2574 'fpa.sql.fpa_main_process_pvt.wf_detach_aw.end',
2575 SQLERRM);
2576 END IF;
2577 wf_core.context('FPA_MAIN_PROCESS_PVT', ' WF_DETACH_AW', SQLERRM);
2578 RAISE;
2579
2580 END;
2581
2582 END FPA_MAIN_PROCESS_PVT;