[Home] [Help]
1653: and end_date >= p_from_date;
1654:
1655: cursor get_quarters (p_year number) is
1656: select distinct sequence, start_date, ent_qtr_id
1657: from HRI_time_ent_qtr
1658: where ent_year_id = p_year
1659: order by start_date;
1660:
1661: begin
1678:
1679: ELSE
1680: --Incremental run
1681: select period_year into l_period_year from gl_periods
1682: where (select nvl(min(start_date), l_from_date) from HRI_time_ent_qtr) between start_date and end_date
1683: and period_set_name = g_period_set_name
1684: and period_type = g_period_type
1685: and adjustment_period_flag='N';
1686:
1686:
1687: END IF;
1688:
1689: -- Bug 5624487
1690: -- delete from HRI_TIME_ENT_QTR where start_date <= l_to_date and end_date >= l_from_date;
1691: delete from HRI_TIME_ENT_QTR
1692: where start_date <= l_to_date and end_date >= l_from_date
1693: or end_date >= g_unassigned_day;
1694:
1687: END IF;
1688:
1689: -- Bug 5624487
1690: -- delete from HRI_TIME_ENT_QTR where start_date <= l_to_date and end_date >= l_from_date;
1691: delete from HRI_TIME_ENT_QTR
1692: where start_date <= l_to_date and end_date >= l_from_date
1693: or end_date >= g_unassigned_day;
1694:
1695: -- ----------------------
1694:
1695: -- ----------------------
1696: -- Populate Enterprise Quarter Level
1697: -- ----------------------
1698: insert into HRI_time_ent_qtr
1699: (ent_qtr_id,
1700: ent_year_id,
1701: sequence,
1702: name,
1741: -- update quarter sequence
1742: l_qtr_seq := 1;
1743: for k in get_quarters (i.ent_year_id)
1744: loop
1745: update HRI_time_ent_qtr
1746: set sequence = l_qtr_seq
1747: where ent_qtr_id = k.ent_qtr_id;
1748: l_qtr_seq := l_qtr_seq + 1;
1749: end loop;
1755: HRI_util.put_line('LOAD_ENT_QUARTER : '||to_char(l_qtr_row)||' records has been populated to Enterprise Quarter Level');
1756: end if;
1757:
1758: -- Bug 5624487
1759: insert into HRI_time_ent_qtr
1760: (ent_qtr_id,
1761: ent_year_id,
1762: sequence,
1763: name,
2635: l_start_date := l_from_date;
2636:
2637: -- Bug 4454026
2638: -- We should default the from date to the start of the fiscal year so that fix of bug 3961336 wroks fine
2639: -- which is to make the sequence in HRI_time_ent_period and HRI_time_ent_qtr independent of GL
2640: -- This is done for initial load and in case the last run was not successfull
2641:
2642: g_phase := 'Checking if all the years in the calendar are fully defined';
2643:
2722: truncate_table('HRI_TIME_WEEK');
2723: truncate_table('HRI_TIME_P445');
2724: truncate_table('HRI_TIME_YEAR445');
2725: truncate_table('HRI_TIME_ENT_PERIOD');
2726: truncate_table('HRI_TIME_ENT_QTR');
2727: truncate_table('HRI_TIME_ENT_YEAR');
2728: truncate_table('HRI_TIME_CAL_NAME');
2729: truncate_table('HRI_TIME_CAL_PERIOD');
2730: truncate_table('HRI_TIME_CAL_QTR');
2754: -- can only detect if there is any changes of the existing periods populated into time dimension
2755: -- If there is a new period added to a quarter, then requires the end date of that quarter to be updated
2756: -- function period_updated is not able to indicate that, that's why we choose to always truncate the
2757: -- tables to avoid any further unique constraint violation
2758: truncate_table('HRI_TIME_ENT_QTR');
2759: truncate_table('HRI_TIME_ENT_YEAR');
2760: end if;
2761: end if;
2762:
3114: truncate_table('HRI_TIME_WEEK');
3115: truncate_table('HRI_TIME_P445');
3116: truncate_table('HRI_TIME_YEAR445');
3117: truncate_table('HRI_TIME_ENT_PERIOD');
3118: truncate_table('HRI_TIME_ENT_QTR');
3119: truncate_table('HRI_TIME_ENT_YEAR');
3120: truncate_table('HRI_TIME_CAL_PERIOD');
3121: truncate_table('HRI_TIME_CAL_QTR');
3122: truncate_table('HRI_TIME_CAL_YEAR');
3618: sysdate,
3619: g_user_id,
3620: g_user_id,
3621: g_login_id
3622: from HRI_TIME_ENT_QTR
3623: where start_date >= c1_rec.ent_year_start_date
3624: and start_date <= c1_rec.ent_qtr_start_date
3625: and end_date < c1_rec.report_date
3626: union all
3636: sysdate,
3637: g_user_id,
3638: g_user_id,
3639: g_login_id
3640: from HRI_TIME_ENT_QTR
3641: where start_date >= c1_rec.ent_year_start_date
3642: and start_date <= c1_rec.ent_qtr_start_date
3643: and end_date >= c1_rec.report_date;
3644: