[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT system_status_code
FROM ams_user_statuses_vl
WHERE user_status_id = p_user_status_id
AND system_status_type = 'OZF_FUND_STATUS'
AND enabled_flag = 'Y';
SELECT user_status_id
FROM ams_user_statuses_vl
WHERE UPPER(system_status_code) = UPPER(p_status_code)
AND system_status_type = 'OZF_FUND_STATUS'
AND enabled_flag = 'Y';
SELECT COUNT(fund_id)
FROM ozf_funds_all_b
WHERE parent_fund_id = p_fund_id;
SELECT fund_number
FROM ozf_funds_all_b
WHERE fund_id = p_fund_id;
SELECT fund_type FROM ozf_funds_all_b
WHERE fund_id = p_fund_id;
ELSIF p_event_type = 'UPDATE' THEN
l_event_name := 'oracle.apps.ozf.fund.budget.update';
SELECT ozf_funds_s.nextval
FROM dual;
SELECT COUNT(fund_id)
FROM ozf_funds_all_b
WHERE fund_id = cv_fund_id;
SELECT count(fund_id) from ozf_funds_all_b
WHERE fund_number = cl_fund_number;
ozf_utility_pvt.debug_message(l_full_name || ': insert');
INSERT INTO ozf_funds_all_b
(
fund_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
created_from,
request_id,
program_application_id,
program_id,
program_update_date,
fund_number,
parent_fund_id,
category_id,
fund_type,
fund_usage, -- obsolete
status_code,
user_status_id,
status_date,
accrued_liable_account,
ded_adjustment_account,
liability_flag,
set_of_books_id, -- obsolete
start_period_id, -- obsolete
end_period_id, -- obsolete
start_date_active,
end_date_active,
budget_amount_tc, -- obsolete
budget_amount_fc, -- obsolete
available_amount, -- obsolete
distributed_amount, -- obsolete
currency_code_tc,
currency_code_fc, -- obsolete
exchange_rate_type, -- obsolete
exchange_rate_date, -- obsolete
exchange_rate, -- obsolete
department_id, -- obsolete
costcentre_id, -- obsolete
owner,
accrual_method,
accrual_operand,
accrual_rate,
accrual_basis,
hierarchy,
hierarchy_level,
hierarchy_id,
parent_node_id,
node_id, --,level_value
budget_flag,
earned_flag,
apply_accrual_on, -- obsolete
accrual_phase,
accrual_cap,
accrual_uom,
object_version_number,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
org_id,
original_budget,
transfered_in_amt,
transfered_out_amt,
holdback_amt,
planned_amt,
committed_amt,
earned_amt,
paid_amt,
plan_type, -- obsolete
plan_id, -- obsolete
liable_accnt_segments, -- obsolete
adjustment_accnt_segments, -- obsolete
fund_calendar,
start_period_name,
end_period_name,
accrual_quantity,
accrue_to_level_id,
accrual_discount_level,
custom_setup_id,
threshold_id,
business_unit_id,
country_id,
task_id,
rollup_original_budget,
rollup_transfered_in_amt,
rollup_transfered_out_amt,
rollup_holdback_amt,
retroactive_flag,
qualifier_id,
-- niprakas added
prev_fund_id,
transfered_flag,
utilized_amt,
rollup_utilized_amt,
product_spread_time_id,
ledger_id -- kdass - R12 MOAC changes
)
VALUES(
l_fund_rec.fund_id
,SYSDATE -- LAST_UPDATE_DATE
,NVL(fnd_global.user_id, -1) -- LAST_UPDATED_BY
,NVL(fnd_global.conc_login_id, -1) -- LAST_UPDATE_LOGIN
,SYSDATE -- CREATION_DATE
,NVL(fnd_global.user_id, -1) -- CREATED_BY
,NULL -- l_fund_rec.created_from -- CREATED_FROM -- we donot use this column
,fnd_global.conc_request_id -- REQUEST_ID
,fnd_global.prog_appl_id -- PROGRAM_APPLICATION_ID
,fnd_global.conc_program_id -- PROGRAM_ID
,SYSDATE -- PROGRAM_UPDATE_DATE
,l_fund_rec.fund_number
,l_fund_rec.parent_fund_id
,l_fund_rec.category_id
,l_fund_rec.fund_type
,l_fund_rec.fund_usage
,l_fund_rec.status_code
,l_fund_rec.user_status_id
,NVL(l_fund_rec.status_date, SYSDATE)
,l_fund_rec.accrued_liable_account
,l_fund_rec.ded_adjustment_account
,NVL(l_fund_rec.liability_flag, 'N')
,l_fund_rec.set_of_books_id
,l_fund_rec.start_period_id
,l_fund_rec.end_period_id
,NVL(l_fund_rec.start_date_active, SYSDATE)
,l_fund_rec.end_date_active
,l_fund_rec.budget_amount_tc
,l_fund_rec.budget_amount_fc
,l_fund_rec.available_amount
,l_fund_rec.distributed_amount
,l_fund_rec.currency_code_tc
,l_fund_rec.currency_code_fc
,l_fund_rec.exchange_rate_type
,l_fund_rec.exchange_rate_date
,l_fund_rec.exchange_rate
,l_fund_rec.department_id
,l_fund_rec.costcentre_id
,NVL(l_fund_rec.owner, NVL(fnd_global.user_id, -1)) -- OWNER
,l_fund_rec.accrual_method
,l_fund_rec.accrual_operand
,l_fund_rec.accrual_rate
,l_fund_rec.accrual_basis
,l_fund_rec.hierarchy
,l_fund_rec.hierarchy_level
,l_fund_rec.hierarchy_id
,l_fund_rec.parent_node_id
,l_fund_rec.node_id --,l_fund_rec.level_value
,NVL(l_fund_rec.budget_flag, 'N')
,NVL(l_fund_rec.earned_flag, 'N')
,l_fund_rec.apply_accrual_on
,l_fund_rec.accrual_phase
,l_fund_rec.accrual_cap
,l_fund_rec.accrual_uom
,l_object_version_number -- OBJECT_VERSION_NUMBER
,l_fund_rec.attribute_category
,l_fund_rec.attribute1
,l_fund_rec.attribute2
,l_fund_rec.attribute3
,l_fund_rec.attribute4
,l_fund_rec.attribute5
,l_fund_rec.attribute6
,l_fund_rec.attribute7
,l_fund_rec.attribute8
,l_fund_rec.attribute9
,l_fund_rec.attribute10
,l_fund_rec.attribute11
,l_fund_rec.attribute12
,l_fund_rec.attribute13
,l_fund_rec.attribute14
,l_fund_rec.attribute15
--,TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10)) -- org_id
,l_fund_rec.org_id -- kdass - R12 MOAC changes
,l_fund_rec.original_budget
,l_fund_rec.transfered_in_amt
,l_fund_rec.transfered_out_amt
,l_fund_rec.holdback_amt
,l_fund_rec.planned_amt
,l_fund_rec.committed_amt
,l_fund_rec.earned_amt
,l_fund_rec.paid_amt
,l_fund_rec.plan_type
,l_fund_rec.plan_id
,l_fund_rec.liable_accnt_segments
,l_fund_rec.adjustment_accnt_segments
,l_fund_rec.fund_calendar
,l_fund_rec.start_period_name
,l_fund_rec.end_period_name
,l_fund_rec.accrual_quantity
,l_fund_rec.accrue_to_level_id
,l_fund_rec.accrual_discount_level
,l_fund_rec.custom_setup_id
,l_fund_rec.threshold_id
,l_fund_rec.business_unit_id
,l_fund_rec.country_id
,l_fund_rec.task_id
,l_fund_rec.rollup_original_budget
,l_fund_rec.rollup_transfered_in_amt
,l_fund_rec.rollup_transfered_out_amt
,l_fund_rec.rollup_holdback_amt
,l_fund_rec.retroactive_flag
,l_fund_rec.qualifier_id
-- niprakas added
,l_fund_rec.prev_fund_id
,l_fund_rec.transfered_flag
,l_fund_rec.utilized_amt
,l_fund_rec.rollup_utilized_amt
,l_fund_rec.product_spread_time_id
,l_fund_rec.ledger_id
);
INSERT INTO ozf_funds_all_tl
(fund_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
created_from,
request_id,
program_application_id,
program_id,
program_update_date,
short_name,
description,
source_lang,
language,
org_id )
SELECT l_fund_rec.fund_id
,SYSDATE -- LAST_UPDATE_DATE
,NVL(fnd_global.user_id, -1) -- LAST_UPDATED_BY
,NVL(fnd_global.conc_login_id, -1) -- LAST_UPDATE_LOGIN
,SYSDATE -- CREATION_DATE
,NVL(fnd_global.user_id, -1) -- CREATED_BY
,NULL -- CREATED_FROM
,fnd_global.conc_request_id -- REQUEST_ID
,fnd_global.prog_appl_id -- PROGRAM_APPLICATION_ID
,fnd_global.conc_program_id -- PROGRAM_ID
,SYSDATE -- PROGRAM_UPDATE_DATE
,l_fund_rec.short_name
,l_fund_rec.description
,USERENV('LANG')
,l.language_code
--,TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10))
,l_fund_rec.org_id -- kdass - R12 MOAC changes
FROM fnd_languages l
WHERE l.installed_flag IN('I', 'B')
AND NOT EXISTS(SELECT NULL
FROM ozf_funds_all_tl t
WHERE t.fund_id = l_fund_rec.fund_id
AND t.language = l.language_code);
ozf_utility_pvt.debug_message(l_full_name || ': insert object attribute');
ozf_utility_pvt.debug_message(l_full_name || ': insert Functional currency record');
ozf_mc_transactions_pvt.insert_mc_transactions(
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 => x_msg_count
,x_msg_data => x_msg_data
,p_mc_transactions_rec => l_mc_transaction_rec
,x_mc_transaction_id => l_mc_transaction_id);
ozf_utility_pvt.debug_message(l_full_name || ': insert access owner');
ozf_utility_pvt.debug_message(l_full_name || ': insert access parent');
update_rollup_amount(
p_api_version => l_api_version
,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
);
/* move this part to update_rollup_amount by feliu.
OPEN c_par_fund_owner(l_fund_rec.parent_fund_id);
update_funds_access(
p_api_version => l_api_version
,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 => 'CREATE'
);
PROCEDURE delete_fund(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_fund_id IN NUMBER
,p_object_version IN NUMBER)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Fund';
SAVEPOINT delete_fund;
ozf_utility_pvt.debug_message(l_full_name || ': delete');
DELETE
FROM ozf_funds_all_b
WHERE fund_id = p_fund_id
AND object_version_number = p_object_version;
DELETE
FROM ozf_funds_all_tl
WHERE fund_id = p_fund_id;
ROLLBACK TO delete_fund;
ROLLBACK TO delete_fund;
ROLLBACK TO delete_fund;
END delete_fund;
SELECT fund_id
FROM ozf_funds_all_b
WHERE fund_id = p_fund_id
AND object_version_number = p_object_version
FOR UPDATE OF fund_id NOWAIT;
SELECT fund_id
FROM ozf_funds_all_tl
WHERE fund_id = p_fund_id
AND USERENV('LANG') IN(language, source_lang)
FOR UPDATE OF fund_id NOWAIT;
PROCEDURE update_fund(
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_fund_rec IN fund_rec_type
,p_mode IN VARCHAR2 := jtf_plsql_api.g_update
)
IS
l_resale_batch_id NUMBER;
update_fund(
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_fund_rec => p_fund_rec
,p_mode => p_mode
,p_resale_batch_id => l_resale_batch_id
,p_batch_type => l_batch_type
,p_use_fund_staging_tables => l_use_fund_staging_tables
,px_ozf_funds_old_rectype => lx_ozf_funds_old_rectype
,px_ozf_funds_new_tbl => lx_ozf_funds_new_tbl
);
END update_fund;
PROCEDURE update_fund(
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_fund_rec IN fund_rec_type
,p_mode IN VARCHAR2 := jtf_plsql_api.g_update
,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
)
IS
l_api_version CONSTANT NUMBER := 1.0;
:= 'Update_Fund';
SELECT status_code
,parent_fund_id
,user_status_id
FROM ozf_funds_all_b
WHERE fund_id = cv_fund_id;
SELECT owner
FROM ozf_funds_all_b
WHERE fund_id = par_fund_id;
SELECT mc_record_id
,object_version_number
FROM ozf_mc_transactions_all
WHERE source_object_id = source_id
AND source_object_name = 'FUND';
SELECT activity_access_id
,object_version_number
FROM ams_act_access
WHERE act_access_to_object_id = p_fund_id
AND arc_act_access_to_object = 'FUND'
AND owner_flag = 'Y';
SELECT activity_access_id
,object_version_number
FROM ams_act_access
WHERE act_access_to_object_id = p_fund_id
AND arc_act_access_to_object = 'FUND'
AND arc_user_or_role_type = 'USER'
AND user_or_role_id = p_par_owner_id
-- 09/05/2001 mpande
AND NVL(owner_flag,'N') = 'N' ;
SELECT currency_code_tc , owner
FROM ozf_funds_all_b
WHERE fund_id = cv_fund_id;
SELECT planned_amt,committed_amt,
earned_amt,paid_amt,transfered_in_amt
,transfered_out_amt,original_budget
,recal_committed,holdback_amt
,utilized_amt -- yzhao: 11.5.10
FROM ozf_funds_all_b
WHERE fund_id = p_fund_rec.fund_id;
SELECT rollup_planned_amt,rollup_committed_amt
,rollup_earned_amt,rollup_paid_amt,rollup_transfered_in_amt
,rollup_transfered_out_amt,rollup_original_budget
,rollup_recal_committed,rollup_holdback_amt
,rollup_utilized_amt -- yzhao: 11.5.10
FROM ozf_funds_all_b
WHERE fund_id = p_fund_rec.fund_id;
SELECT org_id
FROM ozf_funds_all_b
WHERE fund_id = p_fund_rec.fund_id;
SAVEPOINT update_fund;
,p_validation_mode => jtf_plsql_api.g_update
,x_return_status => l_return_status
,p_mode => p_mode); -- For Bug 13529250, to pass p_mode to avoid budget amount validation while claim settlement
,p_validation_mode => jtf_plsql_api.g_update
,x_return_status => l_return_status);
ozf_utility_pvt.debug_message(l_full_name || ': update');
ozf_utility_pvt.debug_message(l_full_name || 'before update status' ||l_fund_rec.original_budget);
IF p_mode IN (jtf_PLSQL_API.G_UPDATE, 'WORKFLOW') THEN
Ozf_fundrules_pvt.update_fund_status(
p_fund_rec => l_fund_rec
/* yzhao: 11/26/2002 how weird to pass IN parameter l_fund_rec, and use member as OUT parameter
most importantly, it breaks with NOCOPY hint
,x_new_status_code => l_fund_rec.status_code
,x_new_status_id => l_fund_rec.user_status_id
*/
,x_new_status_code => l_tmp_status_code
,x_new_status_id => l_tmp_status_id
,x_submit_budget_approval => l_submit_budget_approval
,x_submit_child_approval =>l_submit_child_approval
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_api_version => 1.0);
update_funds_access(
p_api_version => l_api_version
,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 => 'DELETE'
);
update_funds_access(
p_api_version => l_api_version
,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 => 'DELETE'
);
UPDATE ozf_funds_all_tl
SET last_update_date = SYSDATE
,last_updated_by = NVL(fnd_global.user_id, -1)
,last_update_login = NVL(fnd_global.conc_login_id, -1)
,created_from = NULL
,request_id = fnd_global.conc_request_id
,program_application_id = fnd_global.prog_appl_id
,program_id = fnd_global.conc_program_id
,program_update_date = SYSDATE
,short_name = l_fund_rec.short_name
,description = l_fund_rec.description
,source_lang = USERENV('LANG')
WHERE fund_id = l_fund_rec.fund_id
AND USERENV('LANG') IN(language, source_lang);
UPDATE ozf_funds_all_b
SET last_update_date = SYSDATE
,last_updated_by = NVL(fnd_global.user_id, -1)
,last_update_login = NVL(fnd_global.conc_login_id, -1)
,created_from = NULL
,request_id = fnd_global.conc_request_id
,program_application_id = fnd_global.prog_appl_id
,program_id = fnd_global.conc_program_id
,program_update_date = SYSDATE
,fund_number = l_fund_rec.fund_number
,parent_fund_id = l_fund_rec.parent_fund_id
,category_id = l_fund_rec.category_id
,fund_type = l_fund_rec.fund_type
,fund_usage = l_fund_rec.fund_usage
,status_code = l_fund_rec.status_code
,user_status_id = l_fund_rec.user_status_id
,status_date = NVL(l_fund_rec.status_date, SYSDATE)
,accrued_liable_account = l_fund_rec.accrued_liable_account
,ded_adjustment_account = l_fund_rec.ded_adjustment_account
,liability_flag = l_fund_rec.liability_flag
,set_of_books_id = l_fund_rec.set_of_books_id
,start_period_id = l_fund_rec.start_period_id
,end_period_id = l_fund_rec.end_period_id
,start_date_active = l_fund_rec.start_date_active
,end_date_active = l_fund_rec.end_date_active
,budget_amount_tc = l_fund_rec.budget_amount_tc
,budget_amount_fc = l_fund_rec.budget_amount_fc
,available_amount = l_fund_rec.available_amount
,distributed_amount = l_fund_rec.distributed_amount
,currency_code_tc = l_fund_rec.currency_code_tc
,currency_code_fc = l_fund_rec.currency_code_fc
,exchange_rate_type = l_fund_rec.exchange_rate_type
,exchange_rate_date = l_fund_rec.exchange_rate_date
,exchange_rate = l_fund_rec.exchange_rate
,department_id = l_fund_rec.department_id
,costcentre_id = l_fund_rec.costcentre_id
,owner = l_fund_rec.owner
,accrual_method = l_fund_rec.accrual_method
,accrual_operand = l_fund_rec.accrual_operand
,accrual_rate = l_fund_rec.accrual_rate
,accrual_basis = l_fund_rec.accrual_basis
,hierarchy = l_fund_rec.hierarchy
,hierarchy_level = l_fund_rec.hierarchy_level
,hierarchy_id = l_fund_rec.hierarchy_id
,parent_node_id = l_fund_rec.parent_node_id
,node_id = l_fund_rec.node_id --,level_value = l_fund_rec.level_value
,budget_flag = l_fund_rec.budget_flag
,earned_flag = l_fund_rec.earned_flag
,apply_accrual_on = l_fund_rec.apply_accrual_on
,accrual_phase = l_fund_rec.accrual_phase
,accrual_cap = l_fund_rec.accrual_cap
,accrual_uom = l_fund_rec.accrual_uom
,object_version_number = l_fund_rec.object_version_number + 1
,recal_committed = l_fund_rec.recal_committed
,attribute_category = l_fund_rec.attribute_category
,attribute1 = l_fund_rec.attribute1
,attribute2 = l_fund_rec.attribute2
,attribute3 = l_fund_rec.attribute3
,attribute4 = l_fund_rec.attribute4
,attribute5 = l_fund_rec.attribute5
,attribute6 = l_fund_rec.attribute6
,attribute7 = l_fund_rec.attribute7
,attribute8 = l_fund_rec.attribute8
,attribute9 = l_fund_rec.attribute9
,attribute10 = l_fund_rec.attribute10
,attribute11 = l_fund_rec.attribute11
,attribute12 = l_fund_rec.attribute12
,attribute13 = l_fund_rec.attribute13
,attribute14 = l_fund_rec.attribute14
,attribute15 = l_fund_rec.attribute15
,original_budget = l_fund_rec.original_budget
,transfered_in_amt = l_fund_rec.transfered_in_amt
,transfered_out_amt = l_fund_rec.transfered_out_amt
,holdback_amt = l_fund_rec.holdback_amt
,planned_amt = l_fund_rec.planned_amt
,committed_amt = l_fund_rec.committed_amt
,earned_amt = l_fund_rec.earned_amt
,paid_amt = l_fund_rec.paid_amt
,plan_type = l_fund_rec.plan_type
,plan_id = l_fund_rec.plan_id
,liable_accnt_segments = l_fund_rec.liable_accnt_segments
,adjustment_accnt_segments = l_fund_rec.adjustment_accnt_segments
,fund_calendar = l_fund_rec.fund_calendar
,start_period_name = l_fund_rec.start_period_name
,end_period_name = l_fund_rec.end_period_name
,accrual_quantity = l_fund_rec.accrual_quantity
,accrue_to_level_id = l_fund_rec.accrue_to_level_id
,accrual_discount_level = l_fund_rec.accrual_discount_level
,custom_setup_id = l_fund_rec.custom_setup_id
,threshold_id = l_fund_rec.threshold_id
,business_unit_id = l_fund_rec.business_unit_id
,country_id = l_fund_rec.country_id
,task_id = l_fund_rec.task_id
,rollup_original_budget = l_fund_rec.rollup_original_budget
,rollup_transfered_in_amt = l_fund_rec.rollup_transfered_in_amt
,rollup_transfered_out_amt = l_fund_rec.rollup_transfered_out_amt
,rollup_holdback_amt = l_fund_rec.rollup_holdback_amt
,rollup_planned_amt = l_fund_rec.rollup_planned_amt
,rollup_committed_amt = l_fund_rec.rollup_committed_amt
,rollup_earned_amt = l_fund_rec.rollup_earned_amt
,rollup_paid_amt = l_fund_rec.rollup_paid_amt
,rollup_recal_committed = l_fund_rec.rollup_recal_committed
,retroactive_flag = l_fund_rec.retroactive_flag
,qualifier_id = l_fund_rec.qualifier_id
-- niprakas added
,prev_fund_id = l_fund_rec.prev_fund_id
,transfered_flag = l_fund_rec.transfered_flag
,utilized_amt = l_fund_rec.utilized_amt
,rollup_utilized_amt = l_fund_rec.rollup_utilized_amt
,product_spread_time_id = l_fund_rec.product_spread_time_id
-- sangara added
,activation_date = l_fund_rec.activation_date
-- kdass - R12 MOAC changes
,ledger_id = l_fund_rec.ledger_id
WHERE fund_id = l_fund_rec.fund_id
AND object_version_number = l_fund_rec.object_version_number;
l_operation := 'INSERT';
l_operation := 'UPDATE';
IF l_operation = 'UPDATE' THEN
IF G_DEBUG THEN
ozf_utility_pvt.debug_message('fund_id: ' || px_ozf_funds_new_tbl(l_fund_record_num).FUND_ID);
ELSIF l_operation = 'INSERT' THEN
l_fund_record_num := px_ozf_funds_new_tbl.COUNT + 1;
ozf_utility_pvt.debug_message('Going to update mode of update_rollup_amount as l_use_fund_staging_tables is :'||l_use_fund_staging_tables );
update_rollup_amount(
p_api_version => l_api_version
,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
);
update_funds_access(
p_api_version => l_api_version
,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 => 'CREATE'
);
l_mode := 'UPDATE' ; -- when not creating act_budgets
IF l_mode IN ('ACTIVE' , 'UPDATE' ) THEN
ozf_fundrules_pvt.process_approval(
p_fund_rec => l_fund_rec
,p_mode => l_mode
,p_old_fund_status => l_old_status
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_api_version => 1.0);
raise_business_event(p_object_id => p_fund_rec.fund_id ,p_event_type =>'UPDATE');
was removed from this place and put in update_fund_status APIS
The code was removed and not commented because of clarity and cleanliness. Please refer to
earlier versions for bug fixes etc. in releases prior to 11.5.5. (hornet)
**********************************************************************************************/
-- reinitialize the variables
l_act_access_id := NULL;
ozf_utility_pvt.debug_message(l_full_name || ': insert access owner');
ams_access_pvt.update_access(
p_api_version => l_api_version
,p_init_msg_list => 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_commit => fnd_api.g_false
,p_access_rec => l_access_rec);
AMS_Access_PVT.update_object_owner
( 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 => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_object_type => 'FUND'
,p_object_id => l_fund_rec.fund_id
,p_resource_id => l_fund_rec.owner
,p_old_resource_id => l_owner
);
update_funds_access(
p_api_version => l_api_version
,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 => 'CREATE'
);
update_funds_access(
p_api_version => l_api_version
,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 => 'CREATE'
);
ozf_utility_pvt.debug_message(l_full_name ||': insert FC record' ||l_fund_rec.currency_code_tc);
ozf_mc_transactions_pvt.update_mc_transactions(
p_api_version => l_api_version
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
-- 01/13/2003 yzhao fix bug BUG 2750841(same as 2741039) pass in org_id
,p_org_id => l_fund_rec.org_id
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_mc_transactions_rec => l_mc_transaction_rec);
ROLLBACK TO update_fund;
ROLLBACK TO update_fund;
ROLLBACK TO update_fund;
END update_fund;
SELECT 1 from ozf_funds_all_b
WHERE fund_number = p_fund_rec.fund_number;
CURSOR c_prog_fund_number_update
IS
SELECT 1 from ozf_funds_all_b
WHERE fund_number = p_fund_rec.fund_number
AND fund_id <> p_fund_rec.fund_id;
OPEN c_prog_fund_number_update;
FETCH c_prog_fund_number_update INTO l_valid_flag;
CLOSE c_prog_fund_number_update;
SELECT status_code, original_budget
FROM ozf_funds_all_b
WHERE fund_id = cv_fund_id;
SELECT org_id
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;
/* -- Budget Amount cannot be updated for an active budget. #3570045 -- reverted change.
IF p_complete_rec.status_code = 'ACTIVE' THEN
OPEN c_old_status(p_fund_rec.fund_id);
IF p_mode = jtf_plsql_api.g_update THEN
IF p_fund_rec.parent_fund_id <> fnd_api.g_miss_num
AND p_fund_rec.parent_fund_id IS NOT NULL THEN
IF p_complete_rec.fund_id = p_complete_rec.parent_fund_id THEN
IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
IF p_fund_rec.fund_type = 'QUOTA' THEN
fnd_message.set_name('OZF', 'OZF_TP_WRONG_PARENT');
IF p_mode = jtf_plsql_api.g_update THEN
-- mpande added on Sep 11 for giving update access to owner and persons who have access with edit metric flag = 'Y'
l_resource_id := ozf_utility_pvt.get_resource_id(p_user_id => fnd_global.user_id);
ozf_utility_pvt.debug_message('resource'||ams_access_pvt.check_update_access(p_complete_rec.fund_id, 'FUND', l_resource_id, 'USER'));
IF ams_access_pvt.check_update_access(
p_complete_rec.fund_id
,'FUND'
,l_resource_id
,'USER') <>
'F' THEN
IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
IF p_fund_rec.fund_type = 'QUOTA' THEN
fnd_message.set_name('OZF', 'OZF_TP_ILLEGAL_OWNER');
IF p_validation_mode = jtf_plsql_api.g_update THEN
Ozf_fundrules_pvt.check_fund_dates_vs_child(
p_complete_rec.fund_id
,p_complete_rec.start_date_active
,p_complete_rec.end_date_active
,l_return_status);
IF p_validation_mode = jtf_plsql_api.g_update THEN
-- updated 09/04/2001 mpande for Multi Currency Child
Ozf_fundrules_pvt.check_fund_amount_vs_child(
p_complete_rec.fund_id
,p_complete_rec.original_budget
,p_complete_rec.transfered_in_amt
,p_complete_rec.transfered_out_amt
,p_complete_rec.currency_code_tc
,l_return_status);
IF p_validation_mode = jtf_plsql_api.g_update
AND p_complete_rec.status_code = 'ACTIVE' THEN
Ozf_fundrules_pvt.check_fund_type_vs_child(
p_complete_rec.fund_id
,p_complete_rec.fund_type
,l_return_status);
IF p_validation_mode = jtf_plsql_api.g_update
AND p_complete_rec.status_code = 'ACTIVE' THEN
Ozf_fundrules_pvt.check_fund_curr_vs_child(
p_complete_rec.fund_id
,p_complete_rec.currency_code_tc
,l_return_status);
SELECT *
FROM ozf_funds_all_vl
WHERE fund_id = p_fund_rec.fund_id;
SELECT accrued_liability_account
,ded_adjustment_account
FROM ams_categories_vl
WHERE category_id = p_cat_id;
SELECT *
FROM ozf_funds_all_vl
WHERE fund_id = p_fund_id
;
SELECT user_status_id
FROM ams_user_statuses_b
WHERE system_status_type = p_status_type
AND system_status_code = p_status_code
AND default_flag = 'Y'
AND enabled_flag = 'Y'
;
SELECT custom_setup_id
FROM ozf_offers
WHERE qp_list_header_id = p_obj_id;
PROCEDURE update_rollup_amount(
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_fund_rec IN fund_rec_type
) IS
l_api_version CONSTANT NUMBER := 1.0;
:= 'update_rollup_amount';
SELECT fund_id
,object_version_number
,rollup_original_budget
,rollup_transfered_in_amt
,rollup_transfered_out_amt
,rollup_holdback_amt
,rollup_planned_amt
,rollup_committed_amt
,rollup_utilized_amt -- yzhao: 11.5.10
,rollup_earned_amt
,rollup_paid_amt
,rollup_recal_committed
FROM ozf_funds_all_b
connect by prior parent_fund_id =fund_id
start with fund_id = p_fund_rec.fund_id;
UPDATE ozf_funds_all_b
SET object_version_number = fund.object_version_number + 1
,rollup_original_budget = NVL(fund.rollup_original_budget,0) + NVL(p_fund_rec.rollup_original_budget,0)
,rollup_transfered_in_amt = NVL(fund.rollup_transfered_in_amt,0) + NVL(p_fund_rec.rollup_transfered_in_amt,0)
,rollup_transfered_out_amt = NVL(fund.rollup_transfered_out_amt,0) + NVL(p_fund_rec.rollup_transfered_out_amt,0)
,rollup_holdback_amt = NVL(fund.rollup_holdback_amt,0) + NVL(p_fund_rec.rollup_holdback_amt,0)
,rollup_planned_amt = NVL(fund.rollup_planned_amt,0)+ NVL(p_fund_rec.rollup_planned_amt,0)
,rollup_committed_amt = NVL(fund.rollup_committed_amt,0) + NVL(p_fund_rec.rollup_committed_amt,0)
,rollup_utilized_amt = NVL(fund.rollup_utilized_amt,0) + NVL(p_fund_rec.rollup_utilized_amt,0) -- yzhao: 11.5.10
,rollup_earned_amt = NVL(fund.rollup_earned_amt,0) + NVL(p_fund_rec.rollup_earned_amt,0)
,rollup_paid_amt = NVL(fund.rollup_paid_amt,0) + NVL(p_fund_rec.rollup_paid_amt,0)
,rollup_recal_committed = NVL(fund.rollup_recal_committed ,0)+ NVL(p_fund_rec.rollup_recal_committed,0)
WHERE fund_id = fund.fund_id
AND object_version_number = fund.object_version_number;
END update_rollup_amount;
PROCEDURE update_funds_access(
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_fund_rec IN fund_rec_type
,p_mode IN VARCHAR2 := JTF_PLSQL_API.G_CREATE
) IS
l_api_version CONSTANT NUMBER := 1.0;
:= 'update_funds_access';
SELECT fund_id,owner
FROM ozf_funds_all_b
connect by prior parent_fund_id =fund_id
start with fund_id = p_fund_rec.fund_id;
SELECT activity_access_id
,object_version_number
FROM ams_act_access
WHERE act_access_to_object_id = p_fund_id
AND arc_act_access_to_object = 'FUND'
AND arc_user_or_role_type = 'USER'
AND user_or_role_id = p_owner_id;
ams_access_pvt.delete_access(
p_api_version => l_api_version
,p_init_msg_list => 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_commit => fnd_api.g_false
,p_access_id => l_access_id
,p_object_version => l_acc_obj_ver_num);
END IF; -- end delete access mode.
END update_funds_access;