[Home] [Help]
PACKAGE BODY: APPS.FPA_PROJECT_PVT
Source
1 package body FPA_PROJECT_PVT as
2 /* $Header: FPAVPRJB.pls 120.18 2011/08/09 01:14:49 skkoppul ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(200) := 'FPA_PROJECT_PVT';
5 G_APP_NAME CONSTANT VARCHAR2(3) := FPA_UTILITIES_PVT.G_APP_NAME;
6 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
7 L_API_NAME CONSTANT VARCHAR2(35) := 'PROJECT';
8
9 G_SELECTION_CATEGORY VARCHAR2(200) := FND_PROFILE.VALUE('PJP_PORTFOLIO_CLASS_CATEGORY');
10 G_PJP_ORGS_HIER VARCHAR2(200) := FND_PROFILE.VALUE('PJP_ORGANIZATION_HIERARCHY');
11
12 PROCEDURE Get_Project_Details
13 (
14 p_project_id IN NUMBER,
15 x_proj_portfolio OUT NOCOPY NUMBER,
16 x_proj_pc OUT NOCOPY NUMBER,
17 x_class_code_id OUT NOCOPY NUMBER,
18 x_valid_project OUT NOCOPY VARCHAR2,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2
22 ) IS
23
24 -- standard parameters
25 l_return_status VARCHAR2(1);
26 l_api_name CONSTANT VARCHAR2(30) := 'Get_Project_Details';
27 l_api_version CONSTANT NUMBER := 1.0;
28 l_msg_log VARCHAR2(2000) := null;
29 ----------------------------------------------------------------------------
30 l_org_id NUMBER;
31 l_validation BOOLEAN := FALSE;
32
33 CURSOR FUNDING_STATUS_CSR
34 (P_PROJECT_ID IN NUMBER) IS
35 SELECT
36 'T'
37 FROM PA_PROJECTS_ALL
38 WHERE PROJECT_ID = P_PROJECT_ID
39 AND FUNDING_APPROVAL_STATUS_CODE IN
40 ('FUNDING_PROPOSED','FUNDING_ONHOLD','FUNDING_APPROVED');
41
42 CURSOR HIER_VERSION_CSR IS
43 SELECT
44 ORG_STRUCTURE_VERSION_ID
45 FROM
46 PER_ORG_STRUCTURE_VERSIONS
47 WHERE
48 ORGANIZATION_STRUCTURE_ID = G_PJP_ORGS_HIER
49 AND (TRUNC(SYSDATE) BETWEEN TRUNC(DATE_FROM) AND TRUNC(NVL(DATE_TO,
50 SYSDATE)));
51
52
53 -- portfolio for submitted project
54 CURSOR PORTFOLIO_PROJ_CSR
55 (P_PROJECT_ID IN NUMBER,
56 P_PJP_ORG_VERSION_ID IN NUMBER) IS
57 SELECT
58 PTF.PORTFOLIO,
59 PAC.CLASS_CODE,
60 PTF.PORTFOLIO_ORGANIZATION,
61 PA.CARRYING_OUT_ORGANIZATION_ID
62 FROM
63 PA_PROJECT_CLASSES PAC,
64 PA_PROJECTS_ALL PA,
65 PA_CLASS_CODES PCC,
66 FPA_AW_PORTF_HEADERS_V PTF
67 WHERE
68 PTF.PORTFOLIO_CLASS_CODE = PCC.CLASS_CODE_ID
69 AND PAC.CLASS_CODE = PCC.CLASS_CODE
70 AND PAC.CLASS_CATEGORY = PCC.CLASS_CATEGORY
71 AND PCC.CLASS_CATEGORY = G_SELECTION_CATEGORY
72 AND PAC.PROJECT_ID = PA.PROJECT_ID
73 AND PA.PROJECT_ID = P_PROJECT_ID
74 AND (PTF.PORTFOLIO_ORGANIZATION IS NULL
75 OR (PTF.PORTFOLIO_ORGANIZATION IS NOT NULL
76 AND PA.CARRYING_OUT_ORGANIZATION_ID IN
77 (
78 SELECT
79 ORGANIZATION_ID_CHILD
80 FROM
81 PER_ORG_STRUCTURE_ELEMENTS
82 WHERE
83 ORG_STRUCTURE_VERSION_ID = P_PJP_ORG_VERSION_ID
84 CONNECT BY PRIOR ORGANIZATION_ID_CHILD = ORGANIZATION_ID_PARENT
85 AND PRIOR ORG_STRUCTURE_VERSION_ID = P_PJP_ORG_VERSION_ID
86 START WITH ORGANIZATION_ID_PARENT = PTF.PORTFOLIO_ORGANIZATION
87 OR ORGANIZATION_ID_CHILD = PTF.PORTFOLIO_ORGANIZATION
88 UNION
89 SELECT PTF.PORTFOLIO_ORGANIZATION FROM dual --added for bug 6086945
90 ))); -- IN, OR , AND
91
92
93 -- current planning cycle for portfolio
94 CURSOR PORTFOLIO_PC_CSR
95 (P_PORTFOLIO_ID IN NUMBER,
96 P_PROJECT_ID IN NUMBER) IS
97 SELECT
98 PC.PLANNING_CYCLE,
99 CC.CLASS_CODE_ID
100 FROM
101 FPA_AW_PC_INFO_V PC, PA_CLASS_CATEGORIES PCC,
102 PA_PROJECT_CLASSES PAC, PA_CLASS_CODES CC
103 WHERE
104 PC.PC_STATUS IN ('COLLECTING', 'ANALYSIS')
105 AND PC.PC_CATEGORY = PCC.CLASS_CATEGORY_ID
106 AND PAC.CLASS_CATEGORY = PCC.CLASS_CATEGORY
107 AND PCC.CLASS_CATEGORY = CC.CLASS_CATEGORY
108 AND CC.CLASS_CODE = PAC.CLASS_CODE
109 AND PC.PORTFOLIO = P_PORTFOLIO_ID
110 AND PAC.PROJECT_ID = P_PROJECT_ID;
111
112
113 l_portfolio_id FPA_AW_PORTF_HEADERS_V.PORTFOLIO%TYPE := null;
114 l_portfolio_org_id FPA_AW_PORTF_HEADERS_V.PORTFOLIO_ORGANIZATION%TYPE := null;
115 l_class_code PA_CLASS_CODES.CLASS_CODE%TYPE := null;
116 l_class_code_id PA_CLASS_CODES.CLASS_CODE_ID%TYPE := null;
117 l_current_pc_id FPA_AW_PC_INFO_V.PLANNING_CYCLE%TYPE := null;
118 l_project_org_id HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID%TYPE := null;
119 l_pjp_org_version_id PER_ORG_STRUCTURE_VERSIONS.ORG_STRUCTURE_VERSION_ID%TYPE := null;
120 l_flag VARCHAR2(1) := null;
121
122 BEGIN
123 x_valid_project := FND_API.G_FALSE;
124 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
125
126 l_flag := FND_API.G_FALSE;
127 open funding_status_csr(p_project_id);
128 fetch funding_status_csr into l_flag;
129 close funding_status_csr;
130
131 if(l_flag is null or l_flag <> FND_API.G_TRUE) then
132 x_valid_project := FND_API.G_FALSE;
133 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
134 FPA_UTILITIES_PVT.END_ACTIVITY(
135 p_api_name => l_api_name,
136 p_pkg_name => G_PKG_NAME,
137 p_msg_log => null,
138 x_msg_count => x_msg_count,
139 x_msg_data => x_msg_data);
140
141 l_validation := Fpa_Validation_Pvt.Add_Validation(
142 'FPA_V_FUNDING_CODE',
143 FPA_VALIDATION_PVT.G_ERROR,
144 p_project_id,
145 'PROJECT');
146 if(l_validation = false) then
147 return;
148 end if;
149 end if;
150
151 -- get org version id for the PJP hierarchy org
152
153 if(G_PJP_ORGS_HIER is null) then
154 x_valid_project := FND_API.G_FALSE;
155 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
156 FPA_UTILITIES_PVT.END_ACTIVITY(
157 p_api_name => l_api_name,
158 p_pkg_name => G_PKG_NAME,
159 p_msg_log => 'returning: PJP Hierarchy Org not set ',
160 x_msg_count => x_msg_count,
161 x_msg_data => x_msg_data);
162 l_validation := Fpa_Validation_Pvt.Add_Validation(
163 'FPA_V_PJP_ORG',
164 FPA_VALIDATION_PVT.G_ERROR,
165 null, null);
166 if(l_validation) then
167 return;
168 end if;
169 end if;
170
171 open hier_version_csr;
172 fetch hier_version_csr into l_pjp_org_version_id;
173 close hier_version_csr;
174
175 open portfolio_proj_csr(p_project_id, l_pjp_org_version_id);
176 fetch portfolio_proj_csr into l_portfolio_id, l_class_code,
177 l_portfolio_org_id, l_project_org_id;
178 close portfolio_proj_csr;
179
180 if(l_portfolio_id is null) then
181 x_valid_project := FND_API.G_FALSE;
182 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
183 FPA_UTILITIES_PVT.END_ACTIVITY(
184 p_api_name => l_api_name,
185 p_pkg_name => G_PKG_NAME,
186 p_msg_log => 'returning: no portfolio or class code for '||p_project_id,
187 x_msg_count => x_msg_count,
188 x_msg_data => x_msg_data);
189 l_validation := Fpa_Validation_Pvt.Add_Validation(
190 'FPA_V_PROJ_PORTFOLIO_CATG',
191 FPA_VALIDATION_PVT.G_ERROR,
192 p_project_id,
193 'PROJECT');
194 return;
195 end if;
196
197 open portfolio_pc_csr(l_portfolio_id, p_project_id);
198 fetch portfolio_pc_csr into l_current_pc_id, l_class_code_id;
199 close portfolio_pc_csr;
200
201 if(l_current_pc_id is null) then
202 x_valid_project := FND_API.G_FALSE;
203 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
204 FPA_UTILITIES_PVT.END_ACTIVITY(
205 p_api_name => l_api_name,
206 p_pkg_name => G_PKG_NAME,
207 p_msg_log => 'returning: no planning cycle or pc category for '||l_portfolio_id,
208 x_msg_count => x_msg_count,
209 x_msg_data => x_msg_data);
210 l_validation := Fpa_Validation_Pvt.Add_Validation(
211 'FPA_V_PROJ_PC_CATG',
212 FPA_VALIDATION_PVT.G_ERROR,
213 p_project_id,
214 'PROJECT');
215 if(l_validation = false) then
216 return;
217 end if;
218 end if;
219
220 x_proj_portfolio := l_portfolio_id;
221 x_proj_pc := l_current_pc_id;
222 x_class_code_id := l_class_code_id;
223 x_valid_project := FND_API.G_TRUE;
224 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
225
226 FPA_UTILITIES_PVT.END_ACTIVITY(
227 p_api_name => l_api_name,
228 p_pkg_name => G_PKG_NAME,
229 p_msg_log => 'FPA: returning valid project',
230 x_msg_count => x_msg_count,
231 x_msg_data => x_msg_data);
232
233
234 EXCEPTION
235 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
236 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
237 p_api_name => l_api_name,
238 p_pkg_name => G_PKG_NAME,
239 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
240 p_msg_log => l_msg_log,
241 x_msg_count => x_msg_count,
242 x_msg_data => x_msg_data,
243 p_api_type => G_API_TYPE);
244
245 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
246 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
247 p_api_name => l_api_name,
248 p_pkg_name => G_PKG_NAME,
249 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
250 p_msg_log => l_msg_log,
251 x_msg_count => x_msg_count,
252 x_msg_data => x_msg_data,
253 p_api_type => G_API_TYPE);
254
255 when OTHERS then
256 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
257 p_api_name => l_api_name,
258 p_pkg_name => G_PKG_NAME,
259 p_exc_name => 'OTHERS',
260 p_msg_log => l_msg_log||SQLERRM,
261 x_msg_count => x_msg_count,
262 x_msg_data => x_msg_data,
263 p_api_type => G_API_TYPE);
264
265 END Get_Project_Details;
266
267
268
269 FUNCTION Valid_Project(
270 p_project_id IN NUMBER
271 ) RETURN VARCHAR2 IS
272
273 l_return_status VARCHAR2(1);
274 l_msg_count NUMBER;
275 l_msg_data VARCHAR2(4000);
276
277 l_portfolio_id FPA_AW_PORTF_HEADERS_V.PORTFOLIO%TYPE := null;
278 l_current_pc_id FPA_AW_PC_INFO_V.PLANNING_CYCLE%TYPE := null;
279 l_class_code_id NUMBER;
280 l_valid_project VARCHAR2(1) := FND_API.G_FALSE;
281
282 BEGIN
283
284 l_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
285
286 Get_Project_Details(
287 p_project_id => p_project_id,
288 x_proj_portfolio => l_portfolio_id,
289 x_proj_pc => l_current_pc_id,
290 x_class_code_id => l_class_code_id,
291 x_valid_project => l_valid_project,
292 x_return_status => l_return_status,
293 x_msg_count => l_msg_count,
294 x_msg_data => l_msg_data);
295
296 if(l_valid_project is null or l_valid_project <> FND_API.G_TRUE) then
297 return FND_API.G_FALSE;
298 else
299 return FND_API.G_TRUE;
300 end if;
301
302 EXCEPTION
303 when OTHERS then
304 return FND_API.G_FALSE;
305 END Valid_Project;
306
307 /** Verify_Budget_Versions is used to determine if projects under a given scenario have
308 the latest revenue and cost budget data .
309 This function is used in two different cases:
310 1. To determine if an individual project under a scenario contains the latest data.
311 2. To determine if a scenario (all projects under the scenario) contains the latest data.
312 **/
313
314 FUNCTION Verify_Budget_Versions(
315 p_scenario_id IN NUMBER,
316 p_project_id IN NUMBER
317 ) RETURN VARCHAR2 IS
318
319
320 l_cost_version_id NUMBER;
321 l_benefit_version_id NUMBER;
322 l_new_cost_version_id NUMBER;
323 l_new_benefit_version_id NUMBER;
324
325 TYPE PROJTYPE is RECORD(project_id number);
326 TYPE PROJTABLE is TABLE of PROJTYPE;
327 new_projs PROJTABLE;
328
329 cursor all_projs is
330 select project
331 from fpa_aw_proj_info_v
332 where scenario = p_scenario_id;
333
334 cursor one_proj is
335 select p_project_id
336 from dual;
337
338 BEGIN
339
340 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
341 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
342 'fpa.sql.fpa_project_pvt.Verify_Budget_Versions.begin',
343 'Entering fpa_project_pvt.Verify_Budget_Versions');
344 END IF;
345
346
347 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
348 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
349 'fpa.sql.fpa_project_pvt.Verify_Budget_Versions.begin',
350 'Parameters are Scenario Id: ' || p_scenario_id ||
351 ' Project Id: ' || p_project_id);
352 END IF;
353
354 /** if p_project_id is null then we are querying the entire scenario.
355 We need to get all projects under the given scenario.
356 We place all projecs into the TABLE type. **/
357 if p_project_id is null then
358
359 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
360 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
361 'fpa.sql.fpa_project_pvt.Verify_Budget_Versions.begin',
362 'About to query and fetch all projects from the scenario.');
363 end if;
364
365 open all_projs;
366 loop
367 fetch all_projs BULK COLLECT into new_projs;
368 exit when all_projs%NOTFOUND;
369 end loop;
370 close all_projs;
371
372 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
373 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
374 'fpa.sql.fpa_project_pvt.Verify_Budget_Versions.begin',
375 'Done fetching all projects from the scenario.');
376 end if;
377
378 /** If p_project_id is not null then we are querying an individual project.
379 We plase the value of p_project_id in the TABLE type. **/
380 else
381
382 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
383 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
384 'fpa.sql.fpa_project_pvt.Verify_Budget_Versions.begin',
385 'Fetching single project: ' || p_project_id);
386 end if;
387
388 open one_proj;
389 fetch one_proj BULK COLLECT into new_projs;
390 close one_proj;
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_project_pvt.Verify_Budget_Versions.begin',
395 'Done fetching single project: ' || p_project_id);
396 end if;
397
398
399 end if;
400
401 /** Now we loop over all the members of the TABLE type object.
402 For each member (project id) we get ID for cost and revenue plan
403 from Projects Foundation and compare against the plan IDs stored
404 in Portfolio Analysis.
405 **/
406 for i in new_projs.first..new_projs.last loop
407
408 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
409 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
410 'fpa.sql.fpa_project_pvt.Verify_Budget_Versions',
411 'Querying Cost and Benefit Plan IDs.');
412 END IF;
413
414 SELECT
415 DECODE(C.BUDGET_VERSION_ID,NULL,-1,C.BUDGET_VERSION_ID) COST_BUDGET_VERSN_ID,
416 DECODE(B.BUDGET_VERSION_ID,NULL,-1,B.BUDGET_VERSION_ID) BENF_BUDGET_VERSN_ID
417 INTO
418 L_NEW_COST_VERSION_ID, L_NEW_BENEFIT_VERSION_ID
419 FROM
420 PA_PROJECTS_ALL P, PA_BUDGET_VERSIONS C, PA_BUDGET_VERSIONS B
421 WHERE
422 'B' = C.BUDGET_STATUS_CODE (+) AND 'Y' = C.CURRENT_FLAG (+)
423 AND fnd_profile.value('PJP_FINANCIAL_PLAN_TYPE_COST') = C.FIN_PLAN_TYPE_ID (+)
424 AND 'B' = B.BUDGET_STATUS_CODE (+) AND 'Y' = B.CURRENT_FLAG (+)
425 AND fnd_profile.value('PJP_FINANCIAL_PLAN_TYPE_BENEFIT') = B.FIN_PLAN_TYPE_ID (+)
426 AND P.PROJECT_ID = C.PROJECT_ID (+) AND P.PROJECT_ID = B.PROJECT_ID (+)
427 AND P.PROJECT_ID = new_projs(i).project_id;
428 -- AND P.PROJECT_ID = P_PROJECT_ID;
429
430
431 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
432 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
433 'fpa.sql.fpa_project_pvt.Verify_Budget_Versions',
434 'Querying Validation Lines to get latest pulled Plan Version IDs.');
435 END IF;
436
437
438 SELECT
439 C.OBJECT_ID BUDGET_VERSION_COST,
440 B.OBJECT_ID BUDGET_VERSION_BENEFIT
441 INTO
442 L_COST_VERSION_ID, L_BENEFIT_VERSION_ID
443 FROM
444 FPA_VALIDATION_LINES S,
445 FPA_VALIDATION_LINES P,
446 FPA_VALIDATION_LINES C,
447 FPA_VALIDATION_LINES B
448 WHERE
449 S.OBJECT_TYPE = 'BUDGET_VERSIONS_SCENARIO'
450 AND S.HEADER_ID IS NULL
451 AND P.OBJECT_TYPE = 'BUDGET_VERSIONS_PROJ'
452 AND P.HEADER_ID = S.VALIDATION_ID
453 AND C.OBJECT_TYPE = 'BUDGET_VERSION_COST'
454 AND C.HEADER_ID = P.VALIDATION_ID
455 AND C.VALIDATION_TYPE = 'FPA_V_PROJ_COST_VERSION'
456 AND B.OBJECT_TYPE = 'BUDGET_VERSION_BENEFIT'
457 AND B.HEADER_ID = P.VALIDATION_ID
458 AND B.VALIDATION_TYPE = 'FPA_V_PROJ_BENEFIT_VERSION'
459 AND S.OBJECT_ID = P_SCENARIO_ID
460 AND P.OBJECT_ID = new_projs(i).project_id;
461 -- AND P.OBJECT_ID = P_PROJECT_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_project_pvt.Verify_Budget_Versions',
466 'After queries values were ' ||
467 ' Cost version id in PJP: ' || l_cost_version_id ||
468 ' Cost version id in PJT: ' || l_new_cost_version_id ||
469 ' Budget version id in PJP: ' || l_benefit_version_id ||
470 ' Budget version id in PJT: ' || l_new_benefit_version_id);
471 END IF;
472
473 /** If IDs are not identical then we return a FALSE flag, be it for
474 an individual project or for the entire scenario.
475 **/
476 if(l_cost_version_id <> l_new_cost_version_id or
477 l_benefit_version_id <> l_new_benefit_version_id) then
478 return FND_API.G_FALSE;
479 else
480 /** If IDs are identical and querying a single project then we return
481 a TRUE flag.
482 **/
483 if p_project_id is not null then
484 return FND_API.G_TRUE;
485 /** If IDs are identical and we have verified all projects under the scenario
486 contain the latest data, then we return a TRUE fla
487 **/
488 else
489 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
490 fnd_log.string(FND_LOG.LEVEL_PROCEDURE,
491 'fpa.sql.fpa_project_pvt.Verify_Budget_Versions',
492 'Project ID is null. The current value is: ' || new_projs(i).project_id ||
493 ' and the last project is: ' || new_projs(new_projs.last).project_id);
494 END IF;
495
496 if new_projs(i).project_id = new_projs(new_projs.last).project_id then
497 return FND_API.G_TRUE;
498 end if;
499 end if;
500 end if;
501
502 end loop;
503
504 EXCEPTION
505 when OTHERS then
506 return FND_API.G_FALSE;
507 END Verify_Budget_Versions;
508
509
510 PROCEDURE Submit_Project_Aw
511 (
512 p_api_version IN NUMBER,
513 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
514 p_commit IN VARCHAR2 := FND_API.G_FALSE,
515 p_project_id IN NUMBER,
516 x_return_status OUT NOCOPY VARCHAR2,
517 x_msg_count OUT NOCOPY NUMBER,
518 x_msg_data OUT NOCOPY VARCHAR2
519 ) IS
520
521 -- standard parameters
522 l_return_status VARCHAR2(1);
523 l_api_name CONSTANT VARCHAR2(30) := 'Submit_project_Aw';
524 l_api_version CONSTANT NUMBER := 1.0;
525 l_msg_log VARCHAR2(2000) := null;
526 ----------------------------------------------------------------------------
527
528 l_portfolio_id FPA_AW_PORTF_HEADERS_V.PORTFOLIO%TYPE := null;
529 l_current_pc_id FPA_AW_PC_INFO_V.PLANNING_CYCLE%TYPE := null;
530 l_class_code_id NUMBER;
531 l_valid_project VARCHAR2(1) := FND_API.G_FALSE;
532
533 PROCEDURE save_project
534 IS
535 BEGIN
536 BEGIN
537 dbms_aw.execute('MAINTAIN project_d ADD '|| p_project_id );
538 EXCEPTION
539 WHEN OTHERS THEN
540 NULL;
541 END;
542 dbms_aw.execute('oknullstatus = yes');
543 dbms_aw.execute('push portfolio_d');
544 dbms_aw.execute('push planning_cycle_d');
545 dbms_aw.execute('push project_d');
546
547 dbms_aw.execute('LIMIT project_d TO '|| p_project_id );
548 dbms_aw.execute('class_code_project_r = '||l_class_code_id);
549 dbms_aw.execute('portfolio_project_r = '||l_portfolio_id);
550 dbms_aw.execute('LIMIT planning_cycle_d TO '|| l_current_pc_id );
551 dbms_aw.execute('pc_project_r = planning_cycle_d');
552
553 dbms_aw.execute('pop portfolio_d');
554 dbms_aw.execute('pop planning_cycle_d');
555 dbms_aw.execute('pop project_d');
556
557 dbms_aw.execute('UPDATE');
558
559 EXCEPTION
560 WHEN OTHERS then
561 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
562 FND_LOG.String(
563 FND_LOG.LEVEL_PROCEDURE,
564 'procedure save_project',
565 'exception: '||sqlerrm||p_project_id||','||l_class_code_id||','||l_portfolio_id||','||l_current_pc_id);
566 end if;
567 raise;
568 END save_project;
569
570
571 BEGIN
572
573 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
574 -- call START_ACTIVITY to create savepoint, check compatibility
575 -- and initialize message list
576 x_return_status := FPA_UTILITIES_PVT.START_ACTIVITY(
577 p_api_name => l_api_name,
578 p_pkg_name => G_PKG_NAME,
579 p_init_msg_list => p_init_msg_list,
580 l_api_version => l_api_version,
581 p_api_version => p_api_version,
582 p_api_type => G_API_TYPE,
583 p_msg_log => 'Entering Fpa_Project_Load_Pvt.Submit_project_Aw',
584 x_return_status => x_return_status);
585
586 -- check if activity started successfully
587 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
588 l_msg_log := 'start_activity';
589 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
590 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
591 l_msg_log := 'start_activity';
592 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
593 end if;
594
595 Get_Project_Details(
596 p_project_id => p_project_id,
597 x_proj_portfolio => l_portfolio_id,
598 x_proj_pc => l_current_pc_id,
599 x_class_code_id => l_class_code_id,
600 x_valid_project => l_valid_project,
601 x_return_status => x_return_status,
602 x_msg_count => x_msg_count,
603 x_msg_data => x_msg_data);
604
605
606 if(l_valid_project is null or l_valid_project <> FND_API.G_TRUE) then
607 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
608 FPA_UTILITIES_PVT.END_ACTIVITY(
609 p_api_name => l_api_name,
610 p_pkg_name => G_PKG_NAME,
611 p_msg_log => 'returning: project not saved '||p_project_id,
612 x_msg_count => x_msg_count,
613 x_msg_data => x_msg_data);
614 return;
615 end if;
616
617 save_project;
618
619 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
620
621 FPA_UTILITIES_PVT.END_ACTIVITY(
622 p_api_name => l_api_name,
623 p_pkg_name => G_PKG_NAME,
624 p_msg_log => null,
625 x_msg_count => x_msg_count,
626 x_msg_data => x_msg_data);
627
628
629 EXCEPTION
630 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
631 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
632 p_api_name => l_api_name,
633 p_pkg_name => G_PKG_NAME,
634 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
635 p_msg_log => l_msg_log,
636 x_msg_count => x_msg_count,
637 x_msg_data => x_msg_data,
638 p_api_type => G_API_TYPE);
639
640 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
641 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
642 p_api_name => l_api_name,
643 p_pkg_name => G_PKG_NAME,
644 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
645 p_msg_log => l_msg_log,
646 x_msg_count => x_msg_count,
647 x_msg_data => x_msg_data,
648 p_api_type => G_API_TYPE);
649
650 when OTHERS then
651 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
652 p_api_name => l_api_name,
653 p_pkg_name => G_PKG_NAME,
654 p_exc_name => 'OTHERS',
655 p_msg_log => l_msg_log||SQLERRM,
656 x_msg_count => x_msg_count,
657 x_msg_data => x_msg_data,
658 p_api_type => G_API_TYPE);
659
660 END Submit_project_Aw;
661
662
663 PROCEDURE Load_Project_Details_Aw
664 (
665 p_api_version IN NUMBER,
666 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
667 p_type IN VARCHAR2,
668 p_scenario_id IN NUMBER,
669 p_projects IN VARCHAR2,
670 x_return_status OUT NOCOPY VARCHAR2,
671 x_msg_count OUT NOCOPY NUMBER,
672 x_msg_data OUT NOCOPY VARCHAR2
673 ) IS
674
675 -- standard parameters
676 l_return_status VARCHAR2(1);
677 l_api_name CONSTANT VARCHAR2(30) := 'Load_Project_Details_Aw';
678 l_api_version CONSTANT NUMBER := 1.0;
679 l_msg_log VARCHAR2(2000) := null;
680 ----------------------------------------------------------------------------
681 l_projects VARCHAR2(2000) := null;
682
683 l_pc_id NUMBER;
684
685 BEGIN
686
687 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
688 -- call START_ACTIVITY to create savepoint, check compatibility
689 -- and initialize message list
690 x_return_status := FPA_UTILITIES_PVT.START_ACTIVITY(
691 p_api_name => l_api_name,
692 p_pkg_name => G_PKG_NAME,
693 p_init_msg_list => p_init_msg_list,
694 l_api_version => l_api_version,
695 p_api_version => p_api_version,
696 p_api_type => G_API_TYPE,
697 p_msg_log => 'Entering Fpa_Project_Load_Pvt.Load_Project_Details_Aw',
698 x_return_status => x_return_status);
699
700 -- check if activity started successfully
701 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
702 l_msg_log := 'start_activity';
703 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
704 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
705 l_msg_log := 'start_activity';
706 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
707 end if;
708
709
710 l_projects := p_projects;
711
712 if(l_projects is null) then
713 l_projects := 'na';
714 else
715 l_projects := ''''||l_projects||'''';
716 end if;
717
718 if(p_type <> 'REFRESH') then
719 dbms_aw.execute('CALL LOAD_BUDGET_FORECAST_PRG('
720 ||p_scenario_id||','||l_projects||',''LOAD'''||')');
721 end if;
722
723 dbms_aw.execute('CALL LOAD_BUDGET_FORECAST_PRG('
724 ||p_scenario_id||','||l_projects||',''COST'''||')');
725
726 dbms_aw.execute('CALL LOAD_BUDGET_FORECAST_PRG('
727 ||p_scenario_id||','||l_projects||',''BENEFIT'''||')');
728
729 dbms_aw.execute('CALL LOAD_BUDGET_FORECAST_PRG('
730 ||p_scenario_id||','||l_projects||',''SUNK_COST'''||')');
731
732 if(p_type <> 'REFRESH') then
733
734 FPA_SCORECARDS_PVT.Handle_Comments(
735 p_api_version => p_api_version,
736 p_init_msg_list => p_init_msg_list,
737 p_scenario_id => p_scenario_id,
738 p_type => 'PJT',
739 p_source_scenario_id => null,
740 p_delete_project_id => null,
741 x_return_status => x_return_status,
742 x_msg_count => x_msg_count,
743 x_msg_data => x_msg_data);
744 end if;
745
746 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
747 l_msg_log := 'unexpected error - load_project_details_aw';
748 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
749 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
750 l_msg_log := 'error - load_project_details_aw';
751 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
752 end if;
753
754
755 IF (p_projects is null) THEN
756
757 SELECT PLANNING_CYCLE INTO l_pc_id
758 FROM FPA_AW_SCES_V WHERE SCENARIO = p_scenario_id;
759
760 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
761 FND_LOG.STRING
762 (
763 FND_LOG.LEVEL_PROCEDURE,
764 'FPA.SQL.FPA_PROCESS_PVT.Validate',
765 'Ending FPA_PROCESS_PVT.Validate.call l_pc_id: '||l_pc_id
766 );
767 END IF;
768
769
770
771 Fpa_Validation_Pvt.Validate (
772 p_api_version => 1.0,
773 p_init_msg_list => 'F',
774 p_validation_set => 'FPA_VALIDATION_TYPES',
775 p_header_object_id => l_pc_id,
776 p_header_object_type => 'PLANNING_CYCLE',
777 x_return_status => x_return_status,
778 x_msg_count => x_msg_count,
779 x_msg_data => x_msg_data);
780
781
782 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
783 FND_LOG.STRING
784 (
785 FND_LOG.LEVEL_PROCEDURE,
786 'FPA.SQL.FPA_PROCESS_PVT.Validate',
787 'Ending FPA_PROCESS_PVT.Validate.end'
788 );
789 END IF;
790
791 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
792 l_msg_log := 'unexpected error - load_project_details_aw.Validate';
793 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
794 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
795 l_msg_log := 'error - load_project_details_aw.Validate';
796 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
797 end if;
798
799 Fpa_Validation_Process_Pvt.Budget_Version_Validations(
800 p_api_version => 1.0,
801 p_init_msg_list => 'F',
802 p_validation_set => 'FPA_VALIDATION_TYPES',
803 p_header_object_id => l_pc_id,
804 p_header_object_type => 'PLANNING_CYCLE',
805 x_return_status => x_return_status,
806 x_msg_count => x_msg_count,
807 x_msg_data => x_msg_data);
808
809 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
810 FND_LOG.STRING
811 (
812 FND_LOG.LEVEL_PROCEDURE,
813 'fpa.sql.Fpa_Validation_Process_Pvt.Validate_Budget_Versions',
814 'End Fpa_Validation_Process_Pvt.Validate_Budget_Versions.end'
815 );
816 END IF;
817
818 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
819 l_msg_log := 'unexpected error - load_project_details_aw.Validate_Budget_Versions';
820 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
821 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
822 l_msg_log := 'error - load_project_details_aw.Validate_Budget_Versions';
823 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
824 end if;
825
826 END IF;
827
828 FPA_UTILITIES_PVT.END_ACTIVITY(
829 p_api_name => l_api_name,
830 p_pkg_name => G_PKG_NAME,
831 p_msg_log => null,
832 x_msg_count => x_msg_count,
833 x_msg_data => x_msg_data);
834
835 EXCEPTION
836 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
837 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
838 p_api_name => l_api_name,
839 p_pkg_name => G_PKG_NAME,
840 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
841 p_msg_log => l_msg_log,
842 x_msg_count => x_msg_count,
843 x_msg_data => x_msg_data,
844 p_api_type => G_API_TYPE);
845
846 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
847 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
848 p_api_name => l_api_name,
849 p_pkg_name => G_PKG_NAME,
850 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
851 p_msg_log => l_msg_log,
852 x_msg_count => x_msg_count,
853 x_msg_data => x_msg_data,
854 p_api_type => G_API_TYPE);
855
856 when OTHERS then
857 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
858 p_api_name => l_api_name,
859 p_pkg_name => G_PKG_NAME,
860 p_exc_name => 'OTHERS',
861 p_msg_log => l_msg_log||SQLERRM,
862 x_msg_count => x_msg_count,
863 x_msg_data => x_msg_data,
864 p_api_type => G_API_TYPE);
865
866 END Load_Project_Details_Aw;
867
868
869 -- We need the page_id and the pers function name
870 -- for a Planning cycle if one existed or
871 -- provide the default parameters
872 /*
873 PROCEDURE get_config_page_attributes
874 (
875 p_planning_cycle_id IN NUMBER,
876 x_page_id OUT NOCOPY NUMBER,
877 x_pers_function_name OUT NOCOPY VARCHAR2,
878 x_return_status OUT NOCOPY VARCHAR2,
879 x_msg_count OUT NOCOPY NUMBER,
880 x_msg_data OUT NOCOPY VARCHAR2
881 ) IS
882
883
884 CURSOR c_default_attr is
885 select page_id, pers_function_name
886 from pa_page_layouts
887 where page_id = 51;
888
889 CURSOR c_pl_cycle_attr is
890 select pap.page_id, obj.pers_function_name
891 from pa_page_layouts pap, pa_object_page_layouts obj
892 where obj.OBJECT_TYPE = 'PA_PROJECTS'
893 and obj.object_id = p_planning_cycle_id
894 and pap.page_id = obj.page_id;
895
896 l_default_attr c_default_attr%ROWTYPE;
897 l_pl_cycle_attr c_pl_cycle_attr%ROWTYPE;
898
899 BEGIN
900
901 open c_pl_cycle_attr; -- If this cursor fetched a row, we pass the object specific data
902 fetch c_pl_cycle_attr into l_pl_cycle_attr;
903 IF c_pl_cycle_attr%FOUND then
904 x_page_id := l_pl_cycle_attr.page_id;
905 x_pers_function_name := l_pl_cycle_attr.pers_function_name;
906 ELSE
907 -- Default Attributes
908 open c_default_attr;
909 fetch c_default_attr into l_default_attr;
910 x_page_id := l_default_attr.page_id;
911 x_pers_function_name := l_default_attr.pers_function_name;
912 CLOSE c_default_attr;
913 END IF;
914 CLOSE c_pl_cycle_attr;
915
916
917 END get_config_page_attributes;
918 */
919
920 -- We need the page_id
921 -- for a Planning cycle if one existed or
922 -- provide the default parameters
923
924 FUNCTION get_config_page_id
925 (
926 p_planning_cycle_id IN NUMBER
927 )
928 RETURN NUMBER
929
930 IS
931 l_page_id NUMBER;
932 CURSOR c_pl_cycle_attr is
933 select pap.page_id
934 from pa_page_layouts pap, pa_object_page_layouts obj
935 where obj.OBJECT_TYPE = 'PA_PROJECTS'
936 and obj.object_id = p_planning_cycle_id
937 and pap.page_id = obj.page_id;
938
939 l_pl_cycle_attr c_pl_cycle_attr%ROWTYPE;
940
941 BEGIN
942
943 OPEN c_pl_cycle_attr; -- If this cursor fetched a row, we pass the object specific data
944 FETCH c_pl_cycle_attr INTO l_pl_cycle_attr;
945 IF c_pl_cycle_attr%FOUND THEN
946 l_page_id := l_pl_cycle_attr.page_id;
947
948 ELSE
949 -- Default Attibutes
950 l_page_id := 51;
951
952 END IF;
953 CLOSE c_pl_cycle_attr;
954
955 RETURN l_page_id;
956 END get_config_page_id;
957
958 -- We need the pers function name
959 -- for a Planning cycle if one existed or
960 -- provide the default parameters
961
962 FUNCTION get_config_page_function
963 (
964 p_planning_cycle_id IN NUMBER
965 )
966 RETURN VARCHAR2
967 IS
968
969 l_pers_function_name VARCHAR2(50);
970
971 CURSOR c_default_attr is
972 select page_id, pers_function_name
973 from pa_page_layouts
974 where page_id = 51;
975
976 CURSOR c_pl_cycle_attr is
977 select pap.page_id, obj.pers_function_name
978 from pa_page_layouts pap, pa_object_page_layouts obj
979 where obj.OBJECT_TYPE = 'PA_PROJECTS'
980 and obj.object_id = p_planning_cycle_id
981 and pap.page_id = obj.page_id;
982
983 l_default_attr c_default_attr%ROWTYPE;
984 l_pl_cycle_attr c_pl_cycle_attr%ROWTYPE;
985
986 BEGIN
987
988 OPEN c_pl_cycle_attr; -- If this cursor fetched a row, we pass the object specific data
989 FETCH c_pl_cycle_attr INTO l_pl_cycle_attr;
990 IF c_pl_cycle_attr%FOUND THEN
991 l_pers_function_name := l_pl_cycle_attr.pers_function_name;
992 ELSE
993 -- Default Attributes
994 OPEN c_default_attr;
995 FETCH c_default_attr INTO l_default_attr;
996 l_pers_function_name := l_default_attr.pers_function_name;
997 CLOSE c_default_attr;
998 END IF;
999 CLOSE c_pl_cycle_attr;
1000
1001 RETURN l_pers_function_name;
1002
1003 END get_config_page_function;
1004
1005
1006 PROCEDURE UPDATE_PROJ_FUNDING_STATUS
1007 ( p_api_version IN NUMBER,
1008 p_init_msg_list IN VARCHAR2,
1009 p_commit IN VARCHAR2,
1010 p_appr_scenario_id IN NUMBER,
1011 x_return_status OUT NOCOPY VARCHAR2,
1012 x_msg_count OUT NOCOPY NUMBER,
1013 x_msg_data OUT NOCOPY VARCHAR2) IS
1014
1015 cursor c_approved_projects IS
1016 select ppa.rowid, project_id, recommended_funding_status
1017 from pa_projects_all ppa, fpa_aw_proj_info_v sceproj
1018 where scenario = p_appr_scenario_id and sceproj.project = ppa.project_id;
1019 ----------------------------------------------------
1020 -- Bug Reference :6622099, The For Update clause
1021 -- is used if its referenced inside Current of clause
1022 -- of UPDATE or DELETE Statement.This was causing
1023 -- error in 64 Bit DB
1024 ----------------------------------------------------
1025 --for update of funding_approval_status_code nowait;
1026
1027 l_approved_projects c_approved_projects%ROWTYPE;
1028 l_msg_count NUMBER;
1029
1030 BEGIN
1031
1032 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1033 FND_LOG.String
1034 (
1035 FND_LOG.LEVEL_PROCEDURE,
1036 'fpa.sql.FPA_PROJECT_PVT.UPDATE_PROJ_FUNDING_STATUS.begin',
1037 'Entering FPA_PROJECT_PVT.UPDATE_PROJ_FUNDING_STATUS'
1038 );
1039 END IF;
1040
1041
1042 BEGIN
1043 -- Select projects from Approved scenario
1044 OPEN c_approved_projects;
1045 LOOP
1046
1047 FETCH c_approved_projects into l_approved_projects;
1048
1049 EXIT WHEN c_approved_projects%NOTFOUND;
1050
1051 UPDATE PA_PROJECTS_ALL
1052 SET FUNDING_APPROVAL_STATUS_CODE = l_approved_projects.recommended_funding_status
1053 WHERE ROWID = l_approved_projects.rowid;
1054 --WHERE project_id = l_approved_projects.project;
1055
1056 END LOOP;
1057 CLOSE c_approved_projects;
1058
1059 EXCEPTION
1060 WHEN NO_DATA_FOUND THEN
1061 FPA_UTILITIES_PVT.SET_MESSAGE( p_app_name => 'PA',
1062 p_msg_name => 'PA_XC_RECORD_CHANGED');
1063 x_msg_data := 'PA_XC_RECORD_CHANGED';
1064
1065 WHEN TIMEOUT_ON_RESOURCE THEN
1066 FPA_UTILITIES_PVT.SET_MESSAGE( p_app_name => 'PA',
1067 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1068 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1069 x_return_status := 'E' ;
1070
1071 WHEN OTHERS THEN
1072 IF SQLCODE = -54 THEN
1073 FPA_UTILITIES_PVT.SET_MESSAGE( p_app_name => 'PA',
1074 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1075 x_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1076 END IF;
1077
1078 END;
1079
1080
1081 l_msg_count := FND_MSG_PUB.count_msg;
1082 IF l_msg_count > 0 THEN
1083 x_msg_count := l_msg_count;
1084 x_return_status := 'E';
1085 RAISE FND_API.G_EXC_ERROR;
1086 END IF;
1087
1088 IF p_commit = FND_API.G_TRUE then
1089 COMMIT;
1090 END IF;
1091
1092 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1093 FND_LOG.String
1094 (
1095 FND_LOG.LEVEL_PROCEDURE,
1096 'fpa.sql.FPA_PROJECT_PVT.UPDATE_PROJ_FUNDING_STATUS.end',
1097 'Entering FPA_PROJECT_PVT.UPDATE_PROJ_FUNDING_STATUS'
1098 );
1099 END IF;
1100
1101
1102 EXCEPTION
1103
1104 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1105
1106 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1107 fnd_msg_pub.add_exc_msg(p_pkg_name => 'FPA_PROJECT_PVT',
1108 p_procedure_name => 'UPDATE_PROJ_FUNDING_STATUS',
1109 p_error_text => SUBSTRB(SQLERRM,1,240));
1110
1111 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1112 p_data => x_msg_data);
1113
1114 IF p_commit = FND_API.G_TRUE THEN
1115 ROLLBACK;
1116 END IF;
1117
1118 raise;
1119
1120 WHEN FND_API.G_EXC_ERROR THEN
1121 IF p_commit = FND_API.G_TRUE THEN
1122 ROLLBACK;
1123 END IF;
1124 x_return_status := 'E';
1125
1126 WHEN OTHERS THEN
1127 IF p_commit = FND_API.G_TRUE THEN
1128 ROLLBACK;
1129 END IF;
1130 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1131 fnd_msg_pub.add_exc_msg(p_pkg_name => 'FPA_PROJECT_PVT',
1132 p_procedure_name => 'UPDATE_PROJ_FUNDING_STATUS',
1133 p_error_text => SUBSTRB(SQLERRM,1,240));
1134
1135 RAISE;
1136
1137 END UPDATE_PROJ_FUNDING_STATUS;
1138
1139
1140 END FPA_PROJECT_PVT;