-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathEmployee_resume.sql
More file actions
203 lines (165 loc) · 10.8 KB
/
Employee_resume.sql
File metadata and controls
203 lines (165 loc) · 10.8 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
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
SELECT info.company,info.departmentnm, info.sectionnm, info.lineno, info.cardno,info.secreteno,info.empname,info.father_name,info.mother_name,
info.housbandname, info.joining_date, info.designation, info.salary_grade, (info.grosssalary + info.gross_bk)grosssalary, info.resignation_date,
info.birth_date, info.workertype, pic.emppicture, info.tsalary, info.gender, info.bloodgroup, other.book_sl_no, other.open_date, other.purposeofwork,
other.perhrproduction, other.performance, other.nid, other.ref1, other.ref2, info.present_address, info.present_po, info.present_ps, info.present_dist,
info.permanentaddress, info.permanent_po, info.permanent_ps, info.permanent_dist
FROM tb_personal_info info, tb_personal_info_other other, tb_personal_info_picture pic
WHERE info.company =:p_company
AND info.company = pic.company
AND info.company = other.company
AND info.cardno = other.cardno
AND info.cardno = pic.cardno
AND TO_CHAR(info.joining_date,'RRRR') LIKE DECODE(NVL(:p_year,'all'),'all','%',:p_year)
AND RTRIM(TO_CHAR(info.joining_date,'Month')) LIKE DECODE(NVL(:p_month,'all'),'all','%',:p_month)
AND info.departmentnm LIKE DECODE(NVL(:p_deptname,'all'),'all','%',:p_deptname)
AND info.sectionnm LIKE DECODE(NVL(:p_section,'all'),'all','%',:p_section)
AND info.lineno LIKE DECODE(NVL(:p_lineno,'all'),'all','%',:p_lineno)
AND info.designation LIKE DECODE(NVL(:p_desig,'all'),'all','%',:p_desig)
AND info.workertype LIKE DECODE(NVL(:p_worker,'all'),'all','%',:p_worker)
AND info.floorno LIKE DECODE(NVL(:p_floorno,'all'),'all','%',:p_floorno)
AND info.machineno LIKE DECODE(NVL(:p_machineno,'all'),'all','%',:p_machineno)
AND info.shift LIKE DECODE(NVL(:p_shift,'all'),'all','%',:p_shift)
AND info.cardno LIKE DECODE (NVL(:p_cardno,'all'),'all','%',:p_cardno)
AND info.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
ORDER BY info.departmentnm, info.sectionnm, info.lineno,info.cardno ASC
----------- Education
SELECT edu.HIGHEST_DEGREE educa_hdegree, edu.LEVEL_DEGREE educa_level, edu.INSTITUTE_NAME educa_instite,
edu.PASSING_YR educa_yr, edu.CLASS_GRADE educa_grd, edu.SUBJECT educa_sub, info.company, info.cardno
FROM tb_personal_info info, TB_PERSONAL_EDUCATIONAL edu
WHERE info.company = :p_company
AND info.company = edu.company
AND info.cardno = edu.cardno
AND TO_CHAR(info.joining_date,'RRRR') LIKE DECODE(NVL(:p_year,'all'),'all','%',:p_year)
AND RTRIM(TO_CHAR(info.joining_date,'Month')) LIKE DECODE(NVL(:p_month,'all'),'all','%',:p_month)
AND info.departmentnm LIKE DECODE(NVL(:p_deptname,'all'),'all','%',:p_deptname)
AND info.sectionnm LIKE DECODE(NVL(:p_section,'all'),'all','%',:p_section)
AND info.lineno LIKE DECODE(NVL(:p_lineno,'all'),'all','%',:p_lineno)
AND info.designation LIKE DECODE(NVL(:p_desig,'all'),'all','%',:p_desig)
AND info.workertype LIKE DECODE(NVL(:p_worker,'all'),'all','%',:p_worker)
AND info.floorno LIKE DECODE(NVL(:p_floorno,'all'),'all','%',:p_floorno)
AND info.machineno LIKE DECODE(NVL(:p_machineno,'all'),'all','%',:p_machineno)
AND info.shift LIKE DECODE(NVL(:p_shift,'all'),'all','%',:p_shift)
AND info.cardno LIKE DECODE (NVL(:p_cardno,'all'),'all','%',:p_cardno)
AND info.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
ORDER BY edu.PASSING_YR DESC
---------------- Employeement
SELECT ment.companyname employ_company, ment.start_date employ_sdate, ment.end_date employ_edate, ment.designation employ_desig,
ment.LOCATION employ_location, ment.employer_type employ_type, info.company, info.cardno
FROM tb_personal_info info, tb_personal_employment ment
WHERE info.company = :p_company
AND info.company = ment.company
AND info.cardno = ment.cardno
AND TO_CHAR(info.joining_date,'RRRR') LIKE DECODE(NVL(:p_year,'all'),'all','%',:p_year)
AND RTRIM(TO_CHAR(info.joining_date,'Month')) LIKE DECODE(NVL(:p_month,'all'),'all','%',:p_month)
AND info.departmentnm LIKE DECODE(NVL(:p_deptname,'all'),'all','%',:p_deptname)
AND info.sectionnm LIKE DECODE(NVL(:p_section,'all'),'all','%',:p_section)
AND info.lineno LIKE DECODE(NVL(:p_lineno,'all'),'all','%',:p_lineno)
AND info.designation LIKE DECODE(NVL(:p_desig,'all'),'all','%',:p_desig)
AND info.workertype LIKE DECODE(NVL(:p_worker,'all'),'all','%',:p_worker)
AND info.floorno LIKE DECODE(NVL(:p_floorno,'all'),'all','%',:p_floorno)
AND info.machineno LIKE DECODE(NVL(:p_machineno,'all'),'all','%',:p_machineno)
AND info.shift LIKE DECODE(NVL(:p_shift,'all'),'all','%',:p_shift)
AND info.cardno LIKE DECODE (NVL(:p_cardno,'all'),'all','%',:p_cardno)
AND info.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
ORDER BY ment.start_date DESC
------------- Training
SELECT tra.training_title train_title, tra.tr_duration train_dura, tra.tr_year train_yr, tra.institule train_instite,
tra.city train_city, tra.country train_coutry, info.company, info.cardno
FROM tb_personal_info info, tb_personal_training tra
WHERE info.company = :p_company
AND info.company = tra.company
AND info.cardno = tra.cardno
AND TO_CHAR(info.joining_date,'RRRR') LIKE DECODE(NVL(:p_year,'all'),'all','%',:p_year)
AND RTRIM(TO_CHAR(info.joining_date,'Month')) LIKE DECODE(NVL(:p_month,'all'),'all','%',:p_month)
AND info.departmentnm LIKE DECODE(NVL(:p_deptname,'all'),'all','%',:p_deptname)
AND info.sectionnm LIKE DECODE(NVL(:p_section,'all'),'all','%',:p_section)
AND info.lineno LIKE DECODE(NVL(:p_lineno,'all'),'all','%',:p_lineno)
AND info.designation LIKE DECODE(NVL(:p_desig,'all'),'all','%',:p_desig)
AND info.workertype LIKE DECODE(NVL(:p_worker,'all'),'all','%',:p_worker)
AND info.floorno LIKE DECODE(NVL(:p_floorno,'all'),'all','%',:p_floorno)
AND info.machineno LIKE DECODE(NVL(:p_machineno,'all'),'all','%',:p_machineno)
AND info.shift LIKE DECODE(NVL(:p_shift,'all'),'all','%',:p_shift)
AND info.cardno LIKE DECODE (NVL(:p_cardno,'all'),'all','%',:p_cardno)
AND info.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
ORDER BY tra.tr_year DESC
----------- Professional
SELECT pro.CERTIFICATION profecerti, pro.INSTITULE profeinsti, pro.CITY profecity, pro.COUNTRY profecountry,
pro.START_DATE profe_sdate, pro.END_DATE profe_edate, info.company , info.cardno
FROM tb_personal_info info, TB_PERSONAL_PROFESSIONAL pro
WHERE info.company = :p_company
AND info.company = pro.company
AND info.cardno = pro.cardno
AND TO_CHAR(info.joining_date,'RRRR') LIKE DECODE(NVL(:p_year,'all'),'all','%',:p_year)
AND RTRIM(TO_CHAR(info.joining_date,'Month')) LIKE DECODE(NVL(:p_month,'all'),'all','%',:p_month)
AND info.departmentnm LIKE DECODE(NVL(:p_deptname,'all'),'all','%',:p_deptname)
AND info.sectionnm LIKE DECODE(NVL(:p_section,'all'),'all','%',:p_section)
AND info.lineno LIKE DECODE(NVL(:p_lineno,'all'),'all','%',:p_lineno)
AND info.designation LIKE DECODE(NVL(:p_desig,'all'),'all','%',:p_desig)
AND info.workertype LIKE DECODE(NVL(:p_worker,'all'),'all','%',:p_worker)
AND info.floorno LIKE DECODE(NVL(:p_floorno,'all'),'all','%',:p_floorno)
AND info.machineno LIKE DECODE(NVL(:p_machineno,'all'),'all','%',:p_machineno)
AND info.shift LIKE DECODE(NVL(:p_shift,'all'),'all','%',:p_shift)
AND info.cardno LIKE DECODE (NVL(:p_cardno,'all'),'all','%',:p_cardno)
AND info.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
ORDER BY pro.START_DATE DESC
-------------------------------------------- selected -------------
SELECT info.company,info.departmentnm, info.sectionnm, info.lineno, info.cardno,info.secreteno,info.empname,info.father_name,info.mother_name,
info.housbandname, info.joining_date, info.designation, info.salary_grade, (info.grosssalary + info.gross_bk)grosssalary, info.resignation_date,
info.birth_date, info.workertype, pic.emppicture, info.tsalary, info.gender, info.bloodgroup, other.book_sl_no, other.open_date, other.purposeofwork,
other.perhrproduction, other.performance, other.nid, other.ref1, other.ref2, info.present_address, info.present_po, info.present_ps, info.present_dist,
info.permanentaddress, info.permanent_po, info.permanent_ps, info.permanent_dist
FROM tb_personal_info info, tb_personal_info_other other,
tb_personal_info_picture pic, tb_idcard_multiple mul
WHERE info.company = :p_company
AND info.company = pic.company
AND info.company = other.company
AND info.company = mul.company
AND mul.user_name = :p_user
AND info.cardno = mul.cardno
AND info.cardno = other.cardno
AND info.cardno = pic.cardno
ORDER BY info.departmentnm, info.sectionnm, info.lineno,info.cardno ASC
----------- Education
SELECT edu.highest_degree educa_hdegree, edu.level_degree educa_level, edu.institute_name educa_instite,
edu.passing_yr educa_yr, edu.class_grade educa_grd, edu.subject educa_sub, info.company, info.cardno
FROM tb_personal_info info, tb_personal_educational edu, tb_idcard_multiple mul
WHERE info.company = :p_company
AND info.company = edu.company
AND info.company = mul.company
AND mul.user_name = :p_user
AND info.cardno = mul.cardno
AND info.cardno = edu.cardno
ORDER BY edu.passing_yr DESC
---------------- Employeement
SELECT ment.companyname employ_company, ment.start_date employ_sdate, ment.end_date employ_edate, ment.designation employ_desig,
ment.LOCATION employ_location, ment.employer_type employ_type, info.company, info.cardno
FROM tb_personal_info info, tb_personal_employment ment, tb_idcard_multiple mul
WHERE info.company = :p_company
AND info.company = ment.company
AND info.company = mul.company
AND mul.user_name = :p_user
AND info.cardno = mul.cardno
AND info.cardno = ment.cardno
ORDER BY ment.start_date DESC
------------- Training
SELECT tra.training_title train_title, tra.tr_duration train_dura, tra.tr_year train_yr, tra.institule train_instite,
tra.city train_city, tra.country train_coutry, info.company, info.cardno
FROM tb_personal_info info, tb_personal_training tra, tb_idcard_multiple mul
WHERE info.company = :p_company
AND info.company = tra.company
AND info.company = mul.company
AND mul.user_name = :p_user
AND info.cardno = mul.cardno
AND info.cardno = tra.cardno
ORDER BY tra.tr_year DESC
----------- Professional
SELECT pro.certification profecerti, pro.institule profeinsti, pro.city profecity, pro.country profecountry,
pro.start_date profe_sdate, pro.end_date profe_edate, info.company , info.cardno
FROM tb_personal_info info, tb_personal_professional pro, tb_idcard_multiple mul
WHERE info.company = :p_company
AND info.company = pro.company
AND info.company = mul.company
AND mul.user_name = :p_user
AND info.cardno = mul.cardno
AND info.cardno = pro.cardno
ORDER BY pro.start_date DESC