Group Left Report :
declare
V_COMPANY_NAME VARCHAR2(100):='ABC COMPANY.';
cnt number(10):=0;
vGross number(11,2):=0;
vRowIndex NUMBER := 1;
begin
htp.p('
<a id="b_exit" type="button1" class="button button3 exit" align="right" href="'||APEX_UTIL.PREPARE_URL('f?p=&APP_ID.:133:&SESSION.::&DEBUG.::::')||'">Close</a>
<input type="button" class="button button1 t-Icon t-Icon--right fa fa-print" id="print" onclick="printdiv(''div_print1'');" value="Print"/><br/>
<div id="div_print1" style="margin-top:5px; text-align:right;" >
');
htp.p('
<html>
<head>
<title>Suppliers List</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
h2.page-header { margin: 0; padding: 0; text-align:center; }
h3.page-header { margin: 0; padding: 0; text-align:center; }
h1 { text-align: center; text-transform: uppercase; }
p {text-align: center;}
.napkin2, th, td { border: 1px solid black; border-collapse: collapse; padding: 5px; }
.napkin, th { border: 1px solid #607d8b; border-collapse: collapse; padding: 5px; }
.p1, .td1 { border: 0px; text-align: left; }
.full { width:100%; }
.p00 { text-align: right; border:0px; }
.m2, .m3, .m4, .m5 { text-align:center; border: 1px solid #607d8b; }
.m1 { padding-left:5px; border:0px; }
.lower { padding-top: 50px; }
.low, .low1 { text-align:center; }
</style>
</head>
');
htp.p('
<body>
<h2 class="page-header">'||V_COMPANY_NAME||'</h2>
<h3 class="page-header">Employee List (Department Wise)</h3>
<table class="napkin2" align="center" width="100%">
<thead>
<tr>
<th>DEPT</th>
<th>SL</th>
<th>Emp No</th>
<th>Employee Type</th>
<th>Finger Print ID</th>
<th>Card Number</th>
<th>Employee Name</th>
<th>Father</th>
<th>Gender</th>
<th>Religion</th>
<th>Date of Birth</th>
<th>Joining Date</th>
<th>Release Date</th>
<th>Card Assign Dt</th>
<th>Grade</th>
<th>Basic</th>
<th>Medical</th>
<th>House rent</th>
<th>Car Allowance</th>
<th>Food Allowance</th>
<th>Gross</th>
<th>Paymode</th>
</tr>
</thead>
<tbody>
');
-- Loop departments
for d in (select DEPT, COUNT(*) CNT from HR_employees GROUP BY DEPT ORDER BY DEPT) loop
vRowIndex := 1;
-- Loop employees
for r in (
SELECT EMP.EMPNO, EMP.DEPT,
DECODE(EMP.EMPTYP,'W','Worker','F','Factory Staff','M','Management Staff','N','Non-Management') EMPTYP,
EMP.FPID, EMP.CARDNO,
SUBSTR(LTRIM(RTRIM(EMP.ENAME)),1,15) ENAME,
NVL(SUBSTR(LTRIM(RTRIM(EMP.FATHER)),1,15),'N/A') FATHER,
DECODE(EMP.GENDER,'M','MALE','F','FEMALE','') GENDER,
DECODE(EMP.RELIGION,'I','ISLAM','S','SANATAN','C','CHRISTIAN','') RELIGION,
EMP.DOB, EMP.DOJ, EMP.RELEASEDT, EMP.CARD_ASSIGN_DT,
EMP.GRADE, EMP.BASIC, EMP.MEDICAL, EMP.HOUSERENT,
EMP.CONV_ALLOWANCE, EMP.FOOD_ALLOWANCE, EMP.GROSS,
DECODE(EMP.PAYMODE,'C','CASH','B','BANK') PAYMODE
FROM HR_EMPLOYEES EMP
WHERE EMP.DEPT = d.DEPT
ORDER BY EMP.EMPNO
) loop
cnt := cnt + 1;
vGross := vGross + NVL(r.GROSS,0);
htp.p('<tr>');
-- Only first row of dept gets the rowspan
if vRowIndex = 1 then
htp.p('<td rowspan="'||d.cnt||'"><b>'||htf.escape_sc(d.DEPT)||'</b></td>');
end if;
htp.p(
'<td align="center">'||cnt||'</td>'||
'<td align="center">'||htf.escape_sc(r.EMPNO)||'</td>'||
'<td align="center">'||htf.escape_sc(r.EMPTYP)||'</td>'||
'<td align="center">'||htf.escape_sc(r.FPID)||'</td>'||
'<td align="center">'||htf.escape_sc(r.CARDNO)||'</td>'||
'<td align="center">'||htf.escape_sc(r.ENAME)||'</td>'||
'<td align="center">'||htf.escape_sc(r.FATHER)||'</td>'||
'<td align="center">'||htf.escape_sc(r.GENDER)||'</td>'||
'<td align="center">'||htf.escape_sc(r.RELIGION)||'</td>'||
'<td align="center">'||htf.escape_sc(r.DOB)||'</td>'||
'<td align="center">'||htf.escape_sc(r.DOJ)||'</td>'||
'<td align="center">'||htf.escape_sc(r.RELEASEDT)||'</td>'||
'<td align="center">'||htf.escape_sc(r.CARD_ASSIGN_DT)||'</td>'||
'<td align="center">'||htf.escape_sc(r.GRADE)||'</td>'||
'<td align="center">'||htf.escape_sc(r.BASIC)||'</td>'||
'<td align="center">'||htf.escape_sc(r.MEDICAL)||'</td>'||
'<td align="center">'||htf.escape_sc(r.HOUSERENT)||'</td>'||
'<td align="center">'||htf.escape_sc(r.CONV_ALLOWANCE)||'</td>'||
'<td align="center">'||htf.escape_sc(r.FOOD_ALLOWANCE)||'</td>'||
'<td align="center">'||htf.escape_sc(TO_CHAR(r.GROSS,'99,99,99,999.99'))||'</td>'||
'<td align="center">'||htf.escape_sc(r.PAYMODE)||'</td>'
);
htp.p('</tr>');
vRowIndex := vRowIndex + 1;
end loop;
end loop;
-- Totals row
htp.p(
'<tr>'||
'<td colspan="2" align="center">'||cnt||'</td>'||
'<td colspan="17" align="center"><b>Total :</b></td>'||
'<td align="center">'||TO_CHAR(vGross,'99,99,99,999.99')||'</td>'||
'<td></td>'||
'</tr>'
);
htp.p('</tbody></table></body></html></div>');
end;