Payroll_problem_SQL/Daily_Everyinout_SL.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
select distinct mov.ptime ,info.cardno,info.empname,info.designation,
               info.lineno,info.joining_date,info.departmentnm,info.sectionnm
from   tb_data_movement mov,tb_personal_info info
where  info.company = :p_company
and    mov.company    = :p_com
and    mov.pdate          = :p_date
and    mov.cardno        = info.cardno
and    info.departmentnm    like decode(nvl(:p_dept,'all'),'all','%',:p_dept)
and    info.sectionnm          like decode(nvl(:p_section,'all'),'all','%',:p_section)
and    info.designation        like decode(nvl(:p_designation,'all'),'all','%',:p_designation)
and    info.workertype         like decode(nvl(:p_woker,'all'),'all','%',:p_woker)
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.lineno                 like decode(nvl(:p_line,'all'),'all','%',:p_line)
and    info.shift                    like decode(nvl(:p_shift,'all'),'all','%',:p_shift)
and    info.gender               like decode(nvl(:p_gender,'all'),'all','%',:p_gender)
and    info.cardno               like decode(nvl(:cardno,'all'),'all','%',:cardno)
order by info.lineno,info.cardno ,mov.ptime asc

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

select distinct mov.ptime ,info.cardno,info.empname,info.designation,
               info.lineno,info.joining_date,info.departmentnm,info.sectionnm
from   tb_data_movement mov,tb_personal_info info
where  info.company = :p_company
and    mov.company    = :p_com
and    mov.pdate          = :p_date
and    mov.cardno        = info.cardno
and   info.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   info.gender            like decode(nvl(:p_gender,'all'),'all','%',:p_gender)
union
select distinct mov.ptime ,info.cardno,info.empname,info.designation,
               info.lineno,info.joining_date,info.departmentnm,info.sectionnm
from   tb_data_movement mov,tb_personal_info info
where  info.company = :p_company
and    mov.company    = :p_com
and    mov.pdate          = :p_date
and    mov.cardno        = info.cardno
and   info.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   info.gender       like decode(nvl(:p_gender,'all'),'all','%',:p_gender)
union
select distinct mov.ptime ,info.cardno,info.empname,info.designation,
               info.lineno,info.joining_date,info.departmentnm,info.sectionnm
from   tb_data_movement mov,tb_personal_info info
where  info.company = :p_company
and    mov.company    = :p_com
and    mov.pdate          = :p_date
and    mov.cardno        = info.cardno
and   info.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   info.gender        like decode(nvl(:p_gender,'all'),'all','%',:p_gender)
union
select distinct mov.ptime ,info.cardno,info.empname,info.designation,
               info.lineno,info.joining_date,info.departmentnm,info.sectionnm
from   tb_data_movement mov,tb_personal_info info
where  info.company = :p_company
and    mov.company    = :p_com
and    mov.pdate          = :p_date
and    mov.cardno        = info.cardno
and   info.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   info.gender                  like decode(nvl(:p_gender,'all'),'all','%',:p_gender)
union
select distinct mov.ptime ,info.cardno,info.empname,info.designation,
               info.lineno,info.joining_date,info.departmentnm,info.sectionnm
from   tb_data_movement mov,tb_personal_info info
where  info.company = :p_company
and    mov.company    = :p_com
and    mov.pdate          = :p_date
and    mov.cardno        = info.cardno
and   info.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   info.gender            like decode(nvl(:p_gender,'all'),'all','%',:p_gender)
union
select distinct mov.ptime ,info.cardno,info.empname,info.designation,
               info.lineno,info.joining_date,info.departmentnm,info.sectionnm
from   tb_data_movement mov,tb_personal_info info
where  info.company = :p_company
and    mov.company    = :p_com
and    mov.pdate          = :p_date
and    mov.cardno        = info.cardno
and   info.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   info.gender               like decode(nvl(:p_gender,'all'),'all','%',:p_gender)
union
select distinct mov.ptime ,info.cardno,info.empname,info.designation,
               info.lineno,info.joining_date,info.departmentnm,info.sectionnm
from   tb_data_movement mov,tb_personal_info info
where  info.company = :p_company
and    mov.company    = :p_com
and    mov.pdate          = :p_date
and    mov.cardno        = info.cardno
and   info.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   info.gender         like decode(nvl(:p_gender,'all'),'all','%',:p_gender)
union
select distinct mov.ptime ,info.cardno,info.empname,info.designation,
               info.lineno,info.joining_date,info.departmentnm,info.sectionnm
from   tb_data_movement mov,tb_personal_info info
where  info.company = :p_company
and    mov.company    = :p_com
and    mov.pdate          = :p_date
and    mov.cardno        = info.cardno
and   info.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   info.gender        like decode(nvl(:p_gender,'all'),'all','%',:p_gender)
union
select distinct mov.ptime ,info.cardno,info.empname,info.designation,
               info.lineno,info.joining_date,info.departmentnm,info.sectionnm
from   tb_data_movement mov,tb_personal_info info, tb_idcard_multiple mul
where  info.company = :p_company
and    info.company = mul.company
and    mov.company    = :p_com
and    mul.user_name  = :p_user
and    mov.pdate          = :p_date
and    mov.cardno        = info.cardno
and    mov.cardno        = mul.cardno
order by lineno,cardno ,ptime asc