CREATE RESPONSIBILITY THROUGH PL/SQL PACKAGE
FIRST CREATE THE BELOW STAGING TABLE TO LOAD DATA FROM FLAT FILE
CREATE TABLE APPS.XX_RESP_LOAD
(
RESP_NAME VARCHAR2(75 BYTE),
RESP_APPL VARCHAR2(100 BYTE),
RESP_KEY VARCHAR2(100 BYTE),
MENU VARCHAR2(50 BYTE),
DATAGRP VARCHAR2(50 BYTE),
REQ_GRP VARCHAR2(50 BYTE),
STATUS CHAR(1 BYTE),
ERRORMSG VARCHAR2(500 BYTE)
)
PACKAGE TO BE CREATED TO CREATE THE RESPONSIBILITY
PACKAGE SPECIFICATION
CREATE OR REPLACE PACKAGE APPS.xx_create_add_RESP_PKG
AS
PROCEDURE INSERT_ROW (
X_ROWID IN OUT NOCOPY VARCHAR2,
X_RESPONSIBILITY_ID IN NUMBER,
X_APPLICATION_ID IN NUMBER,
X_WEB_HOST_NAME IN VARCHAR2,
X_WEB_AGENT_NAME IN VARCHAR2,
X_DATA_GROUP_APPLICATION_ID IN NUMBER,
X_DATA_GROUP_ID IN NUMBER,
X_MENU_ID IN NUMBER,
X_START_DATE IN DATE,
X_END_DATE IN DATE,
X_GROUP_APPLICATION_ID IN NUMBER,
X_REQUEST_GROUP_ID IN NUMBER,
X_VERSION IN VARCHAR2,
X_RESPONSIBILITY_KEY IN VARCHAR2,
X_RESPONSIBILITY_NAME IN VARCHAR2,
X_DESCRIPTION IN VARCHAR2,
X_CREATION_DATE IN DATE,
X_CREATED_BY IN NUMBER,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER
);
END xx_create_add_RESP_PKG;
PACKAGE BODY
CREATE OR REPLACE PACKAGE BODY APPS.xx_create_add_RESP_PKG
AS
/* $Header: AFSCRSPB.pls 120.4 2007/03/16 23:27:25 jvalenti ship $ */
PROCEDURE INSERT_ROW (
X_ROWID IN OUT NOCOPY VARCHAR2,
X_RESPONSIBILITY_ID IN NUMBER,
X_APPLICATION_ID IN NUMBER,
X_WEB_HOST_NAME IN VARCHAR2,
X_WEB_AGENT_NAME IN VARCHAR2,
X_DATA_GROUP_APPLICATION_ID IN NUMBER,
X_DATA_GROUP_ID IN NUMBER,
X_MENU_ID IN NUMBER,
X_START_DATE IN DATE,
X_END_DATE IN DATE,
X_GROUP_APPLICATION_ID IN NUMBER,
X_REQUEST_GROUP_ID IN NUMBER,
X_VERSION IN VARCHAR2,
X_RESPONSIBILITY_KEY IN VARCHAR2,
X_RESPONSIBILITY_NAME IN VARCHAR2,
X_DESCRIPTION IN VARCHAR2,
X_CREATION_DATE IN DATE,
X_CREATED_BY IN NUMBER,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER
)
IS
CURSOR C
IS
SELECT ROWID
FROM FND_RESPONSIBILITY
WHERE RESPONSIBILITY_ID = X_RESPONSIBILITY_ID
AND APPLICATION_ID = X_APPLICATION_ID;
BEGIN
INSERT INTO FND_RESPONSIBILITY (WEB_HOST_NAME,
WEB_AGENT_NAME,
APPLICATION_ID,
RESPONSIBILITY_ID,
DATA_GROUP_APPLICATION_ID,
DATA_GROUP_ID,
MENU_ID,
START_DATE,
END_DATE,
GROUP_APPLICATION_ID,
REQUEST_GROUP_ID,
VERSION,
RESPONSIBILITY_KEY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES (X_WEB_HOST_NAME,
X_WEB_AGENT_NAME,
X_APPLICATION_ID,
X_RESPONSIBILITY_ID,
X_DATA_GROUP_APPLICATION_ID,
X_DATA_GROUP_ID,
X_MENU_ID,
X_START_DATE,
X_END_DATE,
X_GROUP_APPLICATION_ID,
X_REQUEST_GROUP_ID,
X_VERSION,
X_RESPONSIBILITY_KEY,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN);
-- Added for Function Security Cache Invalidation Project
fnd_function_security_cache.insert_resp (X_RESPONSIBILITY_ID,
X_APPLICATION_ID);
INSERT INTO FND_RESPONSIBILITY_TL (APPLICATION_ID,
RESPONSIBILITY_ID,
RESPONSIBILITY_NAME,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG)
SELECT X_APPLICATION_ID,
X_RESPONSIBILITY_ID,
X_RESPONSIBILITY_NAME,
X_DESCRIPTION,
X_CREATED_BY,
X_CREATION_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATE_LOGIN,
L.LANGUAGE_CODE,
USERENV ('LANG')
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND NOT EXISTS
(SELECT NULL
FROM FND_RESPONSIBILITY_TL T
WHERE T.RESPONSIBILITY_ID = X_RESPONSIBILITY_ID
AND T.APPLICATION_ID = X_APPLICATION_ID
AND T.LANGUAGE = L.LANGUAGE_CODE);
-- Added to maintain "ANY/ALL" diamonds of roles for user/resps.
fnd_user_resp_groups_api.sync_roles_all_secgrps (X_RESPONSIBILITY_ID,
X_APPLICATION_ID,
X_RESPONSIBILITY_KEY,
X_START_DATE,
X_END_DATE);
OPEN c;
FETCH c INTO X_ROWID;
IF (c%NOTFOUND)
THEN
CLOSE c;
DBMS_OUTPUT.PUT_LINE ('NO DATA FOUND IN THE CURSOR');
RAISE NO_DATA_FOUND;
END IF;
CLOSE c;
END INSERT_ROW;
END xx_create_add_RESP_PKG;
/
ANONYMOUS PL/SQL BLOCK FOR CREATING RESPONSIBILITY
DECLARE
CURSOR c1
IS
SELECT *
FROM xx_smp_resp_load
WHERE status IS NULL;
num_total_rows NUMBER;
v_rowid VARCHAR2 (500);
v_web_host_name VARCHAR2 (500);
v_web_agent_name VARCHAR2 (500);
v_version VARCHAR2 (500);
v_responsibility_id NUMBER;
-- some variables
v_resp_name VARCHAR2 (100) := 'XX INV USER - STORE';
v_application VARCHAR2 (100) := 'Asia/Pacific Localizations';
v_resp_key VARCHAR2 (100) := 'XX_INV_STORE_USER';
v_menu_name VARCHAR2 (100) := 'XX_JAI_INV_RECEIVING - India';
v_data_group VARCHAR2 (100) := 'Standard';
v_req_group VARCHAR2 (100) := 'JAI_Inventory_RG';
-- ids and other **** used by the API
v_application_id NUMBER;
v_data_group_id NUMBER;
v_menu_id NUMBER;
v_request_group_id NUMBER;
v_error_msg VARCHAR2 (500);
BEGIN
num_total_rows := 0;
FOR i IN c1
LOOP
BEGIN
-- get application_id
SELECT application_id
INTO v_application_id
FROM fnd_application_vl
WHERE application_name = i.resp_appl;
-- get data group id
SELECT data_group_id
INTO v_data_group_id
FROM fnd_data_groups
WHERE data_group_name = i.datagrp;
-- get the menu_id
SELECT menu_id
INTO v_menu_id
FROM fnd_menus_tl
WHERE user_menu_name = i.menu;
-- get the request_group_id
SELECT request_group_id
INTO v_request_group_id
FROM fnd_request_groups
WHERE request_group_name = i.req_grp;
-- get current responsibility_id
SELECT fnd_responsibility_s.NEXTVAL
INTO v_responsibility_id
FROM DUAL;
-- run API
xx_create_resp_pkg.insert_row
(
-- out params
x_rowid => v_rowid,
-- in params
x_responsibility_id => v_responsibility_id,
x_application_id => v_application_id,
x_web_host_name => v_web_host_name,
x_web_agent_name => v_web_agent_name,
x_data_group_application_id => v_application_id,
x_data_group_id => v_data_group_id,
x_menu_id => v_menu_id,
x_start_date => SYSDATE,
x_end_date => NULL,
x_group_application_id => v_application_id,
x_request_group_id => v_request_group_id,
x_version => 4,
x_responsibility_key => i.resp_key,
x_responsibility_name => i.resp_name,
x_description => '',
x_creation_date => SYSDATE,
x_created_by => -1,
x_last_update_date => SYSDATE,
x_last_updated_by => -1,
x_last_update_login => 0
);
COMMIT;
UPDATE xx_resp_load
SET status = 'Y', errormsg = null
WHERE resp_name = i.resp_name;
COMMIT;
num_total_rows := c1%ROWCOUNT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'Error while creating Resposibility => '
|| i.resp_name
);
v_error_msg := SQLERRM;
UPDATE xx_resp_load
SET status = 'E',
errormsg = v_error_msg
WHERE resp_name = i.resp_name;
COMMIT;
END;
END LOOP;
IF num_total_rows > 0
THEN
--DBMS_OUTPUT.new_line;
DBMS_OUTPUT.put_line ( 'Total Rows Inserted = '
|| TO_CHAR (num_total_rows)
);
fnd_file.put_line (fnd_file.LOG, 'Rows Inserted:' || num_total_rows);
ELSE
fnd_file.put_line (fnd_file.LOG,
'There is no records available to Create'
);
DBMS_OUTPUT.put_line ('There is no records available to Create');
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('failed to add resp' || SQLERRM);
END;
FIRST CREATE THE BELOW STAGING TABLE TO LOAD DATA FROM FLAT FILE
CREATE TABLE APPS.XX_RESP_LOAD
(
RESP_NAME VARCHAR2(75 BYTE),
RESP_APPL VARCHAR2(100 BYTE),
RESP_KEY VARCHAR2(100 BYTE),
MENU VARCHAR2(50 BYTE),
DATAGRP VARCHAR2(50 BYTE),
REQ_GRP VARCHAR2(50 BYTE),
STATUS CHAR(1 BYTE),
ERRORMSG VARCHAR2(500 BYTE)
)
PACKAGE TO BE CREATED TO CREATE THE RESPONSIBILITY
PACKAGE SPECIFICATION
CREATE OR REPLACE PACKAGE APPS.xx_create_add_RESP_PKG
AS
PROCEDURE INSERT_ROW (
X_ROWID IN OUT NOCOPY VARCHAR2,
X_RESPONSIBILITY_ID IN NUMBER,
X_APPLICATION_ID IN NUMBER,
X_WEB_HOST_NAME IN VARCHAR2,
X_WEB_AGENT_NAME IN VARCHAR2,
X_DATA_GROUP_APPLICATION_ID IN NUMBER,
X_DATA_GROUP_ID IN NUMBER,
X_MENU_ID IN NUMBER,
X_START_DATE IN DATE,
X_END_DATE IN DATE,
X_GROUP_APPLICATION_ID IN NUMBER,
X_REQUEST_GROUP_ID IN NUMBER,
X_VERSION IN VARCHAR2,
X_RESPONSIBILITY_KEY IN VARCHAR2,
X_RESPONSIBILITY_NAME IN VARCHAR2,
X_DESCRIPTION IN VARCHAR2,
X_CREATION_DATE IN DATE,
X_CREATED_BY IN NUMBER,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER
);
END xx_create_add_RESP_PKG;
PACKAGE BODY
CREATE OR REPLACE PACKAGE BODY APPS.xx_create_add_RESP_PKG
AS
/* $Header: AFSCRSPB.pls 120.4 2007/03/16 23:27:25 jvalenti ship $ */
PROCEDURE INSERT_ROW (
X_ROWID IN OUT NOCOPY VARCHAR2,
X_RESPONSIBILITY_ID IN NUMBER,
X_APPLICATION_ID IN NUMBER,
X_WEB_HOST_NAME IN VARCHAR2,
X_WEB_AGENT_NAME IN VARCHAR2,
X_DATA_GROUP_APPLICATION_ID IN NUMBER,
X_DATA_GROUP_ID IN NUMBER,
X_MENU_ID IN NUMBER,
X_START_DATE IN DATE,
X_END_DATE IN DATE,
X_GROUP_APPLICATION_ID IN NUMBER,
X_REQUEST_GROUP_ID IN NUMBER,
X_VERSION IN VARCHAR2,
X_RESPONSIBILITY_KEY IN VARCHAR2,
X_RESPONSIBILITY_NAME IN VARCHAR2,
X_DESCRIPTION IN VARCHAR2,
X_CREATION_DATE IN DATE,
X_CREATED_BY IN NUMBER,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER
)
IS
CURSOR C
IS
SELECT ROWID
FROM FND_RESPONSIBILITY
WHERE RESPONSIBILITY_ID = X_RESPONSIBILITY_ID
AND APPLICATION_ID = X_APPLICATION_ID;
BEGIN
INSERT INTO FND_RESPONSIBILITY (WEB_HOST_NAME,
WEB_AGENT_NAME,
APPLICATION_ID,
RESPONSIBILITY_ID,
DATA_GROUP_APPLICATION_ID,
DATA_GROUP_ID,
MENU_ID,
START_DATE,
END_DATE,
GROUP_APPLICATION_ID,
REQUEST_GROUP_ID,
VERSION,
RESPONSIBILITY_KEY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES (X_WEB_HOST_NAME,
X_WEB_AGENT_NAME,
X_APPLICATION_ID,
X_RESPONSIBILITY_ID,
X_DATA_GROUP_APPLICATION_ID,
X_DATA_GROUP_ID,
X_MENU_ID,
X_START_DATE,
X_END_DATE,
X_GROUP_APPLICATION_ID,
X_REQUEST_GROUP_ID,
X_VERSION,
X_RESPONSIBILITY_KEY,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN);
-- Added for Function Security Cache Invalidation Project
fnd_function_security_cache.insert_resp (X_RESPONSIBILITY_ID,
X_APPLICATION_ID);
INSERT INTO FND_RESPONSIBILITY_TL (APPLICATION_ID,
RESPONSIBILITY_ID,
RESPONSIBILITY_NAME,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG)
SELECT X_APPLICATION_ID,
X_RESPONSIBILITY_ID,
X_RESPONSIBILITY_NAME,
X_DESCRIPTION,
X_CREATED_BY,
X_CREATION_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATE_LOGIN,
L.LANGUAGE_CODE,
USERENV ('LANG')
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND NOT EXISTS
(SELECT NULL
FROM FND_RESPONSIBILITY_TL T
WHERE T.RESPONSIBILITY_ID = X_RESPONSIBILITY_ID
AND T.APPLICATION_ID = X_APPLICATION_ID
AND T.LANGUAGE = L.LANGUAGE_CODE);
-- Added to maintain "ANY/ALL" diamonds of roles for user/resps.
fnd_user_resp_groups_api.sync_roles_all_secgrps (X_RESPONSIBILITY_ID,
X_APPLICATION_ID,
X_RESPONSIBILITY_KEY,
X_START_DATE,
X_END_DATE);
OPEN c;
FETCH c INTO X_ROWID;
IF (c%NOTFOUND)
THEN
CLOSE c;
DBMS_OUTPUT.PUT_LINE ('NO DATA FOUND IN THE CURSOR');
RAISE NO_DATA_FOUND;
END IF;
CLOSE c;
END INSERT_ROW;
END xx_create_add_RESP_PKG;
/
ANONYMOUS PL/SQL BLOCK FOR CREATING RESPONSIBILITY
DECLARE
CURSOR c1
IS
SELECT *
FROM xx_smp_resp_load
WHERE status IS NULL;
num_total_rows NUMBER;
v_rowid VARCHAR2 (500);
v_web_host_name VARCHAR2 (500);
v_web_agent_name VARCHAR2 (500);
v_version VARCHAR2 (500);
v_responsibility_id NUMBER;
-- some variables
v_resp_name VARCHAR2 (100) := 'XX INV USER - STORE';
v_application VARCHAR2 (100) := 'Asia/Pacific Localizations';
v_resp_key VARCHAR2 (100) := 'XX_INV_STORE_USER';
v_menu_name VARCHAR2 (100) := 'XX_JAI_INV_RECEIVING - India';
v_data_group VARCHAR2 (100) := 'Standard';
v_req_group VARCHAR2 (100) := 'JAI_Inventory_RG';
-- ids and other **** used by the API
v_application_id NUMBER;
v_data_group_id NUMBER;
v_menu_id NUMBER;
v_request_group_id NUMBER;
v_error_msg VARCHAR2 (500);
BEGIN
num_total_rows := 0;
FOR i IN c1
LOOP
BEGIN
-- get application_id
SELECT application_id
INTO v_application_id
FROM fnd_application_vl
WHERE application_name = i.resp_appl;
-- get data group id
SELECT data_group_id
INTO v_data_group_id
FROM fnd_data_groups
WHERE data_group_name = i.datagrp;
-- get the menu_id
SELECT menu_id
INTO v_menu_id
FROM fnd_menus_tl
WHERE user_menu_name = i.menu;
-- get the request_group_id
SELECT request_group_id
INTO v_request_group_id
FROM fnd_request_groups
WHERE request_group_name = i.req_grp;
-- get current responsibility_id
SELECT fnd_responsibility_s.NEXTVAL
INTO v_responsibility_id
FROM DUAL;
-- run API
xx_create_resp_pkg.insert_row
(
-- out params
x_rowid => v_rowid,
-- in params
x_responsibility_id => v_responsibility_id,
x_application_id => v_application_id,
x_web_host_name => v_web_host_name,
x_web_agent_name => v_web_agent_name,
x_data_group_application_id => v_application_id,
x_data_group_id => v_data_group_id,
x_menu_id => v_menu_id,
x_start_date => SYSDATE,
x_end_date => NULL,
x_group_application_id => v_application_id,
x_request_group_id => v_request_group_id,
x_version => 4,
x_responsibility_key => i.resp_key,
x_responsibility_name => i.resp_name,
x_description => '',
x_creation_date => SYSDATE,
x_created_by => -1,
x_last_update_date => SYSDATE,
x_last_updated_by => -1,
x_last_update_login => 0
);
COMMIT;
UPDATE xx_resp_load
SET status = 'Y', errormsg = null
WHERE resp_name = i.resp_name;
COMMIT;
num_total_rows := c1%ROWCOUNT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'Error while creating Resposibility => '
|| i.resp_name
);
v_error_msg := SQLERRM;
UPDATE xx_resp_load
SET status = 'E',
errormsg = v_error_msg
WHERE resp_name = i.resp_name;
COMMIT;
END;
END LOOP;
IF num_total_rows > 0
THEN
--DBMS_OUTPUT.new_line;
DBMS_OUTPUT.put_line ( 'Total Rows Inserted = '
|| TO_CHAR (num_total_rows)
);
fnd_file.put_line (fnd_file.LOG, 'Rows Inserted:' || num_total_rows);
ELSE
fnd_file.put_line (fnd_file.LOG,
'There is no records available to Create'
);
DBMS_OUTPUT.put_line ('There is no records available to Create');
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('failed to add resp' || SQLERRM);
END;
No comments:
Post a Comment