SQL*Engine - Part 7 - Combo Edit Demo

SQL*Engine - Part 1 - Initial Overview/Demonstration
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

Also PeopleSoft Dynamic Combination Edits - Background info on my XX_CE() function.

Since I'm playing around with my SQL*Engine Utility I thought I'd create a quick demo of my Dynamic Combination Edit function. Previously I demonstrated various methods of utilizing my routine - Within a PeopleSoft Component via PeopleCode, external systems such as Content Server, client/server on the desktop & interface batch processing via Process Scheduler. Now, I'll perform the routine within an SQL*Engine Script & email the results... very simple.

I can simplify this script a great deal by defining the &&COMBO variable as a full delimited string but I've chosen to set individual chartfield variables & use the @CONCAT @SET directive to build the string. &&COMBO will be used as the input parameter for my XX_CE() function - the @LIST, @SELECT and @SET/@COL directives are used to pull the resulting output. Errors are returned as a tilde (~) delimited string. I'll then use @REPLACE to change the tildes (~) into a caret (^) which is then used as a carriage return in the @EMAIL_TXT= directive.

*****************************************************************
*                                                               *
*    MODULE: XXSQI_CE.SQX                                       *
*    AUTHOR: TONY DELIA.                                        *
*      DATE: 09/22/2019.                                        *
*      DESC: SQL*ENGINE SCRIPT - COMBO EDIT SAMPLE.             *
*                                                               *
*****************************************************************
@ON_ERROR=CONTINUE
@DELIMITER=|
@TIMING=ON
@SAFETY=ON
@SPOOL=OFF
*****************************************************************
*  PROGRAM VARIABLES                                            *
*****************************************************************
@SET=&&YMD=@DATE
@HDR_REF=TD_CE_&&YMD
@HDR_DESCR=Combo Edit SQL*Engine Script &&YMD
*****************************************************************
*  SET INDIVIDUAL CHARTFIELD VALUES                             *
*****************************************************************
@SET=&&P1_GLBU=12100
@SET=&&P2_ACCT=110100
@SET=&&P3_DEPT=100
@SET=&&P4_CHF2=001
@SET=&&P5_CHF3=
@SET=&&P6_PROD=16
@SET=&&P7_CHF1=
@SET=&&P8_AFFL=
*****************************************************************
*  CONSOLIDATE INTO DELIMITED (~) POSITIONAL PARAMETER STRING   *
*****************************************************************
@DELIMITER=,
@SET=&&COMBO=
@SET=&&COMBO=@CONCAT(&&P1_GLBU)
@SET=&&COMBO=@CONCAT(~&&P2_ACCT)
@SET=&&COMBO=@CONCAT(~&&P3_DEPT)
@SET=&&COMBO=@CONCAT(~&&P4_CHF2)
@SET=&&COMBO=@CONCAT(~&&P5_CHF3)
@SET=&&COMBO=@CONCAT(~&&P6_PROD)
@SET=&&COMBO=@CONCAT(~&&P7_CHF1)
@SET=&&COMBO=@CONCAT(~&&P8_AFFL)
*****************************************************************
*  EXECUTE COMBO EDIT PL/SQL FUNCTION                           *
*****************************************************************
@STEP=COMBO_EDIT
@COND=NULL
@LIST=XX_CE('&&COMBO')
@SELECT=DUAL
/
@SET=&&OUTPUT=@COL:1
@SET=&&OUTPUT=@REPLACE(~,^)
*****************************************************************
*  CONFIGURE EMAIL NOTIFICATION                                 *
*****************************************************************
@EMAIL=ON
@EMAILX=TDELIA
@EMAIL_SUB=SQL*Engine[%ID_S] %YMD ComboEdit Demo
@EMAIL_TXT=SQL*Engine[%ID_S] %YMD ComboEdit Demo %DBASE^^
@EMAIL_TXT=Combination: &&COMBO^^
@EMAIL_TXT=Errors:^
@EMAIL_TXT=&&OUTPUT^
*****************************************************************
*  DISPLAY VARIABLES                                            *
*****************************************************************
@DISPLAY
@MSG=End of Combination Edit Demonstration
*****************************************************************
*   End of SQL*Engine Script                                    *
*****************************************************************


The script above has been run successfully with the results emailed as expected...

Here's a copy of the SQL*Engine PDF Report - CLICK HERE.

Below is the Email Notification:

The Consolidated Chartfield String used by the XX_CE() function is included along with all the Combination Edit Rules that failed (with a carriage return after each one).

This was a very powerful, yet simple & fun exercise.

SQL*Engine - Dynamic SQL Script Processor - Part - 6 - @WAIT/@EMAIL

SQL*Engine - Part 1 - Initial Overview/Demonstration
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

Welcome back. This is Part 6 of my Custom SQL*Engine series. I added some additional functionality (Part 3 Command Guide updated) which I will highlight here. The @WAIT= directive is a useful companion to the @RUN= directive I explained in Part 2. Once a process is launched it suspends the script until it completes (Success or Failure). I also added Email Notifications that can be controlled through several optional Email-Based directives (@EMAIL=, @EMAILX=, @EMAIL_SUB & @EMAIL_TXT).

To illustrate these new features I'll use a script that resets our nightly GL batch jobset in the case of a failure due to network or database issues. It will call a standard script to update/delete process schedule info, then launch the FS_JGEN Application Engine to clear Temp Table Locks & WAIT for the called process to complete. Then it will retrieve & display the status of the FS_JGEN job and send an email notification.

To execute the script XXSQI_GL_RESET.SQX I simply drag and drop into the proper File Depot location - that's it. File Depot is the name given to our file server structure. The filename pattern XXSQI*.SQX is configured to be automatically processed by the SQL*Engine module XX_TDSQL.SQR. This is part of the broader 24/7 Interface Methodology I developed. The script will be detected automatically within 5 minutes, moved to the /process folder & archived when complete. These actions occur as part of my API used for all inbound/outbound interfaces - SQL*Engine scripts are files just like any other data extract.

Above: XXSQI_GL_RESET.SQX dropped in the server directory. This script will be using the XXSQL_JOB_RESET.LIB for some standard updates & XXSQL_RUN.LIB to launch the FS_JGEN job.

Above: XX_TDSQL launched & running the XXSQI_GL_RESET.SQX script. It has launched the FS_JGEN job & is waiting for it to complete before proceeding further.

Above: FS_JGEN is processing while XX_TDSQL is still in a wait state. Althought Process Monitor lists the run status as "Processing" it has not moved past the @WAIT= directive.

Above: FS_JGEN completes (Success) which allows XX_TDSQL to continue past the @WAIT= directive and wrap up.

 Main Demonstration Script - XXSQI_GL_RESET.SQX:

Before displaying the XXSQI_GL_RESET.SQX script it is worth pointing out that the process instance for the called FS_JGEN job is populated in the XXSQL_RUN.LIB module. It is passed to the &&RUN_pi dynamic variable - then used in the @WAIT=&&RUN_pi directive.

From the called SQL*Engine Script - XXSQL_RUN.LIB:

@RUN=&&RUNPARMS
/
@SET=&&RUN_pi=@ROWS

The &&RUN_pi variable has been set with the process instance. This will be used by the @WAIT= directive in our main script. Scroll down to find it highlighted in YELLOW.

*****************************************************************
*                                                               *
*    MODULE: XXSQI_GL_RESET.SQX                                 *
*    AUTHOR: TONY DELIA.                                        *
*      DATE: 09/15/2019.                                        *
*      DESC: SQL*ENGINE SCRIPT - XXGLPRCS/FS_JGEN RESET.        *
*                                                               *
*****************************************************************
@ON_ERROR=CONTINUE
@DELIMITER=|
@TIMING=ON
@SAFETY=ON
@SPOOL=OFF
*****************************************************************
*  PROGRAM VARIABLES                                            *
*****************************************************************
@SET=&&YMD=@DATE
@HDR_REF=TD_GLRESET_&&YMD
@HDR_DESCR=GL_RESET_&&YMD XXGLPRCS Reset
@SET=&&DBNAME=@DBNAME
@SET=&&LIB=/app/iface/&&DBNAME/19000/AP/inbound
*****************************************************************
*  RESET NIGHTLY GL JOBSET (STANDARD)                           *
*****************************************************************
@SET=&&JOBSET=XX_GL_ALL_BUS_JOBSET 
@SCRIPT=&&LIB/XXSQL_JOB_RESET.LIB
*****************************************************************
*  CALCULATE NEXT START DATE/TIME (10:30PM)                     *
*****************************************************************
@STEP=CALCULATE_NEXT_DTTM
@LIST=to_char(sysdate+(1/12),'YYYYMMDD')||'2230'
@SELECT=DUAL
/
@SET=&&DTTM=@COL:1
@MSG=Next &&JOBSET Start Date[&&DTTM]
*****************************************************************
*  APPLY NEXT START DATE/TIME (10:30PM)                         *
*****************************************************************
@STEP=CALCULATE_NEXT_DTTM
@COND=PASS
update ps_schdldefn
   set startdatetime    = to_date('&&DTTM','YYYYMMDDHH24MI'),
       recurname        = 'XX_GL_DAILY_RCR',
       schedulestatus   = '1',
       lastupddttm      = sysdate,
       lastupdoprid     = 'PSBATCH'
 where schedulename     = '&&JOBSET'
/
@SET=&&TEMP=@ROWS
@MSG=Jobset[&&JOBSET] DttmUpdated[&&TEMP]
*****************************************************************
*  COMMIT NEXT START DATE/TIME                                  *
*****************************************************************
@STEP=COMMIT_NEXT_DTTM
@COND=PASS
commit
/
*****************************************************************
*  RUN JOURNAL GENERATOR (CLEAR LOCKS)                          *
*****************************************************************
@SET=&&RUNPARMS=5|FS_JGEN|PSBATCH|GL_AP_GJ_ALLBUS_NIGHTLY
@SCRIPT=&&LIB/XXSQL_RUN.LIB
*****************************************************************
*  WAIT FOR JOURNAL GENERATOR COMPLETION                        *
*****************************************************************
@WAIT=&&RUN_pi
*****************************************************************
*  SELECT JOURNAL GENERATOR RUN STATUS                          *
*****************************************************************
@STEP=SELECT_RUN_STATUS
@COND=NULL
@LIST=runstatus
@SELECT=psprcsrqst
 where prcsinstance = &&RUN_pi
/
@SET=&&RUN_STAT=@COL:1
@DELIMITER=,
@SET=&&RUN_DESC=@XLTITEM(RUNSTATUS,&&RUN_STAT)
@MSG=Process[&&RUN_pi] Status[&&RUN_STAT] Desc[&&RUN_DESC]
*****************************************************************
*  EMAIL NOTIFICATION                                           *
*****************************************************************
@EMAIL=ON
@EMAILX=TDELIA
@EMAIL_SUB=SQL*Engine[%ID_S] %YMD - GL Reset/JGEN
@EMAIL_TXT=SQL*Engine GL Reset - %DBASE^^
@EMAIL_TXT= File: %FILE^^
@EMAIL_TXT=Batch: %ID_B^^
@EMAIL_TXT=Owner: %OWNER^^
@EMAIL_TXT=FSGEN: &&RUN_pi^^
*****************************************************************
*  DISPLAY ALL VARIABLES                                        *
*****************************************************************
@DISPLAY
*****************************************************************
*   End of SQL*Engine Script                                    *
*****************************************************************

After the @WAIT=&&RUN_pi the script continues and extracts the FS_JGEN run status & displays it on the report. The @XLTITEM= @SET directive translates the value into the long description (found in PSXLATITEM). Then it proceeds with the Email Notification functionality.

@EMAIL=ON activates the email notification (actually ON by default). If this is the only EMAIL option used a generic email will be sent to all recipients on the distribution list of the job.

@EMAILX=TDELIA overrides the distribution list. The email address from the OPRID will be used. Multiple @EMAILX= directives may be used with one or more delimited recipients specified. Recipients may be defined as a PeopleSoft OperatorID or an explicit email address.

A generic Subject & Text will be sent unless they are overridden.

@EMAIL_SUB= will override the generic subject while @EMAIL_TXT= is used for the email text. Reference the Command Guide in Part 3 for more details including meta-variable use.

At the end of the script the Email Notification is sent to the appropriate users.

Above: Email Notification arrives in my Inbox.

Above: Full Email Notification - Both the Subject & Text have been overridden using a combination of Text & Meta-Variables: %ID_S, %YMD, %DBASE, %FILE, %ID_B and %OWNER. The FS_JGEN process instance was passed using the dynamic variable &&RUN_pi.

NOTE: Several other updates were made to the SQL*Engine module - all of which have been updated in the Part 3 - Command Guide post. @STALL= (generic delay for x seconds) and @SET directives @CONCAT, @XLTITEM (used above) and @DATE (formats current date in YYYYMMDD format).

To view the PDF report for this SQL*Engine Script CLICK HERE