[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM pqh_worksheet_details
WHERE worksheet_id = p_worksheet_id
and parent_worksheet_detail_id is null;
pqh_worksheet_details_api.update_worksheet_detail(
p_validate => false
,p_worksheet_detail_id => l_wkd_rec.worksheet_detail_id
,p_object_version_number => l_wkd_ovn
,p_status => p_status
,p_effective_date => sysdate
);
SELECT *
FROM pqh_worksheets
WHERE worksheet_id = p_worksheet_id;
edit_update_budget
(
p_worksheet_id => p_worksheet_id
);
hr_utility.set_location('worksheet updated with Applied'||l_proc, 8);
hr_utility.set_location('wkd updated with Applied'||l_proc, 9);
SELECT *
FROM pqh_worksheets
WHERE worksheet_id = p_worksheet_id;
SELECT *
FROM pqh_worksheet_details
WHERE worksheet_id = p_worksheet_id
AND NVL(action_cd,'X') = 'B' ;
SELECT *
FROM pqh_worksheet_periods
WHERE worksheet_detail_id = p_worksheet_detail_id;
SELECT *
FROM pqh_worksheet_budget_sets
WHERE worksheet_period_id = p_worksheet_period_id;
SELECT *
FROM pqh_worksheet_bdgt_elmnts
WHERE worksheet_budget_set_id = p_worksheet_budget_set_id;
SELECT *
FROM pqh_worksheet_fund_srcs
WHERE worksheet_bdgt_elmnt_id = p_worksheet_bdgt_elmnt_id;
SELECT bvr.budget_version_id
FROM pqh_budget_versions bvr, pqh_worksheets wks
WHERE bvr.budget_id = wks.budget_id
AND bvr.version_number = wks.version_number
AND wks.worksheet_id = p_worksheet_id;
hr_utility.set_location('Called Apply Budget in Update Mode: '||p_mode, 6);
PROCEDURE edit_update_budget
(
p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE
)
IS
-- local variables and cursors
l_proc varchar2(72) := g_package||'edit_update_budget';
delete_child_rows
we pick budget_detail_id from pqh_worksheet_details table for this worksheet_id
and delete only those rows from pqh_budget_periods table onwards for the above
fetched budget_detail_id from pqh_worksheet_details table.
*/
delete_child_rows
(
p_worksheet_id => p_worksheet_id
);
SELECT NVL(max(version_number),0)
FROM pqh_budget_versions
WHERE budget_id = p_budget_id;
SELECT *
FROM pqh_budget_versions
WHERE budget_id = p_budget_id
AND version_number = p_curr_version_number;
SELECT sum(nvl(BUDGET_UNIT1_VALUE,0)) ,
sum(nvl(BUDGET_UNIT2_VALUE,0)) ,
sum(nvl(BUDGET_UNIT3_VALUE,0))
FROM pqh_worksheet_details
WHERE worksheet_id = p_worksheets_rec.worksheet_id
AND nvl(action_cd,'X') = 'B' ;
pqh_budget_versions_api.update_budget_version
(
p_validate => false
,p_budget_version_id => l_budget_versions_rec.budget_version_id
,p_budget_id => l_budget_versions_rec.budget_id
,p_version_number => l_budget_versions_rec.version_number
,p_date_from => p_worksheets_rec.date_from
,p_date_to => p_worksheets_rec.date_to
,p_transfered_to_gl_flag => l_budget_versions_rec.transfered_to_gl_flag
,p_xfer_to_other_apps_cd => l_budget_versions_rec.xfer_to_other_apps_cd
,p_object_version_number => l_object_version_number
,p_budget_unit1_value => l_budget_unit1_value
,p_budget_unit2_value => l_budget_unit2_value
,p_budget_unit3_value => l_budget_unit3_value
,p_budget_unit1_available => l_budget_unit1_available
,p_budget_unit2_available => l_budget_unit2_available
,p_budget_unit3_available => l_budget_unit3_available
,p_effective_date => sysdate
);
pqh_budget_versions_api.update_budget_version
(
p_validate => false
,p_budget_version_id => l_budget_versions_rec.budget_version_id
,p_budget_id => l_budget_versions_rec.budget_id
,p_version_number => l_budget_versions_rec.version_number
,p_date_from => p_worksheets_rec.date_from
,p_date_to => p_worksheets_rec.date_to
,p_transfered_to_gl_flag => l_budget_versions_rec.transfered_to_gl_flag
,p_xfer_to_other_apps_cd => l_budget_versions_rec.xfer_to_other_apps_cd
,p_object_version_number => l_object_version_number
,p_budget_unit1_value => l_budget_unit1_value
,p_budget_unit2_value => l_budget_unit2_value
,p_budget_unit3_value => l_budget_unit3_value
,p_budget_unit1_available => l_budget_unit1_available
,p_budget_unit2_available => l_budget_unit2_available
,p_budget_unit3_available => l_budget_unit3_available
,p_effective_date => sysdate
);
pqh_process_batch_log.insert_log
(
p_message_type_cd => 'ERROR',
p_message_text => SQLERRM
);
SELECT object_version_number
FROM pqh_budget_details
WHERE budget_detail_id = p_worksheet_details_rec.budget_detail_id;
SELECT sum(nvl(BUDGET_UNIT1_VALUE,0)) ,
sum(nvl(BUDGET_UNIT2_VALUE,0)) ,
sum(nvl(BUDGET_UNIT3_VALUE,0))
FROM pqh_worksheet_details
WHERE worksheet_id = p_worksheet_id
AND nvl(action_cd,'X') = 'B' ;
hr_utility.set_location('Update API OVN : '||l_object_version_number, 8);
pqh_budget_details_api.update_budget_detail
(
p_validate => false
,p_budget_detail_id => p_worksheet_details_rec.budget_detail_id
,p_organization_id => p_worksheet_details_rec.organization_id
,p_job_id => p_worksheet_details_rec.job_id
,p_position_id => p_worksheet_details_rec.position_id
,p_grade_id => p_worksheet_details_rec.grade_id
,p_budget_version_id => p_budget_version_id
,p_budget_unit1_percent => l_budget_unit1_percent
,p_budget_unit1_value_type_cd => p_worksheet_details_rec.budget_unit1_value_type_cd
,p_budget_unit1_value => p_worksheet_details_rec.budget_unit1_value
,p_budget_unit1_available => p_worksheet_details_rec.budget_unit1_available
,p_budget_unit2_percent => l_budget_unit2_percent
,p_budget_unit2_value_type_cd => p_worksheet_details_rec.budget_unit2_value_type_cd
,p_budget_unit2_value => p_worksheet_details_rec.budget_unit2_value
,p_budget_unit2_available => p_worksheet_details_rec.budget_unit2_available
,p_budget_unit3_percent => l_budget_unit3_percent
,p_budget_unit3_value_type_cd => p_worksheet_details_rec.budget_unit3_value_type_cd
,p_budget_unit3_value => p_worksheet_details_rec.budget_unit3_value
,p_budget_unit3_available => p_worksheet_details_rec.budget_unit3_available
,p_object_version_number => l_object_version_number
);
hr_utility.set_location('Create API in update mode : ', 9);
ELSE -- i.e not update mode
-- call insert API
hr_utility.set_location('Create API in INSERT Mode : ', 10);
pqh_process_batch_log.insert_log
(
p_message_type_cd => 'ERROR',
p_message_text => SQLERRM
);
pqh_process_batch_log.insert_log
(
p_message_type_cd => 'ERROR',
p_message_text => SQLERRM
);
pqh_process_batch_log.insert_log
(
p_message_type_cd => 'ERROR',
p_message_text => SQLERRM
);
pqh_process_batch_log.insert_log
(
p_message_type_cd => 'ERROR',
p_message_text => SQLERRM
);
pqh_process_batch_log.insert_log
(
p_message_type_cd => 'ERROR',
p_message_text => SQLERRM
);
SELECT *
FROM pqh_worksheets
WHERE worksheet_id = p_worksheet_id;
SELECT *
FROM pqh_budget_details
WHERE budget_version_id = p_curr_budget_version_id
AND budget_detail_id NOT IN ( SELECT budget_detail_id
FROM pqh_worksheet_details
WHERE worksheet_id = p_worksheet_id
AND NVL(action_cd,'X') = 'B' ) ;
SELECT *
FROM pqh_budget_periods
WHERE budget_detail_id = p_budget_detail_id;
SELECT *
FROM pqh_budget_sets
WHERE budget_period_id = p_budget_period_id;
SELECT *
FROM pqh_budget_elements
WHERE budget_set_id = p_budget_set_id;
SELECT *
FROM pqh_budget_fund_srcs
WHERE budget_element_id = p_budget_element_id;
SELECT bvr.budget_version_id
FROM pqh_budget_versions bvr, pqh_worksheets wks
WHERE bvr.budget_id = wks.budget_id
AND bvr.version_number = wks.version_number
AND wks.worksheet_id = p_worksheet_id;
PROCEDURE delete_child_rows
(
p_worksheet_id IN pqh_worksheets.worksheet_id%TYPE
)
IS
-- local variables and cursors
CURSOR budget_period_id_cur IS
SELECT bpr.budget_period_id
FROM pqh_worksheets wks, pqh_worksheet_details wdt ,pqh_budget_periods bpr
WHERE wks.worksheet_id = wdt.worksheet_id
AND wdt.action_cd = 'B'
AND wdt.budget_detail_id = bpr.budget_detail_id
AND wks.worksheet_id = p_worksheet_id;
SELECT bst.budget_set_id
FROM pqh_budget_sets bst, pqh_budget_periods bpr, pqh_budget_details bdt,
pqh_worksheet_details wdt , pqh_worksheets wks
WHERE bst.budget_period_id = bpr.budget_period_id
AND bpr.budget_detail_id = wdt.budget_detail_id
AND wks.worksheet_id = wdt.worksheet_id
AND wdt.action_cd = 'B'
AND wks.worksheet_id = p_worksheet_id;
SELECT bel.budget_element_id
FROM pqh_budget_elements bel, pqh_budget_sets bst,
pqh_budget_periods bpr,
pqh_worksheet_details wdt , pqh_worksheets wks
WHERE bel.budget_set_id = bst.budget_set_id
AND bst.budget_period_id = bpr.budget_period_id
AND bpr.budget_detail_id = wdt.budget_detail_id
AND wks.worksheet_id = wdt.worksheet_id
AND wdt.action_cd = 'B'
AND wks.worksheet_id = p_worksheet_id;
SELECT bfs.budget_fund_src_id
FROM pqh_budget_fund_srcs bfs, pqh_budget_elements bel, pqh_budget_sets bst,
pqh_budget_periods bpr,
pqh_worksheet_details wdt , pqh_worksheets wks
WHERE bfs.budget_element_id = bel.budget_element_id
AND bel.budget_set_id = bst.budget_set_id
AND bst.budget_period_id = bpr.budget_period_id
AND bpr.budget_detail_id = wdt.budget_detail_id
AND wks.worksheet_id = wdt.worksheet_id
AND wdt.action_cd = 'B'
AND wks.worksheet_id = p_worksheet_id;
l_proc varchar2(72) := g_package||'delete_child_rows';
DELETE from pqh_budget_fund_srcs
WHERE budget_fund_src_id = l_budget_fund_src_id;
DELETE from pqh_budget_elements
WHERE budget_element_id = l_budget_element_id;
DELETE from pqh_budget_sets
WHERE budget_set_id = l_budget_set_id;
DELETE from pqh_budget_periods
WHERE budget_period_id = l_budget_period_id;
we update pqh_budget_details and so don't delete due to foreign key constraints
DELETE FROM pqh_budget_details
WHERE budget_detail_id IN (
SELECT wdt.budget_detail_id
FROM pqh_worksheet_details wdt , pqh_worksheets wks
WHERE wks.worksheet_id = wdt.worksheet_id
AND wdt.budget_detail_id IS NOT NULL
AND NVL(wdt.action_cd,'X') = 'B'
AND wks.worksheet_id = p_worksheet_id
);
SELECT *
FROM pqh_worksheets
WHERE worksheet_id = p_worksheet_id;
SELECT count(*)
FROM pqh_budget_details
WHERE budget_version_id = p_budget_version_id;
hr_utility.set_message(8302,'PQH_INVALID_UPDATE_MODE');
SELECT *
FROM pqh_budgets
WHERE budget_id =
(
SELECT b.budget_id
FROM pqh_budgets b, pqh_worksheets wks
WHERE wks.worksheet_id = p_worksheet_id
AND wks.budget_id = b.budget_id
);
SELECT *
FROM pqh_worksheets
WHERE worksheet_id = p_worksheet_id ;
SELECT table_route_id
FROM pqh_table_route
WHERE table_alias = p_table_alias;
SELECT *
FROM pqh_worksheet_details
WHERE worksheet_detail_id = p_worksheet_detail_id ;
SELECT *
FROM pqh_worksheet_periods
WHERE worksheet_period_id = p_worksheet_period_id ;
SELECT *
FROM per_time_periods
WHERE time_period_id = p_time_period_id ;
SELECT *
FROM pqh_worksheet_budget_sets
WHERE worksheet_budget_set_id = p_worksheet_budget_set_id;
SELECT *
FROM pqh_dflt_budget_sets
WHERE dflt_budget_set_id = p_dflt_budget_set_id;
SELECT *
FROM pqh_worksheet_bdgt_elmnts
WHERE worksheet_bdgt_elmnt_id = p_worksheet_bdgt_elmnt_id;
SELECT element_name
FROM pay_element_types_f_tl
WHERE element_type_id = p_element_type_id
and language = userenv('LANG');
SELECT *
FROM pqh_worksheet_fund_srcs
WHERE worksheet_fund_src_id = p_worksheet_fund_src_id;
SELECT *
FROM pay_cost_allocation_keyflex
WHERE cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
If the chk_wks_errors has errors then we will update the wks status to 'APPROVED' from the 'SUBMITTED'
status
If p_validate_only is YES then we would only call the check_wks_errors procedure
p_transaction_id is the WKS Detail ID
If the Apply transaction is successful and the budget can be transfered to GL , we would call the
Apply to GL procedure
*/
l_proc varchar2(72) := g_package||'apply_transaction';
SELECT *
FROM pqh_worksheet_details
WHERE worksheet_detail_id = p_transaction_id;
SELECT *
FROM pqh_transaction_categories
WHERE transaction_category_id = p_transaction_category_id;
SELECT *
FROM pqh_worksheets
WHERE worksheet_id = p_worksheet_id;
SELECT *
FROM pqh_budgets
WHERE budget_id = p_budget_id;
select wf_transaction_category_id
from pqh_worksheets wks, pqh_worksheet_details wkd
where wks.worksheet_id = wkd.worksheet_id
and wkd.worksheet_detail_id = p_transaction_id;
SELECT SUM(nvl(BUDGET_UNIT1_VALUE,0)) ,
SUM(nvl(BUDGET_UNIT2_VALUE,0)) ,
SUM(nvl(BUDGET_UNIT3_VALUE,0))
FROM pqh_budget_details
WHERE budget_version_id = p_budget_version_id;
SELECT *
FROM pqh_budget_versions
WHERE budget_version_id = p_budget_version_id;
SELECT *
FROM pqh_worksheets
WHERE worksheet_id = p_worksheet_id;
pqh_budget_versions_api.update_budget_version
(
p_validate => false
,p_budget_version_id => l_pqh_budget_version_rec.budget_version_id
,p_budget_id => l_pqh_budget_version_rec.budget_id
,p_version_number => l_pqh_budget_version_rec.version_number
,p_date_from => l_pqh_worksheets_rec.date_from
,p_date_to => l_pqh_worksheets_rec.date_to
,p_transfered_to_gl_flag => l_pqh_budget_version_rec.transfered_to_gl_flag
,p_xfer_to_other_apps_cd => l_pqh_budget_version_rec.xfer_to_other_apps_cd
,p_object_version_number => l_object_version_number
,p_budget_unit1_value => l_budget_unit1_value
,p_budget_unit2_value => l_budget_unit2_value
,p_budget_unit3_value => l_budget_unit3_value
,p_budget_unit1_available => l_pqh_budget_version_rec.budget_unit1_available
,p_budget_unit2_available => l_pqh_budget_version_rec.budget_unit2_available
,p_budget_unit3_available => l_pqh_budget_version_rec.budget_unit3_available
,p_effective_date => sysdate
);
This procedure will update the budget status to FROZEN once the budgte is successfully
applied
*/
l_proc varchar2(72) := g_package||'updt_budget_status';
SELECT *
FROM pqh_budgets
WHERE budget_id = p_budget_id
AND NVL(status,'X') <> 'FROZEN';
pqh_budgets_api.update_budget
(
p_validate => false
,p_budget_id => p_budget_id
,p_object_version_number => l_object_version_number
,p_status => 'FROZEN'
,p_effective_date => sysdate
);
This procedure will update the wks status to APPLIED after budget is applied successfully
If the chk wks has error then the wks status will be changed to APPROVED from SUBMITTED
If the wks has errors then the apply budget will not be called. In this case we will not have
g_budget_version_id computed.
*/
l_proc varchar2(72) := g_package||'updt_wks_status';
SELECT *
FROM pqh_worksheets
WHERE worksheet_id = p_worksheet_id;
pqh_worksheets_api.update_worksheet
(
p_validate => false
,p_worksheet_id => p_worksheet_id
,p_object_version_number => l_wks_ovn
,p_transaction_status => p_status
,p_budget_version_id => g_budget_version_id
,p_effective_date => sysdate
);
pqh_worksheets_api.update_worksheet
(
p_validate => false
,p_worksheet_id => p_worksheet_id
,p_object_version_number => l_wks_ovn
,p_transaction_status => p_status
,p_effective_date => sysdate
);
and return D or I. This will be used by apply_transaction to determine whether to update the wks_status
to APPROVED from SUBMIT if the wks had errors
In the following matrix , we have used the abbreviations as follows :
Immediate : I
Deferred : D
Future Dt : F
Past or Present Dt : P-P
*---------------------------------------*
| Future | Action | Post | Net |
| Action CD | Date | Style | |
----------------------------------------
| I | P-P | I | I |
----------------------------------------
| I | P-P | D | D |
----------------------------------------
| I | F | I | I |
----------------------------------------
| I | F | D | D |
----------------------------------------
| D | P-P | I | I |
----------------------------------------
| D | P-P | D | D |
----------------------------------------
| D | F | I | D |
----------------------------------------
| D | F | D | D |
----------------------------------------
*/
l_proc varchar2(72) := g_package||'get_txn_state';
SELECT *
FROM pqh_transaction_categories
WHERE transaction_category_id = p_transaction_category_id;
select worksheet_detail_id
from pqh_worksheet_details
where worksheet_id = p_worksheet_id
and nvl(action_cd,'B') ='D';
select parent_worksheet_detail_id
from pqh_worksheet_details
where worksheet_detail_id = p_transaction_id;
cursor c1 is select status,parent_worksheet_detail_id,
budget_unit1_available,budget_unit2_available,budget_unit3_available
from pqh_worksheet_details
where worksheet_detail_id = p_worksheet_detail_id
and action_cd = 'D'
for update of status;
select budget_unit1_available,budget_unit2_available, budget_unit3_available
from pqh_worksheet_details
where worksheet_detail_id = p_parent_worksheet_detail_id
for update of budget_unit1_available,budget_unit2_available, budget_unit3_available;
update pqh_worksheet_details
set budget_unit1_available = nvl(j.budget_unit1_available,0) + nvl(i.budget_unit1_available,0)
, budget_unit2_available = nvl(j.budget_unit2_available,0) + nvl(i.budget_unit2_available,0)
, budget_unit3_available = nvl(j.budget_unit3_available,0) + nvl(i.budget_unit3_available,0)
where current of c2;
update pqh_worksheet_details
set status = 'APPROVED'
where current of c1;
cursor c0 is select worksheet_id,organization_id
from pqh_worksheet_details
where worksheet_detail_id = p_transaction_id;
cursor c1 is select budget_id,worksheet_name,version_number,worksheet_mode_cd,date_from,date_to,wf_transaction_category_id
from pqh_worksheets
where worksheet_id = l_worksheet_id;
cursor c2 is select budget_name,budgeted_entity_cd,budget_style_cd,budget_start_date,budget_end_date
from pqh_budgets
where budget_id = l_budget_id;
cursor c3 is select name from pqh_transaction_categories
where transaction_category_id = l_tran_cat_id;
cursor c4 is select name from hr_all_organization_units_tl
where organization_id = l_organization_id
and language = userenv('LANG');
This procedure will update the wks status and wks detail status
*/
l_proc varchar2(72) := g_package||'set_status';
cursor c1 is select wkd.worksheet_detail_id transaction_id,
wkd.parent_worksheet_detail_id parent_transaction_id,
wkd.status transaction_status
from pqh_worksheet_details wkd, pqh_worksheets wks
where parent_worksheet_detail_id = p_transaction_id
and parent_worksheet_detail_id <> worksheet_detail_id
and wkd.worksheet_id = wks.worksheet_id
and wks.wf_transaction_category_id = p_transaction_category_id
and NVL(wkd.status,'X') <> p_status;
SELECT wks.*
FROM pqh_worksheets wks
, pqh_worksheet_details wkd
WHERE wkd.worksheet_detail_id = p_transaction_id
and wks.worksheet_id = wkd.worksheet_id;
SELECT wdt.*
FROM pqh_worksheet_details wdt
WHERE wdt.worksheet_detail_id = p_transaction_id;
pqh_worksheet_details_api.update_worksheet_detail
(
p_validate => false
,p_worksheet_detail_id => p_transaction_id
,p_object_version_number => l_object_version_number_wdt
,p_status => p_status
,p_effective_date => sysdate
);
pqh_worksheets_api.update_worksheet
(
p_validate => false
,p_worksheet_id => l_wks_rec.worksheet_id
,p_object_version_number => l_object_version_number
,p_transaction_status => p_status
,p_effective_date => sysdate
);