[Home] [Help]
20: c.item_id inventory_item_id ,
21: c.item_id item_id ,
22: c.item_type item_type
23: FROM ozf_funds_all_b a
24: ,ozf_account_allocations b
25: ,ozf_product_allocations c
26: WHERE a.fund_type = 'QUOTA'
27: --AND p_report_date BETWEEN a.start_date_active
28: -- AND a.end_date_active
108: acct.bill_to_site_use_id BILL_TO_SITE_USE_ID,
109: acct.site_use_id SHIP_TO_SITE_USE_ID,
110: prod.item_type PRODUCT_ATTRIBUTE,
111: prod.item_id PRODUCT_ATTR_VALUE
112: FROM ozf_account_allocations acct,
113: ozf_product_allocations prod,
114: (SELECT DISTINCT a.owner
115: FROM ozf_funds_all_b a
116: WHERE a.fund_type = 'QUOTA'
879:
880: -- One NVL if target record exists but with null value
881: -- Second NVL if no target records exist. Not sure if this can ever happen..
882:
883: -- bill to site_use_id is never null in ozf_account_allocations
884: -- it will be -9996 if not available
885: -- so bill_to_site_use_id in ozf_cust_daily_facts will also be -9996
886:
887: CURSOR target_csr IS
885: -- so bill_to_site_use_id in ozf_cust_daily_facts will also be -9996
886:
887: CURSOR target_csr IS
888: SELECT NVL(SUM(NVL(c1.target,0)),0)
889: FROM ozf_account_allocations b1
890: ,ozf_time_allocations c1
891: ,ozf_funds_all_b d1
892: WHERE b1.allocation_for = 'FUND'
893: AND b1.allocation_for_id = d1.fund_id
970: time.time_id,
971: p_report_date)
972: , NVL(time.target,0) )
973: )
974: FROM ozf_account_allocations cust
975: ,ozf_product_allocations prod
976: ,ozf_time_allocations time
977: ,ozf_funds_all_b quota
978: WHERE
1428: CURSOR target_for_quota_csr ( p_resource_id NUMBER,
1429: p_period_type_id NUMBER,
1430: p_time_id NUMBER ) IS
1431: SELECT SUM(b.target)
1432: FROM ozf_account_allocations a,
1433: ozf_time_allocations b
1434: WHERE
1435: b.allocation_for = 'CUST'
1436: AND b.allocation_for_id = a.account_allocation_id
1493: SUM(fact.ptd_sales) MTD_SALES,
1494: SUM(fact.qtd_sales) QTD_SALES,
1495: SUM(fact.ytd_sales) YTD_SALES
1496: FROM ozf_cust_daily_facts fact,
1497: ozf_account_allocations site,
1498: ozf_product_allocations prod
1499: WHERE fact.report_date = p_report_date
1500: AND fact.ship_to_site_use_id = site.site_use_id
1501: AND fact.product_attribute <> 'OTHERS'
1758:
1759: CURSOR quota_acct_allocs_csr (p_fund_id NUMBER) IS
1760: SELECT account_allocation_id,
1761: site_use_id
1762: FROM ozf_account_allocations
1763: WHERE allocation_for = 'FUND'
1764: AND allocation_for_id = p_fund_id
1765: AND cust_account_id <> -9999;
1766:
1829:
1830: CURSOR acct_time_sales_csr ( p_account_allocation_id NUMBER,
1831: p_time_id NUMBER ) IS
1832: SELECT NVL(SUM(c.lysp_sales),0)
1833: FROM ozf_account_allocations a,
1834: ozf_product_allocations b,
1835: ozf_time_allocations c
1836: WHERE a.account_allocation_id = p_account_allocation_id
1837: AND b.allocation_for = 'CUST'
1971: --
1972: -- dbms_output.put_line('l_product_sales '|| prod.product_allocation_id || ' ' || l_product_sales);
1973: END LOOP; -- End of acct_prod_allocs_csr
1974: --
1975: UPDATE ozf_account_allocations
1976: SET lysp_sales = l_acct_sales
1977: WHERE account_allocation_id = acct.account_allocation_id;
1978:
1979: l_quota_sales := l_quota_sales + l_acct_sales;
2089: IS
2090: SELECT NVL(SUM(b.sales),0) tot_sales
2091: FROM ozf_time_rpt_struct a,
2092: ozf_order_sales_v b,
2093: ozf_account_allocations c
2094: WHERE c.allocation_for = 'FUND'
2095: AND c.allocation_for_id = p_fund_id
2096: AND b.ship_to_site_use_id = c.site_use_id
2097: AND b.time_id = a.time_id
2328: AND kpi.sequence_number = 1
2329: AND Kpi.report_date = c.report_date
2330: AND EXISTS (
2331: SELECT 1
2332: FROM ozf_account_allocations acct,
2333: ozf_product_allocations prod
2334: WHERE acct.site_use_id = c.ship_to_site_use_id
2335: AND prod.item_type = c.product_attribute
2336: AND prod.item_id = c.product_attr_value
2429: AND kpi.sequence_number = 1
2430: AND Kpi.report_date = c.report_date
2431: AND EXISTS (
2432: SELECT 1
2433: FROM ozf_account_allocations acct,
2434: ozf_product_allocations prod
2435: WHERE acct.site_use_id = c.ship_to_site_use_id
2436: AND prod.item_type = c.product_attribute
2437: AND prod.item_id = c.product_attr_value
2530: AND kpi.sequence_number = 1
2531: AND Kpi.report_date = c.report_date
2532: AND EXISTS (
2533: SELECT 1
2534: FROM ozf_account_allocations acct,
2535: ozf_product_allocations prod
2536: WHERE acct.site_use_id = c.ship_to_site_use_id
2537: AND prod.item_type = c.product_attribute
2538: AND prod.item_id = c.product_attr_value
2630: AND kpi.report_date = c.report_date
2631: AND c.product_attribute <> 'OTHERS'
2632: AND EXISTS (
2633: SELECT 1
2634: FROM ozf_account_allocations acct,
2635: ozf_product_allocations prod
2636: WHERE acct.site_use_id = c.ship_to_site_use_id
2637: AND prod.item_type = c.product_attribute
2638: AND prod.item_id = c.product_attr_value
3810: b.bill_to_site_use_id,
3811: c.period_type_id ,
3812: c.time_id) target
3813: FROM ozf_product_allocations a
3814: ,ozf_account_allocations b
3815: ,ozf_time_allocations c
3816: ,ozf_search_selections_t acct
3817: ,ozf_search_selections_t prod
3818: WHERE acct.search_type = 'QUALIFIER'
3830: */
3831:
3832: CURSOR quota_csr IS
3833: SELECT SUM(NVL(b.target,0))
3834: FROM ozf_account_allocations a,
3835: ozf_time_allocations b
3836: WHERE a.allocation_for = 'FUND'
3837: AND a.allocation_for_id IN (
3838: SELECT fund_id
3944:
3945: CURSOR sales_csr (p_as_of_date IN DATE,
3946: p_record_type_id IN NUMBER) IS
3947: SELECT NVL(SUM(NVL(sales.sales,0)),0)
3948: FROM ozf_account_allocations acct,
3949: ozf_product_allocations prod,
3950: ozf_order_sales_v sales,
3951: ozf_time_rpt_struct rpt
3952: WHERE
3988:
3989: -- Same cursor as refesh_kpi
3990: CURSOR quota_csr IS
3991: SELECT SUM(b.target)
3992: FROM ozf_account_allocations a,
3993: ozf_time_allocations b
3994: WHERE
3995: b.allocation_for = 'CUST'
3996: AND b.allocation_for_id = a.account_allocation_id