Table Name Groups
Table Name Ledgers
Table Name Ledger Opening Balances
Table Name Vouchers
Table Name Voucher Details
Table Name Financial Years
===============Store Procedure Example with Recursive Store Procedure=========
====================
This Store Procedure only Retrive ID and Check this Id is used in ParentGroup Id Or Not==================================================
For example -: in Leder Table the cash Ledger Id is 1 i will check that Ledger Id will used in parent Ledger Id or Not OR
Retrive In cash Ledger Has child ledger or Not==========
ALTER PROCEDURE [Ufms].[KC_SelectLedgersCash]
AS
BEGIN
WITH Ledgers AS (SELECT l.Ledger_ID, l.Ledger_Name, l.Parent_Ledger_ID, lo.Dr_Amount, lo.Cr_Amount,lo.Financial_Year
FROM Ufms.FMS_Ledgers AS l INNER JOIN
Ufms.FMS_LedgerOpeningBalances AS lo ON l.Ledger_ID = lo.Ledger_ID
WHERE (l.Ledger_ID = 1 )
UNION ALL
SELECT C.Ledger_ID,c.Ledger_Name,c.Parent_Ledger_ID,f.Dr_Amount,f.Cr_Amount,f.Financial_Year
FROM Ledgers AS f INNER JOIN
Ufms.FMS_Ledgers AS c ON f.Ledger_ID=c.Parent_Ledger_ID
WHERE c.is_Active=1 and c.is_Deleted=0)
SELECT Ledger_ID, Ledger_Name, Parent_Ledger_ID,Dr_Amount,Cr_Amount,Financial_Year
FROM Ledgers AS Ledgers_1
END
=======================Recursive Function ==================================
ALTER procedure [Ufms].[KC_SelectLedgersCashBank]
as
begin
WITH Ledgers AS (SELECT Ledger_ID, Ledger_Name, Parent_Ledger_ID
FROM Ufms.FMS_Ledgers
WHERE (Ledger_ID = 1 or Ledger_ID=2)
UNION ALL
SELECT c.Ledger_ID, c.Ledger_Name, c.Parent_Ledger_ID
FROM Ledgers AS f INNER JOIN
Ufms.FMS_Ledgers AS c ON f.ledger_id = c.Parent_Ledger_ID where c.is_active=1 and c.is_deleted=0)
SELECT Ledger_ID, Ledger_Name, Parent_Ledger_ID
FROM Ledgers AS Ledgers_1
end
==================================================================
--Author Satyabrata--11.08.2012
ALTER PROCEDURE Ufms.Kc_SelectReportLedgerBooks
@Fyear varchar(9)='2012-2013'
AS
BEGIN
SELECT
--From Ledger Table
l.Ledger_ID,l.Ledger_Name,
--From Ledger Opening Balance Table
lob.Dr_Amount as LedgerOBDrAmount,lob.Cr_Amount LedgerOBcrAmount,
lob.Financial_Year,lob.Is_Closed,
--From Voucher Table
v.Voucher_ID,v.Voucher_Date,convert(varchar(20),v.Voucher_Date,111) AS From_date,
v.Voucher_Type,v.Voucher_No,v.Approved,
--Voucher Details Table
vd.Dr_Amount,vd.Cr_Amount,
--Group Table
g.Group_ID,g.Group_Name
FROM
Ufms.FMS_Ledgers AS l
LEFT OUTER JOIN Ufms.FMS_LedgerOpeningBalances AS lob ON l.Ledger_ID=lob.Ledger_ID
LEFT OUTER JOIN Ufms.FMS_VoucherDetails AS vd ON l.Ledger_ID=vd.Ledger_ID
LEFT OUTER JOIN Ufms.FMS_Vouchers AS v ON v.Voucher_ID=vd.Voucher_ID
LEFT OUTER JOIN Ufms.FMS_Groups AS g ON l.Group_ID=g.Group_ID
WHERE lob.Financial_Year=@Fyear
END
===============================================================
============This Store Procedure is show only cash details or cash child details Means if Cash Ledger has Child Ledger so create a view that example is given below=============
That view is passe in this Storeprocedure because that view gives how much child ledger in a Parent Ledger
----------------------------------------------------------------------------------------------
ALTER PROCEDURE [Ufms].[KC_SelectCashDetails]
AS
BEGIN
SELECT CONVERT(varchar(20),V.Voucher_Date,103) AS VoucherDate,CONVERT(varchar(20),V.Voucher_Date,111) AS From_Date,
V.Voucher_No,V.Voucher_Type,V.Approved,VD.Voucher_Detail_ID,V.Voucher_ID,
VD.Dr_Amount,VD.Cr_Amount, L.Ledger_Name, L.Ledger_ID
FROM Ufms.FMS_VoucherDetails AS VD INNER JOIN
Ufms.FMS_Vouchers AS V ON VD.Voucher_ID=V.Voucher_ID INNER JOIN
Ufms.FMS_Ledgers AS L ON VD.Ledger_ID=L.Ledger_ID
WHERE L.Ledger_Id in (select ledger_id from Ufms.
Fms_SelectLedgersCash_View) and (V.Is_Active = 1) AND (V.Is_Deleted = 0) and (Vd.Is_Active = 1) AND (Vd.Is_Deleted = 0)and (l.Is_Active = 1) AND (l.Is_Deleted = 0)
END
=========================================================
===========This retrive how many child ledgers are in a parentLedger(cash) =================
Create view Fms_SelectLedgersCash_View
as
WITH Ledgers AS (SELECT Ledger_ID
FROM Ufms.FMS_Ledgers
WHERE Ledger_ID = '1'
UNION ALL
SELECT C.Ledger_ID
FROM Ledgers AS f INNER JOIN
Ufms.FMS_Ledgers AS c ON f.Ledger_ID=c.Parent_Ledger_ID
WHERE c.is_Active=1 and c.is_Deleted=0)
SELECT Ledger_ID
FROM
Ledgers AS
Ledgers_1
===================================================================
Auto Generated Store Procedure
ALTER PROCEDURE [Ufms].[KC_InsertVouchers]
(
--@Voucher_No varchar(50),
@Voucher_Date datetime,
@Company_ID int,
@Voucher_Type varchar(50),
@Voucher_Amount decimal(18,2),
@Transaction_Type varchar(50),
@Description varchar(500),
--@Created_On datetime,
@Created_By int,
--@Modified_On datetime,
@Modified_By int,
@Out1 int output,
@Out2 varchar(30) output,
@Bank_Reconciliation_Date datetime =null,
-- Parameters for VoucherDetails
@DataTableVoucherDetails AS Ufms.VoucherDetailsType READONLY,
@Mode varchar(10)=null,
@vID int=null
)
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
IF(@Mode!='Edit')
BEGIN
--Get the last Voucher No
DECLARE @Voucher_No varchar(30)
SELECT @Voucher_No=Voucher_No FROM Ufms.FMS_Vouchers WHERE Voucher_Type=@Voucher_Type
DECLARE @Year int
SELECT @Year=year(getdate())
-- Generate the current Financial Year
DECLARE @FYear varchar(10)
IF MONTH(GETDATE())<3
BEGIN
SELECT @FYear=CONVERT(VARCHAR, @Year-1)+'-'+CONVERT(VARCHAR, @Year)
END
ELSE
BEGIN
SELECT @FYear=CONVERT(VARCHAR, @Year)+'-'+CONVERT(VARCHAR, @Year+1)
END
--Get the setup informations for voucher creation
DECLARE @Prefix varchar(10)
DECLARE @With_Financial_Year bit
DECLARE @Auto_Number_Size int
SELECT @Prefix=Prefix, @With_Financial_Year=With_Financial_Year, @Auto_Number_Size=Auto_Number_Size
FROM Ufms.FMS_VoucherSetupDetails
WHERE Voucher_Type=@Voucher_Type AND Company_ID=@Company_ID
SELECT @Auto_Number_Size
--Check the autonumber size
--Set the auto number size
DECLARE @i INT
SET @i=1
DECLARE @Size VARCHAR(10)
SET @Size=''
WHILE(@i<@Auto_Number_Size)
BEGIN
SET @Size=@Size+'0'
SET @i=@i+1
END
--If It's a first voucher entry then generate the 1st voucher no. formats according to the setup
IF @Voucher_No IS NULL
BEGIN
SET @Size=@Size+'1'
--Define VoucherNo
IF @With_Financial_Year=1
BEGIN
SELECT @Voucher_No=@Prefix+'/'+@Size+'/'+@FYear
END
ELSE
BEGIN
SELECT @Voucher_No=@Prefix+'/'+@Size
END
END
ELSE
-- If Voucher no is already generated
BEGIN
DECLARE @P1 VARCHAR(20)
DECLARE @P2 VARCHAR(20)
DECLARE @l int
SET @l=LEN(@Prefix)
SET @P1=Substring(@Voucher_No,1,@l)
SET @P2=Substring(@Voucher_No,@l+2,@Auto_Number_Size)
SET @P2=cast(@P2 as int)+1
SET @Size=@Size+'0'
SET @P2=RIGHT(@Size + RTRIM(@P2),@Auto_Number_Size)
IF @With_Financial_Year=1
BEGIN
DECLARE @P3 VARCHAR(20)
SET @P3=Substring(@Voucher_No,@l+@Auto_Number_Size+3,9)
SET @Voucher_No=@P1+'/'+@P2+'/'+@P3
SET @Out2=@Voucher_No
END
ELSE
BEGIN
SET @Voucher_No=@P1+'/'+@P2
SET @Out2=@Voucher_No
END
END
--Insert the record to Voucher Table
INSERT INTO Ufms.FMS_Vouchers(
Voucher_No,Voucher_Date,Company_ID,Voucher_Type,Voucher_Amount,
Description,Bank_Reconciliation_Date,Created_On,Created_By,Modified_On,Modified_By)
VALUES(
@Voucher_No,@Voucher_Date,@Company_ID,@Voucher_Type,@Voucher_Amount,
@Description,@Bank_Reconciliation_Date,GETDATE(),@Created_By,GETDATE(),@Modified_By)
--SELECT @Company_ID,@Voucher_No,@Prefix,@With_Financial_Year,@Auto_Number_Size,@Size
SET @Out1=SCOPE_IDENTITY()
DECLARE @Voucher_ID int
SET @Voucher_ID=SCOPE_IDENTITY()
SET @Out2=@Voucher_No
-- Insert Data into VoucherDetails
INSERT INTO Ufms.FMS_VoucherDetails
(Voucher_ID,Sl_No,Debit_Or_Credit,Ledger_ID,Dr_Amount,Cr_Amount,Transaction_Type,Cheque_Or_DD_No,Date,Bank_Name,Narration,
Created_On,Created_By,Modified_On,Modified_By)
SELECT
@Voucher_ID,Sl_No,Debit_Or_Credit,Ledger_ID,Dr_Amount,Cr_Amount,Transaction_Type,Cheque_Or_DD_No,Date,Bank_Name,Narration,
GETDATE(),@Created_By,GETDATE(),@Modified_By
FROM @DataTableVoucherDetails
END
ELSE
--For Edit
BEGIN
UPDATE Ufms.FMS_Vouchers
SET
Voucher_Date=@Voucher_Date,Company_ID=@Company_ID,Voucher_Type=@Voucher_Type,Voucher_Amount=@Voucher_Amount,
Description=@Description,Modified_On=GETDATE(),Modified_By=@Modified_By
WHERE
Voucher_ID=@vID
-- Delete Data from VoucherDetails
DELETE FROM Ufms.FMS_VoucherDetails WHERE Voucher_ID=@vID
-- Again Insert Data into VoucherDetails
INSERT INTO Ufms.FMS_VoucherDetails
(Voucher_ID,Sl_No,Debit_Or_Credit,Ledger_ID,Dr_Amount,Cr_Amount,Transaction_Type,Cheque_Or_DD_No,Date,Bank_Name,Narration,
Created_On,Created_By,Modified_On,Modified_By)
SELECT
@vID,Sl_No,Debit_Or_Credit,Ledger_ID,Dr_Amount,Cr_Amount,Transaction_Type,Cheque_Or_DD_No,Date,Bank_Name,Narration,
GETDATE(),@Created_By,GETDATE(),@Modified_By
FROM @DataTableVoucherDetails
END
-- End of Insert Data into VoucherDetails
--IF @@ERROR !=0
--ROLLBACK TRAN
--else
--COMMIT TRAN
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
END
