-- ------------------------------------------------------------------------- --
-- --
-- Title: m_o2w_countries_b.sql --
-- Author: Steve Roach --
-- Date: 30-SEP-2006 --
-- Version: 01.00 --
-- --
-- Description: Container for all procedures and functions relating to --
-- mapping data for the Countries Dimension from ODS to WHS. --
-- --
-- History: --
-- --
-- Date Ver Author Description --
-- --------- ----- ------ -------------------------------------------------- --
-- 30-SEP-06 01.00 SRR First release --
-- --
-- ------------------------------------------------------------------------- --
-- Package body definition.
CREATE OR REPLACE PACKAGE BODY m_o2w_countries
AS
-- map Overload 1
-- Wrapper to do mapping with housekeeping.
PROCEDURE map
IS
l_run_no NUMBER;
BEGIN
-- Run initialise mapping with parameters p_mapping = 'M_O2W_COUNTRIES',
-- run_no = local variable to capture the run number.
ctl_gen.initialise_mapping('M_O2W_COUNTRIES', l_run_no);
-- Run map (overload 2) with parameter p_run_no = the captured run
-- number.
map(l_run_no);
-- Run finalise mapping with parameter run_no = the captured run number.
ctl_gen.finalise_mapping(l_run_no);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,
'm_o2w_countries.map(1) Error: [' || SQLERRM || '].');
END map; -- Overload 1
-- map Overload 2
-- Map ODS.COUNTRIES to WHS.D_COUNTRIES.
PROCEDURE map
(
p_run_no IN NUMBER
)
IS
BEGIN
-- Insert into ODS.COUNTRIES any country in VAL.V_COUNTRIES that is not
-- already there.
-- dbms_output.put_line('map(2): Inserting into D_COUNTRY...');
INSERT INTO d_country
(
id
,name
,iso2_code
,iso3_code
,un3_number
,start_date
,end_date
,run_no
,ins_tsp
)
SELECT d_country_s.NEXTVAL
,name
,NVL(iso2_code, '--')
,NVL(iso3_code, '---')
,NVL(un3_number, '---')
,start_date
,end_date
,p_run_no
,SYSDATE
FROM o_countries
WHERE name NOT IN
(
SELECT name
FROM d_country
);
-- dbms_output.put_line('map(2): Inserting into D_COUNTRY...DONE');
COMMIT;
-- Update and rows in D_COUNTRIES that differ in detail from COUNTRIES.
-- dbms_output.put_line('map(2): Updating D_COUNTRY...');
UPDATE d_country dc SET
(
iso2_code
,iso3_code
,un3_number
,start_date
,end_date
,run_no
,ins_tsp
)
=
(
SELECT NVL(oc.iso2_code, '--')
,NVL(oc.iso3_code, '---')
,NVL(oc.un3_number, '---')
,oc.start_date
,oc.end_date
,p_run_no
,SYSDATE
FROM o_countries oc
WHERE oc.name = dc.name
)
WHERE
(
dc.name IN
(
SELECT dc.name
FROM d_country dc
,o_countries oc
WHERE dc.name = oc.name
AND (
NVL(oc.iso2_code, '--') != dc.iso2_code
OR
NVL(oc.iso3_code, '---') != dc.iso3_code
OR
NVL(oc.un3_number, '--') != dc.un3_number
OR
oc.start_date != dc.start_date
OR
oc.end_date != dc.end_date
)
)
);
-- dbms_output.put_line('map(2): Updating D_COUNTRY...DONE');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,
'm_o2w_countries.map(2) Error: [' || SQLERRM || '].');
END map; -- Overload 2
END m_o2w_countries;
/
SHOW ERRORS
|
0 Comments:
Post a Comment
<< Home