Oracle EBS 动态生成sequence参考

Oracle EBS 动态生成sequence参考

PROCEDURE create_trx_sequence (

P_batch_source_id  IN  number,

P_org_id          IN   number default null,

P_last_number      IN  number

      ) IS

  l_sequence_name varchar2(1024);

  l_app_short_name varchar2(3);

  l_org_id       varchar2(30);

  l_sql_stmt     varchar2(2000);

  l_fnd_user     varchar2(30);

-- Start Bug 6010774, 6903507

  l_sequence_name_2 varchar2(1024);

  l_sql_stmt_2     varchar2(2000);

  l_sequence_name_3 varchar2(1024);

  l_sql_stmt_3     varchar2(2000);

  l_sequence_name_4 varchar2(1024);

  l_sql_stmt_4     varchar2(2000);

  l_country_code    varchar2(30);

-- End Bug 6010774, 6903507

BEGIN

  select min(ou.oracle_username)

  into  l_fnd_user

  from  FND_PRODUCT_INSTALLATIONS pi,

        FND_ORACLE_USERID ou

  where  ou.oracle_id = pi.oracle_id

  and   application_id = 0;

  IF (p_org_id is NULL) THEN

     l_sequence_name := 'RA_TRX_NUMBER_'||to_char(P_batch_source_id)||'_S';

      -- Start Bug 6010774, 6903507

     l_sequence_name_2 := 'JA_GUI_NUMBER_'||to_char(P_batch_source_id)||'_S';

     l_sequence_name_3 := 'JL_ZZ_TRX_NUM_'||to_char(P_batch_source_id)||'_S';

      -- End Bug 6010774, 6903507

     l_sequence_name_4 := 'JL_BR_TRX_NUM_'||to_char(P_batch_source_id)||'_S';

      

  ELSE

     l_sequence_name := 'RA_TRX_NUMBER_' || to_char(P_batch_source_id)||

                     '_' || p_org_id  || '_S';

      -- Start Bug 6010774, 6903507

     l_sequence_name_2 := 'JA_GUI_NUMBER_' || to_char(P_batch_source_id)||

                     '_' || p_org_id  || '_S';

     l_sequence_name_3 := 'JL_ZZ_TRX_NUM_' || to_char(P_batch_source_id)||

                     '_' || p_org_id  || '_S';

      -- End Bug 6010774, 6903507

     l_sequence_name_4 := 'JL_BR_TRX_NUM_' || to_char(P_batch_source_id)||

                       '_' || p_org_id  || '_S';

  END IF;

  l_sql_stmt := 'create sequence '||l_sequence_name||

              ' minvalue 1 maxvalue 99999999999999999999  start with '||

              to_char(P_last_number + 1)||' cache 20';

  ad_ddl.do_ddl(l_fnd_user, 'AR', ad_ddl.create_sequence, l_sql_stmt, l_sequence_name);

  -- Start Bug 6010774, 6903507

  fnd_profile.get('JGZZ_COUNTRY_CODE', l_country_code);

  IF (l_country_code = 'TW') THEN

    l_sql_stmt_2 := 'create sequence '||l_sequence_name_2||

              ' minvalue 1 maxvalue 99999999999999999999  start with '||

              to_char(P_last_number + 1)||' nocache';

   ad_ddl.do_ddl(l_fnd_user, 'JA', ad_ddl.create_sequence, l_sql_stmt_2, l_sequence_name_2);

  ELSIF (l_country_code = 'BR') THEN

     l_sql_stmt_4 := 'create sequence '||l_sequence_name_4||

                ' minvalue 1 maxvalue 99999999999999999999  start with '||

                to_char(P_last_number + 1)||' nocache';

     

     ad_ddl.do_ddl(l_fnd_user, 'JL', ad_ddl.create_sequence, l_sql_stmt_4,l_sequence_name_4);

  ELSIF (l_country_code = 'AR') THEN

    l_sql_stmt_3 := 'create sequence '||l_sequence_name_3||

             ' minvalue 1 maxvalue 99999999999999999999  start with '||

              to_char(P_last_number + 1)||' nocache';

   ad_ddl.do_ddl(l_fnd_user, 'JL', ad_ddl.create_sequence, l_sql_stmt_3, l_sequence_name_3);

  END IF;

  -- End Bug 6010774, 6903507

END;

-- 刘轶鹤