[Home] [Help]
PACKAGE BODY: APPS.BIM_FUND_FACTS
Source
1 PACKAGE BODY BIM_FUND_FACTS AS
2 /* $Header: bimbgtfb.pls 120.4 2005/11/11 05:27:54 arvikuma noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(20) :='BIM_FUND_FACTS';
5 G_FILE_NAME CONSTANT VARCHAR2(20) :='bimbgtfb.pls';
6 l_to_currency VARCHAR2(100) := fnd_profile.value('AMS_DEFAULT_CURR_CODE');
7 l_conversion_type VARCHAR2(30):= fnd_profile.VALUE('AMS_CURR_CONVERSION_TYPE');
8 ---------------------------------------------------------------------
9 -- FUNCTION
10 -- Convert_Currency
11 -- NOTE: Given from currency, from amount, converts to default currency amount.
12 -- Default currency can be get from profile value.
13 -- PARAMETER
14 -- p_from_currency IN VARCHAR2,
15 -- p_to_currency IN VARCHAR2,
16 -- p_from_amount IN NUMBER,
17 -- RETURN NUMBER
18 ---------------------------------------------------------------------
19 FUNCTION convert_currency(
20 p_from_currency VARCHAR2 ,
21 p_from_amount NUMBER) return NUMBER
22 IS
23 l_user_rate CONSTANT NUMBER := 1;
24 l_max_roll_days CONSTANT NUMBER := -1;
25 l_denominator NUMBER; -- Not used in Marketing.
26 l_numerator NUMBER; -- Not used in Marketing.
27 l_to_amount NUMBER;
28 l_rate NUMBER;
29 BEGIN
30
31 -- Conversion type cannot be null in profile
32 IF l_conversion_type IS NULL THEN
33 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
34 fnd_message.set_name('OZF', 'OZF_NO_EXCHANGE_TYPE');
35 fnd_msg_pub.add;
36 END IF;
37 RETURN 0;
38 END IF;
39
40 -- Call the proper GL API to convert the amount.
41 gl_currency_api.convert_closest_amount(
42 x_from_currency => p_from_currency
43 ,x_to_currency => l_to_currency
44 ,x_conversion_date =>sysdate
45 ,x_conversion_type => l_conversion_type
46 ,x_user_rate => l_user_rate
47 ,x_amount => p_from_amount
48 ,x_max_roll_days => l_max_roll_days
49 ,x_converted_amount => l_to_amount
50 ,x_denominator => l_denominator
51 ,x_numerator => l_numerator
52 ,x_rate => l_rate);
53 RETURN (l_to_amount);
54 EXCEPTION
55 WHEN gl_currency_api.no_rate THEN
56 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
57 fnd_message.set_name('OZF', 'OZF_NO_RATE');
58 fnd_msg_pub.add;
59 END IF;
60 RETURN 0;
61 WHEN gl_currency_api.invalid_currency THEN
62 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
63 fnd_message.set_name('OZF', 'OZF_INVALID_CURR');
64 fnd_msg_pub.add;
65 END IF;
66 RETURN 0;
67 WHEN OTHERS THEN
68 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
69 fnd_msg_pub.add_exc_msg('OZF_UTLITY_PVT', 'Convert_curency');
70 END IF;
71 RETURN 0;
72 END convert_currency;
73
74 ---------------------------------------------------------------------
75 -- PROCEDURE
76 -- update_balance
77 -- NOTE: This procedure will be called only once within FUND_FIRST_LOAD
78 -- It inserts data into bim_r_fund_balance, which is a daily balance
79 -- table. It gathers data from bim_r_fund_daily_facts,
80 -- and bim_r_fdsp_daily_facts. Calculates balance based on data from
81 -- those two tables.
82 ---------------------------------------------------------------------
83 PROCEDURE update_balance
84 IS
85 l_id number;
86 l_week DATE;
87 l_index_tablespace varchar2(100);
88
89 /*cursor balance_cur is to get all the fund_id, and all the
90 transaction date excluding the minimum date. */
91 CURSOR balance_cur IS
92 SELECT fund_id,week_date
93 FROM bim_r_fund_balance a
94 WHERE exists --week_date not in
95 ( SELECT /*+ index_desc(b,bim_r_fund_balance_n6) */ null
96 FROM bim_r_fund_balance b
97 WHERE b.fund_id = a.fund_id
98 AND b.week_date < a.week_date )
99 order by fund_id, week_date ;
100
101
102 /*cursor min_date is to get all the info about each fund's minimum date.
103 ie. the initial balance would be the original budget*/
104 CURSOR min_date IS
105 SELECT b.fund_id fund_id,
106 c.week_date week_date,
107 nvl(b.original_budget,0) original,
108 nvl(b.original_budget,0)+nvl(b.transfer_in,0)-nvl(b.transfer_out,0) available,
109 nvl(c.commited_amt,0) commited,
110 nvl(c.commited_amt,0) commited_sum,
111 nvl(b.original_budget,0)+nvl(b.transfer_in,0)-nvl(b.transfer_out,0)-nvl(c.commited_amt,0) balance,
112 nvl(c.utilized_amt,0) utilized,
113 nvl(c.planned,0) planned,
114 nvl(c.paid,0) paid
115 FROM bim_r_fund_daily_facts b ,
116 (SELECT
117 c1.fund_id fund_id,
118 c1.week_date week_date,
119 SUM(nvl(fdsp.commited_amt,0)) commited_amt,
120 SUM(nvl(fdsp.standard_discount,0)+nvl(fdsp.market_expense,0)+nvl(fdsp.accrual,0)) utilized_amt,
121 SUM(nvl(fdsp.planned_amt,0)) planned,
122 SUM(nvl(fdsp.paid_amt,0)) paid
123 FROM bim_r_fdsp_daily_facts fdsp,
124 (SELECT fund_id fund_id,
125 MIN(transaction_create_date) week_date
126 FROM bim_r_fund_daily_facts
127 GROUP BY fund_id) c1
128 WHERE fdsp.fund_id(+) = c1.fund_id
129 AND fdsp.transaction_create_date(+) = c1.week_date
130 GROUP BY c1.fund_id, c1.week_date ) c
131 WHERE b.fund_id = c.fund_id
132 AND c.week_date = b.transaction_create_date;
133
134 l_status VARCHAR2(5);
135 l_industry VARCHAR2(5);
136 l_schema VARCHAR2(30);
137 l_return BOOLEAN;
138
139 BEGIN
140 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
141 begin
142 fnd_message.set_name('BIM','BIM_R_PROC_START');
143 fnd_message.set_token('proc_name', 'UPDATE_BALANCE', FALSE);
144 fnd_file.put_line(fnd_file.log,fnd_message.get);
145 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_daily_facts noparallel';
146 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_daily_facts noparallel';
147 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_balance noparallel';
148 exception
149 WHEN OTHERS THEN
150 ams_utility_pvt.write_conc_log('error alter tablebalance:'||sqlerrm(sqlcode));
151 --dbms_output.put_line('error alter table balance'||sqlerrm(sqlcode));
152 END;
153
154 EXECUTE IMMEDIATE 'truncate table '||l_schema||'.bim_r_fund_balance';
155 --dbms_output.put_line('inside update balance');
156
157 --Insert all the fund_id, and transaction date to start with
158 INSERT into BIM_R_FUND_BALANCE (fund_id, week_date,fis_month, fis_qtr, fis_year)
159 SELECT fund_id,transaction_create_date,fis_month, fis_qtr, fis_year
160 FROM bim_r_fund_daily_facts;
161
162
163 select i.index_tablespace into l_index_tablespace
164 from fnd_product_installations i, fnd_application a
165 where a.application_short_name = 'BIM'
166 and a.application_id = i.application_id;
167
168 BEGIN
169 EXECUTE IMMEDIATE 'drop index bim_r_fund_facts_n6';
170 EXCEPTION
171 WHEN OTHERS THEN
172 null;
173 END;
174
175 BEGIN
176 EXECUTE IMMEDIATE
177 'create index bim_r_fund_facts_n6 on bim_r_fund_daily_facts
178 (fund_id,
179 transaction_create_date) tablespace '||l_index_tablespace||' compute statistics';
180 EXCEPTION
181 WHEN OTHERS THEN
182 null;
183 END;
184
185 BEGIN
186 EXECUTE IMMEDIATE 'drop index bim_r_fdsp_facts_n6';
187 EXCEPTION
188 WHEN OTHERS THEN
189 null;
190 END;
191
192 BEGIN
193 EXECUTE IMMEDIATE
194 'create index bim_r_fdsp_facts_n6 on bim_r_fdsp_daily_facts
195 (fund_id,
196 transaction_create_date) tablespace '||l_index_tablespace||' compute statistics';
197 EXCEPTION
198 WHEN OTHERS THEN
199 null;
200 END;
201
202 BEGIN
203 EXECUTE IMMEDIATE 'drop index bim_r_fund_balance_n6';
204 EXCEPTION
205 WHEN OTHERS THEN
206 null;
207 END;
208
209 BEGIN
210 EXECUTE IMMEDIATE 'create index bim_r_fund_balance_n6 on
211 bim_r_fund_balance(fund_id,week_date) tablespace '||l_index_tablespace||' compute statistics';
212 EXCEPTION
213 WHEN OTHERS THEN
214 null;
215 END;
216
217 BEGIN
218 DBMS_STATS.gather_table_stats('BIM','BIM_R_FUND_BALANCE', estimate_percent => 5,
219 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
220 END;
221
222 --Update the record of mininum date for each fund
223 For l in min_date LOOP
224 BEGIN
225 UPDATE BIM_R_FUND_BALANCE a
226 SET current_available = l.available,
227 past_available = l.original,
228 commited =l.commited,
229 commited_sum=l.commited_sum,
230 current_balance =l.balance,
231 utilized=l.utilized,
232 planned = l.planned,
233 paid = l.paid
234 WHERE a.fund_id = l.fund_id
235 AND a.week_date = l.week_date;
236
237 EXCEPTION
238 WHEN OTHERS THEN
239 ams_utility_pvt.write_conc_log('error updateing minimum balance'||sqlerrm(sqlcode));
240 --dbms_output.put_line('error updateing minimum balance'||sqlerrm(sqlcode));
241 END;
242 END LOOP;
243
244 /* Update in loop: for each fund, each date, populate the balance,
245 commited, utilized, planned, paid */
246 FOR x in balance_cur LOOP
247 BEGIN
248 UPDATE bim_r_fund_balance fb
249 SET (past_available,current_available,commited,commited_sum,
250 current_balance, utilized,planned, paid)
251 =(
252 select /*+ use_nl(maxdate ba) */
253 nvl(ba.current_available,0),
254 nvl(ba.current_available,0)+nvl(a.transfer_in,0) - nvl(a.transfer_out,0),
255 nvl(fdsp.commited_amt,0),
256 nvl(ba.commited_sum,0) + nvl(fdsp.commited_amt,0),
257 nvl(ba.current_available,0)+nvl(a.transfer_in,0) - nvl(a.transfer_out,0) -nvl(ba.commited_sum,0)-nvl(fdsp.commited_amt,0),
258 nvl(fdsp.utilized_amt,0),
259 nvl(fdsp.planned, 0),
260 nvl(fdsp.paid,0)
261 from (select max(week_date) max_date
262 from bim_r_fund_balance
263 where fund_id = x.fund_id
264 and week_date < x.week_date) maxdate,
265 bim_r_fund_balance ba,
266 bim_r_fund_daily_facts a,
267 (select fund_id fund_id,
268 SUM(nvl(commited_amt,0)) commited_amt,
269 SUM(nvl(planned_amt,0)) planned,
270 SUM(nvl(paid_amt,0)) paid,
271 SUM(nvl(standard_discount,0)+nvl(market_expense,0)+nvl(accrual,0)) utilized_amt,
272 transaction_create_date week_date
273 from bim_r_fdsp_daily_facts
274 WHERE fund_id = x.fund_id
275 AND transaction_create_date = x.week_date
276 group by fund_id, transaction_create_date) fdsp
277 where a.fund_id =x.fund_id
278 and a.transaction_create_date = x.week_date
279 and fdsp.fund_id(+) = a.fund_id
280 and fdsp.week_date(+)=a.transaction_create_date
281 and ba.week_date =maxdate.max_date
282 and ba.fund_id = x.fund_id
283 and ba.fund_id = a.fund_id
284 )
285 where fb.fund_id = x.fund_id
286 and fb.week_date = x.week_date ;
287 EXCEPTION
288 WHEN OTHERS THEN
289 ams_utility_pvt.write_conc_log('error updateing balance in loop'||sqlerrm(sqlcode));
290 --dbms_output.put_line('error updateing balance in loop'||sqlerrm(sqlcode));
291 END;
292 END LOOP;
293 fnd_message.set_name('BIM','BIM_R_PROC_END');
294 fnd_message.set_token('proc_name', 'UPDATE_BALANCE', FALSE);
295 fnd_file.put_line(fnd_file.log,fnd_message.get);
296 EXCEPTION
297 WHEN OTHERS THEN
298 ams_utility_pvt.write_conc_log('error updateing balance'||sqlerrm(sqlcode));
299 --dbms_output.put_line('error updateing balance'||sqlerrm(sqlcode));
300 --x_return_status := FND_API.G_RET_STS_ERROR;
301 --FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
302 --FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
303 --FND_MSG_PUB.Add;
304 END update_balance;
305
306 ---------------------------------------------------------------------
307 -- PROCEDURE
308 -- update_sub_balance
309 -- NOTE: This procedure will be called every time when FUND_SUB_LOAD
310 -- get executed. It inserts more data into bim_r_fund_balance.
311 -- It includes the new balance for existing fund, as well as for
312 -- new funds, which didn't have entry in bim_r_fund_balance.
313 ---------------------------------------------------------------------
314 PROCEDURE update_sub_balance(p_start_date DATE, p_end_date DATE)
315 IS
316 l_oldid NUMBER:=0;
317 l_index_tablespace varchar2(100);
318
319 /* cursor fund_cur is to get the new funds, and the transaction
320 date which don't have entry in bim_r_fund_balance */
321 CURSOR fund_cur IS
322 SELECT distinct w.fund_id, w.transaction_create_date
323 FROM bim_r_fund_daily_facts w
324 WHERE not exists( select 1
325 from bim_r_fund_balance b
326 where b.fund_id = w.fund_id
327 and b.week_date = w.transaction_create_date)
328 order by w.fund_id, w.transaction_create_date;
329
330 /* cursor fund_cur_delta is to get the exsiting funds, and its maximum
331 date entry in bim_r_fund_balance, so that new balance will be calcuated
332 based on it. */
333 CURSOR fund_cur_delta(p_date DATE) IS
334 SELECT distinct fund_id fund_id, max(week_date) mdate
335 FROM bim_r_fund_balance
336 WHERE week_date<p_date
337 GROUP BY fund_id;
338
339 l_status VARCHAR2(5);
340 l_industry VARCHAR2(5);
341 l_schema VARCHAR2(30);
342 l_return BOOLEAN;
343
344 BEGIN
345 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
346 fnd_message.set_name('BIM','BIM_R_PROC_START');
347 fnd_message.set_token('proc_name', 'UPDATE_SUB_BALANCE', FALSE);
348 fnd_file.put_line(fnd_file.log,fnd_message.get);
349 BEGIN
350 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_daily_facts noparallel';
351 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_daily_facts noparallel';
352 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_balance noparallel';
353 EXCEPTION
354 WHEN OTHERS THEN
355 FND_FILE.put_line(fnd_file.log,'error alter table in balance'||sqlerrm(sqlcode));
356 END;
357
358 SELECT i.index_tablespace into l_index_tablespace
359 FROM fnd_product_installations i, fnd_application a
360 WHERE a.application_short_name = 'BIM'
361 AND a.application_id = i.application_id;
362
363 BEGIN
364 EXECUTE IMMEDIATE 'drop index bim_r_fund_facts_n6';
365 EXCEPTION
366 WHEN OTHERS THEN
367 null;
368 END;
369
370 BEGIN
371 EXECUTE IMMEDIATE
372 'create index bim_r_fund_facts_n6 on bim_r_fund_daily_facts
373 (fund_id,
374 transaction_create_date) tablespace '||l_index_tablespace||' compute statistics';
375 EXCEPTION
376 WHEN OTHERS THEN
377 null;
378 END;
379
380 BEGIN
381 EXECUTE IMMEDIATE 'drop index bim_r_fdsp_facts_n6';
382 EXCEPTION
383 WHEN OTHERS THEN
384 null;
385 END;
386
387 BEGIN
388 EXECUTE IMMEDIATE
389 'create index bim_r_fdsp_facts_n6 on bim_r_fdsp_daily_facts
390 (fund_id,
391 transaction_create_date) tablespace '||l_index_tablespace||' compute statistics';
392 EXCEPTION
393 WHEN OTHERS THEN
394 null;
395 END;
396
397 BEGIN
398 EXECUTE IMMEDIATE 'drop index bim_r_fund_balance_n6';
399 EXCEPTION
400 WHEN OTHERS THEN
401 null;
402 END;
403
404 BEGIN
405 EXECUTE IMMEDIATE 'create index bim_r_fund_balance_n6 on
406 bim_r_fund_balance(fund_id,week_date) tablespace '||l_index_tablespace||' compute statistics';
407 EXCEPTION
408 WHEN OTHERS THEN
409 null;
410 END;
411
412 --populate balance for existing funds.
413 BEGIN
414 FOR x in fund_cur_delta(p_start_date) LOOP
415 INSERT INTO bim_r_fund_balance(
416 FUND_ID
417 , WEEK_DATE
418 , CURRENT_AVAILABLE
419 , PAST_AVAILABLE
420 , COMMITED
421 , COMMITED_SUM
422 , CURRENT_BALANCE
423 , UTILIZED
424 , PLANNED
425 , PAID
426 , FIS_MONTH
427 , FIS_QTR
428 , FIS_YEAR)
429 SELECT x.fund_id,
430 a.transaction_create_date,
431 nvl(ba.current_available,0)+nvl(a.transfer_in,0) - nvl(a.transfer_out, 0) ,
432 nvl(ba.current_available, 0),
433 nvl(fdsp.commited_amt,0),
434 nvl(ba.commited_sum,0) + nvl(fdsp.commited_amt, 0),
435 nvl(ba.current_available,0)+nvl(a.transfer_in,0) - nvl(a.transfer_out,0) -nvl(ba.commited_sum,0)-nvl(fdsp.
436 commited_amt, 0),
437 nvl(fdsp.utilized,0),
438 nvl(fdsp.planned,0),
439 nvl(fdsp.paid,0),
440 a.fis_month,
441 a.fis_qtr,
442 a.fis_year
443 FROM bim_r_fund_balance ba,
444 bim_r_fund_daily_facts a,
445 (SELECT fund_id fund_id,
446 SUM(nvl(commited_amt,0)) commited_amt,
447 SUM(nvl(standard_discount,0)+nvl(accrual,0)+
448 nvl(market_expense,0)) utilized,
449 SUM(nvl(planned_amt,0)) planned,
450 SUM(nvl(paid_amt,0)) paid,
451 transaction_create_date week_date
452 FROM bim_r_fdsp_daily_facts
453 WHERE fund_id = x.fund_id
454 AND transaction_create_date between p_start_date and p_end_date+0.99999
455 GROUP BY fund_id, transaction_create_date) fdsp
456 WHERE a.fund_id =x.fund_id
457 and a.transaction_create_date between p_start_date and p_end_date+0.99999
458 and fdsp.fund_id(+) = a.fund_id
459 and fdsp.week_date(+)=a.transaction_create_date
460 and ba.week_date =x.mdate
461 and ba.fund_id = a.fund_id;
462 END LOOP;
463 EXCEPTION
464 WHEN OTHERS THEN
465 FND_FILE.put_line(fnd_file.log,'error updateing delta balance'||sqlerrm(sqlcode));
466 END;
467
468 --Insert new funds which don't have entries in bim_r_fund_balance.
469 BEGIN
470 FOR x in fund_cur LOOP
471 -- This is to populate the initial balance for each fund.
472 IF (x.fund_id <>l_oldid) THEN
473 INSERT INTO bim_r_fund_balance(
474 FUND_ID
475 , WEEK_DATE
476 , CURRENT_AVAILABLE
477 , PAST_AVAILABLE
478 , COMMITED
479 , COMMITED_SUM
480 , CURRENT_BALANCE
481 , UTILIZED
482 , PLANNED
483 , PAID
484 , FIS_MONTH
485 , FIS_QTR
486 , FIS_YEAR)
487 SELECT x.fund_id,
488 x.transaction_create_date,
489 nvl(a.original_budget,0)+nvl(a.transfer_in,0) - nvl(a.transfer_out,0),
490 nvl(a.original_budget,0),
491 nvl(fdsp.commited_amt,0),
492 nvl(fdsp.commited_amt,0),
493 nvl(a.original_budget,0)+nvl(a.transfer_in,0)-nvl(a.transfer_out,0)-nvl(fdsp.commited_amt,0),
494 nvl(fdsp.utilized,0),
495 nvl(fdsp.planned,0),
496 nvl(fdsp.paid,0),
497 a.fis_month,
498 a.fis_qtr,
499 a.fis_year
500 FROM bim_r_fund_daily_facts a,
501 (SELECT fund_id fund_id,
502 SUM(nvl(commited_amt,0)) commited_amt,
503 SUM(nvl(standard_discount,0)+nvl(accrual,0)+
504 nvl(market_expense,0)) utilized,
505 SUM(nvl(planned_amt,0)) planned,
506 SUM(nvl(paid_amt,0)) paid,
507 transaction_create_date week_date
508 FROM bim_r_fdsp_daily_facts
509 WHERE fund_id = x.fund_id
510 AND transaction_create_date = x.transaction_create_date
511 GROUP BY fund_id, transaction_create_date) fdsp
512 WHERE a.fund_id =x.fund_id
513 AND a.transaction_create_date = x.transaction_create_date
514 AND fdsp.fund_id(+) = a.fund_id
515 AND fdsp.week_date(+)=a.transaction_create_date;
516 ELSE --to populate the balance for subsequent dates.
517 INSERT INTO bim_r_fund_balance(
518 FUND_ID
519 , WEEK_DATE
520 , PAST_AVAILABLE
521 , CURRENT_AVAILABLE
522 , COMMITED
523 , COMMITED_SUM
524 , CURRENT_BALANCE
525 , UTILIZED
526 , PLANNED
527 , PAID
528 , FIS_MONTH
529 , FIS_QTR
530 , FIS_YEAR)
531 SELECT x.fund_id,
532 x.transaction_create_date,
533 nvl(ba.current_available,0),
534 nvl(ba.current_available,0)+nvl(a.transfer_in,0) - nvl(a.transfer_out,0),
535 nvl(fdsp.commited_amt,0),
536 nvl(ba.commited_sum,0) + nvl(fdsp.commited_amt,0),
537 nvl(ba.current_available,0)+nvl(a.transfer_in,0) - nvl(a.transfer_out,0) - nvl(ba.commited_sum,0)-nvl( fdsp.commited_amt,0),
538 nvl(fdsp.utilized,0),
539 nvl(fdsp.planned,0),
540 nvl(fdsp.paid,0),
541 a.fis_month,
542 a.fis_qtr,
543 a.fis_year
544 FROM (select max(week_date) max_date
545 from bim_r_fund_balance
546 where fund_id = x.fund_id
547 and week_date < x.transaction_create_date) maxdate,
548 bim_r_fund_balance ba,
549 bim_r_fund_daily_facts a,
550 (select fund_id fund_id,
551 sum(nvl(commited_amt,0)) commited_amt,
552 SUM(nvl(standard_discount,0)+nvl(accrual,0)+
553 nvl(market_expense,0)) utilized,
554 SUM(nvl(planned_amt,0)) planned,
555 SUM(nvl(paid_amt,0)) paid,
556 transaction_create_date week_date
557 from bim_r_fdsp_daily_facts
558 where fund_id = x.fund_id
559 and transaction_create_date = x.transaction_create_date
560 group by fund_id, transaction_create_date) fdsp
561 WHERE a.fund_id =x.fund_id
562 and a.transaction_create_date = x.transaction_create_date
563 and fdsp.fund_id(+) = a.fund_id
564 and fdsp.week_date(+)=a.transaction_create_date
565 and ba.week_date =maxdate.max_date
566 and ba.fund_id = x.fund_id;
567 END IF;
568 l_oldid :=x.fund_id;
569 END LOOP;
570 fnd_message.set_name('BIM','BIM_R_PROC_END');
571 fnd_message.set_token('proc_name', 'UPDATE_SUB_BALANCE', FALSE);
572 fnd_file.put_line(fnd_file.log,fnd_message.get);
573 EXCEPTION
574 WHEN OTHERS THEN
575 FND_FILE.put_line(fnd_file.log,'error updateing new balance'||sqlerrm(sqlcode));
576 END;
577 END update_sub_balance;
578
579 ---------------------------------------------------------------------
580 -- PROCEDURE
581 -- POPULATE
582 -- NOTE This procedure can be called externally, ie: from concurrent
583 -- manager. Depending on the parameter, and bim_rep_history data,
584 -- FUND_FIRST_LOAD or FUND_SUB_LOAD will be called accordingly.
585 ---------------------------------------------------------------------
586 PROCEDURE POPULATE
587 (
588 p_api_version_number IN NUMBER ,
589 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
590 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
591 p_commit IN VARCHAR2 := FND_API.G_FALSE,
592 x_msg_count OUT NOCOPY NUMBER ,
593 x_msg_data OUT NOCOPY VARCHAR2 ,
594 x_return_status OUT NOCOPY VARCHAR2 ,
595 p_object IN VARCHAR2 ,
596 p_start_date IN DATE ,
597 p_end_date IN DATE ,
598 p_para_num IN NUMBER
599 ) IS
600 v_error_code NUMBER;
601 v_error_text VARCHAR2(1500);
602 l_last_update_date DATE;
603 l_end_date DATE;
604 l_user_id NUMBER := FND_GLOBAL.USER_ID();
605 l_api_version_number CONSTANT NUMBER := 1.0;
606 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE';
607 l_date DATE;
608 l_sdate DATE :=to_date('01/01/1950', 'DD/MM/YYYY') ;
609
610 -- The maximum end date of the object being populated.
611 CURSOR last_update_history IS
612 SELECT MAX(end_date)
613 FROM bim_rep_history
614 WHERE object = p_object;
615
616
617
618 l_status VARCHAR2(5);
619 l_industry VARCHAR2(5);
620 l_schema VARCHAR2(30);
621 l_return BOOLEAN;
622
623 BEGIN
624 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
625
626 -- Standard call to check for call compatibility.
627 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
628 p_api_version_number,
629 l_api_name,
630 G_PKG_NAME)
631 THEN
632 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
633 END IF;
634
635 -- Initialize message list IF p_init_msg_list IS set to TRUE.
636 IF FND_API.to_Boolean( p_init_msg_list )
637 THEN
638 FND_MSG_PUB.initialize;
639 END IF;
640
641 -- Debug Message
642 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
643
644 -- Initialize API RETURN status to SUCCESS
645 x_return_status := FND_API.G_RET_STS_SUCCESS;
646
647 fnd_message.set_name('BIM','BIM_R_START_FACTS');
648 fnd_message.set_token('p_object', 'Fund', FALSE);
649 fnd_file.put_line(fnd_file.log,fnd_message.get);
650 -- Always make sure data be populated up to sysdate-1
651 IF (trunc(p_end_date) =trunc(sysdate)) THEN
652 l_end_date :=trunc(sysdate-1);
653 ELSE
654 l_end_date :=p_end_date;
655 END IF;
656
657 -- test GL period
658 DECLARE
659 l_start_date DATE;
660 BEGIN
661
662 SELECT start_date
663 INTO l_start_date
664 FROM gl_periods
665 WHERE start_date <
666 (select nvl(min(start_date_active),sysdate)
667 from ozf_funds_all_b
668 )
669 AND rownum < 2;
670
671 IF (l_start_date IS NULL) THEN
672 fnd_message.set_name('BIM','BIM_R_GL_PERIODS');
673 fnd_file.put_line(fnd_file.log,fnd_message.get);
674 END IF ;
675 EXCEPTION
676 WHEN OTHERS THEN
677 fnd_message.set_name('BIM','BIM_R_GL_PERIODS');
678 fnd_file.put_line(fnd_file.log,fnd_message.get);
679 END;
680
681
682 OPEN last_update_history;
683 FETCH last_update_history INTO l_last_update_date;
684 CLOSE last_update_history;
685
686 --Logic check.When load subsequently, p_start_date should be null.
687 IF (l_last_update_date IS NOT NULL AND p_start_date IS NOT NULL) THEN
688
689 fnd_message.set_name('BIM','BIM_R_FIRST_LOAD');
690 fnd_message.set_token('end_date', l_last_update_date, FALSE);
691 fnd_file.put_line(fnd_file.log,fnd_message.get);
692 RAISE FND_API.G_EXC_ERROR;
693 END IF;
694
695 IF p_start_date IS NOT NULL THEN
696 IF (p_start_date >= p_end_date) THEN
697 fnd_message.set_name('BIM',' BIM_R_DATE_VALIDATION');
698 fnd_file.put_line(fnd_file.log,fnd_message.get) ;
699 RAISE FND_API.G_EXC_ERROR;
700 END IF;
701
702 FUND_FIRST_LOAD(p_start_datel =>TRUNC(p_start_date)
703 ,p_end_datel => TRUNC(l_end_date)
704 ,p_para_num =>p_para_num
705 ,x_msg_count => x_msg_count
706 ,x_msg_data => x_msg_data
707 ,x_return_status => x_return_status);
708 IF x_return_status = FND_API.g_ret_sts_error THEN
709 RAISE FND_API.g_exc_error;
710 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
711 RAISE FND_API.g_exc_unexpected_error;
712 END IF;
713 ELSE
714 IF l_last_update_date IS NOT NULL THEN
715 IF (p_end_date <= l_last_update_date) THEN
716 ams_utility_pvt.write_conc_log('The current end date cannot be less than the last end date ');
717 RAISE FND_API.g_exc_error;
718 END IF;
719 -- dbms_output.put_line('in sub load');
720
721 --Load fund subsequently
722 FUND_SUB_LOAD(p_start_datel => TRUNC(l_last_update_date + 1)
723 ,p_end_datel => TRUNC(l_end_date)
724 ,p_para_num => p_para_num
725 ,x_msg_count => x_msg_count
726 ,x_msg_data => x_msg_data
727 ,x_return_status => x_return_status);
728 END IF;
729 IF x_return_status = FND_API.g_ret_sts_error THEN
730 RAISE FND_API.g_exc_error;
731 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
732 RAISE FND_API.g_exc_unexpected_error;
733 END IF;
734 END IF;
735
736 --Standard check of commit
737 IF FND_API.To_Boolean (p_commit) THEN
738 COMMIT WORK;
739 END IF;
740
741 -- Standard call to get message count and IF count IS 1, get message info.
742 FND_msg_PUB.Count_And_Get
743 (p_count => x_msg_count,
744 p_data => x_msg_data
745 );
746 fnd_message.set_name('BIM','BIM_R_END_FACTS');
747 fnd_message.set_token('object_name', 'Fund', FALSE);
748 fnd_file.put_line(fnd_file.log,fnd_message.get);
749 EXCEPTION
750 WHEN FND_API.G_EXC_ERROR THEN
751 x_return_status := FND_API.G_RET_STS_ERROR;
752 -- Standard call to get message count and IF count=1, get the message
753 FND_msg_PUB.Count_And_Get (
754 -- p_encoded => FND_API.G_FALSE,
755 p_count => x_msg_count,
756 p_data => x_msg_data );
757 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
758 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
759 -- Standard call to get message count and IF count=1, get the message
760 FND_msg_PUB.Count_And_Get (
761 --p_encoded => FND_API.G_FALSE,
762 p_count => x_msg_count,
763 p_data => x_msg_data
764 );
765
766 WHEN OTHERS THEN
767 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
768 IF FND_msg_PUB.Check_msg_Level ( FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
769 THEN
770 FND_msg_PUB.Add_Exc_msg( G_PKG_NAME,l_api_name);
771 END IF;
772 -- Standard call to get message count and IF count=1, get the message
773 FND_msg_PUB.Count_And_Get (
774 -- p_encoded => FND_API.G_FALSE,
775 p_count => x_msg_count,
776 p_data => x_msg_data
777 );
778 END POPULATE;
779 -----------------------------------------------------------------------
780 -- PROCEDURE
781 -- LOG_HISTORY
782 --
783 -- Note
784 -- Insert history data for each load: first time or subsequent mode.
785 --------------------------------------------------------------------------
786 PROCEDURE LOG_HISTORY(
787 p_object VARCHAR2 ,
788 p_start_time DATE ,
789 p_end_time DATE ,
790 x_msg_count OUT NOCOPY NUMBER ,
791 x_msg_data OUT NOCOPY VARCHAR2 ,
792 x_return_status OUT NOCOPY VARCHAR2
793 )
794 IS
795 l_user_id NUMBER := FND_GLOBAL.USER_ID();
796 l_table_name VARCHAR2(100):='bim_rep_history';
797 BEGIN
798 INSERT INTO
799 bim_rep_history
800 (creation_date,
801 last_update_date,
802 created_by,
803 last_updated_by,
804 object_last_updated_date,
805 object,
806 start_date,
807 end_date)
808 VALUES
809 (sysdate,
810 sysdate,
811 l_user_id,
812 l_user_id,
813 sysdate,
814 p_object,
815 p_start_time,
816 p_end_time);
817 EXCEPTION
818 WHEN OTHERS THEN
819 x_return_status := FND_API.G_RET_STS_ERROR;
820 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
821 FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
822 FND_MSG_PUB.Add;
823 fnd_file.put_line(fnd_file.log,fnd_message.get);
824 END LOG_HISTORY;
825
826 ---------------------------------------------------------------------
827 -- PROCEDURE
828 -- FUND_FIRST_LOAD
829 -- NOTE This procedure will be executed when load data for first time.
830 -- Transactions for funds whose active date during p_start_date
831 -- and p_end_date will be inserted into bim_r_fund_daily_facts,
832 -- and transactions happened before fund active data are also captured.
833 ---------------------------------------------------------------------
834 PROCEDURE FUND_FIRST_LOAD(
835 p_start_datel DATE,
836 p_end_datel DATE,
837 p_para_num NUMBER ,
838 x_msg_count OUT NOCOPY NUMBER ,
839 x_msg_data OUT NOCOPY VARCHAR2 ,
840 x_return_status OUT NOCOPY VARCHAR2
841 )
842 IS
843 l_user_id NUMBER := FND_GLOBAL.USER_ID();
844 l_min_date DATE;
845 l_last_update_date DATE;
846 l_success VARCHAR2(1) := 'F';
847 l_api_version_number CONSTANT NUMBER := 1.0;
848 l_api_name CONSTANT VARCHAR2(30) := 'FUND_FIRST_LOAD';
849 l_table_name VARCHAR2(100);
850 l_def_tablespace VARCHAR2(100);
851 l_index_tablespace VARCHAR2(100);
852 l_oracle_username VARCHAR2(100);
853 l_count NUMBER:=0;
854 l_old_index VARCHAR2(100);
855 l_col_num VARCHAR2(100);
856
857 TYPE generic_number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
858
859 TYPE generic_char_table IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
860
861 l_pct_free generic_number_table;
862 l_ini_trans generic_number_table;
863 l_max_trans generic_number_table;
864 l_initial_extent generic_number_table;
865 l_next_extent generic_number_table;
866 l_min_extents generic_number_table;
867 l_max_extents generic_number_table;
868 l_pct_increase generic_number_table;
869
870 l_owner generic_char_table;
871 l_index_name generic_char_table;
872 l_ind_column_name generic_char_table;
873 l_index_table_name generic_char_table;
874 i NUMBER;
875
876 l_status VARCHAR2(30);
877 l_industry VARCHAR2(30);
878 l_orcl_schema VARCHAR2(30);
879 l_bol BOOLEAN := fnd_installation.get_app_info ('BIM',l_status,l_industry,l_orcl_schema);
880
881
882 -- Get the original tablespace, index space for application ID:'BIM'
883 CURSOR get_ts_name IS
884 SELECT i.tablespace, i.index_tablespace, u.oracle_username
885 FROM fnd_product_installations i, fnd_application a, fnd_oracle_userid u
886 WHERE a.application_short_name = 'BIM'
887 AND a.application_id = i.application_id
888 AND u.oracle_id = i.oracle_id;
889
890 -- Get all the index defination
891 CURSOR get_index_params (l_schema VARCHAR2) IS
892 SELECT a.owner,a.index_name,b.table_name,b.column_name,pct_free,ini_trans,max_trans
893 ,initial_extent,next_extent,min_extents,
894 max_extents, pct_increase
895 FROM all_indexes a, all_ind_columns b
896 WHERE a.index_name = b.index_name
897 AND a.owner = l_schema
898 AND a.owner = b.index_owner
899 AND (a.index_name like 'BIM_R_FUND%_FACTS%'
900 OR a.index_name like 'BIM_R_FDSP%_FACTS%')
901 ORDER BY a.index_name;
902
903
904
905 l_schema VARCHAR2(30);
906 l_return BOOLEAN;
907
908 BEGIN
909 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
910
911 -- Standard call to check for call compatibility.
912 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
913 l_api_version_number,
914 l_api_name,
915 G_PKG_NAME)
916 THEN
917 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
918 END IF;
919
920 -- Debug Message
921 --AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
922
923 -- Initialize API RETURN status to SUCCESS
924 x_return_status := FND_API.G_RET_STS_SUCCESS;
925
926 /* In order to speed up insertion. We drop all the index before inserting, and
927 create them back after inserting. But we want to keep the parameters(tablespace,
928 indexspace etc) they were created before. */
929 OPEN get_ts_name;
930 FETCH get_ts_name INTO l_def_tablespace, l_index_tablespace, l_oracle_username;
931 CLOSE get_ts_name;
932
933 fnd_message.set_name('BIM','BIM_R_DROP_INDEXES');
934 fnd_file.put_line(fnd_file.log,fnd_message.get);
935 /* Piece of Code for retrieving,storing storage parameters and Dropping the indexes */
936 BEGIN
937 i := 1;
938 l_old_index :='N';
939 FOR x in get_index_params(l_orcl_schema) LOOP
940
941 l_pct_free(i) := x.pct_free;
942 l_ini_trans(i) := x.ini_trans;
943 l_max_trans(i) := x.max_trans;
944 l_initial_extent(i) := x.initial_extent;
945 l_next_extent(i) := x.next_extent;
946 l_min_extents(i) := x.min_extents;
947 l_max_extents(i) := x.max_extents;
948 l_pct_increase(i) := x.pct_increase;
949
950 l_owner(i) :=x.owner;
951 l_index_name(i) :=x.index_name;
952 l_index_table_name(i) := x.table_name;
953 l_ind_column_name(i) := x.column_name;
954
955 --dbms_output.put_line('l_index:'||l_index_name(i)||' '||'i:'||i||'l_old:'|| l_old_index);
956
957 IF l_index_name(i)<>l_old_index THEN --
958 EXECUTE IMMEDIATE 'DROP INDEX '|| l_owner(i) || '.'|| l_index_name(i) ;
959 -- dbms_output.put_line('dropping:'||i||' '||l_owner(i) || '.'|| l_index_name(i));
960 END IF;
961 l_old_index:= l_index_name(i);
962 i := i + 1;
963 END LOOP;
964 EXCEPTION
965 WHEN others THEN
966 --dbms_output.put_line('error dropping index:'||sqlerrm(sqlcode));
967 FND_FILE.put_line(fnd_file.log,'error dropping index'||sqlerrm(sqlcode));
968 x_return_status := FND_API.G_RET_STS_ERROR;
969 END;
970
971 BEGIN
972
973 --for performance reasons
974 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_daily_facts nologging';
975 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_weekly_facts nologging';
976 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_daily_facts nologging';
977 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_weekly_facts nologging';
978 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 1000';
979
980 l_table_name :='bim_r_fund_daily_facts';
981 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
982 fnd_message.set_token('TABLE_NAME','bim_r_fund_daily_facts', FALSE);
983 fnd_file.put_line(fnd_file.log,fnd_message.get);
984 /* First insert: Insert all the transactions: transfer in/out for funds whose active date between p_start_date and p_end_date */
985 INSERT /*+ append parallel(fdf,p_para_num) */
986 INTO bim_r_fund_daily_facts fdf(
987 fund_transaction_id
988 ,creation_date
989 ,last_update_date
990 ,created_by
991 ,last_updated_by
992 ,last_update_login
993 ,fund_id
994 ,parent_fund_id
995 ,fund_number
996 ,start_date
997 ,end_date
998 ,start_period
999 ,end_period
1000 ,set_of_books_id
1001 ,fund_type
1002 --,region
1003 ,country
1004 ,org_id
1005 ,category_id
1006 ,status
1007 ,original_budget
1008 ,transfer_in
1009 ,transfer_out
1010 ,holdback_amt
1011 ,currency_code_fc
1012 ,delete_flag
1013 ,transaction_create_date
1014 ,load_date
1015 ,fis_month
1016 ,fis_qtr
1017 ,fis_year
1018 ,business_unit_id)
1019 SELECT /*+ parallel(inner, p_para_num) */
1020 bim_r_fund_daily_facts_s.nextval,
1021 sysdate,
1022 sysdate,
1023 l_user_id,
1024 l_user_id,
1025 l_user_id,
1026 inner.fund_id,
1027 inner.parent_fund_id,
1028 inner.fund_number,
1029 inner.start_date,
1030 inner.end_date,
1031 inner.start_period,
1032 inner.end_period,
1033 inner.set_of_books_id,
1034 inner.fund_type,
1035 --inner.region,
1036 inner.country,
1037 inner.org_id,
1038 inner.category_id,
1039 inner.status,
1040 inner.original_budget,
1041 inner.transfer_in,
1042 inner.transfer_out,
1043 inner.holdback,
1044 inner.currency_code_fc,
1045 'N',
1046 inner.transaction_create_date,
1047 inner.weekend_date,
1048 BIM_SET_OF_BOOKS.get_fiscal_month(inner.transaction_create_date,204),
1049 BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.transaction_create_date,204),
1050 BIM_SET_OF_BOOKS.get_fiscal_year(inner.transaction_create_date,204),
1051 inner.business_unit_id
1052 FROM (
1053 SELECT fund_id fund_id,
1054 fund_number fund_number,
1055 start_date start_date,
1056 end_date end_date,
1057 start_period start_period,
1058 end_period end_period,
1059 category_id category_id,
1060 status status,
1061 fund_type fund_type,
1062 parent_fund_id parent_fund_id,
1063 country country,
1064 org_id org_id,
1065 business_unit_id business_unit_id,
1066 set_of_books_id set_of_books_id,
1067 currency_code_fc currency_code_fc,
1068 original_budget original_budget,
1069 transaction_create_date transaction_create_date,
1070 weekend_date weekend_date,
1071 SUM(transfer_in) transfer_in,
1072 SUM(transfer_out) transfer_out,
1073 SUM(holdback) holdback
1074 FROM (
1075 SELECT ad.fund_id fund_id,
1076 ad.fund_number fund_number,
1077 ad.start_date_active start_date,
1078 ad.end_date_active end_date,
1079 ad.start_period_name start_period,
1080 ad.end_period_name end_period,
1081 ad.category_id category_id,
1082 ad.status_code status,
1083 ad.fund_type fund_type,
1084 ad.parent_fund_id parent_fund_id,
1085 ad.country_code country,
1086 ad.org_id org_id,
1087 ad.business_unit_id business_unit_id,
1088 ad.set_of_books_id set_of_books_id,
1089 ad.currency_code_fc currency_code_fc,
1090 ad.original_budget original_budget,
1091 ad.tr_date transaction_create_date,
1092 trunc((decode(decode( to_char(ad.tr_date,'MM') , to_char(next_day(ad.tr_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
1093 ,'TRUE'
1094 ,decode(decode(ad.tr_date , (next_day(ad.tr_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
1095 ,'TRUE'
1096 ,ad.tr_date
1097 ,'FALSE'
1098 ,next_day(ad.tr_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
1099 ,'FALSE'
1100 ,decode(decode(to_char(ad.tr_date,'MM'),to_char(next_day(ad.tr_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
1101 ,'FALSE'
1102 ,last_day(ad.tr_date))))) weekend_date,
1103 nvl(SUM(convert_currency(bu1.approved_in_currency,nvl(bu1.approved_original_amount,0))),0) transfer_in,
1104 0 transfer_out,
1105 0 holdback
1106 FROM (SELECT a.fund_id fund_id,
1107 a.fund_number fund_number,
1108 a.start_date_active start_date_active,
1109 a.end_date_active end_date_active,
1110 a.start_period_name start_period_name,
1111 a.end_period_name end_period_name,
1112 a.category_id category_id,
1113 a.status_code status_code,
1114 a.fund_type fund_type,
1115 a.parent_fund_id parent_fund_id,
1116 a.business_unit_id business_unit_id,
1117 a.country_id country_code,
1118 --b.area2_code area2_code,
1119 a.org_id org_id,
1120 a.set_of_books_id set_of_books_id,
1121 a.currency_code_fc currency_code_fc,
1122 -- decode(trunc(d.trdate),trunc(a.PROGRAM_UPDATE_DATE),a.original_budget,0) original_budget,
1123 a.original_budget original_budget,
1124 trunc(d.trdate) tr_date
1125 FROM ozf_funds_all_b a,
1126 bim_intl_dates d
1127 WHERE a.status_code in ('ACTIVE', 'CANCELLED', 'CLOSED')
1128 AND d.trdate between a.start_date_active and least(nvl(a.end_date_active,sysdate-1),p_end_datel)
1129 AND a.start_date_active between p_start_datel and p_end_datel
1130 GROUP BY a.fund_id,
1131 trunc(d.trdate),
1132 -- trunc(a.PROGRAM_UPDATE_DATE),
1133 a.fund_number,
1134 a.start_date_active,
1135 a.end_date_active,
1136 a.start_period_name,
1137 a.end_period_name,
1138 a.category_id,
1139 a.status_code,
1140 a.fund_type,
1141 a.parent_fund_id,
1142 a.country_id,
1143 a.org_id,
1144 a.business_unit_id,
1145 a.set_of_books_id,
1146 a.currency_code_fc,
1147 a.original_budget
1148 ) ad,
1149 ozf_act_budgets BU1
1150 WHERE bu1.approval_date(+) between ad.tr_date and ad.tr_date + 0.99999
1151 AND bu1.transfer_type in ('TRANSFER','REQUEST')
1152 AND bu1.status_code(+) = 'APPROVED'
1153 AND bu1.arc_act_budget_used_by(+) = 'FUND'
1154 AND bu1.act_budget_used_by_id(+) = ad.fund_id
1155 AND bu1.budget_source_type(+) ='FUND'
1156 GROUP BY ad.fund_id,
1157 ad.tr_date ,
1158 ad.fund_number,
1159 ad.start_date_active ,
1160 ad.end_date_active ,
1161 ad.start_period_name ,
1162 ad.end_period_name ,
1163 ad.category_id ,
1164 ad.status_code ,
1165 ad.fund_type ,
1166 ad.parent_fund_id,
1167 ad.country_code,
1168 ad.business_unit_id,
1169 ad.org_id ,
1170 ad.set_of_books_id ,
1171 ad.currency_code_fc ,
1172 ad.original_budget
1173 UNION ALL
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_code 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 ad.original_budget original_budget,
1190 ad.tr_date transaction_create_date,
1191 trunc((decode(decode( to_char(ad.tr_date,'MM') , to_char(next_day(ad.tr_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
1192 ,'TRUE'
1193 ,decode(decode(ad.tr_date , (next_day(ad.tr_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
1194 ,'TRUE'
1195 ,ad.tr_date
1196 ,'FALSE'
1197 ,next_day(ad.tr_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
1198 ,'FALSE'
1199 ,decode(decode(to_char(ad.tr_date,'MM'),to_char(next_day(ad.tr_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
1200 ,'FALSE'
1201 ,last_day(ad.tr_date))))) weekend_date,
1202 0 transfer_in,
1203 nvl(SUM(decode(bu2.transfer_type,'TRANSFER', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0)
1204 +nvl(SUM(decode(bu2.transfer_type,'REQUEST', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0) transfer_out,
1205 --nvl(SUM(convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0))),0) transfer_out,
1206 nvl(SUM(decode(bu2.transfer_type, 'RESERVE', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0)-
1207 nvl(SUM(decode(bu2.transfer_type, 'RELEASE', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0) holdback
1208 FROM (SELECT a.fund_id fund_id,
1209 a.fund_number fund_number,
1210 a.start_date_active start_date_active,
1211 a.end_date_active end_date_active,
1212 a.start_period_name start_period_name,
1213 a.end_period_name end_period_name,
1214 a.category_id category_id,
1215 a.status_code status_code,
1216 a.fund_type fund_type,
1217 a.parent_fund_id parent_fund_id,
1218 a.country_id country_code,
1219 a.org_id org_id,
1220 a.business_unit_id business_unit_id,
1221 a.set_of_books_id set_of_books_id,
1222 a.currency_code_fc currency_code_fc,
1223 -- decode(trunc(d.trdate),trunc(a.PROGRAM_UPDATE_DATE),a.original_budget,0) original_budget,
1224 a.original_budget original_budget,
1225 trunc(d.trdate) tr_date
1226 FROM ozf_funds_all_b a,
1227 bim_intl_dates d
1228 WHERE a.status_code in ('ACTIVE', 'CANCELLED', 'CLOSED')
1229 AND d.trdate between a.start_date_active and least(nvl(a.end_date_active,sysdate-1),p_end_datel)
1230 AND a.start_date_active between p_start_datel and p_end_datel
1231 GROUP BY a.fund_id,
1232 trunc(d.trdate),
1233 -- trunc(a.PROGRAM_UPDATE_DATE),
1234 a.fund_number,
1235 a.start_date_active,
1236 a.end_date_active,
1237 a.start_period_name,
1238 a.end_period_name,
1239 a.category_id,
1240 a.status_code,
1241 a.fund_type,
1242 a.parent_fund_id,
1243 a.country_id,
1244 a.org_id,
1245 a.business_unit_id,
1246 a.set_of_books_id,
1247 a.currency_code_fc,
1248 a.original_budget
1249 ) ad,
1250 ozf_act_budgets BU2
1251 WHERE bu2.approval_date(+) between ad.tr_date and ad.tr_date + 0.99999
1252 AND bu2.status_code(+) = 'APPROVED'
1253 AND bu2.arc_act_budget_used_by(+) = 'FUND'
1254 AND bu2.budget_source_type(+) ='FUND'
1255 AND bu2.budget_source_id (+)= ad.fund_id
1256 GROUP BY ad.fund_id,
1257 ad.tr_date ,
1258 ad.fund_number,
1259 ad.start_date_active ,
1260 ad.end_date_active ,
1261 ad.start_period_name ,
1262 ad.end_period_name ,
1263 ad.category_id ,
1264 ad.status_code ,
1265 ad.fund_type ,
1266 ad.parent_fund_id,
1267 ad.country_code,
1268 ad.org_id ,
1269 ad.business_unit_id,
1270 ad.set_of_books_id ,
1271 ad.currency_code_fc ,
1272 ad.original_budget)
1273 GROUP BY
1274 fund_id,
1275 transaction_create_date,
1276 weekend_date,
1277 fund_number,
1278 start_date,
1279 end_date,
1280 start_period,
1281 end_period,
1282 category_id,
1283 status,
1284 fund_type,
1285 parent_fund_id,
1286 country,
1287 org_id,
1288 business_unit_id,
1289 set_of_books_id,
1290 currency_code_fc,
1291 original_budget
1292 )inner;
1293 l_count:=l_count+SQL%ROWCOUNT;
1294 EXECUTE IMMEDIATE 'COMMIT';
1295 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 20';
1296 EXCEPTION
1297 WHEN others THEN
1298 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 20';
1299 x_return_status := FND_API.G_RET_STS_ERROR;
1300 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
1301 FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
1302 FND_MSG_PUB.Add;
1303 fnd_file.put_line(fnd_file.log,fnd_message.get);
1304 END;
1305
1306 /*Second insert: more records which transact before active date */
1307 BEGIN
1308 -- dbms_output.put_line('inserting extra');
1309 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
1310 fnd_message.set_token('table_name', 'bim_r_fund_daily_facts', FALSE);
1311 fnd_file.put_line(fnd_file.log,fnd_message.get);
1312 INSERT
1313 INTO bim_r_fund_daily_facts fdf(
1314 fund_transaction_id
1315 ,creation_date
1316 ,last_update_date
1317 ,created_by
1318 ,last_updated_by
1319 ,last_update_login
1320 ,fund_id
1321 ,parent_fund_id
1322 ,fund_number
1323 ,start_date
1324 ,end_date
1325 ,start_period
1326 ,end_period
1327 ,set_of_books_id
1328 ,fund_type
1329 --,region
1330 ,country
1331 ,org_id
1332 ,category_id
1333 ,status
1334 ,original_budget
1335 ,transfer_in
1336 ,transfer_out
1337 ,holdback_amt
1338 ,currency_code_fc
1339 ,delete_flag
1340 ,transaction_create_date
1341 ,load_date
1342 ,fis_month
1343 ,fis_qtr
1344 ,fis_year
1345 ,business_unit_id)
1346 -- ,security_group_id)
1347 SELECT
1348 bim_r_fund_daily_facts_s.nextval,
1349 sysdate,
1350 sysdate,
1351 l_user_id,
1352 l_user_id,
1353 l_user_id,
1354 inner.fund_id,
1355 inner.parent_fund_id,
1356 inner.fund_number,
1357 inner.start_date,
1358 inner.end_date,
1359 inner.start_period,
1360 inner.end_period,
1361 inner.set_of_books_id,
1362 inner.fund_type,
1363 --inner.region,
1364 inner.country,
1365 inner.org_id,
1366 inner.category_id,
1367 inner.status,
1368 inner.original_budget,
1369 inner.transfer_in,
1370 inner.transfer_out,
1371 inner.holdback,
1372 inner.currency_code_fc,
1373 'N',
1374 inner.transaction_create_date,
1375 inner.weekend_date,
1376 BIM_SET_OF_BOOKS.get_fiscal_month(inner.transaction_create_date,204),
1377 BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.transaction_create_date,204),
1378 BIM_SET_OF_BOOKS.get_fiscal_year(inner.transaction_create_date,204),
1379 inner.business_unit_id
1380 FROM (
1381 SELECT transaction_create_date transaction_create_date,
1382 trunc((decode(decode( to_char(transaction_create_date,'MM') , to_char(next_day(transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
1383 ,'TRUE'
1384 ,decode(decode(transaction_create_date , (next_day(transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
1385 ,'TRUE'
1386 ,transaction_create_date
1387 ,'FALSE'
1388 ,next_day(transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
1389 ,'FALSE'
1390 ,decode(decode(to_char(transaction_create_date,'MM'),to_char(next_day(transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
1391 ,'FALSE'
1392 ,last_day(transaction_create_date))))) weekend_date,
1393 SUM(transfer_in) transfer_in,
1394 SUM(transfer_out) transfer_out,
1395 SUM(holdback) holdback,
1396 fund_id ,
1397 fund_number ,
1398 start_date ,
1399 end_date ,
1400 start_period ,
1401 end_period ,
1402 category_id ,
1403 status ,
1404 fund_type ,
1405 parent_fund_id ,
1406 business_unit_id ,
1407 country ,
1408 org_id ,
1409 set_of_books_id ,
1410 currency_code_fc ,
1411 original_budget
1412 FROM (
1413 SELECT trunc(bu1.approval_date) transaction_create_date,
1414 nvl(SUM(convert_currency(bu1.approved_in_currency,nvl(bu1.approved_original_amount,0))),0) transfer_in,
1415 0 transfer_out,
1416 0 holdback,
1417 o.fund_id fund_id,
1418 o.fund_number fund_number,
1419 o.start_date_active start_date,
1420 o.end_date_active end_date,
1421 o.start_period_name start_period,
1422 o.end_period_name end_period,
1423 o.category_id category_id,
1424 o.status_code status,
1425 o.fund_type fund_type,
1426 o.parent_fund_id parent_fund_id,
1427 o.business_unit_id business_unit_id,
1428 o.country_id country,
1429 o.org_id org_id,
1430 o.set_of_books_id set_of_books_id,
1431 o.currency_code_fc currency_code_fc,
1432 o.original_budget original_budget
1433 FROM ozf_funds_all_b o,
1434 ozf_act_budgets BU1
1435 WHERE o.start_date_active between p_start_datel and p_end_datel
1436 AND o.status_code in ('ACTIVE','CANCELLED', 'CLOSED')
1437 AND bu1.transfer_type in ('TRANSFER', 'REQUEST')
1438 AND bu1.approval_date <trunc(o.start_date_active)
1439 AND bu1.status_code(+) = 'APPROVED'
1440 AND bu1.arc_act_budget_used_by(+) = 'FUND'
1441 AND bu1.act_budget_used_by_id(+) = o.fund_id
1442 AND bu1.budget_source_type(+) ='FUND'
1443 GROUP BY trunc(bu1.approval_date),
1444 o.fund_id ,
1445 o.fund_number ,
1446 o.start_date_active ,
1447 o.end_date_active ,
1448 o.start_period_name ,
1449 o.end_period_name ,
1450 o.category_id ,
1451 o.status_code ,
1452 o.fund_type ,
1453 o.parent_fund_id ,
1454 o.business_unit_id ,
1455 o.country_id ,
1456 o.org_id ,
1457 o.set_of_books_id ,
1458 o.currency_code_fc ,
1459 o.original_budget
1460 UNION ALL
1461 SELECT trunc(bu2.approval_date) transaction_create_date,
1462 0 transfer_in,
1463 nvl(SUM(decode(bu2.transfer_type,'TRANSFER', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0)
1464 +nvl(SUM(decode(bu2.transfer_type,'REQUEST', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0) transfer_out,
1465 nvl(SUM(decode(bu2.transfer_type, 'RESERVE', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0)-
1466 nvl(SUM(decode(bu2.transfer_type, 'RELEASE', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0) holdback,
1467 --nvl(SUM(convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0))),0) transfer_out,
1468 --nvl(SUM(decode(bu2.transfer_type, 'RESERVE', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0) holdback,
1469 o.fund_id fund_id,
1470 o.fund_number fund_number,
1471 o.start_date_active start_date,
1472 o.end_date_active end_date,
1473 o.start_period_name start_period,
1474 o.end_period_name end_period,
1475 o.category_id category_id,
1476 o.status_code status,
1477 o.fund_type fund_type,
1478 o.parent_fund_id parent_fund_id,
1479 o.business_unit_id business_unit_id,
1480 o.country_id country,
1481 o.org_id org_id,
1482 o.set_of_books_id set_of_books_id,
1483 o.currency_code_fc currency_code_fc,
1484 o.original_budget original_budget
1485 FROM ozf_funds_all_b o,
1486 ozf_act_budgets BU2
1487 WHERE o.start_date_active between p_start_datel and p_end_datel
1488 AND o.status_code in ('ACTIVE','CANCEL', 'CLOSED')
1489 AND bu2.approval_date <trunc(o.start_date_active)
1490 AND bu2.status_code(+) = 'APPROVED'
1491 AND bu2.arc_act_budget_used_by(+) = 'FUND'
1492 AND bu2.budget_source_type(+) ='FUND'
1493 AND bu2.budget_source_id (+)= o.fund_id
1494 GROUP BY trunc(bu2.approval_date),
1495 o.fund_id ,
1496 o.fund_number ,
1497 o.start_date_active ,
1498 o.end_date_active ,
1499 o.start_period_name ,
1500 o.end_period_name ,
1501 o.category_id ,
1502 o.status_code ,
1503 o.fund_type ,
1504 o.parent_fund_id ,
1505 o.business_unit_id ,
1506 o.country_id ,
1507 o.org_id ,
1508 o.set_of_books_id ,
1509 o.currency_code_fc ,
1510 o.original_budget )
1511 GROUP BY transaction_create_date,
1512 fund_id ,
1513 fund_number ,
1514 start_date ,
1515 end_date ,
1516 start_period ,
1517 end_period ,
1518 category_id ,
1519 status ,
1520 fund_type ,
1521 parent_fund_id ,
1522 business_unit_id ,
1523 country ,
1524 org_id ,
1525 set_of_books_id ,
1526 currency_code_fc ,
1527 original_budget )inner;
1528 l_count:=l_count+SQL%ROWCOUNT;
1529 EXECUTE IMMEDIATE 'commit';
1530 --End inserting transactions happened before start_date
1531 EXCEPTION
1532 WHEN others THEN
1533 FND_FILE.put_line(fnd_file.log,'error insert into fund_daily for transactions happened b4 start date'||sqlerrm(sqlcode));
1534 --dbms_output.put_line('error looping inserting INTO bim_r_fund_daily_facts'||sqlerrm(sqlcode));
1535 x_return_status := FND_API.G_RET_STS_ERROR;
1536 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
1537 FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
1538 FND_MSG_PUB.Add;
1539 END;
1540 /* Start inserting into 'bim_r_fdsp_daily_facts' */
1541 l_table_name :='bim_r_fdsp_daily_facts';
1542 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
1543 fnd_message.set_token('table_name', 'bim_r_fdsp_load', FALSE);
1544 fnd_file.put_line(fnd_file.log,fnd_message.get);
1545 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 1000';
1546 EXECUTE IMMEDIATE 'truncate table '||l_schema||'.bim_r_fdsp_load';
1547 /* bim_r_fdsp_load is a intermediate table which holds commited amount, planned amount etc for all objects */
1548 BEGIN
1549 INSERT /*+ append parallel(bfl,p_para_num) */
1550 INTO bim_r_fdsp_load bfl(
1551 spend_transaction_id
1552 ,creation_date
1553 ,last_update_date
1554 ,created_by
1555 ,last_updated_by
1556 ,last_update_login
1557 ,fund_id
1558 ,business_unit_id
1559 ,util_org_id
1560 ,standard_discount
1561 ,accrual
1562 ,market_expense
1563 ,commited_amt
1564 ,planned_amt
1565 ,paid_amt
1566 ,delete_flag
1567 ,transaction_create_date
1568 ,load_date
1569 ,object_id
1570 ,object_type)
1571 SELECT /*+ parallel(act_util, p_para_num) */
1572 0,
1573 sysdate,
1574 sysdate,
1575 -1,
1576 -1,
1577 -1,
1578 act_util.fund_id,
1579 0,
1580 0,
1581 act_util.standard_discount,
1582 act_util.accrual,
1583 act_util.market_expense,
1584 act_util.commited_amt,
1585 act_util.planned_amt,
1586 act_util.paid_amt,
1587 'Y',
1588 act_util.creation_date,
1589 sysdate,
1590 act_util.object_id,
1591 act_util.object_type
1592 FROM (SELECT fund_id fund_id,
1593 object_id object_id,
1594 object_type object_type,
1595 creation_date creation_date,
1596 SUM(nvl(planned_amt,0)) planned_amt,
1597 SUM(nvl(commited_amt,0)) commited_amt,
1598 SUM(nvl(standard_discount,0)) standard_discount,
1599 SUM(nvl(accrual,0)) accrual,
1600 SUM(nvl(market_expense,0)) market_expense,
1601 SUM(nvl(paid_amt,0)) paid_amt
1602 FROM (
1603 SELECT budget_source_id fund_id,
1604 act_budget_used_by_id object_id,
1605 arc_act_budget_used_by object_type,
1606 trunc(nvl(request_date,creation_date)) creation_date,
1607 SUM(convert_currency(request_currency, nvl(request_amount,0))) planned_amt,
1608 0 commited_amt,
1609 0 standard_discount,
1610 0 accrual,
1611 0 market_expense,
1612 0 paid_amt
1613 FROM ozf_act_budgets
1614 WHERE budget_source_type ='FUND'
1615 AND status_code ='PENDING'
1616 AND ARC_ACT_BUDGET_USED_BY <> 'FUND'
1617 GROUP BY trunc(nvl(request_date ,creation_date)),
1618 budget_source_id,act_budget_used_by_id,
1619 arc_act_budget_used_by
1620 UNION ALL
1621 SELECT budget_source_id fund_id,
1622 act_budget_used_by_id object_id,
1623 arc_act_budget_used_by object_type,
1624 trunc(nvl(approval_date,last_update_date)) creation_date,
1625 --0-SUM(convert_currency(request_currency, nvl(request_amount,0))) planned_amt,
1626 0 planned_amt,
1627 SUM(convert_currency(approved_in_currency,nvl(approved_original_amount,0))) commited_amt,
1628 0 standard_discount,
1629 0 accrual,
1630 0 market_expense,
1631 0 paid_amt
1632 FROM ozf_act_budgets
1633 WHERE budget_source_type ='FUND'
1634 AND ARC_ACT_BUDGET_USED_BY <> 'FUND'
1635 AND status_code ='APPROVED'
1636 GROUP BY trunc(nvl(approval_date,last_update_date)),
1637 budget_source_id,act_budget_used_by_id,
1638 arc_act_budget_used_by
1639 UNION ALL
1640 SELECT act_budget_used_by_id fund_id,
1641 budget_source_id object_id,
1642 budget_source_type object_type,
1643 trunc(nvl(approval_date,last_update_date)) creation_date,
1644 0 planned_amt,
1645 0-SUM(convert_currency(approved_in_currency,nvl(approved_original_amount,0))) commited_amt,
1646 0 standard_discount,
1647 0 accrual,
1648 0 market_expense,
1649 0 paid_amt
1650 FROM ozf_act_budgets
1651 WHERE arc_act_budget_used_by ='FUND'
1652 AND budget_source_type<>'FUND'
1653 AND status_code ='APPROVED'
1654 GROUP BY trunc(nvl(approval_date,last_update_date)),
1655 act_budget_used_by_id, budget_source_id,
1656 budget_source_type
1657 UNION ALL
1658 SELECT fund_id fund_id,
1659 plan_id object_id,
1660 plan_type object_type,
1661 trunc(creation_date) creation_date,
1662 0 planned_amt,
1663 0 commited_amt,
1664 SUM(decode(component_type,'OFFR',decode(utilization_type, 'UTILIZED',convert_currency(currency_code,nvl(amount,0)), 0),0)) standard_discount,
1665 SUM(decode(component_type,'OFFR', decode(utilization_type, 'ACCRUAL', convert_currency(currency_code,nvl(amount,0)), 0),0) +
1666 decode(component_type,'OFFR', decode(utilization_type, 'ADJUSTMENT', convert_currency(currency_code,nvl(amount,0)), 0),0)) accrual,
1667 SUM(decode(component_type,'OFFR',0, decode(utilization_type, 'UTILIZED', convert_currency(currency_code,nvl(amount,0)), 0))) market_expense,
1668 sum(decode(component_type,'OFFR',0,convert_currency(currency_code,(nvl(amount,0)-NVL(amount_remaining,0))))) paid_amt
1669 FROM ozf_funds_utilized_all_b
1670 WHERE utilization_type in ('UTILIZED','ACCRUAL','ADJUSTMENT')
1671 GROUP BY trunc(creation_date),fund_id,plan_id,plan_type
1672 )
1673 GROUP BY creation_date, fund_id, object_id,object_type
1674 ) act_util;
1675 EXECUTE IMMEDIATE 'COMMIT';
1676 EXCEPTION
1677 WHEN OTHERS THEN
1678 FND_FILE.put_line(fnd_file.log,'error insert fdsp daily'||sqlerrm(sqlcode));
1679 --dbms_output.put_line('error inserting INTO bim_r_fdsp_daily_facts'||sqlerrm(sqlcode));
1680 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';
1681 x_return_status := FND_API.G_RET_STS_ERROR;
1682 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
1683 FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
1684 FND_MSG_PUB.Add;
1685 END ; --end of insertion into bim_r_fdsp_load.
1686
1687 -- dbms_output.put_line('inside fist inserting into fdsp daily');
1688 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
1689 fnd_message.set_token('table_name', l_table_name, FALSE);
1690 fnd_file.put_line(fnd_file.log,fnd_message.get);
1691 /* First insert: insert into bim_r_fdsp_daily_facts for the funds whose active
1692 date between p_start_date and p_end_date. Fund transactions are: commited, planned, utilized
1693 etc. The measures are on object level. Object are like campaigns, events, offers, etc. */
1694 BEGIN
1695 INSERT /*+ append parallel(fdf,p_para_num) */
1696 INTO bim_r_fdsp_daily_facts fdf(
1697 spend_transaction_id
1698 ,creation_date
1699 ,last_update_date
1700 ,created_by
1701 ,last_updated_by
1702 ,last_update_login
1703 ,fund_id
1704 ,business_unit_id
1705 ,util_org_id
1706 ,standard_discount
1707 ,accrual
1708 ,market_expense
1709 ,commited_amt
1710 ,planned_amt
1711 ,paid_amt
1712 ,delete_flag
1713 ,transaction_create_date
1714 ,load_date
1715 ,object_id
1716 ,object_type
1717 ,fis_month
1718 ,fis_qtr
1719 ,fis_year
1720 )
1721 SELECT /*+ parallel(inner, p_para_num) */
1722 bim_r_fdsp_daily_facts_s.nextval,
1723 sysdate,
1724 sysdate,
1725 l_user_id,
1726 l_user_id,
1727 l_user_id,
1728 inner.fund_id,
1729 inner.business_unit_id,
1730 inner.org_id,
1731 inner.standard_discount,
1732 inner.accrual,
1733 inner.market_expense,
1734 inner.commited_amt,
1735 inner.planned_amt,
1736 inner.paid_amt,
1737 'N',
1738 inner.transaction_create_date,
1739 trunc((decode(decode( to_char(inner.transaction_create_date,'MM') , to_char(next_day(inner.transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
1740 ,'TRUE'
1741 ,decode(decode(inner.transaction_create_date , (next_day(inner.transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
1742 ,'TRUE'
1743 ,inner.transaction_create_date
1744 ,'FALSE'
1745 ,next_day(inner.transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
1746 ,'FALSE'
1747 ,decode(decode(to_char(inner.transaction_create_date,'MM'),to_char(next_day(inner.transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
1748 ,'FALSE'
1749 ,last_day(inner.transaction_create_date))))) weekend_date
1750 ,inner.object_id
1751 ,inner.object_type
1752 ,BIM_SET_OF_BOOKS.get_fiscal_month(inner.transaction_create_date,204)
1753 ,BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.transaction_create_date,204)
1754 ,BIM_SET_OF_BOOKS.get_fiscal_year(inner.transaction_create_date,204)
1755 FROM (SELECT AD.fund_id fund_id,
1756 U.business_unit_id business_unit_id,
1757 U.org_id org_id,
1758 NVL(AU.standard_discount,0) standard_discount,
1759 NVL(AU.accrual,0) accrual,
1760 NVL(AU.market_expense,0) market_expense,
1761 NVL(AU.commited_amt,0) commited_amt,
1762 NVL(AU.planned_amt,0) planned_amt,
1763 NVL(AU.paid_amt,0) paid_amt,
1764 AU.object_id object_id,
1765 U.object_type object_type,
1766 AD.trdate transaction_create_date
1767 FROM (SELECT A.fund_id fund_id,TRUNC(DA.trdate) trdate
1768 FROM ozf_funds_all_b A,
1769 bim_intl_dates DA
1770 WHERE A.status_code IN ( 'ACTIVE','CANCELLED','CLOSED' )
1771 AND DA.trdate between A.start_date_active
1772 and least(nvl(A.end_date_active,sysdate-1),p_end_datel)
1773 AND A.start_date_active between p_start_datel and p_end_datel
1774 ) AD,
1775 bim_r_fdsp_load AU,
1776 (SELECT
1777 D.business_unit_id business_unit_id,D.org_id org_id,
1778 'CAMP' object_type_J,'CAMP' object_type, D.campaign_id object_id
1779 FROM ams_campaigns_all_b D
1780 UNION ALL
1781 SELECT
1782 D.business_unit_id business_unit_id,D.org_id org_id,
1783 'CSCH' object_type_J,'CSCH' object_type,B.SCHEDULE_ID object_id
1784 FROM
1785 ams_campaigns_all_b D,
1786 ams_campaign_schedules_b B
1787 WHERE B.campaign_id = D.campaign_id (+)
1788 UNION ALL
1789 SELECT
1790 D.business_unit_id business_unit_id,D.org_id org_id,
1791 'EVEH' object_type_J,'EVEH' object_type, D.event_header_id object_id
1792 FROM
1793 ams_event_headers_all_b D
1794 UNION ALL
1795 SELECT
1796 D.business_unit_id business_unit_id,D.org_id org_id,
1797 'EVEO' object_type_J, 'EVEO' object_type, D.event_offer_id object_id
1798 FROM
1799 ams_event_offers_all_b D
1800 WHERE event_header_id is not null
1801 UNION ALL
1802 SELECT
1803 D.business_unit_id business_unit_id,D.org_id org_id,
1804 'EONE' object_type_J, 'EONE' object_type, D.event_offer_id object_id
1805 FROM
1806 ams_event_offers_all_b D
1807 WHERE event_header_id is null
1808 UNION ALL
1809 SELECT
1810 BC.business_unit_id business_unit_id,BC.org_id org_id,
1811 'OFFR' object_type_J, 'OFFR' object_type, D.qp_list_header_id object_id
1812 FROM
1813 ams_campaigns_all_b BC,
1814 ams_act_offers D
1815 WHERE
1816 D.arc_act_offer_used_by (+) = 'CAMP' AND D.act_offer_used_by_id =
1817 BC.campaign_id (+) AND BC.show_campaign_flag (+) = 'Y'
1818 UNION ALL
1819 SELECT
1820 BA.business_unit_id business_unit_id,BA.org_id org_id,
1821 'DELV' object_type_J, 'CAMPDELV' object_type, D.using_object_id object_id
1822 FROM
1823 ams_campaigns_all_b BA,
1824 ams_object_associations D
1825 WHERE
1826 D.using_object_type='DELV' AND
1827 D.master_object_type (+) = 'CAMP' AND
1828 D.master_object_id = BA.campaign_id (+)
1829 UNION ALL
1830 SELECT
1831 BA.business_unit_id business_unit_id,BA.org_id org_id,
1832 'DELV' object_type_J, 'CSCHDELV' object_type, D.using_object_id object_id
1833 FROM
1834 ams_campaigns_all_b BA,
1835 ams_campaign_schedules_b E,
1836 ams_object_associations D
1837 WHERE
1838 D.master_object_type (+) = 'CSCH' AND D.master_object_id = E.SCHEDULE_ID
1839 (+) AND E.campaign_id = BA.campaign_id (+)
1840 AND D.using_object_type (+) = 'DELV'
1841 UNION ALL
1842 SELECT
1843 BA.business_unit_id business_unit_id,BA.org_id org_id,
1844 'DELV' object_type_J, 'EVEHDELV' object_type, D.using_object_id object_id
1845 FROM
1846 ams_event_headers_all_b BA,
1847 ams_object_associations D
1848 WHERE D.using_object_type(+) = 'DELV'
1849 AND D.master_object_type(+) = 'EVEH'
1850 AND D.master_object_id = BA.event_header_id (+)
1851 UNION ALL
1852 SELECT
1853 BA.business_unit_id business_unit_id,BA.org_id org_id,
1854 'DELV' object_type_J, 'EVEODELV' object_type, D.using_object_id object_id
1855 FROM
1856 ams_event_offers_all_b BA,
1857 ams_object_associations D
1858 WHERE D.using_object_type(+) = 'DELV'
1859 AND D.master_object_type(+) = 'EVEO'
1860 AND D.master_object_id = BA.event_offer_id (+)
1861 UNION ALL
1862 SELECT
1863 BA.business_unit_id business_unit_id,BA.org_id org_id,
1864 'DELV' object_type_J, 'EONEDELV' object_type, D.using_object_id object_id
1865 FROM
1866 ams_event_offers_all_b BA,
1867 ams_object_associations D
1868 WHERE D.using_object_type(+) = 'DELV'
1869 AND D.master_object_type(+) = 'EONE'
1870 AND D.master_object_id = BA.event_offer_id (+)
1871 ) U
1872 WHERE AU.object_type = U.object_type_J (+)
1873 AND AU.object_id = U.object_id (+)
1874 AND AU.fund_id (+) = AD.fund_id
1875 AND AU.transaction_create_date (+) BETWEEN TRUNC(AD.trdate) AND TRUNC(AD.trdate) + 0.99999
1876 ) inner;
1877 l_count :=l_count +SQL%ROWCOUNT;
1878 EXECUTE IMMEDIATE 'COMMIT';
1879 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';
1880 EXCEPTION
1881 WHEN OTHERS THEN
1882 FND_FILE.put_line(fnd_file.log,'error insert fdsp daily'||sqlerrm(sqlcode));
1883 --dbms_output.put_line('error inserting INTO bim_r_fdsp_daily_facts'||sqlerrm(sqlcode));
1884 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';
1885 x_return_status := FND_API.G_RET_STS_ERROR;
1886 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
1887 FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
1888 FND_MSG_PUB.Add;
1889 END ;
1890
1891 /* Second insert: extra records which happened before start date active */
1892 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
1893 fnd_message.set_token('table_name', l_table_name, FALSE);
1894 fnd_file.put_line(fnd_file.log,fnd_message.get);
1895 BEGIN
1896 INSERT INTO bim_r_fdsp_daily_facts fdf(
1897 spend_transaction_id
1898 ,creation_date
1899 ,last_update_date
1900 ,created_by
1901 ,last_updated_by
1902 ,last_update_login
1903 ,fund_id
1904 ,business_unit_id
1905 ,util_org_id
1906 ,standard_discount
1907 ,accrual
1908 ,market_expense
1909 ,commited_amt
1910 ,planned_amt
1911 ,paid_amt
1912 ,delete_flag
1913 ,transaction_create_date
1914 ,load_date
1915 ,object_id
1916 ,object_type
1917 ,fis_month
1918 ,fis_qtr
1919 ,fis_year
1920 )
1921 SELECT
1922 bim_r_fdsp_daily_facts_s.nextval,
1923 sysdate,
1924 sysdate,
1925 l_user_id,
1926 l_user_id,
1927 l_user_id,
1928 inner.fund_id,
1929 inner.business_unit_id,
1930 inner.org_id,
1931 inner.standard_discount,
1932 inner.accrual,
1933 inner.market_expense,
1934 inner.commited_amt,
1935 inner.planned_amt,
1936 inner.paid_amt,
1937 'N',
1938 inner.transaction_create_date,
1939 trunc((decode(decode( to_char(inner.transaction_create_date,'MM') , to_char(next_day(inner.transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
1940 ,'TRUE'
1941 ,decode(decode(inner.transaction_create_date , (next_day(inner.transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
1942 ,'TRUE'
1943 ,inner.transaction_create_date
1944 ,'FALSE'
1945 ,next_day(inner.transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
1946 ,'FALSE'
1947 ,decode(decode(to_char(inner.transaction_create_date,'MM'),to_char(next_day(inner.transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
1948 ,'FALSE'
1949 ,last_day(inner.transaction_create_date))))) weekend_date
1950 ,inner.object_id
1951 ,inner.object_type
1952 ,BIM_SET_OF_BOOKS.get_fiscal_month(inner.transaction_create_date,204)
1953 ,BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.transaction_create_date,204)
1954 ,BIM_SET_OF_BOOKS.get_fiscal_year(inner.transaction_create_date,204)
1955 FROM (SELECT AD.fund_id fund_id,
1956 U.business_unit_id business_unit_id,
1957 U.org_id org_id,
1958 NVL(AU.standard_discount,0) standard_discount,
1959 NVL(AU.accrual,0) accrual,
1960 NVL(AU.market_expense,0) market_expense,
1961 NVL(AU.commited_amt,0) commited_amt,
1962 NVL(AU.planned_amt,0) planned_amt,
1963 NVL(AU.paid_amt,0) paid_amt,
1964 AU.object_id object_id,
1965 U.object_type object_type,
1966 AU.transaction_create_date transaction_create_date
1967 FROM ozf_funds_all_b AD,
1968 bim_r_fdsp_load AU,
1969 (SELECT
1970 D.business_unit_id business_unit_id,D.org_id org_id,
1971 'CAMP' object_type_J,'CAMP' object_type, D.campaign_id object_id
1972 FROM ams_campaigns_all_b D
1973 UNION ALL
1974 SELECT
1975 D.business_unit_id business_unit_id,D.org_id org_id,
1976 'CSCH' object_type_J,'CSCH' object_type,B.SCHEDULE_ID object_id
1977 FROM
1978 ams_campaigns_all_b D,
1979 ams_campaign_schedules_b B
1980 WHERE B.campaign_id = D.campaign_id (+)
1981 UNION ALL
1982 SELECT
1983 D.business_unit_id business_unit_id,D.org_id org_id,
1984 'EVEH' object_type_J,'EVEH' object_type, D.event_header_id object_id
1985 FROM
1986 ams_event_headers_all_b D
1987 UNION ALL
1988 SELECT
1989 D.business_unit_id business_unit_id,D.org_id org_id,
1990 'EVEO' object_type_J, 'EVEO' object_type, D.event_offer_id object_id
1991 FROM
1992 ams_event_offers_all_b D
1993 WHERE event_header_id is not null
1994 UNION ALL
1995 SELECT
1996 D.business_unit_id business_unit_id,D.org_id org_id,
1997 'EONE' object_type_J, 'EONE' object_type, D.event_offer_id object_id
1998 FROM
1999 ams_event_offers_all_b D
2000 WHERE event_header_id is null
2001 UNION ALL
2002 SELECT
2003 BC.business_unit_id business_unit_id,BC.org_id org_id,
2004 'OFFR' object_type_J, 'OFFR' object_type, D.qp_list_header_id object_id
2005 FROM
2006 ams_campaigns_all_b BC,
2007 ams_act_offers D
2008 WHERE
2009 D.arc_act_offer_used_by (+) = 'CAMP' AND D.act_offer_used_by_id =
2010 BC.campaign_id (+) AND BC.show_campaign_flag (+) = 'Y'
2011 UNION ALL
2012 SELECT
2013 BA.business_unit_id business_unit_id,BA.org_id org_id,
2014 'DELV' object_type_J, 'CAMPDELV' object_type, D.using_object_id object_id
2015 FROM
2016 ams_campaigns_all_b BA,
2017 ams_object_associations D
2018 WHERE
2019 D.using_object_type='DELV' AND
2020 D.master_object_type (+) = 'CAMP' AND
2021 D.master_object_id = BA.campaign_id (+)
2022 UNION ALL
2023 SELECT
2024 BA.business_unit_id business_unit_id,BA.org_id org_id,
2025 'DELV' object_type_J, 'CSCHDELV' object_type, D.using_object_id object_id
2026 FROM
2027 ams_campaigns_all_b BA,
2028 ams_campaign_schedules_b E,
2029 ams_object_associations D
2030 WHERE
2031 D.master_object_type (+) = 'CSCH' AND D.master_object_id = E.SCHEDULE_ID
2032 (+) AND E.campaign_id = BA.campaign_id (+)
2033 AND D.using_object_type (+) = 'DELV'
2034 UNION ALL
2035 SELECT
2036 BA.business_unit_id business_unit_id,BA.org_id org_id,
2037 'DELV' object_type_J, 'EVEHDELV' object_type, D.using_object_id object_id
2038 FROM
2039 ams_event_headers_all_b BA,
2040 ams_object_associations D
2041 WHERE D.using_object_type(+) = 'DELV'
2042 AND D.master_object_type(+) = 'EVEH'
2043 AND D.master_object_id = BA.event_header_id (+)
2044 UNION ALL
2045 SELECT
2046 BA.business_unit_id business_unit_id,BA.org_id org_id,
2047 'DELV' object_type_J, 'EVEODELV' object_type, D.using_object_id object_id
2048 FROM
2049 ams_event_offers_all_b BA,
2050 ams_object_associations D
2051 WHERE D.using_object_type(+) = 'DELV'
2052 AND D.master_object_type(+) = 'EVEO'
2053 AND D.master_object_id = BA.event_offer_id (+)
2054 UNION ALL
2055 SELECT
2056 BA.business_unit_id business_unit_id,BA.org_id org_id,
2057 'DELV' object_type_J, 'EONEDELV' object_type, D.using_object_id object_id
2058 FROM
2059 ams_event_offers_all_b BA,
2060 ams_object_associations D
2061 WHERE D.using_object_type(+) = 'DELV'
2062 AND D.master_object_type(+) = 'EONE'
2063 AND D.master_object_id = BA.event_offer_id (+)
2064 ) U
2065 WHERE AD.start_date_active between p_start_datel and p_end_datel
2066 AND AD.status_code in ('ACTIVE', 'CANCELLED', 'CLOSED')
2067 AND AU.fund_id (+) = AD.fund_id
2068 AND AU.object_type = U.object_type_J (+)
2069 AND AU.object_id = U.object_id (+)
2070 AND AU.transaction_create_date <AD.start_date_active
2071 ) inner;
2072 l_count :=l_count+SQL%ROWCOUNT;
2073 EXECUTE IMMEDIATE 'commit';
2074 EXCEPTION
2075 WHEN others THEN
2076 FND_FILE.put_line(fnd_file.log,'error insert extras into fdsp daily'||sqlerrm(sqlcode));
2077 --dbms_output.put_line('error inserting extra INTO bim_r_fdsp_daily_facts'||sqlerrm(sqlcode));
2078 x_return_status := FND_API.G_RET_STS_ERROR;
2079 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
2080 FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
2081 FND_MSG_PUB.Add;
2082 END;
2083 -- dbms_output.put_line('l_count'||l_count);
2084 fnd_message.set_name('BIM','BIM_R_CALL_PROC');
2085 fnd_message.set_token('proc_name', 'LOG_HISTORY', FALSE);
2086 fnd_file.put_line(fnd_file.log,fnd_message.get);
2087 /* Insert into history table */
2088 --IF l_count>0 THEN
2089 LOG_HISTORY(
2090 'FUND',
2091 p_start_datel,
2092 p_end_datel,
2093 x_msg_count,
2094 x_msg_data,
2095 x_return_status) ;
2096 --END IF;
2097
2098 /* There are discrete dates in bim_r_fund_daily_facts and bim_r_fdsp_daily_facts,because of
2099 the pre-approvals. So, we want to make them have the same dates by inserting into each
2100 table the dates in one table but not in another table for the same funds.*/
2101 BEGIN
2102 -- dbms_output.put_line('balancing');
2103
2104 /* Insert into bim_r_fund_daily_facts the dates which are in bim_r_fdsp_daily_facts
2105 but not in bim_r_fund_daily_facts. */
2106 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
2107 fnd_message.set_token('table_name', 'BIM_R_FUND_DAILY_FACTS', FALSE);
2108 fnd_file.put_line(fnd_file.log,fnd_message.get);
2109 INSERT into bim_r_fund_daily_facts(
2110 fund_transaction_id
2111 ,creation_date
2112 ,last_update_date
2113 ,created_by
2114 ,last_updated_by
2115 ,last_update_login
2116 ,fund_id
2117 ,parent_fund_id
2118 ,fund_number
2119 ,start_date
2120 ,end_date
2121 ,start_period
2122 ,end_period
2123 ,set_of_books_id
2124 ,fund_type
2125 --,region
2126 ,country
2127 ,org_id
2128 ,category_id
2129 ,status
2130 ,original_budget
2131 ,transfer_in
2132 ,transfer_out
2133 ,holdback_amt
2134 ,currency_code_fc
2135 ,delete_flag
2136 ,transaction_create_date
2137 ,load_date
2138 ,fis_month
2139 ,fis_qtr
2140 ,fis_year
2141 ,business_unit_id)
2142 SELECT
2143 bim_r_fund_daily_facts_s.nextval,
2144 sysdate,
2145 sysdate,
2146 l_user_id,
2147 l_user_id,
2148 l_user_id,
2149 a.fund_id ,
2150 a.parent_fund_id parent_fund_id,
2151 a.fund_number fund_number,
2152 a.start_date_active start_date,
2153 a.end_date_active end_date,
2154 a.start_period_name start_period,
2155 a.end_period_name end_period,
2156 a.set_of_books_id set_of_book_id,
2157 a.fund_type fund_type,
2158 a.country_id country,
2159 a.org_id org_id,
2160 a.category_id fund_category,
2161 a.status_code fund_status,
2162 a.original_budget original_amount,
2163 0,
2164 0,
2165 0,
2166 a.currency_code_fc,
2167 'N',
2168 b2.transaction_create_date,
2169 trunc((decode(decode( to_char(transaction_create_date,'MM') , to_char(next_day(transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
2170 ,'TRUE'
2171 ,decode(decode(transaction_create_date , (next_day(transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
2172 ,'TRUE'
2173 ,transaction_create_date
2174 ,'FALSE'
2175 ,next_day(transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
2176 ,'FALSE'
2177 ,decode(decode(to_char(transaction_create_date,'MM'),to_char(next_day(transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
2178 ,'FALSE'
2179 ,last_day(transaction_create_date))))),
2180 b2.fis_month,
2181 b2.fis_qtr,
2182 b2.fis_year,
2183 a.business_unit_id
2184 FROM ozf_funds_all_b a,
2185 (SELECT distinct(fund_id) fund_id,
2186 fis_month fis_month,
2187 fis_qtr fis_qtr,
2188 fis_year fis_year,
2189 transaction_create_date transaction_create_date
2190 FROM bim_r_fdsp_daily_facts fdsp
2191 WHERE fund_id is not null
2192 AND transaction_create_date is not null
2193 AND (fund_id, transaction_create_date) not in
2194 ( SELECT /*+ hash_aj */ fund_id, transaction_create_date
2195 from bim_r_fund_daily_facts b1
2196 )) b2
2197 WHERE b2.fund_id = a.fund_id;
2198 EXECUTE IMMEDIATE 'commit';
2199 EXCEPTION
2200 WHEN OTHERS THEN
2201 FND_FILE.put_line(fnd_file.log,'error insert fund daily for balancing'||sqlerrm(sqlcode));
2202 -- dbms_output.put_line('error insert fund_daily for balancing with fdsp'||sqlerrm(sqlcode));
2203 x_return_status := FND_API.G_RET_STS_ERROR;
2204 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
2205 FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
2206 FND_MSG_PUB.Add;
2207 fnd_file.put_line(fnd_file.log,fnd_message.get);
2208 END;
2209 /* Start of inserting into 'bim_r_fund_weekly_facts'.
2210 Weekly table are summarized on daily tables. */
2211 BEGIN
2212 l_table_name :='bim_r_fund_weekly_facts';
2213 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
2214 fnd_message.set_token('table_name', l_table_name, FALSE);
2215 fnd_file.put_line(fnd_file.log,fnd_message.get);
2216 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_weekly_facts_s CACHE 1000';
2217 INSERT /*+ append parallel(fwf,p_para_num) */ INTO bim_r_fund_weekly_facts fwf(
2218 fund_transaction_id
2219 ,creation_date
2220 ,last_update_date
2221 ,created_by
2222 ,last_updated_by
2223 ,last_update_login
2224 ,fund_id
2225 ,parent_fund_id
2226 ,fund_number
2227 ,start_date
2228 ,end_date
2229 ,start_period
2230 ,end_period
2231 ,set_of_books_id
2232 ,fund_type
2233 -- ,region
2234 ,country
2235 ,org_id
2236 ,category_id
2237 ,status
2238 ,original_budget
2239 ,transfer_in
2240 ,transfer_out
2241 ,holdback_amt
2242 ,currency_code_fc
2243 ,delete_flag
2244 ,transaction_create_date
2245 ,load_date
2246 ,fis_month
2247 ,fis_qtr
2248 ,fis_year
2249 ,business_unit_id)
2250 SELECT /*+ parallel(inner, p_para_num) */
2251 bim_r_fund_weekly_facts_s.nextval
2252 ,sysdate
2253 ,sysdate
2254 ,l_user_id
2255 ,l_user_id
2256 ,l_user_id
2257 ,inner.fund_id
2258 ,inner.parent_fund_id
2259 ,inner.fund_number
2260 ,inner.start_date
2261 ,inner.end_date
2262 ,inner.start_period
2263 ,inner.end_period
2264 ,inner.set_of_books_id
2265 ,inner.fund_type
2266 --,inner.region
2267 ,inner.country
2268 ,inner.org_id
2269 ,inner.category_id
2270 ,inner.status
2271 ,inner.original_budget
2272 ,inner.transfer_in
2273 ,inner.transfer_out
2274 ,inner.holdback_amt
2275 ,inner.currency_code_fc
2276 ,'N'
2277 ,inner.load_date
2278 ,inner.load_date
2279 ,BIM_SET_OF_BOOKS.get_fiscal_month(inner.load_date,204)
2280 ,BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.load_date,204)
2281 ,BIM_SET_OF_BOOKS.get_fiscal_year(inner.load_date,204)
2282 ,inner.business_unit_id
2283 FROM(SELECT fund_id fund_id
2284 ,parent_fund_id parent_fund_id
2285 ,fund_number fund_number
2286 ,start_date start_date
2287 ,end_date end_date
2288 ,start_period start_period
2289 ,end_period end_period
2290 ,set_of_books_id set_of_books_id
2291 ,fund_type fund_type
2292 --,region region
2293 ,country country
2294 ,org_id org_id
2295 ,business_unit_id business_unit_id
2296 ,category_id category_id
2297 ,status status
2298 ,original_budget original_budget
2299 ,SUM(transfer_in) transfer_in
2300 ,SUM(transfer_out) transfer_out
2301 ,SUM(holdback_amt) holdback_amt
2302 ,currency_code_fc currency_code_fc
2303 ,load_date load_date
2304 FROM bim_r_fund_daily_facts
2305 GROUP BY
2306 fund_id
2307 ,load_date
2308 ,parent_fund_id
2309 ,fund_number
2310 ,start_date
2311 ,end_date
2312 ,start_period
2313 ,end_period
2314 ,set_of_books_id
2315 ,fund_type
2316 -- ,region
2317 ,country
2318 ,org_id
2319 ,business_unit_id
2320 ,category_id
2321 ,status
2322 ,original_budget
2323 ,currency_code_fc) inner;
2324 EXECUTE IMMEDIATE 'commit';
2325 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_weekly_facts_s CACHE 20';
2326 EXCEPTION
2327 WHEN others THEN
2328 FND_FILE.put_line(fnd_file.log,'Error insertg fund weekly:'||sqlerrm(sqlcode));
2329 -- dbms_output.put_line('Errorin inserting fund weekly:'||sqlerrm(sqlcode));
2330 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_weekly_facts_s CACHE 20';
2331 x_return_status := FND_API.G_RET_STS_ERROR;
2332 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
2333 FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
2334 FND_MSG_PUB.Add;
2335 END;
2336 /* Insert into bim_r_fdsp_weekly_facts. */
2337 BEGIN
2338 l_table_name :='bim_r_fdsp_weekly_facts';
2339 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
2340 fnd_message.set_token('table_name', l_table_name, FALSE);
2341 fnd_file.put_line(fnd_file.log,fnd_message.get);
2342 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_weekly_facts_s CACHE 1000';
2343 INSERT /*+ append parallel(fwf,p_para_num) */
2344 INTO bim_r_fdsp_weekly_facts fwf(
2345 spend_transaction_id
2346 ,creation_date
2347 ,last_update_date
2348 ,created_by
2349 ,last_updated_by
2350 ,last_update_login
2351 ,fund_id
2352 ,business_unit_id
2353 ,util_org_id
2354 ,standard_discount
2355 ,accrual
2356 ,market_expense
2357 ,commited_amt
2358 ,planned_amt
2359 ,paid_amt
2360 ,delete_flag
2361 ,transaction_create_date
2362 ,load_date
2363 ,object_id
2364 ,object_type
2365 ,fis_month
2366 ,fis_qtr
2367 ,fis_year
2368 )
2369 SELECT /*+ parallel(inner.p_para_num) */
2370 bim_r_fdsp_weekly_facts_s.nextval
2371 ,sysdate
2372 ,sysdate
2373 ,l_user_id
2374 ,l_user_id
2375 ,l_user_id
2376 ,inner.fund_id
2377 ,inner.business_unit_id
2378 ,inner.util_org_id
2379 ,inner.standard_discount
2380 ,inner.accrual
2381 ,inner.market_expense
2382 ,inner.commited_amt
2383 ,inner.planned_amt
2384 ,inner.paid_amt
2385 ,'N'
2386 ,inner.load_date
2387 ,inner.load_date
2388 ,inner.object_id
2389 ,inner.object_type
2390 ,BIM_SET_OF_BOOKS.get_fiscal_month(inner.load_date,204)
2391 ,BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.load_date,204)
2392 ,BIM_SET_OF_BOOKS.get_fiscal_year(inner.load_date,204)
2393 FROM
2394 (SELECT fund_id fund_id
2395 ,business_unit_id business_unit_id
2396 ,util_org_id util_org_id
2397 ,object_id object_id
2398 ,object_type object_type
2399 ,SUM(standard_discount) standard_discount
2400 ,SUM(accrual) accrual
2401 ,SUM(market_expense) market_expense
2402 ,SUM(commited_amt) commited_amt
2403 ,SUM(planned_amt) planned_amt
2404 ,SUM(paid_amt) paid_amt
2405 ,load_date load_date
2406 FROM bim_r_fdsp_daily_facts
2407 GROUP BY load_date
2408 ,business_unit_id
2409 ,object_id
2410 ,object_type
2411 ,fund_id
2412 ,util_org_id) inner;
2413 EXECUTE IMMEDIATE 'commit';
2414 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_weekly_facts_s CACHE 20';
2415 EXCEPTION
2416 when others THEN
2417 FND_FILE.put_line(fnd_file.log,'error insert fdsp_weekly'||sqlerrm(sqlcode));
2418 -- dbms_output.put_line('error inserting INTO bim_r_fdsp_weekly_facts'||sqlerrm(sqlcode));
2419 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_weekly_facts_s CACHE 20';
2420 x_return_status := FND_API.G_RET_STS_ERROR;
2421 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
2422 FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
2423 FND_MSG_PUB.Add;
2424 END;
2425
2426 fnd_message.set_name('BIM','BIM_R_RECREATE_INDEXES');
2427 fnd_file.put_line(fnd_file.log,fnd_message.get);
2428 /* Piece of Code for Recreating the index on the same tablespace with the same storage parameters */
2429 BEGIN
2430 i := i - 1;
2431 WHILE(i>=1) LOOP
2432
2433 IF (i>1) AND (l_index_name(i) =l_index_name(i-1)) THEN
2434 l_col_num :=l_col_num||l_ind_column_name(i)||',';
2435 ELSE
2436 l_col_num :=l_col_num||l_ind_column_name(i);
2437
2438 EXECUTE IMMEDIATE 'CREATE INDEX '
2439 || l_owner(i)
2440 || '.'
2441 || l_index_name(i)
2442 ||' ON '
2443 || l_owner(i)
2444 ||'.'
2445 || l_index_table_name(i)
2446 || ' ('
2447 || l_col_num
2448 || ' )'
2449 || ' tablespace ' || l_index_tablespace
2450 || ' pctfree ' || l_pct_free(i)
2451 || ' initrans ' || l_ini_trans(i)
2452 || ' maxtrans ' || l_max_trans(i)
2453 || ' storage ( '
2454 || ' initial ' || l_initial_extent(i)
2455 || ' next ' || l_next_extent(i)
2456 || ' minextents ' || l_min_extents(i)
2457 || ' maxextents ' || l_max_extents(i)
2458 || ' pctincrease ' || l_pct_increase(i)
2459 || ')'
2460 || ' compute statistics';
2461 l_col_num :='';
2462 END IF;
2463 i := i - 1;
2464 END LOOP;
2465 EXCEPTION
2466 WHEN OTHERS THEN
2467 null;
2468 --FND_FILE.put_line(fnd_file.log,'error creating all indexes'||sqlerrm(sqlcode));
2469 -- DBMS_OUTPUT.PUT_LINE(sqlerrm(sqlcode));
2470 END;
2471
2472 -- For performance reasons.
2473 BEGIN
2474 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_weekly_facts noparallel';
2475 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_weekly_facts noparallel';
2476 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_balance noparallel';
2477 EXCEPTION
2478 WHEN OTHERS THEN
2479 null;
2480 END;
2481 BEGIN
2482 DBMS_STATS.gather_table_stats('BIM','BIM_R_FUND_DAILY_FACTS', estimate_percent => 5,
2483 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
2484 END;
2485 BEGIN
2486 DBMS_STATS.gather_table_stats('BIM','BIM_R_FUND_WEEKLY_FACTS', estimate_percent => 5,
2487 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
2488 END;
2489 BEGIN
2490 DBMS_STATS.gather_table_stats('BIM','BIM_R_FDSP_DAILY_FACTS', estimate_percent => 5,
2491 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
2492 END;
2493 BEGIN
2494 DBMS_STATS.gather_table_stats('BIM','BIM_R_FDSP_WEEKLY_FACTS', estimate_percent => 5,
2495 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
2496 END;
2497 fnd_message.set_name('BIM','BIM_R_CALL_PROC');
2498 fnd_message.set_token('proc_name', 'UPDATE_BALANCE', FALSE);
2499 fnd_file.put_line(fnd_file.log,fnd_message.get);
2500 update_balance;
2501 EXECUTE IMMEDIATE 'COMMIT';
2502 fnd_message.set_name('BIM','BIM_R_PROG_COMPLETION');
2503 fnd_message.set_token('program_name', 'Fund first load', FALSE);
2504 fnd_file.put_line(fnd_file.log,fnd_message.get);
2505 END FUND_FIRST_LOAD;
2506
2507
2508 ---------------------------------------------------------------------
2509 -- PROCEDURE
2510 -- FUND_SUB_LOAD
2511 -- NOTE This procedure will be executed when load data subsequently.
2512 -- p_start_datel is replaced by the maximum loading date from
2513 -- history table,
2514 -- and transactions happened before fund active data are also captured.
2515 ---------------------------------------------------------------------
2516 PROCEDURE FUND_SUB_LOAD(
2517 p_start_datel DATE,
2518 p_end_datel DATE,
2519 p_para_num NUMBER ,
2520 x_msg_count OUT NOCOPY NUMBER ,
2521 x_msg_data OUT NOCOPY VARCHAR2 ,
2522 x_return_status OUT NOCOPY VARCHAR2
2523 )
2524 IS
2525 l_user_id NUMBER := FND_GLOBAL.USER_ID();
2526 l_last_update_date DATE;
2527 l_success varchar2(1) := 'F';
2528 l_api_version_number CONSTANT NUMBER := 1.0;
2529 l_api_name CONSTANT VARCHAR2(30) := 'FUND_SUB_LOAD';
2530 l_table_name VARCHAR2(100);
2531 l_count NUMBER:=0;
2532 l_end_date DATE;
2533
2534
2535
2536 l_status VARCHAR2(5);
2537 l_industry VARCHAR2(5);
2538 l_schema VARCHAR2(30);
2539 l_return BOOLEAN;
2540
2541 BEGIN
2542 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
2543
2544 -- Standard call to check for call compatibility.
2545 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2546 l_api_version_number,
2547 l_api_name,
2548 G_PKG_NAME)
2549 THEN
2550 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2551 END IF;
2552
2553 -- Debug Message
2554 --AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
2555
2556 -- Initialize API RETURN status to SUCCESS
2557 x_return_status := FND_API.G_RET_STS_SUCCESS;
2558
2559 -- Make sure p_end_date is not greate than sysdate-1
2560 IF (trunc(p_end_datel) =trunc(sysdate)) THEN
2561 l_end_date :=trunc(sysdate-1);
2562 ELSE
2563 l_end_date :=p_end_datel;
2564 END IF;
2565 l_table_name :='bim_r_fund_daily_facts';
2566 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
2567 fnd_message.set_token('table_name', l_table_name, FALSE);
2568 fnd_file.put_line(fnd_file.log,fnd_message.get);
2569 --dbms_output.put_line('b4 first inserting into fund daily');
2570
2571 BEGIN -- Alter table for performance reasons
2572 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_daily_facts nologging';
2573 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_weekly_facts nologging';
2574 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_daily_facts nologging';
2575 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_weekly_facts nologging';
2576 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 1000';
2577
2578 /* First insert: Insert transactions happened between p_start_date and p_end_date. */
2579 INSERT /*+ append parallel(fdf,p_para_num) */
2580 INTO bim_r_fund_daily_facts fdf(
2581 fund_transaction_id
2582 ,creation_date
2583 ,last_update_date
2584 ,created_by
2585 ,last_updated_by
2586 ,last_update_login
2587 ,fund_id
2588 ,parent_fund_id
2589 ,fund_number
2590 ,start_date
2591 ,end_date
2592 ,start_period
2593 ,end_period
2594 ,set_of_books_id
2595 ,fund_type
2596 --,region
2597 ,country
2598 ,org_id
2599 ,category_id
2600 ,status
2601 ,original_budget
2602 ,transfer_in
2603 ,transfer_out
2604 ,holdback_amt
2605 ,currency_code_fc
2606 ,delete_flag
2607 ,transaction_create_date
2608 ,load_date
2609 ,fis_month
2610 ,fis_qtr
2611 ,fis_year
2612 ,business_unit_id)
2613 --,security_group_id)
2614 SELECT /*+ parallel(inner, p_para_num) */
2615 bim_r_fund_daily_facts_s.nextval,
2616 sysdate,
2617 sysdate,
2618 l_user_id,
2619 l_user_id,
2620 l_user_id,
2621 inner.fund_id,
2622 inner.parent_fund_id,
2623 inner.fund_number,
2624 inner.start_date,
2625 inner.end_date,
2626 inner.start_period,
2627 inner.end_period,
2628 inner.set_of_books_id,
2629 inner.fund_type,
2630 --inner.region,
2631 inner.country,
2632 inner.org_id,
2633 inner.category_id,
2634 inner.status,
2635 inner.original_budget,
2636 inner.transfer_in,
2637 inner.transfer_out,
2638 inner.holdback,
2639 inner.currency_code_fc,
2640 'N',
2641 inner.transaction_create_date,
2642 inner.weekend_date,
2643 BIM_SET_OF_BOOKS.get_fiscal_month(inner.transaction_create_date,204),
2644 BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.transaction_create_date,204),
2645 BIM_SET_OF_BOOKS.get_fiscal_year(inner.transaction_create_date,204),
2646 inner.business_unit_id
2647 FROM (
2648 SELECT fund_id fund_id,
2649 fund_number fund_number,
2650 start_date start_date,
2651 end_date end_date,
2652 start_period start_period,
2653 end_period end_period,
2654 category_id category_id,
2655 status status,
2656 fund_type fund_type,
2657 parent_fund_id parent_fund_id,
2658 country country,
2659 org_id org_id,
2660 business_unit_id business_unit_id,
2661 set_of_books_id set_of_books_id,
2662 currency_code_fc currency_code_fc,
2663 original_budget original_budget,
2664 transaction_create_date transaction_create_date,
2665 trunc((decode(decode( to_char(transaction_create_date,'MM') , to_char(next_day(transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
2666 ,'TRUE'
2667 ,decode(decode(transaction_create_date , (next_day(transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
2668 ,'TRUE'
2669 ,transaction_create_date
2670 ,'FALSE'
2671 ,next_day(transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
2672 ,'FALSE'
2673 ,decode(decode(to_char(transaction_create_date,'MM'),to_char(next_day(transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
2674 ,'FALSE'
2675 ,last_day(transaction_create_date))))) weekend_date,
2676 SUM(transfer_in) transfer_in,
2677 SUM(transfer_out) transfer_out,
2678 SUM(holdback) holdback
2679 FROM
2680 (SELECT ad.fund_id fund_id,
2681 ad.fund_number fund_number,
2682 ad.start_date_active start_date,
2683 ad.end_date_active end_date,
2684 ad.start_period_name start_period,
2685 ad.end_period_name end_period,
2686 ad.category_id category_id,
2687 ad.status_code status,
2688 ad.fund_type fund_type,
2689 ad.parent_fund_id parent_fund_id,
2690 ad.country_id country,
2691 ad.org_id org_id,
2692 ad.business_unit_id business_unit_id,
2693 ad.set_of_books_id set_of_books_id,
2694 ad.currency_code_fc currency_code_fc,
2695 ad.original_budget original_budget,
2696 trunc(bu1.approval_date) transaction_create_date,
2697 nvl(SUM(convert_currency(bu1.approved_in_currency,nvl(bu1.approved_original_amount,0))),0) transfer_in,
2698 0 transfer_out,
2699 0 holdback
2700 FROM ozf_funds_all_b ad,
2701 ozf_act_budgets BU1
2702 WHERE bu1.approval_date between p_start_datel and p_end_datel + 0.99999
2703 AND ad.status_code in ('ACTIVE', 'CANCELLED', 'CLOSED')
2704 AND bu1.transfer_type in ('TRANSFER','REQUEST')
2705 AND bu1.status_code(+) = 'APPROVED'
2706 AND bu1.arc_act_budget_used_by(+) = 'FUND'
2707 AND bu1.act_budget_used_by_id(+) = ad.fund_id
2708 AND bu1.budget_source_type(+) ='FUND'
2709 GROUP BY ad.fund_id,
2710 trunc(bu1.approval_date),
2711 ad.fund_number,
2712 ad.start_date_active ,
2713 ad.end_date_active ,
2714 ad.start_period_name ,
2715 ad.end_period_name ,
2716 ad.category_id ,
2717 ad.status_code ,
2718 ad.fund_type ,
2719 ad.parent_fund_id,
2720 ad.country_id,
2721 ad.business_unit_id,
2722 ad.org_id ,
2723 ad.set_of_books_id ,
2724 ad.currency_code_fc ,
2725 ad.original_budget
2726 UNION ALL
2727 SELECT ad.fund_id fund_id,
2728 ad.fund_number fund_number,
2729 ad.start_date_active start_date,
2730 ad.end_date_active end_date,
2731 ad.start_period_name start_period,
2732 ad.end_period_name end_period,
2733 ad.category_id category_id,
2734 ad.status_code status,
2735 ad.fund_type fund_type,
2736 ad.parent_fund_id parent_fund_id,
2737 ad.country_id country,
2738 ad.org_id org_id,
2739 ad.business_unit_id business_unit_id,
2740 ad.set_of_books_id set_of_books_id,
2741 ad.currency_code_fc currency_code_fc,
2742 ad.original_budget original_budget,
2743 TRUNC(bu2.approval_date) transaction_create_date,
2744 0 transfer_in,
2745 nvl(SUM(decode(bu2.transfer_type,'TRANSFER', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0)
2746 +nvl(SUM(decode(bu2.transfer_type,'REQUEST', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0) transfer_out,
2747 nvl(SUM(decode(bu2.transfer_type, 'RESERVE', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0)-
2748 nvl(SUM(decode(bu2.transfer_type, 'RELEASE', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0) holdback
2749 FROM ozf_funds_all_b ad,
2750 ozf_act_budgets BU2
2751 WHERE bu2.approval_date between p_start_datel and p_end_datel + 0.99999
2752 AND ad.status_code in ('ACTIVE', 'CANCELLED', 'CLOSED')
2753 AND bu2.status_code = 'APPROVED'
2754 AND bu2.arc_act_budget_used_by = 'FUND'
2755 AND bu2.budget_source_type ='FUND'
2756 AND bu2.budget_source_id = ad.fund_id
2757 GROUP BY ad.fund_id,
2758 trunc(bu2.approval_date) ,
2759 ad.fund_number,
2760 ad.start_date_active ,
2761 ad.end_date_active ,
2762 ad.start_period_name ,
2763 ad.end_period_name ,
2764 ad.category_id ,
2765 ad.status_code ,
2766 ad.fund_type ,
2767 ad.parent_fund_id,
2768 ad.country_id,
2769 ad.org_id ,
2770 ad.business_unit_id,
2771 ad.set_of_books_id ,
2772 ad.currency_code_fc ,
2773 ad.original_budget)
2774 GROUP BY
2775 fund_id,
2776 transaction_create_date,
2777 fund_number,
2778 start_date,
2779 end_date,
2780 start_period,
2781 end_period,
2782 category_id,
2783 status,
2784 fund_type,
2785 parent_fund_id,
2786 country,
2787 org_id,
2788 business_unit_id,
2789 set_of_books_id,
2790 currency_code_fc,
2791 original_budget
2792 )inner;
2793 l_count:=l_count+SQL%ROWCOUNT;
2794 EXECUTE IMMEDIATE 'COMMIT';
2795 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 20';
2796 EXCEPTION
2797 WHEN others THEN
2798 FND_FILE.put_line(fnd_file.log,'error insert into fund_daily'||sqlerrm(sqlcode));
2799 --dbms_output.put_line('error inserting INTO bim_r_fund_daily_facts'||sqlerrm(sqlcode));
2800 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 20';
2801 x_return_status := FND_API.G_RET_STS_ERROR;
2802 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
2803 FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
2804 FND_MSG_PUB.Add;
2805 END;
2806
2807 /*Second insert: For funds whose active date is between p_start_date and p_end_date, insert
2808 transactions happened before active date */
2809 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
2810 fnd_message.set_token('table_name', l_table_name, FALSE);
2811 fnd_file.put_line(fnd_file.log,fnd_message.get);
2812 BEGIN
2813 INSERT /*+ append parallel(fdf,p_para_num) */
2814 INTO bim_r_fund_daily_facts fdf(
2815 fund_transaction_id
2816 ,creation_date
2817 ,last_update_date
2818 ,created_by
2819 ,last_updated_by
2820 ,last_update_login
2821 ,fund_id
2822 ,parent_fund_id
2823 ,fund_number
2824 ,start_date
2825 ,end_date
2826 ,start_period
2827 ,end_period
2828 ,set_of_books_id
2829 ,fund_type
2830 --,region
2831 ,country
2832 ,org_id
2833 ,category_id
2834 ,status
2835 ,original_budget
2836 ,transfer_in
2837 ,transfer_out
2838 ,holdback_amt
2839 ,currency_code_fc
2840 ,delete_flag
2841 ,transaction_create_date
2842 ,load_date
2843 ,fis_month
2844 ,fis_qtr
2845 ,fis_year
2846 ,business_unit_id)
2847 --,security_group_id)
2848 SELECT /*+ parallel(inner, p_para_num) */
2849 bim_r_fund_daily_facts_s.nextval,
2850 sysdate,
2851 sysdate,
2852 l_user_id,
2853 l_user_id,
2854 l_user_id,
2855 inner.fund_id,
2856 inner.parent_fund_id,
2857 inner.fund_number,
2858 inner.start_date,
2859 inner.end_date,
2860 inner.start_period,
2861 inner.end_period,
2862 inner.set_of_books_id,
2863 inner.fund_type,
2864 --inner.region,
2865 inner.country,
2866 inner.org_id,
2867 inner.category_id,
2868 inner.status,
2869 inner.original_budget,
2870 inner.transfer_in,
2871 inner.transfer_out,
2872 inner.holdback,
2873 inner.currency_code_fc,
2874 'N',
2875 inner.transaction_create_date,
2876 inner.weekend_date,
2877 BIM_SET_OF_BOOKS.get_fiscal_month(inner.transaction_create_date,204),
2878 BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.transaction_create_date,204),
2879 BIM_SET_OF_BOOKS.get_fiscal_year(inner.transaction_create_date,204),
2880 inner.business_unit_id
2881 FROM (
2882 SELECT fund_id fund_id,
2883 fund_number fund_number,
2884 start_date start_date,
2885 end_date end_date,
2886 start_period start_period,
2887 end_period end_period,
2888 category_id category_id,
2889 status status,
2890 fund_type fund_type,
2891 parent_fund_id parent_fund_id,
2892 country country,
2893 org_id org_id,
2894 business_unit_id business_unit_id,
2895 set_of_books_id set_of_books_id,
2896 currency_code_fc currency_code_fc,
2897 original_budget original_budget,
2898 transaction_create_date transaction_create_date,
2899 trunc((decode(decode( to_char(transaction_create_date,'MM') , to_char(next_day(transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
2900 ,'TRUE'
2901 ,decode(decode(transaction_create_date , (next_day(transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
2902 ,'TRUE'
2903 ,transaction_create_date
2904 ,'FALSE'
2905 ,next_day(transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
2906 ,'FALSE'
2907 ,decode(decode(to_char(transaction_create_date,'MM'),to_char(next_day(transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
2908 ,'FALSE'
2909 ,last_day(transaction_create_date))))) weekend_date,
2910 SUM(transfer_in) transfer_in,
2911 SUM(transfer_out) transfer_out,
2912 SUM(holdback) holdback
2913 FROM
2914 (SELECT ad.fund_id fund_id,
2915 ad.fund_number fund_number,
2916 ad.start_date_active start_date,
2917 ad.end_date_active end_date,
2918 ad.start_period_name start_period,
2919 ad.end_period_name end_period,
2920 ad.category_id category_id,
2921 ad.status_code status,
2922 ad.fund_type fund_type,
2923 ad.parent_fund_id parent_fund_id,
2924 ad.country_id country,
2925 ad.org_id org_id,
2926 ad.business_unit_id business_unit_id,
2927 ad.set_of_books_id set_of_books_id,
2928 ad.currency_code_fc currency_code_fc,
2929 ad.original_budget original_budget,
2930 trunc(bu1.approval_date) transaction_create_date,
2931 nvl(SUM(convert_currency(bu1.approved_in_currency,nvl(bu1.approved_original_amount,0))),0) transfer_in,
2932 0 transfer_out,
2933 0 holdback
2934 FROM ozf_funds_all_b ad,
2935 ozf_act_budgets BU1
2936 WHERE bu1.approval_date < p_start_datel
2937 AND ad.status_code in ('ACTIVE', 'CANCELLED', 'CLOSED')
2938 AND ad.start_date_active between p_start_datel and p_end_datel
2939 AND bu1.transfer_type in ('TRANSFER','REQUEST')
2940 AND bu1.status_code = 'APPROVED'
2941 AND bu1.arc_act_budget_used_by = 'FUND'
2942 AND bu1.act_budget_used_by_id = ad.fund_id
2943 AND bu1.budget_source_type ='FUND'
2944 GROUP BY ad.fund_id,
2945 trunc(bu1.approval_date),
2946 ad.fund_number,
2947 ad.start_date_active ,
2948 ad.end_date_active ,
2949 ad.start_period_name ,
2950 ad.end_period_name ,
2951 ad.category_id ,
2952 ad.status_code ,
2953 ad.fund_type ,
2954 ad.parent_fund_id,
2955 ad.country_id,
2956 ad.business_unit_id,
2957 ad.org_id ,
2958 ad.set_of_books_id ,
2959 ad.currency_code_fc ,
2960 ad.original_budget
2961 UNION ALL
2962 SELECT ad.fund_id fund_id,
2963 ad.fund_number fund_number,
2964 ad.start_date_active start_date,
2965 ad.end_date_active end_date,
2966 ad.start_period_name start_period,
2967 ad.end_period_name end_period,
2968 ad.category_id category_id,
2969 ad.status_code status,
2970 ad.fund_type fund_type,
2971 ad.parent_fund_id parent_fund_id,
2972 ad.country_id country,
2973 ad.org_id org_id,
2974 ad.business_unit_id business_unit_id,
2975 ad.set_of_books_id set_of_books_id,
2976 ad.currency_code_fc currency_code_fc,
2977 ad.original_budget original_budget,
2978 TRUNC(bu2.approval_date) transaction_create_date,
2979 0 transfer_in,
2980 nvl(SUM(decode(bu2.transfer_type,'TRANSFER', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0)
2981 +nvl(SUM(decode(bu2.transfer_type,'REQUEST', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0) transfer_out,
2982 nvl(SUM(decode(bu2.transfer_type, 'RESERVE', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0)-
2983 nvl(SUM(decode(bu2.transfer_type, 'RELEASE', convert_currency(bu2.approved_in_currency,nvl(bu2.approved_original_amount,0)))),0) holdback
2984 FROM ozf_funds_all_b ad,
2985 ozf_act_budgets BU2
2986 WHERE bu2.approval_date < p_start_datel
2987 AND ad.status_code in ('ACTIVE', 'CANCELLED', 'CLOSED')
2988 AND ad.start_date_active between p_start_datel and p_end_datel
2989 AND bu2.status_code = 'APPROVED'
2990 AND bu2.arc_act_budget_used_by = 'FUND'
2991 AND bu2.budget_source_type ='FUND'
2992 AND bu2.budget_source_id = ad.fund_id
2993 GROUP BY ad.fund_id,
2994 trunc(bu2.approval_date) ,
2995 ad.fund_number,
2996 ad.start_date_active ,
2997 ad.end_date_active ,
2998 ad.start_period_name ,
2999 ad.end_period_name ,
3000 ad.category_id ,
3001 ad.status_code ,
3002 ad.fund_type ,
3003 ad.parent_fund_id,
3004 ad.country_id,
3005 ad.org_id ,
3006 ad.business_unit_id,
3007 ad.set_of_books_id ,
3008 ad.currency_code_fc ,
3009 ad.original_budget)
3010 GROUP BY
3011 fund_id,
3012 transaction_create_date,
3013 fund_number,
3014 start_date,
3015 end_date,
3016 start_period,
3017 end_period,
3018 category_id,
3019 status,
3020 fund_type,
3021 parent_fund_id,
3022 country,
3023 org_id,
3024 business_unit_id,
3025 set_of_books_id,
3026 currency_code_fc,
3027 original_budget
3028 )inner;
3029 l_count:=l_count+SQL%ROWCOUNT;
3030 EXECUTE IMMEDIATE 'commit';
3031 --End inserting transactions happened before start_date
3032 EXCEPTION
3033 WHEN others THEN
3034 FND_FILE.put_line(fnd_file.log,'error insert into fund_daily for transactions happened b4 start date'||sqlerrm(sqlcode));
3035 --dbms_output.put_line('error looping inserting INTO bim_r_fund_daily_facts'||sqlerrm(sqlcode));
3036 x_return_status := FND_API.G_RET_STS_ERROR;
3037 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
3038 FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
3039 FND_MSG_PUB.Add;
3040 END;
3041
3042 /* insert dummy 0s for dates between p_start_date/start_date_active and p_end_date/end_date_active,
3043 but has no transactions.*/
3044 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
3045 fnd_message.set_token('table_name', l_table_name, FALSE);
3046 fnd_file.put_line(fnd_file.log,fnd_message.get);
3047 BEGIN
3048 INSERT /*+ append parallel(fdf,p_para_num) */
3049 INTO bim_r_fund_daily_facts fdf(
3050 fund_transaction_id
3051 ,creation_date
3052 ,last_update_date
3053 ,created_by
3054 ,last_updated_by
3055 ,last_update_login
3056 ,fund_id
3057 ,parent_fund_id
3058 ,fund_number
3059 ,start_date
3060 ,end_date
3061 ,start_period
3062 ,end_period
3063 ,set_of_books_id
3064 ,fund_type
3065 --,region
3066 ,country
3067 ,org_id
3068 ,category_id
3069 ,status
3070 ,original_budget
3071 ,transfer_in
3072 ,transfer_out
3073 ,holdback_amt
3074 ,currency_code_fc
3075 ,delete_flag
3076 ,transaction_create_date
3077 ,load_date
3078 ,fis_month
3079 ,fis_qtr
3080 ,fis_year
3081 ,business_unit_id)
3082 --,security_group_id)
3083 SELECT /*+ parallel(inner, p_para_num) */
3084 bim_r_fund_daily_facts_s.nextval,
3085 sysdate,
3086 sysdate,
3087 l_user_id,
3088 l_user_id,
3089 l_user_id,
3090 inner.fund_id,
3091 inner.parent_fund_id,
3092 inner.fund_number,
3093 inner.start_date,
3094 inner.end_date,
3095 inner.start_period,
3096 inner.end_period,
3097 inner.set_of_books_id,
3098 inner.fund_type,
3099 --inner.region,
3100 inner.country,
3101 inner.org_id,
3102 inner.category_id,
3103 inner.status,
3104 inner.original_budget,
3105 0,
3106 0,
3107 0,
3108 inner.currency_code_fc,
3109 'N',
3110 inner.trdate,
3111 trunc((decode(decode( to_char(inner.trdate,'MM') , to_char(next_day(inner.trdate,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
3112 ,'TRUE'
3113 ,decode(decode(inner.trdate , (next_day(inner.trdate, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
3114 ,'TRUE'
3115 ,inner.trdate
3116 ,'FALSE'
3117 ,next_day(inner.trdate, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
3118 ,'FALSE'
3119 ,decode(decode(to_char(inner.trdate,'MM'),to_char(next_day(inner.trdate,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
3120 ,'FALSE'
3121 ,last_day(inner.trdate))))) ,
3122 BIM_SET_OF_BOOKS.get_fiscal_month(inner.trdate,204),
3123 BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.trdate,204),
3124 BIM_SET_OF_BOOKS.get_fiscal_year(inner.trdate,204),
3125 inner.business_unit_id
3126 FROM (
3127 SELECT distinct a.fund_id fund_id,
3128 a.fund_number fund_number,
3129 a.start_date start_date,
3130 a.end_date end_date,
3131 a.start_period start_period,
3132 a.end_period end_period,
3133 a.category_id category_id,
3134 a.status status,
3135 a.fund_type fund_type,
3136 a.parent_fund_id parent_fund_id,
3137 a.business_unit_id business_unit_id,
3138 a.country country,
3139 a.org_id org_id,
3140 a.set_of_books_id set_of_books_id,
3141 a.currency_code_fc currency_code_fc,
3142 a.original_budget original_budget,
3143 trunc(b.trdate) trdate
3144 FROM bim_r_fund_daily_facts a,
3145 bim_intl_dates b
3146 WHERE b.trdate between p_start_datel and p_end_datel+0.99999
3147 AND b.trdate between a.start_date and nvl(a.end_date,p_end_datel)+0.99999
3148 AND (a.fund_id, trunc(b.trdate)) not in (select c.fund_id, c.transaction_create_date
3149 from bim_r_fund_daily_facts c
3150 where c.fund_id = a.fund_id
3151 and c.transaction_create_date = trunc(b.trdate))) inner;
3152 l_count :=l_count +SQL%ROWCOUNT;
3153 EXECUTE IMMEDIATE 'commit';
3154 EXCEPTION
3155 WHEN others THEN
3156 FND_FILE.put_line(fnd_file.log,'error insert into fund_daily for missing date'||sqlerrm(sqlcode));
3157 --dbms_output.put_line('error insert into fund_daily for missing date'||sqlerrm(sqlcode));
3158 x_return_status := FND_API.G_RET_STS_ERROR;
3159 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
3160 FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
3161 FND_MSG_PUB.Add;
3162 END;
3163
3164 /* insert dummy 0s for funds created between p_start_datel and p_end_datel,
3165 and in those dates between p_start_date/start_date_active and p_end_date/end_date_active,
3166 but has no transactions.*/
3167 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
3168 fnd_message.set_token('table_name', l_table_name, FALSE);
3169 fnd_file.put_line(fnd_file.log,fnd_message.get);
3170 BEGIN
3171 INSERT /*+ append parallel(fdf,p_para_num) */
3172 INTO bim_r_fund_daily_facts fdf(
3173 fund_transaction_id
3174 ,creation_date
3175 ,last_update_date
3176 ,created_by
3177 ,last_updated_by
3178 ,last_update_login
3179 ,fund_id
3180 ,parent_fund_id
3181 ,fund_number
3182 ,start_date
3183 ,end_date
3184 ,start_period
3185 ,end_period
3186 ,set_of_books_id
3187 ,fund_type
3188 --,region
3189 ,country
3190 ,org_id
3191 ,category_id
3192 ,status
3193 ,original_budget
3194 ,transfer_in
3195 ,transfer_out
3196 ,holdback_amt
3197 ,currency_code_fc
3198 ,delete_flag
3199 ,transaction_create_date
3200 ,load_date
3201 ,fis_month
3202 ,fis_qtr
3203 ,fis_year
3204 ,business_unit_id)
3205 --,security_group_id)
3206 SELECT /*+ parallel(inner, p_para_num) */
3207 bim_r_fund_daily_facts_s.nextval,
3208 sysdate,
3209 sysdate,
3210 l_user_id,
3211 l_user_id,
3212 l_user_id,
3213 inner.fund_id,
3214 inner.parent_fund_id,
3215 inner.fund_number,
3216 inner.start_date,
3217 inner.end_date,
3218 inner.start_period,
3219 inner.end_period,
3220 inner.set_of_books_id,
3221 inner.fund_type,
3222 --inner.region,
3223 inner.country,
3224 inner.org_id,
3225 inner.category_id,
3226 inner.status,
3227 inner.original_budget,
3228 0,
3229 0,
3230 0,
3231 inner.currency_code_fc,
3232 'N',
3233 inner.trdate,
3234 trunc((decode(decode( to_char(inner.trdate,'MM') , to_char(next_day(inner.trdate,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
3235 ,'TRUE'
3236 ,decode(decode(inner.trdate , (next_day(inner.trdate, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
3237 ,'TRUE'
3238 ,inner.trdate
3239 ,'FALSE'
3240 ,next_day(inner.trdate, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
3241 ,'FALSE'
3242 ,decode(decode(to_char(inner.trdate,'MM'),to_char(next_day(inner.trdate,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
3243 ,'FALSE'
3244 ,last_day(inner.trdate))))) ,
3245 BIM_SET_OF_BOOKS.get_fiscal_month(inner.trdate,204),
3246 BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.trdate,204),
3247 BIM_SET_OF_BOOKS.get_fiscal_year(inner.trdate,204),
3248 inner.business_unit_id
3249 FROM (
3250 SELECT ad.fund_id fund_id,
3251 ad.fund_number fund_number,
3252 ad.start_date_active start_date,
3253 ad.end_date_active end_date,
3254 ad.start_period_name start_period,
3255 ad.end_period_name end_period,
3256 ad.category_id category_id,
3257 ad.status_code status,
3258 ad.fund_type fund_type,
3259 ad.parent_fund_id parent_fund_id,
3260 ad.country_id country,
3261 ad.org_id org_id,
3262 ad.business_unit_id business_unit_id,
3263 ad.set_of_books_id set_of_books_id,
3264 ad.currency_code_fc currency_code_fc,
3265 ad.original_budget original_budget,
3266 trunc(b.trdate) trdate,
3267 0 transfer_in,
3268 0 transfer_out,
3269 0 holdback
3270 FROM ozf_funds_all_b ad,
3271 bim_intl_dates b
3272 WHERE ad.status_code in ('ACTIVE', 'CANCELLED', 'CLOSED')
3273 AND ad.start_date_active between p_start_datel and p_end_datel
3274 AND b.trdate between p_start_datel and p_end_datel+0.99999
3275 AND b.trdate between ad.start_date_active and nvl(ad.end_date_active,p_end_datel)+0.99999
3276 AND (ad.fund_id, trunc(b.trdate)) not in (select c.fund_id, c.transaction_create_date
3277 from bim_r_fund_daily_facts c
3278 where c.fund_id = ad.fund_id
3279 and c.transaction_create_date = trunc(b.trdate))) inner;
3280 l_count :=l_count +SQL%ROWCOUNT;
3281 EXECUTE IMMEDIATE 'commit';
3282 EXCEPTION
3283 WHEN others THEN
3284 FND_FILE.put_line(fnd_file.log,'error insert into fund_daily for missing date 2'||sqlerrm(sqlcode));
3285 --dbms_output.put_line('error insert into fund_daily for missing date 2'||sqlerrm(sqlcode));
3286 x_return_status := FND_API.G_RET_STS_ERROR;
3287 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
3288 FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
3289 FND_MSG_PUB.Add;
3290 END;
3291
3292 /* Inserting into 'bim_r_fdsp_load' all the objects information */
3293 l_table_name :='bim_r_fdsp_daily_facts';
3294 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
3295 fnd_message.set_token('table_name', 'bim_r_fdsp_load', FALSE);
3296 fnd_file.put_line(fnd_file.log,fnd_message.get);
3297 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 1000';
3298 EXECUTE IMMEDIATE 'truncate table '||l_schema||'.bim_r_fdsp_load';
3299 BEGIN
3300 INSERT /*+ append parallel(bfl,p_para_num) */
3301 INTO bim_r_fdsp_load bfl(
3302 spend_transaction_id
3303 ,creation_date
3304 ,last_update_date
3305 ,created_by
3306 ,last_updated_by
3307 ,last_update_login
3308 ,fund_id
3309 ,business_unit_id
3310 ,util_org_id
3311 ,standard_discount
3312 ,accrual
3313 ,market_expense
3314 ,commited_amt
3315 ,planned_amt
3316 ,paid_amt
3317 ,delete_flag
3318 ,transaction_create_date
3319 ,load_date
3320 ,object_id
3321 ,object_type)
3322 SELECT /*+ parallel(act_util, p_para_num) */
3323 0,
3324 sysdate,
3325 sysdate,
3326 -1,
3327 -1,
3328 -1,
3329 act_util.fund_id,
3330 0,
3331 0,
3332 act_util.standard_discount,
3333 act_util.accrual,
3334 act_util.market_expense,
3335 act_util.commited_amt,
3336 act_util.planned_amt,
3337 act_util.paid_amt,
3338 'Y',
3339 act_util.creation_date,
3340 sysdate,
3341 act_util.object_id,
3342 act_util.object_type
3343 FROM (SELECT fund_id fund_id,
3344 object_id object_id,
3345 object_type object_type,
3346 creation_date creation_date,
3347 SUM(nvl(planned_amt,0)) planned_amt,
3348 SUM(nvl(commited_amt,0)) commited_amt,
3349 SUM(nvl(standard_discount,0)) standard_discount,
3350 SUM(nvl(accrual,0)) accrual,
3351 SUM(nvl(market_expense,0)) market_expense,
3352 SUM(nvl(paid_amt,0)) paid_amt
3353 FROM (
3354 SELECT budget_source_id fund_id,
3355 act_budget_used_by_id object_id,
3356 arc_act_budget_used_by object_type,
3357 trunc(nvl(request_date,creation_date)) creation_date,
3358 SUM(convert_currency(request_currency, nvl(request_amount,0))) planned_amt,
3359 0 commited_amt,
3360 0 standard_discount,
3361 0 accrual,
3362 0 market_expense,
3363 0 paid_amt
3364 FROM ozf_act_budgets
3365 WHERE budget_source_type ='FUND'
3366 AND status_code ='PENDING'
3367 AND ARC_ACT_BUDGET_USED_BY <> 'FUND'
3368 GROUP BY trunc(nvl(request_date ,creation_date)),
3369 budget_source_id,act_budget_used_by_id,
3370 arc_act_budget_used_by
3371 UNION ALL
3372 SELECT budget_source_id fund_id,
3373 act_budget_used_by_id object_id,
3374 arc_act_budget_used_by object_type,
3375 trunc(nvl(approval_date,last_update_date)) creation_date,
3376 0-SUM(convert_currency(request_currency, nvl(request_amount,0))) planned_amt,
3377 SUM(convert_currency(approved_in_currency,nvl(approved_original_amount,0))) commited_amt,
3378 0 standard_discount,
3379 0 accrual,
3380 0 market_expense,
3381 0 paid_amt
3382 FROM ozf_act_budgets
3383 WHERE budget_source_type ='FUND'
3384 AND ARC_ACT_BUDGET_USED_BY <> 'FUND'
3385 AND status_code ='APPROVED'
3386 GROUP BY trunc(nvl(approval_date,last_update_date)),
3387 budget_source_id,act_budget_used_by_id,
3388 arc_act_budget_used_by
3389 UNION ALL
3390 SELECT act_budget_used_by_id fund_id,
3391 budget_source_id object_id,
3392 budget_source_type object_type,
3393 trunc(nvl(approval_date,last_update_date)) creation_date,
3394 0 planned_amt,
3395 0-SUM(convert_currency(approved_in_currency,nvl(approved_original_amount,0))) commited_amt,
3396 0 standard_discount,
3397 0 accrual,
3398 0 market_expense,
3399 0 paid_amt
3400 FROM ozf_act_budgets
3401 WHERE arc_act_budget_used_by ='FUND'
3402 AND budget_source_type<>'FUND'
3403 AND status_code ='APPROVED'
3404 GROUP BY trunc(nvl(approval_date,last_update_date)),
3405 act_budget_used_by_id, budget_source_id,
3406 budget_source_type
3407 UNION ALL
3408 SELECT fund_id fund_id,
3409 plan_id object_id,
3410 plan_type object_type,
3411 trunc(creation_date) creation_date,
3412 0 planned_amt,
3413 0 commited_amt,
3414 SUM(decode(component_type,'OFFR',decode(utilization_type, 'UTILIZED',convert_currency(currency_code,nvl(amount,0)), 0),0)) standard_discount,
3415 SUM(decode(component_type,'OFFR', decode(utilization_type, 'ACCRUAL', convert_currency(currency_code,nvl(amount,0)), 0),0) +
3416 decode(component_type,'OFFR', decode(utilization_type, 'ADJUSTMENT', convert_currency(currency_code,nvl(amount,0)), 0),0)) accrual,
3417 SUM(decode(component_type,'OFFR',0, decode(utilization_type, 'UTILIZED', convert_currency(currency_code,nvl(amount,0)), 0))) market_expense,
3418 sum(decode(component_type,'OFFR',0,convert_currency(currency_code,(nvl(amount,0)-NVL(amount_remaining,0))))) paid_amt
3419 FROM ozf_funds_utilized_all_b
3420 WHERE utilization_type in ('UTILIZED','ACCRUAL','ADJUSTMENT')
3421 GROUP BY trunc(creation_date),fund_id,plan_id,plan_type
3422 )
3423 GROUP BY creation_date, fund_id, object_id,object_type
3424 ) act_util;
3425 EXECUTE IMMEDIATE 'COMMIT';
3426 EXCEPTION
3427 WHEN OTHERS THEN
3428 FND_FILE.put_line(fnd_file.log,'error insert fdsp daily'||sqlerrm(sqlcode));
3429 --dbms_output.put_line('error inserting INTO bim_r_fdsp_daily_facts'||sqlerrm(sqlcode));
3430 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';
3431 x_return_status := FND_API.G_RET_STS_ERROR;
3432 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
3433 FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
3434 FND_MSG_PUB.Add;
3435 END ; --end of insertion into bim_r_fdsp_load.
3436
3437 --dbms_output.put_line('inside fist inserting into fdsp daily');
3438 /* First insert:Insert into 'bim_r_fdsp_daily_facts' the transactions happend between
3439 p_start_date and p_end_date */
3440 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
3441 fnd_message.set_token('table_name', l_table_name, FALSE);
3442 fnd_file.put_line(fnd_file.log,fnd_message.get);
3443 BEGIN
3444 INSERT /*+ append parallel(fdf,p_para_num) */
3445 INTO bim_r_fdsp_daily_facts fdf(
3446 spend_transaction_id
3447 ,creation_date
3448 ,last_update_date
3449 ,created_by
3450 ,last_updated_by
3451 ,last_update_login
3452 ,fund_id
3453 ,business_unit_id
3454 ,util_org_id
3455 ,standard_discount
3456 ,accrual
3457 ,market_expense
3458 ,commited_amt
3459 ,planned_amt
3460 ,paid_amt
3461 ,delete_flag
3462 ,transaction_create_date
3463 ,load_date
3464 ,object_id
3465 ,object_type
3466 ,fis_month
3467 ,fis_qtr
3468 ,fis_year
3469 )
3470 SELECT /*+ parallel(inner, p_para_num) */
3471 bim_r_fdsp_daily_facts_s.nextval,
3472 sysdate,
3473 sysdate,
3474 l_user_id,
3475 l_user_id,
3476 l_user_id,
3477 inner.fund_id,
3478 inner.business_unit_id,
3479 inner.org_id,
3480 inner.standard_discount,
3481 inner.accrual,
3482 inner.market_expense,
3483 inner.commited_amt,
3484 inner.planned_amt,
3485 inner.paid_amt,
3486 'N',
3487 inner.transaction_create_date,
3488 trunc((decode(decode( to_char(inner.transaction_create_date,'MM') , to_char(next_day(inner.transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
3489 ,'TRUE'
3490 ,decode(decode(inner.transaction_create_date , (next_day(inner.transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
3491 ,'TRUE'
3492 ,inner.transaction_create_date
3493 ,'FALSE'
3494 ,next_day(inner.transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
3495 ,'FALSE'
3496 ,decode(decode(to_char(inner.transaction_create_date,'MM'),to_char(next_day(inner.transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
3497 ,'FALSE'
3498 ,last_day(inner.transaction_create_date))))) weekend_date
3499 ,inner.object_id
3500 ,inner.object_type
3501 ,BIM_SET_OF_BOOKS.get_fiscal_month(inner.transaction_create_date,204)
3502 ,BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.transaction_create_date,204)
3503 ,BIM_SET_OF_BOOKS.get_fiscal_year(inner.transaction_create_date,204)
3504 FROM (SELECT AD.fund_id fund_id,
3505 U.business_unit_id business_unit_id,
3506 U.org_id org_id,
3507 NVL(AU.standard_discount,0) standard_discount,
3508 NVL(AU.accrual,0) accrual,
3509 NVL(AU.market_expense,0) market_expense,
3510 NVL(AU.commited_amt,0) commited_amt,
3511 NVL(AU.planned_amt,0) planned_amt,
3512 NVL(AU.paid_amt,0) paid_amt,
3513 AU.object_id object_id,
3514 U.object_type object_type,
3515 AU.transaction_create_date transaction_create_date
3516 FROM ozf_funds_all_b AD,
3517 bim_r_fdsp_load AU,
3518 (SELECT
3519 D.business_unit_id business_unit_id,D.org_id org_id,
3520 'CAMP' object_type_J,'CAMP' object_type, D.campaign_id object_id
3521 FROM ams_campaigns_all_b D
3522 UNION ALL
3523 SELECT
3524 D.business_unit_id business_unit_id,D.org_id org_id,
3525 'CSCH' object_type_J,'CSCH' object_type,B.SCHEDULE_ID object_id
3526 FROM
3527 ams_campaigns_all_b D,
3528 ams_campaign_schedules_b B
3529 WHERE B.campaign_id = D.campaign_id (+)
3530 UNION ALL
3531 SELECT
3532 D.business_unit_id business_unit_id,D.org_id org_id,
3533 'EVEH' object_type_J,'EVEH' object_type, D.event_header_id object_id
3534 FROM
3535 ams_event_headers_all_b D
3536 UNION ALL
3537 SELECT
3538 D.business_unit_id business_unit_id,D.org_id org_id,
3539 'EVEO' object_type_J, 'EVEO' object_type, D.event_offer_id object_id
3540 FROM
3541 ams_event_offers_all_b D
3542 WHERE event_header_id is not null
3543 UNION ALL
3544 SELECT
3545 D.business_unit_id business_unit_id,D.org_id org_id,
3546 'EONE' object_type_J, 'EONE' object_type, D.event_offer_id object_id
3547 FROM
3548 ams_event_offers_all_b D
3549 WHERE event_header_id is null
3550 UNION ALL
3551 SELECT
3552 BC.business_unit_id business_unit_id,BC.org_id org_id,
3553 'OFFR' object_type_J, 'OFFR' object_type, D.qp_list_header_id object_id
3554 FROM
3555 ams_campaigns_all_b BC,
3556 ams_act_offers D
3557 WHERE
3558 D.arc_act_offer_used_by (+) = 'CAMP' AND D.act_offer_used_by_id =
3559 BC.campaign_id (+) AND BC.show_campaign_flag (+) = 'Y'
3560 UNION ALL
3561 SELECT
3562 BA.business_unit_id business_unit_id,BA.org_id org_id,
3563 'DELV' object_type_J, 'CAMPDELV' object_type, D.using_object_id object_id
3564 FROM
3565 ams_campaigns_all_b BA,
3566 ams_object_associations D
3567 WHERE
3568 D.using_object_type='DELV' AND
3569 D.master_object_type (+) = 'CAMP' AND
3570 D.master_object_id = BA.campaign_id (+)
3571 UNION ALL
3572 SELECT
3573 BA.business_unit_id business_unit_id,BA.org_id org_id,
3574 'DELV' object_type_J, 'CSCHDELV' object_type, D.using_object_id object_id
3575 FROM
3576 ams_campaigns_all_b BA,
3577 ams_campaign_schedules_b E,
3578 ams_object_associations D
3579 WHERE
3580 D.master_object_type (+) = 'CSCH' AND D.master_object_id = E.SCHEDULE_ID
3581 (+) AND E.campaign_id = BA.campaign_id (+)
3582 AND D.using_object_type (+) = 'DELV'
3583 UNION ALL
3584 SELECT
3585 BA.business_unit_id business_unit_id,BA.org_id org_id,
3586 'DELV' object_type_J, 'EVEHDELV' object_type, D.using_object_id object_id
3587 FROM
3588 ams_event_headers_all_b BA,
3589 ams_object_associations D
3590 WHERE D.using_object_type(+) = 'DELV'
3591 AND D.master_object_type(+) = 'EVEH'
3592 AND D.master_object_id = BA.event_header_id (+)
3593 UNION ALL
3594 SELECT
3595 BA.business_unit_id business_unit_id,BA.org_id org_id,
3596 'DELV' object_type_J, 'EVEODELV' object_type, D.using_object_id object_id
3597 FROM
3598 ams_event_offers_all_b BA,
3599 ams_object_associations D
3600 WHERE D.using_object_type(+) = 'DELV'
3601 AND D.master_object_type(+) = 'EVEO'
3602 AND D.master_object_id = BA.event_offer_id (+)
3603 UNION ALL
3604 SELECT
3605 BA.business_unit_id business_unit_id,BA.org_id org_id,
3606 'DELV' object_type_J, 'EONEDELV' object_type, D.using_object_id object_id
3607 FROM
3608 ams_event_offers_all_b BA,
3609 ams_object_associations D
3610 WHERE D.using_object_type(+) = 'DELV'
3611 AND D.master_object_type(+) = 'EONE'
3612 AND D.master_object_id = BA.event_offer_id (+)
3613 ) U
3614 WHERE AD.status_code IN ( 'ACTIVE','CANCELLED','CLOSED' )
3615 AND AU.transaction_create_date BETWEEN p_start_datel AND p_end_datel + 0.99999
3616 AND AU.object_type = U.object_type_J (+)
3617 AND AU.object_id = U.object_id (+)
3618 AND AU.fund_id = AD.fund_id
3619 ) inner;
3620 l_count :=l_count +SQL%ROWCOUNT;
3621 EXECUTE IMMEDIATE 'COMMIT';
3622 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';
3623 EXCEPTION
3624 WHEN OTHERS THEN
3625 FND_FILE.put_line(fnd_file.log,'first insert:error insert fdsp daily'||sqlerrm(sqlcode));
3626 -- dbms_output.put_line('error inserting INTO bim_r_fdsp_daily_facts'||sqlerrm(sqlcode));
3627 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';
3628 x_return_status := FND_API.G_RET_STS_ERROR;
3629 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
3630 FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
3631 FND_MSG_PUB.Add;
3632 END ;
3633
3634 /* Second insert: Insert extra records which happened before start date active */
3635 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
3636 fnd_message.set_token('table_name', l_table_name, FALSE);
3637 fnd_file.put_line(fnd_file.log,fnd_message.get);
3638 BEGIN
3639 INSERT /*+ append parallel(fdf,p_para_num) */
3640 INTO bim_r_fdsp_daily_facts fdf(
3641 spend_transaction_id
3642 ,creation_date
3643 ,last_update_date
3644 ,created_by
3645 ,last_updated_by
3646 ,last_update_login
3647 ,fund_id
3648 ,business_unit_id
3649 ,util_org_id
3650 ,standard_discount
3651 ,accrual
3652 ,market_expense
3653 ,commited_amt
3654 ,planned_amt
3655 ,paid_amt
3656 ,delete_flag
3657 ,transaction_create_date
3658 ,load_date
3659 ,object_id
3660 ,object_type
3661 ,fis_month
3662 ,fis_qtr
3663 ,fis_year
3664 )
3665 SELECT /*+ parallel(inner, p_para_num) */
3666 bim_r_fdsp_daily_facts_s.nextval,
3667 sysdate,
3668 sysdate,
3669 l_user_id,
3670 l_user_id,
3671 l_user_id,
3672 inner.fund_id,
3673 inner.business_unit_id,
3674 inner.org_id,
3675 inner.standard_discount,
3676 inner.accrual,
3677 inner.market_expense,
3678 inner.commited_amt,
3679 inner.planned_amt,
3680 inner.paid_amt,
3681 'N',
3682 inner.transaction_create_date,
3683 trunc((decode(decode( to_char(inner.transaction_create_date,'MM') , to_char(next_day(inner.transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
3684 ,'TRUE'
3685 ,decode(decode(inner.transaction_create_date , (next_day(inner.transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
3686 ,'TRUE'
3687 ,inner.transaction_create_date
3688 ,'FALSE'
3689 ,next_day(inner.transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
3690 ,'FALSE'
3691 ,decode(decode(to_char(inner.transaction_create_date,'MM'),to_char(next_day(inner.transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
3692 ,'FALSE'
3693 ,last_day(inner.transaction_create_date))))) weekend_date
3694 ,inner.object_id
3695 ,inner.object_type
3696 ,BIM_SET_OF_BOOKS.get_fiscal_month(inner.transaction_create_date,204)
3697 ,BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.transaction_create_date,204)
3698 ,BIM_SET_OF_BOOKS.get_fiscal_year(inner.transaction_create_date,204)
3699 FROM (SELECT AD.fund_id fund_id,
3700 U.business_unit_id business_unit_id,
3701 U.org_id org_id,
3702 NVL(AU.standard_discount,0) standard_discount,
3703 NVL(AU.accrual,0) accrual,
3704 NVL(AU.market_expense,0) market_expense,
3705 NVL(AU.commited_amt,0) commited_amt,
3706 NVL(AU.planned_amt,0) planned_amt,
3707 NVL(AU.paid_amt,0) paid_amt,
3708 AU.object_id object_id,
3709 U.object_type object_type,
3710 AU.transaction_create_date transaction_create_date
3711 FROM ozf_funds_all_b AD,
3712 bim_r_fdsp_load AU,
3713 (SELECT
3714 D.business_unit_id business_unit_id,D.org_id org_id,
3715 'CAMP' object_type_J,'CAMP' object_type, D.campaign_id object_id
3716 FROM ams_campaigns_all_b D
3717 UNION ALL
3718 SELECT
3719 D.business_unit_id business_unit_id,D.org_id org_id,
3720 'CSCH' object_type_J,'CSCH' object_type,B.SCHEDULE_ID object_id
3721 FROM
3722 ams_campaigns_all_b D,
3723 ams_campaign_schedules_b B
3724 WHERE B.campaign_id = D.campaign_id (+)
3725 UNION ALL
3726 SELECT
3727 D.business_unit_id business_unit_id,D.org_id org_id,
3728 'EVEH' object_type_J,'EVEH' object_type, D.event_header_id object_id
3729 FROM
3730 ams_event_headers_all_b D
3731 UNION ALL
3732 SELECT
3733 D.business_unit_id business_unit_id,D.org_id org_id,
3734 'EVEO' object_type_J, 'EVEO' object_type, D.event_offer_id object_id
3735 FROM
3736 ams_event_offers_all_b D
3737 WHERE event_header_id is not null
3738 UNION ALL
3739 SELECT
3740 D.business_unit_id business_unit_id,D.org_id org_id,
3741 'EONE' object_type_J, 'EONE' object_type, D.event_offer_id object_id
3742 FROM
3743 ams_event_offers_all_b D
3744 WHERE event_header_id is null
3745 UNION ALL
3746 SELECT
3747 BC.business_unit_id business_unit_id,BC.org_id org_id,
3748 'OFFR' object_type_J, 'OFFR' object_type, D.qp_list_header_id object_id
3749 FROM
3750 ams_campaigns_all_b BC,
3751 ams_act_offers D
3752 WHERE
3753 D.arc_act_offer_used_by (+) = 'CAMP' AND D.act_offer_used_by_id =
3754 BC.campaign_id (+) AND BC.show_campaign_flag (+) = 'Y'
3755 UNION ALL
3756 SELECT
3757 BA.business_unit_id business_unit_id,BA.org_id org_id,
3758 'DELV' object_type_J, 'CAMPDELV' object_type, D.using_object_id object_id
3759 FROM
3760 ams_campaigns_all_b BA,
3761 ams_object_associations D
3762 WHERE
3763 D.using_object_type='DELV' AND
3764 D.master_object_type (+) = 'CAMP' AND
3765 D.master_object_id = BA.campaign_id (+)
3766 UNION ALL
3767 SELECT
3768 BA.business_unit_id business_unit_id,BA.org_id org_id,
3769 'DELV' object_type_J, 'CSCHDELV' object_type, D.using_object_id object_id
3770 FROM
3771 ams_campaigns_all_b BA,
3772 ams_campaign_schedules_b E,
3773 ams_object_associations D
3774 WHERE
3775 D.master_object_type (+) = 'CSCH' AND D.master_object_id = E.SCHEDULE_ID
3776 (+) AND E.campaign_id = BA.campaign_id (+)
3777 AND D.using_object_type (+) = 'DELV'
3778 UNION ALL
3779 SELECT
3780 BA.business_unit_id business_unit_id,BA.org_id org_id,
3781 'DELV' object_type_J, 'EVEHDELV' object_type, D.using_object_id object_id
3782 FROM
3783 ams_event_headers_all_b BA,
3784 ams_object_associations D
3785 WHERE D.using_object_type(+) = 'DELV'
3786 AND D.master_object_type(+) = 'EVEH'
3787 AND D.master_object_id = BA.event_header_id (+)
3788 UNION ALL
3789 SELECT
3790 BA.business_unit_id business_unit_id,BA.org_id org_id,
3791 'DELV' object_type_J, 'EVEODELV' object_type, D.using_object_id object_id
3792 FROM
3793 ams_event_offers_all_b BA,
3794 ams_object_associations D
3795 WHERE D.using_object_type(+) = 'DELV'
3796 AND D.master_object_type(+) = 'EVEO'
3797 AND D.master_object_id = BA.event_offer_id (+)
3798 UNION ALL
3799 SELECT
3800 BA.business_unit_id business_unit_id,BA.org_id org_id,
3801 'DELV' object_type_J, 'EONEDELV' object_type, D.using_object_id object_id
3802 FROM
3803 ams_event_offers_all_b BA,
3804 ams_object_associations D
3805 WHERE D.using_object_type(+) = 'DELV'
3806 AND D.master_object_type(+) = 'EONE'
3807 AND D.master_object_id = BA.event_offer_id (+)
3808 ) U
3809 WHERE AD.status_code IN ( 'ACTIVE','CANCELLED','CLOSED' )
3810 AND AD.start_date_active BETWEEN p_start_datel AND p_end_datel + 0.99999
3811 AND AU.transaction_create_date < p_start_datel
3812 AND AU.object_type = U.object_type_J (+)
3813 AND AU.object_id = U.object_id (+)
3814 AND AU.fund_id = AD.fund_id
3815 ) inner;
3816 l_count :=l_count+SQL%ROWCOUNT;
3817 EXECUTE IMMEDIATE 'commit';
3818 EXCEPTION
3819 WHEN others THEN
3820 FND_FILE.put_line(fnd_file.log,'error insert extras into fdsp daily'||sqlerrm(sqlcode));
3821 -- dbms_output.put_line('error inserting extra INTO bim_r_fdsp_daily_facts'||sqlerrm(sqlcode));
3822 x_return_status := FND_API.G_RET_STS_ERROR;
3823 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
3824 FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
3825 FND_MSG_PUB.Add;
3826 END;
3827
3828 /* Insert dummy 0s for the dates between p_start_date/start_date_active and p_end_date/end_date_active,
3829 but has no transactions. */
3830 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
3831 fnd_message.set_token('table_name', l_table_name, FALSE);
3832 fnd_file.put_line(fnd_file.log,fnd_message.get);
3833 BEGIN
3834 INSERT /*+ append parallel(fdf,p_para_num) */
3835 INTO bim_r_fdsp_daily_facts fdf(
3836 spend_transaction_id
3837 ,creation_date
3838 ,last_update_date
3839 ,created_by
3840 ,last_updated_by
3841 ,last_update_login
3842 ,fund_id
3843 ,business_unit_id
3844 ,util_org_id
3845 ,standard_discount
3846 ,accrual
3847 ,market_expense
3848 ,commited_amt
3849 ,planned_amt
3850 ,paid_amt
3851 ,delete_flag
3852 ,transaction_create_date
3853 ,load_date
3854 ,object_id
3855 ,object_type
3856 ,fis_month
3857 ,fis_qtr
3858 ,fis_year
3859 )
3860 SELECT /*+ parallel(inner, p_para_num) */
3861 bim_r_fdsp_daily_facts_s.nextval,
3862 sysdate,
3863 sysdate,
3864 l_user_id,
3865 l_user_id,
3866 l_user_id,
3867 inner.fund_id,
3868 null,
3869 null,
3870 0,
3871 0,
3872 0,
3873 0,
3874 0,
3875 0,
3876 'N',
3877 inner.trdate,
3878 trunc((decode(decode( to_char(inner.trdate,'MM') , to_char(next_day(inner.trdate,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
3879 ,'TRUE'
3880 ,decode(decode(inner.trdate , (next_day(inner.trdate, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
3881 ,'TRUE'
3882 ,inner.trdate
3883 ,'FALSE'
3884 ,next_day(inner.trdate, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
3885 ,'FALSE'
3886 ,decode(decode(to_char(inner.trdate,'MM'),to_char(next_day(inner.trdate,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
3887 ,'FALSE'
3888 ,last_day(inner.trdate))))) weekend_date
3889 ,null
3890 ,null
3891 ,BIM_SET_OF_BOOKS.get_fiscal_month(inner.trdate,204)
3892 ,BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.trdate,204)
3893 ,BIM_SET_OF_BOOKS.get_fiscal_year(inner.trdate,204)
3894 FROM (
3895 SELECT distinct a.fund_id fund_id,
3896 TRUNC(b.trdate) trdate
3897 FROM bim_r_fdsp_daily_facts a,
3898 ozf_funds_all_b f,
3899 bim_intl_dates b
3900 WHERE b.trdate between greatest(p_start_datel, f.start_date_active)
3901 and least(p_end_datel, nvl(f.end_date_active,p_end_datel))
3902 and f.fund_id = a.fund_id
3903 and (a.fund_id, TRUNC(b.trdate)) not in (select c.fund_id, c.transaction_create_date
3904 from bim_r_fdsp_daily_facts c
3905 where c.fund_id = a.fund_id
3906 and c.transaction_create_date = TRUNC(b.trdate)) )inner;
3907 l_count :=l_count+SQL%ROWCOUNT;
3908 -- dbms_output.put_line('missing fdsp:l_count'||l_count);
3909 EXECUTE IMMEDIATE 'commit';
3910 EXCEPTION
3911 WHEN others THEN
3912 FND_FILE.put_line(fnd_file.log,'error insert fdsp daily for missing dates'||sqlerrm(sqlcode));
3913 -- dbms_output.put_line('error inserting fdsp daily for missing dates'||sqlerrm(sqlcode));
3914 x_return_status := FND_API.G_RET_STS_ERROR;
3915 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
3916 FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
3917 FND_MSG_PUB.Add;
3918 END;
3919
3920 /* Insert into bim_r_fdsp_daily_facts the dates which are in bim_r_fund_daily_facts
3921 but not in bim_r_fdsp_daily_facts. */
3922 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
3923 fnd_message.set_token('table_name', l_table_name, FALSE);
3924 fnd_file.put_line(fnd_file.log,fnd_message.get);
3925 BEGIN --insert into fdsp for balancing
3926 l_table_name :='bim_r_fdsp_daily_facts';
3927 INSERT /*+ append parallel(fdf,p_para_num) */
3928 INTO bim_r_fdsp_daily_facts fdf(
3929 spend_transaction_id
3930 ,creation_date
3931 ,last_update_date
3932 ,created_by
3933 ,last_updated_by
3934 ,last_update_login
3935 ,fund_id
3936 ,business_unit_id
3937 ,util_org_id
3938 ,standard_discount
3939 ,accrual
3940 ,market_expense
3941 ,commited_amt
3942 ,planned_amt
3943 ,paid_amt
3944 ,delete_flag
3945 ,transaction_create_date
3946 ,load_date
3947 ,object_id
3948 ,object_type
3949 ,fis_month
3950 ,fis_qtr
3951 ,fis_year
3952 )
3953 SELECT /*+ parallel(inner, p_para_num) */
3954 bim_r_fdsp_daily_facts_s.nextval,
3955 sysdate,
3956 sysdate,
3957 l_user_id,
3958 l_user_id,
3959 l_user_id,
3960 b1.fund_id,
3961 b1.business_unit_id,
3962 b1.util_org_id,
3963 0,
3964 0,
3965 0,
3966 0,
3967 0,
3968 0,
3969 'N',
3970 b1.transaction_create_date,
3971 b1.load_date,
3972 b1.object_id,
3973 b1.object_type,
3974 b1.fis_month,
3975 b1.fis_qtr,
3976 b1.fis_year
3977 FROM bim_r_fdsp_daily_facts b1,
3978 (SELECT distinct fund_id fund_id,
3979 transaction_create_date transaction_create_date
3980 FROM bim_r_fund_daily_facts fd
3981 WHERE (fund_id, transaction_create_date) not in
3982 ( SELECT /*+ hash_aj */ fund_id, transaction_create_date
3983 from bim_r_fdsp_daily_facts
3984 where fund_id is not null
3985 and transaction_create_date is not null
3986 )) b2
3987 WHERE b1.fund_id = b2.fund_id
3988 AND b1.transaction_create_date = b2.transaction_create_date ;
3989 EXECUTE IMMEDIATE 'commit';
3990 EXCEPTION
3991 WHEN OTHERS THEN
3992 FND_FILE.put_line(fnd_file.log,'error insert fdsp daily for balancing'||sqlerrm(sqlcode));
3993 -- dbms_output.put_line('error inserting bim_r_fdsp_daily_facts for balancing'||sqlerrm(sqlcode));
3994 x_return_status := FND_API.G_RET_STS_ERROR;
3995 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
3996 FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
3997 FND_MSG_PUB.Add;
3998 END;
3999 --Insert dummy records into bim_r_fund_daily_facts for balancing
4000 /* Insert into bim_r_fund_daily_facts dates which are in bim_r_fdsp_daily_facts
4001 but not in bim_r_fund_daily_facts */
4002 BEGIN
4003 l_table_name :='bim_r_fund_daily_facts';
4004 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
4005 fnd_message.set_token('table_name', l_table_name, FALSE);
4006 fnd_file.put_line(fnd_file.log,fnd_message.get);
4007 INSERT into bim_r_fund_daily_facts(
4008 fund_transaction_id
4009 ,creation_date
4010 ,last_update_date
4011 ,created_by
4012 ,last_updated_by
4013 ,last_update_login
4014 ,fund_id
4015 ,parent_fund_id
4016 ,fund_number
4017 ,start_date
4018 ,end_date
4019 ,start_period
4020 ,end_period
4021 ,set_of_books_id
4022 ,fund_type
4023 --,region
4024 ,country
4025 ,org_id
4026 ,category_id
4027 ,status
4028 ,original_budget
4029 ,transfer_in
4030 ,transfer_out
4031 ,holdback_amt
4032 ,currency_code_fc
4033 ,delete_flag
4034 ,transaction_create_date
4035 ,load_date
4036 ,fis_month
4037 ,fis_qtr
4038 ,fis_year
4039 ,business_unit_id)
4040 SELECT
4041 bim_r_fund_daily_facts_s.nextval,
4042 sysdate,
4043 sysdate,
4044 l_user_id,
4045 l_user_id,
4046 l_user_id,
4047 a.fund_id ,
4048 a.parent_fund_id parent_fund_id,
4049 a.fund_number fund_number,
4050 a.start_date_active start_date,
4051 a.end_date_active end_date,
4052 a.start_period_name start_period,
4053 a.end_period_name end_period,
4054 a.set_of_books_id set_of_book_id,
4055 a.fund_type fund_type,
4056 a.country_id country,
4057 a.org_id org_id,
4058 a.category_id fund_category,
4059 a.status_code fund_status,
4060 a.original_budget original_amount,
4061 0,
4062 0,
4063 0,
4064 a.currency_code_fc,
4065 'N',
4066 b2.transaction_create_date,
4067 trunc((decode(decode( to_char(transaction_create_date,'MM') , to_char(next_day(transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
4068 ,'TRUE'
4069 ,decode(decode(transaction_create_date , (next_day(transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
4070 ,'TRUE'
4071 ,transaction_create_date
4072 ,'FALSE'
4073 ,next_day(transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
4074 ,'FALSE'
4075 ,decode(decode(to_char(transaction_create_date,'MM'),to_char(next_day(transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
4076 ,'FALSE'
4077 ,last_day(transaction_create_date))))) weekend_date,
4078 b2.fis_month,
4079 b2.fis_qtr,
4080 b2.fis_year,
4081 a.business_unit_id
4082 FROM ozf_funds_all_b a,
4083 (SELECT distinct(fund_id) fund_id,
4084 fis_month fis_month,
4085 fis_qtr fis_qtr,
4086 fis_year fis_year,
4087 transaction_create_date transaction_create_date
4088 FROM bim_r_fdsp_daily_facts fdsp
4089 WHERE fund_id is not null
4090 AND transaction_create_date is not null
4091 AND (fund_id, transaction_create_date) not in
4092 ( SELECT /*+ hash_aj */ fund_id, transaction_create_date
4093 from bim_r_fund_daily_facts b1
4094 )) b2
4095 WHERE b2.fund_id = a.fund_id;
4096 EXECUTE IMMEDIATE 'commit';
4097 EXCEPTION
4098 WHEN OTHERS THEN
4099 FND_FILE.put_line(fnd_file.log,'error insert fund daily for balancing'||sqlerrm(sqlcode));
4100 -- dbms_output.put_line('error insert fund_daily for balancing with fdsp'||sqlerrm(sqlcode));
4101 x_return_status := FND_API.G_RET_STS_ERROR;
4102 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
4103 FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
4104 FND_MSG_PUB.Add;
4105 END;
4106
4107 --Insert into history table.
4108 FND_FILE.put_line(fnd_file.log,'Insert into log history');
4109 -- IF l_count>0 THEN
4110 LOG_HISTORY(
4111 'FUND',
4112 p_start_datel,
4113 p_end_datel,
4114 x_msg_count,
4115 x_msg_data,
4116 x_return_status) ;
4117 -- END IF;
4118 EXECUTE IMMEDIATE 'truncate table '||l_schema||'.bim_r_fund_weekly_facts';
4119 --Start of inserting into 'bim_r_fund_weekly_facts'
4120 BEGIN
4121 l_table_name :='bim_r_fund_weekly_facts';
4122 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
4123 fnd_message.set_token('table_name', l_table_name, FALSE);
4124 fnd_file.put_line(fnd_file.log,fnd_message.get);
4125 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_weekly_facts_s CACHE 1000';
4126 INSERT /*+ append parallel(fwf,p_para_num) */ INTO bim_r_fund_weekly_facts fwf(
4127 fund_transaction_id
4128 ,creation_date
4129 ,last_update_date
4130 ,created_by
4131 ,last_updated_by
4132 ,last_update_login
4133 ,fund_id
4134 ,parent_fund_id
4135 ,fund_number
4136 ,start_date
4137 ,end_date
4138 ,start_period
4139 ,end_period
4140 ,set_of_books_id
4141 ,fund_type
4142 -- ,region
4143 ,country
4144 ,org_id
4145 ,category_id
4146 ,status
4147 ,original_budget
4148 ,transfer_in
4149 ,transfer_out
4150 ,holdback_amt
4151 ,currency_code_fc
4152 ,delete_flag
4153 ,transaction_create_date
4154 ,load_date
4155 ,fis_month
4156 ,fis_qtr
4157 ,fis_year
4158 ,business_unit_id)
4159 SELECT /*+ parallel(inner, p_para_num) */
4160 bim_r_fund_weekly_facts_s.nextval
4161 ,sysdate
4162 ,sysdate
4163 ,l_user_id
4164 ,l_user_id
4165 ,l_user_id
4166 ,inner.fund_id
4167 ,inner.parent_fund_id
4168 ,inner.fund_number
4169 ,inner.start_date
4170 ,inner.end_date
4171 ,inner.start_period
4172 ,inner.end_period
4173 ,inner.set_of_books_id
4174 ,inner.fund_type
4175 --,inner.region
4176 ,inner.country
4177 ,inner.org_id
4178 ,inner.category_id
4179 ,inner.status
4180 ,inner.original_budget
4181 ,inner.transfer_in
4182 ,inner.transfer_out
4183 ,inner.holdback_amt
4184 ,inner.currency_code_fc
4185 ,'N'
4186 ,inner.load_date
4187 ,inner.load_date
4188 ,BIM_SET_OF_BOOKS.get_fiscal_month(inner.load_date,204)
4189 ,BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.load_date,204)
4190 ,BIM_SET_OF_BOOKS.get_fiscal_year(inner.load_date,204)
4191 ,inner.business_unit_id
4192 FROM(SELECT fund_id fund_id
4193 ,parent_fund_id parent_fund_id
4194 ,fund_number fund_number
4195 ,start_date start_date
4196 ,end_date end_date
4197 ,start_period start_period
4198 ,end_period end_period
4199 ,set_of_books_id set_of_books_id
4200 ,fund_type fund_type
4201 --,region region
4202 ,country country
4203 ,org_id org_id
4204 ,business_unit_id business_unit_id
4205 ,category_id category_id
4206 ,status status
4207 ,original_budget original_budget
4208 ,SUM(transfer_in) transfer_in
4209 ,SUM(transfer_out) transfer_out
4210 ,SUM(holdback_amt) holdback_amt
4211 ,currency_code_fc currency_code_fc
4212 ,load_date load_date
4213 FROM bim_r_fund_daily_facts
4214 GROUP BY
4215 fund_id
4216 ,load_date
4217 ,parent_fund_id
4218 ,fund_number
4219 ,start_date
4220 ,end_date
4221 ,start_period
4222 ,end_period
4223 ,set_of_books_id
4224 ,fund_type
4225 -- ,region
4226 ,country
4227 ,org_id
4228 ,business_unit_id
4229 ,category_id
4230 ,status
4231 ,original_budget
4232 ,currency_code_fc) inner;
4233 EXECUTE IMMEDIATE 'commit';
4234 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_weekly_facts_s CACHE 20';
4235 EXCEPTION
4236 WHEN others THEN
4237 FND_FILE.put_line(fnd_file.log,'Error insertg fund weekly:'||sqlerrm(sqlcode));
4238 --dbms_output.put_line('Errorin inserting fund weekly:'||sqlerrm(sqlcode));
4239 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_weekly_facts_s CACHE 20';
4240 x_return_status := FND_API.G_RET_STS_ERROR;
4241 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
4242 FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
4243 FND_MSG_PUB.Add;
4244 END;
4245 BEGIN
4246 EXECUTE IMMEDIATE 'truncate table '||l_schema||'.bim_r_fdsp_weekly_facts';
4247 l_table_name :='bim_r_fdsp_weekly_facts';
4248 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
4249 fnd_message.set_token('table_name', l_table_name, FALSE);
4250 fnd_file.put_line(fnd_file.log,fnd_message.get);
4251 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_weekly_facts_s CACHE 1000';
4252 --dbms_output.put_line('inside fist inserting into fdsp weekly');
4253
4254 --Insert into bim_r_fdsp_weekly_facts
4255 INSERT /*+ append parallel(fwf,p_para_num) */
4256 INTO bim_r_fdsp_weekly_facts fwf(
4257 spend_transaction_id
4258 ,creation_date
4259 ,last_update_date
4260 ,created_by
4261 ,last_updated_by
4262 ,last_update_login
4263 ,fund_id
4264 ,business_unit_id
4265 ,util_org_id
4266 ,standard_discount
4267 ,accrual
4268 ,market_expense
4269 ,commited_amt
4270 ,planned_amt
4271 ,paid_amt
4272 ,delete_flag
4273 ,transaction_create_date
4274 ,load_date
4275 ,object_id
4276 ,object_type
4277 ,fis_month
4278 ,fis_qtr
4279 ,fis_year
4280 )
4281 SELECT /*+ parallel(inner.p_para_num) */
4282 bim_r_fdsp_weekly_facts_s.nextval
4283 ,sysdate
4284 ,sysdate
4285 ,l_user_id
4286 ,l_user_id
4287 ,l_user_id
4288 ,inner.fund_id
4289 ,inner.business_unit_id
4290 ,inner.util_org_id
4291 ,inner.standard_discount
4292 ,inner.accrual
4293 ,inner.market_expense
4294 ,inner.commited_amt
4295 ,inner.planned_amt
4296 ,inner.paid_amt
4297 ,'N'
4298 ,inner.load_date
4299 ,inner.load_date
4300 ,inner.object_id
4301 ,inner.object_type
4302 ,BIM_SET_OF_BOOKS.get_fiscal_month(inner.load_date,204)
4303 ,BIM_SET_OF_BOOKS.get_fiscal_qtr(inner.load_date,204)
4304 ,BIM_SET_OF_BOOKS.get_fiscal_year(inner.load_date,204)
4305 FROM
4306 (SELECT fund_id fund_id
4307 ,business_unit_id business_unit_id
4308 ,util_org_id util_org_id
4309 ,object_id object_id
4310 ,object_type object_type
4311 ,SUM(standard_discount) standard_discount
4312 ,SUM(accrual) accrual
4313 ,SUM(market_expense) market_expense
4314 ,SUM(commited_amt) commited_amt
4315 ,SUM(planned_amt) planned_amt
4316 ,SUM(paid_amt) paid_amt
4317 ,load_date load_date
4318 FROM bim_r_fdsp_daily_facts
4319 GROUP BY load_date
4320 ,business_unit_id
4321 ,object_id
4322 ,object_type
4323 ,fund_id
4324 ,util_org_id) inner;
4325 EXECUTE IMMEDIATE 'commit';
4326 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_weekly_facts_s CACHE 20';
4327 EXCEPTION
4328 when others THEN
4329 FND_FILE.put_line(fnd_file.log,'error insert fdsp_weekly'||sqlerrm(sqlcode));
4330 --dbms_output.put_line('error inserting INTO bim_r_fdsp_weekly_facts'||sqlerrm(sqlcode));
4331 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_weekly_facts_s CACHE 20';
4332 x_return_status := FND_API.G_RET_STS_ERROR;
4333 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
4334 FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
4335 FND_MSG_PUB.Add;
4336 END;
4337 -- for performance reasons
4338 BEGIN
4339 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_weekly_facts noparallel';
4340 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_weekly_facts noparallel';
4341 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_balance noparallel';
4342 EXCEPTION
4343 WHEN OTHERS THEN
4344 null;
4345 END;
4346
4347 --Analysis tables for better performance
4348 BEGIN
4349 DBMS_STATS.gather_table_stats('BIM','BIM_R_FUND_DAILY_FACTS', estimate_percent => 5,
4350 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
4351 END;
4352 BEGIN
4353 DBMS_STATS.gather_table_stats('BIM','BIM_R_FUND_WEEKLY_FACTS', estimate_percent => 5,
4354 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
4355 END;
4356 BEGIN
4357 DBMS_STATS.gather_table_stats('BIM','BIM_R_FDSP_DAILY_FACTS', estimate_percent => 5,
4358 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
4359 END;
4360 BEGIN
4361 DBMS_STATS.gather_table_stats('BIM','BIM_R_FDSP_WEEKLY_FACTS', estimate_percent => 5,
4362 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
4363 END;
4364
4365 --call update subsequent balance
4366 fnd_message.set_name('BIM','BIM_R_CALL_PROC');
4367 fnd_message.set_token('proc_name', 'UPDATE_SUB_BALANCE', FALSE);
4368 fnd_file.put_line(fnd_file.log,fnd_message.get);
4369 update_sub_balance(p_start_datel, p_end_datel);
4370 fnd_message.set_name('BIM','BIM_R_PROG_COMPLETION');
4371 fnd_message.set_token('program_name', 'Fund first load', FALSE);
4372 fnd_file.put_line(fnd_file.log,fnd_message.get);
4373 END FUND_SUB_LOAD;
4374 END BIM_FUND_FACTS;