Payroll_problem_SQL/Employee_Details_Inactive.sql at master · anik112/Payroll_problem_SQL · GitHub

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