[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_distribution_id IN NUMBER,
p_position_id IN NUMBER,
p_data_extract_id IN NUMBER,
p_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
p_effective_start_date IN DATE,
p_effective_end_date IN DATE,
p_chart_of_accounts_id IN NUMBER,
p_code_combination_id IN NUMBER,
p_distribution_percent IN NUMBER,
p_global_default_flag IN VARCHAR2,
p_distribution_default_rule_id IN NUMBER,
p_project_id IN NUMBER:= FND_API.G_MISS_NUM,
p_task_id IN NUMBER:= FND_API.G_MISS_NUM,
p_award_id IN NUMBER:= FND_API.G_MISS_NUM,
p_expenditure_type IN VARCHAR2:= FND_API.G_MISS_CHAR,
p_expenditure_organization_id IN NUMBER:= FND_API.G_MISS_NUM,
p_description IN VARCHAR2:= FND_API.G_MISS_CHAR,
p_mode in varchar2 := 'R'
) is
cursor C is select ROWID from PSB_POSITION_PAY_DISTRIBUTIONS
where distribution_id = P_distribution_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_POSITION_PAY_DISTRIBUTIONS(
distribution_id ,
position_id ,
data_extract_id ,
worksheet_id ,
effective_start_date ,
effective_end_date ,
chart_of_accounts_id ,
code_combination_id ,
distribution_percent ,
global_default_flag ,
distribution_default_rule_id ,
project_id,
task_id,
award_id,
expenditure_type,
expenditure_organization_id,
description,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
) values (
p_distribution_id ,
p_position_id ,
p_data_extract_id ,
decode(p_worksheet_id,FND_API.G_MISS_NUM,null,p_worksheet_id),
p_effective_start_date ,
p_effective_end_date ,
p_chart_of_accounts_id ,
p_code_combination_id ,
p_distribution_percent ,
p_global_default_flag ,
p_distribution_default_rule_id ,
decode(p_project_id, FND_API.G_MISS_NUM, null, p_project_id),
decode(p_task_id, FND_API.G_MISS_NUM, null, p_task_id),
decode(p_award_id, FND_API.G_MISS_NUM, null, p_award_id),
decode(p_expenditure_type, FND_API.G_MISS_CHAR, null, p_expenditure_type),
decode(p_expenditure_organization_id, FND_API.G_MISS_NUM, null, p_expenditure_organization_id),
decode(p_description, FND_API.G_MISS_CHAR, null, p_description),
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
distribution_id,
position_id,
data_extract_id,
worksheet_id,
effective_start_date,
effective_end_date,
chart_of_accounts_id,
code_combination_id,
distribution_percent,
distribution_default_rule_id,
global_default_flag,
project_id,
task_id,
award_id,
expenditure_type,
expenditure_organization_id,
description
from PSB_POSITION_PAY_DISTRIBUTIONS
where distribution_id = P_distribution_id
for update of distribution_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_distribution_id IN NUMBER,
p_code_combination_id IN NUMBER,
p_effective_start_date IN DATE := FND_API.G_MISS_DATE,
p_effective_end_date IN DATE := FND_API.G_MISS_DATE,
p_distribution_percent IN NUMBER,
p_global_default_flag IN VARCHAR2,
p_distribution_default_rule_id IN NUMBER,
p_project_id IN NUMBER:= FND_API.G_MISS_NUM,
p_task_id IN NUMBER:= FND_API.G_MISS_NUM,
p_award_id IN NUMBER:= FND_API.G_MISS_NUM,
p_expenditure_type IN VARCHAR2:= FND_API.G_MISS_CHAR,
p_expenditure_organization_id IN NUMBER:= FND_API.G_MISS_NUM,
p_description IN VARCHAR2:= FND_API.G_MISS_CHAR,
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_POSITION_PAY_DISTRIBUTIONS set
code_combination_id = p_code_combination_id,
effective_start_date = decode(p_effective_start_date, FND_API.G_MISS_DATE, effective_start_date, p_effective_start_date),
effective_end_date = decode(p_effective_end_date, FND_API.G_MISS_DATE, effective_end_date, p_effective_end_date),
distribution_percent = p_distribution_percent,
global_default_flag = p_global_default_flag,
distribution_default_rule_id = p_distribution_default_rule_id,
project_id = decode(p_project_id,FND_API.G_MISS_NUM,
project_id, p_project_id),
task_id = decode(p_task_id,FND_API.G_MISS_NUM,
task_id, p_task_id),
award_id = decode(p_award_id,FND_API.G_MISS_NUM,
award_id, p_award_id),
expenditure_type = decode(p_expenditure_type,
FND_API.G_MISS_CHAR, expenditure_type,
p_expenditure_type),
expenditure_organization_id = decode(p_expenditure_organization_id,
FND_API.G_MISS_NUM, expenditure_organization_id,
p_expenditure_organization_id),
description = decode(p_description,
FND_API.G_MISS_CHAR, description,
p_description),
last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login
where distribution_id = p_distribution_id;
rollback to Update_Row ;
rollback to Update_Row ;
rollback to Update_Row ;
END UPDATE_ROW;
cursor c1 is select rowid from PSB_POSITION_PAY_DISTRIBUTIONS
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_distribution_id => p_distribution_id,
p_position_id => p_position_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => p_worksheet_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_chart_of_accounts_id => p_chart_of_accounts_id,
p_code_combination_id => p_code_combination_id,
p_distribution_percent => p_distribution_percent,
p_global_default_flag => p_global_default_flag,
p_distribution_default_rule_id => p_distribution_default_rule_id,
p_project_id => p_project_id,
p_task_id => p_task_id,
p_award_id => p_award_id,
p_expenditure_type => p_expenditure_type,
p_expenditure_organization_id => p_expenditure_organization_id,
p_description => p_description,
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_distribution_id => p_distribution_id,
p_code_combination_id => p_code_combination_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_distribution_percent => p_distribution_percent,
p_global_default_flag => p_global_default_flag,
p_distribution_default_rule_id => p_distribution_default_rule_id,
p_project_id => p_project_id,
p_task_id => p_task_id,
p_award_id => p_award_id,
p_expenditure_type => p_expenditure_type,
p_expenditure_organization_id => p_expenditure_organization_id,
p_description => p_description,
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_distribution_id in number
) is
--
l_api_name CONSTANT VARCHAR2(30) := 'Delete Row' ;
SAVEPOINT Delete_Row ;
delete from PSB_POSITION_PAY_DISTRIBUTIONS
where distribution_id = p_distribution_id;
rollback to Delete_Row;
rollback to Delete_Row;
rollback to Delete_Row ;
END DELETE_ROW;
PROCEDURE Delete_Distributions
( 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_Distributions';
SAVEPOINT Delete_Distributions_Pvt;
delete from PSB_POSITION_PAY_DISTRIBUTIONS
where data_extract_id = p_data_extract_id;
rollback to Delete_Distributions_Pvt;
rollback to Delete_Distributions_Pvt;
rollback to Delete_Distributions_Pvt;
END Delete_Distributions;
PROCEDURE Delete_Distributions_Position
( 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,
p_worksheet_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Distributions_Position';
SAVEPOINT Delete_Dist_Position_Pvt;
DELETE from PSB_POSITION_PAY_DISTRIBUTIONS
WHERE position_id = p_position_id
/* Bug 4545909 Start */
AND ((worksheet_id IS NULL AND p_worksheet_id IS NULL)
OR worksheet_id = p_worksheet_id);
rollback to Delete_Dist_Position_Pvt;
rollback to Delete_Dist_Position_Pvt;
rollback to Delete_Dist_Position_Pvt;
END Delete_Distributions_Position;
select budget_calendar_id,
budget_group_id
from PSB_WORKSHEETS_V
where worksheet_id = p_worksheet_id;
select name,
nvl(set_of_books_id, root_set_of_books_id) set_of_books_id,
nvl(chart_of_accounts_id, root_chart_of_accounts_id) flex_code
from PSB_BUDGET_GROUPS_V
where budget_group_id = l_budget_group_id;
select effective_start_date
from psb_positions
where position_id = p_position_id;
select effective_end_date,
effective_start_date
from psb_budget_revision_positions
where budget_revision_pos_line_id = p_budget_revision_pos_line_id;
SELECT budget_group_id
FROM psb_budget_revisions
WHERE budget_revision_id = p_worksheet_id;
SELECT set_of_books_id ,
position_id_flex_num
FROM psb_data_extracts
WHERE data_extract_id = l_data_extract_id ;
select chart_of_accounts_id
into l_flex_code
from GL_SETS_OF_BOOKS
where set_of_books_id = l_set_of_books_id;
select name
into l_name
from PSB_BUDGET_GROUPS_V
where budget_group_id = l_rev_budget_group_id;
l_updated_record VARCHAR2(1);
select psb_position_pay_distr_s.nextval DistID
from dual;
select distribution_id,
position_id,
data_extract_id,
worksheet_id,
effective_start_date,
effective_end_date,
chart_of_accounts_id,
code_combination_id,
distribution_percent,
global_default_flag,
distribution_default_rule_id,
project_id,
task_id,
award_id,
expenditure_type,
expenditure_organization_id,
description
from PSB_POSITION_PAY_DISTRIBUTIONS
where (worksheet_id is null or worksheet_id = p_worksheet_id)
and chart_of_accounts_id = p_chart_of_accounts_id
and code_combination_id = p_code_combination_id
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;
update PSB_POSITION_PAY_DISTRIBUTIONS
set distribution_percent = decode(p_distribution_percent, null, distribution_percent, p_distribution_percent),
global_default_flag = decode(p_global_default_flag, null, global_default_flag, p_global_default_flag),
distribution_default_rule_id = decode(p_distribution_default_rule_id, null, distribution_default_rule_id, p_distribution_default_rule_id),
project_id = decode(p_project_id, null, project_id, FND_API.G_MISS_NUM, project_id, p_project_id),
task_id = decode(p_task_id, null, project_id, FND_API.G_MISS_NUM, task_id, p_task_id),
award_id = decode(p_award_id, null, award_id, FND_API.G_MISS_NUM, award_id, p_award_id),
expenditure_type = decode(p_expenditure_type, null, expenditure_type, FND_API.G_MISS_CHAR, expenditure_type, p_expenditure_type),
expenditure_organization_id = decode(p_expenditure_organization_id, null, expenditure_organization_id, FND_API.G_MISS_NUM, expenditure_organization_id, p_expenditure_organization_id),
description = decode(p_description, null, description, FND_API.G_MISS_CHAR, description, p_description),
-- Added for Bug: 3325171
effective_end_date = decode(p_effective_end_date, null, effective_end_date, FND_API.G_MISS_DATE, effective_end_date, p_effective_end_date),
last_update_date = sysdate,
last_updated_by = l_userid,
last_update_login = l_loginid
where position_id = p_position_id
and effective_start_date = p_effective_start_date
and nvl(effective_end_date, FND_API.G_MISS_DATE) = nvl(p_effective_end_date, FND_API.G_MISS_DATE)
and nvl(worksheet_id, FND_API.G_MISS_NUM) = nvl(p_worksheet_id, FND_API.G_MISS_NUM)
and chart_of_accounts_id = p_chart_of_accounts_id
and code_combination_id = p_code_combination_id;
g_pay_dist(l_init_index).delete_flag := null;
g_pay_dist(g_num_pay_dist).delete_flag := FND_API.G_TRUE;
if g_num_pay_dist = 0 then -- No matching records hence direct insert
begin
for c_Seq_Rec in c_Seq loop
l_distribution_id := c_Seq_Rec.DistID;
Insert_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => p_msg_count,
p_msg_data => p_msg_data,
p_rowid => l_rowid,
p_distribution_id => l_distribution_id,
p_position_id => p_position_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => p_worksheet_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_chart_of_accounts_id => p_chart_of_accounts_id,
p_code_combination_id => p_code_combination_id,
p_distribution_percent => p_distribution_percent,
p_global_default_flag => p_global_default_flag,
p_distribution_default_rule_id => p_distribution_default_rule_id,
p_project_id => p_project_id,
p_task_id => p_task_id,
p_award_id => p_award_id,
p_expenditure_type => p_expenditure_type,
p_expenditure_organization_id => p_expenditure_organization_id,
p_description => p_description,
p_mode => p_mode);
end; -- No Matching Records hence Direct Insert
l_updated_record := FND_API.G_FALSE;
Update_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => p_msg_count,
p_msg_data => p_msg_data,
p_distribution_id => g_pay_dist(l_dist_index).distribution_id,
p_code_combination_id => p_code_combination_id,
p_distribution_percent => p_distribution_percent,
p_effective_end_date => p_effective_end_date,
p_global_default_flag => p_global_default_flag,
p_distribution_default_rule_id => p_distribution_default_rule_id,
p_project_id => p_project_id,
p_task_id => p_task_id,
p_award_id => p_award_id,
p_expenditure_type => p_expenditure_type,
p_expenditure_organization_id => p_expenditure_organization_id,
p_description => p_description,
p_mode => p_mode);
g_pay_dist(l_dist_index).delete_flag := FND_API.G_FALSE;
Insert_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => p_msg_count,
p_msg_data => p_msg_data,
p_rowid => l_rowid,
p_distribution_id => l_distribution_id,
p_position_id => p_position_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => p_worksheet_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_chart_of_accounts_id => p_chart_of_accounts_id,
p_code_combination_id => p_code_combination_id,
p_distribution_percent => p_distribution_percent,
p_global_default_flag => p_global_default_flag,
p_distribution_default_rule_id => p_distribution_default_rule_id,
p_project_id => p_project_id,
p_task_id => p_task_id,
p_award_id => p_award_id,
p_expenditure_type => p_expenditure_type,
p_expenditure_organization_id => p_expenditure_organization_id,
p_description => p_description,
p_mode => p_mode);
Update_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => p_msg_count,
p_msg_data => p_msg_data,
p_distribution_id => g_pay_dist(l_dist_index).distribution_id,
p_code_combination_id => g_pay_dist(l_dist_index).code_combination_id,
p_distribution_percent => g_pay_dist(l_dist_index).distribution_percent,
p_effective_end_date => p_effective_start_date - 1,
p_global_default_flag => g_pay_dist(l_dist_index).global_default_flag,
p_distribution_default_rule_id => g_pay_dist(l_dist_index).dist_default_rule_id,
p_project_id => g_pay_dist(l_dist_index).project_id,
p_task_id => g_pay_dist(l_dist_index).task_id,
p_award_id => g_pay_dist(l_dist_index).award_id,
p_expenditure_type => g_pay_dist(l_dist_index).expenditure_type,
p_expenditure_organization_id => g_pay_dist(l_dist_index).expenditure_organization_id,
p_description => g_pay_dist(l_dist_index).description,
p_mode => p_mode);
l_updated_record := FND_API.G_TRUE;
g_pay_dist(l_dist_index).delete_flag := FND_API.G_FALSE;
Update_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => p_msg_count,
p_msg_data => p_msg_data,
p_distribution_id => g_pay_dist(l_dist_index).distribution_id,
p_code_combination_id => g_pay_dist(l_dist_index).code_combination_id,
p_distribution_percent => g_pay_dist(l_dist_index).distribution_percent,
p_effective_start_date => p_effective_end_date + 1,
p_global_default_flag => g_pay_dist(l_dist_index).global_default_flag,
p_distribution_default_rule_id => g_pay_dist(l_dist_index).dist_default_rule_id,
p_project_id => g_pay_dist(l_dist_index).project_id,
p_task_id => g_pay_dist(l_dist_index).task_id,
p_award_id => g_pay_dist(l_dist_index).award_id,
p_expenditure_type => g_pay_dist(l_dist_index).expenditure_type,
p_expenditure_organization_id => g_pay_dist(l_dist_index).expenditure_organization_id,
p_description => g_pay_dist(l_dist_index).description,
p_mode => p_mode);
l_updated_record := FND_API.G_FALSE;
g_pay_dist(l_dist_index).delete_flag := FND_API.G_FALSE;
Insert_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => p_msg_count,
p_msg_data => p_msg_data,
p_rowid => l_rowid,
p_distribution_id => l_distribution_id,
p_position_id => p_position_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => p_worksheet_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_chart_of_accounts_id => p_chart_of_accounts_id,
p_code_combination_id => p_code_combination_id,
p_distribution_percent => p_distribution_percent,
p_global_default_flag => p_global_default_flag,
p_distribution_default_rule_id => p_distribution_default_rule_id,
p_project_id => p_project_id,
p_task_id => p_task_id,
p_award_id => p_award_id,
p_expenditure_type => p_expenditure_type,
p_expenditure_organization_id => p_expenditure_organization_id,
p_description => p_description,
p_mode => p_mode);
if FND_API.to_Boolean(l_updated_record) then
begin
for c_Seq_Rec in c_Seq loop
l_distribution_id := c_Seq_Rec.DistID;
Insert_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => p_msg_count,
p_msg_data => p_msg_data,
p_rowid => l_rowid,
p_distribution_id => l_distribution_id,
p_position_id => g_pay_dist(l_dist_index).position_id,
p_data_extract_id => g_pay_dist(l_dist_index).data_extract_id,
p_worksheet_id => g_pay_dist(l_dist_index).worksheet_id,
p_effective_start_date => p_effective_end_date + 1,
p_effective_end_date => g_pay_dist(l_dist_index).effective_end_date,
p_chart_of_accounts_id => g_pay_dist(l_dist_index).chart_of_accounts_id,
p_code_combination_id => g_pay_dist(l_dist_index).code_combination_id,
p_distribution_percent => g_pay_dist(l_dist_index).distribution_percent,
p_global_default_flag => g_pay_dist(l_dist_index).global_default_flag,
p_distribution_default_rule_id => g_pay_dist(l_dist_index).dist_default_rule_id,
p_project_id => g_pay_dist(l_dist_index).project_id,
p_task_id => g_pay_dist(l_dist_index).task_id,
p_award_id => g_pay_dist(l_dist_index).award_id,
p_expenditure_type => g_pay_dist(l_dist_index).expenditure_type,
p_expenditure_organization_id => g_pay_dist(l_dist_index).expenditure_organization_id,
p_description => g_pay_dist(l_dist_index).description,
p_mode => p_mode);
Update_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => p_msg_count,
p_msg_data => p_msg_data,
p_distribution_id => g_pay_dist(l_dist_index).distribution_id,
p_code_combination_id => g_pay_dist(l_dist_index).code_combination_id,
p_effective_start_date => p_effective_end_date + 1,
p_effective_end_date => g_pay_dist(l_dist_index).effective_end_date,
p_distribution_percent => g_pay_dist(l_dist_index).distribution_percent,
p_global_default_flag => g_pay_dist(l_dist_index).global_default_flag,
p_distribution_default_rule_id => g_pay_dist(l_dist_index).dist_default_rule_id,
p_project_id => g_pay_dist(l_dist_index).project_id,
p_task_id => g_pay_dist(l_dist_index).task_id,
p_award_id => g_pay_dist(l_dist_index).award_id,
p_expenditure_type => g_pay_dist(l_dist_index).expenditure_type,
p_expenditure_organization_id => g_pay_dist(l_dist_index).expenditure_organization_id,
p_description => g_pay_dist(l_dist_index).description,
p_mode => p_mode);
g_pay_dist(l_dist_index).delete_flag := FND_API.G_FALSE;
Insert_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => p_msg_count,
p_msg_data => p_msg_data,
p_rowid => l_rowid,
p_distribution_id => l_distribution_id,
p_position_id => p_position_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => p_worksheet_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_chart_of_accounts_id => p_chart_of_accounts_id,
p_code_combination_id => p_code_combination_id,
p_distribution_percent => p_distribution_percent,
p_global_default_flag => p_global_default_flag,
p_distribution_default_rule_id => p_distribution_default_rule_id,
p_project_id => p_project_id,
p_task_id => p_task_id,
p_award_id => p_award_id,
p_expenditure_type => p_expenditure_type,
p_expenditure_organization_id => p_expenditure_organization_id,
p_description => p_description,
p_mode => p_mode);
if ((FND_API.to_Boolean(g_pay_dist(l_dist_index).delete_flag)) and (g_pay_dist(l_dist_index).worksheet_id is not null)) then
begin
Delete_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => p_msg_count,
p_msg_data => p_msg_data,
p_distribution_id => g_pay_dist(l_dist_index).distribution_id);
select psb_position_pay_distr_s.nextval DistID
from dual;
select distribution_id
from PSB_POSITION_PAY_DISTRIBUTIONS
where chart_of_accounts_id = p_chart_of_accounts_id
and worksheet_id = p_worksheet_id
and code_combination_id = p_code_combination_id
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;
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_distribution_id => l_distribution_id,
p_position_id => p_position_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => p_worksheet_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_chart_of_accounts_id => p_chart_of_accounts_id,
p_code_combination_id => p_code_combination_id,
p_distribution_percent => p_distribution_percent,
p_global_default_flag => p_global_default_flag,
p_distribution_default_rule_id => p_distribution_default_rule_id,
p_project_id => p_project_id,
p_task_id => p_task_id,
p_award_id => p_award_id,
p_expenditure_type => p_expenditure_type,
p_expenditure_organization_id => p_expenditure_organization_id,
p_description => p_description,
p_mode => p_mode );
Select distribution_id,
code_combination_id,
project_id,
task_id,
award_id,
expenditure_type,
expenditure_organization_id,
distribution_percent,
effective_start_date,
effective_end_date,
chart_of_accounts_id,
global_default_flag,
distribution_default_rule_id,
rowid
from psb_position_pay_distributions
where position_id = p_position_id
and worksheet_id is null;
select psb_position_pay_distr_s.nextval DistID
from dual;
Update_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => p_msg_count,
p_msg_data => p_msg_data,
p_distribution_id => C_Distribution_Rec.distribution_id,
/* Bug#2869982 Start */
p_code_combination_id => p_distribution(j).ccid,
--p_code_combination_id => C_Distribution_Rec.code_combination_id,
/* Bug#2869982 End */
p_distribution_percent => p_distribution(j).distr_percent,
p_effective_end_date => p_distribution(j).effective_end_date,
p_global_default_flag => C_Distribution_Rec.global_default_flag,
p_distribution_default_rule_id => C_Distribution_Rec.distribution_default_rule_id,
p_project_id => p_distribution(j).project_id,
p_task_id => p_distribution(j).task_id,
p_award_id => p_distribution(j).award_id,
p_expenditure_type => p_distribution(j).expenditure_type,
p_expenditure_organization_id => p_distribution(j).expenditure_org_id,
p_description => p_distribution(j).description,
p_mode => 'R');
DELETE_ROW (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => p_commit,
p_validation_level => fnd_api.g_valid_level_full,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
p_distribution_id => C_Distribution_Rec.distribution_id);
/* Insert new distribution */
for c_Seq_Rec in c_Seq loop
l_distribution_id := c_Seq_Rec.DistID;
Insert_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => p_msg_count,
p_msg_data => p_msg_data,
p_rowid => l_rowid,
p_distribution_id => l_distribution_id,
p_position_id => p_position_id,
p_data_extract_id => p_data_extract_id,
p_effective_start_date => p_distribution(j).effective_start_date,
p_effective_end_date => p_distribution(j).effective_end_date,
p_chart_of_accounts_id => p_chart_of_accounts_id,
p_code_combination_id => p_distribution(j).ccid,
p_distribution_percent => p_distribution(j).distr_percent,
p_global_default_flag => null,
p_distribution_default_rule_id => null,
p_project_id => p_distribution(j).project_id,
p_task_id => p_distribution(j).task_id,
p_award_id => p_distribution(j).award_id,
p_expenditure_type => p_distribution(j).expenditure_type,
p_expenditure_organization_id => p_distribution(j).expenditure_org_id,
p_description => p_distribution(j).description,
p_mode => 'R');
l_updated_record VARCHAR2(1);
SELECT psb_position_pay_distr_s.NEXTVAL DistID
FROM dual;
SELECT distribution_id,
position_id,
data_extract_id,
worksheet_id,
effective_start_date,
effective_end_date,
chart_of_accounts_id,
code_combination_id,
distribution_percent,
global_default_flag,
distribution_default_rule_id,
project_id,
task_id,
award_id,
expenditure_type,
expenditure_organization_id,
description
FROM PSB_POSITION_PAY_DISTRIBUTIONS
WHERE
/* Bug 4545909 Start */
((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))
/* Bug 4545909 End */
AND chart_of_accounts_id = p_chart_of_accounts_id
AND code_combination_id = p_code_combination_id
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 1
FROM PSB_POSITION_PAY_DISTRIBUTIONS
WHERE data_extract_id = p_data_extract_id
AND position_id = p_position_id and worksheet_id IS NULL;
g_pay_dist(l_init_index).delete_flag := NULL;
g_pay_dist(g_num_pay_dist).delete_flag := FND_API.G_TRUE;
Insert_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => x_msg_count,
p_msg_data => x_msg_data,
p_rowid => l_rowid,
p_distribution_id => l_distribution_id,
p_position_id => p_position_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => p_worksheet_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_chart_of_accounts_id => p_chart_of_accounts_id,
p_code_combination_id => p_code_combination_id,
p_distribution_percent => p_distribution_percent,
p_global_default_flag => p_global_default_flag,
p_distribution_default_rule_id => p_distribution_default_rule_id,
p_project_id => p_project_id,
p_task_id => p_task_id,
p_award_id => p_award_id,
p_expenditure_type => p_expenditure_type,
p_expenditure_organization_id => p_expenditure_organization_id,
p_description => p_description,
p_mode => p_mode);
Insert_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => x_msg_count,
p_msg_data => x_msg_data,
p_rowid => l_rowid,
p_distribution_id => l_distribution_id,
p_position_id => p_position_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => NULL,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_chart_of_accounts_id => p_chart_of_accounts_id,
p_code_combination_id => p_code_combination_id,
p_distribution_percent => p_distribution_percent,
p_global_default_flag => p_global_default_flag,
p_distribution_default_rule_id => p_distribution_default_rule_id,
p_project_id => p_project_id,
p_task_id => p_task_id,
p_award_id => p_award_id,
p_expenditure_type => p_expenditure_type,
p_expenditure_organization_id => p_expenditure_organization_id,
p_description => p_description,
p_mode => p_mode);
Update_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => x_msg_count,
p_msg_data => x_msg_data,
p_distribution_id => g_pay_dist(l_dist_index).distribution_id,
p_code_combination_id => p_code_combination_id,
p_distribution_percent =>
g_pay_dist(l_dist_index).distribution_percent + l_distribution_percent,
p_effective_end_date => p_effective_end_date,
p_global_default_flag => p_global_default_flag,
p_distribution_default_rule_id => p_distribution_default_rule_id,
p_project_id => p_project_id,
p_task_id => p_task_id,
p_award_id => p_award_id,
p_expenditure_type => p_expenditure_type,
p_expenditure_organization_id => p_expenditure_organization_id,
p_description => p_description,
p_mode => p_mode);
Insert_Row
(p_api_version => 1.0,
p_return_status => l_return_status,
p_msg_count => x_msg_count,
p_msg_data => x_msg_data,
p_rowid => l_rowid,
p_distribution_id => l_distribution_id,
p_position_id => p_position_id,
p_data_extract_id => p_data_extract_id,
p_worksheet_id => p_worksheet_id,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => p_effective_end_date,
p_chart_of_accounts_id => p_chart_of_accounts_id,
p_code_combination_id => p_code_combination_id,
p_distribution_percent => l_distribution_percent,
p_global_default_flag => p_global_default_flag,
p_distribution_default_rule_id => p_distribution_default_rule_id,
p_project_id => p_project_id,
p_task_id => p_task_id,
p_award_id => p_award_id,
p_expenditure_type => p_expenditure_type,
p_expenditure_organization_id => p_expenditure_organization_id,
p_description => p_description,
p_mode => p_mode);