[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,p_update_status IN VARCHAR2 := 'Y'
,p_act_budget_id IN NUMBER
,p_target_fund_id IN NUMBER
,p_source_fund_id IN NUMBER
,p_requester_id IN NUMBER
,p_approver_id IN NUMBER
-- ,p_requested_amount IN NUMBER -- both in TRANSFER FROM fund currency ,(SOURCE)
-- this is because the amount which goes to the workflow goes in TRANSFER FROM currency
-- and also returns in from fund currency
,p_requestor_owner IN VARCHAR2 := 'N'
,p_approved_amount IN NUMBER -- in TARNSFER FROM fund currency
,p_child_flag IN VARCHAR2 := 'N'
,p_note IN VARCHAR2
,p_workflow_flag IN VARCHAr2 := 'N'-- flag to indicate that th ereor is being updated from workflow
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
IS
-- Local variables
l_api_name CONSTANT VARCHAR2(30) := 'Approve_Request';
SELECT original_budget source_org_budget
,transfered_in_amt source_trans_in_amt
,transfered_out_amt source_trans_out_amt
,holdback_amt source_holdback_amt
,currency_code_tc source_currency_code
,object_version_number source_obj_num
,committed_amt
FROM ozf_funds_all_vl
WHERE fund_id = cv_fund_id;
SELECT original_budget target_org_budget
,transfered_in_amt target_trans_in_amt
,status_code target_status_code
,user_status_id target_user_status_id
,currency_code_tc target_currency_code
,object_version_number target_obj_num
FROM ozf_funds_all_vl
WHERE fund_id = cv_fund_id;
SELECT object_version_number
FROM ozf_act_budgets
WHERE activity_budget_id = p_act_budget_id;
SELECT currency_code_tc
FROM ozf_funds_all_vl
WHERE fund_id = p_to_fund_id;
ozf_funds_pvt.update_fund(
p_api_version => l_api_version
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_fund_rec => l_source_fund_rec
,p_mode => 'ADJUST');
ozf_funds_pvt.update_fund(
p_api_version => l_api_version
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_fund_rec => l_target_fund_rec
,p_mode => g_cons_fund_mode);
ozf_actbudgets_pvt.update_act_budgets(
p_api_version => l_api_version
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_act_budgets_rec => l_act_budget_rec
,p_child_approval_flag => FND_API.g_false
,p_requestor_owner_flag => p_requestor_owner
);
ozf_funds_pvt.update_fund(
p_api_version => l_api_version
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_fund_rec => l_target_fund_rec
,p_mode => 'ADJUST');
ozf_actbudgets_pvt.update_act_budgets(
p_api_version => l_api_version
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_act_budgets_rec => l_act_budget_rec);
SELECT original_budget target_org_budget
,transfered_in_amt target_trans_in_amt
,status_code target_status_code
,user_status_id target_user_status_id
,currency_code_tc target_currency_code
,object_version_number target_obj_num
FROM ozf_funds_all_vl
WHERE fund_id = cv_fund_id;
SELECT object_version_number
FROM ozf_act_budgets
WHERE activity_budget_id = p_act_budget_id;
SELECT currency_code_tc
FROM ozf_funds_all_vl
WHERE fund_id = p_to_fund_id;
ozf_funds_pvt.update_fund(
p_api_version => l_api_version
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_fund_rec => l_target_fund_rec
,p_mode => g_cons_fund_mode);
ozf_actbudgets_pvt.update_act_budgets(
p_api_version => l_api_version
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_act_budgets_rec => l_act_budget_rec
,p_child_approval_flag => FND_API.g_true
);
ozf_funds_pvt.update_fund(
p_api_version => l_api_version
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_fund_rec => l_target_fund_rec
,p_mode => 'ADJUST');
ozf_actbudgets_pvt.update_act_budgets(
p_api_version => l_api_version
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_act_budgets_rec => l_act_budget_rec);
,p_update_status IN VARCHAR2 := 'Y'
,p_approval_for IN VARCHAR2 := 'FUND'
,p_approval_for_id IN NUMBER
,p_requester_id IN NUMBER
,p_requested_amount IN NUMBER
,p_approval_fm IN VARCHAR2 := 'FUND'
-- ,p_approval_fm_id IN NUMBER DEFAULT NULL yzhao: fix GSCC. default can only be defined in spec.
,p_approval_fm_id IN NUMBER
,p_transfer_type IN VARCHAR2 := 'REQUEST' --- 'REQUEST' OR 'TRANSFER'
,p_child_flag IN VARCHAR2 := 'N' -- flag to indicate whether it is a child fund creation
,p_act_budget_id IN NUMBER := NULL -- request_id ( for a child fund it is null)
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_justification IN VARCHAR2
,p_allocation_flag IN VARCHAR2 := 'N' -- flag to indicate whether it is an allocation or not
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_request_id OUT NOCOPY NUMBER
,x_approver_id OUT NOCOPY NUMBER
,x_is_requester_owner OUT NOCOPY VARCHAR2 -- Use this variable to conditionally trigger the workflow if value ='NO'
)
IS
-- Local variables
l_api_version CONSTANT NUMBER := 1.0;
SELECT object_version_number
FROM ozf_funds_all_b
WHERE fund_id = cv_fund_id;
SELECT owner
FROM ozf_funds_all_b
WHERE fund_id = p_parent_fund_id;
SELECT currency_code_tc
FROM ozf_funds_all_vl
WHERE fund_id = p_fm_fund_id;
SELECT currency_code_tc
FROM ozf_funds_all_vl
WHERE fund_id = p_to_fund_id;
SELECT object_version_number
,request_amount
,user_status_id
FROM ozf_act_budgets
WHERE activity_budget_id = p_act_budget_id;
ozf_actbudgets_pvt.update_act_budgets(
p_api_version => l_api_version
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_act_budgets_rec => l_act_budget_rec
,p_child_approval_flag => FND_API.g_true
-- 10/22/2001 mpande Changed code different owner allocation bug
,p_requestor_owner_flag =>x_is_requester_owner );
,p_update_status => p_update_status
,p_act_budget_id => l_act_budget_id
,p_target_fund_id => p_approval_for_id
,p_source_fund_id => p_approval_fm_id
,p_requester_id => p_requester_id
,p_requestor_owner => x_is_requester_owner
,p_approver_id => x_approver_id
-- ,p_requested_amount => l_fm_curr_requested_amount -- should be passed transferring fm fund_currency
,p_approved_amount => l_fm_curr_requested_amount -- in transferring fm fund_currency
,p_note => NULL
,p_child_flag => p_child_flag
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
SELECT act.request_date
,act.budget_source_id approval_from_id
,fund1.short_name from_budget_name
,fund1.owner_full_name from_budget_owner_name
,fund1.fund_number from_budget_number
,fund1.currency_code_tc from_budget_curr
,act.act_budget_used_by_id approval_for_id
,fund2.short_name to_budget_name
,fund2.owner_full_name to_budget_owner_name
,fund2.fund_number to_budget_number
,fund2.currency_code_tc to_budget_curr
,act.date_required_by
,act.reason_code
FROM ozf_act_budgets act
,ozf_fund_details_v fund1
,ozf_fund_details_v fund2
WHERE activity_budget_id = p_act_budget_id
AND act.budget_source_id = fund1.fund_id
AND act.act_budget_used_by_id = fund2.fund_id;
SELECT act.request_date
,act.budget_source_id approval_from_id
,fund1.short_name from_budget_name
,fund1.owner_full_name from_budget_owner_name
,fund1.fund_number from_budget_number
,fund1.currency_code_tc from_budget_curr
,act.act_budget_used_by_id approval_for_id
,fund2.short_name to_budget_name
,fund2.owner_full_name to_budget_owner_name
,fund2.fund_number to_budget_number
,fund2.currency_code_tc to_budget_curr
,act.date_required_by
,act.reason_code
FROM ozf_act_budgets act
,ozf_fund_details_v fund1
,ozf_fund_details_v fund2
WHERE activity_budget_id = p_act_budget_id
AND act.budget_source_id = fund1.fund_id
AND act.act_budget_used_by_id = fund2.fund_id;
SELECT act.request_date
,act.budget_source_id approval_from_id
,fund1.short_name from_budget_name
,fund1.owner_full_name from_budget_owner_name
,fund1.fund_number from_budget_number
,fund1.currency_code_tc from_budget_curr
,act.act_budget_used_by_id approval_for_id
,fund2.short_name to_budget_name
,fund2.owner_full_name to_budget_owner_name
,fund2.fund_number to_budget_number
,fund2.currency_code_tc to_budget_curr
,act.date_required_by
,act.reason_code
FROM ozf_act_budgets act
,ozf_fund_details_v fund1
,ozf_fund_details_v fund2
WHERE activity_budget_id = p_act_budget_id
AND act.budget_source_id = fund1.fund_id
AND act.act_budget_used_by_id = fund2.fund_id;
SELECT act.request_date
,act.budget_source_id approval_from_id
,fund1.short_name from_budget_name
,fund1.owner_full_name from_budget_owner_name
,fund1.fund_number from_budget_number
,fund1.currency_code_tc from_budget_curr
,act.act_budget_used_by_id approval_for_id
,fund2.short_name to_budget_name
,fund2.owner_full_name to_budget_owner_name
,fund2.fund_number to_budget_number
,fund2.currency_code_tc to_budget_curr
,act.date_required_by
,act.reason_code
FROM ozf_act_budgets act
,ozf_fund_details_v fund1
,ozf_fund_details_v fund2
WHERE activity_budget_id = p_act_budget_id
AND act.budget_source_id = fund1.fund_id
AND act.act_budget_used_by_id = fund2.fund_id;
SELECT act.request_date
,act.budget_source_id approval_from_id
,fund1.short_name from_budget_name
,fund1.owner_full_name from_budget_owner_name
,fund1.fund_number from_budget_number
,fund1.currency_code_tc from_budget_curr
,act.act_budget_used_by_id approval_for_id
,fund2.short_name to_budget_name
,fund2.owner_full_name to_budget_owner_name
,fund2.fund_number to_budget_number
,fund2.currency_code_tc to_budget_curr
,act.date_required_by
,act.reason_code
FROM ozf_act_budgets act
,ozf_fund_details_v fund1
,ozf_fund_details_v fund2
WHERE activity_budget_id = p_act_budget_id
AND act.budget_source_id = fund1.fund_id
AND act.act_budget_used_by_id = fund2.fund_id;
SELECT act_budget_used_by_id
FROM ozf_act_budgets
WHERE activity_budget_id = p_act_budget_id;
SELECT budget_source_id
FROM ozf_act_budgets
WHERE activity_budget_id = p_act_budget_id;
SELECT to_char(category_id)
FROM ozf_funds_all_b
WHERE fund_id = p_src_id;
SELECT short_name
,custom_setup_id
,original_budget
,org_id
,to_char(category_id)
,start_date_active
,end_date_active
,owner
,currency_code_tc
FROM ozf_funds_all_vl
WHERE fund_id = p_act_id;
SELECT fund.short_name
,fund.custom_setup_id
,act1.request_amount
,fund.org_id
,'FUND'
,fund.start_date_active
,fund.end_date_active
,act1.requester_id
,act1.request_currency
FROM ozf_act_budgets act1
,ozf_funds_all_vl fund
WHERE activity_budget_id = p_act_budget_id
AND act1.act_budget_used_by_id = fund.fund_id;
SELECT notes
FROM jtf_notes_vl
WHERE source_object_code = 'AMS_FREQ'
AND note_type = 'AMS_JUSTIFICATION'
AND source_object_id = p_act_budget_id;
SELECT b.text_value, b.number_value
FROM wf_routing_rules a, wf_routing_rule_attributes b
WHERE a.rule_id = b.rule_id
AND a.role = p_approver_name
AND TRUNC(sysdate) BETWEEN TRUNC(NVL(begin_date, sysdate -1)) AND
TRUNC(NVL(end_date,sysdate+1))
AND a.message_name = 'AMS_APPROVAL_REQUIRED_OZF'
AND b.name = 'AMS_AMOUNT';
SELECT org_id
FROM ozf_funds_all_b
WHERE fund_id = p_id;
PROCEDURE update_budgettrans_status(
itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,resultout OUT NOCOPY VARCHAR2)
IS
l_status_code VARCHAR2(30);
l_api_name CONSTANT VARCHAR2(30) := 'Update_ParBudget_Status';
l_update_status VARCHAR2(30);
SELECT budget_source_id approval_from_id
,act_budget_used_by_id approval_for_id
,request_currency
,fund.currency_code_tc
FROM ozf_act_budgets act
,ozf_funds_all_b fund
WHERE activity_budget_id = p_act_budget_id
AND act.budget_source_id = fund.fund_id;
l_update_status :=
wf_engine.getitemattrtext(
itemtype => itemtype
,itemkey => itemkey
,aname => 'UPDATE_GEN_STATUS');
IF l_update_status = 'APPROVED' THEN
l_next_status_id :=
wf_engine.getitemattrnumber(
itemtype => itemtype
,itemkey => itemkey
,aname => 'AMS_NEW_STAT_ID');
ELSIF l_update_status = 'REJECTED' THEN
l_next_status_id :=
wf_engine.getitemattrnumber (
itemtype=> itemtype,
itemkey=> itemkey,
aname => 'AMS_REJECT_STAT_ID'
);
IF l_update_status = 'APPROVED' THEN
approve_request(
p_commit => fnd_api.g_false
,p_update_status => 'Y'
,p_act_budget_id => l_act_budget_id
,p_target_fund_id => l_approval_for_id
,p_source_fund_id => l_approval_fm_id
,p_requester_id => l_requester_id
,p_requestor_owner => 'N'
,p_approver_id => l_approver_id
-- ,p_requested_amount => l_fm_curr_requested_amount -- should be passed transferring fm fund_currency
,p_approved_amount => l_approved_amt_in_from_curr -- in transferring fm fund_currency
,p_child_flag => l_child_transfer_flag
,p_workflow_flag => 'Y'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_note => l_note);
END update_budgettrans_status;
SELECT fund_id
,(NVL(original_budget, 0) - NVL(holdback_amt, 0) + NVL(transfered_in_amt,0) - NVL(transfered_out_amt, 0)- NVL(committed_amt,0)) available_budget
,holdback_amt
,object_version_number
FROM ozf_funds_all_b
WHERE fund_id = cv_fund_id;
SELECT activity_budget_id
,status_code
,object_version_number
FROM ozf_act_budgets
WHERE activity_budget_id = p_act_budget_id;
ozf_funds_pvt.update_fund(
p_api_version => l_api_version
,p_commit => fnd_api.g_false
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_fund_rec => l_fund_rec
,p_mode => 'ADJUST');