SQL*Engine - Part 2 - Launching PeopleSoft Process Requests
SQL*Engine - Part 3 - Command Guide
SQL*Engine - Part 4 - @SET Variables
SQL*Engine - Part 5 - Synonyms/Grants
SQL*Engine - Part 6 - @WAIT/@EMAIL
SQL*Engine - Part 7 - Combo Edit Demo
I had a last minute request from one of our markets yesterday (01/16/2020). Just a simple AP voucher that needed to be closed. It's a basic task that can be completed online by an authorized user but the SQL Update approach is something I'm asked to perform frequently. But instead of using TOAD or SQL*Plus to manually execute the SQL I use my custom SQL*Engine tool. I update a pre-written script with the BU/VOUCHER and drag & drop into File Depot (i.e. our custom Unix File/Folder Architecture). Within 5 minutes it's automatically picked up & processed. The Update is performed with conditional logic for Commit/Rollback, an Email Notification is sent, a report is generated, the source file is auto-archived & all step information (SQL, Rows, Status, Timings) is saved to an audit table available for online viewing.
Source File: XX_SQI_V_CLOSE_20201016.SQX
The SQL*Engine variables &&BU and &&VOUCHER need to be updated.
&&BU=13400
&&VOUCHER=00011174
The Library Script XXSQI_V_CLOSE.LIB is called which contains all the update, commit and rollback steps as well as Email Notification formatting for the requested BU/VOUCHER.
*****************************************************************
* *
* MODULE: XXSQI_V_CLOSE_X.SQX *
* AUTHOR: TONY DELIA. *
* DATE: 10/15/2015. *
* DESC: SQL*ENGINE SCRIPT - VOUCHER CLOSE. *
* *
*****************************************************************
* STANDARD SQL*ENGINE DIRECTIVES *
*****************************************************************
@ON_ERROR=CONTINUE
@DELIMITER=|
@TIMING=ON
@SAFETY=ON
@SPOOL=OFF
*****************************************************************
* FILE DEPOT LIBRARY LOCATION *
*****************************************************************
@SET=&&DBNAME=@DBNAME
@SET=&&LIB=/app/iface/&&DBNAME/19000/AP/inbound
*****************************************************************
* EMAIL NOTIFICATIONS *
*****************************************************************
@EMAIL=ON
@EMAILX=TDELIA
*****************************************************************
* CLOSE VOUCHER *
*****************************************************************
@SET=&&BU=13400
@SET=&&VOUCHER=00111744
@SCRIPT=&&LIB/XXSQL_V_CLOSE.LIB
*****************************************************************
* END OF SQL*ENGINE SCRIPT *
*****************************************************************
XXSQL_V_CLOSE.LIB - Library Module - Called via @SCRIPT= Directive.
This handles the Voucher Update, Commit/Rollback Logic & Email Notification Formatting.
*****************************************************************
* *
* MODULE: XXSQL_V_CLOSE.LIB *
* AUTHOR: TONY DELIA. *
* DATE: 09/23/2019. *
* DESC: SQL*ENGINE SCRIPT - VOUCHER CLOSE. *
* *
*****************************************************************
* *
* Required Substitution Variables *
* -------------------------------------- *
* &&BU = Business Unit *
* &&VOUCHER = Voucher ID *
* *
*****************************************************************
*****************************************************************
* HEADER DIRECTIVES *
*****************************************************************
@SET=&&YMD=@DATE
@SET=&&CRM=REQ_VCLOSE_&&YMD
@HDR_REF=&&CRM
@HDR_DESCR=CLOSE VOUCHER &&BU-&&VOUCHER
*****************************************************************
* PROGRAM VARIABLES - EXPECTED / COUNT *
*****************************************************************
@SET=&&EXPECTED=1
@SET=&&COUNT=0
*****************************************************************
* UPDATE PS_VOUCHER *
*****************************************************************
@STEP=UPDATE_VOUCHER_&&BU_&&VOUCHER
UPDATE ps_voucher u
SET u.process_instance = 0,
u.process_man_close = 'Y',
u.manual_close_dt = TRUNC(sysdate),
u.last_update_dt = TRUNC(sysdate),
u.oprid_last_updt = 'NSS_UPDT',
u.descr254_mixed = u.descr254_mixed || ' - TD &&CRM',
u.invoice_id = substr('$' || u.invoice_id,1,30)
WHERE u.business_unit = '&&BU'
AND u.process_man_close <> 'Y'
AND u.voucher_id = '&&VOUCHER'
/
@SET=&&COUNT=@ROWS
*****************************************************************
* COMPARE ROW COUNT VS. EXPECTED *
*****************************************************************
@STEP=COMPARE_COUNTS_&&BU_&&VOUCHER
@COND=SOME.UPDATE_VOUCHER_&&BU_&&VOUCHER
@COUNT=PSCLOCK
WHERE TO_NUMBER('&&EXPECTED') = TO_NUMBER('&&COUNT')
/
*****************************************************************
* UPDATE VCH_DUP_INV_REG *
*****************************************************************
@STEP=UPDATE_VCH_DUP_INV_REG_&&BU_&&VOUCHER
@COND=SOME.COMPARE_COUNTS_&&BU_&&VOUCHER
UPDATE ps_vch_dup_inv_reg u
SET u.invoice_id = substr('$' || u.invoice_id,1,30)
WHERE u.business_unit = '&&BU'
AND u.voucher_id = '&&VOUCHER'
/
*****************************************************************
* COMMIT *
*****************************************************************
@STEP=COMMIT_&&BU_&&VOUCHER
@COND=SOME.COMPARE_COUNTS_&&BU_&&VOUCHER
COMMIT
/
*****************************************************************
* ROLLBACK *
*****************************************************************
@STEP=ROLLBACK_&&BU_&&VOUCHER
@COND=SKIP.COMMIT_&&BU_&&VOUCHER
ROLLBACK
/
*****************************************************************
* EMAIL NOTIFICATION (@EMAIL/@EMAILX in Calling Script) *
*****************************************************************
@EMAIL_SUB=SQL*Engine[%ID_S] %YMD Voucher Close [%DBASE]
@EMAIL_TXT=SQL*Engine[%ID_S] %YMD Voucher Close^^
@EMAIL_TXT= Script: %FILE^
@EMAIL_TXT= Owner: %OWNER^
@EMAIL_TXT=Voucher: &&BU-&&VOUCHER^^
@EMAIL_TXT= Count: &&COUNT
*****************************************************************
* End of SQL*Engine Script *
*****************************************************************
The Email Notification appears in my Inbox - Success!
Here's the generated PDF Report: CLICK HERE
LOG File that displays variable substitution, email generation & Auto-Archiving of the source SQL*Engine file:
XX_TDSQL SQL*Engine - Dynamic SQL Script Processor
SQL Batch ID: 129
*** Variable Substitution Applied to Input ***
IN: @SET=&&LIB=/app/iface/&&DBNAME/19000/AP/inbound
OUT: @SET=&&LIB=/app/iface/NSFSPRD/19000/AP/inbound
*** Variable Substitution Applied to Input ***
IN: @SCRIPT=&&LIB/XXSQL_V_CLOSE.LIB
OUT: @SCRIPT=/app/iface/NSFSPRD/19000/AP/inbound/XXSQL_V_CLOSE.LIB
*** Variable Substitution Applied to Input ***
IN: @SET=&&CRM=REQ_VCLOSE_&&YMD
OUT: @SET=&&CRM=REQ_VCLOSE_20200116
*** Variable Substitution Applied to Input ***
IN: @HDR_REF=&&CRM
OUT: @HDR_REF=REQ_VCLOSE_20200116
*** Variable Substitution Applied to Input ***
IN: @HDR_DESCR=CLOSE VOUCHER &&BU-&&VOUCHER
OUT: @HDR_DESCR=CLOSE VOUCHER 13400-00111744
*** Variable Substitution Applied to Input ***
IN: @STEP=UPDATE_VOUCHER_&&BU_&&VOUCHER
OUT: @STEP=UPDATE_VOUCHER_13400_00111744
*** Variable Substitution Applied to Input ***
IN: u.descr254_mixed = u.descr254_mixed || ' - TD &&CRM',
OUT: u.descr254_mixed = u.descr254_mixed || ' - TD REQ_VCLOSE_20200116',
*** Variable Substitution Applied to Input ***
IN: WHERE u.business_unit = '&&BU'
OUT: WHERE u.business_unit = '13400'
*** Variable Substitution Applied to Input ***
IN: AND u.voucher_id = '&&VOUCHER'
OUT: AND u.voucher_id = '00111744'
@ROWS Set Parameter: @ROWS
Step/ROW Count Value: 1
*** Variable Substitution Applied to Input ***
IN: @STEP=COMPARE_COUNTS_&&BU_&&VOUCHER
OUT: @STEP=COMPARE_COUNTS_13400_00111744
*** Variable Substitution Applied to Input ***
IN: @COND=SOME.UPDATE_VOUCHER_&&BU_&&VOUCHER
OUT: @COND=SOME.UPDATE_VOUCHER_13400_00111744
*** Variable Substitution Applied to Input ***
IN: WHERE TO_NUMBER('&&EXPECTED') = TO_NUMBER('&&COUNT')
OUT: WHERE TO_NUMBER('1') = TO_NUMBER('1')
*** Variable Substitution Applied to Input ***
IN: @STEP=UPDATE_VCH_DUP_INV_REG_&&BU_&&VOUCHER
OUT: @STEP=UPDATE_VCH_DUP_INV_REG_13400_00111744
*** Variable Substitution Applied to Input ***
IN: @COND=SOME.COMPARE_COUNTS_&&BU_&&VOUCHER
OUT: @COND=SOME.COMPARE_COUNTS_13400_00111744
*** Variable Substitution Applied to Input ***
IN: WHERE u.business_unit = '&&BU'
OUT: WHERE u.business_unit = '13400'
*** Variable Substitution Applied to Input ***
IN: AND u.voucher_id = '&&VOUCHER'
OUT: AND u.voucher_id = '00111744'
*** Variable Substitution Applied to Input ***
IN: @STEP=COMMIT_&&BU_&&VOUCHER
OUT: @STEP=COMMIT_13400_00111744
*** Variable Substitution Applied to Input ***
IN: @COND=SOME.COMPARE_COUNTS_&&BU_&&VOUCHER
OUT: @COND=SOME.COMPARE_COUNTS_13400_00111744
*** Variable Substitution Applied to Input ***
IN: @STEP=ROLLBACK_&&BU_&&VOUCHER
OUT: @STEP=ROLLBACK_13400_00111744
*** Variable Substitution Applied to Input ***
IN: @COND=SKIP.COMMIT_&&BU_&&VOUCHER
OUT: @COND=SKIP.COMMIT_13400_00111744
*** Variable Substitution Applied to Input ***
IN: @EMAIL_TXT=Voucher: &&BU-&&VOUCHER^^
OUT: @EMAIL_TXT=Voucher: 13400-00111744^^
*** Variable Substitution Applied to Input ***
IN: @EMAIL_TXT= Count: &&COUNT
OUT: @EMAIL_TXT= Count: 1
SQL*Engine[129] 2020-01-16 Voucher Close [NSFSPRD]
SQL*Engine[129] 2020-01-16 Voucher Close
Script: XXSQI_V_CLOSE_X_20200116.SQX
Owner: TDELIA
Voucher: 13400-00111744
Count: 1
Mail: mailx -s "SQL*Engine[129] 2020-01-16 Voucher Close [NSFSPRD]" <EMAILID> <<!
https://acsfsprd.advancelocal.net/psc/nsfsprd/EMPLOYEE/ERP/c/CDM_RPT.CDM_RPT.GBL?Page=CDM_RPT_INDEX&Action=U&CDM_ID=413852
SQL*Engine[129] 2020-01-16 Voucher Close
Script: XXSQI_V_CLOSE_X_20200116.SQX
Owner: TDELIA
Voucher: 13400-00111744
Count: 1
! Len: 336
0.000000 >>> [P].tdelia@advancelocal.com
Send: mailx -s "SQL*Engine[129] 2020-01-16 Voucher Close [NSFSPRD]" tdelia@advancelocal.com <<!
https://acsfsprd.advancelocal.net/psc/nsfsprd/EMPLOYEE/ERP/c/CDM_RPT.CDM_RPT.GBL?Page=CDM_RPT_INDEX&Action=U&CDM_ID=413852
SQL*Engine[129] 2020-01-16 Voucher Close
Script: XXSQI_V_CLOSE_X_20200116.SQX
Owner: TDELIA
Voucher: 13400-00111744
Count: 1
!
IDB File Transfer Operation - Archive
Command: /app/filewatcher/bin/nss_mv.sh /app/iface/NSFSPRD/19000/AP/inbound/process/XXSQI_V_CLOSE_X_20200116.SQX /app/iface/NSFSPRD/19000/AP/inbound/archive/XXSQI_V_CLOSE_X_20200116_20200116172526.SQX
Status: Success [0.000000]
Having SQL*Engine Scripts & Library Modules available for a variety of basic tasks is a time saver.