📄 pack_ccdata_report04.pck
字号:
CREATE OR REPLACE PACKAGE PACK_CCDATA_REPORT04 AS
PROCEDURE PROC_CCDATA_REPORT04
(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 --传入参数，展现层次
);
END PACK_CCDATA_REPORT04;
/
CREATE OR REPLACE PACKAGE BODY PACK_CCDATA_REPORT04 AS
PROCEDURE PROC_CCDATA_REPORT04
( 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 --传入参数，展现层次
)
IS
--日志变量
v_thisprocid CS2002.LOG_PROC_EXEC.PROC_ID%TYPE :='CCENT.20000400';
v_thisprocname CS2002.LOG_PROC_EXEC.PROC_NAME%TYPE :=
'PACK_CCDATA_REPORT04.PROC_CCDATA_REPORT04';
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_firstday VARCHAR2(8);
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_cursor2 IS
SELECT CONDITION,FLAG,TARGET
FROM DIC_ID_CONDITION
WHERE REPORTID = P_I_REPORTID
AND SUMTAG = '1';
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_firstday := SUBSTR(P_I_DATE,1,6)||'01';
--删除临时表中的记录
v_stepno:=1;
DELETE FROM DM_DAT_REPORT04
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_REPORT04
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_REPORT01');由于是全局临时表
--判断传入是卡中心，需要二级展现
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;
--计算POS数量
--本行POS数量
INSERT INTO CCENT.DM_TMP_REPORT04(DATADATE,REPORTID,EXHIZONENO,EXHIBANKFLAG,
EXHIAREANAME,ZONENO,BANKFLAG,CURRTYPE,
EXHIFLAG,UNIT,
VALUE01,VALUE02,VALUE03,VALUE04,VALUE05,
VALUE06,VALUE07,VALUE08,VALUE09,VALUE10,
VALUE11,VALUE12,VALUE13,VALUE14,VALUE15,
VALUE16,FLAG)
SELECT P_I_DATE,P_I_REPORTID,ZONENO,BANK_FLAG,
'',P_I_ZONENO,P_I_BANKFLAG,P_I_CURR,
P_I_EXHIFLAG,P_I_UNIT,
SUM(VAL001),0,0,0,0,
0,0,0,0,0,
0,0,0,0,0,
0,'1'
FROM CCENT.DW_DAT_BFHCPSXZ
WHERE DATATIME = P_I_DATE
AND BANK_FLAG = '3'
GROUP BY ZONENO,BANK_FLAG;
--本月新安装POS数量
INSERT INTO CCENT.DM_TMP_REPORT04(DATADATE,REPORTID,EXHIZONENO,EXHIBANKFLAG,
EXHIAREANAME,ZONENO,BANKFLAG,CURRTYPE,
EXHIFLAG,UNIT,
VALUE01,VALUE02,VALUE03,VALUE04,VALUE05,
VALUE06,VALUE07,VALUE08,VALUE09,VALUE10,
VALUE11,VALUE12,VALUE13,VALUE14,VALUE15,
VALUE16,FLAG)
SELECT P_I_DATE,P_I_REPORTID,ZONENO,BANK_FLAG,
'',P_I_ZONENO,P_I_BANKFLAG,P_I_CURR,
P_I_EXHIFLAG,P_I_UNIT,
0,SUM(VAL001),0,0,0,
0,0,0,0,0,
0,0,0,0,0,
0,'2'
FROM CCENT.DW_DAT_BFHCPSXZ
WHERE DATATIME = P_I_DATE
AND ACTDATE BETWEEN TO_DATE(v_firstday,'YYYY-MM-DD')
AND TO_DATE(v_lastday,'YYYY-MM-DD')
AND BANK_FLAG = '3'
GROUP BY ZONENO,BANK_FLAG;
--本月有交易POS数量
INSERT INTO CCENT.DM_TMP_REPORT04(DATADATE,REPORTID,EXHIZONENO,EXHIBANKFLAG,
EXHIAREANAME,ZONENO,BANKFLAG,CURRTYPE,
EXHIFLAG,UNIT,
VALUE01,VALUE02,VALUE03,VALUE04,VALUE05,
VALUE06,VALUE07,VALUE08,VALUE09,VALUE10,
VALUE11,VALUE12,VALUE13,VALUE14,VALUE15,
VALUE16,FLAG)
SELECT P_I_DATE,P_I_REPORTID,ZONENO,BANK_FLAG,
'',P_I_ZONENO,P_I_BANKFLAG,P_I_CURR,
P_I_EXHIFLAG,P_I_UNIT,
0,0,SUM(VAL001),0,0,
0,0,0,0,0,
0,0,0,0,0,
0,'2'
FROM CCENT.DW_DAT_BFHCPSXZ
WHERE DATATIME = P_I_DATE
AND LSTTRXD BETWEEN TO_DATE(v_firstday,'YYYY-MM-DD')
AND TO_DATE(v_lastday,'YYYY-MM-DD')
AND BANK_FLAG = '3'
GROUP BY ZONENO,BANK_FLAG;
--12个月内无交易POS数
INSERT INTO CCENT.DM_TMP_REPORT04(DATADATE,REPORTID,EXHIZONENO,EXHIBANKFLAG,
EXHIAREANAME,ZONENO,BANKFLAG,CURRTYPE,
EXHIFLAG,UNIT,
VALUE01,VALUE02,VALUE03,VALUE04,VALUE05,
VALUE06,VALUE07,VALUE08,VALUE09,VALUE10,
VALUE11,VALUE12,VALUE13,VALUE14,VALUE15,
VALUE16,FLAG)
SELECT P_I_DATE,P_I_REPORTID,ZONENO,BANK_FLAG,
'',P_I_ZONENO,P_I_BANKFLAG,P_I_CURR,
P_I_EXHIFLAG,P_I_UNIT,
0,0,0,SUM(VAL001),0,
0,0,0,0,0,
0,0,0,0,0,
0,'2'
FROM CCENT.DW_DAT_BFHCPSXZ
WHERE DATATIME = P_I_DATE
AND ((TO_NUMBER(
MONTHS_BETWEEN(
TO_DATE(P_I_DATE,'YYYYMM'),
TO_DATE(TO_CHAR(ADD_MONTHS(LSTTRXD,-1),'YYYYMM'),'YYYYMM'))
) >12
)
OR (TO_CHAR(LSTTRXD,'YYYYMMDD') = '99991231'
AND TO_NUMBER(
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -