Script run_PF_D_TIME.sh

Introduction:

Runs the Process Flow PF_D_TIME.

Location:

Owner	dwmgr
Path	$DW_RUN/run_PF_D_TIME.sh
Permissions	750
Run as User	(OS) dwmgr

Notes:

• Specification.
  

run_PF_D_TIME.sh

#!/bin/bash # ########################################################################### # # # # Title: run_PF_D_TIME.sh # # Author: Steve Roach # # Date: 12-MAR-2006 # # Version: 01.00 # # # # Description: Runs the Process Flow PF_D_TIME. # # # # Parameters: start_date - The earliest date that D_TIME is to be # # populated with. # # end_date - The latest date that D_TIME is to be populated # # with. # # log_file_flag - If 'Y' then write all output to the log file # # $DW_LOGS/run_PF_D_TIME.sh.[date] where the date format is; # # 'YYYYMMDD.HHMISS', otherwise, write all output to STDOUT. # # # # Environment: The environmental variable WHS_WBA_PWD contains the password # # for the Oracle account; wba@whs. # # The environmental variable DW_LOGS contains the log directory # # path. # # # # History: # # # # Date Ver Author Description # # --------- ----- ------ ---------------------------------------------------- # # 12-MAR-06 01.00 SRR First release # # # # ########################################################################### # usage() { echo echo "usage $(basename $0) start_date end_date log_file_flag" } if [ "$1" = "" ] then usage echo echo "start_date missing." echo exit 1 fi start_date=$1 if [ "$2" = "" ] then usage echo echo "end_date missing." echo exit 1 fi end_date=$2 if [ "$3" = "Y" -o "$3" = "y" ] then log_file=$DW_LOGS/run_PF_D_TIME.sh.`date '+%Y%m%d.%H%M%S'` sqlplus wba/$WHS_WBA_PWD@whs @sqlplus_exec_template.sql wbr OWF PROCESS PF_D_TIME "," "P_START_DATE=$start_date,P_END_DATE=$end_date" &> $log_file else sqlplus wba/$WHS_WBA_PWD@whs @sqlplus_exec_template.sql wbr OWF PROCESS PF_D_TIME "," "P_START_DATE=$start_date,P_END_DATE=$end_date" 2>&1 fi exit 0

General Purpose Linux Script Header

#!/bin/sh # ########################################################################### # # # # Title: script # # Author: Steve Roach # # Date: DD-MON-YYYY # # Version: 01.00 # # # # Description: # # # # Parameters: parameter - description # # # # Environment: environmental variable description # # # # History: # # # # Date Ver Author Description # # --------- ----- ------ ---------------------------------------------------- # # DD-MON-YY 01.00 SRR First release # # # # ########################################################################### # usage() { echo echo "usage $(basename $0) param1 param2 ..." }

Script sqlplus_exec_template.sql

Introduction:

Oracle supplied script used to run Process Flows from Linux. Original location is $ORACLE_HOME/owb/rtp/sql - where $ORACLE_HOME is the OWB home.

Location:

Owner	dwmgr
Path	$DW_RUN/sqlplus_exec_template.sql
Permissions	750

Notes:

General Purpose SQL Script Header

This template is used as a header for SQL scripts:

-- ------------------------------------------------------------------------- -- -- -- -- Title: title -- -- Author: Steve Roach -- -- Date: DD-MON-YYYY -- -- Version: 01.00 -- -- -- -- Description: Description. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- DD-MON-YY 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- --

All Process Modules

Index for PM_%

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z Other

A:

PM_AD_HOC

B:

C:

D:

E:

F:

G:

H:

I:

J:

K:

L:

M:

N:

O:

P:

Q:

R:

S:

T:

U:

V:

W:

X:

Y:

Z:

Other:

All Process Packages

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z Other

A:

AD_HOC

B:

C:

D:

E:

F:

G:

H:

I:

J:

K:

L:

M:

N:

O:

P:

Q:

R:

S:

T:

U:

V:

W:

X:

Y:

Z:

Other:

Process Module PM_AD_HOC

Introduction:

Process Flow Module to contain Ad-Hoc Data Warehouse mappings.

Metadata:

OWB: Process Flows

Deployed to:

OWF_MGR@WHS

Contains:

AD_HOC

Notes:

Process Package AD_HOC

Introduction:

This Process Flow Package contains Ad-Hoc flows.

Metadata:

OWB: PM_AD_HOC

Contains:

• PF_D_TIME
  

Notes:

Definition Template - Process Flow Module

Introduction:

[Definition]

Metadata:

[location]

Deployed to:

[location]

Contains:

[location]

Notes:

• Specification.
  

Definition Template - Process Flow Packages

Introduction:

[Definition]

Metadata:

[location]

Contains:

• [process flow]
  

Notes:

• Specification.
  

All Process Flows

Index for PF_D_%

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z Other

A:

B:

C:

D:

E:

F:

G:

H:

I:

J:

K:

L:

M:

N:

O:

P:

Q:

R:

S:

T:

PF_D_TIME

U:

V:

W:

X:

Y:

Z:

Other:

Process Flow PF_D_TIME

Introduction:

This Process Flow loads the D_TIME Dimension.

Metadata:

OWB: Process Flows - PM_AD_HOC - AD_HOC

Contains:

• M_W2W_D_TIME
  

Notes:

• Specification.
  

Definition Template - Process Flow

Introduction:

[Definition]

Metadata:

[location]

Contains:

• [mapping]
  

Notes:

• Specification.
  

Script rec_tta_d_time.sql

Introduction:

Recreate Type TTA_D_TIME and its dependencies.

Location:

Owner	dwmgr
Path	$DW_WHS/rec_tta_d_time.sql
Permissions	750
Run as DB User	WHS@WHS

Notes:

• Specification.
  

Script dro_tta_d_time.sql

Introduction:

Drop Type TTA_D_TIME.

Location:

Owner	dwmgr
Path	$DW_WHS/dro_tta_d_time.sql
Permissions	750
Run as DB User	WHS@WHS

Notes:

• Specification.
  

Script dro_tob_d_time.sql

Introduction:

Drop Type TOB_D_TIME.

Location:

Owner	dwmgr
Path	$DW_WHS/dro_tob_d_time.sql
Permissions	750
Run as DB User	WHS@WHS

Notes:

• Specification.
  

Script cre_tta_d_time.sql

Introduction:

Create Type TTA_D_TIME.

Location:

Owner	dwmgr
Path	$DW_WHS/cre_tta_d_time.sql
Permissions	750
Run as DB User	WHS@WHS

Notes:

• Specification.
  

Script cre_tob_d_time.sql

Introduction:

Create Type TOB_D_TIME.

Location:

Owner	dwmgr
Path	$DW_WHS/cre_tob_d_time.sql
Permissions	750
Run as DB User	WHS@WHS

Notes:

• Specification.
  

Script (WHS) cre_syn.sql

Introduction:

Create synonyms for WHS.

Location:

Owner	dwmgr
Path	$DW_WHS/cre_syn.sql
Permissions	750
Run as DB User	WHS@WHS

Notes:

Type TTA_D_TIME

Introduction:

This table type is a table of TOB_D_TIME.

Location:

WHS@WHS

Notes:

• Specification.
  

Type TOB_D_TIME

Introduction:

An object type that describes the parameters to a function that generates D_TIME data.

Location:

WHS@WHS

Notes:

• Specification.
  

Definition Template - Type

Introduction:

[Definition]

Location:

[schema]@[instance]

Notes:

• Specification.
  

All Types

Index for TOB_D_%, TTA_D_%

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z Other

A:

B:

C:

D:

E:

F:

G:

H:

I:

J:

K:

L:

M:

N:

O:

P:

Q:

R:

S:

T:

TOB_D_TIME
TTA_D_TIME

U:

V:

W:

X:

Y:

Z:

Other:

Function F_D_TIME

Introduction:

A function to support the M_W2W_D_TIME 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.

Location:

WHS@WHS

Sourced From:

dwmgr - $DW_WHS/f_d_time.sql



Deployed To:

WHS@WHS

API:

FUNCTION f_d_time(
   p_start_date      IN VARCHAR2,
   p_end_date        IN VARCHAR2)
RETURN tta_d_time;

Notes:

• Specification.
  

Sequence S_D_TIME

Introduction:

A sequence used to generate level keys for the Time Dimension.

Services:

D_TIME

Notes:

• Specification.
  

All Synonyms

Index for WHS.PK_CTL_%

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z Other

A:

B:

C:

D:

E:

F:

G:

WHS.PK_CTL_GEN

H:

I:

J:

K:

L:

M:

N:

O:

P:

Q:

R:

S:

T:

U:

V:

W:

X:

Y:

Z:

Other:

Definition Template - Synonym

Location:

[owner]@[schema]

Referenced Object:

[owner].[object]@[schema]

Notes:

Synonym WHS.PK_CTL_GEN

Location:

whs@whs

Referenced Object:

CTL.PK_CTL_GEN@STA

Notes:

Script p_time_lib_test.sql

Introduction:

Test script for Package P_TIME_LIB.

Location:

Owner	dwmgr
Path	$DW_LIB/p_time_lib_test.sql
Permissions	750

Notes:

• Specification.
  

Script p_time_lib.sql

Introduction:

A set of library functions and procedures to support time, date and period functionality.

Location:

Owner	dwmgr
Path	$DW_LIB/p_time_lib.sql
Permissions	750

Notes:

• Specification.
  

Script p_string_lib_test.sql

Introduction:

Test script for Package P_STRING_LIB.

Location:

Owner	dwmgr
Path	$DW_LIB/p_string_lib_test.sql
Permissions	750

Notes:

• Specification.
  

Script p_string_lib.sql

Introduction:

A set of library functions and procedures to support string manipulation.

Location:

Owner	dwmgr
Path	$DW_LIB/p_string_lib.sql
Permissions	750

Notes:

• Specification.
  

Script p_number_lib_test.sql

Introduction:

Test script for Package P_NUMBER_LIB.

Location:

Owner	dwmgr
Path	$DW_LIB/p_number_lib_test.sql
Permissions	750

Notes:

• Specification.
  

Script p_number_lib.sql

Introduction:

A set of library functions and procedures to support number manipulation.

Location:

Owner	dwmgr
Path	$DW_LIB/p_number_lib.sql
Permissions	750

Notes:

• Specification.
  

Function CARDINAL_NUM

Introduction:

Return the cardinal string equivalent of the input number.

Location:

P_NUMBER_LIB

API:

   FUNCTION cardinal_num(
      p_number          NUMBER)
   RETURN VARCHAR2;

Notes:

• Specification.
  

Package P_NUMBER_LIB

Introduction:

A set of library functions and procedures to support number manipulation.

Metadata Location:

Definition.

Sourced From:

dwmgr - $DW_LIB/p_number_lib.sql

Deployed To:

ctl@sta

Contents:

• CARDINAL_NUM
  

Notes:

• Specification.
  

Function LTRIM_CHR

Introduction:

Strips all of the specified character from the start of the string.

Location:

P_STRING_LIB

API:

   FUNCTION ltrim_chr(
      p_string         VARCHAR2,
      p_chr            CHAR)
   RETURN VARCHAR2;

Notes:

• Specification.
  

Package P_STRING_LIB

Introduction:

A set of library functions and procedures to support string manipulation.

Metadata Location:

Definition.

Sourced From:

dwmgr - $DW_LIB/p_string_lib.sql

Deployed To:

ctl@sta

Contents:

• LTRIM_CHR
  

Notes:

• Specification.
  

Function FINANCIAL_YEAR

Introduction:

Returns the number of the financial year in the Australian financial year (1st July - 30th June) year that a date falls in.

Location:

P_TIME_LIB

API:

   FUNCTION financial_year(
      p_date          DATE)
   RETURN NUMBER;

Notes:

• Specification.
  

Function FINANCIAL_QTR

Introduction:

Returns the number of the financial quarter in the Australian financial year (1st July - 30th June) year that a date falls in.

Location:

P_TIME_LIB

API:

   FUNCTION financial_qtr(
      p_date          DATE)
   RETURN NUMBER;

Notes:

• Specification.
  

Function FINANCIAL_MONTH

Introduction:

Returns the number of the financial month in the Australian financial year (1st July - 30th June) year that a date falls in.

Location:

P_TIME_LIB

API:

   FUNCTION financial_month(
      p_date          DATE)
   RETURN NUMBER;

Notes:

• Specification.
  

Function FINANCIAL_WEEK

Introduction:

Returns the number of the financial week in the Australian financial year (1st July - 30th June) year that a date falls in.

Metadata Location:

P_TIME_LIB

API:

   FUNCTION financial_week(
      p_date          DATE)
   RETURN NUMBER;

Notes:

• Specification.
  

Function CALENDAR_QTR

Introduction:

Returns the number of the calendar quarter in the year that a date falls in.

Location:

P_TIME_LIB

API:

   FUNCTION calendar_qtr(
      p_date          DATE)
   RETURN NUMBER;

Notes:

• Specification.
  

Function CALENDAR_WEEK

Introduction:

Returns the number of the calendar week in the year that a date falls in.

Location:

P_TIME_LIB

API:

   FUNCTION calendar_week(
      p_date          DATE)
   RETURN NUMBER;

Notes:

• Specification.
  

Package P_TIME_LIB

Introduction:

A set of library functions and procedures to support time, date and period functionality.

Metadata Location:

Definition.

Sourced From:

dwmgr - $DW_LIB/p_time_lib.sql

Deployed To:

ctl@sta

Contents:

• calendar_week
• calendar_qtr
• financial_week
• financial_month
• financial_qtr
• financial_year

Notes:

• Specification.
  

All Mappings

Index of M_W2W_D_%

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z Other

A:

B:

C:

D:

E:

F:

G:

H:

I:

J:

K:

L:

M:

N:

O:

P:

Q:

R:

S:

T:

M_W2W_D_TIME

U:

V:

W:

X:

Y:

Z:

Other:

Mapping M_W2W_D_TIME

Introduction:

Pre-load the Time Dimension with natural and financial calendar data.

Metadata Location:

OWB - WHS - Mappings

Deployed To:

whs@whs

Included Objects:

Source Tables:

Target Tables:

• D_TIME
  

Links:

Included in Process Flows:

Process Flow

PF_D_TIME

Notes:

• Specification.
  

Definition Template - Mapping

Introduction:

[definition]

Metadata Location:

[facility metadata is stored]

Deployed To:

[deployment location]

Included Objects:

Source Tables:

• [source]

Target Tables:

• [target]
  

Links:

Included in Process Flows:

Process Flow

Notes:

• Specification.
  

Table D_TIME

Introduction:

The Time Dimension (D_TIME) contains several navigable hierarchies relating to time - these represent different ways of viewing the calendar. The two major groups are Natural (i.e. the normal yearly calendar) and Financial (a calendar representing the Australian business year).

Metadata Location:

OWB - WHS - Dimensions

Deployed To:

whs@whs

Links:

Included in Mappings:

Mappings

M_W2W_D_TIME

Run Dependencies:

Dependent On	Dependent To
None	Multiple objects

Notes:

• Specification.
  

Script dat_repositories.sql

Introduction:

Initial load of Table REPOSITORIES.

Location:

dwmgr - $DW_CTL - 750

Notes:

• Specification.
  

Script pk_ctl_gen_test.sql

Introduction:

Test script for Package PK_CTL_GEN.

Location:

dwmgr - $DW_CTL - 750

Notes:

• Specification.
  

Script pk_ctl_gen.sql

Introduction:

A package for general control procedures.

Location:

dwmgr - $DW_CTL - 750

Notes:

• Specification.
  

All Scripts

Index for F_%, P_%, PK_%, DAT_%, CRE_%, DRO_%, REC_%, run_PF_D_%

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z Other

A:

B:

C:

pk_ctl_gen.sql
pk_ctl_gen_test.sql

D:

E:

F:

G:

H:

I:

J:

K:

L:

M:

N:

p_number_lib.sql
p_number_lib_test.sql

O:

P:

Q:

R:

dat_repositories.sql

S:

p_string_lib.sql
p_string_lib_test.sql
(WHS) cre_syn.sql

T:

p_time_lib.sql
p_time_lib_test.sql

cre_tob_d_time.sql
cre_tta_d_time.sql
dro_tob_d_time.sql
dro_tta_d_time.sql
rec_tta_d_time.sql

run_PF_D_TIME.sh

U:

V:

W:

X:

Y:

Z:

Other:

Definition Template - Script

Introduction:

[Definition]

Location:

Owner
Path
Permissions
Run as User

Notes:

• Specification.
  

Procedure - FINALISE_MAPPING

Introduction:

Close the mapping_histories row by adding an end timestamp.

Metadata Location:

PK_CTL_GEN

API:

   PROCEDURE finalise_mapping(
      p_mapping           IN VARCHAR2,
      p_run_no            IN NUMBER);

Notes:

• Specification.
  

All Functions / Procedures

Index for F_D_%

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z Other

A:

B:

C:

CALENDAR_QTR
CALENDAR_WEEK
CARDINAL_NUM

D:

E:

F:

FINALISE_MAPPING
FINANCIAL_MONTH
FINANCIAL_QTR
FINANCIAL_WEEK
FINANCIAL_YEAR

G:

H:

I:

INITIALISE_MAPPING

J:

K:

L:

LTRIM_CHR

M:

N:

O:

P:

Q:

R:

S:

T:

F_D_TIME

U:

V:

W:

X:

Y:

Z:

Other:

Procedure - INITIALISE_MAPPING

Introduction:

Generate a timestamp and run number for the run. Create a mapping_histories row to start the run.

Metadata Location:

PK_CTL_GEN

API:

   PROCEDURE initialise_mapping(
      p_mapping           IN VARCHAR2,
      p_run_no            OUT NUMBER,
      p_run_date          OUT DATE);

Notes:

• Specification.
  

Definition Template - Function / Procedure

Introduction:

[Definition]

Location:

[schema]@[instance] OR [link to containing package]

Sourced From:

[user] - [path] *Remove if contained in package.

Deployed To:

[schema]@[instance] *Remove if contained in package.

API:

[API]

Notes:

• Specification.
  

Package PK_CTL_GEN

Introduction:

A package for general control procedures.

Metadata Location:

Script - pk_ctl_gen.sql

Sourced From:

dwmgr - $DW_CTL

Deployed To:

ctl@sta

Contents:

• initialise_mapping
• finalise_mapping
  

Notes:

• Specification.

All Packages

Index of PK_%, P_%

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z Other

A:

B:

C:

PK_CTL_GEN

D:

E:

F:

G:

H:

I:

J:

K:

L:

M:

N:

P_NUMBER_LIB

O:

P:

Q:

R:

S:

P_STRING_LIB

T:

P_TIME_LIB

U:

V:

W:

X:

Y:

Z:

Other:

Definition Template - Package

Introduction:

[Definition]

Metadata Location:

[Link to script post]

Sourced From:

[user] - [path]

Deployed To:

[schema]@[instance]

Contents:

[procedure name linked to specification]
[function name linked to specification]

Notes:

• Specification.
  

All Sequences

Index of S_%, S_D_%

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z Other

A:

B:

C:

D:

E:

F:

G:

H:

I:

J:

K:

L:

M:

S_MAH
S_MAL

N:

O:

P:

Q:

R:

S_REP
S_RUN_NO

S:

T:

S_D_TIME

U:

V:

W:

X:

Y:

Z:

Other:

Sequence S_RUN_NO

Introduction:

Generates Run Numbers.

Services:

All mappings.

Notes:

• Specification.
  

Sequence S_MAL

Introduction:

Generate IDs for MAPPING_LIBRARIES.

Services:

MAPPING_LIBRARIES

Notes:

• Specification.
  

Sequence S_MAH

Introduction:

Generate IDs for MAPPING_HISTORIES.

Services:

MAPPING_HISTORIES

Notes:

• Specification.
  

Sequence S_REP

Introduction:

Generate IDs for REPOSITORIES.

Services:

REPOSITORIES

Notes:

• Specification.
  

Definition Template - Sequence

Introduction:

[definition]

Services:

[database object]

Notes:

• Specification.
  

Table MAPPING_HISTORIES

Introduction:

A history of mapping runs.

Metadata Location:

Designer - ctl

Deployed To:

ctl@sta

Links:

None.

Notes:

• Specification.
  

Table MAPPING_LIBRARIES

Introduction:

A library of all available mappings.

Metadata Location:

Designer - ctl

Deployed To:

ctl@sta

Links:

None.

Notes:

• Specification.
  

Definition Template - Table

Introduction:

[definition]

Metadata Location:

[facility metadata is stored]

Deployed To:

[deployment location]

Links:

Included in Mappings:

Mappings

Run Dependencies:

Dependent On	Dependent To

Notes:

• Specification.
  

Table REPOSITORIES

Introduction:

Contains repository references.

Metadata Location:

Designer - ctl

Deployed To:

ctl@sta

Links:

None.

Notes:

• Specification.
  
• This table is seeded with data specified here.
  

All Tables

Index for %, D_%

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z Other

A:

B:

C:

D:

E:

F:

G:

H:

I:

J:

K:

L:

M:

MAPPING_HISTORIES
MAPPING_LIBRARIES

N:

O:

P:

Q:

R:

REPOSITORIES

S:

T:

D_TIME

U:

V:

W:

X:

Y:

Z:

Other:

Configuration Item Dependency Tree

This post links to all Data Warehouse Configuration Items:

• Functions / Procedures
• Mappings
  
• Packages
• Process Flows:
• Modules
• Packages
  
• Flows
  
• Scripts
  
• Sequences
• Synonyms
  
• Tables
• Types
  

Create a Process Flow

Introduction:

This post describes how a Process Flow is created in OWB.

Aim:

Create a Process Flow.

Requirements:

Mappings / Process Flows exist.

Procedure:

• Log on to OWB.
• Open a project.
• Expand 'Process Flows'.
  
• Process Module:
• If a Process Module for this flow exists expand it.
• Otherwise:
  
• Right-Click 'Process Flows'.
• -Create Process Module-
• Welcome:
• [Next]
• Name:
• Name: PM_[name].
• Description: Paste this from the definition section of the design.
• [Next]
• Location:
• Deployment Location: Choose or create a location.
• [Next]
• [Finish]
  
• Process Flow Package:
• If a Process Flow Package for this flow exists expand it.
• Otherwise:
  
• Right-Click the Process Flow Package.
• -Create Process Flow Package-
• Name: No more than 8 characters.
• Description: Paste this from the definition section of the design.
• [OK]
• Process Module:
• Right-Click the Process Module.
• -Create Process-
• Name: PF_[name].
• Description: Paste this from the definition section of the design.
• [OK]
• The Process Flow Diagrammer is launched.
  

Create a Wrapper Process Flow

Introduction:

This post describes how a Wrapper Process Flow is created in OWB.

A Wrapper Process Flow wraps a single Mapping and fulfills the following requirements:

• Allow the Mapping to be executed in stand-alone mode.
• Report any Error and Warning return statuses.
• Is itself wrapped by higher Process Flows in more complex dependency relationships.

Aim:

Create a Wrapper Process Flow.

Requirements:

The Mapping to be wrapped exists.

Procedure:

• Log on to OWB.
• Open a project.
• Expand 'Process Flows'.
• Create a Process Flow.
• Create the following flow:

• Component Configuration:
• START:
• One parameter for each MAPPING input parameter.
• P_SMTP_SERVER (STRING).
• P_PORT (INTEGER)
• P_FROM_ADDRESS (STRING)
• P_TO_ADDRESS (STRING)
• MAPPING:
• Bind the input parameters to the START parameters.
• EMAIL activities:
• Bind the following parameters to the START parameters:
• SMTP_SERVER
• PORT
• FROM_ADDRESS
• TO_ADDRESS
• Subject: [mapping name] has issued a(n) warning / error.
• Message Body: Same as Subject.
  

Create a Mapping

Introduction:

This procedure describes how a mapping is created in OWB. This, by definition can only be the outline. Mappings can be very complex and their design falls into an entire subject area.

Aim:

Create a Dimension definition in OWB.

Requirements:

Access to an OWB Target Module.

Procedure:

• Log on to OWB.
• Open a project.
• Expand 'Databases'
• Expand 'Oracle'
• Expand the target module.
• Right-click 'Dimensions'
• -Create Dimension-
• Welcome:
• [Next]
• Name:
  
• Name: Enter the name.
  
• Description: Paste this from the Definition section of the design.
• [OK]
• This creates the mapping. Double-clicking its name opens the Mapping Design editor.
  

Time Dimension - Table Function - f_d_time

-- ------------------------------------------------------------------------- -- -- -- -- 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;