It is time to go up in the clouds ;). (If you’ve not read Part I and II, please go ahead and read them, I’ll reusing what we discussed over there in the following posts).
This is Vision Application in PBCS. What I did was to add the following members to support Flex Process.
DestEntity, FlexID, and OrgCheck are Smart List members.
FlexDateStamp is a Date member.
Same webform that we saw earlier is imported into PBCS and as you can see from the above screenshot DestEntity is using a Hierarchy driven Smart List.
Now I was under the impression that 17.02 release of PBCS will open up Groovy for everyone however this is still limited in EPBCS and some early adopter clients. So I’m going to talk about how this can be done in a non-Groovy PBCS environment first and then follow-up with a Groovy enabled PBCS environment.
Non Groovy PBCS Environment
Groovy CDF functions are not supported in PBCS and what I’m going to do is a trick which mimics what we did on on-prem.
Here is what I did at a client
- Allow user to save the data by selecting the destination entity
- Create an export data job process which extracts what is there in the webform
- Perform some data massaging operation on the extracted file
- Run a calc rule making use of the data in webform
Once the user saves the data, a scheduled job is going to extract the data from PBCS.
Keep in mind that when using Hierarchy driven Smart List, select “Export Names” for Smart Lists.
Below given is the output after executing this job.
I’m using Groovy (installed on a AWS instance) to perform some data massaging operation, which will help me run the Flex rule.
/* Define parameters */ param1=args[0] /* this is the Flex Org2Org extract file */ param2=args[1] /* this is the Orgcodes file */ // Add header to the extracted PBCS text file datafile = new FileReader(param1) datafile.readLine() File flexorgcodetxtfile = new File(param2) flexorgcodetxtfile.withWriter{ out -> datafile.each { line -> line.splitEachLine('\\|') { flexdat -> if(!flexdat[1].equals('#missing') && !flexdat[3].equals('_2')) out.println flexdat[0] + '|' + flexdat[1].replaceAll('_','')//+ '|' + flexdat[2] } } }
What I’m doing there is to get the Source and Destination entities that were submitted by users.
flexdat[1] will give the destination entity
flexdat[3] will give the status of OrgCheck
Line 13 I’m only processing the lines where there is a destination entity (smart list value) and if those are fresh new lines (fresh lines will not have a value for OrgCheck)
Here is how the file looks like after the groovy script is executed.
Once I get the source and destination entities I can now run a rule by passing the source entity and destination entity and check whether they both are same. If they are OrgCheck will be updated accordingly.
Once that is done, I’ll run the extract data job again, run another groovy script. This is done to run the flex rule on those entities where OrgCheck is 2.
/* Define parameters */ param1=args[0] /* this is the Flex Org2Org extract file */ param2=args[1] /* this is the Orgcodes file */ // Add header to the extracted PBCS text file datafile = new FileReader(param1) datafile.readLine() File flexorgcodetxtfile = new File(param2) flexorgcodetxtfile.withWriter{ out -> datafile.each { line -> line.splitEachLine('\\|') { flexdat -> if(flexdat[3].equals('_2')) out.println flexdat[0] + '|' + flexdat[1].replaceAll('_','') //+ '|' + flexdat[1].replaceAll('_','')+ '|' + flexdat[8] } } }
This time the groovy is only giving me source entities and destination entities which are not the same. I can do this in the previous Groovy itself (however my real world implementation was bit more evolved, I had to check the currencies whether they were same or not. So I had to run the extract twice and then perform the move operation.)
Here is how the file looks file after the execution.
Now to make this execute one execute only fresh lines, I had to move the processed items to a different placeholder.
Time to run the main rule which does the data transfer from source to destination entity.
Now some batch magic!!! (you can use powershell/shell script to do the same)
This is how my root directory looks.
Here is the property bat file.
@ECHO OFF REM ################################################################################ REM # REM # Script : HYC_Set_Environmental_Variables.bat REM # Author : Celvin Kattookaran - Original Version REM # Description : Bat file used to Build all BSO cube (Monthly Cube Build) REM # Date : July 19 2012 REM # ------------------------------------------------------------------------------ REM # Outputs : Returns 0 if successful, 1 if Error occurred REM # ------------------------------------------------------------------------------ REM # Updates : REM # Author Date Description REM # ------------------------------------------------------------------------------ REM # cv.kattookaran July 19 2012 Creation REM # REM # REM ################################################################################# REM ##################################################### REM ########## GLOBAL VARIABLES DEFINITION ############## REM ##################################################### REM # REM # ---------------------------------------------------------------------------------------------------------------------- REM # -- Main directories REM # ---------------------------------------------------------------------------------------------------------------------- SET BASE_DIR=C:\FlexProcess SET EPMAUTOMATELOC=C:\Oracle\EPM Automate SET EXPDIR=%BASE_DIR%\DataFiles SET GROOVYPATH=C:/Program Files (x86)/Groovy/Groovy-2.4.7 SET JAVA_HOME=C:\Program Files\Java\jre1.8.0_111\ REM # ---------------------------------------------------------------------------------------------------------------------- REM # -- Servers, Users, and Passwords REM # ---------------------------------------------------------------------------------------------------------------------- SET EPMENV=Prod SET pbcs_user=support@intekgrate.com SET pbcs_passfile=%BASE_DIR%/Properties/pbcspassword.epw SET pbcs_url=pbcs url SET pbcs_domain=domain REM # ---------------------------------------------------------------------------------------------------------------------- REM # -- Email Lists REM # ---------------------------------------------------------------------------------------------------------------------- SET ADMINEMAILLIST=ckattookaran@intekgrate.com;gkattookaran@intekgrate.com;
Here is the script in it’s full glory :).
echo off call "%~dp0..\..\Properties\HYP_Set_Environment_Variables.bat" echo on REM #---------------------------------------------------------------------------------------------------------------------- REM # -- File Definitions REM # ---------------------------------------------------------------------------------------------------------------------- REM # -- Script file definitions REM # ---------------------------------------------------------------------------------------------------------------------- REM # -- Main log file for script REM #--------- Create Log directories and append date stamp ------------- REM #------ Comments --------# REM # all functions start with : and a call is made to call the function SET SCRIPTS_DIR=%BASE_DIR%\Scripts SET FLEXLOG_DIR=%BASE_DIR%\Logs call :datestamp REM # new Log directory is made each time this script is executed and will be suffixed with date stamp and jobid MKDIR "%FLEXLOG_DIR%\%~n0_%MYDATE%\Logs" MKDIR "%FLEXLOG_DIR%\%~n0_%MYDATE%\Error_Logs" REM # This set the log file name to bat file name SET SCRIPTLOG="%FLEXLOG_DIR%\%~n0_%MYDATE%\Logs\%~n0.log" REM # This set the log file name to bat file name without extension SET MAINLOGFILENAME=%~n0 REM # This path is set so that all other batches can update log files in the newly created log directory SET FLEXLOG=%FLEXLOG_DIR%\%~n0_%MYDATE% SET FLEXERR_DIR=%FLEXLOG%\Error_Logs REM ######################################## REM # Please don't update below this point # REM ######################################## REM # Initialise Variables REM #returned value of script SET EC=0 REM ##################################################### REM ############# MAIN PART OF SCRIPT ################### REM ##################################################### setlocal EnableDelayedExpansion REM call EPMAutomate function to login, password is an encrypted call "%EPMAUTOMATELOC%\bin\epmautomate.bat" login %pbcs_user% %pbcs_passfile% %pbcs_url% %pbcs_domain% call :PBCSExtractFlex SET JAVA_HOME=C:\Program Files\Java\jre1.8.0_111 REM Call groovy script to prepare file call "%GROOVYPATH%\bin\groovy.bat" %SCRIPTS_DIR%\GroovyScripts\flexorg2orgcurr.groovy "%EXPDIR:\=/%/FlexExtracts/FlexOrg2OrgExtract.txt" "%EXPDIR:\=/%/FlexExtracts/FlexOrg2OrgCheck.txt" REM call groovy to extract destination and source entities from Data extract echo. >> %SCRIPTLOG% Echo ------------------------------------------------------------------ >> %SCRIPTLOG% Echo Start Org Check process >> %SCRIPTLOG% Echo ------------------------------------------------------------------ >> %SCRIPTLOG% echo. >> %SCRIPTLOG% call :runorgextprocess call :epmautoerrcheck if %ERRCOUNT% GTR 1 ( call :datestamp echo !DTSTMP! Error running Org check process. >> %SCRIPTLOG% call :exit_process ) else ( echo. >> %SCRIPTLOG% echo !DTSTMP! Org Check process successful. >> %SCRIPTLOG% REM call the extract again to get the entities after Org check to flex data call :PBCSExtractFlex SET JAVA_HOME=C:\Program Files\Java\jre1.8.0_111 REM call groovy to extract different destination and source entities only from Data extract call "%GROOVYPATH%\bin\groovy.bat" %SCRIPTS_DIR%\GroovyScripts\flexorg2orgmovelines.groovy "%EXPDIR:\=/%/FlexExtracts/FlexOrg2OrgExtract.txt" "%EXPDIR:\=/%/FlexExtracts/FlexOrg2MoveLines.txt" echo. >> %SCRIPTLOG% Echo ------------------------------------------------------------------ >> %SCRIPTLOG% Echo Start Flex process >> %SCRIPTLOG% Echo ------------------------------------------------------------------ >> %SCRIPTLOG% echo. >> %SCRIPTLOG% call :runflexorgprocess call :epmautoerrcheck if %ERRCOUNT% GTR 1 ( call :datestamp echo !DTSTMP! Error running flex process. >> %SCRIPTLOG% call :exit_process ) else ( echo. >> %SCRIPTLOG% call :datestamp echo !DTSTMP! **** End Flex process......................... >> %SCRIPTLOG% echo !DTSTMP! Flex process Completed successfuly with exit code=%EC% >> %SCRIPTLOG% REM call %BASE_DIR%\Exes\sendmail.vbs "%BASE_DIR%\mail\SuccessEmail.txt" %SCRIPTLOG% "PBCS %EPMENV% - Flex process was successful" %ADMINEMAILLIST% ) ) GOTO EOF :unzip call "%BASE_DIR%\Exes\7za.exe" e %1 -o%2 -r -aoa GOTO EOF :preparedownloadfile REM Delete the existing files call :remove_file "%~1\%~2" call :remove_file "%~1\%~3" REM Rename the file to Add_Remove_Users.csv REM Any file which has ExportedData in it will be renamed to Add_Remove_Users IF /I %~4 ==data ( FOR %%F IN ("%~1\*ExportedData*") DO ( MOVE /y "%%F" "%~1\%~3" ) ) ELSE ( FOR %%F IN ("%~1\*ExportedMetadata*") DO ( MOVE /y "%%F" "%~1\%~3" ) ) GOTO EOF :move_file move /y %1 %2 GOTO EOF :PBCSExtractFlex call :datestamp echo !DTSTMP! **** Start Flex data extract process......................... >> %SCRIPTLOG% echo. >> %SCRIPTLOG% Echo ------------------------------------------------------------------ >> %SCRIPTLOG% Echo Extracting Flex data >> %SCRIPTLOG% Echo ------------------------------------------------------------------ >> %SCRIPTLOG% echo. >> %SCRIPTLOG% call :datestamp echo !DTSTMP! Start extract data process......................... >> %SCRIPTLOG% call "%EPMAUTOMATELOC%\bin\epmautomate.bat" exportdata "EXP_Flex" IF %ERRORLEVEL% NEQ 0 goto :exit_process REM Call EPMAutomate function to download the file from ZBB call "%EPMAUTOMATELOC%\bin\epmautomate.bat" downloadfile "EXP_Flex.zip" IF %ERRORLEVEL% NEQ 0 goto :exit_process REM Move the downloaded file to the supporting file location call :move_file EXP_Flex.zip "%EXPDIR%\FlexExtracts" REM unzip the package call :unzip "%EXPDIR%\FlexExtracts\EXP_Flex.zip" "%EXPDIR%\FlexExtracts" call :preparedownloadfile "%EXPDIR%\FlexExtracts" "EXP_Flex.zip" "FlexOrg2OrgExtract.txt" "data" call :datestamp echo !DTSTMP! Flex data extract for was successful......................... >> %SCRIPTLOG% GOTO EOF :datestamp REM # ---------------------------------------------------------------------------------------------------------------------- REM # -- Date Stamp definition -- REM # ---------------------------------------------------------------------------------------------------------------------- for /f "tokens=1,2,3,4,5,6* delims=," %%i in ('call "%BASE_DIR%\Exes\date.exe" +"%%Y,%%b,%%d,%%H,%%M,%%S"') do set YR=%%i& set MTH=%%j& set DAY=%%k& set HR=%%l& set MIN=%%m&set SEC=%%n SET MYDATE=%YR%_%MTH%_%DAY%_%HR%%MIN% SET DTSTMP=%YR% %MTH% %DAY% %HR%:%MIN%:%SEC% GOTO EOF :runorgextprocess REM delete epmautomate log files forfiles /m *.log /c "cmd /c del /q @file" Setlocal EnableDelayedExpansion for /f "tokens=1,2,3,4,5,6* delims=|" %%i in (%EXPDIR:\=/%/FlexExtracts/FlexOrg2OrgCheck.txt) do ( REM %%i is Source OrgCode REM %%j is Destination OrgCode REM %%k is LineItem set "lock=!temp!\epmwait!random!.lock" start "" cmd /c 9>"!lock!" call ZBB_FlexProcess_EPMAuto4Args.bat "Flex OrgCheck" "srcent=%%i" "destent=%%j" %SCRIPTLOG% ) endlocal call :epmwait GOTO EOF :runflexorgprocess REM delete epmautomate log files forfiles /m *.log /c "cmd /c del /q @file" Setlocal EnableDelayedExpansion for /f "tokens=1,2,3,4,5,6* delims=|" %%i in (%EXPDIR:\=/%/FlexExtracts/FlexOrg2MoveLines.txt) do ( REM %%i is Source OrgCode REM %%j is Destination OrgCode REM %%k is LineItem set "lock=!temp!\epmwait!random!.lock" start "" cmd /c 9>"!lock!" call ZBB_FlexProcess_EPMAuto4Args.bat "Flex Org2Org" "srcent=%%i" "destent=%%j" %SCRIPTLOG% ) endlocal call :epmwait GOTO EOF :epmautoerrcheck SET ERRCOUNT=0 for /f "delims=" %%i in ('dir /B "*.log"') do ( SET /A ERRCOUNT+=1 ECHO. >> %SCRIPTLOG% findstr /c:"EPMAT" "%%i" >> %SCRIPTLOG% ECHO. >> %SCRIPTLOG% ) GOTO EOF :remove_file If EXIST %1 ( echo "* File %1 found for deleting. Continue with Delete" DEL /Q %1 ) else ( echo "* File %1 can not be found for deleting. Continue process." ) GOTO EOF :exit_process REM # ---------------------------------------------------------------------- REM # -- Exit process Definition -- REM # ---------------------------------------------------------------------- REM Error in process, returned value=2 (process completed with Errors) call :datestamp echo !DTSTMP! Errors in process, Return code=1......................... >> %SCRIPTLOG% SET EC=1 call :datestamp echo !DTSTMP! process Completed with Errors with error code=%EC% >> %SCRIPTLOG% REM call %BASE_DIR%\Exes\sendmail.vbs "%BASE_DIR%\mail\ErrorEmail.txt" %SCRIPTLOG% "PBCS %EPMENV% - Flex process failed" %ADMINEMAILLIST% exit GOTO EOF :epmwait timeout /t 2 /nobreak >NUL for %%A in ("%temp%\epmwait*") do ( ( rem nothing ) 9>%%~fA || goto :epmwait ) 2>nul :delete ::delete the lock files del "%temp%\*.lock" goto :EOF :EOF
Now I do admit that it is a complicated batch process (and a simple functionality delivered in a complicated way, hey Client is the king, isn’t it 😉 )
If you closely look at the video (and the batch file), I’m running calc scripts parallel. I did test this and found that I cannot execute more than 50 rules at the same time. So I had to instruct our client not to enter more than 50 lines in between executions.
Also I had to make the batch script wait till all 50 lines are executed and that is achieved using the epmwait function.
Here is what my log file looks like.
Here is the video of me doing this live.
Next in the series will be how to avoid such a lengthy batch script and do this all in Groovy.
Groovy!
Next is going to all Groovy 🙂