Oracle Apex PL/SQL Dynamic content for HTML Report print 10 rows per page and line break.

Code in PLSQL dynamic Content for HTML Report : 

declare
  N number:= 0;
COMP VARCHAR2(80);
ADDR VARCHAR2(4000);
GSTIN VARCHAR2(80);
TEL NUMBER;
V_NET NUMBER;
cursor c_sale is
select
APEX_ITEM.TEXT_FROM_LOV_QUERY(PRODUCT_NAME,'SELECT PRODUCT_NAME, ID FROM PRODUCT_HGA_MASTER') as PRODUCT,PART_NO PART_NO,INVOICE_NO INV_NO,QTY QTY,RATE RATE,BASIC_AMOUNT GROSS_AMT,(SELECT TAX_RATE FROM PRODUCT_HGA_SALES_DETAILS WHERE PRODUCT_NAME = SHGA.PRODUCT_NAME)TAX_RATE,(SELECT SUM(CGST_AMOUNT + SGST_AMOUNT) FROM PH_SALE_INV_HGA_DETAIL WHERE PRODUCT_NAME = SHGA.PRODUCT_NAME)TAX_AMT,LINE_TOTAL NET_AMT from SALE_INV_HGA_DETAIL SHGA WHERE SHGA.BOOKING_ID = :P853_BOOKING_ID
UNION
select
APEX_ITEM.TEXT_FROM_LOV_QUERY(PRODUCT_NAME,'SELECT PRODUCT_NAME, ID FROM PRODUCT_MASTER') as PRODUCT,NULL PART_NO,VOUCHER_NO INV_NO,QTY QTY,RATE RATE,BASIC_AMOUNT,'999999999.99') GROSS_AMT,TAX_RATE,(SELECT SUM(CGST_AMOUNT + SGST_AMOUNT) FROM SALE_INV_ACC_DETAIL WHERE PRODUCT_NAME = SNGA.PRODUCT_NAME)TAX_AMT,LINE_TOTAL NET_AMT from SALE_INV_ACC_DETAIL SNGA WHERE SNGA.BOOKING_ID = :P853_BOOKING_ID;

begin
SELECT COMPANY_NAME INTO COMP FROM COMPANY;
SELECT ADDRESS INTO ADDR FROM COMPANY;
SELECT GSTIN INTO GSTIN FROM COMPANY;
SELECT TELEPHONE_NO INTO TEL FROM COMPANY;
htp.p('
<img class="logo-img" src="#APP_IMAGES#img-logo.png" alt="test" width="270"
height="70"></div>
');
htp.p('
<div align="center">
<h5 width: 320px; font-size:18px;" ><b>'||COMP||'<b></h5>
<h6 width: 90%; font-size:15px;" >'||ADDR||'<h6>
<h6 width: 320px; font-size:15px;" >TEL:'||TEL||'&emsp; GST No:'||GSTIN||'<h6></br>
</br>') ;
htp.p(
'
<table class ="DBDTL">
<caption style ="text-align=Center;" > </caption>
<thead>
<tr>
<th>Slno</th>
<th>Product</th>
<th>Part No</th>
<th>Inv No</th>
<th>Qty</th>
<th>Rate</th>
<th>Gross Amt</th>
<th>Tax Rate</th>
<th>Tax Amt</th>
<th>Net Amt</th>
</tr>
</thead>
');
for r_sale in c_sale
loop
n:=n+1;
htp.p(
'
<tbody>
<td>'||N||'</td>
<td>' || r_sale.PRODUCT || '</td>
<td>' || r_sale.PART_NO ||'</td>
<td>' || r_sale.INV_NO ||'</td>
<td>' || r_sale.QTY || '</td>
<td>' || r_sale.RATE || '</td>
<td>' || r_sale.GROSS_AMT || '</td>
<td>' || r_sale.TAX_RATE || '</td>
<td>' || r_sale.TAX_AMT || '</td>
<td>' || r_sale.NET_AMT || '</td>
</tr>
</tbody>
'
);
if mod(n, 15) = 0 then
    htp.p('<p style="page-break-before: always"></p>');
end if;
end loop;
htp.p('
<tfoot>
<tr>
<td colspan="1">Total</td>
<td>'||NULL||'</td>
<td>'||NULL||'</td>
<td>'||NULL||'</td>
<td>'||NULL||'</td>
<td>'||NULL||'</td>
<td>'||NULL||'</td>
<td>'||NULL||'</td>
<td>'||null||'</td>
<td>'||V_NET||'</td>
</tr>
</tfoot>
</table>
'
);
htp.p('
<div align="left" margin-left:5em;>
<p><b>Declaration : </b>'||DECL||'</p>
</div>
');

htp.p (' <style>
table {
font-family: "Helvetica Neue", Helvetica, sans-serif
}

.logo-img{
margin-left: 4%;
}
<table style="page-break-before: always">
</style>'
);
end;



Muhammad Abdullah Al Noor

Muhammad Abdullah Al Noor, An Oracle Apex Consultants and founder of Noors Technology (www.noorstech.com). Core Expertise : Database Administration, Oracle Forms and Reports Development, Oracle Apex Application Designer and Development, Linux Professional etc. Also the owner of TrainerBD Training and OraDemy E-Learning. WhatsApp +8801790721177

Post a Comment

Previous Post Next Post