[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.7 2011/05/11 11:51:03 pcheruvu 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 /* Added for 12.2 Payroll billing ER 11847616 */
767 ,x_bill_labor_accrual => 'N'
768 ,x_adj_on_std_inv => 'N'
769 /* Added for 12.2 Payroll billing ER 11847616 */
770
771 );
772
773 --Call Add options api to add options in pa_project_options with the created template.
774 DECLARE
775 CURSOR cur_template_options
776 IS
777 SELECT option_code
778 FROM pa_options;
779 BEGIN
780 FOR cur_template_options_rec IN cur_template_options LOOP
781 -- anlee
782 -- Enable Advanced Structures
783 -- Don't want to add structure or workplan related options by default
784 --FP_M Changes. Tracking Bug 3279978
785 IF cur_template_options_rec.option_code NOT IN ('STRUCTURES', 'STRUCTURES_SS','DELIVERABLES_SS') THEN
786 PA_PROJ_TEMPLATE_SETUP_PUB.Add_Project_Options(
787 p_api_version => p_api_version
788 ,p_init_msg_list => p_init_msg_list
789 ,p_commit => p_commit
790 ,p_validate_only => p_validate_only
791 ,p_validation_level => p_validation_level
792 ,p_calling_module => p_calling_module
793 ,p_debug_mode => p_debug_mode
794 ,p_max_msg_count => p_max_msg_count
795 ,p_project_id => l_template_id
796 ,p_option_code => cur_template_options_rec.option_code
797 ,p_action => 'INSERT'
798 ,x_return_status => l_return_status
799 ,x_msg_count => l_msg_count
800 ,x_msg_data => l_msg_data
801 );
802 END IF;
803 -- End of changes
804 END LOOP;
805 END;
806
807 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
808 x_msg_count := FND_MSG_PUB.count_msg;
809 IF x_msg_count = 1 then
810 pa_interface_utils_pub.get_messages
811 (p_encoded => FND_API.G_TRUE,
812 p_msg_index => 1,
813 p_msg_count => l_msg_count,
814 p_msg_data => l_msg_data,
815 p_data => l_data,
816 p_msg_index_out => l_msg_index_out);
817 x_msg_data := l_data;
818 END IF;
819 raise FND_API.G_EXC_ERROR;
820 END IF;
821
822 --Call Add quick entry api to add two default quick entries( SEGMENT1 and NAME ) in
823 --pa_project_copy_overrides with the created template.
824 DECLARE
825 CURSOR cur_copy_overrides
826 IS
827 SELECT lookup_code, meaning
828 FROM pa_lookups
829 WHERE lookup_type = 'OVERRIDE FIELD'
830 AND lookup_code = 'SEGMENT1'
831 UNION
832 SELECT lookup_code, meaning
833 FROM pa_lookups
834 WHERE lookup_type = 'OVERRIDE FIELD'
835 AND lookup_code = 'NAME'
836 ORDER BY 1 DESC;
837
838 l_rownum NUMBER := 0;
839 BEGIN
840 FOR cur_copy_overrides_rec IN cur_copy_overrides LOOP
841 l_rownum := l_rownum + 1;
842 PA_PROJ_TEMPLATE_SETUP_PUB.Add_Quick_Entry_Field(
843 p_api_version => p_api_version
844 ,p_init_msg_list => p_init_msg_list
845 ,p_commit => p_commit
846 ,p_validate_only => p_validate_only
847 ,p_validation_level => p_validation_level
848 ,p_calling_module => p_calling_module
849 ,p_debug_mode => p_debug_mode
850 ,p_max_msg_count => p_max_msg_count
851 ,p_project_id => l_template_id
852 ,p_sort_order => l_rownum * 10
853 ,p_field_name => cur_copy_overrides_rec.lookup_code
854 ,p_limiting_value => null
855 ,p_prompt => cur_copy_overrides_rec.meaning
856 ,p_required_flag => 'Y'
857 ,x_return_status => l_return_status
858 ,x_msg_count => l_msg_count
859 ,x_msg_data => l_msg_data
860 );
861
862 END LOOP;
863 END;
864
865 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
866 x_msg_count := FND_MSG_PUB.count_msg;
867 IF x_msg_count = 1 then
868 pa_interface_utils_pub.get_messages
869 (p_encoded => FND_API.G_TRUE,
870 p_msg_index => 1,
871 p_msg_count => l_msg_count,
872 p_msg_data => l_msg_data,
873 p_data => l_data,
874 p_msg_index_out => l_msg_index_out);
875 x_msg_data := l_data;
876 END IF;
877 raise FND_API.G_EXC_ERROR;
878 END IF;
879
880 --Insert default resource list
881 Open Get_def_Res_List;
882 Fetch Get_def_Res_List Into x_def_res_list_id, x_rl_asgmt_id;
883 If Get_def_Res_list%NOTFOUND Then
884 x_def_res_list_id := NULL;
885 end if;
886 Close Get_def_Res_List;
887 IF x_def_res_list_id is not null Then
888 INSERT INTO pa_resource_list_assignments (
889 resource_list_assignment_id,
890 resource_list_id,
891 project_id,
892 resource_list_changed_flag,
893 resource_list_accumulated_flag,
894 last_updated_by,
895 last_update_date,
896 creation_date,
897 created_by,
898 last_update_login )
899 Values (
900 x_rl_asgmt_id,
901 x_def_res_list_id,
902 l_template_id,
903 'N',
904 'N',
905 nvl(x_user_id,-1),
906 trunc(sysdate),
907 trunc(sysdate),
908 nvl(x_user_id,-1),
909 nvl(x_login_id,-1));
910 Insert into pa_resource_list_uses (
911 resource_list_assignment_id,
912 use_code,
913 default_flag,
914 last_updated_by,
915 last_update_date,
916 creation_date,
917 created_by,
918 last_update_login )
919 values (
920 x_rl_asgmt_id,
921 x_def_use_code,
922 x_def_flag,
923 nvl(x_user_id,-1),
924 trunc(sysdate),
925 trunc(sysdate),
926 nvl(x_user_id,-1),
927 nvl(x_login_id ,-1));
928 END IF;
929
930
931 --Create a default task
932
933 IF l_project_type_class_code = 'INDIRECT'
934 THEN
935 l_billable_flag := 'N';
936 ELSE
937 l_billable_flag := 'Y';
938 END IF;
939
940 /* From FPM we will not be creating any default task or structure at the time of template creation
941 Please refer Financial structures in HTML Technical Architecture on files online
942 --bug 3301192
943 PA_TASKS_PKG.Insert_Row(
944 X_Rowid => l_row_id,
945 X_task_id => l_task_id,
946 X_Project_id => l_template_id,
947 X_Task_Number => '1',
948 X_Creation_Date => sysdate,
949 X_Created_By => FND_GLOBAL.USER_ID,
950 X_Last_Update_Date => sysdate,
951 X_last_Updated_By => FND_GLOBAL.USER_ID,
952 X_Last_Update_login => FND_GLOBAL.LOGIN_ID,
953 X_Task_Name => 'Task 1',
954 X_Long_Task_Name => 'Task 1',
955 X_Top_Task_Id => null,
956 X_Wbs_level => 1,
957 X_ready_to_Bill_flag => 'Y',
958 X_Ready_To_Distribute_Flag => 'Y',
959 X_parent_task_id => null,
960 X_Description => 'Task 1',
961 X_carrying_out_organization_id => p_organization_id,
962 X_Service_Type_code => l_service_type_code,
963 X_Task_Manager_Person_id => null,
964 X_chargeable_Flag => 'Y',
965 X_Billable_flag => l_billable_flag,
966 X_limit_to_Txn_controls_flag => 'N',
967 X_Start_Date => null,
968 X_Completion_Date => null,
969 X_Address_Id => null, --w_address_id, -Since no customer is created so far
970 X_Labor_Bill_Rate_org_id => l_labor_bill_rate_org_id, -- :project_folder.Labor_Bill_Rate_Org_Id,
971 X_Labor_Std_Bill_Rate_Schdl => l_labor_std_bill_rate_schdl, --:project_folder.Labor_Std_Bill_Rate_Schdl,
972 X_Labor_Schedule_Fixed_Date => null,
973 X_Labor_Schedule_Discount => null,
974 X_Non_Labor_Bill_Rate_Org_Id => l_non_labor_bill_rate_org_id, --:project_folder.Non_Labor_Bill_Rate_Org_Id,
975 X_NL_Std_Bill_Rate_Schdl => l_nl_std_bill_rate_schdl, --:project_folder.Non_Labor_Std_Bill_Rate_Schdl,
976 X_Nl_Schedule_Fixed_Date => null,
977 X_Non_Labor_Schedule_Discount => null,
978 X_Labor_Cost_Multiplier_Name => null,
979 X_Attribute_Category => null,
980 X_Attribute1 => null,
981 X_Attribute2 => null,
982 X_Attribute3 => null,
983 X_Attribute4 => null,
984 X_Attribute5 => null,
985 X_Attribute6 => null,
986 X_Attribute7 => null,
987 X_Attribute8 => null,
988 X_Attribute9 => null,
989 X_Attribute10 => null,
990 X_Cost_Ind_Rate_Sch_Id => l_Cost_Ind_Rate_Sch_Id,
991 X_Rev_ind_rate_sch_id => l_Rev_Ind_Rate_Sch_Id,
992 X_Inv_Ind_rate_sch_id => l_Inv_Ind_Rate_Sch_Id,
993 X_Cost_ind_sch_fixed_date => null,
994 X_Rev_Ind_sch_fixed_date => null,
995 X_Inv_Ind_sch_fixed_date => null,
996 X_Labor_Sch_Type => l_Labor_Sch_Type,
997 X_Non_Labor_Sch_Type => l_Non_Labor_Sch_Type,
998 X_Allow_Cross_Charge_Flag => 'N',
999 X_Project_Rate_Date => null,
1000 X_Project_Rate_Type => l_rate_type2,
1001 X_cc_process_labor_flag => 'N',
1002 X_Labor_tp_schedule_id => null,
1003 X_Labor_tp_fixed_date => null,
1004 X_cc_process_nl_flag => 'N',
1005 X_nl_tp_schedule_id => null,
1006 X_nl_tp_fixed_date => null,
1007 X_receive_project_invoice_flag => 'N',
1008 X_work_type_id => l_work_type_id,
1009 X_TASKFUNC_COST_RATE_TYPE => l_rate_type2,
1010 X_TASKFUNC_COST_RATE_DATE => null,
1011 X_NON_LAB_STD_BILL_RT_SCH_ID => l_non_lab_std_bill_rt_sch_id,
1012 X_job_bill_rate_schedule_id => l_job_bill_rate_schedule_id,
1013 X_emp_bill_rate_schedule_id => l_emp_bill_rate_schedule_id,
1014 X_labor_disc_reason_code => null,
1015 X_non_labor_disc_reason_code => null,
1016 --PA L 2872708
1017 x_retirement_cost_flag => 'N',
1018 x_cint_eligible_flag => 'Y',
1019 x_cint_stop_date => null
1020 --PA L 2872708
1021 );
1022 */ --bug 3301192
1023
1024 --Opportunity Management changes.
1025 PA_OPPORTUNITY_MGT_PVT.CREATE_PROJECT_ATTRIBUTES(
1026 p_project_id => l_template_id
1027 ,x_return_status => l_return_status
1028 ,x_msg_count => l_msg_count
1029 ,x_msg_data => l_msg_data );
1030
1031 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1032 x_msg_count := FND_MSG_PUB.count_msg;
1033 IF x_msg_count = 1 then
1034 pa_interface_utils_pub.get_messages
1035 (p_encoded => FND_API.G_TRUE,
1036 p_msg_index => 1,
1037 p_msg_count => l_msg_count,
1038 p_msg_data => l_msg_data,
1039 p_data => l_data,
1040 p_msg_index_out => l_msg_index_out);
1041 x_msg_data := l_data;
1042 END IF;
1043 raise FND_API.G_EXC_ERROR;
1044 END IF;
1045
1046 /* From FPM we will not be creating any default structure at the time of template creation
1047 Please refer Financial structures in HTML Technical Architecture on files online
1048 --bug 3301192
1049 --Create a Structure Workplan and Financial structures separately
1050
1051 PA_PROJ_TASK_STRUC_PUB.create_default_structure(
1052 p_dest_project_id => l_template_id
1053 ,p_dest_project_name => p_project_name
1054 ,p_dest_project_number => p_project_number
1055 ,p_dest_description => p_description
1056 ,p_struc_type => 'FINANCIAL'
1057 ,x_msg_count => l_msg_count
1058 ,x_msg_data => l_msg_data
1059 ,x_return_status => l_return_status
1060 );
1061
1062 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1063 x_msg_count := FND_MSG_PUB.count_msg;
1064 IF x_msg_count = 1 then
1065 pa_interface_utils_pub.get_messages
1066 (p_encoded => FND_API.G_TRUE,
1067 p_msg_index => 1,
1068 p_msg_count => l_msg_count,
1069 p_msg_data => l_msg_data,
1070 p_data => l_data,
1071 p_msg_index_out => l_msg_index_out);
1072 x_msg_data := l_data;
1073 END IF;
1074 raise FND_API.G_EXC_ERROR;
1075 END IF;
1076
1077 --Project Structures Changes
1078 --Creating tasks in pa_proj_elements from pa_tasks
1079 PA_PROJ_TASK_STRUC_PUB.CREATE_DEFAULT_TASK_STRUCTURE(
1080 p_project_id => l_template_id
1081 ,p_struc_type => 'FINANCIAL'
1082 ,x_msg_count => l_msg_count
1083 ,x_msg_data => l_msg_data
1084 ,x_return_status => l_return_status );
1085
1086 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1087 x_msg_count := FND_MSG_PUB.count_msg;
1088 IF x_msg_count = 1 then
1089 pa_interface_utils_pub.get_messages
1090 (p_encoded => FND_API.G_TRUE,
1091 p_msg_index => 1,
1092 p_msg_count => l_msg_count,
1093 p_msg_data => l_msg_data,
1094 p_data => l_data,
1095 p_msg_index_out => l_msg_index_out);
1096 x_msg_data := l_data;
1097 END IF;
1098 raise FND_API.G_EXC_ERROR;
1099 END IF;
1100 */ --bug 3301192
1101
1102 -- anlee
1103 -- Advanced Project Structures
1104 -- Comment out creation of workplan structure
1105 -- New templates will only have financial structure
1106 /*
1107 --Create a Structure Workplan and Financial structures separately
1108
1109 PA_PROJ_TASK_STRUC_PUB.create_default_structure(
1110 p_dest_project_id => l_template_id
1111 ,p_dest_project_name => p_project_name
1112 ,p_dest_project_number => p_project_number
1113 ,p_dest_description => p_description
1114 ,p_struc_type => 'WORKPLAN'
1115 ,x_msg_count => l_msg_count
1116 ,x_msg_data => l_msg_data
1117 ,x_return_status => l_return_status
1118 );
1119
1120 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1121 x_msg_count := FND_MSG_PUB.count_msg;
1122 IF x_msg_count = 1 then
1123 pa_interface_utils_pub.get_messages
1124 (p_encoded => FND_API.G_TRUE,
1125 p_msg_index => 1,
1126 p_msg_count => l_msg_count,
1127 p_msg_data => l_msg_data,
1128 p_data => l_data,
1129 p_msg_index_out => l_msg_index_out);
1130 x_msg_data := l_data;
1131 END IF;
1132 raise FND_API.G_EXC_ERROR;
1133 END IF;
1134 */
1135 -- End of changes
1136
1137 x_return_status := FND_API.G_RET_STS_SUCCESS;
1138
1139 IF (p_commit = FND_API.G_TRUE) THEN
1140 COMMIT;
1141 END IF;
1142
1143 IF (p_debug_mode = 'Y') THEN
1144 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Create_Project_Template END');
1145 END IF;
1146 EXCEPTION
1147 when FND_API.G_EXC_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_ERROR;
1152 when FND_API.G_EXC_UNEXPECTED_ERROR then
1153 if p_commit = FND_API.G_TRUE then
1154 rollback to Create_Project_Template;
1155 end if;
1156 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1157 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
1158 p_procedure_name => 'Create_Project_Template',
1159 p_error_text => SUBSTRB(SQLERRM,1,240));
1160 when OTHERS then
1161 if p_commit = FND_API.G_TRUE then
1162 rollback to Create_Project_Template;
1163 end if;
1164 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1165 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
1166 p_procedure_name => 'Create_Project_Template',
1167 p_error_text => SUBSTRB(SQLERRM,1,240));
1168 raise;
1169 END Create_Project_Template;
1170
1171 -- API name : Update_Project_Template
1172 -- Type : Public API
1173 -- Pre-reqs : None
1174 -- Return Value :
1175 --
1176 -- Parameters
1177 --p_project_number IN VARCHAR2
1178 --p_project_name IN VARCHAR2
1179 --p_project_type IN VARCHAR2
1180 --p_organization_id IN NUMBER
1181 --p_organization_name IN VARCHAR2
1182 --p_effective_from_date IN DATE
1183 --p_effective_to_date IN DATE
1184 --p_description IN VARCHAR2
1185
1186 PROCEDURE Update_Project_Template(
1187 p_api_version IN NUMBER :=1.0,
1188 p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE,
1189 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
1190 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE,
1191 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
1192 p_calling_module IN VARCHAR2 :='SELF_SERVICE',
1193 p_debug_mode IN VARCHAR2 :='N',
1194 p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
1195 p_project_id IN NUMBER,
1196 p_project_number IN VARCHAR2 := 'JUNK_CHARS',
1197 p_project_name IN VARCHAR2 := 'JUNK_CHARS',
1198 p_project_type IN VARCHAR2 := 'JUNK_CHARS',
1199 p_organization_id IN NUMBER := -9999,
1200 p_effective_from_date IN DATE := TO_DATE( '01-01-1000', 'DD-MM-YYYY' ),
1201 p_effective_to_date IN DATE := TO_DATE( '01-01-1000', 'DD-MM-YYYY' ),
1202 p_description IN VARCHAR2 := 'JUNK_CHARS',
1203 p_security_level IN NUMBER := 0,
1204 -- anlee
1205 -- Project Long Name changes
1206 p_long_name IN VARCHAR2 DEFAULT NULL,
1207 -- End of changes
1208 p_record_version_number IN NUMBER,
1209 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1210 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1211 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1212 ) IS
1213 l_api_name CONSTANT VARCHAR(30) := 'Update_Project_Template';
1214 l_api_version CONSTANT NUMBER := 1.0;
1215
1216 l_return_status VARCHAR2(1);
1217 l_msg_count NUMBER;
1218 l_msg_data VARCHAR2(250);
1219 l_data VARCHAR2(250);
1220 l_msg_index_out NUMBER;
1221 l_error_msg_code VARCHAR2(250);
1222
1223 l_organization_id NUMBER;
1224
1225 l_Status_code VARCHAR2(80);
1226 l_service_type_code VARCHAR2(80);
1227 l_cost_ind_rate_sch_id NUMBER;
1228 l_labor_sch_type VARCHAR2(80);
1229 l_labor_bill_rate_org_id NUMBER;
1230 l_labor_std_bill_rate_schdl VARCHAR2(80);
1231 l_non_labor_sch_type VARCHAR2(80);
1232 l_non_labor_bill_rate_org_id NUMBER;
1233 l_nl_std_bill_rate_schdl VARCHAR2(80);
1234 l_rev_ind_rate_sch_id NUMBER;
1235 l_inv_ind_rate_sch_id NUMBER;
1236 l_labor_invoice_format_id NUMBER;
1237 l_non_labor_invoice_format_id NUMBER;
1238 l_Burden_cost_flag VARCHAR2(80);
1239 l_interface_asset_cost_code VARCHAR2(80);
1240 l_cost_sch_override_flag VARCHAR2(80);
1241 l_billing_offset NUMBER;
1242 l_billing_cycle_id NUMBER;
1243 l_cc_prvdr_flag VARCHAR2(80);
1244 l_bill_job_group_id NUMBER;
1245 l_cost_job_group_id NUMBER;
1246 l_work_type_id NUMBER;
1247 l_role_list_id NUMBER;
1248 l_unassigned_time VARCHAR2(1);
1249 l_emp_bill_rate_schedule_id NUMBER;
1250 l_job_bill_rate_schedule_id NUMBER;
1251 l_budgetary_override_flag VARCHAR2(80);
1252 l_baseline_funding_flag VARCHAR2(80);
1253 l_non_lab_std_bill_rt_sch_id NUMBER;
1254 l_project_type_class_code VARCHAR2(80);
1255 l_effective_from_date DATE;
1256 l_effective_to_date DATE;
1257
1258 l_old_project_type VARCHAR2(80);
1259
1260 -- anlee
1261 -- patchset K changes
1262 l_revaluate_funding_flag VARCHAR2(1);
1263 l_include_gains_losses_flag VARCHAR2(1);
1264 -- End of changes
1265
1266 l_err_code NUMBER;
1267 l_err_stage VARCHAR2(2000);
1268 l_err_stack VARCHAR2(2000);
1269
1270 x_err_code NUMBER;
1271 x_err_stage VARCHAR2(2000);
1272 x_err_stack VARCHAR2(2000);
1273
1274
1275 CURSOR cur_project
1276 IS
1277 SELECT rowid, project_type, carrying_out_organization_id, public_sector_flag, segment1, location_id,
1278 Rev_ind_sch_fixed_date, Inv_ind_sch_fixed_date, distribution_rule,
1279 --bug 3068781
1280 multi_currency_billing_flag,projfunc_currency_code,
1281 PROJFUNC_BIL_RATE_TYPE, PROJECT_BIL_RATE_TYPE, FUNDING_RATE_TYPE,
1282 PROJFUNC_BIL_RATE_DATE_CODE, PROJECT_BIL_RATE_DATE_CODE, FUNDING_RATE_DATE_CODE,
1283 BTC_COST_BASE_REV_CODE
1284 --bug 3068781
1285 --bug 4308335
1286 ,cc_process_labor_flag
1287 ,cc_process_nl_flag
1288 ,labor_tp_schedule_id
1289 ,nl_tp_schedule_id
1290 ,labor_tp_fixed_date
1291 ,nl_tp_fixed_date
1292 --bug 4308335
1293 ,nvl(date_eff_funds_consumption,'N') --federal bug#5511353
1294 ,enable_top_task_customer_flag --federal bug#5511353
1295 ,ar_rec_notify_flag -- 7508661 : EnC
1296 ,auto_release_pwp_inv -- 7508661 : EnC
1297 FROM pa_projects_all
1298 WHERE project_id = p_project_id;
1299
1300 CURSOR cur_pa_tasks IS
1301 SELECT Rev_ind_sch_fixed_date, Inv_ind_sch_fixed_date
1302 FROM pa_tasks
1303 WHERE project_id = p_project_id;
1304
1305 CURSOR cur_project_type_class
1306 IS
1307 SELECT project_type_class_code
1308 FROM pa_project_types_all
1309 WHERE project_type = p_project_type;
1310
1311 CURSOR cur_bill_flag( c_work_type_id NUMBER )
1312 IS
1313 SELECT billable_capitalizable_flag
1314 FROM pa_work_types_vl
1315 WHERE work_type_id = c_work_type_id;
1316
1317 CURSOR cur_dist_rule
1318 IS
1319 /* SELECT r.distribution_rule
1320 FROM pa_project_type_distributions d, pa_distribution_rules r
1321 WHERE d.distribution_rule = r.distribution_rule
1322 AND project_type = p_project_type
1323 AND default_flag = 'Y';
1324 */
1325 --copied from project_folder1.project_type_mir1 when-validate-item validation trigger.
1326
1327 select distribution_rule
1328 from pa_project_type_distributions
1329 where project_type = p_project_type
1330 and default_flag = 'Y';
1331
1332
1333 l_Inv_ind_sch_fixed_date1 DATE;
1334 l_Rev_ind_sch_fixed_date1 DATE;
1335
1336 l_Inv_ind_sch_fixed_date2 DATE;
1337 l_Rev_ind_sch_fixed_date2 DATE;
1338
1339 l_distribution_rule VARCHAR2(20);
1340 l_old_distribution_rule VARCHAR2(20);
1341
1342 l_row_id VARCHAR2(18);
1343 l_proj_number VARCHAR2(80);
1344
1345 l_cc_process_labor_flag VARCHAR2(1);
1346 l_cc_process_nl_flag VARCHAR2(1);
1347 l_labor_tp_schedule_id NUMBER;
1348 l_nl_tp_schedule_id NUMBER;
1349 l_labor_tp_fixed_date DATE;
1350 l_nl_tp_fixed_date DATE;
1351
1352 l_location_id NUMBER;
1353 x_rowid VARCHAR2(18);
1354 l_city_name VARCHAR2(250);
1355 l_country_code VARCHAR2(250);
1356 l_country_name VARCHAR2(250);
1357
1358 /* Type of l_region_name has been changed to %TYPE from varchar2 for the UTF8 change */
1359 l_region_name hr_locations_all.region_1%TYPE;
1360 l_error_message_code VARCHAR2(250);
1361 l_old_organization_id NUMBER;
1362 l_billable_flag VARCHAR2(1);
1363 l_public_sector_flag VARCHAR2(1);
1364
1365 --PA L 2872708
1366 l_asset_allocation_method VARCHAR2(30);
1367 l_CAPITAL_EVENT_PROCESSING VARCHAR2(30);
1368 l_CINT_RATE_SCH_ID NUMBER;
1369 --PA L 2872708
1370
1371 --federal.Bug#5511353
1372 l_date_eff_funds_consumption VARCHAR2(1);
1373 --federal.Bug#5511353
1374
1375 l_ar_rec_notify_flag VARCHAR2(1); -- 7508661 : EnC
1376 l_auto_release_pwp_inv VARCHAR2(1); -- 7508661 : EnC
1377
1378 --bug 3068781 maansari
1379 l_multi_currency_billing_flag VARCHAR2(1); --derived value
1380 l_multi_currency_billing_flag2 VARCHAR2(1); --old value from database
1381 l_PROJFUNC_BIL_RATE_DATE_CODE VARCHAR2(30);
1382 l_PROJECT_BIL_RATE_DATE_CODE VARCHAR2(30);
1383 l_FUNDING_RATE_DATE_CODE VARCHAR2(30);
1384 l_PROJFUNC_BIL_RATE_TYPE VARCHAR2(30);
1385 l_PROJECT_BIL_RATE_TYPE VARCHAR2(30);
1386 l_FUNDING_RATE_TYPE VARCHAR2(30);
1387 l_BTC_COST_BASE_REV_CODE VARCHAR2(90);
1388 l_projfunc_currency_code VARCHAR2(15);
1389 --end bug 3068781
1390 l_warnings_only_flag VARCHAR2(1) := 'N'; --bug3134205
1391
1392 --sunkalya federal
1393
1394 L_old_TOP_TASK_FLAG VARCHAR2(1) := 'N';
1395 l_old_funds_flag VARCHAR2(1) := 'N';
1396 --sunkalya federal
1397
1398 BEGIN
1399 pa_debug.init_err_stack ('PA_PROJ_TEMPLATE_SETUP_PVT.Update_Project_Template');
1400
1401 IF (p_debug_mode = 'Y') THEN
1402 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Update_Project_Template begin');
1403 END IF;
1404
1405 IF (p_commit = FND_API.G_TRUE) THEN
1406 savepoint Update_Project_Template;
1407 END IF;
1408
1409 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
1410 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1411 END IF;
1412
1413 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
1414 FND_MSG_PUB.initialize;
1415 END IF;
1416
1417 --check project type change
1418 OPEN cur_project;
1419 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,
1420 --bug 3068781
1421 l_multi_currency_billing_flag2,l_projfunc_currency_code,
1422 l_PROJFUNC_BIL_RATE_TYPE, l_PROJECT_BIL_RATE_TYPE, l_FUNDING_RATE_TYPE,
1423 l_PROJFUNC_BIL_RATE_DATE_CODE, l_PROJECT_BIL_RATE_DATE_CODE, l_FUNDING_RATE_DATE_CODE,
1424 l_BTC_COST_BASE_REV_CODE
1425 --bug 3068781
1426 --bug4308335
1427 ,l_cc_process_labor_flag
1428 ,l_cc_process_nl_flag
1429 ,l_labor_tp_schedule_id
1430 ,l_nl_tp_schedule_id
1431 ,l_labor_tp_fixed_date
1432 ,l_nl_tp_fixed_date
1433 --end bug4308335
1434 ,l_old_funds_flag --federal bug#5511353
1435 ,L_old_TOP_TASK_FLAG --federal bug#5511353
1436 ,l_ar_rec_notify_flag -- 7508661 : EnC
1437 ,l_auto_release_pwp_inv -- 7508661 : EnC
1438 ;
1439
1440 CLOSE cur_project;
1441
1442 OPEN cur_pa_tasks;
1443 FETCH cur_pa_tasks INTO l_Rev_ind_sch_fixed_date2, l_Inv_ind_sch_fixed_date2;
1444 CLOSE cur_pa_tasks;
1445
1446 --Check for not null
1447 PA_PROJ_TEMPLATE_SETUP_UTILS.Check_Template_attr_req(
1448 p_project_number => p_project_number
1449 ,p_project_name => p_project_name
1450 ,p_project_type => p_project_type
1451 ,p_organization_id => p_organization_id
1452 ,x_return_status => l_return_status
1453 ,x_error_msg_code => l_error_msg_code
1454 );
1455
1456 IF l_return_status = FND_API.G_RET_STS_ERROR
1457 THEN
1458 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1459 p_msg_name => l_error_msg_code);
1460 x_msg_data := l_error_msg_code;
1461 x_return_status := 'E';
1462 RAISE FND_API.G_EXC_ERROR;
1463 END IF;
1464
1465 --Check proj number change
1466 IF l_proj_number <> p_project_number
1467 THEN
1468 pa_project_utils.change_proj_num_ok (
1469 p_project_id,
1470 l_err_code,
1471 l_err_stage,
1472 l_err_stack);
1473 if l_err_code <> 0 Then
1474 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1475 p_msg_name => l_err_stage );
1476 x_msg_data := x_err_stage;
1477 x_return_status := 'E';
1478 RAISE FND_API.G_EXC_ERROR;
1479 end If;
1480 END IF;
1481
1482 --check for project number uniqueness
1483 IF pa_project_utils.check_unique_project_number (x_project_number => p_project_number
1484 ,x_rowid => l_row_id ) = 0
1485 THEN
1486 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1487 p_msg_name => 'PA_SETUP_TMPL_NUM_NOT_UNIQUE' );
1488 x_msg_data := 'PA_SETUP_TMPL_NUM_NOT_UNIQUE';
1489 x_return_status := 'E';
1490 RAISE FND_API.G_EXC_ERROR;
1491 END IF;
1492
1493 --check for project name uniqueness
1494 IF pa_project_utils.check_unique_project_name (x_project_name => p_project_name
1495 ,x_rowid => l_row_id ) = 0
1496 THEN
1497 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1498 p_msg_name => 'PA_SETUP_TMPL_NAME_NOT_UNIQUE' );
1499 x_msg_data := 'PA_SETUP_TMPL_NAME_NOT_UNIQUE';
1500 x_return_status := 'E';
1501 RAISE FND_API.G_EXC_ERROR;
1502 END IF;
1503
1504 -- anlee
1505 -- Project Long Name changes
1506 IF pa_project_utils.check_unique_long_name (p_long_name, l_row_id ) = 0
1507 THEN
1508 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1509 p_msg_name => 'PA_PR_EPR_LONG_NAME_NOT_UNIQUE' );
1510 x_msg_data := 'PA_PR_EPR_LONG_NAME_NOT_UNIQUE';
1511 x_return_status := 'E';
1512 RAISE FND_API.G_EXC_ERROR;
1513 END IF;
1514 -- End of changes
1515
1516 IF p_effective_from_date = TO_DATE( '01-01-1000', 'DD-MM-YYYY' )
1517 THEN
1518 l_effective_from_date := null;
1519 ELSE
1520 l_effective_from_date := p_effective_from_date;
1521 END IF;
1522
1523 IF p_effective_to_date = TO_DATE( '01-01-1000', 'DD-MM-YYYY' )
1524 THEN
1525 l_effective_to_date := null;
1526 ELSE
1527 l_effective_to_date := p_effective_to_date;
1528 END IF;
1529
1530 IF l_effective_from_date IS NOT NULL AND
1531 l_effective_to_date IS NOT NULL
1532 THEN
1533 --Check the start and end dates
1534 IF l_effective_from_date > l_effective_to_date
1535 THEN
1536 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1537 p_msg_name => 'PA_SETUP_CHK_ST_EN_DATE');
1538 x_msg_data := 'PA_SETUP_CHK_ST_EN_DATE';
1539 x_return_status := 'E';
1540 RAISE FND_API.G_EXC_ERROR;
1541 END IF;
1542 ELSIF l_effective_from_date IS NULL AND
1543 l_effective_to_date IS NOT NULL
1544 THEN
1545 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1546 p_msg_name => 'PA_SETUP_ST_DT_WO_EN_DT');
1547 x_msg_data := 'PA_SETUP_ST_DT_WO_EN_DT';
1548 x_return_status := 'E';
1549 RAISE FND_API.G_EXC_ERROR;
1550 END IF;
1551
1552 PA_PROJ_TEMPLATE_SETUP_UTILS.Get_Project_Type_Defaults(
1553 p_project_type => p_project_type
1554 ,x_Status_code => l_Status_code
1555 ,x_service_type_code => l_service_type_code
1556 ,x_cost_ind_rate_sch_id => l_cost_ind_rate_sch_id
1557 ,x_labor_sch_type => l_labor_sch_type
1558 ,x_labor_bill_rate_org_id => l_labor_bill_rate_org_id
1559 ,x_labor_std_bill_rate_schdl => l_labor_std_bill_rate_schdl
1560 ,x_non_labor_sch_type => l_non_labor_sch_type
1561 ,x_non_labor_bill_rate_org_id => l_non_labor_bill_rate_org_id
1562 ,x_nl_std_bill_rate_schdl => l_nl_std_bill_rate_schdl
1563 ,x_rev_ind_rate_sch_id => l_rev_ind_rate_sch_id
1564 ,x_inv_ind_rate_sch_id => l_inv_ind_rate_sch_id
1565 ,x_labor_invoice_format_id => l_labor_invoice_format_id
1566 ,x_non_labor_invoice_format_id => l_non_labor_invoice_format_id
1567 ,x_Burden_cost_flag => l_Burden_cost_flag
1568 ,x_interface_asset_cost_code => l_interface_asset_cost_code
1569 ,x_cost_sch_override_flag => l_cost_sch_override_flag
1570 ,x_billing_offset => l_billing_offset
1571 ,x_billing_cycle_id => l_billing_cycle_id
1572 ,x_cc_prvdr_flag => l_cc_prvdr_flag
1573 ,x_bill_job_group_id => l_bill_job_group_id
1574 ,x_cost_job_group_id => l_cost_job_group_id
1575 ,x_work_type_id => l_work_type_id
1576 ,x_role_list_id => l_role_list_id
1577 ,x_unassigned_time => l_unassigned_time
1578 ,x_emp_bill_rate_schedule_id => l_emp_bill_rate_schedule_id
1579 ,x_job_bill_rate_schedule_id => l_job_bill_rate_schedule_id
1580 ,x_budgetary_override_flag => l_budgetary_override_flag
1581 ,x_baseline_funding_flag => l_baseline_funding_flag
1582 ,x_non_lab_std_bill_rt_sch_id => l_non_lab_std_bill_rt_sch_id
1583 ,x_project_type_class_code => l_project_type_class_code
1584 -- anlee
1585 -- Changes for patchset K
1586 ,x_revaluate_funding_flag => l_revaluate_funding_flag
1587 ,x_include_gains_losses_flag => l_include_gains_losses_flag
1588 -- End of changes
1589 --PA L Changes 2872708
1590 ,x_asset_allocation_method => l_asset_allocation_method
1591 ,x_CAPITAL_EVENT_PROCESSING => l_CAPITAL_EVENT_PROCESSING
1592 ,x_CINT_RATE_SCH_ID => l_CINT_RATE_SCH_ID
1593 --PA L Changes 2872708
1594 --sunkalya.federal.Bug# Bug#5511353
1595 ,x_date_eff_funds_flag => l_date_eff_funds_consumption
1596 ,x_ar_rec_notify_flag => l_ar_rec_notify_flag -- 7508661 : EnC
1597 ,x_auto_release_pwp_inv => l_auto_release_pwp_inv -- 7508661 : EnC
1598 ,x_return_status => l_return_status
1599 ,x_error_msg_code => l_error_msg_code
1600 );
1601
1602 IF l_return_status = FND_API.G_RET_STS_ERROR
1603 THEN
1604 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1605 p_msg_name => l_error_msg_code);
1606 x_msg_data := l_error_msg_code;
1607 x_return_status := 'E';
1608 RAISE FND_API.G_EXC_ERROR;
1609 END IF;
1610
1611 --Validation from Projects form
1612 IF l_unassigned_time = 'N'
1613 THEN
1614 l_cc_process_labor_flag := 'N';
1615 l_cc_process_nl_flag := 'N';
1616 l_labor_tp_schedule_id := null;
1617 l_nl_tp_schedule_id := null;
1618 l_labor_tp_fixed_date := null;
1619 l_nl_tp_fixed_date := null;
1620 END IF;
1621
1622 IF l_project_type_class_code = 'CONTRACT'
1623 THEN
1624 OPEN cur_dist_rule;
1625 FETCH cur_dist_rule INTO l_distribution_rule;
1626 CLOSE cur_dist_rule;
1627 /* Pa_project_utils.check_dist_rule_chg_ok( p_project_id,
1628 l_old_distribution_rule,
1629 l_distribution_rule,
1630 x_err_code,
1631 x_err_stage,
1632 x_err_stack );
1633 IF If x_err_code != 0 Then
1634 THEN
1635 l_distribution_rule := l_old_distribution_rule;
1636 END IF;
1637 */ -- no need here it should done when the user changes distribution rule on UI.
1638 END IF;
1639
1640 IF l_old_project_type <> p_project_type AND p_project_type IS NOT NULL
1641 THEN
1642 /*OPEN cur_project_type_class;
1643 FETCH cur_project_type_class INTO l_project_type_class_code;
1644 CLOSE cur_project_type_class;*/
1645
1646 DELETE FROM PA_BUDGETARY_CONTROL_OPTIONS
1647 WHERE PROJECT_ID = P_PROJECT_ID;
1648
1649 IF l_project_type_class_code IS NOT NULL AND l_project_type_class_code <> 'CONTRACT'
1650 THEN
1651 IF pa_project_utils.check_proj_funding ( p_project_id ) <> 0
1652 THEN
1653 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1654 p_msg_name => 'PA_PR_CANT_CHG_DIR_TO_INDIR' );
1655 x_msg_data := 'PA_PR_CANT_CHG_DIR_TO_INDIR';
1656 x_return_status := 'E';
1657 RAISE FND_API.G_EXC_ERROR;
1658 END IF;
1659 l_labor_sch_type := null;
1660 l_non_labor_sch_type := null;
1661 END IF;
1662
1663 IF l_non_labor_sch_type = 'B' AND l_labor_sch_type = 'B'
1664 THEN
1665 l_rev_ind_rate_sch_id := null;
1666 l_inv_ind_rate_sch_id := null;
1667 l_Rev_ind_sch_fixed_date1 := null;
1668 l_Rev_ind_sch_fixed_date2 := null;
1669 l_Inv_ind_sch_fixed_date1 := null;
1670 l_Inv_ind_sch_fixed_date2 := null;
1671 END IF;
1672
1673 IF l_non_labor_sch_type = 'I'
1674 THEN
1675 l_nl_std_bill_rate_schdl := null;
1676 l_non_labor_bill_rate_org_id := null;
1677 END IF;
1678
1679 IF l_labor_sch_type = 'I'
1680 THEN
1681 l_emp_bill_rate_Schedule_id := null;
1682 l_job_bill_rate_Schedule_id := null;
1683 END IF;
1684
1685 /* pa_project_utils.change_pt_org_ok( x_project_id => p_project_id,
1686 x_err_code => l_err_code,
1687 x_err_stage => l_err_stage,
1688 x_err_stack => l_err_stack );
1689 IF l_err_code <> 0
1690 THEN
1691 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1692 p_msg_name => 'PA_PR_CANT_CHG_PROJ_TYPE' );
1693 x_msg_data := 'PA_PR_CANT_CHG_DIR_TO_INDIR';
1694 x_return_status := 'E';
1695 RAISE FND_API.G_EXC_ERROR;
1696 END IF;
1697 */ --this code is commented in forms PAXPREPR.fmb
1698 END IF;
1699
1700 --Location validation should be done if organization is changed.
1701 /* The following validation is not performed in forms during a project/template update
1702 So we dont need to do this here
1703 IF p_organization_id <> l_old_organization_id
1704 THEN
1705 --Organization Location Validations
1706 pa_location_utils.Get_ORG_Location_Details
1707 (p_organization_id => p_organization_id,
1708 x_country_name => l_country_name,
1709 x_city => l_city_name,
1710 x_region => l_region_name,
1711 x_country_code => l_country_code,
1712 x_return_status => l_return_status,
1713 x_error_message_code => l_error_message_code);
1714
1715 IF l_return_status = FND_API.G_RET_STS_ERROR
1716 THEN
1717 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1718 p_msg_name => l_error_message_code);
1719 x_msg_data := l_error_msg_code;
1720 x_return_status := 'E';
1721 RAISE FND_API.G_EXC_ERROR;
1722 END IF;
1723
1724 pa_location_utils.check_location_exists(
1725 p_country_code => l_country_code,
1726 p_city => l_city_name,
1727 p_region => l_region_name,
1728 x_return_status => l_return_status,
1729 x_location_id => l_location_id);
1730
1731 If l_location_id is null then
1732
1733 If l_city_name is not null
1734 and l_region_name is not null
1735 If l_country_code is not null then
1736
1737 pa_locations_pkg.INSERT_ROW(
1738 p_CITY => l_city_name,
1739 p_REGION => l_region_name,
1740 p_COUNTRY_CODE => l_country_code,
1741 p_CREATION_DATE => SYSDATE,
1742 p_CREATED_BY => FND_GLOBAL.USER_ID,
1743 p_LAST_UPDATE_DATE => SYSDATE,
1744 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1745 p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
1746 X_ROWID => x_rowid,
1747 X_LOCATION_ID => l_location_id);
1748
1749 end if;
1750 end if;
1751 END IF;
1752 */
1753
1754
1755 --Validdate attribute change from WHEN-VALIDATE-RECORD projects form
1756 --The following validation is done in forms when a template is created.
1757
1758 IF ( p_organization_id <> l_old_organization_id OR l_old_project_type <> p_project_type )
1759 THEN
1760 pa_project_utils2.validate_attribute_change
1761 ('ORGANIZATION_VALIDATION' -- X_context
1762 , 'INSERT' -- X_insert_update_mode
1763 , 'SELF_SERVICE' -- X_calling_module
1764 , p_project_id -- X_project_id
1765 , NULL -- X_task_id
1766 , l_old_organization_id -- X_old_value
1767 , p_organization_id -- X_new_value
1768 , p_project_type -- X_project_type
1769 , null -- x_start_date
1770 , null
1771 , l_public_sector_flag -- X_public_sector_flag
1772 , NULL -- X_task_manager_person_id
1773 , NULL -- X_service_type
1774 , NULL -- X_task_start_date
1775 , NULL -- X_task_end_date
1776 , FND_GLOBAL.USER_ID -- X_entered_by_user_id
1777 , null -- X_attribute_category
1778 , null -- X_attribute1
1779 , null -- X_attribute2
1780 , null -- X_attribute3
1781 , null -- X_attribute4
1782 , null -- X_attribute5
1783 , null -- X_attribute6
1784 , null -- X_attribute7
1785 , null -- X_attribute8
1786 , null -- X_attribute9
1787 , null -- X_attribute10
1788 , null -- X_pm_project_code
1789 , null -- X_pm_project_reference
1790 , NULL -- X_pm_task_reference
1791 , 'Y' -- X_functional_security_flag
1792 , l_warnings_only_flag --bug3134205
1793 , x_err_code -- X_err_code
1794 , x_err_stage -- X_err_stage
1795 , x_err_stack); -- X_err_stack
1796
1797 /* Commented the <> 15 condition for bug 2981386 */
1798 if x_err_code <> 0 /* and x_err_code <> 15 */ Then --modified for bug 2393975
1799
1800 if l_err_stage = 'PA_INVALID_PT_CLASS_ORG' then
1801
1802 select meaning
1803 into l_project_type_class_code
1804 from pa_project_types pt
1805 , pa_lookups lps
1806 where pt.project_type = p_project_type
1807 and lps.lookup_type(+) = 'PROJECT TYPE CLASS'
1808 and lps.lookup_code(+) = pt.project_type_class_code;
1809
1810 /* Code addition for bug 2981386 starts */
1811 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1812 p_msg_name => 'PA_INVALID_PT_CLASS_ORG',
1813 p_token1 => 'PT_CLASS',
1814 p_value1 => l_project_type_class_code);
1815 else
1816 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1817 p_msg_name => x_err_stage);
1818
1819 end if;
1820 /* Code addition for bug 2981386 ends */
1821
1822 /* Commented for bug 2981386
1823 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1824 p_msg_name => x_err_stage,
1825 p_token1 => 'PT_CLASS',
1826 p_value1 => l_project_type_class_code );
1827 */
1828 x_msg_data := x_err_stage;
1829 x_return_status := 'E';
1830 RAISE FND_API.G_EXC_ERROR;
1831 End If;
1832 END IF; --validate attribute change
1833
1834 --maansari bug 3068806
1835 IF p_project_type IS NOT NULL AND
1836 p_project_type <> l_old_project_type AND
1837 l_cc_prvdr_flag = 'Y' AND
1838 l_multi_currency_billing_flag2 = 'Y'
1839 THEN
1840 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1841 p_msg_name => 'PA_PR_CANT_CHG_IC_BIL_PT');
1842 x_msg_data := 'PA_PR_CANT_CHG_IC_BIL_PT';
1843 x_return_status := 'E';
1844 RAISE FND_API.G_EXC_ERROR;
1845 END IF;
1846 --end maansari bug 3068806
1847
1848 --bug 3068781
1849
1850 DECLARE
1851 CURSOR cur_job_cur IS SELECT rate_sch_currency_code FROM pa_std_bill_rate_schedules_all
1852 WHERE bill_rate_sch_id = l_job_bill_rate_schedule_id ;
1853
1854 CURSOR cur_emp_cur IS SELECT rate_sch_currency_code FROM pa_std_bill_rate_schedules_all
1855 WHERE bill_rate_sch_id = l_emp_bill_rate_schedule_id ;
1856
1857 CURSOR cur_nl_cur IS SELECT rate_sch_currency_code FROM pa_std_bill_rate_schedules_all
1858 WHERE bill_rate_sch_id = l_non_lab_std_bill_rt_sch_id ;
1859
1860 CURSOR cur_impl IS SELECT default_rate_type FROM pa_implementations;
1861
1862 x_job_rate_sch_currency VARCHAR2(30);
1863 x_emp_rate_sch_currency VARCHAR2(30);
1864 x_nl_rate_sch_currency VARCHAR2(30);
1865 x_default_rate_type VARCHAR2(30);
1866
1867 BEGIN
1868
1869 IF l_cc_prvdr_flag = 'N' --This is not required if the project type is IC billing. bug 2179904
1870 THEN
1871 OPEN cur_job_cur;
1872 FETCH cur_job_cur INTO x_job_rate_sch_currency ;
1873 CLOSE cur_job_cur;
1874
1875 OPEN cur_emp_cur;
1876 FETCH cur_emp_cur INTO x_emp_rate_sch_currency ;
1877 CLOSE cur_emp_cur;
1878
1879 OPEN cur_nl_cur;
1880 FETCH cur_nl_cur INTO x_nl_rate_sch_currency ;
1881 CLOSE cur_nl_cur;
1882
1883 IF x_job_rate_sch_currency is not Null and
1884 x_job_rate_sch_currency <> l_projfunc_currency_code
1885 THEN
1886 l_multi_currency_billing_flag := 'Y';
1887 ELSIF x_emp_rate_sch_currency is not Null and
1888 x_emp_rate_sch_currency <> l_projfunc_currency_code
1889 THEN
1890 l_multi_currency_billing_flag := 'Y';
1891 ELSIF x_nl_rate_sch_currency is not Null and
1892 x_nl_rate_sch_currency <> l_projfunc_currency_code
1893 THEN
1894 l_multi_currency_billing_flag := 'Y';
1895 END IF;
1896
1897 END IF;
1898
1899 IF l_cc_prvdr_flag = 'N' AND
1900 l_multi_currency_billing_flag2 = 'N' AND
1901 NVL( l_multi_currency_billing_flag, 'N') = 'Y'
1902 THEN
1903 OPEN cur_impl;
1904 FETCH cur_impl INTO x_default_rate_type ;
1905 CLOSE cur_impl;
1906
1907 l_PROJFUNC_BIL_RATE_TYPE := x_default_rate_type;
1908 l_PROJECT_BIL_RATE_TYPE := x_default_rate_type;
1909 l_FUNDING_RATE_TYPE := x_default_rate_type;
1910
1911 l_PROJFUNC_BIL_RATE_DATE_CODE := 'PA_INVOICE_DATE';
1912 l_PROJECT_BIL_RATE_DATE_CODE := 'PA_INVOICE_DATE';
1913 l_FUNDING_RATE_DATE_CODE := 'PA_INVOICE_DATE';
1914 l_BTC_COST_BASE_REV_CODE := 'EXP_TRANS_CURR';
1915
1916 UPDATE pa_project_customers
1917 SET inv_rate_type = x_default_rate_type
1918 WHERE project_id = p_project_id;
1919 ELSE
1920 l_PROJFUNC_BIL_RATE_TYPE := l_PROJFUNC_BIL_RATE_TYPE;
1921 l_PROJECT_BIL_RATE_TYPE := l_PROJECT_BIL_RATE_TYPE;
1922 l_FUNDING_RATE_TYPE := l_FUNDING_RATE_TYPE;
1923
1924 l_PROJFUNC_BIL_RATE_DATE_CODE := l_PROJFUNC_BIL_RATE_DATE_CODE;
1925 l_PROJECT_BIL_RATE_DATE_CODE := l_PROJECT_BIL_RATE_DATE_CODE;
1926 l_FUNDING_RATE_DATE_CODE := l_FUNDING_RATE_DATE_CODE;
1927 l_BTC_COST_BASE_REV_CODE := l_BTC_COST_BASE_REV_CODE;
1928
1929 END IF;
1930 END;
1931 --end bug 3068781
1932
1933
1934
1935 --I cant use table handler for update; It may update the unwanted columns as well wih null
1936 UPDATE pa_projects_all
1937 SET SEGMENT1 = p_project_number,
1938 NAME = p_project_name,
1939 description = p_description,
1940 PROJECT_TYPE = p_project_type,
1941 carrying_out_organization_id = p_organization_id,
1942 TEMPLATE_START_DATE_ACTIVE = l_effective_from_date,
1943 TEMPLATE_END_DATE_ACTIVE = l_effective_to_date,
1944 -- not done in project forms PROJECT_STATUS_CODE = l_Status_code,
1945 LABOR_INVOICE_FORMAT_ID = l_labor_invoice_format_id,
1946 NON_LABOR_INVOICE_FORMAT_ID = l_non_labor_invoice_format_id,
1947 BILLING_OFFSET = l_billing_offset,
1948 NON_LABOR_STD_BILL_RATE_SCHDL = l_nl_std_bill_rate_schdl,
1949 NON_LABOR_BILL_RATE_ORG_ID = l_non_labor_bill_rate_org_id,
1950 Non_Labor_Schedule_Fixed_Date = DECODE( l_non_labor_sch_type, 'I', null, Non_Labor_Schedule_Fixed_Date ),
1951 Non_Labor_Schedule_Discount = DECODE( l_non_labor_sch_type, 'I', null, Non_Labor_Schedule_Discount ),
1952 COST_IND_RATE_SCH_ID = l_cost_ind_rate_sch_id,
1953 REV_IND_RATE_SCH_ID = l_rev_ind_rate_sch_id,
1954 REV_IND_SCH_FIXED_DATE = l_REV_IND_SCH_fixed_date1,
1955 INV_IND_RATE_SCH_ID = l_inv_ind_rate_sch_id,
1956 INV_IND_SCH_FIXED_DATE = l_INV_IND_SCH_FIXED_date1,
1957 LABOR_SCH_TYPE = l_labor_sch_type,
1958 NON_LABOR_SCH_TYPE = l_non_labor_sch_type,
1959 BILLING_CYCLE_ID = l_billing_cycle_id,
1960 BILL_JOB_GROUP_ID = l_bill_job_group_id,
1961 COST_JOB_GROUP_ID = l_cost_job_group_id,
1962 ROLE_LIST_ID = l_role_list_id,
1963 WORK_TYPE_ID = l_work_type_id,
1964 JOB_BILL_RATE_SCHEDULE_ID = l_job_bill_rate_schedule_id,
1965 EMP_BILL_RATE_SCHEDULE_ID = l_emp_bill_rate_schedule_id,
1966 labor_schedule_fixed_date = DECODE( l_labor_sch_type, 'I', null, labor_schedule_fixed_date ),
1967 labor_schedule_discount = DECODE( l_labor_sch_type, 'I', null, labor_schedule_discount ),
1968 non_lab_std_bill_rt_sch_id = l_non_lab_std_bill_rt_sch_id,
1969
1970 labor_std_bill_rate_schdl = l_labor_std_bill_rate_schdl,
1971 labor_bill_rate_org_id = l_labor_bill_rate_org_id,
1972 cc_process_labor_flag = l_cc_process_labor_flag,
1973 cc_process_nl_flag = l_cc_process_nl_flag,
1974 labor_tp_schedule_id = l_labor_tp_schedule_id ,
1975 nl_tp_schedule_id = l_nl_tp_schedule_id ,
1976 labor_tp_fixed_date = l_labor_tp_fixed_date ,
1977 nl_tp_fixed_date = l_nl_tp_fixed_date ,
1978 location_id = l_location_id,
1979 distribution_rule = l_distribution_rule,
1980 -- anlee
1981 -- patchset K changes
1982 revaluate_funding_flag = l_revaluate_funding_flag,
1983 include_gains_losses_flag = l_include_gains_losses_flag,
1984 -- End of changes
1985 --PA K Project Access Changes
1986 security_level = p_security_level,
1987 -- anlee
1988 -- Project Long Name changes
1989 long_name = p_long_name,
1990 -- End of changes
1991 --bug 3068781
1992 multi_currency_billing_flag = NVL( l_multi_currency_billing_flag,l_multi_currency_billing_flag2 ),
1993 PROJFUNC_BIL_RATE_TYPE = l_PROJFUNC_BIL_RATE_TYPE,
1994 PROJECT_BIL_RATE_TYPE = l_PROJECT_BIL_RATE_TYPE,
1995 FUNDING_RATE_TYPE = l_FUNDING_RATE_TYPE,
1996 PROJFUNC_BIL_RATE_DATE_CODE = l_PROJFUNC_BIL_RATE_DATE_CODE,
1997 PROJECT_BIL_RATE_DATE_CODE = l_PROJECT_BIL_RATE_DATE_CODE,
1998 FUNDING_RATE_DATE_CODE = l_FUNDING_RATE_DATE_CODE,
1999 BTC_COST_BASE_REV_CODE = l_BTC_COST_BASE_REV_CODE,
2000 --bug 3068781
2001 --
2002 --PA L 2872708
2003 asset_allocation_method = l_asset_allocation_method,
2004 CAPITAL_EVENT_PROCESSING = l_CAPITAL_EVENT_PROCESSING,
2005 CINT_RATE_SCH_ID = l_CINT_RATE_SCH_ID,
2006 --PA L 2872708
2007 record_version_number = NVL( record_version_number, 1 ) + 1,
2008 --sunkalya.federal changes. Bug#5511353
2009 date_eff_funds_consumption = nvl(l_date_eff_funds_consumption,'N')
2010 --sunkalya.federal changes. Bug#5511353
2011
2012 WHERE project_id = p_project_id;
2013
2014 --Sunkalya federal changes. Bug#5511353
2015
2016 IF ( l_old_project_type <> p_project_type ) THEN
2017
2018 DECLARE
2019
2020 hghst_ctr_cust_id NUMBER;
2021 l_return_status VARCHAR2(10);
2022 l_msg_count NUMBER := 0;
2023 l_msg_data VARCHAR2(2000);
2024
2025 BEGIN
2026
2027 IF l_date_eff_funds_consumption ='Y' THEN
2028
2029 UPDATE pa_project_customers
2030 SET
2031 CUSTOMER_BILL_SPLIT = NULL
2032 WHERE
2033 PROJECT_ID = p_project_id;
2034
2035
2036 ELSIF l_old_funds_flag = 'Y' AND l_old_top_task_flag = 'N' THEN
2037
2038
2039 --This api will determine which customer to be made as 100% contributor.
2040 PA_CUSTOMERS_CONTACTS_UTILS.Get_Highest_Contr_Fed_Cust(
2041 P_API_VERSION => 1.0
2042 , P_INIT_MSG_LIST => 'T'
2043 , P_COMMIT => 'F'
2044 , P_VALIDATE_ONLY => 'F'
2045 , P_VALIDATION_LEVEL => 100
2046 , P_DEBUG_MODE => 'N'
2047 , p_calling_module => 'AMG'
2048 , p_project_id => p_project_id
2049 , x_highst_contr_cust_id => hghst_ctr_cust_id
2050 , x_return_status => l_return_status
2051 , x_msg_count => l_msg_count
2052 , x_msg_data => l_msg_data );
2053
2054 IF hghst_ctr_cust_id IS NOT NULL AND l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2055
2056 UPDATE pa_project_customers SET customer_bill_split = 100
2057 WHERE customer_id = hghst_ctr_cust_id AND project_id = p_project_id;
2058
2059 UPDATE pa_project_customers SET customer_bill_split = 0
2060 WHERE customer_id <> hghst_ctr_cust_id AND project_id = p_project_id;
2061 END IF;
2062 END IF;
2063
2064
2065 END;
2066
2067 END IF;
2068
2069 --Federal changes by sunkalya.Bug#5511353.
2070
2071 IF ( l_old_project_type <> p_project_type )
2072 THEN
2073
2074 OPEN cur_bill_flag( l_work_type_id );
2075 FETCH cur_bill_flag INTO l_billable_flag;
2076 CLOSE cur_bill_flag;
2077
2078 UPDATE pa_tasks
2079 SET work_type_id = l_work_type_id,
2080 billable_flag = l_billable_flag,
2081 emp_bill_rate_schedule_id = l_emp_bill_rate_schedule_id,
2082 job_bill_rate_schedule_id = l_job_bill_rate_schedule_id,
2083 labor_schedule_fixed_date = DECODE( l_labor_sch_type, 'I', null, labor_schedule_fixed_date ),
2084 labor_schedule_discount = DECODE( l_labor_sch_type, 'I', null, labor_schedule_discount ),
2085
2086 --bug 2101726
2087 labor_sch_type = l_labor_sch_type,
2088 service_type_code = l_service_type_code,
2089 cost_ind_rate_sch_id = l_cost_ind_rate_sch_id,
2090 non_labor_sch_type = l_non_labor_sch_type,
2091 non_labor_bill_rate_org_id = l_non_labor_bill_rate_org_id,
2092 non_labor_std_bill_rate_schdl = l_nl_std_bill_rate_schdl,
2093 Non_Labor_Schedule_Fixed_Date = DECODE( l_non_labor_sch_type, 'I', null, Non_Labor_Schedule_Fixed_Date ),
2094 Non_Labor_Schedule_Discount = DECODE( l_non_labor_sch_type, 'I', null, Non_Labor_Schedule_Discount ),
2095 rev_ind_rate_sch_id = l_rev_ind_rate_sch_id,
2096 REV_IND_SCH_FIXED_DATE = l_REV_IND_SCH_fixed_date2,
2097 INV_IND_SCH_FIXED_DATE = l_INV_IND_SCH_FIXED_date2,
2098 inv_ind_rate_sch_id = l_inv_ind_rate_sch_id,
2099 labor_bill_rate_org_id = l_labor_bill_rate_org_id,
2100 labor_std_bill_rate_schdl = l_labor_std_bill_rate_schdl,
2101 non_lab_std_bill_rt_sch_id = l_non_lab_std_bill_rt_sch_id
2102
2103 WHERE project_id = p_project_id;
2104
2105 END IF;
2106
2107 x_return_status := FND_API.G_RET_STS_SUCCESS;
2108
2109 IF (p_commit = FND_API.G_TRUE) THEN
2110 COMMIT;
2111 END IF;
2112
2113 IF (p_debug_mode = 'Y') THEN
2114 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Update_Project_Template END');
2115 END IF;
2116 EXCEPTION
2117 when FND_API.G_EXC_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_ERROR;
2122 when FND_API.G_EXC_UNEXPECTED_ERROR then
2123 if p_commit = FND_API.G_TRUE then
2124 rollback to Update_Project_Template;
2125 end if;
2126 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2127 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
2128 p_procedure_name => 'Update_Project_Template',
2129 p_error_text => SUBSTRB(SQLERRM,1,240));
2130 when OTHERS then
2131 if p_commit = FND_API.G_TRUE then
2132 rollback to Update_Project_Template;
2133 end if;
2134 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2135 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
2136 p_procedure_name => 'Update_Project_Template',
2137 p_error_text => SUBSTRB(SQLERRM,1,240));
2138 raise;
2139 END Update_Project_Template;
2140
2141 -- API name : Delete_Project_Template
2142 -- Type : Public API
2143 -- Pre-reqs : None
2144 -- Return Value :
2145 --
2146 -- Parameters
2147 -- p_project_id IN NUMBER,
2148 -- p_record_version_number IN NUMBER,
2149 --
2150
2151 PROCEDURE Delete_Project_Template(
2152 p_api_version IN NUMBER :=1.0,
2153 p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE,
2154 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
2155 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE,
2156 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
2157 p_calling_module IN VARCHAR2 :='SELF_SERVICE',
2158 p_debug_mode IN VARCHAR2 :='N',
2159 p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2160 p_project_id IN NUMBER,
2161 p_record_version_number IN NUMBER,
2162 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2163 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2164 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2165 ) IS
2166 l_api_name CONSTANT VARCHAR(30) := 'Delete_Project_Template';
2167 l_api_version CONSTANT NUMBER := 1.0;
2168
2169 l_return_status VARCHAR2(1);
2170 l_msg_count NUMBER;
2171 l_msg_data VARCHAR2(250);
2172 l_data VARCHAR2(250);
2173 l_msg_index_out NUMBER;
2174 l_error_msg_code VARCHAR2(250);
2175
2176 l_err_code NUMBER;
2177 l_err_stage VARCHAR2(250);
2178 l_err_stack VARCHAR2(250);
2179
2180
2181 BEGIN
2182 pa_debug.init_err_stack ('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Project_Template');
2183
2184 IF (p_debug_mode = 'Y') THEN
2185 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Project_Template begin');
2186 END IF;
2187
2188 IF (p_commit = FND_API.G_TRUE) THEN
2189 savepoint Delete_Project_Template;
2190 END IF;
2191
2192 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2194 END IF;
2195
2196 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2197 FND_MSG_PUB.initialize;
2198 END IF;
2199
2200 --Bug 2947492: The following api call is modified to pass parameters by notation.
2201 Pa_Project_Core.Delete_Project (
2202 x_project_id => p_Project_id,
2203 x_err_code => l_err_code ,
2204 x_err_stage => l_err_stage,
2205 x_err_stack => l_err_stack);
2206
2207 IF l_err_code <> 0
2208 THEN
2209 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2210 p_msg_name => l_err_stage);
2211 x_msg_data := l_err_stage;
2212 x_return_status := 'E';
2213 RAISE FND_API.G_EXC_ERROR;
2214 END IF;
2215
2216 x_return_status := FND_API.G_RET_STS_SUCCESS;
2217
2218 IF (p_commit = FND_API.G_TRUE) THEN
2219 COMMIT;
2220 END IF;
2221
2222 IF (p_debug_mode = 'Y') THEN
2223 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Project_Template END');
2224 END IF;
2225 EXCEPTION
2226 when FND_API.G_EXC_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_ERROR;
2231 when FND_API.G_EXC_UNEXPECTED_ERROR then
2232 if p_commit = FND_API.G_TRUE then
2233 rollback to Delete_Project_Template;
2234 end if;
2235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2236 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
2237 p_procedure_name => 'Delete_Project_Template',
2238 p_error_text => SUBSTRB(SQLERRM,1,240));
2239 when OTHERS then
2240 if p_commit = FND_API.G_TRUE then
2241 rollback to Delete_Project_Template;
2242 end if;
2243 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2244 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
2245 p_procedure_name => 'Delete_Project_Template',
2246 p_error_text => SUBSTRB(SQLERRM,1,240));
2247 raise;
2248 END Delete_Project_Template;
2249
2250 -- API name : Add_Project_Options
2251 -- Type : Public API
2252 -- Pre-reqs : None
2253 -- Return Value :
2254 --
2255 -- Parameters
2256 -- p_project_id IN NUMBER,
2257 -- p_option_copde IN VARCHAR2,
2258 --
2259
2260 PROCEDURE Add_Project_Options(
2261 p_api_version IN NUMBER :=1.0,
2262 p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE,
2263 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
2264 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE,
2265 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
2266 p_calling_module IN VARCHAR2 :='SELF_SERVICE',
2267 p_debug_mode IN VARCHAR2 :='N',
2268 p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2269 p_project_id IN NUMBER,
2270 p_option_code IN VARCHAR2,
2271 p_action IN VARCHAR2 := 'ENABLE',
2272 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2273 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2274 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2275 ) IS
2276 l_api_name CONSTANT VARCHAR(30) := 'Add_Project_Options';
2277 l_api_version CONSTANT NUMBER := 1.0;
2278
2279 l_return_status VARCHAR2(1);
2280 l_msg_count NUMBER;
2281 l_msg_data VARCHAR2(250);
2282 l_data VARCHAR2(250);
2283 l_msg_index_out NUMBER;
2284 l_error_msg_code VARCHAR2(250);
2285
2286 l_err_code NUMBER;
2287 l_err_stage VARCHAR2(250);
2288 l_err_stack VARCHAR2(250);
2289 l_dummy_char VARCHAR2(1);
2290
2291
2292 CURSOR cur_chk_options
2293 IS
2294 SELECT 'x'
2295 FROM pa_project_options
2296 WHERE project_id = p_project_id
2297 AND option_code = p_option_code;
2298 BEGIN
2299 pa_debug.init_err_stack ('PA_PROJ_TEMPLATE_SETUP_PVT.Add_Project_Options');
2300
2301 IF (p_debug_mode = 'Y') THEN
2302 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Add_Project_Options begin');
2303 END IF;
2304
2305 IF (p_commit = FND_API.G_TRUE) THEN
2306 savepoint Add_Project_Options;
2307 END IF;
2308
2309 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2310 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2311 END IF;
2312
2313 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2314 FND_MSG_PUB.initialize;
2315 END IF;
2316
2317 OPEN cur_chk_options;
2318 FETCH cur_chk_options INTO l_dummy_char;
2319 IF cur_chk_options%NOTFOUND
2320 THEN
2321 INSERT INTO pa_project_options
2322 (
2323 project_id,
2324 option_code,
2325 last_update_date,
2326 last_updated_by,
2327 creation_date,
2328 created_by,
2329 last_update_login,
2330 record_version_number
2331 )
2332 VALUES ( p_project_id,
2333 p_option_code,
2334 SYSDATE,
2335 FND_GLOBAL.USER_ID,
2336 SYSDATE,
2337 FND_GLOBAL.USER_ID,
2338 FND_GLOBAL.LOGIN_ID ,
2339 1
2340 );
2341 END IF;
2342 CLOSE cur_chk_options;
2343
2344 IF p_action = 'ENABLE'
2345 THEN
2346 enable_disbale_proj_opt( p_project_id, p_option_code, p_action );
2347 END IF;
2348
2349 x_return_status := FND_API.G_RET_STS_SUCCESS;
2350
2351 IF (p_commit = FND_API.G_TRUE) THEN
2352 COMMIT;
2353 END IF;
2354
2355 IF (p_debug_mode = 'Y') THEN
2356 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Add_Project_Options END');
2357 END IF;
2358 EXCEPTION
2359 when FND_API.G_EXC_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_ERROR;
2364 when FND_API.G_EXC_UNEXPECTED_ERROR then
2365 if p_commit = FND_API.G_TRUE then
2366 rollback to Add_Project_Options;
2367 end if;
2368 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2369 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
2370 p_procedure_name => 'Add_Project_Options',
2371 p_error_text => SUBSTRB(SQLERRM,1,240));
2372 when OTHERS then
2373 if p_commit = FND_API.G_TRUE then
2374 rollback to Add_Project_Options;
2375 end if;
2376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2377 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
2378 p_procedure_name => 'Add_Project_Options',
2379 p_error_text => SUBSTRB(SQLERRM,1,240));
2380 raise;
2381 END Add_Project_Options;
2382
2383
2384 -- API name : Delete_Project_Options
2385 -- Type : Public API
2386 -- Pre-reqs : None
2387 -- Return Value :
2388 --
2389 -- Parameters
2390 -- p_project_id IN NUMBER,
2391 -- p_option_copde IN VARCHAR2,
2392 --
2393
2394 PROCEDURE Delete_Project_Options(
2395 p_api_version IN NUMBER :=1.0,
2396 p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE,
2397 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
2398 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE,
2399 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
2400 p_calling_module IN VARCHAR2 :='SELF_SERVICE',
2401 p_debug_mode IN VARCHAR2 :='N',
2402 p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2403 p_project_id IN NUMBER,
2404 p_option_code IN VARCHAR2,
2405 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2406 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2407 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2408 ) IS
2409 l_api_name CONSTANT VARCHAR(30) := 'Delete_Project_Options';
2410 l_api_version CONSTANT NUMBER := 1.0;
2411
2412 l_return_status VARCHAR2(1);
2413 l_msg_count NUMBER;
2414 l_msg_data VARCHAR2(250);
2415 l_data VARCHAR2(250);
2416 l_msg_index_out NUMBER;
2417 l_error_msg_code VARCHAR2(250);
2418
2419 l_err_code NUMBER;
2420 l_err_stage VARCHAR2(250);
2421 l_err_stack VARCHAR2(250);
2422
2423 l_option_name VARCHAR2(80);
2424
2425 CURSOR cur_option_name
2426 IS
2427 SELECT meaning
2428 FROM fnd_lookup_values
2429 WHERE lookup_type = 'PA_OPTIONS_SS'
2430 AND lookup_code = p_option_code
2431 AND language = userenv('LANG'); -- Bug 5643345: Added the environment language condition.
2432 -- anlee
2433 -- Added for PA_OPTIONS enhancements
2434 CURSOR get_allow_ovr_enabled
2435 IS
2436 SELECT allow_override_enabled_flag
2437 FROM PA_OPTIONS
2438 WHERE option_code = p_option_code;
2439
2440 l_allow_ovr_enabled VARCHAR2(1);
2441 -- End of changes
2442 BEGIN
2443 pa_debug.init_err_stack ('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Project_Options');
2444
2445 IF (p_debug_mode = 'Y') THEN
2446 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Project_Options begin');
2447 END IF;
2448
2449 IF (p_commit = FND_API.G_TRUE) THEN
2450 savepoint Delete_Project_Options;
2451 END IF;
2452
2453 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2454 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2455 END IF;
2456
2457 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2458 FND_MSG_PUB.initialize;
2459 END IF;
2460
2461 --bug
2462
2463 -- anlee
2464 -- Changes for PA_OPTIONS enhancements
2465
2466 OPEN get_allow_ovr_enabled;
2467 FETCH get_allow_ovr_enabled INTO l_allow_ovr_enabled;
2468 CLOSE get_allow_ovr_enabled;
2469
2470 -- IF PA_PROJ_TEMPLATE_SETUP_UTILS.Header_Option( p_option_code ) = 'Y' OR
2471 -- p_option_code = 'BASIC_INFO_SS' --only mandatory option
2472
2473 IF l_allow_ovr_enabled <> 'Y'
2474 -- End of changes
2475 THEN
2476 -- Changes for Bug 5643345
2477
2478 OPEN cur_option_name;
2479 FETCH cur_option_name INTO l_option_name;
2480 CLOSE cur_option_name;
2481
2482
2483 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2484 p_msg_name => 'PA_PROJ_CANT_DISBL_OPTN',
2485 p_token1 => 'OPTION_NAME',
2486 p_value1 => l_option_name
2487 );
2488
2489 /*PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2490 p_msg_name => 'PA_ALL_NO_UPDATE_RECORD');*/
2491 x_msg_data := 'PA_PROJ_CANT_DISBL_OPTN';
2492 -- End of changes for Bug 5643345
2493 x_return_status := 'E';
2494 RAISE FND_API.G_EXC_ERROR;
2495 END IF;
2496
2497 DELETE FROM pa_project_options WHERE project_id = p_project_id AND option_code = p_option_code;
2498
2499 enable_disbale_proj_opt( p_project_id, p_option_code, 'DISABLE' );
2500
2501 x_return_status := FND_API.G_RET_STS_SUCCESS;
2502
2503 IF (p_commit = FND_API.G_TRUE) THEN
2504 COMMIT;
2505 END IF;
2506
2507 IF (p_debug_mode = 'Y') THEN
2508 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Project_Options END');
2509 END IF;
2510 EXCEPTION
2511 when FND_API.G_EXC_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_ERROR;
2516 when FND_API.G_EXC_UNEXPECTED_ERROR then
2517 if p_commit = FND_API.G_TRUE then
2518 rollback to Delete_Project_Options;
2519 end if;
2520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2521 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
2522 p_procedure_name => 'Delete_Project_Options',
2523 p_error_text => SUBSTRB(SQLERRM,1,240));
2524 when OTHERS then
2525 if p_commit = FND_API.G_TRUE then
2526 rollback to Delete_Project_Options;
2527 end if;
2528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2529 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
2530 p_procedure_name => 'Delete_Project_Options',
2531 p_error_text => SUBSTRB(SQLERRM,1,240));
2532 raise;
2533 END Delete_Project_Options;
2534
2535
2536 -- API name : Add_Quick_Entry_Field
2537 -- Type : Public API
2538 -- Pre-reqs : None
2539 -- Return Value :
2540 --
2541 -- Parameters
2542 -- p_project_id IN NUMBER ,
2543 -- p_sort_order IN NUMBER ,
2544 -- p_field_name IN VARCHAR2 := 'ABCD',
2545 -- p_limiting_value IN VARCHAR2 := 'ABCD',
2546 -- p_prompt IN VARCHAR2 ,
2547 -- p_required_flag IN VARCHAR2 := 'N',--
2548
2549 PROCEDURE Add_Quick_Entry_Field(
2550 p_api_version IN NUMBER :=1.0,
2551 p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE,
2552 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
2553 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE,
2554 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
2555 p_calling_module IN VARCHAR2 :='SELF_SERVICE',
2556 p_debug_mode IN VARCHAR2 :='N',
2557 p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2558 p_project_id IN NUMBER ,
2559 p_sort_order IN NUMBER ,
2560 p_field_name IN VARCHAR2 := 'JUNK_CHARS',
2561 p_limiting_value IN VARCHAR2 := 'JUNK_CHARS',
2562 p_prompt IN VARCHAR2 ,
2563 p_required_flag IN VARCHAR2 := 'N',
2564 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2565 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2566 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2567 ) IS
2568 l_api_name CONSTANT VARCHAR(30) := 'Add_Quick_Entry_Field';
2569 l_api_version CONSTANT NUMBER := 1.0;
2570
2571 l_return_status VARCHAR2(1);
2572 l_msg_count NUMBER;
2573 l_msg_data VARCHAR2(250);
2574 l_data VARCHAR2(250);
2575 l_msg_index_out NUMBER;
2576 l_error_msg_code VARCHAR2(250);
2577
2578 l_err_code NUMBER;
2579 l_err_stage VARCHAR2(250);
2580 l_err_stack VARCHAR2(250);
2581 l_dummy_char VARCHAR2(1);
2582 l_field_name_meaning VARCHAR2(250);
2583
2584 CURSOR cur_overrides
2585 IS
2586 SELECT 'x'
2587 FROM pa_project_copy_overrides
2588 WHERE project_id = p_project_id
2589 AND field_name = p_field_name
2590 AND sort_order = p_sort_order;
2591
2592 CURSOR cur_chk_sort_order
2593 IS
2594 SELECT 'x'
2595 FROM pa_project_copy_overrides
2596 WHERE project_id = p_project_id
2597 AND sort_order = p_sort_order;
2598
2599 CURSOR cur_dup_quick_entry
2600 IS
2601 SELECT 'X'
2602 FROM pa_project_copy_overrides
2603 WHERE project_id = p_project_id
2604 AND field_name = p_field_name;
2605
2606
2607 BEGIN
2608 pa_debug.init_err_stack ('PA_PROJ_TEMPLATE_SETUP_PVT.Add_Quick_Entry_Field');
2609
2610 IF (p_debug_mode = 'Y') THEN
2611 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Add_Quick_Entry_Field begin');
2612 END IF;
2613
2614 IF (p_commit = FND_API.G_TRUE) THEN
2615 savepoint Add_Quick_Entry_Field;
2616 END IF;
2617
2618 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2619 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2620 END IF;
2621
2622 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2623 FND_MSG_PUB.initialize;
2624 END IF;
2625
2626 --check sort order unique
2627 OPEN cur_chk_sort_order;
2628 FETCH cur_chk_sort_order INTO l_dummy_char;
2629 IF cur_chk_sort_order%FOUND
2630 THEN
2631 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2632 p_msg_name => 'PA_SETUP_SORT_ORDER_NOT_UNIQ' );
2633 x_msg_data := 'PA_SETUP_SORT_ORDER_NOT_UNIQ';
2634 x_return_status := 'E';
2635 CLOSE cur_chk_sort_order;
2636 RAISE FND_API.G_EXC_ERROR;
2637 ELSE
2638 CLOSE cur_chk_sort_order;
2639 END IF;
2640
2641 --Duplicate quick entry check.
2642 --Removed the CUSTOMER_NAME from the below if for bug 3619423
2643 IF p_field_name NOT IN ( 'KEY_MEMBER', 'CLASSIFICATION', 'ORG_ROLE' )
2644 THEN
2645 --check duplicate quick entry.
2646 OPEN cur_dup_quick_entry;
2647 FETCH cur_dup_quick_entry INTO l_dummy_char;
2648 IF cur_dup_quick_entry%FOUND
2649 THEN
2650 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2651 p_msg_name => 'PA_SETUP_DUP_QUICK_ENTRY' );
2652 x_msg_data := 'PA_SETUP_DUP_QUICK_ENTRY';
2653 x_return_status := 'E';
2654 CLOSE cur_dup_quick_entry;
2655 RAISE FND_API.G_EXC_ERROR;
2656 ELSE
2657 CLOSE cur_dup_quick_entry;
2658 END IF;
2659 /* Bug 4139681 - Replaced the ELSE with the new IF condition so that checks for specifications are done for
2660 field CUSTOMER_NAME as well.
2661 */
2662 --ELSE
2663 END IF; --Bug 4139681
2664 IF p_field_name IN ( 'KEY_MEMBER', 'CLASSIFICATION', 'ORG_ROLE', 'CUSTOMER_NAME' ) THEN--Bug 4139681
2665
2666 IF p_limiting_value IS NULL OR p_limiting_value = 'JUNK_CHARS'
2667 THEN
2668 BEGIN
2669 SELECT meaning
2670 INTO l_field_name_meaning
2671 FROM fnd_lookup_values
2672 WHERE lookup_type = 'OVERRIDE FIELD'
2673 AND lookup_code = p_field_name;
2674 EXCEPTION
2675 WHEN NO_DATA_FOUND THEN
2676 x_msg_data := 'PA_SETUP_INV_FIELD_NAME';
2677 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2678 p_msg_name => 'PA_SETUP_INV_FIELD_NAME');
2679 x_return_status := 'E';
2680 RAISE FND_API.G_EXC_ERROR;
2681 WHEN OTHERS THEN
2682 null;
2683 END;
2684
2685 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2686 p_msg_name => 'PA_SETUP_SPEC_REQ',
2687 p_token1 => 'FIELD_NAME',
2688 p_value1 => l_field_name_meaning );
2689 x_msg_data := 'PA_SETUP_SPEC_REQ';
2690 x_return_status := 'E';
2691 RAISE FND_API.G_EXC_ERROR;
2692 ELSE
2693 IF p_field_name <> 'CLASSIFICATION'
2694 THEN
2695 BEGIN
2696 SELECT 'x'
2697 INTO l_dummy_char
2698 FROM PA_QUICK_ENTRY_SPECS_SS_V
2699 WHERE FIELD_NAME = p_field_name
2700 AND LIMITING_VALUE = p_limiting_value;
2701 EXCEPTION
2702 WHEN NO_DATA_FOUND THEN
2703 x_msg_data := 'PA_SETUP_INV_SPEC';
2704 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2705 p_msg_name => 'PA_SETUP_INV_SPEC',
2706 p_token1 => 'SPECIFICATION',
2707 p_value1 => p_limiting_value );
2708 x_return_status := 'E';
2709 RAISE FND_API.G_EXC_ERROR;
2710 WHEN OTHERS THEN
2711 null;
2712 END;
2713 ELSE
2714 DECLARE
2715 CURSOR cur_pa_proj_type
2716 IS
2717 SELECT project_type_id
2718 FROM pa_project_types ppt, pa_projects pp
2719 WHERE project_id = p_project_id
2720 AND pp.project_type = ppt.project_type;
2721 l_project_type_id NUMBER;
2722 BEGIN
2723 OPEN cur_pa_proj_type;
2724 FETCH cur_pa_proj_type INTO l_project_type_id;
2725 CLOSE cur_pa_proj_type;
2726 IF l_project_type_id IS NOT NULL
2727 THEN
2728 -- Bug#3693202
2729 -- Commented the existing select which looks into
2730 -- PA_QUICK_ENTRY_SPECS_SS_V view , instead accessed
2731 -- PA_VALID_CATEGORIES_V directly fr performance .
2732
2733 -- SELECT 'x'
2734 -- INTO l_dummy_char
2735 -- FROM PA_QUICK_ENTRY_SPECS_SS_V
2736 -- WHERE FIELD_NAME = p_field_name
2737 -- AND object_type_id = l_project_type_id
2738 -- AND LIMITING_VALUE = p_limiting_value;
2739
2740
2741 SELECT 'X'
2742 INTO l_dummy_char
2743 FROM pa_valid_categories_v pvc ,
2744 pa_lookups pl
2745 WHERE pvc.object_type = 'PA_PROJECTS'
2746 AND pvc.object_type_id = l_project_type_id
2747 AND 'CLASSIFICATION' = pl.lookup_code
2748 AND pl.lookup_type = 'OVERRIDE FIELD'
2749 AND pvc.class_category = p_limiting_value ;
2750 END IF;
2751 EXCEPTION
2752 WHEN NO_DATA_FOUND THEN
2753 x_msg_data := 'PA_SETUP_INV_SPEC';
2754 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2755 p_msg_name => 'PA_SETUP_INV_SPEC',
2756 p_token1 => 'SPECIFICATION',
2757 p_value1 => p_limiting_value );
2758 x_return_status := 'E';
2759 RAISE FND_API.G_EXC_ERROR;
2760 WHEN OTHERS THEN
2761 null;
2762 END;
2763 END IF; --<< field_name <> 'CLASSIFICATION' >>
2764 END IF;
2765 END IF;
2766
2767 OPEN cur_overrides;
2768 FETCH cur_overrides INTO l_dummy_char;
2769 IF cur_overrides%NOTFOUND
2770 THEN
2771 INSERT INTO pa_project_copy_overrides
2772 ( PROJECT_ID ,
2773 FIELD_NAME ,
2774 DISPLAY_NAME ,
2775 LAST_UPDATE_DATE ,
2776 LAST_UPDATED_BY ,
2777 CREATION_DATE ,
2778 CREATED_BY ,
2779 LAST_UPDATE_LOGIN ,
2780 LIMITING_VALUE ,
2781 SORT_ORDER ,
2782 MANDATORY_FLAG ,
2783 RECORD_VERSION_NUMBER
2784 )
2785 VALUES ( p_project_id ,
2786 p_field_name ,
2787 p_prompt ,
2788 SYSDATE ,
2789 FND_GLOBAL.USER_ID ,
2790 SYSDATE ,
2791 FND_GLOBAL.USER_ID ,
2792 FND_GLOBAL.LOGIN_ID ,
2793 p_limiting_value ,
2794 p_sort_order ,
2795 p_required_flag ,
2796 1
2797 );
2798 END IF;
2799 CLOSE cur_overrides;
2800
2801 x_return_status := FND_API.G_RET_STS_SUCCESS;
2802
2803 IF (p_commit = FND_API.G_TRUE) THEN
2804 COMMIT;
2805 END IF;
2806
2807 IF (p_debug_mode = 'Y') THEN
2808 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Add_Quick_Entry_Field END');
2809 END IF;
2810 EXCEPTION
2811 when FND_API.G_EXC_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_ERROR;
2816 when FND_API.G_EXC_UNEXPECTED_ERROR then
2817 if p_commit = FND_API.G_TRUE then
2818 rollback to Add_Quick_Entry_Field;
2819 end if;
2820 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2821 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
2822 p_procedure_name => 'Add_Quick_Entry_Field',
2823 p_error_text => SUBSTRB(SQLERRM,1,240));
2824 when OTHERS then
2825 if p_commit = FND_API.G_TRUE then
2826 rollback to Add_Quick_Entry_Field;
2827 end if;
2828 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2829 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
2830 p_procedure_name => 'Add_Quick_Entry_Field',
2831 p_error_text => SUBSTRB(SQLERRM,1,240));
2832 raise;
2833 END Add_Quick_Entry_Field;
2834
2835 -- API name : Update_Quick_Entry_Field
2836 -- Type : Public API
2837 -- Pre-reqs : None
2838 -- Return Value :
2839 --
2840 -- Parameters
2841 -- p_project_id IN NUMBER ,
2842 -- p_sort_order IN NUMBER ,
2843 -- p_field_name IN VARCHAR2 := 'ABCD',
2844 -- p_limiting_value IN VARCHAR2 := 'ABCD',
2845 -- p_prompt IN VARCHAR2 ,
2846 -- p_required_flag IN VARCHAR2 := 'N',--
2847
2848 PROCEDURE Update_Quick_Entry_Field(
2849 p_api_version IN NUMBER :=1.0,
2850 p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE,
2851 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
2852 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE,
2853 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
2854 p_calling_module IN VARCHAR2 :='SELF_SERVICE',
2855 p_debug_mode IN VARCHAR2 :='N',
2856 p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
2857 p_project_id IN NUMBER ,
2858 p_row_id IN VARCHAR2,
2859 p_sort_order IN NUMBER ,
2860 p_field_name IN VARCHAR2 := 'JUNK_CHARS',
2861 p_limiting_value IN VARCHAR2 := 'JUNK_CHARS',
2862 p_prompt IN VARCHAR2 ,
2863 p_required_flag IN VARCHAR2 := 'N',
2864 p_record_version_number IN NUMBER,
2865 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2866 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2867 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2868 ) IS
2869 l_api_name CONSTANT VARCHAR(30) := 'Update_Quick_Entry_Field';
2870 l_api_version CONSTANT NUMBER := 1.0;
2871
2872 l_return_status VARCHAR2(1);
2873 l_msg_count NUMBER;
2874 l_msg_data VARCHAR2(250);
2875 l_data VARCHAR2(250);
2876 l_msg_index_out NUMBER;
2877 l_error_msg_code VARCHAR2(250);
2878
2879 l_err_code NUMBER;
2880 l_err_stage VARCHAR2(250);
2881 l_err_stack VARCHAR2(250);
2882 l_dummy_char VARCHAR2(1);
2883 l_field_name_meaning VARCHAR2(250);
2884
2885 CURSOR cur_chk_sort_order
2886 IS
2887 SELECT 'x'
2888 FROM pa_project_copy_overrides
2889 WHERE rowid <> p_row_id
2890 AND project_id = p_project_id
2891 AND sort_order = p_sort_order;
2892
2893 CURSOR cur_dup_quick_entry
2894 IS
2895 SELECT 'X'
2896 FROM pa_project_copy_overrides
2897 WHERE rowid <> p_row_id
2898 AND project_id = p_project_id
2899 AND field_name = p_field_name;
2900 BEGIN
2901 pa_debug.init_err_stack ('PA_PROJ_TEMPLATE_SETUP_PVT.Update_Quick_Entry_Field');
2902
2903 IF (p_debug_mode = 'Y') THEN
2904 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Update_Quick_Entry_Field begin');
2905 END IF;
2906
2907 IF (p_commit = FND_API.G_TRUE) THEN
2908 savepoint Update_Quick_Entry_Field;
2909 END IF;
2910
2911 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
2912 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2913 END IF;
2914
2915 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2916 FND_MSG_PUB.initialize;
2917 END IF;
2918
2919 --cant update Project Number or Proejct Name
2920 IF ( p_FIELD_NAME = 'SEGMENT1' OR p_FIELD_NAME = 'NAME' ) AND
2921 p_required_flag = 'N'
2922 THEN
2923 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2924 p_msg_name => 'PA_SETUP_CANT_MODFY_OVER' );
2925 x_msg_data := 'PA_SETUP_CANT_MODFY_OVER';
2926 x_return_status := 'E';
2927 RAISE FND_API.G_EXC_ERROR;
2928 END IF;
2929
2930 --check sort order unique
2931 OPEN cur_chk_sort_order;
2932 FETCH cur_chk_sort_order INTO l_dummy_char;
2933 IF cur_chk_sort_order%FOUND
2934 THEN
2935 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2936 p_msg_name => 'PA_SETUP_SORT_ORDER_NOT_UNIQ' );
2937 x_msg_data := 'PA_SETUP_SORT_ORDER_NOT_UNIQ';
2938 x_return_status := 'E';
2939 CLOSE cur_chk_sort_order;
2940 RAISE FND_API.G_EXC_ERROR;
2941 ELSE
2942 CLOSE cur_chk_sort_order;
2943 END IF;
2944
2945 IF p_field_name NOT IN ( 'KEY_MEMBER', 'CLASSIFICATION', 'CUSTOMER_NAME' ,'ORG_ROLE')
2946 THEN
2947 --check duplicate quick entry.
2948 OPEN cur_dup_quick_entry;
2949 FETCH cur_dup_quick_entry INTO l_dummy_char;
2950 IF cur_dup_quick_entry%FOUND
2951 THEN
2952 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2953 p_msg_name => 'PA_SETUP_DUP_QUICK_ENTRY' );
2954 x_msg_data := 'PA_SETUP_DUP_QUICK_ENTRY';
2955 x_return_status := 'E';
2956 CLOSE cur_dup_quick_entry;
2957 RAISE FND_API.G_EXC_ERROR;
2958 ELSE
2959 CLOSE cur_dup_quick_entry;
2960 END IF;
2961 ELSE
2962 IF p_limiting_value IS NULL OR p_limiting_value = 'JUNK_CHARS'
2963 THEN
2964 BEGIN
2965 SELECT meaning
2966 INTO l_field_name_meaning
2967 FROM fnd_lookup_values
2968 WHERE lookup_type = 'OVERRIDE FIELD'
2969 AND lookup_code = p_field_name;
2970 EXCEPTION
2971 WHEN NO_DATA_FOUND THEN
2972 x_msg_data := 'PA_SETUP_INV_FIELD_NAME';
2973 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2974 p_msg_name => 'PA_SETUP_INV_FIELD_NAME');
2975 x_return_status := 'E';
2976 RAISE FND_API.G_EXC_ERROR;
2977 WHEN OTHERS THEN
2978 null;
2979 END;
2980
2981 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2982 p_msg_name => 'PA_SETUP_SPEC_REQ',
2983 p_token1 => 'FIELD_NAME',
2984 p_value1 => l_field_name_meaning );
2985 x_msg_data := 'PA_SETUP_SPEC_REQ';
2986 x_return_status := 'E';
2987 RAISE FND_API.G_EXC_ERROR;
2988 ELSE
2989 IF p_field_name <> 'CLASSIFICATION'
2990 THEN
2991 BEGIN
2992 SELECT 'x'
2993 INTO l_dummy_char
2994 FROM PA_QUICK_ENTRY_SPECS_SS_V
2995 WHERE FIELD_NAME = p_field_name
2996 AND LIMITING_VALUE = p_limiting_value;
2997 EXCEPTION
2998 WHEN NO_DATA_FOUND THEN
2999 x_msg_data := 'PA_SETUP_INV_SPEC';
3000 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3001 p_msg_name => 'PA_SETUP_INV_SPEC',
3002 p_token1 => 'SPECIFICATION',
3003 p_value1 => p_limiting_value );
3004 x_return_status := 'E';
3005 RAISE FND_API.G_EXC_ERROR;
3006 WHEN OTHERS THEN
3007 null;
3008 END;
3009 ELSE
3010 DECLARE
3011 CURSOR cur_pa_proj_type
3012 IS
3013 SELECT project_type_id
3014 FROM pa_project_types ppt, pa_projects pp
3015 WHERE project_id = p_project_id
3016 AND pp.project_type = ppt.project_type;
3017 l_project_type_id NUMBER;
3018 BEGIN
3019 OPEN cur_pa_proj_type;
3020 FETCH cur_pa_proj_type INTO l_project_type_id;
3021 CLOSE cur_pa_proj_type;
3022 IF l_project_type_id IS NOT NULL
3023 THEN
3024
3025 -- Bug#3693202
3026 -- Commented the existing select which looks into
3027 -- PA_QUICK_ENTRY_SPECS_SS_V view , instead accessed
3028 -- PA_VALID_CATEGORIES_V directly fr performance .
3029
3030 -- SELECT 'x'
3031 -- INTO l_dummy_char
3032 -- FROM PA_QUICK_ENTRY_SPECS_SS_V
3033 -- WHERE FIELD_NAME = p_field_name
3034 -- AND object_type_id = l_project_type_id
3035 -- AND LIMITING_VALUE = p_limiting_value;
3036
3037 SELECT 'X'
3038 INTO l_dummy_char
3039 FROM pa_valid_categories_v pvc ,
3040 pa_lookups pl
3041 WHERE pvc.object_type = 'PA_PROJECTS'
3042 AND pvc.object_type_id = l_project_type_id
3043 AND 'CLASSIFICATION' = pl.lookup_code
3044 AND pl.lookup_type = 'OVERRIDE FIELD'
3045 AND pvc.class_category = p_limiting_value ;
3046 END IF;
3047 EXCEPTION
3048 WHEN NO_DATA_FOUND THEN
3049 x_msg_data := 'PA_SETUP_INV_SPEC';
3050 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3051 p_msg_name => 'PA_SETUP_INV_SPEC',
3052 p_token1 => 'SPECIFICATION',
3053 p_value1 => p_limiting_value );
3054 x_return_status := 'E';
3055 RAISE FND_API.G_EXC_ERROR;
3056 WHEN OTHERS THEN
3057 null;
3058 END;
3059 END IF;
3060 END IF;
3061 END IF;
3062
3063 UPDATE pa_project_copy_overrides
3064 SET FIELD_NAME = p_field_name ,
3065 DISPLAY_NAME = p_prompt ,
3066 LAST_UPDATE_DATE = SYSDATE ,
3067 LAST_UPDATED_BY = FND_GLOBAL.USER_ID ,
3068 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID ,
3069 LIMITING_VALUE = p_limiting_value ,
3070 SORT_ORDER = p_sort_order ,
3071 MANDATORY_FLAG = p_required_flag ,
3072 RECORD_VERSION_NUMBER = NVL( RECORD_VERSION_NUMBER, 1 ) + 1
3073 WHERE rowid = p_row_id;
3074
3075 x_return_status := FND_API.G_RET_STS_SUCCESS;
3076
3077 IF (p_commit = FND_API.G_TRUE) THEN
3078 COMMIT;
3079 END IF;
3080
3081 IF (p_debug_mode = 'Y') THEN
3082 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Update_Quick_Entry_Field END');
3083 END IF;
3084 EXCEPTION
3085 when FND_API.G_EXC_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_ERROR;
3090 when FND_API.G_EXC_UNEXPECTED_ERROR then
3091 if p_commit = FND_API.G_TRUE then
3092 rollback to Update_Quick_Entry_Field;
3093 end if;
3094 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3095 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
3096 p_procedure_name => 'Update_Quick_Entry_Field',
3097 p_error_text => SUBSTRB(SQLERRM,1,240));
3098 when OTHERS then
3099 if p_commit = FND_API.G_TRUE then
3100 rollback to Update_Quick_Entry_Field;
3101 end if;
3102 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3103 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
3104 p_procedure_name => 'Update_Quick_Entry_Field',
3105 p_error_text => SUBSTRB(SQLERRM,1,240));
3106 raise;
3107 END Update_Quick_Entry_Field;
3108
3109 -- API name : DELETE_Quick_Entry_Field
3110 -- Type : Public API
3111 -- Pre-reqs : None
3112 -- Return Value :
3113 --
3114 -- Parameters
3115 -- p_project_id IN NUMBER ,
3116 -- p_sort_order IN NUMBER ,
3117 -- p_field_name IN VARCHAR2 := 'ABCD',
3118 -- p_limiting_value IN VARCHAR2 := 'ABCD',
3119 -- p_prompt IN VARCHAR2 ,
3120 -- p_required_flag IN VARCHAR2 := 'N',--
3121
3122 PROCEDURE Delete_Quick_Entry_Field(
3123 p_api_version IN NUMBER :=1.0,
3124 p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE,
3125 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
3126 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE,
3127 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
3128 p_calling_module IN VARCHAR2 :='SELF_SERVICE',
3129 p_debug_mode IN VARCHAR2 :='N',
3130 p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3131 p_project_id IN NUMBER ,
3132 p_row_id IN VARCHAR2,
3133 p_record_version_number IN NUMBER,
3134 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3135 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3136 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3137 ) IS
3138 l_api_name CONSTANT VARCHAR(30) := 'Delete_Quick_Entry_Field';
3139 l_api_version CONSTANT NUMBER := 1.0;
3140
3141 l_return_status VARCHAR2(1);
3142 l_msg_count NUMBER;
3143 l_msg_data VARCHAR2(250);
3144 l_data VARCHAR2(250);
3145 l_msg_index_out NUMBER;
3146 l_error_msg_code VARCHAR2(250);
3147
3148 l_err_code NUMBER;
3149 l_err_stage VARCHAR2(250);
3150 l_err_stack VARCHAR2(250);
3151 l_dummy_char VARCHAR2(1);
3152
3153 CURSOR cur_chk_dflt_qe
3154 IS
3155 SELECT field_name
3156 FROM pa_project_copy_overrides
3157 WHERE rowid = p_row_id;
3158
3159 l_field_name VARCHAR2(80);
3160
3161 BEGIN
3162 pa_debug.init_err_stack ('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Quick_Entry_Field');
3163
3164 IF (p_debug_mode = 'Y') THEN
3165 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Quick_Entry_Field begin');
3166 END IF;
3167
3168 IF (p_commit = FND_API.G_TRUE) THEN
3169 savepoint Delete_Quick_Entry_Field;
3170 END IF;
3171
3172 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
3173 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3174 END IF;
3175
3176 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
3177 FND_MSG_PUB.initialize;
3178 END IF;
3179
3180 OPEN cur_chk_dflt_qe;
3181 FETCH cur_chk_dflt_qe INTO l_field_name;
3182 CLOSE cur_chk_dflt_qe;
3183
3184 --cant update Project Number or Proejct Name
3185 IF l_FIELD_NAME = 'SEGMENT1' OR l_FIELD_NAME = 'NAME'
3186 THEN
3187 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3188 p_msg_name => 'PA_SETUP_CANT_MODFY_OVER' );
3189 x_msg_data := 'PA_SETUP_CANT_MODFY_OVER';
3190 x_return_status := 'E';
3191 RAISE FND_API.G_EXC_ERROR;
3192 END IF;
3193
3194 DELETE FROM pa_project_copy_overrides
3195 WHERE rowid = p_row_id;
3196
3197 x_return_status := FND_API.G_RET_STS_SUCCESS;
3198
3199 IF (p_commit = FND_API.G_TRUE) THEN
3200 COMMIT;
3201 END IF;
3202
3203 IF (p_debug_mode = 'Y') THEN
3204 pa_debug.debug('PA_PROJ_TEMPLATE_SETUP_PVT.Delete_Quick_Entry_Field END');
3205 END IF;
3206 EXCEPTION
3207 when FND_API.G_EXC_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_ERROR;
3212 when FND_API.G_EXC_UNEXPECTED_ERROR then
3213 if p_commit = FND_API.G_TRUE then
3214 rollback to Delete_Quick_Entry_Field;
3215 end if;
3216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3217 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
3218 p_procedure_name => 'Delete_Quick_Entry_Field',
3219 p_error_text => SUBSTRB(SQLERRM,1,240));
3220 when OTHERS then
3221 if p_commit = FND_API.G_TRUE then
3222 rollback to Delete_Quick_Entry_Field;
3223 end if;
3224 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3225 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJ_TEMPLATE_SETUP_PVT',
3226 p_procedure_name => 'Delete_Quick_Entry_Field',
3227 p_error_text => SUBSTRB(SQLERRM,1,240));
3228 raise;
3229 END Delete_Quick_Entry_Field;
3230
3231 PROCEDURE enable_disbale_proj_opt(
3232 p_project_id NUMBER,
3233 p_option_code VARCHAR2,
3234 p_action VARCHAR2
3235 ) IS
3236
3237 CURSOR cur_pa_lkups
3238 IS
3239 SELECT lk1.lookup_code forms_opt_code, lk2.lookup_code ss_opt_code, lk1.meaning
3240 FROM fnd_lookup_values lk1, fnd_lookup_values lk2
3241 WHERE lk1.lookup_type = 'PA_OPTIONS'
3242 AND lk2.lookup_type = 'PA_OPTIONS_SS'
3243 AND lk1.meaning = lk2.meaning
3244 ;
3245
3246 CURSOR cur_chk_options( c_option_code VARCHAR2 )
3247 IS
3248 SELECT 'x'
3249 FROM pa_project_options
3250 WHERE project_id = p_project_id
3251 AND option_code = c_option_code;
3252
3253 l_option_code VARCHAR2(30);
3254 l_found_flag VARCHAR2(1) := 'N';
3255 l_dummy_char VARCHAR2(1);
3256
3257 BEGIN
3258 FOR cur_pa_lkups_rec in cur_pa_lkups LOOP
3259 IF cur_pa_lkups_rec.forms_opt_code = p_option_code
3260 THEN
3261 l_option_code := cur_pa_lkups_rec.ss_opt_code;
3262 l_found_flag := 'Y';
3263 ELSIF cur_pa_lkups_rec.ss_opt_code = p_option_code
3264 THEN
3265 l_option_code := cur_pa_lkups_rec.forms_opt_code;
3266 l_found_flag := 'Y';
3267 ELSE
3268 l_found_flag := 'N';
3269 END IF;
3270
3271 IF l_found_flag = 'Y' AND l_option_code IS NOT NULL AND p_action = 'ENABLE'
3272 THEN
3273 OPEN cur_chk_options( l_option_code );
3274 FETCH cur_chk_options INTO l_dummy_char;
3275 IF cur_chk_options%NOTFOUND
3276 THEN
3277 INSERT INTO pa_project_options
3278 (
3279 project_id,
3280 option_code,
3281 last_update_date,
3282 last_updated_by,
3283 creation_date,
3284 created_by,
3285 last_update_login,
3286 record_version_number
3287 )
3288 VALUES ( p_project_id,
3289 l_option_code,
3290 SYSDATE,
3291 FND_GLOBAL.USER_ID,
3292 SYSDATE,
3293 FND_GLOBAL.USER_ID,
3294 FND_GLOBAL.LOGIN_ID ,
3295 1
3296 );
3297 END IF;
3298 CLOSE cur_chk_options;
3299 ELSIF l_found_flag = 'Y' AND l_option_code IS NOT NULL AND p_action = 'DISABLE'
3300 THEN
3301 DELETE FROM pa_project_options WHERE project_id = p_project_id and option_code = l_option_code ;
3302 END IF; --<< l_insert_flag >>
3303 END LOOP;
3304 END enable_disbale_proj_opt;
3305
3306 END PA_PROJ_TEMPLATE_SETUP_PVT;