OBIEE : Perl SQL get Date Columns and Generate Week of the Year Columns

This Perl program is used to extract the CREATED_DATE column from CONTRACT_DASHBOARD table and then Inserts into the TIME_DIM_NEW table with the additional 2 columns Quarter($qtr) and Week of the Year($week) which are generated using the below perl code.

#!D:\oracle\product\10.2.0\db_1\perl\5.8.3\bin\MSWin32-x86-multi-thread\perl.exe  #   create table TIME_DIM_NEW AS (SELECT * from TIME_DIM WHEREMONTH='srikanth')  #   SELECT count(DISTINCT to_date(CREATED_DATE, 'dd/mm/rrrr')) FROM CONTRACT_DASHBOARD;  #   ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time)    use strict;  use Time::Local;  use DBI;    my $dbh = DBI->connect( 'dbi:Oracle:ORCL','SRIKUSER','SRIKPASSWD', ) || die "Database connection not made: $DBI::errstr";    my $sth = $dbh->prepare(qq!SELECT DISTINCT to_date(CREATED_DATE,'dd/mm/rrrr') CREATED_DATE FROM CONTRACT_DASHBOARD!);    $sth->execute();    my %mHsh = ("jan" => 1, "feb" => 2, "mar" => 3, "apr" => 4,"may" => 5, "jun" => 6, "jul" => 7, "aug" => 8, "sep" => 9, "oct" => 10, "nov" => 11, "dec" => 12 );    my  @splArr;  my $week = 0;  my ($qtr, $year, $updsql);      #  Fetching all the rows with "15-mar-2012" format  while (my $h = $sth->fetchrow_hashref())  {      @splArr = split(/\-/, $h->{CREATED_DATE});      #  Creating a week by brute-force method  #    if ( ($splArr[0] >= 1) && ($splArr[0] <= 7) ) {  #        $week = ($mHsh{lc($splArr[1])} - 1) * 4 + 1;   #    }elsif ( ($splArr[0] >= 8) && ($splArr[0] <= 14) ) {  #        $week = ($mHsh{lc($splArr[1])} - 1) * 4 + 2;  #    }elsif ( ($splArr[0] >= 15) && ($splArr[0] <= 21) ) {  #        $week = ($mHsh{lc($splArr[1])} - 1) * 4 + 2;  #    }elsif ( ($splArr[0] >= 22) && ($splArr[0] <= 31) ) {  #        $week = ($mHsh{lc($splArr[1])} - 1) * 4 + 3;  #    } else { # nothing  #    }        #   Getting Quarter of the Year using the month      if ( ($mHsh{lc($splArr[1])} >= 1) && ($mHsh{lc($splArr[1])} <= 3) ) {              $qtr = 1;      } elsif ( ($mHsh{lc($splArr[1])} >= 4) && ($mHsh{lc($splArr[1])} <= 6) ) {              $qtr = 2;      } elsif ( ($mHsh{lc($splArr[1])} >= 7) && ($mHsh{lc($splArr[1])} <= 9) ) {              $qtr = 3;      } elsif ( ($mHsh{lc($splArr[1])} >= 10) && ($mHsh{lc($splArr[1])} <= 12) ) {              $qtr = 4;      }        if ( $splArr[2] =~ /([0-9]+)\s*/ ) {          $year = $1;      }else { die "Cannot  update with proper Year: $year" }          #  Getting week of the day using today's date.    # my @newArr = localtime(time);    # my ($MONTHDAY, $WEEKDAY, $YEARDAY) = (localtime(time))[3,6,7];    # $week = int($YEARDAY / 7) + 1      #  Getting this $week uses the Time::Local module.      my $gmtime = timegm(0, 0, 0, $splArr[0], $splArr[1]-1, $year-1900);       my @gmtime = gmtime($gmtime);      $week = int($gmtime[7] / 7 ) + 1;          my $rs = $dbh->prepare("INSERT INTO TIME_DIM_NEW (CONT_CREATED_DT, YEAR, QTR, MONTH, WEEK, MONTH_NUM)  VALUES(?, ?, ?, ?, ?, ?)");         $rs->execute($h->{CREATED_DATE}, $year, $qtr, $splArr[1], $week, $mHsh{lc($splArr[1])}) || &die_clean("Couldn't execute sql".$dbh->errstr."\n" );          #  An another Alternate way to inserting into a table using do() instead of execute().      # my $rs = $dbh->do("INSERT INTO TIME_DIM_NEW(CONT_CREATED_DT, YEAR, QTR, MONTH, WEEK, MONTH_NUM) VALUES(\'$h->{CREATED_DATE}\', $year, $qtr, \'$splArr[1]\', $week, $mHsh{lc($splArr[1])})");      # die "$dbh->errstr" if (!$rs);    } # End of while    $dbh->disconnect;  

Summary:
Initially you can observe that the "SELECT DISTINCT ....." statment gets all the dates with 'dd/mm/yyyy' format for e.g: 15-mar-2012.
Next in the while loop observe $qtr and $week is being generated with if-elseif condition and gmtime() funtion respectively.
Then these are all inserted again into a table TIME_DIM_NEW.