1. Implementation Guide
  2. Extensibility
  3. Programmatic Extensibility of RPASCE Through Innovation Workbench
  4. RPASCE Special Expression - execplsql
  5. Examples

Examples

Consider the PL/SQL function SUM present in the package RP_CUSTOM_PKG. To execute the SUM function in the RPASCE application batch, first upload RP_CUSTOM_PKG as described in the section Uploading Custom PL/SQL Packages. The PL/SQL function SUM is declared as below in the package RP_CUSTOM_PKG. 

function SUM (lhsMeas IN VARCHAR2, rhsMeas1 IN VARCHAR2, rhsMeas2 IN VARCHAR2) return number;

Here is a sample definition of the SUM function that adds 2 measures and writes the result to a third measure. Note that the measure lhsMeas is an IN type argument although the function SUM updates it. The measure lhsMeas must be marked as a customer managed measure as described in the Measure Properties subsection of the RPASCE Configuration Tools Changes section. 

    FUNCTION sum (
        lhsmeas  IN VARCHAR2,
        rhsmeas1 IN VARCHAR2,
        rhsmeas2 IN VARCHAR2
    ) RETURN NUMBER IS
-- EXPR 1: lhsMeas = rhsMeas2 + rhsMeas1

        na_ut_lhsmeas  BINARY_DOUBLE := cell_dbl(lhsmeas, NULL);
        na_ut_rhsmeas1 BINARY_DOUBLE := cell_dbl(rhsmeas1, NULL);
        na_ut_rhsmeas2 BINARY_DOUBLE := cell_dbl(rhsmeas2, NULL);
        lhsfactgroup   VARCHAR2(4000);
        rhs1factgroup  VARCHAR2(4000);
        rhs2factgroup  VARCHAR2(4000);
        lhsfacttable   VARCHAR2(4000);
        rhs1facttable  VARCHAR2(4000);
        rhs2facttable  VARCHAR2(4000);
        stmt           VARCHAR2(8000);
    BEGIN
        rp_g_common_pkg.clear_facts(varchar2_table(lhsmeas));
        SELECT
            fact_group
        INTO lhsfactgroup
        FROM
            rp_g_fact_info_md
        WHERE
            fact_name = lhsmeas;

        SELECT
            fact_group
        INTO rhs1factgroup
        FROM
            rp_g_fact_info_md
        WHERE
            fact_name = rhsmeas1;

        SELECT
            fact_group
        INTO rhs2factgroup
        FROM
            rp_g_fact_info_md
        WHERE
            fact_name = rhsmeas2;

        lhsfacttable := 'rp_g_'
                        || lhsfactgroup
                        || '_ft';
        rhs1facttable := 'rp_g_'
                         || rhs1factgroup
                         || '_ft';
        rhs2facttable := 'rp_g_'
                         || rhs2factgroup
                         || '_ft';
        na_ut_lhsmeas := ( na_ut_rhsmeas2 + na_ut_rhsmeas1 );

        stmt := 'MERGE INTO '
                || lhsfacttable
                || ' lhs
    USING (
              SELECT
                  ( coalesce(rhsft01.partition_id, rhsft02.partition_id) )               AS partition_id,
                  ( coalesce(rhsft01.dept_id, rhsft02.dept_id) )                           AS dept_id,
                  ( coalesce(rhsft01.stor_id, rhsft02.stor_id) )                           AS stor_id,
                  ( ( coalesce(rhsft02.'
                || rhsmeas2
                || ', '
                || na_ut_rhsmeas2
                || ') + coalesce(rhsft01.'
                || rhsmeas1
                || ', '
                || na_ut_rhsmeas1
                || ') ) ) AS '
                || lhsmeas
                || '
              FROM
                  (
                      SELECT
                          partition_id,
                          dept_id,
                          stor_id,
                        '
                || rhsmeas1
                || '
                    FROM
                       '
                || rhs1facttable
                || '
                  ) rhsft01
                  FULL OUTER JOIN (
                      SELECT
                          partition_id,
                          dept_id,
                          stor_id,
                          '
                || rhsmeas2
                || '
                      FROM
                          '
                || rhs2facttable
                || '
                  ) rhsft02 ON rhsft01.partition_id = rhsft02.partition_id
                               AND rhsft01.dept_id = rhsft02.dept_id
                               AND rhsft01.stor_id = rhsft02.stor_id
          )
    rhs_final ON ( lhs.partition_id = rhs_final.partition_id
                   AND lhs.dept_id = rhs_final.dept_id
                   AND lhs.stor_id = rhs_final.stor_id )
    WHEN MATCHED THEN UPDATE
    SET lhs.'
                || lhsmeas
                || '= nullif(rhs_final.'
                || lhsmeas
                || ', '
                || na_ut_lhsmeas
                || ') DELETE
    WHERE
        rhs_final.'
                || lhsmeas
                || ' =  '
                || na_ut_lhsmeas
                || '
    WHEN NOT MATCHED THEN
    INSERT (
        lhs.partition_id,
        lhs.dept_id,
        lhs.stor_id,
        lhs.'
                || lhsmeas
                || ' )
    VALUES
        ( rhs_final.partition_id,
          rhs_final.dept_id,
          rhs_final.stor_id,
        nullif(rhs_final.'
                || lhsmeas
                || ', '
                || na_ut_lhsmeas
                || ') )
    WHERE
        rhs_final.'
                || lhsmeas
                || ' != '
                || na_ut_lhsmeas;

        dbms_output.put_line(stmt);
        EXECUTE IMMEDIATE stmt;
        COMMIT;
        RETURN 0;
    END sum;

Now to execute this SUM function from the application batch, add the rule below to application configuration as described in the Rules and Expressions subsection of RPASCE Configuration Tools Changes section. Add the rule group containing the rule to the batch control files as described in the section RPASCE Batch Control File Changes. Patch the application with the updated configuration and batch control files. 

drdvsrcti<-execplsql("RP_CUSTOM_PKG","sum",drdvsrctt, adhdlcratet, add2locopnd)

Here all 3 measures are placeholder scalar string measures that will point to the actual real measures that are being summed.

In this example, the input scalar measures are mapped as follows:

  • drdvsrctt:    lpwpsellthrmn - dept_stor - customer managed (LHS measure)

    Label: Wp Sell Thru R % Min Threshold

  • adhdlcratet:  lpwprtnmn   - dept_stor (RHS1)

    Label: Wp Returns R % Min Threshold

  • add2locopnd:  lpwprtnmx   - dept_stor (RHS2)

    Label: Wp Returns R % Max Threshold

Alternately, the rule could have been configured as below. However, that would mean that it is not possible to change the input measures as part of the batch. It will need a patch to update the input measures to the SUM procedure.

Note:

The measures are in quotes as they are passed to PL/SQL as string constants. If the quotes are missing, then RPASCE will throw an error indicating that it is not possible to invoke execplsql using non-scalar measures. 
drdvsrcti<-execplsql("RP_CUSTOM_PKG","sum",'lpwpsellthrmn' , 'lpwprtnmn' ,'lpwprtnmx' )

Execute this rule group through batch and build a measure analysis workbook with the involved measures. It can then be verified that the SUM evaluated correctly.

Measure Analysis Workbook

If measure lhsMeas is not specified as a customer-managed measure in the application configuration, then the error below will be thrown when execplsql is evaluated. 

<E OCI_ERROR - (1031):
<E SQL Sid 'rpas_iw_conn' ORA-01031: insufficient privileges

The following examples demonstrate execplsql and how a special expression can invoke PL/SQL with a variable number and type of input arguments. 

 intscalar01<-execplsql("RP_CUSTOM_PKG","custom_procedure1","dvsn", true, 1, 1)
 intscalar01<-execplsql("RP_CUSTOM_PKG","custom_procedure2","dvsn", 1123.5813, 23, -1)
 intscalar01<-execplsql("RP_CUSTOM_PKG","custom_function1","dvsn", true, 1)
 intscalar01<-execplsql("RP_CUSTOM_PKG","custom_function2","dvsn", 1)
 intscalar01<-execplsql("RP_CUSTOM_PKG","custom_function2",strscalar1, intscalar02)
 intscalar01<-execplsql("RP_CUSTOM_PKG","custom_procedure3","dvsn", datescalar2, 1, 1)

The PL/SQL counterparts are defined, through very simple demonstration code, in the example custom package below.

rp_custom_pdg.pkb

create or replace package body RP_CUSTOM_PKG is

procedure custom_procedure1(arg1 in varchar2, arg2 in CHAR, arg3 in number, ret out number )
is
begin
   ret:=23;
end custom_procedure1;

procedure custom_procedure2(arg1 in varchar2, arg2 in BINARY_DOUBLE, arg3 in number, ret out number )
is
begin
   ret:=23;
end custom_procedure2;

procedure custom_procedure3(arg1 in varchar2, arg2 in BINARY_DOUBLE, arg3 in timestamp, ret out number )
is
begin
  dbms_output.put_line('arg3: ' || arg3);
   ret:=23;
end custom_procedure3;

function custom_function1(arg1 in varchar2, arg2 in CHAR, arg3 in number) return number
is
 ret integer;
begin
   ret:=23;
   return ret;
end custom_function1;

function custom_function2(arg1 in varchar2, arg3 in number) return number
is
  ret number;
begin
   ret:=23;
   return ret;
end custom_function2;


function SUM
(lhsMeas IN VARCHAR2,
rhsMeas1 IN VARCHAR2,
rhsMeas2 IN VARCHAR2) return number
is
-- EXPR 1: lhsMeas = rhsMeas2 + rhsMeas1

    na_ut_lhsMeas  BINARY_DOUBLE := cell_dbl(lhsMeas, NULL);
    na_ut_rhsMeas1 BINARY_DOUBLE := cell_dbl(rhsMeas1, NULL);
    na_ut_rhsMeas2 BINARY_DOUBLE := cell_dbl(rhsMeas2, NULL);
    lhsFactGroup varchar2(4000);
    rhs1FactGroup varchar2(4000);
    rhs2FactGroup varchar2(4000);
    lhsFactTable varchar2(4000);
    rhs1FactTable varchar2(4000);
    rhs2FactTable varchar2(4000);
    stmt varchar2(8000);
BEGIN

    rp_g_common_pkg.clear_facts(varchar2_table(lhsMeas));
    select fact_group into lhsFactGroup from RP_G_FACT_INFO_MD where FACT_NAME = lhsMeas;
    select fact_group into rhs1FactGroup from RP_G_FACT_INFO_MD where FACT_NAME = rhsMeas1;
    select fact_group into rhs2FactGroup from RP_G_FACT_INFO_MD where FACT_NAME = rhsMeas2;
    lhsFactTable := 'rp_g_' || lhsFactGroup || '_ft';
    rhs1FactTable := 'rp_g_' || rhs1FactGroup || '_ft';
    rhs2FactTable := 'rp_g_' || rhs2FactGroup || '_ft';
    na_ut_lhsMeas := ( na_ut_rhsMeas2 + na_ut_rhsMeas1 );

    -- UPDATE rp_g_fact_info_md
    -- SET
    --     table_na =
    --         CASE lower(fact_name)
    --             WHEN 'b' THEN
    --                 to_char(na_ut_lhsMeas)
    --         END
    -- WHERE
    --     lower(fact_name) IN ( lhsMeas );

    stmt :=      'MERGE INTO ' || lhsFactTable || ' lhs
    USING (
              SELECT
                  ( coalesce(rhsft01.partition_id, rhsft02.partition_id) )               AS partition_id,
                  ( coalesce(rhsft01.dept_id, rhsft02.dept_id) )                           AS dept_id,
                  ( coalesce(rhsft01.stor_id, rhsft02.stor_id) )                           AS stor_id,
                  ( ( coalesce(rhsft02.' || rhsMeas2 || ', ' || na_ut_rhsMeas2 || ') + coalesce(rhsft01.' || rhsMeas1 || ', ' || na_ut_rhsMeas1 || ') ) ) AS '|| lhsMeas||'
              FROM
                  (
                      SELECT
                          partition_id,
                          dept_id,
                          stor_id,
                        '||  rhsMeas1 ||'
                    FROM
                       ' || rhs1FactTable || '
                  ) rhsft01
                  FULL OUTER JOIN (
                      SELECT
                          partition_id,
                          dept_id,
                          stor_id,
                          ' || rhsMeas2 || '
                      FROM
                          ' || rhs2FactTable || '
                  ) rhsft02 ON rhsft01.partition_id = rhsft02.partition_id
                               AND rhsft01.dept_id = rhsft02.dept_id
                               AND rhsft01.stor_id = rhsft02.stor_id
          )
    rhs_final ON ( lhs.partition_id = rhs_final.partition_id
                   AND lhs.dept_id = rhs_final.dept_id
                   AND lhs.stor_id = rhs_final.stor_id )
    WHEN MATCHED THEN UPDATE
    SET lhs.' || lhsMeas || '= nullif(rhs_final.' || lhsMeas || ', ' ||  na_ut_lhsMeas || ') DELETE
    WHERE
        rhs_final.' || lhsMeas || ' =  ' || na_ut_lhsMeas || '
    WHEN NOT MATCHED THEN
    INSERT (
        lhs.partition_id,
        lhs.dept_id,
        lhs.stor_id,
        lhs.' || lhsMeas || ' )
    VALUES
        ( rhs_final.partition_id,
          rhs_final.dept_id,
          rhs_final.stor_id,
        nullif(rhs_final.' || lhsMeas || ', ' || na_ut_lhsMeas || ') )
    WHERE
        rhs_final.' || lhsMeas || ' != ' || na_ut_lhsMeas ;


 DBMS_OUTPUT.PUT_LINE (stmt);

execute immediate stmt;

commit;

return 0;

END SUM;



end RP_CUSTOM_PKG;

rp_custom_pkg.pks

create or replace package RP_CUSTOM_PKG is

procedure custom_procedure1(arg1 in varchar2, arg2 in CHAR, arg3 in number, ret out number );
procedure custom_procedure2(arg1 in varchar2, arg2 in BINARY_DOUBLE, arg3 in number, ret out number );
procedure custom_procedure3(arg1 in varchar2, arg2 in BINARY_DOUBLE, arg3 in timestamp, ret out number );
function custom_function1(arg1 in varchar2, arg2 in CHAR, arg3 in number) return number ;
function custom_function2(arg1 in varchar2, arg3 in number) return number;
function SUM (lhsMeas IN VARCHAR2, rhsMeas1 IN VARCHAR2, rhsMeas2 IN VARCHAR2) return number;
end RP_CUSTOM_PKG;