📄 pack_ccdata_report02.pck
字号:
CREATE OR REPLACE PACKAGE PACK_CCDATA_REPORT02 AS
PROCEDURE PROC_CCDATA_REPORT02
(P_I_DATE IN VARCHAR2, --传入参数 日期8位，月报6位，年报4位
P_I_REPORTID IN VARCHAR2, --传入参数,报表号
P_I_ZONENO IN VARCHAR2, --传入参数，地区号
P_I_BANKFLAG IN VARCHAR2, --传入参数，行级别
P_I_CURR IN VARCHAR2, --传入参数，币种
P_I_UNIT IN VARCHAR2, --传入参数，金额单位
P_I_EXHIFLAG IN VARCHAR2, --传入参数，展现层次
P_O_SUCCEED OUT VARCHAR2 --传出参数，成功标志 0为成功，1为失败
);
END PACK_CCDATA_REPORT02;
/
CREATE OR REPLACE PACKAGE BODY PACK_CCDATA_REPORT02 AS
PROCEDURE PROC_CCDATA_REPORT02
( P_I_DATE IN VARCHAR2, --传入参数 日期8位，月报6位，年报4位
P_I_REPORTID IN VARCHAR2, --传入参数,报表号
P_I_ZONENO IN VARCHAR2, --传入参数，地区号
P_I_BANKFLAG IN VARCHAR2, --传入参数，行级别
P_I_CURR IN VARCHAR2, --传入参数，币种
P_I_UNIT IN VARCHAR2, --传入参数，金额单位
P_I_EXHIFLAG IN VARCHAR2, --传入参数，展现层次
P_O_SUCCEED OUT VARCHAR2 --传出参数，成功标志 0为成功，1为失败
)
IS
--日志变量
v_thisprocid CS2002.LOG_PROC_EXEC.PROC_ID%TYPE :='CCENT.20000200';
v_thisprocname CS2002.LOG_PROC_EXEC.PROC_NAME%TYPE:=
'PACK_CCDATA_REPORT02.PROC_CCDATA_REPORT02';
v_stepno CS2002.LOG_PROC_EXEC.STEP_NUM%TYPE;
v_begintime CS2002.LOG_PROC_EXEC.BEGIN_TIME%TYPE;
v_end_time CS2002.LOG_PROC_EXEC.END_TIME%TYPE;
v_deal_flag CS2002.LOG_PROC_EXEC.DEAL_FLAG%TYPE;
--v_process_level CS2002.LOG_PROC_ERR.LOG_LEVEL%TYPE;
v_proc_err_code CS2002.LOG_PROC_ERR.PROC_ERR_CODE%TYPE;
v_proc_err_txt CS2002.LOG_PROC_ERR.PROC_ERR_TXT%TYPE;
v_succeed VARCHAR2(1); --判断成功标志 ‘0’成功，‘1’失败
v_zone_cond VARCHAR2(100);--选择地区的条件
--v_bankflag VARCHAR2(1);
v_zoneno VARCHAR2(5);
--v_exhibankflag VARCHAR2(1);
--v_exhizoneno VARCHAR2(5);
v_lastday VARCHAR2(8);
--用于动态调用的变量
v_dynamic_cursor NUMBER;
v_result NUMBER;
v_sqltxt VARCHAR2(4000);
--游标变量
v_condition CCENT.DIC_ID_CONDITION.CONDITION%TYPE;--取数条件
v_flag CCENT.DIC_ID_CONDITION.FLAG%TYPE;
v_target CCENT.DIC_ID_CONDITION.TARGET%TYPE;
--取数条件游标
CURSOR cur_condition_cursor IS
SELECT CONDITION,FLAG,TARGET
FROM DIC_ID_CONDITION
WHERE REPORTID = P_I_REPORTID;
BEGIN
--记载数据处理日志
--读取系统时间记载开始处理时间
SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') INTO v_begintime FROM DUAL;
v_lastday:=TO_CHAR(LAST_DAY(TO_DATE(P_I_DATE,'YYYYMM')),'YYYYMMDD');
--删除临时表中的记录
v_stepno:=1;
DELETE FROM DM_DAT_REPORT02
WHERE DATADATE = P_I_DATE
AND REPORTID = P_I_REPORTID
AND ZONENO = P_I_ZONENO
AND BANKFLAG = P_I_BANKFLAG
AND CURRTYPE = P_I_CURR
AND EXHIFLAG = P_I_EXHIFLAG
AND UNIT = P_I_UNIT;
DELETE FROM DM_TMP_REPORT02
WHERE DATADATE = P_I_DATE
AND REPORTID = P_I_REPORTID
AND ZONENO = P_I_ZONENO
AND BANKFLAG = P_I_BANKFLAG
AND CURRTYPE = P_I_CURR
AND EXHIFLAG = P_I_EXHIFLAG
AND UNIT = P_I_UNIT;
--EXECUTE IMMEDIATE ('TRUNCATE TABLE DM_TMP_REPORT02');由于是全局临时表
--判断传入是卡中心，需要二级展现
v_stepno:=2;
IF P_I_ZONENO = '0100' AND P_I_BANKFLAG = '5' AND P_I_EXHIFLAG = '1'
THEN v_zone_cond := 'BANK_FLAG = ''3''';
--从字典表DIC_ID_CONDITION中取出相应条件
--循环从DW卡片统计表中取数
v_stepno:=301;
v_dynamic_cursor := DBMS_SQL.OPEN_CURSOR;
OPEN cur_condition_cursor;
LOOP
FETCH cur_condition_cursor INTO v_condition,v_flag,v_target;
EXIT WHEN cur_condition_cursor%NOTFOUND;
v_sqltxt := 'INSERT INTO CCENT.DM_TMP_REPORT02(DATADATE,REPORTID,EXHIZONENO,';
v_sqltxt := v_sqltxt||'EXHIBANKFLAG,EXHIAREANAME,ZONENO,BANKFLAG,CURRTYPE,';
v_sqltxt := v_sqltxt||'EXHIFLAG,UNIT,VALUE01,VALUE02,VALUE03,VALUE04,VALUE05,';
v_sqltxt := v_sqltxt||'VALUE06,VALUE07,VALUE08,VALUE09,FLAG)';
v_sqltxt := v_sqltxt||' SELECT '||P_I_DATE;
v_sqltxt := v_sqltxt||','||P_I_REPORTID;
v_sqltxt := v_sqltxt||',ZONENO,BANK_FLAG,''''';
v_sqltxt := v_sqltxt||','''||P_I_ZONENO;
v_sqltxt := v_sqltxt||''','||P_I_BANKFLAG;
v_sqltxt := v_sqltxt||','''||P_I_CURR;
v_sqltxt := v_sqltxt||''','||P_I_EXHIFLAG;
v_sqltxt := v_sqltxt||','||P_I_UNIT;
v_sqltxt := v_sqltxt||','||v_target;
v_sqltxt := v_sqltxt||','||v_flag;
v_sqltxt := v_sqltxt||' FROM CCENT.DW_DAT_BFHCZHTJ';
v_sqltxt := v_sqltxt||' WHERE DATATIME ='''||P_I_DATE;
v_sqltxt := v_sqltxt||''' AND CURRTYPE ='''||P_I_CURR;
v_sqltxt := v_sqltxt||''' AND '||v_condition;
v_sqltxt := v_sqltxt||' AND '||v_zone_cond;
v_sqltxt := v_sqltxt||' GROUP BY ZONENO,BANK_FLAG';
DBMS_SQL.PARSE(v_dynamic_cursor, v_sqltxt, DBMS_SQL.V7);
v_result := DBMS_SQL.EXECUTE(v_dynamic_cursor);
END LOOP;
CLOSE cur_condition_cursor;
DBMS_SQL.CLOSE_CURSOR(v_dynamic_cursor);
v_sqltxt := NULL;
--判断传入是发卡机构
ELSIF P_I_BANKFLAG = '3'
THEN v_zoneno := P_I_ZONENO;
v_zone_cond := 'BANK_FLAG = ''3'' AND ZONENO = '''||v_zoneno;
--从字典表DIC_ID_CONDITION中取出相应条件
--循环从DW卡片统计表中取数
v_stepno:=302;
v_dynamic_cursor := DBMS_SQL.OPEN_CURSOR;
OPEN cur_condition_cursor;
LOOP
FETCH cur_condition_cursor INTO v_condition,v_flag,v_target;
EXIT WHEN cur_condition_cursor%NOTFOUND;
v_sqltxt := 'INSERT INTO CCENT.DM_TMP_REPORT02(DATADATE,REPORTID,EXHIZONENO,';
v_sqltxt := v_sqltxt||'EXHIBANKFLAG,EXHIAREANAME,ZONENO,BANKFLAG,CURRTYPE,';
v_sqltxt := v_sqltxt||'EXHIFLAG,UNIT,VALUE01,VALUE02,VALUE03,VALUE04,VALUE05,';
v_sqltxt := v_sqltxt||'VALUE06,VALUE07,VALUE08,VALUE09,FLAG)';
v_sqltxt := v_sqltxt||' SELECT '||P_I_DATE;
v_sqltxt := v_sqltxt||','||P_I_REPORTID;
v_sqltxt := v_sqltxt||',ZONENO,BANK_FLAG,''''';
v_sqltxt := v_sqltxt||','''||P_I_ZONENO;
v_sqltxt := v_sqltxt||''','||P_I_BANKFLAG;
v_sqltxt := v_sqltxt||','''||P_I_CURR;
v_sqltxt := v_sqltxt||''','||P_I_EXHIFLAG;
v_sqltxt := v_sqltxt||','||P_I_UNIT;
v_sqltxt := v_sqltxt||','||v_target;
v_sqltxt := v_sqltxt||','||v_flag;
v_sqltxt := v_sqltxt||' FROM CCENT.DW_DAT_BFHCZHTJ';
v_sqltxt := v_sqltxt||' WHERE DATATIME ='''||P_I_DATE;
v_sqltxt := v_sqltxt||''' AND CURRTYPE ='''||P_I_CURR;
v_sqltxt := v_sqltxt||''' AND '||v_condition;
v_sqltxt := v_sqltxt||' AND '||v_zone_cond;
v_sqltxt := v_sqltxt||''' GROUP BY ZONENO,BANK_FLAG';
DBMS_SQL.PARSE(v_dynamic_cursor, v_sqltxt, DBMS_SQL.V7);
v_result := DBMS_SQL.EXECUTE(v_dynamic_cursor);
END LOOP;
CLOSE cur_condition_cursor;
DBMS_SQL.CLOSE_CURSOR(v_dynamic_cursor);
v_sqltxt := NULL;
--其他均是取下属机构
ELSE
--从字典表DIC_ID_CONDITION中取出相应条件
--循环从DW卡片统计表中取数
v_stepno:=303;
v_dynamic_cursor := DBMS_SQL.OPEN_CURSOR;
OPEN cur_condition_cursor;
LOOP
FETCH cur_condition_cursor INTO v_condition,v_flag,v_target;
EXIT WHEN cur_condition_cursor%NOTFOUND;
v_sqltxt := 'INSERT INTO CCENT.DM_TMP_REPORT02(DATADATE,REPORTID,EXHIZONENO,';
v_sqltxt := v_sqltxt||'EXHIBANKFLAG,EXHIAREANAME,ZONENO,BANKFLAG,CURRTYPE,';
v_sqltxt := v_sqltxt||'EXHIFLAG,UNIT,VALUE01,VALUE02,VALUE03,VALUE04,VALUE05,';
v_sqltxt := v_sqltxt||'VALUE06,VALUE07,VALUE08,VALUE09,FLAG)';
v_sqltxt := v_sqltxt||' SELECT '||P_I_DATE;
v_sqltxt := v_sqltxt||','||P_I_REPORTID;
v_sqltxt := v_sqltxt||',A.ZONENO,A.BANK_FLAG,''''';
v_sqltxt := v_sqltxt||','''||P_I_ZONENO;
v_sqltxt := v_sqltxt||''','||P_I_BANKFLAG;
v_sqltxt := v_sqltxt||','''||P_I_CURR;
v_sqltxt := v_sqltxt||''','||P_I_EXHIFLAG;
v_sqltxt := v_sqltxt||','||P_I_UNIT;
v_sqltxt := v_sqltxt||','||v_target;
v_sqltxt := v_sqltxt||','||v_flag;
v_sqltxt := v_sqltxt||' FROM CCENT.DW_DAT_BFHCZHTJ A,CCENT.PRM_CARD_ORGAN B';
v_sqltxt := v_sqltxt||' WHERE DATATIME ='''||P_I_DATE;
v_sqltxt := v_sqltxt||''' AND CURRTYPE ='''||P_I_CURR;
v_sqltxt := v_sqltxt||''' AND '||v_condition;
v_sqltxt := v_sqltxt||' AND A.ZONENO = B.ZONENO AND A.BANK_FLAG = B.BANKFLAG';
v_sqltxt := v_sqltxt||' AND B.BELONG_ZONENO = '''||P_I_ZONENO;
v_sqltxt := v_sqltxt||''' AND B.BELONG_BANKFLAG = '''||P_I_BANKFLAG;
v_sqltxt := v_sqltxt||''' AND B.START_DATE <= '''||v_lastday;
v_sqltxt := v_sqltxt||''' AND B.END_DATE > '''||v_lastday;
v_sqltxt := v_sqltxt||''' GROUP BY A.ZONENO,A.BANK_FLAG';
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -