[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;
SELECT transaction_currency_code
, conversion_type
, conversion_date
, conversion_rate
FROM ozf_funds_utilized_all_b fu
, ozf_offers offr
, gl_daily_rates gl
WHERE utilization_id = cv_utilization_id
AND fu.plan_id = offr.qp_list_header_id
AND fu.exchange_rate_type = gl.conversion_type
AND TRUNC(fu.exchange_rate_date) = gl.conversion_date
AND offr.transaction_currency_code = gl.from_currency
AND gl.to_currency = G_FUNCTIONAL_CURRENCY;
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();
PROCEDURE Update_Fund_Utils(
x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_line_util_rec IN OUT NOCOPY line_util_rec_type
,p_fu_diff_acctd_amt_rem IN NUMBER
,p_mode IN VARCHAR2 := 'CALCULATE'
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := ' Update_Fund_Utils';
SELECT transaction_currency_code
FROM ozf_offers
WHERE qp_list_header_id = cv_plan_id;
SELECT currency_code
, exchange_rate
, exchange_rate_type
, exchange_rate_date
, amount_remaining
, acctd_amount_remaining
, scan_unit_remaining
, plan_curr_amount_remaining
, amount
, acctd_amount
, plan_curr_amount
, plan_type
, plan_id
, univ_curr_amount_remaining
, component_type
, component_id
, fund_id
, reference_id
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;
SAVEPOINT Update_Fund_Utils;
UPDATE ozf_funds_utilized_all_b
SET amount_remaining = l_fu_new_amt_rem
, acctd_amount_remaining = l_fu_new_acctd_amt_rem
, plan_curr_amount_remaining = l_fu_new_plan_amt_rem
, univ_curr_amount_remaining = l_fu_new_univ_amt_rem
--, scan_unit_remaining = l_fu_new_scan_unit_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;
, 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 chargeback_line_id FROM ozf_chargeback_lines WHERE chargeback_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('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, fu.amount_remaining, fu.scan_unit_remaining '||
', fu.creation_date , fu.currency_code , fu.bill_to_site_use_id '||
'FROM ozf_funds_utilized_all_b fu, ozf_offers o ');
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, fu.amount_remaining, fu.scan_unit_remaining '||
', fu.creation_date , fu.currency_code , fu.bill_to_site_use_id '||
'FROM ozf_funds_utilized_all_b fu '||
'WHERE fu.plan_type = ''PRIC'' ' ||
'AND fu.org_id =');
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;
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 '||
'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 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 = MO_GLOBAL.GET_CURRENT_ORG_ID()
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 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 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;
UPDATE ozf_claim_lines_util_all
SET amount = l_line_util_amount
WHERE claim_line_util_id = l_last_record_num;
UPDATE ozf_claim_lines_all
SET earnings_associated_flag = 'T'
WHERE claim_line_id = l_line_util_rec.claim_line_id;
l_update_fund_amount NUMBER;
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;
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 cla.currency_code
, cla.exchange_rate_type
, cla.exchange_rate_date
, cla.exchange_rate
, cla.creation_date
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, acctd_amount
FROM ozf_claim_lines
WHERE claim_line_id = cv_claim_line_id;
SELECT nvl(SUM(acctd_amount),0)
FROM ozf_claim_lines_util
WHERE claim_line_id = cv_claim_line_id;
SELECT org_id
FROM ozf_claims_all
WHERE claim_id = cv_claim_id;
SELECT currency_code
FROM ozf_funds_utilized_all_b
where utilization_id = cv_utilization_id;
IF l_line_util_rec.update_from_tbl_flag = FND_API.g_false AND p_mode = OZF_CLAIM_UTILITY_PVT.g_manu_mode THEN
OZF_CLAIM_UTILITY_PVT.Check_Claim_access(
P_Api_Version_Number => 1.0
, P_Init_Msg_List => FND_API.G_FALSE
, p_validation_level => FND_API.G_VALID_LEVEL_FULL
, P_Commit => FND_API.G_FALSE
, P_object_id => l_claim_id
, P_object_type => G_CLAIM_OBJECT_TYPE
, P_user_id => OZF_UTILITY_PVT.get_resource_id(NVL(FND_GLOBAL.user_id,-1))
, X_Return_Status => l_return_status
, X_Msg_Count => x_msg_count
, X_Msg_Data => x_msg_data
, X_access => l_access);
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);
IF l_line_util_rec.update_from_tbl_flag = FND_API.g_false THEN
-- set global functional currency
OPEN csr_function_currency;
l_update_fund_amount := l_line_util_rec.amount;
l_update_fund_amount := l_line_util_rec.amount;
Update_Fund_Utils(
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_fu_diff_acctd_amt_rem => (-l_update_fund_amount)
,p_mode => 'CALCULATE'
);
OZF_Utility_PVT.debug_message(l_full_name ||': insert');
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
);
IF l_line_util_rec.update_from_tbl_flag = FND_API.g_false THEN
UPDATE ozf_claim_lines_all
SET earnings_associated_flag = 'T'
WHERE claim_line_id = l_line_util_rec.claim_line_id;
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;
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 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
);
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
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Line_Util';
l_update_fund_amount NUMBER;
SELECT fu.currency_code
FROM ozf_funds_utilized_all_b fu
where fu.utilization_id = cv_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();
SELECT claim_id, acctd_amount
FROM ozf_claim_lines
WHERE claim_line_id = cv_claim_line_id;
SELECT amount, scan_unit , util_curr_amount
FROM ozf_claim_lines_util
WHERE claim_line_util_id = cv_line_util_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);
IF p_line_util_rec.update_from_tbl_flag = FND_API.g_false THEN
-- get functional_currency
OPEN csr_function_currency;
OZF_Utility_PVT.debug_message(l_full_name ||': update funds utilized');
l_update_fund_amount := l_old_utiz_amount - l_line_util_rec.util_curr_amount;
l_update_fund_amount := l_lu_old_amt - l_line_util_rec.amount;
Update_Fund_Utils(
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_fu_diff_acctd_amt_rem => l_update_fund_amount
,p_mode => 'CALCULATE'
);
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_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;
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_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
);
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 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 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
);
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
)
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 fu.currency_code
FROM ozf_funds_utilized_all_b fu
where fu.utilization_id = cv_utilization_id;
SELECT claim_id
FROM ozf_claim_lines
WHERE claim_line_id = cv_claim_line_id;
SELECT claim_line_id
, utilization_id
, amount
, scan_unit
, util_curr_amount
FROM ozf_claim_lines_util
WHERE claim_line_util_id = cv_line_util_id;
SAVEPOINT Delete_Line_Util;
OZF_Utility_PVT.debug_message(l_full_name ||': update funds_utilized');
Update_Fund_Utils(
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_fu_diff_acctd_amt_rem => l_del_line_util_amt
,p_mode => 'NONE'
);
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
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 exchange_rate_type
FROM ozf_sys_parameters_all
WHERE 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;
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 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
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;
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
, offer_id -- >fund_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
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;
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
, 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;
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 r.request_header_id
, r.request_number
, 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;
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 = MO_GLOBAL.GET_CURRENT_ORG_ID();
SELECT SUM(acctd_amount), SUM(acctd_amount_remaining)
FROM ozf_funds_utilized_all_b
WHERE utilization_type IN ('ACCRUAL', 'ADJUSTMENT')
AND org_id = MO_GLOBAL.GET_CURRENT_ORG_ID()
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');
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 = MO_GLOBAL.GET_CURRENT_ORG_ID();
SELECT transaction_currency_code
FROM ozf_offers
WHERE qp_list_header_id = cv_plan_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
FROM ozf_offers
WHERE qp_list_header_id = cv_offer_id;
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 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 cla.status_code
,cla.user_status_id
,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 --Bugfix 7717638
,cln.batch_line_id --Bugfix 7811671
FROM ozf_claims cla, ozf_claim_lines cln
WHERE cla.claim_id = cln.claim_id
AND cln.claim_line_id = cv_claim_line_id;
SELECT product_id, product_level_type, fund_id
FROM ozf_funds_utilized_all_b
WHERE utilization_id = cv_utilization_id;
SELECT budget_source_id,
APPROVED_ORIGINAL_AMOUNT
FROM ozf_act_budgets
WHERE transfer_type = 'REQUEST'
AND act_budget_used_by_id = cv_offer_id
AND APPROVED_IN_CURRENCY = G_ACCRUAL_CURRENCY;
SELECT SUM(APPROVED_ORIGINAL_AMOUNT)
FROM ozf_act_budgets
WHERE transfer_type = 'REQUEST'
AND act_budget_used_by_id = cv_offer_id
AND APPROVED_IN_CURRENCY = G_ACCRUAL_CURRENCY;
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 currency_code = G_ACCRUAL_CURRENCY
GROUP BY product_level_type,product_id,fund_id;
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
ORDER BY
sign(batch_curr_claim_amount) ASC
, util.creation_date ASC;
SAVEPOINT Update_Group_Line_Util;
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 => p_validation_level
,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
);
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 => p_validation_level
,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
);
l_line_util_tbl(l_counter).update_from_tbl_flag := FND_API.g_true;
OZF_Utility_PVT.debug_message(' Prorate Among selected utilization Lines ');
l_line_util_tbl(l_counter).update_from_tbl_flag := FND_API.g_true;
l_line_util_tbl(l_counter).update_from_tbl_flag := FND_API.g_true;
l_line_util_tbl(l_counter).update_from_tbl_flag := FND_API.g_true;
l_line_util_tbl(l_counter).update_from_tbl_flag := FND_API.g_true;
l_line_util_tbl(l_counter).update_from_tbl_flag := FND_API.g_true;
l_line_util_tbl(l_counter).update_from_tbl_flag := FND_API.g_true;
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_fu_diff_acctd_amt_rem => p_line_util_rec.amount
,p_mode => 'NONE'
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
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 currency_code
from ozf_funds_utilized_all_b
where utilization_id = cv_utilization_id ;
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;
SAVEPOINT Delete_Group_Line_Util;
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
);
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 lu.claim_line_util_id
,lu.activity_product_id
,lu.acctd_amount
FROM ozf_claim_lines_util lu, ozf_claim_lines ln
WHERE ln.claim_id = cv_claim_id
AND lu.claim_line_id = ln.claim_line_id
AND lu.utilization_id = -2;
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 fu.fund_id
,fu.currency_code
,qp.currency_code
FROM ozf_funds_utilized_all_b fu
,qp_list_headers_vl qp
WHERE fu.component_id = cv_offer_id
AND fu.org_id = MO_GLOBAL.GET_CURRENT_ORG_ID()
AND fu.component_type = 'OFFR'
AND fu.utilization_type = 'REQUEST'
AND qp.list_header_id = fu.component_id
GROUP BY fu.fund_id, fu.currency_code, qp.currency_code;
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_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 CURRENCY_CODE , EXCHANGE_RATE_TYPE , EXCHANGE_RATE_DATE , EXCHANGE_RATE
from ozf_funds_utilized_all_b
where utilization_id = cv_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();
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 = MO_GLOBAL.GET_CURRENT_ORG_ID();
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 = MO_GLOBAL.GET_CURRENT_ORG_ID()
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
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 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
, sum(plan_curr_amount_remaining)
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;
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
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
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
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;
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
);