GL_IMPORT_HOOK_PKG in R12
Have you ever needed something to be done after GL transfer from sources like Receivables, Payables or Inventory and before Journal Import is run? While the first requirement is to do something before journal import is run and the second one is after before GL posting is done.
Usually we resort to solutions like creating a new program and sandwiching that program between GL transfer and Journal Import program. It works, but again, we need run the Gl interface program setting the value for the parameter Journal Import as ‘No’. This approach has inherent issues. What should we do if the custom program fails? Should we let the journal import run? If it runs, the objective is failed. On the other hand if you want to stop the journal import from picking the rows, you need to manage the status in the interface table (one way of doing it). This gets complicated as you need to manage all the rows that go in as pair of accounting entries. On top of it the issue gets complicated based on summary posting versus detail posting. So let the journal import manage the business of balancing of accounting entries and let us do our part of managing the rows in the gl_interface table the way we want it.
So this post is to address the first one where the requirement is to update one of the references in gl_interface with the comments from the Miscellaneous Cash Receipts coming from Receivables. This update will happen after your transfer to GL from Receivables and before Journal Import is run. I can understand why this is a common requirement. Usually people who use GL as well as auditors require reasons why the miscellaneous cash was posted. Updating these reference columns makes those comments visible in journal entries screen. Just to warn you, this works only if you transfer from source in Detail and not in Summary mode. See the images of miscellaneous cash receipt and journal entries screen.
This is just an example on one use of this package. It can be used for any of your needs either be pre or post journal import program.
Code:
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;
/
======================================================================
CREATE OR REPLACE PACKAGE BODY APPS.gl_import_hook_pkg AS
/* $Header: glujihkb.pls 115.4 2002/11/13 20:08:17 djogg ship $ */
--
-- PUBLIC FUNCTIONS
--
--
-- Procedure
-- pre_module_hook
-- Purpose
-- Hook into journal import for other products.
-- This procedure is called after journal import has selected
-- the sources to process, but before it has started processing the data.
-- If you need to use this hook, please add a call to your own
-- package before the return statement. Please do NOT commit
-- your changes in your package.
-- Returns
-- TRUE - upon success (allows journal import to continue)
-- FALSE - upon failure (causes journal import to abort and display the
-- error in errbuf)
-- History
-- 19-JUN-95 D. J. Ogg Created
-- Arguments
-- sob_id The set of books id
-- run_id The import run id
-- errbuf The error message printed upon error
-- Example
-- gl_import_hook_pkg.pre_module_hook(2, 100, errbuf);
-- Notes
--
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 test_je_import_hook.update_gl_interface(sob_id, run_id, errbuf)) THEN
RETURN(FALSE);
END IF;
RETURN(TRUE);
END pre_module_hook;
--
-- Procedure
-- post_module_hook
-- Purpose
-- Hook into journal import for other products.
-- This procedure is called after journal import has inserted all of the
-- data into gl_je_batches, gl_je_headers, and gl_je_lines, but before
-- it does the final commit.
-- This routine is called once per 100 batches.
-- If you need to use this hook, please add a call to your own
-- package before the return statement. Please do NOT commit
-- your changes in your package.
-- Returns
-- TRUE - upon success (allows journal import to continue)
-- FALSE - upon failure (causes journal import to abort and display the
-- error in errbuf)
-- History
-- 28-FEB-00 D. J. Ogg Created
-- Arguments
-- batch_ids A list of batch ids, separated by the separator
-- separator The separator
-- last_set Indicates whether or not this is the last set
-- errbuf The error message printed upon error
-- Example
-- gl_import_hook_pkg.post_module_hook(2, 100, errbuf);
-- Notes
--
FUNCTION post_module_hook(batch_ids IN VARCHAR2,
separator IN VARCHAR2,
last_set IN BOOLEAN,
errbuf IN OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
BEGIN
gl_ip_process_batches_pkg.process_batches(batch_ids, separator, last_set);
-- Please put your function call here. Make it the following format:
-- IF (NOT dummy(sob_id, run_id, errbuf)) THEN
-- RETURN(FALSE);
-- END IF;
RETURN(TRUE);
END post_module_hook;
END gl_import_hook_pkg;
/
=============================================================
No comments:
Post a Comment