[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct
b.cust_account_id cust_account_id,
b.site_use_id site_use_id ,
b.bill_to_site_use_id bill_to_site_use_id,
c.item_id inventory_item_id ,
c.item_id item_id ,
c.item_type item_type
FROM ozf_funds_all_b a
,ozf_account_allocations b
,ozf_product_allocations c
WHERE a.fund_type = 'QUOTA'
--AND p_report_date BETWEEN a.start_date_active
-- AND a.end_date_active
AND a.status_code <> 'CANCELLED'
AND b.allocation_for = 'FUND'
AND b.allocation_for_id = a.fund_id
AND NVL(b.account_status, 'X') <> 'D'
AND c.allocation_for = 'CUST'
AND c.allocation_for_id = b.account_allocation_id
AND a.parent_fund_id IS NOT NULL
AND NOT EXISTS ( SELECT 1
FROM ozf_funds_all_b bb
WHERE bb.parent_fund_id = a.fund_id );
DELETE FROM ozf_cust_daily_facts
WHERE report_date = p_report_date;
INSERT INTO ozf_cust_daily_facts (
cust_daily_fact_id ,
report_date ,
cust_account_id ,
ship_to_site_use_id ,
bill_to_site_use_id ,
inventory_item_id ,
product_attr_value ,
product_attribute ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login )
VALUES ( ozf_cust_daily_facts_s.nextval,
trunc(p_report_date),
i.cust_account_id,
i.site_use_id,
i.bill_to_site_use_id,
i.inventory_item_id,
i.item_id,
i.item_type,
SYSDATE,
-1,
SYSDATE,
-1,
-1 );
DELETE from OZF_RES_CUST_PROD;
INSERT INTO OZF_RES_CUST_PROD
(SELECT distinct
fund.owner RESOURCE_ID,
acct.parent_party_id PARTY_ID,
acct.cust_account_id CUST_ACCOUNT_ID,
acct.bill_to_site_use_id BILL_TO_SITE_USE_ID,
acct.site_use_id SHIP_TO_SITE_USE_ID,
prod.item_type PRODUCT_ATTRIBUTE,
prod.item_id PRODUCT_ATTR_VALUE
FROM ozf_account_allocations acct,
ozf_product_allocations prod,
(SELECT DISTINCT a.owner
FROM ozf_funds_all_b a
WHERE a.fund_type = 'QUOTA'
AND a.status_code <> 'CANCELLED') fund
WHERE prod.allocation_for = 'CUST'
AND prod.allocation_for_id = acct.account_allocation_id
AND acct.allocation_for = 'FUND'
AND NVL(acct.account_status, 'X') <> 'D'
AND acct.allocation_for_id in
(SELECT aa.fund_id
FROM ozf_funds_all_b aa
WHERE aa.owner = fund.owner
AND aa.fund_type = 'QUOTA'
AND aa.status_code <> 'CANCELLED'
AND NOT EXISTS ( SELECT 1
FROM ozf_funds_all_b bb
WHERE bb.parent_fund_id = aa.fund_id )
UNION ALL
SELECT aa.fund_id
FROM ozf_funds_all_b aa
WHERE aa.fund_type = 'QUOTA'
AND aa.status_code <> 'CANCELLED'
CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
FROM ozf_funds_all_b bb
WHERE bb.owner= fund.owner
AND bb.fund_type = 'QUOTA'
AND bb.status_code <> 'CANCELLED')
)
AND prod.item_type <> 'OTHERS');
SELECT cust_daily_fact_id,
report_date,
cust_account_id,
ship_to_site_use_id,
bill_to_site_use_id,
product_attribute,
product_attr_value,
inventory_item_id
FROM ozf_cust_daily_facts
WHERE report_date = p_report_date;
SELECT a.period_type_id,
NVL(SUM(b.sales),0) tot_sales
FROM ozf_time_rpt_struct a,
ozf_order_sales_v b
WHERE a.report_date = p_report_date
AND BITAND(a.record_type_id, 119) = a.record_type_id
AND a.time_id = b.time_id
AND b.cust_account_id = p_cust_account_id
AND b.ship_to_site_use_id = p_ship_to_site_use_id
AND b.bill_to_site_use_id = DECODE(p_bill_to_site_use_id,
-9996,b.bill_to_site_use_id,
p_bill_to_site_use_id)
AND b.inventory_item_id = p_inventory_item_id
GROUP BY a.period_type_id ;
SELECT inventory_item_id
FROM mtl_item_categories mtl,
eni_prod_denorm_hrchy_v eni
WHERE mtl.category_set_id = eni.category_set_id
AND mtl.category_id = eni.child_id
AND mtl.organization_id = fnd_profile.value('QP_ORGANIZATION_ID')
AND eni.parent_id = p_item_id
AND 'PRICING_ATTRIBUTE2' = p_item_type
UNION ALL
SELECT p_item_id inventory_item_id
FROM dual
WHERE 'PRICING_ATTRIBUTE1' = p_item_type
UNION ALL
SELECT p_item_id inventory_item_id
FROM dual
WHERE 'OTHERS' = p_item_type;
SELECT NVL(SUM(b.sales),0) tot_sales
FROM ozf_time_rpt_struct a,
ozf_order_sales_v b,
ozf_time_day c
WHERE c.report_date = p_report_date
AND a.time_id = decode(p_period_type_id,32,c.ent_period_id,
64, c.ent_qtr_id,
128, c.ent_year_id)
AND b.time_id = a.time_id
AND b.cust_account_id = p_cust_account_id
AND b.ship_to_site_use_id = p_ship_to_site_use_id
AND b.bill_to_site_use_id = DECODE(p_bill_to_site_use_id,
-9996,b.bill_to_site_use_id,
p_bill_to_site_use_id)
AND b.inventory_item_id = p_inventory_item_id;
SELECT b.period_type_id,
NVL(SUM(b.baseline_sales),0) base_sales
FROM ozf_time_rpt_struct a,
ozf_baseline_sales_v b
WHERE a.report_date = p_report_date
AND BITAND(a.record_type_id, 119) = a.record_type_id
AND a.time_id = b.time_id
AND b.data_source = fnd_profile.value('OZF_DASH_BASELINE_SALES_SRC')
AND b.market_type = 'SHIP_TO'
AND b.market_id = p_ship_to_site_use_id
AND b.item_level = 'PRICING_ATTRIBUTE1'
AND b.item_id = p_ITEM_ID
GROUP BY b.period_type_id;
UPDATE ozf_cust_daily_facts
SET ptd_sales = l_mtd_sales
,qtd_sales = l_day_total + l_week_total + l_mth_total
,ytd_sales = l_ytd_sales
,lptd_sales = l_ly_day_total + l_ly_week_total
,lqtd_sales = l_ly_day_total + l_ly_week_total + l_ly_mth_total
,lysp_sales = l_ly_mth_sales
,lysq_sales = l_ly_qtr_sales
,ly_sales = l_ly_sales
,lytd_sales = l_ly_day_total + l_ly_week_total + l_ly_mth_total + l_ly_qtr_total
,mtd_basesales = l_mtd_bsales
,qtd_basesales = l_qtd_bsales
,ytd_basesales = l_ytd_bsales
WHERE cust_daily_fact_id = fact.cust_daily_fact_id;
SELECT b.end_date
FROM ozf_time_day a,
ozf_time_ent_period b
WHERE a.report_date = p_report_date
AND a.ent_period_id = b.ent_period_id;
SELECT inventory_item_id
FROM mtl_item_categories mtl,
eni_prod_denorm_hrchy_v eni
WHERE mtl.category_set_id = eni.category_set_id
AND mtl.category_id = eni.child_id
AND mtl.organization_id = fnd_profile.value('QP_ORGANIZATION_ID')
AND eni.parent_id = p_item_id
AND 'PRICING_ATTRIBUTE2' = p_item_type
UNION ALL
SELECT p_item_id inventory_item_id
FROM dual
WHERE 'PRICING_ATTRIBUTE1' = p_item_type
UNION ALL
SELECT p_item_id inventory_item_id
FROM dual
WHERE 'OTHERS' = p_item_type;
SELECT cust_daily_fact_id,
report_date,
cust_account_id,
ship_to_site_use_id,
bill_to_site_use_id,
product_attribute,
product_attr_value,
inventory_item_id
FROM ozf_cust_daily_facts
WHERE report_date = p_report_date;
SELECT a.line_id,
a.request_date,
a.promise_date,
a.schedule_ship_date,
DECODE( ozf_tp_util_queries.get_quota_unit,
'A', gl_currency_api.convert_amount_sql( b.transactional_curr_code,
fnd_profile.value('OZF_TP_COMMON_CURRENCY'),
a.request_date,
fnd_profile.value('OZF_CURR_CONVERSION_TYPE'),
(a.ordered_quantity*a.unit_selling_price)
)
, inv_convert.inv_um_convert(a.inventory_item_id,
NULL,
a.ordered_quantity,
a.order_quantity_uom,
fnd_profile.value('OZF_TP_COMMON_UOM') ,
NULL,
NULL)
) order_unit,
DECODE (ozf_tp_util_queries.get_quota_unit,
'A', b.transactional_curr_code
, a.order_quantity_uom) from_unit,
DECODE (ozf_tp_util_queries.get_quota_unit,
'A', fnd_profile.value('OZF_TP_COMMON_CURRENCY')
, fnd_profile.value('OZF_TP_COMMON_UOM') ) to_unit,
DECODE (ozf_tp_util_queries.get_quota_unit,
'A', (a.ordered_quantity*a.unit_selling_price)
, a.ordered_quantity ) unit
FROM oe_order_lines_all a,
oe_order_headers_all b
WHERE a.open_flag = 'Y'
AND a.cancelled_flag = 'N'
AND a.booked_flag = 'Y'
AND a.ship_to_org_id = p_ship_to_site_use_id
AND a.invoice_to_org_id = DECODE(p_bill_to_site_use_id,-9996, a.invoice_to_org_id,p_bill_to_site_use_id)
AND a.inventory_item_id = p_inventory_item_id
AND a.header_id = b.header_id ;
SELECT NVL(
SUM(
DECODE (ozf_tp_util_queries.get_quota_unit,
'A', gl_currency_api.convert_amount_sql( a.currency_code,
fnd_profile.value('OZF_TP_COMMON_CURRENCY'),
a.date_requested,
fnd_profile.value('OZF_CURR_CONVERSION_TYPE'),
(a.requested_quantity*a.unit_price))
, inv_convert.inv_um_convert(a.inventory_item_id,
NULL,
a.requested_quantity,
a.requested_quantity_uom,
fnd_profile.value('OZF_TP_COMMON_UOM') ,
NULL,
NULL)
)
) , 0 ) requested_quantity
FROM wsh_deliverables_v a
WHERE a.source_line_id = p_line_id
AND a.released_status = 'B';
UPDATE ozf_cust_daily_facts
SET past_due_order_qty = l_past_due_qty
,current_period_order_qty = l_current_order_qty
,backordered_qty = l_backordered_qty
,booked_for_future_qty = l_future_order_qty
WHERE cust_daily_fact_id = fact.cust_daily_fact_id;
SELECT NVL( DECODE(p_col, 'YEAR_QUOTA', SUM(f.current_year_target),
'PERIOD_QUOTA', SUM(f.current_period_target),
'QTR_QUOTA', SUM(f.current_qtr_target) ) ,0)
FROM ozf_cust_daily_facts f ,
hz_cust_accounts h
WHERE f.report_date = p_report_date
AND f.cust_account_id = h.cust_account_id
AND f.bill_to_site_use_id = NVL(p_bill_to_site_use_id, f.bill_to_site_use_id)
AND f.ship_to_site_use_id = NVL(p_site_use_id, f.ship_to_site_use_id)
AND h.party_id = p_party_id
AND f.product_attribute = 'OTHERS'
AND EXISTS ( SELECT 1
FROM ams_party_market_segments b,
jtf_terr_rsc_all a,
jtf_terr_rsc_access_all c
WHERE b.market_qualifier_type = 'TERRITORY'
AND b.market_qualifier_reference = a.terr_id
AND a.resource_id = p_resource_id
--AND a.primary_contact_flag = 'Y'
AND a.terr_rsc_id = c.terr_rsc_id
AND c.access_type = 'OFFER'
AND c.trans_access_code = 'PRIMARY_CONTACT'
AND b.site_use_code = 'SHIP_TO'
AND b.site_use_id = f.ship_to_site_use_id) ;
SELECT NVL(SUM(NVL(c1.target,0)),0)
FROM ozf_account_allocations b1
,ozf_time_allocations c1
,ozf_funds_all_b d1
WHERE b1.allocation_for = 'FUND'
AND b1.allocation_for_id = d1.fund_id
AND b1.site_use_id = p_site_use_id
AND b1.bill_to_site_use_id = p_bill_to_site_use_id
AND c1.allocation_for = 'CUST'
AND c1.allocation_for_id = b1.account_allocation_id
AND c1.period_type_id = p_period_type_id
AND c1.time_id = p_time_id
AND d1.fund_type = 'QUOTA'
AND d1.status_code <> 'CANCELLED'
AND d1.parent_fund_id IS NOT NULL
-- AND p_report_date BETWEEN d1.start_date_active AND d1.end_date_active
AND NOT EXISTS ( SELECT 1
FROM ozf_funds_all_b dd1
WHERE dd1.parent_fund_id = d1.fund_id);
SELECT cust_daily_fact_id,
report_date,
cust_account_id,
ship_to_site_use_id,
bill_to_site_use_id,
product_attribute,
product_attr_value
FROM ozf_cust_daily_facts
WHERE report_date = p_report_date;
SELECT SUM(
DECODE( prod.item_type, 'OTHERS', ozf_cust_facts_pvt.get_cust_target (
cust.site_use_id,
cust.bill_to_site_use_id,
time.period_type_id ,
time.time_id,
p_report_date)
, NVL(time.target,0) )
)
FROM ozf_account_allocations cust
,ozf_product_allocations prod
,ozf_time_allocations time
,ozf_funds_all_b quota
WHERE
-- Customer Filter
-- cust.site_use_code = 'SHIP_TO'
cust.allocation_for = 'FUND'
AND cust.allocation_for_id = quota.fund_id
AND cust.site_use_id = p_ship_to_site_use_id
AND cust.bill_to_site_use_id = p_bill_to_site_use_id
-- Product Filter
AND prod.allocation_for = 'CUST'
AND prod.allocation_for_id = cust.account_allocation_id
AND prod.item_type = p_item_type
AND prod.item_id = p_item_id
-- Time Filter
AND time.allocation_for = 'PROD'
AND time.allocation_for_id = prod.product_allocation_id
AND time.period_type_id = p_period_type_id
AND time.time_id = p_time_id
-- Cancelled Quota allocations must be ignored
AND quota.fund_type = 'QUOTA'
AND quota.status_code <> 'CANCELLED'
-- This date filter must be removed because users can have quota for
-- Q1, Q2, Q3, Q4 and in Q4 the total year quota
-- must be the sum of all these quotas
-- AND p_report_date BETWEEN quota.start_date_active AND quota.end_date_active
AND quota.parent_fund_id IS NOT NULL
-- Pick only quotas for leaf nodes.
-- This filter is not required since quotas are always generated for leaf nodes
AND NOT EXISTS ( SELECT 1
FROM ozf_funds_all_b dd
WHERE dd.parent_fund_id = quota.fund_id );
SELECT ent_period_id,
ent_qtr_id,
ent_year_id
FROM ozf_time_day
WHERE report_date = p_report_date;
SELECT ent_period_id
FROM ozf_time_ent_period
WHERE ent_qtr_id = p_qtr_id;
SELECT ent_qtr_id
FROM ozf_time_ent_qtr
WHERE ent_year_id = p_year_id;
SELECT ent_period_id
FROM ozf_time_ent_period
WHERE ent_year_id = p_year_id;
UPDATE ozf_cust_daily_facts
SET current_period_target = NVL(l_current_period_target,0)
,current_qtr_target = l_current_qtr_target
,current_year_target = l_current_year_target
WHERE cust_daily_fact_id = fact.cust_daily_fact_id;
PROCEDURE insert_kpi(
p_resource_id IN NUMBER,
p_report_date IN DATE,
p_current_period_target IN NUMBER,
p_current_qtr_target IN NUMBER,
p_current_year_target IN NUMBER,
p_current_period_sales IN NUMBER,
p_current_qtr_sales IN NUMBER,
p_current_year_sales IN NUMBER ) AS
CURSOR period_name_csr IS
SELECT ent_period_id,
ozf_time_api_pvt.get_period_name(ent_period_id, 32) period_name,
ent_qtr_id,
ozf_time_api_pvt.get_period_name(ent_qtr_id, 64) qtr_name,
ent_year_id,
ozf_time_api_pvt.get_period_name(ent_year_id, 128) year_name
FROM ozf_time_day
WHERE report_date = p_report_date;
ozf_utility_pvt.write_conc_log('Private API: ' || 'Insert_Kpi' || ' (-)') ;
DELETE FROM ozf_dashb_daily_kpi
WHERE report_date = p_report_date
AND resource_id = p_resource_id;
INSERT INTO ozf_dashb_daily_kpi(
dashb_daily_kpi_id,
report_date,
resource_id,
period_type_id,
time_id,
sequence_number,
kpi_name,
kpi_value )
VALUES (ozf_dashb_daily_kpi_s.nextval,
p_report_date,
p_resource_id,
l_period_type_id,
l_time_id,
l_kpi_rec_count,
l_kpi_name,
l_kpi_value );
ozf_utility_pvt.write_conc_log('Private API: ' || 'Insert_Kpi' || ' (+)');
END insert_kpi;
SELECT DISTINCT a.owner
FROM ozf_funds_all_b a
WHERE a.fund_type = 'QUOTA'
AND a.status_code <> 'CANCELLED' ;
SELECT SUM(b.target)
FROM ozf_account_allocations a,
ozf_time_allocations b
WHERE
b.allocation_for = 'CUST'
AND b.allocation_for_id = a.account_allocation_id
AND b.period_type_id = p_period_type_id
AND b.time_id = p_time_id
AND a.allocation_for = 'FUND'
AND NVL(a.account_status, 'X') <> 'D'
-- R12: Do not consider UnAllocated Rows
AND a.parent_party_id <> -9999
AND a.allocation_for_id IN ( -- Get leaf node quotas for this resource owns
SELECT aa.fund_id
FROM ozf_funds_all_b aa
WHERE aa.owner = p_resource_id
AND aa.fund_type = 'QUOTA'
AND aa.status_code <> 'CANCELLED'
AND NOT EXISTS ( SELECT 1
FROM ozf_funds_all_b bb
WHERE bb.parent_fund_id = aa.fund_id )
--
UNION ALL -- Get all leaf node quotas in the hierarchy of this resource
--
SELECT aa.fund_id
FROM ozf_funds_all_b aa
WHERE aa.fund_type = 'QUOTA'
AND aa.status_code <> 'CANCELLED'
CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
FROM ozf_funds_all_b bb
WHERE bb.owner = p_resource_id
AND bb.fund_type = 'QUOTA'
AND bb.status_code <> 'CANCELLED' )
);
SELECT SUM(fact.ptd_sales) MTD_SALES,
SUM(fact.qtd_sales) QTD_SALES,
SUM(fact.ytd_sales) YTD_SALES
FROM ozf_cust_daily_facts fact,
(
SELECT DISTINCT c.site_use_id
FROM jtf_terr_rsc_all b
,ams_party_market_segments c
WHERE b.resource_id = p_resource_id
AND b.primary_contact_flag = 'Y'
AND c.market_qualifier_type = 'TERRITORY'
AND c.market_qualifier_reference = b.terr_id
AND c.site_use_code = 'SHIP_TO'
) site
WHERE fact.report_date = p_report_date
AND fact.ship_to_site_use_id = site.site_use_id
AND fact.product_attribute <> 'OTHERS' ;
SELECT
SUM(fact.ptd_sales) MTD_SALES,
SUM(fact.qtd_sales) QTD_SALES,
SUM(fact.ytd_sales) YTD_SALES
FROM ozf_cust_daily_facts fact,
ozf_account_allocations site,
ozf_product_allocations prod
WHERE fact.report_date = p_report_date
AND fact.ship_to_site_use_id = site.site_use_id
AND fact.product_attribute <> 'OTHERS'
AND fact.product_attribute = prod.item_type
AND fact.product_attr_value = prod.item_id
AND prod.allocation_for = 'CUST'
AND prod.allocation_for_id = site.account_allocation_id
AND site.allocation_for = 'FUND'
AND NVL(site.account_status, 'X') <> 'D'
-- R12: Do not consider UnAllocated Rows
AND site.parent_party_id <> -9999
AND site.allocation_for_id in (
SELECT aa.fund_id
FROM ozf_funds_all_b aa
WHERE aa.owner = p_resource_id
AND aa.fund_type = 'QUOTA'
AND aa.status_code <> 'CANCELLED'
AND NOT EXISTS ( SELECT 1
FROM ozf_funds_all_b bb
WHERE bb.parent_fund_id = aa.fund_id )
--
UNION ALL -- Get all leaf node quotas in the hierarchy of this resource
--
SELECT aa.fund_id
FROM ozf_funds_all_b aa
WHERE aa.fund_type = 'QUOTA'
AND aa.status_code <> 'CANCELLED'
CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
FROM ozf_funds_all_b bb
WHERE bb.owner = p_resource_id
AND bb.fund_type = 'QUOTA'
AND bb.status_code <> 'CANCELLED' )
) ;
SELECT ent_period_id,
ent_qtr_id,
ent_year_id
FROM ozf_time_day
WHERE report_date = p_report_date;
SELECT ent_period_id
FROM ozf_time_ent_period
WHERE ent_qtr_id = p_qtr_id;
SELECT ent_qtr_id
FROM ozf_time_ent_qtr
WHERE ent_year_id = p_year_id;
SELECT ent_period_id
FROM ozf_time_ent_period
WHERE ent_year_id = p_year_id;
DELETE FROM ozf_dashb_daily_kpi
WHERE report_date = p_report_date;
insert_kpi ( res.owner,
p_report_date,
l_current_period_target,
l_current_qtr_target,
l_current_year_target,
l_current_period_sales,
l_current_qtr_sales,
l_current_year_sales);
PROCEDURE update_sales_info(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
p_report_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) AS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'update_sales_info';
SELECT a.fund_id
FROM ozf_funds_all_b a
WHERE a.status_code = 'ACTIVE'
AND a.fund_type = 'QUOTA'
-- and a.fund_id in ( 10746 , 10745)
AND NOT EXISTS ( SELECT 1
FROM ozf_funds_all_b b
WHERE b.parent_fund_id = a.fund_id);
SELECT account_allocation_id,
site_use_id
FROM ozf_account_allocations
WHERE allocation_for = 'FUND'
AND allocation_for_id = p_fund_id
AND cust_account_id <> -9999;
SELECT product_allocation_id,
item_type,
item_id
FROM ozf_product_allocations
WHERE allocation_for = 'CUST'
AND allocation_for_id = p_account_allocation_id
ORDER BY item_id DESC ;
SELECT time_allocation_id,
time_id,
period_type_id
FROM ozf_time_allocations
WHERE allocation_for = 'PROD'
AND allocation_for_id = p_product_allocation_id;
SELECT ent_period_id,
ent_qtr_id
FROM ozf_time_day
WHERE report_date = p_report_date;
SELECT NVL(SUM(sales),0)
FROM ozf_order_sales_v
WHERE time_id = p_time_id
AND ship_to_site_use_id = p_site_use_id
AND inventory_item_id = DECODE(p_inventory_item_id,-9999,inventory_item_id,p_inventory_item_id);
SELECT NVL(SUM(sales),0)
FROM ozf_time_rpt_struct a,
ozf_order_sales_v b
WHERE a.report_date = p_report_date
AND BITAND(a.record_type_id, p_record_type_id) = a.record_type_id
AND a.time_id = b.time_id
AND b.ship_to_site_use_id = p_site_use_id
AND b.inventory_item_id = DECODE(p_inventory_item_id,-9999, b.inventory_item_id,p_inventory_item_id);
SELECT inventory_item_id
FROM mtl_item_categories mtl,
eni_prod_denorm_hrchy_v eni
WHERE mtl.category_set_id = eni.category_set_id
AND mtl.category_id = eni.child_id
AND mtl.organization_id = fnd_profile.value('QP_ORGANIZATION_ID')
AND eni.parent_id = p_item_id
AND 'PRICING_ATTRIBUTE2' = p_item_type
UNION ALL
SELECT p_item_id inventory_item_id
FROM dual
WHERE 'PRICING_ATTRIBUTE1' = p_item_type
UNION ALL
SELECT p_item_id inventory_item_id
FROM dual
WHERE 'OTHERS' = p_item_type;
SELECT NVL(SUM(c.lysp_sales),0)
FROM ozf_account_allocations a,
ozf_product_allocations b,
ozf_time_allocations c
WHERE a.account_allocation_id = p_account_allocation_id
AND b.allocation_for = 'CUST'
AND b.allocation_for_id = a.account_allocation_id
AND b.item_type <> 'OTHERS'
AND c.allocation_for = 'PROD'
AND c.allocation_for_id = b.product_allocation_id
AND c.time_id = p_time_id;
UPDATE ozf_time_allocations
SET lysp_sales = l_sales
WHERE time_allocation_id = time.time_allocation_id;
UPDATE ozf_product_allocations
SET lysp_sales = l_product_sales
WHERE product_allocation_id = prod.product_allocation_id;
UPDATE ozf_account_allocations
SET lysp_sales = l_acct_sales
WHERE account_allocation_id = acct.account_allocation_id;
UPDATE ozf_time_allocations c
SET lysp_sales = ( SELECT sum(b.lysp_sales)
FROM ozf_product_allocations a,
ozf_time_allocations b
WHERE a.allocation_for = 'CUST'
AND a.allocation_for_id = acct.account_allocation_id
AND b.allocation_for = 'PROD'
AND b.allocation_for_id = a.product_allocation_id
AND b.time_id = c.time_id )
WHERE c.allocation_for = 'CUST'
AND c.allocation_for_id = acct.account_allocation_id ;
UPDATE ozf_funds_all_b
SET utilized_amt = l_quota_sales
WHERE fund_id = quota.fund_id;
END update_sales_info;
PROCEDURE update_quota_sales_info(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
p_report_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) AS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'update_quota_sales_info';
SELECT a.fund_id,
start_date_active,
end_date_active
FROM ozf_funds_all_b a
WHERE fund_type = 'QUOTA'
AND a.status_code = 'ACTIVE'
AND NOT EXISTS ( SELECT 1
FROM ozf_funds_all_b b
WHERE b.parent_fund_id = a.fund_id );
SELECT a.fund_id,
start_date_active,
end_date_active
FROM ozf_funds_all_b a
WHERE fund_type = 'QUOTA'
AND status_code = 'ACTIVE'
AND EXISTS ( SELECT 1
FROM ozf_funds_all_b b
WHERE b.parent_fund_id = a.fund_id );
SELECT a.fund_id,
NVL(a.utilized_amt,0) utilized_amt
FROM ozf_funds_all_b a
WHERE NOT EXISTS ( SELECT 'x'
FROM ozf_funds_all_b b
WHERE b.parent_fund_id = a.fund_id
AND b.status_code = 'ACTIVE' )
AND a.status_code = 'ACTIVE'
CONNECT BY PRIOR a.fund_id = a.parent_fund_id
START WITH a.parent_fund_id = p_fund_id;
SELECT NVL(SUM(b.sales),0) tot_sales
FROM ozf_time_rpt_struct a,
ozf_order_sales_v b,
ozf_account_allocations c
WHERE c.allocation_for = 'FUND'
AND c.allocation_for_id = p_fund_id
AND b.ship_to_site_use_id = c.site_use_id
AND b.time_id = a.time_id
AND a.report_date = p_as_of_date
AND BITAND(a.record_type_id, 119) = a.record_type_id ;
UPDATE ozf_funds_all_b
SET utilized_amt = (l_xtd_end_date - l_xtd_start_date )
WHERE fund_id = quota.fund_id ;
UPDATE ozf_funds_all_b
SET utilized_amt = l_parent_quota_sales
WHERE fund_id = quota.fund_id;
END update_quota_sales_info;
DELETE FROM OZF_RES_CUST_PROD_FACTS
WHERE report_date = p_report_date;
INSERT INTO OZF_RES_CUST_PROD_FACTS
(SELECT OZF.OZF_RES_CUST_PROD_FACTS_S.nextval,
resource_id,
report_date,
fact_row_for,
party_id,
cust_account_id,
bill_to_site_use_id,
ship_to_site_use_id,
product_attribute,
product_attr_value,
ptd_sales,
qtd_sales,
ytd_sales,
lptd_sales,
lqtd_sales,
lytd_sales,
lysp_sales,
lysq_sales,
ly_sales,
period_quota,
qtr_quota,
year_quota,
mtd_basesales,
qtd_basesales,
ytd_basesales,
outstanding_orders,
current_orders,
back_orders,
future_orders,
tot_ship_psbl_peroid,
ytd_fund_utilized,
ytd_fund_earned,
ytd_fund_paid,
qtd_fund_utilized,
qtd_fund_earned,
qtd_fund_paid,
mtd_fund_utilized,
mtd_fund_earned,
mtd_fund_paid,
fund_unpaid,
open_claims,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
FROM
----FOR PARTY ---
(SELECT
kpi.resource_id resource_id,
c.report_date report_date,
'PARTY' FACT_ROW_FOR,
x.party_id party_id,
NULL CUST_ACCOUNT_ID,
NULL BILL_TO_SITE_USE_ID,
NULL SHIP_TO_SITE_USE_ID,
NULL PRODUCT_ATTRIBUTE,
NULL PRODUCT_ATTR_VALUE,
SUM(ptd_sales) PTD_SALES,
SUM(qtd_sales) QTD_SALES,
SUM(ytd_sales) YTD_SALES,
SUM(lysp_sales) LPTD_SALES,
SUM(lqtd_sales) LQTD_SALES,
SUM(lytd_sales) LYTD_SALES,
SUM(lysp_sales) LYSP_SALES,
SUM(lysq_sales) LYSQ_SALES,
SUM(ly_sales) LY_SALES,
0 PERIOD_QUOTA,
0 QTR_QUOTA,
0 YEAR_QUOTA,
SUM(mtd_basesales) MTD_BASESALES,
SUM(qtd_basesales) QTD_BASESALES,
SUM(ytd_basesales) YTD_BASESALES,
SUM(past_due_order_qty) OUTSTANDING_ORDERS,
SUM(current_period_order_qty) CURRENT_ORDERS,
SUM(backordered_qty) BACK_ORDERS,
SUM(booked_for_future_qty) FUTURE_ORDERS,
SUM(ptd_sales)+SUM(past_due_order_qty)+SUM(current_period_order_qty)+SUM(backordered_qty) TOT_SHIP_PSBL_PEROID,
SUM(YTD_FUND_UTILIZED) YTD_FUND_UTILIZED,
SUM(YTD_FUND_EARNED) YTD_FUND_EARNED,
SUM(YTD_FUND_PAID) YTD_FUND_PAID,
SUM(QTD_FUND_UTILIZED) QTD_FUND_UTILIZED,
SUM(QTD_FUND_EARNED) QTD_FUND_EARNED,
SUM(QTD_FUND_PAID) QTD_FUND_PAID,
SUM(MTD_FUND_UTILIZED) MTD_FUND_UTILIZED,
SUM(MTD_FUND_EARNED) MTD_FUND_EARNED,
SUM(MTD_FUND_PAID) MTD_FUND_PAID,
SUM(FUND_UNPAID) FUND_UNPAID,
SUM(OPEN_CLAIMS) OPEN_CLAIMS,
sysdate CREATION_DATE,
-1 CREATED_BY,
sysdate LAST_UPDATE_DATE,
-1 LAST_UPDATED_BY,
-1 LAST_UPDATE_LOGIN
FROM ozf_cust_daily_facts c,
hz_cust_accounts x,
ozf_dashb_daily_kpi kpi,
(SELECT DISTINCT a.owner
FROM ozf_funds_all_b a
WHERE a.fund_type = 'QUOTA'
AND a.status_code <> 'CANCELLED') fund
WHERE c.cust_account_id = x.cust_account_id
AND c.product_attribute <> 'OTHERS'
AND kpi.resource_id = fund.owner
AND kpi.sequence_number = 1
AND Kpi.report_date = c.report_date
AND EXISTS (
SELECT 1
FROM ozf_account_allocations acct,
ozf_product_allocations prod
WHERE acct.site_use_id = c.ship_to_site_use_id
AND prod.item_type = c.product_attribute
AND prod.item_id = c.product_attr_value
AND prod.allocation_for = 'CUST'
AND prod.allocation_for_id = acct.account_allocation_id
AND acct.allocation_for = 'FUND'
AND acct.allocation_for_id in
(SELECT aa.fund_id
FROM ozf_funds_all_b aa
WHERE aa.owner = kpi.resource_id
AND aa.fund_type = 'QUOTA'
AND aa.status_code <> 'CANCELLED'
AND NOT EXISTS ( SELECT 1
FROM ozf_funds_all_b bb
WHERE bb.parent_fund_id = aa.fund_id )
UNION ALL
SELECT aa.fund_id
FROM ozf_funds_all_b aa
WHERE aa.fund_type = 'QUOTA'
AND aa.status_code <> 'CANCELLED'
CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
FROM ozf_funds_all_b bb
WHERE bb.owner = kpi.resource_id
AND bb.fund_type = 'QUOTA'
AND bb.status_code <> 'CANCELLED')
))
AND c.report_date = p_report_date
AND fund.owner = kpi.resource_id
GROUP BY
kpi.resource_id,
c.report_date,
'PARTY',
x.party_id
UNION ALL
--- FOR BILL_TO----
SELECT
kpi.resource_id resource_id,
c.report_date report_date,
'BILL_TO' FACT_ROW_FOR,
x.party_id party_id,
c.CUST_ACCOUNT_ID CUST_ACCOUNT_ID,
c.bill_to_site_use_id BILL_TO_SITE_USE_ID,
0 SHIP_TO_SITE_USE_ID,
NULL PRODUCT_ATTRIBUTE,
NULL PRODUCT_ATTR_VALUE,
SUM(ptd_sales) PTD_SALES,
SUM(qtd_sales) QTD_SALES,
SUM(ytd_sales) YTD_SALES,
SUM(lysp_sales) LPTD_SALES,
SUM(lqtd_sales) LQTD_SALES,
SUM(lytd_sales) LYTD_SALES,
SUM(lysp_sales) LYSP_SALES,
SUM(lysq_sales) LYSQ_SALES,
SUM(ly_sales) LY_SALES,
0 PERIOD_QUOTA,
0 QTR_QUOTA,
0 YEAR_QUOTA,
SUM(mtd_basesales) MTD_BASESALES,
SUM(qtd_basesales) QTD_BASESALES,
SUM(ytd_basesales) YTD_BASESALES,
SUM(past_due_order_qty) OUTSTANDING_ORDERS,
SUM(current_period_order_qty) CURRENT_ORDERS,
SUM(backordered_qty) BACK_ORDERS,
SUM(booked_for_future_qty) FUTURE_ORDERS,
SUM(ptd_sales)+SUM(past_due_order_qty)+SUM(current_period_order_qty)+SUM(backordered_qty) TOT_SHIP_PSBL_PEROID,
SUM(YTD_FUND_UTILIZED) YTD_FUND_UTILIZED,
SUM(YTD_FUND_EARNED) YTD_FUND_EARNED,
SUM(YTD_FUND_PAID) YTD_FUND_PAID,
SUM(QTD_FUND_UTILIZED) QTD_FUND_UTILIZED,
SUM(QTD_FUND_EARNED) QTD_FUND_EARNED,
SUM(QTD_FUND_PAID) QTD_FUND_PAID,
SUM(MTD_FUND_UTILIZED) MTD_FUND_UTILIZED,
SUM(MTD_FUND_EARNED) MTD_FUND_EARNED,
SUM(MTD_FUND_PAID) MTD_FUND_PAID,
SUM(FUND_UNPAID) FUND_UNPAID,
SUM(OPEN_CLAIMS) OPEN_CLAIMS,
sysdate CREATION_DATE,
-1 CREATED_BY,
sysdate LAST_UPDATE_DATE,
-1 LAST_UPDATED_BY,
-1 LAST_UPDATE_LOGIN
FROM ozf_cust_daily_facts c,
hz_cust_accounts x,
ozf_dashb_daily_kpi kpi,
(SELECT DISTINCT a.owner
FROM ozf_funds_all_b a
WHERE a.fund_type = 'QUOTA'
AND a.status_code <> 'CANCELLED') fund
WHERE c.cust_account_id = x.cust_account_id
AND c.product_attribute <> 'OTHERS'
AND kpi.resource_id = fund.owner
AND kpi.sequence_number = 1
AND Kpi.report_date = c.report_date
AND EXISTS (
SELECT 1
FROM ozf_account_allocations acct,
ozf_product_allocations prod
WHERE acct.site_use_id = c.ship_to_site_use_id
AND prod.item_type = c.product_attribute
AND prod.item_id = c.product_attr_value
AND prod.allocation_for = 'CUST'
AND prod.allocation_for_id = acct.account_allocation_id
AND acct.allocation_for = 'FUND'
AND acct.allocation_for_id in
(SELECT aa.fund_id
FROM ozf_funds_all_b aa
WHERE aa.owner = kpi.resource_id
AND aa.fund_type = 'QUOTA'
AND aa.status_code <> 'CANCELLED'
AND NOT EXISTS ( SELECT 1
FROM ozf_funds_all_b bb
WHERE bb.parent_fund_id = aa.fund_id )
UNION ALL
SELECT aa.fund_id
FROM ozf_funds_all_b aa
WHERE aa.fund_type = 'QUOTA'
AND aa.status_code <> 'CANCELLED'
CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
FROM ozf_funds_all_b bb
WHERE bb.owner = kpi.resource_id
AND bb.fund_type = 'QUOTA'
AND bb.status_code <> 'CANCELLED')
))
AND c.report_date = p_report_date
AND fund.owner = kpi.resource_id
GROUP BY
kpi.resource_id,
c.report_date,
'BILL_TO',
x.party_id,
c.CUST_ACCOUNT_ID,
c.bill_to_site_use_id
UNION ALL
--- FOR SHIP_TO----
SELECT
kpi.resource_id resource_id,
c.report_date report_date,
'SHIP_TO' FACT_ROW_FOR,
x.party_id party_id,
c.CUST_ACCOUNT_ID CUST_ACCOUNT_ID,
c.bill_to_site_use_id BILL_TO_SITE_USE_ID,
c.ship_to_site_use_id SHIP_TO_SITE_USE_ID,
NULL PRODUCT_ATTRIBUTE,
NULL PRODUCT_ATTR_VALUE,
SUM(ptd_sales) PTD_SALES,
SUM(qtd_sales) QTD_SALES,
SUM(ytd_sales) YTD_SALES,
SUM(lysp_sales) LPTD_SALES,
SUM(lqtd_sales) LQTD_SALES,
SUM(lytd_sales) LYTD_SALES,
SUM(lysp_sales) LYSP_SALES,
SUM(lysq_sales) LYSQ_SALES,
SUM(ly_sales) LY_SALES,
0 PERIOD_QUOTA,
0 QTR_QUOTA,
0 YEAR_QUOTA,
SUM(mtd_basesales) MTD_BASESALES,
SUM(qtd_basesales) QTD_BASESALES,
SUM(ytd_basesales) YTD_BASESALES,
SUM(past_due_order_qty) OUTSTANDING_ORDERS,
SUM(current_period_order_qty) CURRENT_ORDERS,
SUM(backordered_qty) BACK_ORDERS,
SUM(booked_for_future_qty) FUTURE_ORDERS,
SUM(ptd_sales)+SUM(past_due_order_qty)+SUM(current_period_order_qty)+SUM(backordered_qty) TOT_SHIP_PSBL_PEROID,
SUM(YTD_FUND_UTILIZED) YTD_FUND_UTILIZED,
SUM(YTD_FUND_EARNED) YTD_FUND_EARNED,
SUM(YTD_FUND_PAID) YTD_FUND_PAID,
SUM(QTD_FUND_UTILIZED) QTD_FUND_UTILIZED,
SUM(QTD_FUND_EARNED) QTD_FUND_EARNED,
SUM(QTD_FUND_PAID) QTD_FUND_PAID,
SUM(MTD_FUND_UTILIZED) MTD_FUND_UTILIZED,
SUM(MTD_FUND_EARNED) MTD_FUND_EARNED,
SUM(MTD_FUND_PAID) MTD_FUND_PAID,
SUM(FUND_UNPAID) FUND_UNPAID,
SUM(OPEN_CLAIMS) OPEN_CLAIMS,
sysdate CREATION_DATE,
-1 CREATED_BY,
sysdate LAST_UPDATE_DATE,
-1 LAST_UPDATED_BY,
-1 LAST_UPDATE_LOGIN
FROM ozf_cust_daily_facts c,
hz_cust_accounts x,
ozf_dashb_daily_kpi kpi,
(SELECT DISTINCT a.owner
FROM ozf_funds_all_b a
WHERE a.fund_type = 'QUOTA'
AND a.status_code <> 'CANCELLED') fund
WHERE c.cust_account_id = x.cust_account_id
AND c.product_attribute <> 'OTHERS'
AND kpi.resource_id = fund.owner
AND kpi.sequence_number = 1
AND Kpi.report_date = c.report_date
AND EXISTS (
SELECT 1
FROM ozf_account_allocations acct,
ozf_product_allocations prod
WHERE acct.site_use_id = c.ship_to_site_use_id
AND prod.item_type = c.product_attribute
AND prod.item_id = c.product_attr_value
AND prod.allocation_for = 'CUST'
AND prod.allocation_for_id = acct.account_allocation_id
AND acct.allocation_for = 'FUND'
AND acct.allocation_for_id in
(SELECT aa.fund_id
FROM ozf_funds_all_b aa
WHERE aa.owner = kpi.resource_id
AND aa.fund_type = 'QUOTA'
AND aa.status_code <> 'CANCELLED'
AND NOT EXISTS ( SELECT 1
FROM ozf_funds_all_b bb
WHERE bb.parent_fund_id = aa.fund_id )
UNION ALL
SELECT aa.fund_id
FROM ozf_funds_all_b aa
WHERE aa.fund_type = 'QUOTA'
AND aa.status_code <> 'CANCELLED'
CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
FROM ozf_funds_all_b bb
WHERE bb.owner = kpi.resource_id
AND bb.fund_type = 'QUOTA'
AND bb.status_code <> 'CANCELLED')
))
AND c.report_date = p_report_date
AND fund.owner = kpi.resource_id
GROUP BY
kpi.resource_id,
c.report_date,
'SHIP_TO',
x.party_id,
c.CUST_ACCOUNT_ID,
c.bill_to_site_use_id,
c.ship_to_site_use_id
UNION ALL
--- FOR PRODUCT----
SELECT
kpi.resource_id resource_id,
c.report_date report_date,
'PRODUCT' FACT_ROW_FOR,
0 party_id,
0 CUST_ACCOUNT_ID,
0 BILL_TO_SITE_USE_ID,
0 SHIP_TO_SITE_USE_ID,
c.product_attribute PRODUCT_ATTRIBUTE,
c.product_attr_value PRODUCT_ATTR_VALUE,
SUM(ptd_sales) PTD_SALES,
SUM(qtd_sales) QTD_SALES,
SUM(ytd_sales) YTD_SALES,
SUM(lysp_sales) LPTD_SALES,
SUM(lqtd_sales) LQTD_SALES,
SUM(lytd_sales) LYTD_SALES,
SUM(lysp_sales) LYSP_SALES,
SUM(lysq_sales) LYSQ_SALES,
SUM(ly_sales) LY_SALES,
SUM(current_period_target) PERIOD_QUOTA,
SUM(current_qtr_target) QTR_QUOTA,
SUM(current_year_target) YEAR_QUOTA,
SUM(mtd_basesales) MTD_BASESALES,
SUM(qtd_basesales) QTD_BASESALES,
SUM(ytd_basesales) YTD_BASESALES,
SUM(past_due_order_qty) OUTSTANDING_ORDERS,
SUM(current_period_order_qty) CURRENT_ORDERS,
SUM(backordered_qty) BACK_ORDERS,
SUM(booked_for_future_qty) FUTURE_ORDERS,
SUM(ptd_sales)+SUM(past_due_order_qty)+SUM(current_period_order_qty)+SUM(backordered_qty) TOT_SHIP_PSBL_PEROID,
SUM(YTD_FUND_UTILIZED) YTD_FUND_UTILIZED,
SUM(YTD_FUND_EARNED) YTD_FUND_EARNED,
SUM(YTD_FUND_PAID) YTD_FUND_PAID,
SUM(QTD_FUND_UTILIZED) QTD_FUND_UTILIZED,
SUM(QTD_FUND_EARNED) QTD_FUND_EARNED,
SUM(QTD_FUND_PAID) QTD_FUND_PAID,
SUM(MTD_FUND_UTILIZED) MTD_FUND_UTILIZED,
SUM(MTD_FUND_EARNED) MTD_FUND_EARNED,
SUM(MTD_FUND_PAID) MTD_FUND_PAID,
SUM(FUND_UNPAID) FUND_UNPAID,
SUM(OPEN_CLAIMS) OPEN_CLAIMS,
sysdate CREATION_DATE,
-1 CREATED_BY,
sysdate LAST_UPDATE_DATE,
-1 LAST_UPDATED_BY,
-1 LAST_UPDATE_LOGIN
FROM ozf_cust_daily_facts c ,
ozf_dashb_daily_kpi kpi,
(SELECT DISTINCT a.owner
FROM ozf_funds_all_b a
WHERE a.fund_type = 'QUOTA'
AND a.status_code <> 'CANCELLED') fund
WHERE
kpi.sequence_number = 1
AND kpi.report_date = c.report_date
AND c.product_attribute <> 'OTHERS'
AND EXISTS (
SELECT 1
FROM ozf_account_allocations acct,
ozf_product_allocations prod
WHERE acct.site_use_id = c.ship_to_site_use_id
AND prod.item_type = c.product_attribute
AND prod.item_id = c.product_attr_value
AND prod.allocation_for = 'CUST'
AND prod.allocation_for_id = acct.account_allocation_id
AND acct.allocation_for = 'FUND'
AND acct.allocation_for_id in
(SELECT aa.fund_id
FROM ozf_funds_all_b aa
WHERE aa.owner = kpi.resource_id
AND aa.fund_type = 'QUOTA'
AND aa.status_code <> 'CANCELLED'
AND NOT EXISTS ( SELECT 1
FROM ozf_funds_all_b bb
WHERE bb.parent_fund_id = aa.fund_id )
UNION ALL
SELECT aa.fund_id
FROM ozf_funds_all_b aa
WHERE aa.fund_type = 'QUOTA'
AND aa.status_code <> 'CANCELLED'
CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
FROM ozf_funds_all_b bb
WHERE bb.owner = kpi.resource_id
AND bb.fund_type = 'QUOTA'
AND bb.status_code <> 'CANCELLED')
))
AND c.report_date = p_report_date
AND fund.owner = kpi.resource_id
GROUP BY
kpi.resource_id,
c.report_date,
'PRODUCT',
c.product_attribute,
c.product_attr_value)
);
UPDATE ozf_res_cust_prod_facts outer
Set (PERIOD_QUOTA, QTR_QUOTA, YEAR_QUOTA)
=
(
SELECT
ozf_cust_facts_pvt.get_cust_target( outer.party_id, NULL, NULL,
'PERIOD_QUOTA', 0,
outer.report_date,outer.resource_id
) PERIOD_QUOTA,
ozf_cust_facts_pvt.get_cust_target( outer.party_id, NULL, NULL,
'QTR_QUOTA', 0,
outer.report_date,outer.resource_id
) QTR_QUOTA,
ozf_cust_facts_pvt.get_cust_target( outer.party_id, NULL, NULL,
'YEAR_QUOTA', 0,
outer.report_date,outer.resource_id
) YEAR_QUOTA
FROM dual
)
WHERE outer.fact_row_for = 'PARTY'
AND outer.report_date = p_report_date;
UPDATE ozf_res_cust_prod_facts outer
Set (PERIOD_QUOTA, QTR_QUOTA, YEAR_QUOTA)
=
(
SELECT
ozf_cust_facts_pvt.get_cust_target( outer.party_id, outer.bill_to_site_use_id,
NULL, 'PERIOD_QUOTA', 0,
outer.report_date,outer.resource_id
) PERIOD_QUOTA,
ozf_cust_facts_pvt.get_cust_target( outer.party_id, outer.bill_to_site_use_id,
NULL, 'QTR_QUOTA', 0,
outer.report_date,outer.resource_id
) QTR_QUOTA,
ozf_cust_facts_pvt.get_cust_target( outer.party_id, outer.bill_to_site_use_id,
NULL, 'YEAR_QUOTA', 0,
outer.report_date,outer.resource_id
) YEAR_QUOTA
FROM dual
)
WHERE outer.fact_row_for = 'BILL_TO'
AND outer.report_date = p_report_date;
UPDATE ozf_res_cust_prod_facts outer
Set (PERIOD_QUOTA, QTR_QUOTA, YEAR_QUOTA)
=
(
SELECT
ozf_cust_facts_pvt.get_cust_target( outer.party_id, outer.bill_to_site_use_id,
outer.ship_to_site_use_id, 'PERIOD_QUOTA', 0,
outer.report_date,outer.resource_id
) PERIOD_QUOTA,
ozf_cust_facts_pvt.get_cust_target( outer.party_id, outer.bill_to_site_use_id,
outer.ship_to_site_use_id, 'QTR_QUOTA', 0,
outer.report_date,outer.resource_id
) QTR_QUOTA,
ozf_cust_facts_pvt.get_cust_target( outer.party_id, outer.bill_to_site_use_id,
outer.ship_to_site_use_id, 'YEAR_QUOTA', 0,
outer.report_date,outer.resource_id
) YEAR_QUOTA
FROM dual
)
WHERE outer.fact_row_for = 'SHIP_TO'
AND outer.report_date = p_report_date;
UPDATE ozf_res_cust_prod_facts outer
Set (YTD_FUND_utilized, YTD_FUND_earned, YTD_FUND_paid)
=
(
SELECT NVL(SUM(utilized_amt),0) tot_utilized,
NVL(SUM(earned_amt),0) tot_earned,
NVL(SUM(paid_amt),0) tot_paid
FROM ozf_time_day a,
ozf_cust_fund_summary_mv b,
ozf_res_cust_prod c
WHERE c.resource_id = outer.resource_id
AND a.report_date = outer.report_date
AND a.ent_year_id = b.time_id
AND b.status_code = 'ACTIVE'
AND b.party_id = outer.party_id
AND b.party_id = c.party_id
AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
AND
(
(
b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
AND b.product_id = c.product_attr_value
)
OR
(
b.product_level_type IS NULL
AND b.product_id IS NULL
AND EXISTS (SELECT 'X'
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id)
)
)
)
WHERE outer.fact_row_for = 'PARTY'
AND outer.report_date = p_report_date ;
Update ozf_res_cust_prod_facts outer
Set (YTD_FUND_utilized, YTD_FUND_earned, YTD_FUND_paid)
=
(
SELECT NVL(SUM(utilized_amt),0) tot_utilized,
NVL(SUM(earned_amt),0) tot_earned,
NVL(SUM(paid_amt),0) tot_paid
FROM ozf_time_day a,
ozf_cust_fund_summary_mv b,
ozf_res_cust_prod c
WHERE c.resource_id = outer.resource_id
AND a.report_date = outer.report_date
AND a.ent_year_id = b.time_id
AND b.status_code = 'ACTIVE'
AND b.party_id = outer.party_id
AND b.party_id = c.party_id
AND b.bill_to_site_use_id = outer.bill_to_site_use_id
AND b.bill_to_site_use_id = c.bill_to_site_use_id
AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
AND
(
(
b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
AND b.product_id = c.product_attr_value
)
OR
(
b.product_level_type IS NULL
AND b.product_id IS NULL
AND EXISTS (SELECT 'X'
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id)
)
)
)
WHERE outer.fact_row_for = 'BILL_TO'
AND outer.report_date = p_report_date ;
Update ozf_res_cust_prod_facts outer
Set (YTD_FUND_utilized, YTD_FUND_earned, YTD_FUND_paid)
=
(
SELECT NVL(SUM(utilized_amt),0) tot_utilized,
NVL(SUM(earned_amt),0) tot_earned,
NVL(SUM(paid_amt),0) tot_paid
FROM ozf_time_day a,
ozf_cust_fund_summary_mv b,
ozf_res_cust_prod c
WHERE c.resource_id = outer.resource_id
AND a.report_date = outer.report_date
AND a.ent_year_id = b.time_id
AND b.status_code = 'ACTIVE'
AND b.party_id = outer.party_id
AND b.party_id = c.party_id
AND b.bill_to_site_use_id = outer.bill_to_site_use_id
AND b.bill_to_site_use_id = c.bill_to_site_use_id
AND b.ship_to_site_use_id = outer.ship_to_site_use_id
AND b.ship_to_site_use_id = c.ship_to_site_use_id
AND (
( b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
AND b.product_id = c.product_attr_value
)
or
( b.product_level_type IS NULL
AND b.product_id IS NULL
AND EXISTS (SELECT 'X'
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id)
)
)
)
WHERE outer.fact_row_for = 'SHIP_TO'
AND outer.report_date = p_report_date ;
Update ozf_res_cust_prod_facts outer
Set (QTD_FUND_utilized, QTD_FUND_earned, QTD_FUND_paid)
=
(
SELECT NVL(SUM(utilized_amt),0) tot_utilized,
NVL(SUM(earned_amt),0) tot_earned,
NVL(SUM(paid_amt),0) tot_paid
FROM ozf_time_day a,
ozf_cust_fund_summary_mv b,
ozf_res_cust_prod c
WHERE c.resource_id = outer.resource_id
AND a.report_date = outer.report_date
AND a.ent_qtr_id = b.time_id
AND b.status_code = 'ACTIVE'
AND b.party_id = outer.party_id
AND b.party_id = c.party_id
AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
AND
(
(
b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
AND b.product_id = c.product_attr_value
)
OR
(
b.product_level_type IS NULL
AND b.product_id IS NULL
AND EXISTS (SELECT 'X'
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id)
)
)
)
WHERE outer.fact_row_for = 'PARTY'
AND outer.report_date = p_report_date ;
Update ozf_res_cust_prod_facts outer
Set (QTD_FUND_utilized, QTD_FUND_earned, QTD_FUND_paid)
=
(
SELECT NVL(SUM(utilized_amt),0) tot_utilized,
NVL(SUM(earned_amt),0) tot_earned,
NVL(SUM(paid_amt),0) tot_paid
FROM ozf_time_day a,
ozf_cust_fund_summary_mv b,
ozf_res_cust_prod c
WHERE c.resource_id = outer.resource_id
AND a.report_date = outer.report_date
AND a.ent_qtr_id = b.time_id
AND b.status_code = 'ACTIVE'
AND b.party_id = outer.party_id
AND b.party_id = c.party_id
AND b.bill_to_site_use_id = outer.bill_to_site_use_id
AND b.bill_to_site_use_id = c.bill_to_site_use_id
AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
AND
(
(
b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
AND b.product_id = c.product_attr_value
)
OR
(
b.product_level_type IS NULL
AND b.product_id IS NULL
AND EXISTS (SELECT 'X'
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id)
)
)
)
WHERE outer.fact_row_for = 'BILL_TO'
AND outer.report_date = p_report_date ;
Update ozf_res_cust_prod_facts outer
Set (QTD_FUND_utilized, QTD_FUND_earned, QTD_FUND_paid)
=
(
SELECT NVL(SUM(utilized_amt),0) tot_utilized,
NVL(SUM(earned_amt),0) tot_earned,
NVL(SUM(paid_amt),0) tot_paid
FROM ozf_time_day a,
ozf_cust_fund_summary_mv b,
ozf_res_cust_prod c
WHERE c.resource_id = outer.resource_id
AND a.report_date = outer.report_date
AND a.ent_qtr_id = b.time_id
AND b.status_code = 'ACTIVE'
AND b.party_id = outer.party_id
AND b.party_id = c.party_id
AND b.bill_to_site_use_id = outer.bill_to_site_use_id
AND b.bill_to_site_use_id = c.bill_to_site_use_id
AND b.ship_to_site_use_id = outer.ship_to_site_use_id
AND b.ship_to_site_use_id = c.ship_to_site_use_id
AND
(
( b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
AND b.product_id = c.product_attr_value
)
OR
( b.product_level_type IS NULL
AND b.product_id IS NULL
AND EXISTS (SELECT count(object_id)
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id)
)
)
)
WHERE outer.fact_row_for = 'SHIP_TO'
AND outer.report_date = p_report_date;
Update ozf_res_cust_prod_facts outer
Set (MTD_FUND_utilized, MTD_FUND_earned, MTD_FUND_paid)
=
(
SELECT NVL(SUM(utilized_amt),0) tot_utilized,
NVL(SUM(earned_amt),0) tot_earned,
NVL(SUM(paid_amt),0) tot_paid
FROM ozf_time_day a,
ozf_cust_fund_summary_mv b,
ozf_res_cust_prod c
WHERE c.resource_id = outer.resource_id
AND a.report_date = outer.report_date
AND a.ent_period_id = b.time_id
AND b.status_code = 'ACTIVE'
AND b.party_id = outer.party_id
AND b.party_id = c.party_id
AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
AND
(
(
b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
AND b.product_id = c.product_attr_value
)
OR
(
b.product_level_type IS NULL
AND b.product_id IS NULL
AND EXISTS (SELECT 'X'
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id)
)
)
)
WHERE outer.fact_row_for = 'PARTY'
AND outer.report_date = p_report_date;
Update ozf_res_cust_prod_facts outer
Set (MTD_FUND_utilized, MTD_FUND_earned, MTD_FUND_paid)
=
(
SELECT NVL(SUM(utilized_amt),0) tot_utilized,
NVL(SUM(earned_amt),0) tot_earned,
NVL(SUM(paid_amt),0) tot_paid
FROM ozf_time_day a,
ozf_cust_fund_summary_mv b,
ozf_res_cust_prod c
WHERE c.resource_id = outer.resource_id
AND a.report_date = outer.report_date
AND a.ent_period_id = b.time_id
AND b.status_code = 'ACTIVE'
AND b.party_id = outer.party_id
AND b.party_id = c.party_id
AND b.bill_to_site_use_id = outer.bill_to_site_use_id
AND b.bill_to_site_use_id = c.bill_to_site_use_id
AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
AND
(
(
b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
AND b.product_id = c.product_attr_value
)
OR
(
b.product_level_type IS NULL
AND b.product_id IS NULL
AND EXISTS (SELECT 'X'
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id)
)
)
)
WHERE outer.fact_row_for = 'BILL_TO'
AND outer.report_date = p_report_date ;
Update ozf_res_cust_prod_facts outer
Set (MTD_FUND_utilized, MTD_FUND_earned, MTD_FUND_paid)
=
(
SELECT NVL(SUM(utilized_amt),0) tot_utilized,
NVL(SUM(earned_amt),0) tot_earned,
NVL(SUM(paid_amt),0) tot_paid
FROM ozf_time_day a,
ozf_cust_fund_summary_mv b,
ozf_res_cust_prod c
WHERE c.resource_id = outer.resource_id
AND a.report_date = outer.report_date
AND a.ent_period_id = b.time_id
AND b.status_code = 'ACTIVE'
AND b.party_id = outer.party_id
AND b.party_id = c.party_id
AND b.bill_to_site_use_id = outer.bill_to_site_use_id
AND b.bill_to_site_use_id = c.bill_to_site_use_id
AND b.ship_to_site_use_id = outer.ship_to_site_use_id
AND b.ship_to_site_use_id = c.ship_to_site_use_id
AND
(
( b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
AND b.product_id = c.product_attr_value
)
OR
( b.product_level_type IS NULL
AND b.product_id IS NULL
AND EXISTS (SELECT 'X'
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id)
)
)
)
WHERE outer.fact_row_for = 'SHIP_TO'
AND outer.report_date = p_report_date ;
Update ozf_res_cust_prod_facts outer
Set (FUND_unpaid)
=
(
SELECT (NVL(SUM(earned_amt),0) - NVL(SUM(paid_amt),0)) tot_unpaid
FROM ozf_cust_fund_summary_mv b,
ozf_res_cust_prod c
WHERE c.resource_id = outer.resource_id
AND b.time_id = -1
AND b.period_type_id = 256
AND b.status_code = 'ACTIVE'
AND b.party_id = c.party_id
AND b.party_id = outer.party_id
AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
AND
(
(
b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
AND b.product_id = c.product_attr_value
)
OR
(
b.product_level_type IS NULL
AND b.product_id IS NULL
AND EXISTS (SELECT 'X'
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id)
)
)
)
WHERE outer.fact_row_for = 'PARTY'
AND outer.report_date = p_report_date ;
Update ozf_res_cust_prod_facts outer
Set (fund_unpaid)
=
(
SELECT (NVL(SUM(earned_amt),0) - NVL(SUM(paid_amt),0)) tot_unpaid
FROM ozf_cust_fund_summary_mv b,
ozf_res_cust_prod c
WHERE c.resource_id = outer.resource_id
AND b.time_id = -1
AND b.period_type_id = 256
AND b.status_code = 'ACTIVE'
AND b.party_id = outer.party_id
AND b.party_id = c.party_id
AND b.bill_to_site_use_id = outer.bill_to_site_use_id
AND b.bill_to_site_use_id = c.bill_to_site_use_id
AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
AND
(
(
b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
AND b.product_id = c.product_attr_value
)
OR
(
b.product_level_type IS NULL
AND b.product_id IS NULL
AND EXISTS (SELECT 'X'
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id)
)
)
)
WHERE outer.fact_row_for = 'BILL_TO'
AND outer.report_date = p_report_date ;
Update ozf_res_cust_prod_facts outer
Set (fund_unpaid)
=
(
SELECT (NVL(SUM(earned_amt),0) - NVL(SUM(paid_amt),0)) tot_unpaid
FROM ozf_cust_fund_summary_mv b,
ozf_res_cust_prod c
WHERE c.resource_id = outer.resource_id
AND b.time_id = -1
AND b.period_type_id = 256
AND b.status_code = 'ACTIVE'
AND b.party_id = outer.party_id
AND b.party_id = c.party_id
AND b.bill_to_site_use_id = outer.bill_to_site_use_id
AND b.bill_to_site_use_id = c.bill_to_site_use_id
AND b.ship_to_site_use_id = outer.ship_to_site_use_id
AND b.ship_to_site_use_id = c.ship_to_site_use_id
AND
(
( b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
AND b.product_id = c.product_attr_value
)
OR
( b.product_level_type IS NULL
AND b.product_id IS NULL
AND EXISTS (SELECT 'X'
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id)
)
)
)
WHERE outer.fact_row_for = 'SHIP_TO'
AND outer.report_date = p_report_date ;
Update ozf_res_cust_prod_facts outer
Set (YTD_FUND_utilized, YTD_FUND_earned, YTD_FUND_paid)
=
(
SELECT NVL(SUM(utilized_amt),0) tot_utilized,
NVL(SUM(earned_amt),0) tot_earned,
NVL(SUM(paid_amt),0) tot_paid
FROM ozf_time_day a,
ozf_cust_fund_summary_mv b,
ozf_res_cust_prod c
WHERE c.resource_id = outer.resource_id
AND a.report_date = outer.report_date
AND a.ent_year_id = b.time_id
AND b.status_code = 'ACTIVE'
AND b.product_level_type = DECODE(outer.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
AND b.product_id = outer.PRODUCT_ATTR_VALUE
AND c.product_attr_value = b.product_id
AND c.product_attribute = DECODE(b.product_level_type, 'FAMILY','PRICING_ATTRIBUTE2','PRICING_ATTRIBUTE1')
AND b.party_id = c.party_id
AND NVL(b.bill_to_site_use_id, c.bill_to_site_use_id) = c.bill_to_site_use_id
AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
)
WHERE outer.fact_row_for = 'PRODUCT'
AND outer.report_date = p_report_date ;
Update ozf_res_cust_prod_facts outer
Set (QTD_FUND_utilized, QTD_FUND_earned, QTD_FUND_paid)
=
(
SELECT NVL(SUM(utilized_amt),0) tot_utilized,
NVL(SUM(earned_amt),0) tot_earned,
NVL(SUM(paid_amt),0) tot_paid
FROM ozf_time_day a,
ozf_cust_fund_summary_mv b,
ozf_res_cust_prod c
WHERE c.resource_id = outer.resource_id
AND a.report_date = outer.report_date
AND a.ent_qtr_id = b.time_id
AND b.status_code = 'ACTIVE'
AND b.product_level_type = DECODE(outer.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
AND b.product_id = outer.PRODUCT_ATTR_VALUE
AND c.product_attr_value = b.product_id
AND c.product_attribute = DECODE(b.product_level_type, 'FAMILY','PRICING_ATTRIBUTE2','PRICING_ATTRIBUTE1')
AND b.party_id = c.party_id
AND NVL(b.bill_to_site_use_id, c.bill_to_site_use_id) = c.bill_to_site_use_id
AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
)
WHERE outer.fact_row_for = 'PRODUCT'
AND outer.report_date = p_report_date ;
Update ozf_res_cust_prod_facts outer
Set (MTD_FUND_utilized, MTD_FUND_earned, MTD_FUND_paid)
=
(
SELECT NVL(SUM(utilized_amt),0) tot_utilized,
NVL(SUM(earned_amt),0) tot_earned,
NVL(SUM(paid_amt),0) tot_paid
FROM ozf_time_day a,
ozf_cust_fund_summary_mv b,
ozf_res_cust_prod c
WHERE c.resource_id = outer.resource_id
AND a.report_date = outer.report_date
AND a.ent_period_id = b.time_id
AND b.status_code = 'ACTIVE'
AND b.product_level_type = DECODE(outer.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
AND b.product_id = outer.PRODUCT_ATTR_VALUE
AND c.product_attr_value = b.product_id
AND c.product_attribute = DECODE(b.product_level_type, 'FAMILY','PRICING_ATTRIBUTE2','PRICING_ATTRIBUTE1')
AND b.party_id = c.party_id
AND NVL(b.bill_to_site_use_id, c.bill_to_site_use_id) = c.bill_to_site_use_id
AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
)
WHERE outer.fact_row_for = 'PRODUCT'
AND outer.report_date = p_report_date ;
Update ozf_res_cust_prod_facts outer
Set (fund_unpaid)
=
(
SELECT (NVL(SUM(earned_amt),0) - NVL(SUM(paid_amt),0)) tot_unpaid
FROM ozf_cust_fund_summary_mv b,
ozf_res_cust_prod c
WHERE c.resource_id = outer.resource_id
AND b.time_id = -1
AND b.period_type_id = 256
AND b.status_code = 'ACTIVE'
AND b.product_level_type = DECODE(outer.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
AND b.product_id = outer.PRODUCT_ATTR_VALUE
AND c.product_attr_value = b.product_id
AND c.product_attribute = DECODE(b.product_level_type, 'FAMILY','PRICING_ATTRIBUTE2','PRICING_ATTRIBUTE1')
AND b.party_id = c.party_id
AND NVL(b.bill_to_site_use_id, c.bill_to_site_use_id) = c.bill_to_site_use_id
AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
FROM ams_act_access_denorm
WHERE object_type = b.plan_type
AND object_id = b.plan_id
AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
)
WHERE outer.fact_row_for = 'PRODUCT'
AND outer.report_date = p_report_date ;
Update ozf_res_cust_prod_facts outer
Set (OPEN_CLAIMS)
=
(
SELECT NVL(SUM(amount_remaining),0)
FROM ozf_claims_all b,
(SELECT DISTINCT resource_id, party_id, cust_account_id, ship_to_site_use_id FROM ozf_res_cust_prod) c
WHERE c.resource_id = outer.resource_id
AND c.party_id = outer.party_id
AND b.cust_account_id = c.cust_account_id
AND b.claim_date <= outer.report_date
AND b.status_code = 'OPEN'
AND b.claim_class <> 'GROUP'
AND
(
(b.cust_shipto_acct_site_id = c.ship_to_site_use_id )
OR
(b.cust_shipto_acct_site_id IS NULL)
)
)
WHERE outer.fact_row_for = 'PARTY'
AND outer.report_date = p_report_date ;
Update ozf_res_cust_prod_facts outer
Set (OPEN_CLAIMS)
=
(
SELECT NVL(SUM(amount_remaining),0)
FROM ozf_claims_all b,
(SELECT DISTINCT resource_id, party_id, cust_account_id, bill_to_site_use_id, ship_to_site_use_id FROM ozf_res_cust_prod) c
WHERE c.resource_id = outer.resource_id
AND c.party_id = outer.party_id
AND c.bill_to_site_use_id = outer.bill_to_site_use_id
AND b.cust_billto_acct_site_id = outer.bill_to_site_use_id
AND b.cust_account_id = c.cust_account_id
AND b.claim_date <= outer.report_date
AND b.status_code = 'OPEN'
AND b.claim_class <> 'GROUP'
AND
(
(b.cust_shipto_acct_site_id = c.ship_to_site_use_id )
OR
(b.cust_shipto_acct_site_id IS NULL)
)
)
WHERE outer.fact_row_for = 'BILL_TO'
AND outer.report_date = p_report_date ;
Update ozf_res_cust_prod_facts outer
Set (OPEN_CLAIMS)
=
(
SELECT NVL(SUM(amount_remaining),0)
FROM ozf_claims_all b,
(SELECT DISTINCT resource_id, party_id, cust_account_id, bill_to_site_use_id, ship_to_site_use_id FROM ozf_res_cust_prod) c
WHERE c.resource_id = outer.resource_id
AND c.party_id = outer.party_id
AND c.bill_to_site_use_id = outer.bill_to_site_use_id
AND b.cust_billto_acct_site_id = outer.bill_to_site_use_id
AND c.ship_to_site_use_id = outer.ship_to_site_use_id
AND b.cust_shipto_acct_site_id = outer.ship_to_site_use_id
AND b.cust_account_id = c.cust_account_id
AND b.claim_date <= outer.report_date
AND b.status_code = 'OPEN'
AND b.claim_class <> 'GROUP'
)
WHERE outer.fact_row_for = 'SHIP_TO'
AND outer.report_date = p_report_date ;
update_sales_info(
l_api_version ,
l_init_msg_list ,
l_report_date ,
x_return_status ,
x_msg_count ,
x_msg_data ) ;
update_quota_sales_info(
l_api_version ,
l_init_msg_list ,
l_report_date ,
x_return_status ,
x_msg_count ,
x_msg_data ) ;
SELECT start_date, end_date INTO x_start_date, x_end_date
FROM ozf_time_ent_qtr
WHERE ent_qtr_id = p_time_id;
SELECT start_date, end_date INTO x_start_date, x_end_date
FROM ozf_time_ent_period
WHERE ent_period_id = p_time_id;
SELECT start_date, end_date INTO x_start_date, x_end_date
FROM ozf_time_week
WHERE week_id = p_time_id;
SELECT start_date, end_date INTO x_start_date, x_end_date
FROM ozf_time_day
WHERE report_date_julian = p_time_id;
SELECT SUM(sales.sales)
FROM ozf_search_selections_t acct,
ozf_search_selections_t prod,
ozf_order_sales_v sales,
ozf_time_rpt_struct rpt
where acct.search_type = 'QUALIFIER'
and prod.search_type = 'ITEM'
and acct.attribute_value = sales.ship_to_site_use_id
and prod.attribute_value = sales.inventory_item_id
and rpt.report_date = p_as_of_date
and BITAND(rpt.record_type_id, p_record_type_id) = rpt.record_type_id
and rpt.time_id = sales.time_id ;
SELECT ozf_cust_facts_pvt.get_cust_target ( b.site_use_id,
b.bill_to_site_use_id,
c.period_type_id ,
c.time_id) target
FROM ozf_product_allocations a
,ozf_account_allocations b
,ozf_time_allocations c
,ozf_search_selections_t acct
,ozf_search_selections_t prod
WHERE acct.search_type = 'QUALIFIER'
AND prod.search_type = 'ITEM'
AND a.allocation_for = 'CUST'
AND a.item_type = prod.attribute_id
AND a.item_id = prod.attribute_value
AND b.account_allocation_id = a.allocation_for_id
AND b.site_use_code = 'SHIP_TO'
AND b.site_use_id = prod.attribute_value
AND c.allocation_for = 'PROD'
AND c.allocation_for_id = a.product_allocation_id
AND c.period_type_id = p_period_type_id
AND c.time_id = p_time_id;
SELECT SUM(NVL(b.target,0))
FROM ozf_account_allocations a,
ozf_time_allocations b
WHERE a.allocation_for = 'FUND'
AND a.allocation_for_id IN (
SELECT fund_id
FROM ozf_funds_all_b
WHERE parent_fund_id IS NOT NULL
AND start_date_active >= p_start_date
AND end_date_active <= p_end_date )
AND b.allocation_for = 'CUST'
AND b.allocation_for_id = a.account_allocation_id
AND NVL(b.account_status, 'X') <> 'D'
AND b.period_type_id = p_period_type_id
AND b.time_id = p_time_id ;
SELECT NVL(SUM(NVL(sales.sales,0)),0)
FROM ozf_account_allocations acct,
ozf_product_allocations prod,
ozf_order_sales_v sales,
ozf_time_rpt_struct rpt
WHERE
rpt.report_date = p_as_of_date
AND BITAND(rpt.record_type_id, p_record_type_id )
= rpt.record_type_id
AND sales.time_id = rpt.time_id
AND sales.ship_to_site_use_id = acct.site_use_id
AND sales.inventory_item_id = prod.item_id
AND prod.allocation_for = 'CUST'
AND prod.allocation_for_id = acct.account_allocation_id
AND acct.allocation_for = 'FUND'
AND NVL(acct.account_status, 'X') <> 'D'
-- R12: Do not consider UnAllocated Rows
AND acct.parent_party_id <> -9999
AND acct.allocation_for_id IN (
SELECT aa.fund_id
FROM ozf_funds_all_b aa
WHERE aa.owner = p_resource_id
AND aa.fund_type = 'QUOTA'
AND aa.status_code <> 'CANCELLED'
AND NOT EXISTS ( SELECT 1
FROM ozf_funds_all_b bb
WHERE bb.parent_fund_id = aa.fund_id )
--
UNION ALL-- Get all leaf node quotas in the hierarchy of this resource
--
SELECT aa.fund_id
FROM ozf_funds_all_b aa
WHERE aa.fund_type = 'QUOTA'
AND aa.status_code <> 'CANCELLED'
CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
FROM ozf_funds_all_b bb
WHERE bb.owner = p_resource_id
AND bb.fund_type = 'QUOTA'
AND bb.status_code <> 'CANCELLED' )
);
SELECT SUM(b.target)
FROM ozf_account_allocations a,
ozf_time_allocations b
WHERE
b.allocation_for = 'CUST'
AND b.allocation_for_id = a.account_allocation_id
AND b.period_type_id = p_period_type_id
AND b.time_id = p_time_id
AND a.allocation_for = 'FUND'
AND NVL(a.account_status, 'X') <> 'D'
-- R12: Do not consider UnAllocated Rows
AND a.parent_party_id <> -9999
AND a.allocation_for_id IN ( -- Get leaf node quotas for this resource owns
SELECT aa.fund_id
FROM ozf_funds_all_b aa
WHERE aa.owner = p_resource_id
AND aa.fund_type = 'QUOTA'
AND aa.status_code <> 'CANCELLED'
AND NOT EXISTS ( SELECT 1
FROM ozf_funds_all_b bb
WHERE bb.parent_fund_id = aa.fund_id )
--
UNION ALL -- Get all leaf node quotas in the hierarchy of this resource
--
SELECT aa.fund_id
FROM ozf_funds_all_b aa
WHERE aa.fund_type = 'QUOTA'
AND aa.status_code <> 'CANCELLED'
CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
FROM ozf_funds_all_b bb
WHERE bb.owner = p_resource_id
AND bb.fund_type = 'QUOTA'
AND bb.status_code <> 'CANCELLED' )
);