[Home] [Help]
PACKAGE BODY: APPS.PA_PROJ_TEMPLATE_SETUP_PVT
Source
1 PACKAGE BODY PA_PROJ_TEMPLATE_SETUP_PVT AS
2 /* $Header: PATMSTVB.pls 120.4.12010000.2 2008/10/27 17:03:36 atshukla ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_PROJ_TEMPLATE_SETUP_PVT';
5
6 -- API name : Create_Project_Template
7 -- Type : Public API
8 -- Pre-reqs : None
9 -- Return Value :
10 --
11 -- Parameters
12 --p_project_number IN VARCHAR2
13 --p_project_name IN VARCHAR2
14 --p_project_type IN VARCHAR2
15 --p_organization_id IN NUMBER
16 --p_organization_name IN VARCHAR2
17 --p_effective_from_date IN DATE
18 --p_effective_to_date IN DATE
19 --p_description IN VARCHAR2
20
21 PROCEDURE Create_Project_Template(
22 p_api_version IN NUMBER :=1.0,
23 p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE,
24 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
25 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE,
26 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
27 p_calling_module IN VARCHAR2 :='SELF_SERVICE',
28 p_debug_mode IN VARCHAR2 :='N',
29 p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
30 p_project_number IN VARCHAR2,
31 p_project_name IN VARCHAR2,
32 p_project_type IN VARCHAR2,
33 p_organization_id IN NUMBER := -9999,
34 p_effective_from_date IN DATE := TO_DATE( '01-01-1000', 'DD-MM-YYYY' ),
35 p_effective_to_date IN DATE := TO_DATE( '01-01-1000', 'DD-MM-YYYY' ),
36 p_description IN VARCHAR2 := 'JUNK_CHARS',
37 p_security_level IN NUMBER := 0,
38 -- anlee
39 -- Project Long Name changes
40 p_long_name IN VARCHAR2 DEFAULT NULL,
41 -- End of changes
42 p_operating_unit_id IN NUMBER, -- 4363092 MOAC changes
43 x_template_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
44 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
45 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
46 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
47 ) IS
48 l_api_name CONSTANT VARCHAR(30) := 'Create_Project_Template';
49 l_api_version CONSTANT NUMBER := 1.0;
50 l_return_status VARCHAR2(1);
51 l_msg_count NUMBER;
52 l_msg_data VARCHAR2(250);
53 l_data VARCHAR2(250);
54 l_msg_index_out NUMBER;
55 l_error_msg_code VARCHAR2(250);
56 l_error_message_code VARCHAR2(250);
57
58 l_organization_id NUMBER;
59 l_template_id NUMBER;
60
61 l_Status_code VARCHAR2(80);
62 l_service_type_code VARCHAR2(80);
63 l_cost_ind_rate_sch_id NUMBER;
64 l_labor_sch_type VARCHAR2(80);
65 l_labor_bill_rate_org_id NUMBER;
66 l_labor_std_bill_rate_schdl VARCHAR2(80);
67 l_non_labor_sch_type VARCHAR2(80);
68 l_non_labor_bill_rate_org_id NUMBER;
69 l_nl_std_bill_rate_schdl VARCHAR2(80);
70 l_rev_ind_rate_sch_id NUMBER;
71 l_inv_ind_rate_sch_id NUMBER;
72 l_labor_invoice_format_id NUMBER;
73 l_non_labor_invoice_format_id NUMBER;
74 l_Burden_cost_flag VARCHAR2(80);
75 l_interface_asset_cost_code VARCHAR2(80);
76 l_cost_sch_override_flag VARCHAR2(80);
77 l_billing_offset NUMBER;
78 l_billing_cycle_id NUMBER;
79 l_cc_prvdr_flag VARCHAR2(80);
80 l_bill_job_group_id NUMBER;
81 l_cost_job_group_id NUMBER;
82 l_work_type_id NUMBER;
83 l_role_list_id NUMBER;
84 l_unassigned_time VARCHAR2(1);
85 l_emp_bill_rate_schedule_id NUMBER;
86 l_job_bill_rate_schedule_id NUMBER;
87 l_budgetary_override_flag VARCHAR2(80);
88 l_baseline_funding_flag VARCHAR2(80);
89 l_non_lab_std_bill_rt_sch_id NUMBER;
90 l_project_type_class_code VARCHAR2(80);
91 l_effective_from_date DATE;
92 l_effective_to_date DATE;
93
94 -- anlee
95 -- patchset K changes
96 l_revaluate_funding_flag VARCHAR2(1);
97 l_include_gains_losses_flag VARCHAR2(1);
98 -- End of changes
99
100 l_row_id VARCHAR2(18);
101 l_task_id NUMBER;
102 l_billable_flag VARCHAR2(1);
103
104 CURSOR Cur_proj_id
105 IS
106 SELECT pa_projects_s.nextval
107 FROM sys.dual;
108
109 CURSOR cur_currency
110 IS
111 SELECT FC.Currency_Code, imp.org_id, imp.exp_start_org_id, imp.exp_org_structure_version_id
112 FROM FND_CURRENCIES FC,
113 GL_SETS_OF_BOOKS GB,
114 PA_IMPLEMENTATIONS IMP
115 WHERE FC.Currency_Code = DECODE(IMP.Set_Of_Books_ID, Null,
116 Null,GB.CURRENCY_CODE)
117 AND GB.Set_Of_Books_ID = IMP.Set_Of_Books_ID;
118
119 l_cur_currency cur_currency%ROWTYPE;
120
121 --bug2319133
122 CURSOR cur_dist_rule
123 IS
124 /* SELECT r.distribution_rule
125 FROM pa_project_type_distributions d, pa_distribution_rules r
126 WHERE d.distribution_rule = r.distribution_rule
127 AND project_type = p_project_type
128 AND default_flag = 'Y';
129 */
130 --copied from project_folder1.project_type_mir1 when-validate-item validation trigger.
131
132 select distribution_rule
133 from pa_project_type_distributions
134 where project_type = p_project_type
135 and default_flag = 'Y';
136
137 l_distribution_rule VARCHAR2(20);
138 --bug2319133
139
140 cursor cur_impl
141 is
142 select adv_action_set_id, multi_currency_billing_flag, default_rate_type
143 ,retn_accounting_flag, competence_match_wt, availability_match_wt, job_level_match_wt
144 from pa_implementations;
145
146 l_adv_action_set_id NUMBER;
147 l_rate_date_code VARCHAR2(30);
148 l_rate_type VARCHAR2(30);
149 l_mcb_flag VARCHAR2(1);
150 l_retn_accounting_flag VARCHAR2(1);
151 l_competence_match_wt NUMBER;
152 l_availability_match_wt NUMBER;
153 l_job_level_match_wt NUMBER;
154 l_public_sector_flag VARCHAR2(1);
155 l_rate_type2 VARCHAR2(30);
156
157 l_location_id NUMBER;
158 x_rowid VARCHAR2(18);
159 l_city_name VARCHAR2(250);
160 l_country_code VARCHAR2(250);
161 l_country_name VARCHAR2(250);
162
163 /* Type of l_region_name has been changed to %TYPE from varchar2 for the UTF8 change */
164 l_region_name hr_locations_all.region_1%TYPE;
165
166 x_err_code Number := 0;
167 x_err_stage Varchar2(80);
168 x_err_stack Varchar2(630);
169
170 t_project_type_class_code VARCHAR2(30);
171 l_calendar_id NUMBER;
172 l_calendar_name VARCHAR2(250);
173
174 Cursor Get_def_Res_List is
175 Select Default_Resource_List_Id,
176 pa_resource_list_assignments_s.nextval
177 from pa_project_types
178 where project_type = p_project_type;
179
180 x_def_res_list_id Number := 0;
181 x_def_use_Code Varchar2(30) := 'ACTUALS_ACCUM';
182 x_def_flag Varchar2(1) := 'Y';
183 x_user_id Number := To_Number (FND_PROFILE.VALUE('USER_ID'));
184 x_login_id Number := To_Number (FND_PROFILE.VALUE('LOGIN_ID'));
185 x_rl_asgmt_id Number := 0;
186 l_BTC_COST_BASE_REV_CODE VARCHAR2(90); --bug 2755727
187
188 --PA L 2872708
189 l_asset_allocation_method VARCHAR2(30);
190 l_CAPITAL_EVENT_PROCESSING VARCHAR2(30);
191 l_CINT_RATE_SCH_ID NUMBER;
192 --PA L 2872708
193
194
195 l_warnings_only_flag VARCHAR2(1) := 'N'; --bug3134205
196
197
198 --sunkalya:federal Bug#5511353
199
200 l_date_eff_funds_flag VARCHAR2(1);
201
202 --sunkalya:federal Bug#5511353
203
204 l_ar_rec_notify_flag VARCHAR2(1) := 'N'; -- 7508661 : EnC
205 l_auto_release_pwp_inv VARCHAR2(1) := 'Y'; -- 7508661 : EnC
206
207 BEGIN
208 pa_debug.init_err_stack ('PA_PROJ_TEMPLATE_SETUP_PVT.Create_Project_Template');
209
210 IF (p_debug_mode = 'Y') THEN
211 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Create_Project_Template begin');
212 END IF;
213
214 IF (p_commit = FND_API.G_TRUE) THEN
215 savepoint Create_Project_Template;
216 END IF;
217
218 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
219 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
220 END IF;
221
222 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
223 FND_MSG_PUB.initialize;
224 END IF;
225
226 --Check for not null
227 PA_PROJ_TEMPLATE_SETUP_UTILS.Check_Template_attr_req(
228 p_project_number => p_project_number
229 ,p_project_name => p_project_name
230 ,p_project_type => p_project_type
231 ,p_organization_id => p_organization_id
232 ,x_return_status => l_return_status
233 ,x_error_msg_code => l_error_msg_code
234 );
235
236 IF l_return_status = FND_API.G_RET_STS_ERROR
237 THEN
238 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
239 p_msg_name => l_error_msg_code);
240 x_msg_data := l_error_msg_code;
241 x_return_status := 'E';
242 RAISE FND_API.G_EXC_ERROR;
243 END IF;
244
245 --check for project number uniqueness
246 IF pa_project_utils.check_unique_project_number (x_project_number => p_project_number
247 ,x_rowid => null ) = 0
248 THEN
249 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
250 p_msg_name => 'PA_SETUP_TMPL_NUM_NOT_UNIQUE' );
251 --x_msg_data := 'PA_PR_EPR_PROJ_NUM_NOT_UNIQUE';
252 x_msg_data := 'PA_SETUP_TMPL_NUM_NOT_UNIQUE';
253 x_return_status := 'E';
254 RAISE FND_API.G_EXC_ERROR;
255 END IF;
256
257 --check for project name uniqueness
258 IF pa_project_utils.check_unique_project_name (x_project_name => p_project_name
259 ,x_rowid => null ) = 0
260 THEN
261 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
262 p_msg_name => 'PA_SETUP_TMPL_NAME_NOT_UNIQUE' );
263 --x_msg_data := 'PA_PR_EPR_PROJ_NAME_NOT_UNIQUE';
264 x_msg_data := 'PA_SETUP_TMPL_NAME_NOT_UNIQUE';
265 x_return_status := 'E';
266 RAISE FND_API.G_EXC_ERROR;
267 END IF;
268
269 -- anlee
270 -- Project Long Name changes
271 -- check for long name uniqueness
272 IF pa_project_utils.check_unique_long_name (p_long_name, null ) = 0
273 THEN
274 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
275 p_msg_name => 'PA_PR_EPR_LONG_NAME_NOT_UNIQUE' );
276 --x_msg_data := 'PA_PR_EPR_LONG_NAME_NOT_UNIQUE';
277 x_msg_data := 'PA_PR_EPR_LONG_NAME_NOT_UNIQUE';
278 x_return_status := 'E';
279 RAISE FND_API.G_EXC_ERROR;
280 END IF;
281 -- End of changes
282
283 IF p_effective_from_date = TO_DATE( '01-01-1000', 'DD-MM-YYYY' )
284 THEN
285 l_effective_from_date := null;
286 ELSE
287 l_effective_from_date := p_effective_from_date;
288 END IF;
289
290 IF p_effective_to_date = TO_DATE( '01-01-1000', 'DD-MM-YYYY' )
291 THEN
292 l_effective_to_date := null;
293 ELSE
294 l_effective_to_date := p_effective_to_date;
295 END IF;
296
297 IF l_effective_from_date IS NOT NULL AND
298 l_effective_to_date IS NOT NULL
299 THEN
300 --Check the start and end dates
301 IF l_effective_from_date > l_effective_to_date
302 THEN
303 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
304 p_msg_name => 'PA_SETUP_CHK_ST_EN_DATE');
305 x_msg_data := 'PA_SETUP_CHK_ST_EN_DATE';
306 x_return_status := 'E';
307 RAISE FND_API.G_EXC_ERROR;
308 END IF;
309 ELSIF l_effective_from_date IS NULL AND
310 l_effective_to_date IS NOT NULL
311 THEN
312 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
313 p_msg_name => 'PA_SETUP_ST_DT_WO_EN_DT');
314 x_msg_data := 'PA_SETUP_ST_DT_WO_EN_DT';
315 x_return_status := 'E';
316 RAISE FND_API.G_EXC_ERROR;
317 END IF;
318
319 PA_PROJ_TEMPLATE_SETUP_UTILS.Get_Project_Type_Defaults(
320 p_project_type => p_project_type
321 ,x_Status_code => l_Status_code
322 ,x_service_type_code => l_service_type_code
323 ,x_cost_ind_rate_sch_id => l_cost_ind_rate_sch_id
324 ,x_labor_sch_type => l_labor_sch_type
325 ,x_labor_bill_rate_org_id => l_labor_bill_rate_org_id
326 ,x_labor_std_bill_rate_schdl => l_labor_std_bill_rate_schdl
327 ,x_non_labor_sch_type => l_non_labor_sch_type
328 ,x_non_labor_bill_rate_org_id => l_non_labor_bill_rate_org_id
329 ,x_nl_std_bill_rate_schdl => l_nl_std_bill_rate_schdl
330 ,x_rev_ind_rate_sch_id => l_rev_ind_rate_sch_id
331 ,x_inv_ind_rate_sch_id => l_inv_ind_rate_sch_id
332 ,x_labor_invoice_format_id => l_labor_invoice_format_id
333 ,x_non_labor_invoice_format_id => l_non_labor_invoice_format_id
334 ,x_Burden_cost_flag => l_Burden_cost_flag
335 ,x_interface_asset_cost_code => l_interface_asset_cost_code
336 ,x_cost_sch_override_flag => l_cost_sch_override_flag
337 ,x_billing_offset => l_billing_offset
338 ,x_billing_cycle_id => l_billing_cycle_id
339 ,x_cc_prvdr_flag => l_cc_prvdr_flag
340 ,x_bill_job_group_id => l_bill_job_group_id
341 ,x_cost_job_group_id => l_cost_job_group_id
342 ,x_work_type_id => l_work_type_id
343 ,x_role_list_id => l_role_list_id
344 ,x_unassigned_time => l_unassigned_time
345 ,x_emp_bill_rate_schedule_id => l_emp_bill_rate_schedule_id
346 ,x_job_bill_rate_schedule_id => l_job_bill_rate_schedule_id
347 ,x_budgetary_override_flag => l_budgetary_override_flag
348 ,x_baseline_funding_flag => l_baseline_funding_flag
349 ,x_non_lab_std_bill_rt_sch_id => l_non_lab_std_bill_rt_sch_id
350 ,x_project_type_class_code => l_project_type_class_code
351 -- anlee
352 -- Changes for patchset K
353 ,x_revaluate_funding_flag => l_revaluate_funding_flag
354 ,x_include_gains_losses_flag => l_include_gains_losses_flag
355 -- End of changes
356 --PA L Changes 2872708
357 ,x_asset_allocation_method => l_asset_allocation_method
358 ,x_CAPITAL_EVENT_PROCESSING => l_CAPITAL_EVENT_PROCESSING
359 ,x_CINT_RATE_SCH_ID => l_CINT_RATE_SCH_ID
360 --PA L Changes 2872708
361 --bug#5511353. Federal changes.
362 ,x_date_eff_funds_flag => l_date_eff_funds_flag
363 --bug#5511353. Federal changes.
364 ,x_ar_rec_notify_flag => l_ar_rec_notify_flag -- 7508661 : EnC
365 ,x_auto_release_pwp_inv => l_auto_release_pwp_inv -- 7508661 : EnC
366 ,x_return_status => l_return_status
367 ,x_error_msg_code => l_error_msg_code
368 );
369
370 IF l_return_status = FND_API.G_RET_STS_ERROR
371 THEN
372 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
373 p_msg_name => l_error_msg_code);
374 x_msg_data := l_error_msg_code;
375 x_return_status := 'E';
376 RAISE FND_API.G_EXC_ERROR;
377 END IF;
378
379 --Organization Location Validations
380 pa_location_utils.Get_ORG_Location_Details
381 (p_organization_id => p_organization_id,
382 x_country_name => l_country_name,
383 x_city => l_city_name,
384 x_region => l_region_name,
385 x_country_code => l_country_code,
386 x_return_status => l_return_status,
387 x_error_message_code => l_error_message_code);
388
389 IF l_return_status = FND_API.G_RET_STS_ERROR
390 THEN
391 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
392 p_msg_name => l_error_message_code);
393 x_msg_data := l_error_msg_code;
394 x_return_status := 'E';
395 RAISE FND_API.G_EXC_ERROR;
396 END IF;
397
398 pa_location_utils.check_location_exists(
399 p_country_code => l_country_code,
400 p_city => l_city_name,
401 p_region => l_region_name,
402 x_return_status => l_return_status,
403 x_location_id => l_location_id);
404
405 If l_location_id is null then
406
407 /* Commented the below line for bug 2688170 */
408 /* If l_city_name is not null
409 and l_region_name is not null */
410 If l_country_code is not null then
411
412 pa_locations_pkg.INSERT_ROW(
413 p_CITY => l_city_name,
414 p_REGION => l_region_name,
415 p_COUNTRY_CODE => l_country_code,
416 p_CREATION_DATE => SYSDATE,
417 p_CREATED_BY => FND_GLOBAL.USER_ID,
418 p_LAST_UPDATE_DATE => SYSDATE,
419 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
420 p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
421 X_ROWID => x_rowid,
422 X_LOCATION_ID => l_location_id);
423
424 end if;
425
426 end if;
427
428 pa_schedule_pub.GET_PROJ_CALENDAR_DEFAULT
429 ( p_proj_organization => p_organization_id,
430 p_project_id => null,
431 x_calendar_id => l_calendar_id,
432 x_calendar_name => l_calendar_name,
433 x_return_status => l_return_status,
434 x_msg_count => l_msg_count,
435 x_msg_data => l_msg_data);
436
437 IF l_return_status = FND_API.G_RET_STS_ERROR
438 THEN
439 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
440 p_msg_name => l_msg_data);
441 x_msg_data := l_msg_data;
442 x_return_status := 'E';
443 RAISE FND_API.G_EXC_ERROR;
444 END IF;
445
446
447 --default public sector flag from profile options.
448 l_public_sector_flag := fnd_profile.value ('PA_DEFAULT_PUBLIC_SECTOR');
449
450 --Call table handler for pa_projects_all
451 OPEN Cur_proj_id;
452 FETCH Cur_proj_id INTO l_template_id;
453 CLOSE Cur_proj_id;
454 x_template_id := l_template_id;
455
456 OPEN cur_currency;
457 FETCH cur_currency INTO l_cur_currency;
458 CLOSE cur_currency;
459
460 OPEN cur_dist_rule;
461 FETCH cur_dist_rule INTO l_distribution_rule;
462 CLOSE cur_dist_rule;
463
464 OPEN cur_impl;
465 FETCH cur_impl INTO l_adv_action_set_id, l_mcb_flag, l_rate_type
466 ,l_retn_accounting_flag, l_competence_match_wt, l_availability_match_wt, l_job_level_match_wt;
467 CLOSE cur_impl;
468
469 l_rate_type2 := l_rate_type;
470
471 IF l_cc_prvdr_flag = 'Y'
472 THEN
473 l_mcb_flag := 'N';
474 END IF;
475
476 IF l_mcb_flag = 'N'
477 THEN
478 l_rate_date_code := null;
479 l_rate_type := null;
480 l_BTC_COST_BASE_REV_CODE := NULL; --bug 2755727
481 ELSE
482 l_rate_date_code := 'PA_INVOICE_DATE';
483 l_BTC_COST_BASE_REV_CODE := 'EXP_TRANS_CURR'; --bug 2755727
484 END IF;
485
486 --Validdate attribute change from WHEN-VALIDATE-RECORD projects form
487 --The following validation is done in forms when a template is created.
488
489 pa_project_utils2.validate_attribute_change
490 ('ORGANIZATION_VALIDATION' -- X_context
491 , 'INSERT' -- X_insert_update_mode
492 , 'SELF_SERVICE' -- X_calling_module
493 , null -- X_project_id
494 , NULL -- X_task_id
495 /*, p_organization_id -- X_old_value --no change Commented for bug 2981386 */
496 , NULL /* Added for bug 2981386 */
497 , p_organization_id -- X_new_value
498 , p_project_type -- X_project_type
499 , null --
500 , null
501 , l_public_sector_flag -- X_public_sector_flag
502 , NULL -- X_task_manager_person_id
503 , NULL -- X_service_type
504 , NULL -- X_task_start_date
505 , NULL -- X_task_end_date
506 , FND_GLOBAL.USER_ID -- X_entered_by_user_id
507 , null -- X_attribute_category
508 , null -- X_attribute1
509 , null -- X_attribute2
510 , null -- X_attribute3
511 , null -- X_attribute4
512 , null -- X_attribute5
513 , null -- X_attribute6
514 , null -- X_attribute7
515 , null -- X_attribute8
516 , null -- X_attribute9
517 , null -- X_attribute10
518 , null -- X_pm_project_code
519 , null -- X_pm_project_reference
520 , NULL -- X_pm_task_reference
521 , 'Y' -- X_functional_security_flag
522 , l_warnings_only_flag --bug3134205
523 , x_err_code -- X_err_code
524 , x_err_stage -- X_err_stage
525 , x_err_stack); -- X_err_stack
526
527
528 if x_err_code <> 0 /* and x_err_code <> 15 */ Then /* Commented for bug 2393975 */
529
530 if x_err_stage = 'PA_INVALID_PT_CLASS_ORG' then
531
532 select meaning
533 into t_project_type_class_code
534 from pa_project_types pt ,pa_lookups lps
535 where pt.project_type = p_project_type
536 and lps.lookup_type(+) = 'PROJECT TYPE CLASS'
537 and lps.lookup_code(+) = pt.project_type_class_code;
538
539 /* Code addition for bug 2393975 begins */
540 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
541 p_msg_name => 'PA_INVALID_PT_CLASS_ORG',
542 p_token1 => 'PT_CLASS',
543 p_value1 => t_project_type_class_code);
544 else
545 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
546 p_msg_name => x_err_stage);
547 end if;
548 /* Code addition for bug 2393975 ends */
549
550 /* Commented for bug 2393975
551 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
552 p_msg_name => x_err_stage,
553 p_token1 => 'PT_CLASS',
554 p_value1 => t_project_type_class_code);
555 */
556 x_msg_data := x_err_stage;
557 x_return_status := 'E';
558 RAISE FND_API.G_EXC_ERROR;
559
560 End If;
561
562 IF t_project_type_class_code <> 'CONTRACT'
563 THEN
564 l_labor_sch_type := null;
565 l_non_labor_sch_type := null;
566 END IF;
567
568 IF t_project_type_class_code = 'CONTRACT'
569 THEN
570 IF l_non_labor_sch_type = 'B'
571 THEN
572 IF l_labor_sch_type = 'B'
573 THEN
574 l_rev_ind_rate_sch_id := null;
575 l_inv_ind_rate_sch_id := null;
576 END IF;
577 ELSIF l_non_labor_sch_type = 'I'
578 THEN
579 l_nl_std_bill_rate_schdl := null;
580 l_non_labor_bill_rate_org_id := null;
581 END IF;
582
583 IF l_labor_sch_type = 'I'
584 THEN
585 l_emp_bill_rate_Schedule_id := null;
586 l_job_bill_rate_Schedule_id := null;
587 END IF;
588 END IF;
589
590
591 PA_PROJECTS_PKG.INSERT_ROW(
592 X_Rowid => l_row_id
593 ,X_Project_Id => l_template_id
594 ,X_Name => p_project_name
595 ,X_Segment1 => p_project_number
596 ,X_Last_Update_Date => SYSDATE
597 ,X_Last_Updated_By => FND_GLOBAL.USER_ID
598 ,X_Creation_Date => SYSDATE
599 ,X_Created_By => FND_GLOBAL.USER_ID
600 ,X_Last_Update_Login => FND_GLOBAL.LOGIN_ID
601 ,X_Project_Type => p_project_type
602 ,X_Carrying_Out_Organization_Id => p_organization_id
603 ,X_Public_Sector_Flag => NVL( l_public_sector_flag, 'N' )
604 ,X_Project_Status_Code => l_Status_code
605 ,X_Description => p_description
606 ,X_Start_Date => null
607 ,X_Completion_Date => null
608 ,X_Closed_Date => null
609 ,X_Distribution_Rule => l_distribution_rule
610 ,X_Labor_Invoice_Format_Id => l_labor_invoice_format_id
611 ,X_NL_Invoice_Format_Id => l_non_labor_invoice_format_id
612 ,X_Retention_Invoice_Format_Id => null
613 ,X_Retention_Percentage => null
614 ,X_Billing_Offset => l_billing_offset
615 ,X_Billing_Cycle_Id => l_billing_cycle_id
616 ,X_Labor_Std_Bill_Rate_Schdl => l_labor_std_bill_rate_schdl
617 ,X_Labor_Bill_Rate_Org_Id => l_labor_bill_rate_org_id
618 ,X_Labor_Schedule_Fixed_Date => null
619 ,X_Labor_Schedule_Discount => null
620 ,X_NL_Std_Bill_Rate_Schdl => l_nl_std_bill_rate_schdl
621 ,X_NL_Bill_Rate_Org_Id => l_non_labor_bill_rate_org_id
622 ,X_NL_Schedule_Fixed_Date => null
623 ,X_NL_Schedule_Discount => null
624 ,X_Limit_To_Txn_Controls_Flag => 'N'
625 ,X_Project_Level_Funding_Flag => null --as in forms
626 ,X_Invoice_Comment => null
627 ,X_Unbilled_Receivable_Dr => null
628 ,X_Unearned_Revenue_Cr => null
629 ,X_Summary_Flag => 'N'
630 ,X_Enabled_Flag => 'Y'
631 ,X_Segment2 => null
632 ,X_Segment3 => null
633 ,X_Segment4 => null
634 ,X_Segment5 => null
635 ,X_Segment6 => null
636 ,X_Segment7 => null
637 ,X_Segment8 => null
638 ,X_Segment9 => null
639 ,X_Segment10 => null
640 ,X_Attribute_Category => null
641 ,X_Attribute1 => null
642 ,X_Attribute2 => null
643 ,X_Attribute3 => null
644 ,X_Attribute4 => null
645 ,X_Attribute5 => null
646 ,X_Attribute6 => null
647 ,X_Attribute7 => null
648 ,X_Attribute8 => null
649 ,X_Attribute9 => null
650 ,X_Attribute10 => null
651 ,X_Cost_Ind_Rate_Sch_Id => l_cost_ind_rate_sch_id
652 ,X_Rev_Ind_Rate_Sch_Id => l_rev_ind_rate_sch_id
653 ,X_Inv_Ind_Rate_Sch_Id => l_inv_ind_rate_sch_id
654 ,X_Cost_Ind_Sch_Fixed_Date => null
655 ,X_Rev_Ind_Sch_Fixed_Date => null
656 ,X_Inv_Ind_Sch_Fixed_Date => null
657 ,X_Labor_Sch_Type => l_labor_sch_type
658 ,X_Non_Labor_Sch_Type => l_non_labor_sch_type
659 ,X_Template_Flag => 'Y'
660 ,X_Verification_Date => null
661 ,X_Created_From_Project_Id => null --l_template_id
662 ,X_Template_Start_Date => l_effective_from_date
663 ,X_Template_End_Date => l_effective_to_date
664 ,X_Project_Currency_Code => l_cur_currency.currency_code
665 ,X_Allow_Cross_Charge_Flag => 'N'
666 ,X_Project_Rate_Date => null
667 ,X_Project_Rate_Type => l_rate_type2
668 ,X_Output_Tax_Code => null
669 ,X_Retention_Tax_Code => null
670 ,X_CC_Process_Labor_Flag => 'N'
671 ,X_Labor_Tp_Schedule_Id => null
672 ,X_Labor_Tp_Fixed_Date => null
673 ,X_CC_Process_NL_Flag => 'N'
674 ,X_Nl_Tp_Schedule_Id => null
675 ,X_Nl_Tp_Fixed_Date => null
676 ,X_CC_Tax_Task_Id => null
677 ,x_bill_job_group_id => l_bill_job_group_id
678 ,x_cost_job_group_id => l_cost_job_group_id
679 ,x_role_list_id => l_role_list_id
680 ,x_work_type_id => l_work_type_id
681 ,x_calendar_id => l_calendar_id
682 ,x_location_id => l_location_id
683 ,x_probability_member_id => null
684 ,x_project_value => null
685 ,x_expected_approval_date => null
686 ,x_team_template_id => null
687 ,x_job_bill_rate_schedule_id => l_job_bill_rate_schedule_id
688 ,x_emp_bill_rate_schedule_id => l_emp_bill_rate_schedule_id
689 ,x_competence_match_wt => l_competence_match_wt
690 ,x_availability_match_wt => l_availability_match_wt
691 ,x_job_level_match_wt => l_job_level_match_wt
692 ,x_enable_automated_search => 'N'
693 ,x_search_min_availability => 100
694 ,x_search_org_hier_id => l_cur_currency.exp_org_structure_version_id
695 ,x_search_starting_org_id => l_cur_currency.exp_start_org_id
696 ,x_search_country_code => null
697 ,x_min_cand_score_reqd_for_nom => 100
698 ,x_non_lab_std_bill_rt_sch_id => l_non_lab_std_bill_rt_sch_id
699 ,x_invproc_currency_type => 'PROJFUNC_CURRENCY'
700 ,x_revproc_currency_code => l_cur_currency.currency_code
701 ,x_project_bil_rate_date_code => l_rate_date_code
702 ,x_project_bil_rate_type => l_rate_type
703 ,x_project_bil_rate_date => null
704 ,x_project_bil_exchange_rate => null
705 ,x_projfunc_currency_code => l_cur_currency.currency_code
706 ,x_projfunc_bil_rate_date_code => l_rate_date_code
707 ,x_projfunc_bil_rate_type => l_rate_type
708 ,x_projfunc_bil_rate_date => null
709 ,x_projfunc_bil_exchange_rate => null
710 ,x_funding_rate_date_code => l_rate_date_code
711 ,x_funding_rate_type => l_rate_type
712 ,x_funding_rate_date => null
713 ,x_funding_exchange_rate => null
714 ,x_baseline_funding_flag => l_baseline_funding_flag
715 ,x_projfunc_cost_rate_type => l_rate_type2
716 ,x_projfunc_cost_rate_date => null
717 ,x_multi_currency_billing_flag => l_mcb_flag
718 ,x_inv_by_bill_trans_curr_flag => 'N'
719 ,x_assign_precedes_task => 'N'
720 ,x_split_cost_from_wokplan_flag => 'Y' --Default the workplan str is split from costing
721 ,x_split_cost_from_bill_flag => 'N'
722 ,x_adv_action_set_id => l_adv_action_set_id
723 ,x_start_adv_action_set_flag => 'Y'
724 ,x_priority_code => null
725 ,x_retn_billing_inv_format_id => null
726 ,x_retn_accounting_flag => l_retn_accounting_flag
727 -- anlee
728 -- patchset K changes
729 ,x_revaluate_funding_flag => l_revaluate_funding_flag
730 ,x_include_gains_losses_flag => l_include_gains_losses_flag
731 -- msundare
732 , x_security_level => p_security_level
733 , x_labor_disc_reason_code => null
734 , x_non_labor_disc_reason_code => null
735 -- End of changes
736 -- anlee
737 -- Project Long Name changes
738 , x_long_name => p_long_name
739 -- End of changes
740 --PA L changes 2872708
741 ,x_asset_allocation_method => l_asset_allocation_method
742 ,x_capital_event_processing => l_capital_event_processing
743 ,x_cint_rate_sch_id => l_cint_rate_sch_id
744 ,x_cint_eligible_flag => 'Y'
745 ,x_cint_stop_date => null
746 --End PA L changes 2872708
747 ,x_record_version_number => 1
748 , X_BTC_COST_BASE_REV_CODE => l_BTC_COST_BASE_REV_CODE --bug 2755727
749 --FP_M Changes. Tracking Bug 3279981
750 , x_revtrans_currency_type => null -- 4363092 for MOAC changes
751 ,x_en_top_task_customer_flag => 'N'
752 ,x_en_top_task_inv_mth_flag => 'N'
753 ,x_revenue_accrual_method =>
754 substr(l_distribution_rule, 1, instr(l_distribution_rule,'/')-1)
755 ,x_invoice_method =>
756 substr(l_distribution_rule, instr(l_distribution_rule,'/')+1)
757 ,x_projfunc_attr_for_ar_flag => 'N'
758 ,x_sys_program_flag => 'N'
759 ,x_allow_multi_program_rollup => 'N'
760 ,x_proj_req_res_format_id =>NULL
761 ,x_proj_asgmt_res_format_id =>NULL
762 ,x_org_id => p_operating_unit_id -- 4363092 MOAC changes
763 ,x_date_eff_funds_flag =>nvl(l_date_eff_funds_flag,'N') --sunkalya:federal changes Bug#5511353
764 ,x_ar_rec_notify_flag => l_ar_rec_notify_flag -- 7508661 : EnC
765 ,x_auto_release_pwp_inv => l_auto_release_pwp_inv -- 7508661 : EnC
766 );
767
768 --Call Add options api to add options in pa_project_options with the created template.
769 DECLARE
770 CURSOR cur_template_options
771 IS
772 SELECT option_code
773 FROM pa_options;
774 BEGIN
775 FOR cur_template_options_rec IN cur_template_options LOOP
776 -- anlee
777 -- Enable Advanced Structures
778 -- Don't want to add structure or workplan related options by default
779 --FP_M Changes. Tracking Bug 3279978
780 IF cur_template_options_rec.option_code NOT IN ('STRUCTURES', 'STRUCTURES_SS','DELIVERABLES_SS') THEN
781 PA_PROJ_TEMPLATE_SETUP_PUB.Add_Project_Options(
782 p_api_version => p_api_version
783 ,p_init_msg_list => p_init_msg_list
784 ,p_commit => p_commit
785 ,p_validate_only => p_validate_only
786 ,p_validation_level => p_validation_level
787 ,p_calling_module => p_calling_module
788 ,p_debug_mode => p_debug_mode
789 ,p_max_msg_count => p_max_msg_count
790 ,p_project_id => l_template_id
791 ,p_option_code => cur_template_options_rec.option_code
792 ,p_action => 'INSERT'
793 ,x_return_status => l_return_status
794 ,x_msg_count => l_msg_count
795 ,x_msg_data => l_msg_data
796 );
797 END IF;
798 -- End of changes
799 END LOOP;
800 END;
801
802 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
803 x_msg_count := FND_MSG_PUB.count_msg;
804 IF x_msg_count = 1 then
805 pa_interface_utils_pub.get_messages
806 (p_encoded => FND_API.G_TRUE,
807 p_msg_index => 1,
808 p_msg_count => l_msg_count,
809 p_msg_data => l_msg_data,
810 p_data => l_data,
811 p_msg_index_out => l_msg_index_out);
812 x_msg_data := l_data;
813 END IF;
814 raise FND_API.G_EXC_ERROR;
815 END IF;
816
817 --Call Add quick entry api to add two default quick entries( SEGMENT1 and NAME ) in
818 --pa_project_copy_overrides with the created template.
819 DECLARE
820 CURSOR cur_copy_overrides
821 IS
822 SELECT lookup_code, meaning
823 FROM pa_lookups
824 WHERE lookup_type = 'OVERRIDE FIELD'
825 AND lookup_code = 'SEGMENT1'
826 UNION
827 SELECT lookup_code, meaning
828 FROM pa_lookups
829 WHERE lookup_type = 'OVERRIDE FIELD'
830 AND lookup_code = 'NAME'
831 ORDER BY 1 DESC;
832
833 l_rownum NUMBER := 0;
834 BEGIN
835 FOR cur_copy_overrides_rec IN cur_copy_overrides LOOP
836 l_rownum := l_rownum + 1;
837 PA_PROJ_TEMPLATE_SETUP_PUB.Add_Quick_Entry_Field(
838 p_api_version => p_api_version
839 ,p_init_msg_list => p_init_msg_list
840 ,p_commit => p_commit
841 ,p_validate_only => p_validate_only
842 ,p_validation_level => p_validation_level
843 ,p_calling_module => p_calling_module
844 ,p_debug_mode => p_debug_mode
845 ,p_max_msg_count => p_max_msg_count
846 ,p_project_id => l_template_id
847 ,p_sort_order => l_rownum * 10
848 ,p_field_name => cur_copy_overrides_rec.lookup_code
849 ,p_limiting_value => null
850 ,p_prompt => cur_copy_overrides_rec.meaning
851 ,p_required_flag => 'Y'
852 ,x_return_status => l_return_status
853 ,x_msg_count => l_msg_count
854 ,x_msg_data => l_msg_data
855 );
856
857 END LOOP;
858 END;
859
860 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
861 x_msg_count := FND_MSG_PUB.count_msg;
862 IF x_msg_count = 1 then
863 pa_interface_utils_pub.get_messages
864 (p_encoded => FND_API.G_TRUE,
865 p_msg_index => 1,
866 p_msg_count => l_msg_count,
867 p_msg_data => l_msg_data,
868 p_data => l_data,
869 p_msg_index_out => l_msg_index_out);
870 x_msg_data := l_data;
871 END IF;
872 raise FND_API.G_EXC_ERROR;
873 END IF;
874
875 --Insert default resource list
876 Open Get_def_Res_List;
877 Fetch Get_def_Res_List Into x_def_res_list_id, x_rl_asgmt_id;
878 If Get_def_Res_list%NOTFOUND Then
879 x_def_res_list_id := NULL;
880 end if;
881 Close Get_def_Res_List;
882 IF x_def_res_list_id is not null Then
883 INSERT INTO pa_resource_list_assignments (
884 resource_list_assignment_id,
885 resource_list_id,
886 project_id,
887 resource_list_changed_flag,
888 resource_list_accumulated_flag,
889 last_updated_by,
890 last_update_date,
891 creation_date,
892 created_by,
893 last_update_login )
894 Values (
895 x_rl_asgmt_id,
896 x_def_res_list_id,
897 l_template_id,
898 'N',
899 'N',
900 nvl(x_user_id,-1),
901 trunc(sysdate),
902 trunc(sysdate),
903 nvl(x_user_id,-1),
904 nvl(x_login_id,-1));
905 Insert into pa_resource_list_uses (
906 resource_list_assignment_id,
907 use_code,
908 default_flag,
909 last_updated_by,
910 last_update_date,
911 creation_date,
912 created_by,
913 last_update_login )
914 values (
915 x_rl_asgmt_id,
916 x_def_use_code,
917 x_def_flag,
918 nvl(x_user_id,-1),
919 trunc(sysdate),
920 trunc(sysdate),
921 nvl(x_user_id,-1),
922 nvl(x_login_id ,-1));
923 END IF;
924
925
926 --Create a default task
927
928 IF l_project_type_class_code = 'INDIRECT'
929 THEN
930 l_billable_flag := 'N';
931 ELSE
932 l_billable_flag := 'Y';
933 END IF;
934
935 /* From FPM we will not be creating any default task or structure at the time of template creation
936 Please refer Financial structures in HTML Technical Architecture on files online
937 --bug 3301192
938 PA_TASKS_PKG.Insert_Row(
939 X_Rowid => l_row_id,
940 X_task_id => l_task_id,
941 X_Project_id => l_template_id,
942 X_Task_Number => '1',
943 X_Creation_Date => sysdate,
944 X_Created_By => FND_GLOBAL.USER_ID,
945 X_Last_Update_Date => sysdate,
946 X_last_Updated_By => FND_GLOBAL.USER_ID,
947 X_Last_Update_login => FND_GLOBAL.LOGIN_ID,
948 X_Task_Name => 'Task 1',
949 X_Long_Task_Name => 'Task 1',
950 X_Top_Task_Id => null,
951 X_Wbs_level => 1,
952 X_ready_to_Bill_flag => 'Y',
953 X_Ready_To_Distribute_Flag => 'Y',
954 X_parent_task_id => null,
955 X_Description => 'Task 1',
956 X_carrying_out_organization_id => p_organization_id,
957 X_Service_Type_code => l_service_type_code,
958 X_Task_Manager_Person_id => null,
959 X_chargeable_Flag => 'Y',
960 X_Billable_flag => l_billable_flag,
961 X_limit_to_Txn_controls_flag => 'N',
962 X_Start_Date => null,
963 X_Completion_Date => null,
964 X_Address_Id => null, --w_address_id, -Since no customer is created so far
965 X_Labor_Bill_Rate_org_id => l_labor_bill_rate_org_id, -- :project_folder.Labor_Bill_Rate_Org_Id,
966 X_Labor_Std_Bill_Rate_Schdl => l_labor_std_bill_rate_schdl, --:project_folder.Labor_Std_Bill_Rate_Schdl,
967 X_Labor_Schedule_Fixed_Date => null,
968 X_Labor_Schedule_Discount => null,
969 X_Non_Labor_Bill_Rate_Org_Id => l_non_labor_bill_rate_org_id, --:project_folder.Non_Labor_Bill_Rate_Org_Id,
970 X_NL_Std_Bill_Rate_Schdl => l_nl_std_bill_rate_schdl, --:project_folder.Non_Labor_Std_Bill_Rate_Schdl,
971 X_Nl_Schedule_Fixed_Date => null,
972 X_Non_Labor_Schedule_Discount => null,
973 X_Labor_Cost_Multiplier_Name => null,
974 X_Attribute_Category => null,
975 X_Attribute1 => null,
976 X_Attribute2 => null,
977 X_Attribute3 => null,
978 X_Attribute4 => null,
979 X_Attribute5 => null,
980 X_Attribute6 => null,
981 X_Attribute7 => null,
982 X_Attribute8 => null,
983 X_Attribute9 => null,
984 X_Attribute10 => null,
985 X_Cost_Ind_Rate_Sch_Id => l_Cost_Ind_Rate_Sch_Id,
986 X_Rev_ind_rate_sch_id => l_Rev_Ind_Rate_Sch_Id,
987 X_Inv_Ind_rate_sch_id => l_Inv_Ind_Rate_Sch_Id,
988 X_Cost_ind_sch_fixed_date => null,
989 X_Rev_Ind_sch_fixed_date => null,
990 X_Inv_Ind_sch_fixed_date => null,
991 X_Labor_Sch_Type => l_Labor_Sch_Type,
992 X_Non_Labor_Sch_Type => l_Non_Labor_Sch_Type,
993 X_Allow_Cross_Charge_Flag => 'N',
994 X_Project_Rate_Date => null,
995 X_Project_Rate_Type => l_rate_type2,
996 X_cc_process_labor_flag => 'N',
997 X_Labor_tp_schedule_id => null,
998 X_Labor_tp_fixed_date => null,
999 X_cc_process_nl_flag => 'N',
1000 X_nl_tp_schedule_id => null,
1001 X_nl_tp_fixed_date => null,
1002 X_receive_project_invoice_flag => 'N',
1003 X_work_type_id => l_work_type_id,
1004 X_TASKFUNC_COST_RATE_TYPE => l_rate_type2,
1005 X_TASKFUNC_COST_RATE_DATE => null,
1006 X_NON_LAB_STD_BILL_RT_SCH_ID => l_non_lab_std_bill_rt_sch_id,
1007 X_job_bill_rate_schedule_id => l_job_bill_rate_schedule_id,
1008 X_emp_bill_rate_schedule_id => l_emp_bill_rate_schedule_id,
1009 X_labor_disc_reason_code => null,
1010 X_non_labor_disc_reason_code => null,
1011 --PA L 2872708
1012 x_retirement_cost_flag => 'N',
1013 x_cint_eligible_flag => 'Y',
1014 x_cint_stop_date => null
1015 --PA L 2872708
1016 );
1017 */ --bug 3301192
1018
1019 --Opportunity Management changes.
1020 PA_OPPORTUNITY_MGT_PVT.CREATE_PROJECT_ATTRIBUTES(
1021 p_project_id => l_template_id
1022 ,x_return_status => l_return_status
1023 ,x_msg_count => l_msg_count
1024 ,x_msg_data => l_msg_data );
1025
1026 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1027 x_msg_count := FND_MSG_PUB.count_msg;
1028 IF x_msg_count = 1 then
1029 pa_interface_utils_pub.get_messages
1030 (p_encoded => FND_API.G_TRUE,
1031 p_msg_index => 1,
1032 p_msg_count => l_msg_count,
1033 p_msg_data => l_msg_data,
1034 p_data => l_data,
1035 p_msg_index_out => l_msg_index_out);
1036 x_msg_data := l_data;
1037 END IF;
1038 raise FND_API.G_EXC_ERROR;
1039 END IF;
1040
1041 /* From FPM we will not be creating any default structure at the time of template creation
1042 Please refer Financial structures in HTML Technical Architecture on files online
1043 --bug 3301192
1044 --Create a Structure Workplan and Financial structures separately
1045
1046 PA_PROJ_TASK_STRUC_PUB.create_default_structure(
1047 p_dest_project_id => l_template_id
1048 ,p_dest_project_name => p_project_name
1049 ,p_dest_project_number => p_project_number
1050 ,p_dest_description => p_description
1051 ,p_struc_type => 'FINANCIAL'
1052 ,x_msg_count => l_msg_count
1053 ,x_msg_data => l_msg_data
1054 ,x_return_status => l_return_status
1055 );
1056
1057 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1058 x_msg_count := FND_MSG_PUB.count_msg;
1059 IF x_msg_count = 1 then
1060 pa_interface_utils_pub.get_messages
1061 (p_encoded => FND_API.G_TRUE,
1062 p_msg_index => 1,
1063 p_msg_count => l_msg_count,
1064 p_msg_data => l_msg_data,
1065 p_data => l_data,
1066 p_msg_index_out => l_msg_index_out);
1067 x_msg_data := l_data;
1068 END IF;
1069 raise FND_API.G_EXC_ERROR;
1070 END IF;
1071
1072 --Project Structures Changes
1073 --Creating tasks in pa_proj_elements from pa_tasks
1074 PA_PROJ_TASK_STRUC_PUB.CREATE_DEFAULT_TASK_STRUCTURE(
1075 p_project_id => l_template_id
1076 ,p_struc_type => 'FINANCIAL'
1077 ,x_msg_count => l_msg_count
1078 ,x_msg_data => l_msg_data
1079 ,x_return_status => l_return_status );
1080
1081 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1082 x_msg_count := FND_MSG_PUB.count_msg;
1083 IF x_msg_count = 1 then
1084 pa_interface_utils_pub.get_messages
1085 (p_encoded => FND_API.G_TRUE,
1086 p_msg_index => 1,
1087 p_msg_count => l_msg_count,
1088 p_msg_data => l_msg_data,
1089 p_data => l_data,
1090 p_msg_index_out => l_msg_index_out);
1091 x_msg_data := l_data;
1092 END IF;
1093 raise FND_API.G_EXC_ERROR;
1094 END IF;
1095 */ --bug 3301192
1096
1097 -- anlee
1098 -- Advanced Project Structures
1099 -- Comment out creation of workplan structure
1100 -- New templates will only have financial structure
1101 /*
1102 --Create a Structure Workplan and Financial structures separately
1103
1104 PA_PROJ_TASK_STRUC_PUB.create_default_structure(
1105 p_dest_project_id => l_template_id
1106 ,p_dest_project_name => p_project_name
1107 ,p_dest_project_number => p_project_number
1108 ,p_dest_description => p_description
1109 ,p_struc_type => 'WORKPLAN'
1110 ,x_msg_count => l_msg_count
1111 ,x_msg_data => l_msg_data
1112 ,x_return_status => l_return_status
1113 );
1114
1115 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1116 x_msg_count := FND_MSG_PUB.count_msg;
1117 IF x_msg_count = 1 then
1118 pa_interface_utils_pub.get_messages
1119 (p_encoded => FND_API.G_TRUE,
1120 p_msg_index => 1,
1121 p_msg_count => l_msg_count,
1122 p_msg_data => l_msg_data,
1123 p_data => l_data,
1124 p_msg_index_out => l_msg_index_out);
1125 x_msg_data := l_data;
1126 END IF;
1127 raise FND_API.G_EXC_ERROR;
1128 END IF;
1129 */
1130 -- End of changes
1131
1132 x_return_status := FND_API.G_RET_STS_SUCCESS;
1133
1134 IF (p_commit = FND_API.G_TRUE) THEN
1135 COMMIT;
1136 END IF;
1137
1138 IF (p_debug_mode = 'Y') THEN
1139 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Create_Project_Template END');
1140 END IF;
1141 EXCEPTION
1142 when FND_API.G_EXC_ERROR then
1143 if p_commit = FND_API.G_TRUE then
1144 rollback to Create_Project_Template;
1145 end if;
1146 x_return_status := FND_API.G_RET_STS_ERROR;
1147 when FND_API.G_EXC_UNEXPECTED_ERROR then
1148 if p_commit = FND_API.G_TRUE then
1149 rollback to Create_Project_Template;
1150 end if;
1151 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1152 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
1153 p_procedure_name => 'Create_Project_Template',
1154 p_error_text => SUBSTRB(SQLERRM,1,240));
1155 when OTHERS then
1156 if p_commit = FND_API.G_TRUE then
1157 rollback to Create_Project_Template;
1158 end if;
1159 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1160 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
1161 p_procedure_name => 'Create_Project_Template',
1162 p_error_text => SUBSTRB(SQLERRM,1,240));
1163 raise;
1164 END Create_Project_Template;
1165
1166 -- API name : Update_Project_Template
1167 -- Type : Public API
1168 -- Pre-reqs : None
1169 -- Return Value :
1170 --
1171 -- Parameters
1172 --p_project_number IN VARCHAR2
1173 --p_project_name IN VARCHAR2
1174 --p_project_type IN VARCHAR2
1175 --p_organization_id IN NUMBER
1176 --p_organization_name IN VARCHAR2
1177 --p_effective_from_date IN DATE
1178 --p_effective_to_date IN DATE
1179 --p_description IN VARCHAR2
1180
1181 PROCEDURE Update_Project_Template(
1182 p_api_version IN NUMBER :=1.0,
1183 p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE,
1184 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
1185 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE,
1186 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
1187 p_calling_module IN VARCHAR2 :='SELF_SERVICE',
1188 p_debug_mode IN VARCHAR2 :='N',
1189 p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
1190 p_project_id IN NUMBER,
1191 p_project_number IN VARCHAR2 := 'JUNK_CHARS',
1192 p_project_name IN VARCHAR2 := 'JUNK_CHARS',
1193 p_project_type IN VARCHAR2 := 'JUNK_CHARS',
1194 p_organization_id IN NUMBER := -9999,
1195 p_effective_from_date IN DATE := TO_DATE( '01-01-1000', 'DD-MM-YYYY' ),
1196 p_effective_to_date IN DATE := TO_DATE( '01-01-1000', 'DD-MM-YYYY' ),
1197 p_description IN VARCHAR2 := 'JUNK_CHARS',
1198 p_security_level IN NUMBER := 0,
1199 -- anlee
1200 -- Project Long Name changes
1201 p_long_name IN VARCHAR2 DEFAULT NULL,
1202 -- End of changes
1203 p_record_version_number IN NUMBER,
1204 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1205 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1206 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1207 ) IS
1208 l_api_name CONSTANT VARCHAR(30) := 'Update_Project_Template';
1209 l_api_version CONSTANT NUMBER := 1.0;
1210
1211 l_return_status VARCHAR2(1);
1212 l_msg_count NUMBER;
1213 l_msg_data VARCHAR2(250);
1214 l_data VARCHAR2(250);
1215 l_msg_index_out NUMBER;
1216 l_error_msg_code VARCHAR2(250);
1217
1218 l_organization_id NUMBER;
1219
1220 l_Status_code VARCHAR2(80);
1221 l_service_type_code VARCHAR2(80);
1222 l_cost_ind_rate_sch_id NUMBER;
1223 l_labor_sch_type VARCHAR2(80);
1224 l_labor_bill_rate_org_id NUMBER;
1225 l_labor_std_bill_rate_schdl VARCHAR2(80);
1226 l_non_labor_sch_type VARCHAR2(80);
1227 l_non_labor_bill_rate_org_id NUMBER;
1228 l_nl_std_bill_rate_schdl VARCHAR2(80);
1229 l_rev_ind_rate_sch_id NUMBER;
1230 l_inv_ind_rate_sch_id NUMBER;
1231 l_labor_invoice_format_id NUMBER;
1232 l_non_labor_invoice_format_id NUMBER;
1233 l_Burden_cost_flag VARCHAR2(80);
1234 l_interface_asset_cost_code VARCHAR2(80);
1235 l_cost_sch_override_flag VARCHAR2(80);
1236 l_billing_offset NUMBER;
1237 l_billing_cycle_id NUMBER;
1238 l_cc_prvdr_flag VARCHAR2(80);
1239 l_bill_job_group_id NUMBER;
1240 l_cost_job_group_id NUMBER;
1241 l_work_type_id NUMBER;
1242 l_role_list_id NUMBER;
1243 l_unassigned_time VARCHAR2(1);
1244 l_emp_bill_rate_schedule_id NUMBER;
1245 l_job_bill_rate_schedule_id NUMBER;
1246 l_budgetary_override_flag VARCHAR2(80);
1247 l_baseline_funding_flag VARCHAR2(80);
1248 l_non_lab_std_bill_rt_sch_id NUMBER;
1249 l_project_type_class_code VARCHAR2(80);
1250 l_effective_from_date DATE;
1251 l_effective_to_date DATE;
1252
1253 l_old_project_type VARCHAR2(80);
1254
1255 -- anlee
1256 -- patchset K changes
1257 l_revaluate_funding_flag VARCHAR2(1);
1258 l_include_gains_losses_flag VARCHAR2(1);
1259 -- End of changes
1260
1261 l_err_code NUMBER;
1262 l_err_stage VARCHAR2(2000);
1263 l_err_stack VARCHAR2(2000);
1264
1265 x_err_code NUMBER;
1266 x_err_stage VARCHAR2(2000);
1267 x_err_stack VARCHAR2(2000);
1268
1269
1270 CURSOR cur_project
1271 IS
1272 SELECT rowid, project_type, carrying_out_organization_id, public_sector_flag, segment1, location_id,
1273 Rev_ind_sch_fixed_date, Inv_ind_sch_fixed_date, distribution_rule,
1274 --bug 3068781
1275 multi_currency_billing_flag,projfunc_currency_code,
1276 PROJFUNC_BIL_RATE_TYPE, PROJECT_BIL_RATE_TYPE, FUNDING_RATE_TYPE,
1277 PROJFUNC_BIL_RATE_DATE_CODE, PROJECT_BIL_RATE_DATE_CODE, FUNDING_RATE_DATE_CODE,
1278 BTC_COST_BASE_REV_CODE
1279 --bug 3068781
1280 --bug 4308335
1281 ,cc_process_labor_flag
1282 ,cc_process_nl_flag
1283 ,labor_tp_schedule_id
1284 ,nl_tp_schedule_id
1285 ,labor_tp_fixed_date
1286 ,nl_tp_fixed_date
1287 --bug 4308335
1288 ,nvl(date_eff_funds_consumption,'N') --federal bug#5511353
1289 ,enable_top_task_customer_flag --federal bug#5511353
1290 ,ar_rec_notify_flag -- 7508661 : EnC
1291 ,auto_release_pwp_inv -- 7508661 : EnC
1292 FROM pa_projects_all
1293 WHERE project_id = p_project_id;
1294
1295 CURSOR cur_pa_tasks IS
1296 SELECT Rev_ind_sch_fixed_date, Inv_ind_sch_fixed_date
1297 FROM pa_tasks
1298 WHERE project_id = p_project_id;
1299
1300 CURSOR cur_project_type_class
1301 IS
1302 SELECT project_type_class_code
1303 FROM pa_project_types_all
1304 WHERE project_type = p_project_type;
1305
1306 CURSOR cur_bill_flag( c_work_type_id NUMBER )
1307 IS
1308 SELECT billable_capitalizable_flag
1309 FROM pa_work_types_vl
1310 WHERE work_type_id = c_work_type_id;
1311
1312 CURSOR cur_dist_rule
1313 IS
1314 /* SELECT r.distribution_rule
1315 FROM pa_project_type_distributions d, pa_distribution_rules r
1316 WHERE d.distribution_rule = r.distribution_rule
1317 AND project_type = p_project_type
1318 AND default_flag = 'Y';
1319 */
1320 --copied from project_folder1.project_type_mir1 when-validate-item validation trigger.
1321
1322 select distribution_rule
1323 from pa_project_type_distributions
1324 where project_type = p_project_type
1325 and default_flag = 'Y';
1326
1327
1328 l_Inv_ind_sch_fixed_date1 DATE;
1329 l_Rev_ind_sch_fixed_date1 DATE;
1330
1331 l_Inv_ind_sch_fixed_date2 DATE;
1332 l_Rev_ind_sch_fixed_date2 DATE;
1333
1334 l_distribution_rule VARCHAR2(20);
1335 l_old_distribution_rule VARCHAR2(20);
1336
1337 l_row_id VARCHAR2(18);
1338 l_proj_number VARCHAR2(80);
1339
1340 l_cc_process_labor_flag VARCHAR2(1);
1341 l_cc_process_nl_flag VARCHAR2(1);
1342 l_labor_tp_schedule_id NUMBER;
1343 l_nl_tp_schedule_id NUMBER;
1344 l_labor_tp_fixed_date DATE;
1345 l_nl_tp_fixed_date DATE;
1346
1347 l_location_id NUMBER;
1348 x_rowid VARCHAR2(18);
1349 l_city_name VARCHAR2(250);
1350 l_country_code VARCHAR2(250);
1351 l_country_name VARCHAR2(250);
1352
1353 /* Type of l_region_name has been changed to %TYPE from varchar2 for the UTF8 change */
1354 l_region_name hr_locations_all.region_1%TYPE;
1355 l_error_message_code VARCHAR2(250);
1356 l_old_organization_id NUMBER;
1357 l_billable_flag VARCHAR2(1);
1358 l_public_sector_flag VARCHAR2(1);
1359
1360 --PA L 2872708
1361 l_asset_allocation_method VARCHAR2(30);
1362 l_CAPITAL_EVENT_PROCESSING VARCHAR2(30);
1363 l_CINT_RATE_SCH_ID NUMBER;
1364 --PA L 2872708
1365
1366 --federal.Bug#5511353
1367 l_date_eff_funds_consumption VARCHAR2(1);
1368 --federal.Bug#5511353
1369
1370 l_ar_rec_notify_flag VARCHAR2(1); -- 7508661 : EnC
1371 l_auto_release_pwp_inv VARCHAR2(1); -- 7508661 : EnC
1372
1373 --bug 3068781 maansari
1374 l_multi_currency_billing_flag VARCHAR2(1); --derived value
1375 l_multi_currency_billing_flag2 VARCHAR2(1); --old value from database
1376 l_PROJFUNC_BIL_RATE_DATE_CODE VARCHAR2(30);
1377 l_PROJECT_BIL_RATE_DATE_CODE VARCHAR2(30);
1378 l_FUNDING_RATE_DATE_CODE VARCHAR2(30);
1379 l_PROJFUNC_BIL_RATE_TYPE VARCHAR2(30);
1380 l_PROJECT_BIL_RATE_TYPE VARCHAR2(30);
1381 l_FUNDING_RATE_TYPE VARCHAR2(30);
1382 l_BTC_COST_BASE_REV_CODE VARCHAR2(90);
1383 l_projfunc_currency_code VARCHAR2(15);
1384 --end bug 3068781
1385 l_warnings_only_flag VARCHAR2(1) := 'N'; --bug3134205
1386
1387 --sunkalya federal
1388
1389 L_old_TOP_TASK_FLAG VARCHAR2(1) := 'N';
1390 l_old_funds_flag VARCHAR2(1) := 'N';
1391 --sunkalya federal
1392
1393 BEGIN
1394 pa_debug.init_err_stack ('PA_PROJ_TEMPLATE_SETUP_PVT.Update_Project_Template');
1395
1396 IF (p_debug_mode = 'Y') THEN
1397 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Update_Project_Template begin');
1398 END IF;
1399
1400 IF (p_commit = FND_API.G_TRUE) THEN
1401 savepoint Update_Project_Template;
1402 END IF;
1403
1404 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
1405 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1406 END IF;
1407
1408 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
1409 FND_MSG_PUB.initialize;
1410 END IF;
1411
1412 --check project type change
1413 OPEN cur_project;
1414 FETCH cur_project INTO l_row_id, l_old_project_type, l_old_organization_id, l_public_sector_flag, l_proj_number, l_location_id , l_Rev_ind_sch_fixed_date1, l_Inv_ind_sch_fixed_date1, l_old_distribution_rule,
1415 --bug 3068781
1416 l_multi_currency_billing_flag2,l_projfunc_currency_code,
1417 l_PROJFUNC_BIL_RATE_TYPE, l_PROJECT_BIL_RATE_TYPE, l_FUNDING_RATE_TYPE,
1418 l_PROJFUNC_BIL_RATE_DATE_CODE, l_PROJECT_BIL_RATE_DATE_CODE, l_FUNDING_RATE_DATE_CODE,
1419 l_BTC_COST_BASE_REV_CODE
1420 --bug 3068781
1421 --bug4308335
1422 ,l_cc_process_labor_flag
1423 ,l_cc_process_nl_flag
1424 ,l_labor_tp_schedule_id
1425 ,l_nl_tp_schedule_id
1426 ,l_labor_tp_fixed_date
1427 ,l_nl_tp_fixed_date
1428 --end bug4308335
1429 ,l_old_funds_flag --federal bug#5511353
1430 ,L_old_TOP_TASK_FLAG --federal bug#5511353
1431 ,l_ar_rec_notify_flag -- 7508661 : EnC
1432 ,l_auto_release_pwp_inv -- 7508661 : EnC
1433 ;
1434
1435 CLOSE cur_project;
1436
1437 OPEN cur_pa_tasks;
1438 FETCH cur_pa_tasks INTO l_Rev_ind_sch_fixed_date2, l_Inv_ind_sch_fixed_date2;
1439 CLOSE cur_pa_tasks;
1440
1441 --Check for not null
1442 PA_PROJ_TEMPLATE_SETUP_UTILS.Check_Template_attr_req(
1443 p_project_number => p_project_number
1444 ,p_project_name => p_project_name
1445 ,p_project_type => p_project_type
1446 ,p_organization_id => p_organization_id
1447 ,x_return_status => l_return_status
1448 ,x_error_msg_code => l_error_msg_code
1449 );
1450
1451 IF l_return_status = FND_API.G_RET_STS_ERROR
1452 THEN
1453 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1454 p_msg_name => l_error_msg_code);
1455 x_msg_data := l_error_msg_code;
1456 x_return_status := 'E';
1457 RAISE FND_API.G_EXC_ERROR;
1458 END IF;
1459
1460 --Check proj number change
1461 IF l_proj_number <> p_project_number
1462 THEN
1463 pa_project_utils.change_proj_num_ok (
1464 p_project_id,
1465 l_err_code,
1466 l_err_stage,
1467 l_err_stack);
1468 if l_err_code <> 0 Then
1469 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1470 p_msg_name => l_err_stage );
1471 x_msg_data := x_err_stage;
1472 x_return_status := 'E';
1473 RAISE FND_API.G_EXC_ERROR;
1474 end If;
1475 END IF;
1476
1477 --check for project number uniqueness
1478 IF pa_project_utils.check_unique_project_number (x_project_number => p_project_number
1479 ,x_rowid => l_row_id ) = 0
1480 THEN
1481 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1482 p_msg_name => 'PA_SETUP_TMPL_NUM_NOT_UNIQUE' );
1483 x_msg_data := 'PA_SETUP_TMPL_NUM_NOT_UNIQUE';
1484 x_return_status := 'E';
1485 RAISE FND_API.G_EXC_ERROR;
1486 END IF;
1487
1488 --check for project name uniqueness
1489 IF pa_project_utils.check_unique_project_name (x_project_name => p_project_name
1490 ,x_rowid => l_row_id ) = 0
1491 THEN
1492 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1493 p_msg_name => 'PA_SETUP_TMPL_NAME_NOT_UNIQUE' );
1494 x_msg_data := 'PA_SETUP_TMPL_NAME_NOT_UNIQUE';
1495 x_return_status := 'E';
1496 RAISE FND_API.G_EXC_ERROR;
1497 END IF;
1498
1499 -- anlee
1500 -- Project Long Name changes
1501 IF pa_project_utils.check_unique_long_name (p_long_name, l_row_id ) = 0
1502 THEN
1503 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1504 p_msg_name => 'PA_PR_EPR_LONG_NAME_NOT_UNIQUE' );
1505 x_msg_data := 'PA_PR_EPR_LONG_NAME_NOT_UNIQUE';
1506 x_return_status := 'E';
1507 RAISE FND_API.G_EXC_ERROR;
1508 END IF;
1509 -- End of changes
1510
1511 IF p_effective_from_date = TO_DATE( '01-01-1000', 'DD-MM-YYYY' )
1512 THEN
1513 l_effective_from_date := null;
1514 ELSE
1515 l_effective_from_date := p_effective_from_date;
1516 END IF;
1517
1518 IF p_effective_to_date = TO_DATE( '01-01-1000', 'DD-MM-YYYY' )
1519 THEN
1520 l_effective_to_date := null;
1521 ELSE
1522 l_effective_to_date := p_effective_to_date;
1523 END IF;
1524
1525 IF l_effective_from_date IS NOT NULL AND
1526 l_effective_to_date IS NOT NULL
1527 THEN
1528 --Check the start and end dates
1529 IF l_effective_from_date > l_effective_to_date
1530 THEN
1531 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1532 p_msg_name => 'PA_SETUP_CHK_ST_EN_DATE');
1533 x_msg_data := 'PA_SETUP_CHK_ST_EN_DATE';
1534 x_return_status := 'E';
1535 RAISE FND_API.G_EXC_ERROR;
1536 END IF;
1537 ELSIF l_effective_from_date IS NULL AND
1538 l_effective_to_date IS NOT NULL
1539 THEN
1540 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1541 p_msg_name => 'PA_SETUP_ST_DT_WO_EN_DT');
1542 x_msg_data := 'PA_SETUP_ST_DT_WO_EN_DT';
1543 x_return_status := 'E';
1544 RAISE FND_API.G_EXC_ERROR;
1545 END IF;
1546
1547 PA_PROJ_TEMPLATE_SETUP_UTILS.Get_Project_Type_Defaults(
1548 p_project_type => p_project_type
1549 ,x_Status_code => l_Status_code
1550 ,x_service_type_code => l_service_type_code
1551 ,x_cost_ind_rate_sch_id => l_cost_ind_rate_sch_id
1552 ,x_labor_sch_type => l_labor_sch_type
1553 ,x_labor_bill_rate_org_id => l_labor_bill_rate_org_id
1554 ,x_labor_std_bill_rate_schdl => l_labor_std_bill_rate_schdl
1555 ,x_non_labor_sch_type => l_non_labor_sch_type
1556 ,x_non_labor_bill_rate_org_id => l_non_labor_bill_rate_org_id
1557 ,x_nl_std_bill_rate_schdl => l_nl_std_bill_rate_schdl
1558 ,x_rev_ind_rate_sch_id => l_rev_ind_rate_sch_id
1559 ,x_inv_ind_rate_sch_id => l_inv_ind_rate_sch_id
1560 ,x_labor_invoice_format_id => l_labor_invoice_format_id
1561 ,x_non_labor_invoice_format_id => l_non_labor_invoice_format_id
1562 ,x_Burden_cost_flag => l_Burden_cost_flag
1563 ,x_interface_asset_cost_code => l_interface_asset_cost_code
1564 ,x_cost_sch_override_flag => l_cost_sch_override_flag
1565 ,x_billing_offset => l_billing_offset
1566 ,x_billing_cycle_id => l_billing_cycle_id
1567 ,x_cc_prvdr_flag => l_cc_prvdr_flag
1568 ,x_bill_job_group_id => l_bill_job_group_id
1569 ,x_cost_job_group_id => l_cost_job_group_id
1570 ,x_work_type_id => l_work_type_id
1571 ,x_role_list_id => l_role_list_id
1572 ,x_unassigned_time => l_unassigned_time
1573 ,x_emp_bill_rate_schedule_id => l_emp_bill_rate_schedule_id
1574 ,x_job_bill_rate_schedule_id => l_job_bill_rate_schedule_id
1575 ,x_budgetary_override_flag => l_budgetary_override_flag
1576 ,x_baseline_funding_flag => l_baseline_funding_flag
1577 ,x_non_lab_std_bill_rt_sch_id => l_non_lab_std_bill_rt_sch_id
1578 ,x_project_type_class_code => l_project_type_class_code
1579 -- anlee
1580 -- Changes for patchset K
1581 ,x_revaluate_funding_flag => l_revaluate_funding_flag
1582 ,x_include_gains_losses_flag => l_include_gains_losses_flag
1583 -- End of changes
1584 --PA L Changes 2872708
1585 ,x_asset_allocation_method => l_asset_allocation_method
1586 ,x_CAPITAL_EVENT_PROCESSING => l_CAPITAL_EVENT_PROCESSING
1587 ,x_CINT_RATE_SCH_ID => l_CINT_RATE_SCH_ID
1588 --PA L Changes 2872708
1589 --sunkalya.federal.Bug# Bug#5511353
1590 ,x_date_eff_funds_flag => l_date_eff_funds_consumption
1591 ,x_ar_rec_notify_flag => l_ar_rec_notify_flag -- 7508661 : EnC
1592 ,x_auto_release_pwp_inv => l_auto_release_pwp_inv -- 7508661 : EnC
1593 ,x_return_status => l_return_status
1594 ,x_error_msg_code => l_error_msg_code
1595 );
1596
1597 IF l_return_status = FND_API.G_RET_STS_ERROR
1598 THEN
1599 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1600 p_msg_name => l_error_msg_code);
1601 x_msg_data := l_error_msg_code;
1602 x_return_status := 'E';
1603 RAISE FND_API.G_EXC_ERROR;
1604 END IF;
1605
1606 --Validation from Projects form
1607 IF l_unassigned_time = 'N'
1608 THEN
1609 l_cc_process_labor_flag := 'N';
1610 l_cc_process_nl_flag := 'N';
1611 l_labor_tp_schedule_id := null;
1612 l_nl_tp_schedule_id := null;
1613 l_labor_tp_fixed_date := null;
1614 l_nl_tp_fixed_date := null;
1615 END IF;
1616
1617 IF l_project_type_class_code = 'CONTRACT'
1618 THEN
1619 OPEN cur_dist_rule;
1620 FETCH cur_dist_rule INTO l_distribution_rule;
1621 CLOSE cur_dist_rule;
1622 /* Pa_project_utils.check_dist_rule_chg_ok( p_project_id,
1623 l_old_distribution_rule,
1624 l_distribution_rule,
1625 x_err_code,
1626 x_err_stage,
1627 x_err_stack );
1628 IF If x_err_code != 0 Then
1629 THEN
1630 l_distribution_rule := l_old_distribution_rule;
1631 END IF;
1632 */ -- no need here it should done when the user changes distribution rule on UI.
1633 END IF;
1634
1635 IF l_old_project_type <> p_project_type AND p_project_type IS NOT NULL
1636 THEN
1637 /*OPEN cur_project_type_class;
1638 FETCH cur_project_type_class INTO l_project_type_class_code;
1639 CLOSE cur_project_type_class;*/
1640
1641 DELETE FROM PA_BUDGETARY_CONTROL_OPTIONS
1642 WHERE PROJECT_ID = P_PROJECT_ID;
1643
1644 IF l_project_type_class_code IS NOT NULL AND l_project_type_class_code <> 'CONTRACT'
1645 THEN
1646 IF pa_project_utils.check_proj_funding ( p_project_id ) <> 0
1647 THEN
1648 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1649 p_msg_name => 'PA_PR_CANT_CHG_DIR_TO_INDIR' );
1650 x_msg_data := 'PA_PR_CANT_CHG_DIR_TO_INDIR';
1651 x_return_status := 'E';
1652 RAISE FND_API.G_EXC_ERROR;
1653 END IF;
1654 l_labor_sch_type := null;
1655 l_non_labor_sch_type := null;
1656 END IF;
1657
1658 IF l_non_labor_sch_type = 'B' AND l_labor_sch_type = 'B'
1659 THEN
1660 l_rev_ind_rate_sch_id := null;
1661 l_inv_ind_rate_sch_id := null;
1662 l_Rev_ind_sch_fixed_date1 := null;
1663 l_Rev_ind_sch_fixed_date2 := null;
1664 l_Inv_ind_sch_fixed_date1 := null;
1665 l_Inv_ind_sch_fixed_date2 := null;
1666 END IF;
1667
1668 IF l_non_labor_sch_type = 'I'
1669 THEN
1670 l_nl_std_bill_rate_schdl := null;
1671 l_non_labor_bill_rate_org_id := null;
1672 END IF;
1673
1674 IF l_labor_sch_type = 'I'
1675 THEN
1676 l_emp_bill_rate_Schedule_id := null;
1677 l_job_bill_rate_Schedule_id := null;
1678 END IF;
1679
1680 /* pa_project_utils.change_pt_org_ok( x_project_id => p_project_id,
1681 x_err_code => l_err_code,
1682 x_err_stage => l_err_stage,
1683 x_err_stack => l_err_stack );
1684 IF l_err_code <> 0
1685 THEN
1686 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1687 p_msg_name => 'PA_PR_CANT_CHG_PROJ_TYPE' );
1688 x_msg_data := 'PA_PR_CANT_CHG_DIR_TO_INDIR';
1689 x_return_status := 'E';
1690 RAISE FND_API.G_EXC_ERROR;
1691 END IF;
1692 */ --this code is commented in forms PAXPREPR.fmb
1693 END IF;
1694
1695 --Location validation should be done if organization is changed.
1696 /* The following validation is not performed in forms during a project/template update
1697 So we dont need to do this here
1698 IF p_organization_id <> l_old_organization_id
1699 THEN
1700 --Organization Location Validations
1701 pa_location_utils.Get_ORG_Location_Details
1702 (p_organization_id => p_organization_id,
1703 x_country_name => l_country_name,
1704 x_city => l_city_name,
1705 x_region => l_region_name,
1706 x_country_code => l_country_code,
1707 x_return_status => l_return_status,
1708 x_error_message_code => l_error_message_code);
1709
1710 IF l_return_status = FND_API.G_RET_STS_ERROR
1711 THEN
1712 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1713 p_msg_name => l_error_message_code);
1714 x_msg_data := l_error_msg_code;
1715 x_return_status := 'E';
1716 RAISE FND_API.G_EXC_ERROR;
1717 END IF;
1718
1719 pa_location_utils.check_location_exists(
1720 p_country_code => l_country_code,
1721 p_city => l_city_name,
1722 p_region => l_region_name,
1723 x_return_status => l_return_status,
1724 x_location_id => l_location_id);
1725
1726 If l_location_id is null then
1727
1728 If l_city_name is not null
1729 and l_region_name is not null
1730 If l_country_code is not null then
1731
1732 pa_locations_pkg.INSERT_ROW(
1733 p_CITY => l_city_name,
1734 p_REGION => l_region_name,
1735 p_COUNTRY_CODE => l_country_code,
1736 p_CREATION_DATE => SYSDATE,
1737 p_CREATED_BY => FND_GLOBAL.USER_ID,
1738 p_LAST_UPDATE_DATE => SYSDATE,
1739 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1740 p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
1741 X_ROWID => x_rowid,
1742 X_LOCATION_ID => l_location_id);
1743
1744 end if;
1745 end if;
1746 END IF;
1747 */
1748
1749
1750 --Validdate attribute change from WHEN-VALIDATE-RECORD projects form
1751 --The following validation is done in forms when a template is created.
1752
1753 IF ( p_organization_id <> l_old_organization_id OR l_old_project_type <> p_project_type )
1754 THEN
1755 pa_project_utils2.validate_attribute_change
1756 ('ORGANIZATION_VALIDATION' -- X_context
1757 , 'INSERT' -- X_insert_update_mode
1758 , 'SELF_SERVICE' -- X_calling_module
1759 , p_project_id -- X_project_id
1760 , NULL -- X_task_id
1761 , l_old_organization_id -- X_old_value
1762 , p_organization_id -- X_new_value
1763 , p_project_type -- X_project_type
1764 , null -- x_start_date
1765 , null
1766 , l_public_sector_flag -- X_public_sector_flag
1767 , NULL -- X_task_manager_person_id
1768 , NULL -- X_service_type
1769 , NULL -- X_task_start_date
1770 , NULL -- X_task_end_date
1771 , FND_GLOBAL.USER_ID -- X_entered_by_user_id
1772 , null -- X_attribute_category
1773 , null -- X_attribute1
1774 , null -- X_attribute2
1775 , null -- X_attribute3
1776 , null -- X_attribute4
1777 , null -- X_attribute5
1778 , null -- X_attribute6
1779 , null -- X_attribute7
1780 , null -- X_attribute8
1781 , null -- X_attribute9
1782 , null -- X_attribute10
1783 , null -- X_pm_project_code
1784 , null -- X_pm_project_reference
1785 , NULL -- X_pm_task_reference
1786 , 'Y' -- X_functional_security_flag
1787 , l_warnings_only_flag --bug3134205
1788 , x_err_code -- X_err_code
1789 , x_err_stage -- X_err_stage
1790 , x_err_stack); -- X_err_stack
1791
1792 /* Commented the <> 15 condition for bug 2981386 */
1793 if x_err_code <> 0 /* and x_err_code <> 15 */ Then --modified for bug 2393975
1794
1795 if l_err_stage = 'PA_INVALID_PT_CLASS_ORG' then
1796
1797 select meaning
1798 into l_project_type_class_code
1799 from pa_project_types pt
1800 , pa_lookups lps
1801 where pt.project_type = p_project_type
1802 and lps.lookup_type(+) = 'PROJECT TYPE CLASS'
1803 and lps.lookup_code(+) = pt.project_type_class_code;
1804
1805 /* Code addition for bug 2981386 starts */
1806 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1807 p_msg_name => 'PA_INVALID_PT_CLASS_ORG',
1808 p_token1 => 'PT_CLASS',
1809 p_value1 => l_project_type_class_code);
1810 else
1811 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1812 p_msg_name => x_err_stage);
1813
1814 end if;
1815 /* Code addition for bug 2981386 ends */
1816
1817 /* Commented for bug 2981386
1818 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1819 p_msg_name => x_err_stage,
1820 p_token1 => 'PT_CLASS',
1821 p_value1 => l_project_type_class_code );
1822 */
1823 x_msg_data := x_err_stage;
1824 x_return_status := 'E';
1825 RAISE FND_API.G_EXC_ERROR;
1826 End If;
1827 END IF; --validate attribute change
1828
1829 --maansari bug 3068806
1830 IF p_project_type IS NOT NULL AND
1831 p_project_type <> l_old_project_type AND
1832 l_cc_prvdr_flag = 'Y' AND
1833 l_multi_currency_billing_flag2 = 'Y'
1834 THEN
1835 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1836 p_msg_name => 'PA_PR_CANT_CHG_IC_BIL_PT');
1837 x_msg_data := 'PA_PR_CANT_CHG_IC_BIL_PT';
1838 x_return_status := 'E';
1839 RAISE FND_API.G_EXC_ERROR;
1840 END IF;
1841 --end maansari bug 3068806
1842
1843 --bug 3068781
1844
1845 DECLARE
1846 CURSOR cur_job_cur IS SELECT rate_sch_currency_code FROM pa_std_bill_rate_schedules_all
1847 WHERE bill_rate_sch_id = l_job_bill_rate_schedule_id ;
1848
1849 CURSOR cur_emp_cur IS SELECT rate_sch_currency_code FROM pa_std_bill_rate_schedules_all
1850 WHERE bill_rate_sch_id = l_emp_bill_rate_schedule_id ;
1851
1852 CURSOR cur_nl_cur IS SELECT rate_sch_currency_code FROM pa_std_bill_rate_schedules_all
1853 WHERE bill_rate_sch_id = l_non_lab_std_bill_rt_sch_id ;
1854
1855 CURSOR cur_impl IS SELECT default_rate_type FROM pa_implementations;
1856
1857 x_job_rate_sch_currency VARCHAR2(30);
1858 x_emp_rate_sch_currency VARCHAR2(30);
1859 x_nl_rate_sch_currency VARCHAR2(30);
1860 x_default_rate_type VARCHAR2(30);
1861
1862 BEGIN
1863
1864 IF l_cc_prvdr_flag = 'N' --This is not required if the project type is IC billing. bug 2179904
1865 THEN
1866 OPEN cur_job_cur;
1867 FETCH cur_job_cur INTO x_job_rate_sch_currency ;
1868 CLOSE cur_job_cur;
1869
1870 OPEN cur_emp_cur;
1871 FETCH cur_emp_cur INTO x_emp_rate_sch_currency ;
1872 CLOSE cur_emp_cur;
1873
1874 OPEN cur_nl_cur;
1875 FETCH cur_nl_cur INTO x_nl_rate_sch_currency ;
1876 CLOSE cur_nl_cur;
1877
1878 IF x_job_rate_sch_currency is not Null and
1879 x_job_rate_sch_currency <> l_projfunc_currency_code
1880 THEN
1881 l_multi_currency_billing_flag := 'Y';
1882 ELSIF x_emp_rate_sch_currency is not Null and
1883 x_emp_rate_sch_currency <> l_projfunc_currency_code
1884 THEN
1885 l_multi_currency_billing_flag := 'Y';
1886 ELSIF x_nl_rate_sch_currency is not Null and
1887 x_nl_rate_sch_currency <> l_projfunc_currency_code
1888 THEN
1889 l_multi_currency_billing_flag := 'Y';
1890 END IF;
1891
1892 END IF;
1893
1894 IF l_cc_prvdr_flag = 'N' AND
1895 l_multi_currency_billing_flag2 = 'N' AND
1896 NVL( l_multi_currency_billing_flag, 'N') = 'Y'
1897 THEN
1898 OPEN cur_impl;
1899 FETCH cur_impl INTO x_default_rate_type ;
1900 CLOSE cur_impl;
1901
1902 l_PROJFUNC_BIL_RATE_TYPE := x_default_rate_type;
1903 l_PROJECT_BIL_RATE_TYPE := x_default_rate_type;
1904 l_FUNDING_RATE_TYPE := x_default_rate_type;
1905
1906 l_PROJFUNC_BIL_RATE_DATE_CODE := 'PA_INVOICE_DATE';
1907 l_PROJECT_BIL_RATE_DATE_CODE := 'PA_INVOICE_DATE';
1908 l_FUNDING_RATE_DATE_CODE := 'PA_INVOICE_DATE';
1909 l_BTC_COST_BASE_REV_CODE := 'EXP_TRANS_CURR';
1910
1911 UPDATE pa_project_customers
1912 SET inv_rate_type = x_default_rate_type
1913 WHERE project_id = p_project_id;
1914 ELSE
1915 l_PROJFUNC_BIL_RATE_TYPE := l_PROJFUNC_BIL_RATE_TYPE;
1916 l_PROJECT_BIL_RATE_TYPE := l_PROJECT_BIL_RATE_TYPE;
1917 l_FUNDING_RATE_TYPE := l_FUNDING_RATE_TYPE;
1918
1919 l_PROJFUNC_BIL_RATE_DATE_CODE := l_PROJFUNC_BIL_RATE_DATE_CODE;
1920 l_PROJECT_BIL_RATE_DATE_CODE := l_PROJECT_BIL_RATE_DATE_CODE;
1921 l_FUNDING_RATE_DATE_CODE := l_FUNDING_RATE_DATE_CODE;
1922 l_BTC_COST_BASE_REV_CODE := l_BTC_COST_BASE_REV_CODE;
1923
1924 END IF;
1925 END;
1926 --end bug 3068781
1927
1928
1929
1930 --I cant use table handler for update; It may update the unwanted columns as well wih null
1931 UPDATE pa_projects_all
1932 SET SEGMENT1 = p_project_number,
1933 NAME = p_project_name,
1934 description = p_description,
1935 PROJECT_TYPE = p_project_type,
1936 carrying_out_organization_id = p_organization_id,
1937 TEMPLATE_START_DATE_ACTIVE = l_effective_from_date,
1938 TEMPLATE_END_DATE_ACTIVE = l_effective_to_date,
1939 -- not done in project forms PROJECT_STATUS_CODE = l_Status_code,
1940 LABOR_INVOICE_FORMAT_ID = l_labor_invoice_format_id,
1941 NON_LABOR_INVOICE_FORMAT_ID = l_non_labor_invoice_format_id,
1942 BILLING_OFFSET = l_billing_offset,
1943 NON_LABOR_STD_BILL_RATE_SCHDL = l_nl_std_bill_rate_schdl,
1944 NON_LABOR_BILL_RATE_ORG_ID = l_non_labor_bill_rate_org_id,
1945 Non_Labor_Schedule_Fixed_Date = DECODE( l_non_labor_sch_type, 'I', null, Non_Labor_Schedule_Fixed_Date ),
1946 Non_Labor_Schedule_Discount = DECODE( l_non_labor_sch_type, 'I', null, Non_Labor_Schedule_Discount ),
1947 COST_IND_RATE_SCH_ID = l_cost_ind_rate_sch_id,
1948 REV_IND_RATE_SCH_ID = l_rev_ind_rate_sch_id,
1949 REV_IND_SCH_FIXED_DATE = l_REV_IND_SCH_fixed_date1,
1950 INV_IND_RATE_SCH_ID = l_inv_ind_rate_sch_id,
1951 INV_IND_SCH_FIXED_DATE = l_INV_IND_SCH_FIXED_date1,
1952 LABOR_SCH_TYPE = l_labor_sch_type,
1953 NON_LABOR_SCH_TYPE = l_non_labor_sch_type,
1954 BILLING_CYCLE_ID = l_billing_cycle_id,
1955 BILL_JOB_GROUP_ID = l_bill_job_group_id,
1956 COST_JOB_GROUP_ID = l_cost_job_group_id,
1957 ROLE_LIST_ID = l_role_list_id,
1958 WORK_TYPE_ID = l_work_type_id,
1959 JOB_BILL_RATE_SCHEDULE_ID = l_job_bill_rate_schedule_id,
1960 EMP_BILL_RATE_SCHEDULE_ID = l_emp_bill_rate_schedule_id,
1961 labor_schedule_fixed_date = DECODE( l_labor_sch_type, 'I', null, labor_schedule_fixed_date ),
1962 labor_schedule_discount = DECODE( l_labor_sch_type, 'I', null, labor_schedule_discount ),
1963 non_lab_std_bill_rt_sch_id = l_non_lab_std_bill_rt_sch_id,
1964
1965 labor_std_bill_rate_schdl = l_labor_std_bill_rate_schdl,
1966 labor_bill_rate_org_id = l_labor_bill_rate_org_id,
1967 cc_process_labor_flag = l_cc_process_labor_flag,
1968 cc_process_nl_flag = l_cc_process_nl_flag,
1969 labor_tp_schedule_id = l_labor_tp_schedule_id ,
1970 nl_tp_schedule_id = l_nl_tp_schedule_id ,
1971 labor_tp_fixed_date = l_labor_tp_fixed_date ,
1972 nl_tp_fixed_date = l_nl_tp_fixed_date ,
1973 location_id = l_location_id,
1974 distribution_rule = l_distribution_rule,
1975 -- anlee
1976 -- patchset K changes
1977 revaluate_funding_flag = l_revaluate_funding_flag,
1978 include_gains_losses_flag = l_include_gains_losses_flag,
1979 -- End of changes
1980 --PA K Project Access Changes
1981 security_level = p_security_level,
1982 -- anlee
1983 -- Project Long Name changes
1984 long_name = p_long_name,
1985 -- End of changes
1986 --bug 3068781
1987 multi_currency_billing_flag = NVL( l_multi_currency_billing_flag,l_multi_currency_billing_flag2 ),
1988 PROJFUNC_BIL_RATE_TYPE = l_PROJFUNC_BIL_RATE_TYPE,
1989 PROJECT_BIL_RATE_TYPE = l_PROJECT_BIL_RATE_TYPE,
1990 FUNDING_RATE_TYPE = l_FUNDING_RATE_TYPE,
1991 PROJFUNC_BIL_RATE_DATE_CODE = l_PROJFUNC_BIL_RATE_DATE_CODE,
1992 PROJECT_BIL_RATE_DATE_CODE = l_PROJECT_BIL_RATE_DATE_CODE,
1993 FUNDING_RATE_DATE_CODE = l_FUNDING_RATE_DATE_CODE,
1994 BTC_COST_BASE_REV_CODE = l_BTC_COST_BASE_REV_CODE,
1995 --bug 3068781
1996 --
1997 --PA L 2872708
1998 asset_allocation_method = l_asset_allocation_method,
1999 CAPITAL_EVENT_PROCESSING = l_CAPITAL_EVENT_PROCESSING,
2000 CINT_RATE_SCH_ID = l_CINT_RATE_SCH_ID,
2001 --PA L 2872708
2002 record_version_number = NVL( record_version_number, 1 ) + 1,
2003 --sunkalya.federal changes. Bug#5511353
2004 date_eff_funds_consumption = nvl(l_date_eff_funds_consumption,'N')
2005 --sunkalya.federal changes. Bug#5511353
2006
2007 WHERE project_id = p_project_id;
2008
2009 --Sunkalya federal changes. Bug#5511353
2010
2011 IF ( l_old_project_type <> p_project_type ) THEN
2012
2013 DECLARE
2014
2015 hghst_ctr_cust_id NUMBER;
2016 l_return_status VARCHAR2(10);
2017 l_msg_count NUMBER := 0;
2018 l_msg_data VARCHAR2(2000);
2019
2020 BEGIN
2021
2022 IF l_date_eff_funds_consumption ='Y' THEN
2023
2024 UPDATE pa_project_customers
2025 SET
2026 CUSTOMER_BILL_SPLIT = NULL
2027 WHERE
2028 PROJECT_ID = p_project_id;
2029
2030
2031 ELSIF l_old_funds_flag = 'Y' AND l_old_top_task_flag = 'N' THEN
2032
2033
2034 --This api will determine which customer to be made as 100% contributor.
2035 PA_CUSTOMERS_CONTACTS_UTILS.Get_Highest_Contr_Fed_Cust(
2036 P_API_VERSION => 1.0
2037 , P_INIT_MSG_LIST => 'T'
2038 , P_COMMIT => 'F'
2039 , P_VALIDATE_ONLY => 'F'
2040 , P_VALIDATION_LEVEL => 100
2041 , P_DEBUG_MODE => 'N'
2042 , p_calling_module => 'AMG'
2043 , p_project_id => p_project_id
2044 , x_highst_contr_cust_id => hghst_ctr_cust_id
2045 , x_return_status => l_return_status
2046 , x_msg_count => l_msg_count
2047 , x_msg_data => l_msg_data );
2048
2049 IF hghst_ctr_cust_id IS NOT NULL AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2050
2051 UPDATE pa_project_customers SET customer_bill_split = 100
2052 WHERE customer_id = hghst_ctr_cust_id AND project_id = p_project_id;
2053
2054 UPDATE pa_project_customers SET customer_bill_split = 0
2055 WHERE customer_id <> hghst_ctr_cust_id AND project_id = p_project_id;
2056 END IF;
2057 END IF;
2058
2059
2060 END;
2061
2062 END IF;
2063
2064 --Federal changes by sunkalya.Bug#5511353.
2065
2066 IF ( l_old_project_type <> p_project_type )
2067 THEN
2068
2069 OPEN cur_bill_flag( l_work_type_id );
2070 FETCH cur_bill_flag INTO l_billable_flag;
2071 CLOSE cur_bill_flag;
2072
2073 UPDATE pa_tasks
2074 SET work_type_id = l_work_type_id,
2075 billable_flag = l_billable_flag,
2076 emp_bill_rate_schedule_id = l_emp_bill_rate_schedule_id,
2077 job_bill_rate_schedule_id = l_job_bill_rate_schedule_id,
2078 labor_schedule_fixed_date = DECODE( l_labor_sch_type, 'I', null, labor_schedule_fixed_date ),
2079 labor_schedule_discount = DECODE( l_labor_sch_type, 'I', null, labor_schedule_discount ),
2080
2081 --bug 2101726
2082 labor_sch_type = l_labor_sch_type,
2083 service_type_code = l_service_type_code,
2084 cost_ind_rate_sch_id = l_cost_ind_rate_sch_id,
2085 non_labor_sch_type = l_non_labor_sch_type,
2086 non_labor_bill_rate_org_id = l_non_labor_bill_rate_org_id,
2087 non_labor_std_bill_rate_schdl = l_nl_std_bill_rate_schdl,
2088 Non_Labor_Schedule_Fixed_Date = DECODE( l_non_labor_sch_type, 'I', null, Non_Labor_Schedule_Fixed_Date ),
2089 Non_Labor_Schedule_Discount = DECODE( l_non_labor_sch_type, 'I', null, Non_Labor_Schedule_Discount ),
2090 rev_ind_rate_sch_id = l_rev_ind_rate_sch_id,
2091 REV_IND_SCH_FIXED_DATE = l_REV_IND_SCH_fixed_date2,
2092 INV_IND_SCH_FIXED_DATE = l_INV_IND_SCH_FIXED_date2,
2093 inv_ind_rate_sch_id = l_inv_ind_rate_sch_id,
2094 labor_bill_rate_org_id = l_labor_bill_rate_org_id,
2095 labor_std_bill_rate_schdl = l_labor_std_bill_rate_schdl,
2096 non_lab_std_bill_rt_sch_id = l_non_lab_std_bill_rt_sch_id
2097
2098 WHERE project_id = p_project_id;
2099
2100 END IF;
2101
2102 x_return_status := FND_API.G_RET_STS_SUCCESS;
2103
2104 IF (p_commit = FND_API.G_TRUE) THEN
2105 COMMIT;
2106 END IF;
2107
2108 IF (p_debug_mode = 'Y') THEN
2109 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Update_Project_Template END');
2110 END IF;
2111 EXCEPTION
2112 when FND_API.G_EXC_ERROR then
2113 if p_commit = FND_API.G_TRUE then
2114 rollback to Update_Project_Template;
2115 end if;
2116 x_return_status := FND_API.G_RET_STS_ERROR;
2117 when FND_API.G_EXC_UNEXPECTED_ERROR then
2118 if p_commit = FND_API.G_TRUE then
2119 rollback to Update_Project_Template;
2120 end if;
2121 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2122 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
2123 p_procedure_name => 'Update_Project_Template',
2124 p_error_text => SUBSTRB(SQLERRM,1,240));
2125 when OTHERS then
2126 if p_commit = FND_API.G_TRUE then
2127 rollback to Update_Project_Template;
2128 end if;
2129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2130 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
2131 p_procedure_name => 'Update_Project_Template',
2132 p_error_text => SUBSTRB(SQLERRM,1,240));
2133 raise;
2134 END Update_Project_Template;
2135
2136 -- API name : Delete_Project_Template
2137 -- Type : Public API
2138 -- Pre-reqs : None
2139 -- Return Value :
2140 --
2141 -- Parameters
2142 -- p_project_id IN NUMBER,
2143 -- p_record_version_number IN NUMBER,
2144 --
2145
2146 PROCEDURE Delete_Project_Template(
2147 p_api_version IN NUMBER :=1.0,
2148 p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE,
2149 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
2150 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE,
2151 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
2152 p_calling_module IN VARCHAR2 :='SELF_SERVICE',
2153 p_debug_mode IN VARCHAR2 :='N',
2154 p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2155 p_project_id IN NUMBER,
2156 p_record_version_number IN NUMBER,
2157 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2158 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2159 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2160 ) IS
2161 l_api_name CONSTANT VARCHAR(30) := 'Delete_Project_Template';
2162 l_api_version CONSTANT NUMBER := 1.0;
2163
2164 l_return_status VARCHAR2(1);
2165 l_msg_count NUMBER;
2166 l_msg_data VARCHAR2(250);
2167 l_data VARCHAR2(250);
2168 l_msg_index_out NUMBER;
2169 l_error_msg_code VARCHAR2(250);
2170
2171 l_err_code NUMBER;
2172 l_err_stage VARCHAR2(250);
2173 l_err_stack VARCHAR2(250);
2174
2175
2176 BEGIN
2177 pa_debug.init_err_stack ('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Project_Template');
2178
2179 IF (p_debug_mode = 'Y') THEN
2180 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Project_Template begin');
2181 END IF;
2182
2183 IF (p_commit = FND_API.G_TRUE) THEN
2184 savepoint Delete_Project_Template;
2185 END IF;
2186
2187 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2189 END IF;
2190
2191 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2192 FND_MSG_PUB.initialize;
2193 END IF;
2194
2195 --Bug 2947492: The following api call is modified to pass parameters by notation.
2196 Pa_Project_Core.Delete_Project (
2197 x_project_id => p_Project_id,
2198 x_err_code => l_err_code ,
2199 x_err_stage => l_err_stage,
2200 x_err_stack => l_err_stack);
2201
2202 IF l_err_code <> 0
2203 THEN
2204 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2205 p_msg_name => l_err_stage);
2206 x_msg_data := l_err_stage;
2207 x_return_status := 'E';
2208 RAISE FND_API.G_EXC_ERROR;
2209 END IF;
2210
2211 x_return_status := FND_API.G_RET_STS_SUCCESS;
2212
2213 IF (p_commit = FND_API.G_TRUE) THEN
2214 COMMIT;
2215 END IF;
2216
2217 IF (p_debug_mode = 'Y') THEN
2218 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Project_Template END');
2219 END IF;
2220 EXCEPTION
2221 when FND_API.G_EXC_ERROR then
2222 if p_commit = FND_API.G_TRUE then
2223 rollback to Delete_Project_Template;
2224 end if;
2225 x_return_status := FND_API.G_RET_STS_ERROR;
2226 when FND_API.G_EXC_UNEXPECTED_ERROR then
2227 if p_commit = FND_API.G_TRUE then
2228 rollback to Delete_Project_Template;
2229 end if;
2230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2231 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
2232 p_procedure_name => 'Delete_Project_Template',
2233 p_error_text => SUBSTRB(SQLERRM,1,240));
2234 when OTHERS then
2235 if p_commit = FND_API.G_TRUE then
2236 rollback to Delete_Project_Template;
2237 end if;
2238 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2239 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
2240 p_procedure_name => 'Delete_Project_Template',
2241 p_error_text => SUBSTRB(SQLERRM,1,240));
2242 raise;
2243 END Delete_Project_Template;
2244
2245 -- API name : Add_Project_Options
2246 -- Type : Public API
2247 -- Pre-reqs : None
2248 -- Return Value :
2249 --
2250 -- Parameters
2251 -- p_project_id IN NUMBER,
2252 -- p_option_copde IN VARCHAR2,
2253 --
2254
2255 PROCEDURE Add_Project_Options(
2256 p_api_version IN NUMBER :=1.0,
2257 p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE,
2258 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
2259 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE,
2260 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
2261 p_calling_module IN VARCHAR2 :='SELF_SERVICE',
2262 p_debug_mode IN VARCHAR2 :='N',
2263 p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2264 p_project_id IN NUMBER,
2265 p_option_code IN VARCHAR2,
2266 p_action IN VARCHAR2 := 'ENABLE',
2267 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2268 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2269 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2270 ) IS
2271 l_api_name CONSTANT VARCHAR(30) := 'Add_Project_Options';
2272 l_api_version CONSTANT NUMBER := 1.0;
2273
2274 l_return_status VARCHAR2(1);
2275 l_msg_count NUMBER;
2276 l_msg_data VARCHAR2(250);
2277 l_data VARCHAR2(250);
2278 l_msg_index_out NUMBER;
2279 l_error_msg_code VARCHAR2(250);
2280
2281 l_err_code NUMBER;
2282 l_err_stage VARCHAR2(250);
2283 l_err_stack VARCHAR2(250);
2284 l_dummy_char VARCHAR2(1);
2285
2286
2287 CURSOR cur_chk_options
2288 IS
2289 SELECT 'x'
2290 FROM pa_project_options
2291 WHERE project_id = p_project_id
2292 AND option_code = p_option_code;
2293 BEGIN
2294 pa_debug.init_err_stack ('PA_PROJ_TEMPLATE_SETUP_PVT.Add_Project_Options');
2295
2296 IF (p_debug_mode = 'Y') THEN
2297 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Add_Project_Options begin');
2298 END IF;
2299
2300 IF (p_commit = FND_API.G_TRUE) THEN
2301 savepoint Add_Project_Options;
2302 END IF;
2303
2304 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2305 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2306 END IF;
2307
2308 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2309 FND_MSG_PUB.initialize;
2310 END IF;
2311
2312 OPEN cur_chk_options;
2313 FETCH cur_chk_options INTO l_dummy_char;
2314 IF cur_chk_options%NOTFOUND
2315 THEN
2316 INSERT INTO pa_project_options
2317 (
2318 project_id,
2319 option_code,
2320 last_update_date,
2321 last_updated_by,
2322 creation_date,
2323 created_by,
2324 last_update_login,
2325 record_version_number
2326 )
2327 VALUES ( p_project_id,
2328 p_option_code,
2329 SYSDATE,
2330 FND_GLOBAL.USER_ID,
2331 SYSDATE,
2332 FND_GLOBAL.USER_ID,
2333 FND_GLOBAL.LOGIN_ID ,
2334 1
2335 );
2336 END IF;
2337 CLOSE cur_chk_options;
2338
2339 IF p_action = 'ENABLE'
2340 THEN
2341 enable_disbale_proj_opt( p_project_id, p_option_code, p_action );
2342 END IF;
2343
2344 x_return_status := FND_API.G_RET_STS_SUCCESS;
2345
2346 IF (p_commit = FND_API.G_TRUE) THEN
2347 COMMIT;
2348 END IF;
2349
2350 IF (p_debug_mode = 'Y') THEN
2351 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Add_Project_Options END');
2352 END IF;
2353 EXCEPTION
2354 when FND_API.G_EXC_ERROR then
2355 if p_commit = FND_API.G_TRUE then
2356 rollback to Add_Project_Options;
2357 end if;
2358 x_return_status := FND_API.G_RET_STS_ERROR;
2359 when FND_API.G_EXC_UNEXPECTED_ERROR then
2360 if p_commit = FND_API.G_TRUE then
2361 rollback to Add_Project_Options;
2362 end if;
2363 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2364 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
2365 p_procedure_name => 'Add_Project_Options',
2366 p_error_text => SUBSTRB(SQLERRM,1,240));
2367 when OTHERS then
2368 if p_commit = FND_API.G_TRUE then
2369 rollback to Add_Project_Options;
2370 end if;
2371 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2372 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
2373 p_procedure_name => 'Add_Project_Options',
2374 p_error_text => SUBSTRB(SQLERRM,1,240));
2375 raise;
2376 END Add_Project_Options;
2377
2378
2379 -- API name : Delete_Project_Options
2380 -- Type : Public API
2381 -- Pre-reqs : None
2382 -- Return Value :
2383 --
2384 -- Parameters
2385 -- p_project_id IN NUMBER,
2386 -- p_option_copde IN VARCHAR2,
2387 --
2388
2389 PROCEDURE Delete_Project_Options(
2390 p_api_version IN NUMBER :=1.0,
2391 p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE,
2392 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
2393 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE,
2394 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
2395 p_calling_module IN VARCHAR2 :='SELF_SERVICE',
2396 p_debug_mode IN VARCHAR2 :='N',
2397 p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2398 p_project_id IN NUMBER,
2399 p_option_code IN VARCHAR2,
2400 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2401 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2402 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2403 ) IS
2404 l_api_name CONSTANT VARCHAR(30) := 'Delete_Project_Options';
2405 l_api_version CONSTANT NUMBER := 1.0;
2406
2407 l_return_status VARCHAR2(1);
2408 l_msg_count NUMBER;
2409 l_msg_data VARCHAR2(250);
2410 l_data VARCHAR2(250);
2411 l_msg_index_out NUMBER;
2412 l_error_msg_code VARCHAR2(250);
2413
2414 l_err_code NUMBER;
2415 l_err_stage VARCHAR2(250);
2416 l_err_stack VARCHAR2(250);
2417
2418 l_option_name VARCHAR2(80);
2419
2420 CURSOR cur_option_name
2421 IS
2422 SELECT meaning
2423 FROM fnd_lookup_values
2424 WHERE lookup_type = 'PA_OPTIONS_SS'
2425 AND lookup_code = p_option_code
2426 AND language = userenv('LANG'); -- Bug 5643345: Added the environment language condition.
2427 -- anlee
2428 -- Added for PA_OPTIONS enhancements
2429 CURSOR get_allow_ovr_enabled
2430 IS
2431 SELECT allow_override_enabled_flag
2432 FROM PA_OPTIONS
2433 WHERE option_code = p_option_code;
2434
2435 l_allow_ovr_enabled VARCHAR2(1);
2436 -- End of changes
2437 BEGIN
2438 pa_debug.init_err_stack ('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Project_Options');
2439
2440 IF (p_debug_mode = 'Y') THEN
2441 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Project_Options begin');
2442 END IF;
2443
2444 IF (p_commit = FND_API.G_TRUE) THEN
2445 savepoint Delete_Project_Options;
2446 END IF;
2447
2448 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2449 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2450 END IF;
2451
2452 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2453 FND_MSG_PUB.initialize;
2454 END IF;
2455
2456 --bug
2457
2458 -- anlee
2459 -- Changes for PA_OPTIONS enhancements
2460
2461 OPEN get_allow_ovr_enabled;
2462 FETCH get_allow_ovr_enabled INTO l_allow_ovr_enabled;
2463 CLOSE get_allow_ovr_enabled;
2464
2465 -- IF PA_PROJ_TEMPLATE_SETUP_UTILS.Header_Option( p_option_code ) = 'Y' OR
2466 -- p_option_code = 'BASIC_INFO_SS' --only mandatory option
2467
2468 IF l_allow_ovr_enabled <> 'Y'
2469 -- End of changes
2470 THEN
2471 -- Changes for Bug 5643345
2472
2473 OPEN cur_option_name;
2474 FETCH cur_option_name INTO l_option_name;
2475 CLOSE cur_option_name;
2476
2477
2478 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2479 p_msg_name => 'PA_PROJ_CANT_DISBL_OPTN',
2480 p_token1 => 'OPTION_NAME',
2481 p_value1 => l_option_name
2482 );
2483
2484 /*PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2485 p_msg_name => 'PA_ALL_NO_UPDATE_RECORD');*/
2486 x_msg_data := 'PA_PROJ_CANT_DISBL_OPTN';
2487 -- End of changes for Bug 5643345
2488 x_return_status := 'E';
2489 RAISE FND_API.G_EXC_ERROR;
2490 END IF;
2491
2492 DELETE FROM pa_project_options WHERE project_id = p_project_id AND option_code = p_option_code;
2493
2494 enable_disbale_proj_opt( p_project_id, p_option_code, 'DISABLE' );
2495
2496 x_return_status := FND_API.G_RET_STS_SUCCESS;
2497
2498 IF (p_commit = FND_API.G_TRUE) THEN
2499 COMMIT;
2500 END IF;
2501
2502 IF (p_debug_mode = 'Y') THEN
2503 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Project_Options END');
2504 END IF;
2505 EXCEPTION
2506 when FND_API.G_EXC_ERROR then
2507 if p_commit = FND_API.G_TRUE then
2508 rollback to Delete_Project_Options;
2509 end if;
2510 x_return_status := FND_API.G_RET_STS_ERROR;
2511 when FND_API.G_EXC_UNEXPECTED_ERROR then
2512 if p_commit = FND_API.G_TRUE then
2513 rollback to Delete_Project_Options;
2514 end if;
2515 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2516 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
2517 p_procedure_name => 'Delete_Project_Options',
2518 p_error_text => SUBSTRB(SQLERRM,1,240));
2519 when OTHERS then
2520 if p_commit = FND_API.G_TRUE then
2521 rollback to Delete_Project_Options;
2522 end if;
2523 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2524 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
2525 p_procedure_name => 'Delete_Project_Options',
2526 p_error_text => SUBSTRB(SQLERRM,1,240));
2527 raise;
2528 END Delete_Project_Options;
2529
2530
2531 -- API name : Add_Quick_Entry_Field
2532 -- Type : Public API
2533 -- Pre-reqs : None
2534 -- Return Value :
2535 --
2536 -- Parameters
2537 -- p_project_id IN NUMBER ,
2538 -- p_sort_order IN NUMBER ,
2539 -- p_field_name IN VARCHAR2 := 'ABCD',
2540 -- p_limiting_value IN VARCHAR2 := 'ABCD',
2541 -- p_prompt IN VARCHAR2 ,
2542 -- p_required_flag IN VARCHAR2 := 'N',--
2543
2544 PROCEDURE Add_Quick_Entry_Field(
2545 p_api_version IN NUMBER :=1.0,
2546 p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE,
2547 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
2548 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE,
2549 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
2550 p_calling_module IN VARCHAR2 :='SELF_SERVICE',
2551 p_debug_mode IN VARCHAR2 :='N',
2552 p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2553 p_project_id IN NUMBER ,
2554 p_sort_order IN NUMBER ,
2555 p_field_name IN VARCHAR2 := 'JUNK_CHARS',
2556 p_limiting_value IN VARCHAR2 := 'JUNK_CHARS',
2557 p_prompt IN VARCHAR2 ,
2558 p_required_flag IN VARCHAR2 := 'N',
2559 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2560 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2561 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2562 ) IS
2563 l_api_name CONSTANT VARCHAR(30) := 'Add_Quick_Entry_Field';
2564 l_api_version CONSTANT NUMBER := 1.0;
2565
2566 l_return_status VARCHAR2(1);
2567 l_msg_count NUMBER;
2568 l_msg_data VARCHAR2(250);
2569 l_data VARCHAR2(250);
2570 l_msg_index_out NUMBER;
2571 l_error_msg_code VARCHAR2(250);
2572
2573 l_err_code NUMBER;
2574 l_err_stage VARCHAR2(250);
2575 l_err_stack VARCHAR2(250);
2576 l_dummy_char VARCHAR2(1);
2577 l_field_name_meaning VARCHAR2(250);
2578
2579 CURSOR cur_overrides
2580 IS
2581 SELECT 'x'
2582 FROM pa_project_copy_overrides
2583 WHERE project_id = p_project_id
2584 AND field_name = p_field_name
2585 AND sort_order = p_sort_order;
2586
2587 CURSOR cur_chk_sort_order
2588 IS
2589 SELECT 'x'
2590 FROM pa_project_copy_overrides
2591 WHERE project_id = p_project_id
2592 AND sort_order = p_sort_order;
2593
2594 CURSOR cur_dup_quick_entry
2595 IS
2596 SELECT 'X'
2597 FROM pa_project_copy_overrides
2598 WHERE project_id = p_project_id
2599 AND field_name = p_field_name;
2600
2601
2602 BEGIN
2603 pa_debug.init_err_stack ('PA_PROJ_TEMPLATE_SETUP_PVT.Add_Quick_Entry_Field');
2604
2605 IF (p_debug_mode = 'Y') THEN
2606 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Add_Quick_Entry_Field begin');
2607 END IF;
2608
2609 IF (p_commit = FND_API.G_TRUE) THEN
2610 savepoint Add_Quick_Entry_Field;
2611 END IF;
2612
2613 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2614 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2615 END IF;
2616
2617 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2618 FND_MSG_PUB.initialize;
2619 END IF;
2620
2621 --check sort order unique
2622 OPEN cur_chk_sort_order;
2623 FETCH cur_chk_sort_order INTO l_dummy_char;
2624 IF cur_chk_sort_order%FOUND
2625 THEN
2626 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2627 p_msg_name => 'PA_SETUP_SORT_ORDER_NOT_UNIQ' );
2628 x_msg_data := 'PA_SETUP_SORT_ORDER_NOT_UNIQ';
2629 x_return_status := 'E';
2630 CLOSE cur_chk_sort_order;
2631 RAISE FND_API.G_EXC_ERROR;
2632 ELSE
2633 CLOSE cur_chk_sort_order;
2634 END IF;
2635
2636 --Duplicate quick entry check.
2637 --Removed the CUSTOMER_NAME from the below if for bug 3619423
2638 IF p_field_name NOT IN ( 'KEY_MEMBER', 'CLASSIFICATION', 'ORG_ROLE' )
2639 THEN
2640 --check duplicate quick entry.
2641 OPEN cur_dup_quick_entry;
2642 FETCH cur_dup_quick_entry INTO l_dummy_char;
2643 IF cur_dup_quick_entry%FOUND
2644 THEN
2645 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2646 p_msg_name => 'PA_SETUP_DUP_QUICK_ENTRY' );
2647 x_msg_data := 'PA_SETUP_DUP_QUICK_ENTRY';
2648 x_return_status := 'E';
2649 CLOSE cur_dup_quick_entry;
2650 RAISE FND_API.G_EXC_ERROR;
2651 ELSE
2652 CLOSE cur_dup_quick_entry;
2653 END IF;
2654 /* Bug 4139681 - Replaced the ELSE with the new IF condition so that checks for specifications are done for
2655 field CUSTOMER_NAME as well.
2656 */
2657 --ELSE
2658 END IF; --Bug 4139681
2659 IF p_field_name IN ( 'KEY_MEMBER', 'CLASSIFICATION', 'ORG_ROLE', 'CUSTOMER_NAME' ) THEN--Bug 4139681
2660
2661 IF p_limiting_value IS NULL OR p_limiting_value = 'JUNK_CHARS'
2662 THEN
2663 BEGIN
2664 SELECT meaning
2665 INTO l_field_name_meaning
2666 FROM fnd_lookup_values
2667 WHERE lookup_type = 'OVERRIDE FIELD'
2668 AND lookup_code = p_field_name;
2669 EXCEPTION
2670 WHEN NO_DATA_FOUND THEN
2671 x_msg_data := 'PA_SETUP_INV_FIELD_NAME';
2672 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2673 p_msg_name => 'PA_SETUP_INV_FIELD_NAME');
2674 x_return_status := 'E';
2675 RAISE FND_API.G_EXC_ERROR;
2676 WHEN OTHERS THEN
2677 null;
2678 END;
2679
2680 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2681 p_msg_name => 'PA_SETUP_SPEC_REQ',
2682 p_token1 => 'FIELD_NAME',
2683 p_value1 => l_field_name_meaning );
2684 x_msg_data := 'PA_SETUP_SPEC_REQ';
2685 x_return_status := 'E';
2686 RAISE FND_API.G_EXC_ERROR;
2687 ELSE
2688 IF p_field_name <> 'CLASSIFICATION'
2689 THEN
2690 BEGIN
2691 SELECT 'x'
2692 INTO l_dummy_char
2693 FROM PA_QUICK_ENTRY_SPECS_SS_V
2694 WHERE FIELD_NAME = p_field_name
2695 AND LIMITING_VALUE = p_limiting_value;
2696 EXCEPTION
2697 WHEN NO_DATA_FOUND THEN
2698 x_msg_data := 'PA_SETUP_INV_SPEC';
2699 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2700 p_msg_name => 'PA_SETUP_INV_SPEC',
2701 p_token1 => 'SPECIFICATION',
2702 p_value1 => p_limiting_value );
2703 x_return_status := 'E';
2704 RAISE FND_API.G_EXC_ERROR;
2705 WHEN OTHERS THEN
2706 null;
2707 END;
2708 ELSE
2709 DECLARE
2710 CURSOR cur_pa_proj_type
2711 IS
2712 SELECT project_type_id
2713 FROM pa_project_types ppt, pa_projects pp
2714 WHERE project_id = p_project_id
2715 AND pp.project_type = ppt.project_type;
2716 l_project_type_id NUMBER;
2717 BEGIN
2718 OPEN cur_pa_proj_type;
2719 FETCH cur_pa_proj_type INTO l_project_type_id;
2720 CLOSE cur_pa_proj_type;
2721 IF l_project_type_id IS NOT NULL
2722 THEN
2723 -- Bug#3693202
2724 -- Commented the existing select which looks into
2725 -- PA_QUICK_ENTRY_SPECS_SS_V view , instead accessed
2726 -- PA_VALID_CATEGORIES_V directly fr performance .
2727
2728 -- SELECT 'x'
2729 -- INTO l_dummy_char
2730 -- FROM PA_QUICK_ENTRY_SPECS_SS_V
2731 -- WHERE FIELD_NAME = p_field_name
2732 -- AND object_type_id = l_project_type_id
2733 -- AND LIMITING_VALUE = p_limiting_value;
2734
2735
2736 SELECT 'X'
2737 INTO l_dummy_char
2738 FROM pa_valid_categories_v pvc ,
2739 pa_lookups pl
2740 WHERE pvc.object_type = 'PA_PROJECTS'
2741 AND pvc.object_type_id = l_project_type_id
2742 AND 'CLASSIFICATION' = pl.lookup_code
2743 AND pl.lookup_type = 'OVERRIDE FIELD'
2744 AND pvc.class_category = p_limiting_value ;
2745 END IF;
2746 EXCEPTION
2747 WHEN NO_DATA_FOUND THEN
2748 x_msg_data := 'PA_SETUP_INV_SPEC';
2749 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2750 p_msg_name => 'PA_SETUP_INV_SPEC',
2751 p_token1 => 'SPECIFICATION',
2752 p_value1 => p_limiting_value );
2753 x_return_status := 'E';
2754 RAISE FND_API.G_EXC_ERROR;
2755 WHEN OTHERS THEN
2756 null;
2757 END;
2758 END IF; --<< field_name <> 'CLASSIFICATION' >>
2759 END IF;
2760 END IF;
2761
2762 OPEN cur_overrides;
2763 FETCH cur_overrides INTO l_dummy_char;
2764 IF cur_overrides%NOTFOUND
2765 THEN
2766 INSERT INTO pa_project_copy_overrides
2767 ( PROJECT_ID ,
2768 FIELD_NAME ,
2769 DISPLAY_NAME ,
2770 LAST_UPDATE_DATE ,
2771 LAST_UPDATED_BY ,
2772 CREATION_DATE ,
2773 CREATED_BY ,
2774 LAST_UPDATE_LOGIN ,
2775 LIMITING_VALUE ,
2776 SORT_ORDER ,
2777 MANDATORY_FLAG ,
2778 RECORD_VERSION_NUMBER
2779 )
2780 VALUES ( p_project_id ,
2781 p_field_name ,
2782 p_prompt ,
2783 SYSDATE ,
2784 FND_GLOBAL.USER_ID ,
2785 SYSDATE ,
2786 FND_GLOBAL.USER_ID ,
2787 FND_GLOBAL.LOGIN_ID ,
2788 p_limiting_value ,
2789 p_sort_order ,
2790 p_required_flag ,
2791 1
2792 );
2793 END IF;
2794 CLOSE cur_overrides;
2795
2796 x_return_status := FND_API.G_RET_STS_SUCCESS;
2797
2798 IF (p_commit = FND_API.G_TRUE) THEN
2799 COMMIT;
2800 END IF;
2801
2802 IF (p_debug_mode = 'Y') THEN
2803 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Add_Quick_Entry_Field END');
2804 END IF;
2805 EXCEPTION
2806 when FND_API.G_EXC_ERROR then
2807 if p_commit = FND_API.G_TRUE then
2808 rollback to Add_Quick_Entry_Field;
2809 end if;
2810 x_return_status := FND_API.G_RET_STS_ERROR;
2811 when FND_API.G_EXC_UNEXPECTED_ERROR then
2812 if p_commit = FND_API.G_TRUE then
2813 rollback to Add_Quick_Entry_Field;
2814 end if;
2815 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2816 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
2817 p_procedure_name => 'Add_Quick_Entry_Field',
2818 p_error_text => SUBSTRB(SQLERRM,1,240));
2819 when OTHERS then
2820 if p_commit = FND_API.G_TRUE then
2821 rollback to Add_Quick_Entry_Field;
2822 end if;
2823 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2824 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
2825 p_procedure_name => 'Add_Quick_Entry_Field',
2826 p_error_text => SUBSTRB(SQLERRM,1,240));
2827 raise;
2828 END Add_Quick_Entry_Field;
2829
2830 -- API name : Update_Quick_Entry_Field
2831 -- Type : Public API
2832 -- Pre-reqs : None
2833 -- Return Value :
2834 --
2835 -- Parameters
2836 -- p_project_id IN NUMBER ,
2837 -- p_sort_order IN NUMBER ,
2838 -- p_field_name IN VARCHAR2 := 'ABCD',
2839 -- p_limiting_value IN VARCHAR2 := 'ABCD',
2840 -- p_prompt IN VARCHAR2 ,
2841 -- p_required_flag IN VARCHAR2 := 'N',--
2842
2843 PROCEDURE Update_Quick_Entry_Field(
2844 p_api_version IN NUMBER :=1.0,
2845 p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE,
2846 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
2847 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE,
2848 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
2849 p_calling_module IN VARCHAR2 :='SELF_SERVICE',
2850 p_debug_mode IN VARCHAR2 :='N',
2851 p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2852 p_project_id IN NUMBER ,
2853 p_row_id IN VARCHAR2,
2854 p_sort_order IN NUMBER ,
2855 p_field_name IN VARCHAR2 := 'JUNK_CHARS',
2856 p_limiting_value IN VARCHAR2 := 'JUNK_CHARS',
2857 p_prompt IN VARCHAR2 ,
2858 p_required_flag IN VARCHAR2 := 'N',
2859 p_record_version_number IN NUMBER,
2860 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2861 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2862 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2863 ) IS
2864 l_api_name CONSTANT VARCHAR(30) := 'Update_Quick_Entry_Field';
2865 l_api_version CONSTANT NUMBER := 1.0;
2866
2867 l_return_status VARCHAR2(1);
2868 l_msg_count NUMBER;
2869 l_msg_data VARCHAR2(250);
2870 l_data VARCHAR2(250);
2871 l_msg_index_out NUMBER;
2872 l_error_msg_code VARCHAR2(250);
2873
2874 l_err_code NUMBER;
2875 l_err_stage VARCHAR2(250);
2876 l_err_stack VARCHAR2(250);
2877 l_dummy_char VARCHAR2(1);
2878 l_field_name_meaning VARCHAR2(250);
2879
2880 CURSOR cur_chk_sort_order
2881 IS
2882 SELECT 'x'
2883 FROM pa_project_copy_overrides
2884 WHERE rowid <> p_row_id
2885 AND project_id = p_project_id
2886 AND sort_order = p_sort_order;
2887
2888 CURSOR cur_dup_quick_entry
2889 IS
2890 SELECT 'X'
2891 FROM pa_project_copy_overrides
2892 WHERE rowid <> p_row_id
2893 AND project_id = p_project_id
2894 AND field_name = p_field_name;
2895 BEGIN
2896 pa_debug.init_err_stack ('PA_PROJ_TEMPLATE_SETUP_PVT.Update_Quick_Entry_Field');
2897
2898 IF (p_debug_mode = 'Y') THEN
2899 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Update_Quick_Entry_Field begin');
2900 END IF;
2901
2902 IF (p_commit = FND_API.G_TRUE) THEN
2903 savepoint Update_Quick_Entry_Field;
2904 END IF;
2905
2906 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2907 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2908 END IF;
2909
2910 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2911 FND_MSG_PUB.initialize;
2912 END IF;
2913
2914 --cant update Project Number or Proejct Name
2915 IF ( p_FIELD_NAME = 'SEGMENT1' OR p_FIELD_NAME = 'NAME' ) AND
2916 p_required_flag = 'N'
2917 THEN
2918 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2919 p_msg_name => 'PA_SETUP_CANT_MODFY_OVER' );
2920 x_msg_data := 'PA_SETUP_CANT_MODFY_OVER';
2921 x_return_status := 'E';
2922 RAISE FND_API.G_EXC_ERROR;
2923 END IF;
2924
2925 --check sort order unique
2926 OPEN cur_chk_sort_order;
2927 FETCH cur_chk_sort_order INTO l_dummy_char;
2928 IF cur_chk_sort_order%FOUND
2929 THEN
2930 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2931 p_msg_name => 'PA_SETUP_SORT_ORDER_NOT_UNIQ' );
2932 x_msg_data := 'PA_SETUP_SORT_ORDER_NOT_UNIQ';
2933 x_return_status := 'E';
2934 CLOSE cur_chk_sort_order;
2935 RAISE FND_API.G_EXC_ERROR;
2936 ELSE
2937 CLOSE cur_chk_sort_order;
2938 END IF;
2939
2940 IF p_field_name NOT IN ( 'KEY_MEMBER', 'CLASSIFICATION', 'CUSTOMER_NAME' ,'ORG_ROLE')
2941 THEN
2942 --check duplicate quick entry.
2943 OPEN cur_dup_quick_entry;
2944 FETCH cur_dup_quick_entry INTO l_dummy_char;
2945 IF cur_dup_quick_entry%FOUND
2946 THEN
2947 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2948 p_msg_name => 'PA_SETUP_DUP_QUICK_ENTRY' );
2949 x_msg_data := 'PA_SETUP_DUP_QUICK_ENTRY';
2950 x_return_status := 'E';
2951 CLOSE cur_dup_quick_entry;
2952 RAISE FND_API.G_EXC_ERROR;
2953 ELSE
2954 CLOSE cur_dup_quick_entry;
2955 END IF;
2956 ELSE
2957 IF p_limiting_value IS NULL OR p_limiting_value = 'JUNK_CHARS'
2958 THEN
2959 BEGIN
2960 SELECT meaning
2961 INTO l_field_name_meaning
2962 FROM fnd_lookup_values
2963 WHERE lookup_type = 'OVERRIDE FIELD'
2964 AND lookup_code = p_field_name;
2965 EXCEPTION
2966 WHEN NO_DATA_FOUND THEN
2967 x_msg_data := 'PA_SETUP_INV_FIELD_NAME';
2968 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2969 p_msg_name => 'PA_SETUP_INV_FIELD_NAME');
2970 x_return_status := 'E';
2971 RAISE FND_API.G_EXC_ERROR;
2972 WHEN OTHERS THEN
2973 null;
2974 END;
2975
2976 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2977 p_msg_name => 'PA_SETUP_SPEC_REQ',
2978 p_token1 => 'FIELD_NAME',
2979 p_value1 => l_field_name_meaning );
2980 x_msg_data := 'PA_SETUP_SPEC_REQ';
2981 x_return_status := 'E';
2982 RAISE FND_API.G_EXC_ERROR;
2983 ELSE
2984 IF p_field_name <> 'CLASSIFICATION'
2985 THEN
2986 BEGIN
2987 SELECT 'x'
2988 INTO l_dummy_char
2989 FROM PA_QUICK_ENTRY_SPECS_SS_V
2990 WHERE FIELD_NAME = p_field_name
2991 AND LIMITING_VALUE = p_limiting_value;
2992 EXCEPTION
2993 WHEN NO_DATA_FOUND THEN
2994 x_msg_data := 'PA_SETUP_INV_SPEC';
2995 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2996 p_msg_name => 'PA_SETUP_INV_SPEC',
2997 p_token1 => 'SPECIFICATION',
2998 p_value1 => p_limiting_value );
2999 x_return_status := 'E';
3000 RAISE FND_API.G_EXC_ERROR;
3001 WHEN OTHERS THEN
3002 null;
3003 END;
3004 ELSE
3005 DECLARE
3006 CURSOR cur_pa_proj_type
3007 IS
3008 SELECT project_type_id
3009 FROM pa_project_types ppt, pa_projects pp
3010 WHERE project_id = p_project_id
3011 AND pp.project_type = ppt.project_type;
3012 l_project_type_id NUMBER;
3013 BEGIN
3014 OPEN cur_pa_proj_type;
3015 FETCH cur_pa_proj_type INTO l_project_type_id;
3016 CLOSE cur_pa_proj_type;
3017 IF l_project_type_id IS NOT NULL
3018 THEN
3019
3020 -- Bug#3693202
3021 -- Commented the existing select which looks into
3022 -- PA_QUICK_ENTRY_SPECS_SS_V view , instead accessed
3023 -- PA_VALID_CATEGORIES_V directly fr performance .
3024
3025 -- SELECT 'x'
3026 -- INTO l_dummy_char
3027 -- FROM PA_QUICK_ENTRY_SPECS_SS_V
3028 -- WHERE FIELD_NAME = p_field_name
3029 -- AND object_type_id = l_project_type_id
3030 -- AND LIMITING_VALUE = p_limiting_value;
3031
3032 SELECT 'X'
3033 INTO l_dummy_char
3034 FROM pa_valid_categories_v pvc ,
3035 pa_lookups pl
3036 WHERE pvc.object_type = 'PA_PROJECTS'
3037 AND pvc.object_type_id = l_project_type_id
3038 AND 'CLASSIFICATION' = pl.lookup_code
3039 AND pl.lookup_type = 'OVERRIDE FIELD'
3040 AND pvc.class_category = p_limiting_value ;
3041 END IF;
3042 EXCEPTION
3043 WHEN NO_DATA_FOUND THEN
3044 x_msg_data := 'PA_SETUP_INV_SPEC';
3045 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3046 p_msg_name => 'PA_SETUP_INV_SPEC',
3047 p_token1 => 'SPECIFICATION',
3048 p_value1 => p_limiting_value );
3049 x_return_status := 'E';
3050 RAISE FND_API.G_EXC_ERROR;
3051 WHEN OTHERS THEN
3052 null;
3053 END;
3054 END IF;
3055 END IF;
3056 END IF;
3057
3058 UPDATE pa_project_copy_overrides
3059 SET FIELD_NAME = p_field_name ,
3060 DISPLAY_NAME = p_prompt ,
3061 LAST_UPDATE_DATE = SYSDATE ,
3062 LAST_UPDATED_BY = FND_GLOBAL.USER_ID ,
3063 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID ,
3064 LIMITING_VALUE = p_limiting_value ,
3065 SORT_ORDER = p_sort_order ,
3066 MANDATORY_FLAG = p_required_flag ,
3067 RECORD_VERSION_NUMBER = NVL( RECORD_VERSION_NUMBER, 1 ) + 1
3068 WHERE rowid = p_row_id;
3069
3070 x_return_status := FND_API.G_RET_STS_SUCCESS;
3071
3072 IF (p_commit = FND_API.G_TRUE) THEN
3073 COMMIT;
3074 END IF;
3075
3076 IF (p_debug_mode = 'Y') THEN
3077 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Update_Quick_Entry_Field END');
3078 END IF;
3079 EXCEPTION
3080 when FND_API.G_EXC_ERROR then
3081 if p_commit = FND_API.G_TRUE then
3082 rollback to Update_Quick_Entry_Field;
3083 end if;
3084 x_return_status := FND_API.G_RET_STS_ERROR;
3085 when FND_API.G_EXC_UNEXPECTED_ERROR then
3086 if p_commit = FND_API.G_TRUE then
3087 rollback to Update_Quick_Entry_Field;
3088 end if;
3089 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3090 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
3091 p_procedure_name => 'Update_Quick_Entry_Field',
3092 p_error_text => SUBSTRB(SQLERRM,1,240));
3093 when OTHERS then
3094 if p_commit = FND_API.G_TRUE then
3095 rollback to Update_Quick_Entry_Field;
3096 end if;
3097 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3098 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
3099 p_procedure_name => 'Update_Quick_Entry_Field',
3100 p_error_text => SUBSTRB(SQLERRM,1,240));
3101 raise;
3102 END Update_Quick_Entry_Field;
3103
3104 -- API name : DELETE_Quick_Entry_Field
3105 -- Type : Public API
3106 -- Pre-reqs : None
3107 -- Return Value :
3108 --
3109 -- Parameters
3110 -- p_project_id IN NUMBER ,
3111 -- p_sort_order IN NUMBER ,
3112 -- p_field_name IN VARCHAR2 := 'ABCD',
3113 -- p_limiting_value IN VARCHAR2 := 'ABCD',
3114 -- p_prompt IN VARCHAR2 ,
3115 -- p_required_flag IN VARCHAR2 := 'N',--
3116
3117 PROCEDURE Delete_Quick_Entry_Field(
3118 p_api_version IN NUMBER :=1.0,
3119 p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE,
3120 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
3121 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE,
3122 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
3123 p_calling_module IN VARCHAR2 :='SELF_SERVICE',
3124 p_debug_mode IN VARCHAR2 :='N',
3125 p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3126 p_project_id IN NUMBER ,
3127 p_row_id IN VARCHAR2,
3128 p_record_version_number IN NUMBER,
3129 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3130 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3131 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3132 ) IS
3133 l_api_name CONSTANT VARCHAR(30) := 'Delete_Quick_Entry_Field';
3134 l_api_version CONSTANT NUMBER := 1.0;
3135
3136 l_return_status VARCHAR2(1);
3137 l_msg_count NUMBER;
3138 l_msg_data VARCHAR2(250);
3139 l_data VARCHAR2(250);
3140 l_msg_index_out NUMBER;
3141 l_error_msg_code VARCHAR2(250);
3142
3143 l_err_code NUMBER;
3144 l_err_stage VARCHAR2(250);
3145 l_err_stack VARCHAR2(250);
3146 l_dummy_char VARCHAR2(1);
3147
3148 CURSOR cur_chk_dflt_qe
3149 IS
3150 SELECT field_name
3151 FROM pa_project_copy_overrides
3152 WHERE rowid = p_row_id;
3153
3154 l_field_name VARCHAR2(80);
3155
3156 BEGIN
3157 pa_debug.init_err_stack ('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Quick_Entry_Field');
3158
3159 IF (p_debug_mode = 'Y') THEN
3160 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Quick_Entry_Field begin');
3161 END IF;
3162
3163 IF (p_commit = FND_API.G_TRUE) THEN
3164 savepoint Delete_Quick_Entry_Field;
3165 END IF;
3166
3167 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
3168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3169 END IF;
3170
3171 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
3172 FND_MSG_PUB.initialize;
3173 END IF;
3174
3175 OPEN cur_chk_dflt_qe;
3176 FETCH cur_chk_dflt_qe INTO l_field_name;
3177 CLOSE cur_chk_dflt_qe;
3178
3179 --cant update Project Number or Proejct Name
3180 IF l_FIELD_NAME = 'SEGMENT1' OR l_FIELD_NAME = 'NAME'
3181 THEN
3182 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3183 p_msg_name => 'PA_SETUP_CANT_MODFY_OVER' );
3184 x_msg_data := 'PA_SETUP_CANT_MODFY_OVER';
3185 x_return_status := 'E';
3186 RAISE FND_API.G_EXC_ERROR;
3187 END IF;
3188
3189 DELETE FROM pa_project_copy_overrides
3190 WHERE rowid = p_row_id;
3191
3192 x_return_status := FND_API.G_RET_STS_SUCCESS;
3193
3194 IF (p_commit = FND_API.G_TRUE) THEN
3195 COMMIT;
3196 END IF;
3197
3198 IF (p_debug_mode = 'Y') THEN
3199 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Quick_Entry_Field END');
3200 END IF;
3201 EXCEPTION
3202 when FND_API.G_EXC_ERROR then
3203 if p_commit = FND_API.G_TRUE then
3204 rollback to Delete_Quick_Entry_Field;
3205 end if;
3206 x_return_status := FND_API.G_RET_STS_ERROR;
3207 when FND_API.G_EXC_UNEXPECTED_ERROR then
3208 if p_commit = FND_API.G_TRUE then
3209 rollback to Delete_Quick_Entry_Field;
3210 end if;
3211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3212 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
3213 p_procedure_name => 'Delete_Quick_Entry_Field',
3214 p_error_text => SUBSTRB(SQLERRM,1,240));
3215 when OTHERS then
3216 if p_commit = FND_API.G_TRUE then
3217 rollback to Delete_Quick_Entry_Field;
3218 end if;
3219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3220 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
3221 p_procedure_name => 'Delete_Quick_Entry_Field',
3222 p_error_text => SUBSTRB(SQLERRM,1,240));
3223 raise;
3224 END Delete_Quick_Entry_Field;
3225
3226 PROCEDURE enable_disbale_proj_opt(
3227 p_project_id NUMBER,
3228 p_option_code VARCHAR2,
3229 p_action VARCHAR2
3230 ) IS
3231
3232 CURSOR cur_pa_lkups
3233 IS
3234 SELECT lk1.lookup_code forms_opt_code, lk2.lookup_code ss_opt_code, lk1.meaning
3235 FROM fnd_lookup_values lk1, fnd_lookup_values lk2
3236 WHERE lk1.lookup_type = 'PA_OPTIONS'
3237 AND lk2.lookup_type = 'PA_OPTIONS_SS'
3238 AND lk1.meaning = lk2.meaning
3239 ;
3240
3241 CURSOR cur_chk_options( c_option_code VARCHAR2 )
3242 IS
3243 SELECT 'x'
3244 FROM pa_project_options
3245 WHERE project_id = p_project_id
3246 AND option_code = c_option_code;
3247
3248 l_option_code VARCHAR2(30);
3249 l_found_flag VARCHAR2(1) := 'N';
3250 l_dummy_char VARCHAR2(1);
3251
3252 BEGIN
3253 FOR cur_pa_lkups_rec in cur_pa_lkups LOOP
3254 IF cur_pa_lkups_rec.forms_opt_code = p_option_code
3255 THEN
3256 l_option_code := cur_pa_lkups_rec.ss_opt_code;
3257 l_found_flag := 'Y';
3258 ELSIF cur_pa_lkups_rec.ss_opt_code = p_option_code
3259 THEN
3260 l_option_code := cur_pa_lkups_rec.forms_opt_code;
3261 l_found_flag := 'Y';
3262 ELSE
3263 l_found_flag := 'N';
3264 END IF;
3265
3266 IF l_found_flag = 'Y' AND l_option_code IS NOT NULL AND p_action = 'ENABLE'
3267 THEN
3268 OPEN cur_chk_options( l_option_code );
3269 FETCH cur_chk_options INTO l_dummy_char;
3270 IF cur_chk_options%NOTFOUND
3271 THEN
3272 INSERT INTO pa_project_options
3273 (
3274 project_id,
3275 option_code,
3276 last_update_date,
3277 last_updated_by,
3278 creation_date,
3279 created_by,
3280 last_update_login,
3281 record_version_number
3282 )
3283 VALUES ( p_project_id,
3284 l_option_code,
3285 SYSDATE,
3286 FND_GLOBAL.USER_ID,
3287 SYSDATE,
3288 FND_GLOBAL.USER_ID,
3289 FND_GLOBAL.LOGIN_ID ,
3290 1
3291 );
3292 END IF;
3293 CLOSE cur_chk_options;
3294 ELSIF l_found_flag = 'Y' AND l_option_code IS NOT NULL AND p_action = 'DISABLE'
3295 THEN
3296 DELETE FROM pa_project_options WHERE project_id = p_project_id and option_code = l_option_code ;
3297 END IF; --<< l_insert_flag >>
3298 END LOOP;
3299 END enable_disbale_proj_opt;
3300
3301 END PA_PROJ_TEMPLATE_SETUP_PVT;