Report with Grand Total

PLSQL Dynamic Content:

DECLARE

CURSOR GRN_SALES_MASTER IS

SELECT TRCODE,

TRCODES,

SERIALNO,

GRN_RLOC,

GRN_MDATE,

--- SLCODE,

FUNC_DEALER_NAME_BN (SLCODE) SLCODE,

FUNC_DEALER_ADDRESS_BN (SLCODE ) ADDRESS,

FUNC_DELAR_BIN_NID(SLCODE) BIN,

GRN_ORDTYP,

ORDTYP,

GRN_FOR,

TENTATIVE_DNDATE,

DN_DATE,

FUNC_DEALER_PHONE (SLCODE) DL_MOBILE,

GRN_DELADD1,

GRN_DELADD2,

GRN_DELADD3,

GRN_DUEDT,

GRN_PARTI,

GRN_TRANSPORT,

GRN_CURRCD,

GRN_EMP,

GRN_COMP,

DN_NO,

NVL (DL_BALANCE, 0) DL_BALANCE,

DL_LAST_COLL,

DL_LAST_COLL_DT,

VR_NUMBER,

INVOICE_NUMBER,

NVL(RE_REF_NO,DN_NO ) RE_REF_NO

FROM GRNORDER_SALES_VW

WHERE SERIALNO = :P33_SERIALNO;

CURSOR GRN_DRIVER IS

SELECT SERIALNO,

DN_NO,

DN_DATE,

GRN_TRANSPORT,

TRANSPORT_NO,

--- TRANSPORT_NO || '-' || VEH_MODEL TRANSPORT,

--- TRANSPORT_NO || '-' || NVL(VEH_MODEL,GRN_TRANSPORT) TRANSPORT,

DECODE(VEH_MODEL,NULL, GRN_TRANSPORT,TRANSPORT_NO)TRANSPORT,

TRUCK_CAPACITY,

VR_NUMBER,

DRIVER_ID,

DRIVER_NM,

DRIVER_ADD,

DRIVER_PHONE,

NVL(VEH_MODEL,GRN_TRANSPORT) VEH_MODEL

FROM GRN_DRIVER_VW

WHERE SERIALNO = :P33_SERIALNO;

CURSOR CUSTOMER_LAST_BALANCCE IS

SELECT NVL(M.DL_BALANCE,0)-SUM(NVL (S_QTY, 0)*NVL(MRATE,0)) LAST_BALANCE

from GRN_SALES_MASTER M,GRN_SALES_DETAIL D

WHERE M.SERIALNO = D.SERIALNO

AND M.SERIALNO=:P33_SERIALNO

GROUP BY M.DL_BALANCE;

CURSOR CUSTOMER_LAST_BALANCCE IS

SELECT NVL (SUM (M.DL_BALANCE), 0) - NVL (SUM (D.S_COST), 0) lAST_BALANCE

FROM GRN_SALES_MASTER M, GRN_SALES_DETAIL D

WHERE M.SERIALNO = D.SERIALNO AND M.SERIALNO = :P33_SERIALNO ;

CURSOR GRN_SALES_DET IS

SELECT ROWNUM SL,

FYEAR,

SERIALNO,

GRN_RLOC,

GRN_SEQ,

ITEMCODE,

FUNC_ITEM_NAME_BN (ITEMCODE) ITEMCODE_BN,

NVL (S_QTY, 0) S_QTY,

NVL (B_QTY, 0) B_QTY,

NVL (A_QTY, 0) A_QTY,

MRATE,

NVL (A_QTY, 0) + NVL(B_QTY,0)+NVL (S_QTY, 0) TOTAL_QTY,

NVL(NVL (S_QTY, 0) + NVL (A_QTY, 0) + NVL(B_QTY,0),0)* NVL(MRATE,0) TOTAL_PRICE,

---ROUND(NVL(NVL(NVL(MRATE,0)/1.15,0)*NVL(15,0)/100,0),2) PER_VATE,

---ROUND(NVL(NVL(NVL(MRATE,0)/1.15,0)*NVL(15,0)/100,0),2)*NVL(NVL(NVL (S_QTY, 0) + NVL (A_QTY, 0) + NVL(B_QTY,0),0),0) TOTAL_VAT,

ROUND((( NVL (S_QTY, 0)*NVL(MRATE,0))/115)*15,4) TOTAL_VAT,

ROUND((( NVL (S_QTY, 0)*NVL(MRATE,0))/115)*15/(NVL (A_QTY, 0) + NVL(B_QTY,0)+NVL (S_QTY, 0)),4) PER_VATE,

NVL(NVL (A_QTY, 0) + NVL(B_QTY,0),0)* NVL(MRATE,0) TOTAL_DISCOUNT,

NVL(NVL(NVL (S_QTY, 0) + NVL (A_QTY, 0) + NVL(B_QTY,0),0)* NVL(MRATE,0),0)-NVL(NVL(NVL (A_QTY, 0) + NVL(B_QTY,0),0)* NVL(MRATE,0),0) PAY_AMOUNT,

--- S_COST,

---B_COST,

ROUND (V_COST,4) V_COST,

PROM_ITEM,

--- FUNC_ITEM_NAME_BN (PROM_ITEM) PROM_ITEM_BN,

FUNC_ITEM_NAME_BANGLA (PROM_ITEM) PROM_ITEM_BN,

NVL (PROM_QTY, 0) PROM_QTY,

PROM_RATE,

--- TOTAL_QTY,

NVL (CART_QTY, 0) CART_QTY

FROM GRN_SALES_DETAIL

WHERE SERIALNO = :P33_SERIALNO;

CURSOR GRN_SALES_DET_SUM IS

SELECT SERIALNO,

SUM (NVL (S_QTY, 0)) SALES_QTY,

SUM (NVL (B_QTY, 0)) BONUS_QTY,

SUM (NVL (A_QTY, 0)) ADD_QTY,

SUM(NVL (A_QTY, 0) + NVL(B_QTY,0)+NVL (S_QTY, 0)) TOTAL_QTY,

---SUM(ROUND(NVL(NVL(NVL(MRATE,0)/1.15,0)*NVL(15,0)/100,0),2)*NVL(NVL(NVL (S_QTY, 0) + NVL (A_QTY, 0) + NVL(B_QTY,0),0),0)) TOTAL_VAT,

SUM(ROUND((( NVL (S_QTY, 0)*NVL(MRATE,0))/115)*15,4)) TOTAL_VAT,

SUM(NVL(NVL (A_QTY, 0) + NVL(B_QTY,0),0)* NVL(MRATE,0) ) TOTAL_DISCOUNT,

SUM(NVL(NVL (S_QTY, 0) + NVL (A_QTY, 0) + NVL(B_QTY,0),0)* NVL(MRATE,0) ) TOTAL_PRICE,

SUM(NVL(NVL(NVL (S_QTY, 0) + NVL (A_QTY, 0) + NVL(B_QTY,0),0)* NVL(MRATE,0),0)-NVL(NVL(NVL (A_QTY, 0) + NVL(B_QTY,0),0)* NVL(MRATE,0),0) ) PAY_AMOUNT,

SUM (NVL (PROM_QTY, 0)) PROM_ITEM_QTY

FROM GRN_SALES_DETAIL

WHERE SERIALNO =:P33_SERIALNO

GROUP BY SERIALNO;

---DEALER LAST DATE---------

CURSOR DEALER_LAST_DATE IS

select MAX(SLS_PM_MDATE) SLS_PM_MDATE

from SLS_PAY_MASTER

where SLS_PM_SUB_SLCD=:P33_SLCODE

order by SLS_PM_MDATE desc;

----DEALER LAST COLLECTION ---------

CURSOR DEALER_LAST_COLLECTION IS

SELECT sum(AMT_CR) LAST_COLLECTION

FROM GL_SL A

WHERE

MDATE>(SELECT to_date(MAX(INV_ISM_MDATE)) FROM INV_ISSUE_MASTER WHERE INV_ISM_TRCODE IN ('IN','INU') AND INV_ISM_SLCODE=a.code and INV_ISM_MDATE<:P33_MDATE)

AND CODE=:P33_SLCODE

AND ACCCODE='220401';

CURSOR DL_COLLECTION_BALANCE IS

SELECT SLS_PM_AMOUNT,

MAX(SLS_PM_MDATE) SLS_PM_MDATE

FROM SLS_PAY_MASTER

WHERE SLS_PM_SUB_SLCD ='SHAE03'

GROUP BY SLS_PM_AMOUNT

ORDER BY SLS_PM_MDATE DESC;

BEGIN

HTP.P ('<div id="PrintMe">');

HTP.P ('<div>');

HTP.P ('<style>

footer {

font-size: 12px;

color: black;

text-align: center;

}

@media print {

footer {

position: fixed;

bottom: 0;

}

</style>');

FOR GRN IN GRN_SALES_MASTER

LOOP

FOR D IN GRN_DRIVER

LOOP

FOR B IN CUSTOMER_LAST_BALANCCE LOOP

FOR DT IN DEALER_LAST_DATE LOOP

FOR C IN DEALER_LAST_COLLECTION LOOP

--- FOR BL IN DL_COLLECTION_BALANCE LOOP

HTP.P (

'<table border="0" cellpadding="0" cellspacing="0">

<tbody>

<tr>

এস. এ. কে কনজ্যুমার প্রোডাক্টস লি.

</td>

</tr>

<tr>

ফ্যাক্টরী: জগদীশপুর,ইটখোলা,মাধবপুর, হবিগঞ্জ -৩৩৩০

</td>

<tr>

হেড অফিস:  বিজয় রাকিন সিটি, কমার্শিয়াল কমপ্লেক্স, লেভেল ১১, প্লট ১/২, ব্লক ডি, সেকশন ১৫, মিরপুর, ঢাকা ১২১৬

</td>

</tr>');

htp.p(' ');

htp.p('<tr>

বিক্রয় পত্র/INVOICE

</td>

</tr>

</tbody>

</table>');

HTP.P('<table width="1062">

<tbody>

<tr>

ইনভয়েস নং

</td>

:

</td>

'

|| GRN.INVOICE_NUMBER

|| '

</td>

ইন:রেফা: নং

</td>

:

</td>

'

|| GRN.RE_REF_NO

|| '

</td>

</tr>

<tr>

ডিস্ট্রিবিউটরের নাম

</td>

:

</td>

'

|| GRN.SLCODE

|| '

</td>

তারিখ

</td>

:

</td>

'

|| GRN.DN_DATE

|| '

</td>

</tr>

<tr>

ঠিকানা

</td>

:

</td>

'

|| GRN.ADDRESS||'

</td>

অর্ডার টাইপ

</td>

:

</td>

'

|| GRN.GRN_FOR

|| '

</td>

</tr>

<tr>

মোবাইল নম্বর

</td>

:

</td>

'

|| GRN.DL_MOBILE

|| '

</td>

বি.আই.এন/এন.আই.ডি.

</td>

:

</td>

'

|| GRN.BIN

|| '

</td>

</tr>

<tr>

ট্রান্সপোর্ট

</td>

:

</td>

'

|| D.TRANSPORT

|| '

</td>

বিস্তারিত

</td>

:

</td>

'|| GRN.GRN_PARTI|| '

</td>

</tr>

<tr>

ড্রাইভার মোবাইল নম্বর

</td>

:

</td>

'

|| D.DRIVER_PHONE

|| '

</td>

ড্রাইভারের নাম

</td>

:

</td>

'

|| D.DRIVER_NM

|| '

</td>

</tr>

<tr>

কালেকশন তারিখ

</td>

:

</td>

'||GRN.DL_LAST_COLL_DT||'

</td>

শেষ কালেকশন

</td>

:

</td>

'||'৳'||TO_CHAR(GRN.DL_LAST_COLL,'999G999G999G999G990D00')||'

</td>

</tr>

<tr>

পূর্বের জের

</td>

:

</td>

'

||'৳'|| TO_CHAR(GRN.DL_BALANCE,'999G999G999G999G990D00')

||'

</td>

বর্তমান জের

</td>

:

</td>

'||'৳'||TO_CHAR(B.LAST_BALANCE,'999G999G999G999G990D00')

||'

</td>

</tr>

</tbody>

</table>');

END LOOP;

HTP.P('<table width="1098">

<tbody>

<tr>

ক্রমিক নং

</td>

পণ্যের নাম

</td>

বিক্রয় পরিমাণ

</td>

বোনাস পরিমাণ

</td>

সমন্বয় পরিমাণ

</td>

মোট পরিমাণ

</td>

'||'৳'||'মূল্য

</td>

'||'৳'||'ভ্যাট পিস

</td>

'||'৳'||'ভ্যাট

</td>

'||'৳'||'ডিসকাউন্ট

</td>

'||'৳'||'মোট মূল্য

</td>

'||'৳'||'মোট পরিশোধ

</td>

প্রমোশনাল আইটেম

</td>

প্রমো. পরিমাণ

</td>

</tr>');

FOR I IN GRN_SALES_DET LOOP

HTP.P('<tr>

'||I.SL||'

</td>

'

|| I.ITEMCODE_BN

|| '

</td>

'

|| I.S_QTY

|| '

</td>

'

|| I.B_QTY

|| '

</td>

'

|| I.A_QTY

|| '

</td>

'||I.TOTAL_QTY||'

</td>

'

|| I.MRATE

|| '

</td>

'

|| I.PER_VATE

|| '

</td>

'

|| I.TOTAL_VAT

|| '

</td>

'

|| I.TOTAL_DISCOUNT

|| '

</td>

'

|| I.TOTAL_PRICE

|| '

</td>

'

|| I.PAY_AMOUNT

|| '

</td>

'

|| I.PROM_ITEM_BN

|| '

</td>

'

|| I.PROM_QTY

|| '

</td>

</tr>');

END LOOP;

FOR S IN GRN_SALES_DET_SUM

LOOP

HTP.P('<tr>

Total

</td>

</td>

'

|| TO_CHAR(S.SALES_QTY ,'999G999G999G999G990D00')

|| '

</td>

'

|| TO_CHAR(S.BONUS_QTY,'999G999G999G999G990D00')

|| '

</td>

'

|| TO_CHAR(S.ADD_QTY ,'999G999G999G999G990D00')

|| '

</td>

'

|| TO_CHAR( S.TOTAL_QTY,'999G999G999G999G990D00')

|| '

</td>

</td>

</td>

'

|| TO_CHAR(S.TOTAL_VAT,'999G999G999G999G990D00')

|| '

</td>

'

|| TO_CHAR(S.TOTAL_DISCOUNT,'999G999G999G999G990D00')

|| '

</td>

'

|| TO_CHAR(S.TOTAL_PRICE,'999G999G999G999G990D00')

|| '

</td>

'

|| TO_CHAR(S.PAY_AMOUNT ,'999G999G999G999G990D00')

|| '

</td>

</td>

'

|| S.PROM_ITEM_QTY

|| '

</td>

</tr>

</tbody>

</table>');

HTP.P (' ');

HTP.p (

'In Word : ' || SPELL_OUT (S.PAY_AMOUNT) || '');

HTP.P('

<tbody>

<tr>

</td>

</td>

</td>

</td>

</td>

</tr>');

HTP.P('<tr>

<div> প্রস্তুতকারীর স্বাক্ষর</div>

</td>

</td>

<div> অনুমোদনকারীর স্বাক্ষর </div>

</td>

</td>

<div> ডিস্ট্রিবিউটরের স্বাক্ষর </div>

</td>

</tr>

</tbody>

</table>');

HTP.P (' ');

HTP.P (

'Maintain & Developed by SAK Consumer Products Ltd. | www.sakcpl.com');

HTP.P (' ');

HTP.P ('</div>');

HTP.P ('</footer>');

HTP.P ('</div>');

END LOOP;

EXCEPTION

WHEN OTHERS

THEN

HTP.P ('Report Problem. Contact with SAK Admin ');

HTP.P (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

END;

Report with Grand Total

Post a Comment

Contact Form