[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
X_Resource_Assignment_Id IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
X_Budget_Version_Id NUMBER,
X_Project_Id NUMBER,
X_Task_Id NUMBER,
X_Resource_List_Member_Id NUMBER,
X_Description VARCHAR2,
X_Start_Date DATE,
X_End_Date DATE,
X_Period_Name VARCHAR2,
X_Quantity IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
X_Unit_Of_Measure VARCHAR2,
X_Track_As_Labor_Flag VARCHAR2,
X_Raw_Cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
X_Burdened_Cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
X_Revenue IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
X_Change_Reason_Code VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Last_Update_Login NUMBER,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
-- Bug Fix: 4569365. Removed MRC code.
-- x_mrc_flag VARCHAR2, /* FPB2: Added for MRC 20-Sep */
X_Calling_Process VARCHAR2 DEFAULT 'PR',
X_Pm_Product_Code VARCHAR2 DEFAULT NULL,
X_Pm_Budget_Line_Reference VARCHAR2 DEFAULT NULL,
X_raw_cost_source VARCHAR2 DEFAULT 'M',
X_burdened_cost_source VARCHAR2 DEFAULT 'M',
X_quantity_source VARCHAR2 DEFAULT 'M',
X_revenue_source VARCHAR2 DEFAULT 'M',
/*New parameters added on 16-mar-2001*/
x_standard_bill_rate NUMBER DEFAULT NULL,
x_average_bill_rate NUMBER DEFAULT NULL,
x_average_cost_rate NUMBER DEFAULT NULL,
x_project_assignment_id NUMBER DEFAULT -1,
x_plan_error_code VARCHAR2 DEFAULT NULL,
x_total_plan_revenue NUMBER DEFAULT NULL,
x_total_plan_raw_cost NUMBER DEFAULT NULL,
x_total_plan_burdened_cost NUMBER DEFAULT NULL,
x_total_plan_quantity NUMBER DEFAULT NULL,
x_average_discount_percentage NUMBER DEFAULT NULL,
x_cost_rejection_code VARCHAR2 DEFAULT NULL,
x_burden_rejection_code VARCHAR2 DEFAULT NULL,
x_revenue_rejection_code VARCHAR2 DEFAULT NULL,
x_other_rejection_code VARCHAR2 DEFAULT NULL,
X_Code_Combination_Id NUMBER DEFAULT NULL,
X_CCID_Gen_Status_Code VARCHAR2 DEFAULT NULL,
X_CCID_Gen_Rej_Message VARCHAR2 DEFAULT NULL
)
IS
CURSOR C IS SELECT rowid FROM pa_budget_lines
WHERE resource_assignment_id = X_Resource_Assignment_Id
AND start_date = X_Start_Date;
select budget_type_code
from pa_budget_versions
where budget_version_id = x_budget_version_id;
l_rows_inserted NUMBER := 0;
If not exists then insert the record, by having the code like this the sequence
pa_resource_assignments_s will not get incremented unnecessarily 23-MAR-2001 */
BEGIN
select resource_assignment_id
into x_resource_assignment_id
from pa_resource_assignments a
where a.budget_version_id = x_budget_version_id
and a.project_id = x_project_id
and nvl(a.task_id,0) = nvl(x_task_id,0)
and a.resource_list_member_id = x_resource_list_member_id
and a.project_assignment_id = x_project_assignment_id;
l_rows_inserted := 0;
SELECT pa_resource_assignments_s.nextval
INTO X_Resource_Assignment_Id
FROM sys.dual;
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,
/*Added 16-mar-2001 by N Gupta*/
standard_bill_rate,
average_bill_rate,
average_cost_rate,
project_assignment_id,
plan_error_code,
total_plan_revenue,
total_plan_raw_cost,
total_plan_burdened_cost,
total_plan_quantity,
average_discount_percentage,
RESOURCE_ASSIGNMENT_TYPE
) VALUES (
X_Resource_Assignment_Id ,
x_budget_version_id,
x_project_id,
x_task_id,
x_resource_list_member_id,
SYSDATE,
x_last_updated_by,
SYSDATE,
x_created_by,
x_last_update_login,
x_unit_of_measure,
x_track_as_labor_flag,
/*Added 16-mar-2001 by N Gupta*/
x_standard_bill_rate,
x_average_bill_rate,
x_average_cost_rate,
x_project_assignment_id,
x_plan_error_code,
x_total_plan_revenue,
x_total_plan_raw_cost,
x_total_plan_burdened_cost,
x_total_plan_quantity,
x_average_discount_percentage,
'USER_ENTERED'
);
/* IF (l_rows_inserted = 0)
THEN
select resource_assignment_id
into x_resource_assignment_id
from pa_resource_assignments a
where a.budget_version_id = x_budget_version_id
and a.project_id = x_project_id
and nvl(a.task_id,0) = nvl(x_task_id,0)
and a.resource_list_member_id = x_resource_list_member_id
and a.project_assignment_id = x_project_assignment_id;
SELECT pa_budget_lines_s.nextval
INTO l_budget_line_id
FROM DUAL;
INSERT INTO pa_budget_lines(
budget_line_id, /* FPB2 */
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,
/*Added 16-mar-2001 By N gupta*/
COST_REJECTION_CODE,
BURDEN_REJECTION_CODE,
REVENUE_REJECTION_CODE,
OTHER_REJECTION_CODE,
Code_Combination_Id,
CCID_Gen_Status_Code,
CCID_Gen_Rej_Message,
projfunc_currency_code,
project_currency_code,
txn_currency_code
) VALUES (
l_budget_line_id, /* FPB2 */
x_budget_version_id, /* FPB2 */
X_Resource_Assignment_Id,
X_Start_Date,
X_Last_Update_Date,
X_Last_Updated_By,
X_Creation_Date,
X_Created_By,
X_Last_Update_Login,
X_End_Date,
X_Period_Name,
(X_Quantity),
pa_currency.round_currency_amt(X_Raw_Cost),
pa_currency.round_currency_amt(X_Burdened_Cost),
pa_currency.round_currency_amt(X_Revenue),
X_Change_Reason_Code,
X_Description,
X_Attribute_Category,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Attribute11,
X_Attribute12,
X_Attribute13,
X_Attribute14,
X_Attribute15,
X_Pm_Product_Code,
X_Pm_Budget_Line_Reference,
X_raw_cost_source,
X_burdened_cost_source,
X_quantity_source ,
X_revenue_source ,
/*Added 16-mar-2001 By N gupta*/
x_cost_rejection_code,
x_burden_rejection_code,
x_revenue_rejection_code,
x_other_rejection_code,
X_Code_Combination_Id,
X_CCID_Gen_Status_Code,
X_CCID_Gen_Rej_Message,
l_projfunc_currency_code,
l_project_currency_code,
l_txn_currency_code
);
p_action => PA_MRC_FINPLAN.G_ACTION_INSERT,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
update pa_budget_versions
set raw_cost = pa_currency.round_currency_amt(nvl(raw_cost,0) + nvl(x_raw_cost,0)),
burdened_cost = pa_currency.round_currency_amt(nvl(burdened_cost,0) + nvl(x_burdened_cost,0) ),
revenue = pa_currency.round_currency_amt(nvl(revenue,0) + nvl(x_revenue,0) ),
labor_quantity =
(to_number(decode(x_track_as_labor_flag,
'Y', nvl(labor_quantity,0) + nvl(x_quantity,0),
nvl(labor_quantity,0)))),
labor_unit_of_measure =
decode(x_track_as_labor_flag, 'Y', x_unit_of_measure,
labor_unit_of_measure),
last_update_date = x_last_update_date,
last_update_login = x_last_update_login,
last_updated_by = x_last_updated_by
where budget_version_id = x_budget_version_id;
, p_procedure_name => 'INSERT_ROW'
, p_error_text => l_msg_data || ' ORA-'||LPAD(substr(SQLCODE,2),5,'0')
);
, p_procedure_name => 'INSERT_ROW'
, p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
);
END Insert_Row;
SELECT l.resource_assignment_id,
l.start_date,
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,
a.budget_version_id,
a.project_id,
a.task_id,
a.resource_list_member_id,
a.unit_of_measure,
a.track_as_labor_flag
FROM pa_resource_assignments a,
pa_budget_lines l
WHERE l.rowid = X_Rowid
AND l.resource_assignment_id = a.resource_assignment_id
FOR UPDATE NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Resource_Assignment_Id NUMBER,
X_Budget_Version_Id NUMBER,
X_Project_Id NUMBER,
X_Task_Id NUMBER,
X_Resource_List_Member_Id NUMBER,
X_Resource_Id NUMBER,
X_Resource_Id_Old NUMBER,
X_Description VARCHAR2,
X_Start_Date DATE,
X_End_Date DATE,
X_Period_Name VARCHAR2,
X_Quantity IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
X_Quantity_Old IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
X_Unit_Of_Measure VARCHAR2,
X_Track_As_Labor_Flag VARCHAR2,
X_Raw_Cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
X_Raw_Cost_Old IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
X_Burdened_Cost IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
X_Burdened_Cost_Old IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
X_Revenue IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
X_Revenue_Old IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
X_Change_Reason_Code VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
-- Bug Fix: 4569365. Removed MRC code.
-- X_MRC_Flag VARCHAR2, /* FPB2: Added for MRC */
X_Calling_Process VARCHAR2 DEFAULT 'PR',
X_raw_cost_source VARCHAR2 DEFAULT 'M',
X_burdened_cost_source VARCHAR2 DEFAULT 'M',
X_quantity_source VARCHAR2 DEFAULT 'M',
X_revenue_source VARCHAR2 DEFAULT 'M',
/*Added following 13 columns on 16-mar-2001*/
x_standard_bill_rate NUMBER DEFAULT NULL,
x_average_bill_rate NUMBER DEFAULT NULL,
x_average_cost_rate NUMBER DEFAULT NULL,
x_project_assignment_id NUMBER DEFAULT NULL,
x_plan_error_code VARCHAR2 DEFAULT NULL,
x_total_plan_revenue NUMBER DEFAULT NULL,
x_total_plan_raw_cost NUMBER DEFAULT NULL,
x_total_plan_burdened_cost NUMBER DEFAULT NULL,
x_total_plan_quantity NUMBER DEFAULT NULL,
x_average_discount_percentage NUMBER DEFAULT NULL,
x_cost_rejection_code VARCHAR2 DEFAULT NULL,
x_burden_rejection_code VARCHAR2 DEFAULT NULL,
x_revenue_rejection_code VARCHAR2 DEFAULT NULL,
x_other_rejection_code VARCHAR2 DEFAULT NULL,
X_Code_Combination_Id NUMBER DEFAULT NULL,
X_CCID_Gen_Status_Code VARCHAR2 DEFAULT NULL,
X_CCID_Gen_Rej_Message VARCHAR2 DEFAULT NULL
) IS
created_by number;
last_updated_by number;
last_update_login number;
select budget_type_code
from pa_budget_versions
where budget_version_id = x_budget_version_id;
last_updated_by := fnd_global.user_id;
last_update_login := fnd_global.login_id;
pa_budget_lines_v_pkg.delete_row(x_rowid => x_rowid,
x_calling_process => x_calling_process
--,
-- Bug Fix: 4569365. Removed MRC code.
-- x_mrc_flag => x_mrc_flag /* FPB2: for MRC */
);
pa_budget_lines_v_pkg.insert_row(
X_Rowid => new_rowid,
X_Resource_Assignment_Id => res_assignment_id,
X_Budget_Version_Id => X_Budget_Version_Id,
X_Project_Id => X_Project_Id,
X_Task_Id => X_Task_Id,
X_Resource_List_Member_Id => X_Resource_List_Member_Id,
X_Description => X_Description,
X_Start_Date => X_Start_Date,
X_End_Date => X_End_Date,
X_Period_Name => X_Period_Name,
X_Quantity => X_Quantity,
X_Unit_Of_Measure => X_Unit_Of_Measure,
X_Track_As_Labor_Flag => X_Track_As_Labor_Flag,
X_Raw_Cost => X_Raw_Cost,
X_Burdened_Cost => X_Burdened_Cost,
X_Revenue => X_Revenue,
X_Change_Reason_Code => X_Change_Reason_Code,
x_last_update_date => SYSDATE,
X_Last_Updated_by => Last_Updated_By,
x_creation_date => SYSDATE,
X_Created_By => Created_By,
X_Last_Update_Login => Last_Update_Login,
X_Attribute_Category => X_Attribute_Category,
X_Attribute1 => X_Attribute1,
X_Attribute2 => X_Attribute2,
X_Attribute3 => X_Attribute3,
X_Attribute4 => X_Attribute4,
X_Attribute5 => X_Attribute5,
X_Attribute6 => X_Attribute6,
X_Attribute7 => X_Attribute7,
X_Attribute8 => X_Attribute8,
X_Attribute9 => X_Attribute9,
X_Attribute10 => X_Attribute10,
X_Attribute11 => X_Attribute11,
X_Attribute12 => X_Attribute12,
X_Attribute13 => X_Attribute13,
X_Attribute14 => X_Attribute14,
X_Attribute15 => X_Attribute15,
X_Calling_Process => X_Calling_Process,
X_raw_cost_source => X_raw_cost_source,
X_burdened_cost_source => X_burdened_cost_source,
X_quantity_source => X_quantity_source ,
X_revenue_source => X_revenue_source,
X_Code_Combination_Id => X_Code_Combination_Id,
X_CCID_Gen_Status_Code => X_CCID_Gen_Status_Code,
X_CCID_Gen_Rej_Message => X_CCID_Gen_Rej_Message
--,
-- Bug Fix: 4569365. Removed MRC code.
-- X_mrc_flag => X_mrc_flag /* FPB2: Added x_mrc_flag for MRC changes. Pass same as input */
);
UPDATE pa_budget_lines
SET
resource_assignment_id = X_Resource_Assignment_Id,
start_date = X_Start_Date,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
last_update_login = X_Last_Update_Login,
end_date = X_End_Date,
period_name = X_Period_Name,
quantity = (X_Quantity),
raw_cost = pa_currency.round_currency_amt(X_Raw_Cost),
burdened_cost = pa_currency.round_currency_amt(X_Burdened_Cost),
revenue = pa_currency.round_currency_amt(X_Revenue),
change_reason_code = X_Change_Reason_Code,
description = X_Description,
attribute_category = X_Attribute_Category,
attribute1 = X_Attribute1,
attribute2 = X_Attribute2,
attribute3 = X_Attribute3,
attribute4 = X_Attribute4,
attribute5 = X_Attribute5,
attribute6 = X_Attribute6,
attribute7 = X_Attribute7,
attribute8 = X_Attribute8,
attribute9 = X_Attribute9,
attribute10 = X_Attribute10,
attribute11 = X_Attribute11,
attribute12 = X_Attribute12,
attribute13 = X_Attribute13,
attribute14 = X_Attribute14,
attribute15 = X_Attribute15,
raw_cost_source = X_raw_cost_source,
burdened_cost_source = X_burdened_cost_source,
quantity_source = X_quantity_source,
revenue_source = X_revenue_source,
/*added 16-mar-2001 by N Gupta*/
COST_REJECTION_CODE = DECODE(X_COST_REJECTION_CODE, NULL, COST_REJECTION_CODE, X_COST_REJECTION_CODE),
BURDEN_REJECTION_CODE= DECODE(X_BURDEN_REJECTION_CODE, NULL, BURDEN_REJECTION_CODE, X_BURDEN_REJECTION_CODE),
REVENUE_REJECTION_CODE=DECODE(X_REVENUE_REJECTION_CODE, NULL, REVENUE_REJECTION_CODE, X_REVENUE_REJECTION_CODE),
OTHER_REJECTION_CODE=DECODE(X_OTHER_REJECTION_CODE, NULL, OTHER_REJECTION_CODE, X_OTHER_REJECTION_CODE),
Code_Combination_Id = X_Code_Combination_Id,
CCID_Gen_Status_Code = X_CCID_Gen_Status_Code,
CCID_Gen_Rej_Message = X_CCID_Gen_Rej_Message
WHERE rowid = X_Rowid
RETURNING budget_line_id INTO l_budget_line_id;
p_action => PA_MRC_FINPLAN.G_ACTION_UPDATE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
update pa_budget_versions
set labor_quantity = (to_number(
decode(x_track_as_labor_flag,
'Y', nvl(labor_quantity,0)
- nvl(x_quantity_old,0)
+ nvl(x_quantity,0),
nvl(labor_quantity,0))) ),
raw_cost = pa_currency.round_currency_amt(nvl(raw_cost,0) - nvl(x_raw_cost_old,0)
+ nvl(x_raw_cost,0) ),
burdened_cost = pa_currency.round_currency_amt(nvl(burdened_cost,0)
- nvl(x_burdened_cost_old,0)
+ nvl(x_burdened_cost,0) ),
revenue = pa_currency.round_currency_amt(nvl(revenue,0) - nvl(x_revenue_old,0)
+ nvl(x_revenue,0) )
where budget_version_id = x_budget_version_id;
, p_procedure_name => 'UPDATE_ROW'
, p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
);
, p_procedure_name => 'UPDATE_ROW'
, p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
);
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2,
-- Bug Fix: 4569365. Removed MRC code.
-- X_mrc_flag VARCHAR2 ,
X_Calling_Process VARCHAR2 DEFAULT 'PR'
) IS
x_raw_cost number;
x_last_updated_by number;
x_last_update_login number;
select l.raw_cost,
l.burdened_cost,
l.revenue,
l.quantity,
l.resource_assignment_id,
a.track_as_labor_flag
into x_raw_cost,
x_burdened_cost,
x_revenue,
x_quantity,
x_resource_assignment_id,
x_track_as_labor_flag
from pa_resource_assignments a,
pa_budget_lines l
where l.rowid = X_Rowid
and l.resource_assignment_id = a.resource_assignment_id;
DELETE FROM pa_budget_lines
WHERE rowid = X_Rowid;
x_last_updated_by := fnd_global.user_id;
x_last_update_login := fnd_global.login_id;
select budget_version_id
into x_budget_version_id
from pa_resource_assignments
where resource_assignment_id = x_resource_assignment_id;
delete pa_resource_assignments
where resource_assignment_id = x_resource_assignment_id
and not exists
(select 1
from pa_budget_lines
where resource_assignment_id = x_resource_assignment_id);
update pa_budget_versions
set raw_cost = pa_currency.round_currency_amt(nvl(raw_cost,0) - nvl(x_raw_cost,0) ),
burdened_cost = pa_currency.round_currency_amt(nvl(burdened_cost,0) - nvl(x_burdened_cost,0) ),
revenue = pa_currency.round_currency_amt(nvl(revenue,0) - nvl(x_revenue,0) ),
labor_quantity = (to_number(
decode(x_track_as_labor_flag,
'Y', nvl(labor_quantity,0) - nvl(x_quantity,0),
nvl(labor_quantity,0))) ),
last_update_date = SYSDATE,
last_update_login = x_last_update_login,
last_updated_by = x_last_updated_by
where budget_version_id = x_budget_version_id;
p_action => PA_MRC_FINPLAN.G_ACTION_DELETE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
END Delete_Row;
select a.resource_name
from pa_budget_lines_v a, pa_budget_lines_v b
where a.budget_version_id = x_budget_version_id
and b.budget_version_id = x_budget_version_id
and a.task_id||null = b.task_id||null
and a.resource_list_member_id = b.resource_list_member_id
and a.row_id <> b.row_id
and ((a.start_date
between b.start_date
and nvl(b.end_date,a.start_date +1))
or (a.end_date
between b.start_date
and nvl(b.end_date,b.end_date+1))
or (b.start_date
between a.start_date
and nvl(a.end_date,b.start_date+1))
); */
select I1.resource_assignment_id
from
PA_BUDGET_LINES I1,
PA_BUDGET_LINES I2
where
I1.budget_version_id = x_budget_version_id
and I2.budget_version_id = x_budget_version_id
and I1.resource_assignment_id = I2.resource_assignment_id
and I1.txn_currency_code = I2.txn_currency_code
and I1.rowid <> I2.rowid
and ((I1.start_date
between I2.start_date and I2.end_date)
or (I1.end_date
between I2.start_date and I2.end_date)
or (I2.start_date
between I1.start_date and I1.end_date))
and (I1.txn_currency_code = I2.txn_currency_code or I1.txn_currency_code is null and I2.txn_currency_code is null);
select SUBSTRB(pa_resources_pkg.get_resource_name(M1.RESOURCE_ID, M1.RESOURCE_TYPE_ID),1,30) resource_name
into x_resource_name
from PA_RESOURCE_LIST_MEMBERS M1,
PA_RESOURCE_ASSIGNMENTS ra
where
ra.resource_assignment_id = v_res_assignment_id and
ra.resource_list_member_id = M1.resource_list_member_id;