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

1. Allow user to save the data by selecting the destination entity
2. Create an export data job process which extracts what is there in the webform
3. Perform some data massaging operation on the extracted file
4. 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.