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.
