Payroll_problem_SQL/Advance_loan_information_v2.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
128
129
130
131
132
133
134
135
136
SELECT a.loanamount,a.dedamount,a.balamount,a.loanmonth,a.dedmonth,
       a.dedyear, a.cardno,b.empname,b.designation,a.loanyear, b.departmentnm, b.sectionnm, b.lineno,a.loan_type
FROM  TB_ADVANCEORLOAN_INFO a,TB_PERSONAL_INFO b
WHERE  b.company        =:p_company
AND   b.company           =a.company
AND   a.loanyear      =:p_year
AND   a.loanmonth  =:p_month
AND   a. loan_type  = :p_type
AND   a.dedamount IS NOT NULL
AND   a.cardno  = b.cardno
AND    b.departmentnm  LIKE DECODE(NVL(:p_dept,'all'),'all','%',:p_dept)
AND    b.sectionnm  LIKE DECODE(NVL(:p_section,'all'),'all','%',:p_section)
AND    b.designation LIKE DECODE(NVL(:p_designation,'all'),'all','%',:p_designation)
AND    b.workertype LIKE DECODE(NVL(:p_woker,'all'),'all','%',:p_woker)
AND   b.floorno LIKE DECODE(NVL(:p_floorno,'all'),'all','%',:p_floorno)
AND   b.machineno LIKE DECODE(NVL(:p_machineno,'all'),'all','%',:p_machineno)
AND    b.lineno  LIKE DECODE(NVL(:p_line,'all'),'all','%',:p_line)
AND    b.gender LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND    a.cardno LIKE DECODE(NVL(:emp,'all'),'all','%',:emp)
AND   b.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
ORDER BY a.slno,a.cardno ASC


---------------------------------------------
SELECT a.loanamount,a.dedamount,a.balamount,a.loanmonth,a.dedmonth,a.slno,
               a.dedyear, a.cardno,b.empname,b.designation,a.loanyear, b.departmentnm, b.sectionnm, b.lineno,a.loan_type
FROM  TB_ADVANCEORLOAN_INFO a,TB_PERSONAL_INFO b
WHERE  b.company = :p_company
AND   b.company      = a.company
AND   a.loanyear       = :p_year
AND   a.loanmonth    = :p_month
AND   a. loan_type    = :p_type
AND   a.dedamount IS NOT NULL
AND   a.cardno          = b.cardno
AND   b.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   b.gender  LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND   b.active   LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT a.loanamount,a.dedamount,a.balamount,a.loanmonth,a.dedmonth,a.slno,
               a.dedyear, a.cardno,b.empname,b.designation,a.loanyear, b.departmentnm, b.sectionnm, b.lineno,a.loan_type
FROM  TB_ADVANCEORLOAN_INFO a,TB_PERSONAL_INFO b
WHERE  b.company = :p_company
AND   b.company      = a.company
AND   a.loanyear       = :p_year
AND   a.loanmonth    = :p_month
AND   a. loan_type    = :p_type
AND   a.dedamount IS NOT NULL
AND   a.cardno         = b.cardno
AND   b.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   b.gender  LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND   b.active   LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT a.loanamount,a.dedamount,a.balamount,a.loanmonth,a.dedmonth,a.slno,
               a.dedyear, a.cardno,b.empname,b.designation,a.loanyear, b.departmentnm, b.sectionnm, b.lineno,a.loan_type
FROM  TB_ADVANCEORLOAN_INFO a,TB_PERSONAL_INFO b
WHERE  b.company = :p_company
AND   b.company      = a.company
AND   a.loanyear       = :p_year
AND   a.loanmonth    = :p_month
AND   a. loan_type    = :p_type
AND   a.dedamount IS NOT NULL
AND   a.cardno         = b.cardno
AND   b.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   b.gender  LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND   b.active   LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT a.loanamount,a.dedamount,a.balamount,a.loanmonth,a.dedmonth,a.slno,
               a.dedyear, a.cardno,b.empname,b.designation,a.loanyear, b.departmentnm, b.sectionnm, b.lineno,a.loan_type
FROM  TB_ADVANCEORLOAN_INFO a,TB_PERSONAL_INFO b
WHERE  b.company = :p_company
AND   b.company      = a.company
AND   a.loanyear       = :p_year
AND   a.loanmonth    = :p_month
AND   a. loan_type    = :p_type
AND   a.dedamount IS NOT NULL
AND   a.cardno         = b.cardno
AND   b.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   b.gender  LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND   b.active   LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT a.loanamount,a.dedamount,a.balamount,a.loanmonth,a.dedmonth,a.slno,
               a.dedyear, a.cardno,b.empname,b.designation,a.loanyear, b.departmentnm, b.sectionnm, b.lineno,a.loan_type
FROM  TB_ADVANCEORLOAN_INFO a,TB_PERSONAL_INFO b
WHERE  b.company = :p_company
AND   b.company      = a.company
AND   a.loanyear       = :p_year
AND   a.loanmonth    = :p_month
AND   a. loan_type    = :p_type
AND   a.dedamount IS NOT NULL
AND   a.cardno         = b.cardno
AND   b.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   b.gender LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND   b.active   LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT a.loanamount,a.dedamount,a.balamount,a.loanmonth,a.dedmonth,a.slno,
               a.dedyear, a.cardno,b.empname,b.designation,a.loanyear, b.departmentnm, b.sectionnm, b.lineno,a.loan_type
FROM  TB_ADVANCEORLOAN_INFO a,TB_PERSONAL_INFO b
WHERE  b.company = :p_company
AND   b.company      = a.company
AND   a.loanyear       = :p_year
AND   a.loanmonth    = :p_month
AND   a. loan_type    = :p_type
AND   a.dedamount IS NOT NULL
AND   a.cardno         = b.cardno
AND   b.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   b.gender LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND   b.active  LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT a.loanamount,a.dedamount,a.balamount,a.loanmonth,a.dedmonth,a.slno,
               a.dedyear, a.cardno,b.empname,b.designation,a.loanyear, b.departmentnm, b.sectionnm, b.lineno,a.loan_type
FROM  TB_ADVANCEORLOAN_INFO a,TB_PERSONAL_INFO b
WHERE  b.company = :p_company
AND   b.company      = a.company
AND   a.loanyear       = :p_year
AND   a.loanmonth    = :p_month
AND   a. loan_type    = :p_type
AND   a.dedamount IS NOT NULL
AND   a.cardno         = b.cardno
AND   b.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   b.gender LIKE DECODE(NVL(:p_gender,'all'),'all','%',:p_gender)
AND   b.active  LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
UNION
SELECT a.loanamount,a.dedamount,a.balamount,a.loanmonth,a.dedmonth,a.slno,
               a.dedyear, a.cardno,b.empname,b.designation,a.loanyear, b.departmentnm, b.sectionnm, b.lineno,a.loan_type
FROM  TB_ADVANCEORLOAN_INFO a,TB_PERSONAL_INFO b,  TB_IDCARD_MULTIPLE mul
WHERE  b.company    = :p_company
AND   b.company         = a.company
AND   b.company         = mul.company
AND   a.loanyear          = :p_year
AND   a.loanmonth       = :p_month
AND   a. loan_type       = :p_type
AND   mul.user_name   = :p_user
AND   a.dedamount IS NOT NULL
AND   a.cardno            = b.cardno
AND   a.cardno            = mul.cardno
ORDER BY slno,cardno ASC