Account Balance Query !

Hi Friends,

I am posting this query for Account Balance as many of us are facing difficulty at the time of balance sheet and I guess this will help to all accountant as it will show opening Balance, GRPO value, AP value, Bank, Issue etc..

Try this..

select a.Account,a.AccountName,sum(a.OPBalance) [Opening Balance],

–sum(a.Purchase) [Purchase]

SUM(a.GRPO)[GOODS RECIPT PO], sum(a.GOODRETURN) [GOODS RETURN],sum(a.APINVOICE) [PURCHASE INVOICE],sum(a.APCREDITNOTE) [PURCHASE CREDIT NOTE],sum(a.LANDEDCOST) [LANDED COST]

,sum(a.DELIVERY) [SALES DELIVERY],sum(a.SALESRETURN) [SALES RETURN],sum(a.ARINVOICE) [SALES INVOICE],sum(a.ARCREDITNOTE) [SALES CREDIT NOTE]

,sum(a.MANUALSTOCKRECEIPT) [MANUAL STOCK RECEIPT],sum(a.INSIALOPENING) [ITEM OPENING],sum(a.RECEIPTFORMPRODUCTION) [RECEIPT FROM PRODUCTION],sum(a.ISSUEDFROMPRODUCTION) [ISSUED FROM PRODUCTION]

,sum(a.INVENTORYTRANSFER) [INVENTORY TRANSFER],sum(a.STOCKVALUATION)[STOCK RE-VALUATION],sum(a.PRODUCTIONORDER) [PRODUCTION ORDER],sum(a.JOURNALENTRY) [JOURNAL ENTERY],sum(a.OUTGOINGPAYMENT)[OUTGOING PAYMENT]

,sum(a.OPENING) [GENERAL OPENING],sum(a.INCOMINGPAYMENT) [INCOMING PAYMENT],sum(a.AUTORECO) [AUTO RECO],sum(a.INERTRECO)[INTER RECO],

—-

(SUM(a.GRPO)+ sum(a.GOODRETURN) +sum(a.APINVOICE)+sum(a.APCREDITNOTE)+sum(a.LANDEDCOST)+

sum(a.DELIVERY)+sum(a.SALESRETURN)+sum(a.ARINVOICE) +sum(a.ARCREDITNOTE) +

sum(a.MANUALSTOCKRECEIPT)+sum(a.INSIALOPENING)+sum(a.RECEIPTFORMPRODUCTION)+sum(a.ISSUEDFROMPRODUCTION)+

sum(a.INVENTORYTRANSFER)+sum(a.STOCKVALUATION)+sum(a.PRODUCTIONORDER) +sum(a.JOURNALENTRY) +sum(a.OUTGOINGPAYMENT)+

sum(a.OPENING) +sum(a.INCOMINGPAYMENT) +sum(a.AUTORECO)+sum(a.INERTRECO))  [Remaining After transactions]

—-

,sum(a.CLBalance) [Closing Balance]

–,(sum(a.OPBalance)+sum(a.Purchase)-sum(a.CLBalance)) [Consumation]

from

(

SELECT

T1.Account as Account,c.acctname as AccountName, sum(T1.Debit-T1.Credit) as OPBalance,

–0 as Purchase

0 as GRPO, 0 AS GOODRETURN,0 AS APINVOICE,0 AS APCREDITNOTE,0 AS LANDEDCOST

,0 AS DELIVERY,0 AS SALESRETURN,0 AS ARINVOICE,0 AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,0 AS STOCKVALUATION,0 AS PRODUCTIONORDER,0 AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,0 as OPENING,0 AS INCOMINGPAYMENT,0 AS AUTORECO,0 AS INERTRECO

,0 as CLBalance

FROM

OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate < ‘[%0]’  –and

–T0.TransType = 3 and

–c.groups

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–sum(T1.Debit-T1.Credit) as Purchase

sum(T1.Debit-T1.Credit) as GRPO, 0 AS GOODRETURN,0 AS APINVOICE,0 AS APCREDITNOTE,0 AS LANDEDCOST

,0 AS DELIVERY,0 AS SALESRETURN,0 AS ARINVOICE,0 AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,0 AS STOCKVALUATION,0 AS PRODUCTIONORDER,0 AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,0 as OPENING,0 AS INCOMINGPAYMENT,0 AS AUTORECO,0 AS INERTRECO

,0 as CLBalance

FROM

OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate >= ‘[%0]’ and T0.RefDate <= ‘[%1]’and

T0.TransType in (’20’) –and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’41000000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–sum(T1.Debit-T1.Credit) as Purchase

0 as GRPO, sum(T1.Debit-T1.Credit) AS GOODRETURN,0 AS APINVOICE,0 AS APCREDITNOTE,0 AS LANDEDCOST

,0 AS DELIVERY,0 AS SALESRETURN,0 AS ARINVOICE,0 AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,0 AS STOCKVALUATION,0 AS PRODUCTIONORDER,0 AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,0 as OPENING,0 AS INCOMINGPAYMENT,0 AS AUTORECO,0 AS INERTRECO

,0 as CLBalance

FROM

OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate >= ‘[%0]’ and T0.RefDate <= ‘[%1]’and

T0.TransType in (’21’) –and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’41000000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–sum(T1.Debit-T1.Credit) as Purchase

0 as GRPO, 0 AS GOODRETURN,sum(T1.Debit-T1.Credit) AS APINVOICE,0 AS APCREDITNOTE,0 AS LANDEDCOST

,0 AS DELIVERY,0 AS SALESRETURN,0 AS ARINVOICE,0 AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,0 AS STOCKVALUATION,0 AS PRODUCTIONORDER,0 AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,0 as OPENING,0 AS INCOMINGPAYMENT,0 AS AUTORECO,0 AS INERTRECO

,0 as CLBalance

FROM

OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate >= ‘[%0]’ and T0.RefDate <= ‘[%1]’and

T0.TransType in (’18’) –and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’41000000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–sum(T1.Debit-T1.Credit) as Purchase

0 as GRPO, 0 AS GOODRETURN,0 AS APINVOICE,sum(T1.Debit-T1.Credit) AS APCREDITNOTE,0 AS LANDEDCOST

,0 AS DELIVERY,0 AS SALESRETURN,0 AS ARINVOICE,0 AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,0 AS STOCKVALUATION,0 AS PRODUCTIONORDER,0 AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,0 as OPENING,0 AS INCOMINGPAYMENT,0 AS AUTORECO,0 AS INERTRECO

,0 as CLBalance

FROM

OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate >= ‘[%0]’ and T0.RefDate <= ‘[%1]’and

T0.TransType in (’19’) –and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’41000000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–sum(T1.Debit-T1.Credit) as Purchase

0 as GRPO, 0 AS GOODRETURN,0 AS APINVOICE,0 AS APCREDITNOTE,sum(T1.Debit-T1.Credit) AS LANDEDCOST

,0 AS DELIVERY,0 AS SALESRETURN,0 AS ARINVOICE,0 AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,0 AS STOCKVALUATION,0 AS PRODUCTIONORDER,0 AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,0 as OPENING,0 AS INCOMINGPAYMENT,0 AS AUTORECO,0 AS INERTRECO

,0 as CLBalance

FROM

OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate >= ‘[%0]’ and T0.RefDate <= ‘[%1]’and

T0.TransType in (’69’) –and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’41000000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–sum(T1.Debit-T1.Credit) as Purchase

0 as GRPO, 0 AS GOODRETURN,0 AS APINVOICE,0 AS APCREDITNOTE,0 AS LANDEDCOST

,sum(T1.Debit-T1.Credit) AS DELIVERY,0 AS SALESRETURN,0 AS ARINVOICE,0 AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,0 AS STOCKVALUATION,0 AS PRODUCTIONORDER,0 AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,0 as OPENING,0 AS INCOMINGPAYMENT,0 AS AUTORECO,0 AS INERTRECO

,0 as CLBalance

FROM

OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate >= ‘[%0]’ and T0.RefDate <= ‘[%1]’and

T0.TransType in (’15’) –and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’41000000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–sum(T1.Debit-T1.Credit) as Purchase

0 as GRPO, 0 AS GOODRETURN,0 AS APINVOICE,0 AS APCREDITNOTE,0 AS LANDEDCOST

,0 AS DELIVERY,sum(T1.Debit-T1.Credit) AS SALESRETURN,0 AS ARINVOICE,0 AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,0 AS STOCKVALUATION,0 AS PRODUCTIONORDER,0 AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,0 as OPENING,0 AS INCOMINGPAYMENT,0 AS AUTORECO,0 AS INERTRECO

,0 as CLBalance

FROM

OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate >= ‘[%0]’ and T0.RefDate <= ‘[%1]’and

T0.TransType in (’16’) –and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’41000000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–sum(T1.Debit-T1.Credit) as Purchase

0 as GRPO, 0 AS GOODRETURN,0 AS APINVOICE,0 AS APCREDITNOTE,0 AS LANDEDCOST

,0 AS DELIVERY,0 AS SALESRETURN,sum(T1.Debit-T1.Credit) AS ARINVOICE,0 AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,0 AS STOCKVALUATION,0 AS PRODUCTIONORDER,0 AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,0 as OPENING,0 AS INCOMINGPAYMENT,0 AS AUTORECO,0 AS INERTRECO

,0 as CLBalance

FROM

OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate >= ‘[%0]’ and T0.RefDate <= ‘[%1]’and

T0.TransType in (’13’) —and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’41000000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–sum(T1.Debit-T1.Credit) as Purchase

0 as GRPO, 0 AS GOODRETURN,0 AS APINVOICE,0 AS APCREDITNOTE,0 AS LANDEDCOST

,0 AS DELIVERY,0 AS SALESRETURN,0 AS ARINVOICE,sum(T1.Debit-T1.Credit) AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,0 AS STOCKVALUATION,0 AS PRODUCTIONORDER,0 AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,0 as OPENING,0 AS INCOMINGPAYMENT,0 AS AUTORECO,0 AS INERTRECO

,0 as CLBalance

FROM

OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate >= ‘[%0]’ and T0.RefDate <= ‘[%1]’and

T0.TransType in (’14’) —and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’41000000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–sum(T1.Debit-T1.Credit) as Purchase

0 as GRPO, 0 AS GOODRETURN,0 AS APINVOICE,0 AS APCREDITNOTE,0 AS LANDEDCOST

,0 AS DELIVERY,0 AS SALESRETURN,0 AS ARINVOICE,0 AS ARCREDITNOTE

,sum(T1.Debit-T1.Credit) AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,0 AS STOCKVALUATION,0 AS PRODUCTIONORDER,0 AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,0 as OPENING,0 AS INCOMINGPAYMENT,0 AS AUTORECO,0 AS INERTRECO

,0 as CLBalance

FROM

OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate >= ‘[%0]’ and T0.RefDate <= ‘[%1]’and

T0.TransType in (‘10000071’) —and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’41000000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–sum(T1.Debit-T1.Credit) as Purchase

0 as GRPO, 0 AS GOODRETURN,0 AS APINVOICE,0 AS APCREDITNOTE,0 AS LANDEDCOST

,0 AS DELIVERY,0 AS SALESRETURN,0 AS ARINVOICE,0 AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,sum(T1.Debit-T1.Credit) AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,0 AS STOCKVALUATION,0 AS PRODUCTIONORDER,0 AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,0 as OPENING,0 AS INCOMINGPAYMENT,0 AS AUTORECO,0 AS INERTRECO

,0 as CLBalance

FROM

OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate >= ‘[%0]’ and T0.RefDate <= ‘[%1]’and

T0.TransType in (‘310000001’) –and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’41000000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–sum(T1.Debit-T1.Credit) as Purchase

0 as GRPO, 0 AS GOODRETURN,0 AS APINVOICE,0 AS APCREDITNOTE,0 AS LANDEDCOST

,0 AS DELIVERY,0 AS SALESRETURN,0 AS ARINVOICE,0 AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,sum(T1.Debit-T1.Credit) AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,0 AS STOCKVALUATION,0 AS PRODUCTIONORDER,0 AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,0 as OPENING,0 AS INCOMINGPAYMENT,0 AS AUTORECO,0 AS INERTRECO

,0 as CLBalance

FROM

OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate >= ‘[%0]’ and T0.RefDate <= ‘[%1]’and

T0.TransType in (’59’) –and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’41000000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–sum(T1.Debit-T1.Credit) as Purchase

0 as GRPO, 0 AS GOODRETURN,0 AS APINVOICE,0 AS APCREDITNOTE,0 AS LANDEDCOST

,0 AS DELIVERY,0 AS SALESRETURN,0 AS ARINVOICE,0 AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,sum(T1.Debit-T1.Credit) AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,0 AS STOCKVALUATION,0 AS PRODUCTIONORDER,0 AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,0 as OPENING,0 AS INCOMINGPAYMENT,0 AS AUTORECO,0 AS INERTRECO

,0 as CLBalance

FROM

OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate >= ‘[%0]’ and T0.RefDate <= ‘[%1]’and

T0.TransType in (’60’) –and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’41000000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–sum(T1.Debit-T1.Credit) as Purchase

0 as GRPO, 0 AS GOODRETURN,0 AS APINVOICE,0 AS APCREDITNOTE,0 AS LANDEDCOST

,0 AS DELIVERY,0 AS SALESRETURN,0 AS ARINVOICE,0 AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,sum(T1.Debit-T1.Credit) AS INVENTORYTRANSFER,0 AS STOCKVALUATION,0 AS PRODUCTIONORDER,0 AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,0 as OPENING,0 AS INCOMINGPAYMENT,0 AS AUTORECO,0 AS INERTRECO

,0 as CLBalance

FROM

OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate >= ‘[%0]’ and T0.RefDate <= ‘[%1]’and

T0.TransType in (’67’) –and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’41000000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–sum(T1.Debit-T1.Credit) as Purchase

0 as GRPO, 0 AS GOODRETURN,0 AS APINVOICE,0 AS APCREDITNOTE,0 AS LANDEDCOST

,0 AS DELIVERY,0 AS SALESRETURN,0 AS ARINVOICE,0 AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,sum(T1.Debit-T1.Credit) AS STOCKVALUATION,0 AS PRODUCTIONORDER,0 AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,0 as OPENING,0 AS INCOMINGPAYMENT,0 AS AUTORECO,0 AS INERTRECO

,0 as CLBalance

FROM

OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate >= ‘[%0]’ and T0.RefDate <= ‘[%1]’and

T0.TransType in (‘162’) –and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’41000000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–sum(T1.Debit-T1.Credit) as Purchase

0 as GRPO, 0 AS GOODRETURN,0 AS APINVOICE,0 AS APCREDITNOTE,0 AS LANDEDCOST

,0 AS DELIVERY,0 AS SALESRETURN,0 AS ARINVOICE,0 AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,0 AS STOCKVALUATION,sum(T1.Debit-T1.Credit) AS PRODUCTIONORDER,0 AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,0 as OPENING,0 AS INCOMINGPAYMENT,0 AS AUTORECO,0 AS INERTRECO

,0 as CLBalance

FROM

OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate >= ‘[%0]’ and T0.RefDate <= ‘[%1]’and

T0.TransType in (‘202’) –and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’41000000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–sum(T1.Debit-T1.Credit) as Purchase

0 as GRPO, 0 AS GOODRETURN,0 AS APINVOICE,0 AS APCREDITNOTE,0 AS LANDEDCOST

,0 AS DELIVERY,0 AS SALESRETURN,0 AS ARINVOICE,0 AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,0 AS STOCKVALUATION,0 AS PRODUCTIONORDER,sum(T1.Debit-T1.Credit) AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,0 as OPENING,0 AS INCOMINGPAYMENT,0 AS AUTORECO,0 AS INERTRECO

,0 as CLBalance

FROM

OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate >= ‘[%0]’ and T0.RefDate <= ‘[%1]’and

T0.TransType in (’30’) –and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’41000000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–sum(T1.Debit-T1.Credit) as Purchase

0 as GRPO, 0 AS GOODRETURN,0 AS APINVOICE,0 AS APCREDITNOTE,0 AS LANDEDCOST

,0 AS DELIVERY,0 AS SALESRETURN,0 AS ARINVOICE,0 AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,0 AS STOCKVALUATION,0 AS PRODUCTIONORDER,0 AS JOURNALENTRY,sum(T1.Debit-T1.Credit) AS OUTGOINGPAYMENT

,0 as OPENING,0 AS INCOMINGPAYMENT,0 AS AUTORECO,0 AS INERTRECO

,0 as CLBalance

FROM

OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate >= ‘[%0]’ and T0.RefDate <= ‘[%1]’and

T0.TransType in (’46’) –and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’41000000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–sum(T1.Debit-T1.Credit) as Purchase

0 as GRPO, 0 AS GOODRETURN,0 AS APINVOICE,0 AS APCREDITNOTE,0 AS LANDEDCOST

,0 AS DELIVERY,0 AS SALESRETURN,0 AS ARINVOICE,0 AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,0 AS STOCKVALUATION,0 AS PRODUCTIONORDER,0 AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,sum(T1.Debit-T1.Credit) as OPENING,0 AS INCOMINGPAYMENT,0 AS AUTORECO,0 AS INERTRECO

,0 as CLBalance

FROM

OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate >= ‘[%0]’ and T0.RefDate <= ‘[%1]’and

T0.TransType in (‘-2’) –and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’41000000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–sum(T1.Debit-T1.Credit) as Purchase

0 as GRPO, 0 AS GOODRETURN,0 AS APINVOICE,0 AS APCREDITNOTE,0 AS LANDEDCOST

,0 AS DELIVERY,0 AS SALESRETURN,0 AS ARINVOICE,0 AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,0 AS STOCKVALUATION,0 AS PRODUCTIONORDER,0 AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,0 as OPENING,sum(T1.Debit-T1.Credit) AS INCOMINGPAYMENT,0 AS AUTORECO,0 AS INERTRECO

,0 as CLBalance

FROM

OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate >= ‘[%0]’ and T0.RefDate <= ‘[%1]’and

T0.TransType in (’24’) –and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’41000000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–sum(T1.Debit-T1.Credit) as Purchase

0 as GRPO, 0 AS GOODRETURN,0 AS APINVOICE,0 AS APCREDITNOTE,0 AS LANDEDCOST

,0 AS DELIVERY,0 AS SALESRETURN,0 AS ARINVOICE,0 AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,0 AS STOCKVALUATION,0 AS PRODUCTIONORDER,0 AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,0 as OPENING,0 AS INCOMINGPAYMENT,sum(T1.Debit-T1.Credit) AS AUTORECO,0 AS INERTRECO

,0 as CLBalance

FROM

OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate >= ‘[%0]’ and T0.RefDate <= ‘[%1]’and

T0.TransType in (‘321’) –and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’41000000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–sum(T1.Debit-T1.Credit) as Purchase

0 as GRPO, 0 AS GOODRETURN,0 AS APINVOICE,0 AS APCREDITNOTE,0 AS LANDEDCOST

,0 AS DELIVERY,0 AS SALESRETURN,0 AS ARINVOICE,0 AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,0 AS STOCKVALUATION,0 AS PRODUCTIONORDER,0 AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,0 as OPENING,0 AS INCOMINGPAYMENT,0 AS AUTORECO,sum(T1.Debit-T1.Credit) AS INERTRECO

,0 as CLBalance

FROM

OJDT T0 

INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate >= ‘[%0]’ and T0.RefDate <= ‘[%1]’and

T0.TransType in (‘-4’) –and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’41000000′,’25080000’)

GROUP BY

T1.Account,c.acctname

union all

SELECT

T1.Account as Account,c.acctname as AccountName, 0 as OPBalance,

–0 as Purchase

0 as GRPO, 0 AS GOODRETURN,0 AS APINVOICE,0 AS APCREDITNOTE,0 AS LANDEDCOST

,0 AS DELIVERY,0 AS SALESRETURN,0 AS ARINVOICE,0 AS ARCREDITNOTE

,0 AS MANUALSTOCKRECEIPT,0 AS INSIALOPENING,0 AS RECEIPTFORMPRODUCTION,0 AS ISSUEDFROMPRODUCTION

,0 AS INVENTORYTRANSFER,0 AS STOCKVALUATION,0 AS PRODUCTIONORDER,0 AS JOURNALENTRY,0 AS OUTGOINGPAYMENT

,0 as OPENING,0 AS INCOMINGPAYMENT,0 AS AUTORECO,0 AS INERTRECO

,sum(T1.Debit-T1.Credit) as CLBalance

FROM

OJDT T0  INNER JOIN JDT1 T1 ON T0.TransId = T1.TransId

left outer join oact c on T1.account = c.acctcode

WHERE T0.RefDate <= ‘[%1]’ –and

–c.fathernum in (‘25010000′,’25020000′,’25030000′,’25050000′,’25060000′,’25070000′,’25080000’)

GROUP BY

T1.Account,c.acctname

) as a

group by a.Account,a.AccountName

order by a.Account,a.AccountName

Thanks,

Harshal Makwana