Payroll_problem_SQL/DN_Bill_DateWise.sql at master · anik112/Payroll_problem_SQL · GitHub

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
SELECT per.departmentnm, per.sectionnm, per.lineno, per.empname, per.designation, per.cardno,
               nt.st_date, nt.ed_date, nt.finyear, nt.total_otamt, nt.total_othr
FROM   TB_PERSONAL_INFO per, TB_CLR_MONTH_OT_DETAIL  nt
WHERE per.company  = :p_company
AND   per.company      = nt.company
AND   nt.total_otamt     > 0
AND   per.cardno         = nt.cardno

AND   per.departmentnm    LIKE DECODE(NVL(:p_dept,'all'),'all','%',:p_dept)
AND   per.sectionnm          LIKE DECODE(NVL(:p_section,'all'),'all','%',:p_section)
AND   per.designation        LIKE DECODE(NVL(:p_designation,'all'),'all','%',:p_designation)
AND   per.workertype         LIKE DECODE(NVL(:p_wtype,'all'),'all','%',:p_wtype)
AND   per.floorno                LIKE DECODE(NVL(:p_floorno,'all'),'all','%',:p_floorno)
AND   per.machineno         LIKE DECODE(NVL(:p_machineno,'all'),'all','%',:p_machineno)
AND   per.lineno                 LIKE DECODE(NVL(:p_line,'all'),'all','%',:p_line)
AND   per.shift                    LIKE DECODE(NVL(:p_shift,'all'),'all','%',:p_shift)
AND   per.gender               LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND   per.cardno               LIKE DECODE(NVL(:p_card,'all'),'all','%',:p_card)
AND   per.active                 LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)

ORDER BY per.departmentnm, per.sectionnm, per.lineno, per.cardno ASC


-----------------------------


SELECT per.departmentnm, per.sectionnm, per.lineno, per.empname, per.designation, per.cardno,
               nt.st_date, nt.ed_date, nt.finyear, nt.total_otamt, nt.total_othr
FROM   TB_PERSONAL_INFO per, TB_CLR_MONTH_OT_DETAIL  nt
WHERE per.company  = :p_company
AND   per.company      = nt.company
AND   nt.total_otamt     > 0
AND   per.cardno         = nt.cardno
AND   per.departmentnm IN ( SELECT item_name FROM TB_SETUP_ITEM WHERE company   =:p_company  AND user_name = :p_user AND  item_name IS NOT NULL )
AND   per.gender               LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND   per.active                 LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT per.departmentnm, per.sectionnm, per.lineno, per.empname, per.designation, per.cardno,
               nt.st_date, nt.ed_date, nt.finyear, nt.total_otamt, nt.total_othr
FROM   TB_PERSONAL_INFO per, TB_CLR_MONTH_OT_DETAIL  nt
WHERE per.company  = :p_company
AND   per.company      = nt.company
AND   nt.total_otamt     > 0
AND   per.cardno         = nt.cardno
AND   per.sectionnm  IN ( SELECT item_name FROM TB_SETUP_ITEM WHERE company   =:p_company  AND user_name = :p_user AND  item_name IS NOT NULL )
AND   per.gender               LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND   per.active                 LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT per.departmentnm, per.sectionnm, per.lineno, per.empname, per.designation, per.cardno,
               nt.st_date, nt.ed_date, nt.finyear, nt.total_otamt, nt.total_othr
FROM   TB_PERSONAL_INFO per, TB_CLR_MONTH_OT_DETAIL  nt
WHERE per.company  = :p_company
AND   per.company      = nt.company
AND   nt.total_otamt     > 0
AND   per.cardno         = nt.cardno
AND   per.designation  IN ( SELECT item_name FROM TB_SETUP_ITEM WHERE company   =:p_company  AND user_name = :p_user AND  item_name IS NOT NULL )
AND   per.gender               LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND   per.active                 LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT per.departmentnm, per.sectionnm, per.lineno, per.empname, per.designation, per.cardno,
               nt.st_date, nt.ed_date, nt.finyear, nt.total_otamt, nt.total_othr
FROM   TB_PERSONAL_INFO per, TB_CLR_MONTH_OT_DETAIL  nt
WHERE per.company  = :p_company
AND   per.company      = nt.company
AND   nt.total_otamt     > 0
AND   per.cardno         = nt.cardno
AND   per.workertype  IN ( SELECT item_name FROM TB_SETUP_ITEM WHERE company   =:p_company  AND user_name = :p_user AND  item_name IS NOT NULL )
AND   per.gender               LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND   per.active                 LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT per.departmentnm, per.sectionnm, per.lineno, per.empname, per.designation, per.cardno,
               nt.st_date, nt.ed_date, nt.finyear, nt.total_otamt, nt.total_othr
FROM   TB_PERSONAL_INFO per, TB_CLR_MONTH_OT_DETAIL  nt
WHERE per.company  = :p_company
AND   per.company      = nt.company
AND   nt.total_otamt     > 0
AND   per.cardno         = nt.cardno
AND   per.floorno  IN ( SELECT item_name FROM TB_SETUP_ITEM WHERE company   =:p_company  AND user_name = :p_user AND  item_name IS NOT NULL )
AND   per.gender               LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND   per.active                 LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT per.departmentnm, per.sectionnm, per.lineno, per.empname, per.designation, per.cardno,
               nt.st_date, nt.ed_date, nt.finyear, nt.total_otamt, nt.total_othr
FROM   TB_PERSONAL_INFO per, TB_CLR_MONTH_OT_DETAIL  nt
WHERE per.company  = :p_company
AND   per.company      = nt.company
AND   nt.total_otamt     > 0
AND   per.cardno         = nt.cardno
AND   per.machineno  IN ( SELECT item_name FROM TB_SETUP_ITEM WHERE company   =:p_company  AND user_name = :p_user AND  item_name IS NOT NULL )
AND   per.gender               LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND   per.active                 LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT per.departmentnm, per.sectionnm, per.lineno, per.empname, per.designation, per.cardno,
               nt.st_date, nt.ed_date, nt.finyear, nt.total_otamt, nt.total_othr
FROM   TB_PERSONAL_INFO per, TB_CLR_MONTH_OT_DETAIL  nt
WHERE per.company  = :p_company
AND   per.company      = nt.company
AND   nt.total_otamt     > 0
AND   per.cardno         = nt.cardno
AND   per.lineno  IN ( SELECT item_name FROM TB_SETUP_ITEM WHERE company   =:p_company  AND user_name = :p_user AND  item_name IS NOT NULL )
AND   per.gender               LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND   per.active                 LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT per.departmentnm, per.sectionnm, per.lineno, per.empname, per.designation, per.cardno,
               nt.st_date, nt.ed_date, nt.finyear, nt.total_otamt, nt.total_othr
FROM   TB_PERSONAL_INFO per, TB_CLR_MONTH_OT_DETAIL  nt
WHERE per.company  = :p_company
AND   per.company      = nt.company
AND   nt.total_otamt     > 0
AND   per.cardno         = nt.cardno
AND   per.shift  IN ( SELECT item_name FROM TB_SETUP_ITEM WHERE company   =:p_company  AND user_name = :p_user AND  item_name IS NOT NULL )
AND   per.gender               LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND   per.active                 LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT per.departmentnm, per.sectionnm, per.lineno, per.empname, per.designation, per.cardno,
               nt.st_date, nt.ed_date, nt.finyear, nt.total_otamt, nt.total_othr
FROM   TB_PERSONAL_INFO per, TB_CLR_MONTH_OT_DETAIL  nt, TB_IDCARD_MULTIPLE mul
WHERE per.company  = :p_company
AND   per.company      = nt.company
AND	  per.company     = mul.company
AND   nt.total_otamt     > 0
AND   mul.user_name   = :p_user
AND   per.cardno      = mul.cardno
AND   per.cardno         = nt.cardno
ORDER BY departmentnm, sectionnm, lineno, cardno ASC