[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FND_MSG_PUB.DELETE_MSG(p_msg_index => 1);
SELECT project_type,
project_status_code
FROM pa_projects
WHERE project_id = l_project_id;
SELECT expected_approval_date, projfunc_currency_code, project_currency_code
,target_start_date, target_finish_date, calendar_id --bug 2805602
FROM pa_projects
WHERE project_id = c_project_id;
SELECT opportunity_value, opp_value_currency_code
FROM pa_project_opp_attrs
WHERE project_id = c_project_id;
SELECT COMPLETION_DATE
FROM PA_PROJECTS_ALL
WHERE project_id=c_project_id;
SELECT wp_approval_reqd_flag, wp_auto_publish_flag, wp_approver_source_id, wp_approver_source_type, wp_default_display_lvl
FROM pa_proj_workplan_attr
WHERE project_id = c_project_id;
select org_information1 --org_information2
from hr_organization_information
--where org_information_context = 'Exp Organization Defaults'
where org_information_context = 'Resource Defaults'
and organization_id = p_org_id;
SELECT FLAG
FROM PA_PROJECT_COPY_OPTIONS_TMP
WHERE CONTEXT = p_flag_name ;
SELECT enable_top_task_inv_mth_flag, revenue_accrual_method, invoice_method
FROM pa_projects_all
WHERE project_id = x_orig_project_id;
select
ppa.funding_approval_status_code,
pps.project_system_status_code
from
pa_projects_all ppa,
pa_project_statuses pps
where
ppa.project_id = x_orig_project_id
and ppa.funding_approval_status_code = pps.project_status_code;
select 1
from pa_project_copy_overrides
where project_id = x_created_from_proj_id
and field_name = 'CUSTOMER_NAME';
select start_date,
template_flag,
created_from_project_id,
project_type,
carrying_out_organization_id,
initial_team_template_id,
baseline_funding_flag
from pa_projects
where project_id = x_orig_project_id;
select min(start_date) min_start
from pa_tasks
where project_id = x_orig_project_id;
SELECT 'X'
FROM pa_project_types
WHERE project_type = l_project_type
AND org_project_flag = 'Y';
cursor p1 is select project_system_status_code
from pa_project_statuses ps
where project_status_code = x_project_status_code;
cursor p2 is select ps.project_system_status_code, ps.starting_status_flag, ps.project_status_code
from pa_project_statuses ps, pa_projects pp
where ps.project_status_code = pp.project_status_code
and pp.project_id = x_orig_project_id;
cursor p3 is select ps.project_system_status_code, ps.starting_status_flag, ps.project_status_code
from pa_project_statuses ps, pa_project_types pt, pa_projects pp
where ps.project_status_code = pt.def_start_proj_status_code
and pt.project_type = pp.project_type
and pp.project_id = x_orig_project_id;
IF (fnd_function.test('PA_PAXPREPR_UPDATE_ORG') = TRUE) THEN
l_org_func_security := 'Y';
X_insert_update_mode => 'INSERT',
X_calling_module => 'PAXPREPR',
X_project_id => NULL,
X_task_id => NULL,
X_old_value => NULL,
X_new_value => l_organization_id,
X_project_type => l_project_type,
X_project_start_date => x_start_date,
X_project_end_date => x_completion_date,
X_public_sector_flag => x_public_sector_flag,
X_task_manager_person_id => NULL,
X_Service_type => NULL,
X_task_start_date => NULL,
X_task_end_date => NULL,
X_entered_by_user_id => FND_GLOBAL.USER_ID,
X_attribute_category => x_attribute_category,
X_attribute1 => x_attribute1,
X_attribute2 => x_attribute2,
X_attribute3 => x_attribute3,
X_attribute4 => x_attribute4,
X_attribute5 => x_attribute5,
X_attribute6 => x_attribute6,
X_attribute7 => x_attribute7,
X_attribute8 => x_attribute8,
X_attribute9 => x_attribute9,
X_attribute10 => x_attribute10,
X_pm_product_code => x_pm_product_code,
X_pm_project_reference => x_pm_project_reference,
X_pm_task_reference => NULL,
-- X_functional_security_flag => 'N', /* Bug#1968394 */
X_functional_security_flag => l_org_func_security, /* Bug#1968394 */
x_warnings_only_flag => l_warnings_only_flag, --bug3134205
X_err_code => x_err_code,
X_err_stage => x_err_stage,
X_err_stack => x_err_stack);
pa_locations_pkg.INSERT_ROW
( p_CITY => x_city,
p_REGION => x_region,
p_COUNTRY_CODE => x_country_code,
p_CREATION_DATE => sysdate,
p_CREATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
X_ROWID => l_rowid,
X_LOCATION_ID => l_location_id);
pa_locations_pkg.INSERT_ROW(
p_CITY => l_city,
p_REGION => l_region,
p_COUNTRY_CODE => l_country_code,
p_CREATION_DATE => sysdate,
p_CREATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_DATE => sysdate,
p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
p_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
X_ROWID => l_rowid,
X_LOCATION_ID => l_location_id);
select pa_projects_s.nextval from sys.dual;
select retn_accounting_flag from pa_implementations;
SELECT 'YES' FROM DUAL
WHERE EXISTS ( SELECT 1 FROM FND_DESCRIPTIVE_FLEXS
WHERE APPLICATION_ID=275
AND APPLICATION_TABLE_NAME='PA_PROJECTS_ALL'
AND DESCRIPTIVE_FLEXFIELD_NAME = 'PA_PROJECTS_DESC_FLEX'
AND CONTEXT_COLUMN_NAME = 'ATTRIBUTE_CATEGORY'
AND DEFAULT_CONTEXT_FIELD_NAME = 'TEMPLATE_FLAG' );
insert into pa_projects (
project_id,
name,
long_name, --long name changes
segment1,
org_id, -- Bug 4363092: MOAC Changes
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
project_type,
carrying_out_organization_id,
public_sector_flag,
project_status_code,
description,
start_date,
completion_date,
closed_date,
distribution_rule,
labor_invoice_format_id,
non_labor_invoice_format_id,
retention_invoice_format_id,
retention_percentage,
billing_offset,
billing_cycle_id,
labor_std_bill_rate_schdl,
labor_bill_rate_org_id,
labor_schedule_fixed_date,
labor_schedule_discount,
non_labor_std_bill_rate_schdl,
non_labor_bill_rate_org_id,
non_labor_schedule_fixed_date,
non_labor_schedule_discount,
limit_to_txn_controls_flag,
project_level_funding_flag,
invoice_comment,
unbilled_receivable_dr,
unearned_revenue_cr,
summary_flag,
enabled_flag,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
cost_ind_rate_sch_id,
rev_ind_rate_sch_id,
inv_ind_rate_sch_id,
cost_ind_sch_fixed_date,
rev_ind_sch_fixed_date,
inv_ind_sch_fixed_date,
labor_sch_type,
non_labor_sch_type,
template_flag,
verification_date,
created_from_project_id,
template_start_date_active,
template_end_date_active,
pm_product_code,
pm_project_reference,
actual_start_date,
actual_finish_date,
early_start_date,
early_finish_date,
late_start_date,
late_finish_date,
scheduled_start_date,
scheduled_finish_date,
project_currency_code,
allow_cross_charge_flag,
project_rate_date,
project_rate_type,
output_tax_code,
retention_tax_code,
cc_process_labor_flag,
labor_tp_schedule_id,
labor_tp_fixed_date,
cc_process_nl_flag,
nl_tp_schedule_id,
nl_tp_fixed_date,
cc_tax_task_id,
bill_job_group_id,
cost_job_group_id,
role_list_id,
work_type_id,
calendar_id,
initial_team_template_id,
location_id,
probability_member_id,
project_value,
expected_approval_date,
job_bill_rate_schedule_id,
emp_bill_rate_schedule_id,
--MCA Sakthi for MultiAgreementCurreny Project
competence_match_wt,
availability_match_wt,
job_level_match_wt,
enable_automated_search,
search_min_availability,
search_org_hier_id,
search_starting_org_id,
search_country_code,
min_cand_score_reqd_for_nom,
non_lab_std_bill_rt_sch_id,
invproc_currency_type,
revproc_currency_code,
project_bil_rate_date_code,
project_bil_rate_type,
project_bil_rate_date,
project_bil_exchange_rate,
projfunc_currency_code,
projfunc_bil_rate_date_code,
projfunc_bil_rate_type,
projfunc_bil_rate_date,
projfunc_bil_exchange_rate,
funding_rate_date_code,
funding_rate_type,
funding_rate_date,
funding_exchange_rate,
baseline_funding_flag,
projfunc_cost_rate_type,
projfunc_cost_rate_date,
multi_currency_billing_flag,
inv_by_bill_trans_curr_flag,
--MCA Sakthi for MultiAgreementCurreny Project
--MCA1
assign_precedes_task,
--MCA1
--Structure
split_cost_from_workplan_flag,
split_cost_from_bill_flag,
--Structure
--Advertisement, Project Setup and Retention changes
priority_code,
retn_billing_inv_format_id,
retn_accounting_flag,
adv_action_set_id,
start_adv_action_set_flag,
--Advertisement, Project Setup and Retention changes
-- anlee
-- Dates changes
target_start_date,
target_finish_date,
-- End of changes
-- anlee
-- patchset K changes
revaluate_funding_flag,
include_gains_losses_flag,
-- msundare
security_level,
labor_disc_reason_code,
non_labor_disc_reason_code,
-- End of changes
record_version_number,
btc_cost_base_rev_code, /* Bug#2638968 */
--PA L bug 2872708
asset_allocation_method,
capital_event_processing,
cint_rate_sch_id,
cint_eligible_flag,
--End PA L 2872708
structure_sharing_code , --FPM bug 3301192
/* Added for FPM development -Project Setup Changes */
enable_top_task_customer_flag,
enable_top_task_inv_mth_flag,
revenue_accrual_method,
invoice_method,
projfunc_attr_for_ar_flag,
sys_program_flag,
allow_multi_program_rollup,
proj_req_res_format_id,
proj_asgmt_res_format_id,
funding_approval_status_code, -- added for 4055319
revtrans_currency_type, -- Added for Bug 4757022
/* Added for FPM development -Project Setup Changes ends*/
--sunkalya:federal Bug#5511353
DATE_EFF_FUNDS_CONSUMPTION
--sunkalya:federal Bug#5511353
,ar_rec_notify_flag -- 7508661 : EnC
,auto_release_pwp_inv -- 7508661 : EnC
/* Added for 12.2 Payroll billing ER 11847616 */
,bill_labor_accrual
,adj_on_std_inv,
/* Added for 12.2 Payroll billing ER 11847616 */
cbs_version_id -- bug# 15834912
,cbs_enable_flag -- for CBS Phase 2 16083858
) select
x_new_project_id,
x_project_name,
NVL( x_long_name, x_project_name ), --long name changes
x_new_project_number,
t.org_id, -- Bug 4363092: MOAC Changes
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
t.project_type,
nvl(x_organization_id, t.carrying_out_organization_id),
nvl(x_public_sector_flag, t.public_sector_flag),
p_project_status_code,
nvl(x_description, t.description),
nvl(x_start_date, t.start_date),
nvl(x_completion_date, t.completion_date + x_delta),
p_closed_date,
--nvl(x_distribution_rule, t.distribution_rule),
nvl(l_new_distribution_rule, t.distribution_rule), --Bug 3279981 Review
t.labor_invoice_format_id,
t.non_labor_invoice_format_id,
t.retention_invoice_format_id,
t.retention_percentage,
t.billing_offset,
t.billing_cycle_id,
t.labor_std_bill_rate_schdl,
t.labor_bill_rate_org_id,
t.labor_schedule_fixed_date,
t.labor_schedule_discount,
t.non_labor_std_bill_rate_schdl,
t.non_labor_bill_rate_org_id,
t.non_labor_schedule_fixed_date,
t.non_labor_schedule_discount,
t.limit_to_txn_controls_flag,
-- t.project_level_funding_flag,
-- this values should not get copyied as no funding
-- information is getting copyied.
'',
t.invoice_comment,
-- Commented following two lines and replaced with NULL
-- for bug # 822580 fix
-- t.unbilled_receivable_dr,
-- t.unearned_revenue_cr,
NULL,
NULL,
t.summary_flag,
t.enabled_flag,
t.segment2,
t.segment3,
t.segment4,
t.segment5,
t.segment6,
t.segment7,
t.segment8,
t.segment9,
t.segment10,
-- Bug 2900258
/* decode(x_attribute_category, null,
t.attribute_category, x_attribute_category), */
/* decode(x_attribute_category, null,
decode(l_is_dff_reference_temp_flag,'YES','N',t.attribute_category), x_attribute_category),
decode(x_attribute_category, null, t.attribute1, x_attribute1),
decode(x_attribute_category, null, t.attribute2, x_attribute2),
decode(x_attribute_category, null, t.attribute3, x_attribute3),
decode(x_attribute_category, null, t.attribute4, x_attribute4),
decode(x_attribute_category, null, t.attribute5, x_attribute5),
decode(x_attribute_category, null, t.attribute6, x_attribute6),
decode(x_attribute_category, null, t.attribute7, x_attribute7),
decode(x_attribute_category, null, t.attribute8, x_attribute8),
decode(x_attribute_category, null, t.attribute9, x_attribute9),
decode(x_attribute_category, null, t.attribute10, x_attribute10),*/
/*Decode for l_pr_dff_flag added for selective copy project options. Tracking bug No 3464332*/
decode(l_pr_dff_flag,'Y',
decode(x_attribute_category, null,
decode(l_is_dff_reference_temp_flag,'YES',
DECODE(t.attribute_category,NULL,NULL,'N'),-- Added for Bug 5757594
t.attribute_category),
x_attribute_category),
null),
decode(l_pr_dff_flag,'Y', decode(x_attribute_category, null, t.attribute1, x_attribute1) ,null),
decode(l_pr_dff_flag,'Y', decode(x_attribute_category, null, t.attribute2, x_attribute2) ,null),
decode(l_pr_dff_flag,'Y', decode(x_attribute_category, null, t.attribute3, x_attribute3) ,null),
decode(l_pr_dff_flag,'Y', decode(x_attribute_category, null, t.attribute4, x_attribute4) ,null),
decode(l_pr_dff_flag,'Y', decode(x_attribute_category, null, t.attribute5, x_attribute5) ,null),
decode(l_pr_dff_flag,'Y', decode(x_attribute_category, null, t.attribute6, x_attribute6) ,null),
decode(l_pr_dff_flag,'Y', decode(x_attribute_category, null, t.attribute7, x_attribute7) ,null),
decode(l_pr_dff_flag,'Y', decode(x_attribute_category, null, t.attribute8, x_attribute8) ,null),
decode(l_pr_dff_flag,'Y', decode(x_attribute_category, null, t.attribute9, x_attribute9) ,null),
decode(l_pr_dff_flag,'Y', decode(x_attribute_category, null, t.attribute10, x_attribute10),null),
t.cost_ind_rate_sch_id,
t.rev_ind_rate_sch_id,
t.inv_ind_rate_sch_id,
t.cost_ind_sch_fixed_date,
t.rev_ind_sch_fixed_date,
t.inv_ind_sch_fixed_date,
t.labor_sch_type,
t.non_labor_sch_type,
decode(x_template_flag, 'Y', 'Y', 'N'),
null,
decode(x_template_flag,'Y',null,x_created_from_proj_id), --Bug:4709791
decode(x_template_flag,'Y', x_start_date, null),
decode(x_template_flag,'Y', x_completion_date, null),
x_pm_product_code,
x_pm_project_reference,
x_actual_start_date,
x_actual_finish_date,
x_early_start_date,
x_early_finish_date,
x_late_start_date,
x_late_finish_date,
-- anlee
-- Dates changes
x_scheduled_start_date,
x_scheduled_finish_date,
-- End of changes
NVL(x_project_currency_code,t.project_currency_code), /* 8297384 */
t.allow_cross_charge_flag,
t.project_rate_date,
t.project_rate_type,
t.output_tax_code,
t.retention_tax_code,
t.cc_process_labor_flag,
t.labor_tp_schedule_id,
t.labor_tp_fixed_date,
t.cc_process_nl_flag,
t.nl_tp_schedule_id,
t.nl_tp_fixed_date,
/* Bug # 2093089 : replaced cc_tax_task_id with NULL. */
/* Reverted the chages of 2093089 for bug # 2185521 */
/* Added decode for Bug 6248841 */
decode(nvl(x_copy_task_flag,'N'),'Y',t.cc_tax_task_id,NULL),
-- NULL,
t.bill_job_group_id,
t.cost_job_group_id,
t.role_list_id,
t.work_type_id,
/* t.calendar_id, commented for bug 2588244 */
/* Added nvl for bug 3185851 */
decode(l_flag, 'Y', t.calendar_id, 'N', nvl(l_cal_id, t.calendar_id)), /* decode added for bug 2588244 */
l_team_template_id,
nvl(l_location_id, t.location_id),
nvl(x_probability_member_id, t.probability_member_id),
nvl(x_project_value, t.project_value),
nvl(x_expected_approval_date, t.expected_approval_date),
t.job_bill_rate_schedule_id,
t.emp_bill_rate_schedule_id,
--MCA Sakthi for MultiAgreementCurreny Project
t.competence_match_wt,
t.availability_match_wt,
t.job_level_match_wt,
t.enable_automated_search,
t.search_min_availability,
t.search_org_hier_id,
t.search_starting_org_id,
t.search_country_code,
t.min_cand_score_reqd_for_nom,
t.non_lab_std_bill_rt_sch_id,
t.invproc_currency_type,
t.revproc_currency_code,
t.project_bil_rate_date_code,
t.project_bil_rate_type,
t.project_bil_rate_date,
t.project_bil_exchange_rate,
t.projfunc_currency_code,
t.projfunc_bil_rate_date_code,
t.projfunc_bil_rate_type,
t.projfunc_bil_rate_date,
t.projfunc_bil_exchange_rate,
t.funding_rate_date_code,
t.funding_rate_type,
t.funding_rate_date,
t.funding_exchange_rate,
t.baseline_funding_flag,
t.projfunc_cost_rate_type,
t.projfunc_cost_rate_date,
--MCA Sakthi for MultiAgreementCurreny Project
t.multi_currency_billing_flag,
t.inv_by_bill_trans_curr_flag,
--MCA
t.assign_precedes_task,
t.split_cost_from_workplan_flag,
t.split_cost_from_bill_flag,
--MCA
--Advertisement, Project Setup and Retention changes
nvl( x_priority_code, t.priority_code ),
t.retn_billing_inv_format_id,
l_retn_accounting_flag, --bugfix 2434241
t.adv_action_set_id,
t.start_adv_action_set_flag,
--Advertisement, Project Setup and Retention changes
-- anlee
-- Dates changes
nvl(x_start_date, t.target_start_date),
nvl(x_completion_date, t.target_finish_date + x_delta),
-- End of changes
-- anlee
-- patchset K changes
t.revaluate_funding_flag,
t.include_gains_losses_flag,
-- msundare
NVL( x_security_level, t.security_level ),
t.labor_disc_reason_code,
t.non_labor_disc_reason_code,
-- End of changes
1,
t.btc_cost_base_rev_code, /* bug#2638968 */
--PA L bug 2872708
t.asset_allocation_method,
t.capital_event_processing,
t.cint_rate_sch_id,
t.cint_eligible_flag,
--End PA L 2872708
t.structure_sharing_code, --FPM bug 3301192
/* Added for FPM development -Project Setup Changes Bug 3279981*/
decode(p_en_top_task_cust_flag, null, t.enable_top_task_customer_flag,
p_en_top_task_cust_flag) ,
decode(p_en_top_task_inv_mth_flag, null, t.enable_top_task_inv_mth_flag,
p_en_top_task_inv_mth_flag) ,
nvl(substr(x_distribution_rule, 1, instr(x_distribution_rule,'/')-1),t.revenue_accrual_method),
nvl(substr(x_distribution_rule, instr(x_distribution_rule,'/')+1),t.invoice_method),
t.projfunc_attr_for_ar_flag,
t.sys_program_flag,
t.allow_multi_program_rollup,
t.proj_req_res_format_id,
t.proj_asgmt_res_format_id,
l_fund_status, -- added for 4055319
t.revtrans_currency_type, -- Added for Bug 4757022
--sunkalya:federal Bug#5511353
decode(p_date_eff_funds_flag, null, nvl(t.DATE_EFF_FUNDS_CONSUMPTION,'N'), p_date_eff_funds_flag)
--sunkalya:federal Bug#5511353
,t.ar_rec_notify_flag -- 7508661 : EnC
,t.auto_release_pwp_inv -- 7508661 : EnC
/* Added for 12.2 Payroll billing ER 11847616 */
,t.bill_labor_accrual
,t.adj_on_std_inv,
/* Added for 12.2 Payroll billing ER 11847616 */
cbs_version_id -- bug# 15834912
,cbs_enable_flag -- for CBS Phase 2 16083858
from pa_projects t
where t.project_id = x_orig_project_id;
x_err_stage := 'Calling PA_PROJECT_CTX_SEARCH_PVT.Insert_Row API ...';
SELECT name, long_name, segment1, description, template_flag
FROM PA_PROJECTS_ALL
WHERE project_id = x_new_project_id;
PA_PROJECT_CTX_SEARCH_PVT.INSERT_ROW (
p_project_id => x_new_project_id
,p_template_flag => l_template_flag
,p_project_name => l_name
,p_project_number => l_number
,p_project_long_name => l_long_name
,p_project_description => l_description
,x_return_status => l_return_status );
x_err_stage := 'API: '||'PA_PROJECT_CTX_SEARCH_PVT.INSERT_ROW'||
' SQL error message: '||SUBSTR( SQLERRM,1,1900);
insert into pa_project_options (
project_id,
option_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login
) select
x_new_project_id,
o.option_code,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
from pa_project_options o
where o.project_id = x_created_from_proj_id;
insert into pa_project_copy_overrides (
project_id,
field_name,
display_name,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
limiting_value,
sort_order,
mandatory_flag,
record_version_number
) select
x_new_project_id,
c.field_name,
c.display_name,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
c.limiting_value,
c.sort_order,
c.mandatory_flag,
1
from pa_project_copy_overrides c
where c.project_id = x_created_from_proj_id;
SELECT name, description
FROM pa_project_subteams
WHERE object_type = 'PA_PROJECTS'
AND object_id = c_project_id;
SELECT DISTINCT
p.resource_source_id resource_source_id
, p.project_role_type project_role_type
, p.resource_type_id resource_type_id
, decode(r_delta, null, x_start_date, -- case B-2
start_date_active + r_delta) start_date_active -- A,C,D,B-1
, decode(r_delta, null, x_completion_date, -- case B-2
end_date_active + r_delta) end_date_active -- A,C,D,B-1
FROM pa_project_parties_v p
WHERE p.project_id = r_project_id
AND p.party_type not in ('ORGANIZATION');
SELECT DISTINCT
p.resource_source_id resource_source_id
, p.project_role_type project_role_type
, p.resource_type_id resource_type_id
, decode(r_delta, null, x_start_date,
start_date_active + r_delta) start_date_active
, decode(r_delta, null, x_completion_date,
end_date_active + r_delta) end_date_active
FROM pa_project_parties_v p
WHERE p.project_id = r_project_id
AND p.party_type not in ('ORGANIZATION')
AND p.project_role_type not in
(select distinct
limiting_value
from pa_project_copy_overrides
where project_id = x_created_from_proj_id
and field_name = 'KEY_MEMBER');*/
SELECT
p.resource_source_id resource_source_id
, r.project_role_type project_role_type
, p.resource_type_id resource_type_id
, decode(r_delta, null, x_start_date, -- case B-2
p.start_date_active + r_delta) start_date_active -- A,C,D,B-1
, decode(r_delta, null, x_completion_date, -- case B-2
p.end_date_active + r_delta) end_date_active -- A,C,D,B-1
FROM pa_project_parties p,PA_PROJECT_ROLE_TYPES_B R
WHERE p.project_id = r_project_id
AND p.project_role_id = r.project_role_id
AND r.role_party_class = 'PERSON';
SELECT
p.project_party_id project_party_id -- Bug 7482391
,p.resource_source_id resource_source_id
, r.project_role_type project_role_type
, p.resource_type_id resource_type_id
, decode(r_delta, null, x_start_date,
p.start_date_active + r_delta) start_date_active
, decode(r_delta, null, x_completion_date,
p.end_date_active + r_delta) end_date_active
, p.end_date_active source_end_date -- bug 12398459
FROM pa_project_parties p,PA_PROJECT_ROLE_TYPES_B R
WHERE p.project_id = r_project_id
AND p.project_role_id = r.project_role_id
AND r.role_party_class = 'PERSON'
AND r.project_role_type not in
(select distinct
limiting_value
from pa_project_copy_overrides
where project_id = x_created_from_proj_id
and field_name = 'KEY_MEMBER');
SELECT 'Y'
FROM hz_parties h
WHERE h.party_id = p_person_id
AND h.party_type = 'PERSON';
/*Following code added for selective copy project options. Tracking bug No 3464332*/
OPEN cur_get_flag('PR_TEAM_MEMBERS_FLAG');
select min(start_date_active) into tmp_min_strt_dt from pa_project_parties where project_id = x_orig_project_id;
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;
SELECT p.end_date_active + x_delta_1
INTO role_end_date
FROM pa_project_parties p,
pa_project_role_types_b r
WHERE p.project_id = x_orig_project_id
AND p.project_role_id = r.project_role_id
AND r.role_party_class = 'PERSON'
AND p.project_party_id = c_role_based_loop_rec_2.project_party_id
AND p.resource_source_id = c_role_based_loop_rec_2.resource_source_id
AND p.resource_type_id = c_role_based_loop_rec_2.resource_type_id
AND r.project_role_type = c_role_based_loop_rec_2.project_role_type
AND r.project_role_type NOT IN
(SELECT DISTINCT limiting_value
FROM pa_project_copy_overrides
WHERE project_id = x_created_from_proj_id
AND field_name = 'KEY_MEMBER');
SELECT
p.resource_source_id resource_source_id
, p.project_party_id project_party_id
, p.project_role_type project_role_type
, decode(r_delta, null, x_start_date, -- case B-2
start_date_active + r_delta) start_date_active -- A,C,D,B-1
, decode(r_delta, null, x_completion_date, -- case B-2
end_date_active + r_delta) end_date_active -- A,C,D,B-1
FROM pa_project_parties_v p
WHERE p.project_id = r_project_id
AND p.party_type = 'ORGANIZATION';
SELECT
p.resource_source_id resource_source_id
, p.project_party_id project_party_id
, p.project_role_type project_role_type
, decode(r_delta, null, x_start_date,
start_date_active + r_delta) start_date_active
, decode(r_delta, null, x_completion_date,
end_date_active + r_delta) end_date_active
FROM pa_project_parties_v p
WHERE p.project_id = r_project_id
AND p.party_type = 'ORGANIZATION'
AND p.project_role_type not in
(select distinct
limiting_value
from pa_project_copy_overrides
where project_id = x_created_from_proj_id
and field_name = 'ORG_ROLE');*/
SELECT
p.resource_source_id resource_source_id
, P.project_party_id project_party_id
, r.project_role_type project_role_type
, decode(r_delta, null, x_start_date, -- case B-2
p.start_date_active + r_delta) start_date_active -- A,C,D,B-1
, decode(r_delta, null, x_completion_date, -- case B-2
p.end_date_active + r_delta) end_date_active -- A,C,D,B-1
FROM pa_project_parties p,PA_PROJECT_ROLE_TYPES_B R
WHERE p.project_id = r_project_id
AND p.project_role_id = r.project_role_id
AND r.role_party_class <> 'PERSON';
SELECT
p.resource_source_id resource_source_id
, p.project_party_id project_party_id
, r.project_role_type project_role_type
, decode(r_delta, null, x_start_date,
p.start_date_active + r_delta) start_date_active
, decode(r_delta, null, x_completion_date,
p.end_date_active + r_delta) end_date_active
FROM pa_project_parties p, PA_PROJECT_ROLE_TYPES_B R
WHERE p.project_id = r_project_id
AND p.project_role_id = r.project_role_id
AND r.role_party_class <> 'PERSON'
AND r.project_role_type not in
(select distinct
limiting_value
from pa_project_copy_overrides
where project_id = x_created_from_proj_id
and field_name = 'ORG_ROLE');
SELECT 'Y'
FROM DUAL
WHERE EXISTS
(SELECT customer_id
FROM pa_project_customers
WHERE project_id = c_project_id
AND project_party_id = c_project_party_id);
INSERT INTO pa_project_classes (
project_id
, class_code
, class_category
, code_percentage
, object_id
, object_type
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
--below column added for bug2244929
, record_version_number)
SELECT
x_new_project_id
, pc.class_code
, pc.class_category
, pc.code_percentage
, x_new_project_id
, 'PA_PROJECTS'
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
, pc.attribute_category
, pc.attribute1
, pc.attribute2
, pc.attribute3
, pc.attribute4
, pc.attribute5
, pc.attribute6
, pc.attribute7
, pc.attribute8
, pc.attribute9
, pc.attribute10
, pc.attribute11
, pc.attribute12
, pc.attribute13
, pc.attribute14
, pc.attribute15
--below column added for bug 2244929
, pc.record_version_number
FROM
pa_project_classes pc
WHERE pc.project_id = x_orig_project_id;
INSERT INTO pa_project_classes (
project_id
, class_code
, class_category
, code_percentage
, object_id
, object_type
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
-- below column added for bug 2244929
, record_version_number)
SELECT
x_new_project_id
, pc.class_code
, pc.class_category
, pc.code_percentage
, x_new_project_id
, 'PA_PROJECTS'
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
, pc.attribute_category
, pc.attribute1
, pc.attribute2
, pc.attribute3
, pc.attribute4
, pc.attribute5
, pc.attribute6
, pc.attribute7
, pc.attribute8
, pc.attribute9
, pc.attribute10
, pc.attribute11
, pc.attribute12
, pc.attribute13
, pc.attribute14
, pc.attribute15
--below column added for bug 2244929
, pc.record_version_number
FROM
pa_project_classes pc
WHERE pc.project_id = x_orig_project_id
and pc.class_category not in (select distinct
limiting_value
from pa_project_copy_overrides
where project_id = x_created_from_proj_id
and field_name = 'CLASSIFICATION');
SELECT enable_top_task_customer_flag
FROM pa_projects_all
WHERE project_id = x_orig_project_id;
SELECT
nvl(DATE_EFF_FUNDS_CONSUMPTION,'N')
FROM
pa_projects_all
WHERE project_id = x_orig_project_id ;
INSERT INTO pa_project_customers (
project_id
, customer_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, project_relationship_code
, customer_bill_split
, bill_to_address_id
, ship_to_address_id
, inv_currency_code
, inv_rate_type
, inv_rate_date
, inv_exchange_rate
, allow_inv_user_rate_type_flag
, bill_another_project_flag
, receiver_task_id
, retention_level_code
, record_version_number
-- Customer Account Relationships changes
, bill_to_customer_id
, ship_to_customer_id
-- Customer Account Relationships changes
--Added for bug 3279981
, default_top_task_cust_flag )
SELECT
x_new_project_id
, cust.customer_id
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
, cust.project_relationship_code
--Bug 3279981
--, cust.customer_bill_split
, decode(l_check_diff_flag, --sunkalya:federal Bug#5511353
NULL , cust.customer_bill_split ,
'Y', null ,
'N', decode(l_hghst_ctr_cust_id, cust.customer_id, 100, 0),
cust.customer_bill_split )
, cust.bill_to_address_id
, cust.ship_to_address_id
, cust.inv_currency_code
, cust.inv_rate_type
, cust.inv_rate_date
, cust.inv_exchange_rate
, cust.allow_inv_user_rate_type_flag
, cust.bill_another_project_flag
, cust.receiver_task_id
, cust.retention_level_code
, 1
-- Customer Account Relationships changes
, bill_to_customer_id
, ship_to_customer_id
-- Customer Account Relationships changes
--Added for bug 3279981
, decode(p_en_top_task_cust_flag,
l_orig_en_top_task_cust, cust.default_top_task_cust_flag,
'Y', decode(l_hghst_ctr_cust_id, cust.customer_id, 'Y', 'N'),
'N','N',
cust.default_top_task_cust_flag
)
FROM
pa_project_customers cust
WHERE cust.project_id = x_orig_project_id;
SELECT customer_id
FROM PA_PROJECT_CUSTOMERS
WHERE project_id = c_project_id;
SELECT PARTY_ID
FROM PA_CUSTOMERS_V
WHERE CUSTOMER_ID = c_customer_id
AND PARTY_TYPE = 'ORGANIZATION';
UPDATE PA_PROJECT_CUSTOMERS
SET project_party_id = l_project_party_id
WHERE project_id = x_new_project_id
AND customer_id = l_customer_id;
INSERT INTO pa_project_customers (
project_id
, customer_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, project_relationship_code
, customer_bill_split
, bill_to_address_id
, ship_to_address_id
, inv_currency_code
, inv_rate_type
, inv_rate_date
, inv_exchange_rate
, allow_inv_user_rate_type_flag
, bill_another_project_flag
, receiver_task_id
, record_version_number
---Customer Account Relationship
, bill_to_customer_id
, ship_to_customer_id)
---Customer Account Relationship
SELECT
x_new_project_id
, cust.customer_id
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
, cust.project_relationship_code
, cust.customer_bill_split
, cust.bill_to_address_id
, cust.ship_to_address_id
, cust.inv_currency_code
, cust.inv_rate_type
, cust.inv_rate_date
, cust.inv_exchange_rate
, cust.allow_inv_user_rate_type_flag
, cust.bill_another_project_flag
, cust.receiver_task_id
, 1
---Customer Account Relationship
, cust.bill_to_customer_id
, cust.ship_to_customer_id
---Customer Account Relationship
FROM
pa_project_customers cust
WHERE cust.project_id = x_orig_project_id
and not exists
(select null
from pa_project_copy_overrides
where project_id = x_created_from_proj_id
and field_name = 'CUSTOMER_NAME');
SELECT
cust.customer_id
, cust.project_relationship_code
, cust.customer_bill_split
, cust.bill_to_address_id
, cust.ship_to_address_id
, cust.inv_currency_code
, cust.inv_rate_type
, cust.inv_rate_date
, cust.inv_exchange_rate
, cust.allow_inv_user_rate_type_flag
, cust.bill_another_project_flag
, cust.receiver_task_id
, cust.bill_to_customer_id
, cust.ship_to_customer_id
, cust.default_top_task_cust_flag
FROM
pa_project_customers cust
WHERE cust.project_id = c_project_id
and not exists -- Added the and condition for bug 3726109
(select null
from pa_project_copy_overrides
where project_id = c_created_from_proj_id
and field_name = 'CUSTOMER_NAME');
select contact.customer_id,
contact.contact_id,
contact.project_contact_type_code,
contact.bill_ship_customer_id
from pa_project_contacts contact
where contact.project_id = c_project_id
and not exists
(select null
from pa_project_copy_overrides
where project_id = c_created_from_proj_id
and field_name = 'CUSTOMER_NAME')
and contact.customer_id = c_customer_id;
select 'Y'
from pa_project_copy_overrides
where project_id = c_created_from_proj_id
and field_name = 'CUSTOMER_NAME';
so that only one record gets inserted at a time and primary
key violations do not occur **/
/*Bug#4770535. parameters to l_bill_to_contact_id ,l_ship_to_contact_id ,l_copy_bill_to_contact_id,
l_copy_ship_to_contact_idare set to NULL so that no primary key violations occur */
l_bill_to_contact_id := NULL;
SELECT customer_id
FROM PA_PROJECT_CUSTOMERS
WHERE project_id = c_project_id
AND not exists
(select null
from pa_project_copy_overrides
where project_id = x_created_from_proj_id
and field_name = 'CUSTOMER_NAME');
SELECT PARTY_ID
FROM PA_CUSTOMERS_V
WHERE CUSTOMER_ID = c_customer_id
AND PARTY_TYPE = 'ORGANIZATION';
UPDATE PA_PROJECT_CUSTOMERS
SET project_party_id = l_project_party_id
WHERE project_id = x_new_project_id
AND customer_id = l_customer_id;
INSERT INTO pa_project_contacts (
project_id
, customer_id
, contact_id
, project_contact_type_code
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, record_version_number
-- Customer Account relationships
, bill_ship_customer_id)
SELECT
x_new_project_id
, c.customer_id
, c.contact_id
, c.project_contact_type_code
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
, 1
-- Customer Account relationships
, c.bill_ship_customer_id
FROM
pa_project_contacts c
WHERE c.project_id = x_orig_project_id
and c.customer_id in
(select customer_id from pa_project_customers
where project_id = x_new_project_id);
INSERT INTO pa_project_contacts (
project_id
, customer_id
, contact_id
, project_contact_type_code
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, record_version_number
-- Customer Account relationships
, bill_ship_customer_id)
SELECT
x_new_project_id
, c.customer_id
, c.contact_id
, c.project_contact_type_code
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
, 1
-- Customer Account relationships
, c.bill_ship_customer_id
FROM
pa_project_customers cust,
pa_project_contacts c
WHERE c.project_id = x_orig_project_id
and c.project_contact_type_code NOT IN ('BILLING','SHIPPING') -- added for bug 8415966
and c.customer_id in
(select customer_id from pa_project_customers
where project_id = x_new_project_id)
and c.project_id = cust.project_id
and c.customer_id = cust.customer_id
and not exists
(select null
from pa_project_copy_overrides
where project_id = x_created_from_proj_id
and field_name = 'CUSTOMER_NAME');
INSERT INTO pa_cost_dist_overrides (
COST_DISTRIBUTION_OVERRIDE_ID
, project_id
, OVERRIDE_TO_ORGANIZATION_ID
, start_date_active
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, person_id
, EXPENDITURE_CATEGORY
, OVERRIDE_FROM_ORGANIZATION_ID
, END_DATE_ACTIVE)
SELECT
pa_cost_dist_overrides_s.nextval
, x_new_project_id
, OVERRIDE_TO_ORGANIZATION_ID
, decode(x_delta, null, x_start_date,
start_date_active + x_delta)
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
, person_id
, EXPENDITURE_CATEGORY
, OVERRIDE_FROM_ORGANIZATION_ID
, decode(x_delta, null, x_completion_date,
end_date_active + x_delta)
FROM
pa_cost_dist_overrides
WHERE project_id = x_orig_project_id;
INSERT INTO pa_credit_receivers (
PERSON_ID
, CREDIT_TYPE_CODE
, project_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, START_DATE_ACTIVE
, CREDIT_PERCENTAGE
, END_DATE_ACTIVE
, TRANSFER_TO_AR_FLAG
, CREDIT_RECEIVER_ID
, RECORD_VERSION_NUMBER
, SALESREP_ID)
SELECT
PERSON_ID
, CREDIT_TYPE_CODE
, x_new_project_id
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
, decode(x_delta, null, x_start_date,
start_date_active + x_delta)
, CREDIT_PERCENTAGE
, decode(x_delta, null, x_completion_date,
end_date_active + x_delta)
, TRANSFER_TO_AR_FLAG
, pa_credit_receivers_s.NEXTVAL
, RECORD_VERSION_NUMBER
, SALESREP_ID
FROM
pa_credit_receivers
WHERE project_id = x_orig_project_id
and task_id is null;
INSERT INTO pa_billing_assignments (
BILLING_ASSIGNMENT_ID,
BILLING_EXTENSION_ID,
PROJECT_TYPE,
PROJECT_ID,
TOP_TASK_ID,
AMOUNT,
PERCENTAGE,
ACTIVE_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
RECORD_VERSION_NUMBER,
DISTRIBUTION_RULE,
/* Added columns for bug#2658340 */
ORG_ID,
RATE_OVERRIDE_CURRENCY_CODE,
PROJECT_CURRENCY_CODE,
PROJECT_RATE_TYPE,
PROJECT_RATE_DATE,
PROJECT_EXCHANGE_RATE,
PROJFUNC_CURRENCY_CODE,
PROJFUNC_RATE_TYPE,
PROJFUNC_RATE_DATE,
PROJFUNC_EXCHANGE_RATE,
FUNDING_RATE_TYPE,
FUNDING_RATE_DATE,
FUNDING_EXCHANGE_RATE)
select
pa_billing_assignments_s.nextval,
BILLING_EXTENSION_ID,
project_type,
x_new_project_id,
null,
AMOUNT,
PERCENTAGE,
ACTIVE_FLAG,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
RECORD_VERSION_NUMBER,
null, /* Bug#2663786 - Distribution should be inserted as null, commented line below.
nvl(x_DISTRIBUTION_RULE, DISTRIBUTION_RULE), */
/* Added columns for bug#2658340 */
ORG_ID,
RATE_OVERRIDE_CURRENCY_CODE,
PROJECT_CURRENCY_CODE,
PROJECT_RATE_TYPE,
PROJECT_RATE_DATE,
PROJECT_EXCHANGE_RATE,
PROJFUNC_CURRENCY_CODE,
PROJFUNC_RATE_TYPE,
PROJFUNC_RATE_DATE,
PROJFUNC_EXCHANGE_RATE,
FUNDING_RATE_TYPE,
FUNDING_RATE_DATE,
FUNDING_EXCHANGE_RATE
from pa_billing_assignments
where project_id = x_orig_project_id
and top_task_id is null;
INSERT INTO pa_ind_rate_schedules (
IND_RATE_SCH_ID,
IND_RATE_SCH_NAME,
BUSINESS_GROUP_ID,
DESCRIPTION,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
COST_PLUS_STRUCTURE,
IND_RATE_SCHEDULE_TYPE,
PROJECT_ID,
TASK_ID,
COST_OVR_SCH_FLAG,
REV_OVR_SCH_FLAG,
INV_OVR_SCH_FLAG,
ORGANIZATION_STRUCTURE_ID,
ORG_STRUCTURE_VERSION_ID,
START_ORGANIZATION_ID, --Added these three columns for bug 2581491
IND_RATE_SCH_USAGE --bug 3053508
)
select
pa_ind_rate_schedules_s.nextval,
-- to_char(x_new_project_id) ||
-- substr(s.ind_rate_sch_name,
-- instr(s.ind_rate_sch_name, '-', -1)),
SUBSTR((TO_CHAR(x_new_project_id) ||
DECODE(INSTR(s.ind_rate_sch_name, '-', -1),'0','-') ||
SUBSTR(s.ind_rate_sch_name,
INSTR(s.ind_rate_sch_name, '-', -1))),1,30), -- Added for bug 3911182.
s.business_group_id,
s.DESCRIPTION,
decode(x_delta, null, x_start_date,
s.start_date_active + x_delta),
decode(x_delta, null, x_completion_date,
s.end_date_active + x_delta),
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID,
s.COST_PLUS_STRUCTURE,
s.IND_RATE_SCHEDULE_TYPE,
x_new_project_id,
null,
s.COST_OVR_SCH_FLAG,
s.REV_OVR_SCH_FLAG,
s.INV_OVR_SCH_FLAG,
s.ORGANIZATION_STRUCTURE_ID,
s.ORG_STRUCTURE_VERSION_ID,
s.START_ORGANIZATION_ID , --Added these three columns for bug 2581491
s.IND_RATE_SCH_USAGE --bug 3053508
FROM
pa_ind_rate_schedules s
WHERE s.project_id = x_orig_project_id
and s.task_id is null;
insert into pa_ind_rate_sch_revisions (
IND_RATE_SCH_REVISION_ID,
IND_RATE_SCH_ID,
IND_RATE_SCH_REVISION,
IND_RATE_SCH_REVISION_TYPE,
COMPILED_FLAG,
COST_PLUS_STRUCTURE,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
COMPILED_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
READY_TO_COMPILE_FLAG,
ACTUAL_SCH_REVISION_ID,
ORGANIZATION_STRUCTURE_ID,
ORG_STRUCTURE_VERSION_ID,
START_ORGANIZATION_ID) --Added these three columns for bug 2581491
select
pa_ind_rate_sch_revisions_s.nextval,
new_sch.ind_rate_sch_id,
rev.IND_RATE_SCH_REVISION,
rev.IND_RATE_SCH_REVISION_TYPE,
'N',
rev.COST_PLUS_STRUCTURE,
decode(x_delta, null, x_start_date,
rev.start_date_active + x_delta),
decode(x_delta, null, x_completion_date,
rev.end_date_active + x_delta),
null,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID,
rev.REQUEST_ID,
NULL,
NULL,
NULL,
'Y',
NULL,
rev.ORGANIZATION_STRUCTURE_ID,
rev.ORG_STRUCTURE_VERSION_ID,
rev.START_ORGANIZATION_ID --Added these three columns for bug 2581491
from pa_ind_rate_sch_revisions rev,
pa_ind_rate_schedules old_sch,
pa_ind_rate_schedules new_sch
where old_sch.project_id = x_orig_project_id
and old_sch.ind_rate_sch_id = rev.IND_RATE_SCH_ID
and old_sch.task_id is null
and new_sch.project_id = x_new_project_id
and new_sch.task_id is null
and substr(new_sch.ind_rate_sch_name, -- added for bug 4213251
decode(instr(new_sch.ind_rate_sch_name, '-', -1), 0 , 0,
instr(new_sch.ind_rate_sch_name, '-', -1)+1))
= substr(old_sch.ind_rate_sch_name,
decode(instr(old_sch.ind_rate_sch_name, '-', -1), 0 , 0,
instr(old_sch.ind_rate_sch_name, '-', -1)+1));
insert into pa_ind_cost_multipliers (
IND_RATE_SCH_REVISION_ID,
ORGANIZATION_ID,
IND_COST_CODE,
MULTIPLIER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN )
select
new_rev.IND_RATE_SCH_REVISION_ID,
mult.ORGANIZATION_ID,
mult.IND_COST_CODE,
mult.MULTIPLIER,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
from pa_ind_cost_multipliers mult,
pa_ind_rate_sch_revisions old_rev,
pa_ind_rate_sch_revisions new_rev,
pa_ind_rate_schedules old_sch,
pa_ind_rate_schedules new_sch
where old_rev.IND_RATE_SCH_REVISION_ID =
mult.IND_RATE_SCH_REVISION_ID
and old_rev.IND_RATE_SCH_REVISION =
new_rev.IND_RATE_SCH_REVISION
and old_sch.ind_rate_sch_id = old_rev.IND_RATE_SCH_ID
and new_sch.ind_rate_sch_id = new_rev.IND_RATE_SCH_ID
and old_sch.project_id = x_orig_project_id
and old_sch.task_id is null
and new_sch.project_id = x_new_project_id
and new_sch.task_id is null
and substr(new_sch.ind_rate_sch_name, -- added for bug 4213251
decode(instr(new_sch.ind_rate_sch_name, '-', -1), 0 , 0,
instr(new_sch.ind_rate_sch_name, '-', -1)+1))
= substr(old_sch.ind_rate_sch_name,
decode(instr(old_sch.ind_rate_sch_name, '-', -1), 0 , 0,
instr(old_sch.ind_rate_sch_name, '-', -1)+1));
INSERT INTO pa_transaction_controls (
project_id
, start_date_active
, chargeable_flag
, billable_indicator
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, person_id
, expenditure_category
, expenditure_type
, non_labor_resource
, scheduled_exp_only
, end_date_active
/*Added for FPM Changes for Project Setup */
, workplan_res_only_flag
, employees_only_flag)
SELECT
x_new_project_id
, decode(x_delta, null, x_start_date,
tc.start_date_active + x_delta)
, tc.chargeable_flag
, tc.billable_indicator
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
, tc.person_id
, tc.expenditure_category
, tc.expenditure_type
, tc.non_labor_resource
, tc.scheduled_exp_only
, decode(x_delta, null, x_completion_date,
tc.end_date_active + x_delta)
/*Added for FPM Changes for Project Setup */
, tc.workplan_res_only_flag
, tc.employees_only_flag
FROM
pa_transaction_controls tc
WHERE
tc.project_id = x_orig_project_id
AND tc.task_id IS NULL;
INSERT INTO pa_project_assets (
PROJECT_ASSET_ID,
PROJECT_ID,
ASSET_NUMBER,
ASSET_NAME,
ASSET_DESCRIPTION,
LOCATION_ID,
ASSIGNED_TO_PERSON_ID,
DATE_PLACED_IN_SERVICE,
ASSET_CATEGORY_ID,
ASSET_KEY_CCID,
BOOK_TYPE_CODE,
ASSET_UNITS,
DEPRECIATE_FLAG,
DEPRECIATION_EXPENSE_CCID,
CAPITALIZED_FLAG,
ESTIMATED_IN_SERVICE_DATE,
CAPITALIZED_COST,
GROUPED_CIP_COST,
AMORTIZE_FLAG,
COST_ADJUSTMENT_FLAG,
CAPITALIZED_DATE,
REVERSE_FLAG,
REVERSAL_DATE,
NEW_MASTER_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
--PA L Changes 2872708
--CAPITAL_EVENT_ID, --do not copy capital event bug 2946015
--FA_PERIOD_NAME,
--PM_PRODUCT_CODE,
--PM_ASSET_REFERENCE,
ESTIMATED_COST,
ESTIMATED_ASSET_UNITS,
MANUFACTURER_NAME,
MODEL_NUMBER,
--TAG_NUMBER,
--SERIAL_NUMBER,
RET_TARGET_ASSET_ID,
PROJECT_ASSET_TYPE,
PARENT_ASSET_ID,
--FA_ASSET_ID,
CAPITAL_HOLD_FLAG,
--end PA L Changes 2872708
ORG_ID --R12: MOAC changes: Bug 4363092
)
select
pa_project_assets_s.nextval,
x_new_PROJECT_ID,
NULL,
ASSET_NAME,
ASSET_DESCRIPTION,
decode(x_orig_template_flag, 'Y', LOCATION_ID, NULL), -- NULL, bug 3433295
NULL,
NULL,
ASSET_CATEGORY_ID,
ASSET_KEY_CCID,
BOOK_TYPE_CODE,
ASSET_UNITS,
DEPRECIATE_FLAG,
DEPRECIATION_EXPENSE_CCID,
'N',
decode(ESTIMATED_IN_SERVICE_DATE, null, null,
decode(x_delta, null, x_start_date,
ESTIMATED_IN_SERVICE_DATE + x_delta)),
0,
0,
AMORTIZE_FLAG,
'N',
NULL,
'N',
NULL,
'N',
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
--PA L chanegs 2872708
--CAPITAL_EVENT_ID, --do not copy capital event bug 2946015
--FA_PERIOD_NAME, --do not copy. please refer bug 2948307
--PM_PRODUCT_CODE,
--PM_ASSET_REFERENCE,
ESTIMATED_COST,
ESTIMATED_ASSET_UNITS,
MANUFACTURER_NAME,
MODEL_NUMBER,
--TAG_NUMBER,
--SERIAL_NUMBER,
RET_TARGET_ASSET_ID,
DECODE( PROJECT_ASSET_TYPE, 'AS-BUILT', 'ESTIMATED', PROJECT_ASSET_TYPE ),
--bug 2872708 refer *** MAANSARI 04/17/03 11:19 am ***
PARENT_ASSET_ID,
--FA_ASSET_ID,
decode( x_orig_template_flag, 'Y', CAPITAL_HOLD_FLAG, 'N', 'N' ),
--end PA L chanegs 2872708
org_id --R12: MOAC changes: Bug 4363092
from pa_project_assets
where project_id = x_orig_project_id;
select orig.project_asset_id orig_project_asset_id,
new.project_asset_id new_project_asset_id
from pa_project_assets orig, pa_project_assets new
where orig.project_id = x_orig_project_id
and new.asset_name = orig.asset_name
and new.project_id = x_new_project_id ;
INSERT INTO pa_project_asset_assignments (
PROJECT_ASSET_ID,
TASK_ID,
PROJECT_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
-- changed to remove bug#604496 :ashia bagai 30-dec-97
-- added UNION to remove bug#604496 : ashia bagai 30-dec-97
-- Common Cost asset assignments would have an asset id = 0
-- and hence would not have a relevant record in pa_project_assets
/* select
new_asset.PROJECT_ASSET_ID,
0,
new_asset.PROJECT_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
from pa_project_asset_assignments assign,
pa_project_assets old_asset,
pa_project_assets new_asset
where old_asset.project_id = x_orig_project_id
and old_asset.project_asset_id =
assign.project_asset_id
and assign.task_id = 0
and old_asset.asset_name = new_asset.asset_name
and new_asset.project_id = x_new_project_id;
select
new_asset.PROJECT_ASSET_ID,
0,
new_asset.PROJECT_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
from pa_project_asset_assignments assign,
pa_project_assets old_asset,
pa_project_assets new_asset
where old_asset.project_id = x_orig_project_id
and old_asset.project_asset_id =
assign.project_asset_id
and assign.task_id = 0
and old_asset.asset_name = new_asset.asset_name
and new_asset.project_id = x_new_project_id
UNION
select
PROJECT_ASSET_ID,
0,
x_new_project_id,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
from pa_project_asset_assignments
where project_id = x_orig_project_id
and task_id = 0
and project_asset_id = 0;
INSERT INTO pa_resource_list_assignments (
RESOURCE_LIST_ASSIGNMENT_ID,
RESOURCE_LIST_ID,
PROJECT_ID,
RESOURCE_LIST_CHANGED_FLAG,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN )
select
pa_resource_list_assignments_s.nextval,
RESOURCE_LIST_ID,
x_new_project_id,
'N',
FND_GLOBAL.USER_ID,
sysdate,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
from pa_resource_list_assignments a,
pa_resource_list_uses u
where a.project_id = x_orig_project_id
and a.resource_list_assignment_id =
u.resource_list_assignment_id
and u.use_code = 'ACTUALS_ACCUM';
INSERT INTO pa_resource_list_uses (
RESOURCE_LIST_ASSIGNMENT_ID,
USE_CODE,
DEFAULT_FLAG,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN )
select
new_list.RESOURCE_LIST_ASSIGNMENT_ID,
use.USE_CODE,
use.Default_Flag,
FND_GLOBAL.USER_ID,
sysdate,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
from pa_resource_list_uses use,
pa_resource_list_assignments old_list,
pa_resource_list_assignments new_list
where old_list.project_id = x_orig_project_id
and old_list.RESOURCE_LIST_ASSIGNMENT_ID =
use.RESOURCE_LIST_ASSIGNMENT_ID
and use.use_code = 'ACTUALS_ACCUM'
and old_list.resource_list_id =
new_list.resource_list_id
and new_list.project_id = x_new_project_id;
INSERT INTO pa_job_bill_rate_overrides (
JOB_ID
, START_DATE_ACTIVE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, RATE
, BILL_RATE_UNIT
, PROJECT_ID
, TASK_ID
--MCB Chanes
, RATE_CURRENCY_CODE
, JOB_BILL_RATE_OVERRIDE_ID
, RECORD_VERSION_NUMBER
--MCB Chanes
, END_DATE_ACTIVE
, DISCOUNT_PERCENTAGE
, RATE_DISC_REASON_CODE )
SELECT
JOB_ID
, decode(x_delta, null, x_start_date,
start_date_active + x_delta)
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
, RATE
, BILL_RATE_UNIT
, x_new_project_id
, null
--MCB Chanes
, RATE_CURRENCY_CODE
, pa_job_bill_rate_overrides_s.NEXTVAL
, RECORD_VERSION_NUMBER
--MCB Chanes
, decode(x_delta, null, x_completion_date,
end_date_active + x_delta)
, DISCOUNT_PERCENTAGE
, RATE_DISC_REASON_CODE
FROM
pa_job_bill_rate_overrides
WHERE project_id = x_orig_project_id
and task_id is null;
INSERT INTO pa_job_bill_title_overrides (
JOB_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, START_DATE_ACTIVE
, BILLING_TITLE
, PROJECT_ID
, TASK_ID
, JOB_BILL_TITLE_OVERRIDE_ID
, RECORD_VERSION_NUMBER
, END_DATE_ACTIVE )
SELECT
JOB_ID
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
, decode(x_delta, null, x_start_date,
start_date_active + x_delta)
, BILLING_TITLE
, x_new_project_id
, null
, pa_job_bill_title_overrides_s.NEXTVAL
, RECORD_VERSION_NUMBER
, decode(x_delta, null, x_completion_date,
end_date_active + x_delta)
FROM
pa_job_bill_title_overrides
WHERE project_id = x_orig_project_id
and task_id is null;
INSERT INTO pa_job_assignment_overrides (
PERSON_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, START_DATE_ACTIVE
, PROJECT_ID
, TASK_ID
, JOB_ID
, BILLING_TITLE
, JOB_ASSIGNMENT_OVERRIDE_ID
, RECORD_VERSION_NUMBER
, END_DATE_ACTIVE )
SELECT
PERSON_ID
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
, decode(x_delta, null, x_start_date,
start_date_active + x_delta)
, x_new_project_id
, null
, JOB_ID
, BILLING_TITLE
, pa_job_assignment_overrides_s.NEXTVAL
, RECORD_VERSION_NUMBER
, decode(x_delta, null, x_completion_date,
end_date_active + x_delta)
FROM
pa_job_assignment_overrides
WHERE project_id = x_orig_project_id
and task_id is null;
INSERT INTO pa_emp_bill_rate_overrides (
PERSON_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, RATE
, BILL_RATE_UNIT
, START_DATE_ACTIVE
, PROJECT_ID
, TASK_ID
--MCB Chanes
, RATE_CURRENCY_CODE
, EMP_BILL_RATE_OVERRIDE_ID
, RECORD_VERSION_NUMBER
--MCB Chanes
, END_DATE_ACTIVE
, DISCOUNT_PERCENTAGE
, RATE_DISC_REASON_CODE)
SELECT
PERSON_ID
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
, RATE
, BILL_RATE_UNIT
, decode(x_delta, null, x_start_date,
start_date_active + x_delta)
, x_new_project_id
, null
--MCB Chanes
, RATE_CURRENCY_CODE
, pa_emp_bill_rate_overrides_s.NEXTVAL
, RECORD_VERSION_NUMBER
--MCB Chanes
, decode(x_delta, null, x_completion_date,
end_date_active + x_delta)
, DISCOUNT_PERCENTAGE
, RATE_DISC_REASON_CODE
FROM
pa_emp_bill_rate_overrides
WHERE project_id = x_orig_project_id
and task_id is null;
INSERT INTO pa_nl_bill_rate_overrides (
EXPENDITURE_TYPE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, START_DATE_ACTIVE
, NON_LABOR_RESOURCE
, MARKUP_PERCENTAGE
, BILL_RATE
, PROJECT_ID
, TASK_ID
--MCB Chanes
, RATE_CURRENCY_CODE
, NL_BILL_RATE_OVERRIDE_ID
, RECORD_VERSION_NUMBER
--MCB Chanes
, END_DATE_ACTIVE
, DISCOUNT_PERCENTAGE
, RATE_DISC_REASON_CODE )
SELECT
EXPENDITURE_TYPE
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
, decode(x_delta, null, x_start_date,
start_date_active + x_delta)
, NON_LABOR_RESOURCE
, MARKUP_PERCENTAGE
, BILL_RATE
, x_new_project_id
, null
--MCB Chanes
, RATE_CURRENCY_CODE
, pa_nl_bill_rate_overrides_s.NEXTVAL
, RECORD_VERSION_NUMBER
--MCB Chanes
, decode(x_delta, null, x_completion_date,
end_date_active + x_delta)
, DISCOUNT_PERCENTAGE
, RATE_DISC_REASON_CODE
FROM
pa_nl_bill_rate_overrides
WHERE project_id = x_orig_project_id
and task_id is null;
INSERT INTO pa_labor_multipliers (
PROJECT_ID
, TASK_ID
, LABOR_MULTIPLIER
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LABOR_MULTIPLIER_ID
, RECORD_VERSION_NUMBER
, LAST_UPDATE_LOGIN )
SELECT
x_new_project_id
, null
, labor_multiplier
, decode(x_delta, null, x_start_date,
start_date_active + x_delta)
, decode(x_delta, null, x_completion_date,
end_date_active + x_delta)
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.USER_ID
, pa_labor_multipliers_s.NEXTVAL
, RECORD_VERSION_NUMBER
, FND_GLOBAL.LOGIN_ID
FROM pa_labor_multipliers
WHERE project_id = x_orig_project_id
and task_id is null;
/*Following code added for selective copy project options. Tracking bug No 3464332*/
OPEN cur_get_flag('PR_ATTACHMENTS_FLAG');
SELECT distinct 'Y'
FROM PA_PROJECT_COPY_OPTIONS_TMP
WHERE CONTEXT = 'WORKPLAN'
AND VERSION_ID IS NOT NULL;
select enable_top_task_customer_flag ,
enable_top_task_inv_mth_flag
Into l_enable_top_task_cust_flag,
l_enable_top_task_inv_mth_flag
from pa_projects
where project_id = x_new_project_id;
Update pa_tasks
set customer_id =x_customer_id
where project_id=x_new_project_id;
Update pa_tasks t
Set t.customer_id = (select old.customer_id from pa_tasks old
where old.project_id = x_orig_project_id
and old.task_number = t.task_number
and old.customer_id is not null)
where t.project_id = x_new_project_id;
Update pa_tasks t
Set t.customer_id = (select old.customer_id from pa_tasks old
where old.project_id = x_orig_project_id
and old.task_number = t.task_number
and old.customer_id is not null)
where t.project_id = x_new_project_id;*/
Update pa_tasks t
set t.revenue_accrual_method =substr(x_distribution_rule, 1, instr(x_distribution_rule,'/')-1),
t.invoice_method = substr(x_distribution_rule, instr(x_distribution_rule,'/')+1)
where t.project_id=x_new_project_id;
Update pa_tasks t
set t.revenue_accrual_method =(select old.revenue_accrual_method
from pa_tasks old
where old.project_id = x_orig_project_id
and old.task_number = t.task_number
and old.revenue_accrual_method is not null),
t.invoice_method = (select old.invoice_method
from pa_tasks old
where old.project_id = x_orig_project_id
and old.task_number = t.task_number
and old.invoice_method is not null)
where t.project_id = x_new_project_id;
/*Following code added for selective copy project options. Tracking bug No 3464332*/
OPEN cur_get_flag('PR_ITEM_ASSOC_FLAG');
/*Following code and IF condition added for selective copy project options. Tracking bug No 3464332*/
OPEN cur_get_flag('PR_USER_DEFN_ATT_FLAG');
x_err_stage := 'PSI Project List-Insert Accum';
pa_accum_proj_list.Insert_Accum
( p_project_id => x_new_project_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
x_err_stage := pa_project_core1.get_message_from_stack('PA_ERR_INSERT_ACCUM');
x_err_stack := x_err_stack||'->pa_accum_proj_list.Insert_Accum';
x_err_stage := 'API: '||'pa_accum_proj_list.Insert_Accum'||' SQL error message: '||SUBSTR( SQLERRM,1,1900);
select 1
from pa_project_copy_overrides
where project_id = x_created_from_proj_id
and field_name = 'CUSTOMER_NAME';
select 'Y' into l_baseline_exists_in_src
from pa_budget_versions pbv
where pbv.project_id = x_orig_project_id
and pbv.budget_status_code = 'B'
and (budget_type_code='AR'
or APPROVED_REV_PLAN_TYPE_FLAG = 'Y') -- Added for bug 6857315
and rownum <=1;
select orig.proj_element_id orig_task_id,
new.proj_element_id new_task_id
from pa_proj_elements orig, pa_proj_elements new
where orig.project_id = x_orig_project_id
and new.element_number = orig.element_number
and new.project_id = x_new_project_id
and new.object_type = 'PA_TASKS'
and orig.object_type = 'PA_TASKS';
UPDATE pa_projects
SET wf_status_code = 'IN_ROUTE'
WHERE project_id = x_new_project_id;
/* Now update back the attributes column in pa_proj_elements and pa_proj_element_versions with actual data from source project */
UPDATE pa_proj_elements ppe1
SET attribute15 = ( SELECT attribute15 FROM pa_proj_elements ppe2
WHERE ppe2.project_id = x_orig_project_id
AND ppe2.proj_element_id = ppe1.attribute15 )
WHERE project_id = x_new_project_id ;
UPDATE pa_proj_element_versions ppevs1
SET attribute15 = ( SELECT attribute15 FROM pa_proj_element_versions ppevs2
WHERE ppevs2.project_id = x_orig_project_id
AND ppevs2.element_version_id = ppevs1.attribute15 )
WHERE project_id = x_new_project_id ;
DELETE FROM PA_PROJECT_COPY_OPTIONS_TMP;
x_err_stage := 'Calling PA_PROJ_TASK_STRUC_PUB.CALL_PROCESS_WBS_UPDATES API ...';
PA_PROJ_TASK_STRUC_PUB.CALL_PROCESS_WBS_UPDATES
( p_dest_project_id => x_new_project_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
x_err_stack := x_err_stack||'->PA_PROJ_TASK_STRUC_PUB.CALL_PROCESS_WBS_UPDATES';
x_err_stage := 'API: '||'PA_PROJ_TASK_STRUC_PUB.CALL_PROCESS_WBS_UPDATES'||
' SQL error message: '||SUBSTR( SQLERRM,1,1900);
select project_id
from pa_projects_all
where segment1 = to_char(param_1);
/* Update the table with new-proj_num, because the unique
Proj number proc will then return uniq identifier
as the new proj number. */
UPDATE PA_UNIQUE_IDENTIFIER_CONTROL
Set Next_Unique_Identifier = next_proj_num
Where Table_Name = 'PA_PROJECTS';
DELETE FROM PA_PROJECT_COPY_OPTIONS_TMP;
INSERT INTO PA_PROJECT_COPY_OPTIONS_TMP(
CONTEXT
,FLAG
,VERSION_ID
)
VALUES(
p_context_tbl(i)
,p_flag_tbl(i)
,p_version_id_tbl(i)
);
SELECT version_id
FROM PA_PROJECT_COPY_OPTIONS_TMP ppcot
WHERE context = 'WORKPLAN';
SELECT ppwa.*
FROM pa_proj_workplan_attr ppwa
,pa_proj_structure_types ppst
,pa_structure_types pst
WHERE ppwa.project_id = p_src_project_id
AND ppwa.proj_element_id = ppst.proj_element_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type = 'WORKPLAN' ;
SELECT ppev.element_version_id, ppevs.status_code,
ppevs.latest_eff_published_flag, ppevs.current_flag, ppevs.current_working_flag
FROM pa_proj_element_versions ppev,
pa_proj_structure_types ppst,
pa_structure_types pst,
pa_proj_elem_ver_structure ppevs
WHERE ppev.project_id = p_src_project_id
AND ppev.object_type = 'PA_STRUCTURES'
AND ppev.element_version_id = ppevs.element_version_id
AND ppevs.project_id = p_src_project_id
AND ppev.proj_element_id = ppst.proj_element_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type = 'WORKPLAN' ;
INSERT INTO PA_PROJECT_COPY_OPTIONS_TMP(
CONTEXT
,FLAG
,VERSION_ID
)
SELECT
lookup_code
,decode(lookup_code,'WP_INTER_PROJ_DEPEND_FLAG','N',decode(lookup_code,'PR_FRM_SRC_TMPL_FLAG','N','Y') )
,null
FROM pa_lookups
WHERE lookup_type = 'PA_COPY_OPTIONS'
AND lookup_code NOT IN
( SELECT context
FROM pa_project_copy_options_tmp
WHERE context <> 'WORKPLAN'
);
Pa_Debug.WRITE(g_module_name, 'Inserted default flag values', l_debug_level3);
INSERT INTO PA_PROJECT_COPY_OPTIONS_TMP(
CONTEXT
,FLAG
,VERSION_ID )
VALUES(
'WORKPLAN'
,'N' --Publish Upon Creation should be unchecked by default
,l_src_ltspub_or_cw_version );
INSERT INTO PA_PROJECT_COPY_OPTIONS_TMP(
CONTEXT
,FLAG
,VERSION_ID )
VALUES(
'WORKPLAN'
,decode(l_versioning_enabled,'N','Y',l_auto_pub_enabled)
,rec_wp_versions.element_version_id ) ;
INSERT INTO PA_PROJECT_COPY_OPTIONS_TMP(
CONTEXT
,FLAG
,VERSION_ID )
VALUES(
'WORKPLAN'
,'N' --Publish Upon Creation should be unchecked by default
,l_src_ltspub_or_cw_version );