[Home] [Help]
PACKAGE BODY: APPS.PA_IMPL_OPTIONS_PUB
Source
1 PACKAGE BODY PA_IMPL_OPTIONS_PUB AS
2 /* $Header: PAIMWRPB.pls 120.2 2006/03/17 01:06:37 dthakker noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_IMPL_OPTIONS_PUB';
5
6 -- API name : Check_Incl_rlzd_gain_loss
7 -- Type : PL/SQL Public function
8 -- Pre-reqs : None
9 -- Return Value : VC2
10 -- Prameters
11 --
12 -- p_org_id IN NUMBER
13 --
14 -- History
15 --
16 -- 16-AUG-02 MAansari -Created
17 --
18 --
19
20 FUNCTION Check_Incl_rlzd_gain_loss(
21 p_org_id IN NUMBER
22 ) RETURN VARCHAR2 IS
23
24 l_return_flag VARCHAR2(1);
25 BEGIN
26
27 l_return_flag := PA_FUND_REVAL_UTIL.Valid_Include_gains_losses( p_org_id );
28
29 RETURN( NVL( l_return_flag, 'N' ) );
30
31 END Check_Incl_rlzd_gain_loss;
32
33 -- API name : Check_Incl_rlzd_gain_loss
34 -- Type : PL/SQL Public procedure
35 -- Pre-reqs : None
36 -- Return Value : N/A
37 -- Prameters
38 --
39 -- x_return_status OUT VARCHAR2
40 --
41 -- History
42 --
43 -- 16-AUG-02 MAansari -Created
44 --
45 --
46
47 PROCEDURE Upgrade_MRC_fund_bud_flag(
48 x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
49 ) IS
50 BEGIN
51 SAVEPOINT upgrade_ou;
52 --Clear Error Messages.
53 FND_MSG_PUB.initialize;
54
55 UPDATE pa_implementations
56 SET ENABLE_MRC_FOR_FUND_FLAG = 'U';
57
58 x_return_status := FND_API.G_RET_STS_SUCCESS;
59 EXCEPTION
60 WHEN OTHERS THEN
61 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
62 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_IMPL_OPTIONS_PUB',
63 p_procedure_name => 'Upgrade_MRC_fund_bud_flag',
64 p_error_text => SUBSTRB(SQLERRM,1,240));
65 rollback to upgrade_ou;
66 raise;
67 END Upgrade_MRC_fund_bud_flag;
68
69 FUNCTION Check_MRC_install(
70 x_err_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
71 ) RETURN VARCHAR2 IS
72 l_return_value VARCHAR2(1);
73 BEGIN
74 IF PA_MC_FUNDINGS_PKG.check_mrc_install( x_err_code )
75 --IF true
76 THEN
77 l_return_value := 'Y';
78 ELSE
79 l_return_value := 'N';
80 END IF;
81 RETURN ( l_return_value );
82 END Check_MRC_install;
83
84 FUNCTION Check_OU_Incl_gain_loss(
85 p_org_id IN NUMBER
86 ) RETURN VARCHAR2 IS
87
88 l_return_flag VARCHAR2(1);
89 BEGIN
90
91 l_return_flag := PA_FUND_REVAL_UTIL.Is_OU_Include_Gains_Losses( p_org_id );
92
93 RETURN( NVL( l_return_flag, 'N' ) );
94
95 END Check_OU_Incl_gain_loss;
96
97 FUNCTION Check_proj_Incl_gain_loss(
98 p_org_id IN NUMBER,
99 p_project_type IN VARCHAR2
100 ) RETURN VARCHAR2 IS
101 CURSOR cur_pa_proj
102 IS
103 SELECT include_gains_losses_flag
104 FROM pa_projects_all
105 WHERE org_id = p_org_id -- 5078716 , Removed nvl condition
106 AND project_type = p_project_type
107 AND include_gains_losses_flag = 'Y';
108
109 l_return_flag VARCHAR2(1);
110 BEGIN
111 OPEN cur_pa_proj;
112 FETCH cur_pa_proj INTO l_return_flag;
113 CLOSE cur_pa_proj;
114 RETURN NVL( l_return_flag, 'N' );
115 END Check_proj_Incl_gain_loss;
116
117 FUNCTION Is_PT_Include_Gains_Losses(
118 p_org_id IN NUMBER,
119 p_project_type IN VARCHAR2
120 ) RETURN VARCHAR2 IS
121 l_return_flag VARCHAR2(1);
122 BEGIN
123 l_return_flag := PA_FUND_REVAL_UTIL.Is_PT_Include_Gains_Losses( p_org_id, p_project_type );
124 RETURN NVL( l_return_flag, 'N' );
125 END Is_PT_Include_Gains_Losses;
126
127
128 FUNCTION option_updateable(
129 p_project_id IN NUMBER
130 ,p_option_code VARCHAR2
131 ) RETURN VARCHAR2 IS
132 l_return_code VARCHAR2(30) := 'Y';
133 l_structure_type VARCHAR2(30) := 'DELIVERABLE' ; /* Included by avaithia Bug 3476115*/
134 l_error_code VARCHAR2(2000) := 'S' ;
135 BEGIN
136
137 IF p_option_code in( 'WORKPLAN_OPTIONS', 'WORKPLAN_OPTIONS_SS', 'TASK_PROGRESS',
138 'WORKPLAN_STRUCTURE', 'WP_TASK_DET', 'PLANNING_OPTIONS_WP',
139 'CURRENCY_SETTINGS_WP', 'RATE_SCHEDULES_WP' )
140 THEN
141 IF PA_PROJ_TASK_STRUC_PUB.WP_STR_EXISTS( p_project_id ) = 'N'
142 THEN
143 l_return_code := 'PA_SETUP_WRKPLN_OPT_ERR';
144 ELSE
145 l_return_code := 'Y';
146 IF p_option_code = 'WP_TASK_DET'
147 THEN
148 IF PA_PROJ_TASK_STRUC_PUB.IS_WP_SEPARATE_FROM_FN( p_project_id ) = 'Y'
149 THEN
150 l_return_code := 'PA_SETUP_WRKPLN_OPT_ERR2'; --new message ios created for bug 2609565
151 END IF;
152 END IF;
153 END IF;
154 -- bug no.3589818 sdnambia start
155 -- bug no. 3683346 sdnambia
156 --Bug Number :4281752 - Included FINANCIAL_OPTIONS_SS in the following list
157 ELSIF p_option_code in ('FBS_SS', 'BUDGETS_AND_FORECASTS', 'PLANNING_OPTIONS', 'CURRENCY_SETTINGS', 'RATE_SCHEDULES','FINANCIAL_OPTIONS_SS')
158 THEN
159 IF PA_PROJECT_STRUCTURE_UTILS.check_financial_enabled(p_project_id) = 'N'
160 THEN
161 l_return_code := 'PA_SETUP_FINPLN_OPT_ERR';
162 ELSE
163 l_return_code := 'Y';
164 END IF;
165 -- bug no.3589818 sdnambia end
166 ELSE /*Included by avaithia Bug 3476115*/
167 IF p_option_code = 'DELIVERABLES_SS' THEN
168 PA_PROJECT_STRUCTURE_UTILS.Check_Structure_Type_Exists
169 (
170 p_project_id => p_project_id
171 ,p_structure_type => l_structure_type
172 ,x_return_status => l_return_code
173 ,x_error_message_code => l_error_code
174 );
175 IF l_return_code <> 'E' THEN /*the API is coded so that it returns "E" when the structure type exists and "S" if not*/
176 l_return_code := 'PA_PS_DELIVERABLE_DISABLED' ;
177 ELSE
178 l_return_code := 'Y';
179 END IF;
180
181 END IF;
182 END IF;
183 Return l_return_code;
184 END option_updateable;
185
186 FUNCTION check_budget_trans_exists(
187 p_project_id IN NUMBER
188 ) RETURN VARCHAR2 IS
189 l_return_flag VARCHAR2(1);
190 BEGIN
191 l_return_flag := pa_fin_plan_utils.check_budget_trans_exists( p_project_id );
192 RETURN NVL( l_return_flag, 'N' );
193 END check_budget_trans_exists;
194
195 FUNCTION enable_auto_baseline(
196 p_project_id IN NUMBER
197 ) RETURN VARCHAR2 IS
198 l_return_flag VARCHAR2(1);
199 BEGIN
200 l_return_flag := pa_fin_plan_utils.enable_auto_baseline( p_project_id );
201 RETURN NVL( l_return_flag, 'N' );
202 END enable_auto_baseline;
203
204 -- -----------------------------------------------------
205 -- procedure Update_Access_level
206 --
207 -- This procedure is invoked when concurrent program
208 -- PRC: Update Project Access Level is run
209 -- -----------------------------------------------------
210 procedure Update_Access_level
211 ( errbuf out NOCOPY varchar2, --File.Sql.39 bug 4440895
212 retcode out NOCOPY varchar2, --File.Sql.39 bug 4440895
213 p_from_project_number in varchar2 default null,
214 p_to_project_number in varchar2 default null,
215 p_project_status in varchar2 default null,
216 p_project_type in varchar2 default null,
217 p_project_organization in number default null,
218 p_access_level in varchar2 default '1'
219 ) is
220
221 cursor c_projects is
222 select project_id
223 from pa_projects_all
224 where segment1 >= nvl(p_from_project_number, segment1)
225 and segment1 <= nvl(p_to_project_number, segment1)
226 and project_status_code = nvl(p_project_status, project_status_code)
227 and project_type = nvl(p_project_type, project_type)
228 and carrying_out_organization_id = nvl(p_project_organization,carrying_out_organization_id);
229
230 l_commit_size number := 5000;
231 l_loop_count number := 0;
232 l_last_update_date date;
233 l_last_updated_by number;
234 l_last_update_login number;
235
236 begin
237
238 l_last_update_date := sysdate;
239 l_last_updated_by := fnd_global.user_id;
240 l_last_update_login := fnd_global.user_id;
241
242 IF p_access_level is not null THEN
243
244 for rec in c_projects loop
245
246 update pa_projects_all
247 set security_level = to_number( p_access_level),
248 last_update_date = l_last_update_date,
249 last_updated_by = l_last_updated_by,
250 last_update_login = l_last_update_login
251 where project_id = rec.project_id;
252
253 l_loop_count := l_loop_count + 1;
254
255 if l_loop_count = l_commit_size then
256 commit;
257 l_loop_count := 0;
258 end if;
259
260 end loop;
261
262 END IF;
263
264 retcode := 0;
265
266 exception
267 when others then
268
269 retcode := 2;
270 errbuf := sqlerrm;
271 rollback;
272 raise;
273
274 end Update_Access_Level;
275
276 procedure COPY_ASSET(
277 p_cur_project_asset_id IN NUMBER,
278 p_asset_name IN VARCHAR2,
279 p_asset_description IN VARCHAR2,
280 p_project_asset_type IN VARCHAR2,
281 p_asset_units IN NUMBER DEFAULT NULL,
282 p_est_asset_units IN NUMBER DEFAULT NULL,
283 p_asset_dpis IN DATE DEFAULT NULL,
284 p_est_asset_dpis IN DATE DEFAULT NULL,
285 p_asset_number IN VARCHAR2 DEFAULT NULL,
286 p_copy_assignments IN VARCHAR2,
287 x_new_project_asset_id OUT NOCOPY NUMBER,
288 x_return_status OUT NOCOPY VARCHAR2,
289 x_msg_data OUT NOCOPY VARCHAR2
290 ) IS
291
292 begin
293
294 PA_COPY_ASSET_PVT.COPY_ASSET(
295 p_cur_project_asset_id =>p_cur_project_asset_id,
296 p_asset_name =>p_asset_name,
297 p_asset_description =>p_asset_description,
298 p_project_asset_type =>p_project_asset_type,
299 p_asset_units =>p_asset_units,
300 p_est_asset_units =>p_est_asset_units,
301 p_asset_dpis =>p_asset_dpis,
302 p_est_asset_dpis =>p_est_asset_dpis,
303 p_asset_number =>p_asset_number,
304 p_copy_assignments =>p_copy_assignments,
305 x_new_project_asset_id => x_new_project_asset_id,
306 x_return_status => x_return_status,
307 x_msg_data => x_msg_data);
308
309 end COPY_ASSET;
310
311 FUNCTION Tag_Number_Exists( P_Tag_Number IN VARCHAR2) RETURN VARCHAR2 IS
312
313 l_return_value VARCHAR2(1) := 'N';
314
315 BEGIN
316 l_return_value := PA_CAPITAL_PROJECT_UTILS.Tag_Number_Exists ( p_tag_number );
317 RETURN ( l_return_value );
318 END Tag_Number_Exists;
319
320 FUNCTION Allow_AssetType_Change(P_From_Asset_Type IN VARCHAR2,
321 P_To_Asset_Type IN VARCHAR2,
322 P_Project_Asset_Id IN NUMBER,
323 P_Capitalized_Flag IN VARCHAR2,
324 P_Capital_Event_Id IN NUMBER
325 ) RETURN VARCHAR2 IS
326
327 l_return_value VARCHAR2(1) := 'N';
328
329 BEGIN
330 l_return_value := PA_CAPITAL_PROJECT_UTILS.Allow_AssetType_Change(
331 P_From_Asset_Type => P_From_Asset_Type
332 ,P_To_Asset_Type => P_To_Asset_Type
333 ,P_Project_Asset_Id => P_Project_Asset_Id
334 ,P_Capitalized_Flag => P_Capitalized_Flag
335 ,P_Capital_Event_Id => P_Capital_Event_Id
336 );
337
338 RETURN ( l_return_value );
339 END Allow_AssetType_Change;
340
341 /*FUNCTION get_depreciation_expense
342 (P_project_asset_id IN NUMBER,
343 P_book_type_code IN VARCHAR2,
344 P_asset_category_id IN NUMBER,
345 P_date_placed_in_service IN DATE
346 ) RETURN NUMBER IS
347
348 l_return_value NUMBER;
349
350 BEGIN
351
352 l_return_value := PA_CAPITAL_PROJECT_UTILS.get_depreciation_expense(
353 p_project_asset_id => p_project_asset_id
354 ,p_book_type_code => p_book_type_code
355 ,p_asset_category_id => p_asset_category_id
356 ,p_date_placed_in_service => p_date_placed_in_service
357
358 RETURN ( l_return_value );
359 END get_depreciation_expense;
360 */
361
362 PROCEDURE CHECK_CUST_FUNDING_EXISTS(
363 p_proj_customer_id NUMBER,
364 p_project_id NUMBER,
365 p_cust_contribution NUMBER,
366 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
367 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
368 x_msg_count OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
369 ) IS
370 BEGIN
371 PA_MULTI_CURRENCY_BILLING.Check_Cust_Funding_Exists(
372 p_proj_customer_id => p_proj_customer_id,
373 p_project_id => p_project_id,
374 p_cust_contribution => p_cust_contribution,
375 x_return_status => x_return_status,
376 x_msg_data => x_msg_data,
377 x_msg_count => x_msg_count
378 );
379
380 END CHECK_CUST_FUNDING_EXISTS;
381
382
383 FUNCTION is_ord_mgmt_installed RETURN VARCHAR2 --for credit receiver feature
384 IS
385 l_return_value VARCHAR2(1) := 'N';
386 BEGIN
387
388 l_return_value := PA_INSTALL.is_ord_mgmt_installed;
389 RETURN ( l_return_value );
390 END is_ord_mgmt_installed;
391
392
393 PROCEDURE check_asset_alloc_method (
394 p_asset_allocation_method VARCHAR2
395 ,p_amg_segment1 VARCHAR2
396 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
397 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
398 ,x_msg_count OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
399 ) IS
400 CURSOR cur_lookup IS
401 SELECT 'x'
402 FROM pa_lookups
403 WHERE lookup_type = 'ASSET_ALLOCATION_METHOD'
404 AND lookup_code = p_asset_allocation_method;
405 l_dummy_char VARCHAR2(1);
406 l_api_name CONSTANT VARCHAR2(30) := 'check_asset_alloc_method';
407 BEGIN
408
409 x_return_status := FND_API.G_RET_STS_SUCCESS;
410
411 OPEN cur_lookup;
412 FETCH cur_lookup INTO l_dummy_char;
413 IF cur_lookup%NOTFOUND
414 THEN
415 CLOSE cur_lookup;
416 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
417 THEN
418 pa_interface_utils_pub.map_new_amg_msg
419 ( p_old_message_code => 'PA_CAP_INV_ASSET_ALLOC'
420 ,p_msg_attribute => 'CHANGE'
421 ,p_resize_flag => 'N'
422 ,p_msg_context => 'PROJECT'
423 ,p_attribute1 => p_amg_segment1
424 ,p_attribute2 => ''
425 ,p_attribute3 => ''
426 ,p_attribute4 => ''
427 ,p_attribute5 => '');
428 END IF;
429 RAISE FND_API.G_EXC_ERROR;
430 ELSE
431 CLOSE cur_lookup;
432 END IF;
433
434 EXCEPTION
435
436 WHEN FND_API.G_EXC_ERROR
437 THEN
438 x_return_status := FND_API.G_RET_STS_ERROR;
439
440 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
441 THEN
442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
443
444 WHEN OTHERS THEN
445 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
446
447 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
448 THEN
449 FND_MSG_PUB.add_exc_msg
450 ( p_pkg_name => G_PKG_NAME
451 , p_procedure_name => l_api_name );
452
453 END IF;
454
455 END check_asset_alloc_method;
456
457 PROCEDURE check_cap_event_method (
458 p_capital_event_processing VARCHAR2
459 ,p_amg_segment1 VARCHAR2
460 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
461 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
462 ,x_msg_count OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
463 ) IS
464 CURSOR cur_lookup IS
465 SELECT 'x'
466 FROM pa_lookups
467 WHERE lookup_type = 'CAPITAL_EVENT_PROCESSING'
468 AND lookup_code = p_capital_event_processing;
469 l_dummy_char VARCHAR2(1);
470 l_api_name CONSTANT VARCHAR2(30) := 'check_cap_event_method';
471 BEGIN
472
473 x_return_status := FND_API.G_RET_STS_SUCCESS;
474
475 OPEN cur_lookup;
476 FETCH cur_lookup INTO l_dummy_char;
477 IF cur_lookup%NOTFOUND
478 THEN
479 CLOSE cur_lookup;
480 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
481 THEN
482 pa_interface_utils_pub.map_new_amg_msg
483 ( p_old_message_code => 'PA_CAP_INV_CAP_EVENT'
484 ,p_msg_attribute => 'CHANGE'
485 ,p_resize_flag => 'N'
486 ,p_msg_context => 'PROJECT'
487 ,p_attribute1 => p_amg_segment1
488 ,p_attribute2 => ''
489 ,p_attribute3 => ''
490 ,p_attribute4 => ''
491 ,p_attribute5 => '');
492 END IF;
493 RAISE FND_API.G_EXC_ERROR;
494 ELSE
495 CLOSE cur_lookup;
496 END IF;
497
498 EXCEPTION
499
500 WHEN FND_API.G_EXC_ERROR
501 THEN
502 x_return_status := FND_API.G_RET_STS_ERROR;
503
504 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
505 THEN
506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
507
508 WHEN OTHERS THEN
509 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
510
511 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
512 THEN
513 FND_MSG_PUB.add_exc_msg
514 ( p_pkg_name => G_PKG_NAME
515 , p_procedure_name => l_api_name );
516
517 END IF;
518
519 END check_cap_event_method;
520
521 PROCEDURE check_cint_schedule (
522 p_cint_rate_sch_id NUMBER
523 ,p_amg_segment1 VARCHAR2
524 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
525 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
526 ,x_msg_count OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
527 ) IS
528 CURSOR cur_sch IS
529 SELECT 'X'
530 FROM pa_ind_rate_schedules_all_bg
531 WHERE IND_RATE_SCH_USAGE = 'CAPITALIZED_INTEREST'
532 AND trunc(sysdate) between START_DATE_ACTIVE
533 AND nvl(trunc(END_DATE_ACTIVE),trunc(sysdate))
534 AND IND_RATE_SCH_ID = p_cint_rate_sch_id ;
535
536 l_dummy_char VARCHAR2(1);
537 l_api_name CONSTANT VARCHAR2(30) := 'check_cint_schedule';
538 BEGIN
539
540 x_return_status := FND_API.G_RET_STS_SUCCESS;
541
542 OPEN cur_sch;
543 FETCH cur_sch INTO l_dummy_char;
544 IF cur_sch%NOTFOUND
545 THEN
546 CLOSE cur_sch;
547 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
548 THEN
549 pa_interface_utils_pub.map_new_amg_msg
550 ( p_old_message_code => 'PA_CAP_INV_CINT_SCH'
551 ,p_msg_attribute => 'CHANGE'
552 ,p_resize_flag => 'N'
553 ,p_msg_context => 'PROJECT'
554 ,p_attribute1 => p_amg_segment1
555 ,p_attribute2 => ''
556 ,p_attribute3 => ''
557 ,p_attribute4 => ''
558 ,p_attribute5 => '');
559 END IF;
560 RAISE FND_API.G_EXC_ERROR;
561 ELSE
562 CLOSE cur_sch;
563 END IF;
564
565 EXCEPTION
566
567 WHEN FND_API.G_EXC_ERROR
568 THEN
569 x_return_status := FND_API.G_RET_STS_ERROR;
570
571 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
572 THEN
573 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
574
575 WHEN OTHERS THEN
576 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
577
578 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
579 THEN
580 FND_MSG_PUB.add_exc_msg
581 ( p_pkg_name => G_PKG_NAME
582 , p_procedure_name => l_api_name );
583
584 END IF;
585
586 END check_cint_schedule;
587
588 --PA L Changes 2872708
589
590
591 END PA_IMPL_OPTIONS_PUB;