[Home] [Help]
PACKAGE BODY: APPS.BIM_I_BGT_FACTS_PKG
Source
1 PACKAGE BODY BIM_I_BGT_FACTS_PKG AS
2 /*$Header: bimibgfb.pls 120.3 2005/10/14 06:11:40 sbassi noship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(20) := 'BIM_I_BGT_FACTS_PKG';
5 g_file_name CONSTANT VARCHAR2(20) := 'bimibgfb.pls';
6 l_global_currency_code CONSTANT varchar2(20) := bis_common_parameters.get_currency_code;
7 l_secondary_currency_code CONSTANT VARCHAR2(20) :=bis_common_parameters.get_secondary_currency_code;
8 l_pgc_rate_type CONSTANT VARCHAR2(20) :=bis_common_parameters.Get_Rate_Type;
9 l_sgc_rate_type CONSTANT VARCHAR2(20) :=bis_common_parameters.Get_secondary_Rate_Type;
10 -- Checks for any missing currency from budget facts table
11
12 FUNCTION Check_Missing_Rates (p_start_date IN Date)
13 Return NUMBER
14 AS
15 l_cnt_miss_rate1 Number := 0;
16 l_cnt_miss_rate2 Number := 0;
17 l_msg_name Varchar2(40);
18
19 CURSOR C_missing_rates1
20 IS
21 SELECT tc_code from_currency,
22 decode(prim_conversion_rate,-3,to_date('01/01/1999','MM/DD/RRRR'),trx_date) transaction_create_date
23 FROM BIM_I_BGT_RATES
24 WHERE prim_conversion_rate < 0
25 AND tc_code is not null
26 AND trx_date >= p_start_date
27 ORDER BY tc_code,
28 trx_date ;
29
30 CURSOR C_missing_rates2
31 IS
32 SELECT tc_code from_currency,
33 decode(sec_conversion_rate,-3,to_date('01/01/1999','MM/DD/RRRR'),trx_date) transaction_create_date
34 FROM BIM_I_BGT_RATES
35 WHERE sec_conversion_rate < 0
36 AND tc_code is not null
37 AND trx_date >= p_start_date
38 ORDER BY tc_code,
39 trx_date ;
40 BEGIN
41 l_msg_name:= 'BIS_DBI_CURR_NO_LOAD';
42 SELECT COUNT(*) INTO l_cnt_miss_rate1 FROM BIM_I_BGT_RATES
43 WHERE
44 prim_conversion_rate < 0
45 AND tc_code is not null
46 AND trx_date >= p_start_date;
47
48 SELECT COUNT(*) INTO l_cnt_miss_rate2 FROM BIM_I_BGT_RATES
49 WHERE
50 sec_conversion_rate <0
51 AND tc_code is not null
52 AND trx_date >= p_start_date;
53
54 If(l_cnt_miss_rate1 > 0 )
55 Then
56 FND_MESSAGE.Set_Name('FII',l_msg_name);
57 BIS_COLLECTION_UTILITIES.debug(l_msg_name||': '||FND_MESSAGE.get);
58 BIS_COLLECTION_UTILITIES.log('Primary Conversion rate could not be found for the given currency. Please check output file for more details' );
59 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
60 FOR rate_record1 in C_missing_rates1
61 LOOP
62 BIS_COLLECTION_UTILITIES.writeMissingRate(
63 p_rate_type => l_pgc_rate_type,
64 p_from_currency => rate_record1.from_currency,
65 p_to_currency => l_global_currency_code,
66 p_date => rate_record1.transaction_create_date);
67 END LOOP;
68 BIS_COLLECTION_UTILITIES.debug('before returning -1' );
69 RETURN -1;
70 ELSE
71 Return 1;
72 End If;
73
74 If(l_cnt_miss_rate2 > 0 and l_secondary_currency_code is not null )
75 Then
76 FND_MESSAGE.Set_Name('FII',l_msg_name);
77 BIS_COLLECTION_UTILITIES.debug(l_msg_name||': '||FND_MESSAGE.get);
78 BIS_COLLECTION_UTILITIES.log('Secondary Conversion rate could not be found for the given currency. Please check output file for more details' );
79 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
80 FOR rate_record2 in C_missing_rates2
81 LOOP
82 BIS_COLLECTION_UTILITIES.writeMissingRate(
83 p_rate_type => l_sgc_rate_type,
84 p_from_currency => rate_record2.from_currency,
85 p_to_currency => l_secondary_currency_code,
86 p_date => rate_record2.transaction_create_date);
87 END LOOP;
88 BIS_COLLECTION_UTILITIES.debug('before returning -1' );
89 RETURN -1;
90 ELSE
91 Return 1;
92 End If;
93
94 EXCEPTION
95 WHEN OTHERS THEN
96 BIS_COLLECTION_UTILITIES.Debug('Error in Check_missing_rates:'||sqlerrm);
97 RAISE;
98 END Check_Missing_Rates;
99
100 ---------------------------------------------------------------------------------------------------
101 /* This procedure will conditionally call the FIRST_LOAD or the SUB_LOAD */
102 ---------------------------------------------------------------------------------------------------
103 PROCEDURE POPULATE
104 (
105 p_api_version_number IN NUMBER
106 ,p_init_msg_list IN VARCHAR2
107 ,p_validation_level IN NUMBER
108 ,p_commit IN VARCHAR2
109 ,x_msg_count OUT NOCOPY NUMBER
110 ,x_msg_data OUT NOCOPY VARCHAR2
111 ,x_return_status OUT NOCOPY VARCHAR2
112 ,p_start_date IN DATE
113 ,p_end_date IN DATE
114 ,p_para_num IN NUMBER
115 ,p_truncate_flg IN VARCHAR2
116 ) IS
117
118 l_profile NUMBER;
119 v_error_code NUMBER;
120 v_error_text VARCHAR2(1500);
121 l_last_update_date DATE;
122 l_user_id NUMBER := FND_GLOBAL.USER_ID();
123 l_api_version_number CONSTANT NUMBER := 1.0;
124 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE';
125 l_success VARCHAR2(3);
126 l_mesg_text VARCHAR2(100);
127 l_load_type VARCHAR2(100);
128 l_period_error VARCHAR2(5000);
129 l_currency_error VARCHAR2(5000);
130 l_err_code NUMBER;
131 l_count number := 0;
132 l_global_start_date DATE;
133 l_missing_date BOOLEAN := FALSE;
134
135 l_conc_start_date DATE;
136 l_conc_end_date DATE;
137 l_start_date DATE;
138 l_end_date DATE;
139 l_global_date DATE;
140 l_sysdate DATE;
141
142 BEGIN
143
144 -- Standard call to check for call compatibility.
145 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
146 p_api_version_number,
147 l_api_name,
148 g_pkg_name)
149 THEN
150 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
151 END IF;
152
153 -- Initialize message list if p_init_msg_list is set to TRUE.
154 IF FND_API.to_Boolean( p_init_msg_list )
155 THEN
156 FND_MSG_PUB.initialize;
157 END IF;
158
159 -- Initialize API return status to SUCCESS
160 x_return_status := FND_API.G_RET_STS_SUCCESS;
161
162 -- Debug Message
163 -- AMS_UTILITY_PVT.debug_message('Private API: ' || 'Running the populate procedure');
164 BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: Facts load starts at:'||sysdate);
165
166 l_global_start_date :=BIS_COMMON_PARAMETERS.GET_GLOBAL_START_DATE();
167
168 /* THIS CODE REPLACES THE GET_LAST_REFRESH_PERIOD TO GET_LAST_REFRESH_DATES */
169
170 bis_collection_utilities.get_last_refresh_dates('BUDGET_FACTS'
171 ,l_conc_start_date,l_conc_end_date,l_start_date,l_end_date);
172
173
174 IF (l_end_date IS NULL) THEN
175
176 IF (p_start_date IS NULL) THEN
177 bis_collection_utilities.log('Please run the Upadate budget Facts First Time Base Summary concurrent program before running this');
178 RAISE FND_API.G_EXC_ERROR;
179 END IF;
180
181 IF (p_start_date >= p_end_date) THEN
182 bis_collection_utilities.log('Start Date Can not be greater than End Date');
183 RAISE FND_API.G_EXC_ERROR;
184 END IF;
185 --Validate time dimension tables
186 fii_time_api.check_missing_date (greatest(l_global_start_date,p_start_date), sysdate, l_missing_date);
187 IF (l_missing_date) THEN
188 bis_collection_utilities.log('Time dimension has at least one missing date between ' || greatest(l_global_start_date,p_start_date) || ' and ' || sysdate);
189 RAISE FND_API.G_EXC_ERROR;
190 END IF;
191
192 l_load_type := 'FIRST_LOAD';
193 BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: First Load');
194
195 FIRST_LOAD(p_start_date => greatest(l_global_start_date,p_start_date)
196 ,p_end_date => sysdate
197 ,p_api_version_number => l_api_version_number
198 ,p_init_msg_list => FND_API.G_FALSE
199 ,p_load_type => l_load_type
200 ,x_msg_count => x_msg_count
201 ,x_msg_data => x_msg_data
202 ,x_return_status => x_return_status
203 );
204
205 ELSE
206 --i.e Incremental has to be executed.
207 IF p_truncate_flg = 'Y' THEN
208
209 l_load_type := 'FIRST_LOAD';
210 l_sysdate := sysdate;
211
212 FIRST_LOAD(p_start_date => greatest(l_global_start_date,p_start_date)
213 ,p_end_date => l_sysdate
214 ,p_api_version_number => l_api_version_number
215 ,p_init_msg_list => FND_API.G_FALSE
216 ,p_load_type => l_load_type
217 ,x_msg_count => x_msg_count
218 ,x_msg_data => x_msg_data
219 ,x_return_status => x_return_status
220 );
221 ELSE
222
223
224 IF (l_end_date >= sysdate) THEN
225 bis_collection_utilities.log('Load Progarm already run upto ' || l_end_date);
226 RAISE FND_API.g_exc_error;
227 END IF;
228 --Validate time dimension tables
229 fii_time_api.check_missing_date (l_end_date, sysdate, l_missing_date);
230 IF (l_missing_date) THEN
231 bis_collection_utilities.log('Time dimension has atleast one missing date between ' || l_end_date || ' and ' || sysdate);
232 RAISE FND_API.G_EXC_ERROR;
233 END IF;
234
235 l_load_type := 'SUBSEQUENT_LOAD';
236 BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: Incremental Load');
237 /*SUB_LOAD(p_start_date => l_end_date+1/86400
238 ,p_end_date => sysdate
239 ,p_api_version_number => l_api_version_number
240 ,p_init_msg_list => FND_API.G_FALSE
241 ,p_load_type => l_load_type
242 ,x_msg_count => x_msg_count
243 ,x_msg_data => x_msg_data
244 ,x_return_status => x_return_status
245 );*/
246 SUB_LOAD(p_start_date => trunc(l_end_date)
247 ,p_end_date => sysdate
248 ,p_api_version_number => l_api_version_number
249 ,p_init_msg_list => FND_API.G_FALSE
250 ,p_load_type => l_load_type
251 ,x_msg_count => x_msg_count
252 ,x_msg_data => x_msg_data
253 ,x_return_status => x_return_status
254 );
255 END IF;
256 END IF;
257
258 IF x_return_status = FND_API.g_ret_sts_error
259 THEN
260 RAISE FND_API.g_exc_error;
261 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
262 RAISE FND_API.g_exc_unexpected_error;
263 END IF;
264
265 --Standard check of commit
266 IF FND_API.To_Boolean ( p_commit ) THEN
267 COMMIT WORK;
268 END IF;
269
270 COMMIT;
271 BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: Facts Concurrent Program Succesfully Completed');
272
273 -- Standard call to get message count and if count is 1, get message info.
274 FND_MSG_PUB.Count_And_Get
275 (p_count => x_msg_count,
276 p_data => x_msg_data
277 );
278
279 EXCEPTION
280
281 WHEN FND_API.G_EXC_ERROR THEN
282 x_return_status := FND_API.G_RET_STS_ERROR;
283 -- Standard call to get message count and if count=1, get the message
284 FND_msg_PUB.Count_And_Get (
285 -- p_encoded => FND_API.G_FALSE,
286 p_count => x_msg_count,
287 p_data => x_msg_data
288 );
289
290
291 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
292
293 /* FOR l_counter IN 1 .. x_msg_count
294 LOOP
295 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
296 fnd_msg_pub.dump_msg(l_counter);
297 end loop; */
298
299 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
300 -- Standard call to get message count and if count=1, get the message
301 FND_msg_PUB.Count_And_Get (
302 --p_encoded => FND_API.G_FALSE,
303 p_count => x_msg_count,
304 p_data => x_msg_data
305 );
306
307 WHEN OTHERS THEN
308 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
309 IF FND_msg_PUB.Check_msg_Level ( FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
310 THEN
311 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
312 END IF;
313 -- Standard call to get message count and if count=1, get the message
314 /* FND_msg_PUB.Count_And_Get (
315 -- p_encoded => FND_API.G_FALSE,
316 p_count => x_msg_count,
317 p_data => x_msg_data
318 );*/
319
320 END POPULATE;
321
322
323 --------------------------------------------------------------------------------------------------
324 -- This procedure will excute when data is loaded for the first time
325
326 -- PROCEDURE FIRST_LOAD
327 --------------------------------------------------------------------------------------------------
328
329 PROCEDURE FIRST_LOAD
330 ( p_start_date IN DATE
331 ,p_end_date IN DATE
332 ,p_api_version_number IN NUMBER
333 ,p_init_msg_list IN VARCHAR2
334 ,p_load_type IN VARCHAR2
335 ,x_msg_count OUT NOCOPY NUMBER
336 ,x_msg_data OUT NOCOPY VARCHAR2
337 ,x_return_status OUT NOCOPY VARCHAR2
338 )
339 IS
340 l_user_id NUMBER := FND_GLOBAL.USER_ID();
341 l_start_date DATE;
342 l_end_date DATE;
343 l_last_update_date DATE;
344 l_success VARCHAR2(3);
345 l_api_version_number CONSTANT NUMBER := 1.0;
346 l_api_name CONSTANT VARCHAR2(30) := 'FIRST_LOAD';
347 l_seq_name VARCHAR(100);
348 l_table_name VARCHAR2(100);
349 l_temp_msg VARCHAR2(100);
350 l_check_missing_rate NUMBER;
351 l_stmt VARCHAR2(50);
352 l_min_date date;
353
354 l_status VARCHAR2(5);
355 l_industry VARCHAR2(5);
356 l_schema VARCHAR2(30);
357 l_return BOOLEAN;
358 BEGIN
359
360 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
361
362 --dbms_output.put_line('inside first load:'|| p_start_date || ' '|| p_end_date);
363
364 -- Standard call to check for call compatibility.
365 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
366 p_api_version_number,
367 l_api_name,
368 g_pkg_name)
369 THEN
370 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
371 END IF;
372
373 -- Initialize message list if p_init_msg_list is set to TRUE.
374 IF FND_API.to_Boolean( p_init_msg_list )
375 THEN
376 FND_msg_PUB.initialize;
377 END IF;
378
379 -- Initialize API return status to SUCCESS
380 x_return_status := FND_API.G_RET_STS_SUCCESS;
381
382 --dbms_output.put_line('BIM_I_BUDGET_FACTS: Running the First Load '||sqlerrm(sqlcode));
383
384 -- The below four commands are necessary for the purpose of the parallel insertion */
385 BEGIN
386 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL dml ';
387 --EXECUTE IMMEDIATE 'ALTER SESSION SET SORT_AREA_SIZE=100000000 ';
388 --EXECUTE IMMEDIATE 'ALTER SESSION SET HASH_AREA_SIZE=100000000 ';
389 --EXECUTE IMMEDIATE 'ALTER TABLE BIM_I_BUDGET_FACTS nologging ';
390 -- EXECUTE IMMEDIATE 'ALTER SEQUENCE BIM_I_BUDGET_FACTS_s CACHE 1000 ';
391
395 BIM_UTL_PKG.drop_index('BIM_I_BUDGET_FACTS');
392
393 /* Piece of Code for retrieving,storing storage parameters and Dropping the indexes */
394 BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Drop index before inserting.');
396 /* End of Code for dropping the existing indexes */
397 EXCEPTION when others then
398 BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: error:'||sqlerrm(sqlcode));
399 --dbms_output.put_line('error first:'||sqlerrm(sqlcode));
400 END;
401 l_table_name :='BIM_I_BUDGET_FACTS';
402 EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_BUDGET_FACTS_STG';
403 EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_BGT_RATES';
404 BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:First insert into table BIM_I_BUDGET_FACTS_STG');
405 -- dbms_output.put_Line('JUST BEFORE THE MAIN INSERT STATMENT');
406 INSERT /*+ append parallel */
407 INTO BIM_I_BUDGET_FACTS_STG CDF(
408 creation_date
409 ,last_update_date
410 ,created_by
411 ,last_updated_by
412 ,last_update_login
413 ,fund_id
414 ,parent_fund_id
415 ,fund_number
416 ,start_date
417 ,end_date
418 ,start_period
419 ,end_period
420 ,set_of_books_id
421 ,fund_type
422 --,region
423 ,country
424 ,org_id
425 ,category_id
426 ,status
427 ,original_budget
428 ,transfer_in
429 ,transfer_out
430 ,holdback_amt
431 ,currency_code_fc
432 ,delete_flag
433 ,transaction_create_date
434 ,business_unit_id
435 ,from_currency
436 ,conversion_rate
437 ,planned
438 ,committed
439 ,utilized
440 ,paid
441 ,metric_type
442 ,accrual
443 ,conversion_rate_s
444 ,original_budget_s
445 ,transfer_in_s
446 ,transfer_out_s
447 ,holdback_amt_s
448 ,planned_s
449 ,committed_s
450 ,utilized_s
451 ,accrual_s
452 ,paid_s)
453 SELECT /*+ parallel */
454 sysdate,
455 sysdate,
456 l_user_id,
457 l_user_id,
458 l_user_id,
459 inner.fund_id,
460 inner.parent_fund_id,
461 inner.fund_number,
462 inner.start_date,
463 inner.end_date,
464 inner.start_period,
465 inner.end_period,
466 inner.set_of_books_id,
467 inner.fund_type,
468 --inner.region,
469 inner.country,
470 inner.org_id,
471 inner.category_id,
472 inner.status,
473 inner.original_budget,
474 inner.transfer_in,
475 inner.transfer_out,
476 inner.holdback_amt,
477 inner.currency_code_fc,
478 'N',
479 inner.transaction_create_date,
480 inner.business_unit_id,
481 inner.from_currency,
482 inner.conversion_rate,
483 inner.planned,
484 inner.committed,
485 inner.utilized,
486 inner.paid,
487 inner.metric_type,
488 inner.accrual,
489 inner.conversion_rate_s,
490 inner.original_budget_s,
491 inner.transfer_in_s,
492 inner.transfer_out_s,
493 inner.holdback_amt_s,
494 inner.planned_s,
495 inner.committed_s,
496 inner.utilized_s,
497 inner.accrual_s,
498 inner.paid_s
499 FROM (
500 SELECT fund_id fund_id,
501 fund_number fund_number,
502 start_date start_date,
503 end_date end_date,
504 start_period start_period,
505 end_period end_period,
506 category_id category_id,
507 status status,
508 fund_type fund_type,
509 parent_fund_id parent_fund_id,
510 country country,
511 org_id org_id,
512 business_unit_id business_unit_id,
513 set_of_books_id set_of_books_id,
514 currency_code_fc currency_code_fc,
515 original_budget original_budget,
516 transaction_create_date transaction_create_date,
517 SUM(transfer_in) transfer_in,
518 SUM(transfer_out) transfer_out,
519 SUM(holdback_amt) holdback_amt,
520 from_currency,
521 conversion_rate,
522 SUM(planned) planned,
523 SUM(committed) committed,
524 SUM(utilized) utilized,
525 SUM(paid) paid,
526 metric_type metric_type,
527 SUM(accrual) accrual,
528 conversion_rate_s,
529 SUM(original_budget_s) original_budget_s,
530 SUM(transfer_in_s) transfer_in_s,
531 SUM(transfer_out_s) transfer_out_s,
532 SUM(holdback_amt_s) holdback_amt_s,
533 SUM(planned_s) planned_s,
534 SUM(committed_s) committed_s,
535 SUM(utilized_s) utilized_s,
536 SUM(accrual_s) accrual_s,
537 SUM(paid_s) paid_s
538 FROM (
539 --original budget
540 SELECT ad.fund_id fund_id,
541 ad.fund_number fund_number,
542 ad.start_date_active start_date,
543 ad.end_date_active end_date,
547 ad.status_code status,
544 ad.start_period_name start_period,
545 ad.end_period_name end_period,
546 ad.category_id category_id,
548 ad.fund_type fund_type,
549 ad.parent_fund_id parent_fund_id,
550 ad.country_id country,
551 ad.org_id org_id,
552 ad.business_unit_id business_unit_id,
553 ad.set_of_books_id set_of_books_id,
554 ad.currency_code_fc currency_code_fc,
555 DECODE(ad.fund_type,'FIXED',ad.original_budget,'FULLY_ACCRUED',0) original_budget,
556 trunc(ad.start_date_active) transaction_create_date,
557 0 transfer_in,
558 0 transfer_out,
559 0 holdback_amt,
560 nvl(ad.currency_code_tc,'USD') from_currency,
561 0 conversion_rate,
562 0 planned,
563 0 committed,
564 0 utilized,
565 0 paid,
566 'ORIGINAL_BUDGET' metric_type,
567 0 accrual,
568 0 conversion_rate_s,
569 0 original_budget_s,
570 0 transfer_in_s,
571 0 transfer_out_s,
572 0 holdback_amt_s,
573 0 planned_s,
574 0 committed_s,
575 0 utilized_s,
576 0 accrual_s,
577 0 paid_s
578 FROM ozf_funds_all_b ad
579 WHERE nvl(ad.end_date_active,sysdate) >=p_start_date
580 AND ad.start_date_active <=p_end_date
581 AND ad.status_code in ('ACTIVE','CLOSED','CANCELLED')
582 AND ad.parent_fund_id is null
583 UNION ALL --transfer_in
584 SELECT ad.fund_id fund_id,
585 ad.fund_number fund_number,
586 ad.start_date_active start_date,
587 ad.end_date_active end_date,
588 ad.start_period_name start_period,
589 ad.end_period_name end_period,
590 ad.category_id category_id,
591 ad.status_code status,
592 ad.fund_type fund_type,
593 ad.parent_fund_id parent_fund_id,
594 ad.country_id country,
595 ad.org_id org_id,
596 ad.business_unit_id business_unit_id,
597 ad.set_of_books_id set_of_books_id,
598 ad.currency_code_fc currency_code_fc,
599 0 original_budget,
600 trunc(bu1.approval_date) transaction_create_date,
601 SUM(nvl(bu1.approved_amount,0)) transfer_in,
602 0 transfer_out,
603 0 holdback_amt,
604 nvl(bu1.request_currency,'USD') from_currency,
605 0 conversion_rate,
606 0 planned,
607 0 committed,
608 0 utilized,
609 0 paid,
610 'TRANSFER_IN' metric_type,
611 0 accrual,
612 0 conversion_rate_s,
613 0 original_budget_s,
614 0 transfer_in_s,
615 0 transfer_out_s,
616 0 holdback_amt_s,
617 0 planned_s,
618 0 committed_s,
619 0 utilized_s,
620 0 accrual_s,
621 0 paid_s
622 FROM ozf_funds_all_b ad,
623 ozf_act_budgets BU1
624 WHERE nvl(ad.end_date_active,sysdate) >p_start_date
625 AND bu1.approval_date <= p_end_date
626 AND bu1.transfer_type in ('TRANSFER','REQUEST')
627 AND bu1.status_code = 'APPROVED'
628 AND bu1.arc_act_budget_used_by = 'FUND'
629 AND bu1.act_budget_used_by_id = ad.fund_id
630 AND bu1.budget_source_type ='FUND'
631 GROUP BY ad.fund_id,
632 trunc(bu1.approval_date) ,
633 ad.fund_number,
634 ad.start_date_active ,
635 ad.end_date_active ,
636 ad.start_period_name ,
637 ad.end_period_name ,
638 ad.category_id ,
639 ad.status_code ,
640 ad.fund_type ,
641 ad.parent_fund_id,
642 ad.country_id,
643 ad.business_unit_id,
644 ad.org_id ,
645 ad.set_of_books_id ,
646 ad.currency_code_fc ,
647 ad.original_budget ,
648 nvl(bu1.request_currency,'USD')
649 UNION ALL --transfer_out
650 SELECT ad.fund_id fund_id,
651 ad.fund_number fund_number,
652 ad.start_date_active start_date,
653 ad.end_date_active end_date,
654 ad.start_period_name start_period,
655 ad.end_period_name end_period,
656 ad.category_id category_id,
657 ad.status_code status,
658 ad.fund_type fund_type,
659 ad.parent_fund_id parent_fund_id,
660 ad.country_id country,
661 ad.org_id org_id,
662 ad.business_unit_id business_unit_id,
663 ad.set_of_books_id set_of_books_id,
664 ad.currency_code_fc currency_code_fc,
665 0 original_budget,
666 trunc(bu2.approval_date) transaction_create_date,
667 0 transfer_in,
668 SUM(decode(bu2.transfer_type,'TRANSFER', nvl(bu2.approved_amount,0),0))+
669 SUM(decode(bu2.transfer_type,'REQUEST', nvl(bu2.approved_amount,0),0)) transfer_out,
670 SUM(decode(bu2.transfer_type, 'RESERVE', nvl(bu2.approved_amount,0),0))-
671 SUM(decode(bu2.transfer_type, 'RELEASE', nvl(bu2.approved_amount,0),0)) holdback_amt,
672 nvl(bu2.request_currency,'USD') from_currency,
673 0 conversion_rate,
674 0 planned,
675 0 committed,
679 0 accrual,
676 0 utilized,
677 0 paid,
678 'TRANSFER_OUT' metric_type,
680 0 conversion_rate_s,
681 0 original_budget_s,
682 0 transfer_in_s,
683 0 transfer_out_s,
684 0 holdback_amt_s,
685 0 planned_s,
686 0 committed_s,
687 0 utilized_s,
688 0 accrual_s,
689 0 paid_s
690 FROM ozf_funds_all_b ad,
691 ozf_act_budgets BU2
692 WHERE nvl(ad.end_date_active,sysdate) >p_start_date
693 AND bu2.approval_date<=p_end_date
694 AND bu2.status_code = 'APPROVED'
695 AND bu2.arc_act_budget_used_by = 'FUND'
696 AND bu2.budget_source_type='FUND'
697 AND bu2.budget_source_id = ad.fund_id
698 GROUP BY ad.fund_id,
699 trunc(bu2.approval_date) ,
700 ad.fund_number,
701 ad.start_date_active ,
702 ad.end_date_active ,
703 ad.start_period_name ,
704 ad.end_period_name ,
705 ad.category_id ,
706 ad.status_code ,
707 ad.fund_type ,
708 ad.parent_fund_id,
709 ad.country_id,
710 ad.org_id ,
711 ad.business_unit_id,
712 ad.set_of_books_id ,
713 ad.currency_code_fc ,
714 ad.original_budget,
715 nvl(bu2.request_currency,'USD')
716 UNION ALL--planned
717 SELECT ad.fund_id fund_id,
718 ad.fund_number fund_number,
719 ad.start_date_active start_date,
720 ad.end_date_active end_date,
721 ad.start_period_name start_period,
722 ad.end_period_name end_period,
723 ad.category_id category_id,
724 ad.status_code status,
725 ad.fund_type fund_type,
726 ad.parent_fund_id parent_fund_id,
727 ad.country_id country,
728 ad.org_id org_id,
729 ad.business_unit_id business_unit_id,
730 ad.set_of_books_id set_of_books_id,
731 ad.currency_code_fc currency_code_fc,
732 0 original_budget,
733 trunc(nvl(bu2.request_date,bu2.creation_date)) transaction_create_date,
734 0 transfer_in,
735 0 transfer_out,
736 0 holdback_amt,
737 nvl(bu2.request_currency,'USD') from_currency,
738 0 conversion_rate,
739 SUM(nvl(bu2.request_amount,0)) planned,
740 0 committed,
741 0 utilized,
742 0 paid,
743 'PLANNED' metric_type,
744 0 accrual,
745 0 conversion_rate_s,
746 0 original_budget_s,
747 0 transfer_in_s,
748 0 transfer_out_s,
749 0 holdback_amt_s,
750 0 planned_s,
751 0 committed_s,
752 0 utilized_s,
753 0 accrual_s,
754 0 paid_s
755 FROM ozf_funds_all_b ad,
756 ozf_act_budgets BU2
757 WHERE nvl(ad.end_date_active,sysdate) >p_start_date
758 AND bu2.budget_source_type ='FUND'
759 AND bu2.ARC_ACT_BUDGET_USED_BY <> 'FUND'
760 AND nvl(bu2.request_date,bu2.creation_date) <=p_end_date
761 AND bu2.budget_source_id = ad.fund_id
762 GROUP BY ad.fund_id,
763 trunc(nvl(bu2.request_date,bu2.creation_date)) ,
764 ad.fund_number,
765 ad.start_date_active ,
766 ad.end_date_active ,
767 ad.start_period_name ,
768 ad.end_period_name ,
769 ad.category_id ,
770 ad.status_code ,
771 ad.fund_type ,
772 ad.parent_fund_id,
773 ad.country_id,
774 ad.org_id ,
775 ad.business_unit_id,
776 ad.set_of_books_id ,
777 ad.currency_code_fc ,
778 ad.original_budget,
779 nvl(bu2.request_currency,'USD')
780 UNION ALL--PLANNED 2
781 SELECT ad.fund_id fund_id,
782 ad.fund_number fund_number,
783 ad.start_date_active start_date,
784 ad.end_date_active end_date,
785 ad.start_period_name start_period,
786 ad.end_period_name end_period,
787 ad.category_id category_id,
788 ad.status_code status,
789 ad.fund_type fund_type,
790 ad.parent_fund_id parent_fund_id,
791 ad.country_id country,
792 ad.org_id org_id,
793 ad.business_unit_id business_unit_id,
794 ad.set_of_books_id set_of_books_id,
795 ad.currency_code_fc currency_code_fc,
796 0 original_budget,
797 trunc(bu2.approval_date) transaction_create_date,
798 0 transfer_in,
799 0 transfer_out,
800 0 holdback_amt,
801 nvl(bu2.request_currency,'USD') from_currency,
802 0 conversion_rate,
803 0-SUM(nvl(bu2.approved_amount,0)) planned,
804 0 committed,
805 0 utilized,
806 0 paid,
807 'PLANNED' metric_type,
808 0 accrual,
809 0 conversion_rate_s,
810 0 original_budget_s,
811 0 transfer_in_s,
812 0 transfer_out_s,
813 0 holdback_amt_s,
814 0 planned_s,
818 0 paid_s
815 0 committed_s,
816 0 utilized_s,
817 0 accrual_s,
819 FROM ozf_funds_all_b ad,
820 ozf_act_budgets BU2
821 WHERE nvl(ad.end_date_active,sysdate) >p_start_date
822 AND bu2.arc_act_budget_used_by ='FUND'
823 AND bu2.budget_source_type<>'FUND'
824 AND bu2.status_code ='APPROVED'
825 AND bu2.approval_date <=p_end_date
826 AND bu2.act_budget_used_by_id = ad.fund_id
827 GROUP BY ad.fund_id,
828 trunc(bu2.approval_date) ,
829 ad.fund_number,
830 ad.start_date_active ,
831 ad.end_date_active ,
832 ad.start_period_name ,
833 ad.end_period_name ,
834 ad.category_id ,
835 ad.status_code ,
836 ad.fund_type ,
837 ad.parent_fund_id,
838 ad.country_id,
839 ad.org_id ,
840 ad.business_unit_id,
841 ad.set_of_books_id ,
842 ad.currency_code_fc ,
843 ad.original_budget,
844 nvl(bu2.request_currency,'USD')
845 UNION ALL--committed 1
846 SELECT ad.fund_id fund_id,
847 ad.fund_number fund_number,
848 ad.start_date_active start_date,
849 ad.end_date_active end_date,
850 ad.start_period_name start_period,
851 ad.end_period_name end_period,
852 ad.category_id category_id,
853 ad.status_code status,
854 ad.fund_type fund_type,
855 ad.parent_fund_id parent_fund_id,
856 ad.country_id country,
857 ad.org_id org_id,
858 ad.business_unit_id business_unit_id,
859 ad.set_of_books_id set_of_books_id,
860 ad.currency_code_fc currency_code_fc,
861 0 original_budget,
862 trunc(bu2.approval_date) transaction_create_date,
863 0 transfer_in,
864 0 transfer_out,
865 0 holdback_amt,
866 nvl(bu2.request_currency,'USD') from_currency,
867 0 conversion_rate,
868 0 planned,
869 SUM(nvl(bu2.approved_amount,0)) committed,
870 0 utilized,
871 0 paid,
872 'COMMITTED' metric_type,
873 0 accrual,
874 0 conversion_rate_s,
875 0 original_budget_s,
876 0 transfer_in_s,
877 0 transfer_out_s,
878 0 holdback_amt_s,
879 0 planned_s,
880 0 committed_s,
881 0 utilized_s,
882 0 accrual_s,
883 0 paid_s
884 FROM ozf_funds_all_b ad,
885 ozf_act_budgets BU2
886 WHERE nvl(ad.end_date_active,sysdate) >p_start_date
887 AND bu2.budget_source_type ='FUND'
888 AND bu2.ARC_ACT_BUDGET_USED_BY <> 'FUND'
889 AND bu2.approval_date <=p_end_date
890 AND bu2.budget_source_id = ad.fund_id
891 GROUP BY ad.fund_id,
892 trunc(bu2.approval_date) ,
893 ad.fund_number,
894 ad.start_date_active ,
895 ad.end_date_active ,
896 ad.start_period_name ,
897 ad.end_period_name ,
898 ad.category_id ,
899 ad.status_code ,
900 ad.fund_type ,
901 ad.parent_fund_id,
902 ad.country_id,
903 ad.org_id ,
904 ad.business_unit_id,
905 ad.set_of_books_id ,
906 ad.currency_code_fc ,
907 ad.original_budget,
908 nvl(bu2.request_currency,'USD')
909 UNION ALL--committed 2
910 SELECT ad.fund_id fund_id,
911 ad.fund_number fund_number,
912 ad.start_date_active start_date,
913 ad.end_date_active end_date,
914 ad.start_period_name start_period,
915 ad.end_period_name end_period,
916 ad.category_id category_id,
917 ad.status_code status,
918 ad.fund_type fund_type,
919 ad.parent_fund_id parent_fund_id,
920 ad.country_id country,
921 ad.org_id org_id,
922 ad.business_unit_id business_unit_id,
923 ad.set_of_books_id set_of_books_id,
924 ad.currency_code_fc currency_code_fc,
925 0 original_budget,
926 trunc(bu2.approval_date) transaction_create_date,
927 0 transfer_in,
928 0 transfer_out,
929 0 holdback_amt,
930 nvl(bu2.request_currency,'USD') from_currency,
931 0 conversion_rate,
932 0 planned,
933 0-SUM(nvl(bu2.approved_amount,0)) committed,
934 0 utilized,
935 0 paid,
936 'COMMITTED' metric_type,
937 0 accrual,
938 0 conversion_rate_s,
939 0 original_budget_s,
940 0 transfer_in_s,
941 0 transfer_out_s,
942 0 holdback_amt_s,
943 0 planned_s,
944 0 committed_s,
945 0 utilized_s,
946 0 accrual_s,
947 0 paid_s
948 FROM ozf_funds_all_b ad,
949 ozf_act_budgets BU2
950 WHERE nvl(ad.end_date_active,sysdate) >p_start_date
951 AND bu2.arc_act_budget_used_by ='FUND'
952 AND bu2.budget_source_type<>'FUND'
956 GROUP BY ad.fund_id,
953 AND bu2.status_code ='APPROVED'
954 AND bu2.approval_date <=p_end_date
955 AND bu2.act_budget_used_by_id = ad.fund_id
957 trunc(bu2.approval_date) ,
958 ad.fund_number,
959 ad.start_date_active ,
960 ad.end_date_active ,
961 ad.start_period_name ,
962 ad.end_period_name ,
963 ad.category_id ,
964 ad.status_code ,
965 ad.fund_type ,
966 ad.parent_fund_id,
967 ad.country_id,
968 ad.org_id ,
969 ad.business_unit_id,
970 ad.set_of_books_id ,
971 ad.currency_code_fc ,
972 ad.original_budget,
973 nvl(bu2.request_currency,'USD')
974 UNION ALL --utilized
975 SELECT ad.fund_id fund_id,
976 ad.fund_number fund_number,
977 ad.start_date_active start_date,
978 ad.end_date_active end_date,
979 ad.start_period_name start_period,
980 ad.end_period_name end_period,
981 ad.category_id category_id,
982 ad.status_code status,
983 ad.fund_type fund_type,
984 ad.parent_fund_id parent_fund_id,
985 ad.country_id country,
986 ad.org_id org_id,
987 ad.business_unit_id business_unit_id,
988 ad.set_of_books_id set_of_books_id,
989 ad.currency_code_fc currency_code_fc,
990 0 original_budget,
991 trunc(u2.creation_date) transaction_create_date,
992 0 transfer_in,
993 0 transfer_out,
994 0 holdback_amt,
995 nvl(u2.currency_code,'USD') from_currency,
996 0 conversion_rate,
997 0 planned,
998 0 committed,
999 SUM(nvl(u2.amount,0)) utilized,
1000 0 paid,
1001 'UTILIZED' metric_type,
1002 0 accrual,
1003 0 conversion_rate_s,
1004 0 original_budget_s,
1005 0 transfer_in_s,
1006 0 transfer_out_s,
1007 0 holdback_amt_s,
1008 0 planned_s,
1009 0 committed_s,
1010 0 utilized_s,
1011 0 accrual_s,
1012 0 paid_s
1013 FROM ozf_funds_all_b ad,
1014 ozf_funds_utilized_all_b u2
1015 WHERE nvl(ad.end_date_active,sysdate) >p_start_date
1016 AND ad.fund_id =u2.fund_id
1017 AND u2.creation_date <=p_end_date
1018 AND u2.utilization_type in ('UTILIZED','ACCRUAL','ADJUSTMENT')
1019 GROUP BY ad.fund_id,
1020 trunc(u2.creation_date),
1021 ad.fund_number,
1022 ad.start_date_active ,
1023 ad.end_date_active ,
1024 ad.start_period_name ,
1025 ad.end_period_name ,
1026 ad.category_id ,
1027 ad.status_code ,
1028 ad.fund_type ,
1029 ad.parent_fund_id,
1030 ad.country_id,
1031 ad.org_id ,
1032 ad.business_unit_id,
1033 ad.set_of_books_id ,
1034 ad.currency_code_fc ,
1035 ad.original_budget,
1036 nvl(u2.currency_code,'USD')
1037 union all --utilized 2
1038 SELECT ad.fund_id fund_id,
1039 ad.fund_number fund_number,
1040 ad.start_date_active start_date,
1041 ad.end_date_active end_date,
1042 ad.start_period_name start_period,
1043 ad.end_period_name end_period,
1044 ad.category_id category_id,
1045 ad.status_code status,
1046 ad.fund_type fund_type,
1047 ad.parent_fund_id parent_fund_id,
1048 ad.country_id country,
1049 ad.org_id org_id,
1050 ad.business_unit_id business_unit_id,
1051 ad.set_of_books_id set_of_books_id,
1052 ad.currency_code_fc currency_code_fc,
1053 0 original_budget,
1054 trunc(u2.creation_date) transaction_create_date,
1055 0 transfer_in,
1056 0 transfer_out,
1057 0 holdback_amt,
1058 nvl(u2.currency_code,'USD') from_currency,
1059 0 conversion_rate,
1060 0 planned,
1061 0 committed,
1062 0-SUM(nvl(u2.amount,0)) utilized,
1063 0 paid,
1064 'UTILIZED' metric_type,
1065 0 accrual,
1066 0 conversion_rate_s,
1067 0 original_budget_s,
1068 0 transfer_in_s,
1069 0 transfer_out_s,
1070 0 holdback_amt_s,
1071 0 planned_s,
1072 0 committed_s,
1073 0 utilized_s,
1074 0 accrual_s,
1075 0 paid_s
1076 FROM ozf_funds_all_b ad,
1077 ozf_funds_utilized_all_b u2
1078 WHERE nvl(ad.end_date_active,sysdate) >p_start_date
1079 AND ad.fund_id =u2.fund_id
1080 AND ad.fund_type='FULLY_ACCRUED'
1081 AND ad.liability_flag='N'
1082 AND ad.accrual_basis='CUSTOMER'
1083 AND u2.creation_date <=p_end_date
1084 AND ad.plan_id=u2.component_id
1085 AND u2.component_type='OFFR'
1086 AND u2.utilization_type ='ACCRUAL'
1087 GROUP BY ad.fund_id,
1088 trunc(u2.creation_date),
1089 ad.fund_number,
1090 ad.start_date_active ,
1091 ad.end_date_active ,
1092 ad.start_period_name ,
1096 ad.fund_type ,
1093 ad.end_period_name ,
1094 ad.category_id ,
1095 ad.status_code ,
1097 ad.parent_fund_id,
1098 ad.country_id,
1099 ad.org_id ,
1100 ad.business_unit_id,
1101 ad.set_of_books_id ,
1102 ad.currency_code_fc ,
1103 ad.original_budget,
1104 nvl(u2.currency_code,'USD')
1105 UNION ALL --accrual
1106 SELECT ad.fund_id fund_id,
1107 ad.fund_number fund_number,
1108 ad.start_date_active start_date,
1109 ad.end_date_active end_date,
1110 ad.start_period_name start_period,
1111 ad.end_period_name end_period,
1112 ad.category_id category_id,
1113 ad.status_code status,
1114 ad.fund_type fund_type,
1115 ad.parent_fund_id parent_fund_id,
1116 ad.country_id country,
1117 ad.org_id org_id,
1118 ad.business_unit_id business_unit_id,
1119 ad.set_of_books_id set_of_books_id,
1120 ad.currency_code_fc currency_code_fc,
1121 0 original_budget,
1122 trunc(u2.creation_date) transaction_create_date,
1123 0 transfer_in,
1124 0 transfer_out,
1125 0 holdback_amt,
1126 nvl(u2.currency_code,'USD') from_currency,
1127 0 conversion_rate,
1128 0 planned,
1129 0 committed,
1130 0 utilized,
1131 0 paid,
1132 'ACCRUAL' metric_type,
1133 SUM(nvl(u2.amount,0)) accrual,
1134 0 conversion_rate_s,
1135 0 original_budget_s,
1136 0 transfer_in_s,
1137 0 transfer_out_s,
1138 0 holdback_amt_s,
1139 0 planned_s,
1140 0 committed_s,
1141 0 utilized_s,
1142 0 accrual_s,
1143 0 paid_s
1144 FROM ozf_funds_all_b ad,
1145 ozf_funds_utilized_all_b u2
1146 WHERE nvl(ad.end_date_active,sysdate) >p_start_date
1147 AND ad.fund_id =u2.fund_id
1148 AND ad.fund_type='FULLY_ACCRUED'
1149 AND ad.liability_flag='N'
1150 AND ad.accrual_basis='CUSTOMER'
1151 AND u2.creation_date <=p_end_date
1152 AND ad.plan_id=u2.component_id
1153 AND u2.component_type='OFFR'
1154 AND u2.utilization_type ='ACCRUAL'
1155 GROUP BY ad.fund_id,
1156 trunc(u2.creation_date),
1157 ad.fund_number,
1158 ad.start_date_active ,
1159 ad.end_date_active ,
1160 ad.start_period_name ,
1161 ad.end_period_name ,
1162 ad.category_id ,
1163 ad.status_code ,
1164 ad.fund_type ,
1165 ad.parent_fund_id,
1166 ad.country_id,
1167 ad.org_id ,
1168 ad.business_unit_id,
1169 ad.set_of_books_id ,
1170 ad.currency_code_fc ,
1171 ad.original_budget,
1172 nvl(u2.currency_code,'USD')
1173 union all --accrual 2
1174 SELECT ad.fund_id fund_id,
1175 ad.fund_number fund_number,
1176 ad.start_date_active start_date,
1177 ad.end_date_active end_date,
1178 ad.start_period_name start_period,
1179 ad.end_period_name end_period,
1180 ad.category_id category_id,
1181 ad.status_code status,
1182 ad.fund_type fund_type,
1183 ad.parent_fund_id parent_fund_id,
1184 ad.country_id country,
1185 ad.org_id org_id,
1186 ad.business_unit_id business_unit_id,
1187 ad.set_of_books_id set_of_books_id,
1188 ad.currency_code_fc currency_code_fc,
1189 0 original_budget,
1190 trunc(u2.creation_date) transaction_create_date,
1191 0 transfer_in,
1192 0 transfer_out,
1193 0 holdback_amt,
1194 nvl(u2.currency_code,'USD') from_currency,
1195 0 conversion_rate,
1196 0 planned,
1197 0 committed,
1198 0 utilized,
1199 0 paid,
1200 'ACCRUAL' metric_type,
1201 SUM(nvl(u2.amount,0)) accrual,
1202 0 conversion_rate_s,
1203 0 original_budget_s,
1204 0 transfer_in_s,
1205 0 transfer_out_s,
1206 0 holdback_amt_s,
1207 0 planned_s,
1208 0 committed_s,
1209 0 utilized_s,
1210 0 accrual_s,
1211 0 paid_s
1212 FROM ozf_funds_all_b ad,
1213 ozf_funds_utilized_all_b u2
1214 WHERE nvl(ad.end_date_active,sysdate) >p_start_date
1215 AND ad.fund_id =u2.fund_id
1216 AND ad.fund_type='FULLY_ACCRUED'
1217 AND ad.accrual_basis='SALES'
1218 AND u2.creation_date <=p_end_date
1219 AND ad.plan_id=u2.component_id
1220 AND u2.component_type='OFFR'
1221 AND u2.utilization_type ='SALES_ACCRUAL'
1222 GROUP BY ad.fund_id,
1223 trunc(u2.creation_date),
1224 ad.fund_number,
1225 ad.start_date_active ,
1226 ad.end_date_active ,
1227 ad.start_period_name ,
1228 ad.end_period_name ,
1229 ad.category_id ,
1230 ad.status_code ,
1231 ad.fund_type ,
1232 ad.parent_fund_id,
1233 ad.country_id,
1234 ad.org_id ,
1235 ad.business_unit_id,
1239 nvl(u2.currency_code,'USD')
1236 ad.set_of_books_id ,
1237 ad.currency_code_fc ,
1238 ad.original_budget,
1240 UNION ALL--paid 1
1241 SELECT ad.fund_id fund_id,
1242 ad.fund_number fund_number,
1243 ad.start_date_active start_date,
1244 ad.end_date_active end_date,
1245 ad.start_period_name start_period,
1246 ad.end_period_name end_period,
1247 ad.category_id category_id,
1248 ad.status_code status,
1249 ad.fund_type fund_type,
1250 ad.parent_fund_id parent_fund_id,
1251 ad.country_id country,
1252 ad.org_id org_id,
1253 ad.business_unit_id business_unit_id,
1254 ad.set_of_books_id set_of_books_id,
1255 ad.currency_code_fc currency_code_fc,
1256 0 original_budget,
1257 trunc(u2.creation_date) transaction_create_date,
1258 0 transfer_in,
1259 0 transfer_out,
1260 0 holdback_amt,
1261 nvl(u2.currency_code,'USD') from_currency,
1262 0 conversion_rate,
1263 0 planned,
1264 0 committed,
1265 0 utilized,
1266 SUM(nvl(u2.amount,0)) paid,
1267 'PAID' metric_type,
1268 0 accrual,
1269 0 conversion_rate_s,
1270 0 original_budget_s,
1271 0 transfer_in_s,
1272 0 transfer_out_s,
1273 0 holdback_amt_s,
1274 0 planned_s,
1275 0 committed_s,
1276 0 utilized_s,
1277 0 accrual_s,
1278 0 paid_s
1279 FROM ozf_funds_all_b ad,
1280 ozf_funds_utilized_all_b u2
1281 WHERE nvl(ad.end_date_active,sysdate) >p_start_date
1282 AND ad.fund_id =u2.fund_id
1283 AND u2.creation_date <=p_end_date
1284 AND u2.utilization_type ='UTILIZED'
1285 GROUP BY ad.fund_id,
1286 trunc(u2.creation_date) ,
1287 ad.fund_number,
1288 ad.start_date_active ,
1289 ad.end_date_active ,
1290 ad.start_period_name ,
1291 ad.end_period_name ,
1292 ad.category_id ,
1293 ad.status_code ,
1294 ad.fund_type ,
1295 ad.parent_fund_id,
1296 ad.country_id,
1297 ad.org_id ,
1298 ad.business_unit_id,
1299 ad.set_of_books_id ,
1300 ad.currency_code_fc ,
1301 ad.original_budget,
1302 nvl(u2.currency_code,'USD')
1303 UNION ALL--paid 2, based on 11.5.9
1304 SELECT ad.fund_id fund_id,
1305 ad.fund_number fund_number,
1306 ad.start_date_active start_date,
1307 ad.end_date_active end_date,
1308 ad.start_period_name start_period,
1309 ad.end_period_name end_period,
1310 ad.category_id category_id,
1311 ad.status_code status,
1312 ad.fund_type fund_type,
1313 ad.parent_fund_id parent_fund_id,
1314 ad.country_id country,
1315 ad.org_id org_id,
1316 ad.business_unit_id business_unit_id,
1317 ad.set_of_books_id set_of_books_id,
1318 ad.currency_code_fc currency_code_fc,
1319 0 original_budget,
1320 trunc(cla.claim_date) transaction_create_date,
1321 0 transfer_in,
1322 0 transfer_out,
1323 0 holdback_amt,
1324 nvl(cuti.currency_code,'USD') from_currency,
1325 0 conversion_rate,
1326 0 planned,
1327 0 committed,
1328 0 utilized,
1329 SUM(nvl(cuti.amount,0)) paid,
1330 'PAID' metric_type,
1331 0 accrual,
1332 0 conversion_rate_s,
1333 0 original_budget_s,
1334 0 transfer_in_s,
1335 0 transfer_out_s,
1336 0 holdback_amt_s,
1337 0 planned_s,
1338 0 committed_s,
1339 0 utilized_s,
1340 0 accrual_s,
1341 0 paid_s
1342 FROM ozf_funds_all_b ad,
1343 ozf_funds_utilized_all_b u2,
1344 ozf_claim_lines_util_all cuti,
1345 ozf_claim_lines_all cln,
1346 ozf_claims_all cla
1347 WHERE nvl(ad.end_date_active,sysdate) >p_start_date
1348 AND ad.fund_id =u2.fund_id
1349 AND cla.claim_date <=p_end_date
1350 AND u2.utilization_id= cuti.utilization_id
1351 AND u2.utilization_type IN ('ACCRUAL','SALES_ACCRUAL','ADJUSTMENT')
1352 AND cuti.claim_line_id= cln.claim_line_id
1353 AND cln.claim_id = cla.claim_id
1354 AND cla.status_code = 'CLOSED'
1355 GROUP BY ad.fund_id,
1356 trunc(cla.claim_date) ,
1357 ad.fund_number,
1358 ad.start_date_active ,
1359 ad.end_date_active ,
1360 ad.start_period_name ,
1361 ad.end_period_name ,
1362 ad.category_id ,
1363 ad.status_code ,
1364 ad.fund_type ,
1365 ad.parent_fund_id,
1366 ad.country_id,
1367 ad.org_id ,
1368 ad.business_unit_id,
1369 ad.set_of_books_id ,
1370 ad.currency_code_fc ,
1371 ad.original_budget,
1372 nvl(cuti.currency_code,'USD')
1373 )
1374 GROUP BY
1375 fund_id,
1376 transaction_create_date,
1377 fund_number,
1378 start_date,
1379 end_date,
1383 status,
1380 start_period,
1381 end_period,
1382 category_id,
1384 fund_type,
1385 parent_fund_id,
1386 country,
1387 org_id,
1388 business_unit_id,
1389 set_of_books_id,
1390 currency_code_fc,
1391 original_budget,
1392 from_currency,
1393 conversion_rate,
1394 metric_type,
1395 conversion_rate_s
1396 )inner;
1397 commit;
1398 BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Inserting into BIM_I_BGT_RATES');
1399 --insert into bim_i_mkt_rates
1400 INSERT /*+ append parallel */
1401 INTO BIM_I_BGT_RATES BRT(tc_code,
1402 trx_date,
1403 prim_conversion_rate,
1404 sec_conversion_rate)
1405 SELECT from_currency,
1406 transaction_create_date,
1407 FII_CURRENCY.get_rate(from_currency,l_global_currency_code,transaction_create_date,l_pgc_rate_type),
1408 FII_CURRENCY.get_rate(from_currency,l_secondary_currency_code,transaction_create_date,l_sgc_rate_type)
1409 FROM (select distinct from_currency from_currency,
1410 transaction_create_date transaction_create_date
1411 from bim_i_budget_facts_stg);
1412 commit;
1413 l_check_missing_rate := Check_Missing_Rates (p_start_date);
1414 if (l_check_missing_rate = -1) then
1415 BIS_COLLECTION_UTILITIES.debug('before truncating first time load' );
1416 l_stmt := 'TRUNCATE table '||l_schema||'.BIM_I_BUDGET_FACTS_stg';
1417 EXECUTE IMMEDIATE l_stmt;
1418 commit;
1419 x_return_status := FND_API.G_RET_STS_ERROR;
1420 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1421 end if;
1422
1423 --BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Inserted '||SQL%COUNT);
1424 EXECUTE IMMEDIATE 'COMMIT';
1425 -- EXECUTE IMMEDIATE 'ALTER SEQUENCE BIM_I_BUDGET_FACTS_s CACHE 20';
1426
1427 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.BIM_I_BUDGET_FACTS';
1428 BIS_COLLECTION_UTILITIES.deleteLogForObject('BUDGET_FACTS');
1429
1430 BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Insert into BIM_I_BUDGET_FACTS');
1431 INSERT /*+ append parallel */
1432 INTO BIM_I_BUDGET_FACTS CDF(
1433 creation_date
1434 ,last_update_date
1435 ,created_by
1436 ,last_updated_by
1437 ,last_update_login
1438 ,fund_id
1439 ,parent_fund_id
1440 ,fund_number
1441 ,start_date
1442 ,end_date
1443 ,start_period
1444 ,end_period
1445 ,set_of_books_id
1446 ,fund_type
1447 --,region
1448 ,country
1449 ,org_id
1450 ,category_id
1451 ,status
1452 ,original_budget
1453 ,transfer_in
1454 ,transfer_out
1455 ,holdback_amt
1456 ,currency_code_fc
1457 ,delete_flag
1458 ,transaction_create_date
1459 ,business_unit_id
1460 ,from_currency
1461 ,conversion_rate
1462 ,planned
1463 ,committed
1464 ,utilized
1465 ,paid
1466 ,metric_type
1467 ,accrual
1468 ,conversion_rate_s
1469 ,original_budget_s
1470 ,transfer_in_s
1471 ,transfer_out_s
1472 ,holdback_amt_s
1473 ,planned_s
1474 ,committed_s
1475 ,utilized_s
1476 ,accrual_s
1477 ,paid_s)
1478 SELECT /*+ parallel */
1479 sysdate,
1480 sysdate,
1481 l_user_id,
1482 l_user_id,
1483 l_user_id,
1484 inner.fund_id,
1485 inner.parent_fund_id,
1486 inner.fund_number,
1487 inner.start_date,
1488 inner.end_date,
1489 inner.start_period,
1490 inner.end_period,
1491 inner.set_of_books_id,
1492 inner.fund_type,
1493 --inner.region,
1494 inner.country,
1495 inner.org_id,
1496 inner.category_id,
1497 inner.status,
1498 inner.original_budget*prim_conversion_rate,
1499 inner.transfer_in*prim_conversion_rate,
1500 inner.transfer_out*prim_conversion_rate,
1501 inner.holdback_amt*prim_conversion_rate,
1502 inner.currency_code_fc,
1503 'N',
1504 inner.transaction_create_date,
1505 inner.business_unit_id,
1506 inner.from_currency,
1507 inner.conversion_rate,
1508 inner.planned*prim_conversion_rate,
1509 inner.committed*prim_conversion_rate,
1510 inner.utilized*prim_conversion_rate,
1511 inner.paid*prim_conversion_rate,
1512 inner.metric_type,
1513 inner.accrual*prim_conversion_rate,
1514 inner.conversion_rate_s,
1515 inner.original_budget*sec_conversion_rate,
1516 inner.transfer_in*sec_conversion_rate,
1517 inner.transfer_out*sec_conversion_rate,
1518 inner.holdback_amt*sec_conversion_rate,
1519 inner.planned*sec_conversion_rate,
1520 inner.committed*sec_conversion_rate,
1521 inner.utilized*sec_conversion_rate,
1522 inner.accrual*sec_conversion_rate,
1523 inner.paid*sec_conversion_rate
1524 FROM bim_i_budget_facts_stg inner, bim_i_bgt_rates rt
1525 where inner.from_currency = rt.tc_code
1526 and inner.transaction_create_date= rt.trx_date;
1527 commit;
1531 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
1528 --dbms_output.put_line('b4 put into history');
1529 -- Analyze the daily facts table
1530 DBMS_STATS.gather_table_stats('BIM','BIM_I_BUDGET_FACTS', estimate_percent => 5,
1532
1533 -- Make entry in the history table
1534 BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: Wrapup');
1535 BEGIN
1536 IF (Not BIS_COLLECTION_UTILITIES.setup('BUDGET_FACTS')) THEN
1537 RAISE FND_API.G_EXC_ERROR;
1538 return;
1539 END IF;
1540
1541 BIS_COLLECTION_UTILITIES.WRAPUP(
1542 p_status =>TRUE ,
1543 p_period_from =>p_start_date,
1544 p_period_to => sysdate
1545 );
1546 Exception when others then
1547 Rollback;
1548 BIS_COLLECTION_UTILITIES.WRAPUP(
1549 p_status => FALSE,
1550 p_period_from =>p_start_date,
1551 p_period_to =>sysdate
1552 );
1553 RAISE FND_API.G_EXC_ERROR;
1554 END;
1555
1556 BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Before create index');
1557
1558 BIM_UTL_PKG.CREATE_INDEX('BIM_I_BUDGET_FACTS');
1559 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
1560 --ams_utility_pvt.write_conc_log('BIM_I_BUDGET_FACTS:FIRST_LOAD: AFTER CREATE INDEX ' || l_temp_msg);
1561 BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:After create index');
1562 /*fnd_message.set_name('BIM','BIM_R_PROG_COMPLETION');
1563 fnd_message.set_token('program_name', 'Budget first load', FALSE);
1564 fnd_file.put_line(fnd_file.log,fnd_message.get);*/
1565 EXCEPTION
1566 WHEN FND_API.G_EXC_ERROR THEN
1567 x_return_status := FND_API.G_RET_STS_ERROR;
1568 -- Standard call to get message count and if count=1, get the message
1569 FND_msg_PUB.Count_And_Get (
1570 -- p_encoded => FND_API.G_FALSE,
1571 p_count => x_msg_count,
1572 p_data => x_msg_data
1573 );
1574
1575 ams_utility_pvt.write_conc_log('BIM_I_BUDGET_FACTS:FIRST_LOAD:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
1576 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1578 -- Standard call to get message count and if count=1, get the message
1579 FND_msg_PUB.Count_And_Get (
1580 --p_encoded => FND_API.G_FALSE,
1581 p_count => x_msg_count,
1582 p_data => x_msg_data
1583 );
1584 BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Unexpected'||sqlerrm(sqlcode));
1585
1586 WHEN OTHERS THEN
1587 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1588 IF FND_msg_PUB.Check_msg_Level (FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
1589 THEN
1590 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
1591 END IF;
1592
1593 -- Standard call to get message count and if count=1, get the message
1594 FND_msg_PUB.Count_And_Get (
1595 -- p_encoded => FND_API.G_FALSE,
1596 p_count => x_msg_count,
1597 p_data => x_msg_data
1598 );
1599 BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:IN OTHERS EXCEPTION'||sqlerrm(sqlcode));
1600 --end;
1601 END FIRST_LOAD;
1602 --------------------------------------------------------------------------------------------------
1603 -- This procedure will execute when data is loaded for subsequent time.
1604
1605 -- PROCEDURE SUB_LOAD
1606 --------------------------------------------------------------------------------------------------
1607 PROCEDURE SUB_LOAD
1608 ( p_start_date IN DATE
1609 ,p_end_date IN DATE
1610 ,p_api_version_number IN NUMBER
1611 ,p_init_msg_list IN VARCHAR2
1612 ,p_load_type IN VARCHAR2
1613 ,x_msg_count OUT NOCOPY NUMBER
1614 ,x_msg_data OUT NOCOPY VARCHAR2
1615 ,x_return_status OUT NOCOPY VARCHAR2
1616 )
1617 IS
1618 l_user_id NUMBER := FND_GLOBAL.USER_ID();
1619 l_start_date DATE;
1620 l_end_date DATE;
1621 l_last_update_date DATE;
1622 l_conc_start_date DATE;
1623 l_conc_end_date DATE;
1624 l_sc_s_date DATE;
1625 l_sc_e_date DATE;
1626 l_success VARCHAR2(3);
1627 l_api_version_number CONSTANT NUMBER := 1.0;
1628 l_api_name CONSTANT VARCHAR2(30) := 'SUB_LOAD';
1629 l_table_name VARCHAR2(100);
1630 l_temp_msg VARCHAR2(100);
1631 l_check_missing_rate NUMBER;
1632 l_min_start_date DATE;
1633 l_min_date date;
1634 l_stmt VARCHAR2(100);
1635
1636 l_status VARCHAR2(5);
1637 l_industry VARCHAR2(5);
1638 l_schema VARCHAR2(30);
1639 l_return BOOLEAN;
1640
1641 BEGIN
1642 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
1643 --dbms_output.put_line('inside sub load:'||p_start_date || ' '|| p_end_date);
1644
1645 -- Standard call to check for call compatibility.
1646 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1647 p_api_version_number,
1648 l_api_name,
1649 g_pkg_name)
1650 THEN
1651 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1652 END IF;
1653
1654 -- Initialize message list if p_init_msg_list is set to TRUE.
1655 IF FND_API.to_Boolean( p_init_msg_list )
1656 THEN
1657 FND_msg_PUB.initialize;
1658 END IF;
1659
1660 -- Initialize API return status to SUCCESS
1661 x_return_status := FND_API.G_RET_STS_SUCCESS;
1662
1663 --dbms_output.put_line('inside sub load 2:');
1664
1665 -- The below four commands are necessary for the purpose of the parallel insertion */
1666 BEGIN
1667 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL dml ';
1668 --EXECUTE IMMEDIATE 'ALTER SESSION SET SORT_AREA_SIZE=100000000 ';
1669 --EXECUTE IMMEDIATE 'ALTER SESSION SET HASH_AREA_SIZE=100000000 ';
1670 --EXECUTE IMMEDIATE 'ALTER TABLE BIM_I_BUDGET_FACTS nologging ';
1671 -- EXECUTE IMMEDIATE 'ALTER SEQUENCE BIM_I_BUDGET_FACTS_s CACHE 1000 ';
1672 EXCEPTION
1673 when others then
1674 --dbms_output.put_line('inside sub load:'||sqlerrm(sqlcode));
1675 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
1676 END;
1677 BEGIN
1678 DELETE from bim_i_budget_facts where transaction_create_date>= p_start_date and metric_type is not null;
1679 COMMIT;
1680 EXCEPTION
1681 when others then
1682 BIS_COLLECTION_UTILITIES.log('BIM_I_budget_facts: Error in deleting data:'|| sqlerrm(sqlcode));
1683 --dbms_output.put_line('error inserting:'||sqlerrm(sqlcode));
1684 END;
1685 EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_BUDGET_FACTS_STG';
1686 EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_BGT_RATES';
1687 --dbms_output.put_line('right b4 inserting');
1688 BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Incremental load start');
1689 INSERT INTO BIM_I_BUDGET_FACTS_STG CDF(
1690 creation_date
1691 ,last_update_date
1692 ,created_by
1693 ,last_updated_by
1694 ,last_update_login
1695 ,fund_id
1696 ,parent_fund_id
1697 ,fund_number
1698 ,start_date
1699 ,end_date
1700 ,start_period
1701 ,end_period
1702 ,set_of_books_id
1703 ,fund_type
1704 --,region
1705 ,country
1706 ,org_id
1707 ,category_id
1708 ,status
1709 ,original_budget
1710 ,transfer_in
1711 ,transfer_out
1712 ,holdback_amt
1713 ,currency_code_fc
1714 ,delete_flag
1715 ,transaction_create_date
1716 ,business_unit_id
1717 ,from_currency
1718 ,conversion_rate
1719 ,planned
1720 ,committed
1721 ,utilized
1722 ,paid
1723 ,metric_type
1724 ,accrual
1728 ,transfer_out_s
1725 ,conversion_rate_s
1726 ,original_budget_s
1727 ,transfer_in_s
1729 ,holdback_amt_s
1730 ,planned_s
1731 ,committed_s
1732 ,utilized_s
1733 ,accrual_s
1734 ,paid_s)
1735 SELECT
1736 sysdate,
1737 sysdate,
1738 l_user_id,
1739 l_user_id,
1740 l_user_id,
1741 inner.fund_id,
1742 inner.parent_fund_id,
1743 inner.fund_number,
1744 inner.start_date,
1745 inner.end_date,
1746 inner.start_period,
1747 inner.end_period,
1748 inner.set_of_books_id,
1749 inner.fund_type,
1750 --inner.region,
1751 inner.country,
1752 inner.org_id,
1753 inner.category_id,
1754 inner.status,
1755 inner.original_budget,
1756 inner.transfer_in,
1757 inner.transfer_out,
1758 inner.holdback_amt,
1759 inner.currency_code_fc,
1760 'N',
1761 inner.transaction_create_date,
1762 inner.business_unit_id,
1763 inner.from_currency,
1764 inner.conversion_rate,
1765 inner.planned,
1766 inner.committed,
1767 inner.utilized,
1768 inner.paid,
1769 inner.metric_type,
1770 inner.accrual,
1771 inner.conversion_rate_s,
1772 inner.original_budget_s,
1773 inner.transfer_in_s,
1774 inner.transfer_out_s,
1775 inner.holdback_amt_s,
1776 inner.planned_s,
1777 inner.committed_s,
1778 inner.utilized_s,
1779 inner.accrual_s,
1780 inner.paid_s
1781 FROM (
1782 SELECT fund_id fund_id,
1783 fund_number fund_number,
1784 start_date start_date,
1785 end_date end_date,
1786 start_period start_period,
1787 end_period end_period,
1788 category_id category_id,
1789 status status,
1790 fund_type fund_type,
1791 parent_fund_id parent_fund_id,
1792 country country,
1793 org_id org_id,
1794 business_unit_id business_unit_id,
1795 set_of_books_id set_of_books_id,
1796 currency_code_fc currency_code_fc,
1797 original_budget original_budget,
1798 transaction_create_date transaction_create_date,
1799 SUM(transfer_in) transfer_in,
1800 SUM(transfer_out) transfer_out,
1801 SUM(holdback_amt) holdback_amt,
1802 from_currency,
1803 conversion_rate,
1804 SUM(planned) planned,
1805 SUM(committed) committed,
1806 SUM(utilized) utilized,
1807 SUM(paid) paid,
1808 metric_type metric_type,
1809 SUM(accrual) accrual,
1810 conversion_rate_s,
1811 SUM(original_budget_s) original_budget_s,
1812 SUM(transfer_in_s) transfer_in_s,
1813 SUM(transfer_out_s) transfer_out_s,
1814 SUM(holdback_amt_s) holdback_amt_s,
1815 SUM(planned_s) planned_s,
1816 SUM(committed_s) committed_s,
1817 SUM(utilized_s) utilized_s,
1818 SUM(accrual_s) accrual_s,
1819 SUM(paid_s) paid_s
1820 FROM (
1821 SELECT ad.fund_id fund_id,
1822 ad.fund_number fund_number,
1823 ad.start_date_active start_date,
1824 ad.end_date_active end_date,
1825 ad.start_period_name start_period,
1826 ad.end_period_name end_period,
1827 ad.category_id category_id,
1828 ad.status_code status,
1829 ad.fund_type fund_type,
1830 ad.parent_fund_id parent_fund_id,
1831 ad.country_id country,
1832 ad.org_id org_id,
1833 ad.business_unit_id business_unit_id,
1834 ad.set_of_books_id set_of_books_id,
1835 ad.currency_code_fc currency_code_fc,
1836 DECODE(ad.fund_type,'FIXED',ad.original_budget,'FULLY_ACCRUED',0) original_budget,
1837 trunc(ad.start_date_active) transaction_create_date,
1838 0 transfer_in,
1839 0 transfer_out,
1840 0 holdback_amt,
1841 nvl(ad.currency_code_tc,'USD') from_currency,
1842 0 conversion_rate,
1843 0 planned,
1844 0 committed,
1845 0 utilized,
1846 0 paid,
1847 'ORIGINAL_BUDGET' metric_type,
1848 0 accrual,
1849 0 conversion_rate_s,
1850 0 original_budget_s,
1851 0 transfer_in_s,
1852 0 transfer_out_s,
1853 0 holdback_amt_s,
1854 0 planned_s,
1855 0 committed_s,
1856 0 utilized_s,
1857 0 accrual_s,
1858 0 paid_s
1859 FROM ozf_funds_all_b ad
1860 WHERE ( ( ad.status_date between p_start_date and p_end_date
1861 AND ad.start_date_active <=p_end_date
1862 )
1863 or ( ad.start_date_active between p_start_date and p_end_date
1864 AND ad.status_date<p_start_date))
1865 AND ad.parent_fund_id is null
1866 AND ad.status_code in ('ACTIVE','CLOSED','CANCELLED')
1867 AND not exists (select 1 from bim_i_budget_facts a
1868 where a.fund_id = ad.fund_id
1869 and a.metric_type= 'ORIGINAL_BUDGET')
1873 ad.start_date_active start_date,
1870 UNION ALL --transfer_in
1871 SELECT ad.fund_id fund_id,
1872 ad.fund_number fund_number,
1874 ad.end_date_active end_date,
1875 ad.start_period_name start_period,
1876 ad.end_period_name end_period,
1877 ad.category_id category_id,
1878 ad.status_code status,
1879 ad.fund_type fund_type,
1880 ad.parent_fund_id parent_fund_id,
1881 ad.country_id country,
1882 ad.org_id org_id,
1883 ad.business_unit_id business_unit_id,
1884 ad.set_of_books_id set_of_books_id,
1885 ad.currency_code_fc currency_code_fc,
1886 0 original_budget,
1887 trunc(bu1.approval_date) transaction_create_date,
1888 SUM(nvl(bu1.approved_amount,0)) transfer_in,
1889 0 transfer_out,
1890 0 holdback_amt,
1891 nvl(bu1.request_currency,'USD') from_currency,
1892 0 conversion_rate,
1893 0 planned,
1894 0 committed,
1895 0 utilized,
1896 0 paid,
1897 'TRANSFER_IN' metric_type,
1898 0 accrual,
1899 0 conversion_rate_s,
1900 0 original_budget_s,
1901 0 transfer_in_s,
1902 0 transfer_out_s,
1903 0 holdback_amt_s,
1904 0 planned_s,
1905 0 committed_s,
1906 0 utilized_s,
1907 0 accrual_s,
1908 0 paid_s
1909 FROM ozf_funds_all_b ad,
1910 ozf_act_budgets BU1
1911 WHERE bu1.approval_date between p_start_date and p_end_date
1912 AND bu1.transfer_type in ('TRANSFER','REQUEST')
1913 AND bu1.status_code = 'APPROVED'
1914 AND bu1.arc_act_budget_used_by = 'FUND'
1915 AND bu1.act_budget_used_by_id = ad.fund_id
1916 AND bu1.budget_source_type ='FUND'
1917 GROUP BY ad.fund_id,
1918 trunc(bu1.approval_date),
1919 ad.fund_number,
1920 ad.start_date_active ,
1921 ad.end_date_active ,
1922 ad.start_period_name ,
1923 ad.end_period_name ,
1924 ad.category_id ,
1925 ad.status_code ,
1926 ad.fund_type ,
1927 ad.parent_fund_id,
1928 ad.country_id,
1929 ad.business_unit_id,
1930 ad.org_id ,
1931 ad.set_of_books_id ,
1932 ad.currency_code_fc ,
1933 ad.original_budget ,
1934 nvl(bu1.request_currency,'USD')
1935 UNION ALL --transfer_out
1936 SELECT ad.fund_id fund_id,
1937 ad.fund_number fund_number,
1938 ad.start_date_active start_date,
1939 ad.end_date_active end_date,
1940 ad.start_period_name start_period,
1941 ad.end_period_name end_period,
1942 ad.category_id category_id,
1943 ad.status_code status,
1944 ad.fund_type fund_type,
1945 ad.parent_fund_id parent_fund_id,
1946 ad.country_id country,
1947 ad.org_id org_id,
1948 ad.business_unit_id business_unit_id,
1949 ad.set_of_books_id set_of_books_id,
1950 ad.currency_code_fc currency_code_fc,
1951 0 original_budget,
1952 trunc(bu2.approval_date) transaction_create_date,
1953 0 transfer_in,
1954 SUM(decode(bu2.transfer_type,'TRANSFER', nvl(bu2.approved_amount,0),0))+
1955 SUM(decode(bu2.transfer_type,'REQUEST', nvl(bu2.approved_amount,0),0)) transfer_out,
1956 SUM(decode(bu2.transfer_type, 'RESERVE', nvl(bu2.approved_amount,0),0))-
1957 SUM(decode(bu2.transfer_type, 'RELEASE', nvl(bu2.approved_amount,0),0)) holdback_amt,
1958 nvl(bu2.request_currency,'USD') from_currency,
1959 0 conversion_rate,
1960 0 planned,
1961 0 committed,
1962 0 utilized,
1963 0 paid,
1964 'TRANSFER_OUT' metric_type,
1965 0 accrual,
1966 0 conversion_rate_s,
1967 0 original_budget_s,
1968 0 transfer_in_s,
1969 0 transfer_out_s,
1970 0 holdback_amt_s,
1971 0 planned_s,
1972 0 committed_s,
1973 0 utilized_s,
1974 0 accrual_s,
1975 0 paid_s
1976 FROM ozf_funds_all_b ad,
1977 ozf_act_budgets BU2
1978 WHERE bu2.approval_date between p_start_date and p_end_date
1979 AND bu2.status_code = 'APPROVED'
1980 AND bu2.arc_act_budget_used_by = 'FUND'
1981 AND bu2.budget_source_type='FUND'
1982 AND bu2.budget_source_id = ad.fund_id
1983 GROUP BY ad.fund_id,
1984 trunc(bu2.approval_date) ,
1985 ad.fund_number,
1986 ad.start_date_active ,
1987 ad.end_date_active ,
1988 ad.start_period_name ,
1989 ad.end_period_name ,
1990 ad.category_id ,
1991 ad.status_code ,
1992 ad.fund_type ,
1993 ad.parent_fund_id,
1994 ad.country_id,
1995 ad.org_id ,
1996 ad.business_unit_id,
1997 ad.set_of_books_id ,
1998 ad.currency_code_fc ,
1999 ad.original_budget,
2000 nvl(bu2.request_currency,'USD')
2001 UNION ALL--planned
2002 SELECT ad.fund_id fund_id,
2003 ad.fund_number fund_number,
2004 ad.start_date_active start_date,
2008 ad.category_id category_id,
2005 ad.end_date_active end_date,
2006 ad.start_period_name start_period,
2007 ad.end_period_name end_period,
2009 ad.status_code status,
2010 ad.fund_type fund_type,
2011 ad.parent_fund_id parent_fund_id,
2012 ad.country_id country,
2013 ad.org_id org_id,
2014 ad.business_unit_id business_unit_id,
2015 ad.set_of_books_id set_of_books_id,
2016 ad.currency_code_fc currency_code_fc,
2017 0 original_budget,
2018 trunc(nvl(bu2.request_date,bu2.creation_date)) transaction_create_date,
2019 0 transfer_in,
2020 0 transfer_out,
2021 0 holdback_amt,
2022 nvl(bu2.request_currency,'USD') from_currency,
2023 0 conversion_rate,
2024 SUM(nvl(bu2.request_amount,0)) planned,
2025 0 committed,
2026 0 utilized,
2027 0 paid,
2028 'PLANNED' metric_type,
2029 0 accrual,
2030 0 conversion_rate_s,
2031 0 original_budget_s,
2032 0 transfer_in_s,
2033 0 transfer_out_s,
2034 0 holdback_amt_s,
2035 0 planned_s,
2036 0 committed_s,
2037 0 utilized_s,
2038 0 accrual_s,
2039 0 paid_s
2040 FROM ozf_funds_all_b ad,
2041 ozf_act_budgets BU2
2042 WHERE bu2.request_date between p_start_date and p_end_date
2043 AND bu2.budget_source_type='FUND'
2044 AND bu2.ARC_ACT_BUDGET_USED_BY <> 'FUND'
2045 AND nvl(bu2.request_date,bu2.creation_date) <=p_end_date
2046 AND bu2.budget_source_id = ad.fund_id
2047 GROUP BY ad.fund_id,
2048 trunc(nvl(bu2.request_date,bu2.creation_date)) ,
2049 ad.fund_number,
2050 ad.start_date_active ,
2051 ad.end_date_active ,
2052 ad.start_period_name ,
2053 ad.end_period_name ,
2054 ad.category_id ,
2055 ad.status_code ,
2056 ad.fund_type ,
2057 ad.parent_fund_id,
2058 ad.country_id,
2059 ad.org_id ,
2060 ad.business_unit_id,
2061 ad.set_of_books_id ,
2062 ad.currency_code_fc ,
2063 ad.original_budget,
2064 nvl(bu2.request_currency,'USD')
2065 UNION ALL--planned 2
2066 SELECT ad.fund_id fund_id,
2067 ad.fund_number fund_number,
2068 ad.start_date_active start_date,
2069 ad.end_date_active end_date,
2070 ad.start_period_name start_period,
2071 ad.end_period_name end_period,
2072 ad.category_id category_id,
2073 ad.status_code status,
2074 ad.fund_type fund_type,
2075 ad.parent_fund_id parent_fund_id,
2076 ad.country_id country,
2077 ad.org_id org_id,
2078 ad.business_unit_id business_unit_id,
2079 ad.set_of_books_id set_of_books_id,
2080 ad.currency_code_fc currency_code_fc,
2081 0 original_budget,
2082 trunc(bu2.approval_date) transaction_create_date,
2083 0 transfer_in,
2084 0 transfer_out,
2085 0 holdback_amt,
2086 nvl(bu2.request_currency,'USD') from_currency,
2087 0 conversion_rate,
2088 0-SUM(nvl(bu2.approved_amount,0)) planned,
2089 0 committed,
2090 0 utilized,
2091 0 paid,
2092 'PLANNED' metric_type,
2093 0 accrual,
2094 0 conversion_rate_s,
2095 0 original_budget_s,
2096 0 transfer_in_s,
2097 0 transfer_out_s,
2098 0 holdback_amt_s,
2099 0 planned_s,
2100 0 committed_s,
2101 0 utilized_s,
2102 0 accrual_s,
2103 0 paid_s
2104 FROM ozf_funds_all_b ad,
2105 ozf_act_budgets BU2
2106 WHERE bu2.approval_date between p_start_date and p_end_date
2107 AND bu2.arc_act_budget_used_by ='FUND'
2108 AND bu2.budget_source_type<>'FUND'
2109 AND bu2.status_code ='APPROVED'
2110 AND bu2.act_budget_used_by_id = ad.fund_id
2111 GROUP BY ad.fund_id,
2112 trunc(bu2.approval_date) ,
2113 ad.fund_number,
2114 ad.start_date_active ,
2115 ad.end_date_active ,
2116 ad.start_period_name ,
2117 ad.end_period_name ,
2118 ad.category_id ,
2119 ad.status_code ,
2120 ad.fund_type ,
2121 ad.parent_fund_id,
2122 ad.country_id,
2123 ad.org_id ,
2124 ad.business_unit_id,
2125 ad.set_of_books_id ,
2126 ad.currency_code_fc ,
2127 ad.original_budget,
2128 nvl(bu2.request_currency,'USD')
2129 UNION ALL--committed 1
2130 SELECT ad.fund_id fund_id,
2131 ad.fund_number fund_number,
2132 ad.start_date_active start_date,
2133 ad.end_date_active end_date,
2134 ad.start_period_name start_period,
2135 ad.end_period_name end_period,
2136 ad.category_id category_id,
2137 ad.status_code status,
2138 ad.fund_type fund_type,
2139 ad.parent_fund_id parent_fund_id,
2140 ad.country_id country,
2141 ad.org_id org_id,
2142 ad.business_unit_id business_unit_id,
2143 ad.set_of_books_id set_of_books_id,
2144 ad.currency_code_fc currency_code_fc,
2148 0 transfer_out,
2145 0 original_budget,
2146 trunc(bu2.approval_date) transaction_create_date,
2147 0 transfer_in,
2149 0 holdback_amt,
2150 nvl(bu2.request_currency,'USD') from_currency,
2151 0 conversion_rate,
2152 0 planned,
2153 SUM(nvl(bu2.approved_amount,0)) committed,
2154 0 utilized,
2155 0 paid,
2156 'COMMITTED' metric_type,
2157 0 accrual,
2158 0 conversion_rate_s,
2159 0 original_budget_s,
2160 0 transfer_in_s,
2161 0 transfer_out_s,
2162 0 holdback_amt_s,
2163 0 planned_s,
2164 0 committed_s,
2165 0 utilized_s,
2166 0 accrual_s,
2167 0 paid_s
2168 FROM ozf_funds_all_b ad,
2169 ozf_act_budgets BU2
2170 WHERE bu2.approval_date between p_start_date and p_end_date
2171 AND bu2.budget_source_type ='FUND'
2172 AND bu2.ARC_ACT_BUDGET_USED_BY <> 'FUND'
2173 AND bu2.budget_source_id = ad.fund_id
2174 GROUP BY ad.fund_id,
2175 trunc(bu2.approval_date) ,
2176 ad.fund_number,
2177 ad.start_date_active ,
2178 ad.end_date_active ,
2179 ad.start_period_name ,
2180 ad.end_period_name ,
2181 ad.category_id ,
2182 ad.status_code ,
2183 ad.fund_type ,
2184 ad.parent_fund_id,
2185 ad.country_id,
2186 ad.org_id ,
2187 ad.business_unit_id,
2188 ad.set_of_books_id ,
2189 ad.currency_code_fc ,
2190 ad.original_budget,
2191 nvl(bu2.request_currency,'USD')
2192 UNION ALL--committed 2
2193 SELECT ad.fund_id fund_id,
2194 ad.fund_number fund_number,
2195 ad.start_date_active start_date,
2196 ad.end_date_active end_date,
2197 ad.start_period_name start_period,
2198 ad.end_period_name end_period,
2199 ad.category_id category_id,
2200 ad.status_code status,
2201 ad.fund_type fund_type,
2202 ad.parent_fund_id parent_fund_id,
2203 ad.country_id country,
2204 ad.org_id org_id,
2205 ad.business_unit_id business_unit_id,
2206 ad.set_of_books_id set_of_books_id,
2207 ad.currency_code_fc currency_code_fc,
2208 0 original_budget,
2209 trunc(bu2.approval_date) transaction_create_date,
2210 0 transfer_in,
2211 0 transfer_out,
2212 0 holdback_amt,
2213 nvl(bu2.request_currency,'USD') from_currency,
2214 0 conversion_rate,
2215 0 planned,
2216 0-SUM(nvl(bu2.approved_amount,0)) committed,
2217 0 utilized,
2218 0 paid,
2219 'COMMITTED' metric_type,
2220 0 accrual,
2221 0 conversion_rate_s,
2222 0 original_budget_s,
2223 0 transfer_in_s,
2224 0 transfer_out_s,
2225 0 holdback_amt_s,
2226 0 planned_s,
2227 0 committed_s,
2228 0 utilized_s,
2229 0 accrual_s,
2230 0 paid_s
2231 FROM ozf_funds_all_b ad,
2232 ozf_act_budgets BU2
2233 WHERE bu2.approval_date between p_start_date and p_end_date
2234 AND bu2.arc_act_budget_used_by ='FUND'
2235 AND bu2.budget_source_type<>'FUND'
2236 AND bu2.status_code ='APPROVED'
2237 AND bu2.act_budget_used_by_id = ad.fund_id
2238 GROUP BY ad.fund_id,
2239 trunc(bu2.approval_date) ,
2240 ad.fund_number,
2241 ad.start_date_active ,
2242 ad.end_date_active ,
2243 ad.start_period_name ,
2244 ad.end_period_name ,
2245 ad.category_id ,
2246 ad.status_code ,
2247 ad.fund_type ,
2248 ad.parent_fund_id,
2249 ad.country_id,
2250 ad.org_id ,
2251 ad.business_unit_id,
2252 ad.set_of_books_id ,
2253 ad.currency_code_fc ,
2254 ad.original_budget,
2255 nvl(bu2.request_currency,'USD')
2256 UNION ALL --utilized
2257 SELECT ad.fund_id fund_id,
2258 ad.fund_number fund_number,
2259 ad.start_date_active start_date,
2260 ad.end_date_active end_date,
2261 ad.start_period_name start_period,
2262 ad.end_period_name end_period,
2263 ad.category_id category_id,
2264 ad.status_code status,
2265 ad.fund_type fund_type,
2266 ad.parent_fund_id parent_fund_id,
2267 ad.country_id country,
2268 ad.org_id org_id,
2269 ad.business_unit_id business_unit_id,
2270 ad.set_of_books_id set_of_books_id,
2271 ad.currency_code_fc currency_code_fc,
2272 0 original_budget,
2273 trunc(u2.creation_date) transaction_create_date,
2274 0 transfer_in,
2275 0 transfer_out,
2276 0 holdback_amt,
2277 nvl(u2.currency_code,'USD') from_currency,
2278 0 conversion_rate,
2279 0 planned,
2280 0 committed,
2281 SUM(nvl(u2.amount,0)) utilized,
2282 0 paid,
2283 'UTILIZED' metric_type,
2284 0 accrual,
2285 0 conversion_rate_s,
2286 0 original_budget_s,
2287 0 transfer_in_s,
2288 0 transfer_out_s,
2289 0 holdback_amt_s,
2290 0 planned_s,
2291 0 committed_s,
2292 0 utilized_s,
2293 0 accrual_s,
2294 0 paid_s
2295 FROM ozf_funds_all_b ad,
2296 ozf_funds_utilized_all_b u2
2297 WHERE u2.creation_date between p_start_date and p_end_date
2298 AND ad.fund_id =u2.fund_id
2299 AND u2.utilization_type in ('UTILIZED','ACCRUAL','ADJUSTMENT')
2300 GROUP BY ad.fund_id,
2301 trunc(u2.creation_date) ,
2302 ad.fund_number,
2303 ad.start_date_active ,
2304 ad.end_date_active ,
2305 ad.start_period_name ,
2306 ad.end_period_name ,
2307 ad.category_id ,
2308 ad.status_code ,
2309 ad.fund_type ,
2310 ad.parent_fund_id,
2311 ad.country_id,
2312 ad.org_id ,
2313 ad.business_unit_id,
2314 ad.set_of_books_id ,
2315 ad.currency_code_fc ,
2316 ad.original_budget,
2317 nvl(u2.currency_code,'USD')
2318 UNION ALL --utilized 2
2319 SELECT ad.fund_id fund_id,
2320 ad.fund_number fund_number,
2321 ad.start_date_active start_date,
2322 ad.end_date_active end_date,
2323 ad.start_period_name start_period,
2324 ad.end_period_name end_period,
2325 ad.category_id category_id,
2326 ad.status_code status,
2327 ad.fund_type fund_type,
2328 ad.parent_fund_id parent_fund_id,
2329 ad.country_id country,
2330 ad.org_id org_id,
2331 ad.business_unit_id business_unit_id,
2332 ad.set_of_books_id set_of_books_id,
2333 ad.currency_code_fc currency_code_fc,
2334 0 original_budget,
2335 trunc(u2.creation_date) transaction_create_date,
2336 0 transfer_in,
2337 0 transfer_out,
2338 0 holdback_amt,
2339 nvl(u2.currency_code,'USD') from_currency,
2340 0 conversion_rate,
2341 0 planned,
2342 0 committed,
2343 0-SUM(nvl(u2.amount,0)) utilized,
2344 0 paid,
2345 'UTILIZED' metric_type,
2346 0 accrual,
2347 0 conversion_rate_s,
2348 0 original_budget_s,
2349 0 transfer_in_s,
2350 0 transfer_out_s,
2351 0 holdback_amt_s,
2352 0 planned_s,
2353 0 committed_s,
2354 0 utilized_s,
2355 0 accrual_s,
2356 0 paid_s
2357 FROM ozf_funds_all_b ad,
2358 ozf_funds_utilized_all_b u2
2359 WHERE u2.creation_date between p_start_date and p_end_date
2360 AND ad.fund_id =u2.fund_id
2361 AND ad.fund_type='FULLY_ACCRUED'
2362 AND ad.accrual_basis ='CUSTOMER'
2363 AND ad.liability_flag='N'
2364 AND ad.plan_id=u2.component_id
2365 AND u2.component_type='OFFR'
2366 AND u2.utilization_type='ACCRUAL'
2367 GROUP BY ad.fund_id,
2368 trunc(u2.creation_date) ,
2369 ad.fund_number,
2370 ad.start_date_active ,
2371 ad.end_date_active ,
2372 ad.start_period_name ,
2373 ad.end_period_name ,
2374 ad.category_id ,
2375 ad.status_code ,
2376 ad.fund_type ,
2377 ad.parent_fund_id,
2378 ad.country_id,
2379 ad.org_id ,
2380 ad.business_unit_id,
2384 nvl(u2.currency_code,'USD')
2381 ad.set_of_books_id ,
2382 ad.currency_code_fc ,
2383 ad.original_budget,
2385 UNION ALL --accrual 1
2386 SELECT ad.fund_id fund_id,
2387 ad.fund_number fund_number,
2388 ad.start_date_active start_date,
2389 ad.end_date_active end_date,
2390 ad.start_period_name start_period,
2391 ad.end_period_name end_period,
2392 ad.category_id category_id,
2393 ad.status_code status,
2394 ad.fund_type fund_type,
2395 ad.parent_fund_id parent_fund_id,
2396 ad.country_id country,
2397 ad.org_id org_id,
2398 ad.business_unit_id business_unit_id,
2399 ad.set_of_books_id set_of_books_id,
2400 ad.currency_code_fc currency_code_fc,
2401 0 original_budget,
2402 trunc(u2.creation_date) transaction_create_date,
2403 0 transfer_in,
2404 0 transfer_out,
2405 0 holdback_amt,
2406 nvl(u2.currency_code,'USD') from_currency,
2407 0 conversion_rate,
2408 0 planned,
2409 0 committed,
2410 0 utilized,
2411 0 paid,
2412 'ACCRUAL' metric_type,
2413 SUM(nvl(u2.amount,0)) accrual,
2414 0 conversion_rate_s,
2415 0 original_budget_s,
2416 0 transfer_in_s,
2417 0 transfer_out_s,
2418 0 holdback_amt_s,
2419 0 planned_s,
2420 0 committed_s,
2421 0 utilized_s,
2422 0 accrual_s,
2423 0 paid_s
2424 FROM ozf_funds_all_b ad,
2425 ozf_funds_utilized_all_b u2
2426 WHERE u2.creation_date between p_start_date and p_end_date
2427 AND ad.fund_id =u2.fund_id
2428 AND ad.fund_type='FULLY_ACCRUED'
2429 AND ad.accrual_basis ='SALES'
2430 AND ad.plan_id=u2.component_id
2431 AND u2.component_type='OFFR'
2432 AND u2.utilization_type='SALES_ACCRUAL'
2433 GROUP BY ad.fund_id,
2434 trunc(u2.creation_date) ,
2435 ad.fund_number,
2436 ad.start_date_active ,
2437 ad.end_date_active ,
2438 ad.start_period_name ,
2439 ad.end_period_name ,
2440 ad.category_id ,
2441 ad.status_code ,
2442 ad.fund_type ,
2443 ad.parent_fund_id,
2444 ad.country_id,
2445 ad.org_id ,
2446 ad.business_unit_id,
2447 ad.set_of_books_id ,
2448 ad.currency_code_fc ,
2449 ad.original_budget,
2450 nvl(u2.currency_code,'USD')
2451 UNION ALL --accrual 2
2452 SELECT ad.fund_id fund_id,
2453 ad.fund_number fund_number,
2454 ad.start_date_active start_date,
2455 ad.end_date_active end_date,
2456 ad.start_period_name start_period,
2457 ad.end_period_name end_period,
2458 ad.category_id category_id,
2459 ad.status_code status,
2460 ad.fund_type fund_type,
2461 ad.parent_fund_id parent_fund_id,
2462 ad.country_id country,
2463 ad.org_id org_id,
2464 ad.business_unit_id business_unit_id,
2465 ad.set_of_books_id set_of_books_id,
2466 ad.currency_code_fc currency_code_fc,
2467 0 original_budget,
2468 trunc(u2.creation_date) transaction_create_date,
2469 0 transfer_in,
2470 0 transfer_out,
2471 0 holdback_amt,
2472 nvl(u2.currency_code,'USD') from_currency,
2473 0 conversion_rate,
2474 0 planned,
2475 0 committed,
2476 0 utilized,
2477 0 paid,
2478 'ACCRUAL' metric_type,
2479 SUM(nvl(u2.amount,0)) accrual,
2480 0 conversion_rate_s,
2481 0 original_budget_s,
2482 0 transfer_in_s,
2483 0 transfer_out_s,
2484 0 holdback_amt_s,
2485 0 planned_s,
2486 0 committed_s,
2487 0 utilized_s,
2488 0 accrual_s,
2489 0 paid_s
2490 FROM ozf_funds_all_b ad,
2491 ozf_funds_utilized_all_b u2
2492 WHERE u2.creation_date between p_start_date and p_end_date
2493 AND ad.fund_id =u2.fund_id
2494 AND ad.fund_type='FULLY_ACCRUED'
2495 AND ad.accrual_basis ='CUSTOMER'
2496 AND ad.liability_flag='N'
2497 AND ad.plan_id=u2.component_id
2498 AND u2.component_type='OFFR'
2499 AND u2.utilization_type ='ACCRUAL'
2500 GROUP BY ad.fund_id,
2501 trunc(u2.creation_date) ,
2502 ad.fund_number,
2503 ad.start_date_active ,
2504 ad.end_date_active ,
2505 ad.start_period_name ,
2506 ad.end_period_name ,
2507 ad.category_id ,
2508 ad.status_code ,
2509 ad.fund_type ,
2510 ad.parent_fund_id,
2511 ad.country_id,
2512 ad.org_id ,
2513 ad.business_unit_id,
2514 ad.set_of_books_id ,
2515 ad.currency_code_fc ,
2516 ad.original_budget,
2517 nvl(u2.currency_code,'USD')
2518 UNION ALL--paid 1
2519 SELECT ad.fund_id fund_id,
2520 ad.fund_number fund_number,
2521 ad.start_date_active start_date,
2522 ad.end_date_active end_date,
2523 ad.start_period_name start_period,
2524 ad.end_period_name end_period,
2525 ad.category_id category_id,
2526 ad.status_code status,
2527 ad.fund_type fund_type,
2528 ad.parent_fund_id parent_fund_id,
2529 ad.country_id country,
2530 ad.org_id org_id,
2531 ad.business_unit_id business_unit_id,
2532 ad.set_of_books_id set_of_books_id,
2536 0 transfer_in,
2533 ad.currency_code_fc currency_code_fc,
2534 0 original_budget,
2535 trunc(u2.creation_date) transaction_create_date,
2537 0 transfer_out,
2538 0 holdback_amt,
2539 nvl(u2.currency_code,'USD') from_currency,
2540 0 conversion_rate,
2541 0 planned,
2542 0 committed,
2543 0 utilized,
2544 SUM(nvl(u2.amount,0)) paid,
2545 'PAID' metric_type,
2546 0 accrual,
2547 0 conversion_rate_s,
2548 0 original_budget_s,
2549 0 transfer_in_s,
2550 0 transfer_out_s,
2551 0 holdback_amt_s,
2552 0 planned_s,
2553 0 committed_s,
2554 0 utilized_s,
2555 0 accrual_s,
2556 0 paid_s
2557 FROM ozf_funds_all_b ad,
2558 ozf_funds_utilized_all_b u2
2559 WHERE u2.creation_date between p_start_date and p_end_date
2560 AND ad.fund_id =u2.fund_id
2561 AND u2.utilization_type ='UTILIZED'
2562 GROUP BY ad.fund_id,
2563 trunc(u2.creation_date) ,
2564 ad.fund_number,
2565 ad.start_date_active ,
2566 ad.end_date_active ,
2567 ad.start_period_name ,
2568 ad.end_period_name ,
2569 ad.category_id ,
2570 ad.status_code ,
2571 ad.fund_type ,
2572 ad.parent_fund_id,
2573 ad.country_id,
2574 ad.org_id ,
2575 ad.business_unit_id,
2576 ad.set_of_books_id ,
2577 ad.currency_code_fc ,
2578 ad.original_budget,
2579 nvl(u2.currency_code,'USD')
2580 UNION ALL--paid 2, based on 11.5.9
2581 SELECT ad.fund_id fund_id,
2582 ad.fund_number fund_number,
2583 ad.start_date_active start_date,
2584 ad.end_date_active end_date,
2585 ad.start_period_name start_period,
2586 ad.end_period_name end_period,
2587 ad.category_id category_id,
2588 ad.status_code status,
2589 ad.fund_type fund_type,
2590 ad.parent_fund_id parent_fund_id,
2591 ad.country_id country,
2592 ad.org_id org_id,
2593 ad.business_unit_id business_unit_id,
2594 ad.set_of_books_id set_of_books_id,
2595 ad.currency_code_fc currency_code_fc,
2596 0 original_budget,
2597 trunc(cla.claim_date) transaction_create_date,
2598 0 transfer_in,
2599 0 transfer_out,
2600 0 holdback_amt,
2601 nvl(cuti.currency_code,'USD') from_currency,
2602 0 conversion_rate,
2603 0 planned,
2604 0 committed,
2605 0 utilized,
2606 SUM(nvl(cuti.amount,0)) paid,
2607 'PAID' metric_type,
2608 0 accrual,
2609 0 conversion_rate_s,
2610 0 original_budget_s,
2611 0 transfer_in_s,
2612 0 transfer_out_s,
2613 0 holdback_amt_s,
2614 0 planned_s,
2615 0 committed_s,
2616 0 utilized_s,
2617 0 accrual_s,
2618 0 paid_s
2619 FROM ozf_funds_all_b ad,
2620 ozf_funds_utilized_all_b u2,
2621 ozf_claim_lines_util_all cuti,
2622 ozf_claim_lines_all cln,
2623 ozf_claims_all cla
2624 WHERE cla.claim_date between p_start_date and p_end_date
2625 AND ad.fund_id =u2.fund_id
2626 AND u2.utilization_id= cuti.utilization_id
2627 AND u2.utilization_type IN ('ACCRUAL','SALES_ACCRUAL','ADJUSTMENT')
2628 AND cuti.claim_line_id= cln.claim_line_id
2629 AND cln.claim_id = cla.claim_id
2630 AND cla.status_code = 'CLOSED'
2631 GROUP BY ad.fund_id,
2632 trunc(cla.claim_date) ,
2633 ad.fund_number,
2634 ad.start_date_active ,
2635 ad.end_date_active ,
2636 ad.start_period_name ,
2637 ad.end_period_name ,
2638 ad.category_id ,
2639 ad.status_code ,
2640 ad.fund_type ,
2641 ad.parent_fund_id,
2642 ad.country_id,
2643 ad.org_id ,
2644 ad.business_unit_id,
2645 ad.set_of_books_id ,
2646 ad.currency_code_fc ,
2647 ad.original_budget,
2648 nvl(cuti.currency_code,'USD')
2649 )
2650 GROUP BY
2651 fund_id,
2652 transaction_create_date,
2653 fund_number,
2654 start_date,
2655 end_date,
2656 start_period,
2657 end_period,
2658 category_id,
2659 status,
2660 fund_type,
2661 parent_fund_id,
2662 country,
2663 org_id,
2664 business_unit_id,
2665 set_of_books_id,
2666 currency_code_fc,
2667 original_budget,
2668 from_currency,
2669 conversion_rate,
2670 metric_type,
2671 conversion_rate_s
2672 )inner;
2673
2674 BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Inserting into BIM_I_BGT_RATES');
2675 --insert into bim_i_mkt_rates
2676 INSERT
2677 INTO BIM_I_BGT_RATES BRT(tc_code,
2678 trx_date,
2679 prim_conversion_rate,
2680 sec_conversion_rate)
2681 SELECT from_currency,
2682 transaction_create_date,
2683 FII_CURRENCY.get_rate(from_currency,l_global_currency_code,transaction_create_date,l_pgc_rate_type),
2684 FII_CURRENCY.get_rate(from_currency,l_secondary_currency_code,transaction_create_date,l_sgc_rate_type)
2685 FROM (select distinct from_currency from_currency,
2686 transaction_create_date transaction_create_date
2687 from bim_i_budget_facts_stg);
2688
2692 commit;
2689 l_check_missing_rate := Check_Missing_Rates (p_start_date);
2690 if (l_check_missing_rate = -1) then
2691 DELETE from BIM_I_BUDGET_FACTS_stg where transaction_create_date>= p_start_date;
2693 x_return_status := FND_API.G_RET_STS_ERROR;
2694 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2695 end if;
2696 BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Incremental Load:after calling checking_missing_rates');
2697
2698 --insert into facts table
2699 INSERT /*+ append parallel */
2700 INTO BIM_I_BUDGET_FACTS CDF(
2701 creation_date
2702 ,last_update_date
2703 ,created_by
2704 ,last_updated_by
2705 ,last_update_login
2706 ,fund_id
2707 ,parent_fund_id
2708 ,fund_number
2709 ,start_date
2710 ,end_date
2711 ,start_period
2712 ,end_period
2713 ,set_of_books_id
2714 ,fund_type
2715 --,region
2716 ,country
2717 ,org_id
2718 ,category_id
2719 ,status
2720 ,original_budget
2721 ,transfer_in
2722 ,transfer_out
2723 ,holdback_amt
2724 ,currency_code_fc
2725 ,delete_flag
2726 ,transaction_create_date
2727 ,business_unit_id
2728 ,from_currency
2729 ,conversion_rate
2730 ,planned
2731 ,committed
2732 ,utilized
2733 ,paid
2734 ,metric_type
2735 ,accrual
2736 ,conversion_rate_s
2737 ,original_budget_s
2738 ,transfer_in_s
2739 ,transfer_out_s
2740 ,holdback_amt_s
2741 ,planned_s
2742 ,committed_s
2743 ,utilized_s
2744 ,accrual_s
2745 ,paid_s)
2746 SELECT /*+ parallel */
2747 sysdate,
2748 sysdate,
2749 l_user_id,
2750 l_user_id,
2751 l_user_id,
2752 inner.fund_id,
2753 inner.parent_fund_id,
2754 inner.fund_number,
2755 inner.start_date,
2756 inner.end_date,
2757 inner.start_period,
2758 inner.end_period,
2759 inner.set_of_books_id,
2760 inner.fund_type,
2761 --inner.region,
2762 inner.country,
2763 inner.org_id,
2764 inner.category_id,
2765 inner.status,
2766 inner.original_budget*prim_conversion_rate,
2767 inner.transfer_in*prim_conversion_rate,
2768 inner.transfer_out*prim_conversion_rate,
2769 inner.holdback_amt*prim_conversion_rate,
2770 inner.currency_code_fc,
2771 'N',
2772 inner.transaction_create_date,
2773 inner.business_unit_id,
2774 inner.from_currency,
2775 inner.conversion_rate,
2776 inner.planned*prim_conversion_rate,
2777 inner.committed*prim_conversion_rate,
2778 inner.utilized*prim_conversion_rate,
2779 inner.paid*prim_conversion_rate,
2780 inner.metric_type,
2781 inner.accrual*prim_conversion_rate,
2782 inner.conversion_rate_s,
2783 inner.original_budget*sec_conversion_rate,
2784 inner.transfer_in*sec_conversion_rate,
2785 inner.transfer_out*sec_conversion_rate,
2786 inner.holdback_amt*sec_conversion_rate,
2787 inner.planned*sec_conversion_rate,
2788 inner.committed*sec_conversion_rate,
2789 inner.utilized*sec_conversion_rate,
2790 inner.accrual*sec_conversion_rate,
2791 inner.paid*sec_conversion_rate
2792 FROM bim_i_budget_facts_stg inner, bim_i_bgt_rates rt
2793 where inner.from_currency = rt.tc_code
2794 and inner.transaction_create_date= rt.trx_date;
2795
2796 -- Analyze the daily facts table
2797 DBMS_STATS.gather_table_stats('BIM','BIM_I_BUDGET_FACTS', estimate_percent => 5,
2798 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
2799
2800 --dbms_output.put_line('b4 inserting log');
2801 BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Before Insert into log.');
2802 BEGIN
2803 IF (Not BIS_COLLECTION_UTILITIES.setup('BUDGET_FACTS')) THEN
2804 RAISE FND_API.G_EXC_ERROR;
2805 return;
2806 END IF;
2807 BIS_COLLECTION_UTILITIES.WRAPUP(
2808 p_status => TRUE ,
2809 p_period_from =>p_start_date,
2810 p_period_to => sysdate
2811 );
2812 Exception when others then
2813 Rollback;
2814 BIS_COLLECTION_UTILITIES.WRAPUP(
2815 p_status => FALSE,
2816 p_period_from =>p_start_date,
2817 p_period_to =>sysdate
2818 );
2819 RAISE FND_API.G_EXC_ERROR;
2820 END;
2821 BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:After Insert into log.');
2822 EXCEPTION
2823 WHEN FND_API.G_EXC_ERROR THEN
2824 x_return_status := FND_API.G_RET_STS_ERROR;
2825 -- Standard call to get message count and if count=1, get the message
2826 FND_msg_PUB.Count_And_Get (
2827 -- p_encoded => FND_API.G_FALSE,
2828 p_count => x_msg_count,
2829 p_data => x_msg_data
2830 );
2831
2832 BIS_COLLECTION_UTILITIES.log('SUBSEQUENT_LOAD:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
2833 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2834
2835 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2836
2837 -- Standard call to get message count and if count=1, get the message
2838 FND_msg_PUB.Count_And_Get (
2839 --p_encoded => FND_API.G_FALSE,
2840 p_count => x_msg_count,
2841 p_data => x_msg_data
2842 );
2843
2844 ams_utility_pvt.write_conc_log('BIM_I_BUDGET_FACTS:SUBSEQUENT_LOAD:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
2845 BIS_COLLECTION_UTILITIES.log('SUBSEQUENT_LOAD:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
2846 WHEN OTHERS THEN
2847
2848 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2849
2850 IF FND_msg_PUB.Check_msg_Level (FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
2851 THEN
2852 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
2853 END IF;
2854
2855 -- Standard call to get message count and if count=1, get the message
2856 FND_msg_PUB.Count_And_Get (
2857 -- p_encoded => FND_API.G_FALSE,
2858 p_count => x_msg_count,
2859 p_data => x_msg_data
2860 );
2861 BIS_COLLECTION_UTILITIES.log('SUBSEQUENT_LOAD:IN other EXCEPTION '||sqlerrm(sqlcode));
2862 END SUB_LOAD;
2863 END BIM_I_BGT_FACTS_PKG;