eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.FII_BUDGET_FORECAST_C dependencies on FII_BUDGET_BASE

Line 17: -- from FII_BUDGET_BASE. Then it will reset the truncation

13: -- Procedure
14: -- Purge_All
15: -- Purpose
16: -- This routine will purge all records for the given plan type
17: -- from FII_BUDGET_BASE. Then it will reset the truncation
18: -- flag and profile option setting in FII_CHANGE_LOG.
19: -- History
20: -- 06-20-02 S Kung Created
21: -- Arguments

Line 36: -- Purge all records from FII_BUDGET_BASE for the specified plan type

32: IF (FIIBUUP_DEBUG) THEN
33: FII_MESSAGE.Func_Ent('FII_BUDGET_FORECAST_C.Purge_All');
34: END IF;
35:
36: -- Purge all records from FII_BUDGET_BASE for the specified plan type
37: IF (FIIBUUP_DEBUG) THEN
38: FII_MESSAGE.Write_Log
39: (msg_name => 'FII_ROUTINE',
40: token_num => 2,

Line 44: v2 => 'Purging all records from FII_BUDGET_BASE...');

40: token_num => 2,
41: t1 => 'ROUTINE',
42: v1 => 'Purge_All()',
43: t2 => 'ACTION',
44: v2 => 'Purging all records from FII_BUDGET_BASE...');
45: END IF;
46:
47: g_phase := 'delete from FII_BUDGET_BASE';
48: DELETE from FII_BUDGET_BASE

Line 47: g_phase := 'delete from FII_BUDGET_BASE';

43: t2 => 'ACTION',
44: v2 => 'Purging all records from FII_BUDGET_BASE...');
45: END IF;
46:
47: g_phase := 'delete from FII_BUDGET_BASE';
48: DELETE from FII_BUDGET_BASE
49: WHERE plan_type_code = FIIBUUP_PURGE_PLAN_TYPE;
50:
51: IF (FIIBUUP_DEBUG) THEN

Line 48: DELETE from FII_BUDGET_BASE

44: v2 => 'Purging all records from FII_BUDGET_BASE...');
45: END IF;
46:
47: g_phase := 'delete from FII_BUDGET_BASE';
48: DELETE from FII_BUDGET_BASE
49: WHERE plan_type_code = FIIBUUP_PURGE_PLAN_TYPE;
50:
51: IF (FIIBUUP_DEBUG) THEN
52: FII_MESSAGE.Write_Log

Line 58: v2 => 'FII_BUDGET_BASE');

54: token_num => 2,
55: t1 => 'NUM',
56: v1 => TO_CHAR(NVL(SQL%ROWCOUNT, 0)),
57: t2 => 'TABLE',
58: v2 => 'FII_BUDGET_BASE');
59: END IF;
60:
61: -- Reset truncation indicator back to normal and
62: -- update FII_CHANGE_LOG to reflect latest profile setting

Line 203: -- in a given time period from FII_BUDGET_BASE.

199: -- Procedure
200: -- Purge_Partial
201: -- Purpose
202: -- This routine will purge all records for the given plan type
203: -- in a given time period from FII_BUDGET_BASE.
204: -- History
205: -- 06-20-02 S Kung Created
206: -- Arguments
207: -- None

Line 293: l_sqlstmt := 'UPDATE FII_BUDGET_BASE b ' ||

289: END IF;
290:
291: g_phase := 'Build SQL statement';
292: -- First, subtract the purge amount from their respective rollup records
293: l_sqlstmt := 'UPDATE FII_BUDGET_BASE b ' ||
294: 'SET (b.prim_amount_g, b.sec_amount_g, ' ||
295: 'b.prim_amount_total, b.sec_amount_total, '||
296: 'b.last_update_date, b.last_updated_by, ' ||
297: 'b.last_update_login) = ' ||

Line 304: 'FROM FII_BUDGET_BASE b2 ' ||

300: '(b.prim_amount_total-SUM(b2.prim_amount_total)), '||
301: '(b.sec_amount_total-SUM(b2.sec_amount_total)), '||
302: 'SYSDATE, ' ||
303: ':user_id, :login_id ' ||
304: 'FROM FII_BUDGET_BASE b2 ' ||
305: 'WHERE b2.plan_type_code = b.plan_type_code ' ||
306: 'AND b2.ledger_id = b.ledger_id '||
307: 'AND b2.company_id = b.company_id '||
308: 'AND b2.cost_center_id = b.cost_center_id '||

Line 355: 'FROM FII_BUDGET_BASE b3 ' ||

351: 'b3.fin_category_id, ' ||
352: 'NVL(b3.category_id, -1), '||
353: 'b3.user_dim1_id, ' ||
354: 'NVL(b3.version_date, :global_start_date) ' ||
355: 'FROM FII_BUDGET_BASE b3 ' ||
356: 'WHERE b3.plan_type_code = :plan_type_code ' ||
357: 'AND b3.time_id = :l_purge_time_id ' ||
358: 'AND b3.period_type_id = :l_purge_period_type_id) ';
359:

Line 420: v2 => 'FII_BUDGET_BASE');

416: token_num => 2,
417: t1 => 'NUM',
418: v1 => TO_CHAR(NVL(SQL%ROWCOUNT, 0)),
419: t2 => 'TABLE',
420: v2 => 'FII_BUDGET_BASE');
421: END IF;
422:
423: -- Next, purge records
424: IF (FIIBUUP_DEBUG) THEN

Line 431: v2 => 'Purging records from FII_BUDGET_BASE...');

427: token_num => 2,
428: t1 => 'ROUTINE',
429: v1 => 'Purge_Partial()',
430: t2 => 'ACTION',
431: v2 => 'Purging records from FII_BUDGET_BASE...');
432: END IF;
433:
434: g_phase := 'Delete from FII_BUDGET_BASE';
435: DELETE from FII_BUDGET_BASE

Line 434: g_phase := 'Delete from FII_BUDGET_BASE';

430: t2 => 'ACTION',
431: v2 => 'Purging records from FII_BUDGET_BASE...');
432: END IF;
433:
434: g_phase := 'Delete from FII_BUDGET_BASE';
435: DELETE from FII_BUDGET_BASE
436: WHERE plan_type_code = FIIBUUP_PURGE_PLAN_TYPE
437: AND ( (time_id = l_purge_time_id
438: AND period_type_id = l_purge_period_type_id)

Line 435: DELETE from FII_BUDGET_BASE

431: v2 => 'Purging records from FII_BUDGET_BASE...');
432: END IF;
433:
434: g_phase := 'Delete from FII_BUDGET_BASE';
435: DELETE from FII_BUDGET_BASE
436: WHERE plan_type_code = FIIBUUP_PURGE_PLAN_TYPE
437: AND ( (time_id = l_purge_time_id
438: AND period_type_id = l_purge_period_type_id)
439: OR (prim_amount_g = 0));

Line 448: v2 => 'FII_BUDGET_BASE');

444: token_num => 2,
445: t1 => 'NUM',
446: v1 => TO_CHAR(NVL(SQL%ROWCOUNT, 0)),
447: t2 => 'TABLE',
448: v2 => 'FII_BUDGET_BASE');
449: END IF;
450:
451: -- Commit all work
452: FND_CONCURRENT.Af_Commit;

Line 524: -- Purge all records from FII_BUDGET_BASE for the specified effective

520: IF (FIIBUUP_DEBUG) THEN
521: FII_MESSAGE.Func_Ent('FII_BUDGET_FORECAST_C.Purge_Eff_Date');
522: END IF;
523:
524: -- Purge all records from FII_BUDGET_BASE for the specified effective
525: -- dates. All records with the same dimensions on and after the effective
526: -- date will be purged.
527: IF (FIIBUUP_DEBUG) THEN
528: FII_MESSAGE.Write_Log

Line 534: v2 => 'Purging records from FII_BUDGET_BASE for ' ||

530: token_num => 2,
531: t1 => 'ROUTINE',
532: v1 => 'Purge_Eff_Date()',
533: t2 => 'ACTION',
534: v2 => 'Purging records from FII_BUDGET_BASE for ' ||
535: version_date ||'...');
536: END IF;
537:
538: g_phase := 'delete from FII_BUDGET_BASE';

Line 538: g_phase := 'delete from FII_BUDGET_BASE';

534: v2 => 'Purging records from FII_BUDGET_BASE for ' ||
535: version_date ||'...');
536: END IF;
537:
538: g_phase := 'delete from FII_BUDGET_BASE';
539: l_tmpstmt :=
540: ' DELETE from FII_BUDGET_BASE b'||
541: ' WHERE b.plan_type_code = :plan_type '||
542: ' AND (b.ledger_id, b.company_id, b.cost_center_id, '||

Line 540: ' DELETE from FII_BUDGET_BASE b'||

536: END IF;
537:
538: g_phase := 'delete from FII_BUDGET_BASE';
539: l_tmpstmt :=
540: ' DELETE from FII_BUDGET_BASE b'||
541: ' WHERE b.plan_type_code = :plan_type '||
542: ' AND (b.ledger_id, b.company_id, b.cost_center_id, '||
543: 'b.fin_category_id, b.category_id, '||
544: ' b.user_dim1_id) IN '||

Line 548: ' FROM FII_BUDGET_BASE b2 '||

544: ' b.user_dim1_id) IN '||
545: ' (SELECT b2.ledger_id, b2.company_id, b2.cost_center_id, '||
546: ' b2.fin_category_id, b2.category_id, '||
547: ' b2.user_dim1_id '||
548: ' FROM FII_BUDGET_BASE b2 '||
549: ' WHERE b2.version_date >= trunc(:version_date)) '||
550: ' AND b.version_date >= trunc(:version_date) ';
551:
552: -- Print out the dynamic SQL statements if running in debug mode

Line 585: v2 => 'FII_BUDGET_BASE');

581: token_num => 2,
582: t1 => 'NUM',
583: v1 => TO_CHAR(NVL(SQL%ROWCOUNT, 0)),
584: t2 => 'TABLE',
585: v2 => 'FII_BUDGET_BASE');
586: END IF;
587:
588: -- Reset truncation indicator back to normal and
589: -- update FII_CHANGE_LOG to reflect latest profile setting

Line 751: g_phase := 'Truncate the staging table FII_BUDGET_BASE';

747: FII_MESSAGE.Func_Ent('FII_BUDGET_FORECAST_C.Psi_Insert_Stg');
748: END IF;
749:
750: -- PSI Budget Extraction will always extract budget data from scratch
751: g_phase := 'Truncate the staging table FII_BUDGET_BASE';
752: FII_UTIL.truncate_table ('FII_BUDGET_STG', 'FII', g_retcode);
753:
754: -- Check if UDD1 is enabled or not
755: SELECT DBI_ENABLED_FLAG

Line 1365: -- that exists in fii_budget_base but not in fii_budget_stg (new data).

1361: -- Procedure
1362: -- Psi_DeleteDiff()
1363: -- Purpose
1364: -- This routine will delete budget data with time/dimension combination
1365: -- that exists in fii_budget_base but not in fii_budget_stg (new data).
1366: -- Arguments
1367: -- None
1368: -- Example
1369: -- result := FII_BUDGET_FORECAST_C.Psi_DeleteDiff;

Line 1386: -- Delete rows from fii_budget_base if time/dimension combination does

1382:
1383: g_phase := 'PSI DeleteDiff';
1384:
1385: ------------------------------------------------------------------------
1386: -- Delete rows from fii_budget_base if time/dimension combination does
1387: -- not exists in the new data in fii_budget_stg.
1388: ------------------------------------------------------------------------
1389: l_sqlstmt :=
1390: ' DELETE FROM fii_budget_base '||

Line 1390: ' DELETE FROM fii_budget_base '||

1386: -- Delete rows from fii_budget_base if time/dimension combination does
1387: -- not exists in the new data in fii_budget_stg.
1388: ------------------------------------------------------------------------
1389: l_sqlstmt :=
1390: ' DELETE FROM fii_budget_base '||
1391: ' WHERE ( plan_type_code, '||
1392: ' time_id, '||
1393: ' period_type_id, '||
1394: ' ledger_id, '||

Line 1419: ' FROM fii_budget_base '||

1415: ' user_dim2_id, '||
1416: ' posted_date, '||
1417: ' prim_amount_g, '||
1418: ' baseline_amount_prim '||
1419: ' FROM fii_budget_base '||
1420: ' MINUS '||
1421: ' SELECT plan_type_code, '||
1422: ' nvl(day, nvl(period, nvl(quarter, year))), '||
1423: ' decode(day, null, '||

Line 1473: ' rows from fii_budget_base');

1469: EXECUTE IMMEDIATE l_sqlstmt;
1470:
1471: IF (FIIBUUP_DEBUG) THEN
1472: fii_util.put_line('Deleted '||SQL%ROWCOUNT||
1473: ' rows from fii_budget_base');
1474: END IF;
1475:
1476: -- Need to commit
1477: FND_CONCURRENT.Af_Commit;

Line 1539: -- into fii_budget_base.

1535: -- Procedure
1536: -- Psi_Insert_Base()
1537: -- Purpose
1538: -- This routine will merge new/modified budget data from fii_budget_stg
1539: -- into fii_budget_base.
1540: -- Arguments
1541: -- None
1542: -- Example
1543: -- result := FII_BUDGET_FORECAST_C.Psi_Insert_Base;

Line 1560: -- Insert new/modified budget data in fii_budget_stg into fii_budget_base

1556:
1557: g_phase := 'PSI Insert Base';
1558:
1559: ------------------------------------------------------------------------
1560: -- Insert new/modified budget data in fii_budget_stg into fii_budget_base
1561: ------------------------------------------------------------------------
1562: -- Bug 5004852: Changed to populate company_cost_center_org_id as well
1563: -- Bug 4943332: Added hints suggested by the performance team
1564: l_sqlstmt :=

Line 1565: ' INSERT /+ append parallel(b)/ INTO fii_budget_base b '||

1561: ------------------------------------------------------------------------
1562: -- Bug 5004852: Changed to populate company_cost_center_org_id as well
1563: -- Bug 4943332: Added hints suggested by the performance team
1564: l_sqlstmt :=
1565: ' INSERT /*+ append parallel(b)*/ INTO fii_budget_base b '||
1566: ' ( plan_type_code, time_id, period_type_id, '||
1567: ' prim_amount_g, prim_amount_total, '||
1568: ' baseline_amount_prim, ledger_id, company_cost_center_org_id, '||
1569: ' company_id, cost_center_id, fin_category_id, category_id, '||

Line 1624: ' FROM fii_budget_base ';

1620: ' user_dim2_id, '||
1621: ' trunc(posted_date), '||
1622: ' sysdate, :user_id, sysdate, :user_id, :login_id, '||
1623: ' :ver_date '||
1624: ' FROM fii_budget_base ';
1625:
1626: -- Print out the dynamic SQL statements if running in debug mode
1627: IF (FIIBUUP_DEBUG) THEN
1628: fii_util.put_line('l_sqlstmt = '|| l_sqlstmt);

Line 1650: ' rows into fii_budget_base');

1646: FIIBUUP_LOGIN_ID, FIIBUUP_GLOBAL_START_DATE;
1647:
1648: IF (FIIBUUP_DEBUG) THEN
1649: fii_util.put_line('Inserted '||SQL%ROWCOUNT||
1650: ' rows into fii_budget_base');
1651: END IF;
1652:
1653: -- Need to commit
1654: FND_CONCURRENT.Af_Commit;

Line 1777: FROM fii_budget_base) v,

1773: SELECT time_id, period_type_id,
1774: ledger_id, company_id, cost_center_id, fin_category_id,
1775: category_id, user_dim1_id, user_dim2_id, posted_date,
1776: baseline_amount_prim
1777: FROM fii_budget_base) v,
1778: gl_ledgers_public_v sob
1779: WHERE sob.ledger_id = v.ledger_id;
1780:
1781: -- Bug 5004852: Added to select the company_cost_center_org_id for the

Line 1943: -- before inserting data into fii_budget_base

1939: raise NO_DATA_FOUND;
1940: END IF;
1941:
1942: -- Bug 5004852: Populate company_cost_center_org_id in fii_budget_stg
1943: -- before inserting data into fii_budget_base
1944: g_phase := 'Open ccc_org_cursor to cache the CCC ORG IDs';
1945:
1946: OPEN ccc_org_cursor;
1947: FETCH ccc_org_cursor BULK COLLECT INTO l_ccc_org_rec.l_ccc_org_id,

Line 1962: -- Merge data in fii_budget_stg into fii_budget_base

1958:
1959: CLOSE ccc_org_cursor;
1960:
1961: ------------------------------------------------------------------------
1962: -- Merge data in fii_budget_stg into fii_budget_base
1963: ------------------------------------------------------------------------
1964: IF (NOT FII_BUDGET_FORECAST_C.Psi_Insert_Base) THEN
1965: raise FIIBUUP_fatal_err;
1966: END IF;

Line 1977: FROM fii_budget_base

1973: BEGIN
1974: -- Bug fix 4943332: Change to return 1 if any row exists
1975: SELECT 1
1976: INTO l_row_exists
1977: FROM fii_budget_base
1978: WHERE rownum = 1;
1979: EXCEPTION
1980: WHEN NO_DATA_FOUND THEN
1981: FII_MESSAGE.Write_Log (msg_name => 'FII_PSI_NO_RECS', token_num => 0);

Line 2664: FROM FII_BUDGET_BASE b, FII_TIME_ENT_PERIOD p

2660: FROM FII_BUDGET_INTERFACE i
2661: WHERE ((trunc(i.version_date) < FIIBUUP_GLOBAL_START_DATE) OR
2662: (trunc(i.version_date) <
2663: (SELECT MAX(b.version_date)
2664: FROM FII_BUDGET_BASE b, FII_TIME_ENT_PERIOD p
2665: WHERE p.name = i.report_time_period
2666: AND b.time_id = p.ent_period_id
2667: AND b.ledger_id = i.ledger_id
2668: AND b.company_id = i.company_id

Line 2687: FROM FII_BUDGET_BASE b, FII_TIME_ENT_QTR q

2683: FROM FII_BUDGET_INTERFACE i
2684: WHERE ((trunc(i.version_date) < FIIBUUP_GLOBAL_START_DATE) OR
2685: (trunc(i.version_date) <
2686: (SELECT MAX(b.version_date)
2687: FROM FII_BUDGET_BASE b, FII_TIME_ENT_QTR q
2688: WHERE q.name = i.report_time_period
2689: AND b.time_id = q.ent_qtr_id
2690: AND b.ledger_id = i.ledger_id
2691: AND b.company_id = i.company_id

Line 2710: FROM FII_BUDGET_BASE b, FII_TIME_ENT_YEAR y

2706: FROM FII_BUDGET_INTERFACE i
2707: WHERE ((trunc(i.version_date) < FIIBUUP_GLOBAL_START_DATE) OR
2708: (trunc(i.version_date) <
2709: (SELECT MAX(b.version_date)
2710: FROM FII_BUDGET_BASE b, FII_TIME_ENT_YEAR y
2711: WHERE y.name = i.report_time_period
2712: AND b.time_id = y.ent_year_id
2713: AND b.ledger_id = i.ledger_id
2714: AND b.company_id = i.company_id

Line 4060: 'FROM FII_BUDGET_BASE bb, ';

4056: l_tmpstmt := ' WHERE trunc(bi.version_date) = trunc(:version_date) ' ||
4057: 'AND bi.report_time_period = t.name '||
4058: 'AND NOT EXISTS ('||
4059: 'SELECT 1 '||
4060: 'FROM FII_BUDGET_BASE bb, ';
4061:
4062: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt || l_bud_time_tab_name || '2 ';
4063: l_fc_sqlstmt := l_fc_sqlstmt || l_tmpstmt || l_fc_time_tab_name || '2 ';
4064:

Line 4104: ' FII_BUDGET_BASE bb, ';

4100: ' bi.prod_category_id, bi.user_dim1_id, '||
4101: ' -2, max(bb.version_date), NULL, NULL, NULL, NULL, '||
4102: ' SYSDATE, :user_id, SYSDATE, :user_id, :login_id '||
4103: ' FROM FII_BUDGET_INTERFACE bi, '||
4104: ' FII_BUDGET_BASE bb, ';
4105:
4106: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt || l_bud_time_tab_name;
4107: l_fc_sqlstmt := l_fc_sqlstmt || l_tmpstmt || l_fc_time_tab_name;
4108:

Line 4121: 'FROM FII_BUDGET_BASE bb2, ';

4117: l_tmpstmt := 'AND bb.version_date < trunc(bi.version_date) '||
4118: 'AND bb.no_version_flag = ''N'' ' ||
4119: 'AND NOT EXISTS ( '||
4120: 'SELECT 1 '||
4121: 'FROM FII_BUDGET_BASE bb2, ';
4122:
4123: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt || l_bud_time_tab_name || '2 ';
4124: l_fc_sqlstmt := l_fc_sqlstmt || l_tmpstmt || l_fc_time_tab_name || '2 ';
4125:

Line 4189: ' FROM FII_BUDGET_INTERFACE bi, FII_BUDGET_BASE bb, ';

4185: ' bi.prod_category_id, bi.user_dim1_id, '||
4186: ' -3, NULL, bb.prim_amount_total, bb.prim_amount_g, '||
4187: ' bb.sec_amount_total, bb.sec_amount_g, '||
4188: ' SYSDATE, :user_id, SYSDATE, :user_id, :login_id '||
4189: ' FROM FII_BUDGET_INTERFACE bi, FII_BUDGET_BASE bb, ';
4190:
4191: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt || l_bud_time_tab_name;
4192: l_fc_sqlstmt := l_fc_sqlstmt || l_tmpstmt || l_fc_time_tab_name;
4193:

Line 4262: ' FROM FII_BUDGET_INTERFACE bi, FII_BUDGET_BASE bb2, ';

4258: ' -4, bb2.version_date, bb2.prim_amount_total, '||
4259: ' bb2.prim_amount_g, '||
4260: ' bb2.sec_amount_total, bb2.sec_amount_g, '||
4261: ' SYSDATE, :user_id, SYSDATE, :user_id, :login_id '||
4262: ' FROM FII_BUDGET_INTERFACE bi, FII_BUDGET_BASE bb2, ';
4263:
4264: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt || l_bud_time_tab_name;
4265: l_fc_sqlstmt := l_fc_sqlstmt || l_tmpstmt || l_fc_time_tab_name;
4266:

Line 4280: ' FROM FII_BUDGET_BASE bb, ';

4276: ' bb.ledger_id, bb.company_id, '||
4277: ' bb.cost_center_id,bb.fin_category_id, '||
4278: ' bb.category_id, bb.user_dim1_id'||
4279: ' order by bb.version_date desc) Rank '||
4280: ' FROM FII_BUDGET_BASE bb, ';
4281:
4282: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt || l_bud_time_tab_name;
4283: l_fc_sqlstmt := l_fc_sqlstmt || l_tmpstmt || l_fc_time_tab_name;
4284:

Line 4563: ' FROM FII_BUDGET_BASE bb, ';

4559: 'AND bi.plan_type_code = ''F'' ';
4560:
4561: l_tmpstmt := ' AND NOT EXISTS ( '||
4562: ' SELECT 1 '||
4563: ' FROM FII_BUDGET_BASE bb, ';
4564:
4565: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt || l_bud_time_tab_name || '2 ';
4566: l_fc_sqlstmt := l_fc_sqlstmt || l_tmpstmt || l_fc_time_tab_name || '2 ';
4567:

Line 4613: ' FII_BUDGET_BASE bb2, '||

4609: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt || l_bud_time_tab_name || ', ';
4610: l_fc_sqlstmt := l_fc_sqlstmt || l_tmpstmt || l_fc_time_tab_name || ', ';
4611:
4612: l_tmpstmt :=
4613: ' FII_BUDGET_BASE bb2, '||
4614: ' (SELECT v.name, v.plan_type_code, v.ledger_id, v.company_id, '||
4615: ' v.cost_center_id, v.fin_category_id, v.category_id, '||
4616: ' v.user_dim1_id, v.version_date '||
4617: ' FROM ( '||

Line 4626: ' FROM FII_BUDGET_BASE bb, ';

4622: ' bb.ledger_id, bb.company_id, '||
4623: ' bb.cost_center_id,bb.fin_category_id, '||
4624: ' bb.category_id, bb.user_dim1_id'||
4625: ' order by bb.version_date desc) Rank '||
4626: ' FROM FII_BUDGET_BASE bb, ';
4627:
4628: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt || l_bud_time_tab_name;
4629: l_fc_sqlstmt := l_fc_sqlstmt || l_tmpstmt || l_fc_time_tab_name;
4630:

Line 4944: 'FROM FII_BUDGET_INTERFACE i, FII_BUDGET_DELTAS d, FII_BUDGET_BASE b, ';

4940: ' - d.orig_sec_amount_total + d.orig_sec_amount_g '||
4941: '), '||
4942: ' DECODE(d.data_type, -4, trunc(i.version_date), NULL), '||
4943: ' d.data_type, ''N'' '||
4944: 'FROM FII_BUDGET_INTERFACE i, FII_BUDGET_DELTAS d, FII_BUDGET_BASE b, ';
4945:
4946: ELSE
4947: l_tmpstmt := ' DECODE (d.data_type, '||
4948: ' -5, DECODE(i.conversion_rate, '||

Line 4961: 'FROM FII_BUDGET_INTERFACE i, FII_BUDGET_DELTAS d, FII_BUDGET_BASE b,';

4957: ' ),'||
4958: ' DECODE(d.data_type, -6, trunc(i.version_date), NULL), '||
4959: ' d.data_type,'||
4960: ' ''Y'' '||
4961: 'FROM FII_BUDGET_INTERFACE i, FII_BUDGET_DELTAS d, FII_BUDGET_BASE b,';
4962: END IF;
4963:
4964: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt || l_bud_time_tab_name ||
4965: ' WHERE d.time_id = t.' || l_bud_join_col_name;

Line 5184: -- in fii_budget_base.

5180: --
5181: -- Purpose
5182: -- This routine determines the adjustments we need to make to the rolled
5183: -- up time dimension for cases where we are overwriting existing records
5184: -- in fii_budget_base.
5185: -- Arguments
5186: -- version_date
5187: -- Example
5188: -- result := FII_BUDGET_FORECAST_C.Adjust_Amount;

Line 5255: 'INSERT INTO FII_BUDGET_BASE_T '||

5251: g_phase := 'Build the SQL statement';
5252:
5253: -- Start building the SQL statement
5254: l_tmpstmt :=
5255: 'INSERT INTO FII_BUDGET_BASE_T '||
5256: ' (plan_type_code, version_date, overwrite_version_date, '||
5257: ' no_version_flag, '||
5258: ' data_type, prim_amount_total, sec_amount_total, '||
5259: ' prim_amount_g, sec_amount_g, creation_date, created_by, '||

Line 5280: ' FROM FII_BUDGET_BASE b, FII_BUDGET_STG s '||

5276: ' b.company_id, b.cost_center_id, b.fin_category_id, '||
5277: ' b.category_id, b.user_dim1_id, b.user_dim2_id, '||
5278: ' b.time_id, b.period_type_id, '||
5279: ' s.day, s.week, s.period, s.quarter, s.year '||
5280: ' FROM FII_BUDGET_BASE b, FII_BUDGET_STG s '||
5281: ' WHERE b.plan_type_code = s. plan_type_code '||
5282: ' AND b.ledger_id = s.ledger_id '||
5283: ' AND b.company_id = s.company_id '||
5284: ' AND b.cost_center_id = s.cost_center_id '||

Line 5349: v2 => 'FII_BUDGET_BASE_T');

5345: token_num => 2,
5346: t1 => 'NUM',
5347: v1 => TO_CHAR(NVL(SQL%ROWCOUNT, 0)),
5348: t2 => 'TABLE',
5349: v2 => 'FII_BUDGET_BASE_T');
5350: END IF;
5351:
5352: IF (FIIBUUP_DEBUG) THEN
5353: FII_MESSAGE.Write_Log

Line 5372: v2 => 'FII_BUDGET_BASE_T');

5368: token_num => 2,
5369: t1 => 'NUM',
5370: v1 => TO_CHAR(NVL(SQL%ROWCOUNT, 0)),
5371: t2 => 'TABLE',
5372: v2 => 'FII_BUDGET_BASE_T');
5373: END IF;
5374:
5375: l_bud_sqlstmt := NULL;
5376: l_fc_sqlstmt := NULL;

Line 5380: ' INSERT INTO FII_BUDGET_BASE_T ( '||

5376: l_fc_sqlstmt := NULL;
5377:
5378: -- Roll up adjustments along time dimension
5379: l_tmpstmt :=
5380: ' INSERT INTO FII_BUDGET_BASE_T ( '||
5381: ' version_date, overwrite_version_date, no_version_flag, '||
5382: ' data_type, period, quarter, year, plan_type_code, '||
5383: ' creation_date, created_by, last_update_date, last_updated_by, '||
5384: ' last_update_login, ledger_id, company_cost_center_org_id, '||

Line 5413: ' FROM FII_BUDGET_BASE_T s, ';

5409: ' s.company_id, s.cost_center_id, s.fin_category_id, '||
5410: ' s.category_id, s.user_dim1_id, s.user_dim2_id, '||
5411: ' sum(s.prim_amount_total), sum(s.prim_amount_g), '||
5412: ' sum(s.sec_amount_total), sum(s.sec_amount_g) '||
5413: ' FROM FII_BUDGET_BASE_T s, ';
5414:
5415: IF (FIIBUUP_BUDGET_TIME_UNIT <> 'Y') THEN
5416: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt || l_bud_time_tab_name ||
5417: ' WHERE s.plan_type_code = ''B'' ' ||

Line 5503: v2 => 'FII_BUDGET_BASE_T');

5499: token_num => 2,
5500: t1 => 'NUM',
5501: v1 => TO_CHAR(NVL(SQL%ROWCOUNT, 0)),
5502: t2 => 'TABLE',
5503: v2 => 'FII_BUDGET_BASE_T');
5504: END IF;
5505: END IF;
5506:
5507: IF (FIIBUUP_FORECAST_TIME_UNIT <> 'Y') THEN

Line 5532: v2 => 'FII_BUDGET_BASE_T');

5528: token_num => 2,
5529: t1 => 'NUM',
5530: v1 => TO_CHAR(NVL(SQL%ROWCOUNT, 0)),
5531: t2 => 'TABLE',
5532: v2 => 'FII_BUDGET_BASE_T');
5533: END IF;
5534:
5535: -- adjust_amount completed, return with success
5536: IF (FIIBUUP_DEBUG) THEN

Line 5894: -- For cases where we are overwriting existing records in fii_budget_base,

5890: -- Procedure
5891: -- Adjust_Stage
5892: --
5893: -- Purpose
5894: -- For cases where we are overwriting existing records in fii_budget_base,
5895: -- we'll add the adjustment records we need to make to the rolled up
5896: -- time dimension into fii_budget_stg.
5897: -- Arguments
5898: -- version_date

Line 5939: ' FROM FII_BUDGET_BASE_T ';

5935: ' cost_center_id, fin_category_id, category_id, '||
5936: ' user_dim1_id, user_dim2_id, prim_amount_total, '||
5937: ' prim_amount_g, sec_amount_total, sec_amount_g, '||
5938: ' data_type, overwrite_version_date, no_version_flag '||
5939: ' FROM FII_BUDGET_BASE_T ';
5940:
5941: IF (FIIBUUP_BUDGET_TIME_UNIT = 'P') THEN
5942: l_bud_sqlstmt := l_bud_sqlstmt || l_tmpstmt ||
5943: ' WHERE period IS NULL AND plan_type_code = ''B'' ';

Line 6094: -- the new amounts into fii_budget_base.

6090: -- Merge
6091: --
6092: -- Purpose
6093: -- This routine will add new records or update existing records with
6094: -- the new amounts into fii_budget_base.
6095: -- Arguments
6096: -- version_date
6097: -- Example
6098: -- result := FII_BUDGET_FORECAST_C.Merge;

Line 6120: ' MERGE /+ PARALLEL(b)/ INTO FII_BUDGET_BASE b '||

6116: g_phase := 'Build the SQL statement';
6117:
6118: -- Start building the SQL statement
6119: l_tmpstmt :=
6120: ' MERGE /*+ PARALLEL(b)*/ INTO FII_BUDGET_BASE b '||
6121: ' USING '||
6122: ' (SELECT /*+ PARALLEL(stg)*/ '||
6123: ' version_date, plan_type_code, '||
6124: ' NVL(period, NVL(quarter, year)) time_id, '||

Line 6222: v2 => 'Merging budget records into fii_budget_base...');

6218: token_num => 2,
6219: t1 => 'ROUTINE',
6220: v1 => 'merge()',
6221: t2 => 'ACTION',
6222: v2 => 'Merging budget records into fii_budget_base...');
6223: END IF;
6224:
6225: g_phase := 'Execute the built SQL l_tmpstmt';
6226: IF (FIIBUUP_BUDGET_TIME_UNIT = 'P') THEN

Line 6247: v2 => 'FII_BUDGET_BASE');

6243: token_num => 2,
6244: t1 => 'NUM',
6245: v1 => TO_CHAR(NVL(SQL%ROWCOUNT, 0)),
6246: t2 => 'TABLE',
6247: v2 => 'FII_BUDGET_BASE');
6248: END IF;
6249:
6250: IF (FIIBUUP_DEBUG) THEN
6251: FII_MESSAGE.Write_Log

Line 6257: v2 => 'Merging forecast records into fii_budget_base...');

6253: token_num => 2,
6254: t1 => 'ROUTINE',
6255: v1 => 'merge()',
6256: t2 => 'ACTION',
6257: v2 => 'Merging forecast records into fii_budget_base...');
6258: END IF;
6259:
6260: -- Bug 4674640: Added commit after enabling parallel dml
6261: FND_CONCURRENT.Af_Commit;

Line 6285: v2 => 'FII_BUDGET_BASE');

6281: token_num => 2,
6282: t1 => 'NUM',
6283: v1 => TO_CHAR(NVL(SQL%ROWCOUNT, 0)),
6284: t2 => 'TABLE',
6285: v2 => 'FII_BUDGET_BASE');
6286: END IF;
6287:
6288: FND_CONCURRENT.Af_Commit;
6289:

Line 6365: ' UPDATE FII_BUDGET_BASE b '||

6361: g_phase := 'Build the SQL statement';
6362:
6363: -- Start building the SQL statement
6364: l_tmpstmt :=
6365: ' UPDATE FII_BUDGET_BASE b '||
6366: ' SET (b.version_date, b.no_version_flag) = '||
6367: ' (SELECT MAX(s.overwrite_version_date), MAX(s.no_version_flag) '||
6368: ' FROM FII_BUDGET_STG s '||
6369: ' WHERE ';

Line 6446: v2 => 'FII_BUDGET_BASE');

6442: token_num => 2,
6443: t1 => 'NUM',
6444: v1 => TO_CHAR(NVL(SQL%ROWCOUNT, 0)),
6445: t2 => 'TABLE',
6446: v2 => 'FII_BUDGET_BASE');
6447: END IF;
6448:
6449: IF (FIIBUUP_DEBUG) THEN
6450: FII_MESSAGE.Write_Log

Line 6469: v2 => 'FII_BUDGET_BASE');

6465: token_num => 2,
6466: t1 => 'NUM',
6467: v1 => TO_CHAR(NVL(SQL%ROWCOUNT, 0)),
6468: t2 => 'TABLE',
6469: v2 => 'FII_BUDGET_BASE');
6470: END IF;
6471:
6472: FND_CONCURRENT.Af_Commit;
6473:

Line 7000: tabname => 'FII_BUDGET_BASE');

6996:
6997: -- Bug 4674640: Added gather stats for the budget base table
6998: FND_STATS.gather_table_stats
6999: (ownname => l_fii_schema,
7000: tabname => 'FII_BUDGET_BASE');
7001:
7002: execute immediate 'alter session enable parallel dml';
7003:
7004: IF (l_ret_code = 'E') THEN

Line 7301: v2 => 'Truncating FII_BUDGET_BASE_T...');

7297: token_num => 2,
7298: t1 => 'ROUTINE',
7299: v1 => 'Main()',
7300: t2 => 'ACTION',
7301: v2 => 'Truncating FII_BUDGET_BASE_T...');
7302: END IF;
7303:
7304: g_phase := 'Truncate the staging table FII_BUDGET_BASE_T';
7305: FII_UTIL.truncate_table ('FII_BUDGET_BASE_T', 'FII', g_retcode);

Line 7304: g_phase := 'Truncate the staging table FII_BUDGET_BASE_T';

7300: t2 => 'ACTION',
7301: v2 => 'Truncating FII_BUDGET_BASE_T...');
7302: END IF;
7303:
7304: g_phase := 'Truncate the staging table FII_BUDGET_BASE_T';
7305: FII_UTIL.truncate_table ('FII_BUDGET_BASE_T', 'FII', g_retcode);
7306:
7307: IF (FIIBUUP_DEBUG) THEN
7308: FII_MESSAGE.Write_Log

Line 7305: FII_UTIL.truncate_table ('FII_BUDGET_BASE_T', 'FII', g_retcode);

7301: v2 => 'Truncating FII_BUDGET_BASE_T...');
7302: END IF;
7303:
7304: g_phase := 'Truncate the staging table FII_BUDGET_BASE_T';
7305: FII_UTIL.truncate_table ('FII_BUDGET_BASE_T', 'FII', g_retcode);
7306:
7307: IF (FIIBUUP_DEBUG) THEN
7308: FII_MESSAGE.Write_Log
7309: (msg_name => 'FII_ROUTINE',

Line 7389: g_phase := 'Delete from FII_BUDGET_BASE when ledgers are no longer set up';

7385: /* Bug 4655730: Commented out this delete statement due to performance issue
7386: and also deleting ledgers from FDS is a corner case. We'll have a long
7387: term fix for this tracked in bug 4660166.
7388:
7389: g_phase := 'Delete from FII_BUDGET_BASE when ledgers are no longer set up';
7390:
7391: IF (FIIBUUP_DEBUG) THEN
7392: FII_MESSAGE.Write_Log
7393: (msg_name => 'FII_ROUTINE',

Line 7398: v2 => 'Deleting records from FII_BUDGET_BASE for ledgers that is no longer set up in FDS...');

7394: token_num => 2,
7395: t1 => 'ROUTINE',
7396: v1 => 'Main()',
7397: t2 => 'ACTION',
7398: v2 => 'Deleting records from FII_BUDGET_BASE for ledgers that is no longer set up in FDS...');
7399: END IF;
7400:
7401: DELETE FROM fii_budget_base
7402: WHERE ledger_id IN (

Line 7401: DELETE FROM fii_budget_base

7397: t2 => 'ACTION',
7398: v2 => 'Deleting records from FII_BUDGET_BASE for ledgers that is no longer set up in FDS...');
7399: END IF;
7400:
7401: DELETE FROM fii_budget_base
7402: WHERE ledger_id IN (
7403: SELECT DISTINCT ledger_id
7404: FROM fii_budget_base
7405: WHERE ledger_id NOT IN (SELECT ledger_id

Line 7404: FROM fii_budget_base

7400:
7401: DELETE FROM fii_budget_base
7402: WHERE ledger_id IN (
7403: SELECT DISTINCT ledger_id
7404: FROM fii_budget_base
7405: WHERE ledger_id NOT IN (SELECT ledger_id
7406: FROM fii_slg_assignments ));
7407:
7408: IF (FIIBUUP_DEBUG) THEN

Line 7415: v2 => 'FII_BUDGET_BASE');

7411: token_num => 2,
7412: t1 => 'NUM',
7413: v1 => TO_CHAR(NVL(SQL%ROWCOUNT, 0)),
7414: t2 => 'TABLE',
7415: v2 => 'FII_BUDGET_BASE');
7416: END IF;
7417: */
7418: -- Process NULL version dates
7419: l_null_ver_date_flag := 'N';

Line 7481: v2 => 'Truncating FII_BUDGET_BASE_T...');

7477: token_num => 2,
7478: t1 => 'ROUTINE',
7479: v1 => 'Main()',
7480: t2 => 'ACTION',
7481: v2 => 'Truncating FII_BUDGET_BASE_T...');
7482: END IF;
7483:
7484: g_phase := 'Truncate the staging table FII_BUDGET_BASE_T';
7485: FII_UTIL.truncate_table ('FII_BUDGET_BASE_T', 'FII', g_retcode);

Line 7484: g_phase := 'Truncate the staging table FII_BUDGET_BASE_T';

7480: t2 => 'ACTION',
7481: v2 => 'Truncating FII_BUDGET_BASE_T...');
7482: END IF;
7483:
7484: g_phase := 'Truncate the staging table FII_BUDGET_BASE_T';
7485: FII_UTIL.truncate_table ('FII_BUDGET_BASE_T', 'FII', g_retcode);
7486:
7487: IF (FIIBUUP_DEBUG) THEN
7488: FII_MESSAGE.Write_Log

Line 7485: FII_UTIL.truncate_table ('FII_BUDGET_BASE_T', 'FII', g_retcode);

7481: v2 => 'Truncating FII_BUDGET_BASE_T...');
7482: END IF;
7483:
7484: g_phase := 'Truncate the staging table FII_BUDGET_BASE_T';
7485: FII_UTIL.truncate_table ('FII_BUDGET_BASE_T', 'FII', g_retcode);
7486:
7487: IF (FIIBUUP_DEBUG) THEN
7488: FII_MESSAGE.Write_Log
7489: (msg_name => 'FII_ROUTINE',

Line 7571: tabname => 'FII_BUDGET_BASE');

7567:
7568: -- Bug 4674640: Added gather stats for the budget base table
7569: FND_STATS.gather_table_stats
7570: (ownname => l_fii_schema,
7571: tabname => 'FII_BUDGET_BASE');
7572:
7573: execute immediate 'alter session enable parallel dml';
7574:
7575: IF (FIIBUUP_DEBUG) THEN