[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT trade_profile_id,
cust_account_id,
site_use_id,
payment_method,
vendor_id,
vendor_site_id,
last_paid_date,
autopay_periodicity,
autopay_periodicity_type,
autopay_flag,
claim_threshold,
claim_currency,
org_id
FROM ozf_cust_trd_prfls
WHERE site_use_id = p_id;
SELECT trade_profile_id,
cust_account_id,
site_use_id,
payment_method,
vendor_id,
vendor_site_id,
last_paid_date,
autopay_periodicity,
autopay_periodicity_type,
autopay_flag,
claim_threshold,
claim_currency,
org_id
FROM ozf_cust_trd_prfls
WHERE cust_account_id = p_id;
SELECT trade_profile_id,
cust_account_id,
site_use_id,
payment_method,
vendor_id,
vendor_site_id,
last_paid_date,
autopay_periodicity,
autopay_periodicity_type,
autopay_flag,
claim_threshold,
claim_currency,
org_id
FROM ozf_cust_trd_prfls
WHERE party_id = p_id
AND cust_account_id IS NULL;
PROCEDURE Delete_Line_Util(
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_line_util_id IN NUMBER
,p_object_version IN NUMBER
,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
,px_upd_line_util_tbl IN OUT NOCOPY upd_fund_util_tbl_type --ninarasi fix for bug 13530939
);
PROCEDURE Update_Line_Util(
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_line_util_rec IN line_util_rec_type
,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
,x_object_version OUT NOCOPY NUMBER
,px_upd_line_util_tbl IN OUT NOCOPY upd_fund_util_tbl_type --ninarasi fix for bug 13530939
);
PROCEDURE Update_Fund_Utils(
p_line_util_rec IN OUT NOCOPY line_util_rec_type
, p_asso_amount IN NUMBER
, p_mode IN VARCHAR2 := 'CALCULATE'
, px_currency_rec IN OUT NOCOPY currency_rec_type
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
,px_upd_line_util_tbl IN OUT NOCOPY upd_fund_util_tbl_type --ninarasi fix for bug 13530939
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := ' Update_Fund_Utils';
SELECT currency_code
, fund_request_currency_code
, exchange_rate_date
, exchange_rate_type
, NVL(acctd_amount_remaining,0)
, NVL(plan_curr_amount_remaining,0)
, reference_id
, plan_id
, plan_type
, plan_currency_code
FROM ozf_funds_utilized_all_b
WHERE utilization_id = cv_utilization_id;
SELECT cla.source_object_class
FROM ozf_claims cla
, ozf_claim_lines line
WHERE cla.claim_id = line.claim_id
AND line.activity_id = cv_request_id;
SELECT cla.source_object_class
FROM ozf_claims_all cla
, ozf_claim_lines_all line
WHERE cla.claim_id = line.claim_id
AND line.claim_line_id = cv_claim_line_id;
SELECT currency_code
FROM ozf_claim_lines
WHERE claim_line_id = cv_claim_line_id;
SAVEPOINT Update_Fund_Utils;
OZF_Utility_PVT.debug_message('Currencies at Update_Fund_Utils API...');
/*UPDATE ozf_funds_utilized_all_b
SET amount_remaining = amount_remaining - l_fu_amt_rem
, acctd_amount_remaining = acctd_amount_remaining - l_fu_acctd_amt_rem
, plan_curr_amount_remaining = plan_curr_amount_remaining - l_fu_plan_amt_rem
, univ_curr_amount_remaining = univ_curr_amount_remaining - l_fu_univ_amt_rem
, fund_request_amount_remaining = fund_request_amount_remaining - l_fu_req_amt_rem
WHERE utilization_id = l_line_util_rec.utilization_id;*/
ROLLBACK TO Update_Fund_Utils;
ROLLBACK TO Update_Fund_Utils;
ROLLBACK TO Update_Fund_Utils;
END Update_Fund_Utils;
,p_select_cust_children_flag IN VARCHAR2
)
IS
l_bg_is_parent_of_cust NUMBER := 0;
SELECT COUNT(seg.party_id)
FROM ams_party_market_segments seg
, hz_cust_accounts hca2
WHERE seg.market_qualifier_reference = cv_bg_party_id
AND hca2.party_id = seg.party_id
AND seg.market_qualifier_type = 'BG'
AND hca2.cust_account_id = cv_cust_account_id
AND seg.market_qualifier_reference <> seg.party_id;
IF p_select_cust_children_flag IS NULL OR
p_select_cust_children_flag = 'F' OR
p_select_cust_children_flag = 'N' THEN
-- not to include member earnings
IF p_buy_group_party_id IS NOT NULL THEN
FND_DSQL.add_text(' IN (SELECT c.cust_account_id FROM hz_cust_accounts c');
FND_DSQL.add_text(' IN (SELECT related_cust_account_id FROM hz_cust_acct_relate');
ELSIF p_select_cust_children_flag = 'T' OR p_select_cust_children_flag = 'Y' THEN
-- to include member earnings
IF p_buy_group_party_id IS NOT NULL THEN
-- if buying group is parent of customer, do not include members
OPEN csr_buy_group(p_buy_group_party_id, p_cust_account_id);
FND_DSQL.add_text(' IN (SELECT c.cust_account_id ');
FND_DSQL.add_text(' IN (SELECT c2.cust_account_id ');
FND_DSQL.add_text(' IN (SELECT cust2.cust_account_id FROM hz_cust_accounts cust2 ');
FND_DSQL.add_text(' WHERE cust2.party_id IN (SELECT seg.party_id from ');
SELECT ln.activity_type
, ln.activity_id
, ln.offer_type
, ln.source_object_class
, ln.source_object_id
, ln.item_type
, ln.item_id
, ln.relationship_type
, ln.related_cust_account_id
, ln.buy_group_party_id
, ln.select_cust_children_flag
, cla.cust_account_id
, ln.earnings_end_date
, ln.claim_currency_amount
, cla.source_object_class
, cla.source_object_id
FROM ozf_claim_lines ln
, ozf_claims cla
WHERE ln.claim_id = cla.claim_id
AND ln.claim_line_id = cv_claim_line_id;
SELECT offer_id
FROM ozf_request_headers_all_b
WHERE request_header_id = cv_request_id;
SELECT offer_type
FROM ozf_offers
WHERE qp_list_header_id = cv_offer_id;
SELECT offer_id
FROM ozf_sd_request_headers_all_b
WHERE request_header_id = cv_request_id;
SELECT NVL(ospa.ship_debit_accrual_flag, 'F')
FROM ozf_sys_parameters_all ospa,
ozf_resale_batches_all orba
WHERE ospa.org_id = orba.org_id
AND orba.resale_batch_id = p_resale_batch_id;
, l_line_util_flt.select_cust_children_flag
, l_line_util_flt.cust_account_id
, l_line_util_flt.end_date
, l_line_util_flt.total_amount
, l_reference_type
, l_reference_id;
IF px_funds_util_flt.select_cust_children_flag IS NULL THEN
px_funds_util_flt.select_cust_children_flag := l_line_util_flt.select_cust_children_flag;
NULL; -- If null, then leave as null implies line is to be deleted. Bugfix 5101106
SELECT currency_code
FROM ozf_claim_lines
where claim_line_id = cv_claim_line_id;
,p_select_cust_children_flag => l_funds_util_flt.select_cust_children_flag
);
FND_DSQL.add_text(' AND fu.object_id IN (SELECT resale_line_id FROM ozf_resale_lines WHERE resale_header_id = ');
FND_DSQL.add_text(' AND fu.product_id IN (select category_id from mtl_item_categories ');
FND_DSQL.add_text(' AND fu.product_id IN (select b.inventory_item_id from eni_denorm_hierarchies a, mtl_item_categories b ');
FND_DSQL.add_text(' AND (select start_date, end_date from ozf_time_ent_qtr where period_name = ');
FND_DSQL.add_text(' AND fu.year_id = SELECT ent_year_id FROM OZF_TIME_ENT_YEAR WHERE fu.gl_date between start_date and end_date ');
FND_DSQL.add_text(' AND fu.year_id = ((SELECT ent_year_id FROM OZF_TIME_ENT_YEAR WHERE fu.gl_date between start_date and end_date) - 1) ');
FND_DSQL.add_text('SELECT fu.utilization_id, fu.cust_account_id '||
', fu.plan_type, fu.plan_id, o.offer_type, o.autopay_method '||
', fu.product_level_type, fu.product_id, fu.acctd_amount_remaining, ');
FND_DSQL.add_text(' EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
FND_DSQL.add_text(' AND EXISTS ( SELECT 1 FROM ams_act_access_denorm act ');
FND_DSQL.add_text('SELECT fu.utilization_id, fu.cust_account_id '||
', fu.plan_type, fu.plan_id, null, null '||
', fu.product_level_type, fu.product_id '||
', fu.acctd_amount_remaining,' );
SELECT o.qp_list_header_id
, o.offer_type
FROM ozf_offers o
, ozf_request_headers_all_b r
WHERE o.qp_list_header_id = r.offer_id
AND r.request_header_id = cv_request_id;
SELECT gs.currency_code
FROM gl_sets_of_books gs
, ozf_sys_parameters org
WHERE org.set_of_books_id = gs.set_of_books_id
AND org.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code, bill_to_site_use_id ');
FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , cust_account_id ');
FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , plan_id ');
FND_DSQL.add_text('SELECT autopay_method, sum(amount_remaining), currency_code , bill_to_site_use_id , cust_account_id, plan_id ');
FND_DSQL.add_text( 'SELECT cust_account_id, plan_type, plan_id, bill_to_site_use_id '||
', product_level_type, product_id '||
', sum(amount_remaining), currency_code '||
'FROM ('||
'SELECT cust_account_id, plan_type, plan_id '||
', decode(product_id, null, null, product_level_type) product_level_type '||
', product_id product_id '||
', acctd_amount_remaining , amount_remaining, currency_code, bill_to_site_use_id '||
'FROM (');
FND_DSQL.add_text( 'SELECT lu.claim_line_util_id, lu.utilization_id, lu.amount, lu.scan_unit, lu.currency_code '||
'FROM (');
FND_DSQL.add_text( 'SELECT utilization_id, amount_remaining, scan_unit_remaining, currency_code, plan_currency_code , plan_curr_amount_remaining , plan_curr_amount , acctd_amount_remaining '|| --ninarasi fix for bug 13550004
'FROM (');
SELECT party_id
FROM hz_cust_accounts
WHERE cust_account_id = cv_cust_account_id;
SELECT CONCAT(CONCAT(party.party_name, ' ('), CONCAT(ca.account_number, ') '))
FROM hz_cust_accounts ca
, hz_parties party
WHERE ca.party_id = party.party_id
AND ca.cust_account_id = cv_cust_account_id;
SELECT 1 FROM po_vendors pv, po_vendor_sites pvs
WHERE pv.vendor_id = pvs.vendor_id
AND (sysdate between nvl(pv.start_date_active, sysdate-1) and nvl(pv.end_date_active, sysdate+1))
AND (pvs.inactive_date is null or pvs.inactive_date > trunc(sysdate))
AND pv.vendor_id = cv_vendor_id
AND pvs.vendor_site_id = cv_vendor_site_id;
SELECT prorate_earnings_flag
FROM ozf_claim_lines
WHERE claim_line_id = cv_claim_line_id;
SELECT NVL(prorate_earnings_flag, 'F')
FROM ozf_sys_parameters
WHERE org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
SELECT budget_source_id,
request_currency,
ARC_ACT_BUDGET_USED_BY,
ACT_BUDGET_USED_BY_ID,
APPROVED_IN_CURRENCY
FROM ozf_act_budgets
WHERE transfer_type = 'REQUEST'
AND act_budget_used_by_id = cv_offer_id
AND budget_source_id = cv_fund_id;
SELECT utilization_id
FROM ozf_funds_utilized_all_b
WHERE plan_type = 'OFFR'
AND org_id = l_currOrgId
AND plan_id = cv_offer_id
AND fund_id = cv_fund_id
AND adjustment_type_id IN ( -11, -1)
AND ( ( cv_product_id IS NULL AND product_id IS NULL )
OR product_id = cv_product_id )
AND ( (cv_product_level_type IS NULL AND product_level_type IS NULL )
OR product_level_type = cv_product_level_type )
AND utilization_type IN ( 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
AND cust_account_id = cv_cust_account_id;
SELECT cla.currency_code
, cla.exchange_rate_type
, cla.exchange_rate_date
, cla.exchange_rate
FROM ozf_claims cla
, ozf_claim_lines ln
WHERE ln.claim_id = cla.claim_id
AND ln.claim_line_id = cv_claim_line_id;
SELECT claim_id
FROM ozf_claim_lines
WHERE claim_line_id = cv_claim_line_id;
SELECT nvl(amount,0)
FROM ozf_claim_lines_util
WHERE claim_line_util_id = cv_claim_line_util_id;
SELECT gs.currency_code
FROM gl_sets_of_books gs
, ozf_sys_parameters org
WHERE org.set_of_books_id = gs.set_of_books_id
AND org.org_id = cv_org_id;
SELECT SUM(amount)
FROM ozf_claim_lines_util
WHERE claim_line_id = cv_claim_line_id;
SELECT COUNT(1),
MAX(claim_line_util_id)
FROM ozf_claim_lines_util_all
WHERE claim_line_id = cv_claim_line_id; */
l_line_util_rec.last_updated_by := NVL(FND_GLOBAL.user_id,-1);
l_line_util_rec.last_update_login := NVL(FND_GLOBAL.conc_login_id,-1);
UPDATE ozf_funds_utilized_all_b
SET amount_remaining = amount_remaining - l_upd_fund_util_tbl(i).amount_remaining
, acctd_amount_remaining = acctd_amount_remaining - l_upd_fund_util_tbl(i).acctd_amount_remaining
, plan_curr_amount_remaining = plan_curr_amount_remaining - l_upd_fund_util_tbl(i).plan_curr_amount_remaining
, univ_curr_amount_remaining = univ_curr_amount_remaining - l_upd_fund_util_tbl(i).univ_curr_amount_remaining
, fund_request_amount_remaining = fund_request_amount_remaining - l_upd_fund_util_tbl(i).fund_request_amount_remaining
WHERE utilization_id = l_upd_fund_util_tbl(i).utilization_id;
l_upd_fund_util_tbl.delete;
INSERT INTO ozf_claim_lines_util_all (
claim_line_util_id,
object_version_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
claim_line_id,
utilization_id,
amount,
currency_code,
exchange_rate_type,
exchange_rate_date,
exchange_rate,
acctd_amount,
util_curr_amount,
plan_curr_amount,
univ_curr_amount,
scan_unit,
activity_product_id,
uom_code,
quantity,
org_id,
fxgl_acctd_amount,
utilized_acctd_amount
)
VALUES (
l_line_util_tbl(i).claim_line_util_id,
l_line_util_tbl(i).object_version_number,
SYSDATE,
l_line_util_tbl(i).last_updated_by,
SYSDATE,
l_line_util_tbl(i).created_by,
l_line_util_tbl(i).last_update_login,
l_line_util_tbl(i).claim_line_id,
l_line_util_tbl(i).utilization_id,
l_line_util_tbl(i).amount,
l_line_util_tbl(i).currency_code,
l_line_util_tbl(i).exchange_rate_type,
l_line_util_tbl(i).exchange_rate_date,
l_line_util_tbl(i).exchange_rate,
l_line_util_tbl(i).acctd_amount,
l_line_util_tbl(i).util_curr_amount,
l_line_util_tbl(i).plan_curr_amount,
l_line_util_tbl(i).univ_curr_amount,
l_line_util_tbl(i).scan_unit,
l_line_util_tbl(i).activity_product_id,
l_line_util_tbl(i).uom_code,
l_line_util_tbl(i).quantity,
l_line_util_tbl(i).org_id,
l_line_util_tbl(i).fxgl_acctd_amount,
l_line_util_tbl(i).utilized_acctd_amount);
l_line_util_tbl.delete;
UPDATE ozf_claim_lines_util_all
SET amount = l_line_util_amount
WHERE claim_line_util_id = l_last_line_util_id;
UPDATE ozf_claim_lines_all
SET earnings_associated_flag = 'T'
WHERE claim_line_id = l_line_util_rec.claim_line_id;
SELECT ozf_claim_lines_util_all_s.NEXTVAL
FROM DUAL;
SELECT COUNT(claim_line_util_id)
FROM ozf_claim_lines_util
WHERE claim_line_util_id = cv_line_util_id;
OZF_Utility_PVT.debug_message('l_line_util_rec.amount - Before Update_Fund_Utils() : '||l_line_util_rec.amount);
Update_Fund_Utils(
p_line_util_rec => l_line_util_rec
, p_asso_amount => -NVL(l_line_util_rec.amount,0)
, p_mode => 'CALCULATE'
, px_currency_rec => l_currency_rec
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, px_upd_line_util_tbl => px_upd_line_util_tbl --ninarasi fix for bug 13530939
);
OZF_Utility_PVT.debug_message('l_line_util_rec.amount - After Update_Fund_Utils() : '||l_line_util_rec.amount);
OZF_Utility_PVT.debug_message('Inserting association record...');
/*INSERT INTO ozf_claim_lines_util_all (
claim_line_util_id,
object_version_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
claim_line_id,
utilization_id,
amount,
currency_code,
exchange_rate_type,
exchange_rate_date,
exchange_rate,
acctd_amount,
util_curr_amount,
plan_curr_amount,
univ_curr_amount,
scan_unit,
activity_product_id,
uom_code,
quantity,
org_id,
fxgl_acctd_amount,
utilized_acctd_amount
)
VALUES (
l_line_util_rec.claim_line_util_id,
l_line_util_rec.object_version_number,
SYSDATE,
l_line_util_rec.last_updated_by,
SYSDATE,
l_line_util_rec.created_by,
l_line_util_rec.last_update_login,
l_line_util_rec.claim_line_id,
l_line_util_rec.utilization_id,
l_line_util_rec.amount,
l_line_util_rec.currency_code,
l_line_util_rec.exchange_rate_type,
l_line_util_rec.exchange_rate_date,
l_line_util_rec.exchange_rate,
l_line_util_rec.acctd_amount,
l_line_util_rec.util_curr_amount,
l_line_util_rec.plan_curr_amount,
l_line_util_rec.univ_curr_amount,
l_line_util_rec.scan_unit,
l_line_util_rec.activity_product_id,
l_line_util_rec.uom_code,
l_line_util_rec.quantity,
l_line_util_rec.org_id,
l_line_util_rec.fxgl_acctd_amount,
l_line_util_rec.utilized_acctd_amount
);*/
PROCEDURE Update_Line_Util_Tbl(
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_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,p_line_util_tbl IN line_util_tbl_type
,p_mode IN VARCHAr2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
,x_error_index OUT NOCOPY NUMBER
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Line_Util_Tbl';
SELECT claim_id
FROM ozf_claim_lines
WHERE claim_line_id = cv_claim_line_id;
SAVEPOINT Update_Line_Util_Tbl;
l_line_util_rec.update_from_tbl_flag := FND_API.g_true;
Update_Line_Util(
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_line_util_rec => l_line_util_rec
, p_mode => OZF_CLAIM_UTILITY_PVT.g_auto_mode
, x_object_version => l_object_version
, px_upd_line_util_tbl => l_upd_fund_util_tbl
);
UPDATE ozf_funds_utilized_all_b
SET amount_remaining = amount_remaining - l_upd_fund_util_tbl(i).amount_remaining
, acctd_amount_remaining = acctd_amount_remaining - l_upd_fund_util_tbl(i).acctd_amount_remaining
, plan_curr_amount_remaining = plan_curr_amount_remaining - l_upd_fund_util_tbl(i).plan_curr_amount_remaining
, univ_curr_amount_remaining = univ_curr_amount_remaining - l_upd_fund_util_tbl(i).univ_curr_amount_remaining
, fund_request_amount_remaining = fund_request_amount_remaining - l_upd_fund_util_tbl(i).fund_request_amount_remaining
WHERE utilization_id = l_upd_fund_util_tbl(i).utilization_id;
l_upd_fund_util_tbl.delete;
ROLLBACK TO Update_Line_Util_Tbl;
ROLLBACK TO Update_Line_Util_Tbl;
ROLLBACK TO Update_Line_Util_Tbl;
END Update_Line_Util_Tbl;
PROCEDURE Update_Line_Util(
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_line_util_rec IN line_util_rec_type
,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
,x_object_version OUT NOCOPY NUMBER
,px_upd_line_util_tbl IN OUT NOCOPY upd_fund_util_tbl_type --ninarasi fix for bug 13530939
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Line_Util';
l_update_fund_amount NUMBER;
l_update_acctd_fund_amount NUMBER;
SELECT gs.currency_code
FROM gl_sets_of_books gs
, ozf_sys_parameters org
WHERE org.set_of_books_id = gs.set_of_books_id
AND org.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
SELECT claim_id, acctd_amount
FROM ozf_claim_lines
WHERE claim_line_id = cv_claim_line_id;
SELECT lu.amount
, lu.scan_unit
, lu.util_curr_amount
, lu.currency_code
, fu.plan_currency_code
FROM ozf_claim_lines_util lu
, ozf_funds_utilized_all_b fu
WHERE lu.claim_line_util_id = cv_line_util_id
AND fu.utilization_id = lu.utilization_id;
SELECT nvl(SUM(acctd_amount),0)
FROM ozf_claim_lines_util
WHERE claim_line_id = cv_claim_line_id;
SELECT cla.creation_date
FROM ozf_claims cla, ozf_claim_lines cl
WHERE cla.claim_id = cl.claim_id
AND cl.claim_line_id = cv_claim_line_id;
SAVEPOINT Update_Line_Util;
l_line_util_rec.last_updated_by := NVL(FND_GLOBAL.user_id,-1);
l_line_util_rec.last_update_login := NVL(FND_GLOBAL.conc_login_id,-1);
OZF_Utility_PVT.debug_message(l_full_name ||': update funds utilized');
l_update_fund_amount := l_lu_old_amt - l_line_util_rec.amount;
IF l_update_fund_amount IS NOT NULL and l_update_fund_amount <> 0 THEN
OZF_UTILITY_PVT.Convert_Currency
( p_from_currency => l_currency_rec.claim_currency_code
, p_to_currency => l_currency_rec.functional_currency_code
, p_conv_date => l_line_util_rec.exchange_rate_date
, p_conv_type => l_line_util_rec.exchange_rate_type
, p_from_amount => l_update_fund_amount
, x_return_status => l_return_status
, x_to_amount => l_update_acctd_fund_amount
, x_rate => l_rate
);
l_update_fund_amount := l_update_acctd_fund_amount;
OZF_Utility_PVT.debug_message('l_line_util_rec.amount1 - Before Update_Fund_Utils() : '||l_line_util_rec.amount);
OZF_Utility_PVT.debug_message('l_update_fund_amount : '|| l_update_fund_amount);
Update_Fund_Utils(
p_line_util_rec => l_line_util_rec
, p_asso_amount => NVL(l_update_fund_amount,0)
, p_mode => 'CALCULATE'
, px_currency_rec => l_currency_rec
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, px_upd_line_util_tbl => px_upd_line_util_tbl --ninarasi fix for bug 13530939
);
OZF_Utility_PVT.debug_message(l_full_name ||': update lines utilized');
UPDATE ozf_claim_lines_util_all SET
object_version_number = l_line_util_rec.object_version_number,
last_update_date = SYSDATE,
last_updated_by = l_line_util_rec.last_updated_by,
last_update_login = l_line_util_rec.last_update_login,
claim_line_id = l_line_util_rec.claim_line_id,
utilization_id = l_line_util_rec.utilization_id,
amount = l_line_util_rec.amount,
currency_code = l_line_util_rec.currency_code,
exchange_rate_type = l_line_util_rec.exchange_rate_type,
exchange_rate_date = l_line_util_rec.exchange_rate_date,
exchange_rate = l_line_util_rec.exchange_rate,
acctd_amount = l_line_util_rec.acctd_amount,
util_curr_amount = l_line_util_rec.util_curr_amount,
plan_curr_amount = l_line_util_rec.plan_curr_amount,
univ_curr_amount = l_line_util_rec.univ_curr_amount,
uom_code = l_line_util_rec.uom_code,
quantity = l_line_util_rec.quantity,
scan_unit = l_line_util_rec.scan_unit,
fxgl_acctd_amount = l_line_util_rec.fxgl_acctd_amount,
utilized_acctd_amount = l_line_util_rec.utilized_acctd_amount
WHERE claim_line_util_id = p_line_util_rec.claim_line_util_id;
ROLLBACK TO Update_Line_Util;
ROLLBACK TO Update_Line_Util;
ROLLBACK TO Update_Line_Util;
END Update_Line_Util;
PROCEDURE Delete_All_Line_Util(
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_funds_util_flt IN funds_util_flt_type
,p_currency_rec IN currency_rec_type
,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_All_Line_Util';
SELECT object_version_number
FROM ozf_claim_lines_util_all
WHERE claim_line_util_id = cv_line_util_id;
SAVEPOINT Delete_All_Line_Util;
Delete_Line_Util(
p_api_version => l_api_version
,p_init_msg_list => FND_API.g_false
,p_commit => FND_API.g_false
,x_return_status => l_return_status
,x_msg_data => x_msg_data
,x_msg_count => x_msg_count
,p_line_util_id => l_lu_line_util_id
,p_object_version => l_object_version_number
,p_mode => OZF_CLAIM_UTILITY_PVT.g_auto_mode
,px_upd_line_util_tbl => l_upd_fund_util_tbl --ninarasi fix for bug 13530939
);
UPDATE ozf_funds_utilized_all_b
SET amount_remaining = amount_remaining - l_upd_fund_util_tbl(i).amount_remaining
, acctd_amount_remaining = acctd_amount_remaining - l_upd_fund_util_tbl(i).acctd_amount_remaining
, plan_curr_amount_remaining = plan_curr_amount_remaining - l_upd_fund_util_tbl(i).plan_curr_amount_remaining
, univ_curr_amount_remaining = univ_curr_amount_remaining - l_upd_fund_util_tbl(i).univ_curr_amount_remaining
, fund_request_amount_remaining = fund_request_amount_remaining - l_upd_fund_util_tbl(i).fund_request_amount_remaining
WHERE utilization_id = l_upd_fund_util_tbl(i).utilization_id;
l_upd_fund_util_tbl.delete;
UPDATE ozf_funds_utilized_all_b
SET amount_remaining = amount_remaining - l_upd_fund_util_tbl(i).amount_remaining
, acctd_amount_remaining = acctd_amount_remaining - l_upd_fund_util_tbl(i).acctd_amount_remaining
, plan_curr_amount_remaining = plan_curr_amount_remaining - l_upd_fund_util_tbl(i).plan_curr_amount_remaining
, univ_curr_amount_remaining = univ_curr_amount_remaining - l_upd_fund_util_tbl(i).univ_curr_amount_remaining
, fund_request_amount_remaining = fund_request_amount_remaining - l_upd_fund_util_tbl(i).fund_request_amount_remaining
WHERE utilization_id = l_upd_fund_util_tbl(i).utilization_id;
l_upd_fund_util_tbl.delete;
ROLLBACK TO Delete_All_Line_Util;
ROLLBACK TO Delete_All_Line_Util;
ROLLBACK TO Delete_All_Line_Util;
END Delete_All_Line_Util;
PROCEDURE Delete_Line_Util_Tbl(
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_data OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,p_line_util_tbl IN line_util_tbl_type
,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
,x_error_index OUT NOCOPY NUMBER
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Line_Util_Tbl';
SELECT claim_id
FROM ozf_claim_lines
WHERE claim_line_id = cv_claim_line_id;
SELECT SUM(acctd_amount)
FROM ozf_claim_lines_util
WHERE claim_line_id = cv_claim_line_id;
SAVEPOINT Delete_Line_Util_Tbl;
Delete_Line_Util(
p_api_version => 1.0
, p_init_msg_list => FND_API.g_false
, p_commit => FND_API.g_false
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_line_util_id => l_line_util_id
, p_object_version => l_object_version
, p_mode => OZF_CLAIM_UTILITY_PVT.g_auto_mode
, px_upd_line_util_tbl => l_upd_fund_util_tbl --ninarasi fix for bug 13530939
);
UPDATE ozf_funds_utilized_all_b
SET amount_remaining = amount_remaining - l_upd_fund_util_tbl(i).amount_remaining
, acctd_amount_remaining = acctd_amount_remaining - l_upd_fund_util_tbl(i).acctd_amount_remaining
, plan_curr_amount_remaining = plan_curr_amount_remaining - l_upd_fund_util_tbl(i).plan_curr_amount_remaining
, univ_curr_amount_remaining = univ_curr_amount_remaining - l_upd_fund_util_tbl(i).univ_curr_amount_remaining
, fund_request_amount_remaining = fund_request_amount_remaining - l_upd_fund_util_tbl(i).fund_request_amount_remaining
WHERE utilization_id = l_upd_fund_util_tbl(i).utilization_id;
l_upd_fund_util_tbl.delete;
UPDATE ozf_claim_lines_all
SET earnings_associated_flag = 'F'
WHERE claim_line_id = p_line_util_tbl(1).claim_line_id;
ROLLBACK TO Delete_Line_Util_Tbl;
ROLLBACK TO Delete_Line_Util_Tbl;
ROLLBACK TO Delete_Line_Util_Tbl;
END Delete_Line_Util_Tbl;
PROCEDURE Delete_Line_Util(
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_line_util_id IN NUMBER
,p_object_version IN NUMBER
,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
,px_upd_line_util_tbl IN OUT NOCOPY upd_fund_util_tbl_type --ninarasi fix for bug 13530939
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Line_Util';
SELECT clu.claim_line_id , clu.currency_code
FROM ozf_claim_lines_util clu
WHERE claim_line_util_id = cv_line_util_id;
SELECT claim_id
FROM ozf_claim_lines
WHERE claim_line_id = cv_claim_line_id;
SELECT lu.claim_line_id
, lu.utilization_id
, Decode(lu.currency_code,fu.plan_currency_code, lu.plan_curr_amount, lu.acctd_amount)
, lu.scan_unit
FROM ozf_claim_lines_util lu
, ozf_funds_utilized_all_b fu
WHERE claim_line_util_id = cv_line_util_id
AND lu.utilization_id = fu.utilization_id;
SELECT gs.currency_code
FROM gl_sets_of_books gs
, ozf_sys_parameters org
WHERE org.set_of_books_id = gs.set_of_books_id
AND org.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
SAVEPOINT Delete_Line_Util;
OZF_Utility_PVT.debug_message(l_full_name ||': update funds_utilized');
Update_Fund_Utils(
p_line_util_rec => l_line_util_rec
, p_asso_amount => NVL(l_del_line_util_amt,0)
, p_mode => 'NONE'
, px_currency_rec => l_currency_rec
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, px_upd_line_util_tbl => px_upd_line_util_tbl --ninarasi fix for bug 13530939
);
OZF_Utility_PVT.debug_message(l_full_name ||': delete');
DELETE FROM ozf_claim_lines_util_all
WHERE claim_line_util_id = p_line_util_id
AND object_version_number = p_object_version;
ROLLBACK TO Delete_Line_Util;
ROLLBACK TO Delete_Line_Util;
ROLLBACK TO Delete_Line_Util;
END Delete_Line_Util;
SELECT object_version_number,
claim_line_id,
utilization_id,
amount,
currency_code,
exchange_rate_type,
exchange_rate_date,
exchange_rate,
acctd_amount,
scan_unit,
activity_product_id,
uom_code,
quantity,
org_id,
fxgl_acctd_amount,
utilized_acctd_amount,
util_curr_amount, --nepanda : fix for bug # 9508390 - issue # 1
univ_curr_amount,
plan_curr_amount
FROM ozf_claim_lines_util
WHERE claim_line_util_id = cv_line_util_id;
SELECT offer_performance_id
, product_attribute_context
, product_attribute
, product_attr_value
, start_date
, end_date
, requirement_type
, estimated_value
, uom_code
FROM ozf_offer_performances
WHERE required_flag = 'Y'
AND product_attribute_context = 'ITEM'
AND requirement_type IN ('AMOUNT', 'VOLUME')
AND list_header_id = cv_offer_id;
SELECT gs.currency_code
FROM gl_sets_of_books gs
, ozf_sys_parameters org
WHERE org.set_of_books_id = gs.set_of_books_id
AND org.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
FND_DSQL.add_text('SELECT NVL(sum(common_quantity), 0), NVL(sum(common_amount), 0), ');
FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');
FND_DSQL.add_text('SELECT NVL(sum(quantity), 0), NVL(sum(quantity*acctd_selling_price), 0), ');
FND_DSQL.add_text(' AND inventory_item_id IN (SELECT s.product_id FROM ( ');
select object_version_number, sales_rep_id
from ozf_claims_all
where claim_id = cv_claim_id;
SELECT salesrep_required_flag
FROM ar_system_parameters;
OZF_claim_PVT.Update_claim(
P_Api_Version => l_api_version,
P_Init_Msg_List => FND_API.g_false,
P_Commit => FND_API.g_false,
P_Validation_Level => FND_API.g_valid_level_full,
X_Return_Status => l_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data,
P_claim => l_claim_Rec,
p_event => 'UPDATE',
p_mode => OZF_claim_Utility_pvt.G_AUTO_MODE,
X_Object_Version_Number => l_object_version_number
);
SELECT claim_line_id
, activity_type
, activity_id
, item_type
, item_id
-- , acctd_amount
, claim_currency_amount
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
SELECT primary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = cv_item_id
AND organization_id = FND_PROFILE.VALUE('AMS_ITEM_ORGANIZATION_ID');
SELECT 1
FROM ozf_offer_performances
WHERE list_header_id = cv_list_header_id;
SELECT
sum(orl.total_accepted_amount)
FROM
ozf_resale_batches_all orb,
ozf_resale_lines_int_all orl
WHERE
orb.batch_type = 'CHARGEBACK'
AND orb.resale_batch_id = orl.resale_batch_id
AND orl.resale_batch_id = p_resale_batch_id
AND NVL(orl.corrected_agreement_id, orl.agreement_id) = p_agreement_id
AND orl.inventory_item_id = p_inventory_item_id
AND orl.agreement_type = 'PL'
AND NVL(orl.tracing_flag, 'F') <> 'T'
AND orl.status_code = 'PROCESSED';
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = l_currOrgId;
SELECT gs.currency_code
FROM gl_sets_of_books gs
, ozf_sys_parameters org
WHERE org.set_of_books_id = gs.set_of_books_id
AND org.org_id = l_currOrgId;
l_line_tbl(l_counter).select_cust_children_flag := l_funds_util_flt.select_cust_children_flag;
l_line_tbl(l_counter).select_cust_children_flag := l_funds_util_flt.select_cust_children_flag;
Update_Group_Line_Util(
p_api_version => 1.0
,p_init_msg_list => FND_API.g_false
,p_commit => FND_API.g_false
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_summary_view => 'ACTIVITY'
,p_funds_util_flt => l_funds_util_flt
);
SELECT claim_line_id
, activity_type
, activity_id
, item_type
, item_id
, acctd_amount
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
SELECT primary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = cv_item_id
AND organization_id = FND_PROFILE.VALUE('AMS_ITEM_ORGANIZATION_ID');
SELECT 1
FROM ozf_offer_performances
WHERE list_header_id = cv_list_header_id;
SELECT
SUM(orl.total_accepted_amount) total_amount,
orl.inventory_item_id product_id,
orb.currency_code currency_code
FROM
ozf_resale_batches_all orb,
ozf_resale_lines_int_all orl
WHERE
orb.batch_type = 'CHARGEBACK'
AND orb.resale_batch_id = orl.resale_batch_id
AND orb.resale_batch_id = p_resale_batch_id
AND NVL(orl.tracing_flag, 'F') <> 'T'
AND orl.status_code = 'PROCESSED'
GROUP BY orl.inventory_item_id, orb.currency_code;
SELECT
orl.resale_line_int_id
FROM
ozf_resale_batches_all orb,
ozf_resale_lines_int_all orl
WHERE
orb.batch_type = 'CHARGEBACK'
AND orb.resale_batch_id = orl.resale_batch_id
AND orb.resale_batch_id = p_resale_batch_id
AND NVL(orl.tracing_flag, 'F') <> 'T'
AND orl.status_code = 'PROCESSED';
SELECT fu.cust_account_id cust_account_id, fu.plan_type plan_type, fu.plan_id plan_id,
fu.product_id product_id, sum(fu.acctd_amount_remaining) amount, fu.currency_code currency_code
FROM ozf_funds_utilized_all_b fu
WHERE fu.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID()
AND fu.acctd_amount_remaining <> 0
AND fu.utilization_type IN ('ACCRUAL', 'ADJUSTMENT')
AND fu.cust_account_id = p_cust_account_id
AND fu.fund_id = p_fund_id
AND fu.product_level_type = 'PRODUCT'
AND fu.product_id = p_product_id
AND fu.gl_posted_flag = 'Y'
AND fu.plan_type = 'OFFR'
GROUP BY cust_account_id, plan_type, plan_id, product_id, currency_code;
Update_Group_Line_Util(
p_api_version => 1.0
,p_init_msg_list => FND_API.g_false
,p_commit => FND_API.g_false
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_summary_view => 'ACTIVITY'
,p_funds_util_flt => l_funds_util_flt
);
SELECT p.party_name
FROM hz_parties p, hz_cust_accounts c
WHERE p.party_id = c.party_id
AND c.cust_account_id = cv_cust_account_id;
SELECT claim_line_id
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
SELECT primary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = cv_item_id
AND organization_id = FND_PROFILE.VALUE('AMS_ITEM_ORGANIZATION_ID');
SELECT cust_account_id, amount,claim_class,currency_code
FROM ozf_claims
WHERE claim_id = cv_claim_id;
SELECT claim_line_id
, activity_type
, activity_id
, item_id
, claim_currency_amount
FROM ozf_claim_lines
WHERE claim_line_id = cv_claim_line_id
AND claim_id = cv_claim_id;
SELECT gs.currency_code
FROM gl_sets_of_books gs
, ozf_sys_parameters org
WHERE org.set_of_books_id = gs.set_of_books_id
AND org.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
l_line_tbl(l_counter).select_cust_children_flag := l_funds_util_flt.select_cust_children_flag;
Update_Group_Line_Util(
p_api_version => 1.0
,p_init_msg_list => FND_API.g_false
,p_commit => FND_API.g_false
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_summary_view => 'ACTIVITY'
,p_funds_util_flt => l_funds_util_flt
);
select claim_line_id
, plan_id
, activity_type
, activity_id
, offer_type
, source_object_class --> document_class
, source_object_id --> document_id
, item_id -->product_id
, amount -->total_amount
, quantity
, quantity_uom -->uom_code
, relationship_type
, related_cust_account_id
, buy_group_cust_account_id
, buy_group_party_id
, select_cust_children_flag
, prorate_earnings_flag
, utilization_id -- Added For Bug 8402328
from ozf_claim_lines_all
WHERE claim_line_id = cv_claim_line_id;
select sum(amount)
from ozf_claim_lines_util
where claim_line_id = cv_claim_line_id;
select sum(scan_unit)
from ozf_claim_lines_util
where claim_line_id = cv_claim_line_id;
, l_funds_util_flt.select_cust_children_flag
,l_funds_util_flt.prorate_earnings_flag
,l_funds_util_flt.utilization_id; -- Added For Bug 8402328
Update_Group_Line_Util(
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_summary_view => null
,p_funds_util_flt => l_funds_util_flt
,p_mode => OZF_CLAIM_UTILITY_PVT.g_auto_mode
);
SELECT partner_cust_account_id
, partner_id
, currency_code
, partner_claim_number
FROM ozf_resale_batches
WHERE resale_batch_id = cv_batch_id;
SELECT r.request_header_id
, r.request_number
--POS Batch Processing by profiles by ateotia (+)
, r.ship_from_stock_flag
, r.offer_type
--POS Batch Processing by profiles by ateotia (-)
, s.agreement_name
FROM ozf_resale_lines_int s
, ozf_request_headers_all_b r
WHERE s.resale_batch_id = cv_batch_id
AND s.agreement_name = r.agreement_number
AND r.partner_id = cv_partner_id
AND r.status_code = 'APPROVED'
AND r.request_class = 'SPECIAL_PRICE'
GROUP BY r.request_header_id
, r.request_number
, s.agreement_name
, r.ship_from_stock_flag
, r.offer_type;
SELECT inventory_item_id
, uom_code
, sum(quantity)
, currency_code
, sum(total_accepted_amount)
FROM ozf_resale_lines_int
WHERE resale_batch_id = cv_batch_id
AND agreement_name = cv_agreement_number
AND status_code = 'PROCESSED'
GROUP BY inventory_item_id
, uom_code
, currency_code;
SELECT act.SCAN_VALUE
, act.UOM_CODE
, act.Quantity
, off.TRANSACTION_CURRENCY_CODE
FROM ozf_offers off
, ams_act_products act
WHERE offer_code = cv_agreement_number
AND ARC_ACT_PRODUCT_USED_BY = 'OFFR'
AND ACT_PRODUCT_USED_BY_ID = off.qp_list_header_id
AND INVENTORY_ITEM_ID = cv_product_id;
SELECT claim_line_id
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
SELECT p.party_name
FROM hz_parties p
, hz_cust_accounts c
WHERE p.party_id = c.party_id
AND c.cust_account_id = cv_cust_account_id;
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = MO_GLOBAL.GET_CURRENT_ORG_ID;
l_line_tbl.DELETE;
SELECT partner_cust_account_id
, partner_id
, currency_code
, partner_claim_number
FROM ozf_resale_batches
WHERE resale_batch_id = cv_batch_id;
SELECT r.request_header_id
, r.request_number
--POS Batch Processing by profiles by ateotia (+)
, r.ship_from_stock_flag
, r.offer_type
--POS Batch Processing by profiles by ateotia (-)
, s.agreement_name
, s.inventory_item_id
, s.uom_code
, s.currency_code
, sum(s.quantity)
, sum(s.total_accepted_amount)
FROM ozf_resale_lines_int_all s,
ozf_request_headers_all_b r
WHERE s.resale_batch_id = cv_batch_id
AND s.status_code = 'PROCESSED'
AND s.agreement_name = r.agreement_number
AND r.partner_id = cv_partner_id
AND r.status_code = 'APPROVED'
AND r.request_class = 'SPECIAL_PRICE'
GROUP BY
r.request_header_id
, r.request_number
, s.agreement_name
, s.inventory_item_id
, s.uom_code
, s.currency_code
, r.ship_from_stock_flag
, r.offer_type ;
SELECT act.SCAN_VALUE
, act.UOM_CODE
, act.Quantity
, off.TRANSACTION_CURRENCY_CODE
FROM ozf_offers off
, ams_act_products act
WHERE offer_code = cv_agreement_number
AND ARC_ACT_PRODUCT_USED_BY = 'OFFR'
AND ACT_PRODUCT_USED_BY_ID = off.qp_list_header_id
AND INVENTORY_ITEM_ID = cv_product_id;
SELECT claim_line_id
FROM ozf_claim_lines
WHERE claim_id = cv_claim_id;
SELECT p.party_name
FROM hz_parties p
, hz_cust_accounts c
WHERE p.party_id = c.party_id
AND c.cust_account_id = cv_cust_account_id;
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = MO_GLOBAL.GET_CURRENT_ORG_ID;
l_line_tbl.DELETE;
SELECT adjustment_flag
FROM ams_act_products
WHERE activity_product_id = cv_activity_product_id;
SELECT count(*)
FROM ozf_claim_lines_util lu, ams_act_products ap
,ozf_claim_lines cln1, ozf_claim_lines cln2
WHERE cln1.claim_line_id = cv_claim_line_id
AND cln1.claim_id = cln2.claim_id
AND lu.claim_line_id = cln2.claim_line_id
AND lu.activity_product_id = ap.activity_product_id
AND lu.utilization_id = -1
AND ap.adjustment_flag = 'N';
SELECT tp.un_earned_pay_allow_to, tp.un_earned_pay_thold_type
, tp.un_earned_pay_thold_amount, tp.un_earned_pay_thold_flag
FROM ozf_cust_trd_prfls tp
WHERE tp.cust_account_id = cv_cust_account_id;
SELECT tp.un_earned_pay_allow_to, tp.un_earned_pay_thold_type
, tp.un_earned_pay_thold_amount, tp.un_earned_pay_thold_flag
FROM ozf_cust_trd_prfls tp, hz_cust_accounts hca
WHERE tp.party_id = hca.party_id
AND tp.cust_account_id IS NULL
AND hca.cust_account_id = cv_cust_account_id;
SELECT un_earned_pay_allow_to, un_earned_pay_thold_type
, un_earned_pay_thold_amount, un_earned_pay_thold_flag
FROM ozf_sys_parameters
WHERE org_id = l_currOrgId;
SELECT SUM(DECODE(p_currency_rec.association_currency_code,p_currency_rec.transaction_currency_code, plan_curr_amount,acctd_amount))
, SUM(DECODE(p_currency_rec.association_currency_code,p_currency_rec.transaction_currency_code, plan_curr_amount_remaining,acctd_amount_remaining))
FROM ozf_funds_utilized_all_b
WHERE utilization_type IN ('ACCRUAL', 'ADJUSTMENT')
AND org_id =l_currOrgId
AND plan_type = 'OFFR'
AND plan_id = cv_plan_id
AND cust_account_id = cv_cust_account_id;
l_un_earned_pay_allow_to = 'ALLOW_SELECTED'
THEN
IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
FND_MESSAGE.set_name('OZF', 'OZF_EARN_OVERPAY_NOT_ALLOWED');
SELECT product_level_type
, product_id
, fund_id
, SUM(amount)
FROM ozf_funds_utilized_all_b
WHERE utilization_type = 'ACCRUAL'
AND org_id = MO_GLOBAL.GET_CURRENT_ORG_ID()
AND gl_posted_flag = 'Y'
AND plan_type = 'OFFR'
AND plan_id = cv_offer_id
AND cust_account_id = cv_cust_account_id
AND (cv_product_id IS NULL OR product_id = cv_product_id)
AND (cv_product_level_type IS NULL OR product_level_type = cv_product_level_type)
AND plan_currency_code = cv_plan_currency_code
GROUP BY product_level_type,product_id,fund_id;
SELECT product_id, product_level_type, fund_id
FROM ozf_funds_utilized_all_b
WHERE utilization_id = cv_utilization_id;
SELECT SUM(approved_original_amount)
FROM ozf_act_budgets a
WHERE transfer_type = 'REQUEST'
AND status_code = 'APPROVED' --ninarasi : fix for bug # 12611938
AND act_budget_used_by_id = cv_offer_id
AND EXISTS ( SELECT 1
FROM ozf_funds_all_vl
WHERE fund_id = budget_source_id
AND CURRENCY_CODE_TC = a.approved_in_currency);
SELECT budget_source_id
, approved_original_amount
, count(*) over () total_funds
FROM ozf_act_budgets a
WHERE transfer_type = 'REQUEST'
AND status_code = 'APPROVED' --ninarasi : fix for bug # 12611938
AND act_budget_used_by_id = cv_offer_id
AND EXISTS ( SELECT 1
FROM ozf_funds_all_vl
WHERE fund_id = budget_source_id
AND currency_code_tc = a.approved_in_currency);
px_line_util_tbl(j).update_from_tbl_flag := FND_API.g_true;
px_line_util_tbl(j).update_from_tbl_flag := FND_API.g_true;
px_line_util_tbl(j).update_from_tbl_flag := FND_API.g_true;
px_line_util_tbl(j).update_from_tbl_flag := FND_API.g_true;
SELECT NVL(SUM(scan_unit * amp.quantity ), 0)
FROM ozf_claim_lines_util lu,
ozf_claims c,
ozf_claim_lines cl,
ams_act_products amp
WHERE lu.activity_product_id = cv_activity_product_id
AND cl.claim_line_id = lu.claim_line_id
AND cl.claim_id = c.claim_id
AND c.status_code <> 'CLOSED'
AND lu.activity_product_id = amp.activity_product_id
AND lu.utilization_id <> -1; */
SELECT util.utilization_id
, line.batch_curr_claim_amount
, offr.transaction_currency_code
FROM ozf_funds_utilized_all_b util
, ozf_sd_batch_lines_all line
, ozf_offers offr
WHERE util.utilization_id = line.utilization_id
AND util.plan_id = offr.qp_list_header_id
AND line.batch_id = cv_batch_id
AND line.item_id = cv_product_id
AND util.reference_id = cv_request_header_id
AND line.batch_line_id = NVL(cv_batch_line_id,batch_line_id)
AND util.reference_type = 'SD_REQUEST'
AND line.batch_curr_claim_amount <> 0;
SELECT util.utilization_id
, util.exchange_rate
, util.exchange_rate_date
, util.exchange_rate_type
, line.batch_curr_claim_amount
FROM ozf_funds_utilized_all_b util
, ozf_sd_batch_lines_all line
WHERE util.utilization_id = line.utilization_id
AND line.status_code ='APPROVED' --//Bugfix: 9794989
AND line.purge_flag ='N'
AND line.batch_id = cv_batch_id
AND line.item_id = cv_product_id
AND util.reference_id = cv_request_header_id
AND line.batch_line_id = NVL(cv_batch_line_id,batch_line_id)
AND util.reference_type = 'SD_REQUEST'
AND line.batch_curr_claim_amount <> 0;
x_line_util_tbl(j).update_from_tbl_flag := FND_API.g_true;
x_line_util_tbl(j).update_from_tbl_flag := FND_API.g_true;
x_line_util_tbl(i).update_from_tbl_flag := FND_API.g_true;
x_line_util_tbl(i).update_from_tbl_flag := FND_API.g_true;
SELECT activity_product_id
FROM ozf_funds_utilized_all_b
WHERE plan_type = 'OFFR'
AND org_id = MO_GLOBAL.GET_CURRENT_ORG_ID()
AND plan_id = cv_plan_id
AND product_level_type = 'PRODUCT'
AND product_id = cv_product_id;
SELECT uom_code
, quantity
, scan_value
, inventory_item_id
FROM ams_act_products
WHERE activity_product_id = cv_activity_product_id;
SELECT status_code
, transaction_currency_code
FROM ozf_offers
WHERE qp_list_header_id = cv_offer_id;
SELECT plan_currency_code
, plan_curr_amount_remaining
, plan_curr_amount
, acctd_amount_remaining
FROM ozf_funds_utilized_all_b util
WHERE utilization_id = cv_util_id;*/
PROCEDURE Update_Group_Line_Util(
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_summary_view IN VARCHAR2 := NULL
,p_funds_util_flt IN funds_util_flt_type
,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Group_Line_Util';
SELECT gs.currency_code
FROM gl_sets_of_books gs
, ozf_sys_parameters org
WHERE org.set_of_books_id = gs.set_of_books_id
AND org.org_id = cv_org_id;
SELECT cla.status_code
, cla.source_object_class
, cla.batch_type
, cla.currency_code
, cla.creation_date
, cla.exchange_rate_type
, cla.exchange_rate_date
, cla.exchange_rate
, cla.source_object_id
, cln.activity_id
, cln.batch_line_id
, cla.created_from
, cla.org_id
FROM ozf_claims cla, ozf_claim_lines cln
WHERE cla.claim_id = cln.claim_id
AND cln.claim_line_id = cv_claim_line_id;
SAVEPOINT Update_Group_Line_Util;
l_line_tbl(1).SELECT_CUST_CHILDREN_FLAG := 'F';
Delete_All_Line_Util ( p_api_version => l_api_version
, p_init_msg_list => FND_API.g_false
, p_commit => FND_API.g_false
, p_validation_level => FND_API.g_valid_level_full
, x_return_status => l_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_currency_rec => l_currency_rec
, p_funds_util_flt => l_funds_util_flt
);
Delete_Group_Line_Util ( p_api_version => l_api_version
, p_init_msg_list => FND_API.g_false
, p_commit => FND_API.g_false
, p_validation_level => FND_API.g_valid_level_full
, x_return_status => l_return_status
, x_msg_data => x_msg_data
, x_msg_count => x_msg_count
, p_funds_util_flt => l_funds_util_flt
);
ROLLBACK TO Update_Group_Line_Util;
ROLLBACK TO Update_Group_Line_Util;
ROLLBACK TO Update_Group_Line_Util;
END Update_Group_Line_Util;
Update_Fund_Utils(
p_line_util_rec => l_line_util_rec
, p_asso_amount => NVL(l_amount,0)
, p_mode => 'NONE'
, px_currency_rec => l_currency_rec
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, px_upd_line_util_tbl => px_upd_line_util_tbl --ninarasi fix for bug 13530939
);
DELETE FROM ozf_claim_lines_util_all
WHERE claim_line_util_id = p_line_util_rec.claim_line_util_id;
PROCEDURE Delete_Group_Line_Util(
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_funds_util_flt IN funds_util_flt_type
,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Group_Line_Util';
SELECT SUM(amount)
FROM ozf_claim_lines_util
WHERE claim_line_id = cv_claim_line_id;
SELECT gs.currency_code
FROM gl_sets_of_books gs
, ozf_sys_parameters org
WHERE org.set_of_books_id = gs.set_of_books_id
AND org.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
SELECT currency_code from ozf_claim_lines
where claim_line_id = cv_claim_line_id;
SELECT transaction_currency_code
FROM ozf_offers
WHERE qp_list_header_id = cv_plan_id;
SELECT claim_line_util_id, acctd_amount, scan_unit
FROM ozf_claim_lines_util
WHERE claim_line_id = cv_claim_line_id
AND activity_product_id = cv_act_product_id
AND utilization_id = -1;
SELECT claim_line_util_id, acctd_amount
FROM ozf_claim_lines_util util
WHERE claim_line_id = cv_claim_line_id
AND activity_product_id = cv_offer_id
AND utilization_id = -2;
SELECT uom_code
, quantity
, scan_value
, inventory_item_id
FROM ams_act_products
WHERE activity_product_id = cv_activity_product_id;
SELECT util_curr_amount
FROM ozf_claim_lines_util
WHERE claim_line_util_id = cv_line_util_id;
SELECT DECODE(ln.currency_code,fu.plan_currency_code,ln.plan_curr_amount,ln.acctd_amount)
FROM ozf_claim_lines_util ln
, ozf_funds_utilized_all_b fu
WHERE claim_line_util_id = cv_line_util_id
AND fu.utilization_id = ln.utilization_id;
SAVEPOINT Delete_Group_Line_Util;
UPDATE ozf_funds_utilized_all_b
SET amount_remaining = amount_remaining - l_upd_fund_util_tbl(i).amount_remaining
, acctd_amount_remaining = acctd_amount_remaining - l_upd_fund_util_tbl(i).acctd_amount_remaining
, plan_curr_amount_remaining = plan_curr_amount_remaining - l_upd_fund_util_tbl(i).plan_curr_amount_remaining
, univ_curr_amount_remaining = univ_curr_amount_remaining - l_upd_fund_util_tbl(i).univ_curr_amount_remaining
, fund_request_amount_remaining = fund_request_amount_remaining - l_upd_fund_util_tbl(i).fund_request_amount_remaining
WHERE utilization_id = l_upd_fund_util_tbl(i).utilization_id;
l_upd_fund_util_tbl.delete;
Update_Line_Util(
p_api_version => l_api_version
,p_init_msg_list => FND_API.g_false
,p_commit => FND_API.g_false
,p_validation_level => FND_API.g_valid_level_full
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_line_util_rec => l_upd_line_util_rec
,x_object_version => l_object_version_number
,px_upd_line_util_tbl => l_upd_fund_util_tbl
);
UPDATE ozf_funds_utilized_all_b
SET amount_remaining = amount_remaining - l_upd_fund_util_tbl(i).amount_remaining
, acctd_amount_remaining = acctd_amount_remaining - l_upd_fund_util_tbl(i).acctd_amount_remaining
, plan_curr_amount_remaining = plan_curr_amount_remaining - l_upd_fund_util_tbl(i).plan_curr_amount_remaining
, univ_curr_amount_remaining = univ_curr_amount_remaining - l_upd_fund_util_tbl(i).univ_curr_amount_remaining
, fund_request_amount_remaining = fund_request_amount_remaining - l_upd_fund_util_tbl(i).fund_request_amount_remaining
WHERE utilization_id = l_upd_fund_util_tbl(i).utilization_id;
l_upd_fund_util_tbl.delete;
UPDATE ozf_claim_lines_all
SET earnings_associated_flag = 'F'
WHERE claim_line_id = p_funds_util_flt.claim_line_id;
ROLLBACK TO Delete_Group_Line_Util;
ROLLBACK TO Delete_Group_Line_Util;
ROLLBACK TO Delete_Group_Line_Util;
END Delete_Group_Line_Util;
PROCEDURE Update_Dummy_Utilizations(
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_claim_line_util_id IN NUMBER
,p_mode IN VARCHAR2 := OZF_CLAIM_UTILITY_PVT.g_auto_mode
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Dummy_Utilizations';
SELECT claim_line_id
,activity_product_id
,uom_code
,quantity
,amount
,scan_unit
,utilization_id
FROM ozf_claim_lines_util
WHERE claim_line_util_id = cv_claim_line_util_id;
SELECT utilization_id
,amount_remaining
,scan_unit_remaining
FROM ozf_funds_utilized_all_vl
WHERE activity_product_id = cv_activity_product_id
AND adjustment_type_id = -8
AND utilization_type ='ADJUSTMENT'
AND amount_remaining <> 0;
SELECT utilization_id
,acctd_amount_remaining
FROM ozf_funds_utilized_all_vl
WHERE plan_type = 'OFFR'
AND plan_id = cv_offer_id
AND adjustment_type_id = -11
AND utilization_type = 'ADJUSTMENT';
SAVEPOINT Update_Dummy_Utilizations;
DELETE FROM ozf_claim_lines_util_all
WHERE claim_line_util_id = p_claim_line_util_id;
ROLLBACK TO Update_Dummy_Utilizations;
ROLLBACK TO Update_Dummy_Utilizations;
ROLLBACK TO Update_Dummy_Utilizations;
END Update_Dummy_Utilizations;
SELECT lu.claim_line_util_id
,lu.activity_product_id
,lu.scan_unit
,ap.act_product_used_by_id
,ap.adjustment_flag
FROM ozf_claim_lines_util lu, ams_act_products ap
,ozf_claim_lines cln
WHERE cln.claim_id = cv_claim_id
AND lu.claim_line_id = cln.claim_line_id
AND lu.activity_product_id = ap.activity_product_id
AND lu.utilization_id = -1
ORDER BY ap.adjustment_flag DESC;
SELECT SUM(lu.scan_unit * amp.quantity)
,lu.utilization_id
FROM ozf_claim_lines_util_all lu
,ozf_claim_lines_all cln
,ams_act_products amp
WHERE cln.claim_id = cv_claim_id
AND lu.claim_line_id = cln.claim_line_id
AND lu.activity_product_id = amp.activity_product_id
GROUP BY lu.utilization_id;
select CUST_ACCOUNT_ID , CUST_BILLTO_ACCT_SITE_ID , CUST_SHIPTO_ACCT_SITE_ID
from ozf_claims_all
where claim_id = cv_claim_id;
Update_Dummy_Utilizations(
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_claim_line_util_id => l_claim_line_util_id
,p_mode => p_mode
);
UPDATE ozf_funds_utilized_all_b
SET scan_unit_remaining = scan_unit_remaining - l_total_scan_unit
WHERE utilization_id = l_utilization_id;
SELECT exchange_rate_type
, exchange_rate_date
, exchange_rate
FROM ozf_funds_utilized_all_b
WHERE utilization_id = cv_utilization_id;
SELECT fu.plan_id, fu.cust_account_id, ln.activity_type
FROM ozf_claim_lines_util lu, ozf_funds_utilized_all_b fu, ozf_claim_lines ln
WHERE lu.utilization_id = fu.utilization_id
AND lu.claim_line_id = ln.claim_line_id
AND ln.claim_id = cv_claim_id
GROUP BY fu.plan_id, fu.cust_account_id, ln.activity_type;
SELECT custom_setup_id
FROM ozf_claims
WHERE claim_id = cv_claim_id;
SELECT NVL(attr_available_flag, 'N')
FROM ams_custom_setup_attr
WHERE custom_setup_id = cv_custom_setup_id
AND object_attribute = 'PAPR';
UPDATE ozf_claims_all
SET approved_flag = 'F'
WHERE claim_id = p_claim_id;
UPDATE ozf_claims_all
SET approved_flag = 'T'
WHERE claim_id = p_claim_id;
SELECT cust_account_id
FROM ozf_claims
WHERE claim_id = cv_claim_id;
SELECT un_earned_pay_thold_type, un_earned_pay_thold_amount, un_earned_pay_thold_flag
FROM ozf_cust_trd_prfls
WHERE cust_account_id = cv_cust_account_id;
SELECT p.un_earned_pay_thold_type, p.un_earned_pay_thold_amount, p.un_earned_pay_thold_flag
FROM ozf_cust_trd_prfls p, hz_cust_accounts c
WHERE p.party_id = c.party_id
AND p.cust_account_id IS NULL
AND c.cust_account_id = cv_cust_account_id;
SELECT un_earned_pay_thold_type, un_earned_pay_thold_amount, un_earned_pay_thold_flag
FROM ozf_sys_parameters
WHERE org_id = l_currOrgId;
SELECT fu.plan_id, fu.cust_account_id, sum(lu.acctd_amount)
FROM ozf_claim_lines_util lu, ozf_funds_utilized_all_b fu
, ozf_claim_lines ln
WHERE lu.utilization_id = fu.utilization_id
AND lu.claim_line_id = ln.claim_line_id
AND ln.claim_id = cv_claim_id
GROUP BY fu.plan_id, fu.cust_account_id;
SELECT sum(acctd_amount), sum(acctd_amount_remaining)
FROM ozf_funds_utilized_all_b
WHERE utilization_type in ('ACCRUAL', 'ADJUSTMENT')
AND org_id = l_currOrgId
AND gl_posted_flag = 'Y'
AND plan_type = 'OFFR'
AND plan_id = cv_plan_id
AND cust_account_id = cv_cust_account_id;
SELECT fu.plan_id, fu.cust_account_id, ln.activity_type
FROM ozf_claim_lines_util lu, ozf_funds_utilized_all_b fu, ozf_claim_lines ln
WHERE lu.utilization_id = fu.utilization_id
AND lu.claim_line_id = ln.claim_line_id
AND ln.claim_id = cv_claim_id
GROUP BY fu.plan_id, fu.cust_account_id, ln.activity_type;
SELECT lu.activity_product_id, sum(lu.acctd_amount)
FROM ozf_claim_lines_util lu, ozf_claim_lines ln
WHERE lu.claim_line_id = ln.claim_line_id
AND lu.utilization_id = -2
AND ln.claim_id = cv_claim_id
GROUP BY lu.activity_product_id;
SELECT ms.primary_uom_code
FROM mtl_system_items ms
, mtl_parameters mp
, org_organization_definitions ood
WHERE ms.inventory_item_id = cv_item_id
AND ms.organization_id = mp.organization_id
AND mp.organization_id = mp.master_organization_Id
AND mp.organization_id = ood.organization_id
AND ood.operating_unit = MO_GLOBAL.GET_CURRENT_ORG_ID();
SELECT item_uom
FROM ozf_sd_request_lines_all
WHERE request_header_id = cv_request_id
AND inventory_item_id = cv_product_id;
SELECT shipped_quantity_uom
FROM ozf_sd_batch_lines_all
WHERE batch_id = cv_batch_id
AND item_id = cv_product_id;
SELECT claim_line_id
, activity_type
, activity_id
, item_type
, item_id
-- , acctd_amount
, amount --7169388
, fund.approved_in_currency --7231613 --//SD MC
FROM ozf_claim_lines_all lines
, ozf_offers offr
, ozf_act_budgets fund
, ozf_sd_request_headers_all_b req
WHERE claim_id = cv_claim_id
AND lines.activity_id = req.request_header_id
AND req.offer_id = offr.qp_list_header_id
AND offr.qp_list_header_id = fund.act_budget_used_by_id
AND arc_act_budget_used_by = 'OFFR'
AND transfer_type = 'REQUEST';
SELECT lines.claim_line_id
, lines.activity_type
, lines.activity_id
, lines.item_type
, lines.item_id
, lines.amount
, offr.transaction_currency_code
FROM ozf_claim_lines_all lines
, ozf_offers offr
, ozf_sd_request_headers_all_b req
WHERE claim_id = cv_claim_id
AND lines.activity_id = req.request_header_id
AND req.offer_id = offr.qp_list_header_id;
SELECT request_currency_code, org_id, created_by
FROM ozf_sd_request_headers_all_b
WHERE request_header_id = cv_request_id;
SELECT DISTINCT
util.cust_account_id
, util.bill_to_site_use_id
FROM ozf_funds_utilized_all_b util
WHERE util.reference_id = cv_request_id;
SELECT cust_account_id
, reference_type
, reference_id
, bill_to_site_use_id
, product_level_type
, product_id
, exchange_rate_type
, SUM(plan_curr_amount_remaining)
, SUM(acctd_amount_remaining) --// ER 9226258
FROM ozf_funds_utilized_all_b
WHERE reference_id = cv_request_id
AND cust_account_id = cv_cust_account_id
AND NVL(bill_to_site_use_id,1) = NVL(cv_bill_to_site_id,1)
AND reference_type = 'SD_REQUEST'
AND gl_posted_flag = 'Y'
GROUP BY
cust_account_id
, reference_type
, reference_id
, bill_to_site_use_id
, product_level_type
, product_id
, exchange_rate_type;
SELECT offr.transaction_currency_code
FROM ozf_sd_request_headers_all_b req
, ozf_offers offr
WHERE offr.qp_list_header_id = req.offer_id
AND req.request_header_id = cv_request_id;
SELECT currency_code, org_id, vendor_id, vendor_site_id, created_by
FROM ozf_sd_batch_headers_all
WHERE batch_id = cv_batch_id;
SELECT DISTINCT
util.cust_account_id
, util.bill_to_site_use_id
, head.claim_number
FROM ozf_funds_utilized_all_b util
, ozf_sd_batch_headers_all head
, ozf_sd_batch_lines_all line
WHERE head.batch_id = cv_batch_id
AND head.batch_id = line.batch_id
--Fix for Bug 10047209
--AND line.status_code ='APPROVED' --//Bugfix: 9794989
AND line.purge_flag ='N'
AND util.utilization_id = line.utilization_id;
SELECT util.cust_account_id
, util.reference_type
, util.reference_id
, util.bill_to_site_use_id
, util.product_level_type
, util.product_id
, sum(line.batch_curr_claim_amount)
FROM ozf_funds_utilized_all_b util
, ozf_sd_batch_lines_all line
WHERE line.batch_id = cv_batch_id
--Fix for Bug 10047209
--AND line.status_code ='APPROVED' --//Bugfix: 9794989
AND line.purge_flag ='N'
AND util.cust_account_id = cv_cust_account_id
AND NVL(util.bill_to_site_use_id,1) = NVL(cv_bill_to_site_id,1)
AND util.utilization_id = line.utilization_id
GROUP BY cust_account_id
, bill_to_site_use_id
, reference_type
, reference_id
, product_level_type
, product_id;
SELECT util.cust_account_id
, util.reference_type
, util.reference_id
, util.bill_to_site_use_id
, util.product_level_type
, util.product_id
, line.batch_curr_claim_amount
, line.batch_line_id
FROM ozf_funds_utilized_all_b util
, ozf_sd_batch_lines_all line
WHERE line.batch_id = cv_batch_id
--Fix for Bug 10047209
-- AND line.status_code ='APPROVED' --//Bugfix: 9794989
AND line.purge_flag ='N'
AND util.cust_account_id = cv_cust_account_id
AND NVL(util.bill_to_site_use_id,1) = NVL(cv_bill_to_site_id,1)
AND util.utilization_id = line.utilization_id;
SELECT resource_id
FROM ozf_sd_request_access req
, ozf_claim_lines line
, ozf_claims cla
WHERE cla.claim_id = cv_claim_id
AND cla.claim_id = line.claim_id
AND line.activity_id = req.request_header_id
AND req.enabled_flag = 'Y'
AND NOT EXISTS ( SELECT NULL
FROM ams_act_access
WHERE user_or_role_id = req.resource_id
AND arc_user_or_role_type = 'USER'
AND arc_act_access_to_object = 'CLAM'
);
SELECT application_id, responsibility_id
FROM fnd_responsibility_vl
WHERE responsibility_name = l_resp_name;
SELECT gs.currency_code
FROM gl_sets_of_books gs
, ozf_sys_parameters org
WHERE org.set_of_books_id = gs.set_of_books_id
AND org.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID();
SELECT supplier_neg_batch_flag
FROM ozf_supp_trd_prfls_all
WHERE supplier_site_id = cv_vendor_site_id
AND org_id = cv_org_id;
Update_Group_Line_Util(
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_summary_view => 'ACTIVITY'
,p_funds_util_flt => l_funds_util_flt
,p_mode => OZF_CLAIM_UTILITY_PVT.g_auto_mode
);
SELECT created_from
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT claim_line_id, object_version_number
FROM ozf_claim_lines_all
WHERE claim_id = p_id
AND (earnings_associated_flag IS NULL
OR earnings_associated_flag ='F');
OZF_Claim_Line_PVT.Delete_Claim_Line_Tbl(
p_api_version => l_api_version
,p_init_msg_list => FND_API.g_false
,p_commit => FND_API.g_false
,p_validation_level => FND_API.g_valid_level_full
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_claim_line_tbl => l_claim_line_tbl
,p_change_object_version => FND_API.g_false
,x_error_index => l_error_index
);
END IF; -- End of delete claim line
l_line_tbl(1).SELECT_CUST_CHILDREN_FLAG := 'F';
Update_Group_Line_Util(
p_api_version => 1.0
,p_init_msg_list => FND_API.g_false
,p_commit => FND_API.g_false
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_summary_view => p_summary_view
,p_funds_util_flt => l_funds_util_flt
);