[Home] [Help]
PACKAGE BODY: APPS.OZF_CUST_FACTS_PVT
Source
1 PACKAGE BODY OZF_CUST_FACTS_PVT AS
2 /*$Header: ozfvcftb.pls 120.13 2011/03/25 09:45:46 kdass ship $*/
3
4 PROCEDURE refresh_accts_and_products(
5 p_api_version IN NUMBER,
6 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
7 p_report_date IN DATE,
8 x_return_status OUT NOCOPY VARCHAR2,
9 x_msg_count OUT NOCOPY NUMBER,
10 x_msg_data OUT NOCOPY VARCHAR2) AS
11
12 /*
13 Populate Accounts and Products only for leaf node quotas
14 */
15 CURSOR c_cust_prod IS
16 SELECT distinct
17 b.cust_account_id cust_account_id,
18 b.site_use_id site_use_id ,
19 b.bill_to_site_use_id bill_to_site_use_id,
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
29 AND a.status_code <> 'CANCELLED'
30 AND b.allocation_for = 'FUND'
31 AND b.allocation_for_id = a.fund_id
32 AND NVL(b.account_status, 'X') <> 'D'
33 AND c.allocation_for = 'CUST'
34 AND c.allocation_for_id = b.account_allocation_id
35 AND a.parent_fund_id IS NOT NULL
36 AND NOT EXISTS ( SELECT 1
37 FROM ozf_funds_all_b bb
38 WHERE bb.parent_fund_id = a.fund_id );
39
40 l_api_version CONSTANT NUMBER := 1.0;
41 l_api_name CONSTANT VARCHAR2(30) := 'refresh_accts_and_products';
42 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
43 l_return_status VARCHAR2(1);
44
45
46 BEGIN
47
48 ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' (-)') ;
49
50 IF FND_API.to_boolean(p_init_msg_list) THEN
51 FND_MSG_PUB.initialize;
52 END IF;
53
54 IF NOT FND_API.compatible_api_call(l_api_version,
55 p_api_version,
56 l_api_name,
57 g_pkg_name)
58 THEN
59 RAISE FND_API.g_exc_unexpected_error;
60 END IF;
61 x_return_status := FND_API.g_ret_sts_success;
62
63 DELETE FROM ozf_cust_daily_facts
64 WHERE report_date = p_report_date;
65
66 -- Get only allocations for the Child Quotas
67
68 FOR i IN c_cust_prod
69 LOOP
70
71 INSERT INTO ozf_cust_daily_facts (
72 cust_daily_fact_id ,
73 report_date ,
74 cust_account_id ,
75 ship_to_site_use_id ,
76 bill_to_site_use_id ,
77 inventory_item_id ,
78 product_attr_value ,
79 product_attribute ,
80 creation_date ,
81 created_by ,
82 last_update_date ,
83 last_updated_by ,
84 last_update_login )
85 VALUES ( ozf_cust_daily_facts_s.nextval,
86 trunc(p_report_date),
87 i.cust_account_id,
88 i.site_use_id,
89 i.bill_to_site_use_id,
90 i.inventory_item_id,
91 i.item_id,
92 i.item_type,
93 SYSDATE,
94 -1,
95 SYSDATE,
96 -1,
97 -1 );
98 END LOOP; --c_cust_prod
99
100 -- for R12, insert records into OZF_RES_CUST_PROD table
101 DELETE from OZF_RES_CUST_PROD;
102
103 INSERT INTO OZF_RES_CUST_PROD
104 (SELECT distinct
105 fund.owner RESOURCE_ID,
106 acct.parent_party_id PARTY_ID,
107 acct.cust_account_id CUST_ACCOUNT_ID,
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'
117 AND a.status_code <> 'CANCELLED') fund
118 WHERE prod.allocation_for = 'CUST'
119 AND prod.allocation_for_id = acct.account_allocation_id
120 AND acct.allocation_for = 'FUND'
121 AND NVL(acct.account_status, 'X') <> 'D'
122 AND acct.allocation_for_id in
123 (SELECT aa.fund_id
124 FROM ozf_funds_all_b aa
125 WHERE aa.owner = fund.owner
126 AND aa.fund_type = 'QUOTA'
127 AND aa.status_code <> 'CANCELLED'
128 AND NOT EXISTS ( SELECT 1
129 FROM ozf_funds_all_b bb
130 WHERE bb.parent_fund_id = aa.fund_id )
131 UNION ALL
132 SELECT aa.fund_id
133 FROM ozf_funds_all_b aa
134 WHERE aa.fund_type = 'QUOTA'
135 AND aa.status_code <> 'CANCELLED'
136 CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
137 START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
138 FROM ozf_funds_all_b bb
139 WHERE bb.owner= fund.owner
140 AND bb.fund_type = 'QUOTA'
141 AND bb.status_code <> 'CANCELLED')
142 )
143 AND prod.item_type <> 'OTHERS');
144
145 ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' (+)');
146
147 EXCEPTION
148 WHEN FND_API.G_EXC_ERROR THEN
149 x_return_status := FND_API.g_ret_sts_error ;
150 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
151 p_count => x_msg_count,
152 p_data => x_msg_data);
153
154 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
155 x_return_status := FND_API.g_ret_sts_unexp_error ;
156 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
157 p_count => x_msg_count,
158 p_data => x_msg_data);
159
160 WHEN OTHERS THEN
161 x_return_status := FND_API.g_ret_sts_unexp_error ;
162 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
163 p_count => x_msg_count,
164 p_data => x_msg_data);
165 END refresh_accts_and_products;
166
167
168 PROCEDURE refresh_sales_info(
169 p_api_version IN NUMBER,
170 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
171 p_report_date IN DATE,
172 x_return_status OUT NOCOPY VARCHAR2,
173 x_msg_count OUT NOCOPY NUMBER,
174 x_msg_data OUT NOCOPY VARCHAR2) AS
175
176 l_api_version CONSTANT NUMBER := 1.0;
177 l_api_name CONSTANT VARCHAR2(30) := 'refresh_sales_info';
178 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
179 l_return_status VARCHAR2(1);
180
181 CURSOR daily_facts_csr IS
182 SELECT cust_daily_fact_id,
183 report_date,
184 cust_account_id,
185 ship_to_site_use_id,
186 bill_to_site_use_id,
187 product_attribute,
188 product_attr_value,
189 inventory_item_id
190 FROM ozf_cust_daily_facts
191 WHERE report_date = p_report_date;
192
193 CURSOR xtd_sales_csr (p_report_date DATE,
194 p_cust_account_id NUMBER,
195 p_ship_to_site_use_id NUMBER,
196 p_bill_to_site_use_id NUMBER,
197 p_inventory_item_id NUMBER ) IS
198 SELECT a.period_type_id,
199 NVL(SUM(b.sales),0) tot_sales
200 FROM ozf_time_rpt_struct a,
201 ozf_order_sales_v b
202 WHERE a.report_date = p_report_date
203 AND BITAND(a.record_type_id, 119) = a.record_type_id
204 AND a.time_id = b.time_id
205 AND b.cust_account_id = p_cust_account_id
206 AND b.ship_to_site_use_id = p_ship_to_site_use_id
207 AND b.bill_to_site_use_id = DECODE(p_bill_to_site_use_id,
208 -9996,b.bill_to_site_use_id,
209 p_bill_to_site_use_id)
210 AND b.inventory_item_id = p_inventory_item_id
211 GROUP BY a.period_type_id ;
212
213 CURSOR items_csr (p_item_type VARCHAR2,
214 p_item_id NUMBER) IS
215 SELECT inventory_item_id
216 FROM mtl_item_categories mtl,
217 eni_prod_denorm_hrchy_v eni
218 WHERE mtl.category_set_id = eni.category_set_id
219 AND mtl.category_id = eni.child_id
220 AND mtl.organization_id = fnd_profile.value('QP_ORGANIZATION_ID')
221 AND eni.parent_id = p_item_id
222 AND 'PRICING_ATTRIBUTE2' = p_item_type
223 UNION ALL
224 SELECT p_item_id inventory_item_id
225 FROM dual
226 WHERE 'PRICING_ATTRIBUTE1' = p_item_type
227 UNION ALL
228 SELECT p_item_id inventory_item_id
229 FROM dual
230 WHERE 'OTHERS' = p_item_type;
231
232 -- for R12, get the last year sales for Month, Quarter and Year as of the report_date
233 CURSOR ly_sales_csr (p_report_date DATE,
234 p_cust_account_id NUMBER,
235 p_ship_to_site_use_id NUMBER,
236 p_bill_to_site_use_id NUMBER,
237 p_inventory_item_id NUMBER,
238 p_period_type_id NUMBER ) IS
239 SELECT NVL(SUM(b.sales),0) tot_sales
240 FROM ozf_time_rpt_struct a,
241 ozf_order_sales_v b,
242 ozf_time_day c
243 WHERE c.report_date = p_report_date
244 AND a.time_id = decode(p_period_type_id,32,c.ent_period_id,
245 64, c.ent_qtr_id,
246 128, c.ent_year_id)
247 AND b.time_id = a.time_id
248 AND b.cust_account_id = p_cust_account_id
249 AND b.ship_to_site_use_id = p_ship_to_site_use_id
250 AND b.bill_to_site_use_id = DECODE(p_bill_to_site_use_id,
251 -9996,b.bill_to_site_use_id,
252 p_bill_to_site_use_id)
253 AND b.inventory_item_id = p_inventory_item_id;
254
255 -- for R12, get the XTD baseline sales last year sales for Month, Quarter and Year as of the report_date
256 CURSOR xtd_baseline_sales_csr (p_report_date DATE,
257 p_ship_to_site_use_id NUMBER,
258 p_item_id NUMBER ) IS
259 SELECT b.period_type_id,
260 NVL(SUM(b.baseline_sales),0) base_sales
261 FROM ozf_time_rpt_struct a,
262 ozf_baseline_sales_v b
263 WHERE a.report_date = p_report_date
264 AND BITAND(a.record_type_id, 119) = a.record_type_id
265 AND a.time_id = b.time_id
266 AND b.data_source = fnd_profile.value('OZF_DASH_BASELINE_SALES_SRC')
267 AND b.market_type = 'SHIP_TO'
268 AND b.market_id = p_ship_to_site_use_id
269 AND b.item_level = 'PRICING_ATTRIBUTE1'
270 AND b.item_id = p_ITEM_ID
271 GROUP BY b.period_type_id;
272
273 l_report_date DATE;
274 l_ly_report_date DATE;
275
276 l_day_total NUMBER;
277 l_week_total NUMBER;
278 l_mth_total NUMBER;
279 l_qtr_total NUMBER;
280
281 l_ly_day_total NUMBER;
282 l_ly_week_total NUMBER;
283 l_ly_mth_total NUMBER;
284 l_ly_qtr_total NUMBER;
285
286 -- Added for R12
287 l_ly_sales NUMBER;
288 l_ly_qtr_sales NUMBER;
289 l_ly_mth_sales NUMBER;
290 l_day_bsales NUMBER;
291 l_week_bsales NUMBER;
292 l_mth_bsales NUMBER;
293 l_qtr_bsales NUMBER;
294 l_ytd_sales NUMBER;
295 l_ytd_bsales NUMBER;
296 l_qtd_sales NUMBER;
297 l_qtd_bsales NUMBER;
298 l_mtd_sales NUMBER;
299 l_mtd_bsales NUMBER;
300
301 BEGIN
302
303 ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' (-)') ;
304
305 IF FND_API.to_boolean(p_init_msg_list) THEN
306 FND_MSG_PUB.initialize;
307 END IF;
308
309 IF NOT FND_API.compatible_api_call(l_api_version,
310 p_api_version,
311 l_api_name,
312 g_pkg_name)
313 THEN
314 RAISE FND_API.g_exc_unexpected_error;
315 END IF;
316 x_return_status := FND_API.g_ret_sts_success;
317
318 --
319 l_report_date := p_report_date;
320 l_ly_report_date := add_months(p_report_date, -12);
321
322 FOR fact IN daily_facts_csr
323 LOOP
324 -- Initialize all variables
325 l_day_total := 0;
326 l_week_total := 0;
327 l_mth_total := 0;
328 l_qtr_total := 0;
329
330 l_ly_day_total := 0;
331 l_ly_week_total := 0;
332 l_ly_mth_total := 0;
333 l_ly_qtr_total := 0;
334
335 l_day_bsales := 0;
336 l_week_bsales := 0;
337 l_mth_bsales := 0;
338 l_qtr_bsales := 0;
339 l_ytd_sales := 0;
340 l_ytd_bsales := 0;
341 l_qtd_sales := 0;
342 l_qtd_bsales := 0;
343 l_mtd_sales := 0;
344 l_mtd_bsales := 0;
345
346 -- Calculate XTD totals
347
348 -- If item_type is a category, then sum for all products in the category
349
350 FOR prod IN items_csr( fact.product_attribute,
351 TO_NUMBER(fact.product_attr_value) )
352 LOOP
353 --
354 FOR sale IN xtd_sales_csr (l_report_date ,
355 fact.cust_account_id ,
356 fact.ship_to_site_use_id ,
357 fact.bill_to_site_use_id ,
358 prod.inventory_item_id )
359 LOOP
360 --
361 IF sale.period_type_id = 1
362 THEN
363 l_day_total := l_day_total + sale.tot_sales ;
364 ELSIF sale.period_type_id = 16
365 THEN
366 l_week_total := l_week_total + sale.tot_sales ;
367 ELSIF sale.period_type_id = 32
368 THEN
369 l_mth_total := l_mth_total + sale.tot_sales ;
370 ELSIF sale.period_type_id = 64
371 THEN
372 l_qtr_total := l_qtr_total + sale.tot_sales ;
373 END IF;
374 --
375 END LOOP; -- xtd_sales_csr
376
377 -- Calculate LYSP and LYTD totals
378
379 FOR sale IN xtd_sales_csr (l_ly_report_date ,
380 fact.cust_account_id ,
381 fact.ship_to_site_use_id ,
382 fact.bill_to_site_use_id ,
383 prod.inventory_item_id )
384 LOOP
385 --
386 IF sale.period_type_id = 1
387 THEN
388 l_ly_day_total := l_ly_day_total + sale.tot_sales ;
389 ELSIF sale.period_type_id = 16
390 THEN
391 l_ly_week_total := l_ly_week_total + sale.tot_sales ;
392 ELSIF sale.period_type_id = 32
393 THEN
394 l_ly_mth_total := l_ly_mth_total + sale.tot_sales ;
395 ELSIF sale.period_type_id = 64
396 THEN
397 l_ly_qtr_total := l_ly_qtr_total + sale.tot_sales ;
398 END IF;
399 --
400 END LOOP; -- xtd_sales_csr
401
402
403 --Added for R12 - baseline Sales
404 --Calculate baseline sales total for all Products in the family
405 FOR basesale IN xtd_baseline_sales_csr (l_report_date ,
406 fact.ship_to_site_use_id,
407 prod.inventory_item_id)
408 LOOP
409 IF basesale.period_type_id = 1
410 THEN
411 l_day_bsales := l_day_bsales + basesale.base_sales ;
412 ELSIF basesale.period_type_id = 16
413 THEN
414 l_week_bsales := l_week_bsales + basesale.base_sales ;
415 ELSIF basesale.period_type_id = 32
416 THEN
417 l_mth_bsales := l_mth_bsales + basesale.base_sales ;
418 ELSIF basesale.period_type_id = 64
419 THEN
420 l_qtr_bsales := l_qtr_bsales + basesale.base_sales ;
421 END IF;
422
423 END LOOP; -- xtd_baseline_sales_csr
424
425
426 --Added for R12 - last year sales
427 OPEN ly_sales_csr (l_ly_report_date ,
428 fact.cust_account_id ,
429 fact.ship_to_site_use_id ,
430 fact.bill_to_site_use_id ,
431 prod.inventory_item_id,
432 128 );
433 FETCH ly_sales_csr INTO l_ly_sales;
434 CLOSE ly_sales_csr;
435
436 --Added for R12 - last year's same quarter sales
437 OPEN ly_sales_csr (l_ly_report_date ,
438 fact.cust_account_id ,
439 fact.ship_to_site_use_id ,
440 fact.bill_to_site_use_id ,
441 prod.inventory_item_id,
442 64 );
443 FETCH ly_sales_csr INTO l_ly_qtr_sales;
444 CLOSE ly_sales_csr;
445
446 --Added for R12 - last year's same month sales
447 OPEN ly_sales_csr (l_ly_report_date ,
448 fact.cust_account_id ,
449 fact.ship_to_site_use_id ,
450 fact.bill_to_site_use_id ,
451 prod.inventory_item_id,
452 32 );
453 FETCH ly_sales_csr INTO l_ly_mth_sales;
454 CLOSE ly_sales_csr;
455
456 END LOOP; -- item_csr
457
458 /*
459 --Moved this inside item_csr because baseline mv now doesnt have data for categories---------
460 --Calculate baseline sales total for all Products in the family
461 FOR basesale IN xtd_baseline_sales_csr (l_report_date ,
462 fact.ship_to_site_use_id,
463 fact.product_attribute,
464 fact.inventory_item_id )
465 LOOP
466 IF basesale.period_type_id = 1
467 THEN
468 l_day_bsales := l_day_bsales + basesale.base_sales ;
469 ELSIF basesale.period_type_id = 16
470 THEN
471 l_week_bsales := l_week_bsales + basesale.base_sales ;
472 ELSIF basesale.period_type_id = 32
473 THEN
474 l_mth_bsales := l_mth_bsales + basesale.base_sales ;
475 ELSIF basesale.period_type_id = 64
476 THEN
477 l_qtr_bsales := l_qtr_bsales + basesale.base_sales ;
478 END IF;
479
480 END LOOP; -- xtd_baseline_sales_csr
481 ------------------------------------------------------------------------------------------
482 */
483
484 l_mtd_sales := l_day_total + l_week_total ;
485 l_mtd_bsales := l_day_bsales + l_week_bsales ;
486 l_qtd_sales := l_mtd_sales + l_mth_total;
487 l_qtd_bsales := l_mtd_bsales + l_mth_bsales;
488 l_ytd_sales := l_qtd_sales + l_qtr_total ;
489 l_ytd_bsales := l_qtd_bsales + l_qtr_bsales ;
490
491 /* MTD Baseline (lesser of: forecasted baseline or MTD sales) */
492 IF l_mtd_sales < l_mtd_bsales THEN
493 l_mtd_bsales := l_mtd_sales ;
494 END IF ;
495
496 /* QTD Baseline (lesser of: forecasted baseline or QTD sales) */
497 IF l_qtd_sales < l_qtd_bsales THEN
498 l_qtd_bsales := l_qtd_sales ;
499 END IF ;
500
501 /* YTD Baseline (lesser of: forecasted baseline or YTD sales) */
502 IF l_ytd_sales < l_ytd_bsales THEN
503 l_ytd_bsales := l_ytd_sales ;
504 END IF ;
505
506 -- ozf_utility_pvt.write_conc_log( 'Fact Id : '||fact.cust_daily_fact_id);
507 -- ozf_utility_pvt.write_conc_log( 'Day : '|| l_day_total);
508 -- ozf_utility_pvt.write_conc_log( 'Week : '|| l_week_total);
509 -- ozf_utility_pvt.write_conc_log( 'Month : '|| l_mth_total);
510 -- ozf_utility_pvt.write_conc_log( 'Quarter : '|| l_qtr_total);
511
512 -- Modified for R12
513 UPDATE ozf_cust_daily_facts
514 SET ptd_sales = l_mtd_sales
515 ,qtd_sales = l_day_total + l_week_total + l_mth_total
516 ,ytd_sales = l_ytd_sales
517 ,lptd_sales = l_ly_day_total + l_ly_week_total
518 ,lqtd_sales = l_ly_day_total + l_ly_week_total + l_ly_mth_total
519 ,lysp_sales = l_ly_mth_sales
520 ,lysq_sales = l_ly_qtr_sales
521 ,ly_sales = l_ly_sales
522 ,lytd_sales = l_ly_day_total + l_ly_week_total + l_ly_mth_total + l_ly_qtr_total
523 ,mtd_basesales = l_mtd_bsales
524 ,qtd_basesales = l_qtd_bsales
525 ,ytd_basesales = l_ytd_bsales
526 WHERE cust_daily_fact_id = fact.cust_daily_fact_id;
527
528 END LOOP;
529 --
530 ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' (+)');
531
532 EXCEPTION
533 WHEN FND_API.G_EXC_ERROR THEN
534 x_return_status := FND_API.g_ret_sts_error ;
535 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
536 p_count => x_msg_count,
537 p_data => x_msg_data);
538
539 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
540 x_return_status := FND_API.g_ret_sts_unexp_error ;
541 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
542 p_count => x_msg_count,
543 p_data => x_msg_data);
544
545 WHEN OTHERS THEN
546 x_return_status := FND_API.g_ret_sts_unexp_error ;
547 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
548 p_count => x_msg_count,
549 p_data => x_msg_data);
550 END refresh_sales_info;
551
552
553 PROCEDURE refresh_orders_info(
554 p_api_version IN NUMBER,
555 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
556 p_report_date IN DATE,
557 x_return_status OUT NOCOPY VARCHAR2,
558 x_msg_count OUT NOCOPY NUMBER,
559 x_msg_data OUT NOCOPY VARCHAR2) AS
560
561 l_api_version CONSTANT NUMBER := 1.0;
562 l_api_name CONSTANT VARCHAR2(30) := 'refresh_orders_info';
563 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
564 l_return_status VARCHAR2(1);
565
566 CURSOR last_day_csr IS
567 SELECT b.end_date
568 FROM ozf_time_day a,
569 ozf_time_ent_period b
570 WHERE a.report_date = p_report_date
571 AND a.ent_period_id = b.ent_period_id;
572
573
574 CURSOR items_csr (p_item_type VARCHAR2,
575 p_item_id NUMBER) IS
576 SELECT inventory_item_id
577 FROM mtl_item_categories mtl,
578 eni_prod_denorm_hrchy_v eni
579 WHERE mtl.category_set_id = eni.category_set_id
580 AND mtl.category_id = eni.child_id
581 AND mtl.organization_id = fnd_profile.value('QP_ORGANIZATION_ID')
582 AND eni.parent_id = p_item_id
583 AND 'PRICING_ATTRIBUTE2' = p_item_type
584 UNION ALL
585 SELECT p_item_id inventory_item_id
586 FROM dual
587 WHERE 'PRICING_ATTRIBUTE1' = p_item_type
588 UNION ALL
589 SELECT p_item_id inventory_item_id
590 FROM dual
591 WHERE 'OTHERS' = p_item_type;
592
593 CURSOR daily_facts_csr IS
594 SELECT cust_daily_fact_id,
595 report_date,
596 cust_account_id,
597 ship_to_site_use_id,
598 bill_to_site_use_id,
599 product_attribute,
600 product_attr_value,
601 inventory_item_id
602 FROM ozf_cust_daily_facts
603 WHERE report_date = p_report_date;
604
605 /*
606 - Convert Order quantity to common uom
607 - Convert Order amount to common currency
608 */
609 CURSOR open_orders_csr( p_ship_to_site_use_id NUMBER,
610 p_bill_to_site_use_id NUMBER,
611 p_inventory_item_id NUMBER) IS
612 SELECT a.line_id,
613 a.request_date,
614 a.promise_date,
615 a.schedule_ship_date,
616 DECODE( ozf_tp_util_queries.get_quota_unit,
617 'A', gl_currency_api.convert_amount_sql( b.transactional_curr_code,
618 fnd_profile.value('OZF_TP_COMMON_CURRENCY'),
619 a.request_date,
620 fnd_profile.value('OZF_CURR_CONVERSION_TYPE'),
621 (a.ordered_quantity*a.unit_selling_price)
622 )
623 , inv_convert.inv_um_convert(a.inventory_item_id,
624 NULL,
625 a.ordered_quantity,
626 a.order_quantity_uom,
627 fnd_profile.value('OZF_TP_COMMON_UOM') ,
628 NULL,
629 NULL)
630 ) order_unit,
631 DECODE (ozf_tp_util_queries.get_quota_unit,
632 'A', b.transactional_curr_code
633 , a.order_quantity_uom) from_unit,
634 DECODE (ozf_tp_util_queries.get_quota_unit,
635 'A', fnd_profile.value('OZF_TP_COMMON_CURRENCY')
636 , fnd_profile.value('OZF_TP_COMMON_UOM') ) to_unit,
637 DECODE (ozf_tp_util_queries.get_quota_unit,
638 'A', (a.ordered_quantity*a.unit_selling_price)
639 , a.ordered_quantity ) unit
640 FROM oe_order_lines_all a,
641 oe_order_headers_all b
642 WHERE a.open_flag = 'Y'
643 AND a.cancelled_flag = 'N'
644 AND a.booked_flag = 'Y'
645 AND a.ship_to_org_id = p_ship_to_site_use_id
646 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)
647 AND a.inventory_item_id = p_inventory_item_id
648 AND a.header_id = b.header_id ;
649
650 CURSOR backorder_csr( p_line_id NUMBER) IS
651 SELECT NVL(
652 SUM(
653 DECODE (ozf_tp_util_queries.get_quota_unit,
654 'A', gl_currency_api.convert_amount_sql( a.currency_code,
655 fnd_profile.value('OZF_TP_COMMON_CURRENCY'),
656 a.date_requested,
657 fnd_profile.value('OZF_CURR_CONVERSION_TYPE'),
658 (a.requested_quantity*a.unit_price))
659 , inv_convert.inv_um_convert(a.inventory_item_id,
660 NULL,
661 a.requested_quantity,
662 a.requested_quantity_uom,
663 fnd_profile.value('OZF_TP_COMMON_UOM') ,
664 NULL,
665 NULL)
666 )
667 ) , 0 ) requested_quantity
668 FROM wsh_deliverables_v a
669 WHERE a.source_line_id = p_line_id
670 AND a.released_status = 'B';
671
672 l_last_day_of_period DATE;
673
674 l_past_due_qty NUMBER;
675 l_backordered_qty NUMBER;
676 l_future_order_qty NUMBER;
677 l_current_order_qty NUMBER;
678
679 l_temp_bo_qty NUMBER;
680
681 BEGIN
682
683 ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' (-)') ;
684
685 IF FND_API.to_boolean(p_init_msg_list) THEN
686 FND_MSG_PUB.initialize;
687 END IF;
688
689 IF NOT FND_API.compatible_api_call(l_api_version,
690 p_api_version,
691 l_api_name,
692 g_pkg_name)
693 THEN
694 RAISE FND_API.g_exc_unexpected_error;
695 END IF;
696 x_return_status := FND_API.g_ret_sts_success;
697 --
698
699 -- Get Last Day of the Period
700 OPEN last_day_csr;
701 FETCH last_day_csr INTO l_last_day_of_period;
702 CLOSE last_day_csr;
703
704 FOR fact IN daily_facts_csr
705 LOOP
706 -- Initialize all variables
707 l_past_due_qty := 0;
708 l_backordered_qty := 0;
709 l_future_order_qty := 0;
710 l_current_order_qty := 0;
711
712 -- For every site-product get the orders info
713
714 FOR prod IN items_csr(fact.product_attribute,
715 TO_NUMBER(fact.product_attr_value))
716 LOOP
717 --
718
719 FOR ord IN open_orders_csr(fact.ship_to_site_use_id,
720 fact.bill_to_site_use_id,
721 prod.inventory_item_id)
722 LOOP
723
724 IF ( ord.order_unit < 0 )
725 THEN
726
727 /*
728 FND_MESSAGE.Set_Name ('OZF', 'OZF_TP_MISSING_CONVERSIONS');
729 FND_MESSAGE.Set_Token('TYPE', 'UOM');
730 FND_MESSAGE.Set_Token('FROM_VALUE', err.uom_code);
731 FND_MESSAGE.Set_Token('TO_VALUE', err.common_uom_code);
732 FND_MESSAGE.Set_Token('DATE', err.transaction_date);
733 l_mesg := FND_MESSAGE.Get;
734 */
735 ozf_utility_pvt.write_conc_log('Cannot convert ' || ord.from_unit || ' to ' || ord.to_unit );
736 ozf_utility_pvt.write_conc_log('This value will be ignored ' || ord.unit );
737
738 GOTO NEXT_ORDER;
739 END IF;
740
741 IF ( SIGN(TRUNC(ord.promise_date) - TRUNC(p_report_date) ) = -1 )
742 THEN
743 -- This Order Line is past the due date
744 -- Check if it is backordered
745 OPEN backorder_csr(ord.line_id);
746 FETCH backorder_csr INTO l_temp_bo_qty ;
747 CLOSE backorder_csr;
748 --
749 IF l_temp_bo_qty = 0
750 THEN
751 -- Order is due but not backorderd
752 l_past_due_qty := l_past_due_qty + ord.order_unit ;
753 ELSE
754 -- Order has been backordered
755 l_backordered_qty := l_backordered_qty + l_temp_bo_qty ;
756 END IF;
757 --
758 END IF;
759
760 IF ( SIGN(l_last_day_of_period - TRUNC(ord.schedule_ship_date) ) = -1 )
761 THEN
762 -- This is a future Order
763 l_future_order_qty := l_future_order_qty + ord.order_unit;
764 END IF;
765
766 IF ( SIGN(l_last_day_of_period - TRUNC(ord.schedule_ship_date) ) >= 0 )
767 THEN
768 -- This Order is due in the current period
769 l_current_order_qty := l_current_order_qty + ord.order_unit;
770 END IF;
771
772 <<NEXT_ORDER>>
773 NULL;
774 END LOOP; -- open_orders_csr
775
776 END LOOP; -- items_csr
777
778 UPDATE ozf_cust_daily_facts
779 SET past_due_order_qty = l_past_due_qty
780 ,current_period_order_qty = l_current_order_qty
781 ,backordered_qty = l_backordered_qty
782 ,booked_for_future_qty = l_future_order_qty
783 WHERE cust_daily_fact_id = fact.cust_daily_fact_id;
784
785 END LOOP;
786 --
787 ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' (+)');
788
789 EXCEPTION
790 WHEN FND_API.G_EXC_ERROR THEN
791 x_return_status := FND_API.g_ret_sts_error ;
792 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
793 p_count => x_msg_count,
794 p_data => x_msg_data);
795
796 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
797 x_return_status := FND_API.g_ret_sts_unexp_error ;
798 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
799 p_count => x_msg_count,
800 p_data => x_msg_data);
801
802 WHEN OTHERS THEN
803 x_return_status := FND_API.g_ret_sts_unexp_error ;
804 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
805 p_count => x_msg_count,
806 p_data => x_msg_data);
807 END refresh_orders_info;
808
809 -- This function is used in DashboardAccountVO
810
811 FUNCTION get_cust_target ( p_party_id IN NUMBER,
812 p_bill_to_site_use_id IN NUMBER,
813 p_site_use_id IN NUMBER,
814 p_col IN VARCHAR2,
815 p_sales IN NUMBER,
816 p_report_date IN DATE,
817 p_resource_id IN NUMBER )
818 RETURN NUMBER IS
819
820 CURSOR cust_target_csr IS
821 SELECT NVL( DECODE(p_col, 'YEAR_QUOTA', SUM(f.current_year_target),
822 'PERIOD_QUOTA', SUM(f.current_period_target),
823 'QTR_QUOTA', SUM(f.current_qtr_target) ) ,0)
824 FROM ozf_cust_daily_facts f ,
825 hz_cust_accounts h
826 WHERE f.report_date = p_report_date
827 AND f.cust_account_id = h.cust_account_id
828 AND f.bill_to_site_use_id = NVL(p_bill_to_site_use_id, f.bill_to_site_use_id)
829 AND f.ship_to_site_use_id = NVL(p_site_use_id, f.ship_to_site_use_id)
830 AND h.party_id = p_party_id
831 AND f.product_attribute = 'OTHERS'
832 AND EXISTS ( SELECT 1
833 FROM ams_party_market_segments b,
834 jtf_terr_rsc_all a,
835 jtf_terr_rsc_access_all c
836 WHERE b.market_qualifier_type = 'TERRITORY'
837 AND b.market_qualifier_reference = a.terr_id
838 AND a.resource_id = p_resource_id
839 --AND a.primary_contact_flag = 'Y'
840 AND a.terr_rsc_id = c.terr_rsc_id
841 AND c.access_type = 'OFFER'
842 AND c.trans_access_code = 'PRIMARY_CONTACT'
843 AND b.site_use_code = 'SHIP_TO'
844 AND b.site_use_id = f.ship_to_site_use_id) ;
845
846 l_return_value NUMBER := 0 ;
847
848 BEGIN
849
850 OPEN cust_target_csr;
851 FETCH cust_target_csr INTO l_return_value;
852 CLOSE cust_target_csr;
853
854 --Remove the calculation. This can be done in UI query
855 /* IF ( p_col IN ('PCT_YEAR_MET', 'PCT_PERIOD_MET') )
856 THEN
857 IF (l_return_value) <> 0
858 THEN
859 l_return_value := ROUND((p_sales*100)/l_return_value ,2) ;
860 END IF;
861 END IF; */
862
863 RETURN l_return_value;
864
865 EXCEPTION
866 WHEN OTHERS THEN
867 l_return_value := 0;
868 RETURN l_return_value;
869 END get_cust_target;
870
871
872
873 FUNCTION get_cust_target ( p_site_use_id IN NUMBER,
874 p_bill_to_site_use_id IN NUMBER,
875 p_period_type_id IN NUMBER ,
876 p_time_id IN NUMBER ,
877 p_report_date IN DATE)
878 RETURN NUMBER IS
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
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
894 AND b1.site_use_id = p_site_use_id
895 AND b1.bill_to_site_use_id = p_bill_to_site_use_id
896 AND c1.allocation_for = 'CUST'
897 AND c1.allocation_for_id = b1.account_allocation_id
898 AND c1.period_type_id = p_period_type_id
899 AND c1.time_id = p_time_id
900 AND d1.fund_type = 'QUOTA'
901 AND d1.status_code <> 'CANCELLED'
902 AND d1.parent_fund_id IS NOT NULL
903 -- AND p_report_date BETWEEN d1.start_date_active AND d1.end_date_active
904 AND NOT EXISTS ( SELECT 1
905 FROM ozf_funds_all_b dd1
906 WHERE dd1.parent_fund_id = d1.fund_id);
907
908 l_target NUMBER := 0;
909
910 BEGIN
911
912 OPEN target_csr ;
913 FETCH target_csr INTO l_target;
914 CLOSE target_csr;
915
916 RETURN l_target ;
917
918 EXCEPTION
919 WHEN OTHERS THEN
920 RETURN l_target ;
921 END get_cust_target;
922
923 PROCEDURE refresh_target_info(
924 p_api_version IN NUMBER,
925 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
926 p_report_date IN DATE,
927 x_return_status OUT NOCOPY VARCHAR2,
928 x_msg_count OUT NOCOPY NUMBER,
929 x_msg_data OUT NOCOPY VARCHAR2) AS
930
931 l_api_version CONSTANT NUMBER := 1.0;
932 l_api_name CONSTANT VARCHAR2(30) := 'refresh_target_info';
933 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
934 l_return_status VARCHAR2(1);
935
936 CURSOR daily_facts_csr IS
937 SELECT cust_daily_fact_id,
938 report_date,
939 cust_account_id,
940 ship_to_site_use_id,
941 bill_to_site_use_id,
942 product_attribute,
943 product_attr_value
944 FROM ozf_cust_daily_facts
945 WHERE report_date = p_report_date;
946
947 -- The 'OTHERS' record will have the total customer target in cust_daily_facts
948 -- remove p_report_date between start_date and end_date
949
950 -- C1 P1 T1 100
951 -- T2 150
952 -- P2 T1 200
953 -- T2 125
954 -- OTHERS 575
955 -- C1 will have targets from all products
956 -- So C1 should always belong to only one leaf node territory with only
957 -- one owner
958
959 CURSOR target_csr (p_item_type VARCHAR2,
960 p_item_id NUMBER,
961 p_ship_to_site_use_id NUMBER,
962 p_bill_to_site_use_id NUMBER,
963 p_period_type_id NUMBER,
964 p_time_id NUMBER) IS
965 SELECT SUM(
966 DECODE( prod.item_type, 'OTHERS', ozf_cust_facts_pvt.get_cust_target (
967 cust.site_use_id,
968 cust.bill_to_site_use_id,
969 time.period_type_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
979 -- Customer Filter
980 -- cust.site_use_code = 'SHIP_TO'
981 cust.allocation_for = 'FUND'
982 AND cust.allocation_for_id = quota.fund_id
983 AND cust.site_use_id = p_ship_to_site_use_id
984 AND cust.bill_to_site_use_id = p_bill_to_site_use_id
985 -- Product Filter
986 AND prod.allocation_for = 'CUST'
987 AND prod.allocation_for_id = cust.account_allocation_id
988 AND prod.item_type = p_item_type
989 AND prod.item_id = p_item_id
990 -- Time Filter
991 AND time.allocation_for = 'PROD'
992 AND time.allocation_for_id = prod.product_allocation_id
993 AND time.period_type_id = p_period_type_id
994 AND time.time_id = p_time_id
995 -- Cancelled Quota allocations must be ignored
996 AND quota.fund_type = 'QUOTA'
997 AND quota.status_code <> 'CANCELLED'
998 -- This date filter must be removed because users can have quota for
999 -- Q1, Q2, Q3, Q4 and in Q4 the total year quota
1000 -- must be the sum of all these quotas
1001 -- AND p_report_date BETWEEN quota.start_date_active AND quota.end_date_active
1002 AND quota.parent_fund_id IS NOT NULL
1003 -- Pick only quotas for leaf nodes.
1004 -- This filter is not required since quotas are always generated for leaf nodes
1005 AND NOT EXISTS ( SELECT 1
1006 FROM ozf_funds_all_b dd
1007 WHERE dd.parent_fund_id = quota.fund_id );
1008
1009 CURSOR period_time_id_csr IS
1010 SELECT ent_period_id,
1011 ent_qtr_id,
1012 ent_year_id
1013 FROM ozf_time_day
1014 WHERE report_date = p_report_date;
1015
1016 l_current_period_time_id NUMBER;
1017 l_current_qtr_time_id NUMBER;
1018 l_current_year_time_id NUMBER;
1019
1020 l_current_period_target NUMBER;
1021 l_current_qtr_target NUMBER;
1022 l_current_year_target NUMBER;
1023
1024 l_current_temp_qtr_target NUMBER;
1025 l_current_temp_year_target NUMBER;
1026
1027 CURSOR period_id_csr(p_qtr_id NUMBER) IS
1028 SELECT ent_period_id
1029 FROM ozf_time_ent_period
1030 WHERE ent_qtr_id = p_qtr_id;
1031
1032 CURSOR qtr_id_csr(p_year_id NUMBER) IS
1033 SELECT ent_qtr_id
1034 FROM ozf_time_ent_qtr
1035 WHERE ent_year_id = p_year_id;
1036
1037 CURSOR period_id_yr_csr(p_year_id NUMBER) IS
1038 SELECT ent_period_id
1039 FROM ozf_time_ent_period
1040 WHERE ent_year_id = p_year_id;
1041
1042 BEGIN
1043
1044 ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' (-)') ;
1045
1046 IF FND_API.to_boolean(p_init_msg_list) THEN
1047 FND_MSG_PUB.initialize;
1048 END IF;
1049
1050 IF NOT FND_API.compatible_api_call(l_api_version,
1051 p_api_version,
1052 l_api_name,
1053 g_pkg_name)
1054 THEN
1055 RAISE FND_API.g_exc_unexpected_error;
1056 END IF;
1057
1058 x_return_status := FND_API.g_ret_sts_success;
1059 --
1060
1061 -- Updates the following columns
1062 -- current_period_target
1063 -- current_qtr_target
1064 -- current_year_target
1065
1066 -- Pseudo code
1067 ---------------------------------------------
1068 -- Get current period_id, quarter_id, year_id
1069 -- We only support Month(32), Quarter(64) and Year Spreads(128)
1070 --
1071 -- Get target for current period
1072 --
1073 -- Get target for current quarter.
1074 -- IF no data found for quarter
1075 -- Then get it for all the months in a Quarter
1076
1077 -- Get target for current year
1078 -- IF no data found for year
1079 -- Then get it for all Quarters in a Year
1080 -- IF still no data found
1081 -- Then get it for all months in a Year
1082 ----------------------------------------------
1083
1084 -- Fetch current period, quarter,year time_ids
1085 OPEN period_time_id_csr;
1086 FETCH period_time_id_csr INTO l_current_period_time_id,
1087 l_current_qtr_time_id,
1088 l_current_year_time_id ;
1089 CLOSE period_time_id_csr;
1090
1091
1092 FOR fact IN daily_facts_csr
1093 LOOP
1094 --
1095 -- Get target for current period
1096 --
1097
1098 OPEN target_csr (fact.product_attribute,
1099 fact.product_attr_value,
1100 fact.ship_to_site_use_id,
1101 fact.bill_to_site_use_id,
1102 32,
1103 l_current_period_time_id);
1104 FETCH target_csr INTO l_current_period_target;
1105 CLOSE target_csr;
1106
1107 --
1108 -- Get target for current quarter
1109 --
1110 OPEN target_csr (fact.product_attribute,
1111 fact.product_attr_value,
1112 fact.ship_to_site_use_id,
1113 fact.bill_to_site_use_id,
1114 64,
1115 l_current_qtr_time_id);
1116 FETCH target_csr INTO l_current_qtr_target;
1117 CLOSE target_csr;
1118
1119 IF l_current_qtr_target IS NULL
1120 THEN
1121 -- Get sum of all periods in the quarter
1122 l_current_temp_qtr_target := 0;
1123 FOR period IN period_id_csr(l_current_qtr_time_id)
1124 LOOP
1125 --
1126 OPEN target_csr (fact.product_attribute,
1127 fact.product_attr_value,
1128 fact.ship_to_site_use_id,
1129 fact.bill_to_site_use_id,
1130 32,
1131 period.ent_period_id);
1132 FETCH target_csr INTO l_current_temp_qtr_target;
1133 CLOSE target_csr;
1134
1135 l_current_qtr_target := NVL(l_current_qtr_target,0)
1136 + NVL(l_current_temp_qtr_target,0);
1137 --
1138 END LOOP;
1139 --
1140 END IF;
1141
1142 --
1143 -- Get target for current year
1144 --
1145 -- Targets available in months + Targets available in qtrs
1146 -- Targets cannot be allocated in Years ?
1147
1148 l_current_temp_year_target := 0;
1149 -- Get all available targets in months for the current year
1150 FOR period IN period_id_yr_csr(l_current_year_time_id)
1151 LOOP
1152 OPEN target_csr (fact.product_attribute,
1153 fact.product_attr_value,
1154 fact.ship_to_site_use_id,
1155 fact.bill_to_site_use_id,
1156 32,
1157 period.ent_period_id);
1158 FETCH target_csr INTO l_current_temp_year_target;
1159 CLOSE target_csr;
1160 l_current_year_target := NVL(l_current_year_target,0)
1161 + NVL(l_current_temp_year_target,0);
1162 END LOOP;
1163
1164
1165 -- Get all available targets in Quarters for the current year
1166 FOR qtr IN qtr_id_csr(l_current_year_time_id)
1167 LOOP
1168 OPEN target_csr (fact.product_attribute,
1169 fact.product_attr_value,
1170 fact.ship_to_site_use_id,
1171 fact.bill_to_site_use_id,
1172 64,
1173 qtr.ent_qtr_id);
1174 FETCH target_csr INTO l_current_temp_year_target ;
1175 CLOSE target_csr;
1176 l_current_year_target := NVL(l_current_year_target,0)
1177 + NVL(l_current_temp_year_target,0);
1178 END LOOP;
1179
1180 UPDATE ozf_cust_daily_facts
1181 SET current_period_target = NVL(l_current_period_target,0)
1182 ,current_qtr_target = l_current_qtr_target
1183 ,current_year_target = l_current_year_target
1184 WHERE cust_daily_fact_id = fact.cust_daily_fact_id;
1185
1186 l_current_period_target := NULL;
1187 l_current_qtr_target := NULL;
1188 l_current_year_target := NULL;
1189
1190 END LOOP;
1191 --
1192 ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' (+)');
1193
1194 EXCEPTION
1195 WHEN FND_API.G_EXC_ERROR THEN
1196 x_return_status := FND_API.g_ret_sts_error ;
1197 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
1198 p_count => x_msg_count,
1199 p_data => x_msg_data);
1200
1201 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1202 x_return_status := FND_API.g_ret_sts_unexp_error ;
1203 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
1204 p_count => x_msg_count,
1205 p_data => x_msg_data);
1206
1207 WHEN OTHERS THEN
1208 x_return_status := FND_API.g_ret_sts_unexp_error ;
1209 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
1210 p_count => x_msg_count,
1211 p_data => x_msg_data);
1212 END refresh_target_info;
1213
1214
1215
1216 PROCEDURE insert_kpi(
1217 p_resource_id IN NUMBER,
1218 p_report_date IN DATE,
1219 p_current_period_target IN NUMBER,
1220 p_current_qtr_target IN NUMBER,
1221 p_current_year_target IN NUMBER,
1222 p_current_period_sales IN NUMBER,
1223 p_current_qtr_sales IN NUMBER,
1224 p_current_year_sales IN NUMBER ) AS
1225
1226 CURSOR period_name_csr IS
1227 SELECT ent_period_id,
1228 ozf_time_api_pvt.get_period_name(ent_period_id, 32) period_name,
1229 ent_qtr_id,
1230 ozf_time_api_pvt.get_period_name(ent_qtr_id, 64) qtr_name,
1231 ent_year_id,
1232 ozf_time_api_pvt.get_period_name(ent_year_id, 128) year_name
1233 FROM ozf_time_day
1234 WHERE report_date = p_report_date;
1235
1236 l_kpi_rec_count NUMBER;
1237 l_ent_period_id NUMBER;
1238 l_ent_period_name VARCHAR2(100);
1239 l_ent_qtr_id NUMBER;
1240 l_ent_qtr_name VARCHAR2(100);
1241 l_ent_year_id NUMBER;
1242 l_ent_year_name VARCHAR2(100);
1243
1244 l_period_type_id NUMBER;
1245 l_time_id NUMBER;
1246 l_kpi_name VARCHAR2(100);
1247 l_kpi_value NUMBER;
1248
1249 l_quota VARCHAR2(100):= FND_MESSAGE.GET_STRING('OZF','OZF_DASHB_KPI_QUOTA');
1250 l_quota_met VARCHAR2(100):= FND_MESSAGE.GET_STRING('OZF','OZF_DASHB_KPI_QUOTA_MET');
1251 l_mtd_sales VARCHAR2(100):= FND_MESSAGE.GET_STRING('OZF','OZF_DASHB_KPI_MTD_SALES');
1252 l_qtd_sales VARCHAR2(100):= FND_MESSAGE.GET_STRING('OZF','OZF_DASHB_KPI_QTD_SALES');
1253 l_ytd_sales VARCHAR2(100):= FND_MESSAGE.GET_STRING('OZF','OZF_DASHB_KPI_YTD_SALES');
1254
1255 BEGIN
1256
1257 ozf_utility_pvt.write_conc_log('Private API: ' || 'Insert_Kpi' || ' (-)') ;
1258
1259 -- inanaiah: bug 4912723 - commented as deletion happens in refresh_kpi_info
1260 /*
1261 DELETE FROM ozf_dashb_daily_kpi
1262 WHERE report_date = p_report_date
1263 AND resource_id = p_resource_id;
1264 */
1265
1266 OPEN period_name_csr;
1267 FETCH period_name_csr INTO l_ent_period_id,
1268 l_ent_period_name,
1269 l_ent_qtr_id,
1270 l_ent_qtr_name,
1271 l_ent_year_id,
1272 l_ent_year_name;
1273 CLOSE period_name_csr;
1274
1275 ozf_utility_pvt.write_conc_log('-- p_resource_id : '|| p_resource_id ) ;
1276 ozf_utility_pvt.write_conc_log('-- p_current_period_target : '|| p_current_period_target ) ;
1277 ozf_utility_pvt.write_conc_log('-- p_current_qtr_target : '|| p_current_qtr_target ) ;
1278 ozf_utility_pvt.write_conc_log('-- p_current_year_target : '|| p_current_year_target ) ;
1279 ozf_utility_pvt.write_conc_log('-- p_current_period_sales : '|| p_current_period_sales ) ;
1280 ozf_utility_pvt.write_conc_log('-- p_current_qtr_sales : '|| p_current_qtr_sales ) ;
1281 ozf_utility_pvt.write_conc_log('-- p_current_year_sales : '|| p_current_year_sales ) ;
1282
1283 l_kpi_rec_count := 0;
1284 LOOP
1285 l_kpi_rec_count := l_kpi_rec_count + 1;
1286 IF l_kpi_rec_count > 9
1287 THEN
1288 EXIT;
1289 END IF;
1290
1291 IF l_kpi_rec_count = 1
1292 THEN
1293 -- Period Quota
1294 l_kpi_name := l_ent_period_name || ' '|| l_quota ;
1295 l_kpi_value := NVL(p_current_period_target,0) ;
1296 l_period_type_id := 32 ;
1297 l_time_id := l_ent_period_id ;
1298 --
1299 ELSIF l_kpi_rec_count = 2
1300 THEN
1301 -- Period Sales
1302 l_kpi_name := l_mtd_sales ;
1303 l_kpi_value := NVL(p_current_period_sales,0) ;
1304 l_period_type_id := 32 ;
1305 l_time_id := l_ent_period_id ;
1306 --
1307 ELSIF l_kpi_rec_count = 3
1308 THEN
1309 -- % Period Quota Met
1310 l_kpi_name := l_ent_period_name|| ' ' || l_quota_met;
1311 IF NVL(p_current_period_target,0) > 0
1312 THEN
1313 l_kpi_value := round((100*NVL(p_current_period_sales,0)/p_current_period_target),2) ;
1314 ELSE
1315 l_kpi_value := 0;
1316 END IF;
1317 l_period_type_id := 32;
1318 l_time_id := l_ent_period_id;
1319 --
1320 ELSIF l_kpi_rec_count = 4
1321 THEN
1322 -- Quarter Quota
1323 l_kpi_name := l_ent_qtr_name || ' ' || l_Quota ;
1324 l_kpi_value := NVL(p_current_qtr_target,0);
1325 l_period_type_id := 64;
1326 l_time_id := l_ent_qtr_id;
1327 --
1328 ELSIF l_kpi_rec_count = 5
1329 THEN
1330 -- Quarter Sales
1331 l_kpi_name := l_QTD_Sales;
1332 l_kpi_value := NVL(p_current_qtr_sales,0) ;
1333 l_period_type_id := 64;
1334 l_time_id := l_ent_qtr_id;
1335 --
1336 ELSIF l_kpi_rec_count = 6
1337 THEN
1338 -- Quarter Quota % Met
1339 l_kpi_name := l_ent_qtr_name || ' ' || l_Quota_Met;
1340 IF NVL(p_current_qtr_target,0) > 0
1341 THEN
1342 l_kpi_value := round((100*NVL(p_current_qtr_sales,0)/p_current_qtr_target),2) ;
1343 ELSE
1344 l_kpi_value := 0;
1345 END IF;
1346 l_period_type_id := 64;
1347 l_time_id := l_ent_qtr_id;
1348 --
1349 ELSIF l_kpi_rec_count = 7
1350 THEN
1351 -- Year Quota
1352 l_kpi_name := l_ent_year_name || ' ' || l_Quota;
1353 l_kpi_value := NVL(p_current_year_target,0);
1354 l_period_type_id := 128;
1355 l_time_id := l_ent_year_id;
1356 --
1357 ELSIF l_kpi_rec_count = 8
1358 THEN
1359 --
1360 l_kpi_name := l_YTD_Sales;
1361 l_kpi_value := NVL(p_current_year_sales,0);
1362 l_period_type_id := 128;
1363 l_time_id := l_ent_year_id;
1364 --
1365 ELSIF l_kpi_rec_count = 9
1366 THEN
1367 --
1368 l_kpi_name := l_ent_year_name || ' ' || l_Quota_Met;
1369 IF NVL(p_current_year_target,0) > 0
1370 THEN
1371 l_kpi_value := round((100*NVL(p_current_year_sales,0)/p_current_year_target),2) ;
1372 ELSE
1373 l_kpi_value := 0;
1374 END IF;
1375 l_period_type_id := 128;
1376 l_time_id := l_ent_year_id;
1377 --
1378 END IF;
1379
1380 INSERT INTO ozf_dashb_daily_kpi(
1381 dashb_daily_kpi_id,
1382 report_date,
1383 resource_id,
1384 period_type_id,
1385 time_id,
1386 sequence_number,
1387 kpi_name,
1388 kpi_value )
1389 VALUES (ozf_dashb_daily_kpi_s.nextval,
1390 p_report_date,
1391 p_resource_id,
1392 l_period_type_id,
1393 l_time_id,
1394 l_kpi_rec_count,
1395 l_kpi_name,
1396 l_kpi_value );
1397
1398 END LOOP;
1399
1400 --
1401 ozf_utility_pvt.write_conc_log('Private API: ' || 'Insert_Kpi' || ' (+)');
1402
1403 END insert_kpi;
1404
1405
1406 PROCEDURE refresh_kpi_info(
1407 p_api_version IN NUMBER,
1408 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1409 p_report_date IN DATE,
1410 x_return_status OUT NOCOPY VARCHAR2,
1411 x_msg_count OUT NOCOPY NUMBER,
1412 x_msg_data OUT NOCOPY VARCHAR2) AS
1413
1414 l_api_version CONSTANT NUMBER := 1.0;
1415 l_api_name CONSTANT VARCHAR2(30) := 'refresh_target_info';
1416 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1417 l_return_status VARCHAR2(1);
1418
1419 -- Cursor to get all resources
1420 CURSOR active_resources_csr IS
1421 SELECT DISTINCT a.owner
1422 FROM ozf_funds_all_b a
1423 WHERE a.fund_type = 'QUOTA'
1424 AND a.status_code <> 'CANCELLED' ;
1425
1426
1427 -- Get Time Level Target Allocation for all quotas of a user
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
1437 AND b.period_type_id = p_period_type_id
1438 AND b.time_id = p_time_id
1439 AND a.allocation_for = 'FUND'
1440 AND NVL(a.account_status, 'X') <> 'D'
1441 -- R12: Do not consider UnAllocated Rows
1442 AND a.parent_party_id <> -9999
1443 AND a.allocation_for_id IN ( -- Get leaf node quotas for this resource owns
1444 SELECT aa.fund_id
1445 FROM ozf_funds_all_b aa
1446 WHERE aa.owner = p_resource_id
1447 AND aa.fund_type = 'QUOTA'
1448 AND aa.status_code <> 'CANCELLED'
1449 AND NOT EXISTS ( SELECT 1
1450 FROM ozf_funds_all_b bb
1451 WHERE bb.parent_fund_id = aa.fund_id )
1452 --
1453 UNION ALL -- Get all leaf node quotas in the hierarchy of this resource
1454 --
1455 SELECT aa.fund_id
1456 FROM ozf_funds_all_b aa
1457 WHERE aa.fund_type = 'QUOTA'
1458 AND aa.status_code <> 'CANCELLED'
1459 CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
1460 START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
1461 FROM ozf_funds_all_b bb
1462 WHERE bb.owner = p_resource_id
1463 AND bb.fund_type = 'QUOTA'
1464 AND bb.status_code <> 'CANCELLED' )
1465 );
1466
1467
1468 /*
1469 CURSOR sales_for_quota_csr( p_resource_id NUMBER,
1470 p_report_date DATE) IS
1471 SELECT SUM(fact.ptd_sales) MTD_SALES,
1472 SUM(fact.qtd_sales) QTD_SALES,
1473 SUM(fact.ytd_sales) YTD_SALES
1474 FROM ozf_cust_daily_facts fact,
1475 (
1476 SELECT DISTINCT c.site_use_id
1477 FROM jtf_terr_rsc_all b
1478 ,ams_party_market_segments c
1479 WHERE b.resource_id = p_resource_id
1480 AND b.primary_contact_flag = 'Y'
1481 AND c.market_qualifier_type = 'TERRITORY'
1482 AND c.market_qualifier_reference = b.terr_id
1483 AND c.site_use_code = 'SHIP_TO'
1484 ) site
1485 WHERE fact.report_date = p_report_date
1486 AND fact.ship_to_site_use_id = site.site_use_id
1487 AND fact.product_attribute <> 'OTHERS' ;
1488 */
1489
1490 CURSOR sales_for_quota_csr(p_resource_id NUMBER,
1491 p_report_date DATE) IS
1492 SELECT
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'
1502 AND fact.product_attribute = prod.item_type
1503 AND fact.product_attr_value = prod.item_id
1504 AND prod.allocation_for = 'CUST'
1505 AND prod.allocation_for_id = site.account_allocation_id
1506 AND site.allocation_for = 'FUND'
1507 AND NVL(site.account_status, 'X') <> 'D'
1508 -- R12: Do not consider UnAllocated Rows
1509 AND site.parent_party_id <> -9999
1510 AND site.allocation_for_id in (
1511 SELECT aa.fund_id
1512 FROM ozf_funds_all_b aa
1513 WHERE aa.owner = p_resource_id
1514 AND aa.fund_type = 'QUOTA'
1515 AND aa.status_code <> 'CANCELLED'
1516 AND NOT EXISTS ( SELECT 1
1517 FROM ozf_funds_all_b bb
1518 WHERE bb.parent_fund_id = aa.fund_id )
1519 --
1520 UNION ALL -- Get all leaf node quotas in the hierarchy of this resource
1521 --
1522 SELECT aa.fund_id
1523 FROM ozf_funds_all_b aa
1524 WHERE aa.fund_type = 'QUOTA'
1525 AND aa.status_code <> 'CANCELLED'
1526 CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
1527 START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
1528 FROM ozf_funds_all_b bb
1529 WHERE bb.owner = p_resource_id
1530 AND bb.fund_type = 'QUOTA'
1531 AND bb.status_code <> 'CANCELLED' )
1532 ) ;
1533
1534
1535
1536 -- Time Cursors
1537
1538 -- Get Current period, quarter, year ids
1539 CURSOR period_time_id_csr IS
1540 SELECT ent_period_id,
1541 ent_qtr_id,
1542 ent_year_id
1543 FROM ozf_time_day
1544 WHERE report_date = p_report_date;
1545
1546 -- Periods in a Quarter
1547 CURSOR period_id_csr(p_qtr_id NUMBER) IS
1548 SELECT ent_period_id
1549 FROM ozf_time_ent_period
1550 WHERE ent_qtr_id = p_qtr_id;
1551
1552 -- Quarters in a Year
1553 CURSOR qtr_id_csr(p_year_id NUMBER) IS
1554 SELECT ent_qtr_id
1555 FROM ozf_time_ent_qtr
1556 WHERE ent_year_id = p_year_id;
1557
1558 -- Periods in a Year
1559 CURSOR period_id_yr_csr(p_year_id NUMBER) IS
1560 SELECT ent_period_id
1561 FROM ozf_time_ent_period
1562 WHERE ent_year_id = p_year_id;
1563
1564 l_current_period_time_id NUMBER;
1565 l_current_qtr_time_id NUMBER;
1566 l_current_year_time_id NUMBER;
1567
1568 l_current_temp_qtr_target NUMBER;
1569 l_current_temp_year_target NUMBER;
1570
1571 l_current_period_target NUMBER;
1572 l_current_qtr_target NUMBER;
1573 l_current_year_target NUMBER;
1574
1575 l_current_period_sales NUMBER;
1576 l_current_qtr_sales NUMBER;
1577 l_current_year_sales NUMBER;
1578
1579 BEGIN
1580
1581 ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' (-)') ;
1582
1583 IF FND_API.to_boolean(p_init_msg_list) THEN
1584 FND_MSG_PUB.initialize;
1585 END IF;
1586
1587 IF NOT FND_API.compatible_api_call(l_api_version,
1588 p_api_version,
1589 l_api_name,
1590 g_pkg_name)
1591 THEN
1592 RAISE FND_API.g_exc_unexpected_error;
1593 END IF;
1594
1595 x_return_status := FND_API.g_ret_sts_success;
1596
1597 -- Fetch current periods
1598
1599 OPEN period_time_id_csr;
1600 FETCH period_time_id_csr INTO l_current_period_time_id,
1601 l_current_qtr_time_id,
1602 l_current_year_time_id;
1603 CLOSE period_time_id_csr;
1604
1605 -- inanaiah: bug 4912723 - delete all the records otherwise target info for cancelled quotas
1606 -- will exist in ozf_dashb_daily_kpi and thus get populated into OZF_RES_CUST_PROD_FACTS.
1607 -- Removed the deletion based on report_date and resource_id in insert_kpi proc.
1608 DELETE FROM ozf_dashb_daily_kpi
1609 WHERE report_date = p_report_date;
1610
1611 -- Process KPI for each resource
1612 FOR res IN active_resources_csr
1613 LOOP
1614 --
1615 -- Fetch Quota Information for the User
1616 --
1617 l_current_period_target := 0;
1618 l_current_qtr_target := 0;
1619 l_current_year_target := 0;
1620 l_current_period_sales := 0;
1621 l_current_qtr_sales := 0;
1622 l_current_year_sales := 0;
1623
1624 -- Get any targets for the current time period
1625 OPEN target_for_quota_csr(res.owner,
1626 32,
1627 l_current_period_time_id);
1628 FETCH target_for_quota_csr INTO l_current_period_target;
1629 CLOSE target_for_quota_csr;
1630
1631 -- Get any targets for the current quarter
1632 OPEN target_for_quota_csr(res.owner,
1633 64,
1634 l_current_qtr_time_id);
1635 FETCH target_for_quota_csr INTO l_current_qtr_target;
1636 CLOSE target_for_quota_csr;
1637
1638 -- Check if targets exist for months in the current quarter and quarter itself
1639 -- This can be possible if a user has two quotas.
1640 -- One in Months, One in Quarter
1641
1642 l_current_temp_qtr_target := 0;
1643 FOR period IN period_id_csr(l_current_qtr_time_id)
1644 LOOP
1645 OPEN target_for_quota_csr ( res.owner,
1646 32,
1647 period.ent_period_id);
1648 FETCH target_for_quota_csr INTO l_current_temp_qtr_target;
1649 CLOSE target_for_quota_csr;
1650
1651 -- Consolidate the period target with quarter target, if any
1652 l_current_qtr_target := NVL(l_current_qtr_target,0)
1653 + NVL(l_current_temp_qtr_target,0);
1654 END LOOP;
1655
1656 -- Get all available targets in months for the current year
1657 l_current_temp_year_target := 0;
1658 FOR period IN period_id_yr_csr(l_current_year_time_id)
1659 LOOP
1660 OPEN target_for_quota_csr (res.owner,
1661 32,
1662 period.ent_period_id);
1663 FETCH target_for_quota_csr INTO l_current_temp_year_target;
1664 CLOSE target_for_quota_csr;
1665
1666 l_current_year_target := NVL(l_current_year_target,0)
1667 + NVL(l_current_temp_year_target,0);
1668 END LOOP;
1669
1670
1671 -- Get all available targets in Quarters for the current year
1672 FOR qtr IN qtr_id_csr(l_current_year_time_id)
1673 LOOP
1674 OPEN target_for_quota_csr ( res.owner,
1675 64,
1676 qtr.ent_qtr_id);
1677 FETCH target_for_quota_csr INTO l_current_temp_year_target ;
1678 CLOSE target_for_quota_csr;
1679
1680 l_current_year_target := NVL(l_current_year_target,0)
1681 + NVL(l_current_temp_year_target,0);
1682 END LOOP;
1683
1684 --
1685 -- Fetch Sales Information for the User
1686 --
1687
1688 OPEN sales_for_quota_csr( res.owner,
1689 p_report_date);
1690 FETCH sales_for_quota_csr INTO l_current_period_sales,
1691 l_current_qtr_sales,
1692 l_current_year_sales;
1693 CLOSE sales_for_quota_csr;
1694
1695 --
1696 -- Insert KPI data
1697 --
1698
1699 insert_kpi ( res.owner,
1700 p_report_date,
1701 l_current_period_target,
1702 l_current_qtr_target,
1703 l_current_year_target,
1704 l_current_period_sales,
1705 l_current_qtr_sales,
1706 l_current_year_sales);
1707
1708 END LOOP; -- Process next resource
1709
1710 ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' (+)');
1711
1712 EXCEPTION
1713
1714 WHEN FND_API.G_EXC_ERROR THEN
1715 x_return_status := FND_API.g_ret_sts_error ;
1716 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
1717 p_count => x_msg_count,
1718 p_data => x_msg_data);
1719
1720 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1721 x_return_status := FND_API.g_ret_sts_unexp_error ;
1722 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
1723 p_count => x_msg_count,
1724 p_data => x_msg_data);
1725
1726 WHEN OTHERS THEN
1727 x_return_status := FND_API.g_ret_sts_unexp_error ;
1728 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
1729 p_count => x_msg_count,
1730 p_data => x_msg_data);
1731
1732 END refresh_kpi_info;
1733
1734
1735 PROCEDURE update_sales_info(
1736 p_api_version IN NUMBER,
1737 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1738 p_report_date IN DATE,
1739 x_return_status OUT NOCOPY VARCHAR2,
1740 x_msg_count OUT NOCOPY NUMBER,
1741 x_msg_data OUT NOCOPY VARCHAR2) AS
1742
1743 l_api_version CONSTANT NUMBER := 1.0;
1744 l_api_name CONSTANT VARCHAR2(30) := 'update_sales_info';
1745 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1746 l_return_status VARCHAR2(1);
1747
1748
1749 CURSOR leaf_level_quotas_csr IS
1750 SELECT a.fund_id
1751 FROM ozf_funds_all_b a
1752 WHERE a.status_code = 'ACTIVE'
1753 AND a.fund_type = 'QUOTA'
1754 -- and a.fund_id in ( 10746 , 10745)
1755 AND NOT EXISTS ( SELECT 1
1756 FROM ozf_funds_all_b b
1757 WHERE b.parent_fund_id = a.fund_id);
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
1767 CURSOR acct_prod_allocs_csr ( p_account_allocation_id NUMBER) IS
1768 SELECT product_allocation_id,
1769 item_type,
1770 item_id
1771 FROM ozf_product_allocations
1772 WHERE allocation_for = 'CUST'
1773 AND allocation_for_id = p_account_allocation_id
1774 ORDER BY item_id DESC ;
1775
1776 CURSOR prod_time_allocs_csr ( p_product_allocation_id NUMBER) IS
1777 SELECT time_allocation_id,
1778 time_id,
1779 period_type_id
1780 FROM ozf_time_allocations
1781 WHERE allocation_for = 'PROD'
1782 AND allocation_for_id = p_product_allocation_id;
1783
1784 CURSOR current_periods_csr IS
1785 SELECT ent_period_id,
1786 ent_qtr_id
1787 FROM ozf_time_day
1788 WHERE report_date = p_report_date;
1789
1790 CURSOR sales_csr( p_time_id NUMBER,
1791 p_site_use_id NUMBER,
1792 p_inventory_item_id NUMBER ) IS
1793 SELECT NVL(SUM(sales),0)
1794 FROM ozf_order_sales_v
1795 WHERE time_id = p_time_id
1796 AND ship_to_site_use_id = p_site_use_id
1797 AND inventory_item_id = DECODE(p_inventory_item_id,-9999,inventory_item_id,p_inventory_item_id);
1798
1799 CURSOR xtd_sales_csr ( p_record_type_id NUMBER,
1800 p_site_use_id NUMBER,
1801 p_inventory_item_id NUMBER) IS
1802 SELECT NVL(SUM(sales),0)
1803 FROM ozf_time_rpt_struct a,
1804 ozf_order_sales_v b
1805 WHERE a.report_date = p_report_date
1806 AND BITAND(a.record_type_id, p_record_type_id) = a.record_type_id
1807 AND a.time_id = b.time_id
1808 AND b.ship_to_site_use_id = p_site_use_id
1809 AND b.inventory_item_id = DECODE(p_inventory_item_id,-9999, b.inventory_item_id,p_inventory_item_id);
1810
1811 CURSOR items_csr (p_item_type VARCHAR2,
1812 p_item_id NUMBER) IS
1813 SELECT inventory_item_id
1814 FROM mtl_item_categories mtl,
1815 eni_prod_denorm_hrchy_v eni
1816 WHERE mtl.category_set_id = eni.category_set_id
1817 AND mtl.category_id = eni.child_id
1818 AND mtl.organization_id = fnd_profile.value('QP_ORGANIZATION_ID')
1819 AND eni.parent_id = p_item_id
1820 AND 'PRICING_ATTRIBUTE2' = p_item_type
1821 UNION ALL
1822 SELECT p_item_id inventory_item_id
1823 FROM dual
1824 WHERE 'PRICING_ATTRIBUTE1' = p_item_type
1825 UNION ALL
1826 SELECT p_item_id inventory_item_id
1827 FROM dual
1828 WHERE 'OTHERS' = p_item_type;
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'
1838 AND b.allocation_for_id = a.account_allocation_id
1839 AND b.item_type <> 'OTHERS'
1840 AND c.allocation_for = 'PROD'
1841 AND c.allocation_for_id = b.product_allocation_id
1842 AND c.time_id = p_time_id;
1843
1844 -- Local Variables
1845
1846 l_current_period_id NUMBER;
1847 l_current_qtr_id NUMBER;
1848 l_record_type_id NUMBER;
1849 l_sales NUMBER;
1850 l_item_sales NUMBER;
1851 l_product_sales NUMBER;
1852 l_acct_sales NUMBER;
1853 l_quota_sales NUMBER;
1854 l_acct_time_sales NUMBER;
1855
1856 BEGIN
1857 ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' (-)') ;
1858
1859 IF FND_API.to_boolean(p_init_msg_list) THEN
1860 FND_MSG_PUB.initialize;
1861 END IF;
1862
1863 IF NOT FND_API.compatible_api_call(l_api_version,
1864 p_api_version,
1865 l_api_name,
1866 g_pkg_name)
1867 THEN
1868 RAISE FND_API.g_exc_unexpected_error;
1869 END IF;
1870
1871 x_return_status := FND_API.g_ret_sts_success;
1872 -- Package Body Start
1873
1874 -- Get the current period and quarter's time_ids
1875 OPEN current_periods_csr;
1876 FETCH current_periods_csr INTO l_current_period_id, l_current_qtr_id;
1877 CLOSE current_periods_csr;
1878
1879 -- dbms_output.put_line(' p_report_date l_current_period_id , l_current_qtr_id '|| p_report_date || ' ' || l_current_period_id || ' ' || l_current_qtr_id);
1880
1881 FOR quota IN leaf_level_quotas_csr
1882 LOOP
1883 -- dbms_output.put_line(' fund_id '|| quota.fund_id );
1884 --
1885 l_quota_sales := 0;
1886 FOR acct IN quota_acct_allocs_csr (quota.fund_id)
1887 LOOP
1888 --
1889 l_acct_sales := 0;
1890 FOR prod IN acct_prod_allocs_csr (acct.account_allocation_id)
1891 LOOP
1892 --
1893 l_product_sales := 0;
1894 FOR time IN prod_time_allocs_csr(prod.product_allocation_id)
1895 LOOP
1896
1897 l_sales := 0;
1898 l_item_sales := 0;
1899
1900 FOR i IN items_csr( prod.item_type, prod.item_id)
1901 LOOP
1902 --
1903 -- Allocations can exist only for Periods and Quarters
1904 -- If current, then sales in mv will not exist for the time_id
1905 -- Instead, we need to XTD sales ie MTD or QTD sales
1906 --
1907
1908 IF (
1909 (time.period_type_id = 32 AND time.time_id = l_current_period_id)
1910 OR
1911 (time.period_type_id = 64 AND time.time_id = l_current_qtr_id)
1912 )
1913 THEN
1914 --
1915 -- Get XTD Sales for time_di
1916 IF time.period_type_id = 32
1917 THEN
1918 l_record_type_id := 23;
1919 ELSE
1920 l_record_type_id := 55;
1921 END IF;
1922
1923 OPEN xtd_sales_csr( l_record_type_id ,
1924 acct.site_use_id ,
1925 i.inventory_item_id );
1926 FETCH xtd_sales_csr INTO l_item_sales;
1927 CLOSE xtd_sales_csr;
1928
1929 ELSE
1930 OPEN sales_csr( time.time_id ,
1931 acct.site_use_id ,
1932 i.inventory_item_id );
1933 FETCH sales_csr INTO l_item_sales;
1934 CLOSE sales_csr;
1935
1936 END IF;
1937 --
1938 l_sales := l_sales + l_item_sales;
1939 --
1940 -- dbms_output.put_line('l_sales ' || time.time_id ||' ' || acct.site_use_id || ' ' || prod.item_id || ' ' || l_sales);
1941 END LOOP; -- End items_csr
1942
1943 IF prod.item_type = 'OTHERS'
1944 THEN
1945
1946 OPEN acct_time_sales_csr(acct.account_allocation_id,
1947 time.time_id);
1948 FETCH acct_time_sales_csr INTO l_acct_time_sales;
1949 CLOSE acct_time_sales_csr;
1950
1951 -- dbms_output.put_line('l_acct_time_sales ' || l_acct_time_sales);
1952
1953 l_sales := l_sales - l_acct_time_sales;
1954 -- dbms_output.put_line('l_sales for others '|| l_sales );
1955
1956 END IF;
1957
1958 UPDATE ozf_time_allocations
1959 SET lysp_sales = l_sales
1960 WHERE time_allocation_id = time.time_allocation_id;
1961
1962 l_product_sales := l_product_sales + l_sales;
1963
1964 END LOOP; -- End of prod_time_allocs_csr
1965 --
1966 UPDATE ozf_product_allocations
1967 SET lysp_sales = l_product_sales
1968 WHERE product_allocation_id = prod.product_allocation_id;
1969
1970 l_acct_sales := l_acct_sales + l_product_sales;
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;
1980 --
1981 -- dbms_output.put_line('l_acct_sales '|| acct.account_allocation_id || ' ' || l_acct_sales);
1982 -- Before moving to next acccout, update the time allocations
1983 -- for this account.
1984
1985 UPDATE ozf_time_allocations c
1986 SET lysp_sales = ( SELECT sum(b.lysp_sales)
1987 FROM ozf_product_allocations a,
1988 ozf_time_allocations b
1989 WHERE a.allocation_for = 'CUST'
1990 AND a.allocation_for_id = acct.account_allocation_id
1991 AND b.allocation_for = 'PROD'
1992 AND b.allocation_for_id = a.product_allocation_id
1993 AND b.time_id = c.time_id )
1994 WHERE c.allocation_for = 'CUST'
1995 AND c.allocation_for_id = acct.account_allocation_id ;
1996
1997
1998 END LOOP; -- End of quota_acct_allocs_csr
1999 --
2000 UPDATE ozf_funds_all_b
2001 SET utilized_amt = l_quota_sales
2002 WHERE fund_id = quota.fund_id;
2003 --
2004 -- dbms_output.put_line('l_quota_sales '|| l_quota_sales);
2005
2006 END LOOP; -- End of leaf_level_quotas_csr
2007
2008 -- Now that all leaf level quotas have been proceesed, parents will be just rollups
2009
2010
2011
2012 -- End Package Body
2013 ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' (+)');
2014
2015 EXCEPTION
2016
2017 WHEN FND_API.G_EXC_ERROR THEN
2018 x_return_status := FND_API.g_ret_sts_error ;
2019 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
2020 p_count => x_msg_count,
2021 p_data => x_msg_data);
2022
2023 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2024 x_return_status := FND_API.g_ret_sts_unexp_error ;
2025 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
2026 p_count => x_msg_count,
2027 p_data => x_msg_data);
2028
2029 WHEN OTHERS THEN
2030 x_return_status := FND_API.g_ret_sts_unexp_error ;
2031 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
2032 p_count => x_msg_count,
2033 p_data => x_msg_data);
2034
2035 END update_sales_info;
2036
2037
2038 PROCEDURE update_quota_sales_info(
2039 p_api_version IN NUMBER,
2040 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2041 p_report_date IN DATE,
2042 x_return_status OUT NOCOPY VARCHAR2,
2043 x_msg_count OUT NOCOPY NUMBER,
2044 x_msg_data OUT NOCOPY VARCHAR2) AS
2045
2046 l_api_version CONSTANT NUMBER := 1.0;
2047 l_api_name CONSTANT VARCHAR2(30) := 'update_quota_sales_info';
2048 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2049 l_return_status VARCHAR2(1);
2050
2051
2052 CURSOR leaf_node_quotas_csr IS
2053 SELECT a.fund_id,
2054 start_date_active,
2055 end_date_active
2056 FROM ozf_funds_all_b a
2057 WHERE fund_type = 'QUOTA'
2058 AND a.status_code = 'ACTIVE'
2059 AND NOT EXISTS ( SELECT 1
2060 FROM ozf_funds_all_b b
2061 WHERE b.parent_fund_id = a.fund_id );
2062
2063 CURSOR parent_quotas_csr IS
2064 SELECT a.fund_id,
2065 start_date_active,
2066 end_date_active
2067 FROM ozf_funds_all_b a
2068 WHERE fund_type = 'QUOTA'
2069 AND status_code = 'ACTIVE'
2070 AND EXISTS ( SELECT 1
2071 FROM ozf_funds_all_b b
2072 WHERE b.parent_fund_id = a.fund_id );
2073
2074 CURSOR all_leaf_node_quotas_csr( p_fund_id NUMBER ) IS
2075 SELECT a.fund_id,
2076 NVL(a.utilized_amt,0) utilized_amt
2077 FROM ozf_funds_all_b a
2078 WHERE NOT EXISTS ( SELECT 'x'
2079 FROM ozf_funds_all_b b
2080 WHERE b.parent_fund_id = a.fund_id
2081 AND b.status_code = 'ACTIVE' )
2082 AND a.status_code = 'ACTIVE'
2083 CONNECT BY PRIOR a.fund_id = a.parent_fund_id
2084 START WITH a.parent_fund_id = p_fund_id;
2085
2086
2087 CURSOR xtd_sales_csr (p_fund_id NUMBER,
2088 p_as_of_date DATE )
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
2098 AND a.report_date = p_as_of_date
2099 AND BITAND(a.record_type_id, 119) = a.record_type_id ;
2100
2101 l_xtd_end_date NUMBER;
2102 l_xtd_start_date NUMBER;
2103
2104 l_parent_quota_sales NUMBER;
2105 l_tot_parent_quota_sales NUMBER;
2106
2107 BEGIN
2108 -- Standard call to check for call compatibility.
2109 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2110 p_api_version,
2111 l_api_name,
2112 g_pkg_name)
2113 THEN
2114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2115 END IF;
2116
2117 -- Initialize message list if p_init_msg_list is set to TRUE.
2118 FND_MSG_PUB.initialize;
2119
2120 -- Debug Message
2121 ozf_utility_pvt.write_conc_log('Private API: ' || l_api_name || ' (-)');
2122
2123 -- Initialize API return status to SUCCESS
2124 x_return_status := FND_API.G_RET_STS_SUCCESS;
2125
2126 -- Procedure body start
2127 FOR quota IN leaf_node_quotas_csr
2128 LOOP
2129
2130 OPEN xtd_sales_csr ( quota.fund_id, quota.end_date_active );
2131 FETCH xtd_sales_csr INTO l_xtd_end_date ;
2132 CLOSE xtd_sales_csr ;
2133
2134 OPEN xtd_sales_csr ( quota.fund_id, quota.start_date_active );
2135 FETCH xtd_sales_csr INTO l_xtd_start_date ;
2136 CLOSE xtd_sales_csr;
2137
2138 IF (l_xtd_end_date > l_xtd_start_date )
2139 THEN
2140 UPDATE ozf_funds_all_b
2141 SET utilized_amt = (l_xtd_end_date - l_xtd_start_date )
2142 WHERE fund_id = quota.fund_id ;
2143 END IF;
2144
2145 END LOOP; -- End of leaf_node_quotas_csr
2146
2147 -- Now update all parent quota sales as sum of all leaf level quotas for each parent
2148
2149 FOR quota IN parent_quotas_csr
2150 LOOP
2151
2152 l_parent_quota_sales := 0;
2153
2154 FOR i IN all_leaf_node_quotas_csr(quota.fund_id)
2155 LOOP
2156 l_parent_quota_sales := l_parent_quota_sales + i.utilized_amt ;
2157 END LOOP;
2158
2159 UPDATE ozf_funds_all_b
2160 SET utilized_amt = l_parent_quota_sales
2161 WHERE fund_id = quota.fund_id;
2162
2163 END LOOP; -- End of parent_quotas_csr
2164
2165 -- Procedure body end
2166 ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' (+)');
2167
2168 EXCEPTION
2169
2170 WHEN FND_API.G_EXC_ERROR THEN
2171 x_return_status := FND_API.g_ret_sts_error ;
2172 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
2173 p_count => x_msg_count,
2174 p_data => x_msg_data);
2175
2176 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2177 x_return_status := FND_API.g_ret_sts_unexp_error ;
2178 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
2179 p_count => x_msg_count,
2180 p_data => x_msg_data);
2181
2182 WHEN OTHERS THEN
2183 x_return_status := FND_API.g_ret_sts_unexp_error ;
2184 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
2185 p_count => x_msg_count,
2186 p_data => x_msg_data);
2187
2188 END update_quota_sales_info;
2189
2190 -- Added in R12 - Used to populate the OZF_RES_CUST_PROD_FACTS table
2191 PROCEDURE populate_res_cust_prod_facts (
2192 p_api_version IN NUMBER,
2193 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2194 p_report_date IN DATE,
2195 x_return_status OUT NOCOPY VARCHAR2,
2196 x_msg_count OUT NOCOPY NUMBER,
2197 x_msg_data OUT NOCOPY VARCHAR2) AS
2198 l_api_version CONSTANT NUMBER := 1.0;
2199 l_api_name CONSTANT VARCHAR2(30) := 'populate_res_cust_prod_facts';
2200 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2201 l_return_status VARCHAR2(1);
2202
2203 BEGIN
2204
2205 ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' (-)') ;
2206
2207 IF FND_API.to_boolean(p_init_msg_list) THEN
2208 FND_MSG_PUB.initialize;
2209 END IF;
2210
2211 IF NOT FND_API.compatible_api_call(l_api_version,
2212 p_api_version,
2213 l_api_name,
2214 g_pkg_name)
2215 THEN
2216 RAISE FND_API.g_exc_unexpected_error;
2217 END IF;
2218 x_return_status := FND_API.g_ret_sts_success;
2219
2220 DELETE FROM OZF_RES_CUST_PROD_FACTS
2221 WHERE report_date = p_report_date;
2222
2223 INSERT INTO OZF_RES_CUST_PROD_FACTS
2224 (SELECT OZF_RES_CUST_PROD_FACTS_S.nextval,
2225 resource_id,
2226 report_date,
2227 fact_row_for,
2228 party_id,
2229 cust_account_id,
2230 bill_to_site_use_id,
2231 ship_to_site_use_id,
2232 product_attribute,
2233 product_attr_value,
2234 ptd_sales,
2235 qtd_sales,
2236 ytd_sales,
2237 lptd_sales,
2238 lqtd_sales,
2239 lytd_sales,
2240 lysp_sales,
2241 lysq_sales,
2242 ly_sales,
2243 period_quota,
2244 qtr_quota,
2245 year_quota,
2246 mtd_basesales,
2247 qtd_basesales,
2248 ytd_basesales,
2249 outstanding_orders,
2250 current_orders,
2251 back_orders,
2252 future_orders,
2253 tot_ship_psbl_peroid,
2254 ytd_fund_utilized,
2255 ytd_fund_earned,
2256 ytd_fund_paid,
2257 qtd_fund_utilized,
2258 qtd_fund_earned,
2259 qtd_fund_paid,
2260 mtd_fund_utilized,
2261 mtd_fund_earned,
2262 mtd_fund_paid,
2263 fund_unpaid,
2264 open_claims,
2265 creation_date,
2266 created_by,
2267 last_update_date,
2268 last_updated_by,
2269 last_update_login
2270 FROM
2271 ----FOR PARTY ---
2272 (SELECT
2273 kpi.resource_id resource_id,
2274 c.report_date report_date,
2275 'PARTY' FACT_ROW_FOR,
2276 x.party_id party_id,
2277 NULL CUST_ACCOUNT_ID,
2278 NULL BILL_TO_SITE_USE_ID,
2279 NULL SHIP_TO_SITE_USE_ID,
2280 NULL PRODUCT_ATTRIBUTE,
2281 NULL PRODUCT_ATTR_VALUE,
2282 SUM(ptd_sales) PTD_SALES,
2283 SUM(qtd_sales) QTD_SALES,
2284 SUM(ytd_sales) YTD_SALES,
2285 SUM(lysp_sales) LPTD_SALES,
2286 SUM(lqtd_sales) LQTD_SALES,
2287 SUM(lytd_sales) LYTD_SALES,
2288 SUM(lysp_sales) LYSP_SALES,
2289 SUM(lysq_sales) LYSQ_SALES,
2290 SUM(ly_sales) LY_SALES,
2291 0 PERIOD_QUOTA,
2292 0 QTR_QUOTA,
2293 0 YEAR_QUOTA,
2294 SUM(mtd_basesales) MTD_BASESALES,
2295 SUM(qtd_basesales) QTD_BASESALES,
2296 SUM(ytd_basesales) YTD_BASESALES,
2297 SUM(past_due_order_qty) OUTSTANDING_ORDERS,
2298 SUM(current_period_order_qty) CURRENT_ORDERS,
2299 SUM(backordered_qty) BACK_ORDERS,
2300 SUM(booked_for_future_qty) FUTURE_ORDERS,
2301 SUM(ptd_sales)+SUM(past_due_order_qty)+SUM(current_period_order_qty)+SUM(backordered_qty) TOT_SHIP_PSBL_PEROID,
2302 SUM(YTD_FUND_UTILIZED) YTD_FUND_UTILIZED,
2303 SUM(YTD_FUND_EARNED) YTD_FUND_EARNED,
2304 SUM(YTD_FUND_PAID) YTD_FUND_PAID,
2305 SUM(QTD_FUND_UTILIZED) QTD_FUND_UTILIZED,
2306 SUM(QTD_FUND_EARNED) QTD_FUND_EARNED,
2307 SUM(QTD_FUND_PAID) QTD_FUND_PAID,
2308 SUM(MTD_FUND_UTILIZED) MTD_FUND_UTILIZED,
2309 SUM(MTD_FUND_EARNED) MTD_FUND_EARNED,
2310 SUM(MTD_FUND_PAID) MTD_FUND_PAID,
2311 SUM(FUND_UNPAID) FUND_UNPAID,
2312 SUM(OPEN_CLAIMS) OPEN_CLAIMS,
2313 sysdate CREATION_DATE,
2314 -1 CREATED_BY,
2315 sysdate LAST_UPDATE_DATE,
2316 -1 LAST_UPDATED_BY,
2317 -1 LAST_UPDATE_LOGIN
2318 FROM ozf_cust_daily_facts c,
2319 hz_cust_accounts x,
2320 ozf_dashb_daily_kpi kpi,
2321 (SELECT DISTINCT a.owner
2322 FROM ozf_funds_all_b a
2323 WHERE a.fund_type = 'QUOTA'
2324 AND a.status_code <> 'CANCELLED') fund
2325 WHERE c.cust_account_id = x.cust_account_id
2326 AND c.product_attribute <> 'OTHERS'
2327 AND kpi.resource_id = fund.owner
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
2337 AND prod.allocation_for = 'CUST'
2338 AND prod.allocation_for_id = acct.account_allocation_id
2339 AND acct.allocation_for = 'FUND'
2340 AND acct.allocation_for_id in
2341 (SELECT aa.fund_id
2342 FROM ozf_funds_all_b aa
2343 WHERE aa.owner = kpi.resource_id
2344 AND aa.fund_type = 'QUOTA'
2345 AND aa.status_code <> 'CANCELLED'
2346 AND NOT EXISTS ( SELECT 1
2347 FROM ozf_funds_all_b bb
2348 WHERE bb.parent_fund_id = aa.fund_id )
2349 UNION ALL
2350 SELECT aa.fund_id
2351 FROM ozf_funds_all_b aa
2352 WHERE aa.fund_type = 'QUOTA'
2353 AND aa.status_code <> 'CANCELLED'
2354 CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
2355 START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
2356 FROM ozf_funds_all_b bb
2357 WHERE bb.owner = kpi.resource_id
2358 AND bb.fund_type = 'QUOTA'
2359 AND bb.status_code <> 'CANCELLED')
2360 ))
2361 AND c.report_date = p_report_date
2362 AND fund.owner = kpi.resource_id
2363 GROUP BY
2364 kpi.resource_id,
2365 c.report_date,
2366 'PARTY',
2367 x.party_id
2368
2369 UNION ALL
2370
2371 --- FOR BILL_TO----
2372
2373 SELECT
2374 kpi.resource_id resource_id,
2375 c.report_date report_date,
2376 'BILL_TO' FACT_ROW_FOR,
2377 x.party_id party_id,
2378 c.CUST_ACCOUNT_ID CUST_ACCOUNT_ID,
2379 c.bill_to_site_use_id BILL_TO_SITE_USE_ID,
2380 0 SHIP_TO_SITE_USE_ID,
2381 NULL PRODUCT_ATTRIBUTE,
2382 NULL PRODUCT_ATTR_VALUE,
2383 SUM(ptd_sales) PTD_SALES,
2384 SUM(qtd_sales) QTD_SALES,
2385 SUM(ytd_sales) YTD_SALES,
2386 SUM(lysp_sales) LPTD_SALES,
2387 SUM(lqtd_sales) LQTD_SALES,
2388 SUM(lytd_sales) LYTD_SALES,
2389 SUM(lysp_sales) LYSP_SALES,
2390 SUM(lysq_sales) LYSQ_SALES,
2391 SUM(ly_sales) LY_SALES,
2392 0 PERIOD_QUOTA,
2393 0 QTR_QUOTA,
2394 0 YEAR_QUOTA,
2395 SUM(mtd_basesales) MTD_BASESALES,
2396 SUM(qtd_basesales) QTD_BASESALES,
2397 SUM(ytd_basesales) YTD_BASESALES,
2398 SUM(past_due_order_qty) OUTSTANDING_ORDERS,
2399 SUM(current_period_order_qty) CURRENT_ORDERS,
2400 SUM(backordered_qty) BACK_ORDERS,
2401 SUM(booked_for_future_qty) FUTURE_ORDERS,
2402 SUM(ptd_sales)+SUM(past_due_order_qty)+SUM(current_period_order_qty)+SUM(backordered_qty) TOT_SHIP_PSBL_PEROID,
2403 SUM(YTD_FUND_UTILIZED) YTD_FUND_UTILIZED,
2404 SUM(YTD_FUND_EARNED) YTD_FUND_EARNED,
2405 SUM(YTD_FUND_PAID) YTD_FUND_PAID,
2406 SUM(QTD_FUND_UTILIZED) QTD_FUND_UTILIZED,
2407 SUM(QTD_FUND_EARNED) QTD_FUND_EARNED,
2408 SUM(QTD_FUND_PAID) QTD_FUND_PAID,
2409 SUM(MTD_FUND_UTILIZED) MTD_FUND_UTILIZED,
2410 SUM(MTD_FUND_EARNED) MTD_FUND_EARNED,
2411 SUM(MTD_FUND_PAID) MTD_FUND_PAID,
2412 SUM(FUND_UNPAID) FUND_UNPAID,
2413 SUM(OPEN_CLAIMS) OPEN_CLAIMS,
2414 sysdate CREATION_DATE,
2415 -1 CREATED_BY,
2416 sysdate LAST_UPDATE_DATE,
2417 -1 LAST_UPDATED_BY,
2418 -1 LAST_UPDATE_LOGIN
2419 FROM ozf_cust_daily_facts c,
2420 hz_cust_accounts x,
2421 ozf_dashb_daily_kpi kpi,
2422 (SELECT DISTINCT a.owner
2423 FROM ozf_funds_all_b a
2424 WHERE a.fund_type = 'QUOTA'
2425 AND a.status_code <> 'CANCELLED') fund
2426 WHERE c.cust_account_id = x.cust_account_id
2427 AND c.product_attribute <> 'OTHERS'
2428 AND kpi.resource_id = fund.owner
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
2438 AND prod.allocation_for = 'CUST'
2439 AND prod.allocation_for_id = acct.account_allocation_id
2440 AND acct.allocation_for = 'FUND'
2441 AND acct.allocation_for_id in
2442 (SELECT aa.fund_id
2443 FROM ozf_funds_all_b aa
2444 WHERE aa.owner = kpi.resource_id
2445 AND aa.fund_type = 'QUOTA'
2446 AND aa.status_code <> 'CANCELLED'
2447 AND NOT EXISTS ( SELECT 1
2448 FROM ozf_funds_all_b bb
2449 WHERE bb.parent_fund_id = aa.fund_id )
2450 UNION ALL
2451 SELECT aa.fund_id
2452 FROM ozf_funds_all_b aa
2453 WHERE aa.fund_type = 'QUOTA'
2454 AND aa.status_code <> 'CANCELLED'
2455 CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
2456 START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
2457 FROM ozf_funds_all_b bb
2458 WHERE bb.owner = kpi.resource_id
2459 AND bb.fund_type = 'QUOTA'
2460 AND bb.status_code <> 'CANCELLED')
2461 ))
2462 AND c.report_date = p_report_date
2463 AND fund.owner = kpi.resource_id
2464 GROUP BY
2465 kpi.resource_id,
2466 c.report_date,
2467 'BILL_TO',
2468 x.party_id,
2469 c.CUST_ACCOUNT_ID,
2470 c.bill_to_site_use_id
2471
2472 UNION ALL
2473 --- FOR SHIP_TO----
2474 SELECT
2475 kpi.resource_id resource_id,
2476 c.report_date report_date,
2477 'SHIP_TO' FACT_ROW_FOR,
2478 x.party_id party_id,
2479 c.CUST_ACCOUNT_ID CUST_ACCOUNT_ID,
2480 c.bill_to_site_use_id BILL_TO_SITE_USE_ID,
2481 c.ship_to_site_use_id SHIP_TO_SITE_USE_ID,
2482 NULL PRODUCT_ATTRIBUTE,
2483 NULL PRODUCT_ATTR_VALUE,
2484 SUM(ptd_sales) PTD_SALES,
2485 SUM(qtd_sales) QTD_SALES,
2486 SUM(ytd_sales) YTD_SALES,
2487 SUM(lysp_sales) LPTD_SALES,
2488 SUM(lqtd_sales) LQTD_SALES,
2489 SUM(lytd_sales) LYTD_SALES,
2490 SUM(lysp_sales) LYSP_SALES,
2491 SUM(lysq_sales) LYSQ_SALES,
2492 SUM(ly_sales) LY_SALES,
2493 0 PERIOD_QUOTA,
2494 0 QTR_QUOTA,
2495 0 YEAR_QUOTA,
2496 SUM(mtd_basesales) MTD_BASESALES,
2497 SUM(qtd_basesales) QTD_BASESALES,
2498 SUM(ytd_basesales) YTD_BASESALES,
2499 SUM(past_due_order_qty) OUTSTANDING_ORDERS,
2500 SUM(current_period_order_qty) CURRENT_ORDERS,
2501 SUM(backordered_qty) BACK_ORDERS,
2502 SUM(booked_for_future_qty) FUTURE_ORDERS,
2503 SUM(ptd_sales)+SUM(past_due_order_qty)+SUM(current_period_order_qty)+SUM(backordered_qty) TOT_SHIP_PSBL_PEROID,
2504 SUM(YTD_FUND_UTILIZED) YTD_FUND_UTILIZED,
2505 SUM(YTD_FUND_EARNED) YTD_FUND_EARNED,
2506 SUM(YTD_FUND_PAID) YTD_FUND_PAID,
2507 SUM(QTD_FUND_UTILIZED) QTD_FUND_UTILIZED,
2508 SUM(QTD_FUND_EARNED) QTD_FUND_EARNED,
2509 SUM(QTD_FUND_PAID) QTD_FUND_PAID,
2510 SUM(MTD_FUND_UTILIZED) MTD_FUND_UTILIZED,
2511 SUM(MTD_FUND_EARNED) MTD_FUND_EARNED,
2512 SUM(MTD_FUND_PAID) MTD_FUND_PAID,
2513 SUM(FUND_UNPAID) FUND_UNPAID,
2514 SUM(OPEN_CLAIMS) OPEN_CLAIMS,
2515 sysdate CREATION_DATE,
2516 -1 CREATED_BY,
2517 sysdate LAST_UPDATE_DATE,
2518 -1 LAST_UPDATED_BY,
2519 -1 LAST_UPDATE_LOGIN
2520 FROM ozf_cust_daily_facts c,
2521 hz_cust_accounts x,
2522 ozf_dashb_daily_kpi kpi,
2523 (SELECT DISTINCT a.owner
2524 FROM ozf_funds_all_b a
2525 WHERE a.fund_type = 'QUOTA'
2526 AND a.status_code <> 'CANCELLED') fund
2527 WHERE c.cust_account_id = x.cust_account_id
2528 AND c.product_attribute <> 'OTHERS'
2529 AND kpi.resource_id = fund.owner
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
2539 AND prod.allocation_for = 'CUST'
2540 AND prod.allocation_for_id = acct.account_allocation_id
2541 AND acct.allocation_for = 'FUND'
2542 AND acct.allocation_for_id in
2543 (SELECT aa.fund_id
2544 FROM ozf_funds_all_b aa
2545 WHERE aa.owner = kpi.resource_id
2546 AND aa.fund_type = 'QUOTA'
2547 AND aa.status_code <> 'CANCELLED'
2548 AND NOT EXISTS ( SELECT 1
2549 FROM ozf_funds_all_b bb
2550 WHERE bb.parent_fund_id = aa.fund_id )
2551 UNION ALL
2552 SELECT aa.fund_id
2553 FROM ozf_funds_all_b aa
2554 WHERE aa.fund_type = 'QUOTA'
2555 AND aa.status_code <> 'CANCELLED'
2556 CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
2557 START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
2558 FROM ozf_funds_all_b bb
2559 WHERE bb.owner = kpi.resource_id
2560 AND bb.fund_type = 'QUOTA'
2561 AND bb.status_code <> 'CANCELLED')
2562 ))
2563 AND c.report_date = p_report_date
2564 AND fund.owner = kpi.resource_id
2565 GROUP BY
2566 kpi.resource_id,
2567 c.report_date,
2568 'SHIP_TO',
2569 x.party_id,
2570 c.CUST_ACCOUNT_ID,
2571 c.bill_to_site_use_id,
2572 c.ship_to_site_use_id
2573
2574 UNION ALL
2575 --- FOR PRODUCT----
2576 SELECT
2577 kpi.resource_id resource_id,
2578 c.report_date report_date,
2579 'PRODUCT' FACT_ROW_FOR,
2580 0 party_id,
2581 0 CUST_ACCOUNT_ID,
2582 0 BILL_TO_SITE_USE_ID,
2583 0 SHIP_TO_SITE_USE_ID,
2584 c.product_attribute PRODUCT_ATTRIBUTE,
2585 c.product_attr_value PRODUCT_ATTR_VALUE,
2586 SUM(ptd_sales) PTD_SALES,
2587 SUM(qtd_sales) QTD_SALES,
2588 SUM(ytd_sales) YTD_SALES,
2589 SUM(lysp_sales) LPTD_SALES,
2590 SUM(lqtd_sales) LQTD_SALES,
2591 SUM(lytd_sales) LYTD_SALES,
2592 SUM(lysp_sales) LYSP_SALES,
2593 SUM(lysq_sales) LYSQ_SALES,
2594 SUM(ly_sales) LY_SALES,
2595 SUM(current_period_target) PERIOD_QUOTA,
2596 SUM(current_qtr_target) QTR_QUOTA,
2597 SUM(current_year_target) YEAR_QUOTA,
2598 SUM(mtd_basesales) MTD_BASESALES,
2599 SUM(qtd_basesales) QTD_BASESALES,
2600 SUM(ytd_basesales) YTD_BASESALES,
2601 SUM(past_due_order_qty) OUTSTANDING_ORDERS,
2602 SUM(current_period_order_qty) CURRENT_ORDERS,
2603 SUM(backordered_qty) BACK_ORDERS,
2604 SUM(booked_for_future_qty) FUTURE_ORDERS,
2605 SUM(ptd_sales)+SUM(past_due_order_qty)+SUM(current_period_order_qty)+SUM(backordered_qty) TOT_SHIP_PSBL_PEROID,
2606 SUM(YTD_FUND_UTILIZED) YTD_FUND_UTILIZED,
2607 SUM(YTD_FUND_EARNED) YTD_FUND_EARNED,
2608 SUM(YTD_FUND_PAID) YTD_FUND_PAID,
2609 SUM(QTD_FUND_UTILIZED) QTD_FUND_UTILIZED,
2610 SUM(QTD_FUND_EARNED) QTD_FUND_EARNED,
2611 SUM(QTD_FUND_PAID) QTD_FUND_PAID,
2612 SUM(MTD_FUND_UTILIZED) MTD_FUND_UTILIZED,
2613 SUM(MTD_FUND_EARNED) MTD_FUND_EARNED,
2614 SUM(MTD_FUND_PAID) MTD_FUND_PAID,
2615 SUM(FUND_UNPAID) FUND_UNPAID,
2616 SUM(OPEN_CLAIMS) OPEN_CLAIMS,
2617 sysdate CREATION_DATE,
2618 -1 CREATED_BY,
2619 sysdate LAST_UPDATE_DATE,
2620 -1 LAST_UPDATED_BY,
2621 -1 LAST_UPDATE_LOGIN
2622 FROM ozf_cust_daily_facts c ,
2623 ozf_dashb_daily_kpi kpi,
2624 (SELECT DISTINCT a.owner
2625 FROM ozf_funds_all_b a
2626 WHERE a.fund_type = 'QUOTA'
2627 AND a.status_code <> 'CANCELLED') fund
2628 WHERE
2629 kpi.sequence_number = 1
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
2639 AND prod.allocation_for = 'CUST'
2640 AND prod.allocation_for_id = acct.account_allocation_id
2641 AND acct.allocation_for = 'FUND'
2642 AND acct.allocation_for_id in
2643 (SELECT aa.fund_id
2644 FROM ozf_funds_all_b aa
2645 WHERE aa.owner = kpi.resource_id
2646 AND aa.fund_type = 'QUOTA'
2647 AND aa.status_code <> 'CANCELLED'
2648 AND NOT EXISTS ( SELECT 1
2649 FROM ozf_funds_all_b bb
2650 WHERE bb.parent_fund_id = aa.fund_id )
2651 UNION ALL
2652 SELECT aa.fund_id
2653 FROM ozf_funds_all_b aa
2654 WHERE aa.fund_type = 'QUOTA'
2655 AND aa.status_code <> 'CANCELLED'
2656 CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
2657 START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
2658 FROM ozf_funds_all_b bb
2659 WHERE bb.owner = kpi.resource_id
2660 AND bb.fund_type = 'QUOTA'
2661 AND bb.status_code <> 'CANCELLED')
2662 ))
2663 AND c.report_date = p_report_date
2664 AND fund.owner = kpi.resource_id
2665 GROUP BY
2666 kpi.resource_id,
2667 c.report_date,
2668 'PRODUCT',
2669 c.product_attribute,
2670 c.product_attr_value)
2671 );
2672
2673 -- inanaiah: updating PERIOD_QUOTA, QTR_QUOTA, YEAR_QUOTA that was set to 0
2674 -- in the above insert stmt. This is done as part of bug 4887783 fix.
2675
2676 -- PARTY
2677 UPDATE ozf_res_cust_prod_facts outer
2678 Set (PERIOD_QUOTA, QTR_QUOTA, YEAR_QUOTA)
2679 =
2680 (
2681 SELECT
2682 ozf_cust_facts_pvt.get_cust_target( outer.party_id, NULL, NULL,
2683 'PERIOD_QUOTA', 0,
2684 outer.report_date,outer.resource_id
2685 ) PERIOD_QUOTA,
2686 ozf_cust_facts_pvt.get_cust_target( outer.party_id, NULL, NULL,
2687 'QTR_QUOTA', 0,
2688 outer.report_date,outer.resource_id
2689 ) QTR_QUOTA,
2690 ozf_cust_facts_pvt.get_cust_target( outer.party_id, NULL, NULL,
2691 'YEAR_QUOTA', 0,
2692 outer.report_date,outer.resource_id
2693 ) YEAR_QUOTA
2694 FROM dual
2695 )
2696 WHERE outer.fact_row_for = 'PARTY'
2697 AND outer.report_date = p_report_date;
2698
2699 -- BILL_TO
2700 UPDATE ozf_res_cust_prod_facts outer
2701 Set (PERIOD_QUOTA, QTR_QUOTA, YEAR_QUOTA)
2702 =
2703 (
2704 SELECT
2705 ozf_cust_facts_pvt.get_cust_target( outer.party_id, outer.bill_to_site_use_id,
2706 NULL, 'PERIOD_QUOTA', 0,
2707 outer.report_date,outer.resource_id
2708 ) PERIOD_QUOTA,
2709 ozf_cust_facts_pvt.get_cust_target( outer.party_id, outer.bill_to_site_use_id,
2710 NULL, 'QTR_QUOTA', 0,
2711 outer.report_date,outer.resource_id
2712 ) QTR_QUOTA,
2713 ozf_cust_facts_pvt.get_cust_target( outer.party_id, outer.bill_to_site_use_id,
2714 NULL, 'YEAR_QUOTA', 0,
2715 outer.report_date,outer.resource_id
2716 ) YEAR_QUOTA
2717 FROM dual
2718 )
2719 WHERE outer.fact_row_for = 'BILL_TO'
2720 AND outer.report_date = p_report_date;
2721
2722 -- SHIP_TO
2723 UPDATE ozf_res_cust_prod_facts outer
2724 Set (PERIOD_QUOTA, QTR_QUOTA, YEAR_QUOTA)
2725 =
2726 (
2727 SELECT
2728 ozf_cust_facts_pvt.get_cust_target( outer.party_id, outer.bill_to_site_use_id,
2729 outer.ship_to_site_use_id, 'PERIOD_QUOTA', 0,
2730 outer.report_date,outer.resource_id
2731 ) PERIOD_QUOTA,
2732 ozf_cust_facts_pvt.get_cust_target( outer.party_id, outer.bill_to_site_use_id,
2733 outer.ship_to_site_use_id, 'QTR_QUOTA', 0,
2734 outer.report_date,outer.resource_id
2735 ) QTR_QUOTA,
2736 ozf_cust_facts_pvt.get_cust_target( outer.party_id, outer.bill_to_site_use_id,
2737 outer.ship_to_site_use_id, 'YEAR_QUOTA', 0,
2738 outer.report_date,outer.resource_id
2739 ) YEAR_QUOTA
2740 FROM dual
2741 )
2742 WHERE outer.fact_row_for = 'SHIP_TO'
2743 AND outer.report_date = p_report_date;
2744
2745 ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' (+)');
2746
2747 EXCEPTION
2748 WHEN FND_API.G_EXC_ERROR THEN
2749 x_return_status := FND_API.g_ret_sts_error ;
2750 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
2751 p_count => x_msg_count,
2752 p_data => x_msg_data);
2753
2754 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2755 x_return_status := FND_API.g_ret_sts_unexp_error ;
2756 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
2757 p_count => x_msg_count,
2758 p_data => x_msg_data);
2759
2760 WHEN OTHERS THEN
2761 x_return_status := FND_API.g_ret_sts_unexp_error ;
2762 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
2763 p_count => x_msg_count,
2764 p_data => x_msg_data);
2765 END populate_res_cust_prod_facts;
2766
2767 -- for R12, proc to get the budget and claims info and update the ozf_res_cust_prod_facts
2768 PROCEDURE refresh_budget_and_claims_info (
2769 p_api_version IN NUMBER,
2770 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2771 p_report_date IN DATE,
2772 x_return_status OUT NOCOPY VARCHAR2,
2773 x_msg_count OUT NOCOPY NUMBER,
2774 x_msg_data OUT NOCOPY VARCHAR2) AS
2775 l_api_version CONSTANT NUMBER := 1.0;
2776 l_api_name CONSTANT VARCHAR2(30) := 'refresh_budget_and_claims_info';
2777 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2778 l_return_status VARCHAR2(1);
2779
2780 BEGIN
2781
2782 ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' (-)') ;
2783
2784 IF FND_API.to_boolean(p_init_msg_list) THEN
2785 FND_MSG_PUB.initialize;
2786 END IF;
2787
2788 IF NOT FND_API.compatible_api_call(l_api_version,
2789 p_api_version,
2790 l_api_name,
2791 g_pkg_name)
2792 THEN
2793 RAISE FND_API.g_exc_unexpected_error;
2794 END IF;
2795 x_return_status := FND_API.g_ret_sts_success;
2796
2797 --YTD budget amounts for 'PARTY'
2798 UPDATE ozf_res_cust_prod_facts outer
2799 Set (YTD_FUND_utilized, YTD_FUND_earned, YTD_FUND_paid)
2800 =
2801 (
2802 SELECT NVL(SUM(utilized_amt),0) tot_utilized,
2803 NVL(SUM(earned_amt),0) tot_earned,
2804 NVL(SUM(paid_amt),0) tot_paid
2805 FROM ozf_time_day a,
2806 ozf_cust_fund_summary_mv b,
2807 ozf_res_cust_prod c
2808 WHERE c.resource_id = outer.resource_id
2809 AND a.report_date = outer.report_date
2810 AND a.ent_year_id = b.time_id
2811 AND b.status_code = 'ACTIVE'
2812 AND b.party_id = outer.party_id
2813 AND b.party_id = c.party_id
2814 AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
2815 FROM ams_act_access_denorm
2816 WHERE object_type = b.plan_type
2817 AND object_id = b.plan_id
2818 AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
2819 AND
2820 (
2821 (
2822 b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
2823 AND b.product_id = c.product_attr_value
2824 )
2825 OR
2826 (
2827 b.product_level_type IS NULL
2828 AND b.product_id IS NULL
2829 AND EXISTS (SELECT 'X'
2830 FROM ams_act_access_denorm
2831 WHERE object_type = b.plan_type
2832 AND object_id = b.plan_id
2833 AND resource_id = outer.resource_id)
2834 )
2835 )
2836 )
2837 WHERE outer.fact_row_for = 'PARTY'
2838 AND outer.report_date = p_report_date ;
2839
2840 --YTD amounts for 'BILL_TO'
2841 Update ozf_res_cust_prod_facts outer
2842 Set (YTD_FUND_utilized, YTD_FUND_earned, YTD_FUND_paid)
2843 =
2844 (
2845 SELECT NVL(SUM(utilized_amt),0) tot_utilized,
2846 NVL(SUM(earned_amt),0) tot_earned,
2847 NVL(SUM(paid_amt),0) tot_paid
2848 FROM ozf_time_day a,
2849 ozf_cust_fund_summary_mv b,
2850 ozf_res_cust_prod c
2851 WHERE c.resource_id = outer.resource_id
2852 AND a.report_date = outer.report_date
2853 AND a.ent_year_id = b.time_id
2854 AND b.status_code = 'ACTIVE'
2855 AND b.party_id = outer.party_id
2856 AND b.party_id = c.party_id
2857 AND b.bill_to_site_use_id = outer.bill_to_site_use_id
2858 AND b.bill_to_site_use_id = c.bill_to_site_use_id
2859 AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
2860 FROM ams_act_access_denorm
2861 WHERE object_type = b.plan_type
2862 AND object_id = b.plan_id
2863 AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
2864 AND
2865 (
2866 (
2867 b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
2868 AND b.product_id = c.product_attr_value
2869 )
2870 OR
2871 (
2872 b.product_level_type IS NULL
2873 AND b.product_id IS NULL
2874 AND EXISTS (SELECT 'X'
2875 FROM ams_act_access_denorm
2876 WHERE object_type = b.plan_type
2877 AND object_id = b.plan_id
2878 AND resource_id = outer.resource_id)
2879 )
2880 )
2881 )
2882 WHERE outer.fact_row_for = 'BILL_TO'
2883 AND outer.report_date = p_report_date ;
2884
2885 --YTD amounts for 'SHIP_TO'
2886 Update ozf_res_cust_prod_facts outer
2887 Set (YTD_FUND_utilized, YTD_FUND_earned, YTD_FUND_paid)
2888 =
2889 (
2890 SELECT NVL(SUM(utilized_amt),0) tot_utilized,
2891 NVL(SUM(earned_amt),0) tot_earned,
2892 NVL(SUM(paid_amt),0) tot_paid
2893 FROM ozf_time_day a,
2894 ozf_cust_fund_summary_mv b,
2895 ozf_res_cust_prod c
2896 WHERE c.resource_id = outer.resource_id
2897 AND a.report_date = outer.report_date
2898 AND a.ent_year_id = b.time_id
2899 AND b.status_code = 'ACTIVE'
2900 AND b.party_id = outer.party_id
2901 AND b.party_id = c.party_id
2902 AND b.bill_to_site_use_id = outer.bill_to_site_use_id
2903 AND b.bill_to_site_use_id = c.bill_to_site_use_id
2904 AND b.ship_to_site_use_id = outer.ship_to_site_use_id
2905 AND b.ship_to_site_use_id = c.ship_to_site_use_id
2906 AND (
2907 ( b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
2908 AND b.product_id = c.product_attr_value
2909 )
2910 or
2911 ( b.product_level_type IS NULL
2912 AND b.product_id IS NULL
2913 AND EXISTS (SELECT 'X'
2914 FROM ams_act_access_denorm
2915 WHERE object_type = b.plan_type
2916 AND object_id = b.plan_id
2917 AND resource_id = outer.resource_id)
2918 )
2919 )
2920 )
2921 WHERE outer.fact_row_for = 'SHIP_TO'
2922 AND outer.report_date = p_report_date ;
2923
2924 --QTD amounts for 'PARTY'
2925 Update ozf_res_cust_prod_facts outer
2926 Set (QTD_FUND_utilized, QTD_FUND_earned, QTD_FUND_paid)
2927 =
2928 (
2929 SELECT NVL(SUM(utilized_amt),0) tot_utilized,
2930 NVL(SUM(earned_amt),0) tot_earned,
2931 NVL(SUM(paid_amt),0) tot_paid
2932 FROM ozf_time_day a,
2933 ozf_cust_fund_summary_mv b,
2934 ozf_res_cust_prod c
2935 WHERE c.resource_id = outer.resource_id
2936 AND a.report_date = outer.report_date
2937 AND a.ent_qtr_id = b.time_id
2938 AND b.status_code = 'ACTIVE'
2939 AND b.party_id = outer.party_id
2940 AND b.party_id = c.party_id
2941 AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
2942 FROM ams_act_access_denorm
2943 WHERE object_type = b.plan_type
2944 AND object_id = b.plan_id
2945 AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
2946 AND
2947 (
2948 (
2949 b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
2950 AND b.product_id = c.product_attr_value
2951 )
2952 OR
2953 (
2954 b.product_level_type IS NULL
2955 AND b.product_id IS NULL
2956 AND EXISTS (SELECT 'X'
2957 FROM ams_act_access_denorm
2958 WHERE object_type = b.plan_type
2959 AND object_id = b.plan_id
2960 AND resource_id = outer.resource_id)
2961 )
2962 )
2963 )
2964 WHERE outer.fact_row_for = 'PARTY'
2965 AND outer.report_date = p_report_date ;
2966
2967 --QTD amounts for 'BILL_TO'
2968 Update ozf_res_cust_prod_facts outer
2969 Set (QTD_FUND_utilized, QTD_FUND_earned, QTD_FUND_paid)
2970 =
2971 (
2972 SELECT NVL(SUM(utilized_amt),0) tot_utilized,
2973 NVL(SUM(earned_amt),0) tot_earned,
2974 NVL(SUM(paid_amt),0) tot_paid
2975 FROM ozf_time_day a,
2976 ozf_cust_fund_summary_mv b,
2977 ozf_res_cust_prod c
2978 WHERE c.resource_id = outer.resource_id
2979 AND a.report_date = outer.report_date
2980 AND a.ent_qtr_id = b.time_id
2981 AND b.status_code = 'ACTIVE'
2982 AND b.party_id = outer.party_id
2983 AND b.party_id = c.party_id
2984 AND b.bill_to_site_use_id = outer.bill_to_site_use_id
2985 AND b.bill_to_site_use_id = c.bill_to_site_use_id
2986 AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
2987 FROM ams_act_access_denorm
2988 WHERE object_type = b.plan_type
2989 AND object_id = b.plan_id
2990 AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
2991 AND
2992 (
2993 (
2994 b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
2995 AND b.product_id = c.product_attr_value
2996 )
2997 OR
2998 (
2999 b.product_level_type IS NULL
3000 AND b.product_id IS NULL
3001 AND EXISTS (SELECT 'X'
3002 FROM ams_act_access_denorm
3003 WHERE object_type = b.plan_type
3004 AND object_id = b.plan_id
3005 AND resource_id = outer.resource_id)
3006 )
3007 )
3008 )
3009 WHERE outer.fact_row_for = 'BILL_TO'
3010 AND outer.report_date = p_report_date ;
3011
3012 --QTD amounts for 'SHIP_TO'
3013 Update ozf_res_cust_prod_facts outer
3014 Set (QTD_FUND_utilized, QTD_FUND_earned, QTD_FUND_paid)
3015 =
3016 (
3017 SELECT NVL(SUM(utilized_amt),0) tot_utilized,
3018 NVL(SUM(earned_amt),0) tot_earned,
3019 NVL(SUM(paid_amt),0) tot_paid
3020 FROM ozf_time_day a,
3021 ozf_cust_fund_summary_mv b,
3022 ozf_res_cust_prod c
3023 WHERE c.resource_id = outer.resource_id
3024 AND a.report_date = outer.report_date
3025 AND a.ent_qtr_id = b.time_id
3026 AND b.status_code = 'ACTIVE'
3027 AND b.party_id = outer.party_id
3028 AND b.party_id = c.party_id
3029 AND b.bill_to_site_use_id = outer.bill_to_site_use_id
3030 AND b.bill_to_site_use_id = c.bill_to_site_use_id
3031 AND b.ship_to_site_use_id = outer.ship_to_site_use_id
3032 AND b.ship_to_site_use_id = c.ship_to_site_use_id
3033 AND
3034 (
3035 ( b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
3036 AND b.product_id = c.product_attr_value
3037 )
3038 OR
3039 ( b.product_level_type IS NULL
3040 AND b.product_id IS NULL
3041 AND EXISTS (SELECT count(object_id)
3042 FROM ams_act_access_denorm
3043 WHERE object_type = b.plan_type
3044 AND object_id = b.plan_id
3045 AND resource_id = outer.resource_id)
3046 )
3047 )
3048 )
3049 WHERE outer.fact_row_for = 'SHIP_TO'
3050 AND outer.report_date = p_report_date;
3051
3052 --MTD amounts for 'PARTY'
3053 Update ozf_res_cust_prod_facts outer
3054 Set (MTD_FUND_utilized, MTD_FUND_earned, MTD_FUND_paid)
3055 =
3056 (
3057 SELECT NVL(SUM(utilized_amt),0) tot_utilized,
3058 NVL(SUM(earned_amt),0) tot_earned,
3059 NVL(SUM(paid_amt),0) tot_paid
3060 FROM ozf_time_day a,
3061 ozf_cust_fund_summary_mv b,
3062 ozf_res_cust_prod c
3063 WHERE c.resource_id = outer.resource_id
3064 AND a.report_date = outer.report_date
3065 AND a.ent_period_id = b.time_id
3066 AND b.status_code = 'ACTIVE'
3067 AND b.party_id = outer.party_id
3068 AND b.party_id = c.party_id
3069 AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
3070 FROM ams_act_access_denorm
3071 WHERE object_type = b.plan_type
3072 AND object_id = b.plan_id
3073 AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
3074 AND
3075 (
3076 (
3077 b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
3078 AND b.product_id = c.product_attr_value
3079 )
3080 OR
3081 (
3082 b.product_level_type IS NULL
3083 AND b.product_id IS NULL
3084 AND EXISTS (SELECT 'X'
3085 FROM ams_act_access_denorm
3086 WHERE object_type = b.plan_type
3087 AND object_id = b.plan_id
3088 AND resource_id = outer.resource_id)
3089 )
3090 )
3091 )
3092 WHERE outer.fact_row_for = 'PARTY'
3093 AND outer.report_date = p_report_date;
3094
3095 --MTD amounts for 'BILL_TO'
3096 Update ozf_res_cust_prod_facts outer
3097 Set (MTD_FUND_utilized, MTD_FUND_earned, MTD_FUND_paid)
3098 =
3099 (
3100 SELECT NVL(SUM(utilized_amt),0) tot_utilized,
3101 NVL(SUM(earned_amt),0) tot_earned,
3102 NVL(SUM(paid_amt),0) tot_paid
3103 FROM ozf_time_day a,
3104 ozf_cust_fund_summary_mv b,
3105 ozf_res_cust_prod c
3106 WHERE c.resource_id = outer.resource_id
3107 AND a.report_date = outer.report_date
3108 AND a.ent_period_id = b.time_id
3109 AND b.status_code = 'ACTIVE'
3110 AND b.party_id = outer.party_id
3111 AND b.party_id = c.party_id
3112 AND b.bill_to_site_use_id = outer.bill_to_site_use_id
3113 AND b.bill_to_site_use_id = c.bill_to_site_use_id
3114 AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
3115 FROM ams_act_access_denorm
3116 WHERE object_type = b.plan_type
3117 AND object_id = b.plan_id
3118 AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
3119 AND
3120 (
3121 (
3122 b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
3123 AND b.product_id = c.product_attr_value
3124 )
3125 OR
3126 (
3127 b.product_level_type IS NULL
3128 AND b.product_id IS NULL
3129 AND EXISTS (SELECT 'X'
3130 FROM ams_act_access_denorm
3131 WHERE object_type = b.plan_type
3132 AND object_id = b.plan_id
3133 AND resource_id = outer.resource_id)
3134 )
3135 )
3136 )
3137 WHERE outer.fact_row_for = 'BILL_TO'
3138 AND outer.report_date = p_report_date ;
3139
3140 --MTD amounts for 'SHIP_TO'
3141 Update ozf_res_cust_prod_facts outer
3142 Set (MTD_FUND_utilized, MTD_FUND_earned, MTD_FUND_paid)
3143 =
3144 (
3145 SELECT NVL(SUM(utilized_amt),0) tot_utilized,
3146 NVL(SUM(earned_amt),0) tot_earned,
3147 NVL(SUM(paid_amt),0) tot_paid
3148 FROM ozf_time_day a,
3149 ozf_cust_fund_summary_mv b,
3150 ozf_res_cust_prod c
3151 WHERE c.resource_id = outer.resource_id
3152 AND a.report_date = outer.report_date
3153 AND a.ent_period_id = b.time_id
3154 AND b.status_code = 'ACTIVE'
3155 AND b.party_id = outer.party_id
3156 AND b.party_id = c.party_id
3157 AND b.bill_to_site_use_id = outer.bill_to_site_use_id
3158 AND b.bill_to_site_use_id = c.bill_to_site_use_id
3159 AND b.ship_to_site_use_id = outer.ship_to_site_use_id
3160 AND b.ship_to_site_use_id = c.ship_to_site_use_id
3161 AND
3162 (
3163 ( b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
3164 AND b.product_id = c.product_attr_value
3165 )
3166 OR
3167 ( b.product_level_type IS NULL
3168 AND b.product_id IS NULL
3169 AND EXISTS (SELECT 'X'
3170 FROM ams_act_access_denorm
3171 WHERE object_type = b.plan_type
3172 AND object_id = b.plan_id
3173 AND resource_id = outer.resource_id)
3174 )
3175 )
3176 )
3177 WHERE outer.fact_row_for = 'SHIP_TO'
3178 AND outer.report_date = p_report_date ;
3179
3180 --UNPAID amount for 'PARTY'
3181 Update ozf_res_cust_prod_facts outer
3182 Set (FUND_unpaid)
3183 =
3184 (
3185 SELECT (NVL(SUM(earned_amt),0) - NVL(SUM(paid_amt),0)) tot_unpaid
3186 FROM ozf_cust_fund_summary_mv b,
3187 ozf_res_cust_prod c
3188 WHERE c.resource_id = outer.resource_id
3189 AND b.time_id = -1
3190 AND b.period_type_id = 256
3191 AND b.status_code = 'ACTIVE'
3192 AND b.party_id = c.party_id
3193 AND b.party_id = outer.party_id
3194 AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
3195 FROM ams_act_access_denorm
3196 WHERE object_type = b.plan_type
3197 AND object_id = b.plan_id
3198 AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
3199 AND
3200 (
3201 (
3202 b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
3203 AND b.product_id = c.product_attr_value
3204 )
3205 OR
3206 (
3207 b.product_level_type IS NULL
3208 AND b.product_id IS NULL
3209 AND EXISTS (SELECT 'X'
3210 FROM ams_act_access_denorm
3211 WHERE object_type = b.plan_type
3212 AND object_id = b.plan_id
3213 AND resource_id = outer.resource_id)
3214 )
3215 )
3216 )
3217 WHERE outer.fact_row_for = 'PARTY'
3218 AND outer.report_date = p_report_date ;
3219
3220 --UNPAID amount for 'BILL_TO'
3221 Update ozf_res_cust_prod_facts outer
3222 Set (fund_unpaid)
3223 =
3224 (
3225 SELECT (NVL(SUM(earned_amt),0) - NVL(SUM(paid_amt),0)) tot_unpaid
3226 FROM ozf_cust_fund_summary_mv b,
3227 ozf_res_cust_prod c
3228 WHERE c.resource_id = outer.resource_id
3229 AND b.time_id = -1
3230 AND b.period_type_id = 256
3231 AND b.status_code = 'ACTIVE'
3232 AND b.party_id = outer.party_id
3233 AND b.party_id = c.party_id
3234 AND b.bill_to_site_use_id = outer.bill_to_site_use_id
3235 AND b.bill_to_site_use_id = c.bill_to_site_use_id
3236 AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
3237 FROM ams_act_access_denorm
3238 WHERE object_type = b.plan_type
3239 AND object_id = b.plan_id
3240 AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
3241 AND
3242 (
3243 (
3244 b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
3245 AND b.product_id = c.product_attr_value
3246 )
3247 OR
3248 (
3249 b.product_level_type IS NULL
3250 AND b.product_id IS NULL
3251 AND EXISTS (SELECT 'X'
3252 FROM ams_act_access_denorm
3253 WHERE object_type = b.plan_type
3254 AND object_id = b.plan_id
3255 AND resource_id = outer.resource_id)
3256 )
3257 )
3258 )
3259 WHERE outer.fact_row_for = 'BILL_TO'
3260 AND outer.report_date = p_report_date ;
3261
3262 --UNPAID amount for 'SHIP_TO'
3263 Update ozf_res_cust_prod_facts outer
3264 Set (fund_unpaid)
3265 =
3266 (
3267 SELECT (NVL(SUM(earned_amt),0) - NVL(SUM(paid_amt),0)) tot_unpaid
3268 FROM ozf_cust_fund_summary_mv b,
3269 ozf_res_cust_prod c
3270 WHERE c.resource_id = outer.resource_id
3271 AND b.time_id = -1
3272 AND b.period_type_id = 256
3273 AND b.status_code = 'ACTIVE'
3274 AND b.party_id = outer.party_id
3275 AND b.party_id = c.party_id
3276 AND b.bill_to_site_use_id = outer.bill_to_site_use_id
3277 AND b.bill_to_site_use_id = c.bill_to_site_use_id
3278 AND b.ship_to_site_use_id = outer.ship_to_site_use_id
3279 AND b.ship_to_site_use_id = c.ship_to_site_use_id
3280 AND
3281 (
3282 ( b.product_level_type = DECODE(c.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
3283 AND b.product_id = c.product_attr_value
3284 )
3285 OR
3286 ( b.product_level_type IS NULL
3287 AND b.product_id IS NULL
3288 AND EXISTS (SELECT 'X'
3289 FROM ams_act_access_denorm
3290 WHERE object_type = b.plan_type
3291 AND object_id = b.plan_id
3292 AND resource_id = outer.resource_id)
3293 )
3294 )
3295 )
3296 WHERE outer.fact_row_for = 'SHIP_TO'
3297 AND outer.report_date = p_report_date ;
3298
3299 --YTD budget amount for 'PRODUCT'
3300 Update ozf_res_cust_prod_facts outer
3301 Set (YTD_FUND_utilized, YTD_FUND_earned, YTD_FUND_paid)
3302 =
3303 (
3304 SELECT NVL(SUM(utilized_amt),0) tot_utilized,
3305 NVL(SUM(earned_amt),0) tot_earned,
3306 NVL(SUM(paid_amt),0) tot_paid
3307 FROM ozf_time_day a,
3308 ozf_cust_fund_summary_mv b,
3309 ozf_res_cust_prod c
3310 WHERE c.resource_id = outer.resource_id
3311 AND a.report_date = outer.report_date
3312 AND a.ent_year_id = b.time_id
3313 AND b.status_code = 'ACTIVE'
3314 AND b.product_level_type = DECODE(outer.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
3315 AND b.product_id = outer.PRODUCT_ATTR_VALUE
3316 AND c.product_attr_value = b.product_id
3317 AND c.product_attribute = DECODE(b.product_level_type, 'FAMILY','PRICING_ATTRIBUTE2','PRICING_ATTRIBUTE1')
3318 AND b.party_id = c.party_id
3319 AND NVL(b.bill_to_site_use_id, c.bill_to_site_use_id) = c.bill_to_site_use_id
3320 AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
3321 FROM ams_act_access_denorm
3322 WHERE object_type = b.plan_type
3323 AND object_id = b.plan_id
3324 AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
3325 )
3326 WHERE outer.fact_row_for = 'PRODUCT'
3327 AND outer.report_date = p_report_date ;
3328
3329 --QTD budget amount for 'PRODUCT'
3330 Update ozf_res_cust_prod_facts outer
3331 Set (QTD_FUND_utilized, QTD_FUND_earned, QTD_FUND_paid)
3332 =
3333 (
3334 SELECT NVL(SUM(utilized_amt),0) tot_utilized,
3335 NVL(SUM(earned_amt),0) tot_earned,
3336 NVL(SUM(paid_amt),0) tot_paid
3337 FROM ozf_time_day a,
3338 ozf_cust_fund_summary_mv b,
3339 ozf_res_cust_prod c
3340 WHERE c.resource_id = outer.resource_id
3341 AND a.report_date = outer.report_date
3342 AND a.ent_qtr_id = b.time_id
3343 AND b.status_code = 'ACTIVE'
3344 AND b.product_level_type = DECODE(outer.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
3345 AND b.product_id = outer.PRODUCT_ATTR_VALUE
3346 AND c.product_attr_value = b.product_id
3347 AND c.product_attribute = DECODE(b.product_level_type, 'FAMILY','PRICING_ATTRIBUTE2','PRICING_ATTRIBUTE1')
3348 AND b.party_id = c.party_id
3349 AND NVL(b.bill_to_site_use_id, c.bill_to_site_use_id) = c.bill_to_site_use_id
3350 AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
3351 FROM ams_act_access_denorm
3352 WHERE object_type = b.plan_type
3353 AND object_id = b.plan_id
3354 AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
3355 )
3356 WHERE outer.fact_row_for = 'PRODUCT'
3357 AND outer.report_date = p_report_date ;
3358
3359 --MTD budget amount for 'PRODUCT'
3360 Update ozf_res_cust_prod_facts outer
3361 Set (MTD_FUND_utilized, MTD_FUND_earned, MTD_FUND_paid)
3362 =
3363 (
3364 SELECT NVL(SUM(utilized_amt),0) tot_utilized,
3365 NVL(SUM(earned_amt),0) tot_earned,
3366 NVL(SUM(paid_amt),0) tot_paid
3367 FROM ozf_time_day a,
3368 ozf_cust_fund_summary_mv b,
3369 ozf_res_cust_prod c
3370 WHERE c.resource_id = outer.resource_id
3371 AND a.report_date = outer.report_date
3372 AND a.ent_period_id = b.time_id
3373 AND b.status_code = 'ACTIVE'
3374 AND b.product_level_type = DECODE(outer.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
3375 AND b.product_id = outer.PRODUCT_ATTR_VALUE
3376 AND c.product_attr_value = b.product_id
3377 AND c.product_attribute = DECODE(b.product_level_type, 'FAMILY','PRICING_ATTRIBUTE2','PRICING_ATTRIBUTE1')
3378 AND b.party_id = c.party_id
3379 AND NVL(b.bill_to_site_use_id, c.bill_to_site_use_id) = c.bill_to_site_use_id
3380 AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
3381 FROM ams_act_access_denorm
3382 WHERE object_type = b.plan_type
3383 AND object_id = b.plan_id
3384 AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
3385 )
3386 WHERE outer.fact_row_for = 'PRODUCT'
3387 AND outer.report_date = p_report_date ;
3388
3389 --Unpaid budget amount for 'PRODUCT'
3390 Update ozf_res_cust_prod_facts outer
3391 Set (fund_unpaid)
3392 =
3393 (
3394 SELECT (NVL(SUM(earned_amt),0) - NVL(SUM(paid_amt),0)) tot_unpaid
3395 FROM ozf_cust_fund_summary_mv b,
3396 ozf_res_cust_prod c
3397 WHERE c.resource_id = outer.resource_id
3398 AND b.time_id = -1
3399 AND b.period_type_id = 256
3400 AND b.status_code = 'ACTIVE'
3401 AND b.product_level_type = DECODE(outer.product_attribute, 'PRICING_ATTRIBUTE2','FAMILY','PRODUCT')
3402 AND b.product_id = outer.PRODUCT_ATTR_VALUE
3403 AND c.product_attr_value = b.product_id
3404 AND c.product_attribute = DECODE(b.product_level_type, 'FAMILY','PRICING_ATTRIBUTE2','PRICING_ATTRIBUTE1')
3405 AND b.party_id = c.party_id
3406 AND NVL(b.bill_to_site_use_id, c.bill_to_site_use_id) = c.bill_to_site_use_id
3407 AND NVL(b.ship_to_site_use_id, DECODE((SELECT count(object_id)
3408 FROM ams_act_access_denorm
3409 WHERE object_type = b.plan_type
3410 AND object_id = b.plan_id
3411 AND resource_id = outer.resource_id), 0, 0, c.ship_to_site_use_id)) = c.ship_to_site_use_id
3412 )
3413 WHERE outer.fact_row_for = 'PRODUCT'
3414 AND outer.report_date = p_report_date ;
3415
3416 --Open Claims amount for 'PARTY'
3417 Update ozf_res_cust_prod_facts outer
3418 Set (OPEN_CLAIMS)
3419 =
3420 (
3421 SELECT NVL(SUM(amount_remaining),0)
3422 FROM ozf_claims_all b,
3423 (SELECT DISTINCT resource_id, party_id, cust_account_id, ship_to_site_use_id FROM ozf_res_cust_prod) c
3424 WHERE c.resource_id = outer.resource_id
3425 AND c.party_id = outer.party_id
3426 AND b.cust_account_id = c.cust_account_id
3427 AND b.claim_date <= outer.report_date
3428 AND b.status_code = 'OPEN'
3429 AND b.claim_class <> 'GROUP'
3430 AND
3431 (
3432 (b.cust_shipto_acct_site_id = c.ship_to_site_use_id )
3433 OR
3434 (b.cust_shipto_acct_site_id IS NULL)
3435 )
3436 )
3437 WHERE outer.fact_row_for = 'PARTY'
3438 AND outer.report_date = p_report_date ;
3439
3440 --Open Claims amount for 'BILL_TO'
3441 Update ozf_res_cust_prod_facts outer
3442 Set (OPEN_CLAIMS)
3443 =
3444 (
3445 SELECT NVL(SUM(amount_remaining),0)
3446 FROM ozf_claims_all b,
3447 (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
3448 WHERE c.resource_id = outer.resource_id
3449 AND c.party_id = outer.party_id
3450 AND c.bill_to_site_use_id = outer.bill_to_site_use_id
3451 AND b.cust_billto_acct_site_id = outer.bill_to_site_use_id
3452 AND b.cust_account_id = c.cust_account_id
3453 AND b.claim_date <= outer.report_date
3454 AND b.status_code = 'OPEN'
3455 AND b.claim_class <> 'GROUP'
3456 AND
3457 (
3458 (b.cust_shipto_acct_site_id = c.ship_to_site_use_id )
3459 OR
3460 (b.cust_shipto_acct_site_id IS NULL)
3461 )
3462 )
3463 WHERE outer.fact_row_for = 'BILL_TO'
3464 AND outer.report_date = p_report_date ;
3465
3466 --Open Claims amount for 'SHIP_TO'
3467 Update ozf_res_cust_prod_facts outer
3468 Set (OPEN_CLAIMS)
3469 =
3470 (
3471 SELECT NVL(SUM(amount_remaining),0)
3472 FROM ozf_claims_all b,
3473 (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
3474 WHERE c.resource_id = outer.resource_id
3475 AND c.party_id = outer.party_id
3476 AND c.bill_to_site_use_id = outer.bill_to_site_use_id
3477 AND b.cust_billto_acct_site_id = outer.bill_to_site_use_id
3478 AND c.ship_to_site_use_id = outer.ship_to_site_use_id
3479 AND b.cust_shipto_acct_site_id = outer.ship_to_site_use_id
3480 AND b.cust_account_id = c.cust_account_id
3481 AND b.claim_date <= outer.report_date
3482 AND b.status_code = 'OPEN'
3483 AND b.claim_class <> 'GROUP'
3484 )
3485 WHERE outer.fact_row_for = 'SHIP_TO'
3486 AND outer.report_date = p_report_date ;
3487
3488 ozf_utility_pvt.write_conc_log('Private API: ' || l_full_name || ' (+)');
3489
3490 EXCEPTION
3491 WHEN FND_API.G_EXC_ERROR THEN
3492 x_return_status := FND_API.g_ret_sts_error ;
3493 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
3494 p_count => x_msg_count,
3495 p_data => x_msg_data);
3496
3497 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3498 x_return_status := FND_API.g_ret_sts_unexp_error ;
3499 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
3500 p_count => x_msg_count,
3501 p_data => x_msg_data);
3502
3503 WHEN OTHERS THEN
3504 x_return_status := FND_API.g_ret_sts_unexp_error ;
3505 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
3506 p_count => x_msg_count,
3507 p_data => x_msg_data);
3508 END refresh_budget_and_claims_info;
3509
3510
3511 PROCEDURE load_daily_facts( ERRBUF OUT NOCOPY VARCHAR2,
3512 RETCODE OUT NOCOPY NUMBER,
3513 p_report_date IN VARCHAR2 )
3514 IS
3515
3516 l_api_version CONSTANT NUMBER := 1.0;
3517 p_api_version CONSTANT NUMBER := 1.0;
3518 l_api_name CONSTANT VARCHAR2(30) := 'load_daily_facts';
3519 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
3520 x_msg_count NUMBER;
3521 x_msg_data VARCHAR2(240);
3522 x_return_status VARCHAR2(1) ;
3523 l_init_msg_list VARCHAR2(10) := FND_API.G_FALSE;
3524
3525 l_report_date DATE;
3526
3527 BEGIN
3528
3529 -- Standard call to check for call compatibility.
3530 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3531 p_api_version,
3532 l_api_name,
3533 g_pkg_name)
3534 THEN
3535 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3536 END IF;
3537
3538 -- Initialize message list if p_init_msg_list is set to TRUE.
3539 FND_MSG_PUB.initialize;
3540
3541 -- Debug Message
3542 ozf_utility_pvt.write_conc_log('Private API: ' || l_api_name || ' (-)');
3543
3544 -- Initialize API return status to SUCCESS
3545 x_return_status := FND_API.G_RET_STS_SUCCESS;
3546
3547 l_report_date := trunc(to_date(p_report_date,'YYYY/MM/DD HH24:MI:SS'));
3548
3549 IF l_report_date IS NULL
3550 THEN
3551 l_report_date := TRUNC(SYSDATE);
3552 END IF;
3553
3554 ozf_utility_pvt.write_conc_log(' -- report_date is : ' || l_report_date );
3555 --
3556 -- Refresh Account and Products
3557 --
3558
3559 refresh_accts_and_products(
3560 l_api_version ,
3561 l_init_msg_list ,
3562 l_report_date ,
3563 x_return_status ,
3564 x_msg_count ,
3565 x_msg_data );
3566
3567 IF x_return_status = FND_API.g_ret_sts_error
3568 THEN
3569 RAISE FND_API.g_exc_error;
3570 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3571 RAISE FND_API.g_exc_unexpected_error;
3572 END IF;
3573
3574 --
3575 -- Refresh Sales Info
3576 --
3577
3578 -- ozf_utility_pvt.write_conc_log('-22');
3579 refresh_sales_info(
3580 l_api_version ,
3581 l_init_msg_list ,
3582 l_report_date ,
3583 x_return_status ,
3584 x_msg_count ,
3585 x_msg_data );
3586
3587 IF x_return_status = FND_API.g_ret_sts_error
3588 THEN
3589 RAISE FND_API.g_exc_error;
3590 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3591 RAISE FND_API.g_exc_unexpected_error;
3592 END IF;
3593
3594 refresh_target_info(
3595 l_api_version ,
3596 l_init_msg_list ,
3597 l_report_date ,
3598 x_return_status ,
3599 x_msg_count ,
3600 x_msg_data );
3601
3602 IF x_return_status = FND_API.g_ret_sts_error
3603 THEN
3604 RAISE FND_API.g_exc_error;
3605 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3606 RAISE FND_API.g_exc_unexpected_error;
3607 END IF;
3608
3609 -- ozf_utility_pvt.write_conc_log('-33');
3610 --
3611 -- Refresh Order Info
3612 --
3613
3614 refresh_orders_info(
3615 l_api_version ,
3616 l_init_msg_list ,
3617 l_report_date ,
3618 x_return_status ,
3619 x_msg_count ,
3620 x_msg_data );
3621
3622 IF x_return_status = FND_API.g_ret_sts_error
3623 THEN
3624 RAISE FND_API.g_exc_error;
3625 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3626 RAISE FND_API.g_exc_unexpected_error;
3627 END IF;
3628
3629 --
3630 -- Refresh KPI Info
3631 --
3632
3633 -- ozf_utility_pvt.write_conc_log('-44');
3634
3635 refresh_kpi_info(
3636 l_api_version ,
3637 l_init_msg_list ,
3638 l_report_date ,
3639 x_return_status ,
3640 x_msg_count ,
3641 x_msg_data );
3642
3643 IF x_return_status = FND_API.g_ret_sts_error
3644 THEN
3645 RAISE FND_API.g_exc_error;
3646 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3647 RAISE FND_API.g_exc_unexpected_error;
3648 END IF;
3649
3650
3651 populate_res_cust_prod_facts (l_api_version ,
3652 l_init_msg_list ,
3653 l_report_date,
3654 x_return_status,
3655 x_msg_count,
3656 x_msg_data);
3657
3658 IF x_return_status = FND_API.g_ret_sts_error
3659 THEN
3660 RAISE FND_API.g_exc_error;
3661 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3662 RAISE FND_API.g_exc_unexpected_error;
3663 END IF;
3664
3665 refresh_budget_and_claims_info(l_api_version ,
3666 l_init_msg_list,
3667 l_report_date,
3668 x_return_status,
3669 x_msg_count,
3670 x_msg_data);
3671
3672 IF x_return_status = FND_API.g_ret_sts_error
3673 THEN
3674 RAISE FND_API.g_exc_error;
3675 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
3676 RAISE FND_API.g_exc_unexpected_error;
3677 END IF;
3678
3679 -- ozf_utility_pvt.write_conc_log('- End');
3680
3681 /*
3682 update_sales_info(
3683 l_api_version ,
3684 l_init_msg_list ,
3685 l_report_date ,
3686 x_return_status ,
3687 x_msg_count ,
3688 x_msg_data ) ;
3689 */
3690
3691 update_quota_sales_info(
3692 l_api_version ,
3693 l_init_msg_list ,
3694 l_report_date ,
3695 x_return_status ,
3696 x_msg_count ,
3697 x_msg_data ) ;
3698
3699 ozf_utility_pvt.write_conc_log('Private API: ' || l_api_name || ' (+)');
3700
3701 EXCEPTION
3702 WHEN FND_API.G_EXC_ERROR THEN
3703 x_return_status := FND_API.g_ret_sts_error ;
3704 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
3705 p_count => x_msg_count,
3706 p_data => x_msg_data);
3707 ERRBUF := x_msg_data;
3708 RETCODE := 2;
3709 ozf_utility_pvt.write_conc_log('Private API: ' || l_api_name || ' Expected Error');
3710
3711 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3712 x_return_status := FND_API.g_ret_sts_unexp_error ;
3713 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
3714 p_count => x_msg_count,
3715 p_data => x_msg_data);
3716 ERRBUF := x_msg_data;
3717 RETCODE := 2;
3718 ozf_utility_pvt.write_conc_log('Private API: ' || l_api_name || ' Error');
3719
3720 WHEN OTHERS THEN
3721 x_return_status := FND_API.g_ret_sts_unexp_error ;
3722 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
3723 p_count => x_msg_count,
3724 p_data => x_msg_data);
3725
3726 ERRBUF := sqlerrm(sqlcode);
3727 RETCODE := sqlcode;
3728 ozf_utility_pvt.write_conc_log('Private API: ' || l_api_name || ' Others');
3729
3730 END load_daily_facts;
3731
3732
3733
3734 PROCEDURE get_dates ( p_period_type_id IN NUMBER,
3735 p_time_id IN NUMBER,
3736 x_record_type_id OUT NOCOPY NUMBER,
3737 x_start_date OUT NOCOPY DATE,
3738 x_end_date OUT NOCOPY DATE) IS
3739
3740
3741 BEGIN
3742 IF p_period_type_id = 64
3743 THEN
3744 --
3745 x_record_type_id := 55;
3746
3747 SELECT start_date, end_date INTO x_start_date, x_end_date
3748 FROM ozf_time_ent_qtr
3749 WHERE ent_qtr_id = p_time_id;
3750 --
3751 ELSIF p_period_type_id = 32
3752 THEN
3753 --
3754 x_record_type_id := 23;
3755
3756 SELECT start_date, end_date INTO x_start_date, x_end_date
3757 FROM ozf_time_ent_period
3758 WHERE ent_period_id = p_time_id;
3759 --
3760 ELSIF p_period_type_id = 16
3761 THEN
3762 --
3763 x_record_type_id := 11;
3764
3765 SELECT start_date, end_date INTO x_start_date, x_end_date
3766 FROM ozf_time_week
3767 WHERE week_id = p_time_id;
3768 --
3769 ELSIF p_period_type_id = 1
3770 THEN
3771 --
3772 x_record_type_id := 1;
3773
3774 SELECT start_date, end_date INTO x_start_date, x_end_date
3775 FROM ozf_time_day
3776 WHERE report_date_julian = p_time_id;
3777 --
3778 END IF;
3779 END get_dates;
3780
3781 -- This function is called from Sales Performance Graphs
3782
3783 FUNCTION get_xtd_total( p_period_type_id IN NUMBER,
3784 p_time_id IN NUMBER,
3785 p_start_date IN DATE,
3786 p_end_date IN DATE,
3787 p_type IN VARCHAR2)
3788 RETURN NUMBER IS
3789
3790
3791 CURSOR sales_csr (p_as_of_date IN DATE,
3792 p_record_type_id IN NUMBER) IS
3793 SELECT SUM(sales.sales)
3794 FROM ozf_search_selections_t acct,
3795 ozf_search_selections_t prod,
3796 ozf_order_sales_v sales,
3797 ozf_time_rpt_struct rpt
3798 where acct.search_type = 'QUALIFIER'
3799 and prod.search_type = 'ITEM'
3800 and acct.attribute_value = sales.ship_to_site_use_id
3801 and prod.attribute_value = sales.inventory_item_id
3802 and rpt.report_date = p_as_of_date
3803 and BITAND(rpt.record_type_id, p_record_type_id) = rpt.record_type_id
3804 and rpt.time_id = sales.time_id ;
3805
3806 -- Get Quota for the give time period
3807 /*
3808 CURSOR quota_csr IS
3809 SELECT ozf_cust_facts_pvt.get_cust_target ( b.site_use_id,
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'
3819 AND prod.search_type = 'ITEM'
3820 AND a.allocation_for = 'CUST'
3821 AND a.item_type = prod.attribute_id
3822 AND a.item_id = prod.attribute_value
3823 AND b.account_allocation_id = a.allocation_for_id
3824 AND b.site_use_code = 'SHIP_TO'
3825 AND b.site_use_id = prod.attribute_value
3826 AND c.allocation_for = 'PROD'
3827 AND c.allocation_for_id = a.product_allocation_id
3828 AND c.period_type_id = p_period_type_id
3829 AND c.time_id = p_time_id;
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
3839 FROM ozf_funds_all_b
3840 WHERE parent_fund_id IS NOT NULL
3841 AND start_date_active >= p_start_date
3842 AND end_date_active <= p_end_date )
3843 AND b.allocation_for = 'CUST'
3844 AND b.allocation_for_id = a.account_allocation_id
3845 AND NVL(b.account_status, 'X') <> 'D'
3846 AND b.period_type_id = p_period_type_id
3847 AND b.time_id = p_time_id ;
3848
3849
3850 l_record_type_id NUMBER;
3851 l_start_date DATE;
3852 l_end_date DATE;
3853 ly_start_date DATE;
3854 ly_end_date DATE;
3855
3856 l_end_xtd NUMBER;
3857 l_start_xtd NUMBER;
3858 l_xtd_sales NUMBER;
3859
3860 BEGIN
3861
3862 get_dates ( p_period_type_id ,
3863 p_time_id ,
3864 l_record_type_id ,
3865 l_start_date ,
3866 l_end_date ) ;
3867
3868 IF p_type = 'XTD'
3869 THEN
3870 --
3871
3872 IF p_start_date > l_start_date
3873 THEN
3874 -- Start Date is middle of month
3875 -- Sales for month = (XTD for l_end_date) - (XTD for p_start_date)
3876 OPEN sales_csr(l_end_date, l_record_type_id);
3877 FETCH sales_csr INTO l_end_xtd;
3878 CLOSE sales_csr;
3879
3880 OPEN sales_csr(p_end_date, l_record_type_id);
3881 FETCH sales_csr INTO l_start_xtd;
3882 CLOSE sales_csr;
3883
3884 l_xtd_sales := l_end_xtd - l_start_xtd;
3885 ELSE
3886 -- In all other cases. Just get XTD
3887
3888 OPEN sales_csr(l_end_date, l_record_type_id);
3889 FETCH sales_csr INTO l_xtd_sales;
3890 CLOSE sales_csr;
3891
3892 END IF;
3893
3894 --
3895 ELSIF p_type = 'LYSP'
3896 THEN
3897 --
3898 ly_start_date := add_months(p_start_date, -12);
3899 ly_end_date := add_months(p_end_date, -12);
3900 l_start_date := add_months(l_start_date , -12);
3901 l_end_date := add_months(l_end_date, -12) ;
3902
3903 IF ly_start_date > l_start_date
3904 THEN
3905 -- Start Date is middle of month
3906 -- Sales for month = (XTD for l_end_date) - (XTD for ly_start_date)
3907 OPEN sales_csr(l_end_date, l_record_type_id);
3908 FETCH sales_csr INTO l_end_xtd;
3909 CLOSE sales_csr;
3910
3911 OPEN sales_csr(ly_end_date, l_record_type_id);
3912 FETCH sales_csr INTO l_start_xtd;
3913 CLOSE sales_csr;
3914
3915 l_xtd_sales := l_end_xtd - l_start_xtd;
3916 ELSE
3917 -- In all other cases. Just get XTD
3918
3919 OPEN sales_csr(l_end_date, l_record_type_id);
3920 FETCH sales_csr INTO l_xtd_sales;
3921 CLOSE sales_csr;
3922 END IF;
3923 --
3924 ELSIF p_type = 'QUOTA'
3925 THEN
3926 OPEN quota_csr;
3927 FETCH quota_csr INTO l_xtd_sales;
3928 CLOSE quota_csr;
3929 END IF;
3930
3931 RETURN l_xtd_sales;
3932 EXCEPTION
3933 WHEN OTHERS THEN
3934 RETURN -999;
3935 END get_xtd_total ;
3936
3937 -- This function to display Dashboard Graph
3938
3939 FUNCTION get_xtd_total( p_resource_id IN NUMBER,
3940 p_period_type_id IN NUMBER,
3941 p_time_id IN NUMBER,
3942 p_type IN VARCHAR2 )
3943 RETURN NUMBER IS
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
3953 rpt.report_date = p_as_of_date
3954 AND BITAND(rpt.record_type_id, p_record_type_id )
3955 = rpt.record_type_id
3956 AND sales.time_id = rpt.time_id
3957 AND sales.ship_to_site_use_id = acct.site_use_id
3958 AND sales.inventory_item_id = prod.item_id
3959 AND prod.allocation_for = 'CUST'
3960 AND prod.allocation_for_id = acct.account_allocation_id
3961 AND acct.allocation_for = 'FUND'
3962 AND NVL(acct.account_status, 'X') <> 'D'
3963 -- R12: Do not consider UnAllocated Rows
3964 AND acct.parent_party_id <> -9999
3965 AND acct.allocation_for_id IN (
3966 SELECT aa.fund_id
3967 FROM ozf_funds_all_b aa
3968 WHERE aa.owner = p_resource_id
3969 AND aa.fund_type = 'QUOTA'
3970 AND aa.status_code <> 'CANCELLED'
3971 AND NOT EXISTS ( SELECT 1
3972 FROM ozf_funds_all_b bb
3973 WHERE bb.parent_fund_id = aa.fund_id )
3974 --
3975 UNION ALL-- Get all leaf node quotas in the hierarchy of this resource
3976 --
3977 SELECT aa.fund_id
3978 FROM ozf_funds_all_b aa
3979 WHERE aa.fund_type = 'QUOTA'
3980 AND aa.status_code <> 'CANCELLED'
3981 CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
3982 START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
3983 FROM ozf_funds_all_b bb
3984 WHERE bb.owner = p_resource_id
3985 AND bb.fund_type = 'QUOTA'
3986 AND bb.status_code <> 'CANCELLED' )
3987 );
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
3997 AND b.period_type_id = p_period_type_id
3998 AND b.time_id = p_time_id
3999 AND a.allocation_for = 'FUND'
4000 AND NVL(a.account_status, 'X') <> 'D'
4001 -- R12: Do not consider UnAllocated Rows
4002 AND a.parent_party_id <> -9999
4003 AND a.allocation_for_id IN ( -- Get leaf node quotas for this resource owns
4004 SELECT aa.fund_id
4005 FROM ozf_funds_all_b aa
4006 WHERE aa.owner = p_resource_id
4007 AND aa.fund_type = 'QUOTA'
4008 AND aa.status_code <> 'CANCELLED'
4009 AND NOT EXISTS ( SELECT 1
4010 FROM ozf_funds_all_b bb
4011 WHERE bb.parent_fund_id = aa.fund_id )
4012 --
4013 UNION ALL -- Get all leaf node quotas in the hierarchy of this resource
4014 --
4015 SELECT aa.fund_id
4016 FROM ozf_funds_all_b aa
4017 WHERE aa.fund_type = 'QUOTA'
4018 AND aa.status_code <> 'CANCELLED'
4019 CONNECT BY PRIOR aa.fund_id = aa.parent_fund_id
4020 START WITH aa.parent_fund_id IN ( SELECT bb.fund_id
4021 FROM ozf_funds_all_b bb
4022 WHERE bb.owner = p_resource_id
4023 AND bb.fund_type = 'QUOTA'
4024 AND bb.status_code <> 'CANCELLED' )
4025 );
4026
4027
4028 l_record_type_id NUMBER;
4029 l_start_date DATE;
4030 l_end_date DATE;
4031
4032 l_end_xtd NUMBER;
4033 l_start_xtd NUMBER;
4034 l_xtd_sales NUMBER;
4035
4036 l_return_value NUMBER;
4037
4038 BEGIN
4039
4040 get_dates ( p_period_type_id ,
4041 p_time_id ,
4042 l_record_type_id ,
4043 l_start_date ,
4044 l_end_date ) ;
4045
4046 IF p_type = 'XTD'
4047 THEN
4048
4049 OPEN sales_csr(l_end_date, l_record_type_id);
4050 FETCH sales_csr INTO l_return_value ;
4051 CLOSE sales_csr;
4052
4053 ELSIF p_type = 'LYSP'
4054 THEN
4055
4056 l_end_date := add_months(l_end_date, -12) ;
4057
4058 OPEN sales_csr(l_end_date, l_record_type_id);
4059 FETCH sales_csr INTO l_return_value;
4060 CLOSE sales_csr;
4061
4062 ELSIF p_type = 'QUOTA'
4063 THEN
4064
4065 OPEN quota_csr;
4066 FETCH quota_csr INTO l_return_value;
4067 CLOSE quota_csr;
4068
4069 END IF;
4070
4071 RETURN l_return_value;
4072
4073 EXCEPTION
4074 WHEN OTHERS THEN
4075 RETURN -9999;
4076 END get_xtd_total ;
4077
4078 END ozf_cust_facts_pvt;