Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\rtasst\owb

Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\rtasst\owb_timeseries.sql

--
-- 
-- (PACKAGED VERSION OWB_TIMESERIES.SQL)
-- CONTAINS TIMESERIES DATA GENERATING TABLE FUNCTIONS AND HELPER FUNCTIONS.
-- HISTORY 
-- 	SROYCHOW,   August 11, 2003,  Created.
--      SROYCHOW,   August 18, 2003,  add code for Fiscal Year. (with 544 & 445) 
--
-- ========================================================
-----------------------------------------------------------
---
---   DROP TYPES AND TABLE TYPES
---
-----------------------------------------------------------

-------------------------------------------------
---
---   DAY IS COMMON TO CALENDAR AND FISCAL YEARS 
---
-------------------------------------------------
DROP FUNCTION DAY_GENERATOR_TFN;
DROP TYPE DAY_TYPE_TABLE ;
DROP TYPE DAY_TYPE ;

-------------------------------------------------
---
---   FISCAL CALENDAR: WEEK, MONTH, QUARTER, YEAR 
---
-------------------------------------------------

DROP FUNCTION FISCAL_WEEK_GENERATOR_TFN;
DROP TYPE FISCAL_WEEK_TYPE_TABLE ;
DROP TYPE FISCAL_WEEK_TYPE ;

DROP FUNCTION FISCAL_MONTH_GENERATOR_TFN;
DROP TYPE FISCAL_MONTH_TYPE_TABLE ;
DROP TYPE FISCAL_MONTH_TYPE ;

DROP FUNCTION FISCAL_QUARTER_GENERATOR_TFN;
DROP TYPE FISCAL_QUARTER_TYPE_TABLE ;
DROP TYPE FISCAL_QUARTER_TYPE ;

DROP FUNCTION FISCAL_YEAR_GENERATOR_TFN;
DROP TYPE FISCAL_YEAR_TYPE_TABLE ;
DROP TYPE FISCAL_YEAR_TYPE ;

-------------------------------------------------
---
---   CALENDAR: WEEK, MONTH, QUARTER, YEAR 
---
-------------------------------------------------

DROP FUNCTION CAL_WEEK_GENERATOR_TFN;
DROP TYPE CAL_WEEK_TYPE_TABLE ;
DROP TYPE CAL_WEEK_TYPE ;

DROP FUNCTION CAL_MONTH_GENERATOR_TFN;
DROP TYPE CAL_MONTH_TYPE_TABLE ;
DROP TYPE CAL_MONTH_TYPE ;

DROP FUNCTION CAL_QUARTER_GENERATOR_TFN;
DROP TYPE CAL_QUARTER_TYPE_TABLE ;
DROP TYPE CAL_QUARTER_TYPE ;

DROP FUNCTION CAL_YEAR_GENERATOR_TFN;
DROP TYPE CAL_YEAR_TYPE_TABLE ;
DROP TYPE CAL_YEAR_TYPE ;

-------------------------------------------------
---
---   OWB TIME SERIES 
---
-------------------------------------------------

DROP PACKAGE OWB_TIMESERIES;

-- ========================================================
-- END OF DROPS
-- ========================================================

-- ========================================================

-- ========================================================
-- BEGIN CREATING (TYPES AND TABLE TYPES)
-- ========================================================
-------------------------------------------------
---
---  LEVEL 1
---  DAY IS COMMON TO CALENDAR AND FISCAL YEARS 
---
-------------------------------------------------

CREATE TYPE DAY_TYPE AS
OBJECT 
(
   --
   -- GENERAL DAY INFO
   --
   DAY_ID			NUMBER,
   DAY 				DATE,
   DAY_JULIANDATE 		NUMBER,
   DAY_DESCRIPTION 		VARCHAR2 (2000),
   DAY_STARTDATE  		DATE,
   DAY_ENDDATE  		DATE,
   DAY_TIMESPAN  		NUMBER,

--
   -- CALENDAR YEAR DAY INFO
   --
   DAY_OF_CAL_WEEK		NUMBER,
   DAY_OF_CAL_MONTH    		NUMBER,
   DAY_OF_CAL_QUARTER		NUMBER,
   DAY_OF_CAL_YEAR		NUMBER,

--
   -- FISCAL CALENDAR YEAR DAY INFO
   --
   DAY_OF_FISCAL_WEEK		NUMBER,
   DAY_OF_FISCAL_MONTH    	NUMBER,
   DAY_OF_FISCAL_QUARTER	NUMBER,
   DAY_OF_FISCAL_YEAR		NUMBER,

--
   -- ADDITIONAL INFO and IDs
   -- 
   CAL_YID			NUMBER,
   CAL_WID			NUMBER,
   CAL_MID			NUMBER,
   CAL_QID			NUMBER,
   FISCAL_WID			NUMBER,
   FISCAL_MID			NUMBER,
   FISCAL_QID			NUMBER,
   FISCAL_YID 			NUMBER,
   FISCAL_SDATE                 DATE,
   FISCAL_DOW			NUMBER,
   FISCAL_TYPE			NUMBER
);
/

CREATE TYPE DAY_TYPE_TABLE AS TABLE OF DAY_TYPE ;
/

-------------------------------------------------
---
---  LEVEL 2.1
---  FISCAL WEEK TYPE
---
-------------------------------------------------

CREATE TYPE FISCAL_WEEK_TYPE AS
OBJECT 
(
   FISCAL_WEEK_ID 			NUMBER,
   FISCAL_WEEK_NUMBER			NUMBER,
   FISCAL_WEEK_DESCRIPTION 		VARCHAR2 (2000),
   FISCAL_WEEK_STARTDATE  		DATE,
   FISCAL_WEEK_ENDDATE  		DATE,
   FISCAL_WEEK_TIMESPAN  		NUMBER,
   FISCAL_WEEK_OF_MONTH 		NUMBER,
   FISCAL_WEEK_OF_QUARTER  		NUMBER,
   FISCAL_WEEK_OF_YEAR  		NUMBER,
   FISCAL_SDATE                         DATE,
   FISCAL_DOW			        NUMBER,
   FISCAL_TYPE			        NUMBER,
   FISCAL_MID			        NUMBER,
   FISCAL_QID			        NUMBER,
   FISCAL_YID 			        NUMBER
);
/

CREATE TYPE FISCAL_WEEK_TYPE_TABLE AS TABLE OF FISCAL_WEEK_TYPE ;
/

-------------------------------------------------
---
---  LEVEL 2.2
---  FISCAL WEEK TYPE
---
-------------------------------------------------

CREATE TYPE FISCAL_MONTH_TYPE AS
OBJECT 
(
   FISCAL_MONTH_ID 			NUMBER,
   FISCAL_MONTH_NUMBER 			NUMBER,
   FISCAL_MONTH_DESCRIPTION 		VARCHAR2 (2000),
   FISCAL_MONTH_STARTDATE  		DATE,
   FISCAL_MONTH_ENDDATE  		DATE,
   FISCAL_MONTH_TIMESPAN  		NUMBER,
   FISCAL_MONTH_OF_QUARTER  		NUMBER,
   FISCAL_MONTH_OF_YEAR  		NUMBER,
   FISCAL_SDATE                         DATE,
   FISCAL_DOW			        NUMBER,
   FISCAL_TYPE			        NUMBER,
   FISCAL_QID			        NUMBER,
   FISCAL_YID 			        NUMBER
);
/

CREATE TYPE FISCAL_MONTH_TYPE_TABLE AS TABLE OF FISCAL_MONTH_TYPE ;
/

-------------------------------------------------
---
---  LEVEL 2.3
---  FISCAL QUARTER TYPE
---
-------------------------------------------------

CREATE TYPE FISCAL_QUARTER_TYPE AS
OBJECT 
(
   FISCAL_QUARTER_ID 			NUMBER,
   FISCAL_QUARTER_NUMBER 		NUMBER,
   FISCAL_QUARTER_DESCRIPTION 		VARCHAR2 (2000),
   FISCAL_QUARTER_STARTDATE  		DATE,
   FISCAL_QUARTER_ENDDATE  		DATE,
   FISCAL_QUARTER_TIMESPAN  		NUMBER,
   FISCAL_QUARTER_OF_YEAR  		NUMBER,
   FISCAL_SDATE                         DATE,
   FISCAL_DOW			        NUMBER,
   FISCAL_TYPE			        NUMBER,
   FISCAL_YID 			        NUMBER
);
/

CREATE TYPE FISCAL_QUARTER_TYPE_TABLE AS TABLE OF FISCAL_QUARTER_TYPE ;
/

-------------------------------------------------
---
---  LEVEL 2.4
---  FISCAL YEAR TYPE
---
-------------------------------------------------

CREATE TYPE FISCAL_YEAR_TYPE AS
OBJECT 
(
   FISCAL_YEAR_ID 			NUMBER,
   FISCAL_YEAR_NUMBER   		NUMBER,
   FISCAL_YEAR_DESCRIPTION 		VARCHAR2 (2000),
   FISCAL_YEAR_STARTDATE  		DATE,
   FISCAL_YEAR_ENDDATE  		DATE,
   FISCAL_YEAR_TIMESPAN 		NUMBER
);
/

CREATE TYPE FISCAL_YEAR_TYPE_TABLE AS TABLE OF FISCAL_YEAR_TYPE ;
/

-------------------------------------------------
---
---  LEVEL 1.1
---  CALENDAR WEEK TYPE
---
-------------------------------------------------

CREATE TYPE CAL_WEEK_TYPE AS
OBJECT 
(
   CAL_WEEK_ID 				NUMBER,
   CAL_WEEK_NUMBER 			NUMBER,
   CAL_WEEK_DESCRIPTION			VARCHAR2 (2000),
   CAL_WEEK_STARTDATE			DATE,
   CAL_WEEK_ENDDATE  			DATE,
   CAL_WEEK_TIMESPAN  			NUMBER
);
/

CREATE TYPE CAL_WEEK_TYPE_TABLE AS TABLE OF CAL_WEEK_TYPE ;
/

-------------------------------------------------
---
---  LEVEL 1.2
---  CALENDAR MONTH TYPE
---
-------------------------------------------------

CREATE TYPE CAL_MONTH_TYPE AS
OBJECT 
(
   CAL_MONTH_ID 			NUMBER,
   CAL_MONTH_NUMBER 			NUMBER,
   CAL_MONTH_DESCRIPTION 		VARCHAR2 (2000),
   CAL_MONTH_STARTDATE  		DATE,
   CAL_MONTH_ENDDATE  			DATE,
   CAL_MONTH_TIMESPAN  			NUMBER,
   CAL_MONTH_OF_QUARTER 		NUMBER,
   CAL_MONTH_OF_YEAR  			NUMBER,
   CAL_YID                              NUMBER,
   CAL_QID                              NUMBER
);
/

CREATE TYPE CAL_MONTH_TYPE_TABLE AS TABLE OF CAL_MONTH_TYPE ;
/

-------------------------------------------------
---
---  LEVEL 1.3
---  CALENDAR QUARTER TYPE
---
-------------------------------------------------

CREATE TYPE CAL_QUARTER_TYPE AS
OBJECT 
(
   CAL_QUARTER_ID 			NUMBER,
   CAL_QUARTER_NUMBER 			NUMBER,
   CAL_QUARTER_OF_YEAR  		NUMBER,
   CAL_QUARTER_DESCRIPTION 		VARCHAR2 (2000),
   CAL_QUARTER_STARTDATE		DATE,
   CAL_QUARTER_ENDDATE  		DATE,
   CAL_QUARTER_TIMESPAN  		NUMBER,
   CAL_YID                              NUMBER
);
/

CREATE TYPE CAL_QUARTER_TYPE_TABLE AS TABLE OF CAL_QUARTER_TYPE ;
/

-------------------------------------------------
---
---  LEVEL 1.4
---  CALENDAR YEAR TYPE
---
-------------------------------------------------

CREATE TYPE CAL_YEAR_TYPE AS
OBJECT 
(
   CAL_YEAR_ID 				NUMBER,
   CAL_YEAR_NUMBER 			NUMBER,

CAL_YEAR_DESCRIPTION 		VARCHAR2 (2000),
   CAL_YEAR_STARTDATE  			DATE,
   CAL_YEAR_ENDDATE  			DATE,
   CAL_YEAR_TIMESPAN 			NUMBER   
);
/

CREATE TYPE CAL_YEAR_TYPE_TABLE AS TABLE OF CAL_YEAR_TYPE ;
/

-- ========================================================
-- END CREATING (TYPES AND TABLE TYPES)
-- ========================================================

-- ========================================================
-- BEGIN CREATING PACKAGE SPECIFICATION OWB_TIMESERIES
-- ========================================================

CREATE OR REPLACE PACKAGE OWB_TIMESERIES WRAPPED IS

-- function getWeekStartDate (thedate IN DATE) return date;

FUNCTION DAY_GENERATOR_TFN  (  
   START_DATE                IN VARCHAR2,
   YEARS                     IN NUMBER,
   FISCAL_TYPE               IN NUMBER, 
   FY_START_DATE             IN VARCHAR2,
   START_DAY_OF_FISCAL_WEEK  IN NUMBER
 ) RETURN DAY_TYPE_TABLE PIPELINED;

FUNCTION CAL_YEAR_GENERATOR_TFN ( 
   INPUT_ROWS IN SYS_REFCURSOR
 ) RETURN CAL_YEAR_TYPE_TABLE PIPELINED;

FUNCTION CAL_QUARTER_GENERATOR_TFN ( 
   INPUT_ROWS IN SYS_REFCURSOR
 ) RETURN CAL_QUARTER_TYPE_TABLE PIPELINED;

FUNCTION CAL_MONTH_GENERATOR_TFN ( 
   INPUT_ROWS IN SYS_REFCURSOR
 ) RETURN CAL_MONTH_TYPE_TABLE PIPELINED;

FUNCTION CAL_WEEK_GENERATOR_TFN ( 
   INPUT_ROWS IN SYS_REFCURSOR
 ) RETURN CAL_WEEK_TYPE_TABLE PIPELINED;

--
 -- FISCAL TABLE FUNCTIONS
 -- 
 FUNCTION FISCAL_YEAR_GENERATOR_TFN ( 
   INPUT_ROWS IN SYS_REFCURSOR
 ) RETURN FISCAL_YEAR_TYPE_TABLE PIPELINED;

FUNCTION FISCAL_WEEK_GENERATOR_TFN ( 
   INPUT_ROWS IN SYS_REFCURSOR
 ) RETURN FISCAL_WEEK_TYPE_TABLE PIPELINED;

FUNCTION FISCAL_MONTH_GENERATOR_TFN ( 
   INPUT_ROWS IN SYS_REFCURSOR
 ) RETURN FISCAL_MONTH_TYPE_TABLE PIPELINED;

FUNCTION FISCAL_QUARTER_GENERATOR_TFN ( 
   INPUT_ROWS IN SYS_REFCURSOR
 ) RETURN FISCAL_QUARTER_TYPE_TABLE PIPELINED;

END OWB_TIMESERIES;
/

-- ========================================================
-- END CREATING PACKAGE SPECIFICATION OWB_TIMESERIES
-- ========================================================

-- ========================================================
-- BEGIN CREATING PACKAGE BODY OWB_TIMESERIES
-- ========================================================

CREATE OR REPLACE PACKAGE BODY OWB_TIMESERIES IS

-- Calender helpers
 function getWeekStartDate(thedate IN DATE) return date;
 function getWeekEndDate(thedate IN DATE) return date;
 function getMonthStartDate(thedate IN DATE) return date;
 function getMonthEndDate(thedate IN DATE) return date;
 function getQuarterStartDate(thedate IN DATE) return date;
 function getQuarterEndDate(thedate IN DATE) return date;
 function getYearStartDate(thedate IN DATE) return date;
 function getYearEndDate(thedate IN DATE) return date;
 function getMonthQuarter(thedate IN DATE) return NUMBER;
 
 -- fiscal helpers
 function getFiscalYearStartDate (theDate IN DATE ) return date;

function getFiscalYearEndDate ( thedate IN DATE ) return date;

function getDayOfFiscalYear (theCurrentDate IN DATE, 
   theFiscalStartDate IN DATE ) return NUMBER;

function getFiscalQuarterStartDate ( thedate IN DATE, ftype NUMBER, 
   qidcount NUMBER, startDayWeek NUMBER) return date;

function getFiscalQuarterEndDate (theDate IN DATE, ftype IN NUMBER, 
   qidcount IN NUMBER, startDayWeek IN NUMBER ) return date ;

function getFiscalQuarterId ( theDate IN DATE, theFiscalStartDate IN DATE, 
   ftype IN NUMBER, startDayWeek IN NUMBER 
 ) return number ;

function getDayOFFiscalQuarter (
  theDate IN DATE, theFiscalStartDate IN DATE,  
  ftype IN NUMBER, startDayWeek IN NUMBER
 ) return NUMBER ;

function getFiscalMonthStartDate (
  thedate IN DATE, ftype NUMBER,
  midcount NUMBER, startDayWeek NUMBER
 ) return date ;

function getFiscalMonthEndDate (
  theDate IN DATE, ftype IN NUMBER,
  midcount IN NUMBER, startDayWeek IN NUMBER
 ) return date ;

function getFiscalMonthId (
  theDate  IN DATE, theFiscalStartDate IN DATE, 
  ftype IN NUMBER, startDayWeek IN NUMBER
 ) return number ;

function getDayOFFiscalMonth (
  theDate IN DATE, theFiscalStartDate IN DATE,  
  ftype IN NUMBER, startDayWeek IN NUMBER
 ) return NUMBER ;

function getFiscalWeekStartDate (
  thedate IN DATE, widcount NUMBER, startDayWeek NUMBER
 ) return date ;

function getFiscalWeekEndDate (
  thedate IN DATE,widcount NUMBER, startDayWeek NUMBER
 ) return date ;

function getFiscalWeekId (
  theDate IN DATE, theFiscalStartDate IN DATE, startDayWeek IN NUMBER
 ) return number ;

function getDayOFFiscalWeek (
  theDate IN DATE, theFiscalStartDate IN DATE, startDayWeek IN NUMBER
 ) return NUMBER ;

function getFiscalWeekendGivenDate (
  theDate IN DATE, theFiscalStartDate IN DATE, startDayWeek IN NUMBER
 ) return DATE ;

function getFiscalWeekStartGivenDate (
  theDate IN DATE, theFiscalStartDate IN DATE, startDayWeek IN NUMBER
 ) return DATE;

function getWeekOfFiscalQtrGivenDate (
  theDate IN DATE, theFiscalStartDate IN DATE,  
  ftype IN NUMBER, startDayWeek IN NUMBER
 ) return NUMBER;

function getFiscalMonthEndGivenDate (
  theDate IN DATE, theFiscalStartDate IN DATE, ftype IN NUMBER, startDayWeek IN NUMBER
 ) return DATE ;

function getFiscalMonthStartGivenDate (
  theDate IN DATE, theFiscalStartDate IN DATE, ftype IN NUMBER, startDayWeek IN NUMBER
 ) return DATE;

function getMonthOfFiscalQtrGivenDate (
  theDate IN DATE, theFiscalStartDate IN DATE, ftype IN NUMBER, startDayWeek IN NUMBER
 ) return NUMBER;

-- ///////////////////////////////////////////////////////////////////
 -- CAL WEEK 
 -- ///////////////////////////////////////////////////////////////////

function getWeekStartDate ( thedate IN DATE ) return date is
  startDate DATE;
  tempDate DATE;
  thisWeekDay NUMBER := 0;
  otherWeekDay NUMBER := 0;
  otherYear NUMBER := 0;
  thisYear NUMBER := 0;
  dayNum NUMBER := 0;
 begin
  -- -- dbms_output.put_line('== thedate = ' || theDate );
  thisWeekDay := to_char(theDate, 'IW');
  thisYear := to_char(theDate, 'YYYY');

-- -- dbms_output.put_line('== thisWeekDay @ = ' || thisWeekDay );
  loop         
    dayNum := dayNum + 1;
    tempDate := theDate - dayNum;
    otherWeekDay := to_char (tempDate, 'IW'); 
    otherYear := to_char(tempDate, 'YYYY');
    -- -- dbms_output.put_line('== otherWeekDay @ = ' || otherWeekDay || ' tempDate = ' || tempDate);
    -- -- dbms_output.put_line('  == otherYear @ = ' || otherYear || ' thisYear = ' || thisYear);

exit when (thisWeekDay != otherWeekDay) OR (thisYear != otherYear) ;
  end loop;
  tempDate := to_date(theDate - dayNum);
  -- -- dbms_output.put_line('== startdate = ' || startDate || '  tempDate = ' || tempDate);
  startDate := to_date(tempDate + 1);
  -- -- dbms_output.put_line('== startdate = ' || startDate );
  return startDate;
 end getWeekStartDate;

function getWeekEndDate (thedate IN DATE) return date is
  endDate DATE;
  tempDate DATE;
  otherYear NUMBER := 0;
  thisYear NUMBER := 0;
  thisWeekDay NUMBER := 0;
  otherWeekDay NUMBER := 0;
  dayNum NUMBER := 0;
 begin
  ---- dbms_output.put_line('== thedate = ' || theDate );
  thisWeekDay := to_char(theDate, 'IW');
  thisYear := to_char(theDate, 'YYYY');

---- dbms_output.put_line('== thisWeekDay @ = ' || thisWeekDay );
  loop         
    dayNum := dayNum + 1;
    tempDate := theDate + dayNum;
    otherWeekDay := to_char (tempDate, 'IW');
    otherYear := to_char(tempDate, 'YYYY');

---- dbms_output.put_line('== otherWeekDay @ = ' || otherWeekDay || ' tempDate = ' || tempDate);
    exit when (thisWeekDay != otherWeekDay) OR (thisYear != otherYear) ;
  end loop;
  tempDate := to_date(theDate + dayNum);
  ---- dbms_output.put_line('== enddate = ' || endDate || '  tempDate = ' || tempDate);
  endDate := to_date(tempDate - 1);
  ---- dbms_output.put_line('== enddate = ' || endDate );
  return endDate;
 end getWeekEndDate;

-- ////////////////////////////////////////////////////////////////////
 -- CAL MONTH
 -- ////////////////////////////////////////////////////////////////////

function getMonthStartDate ( thedate IN DATE ) return date is
  startDate DATE;
 begin
  startDate := trunc(thedate, 'MM');
  return startDate;
 end getMonthStartDate;

function getMonthEndDate (thedate IN DATE) return date is
  endDate DATE;
 begin
  endDate := last_day(theDate);
  return endDate;
 end getMonthEndDate;

-- ////////////////////////////////////////////////////////////////////
 -- CAL QUARTER
 -- ////////////////////////////////////////////////////////////////////

function getQuarterStartDate ( thedate IN DATE ) return date is
  startDate DATE;
  checkDate DATE;
  thisQuarterNo NUMBER := 0;
  checkQuarterNo NUMBER := 0;
 begin
  checkDate := theDate;
  thisQuarterNo := to_char(checkDate, 'Q');
  loop
    checkDate := trunc(checkDate, 'MM');
    -- -- dbms_output.put_line('checkDate = ' || checkDate);
    checkDate := checkDate - 1; -- check for the previous day
    checkQuarterNo := to_char(checkDate, 'Q');
    exit when (checkQuarterNo != thisQuarterNo);
  end loop;
  -- -- dbms_output.put_line('checkDate = ' || checkDate || 
  --    ' checkDateQ =' || to_char(checkDate, 'Q'));
  startDate := checkDate + 1; -- next logical day
  -- -- dbms_output.put_line('startDate = ' || startDate || 
  --  ' startDateQ =' || to_char(startDate, 'Q'));
  -- -- dbms_output.put_line('startDate = ' || startDate);
  return startDate;
 end getQuarterStartDate;

function getQuarterEndDate ( thedate IN DATE) return date is
  endDate DATE;
  checkDate DATE;
  thisQuarterNo NUMBER := 0;
  checkQuarterNo NUMBER := 0;
 begin
  checkDate := theDate;
  thisQuarterNo := to_char(checkDate, 'Q');
  loop
    checkDate := last_day(checkDate);
    -- -- dbms_output.put_line('checkDate = ' || checkDate);
    checkDate := checkDate + 1; -- check for the next day
    checkQuarterNo := to_char(checkDate, 'Q');
    exit when (checkQuarterNo != thisQuarterNo);
  end loop;
  -- -- dbms_output.put_line('checkDate = ' || checkDate || 
  --    ' checkDateQ =' || to_char(checkDate, 'Q'));
  endDate := checkDate - 1; -- previous logical day
  -- -- dbms_output.put_line('endDate = ' || endDate || 
  --  ' endDateQ =' || to_char(endDate, 'Q'));
  -- -- dbms_output.put_line('endDate = ' || endDate);
  return endDate;
 end getQuarterEndDate;

-- ////////////////////////////////////////////////////////////////////////
 -- CAL YEAR
 -- ////////////////////////////////////////////////////////////////////////

function getYearStartDate ( thedate IN DATE ) return date is
  startDate DATE;
 begin
  startDate := trunc(thedate, 'YYYY');
  return startDate;
 end getYearStartDate;

function getYearEndDate ( thedate IN DATE ) return date is
  endDate DATE;
  thisDate DATE;
  quarterEndDate DATE;
  thisQuarter NUMBER := 0;
  nextQuarter NUMBER := 0;
  x NUMBER := 0;
 begin
  thisDate := theDate;
  loop
    quarterEndDate := getQuarterEndDate(thisDate);
    -- -- dbms_output.put_line('qendDate = ' || quarterEndDate);
    thisQuarter := to_char(quarterEndDate, 'Q');
    nextQuarter := to_char(to_date(quarterEndDate + 1), 'Q');
    -- -- dbms_output.put_line('thisQuarter = ' || thisQuarter);
    -- -- dbms_output.put_line('nextQuarter = ' || nextQuarter);
    x := x+1;
    -- -- dbms_output.put_line('x = ' || x);
    thisDate := quarterEndDate + 1;
    if (thisQuarter = 4) then
      exit;
    end if;
  end loop ; 
  endDate := thisDate - 1;
  -- -- dbms_output.put_line('endDate = ' || endDate);
  return endDate;
 end getYearEndDate;

--///////////////////////////////////////////////////////////////////////
 -- HELPERS: MONTH_QUARTER
 --///////////////////////////////////////////////////////////////////////

function getMonthQuarter (thedate IN DATE ) return NUMBER is
  qnum NUMBER := 4;
  thisDate DATE;
  quarterEndDate DATE;
  quarterStartDate DATE;
  monthStartDate DATE;
  monthEndDate DATE;
 begin
  thisDate := theDate;

quarterStartDate := getQuarterStartDate(thisDate);
  -- -- dbms_output.put_line('qStartDate = ' || quarterStartDate);
  quarterEndDate := getQuarterEndDate(thisDate);
  -- -- dbms_output.put_line('qEndDate = ' || quarterEndDate);

monthStartDate := getMonthStartDate(thisDate);
  monthEndDate := getMonthEndDate(thisDate);

if (monthStartDate = quarterStartDate) then
    qnum := 1;
  elsif (monthEndDate = quarterEndDate) then
    qnum := 3;
  else 
    qnum := 2;
  end if;
  return qnum;
 end getMonthQuarter;

-- ////////////////////////////////////////////////////////////////////////
 -- FISCAL YEAR
 -- ////////////////////////////////////////////////////////////////////////

function getFiscalYearStartDate (
  theDate IN DATE
 ) return date 
 is
 begin
  return theDate; 
 end getFiscalYearStartDate;

function getFiscalYearEndDate (
  thedate IN DATE
 ) return date 
 is
  ndays NUMBER;
  endDate DATE;
 begin
  endDate := add_months(theDate, 12);
  endDate := endDate - 1;
  -- ndays := endDate - theDate  ;
  -- -- dbms_output.put_line ('endDate  = ' || endDate || '   ndays =' || ndays );
  return endDate; 
 end getFiscalYearEndDate;

function getDayOfFiscalYear (
  theCurrentDate     IN DATE,
  theFiscalStartDate IN DATE
 ) return NUMBER 
 is
  dfs Date;
  doy NUMBER;
 begin
  dfs := getFiscalYearStartDate ( theFiscalStartDate );
  if theCurrentDate >= dfs then
    doy := (theCurrentDate - dfs) + 1;   
  else
    doy := -1;
  end if;
  return doy; 
 end getDayOfFiscalYear;

-- ////////////////////////////////////////////////////////////////////////
 -- FISCAL QUARTER
 -- ////////////////////////////////////////////////////////////////////////

function getFiscalQuarterStartDate (
  thedate IN DATE, 
  ftype NUMBER,
  qidcount NUMBER,
  startDayWeek NUMBER
 ) return date 
 is
  startDate DATE;
  endDate DATE;
  thisQuarterNo NUMBER := 0;
  checkQuarterNo NUMBER := 0;
  dayOfTheDate NUMBER := 0;
  istWeekSpan NUMBER := 0;
  totalDays NUMBER := 0;
 begin
  ---- dbms_output.put_line ('thedate  = ' || thedate );
  dayOfTheDate := to_char (theDate, 'D'); 
  ---- dbms_output.put_line ('dayOfTheDate  = ' || dayOfTheDate );

istWeekSpan := startDayWeek - dayOfTheDate; 
  ---- dbms_output.put_line ('before adjustment .. istWeekSpan  = '||istWeekSpan );

if (istWeekSpan < 4) then
    istWeekSpan := 7 + istWeekSpan;
  end if;

---- dbms_output.put_line ('after adjustment .. istWeekSpan  = ' || istWeekSpan );
  
  
  if (ftype = 544) then

if (qidcount = 1) then
         startDate := thedate;
      end if;
      if (qidcount = 2) then
           totalDays := istWeekSpan + 3 * (4 * 7);
           ---- dbms_output.put_line ('totalDays  = ' || totalDays );
           startDate := theDate + totalDays ;
      end if;
      if (qidcount = 3) then
           totalDays := istWeekSpan + 3 * (4 * 7);
           ---- dbms_output.put_line ('totalDays1  = ' || totalDays );
           totalDays := totalDays + 5*7 + 2*(4 * 7);
           ---- dbms_output.put_line ('totalDays2  = ' || totalDays );
           startDate := theDate + totalDays ;
      end if; 
      if (qidcount = 4) then
           totalDays := istWeekSpan + 3 * (4 * 7);
           -- -- dbms_output.put_line ('totalDays 1  = ' || totalDays );
           totalDays := totalDays + 5 * 7 + 2*(4 * 7);
           -- -- dbms_output.put_line ('totalDays 2  = ' || totalDays );
           totalDays := totalDays + 5 * 7 + 2*(4 * 7);
           -- -- dbms_output.put_line ('totalDays 3  = ' || totalDays );
           startDate := theDate + totalDays ;
      end if; 
      if (qidcount = 5) then
         startDate := getFiscalYearEndDate(theDate) + 1;
         -- -- dbms_output.put_line ('startDate  = ' || startDate );
      end if; 
  elsif (ftype = 445) then
      if (qidcount = 1) then
         startDate := thedate;
      end if;
      if (qidcount = 2) then
           totalDays := istWeekSpan + (3 * 7) +  (4 * 7) +  (5 * 7);
           ---- dbms_output.put_line ('totalDays  = ' || totalDays );
           startDate := theDate + totalDays + 1;
      end if;
      if (qidcount = 3) then
           totalDays := istWeekSpan + (3 * 7) +  (4 * 7) +  (5 * 7);
           ---- dbms_output.put_line ('totalDays1  = ' || totalDays );
           totalDays := totalDays + 5*7 + 2*(4 * 7);
           ---- dbms_output.put_line ('totalDays2  = ' || totalDays );
           startDate := theDate + totalDays + 1;
      end if; 
      if (qidcount = 4) then
           totalDays := istWeekSpan + (3 * 7) +  (4 * 7) +  (5 * 7);
           -- -- dbms_output.put_line ('totalDays 1  = ' || totalDays );
           totalDays := totalDays + 5 * 7 + 2*(4 * 7);
           -- -- dbms_output.put_line ('totalDays 2  = ' || totalDays );
           totalDays := totalDays + 5 * 7 + 2*(4 * 7);
           -- -- dbms_output.put_line ('totalDays 3  = ' || totalDays );
           startDate := theDate + totalDays + 1;
      end if; 
      if (qidcount = 5) then
         -- -- dbms_output.put_line ('startDate 1 = ' || theDate );
         -- this correction is to compensate for the date loss.
         startDate := getFiscalYearEndDate(theDate) + 1;
         -- -- dbms_output.put_line ('startDate 2 = ' || startDate );
      end if; 
  else
    null;   
  end if;
  return startDate;
 end getFiscalQuarterStartDate;

function getFiscalQuarterEndDate (
  theDate IN DATE, 
  ftype IN NUMBER,
  qidcount IN NUMBER,
  startDayWeek IN NUMBER
 ) return date 
 is
  endDate DATE;
  dayOfTheDate NUMBER := 0;
  istWeekSpan NUMBER := 0;
  totalDays NUMBER := 0;
 begin
  endDate := getFiscalQuarterStartDate(theDate, ftype, qidcount + 1, startDayWeek) - 1;
  return endDate; 
 end getFiscalQuarterEndDate;

function getFiscalQuarterId (
  theDate IN DATE,
  theFiscalStartDate IN DATE, 
  ftype IN NUMBER,
  startDayWeek IN NUMBER
 ) return number
 is
  qid NUMBER;
  startdate DATE;
  enddate   DATE;
  yenddate  DATE;
  datediff  NUMBER := -1;
 begin
  qid := 1;
  loop  
    startdate := getFiscalQuarterStartDate ( theFiscalStartDate, ftype, qid, startDayWeek );
    -- dbms_output.put_line ('startDate = ' || startDate );
    enddate := getFiscalQuarterEndDate ( theFiscalStartDate, ftype, qid, startDayWeek );
    -- dbms_output.put_line ('endDate = ' || endDate );
    yenddate := getFiscalYearEndDate ( theFiscalStartDate ) + 1;
    --dbms_output.put_line ('yendDate = ' || yendDate );
    datediff := theDate - yenddate;
    --dbms_output.put_line ('datediff = ' || datediff );
    if ( datediff >= 0) then
      qid := 0;
      exit;
    else
      if ((theDate >= startDate) AND (theDate <= enddate)) then
        exit;
      else
        qid := qid + 1;
      end if;
    end if;   
  end loop;
  -- dbms_output.put_line ('@> qid = ' || qid );
  return qid; 
 end getFiscalQuarterId;

function getDayOFFiscalQuarter (
  theDate            IN DATE,
  theFiscalStartDate IN DATE,  
  ftype              IN NUMBER,
  startDayWeek       IN NUMBER
 ) return NUMBER 
 is
  qid     NUMBER;
  totalDays NUMBER := 0;
 begin
  qid       := getFiscalQuarterId(theDate, theFiscalStartDate, ftype, startDayWeek);
  -- dbms_output.put_line ('qid = ' || qid );
  totalDays := (theDate - getFiscalQuarterStartDate(theFiscalStartDate, ftype, qid, startDayWeek)) + 1;
  -- dbms_output.put_line ('totalDays = ' || totalDays );
  return totalDays; 
 end getDayOFFiscalQuarter;

function getFiscalQuarterEndGivenDate (
  theDate            IN DATE,
  theFiscalStartDate IN DATE,
  ftype              IN NUMBER,
  startDayWeek       IN NUMBER
 ) return DATE 
 is
  qid     NUMBER;
  endDate DATE;
 begin
  qid := getFiscalQuarterId(theDate, theFiscalStartDate, ftype, startDayWeek);
  dbms_output.put_line ('qid = ' || qid );
  endDate := getFiscalQuarterEndDate(theFiscalStartDate, ftype, qid, startDayWeek);
  return endDate;
 end getFiscalQuarterEndGivenDate;

function getFiscalQuarterStartGivenDate (
  theDate            IN DATE,
  theFiscalStartDate IN DATE,  
  ftype              IN NUMBER,
  startDayWeek       IN NUMBER
 ) return DATE 
 is
  qid NUMBER;
  endDate DATE;
 begin
  qid := getFiscalQuarterId (theDate, theFiscalStartDate, ftype, startDayWeek);
  dbms_output.put_line ('qid = ' || qid );
  endDate := getFiscalQuarterStartDate(theFiscalStartDate, ftype, qid, startDayWeek);
  return endDate;
 end getFiscalQuarterStartGivenDate;

-- ////////////////////////////////////////////////////////////////////////
 -- FISCAL MONTH
 -- ////////////////////////////////////////////////////////////////////////

function getFiscalMonthStartDate (
  thedate IN DATE,  -- Note: this is fiscal start date not any date.
  ftype NUMBER,
  midcount NUMBER,
  startDayWeek NUMBER
 ) return date 
 is
  startDate DATE;
  endDate DATE;
  thisMonthNo NUMBER := 0;
  checkMonthNo NUMBER := 0;
  dayOfTheDate NUMBER := 0;
  istWeekSpan NUMBER := 0;
  totalDays NUMBER := 0;

begin
  ---- dbms_output.put_line ('thedate  = ' || thedate );
  --
  -- computing istWeekSpan which is needed in ist month and 2nd month.
  --
  dayOfTheDate := to_char (theDate, 'D'); 
  ---- dbms_output.put_line ('dayOfTheDate  = ' || dayOfTheDate );

istWeekSpan := startDayWeek - dayOfTheDate; 
  ---- dbms_output.put_line ('before adjustment .. istWeekSpan  = '||istWeekSpan );

if (istWeekSpan < 4) then
       istWeekSpan := 7 + istWeekSpan;
  end if;
  ---- dbms_output.put_line ('after adjustment .. istWeekSpan  = ' || istWeekSpan );

if (ftype = 544) then

if (midcount = 1) then
         totalDays := 0;
      end if;

if ((midcount = 2) OR (midcount = 3) OR (midcount = 4) ) then
           totalDays := istWeekSpan +  ((midcount - 1) * 4 * 7) ;
      end if;

if ((midcount = 5) OR (midcount = 6) OR (midcount = 7)) then
           totalDays := istWeekSpan +  ((midcount - 1)* 4 * 7) + 1*7;
      end if;
                
      if ((midcount = 8) OR (midcount = 9) OR (midcount = 10) ) then
           totalDays := istWeekSpan +  ((midcount - 1) * 4 * 7) + 2*7;
      end if;

if ((midcount = 11) OR (midcount = 12)) then
           totalDays := istWeekSpan +  ((midcount - 1 ) * 4 * 7) + 3*7;
      end if;

---- dbms_output.put_line ('>totalDays  = ' || totalDays );
      startDate := theDate + totalDays;
      ---- dbms_output.put_line ('>sd  = ' || startDate );

else
    if (ftype = 445) then
      if (midcount = 1) then
         totalDays := 0;
      end if;

if ((midcount = 2) OR (midcount = 3)) then
         if (midcount = 2) then
           totalDays := istWeekSpan +  3 * 7  ;
         end if;
         if (midcount = 3) then
           totalDays := istWeekSpan +  3 * 7 +  4 * 7 ;
         end if; 
      end if;

if ((midcount = 4) OR (midcount = 5) OR (midcount = 6)) then
           totalDays := istWeekSpan +  3 * 7 + ((midcount - 3)* 4 * 7) + 5*7;
      end if;
                
      if ((midcount = 7) OR (midcount = 8) OR (midcount = 9) ) then
           totalDays := istWeekSpan +  3 * 7 + ((midcount - 4) * 4 * 7) + 2*5*7;
      end if;

if ((midcount = 10) OR (midcount = 11) OR (midcount = 12)) then
           totalDays := istWeekSpan +  3 * 7 + ((midcount - 5 ) * 4 * 7) + 3*5*7;
      end if;

---- dbms_output.put_line ('>totalDays  = ' || totalDays );
      startDate := theDate + totalDays;
      ---- dbms_output.put_line ('>sd  = ' || startDate );
    end if; 
  end if;
  return startDate;
 end getFiscalMonthStartDate;

function getFiscalMonthEndDate
 (
  theDate IN DATE, -- Note: this is fiscal start date not any date.
  ftype IN NUMBER,
  midcount IN NUMBER,
  startDayWeek IN NUMBER
 ) return date 
 is
  endDate DATE;
  lastDate DATE;
  dayOfTheDate NUMBER := 0;
  istWeekSpan NUMBER := 0;
  totalDays NUMBER := 0;
 begin
  --
  -- computing istWeekSpan which is needed in 1,2,3 month.
  --
  dayOfTheDate := to_char (theDate, 'D'); 
  ---- dbms_output.put_line ('dayOfTheDate  = ' || dayOfTheDate );

istWeekSpan := startDayWeek - dayOfTheDate; 
  ---- dbms_output.put_line ('before adjustment .. istWeekSpan  = '||istWeekSpan );

if (istWeekSpan < 4) then
    istWeekSpan := 7 + istWeekSpan;
  end if;
  -- -- dbms_output.put_line ('after adjustment .. istWeekSpan  = '||istWeekSpan );

lastDate := theDate;

if (ftype = 544) then

if ((midcount = 1) OR (midcount = 2) OR (midcount = 3) ) then
           totalDays := istWeekSpan +  (midcount) * 4 * 7 ;
      end if;

if ((midcount = 4) OR (midcount = 5) OR (midcount = 6)) then
           totalDays := istWeekSpan +  (midcount * 4 * 7) + 1*7;
      end if;
                
      if ((midcount = 7) OR (midcount = 8) OR (midcount = 9) ) then
           totalDays := istWeekSpan +  (midcount * 4 * 7) + 2*7;
      end if;

if ((midcount = 10) OR (midcount = 11) OR (midcount = 12)) then
           totalDays := istWeekSpan +  (midcount * 4 * 7) + 3*7;
      end if;

if (midcount = 12) then
           totalDays := 0;
           lastDate := getFiscalYearEndDate(theDate) + 1;
      end if;

-- -- dbms_output.put_line ('>totalDays  = ' || totalDays );
      endDate := lastDate + totalDays;
      -- -- dbms_output.put_line ('>ed  = ' || endDate );

else
      if ((midcount = 1) OR (midcount = 2)) then
         if (midcount = 1) then
           totalDays := istWeekSpan +  3 * 7  ;
         end if;
         if (midcount = 2) then
           totalDays := istWeekSpan +  3 * 7 +  4 * 7 ;
         end if; 
      end if;

if ((midcount = 3) OR (midcount = 4) OR (midcount = 5)) then
           totalDays := istWeekSpan +  3 * 7 + ((midcount - 2)* 4 * 7) + 5*7;
      end if;
                
      if ((midcount = 6) OR (midcount = 7) OR (midcount = 8) ) then
           totalDays := istWeekSpan +  3 * 7 + ((midcount - 3) * 4 * 7) + 2*5*7;
      end if;

if ((midcount = 9) OR (midcount = 10) OR (midcount = 11)) then
           totalDays := istWeekSpan +  3 * 7 + ((midcount - 4 ) * 4 * 7) + 3*5*7;
      end if;

if (midcount = 12) then
           totalDays := 0;
           lastDate := getFiscalYearEndDate(theDate) + 1;
      end if;

---- dbms_output.put_line ('>totalDays  = ' || totalDays );
      endDate := lastDate + totalDays;
      ---- dbms_output.put_line ('ed  = ' || endDate );

end if;
  return endDate - 1; 
 end getFiscalMonthEndDate;

function getFiscalMonthId (
  theDate            IN DATE,
  theFiscalStartDate IN DATE, 
  ftype              IN NUMBER,
  startDayWeek       IN NUMBER
 ) return number
 is
  mid       NUMBER := 0;
  datediff  NUMBER := 0;
  startdate DATE;
  enddate   DATE;
  yenddate  DATE;
 begin
  mid := 1;
  loop  
    startdate := getFiscalMonthStartDate ( theFiscalStartDate, ftype, mid, startDayWeek );
    --dbms_output.put_line ('startDate = ' || startDate );
    enddate := getFiscalMonthEndDate ( theFiscalStartDate, ftype, mid, startDayWeek );
    --dbms_output.put_line ('endDate = ' || endDate );
    yenddate := getFiscalYearEndDate ( theFiscalStartDate ) + 1;
    --dbms_output.put_line ('yendDate = ' || yendDate );
    datediff := theDate - yenddate;
    --dbms_output.put_line ('datediff = ' || datediff );
    if ( datediff >= 0) then
      mid := 0;
      exit;
    else
      if ((theDate >= startDate) AND (theDate <= enddate)) then
        exit;
      else
        mid := mid + 1;
      end if;
    end if;    
  end loop;
  --dbms_output.put_line ('@@> mid = ' || mid );
  return mid; 
 end getFiscalMonthId;

function getDayOFFiscalMonth (
  theDate            IN DATE,
  theFiscalStartDate IN DATE,  
  ftype              IN NUMBER,
  startDayWeek       IN NUMBER
 ) return NUMBER 
 is
  mid     NUMBER;
  totalDays NUMBER := 0;
 begin
  mid       := getFiscalMonthId(theDate, theFiscalStartDate, ftype, startDayWeek);
  -- dbms_output.put_line ('mid = ' || mid );
  totalDays := (theDate - getFiscalMonthStartDate(theFiscalStartDate, ftype, mid, startDayWeek)) + 1;
  -- dbms_output.put_line ('totalDays = ' || totalDays );
  return totalDays;
 end getDayOFFiscalMonth;

function getMonthOfFiscalQtrGivenDate (
  theDate            IN DATE,
  theFiscalStartDate IN DATE,  
  ftype              IN NUMBER,
  startDayWeek       IN NUMBER
 ) return NUMBER 
 is
  qsd               DATE;
  qed               DATE;
  mide              NUMBER := 0;
  mids              NUMBER := 0;
  num_weeks         NUMBER := 0;
  qid               NUMBER := 0;
  mid_thedate       NUMBER := 0;
  mid_calc          NUMBER := -1;

begin
  qid := getFiscalQuarterId(theDate, theFiscalStartDate, ftype, startDayWeek);
  dbms_output.put_line ('qid = ' || qid );

if (qid = 0) then 
    return mid_calc;
  end if;

qsd := getFiscalQuarterStartDate(theFiscalStartDate, ftype, qid, startDayWeek);
  dbms_output.put_line ('qsd = ' || qsd );

qed := getFiscalQuarterEndDate(theFiscalStartDate, ftype, qid, startDayWeek);
  dbms_output.put_line ('qed = ' || qed );

mids  := getFiscalMonthId  (qsd, theFiscalStartDate, ftype, startDayWeek);
  mids := mod (mids, 12);
  dbms_output.put_line ('mids = ' || mids );
  mide  := getFiscalMonthId  (qed, theFiscalStartDate, ftype, startDayWeek);
  mide  := mod (mide, 12);
  dbms_output.put_line ('mide = ' || mide );

num_weeks := (mide - mids) + 1;
  dbms_output.put_line ('num_weeks = ' || num_weeks );

mid_thedate  := getFiscalMonthId (theDate, theFiscalStartDate, ftype, startDayWeek);
  dbms_output.put_line ('mid_thedate = ' || mid_thedate );

mid_calc := num_weeks - ( mide - mid_thedate );
  dbms_output.put_line ('mid_calc = ' || mid_calc );

return mid_calc;

end getMonthOfFiscalQtrGivenDate;

function getFiscalMonthEndGivenDate (
  theDate            IN DATE,
  theFiscalStartDate IN DATE,
  ftype              IN NUMBER,
  startDayWeek       IN NUMBER
 ) return DATE 
 is
  mid     NUMBER;
  endDate DATE;
 begin
  mid := getFiscalMonthId(theDate, theFiscalStartDate, ftype, startDayWeek);
  dbms_output.put_line ('mid = ' || mid );
  endDate := getFiscalMonthEndDate(theFiscalStartDate, ftype, mid, startDayWeek);
  return endDate;
 end getFiscalMonthEndGivenDate;

function getFiscalMonthStartGivenDate (
  theDate            IN DATE,
  theFiscalStartDate IN DATE,  
  ftype              IN NUMBER,
  startDayWeek       IN NUMBER
 ) return DATE 
 is
  mid NUMBER;
  endDate DATE;
 begin
  mid := getFiscalMonthId(theDate, theFiscalStartDate, ftype, startDayWeek);
  dbms_output.put_line ('mid = ' || mid );
  endDate := getFiscalMonthStartDate(theFiscalStartDate, ftype, mid, startDayWeek);
  return endDate;
 end getFiscalMonthStartGivenDate;

-- ////////////////////////////////////////////////////////////////////////
 -- FISCAL WEEK
 -- ////////////////////////////////////////////////////////////////////////

function getFiscalWeekStartDate (
  thedate IN DATE, -- the fiscal start date
  widcount NUMBER,
  startDayWeek NUMBER
 ) return date 
 is
  startDate DATE;
  lastDate DATE;
  dayOfTheDate NUMBER := 0;
  istWeekSpan NUMBER := 0;
  totalDays NUMBER := 0;
 begin
  ---- dbms_output.put_line ('thedate  = ' || thedate );
  --
  -- computing istWeekSpan which is needed in ist month and 2nd month.
  --
  dayOfTheDate := to_char (theDate, 'D'); 
  ---- dbms_output.put_line ('dayOfTheDate  = ' || dayOfTheDate );

istWeekSpan := startDayWeek - dayOfTheDate; 
  ---- dbms_output.put_line ('before adjustment .. istWeekSpan  = ' || istWeekSpan );

if (istWeekSpan < 4) then
       istWeekSpan := 7 + istWeekSpan;
  end if;
  ---- dbms_output.put_line ('after adjustment .. istWeekSpan  = ' || istWeekSpan );

lastDate := theDate;
      
  if (widcount = 1) then
    totalDays := 0;
  else
    totalDays := istWeekSpan + ((widcount - 2) * 7);
  end if;

---- dbms_output.put_line ('>totalDays  = ' || totalDays );
  startDate := theDate + totalDays;
  ---- dbms_output.put_line ('>sd  = ' || startDate );
  
  return startDate;
 end getFiscalWeekStartDate;

function getFiscalWeekEndDate (
  thedate IN DATE,
  widcount NUMBER,
  startDayWeek NUMBER
 ) return date 
 is
  endDate DATE;
  lastDate DATE;
  dayOfTheDate NUMBER := 0;
  istWeekSpan NUMBER := 0;
  totalDays NUMBER := 0;

istWeekSpan := startDayWeek - dayOfTheDate; 
  ---- dbms_output.put_line ('before adjustment .. istWeekSpan  = '||istWeekSpan );

if (istWeekSpan < 4) then
     istWeekSpan := 7 + istWeekSpan;
  end if;
  ---- dbms_output.put_line ('after adjustment .. istWeekSpan  = ' ||istWeekSpan );

lastDate := theDate;
      
  if (widcount = 52) then
    totalDays := 0;
    lastDate := getFiscalYearEndDate(theDate) + 1;
  else
    if (widcount = 1) then
      totalDays := istWeekSpan;
    else
      totalDays := istWeekSpan + ((widcount - 1) * 7);
    end if;
  end if;

---- dbms_output.put_line ('>totalDays  = ' || totalDays );
  endDate := lastDate + totalDays;
  ---- dbms_output.put_line ('>ed  = ' || endDate );
  
  return (endDate - 1);
 end getFiscalWeekEndDate;

function getFiscalWeekId (
  theDate            IN DATE,
  theFiscalStartDate IN DATE, 
  startDayWeek       IN NUMBER
 ) return number
 is
  wid       NUMBER;
  startdate DATE;
  enddate   DATE;
 begin
  wid := 1;
  loop  
    startdate := getFiscalWeekStartDate ( theFiscalStartDate, wid, startDayWeek );
    -- -- dbms_output.put_line ('startDate = ' || startDate );
    enddate := getFiscalWeekEndDate ( theFiscalStartDate, wid, startDayWeek );
    -- -- dbms_output.put_line ('endDate = ' || endDate );
    if (theDate >= startDate) AND (theDate <= enddate) then
     exit;
    end if;
    wid := wid + 1;    
  end loop;
  -- dbms_output.put_line ('@@> wid = ' || wid );
  return wid; 
 end getFiscalWeekId;

function getDayOFFiscalWeek (
  theDate            IN DATE,
  theFiscalStartDate IN DATE,  
  startDayWeek       IN NUMBER
 ) return NUMBER 
 is
  wid     NUMBER;
  totalDays NUMBER := 0;
 begin
  wid       := getFiscalWeekId(theDate, theFiscalStartDate, startDayWeek);
  -- dbms_output.put_line ('wid = ' || wid );
  totalDays := (theDate - getFiscalWeekStartDate(theFiscalStartDate, wid, startDayWeek)) + 1;
  -- dbms_output.put_line ('totalDays = ' || totalDays );
  return totalDays;
 end getDayOFFiscalWeek;

function getFiscalWeekEndGivenDate (
  theDate            IN DATE,
  theFiscalStartDate IN DATE,  
  startDayWeek       IN NUMBER
 ) return DATE 
 is
  wid     NUMBER;
  endDate DATE;
 begin
  wid       := getFiscalWeekId(theDate, theFiscalStartDate, startDayWeek);
  dbms_output.put_line ('wid = ' || wid );
  endDate := getFiscalWeekEndDate(theFiscalStartDate, wid, startDayWeek);
  return endDate;
 end getFiscalWeekendGivenDate;

function getFiscalWeekStartGivenDate (
  theDate            IN DATE,
  theFiscalStartDate IN DATE,  
  startDayWeek       IN NUMBER
 ) return DATE 
 is
  wid     NUMBER;
  endDate DATE;
 begin
  wid       := getFiscalWeekId(theDate, theFiscalStartDate, startDayWeek);
  dbms_output.put_line ('wid = ' || wid );
  endDate := getFiscalWeekStartDate(theFiscalStartDate, wid, startDayWeek);
  return endDate;
 end getFiscalWeekStartGivenDate;

function getWeekOfFiscalMonthGivenDate (
  theDate            IN DATE,
  theFiscalStartDate IN DATE,  
  ftype              IN NUMBER,
  startDayWeek       IN NUMBER
 ) return NUMBER 
 is
  msd               DATE;
  med               DATE;
  wide              NUMBER := 0;
  wids              NUMBER := 0;
  num_weeks         NUMBER := 0;
  mid               NUMBER := 0;
  wid_thedate       NUMBER := 0;
  wid_calc          NUMBER := 0;

begin
  mid       := getFiscalMonthId(theDate, theFiscalStartDate, ftype, startDayWeek);
  dbms_output.put_line ('mid = ' || mid );

if (mid = 0) then 
    return wid_calc;
  end if;

msd       := getFiscalMonthStartDate(theFiscalStartDate, ftype, mid, startDayWeek);
  dbms_output.put_line ('msd = ' || msd );

med       := getFiscalMonthEndDate(theFiscalStartDate, ftype, mid, startDayWeek);
  dbms_output.put_line ('med = ' || med );

wids  := getFiscalWeekId  (msd, theFiscalStartDate, startDayWeek);
  wids := mod (wids, 52);
  dbms_output.put_line ('wids = ' || wids );

wide  := getFiscalWeekId  (med, theFiscalStartDate, startDayWeek);
  wide  := mod (wide, 52);
 
  dbms_output.put_line ('wide = ' || wide );

num_weeks := (wide - wids) + 1;
  dbms_output.put_line ('num_weeks = ' || num_weeks );

wid_thedate  := getFiscalWeekId  (theDate, theFiscalStartDate, startDayWeek);
  dbms_output.put_line ('wid_thedate = ' || wid_thedate );

wid_calc := num_weeks - ( wide - wid_thedate );
  dbms_output.put_line ('wid_calc = ' || wid_calc );

return wid_calc;

end getWeekOFFiscalMonthGivenDate;

function getWeekOfFiscalQtrGivenDate (
  theDate            IN DATE,
  theFiscalStartDate IN DATE,  
  ftype              IN NUMBER,
  startDayWeek       IN NUMBER
 ) return NUMBER 
 is
  qsd               DATE;
  qed               DATE;
  wide              NUMBER := 0;
  wids              NUMBER := 0;
  num_weeks         NUMBER := 0;
  qid               NUMBER := 0;
  wid_thedate       NUMBER := 0;
  wid_calc          NUMBER := -1;

begin

qid := getFiscalQuarterId(theDate, theFiscalStartDate, ftype, startDayWeek);
  dbms_output.put_line ('qid = ' || qid );

if (qid = 0) then 
    return wid_calc;
  end if;

qsd       := getFiscalQuarterStartDate(theFiscalStartDate, ftype, qid, startDayWeek);
  dbms_output.put_line ('qsd = ' || qsd );

qed       := getFiscalQuarterEndDate(theFiscalStartDate, ftype, qid, startDayWeek);
  dbms_output.put_line ('qed = ' || qed );

wids  := getFiscalWeekId  (qsd, theFiscalStartDate, startDayWeek);
  wids := mod (wids, 52);
  dbms_output.put_line ('wids = ' || wids );

wide  := getFiscalWeekId  (qed, theFiscalStartDate, startDayWeek);
  wide  := mod (wide, 52);
 
  dbms_output.put_line ('wide = ' || wide );

num_weeks := (wide - wids) + 1;
  dbms_output.put_line ('num_weeks = ' || num_weeks );

wid_thedate  := getFiscalWeekId  (theDate, theFiscalStartDate, startDayWeek);
  dbms_output.put_line ('wid_thedate = ' || wid_thedate );

wid_calc := num_weeks - ( wide - wid_thedate );
  dbms_output.put_line ('wid_calc = ' || wid_calc );

return wid_calc;

end getWeekOfFiscalQtrGivenDate;

-----------------------------------------------------------
 ---
 ---   DAY TABLE FUNCTION
 ---
 -----------------------------------------------------------

FUNCTION DAY_GENERATOR_TFN 
 ( 
   START_DATE                IN VARCHAR2,
   YEARS                     IN NUMBER,
   FISCAL_TYPE               IN NUMBER, 
   FY_START_DATE             IN VARCHAR2,
   START_DAY_OF_FISCAL_WEEK  IN NUMBER
 ) 
   RETURN DAY_TYPE_TABLE
   PIPELINED
 IS
   DAY_TYPE_ROW   	      DAY_TYPE;

L_DAY_ID                   NUMBER;
   L_DAY                      DATE;
   L_DAY_JULIANDATE  	      NUMBER;	
   L_DAY_DESCRIPTION 	      VARCHAR (2000);
   L_DAY_STARTDATE   	      DATE;	
   L_DAY_ENDDATE     	      DATE;
   L_DAY_TIMESPAN    	      NUMBER := -1;
   L_DAY_OF_CAL_WEEK	      NUMBER := -1;
   L_DAY_OF_CAL_MONTH         NUMBER := -1;	
   L_DAY_OF_CAL_QUARTER	      NUMBER := -1;	
   L_DAY_OF_CAL_YEAR	      NUMBER := -1; 
   L_DAY_OF_FISCAL_WEEK	      NUMBER := -1;	
   L_DAY_OF_FISCAL_MONTH      NUMBER := -1;
   L_DAY_OF_FISCAL_QUARTER    NUMBER := -1;
   L_DAY_OF_FISCAL_YEAR	      NUMBER := -1;
   DAYS_IN_A_YEAR             NUMBER := -1;
   YEAR_DAYCOUNTER            NUMBER := -1;
   DAY_ID_COUNTER 	      NUMBER := -1;

-- IDs and Additional infos
   L_CAL_YID                  NUMBER :=  100;
   L_CAL_WID		      NUMBER :=  200;
   L_CAL_WID_FLAG             BOOLEAN := FALSE;
   L_CAL_MID		      NUMBER :=  300;
   L_CAL_MID_FLAG             BOOLEAN := FALSE;
   L_CAL_QID		      NUMBER :=  500;
   L_CAL_QID_FLAG             BOOLEAN := FALSE;
   L_FISCAL_WID		      NUMBER  := 2000;
   L_FISCAL_WID_FLAG          BOOLEAN := FALSE;
   L_FISCAL_MID	              NUMBER  := 3000;
   L_FISCAL_MID_FLAG          BOOLEAN := FALSE;
   L_FISCAL_QID		      NUMBER  := 5000;
   L_FISCAL_QID_FLAG          BOOLEAN := FALSE;
   L_FISCAL_YID               NUMBER  := 10000;
   L_FISCAL_SDATE             DATE;
   L_FISCAL_DOW		      NUMBER;
   L_FISCAL_TYPE	      NUMBER;

FIRST_DATE                 DATE;
   STARTDATE_OF_YEAR          DATE;
   ENDDATE_OF_YEAR            DATE;
   CURRENT_DATE               DATE;
   YEAR_COUNTER               NUMBER  := 0;
   START_DATE_OF_FISCAL_YEAR  DATE;
   FISCAL_FLAG_ON             BOOLEAN := FALSE;
   TOTAL_DAYS_FISCAL_YEAR     NUMBER;
   ROW_COUNTER                NUMBER := 0;
 BEGIN

if (FISCAL_TYPE = 544) OR (FISCAL_TYPE = 445) then
     FISCAL_FLAG_ON := TRUE;
   end if;
   
   FIRST_DATE := to_date ( START_DATE, 'DD-MON-YYYY' );   
   if FISCAL_FLAG_ON = TRUE then
      START_DATE_OF_FISCAL_YEAR := to_date ( FY_START_DATE, 'DD-MON-YYYY' );
      dbms_output.put_line ('START_DATE_OF_FISCAL_YEAR  = ' || START_DATE_OF_FISCAL_YEAR  );
   end if;

DAY_ID_COUNTER := 0;
   YEAR_COUNTER   := 0;
   
   STARTDATE_OF_YEAR := FIRST_DATE;
   FOR YEAR_COUNT in 1 .. YEARS  LOOP
     ROW_COUNTER := ROW_COUNTER + 1;
     STARTDATE_OF_YEAR := getYearStartDate (STARTDATE_OF_YEAR);
     -- dbms_output.put_line ('STARTDATE_OF_YEAR  = ' || STARTDATE_OF_YEAR  );
     ENDDATE_OF_YEAR := getYearEndDate (STARTDATE_OF_YEAR);  
     -- dbms_output.put_line ('ENDDATE_OF_YEAR = ' || ENDDATE_OF_YEAR );    
     DAYS_IN_A_YEAR := (ENDDATE_OF_YEAR - STARTDATE_OF_YEAR) + 1;      
     -- dbms_output.put_line ('DAYS_IN_A_YEAR = ' || DAYS_IN_A_YEAR );
     YEAR_DAYCOUNTER := 0;

FOR DAY_COUNT in 1 .. DAYS_IN_A_YEAR  LOOP
       CURRENT_DATE := 
         getYearStartDate (STARTDATE_OF_YEAR) + YEAR_DAYCOUNTER;
       YEAR_DAYCOUNTER := YEAR_DAYCOUNTER + 1;
       -- -- dbms_output.put_line (YEAR_DAYCOUNTER);
       DAY_ID_COUNTER  := DAY_ID_COUNTER + 1;
       L_DAY_ID        := DAY_ID_COUNTER;
       L_DAY           := TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY');

L_DAY_JULIANDATE   := TO_CHAR(CURRENT_DATE, 'J');		
       L_DAY_DESCRIPTION  := 'Day ' || YEAR_DAYCOUNTER || ' of the year '
                               || to_char (CURRENT_DATE, 'YYYY'); 
       -- -- dbms_output.put_line ('L_DAY_DESCRIPTION  ' ||L_DAY_DESCRIPTION );
       L_DAY_STARTDATE    := CURRENT_DATE;
       L_DAY_ENDDATE      := CURRENT_DATE;  		
       L_DAY_TIMESPAN     := 1;
       L_DAY_OF_CAL_WEEK := (CURRENT_DATE - getWeekStartDate(CURRENT_DATE)) + 1;

if L_DAY_OF_CAL_WEEK = 1 then
         if L_CAL_WID_FLAG = FALSE then
           L_CAL_WID_FLAG := TRUE;
         else
           L_CAL_WID := L_CAL_WID + 1;
         end if;
       end if;

L_DAY_OF_CAL_MONTH  := (CURRENT_DATE - getMonthStartDate(CURRENT_DATE)) + 1;

if L_DAY_OF_CAL_MONTH = 1 then
         if L_CAL_MID_FLAG = FALSE then
           L_CAL_MID_FLAG := TRUE;
         else
           L_CAL_MID := L_CAL_MID + 1;
         end if;
       end if;

L_DAY_OF_CAL_QUARTER := (CURRENT_DATE - getQuarterStartDate(CURRENT_DATE))+1;

if L_DAY_OF_CAL_QUARTER = 1 then
         
         if L_CAL_QID_FLAG = FALSE then
           L_CAL_QID_FLAG := TRUE;
         else
           L_CAL_QID := L_CAL_QID + 1;
         end if;

end if;

L_DAY_OF_CAL_YEAR :=(CURRENT_DATE - getYearStartDate(CURRENT_DATE))+1;

if FISCAL_FLAG_ON = TRUE then

L_DAY_OF_FISCAL_WEEK    := getDayOFFiscalWeek (
           CURRENT_DATE, START_DATE_OF_FISCAL_YEAR, 
           START_DAY_OF_FISCAL_WEEK);

if L_DAY_OF_FISCAL_WEEK = 1 then
          if L_FISCAL_WID_FLAG = FALSE then
            L_FISCAL_WID_FLAG := TRUE;
          else
            L_FISCAL_WID := L_FISCAL_WID + 1;
          end if;
         end if;

L_DAY_OF_FISCAL_MONTH   := getDayOFFiscalMonth (
           CURRENT_DATE, START_DATE_OF_FISCAL_YEAR, FISCAL_TYPE,
           START_DAY_OF_FISCAL_WEEK);
         dbms_output.put_line ('mfd=' || L_DAY_OF_FISCAL_MONTH);

if L_DAY_OF_FISCAL_MONTH = 1 then
          if L_FISCAL_MID_FLAG = FALSE then
            L_FISCAL_MID_FLAG := TRUE;
          else
            L_FISCAL_MID := L_FISCAL_MID + 1;
          end if;
         end if;

L_DAY_OF_FISCAL_QUARTER := getDayOFFiscalQuarter (
           CURRENT_DATE, START_DATE_OF_FISCAL_YEAR, FISCAL_TYPE,  
           START_DAY_OF_FISCAL_WEEK);
         dbms_output.put_line ('qfd=' || L_DAY_OF_FISCAL_QUARTER);

if L_DAY_OF_FISCAL_QUARTER = 1 then
          if L_FISCAL_QID_FLAG = FALSE then
            L_FISCAL_QID_FLAG := TRUE;
          else
            L_FISCAL_QID := L_FISCAL_QID + 1;
          end if;
         end if;

L_DAY_OF_FISCAL_YEAR := 
           getDayOFFiscalYear(CURRENT_DATE, START_DATE_OF_FISCAL_YEAR) ;
        
         TOTAL_DAYS_FISCAL_YEAR := getFiscalYearEndDate(START_DATE_OF_FISCAL_YEAR) -
           getFiscalYearStartDate(START_DATE_OF_FISCAL_YEAR) + 1;

dbms_output.put_line ('tfd=' || TOTAL_DAYS_FISCAL_YEAR);
         dbms_output.put_line ('tdy=' || L_DAY_OF_FISCAL_YEAR);

L_FISCAL_SDATE := START_DATE_OF_FISCAL_YEAR;
         L_FISCAL_DOW   := START_DAY_OF_FISCAL_WEEK;
         L_FISCAL_TYPE	:= FISCAL_TYPE;

if TOTAL_DAYS_FISCAL_YEAR = L_DAY_OF_FISCAL_YEAR  then
           START_DATE_OF_FISCAL_YEAR := 
            getFiscalYearEndDate(START_DATE_OF_FISCAL_YEAR) + 1;
           dbms_output.put_line ('@@>> new fiscal year ' || START_DATE_OF_FISCAL_YEAR);
         end if;

if ( ROW_COUNTER <> 1 AND
              L_DAY_OF_FISCAL_WEEK = 1 AND 	
              L_DAY_OF_FISCAL_MONTH = 1 AND 
              L_DAY_OF_FISCAL_QUARTER = 1 AND    
              L_DAY_OF_FISCAL_YEAR  = 1
            ) then
            L_FISCAL_YID := L_FISCAL_YID + 1;
         end if;
          	 
       end if;
        
       DAY_TYPE_ROW := 
        DAY_TYPE(
          L_DAY_ID,
          L_DAY,
          L_DAY_JULIANDATE, 		
          L_DAY_DESCRIPTION, 		
          L_DAY_STARTDATE,  		
          L_DAY_ENDDATE,  		
          L_DAY_TIMESPAN,  		
          L_DAY_OF_CAL_WEEK,		
          L_DAY_OF_CAL_MONTH,    		
          L_DAY_OF_CAL_QUARTER,		
          L_DAY_OF_CAL_YEAR,		 
          L_DAY_OF_FISCAL_WEEK,		
          L_DAY_OF_FISCAL_MONTH,    	
          L_DAY_OF_FISCAL_QUARTER,	
          L_DAY_OF_FISCAL_YEAR,	
          L_CAL_YID,
          L_CAL_WID,
          L_CAL_MID,
          L_CAL_QID,
          L_FISCAL_WID,
          L_FISCAL_MID,
          L_FISCAL_QID,
          L_FISCAL_YID ,
          L_FISCAL_SDATE,
          L_FISCAL_DOW,
          L_FISCAL_TYPE
        );

PIPE ROW (DAY_TYPE_ROW);

END LOOP; -- END OF FOR DAY_COUNT ... 
     STARTDATE_OF_YEAR := ENDDATE_OF_YEAR + 1;
     L_CAL_YID := L_CAL_YID + 1;
     -- dbms_output.put_line ('STARTDATE_OF_NEXT_YEAR = '|| STARTDATE_OF_YEAR );

-- dbms_output.put_line ('============');
   END LOOP; -- END OF YEAR_COUNT in 1 .. YEARS

RETURN ;  
 END;

-----------------------------------------------------------
 ---
 ---   CALENDAR YEAR TABLE FUNCTION
 ---
 -----------------------------------------------------------

FUNCTION CAL_YEAR_GENERATOR_TFN 
 ( 
   INPUT_ROWS IN SYS_REFCURSOR
 ) 
  RETURN CAL_YEAR_TYPE_TABLE
  PIPELINED
 IS 
   L_CAL_YEAR_ID         NUMBER := 0;
   L_CAL_YEAR_NUMBER     NUMBER := 0;
   L_CAL_YEAR_STARTDATE  DATE;
   L_CAL_YEAR_ENDDATE    DATE;
   L_CAL_YEAR_TIMESPAN   NUMBER := 0;
   L_CAL_YEAR_DESC       VARCHAR2(2000);

CAL_YEAR_TYPE_ROW     CAL_YEAR_TYPE;
   YSTARTDATE            DATE;
   YENDDATE              DATE;
   DAY_CAL_YEAR          NUMBER := 0;
   QTR_CAL_YEAR          NUMBER := 0;
   MONTH_CAL_YEAR        NUMBER := 0;
   YEAR_ID               NUMBER := 0;
   IN_DATE               DATE;
   FRM_DAY               NUMBER  := -1;
   FRM_CWEEK             NUMBER  := -1;
   FRM_CMTH              NUMBER  := -1;
   FRM_CQTR              NUMBER  := -1;
 BEGIN
   LOOP
     FETCH INPUT_ROWS INTO FRM_DAY, FRM_CWEEK, FRM_CMTH, FRM_CQTR, 
                           IN_DATE, 
                           YEAR_ID, 
                           DAY_CAL_YEAR, 
                           QTR_CAL_YEAR,
                           MONTH_CAL_YEAR;
     EXIT WHEN INPUT_ROWS%NOTFOUND;
     
     if (FRM_DAY = 1) AND (FRM_CQTR = 0) AND (FRM_CMTH = 0) then
       if DAY_CAL_YEAR = 1 then
         YSTARTDATE := IN_DATE;
         YENDDATE := add_months(YSTARTDATE, 12) - 1;
    
         L_CAL_YEAR_ID := YEAR_ID; 
         L_CAL_YEAR_NUMBER := to_char(YSTARTDATE, 'YYYY');
         L_CAL_YEAR_DESC := 'YEAR'|| ' ' || L_CAL_YEAR_NUMBER; 
         L_CAL_YEAR_STARTDATE := YSTARTDATE;
         L_CAL_YEAR_ENDDATE :=   YENDDATE;
         L_CAL_YEAR_TIMESPAN := YENDDATE - YSTARTDATE + 1; 
     
         CAL_YEAR_TYPE_ROW := 
          CAL_YEAR_TYPE(
           L_CAL_YEAR_ID,
           L_CAL_YEAR_NUMBER, 
           L_CAL_YEAR_DESC ,
           L_CAL_YEAR_STARTDATE , 
           L_CAL_YEAR_ENDDATE , 
           L_CAL_YEAR_TIMESPAN 
         );
         PIPE ROW (CAL_YEAR_TYPE_ROW);
       end if;
     end if;

if (FRM_DAY = 0) AND (FRM_CQTR = 1) AND (FRM_CMTH = 0)then
       if QTR_CAL_YEAR = 1 then
         YSTARTDATE := IN_DATE;
         YENDDATE := add_months(YSTARTDATE, 12) - 1;
    
         L_CAL_YEAR_ID := YEAR_ID; 
         L_CAL_YEAR_NUMBER := to_char(YSTARTDATE, 'YYYY');
         L_CAL_YEAR_DESC := 'YEAR'|| ' ' || L_CAL_YEAR_NUMBER; 
         L_CAL_YEAR_STARTDATE := YSTARTDATE;
         L_CAL_YEAR_ENDDATE :=   YENDDATE;
         L_CAL_YEAR_TIMESPAN := YENDDATE - YSTARTDATE + 1; 
     
         CAL_YEAR_TYPE_ROW := 
          CAL_YEAR_TYPE(
           L_CAL_YEAR_ID,
           L_CAL_YEAR_NUMBER, 
           L_CAL_YEAR_DESC ,
           L_CAL_YEAR_STARTDATE , 
           L_CAL_YEAR_ENDDATE , 
           L_CAL_YEAR_TIMESPAN 
         );
         PIPE ROW (CAL_YEAR_TYPE_ROW);
       end if;
     end if;

if (FRM_DAY = 0) AND (FRM_CQTR = 0) AND (FRM_CMTH = 1) then
       if MONTH_CAL_YEAR = 1 then
         YSTARTDATE := IN_DATE;
         YENDDATE := add_months(YSTARTDATE, 12) - 1;
    
         L_CAL_YEAR_ID := YEAR_ID; 
         L_CAL_YEAR_NUMBER := to_char(YSTARTDATE, 'YYYY');
         L_CAL_YEAR_DESC := 'YEAR'|| ' ' || L_CAL_YEAR_NUMBER; 
         L_CAL_YEAR_STARTDATE := YSTARTDATE;
         L_CAL_YEAR_ENDDATE :=   YENDDATE;
         L_CAL_YEAR_TIMESPAN := YENDDATE - YSTARTDATE + 1; 
     
         CAL_YEAR_TYPE_ROW := 
          CAL_YEAR_TYPE(
           L_CAL_YEAR_ID,
           L_CAL_YEAR_NUMBER, 
           L_CAL_YEAR_DESC ,
           L_CAL_YEAR_STARTDATE , 
           L_CAL_YEAR_ENDDATE , 
           L_CAL_YEAR_TIMESPAN 
         );
         PIPE ROW (CAL_YEAR_TYPE_ROW);
       end if;
     end if;

END LOOP;
   CLOSE INPUT_ROWS;
  RETURN ;

END;

-----------------------------------------------------------
 ---
 ---   CALENDAR QUARTER TABLE FUNCTION
 ---
 -----------------------------------------------------------

FUNCTION CAL_QUARTER_GENERATOR_TFN 
 ( 
   INPUT_ROWS IN SYS_REFCURSOR
 ) 
  RETURN CAL_QUARTER_TYPE_TABLE
  PIPELINED
 IS 
  
  CAL_QUARTER_TYPE_ROW          CAL_QUARTER_TYPE;
  IN_DATE                       DATE;
  L_CAL_QUARTER_ID 		NUMBER;
  L_CAL_QUARTER_NUMBER 		NUMBER;
  L_CAL_QUARTER_DESCRIPTION 	VARCHAR2 (2000);
  L_CAL_QUARTER_YEAR  		NUMBER;
  L_CAL_QUARTER_STARTDATE  	DATE;
  L_CAL_QUARTER_ENDDATE  	DATE;
  L_CAL_QUARTER_TIMESPAN  	NUMBER;
  L_CAL_QUARTER_OF_YEAR         NUMBER;
  L_CAL_YID                     NUMBER;

FRM_DAY     NUMBER := -1;
  FRM_CMTH    NUMBER := -1;
  QCOUNT      NUMBER := 0;
  YEAR_ID     NUMBER := 0;
  QUARTER_ID  NUMBER := 0;
  DAY_CAL_QUARTER    NUMBER := -1;
  DAY_CAL_MONTH      NUMBER := -1;
  QSTARTDATE  DATE;
  QENDDATE    DATE;

BEGIN
   LOOP
     FETCH INPUT_ROWS INTO FRM_DAY, FRM_CMTH, 
                           IN_DATE, 
                           YEAR_ID, 
                           QUARTER_ID,
                           DAY_CAL_QUARTER,
                           DAY_CAL_MONTH;
     EXIT WHEN INPUT_ROWS%NOTFOUND;

if (FRM_DAY = 1) AND (FRM_CMTH = 0) then
       if DAY_CAL_QUARTER = 1 then
         QSTARTDATE := IN_DATE;
         QENDDATE   := getQuarterEndDate(QSTARTDATE);
         QCOUNT     := QCOUNT + 1;   
         L_CAL_YID := YEAR_ID;
         L_CAL_QUARTER_ID := QUARTER_ID; 
         L_CAL_QUARTER_NUMBER := to_char(QSTARTDATE, 'QQYYYY');
         L_CAL_QUARTER_OF_YEAR := QCOUNT;
         L_CAL_QUARTER_DESCRIPTION := 'QTR'|| ' ' || QCOUNT ; 
         L_CAL_QUARTER_STARTDATE := QSTARTDATE;
         L_CAL_QUARTER_ENDDATE :=   QENDDATE;
         L_CAL_QUARTER_TIMESPAN :=  QENDDATE - QSTARTDATE + 1; 
     
         CAL_QUARTER_TYPE_ROW := 
           CAL_QUARTER_TYPE(
             L_CAL_QUARTER_ID,
             L_CAL_QUARTER_NUMBER,
             L_CAL_QUARTER_OF_YEAR,
             L_CAL_QUARTER_DESCRIPTION,
             L_CAL_QUARTER_STARTDATE,
             L_CAL_QUARTER_ENDDATE,
             L_CAL_QUARTER_TIMESPAN, 
             L_CAL_YID
         );
         PIPE ROW (CAL_QUARTER_TYPE_ROW);
       end if;
     end if;

if (FRM_DAY = 0) AND (FRM_CMTH = 1) then
       if DAY_CAL_MONTH = 1 then
         QSTARTDATE := IN_DATE;
         QENDDATE   := getQuarterEndDate(QSTARTDATE);
         QCOUNT     := QCOUNT + 1;   
         L_CAL_YID := YEAR_ID;
         L_CAL_QUARTER_ID := QUARTER_ID; 
         L_CAL_QUARTER_NUMBER := to_char(QSTARTDATE, 'QQYYYY');
         L_CAL_QUARTER_OF_YEAR := QCOUNT;
         L_CAL_QUARTER_DESCRIPTION := 'QTR'|| ' ' || QCOUNT ; 
         L_CAL_QUARTER_STARTDATE := QSTARTDATE;
         L_CAL_QUARTER_ENDDATE :=   QENDDATE;
         L_CAL_QUARTER_TIMESPAN :=  QENDDATE - QSTARTDATE + 1; 
     
         CAL_QUARTER_TYPE_ROW := 
           CAL_QUARTER_TYPE(
             L_CAL_QUARTER_ID,
             L_CAL_QUARTER_NUMBER,
             L_CAL_QUARTER_OF_YEAR,
             L_CAL_QUARTER_DESCRIPTION,
             L_CAL_QUARTER_STARTDATE,
             L_CAL_QUARTER_ENDDATE,
             L_CAL_QUARTER_TIMESPAN, 
             L_CAL_YID
         );
         PIPE ROW (CAL_QUARTER_TYPE_ROW);
       end if;
     end if;

QCOUNT := mod (QCOUNT, 4) ;
   END LOOP;

CLOSE INPUT_ROWS;
  RETURN ;  
 END;

-----------------------------------------------------------
 ---
 ---   CALENDAR MONTH TABLE FUNCTION
 ---
 -----------------------------------------------------------

FUNCTION CAL_MONTH_GENERATOR_TFN 
 ( 
   INPUT_ROWS IN SYS_REFCURSOR
 ) 
  RETURN CAL_MONTH_TYPE_TABLE
  PIPELINED
 IS 
  
  CAL_MONTH_TYPE_ROW            CAL_MONTH_TYPE;
  IN_DATE                       DATE;
  L_CAL_MONTH_ID 		NUMBER;
  L_CAL_MONTH_NUMBER 		NUMBER;
  L_CAL_MONTH_DESCRIPTION 	VARCHAR2 (2000);
  L_CAL_MONTH_STARTDATE  	DATE;
  L_CAL_MONTH_ENDDATE  		DATE;
  L_CAL_MONTH_TIMESPAN  	NUMBER;
  L_CAL_MONTH_OF_QUARTER 	NUMBER;
  L_CAL_MONTH_OF_YEAR  		NUMBER;
  L_CAL_YID                     NUMBER;
  L_CAL_QID                     NUMBER;

FRM_DAY                       NUMBER := -1;

MCOUNT                        NUMBER := 0;
  YEAR_ID                       NUMBER := 0;
  QUARTER_ID                    NUMBER := 0;
  MONTH_ID                      NUMBER := 0;
  DAY_CAL_MONTH                 NUMBER := -1;
  MSTARTDATE                    DATE;
  MENDDATE                      DATE;

BEGIN
   LOOP
     FETCH INPUT_ROWS INTO FRM_DAY, 
                           IN_DATE, 
                           YEAR_ID, 
                           QUARTER_ID,
                           MONTH_ID,
                           DAY_CAL_MONTH;
     EXIT WHEN INPUT_ROWS%NOTFOUND;

if (FRM_DAY = 1) then
       if DAY_CAL_MONTH = 1 then
         
         MSTARTDATE := IN_DATE;
         MENDDATE   := getMonthEndDate(MSTARTDATE);
         MCOUNT     := MCOUNT + 1;   
         L_CAL_YID := YEAR_ID;
         L_CAL_QID := QUARTER_ID;
         L_CAL_MONTH_ID := MONTH_ID; 
         L_CAL_MONTH_NUMBER := to_char(MSTARTDATE, 'MMYYYY');
         L_CAL_MONTH_OF_YEAR := MCOUNT;
         L_CAL_MONTH_DESCRIPTION := 'MONTH'|| ' ' || MCOUNT ; 
         L_CAL_MONTH_STARTDATE := MSTARTDATE;
         L_CAL_MONTH_ENDDATE :=   MENDDATE;
         L_CAL_MONTH_TIMESPAN :=  MENDDATE - MSTARTDATE + 1; 
         L_CAL_MONTH_OF_QUARTER := getMonthQuarter ( MSTARTDATE );
   
         CAL_MONTH_TYPE_ROW := 
           CAL_MONTH_TYPE(
             L_CAL_MONTH_ID,
             L_CAL_MONTH_NUMBER,
             L_CAL_MONTH_DESCRIPTION,
             L_CAL_MONTH_STARTDATE,
             L_CAL_MONTH_ENDDATE,
             L_CAL_MONTH_TIMESPAN,  
             L_CAL_MONTH_OF_QUARTER,
             L_CAL_MONTH_OF_YEAR,
             L_CAL_YID,
             L_CAL_QID
         );
         PIPE ROW (CAL_MONTH_TYPE_ROW);

end if;
    end if; 
    MCOUNT := mod ( MCOUNT, 12 );
   END LOOP;

CLOSE INPUT_ROWS;
  RETURN ;  
 END;

-----------------------------------------------------------
 ---
 ---   CALENDAR WEEK TABLE FUNCTION
 ---
 -----------------------------------------------------------

FUNCTION CAL_WEEK_GENERATOR_TFN 
 ( 
   INPUT_ROWS IN SYS_REFCURSOR
 ) 
  RETURN CAL_WEEK_TYPE_TABLE
  PIPELINED
 IS 
  
  CAL_WEEK_TYPE_ROW             CAL_WEEK_TYPE;
  IN_DATE                       DATE;
  L_CAL_WEEK_ID 		NUMBER;
  L_CAL_WEEK_NUMBER 		NUMBER;
  L_CAL_WEEK_DESCRIPTION 	VARCHAR2 (2000);
  L_CAL_WEEK_STARTDATE  	DATE;
  L_CAL_WEEK_ENDDATE  		DATE;
  L_CAL_WEEK_TIMESPAN  		NUMBER;

FRM_DAY                       NUMBER := -1;
  WCOUNT                        NUMBER := 0;
  WEEK_ID                       NUMBER := 0;
  DAY_CAL_WEEK                  NUMBER := -1;
  WSTARTDATE                    DATE;
  WENDDATE                      DATE;
  WID                           NUMBER := 0;

BEGIN
   LOOP
     FETCH INPUT_ROWS INTO FRM_DAY, 
                           IN_DATE, 
                           WEEK_ID,
                           DAY_CAL_WEEK;
     EXIT WHEN INPUT_ROWS%NOTFOUND;

if (FRM_DAY = 1) then
       if DAY_CAL_WEEK = 1 then
         
         WSTARTDATE := IN_DATE;
         WENDDATE   := getWeekEndDate(WSTARTDATE);
         WCOUNT     := WCOUNT + 1;

L_CAL_WEEK_ID := WEEK_ID; 
         L_CAL_WEEK_NUMBER := to_char(WSTARTDATE, 'WWYYYY');
         L_CAL_WEEK_DESCRIPTION := 'WEEK'|| ' ' || WCOUNT ; 
         L_CAL_WEEK_STARTDATE   := WSTARTDATE;
         L_CAL_WEEK_ENDDATE     := WENDDATE;
         L_CAL_WEEK_TIMESPAN    := WENDDATE - WSTARTDATE + 1; 
   
         CAL_WEEK_TYPE_ROW := 
           CAL_WEEK_TYPE(
             L_CAL_WEEK_ID,
             L_CAL_WEEK_NUMBER,
             L_CAL_WEEK_DESCRIPTION,
             L_CAL_WEEK_STARTDATE,
             L_CAL_WEEK_ENDDATE,
             L_CAL_WEEK_TIMESPAN
         );
         PIPE ROW (CAL_WEEK_TYPE_ROW);
       end if;
    end if; 
    WCOUNT := mod ( WCOUNT, 7 );
   END LOOP;

CLOSE INPUT_ROWS;
  RETURN ;  
 END;

-----------------------------------------------------------
 ---
 ---  FISCAL CALENDAR WEEK TABLE FUNCTION
 ---
 -----------------------------------------------------------

FUNCTION FISCAL_WEEK_GENERATOR_TFN 
 ( 
   INPUT_ROWS IN SYS_REFCURSOR
 ) 
  RETURN FISCAL_WEEK_TYPE_TABLE
  PIPELINED
 IS 
  
  FISCAL_WEEK_TYPE_ROW          FISCAL_WEEK_TYPE;
  IN_DATE                       DATE;
  L_FISCAL_WEEK_ID 		NUMBER;
  L_FISCAL_WEEK_NUMBER 		NUMBER;
  L_FISCAL_WEEK_DESCRIPTION 	VARCHAR2 (2000);
  L_FISCAL_WEEK_STARTDATE  	DATE;
  L_FISCAL_WEEK_ENDDATE  	DATE;
  L_FISCAL_WEEK_TIMESPAN  	NUMBER;
  L_FISCAL_WEEK_OF_MONTH        NUMBER := 0;
  L_FISCAL_WEEK_OF_QUARTER      NUMBER := 0;
  L_FISCAL_WEEK_OF_YEAR         NUMBER := 0;
  L_FISCAL_TYPE                 NUMBER := 0;
  L_FISCAL_DOW                  NUMBER := 0;
  L_FISCAL_SDATE                DATE;
  L_FISCAL_MID			NUMBER := 0;
  L_FISCAL_QID			NUMBER := 0;
  L_FISCAL_YID 			NUMBER := 0;

FRM_DAY                       NUMBER := -1;
  WCOUNT                        NUMBER := 0;
  WEEK_ID                       NUMBER := 0;
  DAY_OF_FISCAL_WEEK            NUMBER := -1;
  FISCAL_DOW                    NUMBER := 0;
  FISCAL_TYPE                   NUMBER := 0;
  FISCAL_YEAR_STARTDATE         DATE;
  WSTARTDATE                    DATE;
  WENDDATE                      DATE;
  WID                           NUMBER := 0;
  ROW_COUNTER                   NUMBER := 0;
  FWID                          NUMBER := 0;
  MONTH_ID                      NUMBER := 0;
  QUARTER_ID                    NUMBER := 0;
  YEAR_ID                       NUMBER := 0; 
 BEGIN
   LOOP
     FETCH INPUT_ROWS INTO FRM_DAY, 
                           IN_DATE,
                           DAY_OF_FISCAL_WEEK,
                           FISCAL_YEAR_STARTDATE,
                           FISCAL_DOW,
                           FISCAL_TYPE,
                           WEEK_ID,
                           MONTH_ID,
                           QUARTER_ID,
                           YEAR_ID;
     EXIT WHEN INPUT_ROWS%NOTFOUND;

ROW_COUNTER := ROW_COUNTER + 1;

if (FRM_DAY = 1) then
       -- 
       -- Do this for the first row and Day of fiscal week != 1
       -- 
       if (((ROW_COUNTER = 1) AND (DAY_OF_FISCAL_WEEK <> 1)) OR (DAY_OF_FISCAL_WEEK = 1))then
         WSTARTDATE := getFiscalWeekStartGivenDate(IN_DATE, FISCAL_YEAR_STARTDATE, FISCAL_DOW);
         WENDDATE   := getFiscalWeekendGivenDate  (IN_DATE, FISCAL_YEAR_STARTDATE, FISCAL_DOW);
         WCOUNT     := WCOUNT + 1;

FWID := getFiscalWeekId ( WSTARTDATE, FISCAL_YEAR_STARTDATE, FISCAL_DOW ) ;
         L_FISCAL_WEEK_ID          := WEEK_ID; 
         L_FISCAL_WEEK_NUMBER      := to_char(WSTARTDATE, 'WWYYYY');
         L_FISCAL_WEEK_DESCRIPTION := 'FISCAL WEEK'|| ' ' || FWID; 
         L_FISCAL_WEEK_STARTDATE   := WSTARTDATE;
         L_FISCAL_WEEK_ENDDATE     := WENDDATE;
         L_FISCAL_WEEK_TIMESPAN    := WENDDATE - WSTARTDATE + 1; 
         L_FISCAL_WEEK_OF_MONTH    := getWeekOfFiscalMonthGivenDate (
             IN_DATE, FISCAL_YEAR_STARTDATE, FISCAL_TYPE, FISCAL_DOW);
       
         L_FISCAL_WEEK_OF_QUARTER  := getWeekOfFiscalQtrGivenDate (
             IN_DATE, FISCAL_YEAR_STARTDATE, FISCAL_TYPE, FISCAL_DOW);

L_FISCAL_WEEK_OF_YEAR     := FWID;
         L_FISCAL_SDATE := FISCAL_YEAR_STARTDATE;
         L_FISCAL_DOW   := FISCAL_DOW;
         L_FISCAL_TYPE  := FISCAL_TYPE;
         L_FISCAL_MID   := MONTH_ID;
         L_FISCAL_QID   := QUARTER_ID;
         L_FISCAL_YID   := YEAR_ID;

FISCAL_WEEK_TYPE_ROW := 
           FISCAL_WEEK_TYPE(
             L_FISCAL_WEEK_ID,
             L_FISCAL_WEEK_NUMBER,
             L_FISCAL_WEEK_DESCRIPTION,
             L_FISCAL_WEEK_STARTDATE,
             L_FISCAL_WEEK_ENDDATE,
             L_FISCAL_WEEK_TIMESPAN,
             L_FISCAL_WEEK_OF_MONTH,
             L_FISCAL_WEEK_OF_QUARTER,
             L_FISCAL_WEEK_OF_YEAR,
             L_FISCAL_SDATE,
             L_FISCAL_DOW,
             L_FISCAL_TYPE,
             L_FISCAL_MID,
             L_FISCAL_QID,
             L_FISCAL_YID 
         );

PIPE ROW (FISCAL_WEEK_TYPE_ROW);

end if; -- end of if (ROW_COUNTER = 1) ...
    end if;

END LOOP;

CLOSE INPUT_ROWS;
  RETURN ;  
 END;

-----------------------------------------------------------
 ---
 ---  FISCAL CALENDAR MONTH TABLE FUNCTION
 ---
 -----------------------------------------------------------

FUNCTION FISCAL_MONTH_GENERATOR_TFN 
 ( 
   INPUT_ROWS IN SYS_REFCURSOR
 ) 
  RETURN FISCAL_MONTH_TYPE_TABLE
  PIPELINED
 IS 
  
  FISCAL_MONTH_TYPE_ROW         FISCAL_MONTH_TYPE;
  IN_DATE                       DATE;
  L_FISCAL_MONTH_ID 		NUMBER;
  L_FISCAL_MONTH_NUMBER 	NUMBER;
  L_FISCAL_MONTH_DESCRIPTION 	VARCHAR2 (2000);
  L_FISCAL_MONTH_STARTDATE  	DATE;
  L_FISCAL_MONTH_ENDDATE  	DATE;
  L_FISCAL_MONTH_TIMESPAN  	NUMBER;
  L_FISCAL_MONTH_OF_QUARTER     NUMBER := 0;
  L_FISCAL_MONTH_OF_YEAR        NUMBER := 0;
  L_FISCAL_SDATE                DATE;
  L_FISCAL_DOW                  NUMBER := 0;
  L_FISCAL_TYPE                 NUMBER := 0;
  L_FISCAL_QID	                NUMBER := 0;
  L_FISCAL_YID                  NUMBER := 0;

FRM_DAY                       NUMBER := -1;
  FRM_WEEK                      NUMBER := -1;
  MCOUNT                        NUMBER := 0;
  FISCAL_MID                    NUMBER := 0;
  FISCAL_YID                    NUMBER := 0;
  FISCAL_QID                    NUMBER := 0;
  DAY_OF_FISCAL_MONTH           NUMBER := -1;
  WEEK_OF_FISCAL_MONTH          NUMBER := -1;
  FISCAL_DOW                    NUMBER := 0;
  FISCAL_TYPE                   NUMBER := 0;
  FISCAL_YEAR_STARTDATE         DATE;
  MSTARTDATE                    DATE;
  MENDDATE                      DATE;
  MID                           NUMBER := 0;
  ROW_COUNTER                   NUMBER := 0;
  FMID                          NUMBER := 0;
 BEGIN
   LOOP

FETCH INPUT_ROWS INTO FRM_DAY, 
                           FRM_WEEK,
                           IN_DATE,
                           DAY_OF_FISCAL_MONTH,
                           WEEK_OF_FISCAL_MONTH,
                           FISCAL_YEAR_STARTDATE,
                           FISCAL_DOW,
                           FISCAL_TYPE,
                           FISCAL_MID,
                           FISCAL_QID,
                           FISCAL_YID;

EXIT WHEN INPUT_ROWS%NOTFOUND;

ROW_COUNTER := ROW_COUNTER + 1;

if (FRM_DAY = 1) AND (FRM_WEEK = 0) then
       -- 
       -- Do this for the first row and Day of fiscal Month != 1
       -- 
       if (((ROW_COUNTER = 1) AND (DAY_OF_FISCAL_MONTH <> 1)) OR (DAY_OF_FISCAL_MONTH = 1 )) then
         MSTARTDATE := getFiscalMonthStartGivenDate(IN_DATE, FISCAL_YEAR_STARTDATE, FISCAL_TYPE, FISCAL_DOW);
         MENDDATE   := getFiscalMonthEndGivenDate  (IN_DATE, FISCAL_YEAR_STARTDATE, FISCAL_TYPE, FISCAL_DOW);
         MCOUNT     := MCOUNT + 1;

FMID := getFiscalMonthId( MSTARTDATE, FISCAL_YEAR_STARTDATE, FISCAL_TYPE, FISCAL_DOW) ;
         L_FISCAL_MONTH_ID          := FISCAL_MID; 
         L_FISCAL_MONTH_NUMBER      := to_char(MSTARTDATE, 'MMYYYY');
         L_FISCAL_MONTH_DESCRIPTION := 'FISCAL MONTH'|| ' ' || FMID; 
         L_FISCAL_MONTH_STARTDATE   := MSTARTDATE;
         L_FISCAL_MONTH_ENDDATE     := MENDDATE;
         L_FISCAL_MONTH_TIMESPAN    := MENDDATE - MSTARTDATE + 1; 
       
         L_FISCAL_MONTH_OF_QUARTER  := getMonthOfFiscalQtrGivenDate (
             IN_DATE, FISCAL_YEAR_STARTDATE, FISCAL_TYPE, FISCAL_DOW);

L_FISCAL_MONTH_OF_YEAR     := FMID;
         L_FISCAL_SDATE := FISCAL_YEAR_STARTDATE;
         L_FISCAL_DOW   := FISCAL_DOW;
         L_FISCAL_TYPE  := FISCAL_TYPE;
         L_FISCAL_QID	:= FISCAL_QID;
         L_FISCAL_YID   := FISCAL_YID;
 
         FISCAL_MONTH_TYPE_ROW := 
           FISCAL_MONTH_TYPE(
             L_FISCAL_MONTH_ID,
             L_FISCAL_MONTH_NUMBER,
             L_FISCAL_MONTH_DESCRIPTION,
             L_FISCAL_MONTH_STARTDATE,
             L_FISCAL_MONTH_ENDDATE,
             L_FISCAL_MONTH_TIMESPAN,
             L_FISCAL_MONTH_OF_QUARTER,
             L_FISCAL_MONTH_OF_YEAR,
             L_FISCAL_SDATE,
             L_FISCAL_DOW,
             L_FISCAL_TYPE,
             L_FISCAL_QID,	
             L_FISCAL_YID 
         );

PIPE ROW (FISCAL_MONTH_TYPE_ROW);

end if; -- end of if (ROW_COUNTER = 1) ...
    end if;

if (FRM_DAY = 0) AND (FRM_WEEK = 1) then
       -- 
       -- Do this for the first row and Day of fiscal Month != 1
       -- 
       if ((WEEK_OF_FISCAL_MONTH = 1) OR ((ROW_COUNTER = 1) AND (WEEK_OF_FISCAL_MONTH <> 1))) then
         MSTARTDATE := getFiscalMonthStartGivenDate(IN_DATE, FISCAL_YEAR_STARTDATE, FISCAL_TYPE, FISCAL_DOW);
         MENDDATE   := getFiscalMonthEndGivenDate  (IN_DATE, FISCAL_YEAR_STARTDATE, FISCAL_TYPE, FISCAL_DOW);
         MCOUNT     := MCOUNT + 1;

FISCAL_MONTH_TYPE_ROW := 
           FISCAL_MONTH_TYPE(
             L_FISCAL_MONTH_ID,
             L_FISCAL_MONTH_NUMBER,
             L_FISCAL_MONTH_DESCRIPTION,
             L_FISCAL_MONTH_STARTDATE,
             L_FISCAL_MONTH_ENDDATE,
             L_FISCAL_MONTH_TIMESPAN,
             L_FISCAL_MONTH_OF_QUARTER,
             L_FISCAL_MONTH_OF_YEAR,
             L_FISCAL_SDATE,
             L_FISCAL_DOW,
             L_FISCAL_TYPE,
             L_FISCAL_QID,	
             L_FISCAL_YID 
         );

PIPE ROW (FISCAL_MONTH_TYPE_ROW);
       end if; -- end of if (ROW_COUNTER = 1) ...

end if;

END LOOP;

CLOSE INPUT_ROWS;
  RETURN ;  
 END;

-----------------------------------------------------------
 ---
 ---  FISCAL CALENDAR QUARTER TABLE FUNCTION
 ---
 -----------------------------------------------------------

FUNCTION FISCAL_QUARTER_GENERATOR_TFN 
 ( 
   INPUT_ROWS IN SYS_REFCURSOR
 ) 
  RETURN FISCAL_QUARTER_TYPE_TABLE
  PIPELINED
 IS  
  FISCAL_QUARTER_TYPE_ROW         FISCAL_QUARTER_TYPE;
  IN_DATE                         DATE;
  L_FISCAL_QUARTER_ID 		  NUMBER;
  L_FISCAL_QUARTER_NUMBER 	  NUMBER;
  L_FISCAL_QUARTER_DESCRIPTION 	  VARCHAR2 (2000);
  L_FISCAL_QUARTER_STARTDATE  	  DATE;
  L_FISCAL_QUARTER_ENDDATE  	  DATE;
  L_FISCAL_QUARTER_TIMESPAN  	  NUMBER;
  L_FISCAL_QUARTER_OF_YEAR        NUMBER := 0;
  L_FISCAL_SDATE                  DATE;
  L_FISCAL_DOW                    NUMBER := 0;
  L_FISCAL_TYPE                   NUMBER := 0;
  L_FISCAL_QID	                  NUMBER := 0;
  L_FISCAL_YID                    NUMBER := 0;
  FRM_DAY                         NUMBER := -1;
  FRM_WEEK                        NUMBER := -1;
  FRM_MONTH                       NUMBER := -1;
  QCOUNT                          NUMBER := 0;
  FISCAL_QID                      NUMBER := 0;
  FISCAL_YID                      NUMBER := 0;
  DAY_OF_FISCAL_QUARTER           NUMBER := -1;
  WEEK_OF_FISCAL_QUARTER          NUMBER := -1;
  MONTH_OF_FISCAL_QUARTER         NUMBER := -1;
  FISCAL_DOW                      NUMBER := 0;
  FISCAL_TYPE                     NUMBER := 0;
  FISCAL_YEAR_STARTDATE           DATE;
  QSTARTDATE                      DATE;
  QENDDATE                        DATE;
  QID                             NUMBER := 0;
  ROW_COUNTER                     NUMBER := 0;
  FQID                            NUMBER := 0;
 BEGIN
   LOOP
     FETCH INPUT_ROWS INTO 
                           FRM_DAY, 
                           FRM_WEEK, 
                           FRM_MONTH,
                           IN_DATE,
                           DAY_OF_FISCAL_QUARTER,
                           WEEK_OF_FISCAL_QUARTER,
                           MONTH_OF_FISCAL_QUARTER,
                           FISCAL_YEAR_STARTDATE,
                           FISCAL_DOW,
                           FISCAL_TYPE,
                           FISCAL_QID,
                           FISCAL_YID;
     EXIT WHEN INPUT_ROWS%NOTFOUND;

ROW_COUNTER := ROW_COUNTER + 1;

if (FRM_DAY = 1) AND (FRM_WEEK = 0) AND (FRM_MONTH = 0) then
       -- 
       -- Do this for the first row and Day of fiscal QUARTER != 1
       -- 
       if (((ROW_COUNTER = 1) AND (DAY_OF_FISCAL_QUARTER <> 1)) OR (DAY_OF_FISCAL_QUARTER = 1 )) then
         QSTARTDATE := getFiscalQuarterStartGivenDate (IN_DATE, FISCAL_YEAR_STARTDATE, FISCAL_TYPE, FISCAL_DOW);
         QENDDATE   := getFiscalQuarterEndGivenDate   (IN_DATE, FISCAL_YEAR_STARTDATE, FISCAL_TYPE, FISCAL_DOW);
         QCOUNT     := QCOUNT + 1;

FQID := getFiscalQuarterId( QSTARTDATE, FISCAL_YEAR_STARTDATE, FISCAL_TYPE, FISCAL_DOW) ;
         L_FISCAL_QUARTER_ID          := FISCAL_QID; 
         L_FISCAL_QUARTER_NUMBER      := to_char(QSTARTDATE, 'QQYYYY');
         L_FISCAL_QUARTER_DESCRIPTION := 'FISCAL QUARTER'|| ' ' || FQID; 
         L_FISCAL_QUARTER_STARTDATE   := QSTARTDATE;
         L_FISCAL_QUARTER_ENDDATE     := QENDDATE;
         L_FISCAL_QUARTER_TIMESPAN    := QENDDATE - QSTARTDATE + 1;        
         L_FISCAL_QUARTER_OF_YEAR     := FQID;
         L_FISCAL_SDATE := FISCAL_YEAR_STARTDATE;
         L_FISCAL_DOW   := FISCAL_DOW;
         L_FISCAL_TYPE  := FISCAL_TYPE;
         L_FISCAL_YID   := FISCAL_YID; 
 
         FISCAL_QUARTER_TYPE_ROW := 
           FISCAL_QUARTER_TYPE(
             L_FISCAL_QUARTER_ID,
             L_FISCAL_QUARTER_NUMBER,
             L_FISCAL_QUARTER_DESCRIPTION,
             L_FISCAL_QUARTER_STARTDATE,
             L_FISCAL_QUARTER_ENDDATE,
             L_FISCAL_QUARTER_TIMESPAN,
             L_FISCAL_QUARTER_OF_YEAR,
             L_FISCAL_SDATE,
             L_FISCAL_DOW,
             L_FISCAL_TYPE,	
             L_FISCAL_YID 
         );

PIPE ROW (FISCAL_QUARTER_TYPE_ROW);

end if; -- end of if (ROW_COUNTER = 1) ...
    end if;

if (FRM_DAY = 0) AND (FRM_WEEK = 1)  AND (FRM_MONTH = 0) then
       -- 
       -- Do this for the first row and Day of fiscal QUARTER != 1
       -- 
       if ((WEEK_OF_FISCAL_QUARTER = 1) OR ((ROW_COUNTER = 1) AND (WEEK_OF_FISCAL_QUARTER <> 1))) then
         QSTARTDATE := getFiscalQuarterStartGivenDate(IN_DATE, FISCAL_YEAR_STARTDATE, FISCAL_TYPE, FISCAL_DOW);
         QENDDATE   := getFiscalQuarterEndGivenDate  (IN_DATE, FISCAL_YEAR_STARTDATE, FISCAL_TYPE, FISCAL_DOW);
         QCOUNT     := QCOUNT + 1;

FISCAL_QUARTER_TYPE_ROW := 
           FISCAL_QUARTER_TYPE(
             L_FISCAL_QUARTER_ID,
             L_FISCAL_QUARTER_NUMBER,
             L_FISCAL_QUARTER_DESCRIPTION,
             L_FISCAL_QUARTER_STARTDATE,
             L_FISCAL_QUARTER_ENDDATE,
             L_FISCAL_QUARTER_TIMESPAN,
             L_FISCAL_QUARTER_OF_YEAR,
             L_FISCAL_SDATE,
             L_FISCAL_DOW,
             L_FISCAL_TYPE,
             L_FISCAL_YID 
         );

PIPE ROW (FISCAL_QUARTER_TYPE_ROW);
       end if; -- end of if (ROW_COUNTER = 1) ...

end if;

if (FRM_DAY = 0) AND (FRM_WEEK = 0)  AND (FRM_MONTH = 1) then
       -- 
       -- Do this for the first row and Day of fiscal QUARTER != 1
       -- 
       if ((MONTH_OF_FISCAL_QUARTER = 1) OR ((ROW_COUNTER = 1) AND (MONTH_OF_FISCAL_QUARTER <> 1))) then
         QSTARTDATE := getFiscalQuarterStartGivenDate(IN_DATE, FISCAL_YEAR_STARTDATE, FISCAL_TYPE, FISCAL_DOW);
         QENDDATE   := getFiscalQuarterEndGivenDate  (IN_DATE, FISCAL_YEAR_STARTDATE, FISCAL_TYPE, FISCAL_DOW);
         QCOUNT     := QCOUNT + 1;

PIPE ROW (FISCAL_QUARTER_TYPE_ROW);
       end if; -- end of if (ROW_COUNTER = 1) ...

end if;

END LOOP;

CLOSE INPUT_ROWS;
  RETURN ;  
 END;

-----------------------------------------------------------
 ---
 ---   FISCAL CALENDAR YEAR TABLE FUNCTION
 ---
 -----------------------------------------------------------

FUNCTION FISCAL_YEAR_GENERATOR_TFN 
 ( 
   INPUT_ROWS IN SYS_REFCURSOR
 ) 
  RETURN FISCAL_YEAR_TYPE_TABLE
  PIPELINED
 IS 
   L_FISCAL_YEAR_ID         NUMBER := 0;
   L_FISCAL_YEAR_NUMBER     NUMBER := 0;
   L_FISCAL_YEAR_STARTDATE  DATE;
   L_FISCAL_YEAR_ENDDATE    DATE;
   L_FISCAL_YEAR_TIMESPAN   NUMBER := 0;
   L_FISCAL_YEAR_DESCRIPTION       VARCHAR2(2000);
   L_FISCAL_SDATE           DATE;
   L_FISCAL_DOW             NUMBER := 0;
   L_FISCAL_TYPE            NUMBER := 0;

FISCAL_YEAR_TYPE_ROW     FISCAL_YEAR_TYPE;
   YSTARTDATE               DATE;
   YENDDATE                 DATE;
   DAY_OF_FISCAL_YEAR          NUMBER := 0;
   WEEK_OF_FISCAL_YEAR         NUMBER := 0;
   MONTH_OF_FISCAL_YEAR        NUMBER := 0;
   QUARTER_OF_FISCAL_YEAR      NUMBER := 0;
   YCOUNT                   NUMBER := 0;

DAY_CAL_YEAR             NUMBER := 0;
   YEAR_ID                  NUMBER := 0;
   IN_DATE                  DATE;
   FRM_DAY                  NUMBER  := -1;
   FRM_FWEEK                NUMBER  := -1;
   FRM_FMONTH               NUMBER  := -1;
   FRM_FQUARTER             NUMBER  := -1;
   FFLAG                    BOOLEAN := TRUE;
   ROW_COUNTER              NUMBER := 0;
   FISCAL_YID               NUMBER := 0;
   FISCAL_YEAR_STARTDATE    DATE ;
 BEGIN
   LOOP
     FETCH INPUT_ROWS INTO FRM_DAY, 
                           FRM_FWEEK, 
                           FRM_FMONTH, 
                           FRM_FQUARTER, 
                           IN_DATE,           
                           DAY_OF_FISCAL_YEAR,
                           WEEK_OF_FISCAL_YEAR,
                           MONTH_OF_FISCAL_YEAR,
                           QUARTER_OF_FISCAL_YEAR,
                           FISCAL_YEAR_STARTDATE,
                           FISCAL_YID;

EXIT WHEN INPUT_ROWS%NOTFOUND;

ROW_COUNTER := ROW_COUNTER + 1;

if (FRM_DAY = 1) AND (FRM_FWEEK = 0) AND (FRM_FMONTH = 0) AND (FRM_FQUARTER = 0) then

if (((ROW_COUNTER = 1) AND (DAY_OF_FISCAL_YEAR <> 1)) OR (DAY_OF_FISCAL_YEAR = 1 )) then
         YSTARTDATE := FISCAL_YEAR_STARTDATE;
         YENDDATE   := getFiscalYearEndDate ( YSTARTDATE );
         YCOUNT     := YCOUNT + 1;

L_FISCAL_YEAR_ID          := FISCAL_YID; 
         L_FISCAL_YEAR_NUMBER      := to_char(YSTARTDATE, 'YYYY');
         L_FISCAL_YEAR_DESCRIPTION := 'FISCAL YEAR'|| ' ' || YCOUNT; 
         L_FISCAL_YEAR_STARTDATE   := YSTARTDATE;
         L_FISCAL_YEAR_ENDDATE     := YENDDATE;
         L_FISCAL_YEAR_TIMESPAN    := YENDDATE - YSTARTDATE + 1;        
  
         FISCAL_YEAR_TYPE_ROW := 
           FISCAL_YEAR_TYPE(
             L_FISCAL_YEAR_ID,
             L_FISCAL_YEAR_NUMBER,
             L_FISCAL_YEAR_DESCRIPTION,
             L_FISCAL_YEAR_STARTDATE,
             L_FISCAL_YEAR_ENDDATE,
             L_FISCAL_YEAR_TIMESPAN
         );

PIPE ROW (FISCAL_YEAR_TYPE_ROW);

end if; -- end of if (ROW_COUNTER = 1) ...
    end if;

if (FRM_DAY = 0) AND (FRM_FWEEK = 1) AND (FRM_FMONTH = 0) AND (FRM_FQUARTER = 0) then

if (((ROW_COUNTER = 1) AND (WEEK_OF_FISCAL_YEAR <> 1)) OR (WEEK_OF_FISCAL_YEAR = 1 )) then
         YSTARTDATE := FISCAL_YEAR_STARTDATE;
         YENDDATE   := getFiscalYearEndDate ( YSTARTDATE );
         YCOUNT     := YCOUNT + 1;

end if; -- end of if (ROW_COUNTER = 1) ...
    end if;

if (FRM_DAY = 0) AND (FRM_FWEEK = 0) AND (FRM_FMONTH = 1) AND (FRM_FQUARTER = 0) then

if (((ROW_COUNTER = 1) AND (MONTH_OF_FISCAL_YEAR <> 1)) OR (MONTH_OF_FISCAL_YEAR = 1 )) then
         YSTARTDATE := FISCAL_YEAR_STARTDATE;
         YENDDATE   := getFiscalYearEndDate ( YSTARTDATE );
         YCOUNT     := YCOUNT + 1;

PIPE ROW (FISCAL_YEAR_TYPE_ROW);

end if; -- end of if (ROW_COUNTER = 1) ...
    end if;

if (FRM_DAY = 0) AND (FRM_FWEEK = 0) AND (FRM_FMONTH = 0) AND (FRM_FQUARTER = 1) then

if (((ROW_COUNTER = 1) AND (QUARTER_OF_FISCAL_YEAR <> 1)) OR (QUARTER_OF_FISCAL_YEAR = 1 )) then
         YSTARTDATE := FISCAL_YEAR_STARTDATE;
         YENDDATE   := getFiscalYearEndDate ( YSTARTDATE );
         YCOUNT     := YCOUNT + 1;

PIPE ROW (FISCAL_YEAR_TYPE_ROW);

end if; -- end of if (ROW_COUNTER = 1) ...
    end if;

END LOOP;
   CLOSE INPUT_ROWS;
  RETURN ;

END;

END OWB_TIMESERIES;
/

-- ========================================================
-- END CREATING PACKAGE BODY OWB_TIMESERIES
-- ========================================================

COMMIT;
/

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de