Cross validation rules

1. What is Cross Validation Rules?

Cross-Validation rules define whether segment values of a particular segment can be combined with other segment values of another particular segment when new combinations are created. In other words, cross validation rules prevents the creation of invalid code combinations. It is applicable for Key Flexfields only.

2. Illustrate the appropriated need for Cross-Validation rules with example?

Let us take Accounting Flexfield for our example, we can use cross validation rules for achieving the below business requirements

1.     To ensure all balance sheet accounts to be associated only with the balance sheet cost center or corporate cost center

2.     To ensure that profit and loss accounts to be associated with specific cost centers other than corporate cost center.

3.     To restrict data entry of cost centers that is not legitimate for a specific company.

3.  A user trying to violate the cross validation rules while data entry, what happens and what are the possibilities?

The user will receive an error message given as part of the cross validation rules definition.

§  The error messages can be made very informative and tell the user exactly what is wrong with the flexfield combination.

§  The invalid segment can also be highlighted.  

4.  What Cross Validation Rules are made up of?

A cross validation rules is composed of one or more cross validation rules elements. There are two types of rule elements and they are “Include” and “Exclude”. Each cross validation rule must have at least one include rule element because cross validation rules exclude all the values unless they are specifically included via rule element. Exclude rule elements override include rule element.

5.  What is Oracle recommendation regarding rule elements definition?

We can accomplish our business requirement either by including specific ranges explicitly or by including all possible ranges and exclude the specific ranges. But the Oracle recommendation is

“One Include Rule Element which encompasses all possible values with several exclude cross validation rule elements”

6.  How do you define an all-encompassing include cross-validation rule element?

An all-encompassing include for numeric segments is “O” through “9”, while for alpha numeric segments it is from “ O” through “Z”. For Example, if our flexfield has three segments with First segment of two Numbers, Second of four characters and third segment of six characters then the all encompassing include is 00-0000-000000 through 99-ZZZZ-ZZZZZZ.

7.  What will be a blank value do in a cross validation rule element?

While defining the rule element, leaving the segment blank includes all possible values

8.  What is the use of error segment and error message?

 While defining a cross validation rule, we need to enter a mandatory error message that will appear if an enabled cross validation rule is being violated. Next we can choose to enter an error segment and an effective date range. The error segment will be highlighted if this particular cross validation rule is violated.

9.  What is the difference between disabling a cross validation rule vs deleting it?

The difference is when we think about reusing the cross validation rules i.e. deleted cross validation rule needs a redefinition while disabled cross validation doesn’t  need a redefinition just  a tick in check box is enough to re enable it.

10. Whether a single flexfield structure can have multiple cross validation rules?

Yes

11. What is the need for multiple cross validation rules?

Even though we can achieve cross validation across more than one segment via single cross validation rule, it is advisable to go for multiple cross validation rules so that a clear error message and error segment can be highlighted to the user which directly point to the specific issue. Moreover, using multiple simple cross validation rules is better than single complex cross validation rule.

12. When a combination is valid for a flexfield structure which has multiple cross validation rules?

Combination is valid only if they are in at least one include rule element and outside of all exclude elements.

13. What is the navigation to define a cross validation?

Responsibility: General Ledger Vision Operations

Navigation:  Setup : Financial : Flexfield : Key : Rules

REF cursor in PL/SQL with example

The Primary purpose of this post is to provide fair idea on the advanced concepts in PL/SQL like REF Cursor. We have given a try and hope to be useful for my audiences.

REF CURSOR

•          A ref cursor is a variable, defined as a cursor type, which will point to, or reference a cursor result.

•          To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. You can access this area through an explicit cursor, which names the work area, or through a cursor variable, which points to the work area. To create cursor variables, you define a REF CURSOR type, and then declare cursor variables of that type.

Syntax of the REF Cursor

Define a REF Cursor TYPE:

   TYPE ref_type_name IS REF CURSOR

    [RETURN {

             cursor_name%ROWTYPE           

            |ref_cursor_name%ROWTYPE

            |record_name%TYPE

            |record_type_name

            |db_table_name%ROWTYPE

            }

    ];

RETURN
specifies the data type of a cursor variable return value. You can use the %ROWTYPE attribute in the RETURN clause to provide a record type that represents a row in a database table or a row from a cursor or strongly typed cursor variable. You can use the %TYPE attribute to provide the datatype of a previously declared record.

Ø  cursor_name
An explicit cursor previously declared within the current scope.

Ø  ref_cursor_name
An ref cursor previously declared within the current scope.

Ø  record_name
A user-defined record previously declared within the current scope.

Ø  record_type_name

A user-defined record type that was defined using the data type specifies RECORD.

Ø  db_table_name
A database table or view, which must be accessible when the declaration is elaborated.

Ø  %ROWTYPEA record type that represents a row in a database table or a row fetched from a cursor or strongly typed cursor variable. Fields in the record and corresponding columns in the row have the same names and datatypes.

Ø  %TYPE
Provides the datatype of a previously declared user-defined record.

Ø  type_name
A user-defined cursor variable type that was defined as a REF CURSOR.

Cursor_variable_declaration:

    cursor_variable_name ref_type_name;

OPEN a REF cursor...

OPEN cursor_variable_name

 FOR select_statement;

/*To be sure it's not open already:*/

IF NOT cursor_variable_name%ISOPEN THEN

   OPEN cursor_variable_name FOR select_statement;

END IF;

Types of REF CURSOR

Strongly Typed: A REF CURSOR that specifies a specific return type

DECLARE

TYPE    EmpCurTyp IS REF CURSOR

RETURN  emp%ROWTYPE; -- strongly typed ref cursor

cursor1 EmpCurTyp;

BEGIN

NULL;

END;

Weakly Typed:  A REF CURSOR that does not specify the return type         

DECLARE

TYPE    EmpCurTyp IS REF CURSOR  -- Weakly typed ref cursor

cursor1 EmpCurTyp;

BEGIN

NULL;

END;

Three statements to control a cursor variable

·      OPEN-FOR

·      FETCH

·      CLOSE

1.     OPEN-FOR statements can open the same cursor variable for different queries. You need not close a cursor variable before reopening it. When you reopen a cursor variable for a different query, the previous query is lost.

2.     PL/SQL makes sure the return type of the cursor variable is compatible with the INTO clause of the FETCH statement.

Simple Example:

DECLARE

 TYPE    EmpCurTyp IS REF CURSOR

 RETURN  emp%ROWTYPE; -- strong cursor

 emp1    EmpCurTyp;

 PROCEDURE process_emp_cv (emp_cv IN EmpCurTyp) IS

  person emp%ROWTYPE;

 BEGIN

     DBMS_OUTPUT.PUT_LINE('-----');

     DBMS_OUTPUT.PUT_LINE('Here are the names from the result set:');

 LOOP

     FETCH emp_cv INTO person;

     EXIT WHEN emp_cv%NOTFOUND;

     DBMS_OUTPUT.PUT_LINE('Name = ' || person.ENAME ||' ' || person.JOB);

 END LOOP;

 END;

BEGIN

   OPEN  emp1

   FOR   SELECT *

         FROM emp

         WHERE ROWNUM < 11;

         process_emp_cv(emp1);

   CLOSE emp1;

   OPEN  emp1

   FOR   SELECT *

         FROM emp

         WHERE ENAME LIKE 'R%';

         process_emp_cv(emp1);

   CLOSE emp1;

END;

Difference between REF CURSOR and CURSOR with Example:

DECLARE

   TYPE rc IS REF CURSOR;

   CURSOR c IS SELECT * FROM dual;

   l_cursor rc;

BEGIN

       IF   (to_char(SYSDATE,'dd') = 30) THEN

            OPEN l_cursor FOR SELECT * FROM emp;

                 

      ELSIF (to_char(SYSDATE,'dd') = 29) THEN

             OPEN l_cursor FOR SELECT * FROM dept;

                   

      ELSE

             OPEN l_cursor FOR SELECT * FROM dual;

      END IF;

      OPEN c;

         -----

             /* some manipulation here */

         -----

      CLOSE c;

END;

Comparisons

1.       Cursor C will always be select * from dual.   The ref cursor can be anything.

2.       Cursor can be global -- a ref cursor cannot (you cannot define them OUTSIDE of a procedure / function).

3.       Ref cursor can be passed from subroutine to subroutine -- a cursor cannot be.

Usage Restrictions

The following are restrictions on cursor variable usage.

1.       Comparison operators cannot be used to test cursor variables for equality, inequality, null, or not null.

2.       Null cannot be assigned to a cursor variable.

3.       The value of a cursor variable cannot be stored in a database column.

4.       Static cursors and cursor variables are not interchangeable. For example, a static cursor cannot be used in an OPEN FOR statement.