-- ------------------------------------------------------------------------- --
-- --
-- Title: f_d_time.sql --
-- Author: Steve Roach --
-- Date: 29-JAN-2006 --
-- Version: 01.00 --
-- --
-- Description: A function to support the M_W2W_D_TIME TABLE FUNCTION --
-- mapping. This, pipelined, function creates a row for the --
-- dimension D_TIME for each date that falls into the period --
-- specified by the input parameters and returns the values in --
-- a table type; tta_d_time. --
-- --
-- Parameters: IN: p_start_date --
-- The first date that will be generated. --
-- p_end_date --
-- The last date that will be generated. --
-- --
-- Return: Table type tta_d_time which is based on tob_d_time. --
-- --
-- Installation: To install for production, run this script as-is. This will --
-- install the package in the target database. --
-- To install for debug, edit the 'ALTER SESSION' statement, --
-- below, changing 'debug: FALSE' to 'debug: TRUE' before --
-- running the script. --
-- --
-- History: --
-- --
-- Date Ver Author Description --
-- --------- ----- ------ -------------------------------------------------- --
-- 29-JAN=06 01.00 SRR First release --
-- --
-- ------------------------------------------------------------------------- --
-- Set flags for running debug mode:
ALTER SESSION SET PLSQL_CCFLAGS = 'debug: FALSE';
-- Compile function.
CREATE OR REPLACE FUNCTION f_d_time(
p_start_date IN VARCHAR2,
p_end_date IN VARCHAR2)
RETURN tta_d_time PIPELINED
IS
-- Declare output record.
out_rec tob_d_time := tob_d_time(NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL);
-- Declare locals.
l_start_date DATE;
l_end_date DATE;
l_date_span NUMBER := 0;
l_day_count NUMBER;
l_current_date DATE;
l_day_id NUMBER := 0;
l_day_start_date DATE;
l_day_end_date DATE;
l_day_julian_date NUMBER;
l_day_name VARCHAR2(10);
l_day_short_name VARCHAR2(3);
l_day_weekday VARCHAR2(1);
l_day_last_day_in_qtr VARCHAR2(1);
l_day_last_day_in_mth VARCHAR2(1);
l_nyr_id NUMBER := 0;
l_nyr_year NUMBER;
l_nyr_name VARCHAR2(4);
l_nyr_days_in_year NUMBER;
l_nqt_id NUMBER := 0;
l_nqt_qtr NUMBER;
l_nqt_name VARCHAR2(11);
l_nqt_short_name VARCHAR2(7);
l_nqt_ordering NUMBER;
l_nqt_days_in_qtr NUMBER;
l_nmn_id NUMBER := 0;
l_nmn_month NUMBER;
l_nmn_name VARCHAR2(10);
l_nmn_short_name VARCHAR2(3);
l_nmn_ordering NUMBER;
l_nmn_days_in_month NUMBER;
l_nwk_id NUMBER := 0;
l_nwk_week NUMBER := 0;
l_nwk_name VARCHAR2(4);
l_nwk_ordering NUMBER;
l_fyr_id NUMBER := 0;
l_fyr_year NUMBER := 0;
l_fyr_name VARCHAR2(9);
l_fyr_short_name VARCHAR2(16);
l_fyr_full_name VARCHAR2(25);
l_fyr_days_in_year NUMBER;
l_fqt_id NUMBER := 0;
l_fqt_qtr NUMBER := 0;
l_fqt_name VARCHAR2(3);
l_fqt_short_name VARCHAR2(11);
l_fqt_full_name VARCHAR2(25);
l_fqt_ordering NUMBER;
l_fmn_id NUMBER := 0;
l_fmn_month NUMBER := 0;
l_fmn_name VARCHAR2(3);
l_fmn_short_name VARCHAR2(11);
l_fmn_full_name VARCHAR2(25);
l_fmn_ordering NUMBER;
l_fwk_id NUMBER := 0;
l_fwk_week NUMBER := 0;
l_fwk_name VARCHAR2(4);
l_fwk_full_name VARCHAR2(25);
l_fwk_short_name VARCHAR2(11);
l_fwk_ordering NUMBER;
-- Declare exceptions.
e_date_span EXCEPTION;
PRAGMA EXCEPTION_INIT(e_date_span, -20001);
BEGIN
$IF $$debug
$THEN
dbms_output.put_line('f_d_time START');
dbms_output.put_line('p_start_date = ' || p_start_date);
dbms_output.put_line('p_end_date = ' || p_end_date);
$END
-- Set range parameters.
l_start_date := TO_DATE(p_start_date, 'DD-MON-RRRR');
l_end_date := TO_DATE(p_end_date, 'DD-MON-RRRR');
l_date_span := l_end_date - l_start_date;
-- Sanity check input parameters.
IF (l_date_span < 1)
THEN
RAISE e_date_span;
END IF;
-- Calculate calendar week of the start date - 1 day.
l_nwk_week := lib_time.calendar_week@sta.lisa@whs_ctl(l_start_date - 1);
$IF $$debug
$THEN
dbms_output.put_line('l_nwk_week = ' || l_nwk_week);
$END
-- Calculate calendar quarter of the start date - 1 day.
l_nqt_qtr := lib_time.calendar_qtr@sta.lisa@whs_ctl(l_start_date - 1);
$IF $$debug
$THEN
dbms_output.put_line('l_nqt_qtr = ' || l_nqt_qtr);
$END
-- Calculate financial week of the start date - 1 day.
l_fwk_week := lib_time.financial_week@sta.lisa@whs_ctl(l_start_date - 1);
$IF $$debug
$THEN
dbms_output.put_line('l_fwk_week = ' || l_fwk_week);
$END
-- Calculate financial month of the start date - 1 day.
l_fmn_month := lib_time.financial_month@sta.lisa@whs_ctl(l_start_date - 1);
$IF $$debug
$THEN
dbms_output.put_line('l_fmn_month = ' || l_fmn_month);
$END
-- Calculate financial quarter of the start date - 1 day.
l_fqt_qtr := lib_time.financial_qtr@sta.lisa@whs_ctl(l_start_date - 1);
$IF $$debug
$THEN
dbms_output.put_line('l_fqt_qtr = ' || l_fqt_qtr);
$END
-- Calculate financial year of the start date - 1 day.
l_fyr_year := lib_time.financial_year@sta.lisa@whs_ctl(l_start_date - 1);
$IF $$debug
$THEN
dbms_output.put_line('l_fyr_year = ' || l_fyr_year);
$END
FOR l_day_count IN 0 .. l_date_span
LOOP
l_current_date := l_start_date + l_day_count;
$IF $$debug
$THEN
dbms_output.put_line('l_current_date = ' || l_current_date);
$END
-- DAY attributes:
SELECT d_time_s.NEXTVAL
INTO l_day_id
FROM dual;
l_day_start_date := TO_DATE(TO_CHAR(l_current_date, 'DD-MON-RRRR') ||
'00:00:00', 'DD-MON-RRRR HH24:MI:SS');
l_day_end_date := TO_DATE(TO_CHAR(l_current_date, 'DD-MON-RRRR') ||
'23:59:59', 'DD-MON-RRRR HH24:MI:SS');
l_day_julian_date := TO_CHAR(l_current_date, 'J');
l_day_name := INITCAP(TO_CHAR(l_current_date, 'DAY'));
l_day_short_name := INITCAP(TO_CHAR(l_current_date, 'DY'));
IF l_day_short_name = 'Sat'
OR
l_day_short_name = 'Sun'
THEN
l_day_weekday := 'N';
ELSE
l_day_weekday := 'Y';
END IF;
IF TO_CHAR(l_current_date, 'DD-MON') = '31-MAR'
OR
TO_CHAR(l_current_date, 'DD-MON') = '30-JUN'
OR
TO_CHAR(l_current_date, 'DD-MON') = '30-SEP'
OR
TO_CHAR(l_current_date, 'DD-MON') = '31-DEC'
THEN
l_day_last_day_in_qtr := 'Y';
ELSE
l_day_last_day_in_qtr := 'N';
END IF;
IF TO_CHAR(l_current_date + 1, 'DD') = '01'
THEN
l_day_last_day_in_mth := 'Y';
ELSE
l_day_last_day_in_mth := 'N';
END IF;
$IF $$debug
$THEN
dbms_output.put_line('l_day_id = ' || l_day_id);
dbms_output.put_line('l_day_start_date = ' ||
TO_CHAR(l_day_start_date, 'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('l_day_end_date = ' ||
TO_CHAR(l_day_end_date, 'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('l_day_julian_date = ' || l_day_julian_date);
dbms_output.put_line('l_day_name = ' || l_day_name);
dbms_output.put_line('l_day_short_name = ' || l_day_short_name);
dbms_output.put_line('l_day_weekday = ' || l_day_weekday);
dbms_output.put_line('l_day_last_day_in_qtr = ' ||
l_day_last_day_in_qtr);
dbms_output.put_line('l_day_last_day_in_mth = ' ||
l_day_last_day_in_mth);
$END
-- NATURAL YEAR attributes:
IF TO_CHAR(l_current_date, 'DD-MON') = '01-JAN'
OR
l_nyr_id = 0
THEN
SELECT d_time_s.NEXTVAL
INTO l_nyr_id
FROM dual;
END IF;
l_nyr_year := TO_NUMBER(TO_CHAR(l_current_date, 'YYYY'));
l_nyr_name := TO_CHAR(l_nyr_year);
DECLARE
l_temp_date DATE;
BEGIN
SELECT TO_DATE('29-FEB-' || l_nyr_name, 'DD-MON-YYYY'),
366
INTO l_temp_date,
l_nyr_days_in_year
FROM dual;
EXCEPTION
WHEN others THEN
l_nyr_days_in_year := 365;
END;
$IF $$debug
$THEN
dbms_output.put_line('l_nyr_id = ' || l_nyr_id);
dbms_output.put_line('l_nyr_year = ' || l_nyr_year);
dbms_output.put_line('l_nyr_name = ' || l_nyr_name);
dbms_output.put_line('l_nyr_days_in_year = ' || l_nyr_days_in_year);
$END
-- NATURAL QUARTER attributes:
IF TO_CHAR(l_current_date, 'DD-MON') = '01-JAN'
OR
TO_CHAR(l_current_date, 'DD-MON') = '01-APR'
OR
TO_CHAR(l_current_date, 'DD-MON') = '01-JUL'
OR
TO_CHAR(l_current_date, 'DD-MON') = '01-OCT'
OR
l_nqt_id = 0
THEN
SELECT d_time_s.NEXTVAL
INTO l_nqt_id
FROM dual;
END IF;
CASE TO_CHAR(l_current_date, 'DD-MON')
WHEN '01-JAN' THEN
l_nqt_qtr := 1;
WHEN '01-APR' THEN
l_nqt_qtr := 2;
WHEN '01-JUL' THEN
l_nqt_qtr := 3;
WHEN '01-OCT' THEN
l_nqt_qtr := 4;
ELSE NULL; -- Keep value from last loop.
END CASE;
l_nqt_name := lib_number.cardinal_num@sta.lisa@whs_ctl(l_nqt_qtr) ||
' Quarter';
l_nqt_short_name := lib_number.cardinal_num@sta.lisa@whs_ctl(l_nqt_qtr)
|| ' Qtr';
l_nqt_ordering := TO_NUMBER(l_nyr_name || TO_CHAR(l_nqt_qtr));
CASE l_nqt_qtr
WHEN 1 THEN
l_nqt_days_in_qtr :=
TO_DATE('31-MAR-' || l_nyr_name, 'DD-MON-YYYY') -
TO_DATE('01-JAN-' || l_nyr_name, 'DD-MON-YYYY') + 1;
WHEN 2 THEN
l_nqt_days_in_qtr :=
TO_DATE('30-JUN-' || l_nyr_name, 'DD-MON-YYYY') -
TO_DATE('01-APR-' || l_nyr_name, 'DD-MON-YYYY') + 1;
WHEN 3 THEN
l_nqt_days_in_qtr :=
TO_DATE('30-SEP-' || l_nyr_name, 'DD-MON-YYYY') -
TO_DATE('01-JUL-' || l_nyr_name, 'DD-MON-YYYY') + 1;
WHEN 4 THEN
l_nqt_days_in_qtr :=
TO_DATE('31-DEC-' || l_nyr_name, 'DD-MON-YYYY') -
TO_DATE('01-OCT-' || l_nyr_name, 'DD-MON-YYYY') + 1;
END CASE;
$IF $$debug
$THEN
dbms_output.put_line('l_nqt_id = ' || l_nqt_id);
dbms_output.put_line('l_nqt_qtr = ' || l_nqt_qtr);
dbms_output.put_line('l_nqt_name = ' || l_nqt_name);
dbms_output.put_line('l_nqt_short_name = ' || l_nqt_short_name);
dbms_output.put_line('l_nqt_ordering = ' || l_nqt_ordering);
dbms_output.put_line('l_nqt_days_in_qtr = ' || l_nqt_days_in_qtr);
$END
-- NATURAL MONTH attributes:
IF TO_CHAR(l_current_date, 'DD') = '01'
OR
l_nmn_id = 0
THEN
SELECT d_time_s.NEXTVAL
INTO l_nmn_id
FROM dual;
END IF;
l_nmn_month :=
TO_NUMBER(lib_string.ltrim_chr@sta.lisa@whs_ctl(TO_CHAR(l_current_date,
'MM'), '0'));
l_nmn_name := INITCAP(RTRIM(TO_CHAR(l_current_date, 'MONTH')));
l_nmn_short_name := INITCAP(TO_CHAR(l_current_date, 'MON'));
l_nmn_ordering := TO_NUMBER(l_nyr_name || TO_CHAR(l_current_date, 'MM'));
l_nmn_days_in_month :=
TO_NUMBER(lib_string.ltrim_chr@sta.lisa@whs_ctl(TO_CHAR(TO_DATE('01'
||
TO_CHAR(ADD_MONTHS(l_current_date, 1), 'MON-YYYY'), 'DD-MON-YYYY') -1,
'DD'), '0'));
$IF $$debug
$THEN
dbms_output.put_line('l_nmn_id = ' || l_nmn_id);
dbms_output.put_line('l_nmn_month = ' || l_nmn_month);
dbms_output.put_line('l_nmn_name = ' || l_nmn_name);
dbms_output.put_line('l_nmn_short_name = ' || l_nmn_short_name);
dbms_output.put_line('l_nmn_ordering = ' || l_nmn_ordering);
dbms_output.put_line('l_nmn_days_in_month = ' || l_nmn_days_in_month);
$END
-- NATURAL WEEK attributes:
IF LTRIM(TO_CHAR(l_current_date, 'DAY')) = 'MONDAY'
OR
l_nwk_id = 0
THEN
SELECT d_time_s.NEXTVAL
INTO l_nwk_id
FROM dual;
END IF;
$IF $$debug
$THEN
dbms_output.put_line('TO_CHAR(l_current_date, ''DD-MON'') = ' ||
TO_CHAR(l_current_date, 'DD-MON'));
dbms_output.put_line('RTRIM(TO_CHAR(l_current_date, ''DAY'')) = ' ||
RTRIM(TO_CHAR(l_current_date, 'DAY')) || '.');
$END
IF TO_CHAR(l_current_date, 'DD-MON') = '01-JAN'
THEN
l_nwk_week := 1;
ELSE
IF RTRIM(TO_CHAR(l_current_date, 'DAY')) = 'MONDAY'
THEN
l_nwk_week := l_nwk_week + 1;
END IF;
END IF;
l_nwk_name := lib_number.cardinal_num@sta.lisa@whs_ctl(l_nwk_week);
l_nwk_ordering := TO_NUMBER(l_nyr_name || LPAD(l_nwk_week, 2, '0'));
$IF $$debug
$THEN
dbms_output.put_line('l_nwk_id = ' || l_nwk_id);
dbms_output.put_line('l_nwk_week = ' || l_nwk_week);
dbms_output.put_line('l_nwk_name = ' || l_nwk_name);
dbms_output.put_line('l_nwk_ordering = ' || l_nwk_ordering);
$END
-- FINANCIAL YEAR attributes:
IF TO_CHAR(l_current_date, 'DD-MON') = '01-JUL'
OR
l_fyr_id = 0
THEN
SELECT d_time_s.NEXTVAL
INTO l_fyr_id
FROM dual;
END IF;
IF TO_CHAR(l_current_date, 'DD-MON') = '01-JUL'
THEN
l_fyr_year := l_fyr_year + 1;
END IF;
l_fyr_name := TO_CHAR(TO_NUMBER(l_fyr_year) - 1) || '/' ||
TO_CHAR(l_fyr_year);
l_fyr_short_name := l_fyr_name || ' Fin Yr';
l_fyr_full_name := l_fyr_name || ' Financial Year';
DECLARE
l_temp_date DATE;
BEGIN
SELECT TO_DATE('29-FEB-' || l_fyr_name, 'DD-MON-YYYY'),
366
INTO l_temp_date,
l_fyr_days_in_year
FROM dual;
EXCEPTION
WHEN others THEN
l_fyr_days_in_year := 365;
END;
$IF $$debug
$THEN
dbms_output.put_line('l_fyr_id = ' || l_fyr_id);
dbms_output.put_line('l_fyr_year = ' || l_fyr_year);
dbms_output.put_line('l_fyr_name = ' || l_fyr_name);
dbms_output.put_line('l_fyr_short_name = ' || l_fyr_short_name);
dbms_output.put_line('l_fyr_full_name = ' || l_fyr_full_name);
dbms_output.put_line('l_fyr_days_in_year = ' || l_fyr_days_in_year);
$END
-- FINANCIAL QUARTER attributes:
IF TO_CHAR(l_current_date, 'DD-MON') = '01-JAN'
OR
TO_CHAR(l_current_date, 'DD-MON') = '01-APR'
OR
TO_CHAR(l_current_date, 'DD-MON') = '01-JUL'
OR
TO_CHAR(l_current_date, 'DD-MON') = '01-OCT'
OR
l_fqt_id = 0
THEN
SELECT d_time_s.NEXTVAL
INTO l_fqt_id
FROM dual;
END IF;
CASE TO_CHAR(l_current_date, 'DD-MON')
WHEN '01-JAN' THEN
l_fqt_qtr := 3;
WHEN '01-APR' THEN
l_fqt_qtr := 4;
WHEN '01-JUL' THEN
l_fqt_qtr := 1;
WHEN '01-OCT' THEN
l_fqt_qtr := 2;
ELSE NULL; -- Keep value from last loop.
END CASE;
l_fqt_name := lib_number.cardinal_num@sta.lisa@whs_ctl(l_fqt_qtr);
l_fqt_short_name := l_fqt_name || ' Fin Qtr';
l_fqt_full_name := l_fqt_name || ' Financial Quarter';
l_fqt_ordering := l_fyr_year || l_fqt_qtr;
$IF $$debug
$THEN
dbms_output.put_line('l_fqt_id = ' || l_fqt_id);
dbms_output.put_line('l_fqt_qtr = ' || l_fqt_qtr);
dbms_output.put_line('l_fqt_name = ' || l_fqt_name);
dbms_output.put_line('l_fqt_short_name = ' || l_fqt_short_name);
dbms_output.put_line('l_fqt_full_name = ' || l_fqt_full_name);
dbms_output.put_line('l_fqt_ordering = ' || l_fqt_ordering);
$END
-- FINANCIAL MONTH attributes:
IF TO_CHAR(l_current_date, 'DD') = '01'
OR
l_fmn_id = 0
THEN
SELECT d_time_s.NEXTVAL
INTO l_fmn_id
FROM dual;
END IF;
IF TO_CHAR(l_current_date, 'DD') = '01'
THEN
l_fmn_month := l_fmn_month + 1;
IF l_fmn_month = 13
THEN
l_fmn_month := 1;
END IF;
END IF;
l_fmn_name := lib_number.cardinal_num@sta.lisa@whs_ctl(l_fmn_month);
l_fmn_short_name := l_fmn_name || ' Fin Mth';
l_fmn_full_name := l_fmn_name || ' Financial Month';
l_fmn_ordering := l_fyr_year || LPAD(TO_CHAR(l_fmn_month), 2, '0');
$IF $$debug
$THEN
dbms_output.put_line('l_fmn_id = ' || l_fmn_id);
dbms_output.put_line('l_fmn_month = ' || l_fmn_month);
dbms_output.put_line('l_fmn_name = ' || l_fmn_name);
dbms_output.put_line('l_fmn_short_name = ' || l_fmn_short_name);
dbms_output.put_line('l_fmn_full_name = ' || l_fmn_full_name);
dbms_output.put_line('l_fmn_ordering = ' || l_fmn_ordering);
$END
-- FINANCIAL WEEK attributes:
IF LTRIM(TO_CHAR(l_current_date, 'DAY')) = 'MONDAY'
OR
l_fwk_id = 0
THEN
SELECT d_time_s.NEXTVAL
INTO l_fwk_id
FROM dual;
END IF;
IF TO_CHAR(l_current_date, 'DD-MON') = '01-JUL'
THEN
l_fwk_week := 1;
ELSE
IF RTRIM(TO_CHAR(l_current_date, 'DAY')) = 'MONDAY'
THEN
l_fwk_week := l_fwk_week + 1;
END IF;
END IF;
l_fwk_name := lib_number.cardinal_num@sta.lisa@whs_ctl(l_fwk_week);
l_fwk_full_name := l_fwk_name || ' Financial Week';
l_fwk_short_name := l_fwk_name || ' Fin Wk';
l_fwk_ordering := l_fyr_year || LPAD(TO_CHAR(l_fwk_week), 2, '0');
$IF $$debug
$THEN
dbms_output.put_line('l_fwk_id = ' || l_fwk_id);
dbms_output.put_line('l_fwk_week = ' || l_fwk_week);
dbms_output.put_line('l_fwk_name = ' || l_fwk_name);
dbms_output.put_line('l_fwk_full_name = ' || l_fwk_full_name);
dbms_output.put_line('l_fwk_short_name = ' || l_fwk_short_name);
dbms_output.put_line('l_fwk_ordering = ' || l_fwk_ordering);
$END
out_rec.DAY_ID := l_day_id;
out_rec.DAY_END_DATE := l_day_end_date;
out_rec.DAY_JULIAN_DATE := l_day_julian_date;
out_rec.DAY_LAST_DAY_IN_MONTH := l_day_last_day_in_mth;
out_rec.DAY_LAST_DAY_IN_QUARTER := l_day_last_day_in_qtr;
out_rec.DAY_NAME := l_day_name;
out_rec.DAY_SHORT_NAME := l_day_short_name;
out_rec.DAY_START_DATE := l_day_start_date;
out_rec.DAY_WEEKDAY := l_day_weekday;
out_rec.FMN_ID := l_fmn_id;
out_rec.FMN_FULL_NAME := l_fmn_full_name;
out_rec.FMN_MONTH := l_fmn_month;
out_rec.FMN_NAME := l_fmn_name;
out_rec.FMN_ORDERING := l_fmn_ordering;
out_rec.FMN_SHORT_NAME := l_fmn_short_name;
out_rec.FQT_ID := l_fqt_id;
out_rec.FQT_FULL_NAME := l_fqt_full_name;
out_rec.FQT_NAME := l_fqt_name;
out_rec.FQT_ORDERING := l_fqt_ordering;
out_rec.FQT_QUARTER := l_fqt_qtr;
out_rec.FQT_SHORT_NAME := l_fqt_short_name;
out_rec.FWK_ID := l_fwk_id;
out_rec.FWK_FULL_NAME := l_fwk_full_name;
out_rec.FWK_NAME := l_fwk_name;
out_rec.FWK_ORDERING := l_fwk_ordering;
out_rec.FWK_SHORT_NAME := l_fwk_short_name;
out_rec.FWK_WEEK := l_fwk_week;
out_rec.FYR_ID := l_fyr_id;
out_rec.FYR_DAYS_IN_YEAR := l_fyr_days_in_year;
out_rec.FYR_FULL_NAME := l_fyr_full_name;
out_rec.FYR_NAME := l_fyr_name;
out_rec.FYR_SHORT_NAME := l_fyr_short_name;
out_rec.FYR_YEAR := l_fyr_year;
out_rec.NMN_ID := l_nmn_id;
out_rec.NMN_DAYS_IN_MONTH := l_nmn_days_in_month;
out_rec.NMN_MONTH := l_nmn_month;
out_rec.NMN_NAME := l_nmn_name;
out_rec.NMN_ORDERING := l_nmn_ordering;
out_rec.NMN_SHORT_NAME := l_nmn_short_name;
out_rec.NQT_ID := l_nqt_id;
out_rec.NQT_DAYS_IN_QUARTER := l_nqt_days_in_qtr;
out_rec.NQT_NAME := l_nqt_name;
out_rec.NQT_ORDERING := l_nqt_ordering;
out_rec.NQT_QUARTER := l_nqt_qtr;
out_rec.NQT_SHORT_NAME := l_nqt_short_name;
out_rec.NWK_ID := l_nwk_id;
out_rec.NWK_NAME := l_nwk_name;
out_rec.NWK_ORDERING := l_nwk_ordering;
out_rec.NWK_WEEK := l_nwk_week;
out_rec.NYR_ID := l_nyr_id;
out_rec.NYR_DAYS_IN_YEAR := l_nyr_days_in_year;
out_rec.NYR_NAME := l_nyr_name;
out_rec.NYR_YEAR := l_nyr_year;
PIPE ROW(out_rec);
END LOOP;
RETURN;
EXCEPTION
WHEN e_date_span
THEN
RAISE_APPLICATION_ERROR(
-20001,
'Date range not contiguous',
FALSE);
WHEN others
THEN
RAISE;
END f_d_time;
/
SHOW ERRORS
SET serveroutput on size 1000000
select f_d_time('01-JAN-2006', '02-JAN-2006') from dual; |
0 Comments:
Post a Comment
<< Home