[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select 'X'
from pa_lookups
where lookup_type='PM_PRODUCT_CODE'
and lookup_code = p_pm_product_code;
SELECT DISTINCT *
FROM pa_override_fields_v pof
WHERE pof.pa_source_template_id = p_pa_source_template_id;
SELECT 'x' FROM
pa_project_role_types
WHERE project_role_type = p_role_type; */
SELECT 'x'
FROM pa_project_role_types_v roles,
pa_role_controls controls,
pa_projects_all proj
WHERE proj.project_id = p_project_id
AND proj.ROLE_LIST_ID is null
AND roles.project_role_id = controls.project_role_id
AND roles.PROJECT_ROLE_TYPE = p_role_type
AND controls.role_control_code = 'ALLOW_AS_PROJ_MEMBER'
AND trunc(sysdate) between start_date_active and nvl(end_date_active,sysdate)
UNION ALL
SELECT 'x'
FROM pa_project_role_types_v roles,
pa_role_controls controls,
pa_projects_all proj,
pa_role_list_members prlm
WHERE proj.project_id = p_project_id
AND proj.ROLE_LIST_ID = prlm.role_list_id
AND prlm.project_role_id = roles.project_role_id
AND roles.project_role_id = controls.project_role_id
AND roles.PROJECT_ROLE_TYPE = p_role_type
AND controls.role_control_code = 'ALLOW_AS_PROJ_MEMBER'
AND trunc(sysdate) between start_date_active and nvl(end_date_active,sysdate);
select count(*)
from pa_project_players
where project_id = p_project_id
and project_role_type = 'PROJECT MANAGER'
and (( nvl(end_date_active,nvl(p_start_date,sysdate)) between
nvl(p_start_date,sysdate) and nvl(p_end_date,sysdate))
OR
(start_date_active between nvl(p_start_date,sysdate) and nvl(p_end_date,sysdate)));
SELECT segment1,COMPLETION_DATE
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
INSERT INTO pa_project_players
(project_id
,person_id
,project_role_type
,start_date_active
,end_date_active
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login )
VALUES
( p_project_id
,p_key_members(i).person_id
,p_key_members(i).project_role_type
,DECODE(p_key_members(i).start_date,
PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
TRUNC(SYSDATE),
NULL,TRUNC(SYSDATE),
p_key_members(i).start_date)
,p_key_members(i).end_date
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id );
/* Select
DECODE(p_key_members(i).start_date,
PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE, TRUNC(SYSDATE),
NULL,TRUNC(SYSDATE),
p_key_members(i).start_date)
Into v_start_date_active
From dual;
Select project_role_id
Into v_project_role_id
From pa_project_role_types
Where project_role_type=p_key_members(i).project_role_type;
SELECT DISTINCT *
FROM pa_override_fields_v pof
WHERE pof.pa_source_template_id = p_pa_source_template_id;
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
SELECT 'Y'
FROM PA_VALID_CATEGORIES_V vc,
PA_PROJECTS_ALL ppa,
PA_PROJECT_TYPES_ALL ppta
WHERE ppa.project_id = c_project_id
AND ppa.project_type = ppta.project_type
AND nvl(ppa.org_id, -99) = nvl(ppta.org_id, -99)
AND ppta.project_type_id = vc.object_type_id
AND vc.class_category = c_class_category; */
SELECT 'Y'
FROM DUAL
WHERE EXISTS
(
SELECT 'Y'
FROM PA_CLASS_CATEGORIES cc,
PA_VALID_CATEGORIES vc,
PA_PROJECT_TYPES_ALL PPTA,
PA_PROJECTS_ALL PPA
WHERE PPA.PROJECT_ID = c_project_id
AND PPA.PROJECT_TYPE = PPTA.PROJECT_TYPE
-- AND nvl(PPA.ORG_ID, -99) = nvl(PPTA.ORG_ID, -99) - R12: Bug 4364092
AND PPA.ORG_ID = PPTA.ORG_ID
AND VC.CLASS_CATEGORY = c_class_category
AND VC.CLASS_CATEGORY = cc.CLASS_CATEGORY
AND TRUNC(SYSDATE) BETWEEN TRUNC(CC.START_DATE_ACTIVE)
AND TRUNC(NVL(CC.END_DATE_ACTIVE, SYSDATE))
AND VC.OBJECT_TYPE_ID = PPTA.PROJECT_TYPE_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(PPTA.START_DATE_ACTIVE)
AND TRUNC(NVL(PPTA.END_DATE_ACTIVE, SYSDATE))
) ;
SELECT 'Y'
FROM DUAL
WHERE EXISTS
(
SELECT 'Y'
FROM PA_CLASS_CATEGORIES cc
WHERE CC.OBJECT_TYPE = 'PA_PROJECTS'
AND CC.ALL_TYPES_VALID_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(CC.START_DATE_ACTIVE)
AND TRUNC(NVL(CC.END_DATE_ACTIVE, SYSDATE))
AND cc.CLASS_CATEGORY = c_class_category
) ;
SELECT 'Y'
FROM PA_CLASS_CATEGORIES
WHERE class_category = c_class_category
AND allow_percent_flag = 'Y';
SELECT allow_percent_flag
FROM PA_CLASS_CATEGORIES
WHERE class_category = c_class_category;
SELECT PICK_ONE_CODE_ONLY_FLAG
FROM PA_CLASS_CATEGORIES
WHERE class_category = c_class_category;
select count(*)
from PA_PROJECT_CLASSES
WHERE Project_id = c_project_id
AND class_category = c_class_category;
IF l_field_is_overridable <> 1 --if not overridable error else insert
THEN
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
pa_interface_utils_pub.map_new_amg_msg
( p_old_message_code => 'PA_CLASS_CAT_NOT_OVERRIDABLE'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'Y'
,p_msg_context => 'PROJ'
,p_attribute1 => l_amg_segment1
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
/* dbms_output.put_line('Trying to insert'); */
INSERT INTO pa_project_classes
(project_id
,class_category
,class_code
,code_percentage
,object_id
,object_type
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,record_version_number /* Added For bug#2766416 */
)
VALUES
( p_project_id
,p_class_categories(i).class_category
,p_class_categories(i).class_code
,l_code_percentage
,p_project_id
,'PA_PROJECTS'
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,0 /* Added for bug#2766416 */
);
/* check if after insertion the constraint is being violated or not */
i := p_class_categories.first;
SELECT customer_id
FROM pa_project_customers
WHERE project_id = p_project_id;
SELECT 'x'
FROM ra_addresses
WHERE customer_id = p_customer_id
AND address_id = p_address_id;
SELECT 'x'
FROM
hz_cust_acct_sites_all acct_site
WHERE
acct_site.cust_account_id = p_customer_id
AND acct_site.cust_acct_site_id = p_address_id;
SELECT 'x'
FROM pa_lookups
WHERE lookup_type = 'SERVICE TYPE'
AND lookup_code = p_service_type_code;
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
SELECT allow_cross_charge_flag,
project_rate_date,
project_rate_type,
cc_process_labor_flag,
labor_tp_schedule_id,
labor_tp_fixed_date,
cc_process_nl_flag,
nl_tp_schedule_id,
nl_tp_fixed_date,
work_type_id,
emp_bill_rate_schedule_id,
job_bill_rate_schedule_id,
projfunc_cost_rate_type,
projfunc_cost_rate_date,
non_lab_std_bill_rt_sch_id
--Sakthi MCB
/*
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_scr_id,
invproc_currency_type,
revproc_currency_code,
project_bil_rate_date_code,
project_bil_rate_type,
project_bil_rate_date,
project_bil_exchange_rate,
profunc_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,
inv_by_bill_trans_curr_flag,
multi_currency_billing_flag,
--Sakthi MCB
--Sakthi Structure
assign_precedes_task,
split_cost_from_workplan_flag,
split_cost_from_bill_flag
--Sakthi Structure
*/
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
SELECT ind_rate_schedule_type
FROM pa_ind_rate_schedules
WHERE ind_rate_sch_id = p_cost_ind_rate_sch_id;
SELECT revenue_accrual_method
FROM pa_projects_all
WHERE project_id = p_project_rec.project_id;
SELECT invoice_method
FROM pa_projects_all
WHERE project_id = p_project_rec.project_id;
SELECT customer_id
FROM pa_project_customers
WHERE project_id = p_project_rec.project_id
AND default_top_task_cust_flag = 'Y';
select gen_etc_source_code
from pa_tasks
where
task_id = l_task_id
and project_id = l_project_id ;
SELECT t.TOP_TASK_ID
FROM pa_tasks t
WHERE t.project_id = p_project_id
AND t.pm_task_reference = p_pm_task_reference;
SELECT t.TOP_TASK_ID
FROM pa_tasks t
WHERE t.project_id = p_project_id
AND t.task_id = p_task_id;
SELECT customer_id FROM pa_tasks WHERE task_id=l_p_parent_task_id;
SELECT parent_task_id
INTO l_pa_parent_task_id
FROM pa_tasks
WHERE task_id = l_task_in_rec.pa_task_id
AND project_id =l_project_id;
SELECT parent_task_id
INTO l_pa_parent_task_id
FROM pa_tasks
WHERE task_number = l_task_in_rec.pa_task_number
AND project_id =l_project_id;
/* select adj_on_std_inv into l_adj_on_std_inv from pa_projects_all where project_id =p_project_rec.project_id ;
select adj_on_std_inv into l_adj_on_std_inv from pa_tasks where task_id =l_task_in_rec.pa_parent_task_id;
(PA_PROJECT_PUB.G_MASS_ADD_TASKS = 'Y' OR PA_PROJECT_PUB.G_MASS_UPDATE_TASKS = 'Y') AND
(PA_PROJECT_PUB.G_FP_DFF_TBL.COUNT > 0)) THEN
i := PA_PROJECT_PUB.G_FP_DFF_TBL.FIRST;
SELECT pa_tasks_s.nextval
INTO l_task_id
FROM DUAL;
INSERT INTO pa_tasks
( task_id
,project_id
,pm_task_reference
,task_number
,task_name
,long_task_name
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,wbs_level
,top_task_id
,parent_task_id
,address_id
,ready_to_bill_flag
,ready_to_distribute_flag
,billable_flag
,chargeable_flag
,limit_to_txn_controls_flag
,description
,service_type_code
,task_manager_person_id
,carrying_out_organization_id
,start_date
,completion_date
,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
,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
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,allow_cross_charge_flag
,project_rate_type
,project_rate_date
,pm_product_code
,actual_start_date
,actual_finish_date
,early_start_date
,early_finish_date
,late_start_date
,late_finish_date
,scheduled_start_date
,scheduled_finish_date
,cc_process_labor_flag
,labor_tp_schedule_id
,labor_tp_fixed_date
,cc_process_nl_flag
,nl_tp_schedule_id
,nl_tp_fixed_date
,receive_project_invoice_flag
,work_type_id
,emp_bill_rate_schedule_id
,job_bill_rate_schedule_id
,non_lab_std_bill_rt_sch_id
,taskfunc_cost_rate_type
,taskfunc_cost_rate_date
,labor_disc_reason_code
,non_labor_disc_reason_code
--PA L 2872708
,retirement_cost_flag
,cint_eligible_flag
,cint_stop_date
--PA L 2872708
--(Begin Venkat) FP_M changes --------------------------------
,revenue_accrual_method
,invoice_method
,customer_id
,gen_etc_source_code
--(End Venkat) FP_M changes --------------------------------
--(Begin Rtarway)
, LABOR_COST_MULTIPLIER_NAME
, RECORD_VERSION_NUMBER
--(End rtarway)
,adj_on_std_inv /* Added for 12.2Payroll billing ER 11899223 */
)
VALUES
( l_task_id
,l_project_id
,rtrim(l_task_in_rec.pm_task_reference)
,substrb(rtrim(l_task_in_rec.pa_task_number),1,25) -- bug 6193314 . added substrb
,substrb(rtrim(l_task_in_rec.task_name),1,20) -- bug 6193314 . added substrb
,rtrim(l_task_in_rec.long_task_name)
,sysdate
,fnd_global.user_id
,sysdate
,fnd_global.user_id
,fnd_global.login_id
,l_wbs_level --wbs_level will be updated in the 2 round.
,l_parent_TOP_TASK_ID -- top task id is updated in this round itself Bug 4378507
--,l_task_id -- Commented for Bug 4378507 top task id will be updated in the 2 round.
,null --parent task id, will be updated in the 2 round
,l_address_id
,l_task_in_rec.ready_to_bill_flag
,l_task_in_rec.ready_to_distribute_flag
,l_task_in_rec.billable_flag
,l_task_in_rec.chargeable_flag
,l_task_in_rec.limit_to_txn_controls_flag
,rtrim(l_task_in_rec.task_description)
,l_task_in_rec.service_type_code
,l_task_in_rec.task_manager_person_id
,l_task_in_rec.carrying_out_organization_id
,l_task_in_rec.task_start_date
,l_task_in_rec.task_completion_date
,l_task_in_rec.labor_std_bill_rate_schdl
,l_task_in_rec.labor_bill_rate_org_id
,l_task_in_rec.labor_schedule_fixed_date
,l_task_in_rec.labor_schedule_discount
,l_task_in_rec.non_labor_std_bill_rate_schdl
,l_task_in_rec.non_labor_bill_rate_org_id
,l_task_in_rec.non_labor_schedule_fixed_date
,l_task_in_rec.non_labor_schedule_discount
,l_task_in_rec.cost_ind_rate_sch_id
,l_task_in_rec.rev_ind_rate_sch_id
,l_task_in_rec.inv_ind_rate_sch_id
,l_task_in_rec.cost_ind_sch_fixed_date
,l_task_in_rec.rev_ind_sch_fixed_date
,l_task_in_rec.inv_ind_sch_fixed_date
,l_task_in_rec.labor_sch_type
,l_task_in_rec.non_labor_sch_type
,rtrim(l_attribute_category)
,rtrim(l_attribute1)
,rtrim(l_attribute2)
,rtrim(l_attribute3)
,rtrim(l_attribute4)
,rtrim(l_attribute5)
,rtrim(l_attribute6)
,rtrim(l_attribute7)
,rtrim(l_attribute8)
,rtrim(l_attribute9)
,rtrim(l_attribute10)
,l_allow_cross_charge_flag
,l_project_rate_type
,l_project_rate_date
,p_project_rec.pm_product_code
,l_actual_start_date
,l_actual_finish_date
,l_early_start_date
,l_early_finish_date
,l_late_start_date
,l_late_finish_date
,l_scheduled_start_date
,l_scheduled_finish_date
,l_cc_process_labor_flag
,l_labor_tp_schedule_id
,l_labor_tp_fixed_date
,l_cc_process_nl_flag
,l_nl_tp_schedule_id
,l_nl_tp_fixed_date
,l_receive_project_invoice_flag
,l_work_type_id
,l_emp_bill_rate_schedule_id
,l_job_bill_rate_schedule_id
,l_non_lab_std_bill_rt_sch_id
,l_taskfunc_cost_rate_type
,l_taskfunc_cost_rate_date
,l_labor_disc_reason_code
,l_non_labor_disc_reason_code
--PA L 2872708
,l_retirement_cost_flag
,l_cint_eligible_flag
,l_cint_stop_date
--PA L 2872708
--(Begin Venkat) FP_M changes --------------------------------
,l_revenue_accrual_method
,l_invoice_method
,l_customer_id1
,l_gen_etc_source_code
--(End Venkat) FP_M changes --------------------------------
--(Begin Rtarway, 3658479)
, l_labor_cost_multiplier_name
, 1
--(End rtarway , 3658479)
,l_adj_on_std_inv /* Added for 12.2Payroll billing ER 11899223 */
);
SELECT *
FROM pa_tasks t
WHERE t.project_id = p_project_id
AND t.pm_task_reference = p_pm_task_reference;
SELECT *
FROM pa_tasks t
WHERE t.project_id = p_project_id
AND t.task_id = p_task_id;
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
SELECT task_number
FROM pa_tasks p
WHERE p.task_id = p_pa_task_id;
UPDATE PA_TASKS
SET
wbs_level = l_wbs_level
,top_task_id = l_top_task_id
,parent_task_id = l_parent_task_id
,ready_to_bill_flag = l_task_in_rec.ready_to_bill_flag
,ready_to_distribute_flag = l_task_in_rec.ready_to_distribute_flag
WHERE project_id = l_project_id
AND task_id = l_task_id;
select customer_id into l_customer_id
from pa_tasks where task_id =l_parent_task_id; /* added for bug 7495993 */
UPDATE PA_TASKS
SET
wbs_level = l_wbs_level
,top_task_id = l_top_task_id
,parent_task_id = l_parent_task_id
,billable_flag = l_task_in_rec.billable_flag
,customer_id = l_customer_id /* added for bug 7495993*/
,service_type_code = l_task_in_rec.service_type_code
,task_manager_person_id = l_task_in_rec.task_manager_person_id
,carrying_out_organization_id = l_task_in_rec.carrying_out_organization_id
,start_date = l_task_in_rec.task_start_date
,completion_date = l_task_in_rec.task_completion_date
,labor_std_bill_rate_schdl = l_task_in_rec.labor_std_bill_rate_schdl
,labor_bill_rate_org_id = l_task_in_rec.labor_bill_rate_org_id
,labor_schedule_fixed_date = l_task_in_rec.labor_schedule_fixed_date
,labor_schedule_discount = l_task_in_rec.labor_schedule_discount
,non_labor_std_bill_rate_schdl = l_task_in_rec.non_labor_std_bill_rate_schdl
,non_labor_bill_rate_org_id = l_task_in_rec.non_labor_bill_rate_org_id
,non_labor_schedule_fixed_date = l_task_in_rec.non_labor_schedule_fixed_date
,non_labor_schedule_discount = l_task_in_rec.non_labor_schedule_discount
,cost_ind_rate_sch_id = l_task_in_rec.cost_ind_rate_sch_id
,rev_ind_rate_sch_id = l_task_in_rec.rev_ind_rate_sch_id
,inv_ind_rate_sch_id = l_task_in_rec.inv_ind_rate_sch_id
,cost_ind_sch_fixed_date = l_task_in_rec.cost_ind_sch_fixed_date
,rev_ind_sch_fixed_date = l_task_in_rec.rev_ind_sch_fixed_date
,inv_ind_sch_fixed_date = l_task_in_rec.inv_ind_sch_fixed_date
,labor_sch_type = l_task_in_rec.labor_sch_type
,non_labor_sch_type = l_task_in_rec.non_labor_sch_type
,gen_etc_source_code = l_task_in_rec.gen_etc_source_code -- BUG 3924597 Added by rtarway for FP.M, etc_source defaulting changes
WHERE project_id = l_project_id
AND task_id = l_task_id;
UPDATE pa_tasks
SET chargeable_flag = 'N'
WHERE task_id = l_parent_task_rec.task_id;
UPDATE pa_tasks
SET chargeable_flag = 'N'
WHERE task_id = l_task_id;
SELECT task_number, long_task_name, description, --bug 2833194
parent_task_id,carrying_out_organization_id,
pm_task_reference, address_id, task_manager_person_id
FROM pa_tasks
WHERE project_id = l_project_id
AND task_id = l_task_id;
l_first_index1 IS NOT NULL AND l_last_index1 IS NOT NULL --called from create_project or update_proejct.
THEN
IF l_index_number > l_first_index1 AND
p_tasks_in(l_index_number-1).pa_task_id IS NOT NULL AND
p_tasks_in(l_index_number-1).pa_task_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
--existing task becomes the ref_task_id
THEN
v_task_id_above := p_tasks_in(l_index_number-1).pa_task_id; --Get the task above.
SELECT project_id
FROM
pa_projects
WHERE
pm_project_reference = p_pm_project_reference;
SELECT task_id
FROM
pa_tasks
WHERE project_id = p_pa_project_id
AND pm_task_reference = p_pm_task_reference;
SELECT 'X'
FROM pa_projects
where project_id = p_pa_project_id;
SELECT 'X'
FROM pa_projects
where project_id = p_pa_project_id;
SELECT 'X'
FROM pa_tasks
WHERE task_id = p_pa_task_id
AND project_id = p_pa_project_id;
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
SELECT 'X'
FROM pa_projects
where project_id = p_pa_project_id;
SELECT 'X'
FROM pa_tasks
WHERE task_id = p_pa_task_id
AND project_id = p_pa_project_id;
SELECT 'X'
FROM pa_proj_elements
WHERE proj_element_id = p_pa_task_id
AND project_id = p_pa_project_id;
SELECT proj_element_id
FROM pa_proj_elements
WHERE PM_SOURCE_REFERENCE = p_pm_task_reference
AND project_id = p_pa_project_id
AND parent_structure_id = ( SELECT ppe.proj_element_id --Added this subQry for bug#3653517
FROM pa_proj_elements ppe,
pa_proj_structure_types ppst,
pa_structure_types pst
WHERE ppe.proj_element_id = ppst.proj_element_id
AND ppe.project_id = p_pa_project_id
AND ppst.structure_type_id = pst.structure_type_id
AND pst.structure_type = p_structure_type)
AND OBJECT_TYPE = 'PA_TASKS';
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
SELECT 1
FROM pa_organizations_project_v
WHERE organization_id = p_organization_id
AND active_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN
TRUNC(date_from) AND NVL(date_to, TRUNC(SYSDATE));
SELECT 'x'
FROM pa_project_statuses
WHERE project_status_code = p_project_status
AND trunc(sysdate) BETWEEN start_date_active and
nvl(end_date_active,trunc(sysdate));
SELECT 'x' FROM
pa_project_type_distributions d
WHERE d.project_type = l_project_type
AND d.distribution_rule = l_dist_rule;
SELECT 'x'
FROM PA_PROJECT_TYPE_DISTRIBUTIONS ppd
WHERE ppd.project_type = p_project_type
AND ppd.distribution_rule = p_dist_rule
AND substr(ppd.Distribution_Rule, instr(ppd.Distribution_Rule,'/')+1) <> 'COST';
SELECT 'x' FROM
pa_employees
WHERE person_id = l_person_id;
SELECT 'x'
FROM pa_class_codes
WHERE class_category = l_class_category
AND class_code = l_class_code ;
PROCEDURE Delete_One_Task
(p_task_id IN NUMBER,
p_return_status OUT NOCOPY VARCHAR2, -- 4537865
p_msg_count OUT NOCOPY NUMBER, -- 4537865
p_msg_data OUT NOCOPY VARCHAR2 ) IS -- 4537865
CURSOR l_get_task_det_csr IS
SELECT project_id,
task_id,
parent_task_id,
top_task_id,
TASK_NAME,
PM_TASK_REFERENCE
FROM
pa_tasks
WHERE task_id = p_task_id;
SELECT task_id
FROM pa_tasks
WHERE project_id = l_project_id
AND parent_task_id = l_task_id;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_One_Task';
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
SELECT task_number
FROM pa_tasks p
WHERE p.task_id = p_pa_task_id;
SAVEPOINT delete_one_task;
( p_old_message_code => 'PA_TOP_TASK_CHILD_NO_DELETE'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'Y'
,p_msg_context => 'TASK'
,p_attribute1 => l_amg_segment1
,p_attribute2 => l_amg_task_number
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
UPDATE pa_tasks
SET parent_task_id = l_task_rec.parent_task_id,
wbs_level = Decode(wbs_level,1,1,wbs_level-1)
WHERE project_id = l_task_rec.project_id
AND parent_task_id = l_task_rec.task_id;
DELETE FROM pa_transaction_controls WHERE task_id = p_task_id;
DELETE FROM pa_billing_assignments WHERE
top_task_id = p_task_id
AND project_id = l_task_rec.project_id;
DELETE FROM pa_labor_multipliers WHERE task_id = p_task_id;
DELETE FROM pa_job_bill_rate_overrides WHERE task_id = p_task_id;
DELETE FROM pa_job_bill_title_overrides WHERE task_id = p_task_id;
DELETE FROM pa_job_assignment_overrides WHERE task_id = p_task_id;
DELETE FROM pa_emp_bill_rate_overrides WHERE task_id = p_task_id;
DELETE FROM pa_nl_bill_rate_overrides WHERE task_id = p_task_id;
DELETE FROM pa_compiled_multipliers
WHERE ind_compiled_set_id IN
(SELECT ics.ind_compiled_set_id
FROM pa_ind_compiled_sets ics,
pa_ind_rate_sch_revisions rev,
pa_ind_rate_schedules sch
WHERE ics.ind_rate_sch_revision_id =
rev.ind_rate_sch_revision_id
AND rev.ind_rate_sch_id = sch.ind_rate_sch_id
AND sch.task_id = p_task_id);
DELETE FROM pa_ind_compiled_sets
WHERE ind_rate_sch_revision_id IN
(SELECT rev.ind_rate_sch_revision_id
FROM pa_ind_rate_sch_revisions rev,
pa_ind_rate_schedules sch
WHERE rev.ind_rate_sch_id = sch.ind_rate_sch_id
AND sch.task_id = p_task_id );
DELETE FROM pa_ind_rate_sch_revisions
WHERE ind_rate_sch_id IN
(SELECT ind_rate_sch_id
FROM pa_ind_rate_schedules
WHERE task_id = p_task_id );
DELETE FROM pa_ind_rate_schedules WHERE task_id = p_task_id;
DELETE FROM pa_project_asset_assignments WHERE task_id = p_task_id;
DELETE FROM pa_percent_completes WHERE task_id = p_task_id;
DELETE FROM pa_tasks WHERE task_id = p_task_id;
ROLLBACK TO delete_one_task;
ROLLBACK TO delete_one_task;
END Delete_One_Task;
,p_update_start_date_flag OUT NOCOPY VARCHAR2 -- 4537865 Added nocopy
,p_update_end_date_flag OUT NOCOPY VARCHAR2 ) -- 4537865 Added nocopy
IS
l_api_name CONSTANT VARCHAR2(30) := 'check_start_end_date';
,p_update_start_date_flag => p_update_start_date_flag
,p_update_end_date_flag => p_update_end_date_flag);
p_update_end_date_flag := NULL ;
p_update_start_date_flag := NULL ;
update the end date of the existing manager with the date provided
Else update the end date of the existing manager to either
(a) new manager's start date -1 or (b) sysdate -1
(being done in check_for_one_manager);
p_proceed_with_update_flag OUT NOCOPY VARCHAR2, -- 4537865
p_return_status OUT NOCOPY VARCHAR2 ) -- 4537865
IS
BEGIN
pa_project_check_pvt.handle_task_number_change_pvt
(p_project_id => p_project_id
,p_task_id => p_task_id
,p_array_cell_number => p_array_cell_number
,p_in_task_number => p_in_task_number
,p_in_task_tbl => p_in_task_tbl
,p_proceed_with_update_flag => p_proceed_with_update_flag
,p_return_status => p_return_status);
p_proceed_with_update_flag := NULL ;
Procedure Update_One_Task
( p_api_version_number IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_msg_count OUT NOCOPY NUMBER, -- 4537865
p_msg_data OUT NOCOPY VARCHAR2, -- 4537865
p_return_status OUT NOCOPY VARCHAR2, -- 4537865
p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_pa_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_pm_task_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_task_number IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_pa_task_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_task_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_long_task_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_task_description IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_task_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_task_completion_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_pm_parent_task_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_pa_parent_task_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_address_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_carrying_out_organization_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_service_type_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_task_manager_person_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_billable_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_chargeable_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_ready_to_bill_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_ready_to_distribute_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_limit_to_txn_controls_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_labor_bill_rate_org_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_labor_std_bill_rate_schdl IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_labor_schedule_fixed_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_labor_schedule_discount IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_nl_bill_rate_org_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_nl_std_bill_rate_schdl IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_nl_schedule_fixed_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_nl_schedule_discount IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_labor_cost_multiplier_name IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_cost_ind_rate_sch_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_rev_ind_rate_sch_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_inv_ind_rate_sch_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_cost_ind_sch_fixed_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_rev_ind_sch_fixed_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_inv_ind_sch_fixed_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_labor_sch_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_nl_sch_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_actual_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_actual_finish_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_early_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_early_finish_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_late_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_late_finish_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_scheduled_start_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_scheduled_finish_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_tasks_dff IN VARCHAR2 := 'N', -- bug 13680655
p_attribute_category IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute1 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute2 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute3 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute4 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute5 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute6 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute7 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute8 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute9 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_attribute10 IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_allow_cross_charge_flag IN VARCHAR2 :=
PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_project_rate_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_project_rate_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_cc_process_labor_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_labor_tp_schedule_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_labor_tp_fixed_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_cc_process_nl_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_nl_tp_schedule_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_nl_tp_fixed_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
p_receive_project_invoice_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_work_type_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_emp_bill_rate_schedule_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_job_bill_rate_schedule_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
--Sakthi MCB
p_non_lab_std_bill_rt_sch_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_taskfunc_cost_rate_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_taskfunc_cost_rate_date IN DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
--Sakthi MCB
p_labor_disc_reason_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_non_labor_disc_reason_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
--PA L changes -- bug 2872708 --update_task
p_retirement_cost_flag VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_cint_eligible_flag VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_cint_stop_date DATE := PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE,
--end PA L changes -- bug 2872708
--(Begin Venkat) FP_M changes ----------------------------------------------
p_invoice_method IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
p_customer_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
p_gen_etc_source_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR,
--(End Venkat) FP_M changes ------------------------------------------------
p_out_pa_task_id OUT NOCOPY NUMBER, -- 4537865
p_out_pm_task_reference OUT NOCOPY VARCHAR2 ) -- 4537865
IS
CURSOR l_get_project_info_csr (l_project_id IN NUMBER)
IS
SELECT project_type,pm_project_reference
FROM pa_projects
WHERE project_id = l_project_id;
SELECT project_type_class_code
FROM pa_project_types pt, pa_projects p
WHERE p.project_id = l_project_id
and p.project_type = pt.project_type;
SELECT 'x'
FROM pa_lookups
WHERE lookup_type = 'SERVICE TYPE'
AND lookup_code = p_service_type_code;
SELECT *
FROM pa_tasks
WHERE project_id = l_project_id
AND task_id = l_task_id;
SELECT 'x' FROM
PA_TASKS
WHERE project_id = l_project_id
AND parent_task_id = l_task_id;
SELECT MIN(TRUNC(start_date))
, MAX(TRUNC(completion_date)) -- Bug Fix 4705139
FROM pa_tasks
WHERE project_id = l_project_id
AND parent_task_id = l_task_id;
SELECT MIN(TRUNC(start_date))
, MAX(TRUNC(completion_date)) -- Bug Fix 4705139
FROM pa_tasks
WHERE project_id = l_project_id
AND task_id = l_task_id;
SELECT trunc(start_date) start_date,trunc(completion_date) completion_date -- Bug Fix 4705139
FROM pa_projects
WHERE project_id = l_project_id;
SELECT top_task_id
FROM pa_tasks
WHERE task_id = p_task_id;
SELECT task_id
FROM pa_tasks
WHERE pm_task_reference = p_pm_task_reference
AND project_id = p_project_id;
SELECT wbs_level
FROM pa_tasks
WHERE task_id = p_pa_task_id
AND project_id = p_project_id;
SELECT 'x'
FROM pa_tasks
WHERE task_id = p_task_id
AND project_id = p_project_id;
SELECT 'x'
FROM pa_tasks
WHERE task_id = p_task_id
FOR UPDATE NOWAIT;
SELECT task_id
FROM pa_tasks
START WITH task_id=p_task_id
CONNECT BY PRIOR task_id=parent_task_id
FOR UPDATE NOWAIT;
SELECT 'x'
FROM pa_tasks
WHERE task_id=p_new_parent_task_id
START WITH task_id=p_task_id
CONNECT BY PRIOR task_id=parent_task_id
FOR UPDATE NOWAIT;
SELECT customer_id
FROM pa_project_customers
WHERE project_id = p_project_id;
SELECT 'x'
FROM ra_addresses
WHERE customer_id = p_customer_id
AND address_id = p_address_id;
SELECT 'x'
FROM
hz_cust_acct_sites_all acct_site
WHERE
acct_site.cust_account_id = p_customer_id
AND acct_site.cust_acct_site_id = p_address_id;
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
SELECT task_number
FROM pa_tasks p
WHERE p.task_id = p_pa_task_id;
SELECT ind_rate_schedule_type
FROM pa_ind_rate_schedules
WHERE ind_rate_sch_id = p_cost_ind_rate_sch_id;
SELECT 'x'
FROM pa_ind_rate_schedules
WHERE ind_rate_sch_id = p_cost_ind_rate_sch_id;
l_api_name CONSTANT VARCHAR2(30):= 'update_one_task';
l_update_yes_flag VARCHAR2(1) := 'N';
l_update_start_date_flag VARCHAR2(1);
l_update_end_date_flag VARCHAR2(1);
SAVEPOINT update_task_pub;
l_module_name := 'PA_PM_UPDATE_TASK';
IF PA_PROJECT_PUB.G_MASS_UPDATE_TASKS = 'Y' THEN
IF PA_PROJECT_PUB.G_UPDATE_TASK_ALLOWED IS NULL THEN
PA_PM_FUNCTION_SECURITY_PUB.check_function_security
(p_api_version_number => p_api_version_number,
p_responsibility_id => l_resp_id,
p_function_name => 'PA_PM_UPDATE_TASK',
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_function_allowed => l_function_allowed );
PA_PROJECT_PUB.G_UPDATE_TASK_ALLOWED := l_function_allowed;
l_function_allowed := PA_PROJECT_PUB.G_UPDATE_TASK_ALLOWED;
p_function_name => 'PA_PM_UPDATE_TASK',
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_function_allowed => l_function_allowed );
IF PA_PROJECT_PUB.G_MASS_UPDATE_TASKS = 'Y' THEN
IF PA_PROJECT_PUB.G_VALID_PRODUCT_CODE IS NULL THEN
/*added for bug no :2413400*/
OPEN p_product_code_csr (p_pm_product_code);
IF PA_PROJECT_PUB.G_MASS_UPDATE_TASKS = 'Y' THEN
IF PA_PROJECT_PUB.G_UPDATE_PRJ_ALLOWED IS NULL THEN
PA_PROJECT_PUB.G_UPDATE_PRJ_ALLOWED := pa_security.allow_update (x_project_id => l_project_id );
IF PA_PROJECT_PUB.G_UPDATE_PRJ_ALLOWED = 'N' THEN
pa_interface_utils_pub.map_new_amg_msg
( p_old_message_code => 'PA_PROJECT_SECURITY_ENFORCED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'Y'
,p_msg_context => 'GENERAL'
,p_attribute1 => ''
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
IF pa_security.allow_update (x_project_id => l_project_id ) = 'N' THEN
-- The user does not have update privileges on this project
-- Hence , raise error
pa_interface_utils_pub.map_new_amg_msg
( p_old_message_code => 'PA_PROJECT_SECURITY_ENFORCED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'Y'
,p_msg_context => 'GENERAL'
,p_attribute1 => ''
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
IF PA_PROJECT_PUB.G_MASS_UPDATE_TASKS = 'Y' THEN
l_amg_segment1 := PA_PROJECT_PUB.G_SEGMENT1;
l_statement := ' UPDATE PA_TASKS SET ';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
IS NULL- so that the description column is not updated to NULL
when no value is passed. */
IF (p_task_description <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR OR
p_task_description IS NULL) /* The new change for bug 3321980 */
AND nvl(p_task_description,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) <>
nvl(l_task_rec.description,PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR )
THEN
--bug 2955326
/*
l_statement := l_statement ||
' DESCRIPTION = '||''''||
RTRIM(p_task_description)||''''||',';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
IF (fnd_function.test('PA_PAXPREPR_UPDATE_ORG') = TRUE) THEN
l_org_func_security := 'Y';
,X_insert_update_mode => NULL
,X_calling_module => 'UPDATE_TASK'
,X_project_id => l_task_rec.project_id
,X_task_id => l_task_id
,X_old_value =>
To_char(l_task_rec.carrying_out_organization_id)
,X_new_value =>
To_char(p_carrying_out_organization_id)
,X_project_type => l_project_type
,X_project_start_date => NULL
,X_project_end_date => NULL
,X_public_sector_flag => NULL
,X_task_manager_person_id => l_task_rec.task_manager_person_id
,X_Service_type => l_task_rec.service_type_code
,X_task_start_date => l_task_rec.start_date
,X_task_end_date => l_task_rec.completion_date
,X_entered_by_user_id => FND_GLOBAL.USER_ID
,X_attribute_category => l_task_rec.attribute_category
,X_attribute1 => l_task_rec.attribute1
,X_attribute2 => l_task_rec.attribute2
,X_attribute3 => l_task_rec.attribute3
,X_attribute4 => l_task_rec.attribute4
,X_attribute5 => l_task_rec.attribute5
,X_attribute6 => l_task_rec.attribute6
,X_attribute7 => l_task_rec.attribute7
,X_attribute8 => l_task_rec.attribute8
,X_attribute9 => l_task_rec.attribute9
,X_attribute10 => l_task_rec.attribute10
,X_pm_product_code => l_task_rec.pm_product_code
,X_pm_project_reference => l_pm_project_reference
,X_pm_task_reference => l_task_rec.pm_task_reference
-- ,X_functional_security_flag => NULL /* Bug#1968394 */
,X_functional_security_flag => l_org_func_security /* Bug#1968394 */
,x_warnings_only_flag => l_warnings_only_flag --bug3134205
,X_err_code => l_err_code
,X_err_stage => l_err_stage
,X_err_stack => l_err_stack );
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
IF NVL(PA_PROJECT_PUB.G_MASS_UPDATE_TASKS,'N') = 'N' THEN
OPEN l_get_project_type_info_csr (l_task_rec.project_id);
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
IF PA_PROJECT_PUB.G_MASS_UPDATE_TASKS = 'Y' THEn
l_project_start_date := PA_PROJECT_PUB.G_PROJ_START_DATE;
block so that the update would only happen when the above conditions
are true */
--Check validity of start and completion date (e.g. start_date can not be later than completion_date)
pa_project_pvt.check_start_end_date( p_old_start_date => l_task_rec.start_date
,p_new_start_date => p_task_start_date
,p_old_end_date => l_task_rec.completion_date
,p_new_end_date => p_task_completion_date
,p_update_start_date_flag => l_update_start_date_flag
,p_update_end_date_flag => l_update_end_date_flag
,p_return_status => l_return_status );
IF l_update_start_date_flag = 'Y'
THEN
IF p_task_start_date < l_project_start_date OR
p_task_start_date > l_project_completion_date
THEN
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
pa_interface_utils_pub.map_new_amg_msg
( p_old_message_code => 'PA_TK_OUTSIDE_PROJECT_RANGE'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'Y'
,p_msg_context => 'TASK'
,p_attribute1 => l_amg_segment1
,p_attribute2 => l_amg_task_number
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
l_update_yes_flag := 'Y';
IF l_update_end_date_flag = 'Y'
THEN
IF p_task_completion_date < l_project_start_date OR
p_task_completion_date > l_project_completion_date
THEN
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
pa_interface_utils_pub.map_new_amg_msg
( p_old_message_code => 'PA_TK_OUTSIDE_PROJECT_RANGE'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'Y'
,p_msg_context => 'TASK'
,p_attribute1 => l_amg_segment1
,p_attribute2 => l_amg_task_number
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
l_update_yes_flag := 'Y';
so that the update would only happen when the above conditions
are true */
--PARENT TASK
--convert_taskref_to_id can not be used because NULL is allowed for the pa_parent_task_id!!!
--rtarway, for DHI ER, BUG 4413568 , following section will be changing the WBS , it involves, moving
--a task. This should not be allowed when p_update_mode is PA_UPD_TASK_ATTR
--Error will be raised , when parent_task_id is different than existing.
l_new_parent_task_id := NULL;
IF NVL(PA_PROJECT_PUB.G_MASS_UPDATE_TASKS,'N') = 'N' THEN
IF p_pm_parent_task_reference IS NULL AND
l_task_rec.parent_task_id IS NOT NULL THEN
-- this means that a sub task is being changed to become a top task
-- which is not allowed
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
pa_interface_utils_pub.map_new_amg_msg
( p_old_message_code => 'PA_TOP_TASK_NOT_ALLOWED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'TASK'
,p_attribute1 => l_amg_segment1
,p_attribute2 => l_amg_task_number
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
IF ( PA_PROJECT_PUB.G_TASK_STR_UPDATE_MODE = 'PA_UPD_TASK_ATTR' ) THEN
PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
p_msg_name => 'PA_WBS_CANT_CHANGE');
l_update_yes_flag := 'Y';
UPDATE pa_tasks
SET wbs_level = wbs_level + l_delta_level
WHERE task_id = l_wbs_change_task_id;
l_update_yes_flag := 'Y';
(PA_PROJECT_PUB.G_MASS_UPDATE_TASKS = 'Y' AND PA_PROJECT_PUB.G_FP_DFF_TBL.COUNT > 0)) THEN -- BEGIN changes for bug 13680655
--bug 6153503
IF (p_attribute_category <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR OR
p_attribute_category IS NULL) THEN
l_attribute_category := p_attribute_category;
IF NVL(PA_PROJECT_PUB.G_MASS_UPDATE_TASKS,'1234') = 'Y' THEN
l_project_type2 := PA_PROJECT_PUB.G_PROJECT_TYPE;
SELECT PROJECT_TYPE
INTO l_project_type2
FROM PA_PROJECTS
WHERE PROJECT_ID = p_pa_project_id;
SELECT PROJECT_TYPE
INTO l_project_type2
FROM PA_PROJECTS
WHERE PROJECT_ID IN (SELECT PROJECT_ID
FROM PA_TASKS
WHERE TASK_ID = p_pa_task_id);
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
select count(1) into lv_count from PA_WORK_TYPES_B where WORK_TYPE_ID = p_work_type_id;
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
IF NVL(PA_PROJECT_PUB.G_MASS_UPDATE_TASKS,'N') = 'N' THEN
Check_Schedule_type
(p_pa_task_id => l_task_id,
p_pa_project_id => l_project_id,
p_in_labor_sch_type => p_labor_sch_type,
p_in_nl_sch_type => p_nl_sch_type,
p_task_name => p_task_name,
p_pm_task_reference => p_pm_task_reference,
p_out_labor_sch_type => l_out_labor_sch_type,
p_out_nl_labor_sch_type => l_out_nl_labor_sch_type,
p_return_status => l_return_status
);
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
/* Should be able to update null since this is not mandatory*/
IF ((p_emp_bill_rate_schedule_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
AND p_emp_bill_rate_schedule_id IS NOT NULL ) OR (p_emp_bill_rate_schedule_id IS NULL))
THEN
IF ((p_emp_bill_rate_schedule_id <> NVL(l_task_rec.EMP_BILL_RATE_SCHEDULE_ID,PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM))
OR (p_emp_bill_rate_schedule_id IS NULL))
THEN
--bug 2955326
/*
l_statement := l_statement ||
' EMP_BILL_RATE_SCHEDULE_ID = '||''''||
TO_CHAR(p_emp_bill_rate_schedule_id)||''''||',';
l_update_yes_flag := 'Y';
1)Should be able to update null when prm is not licensed.
2) When prm is licensed then it is mandatory.
*/
IF ((p_job_bill_rate_schedule_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
AND p_job_bill_rate_schedule_id IS NOT NULL )
OR (p_job_bill_rate_schedule_id is NULL and pa_install.is_prm_licensed = 'N'))
THEN
IF ((p_job_bill_rate_schedule_id <> NVL(l_task_rec.JOB_BILL_RATE_SCHEDULE_ID,
PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)) OR (p_job_bill_rate_schedule_id is NULL))
THEN
--bug 2955326
/*
l_statement := l_statement ||
' JOB_BILL_RATE_SCHEDULE_ID = '||''''||
TO_CHAR(p_job_bill_rate_schedule_id)||''''||',';
l_update_yes_flag := 'Y';
/* Should be able to update null*/
IF ((p_labor_schedule_fixed_date <>
PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
AND p_labor_schedule_fixed_date IS NOT NULL) OR p_labor_schedule_fixed_date IS NULL) THEN
IF p_labor_schedule_fixed_date
<> NVL(l_task_rec.LABOR_SCHEDULE_FIXED_DATE,
PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE) OR (p_labor_schedule_fixed_date IS NULL)
THEN
--bug 2955326
/*
l_statement := l_statement ||
' LABOR_SCHEDULE_FIXED_DATE = to_date('''||
to_char(p_labor_schedule_fixed_date,'YYYY/MM/DD')||
''', ''YYYY/MM/DD''),';
l_update_yes_flag := 'Y';
/* Should be able to update null*/
IF ((p_labor_schedule_discount <>
PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
AND p_labor_schedule_discount IS NOT NULL ) OR (p_labor_schedule_discount IS NULL ))
THEN
IF ((p_labor_schedule_discount <> NVL(l_task_rec.LABOR_SCHEDULE_DISCOUNT,PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM))
OR (p_labor_schedule_discount IS NULL))
THEN
--bug 2955326
/*
l_statement := l_statement ||
' LABOR_SCHEDULE_DISCOUNT = '||''''||
TO_CHAR(p_labor_schedule_discount)||''''||',';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
SELECT ind_rate_schedule_type INTO l_rev_rate_sch_type
FROM pa_ind_rate_schedules
WHERE ind_rate_sch_id = p_rev_ind_rate_sch_id
AND trunc(sysdate) between trunc(start_date_active) and nvl(end_date_active,trunc(sysdate));
SELECT ind_rate_schedule_type INTO l_rev_rate_sch_type
FROM pa_ind_rate_schedules
WHERE ind_rate_sch_id = l_task_rec.REV_IND_RATE_SCH_ID;
SELECT ind_rate_schedule_type INTO l_inv_rate_sch_type
FROM pa_ind_rate_schedules
WHERE ind_rate_sch_id = p_inv_ind_rate_sch_id
AND trunc(sysdate) between trunc(start_date_active) and nvl(end_date_active,trunc(sysdate));
SELECT ind_rate_schedule_type INTO l_inv_rate_sch_type
FROM pa_ind_rate_schedules
WHERE ind_rate_sch_id = l_task_rec.INV_IND_RATE_SCH_ID;
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
SELECT std_bill_rate_schedule INTO l_std_bill_rate_schedule
FROM pa_std_bill_rate_schedules_all
WHERE bill_rate_sch_id=p_non_lab_std_bill_rt_sch_id;
l_update_yes_flag := 'Y';
/* Should be able to update null*/
IF ((p_nl_schedule_fixed_date <>
PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE
AND p_nl_schedule_fixed_date IS NOT NULL) OR (p_nl_schedule_fixed_date IS NULL)) THEN
IF (p_nl_schedule_fixed_date
<> NVL(l_task_rec.NON_LABOR_SCHEDULE_FIXED_DATE,
PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE) OR (p_nl_schedule_fixed_date IS NULL))
THEN
--bug 2955326
/*
l_statement := l_statement ||
' NON_LABOR_SCHEDULE_FIXED_DATE = to_date('''||
to_char(p_nl_schedule_fixed_date,'YYYY/MM/DD')||
''', ''YYYY/MM/DD''),';
l_update_yes_flag := 'Y';
/* Should be able to update null*/
IF ((p_nl_schedule_discount <>
PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
AND p_nl_schedule_discount IS NOT NULL) OR (p_nl_schedule_discount IS NULL)) THEN
-- Bug 9073661
IF (p_nl_schedule_discount <> NVL(l_task_rec.non_labor_schedule_discount,PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
OR p_nl_schedule_discount IS NULL)
THEN
--bug 2955326
/*
l_statement := l_statement ||
' NON_LABOR_SCHEDULE_DISCOUNT = '||''''||
TO_CHAR(p_nl_schedule_discount)||''''||',';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
IF l_update_yes_flag = 'Y'
THEN
-- 4156036 Moved the below code to set invoice_method, customer_id and get_etc_source_code columns
-- before setting the where clause
-- wrong update sql statment was getting generated
IF (p_invoice_method IS NOT NULL AND p_invoice_method <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) AND
NVL(l_task_rec.invoice_method, PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) <> p_invoice_method
THEN
l_statement := l_statement || ' INVOICE_METHOD = :inv_md ,';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
UPDATE PA_TASKS SET customer_id = p_customer_id
WHERE top_task_id=l_task_rec.task_id AND not(task_id =l_task_rec.task_id);
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
' LAST_UPDATE_DATE = '||''''||
SYSDATE||''''||',';
' LAST_UPDATE_DATE = SYSDATE'||',';
' LAST_UPDATED_BY = :g_usr_id,'; --bug 2955326
' LAST_UPDATE_LOGIN = :g_login_id ';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
l_update_yes_flag := 'Y';
code again updates the task_number, trimming the '-' added.**/
END IF;
UPDATE pa_tasks
SET chargeable_flag = 'N'
WHERE task_id = l_task_id;
UPDATE pa_project_accum_headers
SET tasks_restructured_flag = 'Y' ,
last_update_login = l_user_id,
last_update_date = SYSDATE,
last_updated_by = l_user_id
WHERE project_id = l_project_id
AND task_id = 0
AND resource_list_member_id = 0;
ROLLBACK TO Update_Task_pub;
ROLLBACK TO Update_Task_pub;
ROLLBACK TO update_task_pub;
ROLLBACK TO Update_Task_pub;
END update_one_task;
PROCEDURE delete_task1
( p_api_version_number IN NUMBER
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_msg_count OUT NOCOPY NUMBER -- 4537865 Added nocopy hint
,p_msg_data OUT NOCOPY VARCHAR2 -- 4537865 Added nocopy hint
,p_return_status OUT NOCOPY VARCHAR2 -- 4537865 Added nocopy hint
,p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pa_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_pm_task_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pa_task_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_cascaded_delete_flag IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_project_id OUT NOCOPY NUMBER -- 4537865 Added nocopy hint
,p_task_id OUT NOCOPY NUMBER -- 4537865 Added nocopy hint
,p_task_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
,p_structure_type IN VARCHAR2 := 'FINANCIAL'
)
IS
CURSOR lock_tasks_csr ( p_task_id IN NUMBER )
IS
SELECT 'x'
FROM pa_tasks
START WITH task_id=p_task_id
CONNECT BY PRIOR task_id=parent_task_id
FOR UPDATE NOWAIT;
l_api_name CONSTANT VARCHAR2(30) := 'delete_task';
l_delete_task_ok_flag VARCHAR2(1);
l_cascaded_delete_flag VARCHAR2(1);
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
SELECT task_number
FROM pa_tasks p
WHERE p.task_id = p_pa_task_id;
SAVEPOINT delete_task_pub;
p_function_name => 'PA_PM_DELETE_TASK',
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_function_allowed => l_function_allowed );
pa_task_utils.check_delete_task_ok(x_task_id => l_task_id,
--bug 3010538 x_validation_mode => 'R', --bug 2947492
x_err_code => l_err_code,
x_err_stage => l_err_stage,
x_err_stack => l_err_stack);
( p_old_message_code => 'PA_DELETE_TASK_FAILED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'TASK'
,p_attribute1 => l_amg_segment1
,p_attribute2 => l_amg_task_number
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
( p_old_message_code => 'PA_DELETE_TASK_FAILED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'TASK'
,p_attribute1 => l_amg_segment1
,p_attribute2 => l_amg_task_number
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
PA_PROJ_TASK_STRUC_PUB.delete_fin_plan_from_task(
p_task_id => l_task_id
,p_project_id => l_project_id
,P_calling_module => 'AMG'
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status);
IF p_cascaded_delete_flag = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
OR p_cascaded_delete_flag IS NULL
THEN
l_cascaded_delete_flag := 'N';
ELSIF p_cascaded_delete_flag NOT IN ('Y','y','N','n')
THEN
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
pa_interface_utils_pub.map_new_amg_msg
( p_old_message_code => 'PA_CASCADE_FLAG_INVALID'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'PROJ'
,p_attribute1 => l_amg_segment1
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
l_cascaded_delete_flag := p_cascaded_delete_flag;
IF l_cascaded_delete_flag IN ('Y','y')
THEN
pa_project_core.delete_task( x_task_id => l_task_id
--bug 3010538 ,x_validation_mode => 'R' --bug 2947492
,x_err_code => l_err_code
,x_err_stage => l_err_stage
,x_err_stack => l_err_stack );
( p_old_message_code => 'PA_DELETE_TASK_FAILED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'TASK'
,p_attribute1 => l_amg_segment1
,p_attribute2 => l_amg_task_number
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
( p_old_message_code => 'PA_DELETE_TASK_FAILED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'TASK'
,p_attribute1 => l_amg_segment1
,p_attribute2 => l_amg_task_number
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
pa_project_pvt.Delete_One_Task
(p_task_id => l_task_id,
p_return_status => l_return_status,
p_msg_count => p_msg_count,
p_msg_data => p_msg_data );
PA_PROJ_TASK_STRUC_PUB.delete_task_structure
(
p_calling_module => 'AMG'
,p_task_id => l_task_id
,p_task_version_id => p_task_version_id
,p_project_id => l_project_id
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status);
The task deletion calls the PA_PROJECT_PUB.DELETE_TASK API. This was returning an error with status 'E' and error
message PA_TASK_REF_AND_ID_MISSING. There is a call to Convert_pm_taskref_to_id_all API in the PA_PROJECT_PVT.DELETE_TASK1
in the WORKPLAN portion of the code.
Please note that the same local variable l_task_id is used for both IN and OUT parameters. Due to the NOCOPY hint the variable
l_task_id was becoming null when the control enters into the Convert_pm_taskref_to_id_all API. As the required parameter was
NULL the API was raising the above error resulting into an error and stopping the task deletion.
Solution:
In order to avoid this issue a new local variable is created and used to capture the OUT parameters value in the call and
then assign the value back to the old l_task_id variable.
*/
PA_PROJECT_PVT.Convert_pm_taskref_to_id_all (
p_pa_project_id => l_project_id
, p_structure_type => 'WORKPLAN'
, p_pa_task_id => l_task_id
, p_pm_task_reference => l_task_source_reference
, p_out_task_id => l_task_id_out
, p_return_status => l_return_status );
PA_PROJ_TASK_STRUC_PUB.delete_task_structure
(
p_calling_module => 'AMG'
,p_task_id => l_task_id -- changed bug# 3801426 earlierr p_task_id was used
,p_task_version_id => p_task_version_id
,p_project_id => l_project_id --bug 2765115
,p_structure_type => p_structure_type -- added for bug# 3801426 , earlier structure_type was not passed
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_return_status => l_return_status);
ROLLBACK TO delete_task_pub;
ROLLBACK TO delete_task_pub;
ROLLBACK TO delete_task_pub;
ROLLBACK TO delete_task_pub;
END delete_task1;
PROCEDURE delete_project1
( p_api_version_number IN NUMBER
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_msg_count OUT NOCOPY NUMBER -- 4537865 Added nocopy hint
,p_msg_data OUT NOCOPY VARCHAR2 -- 4537865 Added nocopy hint
,p_return_status OUT NOCOPY VARCHAR2 -- 4537865 Added nocopy hint
,p_pm_product_code IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pm_project_reference IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
,p_pa_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
)
IS
CURSOR l_lock_rows_csr( p_project_id NUMBER)
IS
SELECT 'x'
FROM pa_projects
WHERE project_id = p_project_id
FOR UPDATE NOWAIT;
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
l_api_name CONSTANT VARCHAR2(30) := 'delete_project';
SAVEPOINT delete_project_pub;
l_module_name := 'PA_PM_DELETE_PROJECT';
p_function_name => 'PA_PM_DELETE_PROJECT',
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_return_status => l_return_status,
p_function_allowed => l_function_allowed );
IF pa_security.allow_update (x_project_id => l_project_id ) = 'N' THEN
-- The user does not have update privileges on this project
-- Hence , raise error
pa_interface_utils_pub.map_new_amg_msg
( p_old_message_code => 'PA_PROJECT_SECURITY_ENFORCED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'Y'
,p_msg_context => 'GENERAL'
,p_attribute1 => ''
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
pa_project_utils.check_delete_project_ok
(x_project_id => l_project_id,
--bug 3010538 x_validation_mode => 'R', --bug 2947492
x_err_code => l_err_code,
x_err_stage => l_err_stage,
x_err_stack => l_err_stack);
( p_old_message_code => 'PA_DELETE_PROJECT_FAILED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'PROJ'
,p_attribute1 => l_amg_segment1
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
pa_project_core.delete_project (x_project_id => l_project_id,
--bug 3010538 x_validation_mode => 'R', --bug 2947492
x_err_code => l_err_code,
x_err_stage => l_err_stage,
x_err_stack => l_err_stack,
x_commit => p_commit);
( p_old_message_code => 'PA_DELETE_PROJECT_FAILED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'PROJ'
,p_attribute1 => l_amg_segment1
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
( p_old_message_code => 'PA_DELETE_PROJECT_FAILED'
,p_msg_attribute => 'CHANGE'
,p_resize_flag => 'N'
,p_msg_context => 'PROJ'
,p_attribute1 => l_amg_segment1
,p_attribute2 => ''
,p_attribute3 => ''
,p_attribute4 => ''
,p_attribute5 => '');
ROLLBACK TO delete_project_pub;
ROLLBACK TO delete_project_pub;
ROLLBACK TO delete_project_pub;
ROLLBACK TO delete_project_pub;
END delete_project1;
SELECT 'x' FROM
pa_project_role_types_vl role,
fnd_lookup_values lookup
WHERE role.project_role_type = p_role_type
and lookup.lookup_type='PA_ORGANIZATION_ROLE_CLASSES'
and role.role_party_class=lookup.lookup_code
and lookup.language=role.language;
SELECT segment1,COMPLETION_DATE
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
/* Select
DECODE(p_org_roles(i).start_date,
PA_INTERFACE_UTILS_PUB.G_PA_MISS_DATE, TRUNC(SYSDATE),
NULL,TRUNC(SYSDATE),
p_org_roles(i).start_date)
Into v_start_date_active
From dual;
Select role.project_role_id
Into v_project_role_id
From pa_project_role_types_vl role,
fnd_lookup_values lookup
WHERE role.project_role_type = p_org_roles(i).project_role_type
and lookup.lookup_type='PA_ORGANIZATION_ROLE_CLASSES'
and role.role_party_class=lookup.lookup_code
and lookup.language=role.language;
SELECT 'x' FROM
hz_parties
WHERE party_id = l_party_id
AND party_type = 'ORGANIZATION';
SELECT ppe.proj_element_id
FROM pa_proj_elements ppe, pa_proj_structure_types ppst
WHERE ppe.proj_element_id = ppst.proj_element_id
AND ppe.project_id = p_project_id
AND ppe.object_type = 'PA_STRUCTURES'
AND ppst.structure_type_id = 1 ;
SELECT d.element_version_id
FROM pa_proj_structure_types b
,pa_proj_elem_ver_structure d
WHERE d.project_id = p_project_id
AND 1 = b.structure_type_id
AND b.proj_element_id = c_proj_element_id
AND d.proj_element_id = c_proj_element_id
AND d.status_code = 'STRUCTURE_PUBLISHED'
;
l_wp_str_ver_id := PA_PROJECT_STRUCTURE_UTILS.GET_LAST_UPDATED_WORKING_VER( l_wp_str_id );
SELECT labor_sch_type
FROM pa_projects
where project_id = p_pa_project_id;
SELECT labor_sch_type
FROM pa_tasks
where project_id = p_pa_project_id
and task_id= p_pa_task_id;
SELECT non_labor_sch_type
FROM pa_projects
where project_id = p_pa_project_id;
SELECT non_labor_sch_type
FROM pa_tasks
where project_id = p_pa_project_id
and task_id = p_pa_task_id;
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
SELECT ind_rate_schedule_type
FROM pa_ind_rate_schedules
WHERE ind_rate_sch_id = p_rev_ind_rate_sch_id
AND trunc(sysdate) between trunc(start_date_active) and nvl(end_date_active,trunc(sysdate));
SELECT ind_rate_schedule_type
FROM pa_ind_rate_schedules
WHERE ind_rate_sch_id = p_inv_ind_rate_sch_id
AND trunc(sysdate) between trunc(start_date_active) and nvl(end_date_active,trunc(sysdate));
SELECT 'x'
FROM pa_std_bill_rate_schedules_all brs, pa_projects pa
WHERE brs.bill_rate_sch_id = p_emp_bill_rate_schedule_id
AND pa.project_id=p_pa_project_id
and brs.schedule_type = 'EMPLOYEE'
AND ( pa.multi_currency_BILLING_flag='Y'
OR (pa.multi_currency_billing_flag='N'
AND brs.rate_sch_currency_code=pa.project_currency_code))
AND ((pa_multi_currency_billing.is_sharing_bill_rates_allowed(pa.org_id) = 'Y')
or (pa_multi_currency_billing.is_sharing_bill_rates_allowed(pa.org_id)='N'
and brs.org_id=pa.org_id))
and (brs.share_across_ou_flag = 'Y'
OR (brs.share_across_ou_flag = 'N'
and brs.org_id = pa.org_id
));
SELECT 'x'
FROM pa_std_bill_rate_schedules_all brs, pa_project_types_all pt, pa_projects pa
WHERE bill_rate_sch_id = p_job_bill_rate_schedule_id
AND brs.job_group_id=pt.bill_job_group_id
and brs.schedule_type = 'JOB'
AND pa.project_type=pt.project_type
AND pa.org_id = pt.org_id -- added for Bug 5675391
AND ( pa.multi_currency_BILLING_flag='Y'
OR (pa.multi_currency_billing_flag='N'
AND brs.rate_sch_currency_code=pa.project_currency_code))
AND ((pa_multi_currency_billing.is_sharing_bill_rates_allowed(pa.org_id) = 'Y')
or (pa_multi_currency_billing.is_sharing_bill_rates_allowed(pa.org_id)='N'
and brs.org_id=pa.org_id))
and (brs.share_across_ou_flag = 'Y'
OR (brs.share_across_ou_flag = 'N'
and brs.org_id = pa.org_id
))
AND pa.project_id=p_pa_project_id;
SELECT 'x'
FROM pa_std_bill_rate_schedules_all brs, pa_projects pa
WHERE brs.organization_id = p_nl_bill_rate_org_id
AND bill_rate_sch_id = p_non_lab_std_bill_rt_sch_id
and brs.schedule_type = 'NON-LABOR'
and (brs.schedule_type <> 'JOB'
or (brs.schedule_type = 'JOB'
and brs.job_group_id = pa.bill_job_group_id))
AND ( pa.multi_currency_BILLING_flag='Y'
OR (pa.multi_currency_billing_flag='N'
AND brs.rate_sch_currency_code=pa.project_currency_code))
AND ((pa_multi_currency_billing.is_sharing_bill_rates_allowed(pa.org_id) = 'Y')
or (pa_multi_currency_billing.is_sharing_bill_rates_allowed(pa.org_id)='N'
and brs.org_id=pa.org_id))
and (brs.share_across_ou_flag = 'Y'
OR (brs.share_across_ou_flag = 'N'
and brs.org_id = pa.org_id
))
AND pa.project_id=p_pa_project_id;
SELECT segment1
FROM pa_projects p
WHERE p.project_id = p_pa_project_id;
SELECT 'x'
FROM pa_lookups
WHERE lookup_type ='RATE AND DISCOUNT REASON'
AND lookup_code=p_labor_disc_reason_code
AND trunc(nvl(end_date_active,sysdate)) >= trunc(sysdate);
SELECT 'x'
FROM pa_lookups
WHERE lookup_type ='RATE AND DISCOUNT REASON'
AND lookup_code=p_non_labor_disc_reason_code
AND trunc(nvl(end_date_active,sysdate)) >= trunc(sysdate);
SELECT rate_discount_reason_flag
FROM pa_implementations;
SELECT 'Y' FROM pa_tasks
WHERE project_id = p_project_id
AND parent_task_id IS NULL
AND nvl(customer_id, -1) NOT IN (select customer_id
from pa_project_customers
where project_id=p_project_id);
select enable_top_task_customer_flag
from pa_projects_all
where project_id=p_project_id;
if (p_calling_context in ('CREATE_PROJECT','UPDATE_PROJECT')
and l_enable_tt_cust_flag = 'Y') then
open any_invalid_customer;
select count(*) into l_cust_count
from pa_project_customers
where project_id=p_project_id;
if (p_calling_context = 'UPDATE_PROJECT' and l_enable_tt_cust_flag = 'Y') then
begin
select 'Y' into valid_def_ttsk_cust
from pa_project_customers
where project_id=p_project_id
and default_top_task_cust_flag='Y';
select count(*) into l_cust_count
from pa_project_customers
where project_id=p_project_id;
IF p_lock_mode in ('ADD','UPDATE','PROGRESS') THEN
v_result := dbms_lock.request(id => p_project_id,lockmode => dbms_lock.s_mode, timeout => 1 );
ELSIF p_lock_mode in ('DELETE','MOVE','INDENT','OUTDENT','ROLLUP','ADD_F') THEN
--v_result := dbms_lock.request(lockhandle=> v_lockhandle,lockmode => dbms_lock.x_mode, timeout => 1 );
IF p_lock_mode in ('ADD','UPDATE','PROGRESS') THEN
x_msg_data:='PA_CANT_PERFM_ADD_UPD_PRGSS';
SELECT Count(1) FROM PA_PJI_PROJ_EVENTS_LOG
where event_object=p_project_id
AND EVENT_TYPE='DEFER_ROLLUP';
INSERT INTO PA_PJI_PROJ_EVENTS_LOG (EVENT_ID,EVENT_TYPE,EVENT_OBJECT,ATTRIBUTE3,OPERATION_TYPE,
STATUS,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,ATTRIBUTE4)
VALUES (pa_pji_proj_events_log_s.nextval,'DEFER_ROLLUP',p_project_id,p_lock_mode,'I','X',SYSDATE,-1,SYSDATE,-1,p_struc_Ver_Id);
SELECT ATTRIBUTE3 INTO L_ATTRIBUTE3 FROM PA_PJI_PROJ_EVENTS_LOG
where event_object=p_project_id AND EVENT_TYPE='DEFER_ROLLUP' and rownum = 1;
UPDATE PA_PJI_PROJ_EVENTS_LOG
SET ATTRIBUTE3=ATTRIBUTE3||','||p_lock_mode
WHERE event_object=p_project_id AND EVENT_TYPE='DEFER_ROLLUP';
SELECT parent_task_id
FROM pa_tasks
WHERE project_id=p_project_id and task_id=l_task_id;
Select top_task_id from PA_TASKS pt , PA_PROJ_ELEMENT_VERSIONS ppev
where pt.project_id=p_project_id and ppev.proj_element_id=pt.task_id
and ppev.element_version_id=p_task_id and ppev.parent_structure_version_id=p_structure_version_id;*/
Select parent_task_id from PA_TASKS pt , PA_PROJ_ELEMENT_VERSIONS ppev
where pt.project_id=p_project_id and ppev.proj_element_id=pt.task_id
and ppev.element_version_id=l_task_id and ppev.parent_structure_version_id=p_structure_version_id;
UPDATE PA_PROJ_ELEMENT_VERSIONS SET DEFER_CODE='C'
WHERE project_id=p_project_id and element_version_id=p_task_id;
ELSIF p_event_mode in ('ADD_INC','UPDATE') THEN
UPDATE PA_PROJ_ELEMENT_VERSIONS SET DEFER_CODE='C'
WHERE project_id=p_project_id and NVL(DEFER_CODE,'X') not in ('S','P')
AND proj_element_id=p_task_id AND parent_structure_version_id=p_structure_version_id;
ELSIF p_event_mode='DELETE' THEN
OPEN Get_Parent_Task_Id(p_task_id);
UPDATE PA_PROJ_ELEMENT_VERSIONS SET DEFER_CODE='D'
WHERE project_id=p_project_id and NVL(DEFER_CODE,'X')<>'S'
AND proj_element_id=l_parent_task_id AND parent_structure_version_id=p_structure_version_id;
UPDATE PA_PROJ_ELEMENT_VERSIONS SET DEFER_CODE='S'
WHERE project_id=p_project_id AND proj_element_id=l_top_task_id
AND parent_structure_version_id=p_structure_version_id;*/
UPDATE PA_PROJ_ELEMENT_VERSIONS SET DEFER_CODE='S'
WHERE project_id=p_project_id and element_version_id=p_task_id
AND parent_structure_version_id=p_structure_version_id;
UPDATE PA_PROJ_ELEMENT_VERSIONS SET DEFER_CODE='S'
WHERE project_id=p_project_id
AND proj_element_id in (l_parent_task_id,l_top_parent_task_id)
AND parent_structure_version_id=p_structure_version_id;
UPDATE PA_PROJ_ELEMENT_VERSIONS SET DEFER_CODE='S'
WHERE project_id=p_project_id
AND proj_element_id =l_parent_task_id
AND parent_structure_version_id=p_structure_version_id;
UPDATE PA_PROJ_ELEMENT_VERSIONS SET DEFER_CODE='S'
WHERE project_id=p_project_id
AND element_version_id =p_task_id
AND parent_structure_version_id=p_structure_version_id;
UPDATE PA_PROJ_ELEMENT_VERSIONS SET DEFER_CODE='P'
WHERE project_id=p_project_id and NVL(DEFER_CODE,'X') not in ('S','P')
AND proj_element_id=p_task_id AND parent_structure_version_id=p_structure_version_id;