Showing posts with label Oracle General Ledger. Show all posts
Showing posts with label Oracle General Ledger. Show all posts

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
;

Tuesday, January 19, 2021 by Team search · 0

Friday, June 5, 2020

Query to fetch GL Budget Journals in Oracle Apps R12












Tested Instance: R12.2.4

 Tables Involved:

  1. GL_BC_PACKETS_HISTS
  2. GL_LEDGERS
  3. HR_OPERATING_UNITS
  4. GL_JE_BATCHES
  5. GL_JE_HEADERS
  6. GL_JE_LINES
  7. GL_CODE_COMBINATIONS_KFV
  8. FND_USER
  9. GL_PERIODS

Query:

SELECT gl.name ledger_name,
       hou.name operating_unit,
       hou.short_code,   
       (SELECT gbv.budget_name
          FROM gl_bc_packets_hists glbp,
               gl_budget_versions gbv
         WHERE glbp.je_batch_id = glb.je_batch_id
           AND glbp.je_header_id = glh.je_header_id
           AND glbp.je_line_num = gll.je_line_num
           AND glbp.budget_version_id = gbv.budget_version_id
       ) budget_name,
       gl.currency_code,
       gl.period_set_name,
       glh.period_name,
       fu.user_name budget_user,
       glh.creation_date,
       (SELECT je_batch_name
          FROM gl_bc_packets_hists glbp
         WHERE glbp.je_batch_id = glb.je_batch_id
           AND glbp.je_header_id = glh.je_header_id
           AND glbp.je_line_num = gll.je_line_num
       ) budget_batch_name,
       glb.name Journal_batch_name,       
       glh.name journal_name,  
       gcc.concatenated_segments charge_account,
       gll.entered_cr,
       gll.entered_dr,    
       glh.default_effective_date,       
       DECODE(gll.status,
             'P','POSTED',
             'U','UNPOSTED',
             gll.status
             ) posted_status,  
       glh.posted_date,
       gp.start_date period_start_date,
       gp.end_date period_end_date,
       gp.period_year,
       gp.period_num,
       gp.quarter_num,
       hou.organization_id,
       gll.je_line_num,
       gll.je_header_id       
  FROM gl_ledgers gl,
       hr_operating_units hou,
       gl_je_batches glb,
       gl_je_headers glh,
       gl_je_lines gll,
       gl_code_combinations_kfv gcc,
       fnd_user fu,
       gl_periods gp
  WHERE glh.je_source = 'Budget Journal'
    AND glh.je_category = 'Budget'
    AND glb.je_batch_id = glh.je_batch_id
    AND glh.actual_flag = 'B'
    AND glh.je_header_id = gll.je_header_id
    AND gcc.code_combination_id = gll.code_combination_id
    AND fu.user_id = glh.created_by
    AND gp.period_name = glh.period_name
    AND gl.period_set_name = gp.period_set_name
    AND glh.ledger_id = gl.ledger_id
    AND hou.set_of_books_id = gl.ledger_id;

Friday, June 5, 2020 by Team search · 0

Saturday, May 16, 2020

Query to get responsibility wise valid COA segments post applying Security rules (fnd_flex_value_rules)











            In this post, we have shared a query which will help you to get the list of valid values of accounting flexifield post applying the security rules for current responsibility.

Key Security Profile Tables:

  1. FND_FLEX_VALUE_RULE_USAGES
  2. FND_FLEX_VALUE_RULES
  3. FND_FLEX_VALUE_RULE_LINES

If you want to know more about above tables and how they are joined, please visit my old post. Security Rules Table Description

Basic Table level Architecture:

Query (R12.2.4): 

-- Query for segments where security rules where defined
SELECT hou.short_code,
       hou.organization_id,
       FND_PROFILE.VALUE('RESP_ID') resp_id,
       hou.set_of_books_id ledger_id,
       gl.chart_of_accounts_id,
       fifs.segment_name,
       fifs.application_column_name,
       ffv.flex_value,
       ffv.description
  FROM hr_operating_units hou,
       gl_ledgers gl,
       fnd_id_flex_segments_vl fifs,
       fnd_flex_values_vl ffv,
       fnd_flex_value_rule_usages fvru,
       fnd_flex_value_rules fvr,
       fnd_flex_value_rule_lines fvrl
 WHERE hou.set_of_books_id = gl.ledger_id
   AND fifs.id_flex_num = gl.chart_of_accounts_id
   AND fifs.id_flex_code = 'GL#'
   AND fifs.enabled_flag = 'Y'
   AND fifs.flex_value_set_id = ffv.flex_value_set_id
   AND ffv.enabled_flag = 'Y'
   AND NVL(ffv.summary_flag,'N') = 'N'
   AND SYSDATE BETWEEN hou.date_from AND NVL(hou.date_to,SYSDATE+1)
   AND fvru.responsibility_id  = FND_PROFILE.VALUE('RESP_ID') -- For current resp
   AND fvru.flex_value_rule_id = fvr.flex_value_rule_id
   AND fvrl.flex_value_rule_id = fvr.flex_value_rule_id
   AND ffv.flex_value_set_id   = fvr.flex_value_set_id
   AND 1 = (CASE 
        WHEN fvrl.include_exclude_indicator = 'E' 
             AND (ffv.flex_value BETWEEN fvrl.flex_value_low AND fvrl.flex_value_high)
        THEN 2
        WHEN fvrl.include_exclude_indicator = 'I' 
             AND (ffv.flex_value BETWEEN fvrl.flex_value_low AND fvrl.flex_value_high)
             -- for some ou's first all are included and then some of 
             -- them are excluded. Hence, below logic is required
             AND NOT EXISTS (SELECT 1
                               FROM fnd_flex_value_rule_lines fvrl2
                              WHERE fvrl2.flex_value_rule_id = fvrl.flex_value_rule_id
                                AND fvrl2.include_exclude_indicator = 'E' 
                                AND (ffv.flex_value BETWEEN fvrl2.flex_value_low 
                                                        AND fvrl2.flex_value_high
                                    )
                            )
        THEN 1  
        ELSE 2      
        END
       )
UNION ALL
-- Query for segments where no security rules where defined
SELECT hou.short_code,
       hou.organization_id,
       FND_PROFILE.VALUE('RESP_ID') resp_id,
       hou.set_of_books_id ledger_id,
       gl.chart_of_accounts_id,
       fifs.segment_name,
       fifs.application_column_name,
       ffv.flex_value,
       ffv.description
  FROM hr_operating_units hou,
       gl_ledgers gl,
       fnd_id_flex_segments_vl fifs,
       fnd_flex_values_vl ffv
 WHERE hou.set_of_books_id = gl.ledger_id
   AND fifs.id_flex_num = gl.chart_of_accounts_id
   AND fifs.id_flex_code = 'GL#'
   AND fifs.enabled_flag = 'Y'
   AND fifs.flex_value_set_id = ffv.flex_value_set_id
   AND NVL(ffv.summary_flag,'N') = 'N'
   AND ffv.enabled_flag = 'Y'
   AND SYSDATE BETWEEN hou.date_from AND NVL(hou.date_to,SYSDATE+1)
   AND NOT EXISTS ( SELECT 1
                      FROM fnd_flex_value_rule_usages ffvu
                     WHERE ffvu.responsibility_id = FND_PROFILE.VALUE('RESP_ID')
                       AND ffvu.flex_value_set_id = ffv.flex_value_set_id
                  )

Saturday, May 16, 2020 by Team search · 0

Subscribe to: Posts (Atom)

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.


All Rights Reserved Oracle Apps Knowledge Sharing | Home by Team Search