[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT worksheet_id INTO l_worksheet_id
FROM psb_workflow_processes
WHERE item_key = p_item_key ;
SELECT comments INTO l_comments
FROM psb_ws_submit_comments
WHERE operation_id = p_operation_id;
SELECT name ,
budget_group_name
INTO
l_worksheet_name ,
l_budget_group_name
FROM psb_worksheets_v
WHERE worksheet_id = l_worksheet_id;
| PROCEDURE Select_Operation |
+===========================================================================*/
--
-- The API selects the operation to be performed on the worksheet. The
-- appropriate branch on the process is selected accordingly.
--
PROCEDURE Select_Operation
(
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
result OUT NOCOPY VARCHAR2
)
IS
--
l_worksheet_id psb_worksheets.worksheet_id%TYPE ;
wf_core.context('PSBWS', 'Select_Operation',
itemtype, itemkey, to_char(actid), funcmode);
END Select_Operation ;
SELECT budget_group_name INTO g_worksheet_name
FROM psb_worksheets_v
WHERE worksheet_id = l_worksheet_id ;
SELECT NVL(freeze_flag, 'N') INTO l_current_freeze_flag
FROM psb_worksheets
WHERE worksheet_id = l_worksheets_tab(i) ;
SELECT budget_group_id ,
budget_group_name ,
name
INTO
l_budget_group_id ,
g_budget_group_name ,
g_worksheet_name
FROM psb_worksheets_v
WHERE worksheet_id = l_worksheets_tab(i) ;
SELECT wf_role_name
FROM psb_budget_groups bg ,
psb_budget_group_resp resp
WHERE resp.responsibility_type = 'N'
AND bg.budget_group_id = l_budget_group_id
AND bg.budget_group_id = resp.budget_group_id
)
LOOP
--
l_notification_id :=
WF_Notification.SendGroup
( role => l_role_rec.wf_role_name ,
msg_type => 'PSBWS' ,
msg_name => 'NOTIFY_OF_FREEZE_COMPLETION' ,
context => itemtype ||':'|| itemkey ||':'|| actid ,
callback => 'PSB_Submit_Worksheet_PVT.Callback'
) ;
| PROCEDURE Update_View_Line_Flag |
+===========================================================================*/
--
-- API updates view_line flag for all parent worksheets of the submittted
-- worksheet as per the service package selection.
--
PROCEDURE Update_View_Line_Flag
(
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
result OUT NOCOPY VARCHAR2
)
IS
--
l_return_status VARCHAR2(1) ;
SELECT NVL( budget_by_position, 'N') INTO l_budget_by_position
FROM psb_worksheets
WHERE worksheet_id = l_worksheet_id ;
SELECT COUNT(*) INTO l_service_package_count
FROM dual
WHERE EXISTS
( SELECT 1
FROM psb_ws_submit_service_packages
WHERE worksheet_id = l_worksheet_id
AND operation_id = l_operation_id ) ;
UPDATE psb_ws_lines lines
SET lines.view_line_flag =
( SELECT DECODE( COUNT(*), 0, 'N', 'Y' )
FROM psb_ws_account_lines accts
WHERE accts.account_line_id = lines.account_line_id
AND ( l_service_package_count = 0
OR
accts.service_package_id IN
(
SELECT ssp.service_package_id
FROM psb_ws_submit_service_packages ssp
WHERE ssp.worksheet_id = l_worksheet_id
AND ssp.operation_id = l_operation_id
)
)
)
WHERE lines.worksheet_id = l_worksheets_tab(i)
AND EXISTS
( SELECT 1
FROM psb_ws_lines pwl
WHERE pwl.account_line_id = lines.account_line_id
AND pwl.worksheet_id = l_worksheet_id
) ;
UPDATE psb_ws_lines lines
SET lines.view_line_flag = 'N'
WHERE lines.worksheet_id = l_worksheets_tab(i)
AND EXISTS
(
SELECT accts.account_line_id
FROM psb_ws_account_lines accts
WHERE accts.account_line_id = lines.account_line_id
AND accts.service_package_id NOT IN
(
SELECT service_package_id
FROM psb_ws_submit_service_packages
WHERE worksheet_id = l_worksheet_id
AND operation_id = l_operation_id
)
) ;
UPDATE psb_ws_lines_positions lines
SET lines.view_line_flag = 'Y'
WHERE lines.worksheet_id = l_worksheets_tab(i)
AND ( lines.view_line_flag IS NULL OR lines.view_line_flag = 'N' )
AND EXISTS
( SELECT 1
FROM psb_ws_lines_positions pwl
WHERE pwl.position_line_id = lines.position_line_id
AND pwl.worksheet_id = l_worksheet_id
) ;
UPDATE psb_ws_lines_positions lines
SET lines.view_line_flag =
( DECODE ( ( SELECT COUNT(*)
FROM psb_ws_account_lines accts
WHERE accts.position_line_id = lines.position_line_id
AND ( l_service_package_count = 0
OR
accts.service_package_id IN
(
SELECT ssp.service_package_id
FROM psb_ws_submit_service_packages ssp
WHERE ssp.worksheet_id = l_worksheet_id
AND ssp.operation_id = l_operation_id
)
)
),
0, 'N', 'Y'
)
)
WHERE lines.worksheet_id = l_worksheets_tab(i)
AND EXISTS
( SELECT 1
FROM psb_ws_lines_positions pwl
WHERE pwl.position_line_id = lines.position_line_id
AND pwl.worksheet_id = l_worksheet_id
) ;
UPDATE psb_ws_lines_positions lines
SET view_line_flag = 'N'
WHERE lines.worksheet_id = l_worksheets_tab(i)
AND lines.position_line_id IN
(
SELECT accts.position_line_id
FROM psb_ws_lines lines ,
psb_ws_account_lines accts
WHERE lines.worksheet_id = l_worksheets_tab(i)
AND lines.account_line_id = accts.account_line_id
AND accts.service_package_id NOT IN
(
SELECT ssp.service_package_id
FROM psb_ws_submit_service_packages ssp
WHERE ssp.worksheet_id = l_worksheet_id
AND ssp.operation_id = l_operation_id
)
) ;
wf_core.context('PSBWS', 'Update_View_Line_Flag',
PSB_Message_S.Get_Error_Stack(l_msg_count) );
END Update_View_Line_Flag;
SELECT stage_set_id ,
current_stage_seq
INTO
l_stage_set_id ,
l_current_stage_seq
FROM psb_worksheets
WHERE worksheet_id = l_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 ;
SELECT current_stage_seq INTO l_current_stage_seq
FROM psb_worksheets
WHERE worksheet_id = l_worksheets_tab(i) ;
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_worksheets_tab(i) ,
p_current_stage_seq => l_target_stage_seq
) ;
SELECT budget_group_id ,
name
INTO
l_budget_group_id ,
g_worksheet_name
FROM psb_worksheets
WHERE worksheet_id = l_worksheets_tab(i) ;
SELECT wf_role_name
FROM psb_budget_groups bg ,
psb_budget_group_resp resp
WHERE resp.responsibility_type = 'N'
AND bg.budget_group_id = l_budget_group_id
AND bg.budget_group_id = resp.budget_group_id
)
LOOP
--
l_notification_id :=
WF_Notification.SendGroup
( role => l_role_rec.wf_role_name ,
msg_type => 'PSBWS' ,
msg_name => 'NOTIFY_OF_WS_MOVE_COMPLETION' ,
context => itemtype ||':'|| itemkey ||':'|| actid ,
callback => 'PSB_Submit_Worksheet_PVT.Callback'
) ;
SELECT ws.budget_group_id ,
ws.budget_calendar_id ,
bg.root_budget_group_id
INTO
l_budget_group_id ,
l_budget_calendar_id ,
l_root_budget_group_id
FROM psb_worksheets ws ,
psb_budget_groups bg
WHERE worksheet_id = l_worksheet_id
AND ws.budget_group_id = bg.budget_group_id ;
INSERT INTO psb_wf_review_groups
( item_key, budget_workflow_rule_id, sequence )
SELECT itemkey, rules.budget_workflow_rule_id, ROWNUM
FROM psb_budget_group_categories cats ,
psb_budget_workflow_rules rules ,
psb_budget_groups bg
WHERE cats.budget_group_id = l_budget_group_id
AND rules.budget_group_id = l_root_budget_group_id
AND rules.stage_id = cats.stage_id
AND bg.budget_group_id = rules.review_budget_group_id
AND bg.effective_start_date <= PSB_WS_Acct1.g_startdate_pp
AND ( ( bg.effective_end_date IS NULL)
OR
( bg.effective_end_date >= PSB_WS_Acct1.g_enddate_cy )
) ;
SELECT budget_group_id INTO l_budget_group_id
FROM psb_worksheets
WHERE worksheet_id = l_worksheet_id ;
SELECT wrg.budget_workflow_rule_id ,
rules.approval_option ,
rules.review_budget_group_id
INTO
l_budget_workflow_rule_id ,
l_approval_option ,
l_review_budget_group_id
FROM psb_wf_review_groups wrg ,
psb_budget_workflow_rules rules
WHERE item_key = itemkey
AND sequence = l_loop_visited_counter
AND rules.budget_workflow_rule_id = wrg.budget_workflow_rule_id ;
SELECT name INTO l_review_budget_group_name
FROM psb_budget_groups
WHERE budget_group_id = l_review_budget_group_id ;
SELECT account_or_position_type INTO l_account_or_position_type
FROM psb_budget_workflow_rules
WHERE budget_workflow_rule_id = l_budget_workflow_rule_id ;
l_set_tbl.DELETE ;
SELECT account_position_set_id,
account_or_position_type
FROM psb_budget_workflow_rules rules ,
psb_set_relations relations
WHERE rules.budget_workflow_rule_id = l_budget_workflow_rule_id
AND relations.budget_workflow_rule_id = rules.budget_workflow_rule_id
)
LOOP
l_count := l_count + 1;
SELECT name INTO l_new_worksheet_name
FROM psb_worksheets
WHERE worksheet_id = l_new_worksheet_id ;
SELECT wf_role_name INTO l_review_group_approver_name
FROM psb_budget_groups bg ,
psb_budget_group_resp resp
WHERE resp.responsibility_type = 'N'
AND bg.budget_group_id = l_review_budget_group_id
AND bg.budget_group_id = resp.budget_group_id
AND ROWNUM < 2 ;
| PROCEDURE Update_Worksheets_Status |
+===========================================================================*/
--
-- The API updates submission related information in the submitted worksheet
-- and all its lower worksheets.
--
PROCEDURE Update_Worksheets_Status
(
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
result OUT NOCOPY VARCHAR2
)
IS
--
l_return_status VARCHAR2(1) ;
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_id ,
p_date_submitted => SYSDATE ,
p_submitted_by => l_submitter_id
);
wf_core.context('PSBWS', 'Update_Worksheets_Status',
PSB_Message_S.Get_Error_Stack(l_msg_count) );
END Update_Worksheets_Status ;
| PROCEDURE Select_Approvers |
+===========================================================================*/
--
-- The API finds Approvers for the worksheet and then sends notifications
-- to them.
--
PROCEDURE Select_Approvers
(
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
result OUT NOCOPY VARCHAR2
)
IS
--
l_worksheet_id psb_worksheets.worksheet_id%TYPE ;
SELECT bg.parent_budget_group_id
INTO
l_parent_budget_group_id
FROM psb_worksheets ws,
psb_budget_groups bg
WHERE ws.worksheet_id = l_worksheet_id
AND ws.budget_group_id = bg.budget_group_id ;
SELECT wf_role_name
FROM psb_budget_groups bg ,
psb_budget_group_resp resp
WHERE bg.budget_group_id = l_parent_budget_group_id
AND resp.responsibility_type = 'N'
AND bg.budget_group_id = resp.budget_group_id
)
LOOP
--
l_notification_group_id :=
WF_Notification.SendGroup
(
role => l_role_rec.wf_role_name ,
msg_type => 'PSBWS' ,
msg_name => 'NOTIFY_APPROVERS_OF_SUBMISSION' ,
context => itemtype ||':'|| itemkey || ':'|| actid ,
callback => 'PSB_Submit_Worksheet_PVT.Callback'
) ;
wf_core.context('PSBWS', 'Select_Approvers',
itemtype, itemkey, to_char(actid), funcmode);
END Select_Approvers ;
SELECT ws.budget_group_id ,
ws.budget_calendar_id ,
bg.root_budget_group_id
INTO
l_budget_group_id ,
l_budget_calendar_id ,
l_root_budget_group_id
FROM psb_worksheets ws ,
psb_budget_groups bg
WHERE worksheet_id = p_worksheet_id
AND ws.budget_group_id = bg.budget_group_id ;
SELECT 'Exists' INTO l_exists FROM dual
WHERE EXISTS
(SELECT 1
FROM psb_budget_group_categories cats,
psb_budget_workflow_rules rules,
psb_budget_groups bg,
psb_set_relations relations,
psb_budget_accounts ba
WHERE cats.budget_group_id = l_budget_group_id
AND rules.budget_group_id = l_root_budget_group_id
AND bg.budget_group_id = rules.review_budget_group_id
AND bg.effective_start_date <= PSB_WS_Acct1.g_startdate_pp
AND (( bg.effective_end_date IS NULL)
OR( bg.effective_end_date >= PSB_WS_Acct1.g_enddate_cy ))
AND rules.stage_id = cats.stage_id
AND relations.budget_workflow_rule_id = rules.budget_workflow_rule_id
AND relations.account_position_set_id = ba.account_position_set_id
AND EXISTS
(
SELECT 1
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.code_combination_id = ba.code_combination_id
AND ROWNUM < 2
));
SELECT 'Exists' INTO l_exists FROM dual
WHERE EXISTS
(SELECT 1
FROM psb_budget_group_categories cats,
psb_budget_workflow_rules rules,
psb_budget_groups bg,
psb_set_relations relations,
psb_budget_positions bp
WHERE cats.budget_group_id = l_budget_group_id
AND rules.budget_group_id = l_root_budget_group_id
AND bg.budget_group_id = rules.review_budget_group_id
AND bg.effective_start_date <= PSB_WS_Acct1.g_startdate_pp
AND (( bg.effective_end_date IS NULL)
OR( bg.effective_end_date >= PSB_WS_Acct1.g_enddate_cy ))
AND rules.stage_id = cats.stage_id
AND relations.budget_workflow_rule_id = rules.budget_workflow_rule_id
AND relations.account_position_set_id = bp.account_position_set_id
AND EXISTS
(
SELECT 1
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
AND pos.position_id = bp.position_id
AND ROWNUM < 2
));
SELECT 'Exists' INTO l_exists
FROM DUAL
WHERE EXISTS
(SELECT 1
FROM PSB_BUDGET_GROUP_CATEGORIES cats,
PSB_BUDGET_WORKFLOW_RULES rules,
PSB_BUDGET_GROUPS bgrp
WHERE cats.budget_group_id = l_budget_group_id
AND rules.stage_id = cats.stage_id
AND rules.budget_group_id = l_root_budget_group_id
AND bgrp.budget_group_id = rules.review_budget_group_id
AND bgrp.effective_start_date <= PSB_WS_Acct1.g_startdate_pp
AND (( bgrp.effective_end_date IS NULL )
OR( bgrp.effective_end_date >= PSB_WS_Acct1.g_enddate_cy ))
AND EXISTS
(SELECT 1
FROM PSB_POSITIONS ppos,
PSB_WS_LINES_POSITIONS lines,
PSB_WS_POSITION_LINES wspos
WHERE ppos.position_id = wspos.position_id
AND ppos.new_position_flag = 'Y'
AND lines.worksheet_id = p_worksheet_id
AND wspos.position_line_id = lines.position_line_id
)
);