APPS.FII_TIME_HOOK SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 29

  select count(*) into l_count
  from edw_time_cal_day_lstg where ep_cal_period_fk = 'oracle_source' and collection_status='READY';

Line: 36

    edw_log.put_line(to_char(l_count)||' records are ready to update.');

Line: 41

  select instance_code
  into   l_master_instance
  from   edw_system_parameters;

Line: 47

  select count(distinct instance) into instance_count
  from edw_time_cal_day_lstg
  where ep_cal_period_fk = 'oracle_source' and collection_status='READY' and instance <> l_master_instance;

Line: 59

    select min(start_date), max(end_date)
    into l_ep_min, l_ep_max
    from edw_time_ep_cal_period_lstg;

Line: 62

    edw_log.put_line('Data is collected from the master instance, Enterprise Calendar in the staging table will be used to update the Calendar Day foreign keys.');

Line: 67

    select min(start_date), max(end_date)
    into l_ep_min, l_ep_max
    from edw_time_ep_cal_period_ltc;

Line: 70

    edw_log.put_line('Data is not collected from the master instance, Enterprise Calendar in the level table will be used to update the Calendar Day foreign keys.');

Line: 75

  l_statement:='update EDW_TIME_CAL_DAY_LSTG a set ep_cal_period_fk=nvl(
  (select ep.cal_period_pk from '||l_table||' ep
  where a.calendar_date between ep.start_date and ep.end_date
  and ep.timespan=(select min(ep1.timespan) from '||l_table||' ep1
  where a.calendar_date between ep1.start_date and ep1.end_date)
  and rownum=1'||l_join||'),''NA_EDW'')
  where a.ep_cal_period_fk=''oracle_source'' and a.collection_status=''READY''';

Line: 91

  select count(*), min(calendar_date), max(calendar_date)
  into na_count, l_min, l_max
  from edw_time_cal_day_lstg where ep_cal_period_fk = 'NA_EDW' and collection_status='READY';

Line: 96

    edw_log.put_line('WARNING:The foreign key up the Enterprise Calendar Hierarchy is not updated for '||to_char(na_count)||' records!');

Line: 105

  edw_log.put_line(to_char(l_count-na_count)||' records has been updated with the correct enterprise calendar!');