Oracle API for GL Interface Package Body CREATE OR REPLACE PACKAGE BODY xx_import_xfer_gl_iface_api IS /* $Header: XXXXXXXX.pkh, Version 1.0, 07-FEB-2003 UKEXPI $ *********************************************************************** * * * History Log * * * *********************************************************************** * * * App/Release : Oracle e-Business Suite RXXX * * Oracle App : SHORTNAME - FULL_NAME * * Module : SHORTNAME - DESCRIPTION * * Author : Anil Passi(Go 4 Gold), * * Version : 1.0 * * Revised : 11-SEP-2005 * * Description : This package delivers GL interface API * * * * Change History Log * * ================== * * * * Version Date Author Change * * ======= =========== ============ ================================* * * 1.0 10-OCT-2002 A. Passi Initial Version * 1.2 12-OCT-2002 A. Passi Added ability to "transfer_to_gl" such that both the credit and debit can either happen in single API call, or multiple calls(when an amount is distributed). * 1.3 24-Jan-2006 A. Passi Add the trx_number to the batch details. This is so because the recurring journal will have incremental trx_number for each period, with the batch number remaining the same. * 1.4 08-Sep-2005 A. Passi Move the journal to next period if current period is closed **********************************************************************/ g_sob_id INTEGER := fnd_profile.VALUE('GL_SET_OF_BKS_ID'); g_source_name gl_je_headers.je_source%TYPE; g_error_flag VARCHAR2(1) := NULL; g_xx_transaction_source CONSTANT VARCHAR2(80) := 'XX_COST_XFER'; --move these three into the config screen. --$?$ g_gl_user_je_source_name CONSTANT VARCHAR2(30) := nvl(fnd_profile.VALUE('XX_OVERRIDE_GL_JE_SOURCE_NAME') ,'Manual'); --$?$ g_gl_user_je_category_name CONSTANT VARCHAR2(30) := nvl(fnd_profile.VALUE('XX_OVERRIDE_JE_CATEGORY_NAME') ,'Miscellaneous Transaction'); --$?$ g_conversion_type VARCHAR2(30) := nvl(fnd_profile.VALUE('XX_OVERRIDE_GL_RATE_CONVERSION_TYPE') ,'Corporate'); g_gl_batch_prefix VARCHAR2(30) := nvl(fnd_profile.VALUE('XX_OVERRIDE_GL_BATCH_PREFIX') ,'XX_GL_FEED_NAME:'); g_gl_appl_name CONSTANT VARCHAR2(30) := 'SQLGL'; g_conversion_type_code VARCHAR2(30); g_debug_procedure_context VARCHAR2(30); g_debug_header_context CONSTANT VARCHAR2(80) := 'xx_gl_feed.plsql.xx_import_xfer_gl_iface_api.'; --Programming variables --g_group_id and g_interface_run_id will be set each time separately for a batch g_group_id NUMBER; g_interface_run_id NUMBER; PROCEDURE new_gl_interface_batch IS BEGIN g_group_id := NULL; g_interface_run_id := NULL; END new_gl_interface_batch; PROCEDURE debug_stmt(p_msg IN VARCHAR2) IS BEGIN fnd_log.STRING(log_level => fnd_log.level_statement ,module => g_debug_header_context || g_debug_procedure_context ,message => p_msg); IF fnd_global.conc_request_id > 0 AND fnd_profile.VALUE('AFLOG_ENABLED') = 'Y' THEN fnd_file.put_line(which => fnd_file.log ,buff => p_msg); END IF; END debug_stmt; PROCEDURE debug_begin_procedure IS BEGIN fnd_log.STRING(log_level => fnd_log.level_statement ,module => g_debug_header_context || g_debug_procedure_context ,message => 'Begin ' || g_debug_procedure_context); END debug_begin_procedure; PROCEDURE debug_end_procedure IS BEGIN fnd_log.STRING(log_level => fnd_log.level_statement ,module => g_debug_header_context || g_debug_procedure_context ,message => 'End ' || g_debug_procedure_context); END debug_end_procedure; PROCEDURE set_debug_context(p_procedure_name IN VARCHAR2) IS BEGIN g_debug_procedure_context := p_procedure_name; debug_begin_procedure; END set_debug_context; PROCEDURE log_error(p_batch_header_id IN INTEGER ,p_trx_header_id IN INTEGER ,p_error_message IN VARCHAR2) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN IF fnd_global.conc_request_id > 0 THEN fnd_file.put_line(fnd_file.output ,'ERROR::' || p_error_message); g_error_flag := 'Y'; END IF; --log error into your error logging table /* INSERT INTO xx_gl_api_error_logs (error_log_id ,batch_header_id ,trx_header_id ,error_message ,creation_date ,fnd_request_id) VALUES (xx_error_log_s.NEXTVAL -- x_error_log_id ,p_batch_header_id ,p_trx_header_id ,substr(p_error_message, 1, 2000) ,SYSDATE --creation_date ,fnd_global.conc_request_id --fnd_request_id ); */ debug_stmt('ERROR: ' || p_error_message); COMMIT; EXCEPTION WHEN OTHERS THEN debug_stmt('ERROR: in Log_error'); ROLLBACK; END log_error; FUNCTION get_sob_id RETURN INTEGER IS BEGIN set_debug_context('get_sob_id'); debug_stmt('get_sob_id() Returning g_sob_id=>' || g_sob_id); RETURN g_sob_id; debug_end_procedure; END get_sob_id; PROCEDURE set_sob_id(p_sob_id IN INTEGER) IS BEGIN set_debug_context('set_sob_id'); g_sob_id := p_sob_id; debug_stmt('set_sob_id() Setting g_sob_id=>' || g_sob_id); debug_end_procedure; END set_sob_id; PROCEDURE set_source_name(p_source_name IN VARCHAR2) IS BEGIN set_debug_context('set_source_name'); g_source_name := p_source_name; debug_stmt('set_source_name() Setting g_source_name=>' || g_source_name); debug_end_procedure; END set_source_name; FUNCTION get_source_name RETURN VARCHAR2 IS BEGIN RETURN g_source_name; END get_source_name; FUNCTION get_xx_transaction_source RETURN VARCHAR2 IS BEGIN RETURN g_xx_transaction_source; END get_xx_transaction_source; PROCEDURE populate_interface_control(p_user_je_source_name IN VARCHAR2 ,p_group_id IN OUT NUMBER ,p_set_of_books_id NUMBER ,p_interface_run_id IN OUT NUMBER) IS re_use_gl_interface_control EXCEPTION; BEGIN set_debug_context('populate_interface_control'); IF g_group_id IS NOT NULL AND g_interface_run_id IS NOT NULL THEN p_group_id := g_group_id; p_interface_run_id := g_interface_run_id; RAISE re_use_gl_interface_control; END IF; gl_journal_import_pkg.populate_interface_control(user_je_source_name => p_user_je_source_name ,group_id => p_group_id ,set_of_books_id => p_set_of_books_id ,interface_run_id => p_interface_run_id); g_group_id := p_group_id; g_interface_run_id := p_interface_run_id; debug_stmt('Returning New Group p_group_id=>' || p_group_id || ' p_interface_run_id=>' || p_interface_run_id); debug_end_procedure; EXCEPTION WHEN re_use_gl_interface_control THEN debug_stmt('Returning Global Group g_group_id=>' || g_group_id || ' g_interface_run_id=>' || g_interface_run_id); debug_end_procedure; END populate_interface_control; PROCEDURE interface_record IS n_group_id INTEGER; n_sob_id INTEGER; n_interface_run_id INTEGER; BEGIN set_debug_context('interface_record'); n_sob_id := get_sob_id; populate_interface_control(p_user_je_source_name => g_source_name ,p_group_id => n_group_id ,p_set_of_books_id => n_sob_id ,p_interface_run_id => n_interface_run_id); debug_end_procedure; END interface_record; PROCEDURE insert_statement(p_gl_int_rec IN OUT NOCOPY g_gl_int_type_rec) IS BEGIN set_debug_context('insert_statement'); INSERT INTO gl_interface (reference_date ,attribute20 ,CONTEXT ,context2 ,invoice_date ,tax_code ,invoice_identifier ,invoice_amount ,context3 ,ussgl_transaction_code ,descr_flex_error_message ,jgzz_recon_ref ,segment23 ,segment24 ,segment25 ,segment26 ,segment27 ,segment28 ,segment29 ,segment30 ,entered_dr ,entered_cr ,accounted_dr ,accounted_cr ,transaction_date ,reference1 ,reference2 ,reference3 ,reference4 ,reference5 ,reference6 ,reference7 ,reference8 ,reference9 ,reference10 ,reference11 ,reference12 ,reference13 ,reference14 ,reference15 ,reference16 ,reference17 ,reference18 ,reference19 ,reference20 ,reference21 ,reference22 ,reference23 ,reference24 ,reference25 ,reference26 ,reference27 ,reference28 ,reference29 ,reference30 ,je_batch_id ,period_name ,je_header_id ,je_line_num ,chart_of_accounts_id ,functional_currency_code ,code_combination_id ,date_created_in_gl ,warning_code ,status_description ,stat_amount ,group_id ,request_id ,subledger_doc_sequence_id ,subledger_doc_sequence_value ,attribute1 ,attribute2 ,gl_sl_link_id ,gl_sl_link_table ,attribute3 ,attribute4 ,attribute5 ,attribute6 ,attribute7 ,attribute8 ,attribute9 ,attribute10 ,attribute11 ,attribute12 ,attribute13 ,attribute14 ,attribute15 ,attribute16 ,attribute17 ,attribute18 ,attribute19 ,status ,set_of_books_id ,accounting_date ,currency_code ,date_created ,created_by ,actual_flag ,user_je_category_name ,user_je_source_name ,currency_conversion_date ,encumbrance_type_id ,budget_version_id ,user_currency_conversion_type ,currency_conversion_rate ,average_journal_flag ,originating_bal_seg_value ,segment1 ,segment2 ,segment3 ,segment4 ,segment5 ,segment6 ,segment7 ,segment8 ,segment9 ,segment10 ,segment11 ,segment12 ,segment13 ,segment14 ,segment15 ,segment16 ,segment17 ,segment18 ,segment19 ,segment20 ,segment21 ,segment22) VALUES (p_gl_int_rec.reference_date -- reference_date ,p_gl_int_rec.attribute20 -- attribute20 ,p_gl_int_rec.CONTEXT -- context ,p_gl_int_rec.context2 -- context2 ,p_gl_int_rec.invoice_date -- invoice_date ,p_gl_int_rec.tax_code -- tax_code ,p_gl_int_rec.invoice_identifier -- invoice_identifier ,p_gl_int_rec.invoice_amount -- invoice_amount ,p_gl_int_rec.context3 -- context3 ,p_gl_int_rec.ussgl_transaction_code -- ussgl_transaction_code ,p_gl_int_rec.descr_flex_error_message -- descr_flex_error_message ,p_gl_int_rec.jgzz_recon_ref -- jgzz_recon_ref ,p_gl_int_rec.segment23 -- segment23 ,p_gl_int_rec.segment24 -- segment24 ,p_gl_int_rec.segment25 -- segment25 ,p_gl_int_rec.segment26 -- segment26 ,p_gl_int_rec.segment27 -- segment27 ,p_gl_int_rec.segment28 -- segment28 ,p_gl_int_rec.segment29 -- segment29 ,p_gl_int_rec.segment30 -- segment30 ,p_gl_int_rec.entered_dr -- entered_dr ,p_gl_int_rec.entered_cr -- entered_cr ,p_gl_int_rec.accounted_dr -- accounted_dr ,p_gl_int_rec.accounted_cr -- accounted_cr ,p_gl_int_rec.transaction_date -- transaction_date ,p_gl_int_rec.reference1 -- reference1 ,p_gl_int_rec.reference2 -- reference2 ,p_gl_int_rec.reference3 -- reference3 ,p_gl_int_rec.reference4 -- reference4 ,p_gl_int_rec.reference5 -- reference5 ,p_gl_int_rec.reference6 -- reference6 ,p_gl_int_rec.reference7 -- reference7 ,p_gl_int_rec.reference8 -- reference8 ,p_gl_int_rec.reference9 -- reference9 ,p_gl_int_rec.reference10 -- reference10 ,p_gl_int_rec.reference11 -- reference11 ,p_gl_int_rec.reference12 -- reference12 ,p_gl_int_rec.reference13 -- reference13 ,p_gl_int_rec.reference14 -- reference14 ,p_gl_int_rec.reference15 -- reference15 ,p_gl_int_rec.reference16 -- reference16 ,p_gl_int_rec.reference17 -- reference17 ,p_gl_int_rec.reference18 -- reference18 ,p_gl_int_rec.reference19 -- reference19 ,p_gl_int_rec.reference20 -- reference20 ,p_gl_int_rec.reference21 -- reference21 ,p_gl_int_rec.reference22 -- reference22 ,p_gl_int_rec.reference23 -- reference23 ,p_gl_int_rec.reference24 -- reference24 ,p_gl_int_rec.reference25 -- reference25 ,p_gl_int_rec.reference26 -- reference26 ,p_gl_int_rec.reference27 -- reference27 ,p_gl_int_rec.reference28 -- reference28 ,p_gl_int_rec.reference29 -- reference29 ,p_gl_int_rec.reference30 -- reference30 ,p_gl_int_rec.je_batch_id -- je_batch_id ,p_gl_int_rec.period_name -- period_name ,p_gl_int_rec.je_header_id -- je_header_id ,p_gl_int_rec.je_line_num -- je_line_num ,p_gl_int_rec.chart_of_accounts_id -- chart_of_accounts_id ,p_gl_int_rec.functional_currency_code -- functional_currency_code ,p_gl_int_rec.code_combination_id -- code_combination_id ,p_gl_int_rec.date_created_in_gl -- date_created_in_gl ,p_gl_int_rec.warning_code -- warning_code ,p_gl_int_rec.status_description -- status_description ,p_gl_int_rec.stat_amount -- stat_amount ,p_gl_int_rec.group_id -- group_id ,p_gl_int_rec.request_id -- request_id ,p_gl_int_rec.subledger_doc_sequence_id -- subledger_doc_sequence_id ,p_gl_int_rec.subledger_doc_sequence_value -- subledger_doc_sequence_value ,p_gl_int_rec.attribute1 -- attribute1 ,p_gl_int_rec.attribute2 -- attribute2 ,p_gl_int_rec.gl_sl_link_id -- gl_sl_link_id ,p_gl_int_rec.gl_sl_link_table -- gl_sl_link_table ,p_gl_int_rec.attribute3 -- attribute3 ,p_gl_int_rec.attribute4 -- attribute4 ,p_gl_int_rec.attribute5 -- attribute5 ,p_gl_int_rec.attribute6 -- attribute6 ,p_gl_int_rec.attribute7 -- attribute7 ,p_gl_int_rec.attribute8 -- attribute8 ,p_gl_int_rec.attribute9 -- attribute9 ,p_gl_int_rec.attribute10 -- attribute10 ,p_gl_int_rec.attribute11 -- attribute11 ,p_gl_int_rec.attribute12 -- attribute12 ,p_gl_int_rec.attribute13 -- attribute13 ,p_gl_int_rec.attribute14 -- attribute14 ,p_gl_int_rec.attribute15 -- attribute15 ,p_gl_int_rec.attribute16 -- attribute16 ,p_gl_int_rec.attribute17 -- attribute17 ,p_gl_int_rec.attribute18 -- attribute18 ,p_gl_int_rec.attribute19 -- attribute19 ,p_gl_int_rec.status -- status ,p_gl_int_rec.set_of_books_id -- set_of_books_id ,p_gl_int_rec.accounting_date -- accounting_date ,p_gl_int_rec.currency_code -- currency_code ,p_gl_int_rec.date_created -- date_created ,p_gl_int_rec.created_by -- created_by ,p_gl_int_rec.actual_flag -- actual_flag ,p_gl_int_rec.user_je_category_name -- user_je_category_name ,p_gl_int_rec.user_je_source_name -- user_je_source_name ,p_gl_int_rec.currency_conversion_date -- currency_conversion_date ,p_gl_int_rec.encumbrance_type_id -- encumbrance_type_id ,p_gl_int_rec.budget_version_id -- budget_version_id ,p_gl_int_rec.user_currency_conversion_type -- user_currency_conversion_type ,p_gl_int_rec.currency_conversion_rate -- currency_conversion_rate ,p_gl_int_rec.average_journal_flag -- average_journal_flag ,p_gl_int_rec.originating_bal_seg_value -- originating_bal_seg_value ,p_gl_int_rec.segment1 -- segment1 ,p_gl_int_rec.segment2 -- segment2 ,p_gl_int_rec.segment3 -- segment3 ,p_gl_int_rec.segment4 -- segment4 ,p_gl_int_rec.segment5 -- segment5 ,p_gl_int_rec.segment6 -- segment6 ,p_gl_int_rec.segment7 -- segment7 ,p_gl_int_rec.segment8 -- segment8 ,p_gl_int_rec.segment9 -- segment9 ,p_gl_int_rec.segment10 -- segment10 ,p_gl_int_rec.segment11 -- segment11 ,p_gl_int_rec.segment12 -- segment12 ,p_gl_int_rec.segment13 -- segment13 ,p_gl_int_rec.segment14 -- segment14 ,p_gl_int_rec.segment15 -- segment15 ,p_gl_int_rec.segment16 -- segment16 ,p_gl_int_rec.segment17 -- segment17 ,p_gl_int_rec.segment18 -- segment18 ,p_gl_int_rec.segment19 -- segment19 ,p_gl_int_rec.segment20 -- segment20 ,p_gl_int_rec.segment21 -- segment21 ,p_gl_int_rec.segment22 -- segment22 ); debug_stmt(SQL%ROWCOUNT || ' records inserted into GL_INTERFACE'); debug_end_procedure; END insert_statement; FUNCTION check_ccid(p_sob_id IN INTEGER ,p_ccid IN INTEGER) RETURN BOOLEAN IS CURSOR c_check IS SELECT 'x' FROM gl_code_combinations gcc, gl_sets_of_books gsob WHERE gsob.set_of_books_id = p_sob_id AND gcc.code_combination_id = p_ccid AND gcc.chart_of_accounts_id = gsob.chart_of_accounts_id AND gcc.enabled_flag = 'Y'; p_check c_check%ROWTYPE; BEGIN set_debug_context('check_ccid'); debug_stmt('p_sob_id=>' || p_sob_id || ' p_ccid=>' || p_ccid); OPEN c_check; FETCH c_check INTO p_check; IF c_check%NOTFOUND THEN CLOSE c_check; debug_stmt('check_ccid() RETURNED FALSE;'); RETURN FALSE; END IF; CLOSE c_check; debug_stmt('check_ccid() RETURNED TRUE;'); RETURN TRUE; debug_end_procedure; END check_ccid; FUNCTION get_gl_application_id(p_appl_name IN VARCHAR2) RETURN INTEGER IS CURSOR c_check IS SELECT application_id FROM fnd_application_vl WHERE application_short_name = p_appl_name; p_check c_check%ROWTYPE; BEGIN OPEN c_check; FETCH c_check INTO p_check; CLOSE c_check; RETURN p_check.application_id; END get_gl_application_id; FUNCTION check_conversion_type RETURN BOOLEAN IS CURSOR c_check IS SELECT 'x' FROM gl_daily_conversion_types WHERE user_conversion_type = g_conversion_type; p_check c_check%ROWTYPE; BEGIN set_debug_context('check_conversion_type'); debug_stmt('g_conversion_type=>' || g_conversion_type); OPEN c_check; FETCH c_check INTO p_check; IF c_check%NOTFOUND THEN CLOSE c_check; debug_stmt('check_conversion_type() RETURNED FALSE;'); RETURN FALSE; END IF; CLOSE c_check; debug_stmt('check_conversion_type() RETURNED TRUE;'); RETURN TRUE; END check_conversion_type; FUNCTION check_gl_source(p_gl_user_je_source_name IN VARCHAR2) RETURN BOOLEAN IS CURSOR c_check IS SELECT 'x' FROM gl_je_sources WHERE user_je_source_name = nvl(p_gl_user_je_source_name ,g_gl_user_je_source_name); p_check c_check%ROWTYPE; BEGIN set_debug_context('check_gl_source'); debug_stmt('g_gms_cost_xfer_source=>' || g_gl_user_je_source_name); OPEN c_check; FETCH c_check INTO p_check; IF c_check%NOTFOUND THEN CLOSE c_check; debug_stmt('check_gl_source() RETURNED FALSE;'); RETURN FALSE; END IF; CLOSE c_check; debug_stmt('check_gl_source() RETURNED TRUE;'); RETURN TRUE; END check_gl_source; FUNCTION get_next_available_open_date(p_accounting_date IN DATE ,p_gl_appl_id IN NUMBER ,p_sob_id IN NUMBER) RETURN DATE IS CURSOR c_get IS SELECT gps.start_date FROM gl_period_statuses gps WHERE gps.application_id = p_gl_appl_id AND gps.set_of_books_id = p_sob_id AND gps.closing_status IN ('O', 'F') AND trunc(gps.start_date) > p_accounting_date ORDER BY trunc(gps.start_date); p_get c_get%ROWTYPE; BEGIN OPEN c_get; FETCH c_get INTO p_get; CLOSE c_get; RETURN p_get.start_date; END get_next_available_open_date; FUNCTION validate_accounting_date(p_accounting_date IN OUT DATE ,p_gl_appl_id IN NUMBER ,p_sob_id IN NUMBER ,p_batch_header_id IN NUMBER ,p_trx_header_id IN NUMBER) RETURN BOOLEAN IS v_count NUMBER := 0; v_accounting_date DATE; BEGIN set_debug_context('validate_accounting_date'); v_accounting_date := p_accounting_date; debug_stmt('p_accounting_date=>' || p_accounting_date || ' p_gl_appl_id=>' || p_gl_appl_id || ' p_sob_id=>' || p_sob_id); SELECT COUNT(*) INTO v_count FROM gl_period_statuses gps WHERE gps.application_id = p_gl_appl_id AND gps.set_of_books_id = p_sob_id AND gps.closing_status IN ('O', 'F') AND trunc(p_accounting_date) BETWEEN nvl(trunc(gps.start_date) ,trunc(p_accounting_date)) AND nvl(trunc(gps.end_date) ,trunc(p_accounting_date)); IF v_count > 0 THEN debug_stmt('validate_accounting_date() RETURNED TRUE'); RETURN TRUE; ELSE debug_stmt('validate_accounting_date() Cant find open Period for ' || p_accounting_date); --try to find next available date now p_accounting_date := get_next_available_open_date(p_accounting_date => v_accounting_date ,p_gl_appl_id => p_gl_appl_id ,p_sob_id => p_sob_id); IF p_accounting_date IS NULL THEN log_error(p_batch_header_id => p_batch_header_id ,p_trx_header_id => p_trx_header_id ,p_error_message => 'Accounting Date ' || to_char(v_accounting_date ,'DD-MON-YYYY') || ' does not belong to a Open Period'); /* --!!!!!Abort the process here raise_application_error(-20001 ,v_accounting_date || ' Can not find open/future period for this date. Batch Number ' || p_batch_header_id || ' . There must be at least one future/open period for this process to run' ); */ RETURN FALSE; ELSE debug_stmt('validate_accounting_date() Using next available open date ' || p_accounting_date); RETURN TRUE; END IF; END IF; END validate_accounting_date; -- Function to validate currency code FUNCTION validate_currency(p_currency_code IN VARCHAR2 ,p_accounting_date IN DATE) RETURN BOOLEAN IS v_count NUMBER := 0; BEGIN set_debug_context('validate_currency'); SELECT COUNT(*) INTO v_count FROM fnd_currencies fc WHERE fc.currency_code = p_currency_code AND enabled_flag = 'Y' AND p_accounting_date BETWEEN nvl(start_date_active ,p_accounting_date) AND nvl(end_date_active ,p_accounting_date); IF v_count > 0 THEN debug_stmt('validate_currency() RETURNED TRUE'); RETURN TRUE; ELSE debug_stmt('validate_currency() RETURNED FLASE'); RETURN FALSE; END IF; END validate_currency; PROCEDURE set_conversion_type_code IS CURSOR c_get IS SELECT conversion_type FROM gl_daily_conversion_types WHERE user_conversion_type = g_conversion_type; BEGIN set_debug_context('set_conversion_type_code'); debug_stmt('g_conversion_type=>' || g_conversion_type); OPEN c_get; FETCH c_get INTO g_conversion_type_code; CLOSE c_get; debug_stmt('g_conversion_type_code=>' || g_conversion_type_code); debug_end_procedure; END set_conversion_type_code; FUNCTION get_batch_employee_number(p_batch_header_id IN INTEGER) RETURN VARCHAR2 IS CURSOR c_get IS SELECT ppx.employee_number FROM per_people_x ppx --,"your own table here for the feed " bhdr --WHERE ppx.person_id = bhdr.employee_id ; p_get c_get%ROWTYPE; BEGIN --change this function as per your need --i am returning a constant value RETURN '101010'; OPEN c_get; FETCH c_get INTO p_get; CLOSE c_get; RETURN p_get.employee_number; END get_batch_employee_number; FUNCTION get_gl_je_reference1(p_batch_header_id IN INTEGER ,p_trx_number IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN g_gl_batch_prefix || p_batch_header_id || '-' || p_trx_number || ':' || get_batch_employee_number(p_batch_header_id) || ':'; END get_gl_je_reference1; FUNCTION get_sob_currency(p_sob_id IN INTEGER) RETURN VARCHAR2 IS CURSOR c_check IS SELECT currency_code FROM gl_sets_of_books WHERE set_of_books_id = p_sob_id; p_check c_check%ROWTYPE; BEGIN OPEN c_check; FETCH c_check INTO p_check; CLOSE c_check; RETURN p_check.currency_code; END get_sob_currency; /************************************************** Main code for gms_cost_xfer GL Interface begins here **************************************************/ PROCEDURE transfer_to_gl(p_sob_id IN INTEGER ,p_batch_header_id IN INTEGER ,p_trx_header_id IN INTEGER ,p_trx_line_id IN INTEGER ,p_date IN OUT DATE ,p_currency_code IN VARCHAR2 ,p_amount IN NUMBER ,p_dr_account IN INTEGER ,p_cr_account IN INTEGER ,p_journal_name IN VARCHAR2 ,p_header_description IN VARCHAR2 ,p_line_description IN VARCHAR2 ,p_success_flag OUT BOOLEAN ,p_first_record_in_batch IN VARCHAR2 ,p_submit_gl_interface IN VARCHAR2 ,p_gl_user_je_source_name IN VARCHAR2 ,p_gl_user_je_category_name IN VARCHAR2 ,p_trx_number IN VARCHAR2) IS v_gl_appl_id NUMBER; l_gl_int_type_rec g_gl_int_type_rec; v_conc_id INTEGER; conversion_rate_does_not_exist EXCEPTION; invalid_conversion_type EXCEPTION; invalid_dr_ccid EXCEPTION; invalid_cr_ccid EXCEPTION; invalid_currency_code EXCEPTION; not_in_open_period EXCEPTION; invalid_gl_source EXCEPTION; v_func_curr VARCHAR2(30); v_group_id NUMBER; v_interface_run_id NUMBER; v_ret_val BOOLEAN; v_request_id INTEGER; phase VARCHAR2(100); status VARCHAR2(100); dev_phase VARCHAR2(100); dev_status VARCHAR2(100); v_message VARCHAR2(100); v_bool BOOLEAN; v_old_status VARCHAR2(30); BEGIN set_debug_context('transfer_to_gl'); SAVEPOINT gms_cost_xfer_gl_int; set_conversion_type_code; /* Get the GL Application ID */ v_gl_appl_id := get_gl_application_id(p_appl_name => g_gl_appl_name); IF p_first_record_in_batch = 'Y' THEN debug_stmt('p_first_record_in_batch is Y. Hence will reset global variables to initiate new batch'); new_gl_interface_batch; ELSE debug_stmt('Not being the first record, accumulating this line to existing batch.'); END IF; /* May be GL team forgets to create source for gms_cost_xfer, lets see...*/ IF NOT check_gl_source(p_gl_user_je_source_name) THEN RAISE invalid_gl_source; END IF; /* During the design phase in Swiss Re the conversion type has already been changed once Lets not take any chances and check is this is a valid conversion type */ IF NOT check_conversion_type THEN RAISE invalid_conversion_type; END IF; /* Lets validate the accounting date. Although gms_cost_xfer almost always passes the current date. But still this is a good to have validation. */ IF NOT validate_accounting_date(p_accounting_date => p_date ,p_gl_appl_id => v_gl_appl_id ,p_sob_id => p_sob_id ,p_batch_header_id => p_batch_header_id ,p_trx_header_id => p_trx_header_id) THEN RAISE not_in_open_period; END IF; /* Lets validate the currency code. Although gms_cost_xfer will certainly pass correct curr code*/ IF NOT validate_currency(p_accounting_date => p_date ,p_currency_code => p_currency_code) THEN RAISE invalid_currency_code; END IF; v_func_curr := get_sob_currency(p_sob_id); IF nvl(v_func_curr ,'XX') != nvl(p_currency_code ,'XX') THEN IF 'N' = gl_currency_api.rate_exists(x_from_currency => p_currency_code ,x_to_currency => v_func_curr ,x_conversion_date => p_date ,x_conversion_type => g_conversion_type_code) THEN RAISE conversion_rate_does_not_exist; ELSE l_gl_int_type_rec.user_currency_conversion_type := g_conversion_type; l_gl_int_type_rec.currency_conversion_date := p_date; END IF; END IF; set_sob_id(p_sob_id => p_sob_id); set_source_name(p_source_name => nvl(p_gl_user_je_source_name ,g_gl_user_je_source_name)); populate_interface_control(p_user_je_source_name => nvl(p_gl_user_je_source_name ,g_gl_user_je_source_name) ,p_group_id => v_group_id ,p_set_of_books_id => p_sob_id ,p_interface_run_id => v_interface_run_id); --batch name; part of the je batch name l_gl_int_type_rec.reference1 := get_gl_je_reference1(p_batch_header_id => p_batch_header_id ,p_trx_number => p_trx_number); --je batch description l_gl_int_type_rec.reference2 := p_batch_header_id || ': ' || p_header_description || '-' || p_trx_number; --part of the je header name l_gl_int_type_rec.reference4 := p_journal_name || '-' || p_trx_number; --je header description will be the description of the gms_cost_xfer receipt l_gl_int_type_rec.reference5 := p_batch_header_id || ': ' || nvl(p_header_description ,p_journal_name) || '-' || p_trx_number; --if the description is left blank, then use the Journal Name instead --je lines description will be the description of the gms_cost_xfer receipt l_gl_int_type_rec.reference10 := p_line_description; l_gl_int_type_rec.reference_date := p_date; l_gl_int_type_rec.reference21 := p_header_description; l_gl_int_type_rec.group_id := v_group_id; l_gl_int_type_rec.set_of_books_id := p_sob_id; l_gl_int_type_rec.user_je_source_name := nvl(p_gl_user_je_source_name ,g_gl_user_je_source_name); l_gl_int_type_rec.user_je_category_name := nvl(p_gl_user_je_category_name ,g_gl_user_je_category_name); l_gl_int_type_rec.accounting_date := p_date; l_gl_int_type_rec.transaction_date := p_date; l_gl_int_type_rec.currency_code := p_currency_code; l_gl_int_type_rec.date_created := p_date; l_gl_int_type_rec.created_by := fnd_global.user_id; l_gl_int_type_rec.actual_flag := 'A'; l_gl_int_type_rec.status := 'S'; l_gl_int_type_rec.attribute1 := p_trx_header_id; l_gl_int_type_rec.attribute2 := p_trx_line_id; l_gl_int_type_rec.attribute3 := p_trx_number; l_gl_int_type_rec.attribute4 := get_xx_transaction_source; l_gl_int_type_rec.attribute5 := p_batch_header_id; IF p_dr_account IS NOT NULL AND (NOT check_ccid(p_sob_id => p_sob_id ,p_ccid => p_dr_account)) THEN RAISE invalid_dr_ccid; END IF; IF p_cr_account IS NOT NULL AND (NOT check_ccid(p_sob_id => p_sob_id ,p_ccid => p_cr_account)) THEN RAISE invalid_cr_ccid; END IF; /* First the gms_cost_xfer Debit Line */ l_gl_int_type_rec.entered_dr := p_amount; l_gl_int_type_rec.entered_cr := NULL; l_gl_int_type_rec.code_combination_id := p_dr_account; debug_stmt('p_dr_account ' || l_gl_int_type_rec.code_combination_id); IF p_dr_account IS NOT NULL THEN insert_statement(p_gl_int_rec => l_gl_int_type_rec); END IF; l_gl_int_type_rec.entered_dr := NULL; l_gl_int_type_rec.entered_cr := p_amount; l_gl_int_type_rec.code_combination_id := p_cr_account; IF p_cr_account IS NOT NULL THEN insert_statement(p_gl_int_rec => l_gl_int_type_rec); debug_stmt('p_cr_account ' || l_gl_int_type_rec.code_combination_id); END IF; IF p_submit_gl_interface = 'Y' THEN debug_stmt('p_submit_gl_interface=>' || p_submit_gl_interface); /* After inset, submit the request*/ COMMIT; v_conc_id := fnd_request.submit_request(application => 'SQLGL' ,program => 'GLLEZL' ,description => NULL ,start_time => SYSDATE ,sub_request => FALSE ,argument1 => v_interface_run_id ,argument2 => p_sob_id ,argument3 => 'N' ,argument4 => NULL ,argument5 => NULL ,argument6 => 'N' ,argument7 => 'W'); SELECT status_code INTO v_old_status FROM fnd_concurrent_requests WHERE request_id = fnd_global.conc_request_id; UPDATE fnd_concurrent_requests SET status_code = 'W' WHERE request_id = fnd_global.conc_request_id; COMMIT; v_bool := fnd_concurrent.wait_for_request(v_conc_id ,5 ,1000 ,phase ,status ,dev_phase ,dev_status ,v_message); UPDATE fnd_concurrent_requests SET status_code = v_old_status WHERE request_id = fnd_global.conc_request_id; COMMIT; debug_stmt('Request id is ' || v_conc_id); IF (v_conc_id = 0) THEN /* If request not submitted, return false */ ROLLBACK TO gms_cost_xfer_gl_int; p_success_flag := FALSE; debug_stmt(' Returning false as request could not be submitted'); RETURN; END IF; END IF; p_success_flag := TRUE; EXCEPTION WHEN conversion_rate_does_not_exist THEN ROLLBACK TO gms_cost_xfer_gl_int; p_success_flag := FALSE; fnd_message.set_name('XTR' ,'XTR_2207'); fnd_message.set_token('CURR1' ,v_func_curr); fnd_message.set_token('CURR2' ,p_currency_code); fnd_message.set_token('XCHG_DATE' ,to_char(p_date)); fnd_message.set_token('C_TYPE' ,g_conversion_type); WHEN invalid_currency_code THEN ROLLBACK TO gms_cost_xfer_gl_int; p_success_flag := FALSE; fnd_message.set_name('SQLGL' ,'R_PPOS0026'); WHEN invalid_gl_source THEN ROLLBACK TO gms_cost_xfer_gl_int; p_success_flag := FALSE; fnd_message.set_name('SQLGL' ,'SHRD0152'); WHEN invalid_conversion_type THEN ROLLBACK TO gms_cost_xfer_gl_int; p_success_flag := FALSE; fnd_message.set_name('SQLGL' ,'GL_JE_INVALID_CONVERSION_TYPE'); WHEN not_in_open_period THEN ROLLBACK TO gms_cost_xfer_gl_int; p_success_flag := FALSE; fnd_message.set_name('SQLGL' ,'GL_JE_NOT_OPEN_OR_FUTURE_ENT'); WHEN invalid_cr_ccid THEN ROLLBACK TO gms_cost_xfer_gl_int; p_success_flag := FALSE; fnd_message.set_name('AR' ,'AR_AAPI_INVALID_CCID'); fnd_message.set_token('CCID' ,p_cr_account); WHEN invalid_dr_ccid THEN ROLLBACK TO gms_cost_xfer_gl_int; p_success_flag := FALSE; fnd_message.set_name('AR' ,'AR_AAPI_INVALID_CCID'); fnd_message.set_token('CCID' ,p_dr_account); WHEN OTHERS THEN ROLLBACK TO gms_cost_xfer_gl_int; p_success_flag := FALSE; fnd_message.set_name('FND' ,'FS-UNKNOWN'); fnd_message.set_token('ERROR' ,SQLERRM); END transfer_to_gl; END xx_import_xfer_gl_iface_api;