WEBADI 开发实例

主要功能：

1. 用户利用sql 导出具备固定格式的txt file(包含用户名，新增标志，职责，应用，起始时间，终止时间）

2.用户在webadi向导中导入txt file,自动生成excel 模板进行上传.

3.根据模块中的exists flag 来判断职责为新增或失效.

后台package:

----Function&Procedure declare begin-------------------------

  PROCEDURE main(p_user_name          IN VARCHAR2,
                 p_existing_flag      IN VARCHAR2,
                 p_responsibility_key IN VARCHAR2,
                 p_application        IN VARCHAR2,
                 p_start_date         IN OUT DATE,
                 p_end_date           IN OUT DATE);

  PROCEDURE write_log(p_message_type IN NUMBER, p_message_text IN VARCHAR2);

----Function&Procedure declare end---------------------------

END glc_mass_assign_resp_pkg;
/
CREATE OR REPLACE PACKAGE BODY glc_mass_assign_resp_pkg IS

  ----Function&Procedure body begin----------------------------

  PROCEDURE main(p_user_name          IN VARCHAR2,
                 p_existing_flag      IN VARCHAR2,
                 p_responsibility_key IN VARCHAR2,
                 p_application        IN VARCHAR2,
                 p_start_date         IN OUT DATE,
                 p_end_date           IN OUT DATE) IS
 
    lv_user_id NUMBER;
    lv_resp_id NUMBER;
    lv_appl_id NUMBER;
 
    lv_count NUMBER;
  BEGIN
    --Verify data begin
    --User name
    BEGIN
      SELECT fu.user_id
        INTO lv_user_id
        FROM fnd_user fu
       WHERE fu.user_name = upper(TRIM(p_user_name))
            --added by Tony liu on 22-jan-2010
         AND (fu.end_date IS NULL OR fu.end_date > SYSDATE);
    EXCEPTION
      WHEN no_data_found THEN
        write_log(1, 'Failure: Incorrect/Disabled User Name !');
    END;
 
    --Exist flag
    IF upper(TRIM(nvl(p_existing_flag, 'Y'))) <> 'Y' AND
       upper(TRIM(nvl(p_existing_flag, 'N'))) <> 'N' THEN
      write_log(2, 'Failure: Invalid Existing Flag !');
    END IF;
 
    --Responsibility key
    BEGIN
      SELECT fr.responsibility_id
        INTO lv_resp_id
        FROM fnd_responsibility fr
       WHERE upper(fr.responsibility_key) =
             upper(TRIM(p_responsibility_key))
            --added by Tony liu on 22-jan-2010
         AND (fr.end_date IS NULL OR fr.end_date > SYSDATE);
    EXCEPTION
      WHEN no_data_found THEN
        write_log(3, 'Failure: Incorrect/Disabled Responsibility Key!');
    END;
 
    BEGIN
      IF p_existing_flag = 'N' THEN
        SELECT COUNT(1)
          INTO lv_count
          FROM fnd_user_resp_groups_direct fur,
               fnd_user                    fu,
               fnd_responsibility          fr
         WHERE fu.user_id = fur.user_id
           AND fr.responsibility_id = fur.responsibility_id
           AND fu.user_name = p_user_name
           AND fr.responsibility_key = p_responsibility_key;
        IF lv_count > 0 THEN
          write_log(8, 'Failure: Responsibility is already exists!');
        END IF;
      END IF;
    END;
 
    --Application
    BEGIN
      SELECT fat.application_id
        INTO lv_appl_id
        FROM fnd_application_tl fat
       WHERE upper(fat.application_name) = upper(TRIM(p_application))
         AND fat.LANGUAGE = 'US';
    EXCEPTION
      WHEN no_data_found THEN
        write_log(4, 'Failure: Invalid Application Name!');
    END;
 
    --Application and Responsibility check
    BEGIN
      SELECT fr.responsibility_id
        INTO lv_resp_id
        FROM fnd_responsibility fr, fnd_application_tl fat
       WHERE upper(fat.application_name) = upper(TRIM(p_application))
         AND fat.LANGUAGE = 'US'
         AND fat.application_id = fr.application_id
         AND upper(fr.responsibility_key) =
             upper(TRIM(p_responsibility_key))
            --added by Tony liu on 22-jan-2010
         AND (fr.end_date IS NULL OR fr.end_date > SYSDATE);
    EXCEPTION
      WHEN no_data_found THEN
        write_log(9,
                  'Failure: Incorrect combination of Responsibility and Application');
    END;
 
    --User&exist flag&responsibility key&application
    IF p_existing_flag = 'Y' THEN
      BEGIN
        IF fnd_user_resp_groups_api.assignment_exists(user_id                       => lv_user_id,
                                                      responsibility_id             => lv_resp_id,
                                                      responsibility_application_id => lv_appl_id) THEN
          NULL;
        ELSE
          write_log(6,
                    'Failure: Incorrect existing flag,Cant find the responsibility under the user');
        END IF;
       
      END;
    END IF;
 
    --Date
    IF nvl(p_end_date, to_date('01-01-3000','dd-mm-yyyy')) <= nvl(p_start_date, SYSDATE) THEN
      write_log(5, 'Failure: Start date can not be later than end date!');
    END IF;
    --Added by Tony Liu on 22-jan-2010
    IF p_start_date IS NULL THEN
      BEGIN
        SELECT fur.start_date
          INTO p_start_date
          FROM fnd_user_resp_groups_direct fur
         WHERE fur.user_id = lv_user_id
           AND fur.responsibility_id = lv_resp_id
           AND fur.responsibility_application_id = lv_appl_id;
      EXCEPTION
        WHEN no_data_found THEN
          p_start_date := trunc(SYSDATE);
      END;
   
    END IF;
    /*
    IF p_end_date IS NULL THEN
      BEGIN
        SELECT fur.end_date
          INTO p_end_date
          FROM fnd_user_resp_groups_direct fur
         WHERE fur.user_id = lv_user_id
           AND fur.responsibility_id = lv_resp_id
           AND fur.responsibility_application_id = lv_appl_id;
      EXCEPTION
        WHEN no_data_found THEN
          p_end_date := NULL;
      END;
   
    END IF;
    */
 
    --Verify data end
 
    --Responsibility API begin
    --Invalid responsibility
    IF p_existing_flag = 'Y' THEN
      BEGIN
        fnd_user_resp_groups_api.update_assignment(user_id                       => lv_user_id,
                                                   responsibility_id             => lv_resp_id,
                                                   responsibility_application_id => lv_appl_id,
                                                   start_date                    => trunc(p_start_date),
                                                   end_date                      => trunc(p_end_date),
                                                   description                   => '');
        COMMIT;
      EXCEPTION
        WHEN OTHERS THEN
          write_log(7,
                    'Failure: API(fnd_user_resp_groups_api.update_assignment) error!');
      END;
    END IF;
    --Add responsibility
    IF p_existing_flag = 'N' THEN
      BEGIN
        fnd_user_resp_groups_api.upload_assignment(user_id                       => lv_user_id,
                                                   responsibility_id             => lv_resp_id,
                                                   responsibility_application_id => lv_appl_id,
                                                   start_date                    => trunc(p_start_date),
                                                   end_date                      => trunc(p_end_date),
                                                   description                   => '');
        COMMIT;
      EXCEPTION
        WHEN OTHERS THEN
          write_log(7,
                    'Failure: API(fnd_user_resp_groups_api.upload_assignment) error!');
      END;
    END IF;
    --Responsibility API end
 
    --Added by Tony liu on 22-jan-2010
    IF p_existing_flag IS NULL THEN
      --check responsibility
      IF fnd_user_resp_groups_api.assignment_exists(user_id                       => lv_user_id,
                                                    responsibility_id             => lv_resp_id,
                                                    responsibility_application_id => lv_appl_id) THEN
     
        BEGIN
          fnd_user_resp_groups_api.update_assignment(user_id                       => lv_user_id,
                                                     responsibility_id             => lv_resp_id,
                                                     responsibility_application_id => lv_appl_id,
                                                     start_date                    => trunc(p_start_date),
                                                     end_date                      => trunc(p_end_date),
                                                     description                   => '');
          COMMIT;
        EXCEPTION
          WHEN OTHERS THEN
            write_log(7,
                      'Failure: API(fnd_user_resp_groups_api.update_assignment) error!');
        END;
      ELSE
        BEGIN
          fnd_user_resp_groups_api.upload_assignment(user_id                       => lv_user_id,
                                                     responsibility_id             => lv_resp_id,
                                                     responsibility_application_id => lv_appl_id,
                                                     start_date                    => trunc(p_start_date),
                                                     end_date                      => trunc(p_end_date),
                                                     description                   => '');
          COMMIT;
        EXCEPTION
          WHEN OTHERS THEN
            write_log(7,
                      'Failure: API(fnd_user_resp_groups_api.upload_assignment) error!');
        END;
      END IF;
   
    END IF;
 
  EXCEPTION
    WHEN OTHERS THEN
      write_log(9, SQLCODE || ':' || SQLERRM);
  END main;

  PROCEDURE write_log(p_message_type IN NUMBER, p_message_text IN VARCHAR2) IS
    lv_error_code NUMBER;
  BEGIN
 
    lv_error_code := to_number('-2000' || to_char(p_message_type));
    --throw out message
    dbms_standard.raise_application_error(lv_error_code, p_message_text);
  END write_log;
  ----Function&Procedure body end------------------------------
END glc_mass_assign_resp_pkg;

WEBADI设置过程（copy自我的Design)

1.1.           GLC: Mass Assign Responsibility (New Integrator)

           Create WEBADI integrator for update user responsibility.

Desktop integration->Create Document->Views: excel2003->Integrator: HR Integrator Setup->Layout: Integrator Setup->Content: null->Review->Create Documents->Download Document

You have to enter some information as below:

 

Column	Value
Metadata Type	Create
Application Short Name	GLC
Integrator User Name	GLC: Mass Assign Responsibility
View Name	Don’t fill
Form. Name	Don’t fill
API Package Name	GLC_MASS_ASSIGN_RESP_PKG
API Procedure Name	MAIN
Interface User Name	Any name
Interface Parameter list	Any name
API Type	Procedure
API Return Type	Don’t fill
Message	Don’t fill

 

Then upload document.

If there displays a “J” in Message , upload is complete.

If there displays a “L” in Message, upload is fail.

 

1.2.           GLC_MASS_ASSIGN_RESP (New Function)

Create new function in EBS suite

System administrator->Application->Function

Function name:          GLC_MASS_ASSIGN_RESPONSIBILITY

Function type:            Subfunction

 

1.2.1.     Add function in IT&User’s current menu

System administrator->Application->Menu

User Responsibility:               System administrator

IT    Responsibility:              Desktop Integrator

User               Menu:              FND_NAVSEC4.0  (System administrator/security)

IT                  Menu:              DESKTOP INTEGRATION MENU

 

1.2.2.      Maintain integration and form. function associations

Desktop integration->Create Document->Views: excel2003->Integrator: Maintain integration and form. function associations ->Layout: Form. function associations->Content: Mapping: Default Application short name: GLC Integrator user name:GLC: Mass Assign Responsibility->Review->Create Documents->Download Document

Integrator Application Short Name:              GLC

Integrator User Name:                                     GLC: Mass Assign Responsibility

Form. Function List:                                          GLC_MASS_ASSIGN_RESP

 

Then upload document.

After this, IT can create layout and mapping for this integration. User can use this integration to download excel template.

 

 

1.3.           GLC: Mass Assign Responsibility (New Layout)

Create new integration layout for new integration

Desktop integration->Define Layout->Integrator:GLC: Mass Assign Responsibility->Layout: new Layout-> Layout name : GLC: Mass Assign ResponsibilityLayout Key:GLC_MASS_ASSIGN_RESP->Create layout

Field name:    Come from package input parameters( WEBADI will deleteprepositive “P_”)

Placement:       Lines

Default Value: Keep blank (or input some specific content)

Default Type:   None ( or other type , decided by default value)

 

Then save Layout.

 

1.4.           GLC: Mass Assign Responsibility (New Mapping)

Desktop integration->Define Mapping->Integrator:GLC: Mass Assign Responsibility->Mapping: Define Mapping-> Mapping name : GLC: Mass Assign ResponsibilityMapping Key:GLC_MASS_ASSIGN_RESP->Create Mapping

Number of mapping columns:6 (same with layout columns)

Select source columns for text file and target columns for excel template.

Do not enter manually, must select value from LOV.

 

 

1.5.           BNE_MASS ASSIGN RESPONSIBILITY (New Function)

Create function for automatic download integrator.

1.5.1.     Add function in user’s menu and let user download excel template by himself.

Desktop integration->Create Document->Views: excel2003->Integrator:GLC: Mass Assign Responsibility->Layout:GLC: Mass Assign Responsibility->Content: Text file->Content:Parameters (refer to screenshot)->Save

 

 

 

Then save all setup

Shortcut name: GLC: Mass Assign Responsibility

Select “ Save to Shortcut List” and “Save to Form. Function”

Settings:         all selected

 

Then save your setup.

Save to Shortcut List: Let us download excel template quickly in WEBADI.

Save to Form. Function: Let user can download excel template in their responsibility.

System administrator->function-> Find: “BNE_ %”

 

You can find a function named “BNE_ ”+ Integration user name.

Function name: BNE_ MASS ASSIGN RESPONSIBILITY

Also you can change function name and user name to yours favorite.

Function name: BNE_MASS ASSIGN RESPONSIBILITY

User function name:@GLC: Mass Assign Responsibility

 

 

Add this function in user’s menu .

System administrator/security/Mass Assign Responsibility

User can download excel template via this function.

 

1.5.2.     Final function setup

Add “ One Mouse Click” parameters on user’s function.

User only need to click menu and select data file to WEBADI. Then excel template will be downloaded and data will be in it.

 

Original statement:

bne:page=BneCreateDoc&bne:viewer=BNE:EXCEL2000&bne:reporting=N&bne:integrator=GLC:GENERAL_2_INTG&bne:layout=GLC:GLC_MASS_ASSIGN_RESP&bne:content=GLC:GENERAL_2A_CNT&bnectl:file=WM_CONTRACTORS_CONVERSION_DATA_19793875.csv&bne:map=GLC:GLC_MASS_ASSIGN_RESP

 需要删除上面红色部分（指定导入的txt file,不需要，由用户动态选择）

Changed to:

bne:page=BneCreateDoc&bne:viewer=BNE:EXCEL2000&bne:reporting=N&bne:integrator=GLC:GENERAL_2_INTG&bne:layout=GLC:GLC_MASS_ASSIGN_RESP&bne:content=GLC:GENERAL_2A_CNT &bne:map=GLC:GLC_MASS_ASSIGN_RESP&bne:noreview=Yes

 

移植脚本:

安装时，需提供下列ldt 文件：

for webadi:

content / integration / layout / mapping / parameters / security

Download Script.:

1.1.           GLC_MASS_ASSIGN_RESP_INTEGRATION.ldt

1.1.1.     New/Reuse/Copy from          

           New

1.1.2.     Layout

N/A

1.1.3.     Functionality

           Upload integration.

        --Download Integration

           FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/admin/import/bneint.lct           GLC_MASS_ASSIGN_RESP_INTEGRATION.ldt BNE_INTEGRATORS INTEGRATOR_ASN="GLC"           INTEGRATOR_CODE="GENERAL_2_INTG"

 

 

1.2.           GLC_MASS_ASSIGN_RESP_LAYOUT.ldt

1.2.1.     New/Reuse/Copy from          

           New

1.2.2.     Layout

N/A

1.2.3.     Functionality

           Upload layout.

        --Download Layout

           FNDLOAD apps//$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/admin/import/bnelay.lct           GLC_MASS_ASSIGN_RESP_LAYOUT.ldt BNE_LAYOUTS LAYOUT_ASN="GLC"           LAYOUT_CODE="GLC_MASS_ASSIGN_RESP"

 

1.3.           GLC_MASS_ASSIGN_RESP_CONTENT.ldt

1.3.1.     New/Reuse/Copy from          

           New

1.3.2.     Layout

N/A

1.3.3.     Functionality

           Upload content.

        --Download Content

           FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/admin/import/bnecont.lct           GLC_MASS_ASSIGN_RESP_CONTENT.ldt BNE_CONTENTS CONTENT_ASN="GLC"           CONTENT_CODE="GENERAL_2A_CNT"

 

1.4.           GLC_MASS_ASSIGN_RESP_MAPPING.ldt

1.4.1.     New/Reuse/Copy from          

           New

1.4.2.     Layout