[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 ego_item_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;
DELETE EGO_LOGIN_ITEMS_TEMP WHERE CONC_REQUEST_ID = G_REQUEST_ID;
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';
l_select_sql VARCHAR2(32767);
l_insert_sql VARCHAR2(500);
cursor_select INTEGER;
cursor_insert INTEGER;
l_select_sql :=
' SELECT OUT_MSIB.INVENTORY_ITEM_ID, OUT_MSIB.ORGANIZATION_ID, ' || G_REQUEST_ID ||
' FROM MTL_SYSTEM_ITEMS OUT_MSIB ';
l_select_sql := l_select_sql || 'WHERE ' || l_sec_predicate ;
Write_Debug('Access Items SQL => '||l_select_sql);
l_insert_sql := 'INSERT INTO EGO_LOGIN_ITEMS_TEMP (INVENTORY_ITEM_ID, ORGANIZATION_ID, CONC_REQUEST_ID) ';
l_insert_sql := l_insert_sql || ' VALUES (:l_item_id_table, :l_org_id_table, :l_conc_req_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_item_id_table,2500, indx);
DBMS_SQL.DEFINE_ARRAY(cursor_select, 2,l_org_id_table,2500, indx);
DBMS_SQL.DEFINE_ARRAY(cursor_select, 3,l_conc_req_id_table,2500, indx);
Write_Debug('Select Access Items execute...');
cursor_execute := DBMS_SQL.EXECUTE(cursor_select);
l_count := DBMS_SQL.FETCH_ROWS(cursor_select);
DBMS_SQL.COLUMN_VALUE(cursor_select, 1, l_item_id_table);
DBMS_SQL.COLUMN_VALUE(cursor_select, 2, l_org_id_table);
DBMS_SQL.COLUMN_VALUE(cursor_select, 3, l_conc_req_id_table);
DBMS_SQL.BIND_ARRAY(cursor_insert,':l_item_id_table',l_item_id_table);
DBMS_SQL.BIND_ARRAY(cursor_insert,':l_org_id_table',l_org_id_table);
DBMS_SQL.BIND_ARRAY(cursor_insert,':l_conc_req_id_table',l_conc_req_id_table);
Write_Debug('Inserting ''Access Items'' into table');
cursor_execute := DBMS_SQL.EXECUTE(cursor_insert);
l_item_id_table.DELETE;
l_org_id_table.DELETE;
l_conc_req_id_table.DELETE;
DBMS_SQL.CLOSE_CURSOR(cursor_select);
DBMS_SQL.CLOSE_CURSOR(cursor_insert);
Write_Debug('Cursors Insert Access Items and Select Access Items closed...');
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);
p_delete_line_type 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
--
l_program_name CONSTANT VARCHAR2(30) := 'PURGE_LINES';
OR NVL(p_delete_line_type,EGO_ITEM_PUB.G_INTF_DELETE_NONE) NOT IN
(EGO_ITEM_PUB.G_INTF_DELETE_ALL
,EGO_ITEM_PUB.G_INTF_DELETE_ERROR
,EGO_ITEM_PUB.G_INTF_DELETE_SUCCESS
,EGO_ITEM_PUB.G_INTF_DELETE_NONE
) THEN
-- invalid parameters
x_retcode := RETCODE_ERROR;
IF p_delete_line_type = EGO_ITEM_PUB.G_INTF_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 ego_item_people_intf
WHERE data_set_id = NVL(p_data_set_id, data_set_id)
AND creation_date <= NVL(p_closed_date, creation_date)
);
DELETE ego_item_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 = EGO_ITEM_PUB.G_INTF_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 ego_item_people_intf
WHERE data_set_id = NVL(p_data_set_id, data_set_id)
AND creation_date <= NVL(p_closed_date, creation_date)
);
DELETE ego_item_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 = EGO_ITEM_PUB.G_INTF_DELETE_SUCCESS THEN
--
-- delete all success lines
--
DELETE ego_item_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;
IF p_delete_line_type IN
(EGO_ITEM_PUB.G_INTF_DELETE_ALL
,EGO_ITEM_PUB.G_INTF_DELETE_ERROR
,EGO_ITEM_PUB.G_INTF_DELETE_SUCCESS
) THEN
COMMIT WORK;
PROCEDURE validate_update_grant
(p_transaction_type IN VARCHAR2
,p_transaction_id IN NUMBER
,p_inventory_item_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_company_party_id_char IN VARCHAR2
,p_global_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 ego_item_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_inv_item_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_company_party_id_char IN VARCHAR2
,cp_global_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_inv_item_id)
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 = 'COMPANY' AND grants.grantee_key = cp_company_party_id_char) OR
(grants.grantee_type = 'GLOBAL' AND grants.grantee_key = cp_global_party_id_char)
)
AND start_date = cp_start_date;
CURSOR c_get_valid_update
(cp_grant_guid IN RAW
,cp_inv_item_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_company_party_id_char IN VARCHAR2
,cp_global_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_inv_item_id)
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 = 'COMPANY' AND grants.grantee_key = cp_company_party_id_char) OR
(grants.grantee_type = 'GLOBAL' AND grants.grantee_key = cp_global_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_inv_item_id => p_inventory_item_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_company_party_id_char => p_company_party_id_char
,cp_global_party_id_char => p_global_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_inv_item_id => p_inventory_item_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_company_party_id_char => p_company_party_id_char
,cp_global_party_id_char => p_global_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 := 'ITEM';
SELECT CONCATENATED_SEGMENTS
INTO l_item_number
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id;
END IF; -- p_transaction_type UPDATE
END IF; -- c_get_update_grantid
CLOSE c_get_update_grantid;
Write_Debug(' EXCEPTION in validate_update_grant ');
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_inventory_item_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_company_party_id_char IN VARCHAR2
,p_global_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 ego_item_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_inv_item_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_company_party_id_char IN VARCHAR2
,cp_global_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_inv_item_id)
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 = 'COMPANY' AND grants.grantee_key = cp_company_party_id_char) OR
(grants.grantee_type = 'GLOBAL' AND grants.grantee_key = cp_global_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;
Write_Debug(' EXCEPTION in validate_insert_grant ');
END validate_insert_grant;
SELECT item_number, inventory_item_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),
'COMPANY','HZ_COMPANY:'||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,
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 ego_item_people_intf
WHERE data_set_id = G_DATA_SET_ID
AND process_status = G_PS_IN_PROCESS
ORDER BY trans_type, transaction_id
FOR UPDATE OF transaction_id;
CURSOR c_get_delete_grantid
(cp_inv_item_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_company_party_id_char IN VARCHAR2
,cp_global_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_inv_item_id)
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 = 'COMPANY' AND grants.grantee_key = cp_company_party_id_char) OR
(grants.grantee_type = 'GLOBAL' AND grants.grantee_key = cp_global_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));
SELECT COUNT(*)
FROM ego_item_people_intf
WHERE data_set_id = cp_data_set_id
AND process_status = G_PS_IN_PROCESS;
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_inv_item_id => cr.inventory_item_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_company_party_id_char => l_company_party_id_char
,cp_global_party_id_char => l_global_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 ego_item_people_intf
SET process_status = G_PS_SUCCESS
WHERE CURRENT OF c_get_ipi_records;
UPDATE ego_item_people_intf
SET process_status = G_PS_ERROR
WHERE CURRENT OF c_get_ipi_records;
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_inventory_item_id => cr.inventory_item_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_company_party_id_char => l_company_party_id_char
,p_global_party_id_char => l_global_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 ego_item_people_intf
SET process_status = G_PS_SUCCESS
WHERE CURRENT OF c_get_ipi_records;
ELSIF l_return_status = G_UPDATE_REC_NOT_FOUND THEN
-- no record found for overlap
UPDATE ego_item_people_intf
SET process_status = G_PS_ERROR
WHERE CURRENT OF c_get_ipi_records;
ELSIF l_return_status = G_UPDATE_OVERLAP_ERROR THEN
-- overlap will occur if update is done
UPDATE ego_item_people_intf
SET process_status = G_PS_ERROR
WHERE CURRENT OF c_get_ipi_records;
validate_update_grant
(p_transaction_type => cr.transaction_type
,p_transaction_id => cr.transaction_id
,p_inventory_item_id => cr.inventory_item_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_company_party_id_char => l_company_party_id_char
,p_global_party_id_char => l_global_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
-- 4669015 setting successful status to 'UPDATE'/'CREATE'
UPDATE ego_item_people_intf
SET process_status = G_PS_SUCCESS,
transaction_type = 'UPDATE'
WHERE CURRENT OF c_get_ipi_records;
ELSIF l_return_status = G_UPDATE_OVERLAP_ERROR THEN
-- overlap will occur if update is done
UPDATE ego_item_people_intf
SET process_status = G_PS_ERROR
WHERE CURRENT OF c_get_ipi_records;
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_inventory_item_id => cr.inventory_item_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_company_party_id_char => l_company_party_id_char
,p_global_party_id_char => l_global_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
-- 4669015 setting successful status to 'UPDATE'/'CREATE'
UPDATE ego_item_people_intf
SET process_status = G_PS_SUCCESS,
transaction_type = 'CREATE'
WHERE CURRENT OF c_get_ipi_records;
ELSIF l_return_status = G_INSERT_OVERLAP_ERROR THEN
-- insert overlap error
UPDATE ego_item_people_intf
SET process_status = G_PS_ERROR
WHERE CURRENT OF c_get_ipi_records;
validate_insert_grant
(p_transaction_type => cr.transaction_type
,p_transaction_id => cr.transaction_id
,p_inventory_item_id => cr.inventory_item_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_company_party_id_char => l_company_party_id_char
,p_global_party_id_char => l_global_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 ego_item_people_intf
SET process_status = G_PS_SUCCESS
WHERE CURRENT OF c_get_ipi_records;
ELSIF l_return_status = G_INSERT_OVERLAP_ERROR THEN
-- insert overlap error
UPDATE ego_item_people_intf
SET process_status = G_PS_ERROR
WHERE CURRENT OF c_get_ipi_records;
IF c_get_delete_grantid%ISOPEN THEN
CLOSE c_get_delete_grantid;
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'utl_file_dir';
SELECT EGO_IPI_DATASET_ID_S.NEXTVAL
INTO G_CURR_DATASET_ID
FROM DUAL;
p_delete_lines IN NUMBER DEFAULT EGO_ITEM_PUB.G_INTF_DELETE_NONE,
p_debug_mode IN NUMBER DEFAULT EGO_ITEM_PEOPLE_IMPORT_PKG.DEBUG_MODE_ERROR,
p_log_mode IN NUMBER DEFAULT EGO_ITEM_PEOPLE_IMPORT_PKG.LOG_INTO_TABLE_ONLY
) 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
-- ======================================================================
-- the record types used from other procedures
-- noted down here for quick reference
-- Error record type
-- ======================================================================
-- TYPE Error_Rec_Type IS RECORD
-- (organization_id NUMBER
-- ,entity_id VARCHAR2(3)
-- ,message_text VARCHAR2(2000)
-- ,entity_index NUMBER
-- ,message_type VARCHAR2(1)
-- ,row_identifier VARCHAR2(80)
-- ,bo_identifier VARCHAR2(3) := 'ECO'
-- );
SELECT person_id, person_name
FROM ego_people_v
WHERE user_id = cp_user_id;
SELECT COUNT(*)
FROM ego_item_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 ego_item_people_intf
WHERE data_set_id = cp_data_set_id
AND process_status = G_PS_IN_PROCESS;
SELECT transaction_id
FROM ego_item_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 request_id = G_REQUEST_ID
AND ( (inventory_item_id IS NULL AND item_number IS NULL)
OR
(organization_id IS NULL AND organization_code IS NULL)
OR
(internal_role_id IS NULL AND internal_role_name IS NULL AND display_role_name IS NULL)
OR
(grantee_type IS NULL)
)
FOR UPDATE OF transaction_id;
SELECT transaction_id
FROM ego_item_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 request_id = G_REQUEST_ID
AND start_date > NVL(end_date,(start_date + 1))
FOR UPDATE OF transaction_id;
SELECT transaction_id, transaction_type
FROM ego_item_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 request_id = G_REQUEST_ID
AND transaction_type NOT IN ('CREATE', 'UPDATE', 'DELETE', 'SYNC')
FOR UPDATE OF transaction_id;
SELECT transaction_id, grantee_type
FROM ego_item_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 request_id = G_REQUEST_ID
AND (grantee_type IS NULL OR grantee_type NOT IN ('USER', 'GROUP', 'COMPANY', 'GLOBAL'))
FOR UPDATE OF transaction_id;
SELECT transaction_id, grantee_party_id, grantee_name, grantee_type
FROM ego_item_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 request_id = G_REQUEST_ID
AND grantee_party_id IS NULL
FOR UPDATE OF transaction_id;
SELECT transaction_id, internal_role_id, display_role_name, internal_role_name
FROM ego_item_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 request_id = G_REQUEST_ID
AND internal_role_id IS NULL
FOR UPDATE OF transaction_id;
SELECT transaction_id, item_number, inventory_item_id,
organization_code, organization_id
FROM ego_item_people_intf
WHERE data_set_id = G_DATA_SET_ID
AND transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND request_id = G_REQUEST_ID
AND process_status IN (G_INT_ORG_VAL_ERROR, G_INT_ITEM_VAL_ERROR);
SELECT transaction_id, item_number, organization_code
FROM ego_item_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
AND request_id = G_REQUEST_ID
AND inventory_item_id IS NOT NULL
FOR UPDATE OF transaction_id;
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'utl_file_dir';
Write_Debug('Delete Lines '|| to_char (p_delete_lines));
UPDATE ego_item_people_intf
SET creation_date = NVL(creation_date,l_sysdate),
last_update_date = l_sysdate,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
request_id = G_REQUEST_ID,
program_application_id = G_PROG_APPID,
program_id = G_PROG_ID,
program_update_date = l_sysdate,
start_date = NVL(start_date, l_sysdate),
transaction_type = UPPER(transaction_type),
grantee_type = UPPER(grantee_type),
process_status = G_PS_IN_PROCESS,
transaction_id = NVL(transaction_id, EGO_IPI_TRANSACTION_ID_S.NEXTVAL)
WHERE data_set_id = G_DATA_SET_ID
AND process_status = G_PS_TO_BE_PROCESSED;
UPDATE ego_item_people_intf
SET process_status = G_PS_ERROR
WHERE CURRENT OF c_err_mand_params;
UPDATE ego_item_people_intf
SET process_status = G_PS_ERROR
WHERE CURRENT OF c_err_dates;
UPDATE ego_item_people_intf
SET process_status = G_PS_ERROR
WHERE CURRENT OF c_err_transaction_type;
UPDATE ego_item_people_intf
SET process_status = G_PS_ERROR
WHERE CURRENT OF c_err_grantee_type;
UPDATE ego_item_people_intf eipi
SET (eipi.grantee_party_id) =
( SELECT person_id
FROM ego_people_v
WHERE UPPER(user_name) = UPPER(eipi.grantee_name)
)
WHERE eipi.data_set_id = G_DATA_SET_ID
AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND eipi.process_status = G_PS_IN_PROCESS
AND eipi.grantee_party_id IS NULL
AND eipi.grantee_type IS NOT NULL
AND eipi.grantee_type = 'USER';
UPDATE ego_item_people_intf eipi
SET eipi.grantee_party_id =
( SELECT group_id
FROM ego_groups_v
WHERE group_name = eipi.grantee_name
)
WHERE eipi.data_set_id = G_DATA_SET_ID
AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND eipi.process_status = G_PS_IN_PROCESS
AND eipi.grantee_party_id IS NULL
AND eipi.grantee_type IS NOT NULL
AND eipi.grantee_type = 'GROUP';
UPDATE ego_item_people_intf eipi
SET eipi.grantee_party_id =
( SELECT company_id
FROM ego_companies_v
WHERE company_name = eipi.grantee_name
)
WHERE eipi.data_set_id = G_DATA_SET_ID
AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND eipi.process_status = G_PS_IN_PROCESS
AND eipi.grantee_party_id IS NULL
AND eipi.grantee_type IS NOT NULL
AND eipi.grantee_type = 'COMPANY';
UPDATE ego_item_people_intf eipi
SET eipi.grantee_party_id = G_ALL_USERS_PARTY_ID
WHERE eipi.data_set_id = G_DATA_SET_ID
AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND eipi.process_status = G_PS_IN_PROCESS
AND eipi.grantee_party_id IS NULL
AND eipi.grantee_type IS NOT NULL
AND eipi.grantee_type = 'GLOBAL';
UPDATE ego_item_people_intf
SET process_status = G_PS_ERROR
WHERE CURRENT OF c_err_grantee_id;
UPDATE ego_item_people_intf eipi
SET (eipi.internal_role_id, eipi.internal_role_name ) =
( SELECT roles.menu_id internal_role_id,
roles.menu_name internal_role_name
FROM (
SELECT DISTINCT e.menu_id role_id
FROM fnd_form_functions f, fnd_menu_entries e
WHERE e.function_id = f.function_id
AND f.object_id = G_FND_OBJECT_ID
) obj_roles,
fnd_menus roles,
fnd_menus_tl roles_tl
WHERE obj_roles.role_id = roles.menu_id
AND obj_roles.role_id = roles_tl.menu_id
AND roles_tl.language = G_SESSION_LANG
AND roles_tl.user_menu_name = eipi.display_role_name
)
WHERE eipi.data_set_id = G_DATA_SET_ID
AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND eipi.process_status = G_PS_IN_PROCESS
AND eipi.internal_role_id IS NULL
AND eipi.display_role_name IS NOT NULL;
UPDATE ego_item_people_intf
SET process_status = G_PS_ERROR
WHERE CURRENT OF c_err_role_id;
UPDATE ego_item_people_intf eipi
SET eipi.process_status = G_INT_ORG_VAL_ERROR
WHERE eipi.data_set_id = G_DATA_SET_ID
AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND eipi.process_status = G_PS_IN_PROCESS
AND ( (organization_id IS NOT NULL
AND
NOT EXISTS
( SELECT mp.organization_id
FROM mtl_parameters mp
WHERE mp.organization_id = eipi.organization_id
)
)
OR
(organization_id IS NULL
AND
NOT EXISTS
( SELECT mp.organization_id
FROM mtl_parameters mp
WHERE mp.organization_code = eipi.organization_code
)
)
);
UPDATE ego_item_people_intf eipi
SET organization_code =
( SELECT mp.organization_code
FROM mtl_parameters mp
WHERE mp.organization_id = eipi.organization_id
)
WHERE eipi.data_set_id = G_DATA_SET_ID
AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND eipi.process_status = G_PS_IN_PROCESS
AND eipi.organization_id IS NOT NULL;
UPDATE ego_item_people_intf eipi
SET organization_id =
( SELECT mp.organization_id
FROM mtl_parameters mp
WHERE mp.organization_code = eipi.organization_code
)
WHERE eipi.data_set_id = G_DATA_SET_ID
AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND eipi.process_status = G_PS_IN_PROCESS
AND eipi.organization_id IS NULL;
UPDATE ego_item_people_intf eipi
SET process_status = G_INT_ITEM_VAL_ERROR
WHERE eipi.data_set_id = G_DATA_SET_ID
AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND eipi.process_status = G_PS_IN_PROCESS
AND ( (inventory_item_id IS NOT NULL
AND
NOT EXISTS
(SELECT 'x' FROM mtl_system_items_b_kfv item
WHERE item.organization_id = eipi.organization_id
AND item.inventory_item_id = eipi.inventory_item_id)
)
OR
(inventory_item_id IS NULL
AND
NOT EXISTS
(SELECT 'x' FROM mtl_system_items_b_kfv item
WHERE item.organization_id = eipi.organization_id
AND item.concatenated_segments = eipi.item_number)
)
);
UPDATE ego_item_people_intf eipi
SET item_number =
(Select concatenated_segments
from mtl_system_items_b_kfv item
where item.organization_id = eipi.organization_id
and item.inventory_item_id = eipi.inventory_item_id)
WHERE eipi.data_set_id = G_DATA_SET_ID
AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND eipi.process_status = G_PS_IN_PROCESS
AND eipi.inventory_item_id IS NOT NULL;
UPDATE ego_item_people_intf eipi
SET inventory_item_id =
(Select inventory_item_id
from mtl_system_items_b_kfv item
where item.organization_id = eipi.organization_id
and item.concatenated_segments = eipi.item_number)
WHERE eipi.data_set_id = G_DATA_SET_ID
AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND eipi.process_status = G_PS_IN_PROCESS
AND eipi.inventory_item_id IS NULL;
UPDATE ego_item_people_intf eipi
SET process_status = G_PS_ERROR
WHERE eipi.data_set_id = G_DATA_SET_ID
AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND eipi.process_status = G_PS_IN_PROCESS
-- 6459864: ignoring privilege check when defaulting people from style-sku
AND eipi.created_by <> -99
AND eipi.inventory_item_id IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM EGO_LOGIN_ITEMS_TEMP
WHERE inventory_item_id = eipi.inventory_item_id
AND organization_id = eipi.organization_id
AND conc_request_id = G_REQUEST_ID
);
UPDATE ego_item_people_intf eipi
SET eipi.process_status = G_PS_ERROR
WHERE eipi.data_set_id = G_DATA_SET_ID
AND eipi.transaction_id BETWEEN G_FROM_LINE_NUMBER AND G_TO_LINE_NUMBER
AND eipi.process_status IN (G_INT_ITEM_VAL_ERROR
,G_INT_ORG_VAL_ERROR
);
delete data from the interface tables OR Error Link page is not
working.
Bug# 4540712 (RSOUNDAR)
*/
----------------------------------------------------------------
l_err_msg_sql := 'SELECT INTF.ITEM_NUMBER as ITEM_NUMBER, '||
' INTF.ORGANIZATION_CODE as ORGANIZATION_CODE, '||
' MIERR.ERROR_MESSAGE as ERROR_MESSAGE '||
' FROM EGO_ITEM_PEOPLE_INTF INTF, MTL_INTERFACE_ERRORS MIERR '||
' WHERE MIERR.TRANSACTION_ID = INTF.TRANSACTION_ID '||
' AND MIERR.REQUEST_ID = INTF.REQUEST_ID '||
' AND MIERR.request_id = :1';
,p_selectQuery => l_err_msg_sql
,p_request_id => G_REQUEST_ID
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
Write_Debug('based on p_delete_lines :'||To_char(p_delete_lines)||' purge the intf table');
IF p_delete_lines IN
(EGO_ITEM_PUB.G_INTF_DELETE_ALL
,EGO_ITEM_PUB.G_INTF_DELETE_ERROR
,EGO_ITEM_PUB.G_INTF_DELETE_SUCCESS
) THEN
purge_lines
(p_data_set_id => p_data_set_id
,p_closed_date => NULL
,p_delete_line_type => p_delete_lines
,x_retcode => x_retcode
,x_errbuff => x_errbuff
);
p_delete_line_type IN NUMBER
) IS
-- Start OF comments
-- API name : Clean Interface Lines
-- TYPE : Public (called by Concurrent Program)
-- Pre-reqs : None
-- FUNCTION : Removes all the interface lines
--
l_closed_date DATE;
OR NVL(p_delete_line_type,-1) NOT IN
(EGO_ITEM_PUB.G_INTF_DELETE_ALL
,EGO_ITEM_PUB.G_INTF_DELETE_ERROR
,EGO_ITEM_PUB.G_INTF_DELETE_SUCCESS
,EGO_ITEM_PUB.G_INTF_DELETE_NONE
) THEN
-- invalid parameters
x_retcode := RETCODE_ERROR;
,p_delete_line_type => p_delete_line_type
,x_retcode => x_retcode
,x_errbuff => x_errbuff
);