Country Dimension - m_v2o_countries_b.sql

-- ------------------------------------------------------------------------- -- -- -- -- Title: m_v2o_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 VAL to ODS. -- -- -- -- History: -- -- -- -- Date Ver Author Description -- -- --------- ----- ------ -------------------------------------------------- -- -- 30-SEP-06 01.00 SRR First release -- -- -- -- ------------------------------------------------------------------------- -- -- Package body definition. CREATE OR REPLACE PACKAGE BODY m_v2o_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_V2O_COUNTRIES', -- run_no = local variable to capture the run number. ctl_gen.initialise_mapping('M_V2O_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_v2o_countries.map(1) Error: [' || SQLERRM || '].'); END map; -- Overload 1 -- map Overload 2 -- Map VAL.V_COUNTRIES to ODS.O_COUNTRIES. PROCEDURE map ( p_run_no IN NUMBER ) IS BEGIN -- Insert into ODS.O_COUNTRIES any country in VAL.V_COUNTRIES that is not -- already there. INSERT INTO o_countries ( name ,iso2_code ,iso3_code ,un3_number ,start_date ,end_date ,run_no ,ins_tsp ) SELECT name ,iso2_code ,iso3_code ,un3_number ,'01-JAN-1000' ,'31-DEC-3000' ,p_run_no ,SYSDATE FROM v_countries WHERE name NOT IN ( SELECT name FROM o_countries ); COMMIT; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001, 'm_v2o_countries.map(2) Error: [' || SQLERRM || '].'); END map; -- Overload 2 END m_v2o_countries; / SHOW ERRORS