Oracle Apps Knowledge Sharing

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
;