Repository navigation
Expand file tree
/
Copy pathEmployee_Details_Others.sql
More file actions
122 lines (117 loc) · 7.35 KB
/
Copy pathEmployee_Details_Others.sql
File metadata and controls
122 lines (117 loc) · 7.35 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
113
114
115
116
117
118
119
120
121
122
SELECT SECTIONNM,DEPARTMENTNM, lineno, cardno, empname, designation, BIRTH_DATE, joining_date, (grosssalary + GROSS_BK) grosssalary, OTHERS TUS
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND OTHERS > 0
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
--------------------- selected ------------
SELECT SECTIONNM,DEPARTMENTNM, lineno, cardno, empname, designation, BIRTH_DATE, joining_date, (grosssalary + GROSS_BK) grosssalary, OTHERS TUS
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND OTHERS > 0
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 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 active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT SECTIONNM,DEPARTMENTNM, lineno, cardno, empname, designation, BIRTH_DATE, joining_date, (grosssalary + GROSS_BK) grosssalary, OTHERS TUS
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND OTHERS > 0
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 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 active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT SECTIONNM,DEPARTMENTNM, lineno, cardno, empname, designation, BIRTH_DATE, joining_date, (grosssalary + GROSS_BK) grosssalary, OTHERS TUS
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND OTHERS > 0
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 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 active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT SECTIONNM,DEPARTMENTNM, lineno, cardno, empname, designation, BIRTH_DATE, joining_date, (grosssalary + GROSS_BK) grosssalary, OTHERS TUS
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND OTHERS > 0
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 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 active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT SECTIONNM,DEPARTMENTNM, lineno, cardno, empname, designation, BIRTH_DATE, joining_date, (grosssalary + GROSS_BK) grosssalary, OTHERS TUS
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND OTHERS > 0
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 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 active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT SECTIONNM,DEPARTMENTNM, lineno, cardno, empname, designation, BIRTH_DATE, joining_date, (grosssalary + GROSS_BK) grosssalary, OTHERS TUS
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND OTHERS > 0
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 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 active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT SECTIONNM,DEPARTMENTNM, lineno, cardno, empname, designation, BIRTH_DATE, joining_date, (grosssalary + GROSS_BK) grosssalary, OTHERS TUS
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND OTHERS > 0
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 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 active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT SECTIONNM,DEPARTMENTNM, lineno, cardno, empname, designation, BIRTH_DATE, joining_date, (grosssalary + GROSS_BK) grosssalary, OTHERS TUS
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND OTHERS > 0
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 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 active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT info.SECTIONNM,info.DEPARTMENTNM, info.lineno, info.cardno, info.empname, info.designation,
info.BIRTH_DATE, info.joining_date, (info.grosssalary + info.GROSS_BK) grosssalary, info.OTHERS TUS
FROM TB_PERSONAL_INFO info, TB_IDCARD_MULTIPLE mul
WHERE info.company = :p_company
AND info.company = mul.company
AND info.OTHERS > 0
AND mul.USER_NAME = :p_user
AND info.cardno = mul.cardno
ORDER BY DEPARTMENTNM, SECTIONNM, lineno, cardno ASC