[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || UPPER(p_table_name);
l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || p_lookup_table_name;
SELECT 1 FROM fnd_lookup_values lkup
WHERE lkup.LOOKUP_TYPE = p_lookup_type
AND lkup.LOOKUP_CODE = p_lookup_code
AND lkup.view_application_id = p_view_app_id
AND lkup.ENABLED_FLAG = 'Y'
AND lkup.language = USERENV('LANG')
AND lkup.security_group_id = to_number(decode(substrb(userenv('CLIENT_INFO'),55,1
), ' ', '0'
, NULL, '0'
, substrb(userenv('CLIENT_INFO'),55,10
)
)
);
l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || UPPER(p_table_name);
SELECT ams_act_logs_s.NEXTVAL,
ams_act_logs_transaction_id_s.NEXTVAL
FROM DUAL;
SELECT rowid
FROM ams_act_logs
WHERE activity_log_id = l_my_log_id;
INSERT INTO ams_act_logs (
activity_log_id
-- standard who columns
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,object_version_number
,act_log_used_by_id
,arc_act_log_used_by
,log_transaction_id
,log_message_text
,log_message_level
,log_message_type
,description
,budget_id
,threshold_id
,notification_creation_date
)
VALUES (
NVL(p_activity_log_id,l_act_log_id)
-- standard who columns
,SYSDATE
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.User_Id
,FND_GLOBAL.Conc_Login_Id
,1 -- Object Version Number
,p_log_used_by_id
,p_arc_log_used_by
,NVL(p_transaction_id,l_log_tran_id)
,p_msg_data
,p_msg_level
,p_msg_type
,p_desc
,p_budget_id
,p_threshold_id
,p_notification_creat_date
) ;
SELECT source_code,source_code_for_id INTO x_source_code,x_source_id
FROM ams_source_codes
WHERE arc_source_code_for = UPPER(p_activity_type)
AND source_code_for_id = UPPER(p_activity_id);
SELECT campaign_name
FROM ams_campaigns_vl
WHERE campaign_id = p_object_id;
SELECT c.campaign_name
FROM ams_campaigns_vl c,
ams_campaign_schedules s
WHERE s.campaign_schedule_id = p_object_id
AND s.campaign_id = c.campaign_id;
SELECT deliverable_name
FROM ams_deliverables_vl
WHERE deliverable_id = p_object_id;
SELECT event_header_name
FROM ams_event_headers_vl
WHERE event_header_id = p_object_id;
SELECT event_offer_name
FROM ams_event_offers_vl
WHERE event_offer_id = p_object_id;
SELECT short_name
FROM ozf_funds_all_vl
WHERE fund_id = p_object_id;
SELECT party_name
FROM hz_parties
WHERE party_id = p_object_id;
SELECT meaning
FROM ozf_lookups
WHERE lookup_type = UPPER (p_lookup_type)
AND lookup_code = UPPER (p_lookup_code);
select NAME
from HZ_TIMEZONES_VL
where TIMEZONE_ID = l_time_id;
select NAME
from HZ_TIMEZONES_VL
where TIMEZONE_ID = l_time_id;
SELECT meaning
FROM ozf_lookups
WHERE lookup_type = UPPER (p_lookup_type)
AND lookup_code = UPPER (p_lookup_code);
SELECT full_name
FROM ams_jtf_rs_emp_v
WHERE resource_id = p_resource_id;
SELECT area2_code
FROM jtf_loc_hierarchies_vl
WHERE location_hierarchy_id = p_country_id;
SELECT B.area2_code
FROM ams_campaigns_vl A, jtf_loc_hierarchies_vl B
WHERE A.campaign_id = p_object_id
AND A.city_id = B.location_hierarchy_id;
SELECT padded_concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_prod_id
AND organization_id = p_org_id;
SELECT DISTINCT padded_concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_prod_id;
SELECT description
FROM mtl_categories_v
WHERE category_id = p_prod_id;
SELECT qlh.name
FROM qp_list_headers_vl qlh, qp_list_lines qll
WHERE qll.list_header_id = qlh.list_header_id
AND qll.list_line_id = p_price_list_line_id;
SELECT unit_of_measure
FROM mtl_units_of_measure
WHERE uom_code = p_uom_code;
SELECT meaning
FROM qp_lookups
WHERE lookup_type = UPPER(p_lookup_type)
AND lookup_code = UPPER(p_lookup_code);
SELECT resource_id
FROM ams_jtf_rs_emp_v
WHERE user_id = p_user_id;
SELECT system_status_code
FROM ams_user_statuses_vl
WHERE user_status_id = p_user_status_id
AND enabled_flag = 'Y';
SELECT user_status_id
FROM ams_user_statuses_vl
WHERE system_status_type = p_status_type
AND system_status_code = p_status_code
AND default_flag = 'Y'
AND enabled_flag = 'Y';
SELECT theme_approval_flag, budget_approval_flag
FROM ams_status_order_rules
WHERE system_status_type = l_status_type
AND current_status_code = l_old_status_code
AND next_status_code = l_new_status_code;
SELECT attr_available_flag
FROM ams_custom_setup_attr
WHERE custom_setup_id = p_custom_setup_id
-- Following line is added by ptendulk on 19-Jun-2001
AND object_attribute = p_approval_type ;
SELECT theme_approval_flag, budget_approval_flag
FROM ams_status_order_rules
WHERE system_status_type = l_status_type
AND current_status_code = l_old_status_code
AND next_status_code = l_new_status_code;
SELECT gs.set_of_books_id
,gs.currency_code
FROM gl_sets_of_books gs
,org_organization_definitions org
WHERE gs.mrc_sob_type_code = 'P'
AND org.set_of_books_id = gs.set_of_books_id
AND org.operating_unit = p_org_id;
SELECT gs.set_of_books_id
, gs.currency_code
FROM gl_sets_of_books gs
, ozf_sys_parameters_all org
WHERE org.set_of_books_id = gs.set_of_books_id
AND NVL(org.org_id, -99) = NVL(p_org_id, -99);
SELECT gs.set_of_books_id
,gs.currency_code
FROM gl_sets_of_books gs
,org_organization_definitions org
WHERE org.set_of_books_id = gs.set_of_books_id
AND org.operating_unit = p_org_id;
SELECT gs.set_of_books_id
, gs.currency_code
FROM gl_sets_of_books gs
, ozf_sys_parameters_all org
WHERE org.set_of_books_id = gs.set_of_books_id
AND NVL(org.org_id, -99) = NVL(p_org_id, -99);
SELECT ledger_id
FROM ozf_funds_all_b
WHERE fund_id = p_fund_id;
SELECT org_id
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;
SELECT orig_org_id
FROM qp_list_headers_b
WHERE list_header_id = p_list_header_id;
SELECT budget_source_id, budget_source_type
FROM ozf_act_budgets
WHERE act_budget_used_by_id=p_list_header_id
AND transfer_type = 'REQUEST'; --added for bug 13744476
SELECT budget_source_id
FROM ozf_act_budgets
WHERE act_budget_used_by_id = p_campaign_id;
SELECT currency_code
FROM gl_ledgers_public_v
WHERE ledger_id = p_ledger_id;
SELECT employee_id , user_id, category
FROM ams_jtf_rs_emp_v
WHERE resource_id = p_resource_id ;
SELECT 1 FROM DUAL
WHERE EXISTS (SELECT * FROM ams_status_order_rules
WHERE current_status_code = p_current_status
AND next_status_code = p_next_status
AND system_status_type = p_status_type ) ;
SELECT deliverable_name
FROM ams_deliverables_vl
WHERE deliverable_id = p_delv_id;
SELECT user_id
FROM ams_jtf_rs_emp_v
WHERE resource_id = p_resource_id;
SELECT hp.party_name FROM hz_parties hp , hz_cust_accounts hca
WHERE hca.party_id = hp.party_id
AND hca.status = 'A'
AND hca.cust_account_id = p_cust_account_id;
SELECT name FROM ams_user_statuses_vl where user_status_id = p_user_status_id
AND system_status_type = p_system_status_type;
SELECT name FROM fnd_currencies_vl WHERE currency_code = p_currency_code;
SELECT media_name FROM ams_media_vl
WHERE media_id = p_media_id;
SELECT meaning FROM fnd_lookups WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code;
select rr.role_id
from jtf_rs_role_relations rr
, jtf_rs_roles_vl rl
where rr.role_id = rl.role_id
and rl.role_type_code = 'SALES'
and rl.role_code = 'SALES_REP'
and rr.delete_flag = 'N'
and TRUNC(sysdate) between TRUNC(rr.start_date_active)
and TRUNC(nvl(rr.end_date_active,sysdate))
and rr.role_resource_id = cv_resource_id;
SELECT SUM(NVL(plan_curr_committed_amt,0))
FROM ozf_object_fund_summary
WHERE object_id = list_header_id
AND object_type = 'OFFR';
SELECT SUM(AMOUNT)
FROM(
SELECT uti.plan_curr_amount amount
FROM ozf_funds_utilized_all_b uti, ozf_act_budgets act
WHERE uti.utilization_type ='REQUEST'
AND component_type = 'OFFR'
AND component_id = list_header_id
AND act.activity_budget_id = uti.ams_activity_budget_id
AND act.recal_flag is NULL
UNION ALL
SELECT 0-uti.plan_curr_amount amount
FROM ozf_funds_utilized_all_b uti, ozf_act_budgets act
WHERE uti.utilization_type ='TRANSFER'
AND plan_type = 'OFFR'
AND plan_id = list_header_id
AND act.activity_budget_id = uti.ams_activity_budget_id
AND act.recal_flag is NULL);
SELECT SUM(NVL(plan_curr_recal_committed_amt,0))
FROM ozf_object_fund_summary
WHERE object_id = list_header_id
AND object_type = 'OFFR';
SELECT SUM(AMOUNT)
FROM(
SELECT uti.plan_curr_amount amount
FROM ozf_funds_utilized_all_b uti
WHERE uti.utilization_type ='REQUEST'
AND component_type = 'OFFR'
AND component_id = list_header_id
UNION ALL
SELECT 0-uti.plan_curr_amount amount
FROM ozf_funds_utilized_all_b uti
WHERE uti.utilization_type ='TRANSFER'
AND plan_type = 'OFFR'
AND plan_id = list_header_id);
SELECT SUM(NVL(plan_curr_utilized_amt,0))
FROM ozf_object_fund_summary
WHERE object_id = list_header_id
AND object_type = 'OFFR';
SELECT SUM(uti.plan_curr_amount)
FROM ozf_funds_utilized_all_vl uti
WHERE uti.utilization_type IN
('UTILIZED','ACCRUAL','ADJUSTMENT','CHARGEBACK','LEAD_ACCRUAL')
AND plan_type = 'OFFR'
AND plan_id = list_header_id;
SELECT SUM(NVL(plan_curr_earned_amt,0))
FROM ozf_object_fund_summary
WHERE object_id = list_header_id
AND object_type = 'OFFR';
SELECT SUM(uti.plan_curr_amount)
FROM ozf_funds_utilized_all_vl uti
WHERE uti.utilization_type IN
('UTILIZED','ACCRUAL','ADJUSTMENT','CHARGEBACK','LEAD_ACCRUAL')
AND plan_type = 'OFFR'
AND plan_id = p_list_header_id
AND gl_posted_flag NOT in('N','F');
SELECT SUM(NVL(plan_curr_paid_amt,0))
FROM ozf_object_fund_summary
WHERE object_id = list_header_id
AND object_type = 'OFFR';
SELECT SUM(AMOUNT) FROM
(SELECT SUM(plan_curr_amount - NVL(plan_curr_amount_remaining,0)) amount
FROM ozf_funds_utilized_all_b util
WHERE utilization_type = 'UTILIZED'
AND NVL(util.gl_posted_flag,'Y') = 'Y'
AND plan_type = 'OFFR'
AND plan_id = list_header_id
UNION AlL
SELECT cuti.plan_curr_amount amount
FROM ozf_funds_utilized_all_b util,
ozf_claim_lines_util_all cuti,
ozf_claim_lines_all cln,
ozf_claims_all cla
WHERE util.utilization_id(+) = cuti.utilization_id
AND util.utilization_type IN ('ACCRUAL','ADJUSTMENT', 'CHARGEBACK', 'LEAD_ACCRUAL')
AND cuti.claim_line_id = cln.claim_line_id
AND cln.claim_id = cla.claim_id
AND cla.status_code = 'CLOSED'
AND util.plan_type = 'OFFR'
AND util.plan_id = list_header_id);
SELECT name
FROM hr_operating_units
WHERE organization_id = p_org_id;
SELECT name
FROM hr_all_organization_units_tl
WHERE organization_id = p_org_id
AND language = userenv('LANG');
SELECT ak.attribute_label_long
FROM ak_attributes_vl ak
WHERE ak.attribute_code = upper(cp_attributeCode)
AND ak.attribute_application_id = cp_applicationId;
SELECT parameter_value
FROM oe_sys_parameters_all
WHERE parameter_code = 'OE_DISCOUNT_DETAILS_ON_INVOICE'
AND org_id = p_org_id;
SELECT cgl.code_combination_id
FROM ozf_funds_utilized_all_b fu ,
oe_price_adjustments pa ,
oe_order_lines_all ol ,
oe_order_headers_all oh ,
ra_customer_trx_lines_all ctl ,
ra_cust_trx_line_gl_dist_all cgl
WHERE fu.price_adjustment_id = pa.price_adjustment_id
AND ol.line_id = pa.line_id
AND ol.header_id = oh.header_id
AND ctl.interface_line_attribute1 = TO_CHAR(oh.order_number)
--and ctl.sales_order_line = ol.line_number
AND ctl.interface_line_attribute6 = TO_CHAR(ol.line_id) --Fix for bug 15952083
AND cgl.customer_trx_line_id = ctl.customer_trx_line_id
AND cgl.account_class = 'REV'
AND fu.utilization_id = p_utilization_id
AND fu.order_line_id = ctl.interface_line_attribute6;
SELECT cgl.code_combination_id
FROM ozf_funds_utilized_all_b fu ,
oe_price_adjustments pa ,
oe_order_lines_all ol ,
oe_order_headers_all oh ,
ra_customer_trx_lines_all ctl ,
ra_cust_trx_line_gl_dist_all cgl
WHERE fu.price_adjustment_id = pa.price_adjustment_id
AND ol.line_id = pa.line_id
AND ol.header_id = oh.header_id
AND ctl.interface_line_attribute1 = TO_CHAR(oh.order_number)
--AND ctl.sales_order_line = ol.line_number
AND ctl.interface_line_attribute6 = TO_CHAR(ol.line_id) --Fix for bug 15952083
AND ctl.interface_line_attribute11 = TO_CHAR(pa.price_adjustment_id)
AND cgl.customer_trx_line_id = ctl.customer_trx_line_id
AND cgl.account_class = 'REV'
AND fu.utilization_id = p_utilization_id;
PROCEDURE UPDATE_OZF_ACT_BUDGETS (
p_batch_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_budget_update_status OUT NOCOPY VARCHAR2,
px_ozf_act_budgets_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_act_budgets_table,
p_batch_type IN VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_OZF_ACT_BUDGETS';
SELECT
OBJECT_VERSION_NUMBER
FROM OZF_ACT_BUDGETS
WHERE ACTIVITY_BUDGET_ID = p_act_budget_id;*/
SAVEPOINT UPDATE_OZF_ACT_BUDGETS;
/*Bug 9614703, insert all records in staging table and remove update of ozf_act_budgets table to avoid locking.
The update is moved to the concurrent program 'OZF: Update Budgets from Interface Tables'.*/
/*OPEN budget_objversion_csr(l_act_budget_id);
UPDATE ozf_act_budgets
SET request_amount = NVL(request_amount, 0) + NVL(px_ozf_act_budgets_tbl(i).REQUEST_AMOUNT,0)
,src_curr_request_amt = NVL(src_curr_request_amt, 0) + NVL(px_ozf_act_budgets_tbl(i).SRC_CURR_REQUEST_AMT,0)
,approved_amount = NVL(approved_amount, 0) + NVL(px_ozf_act_budgets_tbl(i).APPROVED_AMOUNT,0)
,approved_original_amount = NVL(approved_original_amount, 0) + NVL(px_ozf_act_budgets_tbl(i).APPROVED_ORIGINAL_AMOUNT,0)
,approved_amount_fc = NVL(approved_amount_fc, 0) + NVL(px_ozf_act_budgets_tbl(i).APPROVED_AMOUNT_FC,0)
,parent_src_apprvd_amt = NVL(parent_src_apprvd_amt, 0) + NVL(px_ozf_act_budgets_tbl(i).PARENT_SRC_APPRVD_AMT ,0)
,last_update_date = sysdate
,last_updated_by = NVL (fnd_global.user_id, -1)
,last_update_login = NVL (fnd_global.conc_login_id, -1)
,object_version_number = NVL (object_version_number,0) + 1
WHERE activity_budget_id = px_ozf_act_budgets_tbl(i).ACTIVITY_BUDGET_ID
AND OBJECT_VERSION_NUMBER = l_object_version_number;
x_budget_update_status := OZF_RESALE_COMMON_PVT.G_BATCH_CLOSED;
INSERT INTO OZF_ACT_BUDGETS_INT
(
RESALE_BATCH_ID,
ACTIVITY_BUDGET_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_AMOUNT,
APPROVED_AMOUNT,
APPROVED_ORIGINAL_AMOUNT,
APPROVED_AMOUNT_FC,
PARENT_SRC_APPRVD_AMT,
SRC_CURR_REQUEST_AMT,
-- For JMS ER (+)
BATCH_TYPE,
REQUEST_ID
-- For JMS ER (-)
)
VALUES(
p_batch_id,
px_ozf_act_budgets_tbl(i).ACTIVITY_BUDGET_ID,
SYSDATE,
NVL(fnd_global.user_id, -1),
SYSDATE,
NVL(fnd_global.user_id, -1),
NVL(fnd_global.user_id, -1),
(NVL(px_ozf_act_budgets_tbl(i).REQUEST_AMOUNT,0)),
(NVL(px_ozf_act_budgets_tbl(i).APPROVED_AMOUNT,0)),
(NVL(px_ozf_act_budgets_tbl(i).APPROVED_ORIGINAL_AMOUNT,0)),
(NVL(px_ozf_act_budgets_tbl(i).APPROVED_AMOUNT_FC,0)),
(NVL(px_ozf_act_budgets_tbl(i).PARENT_SRC_APPRVD_AMT ,0)),
(NVL(px_ozf_act_budgets_tbl(i).SRC_CURR_REQUEST_AMT,0)),
-- For JMS ER (+)
p_batch_type,
fnd_global.conc_request_id
-- For JMS ER (-)
);
x_budget_update_status := OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_CLOSE;
x_budget_update_status := OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_ACCRUALS;
px_ozf_act_budgets_tbl.delete;
ROLLBACK TO UPDATE_OZF_ACT_BUDGETS;
ROLLBACK TO UPDATE_OZF_ACT_BUDGETS;
ROLLBACK TO UPDATE_OZF_ACT_BUDGETS;
END UPDATE_OZF_ACT_BUDGETS;
PROCEDURE UPDATE_OZF_FUNDS_ALL_B (
p_batch_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_fund_update_status OUT NOCOPY VARCHAR2,
px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table,
p_batch_type IN VARCHAR2 -- For JMS ER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_OZF_FUNDS_ALL_B';
SELECT mc_record_id, object_version_number
FROM ozf_mc_transactions_all
WHERE source_object_id = source_id
AND source_object_name = 'FUND';
SELECT
ORIGINAL_BUDGET,
OBJECT_VERSION_NUMBER,
CURRENCY_CODE_TC,
ORG_ID,
BUDGET_AMOUNT_TC,
BUDGET_AMOUNT_FC,
AVAILABLE_AMOUNT,
TRANSFERED_IN_AMT,
TRANSFERED_OUT_AMT,
PLANNED_AMT,
COMMITTED_AMT,
EARNED_AMT,
PAID_AMT,
RECAL_COMMITTED,
ROLLUP_ORIGINAL_BUDGET,
ROLLUP_TRANSFERED_IN_AMT,
ROLLUP_TRANSFERED_OUT_AMT,
ROLLUP_HOLDBACK_AMT,
ROLLUP_PLANNED_AMT,
ROLLUP_COMMITTED_AMT,
ROLLUP_RECAL_COMMITTED,
ROLLUP_EARNED_AMT,
ROLLUP_PAID_AMT,
UTILIZED_AMT,
ROLLUP_UTILIZED_AMT
FROM OZF_FUNDS_ALL_B
WHERE FUND_ID = p_fund_id;*/
SAVEPOINT UPDATE_OZF_FUNDS_ALL_B;
All fund records will get inserted into OZF_FUNDS_ALL_B_INT table and processed to
main table via concurrent prgm 'OZF: Update Budgets from Interface Tables'*/
/*OPEN c_mc_record(l_fund_id);
UPDATE OZF_FUNDS_ALL_B
SET
BUDGET_AMOUNT_TC = NVL(BUDGET_AMOUNT_TC,0) + NVL(px_ozf_funds_new_tbl(i).BUDGET_AMOUNT_TC,0),
BUDGET_AMOUNT_FC = NVL(BUDGET_AMOUNT_FC,0) + NVL(px_ozf_funds_new_tbl(i).BUDGET_AMOUNT_FC,0),
AVAILABLE_AMOUNT = NVL(AVAILABLE_AMOUNT,0) + NVL(px_ozf_funds_new_tbl(i).AVAILABLE_AMOUNT,0),
TRANSFERED_IN_AMT = NVL(TRANSFERED_IN_AMT,0) + NVL(px_ozf_funds_new_tbl(i).TRANSFERED_IN_AMT,0),
TRANSFERED_OUT_AMT = NVL(TRANSFERED_OUT_AMT,0) + NVL(px_ozf_funds_new_tbl(i).TRANSFERED_OUT_AMT,0),
PLANNED_AMT = NVL(PLANNED_AMT,0) + NVL(px_ozf_funds_new_tbl(i).PLANNED_AMT,0),
COMMITTED_AMT = NVL(COMMITTED_AMT,0) + NVL(px_ozf_funds_new_tbl(i).COMMITTED_AMT,0),
EARNED_AMT = NVL(EARNED_AMT,0) + NVL(px_ozf_funds_new_tbl(i).EARNED_AMT,0),
PAID_AMT = NVL(PAID_AMT,0) + NVL(px_ozf_funds_new_tbl(i).PAID_AMT,0),
RECAL_COMMITTED = NVL(RECAL_COMMITTED,0) + NVL(px_ozf_funds_new_tbl(i).RECAL_COMMITTED,0),
ROLLUP_ORIGINAL_BUDGET = NVL(ROLLUP_ORIGINAL_BUDGET,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_ORIGINAL_BUDGET,0),
ROLLUP_TRANSFERED_IN_AMT = NVL(ROLLUP_TRANSFERED_IN_AMT,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_TRANSFERED_IN_AMT,0),
ROLLUP_TRANSFERED_OUT_AMT = NVL(ROLLUP_TRANSFERED_OUT_AMT,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_TRANSFERED_OUT_AMT,0),
ROLLUP_HOLDBACK_AMT = NVL(ROLLUP_HOLDBACK_AMT,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_HOLDBACK_AMT,0),
ROLLUP_PLANNED_AMT = NVL(ROLLUP_PLANNED_AMT,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_PLANNED_AMT,0),
ROLLUP_COMMITTED_AMT = NVL(ROLLUP_COMMITTED_AMT,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_COMMITTED_AMT,0),
ROLLUP_RECAL_COMMITTED = NVL(ROLLUP_RECAL_COMMITTED,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_RECAL_COMMITTED,0),
ROLLUP_EARNED_AMT = NVL(ROLLUP_EARNED_AMT,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_EARNED_AMT,0),
ROLLUP_PAID_AMT = NVL(ROLLUP_PAID_AMT,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_PAID_AMT,0),
UTILIZED_AMT = NVL(UTILIZED_AMT,0) + NVL(px_ozf_funds_new_tbl(i).UTILIZED_AMT,0),
ROLLUP_UTILIZED_AMT = NVL(ROLLUP_UTILIZED_AMT,0) + NVL(px_ozf_funds_new_tbl(i).ROLLUP_UTILIZED_AMT,0),
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
WHERE FUND_ID = px_ozf_funds_new_tbl(i).FUND_ID
AND OBJECT_VERSION_NUMBER = l_object_version_number;
GOTO START_INSERTING;
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)
,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
WHERE fund_id = l_fund_id
AND USERENV('LANG') IN(language, source_lang);
GOTO START_INSERTING;
OZF_UTILITY_PVT.debug_message('Updating update_rollup_amount from UPDATE_OZF_FUNDS_ALL_B for batch_id='||p_batch_id);
ozf_funds_pvt.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 => 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
);
GOTO START_INSERTING;
OZF_UTILITY_PVT.debug_message('Updating update_mc_transactions from UPDATE_OZF_FUNDS_ALL_B for batch_id'||p_batch_id);
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
,p_org_id => l_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);
GOTO START_INSERTING;
<>
IF insrt_flag = 'Y' THEN
*/
insrt_count := insrt_count + 1;
INSERT INTO OZF_FUNDS_ALL_B_INT
(
RESALE_BATCH_ID,
BATCH_TYPE,
REQUEST_ID,
FUND_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
BUDGET_AMOUNT_TC,
BUDGET_AMOUNT_FC,
AVAILABLE_AMOUNT,
TRANSFERED_IN_AMT,
TRANSFERED_OUT_AMT,
PLANNED_AMT,
COMMITTED_AMT,
EARNED_AMT,
PAID_AMT,
RECAL_COMMITTED,
ROLLUP_ORIGINAL_BUDGET,
ROLLUP_TRANSFERED_IN_AMT,
ROLLUP_TRANSFERED_OUT_AMT,
ROLLUP_HOLDBACK_AMT,
ROLLUP_PLANNED_AMT,
ROLLUP_COMMITTED_AMT,
ROLLUP_RECAL_COMMITTED,
ROLLUP_EARNED_AMT,
ROLLUP_PAID_AMT,
UTILIZED_AMT,
ROLLUP_UTILIZED_AMT
) VALUES
(
p_batch_id,
p_batch_type,
fnd_global.conc_request_id,
px_ozf_funds_new_tbl(i).FUND_ID,
SYSDATE,
NVL(fnd_global.user_id, -1),
SYSDATE,
NVL(fnd_global.user_id, -1),
NVL(fnd_global.user_id, -1),
NVL(px_ozf_funds_new_tbl(i).BUDGET_AMOUNT_TC,0),
NVL(px_ozf_funds_new_tbl(i).BUDGET_AMOUNT_FC,0),
NVL(px_ozf_funds_new_tbl(i).AVAILABLE_AMOUNT,0),
NVL(px_ozf_funds_new_tbl(i).TRANSFERED_IN_AMT,0),
NVL(px_ozf_funds_new_tbl(i).TRANSFERED_OUT_AMT,0),
NVL(px_ozf_funds_new_tbl(i).PLANNED_AMT,0),
NVL(px_ozf_funds_new_tbl(i).COMMITTED_AMT,0),
NVL(px_ozf_funds_new_tbl(i).EARNED_AMT,0),
NVL(px_ozf_funds_new_tbl(i).PAID_AMT,0),
NVL(px_ozf_funds_new_tbl(i).RECAL_COMMITTED,0),
NVL(px_ozf_funds_new_tbl(i).ROLLUP_ORIGINAL_BUDGET,0),
NVL(px_ozf_funds_new_tbl(i).ROLLUP_TRANSFERED_IN_AMT,0),
NVL(px_ozf_funds_new_tbl(i).ROLLUP_TRANSFERED_OUT_AMT,0),
NVL(px_ozf_funds_new_tbl(i).ROLLUP_HOLDBACK_AMT,0),
NVL(px_ozf_funds_new_tbl(i).ROLLUP_PLANNED_AMT,0),
NVL(px_ozf_funds_new_tbl(i).ROLLUP_COMMITTED_AMT,0),
NVL(px_ozf_funds_new_tbl(i).ROLLUP_RECAL_COMMITTED,0),
NVL(px_ozf_funds_new_tbl(i).ROLLUP_EARNED_AMT,0),
NVL(px_ozf_funds_new_tbl(i).ROLLUP_PAID_AMT,0),
NVL(px_ozf_funds_new_tbl(i).UTILIZED_AMT,0),
NVL(px_ozf_funds_new_tbl(i).ROLLUP_UTILIZED_AMT,0)
);
x_fund_update_status := OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_CLOSE;
x_fund_update_status := OZF_RESALE_COMMON_PVT.G_BATCH_PENDING_ACCRUALS;
px_ozf_funds_new_tbl.delete;
ROLLBACK TO UPDATE_OZF_FUNDS_ALL_B;
ROLLBACK TO UPDATE_OZF_FUNDS_ALL_B;
ROLLBACK TO UPDATE_OZF_FUNDS_ALL_B;
END UPDATE_OZF_FUNDS_ALL_B;