自动创建web adi的代码

/*
Steps to create the custom integrator. 
Step 1. Using bne_integrator_utils.CREATE_INTEGRATOR package create the custom integrator 
Step 2. Using bne_integrator_utils.CREATE_INTERFACE_FOR_API package create the interface for the created integrator. 
Step 3. Using bne_integrator_utils.CREATE_DEFAULT_LAYOUT package create the default layout for the created integrator with the interface. 
Step 4. Adding POPList for the required columns. 
Step 5. Change the excel column prompts, by default the column headers will come as Database column name.  (bne_interface_cols_tl )
*/
DECLARE
    FUNCTION is_exists_map(p_application_id  IN  NUMBER,
                         p_content_code    IN  VARCHAR2)  RETURN BOOLEAN  IS
     CURSOR csr  IS
       SELECT  1
         FROM bne_mapping_lines
        WHERE application_id  = p_application_id
          AND content_code  = p_content_code
    l_dummy  NUMBER
    retval  BOOLEAN
   BEGIN
     OPEN csr
     FETCH csr
       INTO l_dummy
     IF csr %NOTFOUND  THEN
      retval : = FALSE
     ELSE
      retval : = TRUE
     END  IF
     CLOSE csr
     RETURN retval
   END;
   -- BNE_CONTENT_UTILS.CREATE_CONTENT_TO_API_MAP
   PROCEDURE create_map_line(p_application_id  IN  NUMBER,
                            p_interface_code  IN  VARCHAR2,
                            p_content_code    IN  VARCHAR2,
                            p_mapping_code    IN  VARCHAR2)  IS
     CURSOR mapping_cols_c(cp_application_id  IN  NUMBER, cp_content_code  IN  VARCHAR2, cp_interface_code  IN  VARCHAR2)  IS
       SELECT cc.application_id content_app_id,
             cc.content_code,
             cc.sequence_num content_seq_num,
             ic.application_id interface_app_id,
             ic.interface_code,
             ic.sequence_num interface_seq_num
         FROM bne_content_cols_b cc, bne_interface_cols_b ic
        WHERE cc.application_id  = ic.application_id
          AND cc.application_id  = cp_application_id
          AND (cc. col_name  = ic.interface_col_name  OR
             cc. col_name  = substr(ic.interface_col_name,  3)  OR
             cc.sequence_num  = ic.sequence_num  -- add by sky.chan 
             )
          AND cc.content_code  = cp_content_code
          AND ic.interface_code  = cp_interface_code
        ORDER  BY cc.sequence_num  ASC
    vn_sequence  NUMBER
   BEGIN
     IF  NOT is_exists_map(p_application_id, p_content_code)  THEN
      vn_sequence : =  0
        FOR mapping_cols_rec  IN mapping_cols_c(p_application_id,
                                             p_content_code,
                                             p_interface_code) LOOP
         vn_sequence : = vn_sequence  +  1
          --  Create the Mapping records in the BNE_MAPPING_LINES table
          INSERT  INTO bne_mapping_lines
          (application_id,
           mapping_code,
           sequence_num,
           content_app_id,
           content_code,
           content_seq_num,
           interface_app_id,
           interface_code,
           interface_seq_num,
           object_version_number,
           created_by,
           creation_date,
           last_updated_by,
           last_update_date)
         VALUES
          (p_application_id,
           p_mapping_code,
           vn_sequence,
           mapping_cols_rec.content_app_id,
           mapping_cols_rec.content_code,
           mapping_cols_rec.content_seq_num,
           mapping_cols_rec.interface_app_id,
           mapping_cols_rec.interface_code,
           mapping_cols_rec.interface_seq_num,
            1,
           fnd_global. user_id,
           SYSDATE,
           fnd_global. user_id,
           SYSDATE)
          EXIT  WHEN mapping_cols_c %NOTFOUND
        END LOOP
     END  IF
   END;
    PROCEDURE create_bne_integrator(p_application_id   IN  VARCHAR2,
                                  p_object_code      IN  VARCHAR2,
                                  p_integrator_name  IN  VARCHAR2,
                                  p_package_name     IN  VARCHAR2,
                                  p_procedure_name   IN  VARCHAR2)  IS
    l_integrator_name  VARCHAR2( 200) : =  ''
    l_integrator_code  VARCHAR2( 100)
    l_param_list_code  VARCHAR2( 100)
    l_interface_code   VARCHAR2( 100)
    l_mapping_code     VARCHAR2( 100)
    l_layout_code      VARCHAR2( 100)
    l_content_code     VARCHAR2( 100)
    l_parameters        VARCHAR2( 500) : =  ' bne:page=BneCreateDoc ' 
                                       chr( 38)   ' bne: '
    l_rowid            VARCHAR2( 100)
    l_function_id      NUMBER
   BEGIN
     -- todo 1 create integrator
    bne_integrator_utils.create_integrator(p_application_id        => p_application_id,
                                           p_object_code           => p_object_code,
                                           p_integrator_user_name  => p_integrator_name,
                                           p_language              => userenv( ' LANG '),
                                           p_source_language       => userenv( ' LANG '),
                                           p_user_id               => fnd_global. user_id,
                                           p_integrator_code       => l_integrator_code)
     -- todo 2 create content
     -- --todo 2.1 create empty content
     bne_content_utils.create_content(p_application_id,
                                     p_object_code,
                                     l_integrator_code,
                                      ' None ',
                                     userenv( ' LANG '),
                                     userenv( ' LANG '),
                                      '',
                                     fnd_global. user_id,
                                     l_content_code)
     -- --todo 2.2 create text content 
     bne_content_utils.create_content_text(p_application_id,
                                          p_object_code   ' _TXT ',
                                          l_integrator_code,
                                           ' Text ',
                                           2,
                                           ' P ',
                                          userenv( ' LANG '),
                                          userenv( ' LANG '),
                                          fnd_global. user_id,
                                          l_content_code)
      -- todo 3 create interface & map
     bne_integrator_utils.create_api_interface_and_map(p_application_id       => p_application_id,
                                                      p_object_code          => p_object_code,
                                                      p_integrator_code      => l_integrator_code,
                                                      p_api_package_name     => p_package_name,
                                                      p_api_procedure_name   => p_procedure_name,
                                                      p_interface_user_name  => p_integrator_name,
                                                      p_content_code         => l_content_code,
                                                      p_view_name            =>  '',
                                                      p_param_list_name      => p_object_code 
                                                                                ' LIST ',
                                                      p_api_type             =>  ' PROCEDURE ',
                                                      p_api_return_type      =>  '',
                                                      p_upload_type          =>  2,  -- 1= upload to Table. 2 = Upload to PL/SQL API
                                                      p_language             => userenv( ' LANG '),
                                                      p_source_lang          => userenv( ' LANG '),
                                                      p_user_id              => fnd_global. user_id,
                                                      p_param_list_code      => l_param_list_code,
                                                      p_interface_code       => l_interface_code,
                                                      p_mapping_code         => l_mapping_code)
     -- todo 4 create layout
    bne_integrator_utils.create_default_layout(p_application_id   => p_application_id,
                                               p_object_code      => p_object_code,
                                               p_integrator_code  => l_integrator_code,
                                               p_interface_code   => l_interface_code,
                                               p_user_id          => fnd_global. user_id,
                                               p_force            => FALSE,
                                               p_all_columns      => TRUE,
                                               p_layout_code      => l_layout_code)
     -- todo 5   change default block to  line 
     UPDATE bne_layout_blocks_b
        SET block_id  =  1, sequence_num  =  1
      WHERE layout_code  = l_layout_code
     UPDATE bne_layout_blocks_tl
        SET block_id  =  1
      WHERE layout_code  = l_layout_code
      -- todo 6 create layout column
     FOR i  IN ( SELECT  *
                 FROM bne_interface_cols_tl
                WHERE interface_code  = l_interface_code) LOOP
      bne_layout_cols_pkg.insert_row(l_rowid,
                                     p_application_id,  -- APPLICATION_ID
                                     l_layout_code,  -- LAYOUT_CODE
                                      1,  -- BLOCK_ID
                                     (i.sequence_num  *  10),  -- SEQUENCE_NUM
                                      1,  -- OBJECT_VERSION_NUMBER
                                     p_application_id,  -- INTERFACE_APP_ID
                                     l_interface_code,  -- INTERFACE_CODE
                                     i.sequence_num,  -- INTERFACE_SEQ_NUM
                                      NULL,  -- STYLE_CLASS
                                      NULL,  -- HINT_STYLE
                                      NULL,  -- HINT_STYLE_CLASS
                                      NULL,  -- PROMPT_STYLE
                                      NULL,  -- PROMPT_STYLE_CLASS
                                      NULL,  -- DEFAULT_TYPE
                                      NULL,  -- DEFAULT_VALUE
                                      NULL,  -- STYLE
                                     SYSDATE,  -- CREATION_DATE
                                     fnd_global. user_id,  -- CREATED_BY
                                     SYSDATE,  -- LAST_UPDATE_DATE
                                     fnd_global. user_id,  -- LAST_UPDATED_BY
                                     fnd_global. user_id,  -- LAST_UPDATE_LOGIN
                                      NULL,  -- DISPLAY_WIDTH
                                      ' N '  -- READ_ONLY_FLAG
                                     )
     END LOOP
     -- todo 7 create map line .I think there is a bug at: BNE_CONTENT_UTILS.CREATE_CONTENT_TO_API_MAP. because they cannot create the map line.
    create_map_line(p_application_id  => p_application_id,
                    p_interface_code  => l_interface_code,
                    p_content_code    => l_content_code,
                    p_mapping_code    => l_mapping_code)
    dbms_output.put_line( ' l_integrator_code: '  l_integrator_code)
    dbms_output.put_line( ' l_param_list_code '  l_param_list_code)
    dbms_output.put_line( ' l_interface_code: '  l_interface_code)
    dbms_output.put_line( ' l_mapping_code: '  l_mapping_code)
    dbms_output.put_line( ' l_layout_code: '  l_layout_code)
    dbms_output.put_line( ' l_content_code: '  l_content_code)
      --  todo 8 create function
     -- -begin create function 
    l_parameters : = l_parameters  chr( 38)   ' bne:integrator= ' 
                   p_application_id   ' : '  l_integrator_code  chr( 38) 
                    ' bne:noreview=true '
     dbms_output.put_line( ' parameters: '  l_parameters);
      SELECT fnd_form_functions_s.NEXTVAL  INTO l_function_id  FROM sys.dual;
     fnd_form_functions_pkg.insert_row(x_rowid                     => l_rowid,
                                      x_function_id               => l_function_id,
                                      x_web_host_name             =>  '',
                                      x_web_agent_name            =>  '',
                                      x_web_html_call             =>  ' BneApplicationService ',
                                      x_web_encrypt_parameters    =>  '',
                                      x_web_secured               =>  '',
                                      x_object_id                 =>  '',
                                      x_region_application_id     =>  '',
                                      x_region_code               =>  '',
                                      x_web_icon                  =>  '',
                                      x_function_name             => l_integrator_code,
                                      x_application_id            =>  '',
                                      x_form_id                   =>  '',
                                      x_parameters                => l_parameters,
                                      x_type                      =>  ' SERVLET ',
                                      x_user_function_name        => p_integrator_name,
                                      x_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.login_id,
                                      x_maintenance_mode_support  =>  '',
                                      x_context_dependence        =>  '',
                                      x_jrad_ref_path             =>  '');
    dbms_output.put_line( ' function_id: '  l_function_id);
     -- -end create function 
      -- todo 9   add other language 
     /* bne_integrators_pkg.add_language
    bne_interface_cols_pkg.add_language
    bne_content_cols_pkg.add_language
    bne_contents_pkg.add_language
    bne_integrators_pkg.add_language
    bne_layouts_pkg.add_language
    bne_layout_blocks_pkg.add_language
    bne_mappings_pkg.add_language
    bne_mapping_lines_pkg.add_language */
      --  todo  10 update col name OPTIONAL
     UPDATE bne_interface_cols_tl
        SET prompt_left  = sky_test_webadi.get_col_name(prompt_left),
       prompt_above  = sky_test_webadi.get_col_name(prompt_above)
      WHERE application_id  = p_application_id
        AND interface_code  = l_interface_code;
    END;
  BEGIN
  create_bne_integrator(p_application_id   =>  200,
                        p_object_code      =>  ' SKY_TEST_WEBADI1 ',
                        p_integrator_name  =>  ' SKY_TEST_WEBADI1 ',
                        p_package_name     =>  ' SKY_TEST_WEBADI ',
                        p_procedure_name   =>  ' CREATE_TEST ');
END;