Scripts for Oracle Workflow Training Lesson 3 The wft file for Oracle Workflow Training Lesson 3 can be downloaded by right clicking here It is assumed that you have already worked upon the scripts from Training lesson 2. --Create the table in which transaction progress will be tracked create table xx_po_wf_progress ( po_id INTEGER ,p_wf_action_stage VARCHAR2(200) ,p_wf_action_name VARCHAR2(200) ) ; --Create the pl/sql package CREATE OR REPLACE PACKAGE xxxx_po_wf_training_pkg IS PROCEDURE is_po_valid(itemtype IN VARCHAR2 ,itemkey IN VARCHAR2 ,actid IN NUMBER ,funcmode IN VARCHAR2 ,RESULT IN OUT VARCHAR2); PROCEDURE set_wf_approver_role(itemtype IN VARCHAR2 ,itemkey IN VARCHAR2 ,actid IN NUMBER ,funcmode IN VARCHAR2 ,RESULT IN OUT VARCHAR2); PROCEDURE set_wf_status_to_validated(itemtype IN VARCHAR2 ,itemkey IN VARCHAR2 ,actid IN NUMBER ,funcmode IN VARCHAR2 ,RESULT IN OUT VARCHAR2); PROCEDURE start_training_wf(p_po_id IN INTEGER); PROCEDURE xx_validate_response(itemtype IN VARCHAR2 ,itemkey IN VARCHAR2 ,actid IN NUMBER ,funcmode IN VARCHAR2 ,RESULT IN OUT VARCHAR2); PROCEDURE log_status_of_action(itemtype IN VARCHAR2 ,itemkey IN VARCHAR2 ,actid IN NUMBER ,funcmode IN VARCHAR2 ,RESULT IN OUT VARCHAR2); END xxxx_po_wf_training_pkg; / --create the package body CREATE OR REPLACE PACKAGE BODY xxxx_po_wf_training_pkg IS PROCEDURE debug(p_text IN VARCHAR2) IS BEGIN -- apassi1(p_text); NULL; END debug; FUNCTION is_po_validated(p_po_id IN INTEGER) RETURN BOOLEAN IS BEGIN IF p_po_id > 0 THEN RETURN TRUE; END IF; RETURN FALSE; END is_po_validated; PROCEDURE is_po_valid(itemtype IN VARCHAR2 ,itemkey IN VARCHAR2 ,actid IN NUMBER ,funcmode IN VARCHAR2 ,RESULT IN OUT VARCHAR2) IS BEGIN IF (funcmode != 'RUN') THEN RETURN; END IF; --Just a pseudo validation --call a function here, and either assign COMPLETE:Y or COMPLETE:N IF is_po_validated(p_po_id => wf_engine.getitemattrnumber(itemtype => itemtype ,itemkey => itemkey ,aname => 'PO_ID')) THEN RESULT := 'COMPLETE:Y'; ELSE RESULT := 'COMPLETE:N'; END IF; END is_po_valid; PROCEDURE set_wf_approver_role(itemtype IN VARCHAR2 ,itemkey IN VARCHAR2 ,actid IN NUMBER ,funcmode IN VARCHAR2 ,RESULT IN OUT VARCHAR2) IS v_role_email xxxx_po_headers.send_email_to%TYPE; n_ctr INTEGER := 0; BEGIN v_role_email := upper(wf_engine.getitemattrtext(itemtype => itemtype ,itemkey => itemkey ,aname => 'SEND_TO_EMAIL')); SELECT COUNT(*) INTO n_ctr FROM wf_local_roles WHERE NAME = v_role_email; IF n_ctr = 0 THEN wf_directory.createadhocrole(role_name => v_role_email ,role_display_name => v_role_email ,role_description => v_role_email ,notification_preference => 'MAILHTML' ,email_address => v_role_email ,status => 'ACTIVE' ,expiration_date => NULL); END IF; wf_engine.setitemattrtext(itemtype => itemtype ,itemkey => itemkey ,aname => 'SEND_TO_ROLE' ,avalue => v_role_email); RESULT := 'COMPLETE:Y'; RESULT := 'COMPLETE:Y'; END set_wf_approver_role; PROCEDURE set_wf_status_to_validated(itemtype IN VARCHAR2 ,itemkey IN VARCHAR2 ,actid IN NUMBER ,funcmode IN VARCHAR2 ,RESULT IN OUT VARCHAR2) IS BEGIN UPDATE xxxx_po_headers SET po_status = 'VALIDATED' WHERE po_id = wf_engine.getitemattrnumber(itemtype ,itemkey ,'PO_ID'); RESULT := 'COMPLETE:Y'; END set_wf_status_to_validated; PROCEDURE start_training_wf(p_po_id IN INTEGER) IS l_itemtype VARCHAR2(30) := 'XXXXPTR'; l_itemkey VARCHAR2(300) := 'TRAINING-' || p_po_id; CURSOR c_get IS SELECT * FROM xxxx_po_headers WHERE po_id = p_po_id; p_get c_get%ROWTYPE; BEGIN OPEN c_get; FETCH c_get INTO p_get; CLOSE c_get; wf_engine.createprocess(l_itemtype ,l_itemkey ,'MAIN_TRAINING_PROCESS'); wf_engine.setitemuserkey(itemtype => l_itemtype ,itemkey => l_itemkey ,userkey => 'USERKEY: ' || l_itemkey); wf_engine.setitemowner(itemtype => l_itemtype ,itemkey => l_itemkey ,owner => 'SYSADMIN'); wf_engine.setitemattrnumber(itemtype => l_itemtype ,itemkey => l_itemkey ,aname => 'PO_ID' ,avalue => p_po_id); wf_engine.setitemattrtext(itemtype => l_itemtype ,itemkey => l_itemkey ,aname => 'SEND_TO_EMAIL' ,avalue => p_get.send_email_to); wf_engine.setitemattrtext(itemtype => l_itemtype ,itemkey => l_itemkey ,aname => 'PO_DESCRIPTION' ,avalue => p_get.po_description); wf_engine.startprocess(l_itemtype ,l_itemkey); END start_training_wf; PROCEDURE xx_validate_response(itemtype IN VARCHAR2 ,itemkey IN VARCHAR2 ,actid IN NUMBER ,funcmode IN VARCHAR2 ,RESULT IN OUT VARCHAR2) IS l_nid NUMBER; l_activity_result_code VARCHAR2(200); v_response_reason VARCHAR2(50); BEGIN IF (funcmode IN ('RESPOND')) THEN l_nid := wf_engine.context_nid; l_activity_result_code := wf_notification.getattrtext(l_nid ,'XX_RESPONSE_ACTION'); v_response_reason := wf_notification.getattrtext(l_nid ,'XX_RESPONSE_REASON'); IF l_activity_result_code = 'XX_REJECTED' AND v_response_reason IS NULL THEN RESULT := 'ERROR: You must enter rejection reason if rejecting.'; RETURN; END IF; END IF; EXCEPTION WHEN OTHERS THEN RESULT := SQLERRM; END xx_validate_response; FUNCTION get_request_id_from_item_key(p_item_key IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN substr(p_item_key ,instr(p_item_key ,'-') + 1); END get_request_id_from_item_key; PROCEDURE log_status_of_action(itemtype IN VARCHAR2 ,itemkey IN VARCHAR2 ,actid IN NUMBER ,funcmode IN VARCHAR2 ,RESULT IN OUT VARCHAR2) IS n_po_id INTEGER; v_action_name VARCHAR2(200); v_action_stage VARCHAR2(200); BEGIN IF funcmode = 'RUN' THEN n_po_id := get_request_id_from_item_key(itemkey); v_action_stage := wf_engine.getactivityattrtext(itemtype => itemtype ,itemkey => itemkey ,actid => actid ,aname => 'ACTION_STAGE'); v_action_name := wf_engine.getactivityattrtext(itemtype => itemtype ,itemkey => itemkey ,actid => actid ,aname => 'ACTION_NAME'); INSERT INTO xx_po_wf_progress (po_id ,p_wf_action_stage ,p_wf_action_name) VALUES (n_po_id ,v_action_stage ,v_action_name); END IF; RESULT := 'COMPLETE:Y'; END log_status_of_action; END xxxx_po_wf_training_pkg; / --initiate the workflow now DECLARE n_new_po_id INTEGER; BEGIN SELECT oe_order_headers_s.NEXTVAL INTO n_new_po_id FROM dual; INSERT INTO xxxx_po_headers (po_id ,vendor_id ,suggested_vendor_id ,po_description ,po_status ,comments_from_approver ,send_email_to) VALUES (n_new_po_id --po_id ,10000 --vendor_id ,NULL --suggested_vendor_id ,'This is PO Training Description' --po_description ,'INITIAL' --po_status ,NULL --comments_from_approver ,'ANILPASSI@GMAIL.com' --send_email_to ); xxxx_po_wf_training_pkg.start_training_wf(p_po_id => n_new_po_id); COMMIT; END; /

Scripts for Workflow Training Lesson 2 This page contains all the required Scripts and Workflow files prepared for Workflow Training Lesson 2. Click here if you wish to return to Oracle Workflow Training Lesson 2 Workflow .wft File For Training Lesson 2 Rightclick to download the above file and save this to database. --create the table Create table xxxx_po_headers ( po_id INTEGER ,vendor_id INTEGER ,suggested_vendor_id INTEGER ,po_description VARCHAR2(250) ,po_status VARCHAR2(30) ,comments_from_approver VARCHAR2(2000) ,send_email_to VARCHAR2(300) ) ; ----------------- --Create the package ----------------- CREATE OR REPLACE PACKAGE xxxx_po_wf_training_pkg IS PROCEDURE is_po_valid ( itemtype IN VARCHAR2 ,itemkey IN VARCHAR2 ,actid IN NUMBER ,funcmode IN VARCHAR2 ,RESULT IN OUT VARCHAR2 ); PROCEDURE set_wf_approver_role ( itemtype IN VARCHAR2 ,itemkey IN VARCHAR2 ,actid IN NUMBER ,funcmode IN VARCHAR2 ,RESULT IN OUT VARCHAR2 ); PROCEDURE set_wf_status_to_validated ( itemtype IN VARCHAR2 ,itemkey IN VARCHAR2 ,actid IN NUMBER ,funcmode IN VARCHAR2 ,RESULT IN OUT VARCHAR2 ); PROCEDURE start_training_wf(p_po_id IN INTEGER); PROCEDURE xx_validate_response ( itemtype IN VARCHAR2 ,itemkey IN VARCHAR2 ,actid IN NUMBER ,funcmode IN VARCHAR2 ,RESULT IN OUT VARCHAR2 ); END xxxx_po_wf_training_pkg; / -------------------- --Create the package body -------------------- CREATE OR REPLACE PACKAGE BODY xxxx_po_wf_training_pkg IS PROCEDURE debug(p_text IN VARCHAR2) IS BEGIN -- apassi1(p_text); NULL; END debug; FUNCTION is_po_validated(p_po_id IN INTEGER) RETURN BOOLEAN IS BEGIN IF p_po_id > 0 THEN RETURN TRUE; END IF; RETURN FALSE; END is_po_validated; PROCEDURE is_po_valid(itemtype IN VARCHAR2 ,itemkey IN VARCHAR2 ,actid IN NUMBER ,funcmode IN VARCHAR2 ,RESULT IN OUT VARCHAR2) IS BEGIN IF (funcmode != 'RUN') THEN RETURN; END IF; --Just a pseudo validation --call a function here, and either assign COMPLETE:Y or COMPLETE:N IF is_po_validated(p_po_id => wf_engine.getitemattrnumber(itemtype => itemtype ,itemkey => itemkey ,aname => 'PO_ID')) THEN RESULT := 'COMPLETE:Y'; ELSE RESULT := 'COMPLETE:N'; END IF; END is_po_valid; PROCEDURE set_wf_approver_role(itemtype IN VARCHAR2 ,itemkey IN VARCHAR2 ,actid IN NUMBER ,funcmode IN VARCHAR2 ,RESULT IN OUT VARCHAR2) IS v_role_email xxxx_po_headers.send_email_to%TYPE; n_ctr INTEGER := 0; BEGIN v_role_email := upper(wf_engine.getitemattrtext(itemtype => itemtype ,itemkey => itemkey ,aname => 'SEND_TO_EMAIL')); SELECT COUNT(*) INTO n_ctr FROM wf_local_roles WHERE NAME = v_role_email; IF n_ctr = 0 THEN wf_directory.createadhocrole(role_name => v_role_email ,role_display_name => v_role_email ,role_description => v_role_email ,notification_preference => 'MAILHTML' ,email_address => v_role_email ,status => 'ACTIVE' ,expiration_date => NULL); END IF; wf_engine.setitemattrtext(itemtype => itemtype ,itemkey => itemkey ,aname => 'SEND_TO_ROLE' ,avalue => v_role_email); RESULT := 'COMPLETE:Y'; RESULT := 'COMPLETE:Y'; END set_wf_approver_role; PROCEDURE set_wf_status_to_validated(itemtype IN VARCHAR2 ,itemkey IN VARCHAR2 ,actid IN NUMBER ,funcmode IN VARCHAR2 ,RESULT IN OUT VARCHAR2) IS BEGIN UPDATE xxxx_po_headers SET po_status = 'VALIDATED' WHERE po_id = wf_engine.getitemattrnumber(itemtype ,itemkey ,'PO_ID'); RESULT := 'COMPLETE:Y'; END set_wf_status_to_validated; PROCEDURE start_training_wf(p_po_id IN INTEGER) IS l_itemtype VARCHAR2(30) := 'XXXXPTR'; l_itemkey VARCHAR2(300) := 'TRAINING-' || p_po_id; CURSOR c_get IS SELECT * FROM xxxx_po_headers WHERE po_id = p_po_id; p_get c_get%ROWTYPE; BEGIN OPEN c_get; FETCH c_get INTO p_get; CLOSE c_get; wf_engine.createprocess(l_itemtype ,l_itemkey ,'MAIN_TRAINING_PROCESS'); wf_engine.setitemuserkey(itemtype => l_itemtype ,itemkey => l_itemkey ,userkey => 'USERKEY: ' || l_itemkey); wf_engine.setitemowner(itemtype => l_itemtype ,itemkey => l_itemkey ,owner => 'SYSADMIN'); wf_engine.setitemattrnumber(itemtype => l_itemtype ,itemkey => l_itemkey ,aname => 'PO_ID' ,avalue => p_po_id); wf_engine.setitemattrtext(itemtype => l_itemtype ,itemkey => l_itemkey ,aname => 'SEND_TO_EMAIL' ,avalue => p_get.send_email_to); wf_engine.setitemattrtext(itemtype => l_itemtype ,itemkey => l_itemkey ,aname => 'PO_DESCRIPTION' ,avalue => p_get.po_description); wf_engine.startprocess(l_itemtype ,l_itemkey); END start_training_wf; PROCEDURE xx_validate_response(itemtype IN VARCHAR2 ,itemkey IN VARCHAR2 ,actid IN NUMBER ,funcmode IN VARCHAR2 ,RESULT IN OUT VARCHAR2) IS l_nid NUMBER; l_activity_result_code VARCHAR2(200); v_response_reason VARCHAR2(50); BEGIN debug('funcmode is ' || funcmode); IF (funcmode IN ('RESPOND')) THEN l_nid := wf_engine.context_nid; l_activity_result_code := wf_notification.getattrtext(l_nid ,'XX_RESPONSE_ACTION'); debug('l_activity_result_code is ' || l_activity_result_code); v_response_reason := wf_notification.getattrtext(l_nid ,'XX_RESPONSE_REASON'); debug('v_response_reason is ' || v_response_reason); IF l_activity_result_code = 'XX_REJECTED' AND v_response_reason IS NULL THEN RESULT := 'ERROR: You must enter rejection reason if rejecting.'; RETURN; END IF; END IF; EXCEPTION WHEN OTHERS THEN RESULT := SQLERRM; END xx_validate_response; END xxxx_po_wf_training_pkg; / ------ --Now Run the Workflow --Ensure that wft file has been loaded ------ DECLARE n_new_po_id INTEGER; BEGIN SELECT oe_order_headers_s.NEXTVAL INTO n_new_po_id FROM dual; INSERT INTO xxxx_po_headers (po_id ,vendor_id ,suggested_vendor_id ,po_description ,po_status ,comments_from_approver ,send_email_to) VALUES (n_new_po_id --po_id ,10000 --vendor_id ,NULL --suggested_vendor_id ,'This is PO Training Description' --po_description ,'INITIAL' --po_status ,NULL --comments_from_approver ,'ANILPASSI@GMAIL.com' --send_email_to ); xxxx_po_wf_training_pkg.start_training_wf(p_po_id => n_new_po_id); COMMIT; END; /

Oracle Apps FND User name Locks Record Ever wondered how to find the person in Oracle Apps that locks a specific record? Its simple, I find this SQL quite useful. Blaah, you will know the user that is sleeping over the record by locking it. This SQL joins the locking session to FND_USER, so as to display the APPS User Name of the person that locks the record. In the example below, lets say we need to find the list of people that are locking tables in Purchasing. Aslo, lets assume we need to find the screen/conc process that locks the record. SELECT c.owner ,c.object_name ,c.object_type ,fu.user_name locking_user_name ,fl.start_time ,vs.module ,vs.machine ,vs.osuser ,vs.sid ,vp.pid ,vp.spid AS os_process_to_kill ,vs.serial# ,vs.status ,vs.saddr ,vs.audsid ,vs.process FROM fnd_logins fl ,fnd_user fu ,v$locked_object vlocked ,v$process vp ,v$session vs ,dba_objects c WHERE vs.sid = vlocked.session_id AND vlocked.object_id = c.object_id AND vs.paddr = vp.addr AND vp.spid = fl.process_spid(+) AND vp.pid = fl.pid(+) AND fl.user_id = fu.user_id(+) AND c.object_name LIKE 'PO%'; OWNER PO OBJECT_NAME PO_HEADERS_ALL OBJECT_TYPE TABLE LOCKING_USER_NAME PASSIANIL START_TIME 26-SEP-2006 MODULE POXPOEPO MACHINE xxappsserver01 OSUSER Xxtstuser SID 2785 PID 661 OS_PROCESS_TO_KILL 27975 SERIAL# 5518 STATUS INACTIVE SADDR 000000077DD814F0 AUDSID 13482253 PROCESS 3563

Oracle APPS Warn Bounce of Environment to Recent Users Very often, we find ourselves in a need to bounce the development or test environment. This takes some developers/testers etc by surprise. Hence I developed a simple script to list the list of recent users that use the Oracle Apps instance. We can then email the people returned by this list, to inform them of the system unavailability. SELECT ppx.full_name ,fu.user_name ,nvl(ppx.email_address ,fu.email_address) AS email_address FROM per_people_x ppx, fnd_user fu, fnd_logins fl WHERE fl.start_time > SYSDATE - 2 AND fu.user_id = fl.user_id AND ppx.person_id(+) = fu.employee_id AND fu.user_name NOT IN ('INTERFACE', 'SYSADMIN', 'GUEST') AND fu.user_name != 'PASSIA' --exclude anil GROUP BY ppx.full_name ,fu.user_name ,nvl(ppx.email_address ,fu.email_address) ORDER BY 2

Oracle Activity on FND LOGINS FND USER My client asked me to write a quick SQL to find out the load on the Production system, in terms of User activity. This was required for be broken down by External Logins(iSourcing, Students etc) and Internal Employees. Use the SQL below =============== SELECT COUNT(fl.login_id) count_of_logins ,trunc(fl.start_time) start_date ,COUNT(DISTINCT fl.user_id) count_users ,decode(fu.employee_id ,NULL ,'EXTERNAL' ,'INTERNAL') AS emp_type FROM fnd_user fu, fnd_logins fl WHERE fl.start_time > SYSDATE - 10 AND fl.user_id NOT IN (6, 0, -1) AND fu.user_id = fl.user_id GROUP BY trunc(start_time) ,decode(fu.employee_id ,NULL ,'EXTERNAL' ,'INTERNAL') ORDER BY 2 In fact after running this, I discovered that OAM gives a better breakdown, i.e. it will list the breakdown at Module level. That list will be available from Sitemap Tab, after having logged into OAM ( Oracle Applications Manager ).

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;

Oracle API for GL Interface Package Header CREATE OR REPLACE PACKAGE xx_import_xfer_gl_iface_api /* $Header: XXXXXXXX.pkh, Version 1.0, 07-FEB-2002 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 * * Created : 11-OCT-2002 * * Description : This package gives API for GL interface calls * * * * Change History Log * * ================== * * * * Version Date Author Change * * ======= =========== ============ ================================* * * * 1.0 11-OCT-2002 A. Passi Initial Version * * * * Parameters : * * * **********************************************************************/ AUTHID CURRENT_USER IS PROCEDURE set_sob_id(p_sob_id IN INTEGER); PROCEDURE set_source_name(p_source_name IN VARCHAR2); 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); TYPE g_gl_int_type_rec IS RECORD( reference_date DATE ,attribute20 VARCHAR2(150) ,CONTEXT VARCHAR2(150) ,context2 VARCHAR2(150) ,invoice_date DATE ,tax_code VARCHAR2(15) ,invoice_identifier VARCHAR2(20) ,invoice_amount NUMBER ,context3 VARCHAR2(150) ,ussgl_transaction_code VARCHAR2(30) ,descr_flex_error_message VARCHAR2(240) ,jgzz_recon_ref VARCHAR2(240) ,segment23 VARCHAR2(25) ,segment24 VARCHAR2(25) ,segment25 VARCHAR2(25) ,segment26 VARCHAR2(25) ,segment27 VARCHAR2(25) ,segment28 VARCHAR2(25) ,segment29 VARCHAR2(25) ,segment30 VARCHAR2(25) ,entered_dr NUMBER ,entered_cr NUMBER ,accounted_dr NUMBER ,accounted_cr NUMBER ,transaction_date DATE ,reference1 VARCHAR2(100) ,reference2 VARCHAR2(240) ,reference3 VARCHAR2(100) ,reference4 VARCHAR2(100) ,reference5 VARCHAR2(240) ,reference6 VARCHAR2(100) ,reference7 VARCHAR2(100) ,reference8 VARCHAR2(100) ,reference9 VARCHAR2(100) ,reference10 VARCHAR2(240) ,reference11 VARCHAR2(100) ,reference12 VARCHAR2(100) ,reference13 VARCHAR2(100) ,reference14 VARCHAR2(100) ,reference15 VARCHAR2(100) ,reference16 VARCHAR2(100) ,reference17 VARCHAR2(100) ,reference18 VARCHAR2(100) ,reference19 VARCHAR2(100) ,reference20 VARCHAR2(100) ,reference21 VARCHAR2(240) ,reference22 VARCHAR2(240) ,reference23 VARCHAR2(240) ,reference24 VARCHAR2(240) ,reference25 VARCHAR2(240) ,reference26 VARCHAR2(240) ,reference27 VARCHAR2(240) ,reference28 VARCHAR2(240) ,reference29 VARCHAR2(240) ,reference30 VARCHAR2(240) ,je_batch_id NUMBER ,period_name VARCHAR2(15) ,je_header_id NUMBER ,je_line_num NUMBER ,chart_of_accounts_id NUMBER ,functional_currency_code VARCHAR2(15) ,code_combination_id NUMBER ,date_created_in_gl DATE ,warning_code VARCHAR2(4) ,status_description VARCHAR2(240) ,stat_amount NUMBER ,group_id NUMBER ,request_id NUMBER ,subledger_doc_sequence_id NUMBER ,subledger_doc_sequence_value NUMBER ,attribute1 VARCHAR2(150) ,attribute2 VARCHAR2(150) ,gl_sl_link_id NUMBER ,gl_sl_link_table VARCHAR2(30) ,attribute3 VARCHAR2(150) ,attribute4 VARCHAR2(150) ,attribute5 VARCHAR2(150) ,attribute6 VARCHAR2(150) ,attribute7 VARCHAR2(150) ,attribute8 VARCHAR2(150) ,attribute9 VARCHAR2(150) ,attribute10 VARCHAR2(150) ,attribute11 VARCHAR2(150) ,attribute12 VARCHAR2(150) ,attribute13 VARCHAR2(150) ,attribute14 VARCHAR2(150) ,attribute15 VARCHAR2(150) ,attribute16 VARCHAR2(150) ,attribute17 VARCHAR2(150) ,attribute18 VARCHAR2(150) ,attribute19 VARCHAR2(150) ,status VARCHAR2(50) ,set_of_books_id NUMBER ,accounting_date DATE ,currency_code VARCHAR2(15) ,date_created DATE ,created_by NUMBER ,actual_flag VARCHAR2(1) ,user_je_category_name VARCHAR2(25) ,user_je_source_name VARCHAR2(25) ,currency_conversion_date DATE ,encumbrance_type_id NUMBER ,budget_version_id NUMBER ,user_currency_conversion_type VARCHAR2(30) ,currency_conversion_rate NUMBER ,average_journal_flag VARCHAR2(1) ,originating_bal_seg_value VARCHAR2(25) ,segment1 VARCHAR2(25) ,segment2 VARCHAR2(25) ,segment3 VARCHAR2(25) ,segment4 VARCHAR2(25) ,segment5 VARCHAR2(25) ,segment6 VARCHAR2(25) ,segment7 VARCHAR2(25) ,segment8 VARCHAR2(25) ,segment9 VARCHAR2(25) ,segment10 VARCHAR2(25) ,segment11 VARCHAR2(25) ,segment12 VARCHAR2(25) ,segment13 VARCHAR2(25) ,segment14 VARCHAR2(25) ,segment15 VARCHAR2(25) ,segment16 VARCHAR2(25) ,segment17 VARCHAR2(25) ,segment18 VARCHAR2(25) ,segment19 VARCHAR2(25) ,segment20 VARCHAR2(25) ,segment21 VARCHAR2(25) ,segment22 VARCHAR2(25)); PROCEDURE insert_statement(p_gl_int_rec IN OUT NOCOPY g_gl_int_type_rec); 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); PROCEDURE new_gl_interface_batch; FUNCTION get_gl_je_reference1(p_batch_header_id IN INTEGER ,p_trx_number IN VARCHAR2) RETURN VARCHAR2; FUNCTION get_gl_application_id(p_appl_name IN VARCHAR2) RETURN INTEGER; END xx_import_xfer_gl_iface_api;