Repository navigation
Expand file tree
/
Copy pathDaily_Everyinout_SL.sql
More file actions
112 lines (110 loc) · 6.27 KB
/
Copy pathDaily_Everyinout_SL.sql
File metadata and controls
112 lines (110 loc) · 6.27 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
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