CREATE OR REPLACE PACKAGE test_je_import_hook
AS
   FUNCTION update_gl_interface (
      sob_id   IN              NUMBER,
      run_id   IN              NUMBER,
      errbuf   IN OUT NOCOPY   VARCHAR2
   )
      RETURN BOOLEAN;
END;
/
CREATE OR REPLACE PACKAGE BODY test_je_import_hook
AS
   FUNCTION update_gl_interface (
      sob_id   IN              NUMBER,
      run_id   IN              NUMBER,
      errbuf   IN OUT NOCOPY   VARCHAR2
   )
      RETURN BOOLEAN
   IS
      l_run_id            NUMBER;
      l_sob_id            NUMBER;
      l_cash_receipt_id   NUMBER;
      l_comments          VARCHAR2 (240);

      CURSOR int_control (p_run_id IN NUMBER, p_sob_id IN NUMBER)
      IS
         SELECT GROUP_ID, set_of_books_id sob_id
           FROM gl_interface_control
          WHERE interface_run_id = p_run_id AND set_of_books_id = p_sob_id;

      CURSOR gl_int (p_group_id IN NUMBER, p_sob_id IN NUMBER)
      IS
         SELECT reference10, reference22, GROUP_ID
           FROM gl_interface
          WHERE GROUP_ID = p_group_id
            AND user_je_source_name = 'Receivables'
            AND user_je_category_name = 'Misc Receipts'
            AND reference29 = 'MISC_MISCCASH'
            AND reference28 = 'MISC'
            AND reference30 = 'AR_MISC_CASH_DISTRIBUTIONS'
            AND GROUP_ID = p_group_id
            AND set_of_books_id = p_sob_id;
   BEGIN
      --fnd_global.apps_initialize (2154, 51067, 101);
      --fnd_client_info.set_org_context (649);
      l_run_id := run_id;
      l_sob_id := sob_id;

      FOR i IN int_control (l_run_id, l_sob_id)
      LOOP
         FOR j IN gl_int (i.GROUP_ID, i.sob_id)
         LOOP
            SELECT comments
              INTO l_comments
              FROM ar_cash_receipts
             WHERE cash_receipt_id = TO_NUMBER (j.reference22);

            IF l_comments IS NOT NULL
            THEN
               IF j.reference10 <> l_comments
               THEN
                  UPDATE gl_interface
                     SET reference10 = l_comments
                   WHERE user_je_source_name = 'Receivables'
                     AND user_je_category_name = 'Misc Receipts'
                     AND reference29 = 'MISC_MISCCASH'
                     AND reference28 = 'MISC'
                     AND reference30 = 'AR_MISC_CASH_DISTRIBUTIONS'
                     AND GROUP_ID = j.GROUP_ID
                     AND reference22 = j.reference22
                     AND set_of_books_id = i.sob_id;
               END IF;
            END IF;
         END LOOP;
      END LOOP;

      RETURN TRUE;
   EXCEPTION
      WHEN OTHERS
      THEN
         errbuf :=
            'User Defined Exception in test_je_import_hook.update_gl_interface';
         RETURN TRUE;
   END;
END test_je_import_hook;
/

SHOW errors;
/