SQL SHOW系列（四）

 Those SQLs is base on Oracle, includes make job, do procedure with function, and package them. 

Function 

1. CREATE OR REPLACE FUNCTION F_GETREPLYINFO(BO_PROJECT_ID IN VARCHAR2,VERSION IN VARCHAR2) 
2.   RETURN VARCHAR2 IS 
3.   V_PROJECT_ID         VARCHAR2(255); 
4.   V_VERSION            VARCHAR2(255); 
5.   V_REPLY              VARCHAR2(255); 
6.  
7. BEGIN 
8.   V_PROJECT_ID         := BO_PROJECT_ID; 
9.   V_VERSION            := VERSION; 
10.   V_REPLY              := ''; 
11.  
12. SELECT TO_CHAR(T.JHRWSJHYQWCDATE, 'YYYY-MM-DD') 
13.  || ';' || T.JHRWSJSGM || ';' 
14.  || T.JHRWSZTZ  INTO V_REPLY  
15.   FROM BO_JHRWS_VER T 
16.    WHERE T.BO_PROJECT_ID = V_PROJECT_ID  
17.  AND T.VERIONNUMBER = (TO_CHAR(TO_NUMBER(V_VERSION)+1) || '.0'); 
18.  
19.   RETURN(V_REPLY); 
20. EXCEPTION 
21.   WHEN OTHERS THEN 
22.     RETURN(''); 
23. END F_GETREPLYINFO; 
24. /** 
25.   * 函数: 根据项目ID和版本号获取该项目的下一个版本的信息 
26.   * 作者: ZHOU DANYONG 2010-11-04 
27.   **/ 

PROCEDURE

1. CREATE OR REPLACE PROCEDURE P_ENGINEERING_ACCOUNTS IS 
2. BEGIN 
3. /* 
4.  *删除临时表 
5.  */ 
6. DELETE FROM TEM_ENGINEERING_ACCOUNTS; 
7.  
8.   /* 
9.    * 工程台账数据查询并写入临时表 
10.    */ 
11.  INSERT INTO TEM_ENGINEERING_ACCOUNTS (MINEPROJ, BO_PROJECT_ID, ENGINEERING_TYPE, IS_RECIEVED, MISPROJECTCODE, 
12.   SENDSTATE, JHRWSJSDW, SPECIALTYGROUP, SETSPECIALTYGROUPDATE, LEADER, DEPTMENTPATH, LEADERCREATEDATE, FINANCE_MANAGER,  
13.   ENG_SPECIALTY_TYPE, ENGINEERING_STATE, RECIEVEDATE, STARTDATE, COMPLETEDATE, REMARK, DESIGNUNIT, SUPERVISIONUNIT,  
14.   CONSTRUCTIONUNIT, BO_ENGINEERING_ID, ISSURE, BO_JHRWS_ID, ERPTEMPLATE, BUILD_DEPT, PLANARRIVALDATE, PLANTASKHISID,  
15.   PLANSCHEDULE, TOTALINVEST, YEARINVEST, BUILDINGSCALE, MAINTAINCORP, PROJECT_CODE, PROJECT_NAME, PLAN_MANAGER, YEAR, 
16.    PROJECT_TYPE, PROJECT_TYPE_NAME, TOTAL_INVEST, MANAGE_ATTRIBUTE, ABC_CLASS, GROUP_CODE, PRIMARY_CONTENT,  
17.    INTEND_START_DATE, INTEND_ISSUE_DATE, INTEND_END_DATE, JHRWSZTZ, JHRWSNDTZ, ENG_SPECIALTY_TYPE_NAME, JHRWSJHXDDATE,  
18.    JHRWSJHYQWCDATE, JHRWSJHYQTZDATE, PLAN_MANAGER_NAME, SPECIALTYGROUP_NAME, LEADER_NAME, BO_BLANKTEMPLATE_ID,  
19.    IS_SHUDI_PROJECT, BUILD_DEPT_NAME, SJPFTZ, SJPFTZTZ, PREDATE, FINALDATE, CHECK_COST_BUDGET, YEAR_INVEST2, TOTAL_INVEST2,  
20.    SGT_MEETTINGTIME, SJ_MEETTINGTIME, PRE_DESIGN_FEE, PRE_SUPERVISION_FEE, PRE_BUILD_BUDGET, CHECK_AMOUNT, PLANMAPPLYTIME, 
21.     PLANMDELAY, PLANMCONTENT, PLANMINVEST, PLANMREPLY, LASTASSESSNAME) 
22.   SELECT NVL2(E.STARTDATE, 1, 0) MINEPROJ, 
23.        E.*, 
24.        TO_CHAR(E.TOTALINVEST, 'FM9999999999990.00') AS JHRWSZTZ, 
25.        TO_CHAR(E.YEARINVEST, 'FM9999999999990.00') AS JHRWSNDTZ, 
26.        DECODE(E.ENG_SPECIALTY_TYPE, 
27.               '1', 
28.               '传输设备专业', 
29.               '2', 
30.               '通信设备专业', 
31.               '3', 
32.               '光缆线路专业', 
33.               '4', 
34.               '无线专业', 
35.               '5', 
36.               '通信管道专业', 
37.               '6', 
38.               '室内覆盖专业', 
39.               '7', 
40.               '建筑专业') AS ENG_SPECIALTY_TYPE_NAME, 
41.        TO_CHAR(E.PLANARRIVALDATE, 'YYYY-MM-DD') AS JHRWSJHXDDATE, 
42.        TO_CHAR(E.PLANSCHEDULE, 'YYYY-MM-DD') AS JHRWSJHYQWCDATE, 
43.        (SELECT TO_CHAR(JHRWSJHYQWCDATE, 'YYYY-MM-DD') 
44.           FROM BO_JHRWS_VER 
45.          WHERE BO_PROJECT_ID = E.BO_PROJECT_ID 
46.            AND VERIONNUMBER = '1.0') JHRWSJHYQTZDATE, 
47.        (SELECT U.FULLNAME FROM V_USER U WHERE U.USERID = E.PLAN_MANAGER) PLAN_MANAGER_NAME, 
48.        (SELECT V.ORGANIZATIONNAME 
49.           FROM V_ORG V 
50.          WHERE V.ORGANIZATIONID = E.SPECIALTYGROUP) SPECIALTYGROUP_NAME, 
51.        V1.FULLNAME LEADER_NAME, 
52.        '' BO_BLANKTEMPLATE_ID, 
53.        (SELECT DECODE(COMMENTS, '2', '1', '2') 
54.           FROM ORGANIZATION_ 
55.          WHERE ORGANIZATIONID = E.BUILD_DEPT) IS_SHUDI_PROJECT, 
56.        (SELECT NAME FROM ORGANIZATION_ WHERE ORGANIZATIONID = E.BUILD_DEPT) BUILD_DEPT_NAME, 
57.        TO_CHAR(S1.INVESTBUDGET, 'FM9999999999990.00') SJPFTZ, 
58.        TO_CHAR(S2.INVEST, 'FM9999999999990.00') SJPFTZTZ, 
59.        V2.FACTCOMPDATE PREDATE, 
60.        V3.FACTCOMPDATE FINALDATE, 
61.        V4.CHECK_COST_BUDGET CHECK_COST_BUDGET, 
62.        TO_CHAR(V5.YEAR_INVEST, 'FM9999999999990.00') YEAR_INVEST2, 
63.        TO_CHAR(V5.TOTAL_INVEST, 'FM9999999999990.00') TOTAL_INVEST2, 
64.        TO_CHAR(V6.MEETTINGTIME, 'YYYY-MM-DD') AS SGT_MEETTINGTIME, 
65.        TO_CHAR(V7.MEETTINGTIME, 'YYYY-MM-DD') AS SJ_MEETTINGTIME, 
66.        TO_CHAR(V7.PRE_DESIGN_FEE, 'FM9999999999990.00') PRE_DESIGN_FEE, 
67.        TO_CHAR(V7.PRE_SUPERVISION_FEE, 'FM9999999999990.00') PRE_SUPERVISION_FEE, 
68.        TO_CHAR(V7.PRE_BUILD_BUDGET, 'FM9999999999990.00') PRE_BUILD_BUDGET, 
69.        TO_CHAR(T8.CHECK_AMOUNT, 'FM9999999999990.00') CHECK_AMOUNT, 
70.        DECODE(TO_CHAR(T9.JHRWSBGSQDATE, 'YYYY-MM-DD'), '', '无') AS PLANMAPPLYTIME, 
71.        DECODE(TO_CHAR(T9.DELAYDATE, 'YYYY-MM-DD'), '', '无') AS PLANMDELAY, 
72.        DECODE(TO_CHAR(T9.JHRWSBGSQCS_RY, 'YYYY-MM-DD'), '', '无') AS PLANMCONTENT, 
73.        DECODE(T9.JHRWSBGSQCB_BM, '', '无') PLANMINVEST, 
74.        DECODE(F_GETREPLYINFO(XX.BO_PROJECT_ID, XX.VERIONNUMBER), '', '无') PLANMREPLY, /*F_GETLASTASSESSNAME(E.BO_ENGINEERING_ID, E.BUILD_DEPT)*/ 
75.        '数据正在获取中...' LASTASSESSNAME 
76.   FROM V_BO_ENGINEERING E, 
77.        V_USER V1, 
78.        BO_DESIGNAPPROVE_VER S1, 
79.        V_LASTEFFECTED_DESIGNAGREE S2, 
80.        (SELECT * FROM V_BO_TASK_ITEM1_2_PLAN T WHERE T.ASSESSCODE = 'WCCYSJ') V2, 
81.        (SELECT * FROM V_BO_TASK_ITEM1_2_PLAN T WHERE T.ASSESSCODE = 'WCJGSJ') V3, 
82.        (SELECT * 
83.           FROM (SELECT DISTINCT * 
84.                   FROM V_BO_DESIGNCHECKUP_2_ENG T 
85.                  WHERE T.IS_PASS_DESIGNCHECK = '1' 
86.                  ORDER BY T.SGJDJYTIME DESC) 
87.          WHERE ROWNUM = 1) V4, 
88.        (SELECT TT.* 
89.           FROM (SELECT T.*, 
90.                        RANK() OVER(PARTITION BY T.BO_PROJECT_ID ORDER BY T.YEAR DESC) NN 
91.                   FROM V_PROJECT_GCSB T) TT 
92.          WHERE TT.NN = '1') V5, 
93.        (SELECT * 
94.           FROM (SELECT B.*, 
95.                        RANK() OVER(PARTITION BY B.BO_ENGINEERING_ID ORDER BY B.CREATEDATE DESC) Z 
96.                   FROM (SELECT DISTINCT T.BO_ENGINEERING_ID, 
97.                                         T.MEETTINGTIME, 
98.                                         T.CREATEDATE 
99.                           FROM V_BO_DESIGNCHECKUP_2_ENG T 
100.                          WHERE T.TYPE = '2') B) BB 
101.          WHERE BB.Z = 1) V6, 
102.        (SELECT * 
103.           FROM (SELECT B.*, 
104.                        RANK() OVER(PARTITION BY B.BO_ENGINEERING_ID ORDER BY B.CREATEDATE DESC) Z 
105.                   FROM (SELECT T.BO_ENGINEERING_ID, 
106.                                T.MEETTINGTIME, 
107.                                T.PRE_DESIGN_FEE, 
108.                                T.PRE_SUPERVISION_FEE, 
109.                                T.PRE_BUILD_BUDGET, 
110.                                T.CREATEDATE 
111.                           FROM V_BO_DESIGNCHECKUP_2_ENG T 
112.                          WHERE T.TYPE IS NULL 
113.                             OR T.TYPE = '1') B) BB 
114.          WHERE BB.Z = 1) V7, 
115.        (SELECT * 
116.           FROM (SELECT B.*, 
117.                        RANK() OVER(PARTITION BY B.BO_CONTRACT_CHECK_INFO_ID ORDER BY B.CREATORDATE DESC) Z 
118.                   FROM BO_CONTRACT_CHECK_INFO B) BB 
119.          WHERE BB.Z = 1) T8, 
120.        (SELECT * 
121.           FROM (SELECT B.*, 
122.                        RANK() OVER(PARTITION BY B.BO_ENGINEERING_ID ORDER BY B.CREATEDATE DESC) Z 
123.                   FROM (SELECT T.* 
124.                           FROM BO_JHRWSBGSQ T 
125.                          WHERE T.WORKFLOW_STATE = '4') B) BB 
126.          WHERE BB.Z = 1) T9, 
127.        (SELECT * 
128.           FROM (SELECT * 
129.                   FROM (SELECT B.*, 
130.                                RANK() OVER(PARTITION BY B.BO_ENGINEERING_ID ORDER BY B.CREATEDATE DESC) Z 
131.                           FROM BO_JHRWSBGSQ B 
132.                          WHERE B.WORKFLOW_STATE = '4') BB 
133.                  WHERE BB.Z = 1) BBB, 
134.                BO_JHRWS_VER Z 
135.          WHERE BBB.BO_JHRWS_VER_ID = Z.BO_JHRWS_VER_ID(+)) XX 
136.  WHERE E.ENGINEERING_STATE = 2 
137.    AND E.BO_PROJECT_ID = V5.BO_PROJECT_ID(+) 
138.    AND E.BO_ENGINEERING_ID = V6.BO_ENGINEERING_ID(+) 
139.    AND E.BO_ENGINEERING_ID = V7.BO_ENGINEERING_ID(+) 
140.    AND E.BO_ENGINEERING_ID = T8.BO_CONTRACT_CHECK_INFO_ID(+) 
141.    AND E.BO_ENGINEERING_ID = T9.BO_ENGINEERING_ID(+) 
142.    AND E.BO_ENGINEERING_ID = XX.BO_ENGINEERING_ID(+) 
143.    AND V1.USERID = E.LEADER(+) 
144.    AND S1.BO_PROJECT_ID(+) = E.BO_PROJECT_ID 
145.    AND S1.VERIONNUMBER(+) = '1.0' 
146.    AND S2.PROJECTID(+) = E.BO_PROJECT_ID 
147.    AND E.BO_ENGINEERING_ID = V2.BO_ENGINEERING_ID(+) 
148.    AND E.BO_ENGINEERING_ID = V3.BO_ENGINEERING_ID(+) 
149.    AND E.BO_ENGINEERING_ID = V4.BO_ENGINEERING_ID(+) 
150. ; 
151.  
152. COMMIT; 
153. /**出错回滚**/ 
154. EXCEPTION 
155.   WHEN OTHERS THEN ROLLBACK; 
156.  
157. END P_ENGINEERING_ACCOUNTS; 
158. /** 
159.   * P_ENGINEERING_ACCOUNTS 
160.   * 作者：Zhou Danyong 
161.   * 日期：2010-11-05 
162.   **/ 

PACKAGE

1. CREATE OR REPLACE PACKAGE PKG_ACCOUNTS IS 
2.   -- Author  : Zhou Danyong 
3.   -- Created : 2010-11-05 
4.   -- Purpose : 工程台账  
5.   FUNCTION F_GETREPLYINFO(BO_PROJECT_ID IN VARCHAR2,VERSION IN VARCHAR2) RETURN VARCHAR2 ; 
6.   PROCEDURE P_ENGINEERING_ACCOUNTS; 
7.    
8. END PKG_ACCOUNTS; 

Job

1. variable job NUMBER; 
2. begin 
3.   sys.dbms_job.submit(job => :job, 
4.                       what => 'PKG_ACCOUNTS.P_ENGINEERING_ACCOUNTS;', 
5.                       next_date => to_date('06-11-2010 23:59:59', 'dd-mm-yyyy hh24:mi:ss'), 
6.                       interval => 'SYSDATE+ 1'); 
7.   commit; 
8. end; 
9. /