-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathAdvance_loan_information.sql
More file actions
129 lines (128 loc) · 7.49 KB
/
Advance_loan_information.sql
File metadata and controls
129 lines (128 loc) · 7.49 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
select info.DEPARTMENTNM, info.SECTIONNM, info.LINENO, info.CARDNO,
info.EMPNAME, info.DESIGNATION, loan.LOANYEAR , loan.LOANDATE, loan.LOAN_AMT,
loan.REASON_STATUS, loan.PAID_STATUS, loan.LOAN_TYPE
from TB_ADVANCEORLOAN_MASTER loan, tb_personal_info info
where loan.company = :p_company
and info.company = loan.company
and loan.LOANYEAR = :p_year
and loan.LOANMONTH = :p_month
and loan.CARDNO = info.CARDNO
AND info.DEPARTMENTNM LIKE DECODE(NVL(:p_dept,'all'),'all','%',:p_dept)
AND info.SECTIONNM LIKE DECODE(NVL(:p_sec,'all'),'all','%',:p_sec)
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.GENDER LIKE DECODE(nvl(:p_gender,'all'),'all','%',:p_gender)
and info.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
AND info.cardno LIKE DECODE(NVL(:p_card,'all'),'all','%',:p_card)
order by loan.LOANYEAR, info.DEPARTMENTNM, info.SECTIONNM, info.LINENO, info.cardno asc
----------------------------------------------
select DEPARTMENTNM, SECTIONNM, LINENO, CARDNO, EMPNAME, DESIGNATION,
LOANYEAR , LOANDATE, LOAN_AMT, REASON_STATUS, PAID_STATUS, LOAN_TYPE
from (
select info.DEPARTMENTNM, info.SECTIONNM, info.LINENO, info.CARDNO,
info.EMPNAME, info.DESIGNATION, loan.LOANYEAR , loan.LOANDATE, loan.LOAN_AMT,
loan.REASON_STATUS, loan.PAID_STATUS, loan.LOAN_TYPE
from TB_ADVANCEORLOAN_MASTER loan, tb_personal_info info
where loan.company = :p_company
and info.company = loan.company
and loan.LOANYEAR = :p_year
and loan.LOANMONTH = :p_month
and loan.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 info.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
AND info.cardno LIKE DECODE(NVL(:p_card,'all'),'all','%',:p_card)
union
select info.DEPARTMENTNM, info.SECTIONNM, info.LINENO, info.CARDNO,
info.EMPNAME, info.DESIGNATION, loan.LOANYEAR , loan.LOANDATE, loan.LOAN_AMT,
loan.REASON_STATUS, loan.PAID_STATUS, loan.LOAN_TYPE
from TB_ADVANCEORLOAN_MASTER loan, tb_personal_info info
where loan.company = :p_company
and info.company = loan.company
and loan.LOANYEAR = :p_year
and loan.LOANMONTH = :p_month
and loan.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 info.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
AND info.cardno LIKE DECODE(NVL(:p_card,'all'),'all','%',:p_card)
union
select info.DEPARTMENTNM, info.SECTIONNM, info.LINENO, info.CARDNO,
info.EMPNAME, info.DESIGNATION, loan.LOANYEAR , loan.LOANDATE, loan.LOAN_AMT,
loan.REASON_STATUS, loan.PAID_STATUS, loan.LOAN_TYPE
from TB_ADVANCEORLOAN_MASTER loan, tb_personal_info info
where loan.company = :p_company
and info.company = loan.company
and loan.LOANYEAR = :p_year
and loan.LOANMONTH = :p_month
and loan.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 info.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
AND info.cardno LIKE DECODE(NVL(:p_card,'all'),'all','%',:p_card)
union
select info.DEPARTMENTNM, info.SECTIONNM, info.LINENO, info.CARDNO,
info.EMPNAME, info.DESIGNATION, loan.LOANYEAR , loan.LOANDATE, loan.LOAN_AMT,
loan.REASON_STATUS, loan.PAID_STATUS, loan.LOAN_TYPE
from TB_ADVANCEORLOAN_MASTER loan, tb_personal_info info
where loan.company = :p_company
and info.company = loan.company
and loan.LOANYEAR = :p_year
and loan.LOANMONTH = :p_month
and loan.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 info.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
AND info.cardno LIKE DECODE(NVL(:p_card,'all'),'all','%',:p_card)
union
select info.DEPARTMENTNM, info.SECTIONNM, info.LINENO, info.CARDNO,
info.EMPNAME, info.DESIGNATION, loan.LOANYEAR , loan.LOANDATE, loan.LOAN_AMT,
loan.REASON_STATUS, loan.PAID_STATUS, loan.LOAN_TYPE
from TB_ADVANCEORLOAN_MASTER loan, tb_personal_info info
where loan.company = :p_company
and info.company = loan.company
and loan.LOANYEAR = :p_year
and loan.LOANMONTH = :p_month
and loan.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 info.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
AND info.cardno LIKE DECODE(NVL(:p_card,'all'),'all','%',:p_card)
union
select info.DEPARTMENTNM, info.SECTIONNM, info.LINENO, info.CARDNO,
info.EMPNAME, info.DESIGNATION, loan.LOANYEAR , loan.LOANDATE, loan.LOAN_AMT,
loan.REASON_STATUS, loan.PAID_STATUS, loan.LOAN_TYPE
from TB_ADVANCEORLOAN_MASTER loan, tb_personal_info info
where loan.company = :p_company
and info.company = loan.company
and loan.LOANYEAR = :p_year
and loan.LOANMONTH = :p_month
and loan.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 info.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
AND info.cardno LIKE DECODE(NVL(:p_card,'all'),'all','%',:p_card)
union
select info.DEPARTMENTNM, info.SECTIONNM, info.LINENO, info.CARDNO,
info.EMPNAME, info.DESIGNATION, loan.LOANYEAR , loan.LOANDATE, loan.LOAN_AMT,
loan.REASON_STATUS, loan.PAID_STATUS, loan.LOAN_TYPE
from TB_ADVANCEORLOAN_MASTER loan, tb_personal_info info
where loan.company = :p_company
and info.company = loan.company
and loan.LOANYEAR = :p_year
and loan.LOANMONTH = :p_month
and loan.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 info.active LIKE DECODE(NVL(:p_active,'all'),'all','%',:p_active)
AND info.cardno LIKE DECODE(NVL(:p_card,'all'),'all','%',:p_card)
union
select info.DEPARTMENTNM, info.SECTIONNM, info.LINENO, info.CARDNO,
info.EMPNAME, info.DESIGNATION, loan.LOANYEAR , loan.LOANDATE, loan.LOAN_AMT,
loan.REASON_STATUS, loan.PAID_STATUS, loan.LOAN_TYPE
from TB_ADVANCEORLOAN_MASTER loan, tb_personal_info info, TB_IDCARD_MULTIPLE mul
where loan.company = :p_company
and info.company = loan.company
and mul.COMPANY = info.COMPANY
and loan.LOANYEAR = :p_year
and loan.LOANMONTH = :p_month
and mul.USER_NAME = :p_user
and loan.CARDNO = info.CARDNO
and info.CARDNO = mul.CARDNO
) order by LOANYEAR, DEPARTMENTNM, SECTIONNM, LINENO, cardno asc