[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fund_id parent_source_id, committed_amt total_amount , fund_currency parent_curr
FROM ozf_object_fund_summary
WHERE object_type = 'OFFR'
AND object_id = p_src_id
--AND NVL(committed_amt, 0) <> 0
ORDER BY fund_id;
SELECT NVL(budget_offer_yn,'N')
FROM ozf_offers
WHERE qp_list_header_id = p_src_id;
SELECT fund_id , currency_code_tc
FROM ozf_funds_all_b
WHERE plan_id = p_src_id;
SELECT SUM(NVL(a2.amount, 0)) amount
FROM ozf_funds_utilized_all_b a2
WHERE a2.plan_id = p_offer_id
AND a2.plan_type = 'OFFR'
AND a2.fund_id = p_fund_id
AND a2.utilization_type NOT IN ('REQUEST', 'TRANSFER', 'SALES_ACCRUAL');
SELECT utilized_amt
FROM ozf_object_fund_summary
WHERE fund_id = p_fund_id
AND object_type = 'OFFR'
AND object_id = p_offer_id;
x_fund_amt_tbl.DELETE;
SELECT ozf_act_budgets_s.NEXTVAL
FROM DUAL;
SELECT activity_budget_id, object_version_number, approved_amount,approved_amount_fc
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 exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_id;
SELECT nvl(transaction_currency_code,fund_request_curr_code) offer_currency_code
FROM ozf_offers
WHERE qp_list_header_id = p_offer_id;
SELECT currency_code
FROM gl_ledgers_public_v
WHERE ledger_id = p_ledger_id;
UPDATE ozf_act_budgets
SET request_amount = l_old_approved_amount + NVL(l_act_budgets_rec.request_amount, 0),
approved_amount =l_old_approved_amount + NVL(l_act_budgets_rec.request_amount, 0),
src_curr_request_amt =l_old_approved_amount + NVL(l_act_budgets_rec.request_amount, 0),
object_version_number = l_obj_ver_num + 1
,parent_source_id = l_act_budgets_rec.parent_source_id
,parent_src_curr = l_act_budgets_rec.parent_src_curr
,parent_src_apprvd_amt =l_act_budgets_rec.parent_src_apprvd_amt
,approved_amount_fc = NVL(l_old_amount_fc,0) + NVL(l_approved_amount_fc,0)
,approved_original_amount = l_old_approved_amount + l_act_budgets_rec.request_amount
WHERE activity_budget_id = l_activity_id
AND object_version_number = l_obj_ver_num;
INSERT INTO ozf_act_budgets
(activity_budget_id,last_update_date
,last_updated_by, creation_date
,created_by,last_update_login -- other columns
,object_version_number,act_budget_used_by_id
,arc_act_budget_used_by,budget_source_type
,budget_source_id,transaction_type
,request_amount,request_currency
,request_date,user_status_id
,status_code,approved_amount
,approved_original_amount,approved_in_currency
,approval_date, approver_id
,spent_amount, partner_po_number
,partner_po_date, partner_po_approver
,posted_flag, adjusted_flag
,parent_act_budget_id, contact_id
,reason_code, transfer_type
,requester_id,date_required_by
,parent_source_id,parent_src_curr
,parent_src_apprvd_amt,partner_holding_type
,partner_address_id, vendor_id
,owner_id,recal_flag
,attribute_category, attribute1
,attribute2, attribute3
,attribute4, attribute5
,attribute6, attribute7
,attribute8, attribute9
,attribute10, attribute11
,attribute12, attribute13
,attribute14, attribute15
,approved_amount_fc
,src_curr_request_amt
)
VALUES (l_act_budgets_rec.activity_budget_id,SYSDATE
,fnd_global.user_id, SYSDATE
,fnd_global.user_id, fnd_global.conc_login_id
,1, l_act_budgets_rec.act_budget_used_by_id
,l_act_budgets_rec.arc_act_budget_used_by, l_act_budgets_rec.budget_source_type
,l_act_budgets_rec.budget_source_id, l_act_budgets_rec.transaction_type
,l_act_budgets_rec.request_amount, l_act_budgets_rec.request_currency
,SYSDATE, l_act_budgets_rec.user_status_id
,NVL(l_act_budgets_rec.status_code, 'NEW'), l_act_budgets_rec.approved_amount
,l_act_budgets_rec.approved_amount,l_act_budgets_rec.approved_in_currency
,sysdate,l_requester_id
,l_act_budgets_rec.spent_amount, l_act_budgets_rec.partner_po_number
,l_act_budgets_rec.partner_po_date, l_act_budgets_rec.partner_po_approver
,l_act_budgets_rec.posted_flag, l_act_budgets_rec.adjusted_flag
,l_act_budgets_rec.parent_act_budget_id, l_act_budgets_rec.contact_id
,l_act_budgets_rec.reason_code, l_act_budgets_rec.transfer_type
,l_requester_id,l_act_budgets_rec.date_required_by
,l_act_budgets_rec.parent_source_id,l_act_budgets_rec.parent_src_curr
,l_act_budgets_rec.parent_src_apprvd_amt,l_act_budgets_rec.partner_holding_type
,l_act_budgets_rec.partner_address_id, l_act_budgets_rec.vendor_id
,NULL,l_act_budgets_rec.recal_flag
,l_act_budgets_rec.attribute_category, l_act_budgets_rec.attribute1
,l_act_budgets_rec.attribute2, l_act_budgets_rec.attribute3
,l_act_budgets_rec.attribute4, l_act_budgets_rec.attribute5
,l_act_budgets_rec.attribute6, l_act_budgets_rec.attribute7
,l_act_budgets_rec.attribute8, l_act_budgets_rec.attribute9
,l_act_budgets_rec.attribute10, l_act_budgets_rec.attribute11
,l_act_budgets_rec.attribute12, l_act_budgets_rec.attribute13
,l_act_budgets_rec.attribute14, l_act_budgets_rec.attribute15
,l_approved_amount_fc
,l_act_budgets_rec.approved_amount);
|| ': insert complete' || l_act_budgets_rec.activity_budget_id);
SELECT org_id
FROM hz_cust_site_uses_all
WHERE site_use_id = p_site_use_id;
SELECT ozf_funds_utilized_s.NEXTVAL
FROM DUAL;
SELECT object_version_number
,accrual_basis
,fund_type
,original_budget
,earned_amt
,paid_amt
,parent_fund_id
,rollup_original_budget
,rollup_earned_amt
,rollup_paid_amt
-- yzhao 10/14/2003 added below
,committed_amt
,recal_committed
,rollup_committed_amt
,rollup_recal_committed
,plan_id
,NVL(liability_flag, 'N')
-- yzhao: 11.5.10
,utilized_amt
,rollup_utilized_amt
FROM ozf_funds_all_b
WHERE fund_id = p_fund_id;
SELECT mc_record_id
,object_version_number
,amount_column1 -- original
,amount_column6 -- committed; yzhao: 10/14/2003 added
SELECT offer_type, custom_setup_id, beneficiary_account_id,
nvl(transaction_currency_code,fund_request_curr_code) offer_currency_code,
transaction_currency_code,
offer_id,autopay_party_attr,autopay_party_id --nirprasa
FROM ozf_offers
WHERE qp_list_header_id = p_offer_id;
SELECT NVL(accrual_flag,'N')
FROM oe_price_adjustments
WHERE price_Adjustment_id = p_price_Adjustment_id;
SELECT fund_id
,object_version_number
,rollup_original_budget
,rollup_earned_amt
,rollup_paid_amt
-- yzhao: 10/14/2003 added
,rollup_committed_amt
,rollup_recal_committed
-- yzhao: 11.5.10
,rollup_utilized_amt
FROM ozf_funds_all_b
connect by prior parent_fund_id =fund_id
start with fund_id = p_fund_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 month_id, ent_qtr_id, ent_year_id
FROM ozf_time_day
WHERE report_date = trunc(p_date);
SELECT SET_OF_BOOKS_ID
FROM ozf_sys_parameters_all
WHERE org_id = p_org_id;
SELECT sob.set_of_books_id, fun.ORG_id, 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 fun.ledger_id
FROM gl_sets_of_books sob,
ozf_funds_all_b fun
where sob.set_of_books_id = fun.ledger_id
and fun.fund_id = p_fund_id;
SELECT NVL(sob.gl_acct_for_offinv_flag, 'F')
FROM ozf_sys_parameters_all sob
WHERE sob.org_id = p_org_id;
SELECT request_header_id
FROM ozf_request_headers_all_b
WHERE offer_id =p_list_header_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 request_header_id
FROM ozf_sd_request_headers_all_b
WHERE offer_id =p_list_header_id;
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_id;
SELECT NVL2(qpll.price_by_formula_id, qpll.operand, oe.operand),
oe.arithmetic_operator
FROM oe_price_adjustments oe, qp_list_lines qpll
WHERE oe.price_adjustment_id = p_price_adjustment_id
AND oe.list_line_id = qpll.list_line_id;
SELECT 1
FROM oe_order_lines_all
WHERE header_id = p_header_id
AND split_from_line_id = p_line_id;
SELECT utilization_id, object_version_number
FROM ozf_funds_utilized_all_b
WHERE order_line_id = p_line_id
AND object_id = p_object_id
AND object_type = p_object_type
AND plan_id = p_plan_id
AND product_id = p_inventory_item_id
AND price_adjustment_id = p_price_adjustment_id
AND gl_posted_flag = 'N';
SELECT ent_year_id
FROM OZF_TIME_ENT_YEAR ey
WHERE p_gl_date between ey.start_date and ey.end_date;
ELSE -- Added by nirprasa, This is added for partial shipment scenario when amt_reminain was being updated as null.
IF l_utilization_rec.gl_posted_flag IS NULL THEN -- added by feliu on 06/09/04
l_utilization_rec.gl_posted_flag := G_GL_FLAG_NO; -- 'N', waiting for posting to gl
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, gl_posted_flag
,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
-- 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
*/
,order_line_id
,orig_utilization_id -- added by feliu on 08/03/04
-- added by rimehrot for R12
,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
--nirprasa, ER 10216374/9447673
,cost_price
,cost_price_currency_code
--kdass - bug 9470625
,discount_type
,discount_amount
,discount_amount_currency_code
,year_id -- Fix for Bug 12657908
)
VALUES (l_utilization_rec.utilization_id,SYSDATE -- LAST_UPDATE_DATE
,NVL (fnd_global.user_id, -1),NVL (fnd_global.conc_login_id, -1) -- LAST_UPDATE_LOGIN
,SYSDATE,NVL (fnd_global.user_id, -1) -- CREATED_BY
,l_utilization_rec.created_from,fnd_global.conc_request_id -- REQUEST_ID
,fnd_global.prog_appl_id,fnd_global.conc_program_id -- PROGRAM_ID
,SYSDATE,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,G_FAE_START_DATE
,l_utilization_rec.exchange_rate,l_utilization_rec.adjustment_type
,l_utilization_rec.adjustment_date,1 -- 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--TO_NUMBER (SUBSTRB (USERENV ('CLIENT_INFO'), 1, 10)) -- org_id
,l_utilization_rec.adjustment_type_id,l_utilization_rec.camp_schedule_id
,l_utilization_rec.gl_date, l_utilization_rec.gl_posted_flag
,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_utilization_rec.plan_curr_amount,l_utilization_rec.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
-- 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
*/
,l_utilization_rec.order_line_id
,l_utilization_rec.orig_utilization_id
-- added by rimehrot 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
--nirprasa, ER 10216374/9447673
,l_utilization_rec.cost_price
,l_utilization_rec.cost_price_currency_code
--kdass - bug 9470625
,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 12657908
);
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
,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_utilization_rec.adjustment_desc -- ADJUSTMENT_DESCRIPTION
,USERENV ('LANG') -- SOURCE_LANGUAGE
,l.language_code -- LANGUAGE
,l_utilization_rec.org_id --TO_NUMBER (SUBSTRB (USERENV ('CLIENT_INFO'), 1, 10)) -- org_id
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);
UPDATE ozf_funds_utilized_all_b
SET amount = amount+l_utilization_rec.amount, amount_remaining = amount_remaining+l_utilization_rec.amount_remaining,
plan_curr_amount = plan_curr_amount+l_utilization_rec.plan_curr_amount,
plan_curr_amount_remaining = plan_curr_amount_remaining+l_utilization_rec.plan_curr_amount_remaining,
acctd_amount = acctd_amount+l_utilization_rec.acctd_amount,
acctd_amount_remaining = acctd_amount_remaining+l_utilization_rec.acctd_amount_remaining,
fund_request_amount = fund_request_amount+l_utilization_rec.fund_request_amount,
fund_request_amount_remaining = fund_request_amount_remaining+l_utilization_rec.fund_request_amount_remaining,
univ_curr_amount = univ_curr_amount+l_utilization_rec.univ_curr_amount,
univ_curr_amount_remaining = univ_curr_amount_remaining+l_utilization_rec.univ_curr_amount_remaining,
exchange_rate_type = l_utilization_rec.exchange_rate_type, exchange_rate_date = G_FAE_START_DATE,
exchange_rate = l_utilization_rec.exchange_rate, 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_obj_ver_num + 1
WHERE utilization_id = l_utilization_id
AND object_version_number = l_obj_ver_num;
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),
creation_date = SYSDATE, created_by = NVL (fnd_global.user_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,
org_id = l_utilization_rec.org_id
WHERE utilization_id = l_utilization_id;
UPDATE ozf_act_budgets
SET request_amount = NVL(request_amount, 0) + l_utilization_rec.plan_curr_amount
, src_curr_request_amt = NVL(src_curr_request_amt, 0) + l_utilization_rec.amount
, approved_amount = NVL(approved_amount, 0) + l_utilization_rec.fund_request_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;
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_utilization_rec.plan_curr_amount,0)
, univ_curr_amount = NVL(univ_curr_amount, 0) + NVL(l_utilization_rec.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;*/
UPDATE ozf_funds_all_b
SET original_budget = l_original_budget,
utilized_amt = l_utilized_amt,
earned_amt = l_earned_amt,
paid_amt = l_paid_amt,
object_version_number = l_obj_num + 1
,rollup_original_budget = l_rollup_orig_amt
,rollup_utilized_amt = l_rollup_utilized_amt
,rollup_earned_amt = l_rollup_earned_amt
,rollup_paid_amt = l_rollup_paid_amt
-- yzhao: 10/14/2003 Fix TEVA bug - customer fully accrual budget committed amount is always 0 even when accrual happens
,committed_amt = l_committed_amt
,rollup_committed_amt = l_rollup_committed_amt
,recal_committed = l_recal_committed
,rollup_recal_committed = l_rollup_recal_committed
WHERE fund_id = l_utilization_rec.fund_id
AND object_version_number = l_obj_num;
UPDATE ozf_funds_all_b
SET object_version_number = fund.object_version_number + 1
,rollup_original_budget = NVL(fund.rollup_original_budget,0) + NVL(l_new_orig_amt,0)
,rollup_earned_amt = NVL(fund.rollup_earned_amt,0) + NVL(l_new_earned_amt,0)
,rollup_paid_amt = NVL(fund.rollup_paid_amt,0) + NVL(l_new_paid_amt,0)
-- yzhao: 10/14/2003 Fix TEVA bug - customer fully accrual budget committed amount is always 0 even when accrual happens
,rollup_committed_amt = NVL(fund.rollup_committed_amt, 0) + NVL(l_new_committed_amt, 0)
,rollup_recal_committed = NVL(fund.rollup_recal_committed, 0) + NVL(l_new_recal_committed, 0)
-- yzhao: 11.5.10
,rollup_utilized_amt = NVL(fund.rollup_utilized_amt,0) + NVL(l_new_utilized_amt,0)
WHERE fund_id = fund.fund_id
AND object_version_number = fund.object_version_number;
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,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
UPDATE ozf_mc_transactions_all
SET amount_column1 =l_mc_col_1,
amount_column6 =l_mc_col_6, -- yzhao: 10/14/2003
amount_column7 =l_mc_col_7,
amount_column8 =l_mc_col_8,
amount_column9 =l_mc_col_9, -- yzhao: 11.5.10 for utilized_amt
object_version_number = l_mc_obj_num + 1
WHERE mc_record_id = l_mc_record_id
AND object_version_number = l_mc_obj_num;
SELECT sold_to_org_id
FROM oe_order_headers_all
WHERE header_id = p_header_id;
SELECT cust.cust_account_id, header.invoice_to_org_id, header.ship_to_org_id
FROM hz_cust_acct_sites_all acct_site,
hz_cust_site_uses_all site_use,
hz_cust_accounts cust,
oe_order_headers_all header
WHERE header.header_id = p_header_id
AND acct_site.cust_acct_site_id = site_use.cust_acct_site_id
AND acct_site.cust_account_id = cust.cust_account_id
AND site_use.site_use_id = header.invoice_to_org_id ;
SELECT org_id FROM oe_order_headers_all
WHERE header_id = p_order_header_id;
SELECT qp.orig_org_id offer_org_id
FROM qp_list_headers_all qp, ozf_offers off
WHERE qp.list_header_id = p_list_header_id
AND qp.list_header_id = off.qp_list_header_id;
SELECT creation_date
FROM oe_price_adjustments adj
WHERE adj.price_Adjustment_id = p_price_adjustment_id;
SELECT cust.cust_account_id, header.invoice_to_org_id, header.ship_to_org_id
FROM hz_cust_acct_sites_all acct_site,
hz_cust_site_uses_all site_use,
hz_cust_accounts cust,
oe_order_headers_all header
WHERE header.header_id = p_header_id
AND acct_site.cust_acct_site_id = site_use.cust_acct_site_id
AND acct_site.cust_account_id = cust.cust_account_id
AND site_use.site_use_id = header.invoice_to_org_id ;
SELECT header.org_id
FROM oe_order_lines_all line, oe_order_headers_all header
WHERE line_id = p_line_id
AND line.header_id = header.header_id;
SELECT beneficiary_account_id,
autopay_party_attr,autopay_party_id,transaction_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 org_id
FROM hz_cust_site_uses_all
WHERE site_use_id = p_site_use_id;
l_fund_amt_tbl.DELETE;
SELECT line.inventory_item_id,
line.ordered_quantity,
line.cancelled_quantity,
line.line_category_code,
header.transactional_curr_code,
header.org_id,
line.shipping_quantity, -- Catch Weight ER
line.shipping_quantity_uom, -- Catch Weight ER
line.shipping_quantity2, -- Catch Weight ER
line.shipping_quantity_uom2, -- Catch Weight ER
line.fulfillment_base, -- Catch Weight ER
line.order_quantity_uom -- Catch Weight ER
FROM oe_order_lines_all line, oe_order_headers_all header
WHERE line_id = p_line_id
AND line.header_id = header.header_id;
SELECT estim_gl_value
FROM qp_list_lines
WHERE list_line_id = p_list_line_id;
SELECT SUM (plan_curr_amount)
FROM ozf_funds_utilized_all_b
WHERE price_adjustment_id = p_price_adjustment_id
AND object_type = 'ORDER';
SELECT SUM (ordered_quantity - NVL (cancelled_quantity, 0))
FROM oe_order_lines_all
WHERE header_id = p_header_id;
SELECT modifier_level_code,range_break_quantity, adjusted_amount, operand, arithmetic_operator --ER9447673
FROM oe_price_adjustments
WHERE price_adjustment_id = p_price_ad_id;
SELECT NVL (amount, 0) amount,
fund_id,
currency_code,
NVL (plan_curr_amount, 0) plan_curr_amount,
gl_posted_flag, product_id,component_type,utilization_type,
NVL (fund_request_amount, 0) fund_request_amount,plan_currency_code,fund_request_currency_code --nirprasa, ER 8399134, multi-currency enhancement
FROM ozf_funds_utilized_all_b
WHERE price_adjustment_id = p_price_adjustment_id
AND object_type = 'ORDER';
SELECT object_version_number,
plan_type, utilization_type,
amount,
fund_id,
acctd_amount,
plan_id,
fund_request_amount
FROM ozf_funds_utilized_all_b
WHERE utilization_id = p_utilization_id;
SELECT nvl(transaction_currency_code,fund_request_curr_code) offer_currency_code,
transaction_currency_code
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;
SELECT 1
FROM ozf_funds_utilized_all_b
WHERE plan_type = 'OFFR'
AND plan_id = p_list_header_id
AND object_type = 'ORDER'
AND object_id = p_header_id
AND order_line_id = p_line_id
AND utilization_type = 'ADJUSTMENT'
AND price_adjustment_id IS NULL;
SELECT line_id
FROM oe_order_lines_all
WHERE split_from_line_id IS NOT NULL
AND line_id = p_line_id
AND split_by = 'SYSTEM';
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_id;
SELECT custom_setup_id, description
FROM ozf_offers off, qp_list_headers_all qp
WHERE off.qp_list_header_id = p_qp_list_header_id
AND off.qp_list_header_id = qp.list_header_id;
SELECT 1
FROM oe_order_lines_all
WHERE split_from_line_id = p_line_id;
l_adj_amt_tbl.DELETE;
l_operation := 'UPDATE' ;
ELSIF p_line_adj_tbl (i).operation = 'UPDATE' AND p_line_adj_tbl (i).applied_flag = 'N' THEN
l_operation := 'DELETE';
IF l_operation <> 'DELETE' THEN
OPEN c_mod_level (p_line_adj_tbl (i).price_adjustment_id);
l_stmt := 'SELECT ' || l_column_name ||' FROM oe_order_lines_all WHERE line_id = :1 AND org_id = :2';
ELSIF l_operation = 'UPDATE' THEN
IF g_debug_flag = 'Y' THEN
ozf_utility_pvt.write_conc_log ('operation UPDATE');
l_stmt := 'SELECT ' || l_column_name ||' FROM oe_order_lines_all WHERE line_id = :1 AND org_id = :2';
ozf_utility_pvt.write_conc_log (' D: Update earned amount '|| l_earned_amount);
ELSIF l_operation = 'DELETE' AND p_line_adj_tbl (i).price_adjustment_id IS NOT NULL THEN
IF g_debug_flag = 'Y' THEN
ozf_utility_pvt.write_conc_log ('operation DELETE');
SELECT party_id
FROM hz_cust_accounts
WHERE cust_account_id = p_cust_account_id;
SELECT a.party_site_id
FROM hz_cust_acct_sites_all a,
hz_cust_site_uses_all b
WHERE b.site_use_id = p_account_site_id
AND b.cust_acct_site_id = a.cust_acct_site_id;
SELECT 1 FROM DUAL WHERE EXISTS
( SELECT 1
FROM ozf_sales_transactions_all trx
WHERE trx.line_id = p_line_id
AND source_code = 'OM');
SELECT flow_status_code,
booked_flag,
transactional_curr_code,
order_number,
org_id
FROM oe_order_headers_all
WHERE header_id = p_header_id;
SELECT price_adjustment_id,
list_header_id,
adjusted_amount, -- yzhao: 03/21/2003 added following 2 for shipped order
header_id,
operand,
arithmetic_operator
FROM oe_price_adjustments
WHERE line_id = p_line_id;
SELECT sum(plan_curr_amount) plan_curr_amount, sum(amount) amount,
sum(fund_request_amount) fund_request_amount, ----nirprasa, ER 8399134
fund_id,currency_code,
'N' gl_posted_flag,min(plan_id) plan_id,
utilization_type,adjustment_type,
price_adjustment_id,orig_utilization_id,
exchange_rate_type, --nirprasa, added for LGE enhancement
plan_currency_code, ----nirprasa, ER 8399134
fund_request_currency_code ----nirprasa, ER 8399134
FROM ozf_funds_utilized_all_b
WHERE price_adjustment_id = p_price_adjustment_id
AND object_type = 'ORDER'
AND NVL(gl_posted_flag,'N') <> 'Y'
GROUP BY fund_id,
currency_code,
gl_posted_flag,
utilization_type,
adjustment_type,
price_adjustment_id,
orig_utilization_id,
exchange_rate_type,
plan_currency_code,
fund_request_currency_code;
SELECT SUM (plan_curr_amount) -- change to plan_curr_amount from acct_amount by feliu
FROM ozf_funds_utilized_all_b
WHERE price_adjustment_id = p_price_adjustment_id
AND object_type = 'ORDER'
AND utilization_type NOT IN ('ADJUSTMENT', 'LEAD_ADJUSTMENT'); -- remove adjustment amount on 08/03/04 by feliu
SELECT utilization_id, object_version_number, plan_type, utilization_type, amount
, fund_id, acctd_amount, fund_request_amount, plan_id,org_id
, exchange_rate_type, exchange_rate_date
, currency_code, plan_currency_code, fund_request_currency_code
, plan_curr_amount, plan_curr_amount_remaining
, univ_curr_amount
FROM ozf_funds_utilized_all_b
WHERE price_adjustment_id IN (SELECT price_adjustment_id
FROM oe_price_adjustments
WHERE line_id = p_line_id)
AND gl_posted_flag = G_GL_FLAG_NO -- 'N'
AND object_type = 'ORDER'
-- 05/11/2004 kdass fixed bug 3609771 - added UTILIZED to query
AND utilization_type in ('ACCRUAL', 'LEAD_ACCRUAL','SALES_ACCRUAL')
UNION ALL -- added for bug 5485334 kpatro
select utilization_id, object_version_number, plan_type, utilization_type, amount
, fund_id, acctd_amount, plan_curr_amount, plan_id,org_id
, exchange_rate_type, exchange_rate_date
, currency_code, plan_currency_code, fund_request_currency_code
, plan_curr_amount, plan_curr_amount_remaining
, univ_curr_amount
from ozf_funds_utilized_all_b
where object_type = 'ORDER'
and order_line_id = p_line_id
AND gl_posted_flag = G_GL_FLAG_NO
AND utilization_type IN ('ADJUSTMENT','LEAD_ADJUSTMENT')
AND (price_adjustment_id IS NULL or (price_adjustment_id =-1 and orig_utilization_id<>-1)); --added for bug 6021635 nirprasa
SELECT actual_shipment_date, shipping_quantity, shipping_quantity_uom, shipping_quantity2, shipping_quantity_uom2, fulfillment_base
FROM oe_order_lines_all
WHERE line_id = p_line_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.interface_line_attribute1 = p_order_number -- added condition for partial index for bug fix 3917556
AND cust_lines.interface_line_attribute6 = TO_CHAR(p_line_id)
AND cust_lines.interface_line_context = 'ORDER ENTRY';
SELECT line_id
FROM oe_order_lines_all
WHERE header_id = p_header_id
AND split_from_line_id = p_line_id
AND split_by = 'SYSTEM';
SELECT price_adjustment_id
FROM oe_price_adjustments
WHERE line_id = p_line_id
AND list_header_id = p_header_id;
SELECT max(utilization_id)
FROM ozf_funds_utilized_all_b
WHERE price_adjustment_id = p_price_adj_id
AND object_type = 'ORDER';
SELECT NVL(shipped_quantity,ordered_quantity)
FROM oe_order_lines_all
WHERE line_id =p_line_id;
SELECT plan_curr_amount, amount, fund_request_amount, --nirprasa, ER 8399134
fund_id,currency_code,
gl_posted_flag,plan_id,
utilization_type,price_adjustment_id,
adjustment_type,orig_utilization_id,
plan_currency_code,fund_request_currency_code --nirprasa, ER 8399134
FROM ozf_funds_utilized_all_b
WHERE order_line_id = p_order_line_id
AND adjustment_type_id IN(-4,-5);
SELECT nvl(transaction_currency_code, fund_request_curr_code) offer_currency,
transaction_currency_code
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;
SELECT SUM (plan_curr_amount) -- change to plan_curr_amount from acct_amount by feliu
FROM ozf_funds_utilized_all_b
WHERE price_adjustment_id = -1
and order_line_id=p_order_line_id
AND object_type = 'ORDER'
AND utilization_type IN ('ADJUSTMENT', 'LEAD_ADJUSTMENT'); --
SELECT plan_curr_amount, amount,
fund_id,currency_code,
gl_posted_flag,plan_id,
utilization_type,price_adjustment_id,
adjustment_type,orig_utilization_id
FROM ozf_funds_utilized_all_b
WHERE price_adjustment_id = -1
and order_line_id=p_order_line_id
AND object_type = 'ORDER'
AND utilization_id=(
SELECT max(utilization_id)
FROM ozf_funds_utilized_all_b
WHERE price_adjustment_id = -1
and order_line_id=p_order_line_id
AND object_type = 'ORDER');
SELECT DECODE(line.line_category_code,'ORDER',line.ordered_quantity,
'RETURN', -line.ordered_quantity) ordered_quantity,
DECODE(line.line_category_code,'ORDER',NVL(line.shipped_quantity,0),
'RETURN', line.invoiced_quantity,
line.ordered_quantity) shipped_quantity
FROM oe_order_lines_all line, oe_order_headers_all header
WHERE line.line_id = p_order_line_id
AND line.header_id = header.header_id;
SELECT price_adjustment_id , plan_id
FROM ozf_funds_utilized_all_b
WHERE order_line_id = p_line_id;
SELECT custom_setup_id
FROM ozf_offers
WHERE qp_list_header_id = p_qp_list_header_id;
SELECT discount_type,volume_type
FROM ozf_offer_discount_lines
WHERE offer_discount_line_id = p_discount_line_id
AND tier_type = 'PBH';
SELECT group_no,pbh_line_id,include_volume_flag
FROM ozf_order_group_prod
WHERE order_line_id = p_order_line_id
AND qp_list_header_id = p_list_header_id;
SELECT opt.retroactive_flag
FROM ozf_offr_market_options opt
WHERE opt.GROUP_NUMBER= p_group_id
AND opt.qp_list_header_id = p_list_header_id;
SELECT discount
FROM ozf_offer_discount_lines
WHERE p_volume > volume_from
AND p_volume <= volume_to
AND parent_discount_line_id = p_parent_discount_id;
SELECT MIN(volume_from),MAX(volume_to)
FROM ozf_offer_discount_lines
WHERE parent_discount_line_id = p_parent_discount_id;
SELECT discount
FROM ozf_offer_discount_lines
WHERE volume_to =p_max_volume_to
AND parent_discount_line_id = p_parent_discount_id;
SELECT SUM(adjusted_amount_per_pqty)
FROM oe_price_adjustments
WHERE line_id = p_order_line_id
AND accrual_flag = 'N'
AND applied_flag = 'Y'
-- AND list_line_type_code IN ('DIS', 'SUR', 'PBH', 'FREIGHT_CHARGE');
SELECT exchange_rate_date,exchange_rate_type
FROM ozf_funds_utilized_all_b
WHERE utilization_id = p_utilization_id;
one line for shipped: quantity = 8, with old price adjustment id, line operation=UPDATE
another line for backorder: quantity = 2(10-8), with new price adjustment id, line operation=CREATE
handle case for partial ship with running accrual engine before ship. added by fliu on 05/24/04 to fix bug 3357164
If running accrual engine after booking order, one record is created. then partial shipped, two new records will be created. one with positive
for backordered amount. another with negative for adjustment from previous record.
*/
IF p_line_tbl(i).line_id= p_old_line_tbl(i).line_id
AND p_old_line_tbl(i).ordered_quantity <>p_line_tbl(i).shipped_quantity
AND NVL(p_line_tbl(i).shipped_quantity,0) <> 0
--AND p_line_tbl(i).flow_status_code = 'SHIPPED'
THEN
IF g_debug_flag = 'Y' THEN
ozf_utility_pvt.write_conc_log(' D: adjusted_changed_order: partial shipment line(line_id=' || p_line_tbl(i).line_id || ')');
l_stmt := 'SELECT ' || l_column_name ||' FROM oe_order_lines_all WHERE line_id = :1';
OZF_UTILITY_PVT.write_conc_log(': update gl posted flag to F since invoice is not created yet');
UPDATE ozf_funds_utilized_all_b futb
SET gl_posted_flag = 'F'
,gl_date = NULL
WHERE futb.order_line_id = p_line_tbl(i).line_id
AND futb.gl_posted_flag <> 'Y' --bug 13517522 - added this condition so that duplicate ASO message should not re-post accrual to GL
AND EXISTS(select price_adjustment_id from oe_price_adjustments where price_adjustment_id = futb.price_adjustment_id); --ninarasi fix for bug 14750730/14695150
UPDATE ozf_funds_utilized_all_b
SET gl_date = l_gl_date,
year_id = (select ent_year_id FROM OZF_TIME_ENT_YEAR
WHERE l_gl_date between start_date and end_date)
WHERE utilization_id = l_utilIdTbl(t_i);
ELSE--if amount is zero then only update gl_posted_flag to Y in ozf_funds_utilized_all and do not insert record to ozf_ae_lines_all
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)
, object_version_number = l_objVerTbl(t_i) + 1
, gl_posted_flag = G_GL_FLAG_YES
WHERE utilization_id = l_utilIdTbl(t_i)
AND object_version_number = l_objVerTbl(t_i);
l_budget_data := DBMS_XMLGEN.getXml('SELECT ''FUND'' AccrualType,
util.utilization_id UtilizationId,
util.fund_id FundId,
NVL(map.xref_line_id_value,oe.list_line_id) DiscountLineId,
util.product_id ProductId,
util.object_id OrderId,
util.order_line_id OrderLineId,
NVL(line.invoiced_quantity, NVL(line.shipped_quantity, 0)) ShippedQuantity,
(NVL(line.invoiced_quantity, NVL(line.shipped_quantity, 0))*line.unit_selling_price) Revenue,
NVL(util.plan_curr_amount,0) AccrualAmount
FROM oe_order_lines_all line, ozf_funds_utilized_all_b util,
ozf_offers off, oe_price_adjustments oe,
ozf_xref_map map
WHERE line.line_id = util.order_line_id
AND line.header_id = util.object_id
AND util.object_type = ''ORDER''
AND util.request_id = fnd_global.conc_request_id
AND util.plan_type = ''OFFR''
AND util.plan_id = off.qp_list_header_id
AND util.price_adjustment_id = oe.price_adjustment_id
AND util.gl_posted_flag IN (''Y'', ''X'')
AND oe.list_line_id = map.list_line_id(+)
AND NVL(line.invoiced_quantity, NVL(line.shipped_quantity, 0)) <> 0
AND NVL(off.budget_offer_yn, ''N'') = ''Y''', 0);
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,oe.list_line_id) DiscountLineId,
util.product_id ProductId,
util.object_id OrderId,
util.order_line_id OrderLineId,
util.cust_account_id CustomerId,
NVL(line.invoiced_quantity, NVL(line.shipped_quantity, 0)) ShippedQuantity,
(NVL(line.invoiced_quantity, NVL(line.shipped_quantity, 0))*line.unit_selling_price) Revenue,
NVL(util.plan_curr_amount,0) AccrualAmount
FROM oe_order_lines_all line, ozf_funds_utilized_all_b util,
ozf_offers off, oe_price_adjustments oe,
ozf_xref_map map
WHERE line.line_id = util.order_line_id
AND line.header_id = util.object_id
AND util.object_type = ''ORDER''
AND util.request_id = fnd_global.conc_request_id
AND util.price_adjustment_id = oe.price_adjustment_id
AND util.plan_type = ''OFFR''
AND util.plan_id = off.qp_list_header_id
AND util.price_adjustment_id = oe.price_adjustment_id
AND util.gl_posted_flag = ''Y''
AND NVL(line.invoiced_quantity, NVL(line.shipped_quantity, 0)) <> 0
AND oe.list_line_id = map.list_line_id(+)
AND NVL(off.budget_offer_yn, ''N'') = ''N''', 0);
SELECT offer_type
FROM ozf_offers a, ozf_funds_utilized_all_b b
WHERE a.qp_list_header_id = b.plan_id
AND b.utilization_id = p_util_id ;
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_id;
SELECT object_version_number, parent_fund_id, currency_code_tc,liability_flag,accrual_basis
FROM ozf_funds_all_b
WHERE fund_id = p_fund_id;
SELECT mc_record_id
,object_version_number
FROM ozf_mc_transactions_all
WHERE source_object_name ='FUND'
AND source_object_id = p_fund_id;
SELECT fund_id
,object_version_number
FROM ozf_funds_all_b
connect by prior parent_fund_id =fund_id
start with fund_id = p_fund_id;
SELECT objfundsum_id
, object_version_number
, earned_amt
, paid_amt
, plan_curr_earned_amt
, plan_curr_paid_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 NVL(sob.gl_acct_for_offinv_flag, 'F')
FROM ozf_sys_parameters_all sob
WHERE sob.org_id = p_org_id;
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)
, object_version_number = p_util_object_version_number + 1
, gl_posted_flag = l_gl_posted_flag
, exchange_rate_date = gl_date
--, gl_date = sysdate
WHERE utilization_id = p_util_utilization_id
AND object_version_number = p_util_object_version_number;
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,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
UPDATE ozf_funds_all_b
SET original_budget = NVL(original_budget, 0) + NVL(l_orig_amt, 0)
,rollup_original_budget = NVL(rollup_original_budget, 0) + NVL(l_rollup_orig_amt, 0)
,earned_amt = NVL(earned_amt, 0) + NVL(l_earned_amt, 0)
,paid_amt = NVL(paid_amt, 0 ) + NVL(l_paid_amt, 0)
,rollup_earned_amt = NVL(rollup_earned_amt, 0) + NVL(l_rollup_earned_amt, 0)
,rollup_paid_amt = NVL(rollup_paid_amt, 0) + NVL(l_rollup_paid_amt, 0)
,object_version_number = l_obj_num + 1
WHERE fund_id = p_util_fund_id
AND object_version_number = l_obj_num;
UPDATE ozf_funds_all_b
SET object_version_number = fund.object_version_number + 1
,rollup_earned_amt = NVL(rollup_earned_amt,0) + NVL(l_new_univ_amt,0)
,rollup_paid_amt = NVL(rollup_paid_amt,0) + NVL(l_rollup_paid_amt,0)
,rollup_original_budget = NVL(rollup_original_budget,0) + NVL(l_rollup_orig_amt,0)
WHERE fund_id = fund.fund_id
AND object_version_number = fund.object_version_number;
UPDATE ozf_mc_transactions_all
SET amount_column7 = NVL(amount_column7, 0) + NVL(p_util_acctd_amount,0),
amount_column8 = NVL(amount_column8, 0) + NVL(l_mc_col_8, 0),
object_version_number = l_obj_num + 1
WHERE mc_record_id = l_mc_record_id
AND object_version_number = l_obj_num;
SELECT utilization_id, object_version_number,
plan_type, utilization_type,
amount, fund_id, acctd_amount, fund_request_amount, plan_id,org_id, gl_date, object_id, order_line_id
FROM ozf_funds_utilized_all_b
WHERE plan_type IN ( 'OFFR' , 'PRIC') -- yzhao: 10/20/2003 PRICE_LIST is changed to PRIC
-- AND utilization_type = 'ACCRUAL' yzhao: 01/29/2004 11.5.10 off-invoice offer, LEAD_ACCRUAL may post to GL too
AND gl_posted_flag = G_GL_FLAG_FAIL; -- 'F';
SELECT trunc(cust.trx_date) -- transaction(invoice) date
FROM ra_customer_trx_all cust,
ra_customer_trx_lines_all cust_lines,
oe_order_headers_all oeh
WHERE cust.customer_trx_id = cust_lines.customer_trx_id
AND cust_lines.interface_line_attribute1 = oeh.order_number
AND cust_lines.interface_line_attribute6 = TO_CHAR(p_lineId)
AND cust_lines.interface_line_context = 'ORDER ENTRY'
AND oeh.header_id = p_headerId;
UPDATE ozf_funds_utilized_all_b
SET gl_date = l_inv_date
WHERE utilization_id = l_utilIdTbl(i);
SELECT utilization_id, object_version_number, plan_type, utilization_type, amount
, fund_id, acctd_amount, fund_request_amount, plan_id
,org_id, exchange_rate_type, exchange_rate_date
, currency_code, plan_currency_code, fund_request_currency_code
, plan_curr_amount, plan_curr_amount_remaining
, univ_curr_amount,object_id, price_adjustment_id
FROM ozf_funds_utilized_all_b
WHERE utilization_type = 'UTILIZED'
AND gl_posted_flag = 'N'
AND object_type = 'ORDER'
AND price_adjustment_id IS NOT NULL;
SELECT customer_trx_line_id, cust.trx_date
FROM ra_customer_trx_all cust
, ra_customer_trx_lines_all cust_lines
, oe_price_adjustments price
WHERE price.price_adjustment_id = p_price_adjustment_id
AND cust_lines.customer_trx_line_id IS NOT NULL
AND interface_line_context = 'ORDER ENTRY'
AND cust_lines.interface_line_attribute6 = TO_CHAR(price.line_id)
AND cust_lines.interface_line_attribute1 = p_order_number -- added for partial index; performance bug fix 3917556
SELECT order_number
FROM oe_order_headers_all
WHERE header_id = p_header_id;
UPDATE ozf_funds_utilized_all_b
SET gl_date = l_gl_date,
year_id = (select ent_year_id FROM OZF_TIME_ENT_YEAR
WHERE l_gl_date between start_date and end_date)
WHERE utilization_id = l_utilIdTbl(t_i);
SELECT utilization_id, object_version_number, plan_type, utilization_type, amount
, fund_id, acctd_amount, fund_request_amount, plan_id,org_id
FROM ozf_funds_utilized_all_b
WHERE gl_posted_flag = G_GL_FLAG_NO --// OR gl_posted_flag = G_GL_FLAG_FAIL) Commented for Bugfix 13004854
AND orig_utilization_id = p_utilization_id;
UPDATE ozf_funds_utilized_all_b
SET gl_date = p_gl_date,
year_id = (select ent_year_id FROM OZF_TIME_ENT_YEAR
WHERE p_gl_date between start_date and end_date)
WHERE utilization_id = l_utilIdTbl(i);
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_id;
SELECT objfundsum_id
, object_version_number
, utilized_amt
, earned_amt
, paid_amt
, plan_curr_utilized_amt
, plan_curr_earned_amt
, plan_curr_paid_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 fund_id
,object_version_number
FROM ozf_funds_all_b
connect by prior parent_fund_id =fund_id
start with fund_id = p_fund_id;
SELECT object_version_number, parent_fund_id,liability_flag,accrual_basis
FROM ozf_funds_all_b
WHERE fund_id = p_fund_id;
SELECT activity_budget_id
, object_version_number
FROM ozf_act_budgets
WHERE transfer_type = 'UTILIZED'
AND status_code = 'APPROVED'
AND act_budget_used_by_id = p_plan_id;
UPDATE ozf_funds_utilized_all_b
SET amount = l_conv_amount, amount_remaining = l_conv_amount_remg,
acctd_amount = l_conv_acctd_amount, acctd_amount_remaining = l_conv_acctd_amount_remg,
fund_request_amount = l_conv_fund_req_amount, fund_request_amount_remaining = l_conv_fund_req_amount_remg,
univ_curr_amount = l_conv_univ_amount, univ_curr_amount_remaining = l_conv_univ_amount_remg,
exchange_rate_type = l_exchange_rate_type,exchange_rate_date = p_exchange_rate_date,exchange_rate = l_exchange_rate
WHERE utilization_id = p_util_utilization_id;
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,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
UPDATE ozf_act_budgets
SET src_curr_request_amt = NVL(src_curr_request_amt, 0) - p_amount + l_conv_amount
, approved_original_amount = NVL(approved_original_amount, 0) - NVL(p_fund_req_amount, 0)
+ NVL(l_conv_fund_req_amount,0)
, approved_amount_fc = NVL(approved_amount_fc, 0) - NVL(p_acctd_amount,0)
+ NVL(l_conv_acctd_amount,0)
, request_amount = NVL(request_amount, 0) - NVL(p_fund_req_amount, 0)
+ NVL(l_conv_fund_req_amount, 0)
, approved_amount = NVL(approved_amount, 0) - NVL(p_fund_req_amount, 0)
+ NVL(l_conv_fund_req_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 activity_budget_id = l_act_budget_id
AND object_version_number = l_act_budget_objver;
UPDATE ozf_funds_all_b
SET original_budget = NVL(original_budget, 0) + l_orig_amt
,rollup_original_budget = NVL(rollup_original_budget, 0) + l_rollup_orig_amt
,earned_amt = NVL(earned_amt, 0) + NVL(l_conv_amount, 0)- NVL(p_amount, 0)
,paid_amt = NVL(paid_amt, 0 ) + NVL(l_paid_conv_amt, 0) - NVL(l_paid_amt, 0)
,rollup_earned_amt = NVL(rollup_earned_amt, 0) + l_rollup_orig_amt
,rollup_paid_amt = NVL(rollup_paid_amt, 0) + NVL(l_rollup_paid_amt, 0)
- NVL(l_rollup_paid_conv_amt, 0)
,object_version_number = l_obj_num + 1
WHERE fund_id = p_util_fund_id
AND object_version_number = l_obj_num;
UPDATE ozf_funds_all_b
SET object_version_number = fund.object_version_number + 1
,rollup_earned_amt = NVL(rollup_earned_amt,0) + l_rollup_orig_amt
,rollup_paid_amt = NVL(rollup_paid_amt,0) + NVL(l_rollup_paid_amt,0)
- NVL(l_rollup_paid_conv_amt, 0)
,rollup_original_budget = NVL(rollup_original_budget,0) + l_rollup_orig_amt
WHERE fund_id = fund.fund_id
AND object_version_number = fund.object_version_number;