Import Responsibility Through EIM:
Importing new responsibilities and assigning them to users in siebel is one of the most common requirements in siebel projects. Most of the time it's a one time load for a set of users and at other times it may be used as periodic load for new users.
Below is the step by step method for loading new responsibilities and assigning them to users
Responsibility Import:
There are various approaches to load data into siebel. Here we will take a simple one.
Lets discuss these in detail:
1. Prepare the data in CSV format:
Click here to download a sample CSV file and put the data in the defined format. Note that the username column in the sheet should corresponds to the S_USER.LOGIN information. Responsibility column will contain the name of the new responsibility and primary responsibility column should contain the name of the primary responsibility for that user. Mentioning the primary one is optional as it will pick one of the responsibilities as primary if not mentioned. Also mention the responsibility organization in the respective column. Save the file as resp-insert.csv.
2. Creating staging table:
CREATE TABLE "STAGING"."RESP_INSERT"
(
"LOGIN" VARCHAR2(50 BYTE),
"RESP" VARCHAR2(100 BYTE),
"PRIMARY" VARCHAR2(1 BYTE),
"RESPORG" VARCHAR2(100 BYTE)
);
COMMIT;
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON STAGING.RESP_INSERT TO SIEBEL;
COMMIT;
3. Loading the data in staging table through SQLLDR utility.
Prepare a control file (.ctl) and a batch (.bat) file an place them at the same location as the csv file. (Let say in D:\resp-insert\).
Structure of control file (resp-insert.ctl):
LOAD DATA
CHARACTERSET WE8ISO8859P1
TRUNCATE
INTO TABLE STAGING.RESP_INSERT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
LOGIN,
RESP,
PRIMARY,
RESPORG,
DESC
)
Structure of Batch file (resp-insert.bat):
@echo off
set ORCL_PATH=D:\Oracle\product\11.2.0\client_1\BIN
set DB_USR=XXXXXX
set DB_PAS=XXXXXX
set DB_CON=XXXXXX
set CUR_PATH=D:\resp-insert
%ORCL_PATH%\sqlldr %DB_USR%/%DB_PAS%@%DB_CON% CONTROL=%CUR_PATH%\resp-insert.ctl, DATA=%CUR_PATH%\resp-insert.csv, LOG=%CUR_PATH%\SQLLDR_resp-insert.log, SKIP=1, ERRORS=1000, BAD=%CUR_PATH%\resp-insert.bad, DISCARD=%CUR_PATH%\resp-insert.dis
So now up to here we have 3 files, resp-insert.ctl, resp-insert.csv and resp-insert.bat at d:\resp-insert\ location.
Execute the resp-insert.bat file after filling the necessary parameters like db username, password etc. You can check the data in staging table by executing
SELECT * FROM STAGING.RESP_INSERT;
4. Execute the SQL scripts to load the staging table data in EIM table.
Execute the below scripts to insert data into EIM table. You can change the batch numbers if required.
DELETE FROM EIM_RESP WHERE IF_ROW_BATCH_NUM BETWEEN 1 and 99;
DELETE FROM EIM_EMPLOYEE WHERE IF_ROW_BATCH_NUM BETWEEN 100 and 199;
COMMIT;
/*Inserting new responsibilities into EIM table*/
INSERT INTO EIM_RESP
(
ROW_ID,
IF_ROW_STAT,
IF_ROW_BATCH_NUM,
RESP_NAME,
RESP_BU,
RESP_DESC_TEXT
)
SELECT DISTINCT
ROWNUM,
'FOR_IMPORT',
1 + floor((rownum-1)/5000),
STG.RESP,
STG.RESPORG,
STG.DESC
FROM (SELECT DISTINCT RESP, RESPORG, DESC FROM RESP_INSERT) STG;
COMMIT;
/*Inserting user-responsibility details in EIM table*/
INSERT INTO EIM_EMPLOYEE
(
ROW_ID,
PARTY_UID,
PARTY_TYPE_CD,
IF_ROW_STAT,
IF_ROW_BATCH_NUM,
CON_BU,
CON_PRIV_FLG,
CON_PERSON_UID,
RESP_BU,
RESP_NAME,
CON_PR_RESP
)
SELECT
ROWNUM,
PARTY.PARTY_UID,
PARTY.PARTY_TYPE_CD,
'FOR_IMPORT',
100 + floor((rownum-1)/5000),
CONBU.NAME,
CON.PRIV_FLG,
CON.PERSON_UID,
RESPBU.NAME,
stg.resp,
stg.primary
from
S_USER USR,
S_BU RESPBU,
S_CONTACT CON,
S_BU CONBU,
S_PARTY PARTY,
FROM STG.RESP_INSERT STG
WHERE
PARTY.ROW_ID = CON.PAR_ROW_ID
AND CON.PAR_ROW_ID = USR.PAR_ROW_ID
AND CON.BU_ID = CONBU.ROW_ID
AND STG.RESPORG = RESPBU.NAME
AND STG.LOGIN = USR.LOGIN;
COMMIT;
To check the data in EIM tables execute below scripts:
select COUNT(1), IF_ROW_STAT, IF_ROW_BATCH_NUM
FROM EIM_RESP WHERE IF_ROW_BATCH_NUM BETWEEN 1 AND 99
GROUP BY IF_ROW_STAT, IF_ROW_BATCH_NUM ORDER BY IF_ROW_BATCH_NUM;
select COUNT(1), IF_ROW_STAT, IF_ROW_BATCH_NUM
FROM EIM_EMPLOYEE WHERE IF_ROW_BATCH_NUM BETWEEN 100 AND 199
GROUP BY IF_ROW_STAT, IF_ROW_BATCH_NUM ORDER BY IF_ROW_BATCH_NUM;
5. Prepare the IFB file and run the EIM job from UI:
Below IFB file parameters will load the data from EIM table to Siebel Base tables.
[Siebel Interface Manager]
PROCESS = INSERT_RESP
PROCESS = UPDATE_RESP
[INSERT_RESP]
TYPE = IMPORT
BATCH = 1-99
TABLE = EIM_RESP
ONLY BASE TABLES = S_RESP
[UPDATE_RESP]
TYPE = IMPORT
BATCH = 100-199
TABLE = EIM_EMPLOYEE
ONLY BASE TABLES = S_PARTY, S_CONTACT, S_PER_RESP
INSERT ROWS = S_PARTY, FALSE
UPDATE ROWS = S_PARTY, FALSE
INSERT ROWS = S_CONTACT, FALSE
UPDATE ROWS = S_CONTACT, TRUE
Save this file as resp-inser.ifb and place it on siebesrvr/admin folder on server. Run the IFB file from Siebel UI SiteMap --> Administration - Server Management --> Jobs. Create a new job and enter Enterprise Integration Mgr in Job name field, put the ifb name in configuration file parameter and fill other necessary flags for debugging. Click submit.
6. Check the results in EIM table and from UI:
To check the results of imported data, run the scripts as mentioned at step 4 for checking the data in EIM table. Thereafter check some sample data from UI.
7. Downloadable Scripts:
Staging Table Script
Control File
EIM Table Script File
IFB File
8. Independent Resp Insert Interface (Downloadable ZIP File)
Click here to download the complete Responsibility Import interface as a standalone program. Read the instruction as in instructions.txt file inside the resp-insert folder.
"DO COMMENT ON THIS POST FOR FEEDBACK"
Mohammed Arif
http://siebel-eim.weebly.com
Importing new responsibilities and assigning them to users in siebel is one of the most common requirements in siebel projects. Most of the time it's a one time load for a set of users and at other times it may be used as periodic load for new users.
Below is the step by step method for loading new responsibilities and assigning them to users
Responsibility Import:
There are various approaches to load data into siebel. Here we will take a simple one.
- Put the responsibility data in a CSV format file (which can be opened in excel for editing).
- Create a staging/intermediate table to store the data temporarily (this is very useful if some data transformation is to be done before loading in EIM tables).
- Loading the data into staging table through SQLLDR utility.
- Execute the SQL scripts to load the staging table data in EIM table.
- Prepare the IFB file and Run the EIM job from UI.
- Check the results in EIM table and from UI.
- Downloadable scripts
- Independent LOV Interface (Downloadable ZIP File)
Lets discuss these in detail:
1. Prepare the data in CSV format:
Click here to download a sample CSV file and put the data in the defined format. Note that the username column in the sheet should corresponds to the S_USER.LOGIN information. Responsibility column will contain the name of the new responsibility and primary responsibility column should contain the name of the primary responsibility for that user. Mentioning the primary one is optional as it will pick one of the responsibilities as primary if not mentioned. Also mention the responsibility organization in the respective column. Save the file as resp-insert.csv.
2. Creating staging table:
CREATE TABLE "STAGING"."RESP_INSERT"
(
"LOGIN" VARCHAR2(50 BYTE),
"RESP" VARCHAR2(100 BYTE),
"PRIMARY" VARCHAR2(1 BYTE),
"RESPORG" VARCHAR2(100 BYTE)
);
COMMIT;
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON STAGING.RESP_INSERT TO SIEBEL;
COMMIT;
3. Loading the data in staging table through SQLLDR utility.
Prepare a control file (.ctl) and a batch (.bat) file an place them at the same location as the csv file. (Let say in D:\resp-insert\).
Structure of control file (resp-insert.ctl):
LOAD DATA
CHARACTERSET WE8ISO8859P1
TRUNCATE
INTO TABLE STAGING.RESP_INSERT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
LOGIN,
RESP,
PRIMARY,
RESPORG,
DESC
)
Structure of Batch file (resp-insert.bat):
@echo off
set ORCL_PATH=D:\Oracle\product\11.2.0\client_1\BIN
set DB_USR=XXXXXX
set DB_PAS=XXXXXX
set DB_CON=XXXXXX
set CUR_PATH=D:\resp-insert
%ORCL_PATH%\sqlldr %DB_USR%/%DB_PAS%@%DB_CON% CONTROL=%CUR_PATH%\resp-insert.ctl, DATA=%CUR_PATH%\resp-insert.csv, LOG=%CUR_PATH%\SQLLDR_resp-insert.log, SKIP=1, ERRORS=1000, BAD=%CUR_PATH%\resp-insert.bad, DISCARD=%CUR_PATH%\resp-insert.dis
So now up to here we have 3 files, resp-insert.ctl, resp-insert.csv and resp-insert.bat at d:\resp-insert\ location.
Execute the resp-insert.bat file after filling the necessary parameters like db username, password etc. You can check the data in staging table by executing
SELECT * FROM STAGING.RESP_INSERT;
4. Execute the SQL scripts to load the staging table data in EIM table.
Execute the below scripts to insert data into EIM table. You can change the batch numbers if required.
DELETE FROM EIM_RESP WHERE IF_ROW_BATCH_NUM BETWEEN 1 and 99;
DELETE FROM EIM_EMPLOYEE WHERE IF_ROW_BATCH_NUM BETWEEN 100 and 199;
COMMIT;
/*Inserting new responsibilities into EIM table*/
INSERT INTO EIM_RESP
(
ROW_ID,
IF_ROW_STAT,
IF_ROW_BATCH_NUM,
RESP_NAME,
RESP_BU,
RESP_DESC_TEXT
)
SELECT DISTINCT
ROWNUM,
'FOR_IMPORT',
1 + floor((rownum-1)/5000),
STG.RESP,
STG.RESPORG,
STG.DESC
FROM (SELECT DISTINCT RESP, RESPORG, DESC FROM RESP_INSERT) STG;
COMMIT;
/*Inserting user-responsibility details in EIM table*/
INSERT INTO EIM_EMPLOYEE
(
ROW_ID,
PARTY_UID,
PARTY_TYPE_CD,
IF_ROW_STAT,
IF_ROW_BATCH_NUM,
CON_BU,
CON_PRIV_FLG,
CON_PERSON_UID,
RESP_BU,
RESP_NAME,
CON_PR_RESP
)
SELECT
ROWNUM,
PARTY.PARTY_UID,
PARTY.PARTY_TYPE_CD,
'FOR_IMPORT',
100 + floor((rownum-1)/5000),
CONBU.NAME,
CON.PRIV_FLG,
CON.PERSON_UID,
RESPBU.NAME,
stg.resp,
stg.primary
from
S_USER USR,
S_BU RESPBU,
S_CONTACT CON,
S_BU CONBU,
S_PARTY PARTY,
FROM STG.RESP_INSERT STG
WHERE
PARTY.ROW_ID = CON.PAR_ROW_ID
AND CON.PAR_ROW_ID = USR.PAR_ROW_ID
AND CON.BU_ID = CONBU.ROW_ID
AND STG.RESPORG = RESPBU.NAME
AND STG.LOGIN = USR.LOGIN;
COMMIT;
select COUNT(1), IF_ROW_STAT, IF_ROW_BATCH_NUM
FROM EIM_RESP WHERE IF_ROW_BATCH_NUM BETWEEN 1 AND 99
GROUP BY IF_ROW_STAT, IF_ROW_BATCH_NUM ORDER BY IF_ROW_BATCH_NUM;
select COUNT(1), IF_ROW_STAT, IF_ROW_BATCH_NUM
FROM EIM_EMPLOYEE WHERE IF_ROW_BATCH_NUM BETWEEN 100 AND 199
GROUP BY IF_ROW_STAT, IF_ROW_BATCH_NUM ORDER BY IF_ROW_BATCH_NUM;
5. Prepare the IFB file and run the EIM job from UI:
Below IFB file parameters will load the data from EIM table to Siebel Base tables.
[Siebel Interface Manager]
PROCESS = INSERT_RESP
PROCESS = UPDATE_RESP
[INSERT_RESP]
TYPE = IMPORT
BATCH = 1-99
TABLE = EIM_RESP
ONLY BASE TABLES = S_RESP
[UPDATE_RESP]
TYPE = IMPORT
BATCH = 100-199
TABLE = EIM_EMPLOYEE
ONLY BASE TABLES = S_PARTY, S_CONTACT, S_PER_RESP
INSERT ROWS = S_PARTY, FALSE
UPDATE ROWS = S_PARTY, FALSE
INSERT ROWS = S_CONTACT, FALSE
UPDATE ROWS = S_CONTACT, TRUE
6. Check the results in EIM table and from UI:
To check the results of imported data, run the scripts as mentioned at step 4 for checking the data in EIM table. Thereafter check some sample data from UI.
7. Downloadable Scripts:
Staging Table Script
Control File
EIM Table Script File
IFB File
8. Independent Resp Insert Interface (Downloadable ZIP File)
Click here to download the complete Responsibility Import interface as a standalone program. Read the instruction as in instructions.txt file inside the resp-insert folder.
"DO COMMENT ON THIS POST FOR FEEDBACK"
Mohammed Arif
http://siebel-eim.weebly.com
No comments:
Post a Comment