CREATE OR REPLACE PACKAGE BODY OG_PKG_CALC_LJJ IS /****************************************************************************** NAME OG_PKG_CALC_LJJ Package BODY PURPOSE REVISIONS Ver Date Author Description --------- ---------- --------------- ------------------------------------- 1.0 2012-02-23 1. Created this procedure. 1.1 2012-02-27 2. Created OG_JOB_LOG_LJJ procedure. 1.2 2012-02-27 3. Created OG_JOB_ERR_LJJ procedure. 1.3 2012-02-28 4. Created this PACKAGE. 1.4 2012-02-29 5. Fixed this PACKAGE. NOTES Automatically available Auto Replace Keywords Object Name OG_PKG_CALL_LJJ Sysdate 2012-02-27 Date and Time 2012-02-27, 오후 8시, and 2012-02-29 오후 4시 Username Table Name OG_ODS_ORDER_SUMMARY_LJJ, OG_ODS_ORDER_SUMMARY_RATE_LJJ OG_SYSTEM_JOB_LOG_LJJ, OG_SYSTEM_JOB_ERR_LJJ LOGIC 1.SUMMARY TABLE 적재 프로시저가 도는 중 오류 없으면, RATE TABLE 적재 프로시저로 이동하여 적재 후 JOB_LOG 남김 2.SUMMARY TABLE 적재 프로시저가 도는 중 오류 없으면, RATE TABLE 적재 프로시저로 이동하여 적재, BUT, 도중 오류 발생 시 JOB_LOG와 ERR_LOG 남김 3.SUMMARY TABLE 적재 프로시저가 도는 중 오류 발생시, RATE TABLE 적재 프로시저는 SKIP 하고 JOB_LOG와 ERR_LOG 남김 *******************************************************************************/ -------------------------------------------------------------------------------- -- 최초 작성일 : 2012.02.28 -------------------------------------------------------------------------------- PROCEDURE OG_ORDER_SUMMARY_LJJ_PROC( IN_FROM_DATE IN VARCHAR2 , IN_TO_DATE IN VARCHAR2 , n_SEQ OUT NUMBER , d_STIME OUT DATE , d_ETIME OUT DATE , v_YN OUT VARCHAR2 , n_ERCODE OUT NUMBER , v_ERMSG OUT VARCHAR2 , v_WORK_DATE OUT VARCHAR2 ) IS YEAR CHAR(4) := NULL; -- YEAR MONTH CHAR(3) := NULL; -- MONTH SHOP_CODE VARCHAR2(30) := NULL; -- SHOP_CODE SHOP_NAME VARCHAR2(50) := NULL; -- SHOP_NAME TOT_AMT NUMBER := 0 ; -- AGG테이블의 YEAR, MONTH, SHOP의 SUM값 PLAN_AMT NUMBER := 0 ; -- PLAN 테이블의 YEAR, MONTH, SHOP 의 SUM값 DIFF_AMT NUMBER := 0 ; -- TOT_AMT와 PL_AMT 차이의 절대값 DIFF_RATE NUMBER := 0 ; -- TOT_AMT 와 PL_AMT 의 달성율 ( TOT_AMT / PL_AMT ) * 100 BEGIN -- 전체 SUMMARY, RATE 처리 모듈 SELECT MAX(SEQ) INTO n_SEQ FROM OG_SYSTEM_JOB_LOG_LJJ; -- 시퀀스 처리 모듈 SELECT MAX(WORK_DATE) INTO v_WORK_DATE FROM OG_SYSTEM_JOB_LOG_LJJ; IF n_SEQ IS NULL THEN n_SEQ := 1; -- 맨처음 실행 시 ELSIF v_WORK_DATE = TO_CHAR(SYSDATE,'YYYYMMDD') THEN n_SEQ := n_SEQ + 1; -- 같은날 돌렸을 때는 +1 ELSE n_SEQ := 1; -- 다른날 돌렸을 때는 1로 정의 END IF; d_STIME := SYSDATE; -- 작업시간 저장 BEGIN -- SUMMARY 테이블 처리 모듈 MERGE INTO OG_ODS_ORDER_SUMMARY_LJJ T -- OG_ODS_ORDER_SUMMARY_LJJ 에 데이터 적재 USING (SELECT A.YEAR , A.MONTH , SHOP_CODE , SHOP_NAME , A.TOT_AMT , A.PLAN_AMT , ABS((A.TOT_AMT - A.PLAN_AMT)) AS DIFF_AMT , DECODE(A.PLAN_AMT,0,0,((A.TOT_AMT / A.PLAN_AMT) * 100)) AS DIFF_RATE , CREATER , CREAT_TIME FROM (SELECT A.YEAR , A.MONTH , A.SHOP AS SHOP_CODE , A.NAME AS SHOP_NAME , A.TOT_AMT , NVL( (SELECT SUM(PL_AMT) FROM OG_ODS_PLAN_BY_MAEJANG_LJJ WHERE YEAR(+) = A.YEAR AND MONTH(+) = A.MONTH AND SHOP(+) = A.SHOP ), 0 ) AS PLAN_AMT , CREATER , CREAT_TIME FROM (SELECT /*+ INDEX( A OG_ODS_ORDER_AGGREGATION_PK ) */ A.YEAR , A.MONTH , A.SHOP , (SELECT NAME FROM OG_MST_SHOP_LJJ WHERE SHOP = A.SHOP ) AS NAME , SUM(A.TOT_AMT) AS TOT_AMT , 'LJJ' AS CREATER , SYSDATE AS CREAT_TIME FROM OG_ODS_ORDER_AGGREGATION_LJJ A WHERE BASE_DATE BETWEEN IN_FROM_DATE AND IN_TO_DATE GROUP BY A.YEAR, A.MONTH , A.SHOP ) A )A ) F ON ( F.YEAR = T.YEAR AND F.MONTH = T.MONTH AND F.SHOP_CODE = T.SHOP_CODE ) WHEN MATCHED THEN -- 값이 존재하면 UPDATE UPDATE SET T.SHOP_NAME = F.SHOP_NAME , T.TOT_AMT = F.TOT_AMT , T.PLAN_AMT = F.PLAN_AMT , T.DIFF_AMT = F.DIFF_AMT , T.DIFF_RATE = F.DIFF_RATE , T.CREATER = F.CREATER , T.CREAT_TIME = F.CREAT_TIME WHEN NOT MATCHED THEN -- 값이 존재하지 않으면 INSERT INSERT ( T.YEAR , T.MONTH , T.SHOP_CODE , T.SHOP_NAME , T.TOT_AMT , T.PLAN_AMT , T.DIFF_AMT , T.DIFF_RATE , T.CREATER , T.CREAT_TIME ) VALUES ( F.YEAR , F.MONTH , F.SHOP_CODE , F.SHOP_NAME , F.TOT_AMT , F.PLAN_AMT , F.DIFF_AMT , F.DIFF_RATE , F.CREATER , F.CREAT_TIME ); s_YEAR := F.YEAR -- YEAR s_MONTH := F.MONTH --MONTH DBMS_OUTPUT.PUT_LINE(v_YEAR||v_MONTH); d_ETIME := SYSDATE; -- 작업종료시간 저장 EXCEPTION WHEN OTHERS THEN n_ERCODE := SQLCODE; -- 에러코드 변수에 저장 v_ERMSG := SQLERRM; -- 에러메시지 변수에 저장 DBMS_OUTPUT.PUT_LINE('JOB_LOG 에러코드:' || n_ERCODE || ' 에러메시지:' || v_ERMSG || ' 성공실패여부:' || v_YN); END; IF TO_CHAR(n_ERCODE) = '0' OR TO_CHAR(n_ERCODE) IS NULL THEN v_YN := 'Y'; -- 에러발생 X ELSE v_YN := 'N'; ROLLBACK; GOTO JUMP; -- 에러발생 (RATE 적재 하지않고 바로 JOB과 ERR_JOB에 기록 END IF; SAVEPOINT MERGE_RATE; BEGIN -- RATE 테이블 처리 모듈 MERGE INTO OG_ODS_ORDER_SUMMARY_RATE_LJJ T USING (SELECT * FROM (SELECT A.YEAR , A.MONTH , SHOP_CODE , SHOP_NAME , DECODE(A.PLAN_AMT,0,0,((A.TOT_AMT / A.PLAN_AMT) * 100)) AS DIFF_RATE , 'LJJ' AS CREATER , SYSDATE AS CREAT_TIME FROM (SELECT A.YEAR , A.MONTH , A.SHOP AS SHOP_CODE , A.NAME AS SHOP_NAME , A.TOT_AMT , NVL( (SELECT SUM(PL_AMT) FROM OG_ODS_PLAN_BY_MAEJANG_LJJ WHERE YEAR(+) = A.YEAR AND MONTH(+) = A.MONTH AND SHOP(+) = A.SHOP ), 0 ) AS PLAN_AMT , CREATER , CREAT_TIME FROM (SELECT /*+ INDEX( A OG_ODS_ORDER_AGGREGATION_PK ) */ A.YEAR , A.MONTH , A.SHOP , (SELECT NAME FROM OG_MST_SHOP_LJJ WHERE SHOP = A.SHOP ) AS NAME , SUM(A.TOT_AMT) AS TOT_AMT , 'LJJ' AS CREATER , SYSDATE AS CREAT_TIME FROM OG_ODS_ORDER_AGGREGATION_LJJ A WHERE BASE_DATE BETWEEN IN_FROM_DATE AND IN_TO_DATE GROUP BY A.YEAR, A.MONTH , A.SHOP ) A )A ) A WHERE DIFF_RATE >= 100 ) F ON ( F.YEAR = T.YEAR AND F.MONTH = T.MONTH AND F.SHOP_CODE = T.SHOP_CODE ) WHEN MATCHED THEN -- 값이 존재하면 UPDATE UPDATE SET T.SHOP_NAME = F.SHOP_NAME , T.DIFF_RATE = F.DIFF_RATE , T.CREATER = F.CREATER , T.CREAT_TIME = F.CREAT_TIME WHEN NOT MATCHED THEN -- 값이 존재하지 않으면 INSERT INSERT ( T.YEAR , T.MONTH , T.SHOP_CODE , T.SHOP_NAME , T.DIFF_RATE , T.CREATER , T.CREAT_TIME ) VALUES ( F.YEAR , F.MONTH , F.SHOP_CODE , F.SHOP_NAME , F.DIFF_RATE , F.CREATER , F.CREAT_TIME ); d_ETIME := SYSDATE; -- 작업종료시간 저장 EXCEPTION WHEN OTHERS THEN n_ERCODE := SQLCODE; -- 에러코드 변수에 저장 v_ERMSG := SQLERRM; -- 에러메시지 변수에 저장 DBMS_OUTPUT.PUT_LINE('JOB_ERR_LOG 에러코드:' || n_ERCODE || ' 에러메시지:' || v_ERMSG || ' 성공실패여부:' || v_YN); END; IF TO_CHAR(n_ERCODE) = '0' OR TO_CHAR(n_ERCODE) IS NULL THEN v_YN := 'Y'; -- 에러발생 X ELSE v_YN := 'N'; -- 에러발생 ROLLBACK TO MERGE_RATE; END IF; <> DBMS_OUTPUT.PUT_LINE('JUMP시점 에러코드:' || n_ERCODE || ' 에러메시지:' || v_ERMSG || ' 성공실패:' || v_YN); BEGIN OG_PKG_CALC_LJJ.OG_JOB_LOG_LJJ ( n_SEQ => n_SEQ -- 에러 미 발생시 OG_JOB_LOG_LJJ 호출 JOB로그 기록 , v_YEAR => s_YEAR , v_MONTH => s_MONTH , v_SHOP_CODE => SHOP_CODE , d_STIME => d_STIME , d_ETIME => d_ETIME , v_YN => v_YN ); END; IF v_YN = 'N' THEN -- 에러 발생시 OG_JOB_ERR_LJJ 호출 에러로그 기록 BEGIN OG_PKG_CALC_LJJ.OG_JOB_ERR_LJJ ( d_STIME => d_STIME , n_SEQ => n_SEQ , v_YEAR => s_YEAR , v_MONTH => s_MONTH , v_SHOP_CODE => SHOP_CODE , n_ERCODE => n_ERCODE , v_ERMSG => v_ERMSG ); END; END IF; n_ERCODE := 0; v_ERMSG := NULL; DBMS_OUTPUT.PUT_LINE('데이터 입력 완료'); COMMIT; END; -------------------------------------------------------------------------------- -- LOG 기록 프로시저 -------------------------------------------------------------------------------- PROCEDURE OG_JOB_LOG_LJJ ( n_SEQ IN VARCHAR2 , v_YEAR IN VARCHAR2 , v_MONTH IN VARCHAR2 , v_SHOP_CODE IN VARCHAR2 , d_STIME IN DATE , d_ETIME IN DATE , v_YN IN VARCHAR2 ) IS BEGIN INSERT INTO OG_SYSTEM_JOB_LOG_LJJ ( WORK_DATE ,SEQ ,YEAR ,MONTH ,SHOP ,START_TIME ,END_TIME ,SUCCESS_YN ) VALUES ( TO_CHAR(d_STIME, 'YYYYMMDD') -- 작업수행일자 , n_SEQ -- 순번 , v_YEAR -- YEAR , v_MONTH -- MONTH , v_SHOP_CODE -- SHOP_CODE , d_STIME -- 작업수행시작시간 , d_ETIME -- 작업종료시간 , v_YN -- 작업성공여부 ); END; -------------------------------------------------------------------------------- -- ERR_LOG 기록 프로시저 -------------------------------------------------------------------------------- PROCEDURE OG_JOB_ERR_LJJ ( d_STIME IN DATE , n_SEQ IN VARCHAR2 , v_YEAR IN VARCHAR2 , v_MONTH IN VARCHAR2 , v_SHOP_CODE IN VARCHAR2 , n_ERCODE IN NUMBER , v_ERMSG IN VARCHAR2 ) IS BEGIN INSERT INTO OG_SYSTEM_ERR_LOG_LJJ ( WORK_DATE ,SEQ ,YEAR ,MONTH ,SHOP ,ERR_CODE ,ERR_MSG ,ERR_TIME ) VALUES ( TO_CHAR(d_STIME, 'YYYYMMDD') -- 작업수행일자(YYYYMMDD) , n_SEQ -- 순번 , v_YEAR -- YEAR , v_MONTH -- MONTH , v_SHOP_CODE -- SHOP_CODE , n_ERCODE -- 에러코드 , v_ERMSG -- 에러메시지 , SYSDATE -- 에러발생시간 ); END; END OG_PKG_CALC_LJJ; /