Summary: Detail on how to create a Visual Basic script (VBS) which parses the Deduction Detail Summary (DDS) report creating SQL statements to insert detail, from the DDS report, into a temporary table. The benefit of such a VBS is that the deduction detail can then be joined with YTD information and percentages maintained on the Employee Deduction (EDF) table. By joining this information it is possible to balance complex deduction formulas which are dependent on one another.

Request: A client recently asked for assistance in balancing their 401(k) deductions.  See article Q10023 for a summary explanation of the request and solution.  The specific portion of that request, which this article details, relates to Runs a VB script to parse the DDS reports and running of SQL statements to update a temporary (temp_dds) table.  The specific request was for 19 deduction codes.  The client asked that they submit a single emPath report request which triggered the DDS report to run for 19 deduction codes.

Solution: Use a Visual Basic Script to parse the DDS reports creating SQL statements which insert and/or update a temporary table on the emPath database. 

Following is a diagram which depicts this process:

The VBS script used is attached to this article in a zip file.  The script is very small however I attached it as a zip in case there are client filters which prevent my readers from downloading a VB script.

(1) The command procedure statements to run the DDS_parse are as follows:

:sub070_DDS_parse
@echo ****************************************************** 
@echo *** run DDS parse 
time /t
(%datadrive%:)
(cd %hrdatbow%)
(if exist dds_update_temp_ddx_table.sql del dds_update_temp_ddx_table.sql)
(cscript %bow_cmn_wsh%\DDS_parse.vbs %dd_RPT_PR852% dds_update_temp_ddx_table.sql //B)
(goto :eof)

Note that cscript was used to execute the vbs.  In this manner if a file is not found or any other error occurs then an error will be written to the log.  If the vbs is executed directly or using wscript then errors appear as prompts in an interactive mode.

Note that parameters are passed to the vbs.  The first paramter is the DDS report while the second parameter is the SQL file which is created by the vbs.

(2) The vbs uses several features including arguments, file systems objects (fso), substrings, if test, coverting string to double, formating, and writing the results to file.

(3) An example SQL statement created by the vbs is as follows: 

begin insert into temp_ddx
(em_employee_id, dmf_deduction_code, dds_check_date, dds_year_and_run_id, dds_deduction_amount, dds_arrearage_amount)
values('882005970', 'DEFSAVR', '20080131', '2008-S01M', 265.83, 0.00);
Exception WHEN DUP_VAL_ON_INDEX then
update temp_ddx set
  dds_deduction_amount = dds_deduction_amount + 265.83,
  dds_arrearage_amount = dds_arrearage_amount + 0.00
where em_employee_id = '882005970' 
  and dmf_deduction_code = 'DEFSAVR' 
  and dds_check_date = '20080131' 
  and dds_year_and_run_id = '2008-S01M' ;
end; 
/

Note that the SQL statement attempts an insert and if ther is an exception then an update is performed.

(4) The command procedure statements used to execute the SQL are as follows:

:sub080_create_temp_ddx_table
@echo ******************************************************  
@echo *** sub080_create_temp_ddx_table
(time /t)   
(%sq% @%bow_cmn_sql%\dds_create_temp_ddx_table) 
(if not errorlevel 0 goto endwitherror)
(goto :eof)

:sub090_update_temp_ddx_table
@echo ******************************************************  
@echo *** sub090_update_temp_ddx_table
(time /t) 
(%sq% @%hrdatbow%\dds_update_temp_ddx_table) 
(if not errorlevel 0 goto endwitherror)
(goto :eof)

Note that in subroutine sub080 a temporary table is dropped then re-created.  Then in subroutine sub090 the temporary table is inserted and/or updated.

Note that the example is specific to SQLplus updating Oracle.

Note that the example is dependent on the schema user name and password being included in the set of enviornment variable SQ.

*** If you have questions about the batch command procedure techniques used you may want to review the article titled emPath Custom Command Procedures.  It contains a significant amount of detail in a compiled HTML file.