[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_UPDATE_REC_DONE NUMBER := 1;
G_UPDATE_OVERLAP_ERROR NUMBER := -1;
G_UPDATE_REC_NOT_FOUND NUMBER := -2;
G_INSERT_REC_DONE NUMBER := 1;
G_INSERT_OVERLAP_ERROR NUMBER := -1;
SELECT COUNT(*)
INTO l_error_record_count
FROM ENG_CHANGE_PEOPLE_INTF
WHERE data_set_id = G_DATA_SET_ID
AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND process_status = G_PS_ERROR;
SELECT object_id
FROM fnd_objects
WHERE obj_name = c_object_name;
SELECT party_id
FROM hz_parties
WHERE party_type = 'GLOBAL'
AND party_name = 'All Users';
DELETE ENG_CHANGE_ROLES_TEMP;
INSERT into eng_change_roles_temp
(INTERNAL_ROLE_ID,INTERNAL_ROLE_NAME,DISPLAY_ROLE_NAME)
SELECT DISTINCT role_tl.menu_id internal_role_id,
role.menu_name internal_role_name,
role_tl.user_menu_name display_role_name
FROM fnd_menus_tl role_tl,
fnd_menus role,
fnd_menu_entries role_privs,
fnd_form_functions privs
WHERE privs.object_id = G_FND_OBJECT_ID
AND privs.function_id = role_privs.function_id
AND role_privs.menu_id = role_tl.menu_id
AND role_tl.menu_id = role.menu_id
AND role_tl.language = G_SESSION_LANG;
l_select_sql VARCHAR2(32767);
l_insert_sql VARCHAR2(500);
cursor_select INTEGER;
cursor_insert INTEGER;
DELETE ENG_LOGIN_ACCESS_CHANGES;
l_select_sql := 'SELECT OUT_ENG_CHANGES.CHANGE_NOTICE, OUT_ENG_CHANGES.CHANGE_MGMT_TYPE_CODE, OUT_ENG_CHANGES.ORGANIZATION_ID '
|| 'FROM ENG_ENGINEERING_CHANGES OUT_ENG_CHANGES ';
l_select_sql := l_select_sql || ' WHERE ' || l_sec_predicate ;
l_insert_sql := 'INSERT INTO ENG_LOGIN_ACCESS_CHANGES(CHANGE_NOTICE,CHANGE_MGMT_TYPE_CODE,ORGANIZATION_ID) VALUES (:l_change_notice_table, :l_change_mgmt_type_code_table,:l_org_id_table) ';
cursor_select := DBMS_SQL.OPEN_CURSOR;
cursor_insert := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_select,l_select_sql,DBMS_SQL.NATIVE);
DBMS_SQL.PARSE(cursor_insert,l_insert_sql,DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_ARRAY(cursor_select, 1,l_change_notice_table,2500, indx);
DBMS_SQL.DEFINE_ARRAY(cursor_select, 2,l_change_mgmt_type_code_table,2500, indx);
DBMS_SQL.DEFINE_ARRAY(cursor_select, 3,l_org_id_table,2500, indx);
cursor_execute := DBMS_SQL.EXECUTE(cursor_select);
l_count := DBMS_SQL.FETCH_ROWS(cursor_select);
DBMS_SQL.COLUMN_VALUE(cursor_select, 1, l_change_notice_table);
DBMS_SQL.COLUMN_VALUE(cursor_select, 2, l_change_mgmt_type_code_table);
DBMS_SQL.COLUMN_VALUE(cursor_select, 3, l_org_id_table);
DBMS_SQL.BIND_ARRAY(cursor_insert,':l_change_notice_table',l_change_notice_table);
DBMS_SQL.BIND_ARRAY(cursor_insert,':l_change_mgmt_type_code_table',l_change_mgmt_type_code_table);
DBMS_SQL.BIND_ARRAY(cursor_insert,':l_org_id_table',l_org_id_table);
cursor_execute := DBMS_SQL.EXECUTE(cursor_insert);
l_change_notice_table.DELETE;
l_org_id_table.DELETE;
l_change_mgmt_type_code_table.DELETE;
DBMS_SQL.CLOSE_CURSOR(cursor_select);
DBMS_SQL.CLOSE_CURSOR(cursor_insert);
IF DBMS_SQL.IS_OPEN(cursor_select) THEN
DBMS_SQL.CLOSE_CURSOR(cursor_select);
IF DBMS_SQL.IS_OPEN(cursor_insert) THEN
DBMS_SQL.CLOSE_CURSOR(cursor_insert);
PROCEDURE validate_update_grant
(p_transaction_type IN VARCHAR2
,p_transaction_id IN NUMBER
,p_change_id IN NUMBER
,p_organization_id IN NUMBER
,p_internal_role_id IN NUMBER
,p_user_party_id_char IN VARCHAR2
,p_group_party_id_char IN VARCHAR2
,p_global_party_id_char IN VARCHAR2
,p_company_party_id_char IN VARCHAR2
,p_start_date IN DATE
,p_end_date IN DATE
,x_return_status OUT NOCOPY NUMBER) IS
-- Start OF comments
-- API name : validate_update_grant
-- TYPE : PRIVATE
-- Pre-reqs : NONE
--
-- FUNCTION : To check if the required grant can be updated
-- and updates fnd_grants if required
-- NO ACTION IS PERFORMED ON eng_change_people_intf
--
-- Parameters:
-- IN : NONE
--
-- OUT : x_return_status NUMBER
-- Indicates the status of the record
-- -1 Record not found for update
-- -2 Record found for update but will cause overlap
-- 1 Record found and updated
--
CURSOR c_get_update_grantid
(cp_change_id IN NUMBER
,cp_organization_id IN NUMBER
,cp_menu_id IN NUMBER
,cp_object_id IN NUMBER
,cp_user_party_id_char IN VARCHAR2
,cp_group_party_id_char IN VARCHAR2
,cp_global_party_id_char IN VARCHAR2
,cp_company_party_id_char IN VARCHAR2
,cp_start_date IN DATE
) IS
SELECT grant_guid
FROM fnd_grants grants
WHERE grants.object_id = G_FND_OBJECT_ID
AND grants.menu_id = cp_menu_id
AND grants.instance_type = 'INSTANCE'
AND grants.instance_pk1_value = TO_CHAR(cp_change_id)
-- Commented as PK2_Value for ENG_CHANGE in fnd_objects is NULL
-- AND grants.instance_pk2_value = TO_CHAR(cp_organization_id)
AND ((grants.grantee_type = 'USER' AND grants.grantee_key = cp_user_party_id_char ) OR
(grants.grantee_type = 'GROUP' AND grants.grantee_key = cp_group_party_id_char) OR
(grants.grantee_type = 'GLOBAL' AND grants.grantee_key = cp_global_party_id_char) OR
(grants.grantee_type = 'COMPANY' AND grants.grantee_key = cp_company_party_id_char)
)
AND start_date = cp_start_date;
CURSOR c_get_valid_update
(cp_grant_guid IN RAW
,cp_change_id IN NUMBER
,cp_organization_id IN NUMBER
,cp_menu_id IN NUMBER
,cp_object_id IN NUMBER
,cp_user_party_id_char IN VARCHAR2
,cp_group_party_id_char IN VARCHAR2
,cp_global_party_id_char IN VARCHAR2
,cp_company_party_id_char IN VARCHAR2
,cp_start_date IN DATE
,cp_end_date IN DATE
) IS
SELECT grant_guid
FROM fnd_grants grants
WHERE grants.grant_guid <> cp_grant_guid
AND grants.object_id = cp_object_id
AND grants.menu_id = cp_menu_id
AND grants.instance_type = 'INSTANCE'
AND grants.instance_pk1_value = TO_CHAR(cp_change_id)
-- Commented as PK2_Value for ENG_CHANGE in fnd_objects is NULL
-- AND grants.instance_pk2_value = TO_CHAR(cp_organization_id)
AND ((grants.grantee_type = 'USER' AND grants.grantee_key = cp_user_party_id_char ) OR
(grants.grantee_type = 'GROUP' AND grants.grantee_key = cp_group_party_id_char) OR
(grants.grantee_type = 'GLOBAL' AND grants.grantee_key = cp_global_party_id_char) OR
(grants.grantee_type = 'COMPANY' AND grants.grantee_key = cp_company_party_id_char)
)
AND start_date <= NVL(cp_end_date, start_date)
AND NVL(end_date,cp_start_date) >= cp_start_date;
OPEN c_get_update_grantid
(cp_change_id => p_change_id
,cp_organization_id => p_organization_id
,cp_menu_id => p_internal_role_id
,cp_object_id => G_FND_OBJECT_ID
,cp_user_party_id_char => p_user_party_id_char
,cp_group_party_id_char => p_group_party_id_char
,cp_global_party_id_char => p_global_party_id_char
,cp_company_party_id_char => p_company_party_id_char
,cp_start_date => p_start_date
);
FETCH c_get_update_grantid INTO l_grant_guid;
IF c_get_update_grantid%FOUND THEN
--
-- there will be only one record with a given start date
-- check if the update will cause any overlaps
--
OPEN c_get_valid_update
(cp_grant_guid => l_grant_guid
,cp_change_id => p_change_id
,cp_organization_id => p_organization_id
,cp_menu_id => p_internal_role_id
,cp_object_id => G_FND_OBJECT_ID
,cp_user_party_id_char => p_user_party_id_char
,cp_group_party_id_char => p_group_party_id_char
,cp_global_party_id_char => p_global_party_id_char
,cp_company_party_id_char => p_company_party_id_char
,cp_start_date => p_start_date
,cp_end_date => p_end_date
);
FETCH c_get_valid_update INTO l_temp_grant_guid;
IF c_get_valid_update%FOUND THEN
--
-- overlap will occur after update
--
x_return_status := G_UPDATE_OVERLAP_ERROR;
FND_GRANTS_PKG.Update_Grant
(p_api_version => 1.0
,p_grant_guid => l_grant_guid
,p_start_date => p_start_date
,p_end_date => p_end_date
,x_success => l_success
);
x_return_status := G_UPDATE_REC_DONE;
END IF; -- c_get_valid_update
CLOSE c_get_valid_update;
x_return_status := G_UPDATE_REC_NOT_FOUND;
IF p_transaction_type = 'UPDATE' THEN
IF G_DEBUG_MODE >= DEBUG_MODE_ERROR THEN
l_token_tbl_one(1).token_name := 'TYPE';
END IF; -- p_transaction_type UPDATE
END IF; -- c_get_update_grantid
CLOSE c_get_update_grantid;
IF c_get_update_grantid%ISOPEN THEN
CLOSE c_get_update_grantid;
IF c_get_valid_update%ISOPEN THEN
CLOSE c_get_valid_update;
END validate_update_grant;
PROCEDURE validate_insert_grant
(p_transaction_type IN VARCHAR2
,p_transaction_id IN NUMBER
,p_change_id IN NUMBER
,p_organization_id IN NUMBER
,p_internal_role_id IN NUMBER
,p_internal_role_name IN VARCHAR2
,p_grantee_type IN VARCHAR2
,p_grantee_key IN VARCHAR2
,p_user_party_id_char IN VARCHAR2
,p_group_party_id_char IN VARCHAR2
,p_global_party_id_char IN VARCHAR2
,p_company_party_id_char IN VARCHAR2
,p_start_date IN DATE
,p_end_date IN DATE
,x_return_status OUT NOCOPY NUMBER) IS
-- Start OF comments
-- API name : validate_insert_grant
-- TYPE : PRIVATE
-- Pre-reqs : NONE
--
-- FUNCTION : To check if the required grant is valid for insert
-- and inserts the record into fnd_grants if valid
-- NO ACTION IS PERFORMED ON eng_change_people_intf
--
-- Parameters:
-- IN : NONE
--
-- OUT : x_return_status NUMBER
-- Indicates the status of the record
-- -1 Record not found for update
-- -2 Record found for update but will cause overlap
-- 1 Record found and updated
--
CURSOR c_get_overlap_grantid
(cp_change_id IN NUMBER
,cp_organization_id IN NUMBER
,cp_menu_id IN NUMBER
,cp_object_id IN NUMBER
,cp_user_party_id_char IN VARCHAR2
,cp_group_party_id_char IN VARCHAR2
,cp_global_party_id_char IN VARCHAR2
,cp_company_party_id_char IN VARCHAR2
,cp_start_date IN DATE
,cp_end_date IN DATE
) IS
SELECT grant_guid
FROM fnd_grants grants
WHERE grants.object_id = cp_object_id
AND grants.menu_id = cp_menu_id
AND grants.instance_type = 'INSTANCE'
AND grants.instance_pk1_value = TO_CHAR(cp_change_id)
-- Commented as PK2_Value for ENG_CHANGE in fnd_objects is NULL
-- AND grants.instance_pk2_value = TO_CHAR(cp_organization_id)
AND ((grants.grantee_type = 'USER' AND grants.grantee_key = cp_user_party_id_char ) OR
(grants.grantee_type = 'GROUP' AND grants.grantee_key = cp_group_party_id_char) OR
(grants.grantee_type = 'GLOBAL' AND grants.grantee_key = cp_global_party_id_char) OR
(grants.grantee_type = 'COMPANY' AND grants.grantee_key = cp_company_party_id_char)
)
AND start_date <= NVL(cp_end_date, start_date)
AND NVL(end_date,cp_start_date) >= cp_start_date;
x_return_status := G_INSERT_OVERLAP_ERROR;
x_return_status := G_INSERT_REC_DONE;
END validate_insert_grant;
SELECT change_id, organization_id, grantee_party_id, grantee_type,
start_date, end_date, transaction_id, internal_role_id, transaction_type,
internal_role_name,
DECODE(grantee_type, 'USER', 'HZ_PARTY:'||TO_CHAR(grantee_party_id),
'GROUP','HZ_GROUP:'||TO_CHAR(grantee_party_id),
-- bug: 3460466
-- All Users is now represented by grantee_key = 'GLOBAL' in fnd_grants
-- 'GLOBAL','HZ_GLOBAL:'||TO_CHAR(grantee_party_id),
'GLOBAL',grantee_type,
'HZ_COMPANY:'||TO_CHAR(grantee_party_id)) grantee_key,
DECODE(transaction_type, 'CREATE', ORDER_BY_CREATE,
'UPDATE', ORDER_BY_UPDATE,
'SYNC', ORDER_BY_SYNC,
'DELETE', ORDER_BY_DELETE,
ORDER_BY_OTHERS) trans_type
FROM eng_change_people_intf
WHERE data_set_id = G_DATA_SET_ID
AND process_status = G_PS_IN_PROCESS
ORDER BY trans_type, transaction_id;
CURSOR c_get_delete_grantid
(cp_change_id IN NUMBER
,cp_organization_id IN NUMBER
,cp_menu_id IN NUMBER
,cp_object_id IN NUMBER
,cp_user_party_id_char IN VARCHAR2
,cp_group_party_id_char IN VARCHAR2
,cp_global_party_id_char IN VARCHAR2
,cp_company_party_id_char IN VARCHAR2
,cp_start_date IN DATE
,cp_end_date IN DATE
) IS
SELECT grant_guid
FROM fnd_grants grants
WHERE grants.object_id = G_FND_OBJECT_ID
AND grants.menu_id = cp_menu_id
AND grants.instance_type = 'INSTANCE'
AND grants.instance_pk1_value = TO_CHAR(cp_change_id)
-- Commented as PK2_Value for ENG_CHANGE in fnd_objects is NULL
-- AND grants.instance_pk2_value = TO_CHAR(cp_organization_id)
AND ((grants.grantee_type = 'USER' AND grants.grantee_key = cp_user_party_id_char ) OR
(grants.grantee_type = 'GROUP' AND grants.grantee_key = cp_group_party_id_char) OR
(grants.grantee_type = 'GLOBAL' AND grants.grantee_key = cp_global_party_id_char) OR
(grants.grantee_type = 'COMPANY' AND grants.grantee_key = cp_company_party_id_char)
)
AND start_date = cp_start_date
AND ((end_date IS NULL AND cp_end_date is NULL) OR (end_date = cp_end_date));
l_boolean_delete boolean := TRUE;
l_boolean_update boolean := TRUE;
IF cr.transaction_type = 'DELETE' THEN
----------------------------
-- delete records first --
----------------------------
OPEN c_get_delete_grantid
(cp_change_id => cr.change_id
,cp_organization_id => cr.organization_id
,cp_menu_id => cr.internal_role_id
,cp_object_id => G_FND_OBJECT_ID
,cp_user_party_id_char => l_user_party_id_char
,cp_group_party_id_char => l_group_party_id_char
,cp_global_party_id_char => l_global_party_id_char
,cp_company_party_id_char => l_company_party_id_char
,cp_start_date => cr.start_date
,cp_end_date => cr.end_date);
FETCH c_get_delete_grantid INTO l_grant_guid;
IF c_get_delete_grantid%FOUND THEN
FND_GRANTS_PKG.Revoke_Grant
(p_api_version => 1.0
,p_grant_guid => l_grant_guid
,x_success => l_success
,x_errorcode => l_return_status
);
UPDATE eng_change_people_intf
SET process_status = G_PS_SUCCESS
WHERE transaction_id = cr.transaction_id;
UPDATE eng_change_people_intf
SET process_status = G_PS_ERROR
WHERE transaction_id = cr.transaction_id;
END IF; -- c_get_delete_grantid
CLOSE c_get_delete_grantid;
ELSIF cr.transaction_type = 'UPDATE' THEN
----------------------------
-- check for update now --
----------------------------
validate_update_grant
(p_transaction_type => cr.transaction_type
,p_transaction_id => cr.transaction_id
,p_change_id => cr.change_id
,p_organization_id => cr.organization_id
,p_internal_role_id => cr.internal_role_id
,p_user_party_id_char => l_user_party_id_char
,p_group_party_id_char => l_group_party_id_char
,p_global_party_id_char => l_global_party_id_char
,p_company_party_id_char => l_company_party_id_char
,p_start_date => cr.start_date
,p_end_date => cr.end_date
,x_return_status => l_return_status
);
IF l_return_status = G_UPDATE_REC_DONE THEN
-- record successfully updated
UPDATE eng_change_people_intf
SET process_status = G_PS_SUCCESS
WHERE transaction_id = cr.transaction_id;
ELSIF l_return_status = G_UPDATE_REC_NOT_FOUND THEN
-- no record found for overlap
UPDATE eng_change_people_intf
SET process_status = G_PS_ERROR
WHERE transaction_id = cr.transaction_id;
ELSIF l_return_status = G_UPDATE_OVERLAP_ERROR THEN
-- overlap will occur if update is done
UPDATE eng_change_people_intf
SET process_status = G_PS_ERROR
WHERE transaction_id = cr.transaction_id;
validate_update_grant
(p_transaction_type => cr.transaction_type
,p_transaction_id => cr.transaction_id
,p_change_id => cr.change_id
,p_organization_id => cr.organization_id
,p_internal_role_id => cr.internal_role_id
,p_user_party_id_char => l_user_party_id_char
,p_group_party_id_char => l_group_party_id_char
,p_global_party_id_char => l_global_party_id_char
,p_company_party_id_char => l_company_party_id_char
,p_start_date => cr.start_date
,p_end_date => cr.end_date
,x_return_status => l_return_status
);
IF l_return_status = G_UPDATE_REC_DONE THEN
-- record successfully updated
UPDATE eng_change_people_intf
SET process_status = G_PS_SUCCESS
WHERE transaction_id = cr.transaction_id;
ELSIF l_return_status = G_UPDATE_OVERLAP_ERROR THEN
-- overlap will occur if update is done
UPDATE eng_change_people_intf
SET process_status = G_PS_ERROR
WHERE transaction_id = cr.transaction_id;
ELSIF l_return_status = G_UPDATE_REC_NOT_FOUND THEN
-- no record found for overlap
-- now insert the record.
validate_insert_grant
(p_transaction_type => cr.transaction_type
,p_transaction_id => cr.transaction_id
,p_change_id => cr.change_id
,p_organization_id => cr.organization_id
,p_internal_role_id => cr.internal_role_id
,p_internal_role_name => cr.internal_role_name
,p_grantee_type => cr.grantee_type
,p_grantee_key => cr.grantee_key
,p_user_party_id_char => l_user_party_id_char
,p_group_party_id_char => l_group_party_id_char
,p_global_party_id_char => l_global_party_id_char
,p_company_party_id_char => l_company_party_id_char
,p_start_date => cr.start_date
,p_end_date => cr.end_date
,x_return_status => l_return_status
);
IF l_return_status = G_INSERT_REC_DONE THEN
-- record successfully inserted
UPDATE eng_change_people_intf
SET process_status = G_PS_SUCCESS
WHERE transaction_id = cr.transaction_id;
ELSIF l_return_status = G_INSERT_OVERLAP_ERROR THEN
-- insert overlap error
UPDATE eng_change_people_intf
SET process_status = G_PS_ERROR
WHERE transaction_id = cr.transaction_id;
validate_insert_grant
(p_transaction_type => cr.transaction_type
,p_transaction_id => cr.transaction_id
,p_change_id => cr.change_id
,p_organization_id => cr.organization_id
,p_internal_role_id => cr.internal_role_id
,p_internal_role_name => cr.internal_role_name
,p_grantee_type => cr.grantee_type
,p_grantee_key => cr.grantee_key
,p_user_party_id_char => l_user_party_id_char
,p_group_party_id_char => l_group_party_id_char
,p_global_party_id_char => l_global_party_id_char
,p_company_party_id_char => l_company_party_id_char
,p_start_date => cr.start_date
,p_end_date => cr.end_date
,x_return_status => l_return_status
);
IF l_return_status = G_INSERT_REC_DONE THEN
-- record successfully inserted
UPDATE eng_change_people_intf
SET process_status = G_PS_SUCCESS
WHERE transaction_id = cr.transaction_id;
ELSIF l_return_status = G_INSERT_OVERLAP_ERROR THEN
-- insert overlap error
UPDATE eng_change_people_intf
SET process_status = G_PS_ERROR
WHERE transaction_id = cr.transaction_id;
IF c_get_delete_grantid%ISOPEN THEN
CLOSE c_get_delete_grantid;
SELECT ENG_CPI_DATASET_ID_S.NEXTVAL
INTO G_CURR_DATASET_ID
FROM DUAL;
p_delete_lines IN NUMBER ,
p_debug_mode IN NUMBER ,
p_log_mode IN NUMBER
) IS
-- Start OF comments
-- API name : Load Interfance Lines
-- TYPE : Public (called by Concurrent Program)
-- Pre-reqs : None
-- FUNCTION : Process and Load interfance lines into FND_GRANTS.
-- Errors are populated in MTL_INTERFACE_ERRORS
--Currently, assume that the user who submits the 'Change People Import'
--is always Internal user. So, can join with PER_ALL_PEOPLE_F to figure
--out the party id.
CURSOR c_user_party_id (cp_user_id IN NUMBER) IS
SELECT employee.party_id, first_name ||' '|| last_name name
FROM per_all_people_f employee, fnd_user users
WHERE users.user_id = cp_user_id
AND employee.person_id = users.employee_id;
SELECT COUNT(*)
FROM eng_change_people_intf
WHERE data_set_id = cp_data_set_id
AND process_status = G_PS_TO_BE_PROCESSED;
SELECT MIN(transaction_id), MAX(transaction_id)
FROM eng_change_people_intf
WHERE data_set_id = cp_data_set_id
AND process_status = G_PS_TO_BE_PROCESSED;
SELECT transaction_id,start_date,end_date
FROM eng_change_people_intf
WHERE data_set_id = G_DATA_SET_ID
AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND process_status = G_PS_IN_PROCESS
AND start_date > NVL(end_date,(start_date + 1));
SELECT transaction_id, transaction_type
FROM eng_change_people_intf
WHERE data_set_id = G_DATA_SET_ID
AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND process_status = G_PS_IN_PROCESS
AND transaction_type NOT IN ('CREATE', 'UPDATE', 'DELETE', 'SYNC');
SELECT transaction_id, grantee_type
FROM eng_change_people_intf
WHERE data_set_id = G_DATA_SET_ID
AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND process_status = G_PS_IN_PROCESS
AND (grantee_type IS NULL OR grantee_type NOT IN ('USER', 'GROUP', 'COMPANY', 'GLOBAL'));
SELECT transaction_id, grantee_party_id, grantee_name, grantee_type
FROM eng_change_people_intf
WHERE data_set_id = G_DATA_SET_ID
AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND process_status = G_PS_IN_PROCESS
AND grantee_party_id IS NULL;
SELECT transaction_id, internal_role_id, display_role_name, internal_role_name
FROM eng_change_people_intf
WHERE data_set_id = G_DATA_SET_ID
AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND process_status = G_PS_IN_PROCESS
AND internal_role_id IS NULL;
SELECT transaction_id, organization_id, organization_code
FROM eng_change_people_intf
WHERE data_set_id = G_DATA_SET_ID
AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND process_status = G_PS_IN_PROCESS
AND organization_id IS NULL;
SELECT transaction_id, change_mgmt_type_code
FROM eng_change_people_intf
WHERE data_set_id = G_DATA_SET_ID
AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND process_status = G_PS_IN_PROCESS
AND (change_mgmt_type_code IS NULL OR
--commenting out the following lines as ENG_CHANGE_MGMT_TYPES is obsoleted
--change_mgmt_type_code NOT IN (SELECT CHANGE_MGMT_TYPE_CODE FROM ENG_CHANGE_MGMT_TYPES));
change_mgmt_type_code NOT IN (SELECT CHANGE_MGMT_TYPE_CODE FROM ENG_CHANGE_ORDER_TYPES_VL
WHERE TYPE_CLASSIFICATION = 'CATEGORY'));
SELECT transaction_id,organization_code,change_mgmt_type_code,change_notice
FROM eng_change_people_intf
WHERE data_set_id = G_DATA_SET_ID
AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND process_status = G_PS_IN_PROCESS
AND change_id IS NULL;
SELECT change_notice
FROM ENG_LOGIN_ACCESS_CHANGES
WHERE change_notice = cp_change_notice
AND organization_id = cp_organization_id;
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'utl_file_dir';
UPDATE eng_change_people_intf
SET
-- login_user_id = G_USER_ID,
-- login_party_id = l_login_party_id,
creation_date = l_sysdate,
start_date = NVL(start_date, l_sysdate),
transaction_type = UPPER(transaction_type),
change_mgmt_type_code = UPPER(change_mgmt_type_code),
grantee_type = UPPER(grantee_type),
process_status = G_PS_IN_PROCESS
WHERE data_set_id = G_DATA_SET_ID
AND process_status = G_PS_TO_BE_PROCESSED
AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER;
UPDATE eng_change_people_intf
SET process_status = G_PS_ERROR
WHERE transaction_id = cr.transaction_id;
UPDATE eng_change_people_intf
SET process_status = G_PS_ERROR
WHERE transaction_id = cr.transaction_id;
UPDATE eng_change_people_intf
SET process_status = G_PS_ERROR
WHERE transaction_id = cr.transaction_id;
UPDATE eng_change_people_intf ecpi
-- SET (ecpi.grantee_party_id, ecpi.grantee_name) =
-- ( SELECT person_id, person_name
SET (ecpi.grantee_party_id) =
( SELECT person_id
FROM ego_people_v
-- WHERE user_name = ecpi.grantee_user_name
WHERE user_name = upper(ecpi.grantee_name)
)
WHERE ecpi.data_set_id = G_DATA_SET_ID
AND ecpi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND ecpi.process_status = G_PS_IN_PROCESS
AND ecpi.grantee_party_id IS NULL
AND ecpi.grantee_type IS NOT NULL
AND ecpi.grantee_type = 'USER';
UPDATE eng_change_people_intf ecpi
SET ecpi.grantee_party_id =
( SELECT group_id
FROM ego_groups_v
WHERE upper(group_name) = upper(ecpi.grantee_name)
)
WHERE ecpi.data_set_id = G_DATA_SET_ID
AND ecpi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND ecpi.process_status = G_PS_IN_PROCESS
AND ecpi.grantee_party_id IS NULL
AND ecpi.grantee_type IS NOT NULL
AND ecpi.grantee_type = 'GROUP';
UPDATE eng_change_people_intf ecpi
SET ecpi.grantee_party_id =
( SELECT company_id
FROM ego_companies_v
WHERE upper(company_name) = upper(ecpi.grantee_name)
)
WHERE ecpi.data_set_id = G_DATA_SET_ID
AND ecpi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND ecpi.process_status = G_PS_IN_PROCESS
AND ecpi.grantee_party_id IS NULL
AND ecpi.grantee_type IS NOT NULL
AND ecpi.grantee_type = 'COMPANY';
UPDATE eng_change_people_intf ecpi
SET ecpi.grantee_party_id = G_ALL_USERS_PARTY_ID
WHERE ecpi.data_set_id = G_DATA_SET_ID
AND ecpi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND ecpi.process_status = G_PS_IN_PROCESS
AND ecpi.grantee_party_id IS NULL
AND ecpi.grantee_type IS NOT NULL
AND ecpi.grantee_type = 'GLOBAL';
UPDATE eng_change_people_intf
SET process_status = G_PS_ERROR
WHERE transaction_id = cr.transaction_id;
UPDATE eng_change_people_intf ecpi
SET (ecpi.internal_role_id, ecpi.internal_role_name ) =
( SELECT role.internal_role_id,
role.internal_role_name
FROM eng_change_roles_temp role
WHERE role.display_role_name = ecpi.display_role_name
)
WHERE ecpi.data_set_id = G_DATA_SET_ID
AND ecpi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND ecpi.process_status = G_PS_IN_PROCESS
AND ecpi.internal_role_id IS NULL
AND ecpi.display_role_name IS NOT NULL
AND EXISTS ( SELECT role2.internal_role_id
FROM eng_change_roles_temp role2
WHERE role2.display_role_name = ecpi.display_role_name
);
UPDATE eng_change_people_intf
SET process_status = G_PS_ERROR
WHERE transaction_id = cr.transaction_id;
UPDATE eng_change_people_intf ecpi
SET ecpi.organization_id =
( SELECT mp.organization_id
FROM mtl_parameters mp
WHERE mp.organization_code = ecpi.organization_code
)
WHERE ecpi.data_set_id = G_DATA_SET_ID
AND ecpi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND ecpi.process_status = G_PS_IN_PROCESS
AND ecpi.organization_id IS NULL
AND ecpi.organization_code IS NOT NULL
AND EXISTS ( SELECT mp2.organization_id
FROM mtl_parameters mp2
WHERE mp2.organization_code = ecpi.organization_code
);
UPDATE eng_change_people_intf
SET process_status = G_PS_ERROR
WHERE transaction_id = cr.transaction_id;
UPDATE eng_change_people_intf
SET process_status = G_PS_ERROR
WHERE transaction_id = cr.transaction_id;
UPDATE eng_change_people_intf ecpi
SET ecpi.change_id =
( SELECT change_id
FROM eng_engineering_changes eec
WHERE ecpi.change_notice = eec.change_notice
AND ecpi.organization_id = eec.organization_id
AND ecpi.change_mgmt_type_code = eec.change_mgmt_type_code
)
WHERE ecpi.data_set_id = G_DATA_SET_ID
AND ecpi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND ecpi.process_status = G_PS_IN_PROCESS
AND ecpi.change_id IS NULL
AND EXISTS ( SELECT change_id
FROM eng_engineering_changes eec
WHERE ecpi.change_notice = eec.change_notice
AND ecpi.organization_id = eec.organization_id
AND ecpi.change_mgmt_type_code = eec.change_mgmt_type_code
);
UPDATE eng_change_people_intf
SET process_status = G_PS_ERROR
WHERE transaction_id = cr.transaction_id;
IF p_delete_lines IN (DELETE_ALL, DELETE_ERROR, DELETE_SUCCESS) THEN
purge_interface_lines
(p_data_set_id => p_data_set_id
,p_closed_date => NULL
,p_delete_line_type => p_delete_lines
-- ,p_delete_error_log => NULL
,x_retcode => l_retcode
,x_errbuff => l_errbuff
);
p_delete_line_type IN NUMBER,
-- p_delete_error_log IN NUMBER,
x_retcode OUT NOCOPY VARCHAR2,
x_errbuff OUT NOCOPY VARCHAR2
) IS
-- Start OF comments
-- API name : Clean Interface Lines
-- TYPE : Public (called by Concurrent Program)
-- Pre-reqs : None
-- FUNCTION : Removes all the interface lines
--
BEGIN
-- validate the given parameters
IF (p_data_set_id IS NULL AND p_closed_date IS NULL)
OR NVL(p_delete_line_type,-1) NOT IN (DELETE_ALL, DELETE_ERROR, DELETE_SUCCESS) THEN
-- invalid parameters
x_retcode := RETCODE_ERROR;
IF p_delete_line_type = DELETE_ALL THEN
--
-- delete all lines
--
DELETE mtl_interface_errors
WHERE table_name = G_ERROR_TABLE_NAME
AND transaction_id IN
( SELECT transaction_id
FROM eng_change_people_intf
WHERE data_set_id = NVL(p_data_set_id, data_set_id)
AND creation_date <= NVL(p_closed_date, creation_date)
);
DELETE eng_change_people_intf
WHERE data_set_id = NVL(p_data_set_id, data_set_id)
AND creation_date <= NVL(p_closed_date, creation_date);
ELSIF p_delete_line_type = DELETE_ERROR THEN
--
-- delete all error lines
--
DELETE mtl_interface_errors
WHERE table_name = G_ERROR_TABLE_NAME
AND transaction_id IN
( SELECT transaction_id
FROM eng_change_people_intf
WHERE data_set_id = NVL(p_data_set_id, data_set_id)
AND creation_date <= NVL(p_closed_date, creation_date)
);
DELETE eng_change_people_intf
WHERE data_set_id = NVL(p_data_set_id, data_set_id)
AND creation_date <= NVL(p_closed_date, creation_date)
AND process_status = G_PS_ERROR;
ELSIF p_delete_line_type = DELETE_SUCCESS THEN
--
-- delete all success lines
--
DELETE eng_change_people_intf
WHERE data_set_id = NVL(p_data_set_id, data_set_id)
AND creation_date <= NVL(p_closed_date, creation_date)
AND process_status = G_PS_SUCCESS;