640 likes | 833 Views
USING SCRIPTS FOR A BIO-DIESEL AUDIT. CHARLES R GAUNTT ACL USERS GROUP FRIDAY, DECEMBER 12, 2008. BASIC OUTLINE. WHY WE DID THE AUDIT - RENEWABLE FUELS REGULATIONS QUICK AND DIRTY DEVELOPMENT USING SCRIPT RECORDER STREAMLINE LOGIC YEAR 2 AFTER SCRIPTS CLASS
E N D
USING SCRIPTS FOR A BIO-DIESEL AUDIT CHARLES R GAUNTT ACL USERS GROUP FRIDAY, DECEMBER 12, 2008 BIO-DIESEL SCRIPTS
BASIC OUTLINE • WHY WE DID THE AUDIT - RENEWABLE FUELS REGULATIONS • QUICK AND DIRTY DEVELOPMENT USING SCRIPT RECORDER • STREAMLINE LOGIC YEAR 2 AFTER SCRIPTS CLASS • ADVANCED FEATURES WE ARE STUMBLING THROUGH NOW BIO-DIESEL SCRIPTS
RENEWABLE FUELS I WORK FOR SOUTHERN STATES BECAUSE I DO NOT KNOW WHAT AGRICULTURE, THE FASB, THE STATE, THE COOP, OR THE FEDERAL GOVERNMENT WILL DO TO ME NEXT. IN THIS CASE THE FEDERAL GOVERNMENT GOT ME. BIO-DIESEL SCRIPTS
The United States enacted legislation to promote and control the generation of renewable fuels by Energy Companies. Companies that produce or sell Renewable fuels must conduct an audit and file it with the EPA. Internal Audit Departments may conduct these audits. BIO-DIESEL SCRIPTS
Southern States Petroleum Divisions buys a 100% Soybean Based Diesel additive from local energy companies located in Maryland and Virginia. The additive is mixed with Diesel fuel to create a 1%, 5% or 20% mixture for Trucks, Tractors, and Homes. A RIN is assigned to Southern States when we buy the additive. We must report the number of RIN’s bought and then blended on a quarterly basis to the EPA. BIO-DIESEL SCRIPTS
WHY USE ACL? • WE ARE A DOWNSTREAM RETAIL BLENDER OF BIODIESEL RENEWABLE FUELS. • WE MUST PROVE WE ARE A DOWNSTREAM BLENDER THAT DOES NOT SELL 100% PRODUCT. • WE USE ACL AUDIT SOFTWARE TO DEMONSTRATE WE ONLY SELL BLENDS LESS THAN 100%. • WE USE AUDIT SOFTWARE TO DEMONSTRATE WE DO NOT SELL BLENDS OF 100%. BIO-DIESEL SCRIPTS
DESIGN ASPECTS • USED A DATA WAREHOUSE RETAIL SALES FILE. • PIGGY BACKED ON SYSTEMS DEVELOPED BY TAX DEPARTMENT TO FILE FOR FEDERAL TAX REBATES. • CREATED THE DEMAND FOR A RETAIL RECEIVING DATA WAREHOUSE. BIO-DIESEL SCRIPTS
FIRST SHOT AT THE PROBLEM • PROCESSED DATA FOR SEPTEMBER THROUGH DECEMBER. • USED SEPTEMBER TO DEVELOP PILOT SCRIPTS. • REPLICATED FOR REMAINING QUARTERS. • USED THE SCRIPT RECORDER FOR QUICK AND DIRTY DEVELOPMENT • FOUND OUT EDIT TABLE COMMANDS ARE NOT CAPTURED ON SCRIPTS BIO-DIESEL SCRIPTS
Use Script Recorder for Quick Development BIO-DIESEL SCRIPTS
OPEN THE SEPTEMBER FILE BIO-DIESEL SCRIPTS
USE EDIT TABLE COMMAND TO CREATE AN EXPRESSION TO CLASSIFY SKU’S AS BIODIESEL. BIO-DIESEL SCRIPTS
USE DUPLICATE CONDITION AND EDIT CONDITION TO TEST FOR MULTIPLE BIO SKUS BIO-DIESEL SCRIPTS
ADDED BIO MARKER EXPRESSION AS COLUMN ON DATA FILE BIO-DIESEL SCRIPTS
TURN OFF SCRIPT RECORDER BIO-DIESEL SCRIPTS
CREATE SEPTEMBER PILOT SCRIPT BIO-DIESEL SCRIPTS
REVIEWED SEPTEMBER SCRIPT I FOUND OUT THAT NONE OF THE FILTER COMMANDS EXECUTED IN EDIT TABLE LAYOUT WERE CAPTURED IN THE SEPTEMBER SCRIPT. THE SCRIPT HAD TO BE REDESIGNED USING THE SET FILTER ON COMMAND. BIO-DIESEL SCRIPTS
I CREATED A FILTER CALLED BIO SKU BIO-DIESEL SCRIPTS
EXTRACT DATA COMMAND AFTER SETTING THE SCRIPT RECORDER ON AND OPENING SEPTEMBER AGAIN, I EXECUTED THE EXTRACT DATA COMMAND. BIO-DIESEL SCRIPTS
I INSERTED A FILTER IN THE IF PARAMETER OF THE EXTRACT COMMAND BIO-DIESEL SCRIPTS
USING BIO_SKU BIO-DIESEL SCRIPTS
THE RESULTING FILE LOOKS REASONABLE. BIO-DIESEL SCRIPTS
I TURNED SCRIPT RECORDER OFF AND LOOKED AT THE RESULTING SCRIPT. I FOUND FOR THE COMMAND TO WORK FOR EACH MONTHLY FILE, THE BIO_SKU FILTER WOULD NEED TO BE DEFINED IN THE OCTOBER, NOVEMBER, AND DECEMBER FILES. THEREFORE I TOOK THE EASY WAY OUT AND COPIED THE FILTER DIRECTLY INTO THE SCRIPT COMMAND LINE. BIO-DIESEL SCRIPTS
I RAN SEPTEMBER_SCRIPT2 BIO-DIESEL SCRIPTS
AND THE RESULTING FILE APPEARED REASONABLE, TO CONTAIN ONLY BIO DIESEL SKUS BIO-DIESEL SCRIPTS
AGAIN I TURNED SCRIPT RECORDER ON AND THEN BIO-DIESEL SCRIPTS
I NOW START TO CAPTURE A CROSSTAB COMMAND THAT WILL ANALYZE STORE SALES OF THE BIO-DIESEL SKUS BIO-DIESEL SCRIPTS
THE RESULTS ARE STORED IN A FILE SO THEY CAN BE EXPORTED TO EXCEL FOR FURTHER REVIEW. BIO-DIESEL SCRIPTS
THE FILE LOOKS GOOD, THE STORES AND SKUS ARE REASONABLE. BIO-DIESEL SCRIPTS
SO WE PROCEED TO EXPORT TO EXCEL. BIO-DIESEL SCRIPTS
A FULL VIEW IS EXPORTED TO EXCEL. BIO-DIESEL SCRIPTS
THE STANDARD OUTPUT BIO-DIESEL SCRIPTS
HERE IS THE RESULTING SPREADSHEET. NOTE THE TOTALS BY STORE THAT COULD BE USED FOR REPORT PREPARATION. ALSO, THE 65254300 SKU HAD NO SALES, WHICH MEANS SSC IS NOT A DISTRIBUTOR, BUT JUST A DOWNSTREAM BLENDER. BIO-DIESEL SCRIPTS
THE EXPORT COMMAND IS CAPTURED BY SCRIPT RECORDER IN SCRIPT EXPORTCOMMAND WHICH IS THEN COPIED INTO SCRIPT SEPTEMBER_SCRIPT2. BIO-DIESEL SCRIPTS
HERE IS THE COMPLETE SCRIPT FOR SEPTEMBER BIO-DIESEL SCRIPTS
HERE ARE THE SCRIPT COMMANDS IN ENGLISH • OPEN rsssept07 • EXTRACT RECORD IF sku = "65254300" OR SKU = "65246290" OR sku = "65246540" OR SKU = "65246550" OR SKU = "65254600" TO "SEPTEMBERBIODIESEL" OPEN • CROSSTAB ON store_num COLUMNS sku SUBTOTAL trans_amt qty COUNT TO "SEPTEMBERBIODIESELCROSSTAB.FIL" OPEN • OPEN "SEPTEMBERBIODIESELCROSSTAB" • EXPORT FIELDS store_num AS 'store_num' trans_amt_65246540 AS 'trans_amt sku 65246540' qty_65246540 AS 'qty sku 65246540' Count_65246540 AS 'Count sku 65246540' trans_amt_65246550 AS 'trans_amt sku 65246550' qty_65246550 AS 'qty sku 65246550' Count_65246550 AS 'Count sku 65246550' trans_amt_65254600 AS 'trans_amt sku 65254600' qty_65254600 AS 'qty sku 65254600' Count_65254600 AS 'Count sku 65254600' EXCEL TO "SEPTEMBERBIODIESELCROSSTAB" BIO-DIESEL SCRIPTS
THE SEPTEMBER SCRIPT WAS USED AS A FOUNDATION FOR A QUARTERLY SCRIPT BIO-DIESEL SCRIPTS
REPLACE COMMANDS ARE USED TO EDIT THE MONTH. BIO-DIESEL SCRIPTS
CREATING AN OCTOBER SCRIPT. THE OCTOBER SCRIPT IS COPIED AND MODIFIED TWICE TO CREATE A QUARTERLY SCRIPT. BIO-DIESEL SCRIPTS
FIND AND REPLACE BIO-DIESEL SCRIPTS
ARE USED TO CONSTRUCT A QUARTERLY SCRIPT BIO-DIESEL SCRIPTS
THE QUARTERLY SCRIPT WAS EXECUTED TO ANALYZE 2007 BIODIESEL REPORTING. BIO-DIESEL SCRIPTS
USING THE RUN SCRIPT COMMAND BIO-DIESEL SCRIPTS
OCTOBER LOG • Log File C:\Documents and Settings\crg01\My Documents\kpmg08\rssales\salesrss2008_LOG Opened at 15:35:28 on 12/08/2008 • @ DO QUARTERLY_SCRIPT • 15:35:55 - 12/08/2008 • @ OPEN rssoct07 • 15:35:55 - 12/08/2008 • 12 fields activated • Opening file name rssoct07.fil as supplied in the table layout. • @ EXTRACT RECORD IF sku = "65254300" OR SKU = "65246290" OR sku = "65246540" OR SKU = "65246550" OR SKU = "65254600" TO "octOBERBIODIESEL" OPEN • 15:35:55 - 12/08/2008 • 89 of 1083931 met the test: sku = "65254300" OR SKU = "65246290" OR sku = "65246540" OR SKU = "65246550" OR SKU = "65254600" • 89 records produced • Extraction to table C:\Documents and Settings\crg01\My Documents\kpmg08\rssales\octOBERBIODIESEL.FIL is complete • Opening file "octOBERBIODIESEL" • @ OPEN "octOBERBIODIESEL" • 15:35:57 - 12/08/2008 • 12 fields activated • Opening file name octOBERBIODIESEL.FIL as supplied in the table layout. • @ CROSSTAB ON store_num COLUMNS sku SUBTOTAL trans_amt qty COUNT TO "octOBERBIODIESELCROSSTAB.FIL" OPEN • 15:35:57 - 12/08/2008 • 10 records produced • Output to C:\Documents and Settings\crg01\My Documents\kpmg08\rssales\octOBERBIODIESELCROSSTAB.FIL is done • Opening file "octOBERBIODIESELCROSSTAB" • @ OPEN "octOBERBIODIESELCROSSTAB" • 15:35:57 - 12/08/2008 • 13 fields activated • Opening file name octOBERBIODIESELCROSSTAB.FIL as supplied in the table layout. • @ OPEN "octOBERBIODIESELCROSSTAB" • 15:35:57 - 12/08/2008 • 13 fields activated • Opening file name octOBERBIODIESELCROSSTAB.FIL as supplied in the table layout. • @ EXPORT FIELDS store_num AS 'store_num' trans_amt_65246540 AS 'trans_amt sku 65246540' qty_65246540 AS 'qty sku 65246540' Count_65246540 AS 'Count sku 65246540' trans_amt_65246550 AS 'trans_amt sku 65246550' qty_65246550 AS 'qty sku 65246550' Count_65246550 AS 'Count sku 65246550' trans_amt_65254600 AS 'trans_amt sku 65254600' qty_65254600 AS 'qty sku 65254600' Count_65254600 AS 'Count sku 65254600' EXCEL TO "octOBERBIODIESELCROSSTAB" • 15:35:57 - 12/08/2008 • 10 records produced • Output to C:\Documents and Settings\crg01\My Documents\kpmg08\rssales\octOBERBIODIESELCROSSTAB.XLS is done BIO-DIESEL SCRIPTS
NOVEMBER LOG • @ OPEN rssNOV07 • 15:35:57 - 12/08/2008 • 12 fields activated • Opening file name rssnov07.fil as supplied in the table layout. • @ EXTRACT RECORD IF sku = "65254300" OR SKU = "65246290" OR sku = "65246540" OR SKU = "65246550" OR SKU = "65254600" TO "NOVEMBERBIODIESEL" OPEN • 15:35:57 - 12/08/2008 • 68 of 1015988 met the test: sku = "65254300" OR SKU = "65246290" OR sku = "65246540" OR SKU = "65246550" OR SKU = "65254600" • 68 records produced • Extraction to table C:\Documents and Settings\crg01\My Documents\kpmg08\rssales\NOVEMBERBIODIESEL.FIL is complete • Opening file "NOVEMBERBIODIESEL" • @ OPEN "NOVEMBERBIODIESEL" • 15:35:59 - 12/08/2008 • 12 fields activated • Opening file name NOVEMBERBIODIESEL.FIL as supplied in the table layout. • @ CROSSTAB ON store_num COLUMNS sku SUBTOTAL trans_amt qty COUNT TO "NOVEMBERBIODIESELCROSSTAB.FIL" OPEN • 15:35:59 - 12/08/2008 • 6 records produced • Output to C:\Documents and Settings\crg01\My Documents\kpmg08\rssales\NOVEMBERBIODIESELCROSSTAB.FIL is done • Opening file "NOVEMBERBIODIESELCROSSTAB" • @ OPEN "NOVEMBERBIODIESELCROSSTAB" • 15:35:59 - 12/08/2008 • 13 fields activated • Opening file name NOVEMBERBIODIESELCROSSTAB.FIL as supplied in the table layout. • @ OPEN "NOVEMBERBIODIESELCROSSTAB" • 15:35:59 - 12/08/2008 • 13 fields activated • Opening file name NOVEMBERBIODIESELCROSSTAB.FIL as supplied in the table layout. • @ EXPORT FIELDS store_num AS 'store_num' trans_amt_65246540 AS 'trans_amt sku 65246540' qty_65246540 AS 'qty sku 65246540' Count_65246540 AS 'Count sku 65246540' trans_amt_65246550 AS 'trans_amt sku 65246550' qty_65246550 AS 'qty sku 65246550' Count_65246550 AS 'Count sku 65246550' trans_amt_65254600 AS 'trans_amt sku 65254600' qty_65254600 AS 'qty sku 65254600' Count_65254600 AS 'Count sku 65254600' EXCEL TO "NOVEMBERBIODIESELCROSSTAB" • 15:35:59 - 12/08/2008 • 6 records produced • Output to C:\Documents and Settings\crg01\My Documents\kpmg08\rssales\NOVEMBERBIODIESELCROSSTAB.XLS is done • @ OPEN rssDEC07 • 15:36:01 - 12/08/2008 • 12 fields activated BIO-DIESEL SCRIPTS
DECEMBER LOG • Opening file name rssdec07.fil as supplied in the table layout. • @ EXTRACT RECORD IF sku = "65254300" OR SKU = "65246290" OR sku = "65246540" OR SKU = "65246550" OR SKU = "65254600" TO "DECEMBERBIODIESEL" OPEN • 15:36:01 - 12/08/2008 • 63 of 988687 met the test: sku = "65254300" OR SKU = "65246290" OR sku = "65246540" OR SKU = "65246550" OR SKU = "65254600" • 63 records produced • Extraction to table C:\Documents and Settings\crg01\My Documents\kpmg08\rssales\DECEMBERBIODIESEL.FIL is complete • Opening file "DECEMBERBIODIESEL" • @ OPEN "DECEMBERBIODIESEL" • 15:36:04 - 12/08/2008 • 12 fields activated • Opening file name DECEMBERBIODIESEL.FIL as supplied in the table layout. • @ CROSSTAB ON store_num COLUMNS sku SUBTOTAL trans_amt qty COUNT TO "DECEMBERBIODIESELCROSSTAB.FIL" OPEN • 15:36:04 - 12/08/2008 • 7 records produced • Output to C:\Documents and Settings\crg01\My Documents\kpmg08\rssales\DECEMBERBIODIESELCROSSTAB.FIL is done • Opening file "DECEMBERBIODIESELCROSSTAB" • @ OPEN "DECEMBERBIODIESELCROSSTAB" • 15:36:04 - 12/08/2008 • 13 fields activated • Opening file name DECEMBERBIODIESELCROSSTAB.FIL as supplied in the table layout. • @ OPEN "DECEMBERBIODIESELCROSSTAB" • 15:36:04 - 12/08/2008 • 13 fields activated • Opening file name DECEMBERBIODIESELCROSSTAB.FIL as supplied in the table layout. • @ EXPORT FIELDS store_num AS 'store_num' trans_amt_65246540 AS 'trans_amt sku 65246540' qty_65246540 AS 'qty sku 65246540' Count_65246540 AS 'Count sku 65246540' trans_amt_65246550 AS 'trans_amt sku 65246550' qty_65246550 AS 'qty sku 65246550' Count_65246550 AS 'Count sku 65246550' trans_amt_65254600 AS 'trans_amt sku 65254600' qty_65254600 AS 'qty sku 65254600' Count_65254600 AS 'Count sku 65254600' EXCEL TO "DECEMBERBIODIESELCROSSTAB" • 15:36:04 - 12/08/2008 • 7 records produced • Output to C:\Documents and Settings\crg01\My Documents\kpmg08\rssales\DECEMBERBIODIESELCROSSTAB.XLS is done • Execution of QUARTERLY_SCRIPT completed BIO-DIESEL SCRIPTS
AS YOU CAN SEE OCTOBER, NOVEMBER, AND DECEMBER BIO-DIESEL FILES EXIST BIO-DIESEL SCRIPTS
OF COURSE 2008 BRINGS A NEW YEAR. ARMED WITH THE 2007 RESULTS, AN ACL SCRIPT CLASS, AND SEVERAL ACL USER GROUP MEETINGS I TRIED TO IMPROVE THE ACL SCRIPTS. BIO-DIESEL SCRIPTS
YEAR 2 IMPROVEMENTS • MATCH COMMAND • SET OFF COMMAND • COMMENT COMMAND • SUMMARIZE COMMAND • MODULAR SUBPROGRAM SCRIPTS BIO-DIESEL SCRIPTS