[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
delete_flag VARCHAR2(1) );
PROCEDURE INSERT_ROW (
p_api_version in number,
p_init_msg_list in varchar2 := fnd_api.g_false,
p_commit in varchar2 := fnd_api.g_false,
p_validation_level in number := fnd_api.g_valid_level_full,
p_return_status OUT NOCOPY varchar2,
p_msg_count OUT NOCOPY number,
p_msg_data OUT NOCOPY varchar2,
p_rowid in OUT NOCOPY varchar2,
p_position_id in number,
-- de by org
p_organization_id in number := NULL,
p_data_extract_id in number,
p_position_definition_id in number,
p_hr_position_id in number,
p_hr_employee_id in number := fnd_api.g_miss_num ,
p_business_group_id in number,
p_budget_group_id in number := fnd_api.g_miss_num ,
p_effective_start_date in date,
p_effective_end_date in date,
p_set_of_books_id in number,
p_vacant_position_flag in varchar2,
p_availability_status in varchar2 := fnd_api.g_miss_char ,
p_transaction_id in number := fnd_api.g_miss_num ,
p_transaction_status in varchar2 := fnd_api.g_miss_char ,
p_new_position_flag in varchar2 := fnd_api.g_miss_char ,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_attribute11 in varchar2,
p_attribute12 in varchar2,
p_attribute13 in varchar2,
p_attribute14 in varchar2,
p_attribute15 in varchar2,
p_attribute16 in varchar2,
p_attribute17 in varchar2,
p_attribute18 in varchar2,
p_attribute19 in varchar2,
p_attribute20 in varchar2,
p_attribute_category in varchar2,
p_name in varchar2,
p_mode in varchar2 := 'R'
) is
cursor C is select ROWID from PSB_POSITIONS
where POSITION_ID = P_POSITION_ID;
P_LAST_UPDATE_DATE DATE;
P_LAST_UPDATED_BY NUMBER;
P_LAST_UPDATE_LOGIN NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row' ;
SAVEPOINT Insert_Row ;
P_LAST_UPDATE_DATE := SYSDATE;
P_LAST_UPDATED_BY := 1;
P_LAST_UPDATE_LOGIN := 0;
P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
if P_LAST_UPDATED_BY is NULL then
P_LAST_UPDATED_BY := -1;
P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
if P_LAST_UPDATE_LOGIN is NULL then
P_LAST_UPDATE_LOGIN := -1;
insert into PSB_POSITIONS (
position_id ,
organization_id ,
data_extract_id ,
position_definition_id,
hr_position_id ,
hr_employee_id ,
business_group_id ,
budget_group_id ,
effective_start_date ,
effective_end_date ,
set_of_books_id ,
vacant_position_flag ,
availability_status ,
transaction_id ,
transaction_status ,
new_position_flag ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
attribute16 ,
attribute17 ,
attribute18 ,
attribute19 ,
attribute20 ,
attribute_category ,
name ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
values
(
p_position_id ,
p_organization_id ,
p_data_extract_id ,
p_position_definition_id ,
p_hr_position_id ,
l_hr_employee_id ,
p_business_group_id ,
l_budget_group_id ,
p_effective_start_date,
p_effective_end_date ,
p_set_of_books_id ,
p_vacant_position_flag,
l_availability_status ,
l_transaction_id ,
l_transaction_status ,
l_new_position_flag ,
p_attribute1 ,
p_attribute2 ,
p_attribute3 ,
p_attribute4 ,
p_attribute5 ,
p_attribute6 ,
p_attribute7 ,
p_attribute8 ,
p_attribute9 ,
p_attribute10 ,
p_attribute11 ,
p_attribute12 ,
p_attribute13 ,
p_attribute14 ,
p_attribute15 ,
p_attribute16 ,
p_attribute17 ,
p_attribute18 ,
p_attribute19 ,
p_attribute20 ,
p_attribute_category ,
p_name ,
p_last_update_date ,
p_last_updated_by ,
p_last_update_date ,
p_last_updated_by ,
p_last_update_login
);
rollback to INSERT_ROW ;
rollback to INSERT_ROW ;
rollback to INSERT_ROW ;
END INSERT_ROW;
cursor c1 is select
position_id ,
data_extract_id ,
position_definition_id ,
hr_position_id ,
business_group_id ,
effective_start_date,
effective_end_date ,
set_of_books_id ,
vacant_position_flag,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
attribute16 ,
attribute17 ,
attribute18 ,
attribute19 ,
attribute20 ,
attribute_category ,
name
from PSB_POSITIONS
where position_id = P_position_id
for update of position_id nowait;
PROCEDURE UPDATE_ROW (
p_api_version in number,
p_init_msg_list in varchar2 := fnd_api.g_false,
p_commit in varchar2 := fnd_api.g_false,
p_validation_level in number := fnd_api.g_valid_level_full,
p_return_status OUT NOCOPY varchar2,
p_msg_count OUT NOCOPY number,
p_msg_data OUT NOCOPY varchar2,
p_position_id in number,
-- de by org
p_organization_id in number := NULL,
p_data_extract_id in number,
p_position_definition_id in number,
p_hr_position_id in number,
p_hr_employee_id in number := fnd_api.g_miss_num ,
p_business_group_id in number,
p_budget_group_id in number := fnd_api.g_miss_num ,
p_effective_start_date in date,
p_effective_end_date in date,
p_set_of_books_id in number,
p_vacant_position_flag in varchar2,
p_availability_status in varchar2 := fnd_api.g_miss_char ,
p_transaction_id in number := fnd_api.g_miss_num ,
p_transaction_status in varchar2 := fnd_api.g_miss_char ,
p_new_position_flag in varchar2 := fnd_api.g_miss_char ,
p_attribute1 in varchar2,
p_attribute2 in varchar2,
p_attribute3 in varchar2,
p_attribute4 in varchar2,
p_attribute5 in varchar2,
p_attribute6 in varchar2,
p_attribute7 in varchar2,
p_attribute8 in varchar2,
p_attribute9 in varchar2,
p_attribute10 in varchar2,
p_attribute11 in varchar2,
p_attribute12 in varchar2,
p_attribute13 in varchar2,
p_attribute14 in varchar2,
p_attribute15 in varchar2,
p_attribute16 in varchar2,
p_attribute17 in varchar2,
p_attribute18 in varchar2,
p_attribute19 in varchar2,
p_attribute20 in varchar2,
p_attribute_category in varchar2,
p_name in varchar2,
p_mode in varchar2 := 'R'
) is
P_LAST_UPDATE_DATE DATE;
P_LAST_UPDATED_BY NUMBER;
P_LAST_UPDATE_LOGIN NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'Update Row';
SAVEPOINT Update_Row ;
P_LAST_UPDATE_DATE := SYSDATE;
P_LAST_UPDATED_BY := 1;
P_LAST_UPDATE_LOGIN := 0;
P_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
if P_LAST_UPDATED_BY is NULL then
P_LAST_UPDATED_BY := -1;
P_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
if P_LAST_UPDATE_LOGIN is NULL then
P_LAST_UPDATE_LOGIN := -1;
update PSB_POSITIONS set
position_id = p_position_id ,
-- de by org
organization_id = nvl(p_organization_id,organization_id),
data_extract_id = p_data_extract_id ,
position_definition_id = p_position_definition_id ,
hr_position_id = p_hr_position_id ,
hr_employee_id = l_hr_employee_id ,
business_group_id = p_business_group_id ,
budget_group_id = l_budget_group_id ,
effective_start_Date = p_effective_start_date ,
effective_end_date = p_effective_end_date ,
set_of_books_id = p_set_of_books_id ,
vacant_position_flag = p_vacant_position_flag ,
availability_status = l_availability_status ,
transaction_id = l_transaction_id ,
transaction_status = l_transaction_status ,
new_position_flag = l_new_position_flag ,
attribute1 = p_attribute1 ,
attribute2 = p_attribute2 ,
attribute3 = p_attribute3 ,
attribute4 = p_attribute4 ,
attribute5 = p_attribute5 ,
attribute6 = p_attribute6 ,
attribute7 = p_attribute7 ,
attribute8 = p_attribute8 ,
attribute9 = p_attribute9 ,
attribute10 = p_attribute10 ,
attribute11 = p_attribute11 ,
attribute12 = p_attribute12 ,
attribute13 = p_attribute13 ,
attribute14 = p_attribute14 ,
attribute15= p_attribute15 ,
attribute16 = p_attribute16 ,
attribute17 = p_attribute17 ,
attribute18 = p_attribute18 ,
attribute19 = p_attribute19 ,
attribute20 = p_attribute20 ,
attribute_category = p_attribute_category ,
name = p_name ,
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by ,
last_update_login = p_last_update_login
where position_id = p_position_id
;
rollback to Update_Row ;
rollback to Update_Row ;
rollback to Update_Row ;
END UPDATE_ROW;
cursor c1 is select rowid from PSB_POSITIONS
where position_id = p_position_id
;
INSERT_ROW (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_return_status => p_return_status,
p_msg_count => p_msg_count,
p_msg_data => p_msg_data,
p_rowid => p_rowid,
p_position_id => p_position_id,
p_organization_id => p_organization_id,
p_data_extract_id => p_data_extract_id,
p_position_definition_id => p_position_definition_id ,
p_hr_position_id => p_hr_position_id,
p_business_group_id => p_business_group_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_set_of_books_id => p_set_of_books_id,
p_vacant_position_flag => p_vacant_position_flag,
p_attribute1 => p_attribute1 ,
p_attribute2 => p_attribute2 ,
p_attribute3 => p_attribute3 ,
p_attribute4 => p_attribute4 ,
p_attribute5 => p_attribute5 ,
p_attribute6 => p_attribute6 ,
p_attribute7 => p_attribute7 ,
p_attribute8 => p_attribute8 ,
p_attribute9 => p_attribute9 ,
p_attribute10 => p_attribute10 ,
p_attribute11 => p_attribute11 ,
p_attribute12 => p_attribute12 ,
p_attribute13 => p_attribute13 ,
p_attribute14 => p_attribute14 ,
p_attribute15 => p_attribute15 ,
p_attribute16 => p_attribute16 ,
p_attribute17 => p_attribute17 ,
p_attribute18 => p_attribute18 ,
p_attribute19 => p_attribute19 ,
p_attribute20 => p_attribute20 ,
p_attribute_category => p_attribute_category ,
p_name => p_name ,
p_mode => p_mode
);
UPDATE_ROW (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_return_status => p_return_status,
p_msg_count => p_msg_count,
p_msg_data => p_msg_data,
p_position_id => p_position_id,
p_organization_id => p_organization_id,
p_data_extract_id => p_data_extract_id,
p_position_definition_id => p_position_definition_id ,
p_hr_position_id => p_hr_position_id ,
p_business_group_id => p_business_group_id ,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date ,
p_set_of_books_id => p_set_of_books_id ,
p_vacant_position_flag => p_vacant_position_flag,
p_attribute1 => p_attribute1 ,
p_attribute2 => p_attribute2 ,
p_attribute3 => p_attribute3 ,
p_attribute4 => p_attribute4 ,
p_attribute5 => p_attribute5 ,
p_attribute6 => p_attribute6 ,
p_attribute7 => p_attribute7 ,
p_attribute8 => p_attribute8 ,
p_attribute9 => p_attribute9 ,
p_attribute10 => p_attribute10 ,
p_attribute11 => p_attribute11 ,
p_attribute12 => p_attribute12 ,
p_attribute13 => p_attribute13 ,
p_attribute14 => p_attribute14 ,
p_attribute15 => p_attribute15 ,
p_attribute16 => p_attribute16 ,
p_attribute17 => p_attribute17 ,
p_attribute18 => p_attribute18 ,
p_attribute19 => p_attribute19 ,
p_attribute20 => p_attribute20 ,
p_attribute_category => p_attribute_category ,
p_name => p_name ,
p_mode => p_mode
);
PROCEDURE DELETE_ROW (
p_api_version in number,
p_init_msg_list in varchar2 := fnd_api.g_false,
p_commit in varchar2 := fnd_api.g_false,
p_validation_level in number := fnd_api.g_valid_level_full,
p_return_status OUT NOCOPY varchar2,
p_msg_count OUT NOCOPY number,
p_msg_data OUT NOCOPY varchar2,
p_position_id in number
) is
--
l_api_name CONSTANT VARCHAR2(30) := 'Delete Row' ;
SAVEPOINT Delete_Row ;
SELECT count(*) into l_count
FROM psb_ws_position_lines
WHERE position_id = p_position_id;
delete from PSB_POSITIONS
where position_id = p_position_id;
rollback to Delete_Row;
rollback to Delete_Row;
rollback to Delete_Row ;
END DELETE_ROW;
PROCEDURE Delete_Assignments
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
p_worksheet_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Assignments';
SAVEPOINT Delete_Assignments_Pvt;
delete from PSB_POSITION_ASSIGNMENTS
where worksheet_id = p_worksheet_id;
rollback to Delete_Assignments_Pvt;
rollback to Delete_Assignments_Pvt;
rollback to Delete_Assignments_Pvt;
END Delete_Assignments;
PROCEDURE Delete_Assignment_Employees
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_return_status OUT NOCOPY VARCHAR2,
p_msg_count OUT NOCOPY NUMBER,
p_msg_data OUT NOCOPY VARCHAR2,
p_data_extract_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Assignment_Employees';
SAVEPOINT Delete_Assignment_Emp_Pvt;
delete from PSB_POSITION_ASSIGNMENTS
where assignment_type = 'EMPLOYEE'
and data_extract_id = p_data_extract_id;
rollback to Delete_Assignment_Emp_Pvt;
rollback to Delete_Assignment_Emp_Pvt;
rollback to Delete_Assignment_Emp_Pvt;
END Delete_Assignment_Employees;
select position_assignment_id,
data_extract_id,
worksheet_id,
position_id,
assignment_type,
attribute_id,
attribute_value_id,
attribute_value,
pay_element_id,
pay_element_option_id,
effective_start_date,
effective_end_date,
element_value_type,
element_value,
currency_code,
pay_basis,
employee_id,
primary_employee_flag,
global_default_flag,
assignment_default_rule_id,
modify_flag
from PSB_POSITION_ASSIGNMENTS
where (worksheet_id is null or worksheet_id = p_worksheet_id)
and (((p_assignment_type = 'ATTRIBUTE')
and (attribute_id = p_attribute_id))
or ((p_assignment_type = 'EMPLOYEE')
and (employee_id = p_employee_id))
or ((p_assignment_type = 'ELEMENT')
and (pay_element_id = p_pay_element_id)
and ((p_currency_code is null) or (currency_code = p_currency_code))))
and ((((p_effective_end_date is not null)
and ((effective_start_date <= p_effective_end_date)
and (effective_end_date is null))
or ((effective_start_date between p_effective_start_date and p_effective_end_date)
or (effective_end_date between p_effective_start_date and p_effective_end_date)
or ((effective_start_date < p_effective_start_date)
and (effective_end_date > p_effective_end_date)))))
or ((p_effective_end_date is null)
and (nvl(effective_end_date, p_effective_start_date) >= p_effective_start_date)))
and position_id = p_position_id;
select a.position_assignment_id,
a.data_extract_id,
a.worksheet_id,
a.position_id,
a.assignment_type,
a.attribute_id,
a.attribute_value_id,
a.attribute_value,
a.pay_element_id,
a.pay_element_option_id,
a.effective_start_date,
a.effective_end_date,
a.element_value_type,
a.element_value,
a.currency_code,
a.pay_basis,
a.employee_id,
a.primary_employee_flag,
a.global_default_flag,
a.assignment_default_rule_id,
a.modify_flag
from PSB_POSITION_ASSIGNMENTS a,
PSB_PAY_ELEMENTS el
where
( (nvl(a.worksheet_id, FND_API.G_MISS_NUM) = nvl(p_worksheet_id, FND_API.G_MISS_NUM)) OR
(p_worksheet_id is not null and worksheet_id is null
and not exists
(select 1 from
psb_position_assignments c ,psb_pay_elements pe2
where c.position_id = a.position_id
and c.pay_element_id = pe2.pay_element_id
and pe2.salary_flag = 'Y'
and c.worksheet_id = p_worksheet_id
and ( (
nvl(c.effective_start_date,PSB_POSITIONS_PVT.GET_end_date+1) between
nvl(a.effective_start_date,PSB_POSITIONS_PVT.GET_end_date) and
nvl(a.effective_end_date,nvl(PSB_POSITIONS_PVT.GET_end_date,
c.effective_start_date ))) or (
nvl(a.effective_start_date,PSB_POSITIONS_PVT.GET_end_date+1) between
nvl(c.effective_start_date,PSB_POSITIONS_PVT.GET_end_date) and
nvl(c.effective_end_date,nvl(PSB_POSITIONS_PVT.GET_end_date,
a.effective_start_date ))) )
)
)
)
and ( (p_currency_code is null) or (currency_code = p_currency_code))
and ((((p_effective_end_date is not null)
and ((effective_start_date <= p_effective_end_date)
and (effective_end_date is null))
or ((effective_start_date between p_effective_start_date and p_effective_end_date)
or (effective_end_date between p_effective_start_date and p_effective_end_date)
or ((effective_start_date < p_effective_start_date)
and (effective_end_date > p_effective_end_date)))))
or ((p_effective_end_date is null)
and (nvl(effective_end_date, p_effective_start_date) >= p_effective_start_date)))
and position_id = p_position_id
and a.assignment_type = 'ELEMENT'
and a.pay_element_id = el.pay_element_id
and el.salary_flag = 'Y';
select salary_flag
from PSB_PAY_ELEMENTS
where pay_element_id = p_pay_element_id;
Select pay_basis
From psb_position_assignments
Where ((worksheet_id is null) or (worksheet_id = p_worksheet_id))
And assignment_type = 'ELEMENT'
And position_id = p_position_id
And pay_basis is not null
And ROWNUM < 2;
l_updated_record VARCHAR2(1);
update PSB_POSITION_ASSIGNMENTS
set attribute_value_id = decode(p_attribute_value_id, null, attribute_value_id, p_attribute_value_id),
attribute_value = decode(p_attribute_value, null, attribute_value, p_attribute_value),
pay_element_option_id = decode(p_pay_element_option_id, null, pay_element_option_id, p_pay_element_option_id),
element_value_type = decode(p_element_value_type, null, element_value_type, p_element_value_type),
element_value = decode(p_element_value, null, element_value, p_element_value),
currency_code = decode(p_currency_code, null, currency_code, p_currency_code),
pay_basis = decode(p_pay_basis, null, pay_basis, p_pay_basis),
primary_employee_flag = decode(p_primary_employee_flag, null, primary_employee_flag, p_primary_employee_flag),
global_default_flag = decode(p_global_default_flag, null, global_default_flag, p_global_default_flag),
assignment_default_rule_id = decode(p_assignment_default_rule_id, null, assignment_default_rule_id, p_assignment_default_rule_id),
modify_flag = decode(p_modify_flag, null, modify_flag, p_modify_flag),
last_update_date = sysdate,
last_updated_by = l_userid,
last_update_login = l_loginid
where nvl(worksheet_id, FND_API.G_MISS_NUM) = nvl(p_worksheet_id, FND_API.G_MISS_NUM)
and (((p_assignment_type = 'ELEMENT') and (pay_element_id = p_pay_element_id))
or ((p_assignment_type = 'ATTRIBUTE') and (attribute_id = p_attribute_id))
or ((p_assignment_type = 'EMPLOYEE') and (employee_id = p_employee_id)))
and nvl(effective_end_date, FND_API.G_MISS_DATE) = nvl(p_effective_end_date, FND_API.G_MISS_DATE)
and effective_start_date = p_effective_start_date
and position_id = p_position_id;
g_assign(l_init_index).delete_flag := null;
g_assign(g_num_assign).delete_flag := FND_API.G_TRUE;
g_assign(g_num_assign).delete_flag := FND_API.G_TRUE;
/* No Overlaps, Input not for protecting assignment: direct insert */
-- removed salary validation
begin
PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_position_assignment_id => l_position_assignment_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => p_worksheet_id,
p_position_id => p_position_id,
p_assignment_type => p_assignment_type,
p_attribute_id => p_attribute_id,
p_attribute_value_id => p_attribute_value_id,
p_attribute_value => p_attribute_value,
p_pay_element_id => p_pay_element_id,
p_pay_element_option_id => p_pay_element_option_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_element_value_type => p_element_value_type,
p_element_value => p_element_value,
p_currency_code => p_currency_code,
/* For Bug No. 2847566 Start */
--p_pay_basis => p_pay_basis,
p_pay_basis => l_pay_basis,
/* For Bug No. 2847566 End */
p_employee_id => p_employee_id,
p_primary_employee_flag => p_primary_employee_flag,
p_global_default_flag => p_global_default_flag,
p_assignment_default_rule_id => p_assignment_default_rule_id,
p_modify_flag => p_modify_flag,
p_mode => p_mode);
/* Worksheet Overlap, Update Protected Flag for the Assignment */
-- modified 'if' statement to include test on pay_element_id and option_id if input is a salary element
if ((g_assign(l_assign_index).worksheet_id = p_worksheet_id) and
((nvl(g_assign(l_assign_index).pay_element_option_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_option_id, FND_API.G_MISS_NUM)))
OR
(l_salary_flag='Y'
and nvl(g_assign(l_assign_index).pay_element_option_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_option_id, FND_API.G_MISS_NUM)
and nvl(g_assign(l_assign_index).pay_element_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_id, FND_API.G_MISS_NUM)
)
) then
begin
PSB_POSITION_ASSIGNMENTS_PVT.Update_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_assignment_id => g_assign(l_assign_index).position_assignment_id,
p_modify_flag => p_modify_flag,
p_mode => p_mode);
g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
/* There is No Worksheet Overlap, so Replicate Base Overlap and update Protected Flag for the Assignment */
-- modified 'if' statement to include test on pay_element_id and option_id if input is a salary element
if ((nvl(g_assign(l_assign_index).pay_element_option_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_option_id, FND_API.G_MISS_NUM)
)
OR
(l_salary_flag='Y'
and nvl(g_assign(l_assign_index).pay_element_option_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_option_id, FND_API.G_MISS_NUM)
and nvl(g_assign(l_assign_index).pay_element_id, FND_API.G_MISS_NUM) = nvl(p_pay_element_id, FND_API.G_MISS_NUM)
)
) then
begin
Modify_Assignment_WS
(p_return_status => l_return_status,
p_position_assignment_id => l_position_assignment_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => p_worksheet_id,
p_position_id => p_position_id,
p_assignment_type => g_assign(l_assign_index).assignment_type,
p_attribute_id => g_assign(l_assign_index).attribute_id,
p_attribute_value_id => g_assign(l_assign_index).attribute_value_id,
p_attribute_value => g_assign(l_assign_index).attribute_value,
p_pay_element_id => g_assign(l_assign_index).pay_element_id,
p_pay_element_option_id => g_assign(l_assign_index).pay_element_option_id,
p_effective_start_date => greatest(g_assign(l_assign_index).effective_start_date, p_effective_start_date),
p_effective_end_date => least(nvl(g_assign(l_assign_index).effective_end_date, p_effective_end_date), p_effective_end_date),
p_element_value_type => g_assign(l_assign_index).element_value_type,
p_element_value => g_assign(l_assign_index).element_value,
p_currency_code => nvl(p_currency_code, g_assign(l_assign_index).currency_code),
p_pay_basis => g_assign(l_assign_index).pay_basis,
p_employee_id => g_assign(l_assign_index).employee_id,
p_primary_employee_flag => g_assign(l_assign_index).primary_employee_flag,
p_global_default_flag => g_assign(l_assign_index).global_default_flag,
p_assignment_default_rule_id => g_assign(l_assign_index).assignment_default_rule_id,
p_modify_flag => p_modify_flag,
p_rowid => l_rowid);
-- update the overlap's start date or create a new record
-- - depending on the overlap's end date and input's end date either
-- update the overlap's end date or create a new record
-- - modified the update row to pass pay_element_id and pay_element_option_id due to the salary change
-- (iii) overlap dates and overlap records is base and input is not base (= p_worksheet_id) and
-- there is NO worksheet overlap in the overlap records.
-- (If there are worksheet overlaps, it will will be processed in (ii) ).
-- - always create the input record since the overlap is a base
-- - create a record from the input's end date +1 to the overlaps's end date
-- if the overlap's end date is beyond the input's end date
--
-- Modified the all update_row api to pass pay_element_id and pay_element_option_id due to the salary change
for l_assign_index in 1..g_num_assign loop
l_updated_record := FND_API.G_FALSE;
-- updateable. When changing for example the end date, this routine will create a WS specific record.
-- If the original record is a WS specific and the end date is modified, the overlap is updated
--
-- If the change was done from Modify Positions form which processes only base, then just update the record
--
-- From the form, the only possible routines processed for this api are :
-- - 1 overlap only where effective date matches either base or Ws specific
-- - no overlap since the form already tests for overlaps (g_num_assign is 0)
--
-- This logic is also performed when called from other than form and the start date matches
-- ** This poses a problem when the start date matches with overlap and the routine is called from other than
-- form. This routine will be performed, but the other overlaps will be processed using the other routines
-- depending on the date. The result will be overlap assignments.
-- Note that the overlap cursors do not control the way the record is retrieved so the overlap records
-- may come in any order, i.e., overlap with date matches as the first record or last record or middle record
--
if g_assign(l_assign_index).effective_start_date = p_effective_start_date then
begin
if ((nvl(g_assign(l_assign_index).worksheet_id, FND_API.G_MISS_NUM) = nvl(p_worksheet_id, FND_API.G_MISS_NUM)) and
((g_assign(l_assign_index).modify_flag is null) or (g_assign(l_assign_index).modify_flag = 'Y'))) then
begin
--+ pass input pay_element_id and pay_element_option_id so that salary overlap will result
--+ of update will have the new input salary values
PSB_POSITION_ASSIGNMENTS_PVT.Update_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_assignment_id => g_assign(l_assign_index).position_assignment_id,
p_pay_element_option_id => p_pay_element_option_id,
p_attribute_value_id => p_attribute_value_id,
p_attribute_value => p_attribute_value,
p_effective_end_date => p_effective_end_date,
p_pay_element_id => p_pay_element_id,
p_element_value_type => p_element_value_type,
p_element_value => p_element_value,
p_global_default_flag => p_global_default_flag,
p_assignment_default_rule_id => p_assignment_default_rule_id,
p_modify_flag => p_modify_flag,
p_pay_basis => g_assign(l_assign_index).pay_basis,
p_employee_id => p_employee_id,
p_primary_employee_flag => p_primary_employee_flag,
p_mode => p_mode);
g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_position_assignment_id => l_position_assignment_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => p_worksheet_id,
p_position_id => p_position_id,
p_assignment_type => p_assignment_type,
p_attribute_id => p_attribute_id,
p_attribute_value_id => p_attribute_value_id,
p_attribute_value => p_attribute_value,
p_pay_element_id => p_pay_element_id,
p_pay_element_option_id => p_pay_element_option_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_element_value_type => p_element_value_type,
p_element_value => p_element_value,
p_currency_code => nvl(p_currency_code, g_assign(l_assign_index).currency_code),
p_pay_basis => g_assign(l_assign_index).pay_basis,
p_employee_id => p_employee_id,
p_primary_employee_flag => p_primary_employee_flag,
p_global_default_flag => p_global_default_flag,
p_assignment_default_rule_id => p_assignment_default_rule_id,
p_modify_flag => p_modify_flag,
p_mode => p_mode);
--++ pass input pay_element_option_id so that if input is salary, the updated row will
--++ reflect the input value
/* start bug no 4213882 */
IF PSB_HR_POPULATE_DATA_PVT.g_pop_assignment = 'Y' AND
PSB_HR_POPULATE_DATA_PVT.g_extract_method = 'REFRESH' THEN
l_element_id := g_assign(l_assign_index).pay_element_id;
PSB_POSITION_ASSIGNMENTS_PVT.Update_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_assignment_id => g_assign(l_assign_index).position_assignment_id,
/* Bug No 2259505 Start */
-- Uncommented the first line and commented the second line
p_pay_element_option_id => g_assign(l_assign_index).pay_element_option_id,
-- p_pay_element_option_id => p_pay_element_option_id,
/* Bug No 2259505 End */
p_attribute_value_id => g_assign(l_assign_index).attribute_value_id,
p_attribute_value => g_assign(l_assign_index).attribute_value,
p_effective_end_date => p_effective_start_date - 1,
/* start bug no 4213882 */
p_pay_element_id => l_element_id,
/* End bug no 4213882 */
p_element_value_type => g_assign(l_assign_index).element_value_type,
p_element_value => g_assign(l_assign_index).element_value,
p_global_default_flag => g_assign(l_assign_index).global_default_flag,
p_assignment_default_rule_id => g_assign(l_assign_index).assignment_default_rule_id,
p_modify_flag => g_assign(l_assign_index).modify_flag,
p_pay_basis => g_assign(l_assign_index).pay_basis,
p_employee_id => g_assign(l_assign_index).employee_id,
p_primary_employee_flag => g_assign(l_assign_index).primary_employee_flag,
p_mode => p_mode);
l_updated_record := FND_API.G_TRUE;
g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
--++ pass input pay_element_option_id so that if input is salary, the updated row will
--++ reflect the input value
/* start bug no 4213882 */
IF PSB_HR_POPULATE_DATA_PVT.g_pop_assignment = 'Y' AND
PSB_HR_POPULATE_DATA_PVT.g_extract_method = 'REFRESH' THEN
l_element_id := g_assign(l_assign_index).pay_element_id;
PSB_POSITION_ASSIGNMENTS_PVT.Update_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_assignment_id => g_assign(l_assign_index).position_assignment_id,
/* Bug No 2259505 Start */
-- Commented the first line and Uncommented the second line
-- p_pay_element_option_id => p_pay_element_option_id,
p_pay_element_option_id => g_assign(l_assign_index).pay_element_option_id,
/* Bug No 2259505 End */
p_attribute_value_id => g_assign(l_assign_index).attribute_value_id,
p_attribute_value => g_assign(l_assign_index).attribute_value,
p_effective_start_date => p_effective_end_date + 1,
/* start bug no 4213882 */
p_pay_element_id => l_element_id,
/* start bug no 4213882 */
p_element_value_type => g_assign(l_assign_index).element_value_type,
p_element_value => g_assign(l_assign_index).element_value,
p_global_default_flag => g_assign(l_assign_index).global_default_flag,
p_assignment_default_rule_id => g_assign(l_assign_index).assignment_default_rule_id,
p_modify_flag => g_assign(l_assign_index).modify_flag,
p_pay_basis => g_assign(l_assign_index).pay_basis,
p_employee_id => g_assign(l_assign_index).employee_id,
p_primary_employee_flag => g_assign(l_assign_index).primary_employee_flag,
p_mode => p_mode);
l_updated_record := FND_API.G_FALSE;
g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_position_assignment_id => l_position_assignment_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => p_worksheet_id,
p_position_id => p_position_id,
p_assignment_type => p_assignment_type,
p_attribute_id => p_attribute_id,
p_attribute_value_id => p_attribute_value_id,
p_attribute_value => p_attribute_value,
p_pay_element_id => p_pay_element_id,
p_pay_element_option_id => p_pay_element_option_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_element_value_type => p_element_value_type,
p_element_value => p_element_value,
p_currency_code => p_currency_code,
p_pay_basis => g_assign(l_assign_index).pay_basis,
p_employee_id => p_employee_id,
p_primary_employee_flag => p_primary_employee_flag,
p_global_default_flag => p_global_default_flag,
p_assignment_default_rule_id => p_assignment_default_rule_id,
p_modify_flag => p_modify_flag,
p_mode => p_mode);
if FND_API.to_Boolean(l_updated_record) then
begin
PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_position_assignment_id => l_position_assignment_id,
p_data_extract_id => g_assign(l_assign_index).data_extract_id,
p_worksheet_id => p_worksheet_id,
p_position_id => g_assign(l_assign_index).position_id,
p_assignment_type => g_assign(l_assign_index).assignment_type,
p_attribute_id => g_assign(l_assign_index).attribute_id,
p_attribute_value_id => g_assign(l_assign_index).attribute_value_id,
p_attribute_value => g_assign(l_assign_index).attribute_value,
p_pay_element_id => g_assign(l_assign_index).pay_element_id,
p_pay_element_option_id => g_assign(l_assign_index).pay_element_option_id,
p_effective_start_date => p_effective_end_date + 1,
p_effective_end_date => g_assign(l_assign_index).effective_end_date,
p_element_value_type => g_assign(l_assign_index).element_value_type,
p_element_value => g_assign(l_assign_index).element_value,
p_currency_code => g_assign(l_assign_index).currency_code,
p_pay_basis => g_assign(l_assign_index).pay_basis,
p_employee_id => g_assign(l_assign_index).employee_id,
p_primary_employee_flag => g_assign(l_assign_index).primary_employee_flag,
p_global_default_flag => g_assign(l_assign_index).global_default_flag,
p_assignment_default_rule_id => g_assign(l_assign_index).assignment_default_rule_id,
p_modify_flag => g_assign(l_assign_index).modify_flag,
p_mode => p_mode);
--++ pass input pay_element_option_id so that if input is salary, the updated row will
--++ reflect the input value
PSB_POSITION_ASSIGNMENTS_PVT.Update_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_assignment_id => g_assign(l_assign_index).position_assignment_id,
/* Bug No 2259505 Start */
-- Uncommented the first line and commented the second line
p_pay_element_option_id => g_assign(l_assign_index).pay_element_option_id,
-- p_pay_element_option_id => p_pay_element_option_id,
/* Bug No 2259505 End */
p_attribute_value_id => g_assign(l_assign_index).attribute_value_id,
p_attribute_value => g_assign(l_assign_index).attribute_value,
p_effective_start_date => p_effective_end_date + 1,
p_effective_end_date => g_assign(l_assign_index).effective_end_date,
p_pay_element_id => p_pay_element_id,
p_element_value_type => g_assign(l_assign_index).element_value_type,
p_element_value => g_assign(l_assign_index).element_value,
p_pay_basis => g_assign(l_assign_index).pay_basis,
p_employee_id => g_assign(l_assign_index).employee_id,
p_primary_employee_flag => g_assign(l_assign_index).primary_employee_flag,
p_global_default_flag => g_assign(l_assign_index).global_default_flag,
p_assignment_default_rule_id => g_assign(l_assign_index).assignment_default_rule_id,
p_modify_flag => g_assign(l_assign_index).modify_flag,
p_mode => p_mode);
g_assign(l_assign_index).delete_flag := FND_API.G_FALSE;
PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_position_assignment_id => l_position_assignment_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => p_worksheet_id,
p_position_id => p_position_id,
p_assignment_type => p_assignment_type,
p_attribute_id => p_attribute_id,
p_attribute_value_id => p_attribute_value_id,
p_attribute_value => p_attribute_value,
p_pay_element_id => p_pay_element_id,
p_pay_element_option_id => p_pay_element_option_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_element_value_type => p_element_value_type,
p_element_value => p_element_value,
p_currency_code => nvl(p_currency_code, g_assign(l_assign_index).currency_code),
p_pay_basis => g_assign(l_assign_index).pay_basis,
p_employee_id => p_employee_id,
p_primary_employee_flag => p_primary_employee_flag,
p_global_default_flag => p_global_default_flag,
p_assignment_default_rule_id => p_assignment_default_rule_id,
p_modify_flag => p_modify_flag,
p_mode => p_mode);
(FND_API.to_Boolean(g_assign(l_assign_index).delete_flag)) and (g_assign(l_assign_index).worksheet_id is not null)
) then
begin
/* Start bug 4153562 */
-- if the method is refresh and the overlap start date
-- is greater than the input start date, then delete the overlap record
-- as it again created the overlap record. This is only in case of refresh
-- for worksheet specific records, it is bound to create the records, this
-- logic is not being used.
IF (PSB_HR_POPULATE_DATA_PVT.g_extract_method = 'REFRESH') THEN
IF ( g_assign(l_assign_index).effective_start_date > p_effective_start_date ) THEN
PSB_POSITION_ASSIGNMENTS_PVT.Delete_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_assignment_id => g_assign(l_assign_index).position_assignment_id);
PSB_POSITION_ASSIGNMENTS_PVT.Delete_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_assignment_id => g_assign(l_assign_index).position_assignment_id);
select position_assignment_id
from PSB_POSITION_ASSIGNMENTS
where worksheet_id = p_worksheet_id
and (((p_assignment_type = 'ATTRIBUTE')
and (attribute_id = p_attribute_id))
or ((p_assignment_type = 'EMPLOYEE')
and (employee_id = p_employee_id))
or ((p_assignment_type = 'ELEMENT')
and (pay_element_id = p_pay_element_id)
and ((p_currency_code is null) or (currency_code = p_currency_code))))
and ((((p_effective_end_date is not null)
and ((effective_start_date <= p_effective_end_date)
and (effective_end_date is null))
or ((effective_start_date between p_effective_start_date and p_effective_end_date)
or (effective_end_date between p_effective_start_date and p_effective_end_date)
or ((effective_start_date < p_effective_start_date)
and (effective_end_date > p_effective_end_date)))))
or ((p_effective_end_date is null)
and (nvl(effective_end_date, p_effective_start_date) >= p_effective_start_date)))
and position_id = p_position_id;
select salary_flag
from PSB_PAY_ELEMENTS
where pay_element_id = p_pay_element_id;
PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_position_assignment_id => l_position_assignment_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => p_worksheet_id,
p_position_id => p_position_id,
p_assignment_type => p_assignment_type,
p_attribute_id => p_attribute_id,
p_attribute_value_id => p_attribute_value_id,
p_attribute_value => p_attribute_value,
p_pay_element_id => p_pay_element_id,
p_pay_element_option_id => p_pay_element_option_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_element_value_type => p_element_value_type,
p_element_value => p_element_value,
p_currency_code => p_currency_code,
p_pay_basis => p_pay_basis,
p_employee_id => p_employee_id,
p_primary_employee_flag => p_primary_employee_flag,
p_global_default_flag => p_global_default_flag,
p_assignment_default_rule_id => p_assignment_default_rule_id,
p_modify_flag => p_modify_flag);
SELECT position_id,
vacant_position_flag,
effective_start_date,
effective_end_date
FROM psb_positions
WHERE data_extract_id = p_data_extract_id ;
SELECT effective_start_date,
effective_end_date
FROM psb_positions
WHERE position_id = p_position_id ;
l_position_id_tbl.DELETE ;
SELECT a.default_rule_id,
b.priority,
b.global_default_flag,
a.assignment_type,
a.attribute_id,
a.attribute_value_id,
a.attribute_value,
a.pay_element_id,
a.pay_element_option_id,
a.pay_basis,
a.element_value_type,
a.element_value,
a.currency_code
FROM PSB_DEFAULT_ASSIGNMENTS a,
PSB_DEFAULTS b,
PSB_SET_RELATIONS c,
PSB_BUDGET_POSITIONS d
WHERE a.default_rule_id = b.default_rule_id
AND b.priority is not null
AND b.default_rule_id = c.default_rule_id
AND c.account_position_set_id = d.account_position_set_id
AND d.data_extract_id = p_data_extract_id
AND d.position_id = p_position_id
order by b.priority;
selecting only the assignments for a given default ruleset */
-- Bug 4237598 Modified the following cursor
-- so that it will pick rule details for global
-- and non-global default rules
-- Bug 5040737 used order by 2 clause in the following cursor
CURSOR c_Assignment_Ruleset IS
SELECT a.default_rule_id,
f.priority priority,
b.global_default_flag,
b.overwrite,
a.assignment_type,
a.attribute_id,
a.attribute_value_id,
a.attribute_value,
a.pay_element_id,
a.pay_element_option_id,
a.pay_basis,
a.element_value_type,
a.element_value,
a.currency_code
FROM psb_default_assignments a,
psb_defaults b,
psb_set_relations c,
psb_budget_positions d,
psb_entity_set e,
psb_entity_assignment f
WHERE a.default_rule_id = b.default_rule_id
-- AND f.priority IS NOT NULL
AND b.default_rule_id = c.default_rule_id
AND c.account_position_set_id = d.account_position_set_id
AND d.data_extract_id = p_data_extract_id
AND d.position_id = p_position_id
AND e.entity_set_id = f.entity_set_id
AND f.entity_id = b.default_rule_id
AND e.data_extract_id = p_data_extract_id
AND e.entity_type = 'DEFAULT_RULE'
AND e.entity_set_id = p_ruleset_id
UNION
SELECT a.default_rule_id,
d.priority priority,
b.global_default_flag,
b.overwrite,
a.assignment_type,
a.attribute_id,
a.attribute_value_id,
a.attribute_value,
a.pay_element_id,
a.pay_element_option_id,
a.pay_basis,
a.element_value_type,
a.element_value,
a.currency_code
FROM psb_default_assignments a,
psb_defaults b,
psb_entity_set c,
psb_entity_assignment d
WHERE a.default_rule_id = b.default_rule_id
AND b.global_default_flag = 'Y'
AND b.data_extract_id = p_data_extract_id
AND c.entity_set_id = d.entity_set_id
AND b.default_rule_id = d.entity_id
AND c.data_extract_id = p_data_extract_id
AND c.entity_type = 'DEFAULT_RULE'
AND c.entity_set_id = p_ruleset_id
ORDER BY 2;
SELECT a.default_rule_id,
a.priority,
a.global_default_flag
FROM PSB_DEFAULTS a,
PSB_SET_RELATIONS b,
PSB_BUDGET_POSITIONS c
WHERE EXISTS
(SELECT 1
FROM PSB_DEFAULT_ACCOUNT_DISTRS d
WHERE d.default_rule_id = a.default_rule_id)
AND a.priority is not null
AND a.default_rule_id = b.default_rule_id
AND b.account_position_set_id = c.account_position_set_id
AND c.data_extract_id = p_data_extract_id
AND c.position_id = p_position_id
ORDER BY a.priority;
SELECT a.default_rule_id,
f.priority priority,
a.global_default_flag,
a.overwrite
FROM psb_defaults a,
psb_set_relations b,
psb_budget_positions c,
psb_entity_set e,
psb_entity_assignment f
WHERE EXISTS -- Bug 4226623 added the exists clause
(SELECT 1
FROM PSB_DEFAULT_ACCOUNT_DISTRS d
WHERE d.default_rule_id = a.default_rule_id)
-- AND f.priority is not null
AND a.default_rule_id = b.default_rule_id
AND b.account_position_set_id = c.account_position_set_id
AND c.data_extract_id = p_data_extract_id
AND c.position_id = p_position_id
AND e.entity_set_id = f.entity_set_id
AND f.entity_id = a.default_rule_id
AND e.data_extract_id = p_data_extract_id
AND e.entity_type = 'DEFAULT_RULE'
AND e.entity_set_id = p_ruleset_id
UNION
SELECT a.default_rule_id,
c.priority priority,
a.global_default_flag,
a.overwrite
FROM psb_defaults a,
psb_entity_set b,
psb_entity_assignment c
WHERE EXISTS (SELECT 1
FROM PSB_DEFAULT_ACCOUNT_DISTRS d
WHERE d.default_rule_id = a.default_rule_id)
AND a.global_default_flag = 'Y'
AND a.data_extract_id = p_data_extract_id
AND b.entity_set_id = c.entity_set_id
AND a.default_rule_id = c.entity_id
AND b.data_extract_id = p_data_extract_id
AND b.entity_type = 'DEFAULT_RULE'
AND b.entity_set_id = p_ruleset_id
ORDER BY 2;
select chart_of_accounts_id,
code_combination_id,
distribution_percent
from PSB_DEFAULT_ACCOUNT_DISTRS
where default_rule_id = l_default_rule_id;
SELECT *
FROM PSB_POSITION_PAY_DISTRIBUTIONS
WHERE (((p_position_end_date IS NOT NULL)
AND (((effective_start_date <= p_position_end_date)
AND (effective_end_date IS NULL))
OR ((effective_start_date BETWEEN p_position_start_date AND p_position_end_date)
OR (effective_end_date BETWEEN p_position_start_date AND p_position_end_date)
OR ((effective_start_date < p_position_start_date)
AND (effective_end_date > p_position_end_date)))))
OR ((p_position_end_date IS NULL)
AND (NVL(effective_end_date, p_position_start_date) >= p_position_start_date)))
AND data_extract_id = p_data_extract_id
AND position_id = p_position_id
/* Bug 4545909 Start */
AND ((worksheet_id IS NULL AND NOT EXISTS
(SELECT 1 FROM psb_position_pay_distributions
WHERE worksheet_id = p_worksheet_id
AND position_id = p_position_id))
OR worksheet_id = p_worksheet_id
OR(worksheet_id IS NULL AND p_worksheet_id IS NULL));
SELECT 'Exists'
FROM dual
WHERE EXISTS
(SELECT 1
FROM PSB_DEFAULT_ACCOUNT_DISTRS a,
PSB_DEFAULTS b
WHERE a.default_rule_id = b.default_rule_id
AND b.global_default_flag = 'Y'
AND b.data_extract_id = p_data_extract_id);
PSB_POSITION_PAY_DISTR_PVT.Delete_Distributions_Position
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_id => p_position_id);
SELECT 'Exists'
FROM dual
WHERE EXISTS
(SELECT 1
FROM PSB_DEFAULT_ACCOUNT_DISTRS a,
PSB_DEFAULTS b
WHERE a.default_rule_id = b.default_rule_id
AND b.global_default_flag = 'Y'
AND b.data_extract_id = p_data_extract_id
AND a.default_rule_id = l_default_rule_id
);
PSB_POSITION_PAY_DISTR_PVT.Delete_Distributions_Position
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_id => p_position_id,
p_worksheet_id => NULL);
select a.default_rule_id,
b.priority,
b.global_default_flag,
a.pay_element_id,
a.pay_element_option_id,
a.pay_basis,
a.element_value_type,
a.element_value,
a.currency_code
from PSB_DEFAULT_ASSIGNMENTS a,
PSB_DEFAULTS b,
PSB_SET_RELATIONS c,
PSB_BUDGET_POSITIONS d
where EXISTS
( select 1
from PSB_PAY_ELEMENTS pe
where pe.salary_flag <> 'Y'
and pe.data_extract_id = p_data_extract_id
and pe.pay_element_id = a.pay_element_id
)
and a.assignment_type = 'ELEMENT'
and a.default_rule_id = b.default_rule_id
and b.priority is not null
and b.default_rule_id = c.default_rule_id
and c.account_position_set_id = d.account_position_set_id
and d.data_extract_id = p_data_extract_id
and d.position_id = p_position_id
order by b.priority;
select a.default_rule_id,
a.assignment_type,
a.attribute_id,
a.attribute_value_id,
a.attribute_value,
a.pay_element_id,
a.pay_element_option_id,
a.pay_basis,
a.element_value_type,
a.element_value,
a.currency_code
from PSB_DEFAULT_ASSIGNMENTS a,
PSB_DEFAULTS b
where a.default_rule_id = b.default_rule_id
and b.global_default_flag = 'Y'
and b.data_extract_id = p_data_extract_id;
select a.default_rule_id,
a.chart_of_accounts_id,
a.code_combination_id,
a.distribution_percent
from PSB_DEFAULT_ACCOUNT_DISTRS a,
PSB_DEFAULTS b
where a.default_rule_id = b.default_rule_id
and b.global_default_flag = 'Y'
and b.data_extract_id = p_data_extract_id;
FOR l_pos_distr_rec IN ( SELECT 1
FROM dual
WHERE NOT EXISTS ( SELECT 1
FROM psb_position_pay_distributions
WHERE position_id = p_position_id
AND data_extract_id = p_data_extract_id
AND worksheet_id IS NULL))
LOOP
l_distr_exists := FALSE;
FOR l_pos_distr_rec IN ( SELECT 1
FROM dual
WHERE NOT EXISTS ( SELECT 1
FROM psb_position_pay_distributions
WHERE position_id = p_position_id
AND data_extract_id = p_data_extract_id
AND worksheet_id = l_worksheet_id))
LOOP
l_distr_exists := FALSE;
p_select_date in date := fnd_api.g_miss_date
) IS
BEGIN
g_Worksheet_ID := p_Worksheet_ID;
if p_select_date <> fnd_api.g_miss_date then
g_Select_Date := p_Select_date ;
g_Select_Date := Null ;
SELECT effective_start_date ,
effective_end_date
FROM psb_positions
WHERE position_id = p_position_id ;
SELECT worksheet_id,local_copy_flag,global_worksheet_id,
budget_calendar_id
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id ;
SELECT min(start_date) , max(end_date)
FROM psb_worksheets w,psb_budget_periods b
WHERE b.budget_calendar_id = w.budget_calendar_id AND
w.worksheet_id = l_out_worksheet_id AND
budget_period_type = 'Y';
SELECT decode(global_budget_revision,'Y',budget_revision_id,global_budget_revision_id)
FROM psb_budget_revisions
WHERE budget_revision_id = p_worksheet_id;
select 'Salary Overlaps'
from PSB_POSITION_ASSIGNMENTS a,
PSB_PAY_ELEMENTS b
where ((((p_effective_end_date is not null)
and ((a.effective_start_date <= p_effective_end_date)
and (a.effective_end_date is null))
or ((a.effective_start_date between p_effective_start_date and p_effective_end_date)
or (a.effective_end_date between p_effective_start_date and p_effective_end_date)
or ((a.effective_start_date < p_effective_start_date)
and (a.effective_end_date > p_effective_end_date)))))
or ((p_effective_end_date is null)
and (nvl(a.effective_end_date, p_effective_start_date) >= p_effective_start_date)))
and ( (nvl(a.worksheet_id, FND_API.G_MISS_NUM) = nvl(p_worksheet_id, FND_API.G_MISS_NUM)) OR
(p_worksheet_id is not null and worksheet_id is null
and not exists
(select 1 from
psb_position_assignments c ,psb_pay_elements pe2
where c.position_id = a.position_id
and c.pay_element_id = pe2.pay_element_id
and pe2.salary_flag = 'Y'
and c.worksheet_id = p_worksheet_id
and ( (
nvl(c.effective_start_date,PSB_POSITIONS_PVT.GET_end_date+1) between
nvl(a.effective_start_date,PSB_POSITIONS_PVT.GET_end_date) and
nvl(a.effective_end_date,nvl(PSB_POSITIONS_PVT.GET_end_date,
c.effective_start_date ))) or (
nvl(a.effective_start_date,PSB_POSITIONS_PVT.GET_end_date+1) between
nvl(c.effective_start_date,PSB_POSITIONS_PVT.GET_end_date) and
nvl(c.effective_end_date,nvl(PSB_POSITIONS_PVT.GET_end_date,
a.effective_start_date ))) )
)
)
)
and a.pay_element_id = b.pay_element_id
and a.position_id = p_position_id
and b.pay_element_id <> p_pay_element_id
and b.salary_flag = 'Y'
and b.data_extract_id = p_data_extract_id;
SELECT data_extract_id,
budget_calendar_id,
budget_group_id
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id;
SELECT nvl(root_budget_group_id,budget_group_id) ,
nvl(root_chart_of_accounts_id,chart_of_accounts_id),
nvl(root_set_of_books_id,set_of_books_id)
FROM psb_budget_groups_v
WHERE budget_group_id = l_budget_group_id;
(SELECT pos.position_id,
pos.effective_start_date,
pos.effective_end_date,
pos.name ,
emp.employee_number
FROM psb_positions pos,
psb_employees emp
WHERE pos.data_extract_id = l_data_extract_id
AND pos.hr_employee_id = emp.hr_employee_id(+)
AND emp.data_extract_id(+) = l_data_extract_id
ORDER BY name
)
LOOP
VALIDATE_POSITION ( p_worksheet_id => p_worksheet_id,
p_position_id => l_positions_rec.position_id,
p_name => l_positions_rec.name,
p_employee_number => l_positions_rec.employee_number,
p_data_extract_id => l_data_extract_id,
p_root_budget_group_id => l_root_budget_group_id,
p_set_of_books_id => l_set_of_books_id,
p_budget_calendar_id => l_budget_calendar_id,
p_chart_of_accounts_id => l_chart_of_accounts_id,
p_position_start_date => l_positions_rec.effective_start_date,
p_position_end_date => l_positions_rec.effective_end_date,
p_startdate_pp => PSB_WS_ACCT1.g_startdate_pp,
p_enddate_cy => PSB_WS_ACCT1.g_enddate_cy,
p_effective_start_date => PSB_WS_ACCT1.g_startdate_cy,
p_effective_end_date => PSB_WS_ACCT1.g_end_est_date,
p_error_flag => l_error_flag,
p_return_status => l_return_status);
SELECT data_extract_id,
budget_calendar_id,
budget_group_id
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id;
SELECT 'Job Exists'
FROM dual
WHERE exists
(SELECT 1
FROM psb_attribute_values patv,
psb_position_assignments pass,
psb_attributes pat
WHERE patv.attribute_value_id = pass.attribute_value_id
AND (pass.worksheet_id is NULL OR pass.worksheet_id = p_worksheet_id)
AND pass.attribute_id = pat.attribute_id
AND pass.position_id = p_position_id
AND pat.system_attribute_type = 'JOB_CLASS');
SELECT pass.effective_start_date,pass.effective_end_date,pass.pay_basis
FROM psb_pay_elements pe,
psb_position_assignments pass
WHERE pe.salary_flag = 'Y'
AND pe.pay_element_id = pass.pay_element_id
AND (pass.worksheet_id is NULL OR pass.worksheet_id = p_worksheet_id)
AND (((pass.effective_start_date <= p_effective_end_date)
and (pass.effective_end_date is null))
or ((pass.effective_start_date between p_effective_start_date and p_effective_end_date)
or (pass.effective_end_date between p_effective_start_date and p_effective_end_date)
or ((pass.effective_start_date < p_effective_start_date)
and (pass.effective_end_date > p_effective_end_date))))
AND pass.position_id = p_position_id ;
SELECT 'Invalid Pay Basis'
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM psb_pay_elements pe,
psb_position_assignments pass
WHERE NVL(pass.pay_basis,'DUMMY') NOT IN ('ANNUAL', 'HOURLY', 'MONTHLY', 'PERIOD')
AND pe.salary_flag = 'Y'
AND pe.pay_element_id = pass.pay_element_id
AND (pass.worksheet_id is NULL OR pass.worksheet_id = p_worksheet_id)
AND (((pass.effective_start_date <= p_effective_end_date)
AND (pass.effective_end_date is null))
OR ((pass.effective_start_date between p_effective_start_date and p_effective_end_date)
OR (pass.effective_end_date between p_effective_start_date and p_effective_end_date)
OR ((pass.effective_start_date < p_effective_start_date)
AND (pass.effective_end_date > p_effective_end_date))))
AND pass.position_id = p_position_id);
SELECt bp.start_date,
bp.end_date
FROM psb_budget_periods bp
WHERE bp.budget_period_type = 'C'
AND bp.budget_calendar_id = p_budget_calendar_id
ORDER by bp.start_date;
SELECT 'Default Weekly Hours Exists'
FROM dual
WHERE exists
(SELECT 1
FROM psb_attributes pat,
psb_position_assignments pass
WHERE pat.attribute_id = pass.attribute_id
AND pat.system_attribute_type = 'DEFAULT_WEEKLY_HOURS'
AND (pass.worksheet_id is NULL OR pass.worksheet_id = p_worksheet_id)
AND pass.position_id = p_position_id);
l_dynamic_insert_flag VARCHAR2(1) := 'N';
SELECT a.budget_period_id,
a.budget_year_type_id,
b.year_category_type,
period_distribution_type,
calculation_period_type,
a.name,
a.start_date,
a.end_date
FROM psb_budget_year_types b,
psb_budget_periods a
WHERE b.budget_year_type_id = a.budget_year_type_id
AND a.budget_period_type = 'Y'
AND a.budget_calendar_id = P_budget_calendar_id
ORDER BY a.start_date)
LOOP
IF c_budyr_rec.year_category_type = 'PP' THEN
IF l_firstpp THEN
l_firstpp := FALSE;
SELECT code_combination_id,
distribution_percent,
effective_start_date,
effective_end_date
FROM psb_position_pay_distributions a
WHERE code_combination_id is not null
AND chart_of_accounts_id = p_chart_of_accounts_id
AND (worksheet_id is null
AND NOT EXISTS
(SELECT 1
FROM psb_position_pay_distributions c
WHERE (
( NVL(c.effective_start_date, l_end_est_date + 1)
BETWEEN NVL(a.effective_start_date, l_end_est_date)
AND NVL(a.effective_end_date, NVL(l_end_est_date, c.effective_start_date)))
OR ( NVL(a.effective_start_date, l_end_est_date + 1)
BETWEEN NVL(c.effective_start_date, l_end_est_date)
AND NVL(c.effective_end_date, NVL(l_end_est_date, a.effective_start_date)))
)
AND c.position_id = a.position_id
AND c.chart_of_accounts_id = p_chart_of_accounts_id
AND c.code_combination_id is null
AND c.worksheet_id = p_worksheet_id
))
AND position_id = p_position_id
ORDER BY distribution_percent desc)
LOOP
l_ccid := c_dist_ws_rec.code_combination_id;
FOR c_seg_count_rec IN(SELECT COUNT(segment_num) segment_count
FROM fnd_id_flex_segments
WHERE id_flex_code = 'GL#'
AND ID_FLEX_NUM = p_chart_of_accounts_id
AND ENABLED_FLAG = 'Y')
LOOP
l_last_index := c_seg_count_rec.segment_count;
FOR c_dist_rec in(SELECT DISTINCT a.code_combination_id, a.segment1, a.segment2,
a.segment3, a.segment4,
a.segment5, a.segment6, a.segment7, a.segment8,
a.segment9, a.segment10, a.segment11, a.segment12,
a.segment13, a.segment14, a.segment15, a.segment16,
a.segment17, a.segment18, a.segment19, a.segment20,
a.segment21, a.segment22, a.segment23, a.segment24,
a.segment25, a.segment26, a.segment27, a.segment28,
a.segment29, a.segment30,
a.effective_start_date, a.effective_end_date,
e.position_id
FROM
psb_pay_element_distributions a,
psb_pay_elements b,
psb_element_pos_Set_groups c,
psb_set_relations d,
psb_budget_positions e,
psb_position_assignments f
WHERE
a.position_set_group_id = c.position_set_group_id
AND b.pay_element_id = c.pay_element_id
AND b.data_extract_id = l_data_extract_id
AND b.data_extract_id = e.data_extract_id
AND d.account_position_set_id = e.account_position_set_id
AND c.position_set_group_id = d.position_set_group_id
AND e.position_id = p_position_id
AND e.position_id = f.position_id
AND f.assignment_type = 'ELEMENT'
AND f.pay_element_id = b.pay_element_id)
/* Bug 3692601 Start */
-- AND a.code_combination_id IS NULL)
/* Bug 3692601 End */
LOOP
/* Bug 3692601 Start */
IF c_dist_rec.code_combination_id IS NULL THEN
/* Bug 3692601 End */
FOR l_init_index in 1..l_last_index
LOOP
l_seg_val(l_init_index) := NULL;
(SELECT detail_budgeting_allowed_flag, summary_flag
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id = l_ccid_overwritten
)
LOOP
IF cc_rec.detail_budgeting_allowed_flag = 'N'
OR cc_rec.summary_flag = 'Y' THEN
SET_POS_HEADING(l_position_flag, p_name,p_employee_number, p_error_flag);
(SELECT detail_budgeting_allowed_flag, summary_flag
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id = c_dist_rec.code_combination_id
)
LOOP
IF cc_rec.detail_budgeting_allowed_flag = 'N'
OR cc_rec.summary_flag = 'Y' THEN
l_concat_segments := FND_FLEX_EXT.Get_Segs
(application_short_name => 'SQLGL',
key_flex_code => 'GL#',
structure_number => p_chart_of_accounts_id,
combination_id => c_dist_rec.code_combination_id);
SELECT x_sum.sum_tot,x_sum.start_date
FROM
(SELECT SUM(distribution_percent) sum_tot ,
effective_start_date start_date
FROM psb_position_pay_distributions
WHERE code_combination_id IS NOT NULL
AND worksheet_id is null
AND position_id = p_position_id
AND (((effective_start_date <= p_effective_end_date)
AND (effective_end_date is null))
OR ((effective_start_date between p_effective_start_date and p_effective_end_date)
OR (effective_end_date between p_effective_start_date and p_effective_end_date)
OR ((effective_start_date < p_effective_start_date)
AND (effective_end_date > p_effective_end_date))))
GROUP BY position_id,effective_start_date
) x_sum
WHERE x_sum.sum_tot <> 100
UNION
SELECT x_sum.sum_tot,x_sum.start_date
FROM
(SELECT SUM(distribution_percent) sum_tot ,
effective_start_date start_date
FROM psb_position_pay_distributions
WHERE code_combination_id IS NOT NULL
AND worksheet_id = p_worksheet_id
AND position_id = p_position_id
AND (((effective_start_date <= p_effective_end_date)
AND (effective_end_date is null))
OR ((effective_start_date between p_effective_start_date and p_effective_end_date)
OR (effective_end_date between p_effective_start_date and p_effective_end_date)
OR ((effective_start_date < p_effective_start_date)
AND (effective_end_date > p_effective_end_date))))
GROUP BY position_id,effective_start_date
) x_sum
WHERE x_sum.sum_tot <> 100;
(SELECT position_id,
code_combination_id
FROM psb_position_pay_distributions
WHERE position_id = p_position_id
AND code_combination_id IS NOT NULL
AND (((effective_start_date <= p_effective_end_date)
AND (effective_end_date is null))
OR ((effective_start_date between p_effective_start_date and p_effective_end_date)
OR (effective_end_date between p_effective_start_date and p_effective_end_date)
OR ((effective_start_date < p_effective_start_date)
AND (effective_end_date > p_effective_end_date))))
)
LOOP
l_salary_distr_exists := FND_API.G_TRUE; -- distribution exists
delete from PSB_ERROR_MESSAGES
where source_process = 'POSITION_WORKSHEET_EXCEPTION'
and process_id = p_worksheet_id;
PSB_MESSAGE_S.Insert_Error ( p_source_process => 'POSITION_WORKSHEET_EXCEPTION',
p_process_id => p_worksheet_id,
p_msg_count => l_msg_count,
p_msg_data => l_msg_buf,
p_desc_sequence => FND_API.G_FALSE) ;
select 'Valid'
from PSB_BUDGET_GROUPS
where budget_group_type = 'R'
and (p_startdate_pp is null or effective_start_date <= p_startdate_pp)
and (effective_end_date is null
or effective_end_date >= p_enddate_cy)
and budget_group_id = p_position_budget_group_id
start with budget_group_id = l_budget_group_id
connect by prior budget_group_id = parent_budget_group_id;
select budget_group_id
from PSB_BUDGET_REVISIONS
where budget_revision_id = Worksheet;
FUNCTION Get_Select_Date RETURN DATE IS
BEGIN
Return g_Select_Date;
END Get_Select_Date;
SELECT emp.employee_id
INTO l_emp_id
FROM psb_employees emp, psb_position_assignments pavb
WHERE pavb.position_id = p_position_id
AND pavb.assignment_type = 'EMPLOYEE'
AND emp.data_extract_id = p_data_extract_id
AND emp.employee_id = pavb.employee_id
AND rownum=1
AND pavb.worksheet_id IS NULL
ORDER BY pavb.effective_start_date DESC;
SELECT emp.employee_id
INTO l_emp_id
FROM psb_employees emp, psb_position_assignments pavb
WHERE pavb.position_id = p_position_id
AND pavb.assignment_type = 'EMPLOYEE'
AND emp.data_extract_id = p_data_extract_id
AND emp.employee_id = pavb.employee_id
AND rownum=1
AND (pavb.worksheet_id = p_worksheet_id
OR pavb.worksheet_id IS NULL)
ORDER BY pavb.effective_start_date DESC,
NVL(pavb.worksheet_id,0) DESC;
SELECT emp.employee_number
INTO l_emp_number
FROM psb_employees emp, psb_position_assignments pavb
WHERE pavb.position_id = p_position_id
AND pavb.assignment_type = 'EMPLOYEE'
AND emp.data_extract_id = p_data_extract_id
AND emp.employee_id = pavb.employee_id
AND rownum=1
AND pavb.worksheet_id IS NULL
ORDER BY pavb.effective_start_date DESC;
SELECT emp.employee_number
INTO l_emp_number
FROM psb_employees emp, psb_position_assignments pavb
WHERE pavb.position_id = p_position_id
AND pavb.assignment_type = 'EMPLOYEE'
AND emp.data_extract_id = p_data_extract_id
AND emp.employee_id = pavb.employee_id
AND rownum=1
AND (pavb.worksheet_id = p_worksheet_id
OR pavb.worksheet_id IS NULL)
ORDER BY pavb.effective_start_date DESC,
NVL(pavb.worksheet_id,0) DESC;
SELECT emp.full_name
INTO l_emp_name
FROM psb_employees emp, psb_position_assignments pavb
WHERE pavb.position_id = p_position_id
AND pavb.assignment_type = 'EMPLOYEE'
AND emp.data_extract_id = p_data_extract_id
AND emp.employee_id = pavb.employee_id
AND rownum=1
AND pavb.worksheet_id IS NULL
ORDER BY pavb.effective_start_date DESC;
SELECT emp.full_name
INTO l_emp_name
FROM psb_employees emp, psb_position_assignments pavb
WHERE pavb.position_id = p_position_id
AND pavb.assignment_type = 'EMPLOYEE'
AND emp.data_extract_id = p_data_extract_id
AND emp.employee_id = pavb.employee_id
AND rownum=1
AND (pavb.worksheet_id = p_worksheet_id
OR pavb.worksheet_id IS NULL)
ORDER BY pavb.effective_start_date DESC,
NVL(pavb.worksheet_id,0) DESC;
SELECT patv.attribute_value
INTO l_job_name
FROM psb_attribute_values patv,
psb_position_assignments pava
WHERE patv.attribute_value_id = pava.attribute_value_id
AND pava.position_id = p_position_id
AND patv.data_extract_id = p_data_extract_id
AND rownum=1
AND EXISTS
(SELECT 1 FROM psb_attributes pat
WHERE pat.attribute_id = pava.attribute_id
AND pat.system_attribute_type = 'JOB_CLASS')
AND pava.worksheet_id IS NULL
ORDER BY pava.effective_start_date DESC;
SELECT patv.attribute_value
INTO l_job_name
FROM psb_attribute_values patv,
psb_position_assignments pava
WHERE patv.attribute_value_id = pava.attribute_value_id
AND pava.position_id = p_position_id
AND patv.data_extract_id = p_data_extract_id
AND rownum=1
AND exists (SELECT 1 from psb_attributes pat
WHERE pat.attribute_id = pava.attribute_id
AND pat.system_attribute_type = 'JOB_CLASS')
AND (pava.worksheet_id = p_worksheet_id
OR pava.worksheet_id IS NULL)
ORDER BY pava.effective_start_date DESC,
NVL(pava.worksheet_id,0) DESC;
SELECT salary_flag
FROM PSB_PAY_ELEMENTS ppay
WHERE ppay.pay_element_id = l_pay_element_id;
SELECT pay_basis
FROM psb_position_assignments past
WHERE past.assignment_type = 'ELEMENT'
AND past.position_id = p_position_id
AND past.pay_basis IS NOT NULL
AND ROWNUM < 2;
CURSOR l_exists IS SELECT assignment_type,pay_element_id
FROM psb_position_assignments
WHERE (((p_assignment_type = 'ELEMENT') AND (p_assignment_type = assignment_type))
OR ((p_assignment_type = 'ATTRIBUTE') AND (attribute_id = p_attribute_id))
OR ((p_assignment_type = 'EMPLOYEE') AND (employee_id = p_employee_id)))
AND data_extract_id = p_data_extract_id
AND position_id = p_position_id and worksheet_id IS NULL;
CURSOR l_element IS SELECT pay_element_id,salary_flag
FROM psb_pay_elements
WHERE pay_element_id = l_element_id;
UPDATE PSB_POSITION_ASSIGNMENTS
SET attribute_value_id = DECODE(p_attribute_value_id, NULL, attribute_value_id, p_attribute_value_id),
attribute_value = DECODE(p_attribute_value, NULL, attribute_value, p_attribute_value),
pay_element_option_id = DECODE(p_pay_element_option_id, NULL, pay_element_option_id, p_pay_element_option_id),
element_value_type = DECODE(p_element_value_type, NULL, element_value_type, p_element_value_type),
element_value = DECODE(p_element_value, NULL, element_value, p_element_value),
currency_code = DECODE(p_currency_code, NULL, currency_code, p_currency_code),
pay_basis = DECODE(p_pay_basis, NULL, pay_basis, p_pay_basis),
primary_employee_flag = DECODE(p_primary_employee_flag, NULL, primary_employee_flag, p_primary_employee_flag),
global_default_flag = DECODE(p_global_default_flag, NULL, global_default_flag, p_global_default_flag),
assignment_default_rule_id = DECODE(p_assignment_default_rule_id, NULL, assignment_default_rule_id, p_assignment_default_rule_id),
modify_flag = DECODE(p_modify_flag, NULL, modify_flag, 'Y'),
last_update_date = SYSDATE,
last_updated_by = l_userid,
last_update_login = l_loginid
WHERE (((p_assignment_type = 'ELEMENT') AND (pay_element_id = p_pay_element_id))
OR ((p_assignment_type = 'ATTRIBUTE') AND (attribute_id = p_attribute_id))
OR ((p_assignment_type = 'EMPLOYEE') AND (employee_id = p_employee_id)))
AND data_extract_id = p_data_extract_id
AND position_id = p_position_id
AND (worksheet_id = p_worksheet_id OR (worksheet_id IS NULL AND p_worksheet_id IS NULL)); -- bug 4545909
FOR l_assignment_rec IN (SELECT past.position_assignment_id
FROM psb_position_assignments past ,
psb_pay_elements ppay
WHERE past.data_extract_id = p_data_extract_id
AND ((worksheet_id IS NULL AND p_worksheet_id IS NULL)
OR worksheet_id = p_worksheet_id)
AND past.position_id = p_position_id
AND past.pay_element_id = ppay.pay_element_id
AND past.assignment_type = 'ELEMENT'
AND ppay.salary_flag = 'Y'
AND(((p_effective_end_date IS NOT NULL)
AND (((past.effective_start_date <= p_effective_end_date)
AND (past.effective_end_date IS NULL))
OR ((past.effective_start_date BETWEEN p_effective_start_date AND p_effective_end_date)
OR (past.effective_end_date BETWEEN p_effective_start_date AND p_effective_end_date)
OR ((past.effective_start_date < p_effective_start_date)
AND (past.effective_end_date > p_effective_end_date)))))
OR ((p_effective_end_date IS NULL)
AND (NVL(past.effective_end_date, p_effective_start_date) >= p_effective_start_date)))
)
LOOP
PSB_POSITION_ASSIGNMENTS_PVT.delete_row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_position_assignment_id => l_assignment_rec.position_assignment_id );
PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_position_assignment_id => l_position_assignment_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => p_worksheet_id,
p_position_id => p_position_id,
p_assignment_type => p_assignment_type,
p_attribute_id => p_attribute_id,
p_attribute_value_id => p_attribute_value_id,
p_attribute_value => p_attribute_value,
p_pay_element_id => p_pay_element_id,
p_pay_element_option_id => p_pay_element_option_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_element_value_type => p_element_value_type,
p_element_value => p_element_value,
p_currency_code => p_currency_code,
p_pay_basis => l_pay_basis,
p_employee_id => p_employee_id,
p_primary_employee_flag => p_primary_employee_flag,
p_global_default_flag => p_global_default_flag,
p_assignment_default_rule_id => p_assignment_default_rule_id,
p_modify_flag => p_modify_flag,
p_mode => p_mode);
PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_position_assignment_id => l_position_assignment_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => NULL,
p_position_id => p_position_id,
p_assignment_type => p_assignment_type,
p_attribute_id => p_attribute_id,
p_attribute_value_id => p_attribute_value_id,
p_attribute_value => p_attribute_value,
p_pay_element_id => p_pay_element_id,
p_pay_element_option_id => p_pay_element_option_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_element_value_type => p_element_value_type,
p_element_value => p_element_value,
p_currency_code => p_currency_code,
p_pay_basis => l_pay_basis,
p_employee_id => p_employee_id,
p_primary_employee_flag => p_primary_employee_flag,
p_global_default_flag => p_global_default_flag,
p_assignment_default_rule_id => p_assignment_default_rule_id,
p_modify_flag => p_modify_flag,
p_mode => p_mode);
FOR l_pos_assignment_rec IN( SELECT *
FROM psb_position_assignments past
WHERE past.data_extract_id = p_data_extract_id
AND past.position_id = p_position_id
AND ((worksheet_id IS NULL AND NOT EXISTS
(SELECT 1 FROM psb_position_assignments ppa
WHERE ppa.worksheet_id = p_worksheet_id
AND ppa.position_id = p_position_id AND
(p_assignment_type = 'ATTRIBUTE' AND past.attribute_id = ppa.attribute_id) OR
(p_assignment_type = 'ELEMENT' AND past.pay_element_id = ppa.pay_element_id)))
OR worksheet_id = p_worksheet_id
OR (worksheet_id IS NULL AND p_worksheet_id IS NULL))
AND (((p_effective_end_date IS NOT NULL)
AND (((past.effective_start_date <= p_effective_end_date)
AND (past.effective_end_date IS NULL))
OR ((past.effective_start_date BETWEEN p_effective_start_date AND p_effective_end_date)
OR (past.effective_end_date BETWEEN p_effective_start_date AND p_effective_end_date)
OR ((past.effective_start_date < p_effective_start_date)
AND (past.effective_end_date > p_effective_end_date)))))
OR ((p_effective_end_date IS NULL)
AND (NVL(past.effective_end_date, p_effective_start_date) >= p_effective_start_date)))
)
LOOP
l_pos_salary_flag := 'N';
PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_position_assignment_id => l_position_assignment_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => p_worksheet_id,
p_position_id => p_position_id,
p_assignment_type => p_assignment_type,
p_attribute_id => p_attribute_id,
p_attribute_value_id => p_attribute_value_id,
p_attribute_value => p_attribute_value,
p_pay_element_id => p_pay_element_id,
p_pay_element_option_id => p_pay_element_option_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_element_value_type => p_element_value_type,
p_element_value => p_element_value,
p_currency_code => p_currency_code,
p_pay_basis => l_pay_basis,
p_employee_id => p_employee_id,
p_primary_employee_flag => p_primary_employee_flag,
p_global_default_flag => p_global_default_flag,
p_assignment_default_rule_id => p_assignment_default_rule_id,
-- p_modify_flag => p_modify_flag,
p_modify_flag => 'Y', -- bug 5002080
p_mode => p_mode);
PSB_POSITION_ASSIGNMENTS_PVT.Insert_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_rowid => l_rowid,
p_position_assignment_id => l_position_assignment_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => NULL,
p_position_id => p_position_id,
p_assignment_type => p_assignment_type,
p_attribute_id => p_attribute_id,
p_attribute_value_id => p_attribute_value_id,
p_attribute_value => p_attribute_value,
p_pay_element_id => p_pay_element_id,
p_pay_element_option_id => p_pay_element_option_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_element_value_type => p_element_value_type,
p_element_value => p_element_value,
p_currency_code => p_currency_code,
p_pay_basis => l_pay_basis,
p_employee_id => p_employee_id,
p_primary_employee_flag => p_primary_employee_flag,
p_global_default_flag => p_global_default_flag,
p_assignment_default_rule_id => p_assignment_default_rule_id,
-- p_modify_flag => p_modify_flag,
p_modify_flag => 'Y', -- bug 5002080
p_mode => p_mode);