continuous assurance best practices: laying the roadmap to success
TRANSCRIPT
![Page 1: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/1.jpg)
Continuous Assurance Best
Practices: Laying the
Roadmap to Success
![Page 2: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/2.jpg)
MY BACKGROUND
BA’s in History and Religion Studied for an Master’s Degree in Church History Got a job twisting balloons while in grad school. Worked for Babies R Us as an Assistant Manager. Worked Customer Service while working on an
MBA. Took Intro to Pascal in college to fulfill a
distribution requirement!
![Page 3: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/3.jpg)
WHY IS THAT RELEVANT?
Because I am not a techie.
![Page 4: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/4.jpg)
HOW I ENDED UP HERE?Prior to learning ACL:
I was answering phonesAnd twisting balloons
If you can take some clown off the street and teach them ACL, then you can teach anybody ACL.
![Page 5: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/5.jpg)
RECOGNIZED ACL EXPERT 2007 – ACL’s Seven Habits of Successful Auditor’s
Campaign Founder and first president of TexasACL User Group Second person ever recognized as “ACL Super User” 2008 – Spoke at ACL’s Annual Convention 2008 – Participated on ACL Round Table 2009 – Keynote speaker at ACL Annual Conference 2009 – Asked by David Coderre to endorse book 2011 – Spoke at ACL Business Assurance Seminar 2012 – Presented Webinar for ACL
![Page 6: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/6.jpg)
CONTINUOUS ASSURANCE PROGRAM
6 Major Projects Projects range from 40-100+ scripts New projects are built upon a common
“skeleton”. New projects re-use same scripts! Projects may perform over 100 different tests Continuous Assurance results are sent to one of
several standardized report formats Some projects produce over 10K worth of ACL
Logging per day!
![Page 7: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/7.jpg)
OUTLINE Commands Comments End of Scripts Field Names In Scripts Naming Conventions Opening Sections Project Names Reports Script Library Script Names Start of Scripts Table Names Variable names
Naming Conventions Project Names Script Names Table Names Variable Names Field Names
Comments Start of Script In Scripts End of Scripts
Opening Sections Comments Commands
Reports
![Page 8: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/8.jpg)
PROJECT NAMES
![Page 9: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/9.jpg)
ALPHA NUMERIC CONCEPTAll projects begin with two or three
letters followed by two numbers.
The letters organize the scripts by type/purpose/objective/process.
The numbers organize the script within the process.
![Page 10: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/10.jpg)
PROJECT NAMES We use alpha-numeric combinations:2-3 letters to describe the nature of the project
AP --- Accounts PayableAR --- Account ReceivableGL --- General LedgerGLT --- General Ledger TransactionsACS --- Account Code Structure
2 numbers o1 -99
![Page 11: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/11.jpg)
V_TYPE
The alpha-numeric construct used to identify projects can be used throughout the project to perform various tasks.
![Page 12: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/12.jpg)
SCRIPT NAMES
![Page 13: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/13.jpg)
SCRIPT NAMING CONVENTIONS
Again a four digit alpha-numeric construct can be used to define and organize script names.
![Page 14: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/14.jpg)
ADVANCE NAMING CONCEPTUse the Alpha characters to represent the
same processes in every project. For example:
AX## = Start up scriptsBX## = repeating scripts within the projectCX## = Communal scripts DX## = Data Load ScriptsEX## -WX## = Processing/Analytic ScriptsYX## = Administrative FunctionsZX## = Shut down/Clean Up Scripts
![Page 15: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/15.jpg)
2ND AND 3RD PARTSWith both scripts and projects, I will have a second part and possibly 3rd part that describes what the script does. The second part is usually what the script/project does and the third part may describe or qualify the second.
But when referring to the project or script, I always do so via the alpha-numeric constructs.
![Page 16: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/16.jpg)
EXAMPLE:
AA01Naming Conventions
AA02 Script Names
AA03 Table Names
AA04 Data Tables
AA05 Results Tables
AA06 Working Tables
AA07 Temp Tables
AA08 Variable Names
AA09 Field Names
AA10Project Names
BA01Comments
BA02 Start of Script
BA03 In Scripts
BA04 End of Scripts
CA01 Opening Sections
CA02 Comments
CA03 Commands
DA01 Other Issues to Consider
Naming ConventionsScript NamesTable Names
Data TablesResults TablesWorking TablesTemp Tables
Variable NamesField NamesProject Names
CommentsStart of ScriptIn ScriptsEnd of Scripts
Opening SectionsCommentsCommands
Other Issues to Consider
![Page 17: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/17.jpg)
TABLES
![Page 18: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/18.jpg)
TABLE NAMING CONVENTIONS
There are 4 types of tables:
1) Original Data2) Computed data/working tables3) Temporary files4) Final Product(s)/Reports
![Page 19: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/19.jpg)
TABLE NAMING CONVENTIONS
Original data files---Naming should include 3 parts:– A prefix to identify the file as original data. – The original table name without any non-alpha-
numeric characters.– The date of the data---consider YYMMDD v MMDDYY
AAA_Employeefile_111013
AP_VendorFile_111013
STD_memberfile_111013
MSTR_Hrfile_121212
{corporate initials}_Employeefile_111013
![Page 20: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/20.jpg)
TABLE NAME – TEMP FILESTemporary files are labeled TEMP001-
TEMPXXX.
This allows for Easy trouble shooting
Helps to identify where issues exist Helps to determine if expected results flowed
through temp filesClean up of project via a standardized clean
up script
![Page 21: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/21.jpg)
REPORT TABLES OPTION 1
Reports begin with the word “REPORT” followed by the alpha numeric script identifier that generated the report and a short description.
So one might have a REPORT_DA01_DuplicateAddress.
![Page 22: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/22.jpg)
REPORT TABLES OPTION 2
Reports begin with the word “REPORT” followed by the alpha numeric project identifier that generated the report and a short description with dates.
So one might have a REPORT_ACS01_091812_092012.
![Page 23: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/23.jpg)
WORKING TABLES
Working tables are generally one of two types of tables:
1) Data derived from basic data tables.2) Data contained within the project, but
not derived from current data tables.
These tables should be descriptive and contained in a special folder.
![Page 24: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/24.jpg)
VARIABLES
![Page 25: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/25.jpg)
STANDARDIZED VARIABLE NAMES
Two or three part name in creating variables.
Part 1 – the v_ that identifies the value as a variable.
Part 2 – the type of variable being definedPart 3 – a short description/qualifier for
the variable (optional)
![Page 26: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/26.jpg)
VARIABLE NAMING CONVENTIONS
User defined values should:Not be unique and not potentially used by the
client. Poor values would include “Amount”, “Company”, “Name”, etc.
They should be descriptive enough to remember and to understand. Variable “A_1” would not be a good option.
Ideally they should mirror common or anticipated names.
![Page 27: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/27.jpg)
SAMPLE VARIABLES• V_Path– V_path_bat– V_path_data– V_path_results
• V_date– V_date_run– V_date_DOS– V_date1
• V_table– V_table_AP
• V_com– V_com_test1
• V_cnt/v_cntr/V_count– V_cnt_test/
v_cntr_test• V_table– v_table_list
• V_define_tablename• V_Drive• V_type
![Page 28: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/28.jpg)
Field Names
![Page 29: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/29.jpg)
FIELDS NAMES
Field names generated by ACL should always be readily identified as such.
Amount vs amt?Dept vs Department?
Use a prefix “F_”ield or something comparable to identify a field name.
![Page 30: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/30.jpg)
COMMENTS
![Page 31: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/31.jpg)
COMMENT EVERY LINE
Best practice is to put the COM at the start of every record which should be
commented out.
![Page 32: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/32.jpg)
START OF SCRIPT COMMENTComments that go at the start of a script
should be highlighted with two rows of COM followed by asterisks. E.g.:
COM******************
COM******************
COM Written by Porter Broyles Date: 10/13/11
COM Description: This makes it easier to find the start of
COM subscripts in the log.
COM******************
COM******************
![Page 33: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/33.jpg)
OPENING COMMENTSComments should be used at the start of
every script to:1) Identify who wrote the script2) When the script was written3) When the script was last modified (by
whom)4) What the intent of the script is5) Any special considerations
![Page 34: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/34.jpg)
MIDDLE OF SCRIPT COMMENTWhen writing comments they should
stand out. To help them stand out add a line before and after the comment itself. This should include the word COM with a series of asterisks after it:
COM*********************COM details of the commentCOM*********************
![Page 35: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/35.jpg)
END OF SCRIPT COMMENTSWhen using scripts and subscripts, it can
often be a challenge to determine where one is when reviewing the project log. Whenever a subscript is called, the last command in the subscript should be a note indicating that the subscript is completed:
DO AA01_subscript_A~~~~COM*******************COM Returning from AA01_Subscript_ACOM*******************
![Page 36: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/36.jpg)
SET LOG COMMENTSBefore changing logs, always indicate what the next log is going to be. After changing logs, always indicate what the previous log was.
COM*************COM Next Log [new logname]COM*************
SET LOG [new logname]COM*************COM Old Log [old logname]COM*************
![Page 37: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/37.jpg)
SYNTAX: SHOULD BE FAMILIAR
CAPITALIZE ACL commands and functions()
Lowercase for field names DELETE FIELDS before DEFINing them DELETE tables both .fil and format
before creating Don’t abbreviate functions or
commands.
![Page 38: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/38.jpg)
INTERNATIONAL OPERATIONSFunctions use the thousandths separator to separate parts.
In the US this is the COMMA:SUBSTRING(fieldname,start,length)
In most countries, this the is period:SUBSTRING(fieldname.start.length)
Generic option, use a blank space:SUBSTRING(fieldname start length
![Page 39: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/39.jpg)
OTHER THINGS TO CONSIDER
Development v Production Change Control Version Archiving Crash Log
![Page 40: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/40.jpg)
FLOW CHARTING
Diagramming a CAMS via flow charting is an excellent way to understand how a script operates and which scripts/routines are called from different locations. You can indicate what scripts are shared scripts, scripts called via a DO-WHILE sequence, scripts called via a DO IF sequence, etc.
![Page 41: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/41.jpg)
Load Process
CA01Master ScriptScript controls load process
BC01_Select_TableProcess to pick tables
used in project
CA02 Load Data LoopSeries of Script to Load data files
CA03_Select DatesGLTDefines v_new_dateGLT
2
CA06_Load_SQL_IMPImport Files selected in BC01
2 - Repeated for each table being loaded
CA36 Date_ERRChecks to see if create date of folder is the same as data
date
BE01 Error Extracxt
CA21 BarDelimi ClnupChecks to see if table has
extra bars that needs to be cleaned up
CA22 BarDelimit2 CLNUP2
Process is repeated if there are records with extra bar delimiters
CA23 Format STBTakes care of issue with the
SYSTRANSBODY table
CA07_LoaddetailIdentifies syntax for loading each table
23
BE02_NoLoad_ExtractExtracts record if no
tables are loaded
4
CA08_CommunalCumulative file for
GLTTRNSDT
5 --- If table is GLTTRNSDT
CA34 Field Err CA35 record Err
BE01 Error Extracxt BE01 Error Extracxt
6
7
1
CA30 Verify FieldsControl Script
accumulating results from load
CA31 Bars ErrIdentify Bar differences
CA32 Load ErrLoad Errors
CA33 Verify ERRIdentify invalid fields
3
BE01 Error Extracxt3x
BE01 Error ExtracxtBE01 Error Extracxt BE01 Error Extracxt
ca40_dATAnORMALIZATION_%v_type%
Script normalizes data fields. Currenly only used
in IA01---which is a project that hasn’t been
worked much.
1
Green boxes indicate processes that are repeated via a DO WHILE Sequence
Yellow boxes indicate shared scrptsKEY
BC02 Table SelectUser selects tables
BC03_Define_DiabxCreating Dialog Box
BC04 Create ListCreating list of items
Process repeatedUntil options selected
1 - Process repeatedto create dialog bod
2 - Repeat tocreate list
1 2
This script is only called when a project/table requires special treatement
1 3
Skeleton Load Process
![Page 42: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/42.jpg)
DEVELOPMENT VS PRODUCTIONAll changes to scripts in production should be first implemented in a separate development area. The scripts in production should not be changed until and unless vetted in development first.
![Page 43: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/43.jpg)
CHANGE CONTROL AND VERSION ARCHIVING
All script changes should be approved by the manager/director over the project area. When changes are made, do not simply overwrite the old scripts, but copy them to an archive. That way if questions arise as to logic at a previous date, the exact scripts can be revisited.
![Page 44: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/44.jpg)
CRASH LOGThe crash log is one of my favorite best practices. It does several things:1) Serves as documentation should a
external reviewer ask about a process on a date where the script crashed or had to be reperformed.
2) Serves as a source to identify issues with the system/IT.
3) Serve as a means to evaluate one’s own script.
4) Serve as a means to identify bugs (both with the software and with your scripts.)
![Page 45: Continuous Assurance Best Practices: Laying the Roadmap to Success](https://reader030.vdocuments.us/reader030/viewer/2022033105/56649cc95503460f94990870/html5/thumbnails/45.jpg)
CRASH LOG
Date Issue Worked by
Resolved
Reference
5/12/12
Data from Accounts Payable was incomplete
PDB 5/13/12
Link to email from AP Manager
9/1/12 ACL could not connect to data folder
PDB 9/1/12 Link to saved email from IT
10/1/12
ACL could not connect to data folder
PDB 10/1/12
Link to saved email from IT
11/1/12
ACL could not connect to data folder
PDB 11/1/12
Link to saved email from IT