Hi,
As part of our QQube implementation, we are required to generate Balance sheet (and other) reports, akin to the Quickbooks report output, and not just for the 33 rollup months, but for any requested time period. We were able to generate a report on an accrual basis, but were unable to identify a field, value, or date table to use for cash, can you please point us to relevant documentation, or (even better) an example if possible, Below are our queries, which extract the GL to our own intermidiate table, followed by the function that creates the Baalance sheet (accrual based)
Any help or suggestion you can provide is appreciated. Thanks, Tal Our accrual data extract and balance sheet display process goes like this: 1. Create an extract from vf_GLDetail in a view called GetGlDetail. USE QQ_Import IF OBJECT_ID('dbo.GetGLDetail') IS NOT NULL DROP VIEW dbo.GetGLDetail GO Create View DBO.GetGLDetail AS SELECT CO.[Company Name], GLD.[GLTxn Document Master Linking Number], GLD.[GLTxn Document Reference Number], GLD.[GLTxn Document Current Posting State], GLD.[GLTxn Document Entry Type], GLD.[GLTxn Document Master Linking Txn Type], GLD.[GLTxn Document Type], GLD.[GLTxn Line Amount], GLD.[GLTxn Line Amount Type], GLD.[GLTxn Line Billable Status], GLD.[GLTxn Line Budget Amount], GLD.[GLTxn Line Credit Amount], GLD.[GLTxn Line Debit Amount], GLD.[GLTxn Line Forecast Amount], GLD.[GLTxn Line Item Description], GLD.[GLTxn Line Open Amount], GLD.[GLTxn Line Paid Amount], GLD.[GLTxn Line Paid/Open Status], GLD.[GLTxn Line Quantity], GLD.[GLTxn Line Rate], GLD.[GLTxn Line Rate Percent], From_ACCT.[Account Type] [From Account Type], From_ACCT.[Account Posting Type] [From Account Posting Type], From_ACCT.[Account Special Type] [From Account Special Type], From_ACCT.[Account NAME Grouping Mechanism - 1] [From Account NAME Grouping Mechanism - 1], From_ACCT.[Account NAME Grouping Mechanism - 2] [From Account NAME Grouping Mechanism - 2], From_ACCT.[Account NAME Grouping Mechanism - 3] [From Account NAME Grouping Mechanism - 3], From_ACCT.[Account NAME Grouping Mechanism - 4] [From Account NAME Grouping Mechanism - 4], From_ACCT.[Account NAME Grouping Mechanism - 5] [From Account NAME Grouping Mechanism - 5], From_ACCT.[Account BS Grouping - A] [From Account BS Grouping - A], From_ACCT.[Account BS Grouping - B] [From Account BS Grouping - B], From_ACCT.[Account BS Grouping - C] [From Account BS Grouping - C], From_ACCT.[Account BS Grouping - D] [From Account BS Grouping - D], From_Acct_Cash.[Cash Source Account Active] [From Cash Source Account Active], From_Acct_Cash.[Cash Source Account Cash Flow Classification] [From Cash Source Account Cash Flow Classification], From_Acct_Cash.[Cash Source Account Category] [From Cash Source Account Category], To_ACCT.[Account Type] [To Account Type], To_ACCT.[Account Posting Type] [To Account Posting Type], To_ACCT.[Account Special Type] [To Account Special Type], To_ACCT.[Account NAME Grouping Mechanism - 1] [To Account NAME Grouping Mechanism - 1], To_ACCT.[Account NAME Grouping Mechanism - 2] [To Account NAME Grouping Mechanism - 2], To_ACCT.[Account NAME Grouping Mechanism - 3] [To Account NAME Grouping Mechanism - 3], To_ACCT.[Account NAME Grouping Mechanism - 4] [To Account NAME Grouping Mechanism - 4], To_ACCT.[Account NAME Grouping Mechanism - 5] [To Account NAME Grouping Mechanism - 5], To_ACCT.[Account BS Grouping - A] [To Account BS Grouping - A], To_ACCT.[Account BS Grouping - B] [To Account BS Grouping - B], To_ACCT.[Account BS Grouping - C] [To Account BS Grouping - C], To_ACCT.[Account BS Grouping - D] [To Account BS Grouping - D], To_ACCT_Cash.[Cash Source Account Active] [To Cash Source Account Active], To_ACCT_Cash.[Cash Source Account Cash Flow Classification] [To Cash Source Account Cash Flow Classification], To_ACCT_Cash.[Cash Source Account Category] [To Cash Source Account Category], Classes.[Class Grouping Mechanism - 1], Classes.[Class Grouping Mechanism - 2], Classes.[Class Grouping Mechanism - 3], Classes.[Class Grouping Mechanism - 4], Classes.[Class Grouping Mechanism - 5], Customer.[Customer First Name], Customer.[Customer Last Name], Customer.[Customer Name], Customer.[Customer Billing Address State], Vendor.[Vendor First Name], Vendor.[Vendor Last Name], Vendor.[Vendor Name], Vendor.[Vendor Address State], Employee.[Employee First Name], Employee.[Employee Last Name], Employee.[Employee Name], Employee.[Employee Address State], OtherName.[OtherName First Name], OtherName.[OtherName Last Name], OtherName.[OtherName Name], OtherName.[OtherName Address State], Job.[Job Grouping Mechanism - 1], Job.[Job Grouping Mechanism - 2], Job.[Job Grouping Mechanism - 3], Job.[Job Grouping Mechanism - 4], Job.[Job Grouping Mechanism - 5], Item.[Item Grouping Mechanism - 1], Item.[Item Grouping Mechanism - 2], Item.[Item Grouping Mechanism - 3], Item.[Item Grouping Mechanism - 4], Item.[Item Grouping Mechanism - 5], SourceName.[Source Name First Name], SourceName.[Source Name Last Name], SourceName.[Source Name Name], PaymentMethod.[Payment Method Active], PaymentMethod.[Payment Method Name], PaymentMethod.[Payment Method Type], PayrollItem.[Payroll Item Active], PayrollItem.[Payroll Item Category], PayrollItem.[Payroll Item Expense Account Name], PayrollItem.[Payroll Item Expense Account Type], PayrollItem.[Payroll Item Liability Account Name], PayrollItem.[Payroll Item Liability Account Type], PayrollItem.[Payroll Item Name], PayrollItem.[Payroll Item Type], TxnDate.[CalYr Txn Date], TxnDate.[CalYr Txn Date Day is Weekend Day], TxnDate.[CalYr Txn Date Day Number], TxnDate.[CalYr Txn Date Day Number in Quarter], TxnDate.[CalYr Txn Date Day Number in Week], TxnDate.[CalYr Txn Date Day Number in Year], TxnDate.[CalYr Txn Date Day of Week], TxnDate.[CalYr Txn Date IS Last Day of Month], TxnDate.[CalYr Txn Date IS Last Day of Quarter], TxnDate.[CalYr Txn Date IS Last Day of Year], TxnDate.[CalYr Txn Date Month Name], TxnDate.[CalYr Txn Date Month Number], TxnDate.[CalYr Txn Date Month Begin Date], TxnDate.[CalYr Txn Date Month End Date], TxnDate.[CalYr Txn Date Year Number], TxnDate.[CalYr Txn Date Quarter Begin Date], TxnDate.[CalYr Txn Date Quarter End Date], TxnDate.[CalYr Txn Date Quarter Number] FROM vf_GLDETAIL GLD --Company LEFT JOIN vd_Company CO ON CO.LinkForCompanyID = GLD.LinkToCompanyID --Account Links LEFT JOIN vd_AccountSummary Sum_ACCT ON (GLD.LinkToCompanyID = Sum_ACCT.LinkForCompanyID AND GLD.LinkToSummaryAccountID = Sum_ACCT.LinkForSummaryAccountID) LEFT JOIN vd_Account From_ACCT ON (GLD.LinkToCompanyID = From_ACCT.LinkForCompanyID AND Sum_ACCT.LinkForSummaryAccountID = From_ACCT.LinkForAccountID) LEFT JOIN vd_AccountCashSource From_ACCT_Cash ON (GLD.LinkToCompanyID = From_ACCT_Cash.LinkForCompanyID AND GLD.LinkToSummaryAccountID = From_ACCT_Cash.LinkForCashSourceAccountID) LEFT JOIN vd_Account To_ACCT ON (GLD.LinkToCompanyID = To_ACCT.LinkForCompanyID AND GLD.LinkToAccountID = To_ACCT.LinkForAccountID) LEFT JOIN vd_AccountCashSource To_ACCT_Cash ON (GLD.LinkToCompanyID = To_ACCT_Cash.LinkForCompanyID AND GLD.LinkToAccountID = To_ACCT_Cash.LinkForCashSourceAccountID) LEFT JOIN vd_Class Classes ON (GLD.LinkToCompanyID = Classes.LinkForCompanyID AND Classes.LinkForClassID = GLD.LinkToClassID) LEFT JOIN vd_Customer Customer ON (GLD.LinkToCompanyID = Customer.LinkForCompanyID AND Customer.LinkForCustomerID = GLD.LinkToCustomerID) LEFT JOIN vd_Vendor Vendor ON (GLD.LinkToCompanyID = Vendor.LinkForCompanyID AND Vendor.LinkForVendorID = GLD.LinkToVendorID) LEFT JOIN vd_Employee Employee ON (GLD.LinkToCompanyID = Employee.LinkForCompanyID AND Employee.LinkForEmployeeID = GLD.LinkToEmployeeID) LEFT JOIN vd_OtherName OtherName ON (GLD.LinkToCompanyID = OtherName.LinkForCompanyID AND OtherName.LinkForOtherNameID = GLD.LinkToOtherNameID) LEFT JOIN vd_Job Job ON (GLD.LinkToCompanyID = Job.LinkForCompanyID AND Job.LinkForJobID = GLD.LinkToJobID) LEFT JOIN vd_Item Item ON (GLD.LinkToCompanyID = Item.LinkForCompanyID AND Item.LinkForItemID = GLD.LinkToItemID) LEFT JOIN vd_SourceName SourceName ON (GLD.LinkToCompanyID = SourceName.LinkForCompanyID AND SourceName.LinkForSourceNameID = GLD.LinkToSourceNameID) LEFT JOIN vd_PaymentMethod PaymentMethod ON (GLD.LinkToCompanyID = PaymentMethod.LinkForCompanyID AND PaymentMethod.LinkForPaymentMethodID = GLD.LinkToPaymentMethodID) LEFT JOIN vd_PayrollItem PayrollItem ON (GLD.LinkToCompanyID = PayrollItem.LinkForCompanyID AND PayrollItem.LinkForPayrollItemID = GLD.LinkToPayrollItemID) LEFT JOIN vd_CalYrTxnDate TxnDate ON TxnDate.LinkForTxnDateID = GLD.LinkToTxnDateID GO 2. Define a table (GLDetailExtract) structually matching the view output: IF OBJECT_ID('dbo.GLDetailExtract') IS NOT NULL DROP TABLE dbo.GLDetailExtract GO CREATE TABLE dbo.GLDetailExtract ( [Company Name] varchar(59), [GLTxn Document Master Linking Number] int, [GLTxn Document Reference Number] varchar(40), [GLTxn Document Current Posting State] varchar(11), [GLTxn Document Entry Type] varchar(14), [GLTxn Document Master Linking Txn Type] varchar(25), [GLTxn Document Type] varchar(24), [GLTxn Line Amount] decimal(15,2), [GLTxn Line Amount Type] varchar(22), [GLTxn Line Billable Status] varchar(255), [GLTxn Line Budget Amount] numeric(15,2), [GLTxn Line Credit Amount] numeric(32,5), [GLTxn Line Debit Amount] numeric(32,5), [GLTxn Line Forecast Amount] decimal(15,2), [GLTxn Line Item Description] varchar(4096), [GLTxn Line Open Amount] numeric(32,9), [GLTxn Line Paid Amount] numeric(32,10), [GLTxn Line Paid/Open Status] varchar(5), [GLTxn Line Quantity] numeric(32,5), [GLTxn Line Rate] numeric(32,5), [GLTxn Line Rate Percent] numeric(26,9), [From Account Type] varchar(23), [From Account Posting Type] varchar(12), [From Account Special Type] varchar(30), [From Account NAME Grouping Mechanism - 1] varchar(31), [From Account NAME Grouping Mechanism - 2] varchar(31), [From Account NAME Grouping Mechanism - 3] varchar(31), [From Account NAME Grouping Mechanism - 4] varchar(31), [From Account NAME Grouping Mechanism - 5] varchar(31), [From Account BS Grouping - A] varchar(50), [From Account BS Grouping - B] varchar(50), [From Account BS Grouping - C] varchar(50), [From Account BS Grouping - D] varchar(50), [From Cash Source Account Active] varchar(12), [From Cash Source Account Cash Flow Classification] varchar(14), [From Cash Source Account Category] varchar(15), [To Account Type] varchar(23), [To Account Posting Type] varchar(12), [To Account Special Type] varchar(30), [To Account NAME Grouping Mechanism - 1] varchar(31), [To Account NAME Grouping Mechanism - 2] varchar(31), [To Account NAME Grouping Mechanism - 3] varchar(31), [To Account NAME Grouping Mechanism - 4] varchar(31), [To Account NAME Grouping Mechanism - 5] varchar(31), [To Account BS Grouping - A] varchar(50), [To Account BS Grouping - B] varchar(50), [To Account BS Grouping - C] varchar(50), [To Account BS Grouping - D] varchar(50), [To Cash Source Account Active] varchar(12), [To Cash Source Account Cash Flow Classification] varchar(14), [To Cash Source Account Category] varchar(15), [Class Grouping Mechanism - 1] varchar(31), [Class Grouping Mechanism - 2] varchar(31), [Class Grouping Mechanism - 3] varchar(31), [Class Grouping Mechanism - 4] varchar(31), [Class Grouping Mechanism - 5] varchar(31), [Customer First Name] varchar(25), [Customer Last Name] varchar(25), [Customer Name] varchar(41), [Customer Billing Address State] varchar(21), [Vendor First Name] varchar(25), [Vendor Last Name] varchar(25), [Vendor Name] varchar(41), [Vendor Address State] varchar(21), [Employee First Name] varchar(25), [Employee Last Name] varchar(25), [Employee Name] varchar(41), [Employee Address State] varchar(21), [OtherName First Name] varchar(25), [OtherName Last Name] varchar(25), [OtherName Name] varchar(41), [OtherName Address State] varchar(21), [Job Grouping Mechanism - 1] varchar(41), [Job Grouping Mechanism - 2] varchar(41), [Job Grouping Mechanism - 3] varchar(41), [Job Grouping Mechanism - 4] varchar(41), [Job Grouping Mechanism - 5] varchar(41), [Item Grouping Mechanism - 1] varchar(31), [Item Grouping Mechanism - 2] varchar(31), [Item Grouping Mechanism - 3] varchar(31), [Item Grouping Mechanism - 4] varchar(31), [Item Grouping Mechanism - 5] varchar(31), [Source Name First Name] varchar(25), [Source Name Last Name] varchar(25), [Source Name Name] varchar(41), [Payment Method Active] varchar(19), [Payment Method Name] varchar(41), [Payment Method Type] varchar(19), [Payroll Item Active] varchar(17), [Payroll Item Category] varchar(17), [Payroll Item Expense Account Name] varchar(31), [Payroll Item Expense Account Type] varchar(21), [Payroll Item Liability Account Name] varchar(31), [Payroll Item Liability Account Type] varchar(22), [Payroll Item Name] varchar(31), [Payroll Item Type] varchar(20), [CalYr Txn Date] date, [CalYr Txn Date Day is Weekend Day] smallint, [CalYr Txn Date Day Number] int, [CalYr Txn Date Day Number in Quarter] int, [CalYr Txn Date Day Number in Week] int, [CalYr Txn Date Day Number in Year] int, [CalYr Txn Date Day of Week] varchar(10), [CalYr Txn Date IS Last Day of Month] smallint, [CalYr Txn Date IS Last Day of Quarter] smallint, [CalYr Txn Date IS Last Day of Year] smallint, [CalYr Txn Date Month Name] varchar(10), [CalYr Txn Date Month Number] int, [CalYr Txn Date Month Begin Date] date, [CalYr Txn Date Month End Date] date, [CalYr Txn Date Year Number] int, [CalYr Txn Date Quarter Begin Date] date, [CalYr Txn Date Quarter End Date] date, [CalYr Txn Date Quarter Number] int ) GO 3. Insert the results from GetGlDetail into the GLDetailExtract table. INSERT INTO dbo.GLDetailExtract SELECT * FROM dbo.GetGLDetail 4. Function selects from GetGLDetail and returns a BalanceSheet: USE QQ_Import; IF OBJECT_ID('dashboard.BalanceSheetStandard') IS NOT NULL DROP FUNCTION dashboard.BalanceSheetStandard GO CREATE FUNCTION [dashboard].[BalanceSheetStandard] ( @company AS varchar(59), @ASOfDate DATE ) RETURNS TABLE AS RETURN ( SELECT GLE.[Company Name], GLE.[To Account BS Grouping - A], GLE.[To Account BS Grouping - B], GLE.[To Account BS Grouping - C], GLE.[To Account BS Grouping - D], GLE.[To Account Type], GLE.[To Account NAME Grouping Mechanism - 1], GLE.[To Account NAME Grouping Mechanism - 2], SUM(GLE.[GLTxn Line Amount]) Amount FROM GLDetailExtract GLE WHERE GLE.[Company Name] = @company AND GLE.[To Account NAME Grouping Mechanism - 1] IS NOT NULL AND GLE.[CalYr Txn Date] <= @ASOfDate AND GLE.[To Cash Source Account Category] = 'Balance Sheet' GROUP BY GLE.[Company Name], GLE.[To Account Type], GLE.[To Account NAME Grouping Mechanism - 1], GLE.[To Account NAME Grouping Mechanism - 2], GLE.[To Account BS Grouping - A], GLE.[To Account BS Grouping - B], GLE.[To Account BS Grouping - C], GLE.[To Account BS Grouping - D] HAVING SUM(GLE.[GLTxn Line Amount]) <> 0 ) GO
USE QQ_Import
IF OBJECT_ID('dbo.GetGLDetail') IS NOT NULL
DROP VIEW dbo.GetGLDetail
GO
Create View DBO.GetGLDetail AS
SELECT
CO.[Company Name],
GLD.[GLTxn Document Master Linking Number],
GLD.[GLTxn Document Reference Number],
GLD.[GLTxn Document Current Posting State],
GLD.[GLTxn Document Entry Type],
GLD.[GLTxn Document Master Linking Txn Type],
GLD.[GLTxn Document Type],
GLD.[GLTxn Line Amount],
GLD.[GLTxn Line Amount Type],
GLD.[GLTxn Line Billable Status],
GLD.[GLTxn Line Budget Amount],
GLD.[GLTxn Line Credit Amount],
GLD.[GLTxn Line Debit Amount],
GLD.[GLTxn Line Forecast Amount],
GLD.[GLTxn Line Item Description],
GLD.[GLTxn Line Open Amount],
GLD.[GLTxn Line Paid Amount],
GLD.[GLTxn Line Paid/Open Status],
GLD.[GLTxn Line Quantity],
GLD.[GLTxn Line Rate],
GLD.[GLTxn Line Rate Percent],
From_ACCT.[Account Type] [From Account Type],
From_ACCT.[Account Posting Type] [From Account Posting Type],
From_ACCT.[Account Special Type] [From Account Special Type],
From_ACCT.[Account NAME Grouping Mechanism - 1] [From Account NAME Grouping Mechanism - 1],
From_ACCT.[Account NAME Grouping Mechanism - 2] [From Account NAME Grouping Mechanism - 2],
From_ACCT.[Account NAME Grouping Mechanism - 3] [From Account NAME Grouping Mechanism - 3],
From_ACCT.[Account NAME Grouping Mechanism - 4] [From Account NAME Grouping Mechanism - 4],
From_ACCT.[Account NAME Grouping Mechanism - 5] [From Account NAME Grouping Mechanism - 5],
From_ACCT.[Account BS Grouping - A] [From Account BS Grouping - A],
From_ACCT.[Account BS Grouping - B] [From Account BS Grouping - B],
From_ACCT.[Account BS Grouping - C] [From Account BS Grouping - C],
From_ACCT.[Account BS Grouping - D] [From Account BS Grouping - D],
From_Acct_Cash.[Cash Source Account Active] [From Cash Source Account Active],
From_Acct_Cash.[Cash Source Account Cash Flow Classification] [From Cash Source Account Cash Flow Classification],
From_Acct_Cash.[Cash Source Account Category] [From Cash Source Account Category],
To_ACCT.[Account Type] [To Account Type],
To_ACCT.[Account Posting Type] [To Account Posting Type],
To_ACCT.[Account Special Type] [To Account Special Type],
To_ACCT.[Account NAME Grouping Mechanism - 1] [To Account NAME Grouping Mechanism - 1],
To_ACCT.[Account NAME Grouping Mechanism - 2] [To Account NAME Grouping Mechanism - 2],
To_ACCT.[Account NAME Grouping Mechanism - 3] [To Account NAME Grouping Mechanism - 3],
To_ACCT.[Account NAME Grouping Mechanism - 4] [To Account NAME Grouping Mechanism - 4],
To_ACCT.[Account NAME Grouping Mechanism - 5] [To Account NAME Grouping Mechanism - 5],
To_ACCT.[Account BS Grouping - A] [To Account BS Grouping - A],
To_ACCT.[Account BS Grouping - B] [To Account BS Grouping - B],
To_ACCT.[Account BS Grouping - C] [To Account BS Grouping - C],
To_ACCT.[Account BS Grouping - D] [To Account BS Grouping - D],
To_ACCT_Cash.[Cash Source Account Active] [To Cash Source Account Active],
To_ACCT_Cash.[Cash Source Account Cash Flow Classification] [To Cash Source Account Cash Flow Classification],
To_ACCT_Cash.[Cash Source Account Category] [To Cash Source Account Category],
Classes.[Class Grouping Mechanism - 1],
Classes.[Class Grouping Mechanism - 2],
Classes.[Class Grouping Mechanism - 3],
Classes.[Class Grouping Mechanism - 4],
Classes.[Class Grouping Mechanism - 5],
Customer.[Customer First Name],
Customer.[Customer Last Name],
Customer.[Customer Name],
Customer.[Customer Billing Address State],
Vendor.[Vendor First Name],
Vendor.[Vendor Last Name],
Vendor.[Vendor Name],
Vendor.[Vendor Address State],
Employee.[Employee First Name],
Employee.[Employee Last Name],
Employee.[Employee Name],
Employee.[Employee Address State],
OtherName.[OtherName First Name],
OtherName.[OtherName Last Name],
OtherName.[OtherName Name],
OtherName.[OtherName Address State],
Job.[Job Grouping Mechanism - 1],
Job.[Job Grouping Mechanism - 2],
Job.[Job Grouping Mechanism - 3],
Job.[Job Grouping Mechanism - 4],
Job.[Job Grouping Mechanism - 5],
Item.[Item Grouping Mechanism - 1],
Item.[Item Grouping Mechanism - 2],
Item.[Item Grouping Mechanism - 3],
Item.[Item Grouping Mechanism - 4],
Item.[Item Grouping Mechanism - 5],
SourceName.[Source Name First Name],
SourceName.[Source Name Last Name],
SourceName.[Source Name Name],
PaymentMethod.[Payment Method Active],
PaymentMethod.[Payment Method Name],
PaymentMethod.[Payment Method Type],
PayrollItem.[Payroll Item Active],
PayrollItem.[Payroll Item Category],
PayrollItem.[Payroll Item Expense Account Name],
PayrollItem.[Payroll Item Expense Account Type],
PayrollItem.[Payroll Item Liability Account Name],
PayrollItem.[Payroll Item Liability Account Type],
PayrollItem.[Payroll Item Name],
PayrollItem.[Payroll Item Type],
TxnDate.[CalYr Txn Date],
TxnDate.[CalYr Txn Date Day is Weekend Day],
TxnDate.[CalYr Txn Date Day Number],
TxnDate.[CalYr Txn Date Day Number in Quarter],
TxnDate.[CalYr Txn Date Day Number in Week],
TxnDate.[CalYr Txn Date Day Number in Year],
TxnDate.[CalYr Txn Date Day of Week],
TxnDate.[CalYr Txn Date IS Last Day of Month],
TxnDate.[CalYr Txn Date IS Last Day of Quarter],
TxnDate.[CalYr Txn Date IS Last Day of Year],
TxnDate.[CalYr Txn Date Month Name],
TxnDate.[CalYr Txn Date Month Number],
TxnDate.[CalYr Txn Date Month Begin Date],
TxnDate.[CalYr Txn Date Month End Date],
TxnDate.[CalYr Txn Date Year Number],
TxnDate.[CalYr Txn Date Quarter Begin Date],
TxnDate.[CalYr Txn Date Quarter End Date],
TxnDate.[CalYr Txn Date Quarter Number]
FROM vf_GLDETAIL GLD
--Company
LEFT JOIN vd_Company CO
ON CO.LinkForCompanyID = GLD.LinkToCompanyID
--Account Links
LEFT JOIN vd_AccountSummary Sum_ACCT
ON (GLD.LinkToCompanyID = Sum_ACCT.LinkForCompanyID AND
GLD.LinkToSummaryAccountID = Sum_ACCT.LinkForSummaryAccountID)
LEFT JOIN vd_Account From_ACCT
ON (GLD.LinkToCompanyID = From_ACCT.LinkForCompanyID AND
Sum_ACCT.LinkForSummaryAccountID = From_ACCT.LinkForAccountID)
LEFT JOIN vd_AccountCashSource From_ACCT_Cash
ON (GLD.LinkToCompanyID = From_ACCT_Cash.LinkForCompanyID AND
GLD.LinkToSummaryAccountID = From_ACCT_Cash.LinkForCashSourceAccountID)
LEFT JOIN vd_Account To_ACCT
ON (GLD.LinkToCompanyID = To_ACCT.LinkForCompanyID AND
GLD.LinkToAccountID = To_ACCT.LinkForAccountID)
LEFT JOIN vd_AccountCashSource To_ACCT_Cash
ON (GLD.LinkToCompanyID = To_ACCT_Cash.LinkForCompanyID AND
GLD.LinkToAccountID = To_ACCT_Cash.LinkForCashSourceAccountID)
LEFT JOIN vd_Class Classes
ON (GLD.LinkToCompanyID = Classes.LinkForCompanyID AND
Classes.LinkForClassID = GLD.LinkToClassID)
LEFT JOIN vd_Customer Customer
ON (GLD.LinkToCompanyID = Customer.LinkForCompanyID AND
Customer.LinkForCustomerID = GLD.LinkToCustomerID)
LEFT JOIN vd_Vendor Vendor
ON (GLD.LinkToCompanyID = Vendor.LinkForCompanyID AND
Vendor.LinkForVendorID = GLD.LinkToVendorID)
LEFT JOIN vd_Employee Employee
ON (GLD.LinkToCompanyID = Employee.LinkForCompanyID AND
Employee.LinkForEmployeeID = GLD.LinkToEmployeeID)
LEFT JOIN vd_OtherName OtherName
ON (GLD.LinkToCompanyID = OtherName.LinkForCompanyID AND
OtherName.LinkForOtherNameID = GLD.LinkToOtherNameID)
LEFT JOIN vd_Job Job
ON (GLD.LinkToCompanyID = Job.LinkForCompanyID AND
Job.LinkForJobID = GLD.LinkToJobID)
LEFT JOIN vd_Item Item
ON (GLD.LinkToCompanyID = Item.LinkForCompanyID AND
Item.LinkForItemID = GLD.LinkToItemID)
LEFT JOIN vd_SourceName SourceName
ON (GLD.LinkToCompanyID = SourceName.LinkForCompanyID AND
SourceName.LinkForSourceNameID = GLD.LinkToSourceNameID)
LEFT JOIN vd_PaymentMethod PaymentMethod
ON (GLD.LinkToCompanyID = PaymentMethod.LinkForCompanyID AND
PaymentMethod.LinkForPaymentMethodID = GLD.LinkToPaymentMethodID)
LEFT JOIN vd_PayrollItem PayrollItem
ON (GLD.LinkToCompanyID = PayrollItem.LinkForCompanyID AND
PayrollItem.LinkForPayrollItemID = GLD.LinkToPayrollItemID)
LEFT JOIN vd_CalYrTxnDate TxnDate
ON TxnDate.LinkForTxnDateID = GLD.LinkToTxnDateID
IF OBJECT_ID('dbo.GLDetailExtract') IS NOT NULL
DROP TABLE dbo.GLDetailExtract
CREATE TABLE dbo.GLDetailExtract (
[Company Name] varchar(59),
[GLTxn Document Master Linking Number] int,
[GLTxn Document Reference Number] varchar(40),
[GLTxn Document Current Posting State] varchar(11),
[GLTxn Document Entry Type] varchar(14),
[GLTxn Document Master Linking Txn Type] varchar(25),
[GLTxn Document Type] varchar(24),
[GLTxn Line Amount] decimal(15,2),
[GLTxn Line Amount Type] varchar(22),
[GLTxn Line Billable Status] varchar(255),
[GLTxn Line Budget Amount] numeric(15,2),
[GLTxn Line Credit Amount] numeric(32,5),
[GLTxn Line Debit Amount] numeric(32,5),
[GLTxn Line Forecast Amount] decimal(15,2),
[GLTxn Line Item Description] varchar(4096),
[GLTxn Line Open Amount] numeric(32,9),
[GLTxn Line Paid Amount] numeric(32,10),
[GLTxn Line Paid/Open Status] varchar(5),
[GLTxn Line Quantity] numeric(32,5),
[GLTxn Line Rate] numeric(32,5),
[GLTxn Line Rate Percent] numeric(26,9),
[From Account Type] varchar(23),
[From Account Posting Type] varchar(12),
[From Account Special Type] varchar(30),
[From Account NAME Grouping Mechanism - 1] varchar(31),
[From Account NAME Grouping Mechanism - 2] varchar(31),
[From Account NAME Grouping Mechanism - 3] varchar(31),
[From Account NAME Grouping Mechanism - 4] varchar(31),
[From Account NAME Grouping Mechanism - 5] varchar(31),
[From Account BS Grouping - A] varchar(50),
[From Account BS Grouping - B] varchar(50),
[From Account BS Grouping - C] varchar(50),
[From Account BS Grouping - D] varchar(50),
[From Cash Source Account Active] varchar(12),
[From Cash Source Account Cash Flow Classification] varchar(14),
[From Cash Source Account Category] varchar(15),
[To Account Type] varchar(23),
[To Account Posting Type] varchar(12),
[To Account Special Type] varchar(30),
[To Account NAME Grouping Mechanism - 1] varchar(31),
[To Account NAME Grouping Mechanism - 2] varchar(31),
[To Account NAME Grouping Mechanism - 3] varchar(31),
[To Account NAME Grouping Mechanism - 4] varchar(31),
[To Account NAME Grouping Mechanism - 5] varchar(31),
[To Account BS Grouping - A] varchar(50),
[To Account BS Grouping - B] varchar(50),
[To Account BS Grouping - C] varchar(50),
[To Account BS Grouping - D] varchar(50),
[To Cash Source Account Active] varchar(12),
[To Cash Source Account Cash Flow Classification] varchar(14),
[To Cash Source Account Category] varchar(15),
[Class Grouping Mechanism - 1] varchar(31),
[Class Grouping Mechanism - 2] varchar(31),
[Class Grouping Mechanism - 3] varchar(31),
[Class Grouping Mechanism - 4] varchar(31),
[Class Grouping Mechanism - 5] varchar(31),
[Customer First Name] varchar(25),
[Customer Last Name] varchar(25),
[Customer Name] varchar(41),
[Customer Billing Address State] varchar(21),
[Vendor First Name] varchar(25),
[Vendor Last Name] varchar(25),
[Vendor Name] varchar(41),
[Vendor Address State] varchar(21),
[Employee First Name] varchar(25),
[Employee Last Name] varchar(25),
[Employee Name] varchar(41),
[Employee Address State] varchar(21),
[OtherName First Name] varchar(25),
[OtherName Last Name] varchar(25),
[OtherName Name] varchar(41),
[OtherName Address State] varchar(21),
[Job Grouping Mechanism - 1] varchar(41),
[Job Grouping Mechanism - 2] varchar(41),
[Job Grouping Mechanism - 3] varchar(41),
[Job Grouping Mechanism - 4] varchar(41),
[Job Grouping Mechanism - 5] varchar(41),
[Item Grouping Mechanism - 1] varchar(31),
[Item Grouping Mechanism - 2] varchar(31),
[Item Grouping Mechanism - 3] varchar(31),
[Item Grouping Mechanism - 4] varchar(31),
[Item Grouping Mechanism - 5] varchar(31),
[Source Name First Name] varchar(25),
[Source Name Last Name] varchar(25),
[Source Name Name] varchar(41),
[Payment Method Active] varchar(19),
[Payment Method Name] varchar(41),
[Payment Method Type] varchar(19),
[Payroll Item Active] varchar(17),
[Payroll Item Category] varchar(17),
[Payroll Item Expense Account Name] varchar(31),
[Payroll Item Expense Account Type] varchar(21),
[Payroll Item Liability Account Name] varchar(31),
[Payroll Item Liability Account Type] varchar(22),
[Payroll Item Name] varchar(31),
[Payroll Item Type] varchar(20),
[CalYr Txn Date] date,
[CalYr Txn Date Day is Weekend Day] smallint,
[CalYr Txn Date Day Number] int,
[CalYr Txn Date Day Number in Quarter] int,
[CalYr Txn Date Day Number in Week] int,
[CalYr Txn Date Day Number in Year] int,
[CalYr Txn Date Day of Week] varchar(10),
[CalYr Txn Date IS Last Day of Month] smallint,
[CalYr Txn Date IS Last Day of Quarter] smallint,
[CalYr Txn Date IS Last Day of Year] smallint,
[CalYr Txn Date Month Name] varchar(10),
[CalYr Txn Date Month Number] int,
[CalYr Txn Date Month Begin Date] date,
[CalYr Txn Date Month End Date] date,
[CalYr Txn Date Year Number] int,
[CalYr Txn Date Quarter Begin Date] date,
[CalYr Txn Date Quarter End Date] date,
[CalYr Txn Date Quarter Number] int
)
INSERT INTO dbo.GLDetailExtract
SELECT * FROM dbo.GetGLDetail
4. Function selects from GetGLDetail and returns a BalanceSheet:
USE QQ_Import;
IF OBJECT_ID('dashboard.BalanceSheetStandard') IS NOT NULL
DROP FUNCTION dashboard.BalanceSheetStandard
CREATE FUNCTION [dashboard].[BalanceSheetStandard]
(
@company AS varchar(59),
@ASOfDate DATE
RETURNS TABLE
AS
RETURN
GLE.[Company Name],
GLE.[To Account BS Grouping - A],
GLE.[To Account BS Grouping - B],
GLE.[To Account BS Grouping - C],
GLE.[To Account BS Grouping - D],
GLE.[To Account Type],
GLE.[To Account NAME Grouping Mechanism - 1],
GLE.[To Account NAME Grouping Mechanism - 2],
SUM(GLE.[GLTxn Line Amount]) Amount
FROM GLDetailExtract GLE
WHERE
GLE.[Company Name] = @company
AND
GLE.[To Account NAME Grouping Mechanism - 1] IS NOT NULL
GLE.[CalYr Txn Date] <= @ASOfDate
GLE.[To Cash Source Account Category] = 'Balance Sheet'
GROUP BY GLE.[Company Name],
GLE.[To Account BS Grouping - D]
HAVING SUM(GLE.[GLTxn Line Amount]) <> 0
We have many moving parts in this request, so I am going to make a list of items that pertain to what I see in both you request and your SQL.
Choose a location
Forum :
Your session has expired. You are being logged out.