-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathEmployee_Details_Inactive.sql
More file actions
118 lines (108 loc) · 7.08 KB
/
Employee_Details_Inactive.sql
File metadata and controls
118 lines (108 loc) · 7.08 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
SELECT DEPARTMENTNM,SECTIONNM,lineno,cardno,empname,FATHER_NAME,joining_date,RESIGNATION_DATE,designation,(grosssalary+GROSS_BK) GROSSSALARY
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND active = 1
AND DEPARTMENTNM LIKE DECODE(NVL(:p_deptname,'all'),'all','%',:p_deptname)
AND SECTIONNM LIKE DECODE(NVL(:p_section,'all'),'all','%',:p_section)
AND DESIGNATION LIKE DECODE(NVL(:p_desig,'all'),'all','%',:p_desig)
AND WORKERTYPE LIKE DECODE(NVL(:p_worker,'all'),'all','%',:p_worker)
AND FLOORNO LIKE DECODE(NVL(:p_floorno,'all'),'all','%',:p_floorno)
AND MACHINENO LIKE DECODE(NVL(:p_machineno,'all'),'all','%',:p_machineno)
AND lineno LIKE DECODE(NVL(:p_lineno,'all'),'all','%',:p_lineno)
AND SHIFT LIKE DECODE(NVL(:p_shift,'all'),'all','%',:p_shift)
AND GENDER LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND TO_CHAR(resignation_date,'rrrr') LIKE DECODE(NVL(:p_year,'all'),'all','%',:p_year)
AND RTRIM(TO_CHAR(resignation_date,'Month')) LIKE DECODE(NVL(:p_month,'all'),'all','%',:p_month)
AND cardno LIKE DECODE(NVL(:p_cardno,'all'),'all','%',:p_cardno)
AND active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
ORDER BY DEPARTMENTNM,SECTIONNM,lineno,cardno ASC
------------- selected --
SELECT DEPARTMENTNM,SECTIONNM,lineno,cardno,empname,FATHER_NAME,joining_date,RESIGNATION_DATE,designation,(grosssalary+GROSS_BK) GROSSSALARY
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND active = 1
AND DEPARTMENTNM IN ( SELECT ITEM_NAME FROM TB_SETUP_ITEM WHERE company =:p_company AND ITEM_NAME IS NOT NULL AND USER_NAME = :p_user)
AND GENDER LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND TO_CHAR(resignation_date,'rrrr') LIKE DECODE(NVL(:p_year,'all'),'all','%',:p_year)
AND RTRIM(TO_CHAR(resignation_date,'Month')) LIKE DECODE(NVL(:p_month,'all'),'all','%',:p_month)
AND active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT DEPARTMENTNM,SECTIONNM,lineno,cardno,empname,FATHER_NAME,joining_date,RESIGNATION_DATE,designation,(grosssalary+GROSS_BK) GROSSSALARY
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND active = 1
AND SECTIONNM IN ( SELECT ITEM_NAME FROM TB_SETUP_ITEM WHERE company =:p_company AND ITEM_NAME IS NOT NULL AND USER_NAME = :p_user)
AND GENDER LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND TO_CHAR(resignation_date,'rrrr') LIKE DECODE(NVL(:p_year,'all'),'all','%',:p_year)
AND RTRIM(TO_CHAR(resignation_date,'Month')) LIKE DECODE(NVL(:p_month,'all'),'all','%',:p_month)
AND active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT DEPARTMENTNM,SECTIONNM,lineno,cardno,empname,FATHER_NAME,joining_date,RESIGNATION_DATE,designation,(grosssalary+GROSS_BK) GROSSSALARY
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND active = 1
AND designation IN ( SELECT ITEM_NAME FROM TB_SETUP_ITEM WHERE company =:p_company AND ITEM_NAME IS NOT NULL AND USER_NAME = :p_user)
AND GENDER LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND TO_CHAR(resignation_date,'rrrr') LIKE DECODE(NVL(:p_year,'all'),'all','%',:p_year)
AND RTRIM(TO_CHAR(resignation_date,'Month')) LIKE DECODE(NVL(:p_month,'all'),'all','%',:p_month)
AND active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT DEPARTMENTNM,SECTIONNM,lineno,cardno,empname,FATHER_NAME,joining_date,RESIGNATION_DATE,designation,(grosssalary+GROSS_BK) GROSSSALARY
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND active = 1
AND WORKERTYPE IN ( SELECT ITEM_NAME FROM TB_SETUP_ITEM WHERE company =:p_company AND ITEM_NAME IS NOT NULL AND USER_NAME = :p_user)
AND GENDER LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND TO_CHAR(resignation_date,'rrrr') LIKE DECODE(NVL(:p_year,'all'),'all','%',:p_year)
AND RTRIM(TO_CHAR(resignation_date,'Month')) LIKE DECODE(NVL(:p_month,'all'),'all','%',:p_month)
AND active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT DEPARTMENTNM,SECTIONNM,lineno,cardno,empname,FATHER_NAME,joining_date,RESIGNATION_DATE,designation,(grosssalary+GROSS_BK) GROSSSALARY
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND active = 1
AND MACHINENO IN ( SELECT ITEM_NAME FROM TB_SETUP_ITEM WHERE company =:p_company AND ITEM_NAME IS NOT NULL AND USER_NAME = :p_user)
AND GENDER LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND TO_CHAR(resignation_date,'rrrr') LIKE DECODE(NVL(:p_year,'all'),'all','%',:p_year)
AND RTRIM(TO_CHAR(resignation_date,'Month')) LIKE DECODE(NVL(:p_month,'all'),'all','%',:p_month)
AND active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT DEPARTMENTNM,SECTIONNM,lineno,cardno,empname,FATHER_NAME,joining_date,RESIGNATION_DATE,designation,(grosssalary+GROSS_BK) GROSSSALARY
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND active = 1
AND FLOORNO IN ( SELECT ITEM_NAME FROM TB_SETUP_ITEM WHERE company =:p_company AND ITEM_NAME IS NOT NULL AND USER_NAME = :p_user)
AND GENDER LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND TO_CHAR(resignation_date,'rrrr') LIKE DECODE(NVL(:p_year,'all'),'all','%',:p_year)
AND RTRIM(TO_CHAR(resignation_date,'Month')) LIKE DECODE(NVL(:p_month,'all'),'all','%',:p_month)
AND active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT DEPARTMENTNM,SECTIONNM,lineno,cardno,empname,FATHER_NAME,joining_date,RESIGNATION_DATE,designation,(grosssalary+GROSS_BK) GROSSSALARY
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND active = 1
AND lineno IN ( SELECT ITEM_NAME FROM TB_SETUP_ITEM WHERE company =:p_company AND ITEM_NAME IS NOT NULL AND USER_NAME = :p_user)
AND GENDER LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND TO_CHAR(resignation_date,'rrrr') LIKE DECODE(NVL(:p_year,'all'),'all','%',:p_year)
AND RTRIM(TO_CHAR(resignation_date,'Month')) LIKE DECODE(NVL(:p_month,'all'),'all','%',:p_month)
AND active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT DEPARTMENTNM,SECTIONNM,lineno,cardno,empname,FATHER_NAME,joining_date,RESIGNATION_DATE,designation,(grosssalary+GROSS_BK) GROSSSALARY
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND active = 1
AND SHIFT IN ( SELECT ITEM_NAME FROM TB_SETUP_ITEM WHERE company =:p_company AND ITEM_NAME IS NOT NULL AND USER_NAME = :p_user)
AND GENDER LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND TO_CHAR(resignation_date,'rrrr') LIKE DECODE(NVL(:p_year,'all'),'all','%',:p_year)
AND RTRIM(TO_CHAR(resignation_date,'Month')) LIKE DECODE(NVL(:p_month,'all'),'all','%',:p_month)
AND active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT info.DEPARTMENTNM,info.SECTIONNM,info.lineno,info.cardno,info.empname,info.FATHER_NAME,info.joining_date,
info.RESIGNATION_DATE, info.designation,(info.grosssalary+info.GROSS_BK) GROSSSALARY
FROM TB_PERSONAL_INFO info, TB_IDCARD_MULTIPLE mul
WHERE info.company = :p_company
AND info.company = mul.company
AND mul.USER_NAME = :p_user
AND info.active = 1
AND info.cardno = mul.cardno
ORDER BY DEPARTMENTNM, SECTIONNM, lineno, cardno ASC