[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT offer_id, offer_type, custom_setup_id, offer_code, tier_level, object_version_number
FROM ozf_offers
WHERE qp_list_header_id = l_qp_list_header_id;
SELECT object_version_number
FROM ozf_act_budgets
WHERE activity_budget_id = l_act_budg_id;
l_modifier_list_rec.offer_operation := 'UPDATE';
l_modifier_list_rec.modifier_operation := 'UPDATE';
ELSIF p_act_product_tbl(i).operation = 'UPDATE' THEN
l_act_product_rec.activity_product_id := p_act_product_tbl(i).activity_product_id;
AMS_ActProduct_PVT.Update_Act_Product(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_act_Product_rec => l_act_product_rec);
ELSIF p_act_product_tbl(i).operation = 'DELETE' THEN
AMS_ActProduct_PVT.Delete_Act_Product(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_act_Product_id => p_act_product_tbl(i).activity_product_id,
p_object_version => p_act_product_tbl(i).object_version_number);
ELSIF p_discount_tbl(i).operation = 'UPDATE' THEN
l_discount_line_rec.offer_discount_line_id := p_discount_tbl(i).offer_discount_line_id;
OZF_Disc_Line_PVT.Update_discount_line(
p_api_version_number => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ozf_discount_line_rec => l_discount_line_rec);
ELSIF p_discount_tbl(i).operation = 'DELETE' THEN
OZF_Disc_Line_PVT.Delete_offer_line(
p_api_version_number => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_offer_discount_line_id => p_discount_tbl(i).offer_discount_line_id,
p_object_version_number => p_discount_tbl(i).object_version_number);
ELSIF p_excl_tbl(i).operation = 'UPDATE' THEN
l_excl_rec.off_discount_product_id := p_excl_tbl(i).off_discount_product_id;
OZF_Disc_Line_PVT.Update_Product_Exclusion(
p_api_version_number => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ozf_excl_rec => l_excl_rec);
ELSIF p_excl_tbl(i).operation = 'DELETE' THEN
OZF_Disc_Line_PVT.Delete_Ozf_Prod_Line(
p_api_version_number => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_off_discount_product_id => p_excl_tbl(i).off_discount_product_id,
p_object_version_number => p_excl_tbl(i).object_version_number);
ELSIF p_offer_tier_tbl(i).operation = 'UPDATE' THEN
l_offer_tier_rec.offer_discount_line_id := p_offer_tier_tbl(i).offer_discount_line_id;
OZF_Disc_Line_PVT.Update_Disc_Tiers(
p_api_version_number => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tier_rec => l_offer_tier_rec);
ELSIF p_offer_tier_tbl(i).operation = 'DELETE' THEN
IF p_offer_tier_tbl(i).parent_discount_line_id IS NULL OR p_offer_tier_tbl(i).parent_discount_line_id = FND_API.g_miss_num THEN
OZF_Disc_Line_PVT.Delete_Disc_tiers(
p_api_version_number => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_parent_discount_line_id => p_offer_tier_tbl(i).offer_discount_line_id);
OZF_Disc_Line_PVT.Delete_Tier_line(
p_api_version_number => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_offer_discount_line_id => p_offer_tier_tbl(i).offer_discount_line_id,
p_object_version_number => p_offer_tier_tbl(i).object_version_number);
ELSIF p_prod_tbl(i).operation = 'UPDATE' THEN
l_prod_rec.off_discount_product_id := p_prod_tbl(i).off_discount_product_id;
OZF_Disc_Line_PVT.Update_Ozf_Prod_Line(
p_api_version_number => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ozf_prod_rec => l_prod_rec);
ELSIF p_prod_tbl(i).operation = 'DELETE' THEN
OZF_Disc_Line_PVT.Delete_Ozf_Prod_Line(
p_api_version_number => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_off_discount_product_id => p_prod_tbl(i).off_discount_product_id,
p_object_version_number => p_prod_tbl(i).object_version_number);
ELSIF p_excl_tbl(i).operation = 'UPDATE' THEN
l_excl_rec.off_discount_product_id := p_excl_tbl(i).off_discount_product_id;
OZF_Disc_Line_PVT.Update_Product_Exclusion(
p_api_version_number => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ozf_excl_rec => l_excl_rec);
ELSIF p_excl_tbl(i).operation = 'DELETE' THEN
OZF_Disc_Line_PVT.Delete_Ozf_Prod_Line(
p_api_version_number => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_off_discount_product_id => p_excl_tbl(i).off_discount_product_id,
p_object_version_number => p_excl_tbl(i).object_version_number);
ELSIF p_offer_tier_tbl(i).operation = 'UPDATE' THEN
l_offer_tier_rec.offer_discount_line_id := p_offer_tier_tbl(i).offer_discount_line_id;
OZF_Disc_Line_PVT.Update_Disc_Tiers(
p_api_version_number => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tier_rec => l_offer_tier_rec);
ELSIF p_offer_tier_tbl(i).operation = 'DELETE' THEN
IF p_offer_tier_tbl(i).parent_discount_line_id IS NULL OR p_offer_tier_tbl(i).parent_discount_line_id = FND_API.g_miss_num THEN
OZF_Disc_Line_PVT.Delete_Disc_tiers(
p_api_version_number => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_parent_discount_line_id => p_offer_tier_tbl(i).offer_discount_line_id);
OZF_Disc_Line_PVT.Delete_Tier_line(
p_api_version_number => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_offer_discount_line_id => p_offer_tier_tbl(i).offer_discount_line_id,
p_object_version_number => p_offer_tier_tbl(i).object_version_number);
ELSIF p_na_qualifier_tbl(i).operation = 'UPDATE' THEN
OZF_Offr_Qual_PVT.Update_Offr_Qual(
p_api_version_number => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ozf_offr_qual_rec => l_na_qualifier_rec);
ELSIF p_na_qualifier_tbl(i).operation = 'DELETE' THEN
OZF_Offr_Qual_PVT.Delete_Offr_Qual(
p_api_version_number => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_qualifier_id => l_na_qualifier_rec.qualifier_id,
p_object_version_number => l_na_qualifier_rec.object_version_number);
ELSIF p_budget_tbl(i).operation = 'UPDATE' THEN
Ozf_Actbudgets_Pvt.update_act_budgets(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validation_level => Fnd_Api.g_valid_level_full
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_act_budgets_rec => l_act_budgets_rec);
ELSIF p_budget_tbl(i).operation = 'DELETE' THEN
Ozf_Actbudgets_Pvt.delete_act_budgets(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validation_level => Fnd_Api.g_valid_level_full
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_act_budget_id => l_act_budgets_rec.activity_budget_id
,p_object_version => l_act_budgets_rec.object_version_number);
SELECT object_version_number
FROM ozf_act_budgets
WHERE activity_budget_id = l_act_budg_id;
SELECT offer_id, offer_type, custom_setup_id, offer_code, object_version_number
FROM ozf_offers
WHERE qp_list_header_id = l_qp_list_header_id;
l_modifier_list_rec.offer_operation := 'UPDATE';
l_modifier_list_rec.modifier_operation := 'UPDATE';
ELSIF p_qualifier_tbl(i).operation = 'UPDATE' THEN
ozf_volume_offer_qual_pvt.update_vo_qualifier(
p_api_version_number => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validation_level => Fnd_Api.g_valid_level_full
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_qualifiers_rec => l_qualifier_rec);
ELSIF p_qualifier_tbl(i).operation = 'DELETE' THEN
ozf_volume_offer_qual_pvt.delete_vo_qualifier(
p_api_version_number => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validation_level => Fnd_Api.g_valid_level_full
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_qualifier_id => l_qualifier_rec.qualifier_id);
ELSIF p_budget_tbl(i).operation = 'UPDATE' THEN
Ozf_Actbudgets_Pvt.update_act_budgets(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validation_level => Fnd_Api.g_valid_level_full
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_act_budgets_rec => l_act_budgets_rec);
ELSIF p_budget_tbl(i).operation = 'DELETE' THEN
Ozf_Actbudgets_Pvt.delete_act_budgets(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validation_level => Fnd_Api.g_valid_level_full
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_act_budget_id => l_act_budgets_rec.activity_budget_id
,p_object_version => l_act_budgets_rec.object_version_number);
ELSIF p_vo_pbh_tbl(i).operation = 'UPDATE' THEN
ozf_volume_offer_disc_pvt.update_vo_discount(
p_api_version_number => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_vo_disc_rec => l_vo_pbh_rec);
ELSIF p_vo_pbh_tbl(i).operation = 'DELETE' THEN
ozf_volume_offer_disc_pvt.delete_vo_discount(
p_api_version_number => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_offer_discount_line_id => l_vo_pbh_rec.offer_discount_line_id
,p_object_version_number => l_vo_pbh_rec.object_version_number);
ELSIF p_vo_dis_tbl(j).operation = 'UPDATE' THEN
ozf_volume_offer_disc_pvt.update_vo_discount(
p_api_version_number => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_vo_disc_rec => l_vo_dis_rec);
ELSIF p_vo_dis_tbl(j).operation = 'DELETE' THEN
ozf_volume_offer_disc_pvt.delete_vo_discount(
p_api_version_number => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_offer_discount_line_id => l_vo_dis_rec.offer_discount_line_id
,p_object_version_number => l_vo_dis_rec.object_version_number);
ELSIF p_vo_prod_tbl(k).operation = 'UPDATE' THEN
ozf_volume_offer_disc_pvt.update_vo_product(
p_api_version_number => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_vo_prod_rec => l_vo_prod_rec);
ELSIF p_vo_prod_tbl(k).operation = 'DELETE' THEN
ozf_volume_offer_disc_pvt.delete_vo_product(
p_api_version_number => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_off_discount_product_id => l_vo_prod_rec.off_discount_product_id
,p_object_version_number => l_vo_prod_rec.object_version_number);
ELSIF p_vo_mo_tbl(i).operation = 'UPDATE' THEN
ozf_offer_market_options_pvt.update_market_options(
p_api_version_number => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_mo_rec => l_vo_mo_rec);
ELSIF p_vo_mo_tbl(i).operation = 'DELETE' THEN
ozf_offer_market_options_pvt.delete_market_options(
p_api_version_number => p_api_version
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_offer_market_option_id => l_vo_mo_rec.offer_market_option_id
,p_object_version_number => l_vo_mo_rec.object_version_number);
SELECT offer_id, object_version_number
FROM ozf_offers
WHERE qp_list_header_id = l_list_header_id;
SELECT NVL(map.xref_line_id_value,line.list_line_id) list_line_id
, NVL(map.list_line_number,list_line_no) list_line_no
FROM qp_list_lines line, ozf_xref_map map
WHERE list_header_id = p_list_header_id
AND line.list_line_id = map.list_line_id(+);
SELECT NVL(map.xref_line_id_value,prod.activity_product_id) activity_product_id
,NVL(map.list_line_number,prod.attribute1) attribute1
FROM ams_act_products prod, ozf_xref_map map
WHERE act_product_used_by_id = p_list_header_id
AND arc_act_product_used_by = 'OFFR'
AND prod.activity_product_id = map.list_line_id(+);
select jtf.resource_id
from jtf_rs_resource_extns jtf,fnd_user fnd
where upper(fnd.user_name) = cv_user_name --'SIEBEL'
and fnd.user_id = jtf.user_id;
select primary_uom_code
from mtl_system_items
where inventory_item_id = p_product_id
and organization_id = p_org_id;
SELECT fund_id
FROM ozf_funds_all_b
WHERE fund_id = p_fund_id;
SELECT currency_code
FROM fnd_currencies
WHERE currency_code = p_currency_code
AND enabled_flag='Y';
INSERT INTO ozf_xref_map ( map_attr_id
, list_line_id
, list_line_number
, xref_line_id_value
, xref_line_number_value
)
VALUES ( x_qp_list_header_id
, l_act_product_id
, l_act_product_id
, TO_NUMBER(l_act_product_id||'.'||'2')
, l_act_product_id
);
INSERT INTO ozf_xref_map ( map_attr_id
, list_line_id
, list_line_number
, xref_line_id_value
, xref_line_number_value
)
VALUES ( x_qp_list_header_id
, list_line_rec.list_line_id
, list_line_rec.list_line_no
, x_line_tbl(k).list_line_id
, x_line_tbl(k).list_line_number
);
l_modifier_list_rec.offer_operation := 'UPDATE';
l_modifier_list_rec.modifier_operation := 'UPDATE';
l_modifier_line_tbl.DELETE;