-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathArrearAmount_info_SLet.sql
More file actions
131 lines (123 loc) · 8.22 KB
/
ArrearAmount_info_SLet.sql
File metadata and controls
131 lines (123 loc) · 8.22 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
130
131
SELECT arr.cardno,info.empname,info.designation,info.sectionnm,arr.payamt ,arr.remarks , info.departmentnm, info.lineno, arr.reason_status
FROM TB_ARREARAMOUNT_INFO arr,TB_PERSONAL_INFO info
WHERE info.company =:p_company
AND info.company =arr.company
AND arr.finyear =:p_year
AND arr.finmonth =:p_month
AND arr.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_worker,'all'),'all','%',:p_worker)
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.gender LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND arr.reason_status LIKE DECODE(NVL(:p_rea_status,'all'),'all','%',:p_rea_status)
AND arr.adjustment_status LIKE DECODE(NVL(:p_adj_status,'all'),'all','%',:p_adj_status)
AND info.cardno LIKE DECODE(NVL(:p_card,'all'),'all','%',:p_card)
AND info.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
ORDER BY info.departmentnm, info.sectionnm, info.lineno,arr.cardno ASC
----------------------
SELECT arr.cardno,info.empname,info.designation,info.sectionnm,arr.payamt ,arr.remarks , info.departmentnm, info.lineno, arr.reason_status
FROM TB_ARREARAMOUNT_INFO arr,TB_PERSONAL_INFO info
WHERE info.company = :p_company
AND info.company = arr.company
AND arr.finyear = :p_year
AND arr.finmonth = :p_month
AND arr.cardno = info.cardno
AND info.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 arr.reason_status LIKE DECODE(NVL(:p_rea_status,'all'),'all','%',:p_rea_status)
AND arr.adjustment_status LIKE DECODE(NVL(:p_adj_status,'all'),'all','%',:p_adj_status)
AND info.gender LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND info.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT arr.cardno,info.empname,info.designation,info.sectionnm,arr.payamt ,arr.remarks , info.departmentnm, info.lineno, arr.reason_status
FROM TB_ARREARAMOUNT_INFO arr,TB_PERSONAL_INFO info
WHERE info.company = :p_company
AND info.company = arr.company
AND arr.finyear = :p_year
AND arr.finmonth = :p_month
AND arr.cardno = info.cardno
AND info.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 arr.reason_status LIKE DECODE(NVL(:p_rea_status,'all'),'all','%',:p_rea_status)
AND arr.adjustment_status LIKE DECODE(NVL(:p_adj_status,'all'),'all','%',:p_adj_status)
AND info.gender LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND info.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT arr.cardno,info.empname,info.designation,info.sectionnm,arr.payamt ,arr.remarks , info.departmentnm, info.lineno, arr.reason_status
FROM TB_ARREARAMOUNT_INFO arr,TB_PERSONAL_INFO info
WHERE info.company = :p_company
AND info.company = arr.company
AND arr.finyear = :p_year
AND arr.finmonth = :p_month
AND arr.cardno = info.cardno
AND info.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 arr.reason_status LIKE DECODE(NVL(:p_rea_status,'all'),'all','%',:p_rea_status)
AND arr.adjustment_status LIKE DECODE(NVL(:p_adj_status,'all'),'all','%',:p_adj_status)
AND info.gender LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND info.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT arr.cardno,info.empname,info.designation,info.sectionnm,arr.payamt ,arr.remarks , info.departmentnm, info.lineno, arr.reason_status
FROM TB_ARREARAMOUNT_INFO arr,TB_PERSONAL_INFO info
WHERE info.company = :p_company
AND info.company = arr.company
AND arr.finyear = :p_year
AND arr.finmonth = :p_month
AND arr.cardno = info.cardno
AND info.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 arr.reason_status LIKE DECODE(NVL(:p_rea_status,'all'),'all','%',:p_rea_status)
AND arr.adjustment_status LIKE DECODE(NVL(:p_adj_status,'all'),'all','%',:p_adj_status)
AND info.gender LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND info.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT arr.cardno,info.empname,info.designation,info.sectionnm,arr.payamt ,arr.remarks , info.departmentnm, info.lineno, arr.reason_status
FROM TB_ARREARAMOUNT_INFO arr,TB_PERSONAL_INFO info
WHERE info.company = :p_company
AND info.company = arr.company
AND arr.finyear = :p_year
AND arr.finmonth = :p_month
AND arr.cardno = info.cardno
AND info.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 arr.reason_status LIKE DECODE(NVL(:p_rea_status,'all'),'all','%',:p_rea_status)
AND arr.adjustment_status LIKE DECODE(NVL(:p_adj_status,'all'),'all','%',:p_adj_status)
AND info.gender LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND info.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT arr.cardno,info.empname,info.designation,info.sectionnm,arr.payamt ,arr.remarks , info.departmentnm, info.lineno, arr.reason_status
FROM TB_ARREARAMOUNT_INFO arr,TB_PERSONAL_INFO info
WHERE info.company = :p_company
AND info.company = arr.company
AND arr.finyear = :p_year
AND arr.finmonth = :p_month
AND arr.cardno = info.cardno
AND info.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 arr.reason_status LIKE DECODE(NVL(:p_rea_status,'all'),'all','%',:p_rea_status)
AND arr.adjustment_status LIKE DECODE(NVL(:p_adj_status,'all'),'all','%',:p_adj_status)
AND info.gender LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND info.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT arr.cardno,info.empname,info.designation,info.sectionnm,arr.payamt ,arr.remarks , info.departmentnm, info.lineno, arr.reason_status
FROM TB_ARREARAMOUNT_INFO arr,TB_PERSONAL_INFO info
WHERE info.company = :p_company
AND info.company = arr.company
AND arr.finyear = :p_year
AND arr.finmonth = :p_month
AND arr.cardno = info.cardno
AND info.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 arr.reason_status LIKE DECODE(NVL(:p_rea_status,'all'),'all','%',:p_rea_status)
AND arr.adjustment_status LIKE DECODE(NVL(:p_adj_status,'all'),'all','%',:p_adj_status)
AND info.gender LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND info.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT arr.cardno,info.empname,info.designation,info.sectionnm,arr.payamt ,arr.remarks , info.departmentnm, info.lineno, arr.reason_status
FROM TB_ARREARAMOUNT_INFO arr,TB_PERSONAL_INFO info,TB_IDCARD_MULTIPLE mul
WHERE info.company = :p_company
AND info.company = arr.company
AND info.company = mul.company
AND arr.finyear = :p_year
AND arr.finmonth = :p_month
AND mul.user_name = :p_user
AND arr.cardno = info.cardno
AND arr.cardno = mul.cardno
ORDER BY departmentnm,sectionnm,lineno,cardno ASC