Repository navigation
Expand file tree
/
Copy pathFastival_Bonus_BK_FW.sql
More file actions
102 lines (75 loc) · 3.31 KB
/
Copy pathFastival_Bonus_BK_FW.sql
File metadata and controls
102 lines (75 loc) · 3.31 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
select bamt.CARDNO, info.EMPNAME, info.DESIGNATION, bamt.GROSSSAL, bamt.RPAYMENT, info.AC_NO
from TB_BONUS_AMOUNT bamt,TB_PERSONAL_INFO info
where info.COMPANY = :p_company
and info.COMPANY = bamt.company
and bamt.FINYEAR = :p_year
and bamt.FINMONTH = :p_month
and bamt.NPAYMENT > 0
and info.cardno = bamt.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.lineno like decode(nvl(:p_line,'all'),'all','%',:p_line)
and info.WORKERTYPE like decode(nvl(:p_woker,'all'),'all','%',:p_woker)
and info.shift like decode(nvl(:p_shift,'all'),'all','%',:p_shift)
and info.cardno like decode(nvl(:p_card,'all'),'all','%',:p_card)
AND info.CASH_TYPE LIKE DECODE(NVL(:p_paytype,'all'),'all','%',:p_paytype)
AND bamt.PAY_STATUS LIKE DECODE(NVL(:p_pay_sal,'all'),'all','%',:p_pay_sal)
AND info.BANK_NAME LIKE DECODE(NVL(:p_bkname,'all'),'all','%',:p_bkname)
and info.active like decode(nvl(:p_active,'all'),'all','%',:p_active)
order by info.lineno,bamt.CARDNO asc
---- for MFT Format
select bamt.CARDNO, info.EMPNAME, info.DESIGNATION, bamt.GROSSSAL, bamt.RPAYMENT, info.AC_NO, info.MFS_ACNO
from TB_BONUS_AMOUNT bamt,TB_PERSONAL_INFO info
where info.COMPANY = :p_company
and info.COMPANY = bamt.company
and bamt.FINYEAR = :p_year
and bamt.FINMONTH = :p_month
and bamt.NPAYMENT > 0
and info.cardno = bamt.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.lineno like decode(nvl(:p_line,'all'),'all','%',:p_line)
and info.WORKERTYPE like decode(nvl(:p_woker,'all'),'all','%',:p_woker)
and info.shift like decode(nvl(:p_shift,'all'),'all','%',:p_shift)
and info.cardno like decode(nvl(:p_card,'all'),'all','%',:p_card)
AND info.CASH_TYPE LIKE DECODE(NVL(:p_paytype,'all'),'all','%',:p_paytype)
AND bamt.PAY_STATUS LIKE DECODE(NVL(:p_pay_sal,'all'),'all','%',:p_pay_sal)
AND info.BANK_NAME LIKE DECODE(NVL(:p_bkname,'all'),'all','%',:p_bkname)
AND info.MFS_TYPE LIKE DECODE(NVL(:p_mfs_type,'all'),'all','%',:p_mfs_type)
and info.active like decode(nvl(:p_active,'all'),'all','%',:p_active)
order by info.lineno,bamt.CARDNO asc
-------- CF_AC_status formula
v_status varchar2(25);
function CF_AC_StatusFormula return Char is
v_status varchar2(25);
begin
if :p_bkname is not null then
v_status := 'A/C No';
else
v_status := 'A/C No';
end if;
return v_status;
end;
--- for MFT AC header formula
function CF_AC_StatusFormula return Char is
v_status varchar2(25);
begin
if :p_mfs_type is not null then
if upper(:p_mfs_type) = upper('bKash') then
v_status := 'bKash No';
elsif upper(:p_mfs_type) = upper('Rocket') then
v_status := 'Rocket No';
elsif upper(:p_mfs_type) = upper('Nagad') then
v_status := 'Nagad No';
end if;
else
v_status := 'A/C No';
end if;
return v_status;
end;
select distinct bonus_name
from TB_BONUS_SETUP
where bonus_year = :p_year
and bonus_month = :p_month
and bonus_name is not null
and BONUS_CATEGORY = :p_bonus_type