Skip to Content
0
Former Member
Mar 14, 2009 at 07:46 AM

stored procedure for GL Ledger

40 Views

Hi experts...

I have been written some queries as below : -

My friend said need to include OJDT and JDT1 in order to diffrential "Credit". But i did it half way don't know how to separe it.....

CREATE PROCEDURE dbo.GL_Report

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

-- AR INV

SET NOCOUNT ON;

SELECT NNM1.BeginStr, ORIN.DocNum, ORIN.DocDate, ORIN.Series, ORIN.CardCode, ORIN.CardName, ORIN.Comments, RIN1.ItemCode,RIN1.AcctCode, RIN1.Dscription, RIN1.Quantity, ORIN.DocCur, ORIN.DocRate, RIN1.Price, RIN1.LineTotal AS Debit, 0 AS Credit ,RIN1.TotalFrgn, null,null,null,CONVERT(char(10),ORIN.DocType)

FROM ORIN INNER JOIN RIN1 ON ORIN.DocEntry = RIN1.DocEntry LEFT JOIN

NNM1 ON ORIN.Series = NNM1.Series

--WHERE RIN1.ItemCode NOT BETWEEN 'ZZ0001' AND 'ZZ9999'

UNION

SELECT NNM1.BeginStr, ORIN.DocNum, ORIN.DocDate, ORIN.Series, ORIN.CardCode, ORIN.CardName, ORIN.Comments, RIN1.ItemCode,RIN1.AcctCode, RIN1.Dscription, RIN1.Quantity, ORIN.DocCur, ORIN.DocRate, RIN1.Price,0 AS Debit,RIN1.LineTotal AS Credit, RIN1.TotalFrgn, null,null,null,CONVERT(char(10),ORIN.DocType)

FROM ORIN INNER JOIN RIN1 ON ORIN.DocEntry = RIN1.DocEntry LEFT JOIN

NNM1 ON ORIN.Series = NNM1.Series

-- JE

--UNION

--SELECT NNM1.BeginStr, OJDT.TransId ,OJDT.RefDate,OJDT.DocSeries,null, null,null,null,null,null,null,null,null,null,null,JDT1.Account,JDT1.Debit, JDT1.Credit,OJDT.Memo, --OJDT.Ref2,OJDT.Ref1

--FROM OJDT INNER JOIN JDT1 ON OJDT.TransId = JDT1.TransId LEFT JOIN

--NNM1 ON OJDT.Series = NNM1.Series

-- AR CM

UNION

SELECT NNM1.BeginStr, OINV.DocNum, OINV.DocDate, OINV.Series, OINV.CardCode, OINV.CardName, OINV.Comments, INV1.ItemCode,INV1.AcctCode, INV1.Dscription, INV1.Quantity, OINV.DocCur, OINV.DocRate, INV1.Price, INV1.LineTotal AS Debit, 0 AS Credit,INV1.TotalFrgn,null,null,null,CONVERT(char(10),OINV.DocType)

FROM OINV INNER JOIN INV1 ON OINV.DocEntry = INV1.DocEntry LEFT JOIN

NNM1 ON OINV.Series = NNM1.Series

UNION

SELECT NNM1.BeginStr, OINV.DocNum, OINV.DocDate, OINV.Series, OINV.CardCode, OINV.CardName, OINV.Comments, INV1.ItemCode,INV1.AcctCode, INV1.Dscription, INV1.Quantity, OINV.DocCur, OINV.DocRate, INV1.Price, 0 AS Debit,INV1.LineTotal AS Credit,INV1.TotalFrgn,null,null,null,CONVERT(char(10),OINV.DocType)

FROM OINV INNER JOIN INV1 ON OINV.DocEntry = INV1.DocEntry LEFT JOIN

NNM1 ON OINV.Series = NNM1.Series

-- AP INV

UNION

SELECT NNM1.BeginStr, OPCH.DocNum, OPCH.DocDate, OPCH.Series, OPCH.CardCode, OPCH.CardName,OPCH.Comments, PCH1.ItemCode,PCH1.AcctCode, PCH1.Dscription,PCH1.Quantity, OPCH.DocCur, OPCH.DocRate, PCH1.Price, PCH1.LineTotal AS Debit, 0 AS Credit,PCH1.TotalFrgn,null,null,null,CONVERT(char(10),OPCH.DocType)

FROM OPCH INNER JOIN PCH1 ON OPCH.DocEntry = PCH1.DocEntry LEFT JOIN

NNM1 ON OPCH.Series = NNM1.Series

UNION

SELECT NNM1.BeginStr, OPCH.DocNum, OPCH.DocDate, OPCH.Series, OPCH.CardCode, OPCH.CardName,OPCH.Comments, PCH1.ItemCode,PCH1.AcctCode, PCH1.Dscription,PCH1.Quantity, OPCH.DocCur, OPCH.DocRate, PCH1.Price,0 AS Debit, PCH1.LineTotal AS Credit, PCH1.TotalFrgn,null,null,null,CONVERT(char(10),OPCH.DocType)

FROM OPCH INNER JOIN PCH1 ON OPCH.DocEntry = PCH1.DocEntry LEFT JOIN

NNM1 ON OPCH.Series = NNM1.Series

-- AP CM

UNION

SELECT NNM1.BeginStr, ORPC.DocNum, ORPC.DocDate, ORPC.Series, ORPC.CardCode, ORPC.CardName,ORPC.Comments, RPC1.ItemCode,RPC1.AcctCode, RPC1.Dscription,RPC1.Quantity, ORPC.DocCur, ORPC.DocRate, RPC1.Price, RPC1.LineTotal AS Debit, 0 AS Credit,RPC1.TotalFrgn,null,null,null,CONVERT(char(10),ORPC.DocType)

FROM ORPC INNER JOIN RPC1 ON ORPC.DocEntry = RPC1.DocEntry LEFT JOIN

NNM1 ON ORPC.Series = NNM1.Series

UNION

SELECT NNM1.BeginStr, ORPC.DocNum, ORPC.DocDate, ORPC.Series, ORPC.CardCode, ORPC.CardName,ORPC.Comments, RPC1.ItemCode,RPC1.AcctCode, RPC1.Dscription,RPC1.Quantity, ORPC.DocCur, ORPC.DocRate, RPC1.Price,0 AS Debit, RPC1.LineTotal AS Credit, RPC1.TotalFrgn,null,null,null,CONVERT(char(10),ORPC.DocType)

FROM ORPC INNER JOIN RPC1 ON ORPC.DocEntry = RPC1.DocEntry LEFT JOIN

NNM1 ON ORPC.Series = NNM1.Series

-- GOOD RETURN

UNION

SELECT NNM1.BeginStr, ORDN.DocNum, ORDN.DocDate, ORDN.Series, ORDN.CardCode, ORDN.CardName,ORDN.Comments, RDN1.ItemCode,RDN1.AcctCode, RDN1.Dscription,RDN1.Quantity, ORDN.DocCur, ORDN.DocRate, RDN1.Price, RDN1.LineTotal AS Debit, 0 AS Credit,RDN1.TotalFrgn,null,null,null,CONVERT(char(10),ORDN.DocType)

FROM ORDN INNER JOIN RDN1 ON ORDN.DocEntry = RDN1.DocEntry LEFT JOIN

NNM1 ON ORDN.Series = NNM1.Series

UNION

SELECT NNM1.BeginStr, ORDN.DocNum, ORDN.DocDate, ORDN.Series, ORDN.CardCode, ORDN.CardName,ORDN.Comments, RDN1.ItemCode,RDN1.AcctCode, RDN1.Dscription,RDN1.Quantity, ORDN.DocCur, ORDN.DocRate, RDN1.Price,0 AS Debit,RDN1.LineTotal AS Credit,RDN1.TotalFrgn,null,null,null,CONVERT(char(10),ORDN.DocType)

FROM ORDN INNER JOIN RDN1 ON ORDN.DocEntry = RDN1.DocEntry LEFT JOIN

NNM1 ON ORDN.Series = NNM1.Series

-- GOOD RECEIPT PO

UNION

SELECT NNM1.BeginStr, OPDN.DocNum, OPDN.DocDate, OPDN.Series, OPDN.CardCode, OPDN.CardName,OPDN.Comments, PDN1.ItemCode,PDN1.AcctCode, PDN1.Dscription,PDN1.Quantity, OPDN.DocCur, OPDN.DocRate, PDN1.Price, PDN1.LineTotal AS Debit, 0 AS Credit,PDN1.TotalFrgn,null,null,null,CONVERT(char(10),OPDN.DocType)

FROM OPDN INNER JOIN PDN1 ON OPDN.DocEntry = PDN1.DocEntry LEFT JOIN

NNM1 ON OPDN.Series = NNM1.Series

UNION

SELECT NNM1.BeginStr, OPDN.DocNum, OPDN.DocDate, OPDN.Series, OPDN.CardCode, OPDN.CardName,OPDN.Comments, PDN1.ItemCode,PDN1.AcctCode, PDN1.Dscription,PDN1.Quantity, OPDN.DocCur, OPDN.DocRate, PDN1.Price, 0 AS Debit,PDN1.LineTotal AS Credit,PDN1.TotalFrgn,null,null,null,CONVERT(char(10),OPDN.DocType)

FROM OPDN INNER JOIN PDN1 ON OPDN.DocEntry = PDN1.DocEntry LEFT JOIN

NNM1 ON OPDN.Series = NNM1.Series

-- INCOMING (ACCOUNT)

UNION

SELECT NNM1.BeginStr, ORCT.DocNum, ORCT.DocDate, ORCT.Series, ORCT.CardCode, ORCT.CardName, ORCT.Comments, RCT4.Descrip, RCT4.AcctCode, RCT4.AcctName,null,

null, null, null, RCT4.SumApplied AS Debit, 0 AS Credit , null,null,null,null,CONVERT(char(10),ORCT.DocType)

FROM ORCT INNER JOIN RCT4 ON ORCT.DocNum = RCT4.DocNum LEFT JOIN

NNM1 ON ORCT.Series = NNM1.Series

UNION

SELECT NNM1.BeginStr, ORCT.DocNum, ORCT.DocDate, ORCT.Series, ORCT.CardCode, ORCT.CardName, ORCT.Comments, RCT4.Descrip, RCT4.AcctCode, RCT4.AcctName,null,

null, null, null,0 AS Debit, RCT4.SumApplied AS Credit, null,null,null,null,CONVERT(char(10),ORCT.DocType)

FROM ORCT INNER JOIN RCT4 ON ORCT.DocNum = RCT4.DocNum LEFT JOIN

NNM1 ON ORCT.Series = NNM1.Series

-- INCOMING (PAY TO)

UNION

SELECT NNM1.BeginStr, ORCT.DocNum, ORCT.DocDate, ORCT.Series, ORCT.CardCode, ORCT.CardName,ORCT.Comments,RCT1.BankCode,RCT1.CheckAct,null,null,null,null,null,RCT1.CheckSum, null,RCT1.CheckNum AS Debit, 0 AS Credit, null,null,CONVERT(char(10),ORCT.DocType)

FROM ORCT INNER JOIN RCT1 ON ORCT.DocNum = RCT1.DocNum LEFT JOIN

NNM1 ON ORCT.Series = NNM1.Series

UNION

SELECT NNM1.BeginStr, ORCT.DocNum, ORCT.DocDate, ORCT.Series, ORCT.CardCode, ORCT.CardName,ORCT.Comments,RCT1.BankCode,RCT1.CheckAct,null,null,null,null,null,RCT1.CheckSum, null,0 AS Debit,RCT1.CheckNum AS Credit, null,null,CONVERT(char(10),ORCT.DocType)

FROM ORCT INNER JOIN RCT1 ON ORCT.DocNum = RCT1.DocNum LEFT JOIN

NNM1 ON ORCT.Series = NNM1.Series

-- OUTGOING (ACCOUNT)

UNION

SELECT NNM1.BeginStr, OVPM.DocNum, OVPM.DocDate, OVPM.Series, OVPM.CardCode, OVPM.CardName, OVPM.Comments, VPM4.Descrip, VPM4.AcctCode, VPM4.AcctName,null,

null, null, null, VPM4.SumApplied AS Debit, 0 AS Credit, null,null,null,null,CONVERT(char(10),OVPM.DocType)

FROM OVPM INNER JOIN VPM4 ON OVPM.DocNum = VPM4.DocNum LEFT JOIN

NNM1 ON OVPM.Series = NNM1.Series

UNION

SELECT NNM1.BeginStr, OVPM.DocNum, OVPM.DocDate, OVPM.Series, OVPM.CardCode, OVPM.CardName, OVPM.Comments, VPM4.Descrip, VPM4.AcctCode, VPM4.AcctName,null,

null, null, null,0 AS Debit, VPM4.SumApplied AS Credit, null,null,null,null,CONVERT(char(10),OVPM.DocType)

FROM OVPM INNER JOIN VPM4 ON OVPM.DocNum = VPM4.DocNum LEFT JOIN

NNM1 ON OVPM.Series = NNM1.Series

-- OUTGOING (PAY TO)

UNION

SELECT NNM1.BeginStr, OVPM.DocNum, OVPM.DocDate, OVPM.Series, OVPM.CardCode, OVPM.CardName,OVPM.Comments,VPM1.BankCode,VPM1.CheckAct,null,null,null,null,null,VPM1.CheckSum, null,VPM1.CheckNum AS Debit, 0 AS Credit,null, null,CONVERT(char(10),OVPM.DocType)

FROM OVPM INNER JOIN VPM1 ON OVPM.DocNum = VPM1.DocNum LEFT JOIN

NNM1 ON OVPM.Series = NNM1.Series

UNION

SELECT NNM1.BeginStr, OVPM.DocNum, OVPM.DocDate, OVPM.Series, OVPM.CardCode, OVPM.CardName,OVPM.Comments,VPM1.BankCode,VPM1.CheckAct,null,null,null,null,null,VPM1.CheckSum, null,0 AS Debit,VPM1.CheckNum AS Credit, null, null,CONVERT(char(10),OVPM.DocType)

FROM OVPM INNER JOIN VPM1 ON OVPM.DocNum = VPM1.DocNum LEFT JOIN

NNM1 ON OVPM.Series = NNM1.Series

END

GO

Thanks

Regards,

Danny