GL Journal WebADI Import - Custom Validations

Dear Readers

Many of you may be aware that Oracle provide a means to load GL journal entries from WebADI sheet via a seeded functionality

This process has several seeded validations on Source, Category, Period etc. But now, let us suppose that you wanted to add some custom validation in there to prevent loading certain records if the custom logic is not met

Let us just say for the case of this example, that we want to prevent user from loading records with DR amount as 100 ... sounds funny but this can be modified as per your requirement I am just taking this as an example

For this, there is a seeded package gl_import_hook_pkg which provides means to add custom logic

In this package in the FUNCTION pre_module_hook add the code as below

  FUNCTION pre_module_hook(sob_id    IN     NUMBER,

         run_id    IN     NUMBER,

         errbuf    IN OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS

  BEGIN

    -- AX section

    IF ax_setup_pkg.ax_installed THEN

      IF NOT ax_ezl_filter_pkg.EZLFilter(sob_id, run_id, errbuf) THEN

        RETURN FALSE;

      END IF;

    END IF;

    -- Please put your function call here.  Make it the following format:

    --    IF (NOT dummy(sob_id, run_id, errbuf)) THEN

    --      RETURN(FALSE);

    --    END IF;

        IF (NOT anand_test_hook_prc(sob_id, run_id, errbuf)) THEN

          RETURN(FALSE);

        END IF;

    RETURN(TRUE);

  END pre_module_hook;

The code for my custom function is as below

CREATE OR REPLACE FUNCTION anand_test_hook_prc

(

sob_id NUMBER, run_id NUMBER, errbuf IN OUT NOCOPY VARCHAR2

)

RETURN BOOLEAN

AS

   lv_count NUMBER;

   lv_group_id NUMBER;

   lv_je_source VARCHAR2(240);

BEGIN

   INSERT INTO anand_test_hook_dbg VALUES('inside hook process run id value is ' || run_id);

   SELECT   COUNT(*)

   INTO     lv_count

   FROM     gl_interface_control

   WHERE    interface_run_id = run_id;

   INSERT INTO anand_test_hook_dbg VALUES('count in control is ' || lv_count);

   IF lv_count = 1 THEN

      SELECT   group_id, je_source_name

      INTO     lv_group_id, lv_je_source

      FROM     gl_interface_control

      WHERE    interface_run_id = run_id;

      IF lv_je_source = 'Spreadsheet' THEN

         SELECT   COUNT(1)

         INTO     lv_count

         FROM     gl_interface

         WHERE    group_id = lv_group_id

         AND      entered_dr = 100;

         INSERT INTO anand_test_hook_dbg VALUES('count in iface is ' || lv_count);

         IF lv_count <> 0 THEN

            fnd_file.put_line(fnd_file.log, ' *** CUSTOM VALIDATION FAILED *** --> There are records in this batch which have 100 as debit amount');

            fnd_file.put_line(fnd_file.output, ' *** CUSTOM VALIDATION FAILED *** --> There are records in this batch which have 100 as debit amount');

            RETURN FALSE;

         END IF;

      END IF;

   END IF;

   RETURN TRUE;

END anand_test_hook_prc;

When you now try to load GL JV with DR = 100, the custom validation will fail and prevent importing of such records :-)

Hope this helps

Cheers
A