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.

[Home] [Help]

APPS.FII_GL_JE_B_C dependencies on FII_GL_JE_SUMMARY_STG

Line 74: FROM fii_gl_je_summary_stg

70: functional_currency,
71: decode( prim_conversion_rate,
72: -3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
73: least(sysdate, effective_date)) effective_date
74: FROM fii_gl_je_summary_stg
75: WHERE prim_conversion_rate < 0;
76:
77: --bug 3677737: use least(sysdate, effective_date) to replace effective_date
78: cursor SecMissingRate is

Line 84: FROM fii_gl_je_summary_stg

80: functional_currency,
81: decode( sec_conversion_rate,
82: -3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
83: least(sysdate, effective_date) ) effective_date
84: FROM fii_gl_je_summary_stg
85: WHERE sec_conversion_rate < 0;
86: */
87: cursor PSMissingRate is
88: SELECT DISTINCT

Line 100: FROM fii_gl_je_summary_stg

96: decode( sec_conversion_rate,
97: -3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
98: least(sysdate, effective_date))
99: ELSE NULL END sec_effective_date
100: FROM fii_gl_je_summary_stg
101: WHERE prim_conversion_rate < 0
102: OR sec_conversion_rate < 0;
103:
104: BEGIN

Line 462: FROM fii_gl_je_summary_stg

458:
459: begin
460: SELECT 1
461: INTO l_count2
462: FROM fii_gl_je_summary_stg
463: WHERE ROWNUM = 1;
464: exception
465: when NO_DATA_FOUND then
466: l_count2 := 0;

Line 537: FROM fii_gl_je_summary_stg

533:
534: begin
535: SELECT 1
536: INTO l_count2
537: FROM fii_gl_je_summary_stg
538: WHERE ROWNUM = 1;
539: exception
540: when NO_DATA_FOUND then
541: l_count2 := 0;

Line 750: FROM FII_GL_JE_SUMMARY_STG;

746: g_phase := 'For p_program_type <> L ';
747: SELECT MIN(effective_date), MAX(effective_date), sum(decode(sign(prim_conversion_rate), -1, 1, 0)) +
748: sum(decode(sign(sec_conversion_rate), -1, 1, 0)), count(*)
749: INTO l_stg_min, l_stg_max, l_conv_rate_cnt, l_row_cnt
750: FROM FII_GL_JE_SUMMARY_STG;
751:
752: END IF;
753:
754: IF l_row_cnt = 0 THEN

Line 844: TRUNCATE_TABLE('FII_GL_JE_SUMMARY_STG');

840: TRUNCATE_TABLE('FII_GL_NEW_JRL_HEADER_IDS');
841: END IF;
842:
843: IF (g_truncate_stg) THEN
844: TRUNCATE_TABLE('FII_GL_JE_SUMMARY_STG');
845: END IF;
846:
847: COMMIT;
848:

Line 871: --Insert aggregate data into FII_GL_JE_SUMMARY_STG table for week level

867:
868: BEGIN
869:
870: ---------------------------------------------------------------------
871: --Insert aggregate data into FII_GL_JE_SUMMARY_STG table for week level
872: ---------------------------------------------------------------------
873: g_phase := 'Insert aggregate data into FII_GL_JE_SUMMARY_STG table for week level';
874:
875: if g_debug_flag = 'Y' then

Line 873: g_phase := 'Insert aggregate data into FII_GL_JE_SUMMARY_STG table for week level';

869:
870: ---------------------------------------------------------------------
871: --Insert aggregate data into FII_GL_JE_SUMMARY_STG table for week level
872: ---------------------------------------------------------------------
873: g_phase := 'Insert aggregate data into FII_GL_JE_SUMMARY_STG table for week level';
874:
875: if g_debug_flag = 'Y' then
876: FII_UTIL.put_line('');
877: FII_UTIL.put_line('Inserting weekly aggregated data into FII_GL_JE_SUMMARY_STG table');

Line 877: FII_UTIL.put_line('Inserting weekly aggregated data into FII_GL_JE_SUMMARY_STG table');

873: g_phase := 'Insert aggregate data into FII_GL_JE_SUMMARY_STG table for week level';
874:
875: if g_debug_flag = 'Y' then
876: FII_UTIL.put_line('');
877: FII_UTIL.put_line('Inserting weekly aggregated data into FII_GL_JE_SUMMARY_STG table');
878: FII_UTIL.start_timer;
879: end if;
880:
881: INSERT INTO fii_gl_je_summary_stg

Line 881: INSERT INTO fii_gl_je_summary_stg

877: FII_UTIL.put_line('Inserting weekly aggregated data into FII_GL_JE_SUMMARY_STG table');
878: FII_UTIL.start_timer;
879: end if;
880:
881: INSERT INTO fii_gl_je_summary_stg
882: (
883: week,
884: cost_center_id,
885: fin_category_id,

Line 940: FROM fii_gl_je_summary_stg stg,

936: stg.last_updated_by,
937: stg.creation_date,
938: stg.created_by,
939: stg.last_update_login
940: FROM fii_gl_je_summary_stg stg,
941: fii_time_day fday
942: WHERE stg.day = fday.report_date_julian
943: GROUP BY
944: stg.cost_center_id,

Line 965: FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' rows of aggregated data into FII_GL_JE_SUMMARY_STG table');

961: stg.posted_date;
962:
963:
964: if g_debug_flag = 'Y' then
965: FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' rows of aggregated data into FII_GL_JE_SUMMARY_STG table');
966: FII_UTIL.stop_timer;
967: FII_UTIL.print_timer('Duration');
968: end if;
969:

Line 989: -- Update FII_GL_JE_SUMMARY_STG table for global amount after all error

985:
986: BEGIN
987:
988: --------------------------------------------------------------------
989: -- Update FII_GL_JE_SUMMARY_STG table for global amount after all error
990: -- checks passed.
991: --------------------------------------------------------------------
992: g_phase := 'Update global amount in FII_GL_JE_SUMMARY_STG table';
993:

Line 992: g_phase := 'Update global amount in FII_GL_JE_SUMMARY_STG table';

988: --------------------------------------------------------------------
989: -- Update FII_GL_JE_SUMMARY_STG table for global amount after all error
990: -- checks passed.
991: --------------------------------------------------------------------
992: g_phase := 'Update global amount in FII_GL_JE_SUMMARY_STG table';
993:
994: if g_debug_flag = 'Y' then
995: FII_UTIL.start_timer;
996: FII_UTIL.put_line('Updating global amount in FII_GL_JE_SUMMARY_STG');

Line 996: FII_UTIL.put_line('Updating global amount in FII_GL_JE_SUMMARY_STG');

992: g_phase := 'Update global amount in FII_GL_JE_SUMMARY_STG table';
993:
994: if g_debug_flag = 'Y' then
995: FII_UTIL.start_timer;
996: FII_UTIL.put_line('Updating global amount in FII_GL_JE_SUMMARY_STG');
997: end if;
998:
999: Update FII_GL_JE_SUMMARY_STG stg
1000: SET stg.prim_amount_g = round((stg.amount_b * prim_conversion_rate)/g_primary_mau)*g_primary_mau,

Line 999: Update FII_GL_JE_SUMMARY_STG stg

995: FII_UTIL.start_timer;
996: FII_UTIL.put_line('Updating global amount in FII_GL_JE_SUMMARY_STG');
997: end if;
998:
999: Update FII_GL_JE_SUMMARY_STG stg
1000: SET stg.prim_amount_g = round((stg.amount_b * prim_conversion_rate)/g_primary_mau)*g_primary_mau,
1001: stg.sec_amount_g = round((stg.amount_b * sec_conversion_rate)/g_secondary_mau)*g_secondary_mau,
1002: stg.committed_amount_prim = round((stg.committed_amount_b * prim_conversion_rate)/g_primary_mau)*g_primary_mau,
1003: stg.obligated_amount_prim = round((stg.obligated_amount_b * prim_conversion_rate)/g_primary_mau)*g_primary_mau,

Line 1007: FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records in FII_GL_JE_SUMMARY_STG');

1003: stg.obligated_amount_prim = round((stg.obligated_amount_b * prim_conversion_rate)/g_primary_mau)*g_primary_mau,
1004: stg.other_amount_prim = round((stg.other_amount_b * prim_conversion_rate)/g_primary_mau)*g_primary_mau;
1005:
1006: if g_debug_flag = 'Y' then
1007: FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records in FII_GL_JE_SUMMARY_STG');
1008: FII_UTIL.stop_timer;
1009: FII_UTIL.print_timer('Duration');
1010: end if;
1011:

Line 1013: --Insert aggregate data into FII_GL_JE_SUMMARY_STG table for higher

1009: FII_UTIL.print_timer('Duration');
1010: end if;
1011:
1012: ---------------------------------------------------------------------
1013: --Insert aggregate data into FII_GL_JE_SUMMARY_STG table for higher
1014: --time levels Period, Quarter and Year.
1015: ---------------------------------------------------------------------
1016: g_phase := 'Insert aggregate data into FII_GL_JE_SUMMARY_STG table';
1017:

Line 1016: g_phase := 'Insert aggregate data into FII_GL_JE_SUMMARY_STG table';

1012: ---------------------------------------------------------------------
1013: --Insert aggregate data into FII_GL_JE_SUMMARY_STG table for higher
1014: --time levels Period, Quarter and Year.
1015: ---------------------------------------------------------------------
1016: g_phase := 'Insert aggregate data into FII_GL_JE_SUMMARY_STG table';
1017:
1018: if g_debug_flag = 'Y' then
1019: FII_UTIL.put_line('');
1020: FII_UTIL.put_line('Inserting aggregated data into FII_GL_JE_SUMMARY_STG table');

Line 1020: FII_UTIL.put_line('Inserting aggregated data into FII_GL_JE_SUMMARY_STG table');

1016: g_phase := 'Insert aggregate data into FII_GL_JE_SUMMARY_STG table';
1017:
1018: if g_debug_flag = 'Y' then
1019: FII_UTIL.put_line('');
1020: FII_UTIL.put_line('Inserting aggregated data into FII_GL_JE_SUMMARY_STG table');
1021: FII_UTIL.start_timer;
1022: end if;
1023:
1024: --

Line 1028: INSERT INTO fii_gl_je_summary_stg

1024: --
1025: --bug 3356106: remove rollup by week_id (it's now handled in Sum_aggregate_week)
1026: --
1027:
1028: INSERT INTO fii_gl_je_summary_stg
1029: (year,
1030: quarter,
1031: period,
1032: day,

Line 1091: FROM fii_gl_je_summary_stg stg,

1087: stg.last_updated_by,
1088: stg.creation_date,
1089: stg.created_by,
1090: stg.last_update_login
1091: FROM fii_gl_je_summary_stg stg,
1092: fii_time_day fday
1093: WHERE stg.day = fday.report_date_julian
1094: GROUP BY
1095: stg.cost_center_id,

Line 1119: ' rows of aggregated data into FII_GL_JE_SUMMARY_STG table');

1115:
1116:
1117: if g_debug_flag = 'Y' then
1118: FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT ||
1119: ' rows of aggregated data into FII_GL_JE_SUMMARY_STG table');
1120: FII_UTIL.stop_timer;
1121: FII_UTIL.print_timer('Duration');
1122: end if;
1123:

Line 1143: -- FII_GL_JE_SUMMARY_STG into GL base summary table FII_GL_JE_SUMMARY_B.

1139: BEGIN
1140:
1141: ----------------------------------------------------------------------
1142: -- Merges newly collected/summarized records from temporary table
1143: -- FII_GL_JE_SUMMARY_STG into GL base summary table FII_GL_JE_SUMMARY_B.
1144: -- FII_GL_JE_SUMMARY_B uses the nested summary table structure.
1145:
1146: -- If the merging record is new in FII_GL_JE_SUMMARY_STG then the record
1147: -- will be inserted into FII_GL_JE_SUMMARY_B table. A merging reord is

Line 1146: -- If the merging record is new in FII_GL_JE_SUMMARY_STG then the record

1142: -- Merges newly collected/summarized records from temporary table
1143: -- FII_GL_JE_SUMMARY_STG into GL base summary table FII_GL_JE_SUMMARY_B.
1144: -- FII_GL_JE_SUMMARY_B uses the nested summary table structure.
1145:
1146: -- If the merging record is new in FII_GL_JE_SUMMARY_STG then the record
1147: -- will be inserted into FII_GL_JE_SUMMARY_B table. A merging reord is
1148: -- consoidered if the combination of period, Period_type,
1149: -- Cost center Organization id, natural Account, Journal Entry source,
1150: -- Journal Entry category, Set Of Books Id, Functional Currency Code,

Line 1189: FROM fii_gl_je_summary_stg

1185: SUM(OBLIGATED_AMOUNT_PRIM) OBLIGATED_AMOUNT_PRIM,
1186: SUM(OTHER_AMOUNT_B) OTHER_AMOUNT_B,
1187: SUM(OTHER_AMOUNT_PRIM) OTHER_AMOUNT_PRIM,
1188: POSTED_DATE
1189: FROM fii_gl_je_summary_stg
1190: WHERE year IS NOT NULL
1191: OR week IS NOT NULL
1192: GROUP BY
1193: NVL(day, NVL(week, NVL(period, NVL(quarter, year)))),

Line 1493: -- FII_GL_JE_SUMMARY_STG.

1489:
1490: ------------------------------------------------------------------
1491: -- Insert summarize journal entry lines at day level whose journal
1492: -- Header IDs are stored in FII_GL_NEW_JRL_HEADER_IDS table into
1493: -- FII_GL_JE_SUMMARY_STG.
1494: ------------------------------------------------------------------
1495: if g_debug_flag = 'Y' then
1496: FII_UTIL.put_line('Processing ID range: ' || p_start_range ||
1497: ' to ' || p_end_range);

Line 1500: l_stmt:= 'INSERT INTO FII_GL_JE_SUMMARY_STG

1496: FII_UTIL.put_line('Processing ID range: ' || p_start_range ||
1497: ' to ' || p_end_range);
1498: end if;
1499:
1500: l_stmt:= 'INSERT INTO FII_GL_JE_SUMMARY_STG
1501: (day,
1502: week,
1503: period,
1504: quarter,

Line 1629: FII_UTIL.put_line('Inserted '||l_number_of_rows||' into table FII_GL_JE_SUMMARY_STG with day level data');

1625: commit;
1626:
1627: if g_debug_flag = 'Y' then
1628: FII_UTIL.put_line('');
1629: FII_UTIL.put_line('Inserted '||l_number_of_rows||' into table FII_GL_JE_SUMMARY_STG with day level data');
1630: FII_UTIL.stop_timer;
1631: FII_UTIL.print_timer('Duration');
1632: end if;
1633:

Line 1881: g_phase := 'Inserting into FII_GL_JE_SUMMARY_STG';

1877: fii_util.start_timer;
1878: fii_util.put_line('');
1879: end if;
1880:
1881: g_phase := 'Inserting into FII_GL_JE_SUMMARY_STG';
1882: INSERT /*+ append parallel(fii_gl_je_summary_stg) */ INTO FII_GL_JE_SUMMARY_STG
1883: (day,
1884: week,
1885: period,

Line 1882: INSERT /+ append parallel(fii_gl_je_summary_stg) / INTO FII_GL_JE_SUMMARY_STG

1878: fii_util.put_line('');
1879: end if;
1880:
1881: g_phase := 'Inserting into FII_GL_JE_SUMMARY_STG';
1882: INSERT /*+ append parallel(fii_gl_je_summary_stg) */ INTO FII_GL_JE_SUMMARY_STG
1883: (day,
1884: week,
1885: period,
1886: quarter,

Line 2123: g_phase := 'Calling FND_STATS to collect statistics for FII_GL_JE_SUMMARY_STG';

2119: -- gather stats so that stats will be gathered at 10% vs 99%.
2120: commit;
2121:
2122: --Call FND_STATS to collect statistics after populating the table
2123: g_phase := 'Calling FND_STATS to collect statistics for FII_GL_JE_SUMMARY_STG';
2124: FND_STATS.gather_table_stats
2125: (ownname => g_fii_schema,
2126: tabname => 'FII_GL_JE_SUMMARY_STG');
2127:

Line 2126: tabname => 'FII_GL_JE_SUMMARY_STG');

2122: --Call FND_STATS to collect statistics after populating the table
2123: g_phase := 'Calling FND_STATS to collect statistics for FII_GL_JE_SUMMARY_STG';
2124: FND_STATS.gather_table_stats
2125: (ownname => g_fii_schema,
2126: tabname => 'FII_GL_JE_SUMMARY_STG');
2127:
2128: g_phase := 'Enabling parallel dml';
2129: execute immediate 'alter session enable parallel dml';
2130:

Line 2132: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_gl_je_summary_stg');

2128: g_phase := 'Enabling parallel dml';
2129: execute immediate 'alter session enable parallel dml';
2130:
2131: if g_debug_flag = 'Y' then
2132: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_gl_je_summary_stg');
2133: fii_util.stop_timer;
2134: fii_util.print_timer('Duration');
2135: end if;
2136:

Line 2454: select /+ no_merge parallel(FII_gl_je_summary_STG)/ distinct

2450: dt trx_date,
2451: decode(cc, l_global_prim_curr_code, 1, FII_CURRENCY.GET_GLOBAL_RATE_PRIMARY (cc,least(sysdate, dt))) PRIM_CONVERSION_RATE,
2452: decode(cc, l_global_sec_curr_code, 1, FII_CURRENCY.GET_GLOBAL_RATE_SECONDARY(cc,least(sysdate, dt))) SEC_CONVERSION_RATE
2453: from (
2454: select /*+ no_merge parallel(FII_gl_je_summary_STG)*/ distinct
2455: FUNCTIONAL_CURRENCY cc,
2456: effective_date dt
2457: from FII_gl_je_summary_STG
2458: );

Line 2457: from FII_gl_je_summary_STG

2453: from (
2454: select /*+ no_merge parallel(FII_gl_je_summary_STG)*/ distinct
2455: FUNCTIONAL_CURRENCY cc,
2456: effective_date dt
2457: from FII_gl_je_summary_STG
2458: );
2459:
2460:
2461: --Call FND_STATS to collect statistics after populating the table

Line 2565: FROM FII_GL_JE_SUMMARY_STG stg, fii_gl_revenue_rates_temp r

2561: stg.created_by,
2562: stg.last_update_date,
2563: stg.last_update_login,
2564: stg.last_updated_by
2565: FROM FII_GL_JE_SUMMARY_STG stg, fii_gl_revenue_rates_temp r
2566: where stg.year IS NOT NULL
2567: AND stg.effective_date = r.trx_date
2568: AND stg.functional_currency = r.functional_currency
2569: GROUP BY stg.day,

Line 3335: TRUNCATE_TABLE('FII_GL_JE_SUMMARY_STG');

3331: IF p_program_type = 'L' THEN
3332: IF g_debug_flag = 'Y' then
3333: FII_UTIL.put_line('Running in Initial Load mode, truncate STG, summary and other processing tables.');
3334: END IF;
3335: TRUNCATE_TABLE('FII_GL_JE_SUMMARY_STG');
3336: TRUNCATE_TABLE('FII_GL_JE_SUMMARY_B');
3337: TRUNCATE_TABLE('FII_GL_PROCESSED_HEADER_IDS');
3338:
3339: COMMIT;

Line 3487: FROM fii_gl_je_summary_stg;

3483: end if;
3484:
3485: SELECT COUNT(*)
3486: INTO stg_count
3487: FROM fii_gl_je_summary_stg;
3488:
3489: IF (stg_count > 0) THEN
3490: g_resume_flag := 'Y';
3491: ELSE

Line 3774: Update FII_GL_JE_SUMMARY_STG stg

3770:
3771: FII_UTIL.start_timer;
3772: end if;
3773:
3774: Update FII_GL_JE_SUMMARY_STG stg
3775: SET prim_conversion_rate =
3776: fii_currency.get_global_rate_primary(stg.functional_currency,least(sysdate, stg.effective_date))
3777: WHERE stg.prim_conversion_rate < 0;
3778:

Line 3789: Update FII_GL_JE_SUMMARY_STG stg

3785: end if;
3786:
3787: commit; --use commit after print out correct SQL%ROWCOUNT
3788:
3789: Update FII_GL_JE_SUMMARY_STG stg
3790: SET sec_conversion_rate =
3791: fii_currency.get_global_rate_secondary(stg.functional_currency,least(sysdate, stg.effective_date))
3792: WHERE stg.sec_conversion_rate < 0;
3793:

Line 3808: -- or missing time dimension record in the FII_GL_JE_SUMMARY_STG

3804:
3805: -----------------------------------------------------------------
3806: -- If all the child process completes successfully then Invoke
3807: -- Summary_err_check routine to check for any missing rates record
3808: -- or missing time dimension record in the FII_GL_JE_SUMMARY_STG
3809: -- table.
3810: -----------------------------------------------------------------
3811: g_phase:= 'Summarization Error Check';
3812: if g_debug_flag = 'Y' then

Line 3828: -- YTD into the FII_GL_JE_SUMMARY_STG table.

3824: g_truncate_stg := TRUE;
3825:
3826: -------------------------------------------------------------
3827: -- Call Summarization_aggreagte routine to insert PTD,QTD and
3828: -- YTD into the FII_GL_JE_SUMMARY_STG table.
3829: -------------------------------------------------------------
3830: g_phase := 'Aggregating summarized data';
3831: if g_debug_flag = 'Y' then
3832: FII_UTIL.put_line('');