[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;
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, 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 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 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;
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
)
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,SYSDATE
,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
);
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_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.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;
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 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
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
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
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
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, plan_curr_amount
FROM ozf_funds_utilized_all_b
WHERE utilization_id = p_utilization_id;
SELECT nvl(transaction_currency_code,fund_request_curr_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;
l_adj_amt_tbl.DELETE;
l_operation := 'UPDATE' ;
IF l_operation <> 'DELETE' THEN
OPEN c_mod_level (p_line_adj_tbl (i).price_adjustment_id);
ELSIF l_operation = 'UPDATE' THEN
IF g_debug_flag = 'Y' THEN
ozf_utility_pvt.write_conc_log ('operation UPDATE');
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
FROM oe_price_adjustments
WHERE line_id = p_line_id;
SELECT plan_curr_amount, amount,
fund_id,currency_code,
gl_posted_flag,plan_id,
utilization_type,price_adjustment_id,
adjustment_type,orig_utilization_id,
exchange_rate_type --nirprasa
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';
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, plan_curr_amount, plan_id,org_id
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
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
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.sales_order = p_order_number -- added condition for partial index for bug fix 3917556
AND cust_lines.interface_line_attribute6 = TO_CHAR(p_line_id);
SELECT line_id
FROM oe_order_lines_all
WHERE 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_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 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
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 offer_type
FROM ozf_offers
WHERE qp_list_header_id = p_offer_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');
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 || ')');
UPDATE ozf_funds_utilized_all_b
SET gl_date = l_gl_date
WHERE utilization_id = l_utilIdTbl(t_i);
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
--, 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, plan_curr_amount, plan_id,org_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 utilization_id, object_version_number,
plan_type, utilization_type,
amount, fund_id, acctd_amount, plan_curr_amount, plan_id
,org_id,object_id, price_adjustment_id
FROM ozf_funds_utilized_all_b
WHERE utilization_type = 'UTILIZED'
AND gl_posted_flag = G_GL_FLAG_NO -- '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.sales_order = 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
WHERE utilization_id = l_utilIdTbl(t_i);
SELECT utilization_id, object_version_number, plan_type, utilization_type, amount
, fund_id, acctd_amount, plan_curr_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)
AND orig_utilization_id = p_utilization_id;
UPDATE ozf_funds_utilized_all_b
SET gl_date = p_gl_date
WHERE utilization_id = l_utilIdTbl(i);