CREATE OR REPLACE PROCEDURE dw.load_cust_hier_bkp2 AS v_in_cnt NUMBER; v_out_cnt NUMBER; v_tot_in NUMBER := 0; v_tot_out NUMBER := 0; v_updt_cnt NUMBER; v_dup_cnt NUMBER; v_commit_rows NUMBER := 10000; v_commit_cnt NUMBER := 10000; v_begin_dt DATE; v_end_dt DATE; v_last_mth_dt DATE; --last bus month END DATE v_last_day_dt DATE; --last bus day END DATE first_rec BOOLEAN := TRUE; doing_both BOOLEAN := FALSE; mpg_only BOOLEAN := FALSE; v_next_row NUMBER; last_targ NUMBER; last_rec BOOLEAN; no_targ_found BOOLEAN; write_dup BOOLEAN; v_orig_from_dt DATE; v_orig_to_dt DATE; v_from_yr NUMBER (4); v_to_yr NUMBER (4); v_yr_diff NUMBER (4); v_sorg_cd cust_hier_bkp2.sorg_cd%TYPE; v_cust_cd cust_hier_bkp2.cust_cd%TYPE; v_bunt cust_hier_bkp2.bunt%TYPE; --DP v_div cust_hier_bkp2.div%TYPE; v_from_dt cust_hier_bkp2.from_dt%TYPE; -- source from_dt v_to_dt cust_hier_bkp2.to_dt%TYPE; -- source to_dt v_from_dt_orig cust_hier_bkp2.from_dt%TYPE; -- original from_dt v_to_dt_orig cust_hier_bkp2.to_dt%TYPE; -- original to_dt t_from_dt cust_hier_bkp2.from_dt%TYPE; -- target from_dt t_to_dt cust_hier_bkp2.to_dt%TYPE; -- target to_dt v_ter cust_hier_bkp2.cust_ter_cd%TYPE; v_rgn cust_hier_bkp2.cust_rgn_cd%TYPE; v_grp cust_hier_bkp2.cust_grp_cd%TYPE; v_area cust_hier_bkp2.cust_area_cd%TYPE; v_mkt cust_hier_bkp2.cust_mkt_cd%TYPE; v_sorg_desc cust_hier_bkp2.sorg_desc%TYPE; v_cust_nm cust_hier_bkp2.cust_nm%TYPE; v_cust_ter_desc cust_hier_bkp2.cust_ter_desc%TYPE; v_rgn_desc cust_hier_bkp2.cust_rgn_desc%TYPE; v_grp_desc cust_hier_bkp2.cust_grp_desc%TYPE; v_area_desc cust_hier_bkp2.cust_area_desc%TYPE; v_mkt_desc cust_hier_bkp2.cust_mkt_desc%TYPE; v_curr_flag cust_hier_bkp2.curr_flag%TYPE; v_last_mth_flag cust_hier_bkp2.last_mth_flag%TYPE; v_asgn_typ cust_hier_bkp2.asgn_typ%TYPE; v_plan_cd cust_hier_bkp2.comp_plan_cd%TYPE; v_plan_nm cust_hier_bkp2.comp_plan_nm%TYPE; v_plan_id cust_hier_bkp2.comp_plan_id%TYPE; v_mpg mpg_div_s004.mpg_id%TYPE; v_src_values VARCHAR2 (2000); v_prev_values VARCHAR2 (2000); /* Error handling variables */ v_error_code NUMBER; v_error_message VARCHAR2 (200); CURSOR c_mpg IS SELECT /*+ ALL_ROWS */ DISTINCT sorg_cd, mpg, bunt, --DP bunt_orig, -- ES/CS 5/2002 div, cust_cd, to_dt, from_dt, cust_rgn_cd, cust_grp_cd, cust_area_cd, cust_nm, cust_rgn_desc, cust_grp_desc, cust_area_desc, sorg_desc, cust_ter_cd, cust_ter_desc, mkt_cd, mkt_desc, 'mpg' asgn_typ, dflt_flag FROM cust_hier_tab z WHERE sorg_cd IN ('S004', 'S096') AND z.mpg IN ('01', '02', '05', '06') --AND z.cust_cd = '27174' ORDER BY z.sorg_cd, z.mpg, z.cust_cd, z.to_dt DESC, z.dflt_flag DESC, z.from_dt DESC; src_rec c_mpg%ROWTYPE; CURSOR c_div_only_mpg IS SELECT /*+ ALL_ROWS */ DISTINCT z.sorg_cd, z.mpg, z.bunt, -- DP z.bunt_orig, -- ES/CS 5/2002 NVL (zd.div_id, 'na') div, cust_cd, to_dt, from_dt, cust_rgn_cd, cust_grp_cd, cust_area_cd, cust_nm, cust_rgn_desc, cust_grp_desc, cust_area_desc, sorg_desc, cust_ter_cd, cust_ter_desc, mkt_cd, mkt_desc, DECODE (z.div, 'na', 'mpg', 'div') asgn_typ, dflt_flag FROM cust_hier_tab z, zrep_mpg_div zd WHERE sorg_cd IN ('S004', 'S096') AND (z.mpg = zd.mpg_id OR z.div = zd.div_id) AND zd.div_id IN ('AB', 'AF', 'PC', 'RD', 'NV', 'VS', 'CS', 'AX', 'VC') AND NOT EXISTS ( SELECT 1 FROM cust_hier_tab c2 WHERE c2.cust_cd = z.cust_cd AND c2.div = zd.div_id) ORDER BY z.sorg_cd, z.cust_cd, z.bunt_orig, -- ES/CS 5/2002 div, asgn_typ, z.to_dt DESC, z.dflt_flag DESC, z.from_dt DESC; CURSOR c_mpg_and_div IS SELECT /*+ ALL_ROWS */ DISTINCT z.sorg_cd, z.mpg, z.bunt, --DP z.bunt_orig, -- ES/CS 5/2002 NVL (zd.div_id, 'na') div, cust_cd, to_dt, from_dt, cust_rgn_cd, cust_grp_cd, cust_area_cd, cust_nm, cust_rgn_desc, cust_grp_desc, cust_area_desc, sorg_desc, cust_ter_cd, cust_ter_desc, mkt_cd, mkt_desc, DECODE (z.div, 'na', 'mpg', 'div') asgn_typ, dflt_flag FROM cust_hier_tab z, zrep_mpg_div zd WHERE sorg_cd IN ('S004', 'S096') AND (z.mpg = zd.mpg_id OR z.div = zd.div_id) AND zd.div_id IN ('AB', 'AF', 'PC', 'RD', 'NV', 'VS', 'CS', 'AX', 'VC') AND EXISTS ( SELECT 1 FROM cust_hier_tab c2 WHERE c2.cust_cd = z.cust_cd AND c2.div = zd.div_id) ORDER BY z.sorg_cd, z.cust_cd, z.bunt_orig, -- ES/CS 5/2002 div, asgn_typ, z.to_dt DESC, z.dflt_flag DESC, z.from_dt DESC; CURSOR c_mpg_div IS SELECT div_id FROM mpg_div_s004 WHERE mpg_id = v_mpg ORDER BY div_id; -- CURSOR c_targ_dates ( p_sorg VARCHAR2, p_cust VARCHAR2, p_div VARCHAR2, p_bunt VARCHAR2 ) --DP IS SELECT /*+ INDEX (cust_hier_bkp2_pk) */ from_dt, to_dt FROM cust_hier_bkp2 WHERE sorg_cd = p_sorg AND bunt = p_bunt --DP AND div = p_div AND cust_cd = p_cust ORDER BY to_dt DESC; TYPE t_targ_dates IS TABLE OF c_targ_dates%ROWTYPE INDEX BY BINARY_INTEGER; targ_tab t_targ_dates; -- -- PROCEDURE do_insert IS -- PROCEDURE process_insert_record IS CURSOR c_es_div_split IS SELECT div_id FROM zrep_mpg_div WHERE div_id IN ('PC', 'BP', 'BI', 'CI', 'CR'); -- PROCEDURE write_record IS CURSOR c_plan_fields IS SELECT x.comp_plan_id, x.comp_plan_cd, cp.comp_plan_nm FROM cp_div_xref@dm x, comp_plan@dm cp WHERE x.comp_plan_id = cp.comp_plan_id AND x.div = v_div AND x.sorg_cd = v_sorg_cd AND x.comp_plan_yr = TO_NUMBER (TO_CHAR (v_to_dt, 'yyyy')); -- BEGIN -- write_record OPEN c_plan_fields; FETCH c_plan_fields INTO v_plan_id, v_plan_cd, v_plan_nm; CLOSE c_plan_fields; INSERT INTO cust_hier_bkp2 (sorg_cd, cust_cd, bunt, --DP div, from_dt, to_dt, cust_ter_cd, cust_rgn_cd, cust_grp_cd, cust_area_cd, sorg_desc, cust_nm, cust_ter_desc, cust_rgn_desc, cust_grp_desc, cust_area_desc, cust_mkt_cd, cust_mkt_desc, curr_flag, last_mth_flag, comp_plan_id, comp_plan_cd, comp_plan_nm, asgn_typ, lddt ) VALUES (v_sorg_cd, v_cust_cd, v_bunt, --DP v_div, TRUNC (v_from_dt), TO_DATE (TO_CHAR (v_to_dt, 'mmddyyyy') || '235959', 'mmddyyyyhh24miss' ), v_ter, v_rgn, v_grp, v_area, v_sorg_desc, v_cust_nm, v_cust_ter_desc, v_rgn_desc, v_grp_desc, v_area_desc, v_mkt, v_mkt_desc, v_curr_flag, v_last_mth_flag, v_plan_id, v_plan_cd, v_plan_nm, v_asgn_typ, v_begin_dt ); v_plan_id := NULL; v_plan_cd := NULL; v_plan_nm := NULL; v_out_cnt := v_out_cnt + 1; IF doing_both THEN COMMIT; ELSE -- commiting v_commit_rows rows at a time. IF v_out_cnt >= v_commit_cnt THEN COMMIT; p.l ( 'Commit point reached: ' || v_out_cnt || 'at: ' || TO_CHAR (SYSDATE, 'mm/dd hh24:mi:ss') ); v_commit_cnt := v_commit_cnt + v_commit_rows; END IF; END IF; END write_record; FUNCTION write_div RETURN BOOLEAN IS return_true_false BOOLEAN; BEGIN IF v_to_dt < TO_DATE ('08012001', 'mmddyyyy') AND (v_div = 'BH' OR v_div = 'TH') THEN -- Start of BH/TH at CRM return_true_false := FALSE; ELSIF v_to_dt < TO_DATE ('10012001', 'mmddyyyy') AND v_div = 'RD' THEN -- Start of RD at USA/VI return_true_false := FALSE; ELSIF v_to_dt < TO_DATE ('01012002', 'mmddyyyy') AND (v_div = 'DD' OR v_div = 'CK') THEN -- Start of DD/CK at VI return_true_false := FALSE; ELSIF v_to_dt < TO_DATE ('12012001', 'mmddyyyy') AND v_div = 'NV' THEN -- Start of NV at ES return_true_false := FALSE; ELSIF v_to_dt < TO_DATE ('01012002', 'mmddyyyy') AND v_div = 'LP' THEN -- Start of LP at ES return_true_false := FALSE; ELSIF v_to_dt < TO_DATE ('01012003', 'mmddyyyy') AND ( v_div = 'AN' OR v_div = 'AX' OR v_div = 'BT' OR v_div = 'FB' OR v_div = 'VH' ) THEN -- Start of AN,AX,BT,FB,VH at CS return_true_false := FALSE; ELSIF v_to_dt < TO_DATE ('01012005', 'mmddyyyy') AND ( v_div = 'CR' OR v_div = 'CI' OR v_div = 'BP' OR v_div = 'BI' OR v_div = 'PM' ) THEN -- Start of CR,CI,BP,BI at ES and PM at CRM return_true_false := FALSE; ELSIF v_to_dt < TO_DATE ('01012005', 'mmddyyyy') AND v_div = 'VC' THEN -- Start of legacy VC at VI/ABT return_true_false := FALSE; ELSE return_true_false := TRUE; END IF; RETURN return_true_false; END write_div; -- -- BEGIN -- process_insert_record --p.l('Start process_insert_record'); IF v_last_day_dt BETWEEN v_from_dt AND v_to_dt THEN v_curr_flag := 1; ELSE v_curr_flag := 0; END IF; IF v_last_mth_dt BETWEEN v_from_dt AND v_to_dt THEN v_last_mth_flag := 1; ELSE v_last_mth_flag := 0; END IF; IF mpg_only THEN FOR v_mpg_div IN c_mpg_div LOOP v_div := v_mpg_div.div_id; IF write_div THEN write_record; END IF; END LOOP; ELSE IF v_div = 'PC' THEN FOR cdiv IN c_es_div_split LOOP v_div := cdiv.div_id; IF write_div THEN write_record; END IF; END LOOP; v_div := 'PC'; ELSE IF write_div THEN write_record; END IF; END IF; END IF; END process_insert_record; -- -- BEGIN -- do_insert v_orig_from_dt := v_from_dt; v_orig_to_dt := v_to_dt; v_from_yr := TO_NUMBER (TO_CHAR (v_from_dt, 'yyyy')); v_to_yr := TO_NUMBER (TO_CHAR (v_to_dt, 'yyyy')); v_yr_diff := v_to_yr - v_from_yr; IF v_yr_diff = 0 THEN process_insert_record; END IF; IF v_yr_diff > 0 THEN -- write first year record v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr), 'mmddyyyy'); process_insert_record; IF v_yr_diff = 1 THEN -- write 2nd year record v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 1), 'mmddyyyy'); v_to_dt := v_orig_to_dt; process_insert_record; ELSIF v_yr_diff = 2 THEN -- write 2nd and 3rd records v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 1), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 1), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 2), 'mmddyyyy'); v_to_dt := v_orig_to_dt; process_insert_record; ELSIF v_yr_diff = 3 THEN -- write 2nd, 3rd and 4th records v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 1), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 1), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 2), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 2), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 3), 'mmddyyyy'); v_to_dt := v_orig_to_dt; process_insert_record; ELSIF v_yr_diff = 4 THEN -- write 2nd, 3rd, 4th and 5th records v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 1), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 1), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 2), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 2), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 3), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 3), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 4), 'mmddyyyy'); v_to_dt := v_orig_to_dt; process_insert_record; ELSIF v_yr_diff = 5 THEN -- write 2nd, 3rd, 4th 5th and 6th records v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 1), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 1), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 2), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 2), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 3), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 3), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 4), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 4), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 5), 'mmddyyyy'); v_to_dt := v_orig_to_dt; process_insert_record; ELSIF v_yr_diff = 6 THEN -- write 2nd, 3rd, 4th 5th 6th 7th records v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 1), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 1), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 2), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 2), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 3), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 3), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 4), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 4), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 5), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 5), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 6), 'mmddyyyy'); v_to_dt := v_orig_to_dt; process_insert_record; ------ ELSIF v_yr_diff = 7 THEN -- write 2nd, 3rd, 4th 5th 6th 7th 8th records v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 1), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 1), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 2), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 2), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 3), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 3), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 4), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 4), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 5), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 5), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 6), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 6), 'mmddyyyy'); -- v_to_dt := v_orig_to_dt; -- Todd: this is wrong, I commented it out. process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 7), 'mmddyyyy'); v_to_dt := v_orig_to_dt; process_insert_record; ------ ELSIF v_yr_diff = 8 THEN -- write 2nd, 3rd, 4th 5th 6th 7th 8th 9th records v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 1), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 1), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 2), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 2), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 3), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 3), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 4), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 4), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 5), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 5), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 6), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 6), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 7), 'mmddyyyy'); v_to_dt := TO_DATE ('1231' || TO_CHAR (v_from_yr + 7), 'mmddyyyy'); process_insert_record; v_from_dt := TO_DATE ('0101' || TO_CHAR (v_from_yr + 8), 'mmddyyyy'); v_to_dt := v_orig_to_dt; process_insert_record; ------ ELSE -- abend with error p.l ('End year: ' || v_to_yr || ' is greater than 2007'); raise_application_error (-20001, 'End Year greater than 2007'); END IF; END IF; v_from_dt := v_orig_from_dt; v_to_dt := v_orig_to_dt; END do_insert; -- -- PROCEDURE save_source -- save source in global variables IS BEGIN v_sorg_cd := src_rec.sorg_cd; v_cust_cd := src_rec.cust_cd; v_mpg := src_rec.mpg; v_bunt := src_rec.bunt; --DP v_div := src_rec.div; v_from_dt := src_rec.from_dt; v_to_dt := src_rec.to_dt; v_from_dt_orig := src_rec.from_dt; v_to_dt_orig := src_rec.to_dt; v_ter := src_rec.cust_ter_cd; v_rgn := src_rec.cust_rgn_cd; v_grp := src_rec.cust_grp_cd; v_area := src_rec.cust_area_cd; v_sorg_desc := src_rec.sorg_desc; v_cust_nm := src_rec.cust_nm; v_cust_ter_desc := src_rec.cust_ter_desc; v_rgn_desc := src_rec.cust_rgn_desc; v_grp_desc := src_rec.cust_grp_desc; v_area_desc := src_rec.cust_area_desc; v_mkt := src_rec.mkt_cd; v_mkt_desc := src_rec.mkt_desc; v_asgn_typ := src_rec.asgn_typ; v_prev_values := v_ter || v_rgn || v_grp || v_area; END save_source; -- -- PROCEDURE clear_keys -- clear key fields IS BEGIN v_sorg_cd := NULL; v_cust_cd := NULL; v_mpg := NULL; v_bunt := NULL; v_div := NULL; v_from_dt := NULL; v_to_dt := NULL; v_from_dt_orig := NULL; v_to_dt_orig := NULL; END clear_keys; -- -- PROCEDURE fill_targ_tab ( sorg_in IN cust_hier_bkp2.sorg_cd%TYPE, cust_in IN cust_hier_bkp2.cust_cd%TYPE, div_in IN cust_hier_bkp2.div%TYPE, bunt_in IN cust_hier_bkp2.bunt%TYPE --DP ) IS BEGIN targ_tab.DELETE; no_targ_found := TRUE; last_targ := 0; FOR targ_dates_rec IN c_targ_dates (sorg_in, cust_in, div_in, bunt_in) --DP LOOP --p.l('Found a targ record'); no_targ_found := FALSE; v_next_row := NVL (targ_tab.LAST, 0) + 1; targ_tab (v_next_row) := targ_dates_rec; END LOOP; last_targ := targ_tab.LAST; END fill_targ_tab; -- -- PROCEDURE process_new_record IS BEGIN fill_targ_tab (v_sorg_cd, v_cust_cd, v_div, v_bunt); --DP IF no_targ_found THEN do_insert; ELSE -- possibly add but do not over-ride last_rec := FALSE; FOR i IN 1 .. last_targ LOOP IF i = last_targ THEN last_rec := TRUE; -- just for readability later END IF; t_from_dt := targ_tab (i).from_dt; t_to_dt := targ_tab (i).to_dt; IF v_to_dt > t_to_dt THEN IF v_from_dt > t_to_dt THEN do_insert; EXIT; ELSIF v_from_dt >= t_from_dt THEN v_from_dt:= TRUNC (t_to_dt) + 1; do_insert; EXIT; ELSE v_from_dt := TRUNC (t_to_dt) + 1; do_insert; v_to_dt := TRUNC (t_from_dt) - 1 / (24 * 60 * 60); v_from_dt := v_from_dt_orig; IF last_rec THEN do_insert; ELSE NULL; END IF; END IF; ELSIF v_from_dt >= t_from_dt THEN EXIT; ELSE IF v_to_dt >= t_from_dt THEN v_to_dt := TRUNC (t_from_dt) - 1 / (24 * 60 * 60); IF last_rec THEN do_insert; ELSE NULL; END IF; ELSE IF last_rec THEN do_insert; ELSE NULL; END IF; END IF; END IF; -- starting if END LOOP; -- targ_tab loop END IF; -- no_dirs_found END process_new_record; -- -- BEGIN v_begin_dt := SYSDATE; v_in_cnt := 0; v_out_cnt := 0; SELECT d_val INTO v_last_day_dt FROM gp_parms WHERE KEY = 'end_of_business' AND parm = 'day'; SELECT d_val INTO v_last_mth_dt FROM gp_parms WHERE KEY = 'end_of_business' AND parm = 'month'; truncate_table ('cust_hier_bkp2'); INSERT INTO cust_hier_bkp2 (sorg_cd, bunt, --DP div, from_dt, to_dt, cust_cd, cust_ter_cd, cust_rgn_cd, cust_grp_cd, cust_area_cd, cust_nm, cust_ter_desc, cust_rgn_desc, cust_grp_desc, cust_area_desc, sorg_desc, cust_mkt_cd, cust_mkt_desc, comp_plan_id, comp_plan_cd, comp_plan_nm, curr_flag, last_mth_flag, asgn_typ, lddt ) VALUES ('S004', 'na', --DP 'na', TO_DATE ('01011900', 'mmddyyyy'), TO_DATE ('12319999235959', 'mmddyyyyhh24miss'), 'na', 'na', 'na', 'na', 'na', 'Not Known', 'Not Known', 'Not Known', 'Not Known', 'Not Known', 'US Sales Org', 'USA', 'United States', 0, 'na', 'Missing', 1, 1, 'div', SYSDATE ); COMMIT; -- doing_both := TRUE; mpg_only := FALSE; p.l ( 'Start CUSTs with both MPG and DIV assignments at: ' || TO_CHAR (SYSDATE, 'mm/dd/yyyy hh24:mi:ss') ); p.l (' '); first_rec := TRUE; clear_keys; OPEN c_mpg_and_div; LOOP FETCH c_mpg_and_div INTO src_rec; EXIT WHEN c_mpg_and_div%NOTFOUND; v_in_cnt := v_in_cnt + 1; --write_dup := TRUE; -- save_source; process_new_record; END LOOP; COMMIT; CLOSE c_mpg_and_div; p.l ( 'Customers with both MPG and DIV assignments done: ' || TO_CHAR (SYSDATE, 'mm/dd/yyyy hh24:mi:ss') ); p.l ('Input records: ' || v_in_cnt || ', Output records: ' || v_out_cnt); v_tot_in := v_tot_in + v_in_cnt; v_tot_out := v_tot_out + v_out_cnt; v_in_cnt := 0; v_out_cnt := 0; p.l ( 'Total Input records: ' || v_tot_in || ', Total Output records: ' || v_tot_out ); p.l ('Input/Output ratio: ' || TO_CHAR (v_tot_in / v_tot_out, '999.99')); p.l (' '); -- -- doing_both := FALSE; mpg_only := FALSE; p.l (' '); p.l ( 'Start sequencial loop processing at: ' || TO_CHAR (SYSDATE, 'mm/dd/yyyy hh24:mi:ss') ); first_rec := TRUE; clear_keys; OPEN c_div_only_mpg; LOOP FETCH c_div_only_mpg INTO src_rec; -- Process last record IF c_div_only_mpg%NOTFOUND AND NOT first_rec THEN do_insert; END IF; EXIT WHEN c_div_only_mpg%NOTFOUND; v_in_cnt := v_in_cnt + 1; IF src_rec.sorg_cd = v_sorg_cd --src_key = prev_key AND src_rec.cust_cd = v_cust_cd AND src_rec.bunt = v_bunt --DP AND src_rec.div = v_div THEN v_src_values := src_rec.cust_ter_cd || src_rec.cust_rgn_cd || src_rec.cust_grp_cd || src_rec.cust_area_cd; IF v_src_values = v_prev_values THEN IF src_rec.from_dt <= v_from_dt THEN v_from_dt := src_rec.from_dt; END IF; ELSE IF src_rec.from_dt < v_from_dt THEN do_insert; src_rec.to_dt := TRUNC (v_from_dt) - 1 / (24 * 60 * 60); save_source; END IF; END IF; ELSE IF first_rec THEN first_rec := FALSE; ELSE do_insert; END IF; save_source; END IF; -- src_key = prev_key END LOOP; -- rec in c_div_only_mpg cursor loop COMMIT; CLOSE c_div_only_mpg; p.l ( 'Division assign. Customers with only MPG assign. done: ' || TO_CHAR (SYSDATE, 'mm/dd/yyyy hh24:mi:ss') ); p.l ('Input records: ' || v_in_cnt || ', Output records: ' || v_out_cnt); v_tot_in := v_tot_in + v_in_cnt; v_tot_out := v_tot_out + v_out_cnt; v_in_cnt := 0; v_out_cnt := 0; p.l ( 'Total Input records: ' || v_tot_in || ', Total Output records: ' || v_tot_out ); p.l ('Input/Output ratio: ' || TO_CHAR (v_tot_in / v_tot_out, '999.99')); p.l (' '); -- -- /* ******************************* ** Process pure MPG assignments ******************************* */ doing_both := FALSE; mpg_only := TRUE; first_rec := TRUE; clear_keys; OPEN c_mpg; IF src_rec.sorg_cd = v_sorg_cd --src_key = prev_key AND src_rec.cust_cd = v_cust_cd AND src_rec.mpg = v_mpg THEN --p.l ('MS1-same key, looking for same hierarchy'); v_src_values := src_rec.cust_ter_cd || src_rec.cust_rgn_cd || src_rec.cust_grp_cd || src_rec.cust_area_cd; IF v_src_values = v_prev_values THEN --p.l ( 'MS2-same key and hierarchy' ); IF src_rec.from_dt <= v_from_dt THEN --p.l( 'MS3-if src from_dt is earlier, use it' ); v_from_dt := src_rec.from_dt; END IF; ELSE IF src_rec.from_dt < v_from_dt THEN do_insert; src_rec.to_dt := TRUNC (v_from_dt) - 1 / (24 * 60 * 60); save_source; END IF; END IF; ELSE IF first_rec THEN first_rec := FALSE; ELSE do_insert; END IF; save_source; END IF; -- src_key = prev_key END LOOP; -- rec in c_mpg cursor loop COMMIT; CLOSE c_mpg; mpg_only := FALSE; p.l ( 'Customers with only MPG assignments done: ' || TO_CHAR (SYSDATE, 'mm/dd/yyyy hh24:mi:ss') ); p.l ('Input records: ' || v_in_cnt || ', Output records: ' || v_out_cnt); p.l (' '); v_tot_in := v_tot_in + v_in_cnt; v_tot_out := v_tot_out + v_out_cnt; p.l ( 'Total Input records: ' || v_tot_in || ', Total Output records: ' || v_tot_out ); -- -- p.l ( 'Analyze tables started: ' || TO_CHAR (SYSDATE, 'mm/dd/yyyy hh24:mi:ss') ); atab ('cust_hier_bkp2'); v_end_dt := SYSDATE; p.l (' '); p.l ('Finished at: ' || TO_CHAR (v_end_dt, 'mm/dd/yyyy hh24:mi:ss')); EXCEPTION WHEN NO_DATA_FOUND THEN v_error_code := SQLCODE; v_error_message := SUBSTR (SQLERRM, 1, 200); p.l ('Failure during end of business date processing'); p.l ('ERROR : ' || v_error_message); raise_application_error (-20001, v_error_message); WHEN OTHERS THEN v_error_code := SQLCODE; v_error_message := SUBSTR (SQLERRM, 1, 200); p.l ('Error: ' || v_error_message); p.l ('Sorg: ' || v_sorg_cd || ' CUST: ' || v_cust_cd); p.l ( ' DIV: ' || v_div || ' BUNT: ' || v_bunt || ' from_dt: ' || v_from_dt || ' to_dt: ' || v_to_dt ); --DP p.l ( 'v_from: ' || v_from_dt || ' v_to: ' || v_to_dt || ' v_pcd: ' || v_plan_cd ); p.l ( 'o_from: ' || v_orig_from_dt || ' o_to: ' || v_orig_to_dt || ' fyr: ' || v_from_yr || ' tyr: ' || v_to_yr || ' yr_dif: ' || v_yr_diff ); raise_application_error (-20001, v_error_message); END; /