-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathD-From-Salary.txt
More file actions
145 lines (132 loc) · 6.85 KB
/
D-From-Salary.txt
File metadata and controls
145 lines (132 loc) · 6.85 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
132
133
134
135
136
137
138
139
140
141
142
--------------------
------------------ Salary last 3 months with net payment
select a.DEPT_NAME, a.SECTION_NAME, a.LINENO, a.CARDNO, a.EMPNAME, a.DESIGNATION, a.SALARY_GRADE,
a.BIRTH_DATE, a.JOINING_DATE, a.GROSSSALARY, a.FATHER_NAME, a.MOTHER_NAME, a.HOUSBANDNAME,
a.MARITALSTATUS, a.GENDER, a.PRESENT_ADDRESS, a.PERMANENTADDRESS,
a.NID , a.BIRTH_CERTIFICATE_NO, a.OWN_CELLNO, a.NOMINEE_CELLNO,
a.BANK_NAME, a.AC_NO,
b.NET_PAYMENT Sal_Dec_19, c.NET_PAYMENT Sal_Jan_20, d.NET_PAYMENT Sal_Feb_20
from
(select info.DEPT_NAME, info.SECTION_NAME, info.LINENO, info.CARDNO, info.EMPNAME, info.DESIGNATION, info.SALARY_GRADE,
info.BIRTH_DATE, info.JOINING_DATE, info.GROSSSALARY, info.FATHER_NAME, info.MOTHER_NAME, info.HOUSBANDNAME,
info.MARITALSTATUS, info.GENDER,info.PRESENT_ADDRESS,info.PERMANENTADDRESS,
other.NID , other.BIRTH_CERTIFICATE_NO, other.OWN_CELLNO, other.NOMINEE_CELLNO,
info.BANK_NAME, info.AC_NO
from TB_PERSONAL_INFO info, TB_PERSONAL_INFO_OTHER other
where info.COMPANY_NAME = 'Amex Knitting & Dyeing Ind. Ltd'
and info.COMPANY_NAME = other.COMPANY
and info.active = 0
and info.cardno = other.cardno
order by info.DEPT_NAME, info.SECTION_NAME, info.LINENO, info.CARDNO asc)a,
( select sal.NET_PAYMENT , info.CARDNO
from TB_MONTHSALARY sal, TB_PERSONAL_INFO info
where info.COMPANY_NAME = 'Amex Knitting & Dyeing Ind. Ltd'
and info.COMPANY_NAME = sal.COMPANY
and sal.FINYEAR = 2019
and sal.FINMONTH = 'December'
and sal.EMPCODE = info.CARDNO)b,
( select sal.NET_PAYMENT, info.CARDNO
from TB_MONTHSALARY sal, TB_PERSONAL_INFO info
where info.COMPANY_NAME = 'Amex Knitting & Dyeing Ind. Ltd'
and info.COMPANY_NAME = sal.COMPANY
and sal.FINYEAR = 2020
and sal.FINMONTH = 'January'
and sal.EMPCODE = info.CARDNO)c,
( select sal.NET_PAYMENT, info.CARDNO
from TB_MONTHSALARY sal, TB_PERSONAL_INFO info
where info.COMPANY_NAME = 'Amex Knitting & Dyeing Ind. Ltd'
and info.COMPANY_NAME = sal.COMPANY
and sal.FINYEAR = 2020
and sal.FINMONTH = 'February'
and sal.EMPCODE = info.CARDNO)d
where a.cardno = b.cardno(+)
and a.cardno = c.cardno(+)
and a.cardno = d.cardno(+)
------------------------------
--------------- Gross Salary Last 3 Months
select a.DEPT_NAME, a.SECTION_NAME, a.LINENO, a.CARDNO, a.EMPNAME, a.DESIGNATION, a.SALARY_GRADE,
a.BIRTH_DATE, a.JOINING_DATE, a.GROSSSALARY, a.FATHER_NAME, a.MOTHER_NAME, a.HOUSBANDNAME,
a.MARITALSTATUS, a.GENDER, a.PRESENT_ADDRESS, a.PERMANENTADDRESS,
a.NID , a.BIRTH_CERTIFICATE_NO, a.OWN_CELLNO, a.NOMINEE_CELLNO,
a.BANK_NAME, a.AC_NO,
b.NET_PAYMENT Sal_Dec_19, c.NET_PAYMENT Sal_Jan_20, d.NET_PAYMENT Sal_Feb_20
from
(select info.DEPT_NAME, info.SECTION_NAME, info.LINENO, info.CARDNO, info.EMPNAME, info.DESIGNATION, info.SALARY_GRADE,
info.BIRTH_DATE, info.JOINING_DATE, info.GROSSSALARY, info.FATHER_NAME, info.MOTHER_NAME, info.HOUSBANDNAME,
info.MARITALSTATUS, info.GENDER,info.PRESENT_ADDRESS,info.PERMANENTADDRESS,
other.NID , other.BIRTH_CERTIFICATE_NO, other.OWN_CELLNO, other.NOMINEE_CELLNO,
info.BANK_NAME, info.AC_NO
from TB_PERSONAL_INFO info, TB_PERSONAL_INFO_OTHER other
where info.COMPANY_NAME = 'Amex Knitting & Dyeing Ind. Ltd'
and info.COMPANY_NAME = other.COMPANY
and info.active = 0
and info.cardno = other.cardno
order by info.DEPT_NAME, info.SECTION_NAME, info.LINENO, info.CARDNO asc)a,
( select sal.GROSS_SALARY NET_PAYMENT , info.CARDNO
from TB_MONTHSALARY sal, TB_PERSONAL_INFO info
where info.COMPANY_NAME = 'Amex Knitting & Dyeing Ind. Ltd'
and info.COMPANY_NAME = sal.COMPANY
and sal.FINYEAR = 2019
and sal.FINMONTH = 'December'
and sal.EMPCODE = info.CARDNO)b,
( select sal.GROSS_SALARY NET_PAYMENT, info.CARDNO
from TB_MONTHSALARY sal, TB_PERSONAL_INFO info
where info.COMPANY_NAME = 'Amex Knitting & Dyeing Ind. Ltd'
and info.COMPANY_NAME = sal.COMPANY
and sal.FINYEAR = 2020
and sal.FINMONTH = 'January'
and sal.EMPCODE = info.CARDNO)c,
( select sal.GROSS_SALARY NET_PAYMENT, info.CARDNO
from TB_MONTHSALARY sal, TB_PERSONAL_INFO info
where info.COMPANY_NAME = 'Amex Knitting & Dyeing Ind. Ltd'
and info.COMPANY_NAME = sal.COMPANY
and sal.FINYEAR = 2020
and sal.FINMONTH = 'February'
and sal.EMPCODE = info.CARDNO)d
where a.cardno = b.cardno(+)
and a.cardno = c.cardno(+)
and a.cardno = d.cardno(+)
-------------------------
---------------------- Net Wages = Gross - Absent Sal. last 3 months
select a.DEPT_NAME, a.SECTION_NAME, a.LINENO, a.CARDNO, a.EMPNAME, a.DESIGNATION, a.SALARY_GRADE,
a.BIRTH_DATE, a.JOINING_DATE, a.GROSSSALARY, a.FATHER_NAME, a.MOTHER_NAME, a.HOUSBANDNAME,
a.MARITALSTATUS, a.GENDER, a.PRESENT_ADDRESS, a.PERMANENTADDRESS,
a.NID , a.BIRTH_CERTIFICATE_NO, a.OWN_CELLNO, a.NOMINEE_CELLNO,
a.BANK_NAME, a.AC_NO,
b.NET_PAYMENT Sal_Dec_19, c.NET_PAYMENT Sal_Jan_20, d.NET_PAYMENT Sal_Feb_20
from
(select info.DEPT_NAME, info.SECTION_NAME, info.LINENO, info.CARDNO, info.EMPNAME, info.DESIGNATION, info.SALARY_GRADE,
info.BIRTH_DATE, info.JOINING_DATE, info.GROSSSALARY, info.FATHER_NAME, info.MOTHER_NAME, info.HOUSBANDNAME,
info.MARITALSTATUS, info.GENDER,info.PRESENT_ADDRESS,info.PERMANENTADDRESS,
other.NID , other.BIRTH_CERTIFICATE_NO, other.OWN_CELLNO, other.NOMINEE_CELLNO,
info.BANK_NAME, info.AC_NO
from TB_PERSONAL_INFO info, TB_PERSONAL_INFO_OTHER other
where info.COMPANY_NAME = 'Amex Knitting & Dyeing Ind. Ltd'
and info.COMPANY_NAME = other.COMPANY
and info.active = 0
and info.cardno = other.cardno
order by info.DEPT_NAME, info.SECTION_NAME, info.LINENO, info.CARDNO asc)a,
( select (sal.GROSS_SALARY - sal.ABSENT_DEDUCTION_AMT) NET_PAYMENT , info.CARDNO
from TB_MONTHSALARY sal, TB_PERSONAL_INFO info
where info.COMPANY_NAME = 'Amex Knitting & Dyeing Ind. Ltd'
and info.COMPANY_NAME = sal.COMPANY
and sal.FINYEAR = 2019
and sal.FINMONTH = 'December'
and sal.EMPCODE = info.CARDNO)b,
( select (sal.GROSS_SALARY - sal.ABSENT_DEDUCTION_AMT) NET_PAYMENT, info.CARDNO
from TB_MONTHSALARY sal, TB_PERSONAL_INFO info
where info.COMPANY_NAME = 'Amex Knitting & Dyeing Ind. Ltd'
and info.COMPANY_NAME = sal.COMPANY
and sal.FINYEAR = 2020
and sal.FINMONTH = 'January'
and sal.EMPCODE = info.CARDNO)c,
( select (sal.GROSS_SALARY - sal.ABSENT_DEDUCTION_AMT)NET_PAYMENT, info.CARDNO
from TB_MONTHSALARY sal, TB_PERSONAL_INFO info
where info.COMPANY_NAME = 'Amex Knitting & Dyeing Ind. Ltd'
and info.COMPANY_NAME = sal.COMPANY
and sal.FINYEAR = 2020
and sal.FINMONTH = 'February'
and sal.EMPCODE = info.CARDNO)d
where a.cardno = b.cardno(+)
and a.cardno = c.cardno(+)
and a.cardno = d.cardno(+)