Repository navigation
Expand file tree
/
Copy pathEmployee_Details_F2.sql
More file actions
109 lines (106 loc) · 6.78 KB
/
Copy pathEmployee_Details_F2.sql
File metadata and controls
109 lines (106 loc) · 6.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
SELECT DEPARTMENTNM, SECTIONNM, lineno, cardno, empname, FATHER_NAME, designation, BIRTH_DATE, joining_date
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
SELECT DEPARTMENTNM, SECTIONNM, lineno, cardno, empname, FATHER_NAME, designation, BIRTH_DATE, joining_date
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 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 DEPARTMENTNM, SECTIONNM, lineno, cardno, empname, FATHER_NAME, designation, BIRTH_DATE, joining_date
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 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 DEPARTMENTNM, SECTIONNM, lineno, cardno, empname, FATHER_NAME, designation, BIRTH_DATE, joining_date
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 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 DEPARTMENTNM, SECTIONNM, lineno, cardno, empname, FATHER_NAME, designation, BIRTH_DATE, joining_date
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 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 DEPARTMENTNM, SECTIONNM, lineno, cardno, empname, FATHER_NAME, designation, BIRTH_DATE, joining_date
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 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 DEPARTMENTNM, SECTIONNM, lineno, cardno, empname, FATHER_NAME, designation, BIRTH_DATE, joining_date
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 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 DEPARTMENTNM, SECTIONNM, lineno, cardno, empname, FATHER_NAME, designation, BIRTH_DATE, joining_date
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 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 DEPARTMENTNM, SECTIONNM, lineno, cardno, empname, FATHER_NAME, designation, BIRTH_DATE, joining_date
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 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.DEPARTMENTNM, info.SECTIONNM, info.lineno, info.cardno, info.empname, info.FATHER_NAME, info.designation, info.BIRTH_DATE, info.joining_date
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.cardno = mul.cardno
ORDER BY DEPARTMENTNM, SECTIONNM, lineno, cardno ASC