[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1 FROM ozf_time_ent_period WHERE ent_period_id = p_time_id;
SELECT 1 FROM ozf_time_ent_qtr WHERE ent_qtr_id = p_time_id;
Columns allowed to update
-------------------------
short_name
description
quota_amount
owner
threshold_id
Derived
-------
status_code
start_date_active
end_date_active
currency_code_tc
created_from
*/
---------------------------------------------------------------------
PROCEDURE validate_quota_attributes (
p_quota_rec IN OUT NOCOPY quota_rec_type
,p_mode IN VARCHAR2
,p_method IN VARCHAR2 := FND_API.G_MISS_CHAR
,p_fund_rec IN OUT NOCOPY OZF_Funds_Pub.fund_rec_type
,x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name VARCHAR(30) := 'Validate_Quota_Attributes';
SELECT 1
FROM ozf_funds_all_b
WHERE fund_type = 'QUOTA'
AND fund_id = p_quota_id;
SELECT fund_id
FROM ozf_funds_all_b
WHERE fund_type = 'QUOTA'
AND fund_number = p_quota_number;
SELECT custom_setup_id
FROM ams_custom_setups_vl
WHERE object_type = 'FUND'
AND application_id = 682
AND activity_type_code = 'QUOTA'
AND custom_setup_id = p_custom_setup_id;
SELECT start_date, end_date FROM OZF_TIME_ENT_PERIOD
WHERE name = p_period
UNION ALL
SELECT start_date, end_date FROM OZF_TIME_ENT_QTR
WHERE name = p_period
UNION ALL
SELECT start_date, end_date FROM OZF_TIME_ENT_YEAR
WHERE name = p_period;
SELECT system_status_code
FROM ams_user_statuses_vl
WHERE system_status_type = 'OZF_FUND_STATUS'
AND user_status_id = p_user_status_id
AND enabled_flag ='Y';
SELECT 1
FROM jtf_rs_resource_extns
WHERE resource_id = p_resource_id
AND category = 'EMPLOYEE';
SELECT 1
FROM ozf_thresholds_all_b
WHERE threshold_id = p_threshold_id
AND threshold_type = 'QUOTA';
IF p_mode = 'UPDATE'
THEN
-- UPDATE MODE
--if both quota id and quota number are null, then raise exception
IF (p_quota_rec.quota_id = fnd_api.g_miss_num OR p_quota_rec.quota_id IS NULL)
AND
(p_quota_rec.quota_number = fnd_api.g_miss_char OR p_quota_rec.quota_number IS NULL)
THEN
IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
fnd_message.set_name('OZF', 'OZF_NO_QUOTA_ID_NUM');
END IF; -- End Update Mode
SELECT 1
FROM ozf_funds_all_b
WHERE fund_type = 'QUOTA'
AND fund_id = p_quota_id;
SELECT fund_id
FROM ozf_funds_all_b
WHERE fund_type = 'QUOTA'
AND fund_number = p_quota_number;
SELECT 1 FROM OZF_TIME_ENT_PERIOD
WHERE name = p_period
UNION
SELECT 1 FROM OZF_TIME_ENT_QTR
WHERE name = p_period
UNION
SELECT 1 FROM OZF_TIME_ENT_YEAR
WHERE name = p_period;
SELECT start_date
FROM gl_periods_v
WHERE period_set_name = fnd_profile.value ('AMS_CAMPAIGN_DEFAULT_CALENDER')
AND period_name = p_period;
SELECT end_date
FROM gl_periods_v
WHERE period_set_name = fnd_profile.value ('AMS_CAMPAIGN_DEFAULT_CALENDER')
AND period_name = p_period;
SELECT product_spread_time_id
FROM ozf_funds_all_b
WHERE fund_id = p_quota_id;
SELECT heirarchy_id
FROM ozf_terr_levels_all
WHERE heirarchy_id = p_hier_id;
SELECT level_depth
FROM ozf_terr_levels_all
WHERE heirarchy_id = p_hier_id
AND level_depth = p_from_level;
SELECT level_depth
FROM ozf_terr_levels_all
WHERE heirarchy_id = p_hier_id
AND level_depth >= p_from_level
AND level_depth = p_to_level;
SELECT node_id
FROM ozf_terr_v
WHERE hierarchy_id = p_hier_id
AND level_depth = p_from_level;
SELECT lookup_code
FROM ozf_lookups
WHERE lookup_type = 'OZF_FUND_ALLOC_METHOD'
AND lookup_code = p_method_code;
SELECT ozf_product_allocations_s.NEXTVAL
FROM DUAL;
SELECT count(p.product_allocation_id)
FROM ozf_product_allocations p
WHERE p.product_allocation_id = p_product_alloc_id;
SELECT ozf_time_allocations_s.NEXTVAL
FROM DUAL;
SELECT count(t.time_allocation_id)
FROM ozf_time_allocations t
WHERE t.time_allocation_id = p_time_alloc_id;
SELECT ozf_account_allocations_s.NEXTVAL
FROM DUAL;
SELECT count(account_allocation_id)
FROM ozf_account_allocations
WHERE account_allocation_id = p_account_alloc_id;
Ozf_Product_Allocations_Pkg.Insert_Row(
px_product_allocation_id => l_product_allocation_id,
p_allocation_for => p_product_alloc_rec.allocation_for,
p_allocation_for_id => p_product_alloc_rec.allocation_for_id,
p_fund_id => p_product_alloc_rec.fund_id,
p_item_type => p_product_alloc_rec.item_type,
p_item_id => p_product_alloc_rec.item_id,
p_selected_flag => p_product_alloc_rec.selected_flag,
p_target => NVL(p_product_alloc_rec.target, 0),
p_lysp_sales => NVL(p_product_alloc_rec.lysp_sales, 0),
p_parent_product_allocation_id => p_product_alloc_rec.parent_product_allocation_id,
px_object_version_number => l_object_version_number,
p_creation_date => SYSDATE,
p_created_by => FND_GLOBAL.USER_ID,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.conc_login_id,
p_attribute_category => p_product_alloc_rec.attribute_category,
p_attribute1 => p_product_alloc_rec.attribute1,
p_attribute2 => p_product_alloc_rec.attribute2,
p_attribute3 => p_product_alloc_rec.attribute3,
p_attribute4 => p_product_alloc_rec.attribute4,
p_attribute5 => p_product_alloc_rec.attribute5,
p_attribute6 => p_product_alloc_rec.attribute6,
p_attribute7 => p_product_alloc_rec.attribute7,
p_attribute8 => p_product_alloc_rec.attribute8,
p_attribute9 => p_product_alloc_rec.attribute9,
p_attribute10 => p_product_alloc_rec.attribute10,
p_attribute11 => p_product_alloc_rec.attribute11,
p_attribute12 => p_product_alloc_rec.attribute12,
p_attribute13 => p_product_alloc_rec.attribute13,
p_attribute14 => p_product_alloc_rec.attribute14,
p_attribute15 => p_product_alloc_rec.attribute15,
px_org_id => l_org_id
);
Ozf_Account_Allocations_Pkg.Insert_Row(
px_Account_allocation_id => l_account_allocation_id,
p_allocation_for => p_account_alloc_rec.allocation_for,
p_allocation_for_id => p_account_alloc_rec.allocation_for_id,
p_cust_account_id => p_account_alloc_rec.cust_account_id,
p_site_use_id => p_account_alloc_rec.site_use_id,
p_site_use_code => 'SHIP_TO',
p_location_id => p_account_alloc_rec.location_id,
p_bill_to_site_use_id => p_account_alloc_rec.bill_to_site_use_id,
p_bill_to_location_id => p_account_alloc_rec.bill_to_location_id,
p_parent_party_id => p_account_alloc_rec.parent_party_id,
p_rollup_party_id => p_account_alloc_rec.rollup_party_id,
p_selected_flag => p_account_alloc_rec.selected_flag,
p_target => p_account_alloc_rec.target,
p_lysp_sales => p_account_alloc_rec.lysp_sales,
p_parent_Account_allocation_id => p_account_alloc_rec.parent_Account_allocation_id,
px_object_version_number => l_object_version_number,
p_creation_date => SYSDATE,
p_created_by => FND_GLOBAL.USER_ID,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.conc_login_id,
p_attribute_category => p_account_alloc_rec.attribute_category,
p_attribute1 => p_account_alloc_rec.attribute1,
p_attribute2 => p_account_alloc_rec.attribute2,
p_attribute3 => p_account_alloc_rec.attribute3,
p_attribute4 => p_account_alloc_rec.attribute4,
p_attribute5 => p_account_alloc_rec.attribute5,
p_attribute6 => p_account_alloc_rec.attribute6,
p_attribute7 => p_account_alloc_rec.attribute7,
p_attribute8 => p_account_alloc_rec.attribute8,
p_attribute9 => p_account_alloc_rec.attribute9,
p_attribute10 => p_account_alloc_rec.attribute10,
p_attribute11 => p_account_alloc_rec.attribute11,
p_attribute12 => p_account_alloc_rec.attribute12,
p_attribute13 => p_account_alloc_rec.attribute13,
p_attribute14 => p_account_alloc_rec.attribute14,
p_attribute15 => p_account_alloc_rec.attribute15,
px_org_id => l_org_id
);
Ozf_Time_Allocations_Pkg.Insert_Row(
px_time_allocation_id => l_time_allocation_id,
p_allocation_for => p_time_alloc_rec.allocation_for,
p_allocation_for_id => p_time_alloc_rec.allocation_for_id,
p_time_id => p_time_alloc_rec.time_id,
p_period_type_id => p_time_alloc_rec.period_type_id,
p_target => NVL(p_time_alloc_rec.target, 0),
p_lysp_sales => NVL(p_time_alloc_rec.lysp_sales, 0),
px_object_version_number => l_object_version_number,
p_creation_date => SYSDATE,
p_created_by => FND_GLOBAL.USER_ID,
p_last_update_date => SYSDATE,
p_last_updated_by => FND_GLOBAL.USER_ID,
p_last_update_login => FND_GLOBAL.conc_login_id,
p_attribute_category => p_time_alloc_rec.attribute_category,
p_attribute1 => p_time_alloc_rec.attribute1,
p_attribute2 => p_time_alloc_rec.attribute2,
p_attribute3 => p_time_alloc_rec.attribute3,
p_attribute4 => p_time_alloc_rec.attribute4,
p_attribute5 => p_time_alloc_rec.attribute5,
p_attribute6 => p_time_alloc_rec.attribute6,
p_attribute7 => p_time_alloc_rec.attribute7,
p_attribute8 => p_time_alloc_rec.attribute8,
p_attribute9 => p_time_alloc_rec.attribute9,
p_attribute10 => p_time_alloc_rec.attribute10,
p_attribute11 => p_time_alloc_rec.attribute11,
p_attribute12 => p_time_alloc_rec.attribute12,
p_attribute13 => p_time_alloc_rec.attribute13,
p_attribute14 => p_time_alloc_rec.attribute14,
p_attribute15 => p_time_alloc_rec.attribute15,
px_org_id => l_org_id
);
l_product_alloc_rec.selected_flag := 'N';
SELECT party_site.party_id,
acct_site.cust_account_id,
party_site.location_id,
site_use.bill_to_site_use_id
FROM hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_cust_site_uses_all site_use
WHERE site_use.site_use_id = p_site_use_id
AND site_use.site_use_code = p_site_use_code
AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.party_site_id = party_site.party_site_id ;
l_account_alloc_rec.selected_flag := p_quota_accounts_tbl(l_account_counter).selected_flag;
l_prod_for_this_acct_tbl.DELETE;
l_prod_sprd_for_this_acct_tbl.DELETE;
PROCEDURE update_quota(
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_quota_rec IN quota_rec_type
)
IS
l_api_name VARCHAR(30) := 'update_quota';
l_mode VARCHAR2(6) := 'UPDATE';
SELECT fund_id
FROM ozf_funds_all_b
WHERE fund_number = quota_number;
SELECT fund_number, short_name, custom_setup_id, description, status_code,
user_status_id, start_period_name, end_period_name, original_budget,
currency_code_tc, owner, threshold_id, product_spread_time_id, object_version_number
FROM ozf_funds_all_vl
WHERE fund_id = quota_id;
SAVEPOINT update_quota;
,p_mode => 'UPDATE'
,p_method => 'MANUAL'
,p_fund_rec => l_fund_rec
,x_return_status => x_return_status);
ozf_funds_pub.update_fund(p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,p_commit => l_commit
,p_validation_level => l_validation_level
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_fund_rec => l_fund_rec
,p_modifier_list_rec => l_modifier_list_rec
,p_modifier_line_tbl => l_modifier_line_tbl
,p_vo_pbh_tbl => l_vo_pbh_tbl
,p_vo_dis_tbl => l_vo_dis_tbl
,p_vo_prod_tbl => l_vo_prod_tbl
,p_qualifier_tbl => l_qualifier_tbl
,p_vo_mo_tbl => l_vo_mo_tbl
);
ROLLBACK TO update_quota;
ROLLBACK TO update_quota;
ROLLBACK TO update_quota;
END Update_Quota;
PROCEDURE Update_Quota_Product_Spread(
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_quota_products_tbl IN quota_products_tbl_type
,p_quota_prod_spread_tbl IN quota_prod_spread_tbl_type )
IS
l_api_name VARCHAR(30) := 'update_quota_product_spread';
SELECT product_allocation_id,
object_version_number
FROM ozf_product_allocations
WHERE product_allocation_id = p_product_allocation_id;
SELECT time_allocation_id,
object_version_number
FROM ozf_time_allocations
WHERE time_allocation_id = p_time_allocation_id;
SAVEPOINT UPDATE_QUOTA_PRODUCT_SPREAD;
UPDATE ozf_product_allocations
SET target = NVL(p_quota_products_tbl(l_prod_alloc_counter).target, target),
lysp_sales = NVL(p_quota_products_tbl(l_prod_alloc_counter).lysp_sales, lysp_sales),
object_version_number = l_object_version_number + 1 ,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE product_allocation_id = l_product_allocation_id;
UPDATE ozf_time_allocations
SET target = NVL(p_quota_prod_spread_tbl(l_prod_sprd_counter).target, target),
lysp_sales = NVL(p_quota_prod_spread_tbl(l_prod_sprd_counter).lysp_sales, lysp_sales),
object_version_number = l_object_version_number + 1 ,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE time_allocation_id = l_time_allocation_id;
ROLLBACK TO update_quota_product_spread;
ROLLBACK TO update_quota_product_spread;
ROLLBACK TO update_quota_product_spread;
END Update_Quota_Product_Spread;
PROCEDURE Update_Quota_Account_Spread(
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_quota_accounts_tbl IN quota_accounts_tbl_type
,p_account_spread_tbl IN account_spread_tbl_type )
IS
l_api_name VARCHAR(30) := 'update_quota_account_spread';
SELECT account_allocation_id,
object_version_number
FROM ozf_account_allocations
WHERE account_allocation_id = p_account_allocation_id;
SELECT time_allocation_id,
object_version_number
FROM ozf_time_allocations
WHERE time_allocation_id = p_time_allocation_id;
SAVEPOINT UPDATE_QUOTA_ACCOUNT_SPREAD;
UPDATE ozf_account_allocations
SET target = NVL(p_quota_accounts_tbl(l_acct_alloc_counter).target, target),
lysp_sales = NVL(p_quota_accounts_tbl(l_acct_alloc_counter).lysp_sales, lysp_sales),
object_version_number = l_object_version_number + 1 ,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE account_allocation_id = l_account_allocation_id;
UPDATE ozf_time_allocations
SET target = NVL(p_account_spread_tbl(l_acct_sprd_counter).target, target),
lysp_sales = NVL(p_account_spread_tbl(l_acct_sprd_counter).lysp_sales, lysp_sales),
object_version_number = l_object_version_number + 1 ,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID
WHERE time_allocation_id = l_time_allocation_id;
ROLLBACK TO update_quota_account_spread;
ROLLBACK TO update_quota_account_spread;
ROLLBACK TO update_quota_account_spread;
END Update_Quota_Account_Spread ;
PROCEDURE delete_quota_allocations ( p_quota_id IN NUMBER) IS
CURSOR c_child_quotas (c_quota_id NUMBER) IS
SELECT fund_id
from ozf_funds_all_b
where fund_type = 'QUOTA'
and parent_fund_id = c_quota_id;
delete_quota_allocations(i.fund_id);
delete from ozf_time_allocations
where allocation_for = 'PROD'
and allocation_for_id in ( select product_allocation_id
from ozf_product_allocations
where allocation_for = 'CUST'
and allocation_for_id in ( select account_allocation_id
from ozf_account_allocations
where allocation_for = 'FUND'
and allocation_for_id = p_quota_id)
);
delete from ozf_product_allocations
where allocation_for = 'CUST'
and allocation_for_id in ( select account_allocation_id
from ozf_account_allocations
where allocation_for = 'FUND'
and allocation_for_id = p_quota_id);
delete from ozf_time_allocations
where allocation_for = 'CUST'
and allocation_for_id in (select account_allocation_id
from ozf_account_allocations
where allocation_for = 'FUND'
and allocation_for_id = p_quota_id ) ;
delete from ozf_account_allocations
where allocation_for = 'FUND'
and allocation_for_id = p_quota_id ;
delete from ozf_time_allocations
where allocation_for = 'PROD'
and allocation_for_id in (select product_allocation_id
from ozf_product_allocations
where allocation_for = 'FUND'
and allocation_for_id = p_quota_id ) ;
delete from ozf_product_allocations
where allocation_for = 'FUND'
and allocation_for_id = p_quota_id;
PROCEDURE delete_quota(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_quota_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name VARCHAR(30) := 'delete_quota';
SELECT object_version_number
FROM ozf_funds_all_b
WHERE fund_type = 'QUOTA'
AND fund_id = p_quota_id ;
SAVEPOINT delete_quota;
delete_quota_allocations(p_quota_id);
OZF_FUNDS_PUB.delete_fund(p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,p_commit => l_commit
,p_fund_id => p_quota_id
,p_object_version => l_object_version
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO delete_quota;
ROLLBACK TO delete_quota;
ROLLBACK TO delete_quota;
END delete_quota;
SELECT 1
FROM ozf_funds_all_b
WHERE fund_type = 'QUOTA'
AND fund_id = p_quota_id;
SELECT 1
FROM ams_act_products
WHERE act_product_used_by_id = p_quota_id
and arc_act_product_used_by = 'FUND';
SELECT 1
FROM ozf_product_allocations
WHERE fund_id = p_quota_id;
SELECT 1
FROM ozf_act_metrics_all
WHERE activity_metric_id = p_alloc_id;
SELECT status_code, object_version_number
FROM ozf_act_metrics_all
WHERE activity_metric_id = p_alloc_id;
PROCEDURE update_alloc_status(
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
,p_alloc_id IN NUMBER
,p_alloc_status IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name VARCHAR(30) := 'create_allocation';
SELECT object_version_number
FROM ozf_act_metrics_all
WHERE activity_metric_id = p_alloc_id;
SAVEPOINT update_alloc_status;
ozf_fund_allocations_pvt.update_alloc_status(p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,p_commit => l_commit
,p_validation_level => l_validation_level
,p_alloc_id => p_alloc_id
,p_alloc_status => p_alloc_status
,p_alloc_obj_ver => l_alloc_obj_ver
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO update_alloc_status;
ROLLBACK TO update_alloc_status;
ROLLBACK TO update_alloc_status;
END update_alloc_status;