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