Hi all

I had posted an article sometime back titled Macro to provide access to Transaction Dimension Codes to Users where I had provided a SQL script to create a macro code to provide access to all users to all transaction dimension codes setup in the company.

I had received a few comments from user community if the same task which is done by macro can be done using a SQL script. So I decided to write up a simple script to provide access to all users to all transaction dimensions and dimension codes setup in a company.

DECLARE…

Guys

There is a smartlist object for AP transactions, but this smartlist does not show the analytical accounting information in it.

So in this post, I have decided to post a SQL view to generate a view to show AP transaction information along with the analytical accounting information.

IF EXISTS ( SELECT  *
            FROM    sys.views
            WHERE   object_id = OBJECT_ID(N'[dbo].[vw_AAG20000]') )
    DROP VIEW  [dbo].[vw_AAG20000]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vw_AAG20000]
AS  SELECT  [aaSubLedgerHdrID],
            [SERIES],
            [DOCTYPE] = CASE WHEN [DOCTYPE] = 0 THEN 1
                             WHEN [DOCTYPE] = 1 THEN 6
                             ELSE 5
                        END,
            [DOCNUMBR],
            [Master_ID],
            [aaHdrErrors],
            [DEX_ROW_ID]
    FROM    [dbo].[AAG20000]

GO

IF EXISTS ( SELECT  *
            FROM    sys.views
            WHERE   object_id = OBJECT_ID(N'[dbo].[vw_APTransactions]') )
    DROP VIEW  [dbo].[vw_APTransactions]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vw_APTransactions]
AS  SELECT  *,
            'Open' AS STATUS
    FROM    dbo.PM20000
    UNION ALL
    SELECT  *,
            'History' AS STATUS
    FROM    dbo.PM30200

GO

IF EXISTS ( SELECT  *
            FROM    sys.views
            WHERE   object_id = OBJECT_ID(N'[dbo].[vw_APAAInformation]') )
    DROP VIEW  [dbo].[vw_APAAInformation]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[vw_APAAInformation]
AS  SELECT  A.VENDORID,
            I.VENDNAME,
            I.VNDCLSID AS VENDORCLASS,
            J.VNDCLDSC AS VENDORCLASSDESC,
            A.VCHRNMBR AS VOUCHERNO,
            A.DOCNUMBR AS INVOICENO,
            A.DOCDATE AS DOCUMENTDATE,
            A.PSTGDATE AS POSTINGDATE,
            G.ACTNUMST AS GLACCOUNTNO,
            H.ACTDESCR AS…

New from veeyeskay is a SQL Script to Reconcile GL vs. AA Balances.