· 9 years ago · Nov 15, 2016, 09:18 PM
1DROP PROCEDURE IF EXISTS `get_police_station_for_hierarchy` ;
2
3DELIMITER $$
4
5 CREATE PROCEDURE `get_police_station_for_hierarchy`(officecd BIGINT,pstsaffcd VARCHAR(20))
6BEGIN
7DECLARE table_count INT;
8DECLARE officerInCharge VARCHAR(20);
9DROP TEMPORARY TABLE IF EXISTS temp1;
10DROP TEMPORARY TABLE IF EXISTS temp2;
11DROP TEMPORARY TABLE IF EXISTS temp3;
12DROP TEMPORARY TABLE IF EXISTS temp4;
13CREATE TEMPORARY TABLE temp1 (cd BIGINT,off_level INT);
14CREATE TEMPORARY TABLE temp2 (cd BIGINT,off_level INT);
15CREATE TEMPORARY TABLE temp3 (cd BIGINT,off_level INT);
16CREATE TEMPORARY TABLE temp4 (cd BIGINT,off_level INT);
17
18 SELECT OFFICE_INCHARGE_CD INTO officerInCharge FROM m_office_types WHERE OFFICE_CD=officecd ;
19 IF(officerInCharge=pstsaffcd) THEN
20INSERT INTO temp1
21SELECT m_office_hierarchy.OFFICE_CD,m_office_types.OFFICE_TYPE_CD FROM m_office_hierarchy,m_office_types WHERE SUPERVISOR_OFFICE_CD =officecd
22AND m_office_hierarchy.office_cd=m_office_types.office_cd AND m_office_hierarchy.lang_cd=m_office_types.lang_cd AND m_office_hierarchy.RECORD_STATUS <> 'D'
23AND m_office_types.RECORD_STATUS <> 'D' AND m_office_types.OFFICE_TYPE_CD=7;
24INSERT INTO temp2
25SELECT m_office_hierarchy.OFFICE_CD,m_office_types.OFFICE_TYPE_CD FROM m_office_hierarchy,m_office_types WHERE SUPERVISOR_OFFICE_CD=officecd
26AND m_office_hierarchy.office_cd=m_office_types.office_cd AND m_office_hierarchy.lang_cd=m_office_types.lang_cd
27AND m_office_hierarchy.RECORD_STATUS <> 'D' AND m_office_types.RECORD_STATUS <> 'D' AND m_office_types.OFFICE_TYPE_CD <> 7 ;
28SELECT COUNT(*) INTO table_count FROM temp2;
29WHILE (table_count>0)
30DO
31INSERT INTO temp1
32SELECT m_office_hierarchy.OFFICE_CD,m_office_types.OFFICE_TYPE_CD FROM m_office_hierarchy,m_office_types WHERE SUPERVISOR_OFFICE_CD IN (SELECT cd FROM temp2)
33AND m_office_hierarchy.office_cd=m_office_types.office_cd AND m_office_hierarchy.lang_cd=m_office_types.lang_cd
34 AND m_office_hierarchy.RECORD_STATUS <> 'D' AND m_office_types.RECORD_STATUS <> 'D' AND m_office_types.OFFICE_TYPE_CD=7;
35INSERT INTO temp3
36SELECT m_office_hierarchy.OFFICE_CD,m_office_types.OFFICE_TYPE_CD FROM m_office_hierarchy,m_office_types WHERE SUPERVISOR_OFFICE_CD IN (SELECT cd FROM temp2)
37AND m_office_hierarchy.office_cd=m_office_types.office_cd AND m_office_hierarchy.lang_cd=m_office_types.lang_cd
38AND m_office_hierarchy.RECORD_STATUS <> 'D' AND m_office_types.RECORD_STATUS <> 'D' AND m_office_types.OFFICE_TYPE_CD <> 7 AND m_office_hierarchy.OFFICE_CD NOT IN (SELECT cd FROM temp4) AND m_office_hierarchy.OFFICE_CD <> officecd ;
39DELETE FROM temp2;
40INSERT INTO temp2 SELECT * FROM temp3;
41INSERT INTO temp4 SELECT * FROM temp3;
42DELETE FROM temp3;
43SELECT COUNT(*) INTO table_count FROM temp2;
44END WHILE;
45SELECT DISTINCT cd,M_OFFICE_TYPES.office_name FROM temp1,m_office_types WHERE temp1.cd=m_office_types.office_cd AND m_office_types.record_status <> 'D';
46ELSE
47SELECT '' ;
48END IF ;
49DROP TABLE temp1;
50DROP TABLE temp2;
51DROP TABLE temp3;
52DROP TABLE temp4;
53END $$
54DELIMITER ;
55
56create or replace PROCEDURE get_police_statn_for_hierarchy(officecd IN NUMBER,pstsaffcd IN VARCHAR2)
57 as
58 v_refcur SYS_REFCURSOR;
59 table_count NUMBER(10,0);
60 officerInCharge VARCHAR2(20);
61 BEGIN
62 -- This procedure was converted on Mon Nov 14 14:57:57 2016 using Ispirer SQLWays 7.0 Build 3434 64bit Licensed to prabhat.gang@gmail.com - prabhat - India (Demo License, Ispirer MnMTK 2015 Mysql to Oracle Database Migration, 1 month, 20161114).
63
64 EXECUTE IMMEDIATE ' TRUNCATE TABLE temp1 ';
65 EXECUTE IMMEDIATE ' TRUNCATE TABLE temp2 ';
66 EXECUTE IMMEDIATE ' TRUNCATE TABLE temp3 ';
67
68 INSERT INTO temp1
69 SELECT m_office_hierarchy.OFFICE_CD,m_office_types.OFFICE_TYPE_CD FROM m_office_hierarchy,m_office_types WHERE SUPERVISOR_OFFICE_CD = officecd
70 and m_office_hierarchy.office_cd = m_office_types.office_cd and m_office_hierarchy.lang_cd = m_office_types.lang_cd and m_office_hierarchy.RECORD_STATUS <> 'D'
71 and m_office_types.RECORD_STATUS <> 'D' and m_office_types.OFFICE_TYPE_CD = 7;
72 INSERT INTO temp2
73 SELECT m_office_hierarchy.OFFICE_CD,m_office_types.OFFICE_TYPE_CD FROM m_office_hierarchy,m_office_types WHERE SUPERVISOR_OFFICE_CD = officecd
74 and m_office_hierarchy.office_cd = m_office_types.office_cd and m_office_hierarchy.lang_cd = m_office_types.lang_cd
75 and m_office_hierarchy.RECORD_STATUS <> 'D' and m_office_types.RECORD_STATUS <> 'D' and m_office_types.OFFICE_TYPE_CD <> 7;
76 SELECT COUNT(*) INTO table_count FROM temp2;
77 WHILE (table_count > 0) loop
78 INSERT INTO temp1
79 SELECT m_office_hierarchy.OFFICE_CD,m_office_types.OFFICE_TYPE_CD FROM m_office_hierarchy,m_office_types WHERE SUPERVISOR_OFFICE_CD IN(SELECT cd FROM temp2)
80 and m_office_hierarchy.office_cd = m_office_types.office_cd and m_office_hierarchy.lang_cd = m_office_types.lang_cd
81 and m_office_hierarchy.RECORD_STATUS <> 'D' and m_office_types.RECORD_STATUS <> 'D' and m_office_types.OFFICE_TYPE_CD = 7;
82 INSERT INTO temp3
83 SELECT m_office_hierarchy.OFFICE_CD,m_office_types.OFFICE_TYPE_CD FROM m_office_hierarchy,m_office_types WHERE SUPERVISOR_OFFICE_CD IN(SELECT cd FROM temp2)
84 and m_office_hierarchy.office_cd = m_office_types.office_cd and m_office_hierarchy.lang_cd = m_office_types.lang_cd
85 and m_office_hierarchy.RECORD_STATUS <> 'D' and m_office_types.RECORD_STATUS <> 'D' and m_office_types.OFFICE_TYPE_CD <> 7;
86 DELETE FROM temp2;
87 INSERT INTO temp2 SELECT * FROM temp3;
88 DELETE FROM temp3;
89 SELECT COUNT(*) INTO table_count FROM temp2;
90 table_count := 100;
91 end loop;
92 open v_refcur for SELECT DISTINCT cd,M_OFFICE_TYPES.office_name FROM temp1,m_office_types WHERE temp1.cd = m_office_types.office_cd and m_office_types.record_status <> 'D';
93 dbms_sql.return_result(v_refcur);
94
95 EXECUTE IMMEDIATE ' TRUNCATE TABLE temp1 ';
96 EXECUTE IMMEDIATE ' TRUNCATE TABLE temp2 ';
97 EXECUTE IMMEDIATE ' TRUNCATE TABLE temp3 ';
98
99 close v_refcur;
100 END;