Bad Debt Model Quarterly

 One of my users requested this report to be able to get the specific list of accounts in one report instead of going to Summary Inquiry one at a time.


Solution:

1. Create an SQL View in your SQL Server by running the SQL script below


USE [CO100 - @yourcompanyID]

GO

/****** Object:  View [dbo].[view_Open_Yr_GL_TB_Month_End]    Script Date: 10/11/2023 2:15:51 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create view [dbo].[view_Open_Yr_GL_TB_Month_End]

as

 -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

-- Created July 9, 2014 by Victoria Yudin

-- Flexible Solutions, Inc.

-- For other code, please visit http://victoriayudin.com

-- Only returns the first open year in GP

-- Month names in columns use calendar fiscal year

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

 select

g.YEAR1 [Year],

a.ACTNUMST Account,

g.ACTDESCR [Description],

c.ACCATDSC Category,

sum(case g.PERIODID when 0

    then g.PERDBLNC else 0 end) Beginning_Balance,

sum(case when g.PERIODID <= 1

    then g.DEBITAMT-g.CRDTAMNT else 0 end) Jan_Balance,

sum(case when g.PERIODID <= 2

    then g.DEBITAMT-g.CRDTAMNT else 0 end) Feb_Balance,

sum(case when g.PERIODID <= 3

    then g.DEBITAMT-g.CRDTAMNT else 0 end) Mar_Balance,

sum(case when g.PERIODID <= 4

    then g.DEBITAMT-g.CRDTAMNT else 0 end) Apr_Balance,

sum(case when g.PERIODID <= 5

    then g.DEBITAMT-g.CRDTAMNT else 0 end) May_Balance,

sum(case when g.PERIODID <= 6

    then g.DEBITAMT-g.CRDTAMNT else 0 end) Jun_Balance,

sum(case when g.PERIODID <= 7

    then g.DEBITAMT-g.CRDTAMNT else 0 end) Jul_Balance,

sum(case when g.PERIODID <= 8

    then g.DEBITAMT-g.CRDTAMNT else 0 end) Aug_Balance,

sum(case when g.PERIODID <= 9

    then g.DEBITAMT-g.CRDTAMNT else 0 end) Sep_Balance,

sum(case when g.PERIODID <= 10

    then g.DEBITAMT-g.CRDTAMNT else 0 end) Oct_Balance,

sum(case when g.PERIODID <= 11

    then g.DEBITAMT-g.CRDTAMNT else 0 end) Nov_Balance,

sum(case when g.PERIODID <= 12

    then g.DEBITAMT-g.CRDTAMNT else 0 end) Dec_Balance

 from GL11110 g --GL summary data

 inner join GL00102 c --categories

on g.ACCATNUM = c.ACCATNUM

 inner join GL00105 a --for account number

on g.ACTINDX = a.ACTINDX

 where g.ACCTTYPE = 1

and g.YEAR1 = (select min(YEAR1) from GL11110)

 group by g.YEAR1, g.ACTDESCR, a.ACTNUMST, c.ACCATDSC

 GO


2. Create your Smartlist Builder 

Add your SQL Table (Views)




Add your SQL Script


Here is the SQL Script for the accounts filter

select * from CO100..GL00105 where ACTNUMST IN 

('100-000-14800', 

'100-000-13000', 

'200-000-12502',

'200-000-12501',

'100-000-11999',

'100-645-41000',

'100-000-12500',

'100-000-12504',

'100-645-41300')

union all

select * from CO200..GL00105 where ACTNUMST IN 


('100-000-14800', 

'100-000-13000', 

'200-000-12502',

'200-000-12501',

'100-000-11999',

'100-645-41000',

'100-645-41300')


Edit your Display Name and mark defaults




3. Grant Users access to SmartList Object



Comments

Post a Comment

Popular posts from this blog

GP Guru Level Up: Episode 3 - How to bring your out of the box Aged Trial Balance into Smartlist

Image
 We had a great session at our Dynamics User Group Virtual with our guest speaker Marion Palaming discussing techniques in using SQL Views to create  PM and RM Aged Trial Balance into Smartlist. Here are the SQL Views used in the presentation: Disclaimer: did some minor updates from Victoria Yudin's SQL Views RECEIVABLES MODULE USE ['yourDB] GO /****** Object:  View [dbo].[RM_SMAging_CurrentDate]    Script Date: 4/21/2023 10:54:48 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[RM_SMAging_CurrentDate] AS SELECT CM.CUSTNMBR AS Customer_ID, CM.CUSTNAME AS Customer_Name, CM.PYMTRMID AS Customer_Terms, CM.CUSTCLAS AS Customer_Class, CM.PRCLEVEL AS Price_Level, RM.CURNCYID,               CASE RM.RMDTYPAL WHEN 1 THEN 'Sale / Invoice' WHEN 3 THEN 'Debit Memo' WHEN 4 THEN 'Finance Charge' WHEN 5 THEN 'Service Repair' WHEN 6 THEN 'Warranty' WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN &
Read more

Extract Journal Entries for Auditors using SQL Query

Twice a year, I receive a request to extract Journal Entries for Auditors.  It could have been done using the Smartlist in Dynamics GP but my End Users didn't want to use it because it is time consuming on their end. We extract into two sets:  January to August and then October to December once they are done with closing their books.  Until today, I'm still using the SQL scripts below and extract the data into Excel. OPEN YEAR select A.OPENYEAR 'Year',            TRXDATE 'Trx_Date', ORPSTDDT 'Originating_Posted_Date', A.JRNENTRY 'Journal_Entry', A.RCTRXSEQ 'Recurring_Trx_Sequence', ORTRXSRC 'Originating_TRX_Source',      A.SOURCDOC 'Source Document',            A.REFRENCE 'Reference',              ORMSTRID 'Originating_Master_ID',        ORMSTRNM 'Originating_Master_Name',             ORDOCNUM 'Originating_Doc_Number', DEBITAMT 'Debit_Amount', CRDTAMNT 'Cr
Read more

GP Guru Level Up: Episode 2 - How to use the Replicator to create a macro with John Arnold

Image
 In continuation of my previous blog in  Episode 1  about using a macro to process NSF, I learned that there's an easiest way to create a macro using the  Replicator and it's   free for everyone!  This is developed by John Arnold , Senior Software Engineer from US Digital. Preparation steps: Go to John's website:  Rnoldz.com/ Click on Replicator >> Download the Replicator Excel File Right click on the file >>  Mark the checkbox to Unblock Security Open the Replicator File Ready your NSF macro Ready your data in Excel Here is the NSF macro template for future use: # DEXVERSION=18.00.0021.000 2 2 CheckActiveWin dictionary 'default'  form 'RM_Misc_Transaction' window 'RM_Misc_Transaction'    TypeTo field 'Customer Number' ,  '09728003SA'   MoveTo field 'Document Type' item 1  # 'Sales / Invoices'    ClickHit field 'Document Type' item 7  # 'Payments'    MoveTo field '(L) Document Number
Read more