[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,p_mode IN VARCHAR2 :='UPDATE'-- added by mpande 12/27/2001
);
,p_mode IN VARCHAR2 :='UPDATE'-- added by mpande 12/27/2001
,p_resale_batch_id IN NUMBER DEFAULT NULL
,p_batch_type IN VARCHAR2
,p_use_fund_staging_tables IN VARCHAR2 DEFAULT 'F'
,px_ozf_funds_old_rectype IN OZF_UTILITY_PVT.ozf_funds_all_b_rectype
,px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table -- For TPA Parallel Execution ER - 9614703
);
SELECT custom_setup_id
FROM ozf_offers
WHERE qp_list_header_id = p_object_id;
SELECT ozf_act_budgets_s.NEXTVAL
FROM DUAL;
SELECT 1
FROM ozf_act_budgets
WHERE activity_budget_id = p_id;
SELECT 1
FROM ozf_funds_all_b
WHERE 'FUND' = p_type
AND fund_type = 'QUOTA'
AND fund_id = p_fund_id;
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_id;
SELECT currency_code
FROM gl_ledgers_public_v
WHERE ledger_id = p_ledger_id;
INSERT INTO ozf_act_budgets
(activity_budget_id, -- standard who columns
last_update_date
, last_updated_by
, creation_date
,created_by, last_update_login -- other columns
,object_version_number
,act_budget_used_by_id
,arc_act_budget_used_by, budget_source_type
,budget_source_id, transaction_type
,request_amount, request_currency
,request_date, user_status_id
,status_code, approved_amount
,approved_original_amount
,approved_in_currency
,approval_date, approver_id
,spent_amount, partner_po_number
,partner_po_date, partner_po_approver
,posted_flag, adjusted_flag
,parent_act_budget_id, contact_id
,reason_code, transfer_type
,requester_id
,date_required_by
,parent_source_id
,parent_src_curr
,parent_src_apprvd_amt
,partner_holding_type
,partner_address_id, vendor_id
,owner_id
,recal_flag
,attribute_category, attribute1
,attribute2, attribute3
,attribute4, attribute5
,attribute6, attribute7
,attribute8, attribute9
,attribute10, attribute11
,attribute12, attribute13
,attribute14, attribute15
,approved_amount_fc
,src_curr_request_amt
)
VALUES (l_act_budgets_rec.activity_budget_id, -- standard who columns
SYSDATE, fnd_global.user_id, SYSDATE
,fnd_global.user_id, fnd_global.conc_login_id, 1
, -- object_version_number
l_act_budgets_rec.act_budget_used_by_id
,l_act_budgets_rec.arc_act_budget_used_by, l_act_budgets_rec.budget_source_type
,l_act_budgets_rec.budget_source_id, l_act_budgets_rec.transaction_type
,l_act_budgets_rec.request_amount, l_act_budgets_rec.request_currency
,NVL (l_act_budgets_rec.request_date, SYSDATE), l_act_budgets_rec.user_status_id
,NVL (l_act_budgets_rec.status_code, 'NEW'), l_act_budgets_rec.approved_amount
,l_act_budgets_rec.approved_original_amount
,l_act_budgets_rec.approved_in_currency
,l_act_budgets_rec.approval_date
,l_act_budgets_rec.approver_id
,l_act_budgets_rec.spent_amount, l_act_budgets_rec.partner_po_number
,l_act_budgets_rec.partner_po_date, l_act_budgets_rec.partner_po_approver
,l_act_budgets_rec.posted_flag, l_act_budgets_rec.adjusted_flag
,l_act_budgets_rec.parent_act_budget_id, l_act_budgets_rec.contact_id
,l_act_budgets_rec.reason_code, l_act_budgets_rec.transfer_type
,NVL (
l_act_budgets_rec.requester_id
,ozf_utility_pvt.get_resource_id (fnd_global.user_id)
) --l_act_budgets_rec.requester_id
,l_act_budgets_rec.date_required_by
,null --l_act_budgets_rec.parent_source_id
,l_act_budgets_rec.parent_src_curr
,l_act_budgets_rec.parent_src_apprvd_amt
,l_act_budgets_rec.partner_holding_type
,l_act_budgets_rec.partner_address_id, l_act_budgets_rec.vendor_id
,l_act_budgets_rec.owner_id
,l_act_budgets_rec.recal_flag
,p_act_budgets_rec.attribute_category, p_act_budgets_rec.attribute1
,p_act_budgets_rec.attribute2, p_act_budgets_rec.attribute3
,p_act_budgets_rec.attribute4, p_act_budgets_rec.attribute5
,p_act_budgets_rec.attribute6, p_act_budgets_rec.attribute7
,p_act_budgets_rec.attribute8, p_act_budgets_rec.attribute9
,p_act_budgets_rec.attribute10, p_act_budgets_rec.attribute11
,p_act_budgets_rec.attribute12, p_act_budgets_rec.attribute13
,p_act_budgets_rec.attribute14, p_act_budgets_rec.attribute15
,l_fc_amount
,l_act_budgets_rec.src_curr_req_amt);
PROCEDURE update_act_budgets (
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
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_act_budgets_rec IN act_budgets_rec_type
) IS
l_utilized_amount NUMBER;
update_act_budgets (
p_api_version=> p_api_version
,p_init_msg_list=> p_init_msg_list
,p_commit=> p_commit
,p_validation_level=> p_validation_level
,x_return_status=> x_return_status
,x_msg_count=> x_msg_count
,x_msg_data=> x_msg_data
,p_act_budgets_rec=> p_act_budgets_rec
,p_parent_process_flag=> fnd_api.g_false
,p_parent_process_key=> fnd_api.g_miss_char
,p_parent_context=> fnd_api.g_miss_char
,p_parent_approval_flag=> fnd_api.g_false
,p_continue_flow=> fnd_api.g_false
,p_child_approval_flag=> fnd_api.g_false
-- 10/22/2001 mpande Changed code different owner allocation bug
,p_requestor_owner_flag => 'N'
,x_utilized_amount => l_utilized_amount
);
END update_act_budgets;
PROCEDURE update_act_budgets (
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
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_act_budgets_rec IN act_budgets_rec_type
,p_child_approval_flag IN VARCHAR2
-- 10/22/2001 mpande Changed code different owner allocation bug
,p_requestor_owner_flag IN VARCHAR2 := 'N'
,p_act_util_rec IN act_util_rec_type := NULL
) IS
l_utilized_amount NUMBER;
update_act_budgets (
p_api_version=> p_api_version
,p_init_msg_list=> p_init_msg_list
,p_commit=> p_commit
,p_validation_level=> p_validation_level
,x_return_status=> x_return_status
,x_msg_count=> x_msg_count
,x_msg_data=> x_msg_data
,p_act_budgets_rec=> p_act_budgets_rec
,p_child_approval_flag=> p_child_approval_flag
,p_parent_process_flag=> fnd_api.g_false
,p_parent_process_key=> fnd_api.g_miss_char
,p_parent_context=> fnd_api.g_miss_char
,p_parent_approval_flag=> fnd_api.g_false
,p_continue_flow=> fnd_api.g_false
-- 10/22/2001 mpande Changed code different owner allocation bug
,p_requestor_owner_flag => p_requestor_owner_flag
,p_act_util_rec => p_act_util_rec
,x_utilized_amount => l_utilized_amount
);
END update_act_budgets;
PROCEDURE update_act_budgets (
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
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_act_budgets_rec IN act_budgets_rec_type
,p_child_approval_flag IN VARCHAR2
-- 10/22/2001 mpande Changed code different owner allocation bug
,p_requestor_owner_flag IN VARCHAR2 := 'N'
,p_act_util_rec IN act_util_rec_type := NULL
,x_utilized_amount OUT NOCOPY NUMBER -- yzhao: added 06/21/2004 to return actual utilized amount
) IS
BEGIN
update_act_budgets (
p_api_version=> p_api_version
,p_init_msg_list=> p_init_msg_list
,p_commit=> p_commit
,p_validation_level=> p_validation_level
,x_return_status=> x_return_status
,x_msg_count=> x_msg_count
,x_msg_data=> x_msg_data
,p_act_budgets_rec=> p_act_budgets_rec
,p_child_approval_flag=> p_child_approval_flag
,p_parent_process_flag=> fnd_api.g_false
,p_parent_process_key=> fnd_api.g_miss_char
,p_parent_context=> fnd_api.g_miss_char
,p_parent_approval_flag=> fnd_api.g_false
,p_continue_flow=> fnd_api.g_false
-- 10/22/2001 mpande Changed code different owner allocation bug
,p_requestor_owner_flag => p_requestor_owner_flag
,p_act_util_rec => p_act_util_rec
,x_utilized_amount => x_utilized_amount
);
END update_act_budgets;
PROCEDURE update_act_budgets (
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
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_act_budgets_rec IN act_budgets_rec_type
,p_parent_process_flag IN VARCHAR2
,p_parent_process_key IN VARCHAR2
,p_parent_context IN VARCHAR2
,p_parent_approval_flag IN VARCHAR2
,p_continue_flow IN VARCHAR2
,p_child_approval_flag IN VARCHAR2 := fnd_api.g_false
,p_requestor_owner_flag IN VARCHAR2 := 'N'
,p_act_util_rec IN act_util_rec_type := NULL
) IS
l_utilized_amount NUMBER;
update_act_budgets (
p_api_version=> p_api_version
,p_init_msg_list=> p_init_msg_list
,p_commit=> p_commit
,p_validation_level=> p_validation_level
,x_return_status=> x_return_status
,x_msg_count=> x_msg_count
,x_msg_data=> x_msg_data
,p_act_budgets_rec=> p_act_budgets_rec
,p_child_approval_flag=> p_child_approval_flag
,p_parent_process_flag=>p_parent_process_flag
,p_parent_process_key=>p_parent_process_key
,p_parent_context=> p_parent_context
,p_parent_approval_flag=> p_parent_approval_flag
,p_continue_flow=> p_continue_flow
,p_requestor_owner_flag => p_requestor_owner_flag
,p_act_util_rec => p_act_util_rec
,x_utilized_amount => l_utilized_amount
);
END update_act_budgets;
PROCEDURE update_act_budgets (
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
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_act_budgets_rec IN act_budgets_rec_type
,p_parent_process_flag IN VARCHAR2
,p_parent_process_key IN VARCHAR2
,p_parent_context IN VARCHAR2
,p_parent_approval_flag IN VARCHAR2
,p_continue_flow IN VARCHAR2
,p_child_approval_flag IN VARCHAR2 := fnd_api.g_false
,p_requestor_owner_flag IN VARCHAR2 := 'N'
,p_act_util_rec IN act_util_rec_type := NULL
,x_utilized_amount OUT NOCOPY NUMBER
) IS
l_utilization_id NUMBER;
update_act_budgets (
p_api_version=> p_api_version
,p_init_msg_list=> p_init_msg_list
,p_commit=> p_commit
,p_validation_level=> p_validation_level
,x_return_status=> x_return_status
,x_msg_count=> x_msg_count
,x_msg_data=> x_msg_data
,p_act_budgets_rec=> p_act_budgets_rec
,p_child_approval_flag=> p_child_approval_flag
,p_parent_process_flag=>p_parent_process_flag
,p_parent_process_key=>p_parent_process_key
,p_parent_context=> p_parent_context
,p_parent_approval_flag=> p_parent_approval_flag
,p_continue_flow=> p_continue_flow
,p_requestor_owner_flag => p_requestor_owner_flag
,p_act_util_rec => p_act_util_rec
,x_utilized_amount => x_utilized_amount
,x_utilization_id => l_utilization_id
);
END update_act_budgets;
PROCEDURE update_act_budgets (
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
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_act_budgets_rec IN act_budgets_rec_type
,p_parent_process_flag IN VARCHAR2
,p_parent_process_key IN VARCHAR2
,p_parent_context IN VARCHAR2
,p_parent_approval_flag IN VARCHAR2
,p_continue_flow IN VARCHAR2
,p_child_approval_flag IN VARCHAR2 := fnd_api.g_false
,p_requestor_owner_flag IN VARCHAR2 := 'N'
,p_act_util_rec IN act_util_rec_type := NULL
,x_utilized_amount OUT NOCOPY NUMBER -- yzhao: 06/21/2004 added to return actual utilized amount\
,x_utilization_id OUT NOCOPY NUMBER ) IS
l_old_act_budgets_rec act_budgets_rec_type;
update_act_budgets (
p_api_version=> p_api_version
,p_init_msg_list=> p_init_msg_list
,p_commit=> p_commit
,p_validation_level=> p_validation_level
,x_return_status=> x_return_status
,x_msg_count=> x_msg_count
,x_msg_data=> x_msg_data
,p_old_act_budgets_rec => l_old_act_budgets_rec
,p_act_budgets_rec=> p_act_budgets_rec
,p_child_approval_flag=> p_child_approval_flag
,p_parent_process_flag=>p_parent_process_flag
,p_parent_process_key=>p_parent_process_key
,p_parent_context=> p_parent_context
,p_parent_approval_flag=> p_parent_approval_flag
,p_continue_flow=> p_continue_flow
,p_requestor_owner_flag => p_requestor_owner_flag
,p_act_util_rec => p_act_util_rec
,x_utilized_amount => x_utilized_amount
,p_batch_type => l_batch_type
,p_use_fund_staging_tables => l_use_fund_staging_tables
,px_ozf_act_budgets_tbl => l_ozf_act_budgets_tbl
,px_ozf_funds_old_rectype => lx_ozf_funds_old_rectype
,px_ozf_funds_new_tbl => lx_ozf_funds_new_tbl -- For TPA Parallel Execution ER - 9614703
,x_utilization_id => x_utilization_id
);
END update_act_budgets;
PROCEDURE update_act_budgets (
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
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_old_act_budgets_rec IN act_budgets_rec_type
,p_act_budgets_rec IN act_budgets_rec_type
,p_parent_process_flag IN VARCHAR2
,p_parent_process_key IN VARCHAR2
,p_parent_context IN VARCHAR2
,p_parent_approval_flag IN VARCHAR2
,p_continue_flow IN VARCHAR2
,p_child_approval_flag IN VARCHAR2 := fnd_api.g_false
,p_requestor_owner_flag IN VARCHAR2 := 'N'
,p_act_util_rec IN act_util_rec_type -- For Bug#8867381 removed ':= NULL'
,x_utilized_amount OUT NOCOPY NUMBER -- yzhao: 06/21/2004 added to return actual utilized amount
,p_batch_type IN VARCHAR2
,p_use_fund_staging_tables IN VARCHAR2
,px_ozf_act_budgets_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_act_budgets_table
,px_ozf_funds_old_rectype IN OZF_UTILITY_PVT.ozf_funds_all_b_rectype
,px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table -- For TPA Parallel Execution ER - 9614703
) IS
l_utilization_id NUMBER; --nirprasa - added for Bug 9383565
update_act_budgets (
p_api_version=> p_api_version
,p_init_msg_list=> p_init_msg_list
,p_commit=> p_commit
,p_validation_level=> p_validation_level
,x_return_status=> x_return_status
,x_msg_count=> x_msg_count
,x_msg_data=> x_msg_data
,p_old_act_budgets_rec => p_old_act_budgets_rec
,p_act_budgets_rec=> p_act_budgets_rec
,p_child_approval_flag=> p_child_approval_flag
,p_parent_process_flag=>p_parent_process_flag
,p_parent_process_key=>p_parent_process_key
,p_parent_context=> p_parent_context
,p_parent_approval_flag=> p_parent_approval_flag
,p_continue_flow=> p_continue_flow
,p_requestor_owner_flag => p_requestor_owner_flag
,p_act_util_rec => p_act_util_rec
,x_utilized_amount => x_utilized_amount
,p_batch_type => p_batch_type
,p_use_fund_staging_tables => p_use_fund_staging_tables
,px_ozf_act_budgets_tbl => px_ozf_act_budgets_tbl
,px_ozf_funds_old_rectype => px_ozf_funds_old_rectype
,px_ozf_funds_new_tbl => px_ozf_funds_new_tbl -- For TPA Parallel Execution ER - 9614703
,x_utilization_id => l_utilization_id
);
END update_act_budgets;
PROCEDURE update_act_budgets (
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
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_old_act_budgets_rec IN act_budgets_rec_type
,p_act_budgets_rec IN act_budgets_rec_type
,p_parent_process_flag IN VARCHAR2
,p_parent_process_key IN VARCHAR2
,p_parent_context IN VARCHAR2
,p_parent_approval_flag IN VARCHAR2
,p_continue_flow IN VARCHAR2
,p_child_approval_flag IN VARCHAR2 := fnd_api.g_false
,p_requestor_owner_flag IN VARCHAR2 := 'N'
,p_act_util_rec IN act_util_rec_type -- For Bug#8867381 removed ':= NULL'
,x_utilized_amount OUT NOCOPY NUMBER -- yzhao: 06/21/2004 added to return actual utilized amount
,p_batch_type IN VARCHAR2
,p_use_fund_staging_tables IN VARCHAR2
,px_ozf_act_budgets_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_act_budgets_table
,px_ozf_funds_old_rectype IN OZF_UTILITY_PVT.ozf_funds_all_b_rectype
,px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table -- For TPA Parallel Execution ER - 9614703
,x_utilization_id OUT NOCOPY NUMBER --nirprasa - added for Bug 9383565
) IS
l_api_name CONSTANT VARCHAR2 (30) := 'Update_Act_Budgets';
SELECT status_code, approved_amount, parent_src_apprvd_amt
FROM ozf_act_budgets
WHERE activity_budget_id = p_act_budgets_rec.activity_budget_id;
SELECT object_version_number, currency_code_tc, planned_amt
FROM ozf_funds_all_b
WHERE fund_id = l_fund_id;
SELECT 1
FROM ozf_funds_all_b
WHERE 'FUND' = p_type
AND fund_type = 'QUOTA'
AND fund_id = p_fund_id;
SELECT status_code
FROM ozf_offers
WHERE qp_list_header_id = p_act_budgets_rec.act_budget_used_by_id;
SELECT status_code
FROM ozf_offers
WHERE qp_list_header_id = l_qp_header_id;
SELECT objfundsum_id
, object_version_number
, planned_amt
, plan_curr_planned_amt
, univ_curr_planned_amt
FROM ozf_object_fund_summary
WHERE object_type = p_object_type
AND object_id = p_object_id
AND fund_id = p_fund_id;
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_id;
SELECT currency_code
FROM gl_ledgers_public_v
WHERE ledger_id = p_ledger_id;
SAVEPOINT update_act_budgets_pvt;
ozf_utility_pvt.debug_message(' update_Act_budgets.plan_currency_code mg: ' || p_act_util_rec.plan_currency_code);
ozf_objfundsum_pvt.update_objfundsum(
p_api_version => 1.0,
p_init_msg_list => Fnd_Api.G_FALSE,
p_validation_level => Fnd_Api.G_VALID_LEVEL_NONE,
p_objfundsum_rec => l_objfundsum_rec,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ozf_funds_pvt.update_fund (
p_api_version=> 1.0
,p_init_msg_list=> fnd_api.g_false
, -- allow the calling API to handle
p_commit=> fnd_api.g_false
, -- allow the calling API to handle
p_validation_level=> p_validation_level
,x_return_status=> l_return_status
,x_msg_count=> x_msg_count
,x_msg_data=> x_msg_data
,p_fund_rec=> l_fund_rec
,p_mode=> g_cons_fund_mode
);
ozf_objfundsum_pvt.update_objfundsum(
p_api_version => 1.0,
p_init_msg_list => Fnd_Api.G_FALSE,
p_validation_level => Fnd_Api.G_VALID_LEVEL_NONE,
p_objfundsum_rec => l_objfundsum_rec,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ozf_funds_pvt.update_fund (
p_api_version=> 1.0
,p_init_msg_list=> fnd_api.g_false
,p_commit=> fnd_api.g_false
,p_validation_level=> p_validation_level
,x_return_status=> l_return_status
,x_msg_count=> x_msg_count
,x_msg_data=> x_msg_data
,p_fund_rec=> l_fund_rec
,p_mode=> g_cons_fund_mode
);
,p_validation_mode=> jtf_plsql_api.g_update
,x_return_status=> l_return_status
);
,p_validation_mode=> jtf_plsql_api.g_update
,x_return_status=> l_return_status
);
ozf_utility_pvt.debug_message('Going to update mode as l_use_fund_staging_tables is :'||l_use_fund_staging_tables );
UPDATE ozf_act_budgets
SET last_update_date = SYSDATE
,last_updated_by = fnd_global.user_id
,last_update_login = fnd_global.conc_login_id
,object_version_number = l_act_budgets_rec.object_version_number
+ 1
,act_budget_used_by_id = l_act_budgets_rec.act_budget_used_by_id
,arc_act_budget_used_by = l_act_budgets_rec.arc_act_budget_used_by
,budget_source_type = l_act_budgets_rec.budget_source_type
,budget_source_id = l_act_budgets_rec.budget_source_id
,transaction_type = l_act_budgets_rec.transaction_type
,request_amount = l_act_budgets_rec.request_amount
,request_currency = l_act_budgets_rec.request_currency
,request_date = NVL (l_act_budgets_rec.request_date, SYSDATE)
,user_status_id = l_act_budgets_rec.user_status_id
,status_code = l_act_budgets_rec.status_code
,approved_amount = l_act_budgets_rec.approved_amount
,approved_original_amount = l_act_budgets_rec.approved_original_amount
,approved_in_currency = l_act_budgets_rec.approved_in_currency
,approval_date = l_act_budgets_rec.approval_date
,approver_id = l_act_budgets_rec.approver_id
,spent_amount = l_act_budgets_rec.spent_amount
,partner_po_number = l_act_budgets_rec.partner_po_number
,partner_po_date = l_act_budgets_rec.partner_po_date
,partner_po_approver = l_act_budgets_rec.partner_po_approver
,posted_flag = l_act_budgets_rec.posted_flag
,adjusted_flag = l_act_budgets_rec.adjusted_flag
,transfer_type = l_act_budgets_rec.transfer_type
,reason_code = l_act_budgets_rec.reason_code
,parent_act_budget_id = l_act_budgets_rec.parent_act_budget_id
,contact_id = l_act_budgets_rec.contact_id
,requester_id = NVL (
l_act_budgets_rec.requester_id
,ozf_utility_pvt.get_resource_id (fnd_global.user_id)
) --l_act_budgets_rec.requester_id
,date_required_by = l_act_budgets_rec.date_required_by
,parent_source_id = l_act_budgets_rec.parent_source_id,
parent_src_curr = l_act_budgets_rec.parent_src_curr,
parent_src_apprvd_amt = l_act_budgets_rec.parent_src_apprvd_amt,
partner_holding_type = l_act_budgets_rec.partner_holding_type
,partner_address_id = l_act_budgets_rec.partner_address_id
,vendor_id = l_act_budgets_rec.vendor_id
,owner_id = l_act_budgets_rec.owner_id
,recal_flag = l_act_budgets_rec.recal_flag
,attribute_category = p_act_budgets_rec.attribute_category
,attribute1 = p_act_budgets_rec.attribute1
,attribute2 = p_act_budgets_rec.attribute2
,attribute3 = p_act_budgets_rec.attribute3
,attribute4 = p_act_budgets_rec.attribute4
,attribute5 = p_act_budgets_rec.attribute5
,attribute6 = p_act_budgets_rec.attribute6
,attribute7 = p_act_budgets_rec.attribute7
,attribute8 = p_act_budgets_rec.attribute8
,attribute9 = p_act_budgets_rec.attribute9
,attribute10 = p_act_budgets_rec.attribute10
,attribute11 = p_act_budgets_rec.attribute11
,attribute12 = p_act_budgets_rec.attribute12
,attribute13 = p_act_budgets_rec.attribute13
,attribute14 = p_act_budgets_rec.attribute14
,attribute15 = p_act_budgets_rec.attribute15
-- 11/16/2001 mpande
,approved_amount_fc = l_fc_amount
-- 12/17/2001 mpande
,src_curr_request_amt = l_src_curr_request_amt
WHERE activity_budget_id = l_act_budgets_rec.activity_budget_id
AND object_version_number = l_act_budgets_rec.object_version_number;
l_operation := 'INSERT';
l_operation := 'UPDATE';
IF l_operation = 'UPDATE' THEN
px_ozf_act_budgets_tbl(l_budget_count).RESALE_BATCH_ID := l_resale_batch_id;
ELSIF l_operation = 'INSERT' THEN
l_budget_count := px_ozf_act_budgets_tbl.COUNT + 1;
ROLLBACK TO update_act_budgets_pvt;
ROLLBACK TO update_act_budgets_pvt;
ROLLBACK TO update_act_budgets_pvt;
END update_act_budgets;
PROCEDURE delete_act_budgets (
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
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_act_budget_id IN NUMBER
,p_object_version IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Act_Budgets';
SELECT status_code
FROM ozf_act_budgets
WHERE activity_budget_id = p_act_budget_id;
SAVEPOINT delete_act_budgets_pvt;
fnd_message.set_name ('OZF', 'OZF_ACT_BUDGET_NO_DELETE');
DELETE FROM ozf_act_budgets
WHERE activity_budget_id = l_act_budget_id
AND object_version_number = p_object_version;
ROLLBACK TO delete_act_budgets_pvt;
ROLLBACK TO delete_act_budgets_pvt;
ROLLBACK TO delete_act_budgets_pvt;
END delete_act_budgets;
SELECT status_code
FROM ozf_act_budgets
WHERE activity_budget_id = p_act_budget_id
AND object_version_number = p_object_version
FOR UPDATE OF activity_budget_id NOWAIT;
following update on UTILIZED record failed with approver not exists error,
which does not make sense.
-- Check FK parameter: approver_id
IF p_act_budgets_rec.approver_id <> fnd_api.g_miss_num THEN
l_table_name := 'ams_jtf_rs_emp_v';
SELECT approved_amount
FROM ozf_act_budgets
WHERE activity_budget_id = p_act_budgets_rec.activity_budget_id;
SELECT 'X'
FROM ozf_funds_all_b ozf
WHERE ozf.fund_type = 'FULLY_ACCRUED' and ozf.fund_id = p_fund_id
and ozf.plan_id = p_list_header_id;
SELECT offer_type, org_id
FROM ozf_offers
WHERE qp_list_header_id = l_qp_list_header_id;
SELECT NVL(ledger_id,0)
FROM ozf_funds_all_b
WHERE fund_id = p_fund_id;
IF p_validation_mode = jtf_plsql_api.g_update THEN
OPEN c_current_amount;
SELECT *
FROM ozf_act_budgets
WHERE activity_budget_id = p_act_budgets_rec.activity_budget_id;
SELECT user_status_id
FROM ams_user_statuses_vl
WHERE system_status_type = l_budget_status_type
AND system_status_code = p_status_code
AND default_flag = 'Y'; -- this should be yes and not 'N'
SELECT owner
FROM ozf_funds_all_b
WHERE fund_id = p_source_fund_id;
SELECT custom_setup_id
FROM ozf_offers
WHERE qp_list_header_id = p_object_id;
SELECT transaction_currency_code
FROM ams_campaigns_vl
WHERE campaign_id = p_object_id;
SELECT transaction_currency_code
FROM ams_campaign_schedules_vl
WHERE schedule_id = p_object_id;
SELECT currency_code_tc
FROM ams_event_headers_vl
WHERE event_header_id = p_object_id;
SELECT currency_code_tc
FROM ams_event_offers_vl
WHERE event_offer_id = p_object_id;
SELECT transaction_currency_code
FROM ams_deliverables_vl
WHERE deliverable_id = p_object_id;
SELECT currency_code_tc
FROM ozf_funds_all_b
WHERE fund_id = p_object_id;
SELECT nvl(transaction_currency_code, fund_request_curr_code)
FROM ozf_offers
WHERE qp_list_header_id = p_object_id;
SELECT currency_code
FROM qp_list_headers_b
WHERE list_header_id = p_object_id;
SELECT currency_code
FROM ozf_worksheet_headers_vl
WHERE worksheet_header_id = p_object_id;
SELECT currency_code
FROM ozf_request_headers_all_b
WHERE request_header_id = p_object_id;
SELECT currency_code
FROM ozf_request_headers_all_b
WHERE request_header_id = p_object_id;
,p_mode IN VARCHAR2 :='UPDATE'-- added by mpande 12/27/2001
) IS
l_resale_batch_id NUMBER;
,p_mode IN VARCHAR2 :='UPDATE'-- added by mpande 12/27/2001
,p_resale_batch_id IN NUMBER
,p_batch_type IN VARCHAR2
,p_use_fund_staging_tables IN VARCHAR2
,px_ozf_funds_old_rectype IN OZF_UTILITY_PVT.ozf_funds_all_b_rectype
,px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table -- For TPA Parallel Execution ER Bug#9614703
) IS
l_api_name CONSTANT VARCHAR2 (30) := 'Process_Approval';
SELECT object_version_number, currency_code_tc, planned_amt, committed_amt, recal_committed
FROM ozf_funds_all_b
WHERE fund_id = l_fund_id;
SELECT objfundsum_id
, object_version_number
, planned_amt
, committed_amt
, recal_committed_amt
, plan_curr_planned_amt
, plan_curr_committed_amt
, plan_curr_recal_committed_amt
, univ_curr_planned_amt
, univ_curr_committed_amt
, univ_curr_recal_committed_amt
FROM ozf_object_fund_summary
WHERE object_type = p_object_type
AND object_id = p_object_id
AND fund_id = p_fund_id;
IF p_mode = 'UPDATE' THEN
init_act_budgets_rec (l_temp_in_rec);
IF p_mode = 'UPDATE' THEN
complete_act_budgets_rec (l_temp_in_rec, l_temp_rec);
IF p_mode = 'UPDATE' THEN
IF l_temp_rec.transfer_type = 'REQUEST' THEN
-- R12: yzhao ozf_object_fund_summary decrease planned amount
l_objfundsum_rec.planned_amt := NVL(l_objfundsum_rec.planned_amt, 0) - NVL (l_fund_rec.planned_amt, 0);
ozf_utility_pvt.debug_message ('bef update fund ');
ozf_funds_pvt.update_fund (
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
,x_return_status=> l_return_status
,x_msg_count=> x_msg_count
,x_msg_data=> x_msg_data
,p_fund_rec=> l_fund_rec
,p_mode=> g_cons_fund_mode
,p_resale_batch_id => p_resale_batch_id
,p_batch_type => p_batch_type
,p_use_fund_staging_tables => p_use_fund_staging_tables
,px_ozf_funds_old_rectype => px_ozf_funds_old_rectype
,px_ozf_funds_new_tbl => px_ozf_funds_new_tbl -- For TPA Parallel Execution ER Bug#9614703
);
ozf_objfundsum_pvt.update_objfundsum(
p_api_version => 1.0,
p_init_msg_list => Fnd_Api.G_FALSE,
p_validation_level => Fnd_Api.G_VALID_LEVEL_NONE,
p_objfundsum_rec => l_objfundsum_rec,
p_conv_date => p_act_budget_rec.exchange_rate_date, --bug 7425189, 8532055
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
update_reconcile_objfundsum(
p_api_version => 1.0,
p_init_msg_list => Fnd_Api.G_FALSE,
p_validation_level => Fnd_Api.G_VALID_LEVEL_NONE,
p_objfundsum_rec => l_objfundsum_rec,
p_conv_date => p_act_budget_rec.exchange_rate_date,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ozf_objfundsum_pvt.update_objfundsum(
p_api_version => 1.0,
p_init_msg_list => Fnd_Api.G_FALSE,
p_validation_level => Fnd_Api.G_VALID_LEVEL_NONE,
p_objfundsum_rec => l_objfundsum_rec,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT object_version_number, currency_code_tc, planned_amt
FROM ozf_funds_all_b
WHERE fund_id = l_fund_id;
SELECT objfundsum_id
, object_version_number
, planned_amt
, plan_curr_planned_amt
, univ_curr_planned_amt
FROM ozf_object_fund_summary
WHERE object_type = p_object_type
AND object_id = p_object_id
AND fund_id = p_fund_id;
ozf_objfundsum_pvt.update_objfundsum(
p_api_version => 1.0,
p_init_msg_list => Fnd_Api.G_FALSE,
p_validation_level => Fnd_Api.G_VALID_LEVEL_NONE,
p_objfundsum_rec => l_objfundsum_rec,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ozf_funds_pvt.update_fund (
p_api_version=> 1.0
,p_init_msg_list=> fnd_api.g_false
, -- allow the calling API to handle
p_commit=> fnd_api.g_false
, -- allow the calling API to handle
p_validation_level=> fnd_api.g_valid_level_full
,x_return_status=> l_return_status
,x_msg_count=> x_msg_count
,x_msg_data=> x_msg_data
,p_fund_rec=> l_fund_rec
,p_mode=> g_cons_fund_mode
);
SELECT fund_id
,fund_currency
,NVL(committed_amt,0)-NVL(utilized_amt,0) total_amount
,NVL(univ_curr_committed_amt,0) total_acctd_amount
FROM ozf_object_fund_summary
WHERE object_id =p_object_id
AND object_type = p_obj_type;
SELECT SUM(NVL(univ_curr_committed_amt,0))
FROM ozf_object_fund_summary
WHERE object_id =p_object_id
AND object_type = p_obj_type;
PROCEDURE update_reconcile_objfundsum (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
p_objfundsum_rec IN OZF_OBJFUNDSUM_PVT.objfundsum_rec_type,
p_conv_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_ACTBUDGETS_PVT';
L_API_NAME CONSTANT VARCHAR2(30) := 'update_reconcile_objfundsum';
SAVEPOINT sp_update_reconcile_objfundsum;
ozf_utility_pvt.debug_message(l_full_name ||': update object fund summary Table');
UPDATE ozf_object_fund_summary
SET object_version_number= object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = Fnd_Global.User_ID,
last_update_login = Fnd_Global.Conc_Login_ID,
fund_id = l_objfundsum_rec.fund_id,
fund_currency = l_objfundsum_rec.fund_currency,
object_type = l_objfundsum_rec.object_type,
object_id = l_objfundsum_rec.object_id,
object_currency = l_objfundsum_rec.object_currency,
reference_object_type = l_objfundsum_rec.reference_object_type,
reference_object_id = l_objfundsum_rec.reference_object_id,
source_from_parent = l_objfundsum_rec.source_from_parent,
planned_amt = l_objfundsum_rec.planned_amt,
committed_amt = l_objfundsum_rec.committed_amt,
recal_committed_amt = l_objfundsum_rec.recal_committed_amt,
utilized_amt = l_objfundsum_rec.utilized_amt,
earned_amt = l_objfundsum_rec.earned_amt,
paid_amt = l_objfundsum_rec.paid_amt,
plan_curr_planned_amt = l_objfundsum_rec.plan_curr_planned_amt,
plan_curr_committed_amt = l_objfundsum_rec.plan_curr_committed_amt,
plan_curr_recal_committed_amt = l_objfundsum_rec.plan_curr_recal_committed_amt,
plan_curr_utilized_amt = l_objfundsum_rec.plan_curr_utilized_amt,
plan_curr_earned_amt = l_objfundsum_rec.plan_curr_earned_amt,
plan_curr_paid_amt = l_objfundsum_rec.plan_curr_paid_amt,
univ_curr_planned_amt = l_objfundsum_rec.univ_curr_planned_amt,
univ_curr_committed_amt = l_objfundsum_rec.univ_curr_committed_amt,
univ_curr_recal_committed_amt = l_objfundsum_rec.univ_curr_recal_committed_amt,
univ_curr_utilized_amt = l_objfundsum_rec.univ_curr_utilized_amt,
univ_curr_earned_amt = l_objfundsum_rec.univ_curr_earned_amt,
univ_curr_paid_amt = l_objfundsum_rec.univ_curr_paid_amt,
attribute_category = l_objfundsum_rec.attribute_category,
attribute1 = l_objfundsum_rec.attribute1,
attribute2 = l_objfundsum_rec.attribute2,
attribute3 = l_objfundsum_rec.attribute3,
attribute4 = l_objfundsum_rec.attribute4,
attribute5 = l_objfundsum_rec.attribute5,
attribute6 = l_objfundsum_rec.attribute6,
attribute7 = l_objfundsum_rec.attribute7,
attribute8 = l_objfundsum_rec.attribute8,
attribute9 = l_objfundsum_rec.attribute9,
attribute10 = l_objfundsum_rec.attribute10,
attribute11 = l_objfundsum_rec.attribute11,
attribute12 = l_objfundsum_rec.attribute12,
attribute13 = l_objfundsum_rec.attribute13,
attribute14 = l_objfundsum_rec.attribute14,
attribute15 = l_objfundsum_rec.attribute15
WHERE objfundsum_id = l_objfundsum_rec.objfundsum_id
AND object_version_number = l_objfundsum_rec.object_version_number;
ROLLBACK TO SP_update_reconcile_objfundsum;
ROLLBACK TO SP_update_reconcile_objfundsum;
ROLLBACK TO SP_update_reconcile_objfundsum;
END update_reconcile_objfundsum;