[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_CORE1
Source
1 package body PA_PROJECT_CORE1 as
2 -- $Header: PAXPCO1B.pls 120.13.12010000.5 2008/11/28 06:10:40 vgovvala ship $
3
4 g_module_name VARCHAR2(100) := 'PA_PROJECT_CORE1';
5 Invalid_Arg_Exc EXCEPTION;
6
7 --
8 -- PROCEDURE
9 -- get_project_number_by_numcode
10 -- PURPOSE
11 -- This procedure retrieves project number for a specified
12 -- project id according to the implementation-defined Project
13 -- number generation mode. If mode is 'MANUAL', the
14 -- user-provided project number is used and stored in
15 -- x_resu_proj_num (note this is non-NULL);
16 -- otherwise, a system generated number will be used.
17 --
18 procedure get_project_number_by_numcode ( x_orig_proj_num IN varchar2
19 , x_resu_proj_num IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
20 , x_proj_number_gen_mode OUT NOCOPY varchar2 -- Added for Bug# 7445534
21 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
22 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
23 , x_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
24 is
25 old_stack varchar2(630);
26 proj_number_gen_mode varchar2(30);
27 proj_number_type varchar2(30);
28 l_proj_num_numeric NUMBER;
29 unique_id number;
30 status number;
31
32
33
34 begin
35 x_err_code := 0;
36 old_stack := x_err_stack;
37 x_err_stack := x_err_stack || '->get_project_number';
38
39 x_resu_proj_num := NULL;
40
41 x_err_stage := 'Generating project number...';
42
43 -- Generate Project Number according to the implementation-defined Project
44 -- number generation mode
45 proj_number_gen_mode := PA_PROJECT_UTILS.GetProjNumMode;
46 x_proj_number_gen_mode := proj_number_gen_mode; -- Added for Bug# 7445534
47
48 if proj_number_gen_mode = 'MANUAL' then
49 proj_number_type := PA_PROJECT_UTILS.GetProjNumType;
50 begin
51 if proj_number_type = 'NUMERIC' then
52 l_proj_num_numeric := TO_NUMBER(x_orig_proj_num);
53 end if;
54 exception when value_error then
55 x_err_code := 40;
56 x_err_stage := 'PA_PR_NUMERIC_NUM_REG';
57 return;
58 end;
59 end if;
60
61 if proj_number_gen_mode is NULL then
62 x_err_code := 10;
63 x_err_stage := 'PA_NO_GEN_MODE';
64 return;
65 elsif proj_number_gen_mode = 'AUTOMATIC' then
66 PA_UTILS_SQNUM_PKG.get_unique_proj_num('PA_PROJECTS',
67 FND_GLOBAL.USER_ID,
68 unique_id,
69 status);
70 if status = 0 then
71 x_resu_proj_num := to_char(unique_id);
72 else
73 x_err_code := 20;
74 x_err_stage := 'PA_NO_UNIQUE_ID';
75 return;
76 end if;
77 elsif x_orig_proj_num is NULL then
78 x_err_code := 30;
79 x_err_stage := 'PA_NO_ORIG_PROJNUM';
80 return;
81 else
82 x_resu_proj_num := x_orig_proj_num;
83 end if; -- Proj_Number_Gen_Mode
84
85 x_err_stack := old_stack;
86
87 exception
88 when others then
89 x_err_code := SQLCODE;
90 x_err_stage := 'PA_SQL_ERROR';
91 end get_project_number_by_numcode;
92
93 --
94 -- FUNCTION
95 --
96 -- Get_Message_from_stack
97 -- This function returns message from the stack and if does not
98 -- find one then returns whatever message passed to it.
99 -- HISTORY
100 -- 12-DEC-01 MAansari -Created
101
102 FUNCTION Get_Message_from_stack( p_err_stage IN VARCHAR2 ) RETURN VARCHAR2 IS
103 x_msg_count NUMBER;
104 l_msg_count NUMBER;
105 l_msg_data VARCHAR2(2000);
106 l_data VARCHAR2(2000);
107 l_msg_index_out NUMBER;
108 l_app_name VARCHAR2(2000) := 'PA';
109 l_temp_name VARCHAR2(2000);
110 BEGIN
111 x_msg_count := FND_MSG_PUB.count_msg;
112
113 FND_MSG_PUB.get (
114 p_msg_index => 1,
115 p_encoded => FND_API.G_TRUE,
116 p_data => l_data,
117 p_msg_index_out => l_msg_index_out );
118
119 if l_data is not null then
120 FND_MESSAGE.PARSE_ENCODED(ENCODED_MESSAGE => l_data,
121 APP_SHORT_NAME => l_app_name,
122 MESSAGE_NAME => l_msg_data);
123
124 FND_MSG_PUB.DELETE_MSG(p_msg_index => 1);
125 else
126 l_msg_data := p_err_stage;
127 end if;
128
129 return l_msg_data;
130
131 END Get_Message_from_stack;
132
133 -- Added for Bug# 7445534
134 PROCEDURE revert_proj_number(p_proj_number_gen_mode IN VARCHAR2,
135 p_project_number IN VARCHAR2 ) IS
136 BEGIN
137 IF p_proj_number_gen_mode = 'AUTOMATIC' THEN
138 PA_UTILS_SQNUM_PKG.revert_unique_proj_num(p_table_name => 'PA_PROJECTS',
139 p_user_id => FND_GLOBAL.USER_ID,
140 p_unique_number => TO_NUMBER(p_project_number));
141 END IF;
142 END revert_proj_number;
143
144 --
145 -- PROCEDURE
146 -- copy_project
147 -- PURPOSE
148 --
149 -- The objective of this procedure is to create a new
150 -- project and other project related information such
151 -- as wbs, project players, budget information, billing,
152 -- and costing information by copying from a specific project
153 -- and its related information.
154 --
155 -- Users can choose whether to copy budget, tasks, and task
156 -- related information by passing in 'Y' or 'N' for the
157 -- x_copy_task_flag and x_copy_budget_flag parameters.
158 -- Users can also choose whether to use copy override
159 -- associated with the original project by passing in
160 -- 'Y' or 'N' for x_use_override_flag. If 'Y' is passed
161 -- for x_use_override_flag, then project players with
162 -- project roles that are overrideable, project classes
163 -- with categories that are overrideable, and customers
164 -- with relationship role type that are overrideable will
165 -- not get copied from the original project to the new
166 -- project. The overrideable information can be entered
167 -- via the Enter Project form in this case.
168 --
169 -- If no value is provided for any of these flag, the
170 -- default is 'Y'.
171 --
172 -- User can pass 'Y' or 'N' for x_template_flag to indicate
173 -- whether the resulting record is a template or not. If
174 -- no value is passed, the default is 'N'.
175 --
176 -- HISTORY
177 -- Ri Singh 03/01/99 : added call to pa_budget_utils2.submit_budget to
178 -- change status to submitted before baselining
179 -- tsaifee 01/24/97 -
180 -- The number returned by the get_project_number... proc is then
181 -- checked for being unique.
182 --
183 -- 31-DEC-97 jwhite - For the call to Check_Wf_Enabled,
184 -- if x_err_code > 0, then assign
185 -- zero to x_err_code.
186 -- 17-JUL-2000 Mohnish
187 -- added code for ROLE BASED SECURITY:
188 -- added the call to PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY
189 -- 19-JUL-2000 Mohnish incorporated PA_PROJECT_PARTIES_PUB API changes
190 -- 15-AUG-2000 Sakthi Modified 'PROJECTS' TO 'PA_PROJECTS' in PA_PROJECT_PARTIES_PUB API changes
191 --
192 -- 24-Sep-02 msundare Added additional parameter x_security_level
193 -- 18-DEC-02 gjain For bug 2588244 Added logic in copy_project to copy the
194 -- default calendar from the organization specified in quick entry
195 --
196 -- 11-Feb-03 sacgupta Bug2787577.
197 -- Modified the call to pa_accum_proj_list.Insert_Accum API.
198 -- Now this API will be called for Projects only and not for
199 -- Project Templates.
200 --
201
202 procedure copy_project (
203 x_orig_project_id IN number
204 , x_project_name IN varchar2
205 , x_long_name IN VARCHAR2 default null
206 , x_project_number IN varchar2
207 , x_description IN varchar2
208 , x_project_type IN varchar2
209 , x_project_status_code IN varchar2
210 , x_distribution_rule IN varchar2
211 , x_public_sector_flag IN varchar2
212 , x_organization_id IN number
213 , x_start_date IN date
214 , x_completion_date IN date
215 , x_probability_member_id IN number
216 , x_project_value IN number
217 , x_expected_approval_date IN date
218 --MCA Sakthi for MultiAgreementCurreny Project
219 , x_agreement_currency IN VARCHAR2
220 , x_agreement_amount IN NUMBER
221 , x_agreement_org_id IN NUMBER
222 --MCA Sakthi for MultiAgreementCurreny Project
223 , x_copy_task_flag IN varchar2
224 , x_copy_budget_flag IN varchar2
225 , x_use_override_flag IN varchar2
226 , x_copy_assignment_flag IN varchar2 default 'N'
227 , x_template_flag IN varchar2
228 , x_project_id OUT NOCOPY number --File.Sql.39 bug 4440895
229 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
230 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
231 , x_err_stack IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
232 , x_customer_id IN number default NULL
233 , x_new_project_number IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
234 , x_pm_product_code IN varchar2 default NULL
235 , x_pm_project_reference IN varchar2 default NULL
236 , x_attribute_category IN varchar2 default NULL
237 , x_attribute1 IN varchar2 default NULL
238 , x_attribute2 IN varchar2 default NULL
239 , x_attribute3 IN varchar2 default NULL
240 , x_attribute4 IN varchar2 default NULL
241 , x_attribute5 IN varchar2 default NULL
242 , x_attribute6 IN varchar2 default NULL
243 , x_attribute7 IN varchar2 default NULL
244 , x_attribute8 IN varchar2 default NULL
245 , x_attribute9 IN varchar2 default NULL
246 , x_attribute10 IN varchar2 default NULL
247 , x_actual_start_date IN DATE default NULL
248 , x_actual_finish_date IN DATE default NULL
249 , x_early_start_date IN DATE default NULL
250 , x_early_finish_date IN DATE default NULL
251 , x_late_start_date IN DATE default NULL
252 , x_late_finish_date IN DATE default NULL
253 , x_scheduled_start_date IN DATE default NULL
254 , x_scheduled_finish_date IN DATE default NULL
255 , x_team_template_id IN NUMBER
256 , x_country_code IN VARCHAR2
257 , x_region IN VARCHAR2
258 , x_city IN VARCHAR2
259 -- for opportunity value changes
260 -- anlee
261 , x_opp_value_currency_code IN VARCHAR2
262 --for org forecasting maansari
263 , x_org_project_copy_flag IN VARCHAR2 default 'N'
264 , x_priority_code IN VARCHAR2 default null
265 -- For Project setup changes in FP.K
266 , x_security_level IN NUMBER default 1
267 --Bug 3279981 For FP_M
268 , p_en_top_task_cust_flag IN VARCHAR2 default null
269 , p_en_top_task_inv_mth_flag IN VARCHAR2 default null
270 --Bug 3279981 For FP_M
271 --sunkalya:federal Bug#5511353
272 , p_date_eff_funds_flag IN VARCHAR2 default null
273 --sunkalya:federal Bug#5511353
274 , p_ar_rec_notify_flag IN VARCHAR2 default 'N' -- 7508661 : EnC
275 , p_auto_release_pwp_inv IN VARCHAR2 default 'Y' -- 7508661 : EnC
276 )
277 is
278 status_code number;
279 x_new_project_id number;
280 x_orig_start_date date;
281 x_orig_template_flag varchar2(2);
282 x_delta number default NULL;
283 x_created_from_proj_id Number ;
284 x_temp_project_id Number ;
285 old_stack varchar2(630);
286 x_res_list_assgmt_id Number ;
287 p_project_status_code varchar2(30);
288 p_closed_date date;
289 l_project_type VARCHAR2(30);
290 l_organization_id NUMBER;
291 l_item_type VARCHAR2(30);
292 l_wf_process VARCHAR2(30);
293 l_wf_item_type VARCHAR2(30);
294 l_wf_type VARCHAR2(30);
295 l_wf_party_process VARCHAR2(30);
296 l_wf_enabled_flag VARCHAR2(1);
297 l_assignment_id NUMBER;
298 l_err_code NUMBER := 0;
299 l_team_template_id PA_TEAM_TEMPLATES.TEAM_TEMPLATE_ID%TYPE;
300 l_location_id PA_LOCATIONS.LOCATION_ID%TYPE;
301 l_start_date DATE;
302 l_return_status VARCHAR2(1);
303 l_msg_data VARCHAR2(2000);
304 l_msg_count NUMBER;
305 l_rowid VARCHAR2(250);
306 l_baseline_funding_flag VARCHAR2(1);
307 l_long_name VARCHAR2(255); --long name changes
308 new_prj_end_date DATE; -- Bug 7482391
309
310 l_city VARCHAR2(80);
311 /*l_region varchar2(240);
312 UTF8 changes made for commented code and changed from varchar2 to
313 hr_locations_all.region_1%type*/
314 l_region HR_LOCATIONS_ALL.REGION_1%TYPE;
315 l_country_code VARCHAR2(2);
316 x_error_message_code VARCHAR2(240);
317 l_country_name VARCHAR2(2000);
318
319 --Project Structure Changes
320 l_split_costing VARCHAR2(1);
321 l_split_billing VARCHAR2(1);
322
323 l_org_func_security VARCHAR2(1); /* bug#1968394 */
324
325 l_warnings_only_flag VARCHAR2(1) := 'N'; --bug3134205
326
327 CURSOR l_get_details_for_wf_csr (l_project_id IN NUMBER ) IS
328 SELECT project_type,
329 project_status_code
330 FROM pa_projects
331 WHERE project_id = l_project_id;
332
333 -- for opportunity value changes
334 -- anlee
335 l_expected_approval_date DATE;
336 l_projfunc_currency_code VARCHAR2(15);
337 l_project_currency_code VARCHAR2(15);
338 l_opportunity_value NUMBER;
339 l_opp_value_currency_code VARCHAR2(15);
340 l_cal_id NUMBER default null; /* added for bug 2588244 */
341 l_flag VARCHAR2(1) default null; /* added for bug 2588244 */
342
343 CURSOR l_get_details_for_opp_csr (c_project_id IN NUMBER) IS
344 SELECT expected_approval_date, projfunc_currency_code, project_currency_code
345 ,target_start_date, target_finish_date, calendar_id --bug 2805602
346 FROM pa_projects
347 WHERE project_id = c_project_id;
348
349 l_target_start_date DATE; --added for bug 2805602
350 l_target_finish_date DATE; --added for bug 2805602
351
352
353 CURSOR l_get_details_for_opp_csr2 (c_project_id IN NUMBER) IS
354 SELECT opportunity_value, opp_value_currency_code
355 FROM pa_project_opp_attrs
356 WHERE project_id = c_project_id;
357
358 l_errorcode NUMBER;
359
360 -- Bug 7482391
361 cursor new_prj_end_date_csr(c_project_id IN NUMBER) IS
362 SELECT COMPLETION_DATE
363 FROM PA_PROJECTS_ALL
364 WHERE project_id=c_project_id;
365
366 -- anlee
367 /*
368 -- workplan attr changes
369 -- anlee
370 l_approval_reqd_flag VARCHAR2(1);
371 l_auto_publish_flag VARCHAR2(1);
372 l_approver_source_id NUMBER;
373 l_approver_source_type NUMBER;
374 l_default_outline_lvl NUMBER;
375
376 CURSOR l_get_workplan_attrs_csr(c_project_id IN NUMBER) IS
377 SELECT wp_approval_reqd_flag, wp_auto_publish_flag, wp_approver_source_id, wp_approver_source_type, wp_default_display_lvl
378 FROM pa_proj_workplan_attr
379 WHERE project_id = c_project_id;
380 */
381
382 /* below cursor added for bug 2588244 */
383 -- Change for R12 Org Info Type changes
384 CURSOR l_get_default_calendar(p_org_id IN NUMBER) IS
385 select org_information1 --org_information2
386 from hr_organization_information
387 --where org_information_context = 'Exp Organization Defaults'
388 where org_information_context = 'Resource Defaults'
389 and organization_id = p_org_id;
390
391 --Following code added for selective copy project options. Tracking bug No 3464332
392 CURSOR cur_get_flag( p_flag_name IN VARCHAR2 ) IS
393 SELECT FLAG
394 FROM PA_PROJECT_COPY_OPTIONS_TMP
395 WHERE CONTEXT = p_flag_name ;
396
397 l_pr_team_members_flag VARCHAR2(1);
398 l_pr_attachments_flag VARCHAR2(1);
399 l_pr_frm_src_tmpl_flag VARCHAR2(1);
400 l_pr_item_assoc_flag VARCHAR2(1);
401 l_pr_dff_flag VARCHAR2(1);
402 l_pr_user_defn_att_flag VARCHAR2(1);
403
404 l_fin_txn_control_flag VARCHAR2(1);
405 l_fn_cb_overrides_flag VARCHAR2(1);
406 l_fn_assets_flag VARCHAR2(1);
407 l_fn_asset_assignments_flag VARCHAR2(1);
408 l_is_fin_str_copied VARCHAR2(1) := 'N'; -- Bug 4188514
409 ----End selective copy project options. Tracking bug No 3464332
410 --Bug 3279981 FP_M Project Setup development
411 CURSOR cur_get_orig_bill_info IS
412 SELECT enable_top_task_inv_mth_flag, revenue_accrual_method, invoice_method
413 FROM pa_projects_all
414 WHERE project_id = x_orig_project_id;
415
416 -- 4055319 Added below cursor to retrieve source's funding approval status code
417
418 CURSOR cur_proj_fund_status IS
419 select
420 ppa.funding_approval_status_code,
421 pps.project_system_status_code
422 from
423 pa_projects_all ppa,
424 pa_project_statuses pps
425 where
426 ppa.project_id = x_orig_project_id
427 and ppa.funding_approval_status_code = pps.project_status_code;
428
429 -- added below local variables
430
431 l_fund_status VARCHAR2(30);
432 l_org_fund_status VARCHAR2(30);
433 l_org_fund_sys_status VARCHAR2(30);
434
435 -- 4055319 end
436
437 l_hghst_ctr_cust_id NUMBER := NULL;
438 l_orig_en_top_task_inv_mth VARCHAR2(1);
439 l_orig_en_top_task_cust VARCHAR2(1);
440 l_orig_rev_acc_mth VARCHAR2(30);
441 l_orig_inv_mth VARCHAR2(30);
442 l_new_distribution_rule VARCHAR2(30);
443 --Bug 3279981 FP_M Project Setup development
444 l_dup_name_flag VARCHAR2(1); --Bug 2450064
445
446 l_baseline_exists_in_src VARCHAR2(1); --Bug 5378256/5137355
447 l_orig_date_eff_funds_flag VARCHAR2(1); --sunkalya:federal. Bug#5511353
448 l_check_diff_flag VARCHAR2(1) := NULL; --sunkalya:federal Bug#5511353
449
450 --bug#5859329
451 l_tmp_end_date_active DATE;
452 --bug#5859329
453
454 -- Bug 7482391
455 l_tmp_start_date_active DATE;
456
457 -- Added for Bug# 7445534
458 x_proj_number_gen_mode VARCHAR2(30);
459
460 begin
461 savepoint copy_project;
462 x_err_code := 0;
463 old_stack := x_err_stack;
464
465 x_err_stack := x_err_stack || '->copy_project';
466
467 -- Check original project id
468 if (x_orig_project_id is null ) then
469 x_err_code := 10;
470 x_err_stage := 'PA_NO_ORIG_PROJ_ID';
471 return;
472 end if ;
473
474 -- Check project name
475 if (x_project_name is null ) then
476 x_err_code := 20;
477 x_err_stage := 'PA_PR_NO_PROJ_NAME';
478 return;
479 end if ;
480
481
482 -- If the site implementation of USER_DEFINED_PROJECT_NUM_CODE
483 -- is AUTOMATIC, store system generated number in SEGMENT1;
484
485 --EH Changes
486 BEGIN
487 if (x_template_flag = 'N') then
488 get_project_number_by_numcode (x_project_number,
489 x_new_project_number,
490 x_proj_number_gen_mode, -- Added for Bug# 7445534
491 x_err_code,
492 x_err_stage,
493 x_err_stack);
494 else
495 x_new_project_number := x_project_number;
496 end if;
497
498 if x_err_code <> 0 then
499 x_err_code := 765;
500 IF x_err_stage IS NULL
501 THEN
502 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_PRJ_COR1_GET_PRJ_NUM');
503 END IF;
504 x_err_stack := x_err_stack||'->pa_project_core1.get_project_number_by_numcode';
505 rollback to copy_project;
506 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
507 return;
508 end if;
509 EXCEPTION WHEN OTHERS THEN
510 x_err_code := 765;
511 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
512 -- IF x_err_stage IS NULL
513 -- THEN
514 x_err_stage := 'API: '||'pa_project_core1.get_project_number_by_numcode'||
515 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
516 -- END IF;
517 rollback to copy_project;
518 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
519 return;
520 END;
521
522 -- Check project number
523 -- Either x_project_number or x_new_project_number should have
524 -- a valid value depending on whether the site implementatation
525 -- for USER_DEFINED_PROJECT_NUM_CODE is AUTOMATIC or MANUAL
526 if (x_project_number is null and x_new_project_number is null) then
527 x_err_code := 30;
528 x_err_stage := 'PA_PR_NO_PROJ_NUM';
529 return;
530 end if ;
531
532 -- Check project start and completion date
533 -- If start date is not null, then completion can be null or not null
534 -- If completion date is not null, then start date must be provided.
535
536 if (x_completion_date is not null and x_start_date is null) then
537 x_err_code := 40;
538 x_err_stage := 'PA_PR_START_DATE_NEEDED';
539 return;
540 end if;
541
542 -- Uniqueness check for project name
543 x_err_stage := 'check uniqueness for project name '|| x_project_name;
544 status_code :=
545 pa_project_utils.check_unique_project_name(x_project_name,
546 null);
547 if ( status_code = 0 ) then
548 x_err_code := 50;
549 x_err_stage := 'PA_PR_EPR_PROJ_NAME_NOT_UNIQUE';
550 return;
551 elsif ( status_code < 0 ) then
552 x_err_code := status_code;
553 return;
554 /*Added for bug 2450064*/
555 elsif ( status_code = 1 ) then
556 pjm_seiban_pkg.project_name_dup(x_project_name,l_dup_name_flag);
557 if l_dup_name_flag = 'Y' then
558 x_err_code := 61;
559 x_err_stage := 'PA_SEIBAN_NAME_NOT_UNIQUE';
560 return;
561 end if;
562 /*Added till here for bug 2450064*/
563 end if;
564
565 /* Bug#2638968: Commented this as this check is already taken care in pa_project_pub.create-project API.
566 This check was causing uniqueness problem in project connect as the MISS CHAR is not taken care. */
567 -- Uniqueness check for long_name name Bug#2638968
568 /*Uncommented the below code for bug 3268727 since unique long name is not taken care
569 in Self Service as it is taken care in forms*/
570 if (x_long_name is NOT NULL and x_long_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) then
571 x_err_stage := 'check uniqueness for long name '|| x_long_name;
572 status_code :=
573 pa_project_utils.check_unique_long_name(x_long_name,
574 null);
575 if ( status_code = 0 ) then
576 x_err_code := 50;
577 x_err_stage := 'PA_PR_EPR_LONG_NAME_NOT_UNIQUE';
578 return;
579 elsif ( status_code < 0 ) then
580 x_err_code := status_code;
581 return;
582 end if;
583 end if;
584
585 /*Adding the below code for bug 3268727.When the long name is null, we
586 copy the x_project_name into long_name.Hence, when the x_long_name is null, then we check uniqueness for
587 long_name with the parameter of x_long_name*/
588
589 if (x_long_name is NULL or x_long_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) then
590 x_err_stage := 'check uniqueness for long name '|| x_project_name;
591 status_code :=
592 pa_project_utils.check_unique_long_name(x_project_name,
593 null);
594 if ( status_code = 0 ) then
595 x_err_code := 50;
596 x_err_stage := 'PA_PR_EPR_LONG_NAME_NOT_UNIQUE';
597 return;
598 elsif ( status_code < 0 ) then
599 x_err_code := status_code;
600 return;
601 end if;
602 end if;
603 /*End of code addition for the bug 3268727*/
604
605 -- Uniqueness check for project number
606 -- tsaifee 01/24/97- x_new_project_number used instead of
607 -- x_project_number
608 x_err_stage := 'check uniqueness for project number '||
609 x_new_project_number;
610 status_code :=
611 pa_project_utils.check_unique_project_number(x_new_project_number,
612 null);
613 if ( status_code = 0 ) then
614 x_err_code := 60;
615 x_err_stage := 'PA_PR_EPR_PROJ_NUM_NOT_UNIQUE';
616 return;
617 elsif ( status_code < 0 ) then
618 x_err_code := status_code;
619 return;
620 /*Added for bug 2450064*/
621 elsif ( status_code = 1 ) then
622 pjm_seiban_pkg.project_number_dup(x_new_project_number,l_dup_name_flag);
623 if l_dup_name_flag = 'Y' then
624 x_err_code := 61;
625 x_err_stage := 'PA_SEIBAN_NUM_NOT_UNIQUE';
626 return;
627 end if;
628 /*Added till here for bug 2450064*/
629 end if;
630
631
632 -- verify date range
633 if (x_start_date is not null AND x_completion_date is not null
634 AND x_start_date > x_completion_date ) then
635 -- invaid date range
636 x_err_code := 80;
637 x_err_stage := 'PA_SU_INVALID_DATES';
638 -- existing message name from PAXTKETK
639 return;
640 end if;
641
642 -- get original project start and completion dates
643 -- determine the shift days (delta).
644 -- delta = new project start date - nvl(old project start date,
645 -- earlist task start date)
646
647 -- old project new project
648 -- case start date start date new start date new end date
649 -- ---- ----------- ----------- ----------------- -----------------
650 -- A not null not null old start date old end date
651 -- + delta + delta
652 -- B-1 null not null old start date old end date
653 -- (old task has start date) + delta + delta
654 -- B-2 null not null new proj start new proj completion
655 -- (old task has no start date) date date
656 -- C not null null old start date old end date
657 -- D null null old start date old end date
658
659 declare
660 cursor c1 is
661 select start_date,
662 template_flag,
663 created_from_project_id,
664 project_type,
665 carrying_out_organization_id,
666 initial_team_template_id,
667 baseline_funding_flag
668 from pa_projects
669 where project_id = x_orig_project_id;
670
671 -- use min(start_date) as pseudo original project start
672 cursor c2 is
673 select min(start_date) min_start
674 from pa_tasks
675 where project_id = x_orig_project_id;
676
677 c2_rec c2%rowtype;
678
679
680 begin
681 open c1;
682 fetch c1 into
683 x_orig_start_date,
684 x_orig_template_flag,
685 x_created_from_proj_id,
686 l_project_type,
687 l_organization_id,
688 l_team_template_id,
689 l_baseline_funding_flag;
690 close c1;
691
692 -- If x_team_template is null, get from orig template/project
693 l_team_template_id := nvl(x_team_template_id, l_team_template_id);
694 -- created_from_project_id would be null, only for those
695 -- templates which were created afresh through the form
696 -- If the source project was a template,then created_from_proj_id
697 -- would be the project_id of source project. Hence,in both these
698 -- cases,we should populate created_from_project_id with the
699 -- project_id of the source project.
700
701 if (x_created_from_proj_id is null or
702 x_orig_template_flag = 'Y' )
703 Then
704 x_created_from_proj_id := x_orig_project_id;
705 end if ;
706 /*
707 if (x_created_from_proj_id is null or
708 x_orig_template_flag = 'Y' )
709 Then
710 x_created_from_proj_id := x_orig_project_id;
711 x_temp_project_id := NULL;
712 else
713 x_temp_project_id := x_created_from_proj_id;
714 end if ;
715 */
716
717 if (x_start_date is null) then
718 -- case C or D
719 x_delta := 0;
720 elsif (x_orig_start_date is not null) then
721 -- case A
722 x_delta := x_start_date - x_orig_start_date;
723 else
724 -- case B
725 open c2;
726 fetch c2 into c2_rec;
727 if c2%found then
728 -- case B-1: x_delta is difference between
729 -- new project start date and the
730 -- start date of the earlist task
731 -- of old project
732 -- case B-2: x_delta is NULL
733
734 -- 3874742 reverted back the changes , refer bug update "ADORAIRA 10/26/04 02:47 am"
735 -- 3874742 Added code to check c2_rec.min_start for NULL
736 -- if it is null, set x_delta to 0.
737 -- IF c2_rec.min_start IS NOT NULL THEN
738 x_delta := x_start_date - c2_rec.min_start;
739 -- ELSE
740 -- x_delta := 0;
741 -- END IF;
742 end if;
743 close c2;
744
745 end if;
746
747 -- 3874742 reverted back the changes , refer bug update "ADORAIRA 10/26/04 02:47 am"
748 -- 3874742 Added code to check x_delta for NULL value
749 -- if it is NULL, set x_delta to 0.
750
751 -- IF x_delta IS NULL THEN
752 -- x_delta := 0;
753 -- END IF;
754 -- 3874742 end
755
756 --Organization Forecasting Changes
757 -- IF x_orig_template_flag = 'N' and x_template_flag = 'N'
758 -- THEN
759 IF x_org_project_copy_flag = 'N'
760 THEN
761 DECLARE
762 CURSOR cur_pa_proj_type
763 IS
764 SELECT 'X'
765 FROM pa_project_types
766 WHERE project_type = l_project_type
767 AND org_project_flag = 'Y';
768
769 l_dummy_char VARCHAR2(1);
770 BEGIN
771 OPEN cur_pa_proj_type;
772 FETCH cur_pa_proj_type INTO l_dummy_char;
773 IF cur_pa_proj_type%FOUND
774 THEN
775 CLOSE cur_pa_proj_type;
776 x_err_code := 910;
777 x_err_stage := 'PA_ORG_FC_CANT_CR_PR';
778 return;
779 END IF;
780 CLOSE cur_pa_proj_type;
781 END;
782 END IF;
783 end;
784
785
786 declare
787 cursor p1 is select project_system_status_code
788 from pa_project_statuses ps
789 where project_status_code = x_project_status_code;
790
791 cursor p2 is select ps.project_system_status_code, ps.starting_status_flag, ps.project_status_code
792 from pa_project_statuses ps, pa_projects pp
793 where ps.project_status_code = pp.project_status_code
794 and pp.project_id = x_orig_project_id;
795
796 cursor p3 is select ps.project_system_status_code, ps.starting_status_flag, ps.project_status_code
797 from pa_project_statuses ps, pa_project_types pt, pa_projects pp
798 where ps.project_status_code = pt.def_start_proj_status_code
799 and pt.project_type = pp.project_type
800 and pp.project_id = x_orig_project_id;
801
802 p1_rec p1%rowtype;
803 p2_rec p2%rowtype;
804 p3_rec p3%rowtype;
805
806 begin
807
808 If x_project_status_code is not null then
809 p_project_status_code := x_project_status_code;
810 open p1;
811 fetch p1 into p1_rec;
812 close p1;
813
814 /* If p1_rec.project_system_status_code = 'CLOSED' then */
815 If pa_utils2.IsProjectClosed(p1_rec.project_system_status_code) = 'Y' then
816 p_closed_date := sysdate;
817 Else
818 p_closed_date := null;
819 End If;
820 Else
821 open p2;
822 fetch p2 into p2_rec;
823 close p2;
824 If p2_rec.starting_status_flag = 'Y' then
825 p_project_status_code := p2_rec.project_status_code;
826
827 /* If p2_rec.project_system_status_code = 'CLOSED' then */
828 If pa_utils2.IsProjectClosed(p2_rec.project_system_status_code)= 'Y' then
829 p_closed_date := sysdate;
830 Else
831 p_closed_date := null;
832 End If;
833 Else
834 open p3;
835 fetch p3 into p3_rec;
836 close p3;
837 p_project_status_code := p3_rec.project_status_code;
838
839 /* If p3_rec.project_system_status_code = 'CLOSED' then */
840 If pa_utils2.IsProjectClosed(p3_rec.project_system_status_code) = 'Y' then
841 p_closed_date := sysdate;
842 Else
843 p_closed_date := null;
844 End If;
845 End If;
846
847 End If;
848 end;
849
850 -- Check for org validation starts here.
851
852 if x_project_type is not null then
853 l_project_type := x_project_type;
854 end if;
855
856 if x_organization_id is not null then
857 l_organization_id := x_organization_id;
858 end if;
859
860 x_err_stage := 'Check valid Organization ... ';
861
862 if pa_project_pvt.check_valid_org(l_organization_id) = 'Y' then
863
864 x_err_stage := 'Validate Attribute Change ... ';
865
866 -- Code Added for the bug#1968394
867 -- Test the function security for Org changes
868 --
869 IF (fnd_function.test('PA_PAXPREPR_UPDATE_ORG') = TRUE) THEN
870 l_org_func_security := 'Y';
871 ELSE
872 l_org_func_security := 'N';
873 END IF;
874
875 --EH Changes
876 BEGIN
877 x_err_stage := 'Validate Attribute Change';
878 pa_project_utils2.validate_attribute_change
879 (X_Context => 'ORGANIZATION_VALIDATION',
880 X_insert_update_mode => 'INSERT',
881 X_calling_module => 'PAXPREPR',
882 X_project_id => NULL,
883 X_task_id => NULL,
884 X_old_value => NULL,
885 X_new_value => l_organization_id,
886 X_project_type => l_project_type,
887 X_project_start_date => x_start_date,
888 X_project_end_date => x_completion_date,
889 X_public_sector_flag => x_public_sector_flag,
890 X_task_manager_person_id => NULL,
891 X_Service_type => NULL,
892 X_task_start_date => NULL,
893 X_task_end_date => NULL,
894 X_entered_by_user_id => FND_GLOBAL.USER_ID,
895 X_attribute_category => x_attribute_category,
896 X_attribute1 => x_attribute1,
897 X_attribute2 => x_attribute2,
898 X_attribute3 => x_attribute3,
899 X_attribute4 => x_attribute4,
900 X_attribute5 => x_attribute5,
901 X_attribute6 => x_attribute6,
902 X_attribute7 => x_attribute7,
903 X_attribute8 => x_attribute8,
904 X_attribute9 => x_attribute9,
905 X_attribute10 => x_attribute10,
906 X_pm_product_code => x_pm_product_code,
907 X_pm_project_reference => x_pm_project_reference,
908 X_pm_task_reference => NULL,
909 -- X_functional_security_flag => 'N', /* Bug#1968394 */
910 X_functional_security_flag => l_org_func_security, /* Bug#1968394 */
911 x_warnings_only_flag => l_warnings_only_flag, --bug3134205
912 X_err_code => x_err_code,
913 X_err_stage => x_err_stage,
914 X_err_stack => x_err_stack);
915
916 IF l_err_code <>0
917 THEN
918 x_err_code := 740;
919 IF x_err_stage IS NULL
920 THEN
921 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_VAL_ATTR_CHANGE');
922 END IF;
923 x_err_stack := x_err_stack||'->pa_project_utils2.validate_attribute_change';
924 rollback to copy_project;
925 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
926 return;
927 END IF;
928
929 EXCEPTION WHEN OTHERS THEN
930 x_err_code := 740;
931 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
932 -- IF x_err_stage IS NULL
933 -- THEN
934 x_err_stage := 'API: '||'pa_project_utils2.validate_attribute_change'||' SQL error message: '
935 ||SUBSTR( SQLERRM,1,1900);
936 -- END IF;
937 rollback to copy_project;
938 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
939 return;
940 END ;
941 else
942 x_err_code := 440;
943 x_err_stage := 'PA_PROJ_ORG_NOT_ACTIVE';
944 return;
945 end if;
946
947 -- Return if err code <>0,since Org validation has failed.
948 IF x_err_code <> 0 THEN
949 return;
950 END IF;
951
952 -- Check for org validation ends here.
953
954 /* code addition for bug 2588244 starts */
955 If x_organization_id is not null then
956 l_flag := 'N';
957 OPEN l_get_default_calendar(x_organization_id);
958 FETCH l_get_default_calendar into l_cal_id;
959 CLOSE l_get_default_calendar;
960 else
961 l_flag := 'Y';
962 end if;
963 /* code addition for bug 2588244 ends */
964
965 -- Location validation
966 if x_country_code is not null then
967 --EH changes
968 BEGIN
969 pa_location_utils.check_location_exists
970 ( p_country_code => x_country_code,
971 p_city => x_city,
972 p_region => x_region,
973 x_return_status => l_return_status,
974 x_location_id => l_location_id);
975 IF l_return_status <> 'S'
976 THEN
977 x_err_code := 770;
978 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_CHK_LOC_EXISTS');
979 x_err_stack := x_err_stack||'->pa_location_utils.check_location_exists';
980 rollback to copy_project;
981 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
982 return;
983 END IF;
984 EXCEPTION WHEN OTHERS THEN
985 x_err_code := 770;
986 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
987 -- IF x_err_stage IS NULL
988 -- THEN
989 x_err_stage := 'API: '||'pa_location_utils.check_location_exists'||
990 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
991 -- END IF;
992 rollback to copy_project;
993 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
994 return;
995 END;
996
997 if l_location_id is null then
998
999 pa_locations_pkg.INSERT_ROW
1000 ( p_CITY => x_city,
1001 p_REGION => x_region,
1002 p_COUNTRY_CODE => x_country_code,
1003 p_CREATION_DATE => sysdate,
1004 p_CREATED_BY => FND_GLOBAL.USER_ID,
1005 p_LAST_UPDATE_DATE => sysdate,
1006 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1007 p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
1008 X_ROWID => l_rowid,
1009 X_LOCATION_ID => l_location_id);
1010
1011 end if;
1012
1013 --Bug#1947235 Updated by MAansari
1014 elsif x_organization_id IS NOT NULL AND x_country_code IS NULL then
1015
1016 --Bug#1947235 Updated by MAansari
1017 -- The following local variables are added
1018 -- l_city VARCHAR2(80);
1019 -- l_region hr_locations_all.region_1%type;
1020 -- l_country_code VARCHAR2(2);
1021 -- x_error_msg_code VARCHAR2(240);
1022 -- l_country_name VARCHAR2(2000);
1023
1024 --EH Changes
1025 BEGIN
1026 pa_location_utils.Get_ORG_Location_Details
1027 (p_organization_id => x_organization_id,
1028 x_country_name => l_country_name,
1029 x_city => l_city,
1030 x_region => l_region,
1031 x_country_code => l_country_code,
1032 x_return_status => l_return_status,
1033 x_error_message_code => x_error_message_code);
1034 IF l_return_status <> 'S'
1035 THEN
1036 x_err_code := 780;
1037 IF x_error_message_code IS NULL
1038 THEN
1039 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_GET_ORG_LOC_DTLS');
1040 ELSE
1041 x_err_stage := x_error_message_code; --bug fix 2680591
1042 END IF;
1043 x_err_stack := x_err_stack||'->pa_location_utils.Get_ORG_Location_Details';
1044 rollback to copy_project;
1045 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
1046 return;
1047 END IF;
1048 EXCEPTION WHEN OTHERS THEN
1049 x_err_code := 780;
1050 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
1051 -- IF x_error_message_code IS NULL
1052 -- THEN
1053 x_err_stage := 'API: '||'pa_location_utils.Get_ORG_Location_Details'||
1054 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
1055 -- END IF;
1056 rollback to copy_project;
1057 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
1058 return;
1059 END;
1060
1061 --EH Changes
1062 BEGIN
1063 pa_location_utils.check_location_exists(
1064 p_country_code => l_country_code,
1065 p_city => l_city,
1066 p_region => l_region,
1067 x_return_status => l_return_status,
1068 x_location_id => l_location_id);
1069 IF l_return_status <> 'S'
1070 THEN
1071 x_err_code := 775;
1072 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_CHK_LOC_EXISTS');
1073 x_err_stack := x_err_stack||'->pa_location_utils.check_location_exists';
1074 rollback to copy_project;
1075 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
1076 return;
1077 END IF;
1078 EXCEPTION WHEN OTHERS THEN
1079 x_err_code := 775;
1080 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
1081 -- IF x_err_stage IS NULL
1082 -- THEN
1083 x_err_stage := 'API: '||'pa_location_utils.check_location_exists'||
1084 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
1085 -- END IF;
1086 rollback to copy_project;
1087 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
1088 return;
1089 END;
1090
1091 If l_location_id is null then
1092 /* Commented the below line for bug 2688170 */
1093 /* If l_city is not null and l_region is not null and */
1094 If l_country_code is not null then
1095 pa_locations_pkg.INSERT_ROW(
1096 p_CITY => l_city,
1097 p_REGION => l_region,
1098 p_COUNTRY_CODE => l_country_code,
1099 p_CREATION_DATE => sysdate,
1100 p_CREATED_BY => FND_GLOBAL.USER_ID,
1101 p_LAST_UPDATE_DATE => sysdate,
1102 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1103 p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
1104 X_ROWID => l_rowid,
1105 X_LOCATION_ID => l_location_id);
1106 end if;
1107 end if;
1108 --end Bug#1947235
1109 end if;
1110
1111 -- copy project
1112 declare
1113 cursor c1 is
1114 select pa_projects_s.nextval from sys.dual;
1115
1116 --bug 2434241
1117 cursor c2 is
1118 select retn_accounting_flag from pa_implementations;
1119 l_retn_accounting_flag VARCHAR2(1);
1120 --bug 2434241
1121
1122 -- Bug 2900258
1123 Cursor c3 is
1124 SELECT 'YES' FROM DUAL
1125 WHERE EXISTS ( SELECT 1 FROM FND_DESCRIPTIVE_FLEXS
1126 WHERE APPLICATION_ID=275
1127 AND APPLICATION_TABLE_NAME='PA_PROJECTS_ALL'
1128 AND DESCRIPTIVE_FLEXFIELD_NAME = 'PA_PROJECTS_DESC_FLEX'
1129 AND CONTEXT_COLUMN_NAME = 'ATTRIBUTE_CATEGORY'
1130 AND DEFAULT_CONTEXT_FIELD_NAME = 'TEMPLATE_FLAG' );
1131
1132 l_is_dff_reference_temp_flag VARCHAR2(3) :='NO'; -- Bug 2900258
1133
1134 begin
1135 x_err_stage := 'get project id from sequence';
1136 open c1;
1137 fetch c1 into x_new_project_id;
1138 close c1;
1139
1140 --bug 2434241
1141 x_err_stage := 'get retention accounting flag from implementations';
1142 open c2;
1143 fetch c2 into l_retn_accounting_flag;
1144 close c2;
1145 --bug 2434241
1146
1147 -- Bug 2900258
1148
1149 IF x_template_flag = 'N' THEN
1150 Open c3;
1151 Fetch c3 into l_is_dff_reference_temp_flag;
1152 close c3;
1153 END IF;
1154
1155 x_err_stage := 'creating project with project id of '||
1156 x_new_project_id || ' by copying original project '||
1157 x_orig_project_id;
1158
1159 --Following code added for selective copy project changes. Tracking Bug no. 3464332
1160 PA_PROJECT_CORE1.populate_default_copy_options( p_src_project_id => x_orig_project_id
1161 ,p_src_template_flag => x_orig_template_flag
1162 ,p_dest_template_flag => x_template_flag
1163 ,x_return_status => l_return_status
1164 ,x_msg_count => l_msg_count
1165 ,x_msg_data => l_msg_data
1166 );
1167 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1168 ROLLBACK TO copy_project;
1169 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
1170 x_err_code := 199;
1171 x_err_stage := 'API: '||'PA_PROJECT_CORE1.POPULATE_DEFAULT_COPY_OPTIONS'||
1172 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
1173 x_err_stack := x_err_stack||'->PA_PROJECT_CORE1.POPULATE_DEFAULT_COPY_OPTIONS';
1174 RETURN;
1175 END IF;
1176 --Following code added for selective copy project changes. Tracking Bug no. 3464332
1177 OPEN cur_get_flag('PR_DFF_FLAG');
1178 FETCH cur_get_flag INTO l_pr_dff_flag;
1179 CLOSE cur_get_flag;
1180 --Bug 3279981 Review :
1181 --IF the invoice method at top task flag has been changed as compared to original project, accordingly we need
1182 --to remove/populate 'WORK' in the distribution rule's invoice method for the new project
1183 --IF the top task inv method flag has been checked for the new project, we need to populate distr. rule's invoice
1184 --method internally as 'WORK', if the distr. rule has been passed
1185 OPEN cur_get_orig_bill_info;
1186 FETCH cur_get_orig_bill_info INTO l_orig_en_top_task_inv_mth, l_orig_rev_acc_mth, l_orig_inv_mth;
1187 CLOSE cur_get_orig_bill_info;
1188
1189 IF 'Y' = l_orig_en_top_task_inv_mth AND 'N' = p_en_top_task_inv_mth_flag AND x_distribution_rule IS NULL THEN
1190 l_new_distribution_rule := upper(l_orig_rev_acc_mth)||'/'||upper(l_orig_inv_mth);
1191 ELSIF 'N' = l_orig_en_top_task_inv_mth AND 'Y' = p_en_top_task_inv_mth_flag AND x_distribution_rule IS NULL THEN
1192 l_new_distribution_rule := upper(l_orig_rev_acc_mth)||'/'||'WORK';
1193 ELSIF 'Y' = p_en_top_task_inv_mth_flag AND x_distribution_rule IS NOT NULL THEN
1194 l_new_distribution_rule := substr(x_distribution_rule, 1, instr(x_distribution_rule,'/')-1)||'/'||'WORK';
1195 ELSE
1196 l_new_distribution_rule := x_distribution_rule;
1197 END IF;
1198 --Bug 3279981 Review
1199
1200 -- 4055319 funding_approval_status changes
1201 -- If destination type is project ( i.e project is getting created ) from template/project
1202 -- retrieve source's funding_approval_status_code
1203 -- if it is mapped to system status FUNDING_PROPOSED , copy source's funding_approval_status_code in the destination
1204 -- else copy it as null
1205
1206 -- below code is to derive default value mentioned above
1207 l_fund_status := NULL;
1208
1209 IF x_template_flag = 'N' THEN
1210 OPEN cur_proj_fund_status;
1211 FETCH cur_proj_fund_status INTO l_org_fund_status, l_org_fund_sys_status;
1212 CLOSE cur_proj_fund_status;
1213
1214 IF l_org_fund_sys_status = 'FUNDING_PROPOSED' THEN
1215 l_fund_status := l_org_fund_status;
1216 END IF;
1217 END IF;
1218
1219 -- 4055319 end
1220
1221 insert into pa_projects (
1222 project_id,
1223 name,
1224 long_name, --long name changes
1225 segment1,
1226 org_id, -- Bug 4363092: MOAC Changes
1227 last_update_date,
1228 last_updated_by,
1229 creation_date,
1230 created_by,
1231 last_update_login,
1232 project_type,
1233 carrying_out_organization_id,
1234 public_sector_flag,
1235 project_status_code,
1236 description,
1237 start_date,
1238 completion_date,
1239 closed_date,
1240 distribution_rule,
1241 labor_invoice_format_id,
1242 non_labor_invoice_format_id,
1243 retention_invoice_format_id,
1244 retention_percentage,
1245 billing_offset,
1246 billing_cycle_id,
1247 labor_std_bill_rate_schdl,
1248 labor_bill_rate_org_id,
1249 labor_schedule_fixed_date,
1250 labor_schedule_discount,
1251 non_labor_std_bill_rate_schdl,
1252 non_labor_bill_rate_org_id,
1253 non_labor_schedule_fixed_date,
1254 non_labor_schedule_discount,
1255 limit_to_txn_controls_flag,
1256 project_level_funding_flag,
1257 invoice_comment,
1258 unbilled_receivable_dr,
1259 unearned_revenue_cr,
1260 summary_flag,
1261 enabled_flag,
1262 segment2,
1263 segment3,
1264 segment4,
1265 segment5,
1266 segment6,
1267 segment7,
1268 segment8,
1269 segment9,
1270 segment10,
1271 attribute_category,
1272 attribute1,
1273 attribute2,
1274 attribute3,
1275 attribute4,
1276 attribute5,
1277 attribute6,
1278 attribute7,
1279 attribute8,
1280 attribute9,
1281 attribute10,
1282 cost_ind_rate_sch_id,
1283 rev_ind_rate_sch_id,
1284 inv_ind_rate_sch_id,
1285 cost_ind_sch_fixed_date,
1286 rev_ind_sch_fixed_date,
1287 inv_ind_sch_fixed_date,
1288 labor_sch_type,
1289 non_labor_sch_type,
1290 template_flag,
1291 verification_date,
1292 created_from_project_id,
1293 template_start_date_active,
1294 template_end_date_active,
1295 pm_product_code,
1296 pm_project_reference,
1297 actual_start_date,
1298 actual_finish_date,
1299 early_start_date,
1300 early_finish_date,
1301 late_start_date,
1302 late_finish_date,
1303 scheduled_start_date,
1304 scheduled_finish_date,
1305 project_currency_code,
1306 allow_cross_charge_flag,
1307 project_rate_date,
1308 project_rate_type,
1309 output_tax_code,
1310 retention_tax_code,
1311 cc_process_labor_flag,
1312 labor_tp_schedule_id,
1313 labor_tp_fixed_date,
1314 cc_process_nl_flag,
1315 nl_tp_schedule_id,
1316 nl_tp_fixed_date,
1317 cc_tax_task_id,
1318 bill_job_group_id,
1319 cost_job_group_id,
1320 role_list_id,
1321 work_type_id,
1322 calendar_id,
1323 initial_team_template_id,
1324 location_id,
1325 probability_member_id,
1326 project_value,
1327 expected_approval_date,
1328 job_bill_rate_schedule_id,
1329 emp_bill_rate_schedule_id,
1330 --MCA Sakthi for MultiAgreementCurreny Project
1331 competence_match_wt,
1332 availability_match_wt,
1333 job_level_match_wt,
1334 enable_automated_search,
1335 search_min_availability,
1336 search_org_hier_id,
1337 search_starting_org_id,
1338 search_country_code,
1339 min_cand_score_reqd_for_nom,
1340 non_lab_std_bill_rt_sch_id,
1341 invproc_currency_type,
1342 revproc_currency_code,
1343 project_bil_rate_date_code,
1344 project_bil_rate_type,
1345 project_bil_rate_date,
1346 project_bil_exchange_rate,
1347 projfunc_currency_code,
1348 projfunc_bil_rate_date_code,
1349 projfunc_bil_rate_type,
1350 projfunc_bil_rate_date,
1351 projfunc_bil_exchange_rate,
1352 funding_rate_date_code,
1353 funding_rate_type,
1354 funding_rate_date,
1355 funding_exchange_rate,
1356 baseline_funding_flag,
1357 projfunc_cost_rate_type,
1358 projfunc_cost_rate_date,
1359 multi_currency_billing_flag,
1360 inv_by_bill_trans_curr_flag,
1361 --MCA Sakthi for MultiAgreementCurreny Project
1362 --MCA1
1363 assign_precedes_task,
1364 --MCA1
1365 --Structure
1366 split_cost_from_workplan_flag,
1367 split_cost_from_bill_flag,
1368 --Structure
1369 --Advertisement, Project Setup and Retention changes
1370
1371 priority_code,
1372 retn_billing_inv_format_id,
1373 retn_accounting_flag,
1374 adv_action_set_id,
1375 start_adv_action_set_flag,
1376
1377 --Advertisement, Project Setup and Retention changes
1378
1379 -- anlee
1380 -- Dates changes
1381 target_start_date,
1382 target_finish_date,
1383 -- End of changes
1384 -- anlee
1385 -- patchset K changes
1386 revaluate_funding_flag,
1387 include_gains_losses_flag,
1388 -- msundare
1389 security_level,
1390 labor_disc_reason_code,
1391 non_labor_disc_reason_code,
1392 -- End of changes
1393 record_version_number,
1394 btc_cost_base_rev_code, /* Bug#2638968 */
1395 --PA L bug 2872708
1396 asset_allocation_method,
1397 capital_event_processing,
1398 cint_rate_sch_id,
1399 cint_eligible_flag,
1400 --End PA L 2872708
1401 structure_sharing_code , --FPM bug 3301192
1402 /* Added for FPM development -Project Setup Changes */
1403 enable_top_task_customer_flag,
1404 enable_top_task_inv_mth_flag,
1405 revenue_accrual_method,
1406 invoice_method,
1407 projfunc_attr_for_ar_flag,
1408 sys_program_flag,
1409 allow_multi_program_rollup,
1410 proj_req_res_format_id,
1411 proj_asgmt_res_format_id,
1412 funding_approval_status_code, -- added for 4055319
1413 revtrans_currency_type, -- Added for Bug 4757022
1414
1415 /* Added for FPM development -Project Setup Changes ends*/
1416 --sunkalya:federal Bug#5511353
1417 DATE_EFF_FUNDS_CONSUMPTION
1418 --sunkalya:federal Bug#5511353
1419 ,ar_rec_notify_flag -- 7508661 : EnC
1420 ,auto_release_pwp_inv -- 7508661 : EnC
1421 ) select
1422 x_new_project_id,
1423 x_project_name,
1424 NVL( x_long_name, x_project_name ), --long name changes
1425 x_new_project_number,
1426 t.org_id, -- Bug 4363092: MOAC Changes
1427 sysdate,
1428 FND_GLOBAL.USER_ID,
1429 sysdate,
1430 FND_GLOBAL.USER_ID,
1431 FND_GLOBAL.LOGIN_ID,
1432 t.project_type,
1433 nvl(x_organization_id, t.carrying_out_organization_id),
1434 nvl(x_public_sector_flag, t.public_sector_flag),
1435 p_project_status_code,
1436 nvl(x_description, t.description),
1437 nvl(x_start_date, t.start_date),
1438 nvl(x_completion_date, t.completion_date + x_delta),
1439 p_closed_date,
1440 --nvl(x_distribution_rule, t.distribution_rule),
1441 nvl(l_new_distribution_rule, t.distribution_rule), --Bug 3279981 Review
1442 t.labor_invoice_format_id,
1443 t.non_labor_invoice_format_id,
1444 t.retention_invoice_format_id,
1445 t.retention_percentage,
1446 t.billing_offset,
1447 t.billing_cycle_id,
1448 t.labor_std_bill_rate_schdl,
1449 t.labor_bill_rate_org_id,
1450 t.labor_schedule_fixed_date,
1451 t.labor_schedule_discount,
1452 t.non_labor_std_bill_rate_schdl,
1453 t.non_labor_bill_rate_org_id,
1454 t.non_labor_schedule_fixed_date,
1455 t.non_labor_schedule_discount,
1456 t.limit_to_txn_controls_flag,
1457 -- t.project_level_funding_flag,
1458 -- this values should not get copyied as no funding
1459 -- information is getting copyied.
1460 '',
1461 t.invoice_comment,
1462 -- Commented following two lines and replaced with NULL
1463 -- for bug # 822580 fix
1464 -- t.unbilled_receivable_dr,
1465 -- t.unearned_revenue_cr,
1466 NULL,
1467 NULL,
1468 t.summary_flag,
1469 t.enabled_flag,
1470 t.segment2,
1471 t.segment3,
1472 t.segment4,
1473 t.segment5,
1474 t.segment6,
1475 t.segment7,
1476 t.segment8,
1477 t.segment9,
1478 t.segment10,
1479 -- Bug 2900258
1480 /* decode(x_attribute_category, null,
1481 t.attribute_category, x_attribute_category), */
1482 /* decode(x_attribute_category, null,
1483 decode(l_is_dff_reference_temp_flag,'YES','N',t.attribute_category), x_attribute_category),
1484 decode(x_attribute_category, null, t.attribute1, x_attribute1),
1485 decode(x_attribute_category, null, t.attribute2, x_attribute2),
1486 decode(x_attribute_category, null, t.attribute3, x_attribute3),
1487 decode(x_attribute_category, null, t.attribute4, x_attribute4),
1488 decode(x_attribute_category, null, t.attribute5, x_attribute5),
1489 decode(x_attribute_category, null, t.attribute6, x_attribute6),
1490 decode(x_attribute_category, null, t.attribute7, x_attribute7),
1491 decode(x_attribute_category, null, t.attribute8, x_attribute8),
1492 decode(x_attribute_category, null, t.attribute9, x_attribute9),
1493 decode(x_attribute_category, null, t.attribute10, x_attribute10),*/
1494 /*Decode for l_pr_dff_flag added for selective copy project options. Tracking bug No 3464332*/
1495 decode(l_pr_dff_flag,'Y',
1496 decode(x_attribute_category, null,
1497 decode(l_is_dff_reference_temp_flag,'YES',
1498 DECODE(t.attribute_category,NULL,NULL,'N'),-- Added for Bug 5757594
1499 t.attribute_category),
1500 x_attribute_category),
1501 null),
1502 decode(l_pr_dff_flag,'Y', decode(x_attribute_category, null, t.attribute1, x_attribute1) ,null),
1503 decode(l_pr_dff_flag,'Y', decode(x_attribute_category, null, t.attribute2, x_attribute2) ,null),
1504 decode(l_pr_dff_flag,'Y', decode(x_attribute_category, null, t.attribute3, x_attribute3) ,null),
1505 decode(l_pr_dff_flag,'Y', decode(x_attribute_category, null, t.attribute4, x_attribute4) ,null),
1506 decode(l_pr_dff_flag,'Y', decode(x_attribute_category, null, t.attribute5, x_attribute5) ,null),
1507 decode(l_pr_dff_flag,'Y', decode(x_attribute_category, null, t.attribute6, x_attribute6) ,null),
1508 decode(l_pr_dff_flag,'Y', decode(x_attribute_category, null, t.attribute7, x_attribute7) ,null),
1509 decode(l_pr_dff_flag,'Y', decode(x_attribute_category, null, t.attribute8, x_attribute8) ,null),
1510 decode(l_pr_dff_flag,'Y', decode(x_attribute_category, null, t.attribute9, x_attribute9) ,null),
1511 decode(l_pr_dff_flag,'Y', decode(x_attribute_category, null, t.attribute10, x_attribute10),null),
1512 t.cost_ind_rate_sch_id,
1513 t.rev_ind_rate_sch_id,
1514 t.inv_ind_rate_sch_id,
1515 t.cost_ind_sch_fixed_date,
1516 t.rev_ind_sch_fixed_date,
1517 t.inv_ind_sch_fixed_date,
1518 t.labor_sch_type,
1519 t.non_labor_sch_type,
1520 decode(x_template_flag, 'Y', 'Y', 'N'),
1521 null,
1522 decode(x_template_flag,'Y',null,x_created_from_proj_id), --Bug:4709791
1523 decode(x_template_flag,'Y', x_start_date, null),
1524 decode(x_template_flag,'Y', x_completion_date, null),
1525 x_pm_product_code,
1526 x_pm_project_reference,
1527 x_actual_start_date,
1528 x_actual_finish_date,
1529 x_early_start_date,
1530 x_early_finish_date,
1531 x_late_start_date,
1532 x_late_finish_date,
1533 -- anlee
1534 -- Dates changes
1535 x_scheduled_start_date,
1536 x_scheduled_finish_date,
1537 -- End of changes
1538 t.project_currency_code,
1539 t.allow_cross_charge_flag,
1540 t.project_rate_date,
1541 t.project_rate_type,
1542 t.output_tax_code,
1543 t.retention_tax_code,
1544 t.cc_process_labor_flag,
1545 t.labor_tp_schedule_id,
1546 t.labor_tp_fixed_date,
1547 t.cc_process_nl_flag,
1548 t.nl_tp_schedule_id,
1549 t.nl_tp_fixed_date,
1550 /* Bug # 2093089 : replaced cc_tax_task_id with NULL. */
1551 /* Reverted the chages of 2093089 for bug # 2185521 */
1552 /* Added decode for Bug 6248841 */
1553 decode(nvl(x_copy_task_flag,'N'),'Y',t.cc_tax_task_id,NULL),
1554 -- NULL,
1555 t.bill_job_group_id,
1556 t.cost_job_group_id,
1557 t.role_list_id,
1558 t.work_type_id,
1559 /* t.calendar_id, commented for bug 2588244 */
1560 /* Added nvl for bug 3185851 */
1561 decode(l_flag, 'Y', t.calendar_id, 'N', nvl(l_cal_id, t.calendar_id)), /* decode added for bug 2588244 */
1562 l_team_template_id,
1563 nvl(l_location_id, t.location_id),
1564 nvl(x_probability_member_id, t.probability_member_id),
1565 nvl(x_project_value, t.project_value),
1566 nvl(x_expected_approval_date, t.expected_approval_date),
1567 t.job_bill_rate_schedule_id,
1568 t.emp_bill_rate_schedule_id,
1569 --MCA Sakthi for MultiAgreementCurreny Project
1570 t.competence_match_wt,
1571 t.availability_match_wt,
1572 t.job_level_match_wt,
1573 t.enable_automated_search,
1574 t.search_min_availability,
1575 t.search_org_hier_id,
1576 t.search_starting_org_id,
1577 t.search_country_code,
1578 t.min_cand_score_reqd_for_nom,
1579 t.non_lab_std_bill_rt_sch_id,
1580 t.invproc_currency_type,
1581 t.revproc_currency_code,
1582 t.project_bil_rate_date_code,
1583 t.project_bil_rate_type,
1584 t.project_bil_rate_date,
1585 t.project_bil_exchange_rate,
1586 t.projfunc_currency_code,
1587 t.projfunc_bil_rate_date_code,
1588 t.projfunc_bil_rate_type,
1589 t.projfunc_bil_rate_date,
1590 t.projfunc_bil_exchange_rate,
1591 t.funding_rate_date_code,
1592 t.funding_rate_type,
1593 t.funding_rate_date,
1594 t.funding_exchange_rate,
1595 t.baseline_funding_flag,
1596 t.projfunc_cost_rate_type,
1597 t.projfunc_cost_rate_date,
1598 --MCA Sakthi for MultiAgreementCurreny Project
1599 t.multi_currency_billing_flag,
1600 t.inv_by_bill_trans_curr_flag,
1601 --MCA
1602 t.assign_precedes_task,
1603 t.split_cost_from_workplan_flag,
1604 t.split_cost_from_bill_flag,
1605 --MCA
1606 --Advertisement, Project Setup and Retention changes
1607
1608 nvl( x_priority_code, t.priority_code ),
1609 t.retn_billing_inv_format_id,
1610 l_retn_accounting_flag, --bugfix 2434241
1611 t.adv_action_set_id,
1612 t.start_adv_action_set_flag,
1613 --Advertisement, Project Setup and Retention changes
1614
1615 -- anlee
1616 -- Dates changes
1617 nvl(x_start_date, t.target_start_date),
1618 nvl(x_completion_date, t.target_finish_date + x_delta),
1619 -- End of changes
1620 -- anlee
1621 -- patchset K changes
1622 t.revaluate_funding_flag,
1623 t.include_gains_losses_flag,
1624 -- msundare
1625 NVL( x_security_level, t.security_level ),
1626 t.labor_disc_reason_code,
1627 t.non_labor_disc_reason_code,
1628 -- End of changes
1629 1,
1630 t.btc_cost_base_rev_code, /* bug#2638968 */
1631 --PA L bug 2872708
1632 t.asset_allocation_method,
1633 t.capital_event_processing,
1634 t.cint_rate_sch_id,
1635 t.cint_eligible_flag,
1636 --End PA L 2872708
1637 t.structure_sharing_code, --FPM bug 3301192
1638 /* Added for FPM development -Project Setup Changes Bug 3279981*/
1639 decode(p_en_top_task_cust_flag, null, t.enable_top_task_customer_flag,
1640 p_en_top_task_cust_flag) ,
1641 decode(p_en_top_task_inv_mth_flag, null, t.enable_top_task_inv_mth_flag,
1642 p_en_top_task_inv_mth_flag) ,
1643 nvl(substr(x_distribution_rule, 1, instr(x_distribution_rule,'/')-1),t.revenue_accrual_method),
1644 nvl(substr(x_distribution_rule, instr(x_distribution_rule,'/')+1),t.invoice_method),
1645 t.projfunc_attr_for_ar_flag,
1646 t.sys_program_flag,
1647 t.allow_multi_program_rollup,
1648 t.proj_req_res_format_id,
1649 t.proj_asgmt_res_format_id,
1650 l_fund_status, -- added for 4055319
1651 t.revtrans_currency_type, -- Added for Bug 4757022
1652 --sunkalya:federal Bug#5511353
1653 decode(p_date_eff_funds_flag, null, nvl(t.DATE_EFF_FUNDS_CONSUMPTION,'N'), p_date_eff_funds_flag)
1654 --sunkalya:federal Bug#5511353
1655 ,t.ar_rec_notify_flag -- 7508661 : EnC
1656 ,t.auto_release_pwp_inv -- 7508661 : EnC
1657 from pa_projects t
1658 where t.project_id = x_orig_project_id;
1659
1660 if (SQL%NOTFOUND) then
1661 x_err_code := 90;
1662 x_err_stage := 'PA_NO_PROJ_CREATED';
1663 rollback to copy_project;
1664 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
1665 return;
1666 end if;
1667 end;
1668
1669 -- 4199336 commented below code because of pjp depandancy issue
1670
1671 -- 4055319 : When ever a project is created or updated in PJT, it may be considered for funding approval,
1672 -- i.e. Submitted to PJP. below API from PJP is called to achieve this.
1673
1674 BEGIN
1675
1676 IF x_template_flag = 'N' THEN
1677
1678 PA_PJP_PVT.Submit_Project_Aw -- Changed from FPA_PROCESS_PVT to PA_PJP_PVT package
1679 (
1680 p_api_version => 1.0
1681 ,p_init_msg_list => FND_API.G_FALSE
1682 ,p_commit => FND_API.G_FALSE
1683 ,p_project_id => x_new_project_id
1684 ,x_return_status => l_return_status
1685 ,x_msg_count => l_msg_count
1686 ,x_msg_data => l_msg_data
1687 );
1688
1689 END IF;
1690
1691 EXCEPTION WHEN OTHERS THEN
1692 x_err_code := 114;
1693 x_err_stage := 'API: '||'FPA_PROCESS_PVT.Submit_Project_Aw'||
1694 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
1695 rollback to copy_project;
1696 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
1697 return;
1698 END;
1699 -- 4055319 end
1700
1701 -- 4199336 end
1702
1703 -- anlee
1704 -- Added for intermedia search
1705 x_err_stage := 'Calling PA_PROJECT_CTX_SEARCH_PVT.Insert_Row API ...';
1706 DECLARE
1707 l_return_status VARCHAR2(1);
1708 CURSOR get_proj_attr
1709 IS
1710 SELECT name, long_name, segment1, description, template_flag
1711 FROM PA_PROJECTS_ALL
1712 WHERE project_id = x_new_project_id;
1713
1714 l_name VARCHAR2(30);
1715 l_long_name VARCHAR2(240);
1716 l_number VARCHAR2(25);
1717 l_description VARCHAR2(250);
1718 l_template_flag VARCHAR2(1);
1719 BEGIN
1720 OPEN get_proj_attr;
1721 FETCH get_proj_attr INTO l_name, l_long_name, l_number, l_description, l_template_flag;
1722 CLOSE get_proj_attr;
1723
1724 PA_PROJECT_CTX_SEARCH_PVT.INSERT_ROW (
1725 p_project_id => x_new_project_id
1726 ,p_template_flag => l_template_flag
1727 ,p_project_name => l_name
1728 ,p_project_number => l_number
1729 ,p_project_long_name => l_long_name
1730 ,p_project_description => l_description
1731 ,x_return_status => l_return_status );
1732
1733 EXCEPTION WHEN OTHERS THEN
1734 x_err_code := 114;
1735 x_err_stage := 'API: '||'PA_PROJECT_CTX_SEARCH_PVT.INSERT_ROW'||
1736 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
1737 rollback to copy_project;
1738 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
1739 return;
1740 END;
1741 -- anlee end of changes
1742
1743 x_err_stage := 'copying project options ';
1744
1745 -- Copy all options relevant to the created_from_project_id
1746
1747 if (x_template_flag = 'Y') then
1748 insert into pa_project_options (
1749 project_id,
1750 option_code,
1751 last_update_date,
1752 last_updated_by,
1753 creation_date,
1754 created_by,
1755 last_update_login
1756 ) select
1757 x_new_project_id,
1758 o.option_code,
1759 sysdate,
1760 FND_GLOBAL.USER_ID,
1761 sysdate,
1762 FND_GLOBAL.USER_ID,
1763 FND_GLOBAL.LOGIN_ID
1764 from pa_project_options o
1765 where o.project_id = x_created_from_proj_id;
1766
1767 x_err_stage := 'copying project copy overrides ';
1768
1769 insert into pa_project_copy_overrides (
1770 project_id,
1771 field_name,
1772 display_name,
1773 last_update_date,
1774 last_updated_by,
1775 creation_date,
1776 created_by,
1777 last_update_login,
1778 limiting_value,
1779 sort_order,
1780 mandatory_flag,
1781 record_version_number
1782 ) select
1783 x_new_project_id,
1784 c.field_name,
1785 c.display_name,
1786 sysdate,
1787 FND_GLOBAL.USER_ID,
1788 sysdate,
1789 FND_GLOBAL.USER_ID,
1790 FND_GLOBAL.LOGIN_ID,
1791 c.limiting_value,
1792 c.sort_order,
1793 c.mandatory_flag,
1794 1
1795 from pa_project_copy_overrides c
1796 where c.project_id = x_created_from_proj_id;
1797 end if;
1798
1799 -- anlee
1800 -- Copying Subteams
1801 x_err_stage := 'Calling PA_PROJECT_SUBTEAMS_PUB.Create_Subteam API ...';
1802
1803 DECLARE
1804 CURSOR get_subteams_csr(c_project_id NUMBER) IS
1805 SELECT name, description
1806 FROM pa_project_subteams
1807 WHERE object_type = 'PA_PROJECTS'
1808 AND object_id = c_project_id;
1809
1810 l_name PA_PROJECT_SUBTEAMS.name%TYPE;
1811 l_description PA_PROJECT_SUBTEAMS.description%TYPE;
1812 l_new_subteam_id NUMBER;
1813 l_subteam_row_id ROWID;
1814 l_return_status VARCHAR2(1);
1815 l_msg_count NUMBER;
1816 l_msg_data VARCHAR2(2000);
1817
1818 BEGIN
1819 OPEN get_subteams_csr(x_orig_project_id);
1820 LOOP
1821 FETCH get_subteams_csr INTO l_name, l_description;
1822 EXIT WHEN get_subteams_csr%NOTFOUND;
1823
1824 PA_PROJECT_SUBTEAMS_PUB.Create_Subteam(
1825 p_subteam_name => l_name
1826 ,p_object_type => 'PA_PROJECTS'
1827 ,p_object_id => x_new_project_id
1828 ,p_description => l_description
1829 ,p_record_version_number => 1
1830 ,p_calling_module => 'PROJECT_SUBTEAMS'
1831 ,p_init_msg_list => FND_API.G_TRUE
1832 ,p_validate_only => FND_API.G_FALSE
1833 ,x_new_subteam_id => l_new_subteam_id
1834 ,x_subteam_row_id => l_subteam_row_id
1835 ,x_return_status => l_return_status
1836 ,x_msg_count => l_msg_count
1837 ,x_msg_data => l_msg_data);
1838
1839 IF (l_return_status <> 'S') Then
1840 rollback to copy_project;
1841 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
1842 x_err_code := 102;
1843 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_CR_PROJ_SUBTEAM');
1844 x_err_stack := x_err_stack||'->PA_PROJECT_PARTIES_PUB.CREATE_SUBTEAM';
1845 return;
1846 END IF;
1847 END LOOP;
1848 CLOSE get_subteams_csr; --Bug 3905797
1849
1850 EXCEPTION WHEN OTHERS THEN
1851 x_err_code := 102;
1852 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
1853 -- IF x_err_stage IS NULL
1854 -- THEN
1855 x_err_stage := 'API: '||'PA_PROJECT_SUBTEAMS_PUB.CREATE_SUBTEAM'||
1856 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
1857 -- END IF;
1858 rollback to copy_project;
1859 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
1860 return;
1861 END;
1862
1863 -- anlee
1864 -- Copying opportunity value attributes
1865
1866 x_err_stage := 'Calling PA_OPPORTUNITY_MGT_PVT.COPY_PROJECT_ATTRIBUTES API ...';
1867
1868 BEGIN
1869 PA_OPPORTUNITY_MGT_PVT.COPY_PROJECT_ATTRIBUTES
1870 ( p_source_project_id => x_orig_project_id
1871 ,p_dest_project_id => x_new_project_id
1872 ,x_return_status => l_return_status
1873 ,x_msg_count => l_msg_count
1874 ,x_msg_data => l_msg_data);
1875
1876 if l_return_status <> 'S' then
1877 x_err_code := 128;
1878 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_COPY_PROJ_OPP_ATTR');
1879 x_err_stack := x_err_stack||'->PA_OPPORTUNITY_MGT_PVT.COPY_PROJECT_ATTRIBUTES';
1880 rollback to copy_project;
1881 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
1882 return;
1883 end if;
1884 EXCEPTION WHEN OTHERS THEN
1885 x_err_code := 128;
1886 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
1887 -- IF x_err_stage IS NULL
1888 -- THEN
1889 x_err_stage := 'API: '||'PA_OPPORTUNITY_MGT_PVT.COPY_PROJECT_ATTRIBUTES'||
1890 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
1891 -- END IF;
1892 rollback to copy_project;
1893 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
1894 return;
1895 END;
1896
1897 OPEN l_get_details_for_opp_csr(x_new_project_id);
1898 FETCH l_get_details_for_opp_csr INTO l_expected_approval_date, l_projfunc_currency_code, l_project_currency_code
1899 ,l_target_start_date, l_target_finish_date , l_cal_id; --bug 2805602
1900 CLOSE l_get_details_for_opp_csr;
1901
1902 OPEN l_get_details_for_opp_csr2(x_new_project_id);
1903 FETCH l_get_details_for_opp_csr2 INTO l_opportunity_value, l_opp_value_currency_code;
1904 CLOSE l_get_details_for_opp_csr2;
1905
1906 -- Modify opportunity value attributes with values entered in quick entry
1907
1908 x_err_stage := 'Calling PA_OPPORTUNITY_MGT_PVT.MODIFY_PROJECT_ATTRIBUTES API ...';
1909
1910 BEGIN
1911 PA_OPPORTUNITY_MGT_PVT.MODIFY_PROJECT_ATTRIBUTES
1912 ( p_project_id => x_new_project_id
1913 ,p_opportunity_value => nvl(x_project_value, l_opportunity_value)
1914 ,p_opp_value_currency_code => nvl(x_opp_value_currency_code, l_opp_value_currency_code)
1915 ,p_expected_approval_date => l_expected_approval_date
1916 ,x_return_status => l_return_status
1917 ,x_msg_count => l_msg_count
1918 ,x_msg_data => l_msg_data);
1919
1920 if l_return_status <> 'S' then
1921 x_err_code := 130;
1922 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_MOD_PROJ_OPP_ATTR');
1923 x_err_stack := x_err_stack||'->PA_OPPORTUNITY_MGT_PVT.MODIFY_PROJECT_ATTRIBUTES';
1924 rollback to copy_project;
1925 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
1926 return;
1927 end if;
1928 EXCEPTION WHEN OTHERS THEN
1929 x_err_code := 130;
1930 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
1931 -- IF x_err_stage IS NULL
1932 -- THEN
1933 x_err_stage := 'API: '||'PA_OPPORTUNITY_MGT_PVT.MODIFY_PROJECT_ATTRIBUTES'||
1934 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
1935 -- END IF;
1936 rollback to copy_project;
1937 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
1938 return;
1939 END;
1940
1941 -- anlee
1942 -- Copying workplan attributes
1943 /*
1944 x_err_stage := 'Calling PA_WORKPLAN_ATTR_PUB.CREATE_PROJ_WORKPLAN_ATTRS API ...';
1945
1946 OPEN l_get_workplan_attrs_csr(x_orig_project_id);
1947 FETCH l_get_workplan_attrs_csr
1948 INTO l_approval_reqd_flag, l_auto_publish_flag, l_approver_source_id, l_approver_source_type, l_default_outline_lvl;
1949 CLOSE l_get_workplan_attrs_csr;
1950
1951 BEGIN
1952 PA_WORKPLAN_ATTR_PUB.CREATE_PROJ_WORKPLAN_ATTRS
1953 ( p_validate_only => FND_API.G_FALSE
1954 ,p_project_id => x_new_project_id
1955 ,p_approval_reqd_flag => l_approval_reqd_flag
1956 ,p_auto_publish_flag => l_auto_publish_flag
1957 ,p_approver_source_id => l_approver_source_id
1958 ,p_approver_source_type => l_approver_source_type
1959 ,p_default_outline_lvl => l_default_outline_lvl
1960 ,x_return_status => l_return_status
1961 ,x_msg_count => l_msg_count
1962 ,x_msg_data => l_msg_data );
1963
1964 if l_return_status <> 'S' then
1965 x_err_code := 710;
1966 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_COPY_WRKPLN_ATTR');
1967 x_err_stack := x_err_stack||'->PA_WORKPLAN_ATTR_PUB.CREATE_PROJ_WORKPLAN_ATTRS';
1968 rollback to copy_project;
1969 return;
1970 end if;
1971 EXCEPTION WHEN OTHERS THEN
1972 x_err_code := 710;
1973 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
1974 -- IF x_err_stage IS NULL
1975 -- THEN
1976 x_err_stage := 'API: '||'PA_WORKPLAN_ATTR_PUB.CREATE_PROJ_WORKPLAN_ATTRS'||
1977 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
1978
1979 -- END IF;
1980 rollback to copy_project;
1981 return;
1982 END;
1983 */
1984
1985 -- Creating assignments
1986 l_start_date := nvl(x_start_date, x_orig_start_date);
1987 l_start_date := nvl(l_start_date, sysdate);
1988
1989 x_err_stage := 'Calling Team_Template.Execute_apply_Team_template API ...';
1990
1991 /* Bug 4092701 - Commented the prm_licensed check */
1992 /* if((l_team_template_id is not null) AND (pa_install.is_prm_licensed() = 'Y')) then */
1993 if l_team_template_id is not null then
1994 --EH Changes
1995 BEGIN
1996
1997 PA_TEAM_TEMPLATES_PUB.EXECUTE_APPLY_TEAM_TEMPLATE
1998 ( p_team_template_id => l_team_template_id
1999 ,p_project_id => x_new_project_id
2000 ,p_project_start_date => l_start_date
2001 ,x_return_status => l_return_status
2002 ,x_msg_count => l_msg_count
2003 ,x_msg_data => l_msg_data);
2004
2005 if l_return_status <> 'S' then
2006 x_err_code := 256;
2007 -- x_err_stage := 'PA_NO_PROJ_CREATED';
2008 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_EXEC_APPLY_TEAM_TMP');
2009 x_err_stack := x_err_stack||'->PA_TEAM_TEMPLATES_PUB.EXECUTE_APPLY_TEAM_TEMPLATE';
2010 rollback to copy_project;
2011 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
2012 return;
2013 end if;
2014 EXCEPTION WHEN OTHERS THEN
2015 x_err_code := 256;
2016 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
2017 -- IF x_err_stage IS NULL
2018 -- THEN
2019 x_err_stage := 'API: '||'PA_TEAM_TEMPLATES_PUB.EXECUTE_APPLY_TEAM_TEMPLATE'||
2020 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
2021 -- END IF;
2022 rollback to copy_project;
2023 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
2024 return;
2025 END;
2026 end if;
2027
2028 /* Declare -- FOR ROLE BASED CHANGES
2029 Commented the below code for the bug 2719670
2030 cursor c_role_based_loop_csr_1(r_project_id IN NUMBER, r_delta IN NUMBER) is
2031 SELECT DISTINCT
2032 p.resource_source_id resource_source_id
2033 , p.project_role_type project_role_type
2034 , p.resource_type_id resource_type_id
2035 , decode(r_delta, null, x_start_date, -- case B-2
2036 start_date_active + r_delta) start_date_active -- A,C,D,B-1
2037 , decode(r_delta, null, x_completion_date, -- case B-2
2038 end_date_active + r_delta) end_date_active -- A,C,D,B-1
2039 FROM pa_project_parties_v p
2040 WHERE p.project_id = r_project_id
2041 AND p.party_type not in ('ORGANIZATION');
2042 Corrected the spelling of organization in the above csr for bug 2689578
2043
2044 cursor c_role_based_loop_csr_2(r_project_id IN NUMBER, r_delta IN NUMBER) is
2045 SELECT DISTINCT
2046 p.resource_source_id resource_source_id
2047 , p.project_role_type project_role_type
2048 , p.resource_type_id resource_type_id
2049 , decode(r_delta, null, x_start_date,
2050 start_date_active + r_delta) start_date_active
2051 , decode(r_delta, null, x_completion_date,
2052 end_date_active + r_delta) end_date_active
2053 FROM pa_project_parties_v p
2054 WHERE p.project_id = r_project_id
2055 AND p.party_type not in ('ORGANIZATION')
2056 AND p.project_role_type not in
2057 (select distinct
2058 limiting_value
2059 from pa_project_copy_overrides
2060 where project_id = x_created_from_proj_id
2061 and field_name = 'KEY_MEMBER');*/
2062
2063 /* Added the below code for bug 2719670, for performance improvement*/
2064 Declare -- FOR ROLE BASED CHANGES
2065 cursor c_role_based_loop_csr_1(r_project_id IN NUMBER, r_delta IN NUMBER) is
2066 SELECT
2067 p.resource_source_id resource_source_id
2068 , r.project_role_type project_role_type
2069 , p.resource_type_id resource_type_id
2070 , decode(r_delta, null, x_start_date, -- case B-2
2071 p.start_date_active + r_delta) start_date_active -- A,C,D,B-1
2072 , decode(r_delta, null, x_completion_date, -- case B-2
2073 p.end_date_active + r_delta) end_date_active -- A,C,D,B-1
2074 FROM pa_project_parties p,PA_PROJECT_ROLE_TYPES_B R
2075 WHERE p.project_id = r_project_id
2076 AND p.project_role_id = r.project_role_id
2077 AND r.role_party_class = 'PERSON';
2078 /*Corrected the spelling of organization in the above csr for bug 2689578*/
2079
2080 cursor c_role_based_loop_csr_2(r_project_id IN NUMBER, r_delta IN NUMBER) is
2081 SELECT
2082 p.project_party_id project_party_id -- Bug 7482391
2083 ,p.resource_source_id resource_source_id
2084 , r.project_role_type project_role_type
2085 , p.resource_type_id resource_type_id
2086 , decode(r_delta, null, x_start_date,
2087 p.start_date_active + r_delta) start_date_active
2088 , decode(r_delta, null, x_completion_date,
2089 p.end_date_active + r_delta) end_date_active
2090 FROM pa_project_parties p,PA_PROJECT_ROLE_TYPES_B R
2091 WHERE p.project_id = r_project_id
2092 AND p.project_role_id = r.project_role_id
2093 AND r.role_party_class = 'PERSON'
2094 AND r.project_role_type not in
2095 (select distinct
2096 limiting_value
2097 from pa_project_copy_overrides
2098 where project_id = x_created_from_proj_id
2099 and field_name = 'KEY_MEMBER');
2100 /*Code addition for bug 2719670 ends*/
2101
2102 /* Bug 3022296 : Added new cursor for checking ext. customer member existance */
2103 CURSOR chk_for_hz_parties(p_person_id NUMBER) IS
2104 SELECT 'Y'
2105 FROM hz_parties h
2106 WHERE h.party_id = p_person_id
2107 AND h.party_type = 'PERSON';
2108 /* End 3022296 */
2109
2110 c_role_based_loop_rec_1 c_role_based_loop_csr_1%ROWTYPE ;
2111 c_role_based_loop_rec_2 c_role_based_loop_csr_2%ROWTYPE ;
2112 v_null_number NUMBER;
2113 v_null_char VARCHAR2(255);
2114 v_null_date DATE;
2115 x_return_status VARCHAR2(255);
2116 x_msg_count NUMBER;
2117 x_msg_data VARCHAR2(2000);
2118 x_project_party_id NUMBER;
2119 x_resource_id NUMBER;
2120 l_hz_parties VARCHAR(1) := 'N'; --Bug 3022296
2121
2122 /* Added for Bug 7482391 */
2123 role_end_date date;
2124 x_delta_1 number;
2125 tmp_min_strt_dt date;
2126 f_flag varchar2(1) := 'Y';
2127
2128 Begin -- FOR ROLE BASED CHANGES
2129
2130 -- begin NEW code for ROLE BASED SECURITY
2131
2132 x_err_stage := 'Calling CREATE_PROJECT_PARTY API ...';
2133
2134 if (x_use_override_flag = 'N') then
2135 FOR c_role_based_loop_rec_1
2136 IN c_role_based_loop_csr_1(x_orig_project_id,x_delta) LOOP
2137
2138 --EH Changes
2139 BEGIN
2140 /* Bug 3022296 : Open cursor */
2141 OPEN chk_for_hz_parties(c_role_based_loop_rec_1.resource_source_id);
2142 fetch chk_for_hz_parties into l_hz_parties;
2143 close chk_for_hz_parties;
2144
2145 --bug 2778730 and modified if through bug 3022296 Start
2146 IF ( ( c_role_based_loop_rec_1.resource_type_id = 101
2147 AND PA_RESOURCE_UTILS.check_res_not_terminated(
2148 p_object_type => 'PERSON',
2149 p_object_id => c_role_based_loop_rec_1.resource_source_id ,
2150 p_effective_start_date => c_role_based_loop_rec_1.start_date_active)
2151 )
2152 OR ( c_role_based_loop_rec_1.resource_type_id = 112
2153 AND nvl(l_hz_parties,'N') = 'Y')
2154 )THEN -- Bug 3022296 End
2155 --bug 2778730
2156 /* Bug 3022296 : End */
2157 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(
2158 p_api_version => 1.0 -- p_api_version
2159 , p_init_msg_list => FND_API.G_TRUE -- p_init_msg_list
2160 , p_commit => FND_API.G_FALSE -- p_commit
2161 , p_validate_only => FND_API.G_FALSE -- p_validate_only
2162 , p_validation_level => FND_API.G_VALID_LEVEL_FULL -- p_validation_level
2163 , p_debug_mode => 'N' -- p_debug_mode
2164 , p_object_id => x_new_project_id -- p_object_id
2165 , p_OBJECT_TYPE => 'PA_PROJECTS' -- p_OBJECT_TYPE
2166 , p_project_role_id => v_null_number -- p_project_role_id
2167 , p_project_role_type => c_role_based_loop_rec_1.project_role_type -- p_project_role_type
2168 , p_RESOURCE_TYPE_ID => c_role_based_loop_rec_1.resource_type_id -- p_RESOURCE_TYPE_ID
2169 , p_resource_source_id => c_role_based_loop_rec_1.resource_source_id -- p_resource_source_id
2170 , p_resource_name => v_null_char -- p_resource_name
2171 , p_start_date_active => c_role_based_loop_rec_1.start_date_active -- p_start_date_active
2172 , p_scheduled_flag => 'N' -- p_scheduled_flag
2173 -- , p_record_version_number => 1 -- p_record_version_number
2174 , p_calling_module => 'FORM' -- p_calling_module
2175 , p_project_id => x_new_project_id -- p_project_id
2176 , p_project_end_date => v_null_date -- p_project_end_date
2177 , p_end_date_active => c_role_based_loop_rec_1.end_date_active -- p_end_date_active
2178 , x_project_party_id => x_project_party_id -- x_project_party_id
2179 , x_resource_id => x_resource_id -- x_resource_id
2180 , x_wf_item_type =>l_wf_item_type
2181 , x_wf_type => l_wf_type
2182 , x_wf_process => l_wf_party_process
2183 , x_assignment_id => l_assignment_id
2184 , x_return_status => x_return_status -- x_return_status
2185 , x_msg_count => x_msg_count -- x_msg_count
2186 , x_msg_data => x_msg_data -- x_msg_data
2187 );
2188 IF (x_return_status <> 'S') Then
2189 -- p_return_status := x_return_status;
2190 -- p_msg_count := x_msg_count;
2191 -- p_msg_data := SUBSTR(p_msg_data||x_msg_data,1,2000);
2192 /* Bug no 1990875 added rollback statement as no project should be created
2193 if copying of key member fails */
2194 rollback to copy_project;
2195 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
2196 x_err_code := 125;
2197 -- x_err_stage := 'PA_NO_PROJ_CREATED'; --commented by ansari
2198 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_CR_PROJ_PARTY_PUB');
2199 x_err_stack := x_err_stack||'->PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY';
2200 return;
2201 END IF;
2202 END IF; --bug 2778730
2203 EXCEPTION WHEN OTHERS THEN
2204 x_err_code := 125;
2205 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
2206 -- IF x_err_stage IS NULL
2207 -- THEN
2208 x_err_stage := 'API: '||'PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY'||
2209 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
2210 -- END IF;
2211 rollback to copy_project;
2212 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
2213 return;
2214 END;
2215 END LOOP;
2216 else
2217 /*Following code added for selective copy project options. Tracking bug No 3464332*/
2218 OPEN cur_get_flag('PR_TEAM_MEMBERS_FLAG');
2219 FETCH cur_get_flag INTO l_pr_team_members_flag;
2220 CLOSE cur_get_flag;
2221
2222 IF 'Y' = l_pr_team_members_flag THEN
2223 /* Bug 2009240 Uncommenting the code in ELSE part */
2224 --EH Changes
2225 FOR c_role_based_loop_rec_2
2226 IN c_role_based_loop_csr_2(x_orig_project_id,x_delta) LOOP
2227
2228 BEGIN
2229 /* Bug 3022296 : Open cursor */
2230 OPEN chk_for_hz_parties(c_role_based_loop_rec_2.resource_source_id);
2231 fetch chk_for_hz_parties into l_hz_parties;
2232 close chk_for_hz_parties;
2233
2234 -- Bug 7482391
2235 open new_prj_end_date_csr(x_new_project_id);
2236 fetch new_prj_end_date_csr into new_prj_end_date;
2237 close new_prj_end_date_csr;
2238
2239 --bug 2778730 and modified the if condition through bug 3022296
2240 IF ( ( c_role_based_loop_rec_2.resource_type_id = 101
2241 AND PA_RESOURCE_UTILS.check_res_not_terminated(
2242 p_object_type => 'PERSON',
2243 p_object_id => c_role_based_loop_rec_2.resource_source_id ,
2244 p_effective_start_date => c_role_based_loop_rec_2.start_date_active)
2245 )
2246 OR ( c_role_based_loop_rec_2.resource_type_id = 112
2247 AND nvl(l_hz_parties,'N') = 'Y')
2248 )THEN -- Bug 3022296
2249 /* Bug 3022296 : End */
2250 --bug 2778730
2251
2252 -- Start bug#5859329
2253
2254 -- IF c_role_based_loop_rec_2.project_role_type = 'PROJECT MANAGER' THEN --changed for bug 6780448
2255
2256 /* Bug 7482391 changes start here*/
2257 l_tmp_start_date_active := c_role_based_loop_rec_2.start_date_active;
2258
2259 if ((c_role_based_loop_rec_2.start_date_active < x_completion_date) OR (x_completion_date is null))then
2260
2261 if ( x_completion_date is not null and c_role_based_loop_rec_2.end_date_active > x_completion_date ) then
2262 l_tmp_end_date_active := x_completion_date;
2263
2264 elsif (x_completion_date is null and new_prj_end_date is not null and c_role_based_loop_rec_2.end_date_active > new_prj_end_date ) then
2265 l_tmp_end_date_active := new_prj_end_date;
2266
2267 elsif (x_start_date is not null and x_orig_start_date is null) then
2268
2269 if (x_delta is null) then
2270 select min(start_date_active) into tmp_min_strt_dt from pa_project_parties where project_id = x_orig_project_id;
2271 x_delta_1 := x_start_date - tmp_min_strt_dt;
2272 select p.start_date_active + x_delta_1 into l_tmp_start_date_active from pa_project_parties p where p.project_party_id=c_role_based_loop_rec_2.project_party_id;
2273
2274 end if;
2275
2276 if(l_tmp_start_date_active < x_start_date or l_tmp_start_date_active > x_completion_date) then
2277 f_flag:='N';
2278 end if;
2279
2280 SELECT p.end_date_active + x_delta_1
2281 INTO role_end_date
2282 FROM pa_project_parties p,
2283 pa_project_role_types_b r
2284 WHERE p.project_id = x_orig_project_id
2285 AND p.project_role_id = r.project_role_id
2286 AND r.role_party_class = 'PERSON'
2287 AND p.project_party_id = c_role_based_loop_rec_2.project_party_id
2288 AND p.resource_source_id = c_role_based_loop_rec_2.resource_source_id
2289 AND p.resource_type_id = c_role_based_loop_rec_2.resource_type_id
2290 AND r.project_role_type = c_role_based_loop_rec_2.project_role_type
2291 AND r.project_role_type NOT IN
2292 (SELECT DISTINCT limiting_value
2293 FROM pa_project_copy_overrides
2294 WHERE project_id = x_created_from_proj_id
2295 AND field_name = 'KEY_MEMBER');
2296
2297 if (new_prj_end_date is not null and role_end_date is not null and role_end_date > new_prj_end_date) then
2298 l_tmp_end_date_active := new_prj_end_date;
2299
2300 else
2301 l_tmp_end_date_active := role_end_date;
2302 end if;
2303
2304 else
2305 l_tmp_end_date_active := c_role_based_loop_rec_2.end_date_active;
2306 /* ELSE --changed for 6780448
2307
2308 l_tmp_end_date_active := v_null_date; --changed for 6780448
2309 */ END IF;
2310
2311 -- End bug#5859329
2312 /* Bug 7482391 changes end here*/
2313 if (f_flag = 'Y') then -- Bug 7482391
2314 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(
2315 p_api_version => 1.0 -- p_api_version
2316 , p_init_msg_list => FND_API.G_TRUE -- p_init_msg_list
2317 , p_commit => FND_API.G_FALSE -- p_commit
2318 , p_validate_only => FND_API.G_FALSE -- p_validate_only
2319 , p_validation_level => FND_API.G_VALID_LEVEL_FULL -- p_validation_level
2320 , p_debug_mode => 'N' -- p_debug_mode
2321 , p_object_id => x_new_project_id -- p_object_id
2322 , p_OBJECT_TYPE => 'PA_PROJECTS' -- p_OBJECT_TYPE
2323 , p_project_role_id => v_null_number -- p_project_role_id
2324 , p_project_role_type => c_role_based_loop_rec_2.project_role_type -- p_project_role_type
2325 , p_RESOURCE_TYPE_ID => c_role_based_loop_rec_2.resource_type_id -- p_RESOURCE_TYPE_ID
2326 , p_resource_source_id => c_role_based_loop_rec_2.resource_source_id -- p_resource_source_id
2327 , p_resource_name => v_null_char -- p_resource_name
2328 , p_start_date_active => l_tmp_start_date_active -- p_start_date_active
2329 , p_scheduled_flag => 'N' -- p_scheduled_flag
2330 -- , p_record_version_number => 1 -- p_record_version_number
2331 , p_calling_module => 'FORM' -- p_calling_module
2332 , p_project_id => x_new_project_id -- p_project_id
2333 , p_project_end_date => v_null_date -- p_project_end_date
2334 , p_end_date_active => l_tmp_end_date_active -- bug#5859329 replaced v_null_date with l_tmp_end_date_active
2335 , x_project_party_id => x_project_party_id -- x_project_party_id
2336 , x_resource_id => x_resource_id -- x_resource_id
2337 , x_wf_item_type =>l_wf_item_type
2338 , x_wf_type => l_wf_type
2339 , x_wf_process => l_wf_party_process
2340 , x_assignment_id => l_assignment_id
2341 , x_return_status => x_return_status -- x_return_status
2342 , x_msg_count => x_msg_count -- x_msg_count
2343 , x_msg_data => x_msg_data -- x_msg_data
2344 );
2345 end if; -- Bug 7482391
2346 IF (x_return_status <> 'S') Then
2347 -- p_return_status := x_return_status;
2348 -- p_msg_count := x_msg_count;
2349 -- p_msg_data := SUBSTR(p_msg_data||x_msg_data,1,2000);
2350 /* Bug no 1990875 added rollback statement as no project should be created
2351 if copying of key member fails */
2352 rollback to copy_project;
2353 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
2354
2355 x_err_code := 125;
2356 -- x_err_stage := 'PA_NO_PROJ_CREATED';
2357 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_CR_PROJ_PARTY_PUB');
2358 x_err_stack := x_err_stack||'->PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY';
2359 return;
2360 END IF;
2361 END IF; -- Bug 7482391
2362 END IF; --bug 2778730
2363 EXCEPTION WHEN OTHERS THEN
2364 x_err_code := 125;
2365 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
2366 -- IF x_err_stage IS NULL
2367 -- THEN
2368 x_err_stage := 'API: '||'PA_PROJECT_PARTIES_PUB.CREATE_PROJECT _PARTY'||' SQL error message: '||SUBSTR( SQLERRM,1,1900);
2369 -- END IF;
2370 rollback to copy_project;
2371 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
2372 return;
2373 END;
2374 END LOOP;
2375 /* Bug 2009240 End of uncommenting */
2376 END IF; --'Y' = l_pr_team_members_flag
2377 end if;
2378 End; -- FOR ROLE BASED CHANGES
2379 -- end NEW code for ROLE BASED SECURITY
2380
2381
2382 -- anlee
2383 -- For Org Role changes
2384 -- Copy over organizations from the source template/project
2385 /*Declare
2386 Commented the below code for the bug 2719670
2387 cursor c_role_based_loop_csr_1(r_project_id IN NUMBER, r_delta IN NUMBER) is
2388 SELECT
2389 p.resource_source_id resource_source_id
2390 , p.project_party_id project_party_id
2391 , p.project_role_type project_role_type
2392 , decode(r_delta, null, x_start_date, -- case B-2
2393 start_date_active + r_delta) start_date_active -- A,C,D,B-1
2394 , decode(r_delta, null, x_completion_date, -- case B-2
2395 end_date_active + r_delta) end_date_active -- A,C,D,B-1
2396 FROM pa_project_parties_v p
2397 WHERE p.project_id = r_project_id
2398 AND p.party_type = 'ORGANIZATION';
2399
2400 cursor c_role_based_loop_csr_2(r_project_id IN NUMBER, r_delta IN NUMBER) is
2401 SELECT
2402 p.resource_source_id resource_source_id
2403 , p.project_party_id project_party_id
2404 , p.project_role_type project_role_type
2405 , decode(r_delta, null, x_start_date,
2406 start_date_active + r_delta) start_date_active
2407 , decode(r_delta, null, x_completion_date,
2408 end_date_active + r_delta) end_date_active
2409 FROM pa_project_parties_v p
2410 WHERE p.project_id = r_project_id
2411 AND p.party_type = 'ORGANIZATION'
2412 AND p.project_role_type not in
2413 (select distinct
2414 limiting_value
2415 from pa_project_copy_overrides
2416 where project_id = x_created_from_proj_id
2417 and field_name = 'ORG_ROLE');*/
2418
2419 /* Added the below code for bug 2719670*/
2420 Declare
2421 cursor c_role_based_loop_csr_1(r_project_id IN NUMBER, r_delta IN NUMBER) is
2422 SELECT
2423 p.resource_source_id resource_source_id
2424 , P.project_party_id project_party_id
2425 , r.project_role_type project_role_type
2426 , decode(r_delta, null, x_start_date, -- case B-2
2427 p.start_date_active + r_delta) start_date_active -- A,C,D,B-1
2428 , decode(r_delta, null, x_completion_date, -- case B-2
2429 p.end_date_active + r_delta) end_date_active -- A,C,D,B-1
2430 FROM pa_project_parties p,PA_PROJECT_ROLE_TYPES_B R
2431 WHERE p.project_id = r_project_id
2432 AND p.project_role_id = r.project_role_id
2433 AND r.role_party_class <> 'PERSON';
2434
2435 cursor c_role_based_loop_csr_2(r_project_id IN NUMBER, r_delta IN NUMBER) is
2436 SELECT
2437 p.resource_source_id resource_source_id
2438 , p.project_party_id project_party_id
2439 , r.project_role_type project_role_type
2440 , decode(r_delta, null, x_start_date,
2441 p.start_date_active + r_delta) start_date_active
2442 , decode(r_delta, null, x_completion_date,
2443 p.end_date_active + r_delta) end_date_active
2444 FROM pa_project_parties p, PA_PROJECT_ROLE_TYPES_B R
2445 WHERE p.project_id = r_project_id
2446 AND p.project_role_id = r.project_role_id
2447 AND r.role_party_class <> 'PERSON'
2448 AND r.project_role_type not in
2449 (select distinct
2450 limiting_value
2451 from pa_project_copy_overrides
2452 where project_id = x_created_from_proj_id
2453 and field_name = 'ORG_ROLE');
2454 /*Code addition for bug 2719670 ends*/
2455
2456 cursor c_cust_created_org_csr(c_project_id IN NUMBER, c_project_party_id IN NUMBER) is
2457 SELECT 'Y'
2458 FROM DUAL
2459 WHERE EXISTS
2460 (SELECT customer_id
2461 FROM pa_project_customers
2462 WHERE project_id = c_project_id
2463 AND project_party_id = c_project_party_id);
2464
2465 c_role_based_loop_rec_1 c_role_based_loop_csr_1%ROWTYPE ;
2466 c_role_based_loop_rec_2 c_role_based_loop_csr_2%ROWTYPE ;
2467 l_temp VARCHAR2(1);
2468 v_null_number NUMBER;
2469 v_null_char VARCHAR2(255);
2470 v_null_date DATE;
2471 x_return_status VARCHAR2(255);
2472 x_msg_count NUMBER;
2473 x_msg_data VARCHAR2(2000);
2474 x_project_party_id NUMBER;
2475 x_resource_id NUMBER;
2476
2477 Begin
2478
2479 x_err_stage := 'Calling CREATE_PROJECT_PARTY API ...';
2480
2481 if (x_use_override_flag = 'N') then
2482 FOR c_role_based_loop_rec_1
2483 IN c_role_based_loop_csr_1(x_orig_project_id, x_delta) LOOP
2484
2485 BEGIN
2486
2487 OPEN c_cust_created_org_csr(x_orig_project_id, c_role_based_loop_rec_1.project_party_id);
2488 FETCH c_cust_created_org_csr INTO l_temp;
2489
2490 -- Don't want to copy over those project organizations that were created along with
2491 -- a customer billing account
2492 if c_cust_created_org_csr%NOTFOUND then
2493 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(
2494 p_api_version => 1.0
2495 , p_init_msg_list => FND_API.G_TRUE
2496 , p_commit => FND_API.G_FALSE
2497 , p_validate_only => FND_API.G_FALSE
2498 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
2499 , p_debug_mode => 'N'
2500 , p_object_id => x_new_project_id
2501 , p_OBJECT_TYPE => 'PA_PROJECTS'
2502 , p_project_role_id => v_null_number
2503 , p_project_role_type => c_role_based_loop_rec_1.project_role_type
2504 , p_RESOURCE_TYPE_ID => 112
2505 , p_resource_source_id => c_role_based_loop_rec_1.resource_source_id
2506 , p_resource_name => v_null_char
2507 , p_start_date_active => c_role_based_loop_rec_1.start_date_active
2508 , p_scheduled_flag => 'N'
2509 , p_calling_module => 'FORM'
2510 , p_project_id => x_new_project_id
2511 , p_project_end_date => v_null_date
2512 , p_end_date_active => c_role_based_loop_rec_1.end_date_active
2513 , x_project_party_id => x_project_party_id
2514 , x_resource_id => x_resource_id
2515 , x_wf_item_type => l_wf_item_type
2516 , x_wf_type => l_wf_type
2517 , x_wf_process => l_wf_party_process
2518 , x_assignment_id => l_assignment_id
2519 , x_return_status => x_return_status
2520 , x_msg_count => x_msg_count
2521 , x_msg_data => x_msg_data );
2522 IF (x_return_status <> 'S') Then
2523 CLOSE c_cust_created_org_csr;
2524 rollback to copy_project;
2525 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
2526 x_err_code := 125;
2527 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_CR_PROJ_PARTY_PUB');
2528 x_err_stack := x_err_stack||'->PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY';
2529 return;
2530 END IF;
2531 END IF;
2532
2533 CLOSE c_cust_created_org_csr;
2534 EXCEPTION WHEN OTHERS THEN
2535 x_err_code := 125;
2536 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
2537 -- IF x_err_stage IS NULL
2538 -- THEN
2539 x_err_stage := 'API: '||'PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY'||
2540 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
2541 -- END IF;
2542 rollback to copy_project;
2543 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
2544 return;
2545 END;
2546 END LOOP;
2547 else
2548
2549 FOR c_role_based_loop_rec_2
2550 IN c_role_based_loop_csr_2(x_orig_project_id, x_delta) LOOP
2551
2552 BEGIN
2553 OPEN c_cust_created_org_csr(x_orig_project_id, c_role_based_loop_rec_2.project_party_id);
2554 FETCH c_cust_created_org_csr INTO l_temp;
2555
2556 -- Don't want to copy over those project organizations that were created along with
2557 -- a customer billing account
2558 if c_cust_created_org_csr%NOTFOUND then
2559 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(
2560 p_api_version => 1.0
2561 , p_init_msg_list => FND_API.G_TRUE
2562 , p_commit => FND_API.G_FALSE
2563 , p_validate_only => FND_API.G_FALSE
2564 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
2565 , p_debug_mode => 'N'
2566 , p_object_id => x_new_project_id
2567 , p_OBJECT_TYPE => 'PA_PROJECTS'
2568 , p_project_role_id => v_null_number
2569 , p_project_role_type => c_role_based_loop_rec_2.project_role_type
2570 , p_RESOURCE_TYPE_ID => 112
2571 , p_resource_source_id => c_role_based_loop_rec_2.resource_source_id
2572 , p_resource_name => v_null_char
2573 , p_start_date_active => c_role_based_loop_rec_2.start_date_active
2574 , p_scheduled_flag => 'N'
2575 , p_calling_module => 'FORM'
2576 , p_project_id => x_new_project_id
2577 , p_project_end_date => v_null_date
2578 , p_end_date_active => c_role_based_loop_rec_2.end_date_active
2579 , x_project_party_id => x_project_party_id
2580 , x_resource_id => x_resource_id
2581 , x_wf_item_type => l_wf_item_type
2582 , x_wf_type => l_wf_type
2583 , x_wf_process => l_wf_party_process
2584 , x_assignment_id => l_assignment_id
2585 , x_return_status => x_return_status
2586 , x_msg_count => x_msg_count
2587 , x_msg_data => x_msg_data );
2588
2589 IF (x_return_status <> 'S') Then
2590 CLOSE c_cust_created_org_csr;
2591
2592 rollback to copy_project;
2593 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
2594 x_err_code := 125;
2595 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_CR_PROJ_PARTY_PUB');
2596 x_err_stack := x_err_stack||'->PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY';
2597 return;
2598 END IF;
2599 END IF;
2600
2601 CLOSE c_cust_created_org_csr;
2602 EXCEPTION WHEN OTHERS THEN
2603 x_err_code := 125;
2604 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
2605 -- IF x_err_stage IS NULL
2606 -- THEN
2607 x_err_stage := 'API: '||'PA_PROJECT_PARTIES_PUB.CREATE_PROJECT _PARTY'||' SQL error message: '||SUBSTR( SQLERRM,1,1900);
2608 -- END IF;
2609 rollback to copy_project;
2610 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
2611 return;
2612 END;
2613 END LOOP;
2614 end if;
2615 End;
2616
2617
2618 if ( x_use_override_flag = 'N' ) then
2619
2620 -- Copy all project classes from original project
2621 x_err_stage := 'copying project classes not using override ';
2622
2623 -- anlee
2624 -- Classification enhancements
2625 INSERT INTO pa_project_classes (
2626 project_id
2627 , class_code
2628 , class_category
2629 , code_percentage
2630 , object_id
2631 , object_type
2632 , last_update_date
2633 , last_updated_by
2634 , creation_date
2635 , created_by
2636 , last_update_login
2637 , attribute_category
2638 , attribute1
2639 , attribute2
2640 , attribute3
2641 , attribute4
2642 , attribute5
2643 , attribute6
2644 , attribute7
2645 , attribute8
2646 , attribute9
2647 , attribute10
2648 , attribute11
2649 , attribute12
2650 , attribute13
2651 , attribute14
2652 , attribute15
2653 --below column added for bug2244929
2654 , record_version_number)
2655 SELECT
2656 x_new_project_id
2657 , pc.class_code
2658 , pc.class_category
2659 , pc.code_percentage
2660 , x_new_project_id
2661 , 'PA_PROJECTS'
2662 , sysdate
2663 , FND_GLOBAL.USER_ID
2664 , sysdate
2665 , FND_GLOBAL.USER_ID
2666 , FND_GLOBAL.LOGIN_ID
2667 , pc.attribute_category
2668 , pc.attribute1
2669 , pc.attribute2
2670 , pc.attribute3
2671 , pc.attribute4
2672 , pc.attribute5
2673 , pc.attribute6
2674 , pc.attribute7
2675 , pc.attribute8
2676 , pc.attribute9
2677 , pc.attribute10
2678 , pc.attribute11
2679 , pc.attribute12
2680 , pc.attribute13
2681 , pc.attribute14
2682 , pc.attribute15
2683 --below column added for bug 2244929
2684 , pc.record_version_number
2685 FROM
2686 pa_project_classes pc
2687 WHERE pc.project_id = x_orig_project_id;
2688 else
2689
2690 -- Copy only project classes with categories that are not
2691 -- overrideable. The other project classes can be entered
2692 -- using Enter Project form.
2693
2694 x_err_stage := 'copying project classes using override ';
2695
2696 INSERT INTO pa_project_classes (
2697 project_id
2698 , class_code
2699 , class_category
2700 , code_percentage
2701 , object_id
2702 , object_type
2703 , last_update_date
2704 , last_updated_by
2705 , creation_date
2706 , created_by
2707 , last_update_login
2708 , attribute_category
2709 , attribute1
2710 , attribute2
2711 , attribute3
2712 , attribute4
2713 , attribute5
2714 , attribute6
2715 , attribute7
2716 , attribute8
2717 , attribute9
2718 , attribute10
2719 , attribute11
2720 , attribute12
2721 , attribute13
2722 , attribute14
2723 , attribute15
2724 -- below column added for bug 2244929
2725 , record_version_number)
2726 SELECT
2727 x_new_project_id
2728 , pc.class_code
2729 , pc.class_category
2730 , pc.code_percentage
2731 , x_new_project_id
2732 , 'PA_PROJECTS'
2733 , sysdate
2734 , FND_GLOBAL.USER_ID
2735 , sysdate
2736 , FND_GLOBAL.USER_ID
2737 , FND_GLOBAL.LOGIN_ID
2738 , pc.attribute_category
2739 , pc.attribute1
2740 , pc.attribute2
2741 , pc.attribute3
2742 , pc.attribute4
2743 , pc.attribute5
2744 , pc.attribute6
2745 , pc.attribute7
2746 , pc.attribute8
2747 , pc.attribute9
2748 , pc.attribute10
2749 , pc.attribute11
2750 , pc.attribute12
2751 , pc.attribute13
2752 , pc.attribute14
2753 , pc.attribute15
2754 --below column added for bug 2244929
2755 , pc.record_version_number
2756 FROM
2757 pa_project_classes pc
2758 WHERE pc.project_id = x_orig_project_id
2759 and pc.class_category not in (select distinct
2760 limiting_value
2761 from pa_project_copy_overrides
2762 where project_id = x_created_from_proj_id
2763 and field_name = 'CLASSIFICATION');
2764 end if;
2765 -- anlee
2766 -- End changes
2767
2768 --Bug 3279981
2769 --If the customer at top task flag has been enabled/diabled (NOT SAME AS SOURCE PROJECT VALUE)
2770 --then we need to set a default top task customer / customer with 100 % contribution
2771 --This customer id is retrieved in the code below
2772 IF x_use_override_flag = 'N' OR x_customer_id is null THEN
2773 DECLARE
2774
2775 CURSOR cur_get_orig_tt_cust_flag IS
2776 SELECT enable_top_task_customer_flag
2777 FROM pa_projects_all
2778 WHERE project_id = x_orig_project_id;
2779
2780 --sunkalya:federal Bug#5511353
2781
2782 CURSOR get_date_eff_funds_flag
2783 IS
2784 SELECT
2785 nvl(DATE_EFF_FUNDS_CONSUMPTION,'N')
2786 FROM
2787 pa_projects_all
2788 WHERE project_id = x_orig_project_id ;
2789 --sunkalya:federal Bug#5511353
2790
2791 l_exclude_cust_id_tbl PA_PLSQL_DATATYPES.NumTabTyp;
2792 l_hghst_ctr_cust_name VARCHAR2(50);
2793 l_hghst_ctr_cust_num VARCHAR2(30);
2794 l_return_status VARCHAR2(10);
2795 l_msg_count NUMBER;
2796 l_msg_data VARCHAR2(2000);
2797
2798
2799 BEGIN
2800
2801 OPEN cur_get_orig_tt_cust_flag;
2802 FETCH cur_get_orig_tt_cust_flag INTO l_orig_en_top_task_cust;
2803 CLOSE cur_get_orig_tt_cust_flag;
2804
2805 OPEN get_date_eff_funds_flag;
2806 FETCH get_date_eff_funds_flag INTO l_orig_date_eff_funds_flag;
2807 CLOSE get_date_eff_funds_flag;
2808
2809 l_exclude_cust_id_tbl(1) := 0;
2810 IF ('Y' = p_en_top_task_cust_flag AND 'N' = l_orig_en_top_task_cust )
2811 OR ('N' = p_en_top_task_cust_flag AND 'Y' = l_orig_en_top_task_cust )
2812 OR ('N' = p_date_eff_funds_flag AND 'Y' = l_orig_date_eff_funds_flag) --sunkalya:federal Bug#5511353
2813 OR ('Y' = p_date_eff_funds_flag AND 'N' = l_orig_date_eff_funds_flag) --sunkalya:federal Bug#5511353
2814 THEN --sunkalya:federal Bug#5511353
2815
2816 IF p_en_top_task_cust_flag ='Y' OR p_date_eff_funds_flag ='Y' THEN
2817
2818 l_check_diff_flag := 'Y';
2819
2820 ELSIF p_en_top_task_cust_flag = 'N' AND p_date_eff_funds_flag = 'N' THEN
2821
2822 l_check_diff_flag := 'N';
2823
2824 ELSIF p_en_top_task_cust_flag ='N' OR p_date_eff_funds_flag ='N' THEN
2825
2826 IF p_en_top_task_cust_flag = 'N' THEN
2827 IF l_orig_date_eff_funds_flag ='Y' THEN
2828
2829 l_check_diff_flag := 'Y';
2830 ELSE
2831 l_check_diff_flag := 'N';
2832 END IF;
2833 ELSIF p_date_eff_funds_flag ='N' THEN
2834 IF l_orig_en_top_task_cust ='Y' THEN
2835
2836 l_check_diff_flag := 'Y';
2837 ELSE
2838 l_check_diff_flag := 'N';
2839 END IF;
2840 END IF;
2841
2842 END IF;
2843 --sunkalya:federal Bug#5511353
2844
2845 IF (p_en_top_task_cust_flag ='N' AND l_orig_en_top_task_cust ='Y') OR
2846 (p_en_top_task_cust_flag ='Y' AND l_orig_en_top_task_cust ='N')
2847 THEN
2848
2849 pa_top_task_cust_invoice_pvt.Get_Highest_Contr_Cust(
2850 P_API_VERSION => 1.0
2851 , P_INIT_MSG_LIST => 'T'
2852 , P_COMMIT => 'F'
2853 , P_VALIDATE_ONLY => 'F'
2854 , P_VALIDATION_LEVEL => 100
2855 , P_DEBUG_MODE => 'N'
2856 , p_calling_module => 'AMG'
2857 , p_project_id => x_orig_project_id
2858 , p_exclude_cust_id_tbl => l_exclude_cust_id_tbl
2859 , x_highst_contr_cust_id => l_hghst_ctr_cust_id
2860 , x_highst_contr_cust_name => l_hghst_ctr_cust_name
2861 , x_highst_contr_cust_num => l_hghst_ctr_cust_num
2862 , x_return_status => l_return_status
2863 , x_msg_count => l_msg_count
2864 , x_msg_data => l_msg_data );
2865
2866 ElSIF p_date_eff_funds_flag ='N' AND l_orig_date_eff_funds_flag ='Y' THEN
2867
2868 PA_CUSTOMERS_CONTACTS_UTILS.Get_Highest_Contr_Fed_Cust(
2869 P_API_VERSION => 1.0
2870 , P_INIT_MSG_LIST => 'T'
2871 , P_COMMIT => 'F'
2872 , P_VALIDATE_ONLY => 'F'
2873 , P_VALIDATION_LEVEL => 100
2874 , P_DEBUG_MODE => 'N'
2875 , p_calling_module => 'AMG'
2876 , p_project_id => x_orig_project_id
2877 , x_highst_contr_cust_id => l_hghst_ctr_cust_id
2878 , x_return_status => l_return_status
2879 , x_msg_count => l_msg_count
2880 , x_msg_data => l_msg_data );
2881 END IF;
2882
2883 --Sunkalya:federal Bug#5511353
2884
2885 IF l_return_status <> 'S' THEN
2886 x_err_code := 930;
2887 x_err_stack := x_err_stack||'->PA_TOP_TASK_CUST_INVOICE_PVT.GET_HIGHEST_CONTR_CUST';
2888 ROLLBACK TO copy_project;
2889 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
2890 RETURN;
2891 END IF;
2892 END IF;
2893 EXCEPTION
2894 WHEN OTHERS THEN
2895 x_err_code := 930;
2896 x_err_stage := 'API: '||'PA_TOP_TASK_CUST_INVOICE_PVT.GET_HIGHEST_CONTR_CUST'||
2897 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
2898 ROLLBACK TO copy_project;
2899 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
2900 RETURN;
2901 END;
2902 END IF;-- IF x_use_override_flag = 'N' OR x_customer_id is null
2903 --Bug 3279981
2904 --If the customer at top task flag has been changed, the customer bill split and default top task
2905 --customer values need to be taken care of in the insert for pa_project_customers
2906
2907 if ( x_use_override_flag = 'N' ) then
2908
2909 -- Copy all project customers from original project
2910 x_err_stage := 'copying project customers not using override';
2911
2912 INSERT INTO pa_project_customers (
2913 project_id
2914 , customer_id
2915 , last_update_date
2916 , last_updated_by
2917 , creation_date
2918 , created_by
2919 , last_update_login
2920 , project_relationship_code
2921 , customer_bill_split
2922 , bill_to_address_id
2923 , ship_to_address_id
2924 , inv_currency_code
2925 , inv_rate_type
2926 , inv_rate_date
2927 , inv_exchange_rate
2928 , allow_inv_user_rate_type_flag
2929 , bill_another_project_flag
2930 , receiver_task_id
2931 , retention_level_code
2932 , record_version_number
2933 -- Customer Account Relationships changes
2934 , bill_to_customer_id
2935 , ship_to_customer_id
2936 -- Customer Account Relationships changes
2937 --Added for bug 3279981
2938 , default_top_task_cust_flag )
2939 SELECT
2940 x_new_project_id
2941 , cust.customer_id
2942 , sysdate
2943 , FND_GLOBAL.USER_ID
2944 , sysdate
2945 , FND_GLOBAL.USER_ID
2946 , FND_GLOBAL.LOGIN_ID
2947 , cust.project_relationship_code
2948 --Bug 3279981
2949 --, cust.customer_bill_split
2950 , decode(l_check_diff_flag, --sunkalya:federal Bug#5511353
2951 NULL , cust.customer_bill_split ,
2952 'Y', null ,
2953 'N', decode(l_hghst_ctr_cust_id, cust.customer_id, 100, 0),
2954 cust.customer_bill_split )
2955 , cust.bill_to_address_id
2956 , cust.ship_to_address_id
2957 , cust.inv_currency_code
2958 , cust.inv_rate_type
2959 , cust.inv_rate_date
2960 , cust.inv_exchange_rate
2961 , cust.allow_inv_user_rate_type_flag
2962 , cust.bill_another_project_flag
2963 , cust.receiver_task_id
2964 , cust.retention_level_code
2965 , 1
2966 -- Customer Account Relationships changes
2967 , bill_to_customer_id
2968 , ship_to_customer_id
2969 -- Customer Account Relationships changes
2970 --Added for bug 3279981
2971 , decode(p_en_top_task_cust_flag,
2972 l_orig_en_top_task_cust, cust.default_top_task_cust_flag,
2973 'Y', decode(l_hghst_ctr_cust_id, cust.customer_id, 'Y', 'N'),
2974 'N','N',
2975 cust.default_top_task_cust_flag
2976 )
2977 FROM
2978 pa_project_customers cust
2979 WHERE cust.project_id = x_orig_project_id;
2980
2981 -- anlee org role changes
2982 -- create a project party if the added customer is an organization
2983 DECLARE
2984 l_party_id NUMBER;
2985 l_project_party_id NUMBER;
2986 l_resource_id NUMBER;
2987 l_wf_item_type VARCHAR2(30);
2988 l_wf_type VARCHAR2(30);
2989 l_wf_party_process VARCHAR2(30);
2990 l_assignment_id NUMBER;
2991 l_return_status VARCHAR2(1);
2992 l_msg_data VARCHAR2(2000);
2993 l_msg_count NUMBER;
2994 l_end_date_active DATE;
2995 l_customer_id NUMBER;
2996
2997 CURSOR l_customers_csr (c_project_id IN NUMBER) IS
2998 SELECT customer_id
2999 FROM PA_PROJECT_CUSTOMERS
3000 WHERE project_id = c_project_id;
3001
3002 CURSOR l_check_org_csr (c_customer_id IN NUMBER) IS
3003 SELECT PARTY_ID
3004 FROM PA_CUSTOMERS_V
3005 WHERE CUSTOMER_ID = c_customer_id
3006 AND PARTY_TYPE = 'ORGANIZATION';
3007 BEGIN
3008
3009 OPEN l_customers_csr(x_orig_project_id);
3010 LOOP
3011 FETCH l_customers_csr INTO l_customer_id;
3012 EXIT WHEN l_customers_csr%NOTFOUND;
3013
3014 l_party_id := null;
3015 l_project_party_id := null;
3016 OPEN l_check_org_csr(l_customer_id);
3017 FETCH l_check_org_csr INTO l_party_id;
3018 IF l_check_org_csr%NOTFOUND then
3019 l_party_id := null;
3020 END IF;
3021 CLOSE l_check_org_csr;
3022
3023 if l_party_id is not null then
3024
3025 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(
3026 p_validate_only => FND_API.G_FALSE
3027 , p_object_id => x_new_project_id
3028 , p_OBJECT_TYPE => 'PA_PROJECTS'
3029 , p_project_role_id => 100
3030 , p_project_role_type => 'CUSTOMER_ORG'
3031 , p_RESOURCE_TYPE_ID => 112
3032 , p_resource_source_id => l_party_id
3033 , p_start_date_active => null
3034 , p_calling_module => 'FORM'
3035 , p_project_id => x_new_project_id
3036 , p_project_end_date => null
3037 , p_end_date_active => l_end_date_active
3038 , x_project_party_id => l_project_party_id
3039 , x_resource_id => l_resource_id
3040 , x_wf_item_type => l_wf_item_type
3041 , x_wf_type => l_wf_type
3042 , x_wf_process => l_wf_party_process
3043 , x_assignment_id => l_assignment_id
3044 , x_return_status => l_return_status
3045 , x_msg_count => l_msg_count
3046 , x_msg_data => l_msg_data );
3047
3048 IF (l_return_status <> 'S') Then
3049
3050 rollback to copy_project;
3051 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
3052 x_err_code := 167;
3053 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_CR_PROJ_PARTY_PUB');
3054 x_err_stack := x_err_stack||'->PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY';
3055 return;
3056 END IF;
3057
3058 -- Add the new project party ID to the customers row
3059 UPDATE PA_PROJECT_CUSTOMERS
3060 SET project_party_id = l_project_party_id
3061 WHERE project_id = x_new_project_id
3062 AND customer_id = l_customer_id;
3063 end if;
3064 end loop;
3065 CLOSE l_customers_csr; --Bug 3905797
3066
3067 EXCEPTION WHEN OTHERS THEN
3068 x_err_code := 167;
3069 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
3070 -- IF x_err_stage IS NULL
3071 -- THEN
3072 x_err_stage := 'API: '||'PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY'||' SQL error message: '||SUBSTR( SQLERRM,1,1900);
3073 -- END IF;
3074 rollback to copy_project;
3075 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
3076 return;
3077 END;
3078
3079 elsif x_customer_id is null then --Bug 2984536. This api is except in case of org forecast project copy is always
3080 -- called with the x_use_override_flag as Y. Now we will do different processing
3081 -- depending on if customer has passed the customer id thru quick entry.
3082
3083 -- Copy only project customers with relationship code that
3084 -- are not overrideable. The other customers can be entered
3085 -- using Enter Project form.
3086 x_err_stage := 'copying project customers using override';
3087
3088 /* Commented for bug 2984536. Need to do processing for each of the customer before copying
3089 into the target project.
3090 INSERT INTO pa_project_customers (
3091 project_id
3092 , customer_id
3093 , last_update_date
3094 , last_updated_by
3095 , creation_date
3096 , created_by
3097 , last_update_login
3098 , project_relationship_code
3099 , customer_bill_split
3100 , bill_to_address_id
3101 , ship_to_address_id
3102 , inv_currency_code
3103 , inv_rate_type
3104 , inv_rate_date
3105 , inv_exchange_rate
3106 , allow_inv_user_rate_type_flag
3107 , bill_another_project_flag
3108 , receiver_task_id
3109 , record_version_number
3110 ---Customer Account Relationship
3111 , bill_to_customer_id
3112 , ship_to_customer_id)
3113 ---Customer Account Relationship
3114
3115 SELECT
3116 x_new_project_id
3117 , cust.customer_id
3118 , sysdate
3119 , FND_GLOBAL.USER_ID
3120 , sysdate
3121 , FND_GLOBAL.USER_ID
3122 , FND_GLOBAL.LOGIN_ID
3123 , cust.project_relationship_code
3124 , cust.customer_bill_split
3125 , cust.bill_to_address_id
3126 , cust.ship_to_address_id
3127 , cust.inv_currency_code
3128 , cust.inv_rate_type
3129 , cust.inv_rate_date
3130 , cust.inv_exchange_rate
3131 , cust.allow_inv_user_rate_type_flag
3132 , cust.bill_another_project_flag
3133 , cust.receiver_task_id
3134 , 1
3135 ---Customer Account Relationship
3136 , cust.bill_to_customer_id
3137 , cust.ship_to_customer_id
3138 ---Customer Account Relationship
3139 FROM
3140 pa_project_customers cust
3141 WHERE cust.project_id = x_orig_project_id
3142 and not exists
3143 (select null
3144 from pa_project_copy_overrides
3145 where project_id = x_created_from_proj_id
3146 and field_name = 'CUSTOMER_NAME');
3147 */
3148
3149 -- Bug 2984536. This processing is done only if no customer id is passed as input.
3150 -- Fetch all the customers for the source project. Call get_customer_info to obtain the
3151 -- Valid customer details. Use this info thus obtained to insert a customer record.
3152 DECLARE
3153 -- Define the cursor to fetch the customer details from the source.
3154 CURSOR cur_cust_info(c_project_id pa_projects_all.project_id%TYPE,
3155 c_created_from_proj_id pa_projects_all.project_id%TYPE ) IS -- Added the parameter for bug 3726109
3156 SELECT
3157 cust.customer_id
3158 , cust.project_relationship_code
3159 , cust.customer_bill_split
3160 , cust.bill_to_address_id
3161 , cust.ship_to_address_id
3162 , cust.inv_currency_code
3163 , cust.inv_rate_type
3164 , cust.inv_rate_date
3165 , cust.inv_exchange_rate
3166 , cust.allow_inv_user_rate_type_flag
3167 , cust.bill_another_project_flag
3168 , cust.receiver_task_id
3169 , cust.bill_to_customer_id
3170 , cust.ship_to_customer_id
3171 , cust.default_top_task_cust_flag
3172 FROM
3173 pa_project_customers cust
3174 WHERE cust.project_id = c_project_id
3175 and not exists -- Added the and condition for bug 3726109
3176 (select null
3177 from pa_project_copy_overrides
3178 where project_id = c_created_from_proj_id
3179 and field_name = 'CUSTOMER_NAME');
3180 --CURSORS BELOW ADDED BY ADITI for Bug 3110489 Code Change Begins
3181 CURSOR cur_contact_info(c_project_id pa_projects_all.project_id%TYPE,
3182 c_created_from_proj_id pa_projects_all.project_id%TYPE,
3183 c_customer_id pa_project_customers.customer_id%TYPE)
3184 IS
3185 select contact.customer_id,
3186 contact.contact_id,
3187 contact.project_contact_type_code,
3188 contact.bill_ship_customer_id
3189 from pa_project_contacts contact
3190 where contact.project_id = c_project_id
3191 and not exists
3192 (select null
3193 from pa_project_copy_overrides
3194 where project_id = c_created_from_proj_id
3195 and field_name = 'CUSTOMER_NAME')
3196 and contact.customer_id = c_customer_id;
3197
3198 CURSOR cur_cust_override_exists (c_created_from_proj_id pa_projects_all.project_id%TYPE )
3199 IS
3200 select 'Y'
3201 from pa_project_copy_overrides
3202 where project_id = c_created_from_proj_id
3203 and field_name = 'CUSTOMER_NAME';
3204 /** Code Change for Bug 3110489 ends **/
3205 l_cur_cust_info cur_cust_info%rowtype;
3206 l_cur_contact_info cur_contact_info%rowtype;
3207 l_bill_to_contact_id pa_project_contacts.contact_id%TYPE;
3208 l_ship_to_contact_id pa_project_contacts.contact_id%TYPE;
3209 l_copy_bill_to_contact_id pa_project_contacts.contact_id%TYPE; --Added for BUg 3110489
3210 l_copy_ship_to_contact_id pa_project_contacts.contact_id%TYPE; --Added for BUg 3110489
3211 l_dummy VARCHAR2(1); --Added for BUg 3110489
3212
3213
3214 /*The following parameter is_contact_present_flag is added for Bug#4770535 for get_customer_info not
3215 getting called unnecessarily second time when contact exist for the customer. */
3216 is_contact_present_flag VARCHAR2(1):= 'N';
3217 -- End of change For Bug#4770535
3218
3219 BEGIN
3220
3221 -- obtain the customer info from the source.
3222 /* Addded one more parameter x_created_from_proj_id to the cursor for bug 3726109 */
3223 OPEN cur_cust_info(x_orig_project_id, x_created_from_proj_id);
3224 Loop
3225 --Bug#4770535 The following two parameters are initialized to null
3226 l_copy_bill_to_contact_id := NULL;
3227 l_copy_ship_to_contact_id := NULL;
3228 --End of Change for Bug#4770535
3229
3230 Fetch cur_cust_info into l_cur_cust_info;
3231
3232 EXIT WHEN cur_cust_info%NOTFOUND;
3233 -- Made the flag is_contact_present_flag 'N' so that it gets set evertime the cur_cust_info loop runs for Bug#4770535
3234 is_contact_present_flag := 'N';
3235 -- End of change done for Bug#4770535
3236
3237 /** CODE ADDED BY ADITI for BUg 3110489 This code is added to cppy the contacts from
3238 the source project to target project **/
3239 OPEN cur_contact_info(x_orig_project_id, x_created_from_proj_id,l_cur_cust_info.customer_id);
3240 Loop
3241 /** Variables are initialised to null at the beginning of the loop
3242 so that only one record gets inserted at a time and primary
3243 key violations do not occur **/
3244
3245 /*Bug#4770535. parameters to l_bill_to_contact_id ,l_ship_to_contact_id ,l_copy_bill_to_contact_id,
3246 l_copy_ship_to_contact_idare set to NULL so that no primary key violations occur */
3247 l_bill_to_contact_id := NULL;
3248 l_ship_to_contact_id := NULL;
3249 l_copy_bill_to_contact_id := NULL;
3250 l_copy_ship_to_contact_id := NULL;
3251
3252 --End of changes for Bug#4770535
3253
3254 FETCH cur_contact_info INTO l_cur_contact_info;
3255 EXIT when cur_contact_info%NOTFOUND;
3256
3257 --Set the is_contact_present_flag to Y as the contact exists for this customer.Bug#4770535
3258 is_contact_present_flag := 'Y';
3259 --End of change for Bug#4770535
3260
3261 IF l_cur_contact_info.project_contact_type_code = 'BILLING' THEN
3262
3263 -- Bug#4770535. Added l_bill_to_contact_id also which will be used below in get_customer_info
3264 l_bill_to_contact_id := l_cur_contact_info.CONTACT_ID;
3265 l_copy_bill_to_contact_id := l_cur_contact_info.CONTACT_ID;
3266 -- End of change for Bug#4770535
3267
3268
3269
3270 ELSIF l_cur_contact_info.project_contact_type_code = 'SHIPPING' THEN
3271
3272 --Bug#4770535.Added l_ship_to_contact_id also which will be used below in get_customer_info
3273 l_ship_to_contact_id := l_cur_contact_info.CONTACT_ID;
3274 l_copy_ship_to_contact_id := l_cur_contact_info.CONTACT_ID;
3275 --End of change for Bug#4770535
3276
3277
3278 END if;
3279
3280 /** CODE changes END for Bug 3110489 **/
3281
3282
3283 /*Bug#4770535.Instead of passing l_copy_bill_to_contact_id and l_copy_ship_to_contact_id, now
3284 l_bill_to_contact_id and l_ship_to_contact_id are being passed.*/
3285 pa_customer_info.Get_Customer_Info
3286 ( X_project_ID => x_new_project_id,
3287 X_Customer_Id => l_cur_cust_info.customer_id,
3288 p_quick_entry_flag => 'N',
3289 X_Bill_To_Customer_Id => l_cur_cust_info.bill_to_customer_id,
3290 X_Ship_To_Customer_Id => l_cur_cust_info.ship_to_customer_id,
3291 X_Bill_To_Address_Id => l_cur_cust_info.bill_to_address_id,
3292 X_Ship_To_Address_Id => l_cur_cust_info.ship_to_address_id,
3293 X_Bill_To_Contact_Id => l_bill_to_contact_id,
3294 X_Ship_To_Contact_Id => l_ship_to_contact_id,
3295 X_Err_Code => x_err_code,
3296 X_Err_Stage => x_err_stage,
3297 X_Err_Stack => x_err_stack
3298 );
3299 IF x_err_code <> 0
3300 THEN
3301 rollback to copy_project;
3302 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
3303
3304 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3305 THEN
3306 IF NOT pa_project_pvt.check_valid_message(x_err_stage)
3307 THEN
3308 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3309 p_msg_name =>
3310 'PA_GET_CUSTOMER_INFO_FAILED');
3311 ELSE
3312 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3313 p_msg_name => x_err_stage);
3314 END IF;
3315 END IF;
3316 return;
3317 END IF;
3318
3319 --Added for bug 3279981
3320 --If the default top task customer flag is NOT THE SAME AS IN THE SOURCE PROJECT,
3321 --the customer bill split and default top task customer flag values need to be taken care of
3322 --sunkalya:federal changes****** Bug#5511353
3323
3324 IF ('Y' = p_en_top_task_cust_flag AND 'N' = l_orig_en_top_task_cust )
3325 OR ('N' = p_en_top_task_cust_flag AND 'Y' = l_orig_en_top_task_cust )
3326 OR ('N' = p_date_eff_funds_flag AND 'Y' = l_orig_date_eff_funds_flag)
3327 OR ('Y' = p_date_eff_funds_flag AND 'N' = l_orig_date_eff_funds_flag)
3328 THEN
3329
3330 IF p_en_top_task_cust_flag ='Y' OR p_date_eff_funds_flag ='Y' THEN --case #1
3331
3332
3333 l_cur_cust_info.customer_bill_split := NULL;
3334
3335 IF ( 'Y' = p_en_top_task_cust_flag ) THEN
3336
3337 IF l_hghst_ctr_cust_id = l_cur_cust_info.customer_id THEN
3338
3339 l_cur_cust_info.default_top_task_cust_flag := 'Y';
3340
3341 ELSE
3342
3343 l_cur_cust_info.default_top_task_cust_flag := 'N';
3344
3345 END IF;
3346
3347 END IF;
3348
3349 ELSIF p_en_top_task_cust_flag = 'N' AND p_date_eff_funds_flag = 'N' THEN --case #2
3350
3351 IF l_hghst_ctr_cust_id = l_cur_cust_info.customer_id THEN
3352
3353 l_cur_cust_info.customer_bill_split := 100;
3354
3355 ELSE
3356
3357 l_cur_cust_info.customer_bill_split := 0;
3358
3359 END IF;
3360
3361 l_cur_cust_info.default_top_task_cust_flag := 'N';
3362
3363
3364 ELSIF p_en_top_task_cust_flag ='N' OR p_date_eff_funds_flag ='N' THEN --case #3.When one of the flag
3365 --Is Only passed and the other as null.
3366
3367 IF p_en_top_task_cust_flag = 'N' THEN
3368
3369 IF l_orig_date_eff_funds_flag ='Y' THEN
3370
3371 l_cur_cust_info.default_top_task_cust_flag := 'N';
3372
3373 ELSE
3374
3375 IF l_hghst_ctr_cust_id = l_cur_cust_info.customer_id THEN
3376
3377 l_cur_cust_info.customer_bill_split := 100;
3378
3379 ELSE
3380
3381 l_cur_cust_info.customer_bill_split := 0;
3382
3383 END IF;
3384 l_cur_cust_info.default_top_task_cust_flag := 'N';
3385
3386 END IF;
3387
3388 ELSIF p_date_eff_funds_flag ='N' THEN
3389
3390 IF l_orig_en_top_task_cust ='N' THEN
3391
3392 IF l_hghst_ctr_cust_id = l_cur_cust_info.customer_id THEN
3393
3394 l_cur_cust_info.customer_bill_split := 100;
3395
3396 ELSE
3397
3398 l_cur_cust_info.customer_bill_split := 0;
3399
3400 END IF;
3401 l_cur_cust_info.default_top_task_cust_flag := 'N';
3402
3403 END IF;
3404 END IF;
3405
3406 END IF;
3407 END IF;
3408 --sunkalya:federal changes*****
3409
3410 -- insert the project customer record.
3411
3412 --Added the following if conditions for Bug#4770535.If there is no billing or shipping contact for this customer,
3413 --we should not copy the values returned from get_customer_info by mistake as it gives the contact at primary site.
3414
3415
3416 IF (l_copy_bill_to_contact_id IS NULL OR l_copy_bill_to_contact_id= FND_API.G_MISS_NUM) THEN
3417 l_bill_to_contact_id := NULL;
3418 END IF;
3419
3420 IF (l_copy_ship_to_contact_id IS NULL OR l_copy_ship_to_contact_id = FND_API.G_MISS_NUM) THEN
3421 l_ship_to_contact_id := NULL;
3422 END IF;
3423
3424
3425 /*Bug#4770535 changed the variables l_copy_bill_to_contact_id and l_copy_ship_to_contact_id to l_bill_to_contact_id and
3426 l_ship_to_contact_id respectively which are passed below into Create_Customer_Contacts. */
3427
3428
3429 pa_customer_info.Create_Customer_Contacts
3430 ( X_Project_Id => x_new_project_id,
3431 X_Customer_Id => l_cur_cust_info.customer_id,
3432 X_Project_Relation_Code => l_cur_cust_info.project_relationship_code,
3433 X_Customer_Bill_Split => l_cur_cust_info.customer_bill_split,
3434 X_Bill_To_Customer_Id => l_cur_cust_info.bill_to_customer_id,
3435 X_Ship_To_Customer_Id => l_cur_cust_info.ship_to_customer_id,
3436 X_Bill_To_Address_Id => l_cur_cust_info.bill_to_address_id,
3437 X_Ship_To_Address_Id => l_cur_cust_info.ship_to_address_id,
3438 X_Bill_To_Contact_Id => l_bill_to_contact_id, --Bug#4770535
3439 X_Ship_To_Contact_Id => l_ship_to_contact_id, --Bug#4770535
3440 X_Inv_Currency_Code => l_cur_cust_info.inv_currency_code,
3441 X_Inv_Rate_Type => l_cur_cust_info.inv_rate_type,
3442 X_Inv_Rate_Date => l_cur_cust_info.inv_rate_date,
3443 X_Inv_Exchange_Rate => l_cur_cust_info.inv_exchange_rate,
3444 X_Allow_Inv_Rate_Type_Fg => l_cur_cust_info.allow_inv_user_rate_type_flag,
3445 X_Bill_Another_Project_Fg => l_cur_cust_info.bill_another_project_flag,
3446 X_Receiver_Task_Id => l_cur_cust_info.receiver_task_id,
3447 P_default_top_task_customer => l_cur_cust_info.default_top_task_cust_flag,
3448 X_User => FND_GLOBAL.USER_ID,
3449 X_Login => FND_GLOBAL.LOGIN_ID,
3450 X_Err_Code => x_err_code,
3451 X_Err_Stage => x_err_stage,
3452 X_Err_Stack => x_err_stack
3453 );
3454 IF x_err_code > 0
3455 THEN
3456 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3457 THEN
3458 IF NOT pa_project_pvt.check_valid_message(x_err_stage)
3459 THEN
3460 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3461 p_msg_name => 'PA_PROJ_CR_CONTACTS_FAILED');
3462 ELSE
3463 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3464 p_msg_name => x_err_stage);
3465 END IF;
3466 rollback to copy_project;
3467 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
3468 return;
3469 END IF;
3470
3471 ELSIF x_err_code < 0
3472 THEN
3473 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3474 p_msg_name => 'PA_PROJ_CR_CONTACTS_FAILED');
3475 rollback to copy_project;
3476 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
3477 return;
3478 END IF;
3479
3480
3481 End Loop; -- End of Loop for cur_contact_info Bug 3110489
3482 CLOSE cur_contact_info; --Added for Bug 3110489
3483
3484 /** The code written above is copied below. This is to facilitate the case when the source
3485 project has a customer, but no contacts. Added for Bug 3110489 **/
3486
3487 /* Added the below IF condition for Bug#4770535. The call to get_customer_info API below should be executed only when
3488 there are no contact to the customer. The IF condition added below checks for this condition and eliminates unconditional call to
3489 get_customer_info. */
3490
3491 IF( is_contact_present_flag = 'N') THEN
3492
3493
3494 /*Bug#4770535. Always pass null in place of X_Bill_To_Contact_Id and X_Ship_To_Contact_Id below
3495 as in this case no contact exists for this customer. Also as they are IN OUT parameters inside get_customer_info,
3496 we need to reset them*/
3497
3498 l_bill_to_contact_id := null;
3499 l_ship_to_contact_id := null;
3500 pa_customer_info.Get_Customer_Info
3501 ( X_project_ID => x_new_project_id,
3502 X_Customer_Id => l_cur_cust_info.customer_id,
3503 p_quick_entry_flag => 'N',
3504 X_Bill_To_Customer_Id => l_cur_cust_info.bill_to_customer_id,
3505 X_Ship_To_Customer_Id => l_cur_cust_info.ship_to_customer_id,
3506 X_Bill_To_Address_Id => l_cur_cust_info.bill_to_address_id,
3507 X_Ship_To_Address_Id => l_cur_cust_info.ship_to_address_id,
3508 X_Bill_To_Contact_Id => l_bill_to_contact_id,
3509 X_Ship_To_Contact_Id => l_ship_to_contact_id,
3510 X_Err_Code => x_err_code,
3511 X_Err_Stage => x_err_stage,
3512 X_Err_Stack => x_err_stack
3513 );
3514
3515 IF x_err_code <> 0
3516 THEN
3517 rollback to copy_project;
3518 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
3519
3520 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3521 THEN
3522 IF NOT pa_project_pvt.check_valid_message(x_err_stage)
3523 THEN
3524 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3525 p_msg_name =>
3526 'PA_GET_CUSTOMER_INFO_FAILED');
3527 ELSE
3528 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3529 p_msg_name => x_err_stage);
3530 END IF;
3531 END IF;
3532 return;
3533 END IF;
3534
3535
3536 --Added for bug 3279981
3537 --If the default top task customer flag is NOT THE SAME AS IN THE SOURCE PROJECT,
3538 --the customer bill split and default top task customer flag values need to be taken care of
3539 --sunkalya:federal changes****** Bug#5511353
3540
3541 IF ('Y' = p_en_top_task_cust_flag AND 'N' = l_orig_en_top_task_cust )
3542 OR ('N' = p_en_top_task_cust_flag AND 'Y' = l_orig_en_top_task_cust )
3543 OR ('N' = p_date_eff_funds_flag AND 'Y' = l_orig_date_eff_funds_flag)
3544 OR ('Y' = p_date_eff_funds_flag AND 'N' = l_orig_date_eff_funds_flag)
3545 THEN
3546
3547 IF p_en_top_task_cust_flag ='Y' OR p_date_eff_funds_flag ='Y' THEN --case #1
3548
3549
3550 l_cur_cust_info.customer_bill_split := NULL;
3551
3552 IF ( 'Y' = p_en_top_task_cust_flag ) THEN
3553
3554 IF l_hghst_ctr_cust_id = l_cur_cust_info.customer_id THEN
3555
3556 l_cur_cust_info.default_top_task_cust_flag := 'Y';
3557
3558 ELSE
3559
3560 l_cur_cust_info.default_top_task_cust_flag := 'N';
3561
3562 END IF;
3563
3564 END IF;
3565
3566 ELSIF p_en_top_task_cust_flag = 'N' AND p_date_eff_funds_flag = 'N' THEN --case #2
3567
3568 IF l_hghst_ctr_cust_id = l_cur_cust_info.customer_id THEN
3569
3570 l_cur_cust_info.customer_bill_split := 100;
3571
3572 ELSE
3573
3574 l_cur_cust_info.customer_bill_split := 0;
3575
3576 END IF;
3577
3578 l_cur_cust_info.default_top_task_cust_flag := 'N';
3579
3580
3581 ELSIF p_en_top_task_cust_flag ='N' OR p_date_eff_funds_flag ='N' THEN --case #3.When one of the flag
3582 --Is Only passed and the other as null.
3583
3584 IF p_en_top_task_cust_flag = 'N' THEN
3585
3586 IF l_orig_date_eff_funds_flag ='Y' THEN
3587
3588 l_cur_cust_info.default_top_task_cust_flag := 'N';
3589
3590 ELSE
3591
3592 IF l_hghst_ctr_cust_id = l_cur_cust_info.customer_id THEN
3593
3594 l_cur_cust_info.customer_bill_split := 100;
3595
3596 ELSE
3597
3598 l_cur_cust_info.customer_bill_split := 0;
3599
3600 END IF;
3601 l_cur_cust_info.default_top_task_cust_flag := 'N';
3602
3603 END IF;
3604
3605 ELSIF p_date_eff_funds_flag ='N' THEN
3606
3607 IF l_orig_en_top_task_cust ='N' THEN
3608
3609 IF l_hghst_ctr_cust_id = l_cur_cust_info.customer_id THEN
3610
3611 l_cur_cust_info.customer_bill_split := 100;
3612
3613 ELSE
3614
3615 l_cur_cust_info.customer_bill_split := 0;
3616
3617 END IF;
3618 l_cur_cust_info.default_top_task_cust_flag := 'N';
3619
3620 END IF;
3621 END IF;
3622
3623 END IF;
3624 END IF;
3625 --sunkalya:federal changes*****
3626
3627
3628
3629 -- insert the project customer record.
3630
3631 l_bill_to_contact_id := null; --Bug#4770535 always pass NULL for bill to and ship to contact ids as
3632 --there are no contacts for the customer and by mistake we should not copy the
3633 --contact returned by get_customer_info aboove as it will return contact at primary site.
3634 l_ship_to_contact_id := null;
3635
3636 pa_customer_info.Create_Customer_Contacts
3637 ( X_Project_Id => x_new_project_id,
3638 X_Customer_Id => l_cur_cust_info.customer_id,
3639 X_Project_Relation_Code => l_cur_cust_info.project_relationship_code,
3640 X_Customer_Bill_Split => l_cur_cust_info.customer_bill_split,
3641 X_Bill_To_Customer_Id => l_cur_cust_info.bill_to_customer_id,
3642 X_Ship_To_Customer_Id => l_cur_cust_info.ship_to_customer_id,
3643 X_Bill_To_Address_Id => l_cur_cust_info.bill_to_address_id,
3644 X_Ship_To_Address_Id => l_cur_cust_info.ship_to_address_id,
3645 X_Bill_To_Contact_Id => l_bill_to_contact_id,
3646 X_Ship_To_Contact_Id => l_ship_to_contact_id,
3647 X_Inv_Currency_Code => l_cur_cust_info.inv_currency_code,
3648 X_Inv_Rate_Type => l_cur_cust_info.inv_rate_type,
3649 X_Inv_Rate_Date => l_cur_cust_info.inv_rate_date,
3650 X_Inv_Exchange_Rate => l_cur_cust_info.inv_exchange_rate,
3651 X_Allow_Inv_Rate_Type_Fg => l_cur_cust_info.allow_inv_user_rate_type_flag,
3652 X_Bill_Another_Project_Fg => l_cur_cust_info.bill_another_project_flag,
3653 X_Receiver_Task_Id => l_cur_cust_info.receiver_task_id,
3654 P_default_top_task_customer => l_cur_cust_info.default_top_task_cust_flag,
3655 X_User => FND_GLOBAL.USER_ID,
3656 X_Login => FND_GLOBAL.LOGIN_ID,
3657 X_Err_Code => x_err_code,
3658 X_Err_Stage => x_err_stage,
3659 X_Err_Stack => x_err_stack
3660 );
3661 IF x_err_code > 0
3662 THEN
3663 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
3664 THEN
3665 IF NOT pa_project_pvt.check_valid_message(x_err_stage)
3666 THEN
3667 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3668 p_msg_name => 'PA_PROJ_CR_CONTACTS_FAILED');
3669 ELSE
3670 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3671 p_msg_name => x_err_stage);
3672 END IF;
3673 rollback to copy_project;
3674 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
3675 return;
3676 END IF;
3677
3678 ELSIF x_err_code < 0
3679 THEN
3680 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3681 p_msg_name => 'PA_PROJ_CR_CONTACTS_FAILED');
3682 rollback to copy_project;
3683 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
3684 return;
3685 END IF;
3686 /** End of code change for Bug 3110489 **/
3687
3688 END IF; -- if is_contact_present_flag = 'N'
3689 END Loop;
3690 CLOSE cur_cust_info; --Bug 3905797
3691 END;
3692
3693 -- anlee org role changes
3694 -- create a project party if the added customer is an organization
3695 DECLARE
3696 l_party_id NUMBER;
3697 l_project_party_id NUMBER;
3698 l_resource_id NUMBER;
3699 l_wf_item_type VARCHAR2(30);
3700 l_wf_type VARCHAR2(30);
3701 l_wf_party_process VARCHAR2(30);
3702 l_assignment_id NUMBER;
3703 l_return_status VARCHAR2(1);
3704 l_msg_data VARCHAR2(2000);
3705 l_msg_count NUMBER;
3706 l_end_date_active DATE;
3707 l_customer_id NUMBER;
3708
3709 CURSOR l_customers_csr (c_project_id IN NUMBER) IS
3710 SELECT customer_id
3711 FROM PA_PROJECT_CUSTOMERS
3712 WHERE project_id = c_project_id
3713 AND not exists
3714 (select null
3715 from pa_project_copy_overrides
3716 where project_id = x_created_from_proj_id
3717 and field_name = 'CUSTOMER_NAME');
3718
3719 CURSOR l_check_org_csr (c_customer_id IN NUMBER) IS
3720 SELECT PARTY_ID
3721 FROM PA_CUSTOMERS_V
3722 WHERE CUSTOMER_ID = c_customer_id
3723 AND PARTY_TYPE = 'ORGANIZATION';
3724 BEGIN
3725 -- Bug 2984536. The customer records are created only if x_customer_id is null. Dont do processing
3726 -- for project parties if x_customer_id is not null. Included this condition as the cursor doesnot take
3727 -- care of this.
3728 If x_customer_id is null THEN
3729 OPEN l_customers_csr(x_orig_project_id);
3730 LOOP
3731 FETCH l_customers_csr INTO l_customer_id;
3732 EXIT WHEN l_customers_csr%NOTFOUND;
3733
3734 l_party_id := null;
3735 l_project_party_id := null;
3736 OPEN l_check_org_csr(l_customer_id);
3737 FETCH l_check_org_csr INTO l_party_id;
3738 IF l_check_org_csr%NOTFOUND then
3739 l_party_id := null;
3740 END IF;
3741 CLOSE l_check_org_csr;
3742
3743 if l_party_id is not null then
3744
3745 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(
3746 p_validate_only => FND_API.G_FALSE
3747 , p_object_id => x_new_project_id
3748 , p_OBJECT_TYPE => 'PA_PROJECTS'
3749 , p_project_role_id => 100
3750 , p_project_role_type => 'CUSTOMER_ORG'
3751 , p_RESOURCE_TYPE_ID => 112
3752 , p_resource_source_id => l_party_id
3753 , p_start_date_active => null
3754 , p_calling_module => 'FORM'
3755 , p_project_id => x_new_project_id
3756 , p_project_end_date => null
3757 , p_end_date_active => l_end_date_active
3758 , x_project_party_id => l_project_party_id
3759 , x_resource_id => l_resource_id
3760 , x_wf_item_type => l_wf_item_type
3761 , x_wf_type => l_wf_type
3762 , x_wf_process => l_wf_party_process
3763 , x_assignment_id => l_assignment_id
3764 , x_return_status => l_return_status
3765 , x_msg_count => l_msg_count
3766 , x_msg_data => l_msg_data );
3767
3768 IF (l_return_status <> 'S') Then
3769
3770 rollback to copy_project;
3771 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
3772 x_err_code := 168;
3773 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_CR_PROJ_PARTY_PUB');
3774 x_err_stack := x_err_stack||'->PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY';
3775 return;
3776 END IF;
3777
3778 -- Add the new project party ID to the customers row
3779 UPDATE PA_PROJECT_CUSTOMERS
3780 SET project_party_id = l_project_party_id
3781 WHERE project_id = x_new_project_id
3782 AND customer_id = l_customer_id;
3783 end if;
3784 end loop;
3785 CLOSE l_customers_csr; --Bug 3905797
3786 End if; -- -- Bug 2984536.
3787
3788 EXCEPTION WHEN OTHERS THEN
3789 x_err_code := 168;
3790 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
3791 -- IF x_err_stage IS NULL
3792 -- THEN
3793 x_err_stage := 'API: '||'PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY'||' SQL error message: '||SUBSTR( SQLERRM,1,1900);
3794 -- END IF;
3795 rollback to copy_project;
3796 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
3797 return;
3798 END;
3799
3800 end if;
3801
3802 if ( x_use_override_flag = 'N' ) then
3803
3804 x_err_stage := 'copying all billing contacts ';
3805
3806 INSERT INTO pa_project_contacts (
3807 project_id
3808 , customer_id
3809 , contact_id
3810 , project_contact_type_code
3811 , last_update_date
3812 , last_updated_by
3813 , creation_date
3814 , created_by
3815 , last_update_login
3816 , record_version_number
3817 -- Customer Account relationships
3818 , bill_ship_customer_id)
3819 SELECT
3820 x_new_project_id
3821 , c.customer_id
3822 , c.contact_id
3823 , c.project_contact_type_code
3824 , sysdate
3825 , FND_GLOBAL.USER_ID
3826 , sysdate
3827 , FND_GLOBAL.USER_ID
3828 , FND_GLOBAL.LOGIN_ID
3829 , 1
3830 -- Customer Account relationships
3831 , c.bill_ship_customer_id
3832 FROM
3833 pa_project_contacts c
3834 WHERE c.project_id = x_orig_project_id
3835 and c.customer_id in
3836 (select customer_id from pa_project_customers
3837 where project_id = x_new_project_id);
3838 else
3839
3840 x_err_stage := 'copying billing contacts using overrides';
3841 -- Bug 2984536 - Commenting the following piece of code. The billing contacts are
3842 -- copied along with the creation of the project customers. This is commented here
3843 -- so as not to copy wrong contact details, if the bill to customer gets changed due to the
3844 -- relationship code.
3845 /* INSERT INTO pa_project_contacts (
3846 project_id
3847 , customer_id
3848 , contact_id
3849 , project_contact_type_code
3850 , last_update_date
3851 , last_updated_by
3852 , creation_date
3853 , created_by
3854 , last_update_login
3855 , record_version_number
3856 -- Customer Account relationships
3857 , bill_ship_customer_id)
3858
3859 SELECT
3860 x_new_project_id
3861 , c.customer_id
3862 , c.contact_id
3863 , c.project_contact_type_code
3864 , sysdate
3865 , FND_GLOBAL.USER_ID
3866 , sysdate
3867 , FND_GLOBAL.USER_ID
3868 , FND_GLOBAL.LOGIN_ID
3869 , 1
3870 -- Customer Account relationships
3871 , c.bill_ship_customer_id
3872
3873 FROM
3874 pa_project_customers cust,
3875 pa_project_contacts c
3876 WHERE c.project_id = x_orig_project_id
3877 and c.customer_id in
3878 (select customer_id from pa_project_customers
3879 where project_id = x_new_project_id)
3880 and c.project_id = cust.project_id
3881 and c.customer_id = cust.customer_id
3882 and not exists
3883 (select null
3884 from pa_project_copy_overrides
3885 where project_id = x_created_from_proj_id
3886 and field_name = 'CUSTOMER_NAME');
3887 */
3888 end if;
3889
3890
3891 x_err_stage := 'copying cost distribution override ';
3892
3893 --Below code added for selective copy project. Tracking Bug No. 3464332
3894 OPEN cur_get_flag('FN_COST_BILL_OVERRIDES_FLAG');
3895 FETCH cur_get_flag INTO l_fn_cb_overrides_flag;
3896 CLOSE cur_get_flag;
3897
3898 IF 'Y' = l_fn_cb_overrides_flag THEN
3899 INSERT INTO pa_cost_dist_overrides (
3900 COST_DISTRIBUTION_OVERRIDE_ID
3901 , project_id
3902 , OVERRIDE_TO_ORGANIZATION_ID
3903 , start_date_active
3904 , last_update_date
3905 , last_updated_by
3906 , creation_date
3907 , created_by
3908 , last_update_login
3909 , person_id
3910 , EXPENDITURE_CATEGORY
3911 , OVERRIDE_FROM_ORGANIZATION_ID
3912 , END_DATE_ACTIVE)
3913 SELECT
3914 pa_cost_dist_overrides_s.nextval
3915 , x_new_project_id
3916 , OVERRIDE_TO_ORGANIZATION_ID
3917 , decode(x_delta, null, x_start_date,
3918 start_date_active + x_delta)
3919 , sysdate
3920 , FND_GLOBAL.USER_ID
3921 , sysdate
3922 , FND_GLOBAL.USER_ID
3923 , FND_GLOBAL.LOGIN_ID
3924 , person_id
3925 , EXPENDITURE_CATEGORY
3926 , OVERRIDE_FROM_ORGANIZATION_ID
3927 , decode(x_delta, null, x_completion_date,
3928 end_date_active + x_delta)
3929 FROM
3930 pa_cost_dist_overrides
3931 WHERE project_id = x_orig_project_id;
3932 END IF;
3933
3934 x_err_stage := 'copying credit receivers ';
3935
3936 INSERT INTO pa_credit_receivers (
3937 PERSON_ID
3938 , CREDIT_TYPE_CODE
3939 , project_id
3940 , last_update_date
3941 , last_updated_by
3942 , creation_date
3943 , created_by
3944 , last_update_login
3945 , START_DATE_ACTIVE
3946 , CREDIT_PERCENTAGE
3947 , END_DATE_ACTIVE
3948 , TRANSFER_TO_AR_FLAG
3949 , CREDIT_RECEIVER_ID
3950 , RECORD_VERSION_NUMBER
3951 , SALESREP_ID)
3952 SELECT
3953 PERSON_ID
3954 , CREDIT_TYPE_CODE
3955 , x_new_project_id
3956 , sysdate
3957 , FND_GLOBAL.USER_ID
3958 , sysdate
3959 , FND_GLOBAL.USER_ID
3960 , FND_GLOBAL.LOGIN_ID
3961 , decode(x_delta, null, x_start_date,
3962 start_date_active + x_delta)
3963 , CREDIT_PERCENTAGE
3964 , decode(x_delta, null, x_completion_date,
3965 end_date_active + x_delta)
3966 , TRANSFER_TO_AR_FLAG
3967 , pa_credit_receivers_s.NEXTVAL
3968 , RECORD_VERSION_NUMBER
3969 , SALESREP_ID
3970 FROM
3971 pa_credit_receivers
3972 WHERE project_id = x_orig_project_id
3973 and task_id is null;
3974
3975
3976 -- copy project level billing assignment
3977 x_err_stage := 'copying project billing assignment ';
3978
3979 INSERT INTO pa_billing_assignments (
3980 BILLING_ASSIGNMENT_ID,
3981 BILLING_EXTENSION_ID,
3982 PROJECT_TYPE,
3983 PROJECT_ID,
3984 TOP_TASK_ID,
3985 AMOUNT,
3986 PERCENTAGE,
3987 ACTIVE_FLAG,
3988 CREATION_DATE,
3989 CREATED_BY,
3990 LAST_UPDATE_DATE,
3991 LAST_UPDATED_BY,
3992 LAST_UPDATE_LOGIN,
3993 ATTRIBUTE_CATEGORY,
3994 ATTRIBUTE1,
3995 ATTRIBUTE2,
3996 ATTRIBUTE3,
3997 ATTRIBUTE4,
3998 ATTRIBUTE5,
3999 ATTRIBUTE6,
4000 ATTRIBUTE7,
4001 ATTRIBUTE8,
4002 ATTRIBUTE9,
4003 ATTRIBUTE10,
4004 ATTRIBUTE11,
4005 ATTRIBUTE12,
4006 ATTRIBUTE13,
4007 ATTRIBUTE14,
4008 ATTRIBUTE15,
4009 RECORD_VERSION_NUMBER,
4010 DISTRIBUTION_RULE,
4011 /* Added columns for bug#2658340 */
4012 ORG_ID,
4013 RATE_OVERRIDE_CURRENCY_CODE,
4014 PROJECT_CURRENCY_CODE,
4015 PROJECT_RATE_TYPE,
4016 PROJECT_RATE_DATE,
4017 PROJECT_EXCHANGE_RATE,
4018 PROJFUNC_CURRENCY_CODE,
4019 PROJFUNC_RATE_TYPE,
4020 PROJFUNC_RATE_DATE,
4021 PROJFUNC_EXCHANGE_RATE,
4022 FUNDING_RATE_TYPE,
4023 FUNDING_RATE_DATE,
4024 FUNDING_EXCHANGE_RATE)
4025 select
4026 pa_billing_assignments_s.nextval,
4027 BILLING_EXTENSION_ID,
4028 project_type,
4029 x_new_project_id,
4030 null,
4031 AMOUNT,
4032 PERCENTAGE,
4033 ACTIVE_FLAG,
4034 sysdate,
4035 FND_GLOBAL.USER_ID,
4036 sysdate,
4037 FND_GLOBAL.USER_ID,
4038 FND_GLOBAL.LOGIN_ID,
4039 ATTRIBUTE_CATEGORY,
4040 ATTRIBUTE1,
4041 ATTRIBUTE2,
4042 ATTRIBUTE3,
4043 ATTRIBUTE4,
4044 ATTRIBUTE5,
4045 ATTRIBUTE6,
4046 ATTRIBUTE7,
4047 ATTRIBUTE8,
4048 ATTRIBUTE9,
4049 ATTRIBUTE10,
4050 ATTRIBUTE11,
4051 ATTRIBUTE12,
4052 ATTRIBUTE13,
4053 ATTRIBUTE14,
4054 ATTRIBUTE15,
4055 RECORD_VERSION_NUMBER,
4056 null, /* Bug#2663786 - Distribution should be inserted as null, commented line below.
4057 nvl(x_DISTRIBUTION_RULE, DISTRIBUTION_RULE), */
4058 /* Added columns for bug#2658340 */
4059 ORG_ID,
4060 RATE_OVERRIDE_CURRENCY_CODE,
4061 PROJECT_CURRENCY_CODE,
4062 PROJECT_RATE_TYPE,
4063 PROJECT_RATE_DATE,
4064 PROJECT_EXCHANGE_RATE,
4065 PROJFUNC_CURRENCY_CODE,
4066 PROJFUNC_RATE_TYPE,
4067 PROJFUNC_RATE_DATE,
4068 PROJFUNC_EXCHANGE_RATE,
4069 FUNDING_RATE_TYPE,
4070 FUNDING_RATE_DATE,
4071 FUNDING_EXCHANGE_RATE
4072 from pa_billing_assignments
4073 where project_id = x_orig_project_id
4074 and top_task_id is null;
4075
4076
4077 -- copying burden schedule for project:
4078
4079 x_err_stage := 'copying project level burden schedules ';
4080
4081 INSERT INTO pa_ind_rate_schedules (
4082 IND_RATE_SCH_ID,
4083 IND_RATE_SCH_NAME,
4084 BUSINESS_GROUP_ID,
4085 DESCRIPTION,
4086 START_DATE_ACTIVE,
4087 END_DATE_ACTIVE,
4088 LAST_UPDATE_DATE,
4089 LAST_UPDATED_BY,
4090 CREATED_BY,
4091 CREATION_DATE,
4092 LAST_UPDATE_LOGIN,
4093 COST_PLUS_STRUCTURE,
4094 IND_RATE_SCHEDULE_TYPE,
4095 PROJECT_ID,
4096 TASK_ID,
4097 COST_OVR_SCH_FLAG,
4098 REV_OVR_SCH_FLAG,
4099 INV_OVR_SCH_FLAG,
4100 ORGANIZATION_STRUCTURE_ID,
4101 ORG_STRUCTURE_VERSION_ID,
4102 START_ORGANIZATION_ID, --Added these three columns for bug 2581491
4103 IND_RATE_SCH_USAGE --bug 3053508
4104 )
4105 select
4106 pa_ind_rate_schedules_s.nextval,
4107 -- to_char(x_new_project_id) ||
4108 -- substr(s.ind_rate_sch_name,
4109 -- instr(s.ind_rate_sch_name, '-', -1)),
4110 SUBSTR((TO_CHAR(x_new_project_id) ||
4111 DECODE(INSTR(s.ind_rate_sch_name, '-', -1),'0','-') ||
4112 SUBSTR(s.ind_rate_sch_name,
4113 INSTR(s.ind_rate_sch_name, '-', -1))),1,30), -- Added for bug 3911182.
4114 s.business_group_id,
4115 s.DESCRIPTION,
4116 decode(x_delta, null, x_start_date,
4117 s.start_date_active + x_delta),
4118 decode(x_delta, null, x_completion_date,
4119 s.end_date_active + x_delta),
4120 sysdate,
4121 FND_GLOBAL.USER_ID,
4122 FND_GLOBAL.USER_ID,
4123 sysdate,
4124 FND_GLOBAL.LOGIN_ID,
4125 s.COST_PLUS_STRUCTURE,
4126 s.IND_RATE_SCHEDULE_TYPE,
4127 x_new_project_id,
4128 null,
4129 s.COST_OVR_SCH_FLAG,
4130 s.REV_OVR_SCH_FLAG,
4131 s.INV_OVR_SCH_FLAG,
4132 s.ORGANIZATION_STRUCTURE_ID,
4133 s.ORG_STRUCTURE_VERSION_ID,
4134 s.START_ORGANIZATION_ID , --Added these three columns for bug 2581491
4135 s.IND_RATE_SCH_USAGE --bug 3053508
4136 FROM
4137 pa_ind_rate_schedules s
4138 WHERE s.project_id = x_orig_project_id
4139 and s.task_id is null;
4140
4141
4142 x_err_stage := 'copying burden schedule revisions ';
4143
4144 insert into pa_ind_rate_sch_revisions (
4145 IND_RATE_SCH_REVISION_ID,
4146 IND_RATE_SCH_ID,
4147 IND_RATE_SCH_REVISION,
4148 IND_RATE_SCH_REVISION_TYPE,
4149 COMPILED_FLAG,
4150 COST_PLUS_STRUCTURE,
4151 START_DATE_ACTIVE,
4152 END_DATE_ACTIVE,
4153 COMPILED_DATE,
4154 LAST_UPDATE_DATE,
4155 LAST_UPDATED_BY,
4156 CREATED_BY,
4157 CREATION_DATE,
4158 LAST_UPDATE_LOGIN,
4159 REQUEST_ID,
4160 PROGRAM_APPLICATION_ID,
4161 PROGRAM_ID,
4162 PROGRAM_UPDATE_DATE,
4163 READY_TO_COMPILE_FLAG,
4164 ACTUAL_SCH_REVISION_ID,
4165 ORGANIZATION_STRUCTURE_ID,
4166 ORG_STRUCTURE_VERSION_ID,
4167 START_ORGANIZATION_ID) --Added these three columns for bug 2581491
4168 select
4169 pa_ind_rate_sch_revisions_s.nextval,
4170 new_sch.ind_rate_sch_id,
4171 rev.IND_RATE_SCH_REVISION,
4172 rev.IND_RATE_SCH_REVISION_TYPE,
4173 'N',
4174 rev.COST_PLUS_STRUCTURE,
4175 decode(x_delta, null, x_start_date,
4176 rev.start_date_active + x_delta),
4177 decode(x_delta, null, x_completion_date,
4178 rev.end_date_active + x_delta),
4179 null,
4180 sysdate,
4181 FND_GLOBAL.USER_ID,
4182 FND_GLOBAL.USER_ID,
4183 sysdate,
4184 FND_GLOBAL.LOGIN_ID,
4185 rev.REQUEST_ID,
4186 NULL,
4187 NULL,
4188 NULL,
4189 'Y',
4190 NULL,
4191 rev.ORGANIZATION_STRUCTURE_ID,
4192 rev.ORG_STRUCTURE_VERSION_ID,
4193 rev.START_ORGANIZATION_ID --Added these three columns for bug 2581491
4194 from pa_ind_rate_sch_revisions rev,
4195 pa_ind_rate_schedules old_sch,
4196 pa_ind_rate_schedules new_sch
4197 where old_sch.project_id = x_orig_project_id
4198 and old_sch.ind_rate_sch_id = rev.IND_RATE_SCH_ID
4199 and old_sch.task_id is null
4200 and new_sch.project_id = x_new_project_id
4201 and new_sch.task_id is null
4202 and substr(new_sch.ind_rate_sch_name, -- added for bug 4213251
4203 decode(instr(new_sch.ind_rate_sch_name, '-', -1), 0 , 0,
4204 instr(new_sch.ind_rate_sch_name, '-', -1)+1))
4205 = substr(old_sch.ind_rate_sch_name,
4206 decode(instr(old_sch.ind_rate_sch_name, '-', -1), 0 , 0,
4207 instr(old_sch.ind_rate_sch_name, '-', -1)+1));
4208
4209 /* Commented the following code for bug 4213251
4210 and substr(new_sch.ind_rate_sch_name,
4211 instr(new_sch.ind_rate_sch_name, '-', -1))
4212 = substr(old_sch.ind_rate_sch_name,
4213 instr(old_sch.ind_rate_sch_name, '-', -1)); */
4214
4215 insert into pa_ind_cost_multipliers (
4216 IND_RATE_SCH_REVISION_ID,
4217 ORGANIZATION_ID,
4218 IND_COST_CODE,
4219 MULTIPLIER,
4220 LAST_UPDATE_DATE,
4221 LAST_UPDATED_BY,
4222 CREATED_BY,
4223 CREATION_DATE,
4224 LAST_UPDATE_LOGIN )
4225 select
4226 new_rev.IND_RATE_SCH_REVISION_ID,
4227 mult.ORGANIZATION_ID,
4228 mult.IND_COST_CODE,
4229 mult.MULTIPLIER,
4230 sysdate,
4231 FND_GLOBAL.USER_ID,
4232 FND_GLOBAL.USER_ID,
4233 sysdate,
4234 FND_GLOBAL.LOGIN_ID
4235 from pa_ind_cost_multipliers mult,
4236 pa_ind_rate_sch_revisions old_rev,
4237 pa_ind_rate_sch_revisions new_rev,
4238 pa_ind_rate_schedules old_sch,
4239 pa_ind_rate_schedules new_sch
4240 where old_rev.IND_RATE_SCH_REVISION_ID =
4241 mult.IND_RATE_SCH_REVISION_ID
4242 and old_rev.IND_RATE_SCH_REVISION =
4243 new_rev.IND_RATE_SCH_REVISION
4244 and old_sch.ind_rate_sch_id = old_rev.IND_RATE_SCH_ID
4245 and new_sch.ind_rate_sch_id = new_rev.IND_RATE_SCH_ID
4246 and old_sch.project_id = x_orig_project_id
4247 and old_sch.task_id is null
4248 and new_sch.project_id = x_new_project_id
4249 and new_sch.task_id is null
4250 and substr(new_sch.ind_rate_sch_name, -- added for bug 4213251
4251 decode(instr(new_sch.ind_rate_sch_name, '-', -1), 0 , 0,
4252 instr(new_sch.ind_rate_sch_name, '-', -1)+1))
4253 = substr(old_sch.ind_rate_sch_name,
4254 decode(instr(old_sch.ind_rate_sch_name, '-', -1), 0 , 0,
4255 instr(old_sch.ind_rate_sch_name, '-', -1)+1));
4256
4257 /* Commented the following code for bug 4213251
4258 and substr(new_sch.ind_rate_sch_name,
4259 instr(new_sch.ind_rate_sch_name, '-', -1))
4260 = substr(old_sch.ind_rate_sch_name,
4261 instr(old_sch.ind_rate_sch_name, '-', -1)); */
4262
4263
4264 x_err_stage := 'copying project level transaction control';
4265 --Below code added for selective copy project. Tracking Bug No. 3464332
4266 --Check whether the Transaction Controls flag is checked or not
4267 OPEN cur_get_flag('FN_TXN_CONTROL_FLAG');
4268 FETCH cur_get_flag INTO l_fin_txn_control_flag;
4269 CLOSE cur_get_flag;
4270
4271 IF 'Y' = l_fin_txn_control_flag THEN
4272 INSERT INTO pa_transaction_controls (
4273 project_id
4274 , start_date_active
4275 , chargeable_flag
4276 , billable_indicator
4277 , creation_date
4278 , created_by
4279 , last_update_date
4280 , last_updated_by
4281 , last_update_login
4282 , person_id
4283 , expenditure_category
4284 , expenditure_type
4285 , non_labor_resource
4286 , scheduled_exp_only
4287 , end_date_active
4288 /*Added for FPM Changes for Project Setup */
4289 , workplan_res_only_flag
4290 , employees_only_flag)
4291 SELECT
4292 x_new_project_id
4293 , decode(x_delta, null, x_start_date,
4294 tc.start_date_active + x_delta)
4295 , tc.chargeable_flag
4296 , tc.billable_indicator
4297 , sysdate
4298 , FND_GLOBAL.USER_ID
4299 , sysdate
4300 , FND_GLOBAL.USER_ID
4301 , FND_GLOBAL.LOGIN_ID
4302 , tc.person_id
4303 , tc.expenditure_category
4304 , tc.expenditure_type
4305 , tc.non_labor_resource
4306 , tc.scheduled_exp_only
4307 , decode(x_delta, null, x_completion_date,
4308 tc.end_date_active + x_delta)
4309 /*Added for FPM Changes for Project Setup */
4310 , tc.workplan_res_only_flag
4311 , tc.employees_only_flag
4312 FROM
4313 pa_transaction_controls tc
4314 WHERE
4315 tc.project_id = x_orig_project_id
4316 AND tc.task_id IS NULL;
4317 END IF;--IF 'Y' = l_fin_txn_control_flag
4318
4319 x_err_stage := 'copying project assets';
4320
4321 --Below code added for selective copy project. Tracking Bug No. 3464332
4322 OPEN cur_get_flag('FN_ASSETS_FLAG');
4323 FETCH cur_get_flag INTO l_fn_assets_flag;
4324 CLOSE cur_get_flag;
4325
4326 IF 'Y' = l_fn_assets_flag THEN
4327 INSERT INTO pa_project_assets (
4328 PROJECT_ASSET_ID,
4329 PROJECT_ID,
4330 ASSET_NUMBER,
4331 ASSET_NAME,
4332 ASSET_DESCRIPTION,
4333 LOCATION_ID,
4334 ASSIGNED_TO_PERSON_ID,
4335 DATE_PLACED_IN_SERVICE,
4336 ASSET_CATEGORY_ID,
4337 ASSET_KEY_CCID,
4338 BOOK_TYPE_CODE,
4339 ASSET_UNITS,
4340 DEPRECIATE_FLAG,
4341 DEPRECIATION_EXPENSE_CCID,
4342 CAPITALIZED_FLAG,
4343 ESTIMATED_IN_SERVICE_DATE,
4344 CAPITALIZED_COST,
4345 GROUPED_CIP_COST,
4346 AMORTIZE_FLAG,
4347 COST_ADJUSTMENT_FLAG,
4348 CAPITALIZED_DATE,
4349 REVERSE_FLAG,
4350 REVERSAL_DATE,
4351 NEW_MASTER_FLAG,
4352 CREATION_DATE,
4353 CREATED_BY,
4354 LAST_UPDATE_DATE,
4355 LAST_UPDATED_BY,
4356 LAST_UPDATE_LOGIN,
4357 ATTRIBUTE_CATEGORY,
4358 ATTRIBUTE1,
4359 ATTRIBUTE2,
4360 ATTRIBUTE3,
4361 ATTRIBUTE4,
4362 ATTRIBUTE5,
4363 ATTRIBUTE6,
4364 ATTRIBUTE7,
4365 ATTRIBUTE8,
4366 ATTRIBUTE9,
4367 ATTRIBUTE10,
4368 ATTRIBUTE11,
4369 ATTRIBUTE12,
4370 ATTRIBUTE13,
4371 ATTRIBUTE14,
4372 ATTRIBUTE15,
4373 --PA L Changes 2872708
4374 --CAPITAL_EVENT_ID, --do not copy capital event bug 2946015
4375 --FA_PERIOD_NAME,
4376 --PM_PRODUCT_CODE,
4377 --PM_ASSET_REFERENCE,
4378 ESTIMATED_COST,
4379 ESTIMATED_ASSET_UNITS,
4380 MANUFACTURER_NAME,
4381 MODEL_NUMBER,
4382 --TAG_NUMBER,
4383 --SERIAL_NUMBER,
4384 RET_TARGET_ASSET_ID,
4385 PROJECT_ASSET_TYPE,
4386 PARENT_ASSET_ID,
4387 --FA_ASSET_ID,
4388 CAPITAL_HOLD_FLAG,
4389 --end PA L Changes 2872708
4390 ORG_ID --R12: MOAC changes: Bug 4363092
4391 )
4392 select
4393 pa_project_assets_s.nextval,
4394 x_new_PROJECT_ID,
4395 NULL,
4396 ASSET_NAME,
4397 ASSET_DESCRIPTION,
4398 decode(x_orig_template_flag, 'Y', LOCATION_ID, NULL), -- NULL, bug 3433295
4399 NULL,
4400 NULL,
4401 ASSET_CATEGORY_ID,
4402 ASSET_KEY_CCID,
4403 BOOK_TYPE_CODE,
4404 ASSET_UNITS,
4405 DEPRECIATE_FLAG,
4406 DEPRECIATION_EXPENSE_CCID,
4407 'N',
4408 decode(ESTIMATED_IN_SERVICE_DATE, null, null,
4409 decode(x_delta, null, x_start_date,
4410 ESTIMATED_IN_SERVICE_DATE + x_delta)),
4411 0,
4412 0,
4413 AMORTIZE_FLAG,
4414 'N',
4415 NULL,
4416 'N',
4417 NULL,
4418 'N',
4419 sysdate,
4420 FND_GLOBAL.USER_ID,
4421 sysdate,
4422 FND_GLOBAL.USER_ID,
4423 FND_GLOBAL.LOGIN_ID,
4424 ATTRIBUTE_CATEGORY,
4425 ATTRIBUTE1,
4426 ATTRIBUTE2,
4427 ATTRIBUTE3,
4428 ATTRIBUTE4,
4429 ATTRIBUTE5,
4430 ATTRIBUTE6,
4431 ATTRIBUTE7,
4432 ATTRIBUTE8,
4433 ATTRIBUTE9,
4434 ATTRIBUTE10,
4435 ATTRIBUTE11,
4436 ATTRIBUTE12,
4437 ATTRIBUTE13,
4438 ATTRIBUTE14,
4439 ATTRIBUTE15,
4440 --PA L chanegs 2872708
4441 --CAPITAL_EVENT_ID, --do not copy capital event bug 2946015
4442 --FA_PERIOD_NAME, --do not copy. please refer bug 2948307
4443 --PM_PRODUCT_CODE,
4444 --PM_ASSET_REFERENCE,
4445 ESTIMATED_COST,
4446 ESTIMATED_ASSET_UNITS,
4447 MANUFACTURER_NAME,
4448 MODEL_NUMBER,
4449 --TAG_NUMBER,
4450 --SERIAL_NUMBER,
4451 RET_TARGET_ASSET_ID,
4452 DECODE( PROJECT_ASSET_TYPE, 'AS-BUILT', 'ESTIMATED', PROJECT_ASSET_TYPE ),
4453 --bug 2872708 refer *** MAANSARI 04/17/03 11:19 am ***
4454 PARENT_ASSET_ID,
4455 --FA_ASSET_ID,
4456 decode( x_orig_template_flag, 'Y', CAPITAL_HOLD_FLAG, 'N', 'N' ),
4457 --end PA L chanegs 2872708
4458 org_id --R12: MOAC changes: Bug 4363092
4459 from pa_project_assets
4460 where project_id = x_orig_project_id;
4461 END IF;--IF 'Y' = l_fn_assets_flag THEN
4462
4463 -- This block copies the attachments for a
4464 -- perticular asset
4465
4466 Begin
4467 Declare
4468 cursor c_attach_assets is
4469 select orig.project_asset_id orig_project_asset_id,
4470 new.project_asset_id new_project_asset_id
4471 from pa_project_assets orig, pa_project_assets new
4472 where orig.project_id = x_orig_project_id
4473 and new.asset_name = orig.asset_name
4474 and new.project_id = x_new_project_id ;
4475
4476 c_atch c_attach_assets%rowtype ;
4477
4478 begin
4479 open c_attach_assets;
4480 loop
4481 fetch c_attach_assets
4482 into c_atch ;
4483 if c_attach_assets%notfound then
4484 exit ;
4485 end if;
4486 fnd_attached_documents2_pkg.copy_attachments
4487 ('PA_PROJECT_ASSETS',
4488 c_atch.orig_project_asset_id,
4489 null, null, null, null,
4490 'PA_PROJECT_ASSETS',
4491 c_atch.new_project_asset_id,
4492 null, null, null, null,
4493 FND_GLOBAL.USER_ID,
4494 FND_GLOBAL.LOGIN_ID,
4495 275, null, null);
4496
4497 end loop ;
4498 close c_attach_assets;
4499 exception
4500 when NO_DATA_FOUND then
4501 null;
4502 when others then
4503 null ;
4504 end ;
4505 end ;
4506
4507 -- End copy attachments
4508
4509 x_err_stage := 'copying project level project asset assignment';
4510 --Below code added for selective copy project. Tracking Bug No. 3464332
4511 OPEN cur_get_flag('FN_ASSET_ASSIGNMENTS_FLAG');
4512 FETCH cur_get_flag INTO l_fn_asset_assignments_flag;
4513 CLOSE cur_get_flag;
4514
4515 IF 'Y' = l_fn_asset_assignments_flag THEN
4516 INSERT INTO pa_project_asset_assignments (
4517 PROJECT_ASSET_ID,
4518 TASK_ID,
4519 PROJECT_ID,
4520 CREATION_DATE,
4521 CREATED_BY,
4522 LAST_UPDATE_DATE,
4523 LAST_UPDATED_BY,
4524 LAST_UPDATE_LOGIN)
4525 -- changed to remove bug#604496 :ashia bagai 30-dec-97
4526 -- added UNION to remove bug#604496 : ashia bagai 30-dec-97
4527 -- Common Cost asset assignments would have an asset id = 0
4528 -- and hence would not have a relevant record in pa_project_assets
4529 /* select
4530 new_asset.PROJECT_ASSET_ID,
4531 0,
4532 new_asset.PROJECT_ID,
4533 sysdate,
4534 FND_GLOBAL.USER_ID,
4535 sysdate,
4536 FND_GLOBAL.USER_ID,
4537 FND_GLOBAL.LOGIN_ID
4538 from pa_project_asset_assignments assign,
4539 pa_project_assets old_asset,
4540 pa_project_assets new_asset
4541 where old_asset.project_id = x_orig_project_id
4542 and old_asset.project_asset_id =
4543 assign.project_asset_id
4544 and assign.task_id = 0
4545 and old_asset.asset_name = new_asset.asset_name
4546 and new_asset.project_id = x_new_project_id;
4547 */
4548 select
4549 new_asset.PROJECT_ASSET_ID,
4550 0,
4551 new_asset.PROJECT_ID,
4552 sysdate,
4553 FND_GLOBAL.USER_ID,
4554 sysdate,
4555 FND_GLOBAL.USER_ID,
4556 FND_GLOBAL.LOGIN_ID
4557 from pa_project_asset_assignments assign,
4558 pa_project_assets old_asset,
4559 pa_project_assets new_asset
4560 where old_asset.project_id = x_orig_project_id
4561 and old_asset.project_asset_id =
4562 assign.project_asset_id
4563 and assign.task_id = 0
4564 and old_asset.asset_name = new_asset.asset_name
4565 and new_asset.project_id = x_new_project_id
4566 UNION
4567 select
4568 PROJECT_ASSET_ID,
4569 0,
4570 x_new_project_id,
4571 sysdate,
4572 FND_GLOBAL.USER_ID,
4573 sysdate,
4574 FND_GLOBAL.USER_ID,
4575 FND_GLOBAL.LOGIN_ID
4576 from pa_project_asset_assignments
4577 where project_id = x_orig_project_id
4578 and task_id = 0
4579 and project_asset_id = 0;
4580 END IF;--IF 'Y' = l_fn_asset_assignments_flag THEN
4581 --end of change for bug#604496
4582
4583 -- end of copy project asset assignments
4584 x_err_stage := 'copying project resource list assignments';
4585
4586 INSERT INTO pa_resource_list_assignments (
4587 RESOURCE_LIST_ASSIGNMENT_ID,
4588 RESOURCE_LIST_ID,
4589 PROJECT_ID,
4590 RESOURCE_LIST_CHANGED_FLAG,
4591 LAST_UPDATED_BY,
4592 LAST_UPDATE_DATE,
4593 CREATION_DATE,
4594 CREATED_BY,
4595 LAST_UPDATE_LOGIN )
4596 select
4597 pa_resource_list_assignments_s.nextval,
4598 RESOURCE_LIST_ID,
4599 x_new_project_id,
4600 'N',
4601 FND_GLOBAL.USER_ID,
4602 sysdate,
4603 sysdate,
4604 FND_GLOBAL.USER_ID,
4605 FND_GLOBAL.LOGIN_ID
4606 from pa_resource_list_assignments a,
4607 pa_resource_list_uses u
4608 where a.project_id = x_orig_project_id
4609 and a.resource_list_assignment_id =
4610 u.resource_list_assignment_id
4611 and u.use_code = 'ACTUALS_ACCUM';
4612
4613
4614 x_err_stage := 'copying project resource list uses';
4615
4616 INSERT INTO pa_resource_list_uses (
4617 RESOURCE_LIST_ASSIGNMENT_ID,
4618 USE_CODE,
4619 DEFAULT_FLAG,
4620 LAST_UPDATED_BY,
4621 LAST_UPDATE_DATE,
4622 CREATION_DATE,
4623 CREATED_BY,
4624 LAST_UPDATE_LOGIN )
4625 select
4626 new_list.RESOURCE_LIST_ASSIGNMENT_ID,
4627 use.USE_CODE,
4628 use.Default_Flag,
4629 FND_GLOBAL.USER_ID,
4630 sysdate,
4631 sysdate,
4632 FND_GLOBAL.USER_ID,
4633 FND_GLOBAL.LOGIN_ID
4634 from pa_resource_list_uses use,
4635 pa_resource_list_assignments old_list,
4636 pa_resource_list_assignments new_list
4637 where old_list.project_id = x_orig_project_id
4638 and old_list.RESOURCE_LIST_ASSIGNMENT_ID =
4639 use.RESOURCE_LIST_ASSIGNMENT_ID
4640 and use.use_code = 'ACTUALS_ACCUM'
4641 and old_list.resource_list_id =
4642 new_list.resource_list_id
4643 and new_list.project_id = x_new_project_id;
4644
4645 x_err_stage := 'copying project level job bill rate overrides';
4646
4647 --Below condition added for selective copy project. Tracking Bug No. 3464332
4648 IF 'Y' = l_fn_cb_overrides_flag THEN
4649 INSERT INTO pa_job_bill_rate_overrides (
4650 JOB_ID
4651 , START_DATE_ACTIVE
4652 , LAST_UPDATE_DATE
4653 , LAST_UPDATED_BY
4654 , CREATION_DATE
4655 , CREATED_BY
4656 , LAST_UPDATE_LOGIN
4657 , RATE
4658 , BILL_RATE_UNIT
4659 , PROJECT_ID
4660 , TASK_ID
4661 --MCB Chanes
4662 , RATE_CURRENCY_CODE
4663 , JOB_BILL_RATE_OVERRIDE_ID
4664 , RECORD_VERSION_NUMBER
4665 --MCB Chanes
4666 , END_DATE_ACTIVE
4667 , DISCOUNT_PERCENTAGE
4668 , RATE_DISC_REASON_CODE )
4669 SELECT
4670 JOB_ID
4671 , decode(x_delta, null, x_start_date,
4672 start_date_active + x_delta)
4673 , sysdate
4674 , FND_GLOBAL.USER_ID
4675 , sysdate
4676 , FND_GLOBAL.USER_ID
4677 , FND_GLOBAL.LOGIN_ID
4678 , RATE
4679 , BILL_RATE_UNIT
4680 , x_new_project_id
4681 , null
4682 --MCB Chanes
4683 , RATE_CURRENCY_CODE
4684 , pa_job_bill_rate_overrides_s.NEXTVAL
4685 , RECORD_VERSION_NUMBER
4686 --MCB Chanes
4687 , decode(x_delta, null, x_completion_date,
4688 end_date_active + x_delta)
4689 , DISCOUNT_PERCENTAGE
4690 , RATE_DISC_REASON_CODE
4691 FROM
4692 pa_job_bill_rate_overrides
4693 WHERE project_id = x_orig_project_id
4694 and task_id is null;
4695 END IF;
4696
4697
4698 x_err_stage := 'copying project level job bill title overrides';
4699
4700 --Below condition added for selective copy project. Tracking Bug No. 3464332
4701 IF 'Y' = l_fn_cb_overrides_flag THEN
4702 INSERT INTO pa_job_bill_title_overrides (
4703 JOB_ID
4704 , LAST_UPDATE_DATE
4705 , LAST_UPDATED_BY
4706 , CREATION_DATE
4707 , CREATED_BY
4708 , LAST_UPDATE_LOGIN
4709 , START_DATE_ACTIVE
4710 , BILLING_TITLE
4711 , PROJECT_ID
4712 , TASK_ID
4713 , JOB_BILL_TITLE_OVERRIDE_ID
4714 , RECORD_VERSION_NUMBER
4715 , END_DATE_ACTIVE )
4716 SELECT
4717 JOB_ID
4718 , sysdate
4719 , FND_GLOBAL.USER_ID
4720 , sysdate
4721 , FND_GLOBAL.USER_ID
4722 , FND_GLOBAL.LOGIN_ID
4723 , decode(x_delta, null, x_start_date,
4724 start_date_active + x_delta)
4725 , BILLING_TITLE
4726 , x_new_project_id
4727 , null
4728 , pa_job_bill_title_overrides_s.NEXTVAL
4729 , RECORD_VERSION_NUMBER
4730 , decode(x_delta, null, x_completion_date,
4731 end_date_active + x_delta)
4732 FROM
4733 pa_job_bill_title_overrides
4734 WHERE project_id = x_orig_project_id
4735 and task_id is null;
4736 END IF;
4737
4738 x_err_stage := 'copying project level job assignment overrides';
4739
4740 --Below condition added for selective copy project. Tracking Bug No. 3464332
4741 IF 'Y' = l_fn_cb_overrides_flag THEN
4742 INSERT INTO pa_job_assignment_overrides (
4743 PERSON_ID
4744 , LAST_UPDATE_DATE
4745 , LAST_UPDATED_BY
4746 , CREATION_DATE
4747 , CREATED_BY
4748 , LAST_UPDATE_LOGIN
4749 , START_DATE_ACTIVE
4750 , PROJECT_ID
4751 , TASK_ID
4752 , JOB_ID
4753 , BILLING_TITLE
4754 , JOB_ASSIGNMENT_OVERRIDE_ID
4755 , RECORD_VERSION_NUMBER
4756 , END_DATE_ACTIVE )
4757 SELECT
4758 PERSON_ID
4759 , sysdate
4760 , FND_GLOBAL.USER_ID
4761 , sysdate
4762 , FND_GLOBAL.USER_ID
4763 , FND_GLOBAL.LOGIN_ID
4764 , decode(x_delta, null, x_start_date,
4765 start_date_active + x_delta)
4766 , x_new_project_id
4767 , null
4768 , JOB_ID
4769 , BILLING_TITLE
4770 , pa_job_assignment_overrides_s.NEXTVAL
4771 , RECORD_VERSION_NUMBER
4772 , decode(x_delta, null, x_completion_date,
4773 end_date_active + x_delta)
4774 FROM
4775 pa_job_assignment_overrides
4776 WHERE project_id = x_orig_project_id
4777 and task_id is null;
4778 END IF;
4779
4780 x_err_stage := 'copying project level emp bill rate overrides';
4781
4782 --Below condition added for selective copy project. Tracking Bug No. 3464332
4783 IF 'Y' = l_fn_cb_overrides_flag THEN
4784 INSERT INTO pa_emp_bill_rate_overrides (
4785 PERSON_ID
4786 , LAST_UPDATE_DATE
4787 , LAST_UPDATED_BY
4788 , CREATION_DATE
4789 , CREATED_BY
4790 , LAST_UPDATE_LOGIN
4791 , RATE
4792 , BILL_RATE_UNIT
4793 , START_DATE_ACTIVE
4794 , PROJECT_ID
4795 , TASK_ID
4796 --MCB Chanes
4797 , RATE_CURRENCY_CODE
4798 , EMP_BILL_RATE_OVERRIDE_ID
4799 , RECORD_VERSION_NUMBER
4800 --MCB Chanes
4801 , END_DATE_ACTIVE
4802 , DISCOUNT_PERCENTAGE
4803 , RATE_DISC_REASON_CODE)
4804 SELECT
4805 PERSON_ID
4806 , sysdate
4807 , FND_GLOBAL.USER_ID
4808 , sysdate
4809 , FND_GLOBAL.USER_ID
4810 , FND_GLOBAL.LOGIN_ID
4811 , RATE
4812 , BILL_RATE_UNIT
4813 , decode(x_delta, null, x_start_date,
4814 start_date_active + x_delta)
4815 , x_new_project_id
4816 , null
4817 --MCB Chanes
4818 , RATE_CURRENCY_CODE
4819 , pa_emp_bill_rate_overrides_s.NEXTVAL
4820 , RECORD_VERSION_NUMBER
4821 --MCB Chanes
4822 , decode(x_delta, null, x_completion_date,
4823 end_date_active + x_delta)
4824 , DISCOUNT_PERCENTAGE
4825 , RATE_DISC_REASON_CODE
4826 FROM
4827 pa_emp_bill_rate_overrides
4828 WHERE project_id = x_orig_project_id
4829 and task_id is null;
4830 END IF;
4831
4832 x_err_stage := 'copying project level nl bill rate overrides';
4833
4834 --Below condition added for selective copy project. Tracking Bug No. 3464332
4835 IF 'Y' = l_fn_cb_overrides_flag THEN
4836 INSERT INTO pa_nl_bill_rate_overrides (
4837 EXPENDITURE_TYPE
4838 , LAST_UPDATE_DATE
4839 , LAST_UPDATED_BY
4840 , CREATION_DATE
4841 , CREATED_BY
4842 , LAST_UPDATE_LOGIN
4843 , START_DATE_ACTIVE
4844 , NON_LABOR_RESOURCE
4845 , MARKUP_PERCENTAGE
4846 , BILL_RATE
4847 , PROJECT_ID
4848 , TASK_ID
4849 --MCB Chanes
4850 , RATE_CURRENCY_CODE
4851 , NL_BILL_RATE_OVERRIDE_ID
4852 , RECORD_VERSION_NUMBER
4853 --MCB Chanes
4854 , END_DATE_ACTIVE
4855 , DISCOUNT_PERCENTAGE
4856 , RATE_DISC_REASON_CODE )
4857 SELECT
4858 EXPENDITURE_TYPE
4859 , sysdate
4860 , FND_GLOBAL.USER_ID
4861 , sysdate
4862 , FND_GLOBAL.USER_ID
4863 , FND_GLOBAL.LOGIN_ID
4864 , decode(x_delta, null, x_start_date,
4865 start_date_active + x_delta)
4866 , NON_LABOR_RESOURCE
4867 , MARKUP_PERCENTAGE
4868 , BILL_RATE
4869 , x_new_project_id
4870 , null
4871 --MCB Chanes
4872 , RATE_CURRENCY_CODE
4873 , pa_nl_bill_rate_overrides_s.NEXTVAL
4874 , RECORD_VERSION_NUMBER
4875 --MCB Chanes
4876 , decode(x_delta, null, x_completion_date,
4877 end_date_active + x_delta)
4878 , DISCOUNT_PERCENTAGE
4879 , RATE_DISC_REASON_CODE
4880 FROM
4881 pa_nl_bill_rate_overrides
4882 WHERE project_id = x_orig_project_id
4883 and task_id is null;
4884 END IF;
4885
4886 x_err_stage := 'copying project level labor multipliers ';
4887
4888 INSERT INTO pa_labor_multipliers (
4889 PROJECT_ID
4890 , TASK_ID
4891 , LABOR_MULTIPLIER
4892 , START_DATE_ACTIVE
4893 , END_DATE_ACTIVE
4894 , LAST_UPDATE_DATE
4895 , LAST_UPDATED_BY
4896 , CREATION_DATE
4897 , CREATED_BY
4898 , LABOR_MULTIPLIER_ID
4899 , RECORD_VERSION_NUMBER
4900 , LAST_UPDATE_LOGIN )
4901
4902 SELECT
4903 x_new_project_id
4904 , null
4905 , labor_multiplier
4906 , decode(x_delta, null, x_start_date,
4907 start_date_active + x_delta)
4908 , decode(x_delta, null, x_completion_date,
4909 end_date_active + x_delta)
4910 , sysdate
4911 , FND_GLOBAL.USER_ID
4912 , sysdate
4913 , FND_GLOBAL.USER_ID
4914 , pa_labor_multipliers_s.NEXTVAL
4915 , RECORD_VERSION_NUMBER
4916 , FND_GLOBAL.LOGIN_ID
4917 FROM pa_labor_multipliers
4918 WHERE project_id = x_orig_project_id
4919 and task_id is null;
4920
4921 -- added the following call to copy the attachments to
4922 -- a perticular project to the newly created project
4923
4924 /*Following code added for selective copy project options. Tracking bug No 3464332*/
4925 OPEN cur_get_flag('PR_ATTACHMENTS_FLAG');
4926 FETCH cur_get_flag INTO l_pr_attachments_flag;
4927 CLOSE cur_get_flag;
4928
4929 OPEN cur_get_flag('PR_FRM_SRC_TMPL_FLAG');
4930 FETCH cur_get_flag INTO l_pr_frm_src_tmpl_flag;
4931 CLOSE cur_get_flag;
4932 --Following two IF conditions added for selective copy project
4933 IF 'Y' = l_pr_attachments_flag THEN
4934 IF 'Y' = l_pr_frm_src_tmpl_flag THEN
4935 fnd_attached_documents2_pkg.copy_attachments('PA_PROJECTS',
4936 --x_orig_project_id, Bug 3694616
4937 x_created_from_proj_id,
4938 null,
4939 null,
4940 null,
4941 null,
4942 'PA_PROJECTS',
4943 --x_created_from_proj_id, Bug 3694616
4944 x_new_project_id,
4945 null,
4946 null,
4947 null,
4948 null,
4949 FND_GLOBAL.USER_ID,
4950 FND_GLOBAL.LOGIN_ID,
4951 275, null, null);
4952 ELSE
4953 fnd_attached_documents2_pkg.copy_attachments('PA_PROJECTS',
4954 x_orig_project_id,
4955 null,
4956 null,
4957 null,
4958 null,
4959 'PA_PROJECTS',
4960 x_new_project_id,
4961 null,
4962 null,
4963 null,
4964 null,
4965 FND_GLOBAL.USER_ID,
4966 FND_GLOBAL.LOGIN_ID,
4967 275, null, null);
4968 END IF; -- 'Y' = l_pr_frm_src_tmpl_flag
4969 END IF; --'Y' = l_pr_attachments_flag
4970
4971 -------
4972 --EH changes
4973 DECLARE
4974 --Added for Selective Copy Project. Tracking Bug No. 3464332
4975 --This cursor determines if atleast one WP version has been selected by the user
4976 CURSOR min_one_wp_version_sel IS
4977 SELECT distinct 'Y'
4978 FROM PA_PROJECT_COPY_OPTIONS_TMP
4979 WHERE CONTEXT = 'WORKPLAN'
4980 AND VERSION_ID IS NOT NULL;
4981
4982 l_workplan_enabled VARCHAR2(1) := 'N';
4983 l_shared VARCHAR2(1);
4984 l_min_one_wp_ver_sel VARCHAR2(1) := 'N';
4985 l_fin_tasks_flag VARCHAR2(1) := 'Y';
4986 BEGIN
4987 --Check whether WP structure is enabled for the source project
4988 l_workplan_enabled := PA_PROJ_TASK_STRUC_PUB.WP_STR_EXISTS( x_orig_project_id );
4989
4990 IF NVL( l_workplan_enabled, 'N' ) = 'Y' THEN
4991 --Check whether the structures are shared or not in the source project
4992 l_shared := PA_PROJECT_STRUCTURE_UTILS.check_sharing_enabled( x_orig_project_id );
4993 ELSE
4994 l_shared := 'N';
4995 END IF;
4996
4997 --Check whether atleast one WP version has been selected by the user or not
4998 OPEN min_one_wp_version_sel;
4999 FETCH min_one_wp_version_sel INTO l_min_one_wp_ver_sel;
5000 CLOSE min_one_wp_version_sel;
5001
5002 --Check whether the financial tasks flag is checked or not
5003 OPEN cur_get_flag('FN_FIN_TASKS_FLAG');
5004 FETCH cur_get_flag INTO l_fin_tasks_flag;
5005 CLOSE cur_get_flag;
5006
5007 IF 'Y' = l_fin_tasks_flag OR ('Y' = l_shared AND 'Y' = l_min_one_wp_ver_sel) THEN
5008 --End selective copy project. Tracking Bug No. 3464332
5009 if (x_copy_task_flag = 'Y' ) then
5010 x_err_stage := 'call copy_task';
5011 pa_project_core2.copy_task ( x_orig_project_id,
5012 x_new_project_id,
5013 x_err_code,
5014 x_err_stage,
5015 x_err_stack);
5016
5017 -- if application or oracle error return
5018 if ( x_err_code > 0 or x_err_code < 0 ) then
5019 x_err_code := 800;
5020 IF x_err_stage IS NULL
5021 THEN
5022 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_COPY_TASK');
5023 END IF;
5024 x_err_stack := x_err_stack||'->pa_project_core2.copy_task';
5025 rollback to copy_project;
5026 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5027 return;
5028 end if;
5029 end if;
5030 -- Bug 4221196 - Moved l_is_fin_str_copied := 'Y' out of x_copy_task_flag check
5031 /* Bug 4188514 - If financial tasks are copied then only copy in budget versions */
5032 l_is_fin_str_copied := 'Y';
5033 END IF;
5034 EXCEPTION WHEN OTHERS THEN
5035 x_err_code := 800;
5036 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
5037 -- IF x_err_stage IS NULL
5038 -- THEN
5039 x_err_stage := 'API: '||'pa_project_core2.copy_task'||' SQL error message: '||SUBSTR( SQLERRM,1,1900);
5040 -- END IF;
5041 rollback to copy_project;
5042 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5043 return;
5044 END;
5045
5046 /*FPM Changes for Project Setp- Customer and Invoice Method at Top task */
5047
5048 /* if customer at top task is enabled for project and customer is present in quick entry, copy the same customer to all top tasks.
5049 otherwsie copy the source task customers */
5050
5051 DECLARE
5052
5053 l_enable_top_task_cust_flag varchar2(1);
5054 l_enable_top_task_inv_mth_flag varchar2(1);
5055
5056 Begin
5057 select enable_top_task_customer_flag ,
5058 enable_top_task_inv_mth_flag
5059 Into l_enable_top_task_cust_flag,
5060 l_enable_top_task_inv_mth_flag
5061 from pa_projects
5062 where project_id = x_new_project_id;
5063
5064 IF nvl(l_enable_top_task_cust_flag,'N') ='Y' Then
5065 If x_customer_id is not null then
5066 Update pa_tasks
5067 set customer_id =x_customer_id
5068 where project_id=x_new_project_id;
5069 Else
5070 Update pa_tasks t
5071 Set t.customer_id = (select old.customer_id from pa_tasks old
5072 where old.project_id = x_orig_project_id
5073 and old.task_number = t.task_number
5074 and old.customer_id is not null)
5075 where t.project_id = x_new_project_id;
5076 End if;
5077 END IF;
5078
5079 If x_distribution_rule is not null then
5080 Update pa_tasks t
5081 set t.revenue_accrual_method =substr(x_distribution_rule, 1, instr(x_distribution_rule,'/')-1),
5082 t.invoice_method = substr(x_distribution_rule, instr(x_distribution_rule,'/')+1)
5083 where t.project_id=x_new_project_id;
5084 Else
5085 Update pa_tasks t
5086 set t.revenue_accrual_method =(select old.revenue_accrual_method
5087 from pa_tasks old
5088 where old.project_id = x_orig_project_id
5089 and old.task_number = t.task_number
5090 and old.revenue_accrual_method is not null),
5091 t.invoice_method = (select old.invoice_method
5092 from pa_tasks old
5093 where old.project_id = x_orig_project_id
5094 and old.task_number = t.task_number
5095 and old.invoice_method is not null)
5096 where t.project_id = x_new_project_id;
5097 End if;
5098
5099 End ;
5100
5101 /*FPM Changes for Project Setp- Customer and Invoice Method at Top task ends */
5102
5103 --Bug 3847507 : Shifted the following two calls to before copy_structure as the copy_wp_budget_versions
5104 -- call requires the resource list/ RBS information to have been copied
5105 --Copy resource lists from source to destination project
5106 BEGIN
5107 PA_CREATE_RESOURCE.Copy_Resource_Lists( p_source_project_id => x_orig_project_id
5108 ,p_destination_project_id => x_new_project_id
5109 ,x_return_status => l_return_status ) ;
5110 IF l_return_status <> 'S' THEN
5111 x_err_code := 905;
5112 x_err_stack := x_err_stack||'->PA_CREATE_RESOURCE.Copy_Resource_Lists';
5113 ROLLBACK TO copy_project;
5114 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5115 RETURN;
5116 END IF;
5117 EXCEPTION
5118 WHEN OTHERS THEN
5119 x_err_code := 905;
5120 x_err_stage := 'API: '||'PA_CREATE_RESOURCE.Copy_Resource_Lists'||
5121 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
5122 ROLLBACK TO copy_project;
5123 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5124 RETURN;
5125 END ;
5126 --Copy RBS associations from source to destination project
5127 BEGIN
5128 PA_RBS_ASGMT_PVT.Copy_Project_Assignment( p_rbs_src_project_id => x_orig_project_id
5129 ,p_rbs_dest_project_id => x_new_project_id
5130 ,x_return_status => l_return_status ) ;
5131 IF l_return_status <> 'S' THEN
5132 x_err_code := 920;
5133 x_err_stack := x_err_stack||'->PA_RBS_ASGMT_PUB.Copy_Project_Assignment';
5134 ROLLBACK TO copy_project;
5135 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5136 RETURN;
5137 END IF;
5138 EXCEPTION
5139 WHEN OTHERS THEN
5140 x_err_code := 920;
5141 x_err_stage := 'API: '||'PA_RBS_ASGMT_PUB.Copy_Project_Assignment'||
5142 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
5143 ROLLBACK TO copy_project;
5144 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5145 RETURN;
5146 END ;
5147 --Bug 3847507 : End shift of above two calls
5148 --Project Structure changes
5149 --EH Changes
5150 BEGIN
5151 --This condition is checked here to make sure that the tasks
5152 --have already been created.
5153 -- if (x_copy_task_flag = 'Y' ) then
5154 /*PA_PROJ_TASK_STRUC_PUB.COPY_STRUCTURE(
5155 p_dest_project_id => x_new_project_id
5156 ,p_src_project_id => x_orig_project_id
5157 -- anlee
5158 -- Dates changes
5159 ,p_delta => x_delta
5160 -- End of changes
5161 ,x_msg_count => l_msg_count
5162 ,x_msg_data => l_msg_data
5163 ,x_return_status => l_return_status );
5164 */ --bug 2805602 --commented out the previous call and added the following.
5165
5166 --bug 3991169
5167 IF (x_start_date IS NULL) THEN
5168 l_target_start_date := NULL;
5169 l_target_finish_date := NULL;
5170 END IF;
5171 --end bug 3991169
5172
5173 PA_PROJ_TASK_STRUC_PUB.COPY_STRUCTURE(
5174 p_dest_project_id => x_new_project_id
5175 ,p_src_project_id => x_orig_project_id
5176 ,p_delta => x_delta
5177 ,p_dest_template_flag => x_template_flag
5178 ,p_src_template_flag => x_orig_template_flag
5179 ,p_dest_project_name => x_project_name
5180 ,p_target_start_date => l_target_start_date
5181 ,p_target_finish_date => l_target_finish_date
5182 ,p_calendar_id => l_cal_id
5183 ,p_copy_task_flag => x_copy_task_flag
5184 ,x_msg_count => l_msg_count
5185 ,x_msg_data => l_msg_data
5186 ,x_return_status => l_return_status );
5187
5188 IF l_return_status <> 'S'
5189 THEN
5190 x_err_code := 805;
5191 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_DFLT_STRUCTURE');
5192 x_err_stack := x_err_stack||'->PA_PROJ_TASK_STRUC_PUB.COPY_STRUCTURE';
5193 rollback to copy_project;
5194 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5195 return;
5196 END IF;
5197 -- end if;
5198 EXCEPTION WHEN OTHERS THEN
5199 x_err_code := 805;
5200 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
5201 -- IF x_err_stage IS NULL
5202 -- THEN
5203 x_err_stage := 'API: '||'PA_PROJ_TASK_STRUC_PUB.COPY_STRUCTURE'||
5204
5205 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
5206 -- END IF;
5207 rollback to copy_project;
5208 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5209 return;
5210 END;
5211 --Project Structure changes end
5212
5213 --EH Changes
5214 BEGIN
5215 x_err_stage := 'Copy Object Page Layouts';
5216 PA_Page_layout_Utils.copy_object_page_layouts(
5217 p_object_type => 'PA_PROJECTS',
5218 P_object_id_from => x_orig_project_id ,
5219 P_object_id_to => x_new_project_id,
5220 x_return_status => l_return_status,
5221 x_msg_count => l_msg_count,
5222 x_msg_data => l_msg_data );
5223 IF l_return_status <> fnd_api.g_ret_sts_success
5224 THEN
5225 x_err_code := 730;
5226 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_COPY_OBJ_PG_LAY');
5227 x_err_stack := x_err_stack||'->PA_Page_layout_Utils.copy_object_page_layouts';
5228 rollback to copy_project;
5229 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5230 return;
5231 END IF;
5232 EXCEPTION WHEN OTHERS THEN
5233 x_err_code := 730;
5234 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
5235 -- IF x_err_stage IS NULL
5236 -- THEN
5237 x_err_stage := 'API: '||'PA_Page_layout_Utils.copy_object_page_layouts'||' SQL error message: '||SUBSTR( SQLERRM,1,1900);
5238 -- END IF;
5239 rollback to copy_project;
5240 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5241 return;
5242 END;
5243
5244 --MW Changes need to be executed AFTER copying the layouts above, do not move
5245 --this code. Bug 2246601, Action Sets.
5246 BEGIN
5247 x_err_stage := 'Copy Project Status Action Sets';
5248 PA_PROJ_STAT_ACTSET.copy_action_sets(
5249 p_project_id_from => x_orig_project_id ,
5250 p_project_id_to => x_new_project_id,
5251 x_return_status => l_return_status,
5252 x_msg_count => l_msg_count,
5253 x_msg_data => l_msg_data );
5254 IF l_return_status <> fnd_api.g_ret_sts_success
5255 THEN
5256 x_err_code := 844;
5257 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_COPY_PROJ_ACTSET');
5258 x_err_stack := x_err_stack||'->PA_PROJ_STAT_ACTSET.copy_action_sets';
5259 rollback to copy_project;
5260 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5261 return;
5262 END IF;
5263 EXCEPTION WHEN OTHERS THEN
5264 x_err_code := 844;
5265 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
5266 -- IF x_err_stage IS NULL
5267 -- THEN
5268 x_err_stage := 'API: '||'PA_PROJ_STAT_ACTSET.copy_action_sets'||' SQL error message: '||SUBSTR( SQLERRM,1,1900);
5269 -- END IF;
5270 rollback to copy_project;
5271 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5272 return;
5273 END;
5274
5275 BEGIN
5276 x_err_stage := 'Copy Task Progress Action Set';
5277 PA_TASK_PROG_ACTSET.copy_action_sets(
5278 p_project_id_from => x_orig_project_id ,
5279 p_project_id_to => x_new_project_id,
5280 x_return_status => l_return_status,
5281 x_msg_count => l_msg_count,
5282 x_msg_data => l_msg_data );
5283 IF l_return_status <> fnd_api.g_ret_sts_success
5284 THEN
5285 x_err_code := 866;
5286 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_COPY_TASK_ACTSET');
5287 x_err_stack := x_err_stack||'->PA_TASK_PROG_ACTSET.copy_action_sets';
5288 rollback to copy_project;
5289 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5290 return;
5291 END IF;
5292 EXCEPTION WHEN OTHERS THEN
5293 x_err_code := 866;
5294 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
5295 -- IF x_err_stage IS NULL
5296 -- THEN
5297 x_err_stage := 'API: '||'PA_TASK_PROG_ACTSET.copy_action_sets'||' SQL error message: '||SUBSTR( SQLERRM,1,1900);
5298 -- END IF;
5299 rollback to copy_project;
5300 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5301 return;
5302 END;
5303
5304 --END MW CHanges
5305
5306
5307 /* Bug#3480409 : FP.M Changes: Added code for copying Perf/Score rules, starts here */
5308 BEGIN
5309 x_err_stage := 'Copy object Perf/Score rules';
5310 PA_PERF_EXCP_UTILS.copy_object_rule_assoc
5311 ( p_from_object_type => 'PA_PROJECTS'
5312 ,p_from_object_id => x_orig_project_id
5313 ,p_to_object_type => 'PA_PROJECTS'
5314 ,p_to_object_id => x_new_project_id
5315 ,x_return_status => l_return_status
5316 ,x_msg_count => l_msg_count
5317 ,x_msg_data => l_msg_data );
5318
5319 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
5320 x_err_code := 880;
5321 x_err_stage := pa_project_core1.get_message_from_stack('PA_PERF_COPY_RULES_ERR');
5322 x_err_stack := x_err_stack||'->PA_PERF_EXCP_UTILS.copy_object_rule_assoc';
5323 rollback to copy_project;
5324 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5325 return;
5326 END IF;
5327 EXCEPTION WHEN OTHERS THEN
5328 x_err_code := 880;
5329 x_err_stage := 'API: '||'PA_Page_layout_Utils.copy_object_page_layouts'||' SQL error message: '||SUBSTR( SQLERRM,1,1900);
5330 rollback to copy_project;
5331 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5332 return;
5333 END;
5334 /* Bug#3480409 : FP.M Changes: Added code for copying Perf/Score rules, ends here */
5335
5336 -- anlee
5337 -- Copying item associations
5338
5339 x_err_stage := 'Calling PA_EGO_WRAPPER_PUB.COPY_ITEM_ASSOCS API ...';
5340
5341 BEGIN
5342 /*Following code added for selective copy project options. Tracking bug No 3464332*/
5343 OPEN cur_get_flag('PR_ITEM_ASSOC_FLAG');
5344 FETCH cur_get_flag INTO l_pr_item_assoc_flag;
5345 CLOSE cur_get_flag;
5346
5347 IF 'Y' = l_pr_item_assoc_flag THEN
5348 PA_EGO_WRAPPER_PUB.COPY_ITEM_ASSOCS
5349 ( p_project_id_from => x_orig_project_id
5350 ,p_project_id_to => x_new_project_id
5351 ,p_init_msg_list => fnd_api.g_FALSE
5352 ,p_commit => fnd_api.g_FALSE
5353 ,x_return_status => l_return_status
5354 ,x_errorcode => l_errorcode
5355 ,x_msg_count => l_msg_count
5356 ,x_msg_data => l_msg_data);
5357
5358 if l_return_status <> 'S' then
5359 x_err_code := 710;
5360 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_COPY_ITEM_ASSOCS');
5361 x_err_stack := x_err_stack||'->PA_EGO_WRAPPER_PUB.COPY_ITEM_ASSOCS';
5362 rollback to copy_project;
5363 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5364 return;
5365 end if;
5366 END IF; --for 'Y' = l_pr_item_assoc_flag
5367 EXCEPTION WHEN OTHERS THEN
5368 x_err_code := 710;
5369 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
5370 -- IF x_err_stage IS NULL
5371 -- THEN
5372 x_err_stage := 'API: '||'PA_EGO_WRAPPER_PUB.COPY_ITEM_ASSOCS'||
5373 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
5374 -- END IF;
5375 rollback to copy_project;
5376 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5377 return;
5378 END;
5379 -- anlee end of changes
5380
5381 -- anlee
5382 -- Ext Attribute changes
5383 -- Bug 2904327
5384 x_err_stage := 'Calling PA_USER_ATTR_PUB.COPY_USER_ATTRS_DATA API ...';
5385
5386 BEGIN
5387 /*Following code and IF condition added for selective copy project options. Tracking bug No 3464332*/
5388 OPEN cur_get_flag('PR_USER_DEFN_ATT_FLAG');
5389 FETCH cur_get_flag INTO l_pr_user_defn_att_flag;
5390 CLOSE cur_get_flag;
5391
5392 IF 'Y' = l_pr_user_defn_att_flag THEN
5393 PA_USER_ATTR_PUB.COPY_USER_ATTRS_DATA
5394 ( p_object_id_from => x_orig_project_id
5395 ,p_object_id_to => x_new_project_id
5396 ,p_object_type => 'PA_PROJECTS'
5397 ,x_return_status => l_return_status
5398 ,x_errorcode => l_errorcode
5399 ,x_msg_count => l_msg_count
5400 ,x_msg_data => l_msg_data);
5401
5402 if l_return_status <> 'S' then
5403 x_err_code := 16384;
5404 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_COPY_PROJ_EXT_ATTR');
5405 x_err_stack := x_err_stack||'->PA_USER_ATTR_PUB.COPY_USER_ATTRS_DATA';
5406 rollback to copy_project;
5407 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5408 return;
5409 end if;
5410 END IF; --for 'Y' = l_pr_user_defn_att_flag
5411 EXCEPTION WHEN OTHERS THEN
5412 x_err_code := 16384;
5413 x_err_stage := 'API: '||'PA_USER_ATTR_PUB.COPY_USER_ATTRS_DATA'||
5414 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
5415 rollback to copy_project;
5416 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5417 return;
5418 END;
5419 -- anlee end of changes
5420
5421 --EH Changes
5422 --
5423 -- Bug2787577. If x_template_flag = 'Y', do not call API pa_accum_proj_list.Insert_accum.
5424 -- Encapsulated the EH changes inside the IF - END IF
5425 --
5426 IF x_template_flag = 'N' THEN
5427 BEGIN
5428 x_err_stage := 'PSI Project List-Insert Accum';
5429 pa_accum_proj_list.Insert_Accum
5430 ( p_project_id => x_new_project_id
5431 ,x_return_status => l_return_status
5432 ,x_msg_count => l_msg_count
5433 ,x_msg_data => l_msg_data
5434 );
5435 IF l_return_status <> fnd_api.g_ret_sts_success
5436 THEN
5437 x_err_code := 735;
5438 x_err_stage := pa_project_core1.get_message_from_stack('PA_ERR_INSERT_ACCUM');
5439 x_err_stack := x_err_stack||'->pa_accum_proj_list.Insert_Accum';
5440 rollback to copy_project;
5441 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5442 return;
5443 END IF;
5444 EXCEPTION WHEN OTHERS THEN
5445 x_err_code := 735;
5446 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
5447 -- IF x_err_stage IS NULL
5448 -- THEN
5449 x_err_stage := 'API: '||'pa_accum_proj_list.Insert_Accum'||' SQL error message: '||SUBSTR( SQLERRM,1,1900);
5450 -- END IF;
5451 rollback to copy_project;
5452 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5453 return;
5454 END;
5455 END IF;
5456 -- Bug 2787577 Changes ends
5457
5458
5459 -- Call Agreement API to copy an agreement/fundings if exists.
5460 x_err_stage := 'copying agreement and funding ';
5461
5462 if x_orig_template_flag = 'Y' then
5463
5464 declare
5465 cursor cust is
5466 select 1
5467 from pa_project_copy_overrides
5468 where project_id = x_created_from_proj_id
5469 and field_name = 'CUSTOMER_NAME';
5470
5471 ovr_cust number;
5472
5473 begin
5474
5475 open cust;
5476 fetch cust into ovr_cust;
5477
5478 -- If there is a customer option in Quick Entry and no
5479 -- override customer is entered, create no agreement since
5480 -- there is no project customer for the new project.
5481 if (cust%notfound or x_customer_id is not null) then
5482
5483 --EH Changes
5484
5485 BEGIN
5486 pa_billing_core.copy_agreement(
5487 x_orig_project_id,
5488 x_new_project_id,
5489 x_customer_id,
5490 --MCA Sakthi for MultiAgreementCurreny Project
5491 x_agreement_org_id,
5492 x_agreement_currency,
5493 x_agreement_amount,
5494 --MCA Sakthi for MultiAgreementCurreny Project
5495 x_template_flag,
5496 x_delta,
5497 x_err_code,
5498 x_err_stage,
5499 x_err_stack);
5500
5501 if x_err_code <> 0 then
5502 x_err_code := 745;
5503 IF x_err_stage IS NULL
5504 THEN
5505 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_COPY_AGREEMENT');
5506 END IF;
5507 x_err_stack := x_err_stack||'->pa_billing_core.copy_agreement';
5508 rollback to copy_project;
5509 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5510 return;
5511 end if;
5512 EXCEPTION WHEN OTHERS THEN
5513 x_err_code := 745;
5514 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
5515 -- IF x_err_stage IS NULL
5516 -- THEN
5517 x_err_stage := 'API: '||'pa_billing_core.copy_agreement'||
5518 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
5519 -- END IF;
5520 rollback to copy_project;
5521 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5522 return;
5523 END;
5524 end if;
5525
5526 end;
5527
5528 end if; -- end of copy agreement
5529
5530 --commented out for bug 2981655 if (x_copy_budget_flag = 'Y') then
5531 --PA K Changes. As per request from Ramesh team we are removing all
5532 --the calls for budgets and calling one wrapper API instead.
5533
5534 /* Bug 4188514 - If financial tasks are copied then only copy the budget versions */
5535 IF l_is_fin_str_copied = 'Y' THEN
5536 BEGIN
5537 PA_FP_COPY_FROM_PKG.copy_finplans_from_project
5538 ( p_source_project_id => x_orig_project_id
5539 ,p_target_project_id => x_new_project_id
5540 ,p_shift_days => NVL(x_delta,0) -- 3874742 passing p_shift_days as 0 if x_delta is NULL
5541 ,p_copy_version_and_elements => NVL(x_copy_budget_flag,'Y') --bug 2981655. Default is always 'Y'. spoke to Ramesh
5542 ,p_agreement_amount => x_agreement_amount --2986930
5543 ,x_return_status => l_return_status
5544 ,x_msg_count => l_msg_count
5545 ,x_msg_data => l_msg_data
5546 );
5547 IF l_return_status <> fnd_api.g_ret_sts_success
5548 THEN
5549 IF l_msg_data IS NULL THEN
5550 l_msg_data := 'PA_ERR_COPY_FINPLANS';
5551 END IF ;
5552 x_err_code := 750;
5553 x_err_stage := pa_project_core1.get_message_from_stack(l_msg_data);
5554 x_err_stack := x_err_stack||'->PA_FP_COPY_FROM_PKG.copy_finplans_from_project';
5555 rollback to copy_project;
5556 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5557 return;
5558 END IF;
5559 EXCEPTION WHEN OTHERS THEN
5560 x_err_code := 750;
5561 -- x_err_stage := pa_project_core1.get_message_from_stack( null );
5562 -- IF x_err_stage IS NULL
5563 -- THEN
5564 x_err_stage := 'API: '||'PA_FP_COPY_FROM_PKG.copy_finplans_from_project'||' SQL error message: '||
5565 SUBSTR( SQLERRM,1,1900);
5566 -- END IF;
5567 rollback to copy_project;
5568 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5569 return;
5570 END;
5571 END IF; -- Bug 4188514
5572
5573 --commented out for bug 2981655 end if; -- end of copy budget
5574
5575 --bug 3301192
5576
5577 -- Commented out for bug # 3620190.
5578 /*
5579 BEGIN
5580 PA_PROGRESS_REPORT_UTILS.copy_project_tab_menu(
5581 p_src_project_id => x_orig_project_id,
5582 p_dest_project_id => x_new_project_id,
5583 x_msg_count => l_msg_count,
5584 x_msg_data => l_msg_data,
5585 x_return_status => l_return_status);
5586 EXCEPTION WHEN OTHERS THEN
5587 x_err_code := 755;
5588 x_err_stage := 'API: '||'PA_PROGRESS_REPORT_UTILS.copy_project_tab_menu'||' SQL error message: '||
5589 SUBSTR( SQLERRM,1,1900);
5590 rollback to copy_project;
5591 return;
5592 END;
5593 */
5594 -- Commented out for bug # 3620190.
5595
5596 --bug 3301192
5597 --Bug 5378256/5137355 : Baseline budget should be created in the target only if src has one.
5598 --Bug 6857315 Modified the cursor to check if the self service approved revenue budget exists in
5599 --source or not. Earlier code used to check for old model budget only
5600 -- This code will check for both SS and old model.
5601 BEGIN
5602 select 'Y' into l_baseline_exists_in_src
5603 from pa_budget_versions pbv
5604 where pbv.project_id = x_orig_project_id
5605 and pbv.budget_status_code = 'B'
5606 and (budget_type_code='AR'
5607 or APPROVED_REV_PLAN_TYPE_FLAG = 'Y') -- Added for bug 6857315
5608 and rownum <=1;
5609 EXCEPTION
5610 When no_data_found then
5611 l_baseline_exists_in_src := 'N';
5612 END;
5613
5614
5615 --EH Changes
5616 --bug 2621734
5617 DECLARE
5618
5619 BEGIN
5620 IF NVL( l_baseline_funding_flag, 'N' ) = 'Y'
5621 and PA_BILLING_CORE.check_funding_exists(x_project_id => x_new_project_id) = 'Y'
5622 and nvl(l_baseline_exists_in_src,'N') = 'Y' --Bug 5378256/5137355
5623 THEN
5624 PA_BASELINE_FUNDING_PKG.create_budget_baseline (
5625 p_project_id => x_new_project_id,
5626 x_err_code => x_err_code,
5627 x_status => x_err_stage );
5628
5629 IF x_err_code <> 0
5630 THEN
5631 x_err_code := 755;
5632 x_err_stage := pa_project_core1.get_message_from_stack('PA_ERR_BUDGT_BASLINE');
5633 x_err_stack := x_err_stack||'->PA_BASELINE_FUNDING_PKG.create_budget_baseline';
5634 rollback to copy_project;
5635 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5636 return;
5637 END IF;
5638 END IF;
5639 EXCEPTION WHEN OTHERS THEN
5640 x_err_code := 755;
5641 x_err_stage := 'API: '||'PA_BASELINE_FUNDING_PKG.create_budget_baseline'||' SQL error message: '||
5642 SUBSTR( SQLERRM,1,1900);
5643 rollback to copy_project;
5644 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5645 return;
5646 END;
5647 --bug 2621734
5648
5649 -- maansari
5650 -- Task Association changes
5651 -- Bug 3024607
5652 /* commenting out as the decision to include this is in b3 not yet made
5653 x_err_stage := 'Calling PA_TASK_PUB1.Copy_Task_Associations API ...';
5654
5655 BEGIN
5656 PA_TASK_PUB1.Copy_Task_Associations
5657 ( p_project_id_from => x_orig_project_id
5658 ,p_project_id_to => x_new_project_id
5659 ,x_return_status => l_return_status
5660 ,x_msg_count => l_msg_count
5661 ,x_msg_data => l_msg_data);
5662
5663 if l_return_status <> 'S' then
5664 x_err_code := 760;
5665 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_COPY_PROJ_TASK_ASSN');
5666 x_err_stack := x_err_stack||'->PA_TASK_PUB1.COPY_TASK_ASSOCIATIONS';
5667 rollback to copy_project;
5668 return;
5669 end if;
5670 EXCEPTION WHEN OTHERS THEN
5671 x_err_code := 760;
5672 x_err_stage := 'API: '||'PA_TASK_PUB1.COPY_TASK_ASSOCIATIONS'||
5673 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
5674 rollback to copy_project;
5675 return;
5676 END;
5677 */
5678 -- 3024607 maansari end of changes
5679 --bug 3068506 maansari Copy Task Attachments ( moved from PA_PROJECT_CORE2.copy_task )
5680 /* Bug 3140032. It needs to be moved to PA_PROJ_TASK_STRUC_PUB.copy_structures_tasks_bulk api.
5681 x_err_stage := 'Copying task attachments..';
5682 Begin
5683 Declare
5684 cursor c_attach_tasks is
5685 select orig.proj_element_id orig_task_id,
5686 new.proj_element_id new_task_id
5687 from pa_proj_elements orig, pa_proj_elements new
5688 where orig.project_id = x_orig_project_id
5689 and new.element_number = orig.element_number
5690 and new.project_id = x_new_project_id
5691 and new.object_type = 'PA_TASKS'
5692 and orig.object_type = 'PA_TASKS';
5693
5694 c_atch c_attach_tasks%rowtype ;
5695
5696 begin
5697 open c_attach_tasks;
5698 loop
5699 fetch c_attach_tasks
5700 into c_atch ;
5701 if c_attach_tasks%notfound then
5702 exit ;
5703 end if;
5704 fnd_attached_documents2_pkg.copy_attachments('PA_TASKS',
5705 c_atch.orig_task_id,
5706 null, null, null, null,
5707 'PA_TASKS',
5708 c_atch.new_task_id,
5709 null, null, null, null,
5710 FND_GLOBAL.USER_ID,
5711 FND_GLOBAL.LOGIN_ID,
5712 275, null, null);
5713
5714 end loop ;
5715 close c_attach_tasks;
5716 exception
5717 when NO_DATA_FOUND then
5718 --rollback to copy_project;
5719 --return;
5720 null;
5721 when others then
5722 null;
5723 end ;
5724 end ;
5725
5726 -- End of the attachment call
5727 --end bug 3068506 maansari Copy Task Attachements
5728 3140032 Moved to PA_PROJ_TASK_STRUC_PUB.copy_structures_tasks_bulk api*/
5729
5730 x_project_id := x_new_project_id;
5731 x_err_stack := old_stack;
5732 -- Check if WF is enabled for the new project status
5733
5734 l_project_type := NULL;
5735 p_project_status_code := NULL;
5736
5737 OPEN l_get_details_for_wf_csr (x_new_project_id);
5738 FETCH l_get_details_for_wf_csr INTO
5739 l_project_type,
5740 p_project_status_code;
5741 CLOSE l_get_details_for_wf_csr;
5742 IF (l_project_type IS NOT NULL AND p_project_status_code IS NOT NULL)
5743 THEN
5744
5745 pa_project_stus_utils.check_wf_enabled
5746 (x_project_status_code => p_project_status_code,
5747 x_project_type => l_project_type,
5748 x_project_id => x_new_project_id,
5749 x_wf_item_type => l_item_type,
5750 x_wf_process => l_wf_process,
5751 x_wf_enabled_flag => l_wf_enabled_flag,
5752 x_err_code => l_err_code
5753 );
5754
5755
5756 -- 31-DEC-97, jwhite -----------------------------------------
5757 -- Workflow is NOT coupled to changing statues.
5758 -- So, the x_err_code for the aforementioned Check_Wf_Enabled
5759 --- is IGNORED if x_err_code > 0.
5760 --
5761
5762 IF (l_err_code > 0)
5763 THEN
5764 x_err_code := 0;
5765 END IF;
5766 -- ------------------------------------------------------------------
5767
5768 IF x_err_code = 0 THEN
5769 IF l_wf_enabled_flag = 'Y' THEN -- start the project workflow
5770 UPDATE pa_projects
5771 SET wf_status_code = 'IN_ROUTE'
5772 WHERE project_id = x_new_project_id;
5773 Pa_project_wf.Start_Project_Wf
5774 (p_project_id => x_new_project_id,
5775 p_err_stack => x_err_stack,
5776 p_err_stage => x_err_stage,
5777 p_err_code => x_err_code );
5778 END IF;
5779 END IF;
5780 END IF;
5781 --SMukka Added this plsql block of code
5782 /* BEGIN
5783 PA_PERF_EXCP_UTILS.copy_object_rule_assoc
5784 (
5785 p_from_object_type =>'PA_PROJECTS'
5786 ,p_from_object_id =>x_orig_project_id
5787 ,p_to_object_type =>'PA_PROJECTS'
5788 ,p_to_object_id =>x_project_id
5789 ,x_msg_count =>l_msg_count
5790 ,x_msg_data =>l_msg_data
5791 ,x_return_status =>l_return_status
5792 );
5793 IF l_return_status <> 'S' THEN
5794 x_err_code := 905;
5795 x_err_stack := x_err_stack||'->PA_PERF_EXCP_UTILS.copy_object_rule_assoc';
5796 ROLLBACK TO copy_project;
5797 RETURN;
5798 END IF;
5799 EXCEPTION
5800 WHEN OTHERS THEN
5801 x_err_code := SQLCODE;
5802 x_err_stage := 'PA_PERF_EXCP_UTILS.copy_object_rule_assoc: '||SUBSTRB(SQLERRM,1,240);
5803 ROLLBACK TO copy_project;
5804 END;*/
5805
5806 /* Bug 3847507 : Shifted the foll. two calls to copy resource lists/rbs information to before
5807 the call for copy_structure
5808 --Copy resource lists from source to destination project
5809 BEGIN
5810 PA_CREATE_RESOURCE.Copy_Resource_Lists( p_source_project_id => x_orig_project_id
5811 ,p_destination_project_id => x_new_project_id
5812 ,x_return_status => l_return_status ) ;
5813 IF l_return_status <> 'S' THEN
5814 x_err_code := 905;
5815 x_err_stack := x_err_stack||'->PA_CREATE_RESOURCE.Copy_Resource_Lists';
5816 ROLLBACK TO copy_project;
5817 RETURN;
5818 END IF;
5819 EXCEPTION
5820 WHEN OTHERS THEN
5821 x_err_code := 905;
5822 x_err_stage := 'API: '||'PA_CREATE_RESOURCE.Copy_Resource_Lists'||
5823 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
5824 ROLLBACK TO copy_project;
5825 RETURN;
5826 END ;
5827 --Copy RBS associations from source to destination project
5828 BEGIN
5829 PA_RBS_ASGMT_PVT.Copy_Project_Assignment( p_rbs_src_project_id => x_orig_project_id
5830 ,p_rbs_dest_project_id => x_new_project_id
5831 ,x_return_status => l_return_status ) ;
5832 IF l_return_status <> 'S' THEN
5833 x_err_code := 920;
5834 x_err_stack := x_err_stack||'->PA_RBS_ASGMT_PUB.Copy_Project_Assignment';
5835 ROLLBACK TO copy_project;
5836 RETURN;
5837 END IF;
5838 EXCEPTION
5839 WHEN OTHERS THEN
5840 x_err_code := 920;
5841 x_err_stage := 'API: '||'PA_RBS_ASGMT_PUB.Copy_Project_Assignment'||
5842 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
5843 ROLLBACK TO copy_project;
5844 RETURN;
5845 END ; */
5846
5847 /* This code is moved to Copy_Project API for bug 4200168 so that BF can use attribute15 to access source task id in copy finplan API*/
5848
5849 /* Now update back the attributes column in pa_proj_elements and pa_proj_element_versions with actual data from source project */
5850 UPDATE pa_proj_elements ppe1
5851 SET attribute15 = ( SELECT attribute15 FROM pa_proj_elements ppe2
5852 WHERE ppe2.project_id = x_orig_project_id
5853 AND ppe2.proj_element_id = ppe1.attribute15 )
5854 WHERE project_id = x_new_project_id ;
5855
5856 UPDATE pa_proj_element_versions ppevs1
5857 SET attribute15 = ( SELECT attribute15 FROM pa_proj_element_versions ppevs2
5858 WHERE ppevs2.project_id = x_orig_project_id
5859 AND ppevs2.element_version_id = ppevs1.attribute15 )
5860 WHERE project_id = x_new_project_id ;
5861
5862 --Following code added for selective copy project changes. Tracking Bug no. 3464332
5863 --Delete all records from the global temporary table, used to store copy options in copy project
5864 DELETE FROM PA_PROJECT_COPY_OPTIONS_TMP;
5865
5866 --********************************************************
5867 --DO NOT ADD ANY CODE AFTER THIS CALL. bug 3163280
5868 --********************************************************
5869 --Call the process updates WBS api to kick-off concurrent program or online processing.
5870 x_err_stage := 'Calling PA_PROJ_TASK_STRUC_PUB.CALL_PROCESS_WBS_UPDATES API ...';
5871
5872 if (x_copy_task_flag = 'Y' ) then --no need to call for AMG. AMG has its own call.
5873
5874 BEGIN
5875 PA_PROJ_TASK_STRUC_PUB.CALL_PROCESS_WBS_UPDATES
5876 ( p_dest_project_id => x_new_project_id
5877 ,x_return_status => l_return_status
5878 ,x_msg_count => l_msg_count
5879 ,x_msg_data => l_msg_data);
5880
5881 if l_return_status <> 'S' then
5882 x_err_code := 1010;
5883 x_err_stage := pa_project_core1.get_message_from_stack( 'PA_ERR_PROCESS_WBS');
5884 x_err_stack := x_err_stack||'->PA_PROJ_TASK_STRUC_PUB.CALL_PROCESS_WBS_UPDATES';
5885 rollback to copy_project;
5886 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5887 return;
5888 end if;
5889 EXCEPTION WHEN OTHERS THEN
5890 x_err_code := 1010;
5891 x_err_stage := 'API: '||'PA_PROJ_TASK_STRUC_PUB.CALL_PROCESS_WBS_UPDATES'||
5892 ' SQL error message: '||SUBSTR( SQLERRM,1,1900);
5893 rollback to copy_project;
5894 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5895 return;
5896 END;
5897 end if;
5898 --********************************************************
5899 --DO NOT ADD ANY CODE AFTER THIS CALL. bug 3163280
5900 --********************************************************
5901
5902
5903 exception
5904 when others then
5905 x_err_code := SQLCODE;
5906 x_err_stage := SUBSTRB(SQLERRM,1,240);
5907 rollback to copy_project;
5908 revert_proj_number(x_proj_number_gen_mode,x_new_project_number); -- Added for Bug# 7445534
5909
5910 end copy_project;
5911
5912 /*********************************************************************
5913 ** Get_Next_Avail_Proj_Num
5914 ** Procedure to return the next available
5915 ** Project number for automatic project numbering.
5916 **
5917 ** Called when the new automatic project number generated is not unique.
5918 **
5919 ** Parameters :
5920 ** Start_proj_num - Project number(segment1) which was found to be
5921 ** non-unique. It starts from the next number.
5922 ** No_tries - The no of times it should loop around for checking
5923 ** the uniqueness of the project number.
5924 ** Next_proj_num - Next Project number
5925 ** x_error_code - 0 if unique number found in the tries specified
5926 ** 1 if unique number not found
5927 ** SQLCODE (< 0) if exception raised.
5928 ** x_err_stage - Message
5929 ** x_error_stack - The Satck of procedures.
5930 **
5931 ** Author - tsaifee
5932 **
5933 *********************************************************************/
5934 Procedure Get_Next_Avail_Proj_Num (
5935 Start_proj_num IN Number,
5936 No_tries IN Number,
5937 Next_proj_num IN OUT NOCOPY Number, --File.Sql.39 bug 4440895
5938 x_error_code IN OUT NOCOPY Number, --File.Sql.39 bug 4440895
5939 x_error_stack IN OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
5940 x_error_stage IN OUT NOCOPY Varchar2 ) --File.Sql.39 bug 4440895
5941
5942 IS
5943 Cursor C1(param_1 Number) IS
5944 select project_id
5945 from pa_projects_all
5946 where segment1 = to_char(param_1);
5947 loop_cnt number := 0;
5948 x_id number;
5949 old_stack varchar2(630);
5950
5951 BEGIN
5952
5953 old_stack := x_error_stack;
5954 x_error_code := 0;
5955 x_error_stack := x_error_stack || '->Get_Next_Avail_Num';
5956 next_proj_num := start_proj_num ;
5957
5958 LOOP
5959 next_proj_num := next_proj_num + 1;
5960 loop_cnt := loop_cnt + 1;
5961 Open C1(next_proj_num);
5962 Fetch C1 into x_id;
5963
5964 if (C1%NOTFOUND) then
5965 Close C1;
5966 /* Update the table with new-proj_num, because the unique
5967 Proj number proc will then return uniq identifier
5968 as the new proj number. */
5969
5970 UPDATE PA_UNIQUE_IDENTIFIER_CONTROL
5971 Set Next_Unique_Identifier = next_proj_num
5972 Where Table_Name = 'PA_PROJECTS';
5973 x_error_stack := old_stack;
5974 Return ;
5975 end if;
5976
5977 Close C1;
5978 if (loop_cnt = No_tries) then
5979 x_error_stage := 'PA_PR_EPR_PROJ_NAME_NOT_UNIQUE';
5980 x_error_code := 1;
5981 x_error_stack := old_stack;
5982 Return ;
5983 end if;
5984 END LOOP;
5985
5986 EXCEPTION
5987 When OTHERS then
5988 x_error_code := SQLCODE;
5989 Return ;
5990
5991 END Get_Next_Avail_Proj_Num;
5992
5993 -------------------------------------------------------------
5994 -- PROCEDURE : populate_copy_options
5995 -- PURPOSE : This API should be called to populate values for copy options into the global
5996 -- temporary table PA_PROJECT_COPY_OPTIONS_TMP
5997 -- PARAMETERS Type Required Description and Purpose
5998 -- ---------------------- ----------------------------- -------- ---------------------------------------------------
5999 -- p_context_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE Y The context of the record
6000 -- p_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE Y Value would be 'Y' or 'N'
6001 -- Context is a flag - Whether flag is selected or not
6002 -- Context is 'WOKPLAN' - Publish upon creation flag
6003 -- p_version_id_tbl SYSTEM.PA_NUM_TBL_TYP Y Context is a flag - NULL
6004 -- Context is 'WORKPLAN' - WP version id
6005 -- +------------------------------------------------+
6006 -- CONTEXT FLAG VERSION_ID
6007 -- +------------------------------------------------+
6008 -- <some flag> whether selected NULL
6009 -- WORKPLAN Pub upon creation WP version id
6010 -- WORKPLAN NULL NULL
6011 --Context = WORKPLAN and Version_id as NULL indicates that no workplan versions are selected
6012 --
6013 -- HISTORY
6014 -- 11-MAR-2004 sabansal Created
6015 PROCEDURE populate_copy_options( p_api_version IN NUMBER := 1.0
6016 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
6017 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
6018 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
6019 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
6020 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
6021 ,p_debug_mode IN VARCHAR2 := 'N'
6022 ,p_context_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE()
6023 ,p_flag_tbl IN SYSTEM.PA_VARCHAR2_1_TBL_TYPE := SYSTEM.PA_VARCHAR2_1_TBL_TYPE()
6024 ,p_version_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE()
6025 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
6026 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
6027 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
6028 ) IS
6029 l_debug_mode VARCHAR2(1);
6030 l_debug_level2 CONSTANT NUMBER := 2;
6031 l_debug_level3 CONSTANT NUMBER := 3;
6032 l_debug_level4 CONSTANT NUMBER := 4;
6033 l_debug_level5 CONSTANT NUMBER := 5;
6034 BEGIN
6035
6036 x_msg_count := 0;
6037 x_return_status := FND_API.G_RET_STS_SUCCESS;
6038 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
6039
6040 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
6041 FND_MSG_PUB.initialize;
6042 END IF;
6043
6044 IF (p_commit = FND_API.G_TRUE) THEN
6045 SAVEPOINT pop_copy_options;
6046 END IF;
6047
6048 IF l_debug_mode = 'Y' THEN
6049 PA_DEBUG.set_curr_function( p_function => 'populate_copy_options',
6050 p_debug_mode => l_debug_mode );
6051 END IF;
6052
6053 IF l_debug_mode = 'Y' THEN
6054 Pa_Debug.g_err_stage:= 'Printing Input parameters';
6055 IF nvl(p_context_tbl.LAST,0) > 0 THEN
6056 Pa_Debug.WRITE(g_module_name, Pa_Debug.g_err_stage, l_debug_level3);
6057 FOR i IN p_context_tbl.FIRST..p_context_tbl.LAST LOOP
6058 Pa_Debug.WRITE(g_module_name,'p_context_tbl('||i||')'||':'||p_context_tbl(i),
6059 l_debug_level3);
6060
6061 Pa_Debug.WRITE(g_module_name,'p_flag_tbl('||i||')'||':'||p_flag_tbl(i),
6062 l_debug_level3);
6063
6064 Pa_Debug.WRITE(g_module_name,'p_version_id_tbl('||i||')'||':'||p_version_id_tbl(i),
6065 l_debug_level3);
6066 END LOOP;
6067 ELSE
6068 Pa_Debug.WRITE(g_module_name,'## Context table doesnot contain any values! ##',
6069 l_debug_level3);
6070 END IF;
6071 END IF;
6072
6073 --First truncate the global temporary table
6074 DELETE FROM PA_PROJECT_COPY_OPTIONS_TMP;
6075
6076 --If the table passed is not NULL
6077 IF nvl(p_context_tbl.LAST,0) > 0 THEN
6078 --Populate the records in the global temporary table
6079 FOR i IN p_context_tbl.FIRST..p_context_tbl.LAST LOOP
6080 INSERT INTO PA_PROJECT_COPY_OPTIONS_TMP(
6081 CONTEXT
6082 ,FLAG
6083 ,VERSION_ID
6084 )
6085 VALUES(
6086 p_context_tbl(i)
6087 ,p_flag_tbl(i)
6088 ,p_version_id_tbl(i)
6089 );
6090 END LOOP;
6091 END IF;
6092
6093 IF (p_commit = FND_API.G_TRUE) THEN
6094 COMMIT;
6095 END IF;
6096
6097 EXCEPTION
6098 WHEN OTHERS THEN
6099 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
6100 x_msg_count := 1;
6101 x_msg_data := SQLERRM;
6102
6103 IF p_commit = FND_API.G_TRUE THEN
6104 ROLLBACK TO pop_copy_options;
6105 END IF;
6106
6107 Fnd_Msg_Pub.add_exc_msg
6108 ( p_pkg_name => 'PA_PROJECT_CORE1'
6109 , p_procedure_name => 'populate_copy_options'
6110 , p_error_text => x_msg_data);
6111
6112 IF l_debug_mode = 'Y' THEN
6113 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
6114 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
6115 l_debug_level5);
6116 Pa_Debug.reset_curr_function;
6117 END IF;
6118
6119 END populate_copy_options;
6120 ----------------------------------------
6121
6122
6123
6124
6125
6126 -- PROCEDURE : populate_default_copy_options
6127 -- PURPOSE : This API should be called to populate default values for copy options into the global
6128 -- temporary table PA_PROJECT_COPY_OPTIONS_TMP
6129 -- PARAMETERS Type Required Description and Purpose
6130 -- ---------------------- ------ -------- ---------------------------------------------------
6131 -- p_src_project_id NUMBER Y The source project id from which the default copy
6132 -- options would be populated
6133 -- p_src_template_flag VARCHAR2 Y Whether the source is a project or a template
6134 -- p_dest_template_flag VARCHAR2 Y Whether creating a project or a template
6135 PROCEDURE populate_default_copy_options( p_api_version IN NUMBER := 1.0
6136 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
6137 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
6138 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
6139 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
6140 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
6141 ,p_debug_mode IN VARCHAR2 := 'N'
6142 ,p_src_project_id IN NUMBER
6143 ,p_src_template_flag IN VARCHAR2
6144 ,p_dest_template_flag IN VARCHAR2
6145 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
6146 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
6147 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
6148 ) IS
6149 CURSOR cur_wp_record_present IS
6150 SELECT version_id
6151 FROM PA_PROJECT_COPY_OPTIONS_TMP ppcot
6152 WHERE context = 'WORKPLAN';
6153
6154 CURSOR cur_get_wp_attr IS
6155 SELECT ppwa.*
6156 FROM pa_proj_workplan_attr ppwa
6157 ,pa_proj_structure_types ppst
6158 ,pa_structure_types pst
6159 WHERE ppwa.project_id = p_src_project_id
6160 AND ppwa.proj_element_id = ppst.proj_element_id
6161 AND ppst.structure_type_id = pst.structure_type_id
6162 AND pst.structure_type = 'WORKPLAN' ;
6163
6164 --Cursor to retrieve all workplan versions from the source project
6165 CURSOR cur_get_wp_versions IS
6166 SELECT ppev.element_version_id, ppevs.status_code,
6167 ppevs.latest_eff_published_flag, ppevs.current_flag, ppevs.current_working_flag
6168 FROM pa_proj_element_versions ppev,
6169 pa_proj_structure_types ppst,
6170 pa_structure_types pst,
6171 pa_proj_elem_ver_structure ppevs
6172 WHERE ppev.project_id = p_src_project_id
6173 AND ppev.object_type = 'PA_STRUCTURES'
6174 AND ppev.element_version_id = ppevs.element_version_id
6175 AND ppevs.project_id = p_src_project_id
6176 AND ppev.proj_element_id = ppst.proj_element_id
6177 AND ppst.structure_type_id = pst.structure_type_id
6178 AND pst.structure_type = 'WORKPLAN' ;
6179
6180 l_debug_mode VARCHAR2(1);
6181 l_debug_level2 CONSTANT NUMBER := 2;
6182 l_debug_level3 CONSTANT NUMBER := 3;
6183 l_debug_level4 CONSTANT NUMBER := 4;
6184 l_debug_level5 CONSTANT NUMBER := 5;
6185
6186 l_workplan_enabled VARCHAR2(1) := 'N';
6187 l_wp_record_present NUMBER(15);
6188 l_shared VARCHAR2(1);
6189 l_versioning_enabled VARCHAR2(1);
6190 l_auto_pub_enabled VARCHAR2(1);
6191
6192 l_wp_attr_rec cur_get_wp_attr%ROWTYPE;
6193 rec_wp_versions cur_get_wp_versions%ROWTYPE;
6194 l_src_ltspub_or_cw_version NUMBER(15);
6195
6196 BEGIN
6197
6198 x_msg_count := 0;
6199 x_return_status := FND_API.G_RET_STS_SUCCESS;
6200 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
6201
6202 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_TRUE)) THEN
6203 FND_MSG_PUB.initialize;
6204 END IF;
6205
6206 IF (p_commit = FND_API.G_TRUE) THEN
6207 SAVEPOINT def_copy_options;
6208 END IF;
6209
6210 IF l_debug_mode = 'Y' THEN
6211 PA_DEBUG.set_curr_function( p_function => 'populate_default_copy_options',
6212 p_debug_mode => l_debug_mode );
6213 END IF;
6214
6215 IF l_debug_mode = 'Y' THEN
6216 Pa_Debug.g_err_stage:= 'Printing Input parameters';
6217 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
6218 l_debug_level3);
6219
6220 Pa_Debug.WRITE(g_module_name,'p_src_project_id'||':'||p_src_project_id,
6221 l_debug_level3);
6222
6223 Pa_Debug.WRITE(g_module_name,'p_src_template_flag'||':'||p_src_template_flag,
6224 l_debug_level3);
6225
6226 Pa_Debug.WRITE(g_module_name,'p_dest_template_flag'||':'||p_dest_template_flag,
6227 l_debug_level3);
6228 END IF;
6229
6230 IF l_debug_mode = 'Y' THEN
6231 Pa_Debug.g_err_stage:= 'Validating Input parameters';
6232 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
6233 l_debug_level3);
6234 END IF;
6235
6236 IF ( ( p_src_project_id IS NULL OR p_src_project_id = FND_API.G_MISS_NUM ) AND
6237 ( p_src_template_flag IS NULL OR p_src_template_flag = FND_API.G_MISS_CHAR )
6238 )
6239 THEN
6240 IF l_debug_mode = 'Y' THEN
6241 Pa_Debug.g_err_stage:= 'PA_PROJECT_CORE1 : populate_default_copy_options :
6242 p_src_project_id, p_src_template_flag are NULL';
6243 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
6244 l_debug_level3);
6245 END IF;
6246 RAISE Invalid_Arg_Exc;
6247 END IF;
6248
6249 --First, insert default values for flags, in case they are not there
6250 INSERT INTO PA_PROJECT_COPY_OPTIONS_TMP(
6251 CONTEXT
6252 ,FLAG
6253 ,VERSION_ID
6254 )
6255 SELECT
6256 lookup_code
6257 ,decode(lookup_code,'WP_INTER_PROJ_DEPEND_FLAG','N',decode(lookup_code,'PR_FRM_SRC_TMPL_FLAG','N','Y') )
6258 ,null
6259 FROM pa_lookups
6260 WHERE lookup_type = 'PA_COPY_OPTIONS'
6261 AND lookup_code NOT IN
6262 ( SELECT context
6263 FROM pa_project_copy_options_tmp
6264 WHERE context <> 'WORKPLAN'
6265 );
6266
6267 IF l_debug_mode = 'Y' THEN
6268 Pa_Debug.WRITE(g_module_name, 'Inserted default flag values', l_debug_level3);
6269 END IF;
6270
6271 l_workplan_enabled := PA_PROJECT_STRUCTURE_UTILS.check_workplan_enabled( p_src_project_id );
6272
6273 IF NVL( l_workplan_enabled, 'N' ) = 'Y' THEN
6274 l_shared := PA_PROJECT_STRUCTURE_UTILS.check_sharing_enabled(p_src_project_id);
6275 --Get workplan attributes
6276 OPEN cur_get_wp_attr;
6277 FETCH cur_get_wp_attr INTO l_wp_attr_rec;
6278 CLOSE cur_get_wp_attr;
6279
6280 l_versioning_enabled := l_wp_attr_rec.WP_ENABLE_VERSION_FLAG;
6281 l_auto_pub_enabled := l_wp_attr_rec.AUTO_PUB_UPON_CREATION_FLAG;
6282 ELSE
6283 l_shared := 'N';
6284 END IF;
6285
6286 OPEN cur_wp_record_present;
6287 FETCH cur_wp_record_present INTO l_wp_record_present;
6288
6289 --If workplan is enabled and there is no record for workplan in global temporary table, then populate
6290 --values for default workplan versions to be copied
6291 IF 'Y' = l_workplan_enabled AND cur_wp_record_present%NOTFOUND THEN
6292
6293 IF l_debug_mode = 'Y' THEN
6294 Pa_Debug.WRITE(g_module_name, 'Populating default workplan record(s)', l_debug_level3);
6295 END IF;
6296
6297 --If creating a PROJECT
6298 IF 'N' = p_dest_template_flag THEN
6299 --IF source is PROJECT with VERSIONING ENABLED
6300 -- Select LATEST PUBLISHED, else the CURRENT WORKING version from the source project
6301 -- Flag = 'N'
6302 --END IF;
6303 --IF source is TEMPLATE WITH VERSIONING ENABLED or TEMPLATE/PROJECT WITH VERSIONING DISABLED
6304 -- Select single WP version in the source.
6305 -- Flag = 'Y' if VERSIONING IS DISABLED or Flag = Auto Pub Upon Creation if TEMPLATE WITH
6306 -- VERSIONING ENABLED
6307 --END IF;
6308
6309 IF 'N' = p_src_template_flag AND 'Y' = l_versioning_enabled THEN
6310 FOR rec_wp_versions IN cur_get_wp_versions LOOP
6311 IF 'Y' = rec_wp_versions.latest_eff_published_flag THEN
6312 l_src_ltspub_or_cw_version := rec_wp_versions.element_version_id ;
6313 EXIT;
6314 END IF;
6315 END LOOP;
6316 IF l_src_ltspub_or_cw_version IS NULL THEN
6317 FOR rec_wp_versions IN cur_get_wp_versions LOOP
6318 IF 'Y' = rec_wp_versions.current_working_flag THEN
6319 l_src_ltspub_or_cw_version := rec_wp_versions.element_version_id;
6320 EXIT;
6321 END IF;
6322 END LOOP;
6323 END IF;
6324
6325 INSERT INTO PA_PROJECT_COPY_OPTIONS_TMP(
6326 CONTEXT
6327 ,FLAG
6328 ,VERSION_ID )
6329 VALUES(
6330 'WORKPLAN'
6331 ,'N' --Publish Upon Creation should be unchecked by default
6332 ,l_src_ltspub_or_cw_version );
6333 END IF;
6334
6335 --If source is TEMPLATE WITH VERSIONING ENABLED or TEMPLATE/PROJECT WITH VERSIONING DISABLED
6336 IF 'Y' = p_src_template_flag OR 'N' = l_versioning_enabled THEN
6337 --In this case, there would be only a SINGLE WP version in the source project
6338 --So, the following loop will execute only ONCE
6339 FOR rec_wp_versions IN cur_get_wp_versions LOOP
6340 INSERT INTO PA_PROJECT_COPY_OPTIONS_TMP(
6341 CONTEXT
6342 ,FLAG
6343 ,VERSION_ID )
6344 VALUES(
6345 'WORKPLAN'
6346 ,decode(l_versioning_enabled,'N','Y',l_auto_pub_enabled)
6347 ,rec_wp_versions.element_version_id ) ;
6348 END LOOP;
6349 --Note: In case of versioning disabled for source project, always populate flag as 'Y'
6350 END IF;
6351
6352 ELSE
6353 --IF creating a TEMPLATE
6354
6355 FOR rec_wp_versions IN cur_get_wp_versions LOOP
6356 IF 'Y' = rec_wp_versions.latest_eff_published_flag THEN
6357 l_src_ltspub_or_cw_version := rec_wp_versions.element_version_id ;
6358 EXIT;
6359 END IF;
6360 END LOOP;
6361 IF l_src_ltspub_or_cw_version IS NULL THEN
6362 FOR rec_wp_versions IN cur_get_wp_versions LOOP
6363 IF 'Y' = rec_wp_versions.current_working_flag THEN
6364 l_src_ltspub_or_cw_version := rec_wp_versions.element_version_id;
6365 EXIT;
6366 END IF;
6367 END LOOP;
6368 END IF;
6369
6370 INSERT INTO PA_PROJECT_COPY_OPTIONS_TMP(
6371 CONTEXT
6372 ,FLAG
6373 ,VERSION_ID )
6374 VALUES(
6375 'WORKPLAN'
6376 ,'N' --Publish Upon Creation should be unchecked by default
6377 ,l_src_ltspub_or_cw_version );
6378
6379 END IF;--IF destination is a project
6380 END IF;--IF 'Y' = l_workplan_enabled AND cur_wp_record_present%NOTFOUND
6381
6382 CLOSE cur_wp_record_present;
6383
6384 IF (p_commit = FND_API.G_TRUE) THEN
6385 COMMIT;
6386 END IF;
6387
6388 EXCEPTION
6389 WHEN Invalid_Arg_Exc THEN
6390 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
6391 x_msg_count := 1;
6392 x_msg_data := ' PA_PROJECT_CORE1 : populate_default_copy_options : NULL parameters passed';
6393
6394 IF p_commit = FND_API.G_TRUE THEN
6395 ROLLBACK TO def_copy_options;
6396 END IF;
6397
6398 Fnd_Msg_Pub.add_exc_msg
6399 ( p_pkg_name => 'PA_PROJECT_CORE1'
6400 , p_procedure_name => 'populate_default_copy_options'
6401 , p_error_text => x_msg_data);
6402
6403 IF l_debug_mode = 'Y' THEN
6404 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
6405 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
6406 l_debug_level5);
6407 Pa_Debug.reset_curr_function;
6408 END IF;
6409 RAISE;
6410
6411 WHEN OTHERS THEN
6412 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
6413 x_msg_count := 1;
6414 x_msg_data := SQLERRM;
6415
6416 IF p_commit = FND_API.G_TRUE THEN
6417 ROLLBACK TO def_copy_options;
6418 END IF;
6419
6420 Fnd_Msg_Pub.add_exc_msg
6421 ( p_pkg_name => 'PA_PROJECT_CORE1'
6422 , p_procedure_name => 'populate_default_copy_options'
6423 , p_error_text => x_msg_data);
6424
6425 IF l_debug_mode = 'Y' THEN
6426 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
6427 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
6428 l_debug_level5);
6429 Pa_Debug.reset_curr_function;
6430 END IF;
6431 RAISE;
6432
6433 END populate_default_copy_options;
6434
6435 END PA_PROJECT_CORE1 ;