[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT org_id
FROM ozf_funds_all_b
WHERE fund_id = (SELECT budget_source_id FROM ozf_act_budgets
WHERE activity_budget_id = p_id);
SELECT system_status_code
FROM ams_user_statuses_vl
WHERE user_status_id = p_status_id;
OZF_ActBudgets_PVT.Update_Act_Budgets (
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_act_budgets_rec => l_act_budget_rec
);
OZF_ActBudgets_PVT.Update_Act_Budgets (
p_api_version => 1.0,
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 => l_act_budgets_rec
);
SELECT req.request_header_id
FROM ozf_request_headers_all_b req, ozf_act_budgets act
WHERE req.offer_id = act.act_budget_used_by_id
AND act.activity_budget_id = p_act_budget_id;
UPDATE ozf_approval_access
SET approval_access_flag = 'Y'
WHERE object_type ='SOFT_FUND'
AND object_id = l_request_header_id
AND approval_level = (SELECT min(approval_level) from ozf_approval_access WHERE object_type ='SOFT_FUND'
AND object_id = l_request_header_id );
OZF_ActBudgets_PVT.Update_Act_Budgets (
p_api_version => 1.0,
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 => l_act_budgets_rec
);
SELECT owner_user_id ,status_code
FROM ams_campaigns_all_b
WHERE campaign_id = p_act_budget_rec.act_budget_used_by_id;
SELECT act.request_amount,NVL(rsc.partner_party_name,'Partner')
, NVL(camp.description,''),
camp.source_code, TO_CHAR(camp.actual_exec_start_date),TO_CHAR(camp.actual_exec_end_date),camp.campaign_name
FROM ozf_act_budgets act, ams_campaigns_vl camp, pv_resource_info_v rsc
WHERE act.activity_budget_id = p_act_budget_rec.activity_budget_id
AND act.act_budget_used_by_id = camp.campaign_id
AND act.requester_Id = rsc.rsc_resource_id(+);
SELECT SUM(approved_amount)
FROM ozf_act_budgets
WHERE act_budget_used_by_id = p_act_budget_rec.act_budget_used_by_id
AND arc_act_budget_used_by = 'CAMP'
AND budget_source_type = 'FUND'
AND status_code = 'APPROVED';
SELECT act.activity_budget_id, act.budget_source_id,
(fund.committed_amt - act.request_amount) committed_amt,
fund.object_version_number
FROM ozf_act_budgets act, ozf_funds_all_b fund
WHERE act.arc_act_budget_used_by = 'OFFR'
AND act.act_budget_used_by_id = p_offer_id
AND act.transfer_type = 'REQUEST'
AND act.status_code = 'APPROVED'
AND act.budget_source_id = fund.fund_id;
SELECT act.activity_budget_id, act.budget_source_id,
(fund.committed_amt - act.request_amount) committed_amt,
fund.object_version_number, util.object_version_number util_object_version,
util.utilization_id
FROM ozf_act_budgets act, ozf_fund_details_v fund, ozf_funds_utilized_all_b util
WHERE act.arc_act_budget_used_by = 'OFFR'
AND act.act_budget_used_by_id = p_offer_id
AND act.transfer_type = 'REQUEST'
AND act.status_code = 'APPROVED'
AND act.budget_source_id = fund.fund_id
AND act.activity_budget_id = util.ams_activity_budget_id;
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 => l_msg_count
,x_msg_data => l_msg_data
,p_fund_rec => l_fund_rec
,p_mode => jtf_plsql_api.g_update
);
ozf_actbudgets_pvt.update_act_budgets ( 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 => l_msg_count
,x_msg_data => l_msg_data
,p_act_budgets_rec => l_act_budgets_rec);
ozf_fund_utilized_pvt.delete_utilization ( p_api_version => 1.0
,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_utilization_id => l_utilization_id
,p_object_version => l_util_object_version);
SELECT 1
FROM ozf_temp_eligibility offr
WHERE object_type = 'OFFR'
AND offr.eligibility_id > 0
AND (
(NOT EXISTS
(SELECT 1
FROM ozf_temp_eligibility fund
WHERE fund.object_type = 'FUND'
AND fund.exclude_flag = 'N'
AND fund.eligibility_id > 0
AND fund.eligibility_id = offr.eligibility_id))
OR
(EXISTS
(SELECT 1
FROM ozf_temp_eligibility fund
WHERE fund.object_type = 'FUND'
AND fund.exclude_flag = 'Y'
AND fund.eligibility_id > 0
AND fund.eligibility_id = offr.eligibility_id))
);
SELECT 1
FROM ozf_temp_eligibility fund
WHERE fund.object_type = 'FUND'
AND exclude_flag = 'Y'
AND fund.eligibility_id > 0
AND EXISTS
(SELECT 1
FROM ozf_temp_eligibility offr
WHERE offr.object_type = 'OFFR'
AND offr.eligibility_id > 0
AND offr.eligibility_id = fund.eligibility_id);
SELECT 1
FROM ozf_temp_eligibility offr
WHERE object_type = 'OFFR'
AND offr.eligibility_id > 0
AND NOT EXISTS
(SELECT 1
FROM ozf_temp_eligibility fund
WHERE offr.eligibility_id = fund.eligibility_id
AND fund.object_type = 'FUND'
AND fund.eligibility_id > 0
AND fund.exclude_flag = 'Y');
SELECT 1
FROM ozf_temp_eligibility offr, ozf_temp_eligibility fund
WHERE offr.object_type = 'OFFR'
AND fund.eligibility_id > 0
AND offr.eligibility_id > 0
AND fund.object_type = 'FUND'
AND fund.exclude_flag = 'N'
AND offr.eligibility_id = fund.eligibility_id;
SELECT decode(level_type_code, 'PRODUCT', inventory_item_id, category_id)
,excluded_flag
,decode(level_type_code, 'PRODUCT', 'PRICING_ATTRIBUTE1', 'PRICING_ATTRIBUTE2') attribute
FROM ams_act_products
WHERE act_product_used_by_id = p_act_product_used_by_id
AND arc_act_product_used_by = p_arc_act_product_used_by
AND excluded_flag = p_excluded_flag;
FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
FND_DSQL.add_text('SELECT ''FUND'', ''N'', product_id FROM (');
FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
FND_DSQL.add_text('SELECT ''FUND'', ''Y'', product_id FROM (');
SELECT market_segment_id, segment_type, exclude_flag
FROM ams_act_market_segments
WHERE act_market_segment_used_by_id = p_budget_id
AND arc_act_market_segment_used_by = 'FUND'
AND exclude_flag = p_exclude_flag;
FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
FND_DSQL.add_text('SELECT ''FUND'', ''N'', party_id FROM (');
FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
FND_DSQL.add_text('SELECT ''FUND'', ''Y'', party_id FROM (');
SELECT decode(level_type_code, 'PRODUCT', inventory_item_id, category_id)
, excluded_flag
, decode(level_type_code, 'PRODUCT', 'PRICING_ATTRIBUTE1', 'PRICING_ATTRIBUTE2') attribute
FROM ams_act_products
WHERE act_product_used_by_id = p_act_product_used_by_id
AND arc_act_product_used_by = p_arc_act_product_used_by
AND excluded_flag = p_excluded_flag;
SELECT count(*)
FROM ozf_temp_eligibility
WHERE object_type = 'OFFR';
EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
FND_DSQL.add_text('SELECT ''OFFR'', ''N'', product_id FROM (' );
SELECT distinct decode(level_type_code, 'PRODUCT', inventory_item_id, category_id)
,excluded_flag
,decode(level_type_code, 'PRODUCT', 'PRICING_ATTRIBUTE1', 'PRICING_ATTRIBUTE2') attribute
FROM ams_act_products
WHERE act_product_used_by_id
IN
(SELECT budget_source_id FROM ozf_act_budgets
WHERE arc_act_budget_used_by = 'OFFR'
AND act_budget_used_by_id = p_object_id
AND transfer_type = 'REQUEST'
AND status_code = 'APPROVED')
AND arc_act_product_used_by = 'FUND'
AND excluded_flag = p_excluded_flag;
SELECT decode(level_type_code, 'PRODUCT', inventory_item_id, category_id)
,excluded_flag
,decode(level_type_code, 'PRODUCT', 'PRICING_ATTRIBUTE1', 'PRICING_ATTRIBUTE2') attribute
FROM ams_act_products
WHERE act_product_used_by_id = p_object_id
AND arc_act_product_used_by = 'OFFR'
AND excluded_flag = 'N';
EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
FND_DSQL.add_text('SELECT ''FUND'', ''N'', product_id FROM (');
FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
FND_DSQL.add_text('SELECT ''FUND'', ''Y'', product_id FROM (');
FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
FND_DSQL.add_text('SELECT ''OFFR'', ''N'', product_id FROM (' );
SELECT org_id
FROM ozf_funds_all_b
WHERE fund_id = p_budget_id;
SELECT offer_type, qualifier_id
FROM ozf_offers
WHERE qp_list_header_id = p_object_id;
EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
FND_DSQL.add_text('SELECT ''OFFR'', ''N'', party_id FROM (' );
select party.party_id from hz_cust_accounts account,hz_parties party where account.party_id=party.party_id and account.cust_account_id =
*/
l_offer_mark := TRUE;
SELECT distinct market_segment_id, segment_type, exclude_flag
FROM ams_act_market_segments
WHERE act_market_segment_used_by_id
IN
(SELECT budget_source_id FROM ozf_act_budgets
WHERE arc_act_budget_used_by = 'OFFR'
AND act_budget_used_by_id = p_object_id
AND transfer_type = 'REQUEST'
AND status_code = 'APPROVED')
AND arc_act_market_segment_used_by = 'FUND'
AND exclude_flag = p_exclude_flag;
SELECT offer_type, qualifier_id
FROM ozf_offers
WHERE qp_list_header_id = p_object_id;
EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
FND_DSQL.add_text('SELECT ''FUND'', ''N'', party_id FROM (');
FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
FND_DSQL.add_text('SELECT ''FUND'', ''Y'', party_id FROM (');
FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id) ');
FND_DSQL.add_text('SELECT ''OFFR'', ''N'', party_id FROM (' );
select party.party_id from hz_cust_accounts account,hz_parties party where account.party_id=party.party_id and account.cust_account_id =
*/
l_offer_mark := TRUE;
SELECT budget_source_id
FROM ozf_act_budgets
WHERE activity_budget_id = p_actbudget_id;
SELECT offer_type
FROM ozf_offers
WHERE qp_list_header_id = p_object_id;
SELECT offer_type
FROM ozf_offers
WHERE qp_list_header_id = p_object_id;
SELECT 1
FROM dual
WHERE (EXISTS
(SELECT 1
FROM ozf_temp_eligibility
WHERE object_type = 'FUND'
AND exclude_flag = 'N'
AND eligibility_id = p_item_id))
AND (
NOT EXISTS
(SELECT 1
FROM ozf_temp_eligibility
WHERE object_type = 'FUND'
AND exclude_flag = 'Y'
AND eligibility_id = p_item_id));
SELECT 1
FROM ozf_temp_eligibility
WHERE object_type = 'FUND'
AND exclude_flag = 'Y'
AND eligibility_id = p_item_id;
SELECT party_id
FROM hz_cust_accounts
WHERE cust_account_id = p_cust_account_id;
EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
SELECT SUM(NVL(request_amount,0))
FROM ozf_act_budgets
WHERE act_budget_used_by_id= p_object_id
AND arc_act_budget_used_by = p_object_type;
SELECT budget_amount_tc,owner_user_id
FROM ams_campaigns_vl
WHERE campaign_id = p_object_id;
SELECT budget_amount_tc,owner_user_id
FROM ams_campaign_schedules_vl
WHERE schedule_id=p_object_id;
SELECT budget_amount_tc,owner_id
FROM ozf_offers
WHERE qp_list_header_id=p_object_id;
SELECT fund_amount_tc,owner_user_id
FROM ams_event_headers_vl
WHERE event_header_id = p_object_id;
SELECT fund_amount_tc,owner_user_id
FROM ams_event_offers_vl
WHERE event_offer_id = p_object_id;
SELECT fund_amount_tc,owner_user_id
FROM ams_event_offers_vl
WHERE event_offer_id = p_object_id;
SELECT budget_amount_tc,owner_user_id
FROM ams_deliverables_vl
WHERE deliverable_id = p_object_id;
SELECT activity_budget_id, act_budget_used_by_id,arc_act_budget_used_by,requester_id
FROM ozf_act_budgets
WHERE arc_act_budget_used_by = p_object_type
AND act_budget_used_by_id = p_object_id
AND transfer_type = 'REQUEST';
SELECT offer_type,custom_setup_id, reusable,offer_amount,offer_code,owner_id, qph.description
FROM ozf_offers , qp_list_headers qph
WHERE qp_list_header_id = p_object_id
and qp_list_header_id = qph.list_header_id ;
SELECT fun.short_name
FROM ozf_act_budgets , ozf_funds_all_tl fun
WHERE activity_budget_id = p_activity_id
AND budget_source_id = fun.fund_id
AND USERENV('LANG') IN (fun.language, fun.source_lang);
SELECT fun.short_name
FROM ozf_act_budgets , ozf_fund_details_v fun
WHERE activity_budget_id = p_activity_id
AND budget_source_id = fun.fund_id;
ozf_actbudgets_pvt.Update_Act_Budgets (
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 => l_msg_count,
x_msg_data => l_msg_data,
p_act_budgets_rec => l_act_budgets_rec
);
ozf_utility_pvt.Write_Conc_log('Update_Act_Budgets fails.');
/*kdass 05-DEC-2005 bug 4662453 - Update_Act_Budgets is being called twice, so removing this one
ozf_actbudgets_pvt.Update_Act_Budgets ( 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 => l_msg_count,
x_msg_data => l_msg_data,
p_act_budgets_rec => l_act_budgets_rec
);
ozf_utility_pvt.Write_Conc_log('return status from Update_Act_Budgets = ' || l_return_status);
ozf_utility_pvt.Write_Conc_log('Update_Act_Budgets fails');
ozf_actbudgets_pvt.Update_Act_Budgets ( 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 => l_msg_count,
x_msg_data => l_msg_data,
p_act_budgets_rec => l_act_budgets_rec
);
ozf_utility_pvt.Write_Conc_log('Update_Act_Budgets returns = ' || l_return_status);
UPDATE ozf_offers
SET status_code = 'DRAFT'
,user_status_id = OZF_Utility_PVT.get_default_user_status ('OZF_OFFER_STATUS', 'DRAFT')
,status_date = SYSDATE
,object_version_number = object_version_number + 1
WHERE qp_list_header_id = l_modifier_list_rec.qp_list_header_id;
UPDATE ozf_act_budgets
SET status_code = 'DRAFT'
,user_status_id = OZF_Utility_PVT.get_default_user_status ('OZF_BUDGETSOURCE_STATUS', 'DRAFT')
,object_version_number = object_version_number + 1
WHERE activity_budget_id = p_actbudget_id
AND status_code = 'PENDING';
UPDATE ozf_act_budgets
SET status_code = 'DRAFT'
,user_status_id = OZF_Utility_PVT.get_default_user_status ('OZF_BUDGETSOURCE_STATUS', 'DRAFT')
,object_version_number = object_version_number + 1
WHERE activity_budget_id = request_rec.activity_budget_id
AND status_code = 'PENDING';
select activity_budget_id
from ozf_act_budgets
where act_budget_used_by_id = p_object_id
and arc_act_budget_used_by = p_object_type
and transfer_type = 'REQUEST'
and status_code = 'NEW';
select NVL(SUM(request_amount),0)
from ozf_act_budgets
where act_budget_used_by_id = p_object_id
and arc_act_budget_used_by = p_object_type
and transfer_type = 'REQUEST';
SELECT NVL(attr_available_flag,'N')
FROM ams_custom_setup_attr
WHERE custom_setup_id = p_custom_setup_id
AND object_attribute = 'BREQ';
SELECT NVL(offer_amount,0),owner_id,custom_setup_id,offer_code
FROM ozf_offers
WHERE qp_list_header_id = p_object_id;
ozf_actbudgets_pvt.update_act_budgets (
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_data=> x_msg_data
,x_msg_count=> x_msg_count
,p_act_budgets_rec=> l_act_budgets_rec
);