[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_WS_Lines_Pvt
(
p_worksheet_id IN psb_ws_lines.worksheet_id%TYPE,
p_account_line_id IN psb_ws_lines.account_line_id%TYPE,
p_freeze_flag IN psb_ws_lines.freeze_flag%TYPE,
p_view_line_flag IN psb_ws_lines.view_line_flag%TYPE,
p_last_update_date IN psb_ws_lines.last_update_date%TYPE,
p_last_updated_by IN psb_ws_lines.last_updated_by%TYPE,
p_last_update_login IN psb_ws_lines.last_update_login%TYPE,
p_created_by IN psb_ws_lines.created_by%TYPE,
p_creation_date IN psb_ws_lines.creation_date%TYPE,
p_return_status OUT NOCOPY VARCHAR2
) ;
PROCEDURE Delete_Worksheet_Pvt
(
p_worksheet_id IN psb_worksheets.worksheet_id%TYPE ,
p_budget_by_position IN psb_worksheets.budget_by_position%TYPE ,
p_delete_lines_flag IN VARCHAR2 ,
p_return_status OUT NOCOPY VARCHAR2
) ;
SELECT *
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id ;
SELECT '1'
FROM psb_budget_accounts
WHERE account_position_set_id = c_current_account_set_id
AND code_combination_id = c_code_combination_id ;
SELECT '1'
FROM psb_budget_positions
WHERE account_position_set_id = c_current_account_set_id
AND position_id = c_position_id ;
SELECT '1' INTO l_tmp_char
FROM psb_budget_groups
WHERE budget_group_id = p_budget_group_id ;
SELECT name INTO l_main_budget_group_name
FROM psb_budget_groups
WHERE budget_group_id = l_main_budget_group_id ;
SELECT count(*) INTO l_service_package_count
FROM psb_ws_submit_service_packages
WHERE worksheet_id = p_worksheet_id
AND operation_id = NVL( p_service_package_operation_id ,
FND_API.G_MISS_NUM ) ;
SELECT lines.* ,
accts.code_combination_id ,
accts.budget_group_id
FROM psb_ws_lines lines ,
psb_ws_account_lines accts
WHERE lines.worksheet_id = p_worksheet_id
AND lines.account_line_id = accts.account_line_id
AND ( l_service_package_count = 0
OR
accts.service_package_id IN
( SELECT service_package_id
FROM psb_ws_submit_service_packages
WHERE worksheet_id = p_worksheet_id
AND operation_id = p_service_package_operation_id )
)
)
LOOP
--
-- Search l_lines_rec.code_combination_id in the
-- p_account_position_set_tbl table.
--
FOR i IN 1..p_account_position_set_tbl.COUNT
LOOP
-- Process only account sets first. Using GOTO as PL/SQL lacks
-- CONTINUE statement.
IF p_account_position_set_tbl(i).account_or_position_type = 'P' THEN
GOTO end_account_loop ;
Insert_WS_Lines_Pvt
(
p_worksheet_id => l_new_worksheet_id,
p_account_line_id => l_lines_rec.account_line_id ,
p_freeze_flag => l_lines_rec.freeze_flag ,
p_view_line_flag => l_lines_rec.view_line_flag ,
p_last_update_date => g_current_date,
p_last_updated_by => g_current_user_id,
p_last_update_login => g_current_login_id,
p_created_by => g_current_user_id,
p_creation_date => g_current_date,
p_return_status => l_return_status
) ;
SELECT pos_lines.* ,
pos.position_id
FROM psb_ws_lines_positions pos_lines ,
psb_ws_position_lines pos
WHERE pos_lines.worksheet_id = p_worksheet_id
AND pos.position_line_id = pos_lines.position_line_id
AND (
l_service_package_count = 0
OR
pos_lines.position_line_id IN
(
SELECT accts.position_line_id
FROM psb_ws_account_lines accts
WHERE accts.position_line_id = pos_lines.position_line_id
AND accts.service_package_id IN
(
SELECT sp.service_package_id
FROM psb_ws_submit_service_packages sp
WHERE worksheet_id = p_worksheet_id
AND operation_id = p_service_package_operation_id
)
)
)
)
LOOP
--
-- Search l_lines_pos_rec.position_id in the p_account_position_set_tbl
-- table.
--
FOR i IN 1..p_account_position_set_tbl.COUNT
LOOP
-- Process only position sets now. Using GOTO as PL/SQL lacks
-- CONTINUE statement.
IF p_account_position_set_tbl(i).account_or_position_type = 'A' THEN
GOTO end_position_loop ;
SELECT lines.*
FROM psb_ws_lines lines,
psb_ws_account_lines accts
WHERE accts.position_line_id = l_lines_pos_rec.position_line_id
AND lines.worksheet_id = p_worksheet_id
AND lines.account_line_id = accts.account_line_id
)
LOOP
--
Insert_WS_Lines_Pvt
(
p_worksheet_id => l_new_worksheet_id,
p_account_line_id => l_lines_rec.account_line_id ,
p_freeze_flag => l_lines_rec.freeze_flag ,
p_view_line_flag => l_lines_rec.view_line_flag ,
p_last_update_date => g_current_date,
p_last_updated_by => g_current_user_id,
p_last_update_login => g_current_login_id,
p_created_by => g_current_user_id,
p_creation_date => g_current_date,
p_return_status => l_return_status
) ;
SELECT *
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id ;
SELECT '1' INTO l_tmp_char
FROM psb_budget_groups
WHERE budget_group_id = p_budget_group_id ;
SELECT name INTO l_main_budget_group_name
FROM psb_budget_groups
WHERE budget_group_id = l_main_budget_group_id ;
SELECT lines.*
FROM psb_ws_lines lines ,
psb_ws_account_lines accts
WHERE lines.worksheet_id = p_worksheet_id
AND lines.account_line_id = accts.account_line_id
/*For Bug No : 2236283 Start*/
/*
AND accts.budget_group_id IN
( SELECT budget_group_id
FROM psb_budget_groups
WHERE budget_group_type = 'R'
AND effective_start_date <= PSB_WS_Acct1.g_startdate_pp
AND ((effective_end_date IS NULL)
OR
(effective_end_date >= PSB_WS_Acct1.g_enddate_cy))
START WITH budget_group_id = p_budget_group_id
CONNECT BY PRIOR budget_group_id = parent_budget_group_id
)
*/
AND EXISTS
( SELECT 1
FROM psb_budget_groups
WHERE budget_group_type = 'R'
AND budget_group_id = accts.budget_group_id
AND effective_start_date <= PSB_WS_Acct1.g_startdate_pp
AND ((effective_end_date IS NULL)
OR
(effective_end_date >= PSB_WS_Acct1.g_enddate_cy))
START WITH budget_group_id = p_budget_group_id
CONNECT BY PRIOR budget_group_id = parent_budget_group_id
)
/*For Bug No : 2236283 End*/
)
LOOP
--
-- Put the CCID in the psb_ws_lines table for the new worksheet.
--
Insert_WS_Lines_Pvt
(
p_worksheet_id => l_new_worksheet_id,
p_account_line_id => l_lines_rec.account_line_id ,
p_freeze_flag => l_lines_rec.freeze_flag ,
p_view_line_flag => l_lines_rec.view_line_flag ,
p_last_update_date => g_current_date,
p_last_updated_by => g_current_user_id,
p_last_update_login => g_current_login_id,
p_created_by => g_current_user_id,
p_creation_date => g_current_date,
p_return_status => l_return_status
) ;
SELECT lines.*
FROM psb_ws_lines_positions lines ,
psb_ws_position_lines pos
WHERE lines.worksheet_id = p_worksheet_id
AND lines.position_line_id = pos.position_line_id
/*For Bug No : 2236283 Start*/
/*
AND lines.position_line_id IN
(
SELECT acct_lines.position_line_id
FROM psb_ws_account_lines acct_lines
WHERE acct_lines.budget_group_id IN
(
SELECT bg.budget_group_id
FROM psb_budget_groups bg
WHERE bg.budget_group_type = 'R'
AND effective_start_date <= PSB_WS_Acct1.g_startdate_pp
AND ((effective_end_date IS NULL)
OR
(effective_end_date >= PSB_WS_Acct1.g_enddate_cy )
)
START WITH bg.budget_group_id = p_budget_group_id
CONNECT BY PRIOR bg.budget_group_id =
bg.parent_budget_group_id
)
)
*/
AND EXISTS
(
SELECT 1
FROM psb_ws_account_lines acct_lines
WHERE acct_lines.position_line_id = lines.position_line_id
AND EXISTS
(
SELECT bg.budget_group_id
FROM psb_budget_groups bg
WHERE bg.budget_group_type = 'R'
AND budget_group_id = acct_lines.budget_group_id
AND effective_start_date <= PSB_WS_Acct1.g_startdate_pp
AND ((effective_end_date IS NULL)
OR
(effective_end_date >= PSB_WS_Acct1.g_enddate_cy )
)
START WITH bg.budget_group_id = p_budget_group_id
CONNECT BY PRIOR bg.budget_group_id =
bg.parent_budget_group_id
)
)
/*For Bug No : 2236283 End*/
)
LOOP
--
-- Put the position_line_id in the psb_ws_lines_positions table for
-- the new worksheet.
--
PSB_WS_Pos_Pvt.Create_Position_Matrix
(
p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_FALSE ,
p_validation_level => FND_API.G_VALID_LEVEL_NONE ,
p_return_status => l_return_status ,
p_msg_count => l_msg_count ,
p_msg_data => l_msg_data ,
--
p_worksheet_id => l_new_worksheet_id ,
p_position_line_id => l_lines_rec.position_line_id ,
p_freeze_flag => l_lines_rec.freeze_flag ,
p_view_line_flag => l_lines_rec.view_line_flag
) ;
SELECT *
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id ;
SELECT name INTO l_main_budget_group_name
FROM psb_budget_groups
WHERE budget_group_id = l_ws_row_type.budget_group_id ;
SELECT accts.*
FROM psb_ws_lines lines ,
psb_ws_account_lines accts
WHERE lines.worksheet_id = p_worksheet_id
AND lines.account_line_id = accts.account_line_id
AND accts.position_line_id IS NULL
)
LOOP
--
-- Create records in psb_ws_lines and psb_ws_account_lines for the
-- new worksheet.
--
PSB_WS_Ops_Pvt.Create_Local_Dist_Pvt
(
p_account_line_id => l_lines_accts_rec.account_line_id ,
p_new_worksheet_id => l_new_worksheet_id ,
p_new_position_line_id => NULL ,
/*For Bug No : 2440100 Start*/
p_return_status => l_return_status
) ;
SELECT positions.*
FROM psb_ws_lines_positions lines ,
psb_ws_position_lines positions
WHERE lines.worksheet_id = p_worksheet_id
AND lines.position_line_id = positions.position_line_id
)
LOOP
--
-- API creates records in psb_ws_lines_positions and
-- psb_ws_position_lines for the new worksheet.
--
PSB_WS_Pos_Pvt.Create_Position_Lines
(
p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_FALSE ,
p_commit => FND_API.G_FALSE ,
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_position_line_id => l_new_position_line_id ,
p_worksheet_id => l_new_worksheet_id ,
p_position_id => l_lines_pos_rec.position_id ,
p_budget_group_id => l_lines_pos_rec.budget_group_id ,
p_copy_of_position_line_id => l_lines_pos_rec.position_line_id
);
SELECT account_line_id
FROM psb_ws_account_lines
WHERE position_line_id = l_lines_pos_rec.position_line_id
)
LOOP
--
PSB_WS_Ops_Pvt.Create_Local_Dist_Pvt
(
p_account_line_id => l_accts_rec.account_line_id ,
p_new_worksheet_id => l_new_worksheet_id ,
p_new_position_line_id => l_new_position_line_id ,
p_return_status => l_return_status
) ;
UPDATE psb_ws_account_lines
SET position_line_id = l_new_position_line_id
WHERE position_line_id = l_lines_pos_rec.position_line_id
AND account_line_id IN
(
SELECT account_line_id
FROM psb_ws_lines
WHERE worksheet_id = l_new_worksheet_id
) ;
SELECT *
FROM psb_ws_fte_lines
WHERE position_line_id = l_lines_pos_rec.position_line_id
)
LOOP
--
-- Populate the l_period_fte_tbl ( used by PSB_WS_Acct1 API )
--
l_period_fte_tbl(1) := l_fte_rec.period1_fte ;
SELECT *
FROM psb_ws_element_lines
WHERE position_line_id = l_lines_pos_rec.position_line_id
)
LOOP
-- API to create new element lines in psb_ws_element_lines.
PSB_WS_Pos_Pvt.Create_Element_Lines
(
p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_FALSE ,
p_commit => FND_API.G_FALSE ,
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_element_line_id => l_new_element_line_id ,
p_position_line_id => l_new_position_line_id ,
p_budget_year_id => l_element_rec.budget_year_id ,
p_pay_element_id => l_element_rec.pay_element_id ,
p_currency_code => l_element_rec.currency_code ,
p_element_cost => l_element_rec.element_cost ,
p_element_set_id => l_element_rec.element_set_id ,
p_service_package_id => l_element_rec.service_package_id ,
p_stage_set_id => l_element_rec.stage_set_id ,
p_start_stage_seq => l_element_rec.start_stage_seq ,
p_current_stage_seq => l_element_rec.current_stage_seq,
p_end_stage_seq => nvl(l_element_rec.end_stage_seq, FND_API.G_MISS_NUM)
);
SELECT asgn.*
FROM psb_ws_position_lines pos ,
psb_position_assignments asgn
WHERE pos.position_line_id = l_lines_pos_rec.position_line_id
AND asgn.worksheet_id = l_ws_row_type.global_worksheet_id
AND asgn.position_id = pos.position_id
)
LOOP
--
-- API will create new position assignments.
--
PSB_Positions_Pvt.Modify_Assignment
(
p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_FALSE ,
p_commit => FND_API.G_FALSE ,
p_validation_level => FND_API.G_VALID_LEVEL_NONE ,
p_return_status => l_return_status ,
p_msg_count => l_msg_count ,
p_msg_data => l_msg_data ,
--
p_position_assignment_id => l_new_position_assignment_id ,
p_element_value_type => l_asgn_rec.element_value_type ,
p_data_extract_id => l_asgn_rec.data_extract_id ,
p_worksheet_id => l_new_worksheet_id ,
p_position_id => l_asgn_rec.position_id ,
p_assignment_type => l_asgn_rec.assignment_type ,
p_attribute_id => l_asgn_rec.attribute_id ,
p_attribute_value_id => l_asgn_rec.attribute_value_id ,
p_attribute_value => l_asgn_rec.attribute_value ,
p_pay_element_id => l_asgn_rec.pay_element_id ,
p_pay_element_option_id => l_asgn_rec.pay_element_option_id ,
p_effective_start_date => l_asgn_rec.effective_start_date ,
p_effective_end_date => l_asgn_rec.effective_end_date ,
p_element_value => l_asgn_rec.element_value ,
p_global_default_flag => l_asgn_rec.global_default_flag ,
p_assignment_default_rule_id =>
l_asgn_rec.assignment_default_rule_id ,
p_modify_flag => l_asgn_rec.modify_flag ,
p_rowid => l_rowid ,
p_currency_code => l_asgn_rec.currency_code ,
p_pay_basis => l_asgn_rec.pay_basis ,
p_employee_id => l_asgn_rec.employee_id ,
p_primary_employee_flag => l_asgn_rec.primary_employee_flag ,
p_mode => 'R'
) ;
SELECT account_line_id
FROM psb_ws_account_lines
WHERE account_line_id = NVL( c_copy_of_account_line_id , -99)
AND ROWNUM < 2 ;
SELECT position_line_id, position_id
FROM psb_ws_position_lines
WHERE position_line_id = NVL( c_copy_of_position_line_id , -99)
AND ROWNUM < 2 ;
SELECT budget_group_id ,
NVL( budget_by_position, 'N' ) ,
NVL( copy_of_worksheet_id, -99 ) ,
current_stage_seq
INTO
l_source_budget_group_id ,
l_source_budget_by_position ,
l_source_copy_of_worksheet_id ,
l_source_current_stage_seq
FROM psb_worksheets
WHERE worksheet_id = p_source_worksheet_id ;
SELECT budget_group_id ,
current_stage_seq ,
NVL( freeze_flag , 'N' ) ,
global_worksheet_id ,
global_worksheet_flag
INTO
l_target_budget_group_id ,
l_target_current_stage_seq ,
l_target_freeze_flag ,
l_global_worksheet_id ,
l_global_worksheet_flag
FROM psb_worksheets
WHERE worksheet_id = p_target_worksheet_id ;
SELECT accts.*
FROM psb_ws_lines lines ,
psb_ws_account_lines accts
WHERE lines.worksheet_id = p_source_worksheet_id
AND lines.account_line_id = accts.account_line_id
AND accts.position_line_id IS NULL
AND accts.end_stage_seq IS NULL
AND accts.template_id IS NULL
AND accts.balance_type = 'E'
)
LOOP
--
-- Finding p_target_worksheet_id in psb_ws_account_lines.
--
OPEN l_ws_account_lines_csr ( l_lines_accts_rec.copy_of_account_line_id ) ;
SELECT positions.*
FROM psb_ws_lines_positions lines ,
psb_ws_position_lines positions
WHERE lines.worksheet_id = p_source_worksheet_id
AND lines.position_line_id = positions.position_line_id
)
LOOP
--
-- Finding p_target_worksheet_id in psb_ws_position_lines.
--
OPEN l_ws_position_lines_csr( l_lines_pos_rec.copy_of_position_line_id );
SELECT account_line_id
FROM psb_ws_account_lines
WHERE position_line_id = l_lines_pos_rec.position_line_id
)
LOOP
--
PSB_WS_Ops_Pvt.Create_Local_Dist_Pvt
(
p_account_line_id => l_accts_rec.account_line_id ,
p_new_worksheet_id => p_target_worksheet_id ,
p_new_position_line_id => l_new_position_line_id ,
p_return_status => l_return_status
) ;
-- Update the old position_line_id with new position_line_id in
-- psb_ws_account_lines table.
--
UPDATE psb_ws_account_lines
SET position_line_id = l_new_position_line_id
WHERE position_line_id = l_lines_pos_rec.position_line_id
AND account_line_id IN
(
SELECT account_line_id
FROM psb_ws_lines
WHERE worksheet_id = p_target_worksheet_id
) ;
SELECT *
FROM psb_ws_fte_lines
WHERE position_line_id = l_lines_pos_rec.position_line_id
)
LOOP
--
-- Populate the l_period_amount_tbl ( used by PSB_WS_Acct1 API )
--
l_period_fte_tbl(1) := l_fte_rec.period1_fte ;
SELECT *
FROM psb_ws_element_lines
WHERE position_line_id = l_lines_pos_rec.position_line_id
)
LOOP
-- API to create new element lines in psb_ws_element_lines.
PSB_WS_Pos_Pvt.Create_Element_Lines
(
p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_FALSE ,
p_commit => FND_API.G_FALSE ,
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_element_line_id => l_new_element_line_id ,
p_position_line_id => l_new_position_line_id ,
p_budget_year_id => l_element_rec.budget_year_id ,
p_pay_element_id => l_element_rec.pay_element_id ,
p_currency_code => l_element_rec.currency_code ,
p_element_cost => l_element_rec.element_cost ,
p_element_set_id => l_element_rec.element_set_id ,
p_service_package_id => l_element_rec.service_package_id ,
p_stage_set_id => l_element_rec.stage_set_id ,
p_start_stage_seq => l_element_rec.start_stage_seq ,
p_current_stage_seq => l_element_rec.current_stage_seq,
p_end_stage_seq => NVL( l_element_rec.end_stage_seq ,
FND_API.G_MISS_NUM )
);
SELECT *
FROM psb_position_assignments
WHERE position_id = l_lines_pos_rec.position_id
AND worksheet_id = p_source_worksheet_id
)
LOOP
--
-- API will create a new position assignments.
--
PSB_Positions_Pvt.Modify_Assignment
(
p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_FALSE ,
p_commit => FND_API.G_FALSE ,
p_validation_level => FND_API.G_VALID_LEVEL_NONE ,
p_return_status => l_return_status ,
p_msg_count => l_msg_count ,
p_msg_data => l_msg_data ,
--
p_position_assignment_id => l_new_position_assignment_id ,
p_element_value_type => l_asgn_rec.element_value_type ,
p_data_extract_id => l_asgn_rec.data_extract_id ,
p_worksheet_id => l_global_worksheet_id ,
p_position_id => l_asgn_rec.position_id ,
p_assignment_type => l_asgn_rec.assignment_type ,
p_attribute_id => l_asgn_rec.attribute_id ,
p_attribute_value_id => l_asgn_rec.attribute_value_id ,
p_attribute_value => l_asgn_rec.attribute_value ,
p_pay_element_id => l_asgn_rec.pay_element_id ,
p_pay_element_option_id => l_asgn_rec.pay_element_option_id ,
p_effective_start_date => l_asgn_rec.effective_start_date ,
p_effective_end_date => l_asgn_rec.effective_end_date ,
p_element_value => l_asgn_rec.element_value ,
p_global_default_flag => l_asgn_rec.global_default_flag ,
p_assignment_default_rule_id =>
l_asgn_rec.assignment_default_rule_id ,
p_modify_flag => l_asgn_rec.modify_flag ,
p_rowid => l_rowid ,
p_currency_code => l_asgn_rec.currency_code ,
p_pay_basis => l_asgn_rec.pay_basis ,
p_employee_id => l_asgn_rec.employee_id ,
p_primary_employee_flag => l_asgn_rec.primary_employee_flag ,
p_mode => 'R'
) ;
-- Update the old position_line_id with new position_line_id in
-- psb_ws_account_lines table.
--
/*For Bug No : 2534088 Start*/
--commented the following code as the complete code to be implemented
--for inserting/updating the account lines for target worksheet
/*
UPDATE psb_ws_account_lines
SET position_line_id = l_target_position_line_id
WHERE position_line_id = l_lines_pos_rec.position_line_id
AND account_line_id IN
(
SELECT account_line_id
FROM psb_ws_lines
WHERE worksheet_id = p_target_worksheet_id
) ;
SELECT accts.*
FROM psb_ws_lines lines ,
psb_ws_account_lines accts
WHERE lines.worksheet_id = p_source_worksheet_id
AND lines.account_line_id = accts.account_line_id
AND accts.position_line_id = l_lines_pos_rec.position_line_id
AND accts.end_stage_seq IS NULL
AND accts.template_id IS NULL
)
LOOP
--
-- Finding p_target_worksheet_id in psb_ws_account_lines.
--
OPEN l_ws_account_lines_csr ( l_lines_accts_rec.copy_of_account_line_id ) ;
DELETE psb_ws_fte_lines
WHERE position_line_id = l_target_position_line_id ;
SELECT *
FROM psb_ws_fte_lines
WHERE position_line_id = l_lines_pos_rec.position_line_id
)
LOOP
--
-- Populate the l_period_amount_tbl ( used by PSB_WS_Acct1 API )
--
l_period_fte_tbl(1) := l_fte_rec.period1_fte ;
DELETE psb_ws_element_lines
WHERE position_line_id = l_target_position_line_id ;
SELECT *
FROM psb_ws_element_lines
WHERE position_line_id = l_lines_pos_rec.position_line_id
)
LOOP
-- API to create new element lines in psb_ws_element_lines.
PSB_WS_Pos_Pvt.Create_Element_Lines
(
p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_FALSE ,
p_commit => FND_API.G_FALSE ,
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_element_line_id => l_new_element_line_id ,
p_position_line_id => l_target_position_line_id ,
p_budget_year_id => l_element_rec.budget_year_id ,
p_pay_element_id => l_element_rec.pay_element_id ,
p_currency_code => l_element_rec.currency_code ,
p_element_cost => l_element_rec.element_cost ,
p_element_set_id => l_element_rec.element_set_id ,
p_service_package_id => l_element_rec.service_package_id ,
p_stage_set_id => l_element_rec.stage_set_id ,
p_start_stage_seq => l_element_rec.start_stage_seq ,
p_current_stage_seq => l_element_rec.current_stage_seq,
p_end_stage_seq => NVL( l_element_rec.end_stage_seq,
FND_API.G_MISS_NUM )
);
DELETE psb_position_assignments
WHERE position_id = l_target_position_id
AND worksheet_id = l_global_worksheet_id ;
SELECT *
FROM psb_position_assignments
WHERE position_id = l_lines_pos_rec.position_id
AND worksheet_id = p_source_worksheet_id
)
LOOP
--
-- API will create a new position assignments.
--
PSB_Positions_Pvt.Modify_Assignment
(
p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_FALSE ,
p_commit => FND_API.G_FALSE ,
p_validation_level => FND_API.G_VALID_LEVEL_NONE ,
p_return_status => l_return_status ,
p_msg_count => l_msg_count ,
p_msg_data => l_msg_data ,
--
p_position_assignment_id => l_new_position_assignment_id ,
p_element_value_type => l_asgn_rec.element_value_type ,
p_data_extract_id => l_asgn_rec.data_extract_id ,
p_worksheet_id => l_global_worksheet_id ,
p_position_id => l_asgn_rec.position_id ,
p_assignment_type => l_asgn_rec.assignment_type ,
p_attribute_id => l_asgn_rec.attribute_id ,
p_attribute_value_id => l_asgn_rec.attribute_value_id ,
p_attribute_value => l_asgn_rec.attribute_value ,
p_pay_element_id => l_asgn_rec.pay_element_id ,
p_pay_element_option_id => l_asgn_rec.pay_element_option_id ,
p_effective_start_date => l_asgn_rec.effective_start_date ,
p_effective_end_date => l_asgn_rec.effective_end_date ,
p_element_value => l_asgn_rec.element_value ,
p_global_default_flag => l_asgn_rec.global_default_flag ,
p_assignment_default_rule_id =>
l_asgn_rec.assignment_default_rule_id ,
p_modify_flag => l_asgn_rec.modify_flag ,
p_rowid => l_rowid ,
p_currency_code => l_asgn_rec.currency_code ,
p_pay_basis => l_asgn_rec.pay_basis ,
p_employee_id => l_asgn_rec.employee_id ,
p_primary_employee_flag => l_asgn_rec.primary_employee_flag ,
p_mode => 'R'
) ;
| PROCEDURE Delete_Worksheet |
+===========================================================================*/
--
-- The API This API deletes a local or global worksheet.
--
PROCEDURE Delete_Worksheet
(
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 psb_worksheets.worksheet_id%TYPE ,
p_keep_local_copy_flag IN VARCHAR2 := 'N'
)
IS
--
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Worksheet' ;
SAVEPOINT Delete_Worksheet ;
SELECT NVL( global_worksheet_flag, 'N') ,
NVL( local_copy_flag, 'N') ,
NVL( budget_by_position, 'N')
INTO
l_global_worksheet_flag ,
l_local_copy_flag ,
l_budget_by_position
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id ;
SELECT worksheet_id
FROM psb_worksheets
WHERE global_worksheet_id = p_worksheet_id
AND NVL( global_worksheet_flag, 'N' ) = 'N'
)
LOOP
--
PSB_Concurrency_Control_Pub.Enforce_Concurrency_Control
(
p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_FALSE ,
p_validation_level => FND_API.G_VALID_LEVEL_NONE ,
p_return_status => l_return_status ,
p_msg_count => l_msg_count ,
p_msg_data => l_msg_data ,
--
p_concurrency_class => 'MAINTENANCE' ,
p_concurrency_entity_name => 'WORKSHEET',
p_concurrency_entity_id => l_worksheet_rec.worksheet_id
);
SELECT worksheet_id, budget_by_position
FROM psb_worksheets
WHERE global_worksheet_id = p_worksheet_id
AND NVL( global_worksheet_flag, 'N' ) = 'N'
AND NVL( local_copy_flag, 'N' ) = 'N'
)
LOOP
--
Delete_Worksheet_Pvt
(
p_worksheet_id => l_worksheet_rec.worksheet_id ,
p_budget_by_position => l_worksheet_rec.budget_by_position ,
p_delete_lines_flag => 'N' ,
p_return_status => l_return_status
) ;
SELECT worksheet_id, budget_by_position
FROM psb_worksheets
WHERE global_worksheet_id = p_worksheet_id
AND NVL( global_worksheet_flag, 'N' ) = 'N'
AND NVL( local_copy_flag, 'N' ) = 'Y'
)
LOOP
--
IF p_keep_local_copy_flag = 'N' THEN
--
Delete_Worksheet_Pvt
(
p_worksheet_id => l_worksheet_rec.worksheet_id ,
p_budget_by_position => l_worksheet_rec.budget_by_position ,
p_delete_lines_flag => 'Y' ,
p_return_status => l_return_status
) ;
-- As global worksheet is being deleted, set global_worksheet_id
-- column to null in the local worksheet.
--
PSB_Worksheet_Pvt.Update_Worksheet
(
p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_FALSE ,
p_commit => FND_API.G_FALSE ,
p_validation_level => FND_API.G_VALID_LEVEL_NONE ,
p_return_status => l_return_status ,
p_msg_count => l_msg_count ,
p_msg_data => l_msg_data ,
--
p_worksheet_id => l_worksheet_rec.worksheet_id ,
p_global_worksheet_id => NULL
) ;
END LOOP ; -- Delete or update all child local worksheets.
Delete_Worksheet_Pvt
(
p_worksheet_id => p_worksheet_id ,
p_budget_by_position => l_budget_by_position ,
p_delete_lines_flag => 'Y' ,
p_return_status => l_return_status
) ;
Delete_Worksheet_Pvt
(
p_worksheet_id => p_worksheet_id ,
p_budget_by_position => l_budget_by_position ,
p_delete_lines_flag => 'Y' ,
p_return_status => l_return_status
) ;
SELECT worksheet_id
FROM psb_worksheets
WHERE copy_of_worksheet_id = l_worksheets_tab(i)
)
LOOP
-- Lock the current worksheet.
PSB_Concurrency_Control_Pub.Enforce_Concurrency_Control
(
p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_FALSE ,
p_validation_level => FND_API.G_VALID_LEVEL_NONE ,
p_return_status => l_return_status ,
p_msg_count => l_msg_count ,
p_msg_data => l_msg_data ,
--
p_concurrency_class => 'MAINTENANCE' ,
p_concurrency_entity_name => 'WORKSHEET',
p_concurrency_entity_id => l_local_ws_rec.worksheet_id
);
Delete_Worksheet_Pvt
(
p_worksheet_id => l_worksheets_tab(i) ,
p_budget_by_position => l_budget_by_position ,
p_delete_lines_flag => 'N' ,
p_return_status => l_return_status
) ;
SELECT worksheet_id
FROM psb_worksheets
WHERE copy_of_worksheet_id = l_worksheets_tab(i)
)
LOOP
Delete_Worksheet_Pvt
(
p_worksheet_id => l_local_ws_rec.worksheet_id ,
p_budget_by_position => l_budget_by_position ,
p_delete_lines_flag => 'Y' ,
p_return_status => l_return_status
) ;
END Delete_Worksheet ;
SELECT *
FROM psb_ws_lines
WHERE account_line_id = p_account_line_id ;
Insert_WS_Lines_Pvt
(
p_worksheet_id => l_current_worksheet_id ,
p_account_line_id => p_account_line_id ,
p_freeze_flag => l_ws_lines_row_type.freeze_flag ,
p_view_line_flag => l_ws_lines_row_type.view_line_flag ,
p_last_update_date => g_current_date,
p_last_updated_by => g_current_user_id,
p_last_update_login => g_current_login_id,
p_created_by => g_current_user_id,
p_creation_date => g_current_date,
p_return_status => l_return_status
) ;
Insert_WS_Lines_Pvt
(
p_worksheet_id => l_current_worksheet_id ,
p_account_line_id => p_account_line_id ,
p_freeze_flag => l_ws_lines_row_type.freeze_flag ,
p_view_line_flag => l_ws_lines_row_type.view_line_flag ,
p_last_update_date => g_current_date,
p_last_updated_by => g_current_user_id,
p_last_update_login => g_current_login_id,
p_created_by => g_current_user_id,
p_creation_date => g_current_date,
p_return_status => l_return_status
) ;
select b.account_line_id,
a.view_line_flag,
a.freeze_flag
from psb_ws_lines a,
psb_ws_account_lines b
where a.account_line_id = b.account_line_id
and a.worksheet_id = p_worksheet_id
and b.position_line_id = p_position_line_id;
SELECT *
FROM psb_ws_lines
WHERE account_line_id = p_account_line_id ;
Insert_WS_Lines_Pvt
(
p_worksheet_id => p_worksheet_tbl(i) ,
p_account_line_id => p_account_line_id ,
p_freeze_flag => l_ws_lines_row_type.freeze_flag ,
p_view_line_flag => l_ws_lines_row_type.view_line_flag ,
p_last_update_date => g_current_date,
p_last_updated_by => g_current_user_id,
p_last_update_login => g_current_login_id,
p_created_by => g_current_user_id,
p_creation_date => g_current_date,
p_return_status => l_return_status
) ;
SELECT *
FROM psb_ws_lines_positions
WHERE position_line_id = p_position_line_id ;
SELECT *
FROM psb_ws_lines_positions
WHERE position_line_id = p_position_line_id ;
SELECT NVL(worksheet_type, '9'),
-- bug start 3970347
-- get the global worksheet flag from psb_worksheet table
global_worksheet_flag
-- bug end 3970347
INTO l_worksheet_type,
-- bug start 3970347
l_global_worksheet_flag
-- bug end 3970347
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id;
SELECT NVL(freeze_flag, 'N') INTO l_parent_freeze_flag
FROM psb_worksheets
WHERE worksheet_id = l_parent_worksheet_id ;
FOR l_gl_cutoff_period_rec IN (SELECT gl_cutoff_period
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id)
LOOP
l_gl_cutoff_period := l_gl_cutoff_period_rec.gl_cutoff_period;
PSB_Worksheet_Pvt.Update_Worksheet
(
p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_NONE,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data ,
--
p_worksheet_id => p_worksheet_id ,
p_freeze_flag => p_freeze_flag ,
-- bug start 3970347
-- pass the gl cutoff period to the update_worksheet API
p_gl_cutoff_period => l_gl_cutoff_period
-- bug end 3970347
);
UPDATE psb_ws_lines
SET freeze_flag = p_freeze_flag
WHERE worksheet_id = p_worksheet_id;
SELECT NVL(budget_by_position, 'N') INTO l_budget_by_position
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id ;
UPDATE psb_ws_lines_positions
SET freeze_flag = p_freeze_flag
WHERE worksheet_id = p_worksheet_id;
SELECT NVL( budget_by_position, 'N') ,
/* bug start 3970347 */
-- get the global worksheet flag from psb_worksheets table
global_worksheet_flag
/* bug end 3970347 */
INTO l_budget_by_position ,
/* bug start 3970347 */
l_global_worksheet_flag
/* bug end 3970347 */
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id ;
SELECT stage_set_id ,
current_stage_seq
INTO
l_stage_set_id ,
l_current_stage_seq
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id ;
SELECT MIN (sequence_number) INTO l_target_stage_seq
FROM psb_budget_stages
WHERE budget_stage_set_id = l_stage_set_id
AND sequence_number > l_current_stage_seq
ORDER BY sequence_number ;
FOR l_gl_cutoff_period_rec IN (SELECT gl_cutoff_period
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id)
LOOP
l_gl_cutoff_period := l_gl_cutoff_period_rec.gl_cutoff_period;
PSB_Worksheet_Pvt.Update_Worksheet
(
p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_NONE,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data,
--
p_worksheet_id => p_worksheet_id,
p_current_stage_seq => l_target_stage_seq ,
-- bug start 3970347
p_gl_cutoff_period => l_gl_cutoff_period
-- bug end 3970347
) ;
SELECT COUNT(*) INTO l_service_package_count
FROM dual
WHERE EXISTS
( SELECT 1
FROM psb_ws_submit_service_packages
WHERE worksheet_id = p_worksheet_id
AND operation_id = p_operation_id ) ;
UPDATE psb_ws_account_lines
SET current_stage_seq = l_target_stage_seq
WHERE l_target_stage_seq > current_stage_seq
AND end_stage_seq is null
AND account_line_id IN
( SELECT account_line_id
FROM psb_ws_lines
WHERE worksheet_id = p_worksheet_id ) ;
UPDATE psb_ws_fte_lines
SET current_stage_seq = l_target_stage_seq
WHERE l_target_stage_seq > current_stage_seq
AND end_stage_seq is null
AND position_line_id IN
( SELECT position_line_id
FROM psb_ws_lines_positions
WHERE worksheet_id = p_worksheet_id ) ;
UPDATE psb_ws_element_lines
SET current_stage_seq = l_target_stage_seq
WHERE l_target_stage_seq > current_stage_seq
AND end_stage_seq is null
AND position_line_id IN
( SELECT position_line_id
FROM psb_ws_lines_positions
WHERE worksheet_id = p_worksheet_id ) ;
UPDATE psb_ws_account_lines
SET current_stage_seq = l_target_stage_seq
WHERE l_target_stage_seq > current_stage_seq
AND end_stage_seq is null
AND account_line_id IN
( SELECT account_line_id
FROM psb_ws_lines
WHERE worksheet_id = p_worksheet_id
)
AND service_package_id IN
( SELECT service_package_id
FROM psb_ws_submit_service_packages
WHERE worksheet_id = p_worksheet_id
AND operation_id = p_operation_id
) ;
UPDATE psb_ws_fte_lines
SET current_stage_seq = l_target_stage_seq
WHERE l_target_stage_seq > current_stage_seq
AND end_stage_seq is null
AND position_line_id IN
( SELECT position_line_id
FROM psb_ws_lines_positions
WHERE worksheet_id = p_worksheet_id
)
AND service_package_id IN
( SELECT service_package_id
FROM psb_ws_submit_service_packages
WHERE worksheet_id = p_worksheet_id
AND operation_id = p_operation_id
) ;
UPDATE psb_ws_element_lines
SET current_stage_seq = l_target_stage_seq
WHERE l_target_stage_seq > current_stage_seq
AND end_stage_seq is null
AND position_line_id IN
( SELECT position_line_id
FROM psb_ws_lines_positions
WHERE worksheet_id = p_worksheet_id
)
AND service_package_id IN
( SELECT service_package_id
FROM psb_ws_submit_service_packages
WHERE worksheet_id = p_worksheet_id
AND operation_id = p_operation_id
) ;
SELECT ws.budget_group_id ,
ws.global_worksheet_id ,
ws.global_worksheet_flag ,
bg.parent_budget_group_id
INTO
l_budget_group_id ,
l_global_worksheet_id ,
l_global_worksheet_flag ,
l_parent_budget_group_id
FROM psb_worksheets ws,
psb_budget_groups bg
WHERE ws.worksheet_id = p_worksheet_id
AND ws.budget_group_id = bg.budget_group_id ;
SELECT budget_group_id INTO l_global_budget_group_id
FROM psb_worksheets
WHERE worksheet_id = l_global_worksheet_id ;
SELECT worksheet_id INTO p_worksheet_id_OUT
FROM psb_worksheets
WHERE global_worksheet_id = l_global_worksheet_id
AND budget_group_id = l_parent_budget_group_id
AND worksheet_type = 'O' ;
SELECT DISTINCT child_worksheet_id
INTO p_worksheet_id_OUT
FROM psb_ws_distribution_details details, psb_ws_distributions distr
WHERE distr.worksheet_id = details.worksheet_id
-- Bug No 2297742 Start
-- AND distr.distribution_option_flag = 'W'
AND nvl(distr.distribution_option_flag, 'W') = 'W'
-- Bug No 2297742 End
AND global_worksheet_id = l_global_worksheet_id
AND child_budget_group_id = l_parent_budget_group_id;
p_worksheet_tbl.DELETE ;
p_worksheet_tbl.DELETE ;
SELECT budget_group_id ,
budget_calendar_id ,
global_worksheet_id ,
NVL( global_worksheet_flag , 'N' )
INTO
l_budget_group_id ,
l_budget_calendar_id ,
l_global_worksheet_id ,
l_global_worksheet_flag
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id ;
SELECT budget_group_id
FROM psb_budget_groups
WHERE budget_group_type = 'R'
AND effective_start_date <= PSB_WS_Acct1.g_startdate_pp
AND ((effective_end_date IS NULL)
OR
(effective_end_date >= PSB_WS_Acct1.g_enddate_cy))
START WITH budget_group_id = l_budget_group_id
CONNECT BY PRIOR budget_group_id = parent_budget_group_id
)
LOOP
--
-- The hierarchial query will also return the l_budget_group_id.
-- Do not consider it.
--
IF l_budget_group_rec.budget_group_id <> l_budget_group_id THEN
l_child_worksheet_id := NULL ;
SELECT worksheet_id INTO l_child_worksheet_id
FROM psb_worksheets
WHERE global_worksheet_id = l_global_worksheet_id
AND budget_group_id = l_budget_group_rec.budget_group_id
AND worksheet_type = 'O' ;
SELECT child_worksheet_id
INTO l_child_worksheet_id
FROM psb_ws_distribution_details details, psb_ws_distributions distr
WHERE distr.worksheet_id = details.worksheet_id
-- Bug No 2297742 Start
-- AND distr.distribution_option_flag = 'W'
AND nvl(distr.distribution_option_flag, 'W') = 'W'
-- Bug No 2297742 End
AND global_worksheet_id = l_global_worksheet_id
AND child_budget_group_id = l_budget_group_rec.budget_group_id
AND ROWNUM < 2 ;
| PROCEDURE Update_Worksheet |
+===========================================================================*/
--
-- The API takes 2 worksheets, source and target. It updates target worksheet
-- by adding new account or position lines if they are their in the source
-- worksheet and not in the target worksheet. It also updates the worksheet
-- submission related columns in the source worksheet.
--
PROCEDURE Update_Worksheet
(
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_source_worksheet_id IN psb_worksheets.worksheet_id%TYPE ,
p_target_worksheet_id IN psb_worksheets.worksheet_id%TYPE
)
IS
--
l_api_name CONSTANT VARCHAR2(30) := 'Update_Worksheet' ;
SAVEPOINT Update_Worksheet_Pvt ;
SELECT budget_group_id ,
global_worksheet_id ,
NVL( global_worksheet_flag , 'N' ) ,
NVL( local_copy_flag , 'N' ) ,
NVL( budget_by_position , 'N' ),
/* Bug No 2378285 Start */
gl_cutoff_period
/* Bug No 2378285 End */
INTO
l_source_budget_group_id ,
l_source_global_worksheet_id ,
l_source_global_worksheet_flag ,
l_source_local_copy_flag ,
l_source_budget_by_position,
/* Bug No 2378285 Start */
l_gl_cutoff_period
/* Bug No 2378285 End */
FROM psb_worksheets
WHERE worksheet_id = p_source_worksheet_id ;
PSB_Worksheet_Pvt.Update_Worksheet
(
p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_NONE,
p_return_status => l_return_status,
p_msg_count => l_msg_count,
p_msg_data => l_msg_data ,
--
p_worksheet_id => p_target_worksheet_id ,
p_gl_cutoff_period => l_gl_cutoff_period,
p_date_submitted => NULL ,
p_submitted_by => NULL
);
SELECT budget_group_id ,
global_worksheet_id ,
NVL( local_copy_flag , 'N' )
INTO
l_target_budget_group_id ,
l_target_global_worksheet_id ,
l_target_local_copy_flag
FROM psb_worksheets
WHERE worksheet_id = p_target_worksheet_id ;
SELECT budget_calendar_id INTO l_budget_calendar_id
FROM psb_worksheets
WHERE worksheet_id = l_source_global_worksheet_id ;
SELECT lines.account_line_id
FROM psb_ws_lines lines ,
psb_ws_account_lines accts
WHERE lines.worksheet_id = p_source_worksheet_id
AND lines.account_line_id = accts.account_line_id
/*For Bug No : 2236283 Start*/
/*
AND accts.budget_group_id IN
( SELECT budget_group_id
FROM psb_budget_groups
WHERE budget_group_type = 'R'
AND effective_start_date <= PSB_WS_Acct1.g_startdate_pp
AND ((effective_end_date IS NULL)
OR
(effective_end_date >= PSB_WS_Acct1.g_enddate_cy))
START WITH budget_group_id = l_target_budget_group_id
CONNECT BY PRIOR budget_group_id = parent_budget_group_id
)
*/
AND EXISTS
( SELECT 1
FROM psb_budget_groups
WHERE budget_group_type = 'R'
AND budget_group_id = accts.budget_group_id
AND effective_start_date <= PSB_WS_Acct1.g_startdate_pp
AND ((effective_end_date IS NULL)
OR
(effective_end_date >= PSB_WS_Acct1.g_enddate_cy))
START WITH budget_group_id = l_target_budget_group_id
CONNECT BY PRIOR budget_group_id = parent_budget_group_id
)
/*For Bug No : 2236283 End*/
MINUS
SELECT lines.account_line_id
FROM psb_ws_lines lines
WHERE worksheet_id = p_target_worksheet_id
)
LOOP
SELECT * INTO l_ws_lines_rec
FROM psb_ws_lines
WHERE worksheet_id = p_source_worksheet_id
AND account_line_id = l_account_line_id_rec.account_line_id ;
Insert_WS_Lines_Pvt
(
p_worksheet_id => p_target_worksheet_id,
p_account_line_id => l_ws_lines_rec.account_line_id ,
p_freeze_flag => l_ws_lines_rec.freeze_flag ,
p_view_line_flag => l_ws_lines_rec.view_line_flag ,
p_last_update_date => g_current_date,
p_last_updated_by => g_current_user_id,
p_last_update_login => g_current_login_id,
p_created_by => g_current_user_id,
p_creation_date => g_current_date,
p_return_status => l_return_status
) ;
SELECT lines.position_line_id
FROM psb_ws_lines_positions lines ,
psb_ws_position_lines pos
WHERE lines.worksheet_id = p_source_worksheet_id
AND lines.position_line_id = pos.position_line_id
/*For Bug No : 2236283 Start*/
/*
AND lines.position_line_id IN
(
SELECT acct_lines.position_line_id
FROM psb_ws_account_lines acct_lines
WHERE acct_lines.budget_group_id IN
(
SELECT bg.budget_group_id
FROM psb_budget_groups bg
WHERE bg.budget_group_type = 'R'
AND effective_start_date <= PSB_WS_Acct1.g_startdate_pp
AND ((effective_end_date IS NULL)
OR
(effective_end_date >= PSB_WS_Acct1.g_enddate_cy))
START WITH bg.budget_group_id = l_target_budget_group_id
CONNECT BY PRIOR bg.budget_group_id = bg.parent_budget_group_id
)
)
*/
AND EXISTS
(
SELECT 1
FROM psb_ws_account_lines acct_lines
WHERE acct_lines.position_line_id = lines.position_line_id
AND EXISTS
(
SELECT 1
FROM psb_budget_groups bg
WHERE bg.budget_group_type = 'R'
AND bg.budget_group_id = acct_lines.budget_group_id
AND effective_start_date <= PSB_WS_Acct1.g_startdate_pp
AND ((effective_end_date IS NULL)
OR
(effective_end_date >= PSB_WS_Acct1.g_enddate_cy))
START WITH bg.budget_group_id = l_target_budget_group_id
CONNECT BY PRIOR bg.budget_group_id = bg.parent_budget_group_id
)
)
/*For Bug No : 2236283 End*/
MINUS
SELECT position_line_id
FROM psb_ws_lines_positions
WHERE worksheet_id = p_target_worksheet_id
)
LOOP
SELECT * INTO l_ws_lines_positions_rec
FROM psb_ws_lines_positions
WHERE worksheet_id = p_source_worksheet_id
AND position_line_id = l_lines_pos_rec.position_line_id ;
ROLLBACK TO Update_Worksheet_Pvt ;
ROLLBACK TO Update_Worksheet_Pvt ;
ROLLBACK TO Update_Worksheet_Pvt ;
END Update_Worksheet ;
SELECT *
FROM psb_ws_account_lines
WHERE account_line_id = p_account_line_id
)
LOOP
--
-- Populate the l_period_amount_tbl ( used by PSB_WS_Acct1 API )
--
l_period_amount_tbl(1) := l_accts_rec.period1_amount ;
| PROCEDURE Insert_WS_Lines_Pvt ( Private ) |
+===========================================================================*/
--
-- The private procedure inserts a new record in psb_ws_lines table.
--
PROCEDURE Insert_WS_Lines_Pvt
(
p_worksheet_id IN psb_ws_lines.worksheet_id%TYPE,
p_account_line_id IN psb_ws_lines.account_line_id%TYPE,
p_freeze_flag IN psb_ws_lines.freeze_flag%TYPE,
p_view_line_flag IN psb_ws_lines.view_line_flag%TYPE,
p_last_update_date IN psb_ws_lines.last_update_date%TYPE,
p_last_updated_by IN psb_ws_lines.last_updated_by%TYPE,
p_last_update_login IN psb_ws_lines.last_update_login%TYPE,
p_created_by IN psb_ws_lines.created_by%TYPE,
p_creation_date IN psb_ws_lines.creation_date%TYPE,
p_return_status OUT NOCOPY VARCHAR2
)
IS
--
l_api_name CONSTANT VARCHAR2(30) := 'Insert_WS_Lines_Pvt' ;
update psb_ws_lines
set freeze_flag = p_freeze_flag,
view_line_flag = p_view_line_flag,
last_update_date = g_current_date,
last_updated_by = g_current_user_id,
last_update_login = g_current_login_id
where account_line_id = p_account_line_id
and worksheet_id = p_worksheet_id;
INSERT INTO psb_ws_lines
(
worksheet_id,
account_line_id,
freeze_flag,
view_line_flag,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date
)
VALUES
( p_worksheet_id,
p_account_line_id,
p_freeze_flag,
p_view_line_flag,
g_current_date,
g_current_user_id,
g_current_login_id,
g_current_user_id,
g_current_date
);
END Insert_WS_Lines_Pvt ;
| PROCEDURE Delete_Worksheet_Pvt ( Private ) |
+===========================================================================*/
--
-- This API deletes an official worksheet by performing deletes on
-- psb_worksheets and matrix tables (psb_ws_lines and psb_ws_lines_positions).
-- It also deletes worksheet related data from other tables.
--
PROCEDURE Delete_Worksheet_Pvt
(
p_worksheet_id IN psb_worksheets.worksheet_id%TYPE ,
p_budget_by_position IN psb_worksheets.budget_by_position%TYPE ,
p_delete_lines_flag IN VARCHAR2 ,
p_return_status OUT NOCOPY VARCHAR2
)
IS
--
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Worksheet_Pvt' ;
SELECT account_line_id
FROM psb_ws_lines
WHERE worksheet_id = p_worksheet_id;
SELECT position_line_id
FROM psb_ws_lines_positions
WHERE worksheet_id = p_worksheet_id;
SAVEPOINT Delete_Worksheet_Pvt ;
IF p_delete_lines_flag = 'Y' THEN
--
-- Deleting account related information.
--
OPEN l_ws_account_lines_csr;
DELETE psb_ws_account_lines
WHERE account_line_id = l_account_line_id;
SAVEPOINT Delete_Worksheet_Pvt;
DELETE psb_ws_position_lines
WHERE position_line_id = l_position_line_id;
DELETE psb_ws_fte_lines
WHERE position_line_id = l_position_line_id;
DELETE psb_ws_element_lines
WHERE position_line_id = l_position_line_id;
SAVEPOINT Delete_Worksheet_Pvt;
END IF; -- if p_delete_lines_flag is 'Y'.
SAVEPOINT Delete_Worksheet_Pvt;
DELETE psb_ws_lines
WHERE worksheet_id = p_worksheet_id ;
SAVEPOINT Delete_Worksheet_Pvt;
DELETE psb_ws_lines_positions
WHERE worksheet_id = p_worksheet_id ;
SAVEPOINT Delete_Worksheet_Pvt;
DELETE psb_ws_distribution_details
WHERE child_worksheet_id = p_worksheet_id ;
DELETE psb_workflow_processes
WHERE worksheet_id = p_worksheet_id ;
DELETE psb_ws_distributions
WHERE worksheet_id = p_worksheet_id ;
DELETE psb_ws_submit_service_packages
WHERE worksheet_id = p_worksheet_id ;
DELETE psb_ws_user_profiles
WHERE worksheet_id = p_worksheet_id ;
DELETE psb_position_assignments
WHERE worksheet_id = p_worksheet_id ;
DELETE psb_pay_element_rates
WHERE worksheet_id = p_worksheet_id ;
DELETE psb_ws_submit_comments
WHERE worksheet_id = p_worksheet_id ;
DELETE psb_worksheets
WHERE worksheet_id = p_worksheet_id ;
fnd_attached_documents2_pkg.delete_attachments
(X_entity_name => 'PSB_WORKSHEETS',
X_pk1_value => p_worksheet_id,
X_delete_document_flag => 'Y'
);
ROLLBACK TO Delete_Worksheet_Pvt ;
END Delete_Worksheet_Pvt ;
| PROCEDURE Delete_Worksheet_CP |
+===========================================================================*/
--
-- This is the execution file for the concurrent program 'Maintain Budget
-- Account Codes'.
--
PROCEDURE Delete_Worksheet_CP
(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
--
p_worksheet_id IN NUMBER ,
p_keep_local_copy_flag IN VARCHAR2
)
IS
--
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Worksheet_CP' ;
SAVEPOINT Delete_Worksheet_CP_Pvt ;
PSB_WS_Ops_Pvt.Delete_Worksheet
(
p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_TRUE,
/*For Bug No : 2266309 Start*/
--p_commit => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
/*For Bug No : 2266309 End*/
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_worksheet_id => p_worksheet_id,
p_keep_local_copy_flag => p_keep_local_copy_flag
);
END Delete_Worksheet_CP ;
SELECT *
FROM psb_worksheets
WHERE worksheet_id = p_worksheet_id ;
SELECT name INTO l_main_budget_group_name
FROM psb_budget_groups
WHERE budget_group_id = l_main_budget_group_id ;
SELECT count(*) INTO l_service_package_count
FROM psb_ws_submit_service_packages
WHERE worksheet_id = p_worksheet_id
AND operation_id = NVL( p_service_package_operation_id ,
FND_API.G_MISS_NUM ) ;
SELECT pos_lines.* ,
pos.position_id
FROM psb_ws_lines_positions pos_lines ,
psb_ws_position_lines pos ,
psb_positions positions
WHERE pos_lines.worksheet_id = p_worksheet_id
AND pos.position_line_id = pos_lines.position_line_id
AND positions.position_id = pos.position_id
--AND positions.hr_position_id IS NULL
AND positions.new_position_flag = 'Y'
AND (
l_service_package_count = 0
OR
pos_lines.position_line_id IN
(
SELECT accts.position_line_id
FROM psb_ws_account_lines accts
WHERE accts.position_line_id = pos_lines.position_line_id
AND accts.service_package_id IN
(
SELECT sp.service_package_id
FROM psb_ws_submit_service_packages sp
WHERE worksheet_id = p_worksheet_id
AND operation_id = p_service_package_operation_id
)
)
)
)
LOOP
-- At least one line should get created for the worksheet.
l_lines_added := l_lines_added + 1 ;
SELECT lines.*
FROM psb_ws_lines lines,
psb_ws_account_lines accts
WHERE accts.position_line_id = l_lines_pos_rec.position_line_id
AND lines.worksheet_id = p_worksheet_id
AND lines.account_line_id = accts.account_line_id
)
LOOP
--
Insert_WS_Lines_Pvt
(
p_worksheet_id => l_new_worksheet_id,
p_account_line_id => l_lines_rec.account_line_id ,
p_freeze_flag => l_lines_rec.freeze_flag ,
p_view_line_flag => l_lines_rec.view_line_flag ,
p_last_update_date => g_current_date,
p_last_updated_by => g_current_user_id,
p_last_update_login => g_current_login_id,
p_created_by => g_current_user_id,
p_creation_date => g_current_date,
p_return_status => l_return_status
) ;