-- ABS-42967/ ABS-46380

---DATE_YEARWEEKDAY table---
CREATE TABLE DATE_YEARWEEKDAY
(
  DATE_   DATE NOT NULL,
  YEAR    NUMBER(4) NOT NULL,
  WEEK    NUMBER(2) NOT NULL,
  DAY     NUMBER(1) NOT NULL,
  US_YEAR NUMBER(4) NOT NULL,
  US_WEEK NUMBER(2) NOT NULL
);

ALTER TABLE DATE_YEARWEEKDAY
  ADD CONSTRAINT XPKDATE_YEARWEEKDAY PRIMARY KEY (DATE_);
  
CREATE UNIQUE INDEX XAK1DATE_YEARWEEKDAY ON DATE_YEARWEEKDAY (YEAR ASC, WEEK ASC, DAY ASC);
CREATE UNIQUE INDEX XAK2DATE_YEARWEEKDAY ON DATE_YEARWEEKDAY (US_YEAR ASC, US_WEEK ASC, DAY ASC);

--procedure to fill new table
create or replace procedure fill_date_yearweek as
  lv_d_start_val_date date := to_date('31-12-1990', 'dd-mm-yyyy');
  lv_d_end_val_date   date := to_date('31-12-2099', 'dd-mm-yyyy');
  lv_d_date           date;
  lv_us_week          number(2);
  lv_us_year          number(4);
  lv_noofrec          number;

  function getamericanweekno(fp_date in date) return number is
    lv_weeknumber number;
    lv_yearnumber number;
    lv_daynumber  number;
  begin
    lv_daynumber := to_number(to_char(fp_date, 'D'));
    if lv_daynumber > 1 then
      begin
        lv_weeknumber := ceil((7 + (trunc(fp_date, 'd') - trunc(fp_date, 'Y'))) / 7);
        if lv_weeknumber >= 53 then
          lv_yearnumber := to_number(to_char(fp_date, 'YYYY') + 1);
          if to_date('01-01-' || to_char(lv_yearnumber), 'dd-mm-yyyy') - 7 < trunc(fp_date, 'd') then
            lv_weeknumber := 1;
          end if;
        end if;
      end;
    end if;
    if lv_daynumber = 1 then
      begin
        lv_weeknumber := ceil((7 + (trunc(fp_date - 7, 'd') - trunc(fp_date - 7, 'Y'))) / 7);
        if lv_weeknumber >= 53 then
          lv_yearnumber := to_number(to_char(fp_date - 7, 'YYYY') + 1);
          if to_date('01-01-' || to_char(lv_yearnumber), 'dd-mm-yyyy') - 7 <
             trunc(fp_date - 7, 'd') then
            lv_weeknumber := 1;
          end if;
        end if;
      end;
    end if;
    return lv_weeknumber;
  end getamericanweekno;

  function getyearofweekus(vdate date) return int is
    yearnumber          integer;
    iso_weeknumber      integer;
    american_weeknumber integer;
  begin
    yearnumber          := cast(to_char(vdate, 'IYYY') as numeric);
    iso_weeknumber      := getisoweek(vdate);
    american_weeknumber := getamericanweekno(vdate);
    if american_weeknumber in (1, 2) and american_weeknumber < iso_weeknumber then
      yearnumber := yearnumber + 1;
    end if;
    return yearnumber;
  end getyearofweekus;
begin
  select count(1) into lv_noofrec from date_yearweekday;
  if lv_noofrec = 0 then
  begin
    --run only once to fill the table
      for i in 0 .. lv_d_end_val_date - lv_d_start_val_date loop
      begin
        lv_d_date := lv_d_start_val_date + i;
        insert into date_yearweekday
        (date_, year, week, day, us_year, us_week)
        values
        (lv_d_date, getyearofweek(lv_d_date), getisoweek(lv_d_date), getabsdaynumber(lv_d_date),
         getyearofweek(lv_d_date) * -1, getisoweek(lv_d_date) * -1);
      end;
      end loop;
  --use american settings to get american year/week.
  execute immediate 'ALTER SESSION SET NLS_LANGUAGE= ''AMERICAN'' NLS_TERRITORY= ''AMERICA''';
  for x in (select * from date_yearweekday) loop
    begin
      lv_us_week := getamericanweekno(x.date_);
      lv_us_year := getyearofweekus(x.date_);
      update date_yearweekday z
      set    z.us_year = lv_us_year, z.us_week = lv_us_week
      where  z.date_ = x.date_;
    end;
  end loop;
  end;
  commit;
  end if;
exception
  when others then
    rollback;
end fill_date_yearweek;
/

execute fill_date_yearweek;

drop procedure fill_date_yearweek;
