Tuesday, January 19, 2021

Import GL Daily Rates in Oracle Apps R12 (GL_DAILY_RATES_INTERFACE, GL_DAILY_RATES)

 

 

 

 

 

 

 

In this post, we have provided a base script which can be used to import daily currency conversion rates in general ledger

Interface Table: GL_DAILY_RATES_INTERFACE

Instance: R12

Script:

DECLARE lv_from_currency VARCHAR2(5) DEFAULT 'INR'; lv_to_currenty VARCHAR2(5) DEFAULT 'SEK'; ln_conversion_rate NUMBER DEFAULT 10; ln_batch_number NUMBER DEFAULT 1; ln_dummy_char NUMBER; lv_request_id NUMBER; lv_result BOOLEAN; lv_phase1 VARCHAR2(100); lv_status1 VARCHAR2(100); lv_dev_phase1 VARCHAR2(100); lv_dev_status1 VARCHAR2(100); lv_message1 VARCHAR2(100); BEGIN -- check whether the from currency exists and enabled BEGIN SELECT 1 INTO ln_dummy_char FROM fnd_currencies WHERE currency_flag = 'Y' AND enabled_flag = 'Y' AND currency_code = UPPER(lv_from_currency); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Error: From Currency is not valid'); END; -- check whether the from currency exists and enabled BEGIN SELECT 1 INTO ln_dummy_char FROM fnd_currencies WHERE currency_flag = 'Y' AND enabled_flag = 'Y' AND currency_code = UPPER(lv_to_currenty); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Error: To Currency is not valid'); END; -- Insert into Interface table INSERT INTO gl_daily_rates_interface (from_currency, to_currency, from_conversion_date, to_conversion_date, user_conversion_type, conversion_rate, mode_flag, inverse_conversion_rate, user_id, launch_rate_change, error_code, batch_number ) VALUES (lv_from_currency, lv_to_currenty, TO_DATE(SYSDATE,'DD-MON-RRRR'), TO_DATE(SYSDATE+30,'DD-MON-RRRR'), 'Corporate', ln_conversion_rate, 'I', (1/ln_conversion_rate), 0, NULL, NULL, ln_batch_number ); -- Initialize the enviroment fnd_global.apps_initialize ( user_id => 0 --User Id ,resp_id => 52480 --Responsibility Id ,resp_appl_id => 101 --Responsibility Application Id ); -- Submit Program - Daily Rates Import and Calculation lv_request_id := fnd_request.submit_request(application => 'SQLGL', program => 'GLDRICCP', description => NULL, start_time => NULL, sub_request => NULL, argument1 => ln_batch_number ); COMMIT; IF lv_request_id = 0 THEN DBMS_OUTPUT.PUT_LINE(' Failed to submit Process GLDRICCP.' || fnd_message.get ); ELSE lv_result := fnd_concurrent.wait_for_request(lv_request_id ,1 ,0 ,lv_phase1 ,lv_status1 ,lv_dev_phase1 ,lv_dev_status1 ,lv_message1 ); END IF; IF NOT lv_result THEN DBMS_OUTPUT.PUT_LINE('No Status returned for the request Id: ' || lv_request_id ); ELSE DBMS_OUTPUT.PUT_LINE('The Req-Id of GLDRICCP Process is ' || lv_request_id ); END IF; END
;

0 Responses to “Import GL Daily Rates in Oracle Apps R12 (GL_DAILY_RATES_INTERFACE, GL_DAILY_RATES)”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.