[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_last_update_login number(15);
select period_name,
start_date,
end_date
from pa_periods
where start_date between x_start_period_start_date
and x_end_period_end_date;
select p.period_name,
p.start_date,
p.end_date
from gl_period_statuses p,
pa_implementations i
where p.application_id = pa_period_process_pkg.application_id
and p.set_of_books_id = i.set_of_books_id
and p.adjustment_period_flag = 'N' -- Added for bug 3688017
and p.start_date between x_start_period_start_date
and x_end_period_end_date;
select budget_amount_code
from pa_budget_versions b,pa_budget_types t
where b.budget_version_id = x_version_id
and b.budget_type_code = t.budget_type_code;
x_last_update_login := FND_GLOBAL.LOGIN_ID;
/* Bug# 2107130 Modified the following select statement */
select entry_level_code, categorization_code,
time_phased_type_code, cost_quantity_flag,
raw_cost_flag, burdened_cost_flag,
rev_quantity_flag, revenue_flag
into x_entry_level_code, x_categorization_code,
x_time_phased_type_code, x_cost_quantity_flag,
x_raw_cost_flag, x_burdened_cost_flag,
x_rev_quantity_flag, x_revenue_flag
from pa_budget_entry_methods
where budget_entry_method_code = x_budget_entry_method_code;
select m.resource_list_member_id,
m.track_as_labor_flag,
r.unit_of_measure
into x_uncat_res_list_member_id,
x_uncat_track_as_labor_flag,
x_uncat_unit_of_measure
from pa_resources r,
pa_resource_list_members m,
pa_implementations i,
pa_resource_lists l
where l.uncategorized_flag = 'Y'
and l.resource_list_id = m.resource_list_id
and i.business_group_id = l.business_group_id
and m.resource_id = r.resource_id;
select m.resource_list_member_id,
m.track_as_labor_flag,
r.unit_of_measure
into x_uncat_res_list_member_id,
x_uncat_track_as_labor_flag,
x_uncat_unit_of_measure
from pa_resources r,
pa_resource_list_members m,
pa_implementations i,
pa_resource_lists l
where l.uncategorized_flag = 'Y'
and l.resource_list_id = m.resource_list_id
and i.business_group_id = l.business_group_id
and m.resource_id = r.resource_id
and m.resource_class_code = 'FINANCIAL_ELEMENTS'
AND m.resource_class_id = 4 /* bug 4176179 */
AND m.resource_class_flag = 'Y'; /* bug 4176179 */
select start_date
into x_start_period_start_date
from pa_periods
where period_name = x_start_period;
select end_date
into x_end_period_end_date
from pa_periods
where period_name = x_end_period;
select start_date
into x_start_period_start_date
from gl_period_statuses p,
pa_implementations i
where p.period_name = x_start_period
and p.application_id = pa_period_process_pkg.application_id
and p.set_of_books_id = i.set_of_books_id;
select end_date
into x_end_period_end_date
from gl_period_statuses p,
pa_implementations i
where p.period_name = x_end_period
and p.application_id = pa_period_process_pkg.application_id
and p.set_of_books_id = i.set_of_books_id;
x_err_stage := 'delete budget lines <' || to_char(x_version_id)
|| '><' || x_start_period
|| '><' || x_end_period
|| '>';
select rowid
from pa_budget_lines l
where l.resource_assignment_id in
(select a.resource_assignment_id
from pa_resource_assignments a
where a.budget_version_id = x_version_id)
and l.start_date between x_start_period_start_date and
x_end_period_end_date) loop
pa_budget_lines_v_pkg.delete_row(X_Rowid => bl_rec.rowid);
select period_name,
start_date,
end_date
bulk collect into
l_period_name,
l_start_date,
l_end_date
from pa_periods
where start_date between x_start_period_start_date
and x_end_period_end_date;
SELECT
sum(tot_revenue),
sum(tot_raw_cost),
sum(tot_burdened_cost),
sum(tot_quantity),
sum(tot_labor_hours),
sum(tot_billable_raw_cost),
sum(tot_billable_burdened_cost),
sum(tot_billable_quantity),
sum(tot_billable_labor_hours),
sum(tot_cmt_raw_cost),
sum(tot_cmt_burdened_cost),
Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
INTO
x_revenue,
x_raw_cost,
x_burdened_cost,
x_quantity,
x_labor_hours,
x_billable_raw_cost,
x_billable_burdened_cost,
x_billable_quantity,
x_billable_labor_hours,
x_cmt_raw_cost,
x_cmt_burdened_cost,
x_unit_of_measure
FROM
pa_txn_accum pta
WHERE
pta.project_id = x_project_id
AND EXISTS
( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
WHERE
PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
)
AND pta.pa_period = l_period_name(i);
select p.period_name,
p.start_date,
p.end_date
bulk collect into
l_period_name,
l_start_date,
l_end_date
from gl_period_statuses p,
pa_implementations i
where p.application_id = pa_period_process_pkg.application_id
and p.set_of_books_id = i.set_of_books_id
and p.adjustment_period_flag = 'N'
and p.start_date between x_start_period_start_date
and x_end_period_end_date;
SELECT
sum(tot_revenue),
sum(tot_raw_cost),
sum(tot_burdened_cost),
sum(tot_quantity),
sum(tot_labor_hours),
sum(tot_billable_raw_cost),
sum(tot_billable_burdened_cost),
sum(tot_billable_quantity),
sum(tot_billable_labor_hours),
sum(tot_cmt_raw_cost),
sum(tot_cmt_burdened_cost),
Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
INTO
x_revenue,
x_raw_cost,
x_burdened_cost,
x_quantity,
x_labor_hours,
x_billable_raw_cost,
x_billable_burdened_cost,
x_billable_quantity,
x_billable_labor_hours,
x_cmt_raw_cost,
x_cmt_burdened_cost,
x_unit_of_measure
FROM
pa_txn_accum pta
WHERE
pta.project_id = x_project_id
AND EXISTS
( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
WHERE
PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
)
AND pta.gl_period = l_period_name(i);
select (NVL(quantity, 0) + nvl(TmpActTab(j).labor_hours, 0))
, (NVL(raw_cost,0) + nvl(TmpActTab(j).raw_cost, 0))
, (NVL(burdened_cost,0) + nvl(TmpActTab(j).burdened_cost, 0))
, (NVL(revenue,0) + nvl(TmpActTab(j).revenue, 0))
, pbl.resource_assignment_id
, pbl.rowid
into x_new_quantity,
x_new_raw_cost,
x_new_burdened_cost,
x_new_revenue,
x_new_assignment_id,
x_new_row_id
from pa_budget_lines pbl
where pbl.resource_assignment_id in (
select distinct pbl1.resource_assignment_id
from pa_budget_lines pbl1,
pa_resource_assignments pra,
pa_resource_list_members p1,
pa_resource_list_members p2
where pra.resource_list_member_id = p2.resource_list_member_id
and p1.parent_member_id = p2.resource_list_member_id
and p1.resource_list_member_id = x_uncat_res_list_member_id
and pbl1.resource_assignment_id = pra.resource_assignment_id
and pra.budget_version_id = x_version_id
and pbl1.period_name = TmpActTab(j).period_name
)
and pbl.budget_version_id = x_version_id
and pbl.period_name = TmpActTab(j).period_name ;
pa_budget_lines_v_pkg.update_Row(X_Rowid => x_new_row_id,
X_Resource_Assignment_Id => x_new_assignment_id,
X_Budget_Version_Id => x_version_id,
X_Project_Id => x_project_id,
X_Task_Id => 0,
X_Resource_List_Member_Id => x_uncat_res_list_member_id,
X_Resource_Id => NULL,
X_Resource_Id_Old => NULL,
X_Description => NULL,
X_Start_Date => TmpActTab(j).start_date ,
X_End_Date => TmpActTab(j).end_date,
X_Period_Name => TmpActTab(j).period_name,
X_Quantity => x_new_quantity,
X_Quantity_Old => TmpActTab(j).labor_hours,
X_Unit_Of_Measure => x_uncat_unit_of_measure,
X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
X_Raw_Cost => x_new_raw_cost,
X_Raw_Cost_Old => TmpActTab(j).raw_cost,
X_Burdened_Cost => x_new_burdened_cost,
X_Burdened_Cost_Old => TmpActTab(j).burdened_cost,
X_Revenue => x_new_revenue,
X_Revenue_Old => TmpActTab(j).revenue,
X_Change_Reason_Code => NULL,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => x_created_by,
X_Last_Update_Login => x_last_update_login,
X_Attribute_Category => NULL,
X_Attribute1 => NULL,
X_Attribute2 => NULL,
X_Attribute3 => NULL,
X_Attribute4 => NULL,
X_Attribute5 => NULL,
X_Attribute6 => NULL,
X_Attribute7 => NULL,
X_Attribute8 => NULL,
X_Attribute9 => NULL,
X_Attribute10 => NULL,
X_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
-- X_mrc_flag => 'Y', -- Removed MRC code.
X_Calling_Process => 'PR',
X_raw_cost_source => 'A',
X_burdened_cost_source => 'A',
X_quantity_source => 'A',
X_revenue_source => 'A' );
pa_budget_lines_v_pkg.insert_row (
X_Rowid => x_rowid,
X_Resource_Assignment_Id => x_resource_assignment_id,
X_Budget_Version_Id => x_version_id,
X_Project_Id => x_project_id,
X_Task_Id => 0,
X_Resource_List_Member_Id => x_uncat_res_list_member_id,
X_Description => NULL,
X_Start_Date => TmpActTab(j).start_date,
X_End_Date => TmpActTab(j).end_date,
X_Period_Name => TmpActTab(j).period_name,
X_Quantity => TmpActTab(j).labor_hours,
X_Unit_Of_Measure => x_uncat_unit_of_measure,
X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
X_Raw_Cost => TmpActTab(j).raw_cost,
X_Burdened_Cost => TmpActTab(j).burdened_cost,
X_Revenue => TmpActTab(j).revenue,
X_Change_Reason_Code => NULL,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => x_created_by,
X_Creation_Date => sysdate,
X_Created_By => x_created_by,
X_Last_Update_Login => x_last_update_login,
X_Attribute_Category => NULL,
X_Attribute1 => NULL,
X_Attribute2 => NULL,
X_Attribute3 => NULL,
X_Attribute4 => NULL,
X_Attribute5 => NULL,
X_Attribute6 => NULL,
X_Attribute7 => NULL,
X_Attribute8 => NULL,
X_Attribute9 => NULL,
X_Attribute10 => NULL,
X_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
X_Calling_Process => 'PR',
X_Pm_Product_Code => NULL,
X_Pm_Budget_Line_Reference => NULL,
X_raw_cost_source => 'A',
X_burdened_cost_source => 'A',
X_quantity_source => 'A',
X_revenue_source => 'A' --,
--X_mrc_flag => 'Y' -- FPB2: Added x_mrc_flag for MRC changes
);
Changed the following call to the procedure pa_budget_lines_v_pkg.insert_row
from "Positional Parameter Passing" to "Named Parameter Passing".
pa_budget_lines_v_pkg.insert_row (
X_Rowid => x_rowid,
X_Resource_Assignment_Id => x_resource_assignment_id,
X_Budget_Version_Id => x_version_id,
X_Project_Id => x_project_id,
X_Task_Id => 0,
X_Resource_List_Member_Id => x_uncat_res_list_member_id,
X_Description => NULL,
X_Start_Date => period_rec.start_date,
X_End_Date => period_rec.end_date,
X_Period_Name => period_rec.period_name,
X_Quantity => x_labor_hours, -- Changed for bug# 2107130
X_Unit_Of_Measure => x_uncat_unit_of_measure,
X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
X_Raw_Cost => x_raw_cost,
X_Burdened_Cost => x_burdened_cost,
X_Revenue => x_revenue,
X_Change_Reason_Code => NULL,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => x_created_by,
X_Creation_Date => sysdate,
X_Created_By => x_created_by,
X_Last_Update_Login => x_last_update_login,
X_Attribute_Category => NULL,
X_Attribute1 => NULL,
X_Attribute2 => NULL,
X_Attribute3 => NULL,
X_Attribute4 => NULL,
X_Attribute5 => NULL,
X_Attribute6 => NULL,
X_Attribute7 => NULL,
X_Attribute8 => NULL,
X_Attribute9 => NULL,
X_Attribute10 => NULL,
X_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
X_Calling_Process => 'PR',
X_Pm_Product_Code => NULL,
X_Pm_Budget_Line_Reference => NULL,
X_raw_cost_source => 'A',
X_burdened_cost_source => 'A',
X_quantity_source => 'A',
X_revenue_source => 'A');
select p.period_name,
p.start_date,
p.end_date,
m.resource_list_member_id,
m.resource_id,
m.track_as_labor_flag
bulk collect into
l_period_name,
l_start_date,
l_end_date,
l_resource_list_member_id,
l_resource_id,
l_track_as_labor_flag
from pa_periods p,
pa_resource_list_members m
where m.resource_list_id = x_resource_list_id
and nvl(m.migration_code, 'M') = 'M'
and not exists
(select 1
from pa_resource_list_members m1
where m1.parent_member_id = m.resource_list_member_id)
and p.start_date between x_start_period_start_date
and x_end_period_end_date;
SELECT
sum(tot_revenue),
sum(tot_raw_cost),
sum(tot_burdened_cost),
sum(tot_quantity),
sum(tot_labor_hours),
sum(tot_billable_raw_cost),
sum(tot_billable_burdened_cost),
sum(tot_billable_quantity),
sum(tot_billable_labor_hours),
sum(tot_cmt_raw_cost),
sum(tot_cmt_burdened_cost),
Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
INTO
x_revenue,
x_raw_cost,
x_burdened_cost,
x_quantity,
x_labor_hours,
x_billable_raw_cost,
x_billable_burdened_cost,
x_billable_quantity,
x_billable_labor_hours,
x_cmt_raw_cost,
x_cmt_burdened_cost,
x_unit_of_measure
FROM
pa_txn_accum pta
WHERE
pta.project_id = x_project_id
AND EXISTS
( SELECT 'Yes'
FROM PA_RESOURCE_ACCUM_DETAILS PRAD
WHERE PRAD.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID
AND PRAD.RESOURCE_LIST_MEMBER_ID IN
( -- Fetch both 2nd level and group level resource list member
SELECT PRLM.RESOURCE_LIST_MEMBER_ID
FROM PA_RESOURCE_LIST_MEMBERS PRLM
WHERE (prlm.resource_list_member_id = l_RESOURCE_LIST_MEMBER_ID(i)
or
PRLM.PARENT_MEMBER_ID = l_RESOURCE_LIST_MEMBER_ID(i) )
)
)
AND EXISTS
( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
WHERE
PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
)
AND pta.pa_period = l_period_name(i) ;
select p.period_name,
p.start_date,
p.end_date,
m.resource_list_member_id,
m.resource_id,
m.track_as_labor_flag
bulk collect into
l_period_name,
l_start_date,
l_end_date,
l_resource_list_member_id,
l_resource_id,
l_track_as_labor_flag
from gl_period_statuses p,
pa_implementations i,
pa_resource_list_members m
where m.resource_list_id = x_resource_list_id
and not exists
(select 1
from pa_resource_list_members m1
where m1.parent_member_id = m.resource_list_member_id)
and p.application_id = pa_period_process_pkg.application_id
and p.set_of_books_id = i.set_of_books_id
and p.adjustment_period_flag = 'N'
and p.start_date between x_start_period_start_date
and x_end_period_end_date;
SELECT
sum(tot_revenue),
sum(tot_raw_cost),
sum(tot_burdened_cost),
sum(tot_quantity),
sum(tot_labor_hours),
sum(tot_billable_raw_cost),
sum(tot_billable_burdened_cost),
sum(tot_billable_quantity),
sum(tot_billable_labor_hours),
sum(tot_cmt_raw_cost),
sum(tot_cmt_burdened_cost),
Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
INTO
x_revenue,
x_raw_cost,
x_burdened_cost,
x_quantity,
x_labor_hours,
x_billable_raw_cost,
x_billable_burdened_cost,
x_billable_quantity,
x_billable_labor_hours,
x_cmt_raw_cost,
x_cmt_burdened_cost,
x_unit_of_measure
FROM
pa_txn_accum pta
WHERE
pta.project_id = x_project_id
AND EXISTS
( SELECT 'Yes'
FROM PA_RESOURCE_ACCUM_DETAILS PRAD
WHERE PRAD.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID
AND PRAD.RESOURCE_LIST_MEMBER_ID IN
( -- Fetch both 2nd level and group level resource list member
SELECT PRLM.RESOURCE_LIST_MEMBER_ID
FROM PA_RESOURCE_LIST_MEMBERS PRLM
WHERE (prlm.resource_list_member_id = l_RESOURCE_LIST_MEMBER_ID(i)
or
PRLM.PARENT_MEMBER_ID = l_RESOURCE_LIST_MEMBER_ID(i) )
)
)
AND EXISTS
( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
WHERE
PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
)
AND pta.gl_period = l_period_name(i);
select (NVL(quantity, 0) + nvl(TmpActTab(j).quantity, 0))
, (NVL(raw_cost,0) + nvl(TmpActTab(j).raw_cost, 0))
, (NVL(burdened_cost,0) + nvl(TmpActTab(j).burdened_cost, 0))
, (NVL(revenue,0) + nvl(TmpActTab(j).revenue, 0))
, pbl.resource_assignment_id
, pbl.rowid
into x_new_quantity,
x_new_raw_cost,
x_new_burdened_cost,
x_new_revenue,
x_new_assignment_id,
x_new_row_id
from pa_budget_lines pbl
where pbl.resource_assignment_id in (
select distinct pbl1.resource_assignment_id
from pa_budget_lines pbl1,
pa_resource_assignments pra,
pa_resource_list_members p1,
pa_resource_list_members p2
where pra.resource_list_member_id = p2.resource_list_member_id
and p1.parent_member_id = p2.resource_list_member_id
and p1.resource_list_member_id = TmpActTab(j).resource_list_member_id
and pbl1.resource_assignment_id = pra.resource_assignment_id
and pra.budget_version_id = x_version_id
and pbl1.period_name = TmpActTab(j).period_name
)
and pbl.budget_version_id = x_version_id
and pbl.period_name = TmpActTab(j).period_name ;
pa_budget_lines_v_pkg.update_Row(X_Rowid => x_new_row_id,
X_Resource_Assignment_Id => x_new_assignment_id,
X_Budget_Version_Id => x_version_id,
X_Project_Id => x_project_id,
X_Task_Id => 0,
X_Resource_List_Member_Id => TmpActTab(j).resource_list_member_id,
X_Resource_Id => NULL,
X_Resource_Id_Old => NULL,
X_Description => NULL,
X_Start_Date => TmpActTab(j).start_date,
X_End_Date => TmpActTab(j).end_date,
X_Period_Name => TmpActTab(j).period_name,
X_Quantity => x_new_quantity,
X_Quantity_Old => TmpActTab(j).quantity,
X_Unit_Of_Measure => TmpActTab(j).unit_of_measure,
X_Track_As_Labor_Flag => TmpActTab(j).track_as_labor_flag,
X_Raw_Cost => x_new_raw_cost,
X_Raw_Cost_Old => TmpActTab(j).raw_cost,
X_Burdened_Cost => x_new_burdened_cost,
X_Burdened_Cost_Old => TmpActTab(j).burdened_cost,
X_Revenue => x_new_revenue,
X_Revenue_Old => TmpActTab(j).revenue,
X_Change_Reason_Code => NULL,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => x_created_by,
X_Last_Update_Login => x_last_update_login,
X_Attribute_Category => NULL,
X_Attribute1 => NULL,
X_Attribute2 => NULL,
X_Attribute3 => NULL,
X_Attribute4 => NULL,
X_Attribute5 => NULL,
X_Attribute6 => NULL,
X_Attribute7 => NULL,
X_Attribute8 => NULL,
X_Attribute9 => NULL,
X_Attribute10 => NULL,
X_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
-- X_mrc_flag => 'Y', -- Removed MRC code.
X_Calling_Process => 'PR',
X_raw_cost_source => 'A',
X_burdened_cost_source => 'A',
X_quantity_source => 'A',
X_revenue_source => 'A' );
pa_budget_lines_v_pkg.insert_row (
X_Rowid => x_rowid,
X_Resource_Assignment_Id => x_resource_assignment_id,
X_Budget_Version_Id => x_version_id,
X_Project_Id => x_project_id,
X_Task_Id => 0,
X_Resource_List_Member_Id => TmpActTab(j).resource_list_member_id,
X_Description => NULL,
X_Start_Date => TmpActTab(j).start_date,
X_End_Date => TmpActTab(j).end_date,
X_Period_Name => TmpActTab(j).period_name,
X_Quantity => TmpActTab(j).quantity,
X_Unit_Of_Measure => TmpActTab(j).unit_of_measure,
X_Track_As_Labor_Flag => TmpActTab(j).track_as_labor_flag,
X_Raw_Cost => TmpActTab(j).raw_cost,
X_Burdened_Cost => TmpActTab(j).burdened_cost,
X_Revenue => TmpActTab(j).revenue,
X_Change_Reason_Code => NULL,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => x_created_by,
X_Creation_Date => sysdate,
X_Created_By => x_created_by,
X_Last_Update_Login => x_last_update_login,
X_Attribute_Category => NULL,
X_Attribute1 => NULL,
X_Attribute2 => NULL,
X_Attribute3 => NULL,
X_Attribute4 => NULL,
X_Attribute5 => NULL,
X_Attribute6 => NULL,
X_Attribute7 => NULL,
X_Attribute8 => NULL,
X_Attribute9 => NULL,
X_Attribute10 => NULL,
X_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
X_Calling_Process => 'PR',
X_Pm_Product_Code => NULL,
X_Pm_Budget_Line_Reference => NULL,
X_raw_cost_source => 'A',
X_burdened_cost_source => 'A',
X_quantity_source => 'A',
X_revenue_source => 'A' --,
--X_mrc_flag => 'Y' -- FPB2: Added x_mrc_flag for MRC changes
);
for res_rec in (select m.resource_list_member_id,
m.resource_id,
m.track_as_labor_flag
from pa_resource_list_members m
where m.resource_list_id = x_resource_list_id
and nvl(m.migration_code, 'M') = 'M'
and not exists
(select 1
from pa_resource_list_members m1
where m1.parent_member_id =
m.resource_list_member_id)
) loop
x_err_stage := 'process period and resource <'
|| period_rec.period_name
|| '><' || to_char(res_rec.resource_list_member_id)
|| '>';
Changed the following call to the procedure pa_budget_lines_v_pkg.insert_row
from "Positional Parameter Passing" to "Named Parameter Passing".
pa_budget_lines_v_pkg.insert_row (
X_Rowid => x_rowid,
X_Resource_Assignment_Id => x_resource_assignment_id,
X_Budget_Version_Id => x_version_id,
X_Project_Id => x_project_id,
X_Task_Id => 0,
X_Resource_List_Member_Id => res_rec.resource_list_member_id,
X_Description => NULL,
X_Start_Date => period_rec.start_date,
X_End_Date => period_rec.end_date,
X_Period_Name => period_rec.period_name,
X_Quantity => x_quantity,
X_Unit_Of_Measure => x_unit_of_measure,
X_Track_As_Labor_Flag => res_rec.track_as_labor_flag,
X_Raw_Cost => x_raw_cost,
X_Burdened_Cost => x_burdened_cost,
X_Revenue => x_revenue,
X_Change_Reason_Code => NULL,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => x_created_by,
X_Creation_Date => sysdate,
X_Created_By => x_created_by,
X_Last_Update_Login => x_last_update_login,
X_Attribute_Category => NULL,
X_Attribute1 => NULL,
X_Attribute2 => NULL,
X_Attribute3 => NULL,
X_Attribute4 => NULL,
X_Attribute5 => NULL,
X_Attribute6 => NULL,
X_Attribute7 => NULL,
X_Attribute8 => NULL,
X_Attribute9 => NULL,
X_Attribute10 => NULL,
X_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
X_Calling_Process => 'PR',
X_Pm_Product_Code => NULL,
X_Pm_Budget_Line_Reference => NULL,
X_raw_cost_source => 'A',
X_burdened_cost_source => 'A',
X_quantity_source => 'A',
X_revenue_source => 'A');
select p.period_name,
p.start_date,
p.end_date,
t.task_id
bulk collect into
l_period_name,
l_start_date,
l_end_date,
l_task_id
from pa_periods p,
pa_tasks t
where t.project_id = x_project_id
and t.task_id = t.top_task_id
and p.start_date between x_start_period_start_date
and x_end_period_end_date;
SELECT
sum(tot_revenue),
sum(tot_raw_cost),
sum(tot_burdened_cost),
sum(tot_quantity),
sum(tot_labor_hours),
sum(tot_billable_raw_cost),
sum(tot_billable_burdened_cost),
sum(tot_billable_quantity),
sum(tot_billable_labor_hours),
sum(tot_cmt_raw_cost),
sum(tot_cmt_burdened_cost),
Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
INTO
x_revenue,
x_raw_cost,
x_burdened_cost,
x_quantity,
x_labor_hours,
x_billable_raw_cost,
x_billable_burdened_cost,
x_billable_quantity,
x_billable_labor_hours,
x_cmt_raw_cost,
x_cmt_burdened_cost,
x_unit_of_measure
FROM
pa_txn_accum pta
WHERE
pta.project_id = x_project_id
AND pta.task_id IN
(SELECT
task_id
FROM
pa_tasks
CONNECT BY PRIOR task_id = parent_task_id
START WITH task_id = l_task_id(i)
)
AND EXISTS
( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
WHERE
PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
)
AND pta.pa_period = l_period_name(i) ;
select p.period_name,
p.start_date,
p.end_date,
t.task_id
bulk collect into
l_period_name,
l_start_date,
l_end_date,
l_task_id
from gl_period_statuses p,
pa_implementations i,
pa_tasks t
where t.project_id = x_project_id
and t.task_id = t.top_task_id
and p.application_id = pa_period_process_pkg.application_id
and p.set_of_books_id = i.set_of_books_id
and p.adjustment_period_flag = 'N'
and p.start_date between x_start_period_start_date
and x_end_period_end_date;
SELECT
sum(tot_revenue),
sum(tot_raw_cost),
sum(tot_burdened_cost),
sum(tot_quantity),
sum(tot_labor_hours),
sum(tot_billable_raw_cost),
sum(tot_billable_burdened_cost),
sum(tot_billable_quantity),
sum(tot_billable_labor_hours),
sum(tot_cmt_raw_cost),
sum(tot_cmt_burdened_cost),
Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
INTO
x_revenue,
x_raw_cost,
x_burdened_cost,
x_quantity,
x_labor_hours,
x_billable_raw_cost,
x_billable_burdened_cost,
x_billable_quantity,
x_billable_labor_hours,
x_cmt_raw_cost,
x_cmt_burdened_cost,
x_unit_of_measure
FROM
pa_txn_accum pta
WHERE
pta.project_id = x_project_id
AND pta.task_id IN
(SELECT
task_id
FROM
pa_tasks
CONNECT BY PRIOR task_id = parent_task_id
START WITH task_id = l_task_id(i)
)
AND EXISTS
( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
WHERE
PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
)
AND pta.gl_period = l_period_name(i);
select (NVL(quantity, 0) + nvl(TmpActTab(j).labor_hours, 0))
, (NVL(raw_cost,0) + nvl(TmpActTab(j).raw_cost, 0))
, (NVL(burdened_cost,0) + nvl(TmpActTab(j).burdened_cost, 0))
, (NVL(revenue,0) + nvl(TmpActTab(j).revenue, 0))
, pbl.resource_assignment_id
, pbl.rowid
into x_new_quantity,
x_new_raw_cost,
x_new_burdened_cost,
x_new_revenue,
x_new_assignment_id,
x_new_row_id
from pa_budget_lines pbl
where pbl.resource_assignment_id in (
select distinct pbl1.resource_assignment_id
from pa_budget_lines pbl1,
pa_resource_assignments pra,
pa_resource_list_members p1,
pa_resource_list_members p2
where pra.resource_list_member_id = p2.resource_list_member_id
and p1.parent_member_id = p2.resource_list_member_id
and p1.resource_list_member_id = x_uncat_res_list_member_id
and pbl1.resource_assignment_id = pra.resource_assignment_id
and pra.budget_version_id = x_version_id
and pra.task_id = TmpActTab(j).task_id
and pbl1.period_name = TmpActTab(j).period_name
)
and pbl.budget_version_id = x_version_id
and pbl.period_name =TmpActTab(j).period_name ;
pa_budget_lines_v_pkg.update_Row(X_Rowid => x_new_row_id,
X_Resource_Assignment_Id => x_new_assignment_id,
X_Budget_Version_Id => x_version_id,
X_Project_Id => x_project_id,
X_Task_Id => TmpActTab(j).task_id,
X_Resource_List_Member_Id => x_uncat_res_list_member_id,
X_Resource_Id => NULL,
X_Resource_Id_Old => NULL,
X_Description => NULL,
X_Start_Date => TmpActTab(j).start_date,
X_End_Date => TmpActTab(j).end_date,
X_Period_Name => TmpActTab(j).period_name,
X_Quantity => x_new_quantity,
X_Quantity_Old => TmpActTab(j).labor_hours,
X_Unit_Of_Measure => x_uncat_unit_of_measure,
X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
X_Raw_Cost => x_new_raw_cost,
X_Raw_Cost_Old => TmpActTab(j).raw_cost,
X_Burdened_Cost => x_new_burdened_cost,
X_Burdened_Cost_Old => TmpActTab(j).burdened_cost,
X_Revenue => x_new_revenue,
X_Revenue_Old => TmpActTab(j).revenue,
X_Change_Reason_Code => NULL,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => x_created_by,
X_Last_Update_Login => x_last_update_login,
X_Attribute_Category => NULL,
X_Attribute1 => NULL,
X_Attribute2 => NULL,
X_Attribute3 => NULL,
X_Attribute4 => NULL,
X_Attribute5 => NULL,
X_Attribute6 => NULL,
X_Attribute7 => NULL,
X_Attribute8 => NULL,
X_Attribute9 => NULL,
X_Attribute10 => NULL,
X_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
-- X_mrc_flag => 'Y', -- Removed MRC code.
X_Calling_Process => 'PR',
X_raw_cost_source => 'A',
X_burdened_cost_source => 'A',
X_quantity_source => 'A',
X_revenue_source => 'A' );
pa_budget_lines_v_pkg.insert_row (
X_Rowid => x_rowid,
X_Resource_Assignment_Id => x_resource_assignment_id,
X_Budget_Version_Id => x_version_id,
X_Project_Id => x_project_id,
X_Task_Id => TmpActTab(j).task_id,
X_Resource_List_Member_Id => x_uncat_res_list_member_id,
X_Description => NULL,
X_Start_Date => TmpActTab(j).start_date,
X_End_Date => TmpActTab(j).end_date,
X_Period_Name => TmpActTab(j).period_name,
X_Quantity => TmpActTab(j).labor_hours,
X_Unit_Of_Measure => x_uncat_unit_of_measure,
X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
X_Raw_Cost => TmpActTab(j).raw_cost,
X_Burdened_Cost => TmpActTab(j).burdened_cost,
X_Revenue => TmpActTab(j).revenue,
X_Change_Reason_Code => NULL,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => x_created_by,
X_Creation_Date => sysdate,
X_Created_By => x_created_by,
X_Last_Update_Login => x_last_update_login,
X_Attribute_Category => NULL,
X_Attribute1 => NULL,
X_Attribute2 => NULL,
X_Attribute3 => NULL,
X_Attribute4 => NULL,
X_Attribute5 => NULL,
X_Attribute6 => NULL,
X_Attribute7 => NULL,
X_Attribute8 => NULL,
X_Attribute9 => NULL,
X_Attribute10 => NULL,
X_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
X_Calling_Process => 'PR',
X_Pm_Product_Code => NULL,
X_Pm_Budget_Line_Reference => NULL,
X_raw_cost_source => 'A',
X_burdened_cost_source => 'A',
X_quantity_source => 'A',
X_revenue_source => 'A' --,
--X_mrc_flag => 'Y' -- FPB2: Added x_mrc_flag for MRC changes
);
select p.period_name,
p.start_date,
p.end_date,
t.task_id,
m.resource_list_member_id,
m.resource_id,
m.track_as_labor_flag
bulk collect into
l_period_name,
l_start_date,
l_end_date,
l_task_id,
l_resource_list_member_id,
l_resource_id,
l_track_as_labor_flag
from pa_periods p,
pa_tasks t,
pa_resource_list_members m
where m.resource_list_id = x_resource_list_id
and nvl(m.migration_code, 'M') = 'M'
and not exists
(select 1
from pa_resource_list_members m1
where m1.parent_member_id =
m.resource_list_member_id)
and t.project_id = x_project_id
and t.task_id = t.top_task_id
and p.start_date between x_start_period_start_date
and x_end_period_end_date;
SELECT
sum(tot_revenue),
sum(tot_raw_cost),
sum(tot_burdened_cost),
sum(tot_quantity),
sum(tot_labor_hours),
sum(tot_billable_raw_cost),
sum(tot_billable_burdened_cost),
sum(tot_billable_quantity),
sum(tot_billable_labor_hours),
sum(tot_cmt_raw_cost),
sum(tot_cmt_burdened_cost),
Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
INTO
x_revenue,
x_raw_cost,
x_burdened_cost,
x_quantity,
x_labor_hours,
x_billable_raw_cost,
x_billable_burdened_cost,
x_billable_quantity,
x_billable_labor_hours,
x_cmt_raw_cost,
x_cmt_burdened_cost,
x_unit_of_measure
FROM
pa_txn_accum pta
WHERE
pta.project_id = x_project_id
AND pta.task_id IN
(SELECT
task_id
FROM
pa_tasks
CONNECT BY PRIOR task_id = parent_task_id
START WITH task_id = l_task_id(i)
)
AND EXISTS
( SELECT 'Yes'
FROM PA_RESOURCE_ACCUM_DETAILS PRAD
WHERE PRAD.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID
AND PRAD.RESOURCE_LIST_MEMBER_ID IN
( -- Fetch both 2nd level and group level resource list member
SELECT PRLM.RESOURCE_LIST_MEMBER_ID
FROM PA_RESOURCE_LIST_MEMBERS PRLM
WHERE (prlm.resource_list_member_id = l_RESOURCE_LIST_MEMBER_ID(i)
or
PRLM.PARENT_MEMBER_ID = l_RESOURCE_LIST_MEMBER_ID(i) )
)
)
AND EXISTS
( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
WHERE
PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
)
AND pta.pa_period = l_period_name(i) ;
x_err_stage := 'PA: Period Before inserting into TmpActTab';
x_err_stage := 'PA: Period After inserting into TmpActTab';
select p.period_name,
p.start_date,
p.end_date,
t.task_id,
m.resource_list_member_id,
m.resource_id,
m.track_as_labor_flag
bulk collect into
l_period_name,
l_start_date,
l_end_date,
l_task_id,
l_resource_list_member_id,
l_resource_id,
l_track_as_labor_flag
from gl_period_statuses p,
pa_implementations i,
pa_tasks t,
pa_resource_list_members m
where m.resource_list_id = x_resource_list_id
and not exists
(select 1
from pa_resource_list_members m1
where m1.parent_member_id =
m.resource_list_member_id)
and t.project_id = x_project_id
and t.task_id = t.top_task_id
and p.application_id = pa_period_process_pkg.application_id
and p.set_of_books_id = i.set_of_books_id
and p.adjustment_period_flag = 'N'
and p.start_date between x_start_period_start_date
and x_end_period_end_date;
SELECT
sum(tot_revenue),
sum(tot_raw_cost),
sum(tot_burdened_cost),
sum(tot_quantity),
sum(tot_labor_hours),
sum(tot_billable_raw_cost),
sum(tot_billable_burdened_cost),
sum(tot_billable_quantity),
sum(tot_billable_labor_hours),
sum(tot_cmt_raw_cost),
sum(tot_cmt_burdened_cost),
Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
INTO
x_revenue,
x_raw_cost,
x_burdened_cost,
x_quantity,
x_labor_hours,
x_billable_raw_cost,
x_billable_burdened_cost,
x_billable_quantity,
x_billable_labor_hours,
x_cmt_raw_cost,
x_cmt_burdened_cost,
x_unit_of_measure
FROM
pa_txn_accum pta
WHERE
pta.project_id = x_project_id
AND pta.task_id IN
(SELECT
task_id
FROM
pa_tasks
CONNECT BY PRIOR task_id = parent_task_id
START WITH task_id = l_task_id(i)
)
AND EXISTS
( SELECT 'Yes'
FROM PA_RESOURCE_ACCUM_DETAILS PRAD
WHERE PRAD.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID
AND PRAD.RESOURCE_LIST_MEMBER_ID IN
( -- Fetch both 2nd level and group level resource list member
SELECT PRLM.RESOURCE_LIST_MEMBER_ID
FROM PA_RESOURCE_LIST_MEMBERS PRLM
WHERE (prlm.resource_list_member_id = l_RESOURCE_LIST_MEMBER_ID(i)
or
PRLM.PARENT_MEMBER_ID = l_RESOURCE_LIST_MEMBER_ID(i) )
)
)
AND EXISTS
( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
WHERE
PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
)
AND pta.gl_period = l_period_name(i);
x_err_stage := 'Before inserting into TmpActTab';
x_err_stage := 'After inserting into TmpActTab';
select (NVL(quantity, 0) + nvl(TmpActTab(j).quantity, 0))
, (NVL(raw_cost,0) + nvl(TmpActTab(j).raw_cost, 0))
, (NVL(burdened_cost,0) + nvl(TmpActTab(j).burdened_cost, 0))
, (NVL(revenue,0) + nvl(TmpActTab(j).revenue, 0))
, pbl.resource_assignment_id
, pbl.rowid
into x_new_quantity,
x_new_raw_cost,
x_new_burdened_cost,
x_new_revenue,
x_new_assignment_id,
x_new_row_id
from pa_budget_lines pbl
where pbl.resource_assignment_id in (
select distinct pbl1.resource_assignment_id
from pa_budget_lines pbl1,
pa_resource_assignments pra,
pa_resource_list_members p1,
pa_resource_list_members p2
where pra.resource_list_member_id = p2.resource_list_member_id
and p1.parent_member_id = p2.resource_list_member_id
and p1.resource_list_member_id = TmpActTab(j).resource_list_member_id
and pbl1.resource_assignment_id = pra.resource_assignment_id
and pra.budget_version_id = x_version_id
and pra.task_id = TmpActTab(j).task_id
and pbl1.period_name = TmpActTab(j).period_name
)
and pbl.budget_version_id = x_version_id
and pbl.period_name = TmpActTab(j).period_name ;
pa_budget_lines_v_pkg.update_Row(X_Rowid => x_new_row_id,
X_Resource_Assignment_Id => x_new_assignment_id,
X_Budget_Version_Id => x_version_id,
X_Project_Id => x_project_id,
X_Task_Id => TmpActTab(j).task_id,
X_Resource_List_Member_Id => TmpActTab(j).resource_list_member_id,
X_Resource_Id => NULL,
X_Resource_Id_Old => NULL,
X_Description => NULL,
X_Start_Date => TmpActTab(j).start_date,
X_End_Date => TmpActTab(j).end_date,
X_Period_Name => TmpActTab(j).period_name,
X_Quantity => x_new_quantity,
X_Quantity_Old => TmpActTab(j).quantity,
X_Unit_Of_Measure => TmpActTab(j).unit_of_measure,
X_Track_As_Labor_Flag => TmpActTab(j).track_as_labor_flag,
X_Raw_Cost => x_new_raw_cost,
X_Raw_Cost_Old => TmpActTab(j).raw_cost,
X_Burdened_Cost => x_new_burdened_cost,
X_Burdened_Cost_Old => TmpActTab(j).burdened_cost,
X_Revenue => x_new_revenue,
X_Revenue_Old => TmpActTab(j).revenue,
X_Change_Reason_Code => NULL,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => x_created_by,
X_Last_Update_Login => x_last_update_login,
X_Attribute_Category => NULL,
X_Attribute1 => NULL,
X_Attribute2 => NULL,
X_Attribute3 => NULL,
X_Attribute4 => NULL,
X_Attribute5 => NULL,
X_Attribute6 => NULL,
X_Attribute7 => NULL,
X_Attribute8 => NULL,
X_Attribute9 => NULL,
X_Attribute10 => NULL,
X_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
-- X_mrc_flag => 'Y', -- Removed MRC code.
X_Calling_Process => 'PR',
X_raw_cost_source => 'A',
X_burdened_cost_source => 'A',
X_quantity_source => 'A',
X_revenue_source => 'A' );
pa_budget_lines_v_pkg.insert_row (
X_Rowid => x_rowid,
X_Resource_Assignment_Id => x_resource_assignment_id,
X_Budget_Version_Id => x_version_id,
X_Project_Id => x_project_id,
X_Task_Id => TmpActTab(j).task_id,
X_Resource_List_Member_Id => TmpActTab(j).resource_list_member_id,
X_Description => NULL,
X_Start_Date => TmpActTab(j).start_date,
X_End_Date => TmpActTab(j).end_date,
X_Period_Name => TmpActTab(j).period_name,
X_Quantity => TmpActTab(j).quantity,
X_Unit_Of_Measure => TmpActTab(j).unit_of_measure,
X_Track_As_Labor_Flag => TmpActTab(j).track_as_labor_flag,
X_Raw_Cost => TmpActTab(j).raw_cost,
X_Burdened_Cost => TmpActTab(j).burdened_cost,
X_Revenue => TmpActTab(j).revenue,
X_Change_Reason_Code => NULL,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => x_created_by,
X_Creation_Date => sysdate,
X_Created_By => x_created_by,
X_Last_Update_Login => x_last_update_login,
X_Attribute_Category => NULL,
X_Attribute1 => NULL,
X_Attribute2 => NULL,
X_Attribute3 => NULL,
X_Attribute4 => NULL,
X_Attribute5 => NULL,
X_Attribute6 => NULL,
X_Attribute7 => NULL,
X_Attribute8 => NULL,
X_Attribute9 => NULL,
X_Attribute10 => NULL,
X_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
X_Calling_Process => 'PR',
X_Pm_Product_Code => NULL,
X_Pm_Budget_Line_Reference => NULL,
X_raw_cost_source => 'A',
X_burdened_cost_source => 'A',
X_quantity_source => 'A',
X_revenue_source => 'A' --,
--X_mrc_flag => 'Y' -- FPB2: Added x_mrc_flag for MRC changes
);
for top_task_rec in (select t.task_id
from pa_tasks t
where t.project_id = x_project_id
and t.task_id = t.top_task_id) loop
x_raw_cost:= 0;
Changed the following call to the procedure pa_budget_lines_v_pkg.insert_row
from "Positional Parameter Passing" to "Named Parameter Passing".
pa_budget_lines_v_pkg.insert_row (
X_Rowid => x_rowid,
X_Resource_Assignment_Id => x_resource_assignment_id,
X_Budget_Version_Id => x_version_id,
X_Project_Id => x_project_id,
X_Task_Id => top_task_rec.task_id,
X_Resource_List_Member_Id => x_uncat_res_list_member_id,
X_Description => NULL,
X_Start_Date => period_rec.start_date,
X_End_Date => period_rec.end_date,
X_Period_Name => period_rec.period_name,
X_Quantity => x_labor_hours, -- Changed for bug# 2107130
X_Unit_Of_Measure => x_uncat_unit_of_measure,
X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
X_Raw_Cost => x_raw_cost,
X_Burdened_Cost => x_burdened_cost,
X_Revenue => x_revenue,
X_Change_Reason_Code => NULL,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => x_created_by,
X_Creation_Date => sysdate,
X_Created_By => x_created_by,
X_Last_Update_Login => x_last_update_login,
X_Attribute_Category => NULL,
X_Attribute1 => NULL,
X_Attribute2 => NULL,
X_Attribute3 => NULL,
X_Attribute4 => NULL,
X_Attribute5 => NULL,
X_Attribute6 => NULL,
X_Attribute7 => NULL,
X_Attribute8 => NULL,
X_Attribute9 => NULL,
X_Attribute10 => NULL,
X_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
X_Calling_Process => 'PR',
X_Pm_Product_Code => NULL,
X_Pm_Budget_Line_Reference => NULL,
X_raw_cost_source => 'A',
X_burdened_cost_source => 'A',
X_quantity_source => 'A',
X_revenue_source => 'A');
for res_rec in (select m.resource_list_member_id,
m.resource_id,
m.track_as_labor_flag
from pa_resource_list_members m
where m.resource_list_id =
x_resource_list_id
and nvl(m.migration_code, 'M') = 'M'
and not exists
(select 1
from pa_resource_list_members m1
where m1.parent_member_id =
m.resource_list_member_id)
) loop
x_quantity:= 0;
Changed the following call to the procedure pa_budget_lines_v_pkg.insert_row
from "Positional Parameter Passing" to "Named Parameter Passing".
pa_budget_lines_v_pkg.insert_row (
X_Rowid => x_rowid,
X_Resource_Assignment_Id => x_resource_assignment_id,
X_Budget_Version_Id => x_version_id,
X_Project_Id => x_project_id,
X_Task_Id => top_task_rec.task_id,
X_Resource_List_Member_Id => res_rec.resource_list_member_id,
X_Description => NULL,
X_Start_Date => period_rec.start_date,
X_End_Date => period_rec.end_date,
X_Period_Name => period_rec.period_name,
X_Quantity => x_quantity,
X_Unit_Of_Measure => x_unit_of_measure,
X_Track_As_Labor_Flag => res_rec.track_as_labor_flag,
X_Raw_Cost => x_raw_cost,
X_Burdened_Cost => x_burdened_cost,
X_Revenue => x_revenue,
X_Change_Reason_Code => NULL,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => x_created_by,
X_Creation_Date => sysdate,
X_Created_By => x_created_by,
X_Last_Update_Login => x_last_update_login,
X_Attribute_Category => NULL,
X_Attribute1 => NULL,
X_Attribute2 => NULL,
X_Attribute3 => NULL,
X_Attribute4 => NULL,
X_Attribute5 => NULL,
X_Attribute6 => NULL,
X_Attribute7 => NULL,
X_Attribute8 => NULL,
X_Attribute9 => NULL,
X_Attribute10 => NULL,
X_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
X_Calling_Process => 'PR',
X_Pm_Product_Code => NULL,
X_Pm_Budget_Line_Reference => NULL,
X_raw_cost_source => 'A',
X_burdened_cost_source => 'A',
X_quantity_source => 'A',
X_revenue_source => 'A');
select p.period_name,
p.start_date,
p.end_date,
t.task_id
bulk collect into
l_period_name,
l_start_date,
l_end_date,
l_task_id
from pa_periods p,
pa_tasks t
where t.project_id = x_project_id
and not exists
(select 1
from pa_tasks t1
where t1.parent_task_id = t.task_id)
and p.start_date between x_start_period_start_date
and x_end_period_end_date;
SELECT
sum(tot_revenue),
sum(tot_raw_cost),
sum(tot_burdened_cost),
sum(tot_quantity),
sum(tot_labor_hours),
sum(tot_billable_raw_cost),
sum(tot_billable_burdened_cost),
sum(tot_billable_quantity),
sum(tot_billable_labor_hours),
sum(tot_cmt_raw_cost),
sum(tot_cmt_burdened_cost),
Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
INTO
x_revenue,
x_raw_cost,
x_burdened_cost,
x_quantity,
x_labor_hours,
x_billable_raw_cost,
x_billable_burdened_cost,
x_billable_quantity,
x_billable_labor_hours,
x_cmt_raw_cost,
x_cmt_burdened_cost,
x_unit_of_measure
FROM
pa_txn_accum pta
WHERE
pta.project_id = x_project_id
AND pta.task_id IN
(SELECT
task_id
FROM
pa_tasks
CONNECT BY PRIOR task_id = parent_task_id
START WITH task_id = l_task_id(i)
)
AND EXISTS
( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
WHERE
PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
)
AND pta.pa_period = l_period_name(i) ;
x_err_stage := 'PA: Period Before inserting into tmp table';
x_err_stage := 'PA: Period After inserting into tmp table';
select p.period_name,
p.start_date,
p.end_date,
t.task_id
bulk collect into
l_period_name,
l_start_date,
l_end_date,
l_task_id
from gl_period_statuses p,
pa_implementations i,
pa_tasks t
where t.project_id = x_project_id
and not exists
(select 1
from pa_tasks t1
where t1.parent_task_id = t.task_id)
and p.application_id = pa_period_process_pkg.application_id
and p.set_of_books_id = i.set_of_books_id
and p.adjustment_period_flag = 'N'
and p.start_date between x_start_period_start_date
and x_end_period_end_date;
SELECT
sum(tot_revenue),
sum(tot_raw_cost),
sum(tot_burdened_cost),
sum(tot_quantity),
sum(tot_labor_hours),
sum(tot_billable_raw_cost),
sum(tot_billable_burdened_cost),
sum(tot_billable_quantity),
sum(tot_billable_labor_hours),
sum(tot_cmt_raw_cost),
sum(tot_cmt_burdened_cost),
Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
INTO
x_revenue,
x_raw_cost,
x_burdened_cost,
x_quantity,
x_labor_hours,
x_billable_raw_cost,
x_billable_burdened_cost,
x_billable_quantity,
x_billable_labor_hours,
x_cmt_raw_cost,
x_cmt_burdened_cost,
x_unit_of_measure
FROM
pa_txn_accum pta
WHERE
pta.project_id = x_project_id
AND pta.task_id IN
(SELECT
task_id
FROM
pa_tasks
CONNECT BY PRIOR task_id = parent_task_id
START WITH task_id = l_task_id(i)
)
AND EXISTS
( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
WHERE
PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
)
AND pta.gl_period = l_period_name(i);
select (NVL(quantity, 0) + nvl(TmpActTab(j).labor_hours, 0))
, (NVL(raw_cost,0) + nvl(TmpActTab(j).raw_cost, 0))
, (NVL(burdened_cost,0) + nvl(TmpActTab(j).revenue, 0))
, (NVL(revenue,0) + nvl(TmpActTab(j).revenue, 0))
, pbl.resource_assignment_id
, pbl.rowid
into x_new_quantity,
x_new_raw_cost,
x_new_burdened_cost,
x_new_revenue,
x_new_assignment_id,
x_new_row_id
from pa_budget_lines pbl
where pbl.resource_assignment_id in (
select distinct pbl1.resource_assignment_id
from pa_budget_lines pbl1,
pa_resource_assignments pra,
pa_resource_list_members p1,
pa_resource_list_members p2
where pra.resource_list_member_id = p2.resource_list_member_id
and p1.parent_member_id = p2.resource_list_member_id
and p1.resource_list_member_id = x_uncat_res_list_member_id
and pbl1.resource_assignment_id = pra.resource_assignment_id
and pra.budget_version_id = x_version_id
and pra.task_id = TmpActTab(j).task_id
and pbl1.period_name = TmpActTab(j).period_name
)
and pbl.budget_version_id = x_version_id
and pbl.period_name = TmpActTab(j).period_name ;
pa_budget_lines_v_pkg.update_Row(X_Rowid => x_new_row_id,
X_Resource_Assignment_Id => x_new_assignment_id,
X_Budget_Version_Id => x_version_id,
X_Project_Id => x_project_id,
X_Task_Id => TmpActTab(j).task_id,
X_Resource_List_Member_Id => x_uncat_res_list_member_id,
X_Resource_Id => NULL,
X_Resource_Id_Old => NULL,
X_Description => NULL,
X_Start_Date => TmpActTab(j).start_date,
X_End_Date => TmpActTab(j).end_date,
X_Period_Name => TmpActTab(j).period_name,
X_Quantity => x_new_quantity,
X_Quantity_Old => TmpActTab(j).labor_hours,
X_Unit_Of_Measure => x_uncat_unit_of_measure,
X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
X_Raw_Cost => x_new_raw_cost,
X_Raw_Cost_Old => TmpActTab(j).raw_cost,
X_Burdened_Cost => x_new_burdened_cost,
X_Burdened_Cost_Old => TmpActTab(j).burdened_cost,
X_Revenue => x_new_revenue,
X_Revenue_Old => TmpActTab(j).revenue,
X_Change_Reason_Code => NULL,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => x_created_by,
X_Last_Update_Login => x_last_update_login,
X_Attribute_Category => NULL,
X_Attribute1 => NULL,
X_Attribute2 => NULL,
X_Attribute3 => NULL,
X_Attribute4 => NULL,
X_Attribute5 => NULL,
X_Attribute6 => NULL,
X_Attribute7 => NULL,
X_Attribute8 => NULL,
X_Attribute9 => NULL,
X_Attribute10 => NULL,
X_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
-- X_mrc_flag => 'Y', -- Removed MRC code.
X_Calling_Process => 'PR',
X_raw_cost_source => 'A',
X_burdened_cost_source => 'A',
X_quantity_source => 'A',
X_revenue_source => 'A' );
pa_budget_lines_v_pkg.insert_row (
X_Rowid => x_rowid,
X_Resource_Assignment_Id => x_resource_assignment_id,
X_Budget_Version_Id => x_version_id,
X_Project_Id => x_project_id,
X_Task_Id => TmpActTab(j).task_id,
X_Resource_List_Member_Id => x_uncat_res_list_member_id,
X_Description => NULL,
X_Start_Date => TmpActTab(j).start_date,
X_End_Date => TmpActTab(j).end_date,
X_Period_Name => TmpActTab(j).period_name,
X_Quantity => TmpActTab(j).labor_hours,
X_Unit_Of_Measure => x_uncat_unit_of_measure,
X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
X_Raw_Cost => TmpActTab(j).raw_cost,
X_Burdened_Cost => TmpActTab(j).burdened_cost,
X_Revenue => TmpActTab(j).revenue,
X_Change_Reason_Code => NULL,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => x_created_by,
X_Creation_Date => sysdate,
X_Created_By => x_created_by,
X_Last_Update_Login => x_last_update_login,
X_Attribute_Category => NULL,
X_Attribute1 => NULL,
X_Attribute2 => NULL,
X_Attribute3 => NULL,
X_Attribute4 => NULL,
X_Attribute5 => NULL,
X_Attribute6 => NULL,
X_Attribute7 => NULL,
X_Attribute8 => NULL,
X_Attribute9 => NULL,
X_Attribute10 => NULL,
X_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
X_Calling_Process => 'PR',
X_Pm_Product_Code => NULL,
X_Pm_Budget_Line_Reference => NULL,
X_raw_cost_source => 'A',
X_burdened_cost_source => 'A',
X_quantity_source => 'A',
X_revenue_source => 'A' --,
--X_mrc_flag => 'Y' -- FPB2: Added x_mrc_flag for MRC changes
);
select p.period_name,
p.start_date,
p.end_date,
t.task_id,
m.resource_list_member_id,
m.resource_id,
m.track_as_labor_flag
bulk collect into
l_period_name,
l_start_date,
l_end_date,
l_task_id,
l_resource_list_member_id,
l_resource_id,
l_track_as_labor_flag
from pa_periods p,
pa_tasks t,
pa_resource_list_members m
where m.resource_list_id = x_resource_list_id
and nvl(m.migration_code, 'M') = 'M'
and not exists
(select 1
from pa_resource_list_members m1
where m1.parent_member_id =
m.resource_list_member_id)
and t.project_id = x_project_id
and not exists
(select 1
from pa_tasks t1
where t1.parent_task_id = t.task_id)
and p.start_date between x_start_period_start_date
and x_end_period_end_date;
SELECT
sum(tot_revenue),
sum(tot_raw_cost),
sum(tot_burdened_cost),
sum(tot_quantity),
sum(tot_labor_hours),
sum(tot_billable_raw_cost),
sum(tot_billable_burdened_cost),
sum(tot_billable_quantity),
sum(tot_billable_labor_hours),
sum(tot_cmt_raw_cost),
sum(tot_cmt_burdened_cost),
Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
INTO
x_revenue,
x_raw_cost,
x_burdened_cost,
x_quantity,
x_labor_hours,
x_billable_raw_cost,
x_billable_burdened_cost,
x_billable_quantity,
x_billable_labor_hours,
x_cmt_raw_cost,
x_cmt_burdened_cost,
x_unit_of_measure
FROM
pa_txn_accum pta
WHERE
pta.project_id = x_project_id
AND pta.task_id IN
(SELECT
task_id
FROM
pa_tasks
CONNECT BY PRIOR task_id = parent_task_id
START WITH task_id = l_task_id(i)
)
AND EXISTS
( SELECT 'Yes'
FROM PA_RESOURCE_ACCUM_DETAILS PRAD
WHERE PRAD.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID
AND PRAD.RESOURCE_LIST_MEMBER_ID IN
( -- Fetch both 2nd level and group level resource list member
SELECT PRLM.RESOURCE_LIST_MEMBER_ID
FROM PA_RESOURCE_LIST_MEMBERS PRLM
WHERE (prlm.resource_list_member_id = l_RESOURCE_LIST_MEMBER_ID(i)
or
PRLM.PARENT_MEMBER_ID = l_RESOURCE_LIST_MEMBER_ID(i) )
)
)
AND EXISTS
( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
WHERE
PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
)
AND pta.pa_period = l_period_name(i) ;
x_err_stage := 'lowest level task, categorized: Before inserting in Tmp table '||i;
x_err_stage := 'lowest level task, categorized: After inserting in Tmp table';
select p.period_name,
p.start_date,
p.end_date,
t.task_id,
m.resource_list_member_id,
m.resource_id,
m.track_as_labor_flag
bulk collect into
l_period_name,
l_start_date,
l_end_date,
l_task_id,
l_resource_list_member_id,
l_resource_id,
l_track_as_labor_flag
from gl_period_statuses p,
pa_implementations i,
pa_tasks t,
pa_resource_list_members m
where m.resource_list_id = x_resource_list_id
and not exists
(select 1
from pa_resource_list_members m1
where m1.parent_member_id =
m.resource_list_member_id)
and t.project_id = x_project_id
and not exists
(select 1
from pa_tasks t1
where t1.parent_task_id = t.task_id)
and p.application_id = pa_period_process_pkg.application_id
and p.set_of_books_id = i.set_of_books_id
and p.adjustment_period_flag = 'N'
and p.start_date between x_start_period_start_date
and x_end_period_end_date;
SELECT
sum(tot_revenue),
sum(tot_raw_cost),
sum(tot_burdened_cost),
sum(tot_quantity),
sum(tot_labor_hours),
sum(tot_billable_raw_cost),
sum(tot_billable_burdened_cost),
sum(tot_billable_quantity),
sum(tot_billable_labor_hours),
sum(tot_cmt_raw_cost),
sum(tot_cmt_burdened_cost),
Decode(sign(count(Distinct unit_of_measure)- 1), 0, max(unit_of_measure),null) unit_of_measure
INTO
x_revenue,
x_raw_cost,
x_burdened_cost,
x_quantity,
x_labor_hours,
x_billable_raw_cost,
x_billable_burdened_cost,
x_billable_quantity,
x_billable_labor_hours,
x_cmt_raw_cost,
x_cmt_burdened_cost,
x_unit_of_measure
FROM
pa_txn_accum pta
WHERE
pta.project_id = x_project_id
AND pta.task_id IN
(SELECT
task_id
FROM
pa_tasks
CONNECT BY PRIOR task_id = parent_task_id
START WITH task_id = l_task_id(i)
)
AND EXISTS
( SELECT 'Yes'
FROM PA_RESOURCE_ACCUM_DETAILS PRAD
WHERE PRAD.TXN_ACCUM_ID = PTA.TXN_ACCUM_ID
AND PRAD.RESOURCE_LIST_MEMBER_ID IN
( -- Fetch both 2nd level and group level resource list member
SELECT PRLM.RESOURCE_LIST_MEMBER_ID
FROM PA_RESOURCE_LIST_MEMBERS PRLM
WHERE (prlm.resource_list_member_id = l_RESOURCE_LIST_MEMBER_ID(i)
or
PRLM.PARENT_MEMBER_ID = l_RESOURCE_LIST_MEMBER_ID(i) )
)
)
AND EXISTS
( SELECT 'Yes' FROM PA_TXN_ACCUM_DETAILS PTAD
WHERE
PTA.TXN_ACCUM_ID = PTAD.TXN_ACCUM_ID
)
AND pta.gl_period = l_period_name(i);
select (NVL(quantity, 0) + nvl(TmpActTab(j).labor_hours, 0))
, (NVL(raw_cost,0) + nvl(TmpActTab(j).raw_cost, 0))
, (NVL(burdened_cost,0) + nvl(TmpActTab(j).burdened_cost, 0))
, (NVL(revenue,0) + nvl(TmpActTab(j).revenue, 0))
, pbl.resource_assignment_id
, pbl.rowid
into x_new_quantity,
x_new_raw_cost,
x_new_burdened_cost,
x_new_revenue,
x_new_assignment_id,
x_new_row_id
from pa_budget_lines pbl
where pbl.resource_assignment_id in (
select distinct pbl1.resource_assignment_id
from pa_budget_lines pbl1,
pa_resource_assignments pra,
pa_resource_list_members p1,
pa_resource_list_members p2
where pra.resource_list_member_id = p2.resource_list_member_id
and p1.parent_member_id = p2.resource_list_member_id
and p1.resource_list_member_id = TmpActTab(j).resource_list_member_id
and pbl1.resource_assignment_id = pra.resource_assignment_id
and pra.budget_version_id = x_version_id
and pra.task_id = TmpActTab(j).task_id
and pbl1.period_name = TmpActTab(j).period_name
)
and pbl.budget_version_id = x_version_id
and pbl.period_name = TmpActTab(j).period_name ;
pa_budget_lines_v_pkg.update_Row(X_Rowid => x_new_row_id,
X_Resource_Assignment_Id => x_new_assignment_id,
X_Budget_Version_Id => x_version_id,
X_Project_Id => x_project_id,
X_Task_Id => TmpActTab(j).task_id,
X_Resource_List_Member_Id => TmpActTab(j).resource_list_member_id,
X_Resource_Id => NULL,
X_Resource_Id_Old => NULL,
X_Description => NULL,
X_Start_Date => TmpActTab(j).start_date,
X_End_Date => TmpActTab(j).end_date,
X_Period_Name => TmpActTab(j).period_name,
X_Quantity => x_new_quantity,
X_Quantity_Old => TmpActTab(j).quantity,
X_Unit_Of_Measure => TmpActTab(j).unit_of_measure,
X_Track_As_Labor_Flag => TmpActTab(j).track_as_labor_flag,
X_Raw_Cost => x_new_raw_cost,
X_Raw_Cost_Old => TmpActTab(j).raw_cost,
X_Burdened_Cost => x_new_burdened_cost,
X_Burdened_Cost_Old => TmpActTab(j).burdened_cost,
X_Revenue => x_new_revenue,
X_Revenue_Old => TmpActTab(j).revenue,
X_Change_Reason_Code => NULL,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => x_created_by,
X_Last_Update_Login => x_last_update_login,
X_Attribute_Category => NULL,
X_Attribute1 => NULL,
X_Attribute2 => NULL,
X_Attribute3 => NULL,
X_Attribute4 => NULL,
X_Attribute5 => NULL,
X_Attribute6 => NULL,
X_Attribute7 => NULL,
X_Attribute8 => NULL,
X_Attribute9 => NULL,
X_Attribute10 => NULL,
X_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
-- X_mrc_flag => 'Y', -- Removed MRC code.
X_Calling_Process => 'PR',
X_raw_cost_source => 'A',
X_burdened_cost_source => 'A',
X_quantity_source => 'A',
X_revenue_source => 'A' );
pa_budget_lines_v_pkg.insert_row (
X_Rowid => x_rowid,
X_Resource_Assignment_Id => x_resource_assignment_id,
X_Budget_Version_Id => x_version_id,
X_Project_Id => x_project_id,
X_Task_Id => TmpActTab(j).task_id,
X_Resource_List_Member_Id => TmpActTab(j).resource_list_member_id,
X_Description => NULL,
X_Start_Date => TmpActTab(j).start_date,
X_End_Date => TmpActTab(j).end_date,
X_Period_Name => TmpActTab(j).period_name,
X_Quantity => TmpActTab(j).quantity,
X_Unit_Of_Measure => TmpActTab(j).unit_of_measure,
X_Track_As_Labor_Flag => TmpActTab(j).track_as_labor_flag,
X_Raw_Cost => TmpActTab(j).raw_cost,
X_Burdened_Cost => TmpActTab(j).burdened_cost,
X_Revenue => TmpActTab(j).revenue,
X_Change_Reason_Code => NULL,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => x_created_by,
X_Creation_Date => sysdate,
X_Created_By => x_created_by,
X_Last_Update_Login => x_last_update_login,
X_Attribute_Category => NULL,
X_Attribute1 => NULL,
X_Attribute2 => NULL,
X_Attribute3 => NULL,
X_Attribute4 => NULL,
X_Attribute5 => NULL,
X_Attribute6 => NULL,
X_Attribute7 => NULL,
X_Attribute8 => NULL,
X_Attribute9 => NULL,
X_Attribute10 => NULL,
X_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
X_Calling_Process => 'PR',
X_Pm_Product_Code => NULL,
X_Pm_Budget_Line_Reference => NULL,
X_raw_cost_source => 'A',
X_burdened_cost_source => 'A',
X_quantity_source => 'A',
X_revenue_source => 'A'--,
--X_mrc_flag => 'Y' -- FPB2: Added x_mrc_flag for MRC changes
);
for task_rec in (select t.task_id
from pa_tasks t
where t.project_id = x_project_id
and not exists
(select 1
from pa_tasks t1
where t1.parent_task_id = t.task_id)
) loop
if (x_categorization_code = 'N') then
-- lowest level task, uncategorized
x_quantity := 0;
Changed the following call to the procedure pa_budget_lines_v_pkg.insert_row
from "Positional Parameter Passing" to "Named Parameter Passing".
pa_budget_lines_v_pkg.insert_row (
X_Rowid => x_rowid,
X_Resource_Assignment_Id => x_resource_assignment_id,
X_Budget_Version_Id => x_version_id,
X_Project_Id => x_project_id,
X_Task_Id => task_rec.task_id,
X_Resource_List_Member_Id => x_uncat_res_list_member_id,
X_Description => NULL,
X_Start_Date => period_rec.start_date,
X_End_Date => period_rec.end_date,
X_Period_Name => period_rec.period_name,
X_Quantity => x_labor_hours, -- Changed for bug# 2107130
X_Unit_Of_Measure => x_uncat_unit_of_measure,
X_Track_As_Labor_Flag => x_uncat_track_as_labor_flag,
X_Raw_Cost => x_raw_cost,
X_Burdened_Cost => x_burdened_cost,
X_Revenue => x_revenue,
X_Change_Reason_Code => NULL,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => x_created_by,
X_Creation_Date => sysdate,
X_Created_By => x_created_by,
X_Last_Update_Login => x_last_update_login,
X_Attribute_Category => NULL,
X_Attribute1 => NULL,
X_Attribute2 => NULL,
X_Attribute3 => NULL,
X_Attribute4 => NULL,
X_Attribute5 => NULL,
X_Attribute6 => NULL,
X_Attribute7 => NULL,
X_Attribute8 => NULL,
X_Attribute9 => NULL,
X_Attribute10 => NULL,
X_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
X_Calling_Process => 'PR',
X_Pm_Product_Code => NULL,
X_Pm_Budget_Line_Reference => NULL,
X_raw_cost_source => 'A',
X_burdened_cost_source => 'A',
X_quantity_source => 'A',
X_revenue_source => 'A');
for res_rec in (select m.resource_list_member_id,
m.resource_id,
m.track_as_labor_flag
from pa_resource_list_members m
where m.resource_list_id =
x_resource_list_id
and nvl(m.migration_code, 'M') = 'M'
and not exists
(select 1
from pa_resource_list_members m1
where m1.parent_member_id =
m.resource_list_member_id)
) loop
x_err_stage := 'process period/task/resource <'
|| period_rec.period_name
|| '><' || to_char(task_rec.task_id)
|| '><' || to_char(res_rec.resource_list_member_id)
|| '>';
Changed the following call to the procedure pa_budget_lines_v_pkg.insert_row
from "Positional Parameter Passing" to "Named Parameter Passing".
pa_budget_lines_v_pkg.insert_row (
X_Rowid => x_rowid,
X_Resource_Assignment_Id => x_resource_assignment_id,
X_Budget_Version_Id => x_version_id,
X_Project_Id => x_project_id,
X_Task_Id => task_rec.task_id,
X_Resource_List_Member_Id => res_rec.resource_list_member_id,
X_Description => NULL,
X_Start_Date => period_rec.start_date,
X_End_Date => period_rec.end_date,
X_Period_Name => period_rec.period_name,
X_Quantity => x_quantity,
X_Unit_Of_Measure => x_unit_of_measure,
X_Track_As_Labor_Flag => res_rec.track_as_labor_flag,
X_Raw_Cost => x_raw_cost,
X_Burdened_Cost => x_burdened_cost,
X_Revenue => x_revenue,
X_Change_Reason_Code => NULL,
X_Last_Update_Date => sysdate,
X_Last_Updated_By => x_created_by,
X_Creation_Date => sysdate,
X_Created_By => x_created_by,
X_Last_Update_Login => x_last_update_login,
X_Attribute_Category => NULL,
X_Attribute1 => NULL,
X_Attribute2 => NULL,
X_Attribute3 => NULL,
X_Attribute4 => NULL,
X_Attribute5 => NULL,
X_Attribute6 => NULL,
X_Attribute7 => NULL,
X_Attribute8 => NULL,
X_Attribute9 => NULL,
X_Attribute10 => NULL,
X_Attribute11 => NULL,
X_Attribute12 => NULL,
X_Attribute13 => NULL,
X_Attribute14 => NULL,
X_Attribute15 => NULL,
X_Calling_Process => 'PR',
X_Pm_Product_Code => NULL,
X_Pm_Budget_Line_Reference => NULL,
X_raw_cost_source => 'A',
X_burdened_cost_source => 'A',
X_quantity_source => 'A',
X_revenue_source => 'A');
select p2.resource_list_member_id parent_member_id
from pa_resource_list_members p1,
pa_resource_list_members p2
where p1.parent_member_id = p2.resource_list_member_id
and p1.resource_list_member_id = x_child_member_id; -- child id
select pbl.resource_assignment_id resource_assignment_id
from pa_budget_lines pbl,
pa_resource_assignments pra
where pra.resource_list_member_id = l_parent_id
and pbl.resource_assignment_id = pra.resource_assignment_id
and pra.budget_version_id = x_budget_version_id
and nvl(pra.task_id, 0) = nvl(x_task_id, 0) ;
x_last_update_login NUMBER(15);
x_last_update_login := FND_GLOBAL.LOGIN_ID;
select 'Y'
into l_target_is_baselined
from pa_budget_versions
where budget_status_code = 'B'
and budget_version_id = x_dest_version_id;
insert into pa_resource_assignments
(resource_assignment_id,
budget_version_id,
project_id,
task_id,
resource_list_member_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
unit_of_measure,
track_as_labor_flag,
project_assignment_id,
RESOURCE_ASSIGNMENT_TYPE
)
select pa_resource_assignments_s.nextval,
x_dest_version_id,
s.project_id,
s.task_id,
s.resource_list_member_id,
SYSDATE,
x_created_by,
SYSDATE,
x_created_by,
x_last_update_login,
s.unit_of_measure,
s.track_as_labor_flag,
-1,
s.RESOURCE_ASSIGNMENT_TYPE
from
pa_resource_assignments s
where s.budget_version_id = x_src_version_id
and NVL(s.RESOURCE_ASSIGNMENT_TYPE,'USER_ENTERED') = 'USER_ENTERED';
insert into pa_budget_lines
(budget_line_id, /* FPB2 during changes for MRC */
budget_version_id, /* FPB2 */
resource_assignment_id,
start_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
end_date,
period_name,
quantity,
raw_cost,
burdened_cost,
revenue,
change_reason_code,
description,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
pm_product_code,
pm_budget_line_reference,
raw_cost_source,
burdened_cost_source,
quantity_source,
revenue_source,
Code_Combination_Id,
CCID_Gen_Status_Code,
CCID_Gen_Rej_Message,
projfunc_currency_code,
project_currency_code,
txn_currency_code
)
select
bmt.target_budget_line_id, /* FPB2 */
da.budget_version_id, /* FPB2 */
da.resource_assignment_id,
l.start_date,
SYSDATE,
x_created_by,
SYSDATE,
x_created_by,
x_last_update_login,
l.end_date,
l.period_name,
l.quantity,
l.raw_cost,
l.burdened_cost,
l.revenue,
l.change_reason_code,
l.description,
l.attribute_category,
l.attribute1,
l.attribute2,
l.attribute3,
l.attribute4,
l.attribute5,
l.attribute6,
l.attribute7,
l.attribute8,
l.attribute9,
l.attribute10,
l.attribute11,
l.attribute12,
l.attribute13,
l.attribute14,
l.attribute15,
decode(x_pm_flag,'Y',l.pm_product_code,NULL),
decode(x_pm_flag,'Y',l.pm_budget_line_reference,NULL),
'B',
'B',
'B',
'B',
l.Code_Combination_Id,
l.CCID_Gen_Status_Code,
l.CCID_Gen_Rej_Message,
l.projfunc_currency_code,
l.project_currency_code,
l.txn_currency_code
from pa_budget_lines l,
pa_resource_assignments sa,
pa_resource_assignments da,
pa_fp_bl_map_tmp bmt /* FPB2 */
where l.resource_assignment_id = sa.resource_assignment_id
and sa.budget_version_id = x_src_version_id
and sa.task_id = da.task_id
and sa.project_id = da.project_id
and sa.resource_list_member_id = da.resource_list_member_id
and da.budget_version_id = x_dest_version_id
and NVL(sa.RESOURCE_ASSIGNMENT_TYPE,'USER_ENTERED') = 'USER_ENTERED'
and bmt.source_budget_line_id = l.budget_line_id /* FPB2: MRC */ ;
select pbv.resource_list_id,
pbem.time_phased_type_code,
pbv.budget_version_id
from pa_budget_versions pbv,
pa_budget_entry_methods pbem
where pbem.budget_entry_method_code = pbv.budget_entry_method_code
and pbv.project_id = x_project_id
and pbv.budget_type_code = x_budget_type_code
and pbv.budget_status_code = 'W';
select budget_amount_code
from pa_budget_versions b,
pa_budget_types t
where b.budget_version_id = x_version_id
and b.budget_type_code = t.budget_type_code;
procedure update_budget_version (x_request_id number default null,
x_budget_version_id pa_budget_versions.budget_version_id%type)
is
pragma autonomous_transaction;
update pa_budget_versions
set request_id = x_request_id
where budget_version_id = x_budget_version_id;
select pbv.resource_list_id,
pbv.budget_entry_method_code,
pbv.budget_version_id
from pa_budget_versions pbv
where pbv.project_id = x_project_id
and pbv.budget_type_code = x_budget_type_code
and pbv.budget_status_code = 'W';
select request_id
into l_request_id
from pa_budget_versions
where project_id = x_project_id
and budget_type_code = x_budget_type_code
and budget_status_code = 'W';
select period_start_date
into l_start_period_date
from pa_budget_periods_v
where period_name = x_start_period;
select period_start_date
into l_end_period_date
from pa_budget_periods_v
where period_name = x_end_period;
update_budget_version( x_request_id => -99,
x_budget_version_id => l_budget_version_id);
update pa_budget_versions
set request_id = NULL
where budget_version_id = l_budget_version_id;