Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE [EUROPA_PROD_DB]
- GO
- /****** Object: View [dbo].[vw_Transactions] Script Date: 05/04/2012 12:51:24 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE VIEW [dbo].[vw_Transactions] AS
- SELECT
- TA.AcctCode,TA.AcctName,TA.GroupMask,TA.Levels,TA.ActType,TA.L1Parent,TA.L2Parent,
- TA.L3Parent,TA.L4Parent,
- TH.TransId,TH.TransType,TH.RefDate PostingDate,TH.DueDate,
- TH.TaxDate,TD.Account,TD.Debit,TD.Credit
- FROM OJDT TH
- INNER JOIN JDT1 TD ON TH.TransId=TD.TransId
- INNER JOIN vw_AccountDetails TA ON TD.Account=TA.AcctCode
- GO
- USE [EUROPA_PROD_DB]
- GO
- /****** Object: View [dbo].[vw_AccountDetails] Script Date: 05/04/2012 12:50:39 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE VIEW [dbo].[vw_AccountDetails]
- AS
- SELECT TA.AcctCode,TA.AcctName,TA.GroupMask,TA.Levels,TA.ActType,
- CASE Levels
- WHEN 1 THEN
- TA.AcctName
- WHEN 2 THEN
- (SELECT TA1.AcctName FROM OACT TA1 WHERE TA1.AcctCode=TA.FatherNum)
- WHEN 3 THEN
- (SELECT TA3.AcctName FROM OACT TA3 WHERE TA3.AcctCode=
- (SELECT TA2.FatherNum FROM OACT TA2 WHERE TA2.AcctCode=
- (SELECT TA1.AcctCode FROM OACT TA1 WHERE TA1.AcctCode=TA.FatherNum)))
- WHEN 4 THEN
- (SELECT TA3.AcctName FROM OACT TA3 WHERE TA3.AcctCode=
- (SELECT TA3.FatherNum FROM OACT TA3 WHERE TA3.AcctCode=
- (SELECT TA2.FatherNum FROM OACT TA2 WHERE TA2.AcctCode=
- (SELECT TA1.AcctCode FROM OACT TA1 WHERE TA1.AcctCode=TA.FatherNum))))
- WHEN 5 THEN
- (SELECT TA3.AcctName FROM OACT TA3 WHERE TA3.AcctCode=
- (SELECT TA3.FatherNum FROM OACT TA3 WHERE TA3.AcctCode=
- (SELECT TA3.FatherNum FROM OACT TA3 WHERE TA3.AcctCode=
- (SELECT TA2.FatherNum FROM OACT TA2 WHERE TA2.AcctCode=
- (SELECT TA1.AcctCode FROM OACT TA1 WHERE TA1.AcctCode=TA.FatherNum)))))
- ELSE '' END AS L1Parent,
- CASE Levels
- WHEN 1 THEN
- ''
- WHEN 2 THEN
- TA.AcctName
- WHEN 3 THEN
- (SELECT TA1.AcctName FROM OACT TA1 WHERE TA1.AcctCode=TA.FatherNum)
- WHEN 4 THEN
- (SELECT TA3.AcctName FROM OACT TA3 WHERE TA3.AcctCode=
- (SELECT TA2.FatherNum FROM OACT TA2 WHERE TA2.AcctCode=
- (SELECT TA1.AcctCode FROM OACT TA1 WHERE TA1.AcctCode=TA.FatherNum)))
- WHEN 5 THEN
- (SELECT TA3.AcctName FROM OACT TA3 WHERE TA3.AcctCode=
- (SELECT TA3.FatherNum FROM OACT TA3 WHERE TA3.AcctCode=
- (SELECT TA2.FatherNum FROM OACT TA2 WHERE TA2.AcctCode=
- (SELECT TA1.AcctCode FROM OACT TA1 WHERE TA1.AcctCode=TA.FatherNum))))
- ELSE '' END AS L2Parent,
- CASE Levels
- WHEN 1 THEN
- ''
- WHEN 2 THEN
- ''
- WHEN 3 THEN
- TA.AcctName
- WHEN 4 THEN
- (SELECT TA1.AcctName FROM OACT TA1 WHERE TA1.AcctCode=TA.FatherNum)
- WHEN 5 THEN
- (SELECT TA3.AcctName FROM OACT TA3 WHERE TA3.AcctCode=
- (SELECT TA2.FatherNum FROM OACT TA2 WHERE TA2.AcctCode=
- (SELECT TA1.AcctCode FROM OACT TA1 WHERE TA1.AcctCode=TA.FatherNum)))
- ELSE '' END AS L3Parent,
- CASE Levels
- WHEN 1 THEN
- ''
- WHEN 2 THEN
- ''
- WHEN 3 THEN
- ''
- WHEN 4 THEN
- TA.AcctName
- WHEN 5 THEN
- (SELECT TA1.AcctName FROM OACT TA1 WHERE TA1.AcctCode=TA.FatherNum)
- ELSE '' END AS L4Parent
- FROM OACT TA
- WHERE TA.Postable='Y'
- GO
- CREATE PROCEDURE sp_CustomProfit_And_Loss
- (
- @BeginDate DATETIME,
- @EndDate DATETIME
- )
- AS
- BEGIN
- SELECT L1Parent,L2Parent,Balance,RowOrder
- FROM fn_CustomProfit_And_Loss(@BeginDate,@EndDate)
- order by RowOrder
- END
- CREATE FUNCTION fn_CustomProfit_And_Loss
- (
- @BeginDate DATETIME,
- @EndDate DATETIME
- )
- RETURNS @table TABLE(
- L1Parent VARCHAR(72),
- L2Parent VARCHAR(72),
- Balance MONEY,
- RowOrder INT
- )
- AS
- BEGIN
- SET @BeginDate=CAST(YEAR(@BeginDate) AS CHAR(4))+'-'+CAST(MONTH(@BeginDate) AS CHAR(2)) +'-'+ CAST(DAY(@BeginDate) AS CHAR(2))
- SET @EndDate=CAST(YEAR(@EndDate) AS CHAR(4))+'-'+CAST(MONTH(@EndDate) AS CHAR(2)) +'-'+ CAST(DAY(@EndDate) AS CHAR(2))
- --SET @EndDate='2012-12-31'
- INSERT INTO @table
- SELECT
- CASE L1Parent WHEN 'Turnover' THEN 'Income' ELSE L1Parent END L1Parent,
- L2Parent,
- SUM(Credit-Debit) Balance,
- 1 RowOrder
- FROM vw_Transactions
- WHERE GroupMask=4--Income
- AND PostingDate BETWEEN @BeginDate AND @EndDate
- GROUP BY L1Parent,L2Parent
- UNION ALL
- SELECT
- CASE L1Parent WHEN 'Cost of Sales' THEN 'Cost Of Goods Sold' ELSE L1Parent END L1Parent,
- L2Parent,
- SUM(Debit-Credit) Balance,
- 2 RowOrder
- FROM vw_Transactions
- WHERE GroupMask=5--COGS
- AND PostingDate BETWEEN @BeginDate AND @EndDate
- GROUP BY L1Parent,L2Parent
- UNION ALL
- SELECT
- CASE L1Parent WHEN 'Operating Costs' THEN 'Expenses' ELSE L1Parent END L1Parent,
- L2Parent,
- SUM(Debit-Credit) Balance,
- 3 RowOrder
- FROM vw_Transactions
- WHERE GroupMask IN(6,71,81)--Expenses
- AND PostingDate BETWEEN @BeginDate AND @EndDate
- GROUP BY L1Parent,L2Parent
- RETURN
- END
Add Comment
Please, Sign In to add comment