Wednesday, August 20, 2014

Oracle API to validate gl code combinations in Oracle General Ledger

Oracle API to finds combination_id for given segment values


Oracle Applications

Cross Validation Rule API- CCID in Oracle GL


p_ccid number number ;
l_ccid_number number;
l_set_of_books_id number;

 /* Find the Chart Of Accounts ID */

    SELECT  gls.chart_of_accounts_id,
            gls.set_of_books_id
    INTO    l_ccid_number,
            l_set_of_books_id
    FROM    gl_sets_of_books gls
    WHERE   gls.set_of_books_id = p_sob_id;

 /* Get the delimiter for the code combination */

   l_delimiter := fnd_flex_ext.get_delimiter(
                     application_short_name  => 'SQLGL' ,
                     key_flex_code           => 'GL#' ,
                     structure_number        => l_ccid_number);

 /* Concatenate the segment values */

    l_segments :=p_segment1 || l_delimiter || p_segment2 || l_delimiter ||
                 p_segment3 || l_delimiter || p_segment4 || l_delimiter ||
                 p_segment5 || l_delimiter || p_segment6 || l_delimiter ||
                 p_segment7 || l_delimiter || p_segment8 || l_delimiter ||
                 p_segment9 || l_delimiter || p_segment10;

--API to validate account
/*      Finds combination_id for given segment values.                      */
/*      If validation date is NULL checks all cross-validation rules.       */
/*      Returns TRUE if combination valid, or FALSE and sets error message  */
/*      on server using FND_MESSAGE if invalid.                             */

   x_status :=
      fnd_flex_ext.get_combination_id (application_short_name       => 'SQLGL'
                                      ,key_flex_code                => 'GL#'
                                      ,structure_number           => l_ccid_number
                                      ,validation_date               => SYSDATE
                                      ,n_segments                     => 10 --number of segments
                                      ,segments                         => l_segments
                                      ,combination_id               => p_ccid
                                      ,data_set                          => -1
                                      );

    IF NOT x_status
    THEN
      p_err_msg := 'Invalid Account Segment ' || p_ccid;
      ROLLBACK;
      RETURN 1;
    ELSE
      COMMIT;
      RETURN 0;
    END IF;

--Another API
/* Validate the code combination also, this API will return ccid, if valid and already exists. Else creates a new ccid for a valid code combination.*/

    x_cao_ccid := fnd_flex_ext.get_ccid (
                            application_short_name  => 'SQLGL' ,
                            key_flex_code           => 'GL#' ,
                            structure_number        => l_ccid_number ,
                            validation_date         => to_char(sysdate, 'DD-MON-YYYY') ,
                            concatenated_segments   => l_segments);
    /* If the above API returns a non-zero value, cross-validation successful. */
    IF (x_cao_ccid = 0) THEN
        --Error
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Validation Error' );
        FND_FILE.PUT_LINE(FND_FILE.LOG, SUBSTR(fnd_flex_ext.get_message, 0, 240));
    ELSE
        --success
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Validation Successful' );
       
    END IF;

You can see that below where segments has been defined and segment separator



 

1 comment:

  1. fnd_flex_ext.get_combination_id also creates a combination. Please update your post

    ReplyDelete