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