Payroll_problem_SQL/Employee_short_info.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
SELECT cardno, secreteno, empname, designation,  lineno, SECTIONNM, DEPARTMENTNM
FROM TB_PERSONAL_INFO
WHERE company =:p_company
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   CASH_TYPE    LIKE DECODE(NVL(:p_paytype,'all'),'all','%',:p_paytype)
AND   BANK_NAME    LIKE DECODE(NVL(:p_bkname,'all'),'all','%',:p_bkname)
AND   MFT_TYPE     LIKE DECODE(NVL(:p_mft_type,'all'),'all','%',:p_mft_type)
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


----------------------------------------


SELECT cardno, secreteno, empname, designation,  lineno, SECTIONNM, DEPARTMENTNM
FROM   TB_PERSONAL_INFO
WHERE  company  =:p_company
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    active        LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT cardno, secreteno, empname, designation,  lineno, SECTIONNM, DEPARTMENTNM
FROM   TB_PERSONAL_INFO
WHERE  company  =:p_company
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    active     LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT cardno, secreteno, empname, designation,  lineno, SECTIONNM, DEPARTMENTNM
FROM   TB_PERSONAL_INFO
WHERE  company  =:p_company
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    active      LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT cardno, secreteno, empname, designation,  lineno, SECTIONNM, DEPARTMENTNM
FROM   TB_PERSONAL_INFO
WHERE  company  =:p_company
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    active      LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT cardno, secreteno, empname, designation,  lineno, SECTIONNM, DEPARTMENTNM
FROM   TB_PERSONAL_INFO
WHERE  company  =:p_company
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    active   LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT cardno, secreteno, empname, designation,  lineno, SECTIONNM, DEPARTMENTNM
FROM   TB_PERSONAL_INFO
WHERE  company   =:p_company
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    active    LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT cardno, secreteno, empname, designation,  lineno, SECTIONNM, DEPARTMENTNM
FROM   TB_PERSONAL_INFO
WHERE  company =:p_company
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    active  LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT cardno, secreteno, empname, designation,  lineno, SECTIONNM, DEPARTMENTNM
FROM   TB_PERSONAL_INFO
WHERE  company =:p_company
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    active  LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT emp.cardno, emp.secreteno, emp.empname, emp.designation, emp.lineno, emp.SECTIONNM, emp.DEPARTMENTNM
FROM   TB_PERSONAL_INFO emp, TB_IDCARD_MULTIPLE mul
WHERE  emp.company   =:p_company
AND    emp.company   = mul.company
AND    mul.USER_NAME = :p_user
AND    emp.cardno    = mul.cardno
AND    emp.GENDER LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND    emp.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
ORDER BY DEPARTMENTNM,SECTIONNM,lineno,cardno ASC