Repository navigation
Expand file tree
/
Copy pathEmployee_Details.sql
More file actions
129 lines (120 loc) · 7.33 KB
/
Copy pathEmployee_Details.sql
File metadata and controls
129 lines (120 loc) · 7.33 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
SELECT SECTIONNM,DEPARTMENTNM,lineno,cardno,secreteno,empname,designation,joining_date,(grosssalary + GROSS_BK)grosssalary,WORKERTYPE
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
---------------- basic func
FUNCTION CF_Basic_SalFormula RETURN NUMBER IS
v_basic NUMBER:= 0;
BEGIN
BEGIN
v_basic := Basic_Salary(:p_company,:GROSSSALARY,:WORKERTYPE);
EXCEPTION
WHEN OTHERS THEN v_basic := 0;
END;
RETURN v_basic;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
-------------------------------- SLect SQL ----
SELECT SECTIONNM,DEPARTMENTNM,lineno,cardno,secreteno,empname,designation,joining_date,(grosssalary + GROSS_BK)grosssalary,WORKERTYPE
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 SECTIONNM,DEPARTMENTNM,lineno,cardno,secreteno,empname,designation,joining_date,(grosssalary + GROSS_BK)grosssalary,WORKERTYPE
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 SECTIONNM,DEPARTMENTNM,lineno,cardno,secreteno,empname,designation,joining_date,(grosssalary + GROSS_BK)grosssalary,WORKERTYPE
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 SECTIONNM,DEPARTMENTNM,lineno,cardno,secreteno,empname,designation,joining_date,(grosssalary + GROSS_BK)grosssalary,WORKERTYPE
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 SECTIONNM,DEPARTMENTNM,lineno,cardno,secreteno,empname,designation,joining_date,(grosssalary + GROSS_BK)grosssalary,WORKERTYPE
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 SECTIONNM,DEPARTMENTNM,lineno,cardno,secreteno,empname,designation,joining_date,(grosssalary + GROSS_BK)grosssalary,WORKERTYPE
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 SECTIONNM,DEPARTMENTNM,lineno,cardno,secreteno,empname,designation,joining_date,(grosssalary + GROSS_BK)grosssalary,WORKERTYPE
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 SECTIONNM,DEPARTMENTNM,lineno,cardno,secreteno,empname,designation,joining_date,(grosssalary + GROSS_BK)grosssalary,WORKERTYPE
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.SECTIONNM,info.DEPARTMENTNM,info.lineno,info.cardno,info.secreteno,info.empname,
info.designation,info.joining_date,(info.grosssalary + info.GROSS_BK)grosssalary,info.WORKERTYPE
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