1 2 Previous Next 22 Replies Latest reply: Jul 25, 2013 2:08 AM by JohnGoodwin RSS

    CDF 11.1.2.1

    DjSmeadly

      I have a sequence that I would like to have a CDF for it that I can use in an HBR to run as soon as it's launched. Basically this sequence is attached on multiple if not every web form. i'd like it to run on the save of the web form. That way an email is fired off everytime it runs. Although I looked through the oracle documentation, John Goodwin's blog, and other sites to try and understand the syntax and all i need to do to register the CDF.

       

      Can someone help me?

       

      Thanks,

        • 1. Re: CDF 11.1.2.1
          Celvin Kattookaran

          Check CDF_Email.zip here Oracle Essbase Sample Code

           

          Regards

           

           

          Celvin

          http://www.orahyplabs.com

          • 2. Re: CDF 11.1.2.1
            DjSmeadly

            Celvin,

             

            I have downloaded it and also set it up. Although the instructions are lacking. I am pretty confused. So I am still at a stand still.

             

            Thanks,

            • 3. Re: CDF 11.1.2.1
              JohnGoodwin

              Which CDF have you downloaded, at which point are you stuck.

               

              Cheers

               

              John

              http://john-goodwin.blogspot.com/

              • 4. Re: CDF 11.1.2.1
                _RahulS_

                Check this, hope it will give a little clue:

                Hyperion Planning and More...: How to register CDFs :

                • 5. Re: CDF 11.1.2.1
                  DjSmeadly

                  the CDF's are registered.

                  I am a little confused by the syntax of:

                  • RUNJAVA com.oracle.essbase.cdf.SetEmailProp ...
                  • @JaddToAlert ...
                  • RUNJAVA com.oracle.essbase.cdf.SendMail ...


                  the environment I will fully be testing on is 11.1.2.1 which uses HBRs. I am presently trying to test this on a calculation script in 11.1.2.3 and then I am going to try to pass it also in a CalcMgr Rule.

                   

                   

                  Do you think I will be able to use a gmail account to test this?
                  That is where I am at thus far.
                  This is example of a rule that I would like to test via calculation script with the information to send the email.

                   

                  /*STARTCOMPONENT:SCRIPT*/

                  SET UPDATECALC OFF;

                  SET CACHE HIGH;

                  SET CALCPARALLEL 2;

                  SET CALCTASKDIMS 2;

                  SET LOCKBLOCK HIGH;

                  SET FRMLBOTTOMUP ON;

                   

                  /*--------------------------------------------------------------------------*/

                  /* Standard Wages                                                                      */

                  /*--------------------------------------------------------------------------*/

                   

                  FIX({TMPA_Version})

                    FIX(@RELATIVE({TMPA_Entity},0),@RELATIVE("Labor_Class",0))

                      "Standard_Wages_Base"

                      (

                      IF(@ISMBR(&BudYr) AND @ISMBR(&BudMth) AND @ISMBR("Budget"))

                   

                  /*--------------------------------------------------------------------------*/

                  /* Headcount Driven Benefits and FUTA/SUTA                           */

                  /*--------------------------------------------------------------------------*/

                   

                    IF(@ISUDA(Job_Description,"Std_Emp") OR @ISUDA(Job_Description,"Inactive_Emp"))

                    "ST_Disability"=#missing;

                   

                      "WC";

                      "Post_Retirement";

                      "Group_Life";

                      "LT_Disability";

                      "FUTA";

                      "SUTA";

                      "Medical_Comp";

                       "Medical_Emp";

                   

                      "Dental_Comp";

                      "Dental_Emp";

                   

                      "Fidelity_$";

                      "Fidelity_Annual_Enrollment_$";

                      "Focused_Health_$";

                      "Pharmacy_$";

                      "Principal_Health_Vision_$";

                      "SHPS_Flex_Plan_$";

                      "EAP_$";

                      "Express_Scripts_$";

                      "Gas_Price";

                      "Vehicle_Lease";

                    ENDIF

                   

                  /*--------------------------------------------------------------------------*/

                  /* VWF Wages                                                                             */

                  /*--------------------------------------------------------------------------*/

                    IF(@ISUDA(Job_Description,"VWF_Emp"))

                      "VWF_Wages_Base";

                      "VWF_Wages_Premium";

                    ENDIF

                   

                   

                  /*--------------------------------------------------------------------------*/

                  /* ST_Disability                                                                          */

                  /*--------------------------------------------------------------------------*/

                    IF(@ISUDA(Job_Description,"ST_Eligible") OR @ISUDA(Job_Description,"Inactive_Emp"))

                      "ST_Disability";

                    ENDIF

                   

                  /*--------------------------------------------------------------------------*/

                  /* Temp Wages                                                                            */

                  /*--------------------------------------------------------------------------*/

                    IF(@ISUDA(Job_Description,"Tmp_Emp"))

                      "Temp_Wages_Base";

                      "Temp_Wages_Premium";

                    ENDIF

                  /*--------------------------------------------------------------------------*/

                  /* SGNA Wages                                                                           */

                  /*--------------------------------------------------------------------------*/

                    IF(@ISUDA(Job_Description,"SGA_Wage"))

                      "SGNA_Wages_Base";

                      "SGNA_Wages_Premium";

                    ENDIF

                   

                    ELSEIF(@ISMBR(&FcstYr) AND @ISMBR(&FcstMth) AND @ISMBR("Forecast"))

                   

                  /*--------------------------------------------------------------------------*/

                  /* Headcount Driven Benefits and FUTA/SUTA                           */

                  /*--------------------------------------------------------------------------*/

                   

                    IF(@ISUDA(Job_Description,"Std_Emp") OR @ISUDA(Job_Description,"Inactive_Emp"))

                    "ST_Disability"=#missing;

                   

                      "WC";

                      "Post_Retirement";

                      "Group_Life";

                      "LT_Disability";

                      "FUTA";

                      "SUTA";

                      "Medical_Comp";

                       "Medical_Emp";

                   

                      "Dental_Comp";

                      "Dental_Emp";

                   

                      "Fidelity_$";

                      "Fidelity_Annual_Enrollment_$";

                      "Focused_Health_$";

                      "Pharmacy_$";

                      "Principal_Health_Vision_$";

                      "SHPS_Flex_Plan_$";

                      "EAP_$";

                      "Express_Scripts_$";

                      "Gas_Price";

                      "Vehicle_Lease";

                    ENDIF

                   

                  /*--------------------------------------------------------------------------*/

                  /* VWF Wages                                                                             */

                  /*--------------------------------------------------------------------------*/

                    IF(@ISUDA(Job_Description,"VWF_Emp"))

                      "VWF_Wages_Base";

                      "VWF_Wages_Premium";

                    ENDIF

                   

                   

                  /*--------------------------------------------------------------------------*/

                  /* ST_Disability                                                                          */

                  /*--------------------------------------------------------------------------*/

                    IF(@ISUDA(Job_Description,"ST_Eligible") OR @ISUDA(Job_Description,"Inactive_Emp"))

                      "ST_Disability";

                    ENDIF

                   

                  /*--------------------------------------------------------------------------*/

                  /* Temp Wages                                                                            */

                  /*--------------------------------------------------------------------------*/

                    IF(@ISUDA(Job_Description,"Tmp_Emp"))

                      "Temp_Wages_Base";

                      "Temp_Wages_Premium";

                    ENDIF

                  /*--------------------------------------------------------------------------*/

                  /* SGNA Wages                                                                           */

                  /*--------------------------------------------------------------------------*/

                    IF(@ISUDA(Job_Description,"SGA_Wage"))

                      "SGNA_Wages_Base";

                      "SGNA_Wages_Premium";

                    ENDIF

                    ENDIF)

                    ENDFIX

                  ENDFIX

                   

                  /*--------------------------------------------------------------------------*/

                  /* Consolidate Sparse Dimensions                                               */

                  /*--------------------------------------------------------------------------*/

                    FIX({TMPA_Version})

                    FIX("Budget", &BudYr, "Apr":"May")

                      AGG("Entity","Job_Description","Labor_Class");

                    ENDFIX

                    FIX("Forecast", &FcstYr, &FcstMth)

                    AGG("Entity","Job_Description","Labor_Class");

                    ENDFIX

                          ENDFIX

                  /*ENDCOMPONENT*/

                   

                   

                  If you can help me with adding the necessary components to make this work and test it out right in a calculation script that would be great. I know any global variables will need to be hardcoded.

                   

                  Thanks,

                  • 6. Re: CDF 11.1.2.1
                    DjSmeadly

                    John,

                    Sorry I have downloaded the CDF_Email.zip.

                    I have done as per all the instructions.

                    Although when i try to use the

                    • @JaddtoAlert
                    • @JsetEmailProp
                    • @JsendMail

                     

                    The syntax pops up but the calculation script will not validate.

                    i.e.operator expected after [@JsendMail]

                    i.e. operator expected after [@JaddtoAlert]

                    • 7. Re: CDF 11.1.2.1
                      Celvin Kattookaran

                      Did you update the udf.policy file? That error is because the function is not registered correctly.

                       

                      Regards

                       

                       

                      Celvin

                      http://www.orahyplabs.com

                      • 8. Re: CDF 11.1.2.1
                        DjSmeadly

                        Celvin,

                         

                        I updated both udf.policy files located here

                         

                        • D:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseServer\java\udf.policy

                        also the one located here:

                        • D:\Oracle\Middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\java\udf.policy.

                          I am doing a full services stop and start.

                          I will report back when services are up and let you know if that helped.



                        • 9. Re: CDF 11.1.2.1
                          DjSmeadly

                          Same thing after restarting the services. If you have an email I will gladly send you the information I can have and you can tell me if I have done something wrong.

                           

                          Thanks,

                          • 10. Re: CDF 11.1.2.1
                            DjSmeadly

                            Status update:

                             

                            I tried to find the essbase log that had the verification of the functions getting registered (no luck in finding). Although after uncommenting a certain line in both udf.policy files. and then bouncing the services the calculation script is validating.

                             

                            I am not sure if everything is working as it should or if now it's an issue of my email smtp. here is the calc script below:

                             

                             

                            //ESS_LOCALE English_UnitedStates.Latin1@Binary

                            /*STARTCOMPONENT:SCRIPT*/

                            SET UPDATECALC OFF;

                            SET CACHE HIGH;

                            SET CALCPARALLEL 2;

                            SET CALCTASKDIMS 2;

                            SET LOCKBLOCK HIGH;

                            SET FRMLBOTTOMUP ON;

                             

                             

                             

                             

                            RUNJAVA com.hyperion.essbase.calculator.EssbaseAlert "smtp.gmx.com" "djsmeadly@gmx.co.uk" "djsmeadly@gmx.co.uk" "" "" "Mail Subject" "Mail Body" "";

                            RUNJAVA com.oracle.essbase.cdf.SetEmailProp "target:target1" "subject:East % Sales Variance"  "css:C:/target1.css";

                            RUNJAVA com.oracle.essbase.cdf.SetEmailProp "target:target2" "subject:West % Sales Variance" "css:D:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseServer\java\udf\Email\target2.css";

                            RUNJAVA com.oracle.essbase.cdf.SetEmailProp "target:target3" "subject:South % Sales Variance" "css:D:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseServer\java\udf\Email\target1.css";

                            RUNJAVA com.oracle.essbase.cdf.SetEmailProp "target:target4" "subject:Central % Sales Variance";

                            RUNJAVA com.oracle.essbase.cdf.SetEmailProp "target:all" "title:<center><b>% Sales Variance</b></center>" "header:Market,Product,Period,Variance %" ;

                            /*--------------------------------------------------------------------------*/

                            /* Standard Wages*/

                            /*--------------------------------------------------------------------------*/

                             

                             

                            FIX("Working")

                              FIX(@RELATIVE("MI_No_CC",0),@RELATIVE("Labor_Class",0))

                             

                                "Standard_Wages_Base"

                                (

                                IF(@ISMBR(&BudYr) AND @ISMBR(&BudMth) AND @ISMBR("Budget"))

                              @JaddToAlert("target1",@List(@NAME(@currmbr("Entity")),@ALIAS(@currmbr("Version")),@NAME(@currmbr("Years"))),@RELATIVE("Labor_Class",0));

                             

                            /*--------------------------------------------------------------------------*/

                            /* Headcount Driven Benefits and FUTA/SUTA */

                            /*--------------------------------------------------------------------------*/

                             

                             

                              IF(@ISUDA(Job_Description,"Std_Emp") OR @ISUDA

                            (Job_Description,"Inactive_Emp"))

                             

                              "ST_Disability"=#missing;

                             

                             

                                "WC";

                                "Post_Retirement";

                                "Group_Life";

                                "LT_Disability";

                                "FUTA";

                                "SUTA";

                                "Medical_Comp";

                                "Medical_Emp";

                             

                             

                                "Dental_Comp";

                                "Dental_Emp";

                             

                             

                                "Fidelity_$";

                                "Fidelity_Annual_Enrollment_$";

                                "Focused_Health_$";

                                "Pharmacy_$";

                                "Principal_Health_Vision_$";

                                "SHPS_Flex_Plan_$";

                                "EAP_$";

                                "Express_Scripts_$";

                                "Gas_Price";

                                "Vehicle_Lease";

                             

                              ENDIF

                             

                             

                            /*--------------------------------------------------------------------------*/

                            /* VWF Wages */

                            /*--------------------------------------------------------------------------*/

                              IF(@ISUDA(Job_Description,"VWF_Emp"))

                                "VWF_Wages_Base";

                                "VWF_Wages_Premium";

                              ENDIF

                             

                             

                             

                             

                            /*--------------------------------------------------------------------------*/

                            /* ST_Disability */

                            /*--------------------------------------------------------------------------*/

                              IF(@ISUDA(Job_Description,"ST_Eligible") OR @ISUDA

                            (Job_Description,"Inactive_Emp"))

                                "ST_Disability";

                              ENDIF

                             

                             

                            /*--------------------------------------------------------------------------*/

                            /* Temp Wages*/

                            /*--------------------------------------------------------------------------*/

                              IF(@ISUDA(Job_Description,"Tmp_Emp"))

                                "Temp_Wages_Base";

                                "Temp_Wages_Premium";

                              ENDIF

                            /*--------------------------------------------------------------------------*/

                            /* SGNA Wages */

                            /*--------------------------------------------------------------------------*/

                              IF(@ISUDA(Job_Description,"SGA_Wage"))

                                "SGNA_Wages_Base";

                                "SGNA_Wages_Premium";

                              ENDIF

                             

                             

                              ELSEIF(@ISMBR(&FcstYr) AND @ISMBR(&FcstMth) AND @ISMBR("Forecast"))

                              @JaddToAlert("target2",@List(@NAME(@currmbr("Entity")),@ALIAS(@currmbr("Version")),@NAME(@currmbr("Years"))),@RELATIVE("Labor_Class",0));

                            /*--------------------------------------------------------------------------*/

                            /* Headcount Driven Benefits and FUTA/SUTA*/

                            /*--------------------------------------------------------------------------*/

                             

                             

                              IF(@ISUDA(Job_Description,"Std_Emp") OR @ISUDA

                            (Job_Description,"Inactive_Emp"))

                              "ST_Disability"=#missing;

                             

                                "WC";

                                "Post_Retirement";

                                "Group_Life";

                                "LT_Disability";

                                "FUTA";

                                "SUTA";

                                "Medical_Comp";

                                "Medical_Emp";

                             

                             

                                "Dental_Comp";

                                "Dental_Emp";

                             

                             

                                "Fidelity_$";

                                "Fidelity_Annual_Enrollment_$";

                                "Focused_Health_$";

                                "Pharmacy_$";

                                "Principal_Health_Vision_$";

                                "SHPS_Flex_Plan_$";

                                "EAP_$";

                                "Express_Scripts_$";

                                "Gas_Price";

                                "Vehicle_Lease";

                              ENDIF

                             

                             

                            /*--------------------------------------------------------------------------*/

                            /* VWF Wages*/

                            /*--------------------------------------------------------------------------*/

                              IF(@ISUDA(Job_Description,"VWF_Emp"))

                                "VWF_Wages_Base";

                                "VWF_Wages_Premium";

                              ENDIF

                             

                             

                             

                             

                            /*--------------------------------------------------------------------------*/

                            /* ST_Disability */

                            /*--------------------------------------------------------------------------*/

                              IF(@ISUDA(Job_Description,"ST_Eligible") OR @ISUDA

                            (Job_Description,"Inactive_Emp"))

                                "ST_Disability";

                              ENDIF

                             

                             

                            /*--------------------------------------------------------------------------*/

                            /* Temp Wages*/

                            /*--------------------------------------------------------------------------*/

                              IF(@ISUDA(Job_Description,"Tmp_Emp"))

                                "Temp_Wages_Base";

                                "Temp_Wages_Premium";

                              ENDIF

                            /*--------------------------------------------------------------------------*/

                            /* SGNA Wages */

                            /*--------------------------------------------------------------------------*/

                              IF(@ISUDA(Job_Description,"SGA_Wage"))

                                "SGNA_Wages_Base";

                                "SGNA_Wages_Premium";

                              ENDIF

                              ENDIF)

                              ENDFIX

                            ENDFIX

                             

                             

                            /*--------------------------------------------------------------------------*/

                            /* Consolidate Sparse Dimensions*/

                            /*--------------------------------------------------------------------------*/

                              FIX("Working")

                              FIX("Budget", &BudYr, "Apr":"May")

                                AGG("Entity","Job_Description","Labor_Class");

                              ENDFIX

                              FIX("Forecast", &FcstYr, &FcstMth)

                              AGG("Entity","Job_Description","Labor_Class");

                              ENDFIX

                                    ENDFIX

                             

                             

                             

                             

                            RUNJAVA com.oracle.essbase.cdf.SendMail target:target1 server:"smtp.gmx.com" from:"djsmeadly@gmx.co.uk" to:"djsmeadly@gmx.co.uk";

                            RUNJAVA com.oracle.essbase.cdf.SendMail target:target2 server:"smtp.gmx.com" from:"djsmeadly@gmx.co.uk" to:"djsmeadly@gmx.co.uk";

                            RUNJAVA com.oracle.essbase.cdf.SendMail target:target3 server:<servername> from:<from> to:<to>;

                            RUNJAVA com.oracle.essbase.cdf.SendMail target:target4 server:<servername> from:<from> to:<to>;

                            RUNJAVA com.oracle.essbase.cdf.SendMail server:<servername> from:<from> to:<to> "subject:mail attachement" "body:this is a test of the email attachement" "file:c:/console.log";

                            /*ENDCOMPONENT*/

                            • 11. Re: CDF 11.1.2.1
                              JohnGoodwin

                              Here is what I did on 11.1.2.1

                               

                              • Download CDF_Email.zip
                              • Extracted CDF_Email.jar to D:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseServer\java\udf
                              • Edited D:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseServer\java\udf.policy

                               

                              Added

                               

                              grant codeBase "file:${essbase.java.home}/udf/*" {

                                 permission java.security.AllPermission;

                              };

                               

                              Started Maxl session

                               

                              LOGIN admin password ON servername;

                               

                              /*

                              ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                              Register function JaddToAlert

                              ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                              */

                              CREATE OR REPLACE FUNCTION '@JaddToAlert'

                              AS 'com.oracle.essbase.cdf.AlertFunctions.addToeMailBody(String,String[],double[])'

                              SPEC '@JaddToAlert(targetName,stringArray,doubleArray)'

                              COMMENT '';

                               

                              /*

                              ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                              Register function JsetEmailProp

                              ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                              */

                              CREATE OR REPLACE FUNCTION '@JsetEmailProp'

                              AS 'com.oracle.essbase.cdf.AlertFunctions.setProperties(String[])'

                              SPEC '@JsetEmailProp(stringArray)'

                              COMMENT '';

                               

                              /*

                              ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                              Register function JsendMail

                              ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

                              */

                              CREATE OR REPLACE FUNCTION '@JsendMail'

                              AS 'com.oracle.essbase.cdf.SendMail.sendThisEmail(String[])'

                              SPEC '@JsendMail(listOfStrings)'

                              COMMENT '';

                               

                              logout;

                              exit;

                               

                              Restart Essbase, start up an application and check the app log.

                               

                              Please note that the registration of @JsendMail in RegisterEmailFunctions.msh did not work for me as it specifies

                               

                              CREATE OR REPLACE FUNCTION '@JsendMail'

                              AS 'com.hyperion.essbase.cdf.alert.SendMail.sendThisEmail(String[])'

                              SPEC '@JsendMail(listOfStrings)'

                              COMMENT '';

                               

                              This produced the following in the logs

                               

                              Wrong java method specification [com.hyperion.essbase.cdf.alert.SendMail.sendThisEmail(String[])] (function [@JsendMail]): [specified class not found]

                               

                              I updated it to

                               

                              CREATE OR REPLACE FUNCTION '@JsendMail'

                              AS 'com.oracle.essbase.cdf.SendMail.sendThisEmail(String[])'

                              SPEC '@JsendMail(listOfStrings)'

                              COMMENT '';

                               

                              and then function was correctly registered.

                               

                              External [GLOBAL] function [@JsendMail] registered OK

                               

                              Cheers

                               

                              John

                              http://john-goodwin.blogspot.com/

                              • 12. Re: CDF 11.1.2.1
                                DjSmeadly

                                John,

                                 

                                Do i need to updated any of the files below? I ask since i did but if not i will put them back to their origins:

                                 

                                 

                                • AlertFunctions.java
                                • OutputInfo.java
                                • SendMail.java
                                • SetEmailProp.java

                                 

                                Otherewise on the app log I am not getting any errors and it doesnt say anything about anything registered. I am looking at the log located here:D:\Oracle\Middleware\user_projects\epmsystem1\diagnostics\logs\essbase\essbase\app\xTMVA\xTMVA.LOG

                                • 13. Re: CDF 11.1.2.1
                                  JohnGoodwin

                                  No you shouldn't need any of the .java files as they are all compiled in CDF_Email.jar

                                   

                                  copying CDF_Email.jar to D:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseServer\java\udf should be good enough


                                  If the functions have been registered using Maxl or through EAS then once you start up the application and then check the application log it should have something like the following:


                                  External [GLOBAL] function [@JsendMail] registered OK


                                  If they have not been registered you would probably see errors when trying to run a calc script,

                                   

                                  Cheers


                                  John

                                  http://john-goodwin.blogspot.com/

                                  • 14. Re: CDF 11.1.2.1
                                    DjSmeadly

                                    John,

                                     

                                    They are registered a OK. Although I am confused if I remove the .java files. How does the calculation script know where to send the email via server, login, etc.?

                                     

                                    Thanks,

                                    1 2 Previous Next