[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT org_id
FROM ozf_funds_all_b
WHERE fund_id = p_actbudget_id;
SELECT parent_src_id, parent_currency, SUM (amount) total_amount
FROM (SELECT a1.parent_source_id parent_src_id, a1.parent_src_curr parent_currency
,NVL (SUM (a1.parent_src_apprvd_amt), 0) amount
FROM ozf_act_budgets a1
WHERE a1.act_budget_used_by_id = p_budget_used_by_id
AND a1.arc_act_budget_used_by = p_budget_used_by_type
AND a1.status_code = 'APPROVED'
AND a1.transfer_type <> 'UTILIZED'
GROUP BY a1.parent_source_id, a1.parent_src_curr
UNION
SELECT a2.parent_source_id parent_src_id, a2.parent_src_curr parent_currency
,-NVL (SUM (a2.parent_src_apprvd_amt), 0) amount
FROM ozf_act_budgets a2
WHERE a2.budget_source_id = p_budget_used_by_id
AND a2.budget_source_type = p_budget_used_by_type
AND a2.status_code = 'APPROVED'
GROUP BY a2.parent_source_id, a2.parent_src_curr)
GROUP BY parent_src_id, parent_currency
ORDER BY parent_src_id;
SELECT parent_src_id
,parent_currency
,SUM (amount) total_amount
FROM (SELECT a1.fund_id parent_src_id
,a1.currency_code parent_currency
,NVL (SUM (a1.amount), 0) amount
FROM ozf_funds_utilized_all_vl a1
WHERE a1.component_id = p_budget_used_by_id
AND a1.component_type = p_budget_used_by_type
-- AND a1.status_code = 'APPROVED' -- only approved record are present here
AND a1.utilization_type IN ('TRANSFER', 'REQUEST')
GROUP BY a1.fund_id, a1.currency_code
UNION
SELECT a2.fund_id parent_src_id
,a2.currency_code parent_currency
,-NVL (SUM (a2.amount), 0) amount
FROM ozf_funds_utilized_all_vl a2
WHERE a2.plan_id = p_budget_used_by_id
AND a2.plan_type = p_budget_used_by_type
AND a2.utilization_type IN ('TRANSFER', 'REQUEST', 'UTILIZED')
-- AND a2.status_code = 'APPROVED' -- -- only approved record are present here
GROUP BY a2.fund_id, a2.currency_code)
GROUP BY parent_src_id, parent_currency
ORDER BY parent_src_id;
SELECT activity_budget_id
,object_version_number
,approved_amount
,parent_src_apprvd_amt
FROM ozf_act_budgets
WHERE act_budget_used_by_id = p_used_by_id
AND arc_act_budget_used_by = p_used_by_type
-- AND parent_source_id = p_parent_src_id
AND transfer_type = 'UTILIZED';
ozf_actbudgets_pvt.update_act_budgets (
p_api_version=> l_api_version
,x_return_status=> l_return_status
,x_msg_count=> x_msg_count
,x_msg_data=> x_msg_data
,p_act_budgets_rec=> l_act_budgets_rec
);
SELECT fund_id parent_source
,fund_currency parent_curr
,NVL(committed_amt,0)-NVL(utilized_amt,0) total_amount
,NVL(univ_curr_committed_amt,0)-NVL(univ_curr_utilized_amt,0) total_acctd_amount
FROM ozf_object_fund_summary
WHERE object_id =p_budget_source_id
AND object_type = p_budget_source_type;
SELECT parent_source
,parent_curr
,SUM (amount) total_amount
,SUM(acctd_amount) total_acctd_amount
FROM (SELECT a1.fund_id parent_source
,a1.currency_code parent_curr
,SUM(NVL(a1.amount, 0)) amount
,SUM(NVL(a1.acctd_amount,0)) acctd_amount
FROM ozf_funds_utilized_all_b a1
WHERE a1.component_id = p_budget_source_id
AND a1.component_type = p_budget_source_type
AND a1.utilization_type NOT IN
('ADJUSTMENT', 'ACCRUAL', 'UTILIZED', 'SALES_ACCRUAL', 'CHARGEBACK')
GROUP BY a1.fund_id, a1.currency_code
UNION
SELECT a2.fund_id parent_source
,a2.currency_code parent_curr
,-SUM(NVL(a2.amount, 0)) amount,
-SUM(NVL(a2.acctd_amount,0)) acctd_amount
FROM ozf_funds_utilized_all_b a2
WHERE a2.plan_id = p_budget_source_id
AND a2.plan_type =p_budget_source_type
GROUP BY a2.fund_id, a2.currency_code)
GROUP BY parent_source, parent_curr
ORDER BY parent_source;
SELECT SUM(NVL(univ_curr_committed_amt,0) - NVL(univ_curr_utilized_amt,0))
FROM ozf_object_fund_summary
WHERE object_id =p_budget_source_id
AND object_type = p_budget_source_type;
SELECT SUM(acctd_amount) total_acctd_amount
FROM (SELECT SUM(NVL(a1.acctd_amount,0)) acctd_amount
FROM ozf_funds_utilized_all_b a1
WHERE a1.component_id = p_budget_source_id
AND a1.component_type =p_budget_source_type
AND a1.utilization_type NOT IN
('ADJUSTMENT', 'ACCRUAL', 'UTILIZED', 'SALES_ACCRUAL', 'CHARGEBACK')
UNION
SELECT -SUM(NVL(a2.acctd_amount,0)) acctd_amount
FROM ozf_funds_utilized_all_b a2
WHERE a2.plan_id = p_budget_source_id
AND a2.plan_type = p_budget_source_type);
p_updated_amount NUMBER := 0;
SELECT org_id
FROM hz_cust_site_uses_all
WHERE site_use_id = p_site_use_id;
SELECT offer_type
,qualifier_id,qualifier_type,NVL(budget_offer_yn,'N'),beneficiary_account_id,
autopay_party_attr,autopay_party_id
FROM ozf_offers
WHERE qp_list_header_id = p_offer_id;
SELECT fund_type
,accrual_basis,plan_id
FROM ozf_funds_all_b
WHERE fund_id = p_ofr_fund_id;
SELECT NVL(accrual_flag,'N')
FROM oe_price_adjustments
WHERE price_Adjustment_id = p_price_Adjustment_id;
SELECT fund_id , currency_code_tc, accrual_basis
FROM ozf_funds_all_b
WHERE plan_id = p_plan_id;
SELECT sites.cust_account_id, uses.bill_to_site_use_id
FROM hz_cust_acct_sites_all sites, hz_cust_site_uses_all uses
WHERE uses.site_use_id = p_site_use_id
AND sites.cust_acct_site_id = uses.cust_acct_site_id;
select max(cust_account_id) from hz_cust_accounts
where party_id = p_party_id
and status= 'A';
SELECT 'X' FROM ozf_act_budgets
WHERE act_budget_used_by_id = p_offer_id
AND budget_source_id = p_fund_id
AND status_code ='APPROVED'
AND transfer_type = 'REQUEST';
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_id;
SELECT status_code
,lumpsum_amount
,object_version_number
,distribution_type
,qp_list_header_id
,offer_id
FROM ozf_offers
WHERE qp_list_header_id = p_qp_list_header_id;
SELECT SUM (line_lumpsum_qty)
FROM ams_act_products
WHERE arc_act_product_used_by = 'OFFR'
AND act_product_used_by_id = p_qp_list_header_id;
SELECT SUM(NVL(plan_curr_committed_amt,0))
FROM ozf_object_fund_summary
WHERE object_id =p_offer_id
AND object_type = 'OFFR';
SELECT SUM(DECODE(transfer_type,'REQUEST', NVL(approved_amount,0),NVL(0-approved_original_amount,0)))
FROM ozf_act_budgets
WHERE status_code = 'APPROVED'
AND ((arc_act_budget_used_by = 'OFFR' AND act_budget_used_by_id = l_id and transfer_type ='REQUEST')
OR (budget_source_type = 'OFFR' AND budget_source_id = l_id and transfer_type ='TRANSFER'));
/* SELECT SUM(DECODE(transfer_type,'REQUEST', NVL(approved_amount,0),NVL(0-approved_original_amount,0)))
FROM ozf_act_budgets
WHERE arc_act_budget_used_by = 'OFFR'
AND act_budget_used_by_id = l_id;
SELECT activity_budget_id, object_version_number, approved_amount
FROM ozf_act_budgets
WHERE act_budget_used_by_id = p_used_by_id
AND arc_act_budget_used_by = p_used_by_type
AND transfer_type = 'UTILIZED';
SELECT nvl(transaction_currency_code,fund_request_curr_code) offer_currency_code
FROM ozf_offers
WHERE qp_list_header_id = p_offer_id;
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_id;
SELECT REQUEST_AMOUNT, APPROVED_AMOUNT, APPROVED_ORIGINAL_AMOUNT, APPROVED_AMOUNT_FC,
PARENT_SRC_APPRVD_AMT, SRC_CURR_REQUEST_AMT FROM OZF_ACT_BUDGETS
WHERE ACTIVITY_BUDGET_ID = p_act_budget_id;
/* Bug#9614703 update to ozf_funds_all_tl table was happening during creation of act_budget
records. So we need to send p_use_fund_staging_tables flag and px_ozf_act_budgets_tbl rec
in this flow as well. Hence used the overloaded procedure.
*/
ozf_actbudgets_pvt.create_act_budgets (
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=> x_return_status,
x_msg_count=> x_msg_count,
x_msg_data=> x_msg_data,
p_act_budgets_rec=> l_act_budgets_rec,
p_act_util_rec => l_act_util_rec,
p_approval_flag => fnd_api.g_true, -- auto approved
x_act_budget_id=> l_act_budget_id,
x_utilized_amount => x_utilized_amount, -- yzhao: 06/21/2004 added for chargeback
p_resale_batch_id => l_act_util_rec.reference_id,
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,
x_utilization_id => x_utilization_id --nirprasa - added for Bug 9383565
);
ozf_actbudgets_pvt.update_act_budgets (
p_api_version=> l_api_version,
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,
p_child_approval_flag => 'N' ,
p_act_util_rec => l_act_util_rec,
x_utilized_amount => x_utilized_amount -- yzhao: 06/21/2004 added for chargeback
);
ozf_actbudgets_pvt.update_act_budgets (
p_api_version=> l_api_version,
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=> l_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 => 'N',
p_requestor_owner_flag => 'N',
p_act_util_rec => l_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 ERBug#9614703
x_utilization_id => x_utilization_id --nirprasa - added for Bug 9383565
);
ozf_utility_pvt.debug_message(l_full_name||' : '||'update act budget retrun status'||x_return_status);
SELECT qp.start_date_active, NVL(qp.currency_code, ofs.fund_request_curr_code) currency_code,
NVL(ofs.account_closed_flag,'N'),ofs.qualifier_id, ofs.qualifier_type,ofs.owner_id,ofs.custom_setup_id,
ofs.org_id
FROM qp_list_headers_b qp,ozf_offers ofs
WHERE qp.list_header_id = p_offer_id
AND qp.list_header_id = ofs.qp_list_header_id;
SELECT activity_product_id,DECODE (level_type_code, 'PRODUCT', inventory_item_id, category_id) product_id
,level_type_code,scan_value,scan_unit_forecast,quantity
FROM ams_act_products
WHERE act_product_used_by_id = p_offer_id
AND arc_act_product_used_by = 'OFFR';
SELECT NVL(plan_curr_committed_amt,0) approved_amount
,fund_id
,fund_currency currency_code
FROM ozf_object_fund_summary
WHERE object_id =p_offer_id
AND object_type = 'OFFR';
SELECT SUM (approved_amount) approved_amount, fund_id, currency_code
FROM (
SELECT NVL(plan_curr_amount,0) approved_amount, fund_id, currency_code
FROM ozf_funds_utilized_all_b
WHERE utilization_type = 'REQUEST'
AND component_type = 'OFFR'
AND component_id = p_offer_id
UNION ALL
SELECT NVL(-plan_curr_amount,0) approved_amount, fund_id, currency_code
FROM ozf_funds_utilized_all_b
WHERE utilization_type = 'TRANSFER'
AND plan_type = 'OFFR'
AND plan_id = p_offer_id
) GROUP BY fund_id, currency_code;
SELECT SUM(NVL(DECODE(utilization_type, 'REQUEST',util.plan_curr_amount,-util.plan_curr_amount),0)) approved_amount,
util.fund_id,util.currency_code
FROM ozf_funds_utilized_all_b util
WHERE util.utilization_type IN ('REQUEST','TRANSFER')
AND DECODE(util.utilization_type,'REQUEST', util.component_type,util.plan_type) = 'OFFR'
AND DECODE(util.utilization_type,'REQUEST', util.component_id,util.plan_id) = p_offer_id
GROUP BY util.fund_id,util.currency_code;
SELECT SUM(NVL(plan_curr_committed_amt,0))
FROM ozf_object_fund_summary
WHERE object_id =p_offer_id
AND object_type = 'OFFR';
SELECT SUM (approved_amount)
FROM (SELECT NVL(plan_curr_amount,0) approved_amount
FROM ozf_funds_utilized_all_b
WHERE utilization_type = 'REQUEST'
AND component_type = 'OFFR'
AND component_id = p_offer_id
UNION ALL
SELECT NVL(-plan_curr_amount,0) approved_amount
FROM ozf_funds_utilized_all_b
WHERE utilization_type = 'TRANSFER'
AND plan_type = 'OFFR'
AND plan_id = p_offer_id);
SELECT SUM(DECODE(utilization_type,'REQUEST',plan_curr_amount,'TRANSFER',-plan_curr_amount))
FROM ozf_funds_utilized_all_b
WHERE utilization_type IN ('REQUEST','TRANSFER')
AND DECODE(utilization_type,'REQUEST', component_type,plan_type) = 'OFFR'
AND DECODE(utilization_type,'REQUEST', component_id,plan_id) = p_offer_id;
SELECT NVL(SUM(plan_curr_amount),0)
FROM ozf_funds_utilized_all_b
WHERE plan_id = p_offer_id
AND plan_type = 'OFFR'
AND utilization_type ='ACCRUAL';
SELECT request_header_id
FROM ozf_request_headers_all_b
WHERE offer_id =p_offer_id;
select max(cust_account_id) from hz_cust_accounts
where party_id = p_party_id
and status= 'A';
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_id;
SELECT qp.start_date_active,qp.end_date_active, NVL(qp.currency_code, ofs.fund_request_curr_code) currency_code,
ofs.custom_setup_id,NVL(ofs.account_closed_flag,'N'),ofs.qualifier_id, ofs.qualifier_type,
ofs.org_id
FROM qp_list_headers_b qp,ozf_offers ofs
WHERE qp.list_header_id = p_offer_id
AND qp.list_header_id = ofs.qp_list_header_id;
SELECT offer_amount
,distribution_type,owner_id
FROM ozf_offers
WHERE qp_list_header_id = p_offer_id;
SELECT DECODE (level_type_code, 'PRODUCT', inventory_item_id, category_id) product_id
,SUM(line_lumpsum_qty) amount, level_type_code
FROM ams_act_products
WHERE act_product_used_by_id = p_offer_id
AND arc_act_product_used_by = 'OFFR'
GROUP BY inventory_item_id,level_type_code,category_id; -- added by feliu to fix bug 4861647
SELECT SUM (line_lumpsum_qty) total_quantity
FROM ams_act_products
WHERE act_product_used_by_id = p_offer_id
AND arc_act_product_used_by = 'OFFR';
SELECT NVL(plan_curr_committed_amt,0) approved_amount
,fund_id
,fund_currency currency_code
FROM ozf_object_fund_summary
WHERE object_id =p_offer_id
AND object_type = 'OFFR';
SELECT SUM (approved_amount) approved_amount, fund_id, currency_code
FROM (
SELECT NVL(plan_curr_amount,0) approved_amount, fund_id, currency_code
FROM ozf_funds_utilized_all_b
WHERE utilization_type = 'REQUEST'
AND component_type = 'OFFR'
AND component_id = p_offer_id
UNION ALL
SELECT NVL(-plan_curr_amount,0) approved_amount, fund_id, currency_code
FROM ozf_funds_utilized_all_b
WHERE utilization_type = 'TRANSFER'
AND plan_type = 'OFFR'
AND plan_id = p_offer_id
) GROUP BY fund_id, currency_code;
SELECT SUM(NVL(DECODE(utilization_type, 'REQUEST',util.plan_curr_amount,-util.plan_curr_amount),0)) approved_amount,
util.fund_id,util.currency_code
FROM ozf_funds_utilized_all_b util
WHERE util.utilization_type IN ('REQUEST','TRANSFER')
AND DECODE(util.utilization_type,'REQUEST', util.component_type,util.plan_type) = 'OFFR'
AND DECODE(util.utilization_type,'REQUEST', util.component_id,util.plan_id) = p_offer_id
GROUP BY util.fund_id,util.currency_code;
SELECT SUM(NVL(plan_curr_committed_amt,0))
FROM ozf_object_fund_summary
WHERE object_id =p_offer_id
AND object_type = 'OFFR';
SELECT SUM (approved_amount)
FROM (SELECT NVL(plan_curr_amount,0) approved_amount
FROM ozf_funds_utilized_all_b
WHERE utilization_type = 'REQUEST'
AND component_type = 'OFFR'
AND component_id = p_offer_id
UNION ALL
SELECT NVL(-plan_curr_amount,0) approved_amount
FROM ozf_funds_utilized_all_b
WHERE utilization_type = 'TRANSFER'
AND plan_type = 'OFFR'
AND plan_id = p_offer_id);
SELECT SUM(DECODE(utilization_type,'REQUEST',plan_curr_amount,'TRANSFER',-plan_curr_amount))
FROM ozf_funds_utilized_all_b
WHERE utilization_type IN ('REQUEST','TRANSFER')
AND DECODE(utilization_type,'REQUEST', component_type,plan_type) = 'OFFR'
AND DECODE(utilization_type,'REQUEST', component_id,plan_id) = p_offer_id;
SELECT NVL(SUM(plan_curr_amount),0), MAX(creation_date)
FROM ozf_funds_utilized_all_b
WHERE plan_id = p_offer_id
AND plan_type = 'OFFR'
AND utilization_type ='ACCRUAL';
SELECT SUM(util.plan_curr_amount)
FROM ozf_funds_utilized_all_b util
WHERE util.component_id = p_offer_id
AND util.component_type = 'OFFR'
AND util.utilization_type ='ACCRUAL'
--AND product_id =p_product_id
AND fund_id = p_fund_id;
SELECT attr_available_flag
FROM ams_custom_setup_attr
WHERE custom_setup_id = p_custom_setup_id
AND object_attribute = 'SCPO';
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_id;
l_offer_data := DBMS_XMLGEN.getXml('SELECT ''OFFR'' AccrualType,
util.utilization_id UtilizationId,
util.plan_id PromotionId,
util.fund_id FundId,
NVL(map.xref_line_id_value,line.activity_product_id) DiscountLineId,
util.product_id ProductId,
util.cust_account_id CustomerId,
util.plan_curr_amount AccrualAmount,
''0'' ShippedQuantity,
util.plan_curr_amount Revenue
FROM ozf_funds_utilized_all_b util, ams_act_products line,
ozf_xref_map map
WHERE util.product_id = line.inventory_item_id
AND util.plan_type = ''OFFR''
AND util.plan_id = line.act_product_used_by_id
AND line.arc_act_product_used_by = ''OFFR''
AND util.gl_posted_flag = ''Y''
AND line.activity_product_id = map.list_line_id(+)
AND line.act_product_used_by_id = ' || p_offer_id, 0);
SELECT custom_setup_id
FROM ozf_offers
WHERE qp_list_header_id = p_offer_id;
SELECT DECODE (level_type_code, 'PRODUCT', inventory_item_id, category_id) product_id
,level_type_code,scan_value, quantity
FROM ams_act_products
WHERE activity_product_Id = p_product_activity_id;
SELECT nvl(transaction_currency_code,fund_request_curr_code),NVL(source_from_parent,'N'),qualifier_id
FROM ozf_offers
WHERE qp_list_header_id = p_qp_list_header_id;
SELECT SUM(NVL(plan_curr_committed_amt,0) - NVL(plan_curr_utilized_amt,0))
from ozf_object_fund_summary
WHERE object_type = 'OFFR'
AND object_id = p_qp_list_header_id;
SELECT act_offer_used_by_id
FROM ozf_act_offers
WHERE qp_list_header_id = p_qp_list_header_id
AND arc_act_offer_used_by = 'CAMP';
SELECT fund_id,plan_type,plan_id, currency_code
FROM ozf_funds_utilized_all_b
WHERE activity_product_Id = p_product_activity_id;
SELECT available_budget,fund_id,currency_code_tc
FROM ozf_fund_details_v
WHERE fund_id = p_fund_id;
SELECT (NVL(original_budget, 0) - NVL(holdback_amt, 0)
+ NVL(transfered_in_amt, 0) - NVL(transfered_out_amt, 0)) available_budget,
fund_id,currency_code_tc
FROM ozf_funds_all_b
WHERE fund_id = p_fund_id;
SELECT fund_id
,fund_currency
,NVL(committed_amt,0)-NVL(utilized_amt,0) committed_amt
FROM ozf_object_fund_summary
WHERE object_id =p_qp_list_header_id
AND object_type = 'OFFR';
SELECT fund_id
,fund_currency
,SUM (amount) committed_amt
FROM (SELECT a1.fund_id fund_id
,a1.currency_code fund_currency
,NVL (SUM (a1.amount), 0) amount
FROM ozf_funds_utilized_all_b a1
WHERE a1.component_id = p_qp_list_header_id
AND a1.component_type = 'OFFR'
AND a1.utilization_type = 'REQUEST'
GROUP BY a1.fund_id, a1.currency_code
UNION
SELECT a2.fund_id fund_id
,a2.currency_code fund_currency
,-NVL (SUM (a2.amount), 0) amount
FROM ozf_funds_utilized_all_b a2
WHERE a2.plan_id = p_qp_list_header_id
AND a2.plan_type = 'OFFR'
GROUP BY a2.fund_id, a2.currency_code)
GROUP BY fund_id, fund_currency
ORDER BY fund_id;
PROCEDURE update_budget_source(
p_object_version_number IN NUMBER
,p_budget_used_by_id IN NUMBER
,p_budget_used_by_type IN VARCHAR2
,p_from_parent IN VARCHAR2
,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
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2 (50) := 'update_budget_source';
SAVEPOINT update_budget_source;
UPDATE ams_campaign_schedules_b
SET source_from_parent = p_from_parent
--,object_version_number = p_object_version_number + 1
WHERE schedule_id = p_budget_used_by_id;
UPDATE ams_event_offers_all_b
SET source_from_parent = p_from_parent
WHERE event_offer_id = p_budget_used_by_id;
ROLLBACK TO update_budget_source;
ROLLBACK TO update_budget_source;
ROLLBACK TO update_budget_source;
END update_budget_source;
SELECT req.request_header_id, req.currency_code,off.qualifier_id, req.submitted_by,off.org_id --approved_by
FROM ozf_request_headers_all_b req, ozf_offers off
WHERE req.offer_id =p_offer_id
AND req.offer_id = off.qp_list_header_id;
select item_id, NVL(approved_amount,0),item_type from ozf_request_lines_all
where request_header_id =p_request_header_id;
SELECT NVL(plan_curr_committed_amt,0) approved_amount
,fund_id
,fund_currency currency_code
FROM ozf_object_fund_summary
WHERE object_id =p_offer_id
AND object_type = 'OFFR';
SELECT SUM (approved_amount) approved_amount, fund_id, currency_code
FROM (
SELECT NVL(plan_curr_amount,0) approved_amount, fund_id, currency_code
FROM ozf_funds_utilized_all_b
WHERE utilization_type = 'REQUEST'
AND component_type = 'OFFR'
AND component_id = p_offer_id
UNION ALL
SELECT NVL(-plan_curr_amount,0) approved_amount, fund_id, currency_code
FROM ozf_funds_utilized_all_b
WHERE utilization_type = 'TRANSFER'
AND plan_type = 'OFFR'
AND plan_id = p_offer_id
) GROUP BY fund_id, currency_code;
SELECT SUM(NVL(DECODE(utilization_type, 'REQUEST',util.plan_curr_amount,-util.plan_curr_amount),0)) approved_amount,
util.fund_id,util.currency_code
FROM ozf_funds_utilized_all_b util
WHERE util.utilization_type IN ('REQUEST','TRANSFER')
AND DECODE(util.utilization_type,'REQUEST', util.component_type,util.plan_type) = 'OFFR'
AND DECODE(util.utilization_type,'REQUEST', util.component_id,util.plan_id) = p_offer_id
GROUP BY util.fund_id,util.currency_code;
SELECT SUM(NVL(plan_curr_committed_amt,0))
FROM ozf_object_fund_summary
WHERE object_id =p_offer_id
AND object_type = 'OFFR';
SELECT SUM (approved_amount)
FROM (SELECT NVL(plan_curr_amount,0) approved_amount
FROM ozf_funds_utilized_all_b
WHERE utilization_type = 'REQUEST'
AND component_type = 'OFFR'
AND component_id = p_offer_id
UNION ALL
SELECT NVL(-plan_curr_amount,0) approved_amount
FROM ozf_funds_utilized_all_b
WHERE utilization_type = 'TRANSFER'
AND plan_type = 'OFFR'
AND plan_id = p_offer_id);
SELECT SUM(DECODE(utilization_type,'REQUEST',plan_curr_amount,'TRANSFER',-plan_curr_amount))
FROM ozf_funds_utilized_all_b
WHERE utilization_type IN ('REQUEST','TRANSFER')
AND DECODE(utilization_type,'REQUEST', component_type,plan_type) = 'OFFR'
AND DECODE(utilization_type,'REQUEST', component_id,plan_id) = p_offer_id;
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_id;
PROCEDURE update_request_status (
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_offer_is IN NUMBER
) IS
CURSOR c_req_header_rec(p_offer_id IN NUMBER) IS
SELECT request_header_id,object_version_number,status_code
FROM ozf_request_headers_all_b
WHERE offer_id = p_offer_id;
l_api_name VARCHAR2 (60) := 'update_request_status';
UPDATE ozf_request_headers_all_b
SET status_code ='APPROVED',
object_version_number = l_obj_ver_num + 1
WHERE request_header_id = l_req_header_id;
END update_request_status;