-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathEmployee_rescue.sql
More file actions
136 lines (128 loc) · 7.78 KB
/
Employee_rescue.sql
File metadata and controls
136 lines (128 loc) · 7.78 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
123
124
125
126
127
128
129
130
131
132
133
134
135
SELECT emp.cardno, emp.empname, emp.designation, emp.SECTIONNM, emp.joining_date, emp.TEAM_JOIN_DATE, emp.lineno, emp.TEAM_NAME, emp.TEAM_DESIGNATION,
pic.EMPPICTURE, sing.EMPSIGN
FROM tb_personal_info emp, TB_PERSONAL_INFO_PICTURE pic, TB_PERSONAL_INFO_SIGN sing
WHERE emp.company = :p_company
AND emp.company = pic.company
AND emp.company = sing.company
AND emp.cardno = pic.cardno
AND emp.cardno = sing.cardno
AND emp.TEAM_NAME IS NOT NULL
AND emp.DEPARTMENTNM LIKE DECODE(NVL(:p_deptname,'all'),'all','%',:p_deptname)
AND emp.SECTIONNM LIKE DECODE(NVL(:p_section,'all'),'all','%',:p_section)
AND emp.DESIGNATION LIKE DECODE(NVL(:p_desig,'all'),'all','%',:p_desig)
AND emp.WORKERTYPE LIKE DECODE(NVL(:p_worker,'all'),'all','%',:p_worker)
AND emp.FLOORNO LIKE DECODE(NVL(:p_floorno,'all'),'all','%',:p_floorno)
AND emp.MACHINENO LIKE DECODE(NVL(:p_machineno,'all'),'all','%',:p_machineno)
AND emp.lineno LIKE DECODE(NVL(:p_lineno,'all'),'all','%',:p_lineno)
AND emp.SHIFT LIKE DECODE(NVL(:p_shift,'all'),'all','%',:p_shift)
AND emp.GENDER LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND emp.CASH_TYPE LIKE DECODE(NVL(:p_paytype,'all'),'all','%',:p_paytype)
AND emp.BANK_NAME LIKE DECODE(NVL(:p_bkname,'all'),'all','%',:p_bkname)
AND emp.MFT_TYPE LIKE DECODE(NVL(:p_mft_type,'all'),'all','%',:p_mft_type)
AND emp.cardno LIKE DECODE(NVL(:p_cardno,'all'),'all','%',:p_cardno)
AND emp.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
ORDER BY emp.TEAM_NAME, emp.TEAM_DESIGNATION DESC
------------- seletced ----
SELECT a.cardno, a.empname, a.designation, a.SECTIONNM, a.joining_date, a.TEAM_JOIN_DATE, a.lineno, a.TEAM_NAME, a.TEAM_DESIGNATION, pic.EMPPICTURE, sing.EMPSIGN
FROM
(SELECT cardno, empname, designation, SECTIONNM, joining_date, TEAM_JOIN_DATE, lineno, TEAM_NAME, TEAM_DESIGNATION
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND TEAM_NAME IS NOT NULL
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 cardno, empname, designation, SECTIONNM, joining_date, TEAM_JOIN_DATE, lineno, TEAM_NAME, TEAM_DESIGNATION
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND TEAM_NAME IS NOT NULL
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 cardno, empname, designation, SECTIONNM, joining_date, TEAM_JOIN_DATE, lineno, TEAM_NAME, TEAM_DESIGNATION
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND TEAM_NAME IS NOT NULL
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 cardno, empname, designation, SECTIONNM, joining_date, TEAM_JOIN_DATE, lineno, TEAM_NAME, TEAM_DESIGNATION
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND TEAM_NAME IS NOT NULL
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 cardno, empname, designation, SECTIONNM, joining_date, TEAM_JOIN_DATE, lineno, TEAM_NAME, TEAM_DESIGNATION
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND TEAM_NAME IS NOT NULL
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 cardno, empname, designation, SECTIONNM, joining_date, TEAM_JOIN_DATE, lineno, TEAM_NAME, TEAM_DESIGNATION
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND TEAM_NAME IS NOT NULL
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 cardno, empname, designation, SECTIONNM, joining_date, TEAM_JOIN_DATE, lineno, TEAM_NAME, TEAM_DESIGNATION
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND TEAM_NAME IS NOT NULL
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 cardno, empname, designation, SECTIONNM, joining_date, TEAM_JOIN_DATE, lineno, TEAM_NAME, TEAM_DESIGNATION
FROM TB_PERSONAL_INFO
WHERE company = :p_company
AND TEAM_NAME IS NOT NULL
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.cardno, info.empname, info.designation, info.SECTIONNM, info.joining_date, info.TEAM_JOIN_DATE, info.lineno, info.TEAM_NAME, info.TEAM_DESIGNATION
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.TEAM_NAME IS NOT NULL
AND info.cardno = mul.cardno) a,
TB_PERSONAL_INFO_PICTURE pic, TB_PERSONAL_INFO_SIGN sing
WHERE pic.company = :p_company
AND sing.company = pic.company
AND pic.cardno = a.cardno
AND sing.cardno = a.cardno
ORDER BY a.TEAM_NAME, a.TEAM_DESIGNATION DESC