ORACLE 动态生成键关键字的值

键关键字查询

SELECT fa.application_short_name 应用简称,
       fif.id_flex_code          键值关键字,
       fis.id_flex_num           账户id
  FROM fnd_id_flexs fif, fnd_id_flex_structures fis, fnd_application fa
 WHERE 1 = 1
   AND fif.application_id = fa.application_id
   AND fif.application_id = fis.application_id
   AND fif.id_flex_code = fis.id_flex_code
   AND fa.product_code = 'FA'

动态生成程序

DECLARE
  l_loc_id   NUMBER;
  p_err_msg  VARCHAR2(1000);
  p_segment1 VARCHAR2(10) := '101';
  p_segment2 VARCHAR2(10) := '100500';
  p_segment3 VARCHAR2(10) := '130019';
BEGIN
  l_loc_id  := fnd_flex_ext.get_ccid(application_short_name => 'OFA',--应用简称
                                     key_flex_code          => 'LOC#',--LOC# 对应 地点  键值关键字
                                     structure_number       => 101,--账户id
                                     validation_date        => to_char(SYSDATE,
                                                                       fnd_flex_ext.date_format),
                                     concatenated_segments  => p_segment1 || '.' ||
                                                               p_segment2 || '.' ||
                                                               TRIM(p_segment3));
  p_err_msg := nvl(fnd_flex_ext.get_message, fnd_api.g_ret_sts_success);
  dbms_output.put_line('l_loc_id = ' || l_loc_id);
  dbms_output.put_line('P_ERR_MSG = ' || p_err_msg);
END;