主要功能：
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
