[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_cons_fund_mode CONSTANT VARCHAR2 (30) := 'ADJUST'; --JTF_PLSQL_API.G_UPDATE
SELECT org_id, ledger_id
FROM ozf_funds_all_b
WHERE fund_id = p_fund_id;
SELECT ozf_funds_utilized_s.NEXTVAL
FROM DUAL;
SELECT 'X'
FROM ozf_funds_utilized_all_b
WHERE utilization_id = cv_utilization_id;
SELECT gl_posted_flag
FROM ozf_funds_utilized_all_b
WHERE utilization_id = p_utilization_id;
SELECT utilized_amt
,earned_amt
,object_version_number
,committed_amt
,accrual_basis
,fund_type
,original_budget
,paid_amt
,plan_id
,liability_flag -- yzhao: 10/20/2003 added
,recal_committed -- yzhao: 10/20/2003 added
--13-DEC-2010 kdass - TPA Parallel Execution ER - 9614703
,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;
SELECT adjustment_type
FROM ozf_claim_types_all_b
WHERE claim_type_id = p_adj_type_id;
update ozf_act_budgets REQUEST between fully accrual budget and its offer when accrual happens
*/
CURSOR c_accrual_budget_reqeust(p_fund_id IN NUMBER, p_plan_id IN NUMBER) IS
SELECT activity_budget_id
, object_version_number
FROM ozf_act_budgets
WHERE arc_act_budget_used_by = 'OFFR'
AND act_budget_used_by_id = p_plan_id
AND budget_source_type = 'FUND'
AND budget_source_id = p_fund_id
AND transfer_type = 'REQUEST'
AND status_code = 'APPROVED';
SELECT utilization_id
, object_version_number
FROM ozf_funds_utilized_all_b
WHERE utilization_type = 'REQUEST'
AND fund_id = p_fund_id
AND plan_type = 'FUND'
AND plan_id = p_fund_id
AND component_type = 'OFFR'
AND component_id = p_plan_id
AND ams_activity_budget_id = p_actbudget_id;
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT 1
FROM qp_list_lines line, qp_pricing_attributes attr
WHERE attr.list_header_id = p_qp_list_header_id
AND line.list_line_id = attr.list_line_id
AND NVL(line.accrual_flag, 'N') = 'Y'
AND attr.product_attribute = DECODE(NVL(p_product_type, 'OTHER')
, 'PRODUCT', 'ITEM', 'FAMILY', 'CATEGORY', attr.product_attribute)
AND attr.product_attr_value = NVL(p_product_id, attr.product_attr_value)
AND attr.product_attribute_context = 'ITEM'
);
SELECT month_id, ent_qtr_id, ent_year_id
FROM ozf_time_day
WHERE report_date = trunc(p_date);
SELECT offer_type, volume_offer_type, custom_setup_id
FROM ozf_offers
WHERE qp_list_header_id = p_offer_id;
SELECT request_header_id
FROM ozf_sd_request_headers_all_b
WHERE offer_id =p_list_header_id;
SELECT NVL(sob.gl_acct_for_offinv_flag, 'F')
FROM ozf_sys_parameters_all sob
,ozf_funds_all_b fun
WHERE fun.fund_id = p_fund_id
AND sob.org_id = fun.ORG_id ;
SELECT NVL(sob.gl_acct_for_offinv_flag, 'F')
FROM ozf_sys_parameters_all sob
WHERE sob.org_id = p_org_id;
SELECT objfundsum_id
, object_version_number
, committed_amt
, recal_committed_amt
, utilized_amt
, earned_amt
, paid_amt
, plan_curr_committed_amt
, plan_curr_recal_committed_amt
, plan_curr_utilized_amt
, plan_curr_earned_amt
, plan_curr_paid_amt
, univ_curr_committed_amt
, univ_curr_recal_committed_amt
, univ_curr_utilized_amt
, univ_curr_earned_amt
, univ_curr_paid_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 gps.period_name, gps.start_date,
gps.end_date, gps.closing_status
FROM gl_period_statuses gps
WHERE gps.application_id = 101
AND gps.set_of_books_id = c_sob_id
AND Nvl(gps.adjustment_period_flag,'N') = 'N'
AND gps.closing_status = 'C'
AND to_date(c_gl_date) between gps.start_date and gps.end_date
ORDER BY gps.start_date desc;
SELECT line_category_code, shipped_quantity, flow_status_code,
invoice_interface_status_code, invoiced_quantity, actual_shipment_date
FROM oe_order_lines_all
WHERE line_id = p_line_id;
SELECT order_number
FROM oe_order_headers_all
WHERE header_id = p_header_id;
SELECT cust.trx_date -- transaction(invoice) date
FROM ra_customer_trx_all cust
,ra_customer_trx_lines_all cust_lines
WHERE cust.customer_trx_id = cust_lines.customer_trx_id
AND cust_lines.sales_order = p_order_number
AND cust_lines.interface_line_attribute6 = TO_CHAR(p_line_id);
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_id;
SELECT orig_org_id
FROM qp_list_headers_b
WHERE list_header_id = p_plan_id;
SELECT org_id
FROM ozf_claim_types_all_b
WHERE claim_type_id = p_adj_id;
SELECT org_id
FROM hz_cust_site_uses_all
WHERE site_use_id = p_site_use_id;
SELECT plan_curr_amount,plan_curr_amount_remaining
FROM ozf_funds_utilized_all_b
WHERE utilization_id = p_utilization_id;
SELECT plan_curr_amount_remaining
FROM ozf_funds_utilized_all_b
WHERE utilization_id = p_utilization_id;
SELECT ent_year_id
FROM OZF_TIME_ENT_YEAR ey
WHERE p_gl_date between ey.start_date and ey.end_date;
ozf_utility_pvt.debug_message ( l_full_name || ': insert');
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 request_amount = NVL(request_amount, 0) + l_plan_curr_amount
, src_curr_request_amt = NVL(src_curr_request_amt, 0) + l_utilization_rec.amount
, approved_amount = NVL(approved_amount, 0) + l_plan_curr_amount
, approved_original_amount = NVL(approved_original_amount, 0) + l_utilization_rec.amount
, approved_amount_fc = NVL(approved_amount_fc, 0) + l_utilization_rec.acctd_amount
, 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 = l_act_budget_objver + 1
WHERE activity_budget_id = l_act_budget_id
AND object_version_number = l_act_budget_objver;
l_operation := 'INSERT';
l_operation := 'UPDATE';
IF l_operation = 'UPDATE' THEN
px_ozf_act_budgets_tbl(l_budget_count).RESALE_BATCH_ID := l_utilization_rec.reference_id;
ELSIF l_operation = 'INSERT' THEN
l_budget_count := px_ozf_act_budgets_tbl.COUNT + 1;
UPDATE ozf_funds_utilized_all_b
SET amount = NVL(amount, 0) + NVL(l_utilization_rec.amount, 0)
, plan_curr_amount = NVL(plan_curr_amount, 0) + NVL(l_plan_curr_amount, 0)
, univ_curr_amount = NVL(univ_curr_amount, 0) + NVL(l_univ_curr_amount, 0)
, acctd_amount = NVL(acctd_amount, 0) + NVL(l_utilization_rec.acctd_amount, 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 = l_act_budget_objver + 1
WHERE utilization_id = l_act_budget_id
AND object_version_number = l_act_budget_objver;
INSERT INTO ozf_funds_utilized_all_b
(utilization_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
,utilization_type
,fund_id
,plan_type
,plan_id
,component_type
,component_id
,object_type
,object_id
,order_id
,invoice_id
,amount
,acctd_amount
,currency_code
,exchange_rate_type
,exchange_rate_date
,exchange_rate
,adjustment_type
,adjustment_date
,object_version_number
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,org_id
,adjustment_type_id
,camp_schedule_id
,gl_date
,product_level_type
,product_id
,ams_activity_budget_id
,amount_remaining
,acctd_amount_remaining
,cust_account_id
,price_adjustment_id
,plan_curr_amount
,plan_curr_amount_remaining
,scan_unit
,scan_unit_remaining
,activity_product_id
,volume_offer_tiers_id
,gl_posted_flag
-- 11/04/2003 yzhao 11.5.10: added
,billto_cust_account_id
,reference_type
,reference_id
/*fix for bug 4778995
,month_id
,quarter_id
,year_id
*/
-- 01/02/2004 kdass added for 11.5.10
,order_line_id
,orig_utilization_id
,bill_to_site_use_id
,ship_to_site_use_id
,univ_curr_amount
,univ_curr_amount_remaining
,fund_request_currency_code
,fund_request_amount
,fund_request_amount_remaining
,plan_currency_code
--kdass - bug 9470625
,cost_price
,cost_price_currency_code
,discount_type
,discount_amount
,discount_amount_currency_code
,year_id --Fix for Bug 11793070
,list_line_id
)
VALUES (l_utilization_rec.utilization_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
,l_utilization_rec.created_from -- CREATED_FROM
,l_request_id -- REQUEST_ID
,fnd_global.prog_appl_id -- PROGRAM_APPLICATION_ID
,fnd_global.conc_program_id -- PROGRAM_ID
,SYSDATE -- PROGRAM_UPDATE_DATE
,l_utilization_rec.utilization_type
,l_utilization_rec.fund_id
,l_utilization_rec.plan_type
,l_utilization_rec.plan_id
,l_utilization_rec.component_type
,l_utilization_rec.component_id
,l_utilization_rec.object_type
,l_utilization_rec.object_id
,l_utilization_rec.order_id
,l_utilization_rec.invoice_id
,l_utilization_rec.amount
,l_utilization_rec.acctd_amount
,l_utilization_rec.currency_code
,l_utilization_rec.exchange_rate_type
,NVL (l_utilization_rec.exchange_rate_date, SYSDATE)
,l_utilization_rec.exchange_rate
,l_utilization_rec.adjustment_type
,NVL(l_utilization_rec.adjustment_date,SYSDATE)
,l_object_version_number -- object_version_number
,l_utilization_rec.attribute_category
,l_utilization_rec.attribute1
,l_utilization_rec.attribute2
,l_utilization_rec.attribute3
,l_utilization_rec.attribute4
,l_utilization_rec.attribute5
,l_utilization_rec.attribute6
,l_utilization_rec.attribute7
,l_utilization_rec.attribute8
,l_utilization_rec.attribute9
,l_utilization_rec.attribute10
,l_utilization_rec.attribute11
,l_utilization_rec.attribute12
,l_utilization_rec.attribute13
,l_utilization_rec.attribute14
,l_utilization_rec.attribute15
,l_utilization_rec.org_id
,l_utilization_rec.adjustment_type_id
,l_utilization_rec.camp_schedule_id
,l_utilization_rec.gl_date
,l_utilization_rec.product_level_type
,l_utilization_rec.product_id
,l_utilization_rec.ams_activity_budget_id
,l_utilization_rec.amount_remaining
,l_utilization_rec.acctd_amount_remaining
,l_utilization_rec.cust_account_id
,l_utilization_rec.price_adjustment_id
,l_plan_curr_amount
,l_plan_curr_amount_remaining
,l_utilization_rec.scan_unit
,l_utilization_rec.scan_unit_remaining
,l_utilization_rec.activity_product_id
,l_utilization_rec.volume_offer_tiers_id
,l_utilization_rec.gl_posted_flag -- yzhao: 03/20/2003 added
-- 11/04/2003 yzhao 11.5.10: added
,l_utilization_rec.billto_cust_account_id
,l_utilization_rec.reference_type
,l_utilization_rec.reference_id
/*fix for bug 4778995
,l_utilization_rec.month_id
,l_utilization_rec.quarter_id
,l_utilization_rec.year_id
*/
-- 01/02/2004 kdass added for 11.5.10
,l_utilization_rec.order_line_id
,l_utilization_rec.orig_utilization_id
-- 06/15/2005 Ribha added for R12
,l_utilization_rec.bill_to_site_use_id
,l_utilization_rec.ship_to_site_use_id
,l_utilization_rec.univ_curr_amount
,l_utilization_rec.univ_curr_amount_remaining
,l_utilization_rec.fund_request_currency_code
,l_utilization_rec.fund_request_amount
,l_utilization_rec.fund_request_amount_remaining
,l_utilization_rec.plan_currency_code
--kdass - bug 9470625
,l_utilization_rec.cost_price
,l_utilization_rec.cost_price_currency_code
,l_utilization_rec.discount_type
,l_utilization_rec.discount_amount
,l_utilization_rec.discount_amount_currency_code
,l_utilization_rec.year_id--Fix for Bug 11793070
,l_utilization_rec.list_line_id
);
INSERT INTO ozf_funds_utilized_all_tl
(utilization_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
,adjustment_desc
,source_lang
,language
,org_id
)
SELECT l_utilization_rec.utilization_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
,l_utilization_rec.created_from -- CREATED_FROM
,l_request_id -- REQUEST_ID
,fnd_global.prog_appl_id -- PROGRAM_APPLICATION_ID
,fnd_global.conc_program_id -- PROGRAM_ID
,SYSDATE -- PROGRAM_UPDATE_DATE
,l_utilization_rec.adjustment_desc -- ADJUSTMENT_DESCRIPTION
,USERENV ('LANG') -- SOURCE_LANGUAGE
,l.language_code -- LANGUAGE
,l_utilization_rec.org_id -- fix for 3640740
FROM fnd_languages l
WHERE l.installed_flag IN ('I', 'B')
AND NOT EXISTS ( SELECT NULL
FROM ozf_funds_utilized_all_tl t
WHERE t.utilization_id = l_utilization_rec.utilization_id
AND t.language = l.language_code);
ozf_utility_pvt.debug_message ( l_full_name || ': inserted:' || ': Utilization_id: ' || l_utilization_rec.utilization_id || ': amount:' || l_utilization_rec.amount);
UPDATE ozf_funds_utilized_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)
, request_id = l_request_id
, gl_posted_flag = l_gl_posted_flag
--, gl_date = sysdate
WHERE utilization_id = l_utilization_rec.utilization_id
AND object_version_number = l_object_version_number;
ozf_funds_pvt.update_fund (
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=> g_cons_fund_mode
,p_resale_batch_id=>l_utilization_rec.reference_id
,p_batch_type => p_batch_type
,p_use_fund_staging_tables => p_use_fund_staging_tables
,px_ozf_funds_old_rectype => lx_ozf_funds_old_rectype
,px_ozf_funds_new_tbl => px_ozf_funds_new_tbl -- For TPA Parallel Execution ER Bug#9614703
);
OZF_ACTBUDGETS_PVT.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 => l_utilization_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,
p_conv_date => l_utilization_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
);
PROCEDURE delete_utilization (
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_utilization_id IN NUMBER
,p_object_version IN NUMBER
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2 (30) := 'Delete_Utilization';
SAVEPOINT delete_utilization;
|| ': delete');
DELETE FROM ozf_funds_utilized_all_b
WHERE utilization_id = p_utilization_id
AND object_version_number = p_object_version;
DELETE FROM ozf_funds_utilized_all_tl
WHERE utilization_id = p_utilization_id;
ROLLBACK TO delete_utilization;
ROLLBACK TO delete_utilization;
ROLLBACK TO delete_utilization;
END delete_utilization;
SELECT utilization_id
FROM ozf_funds_utilized_all_b
WHERE utilization_id = p_utilization_id
AND object_version_number = p_object_version
FOR UPDATE OF utilization_id NOWAIT;
SELECT utilization_id
FROM ozf_funds_utilized_all_tl
WHERE utilization_id = p_utilization_id
AND USERENV ('LANG') IN (language, source_lang)
FOR UPDATE OF utilization_id NOWAIT;
PROCEDURE update_utilization (
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_utilization_rec IN utilization_rec_type
,p_mode IN VARCHAR2 := 'UPDATE'
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2 (30) := 'Update_Utilization';
SELECT utilized_amt
,object_version_number
FROM ozf_funds_all_b
WHERE fund_id = p_fund_id;
SELECT amount
FROM ozf_funds_utilized_all_b
WHERE utilization_id = p_util_id;
SAVEPOINT update_utilization;
,p_validation_mode=> jtf_plsql_api.g_update
,x_return_status=> l_return_status
);
|| ': update');
UPDATE ozf_funds_utilized_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 = l_utilization_rec.created_from
,request_id = l_request_id
,program_application_id = fnd_global.prog_appl_id
,program_id = fnd_global.conc_program_id
,program_update_date = SYSDATE
,utilization_type = l_utilization_rec.utilization_type
,fund_id = l_utilization_rec.fund_id
,plan_type = l_utilization_rec.plan_type
,plan_id = l_utilization_rec.plan_id
,component_type = l_utilization_rec.component_type
,component_id = l_utilization_rec.component_id
,object_type = l_utilization_rec.object_type
,object_id = l_utilization_rec.object_id
,order_id = l_utilization_rec.order_id
,invoice_id = l_utilization_rec.invoice_id
,amount = l_utilization_rec.amount
,acctd_amount = l_utilization_rec.acctd_amount
,currency_code = l_utilization_rec.currency_code
,exchange_rate_type = l_utilization_rec.exchange_rate_type
,exchange_rate_date = l_utilization_rec.exchange_rate_date
,exchange_rate = l_utilization_rec.exchange_rate
,adjustment_type = l_utilization_rec.adjustment_type
,adjustment_date = l_utilization_rec.adjustment_date
,object_version_number = l_utilization_rec.object_version_number
+ 1
,attribute_category = l_utilization_rec.attribute_category
,attribute1 = l_utilization_rec.attribute1
,attribute2 = l_utilization_rec.attribute2
,attribute3 = l_utilization_rec.attribute3
,attribute4 = l_utilization_rec.attribute4
,attribute5 = l_utilization_rec.attribute5
,attribute6 = l_utilization_rec.attribute6
,attribute7 = l_utilization_rec.attribute7
,attribute8 = l_utilization_rec.attribute8
,attribute9 = l_utilization_rec.attribute9
,attribute10 = l_utilization_rec.attribute10
,attribute11 = l_utilization_rec.attribute11
,attribute12 = l_utilization_rec.attribute12
,attribute13 = l_utilization_rec.attribute13
,attribute14 = l_utilization_rec.attribute14
,attribute15 = l_utilization_rec.attribute15
,adjustment_type_id = l_utilization_rec.adjustment_type_id
,camp_schedule_id = l_utilization_rec.camp_schedule_id
,gl_date = l_utilization_rec.gl_date
,product_level_type = l_utilization_rec.product_level_type
,product_id = l_utilization_rec.product_id
,ams_activity_budget_id = l_utilization_rec.ams_activity_budget_id
,amount_remaining = l_utilization_rec.amount_remaining
,acctd_amount_remaining = l_utilization_rec.acctd_amount_remaining
,cust_account_id = l_utilization_rec.cust_account_id
,price_adjustment_id = l_utilization_rec.price_adjustment_id
,plan_curr_amount = l_plan_curr_amount
,plan_curr_amount_remaining = l_plan_curr_amount_remaining
,scan_unit = l_utilization_rec.scan_unit
,scan_unit_remaining = l_utilization_rec.scan_unit_remaining
,activity_product_id = l_utilization_rec.activity_product_id
,gl_posted_flag = l_utilization_rec.gl_posted_flag -- yzhao: 03/20/2003 added
-- 11/04/2003 yzhao 11.5.10: added
,billto_cust_account_id = l_utilization_rec.billto_cust_account_id
,reference_type = l_utilization_rec.reference_type
,reference_id = l_utilization_rec.reference_id
/*fix for bug 4778995
,month_id = l_utilization_rec.month_id
,quarter_id = l_utilization_rec.quarter_id
,year_id = l_utilization_rec.year_id
*/
-- R12 yzhao added universal currency
,bill_to_site_use_id = l_utilization_rec.bill_to_site_use_id
,ship_to_site_use_id = l_utilization_rec.ship_to_site_use_id
,univ_curr_amount = l_univ_curr_amount
,univ_curr_amount_remaining = l_univ_curr_amount_remaining
--Fix for Bug 11793070
,year_id = l_utilization_rec.year_id
WHERE utilization_id = l_utilization_rec.utilization_id
AND object_version_number = l_utilization_rec.object_version_number;
UPDATE ozf_funds_utilized_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 = l_utilization_rec.created_from
,request_id = l_request_id
,program_application_id = fnd_global.prog_appl_id
,program_id = fnd_global.conc_program_id
,program_update_date = SYSDATE
,adjustment_desc = l_utilization_rec.adjustment_desc
,source_lang = USERENV ('LANG')
WHERE utilization_id = l_utilization_rec.utilization_id
AND USERENV ('LANG') IN (language, source_lang);
ozf_funds_pvt.update_fund (
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=> g_cons_fund_mode
);
ROLLBACK TO update_utilization;
ROLLBACK TO update_utilization;
ROLLBACK TO update_utilization;
END update_utilization;
SELECT SUM (total_amount) existing_amount
FROM (SELECT parent_source
,SUM (amount) total_amount
FROM (SELECT a1.fund_id parent_source
,NVL (SUM (a1.amount), 0) amount
FROM ozf_funds_utilized_all_b a1
WHERE a1.component_id = p_object_id
AND a1.component_type = p_object_type
AND a1.utilization_type IN ('TRANSFER', 'REQUEST')
GROUP BY a1.fund_id
UNION
SELECT a2.fund_id parent_source
,-NVL (SUM (a2.amount), 0) amount
FROM ozf_funds_utilized_all_b a2
WHERE a2.plan_id = p_object_id
AND a2.plan_type = p_object_type
AND a2.utilization_type NOT IN ('TRANSFER', 'REQUEST')
GROUP BY a2.fund_id)
WHERE parent_source = p_fund_src_id
GROUP BY parent_source);
SELECT SUM(NVL(committed_amt,0)-NVL(utilized_amt,0)) total_amount
FROM ozf_object_fund_summary
WHERE object_id =p_object_id
AND object_type = p_object_type
and fund_id = p_fund_src_id;
SELECT SUM (amount)
FROM ozf_funds_utilized_all_b
WHERE fund_id = p_fund_id
AND plan_type = p_act_type
AND plan_id = p_act_id
-- AND utilization_type NOT IN ('REQUEST', 'TRANSFER') R12 no REQUEST/TRANSFER in util table
AND NVL(cust_account_id,-1) = NVL(p_cust_account_id,-2);
SELECT SUM (amount)
FROM ozf_funds_utilized_all_b
WHERE fund_id = p_fund_id
AND component_type = 'OFFR'
AND component_id = p_offer_id
AND utilization_type NOT IN ('REQUEST', 'TRANSFER');
SELECT status_code
FROM ozf_funds_all_b
WHERE fund_id = p_fund_id;
,p_mode IN VARCHAR2 := 'INSERT'
,x_return_status OUT NOCOPY VARCHAR2
) IS
CURSOR c_fund_type (p_fund_id IN NUMBER) IS
SELECT 'X'
FROM ozf_funds_all_b ozf
WHERE ozf.fund_type = 'FULLY_ACCRUED'
AND ozf.fund_id = p_fund_id;
IF p_mode <> 'UPDATE' THEN
-- Check for committed amount exists all offers except FULLY ACCRUED budget offer
/* yzhao: 09/29/2005 R12 no TRANSFER/REQUEST in utilization table
IF p_complete_rec.utilization_type NOT IN ('TRANSFER', 'REQUEST')
AND l_dummy IS NULL AND
*/
IF l_dummy IS NULL AND
NVL(p_complete_rec.adjustment_type,'N') NOT IN ( 'DECREASE_EARNED' ,'DECREASE_COMM_EARNED') THEN
IF check_committed_amount_exists (
p_complete_rec.amount
,p_complete_rec.plan_id
,p_complete_rec.plan_type
,p_complete_rec.fund_id
,p_complete_rec.component_id
) = fnd_api.g_false THEN
IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
fnd_message.set_name ('OZF', 'OZF_FUND_NO_COMMITTMENT');
SELECT *
FROM ozf_funds_utilized_all_vl
WHERE utilization_id = p_utilization_rec.utilization_id;
SELECT activity_budget_id, object_version_number,request_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';
SELECT offer_type FROM ozf_offers
WHERE qp_list_header_id = p_offer_id;
SELECT act.activity_product_id,act.scan_value
FROM
(SELECT activity_product_id,level_type_code level_code,
DECODE (level_type_code, 'PRODUCT', inventory_item_id, category_id) product_id
,scan_value,channel_id
FROM ams_act_products
WHERE act_product_used_by_id = p_offer_id
AND arc_act_product_used_by = 'OFFR'
) act
WHERE act.level_code = p_product_type
AND act.product_id = p_product_id
AND act.channel_id = p_channel_id;
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_id;
SELECT NVL(transaction_currency_code,fund_request_curr_code) fund_request_curr_code,
transaction_currency_code
FROM ozf_offers
WHERE qp_list_header_id = p_activity_id;