[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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_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_account_allocations_s.NEXTVAL
FROM DUAL;
SELECT count(account_allocation_id)
FROM ozf_account_allocations
WHERE account_allocation_id = p_account_alloc_id;
SELECT
a.allocation_for_id,
a.site_use_id
FROM
ozf_account_allocations a
WHERE
a.account_allocation_id = l_account_allocation_id;
SELECT
fund.node_id territory_id
FROM
ozf_funds_all_vl fund
WHERE
fund.fund_id = l_fund_id;
SELECT
j.terr_id territory_id
FROM
ozf_funds_all_vl fund, jtf_terr_rsc_all j, jtf_terr_rsc_access_all j2
WHERE
fund.fund_id = l_fund_id
AND j.resource_id = fund.owner
-- AND j.primary_contact_flag = 'Y' ;
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_id = NVL(l_site_use_id, a.site_use_id)
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.inventory_item_id = l_product_id
AND bsmv.time_id = l_time_id;
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id = l_time_id
AND bsmv.inventory_item_id IN
( SELECT DISTINCT MIC.INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES MIC,
ENI_PROD_DENORM_HRCHY_V DENORM
WHERE MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID
AND MIC.CATEGORY_ID = DENORM.CHILD_ID
AND DENORM.PARENT_ID = l_category_id
MINUS
SELECT a.inventory_item_id
FROM ams_act_products a
WHERE act_product_used_by_id = l_fund_id
AND arc_act_product_used_by = 'FUND'
AND level_type_code = 'PRODUCT'
AND excluded_flag IN ('Y', 'N')
);
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_id = l_site_use_id
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id = l_time_id
AND bsmv.inventory_item_id IN
(SELECT DISTINCT MIC.INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES MIC,
ENI_PROD_DENORM_HRCHY_V DENORM
WHERE MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID
AND MIC.CATEGORY_ID = DENORM.CHILD_ID
AND DENORM.PARENT_ID = l_category_id
MINUS
SELECT p.item_id
FROM ozf_product_allocations p
WHERE p.fund_id = l_fund_id
AND p.item_type = 'PRICING_ATTRIBUTE1'
);
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id = l_time_id
AND NOT EXISTS
(
( SELECT prod.inventory_item_id
FROM ams_act_products prod
WHERE
prod.level_type_code = 'PRODUCT'
AND prod.arc_act_product_used_by = 'FUND'
AND prod.act_product_used_by_id = l_fund_id
AND prod.excluded_flag = 'N'
AND prod.inventory_item_id = bsmv.inventory_item_id
UNION ALL
SELECT MIC.INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES MIC,
ENI_PROD_DENORM_HRCHY_V DENORM,
AMS_ACT_PRODUCTS prod
WHERE
prod.level_type_code = 'FAMILY'
AND prod.arc_act_product_used_by = 'FUND'
AND prod.act_product_used_by_id = l_fund_id
AND prod.excluded_flag = 'N'
AND prod.category_id = DENORM.PARENT_ID
AND MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID
AND MIC.CATEGORY_ID = DENORM.CHILD_ID
AND MIC.INVENTORY_ITEM_ID = bsmv.inventory_item_id
)
MINUS
SELECT prod.inventory_item_id
FROM ams_act_products prod
where
prod.level_type_code = 'PRODUCT'
AND prod.arc_act_product_used_by = 'FUND'
AND prod.act_product_used_by_id = l_fund_id
AND prod.excluded_flag = 'Y'
AND prod.inventory_item_id = bsmv.inventory_item_id
);
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_id = l_site_use_id
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id = l_time_id
AND NOT EXISTS
(
( SELECT p.item_id
FROM ozf_product_allocations p
WHERE
p.fund_id = l_fund_id
AND p.item_type = 'PRICING_ATTRIBUTE1'
AND p.item_id = bsmv.inventory_item_id
UNION ALL
SELECT MIC.INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES MIC,
ENI_PROD_DENORM_HRCHY_V DENORM,
OZF_PRODUCT_ALLOCATIONS p
WHERE p.FUND_ID = l_fund_id
AND p.ITEM_TYPE = 'PRICING_ATTRIBUTE2'
AND p.ITEM_ID = DENORM.PARENT_ID
AND MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID
AND MIC.CATEGORY_ID = DENORM.CHILD_ID
AND MIC.INVENTORY_ITEM_ID = bsmv.inventory_item_id
)
MINUS
SELECT prod.inventory_item_id
FROM ams_act_products prod
where
prod.level_type_code = 'PRODUCT'
AND prod.arc_act_product_used_by = 'FUND'
AND prod.act_product_used_by_id = l_fund_id
AND prod.excluded_flag = 'Y'
AND prod.inventory_item_id = bsmv.inventory_item_id
);
UPDATE OZF_TIME_ALLOCATIONS t
SET t.TARGET = t.TARGET + l_diff_target,
t.object_version_number = t.object_version_number + 1,
t.last_update_date = SYSDATE,
t.last_updated_by = FND_GLOBAL.USER_ID,
t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id)
FROM OZF_TIME_ALLOCATIONS x
WHERE x.allocation_for = 'PROD'
AND x.allocation_for_id IN ( SELECT max(p.product_allocation_id)
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = l_object_type
AND p.allocation_for_id = l_object_id
AND p.target =
(SELECT max(xz.target)
FROM OZF_PRODUCT_ALLOCATIONS xz
WHERE xz.allocation_for = l_object_type
AND xz.allocation_for_id = l_object_id
)
)
AND x.target =
(SELECT max(zx.target)
FROM OZF_TIME_ALLOCATIONS zx
WHERE zx.allocation_for = 'PROD'
AND zx.allocation_for_id IN (SELECT max(pz.product_allocation_id)
FROM OZF_PRODUCT_ALLOCATIONS pz
WHERE pz.allocation_for = l_object_type
AND pz.allocation_for_id = l_object_id
AND pz.target =
(SELECT max(xz.target)
FROM OZF_PRODUCT_ALLOCATIONS xz
WHERE xz.allocation_for = l_object_type
AND xz.allocation_for_id = l_object_id
)
)
)
)
RETURNING t.allocation_for_id INTO l_temp_product_allocation_id;
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.TARGET = p.TARGET + l_diff_target,
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.product_allocation_id = l_temp_product_allocation_id;
UPDATE OZF_TIME_ALLOCATIONS t
SET t.TARGET = t.TARGET + l_diff_target,
t.object_version_number = t.object_version_number + 1,
t.last_update_date = SYSDATE,
t.last_updated_by = FND_GLOBAL.USER_ID,
t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
WHERE x.allocation_for = 'PROD'
AND x.allocation_for_id IN ( SELECT p.product_allocation_id
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'CUST'
AND p.allocation_for_id = l_account_allocation_id
AND p.item_id = -9999 )
);
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.TARGET = p.TARGET + l_diff_target,
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.allocation_for = 'CUST'
AND p.allocation_for_id = l_account_allocation_id
AND p.item_id = -9999;
SELECT
activity_metric_fact_id,
act_metric_used_by_id,
arc_act_metric_used_by,
activity_metric_id,
hierarchy_id,
hierarchy_type,
node_id,
previous_fact_id,
recommend_total_amount,
status_code
FROM
OZF_ACT_METRIC_FACTS_ALL
WHERE
activity_metric_fact_id = l_fact_id;
SELECT
activity_metric_id,
arc_act_metric_used_by,
act_metric_used_by_id,
product_spread_time_id period_type_id, -- (eg.. 32 for monthly, 64 for quarterly),
published_flag,
status_code,
start_period_name,
end_period_name,
from_date,
to_date
FROM
OZF_ACT_METRICS_ALL
WHERE
activity_metric_id = l_allocation_id;
SELECT
owner,
start_period_id,
end_period_id,
start_date_active,
end_date_active,
status_code,
original_budget,
transfered_in_amt,
transfered_out_amt,
node_id, -- (=territory id)
product_spread_time_id period_type_id -- (= minor_scale_id i.e. qtrly or monthly)
FROM
ozf_funds_all_vl
WHERE
fund_id = l_fund_id;
SELECT SUM(t.target)
FROM
ozf_time_allocations t,
ozf_product_allocations p
WHERE
p.fund_id = l_fund_id
AND t.allocation_for_id = p.product_allocation_id
AND t.allocation_for = 'PROD'
AND t.time_id IN (l_in_clause);
' SELECT SUM(t.target) '||
' FROM '||
' ozf_time_allocations t,'||
' ozf_product_allocations p'||
' WHERE'||
' p.fund_id = :l_fund_id'||
' AND t.allocation_for_id = p.product_allocation_id'||
' AND t.allocation_for = ''PROD'' '||
' AND t.time_id IN (';
SELECT
p.product_allocation_id,
p.item_id,
p.item_type
FROM
ozf_product_allocations p
WHERE
p.fund_id = l_fund_id;
SELECT t.target
FROM
ozf_time_allocations t
WHERE
t.allocation_for_id = l_product_allocation_id
AND t.allocation_for = 'PROD'
AND t.time_id = l_time_id;
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_id = bsmv.ship_to_site_use_id
AND bsmv.inventory_item_id = l_product_id
AND bsmv.time_id = l_time_id;
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id = l_time_id
AND bsmv.inventory_item_id IN
( SELECT DISTINCT MIC.INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES MIC,
ENI_PROD_DENORM_HRCHY_V DENORM
WHERE MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID
AND MIC.CATEGORY_ID = DENORM.CHILD_ID
AND DENORM.PARENT_ID = l_category_id
MINUS
SELECT p.item_id
FROM ozf_product_allocations p
WHERE p.fund_id = l_fund_id
AND p.item_type = 'PRICING_ATTRIBUTE1'
);
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id = l_time_id
AND NOT EXISTS
(
( SELECT p.item_id
FROM ozf_product_allocations p
WHERE
p.fund_id = l_fund_id
AND p.item_type = 'PRICING_ATTRIBUTE1'
AND p.item_id = bsmv.inventory_item_id
UNION ALL
SELECT MIC.INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES MIC,
ENI_PROD_DENORM_HRCHY_V DENORM,
OZF_PRODUCT_ALLOCATIONS p
WHERE p.FUND_ID = l_fund_id
AND p.ITEM_TYPE = 'PRICING_ATTRIBUTE2'
AND p.ITEM_ID = DENORM.PARENT_ID
AND MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID
AND MIC.CATEGORY_ID = DENORM.CHILD_ID
AND MIC.INVENTORY_ITEM_ID = bsmv.inventory_item_id
)
MINUS
SELECT prod.inventory_item_id
FROM ams_act_products prod
where
prod.level_type_code = 'PRODUCT'
AND prod.arc_act_product_used_by = 'FUND'
AND prod.act_product_used_by_id = l_fund_id
AND prod.excluded_flag = 'Y'
AND prod.inventory_item_id = bsmv.inventory_item_id
);
p_prod_alloc_rec.selected_flag := 'N';
Ozf_Product_Allocations_Pkg.Insert_Row(
px_product_allocation_id => l_product_allocation_id,
p_allocation_for => p_prod_alloc_rec.allocation_for,
p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
p_fund_id => p_prod_alloc_rec.fund_id,
p_item_type => p_prod_alloc_rec.item_type,
p_item_id => p_prod_alloc_rec.item_id,
p_selected_flag => p_prod_alloc_rec.selected_flag,
p_target => NVL(p_prod_alloc_rec.target, 0),
p_lysp_sales => NVL(p_prod_alloc_rec.lysp_sales, 0),
p_parent_product_allocation_id => p_prod_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_prod_alloc_rec.attribute_category,
p_attribute1 => p_prod_alloc_rec.attribute1,
p_attribute2 => p_prod_alloc_rec.attribute2,
p_attribute3 => p_prod_alloc_rec.attribute3,
p_attribute4 => p_prod_alloc_rec.attribute4,
p_attribute5 => p_prod_alloc_rec.attribute5,
p_attribute6 => p_prod_alloc_rec.attribute6,
p_attribute7 => p_prod_alloc_rec.attribute7,
p_attribute8 => p_prod_alloc_rec.attribute8,
p_attribute9 => p_prod_alloc_rec.attribute9,
p_attribute10 => p_prod_alloc_rec.attribute10,
p_attribute11 => p_prod_alloc_rec.attribute11,
p_attribute12 => p_prod_alloc_rec.attribute12,
p_attribute13 => p_prod_alloc_rec.attribute13,
p_attribute14 => p_prod_alloc_rec.attribute14,
p_attribute15 => p_prod_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
);
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.lysp_sales = l_total_lysp_sales,
p.target = ROUND( l_total_quota, 0),
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.product_allocation_id = l_product_allocation_id;
SELECT SUM(p.TARGET) INTO l_diff_target_1
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'FACT'
AND p.allocation_for_id = l_fact_id;
UPDATE OZF_TIME_ALLOCATIONS t
SET t.TARGET = t.TARGET + l_diff_target,
t.object_version_number = t.object_version_number + 1,
t.last_update_date = SYSDATE,
t.last_updated_by = FND_GLOBAL.USER_ID,
t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
WHERE x.allocation_for = 'PROD'
AND x.allocation_for_id IN ( SELECT p.product_allocation_id
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'FACT'
AND p.allocation_for_id = l_fact_id
AND p.item_id = -9999 )
);
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.TARGET = p.TARGET + l_diff_target,
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.allocation_for = 'FACT'
AND p.allocation_for_id = l_fact_id
AND p.item_id = -9999;
SELECT
activity_metric_fact_id,
act_metric_used_by_id,
arc_act_metric_used_by,
activity_metric_id,
hierarchy_id,
hierarchy_type,
node_id,
previous_fact_id,
recommend_total_amount,
status_code
FROM
OZF_ACT_METRIC_FACTS_ALL
WHERE
activity_metric_fact_id = l_fact_id;
SELECT
activity_metric_id,
arc_act_metric_used_by,
act_metric_used_by_id,
product_spread_time_id period_type_id, -- (eg.. 32 for monthly, 64 for quarterly),
published_flag,
status_code,
start_period_name,
end_period_name,
from_date,
to_date
FROM
OZF_ACT_METRICS_ALL
WHERE
activity_metric_id = l_allocation_id;
SELECT
owner,
start_period_id,
end_period_id,
start_date_active,
end_date_active,
status_code,
original_budget,
transfered_in_amt,
transfered_out_amt,
node_id, -- (=territory id)
product_spread_time_id period_type_id -- (= minor_scale_id i.e. qtrly or monthly)
FROM
ozf_funds_all_vl
WHERE
fund_id = l_fund_id;
SELECT SUM(t.target)
FROM
ozf_time_allocations t,
ozf_product_allocations p
WHERE
p.fund_id = l_fund_id
AND t.allocation_for_id = p.product_allocation_id
AND t.allocation_for = 'PROD'
AND t.time_id IN (l_in_clause);
' SELECT SUM(t.target) '||
' FROM '||
' ozf_time_allocations t,'||
' ozf_product_allocations p'||
' WHERE'||
' p.fund_id = :l_fund_id'||
' AND t.allocation_for_id = p.product_allocation_id'||
' AND t.allocation_for = ''PROD'' '||
' AND t.time_id IN (';
SELECT
p.product_allocation_id,
p.item_id,
p.item_type
FROM
ozf_product_allocations p
WHERE
p.fund_id = l_fund_id;
SELECT t.target
FROM
ozf_time_allocations t
WHERE
t.allocation_for_id = l_product_allocation_id
AND t.allocation_for = 'PROD'
AND t.time_id = l_time_id;
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_id = bsmv.ship_to_site_use_id
AND bsmv.inventory_item_id = l_product_id
AND bsmv.time_id = l_time_id;
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id = l_time_id
AND bsmv.inventory_item_id IN
( SELECT DISTINCT MIC.INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES MIC,
ENI_PROD_DENORM_HRCHY_V DENORM
WHERE MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID
AND MIC.CATEGORY_ID = DENORM.CHILD_ID
AND DENORM.PARENT_ID = l_category_id
MINUS
SELECT p.item_id
FROM ozf_product_allocations p
WHERE p.fund_id = l_fund_id
AND p.item_type = 'PRICING_ATTRIBUTE1'
);
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id = l_time_id
AND NOT EXISTS
(
( SELECT p.item_id
FROM ozf_product_allocations p
WHERE
p.fund_id = l_fund_id
AND p.item_type = 'PRICING_ATTRIBUTE1'
AND p.item_id = bsmv.inventory_item_id
UNION ALL
SELECT MIC.INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES MIC,
ENI_PROD_DENORM_HRCHY_V DENORM,
OZF_PRODUCT_ALLOCATIONS p
WHERE p.FUND_ID = l_fund_id
AND p.ITEM_TYPE = 'PRICING_ATTRIBUTE2'
AND p.ITEM_ID = DENORM.PARENT_ID
AND MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID
AND MIC.CATEGORY_ID = DENORM.CHILD_ID
AND MIC.INVENTORY_ITEM_ID = bsmv.inventory_item_id
)
MINUS
SELECT prod.inventory_item_id
FROM ams_act_products prod
where
prod.level_type_code = 'PRODUCT'
AND prod.arc_act_product_used_by = 'FUND'
AND prod.act_product_used_by_id = l_fund_id
AND prod.excluded_flag = 'Y'
AND prod.inventory_item_id = bsmv.inventory_item_id
);
p_prod_alloc_rec.selected_flag := 'N';
Ozf_Product_Allocations_Pkg.Insert_Row(
px_product_allocation_id => l_product_allocation_id,
p_allocation_for => p_prod_alloc_rec.allocation_for,
p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
p_fund_id => p_prod_alloc_rec.fund_id,
p_item_type => p_prod_alloc_rec.item_type,
p_item_id => p_prod_alloc_rec.item_id,
p_selected_flag => p_prod_alloc_rec.selected_flag,
p_target => NVL(p_prod_alloc_rec.target, 0),
p_lysp_sales => NVL(p_prod_alloc_rec.lysp_sales, 0),
p_parent_product_allocation_id => p_prod_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_prod_alloc_rec.attribute_category,
p_attribute1 => p_prod_alloc_rec.attribute1,
p_attribute2 => p_prod_alloc_rec.attribute2,
p_attribute3 => p_prod_alloc_rec.attribute3,
p_attribute4 => p_prod_alloc_rec.attribute4,
p_attribute5 => p_prod_alloc_rec.attribute5,
p_attribute6 => p_prod_alloc_rec.attribute6,
p_attribute7 => p_prod_alloc_rec.attribute7,
p_attribute8 => p_prod_alloc_rec.attribute8,
p_attribute9 => p_prod_alloc_rec.attribute9,
p_attribute10 => p_prod_alloc_rec.attribute10,
p_attribute11 => p_prod_alloc_rec.attribute11,
p_attribute12 => p_prod_alloc_rec.attribute12,
p_attribute13 => p_prod_alloc_rec.attribute13,
p_attribute14 => p_prod_alloc_rec.attribute14,
p_attribute15 => p_prod_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
);
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.lysp_sales = l_total_lysp_sales,
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.product_allocation_id = l_product_allocation_id;
UPDATE OZF_TIME_ALLOCATIONS t
SET t.TARGET = ROUND((NVL(t.LYSP_SALES, 0) * l_multiplying_factor), 0),
t.object_version_number = t.object_version_number + 1,
t.last_update_date = SYSDATE,
t.last_updated_by = FND_GLOBAL.USER_ID,
t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE t.allocation_for = 'PROD'
AND t.allocation_for_id IN ( SELECT p.product_allocation_id
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'FACT'
AND p.allocation_for_id = l_fact_id );
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.TARGET = (SELECT SUM(ti.TARGET)
FROM OZF_TIME_ALLOCATIONS ti
WHERE ti.ALLOCATION_FOR = 'PROD'
AND ti.ALLOCATION_FOR_ID = p.PRODUCT_ALLOCATION_ID),
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.allocation_for = 'FACT'
AND p.allocation_for_id = l_fact_id;
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.TARGET = ROUND(NVL(l_fact_rec.recommend_total_amount, 0),0),
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.allocation_for = 'FACT'
AND p.allocation_for_id = l_fact_id
AND p.item_id = -9999;
UPDATE OZF_TIME_ALLOCATIONS t
SET t.TARGET = ROUND((NVL(l_fact_rec.recommend_total_amount, 0) / l_denominator), 0),
t.object_version_number = t.object_version_number + 1,
t.last_update_date = SYSDATE,
t.last_updated_by = FND_GLOBAL.USER_ID,
t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE t.allocation_for = 'PROD'
AND t.allocation_for_id IN ( SELECT p.product_allocation_id
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'FACT'
AND p.allocation_for_id = l_fact_id
AND p.item_id = -9999 );
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.TARGET = (SELECT SUM(ti.TARGET)
FROM OZF_TIME_ALLOCATIONS ti
WHERE ti.ALLOCATION_FOR = 'PROD'
AND ti.ALLOCATION_FOR_ID = p.PRODUCT_ALLOCATION_ID),
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.allocation_for = 'FACT'
AND p.allocation_for_id = l_fact_id
AND p.item_id = -9999;
SELECT SUM(p.TARGET) INTO l_diff_target_1
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'FACT'
AND p.allocation_for_id = l_fact_id;
UPDATE OZF_TIME_ALLOCATIONS t
SET t.TARGET = t.TARGET + l_diff_target,
t.object_version_number = t.object_version_number + 1,
t.last_update_date = SYSDATE,
t.last_updated_by = FND_GLOBAL.USER_ID,
t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
WHERE x.allocation_for = 'PROD'
AND x.allocation_for_id IN ( SELECT p.product_allocation_id
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'FACT'
AND p.allocation_for_id = l_fact_id
AND p.item_id = -9999 )
);
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.TARGET = p.TARGET + l_diff_target,
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.allocation_for = 'FACT'
AND p.allocation_for_id = l_fact_id
AND p.item_id = -9999;
SELECT
owner,
start_period_id,
end_period_id,
start_date_active,
end_date_active,
status_code,
original_budget,
transfered_in_amt,
transfered_out_amt,
node_id, -- (=territory id)
product_spread_time_id period_type_id -- (= minor_scale_id i.e. qtrly or monthly)
FROM
ozf_funds_all_vl
WHERE
fund_id = l_fund_id;
SELECT
j.terr_id territory_id
FROM
jtf_terr_rsc_all j, jtf_terr_rsc_access_all j2
WHERE
j.resource_id = l_resource_id
-- AND j.primary_contact_flag = 'Y' ;
SELECT
inventory_item_id
FROM
ams_act_products
WHERE
act_product_used_by_id = l_fund_id
AND arc_act_product_used_by = 'FUND'
AND level_type_code = 'PRODUCT'
AND NVL(excluded_flag,'N') = 'N';
SELECT
category_id
FROM
ams_act_products
WHERE
act_product_used_by_id = l_fund_id
AND arc_act_product_used_by = 'FUND'
AND level_type_code = 'FAMILY'
AND NVL(excluded_flag,'N') = 'N';
SELECT
inventory_item_id
FROM
ams_act_products
WHERE
act_product_used_by_id = l_fund_id
AND arc_act_product_used_by = 'FUND'
AND level_type_code = 'PRODUCT'
AND excluded_flag ='Y';
SELECT
category_id
FROM
ams_act_products
WHERE
act_product_used_by_id = l_fund_id
AND arc_act_product_used_by = 'FUND'
AND level_type_code = 'FAMILY'
AND excluded_flag ='Y';
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.inventory_item_id = l_product_id
AND bsmv.time_id = l_time_id;
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id = l_time_id
AND bsmv.inventory_item_id IN
( SELECT DISTINCT MIC.INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES MIC,
ENI_PROD_DENORM_HRCHY_V DENORM
WHERE MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID
AND MIC.CATEGORY_ID = DENORM.CHILD_ID
AND DENORM.PARENT_ID = l_category_id
MINUS
SELECT a.inventory_item_id
FROM ams_act_products a
WHERE act_product_used_by_id = l_fund_id
AND arc_act_product_used_by = 'FUND'
AND level_type_code = 'PRODUCT'
AND excluded_flag IN ('Y', 'N')
);
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id = l_time_id
AND NOT EXISTS
(
( SELECT prod.inventory_item_id
FROM ams_act_products prod
WHERE
prod.level_type_code = 'PRODUCT'
AND prod.arc_act_product_used_by = 'FUND'
AND prod.act_product_used_by_id = l_fund_id
AND prod.excluded_flag = 'N'
AND prod.inventory_item_id = bsmv.inventory_item_id
UNION ALL
SELECT MIC.INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES MIC,
ENI_PROD_DENORM_HRCHY_V DENORM,
AMS_ACT_PRODUCTS prod
WHERE
prod.level_type_code = 'FAMILY'
AND prod.arc_act_product_used_by = 'FUND'
AND prod.act_product_used_by_id = l_fund_id
AND prod.excluded_flag = 'N'
AND prod.category_id = DENORM.PARENT_ID
AND MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID
AND MIC.CATEGORY_ID = DENORM.CHILD_ID
AND MIC.INVENTORY_ITEM_ID = bsmv.inventory_item_id
)
MINUS
SELECT prod.inventory_item_id
FROM ams_act_products prod
where
prod.level_type_code = 'PRODUCT'
AND prod.arc_act_product_used_by = 'FUND'
AND prod.act_product_used_by_id = l_fund_id
AND prod.excluded_flag = 'Y'
AND prod.inventory_item_id = bsmv.inventory_item_id
);
p_prod_alloc_rec.selected_flag := 'N';
Ozf_Product_Allocations_Pkg.Insert_Row(
px_product_allocation_id => l_product_allocation_id,
p_allocation_for => p_prod_alloc_rec.allocation_for,
p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
p_fund_id => p_prod_alloc_rec.fund_id,
p_item_type => p_prod_alloc_rec.item_type,
p_item_id => p_prod_alloc_rec.item_id,
p_selected_flag => p_prod_alloc_rec.selected_flag,
p_target => NVL(p_prod_alloc_rec.target, 0),
p_lysp_sales => NVL(p_prod_alloc_rec.lysp_sales, 0),
p_parent_product_allocation_id => p_prod_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_prod_alloc_rec.attribute_category,
p_attribute1 => p_prod_alloc_rec.attribute1,
p_attribute2 => p_prod_alloc_rec.attribute2,
p_attribute3 => p_prod_alloc_rec.attribute3,
p_attribute4 => p_prod_alloc_rec.attribute4,
p_attribute5 => p_prod_alloc_rec.attribute5,
p_attribute6 => p_prod_alloc_rec.attribute6,
p_attribute7 => p_prod_alloc_rec.attribute7,
p_attribute8 => p_prod_alloc_rec.attribute8,
p_attribute9 => p_prod_alloc_rec.attribute9,
p_attribute10 => p_prod_alloc_rec.attribute10,
p_attribute11 => p_prod_alloc_rec.attribute11,
p_attribute12 => p_prod_alloc_rec.attribute12,
p_attribute13 => p_prod_alloc_rec.attribute13,
p_attribute14 => p_prod_alloc_rec.attribute14,
p_attribute15 => p_prod_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
);
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.lysp_sales = NVL(l_total_lysp_sales,0),
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.product_allocation_id = l_product_allocation_id;
p_prod_alloc_rec.selected_flag := 'N';
Ozf_Product_Allocations_Pkg.Insert_Row(
px_product_allocation_id => l_product_allocation_id,
p_allocation_for => p_prod_alloc_rec.allocation_for,
p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
p_fund_id => p_prod_alloc_rec.fund_id,
p_item_type => p_prod_alloc_rec.item_type,
p_item_id => p_prod_alloc_rec.item_id,
p_selected_flag => p_prod_alloc_rec.selected_flag,
p_target => NVL(p_prod_alloc_rec.target,0),
p_lysp_sales => NVL(p_prod_alloc_rec.lysp_sales, 0),
p_parent_product_allocation_id => p_prod_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_prod_alloc_rec.attribute_category,
p_attribute1 => p_prod_alloc_rec.attribute1,
p_attribute2 => p_prod_alloc_rec.attribute2,
p_attribute3 => p_prod_alloc_rec.attribute3,
p_attribute4 => p_prod_alloc_rec.attribute4,
p_attribute5 => p_prod_alloc_rec.attribute5,
p_attribute6 => p_prod_alloc_rec.attribute6,
p_attribute7 => p_prod_alloc_rec.attribute7,
p_attribute8 => p_prod_alloc_rec.attribute8,
p_attribute9 => p_prod_alloc_rec.attribute9,
p_attribute10 => p_prod_alloc_rec.attribute10,
p_attribute11 => p_prod_alloc_rec.attribute11,
p_attribute12 => p_prod_alloc_rec.attribute12,
p_attribute13 => p_prod_alloc_rec.attribute13,
p_attribute14 => p_prod_alloc_rec.attribute14,
p_attribute15 => p_prod_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
);
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.lysp_sales = NVL(l_total_lysp_sales, 0),
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.product_allocation_id = l_product_allocation_id;
p_prod_alloc_rec.selected_flag := 'N';
Ozf_Product_Allocations_Pkg.Insert_Row(
px_product_allocation_id => l_product_allocation_id,
p_allocation_for => p_prod_alloc_rec.allocation_for,
p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
p_fund_id => p_prod_alloc_rec.fund_id,
p_item_type => p_prod_alloc_rec.item_type,
p_item_id => p_prod_alloc_rec.item_id,
p_selected_flag => p_prod_alloc_rec.selected_flag,
p_target => NVL(p_prod_alloc_rec.target, 0),
p_lysp_sales => NVL(p_prod_alloc_rec.lysp_sales, 0),
p_parent_product_allocation_id => p_prod_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_prod_alloc_rec.attribute_category,
p_attribute1 => p_prod_alloc_rec.attribute1,
p_attribute2 => p_prod_alloc_rec.attribute2,
p_attribute3 => p_prod_alloc_rec.attribute3,
p_attribute4 => p_prod_alloc_rec.attribute4,
p_attribute5 => p_prod_alloc_rec.attribute5,
p_attribute6 => p_prod_alloc_rec.attribute6,
p_attribute7 => p_prod_alloc_rec.attribute7,
p_attribute8 => p_prod_alloc_rec.attribute8,
p_attribute9 => p_prod_alloc_rec.attribute9,
p_attribute10 => p_prod_alloc_rec.attribute10,
p_attribute11 => p_prod_alloc_rec.attribute11,
p_attribute12 => p_prod_alloc_rec.attribute12,
p_attribute13 => p_prod_alloc_rec.attribute13,
p_attribute14 => p_prod_alloc_rec.attribute14,
p_attribute15 => p_prod_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
);
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.lysp_sales = NVL(l_total_lysp_sales, 0),
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.product_allocation_id = l_product_allocation_id;
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.TARGET = ROUND( (NVL(p.LYSP_SALES, 0) * l_multiplying_factor), 0),
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.allocation_for = 'FUND'
AND p.allocation_for_id = l_fund_id;
UPDATE OZF_TIME_ALLOCATIONS t
SET t.TARGET = ROUND((NVL(t.LYSP_SALES, 0) * l_multiplying_factor), 0),
t.object_version_number = t.object_version_number + 1,
t.last_update_date = SYSDATE,
t.last_updated_by = FND_GLOBAL.USER_ID,
t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE t.allocation_for = 'PROD'
AND t.allocation_for_id IN ( SELECT p.product_allocation_id
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'FUND'
AND p.allocation_for_id = l_fund_id );
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.TARGET = (SELECT SUM(ti.TARGET)
FROM OZF_TIME_ALLOCATIONS ti
WHERE ti.ALLOCATION_FOR = 'PROD'
AND ti.ALLOCATION_FOR_ID = p.PRODUCT_ALLOCATION_ID),
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.allocation_for = 'FUND'
AND p.allocation_for_id = l_fund_id;
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.TARGET = ROUND( l_total_root_quota, 0),
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.allocation_for = 'FUND'
AND p.allocation_for_id = l_fund_id
AND p.item_id = -9999;
UPDATE OZF_TIME_ALLOCATIONS t
SET t.TARGET = ROUND((l_total_root_quota / l_denominator), 0),
t.object_version_number = t.object_version_number + 1,
t.last_update_date = SYSDATE,
t.last_updated_by = FND_GLOBAL.USER_ID,
t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE t.allocation_for = 'PROD'
AND t.allocation_for_id IN ( SELECT p.product_allocation_id
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'FUND'
AND p.allocation_for_id = l_fund_id
AND p.item_id = -9999 );
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.TARGET = (SELECT SUM(ti.TARGET)
FROM OZF_TIME_ALLOCATIONS ti
WHERE ti.ALLOCATION_FOR = 'PROD'
AND ti.ALLOCATION_FOR_ID = p.PRODUCT_ALLOCATION_ID),
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.allocation_for = 'FUND'
AND p.allocation_for_id = l_fund_id
AND p.item_id = -9999;
SELECT p.TARGET INTO l_diff_target_1
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'FUND'
AND p.allocation_for_id = l_fund_id
AND p.item_id = -9999;
SELECT SUM(t.TARGET) INTO l_diff_target_2
FROM OZF_TIME_ALLOCATIONS t
WHERE t.allocation_for = 'PROD'
AND t.allocation_for_id IN ( SELECT p.product_allocation_id
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'FUND'
AND p.allocation_for_id = l_fund_id
AND p.item_id = -9999 );
SELECT (a.target-b.target) INTO l_diff_target
FROM
(
SELECT p.TARGET target
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'FUND'
AND p.allocation_for_id = l_fund_id
AND p.item_id = -9999
) a,
(
SELECT SUM(t.TARGET) target
FROM OZF_TIME_ALLOCATIONS t
WHERE t.allocation_for = 'PROD'
AND t.allocation_for_id IN ( SELECT p.product_allocation_id
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'FUND'
AND p.allocation_for_id = l_fund_id
AND p.item_id = -9999 )
) b;
SELECT
(
SELECT p.TARGET
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'FUND'
AND p.allocation_for_id = l_fund_id
AND p.item_id = -9999
)
-
(
SELECT SUM(t.TARGET)
FROM OZF_TIME_ALLOCATIONS t
WHERE t.allocation_for = 'PROD'
AND t.allocation_for_id IN ( SELECT p.product_allocation_id
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'FUND'
AND p.allocation_for_id = l_fund_id
AND p.item_id = -9999 )
) diff_target INTO l_diff_target
FROM DUAL;
SELECT SUM(p.TARGET) INTO l_diff_target_1
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'FUND'
AND p.allocation_for_id = l_fund_id;
UPDATE OZF_TIME_ALLOCATIONS t
SET t.TARGET = t.TARGET + l_diff_target,
t.object_version_number = t.object_version_number + 1,
t.last_update_date = SYSDATE,
t.last_updated_by = FND_GLOBAL.USER_ID,
t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
WHERE x.allocation_for = 'PROD'
AND x.allocation_for_id IN ( SELECT p.product_allocation_id
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'FUND'
AND p.allocation_for_id = l_fund_id
AND p.item_id = -9999 )
);
SELECT
activity_metric_fact_id,
act_metric_used_by_id,
arc_act_metric_used_by,
activity_metric_id,
hierarchy_id,
hierarchy_type,
node_id,
previous_fact_id,
recommend_total_amount,
status_code
FROM
OZF_ACT_METRIC_FACTS_ALL
WHERE
activity_metric_fact_id = l_fact_id;
UPDATE ozf_product_allocations p
SET p.fund_id = l_fund_id,
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.allocation_for = 'FACT'
AND p.allocation_for_id = l_fact_id;
PROCEDURE delete_fact_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_error_number OUT NOCOPY NUMBER,
x_error_message OUT NOCOPY VARCHAR2,
p_fact_id IN NUMBER
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'delete_fact_product_spread';
SELECT DISTINCT
p.product_allocation_id
FROM
ozf_product_allocations p
WHERE
p.allocation_for = 'FACT'
AND p.allocation_for_id = l_fact_id;
SAVEPOINT delete_fact_product_spread;
DELETE ozf_time_allocations t
WHERE t.allocation_for_id = fact_product_spread_rec.product_allocation_id
AND t.allocation_for = 'PROD';
DELETE ozf_product_allocations p
WHERE p.allocation_for = 'FACT'
AND p.allocation_for_id = l_fact_id;
ROLLBACK TO delete_fact_product_spread;
ROLLBACK TO delete_fact_product_spread;
ROLLBACK TO delete_fact_product_spread;
END delete_fact_product_spread;
PROCEDURE delete_cascade_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_error_number OUT NOCOPY NUMBER,
x_error_message OUT NOCOPY VARCHAR2,
p_fund_id IN NUMBER
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'delete_cascade_product_spread';
SELECT
owner,
start_period_id,
end_period_id,
start_date_active,
end_date_active,
status_code,
original_budget,
transfered_in_amt,
transfered_out_amt,
node_id, -- (=territory id)
product_spread_time_id period_type_id -- (= minor_scale_id i.e. qtrly or monthly)
FROM
ozf_funds_all_vl
WHERE
fund_id = l_fund_id;
SELECT
activity_metric_id,
arc_act_metric_used_by,
act_metric_used_by_id,
product_spread_time_id period_type_id, -- (eg.. 32 for monthly, 64 for quarterly),
published_flag,
status_code,
start_period_name,
end_period_name,
from_date,
to_date
FROM
OZF_ACT_METRICS_ALL
WHERE
arc_act_metric_used_by = 'FUND'
AND act_metric_used_by_id = l_fund_id;
SELECT
activity_metric_fact_id,
act_metric_used_by_id,
arc_act_metric_used_by,
activity_metric_id,
hierarchy_id,
hierarchy_type,
node_id,
previous_fact_id,
recommend_total_amount,
status_code
FROM
OZF_ACT_METRIC_FACTS_ALL
WHERE
activity_metric_id = l_allocation_id;
SELECT DISTINCT
p.product_allocation_id
FROM
ozf_product_allocations p
WHERE
p.allocation_for = 'FUND'
AND p.allocation_for_id = l_fund_id
AND p.fund_id = l_fund_id;
SAVEPOINT delete_cascade_product_spread;
delete_fact_product_spread
(p_api_version => p_api_version,
x_return_status => x_return_status,
x_error_number => x_error_number,
x_error_message => x_error_message,
p_fact_id => fact_rec.activity_metric_fact_id
);
DELETE ozf_time_allocations t
WHERE t.allocation_for_id = fund_product_spread_rec.product_allocation_id
AND t.allocation_for = 'PROD';
DELETE ozf_product_allocations p
WHERE p.allocation_for = 'FUND'
AND p.allocation_for_id = l_fund_id
AND p.fund_id = l_fund_id;
ROLLBACK TO delete_cascade_product_spread;
ROLLBACK TO delete_cascade_product_spread;
ROLLBACK TO delete_cascade_product_spread;
ROLLBACK TO delete_cascade_product_spread;
END delete_cascade_product_spread;
SELECT
owner,
start_period_id,
end_period_id,
start_date_active,
end_date_active,
status_code,
original_budget,
transfered_in_amt,
transfered_out_amt,
node_id, -- (=territory id)
product_spread_time_id period_type_id -- (= minor_scale_id i.e. qtrly or monthly)
FROM
ozf_funds_all_vl
WHERE
fund_id = l_fund_id;
SELECT
activity_metric_id,
arc_act_metric_used_by,
act_metric_used_by_id,
product_spread_time_id period_type_id, -- (eg.. 32 for monthly, 64 for quarterly),
published_flag,
status_code,
start_period_name,
end_period_name,
from_date,
to_date
FROM
OZF_ACT_METRICS_ALL
WHERE
arc_act_metric_used_by = 'FUND'
AND act_metric_used_by_id = l_fund_id;
SELECT
activity_metric_fact_id,
act_metric_used_by_id,
arc_act_metric_used_by,
activity_metric_id,
hierarchy_id,
hierarchy_type,
node_id, --- this is territory_id of this FACT ******* confirm this *********
previous_fact_id,
recommend_total_amount,
status_code
FROM
OZF_ACT_METRIC_FACTS_ALL
WHERE
activity_metric_id = l_allocation_id;
SELECT product_allocation_id,
allocation_for,
allocation_for_id,
fund_id,
item_id,
item_type,
target,
lysp_sales
FROM OZF_PRODUCT_ALLOCATIONS
WHERE allocation_for = 'FACT'
AND allocation_for_id = l_fact_id
AND item_id = -9999;
SELECT time_allocation_id,
allocation_for,
allocation_for_id,
time_id,
period_type_id,
target,
lysp_sales
FROM OZF_TIME_ALLOCATIONS
WHERE allocation_for = 'PROD'
AND allocation_for_id = l_prod_alloc_id;
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.inventory_item_id = l_product_id
AND bsmv.time_id = l_time_id;
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id = l_time_id
AND bsmv.inventory_item_id IN
( SELECT DISTINCT MIC.INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES MIC,
ENI_PROD_DENORM_HRCHY_V DENORM
WHERE MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID
AND MIC.CATEGORY_ID = DENORM.CHILD_ID
AND DENORM.PARENT_ID = l_category_id
MINUS
SELECT a.inventory_item_id
FROM ams_act_products a
WHERE act_product_used_by_id = l_fund_id
AND arc_act_product_used_by = 'FUND'
AND level_type_code = 'PRODUCT'
AND excluded_flag IN ('Y', 'N')
);
p_prod_alloc_rec.selected_flag := 'N';
Ozf_Product_Allocations_Pkg.Insert_Row(
px_product_allocation_id => l_product_allocation_id,
p_allocation_for => p_prod_alloc_rec.allocation_for,
p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
p_fund_id => p_prod_alloc_rec.fund_id,
p_item_type => p_prod_alloc_rec.item_type,
p_item_id => p_prod_alloc_rec.item_id,
p_selected_flag => p_prod_alloc_rec.selected_flag,
p_target => NVL(p_prod_alloc_rec.target, 0),
p_lysp_sales => NVL(p_prod_alloc_rec.lysp_sales, 0),
p_parent_product_allocation_id => p_prod_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_prod_alloc_rec.attribute_category,
p_attribute1 => p_prod_alloc_rec.attribute1,
p_attribute2 => p_prod_alloc_rec.attribute2,
p_attribute3 => p_prod_alloc_rec.attribute3,
p_attribute4 => p_prod_alloc_rec.attribute4,
p_attribute5 => p_prod_alloc_rec.attribute5,
p_attribute6 => p_prod_alloc_rec.attribute6,
p_attribute7 => p_prod_alloc_rec.attribute7,
p_attribute8 => p_prod_alloc_rec.attribute8,
p_attribute9 => p_prod_alloc_rec.attribute9,
p_attribute10 => p_prod_alloc_rec.attribute10,
p_attribute11 => p_prod_alloc_rec.attribute11,
p_attribute12 => p_prod_alloc_rec.attribute12,
p_attribute13 => p_prod_alloc_rec.attribute13,
p_attribute14 => p_prod_alloc_rec.attribute14,
p_attribute15 => p_prod_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
);
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.lysp_sales = l_total_lysp_sales,
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.product_allocation_id = l_product_allocation_id;
PROCEDURE delete_single_product
(
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_error_number OUT NOCOPY NUMBER,
x_error_message OUT NOCOPY VARCHAR2,
p_fund_id IN NUMBER,
p_item_id IN NUMBER,
p_item_type IN VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'delete_single_product';
SELECT
owner,
start_period_id,
end_period_id,
start_date_active,
end_date_active,
status_code,
original_budget,
transfered_in_amt,
transfered_out_amt,
node_id, -- (=territory id)
product_spread_time_id period_type_id -- (= minor_scale_id i.e. qtrly or monthly)
FROM
ozf_funds_all_vl
WHERE
fund_id = l_fund_id;
SELECT
activity_metric_id,
arc_act_metric_used_by,
act_metric_used_by_id,
product_spread_time_id period_type_id, -- (eg.. 32 for monthly, 64 for quarterly),
published_flag,
status_code,
start_period_name,
end_period_name,
from_date,
to_date
FROM
OZF_ACT_METRICS_ALL
WHERE
arc_act_metric_used_by = 'FUND'
AND act_metric_used_by_id = l_fund_id;
SELECT
activity_metric_fact_id,
act_metric_used_by_id,
arc_act_metric_used_by,
activity_metric_id,
hierarchy_id,
hierarchy_type,
node_id, --- this is territory_id of this FACT ******* confirm this *********
previous_fact_id,
recommend_total_amount,
status_code
FROM
OZF_ACT_METRIC_FACTS_ALL
WHERE
activity_metric_id = l_allocation_id;
SELECT product_allocation_id,
allocation_for,
allocation_for_id,
fund_id,
item_id,
item_type,
target,
lysp_sales
FROM OZF_PRODUCT_ALLOCATIONS
WHERE allocation_for = 'FACT'
AND allocation_for_id = l_fact_id
AND item_id = l_item_id
AND item_type = l_item_type;
SELECT time_allocation_id,
allocation_for,
allocation_for_id,
time_id,
period_type_id,
target,
lysp_sales
FROM OZF_TIME_ALLOCATIONS
WHERE allocation_for = 'PROD'
AND allocation_for_id = l_prod_alloc_id;
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.inventory_item_id = l_product_id
AND bsmv.time_id = l_time_id;
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id = l_time_id
AND bsmv.inventory_item_id IN
( SELECT DISTINCT MIC.INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES MIC,
ENI_PROD_DENORM_HRCHY_V DENORM
WHERE MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID
AND MIC.CATEGORY_ID = DENORM.CHILD_ID
AND DENORM.PARENT_ID = l_category_id
MINUS
SELECT a.inventory_item_id
FROM ams_act_products a
WHERE act_product_used_by_id = l_fund_id
AND arc_act_product_used_by = 'FUND'
AND level_type_code = 'PRODUCT'
AND excluded_flag IN ('Y', 'N')
);
SAVEPOINT delete_single_product;
UPDATE ozf_time_allocations t -- Update Others Quota for Jul03, Aug03, Sep03 etc
SET t.target = t.target + NVL(time_alloc_rec.target, 0),
t.object_version_number = t.object_version_number + 1,
t.last_update_date = SYSDATE,
t.last_updated_by = FND_GLOBAL.USER_ID,
t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE
t.time_id = time_alloc_rec.time_id
AND t.allocation_for_id = l_others_prod_alloc_rec.product_allocation_id
AND t.allocation_for = 'PROD';
UPDATE ozf_product_allocations p -- Update Others Quota for Q3-03 etc
SET p.target = p.target + NVL(l_prod_alloc_rec.target, 0),
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.product_allocation_id = l_others_prod_alloc_rec.product_allocation_id;
DELETE ozf_time_allocations t
WHERE t.allocation_for_id = l_prod_alloc_rec.product_allocation_id
AND t.allocation_for = 'PROD';
DELETE ozf_product_allocations p
WHERE p.product_allocation_id = l_prod_alloc_rec.product_allocation_id;
ROLLBACK TO delete_single_product;
ROLLBACK TO delete_single_product;
ROLLBACK TO delete_single_product;
ROLLBACK TO delete_single_product;
END delete_single_product;
PROCEDURE delete_target_allocation
(
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_error_number OUT NOCOPY NUMBER,
x_error_message OUT NOCOPY VARCHAR2,
p_fund_id IN NUMBER
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'delete_target_allocation';
SELECT DISTINCT
p.account_allocation_id
FROM
ozf_account_allocations p
WHERE
p.allocation_for = 'FUND'
AND p.allocation_for_id = l_fund_id;
SELECT DISTINCT
p.product_allocation_id
FROM
ozf_product_allocations p
WHERE
p.allocation_for = 'CUST'
AND p.allocation_for_id = l_acct_allocation_id;
SAVEPOINT delete_target_allocation;
DELETE ozf_time_allocations t
WHERE t.allocation_for_id = account_rec.account_allocation_id
AND t.allocation_for = 'CUST';
DELETE ozf_time_allocations t
WHERE t.allocation_for_id = product_rec.product_allocation_id
AND t.allocation_for = 'PROD';
DELETE ozf_product_allocations p
WHERE p.allocation_for = 'CUST'
AND p.allocation_for_id = account_rec.account_allocation_id;
DELETE ozf_account_allocations p
WHERE p.allocation_for = 'FUND'
AND p.allocation_for_id = l_fund_id;
ROLLBACK TO delete_target_allocation;
ROLLBACK TO delete_target_allocation;
ROLLBACK TO delete_target_allocation;
END delete_target_allocation;
SELECT
owner,
start_period_id,
end_period_id,
start_date_active,
end_date_active,
status_code,
original_budget,
transfered_in_amt,
transfered_out_amt,
node_id, -- (=territory id)
product_spread_time_id period_type_id -- (= minor_scale_id i.e. qtrly or monthly)
FROM
ozf_funds_all_vl
WHERE
fund_id = l_fund_id;
SELECT
j.terr_id territory_id
FROM
jtf_terr_rsc_all j, jtf_terr_rsc_access_all j2
WHERE
j.resource_id = l_resource_id
-- AND j.primary_contact_flag = 'Y' ;
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id IN (l_in_clause);
' SELECT'||
' SUM(bsmv.sales) sales'||
' FROM'||
' ozf_order_sales_v bsmv,'||
' ams_party_market_segments a'||
' WHERE'||
' a.market_qualifier_reference = :l_territory_id '||
' AND a.market_qualifier_type=''TERRITORY'' '||
' AND bsmv.ship_to_site_use_id = a.site_use_id'||
' AND bsmv.time_id IN (';
SELECT
a.cust_account_id cust_account_id,
a.site_use_id site_use_id,
a.site_use_code site_use_code,
OZF_LOCATION_PVT.get_location_id(a.site_use_id) location_id,
NVL(a.bill_to_site_use_id, -9996) bill_to_site_use_id,
OZF_LOCATION_PVT.get_location_id(a.bill_to_site_use_id) bill_to_location_id,
a.party_id parent_party_id,
NVL(a.rollup_party_id, a.party_id) rollup_party_id
FROM
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_code = 'SHIP_TO'
AND a.party_id IS NOT NULL
AND a.site_use_id IS NOT NULL;
SELECT
a.cust_account_id cust_account_id,
a.site_use_id site_use_id,
a.site_use_code site_use_code,
OZF_LOCATION_PVT.get_location_id(a.site_use_id) location_id,
a.bill_to_site_use_id bill_to_site_use_id,
OZF_LOCATION_PVT.get_location_id(a.bill_to_site_use_id) bill_to_location_id,
a.party_id parent_party_id,
a.rollup_party_id rollup_party_id
FROM
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY';
SELECT
SUM(bsmv.sales) account_sales
FROM
ozf_order_sales_v bsmv
WHERE
bsmv.ship_to_site_use_id = l_site_use_id
AND bsmv.time_id IN (l_in_clause);
' SELECT '||
' SUM(bsmv.sales) account_sales'||
' FROM '||
' ozf_order_sales_v bsmv'||
' WHERE '||
' bsmv.ship_to_site_use_id = :l_site_use_id'||
' AND bsmv.time_id IN (' ;
SELECT
SUM(bsmv.sales) account_sales
FROM
ozf_order_sales_v bsmv
WHERE
bsmv.ship_to_site_use_id = l_site_use_id
AND bsmv.time_id = l_time_id;
SELECT SUM(t.target)
FROM
ozf_time_allocations t,
ozf_product_allocations p
WHERE
p.fund_id = l_fund_id
AND t.allocation_for_id = p.product_allocation_id
AND t.allocation_for = 'PROD'
AND t.time_id IN (l_in_clause);
' SELECT SUM(t.target) '||
' FROM '||
' ozf_time_allocations t,'||
' ozf_product_allocations p'||
' WHERE'||
' p.fund_id = :l_fund_id'||
' AND t.allocation_for_id = p.product_allocation_id'||
' AND t.allocation_for = ''PROD'' '||
' AND t.time_id IN (';
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_id = l_site_use_id
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.inventory_item_id = l_product_id
AND bsmv.time_id = l_time_id;
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_id = l_site_use_id
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id = l_time_id
AND bsmv.inventory_item_id IN
( SELECT DISTINCT MIC.INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES MIC,
ENI_PROD_DENORM_HRCHY_V DENORM
WHERE MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID
AND MIC.CATEGORY_ID = DENORM.CHILD_ID
AND DENORM.PARENT_ID = l_category_id
MINUS
SELECT p.item_id
FROM ozf_product_allocations p
WHERE p.fund_id = l_fund_id
AND p.item_type = 'PRICING_ATTRIBUTE1'
);
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_id = l_site_use_id
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id = l_time_id
AND NOT EXISTS
(
( SELECT p.item_id
FROM ozf_product_allocations p
WHERE
p.fund_id = l_fund_id
AND p.item_type = 'PRICING_ATTRIBUTE1'
AND p.item_id = bsmv.inventory_item_id
UNION ALL
SELECT MIC.INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES MIC,
ENI_PROD_DENORM_HRCHY_V DENORM,
OZF_PRODUCT_ALLOCATIONS p
WHERE p.FUND_ID = l_fund_id
AND p.ITEM_TYPE = 'PRICING_ATTRIBUTE2'
AND p.ITEM_ID = DENORM.PARENT_ID
AND MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID
AND MIC.CATEGORY_ID = DENORM.CHILD_ID
AND MIC.INVENTORY_ITEM_ID = bsmv.inventory_item_id
)
MINUS
SELECT prod.inventory_item_id
FROM ams_act_products prod
where
prod.level_type_code = 'PRODUCT'
AND prod.arc_act_product_used_by = 'FUND'
AND prod.act_product_used_by_id = l_fund_id
AND prod.excluded_flag = 'Y'
AND prod.inventory_item_id = bsmv.inventory_item_id
);
SELECT
p.product_allocation_id,
p.item_id,
p.item_type,
p.target
FROM
ozf_product_allocations p
WHERE
p.fund_id = l_fund_id;
SELECT t.target
FROM
ozf_time_allocations t
WHERE
t.allocation_for_id = l_product_allocation_id
AND t.allocation_for = 'PROD'
AND t.time_id = l_time_id;
p_acct_alloc_rec.selected_flag := 'Y';
Ozf_Account_Allocations_Pkg.Insert_Row(
px_Account_allocation_id => l_account_allocation_id,
p_allocation_for => p_acct_alloc_rec.allocation_for,
p_allocation_for_id => p_acct_alloc_rec.allocation_for_id,
p_cust_account_id => p_acct_alloc_rec.cust_account_id,
p_site_use_id => p_acct_alloc_rec.site_use_id,
p_site_use_code => p_acct_alloc_rec.site_use_code,
p_location_id => p_acct_alloc_rec.location_id,
p_bill_to_site_use_id => p_acct_alloc_rec.bill_to_site_use_id,
p_bill_to_location_id => p_acct_alloc_rec.bill_to_location_id,
p_parent_party_id => p_acct_alloc_rec.parent_party_id,
p_rollup_party_id => p_acct_alloc_rec.rollup_party_id,
p_selected_flag => p_acct_alloc_rec.selected_flag,
p_target => p_acct_alloc_rec.target,
p_lysp_sales => p_acct_alloc_rec.lysp_sales,
p_parent_Account_allocation_id => p_acct_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_acct_alloc_rec.attribute_category,
p_attribute1 => p_acct_alloc_rec.attribute1,
p_attribute2 => p_acct_alloc_rec.attribute2,
p_attribute3 => p_acct_alloc_rec.attribute3,
p_attribute4 => p_acct_alloc_rec.attribute4,
p_attribute5 => p_acct_alloc_rec.attribute5,
p_attribute6 => p_acct_alloc_rec.attribute6,
p_attribute7 => p_acct_alloc_rec.attribute7,
p_attribute8 => p_acct_alloc_rec.attribute8,
p_attribute9 => p_acct_alloc_rec.attribute9,
p_attribute10 => p_acct_alloc_rec.attribute10,
p_attribute11 => p_acct_alloc_rec.attribute11,
p_attribute12 => p_acct_alloc_rec.attribute12,
p_attribute13 => p_acct_alloc_rec.attribute13,
p_attribute14 => p_acct_alloc_rec.attribute14,
p_attribute15 => p_acct_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 => p_time_alloc_rec.target,
p_lysp_sales => p_time_alloc_rec.lysp_sales,
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
);
p_prod_alloc_rec.selected_flag := 'N';
Ozf_Product_Allocations_Pkg.Insert_Row(
px_product_allocation_id => l_product_allocation_id,
p_allocation_for => p_prod_alloc_rec.allocation_for,
p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
p_fund_id => p_prod_alloc_rec.fund_id,
p_item_type => p_prod_alloc_rec.item_type,
p_item_id => p_prod_alloc_rec.item_id,
p_selected_flag => p_prod_alloc_rec.selected_flag,
p_target => p_prod_alloc_rec.target,
p_lysp_sales => p_prod_alloc_rec.lysp_sales,
p_parent_product_allocation_id => p_prod_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_prod_alloc_rec.attribute_category,
p_attribute1 => p_prod_alloc_rec.attribute1,
p_attribute2 => p_prod_alloc_rec.attribute2,
p_attribute3 => p_prod_alloc_rec.attribute3,
p_attribute4 => p_prod_alloc_rec.attribute4,
p_attribute5 => p_prod_alloc_rec.attribute5,
p_attribute6 => p_prod_alloc_rec.attribute6,
p_attribute7 => p_prod_alloc_rec.attribute7,
p_attribute8 => p_prod_alloc_rec.attribute8,
p_attribute9 => p_prod_alloc_rec.attribute9,
p_attribute10 => p_prod_alloc_rec.attribute10,
p_attribute11 => p_prod_alloc_rec.attribute11,
p_attribute12 => p_prod_alloc_rec.attribute12,
p_attribute13 => p_prod_alloc_rec.attribute13,
p_attribute14 => p_prod_alloc_rec.attribute14,
p_attribute15 => p_prod_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 => p_time_alloc_rec.target,
p_lysp_sales => p_time_alloc_rec.lysp_sales,
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
);
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.lysp_sales = l_total_product_sales,
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.product_allocation_id = l_product_allocation_id;
p_acct_alloc_rec.selected_flag := 'N';
Ozf_Account_Allocations_Pkg.Insert_Row(
px_Account_allocation_id => l_account_allocation_id,
p_allocation_for => p_acct_alloc_rec.allocation_for,
p_allocation_for_id => p_acct_alloc_rec.allocation_for_id,
p_cust_account_id => p_acct_alloc_rec.cust_account_id,
p_site_use_id => p_acct_alloc_rec.site_use_id,
p_site_use_code => p_acct_alloc_rec.site_use_code,
p_location_id => p_acct_alloc_rec.location_id,
p_bill_to_site_use_id => p_acct_alloc_rec.bill_to_site_use_id,
p_bill_to_location_id => p_acct_alloc_rec.bill_to_location_id,
p_parent_party_id => p_acct_alloc_rec.parent_party_id,
p_rollup_party_id => p_acct_alloc_rec.rollup_party_id,
p_selected_flag => p_acct_alloc_rec.selected_flag,
p_target => p_acct_alloc_rec.target,
p_lysp_sales => p_acct_alloc_rec.lysp_sales,
p_parent_Account_allocation_id => p_acct_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_acct_alloc_rec.attribute_category,
p_attribute1 => p_acct_alloc_rec.attribute1,
p_attribute2 => p_acct_alloc_rec.attribute2,
p_attribute3 => p_acct_alloc_rec.attribute3,
p_attribute4 => p_acct_alloc_rec.attribute4,
p_attribute5 => p_acct_alloc_rec.attribute5,
p_attribute6 => p_acct_alloc_rec.attribute6,
p_attribute7 => p_acct_alloc_rec.attribute7,
p_attribute8 => p_acct_alloc_rec.attribute8,
p_attribute9 => p_acct_alloc_rec.attribute9,
p_attribute10 => p_acct_alloc_rec.attribute10,
p_attribute11 => p_acct_alloc_rec.attribute11,
p_attribute12 => p_acct_alloc_rec.attribute12,
p_attribute13 => p_acct_alloc_rec.attribute13,
p_attribute14 => p_acct_alloc_rec.attribute14,
p_attribute15 => p_acct_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 => p_time_alloc_rec.target,
p_lysp_sales => p_time_alloc_rec.lysp_sales,
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
);
SELECT a.TARGET INTO l_diff_target_1
FROM OZF_ACCOUNT_ALLOCATIONS a
WHERE a.allocation_for = 'FUND'
AND a.allocation_for_id = l_fund_id
AND a.parent_party_id = -9999;
SELECT SUM(t.TARGET) INTO l_diff_target_2
FROM OZF_TIME_ALLOCATIONS t
WHERE t.allocation_for = 'CUST'
AND t.allocation_for_id IN ( SELECT a.account_allocation_id
FROM OZF_ACCOUNT_ALLOCATIONS a
WHERE a.allocation_for = 'FUND'
AND a.allocation_for_id = l_fund_id
AND a.parent_party_id = -9999 );
UPDATE OZF_TIME_ALLOCATIONS t
SET t.TARGET = t.TARGET + l_diff_target,
t.object_version_number = t.object_version_number + 1,
t.last_update_date = SYSDATE,
t.last_updated_by = FND_GLOBAL.USER_ID,
t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
WHERE x.allocation_for = 'CUST'
AND x.allocation_for_id IN ( SELECT a.account_allocation_id
FROM OZF_ACCOUNT_ALLOCATIONS a
WHERE a.allocation_for = 'FUND'
AND a.allocation_for_id = l_fund_id
AND a.parent_party_id = -9999 )
);
SELECT
owner,
start_period_id,
end_period_id,
start_date_active,
end_date_active,
status_code,
original_budget,
transfered_in_amt,
transfered_out_amt,
node_id, -- (=territory id)
product_spread_time_id period_type_id -- (= minor_scale_id i.e. qtrly or monthly)
FROM
ozf_funds_all_vl
WHERE
fund_id = l_fund_id;
SELECT
j.terr_id territory_id
FROM
jtf_terr_rsc_all j, jtf_terr_rsc_access_all j2
WHERE
j.resource_id = l_resource_id
-- AND j.primary_contact_flag = 'Y' ;
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id IN (l_in_clause);
' SELECT'||
' SUM(bsmv.sales) sales'||
' FROM'||
' ozf_order_sales_v bsmv,'||
' ams_party_market_segments a'||
' WHERE'||
' a.market_qualifier_reference = :l_territory_id '||
' AND a.market_qualifier_type=''TERRITORY'' '||
' AND bsmv.ship_to_site_use_id = a.site_use_id'||
' AND bsmv.time_id IN (';
SELECT
a.cust_account_id cust_account_id,
a.site_use_id site_use_id,
a.site_use_code site_use_code,
OZF_LOCATION_PVT.get_location_id(a.site_use_id) location_id,
NVL(a.bill_to_site_use_id, -9996) bill_to_site_use_id,
OZF_LOCATION_PVT.get_location_id(a.bill_to_site_use_id) bill_to_location_id,
a.party_id parent_party_id,
NVL(a.rollup_party_id, a.party_id) rollup_party_id
FROM
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_code = 'SHIP_TO'
AND a.party_id IS NOT NULL
AND a.site_use_id IS NOT NULL;
SELECT
a.cust_account_id cust_account_id,
a.site_use_id site_use_id,
a.site_use_code site_use_code,
OZF_LOCATION_PVT.get_location_id(a.site_use_id) location_id,
a.bill_to_site_use_id bill_to_site_use_id,
OZF_LOCATION_PVT.get_location_id(a.bill_to_site_use_id) bill_to_location_id,
a.party_id parent_party_id,
a.rollup_party_id rollup_party_id
FROM
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY';
SELECT
SUM(bsmv.sales) account_sales
FROM
ozf_order_sales_v bsmv
WHERE
bsmv.ship_to_site_use_id = l_site_use_id
AND bsmv.time_id IN (l_in_clause);
' SELECT '||
' SUM(bsmv.sales) account_sales'||
' FROM '||
' ozf_order_sales_v bsmv'||
' WHERE '||
' bsmv.ship_to_site_use_id = :l_site_use_id'||
' AND bsmv.time_id IN (' ;
SELECT
SUM(bsmv.sales) account_sales
FROM
ozf_order_sales_v bsmv
WHERE
bsmv.ship_to_site_use_id = l_site_use_id
AND bsmv.time_id = l_time_id;
SELECT SUM(t.target)
FROM
ozf_time_allocations t,
ozf_product_allocations p
WHERE
p.fund_id = l_fund_id
AND t.allocation_for_id = p.product_allocation_id
AND t.allocation_for = 'PROD'
AND t.time_id IN (l_in_clause);
' SELECT SUM(t.target) '||
' FROM '||
' ozf_time_allocations t,'||
' ozf_product_allocations p'||
' WHERE'||
' p.fund_id = :l_fund_id'||
' AND t.allocation_for_id = p.product_allocation_id'||
' AND t.allocation_for = ''PROD'' '||
' AND t.time_id IN (';
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_id = l_site_use_id
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.inventory_item_id = l_product_id
AND bsmv.time_id = l_time_id;
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_id = l_site_use_id
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id = l_time_id
AND bsmv.inventory_item_id IN
( SELECT DISTINCT MIC.INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES MIC,
ENI_PROD_DENORM_HRCHY_V DENORM
WHERE MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID
AND MIC.CATEGORY_ID = DENORM.CHILD_ID
AND DENORM.PARENT_ID = l_category_id
MINUS
SELECT p.item_id
FROM ozf_product_allocations p
WHERE p.fund_id = l_fund_id
AND p.item_type = 'PRICING_ATTRIBUTE1'
);
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_id = l_site_use_id
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id = l_time_id
AND NOT EXISTS
(
( SELECT p.item_id
FROM ozf_product_allocations p
WHERE
p.fund_id = l_fund_id
AND p.item_type = 'PRICING_ATTRIBUTE1'
AND p.item_id = bsmv.inventory_item_id
UNION ALL
SELECT MIC.INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES MIC,
ENI_PROD_DENORM_HRCHY_V DENORM,
OZF_PRODUCT_ALLOCATIONS p
WHERE p.FUND_ID = l_fund_id
AND p.ITEM_TYPE = 'PRICING_ATTRIBUTE2'
AND p.ITEM_ID = DENORM.PARENT_ID
AND MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID
AND MIC.CATEGORY_ID = DENORM.CHILD_ID
AND MIC.INVENTORY_ITEM_ID = bsmv.inventory_item_id
)
MINUS
SELECT prod.inventory_item_id
FROM ams_act_products prod
where
prod.level_type_code = 'PRODUCT'
AND prod.arc_act_product_used_by = 'FUND'
AND prod.act_product_used_by_id = l_fund_id
AND prod.excluded_flag = 'Y'
AND prod.inventory_item_id = bsmv.inventory_item_id
);
SELECT
p.product_allocation_id,
p.item_id,
p.item_type,
p.target
FROM
ozf_product_allocations p
WHERE
p.fund_id = l_fund_id;
SELECT t.target
FROM
ozf_time_allocations t
WHERE
t.allocation_for_id = l_product_allocation_id
AND t.allocation_for = 'PROD'
AND t.time_id = l_time_id;
p_acct_alloc_rec.selected_flag := 'Y';
Ozf_Account_Allocations_Pkg.Insert_Row(
px_Account_allocation_id => l_account_allocation_id,
p_allocation_for => p_acct_alloc_rec.allocation_for,
p_allocation_for_id => p_acct_alloc_rec.allocation_for_id,
p_cust_account_id => p_acct_alloc_rec.cust_account_id,
p_site_use_id => p_acct_alloc_rec.site_use_id,
p_site_use_code => p_acct_alloc_rec.site_use_code,
p_location_id => p_acct_alloc_rec.location_id,
p_bill_to_site_use_id => p_acct_alloc_rec.bill_to_site_use_id,
p_bill_to_location_id => p_acct_alloc_rec.bill_to_location_id,
p_parent_party_id => p_acct_alloc_rec.parent_party_id,
p_rollup_party_id => p_acct_alloc_rec.rollup_party_id,
p_selected_flag => p_acct_alloc_rec.selected_flag,
p_target => p_acct_alloc_rec.target,
p_lysp_sales => p_acct_alloc_rec.lysp_sales,
p_parent_Account_allocation_id => p_acct_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_acct_alloc_rec.attribute_category,
p_attribute1 => p_acct_alloc_rec.attribute1,
p_attribute2 => p_acct_alloc_rec.attribute2,
p_attribute3 => p_acct_alloc_rec.attribute3,
p_attribute4 => p_acct_alloc_rec.attribute4,
p_attribute5 => p_acct_alloc_rec.attribute5,
p_attribute6 => p_acct_alloc_rec.attribute6,
p_attribute7 => p_acct_alloc_rec.attribute7,
p_attribute8 => p_acct_alloc_rec.attribute8,
p_attribute9 => p_acct_alloc_rec.attribute9,
p_attribute10 => p_acct_alloc_rec.attribute10,
p_attribute11 => p_acct_alloc_rec.attribute11,
p_attribute12 => p_acct_alloc_rec.attribute12,
p_attribute13 => p_acct_alloc_rec.attribute13,
p_attribute14 => p_acct_alloc_rec.attribute14,
p_attribute15 => p_acct_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 => p_time_alloc_rec.target,
p_lysp_sales => p_time_alloc_rec.lysp_sales,
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
);
SELECT SUM(ti.TARGET) into l_total_account_target
FROM OZF_TIME_ALLOCATIONS ti
WHERE ti.ALLOCATION_FOR = 'CUST'
AND ti.ALLOCATION_FOR_ID = l_account_allocation_id;
UPDATE OZF_ACCOUNT_ALLOCATIONS a
SET a.TARGET = l_total_account_target,
a.object_version_number = a.object_version_number + 1,
a.last_update_date = SYSDATE,
a.last_updated_by = FND_GLOBAL.USER_ID,
a.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE a.account_allocation_id = l_account_allocation_id;
p_prod_alloc_rec.selected_flag := 'N';
Ozf_Product_Allocations_Pkg.Insert_Row(
px_product_allocation_id => l_product_allocation_id,
p_allocation_for => p_prod_alloc_rec.allocation_for,
p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
p_fund_id => p_prod_alloc_rec.fund_id,
p_item_type => p_prod_alloc_rec.item_type,
p_item_id => p_prod_alloc_rec.item_id,
p_selected_flag => p_prod_alloc_rec.selected_flag,
p_target => p_prod_alloc_rec.target,
p_lysp_sales => p_prod_alloc_rec.lysp_sales,
p_parent_product_allocation_id => p_prod_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_prod_alloc_rec.attribute_category,
p_attribute1 => p_prod_alloc_rec.attribute1,
p_attribute2 => p_prod_alloc_rec.attribute2,
p_attribute3 => p_prod_alloc_rec.attribute3,
p_attribute4 => p_prod_alloc_rec.attribute4,
p_attribute5 => p_prod_alloc_rec.attribute5,
p_attribute6 => p_prod_alloc_rec.attribute6,
p_attribute7 => p_prod_alloc_rec.attribute7,
p_attribute8 => p_prod_alloc_rec.attribute8,
p_attribute9 => p_prod_alloc_rec.attribute9,
p_attribute10 => p_prod_alloc_rec.attribute10,
p_attribute11 => p_prod_alloc_rec.attribute11,
p_attribute12 => p_prod_alloc_rec.attribute12,
p_attribute13 => p_prod_alloc_rec.attribute13,
p_attribute14 => p_prod_alloc_rec.attribute14,
p_attribute15 => p_prod_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 => p_time_alloc_rec.target,
p_lysp_sales => p_time_alloc_rec.lysp_sales,
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
);
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.lysp_sales = l_total_product_sales,
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.product_allocation_id = l_product_allocation_id;
UPDATE OZF_TIME_ALLOCATIONS t
SET t.TARGET = ROUND((NVL(t.LYSP_SALES, 0) * l_prod_mltply_factor), 0),
t.object_version_number = t.object_version_number + 1,
t.last_update_date = SYSDATE,
t.last_updated_by = FND_GLOBAL.USER_ID,
t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE t.allocation_for = 'PROD'
AND t.allocation_for_id IN ( SELECT p.product_allocation_id
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'CUST'
AND p.allocation_for_id = l_account_allocation_id);
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.TARGET = (SELECT SUM(ti.TARGET)
FROM OZF_TIME_ALLOCATIONS ti
WHERE ti.ALLOCATION_FOR = 'PROD'
AND ti.ALLOCATION_FOR_ID = p.PRODUCT_ALLOCATION_ID),
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.allocation_for = 'CUST'
AND p.allocation_for_id = l_account_allocation_id;
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.TARGET = ROUND(NVL(l_total_account_target, 0),0),
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.allocation_for = 'CUST'
AND p.allocation_for_id = l_account_allocation_id
AND p.item_id = -9999;
UPDATE OZF_TIME_ALLOCATIONS t
SET t.TARGET = ROUND((NVL(l_total_account_target, 0) / l_p_denominator), 0),
t.object_version_number = t.object_version_number + 1,
t.last_update_date = SYSDATE,
t.last_updated_by = FND_GLOBAL.USER_ID,
t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE t.allocation_for = 'PROD'
AND t.allocation_for_id IN ( SELECT p.product_allocation_id
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'CUST'
AND p.allocation_for_id = l_account_allocation_id
AND p.item_id = -9999 );
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.TARGET = (SELECT SUM(ti.TARGET)
FROM OZF_TIME_ALLOCATIONS ti
WHERE ti.ALLOCATION_FOR = 'PROD'
AND ti.ALLOCATION_FOR_ID = p.PRODUCT_ALLOCATION_ID),
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.allocation_for = 'CUST'
AND p.allocation_for_id = l_account_allocation_id
AND p.item_id = -9999;
SELECT SUM(p.TARGET) INTO l_diff_target_1
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'CUST'
AND p.allocation_for_id = l_account_allocation_id;
UPDATE OZF_TIME_ALLOCATIONS t
SET t.TARGET = t.TARGET + l_diff_target,
t.object_version_number = t.object_version_number + 1,
t.last_update_date = SYSDATE,
t.last_updated_by = FND_GLOBAL.USER_ID,
t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id)
FROM OZF_TIME_ALLOCATIONS x
WHERE x.allocation_for = 'PROD'
AND x.allocation_for_id IN ( SELECT max(p.product_allocation_id)
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'CUST'
AND p.allocation_for_id = l_account_allocation_id
AND p.target =
(SELECT max(xz.target)
FROM OZF_PRODUCT_ALLOCATIONS xz
WHERE xz.allocation_for = 'CUST'
AND xz.allocation_for_id = l_account_allocation_id
)
)
AND x.target =
(SELECT max(zx.target)
FROM OZF_TIME_ALLOCATIONS zx
WHERE zx.allocation_for = 'PROD'
AND zx.allocation_for_id IN (SELECT max(pz.product_allocation_id)
FROM OZF_PRODUCT_ALLOCATIONS pz
WHERE pz.allocation_for = 'CUST'
AND pz.allocation_for_id = l_account_allocation_id
AND pz.target =
(SELECT max(xz.target)
FROM OZF_PRODUCT_ALLOCATIONS xz
WHERE xz.allocation_for = 'CUST'
AND xz.allocation_for_id = l_account_allocation_id
)
)
)
)
RETURNING t.allocation_for_id INTO l_temp_product_allocation_id;
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.TARGET = p.TARGET + l_diff_target,
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.product_allocation_id = l_temp_product_allocation_id;
UPDATE OZF_TIME_ALLOCATIONS t
SET t.TARGET = t.TARGET + l_diff_target,
t.object_version_number = t.object_version_number + 1,
t.last_update_date = SYSDATE,
t.last_updated_by = FND_GLOBAL.USER_ID,
t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
WHERE x.allocation_for = 'PROD'
AND x.allocation_for_id IN ( SELECT p.product_allocation_id
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'CUST'
AND p.allocation_for_id = l_account_allocation_id
AND p.item_id = -9999 )
);
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.TARGET = p.TARGET + l_diff_target,
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.allocation_for = 'CUST'
AND p.allocation_for_id = l_account_allocation_id
AND p.item_id = -9999;
p_acct_alloc_rec.selected_flag := 'N';
Ozf_Account_Allocations_Pkg.Insert_Row(
px_Account_allocation_id => l_account_allocation_id,
p_allocation_for => p_acct_alloc_rec.allocation_for,
p_allocation_for_id => p_acct_alloc_rec.allocation_for_id,
p_cust_account_id => p_acct_alloc_rec.cust_account_id,
p_site_use_id => p_acct_alloc_rec.site_use_id,
p_site_use_code => p_acct_alloc_rec.site_use_code,
p_location_id => p_acct_alloc_rec.location_id,
p_bill_to_site_use_id => p_acct_alloc_rec.bill_to_site_use_id,
p_bill_to_location_id => p_acct_alloc_rec.bill_to_location_id,
p_parent_party_id => p_acct_alloc_rec.parent_party_id,
p_rollup_party_id => p_acct_alloc_rec.rollup_party_id,
p_selected_flag => p_acct_alloc_rec.selected_flag,
p_target => p_acct_alloc_rec.target,
p_lysp_sales => p_acct_alloc_rec.lysp_sales,
p_parent_Account_allocation_id => p_acct_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_acct_alloc_rec.attribute_category,
p_attribute1 => p_acct_alloc_rec.attribute1,
p_attribute2 => p_acct_alloc_rec.attribute2,
p_attribute3 => p_acct_alloc_rec.attribute3,
p_attribute4 => p_acct_alloc_rec.attribute4,
p_attribute5 => p_acct_alloc_rec.attribute5,
p_attribute6 => p_acct_alloc_rec.attribute6,
p_attribute7 => p_acct_alloc_rec.attribute7,
p_attribute8 => p_acct_alloc_rec.attribute8,
p_attribute9 => p_acct_alloc_rec.attribute9,
p_attribute10 => p_acct_alloc_rec.attribute10,
p_attribute11 => p_acct_alloc_rec.attribute11,
p_attribute12 => p_acct_alloc_rec.attribute12,
p_attribute13 => p_acct_alloc_rec.attribute13,
p_attribute14 => p_acct_alloc_rec.attribute14,
p_attribute15 => p_acct_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 => p_time_alloc_rec.target,
p_lysp_sales => p_time_alloc_rec.lysp_sales,
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
);
SELECT a.TARGET INTO l_diff_target_1
FROM OZF_ACCOUNT_ALLOCATIONS a
WHERE a.allocation_for = 'FUND'
AND a.allocation_for_id = l_fund_id
AND a.parent_party_id = -9999;
SELECT SUM(t.TARGET) INTO l_diff_target_2
FROM OZF_TIME_ALLOCATIONS t
WHERE t.allocation_for = 'CUST'
AND t.allocation_for_id IN ( SELECT a.account_allocation_id
FROM OZF_ACCOUNT_ALLOCATIONS a
WHERE a.allocation_for = 'FUND'
AND a.allocation_for_id = l_fund_id
AND a.parent_party_id = -9999 );
UPDATE OZF_TIME_ALLOCATIONS t
SET t.TARGET = t.TARGET + l_diff_target,
t.object_version_number = t.object_version_number + 1,
t.last_update_date = SYSDATE,
t.last_updated_by = FND_GLOBAL.USER_ID,
t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
WHERE x.allocation_for = 'CUST'
AND x.allocation_for_id IN ( SELECT a.account_allocation_id
FROM OZF_ACCOUNT_ALLOCATIONS a
WHERE a.allocation_for = 'FUND'
AND a.allocation_for_id = l_fund_id
AND a.parent_party_id = -9999 )
);
SELECT SUM(a.TARGET) INTO l_diff_target_1
FROM OZF_ACCOUNT_ALLOCATIONS a
WHERE a.allocation_for = 'FUND'
AND a.allocation_for_id = l_fund_id;
UPDATE OZF_TIME_ALLOCATIONS t
SET t.TARGET = t.TARGET + l_diff_target,
t.object_version_number = t.object_version_number + 1,
t.last_update_date = SYSDATE,
t.last_updated_by = FND_GLOBAL.USER_ID,
t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id) from OZF_TIME_ALLOCATIONS x
WHERE x.allocation_for = 'CUST'
AND x.allocation_for_id IN ( SELECT a.account_allocation_id
FROM OZF_ACCOUNT_ALLOCATIONS a
WHERE a.allocation_for = 'FUND'
AND a.allocation_for_id = l_fund_id
AND a.parent_party_id = -9999)
);
UPDATE OZF_ACCOUNT_ALLOCATIONS a
SET a.TARGET = a.TARGET + l_diff_target,
a.object_version_number = a.object_version_number + 1,
a.last_update_date = SYSDATE,
a.last_updated_by = FND_GLOBAL.USER_ID,
a.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE a.allocation_for = 'FUND'
AND a.allocation_for_id = l_fund_id
AND a.parent_party_id = -9999;
UPDATE OZF_TIME_ALLOCATIONS t
SET t.TARGET = t.TARGET + l_diff_target,
t.object_version_number = t.object_version_number + 1,
t.last_update_date = SYSDATE,
t.last_updated_by = FND_GLOBAL.USER_ID,
t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE t.time_allocation_id = (SELECT MAX(x.time_allocation_id)
FROM OZF_TIME_ALLOCATIONS x
WHERE x.allocation_for = 'CUST'
AND x.allocation_for_id IN ( SELECT MAX(a.account_allocation_id)
FROM OZF_ACCOUNT_ALLOCATIONS a
WHERE a.allocation_for = 'FUND'
AND a.allocation_for_id = l_fund_id
AND a.target = (SELECT MAX(xyz.target)
FROM OZF_ACCOUNT_ALLOCATIONS xyz
WHERE xyz.allocation_for = 'FUND'
AND xyz.allocation_for_id = l_fund_id)
)
AND x.target = (SELECT MAX(xyz2.target)
FROM OZF_TIME_ALLOCATIONS xyz2
WHERE xyz2.allocation_for = 'CUST'
AND xyz2.allocation_for_id IN
( SELECT MAX(ax.account_allocation_id)
FROM OZF_ACCOUNT_ALLOCATIONS ax
WHERE ax.allocation_for = 'FUND'
AND ax.allocation_for_id = l_fund_id
AND ax.target =
(SELECT MAX(yz.target)
FROM OZF_ACCOUNT_ALLOCATIONS yz
WHERE yz.allocation_for = 'FUND'
AND yz.allocation_for_id = l_fund_id)
)
)
)
RETURNING t.allocation_for_id INTO l_temp_account_allocation_id;
UPDATE OZF_ACCOUNT_ALLOCATIONS a
SET a.TARGET = a.TARGET + l_diff_target,
a.object_version_number = a.object_version_number + 1,
a.last_update_date = SYSDATE,
a.last_updated_by = FND_GLOBAL.USER_ID,
a.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE a.account_allocation_id = l_temp_account_allocation_id;
UPDATE OZF_TIME_ALLOCATIONS t
SET t.TARGET = t.TARGET + l_diff_target,
t.object_version_number = t.object_version_number + 1,
t.last_update_date = SYSDATE,
t.last_updated_by = FND_GLOBAL.USER_ID,
t.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE t.time_allocation_id = (SELECT max(x.time_allocation_id)
FROM OZF_TIME_ALLOCATIONS x
WHERE x.allocation_for = 'PROD'
AND x.allocation_for_id IN ( SELECT max(p.product_allocation_id)
FROM OZF_PRODUCT_ALLOCATIONS p
WHERE p.allocation_for = 'CUST'
AND p.allocation_for_id = l_temp_account_allocation_id
AND p.target =
(SELECT max(xz.target)
FROM OZF_PRODUCT_ALLOCATIONS xz
WHERE xz.allocation_for = 'CUST'
AND xz.allocation_for_id = l_temp_account_allocation_id
)
)
AND x.target =
(SELECT max(zx.target)
FROM OZF_TIME_ALLOCATIONS zx
WHERE zx.allocation_for = 'PROD'
AND zx.allocation_for_id IN (SELECT max(pz.product_allocation_id)
FROM OZF_PRODUCT_ALLOCATIONS pz
WHERE pz.allocation_for = 'CUST'
AND pz.allocation_for_id = l_temp_account_allocation_id
AND pz.target =
(SELECT max(xz.target)
FROM OZF_PRODUCT_ALLOCATIONS xz
WHERE xz.allocation_for = 'CUST'
AND xz.allocation_for_id = l_temp_account_allocation_id
)
)
)
)
RETURNING t.allocation_for_id INTO l_temp_product_allocation_id;
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.TARGET = p.TARGET + l_diff_target,
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.product_allocation_id = l_temp_product_allocation_id;
SELECT
owner,
start_period_id,
end_period_id,
start_date_active,
end_date_active,
status_code,
original_budget,
transfered_in_amt,
transfered_out_amt,
node_id, -- (=territory id)
product_spread_time_id period_type_id -- (= minor_scale_id i.e. qtrly or monthly)
FROM
ozf_funds_all_vl
WHERE
fund_id = l_fund_id;
SELECT
j.terr_id territory_id
FROM
jtf_terr_rsc_all j, jtf_terr_rsc_access_all j2
WHERE
j.resource_id = l_resource_id
-- AND j.primary_contact_flag = 'Y' ;
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ozf_account_allocations a
WHERE
a.allocation_for = 'FUND'
AND a.allocation_for_id = l_fund_id
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id IN (l_in_clause);
SELECT
a.account_allocation_id account_allocation_id,
a.cust_account_id cust_account_id,
a.site_use_id site_use_id,
a.site_use_code site_use_code,
a.location_id location_id,
a.bill_to_site_use_id bill_to_site_use_id,
a.bill_to_location_id bill_to_location_id,
a.parent_party_id parent_party_id,
a.rollup_party_id rollup_party_id
FROM
ozf_account_allocations a
WHERE
a.allocation_for = 'FUND'
AND a.allocation_for_id = l_fund_id;
SELECT
SUM(bsmv.sales) account_sales
FROM
ozf_order_sales_v bsmv
WHERE
bsmv.ship_to_site_use_id = l_site_use_id
AND bsmv.time_id IN (l_in_clause);
SELECT
SUM(bsmv.sales) account_sales
FROM
ozf_order_sales_v bsmv
WHERE
bsmv.ship_to_site_use_id = l_site_use_id
AND bsmv.time_id = l_time_id;
SELECT SUM(t.target)
FROM
ozf_time_allocations t,
ozf_product_allocations p
WHERE
p.fund_id = l_fund_id
AND t.allocation_for_id = p.product_allocation_id
AND t.allocation_for = 'PROD'
AND t.time_id IN (l_in_clause);
' SELECT SUM(t.target) '||
' FROM '||
' ozf_time_allocations t,'||
' ozf_product_allocations p'||
' WHERE'||
' p.fund_id = :l_fund_id'||
' AND t.allocation_for_id = p.product_allocation_id'||
' AND t.allocation_for = ''PROD'' '||
' AND t.time_id IN (';
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_id = l_site_use_id
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.inventory_item_id = l_product_id
AND bsmv.time_id = l_time_id;
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_id = l_site_use_id
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id = l_time_id
AND bsmv.inventory_item_id IN
( SELECT mtl.inventory_item_id
FROM mtl_item_categories mtl
WHERE mtl.category_id = l_category_id
MINUS
SELECT p.item_id
FROM ozf_product_allocations p
WHERE p.fund_id = l_fund_id
AND p.item_type = 'PRICING_ATTRIBUTE1'
);
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_id = l_site_use_id
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id = l_time_id
AND NOT EXISTS
(
( SELECT p.item_id
FROM ozf_product_allocations p
WHERE
p.fund_id = l_fund_id
AND p.item_type = 'PRICING_ATTRIBUTE1'
AND p.item_id = bsmv.inventory_item_id
UNION ALL
SELECT mtl.inventory_item_id
FROM ozf_product_allocations p,
mtl_item_categories mtl
WHERE
p.fund_id = l_fund_id
AND p.item_type = 'PRICING_ATTRIBUTE2'
AND p.item_id = mtl.category_id
AND mtl.inventory_item_id = bsmv.inventory_item_id
)
MINUS
SELECT prod.inventory_item_id
FROM ams_act_products prod
where
prod.level_type_code = 'PRODUCT'
AND prod.arc_act_product_used_by = 'FUND'
AND prod.act_product_used_by_id = l_fund_id
AND prod.excluded_flag = 'Y'
AND prod.inventory_item_id = bsmv.inventory_item_id
);
SELECT
p.product_allocation_id,
p.item_id,
p.item_type,
p.target
FROM
ozf_product_allocations p
WHERE
p.allocation_for = 'CUST'
AND p.allocation_for_id = l_account_allocation_id;
SELECT t.target
FROM
ozf_time_allocations t
WHERE
t.allocation_for_id = l_product_allocation_id
AND t.allocation_for = 'PROD'
AND t.time_id = l_time_id;
SELECT
p.product_allocation_id,
p.target
FROM
ozf_product_allocations p
WHERE
p.fund_id = l_fund_id
AND p.allocation_for = 'FACT'
AND p.allocation_for_id = l_addon_fact_id
AND p.item_id = l_item_id
AND p.item_type = l_item_type;
SELECT t.target
FROM
ozf_time_allocations t
WHERE
t.allocation_for_id = l_product_allocation_id
AND t.allocation_for = 'PROD'
AND t.time_id = l_time_id;
SELECT DISTINCT t.time_id
FROM
ozf_time_allocations t,
ozf_account_allocations a
WHERE
a.allocation_for = 'FUND'
AND a.allocation_for_id = l_fund_id
AND t.allocation_for_id = a.account_allocation_id
AND t.allocation_for = 'CUST';
SELECT * BULK COLLECT INTO l_period_tbl
FROM
( SELECT * FROM TABLE(CAST(l_new_ozf_period_tbl as OZF_PERIOD_TBL_TYPE))
MINUS
SELECT * FROM TABLE(CAST(l_old_ozf_period_tbl as OZF_PERIOD_TBL_TYPE))
);
UPDATE ozf_account_allocations a
SET a.target = a.target + l_total_account_target,
a.lysp_sales = a.lysp_sales + l_total_account_sales,
a.object_version_number = a.object_version_number + 1,
a.last_update_date = SYSDATE,
a.last_updated_by = FND_GLOBAL.USER_ID,
a.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE a.account_allocation_id = l_account_allocation_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 => p_time_alloc_rec.target,
p_lysp_sales => p_time_alloc_rec.lysp_sales,
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
);
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 => p_time_alloc_rec.target,
p_lysp_sales => p_time_alloc_rec.lysp_sales,
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
);
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.lysp_sales = p.lysp_sales + l_total_product_sales,
p.target = p.target + l_total_product_target,
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.product_allocation_id = l_product_allocation_id;
UPDATE ozf_account_allocations a
SET a.target = a.target + l_total_target_unalloc,
a.object_version_number = a.object_version_number + 1,
a.last_update_date = SYSDATE,
a.last_updated_by = FND_GLOBAL.USER_ID,
a.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE a.allocation_for = 'FUND'
AND a.allocation_for_id = l_fund_id
AND a.site_use_id = -9999
RETURNING account_allocation_id INTO l_account_allocation_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 => p_time_alloc_rec.target,
p_lysp_sales => p_time_alloc_rec.lysp_sales,
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
);
select count(product_allocation_id)
from ozf_product_allocations
where fund_id = l_fund_id;
IF l_mode IN ('CREATE', 'DELETE', 'PUBLISH', 'ADD')
AND l_obj_id > 0
AND l_context IN ('ROOT', 'FACT')
THEN
NULL;
ELSIF l_mode = 'DELETE' AND l_context = 'ROOT' THEN
delete_cascade_product_spread
(p_api_version => p_api_version,
x_return_status => x_return_status,
x_error_number => x_error_number,
x_error_message => x_error_message,
p_fund_id => l_obj_id
);
ELSIF l_mode = 'DELETE' AND l_context = 'FACT' THEN
delete_fact_product_spread
(p_api_version => p_api_version,
x_return_status => x_return_status,
x_error_number => x_error_number,
x_error_message => x_error_message,
p_fact_id => l_obj_id
);
IF l_mode IN ('ADD', 'DELETE')
AND l_fund_id > 0
AND l_item_id > 0
AND l_item_type IN ('PRICING_ATTRIBUTE1', 'PRICING_ATTRIBUTE2')
THEN
NULL;
ELSIF l_mode = 'DELETE' THEN -- this will remove ONE product only and adjust the OTHERS quota for all FACTS
delete_single_product
(p_api_version => p_api_version,
x_return_status => x_return_status,
x_error_number => x_error_number,
x_error_message => x_error_message,
p_fund_id => l_fund_id,
p_item_id => l_item_id,
p_item_type => l_item_type
);
select account_allocation_id
from ozf_account_allocations
where allocation_for = 'FUND'
AND allocation_for_id = l_fund_id
AND parent_party_id > 0;
IF l_mode IN ('FIRSTTIME', 'ADDON', 'DELETE') AND l_fund_id > 0 THEN
NULL;
ELSIF l_mode = 'DELETE' THEN
delete_target_allocation
(p_api_version => p_api_version,
x_return_status => x_return_status,
x_error_number => x_error_number,
x_error_message => x_error_message,
p_fund_id => l_fund_id
);
SELECT
target into l_target
FROM
OZF_TIME_ALLOCATIONS
WHERE
allocation_for = p_allocation_for
AND allocation_for_id = p_allocation_for_id
AND time_id = p_time_id;
SELECT
time_allocation_id into l_time_allocation_id
FROM
OZF_TIME_ALLOCATIONS
WHERE
allocation_for = p_allocation_for
AND allocation_for_id = p_allocation_for_id
AND time_id = p_time_id;
SELECT
lysp_sales into l_lysp_sales
FROM
OZF_TIME_ALLOCATIONS
WHERE
allocation_for = p_allocation_for
AND allocation_for_id = p_allocation_for_id
AND time_id = p_time_id;
SELECT
owner,
start_period_id,
end_period_id,
start_date_active,
end_date_active,
status_code,
original_budget,
transfered_in_amt,
transfered_out_amt,
node_id, -- (=territory id)
product_spread_time_id period_type_id -- (= minor_scale_id i.e. qtrly or monthly)
FROM
ozf_funds_all_vl
WHERE
fund_id = p_fund_id;
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_id = l_site_use_id
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.inventory_item_id = l_product_id
AND bsmv.time_id = l_time_id;
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_id = l_site_use_id
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id = l_time_id
AND bsmv.inventory_item_id IN
( SELECT DISTINCT MIC.INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES MIC,
ENI_PROD_DENORM_HRCHY_V DENORM
WHERE MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID
AND MIC.CATEGORY_ID = DENORM.CHILD_ID
AND DENORM.PARENT_ID = l_category_id
MINUS
SELECT p.item_id
FROM ozf_product_allocations p
WHERE p.fund_id = l_fund_id
AND p.item_type = 'PRICING_ATTRIBUTE1'
);
SELECT
SUM(bsmv.sales) sales
FROM
ozf_order_sales_v bsmv,
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_id = l_site_use_id
AND bsmv.ship_to_site_use_id = a.site_use_id
AND bsmv.time_id = l_time_id
AND NOT EXISTS
(
( SELECT p.item_id
FROM ozf_product_allocations p
WHERE
p.fund_id = l_fund_id
AND p.item_type = 'PRICING_ATTRIBUTE1'
AND p.item_id = bsmv.inventory_item_id
UNION ALL
SELECT MIC.INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES MIC,
ENI_PROD_DENORM_HRCHY_V DENORM,
OZF_PRODUCT_ALLOCATIONS p
WHERE p.FUND_ID = l_fund_id
AND p.ITEM_TYPE = 'PRICING_ATTRIBUTE2'
AND p.ITEM_ID = DENORM.PARENT_ID
AND MIC.CATEGORY_SET_ID = DENORM.CATEGORY_SET_ID
AND MIC.CATEGORY_ID = DENORM.CHILD_ID
AND MIC.INVENTORY_ITEM_ID = bsmv.inventory_item_id
)
MINUS
SELECT prod.inventory_item_id
FROM ams_act_products prod
where
prod.level_type_code = 'PRODUCT'
AND prod.arc_act_product_used_by = 'FUND'
AND prod.act_product_used_by_id = l_fund_id
AND prod.excluded_flag = 'Y'
AND prod.inventory_item_id = bsmv.inventory_item_id
);
SELECT
p.product_allocation_id,
p.item_id,
p.item_type,
p.target
FROM
ozf_product_allocations p
WHERE
p.fund_id = l_fund_id;
SELECT *
FROM ozf_time_allocations tt
WHERE tt.allocation_for = 'CUST'
AND tt.allocation_for_id = l_alloc_for_id; -- p_acct_alloc_rec.account_allocation_id;
SELECT *
FROM
ozf_product_allocations p
WHERE
p.allocation_for = 'CUST'
AND p.allocation_for_id = l_acct_alloc_id; -- p_acct_alloc_rec.account_allocation_id;
SELECT *
FROM ozf_time_allocations tt
WHERE tt.allocation_for = 'PROD'
AND tt.allocation_for_id = l_alloc_for_id; -- p_prod_alloc_rec.product_allocation_id;
SELECT * INTO p_acct_alloc_rec
FROM ozf_account_allocations aa
WHERE aa.allocation_for = 'FUND'
and allocation_for_id = p_corr_fund_id
and site_use_code = 'SHIP_TO'
and site_use_id = p_ship_to_site_use_id;
SELECT * INTO p_acct_alloc_rec
FROM ozf_account_allocations aa
WHERE aa.allocation_for = 'FUND'
and allocation_for_id = p_fund_id
and site_use_id = -9999;
SELECT
a.cust_account_id cust_account_id,
a.site_use_id site_use_id,
a.site_use_code site_use_code,
OZF_LOCATION_PVT.get_location_id(a.site_use_id) location_id,
NVL(a.bill_to_site_use_id, -9996) bill_to_site_use_id,
OZF_LOCATION_PVT.get_location_id(a.bill_to_site_use_id) bill_to_location_id,
a.party_id parent_party_id,
NVL(a.rollup_party_id, a.party_id) rollup_party_id
INTO
p_acct_alloc_rec.cust_account_id,
p_acct_alloc_rec.site_use_id,
p_acct_alloc_rec.site_use_code,
p_acct_alloc_rec.location_id,
p_acct_alloc_rec.bill_to_site_use_id,
p_acct_alloc_rec.bill_to_location_id,
p_acct_alloc_rec.parent_party_id,
p_acct_alloc_rec.rollup_party_id
FROM
ams_party_market_segments a
WHERE
a.market_qualifier_reference = p_terr_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_code = 'SHIP_TO'
AND a.party_id IS NOT NULL
AND a.site_use_id IS NOT NULL
AND a.site_use_id = p_ship_to_site_use_id;
p_acct_alloc_rec.selected_flag := 'Y';
Ozf_Account_Allocations_Pkg.Insert_Row(
px_Account_allocation_id => l_account_allocation_id,
p_allocation_for => p_acct_alloc_rec.allocation_for,
p_allocation_for_id => p_fund_id,
p_cust_account_id => p_acct_alloc_rec.cust_account_id,
p_site_use_id => p_acct_alloc_rec.site_use_id,
p_site_use_code => p_acct_alloc_rec.site_use_code,
p_location_id => p_acct_alloc_rec.location_id,
p_bill_to_site_use_id => p_acct_alloc_rec.bill_to_site_use_id,
p_bill_to_location_id => p_acct_alloc_rec.bill_to_location_id,
p_parent_party_id => p_acct_alloc_rec.parent_party_id,
p_rollup_party_id => p_acct_alloc_rec.rollup_party_id,
p_selected_flag => p_acct_alloc_rec.selected_flag,
p_target => 0,
p_lysp_sales => p_acct_alloc_rec.lysp_sales,
p_parent_Account_allocation_id => p_acct_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_acct_alloc_rec.attribute_category,
p_attribute1 => p_acct_alloc_rec.attribute1,
p_attribute2 => p_acct_alloc_rec.attribute2,
p_attribute3 => p_acct_alloc_rec.attribute3,
p_attribute4 => p_acct_alloc_rec.attribute4,
p_attribute5 => p_acct_alloc_rec.attribute5,
p_attribute6 => p_acct_alloc_rec.attribute6,
p_attribute7 => p_acct_alloc_rec.attribute7,
p_attribute8 => p_acct_alloc_rec.attribute8,
p_attribute9 => p_acct_alloc_rec.attribute9,
p_attribute10 => p_acct_alloc_rec.attribute10,
p_attribute11 => p_acct_alloc_rec.attribute11,
p_attribute12 => p_acct_alloc_rec.attribute12,
p_attribute13 => p_acct_alloc_rec.attribute13,
p_attribute14 => p_acct_alloc_rec.attribute14,
p_attribute15 => p_acct_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 => l_account_allocation_id,
p_time_id => p_time_alloc_rec.time_id,
p_period_type_id => p_time_alloc_rec.period_type_id,
p_target => p_time_alloc_rec.target,
p_lysp_sales => p_time_alloc_rec.lysp_sales,
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
);
update ozf_time_allocations tt
set tt.target = 0
WHERE tt.allocation_for = 'CUST'
AND tt.allocation_for_id = l_account_allocation_id
and EXISTS
(select 'x'
from ozf_time_ent_period period
where period.ent_period_id = tt.time_id
and period.end_date < trunc(sysdate)
and tt.period_type_id = 32
UNION
select 'x'
from ozf_time_ent_qtr qtr
where qtr.ent_qtr_id = tt.time_id
and qtr.end_date < trunc(sysdate)
and tt.period_type_id = 64
);
update ozf_time_allocations tta
set tta.target = tta.target - (
SELECT ttb.target
FROM ozf_time_allocations ttb
WHERE ttb.allocation_for = 'CUST'
AND ttb.allocation_for_id = l_account_allocation_id
AND ttb.time_id = tta.time_id
)
where
tta.allocation_for = 'CUST'
AND tta.allocation_for_id = ( SELECT aa.account_allocation_id
FROM ozf_account_allocations aa
WHERE aa.allocation_for = 'FUND'
and aa.allocation_for_id = p_fund_id
and aa.site_use_id = -9999)
and EXISTS
(select 'x'
from ozf_time_ent_period period
where period.ent_period_id = tta.time_id
and period.end_date >= trunc(sysdate)
and tta.period_type_id = 32
UNION
select 'x'
from ozf_time_ent_qtr qtr
where qtr.ent_qtr_id = tta.time_id
and qtr.end_date >= trunc(sysdate)
and tta.period_type_id = 64
);
update ozf_time_allocations tta, ozf_time_allocations ttb
set tta.target = tta.target - ttb.target
where ttb.allocation_for = 'CUST'
AND ttb.allocation_for_id = l_account_allocation_id
AND tta.time_id = ttb.time_id
AND tta.allocation_for = 'CUST'
AND tta.allocation_for_id ( SELECT aa.account_allocation_id
FROM ozf_account_allocations aa
WHERE aa.allocation_for = 'FUND'
and aa.allocation_for_id = p_fund_id
and aa.site_use_id = -9999)
and EXISTS
(select 'x'
from ozf_time_ent_period period
where period.ent_period_id = tta.time_id
and period.start_date >= trunc(sysdate)
and tta.period_type_id = 32
UNION
select 'x'
from ozf_time_ent_qtr qtr
where qtr.ent_qtr_id = tta.time_id
and qtr.start_date >= trunc(sysdate)
and tta.period_type_id = 64
);
update ozf_time_allocations tt
set tt.target = 0,
tt.lysp_sales = (
SELECT SUM(bsmv.sales)
FROM ozf_order_sales_v bsmv
WHERE bsmv.ship_to_site_use_id = p_ship_to_site_use_id
AND bsmv.time_id = OZF_TIME_API_PVT.get_lysp_id(tt.time_id, tt.period_type_id)
)
WHERE tt.allocation_for = 'CUST'
AND tt.allocation_for_id = l_account_allocation_id;
UPDATE OZF_ACCOUNT_ALLOCATIONS aa
SET (aa.TARGET, aa.LYSP_SALES) = (
SELECT SUM(ti.TARGET), SUM(ti.lysp_sales)
FROM OZF_TIME_ALLOCATIONS ti
WHERE ti.ALLOCATION_FOR = 'CUST'
AND ti.ALLOCATION_FOR_ID = aa.account_allocation_id
),
aa.object_version_number = aa.object_version_number + 1,
aa.last_update_date = SYSDATE,
aa.last_updated_by = FND_GLOBAL.USER_ID,
aa.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE
(aa.account_allocation_id = l_account_allocation_id)
OR
(
aa.allocation_for = 'FUND'
and aa.allocation_for_id = p_fund_id
and aa.site_use_id = -9999
);
Ozf_Product_Allocations_Pkg.Insert_Row(
px_product_allocation_id => l_product_allocation_id,
p_allocation_for => p_prod_alloc_rec.allocation_for,
p_allocation_for_id => l_account_allocation_id,
p_fund_id => p_fund_id,
p_item_type => p_prod_alloc_rec.item_type,
p_item_id => p_prod_alloc_rec.item_id,
p_selected_flag => p_prod_alloc_rec.selected_flag,
p_target => p_prod_alloc_rec.target,
p_lysp_sales => p_prod_alloc_rec.lysp_sales,
p_parent_product_allocation_id => p_prod_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_prod_alloc_rec.attribute_category,
p_attribute1 => p_prod_alloc_rec.attribute1,
p_attribute2 => p_prod_alloc_rec.attribute2,
p_attribute3 => p_prod_alloc_rec.attribute3,
p_attribute4 => p_prod_alloc_rec.attribute4,
p_attribute5 => p_prod_alloc_rec.attribute5,
p_attribute6 => p_prod_alloc_rec.attribute6,
p_attribute7 => p_prod_alloc_rec.attribute7,
p_attribute8 => p_prod_alloc_rec.attribute8,
p_attribute9 => p_prod_alloc_rec.attribute9,
p_attribute10 => p_prod_alloc_rec.attribute10,
p_attribute11 => p_prod_alloc_rec.attribute11,
p_attribute12 => p_prod_alloc_rec.attribute12,
p_attribute13 => p_prod_alloc_rec.attribute13,
p_attribute14 => p_prod_alloc_rec.attribute14,
p_attribute15 => p_prod_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 => l_product_allocation_id,
p_time_id => p_time_alloc_rec.time_id,
p_period_type_id => p_time_alloc_rec.period_type_id,
p_target => p_time_alloc_rec.target,
p_lysp_sales => p_time_alloc_rec.lysp_sales,
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
);
update ozf_time_allocations tt
set tt.target = 0
WHERE tt.allocation_for = 'PROD'
AND tt.allocation_for_id = l_product_allocation_id
and EXISTS
(select 'x'
from ozf_time_ent_period
where ent_period_id = tt.time_id
and end_date < trunc(sysdate)
and tt.period_type_id = 32
UNION
select 'x'
from ozf_time_ent_qtr
where ent_qtr_id = tt.time_id
and end_date < trunc(sysdate)
and tt.period_type_id = 64
);
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.TARGET = (SELECT SUM(ti.TARGET)
FROM OZF_TIME_ALLOCATIONS ti
WHERE ti.ALLOCATION_FOR = 'PROD'
AND ti.ALLOCATION_FOR_ID = p.product_allocation_id),
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.product_allocation_id = l_product_allocation_id;
p_prod_alloc_rec.selected_flag := 'N';
Ozf_Product_Allocations_Pkg.Insert_Row(
px_product_allocation_id => l_product_allocation_id,
p_allocation_for => p_prod_alloc_rec.allocation_for,
p_allocation_for_id => p_prod_alloc_rec.allocation_for_id,
p_fund_id => p_fund_id,
p_item_type => p_prod_alloc_rec.item_type,
p_item_id => p_prod_alloc_rec.item_id,
p_selected_flag => p_prod_alloc_rec.selected_flag,
p_target => p_prod_alloc_rec.target,
p_lysp_sales => p_prod_alloc_rec.lysp_sales,
p_parent_product_allocation_id => p_prod_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_prod_alloc_rec.attribute_category,
p_attribute1 => p_prod_alloc_rec.attribute1,
p_attribute2 => p_prod_alloc_rec.attribute2,
p_attribute3 => p_prod_alloc_rec.attribute3,
p_attribute4 => p_prod_alloc_rec.attribute4,
p_attribute5 => p_prod_alloc_rec.attribute5,
p_attribute6 => p_prod_alloc_rec.attribute6,
p_attribute7 => p_prod_alloc_rec.attribute7,
p_attribute8 => p_prod_alloc_rec.attribute8,
p_attribute9 => p_prod_alloc_rec.attribute9,
p_attribute10 => p_prod_alloc_rec.attribute10,
p_attribute11 => p_prod_alloc_rec.attribute11,
p_attribute12 => p_prod_alloc_rec.attribute12,
p_attribute13 => p_prod_alloc_rec.attribute13,
p_attribute14 => p_prod_alloc_rec.attribute14,
p_attribute15 => p_prod_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 => p_time_alloc_rec.target,
p_lysp_sales => p_time_alloc_rec.lysp_sales,
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
);
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.lysp_sales = l_total_product_sales,
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.product_allocation_id = l_product_allocation_id;
PROCEDURE adjust_target_for_acct_deleted
(
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_error_number OUT NOCOPY NUMBER,
x_error_message OUT NOCOPY VARCHAR2,
p_fund_id IN NUMBER,
p_ship_to_site_use_id IN NUMBER
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'adjust_target_for_acct_deleted';
SAVEPOINT adjust_target_for_acct_deleted;
SELECT aa.account_allocation_id INTO l_account_allocation_id
FROM ozf_account_allocations aa
WHERE aa.allocation_for = 'FUND'
and aa.allocation_for_id = p_fund_id
and aa.site_use_code = 'SHIP_TO'
and aa.site_use_id = p_ship_to_site_use_id;
SELECT aa.account_allocation_id INTO l_unalloc_acct_alloc_id
FROM ozf_account_allocations aa
WHERE aa.allocation_for = 'FUND'
and aa.allocation_for_id = p_fund_id
and aa.site_use_id = -9999;
update ozf_time_allocations tta
set tta.target = tta.target + (
SELECT ttb.target
FROM ozf_time_allocations ttb
WHERE ttb.allocation_for = 'CUST'
AND ttb.allocation_for_id = l_account_allocation_id
AND ttb.time_id = tta.time_id
)
where
tta.allocation_for = 'CUST'
AND tta.allocation_for_id = l_unalloc_acct_alloc_id
and EXISTS
(select 'x'
from ozf_time_ent_period period
where period.ent_period_id = tta.time_id
and period.end_date >= trunc(sysdate)
and tta.period_type_id = 32
UNION
select 'x'
from ozf_time_ent_qtr qtr
where qtr.ent_qtr_id = tta.time_id
and qtr.end_date >= trunc(sysdate)
and tta.period_type_id = 64
);
UPDATE OZF_ACCOUNT_ALLOCATIONS aa
SET (aa.TARGET, aa.LYSP_SALES) = (
SELECT SUM(ti.TARGET), SUM(ti.lysp_sales)
FROM OZF_TIME_ALLOCATIONS ti
WHERE ti.ALLOCATION_FOR = 'CUST'
AND ti.ALLOCATION_FOR_ID = aa.account_allocation_id
),
aa.object_version_number = aa.object_version_number + 1,
aa.last_update_date = SYSDATE,
aa.last_updated_by = FND_GLOBAL.USER_ID,
aa.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE aa.account_allocation_id = l_unalloc_acct_alloc_id;
update ozf_time_allocations ttb
set ttb.target = 0,
ttb.account_status = 'D'
WHERE ttb.allocation_for = 'CUST'
AND ttb.allocation_for_id = l_account_allocation_id
AND EXISTS
(select 'x'
from ozf_time_ent_period period
where period.ent_period_id = ttb.time_id
and period.end_date >= trunc(sysdate)
and ttb.period_type_id = 32
UNION
select 'x'
from ozf_time_ent_qtr qtr
where qtr.ent_qtr_id = ttb.time_id
and qtr.end_date >= trunc(sysdate)
and ttb.period_type_id = 64
);
UPDATE OZF_ACCOUNT_ALLOCATIONS aa
SET (aa.TARGET, aa.LYSP_SALES) = (
SELECT SUM(ti.TARGET), SUM(ti.lysp_sales)
FROM OZF_TIME_ALLOCATIONS ti
WHERE ti.ALLOCATION_FOR = 'CUST'
AND ti.ALLOCATION_FOR_ID = aa.account_allocation_id
),
aa.account_status = 'D',
aa.object_version_number = aa.object_version_number + 1,
aa.last_update_date = SYSDATE,
aa.last_updated_by = FND_GLOBAL.USER_ID,
aa.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE
aa.account_allocation_id = l_account_allocation_id;
update ozf_time_allocations tt
set tt.target = 0,
tt.account_status = 'D'
WHERE tt.allocation_for = 'PROD'
AND tt.allocation_for_id IN (
SELECT pp.product_allocation_id
FROM ozf_product_allocations pp
WHERE pp.allocation_for = 'CUST'
AND pp.allocation_for_id = l_account_allocation_id
)
and EXISTS
(select 'x'
from ozf_time_ent_period
where ent_period_id = tt.time_id
and end_date >= trunc(sysdate)
and tt.period_type_id = 32
UNION
select 'x'
from ozf_time_ent_qtr
where ent_qtr_id = tt.time_id
and end_date >= trunc(sysdate)
and tt.period_type_id = 64
);
UPDATE OZF_PRODUCT_ALLOCATIONS p
SET p.TARGET = (SELECT SUM(ti.TARGET)
FROM OZF_TIME_ALLOCATIONS ti
WHERE ti.ALLOCATION_FOR = 'PROD'
AND ti.ALLOCATION_FOR_ID = p.product_allocation_id),
p.account_status = 'D',
p.object_version_number = p.object_version_number + 1,
p.last_update_date = SYSDATE,
p.last_updated_by = FND_GLOBAL.USER_ID,
p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
WHERE p.product_allocation_id IN (
SELECT pp.product_allocation_id
FROM ozf_product_allocations pp
WHERE pp.allocation_for = 'CUST'
AND pp.allocation_for_id = l_account_allocation_id
);
ROLLBACK TO adjust_target_for_acct_deleted;
ROLLBACK TO adjust_target_for_acct_deleted;
ROLLBACK TO adjust_target_for_acct_deleted;
ROLLBACK TO adjust_target_for_acct_deleted;
END adjust_target_for_acct_deleted;
SELECT DISTINCT
FF.NODE_ID
FROM OZF_FUNDS_ALL_b FF
WHERE
FF.FUND_TYPE = 'QUOTA'
AND FF.STATUS_CODE <> 'CANCELLED'
AND FF.NODE_ID = NVL(p_terr_id, FF.NODE_ID)
AND EXISTS
(SELECT 'x'
FROM OZF_ACCOUNT_ALLOCATIONS AA
WHERE AA.ALLOCATION_FOR = 'FUND'
AND AA.ALLOCATION_FOR_ID = FF.FUND_ID
);
SELECT DISTINCT FF.NODE_ID
FROM OZF_FUNDS_ALL_b FF
WHERE
FF.FUND_TYPE = 'QUOTA'
AND FF.STATUS_CODE <> 'CANCELLED'
AND FF.NODE_ID = NVL(p_terr_id, FF.NODE_ID)
AND EXISTS
(SELECT 'x'
FROM OZF_ACCOUNT_ALLOCATIONS AA
WHERE AA.ALLOCATION_FOR = 'FUND'
AND AA.ALLOCATION_FOR_ID = FF.FUND_ID
)
----------
UNION ALL
----------
SELECT DISTINCT FF.NODE_ID
FROM OZF_FUNDS_ALL_b FF
WHERE
FF.FUND_TYPE = 'QUOTA'
AND FF.STATUS_CODE <> 'CANCELLED'
AND EXISTS
(SELECT 'x'
FROM OZF_ACCOUNT_ALLOCATIONS AA
WHERE AA.ALLOCATION_FOR = 'FUND'
AND AA.ALLOCATION_FOR_ID = FF.FUND_ID
)
AND FF.NODE_ID IN
(
select distinct node_id
from ozf_funds_all_b outer
where not exists (select 'x' from ozf_funds_all_b inner where inner.parent_fund_id = outer.fund_id)
connect by prior fund_id = parent_fund_id
start with parent_fund_id = (select inner2.fund_id
from ozf_funds_all_b inner2
where inner2.node_id = p_terr_id -- 3104
and inner2.fund_type = 'QUOTA'
and inner2.status_code <> 'CANCELLED'
and rownum = 1
)
);
SELECT
'A' account_status_code,
---a.cust_account_id cust_account_id,
a.site_use_id site_use_id
---a.site_use_code site_use_code,
---OZF_LOCATION_PVT.get_location_id(a.site_use_id) location_id,
---NVL(a.bill_to_site_use_id, -9996) bill_to_site_use_id,
---OZF_LOCATION_PVT.get_location_id(a.bill_to_site_use_id) bill_to_location_id,
---a.party_id parent_party_id,
---NVL(a.rollup_party_id, a.party_id) rollup_party_id
FROM
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_code = 'SHIP_TO'
AND a.party_id IS NOT NULL
AND a.site_use_id IS NOT NULL
------------
MINUS
------------
SELECT
'A' account_status_code,
---a.cust_account_id cust_account_id,
a.site_use_id site_use_id
---a.site_use_code site_use_code,
---OZF_LOCATION_PVT.get_location_id(a.site_use_id) location_id,
---NVL(a.bill_to_site_use_id, -9996) bill_to_site_use_id,
---OZF_LOCATION_PVT.get_location_id(a.bill_to_site_use_id) bill_to_location_id,
---a.party_id parent_party_id,
---NVL(a.rollup_party_id, a.party_id) rollup_party_id
FROM
ozf_party_market_segments_t a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_code = 'SHIP_TO'
AND a.party_id IS NOT NULL
AND a.site_use_id IS NOT NULL;
CURSOR deleted_accounts_csr (l_territory_id number)
IS
SELECT
'D' account_status_code,
---a.cust_account_id cust_account_id,
a.site_use_id site_use_id
---a.site_use_code site_use_code,
---OZF_LOCATION_PVT.get_location_id(a.site_use_id) location_id,
---NVL(a.bill_to_site_use_id, -9996) bill_to_site_use_id,
---OZF_LOCATION_PVT.get_location_id(a.bill_to_site_use_id) bill_to_location_id,
---a.party_id parent_party_id,
---NVL(a.rollup_party_id, a.party_id) rollup_party_id
FROM
ozf_party_market_segments_t a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_code = 'SHIP_TO'
AND a.party_id IS NOT NULL
AND a.site_use_id IS NOT NULL
------------
MINUS
------------
SELECT
'D' account_status_code,
---a.cust_account_id cust_account_id,
a.site_use_id site_use_id
---a.site_use_code site_use_code,
---OZF_LOCATION_PVT.get_location_id(a.site_use_id) location_id,
---NVL(a.bill_to_site_use_id, -9996) bill_to_site_use_id,
---OZF_LOCATION_PVT.get_location_id(a.bill_to_site_use_id) bill_to_location_id,
---a.party_id parent_party_id,
---NVL(a.rollup_party_id, a.party_id) rollup_party_id
FROM
ams_party_market_segments a
WHERE
a.market_qualifier_reference = l_territory_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_code = 'SHIP_TO'
AND a.party_id IS NOT NULL
AND a.site_use_id IS NOT NULL;
SELECT
FF.FUND_ID
FROM OZF_FUNDS_ALL_b FF
WHERE
FF.FUND_TYPE = 'QUOTA'
AND FF.STATUS_CODE <> 'CANCELLED'
AND FF.NODE_ID = l_terr_id
AND EXISTS
(SELECT 'x'
FROM OZF_ACCOUNT_ALLOCATIONS AA
WHERE AA.ALLOCATION_FOR = 'FUND'
AND AA.ALLOCATION_FOR_ID = FF.FUND_ID
AND AA.site_use_id <> l_ship_to_id -- newly added to the FUND
);
SELECT
FF.FUND_ID
FROM OZF_FUNDS_ALL_b FF
WHERE
FF.FUND_TYPE = 'QUOTA'
AND FF.STATUS_CODE <> 'CANCELLED'
AND FF.NODE_ID = l_terr_id
AND EXISTS
(SELECT 'x'
FROM OZF_ACCOUNT_ALLOCATIONS AA
WHERE AA.ALLOCATION_FOR = 'FUND'
AND AA.ALLOCATION_FOR_ID = FF.FUND_ID
AND AA.site_use_id = l_ship_to_id -- the one deleted from the FUND targets
);
SELECT
a.activity_metric_id,
a.level_depth,
a.node_id,
a.previous_fact_id,
a.activity_metric_fact_id
from ozf_act_metric_facts_all a
where a.arc_act_metric_used_by = 'FUND'
and a.act_metric_used_by_id = l_fund_id
order by a.activity_metric_id desc;
SELECT
a.act_metric_used_by_id,
a.node_id,
a.previous_fact_id,
a.activity_metric_fact_id
from ozf_act_metric_facts_all a, OZF_FUNDS_ALL_b FF
where a.arc_act_metric_used_by = 'FUND'
and a.activity_metric_id = l_allocation_id
--and a.level_depth = l_level_depth
and a.node_id = FF.NODE_ID
AND FF.FUND_TYPE = 'QUOTA'
AND FF.STATUS_CODE <> 'CANCELLED'
AND FF.FUND_ID = a.act_metric_used_by_id
AND EXISTS
(SELECT 'x'
FROM OZF_ACCOUNT_ALLOCATIONS AA
WHERE AA.ALLOCATION_FOR = 'FUND'
AND AA.ALLOCATION_FOR_ID = FF.FUND_ID
AND AA.site_use_id = l_ship_to_site_use_id -- new added
)
AND EXISTS
(SELECT 'x'
FROM
(
SELECT
'D' account_status_code,
a.market_qualifier_reference territory_id,
a.site_use_id site_use_id
FROM
ozf_party_market_segments_t a
WHERE
a.market_qualifier_type='TERRITORY'
AND a.site_use_code = 'SHIP_TO'
AND a.party_id IS NOT NULL
AND a.site_use_id = l_ship_to_site_use_id
------------
MINUS
------------
SELECT
'D' account_status_code,
a.market_qualifier_reference territory_id,
a.site_use_id site_use_id
FROM
ams_party_market_segments a
WHERE
a.market_qualifier_type='TERRITORY'
AND a.site_use_code = 'SHIP_TO'
AND a.party_id IS NOT NULL
AND a.site_use_id = l_ship_to_site_use_id
)
);
FOR del_accounts IN deleted_accounts_csr (terr.node_id)
LOOP
FOR quota IN del_quota_list_csr(terr.node_id, del_accounts.site_use_id)
LOOP
IF G_DEBUG_LEVEL THEN
Ozf_Utility_pvt.write_conc_log('- '||l_full_api_name||'- Process Deleted Accounts for Site Use Id =>'||del_accounts.site_use_id);
adjust_target_for_acct_deleted (
p_api_version => p_api_version,
x_return_status => x_return_status,
x_error_number => x_error_number,
x_error_message => x_error_message,
p_fund_id => quota.fund_id,
p_ship_to_site_use_id => del_accounts.site_use_id
);
END LOOP; -- deleted_accounts_csr (terr.node_id)
Ozf_Utility_pvt.write_conc_log('- '||l_full_api_name||'- UPDATE ACCOUNT ALLOCATION Table''s account details based upon latest information from Terr (i.e from TCA).');
UPDATE
(SELECT
FF.NODE_ID territory_id,
aa.account_allocation_id,
aa.site_use_id,
aa.site_use_code,
aa.cust_account_id,
aa.location_id,
aa.bill_to_site_use_id,
aa.bill_to_location_id,
aa.parent_party_id,
aa.rollup_party_id
FROM ozf_account_allocations aa, ozf_funds_all_b ff
WHERE FF.FUND_TYPE = 'QUOTA'
AND FF.STATUS_CODE <> 'CANCELLED'
AND aa.allocation_for = 'FUND'
AND aa.allocation_for_id = FF.FUND_ID
AND FF.NODE_ID = terr.node_id
AND aa.parent_party_id IS NOT NULL
AND aa.parent_party_id > 0
) alloc
SET
(
alloc.cust_account_id,
alloc.location_id,
alloc.bill_to_site_use_id,
alloc.bill_to_location_id,
alloc.parent_party_id,
alloc.rollup_party_id
)
= (
SELECT
a.cust_account_id cust_account_id,
OZF_LOCATION_PVT.get_location_id(a.site_use_id) location_id,
NVL(a.bill_to_site_use_id, -9996) bill_to_site_use_id,
OZF_LOCATION_PVT.get_location_id(a.bill_to_site_use_id) bill_to_location_id,
a.party_id parent_party_id,
NVL(a.rollup_party_id, a.party_id) rollup_party_id
FROM
ams_party_market_segments a
WHERE
a.market_qualifier_reference = alloc.territory_id
AND a.market_qualifier_reference = terr.node_id
AND a.market_qualifier_type='TERRITORY'
AND a.site_use_code = 'SHIP_TO'
AND a.party_id IS NOT NULL
AND a.site_use_id IS NOT NULL
AND a.site_use_id = alloc.site_use_id
AND a.site_use_code = alloc.site_use_code
);