1、Packages

create or replace package SXFX is /*【运营模块】【时效报表】申请共享系统时效分析报表 */
type T_CURSOR is ref cursor;            

procedure proc_SCAN(  
  DJBH IN VARCHAR2,
  JYZTMC IN VARCHAR2,
  Re_CURSOR out T_CURSOR
);
end SXFX;

2、Package bodies

create or replace package body SXFX is


/*扫描分析*/
procedure proc_SCAN(
          DJBH IN VARCHAR2,
          JYZTMC IN VARCHAR2,
          Re_CURSOR out T_CURSOR
          )  AS
          SQLSTR VARCHAR2(5000);
          SQLWHERE VARCHAR2(200);
          BEGIN

          --单据编号         
          IF trim(DJBH) IS NOT NULL THEN
              SQLWHERE := ' AND DJBH = '''  || trim(DJBH) || ''' ';
          END IF;
 
           --单位         
          IF trim(JYZTMC) IS NOT NULL THEN
              SQLWHERE := ' AND JYZTMC = '''  || trim(JYZTMC) || ''' ';
          END IF;
 --BW_Query_SXFX

--alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';

DELETE FROM BW_Query_SXFX WHERE 1 = 1;

INSERT INTO BW_Query_SXFX(DJID, DJTYPE, COrder)
SELECT DISTINCT LSRWZTLOG_DJID, LSRWZTLOG_FLBH, 1
FROM LSRWZTLOG
WHERE 1 = 1
AND LSRWZTLOG_FLBH IN ('WEBROBX', 'APFKD', 'APYFD', 'WEBROJK')
AND LSRWZTLOG_TIME > to_date('2021-7-1', 'yyyy-mm-dd hh:mi:ss') 
AND LSRWZTLOG_TIME < to_date('2021-10-1', 'yyyy-mm-dd hh:mi:ss')
--AND  LSRWZTLOG_FLBH = 'WEBROBX' ;
;

UPDATE BW_Query_SXFX SET DJLXID = (SELECT APFKD_DJLXID FROM APFKD B WHERE B.ID = BW_Query_SXFX.DJID),
DJBH = (SELECT APFKD_DJBH FROM APFKD B WHERE B.ID = BW_Query_SXFX.DJID),
Creator = (SELECT APFKD_ZDRMC FROM APFKD B WHERE B.ID = BW_Query_SXFX.DJID),
ZDQJY = (SELECT APFKD_KJND FROM APFKD B WHERE B.ID = BW_Query_SXFX.DJID),
ZDQJM = (SELECT APFKD_KJQJ FROM APFKD B WHERE B.ID = BW_Query_SXFX.DJID),
BZAmt = (SELECT APFKD_JE FROM APFKD B WHERE B.ID = BW_Query_SXFX.DJID)
WHERE EXISTS(SELECT 1 FROM APFKD C WHERE C.ID = BW_Query_SXFX.DJID)
AND DJTYPE = 'APFKD'
;

UPDATE BW_Query_SXFX SET DJLXID = (SELECT APYFD_DJLXID FROM APYFD B WHERE B.ID = BW_Query_SXFX.DJID),
DJBH = (SELECT APYFD_DJBH FROM APYFD B WHERE B.ID = BW_Query_SXFX.DJID),
Creator = (SELECT APYFD_ZDRMC FROM APYFD B WHERE B.ID = BW_Query_SXFX.DJID),
ZDQJY = (SELECT APYFD_KJND FROM APYFD B WHERE B.ID = BW_Query_SXFX.DJID),
ZDQJM = (SELECT APYFD_KJQJ FROM APYFD B WHERE B.ID = BW_Query_SXFX.DJID),
BZAmt = (SELECT APYFD_JE FROM APYFD B WHERE B.ID = BW_Query_SXFX.DJID)
--JYZTMC = (SELECT ROBXDJ_NM8 FROM APYFD B WHERE B.ID = BW_Query_SXFX.DJID),
--BMMC = (SELECT ROBXDJ_GXM2 FROM APYFD B WHERE B.ID = BW_Query_SXFX.DJID)
WHERE EXISTS(SELECT 1 FROM APYFD C WHERE C.ID = BW_Query_SXFX.DJID)
AND DJTYPE = 'APYFD'
;

UPDATE BW_Query_SXFX SET DJLXID = (SELECT ROBXDJ_BXLX FROM ROBXDJ B WHERE B.ROBXDJ_NM = BW_Query_SXFX.DJID),
DJBH = (SELECT ROBXDJ_BH FROM ROBXDJ B WHERE B.ROBXDJ_NM = BW_Query_SXFX.DJID),
Creator = (SELECT ROBXDJ_USER FROM ROBXDJ B WHERE B.ROBXDJ_NM = BW_Query_SXFX.DJID),
JYZTMC = (SELECT ROBXDJ_NM8 FROM ROBXDJ B WHERE B.ROBXDJ_NM = BW_Query_SXFX.DJID),
BMMC = (SELECT ROBXDJ_GXM2 FROM ROBXDJ B WHERE B.ROBXDJ_NM = BW_Query_SXFX.DJID),
ZDQJY = (SELECT ROBXDJ_KJND FROM ROBXDJ B WHERE B.ROBXDJ_NM = BW_Query_SXFX.DJID),
ZDQJM = (SELECT ROBXDJ_KJQJ FROM ROBXDJ B WHERE B.ROBXDJ_NM = BW_Query_SXFX.DJID),
BZAmt = (SELECT ROBXDJ_BXJE FROM ROBXDJ B WHERE B.ROBXDJ_NM = BW_Query_SXFX.DJID)
WHERE EXISTS(SELECT 1 FROM ROBXDJ C WHERE C.ROBXDJ_NM = BW_Query_SXFX.DJID)
AND DJTYPE = 'WEBROBX'
;

UPDATE BW_Query_SXFX SET DJLXID = (SELECT ROYWSQ_SQLX FROM ROYWSQ B WHERE B.ROYWSQ_NM = BW_Query_SXFX.DJID),
DJBH = (SELECT ROYWSQ_BH FROM ROYWSQ B WHERE B.ROYWSQ_NM = BW_Query_SXFX.DJID),
Creator = (SELECT ROYWSQ_USER FROM ROYWSQ B WHERE B.ROYWSQ_NM = BW_Query_SXFX.DJID),
JYZTMC = (SELECT ROYWSQ_GXM4 FROM ROYWSQ B WHERE B.ROYWSQ_NM = BW_Query_SXFX.DJID),
BMMC = (SELECT ROYWSQ_GXM3 FROM ROYWSQ B WHERE B.ROYWSQ_NM = BW_Query_SXFX.DJID),
ZDQJY = (SELECT ROYWSQ_KJND FROM ROYWSQ B WHERE B.ROYWSQ_NM = BW_Query_SXFX.DJID),
ZDQJM = (SELECT ROYWSQ_KJQJ FROM ROYWSQ B WHERE B.ROYWSQ_NM = BW_Query_SXFX.DJID),
BZAmt = (SELECT ROYWSQ_JKJE FROM ROYWSQ B WHERE B.ROYWSQ_NM = BW_Query_SXFX.DJID)
WHERE EXISTS(SELECT 1 FROM ROYWSQ C WHERE C.ROYWSQ_NM = BW_Query_SXFX.DJID)
AND DJTYPE = 'WEBROJK'
;





UPDATE BW_Query_SXFX SET DJLXMC = (SELECT APFKDJLX_DJMC FROM APFKDJLX B WHERE B.ID = BW_Query_SXFX.DJLXID) 
WHERE EXISTS(SELECT 1 FROM APFKDJLX C WHERE C.ID = BW_Query_SXFX.DJLXID)
AND DJTYPE = 'APFKD'
;

UPDATE BW_Query_SXFX SET DJLXMC = (SELECT APYFDJLX_DJMC FROM APYFDJLX B WHERE B.ID = BW_Query_SXFX.DJLXID) 
WHERE EXISTS(SELECT 1 FROM APYFDJLX C WHERE C.ID = BW_Query_SXFX.DJLXID)
AND DJTYPE = 'APYFD'
;

UPDATE BW_Query_SXFX SET DJLXMC = (SELECT ROBXLX_MC FROM ROBXLX B WHERE B.ROBXLX_NM = BW_Query_SXFX.DJLXID) 
WHERE EXISTS(SELECT 1 FROM ROBXLX C WHERE C.ROBXLX_NM = BW_Query_SXFX.DJLXID)
AND DJTYPE = 'WEBROBX';

UPDATE BW_Query_SXFX SET DJLXMC = (SELECT ROSQLX_MC FROM ROSQLX B WHERE B.ROSQLX_NM = BW_Query_SXFX.DJLXID) 
WHERE EXISTS(SELECT 1 FROM ROSQLX C WHERE C.ROSQLX_NM = BW_Query_SXFX.DJLXID)
AND DJTYPE = 'WEBROJK'
;

DELETE FROM BW_Query_SXFX WHERE DJLXMC IS NULL;

DELETE FROM BW_Query_SXFX WHERE DJLXMC IN ('发票核减表单', '发票红冲作废申请单','集采额度',
'甲供材付款申请单', '甲供材付款申请调整单', '甲供材结算单批', '甲供材结算单批调整单',
'开票申请单', '经营单位额度', '进度款申请单', '进项红字信息申请单', '票据冲销单',
'收款认领单', '收款认领单（冲借款/预付款）', '项目产值进度单(非上线项目)', 
'项目产值进度单(上线)'
);


UPDATE BW_Query_SXFX SET DJZT = (SELECT FSYXDJ_DJZT FROM FSYXDJ B WHERE B.FSYXDJ_DJNM = BW_Query_SXFX.DJID) 
WHERE 1 = 1;

UPDATE BW_Query_SXFX SET DJZTMC = (SELECT FSSCYWZT_ZTSM FROM fsscywzt B WHERE B.FSSCYWZT_ZT = BW_Query_SXFX.DJZT AND B.FSSCYWZT_YWLX = 'YW') 
WHERE 1 = 1;

UPDATE BW_Query_SXFX SET TJRQ = (SELECT MIN(LSRWZTLOG_TIME) FROM LSRWZTLOG B WHERE B.LSRWZTLOG_DJID = BW_Query_SXFX.DJID AND B.LSRWZTLOG_GNID = 'ZD' AND LSRWZTLOG_CZ = '10')
WHERE 1 = 1;





/*
DELETE FROM BW_Query_SXFX1 WHERE 1 = 1;

INSERT INTO BW_Query_SXFX1(DJID, GNID, CZID, CNum)
SELECT LSRWZTLOG_DJID, LSRWZTLOG_GNID, '999', COUNT(0) FROM LSRWZTLOG
WHERE LSRWZTLOG_GNID = 'FSJS' 
AND LSRWZTLOG_CZ IN ('7', '9')
AND LSRWZTLOG_DJID IN (SELECT DJID FROM BW_Query_SXFX)
GROUP BY LSRWZTLOG_DJID, LSRWZTLOG_GNID--, LSRWZTLOG_CZ
ORDER BY COUNT(0) DESC;
*/

--BW


--BW

 SQLSTR := 'SELECT 
DJLXMC AS "单据类型" 
, DJBH AS "单据编号"
, DJZTMC AS "单据状态"
, Creator AS "制单人"
, JYZTMC AS "所属中心经营主体"
, BMMC AS "所属部门"
, TJRQ AS "第一次制单提交审批时间"
, ZDQJY AS "制单期间年"
, ZDQJM AS "制单期间月"
, BZAMT AS " 报账金额"
, TIME2 AS "扫描时间"            
FROM BW_Query_SXFX  where  1=1 ';
    --IF trim(SQLWHERE) IS NOT NULL THEN
      SQLSTR := SQLSTR || SQLWHERE;
   -- END IF;

--|| sqlwhere || ' ORDER BY DJBH, COrder ';

--EXECUTE IMMEDIATE SQLSTR;
    OPEN Re_CURSOR FOR SQLSTR;
end proc_SCAN;

end SXFX;