📄 pack_ccdata_report04.pck
字号:
MONTHS_BETWEEN(
TO_DATE(P_I_DATE,'YYYYMM'),
TO_DATE(TO_CHAR(ADD_MONTHS(ACTDATE,-1),'YYYYMM'),'YYYYMM')
)
) >12
)
)
AND BANK_FLAG = '3'
GROUP BY ZONENO,BANK_FLAG;
v_dynamic_cursor := DBMS_SQL.OPEN_CURSOR;
v_stepno:=401;
--计算交易指标
OPEN cur_condition_cursor2;
LOOP
FETCH cur_condition_cursor2 INTO v_condition,v_flag,v_target;
EXIT WHEN cur_condition_cursor2%NOTFOUND;
v_sqltxt := 'INSERT INTO CCENT.DM_TMP_REPORT04(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,VALUE10,VALUE11,VALUE12,';
v_sqltxt := v_sqltxt||'VALUE13,VALUE14,VALUE15,VALUE16,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_BFHCJYTJ';
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_cursor2;
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;
--计算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 ZONENO = P_I_ZONENO
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 ZONENO = P_I_ZONENO
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 ZONENO = P_I_ZONENO
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(
MONTHS_BETWEEN(
TO_DATE(P_I_DATE,'YYYYMM'),
TO_DATE(TO_CHAR(ADD_MONTHS(ACTDATE,-1),'YYYYMM'),'YYYYMM')
)
) >12
)
)
AND ZONENO = P_I_ZONENO
AND BANK_FLAG = '3'
GROUP BY ZONENO,BANK_FLAG;
v_dynamic_cursor := DBMS_SQL.OPEN_CURSOR;
v_stepno:=402;
--计算交易类指标
OPEN cur_condition_cursor2;
LOOP
FETCH cur_condition_cursor2 INTO v_condition,v_flag,v_target;
EXIT WHEN cur_condition_cursor2%NOTFOUND;
v_sqltxt := 'INSERT INTO CCENT.DM_TMP_REPORT04(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,VALUE10,VALUE11,VALUE12,';
v_sqltxt := v_sqltxt||'VALUE13,VALUE14,VALUE15,VALUE16,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_BFHCJYTJ';
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_cursor2;
DBMS_SQL.CLOSE_CURSOR(v_dynamic_cursor);
v_sqltxt := NULL;
--其他均是取下属机构
ELSE
--v_zone_cond:='EXSISTS (SELECT 1 FROM PRM_CARD_ORGAN WHERE ZONENO =DW_DAT_BFHCKPTJ.ZONENO AND BANKFLAG
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -