Data Warehouse Components

This post links to other posts detailing the Data Warehouse components:

• Configuration Item Dependency Tree


• The Control Repository
• The Time Dimension
  
• Get HTML
• The Country Dimension
  

Install TOAD

Introduction:

TOAD is, by far, the best Oracle developer tool there is. The downside is, the licences are very expensive - about $800 per user, I think. This is how to install the free version.

Aim:

Install and configure TOAD.

Requirements:

A PC running Oracle.

Procedure:

• Get the software:
• Go here and download TOAD (toadfree.zip).
• Unzip the file to 'Toad Freeware Install.EXE'.
• Install TOAD
  
• Run the executable and go through the installer.
• Set options:
• On first startup, choose the layout options presented.

Tables

2 Columns:

Title	Title
Data	Data
Data	Data

3 Columns:

Title	Title	Title
Data	Data	Data
Data	Data	Data

4 Columns:

Title	Title	Title	Title
Data	Data	Data	Data
Data	Data	Data	Data

5 Columns:

Title	Title	Title	Title	Title
Data	Data	Data	Data	Data
Data	Data	Data	Data	Data

Vertical:

Title	Data
Title	Data

In-page Index

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:

U:

V:

W:

X:

Y:

Z:

Other:

Create a Runtime Repository Connection

Introduction:

This procedure describes how to create a Runtime Repository Connection in OWB. This connection is used by OWB when deploying objects.

Aim:

Create a Runtime Repository Connection in OWB.

Requirements:

OWB is up and running.

Procedure:

• Log on to OWB.
• Open a project.
• Right-click Runtime Repository Connections.
• -Create Runtime Repository Connection-
• Welcome:
• [Next]
• Name:
  
• Name: Something that informs you about its function but keep this short, e.g. WHS_RTM.
• Description: A description.
• [Next]
  
• Details:
• Host Name: The same host that the Target User and Runtime Repository are on, e.g. lisa.
• Port Number: The same Runtime Repository uses, e.g. 1521.
• Service Name: The same Service (SID) that the Runtime Repository is on, e.g. whs.
• Connect As User: The Runtime Access User, e.g. wba.
• Runtime Repository Owner, e.g. wbr.
  
• [Next]
• [Finish]
  

Testing:

• If any objects can be deployed, this has worked.
  

Create a Connector

Introduction:

This procedure describes how a Connector is entered into OWB. This is a Database Link between two Oracle Modules. While these can be created in OWB, any maintenance, including dropping them, is done directly on the database.

Aim:

Create a Connector in OWB.

Requirements:

At least two Oracle Modules have been created in OWB.

Procedure:

• Log on to OWB.
• Open a project.
• Expand 'Databases'
• Expand 'Oracle'
• Expand 'Locations'
• Right-click the Module Location that will own the Connector, i.e. the 'from' end.
  
• -Create Connector-
• Welcome:
• [Next]
• Name:
  
• Name: Something that informs you about its function but keep this short. Names approaching 30 characters can cause problems when debugging mappings. e.g. for a Connector from whs to ctl; WHS_CTL.
• Description: A description.
• [Next]
  
• Details:
• Database: -the 'to' end-
• [Next]
• [Finish]
  

Testing:

• A new connector appears under the 'from' end Location.
  
• Right-click the new Connector.
• -Validate...-
• This should result in the message; 'Validation completed successfully.'
  

Time Dimension Implementation Notes

• As this is the first time that whs objects will be created in designer, create an Application System Context Container:
• Open Designer - Entity Relationship Diagrammer.
• File -> New
• Choose Container:
• Click [List-of-Values].
• [Create]
• Select Type:
• -Application Systems-
• [OK]
• Select Object:
• Change the name of the newly created icon to whs.
• [OK]
• Choose Container:
• -whs-
• [OK]
• The container is created and a diagrammer window displayed. There is no diagram at this stage so the Entity Relationship Diagrammer can be closed.
• Open Design Editor - Server Model. The new container should be listed.
  
• Open Design Editor - Server Model - whs and create and generate the following objects to whs@whs:
• D_TIME_S
• Move the following code to the whs code directory:
• 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
• f_d_time.sql
  
• Start OWB:
• I need a DB Link between whs and ctl so that my table function can access the PL/SQL library housed on ctl:
• Create an Oracle Module for whs as a Warehouse Target creating a location name WHS_TGT.WHS as the Connection and WHS_TGT as the Location.
• Create a Connector from whs to ctl named WHS_CTL.
• The Connector needs to be deployed to whs before it can be used and before that can happen, a Runtime Repository Connection needs to be created:
• Create a Runtime Repository Connection to the repository on whs named WHS_RTM.
  
• As dwmgr, start sqlplus as whs@whs and run the following scripts:
  
• rec_tta_d_time.sql
• Ignore the errors associated with the DROP statements.
• f_d_time.sql
  
• Note: This will create the function and then run a short test where it selects a few days from the function.
  
• Define the Dimension in OWB:
• Open OWB.
• Create A Dimension D_TIME base on the Definition.
• Create the Mapping in OWB:
• Open OWB.
• Create A Mapping M_W2W_D_TIME based on the Definition.
• Create the Process Flow in OWB:
• Open OWB:
• Create a Wrapper Process Flow - PF_D_TIME for M_W2W_D_TIME based on the Definition.
• Create the shell wrapper script:
• run_PF_D_TIME.sh
  

Control Repository - Implementation Notes

Implementing in Oracle XE:

• Upload to CTL:
• run_no_s.sql
• repositories_a.sql
• repositories_t.sql
• repositories_i.sql
• repositories_c.sql
• repositories_d.sql
• mapping_libraries_a.sql
• mapping_libraries_t.sql
• mapping_libraries_i.sql
• mapping_libraries_c.sql
• mapping_histories_a.sql
• mapping_histories_t.sql
• mapping_histories_i.sql
• mapping_histories_c.sql
• ctl_gen_a.sql
• ctl_gen_h.sql
• ctl_gen_b.sql

• Run the following scripts in order:
• CTL script: run_no_s.sql
• CTL script:repositories_a.sql
• CTL script: repositories_d.sql
• CTL script: mapping_libraries_a.sql
• CTL script: mapping_histories_a.sql
• CTL script: ctl_gen_a.sql
• CTL command: EXEC lib_util.create_grants;
• STA command: EXEC lib_util.create_synonyms;
• VAL command: EXEC lib_util.create_synonyms;
• ODS command: EXEC lib_util.create_synonyms;

• Run the test script; CTL: ctl_gen_test.sql. NOTE: Best run from the SQL Command Line.
  
• Each test outputs a result and, at the end, a pass/fail summary.
• Run the test script; STA: ctl_gen_test.sql. NOTE: Best run from the SQL Command Line.
  
• Each test outputs a result and, at the end, a pass/fail summary.
• Run the test script; VAL: ctl_gen_test.sql. NOTE: Best run from the SQL Command Line.
  
• Each test outputs a result and, at the end, a pass/fail summary.
• Run the test script; ODS: ctl_gen_test.sql. NOTE: Best run from the SQL Command Line.
  
• Each test outputs a result and, at the end, a pass/fail summary.

Create Warehouse Linux Infrastructure

Introduction:

This procedure is to create some Linux accounts and directories that will be used for managing and running the warehouse.

Aim:

Set up Linux-side maintenance accounts:

• dwmgr - Data Warehouse Manager:
• This account holds the Linux-side code library and is used to manage the Warehouse.
• dwoper - Data Warehouse Operator:
• This account carries out day-to-day Warehouse maintenance.

Directories:

• /u01
• dw
• code
• ctl - Control schema scripts.
  
• ods - ODS schema scripts.
• sta - Staging schema scripts.
• val - Validation schema scripts.
• whs - Warehouse schema scripts.
• lib - Library scripts.
• data
• landing - Where files first arrive.
• staging - Where files are loaded to.
• processed - Where files are stored after processing - the file archive.
• log - File processing logs.
• ctl - SQL*LOADER control logs.
• bad - SQL*LOADER bad files.
• dsc - SQL*LOADER discard files.
• log - General logs.
  

Requirements:

Linux is installed and configured.

Procedure:

• On the Linux desktop:
• -Desktop-
• -System Settings-
• -Users and Groups-
• Query:
• Password for root: *********
• User Manager:
• [Add Group]
• Create New Group:
• Group Name: dw
• [OK]
• [Add User]
• User Name: dwmgr
• Full Name: Data Warehouse Manager
• Password: *********
• Confirm Password: *********
• Create home directory
• Home Directory: /home/dwmgr
• [OK]
• -dwmgr-
• [Properties]
• User Properties:
  
• [Groups]
• dw
• dwmgr
• Primary Group: dw
• [OK]
  
• [Add User]
• User Name: dwoper
• Full Name: Data Warehouse Operator
  
• Password: *********
• Confirm Password: *********
• Create home directory
• Home Directory: /home/dwoper
• [OK]
• -dwoper-
• [Properties]
• User Properties:
  
• [Groups]
• dw
• dwoper
• Primary Group: dw
• [OK]
  
• Open Terminal.
• $ su - root
• Password: *********
• # cd /u01
• # mkdir dw
• # cd dw
• # mkdir -p code/ctl
  
• # mkdir -p code/ods
• # mkdir -p code/sta
• # mkdir -p code/val
• # mkdir -p code/whs
• # mkdir -p code/lib
• # mkdir -p data/landing
• # mkdir -p data/staging
• # mkdir -p data/processed
• # mkdir -p data/log/log
• # mkdir -p data/log/ctl
• # mkdir -p data/log/bad
• # mkdir -p data/log/dsc
• # cd ..
• # chown -R dwmgr.dw dw
• Edit both of the new user's .bash_profile file:
• Open Terminal and log in as [new user].
• $ vi .bash_profile
• Add the following just below the line 'export PATH':
  

# Warehouse paths: export DW_BASE=/u01/dw export DW_CODE=$DW_BASE/code export DW_CTL=$DW_CODE/ctl export DW_ODS=$DW_CODE/ods export DW_STA=$DW_CODE/sta export DW_VAL=$DW_CODE/val export DW_WHS=$DW_CODE/whs export DW_LIB=$DW_CODE/lib export DW_DATA=$DW_BASE/data export DW_LAND=$DW_DATA/landing export DW_STAG=$DW_DATA/staging export DW_PROC=$DW_DATA/processed export DW_LOGS=$DW_DATA/log export DW_LOG_LOG=$DW_LOGS/log export DW_CTL_LOG=$DW_LOGS/ctl export DW_BAD_LOG=$DW_LOGS/bad export DW_DSC_LOG=$DW_LOGS/dsc # Oracle definitions & locations. export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/10.2.0.1/db01 export PATH=$PATH:$ORACLE_HOME/bin export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib export ORACLE_TERM=vt100 export ORACLE_SID=`grep Y$ /etc/oratab | cut -d: -f1 | head -1` umask 022

• Install chora.
  

Control Repository - dat_repositories.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: dat_repositories.sql -- -- Author: Steve Roach -- -- Date: 12-FEB-2006 -- -- Version: 01.00 -- -- -- -- Description: Seed data for REPOSITORIES. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 12-FEB-06 1.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- DELETE FROM repositories; INSERT INTO repositories VALUES( s_rep.NEXTVAL, 'CONTROL'); INSERT INTO repositories VALUES( s_rep.NEXTVAL, 'EXTERNAL FILE'); INSERT INTO repositories VALUES( s_rep.NEXTVAL, 'EXTERNAL TABLE'); INSERT INTO repositories VALUES( s_rep.NEXTVAL, 'ODS'); INSERT INTO repositories VALUES( s_rep.NEXTVAL, 'STAGING'); INSERT INTO repositories VALUES( s_rep.NEXTVAL, 'VALIDATION'); INSERT INTO repositories VALUES( s_rep.NEXTVAL, 'WAREHOUSE'); COMMIT;

Control Repository - Configuration Items

This is a list of links to posts that comprise the configuration of the sub-system:

• Sequence RUN_NO_S
• Table REPOSITORIES
• Data REPOSITORIES
  
• Table MAPPING_LIBRARIES
• Table MAPPING_HISTORIES
• Package Procedure CTL_GEN
• Test script CTL_GEN_TEST

Control Repository - ctl_gen_test.sql

SET SERVEROUTPUT ON SIZE 1000000; -- Test ctl_gen: DECLARE l_mapping_1 VARCHAR2(30) := 'TEST_MAP_1'; l_mapping_2 VARCHAR2(30) := 'TEST_MAP_2'; l_run_no_1 NUMBER; l_run_no_2 NUMBER; l_end_date_1 VARCHAR2(30); l_end_date_2 VARCHAR2(30); l_row_count NUMBER; l_row_data VARCHAR2(4000); l_passed BOOLEAN := TRUE; BEGIN dbms_output.put_line('-'); dbms_output.put_line('- Initialise...'); -- Initialisation code. DELETE FROM mapping_histories WHERE mapping_library IN (l_mapping_1, l_mapping_2); DELETE FROM mapping_libraries WHERE mapping_name IN (l_mapping_1, l_mapping_2); INSERT INTO mapping_libraries ( mapping_name ,rep_from ,rep_to ) VALUES ( l_mapping_1 ,'CONTROL' ,'CONTROL' ); INSERT INTO mapping_libraries ( mapping_name ,rep_from ,rep_to ) VALUES ( l_mapping_2 ,'CONTROL' ,'CONTROL' ); COMMIT; dbms_output.put_line('- Done.'); dbms_output.put_line('-'); dbms_output.put_line('+- PROCEDURE initialise_mapping test ' || 'start:'); dbms_output.put_line('|'); dbms_output.put_line ('| Test 1: Pass ''TEST_MAP_1'': General test.'); dbms_output.put_line('|'); dbms_output.put_line ('| Expected result: Returns a Run number.'); dbms_output.put_line ('| Inserts a row into MAPPING_HISTORIES.'); dbms_output.put_line('|'); ctl_gen.initialise_mapping(l_mapping_1, l_run_no_1); dbms_output.put_line('| Result: Run number = ' || l_run_no_1); SELECT COUNT(*) INTO l_row_count FROM mapping_histories WHERE mapping_library = l_mapping_1; dbms_output.put_line( '| MAPPING_HISTORIES row count = ' || l_row_count); dbms_output.put_line('| MAPPING_HISTORIES data :'); SELECT '| [' || run_no || ', ' || TO_CHAR(start_tsp, 'DD-MON-YYYY HH24:MI:SS') || ', ' || NVL(TO_CHAR(end_tsp, 'DD-MON-YYYY HH24:MI:SS'), 'NULL') || ', ' || mapping_library || ']' INTO l_row_data FROM mapping_histories WHERE mapping_library = l_mapping_1; dbms_output.put_line(l_row_data); dbms_output.put_line('|'); dbms_output.put_line('| +----------+'); IF l_run_no_1 > 0 AND l_row_count = 1 THEN dbms_output.put_line('| | PASSED |'); ELSE dbms_output.put_line('| | FAILED |'); l_passed := FALSE; END IF; dbms_output.put_line('| +----------+'); dbms_output.put_line('|'); dbms_output.put_line('+- PROCEDURE initialise_mapping test end:'); dbms_output.put_line('+'); dbms_output.put_line('+- PROCEDURE finalise_mapping test start:'); dbms_output.put_line('|'); dbms_output.put_line ('| Test 1: Pass ''TEST_MAP_1'': General test.'); dbms_output.put_line('|'); dbms_output.put_line ('| Expected result: Updates MAPPING_HISTORIES with an end ' || 'date for ' || 'target'); dbms_output.put_line('| mapping.'); dbms_output.put_line('|'); ctl_gen.initialise_mapping(l_mapping_2, l_run_no_2); ctl_gen.finalise_mapping(l_run_no_1); dbms_output.put_line('| Result: MAPPING_HISTORIES data :'); SELECT '| [' || run_no || ', ' || TO_CHAR(start_tsp, 'DD-MON-YYYY HH24:MI:SS') || ', ' || NVL(TO_CHAR(end_tsp, 'DD-MON-YYYY HH24:MI:SS'), 'NULL') || ', ' || mapping_library || ']' INTO l_row_data FROM mapping_histories WHERE mapping_library = l_mapping_1; dbms_output.put_line(l_row_data); SELECT '| [' || run_no || ', ' || TO_CHAR(start_tsp, 'DD-MON-YYYY HH24:MI:SS') || ', ' || NVL(TO_CHAR(end_tsp, 'DD-MON-YYYY HH24:MI:SS'), 'NULL') || ', ' || mapping_library || ']' INTO l_row_data FROM mapping_histories WHERE mapping_library = l_mapping_2; dbms_output.put_line(l_row_data); SELECT TO_CHAR(end_tsp, 'DD-MON-YYYY HH24:MI:SS') INTO l_end_date_1 FROM mapping_histories WHERE mapping_library = l_mapping_1; dbms_output.put_line('| End date (' || l_mapping_1 || ') = ' || l_end_date_1); SELECT TO_CHAR(end_tsp, 'DD-MON-YYYY HH24:MI:SS') INTO l_end_date_2 FROM mapping_histories WHERE mapping_library = l_mapping_2; dbms_output.put_line('| End date (' || l_mapping_2 || ') = ' || NVL(l_end_date_2, 'NULL')); dbms_output.put_line('|'); dbms_output.put_line('| +----------+'); IF l_end_date_1 IS NOT NULL AND l_end_date_2 IS NULL THEN dbms_output.put_line('| | PASSED |'); ELSE dbms_output.put_line('| | FAILED |'); l_passed := FALSE; END IF; dbms_output.put_line('| +----------+'); dbms_output.put_line('|'); dbms_output.put_line('+- PROCEDURE finalise_mapping test end:'); dbms_output.put_line('-'); dbms_output.put_line('- Overall result:'); dbms_output.put_line('-'); dbms_output.put_line('- +----------+'); IF l_passed = TRUE THEN dbms_output.put_line('- | PASSED |'); ELSE dbms_output.put_line('- | FAILED |'); END IF; dbms_output.put_line('- +----------+'); dbms_output.put_line('-'); dbms_output.put_line('- Clean up...'); -- Clean up code. DELETE FROM mapping_histories WHERE mapping_library IN (l_mapping_1, l_mapping_2); DELETE FROM mapping_libraries WHERE mapping_name IN (l_mapping_1, l_mapping_2); COMMIT; dbms_output.put_line('- Done.'); dbms_output.put_line('-'); END; /

Control Repository - CTL_GEN

Scripts that support this object:

• ctl_gen_a.sql
• ctl_gen_h.sql
• ctl_gen_b.sql

Control Repository - Technical Specification

A number of objects will be built to support this sub-system:

Sequences:

• Name: RUN_NO_S
• Description: Generates Run Numbers.
  

Tables:

• Name: REPOSITORIES
• Description: Contains repository references.
• Columns:
• Name: NAME
• Description: Name of repository.
• Datatype: VARCHAR2(50)
• Null: No
• Unique: Yes
• Part of PK?: Yes
• Seed Data
• 'CONTROL'
• 'EXTERNAL FILE'
• 'EXTERNAL TABLE'
• 'EXTERNAL_HTML'
• 'ODS'
• 'STAGING'
• 'VALIDATION'
• 'WAREHOUSE'
  

• Name: MAPPING_LIBRARIES
• Description: A library of all available mappings.
• Columns:
• Name: MAPPING_NAME
• Description: Name of the mapping.
• Datatype: VARCHAR2(30)
• Null: No
• Unique: Yes
• Part of PK?: Yes
• Name: REP_FROM
• Description: The FROM repository for data movement. Foreign key from REPOSITORIES.NAME.
  
• Datatype: VARCHAR2(50)
  
• Null: No
• Unique: No
• Part of PK?: No
  
• Name: REP_TO
• Description: The TO repository for data movement. Foreign key from REPOSITORIES.NAME.
• Datatype: VARCHAR2(50)
• Null: No
• Unique: No
• Part of PK?: No

• Name: MAPPING_HISTORIES
• Description: A history of mapping runs.
• Columns:
• Name: RUN_NO
• Description: Primary key. Generated from RUN_NO_S.
• Datatype: NUMBER
• Null: No
• Unique: Yes
• Part of PK?: Yes
  
• Name: MAPPING_LIBRARY
• Description: Foreign key from MAPPING_LIBRARIES.MAPPING_NAME.
• Datatype: VARCHAR2(30)
  
• Null: No
• Unique: No
• Part of PK?: No
  
• Name: START_TSP
• Description: Timestamp of mapping run start.
• Datatype: DATE
• Null: No
• Unique: No
• Part of PK?: No
• Name: END_TSP
• Description: Timestamp of mapping run end.
• Datatype: DATE
• Null: Yes
• Unique: No
• Part of PK?: No

Processes:

• Package: CTL_GEN
  
• Description: A package for general control procedures.
• Procedure: initialise_mapping:
• Description: Generate a run number for the run. Create a mapping_histories row to start the run.
• Parameters:
• p_mapping
• Type: VARCHAR2
  
• Direction: IN
• Description: Mapping Name.
• p_run_no
• Type: NUMBER
  
• Direction: OUT
• Description: Number assigned to mapping run.
• Action:
• Generate a run number for the run.
• Create a mapping_histories row to start the run.
  

• Procedure: finalise_mapping:
• Description: Close the mapping_histories row by adding an end timestamp.
• Parameters:
• p_run_no
• Type: NUMBER
• Direction: IN
• Description: Number assigned to mapping run.
• Action:
• Close the mapping_histories row by adding an end timestamp.

HTML In-page Reference

This
links to
this.

Time Dimension - rec_tta_d_time.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: rec_tta_d_time.sql -- -- Author: Steve Roach -- -- Date: 29-JAN-2006 -- -- Version: 01.00 -- -- -- -- Description: Recreates tta_d_time by dropping the table type, then the -- -- object type, then creating the object type, then the table -- -- type. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- DD-MON-YY 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- @dro_tta_d_time @dro_tob_d_time @cre_tob_d_time @cre_tta_d_time

Time Dimension - dro_tta_d_time.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: dro_tta_d_time.sql -- -- Author: Steve Roach -- -- Date: 29-JAN-2006 -- -- Version: 01.00 -- -- -- -- Description: Drops the Table Type; tta_d_time. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 29-JAN-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- DROP TYPE tta_d_time;

Time Dimension - cre_tta_d_time.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: cre_tta_d_time.sql -- -- Author: Steve Roach -- -- Date: 29-JAN-2006 -- -- Version: 01.00 -- -- -- -- Description: Creates the Table Type; tta_d_time. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 29-JAN-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- CREATE TYPE tta_d_time AS TABLE OF tob_d_time; / SHOW ERRORS

Time Dimension - Table Type - tta_d_time

Introduction:

These are the maintenance scripts for this object.

Scripts:

• cre_tta_d_time.sql
• dro_tta_d_time.sql
• rec_tta_d_time.sql
  

Time Dimension - dro_tob_d_time.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: dro_tob_d_time.sql -- -- Author: Steve Roach -- -- Date: 29-JAN-2006 -- -- Version: 01.00 -- -- -- -- Description: Drops the Scalar Object Type; tob_d_time. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 29-JAN-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- DROP TYPE tob_d_time;

Time Dimension - cre_tob_d_time.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: cre_tob_d_time.sql -- -- Author: Steve Roach -- -- Date: 29-JAN-2006 -- -- Version: 01.00 -- -- -- -- Description: Creates the Scalar Object Type; tob_d_time. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 29-JAN-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- CREATE TYPE tob_d_time AS OBJECT( DAY_ID NUMBER, FMN_ID NUMBER, FQT_ID NUMBER, FWK_ID NUMBER, FYR_ID NUMBER, NMN_ID NUMBER, NQT_ID NUMBER, NWK_ID NUMBER, NYR_ID NUMBER, DAY_END_DATE DATE, DAY_JULIAN_DATE NUMBER(7), DAY_LAST_DAY_IN_MONTH VARCHAR2(1), DAY_LAST_DAY_IN_QUARTER VARCHAR2(1), DAY_NAME VARCHAR2(10), DAY_SHORT_NAME VARCHAR2(3), DAY_START_DATE DATE, DAY_WEEKDAY VARCHAR2(1), FMN_FULL_NAME VARCHAR2(25), FMN_MONTH NUMBER(2), FMN_NAME VARCHAR2(10), FMN_ORDERING NUMBER(6), FMN_SHORT_NAME VARCHAR2(11), FQT_FULL_NAME VARCHAR2(25), FQT_NAME VARCHAR2(3), FQT_ORDERING NUMBER(5), FQT_QUARTER NUMBER(1), FQT_SHORT_NAME VARCHAR2(11), FWK_FULL_NAME VARCHAR2(25), FWK_NAME VARCHAR2(4), FWK_ORDERING NUMBER(6), FWK_SHORT_NAME VARCHAR2(20), FWK_WEEK NUMBER(2), FYR_DAYS_IN_YEAR NUMBER(3), FYR_FULL_NAME VARCHAR2(25), FYR_NAME VARCHAR2(9), FYR_SHORT_NAME VARCHAR2(20), FYR_YEAR NUMBER(4), NMN_DAYS_IN_MONTH NUMBER(2), NMN_MONTH NUMBER(2), NMN_NAME VARCHAR2(10), NMN_ORDERING NUMBER(6), NMN_SHORT_NAME VARCHAR2(3), NQT_DAYS_IN_QUARTER NUMBER(2), NQT_NAME VARCHAR2(11), NQT_ORDERING NUMBER(5), NQT_QUARTER NUMBER(1), NQT_SHORT_NAME VARCHAR2(7), NWK_NAME VARCHAR2(4), NWK_ORDERING NUMBER(6), NWK_WEEK NUMBER(2), NYR_DAYS_IN_YEAR NUMBER(3), NYR_NAME VARCHAR2(4), NYR_YEAR NUMBER(4) ); / SHOW ERRORS

Time Dimension - Object Type; tob_d_time

Introduction:

These are the maintenance scripts for this object.

Scripts:

• cre_tob_d_time.sql
• dro_tob_d_time.sql
  

Install Oracle Workflow Server

Introduction:

Oracle Workflow stages the OWB Process Flows.

Aim:

Install and configure Oracle Workflow Server.

Requirements:

Oracle is installed and running on the server.

Procedure:

• Log on to Linux as Oracle.
• Open Firefox.
  
• Get the software:
• Go here (OTN - link through here to register), promise that you're not a terrorist, and download the following file:
• 10201_companion_linux32.zip
  
• Load the software:
• Open Terminal.
• $ mkdir orawf
• $ cd orawf
• $ unzip 10201_companion_linux32.zip
• This creates the directory; companion. Probably a good idea to burn the contents of this directory to CD now.
• $ cd companion
  
• $ ./runInstaller
• Welcome:
• [Next]
• Select a Product to Install:
• Oracle Database 10g Products 10.2.0.1.0
• [Next]
  
• Specify Home Details:
• Name: OraDb10g_home1
• Path: /u01/app/oracle/10.2.0.1/db01
• [Next]
  
• Product-Specific Prerequisite Checks:
• [Next]
• Summary:
• [Install]
• End of Installation:
• Two URLs are listed. Make a note of these.
• [Exit]
• Exit:
• [Yes]
• Install the software for the instance whs:
• In OEM Console:
• Expand instance -> Instance
• -Configuration-
• General tab:
• [All Initialisation Parameters]
• Ensure the following parameters:
• aq_tm_processes: 2
• joq_queue_processes: 10
• [Apply]
• In Terminal:
• $ cd $ORACLE_HOME/wf/install
• $ export ORACLE_SID=whs
  
• $ ./wfinstall.csh
• Oracle Workflow Configuration Assistant:
• Install Option: -Server Only-
  
• Workflow Account: owf_mgr
• Workflow Password: *********
  
• SYS Password: *********
• TNS Connect Descriptor: lisa:1521:whs
• [Submit]
• Popup: [OK]
• In OEM Console:
• Add the following privileges to the OWF_MGR user:
• CREATE DATABASE LINK
• EXECUTE ANY PROCEDURE
  

Testing:

Extensive testing will be done when there are process flows to deploy.

PL/SQL Package Test Script

Paste this code into a .sql script and run it in SQL*PLUS by the owner of the target schema.

SET SERVEROUTPUT ON SIZE 1000000; -- Test pk_package: DECLARE l_... l_passed BOOLEAN := TRUE; BEGIN dbms_output.put_line('-'); dbms_output.put_line('FUNCTION function test start:'); dbms_output.put_line('-'); dbms_output.put_line('Initialise:'); -- Initialisation code. ... dbms_output.put_line ('Test 1: Pass ...: Description.'); dbms_output.put_line ('Expected result: ...'); l_... := pk_package...(...); dbms_output.put_line('Result = ' || l_...); IF l_... = ... THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line('FUNCTION function test end:'); dbms_output.put_line('-'); dbms_output.put_line('-'); dbms_output.put_line('PROCEDURE procedure test start:'); dbms_output.put_line('-'); dbms_output.put_line ('Test 1: Pass ...: Description.'); dbms_output.put_line ('Expected result: ...'); pk_package...(l..); dbms_output.put_line('Result = ' || l_...); IF l_... = ... THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line('PROCEDURE procedure test end:'); dbms_output.put_line('-'); dbms_output.put_line('Clean up:'); -- Clean up code. ... dbms_output.put_line IF l_passed =TRUE THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); END IF; dbms_output.put_line('-'); END; /

PL/SQL Package Header

This template is used as a header for PL/SQL Packages:

-- ------------------------------------------------------------------------- -- -- -- -- Title: pk_package.sql -- -- Author: Steve Roach -- -- -- -- Description: A package for... -- -- -- -- Functions: function -- -- ... -- -- -- -- Procedures: procedure -- -- ... -- -- -- -- 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. -- -- -- -- ------------------------------------------------------------------------- -- -- Set flags for running debug mode: ALTER SESSION SET PLSQL_CCFLAGS = 'debug: FALSE'; -- Package definition. CREATE OR REPLACE PACKAGE pk_package AS FUNCTION function( ... ... ) RETURN ...; PROCEDURE procedure( ... ... ); END pk_package; / SHOW ERRORS CREATE OR REPLACE PACKAGE BODY pk_package AS -- Description... FUNCTION function( ... ... ) RETURN ... IS ... BEGIN $IF $$debug $THEN dbms_output.put_line('function START'); $END NULL; ... $IF $$debug $THEN dbms_output.put_line('RETURN = ' || ...); dbms_output.put_line('function END'); $END RETURN ...; END function; -- Description... PROCEDURE procedure( ... ... ) IS ... BEGIN $IF $$debug $THEN dbms_output.put_line('procedure START'); $END NULL; ... $IF $$debug $THEN dbms_output.put_line('procedure END'); $END END procedure; END pk_package; / SHOW ERRORS

Test Script

The Control Repository

This post contains links to other posts which document the Control Repository.

• Functional Specification
• Technical Specification
• Configuration Items
• Implementation Notes

Number PL/SQL Library

Pre-Requisites:

None.

Code:

Paste this code into a .sql script and run it in SQL*PLUS by the owner of the target schema.

-- ------------------------------------------------------------------------- -- -- -- -- Title: lib_number.sql -- -- Author: Steve Roach -- -- -- -- Description: A set of library functions and procedures to support number -- -- manipulation. -- -- -- -- Functions: cardinal_num -- -- Return the cardinal string equivalent of the input -- -- number. -- -- -- -- 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. -- -- -- -- ------------------------------------------------------------------------- -- -- Set flags for running debug mode: ALTER SESSION SET PLSQL_CCFLAGS = 'debug: FALSE'; -- Package definition. CREATE OR REPLACE PACKAGE lib_number AS FUNCTION cardinal_num( p_number NUMBER) RETURN VARCHAR2; END lib_number; / SHOW ERRORS CREATE OR REPLACE PACKAGE BODY lib_number AS -- Return the cardinal string equivalent of the input -- number. FUNCTION cardinal_num( p_number NUMBER) RETURN VARCHAR2 IS l_cardinal VARCHAR2(4000); l_last_digit VARCHAR2(1); BEGIN $IF $$debug $THEN dbms_output.put_line('cardinal_num START'); $END l_cardinal := TO_CHAR(p_number); l_last_digit := SUBSTR(l_cardinal, LENGTH(l_cardinal - 1)); CASE WHEN l_last_digit = '1' THEN l_cardinal := l_cardinal || 'st'; WHEN l_last_digit = '2' THEN l_cardinal := l_cardinal || 'nd'; WHEN l_last_digit = '3' THEN l_cardinal := l_cardinal || 'rd'; ELSE l_cardinal := l_cardinal || 'th'; END CASE; $IF $$debug $THEN dbms_output.put_line('RETURN = ' || l_cardinal); dbms_output.put_line('cardinal_num END'); $END RETURN l_cardinal; END cardinal_num; END lib_number; / SHOW ERRORS

Number PL/SQL Library Test Script

Paste this code into a .sql script and run it in SQL*PLUS by the owner of the target schema.

SET SERVEROUTPUT ON SIZE 1000000; -- Test p_number_lib: DECLARE l_string VARCHAR2(100); l_passed BOOLEAN := TRUE; BEGIN dbms_output.put_line('-'); dbms_output.put_line('FUNCTION cardinal_num test start:'); dbms_output.put_line('-'); dbms_output.put_line ('Test 1: Pass 1: Example.'); dbms_output.put_line ('Expected result: "1st"'); l_string := lib_number.cardinal_num(1); dbms_output.put_line('Result = ' || l_string); IF l_string = '1st' THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line ('Test 2: Pass 22: Example.'); dbms_output.put_line ('Expected result: "22nd"'); l_string := lib_number.cardinal_num(22); dbms_output.put_line('Result = ' || l_string); IF l_string = '22nd' THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line ('Test 3: Pass 9753: Example.'); dbms_output.put_line ('Expected result: "9753rd"'); l_string := lib_number.cardinal_num(9753); dbms_output.put_line('Result = ' || l_string); IF l_string = '9753rd' THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line ('Test 4: Pass 24680: Example.'); dbms_output.put_line ('Expected result: "24680th"'); l_string := lib_number.cardinal_num(24680); dbms_output.put_line('Result = ' || l_string); IF l_string = '24680th' THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line('FUNCTION cardinal_num test end:'); IF l_passed =TRUE THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); END IF; dbms_output.put_line('-'); END; /

PL/SQL Library Test Script

Paste this code into a .sql script and run it in SQL*PLUS by the owner of the target schema.

SET SERVEROUTPUT ON SIZE 1000000; -- Test p_script_lib: DECLARE l_... l_passed BOOLEAN := TRUE; BEGIN dbms_output.put_line('-'); dbms_output.put_line('FUNCTION function test start:'); dbms_output.put_line('-'); dbms_output.put_line ('Test 1: Pass ...: Description.'); dbms_output.put_line ('Expected result: ...'); l_... := p_..._lib....(...); dbms_output.put_line('Result = ' || l_...); IF l_string = ... THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line('FUNCTION function test end:'); IF l_passed =TRUE THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); END IF; dbms_output.put_line('-'); END; /

Grey Screen Template

Test script goes here

Time / Date PL/SQL Library Test Script

Paste this code into a .sql script and run it in SQL*PLUS by the owner of the target schema.

SET SERVEROUTPUT ON SIZE 1000000; -- Test lib_time: DECLARE l_date DATE; l_week NUMBER; l_month NUMBER; l_qtr NUMBER; l_year NUMBER; l_passed BOOLEAN := TRUE; BEGIN dbms_output.put_line('-'); dbms_output.put_line('FUNCTION calendar_week test start:'); dbms_output.put_line('-'); dbms_output.put_line ('Test 1: Passing 12-JAN-2005. 1st Jan is a Saturday.'); dbms_output.put_line ('Expected result: Returns 3'); l_week := lib_time.calendar_week('12-JAN-2005'); dbms_output.put_line('Result = ' || l_week); IF l_week = 3 THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line ('Test 2: Passing 11-JAN-2006. 1st Jan is a Sunday.'); dbms_output.put_line ('Expected result: Returns 3'); l_week := lib_time.calendar_week('11-JAN-2006'); dbms_output.put_line('Result = ' || l_week); IF l_week = 3 THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line ('Test 3: Passing 10-JAN-2007. 1st Jan is a Monday.'); dbms_output.put_line ('Expected result: Returns 2'); l_week := lib_time.calendar_week('10-JAN-2007'); dbms_output.put_line('Result = ' || l_week); IF l_week = 2 THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line ('Test 4: Passing 09-JAN-2008. 1st Jan is a Tuesday.'); dbms_output.put_line ('Expected result: Returns 2'); l_week := lib_time.calendar_week('09-JAN-2008'); dbms_output.put_line('Result = ' || l_week); IF l_week = 2 THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line('FUNCTION calendar_week test end:'); dbms_output.put_line('-'); dbms_output.put_line('FUNCTION calendar_qtr test start:'); dbms_output.put_line('-'); dbms_output.put_line ('Test 1: Passing 12-JAN-2005.'); dbms_output.put_line ('Expected result: Returns 1'); l_qtr := lib_time.calendar_qtr('12-JAN-2005'); dbms_output.put_line('Result = ' || l_qtr); IF l_qtr = 1 THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line ('Test 2: Passing 12-OCT-2005.'); dbms_output.put_line ('Expected result: Returns 4'); l_qtr := lib_time.calendar_qtr('12-OCT-2005'); dbms_output.put_line('Result = ' || l_qtr); IF l_qtr = 4 THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line('FUNCTION calendar_qtr test end:'); dbms_output.put_line('-'); dbms_output.put_line('FUNCTION financial_week test start:'); dbms_output.put_line('-'); dbms_output.put_line ('Test 1: Passing 12-JUL-2006. 1st Jul is a Saturday.'); dbms_output.put_line ('Expected result: Returns 3'); l_week := lib_time.financial_week('12-JUL-2006'); dbms_output.put_line('Result = ' || l_week); IF l_week = 3 THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line ('Test 2: Passing 11-JUL-2007. 1st Jul is a Sunday.'); dbms_output.put_line ('Expected result: Returns 3'); l_week := lib_time.financial_week('11-JUL-2007'); dbms_output.put_line('Result = ' || l_week); IF l_week = 3 THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line ('Test 3: Passing 10-JUL-2013. 1st Jul is a Monday.'); dbms_output.put_line ('Expected result: Returns 2'); l_week := lib_time.financial_week('10-JUL-2013'); dbms_output.put_line('Result = ' || l_week); IF l_week = 2 THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line ('Test 4: Passing 09-JUL-2014. 1st Jul is a Tuesday.'); dbms_output.put_line ('Expected result: Returns 2'); l_week := lib_time.financial_week('09-JUL-2014'); dbms_output.put_line('Result = ' || l_week); IF l_week = 2 THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line ('Test 5: Passing 22-JUN-2006. 1st Jul in previous calendar year.'); dbms_output.put_line ('Expected result: Returns 52'); l_week := lib_time.financial_week('22-JUN-2006'); dbms_output.put_line('Result = ' || l_week); IF l_week = 52 THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line('FUNCTION financial_week test end:'); dbms_output.put_line('-'); dbms_output.put_line('FUNCTION financial_month test start:'); dbms_output.put_line('-'); dbms_output.put_line ('Test 1: Passing 12-APR-2006. In first half of calendar year.'); dbms_output.put_line ('Expected result: Returns 10'); l_month := lib_time.financial_month('12-APR-2006'); dbms_output.put_line('Result = ' || l_month); IF l_month = 10 THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line ('Test 2: Passing 12-SEP-2006. In second half of calendar year.'); dbms_output.put_line ('Expected result: Returns 3'); l_month := lib_time.financial_month('12-SEP-2006'); dbms_output.put_line('Result = ' || l_month); IF l_month = 3 THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line('FUNCTION financial_month test end:'); dbms_output.put_line('-'); dbms_output.put_line('FUNCTION financial_qtr test start:'); dbms_output.put_line('-'); dbms_output.put_line ('Test 1: Passing 12-JAN-2005.'); dbms_output.put_line ('Expected result: Returns 3'); l_qtr := lib_time.financial_qtr('12-JAN-2005'); dbms_output.put_line('Result = ' || l_qtr); IF l_qtr = 3 THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line ('Test 2: Passing 12-OCT-2005.'); dbms_output.put_line ('Expected result: Returns 2'); l_qtr := lib_time.financial_qtr('12-OCT-2005'); dbms_output.put_line('Result = ' || l_qtr); IF l_qtr = 2 THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line('FUNCTION financial_qtr test end:'); dbms_output.put_line('-'); dbms_output.put_line('FUNCTION financial_year test start:'); dbms_output.put_line('-'); dbms_output.put_line ('Test 1: Passing 12-JAN-2005.'); dbms_output.put_line ('Expected result: Returns 2005'); l_year := lib_time.financial_year('12-JAN-2005'); dbms_output.put_line('Result = ' || l_year); IF l_year = 2005 THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line ('Test 2: Passing 12-OCT-2005.'); dbms_output.put_line ('Expected result: Returns 2006'); l_year := lib_time.financial_year('12-OCT-2005'); dbms_output.put_line('Result = ' || l_year); IF l_year = 2006 THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); l_passed := FALSE; END IF; dbms_output.put_line('-'); dbms_output.put_line('FUNCTION financial_qtr test end:'); dbms_output.put_line('-'); IF l_passed =TRUE THEN dbms_output.put_line('---PASSED---'); ELSE dbms_output.put_line('---FAILED---'); END IF; dbms_output.put_line('-'); END; /

Time / Date PL/SQL Library

Pre-Requisites:

None.

Code:

Paste this code into a .sql script and run it in SQL*PLUS by the owner of the target schema.

-- ------------------------------------------------------------------------- -- -- -- -- Title: lib_time.sql -- -- Author: Steve Roach -- -- -- -- Description: A set of library functions and procedures to support time, -- -- date and period functionality. -- -- -- -- Functions: calendar_week -- -- Returns the number of the calendar week in the year that -- -- a date falls in. -- -- calendar_qtr -- -- Returns the number of the calendar quarter in the year -- -- that a date falls in. -- -- financial_week (Australia) -- -- Returns the number of the financial week in the -- -- Australian financial year (1st July - 30th June) year -- -- that a date falls in. -- -- financial_month (Australia) -- -- Returns the number of the financial month in the -- -- Australian financial year (1st July - 30th June) year -- -- that a date falls in. -- -- financial_qtr (Australia) -- -- Returns the number of the financial quarter in the -- -- Australian financial year (1st July - 30th June) year -- -- that a date falls in. -- -- financial_year (Australia) -- -- Returns the number of the financial year in the -- -- Australian financial year (1st July - 30th June) year -- -- that a date falls in. -- -- -- -- 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. -- -- -- -- ------------------------------------------------------------------------- -- -- Set flags for running debug mode: ALTER SESSION SET PLSQL_CCFLAGS = 'debug: FALSE'; -- Compile function. CREATE OR REPLACE PACKAGE lib_time AS FUNCTION calendar_week( p_date DATE) RETURN NUMBER; FUNCTION calendar_qtr( p_date DATE) RETURN NUMBER; FUNCTION financial_week( p_date DATE) RETURN NUMBER; FUNCTION financial_month( p_date DATE) RETURN NUMBER; FUNCTION financial_qtr( p_date DATE) RETURN NUMBER; FUNCTION financial_year( p_date DATE) RETURN NUMBER; END lib_time; / SHOW ERRORS CREATE OR REPLACE PACKAGE BODY lib_time AS -- Returns the number of the calendar week in the year that -- a date falls in. FUNCTION calendar_week( p_date DATE) RETURN NUMBER IS l_start_date DATE; l_day_count NUMBER; l_date_span NUMBER := 0; l_week_number NUMBER := 1; BEGIN $IF $$debug $THEN dbms_output.put_line('calendar_week START'); $END l_start_date := TO_DATE('02-JAN-' || TO_CHAR(p_date, 'YYYY')); l_date_span := p_date - l_start_date; $IF $$debug $THEN dbms_output.put_line('p_date = ' || p_date); dbms_output.put_line('l_start_date = ' || l_start_date); dbms_output.put_line('l_date_span = ' || l_date_span); dbms_output.put_line('-'); $END FOR l_day_count IN 0 .. l_date_span LOOP $IF $$debug $THEN dbms_output.put_line('l_day_count = ' || l_day_count); dbms_output.put_line('IF criteria = ' || TO_CHAR(l_start_date + l_day_count, 'DY')); dbms_output.put_line('-'); $END IF TO_CHAR(l_start_date + l_day_count, 'DY') = 'MON' THEN l_week_number := l_week_number + 1; $IF $$debug $THEN dbms_output.put_line('l_week_number = ' || l_week_number); $END END IF; END LOOP; $IF $$debug $THEN dbms_output.put_line('RETURN = ' || l_week_number); dbms_output.put_line('calendar_week END'); dbms_output.put_line('-'); $END RETURN l_week_number; END calendar_week; -- Returns the number of the calendar quarter in the year -- that a date falls in. FUNCTION calendar_qtr( p_date DATE) RETURN NUMBER IS l_qtr NUMBER; BEGIN l_qtr := CASE TO_CHAR(p_date - 1, 'MM') WHEN '01' THEN 1 WHEN '02' THEN 1 WHEN '03' THEN 1 WHEN '04' THEN 2 WHEN '05' THEN 2 WHEN '06' THEN 2 WHEN '07' THEN 3 WHEN '08' THEN 3 WHEN '09' THEN 3 WHEN '10' THEN 4 WHEN '11' THEN 4 ELSE 4 END; RETURN l_qtr; END; -- Returns the number of the financial week in the -- Australian financial year (1st July - 30th June) year -- that a date falls in. FUNCTION financial_week( p_date DATE) RETURN NUMBER IS l_start_date DATE; l_fin_year_start_year NUMBER; l_day_count NUMBER; l_date_span NUMBER := 0; l_week_number NUMBER := 1; BEGIN $IF $$debug $THEN dbms_output.put_line('financial_week START'); dbms_output.put_line('p_date = ' || p_date); $END l_fin_year_start_year := TO_NUMBER(TO_CHAR(p_date, 'YYYY')); $IF $$debug $THEN dbms_output.put_line('l_fin_year_start_year = ' || l_fin_year_start_year); dbms_output.put_line('TO_CHAR(p_date, ''MM'') = ' || TO_CHAR(p_date, 'MM')); dbms_output.put_line( 'lib_string.ltrim_chr(TO_CHAR(p_date, ''MM''), ''0'') = ' || lib_string.ltrim_chr(TO_CHAR(p_date, 'MM'), '0')); $END IF TO_NUMBER(lib_string.ltrim_chr(TO_CHAR(p_date, 'MM'), '0')) < 7 THEN l_fin_year_start_year := l_fin_year_start_year - 1; END IF; l_start_date := TO_DATE('02-JUL-' || TO_CHAR(l_fin_year_start_year)); l_date_span := p_date - l_start_date; $IF $$debug $THEN dbms_output.put_line('p_date = ' || p_date); dbms_output.put_line('l_fin_year_start_year = ' || l_fin_year_start_year); dbms_output.put_line('l_start_date = ' || l_start_date); dbms_output.put_line('l_date_span = ' || l_start_date); $END FOR l_day_count IN 0 .. l_date_span LOOP $IF $$debug $THEN dbms_output.put_line('l_day_count = ' || l_day_count); dbms_output.put_line('IF criteria = ' || TO_CHAR(l_start_date + l_day_count, 'DY')); $END IF TO_CHAR(l_start_date + l_day_count, 'DY') = 'MON' THEN l_week_number := l_week_number + 1; END IF; END LOOP; $IF $$debug $THEN dbms_output.put_line('RETURN = ' || l_week_number); dbms_output.put_line('calendar_week END'); $END RETURN l_week_number; END financial_week; -- Returns the number of the financial month in the -- Australian financial year (1st July - 30th June) year -- that a date falls in. FUNCTION financial_month( p_date DATE) RETURN NUMBER IS l_month NUMBER; BEGIN l_month := TO_NUMBER(lib_string.ltrim_chr(TO_CHAR(p_date - 1, 'MM'), '0')); IF l_month > 6 THEN RETURN l_month - 6; ELSE RETURN l_month + 6; END IF; END; -- Returns the number of the financial quarter in the -- Australian financial year (1st July - 30th June) year -- that a date falls in. FUNCTION financial_qtr( p_date DATE) RETURN NUMBER IS l_qtr NUMBER; BEGIN l_qtr := CASE TO_CHAR(p_date - 1, 'MM') WHEN '01' THEN 3 WHEN '02' THEN 3 WHEN '03' THEN 3 WHEN '04' THEN 4 WHEN '05' THEN 4 WHEN '06' THEN 4 WHEN '07' THEN 1 WHEN '08' THEN 1 WHEN '09' THEN 1 WHEN '10' THEN 2 WHEN '11' THEN 2 ELSE 2 END; RETURN l_qtr; END; -- Returns the number of the financial year in the -- Australian financial year (1st July - 30th June) year -- that a date falls in. FUNCTION financial_year( p_date DATE) RETURN NUMBER IS BEGIN IF TO_NUMBER(lib_string.ltrim_chr(TO_CHAR(p_date, 'MM'), '0')) > 6 THEN RETURN TO_NUMBER(TO_CHAR(p_date, 'YYYY')) + 1; ELSE RETURN TO_NUMBER(TO_CHAR(p_date, 'YYYY')); END IF; END; END lib_time; / SHOW ERRORS