COA-- Value Set Information

COA-- Value Set Information

select
        FIFS.ID_FLEX_STRUCTURE_CODE  ,
        FIFSE.SEGMENT_NAME,
        FIFSE.SEGMENT_NUM,
        FIFSE.FLEX_VALUE_SET_ID,
         FIF.APPLICATION_ID  ,
        FIF.ID_FLEX_CODE    ,
        FIF.ID_FLEX_NAME    ,
        FIF.APPLICATION_TABLE_NAME ,
        FIF.DESCRIPTION     ,
        FIFS.ID_FLEX_NUM   
from    FND_ID_FLEXS FIF    ,
        FND_ID_FLEX_STRUCTURES FIFS ,
        FND_ID_FLEX_SEGMENTS FIFSE
where   FIF.APPLICATION_ID = FIFS.APPLICATION_ID
and     FIF.ID_FLEX_CODE   = FIFS.ID_FLEX_CODE
and     FIFSE.APPLICATION_ID = FIF.APPLICATION_ID
and     FIFSE.ID_FLEX_CODE = FIF.ID_FLEX_CODE
and     FIFSE.ID_FLEX_NUM = FIFS.ID_FLEX_NUM
and     FIF.ID_FLEX_CODE LIKE 'GL#'
and     FIF.ID_FLEX_NAME LIKE 'Accounting Flexfield'
order by SEGMENT_NUM

API to Create Locations

 API to Create Locations:
 ------------------------------------
CREATE OR REPLACE PROCEDURE APPS.xxmfz_create_location
IS
   l_row_id              VARCHAR2 (500) := NULL;
   l_location_id   NUMBER := NULL;
   l_object_version_number       NUMBER := NULL;
   l_err_msg             VARCHAR2 (500) := NULL;
   l_db_nls_language     VARCHAR2 (500) := NULL;
  
    --  declare
        ex_loc_id number;
        ex_object_version_number number;

   CURSOR fetch_details
   IS
  
   -- create table xxmfz_locations as select * from hr_locations_all@prod
 --  hr_locations_all
      SELECT * FROM xxmfz_locations  where process_flag = 'N';
              
         
       
    BEGIN
     
       FOR get_details IN fetch_details
       LOOP
        
          BEGIN                  
                
            hr_location_api.create_location
            ( p_validate => false
            ,p_effective_date => sysdate
            ,p_language_code => NULL
            ,p_location_code =>get_details.LOCATION_CODE
            ,p_description => get_details.description
            ,p_timezone_code => 'Asia/Dacca'--get_details.timezone_code
            ,p_tp_header_id => NULL
            ,p_ece_tp_location_code => NULL
            ,p_address_line_1 => get_details.address_line_1
            ,p_address_line_2 => get_details.address_line_2
            ,p_address_line_3 => get_details.address_line_3
            ,p_bill_to_site_flag => 'Y'
            ,p_country => 'BD'
            ,p_designated_receiver_id => NULL
            ,p_in_organization_flag =>'Y'
            ,p_inactive_date => NULL
            ,p_operating_unit_id => NULL
            ,p_inventory_organization_id => NULL
            ,p_office_site_flag => 'Y'
            ,p_postal_code => NULL
            ,p_receiving_site_flag => 'Y'
            ,p_region_1 => NULL
            ,p_region_2 => NULL
            ,p_region_3 => NULL
            ,p_ship_to_location_id => NULL
            ,p_ship_to_site_flag => 'Y'
            ,p_style => 'BD'
            ,p_tax_name => NULL
            ,p_telephone_number_1 =>NULL
            ,p_telephone_number_2 => NULL--'234-763-098'
            ,p_telephone_number_3 => NULL
            ,p_town_or_city => get_details.town_or_city
            ,p_loc_information13 => 'Dhaka'--get_details.loc_information13
            ,p_loc_information14 => NULL
            ,p_loc_information15 => NULL
            ,p_loc_information16 => NULL
            ,p_loc_information17 => NULL
            ,p_loc_information18 => NULL
            ,p_loc_information19 => NULL
            ,p_loc_information20 => NULL
            ,p_attribute_category => NULL
            ,p_attribute1 => NULL
            ,p_attribute2 => NULL
            ,p_attribute3 => NULL
            ,p_attribute4 => NULL
            ,p_attribute5 => NULL
            ,p_attribute6 => NULL
            ,p_attribute7 => NULL
            ,p_attribute8 => NULL
            ,p_attribute9 => NULL
            ,p_attribute10 => NULL
            ,p_attribute11 => NULL
            ,p_attribute12 => NULL
            ,p_attribute13 => NULL
            ,p_attribute14 => NULL
            ,p_attribute15 => NULL
            ,p_attribute16 => NULL
            ,p_attribute17 => NULL
            ,p_attribute18 => NULL
            ,p_attribute19 => NULL
            ,p_attribute20 => NULL
            ,p_global_attribute_category => NULL
            ,p_global_attribute1 => NULL
            ,p_global_attribute2 => NULL
            ,p_global_attribute3 => NULL
            ,p_global_attribute4 => NULL
            ,p_global_attribute5 => NULL
            ,p_global_attribute6 => NULL
            ,p_global_attribute7 => NULL
            ,p_global_attribute8 => NULL
            ,p_global_attribute9 => NULL
            ,p_global_attribute10 => NULL
            ,p_global_attribute11 => NULL
            ,p_global_attribute12 => NULL
            ,p_global_attribute13 => NULL
            ,p_global_attribute14 => NULL
            ,p_global_attribute15 => NULL
            ,p_global_attribute16 => NULL
            ,p_global_attribute17 => NULL
            ,p_global_attribute18 => NULL
            ,p_global_attribute19 => NULL
            ,p_global_attribute20 => NULL
            ,p_business_group_id => NULL
            ,p_location_id => ex_loc_id
            ,p_object_version_number => ex_object_version_number
            );

                   
               
             UPDATE xxmfz_locations
                SET process_flag = 'Y'
              WHERE LOCATION_CODE = get_details.LOCATION_CODE;
             COMMIT;
          EXCEPTION
             WHEN OTHERS
             THEN
                l_err_msg := SQLERRM;
                UPDATE xxmfz_locations
                   SET process_flag = 'N', err_msg = l_err_msg
                 WHERE LOCATION_CODE = get_details.LOCATION_CODE;
                COMMIT;
          END;
       END LOOP;
    EXCEPTION
       WHEN OTHERS
       THEN
          l_err_msg := SQLERRM;
          DBMS_OUTPUT.put_line ('Main Exception: ' || l_err_msg);
    END;
    /

API to Upload(Insert) Flex value set values in Oracle Applications R12.2.6

FND API to Delete, Add and Update value set values in Oracle Applications 

 

01. Need to create a table like :

 

 CREATE TABLE APPS.XXMFZ_FND_SEG_VALUE_STG
(
  LS_VALUE_SET_NAME  VARCHAR2(250 BYTE),
  LS_FLEX_VALUE      VARCHAR2(250 BYTE),
  LS_ENABLE_FLAG     CHAR(1 BYTE),
  LS_COMPILED_ATTR   VARCHAR2(10 BYTE),
  START_DATE_ACTIVE  DATE,
  END_DATE_ACTIVE    DATE,
  LS_MEANING         VARCHAR2(250 BYTE),
  LS_DESCRIPTION     VARCHAR2(250 BYTE),
  VALUE_CATEGORY     VARCHAR2(250 BYTE),
  PROCESS_FLAG       CHAR(1 BYTE),
  ERR_MSG            VARCHAR2(500 BYTE)
);

02.  API :

CREATE OR REPLACE PROCEDURE APPS.xxmfz_load_flex_values(XXflex_value_set_name VARCHAR2) -- Value Set name
IS
   l_row_id              VARCHAR2 (500) := NULL;
   l_flex_value_set_id   NUMBER := NULL;
   l_flex_value_id       NUMBER := NULL;
   l_err_msg             VARCHAR2 (500) := NULL;
   l_db_nls_language     VARCHAR2 (500) := NULL;
   CURSOR fetch_details
   IS
      SELECT * FROM XXMFZ_FND_SEG_VALUE_STG  where process_flag = 'N';
BEGIN

   fnd_global.set_nls (p_nls_language                => 'US',
                       p_nls_date_format             => NULL,
                       p_nls_date_language           => NULL,
                       p_nls_numeric_characters      => NULL,
                       p_nls_sort                    => NULL,
                       p_nls_territory               => NULL,
                       p_db_nls_language             => l_db_nls_language,
                       p_db_nls_date_format          => l_db_nls_language,
                       p_db_nls_date_language        => l_db_nls_language,
                       p_db_nls_numeric_characters   => l_db_nls_language,
                       p_db_nls_sort                 => l_db_nls_language,
                       p_db_nls_territory            => l_db_nls_language,
                       p_db_nls_charset              => l_db_nls_language);
   -- Get Value Set ID
   SELECT flex_value_set_id
     INTO l_flex_value_set_id
     FROM apps.fnd_flex_value_sets
    WHERE flex_value_set_name = XXflex_value_set_name; --' Change the Name as per Value Set
   FOR get_details IN fetch_details
   LOOP
      -- Get Next Sequence Number
      SELECT apps.fnd_flex_values_s.NEXTVAL INTO l_flex_value_id FROM DUAL;
      BEGIN
         apps.fnd_flex_values_pkg.
          insert_row (
            x_rowid                        => l_row_id,
            x_flex_value_id                => l_flex_value_id,
            x_attribute_sort_order         => NULL,
            x_flex_value_set_id            => l_flex_value_set_id,
            x_flex_value                   => get_details.ls_flex_value,
            x_enabled_flag                 => 'Y',
            x_summary_flag                 => 'N',
            x_start_date_active            => NULL, --TO_DATE ('01-JUL-2019', 'DD-MON-YYYY'),
            x_end_date_active              => NULL,
            x_parent_flex_value_low        => NULL,
            x_parent_flex_value_high       => NULL,
            x_structured_hierarchy_level   => NULL,
            x_hierarchy_level              => NULL,
            x_compiled_value_attributes    =>  'Y'||CHR(10)||'Y',
            x_value_category               => NULL,
            x_attribute1                   => NULL,
            x_attribute2                   => NULL,
            x_attribute3                   => NULL,
            x_attribute4                   => NULL,
            x_attribute5                   => NULL,
            x_attribute6                   => NULL,
            x_attribute7                   => NULL,
            x_attribute8                   => NULL,
            x_attribute9                   => NULL,
            x_attribute10                  => NULL,
            x_attribute11                  => NULL,
            x_attribute12                  => NULL,
            x_attribute13                  => NULL,
            x_attribute14                  => NULL,
            x_attribute15                  => NULL,
            x_attribute16                  => NULL,
            x_attribute17                  => NULL,
            x_attribute18                  => NULL,
            x_attribute19                  => NULL,
            x_attribute20                  => NULL,
            x_attribute21                  => NULL,
            x_attribute22                  => NULL,
            x_attribute23                  => NULL,
            x_attribute24                  => NULL,
            x_attribute25                  => NULL,
            x_attribute26                  => NULL,
            x_attribute27                  => NULL,
            x_attribute28                  => NULL,
            x_attribute29                  => NULL,
            x_attribute30                  => NULL,
            x_attribute31                  => NULL,
            x_attribute32                  => NULL,
            x_attribute33                  => NULL,
            x_attribute34                  => NULL,
            x_attribute35                  => NULL,
            x_attribute36                  => NULL,
            x_attribute37                  => NULL,
            x_attribute38                  => NULL,
            x_attribute39                  => NULL,
            x_attribute40                  => NULL,
            x_attribute41                  => NULL,
            x_attribute42                  => NULL,
            x_attribute43                  => NULL,
            x_attribute44                  => NULL,
            x_attribute45                  => NULL,
            x_attribute46                  => NULL,
            x_attribute47                  => NULL,
            x_attribute48                  => NULL,
            x_attribute49                  => NULL,
            x_attribute50                  => NULL,
            x_flex_value_meaning           => get_details.ls_meaning,
            x_description                  => get_details.ls_description,
            x_creation_date                => SYSDATE,
            x_created_by                   => fnd_global.user_id,
            x_last_update_date             => SYSDATE,
            x_last_updated_by              => fnd_global.user_id,
            x_last_update_login            => fnd_global.user_id);
               
           
         UPDATE XXMFZ_FND_SEG_VALUE_STG
            SET process_flag = 'Y'
          WHERE ls_meaning = get_details.ls_meaning;
         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_err_msg := SQLERRM;
            UPDATE XXMFZ_FND_SEG_VALUE_STG
               SET process_flag = 'N', err_msg = l_err_msg
             WHERE ls_meaning = get_details.ls_meaning;
            COMMIT;
      END;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      l_err_msg := SQLERRM;
      DBMS_OUTPUT.put_line ('Main Exception: ' || l_err_msg);
END;
/

03. call API:

exec apps.xxmfz_load_flex_values('MFZ_BU_VS');

if upload from another Instance, create a DB link and Insert Data to the staging table :

insert into apps.XXMFZ_FND_SEG_VALUE_STG(LS_VALUE_SET_NAME,LS_FLEX_VALUE, LS_ENABLE_FLAG, LS_MEANING, LS_DESCRIPTION, PROCESS_FLAG)
select flex_value_set_name, ffv.FLEX_VALUE, ENABLED_FLAG, FLEX_VALUE_MEANING, ffvt.DESCRIPTION, 'N' pros_flag from
     fnd_flex_values@prod ffv, fnd_flex_values_tl@prod ffvt, apps.fnd_flex_value_sets@prod vst
     where flex_value_set_name='PRG_FUTURE2_VS'  --ffv.flex_value_set_id = 1014876
     AND ffv.flex_value_id = ffvt.flex_value_id and ENABLED_FLAG='Y' and ffv.flex_value_set_id=vst.flex_value_set_id
     order by ffv.FLEX_VALUE