Monday, April 25, 2016

API to fetch Accounting Key Flexfield Concatenated Segment description in Oracle Apps R12













Tested Instance: R12.2.4

API:  GL_FLEXFIELDS_PKG.GET_CONCAT_DESCRIPTION

Query: 

SELECT gcc.concatenated_segments,
       gl_flexfields_pkg.get_concat_description( gcc.chart_of_accounts_id,
                                                 gcc.code_combination_id
                                               ) acc_description
  FROM gl_code_combinations_kfv gcc;
 

Hope This helps!!


Monday, April 25, 2016 by Team search · 0

Monday, April 18, 2016

API to update HR Jobs in oracle apps R12 (PER_JOBS) (HR_JOB_API.UPDATE_JOB)












This post is for Updating JOBS in HR Module. I have prepared a small script which can used to update the JOBS in Oracle Apps R12. Before we get into script, let have a brief introduction to related tables.


  1. PER_JOB_GROUPS is used to group records contained in PER_JOBS. Each Job Group has to be within a business group unless the profile option HR: Cross Business Group Profile is set to 'N' in which case a Job Group can have a NULL Business Group (considered to be a Global Job Group). Jobs in Global Job Groups can be viewed across the system. All HR jobs, i.e., those jobs used within the HRMS system, must be defined within a 'Default HR Job Group'. The default HR Job Group is created at the same time a Business Group is created.
  2. PER_JOBS holds jobs that have been defined for a Business Group. The
    NAME is a concatenation of key flexfield segments, held in PER_JOB_DEFINITIONS. Jobs define the role that an employee can perform in the business group, and they are independent of specific organizations. Each Job is contained within a Job Group.
  3. PER_JOBS_TL: Translated columns for per_jobs
  4. PER_JOB_DEFINITIONS is a key flexfield combinations table. It holds
    the segment combinations for jobs that are stored in PER_JOBS. Oracle
    Applications do not support code combinations IDs that exceed 2,000,000,000.

Tested Version: R12.2.4

Script:
DECLARE
  lv_lang                  VARCHAR2(50)   DEFAULT 'US';
  lv_job                   VARCHAR2(2000) DEFAULT 'SHARE.ORACLEAPPS';
  lv_new_job_name          VARCHAR2(200)  DEFAULT 'SHARE.ORACLEAPPSNEW';
 
  ln_business_group_id          NUMBER;
  ln_job_group_id               NUMBER; 
  ln_job_id                     NUMBER;
  ln_object_version_number      NUMBER;
  ln_job_definition_id          NUMBER;
  lb_valid_grades_changed_warn  BOOLEAN;
 
BEGIN
 
  -- get job details
  BEGIN
    SELECT job_id,
           job_definition_id,
           object_version_number
      INTO ln_job_id,
           ln_job_definition_id,
           ln_object_version_number
      FROM per_jobs
     WHERE name = lv_job;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Unable to get the job details.Error:'||SQLERRM);
      RAISE;
  END;

  HR_JOB_API.UPDATE_JOB
  (p_validate                      => FALSE
  ,p_job_id                        => ln_job_id
  ,p_object_version_number         => ln_object_version_number
  ,p_comments                      => 'TEST JOB Updated'
  ,p_concat_segments               => lv_new_job_name
  ,p_language_code                 => lv_lang
  ,p_job_definition_id             => ln_job_definition_id
  ,p_name                          => lv_job
  ,p_valid_grades_changed_warning  => lb_valid_grades_changed_warn 
  ,p_effective_date                => SYSDATE
  );
 
  DBMS_OUTPUT.PUT_LINE('JOB ID: '||ln_job_id);
  DBMS_OUTPUT.PUT_LINE('OBJECT_VERSION_NUMBER: '||ln_object_version_number);
  DBMS_OUTPUT.PUT_LINE('JOB_DEFINITION_ID: '||ln_job_definition_id);
  DBMS_OUTPUT.PUT_LINE('JOB_NAME: '||lv_job);
EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Unable to create a job.Error:'||SQLERRM);
      RAISE; 
END;
/



Happy Sharing!! Enjoy Learning!!

 

Monday, April 18, 2016 by Team search · 0

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.