[Home] [Help]
PACKAGE BODY: APPS.FPA_VALIDATION_PROCESS_PVT
Source
1 package body FPA_VALIDATION_PROCESS_PVT as
2 /* $Header: FPAVVLPB.pls 120.16 2011/08/09 01:17:31 skkoppul noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(200) := 'FPA_VALIDATION_PROCESS_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) := 'VALIDATION_PROCESS';
8
9 PROCEDURE Validate_Project_Details
10 (
11 p_validation_set IN VARCHAR2,
12 p_header_object_id IN NUMBER,
13 p_header_object_type IN VARCHAR2,
14 p_object_id IN NUMBER,
15 p_object_type IN VARCHAR2,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2
19 ) IS
20
21 -- standard parameters
22 l_return_status VARCHAR2(1);
23 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Projects_Details';
24 l_api_version CONSTANT NUMBER := 1.0;
25 l_msg_log VARCHAR2(2000) := null;
26 ----------------------------------------------------------------------------
27
28 CURSOR FUNDING_DATES_CSR (PC_ID IN NUMBER) IS
29 SELECT GLS.START_DATE,
30 GLE.END_DATE
31 FROM FPA_AW_PC_INFO_V PC, GL_PERIODS GLS, GL_PERIODS GLE
32 WHERE PC.CALENDAR_NAME = GLS.PERIOD_SET_NAME
33 AND PC.PERIOD_TYPE = GLS.PERIOD_TYPE
34 AND PC.CALENDAR_NAME = GLE.PERIOD_SET_NAME
35 AND PC.PERIOD_TYPE = GLE.PERIOD_TYPE
36 AND PC.FUNDING_PERIOD_FROM = GLS.PERIOD_NAME
37 AND PC.FUNDING_PERIOD_TO = GLE.PERIOD_NAME
38 AND PC.PLANNING_CYCLE = PC_ID;
39
40
41 CURSOR FIN_PLANS_CSR (P_PROJECT_ID IN NUMBER,
42 P_PLAN_TYPE IN NUMBER,
43 P_START_DATE IN DATE,
44 P_END_DATE IN DATE) IS
45 SELECT 'T'
46 FROM PA_PROJECTS_ALL P, PA_BUDGET_VERSIONS V,
47 PA_BUDGET_LINES L
48 WHERE
49 V.BUDGET_VERSION_ID = L.BUDGET_VERSION_ID
50 AND V.BUDGET_STATUS_CODE = 'B'
51 AND V.CURRENT_FLAG = 'Y'
52 AND V.PROJECT_ID = P.PROJECT_ID
53 AND P.PROJECT_ID = P_PROJECT_ID
54 AND V.FIN_PLAN_TYPE_ID = P_PLAN_TYPE
55 AND ((P_START_DATE BETWEEN L.START_DATE AND L.END_DATE)
56 OR (P_END_DATE BETWEEN L.START_DATE AND L.END_DATE));
57
58
59 l_start_date DATE := NULL;
60 l_end_date DATE := NULL;
61 l_cost_plan_type NUMBER := NULL;
62 l_benefit_plan_type NUMBER := NULL;
63 l_validation BOOLEAN := NULL;
64 l_exists VARCHAR2(1) := NULL;
65
66 BEGIN
67
68 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
69
70 select
71 fnd_profile.value('PJP_FINANCIAL_PLAN_TYPE_COST'),
72 fnd_profile.value('PJP_FINANCIAL_PLAN_TYPE_BENEFIT')
73 into l_cost_plan_type, l_benefit_plan_type from dual;
74 if(l_cost_plan_type is null or l_benefit_plan_type is null) then
75 return;
76 end if;
77
78 open funding_dates_csr(pc_id => p_header_object_id);
79 fetch funding_dates_csr into l_start_date, l_end_date;
80 close funding_dates_csr;
81
82
83 if(l_start_date is null or l_end_date is null) then
84 return;
85 end if;
86
87 open fin_plans_csr(p_project_id => p_object_id,
88 p_plan_type => l_cost_plan_type,
89 p_start_date => l_start_date,
90 p_end_date => l_end_date);
91 fetch fin_plans_csr into l_exists;
92 close fin_plans_csr;
93
94 if(l_exists is null or l_exists <> FND_API.G_TRUE) then
95 l_validation := Fpa_Validation_Pvt.Add_Validation(
96 'FPA_V_PROJ_COST_PTYPE',
97 FPA_VALIDATION_PVT.G_WARNING,
98 p_object_id, 'PROJECT');
99 end if;
100
101 l_exists := null;
102 open fin_plans_csr(p_project_id => p_object_id,
103 p_plan_type => l_benefit_plan_type,
104 p_start_date => l_start_date,
105 p_end_date => l_end_date);
106 fetch fin_plans_csr into l_exists;
107
108 close fin_plans_csr;
109 if(l_exists is null or l_exists <> FND_API.G_TRUE) then
110 l_validation := Fpa_Validation_Pvt.Add_Validation(
111 'FPA_V_PROJ_BENF_PTYPE',
112 FPA_VALIDATION_PVT.G_WARNING,
113 p_object_id, 'PROJECT');
114 end if;
115
116 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
117
118 FPA_UTILITIES_PVT.END_ACTIVITY(
119 p_api_name => l_api_name,
120 p_pkg_name => G_PKG_NAME,
121 p_msg_log => null,
122 x_msg_count => x_msg_count,
123 x_msg_data => x_msg_data);
124
125
126 EXCEPTION
127 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
128 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
129 p_api_name => l_api_name,
130 p_pkg_name => G_PKG_NAME,
131 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
132 p_msg_log => l_msg_log,
133 x_msg_count => x_msg_count,
134 x_msg_data => x_msg_data,
135 p_api_type => G_API_TYPE);
136
137 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
138 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
139 p_api_name => l_api_name,
140 p_pkg_name => G_PKG_NAME,
141 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
142 p_msg_log => l_msg_log,
143 x_msg_count => x_msg_count,
144 x_msg_data => x_msg_data,
145 p_api_type => G_API_TYPE);
146
147 when OTHERS then
148 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
149 p_api_name => l_api_name,
150 p_pkg_name => G_PKG_NAME,
151 p_exc_name => 'OTHERS',
152 p_msg_log => l_msg_log||SQLERRM,
153 x_msg_count => x_msg_count,
154 x_msg_data => x_msg_data,
155 p_api_type => G_API_TYPE);
156
157 END Validate_Project_Details;
158
159
160 PROCEDURE Create_Proj_Budget_Versions
161 (
162 p_api_version IN NUMBER,
163 p_init_msg_list IN VARCHAR2,
164 p_validation_set IN VARCHAR2,
165 p_scen_vline_id IN NUMBER,
166 p_project_id IN NUMBER,
167 p_cost_bversion_id IN NUMBER,
168 p_benefit_bversion_id IN NUMBER,
169 x_return_status OUT NOCOPY VARCHAR2,
170 x_msg_count OUT NOCOPY NUMBER,
171 x_msg_data OUT NOCOPY VARCHAR2
172 ) IS
173
174 -- standard parameters
175 l_return_status VARCHAR2(1);
176 l_init_msg_list VARCHAR2(1) := 'F';
177 l_api_name CONSTANT VARCHAR2(30) := 'Create_Proj_Budget_Versions';
178 l_api_version CONSTANT NUMBER := 1.0;
179 l_msg_log VARCHAR2(2000) := null;
180 l_msg_count NUMBER;
181 l_msg_data VARCHAR2(2000);
182 ----------------------------------------------------------------------------
183
184 L_VALIDATION_LINES_REC FPA_VALIDATION_PVT.FPA_VALIDATION_LINES_REC;
185 L_PROJ_VALIDATION_ID NUMBER;
186
187 L_COST_VLINE_ID NUMBER;
188 L_BENEFIT_VLINE_ID NUMBER;
189
190
191 BEGIN
192
193 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
194
195 BEGIN
196 SELECT
197 C.VALIDATION_ID,
198 B.VALIDATION_ID
199 INTO
200 L_COST_VLINE_ID, L_BENEFIT_VLINE_ID
201 FROM
202 FPA_VALIDATION_LINES S,
203 FPA_VALIDATION_LINES P,
204 FPA_VALIDATION_LINES C,
205 FPA_VALIDATION_LINES B
206 WHERE
207 S.VALIDATION_ID = P_SCEN_VLINE_ID
208 AND P.OBJECT_TYPE = 'BUDGET_VERSIONS_PROJ'
209 AND P.HEADER_ID = S.VALIDATION_ID
210 AND C.OBJECT_TYPE = 'BUDGET_VERSION_COST'
211 AND C.HEADER_ID = P.VALIDATION_ID
212 AND C.VALIDATION_TYPE = 'FPA_V_PROJ_COST_VERSION'
213 AND B.OBJECT_TYPE = 'BUDGET_VERSION_BENEFIT'
214 AND B.HEADER_ID = P.VALIDATION_ID
215 AND B.VALIDATION_TYPE = 'FPA_V_PROJ_BENEFIT_VERSION'
216 AND P.OBJECT_ID = P_PROJECT_ID;
217
218 DELETE FROM FPA_VALIDATION_LINES
219 WHERE VALIDATION_ID IN (L_COST_VLINE_ID, L_BENEFIT_VLINE_ID);
220
221 EXCEPTION
222 WHEN OTHERS THEN
223 NULL;
224 END;
225
226 l_validation_lines_rec := null;
227 l_validation_lines_rec.header_id := p_scen_vline_id;
228 l_validation_lines_rec.object_id := p_project_id;
229 l_validation_lines_rec.object_type := 'BUDGET_VERSIONS_PROJ';
230 l_validation_lines_rec.validation_type := p_validation_set;
231 l_validation_lines_rec.message_id := null;
232 l_validation_lines_rec.severity := 'I';
233
234 Fpa_Validation_Pvt.Create_Validation_Line(
235 p_api_version => l_api_version,
236 p_init_msg_list => l_init_msg_list,
237 p_validation_set => p_validation_set,
238 p_validation_lines_rec => l_validation_lines_rec,
239 x_validation_id => l_proj_validation_id,
240 x_return_status => l_return_status,
241 x_msg_count => l_msg_count,
242 x_msg_data => l_msg_data);
243
244 if (l_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
245 l_msg_log := 'BUDGET_VERSIONS_PROJ-Create_Validation_Line';
246 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
247 elsif (l_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
248 l_msg_log := 'BUDGET_VERSIONS_PROJ-Create_Validation_Line';
249 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
250 end if;
251
252 l_validation_lines_rec := null;
253 l_validation_lines_rec.header_id := l_proj_validation_id;
254 l_validation_lines_rec.object_id := p_cost_bversion_id;
255 l_validation_lines_rec.object_type := 'BUDGET_VERSION_COST';
256 l_validation_lines_rec.validation_type := 'FPA_V_PROJ_COST_VERSION';
257 l_validation_lines_rec.message_id := 'FPA_V_PROJ_COST_VERSION';
258 l_validation_lines_rec.severity := 'I';
259
260 Fpa_Validation_Pvt.Create_Validation_Line(
261 p_api_version => l_api_version,
262 p_init_msg_list => l_init_msg_list,
263 p_validation_set => p_validation_set,
264 p_validation_lines_rec => l_validation_lines_rec,
265 x_validation_id => l_validation_lines_rec.validation_id,
266 x_return_status => l_return_status,
267 x_msg_count => l_msg_count,
268 x_msg_data => l_msg_data);
269
270 if (l_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
271 l_msg_log := 'BUDGET_VERSION_COST-Create_Validation_Line';
272 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
273 elsif (l_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
274 l_msg_log := 'BUDGET_VERSION_COST-Create_Validation_Line';
275 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
276 end if;
277
278 l_validation_lines_rec := null;
279 l_validation_lines_rec.header_id := l_proj_validation_id;
280 l_validation_lines_rec.object_id := p_benefit_bversion_id;
281 l_validation_lines_rec.object_type := 'BUDGET_VERSION_BENEFIT';
282 l_validation_lines_rec.validation_type := 'FPA_V_PROJ_BENEFIT_VERSION';
283 l_validation_lines_rec.message_id := 'FPA_V_PROJ_BENEFIT_VERSION';
284 l_validation_lines_rec.severity := 'I';
285
286 Fpa_Validation_Pvt.Create_Validation_Line(
287 p_api_version => l_api_version,
288 p_init_msg_list => l_init_msg_list,
289 p_validation_set => p_validation_set,
290 p_validation_lines_rec => l_validation_lines_rec,
291 x_validation_id => l_validation_lines_rec.validation_id,
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_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
297 l_msg_log := 'BUDGET_VERSION_BENEFIT-Create_Validation_Line';
298 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
299 elsif (l_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
300 l_msg_log := 'BUDGET_VERSION_BENEFIT-Create_Validation_Line';
301 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
302 end if;
303
304 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
305
306 FPA_UTILITIES_PVT.END_ACTIVITY(
307 p_api_name => l_api_name,
308 p_pkg_name => G_PKG_NAME,
309 p_msg_log => null,
310 x_msg_count => x_msg_count,
311 x_msg_data => x_msg_data);
312
313
314 EXCEPTION
315 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
316 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
317 p_api_name => l_api_name,
318 p_pkg_name => G_PKG_NAME,
319 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
320 p_msg_log => l_msg_log,
321 x_msg_count => x_msg_count,
322 x_msg_data => x_msg_data,
323 p_api_type => G_API_TYPE);
324
325 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
326 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
327 p_api_name => l_api_name,
328 p_pkg_name => G_PKG_NAME,
329 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
330 p_msg_log => l_msg_log,
331 x_msg_count => x_msg_count,
332 x_msg_data => x_msg_data,
333 p_api_type => G_API_TYPE);
334
335 when OTHERS then
336 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
337 p_api_name => l_api_name,
338 p_pkg_name => G_PKG_NAME,
339 p_exc_name => 'OTHERS',
340 p_msg_log => l_msg_log||SQLERRM,
341 x_msg_count => x_msg_count,
342 x_msg_data => x_msg_data,
343 p_api_type => G_API_TYPE);
344
345 END Create_Proj_Budget_Versions;
346
347
348 PROCEDURE Update_Proj_Budget_Versions
349 (
350 p_api_version IN NUMBER,
351 p_init_msg_list IN VARCHAR2,
352 p_validation_set IN VARCHAR2,
353 p_scen_vline_id IN NUMBER,
354 p_project_id IN NUMBER,
355 p_cost_bversion_id IN NUMBER,
356 p_benefit_bversion_id IN NUMBER,
357 x_return_status OUT NOCOPY VARCHAR2,
358 x_msg_count OUT NOCOPY NUMBER,
359 x_msg_data OUT NOCOPY VARCHAR2
360 ) IS
361
362 -- standard parameters
363 l_return_status VARCHAR2(1);
364 l_init_msg_list VARCHAR2(1) := 'F';
365 l_api_name CONSTANT VARCHAR2(30) := 'Update_Proj_Budget_Versions';
366 l_api_version CONSTANT NUMBER := 1.0;
367 l_msg_log VARCHAR2(2000) := null;
368 l_msg_count NUMBER;
369 l_msg_data VARCHAR2(2000);
370 ----------------------------------------------------------------------------
371
372 L_COST_VLINE_ID NUMBER;
373 L_BENEFIT_VLINE_ID NUMBER;
374
375 BEGIN
376
377 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
378
379 SELECT
380 C.VALIDATION_ID,
381 B.VALIDATION_ID
382 INTO
383 L_COST_VLINE_ID, L_BENEFIT_VLINE_ID
384 FROM
385 FPA_VALIDATION_LINES S,
386 FPA_VALIDATION_LINES P,
387 FPA_VALIDATION_LINES C,
388 FPA_VALIDATION_LINES B
389 WHERE
390 S.VALIDATION_ID = P_SCEN_VLINE_ID
391 AND P.OBJECT_TYPE = 'BUDGET_VERSIONS_PROJ'
392 AND P.HEADER_ID = S.VALIDATION_ID
393 AND C.OBJECT_TYPE = 'BUDGET_VERSION_COST'
394 AND C.HEADER_ID = P.VALIDATION_ID
395 AND C.VALIDATION_TYPE = 'FPA_V_PROJ_COST_VERSION'
396 AND B.OBJECT_TYPE = 'BUDGET_VERSION_BENEFIT'
397 AND B.HEADER_ID = P.VALIDATION_ID
398 AND B.VALIDATION_TYPE = 'FPA_V_PROJ_BENEFIT_VERSION'
399 AND P.OBJECT_ID = P_PROJECT_ID;
400
401 UPDATE
402 FPA_VALIDATION_LINES
403 SET
404 OBJECT_ID = P_COST_BVERSION_ID,
405 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
406 LAST_UPDATE_DATE = SYSDATE,
407 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
408 WHERE
409 VALIDATION_ID = L_COST_VLINE_ID;
410
411 UPDATE
412 FPA_VALIDATION_LINES
413 SET
414 OBJECT_ID = P_BENEFIT_BVERSION_ID,
415 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
416 LAST_UPDATE_DATE = SYSDATE,
417 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
418 WHERE
419 VALIDATION_ID = L_BENEFIT_VLINE_ID;
420
421 EXCEPTION
422 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
423 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
424 p_api_name => l_api_name,
425 p_pkg_name => G_PKG_NAME,
426 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
427 p_msg_log => l_msg_log,
428 x_msg_count => x_msg_count,
429 x_msg_data => x_msg_data,
430 p_api_type => G_API_TYPE);
431
432 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
433 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
434 p_api_name => l_api_name,
435 p_pkg_name => G_PKG_NAME,
436 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
437 p_msg_log => l_msg_log,
438 x_msg_count => x_msg_count,
439 x_msg_data => x_msg_data,
440 p_api_type => G_API_TYPE);
441
442 when OTHERS then
443 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
444 p_api_name => l_api_name,
445 p_pkg_name => G_PKG_NAME,
446 p_exc_name => 'OTHERS',
447 p_msg_log => l_msg_log||SQLERRM,
448 x_msg_count => x_msg_count,
449 x_msg_data => x_msg_data,
450 p_api_type => G_API_TYPE);
451
452 END Update_Proj_Budget_Versions;
453
454
455
456 PROCEDURE Budget_Version_Validations
457 (
458 p_api_version IN NUMBER,
459 p_init_msg_list IN VARCHAR2,
460 p_validation_set IN VARCHAR2,
461 p_header_object_id IN NUMBER,
462 p_header_object_type IN VARCHAR2,
463 x_return_status OUT NOCOPY VARCHAR2,
464 x_msg_count OUT NOCOPY NUMBER,
465 x_msg_data OUT NOCOPY VARCHAR2
466 ) IS
467
468 -- standard parameters
469 l_return_status VARCHAR2(1);
470 l_init_msg_list VARCHAR2(1) := 'F';
471 l_api_name CONSTANT VARCHAR2(30) := 'Budget_Version_Validations';
472 l_api_version CONSTANT NUMBER := 1.0;
473 l_msg_log VARCHAR2(2000) := null;
474 l_msg_count NUMBER;
475 l_msg_data VARCHAR2(2000);
476 ----------------------------------------------------------------------------
477
478
479 CURSOR BUDGET_VERS_CSR (P_SCENARIO_ID IN NUMBER) IS
480 SELECT
481 S.PROJECT PROJECT,
482 DECODE(C.BUDGET_VERSION_ID,NULL,-1,C.BUDGET_VERSION_ID) COST_BUDGET_VERSN_ID,
483 DECODE(B.BUDGET_VERSION_ID,NULL,-1,B.BUDGET_VERSION_ID) BENF_BUDGET_VERSN_ID
484 FROM
485 PA_BUDGET_VERSIONS C, PA_BUDGET_VERSIONS B,
486 FPA_AW_PROJ_INFO_V S
487 WHERE
488 'B' = C.BUDGET_STATUS_CODE (+) AND 'Y' = C.CURRENT_FLAG (+)
489 AND FND_PROFILE.value('PJP_FINANCIAL_PLAN_TYPE_COST') = C.FIN_PLAN_TYPE_ID (+)
490 AND S.PROJECT = C.PROJECT_ID (+)
491 AND 'B' = B.BUDGET_STATUS_CODE (+) AND 'Y' = B.CURRENT_FLAG (+)
492 AND FND_PROFILE.value('PJP_FINANCIAL_PLAN_TYPE_BENEFIT') = B.FIN_PLAN_TYPE_ID (+)
493 AND S.PROJECT = B.PROJECT_ID (+)
494 AND S.SCENARIO = P_SCENARIO_ID;
495
496 L_PC_ID NUMBER := NULL;
497 L_SCENARIO_ID NUMBER := NULL;
498
499 BUDGET_VERSIONS_REC BUDGET_VERS_CSR%ROWTYPE;
500 L_VALIDATION_LINES_REC FPA_VALIDATION_PVT.FPA_VALIDATION_LINES_REC;
501 L_SCEN_VALIDATION_ID NUMBER;
502 L_PROJ_VALIDATION_ID NUMBER;
503
504 BEGIN
505
506 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
507 fnd_log.string ( FND_LOG.LEVEL_PROCEDURE,
508 'fpa.sql.FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations 1.begin',
509 'Entering FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations 1.');
510 END IF;
511
512 dbms_aw.execute('ALLSTAT');
513 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
514
515 if(p_header_object_type = 'SCENARIO') then
516 l_scenario_id := p_header_object_id;
517 else
518 SELECT SCENARIO INTO L_SCENARIO_ID
519 FROM FPA_AW_SCE_INFO_V
520 WHERE IS_INITIAL_SCENARIO = 1 AND PLANNING_CYCLE = P_HEADER_OBJECT_ID;
521
522 end if;
523
524 l_validation_lines_rec := null;
525 l_validation_lines_rec.header_id := null;
526 l_validation_lines_rec.object_id := l_scenario_id;
527 l_validation_lines_rec.object_type := 'BUDGET_VERSIONS_SCENARIO';
528 l_validation_lines_rec.validation_type := p_validation_set;
529 l_validation_lines_rec.message_id := null;
530 l_validation_lines_rec.severity := 'I';
531
532 Fpa_Validation_Pvt.Create_Validation_Line(
533 p_api_version => l_api_version,
534 p_init_msg_list => l_init_msg_list,
535 p_validation_set => p_validation_set,
536 p_validation_lines_rec => l_validation_lines_rec,
537 x_validation_id => l_scen_validation_id,
538 x_return_status => l_return_status,
539 x_msg_count => l_msg_count,
540 x_msg_data => l_msg_data);
541
542 if (l_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
543 l_msg_log := 'BUDGET_VERSIONS_SCENARIO-Create_Validation_Line';
544 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
545 elsif (l_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
546 l_msg_log := 'BUDGET_VERSIONS_SCENARIO-Create_Validation_Line';
547 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
548 end if;
549
550
551
552 FOR budget_versions_rec in budget_vers_csr(l_scenario_id) LOOP
553
554 Create_Proj_Budget_Versions(
555 p_api_version => l_api_version,
556 p_init_msg_list => l_init_msg_list,
557 p_validation_set => p_validation_set,
558 p_scen_vline_id => l_scen_validation_id,
559 p_project_id => budget_versions_rec.project,
560 p_cost_bversion_id => budget_versions_rec.cost_budget_versn_id,
561 p_benefit_bversion_id => budget_versions_rec.benf_budget_versn_id,
562 x_return_status => l_return_status,
563 x_msg_count => l_msg_count,
564 x_msg_data => l_msg_data);
565
566 if (l_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
567 l_msg_log := 'BUDGET_VERSIONS_PROJ-Create_Validation_Line';
568 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
569 elsif (l_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
570 l_msg_log := 'BUDGET_VERSIONS_PROJ-Create_Validation_Line';
571 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
572 end if;
573
574 END LOOP;
575
576 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
577
578 FPA_UTILITIES_PVT.END_ACTIVITY(
579 p_api_name => l_api_name,
580 p_pkg_name => G_PKG_NAME,
581 p_msg_log => null,
582 x_msg_count => x_msg_count,
583 x_msg_data => x_msg_data);
584
585
586 EXCEPTION
587 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
588 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
589 p_api_name => l_api_name,
590 p_pkg_name => G_PKG_NAME,
591 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
592 p_msg_log => l_msg_log,
593 x_msg_count => x_msg_count,
594 x_msg_data => x_msg_data,
595 p_api_type => G_API_TYPE);
596
597 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
598 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
599 p_api_name => l_api_name,
600 p_pkg_name => G_PKG_NAME,
601 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
602 p_msg_log => l_msg_log,
603 x_msg_count => x_msg_count,
604 x_msg_data => x_msg_data,
605 p_api_type => G_API_TYPE);
606
607
608 when OTHERS then
609 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
610 p_api_name => l_api_name,
611 p_pkg_name => G_PKG_NAME,
612 p_exc_name => 'OTHERS',
613 p_msg_log => l_msg_log||SQLERRM,
614 x_msg_count => x_msg_count,
615 x_msg_data => x_msg_data,
616 p_api_type => G_API_TYPE);
617
618 END Budget_Version_Validations;
619
620
621 PROCEDURE Budget_Version_Validations
622 (
623 p_api_version IN NUMBER,
624 p_init_msg_list IN VARCHAR2,
625 p_validation_set IN VARCHAR2,
626 p_header_object_id IN NUMBER,
627 p_header_object_type IN VARCHAR2,
628 p_line_projects_tbl IN PROJECT_ID_TBL_TYPE,
629 p_type IN VARCHAR2,
630 x_return_status OUT NOCOPY VARCHAR2,
631 x_msg_count OUT NOCOPY NUMBER,
632 x_msg_data OUT NOCOPY VARCHAR2
633 ) IS
634
635 cursor val_lines_csr(sce_id IN NUMBER,
636 validation_set IN VARCHAR2) is
637 SELECT VALIDATION_ID
638 FROM FPA_VALIDATION_LINES
639 WHERE OBJECT_TYPE = 'BUDGET_VERSIONS_SCENARIO' AND
640 OBJECT_ID = sce_id AND
641 HEADER_ID IS NULL AND
642 VALIDATION_TYPE = validation_set;
643
644 -- standard parameters
645 l_return_status VARCHAR2(1);
646 l_init_msg_list VARCHAR2(1) := 'F';
647 l_api_name CONSTANT VARCHAR2(30) := 'Budget_Version_Validations';
648 l_api_version CONSTANT NUMBER := 1.0;
649 l_msg_log VARCHAR2(2000) := null;
650 l_msg_count NUMBER;
651 l_msg_data VARCHAR2(2000);
652 ----------------------------------------------------------------------------
653
654 TYPE BUDGET_VERS_CSR IS REF CURSOR;
655 L_BUDGET_VERS_CSR BUDGET_VERS_CSR;
656
657 I NUMBER;
658 L_SQL_STR VARCHAR2(1000);
659 L_SCENARIO_ID NUMBER;
660 L_COST_BVERSION_ID NUMBER;
661 L_BENEFIT_BVERSION_ID NUMBER;
662 L_SCEN_VALIDATION_ID NUMBER;
663
664 l_type VARCHAR2(10);
665
666 BEGIN
667
668 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
669 fnd_log.string ( FND_LOG.LEVEL_PROCEDURE,
670 'fpa.sql.FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations 2.',
671 'Entering FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations 2.begin');
672 END IF;
673
674 l_type := p_type;
675
676 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
677
678 IF(p_header_object_type = 'SCENARIO') THEN
679 l_scenario_id := p_header_object_id;
680 ELSE
681 SELECT SCENARIO INTO L_SCENARIO_ID
682 FROM FPA_AW_SCE_INFO_V
683 WHERE IS_INITIAL_SCENARIO = 1 AND PLANNING_CYCLE = P_HEADER_OBJECT_ID;
684
685 END IF;
686
687 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
688 fnd_log.string ( FND_LOG.LEVEL_PROCEDURE,
689 'fpa.sql.FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations 2.',
690 'Querying FPA_VALIDATION_LINES to obtain current validation ID. Values are, scenario id: ' || l_scenario_id || ', Validation Type: ' || p_validation_set);
691 END IF;
692
693 -- Bug Reference : 6006705
694 -- We need to trap the no data found condition here.
695 BEGIN
696 open val_lines_csr(sce_id => L_SCENARIO_ID,
697 validation_set => P_VALIDATION_SET);
698 fetch val_lines_csr into L_SCEN_VALIDATION_ID;
699 EXCEPTION
700 WHEN NO_DATA_FOUND THEN
701 L_SCEN_VALIDATION_ID := 0;
702 l_type := 'CREATE';
703 WHEN OTHERS THEN
704 NULL;
705 END;
706 close val_lines_csr;
707
708 -- Check value returned from cursor above, if the value is null then this is an upgrade step
709 -- and need to default it to a value.
710 if L_SCEN_VALIDATION_ID is null then
711 L_SCEN_VALIDATION_ID := 0;
712 l_type := 'CREATE';
713 end if;
714
715 /*
716 SELECT VALIDATION_ID
717 INTO L_SCEN_VALIDATION_ID
718 FROM FPA_VALIDATION_LINES
719 WHERE OBJECT_TYPE = 'BUDGET_VERSIONS_SCENARIO' AND
720 OBJECT_ID = L_SCENARIO_ID AND
721 HEADER_ID IS NULL AND
722 VALIDATION_TYPE = P_VALIDATION_SET;
723 */
724
725 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
726 fnd_log.string ( FND_LOG.LEVEL_PROCEDURE,
727 'fpa.sql.FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations 2.',
728 'Constructing dynamic SQL to query Budget Version information.');
729 END IF;
730
731 l_sql_str := 'SELECT '
732 || 'DECODE(C.BUDGET_VERSION_ID,NULL,-1,C.BUDGET_VERSION_ID) COST_BUDGET_VERSN_ID, '
733 || 'DECODE(B.BUDGET_VERSION_ID,NULL,-1,B.BUDGET_VERSION_ID) BENF_BUDGET_VERSN_ID '
734 || 'FROM PA_PROJECTS_ALL P, PA_BUDGET_VERSIONS C, PA_BUDGET_VERSIONS B '
735 || 'WHERE ''B'' = C.BUDGET_STATUS_CODE (+) AND ''Y'' = C.CURRENT_FLAG (+) '
736 || 'AND fnd_profile.value(''PJP_FINANCIAL_PLAN_TYPE_COST'') = C.FIN_PLAN_TYPE_ID (+) '
737 || 'AND ''B'' = B.BUDGET_STATUS_CODE (+) AND ''Y'' = B.CURRENT_FLAG (+) '
738 || 'AND fnd_profile.value(''PJP_FINANCIAL_PLAN_TYPE_BENEFIT'') = B.FIN_PLAN_TYPE_ID (+) '
739 || 'AND P.PROJECT_ID = C.PROJECT_ID (+) AND P.PROJECT_ID = B.PROJECT_ID (+) '
740 || 'AND P.PROJECT_ID = :1';
741
742 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
743 fnd_log.string ( FND_LOG.LEVEL_PROCEDURE,
744 'fpa.sql.FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations 2.',
745 'Entering loop for insert or updates into FPA_VALIDATION_LINES. Operation to be performed is: ' || l_type);
746 END IF;
747
748
749 FOR i IN p_line_projects_tbl.first .. p_line_projects_tbl.last LOOP
750
751 OPEN l_budget_vers_csr FOR l_sql_str USING p_line_projects_tbl(i);
752 FETCH l_budget_vers_csr INTO l_cost_bversion_id, l_benefit_bversion_id;
753 CLOSE l_budget_vers_csr;
754
755 IF(l_type = 'CREATE') THEN
756
757 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
758 fnd_log.string ( FND_LOG.LEVEL_PROCEDURE,
759 'fpa.sql.FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations 2.',
760 'Calling procedure Create_Proj_Budget_Versions.');
761 END IF;
762
763 Create_Proj_Budget_Versions(
764 p_api_version => l_api_version,
765 p_init_msg_list => l_init_msg_list,
766 p_validation_set => p_validation_set,
767 p_scen_vline_id => l_scen_validation_id,
768 p_project_id => p_line_projects_tbl(i),
769 p_cost_bversion_id => l_cost_bversion_id,
770 p_benefit_bversion_id => l_benefit_bversion_id,
771 x_return_status => l_return_status,
772 x_msg_count => l_msg_count,
773 x_msg_data => l_msg_data);
774
775 ELSIF(l_type = 'UPDATE') THEN
776
777 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
778 fnd_log.string ( FND_LOG.LEVEL_PROCEDURE,
779 'fpa.sql.FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations 2.',
780 'Calling procedure Update_Proj_Budget_Versions.');
781 END IF;
782
783 Update_Proj_Budget_Versions(
784 p_api_version => l_api_version,
785 p_init_msg_list => l_init_msg_list,
786 p_validation_set => p_validation_set,
787 p_scen_vline_id => l_scen_validation_id,
788 p_project_id => p_line_projects_tbl(i),
789 p_cost_bversion_id => l_cost_bversion_id,
790 p_benefit_bversion_id => l_benefit_bversion_id,
791 x_return_status => l_return_status,
792 x_msg_count => l_msg_count,
793 x_msg_data => l_msg_data);
794
795 END IF;
796
797 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
798 fnd_log.string ( FND_LOG.LEVEL_PROCEDURE,
799 'fpa.sql.FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations 2.',
800 'Checking return status inside loop for inserting creating FPA_VALIDATION_LINES.');
801 END IF;
802
803 if (l_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
804 l_msg_log := 'BUDGET_VERSIONS_PROJ-Create_Validation_Line';
805 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
806 elsif (l_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
807 l_msg_log := 'BUDGET_VERSIONS_PROJ-Create_Validation_Line';
808 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
809 end if;
810
811 END LOOP;
812
813 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
814 fnd_log.string ( FND_LOG.LEVEL_PROCEDURE,
815 'fpa.sql.FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations 2.',
816 'Finished loop for inserting updating FPA_VALIDATION_LINES.');
817 END IF;
818
819 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
820 fnd_log.string ( FND_LOG.LEVEL_PROCEDURE,
821 'fpa.sql.FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations 2.',
822 'Calling FPA_UTILITIES_PVT.G_RET_STS_SUCCESS.');
823 END IF;
824
825 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
826
827 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
828 fnd_log.string ( FND_LOG.LEVEL_PROCEDURE,
829 'fpa.sql.FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations 2.',
830 'Calling FPA_UTILITIES_PVT.END_ACTIVITY.');
831 END IF;
832
833 FPA_UTILITIES_PVT.END_ACTIVITY(
834 p_api_name => l_api_name,
835 p_pkg_name => G_PKG_NAME,
836 p_msg_log => null,
837 x_msg_count => x_msg_count,
838 x_msg_data => x_msg_data);
839
840 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
841 fnd_log.string ( FND_LOG.LEVEL_PROCEDURE,
842 'fpa.sql.FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations 2.end.',
843 'Finishing FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations 2.');
844 END IF;
845
846
847 EXCEPTION
848 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
849 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
850 p_api_name => l_api_name,
851 p_pkg_name => G_PKG_NAME,
852 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
853 p_msg_log => l_msg_log,
854 x_msg_count => x_msg_count,
855 x_msg_data => x_msg_data,
856 p_api_type => G_API_TYPE);
857
858 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
859 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
860 p_api_name => l_api_name,
861 p_pkg_name => G_PKG_NAME,
862 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
863 p_msg_log => l_msg_log,
864 x_msg_count => x_msg_count,
865 x_msg_data => x_msg_data,
866 p_api_type => G_API_TYPE);
867
868 when OTHERS then
869 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
870 p_api_name => l_api_name,
871 p_pkg_name => G_PKG_NAME,
872 p_exc_name => 'OTHERS',
873 p_msg_log => l_msg_log||SQLERRM,
874 x_msg_count => x_msg_count,
875 x_msg_data => x_msg_data,
876 p_api_type => G_API_TYPE);
877
878 END Budget_Version_Validations;
879
880
881 PROCEDURE Validate_Collect_Projects
882 (
883 p_api_version IN NUMBER,
884 p_init_msg_list IN VARCHAR2,
885 p_validation_set IN VARCHAR2,
886 p_header_object_id IN NUMBER,
887 x_return_status OUT NOCOPY VARCHAR2,
888 x_msg_count OUT NOCOPY NUMBER,
889 x_msg_data OUT NOCOPY VARCHAR2
890 ) IS
891
892 -- standard parameters
893 l_return_status VARCHAR2(1);
894 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Collect_Projects';
895 l_api_version CONSTANT NUMBER := 1.0;
896 l_msg_log VARCHAR2(2000) := null;
897 ----------------------------------------------------------------------------
898 -- l_org_id NUMBER;
899 l_level_error BOOLEAN := FALSE;
900
901 CURSOR COLLECT_PROJECTS_CSR
902 (P_PC_ID IN NUMBER) IS
903 SELECT
904 PROJECT
905 FROM FPA_AW_PROJS_V
906 WHERE PLANNING_CYCLE = P_PC_ID;
907
908 l_portfolio_id FPA_AW_PORTF_HEADERS_V.PORTFOLIO%TYPE := null;
909 l_current_pc_id FPA_AW_PC_INFO_V.PLANNING_CYCLE%TYPE := null;
910 l_class_code_id NUMBER;
911 l_valid_project VARCHAR2(1) := FND_API.G_FALSE;
912 l_validation BOOLEAN := FALSE;
913
914 collect_projects_rec collect_projects_csr%ROWTYPE;
915
916 BEGIN
917 l_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
918
919 FOR collect_projects_rec in collect_projects_csr(p_header_object_id) LOOP
920
921 Fpa_Project_Pvt.Get_Project_Details(
922 p_project_id => collect_projects_rec.project,
923 x_proj_portfolio => l_portfolio_id,
924 x_proj_pc => l_current_pc_id,
925 x_class_code_id => l_class_code_id,
926 x_valid_project => l_valid_project,
927 x_return_status => x_return_status,
928 x_msg_count => x_msg_count,
929 x_msg_data => x_msg_data);
930
931 Validate_Project_Details(
932 p_validation_set => p_validation_set,
933 p_header_object_id => p_header_object_id,
934 p_header_object_type => 'PLANNING_CYCLE',
935 p_object_id => collect_projects_rec.project,
936 p_object_type => 'PROJECT',
937 x_return_status => x_return_status,
938 x_msg_count => x_msg_count,
939 x_msg_data => x_msg_data);
940
941 l_level_error := FPA_VALIDATION_PVT.Check_Error_Level(
942 collect_projects_rec.project,
943 'PROJECT',
944 FPA_VALIDATION_PVT.G_ERROR);
945
946 if(not l_level_error) then
947 l_validation := Fpa_Validation_Pvt.Add_Validation(
948 'FPA_V_PROJECT_SUBMITTED',
949 FPA_VALIDATION_PVT.G_INFORMATION,
950 collect_projects_rec.project,
951 'PROJECT');
952 end if;
953
954 END LOOP;
955
956
957 FPA_UTILITIES_PVT.END_ACTIVITY(
958 p_api_name => l_api_name,
959 p_pkg_name => G_PKG_NAME,
960 p_msg_log => null,
961 x_msg_count => x_msg_count,
962 x_msg_data => x_msg_data);
963
964
965 EXCEPTION
966 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
967 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
968 p_api_name => l_api_name,
969 p_pkg_name => G_PKG_NAME,
970 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
971 p_msg_log => l_msg_log,
972 x_msg_count => x_msg_count,
973 x_msg_data => x_msg_data,
974 p_api_type => G_API_TYPE);
975
976 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
977 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
978 p_api_name => l_api_name,
979 p_pkg_name => G_PKG_NAME,
980 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
981 p_msg_log => l_msg_log,
982 x_msg_count => x_msg_count,
983 x_msg_data => x_msg_data,
984 p_api_type => G_API_TYPE);
985
986 when OTHERS then
987 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
988 p_api_name => l_api_name,
989 p_pkg_name => G_PKG_NAME,
990 p_exc_name => 'OTHERS',
991 p_msg_log => l_msg_log||SQLERRM,
992 x_msg_count => x_msg_count,
993 x_msg_data => x_msg_data,
994 p_api_type => G_API_TYPE);
995
996 END Validate_Collect_Projects;
997
998
999 PROCEDURE Validate_Proj_Refresh_Plans
1000 (
1001 p_project_id IN NUMBER,
1002 x_return_status OUT NOCOPY VARCHAR2,
1003 x_msg_count OUT NOCOPY NUMBER,
1004 x_msg_data OUT NOCOPY VARCHAR2
1005 ) IS
1006
1007 -- standard parameters
1008 l_return_status VARCHAR2(1);
1009 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Proj_Refresh_Plans';
1010 l_api_version CONSTANT NUMBER := 1.0;
1011 l_msg_log VARCHAR2(2000) := null;
1012 ----------------------------------------------------------------------------
1013
1014 CURSOR FIN_PLANS_CSR (P_PROJECT_ID IN NUMBER,
1015 P_PLAN_TYPE IN NUMBER) IS
1016 SELECT 'T'
1017 FROM PA_PROJECTS_ALL P, PA_BUDGET_VERSIONS V,
1018 PA_BUDGET_LINES L
1019 WHERE
1020 V.BUDGET_VERSION_ID = L.BUDGET_VERSION_ID
1021 AND V.BUDGET_STATUS_CODE = 'B'
1022 AND V.CURRENT_FLAG = 'Y'
1023 AND V.PROJECT_ID = P.PROJECT_ID
1024 AND P.PROJECT_ID = P_PROJECT_ID
1025 AND V.FIN_PLAN_TYPE_ID = P_PLAN_TYPE;
1026
1027
1028 l_cost_plan_type NUMBER := NULL;
1029 l_benefit_plan_type NUMBER := NULL;
1030 l_validation BOOLEAN := NULL;
1031 l_exists VARCHAR2(1) := NULL;
1032
1033 BEGIN
1034
1035 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
1036
1037 SELECT
1038 FND_PROFILE.VALUE('PJP_FINANCIAL_PLAN_TYPE_COST'),
1039 FND_PROFILE.VALUE('PJP_FINANCIAL_PLAN_TYPE_BENEFIT')
1040 INTO L_COST_PLAN_TYPE, L_BENEFIT_PLAN_TYPE FROM DUAL;
1041
1042 if(l_cost_plan_type is null or l_benefit_plan_type is null) then
1043 return;
1044 end if;
1045
1046 open fin_plans_csr(p_project_id => p_project_id,
1047 p_plan_type => l_cost_plan_type);
1048 fetch fin_plans_csr into l_exists;
1049 close fin_plans_csr;
1050
1051 if(l_exists is null or l_exists <> FND_API.G_TRUE) then
1052 l_validation := Fpa_Validation_Pvt.Add_Validation(
1053 'FPA_V_PROJ_COST_PTYPE',
1054 FPA_VALIDATION_PVT.G_WARNING,
1055 p_project_id, 'PROJECT');
1056 end if;
1057
1058 l_exists := null;
1059 open fin_plans_csr(p_project_id => p_project_id,
1060 p_plan_type => l_benefit_plan_type);
1061 fetch fin_plans_csr into l_exists;
1062
1063 close fin_plans_csr;
1064 if(l_exists is null or l_exists <> FND_API.G_TRUE) then
1065 l_validation := Fpa_Validation_Pvt.Add_Validation(
1066 'FPA_V_PROJ_BENF_PTYPE',
1067 FPA_VALIDATION_PVT.G_WARNING,
1068 p_project_id, 'PROJECT');
1069 end if;
1070
1071 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
1072
1073 FPA_UTILITIES_PVT.END_ACTIVITY(
1074 p_api_name => l_api_name,
1075 p_pkg_name => G_PKG_NAME,
1076 p_msg_log => null,
1077 x_msg_count => x_msg_count,
1078 x_msg_data => x_msg_data);
1079
1080
1081 EXCEPTION
1082 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
1083 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1084 p_api_name => l_api_name,
1085 p_pkg_name => G_PKG_NAME,
1086 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
1087 p_msg_log => l_msg_log,
1088 x_msg_count => x_msg_count,
1089 x_msg_data => x_msg_data,
1090 p_api_type => G_API_TYPE);
1091
1092 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
1093 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1094 p_api_name => l_api_name,
1095 p_pkg_name => G_PKG_NAME,
1096 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
1097 p_msg_log => l_msg_log,
1098 x_msg_count => x_msg_count,
1099 x_msg_data => x_msg_data,
1100 p_api_type => G_API_TYPE);
1101
1102 when OTHERS then
1103 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1104 p_api_name => l_api_name,
1105 p_pkg_name => G_PKG_NAME,
1106 p_exc_name => 'OTHERS',
1107 p_msg_log => l_msg_log||SQLERRM,
1108 x_msg_count => x_msg_count,
1109 x_msg_data => x_msg_data,
1110 p_api_type => G_API_TYPE);
1111
1112 END Validate_Proj_Refresh_Plans;
1113
1114
1115 PROCEDURE Validate_Project_Refresh
1116 (
1117 p_api_version IN NUMBER,
1118 p_init_msg_list IN VARCHAR2,
1119 p_validation_set IN VARCHAR2,
1120 p_scenario_id IN NUMBER,
1121 p_projects_tbl IN PROJECT_ID_TBL_TYPE,
1122 x_return_status OUT NOCOPY VARCHAR2,
1123 x_msg_count OUT NOCOPY NUMBER,
1124 x_msg_data OUT NOCOPY VARCHAR2
1125 ) IS
1126
1127 -- standard parameters
1128 l_return_status VARCHAR2(1);
1129 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Project_Refresh';
1130 l_api_version CONSTANT NUMBER := 1.0;
1131 l_msg_log VARCHAR2(2000) := null;
1132 ----------------------------------------------------------------------------
1133 -- l_org_id NUMBER;
1134 l_level_error BOOLEAN := FALSE;
1135
1136
1137 l_portfolio_id FPA_AW_PORTF_HEADERS_V.PORTFOLIO%TYPE := null;
1138 l_current_pc_id FPA_AW_PC_INFO_V.PLANNING_CYCLE%TYPE := null;
1139 l_class_code_id NUMBER;
1140 l_pc_id NUMBER;
1141 l_valid_project VARCHAR2(1) := FND_API.G_FALSE;
1142 l_validation BOOLEAN := FALSE;
1143 i NUMBER;
1144
1145
1146 BEGIN
1147 l_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
1148
1149 SELECT PLANNING_CYCLE INTO L_PC_ID
1150 FROM FPA_AW_SCES_V WHERE SCENARIO = P_SCENARIO_ID;
1151
1152 FOR i IN p_projects_tbl.first .. p_projects_tbl.last
1153 LOOP
1154
1155 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1156 FND_LOG.STRING
1157 (
1158 FND_LOG.LEVEL_PROCEDURE,
1159 'fpa.sql.FPA_VALIDATION_PROCESS_PVT.Validate_Project_Refresh',
1160 'processing project = '||p_projects_tbl(i)
1161 );
1162 END IF;
1163
1164 Validate_Proj_Refresh_Plans(
1165 p_project_id => p_projects_tbl(i),
1166 x_return_status => x_return_status,
1167 x_msg_count => x_msg_count,
1168 x_msg_data => x_msg_data);
1169
1170
1171 END LOOP;
1172
1173 FPA_UTILITIES_PVT.END_ACTIVITY(
1174 p_api_name => l_api_name,
1175 p_pkg_name => G_PKG_NAME,
1176 p_msg_log => null,
1177 x_msg_count => x_msg_count,
1178 x_msg_data => x_msg_data);
1179
1180
1181 EXCEPTION
1182 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
1183 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1184 p_api_name => l_api_name,
1185 p_pkg_name => G_PKG_NAME,
1186 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
1187 p_msg_log => l_msg_log,
1188 x_msg_count => x_msg_count,
1189 x_msg_data => x_msg_data,
1190 p_api_type => G_API_TYPE);
1191
1192 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
1193 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1194 p_api_name => l_api_name,
1195 p_pkg_name => G_PKG_NAME,
1196 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
1197 p_msg_log => l_msg_log,
1198 x_msg_count => x_msg_count,
1199 x_msg_data => x_msg_data,
1200 p_api_type => G_API_TYPE);
1201
1202 when OTHERS then
1203 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1204 p_api_name => l_api_name,
1205 p_pkg_name => G_PKG_NAME,
1206 p_exc_name => 'OTHERS',
1207 p_msg_log => l_msg_log||SQLERRM,
1208 x_msg_count => x_msg_count,
1209 x_msg_data => x_msg_data,
1210 p_api_type => G_API_TYPE);
1211
1212 END Validate_Project_Refresh;
1213
1214 PROCEDURE Validate
1215 (
1216 p_api_version IN NUMBER,
1217 p_init_msg_list IN VARCHAR2,
1218 p_validation_set IN VARCHAR2,
1219 p_header_object_id IN NUMBER,
1220 p_header_object_type IN VARCHAR2,
1221 p_line_projects_tbl IN PROJECT_ID_TBL_TYPE,
1222 x_return_status OUT NOCOPY VARCHAR2,
1223 x_msg_count OUT NOCOPY NUMBER,
1224 x_msg_data OUT NOCOPY VARCHAR2
1225 ) IS
1226
1227 -- standard parameters
1228 l_return_status VARCHAR2(1);
1229 l_api_name CONSTANT VARCHAR2(30) := 'Validate';
1230 l_api_version CONSTANT NUMBER := 1.0;
1231 l_msg_log VARCHAR2(2000) := null;
1232 ----------------------------------------------------------------------------
1233
1234 BEGIN
1235
1236 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
1237
1238 x_return_status := FPA_UTILITIES_PVT.START_ACTIVITY(
1239 p_api_name => l_api_name,
1240 p_pkg_name => G_PKG_NAME,
1241 p_init_msg_list => p_init_msg_list,
1242 l_api_version => l_api_version,
1243 p_api_version => p_api_version,
1244 p_api_type => G_API_TYPE,
1245 p_msg_log => 'Entering Fpa_Validation_Process_Pvt.Validate',
1246 x_return_status => x_return_status);
1247
1248 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
1249 l_msg_log := 'start_activity';
1250 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
1251 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
1252 l_msg_log := 'start_activity';
1253 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
1254 end if;
1255
1256 if(p_header_object_type = 'PLANNING_CYCLE') then
1257
1258 Validate_Collect_Projects(
1259 p_api_version => p_api_version,
1260 p_init_msg_list => p_init_msg_list,
1261 p_validation_set => p_validation_set,
1262 p_header_object_id => p_header_object_id,
1263 x_return_status => x_return_status,
1264 x_msg_count => x_msg_count,
1265 x_msg_data => x_msg_data);
1266
1267 elsif (p_header_object_type = 'SCENARIO') then
1268
1269 Validate_Project_Refresh(
1270 p_api_version => p_api_version,
1271 p_init_msg_list => p_init_msg_list,
1272 p_validation_set => p_validation_set,
1273 p_scenario_id => p_header_object_id,
1274 p_projects_tbl => p_line_projects_tbl,
1275 x_return_status => x_return_status,
1276 x_msg_count => x_msg_count,
1277 x_msg_data => x_msg_data);
1278
1279 end if;
1280
1281 if (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR) then
1282 l_msg_log := 'Validate_Collect_Projects';
1283 raise FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR;
1284 elsif (x_return_status = FPA_UTILITIES_PVT.G_RET_STS_ERROR) then
1285 l_msg_log := 'Validate_Collect_Projects';
1286 raise FPA_UTILITIES_PVT.G_EXCEPTION_ERROR;
1287 end if;
1288
1289 x_return_status := FPA_UTILITIES_PVT.G_RET_STS_SUCCESS;
1290
1291 FPA_UTILITIES_PVT.END_ACTIVITY(
1292 p_api_name => l_api_name,
1293 p_pkg_name => G_PKG_NAME,
1294 p_msg_log => null,
1295 x_msg_count => x_msg_count,
1296 x_msg_data => x_msg_data);
1297
1298
1299 EXCEPTION
1300 when FPA_UTILITIES_PVT.G_EXCEPTION_ERROR then
1301 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1302 p_api_name => l_api_name,
1303 p_pkg_name => G_PKG_NAME,
1304 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_ERROR',
1305 p_msg_log => l_msg_log,
1306 x_msg_count => x_msg_count,
1307 x_msg_data => x_msg_data,
1308 p_api_type => G_API_TYPE);
1309
1310 when FPA_UTILITIES_PVT.G_EXCEPTION_UNEXPECTED_ERROR then
1311 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1312 p_api_name => l_api_name,
1313 p_pkg_name => G_PKG_NAME,
1314 p_exc_name => 'FPA_UTILITIES_PVT.G_RET_STS_UNEXP_ERROR',
1315 p_msg_log => l_msg_log,
1316 x_msg_count => x_msg_count,
1317 x_msg_data => x_msg_data,
1318 p_api_type => G_API_TYPE);
1319
1320 when OTHERS then
1321 x_return_status := FPA_UTILITIES_PVT.HANDLE_EXCEPTIONS(
1322 p_api_name => l_api_name,
1323 p_pkg_name => G_PKG_NAME,
1324 p_exc_name => 'OTHERS',
1325 p_msg_log => l_msg_log||SQLERRM,
1326 x_msg_count => x_msg_count,
1327 x_msg_data => x_msg_data,
1328 p_api_type => G_API_TYPE);
1329
1330 END Validate;
1331
1332
1333 FUNCTION Object_Name(
1334 p_object_id IN NUMBER,
1335 p_object_type IN VARCHAR2) RETURN VARCHAR2 IS
1336
1337 l_object_name VARCHAR2(200);
1338
1339 BEGIN
1340
1341 if(p_object_type = 'PROJECT') then
1342 SELECT NAME INTO L_OBJECT_NAME
1343 FROM PA_PROJECTS_ALL WHERE PROJECT_ID = P_OBJECT_ID;
1344 else
1345 SELECT NAME INTO L_OBJECT_NAME
1346 FROM FPA_OBJECTS_TL WHERE ID = P_OBJECT_ID
1347 AND LANGUAGE = USERENV('LANG'); -- Bug Ref # 6327682;
1348 end if;
1349
1350 return l_object_name;
1351
1352 EXCEPTION
1353 WHEN OTHERS THEN
1354 if (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
1355 FND_LOG.String(
1356 FND_LOG.LEVEL_PROCEDURE,
1357 'fpa.sql.FPA_VALIDATIONS_PROCESS_PVT.Object_Name',
1358 'EXCEPTION:'||sqlerrm||p_object_id||','||p_object_type);
1359 end if;
1360 return null;
1361 END Object_Name;
1362
1363
1364
1365 END FPA_VALIDATION_PROCESS_PVT;