[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 object_id
into l_object_id
from fnd_objects
where obj_name=p_object_name;
SELECT party_id
INTO x_orig_system_id
FROM ego_user_v
where user_name = x_user_name;
SELECT 'HZ_COMPANY:'||group_membership_rel.object_id company_name
FROM hz_relationships group_membership_rel
WHERE group_membership_rel.RELATIONSHIP_CODE = 'EMPLOYEE_OF'
AND group_membership_rel.status= 'A'
AND group_membership_rel.start_date <= SYSDATE
AND NVL(group_membership_rel.end_date, SYSDATE) >= SYSDATE
AND group_membership_rel.subject_id = cp_orig_system_id;
SELECT 'HZ_GROUP:'||group_membership_rel.object_id group_name
FROM hz_relationships group_membership_rel
WHERE group_membership_rel.RELATIONSHIP_CODE = 'MEMBER_OF'
AND group_membership_rel.status= 'A'
AND group_membership_rel.start_date <= SYSDATE
AND NVL(group_membership_rel.end_date, SYSDATE) >= SYSDATE
AND group_membership_rel.subject_id = cp_orig_system_id;
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_2 VARCHAR2(32767);
l_select_sql VARCHAR2(32767);
cursor_select INTEGER;
l_select_sql :=
' SELECT OUT_MSIB.INVENTORY_ITEM_ID, OUT_MSIB.ORGANIZATION_ID, ' || G_REQUEST_ID ||
' FROM fnd_grants grants, fnd_form_functions functions, fnd_menu_entries cmf, mtl_system_items out_msib ';
l_select_sql := l_select_sql || ' WHERE ' || l_sec_predicate ;
l_select_sql_2 := ' SELECT DISTINCT INVENTORY_ITEM_ID, ORGANIZATION_ID, ' || G_REQUEST_ID ||
' FROM MTL_SYSTEM_ITEMS_B, ego_item_cat_denorm_hier cathier ' ||
' WHERE item_catalog_group_id = cathier.child_catalog_group_id ';
l_dynamic_sql_1 := ' SELECT DISTINCT instance_sets.predicate ' ||
' FROM fnd_grants grants, fnd_form_functions functions, ' ||
' fnd_menu_entries cmf, fnd_object_instance_sets instance_sets ' ||
' WHERE grants.instance_type = ''SET'' ' ||
' AND grants.start_date <= SYSDATE ' ||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
' AND cmf.function_id = functions.function_id ' ||
' AND cmf.menu_id = grants.menu_id ' ||
' AND grants.instance_set_id = instance_sets.instance_set_id ' ||
' AND grants.object_id = :object_id ' ||
' AND functions.function_name = ''EGO_ADD_ITEM_PEOPLE'' ' ||
' AND ((grants.grantee_type = ''USER'' ' ||
' AND grants.grantee_key = :grantee_key ) ' ||
' OR (grants.grantee_type = ''GROUP'' ' ||
' AND grants.grantee_key in ( :group_info ))' -- ||l_group_info||' )) '
|| ' OR (grants.grantee_type = ''COMPANY'' ' ||
' AND grants.grantee_key in ( :company_info ))' --||l_company_info||' )) '
|| ' OR (grants.grantee_type = ''GLOBAL'' ' ||
' AND grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL''))) ';
l_select_sql_2 := substrb(l_select_sql_2 || ' AND ( ' || l_set_predicates || ' ) '
, 1, 32767);
l_select_sql := l_select_sql || ' UNION ' || l_select_sql_2 ;
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;
DBMS_SQL.PARSE(cursor_select,l_select_sql,DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE( cursor_select, ':o', l_object_id ); --Bug 13637215 BINDING
DBMS_SQL.BIND_VARIABLE( cursor_select, ':u', l_user_name ); --Bug 13637215 BINDING
DBMS_SQL.BIND_VARIABLE( cursor_select, ':g', l_group_info ); --Bug 13637215 BINDING
DBMS_SQL.BIND_VARIABLE( cursor_select, ':c', l_company_info ); --Bug 13637215 BINDING
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);
l_item_id_table.DELETE;
l_org_id_table.DELETE;
l_conc_req_id_table.DELETE;
DBMS_SQL.CLOSE_CURSOR(cursor_select);
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 (cp_start_date > start_date-1 AND cp_start_date <= start_date) -- CHECHAND for Bug# 9946288
-- 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';
l_update_sql VARCHAR2(32767);
l_select_sql VARCHAR2(32767);
l_select_sql_2 VARCHAR2(32767);
cursor_update INTEGER;
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;
l_update_sql :=
' UPDATE ego_item_people_intf eipi '
|| ' SET process_status = :e' --|| G_PS_ERROR
|| ' WHERE eipi.data_set_id = :d' -- || G_DATA_SET_ID
|| ' AND eipi.transaction_id BETWEEN :f AND :t' --|| G_FROM_LINE_NUMBER || ' AND ' || G_TO_LINE_NUMBER
|| ' AND eipi.process_status = :p' --|| 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 ( ';
l_select_sql :=
' SELECT /*+ no_unnest index(grants FND_GRANTS_N5) */ ''X'' ' || --Bug 13637215 add hint
' FROM fnd_grants grants, fnd_form_functions functions, fnd_menu_entries cmf ' ||
' WHERE grants.instance_pk1_value = to_char(eipi.INVENTORY_ITEM_ID) ' ||
' AND grants.instance_pk2_value = to_char(eipi.ORGANIZATION_ID) ' ||
' AND grants.start_date <= sysdate ' ||
' AND (grants.end_date IS NULL OR grants.end_date >= sysdate) ' ||
' AND grants.instance_type = ''INSTANCE'' ' ||
' AND cmf.function_id = functions.function_id ' ||
' AND cmf.menu_id = grants.menu_id ' ||
' AND grants.object_id = :o' --|| l_object_id
|| ' AND functions.function_name = ''' || 'EGO_ADD_ITEM_PEOPLE' || '''' ||
' AND ((grants.grantee_type = ''USER'' ' ||
' AND grants.grantee_key = :u ) ' -- ''||l_user_name||''')'
|| ' OR (grants.grantee_type = ''GROUP'' '||
' AND grants.grantee_key in ( :g )) ' --|| l_group_info || ')) '
|| ' OR (grants.grantee_type = ''COMPANY'' '||
' AND grants.grantee_key in ( :c )) ' -- || l_company_info || ')) '
|| ' OR (grants.grantee_type = ''GLOBAL'' ' ||
' AND grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL''))) ';
l_select_sql_2 :=
' SELECT /*+ no_unnest */ ''X'' ' ||
' FROM MTL_SYSTEM_ITEMS_B msi, ego_item_cat_denorm_hier cathier ' ||
' WHERE msi.inventory_item_id = eipi.inventory_item_id ' ||
' AND msi.organization_id = eipi.organization_id' ||
' AND msi.item_catalog_group_id = cathier.child_catalog_group_id ';
l_dynamic_sql_1 := ' SELECT DISTINCT instance_sets.predicate ' ||
' FROM fnd_grants grants, fnd_form_functions functions, ' ||
' fnd_menu_entries cmf, fnd_object_instance_sets instance_sets ' ||
' WHERE grants.instance_type = ''SET'' ' ||
' AND grants.start_date <= SYSDATE ' ||
' AND NVL(grants.end_date,SYSDATE) >= SYSDATE ' ||
' AND cmf.function_id = functions.function_id ' ||
' AND cmf.menu_id = grants.menu_id ' ||
' AND grants.instance_set_id = instance_sets.instance_set_id ' ||
' AND grants.object_id = :object_id ' ||
' AND functions.function_name = ''EGO_ADD_ITEM_PEOPLE'' ' ||
' AND ((grants.grantee_type = ''USER'' ' ||
' AND grants.grantee_key = :grantee_key )' ||
' OR (grants.grantee_type = ''GROUP'' ' ||
' AND grants.grantee_key in ( :group_info ))' --||l_group_info||' ))'
|| ' OR (grants.grantee_type = ''COMPANY'' ' ||
' AND grants.grantee_key in ( :company_info ))' --||l_company_info||' ))'
|| ' OR (grants.grantee_type = ''GLOBAL'' ' ||
' AND grants.grantee_key in (''HZ_GLOBAL:-1000'', ''GLOBAL''))) ';
l_select_sql_2 := substrb(l_select_sql_2 || ' AND ( ' || l_set_predicates || ' ) '
, 1, 32767);
l_select_sql := l_select_sql || ' UNION ALL ' || l_select_sql_2 ;
l_update_sql := l_update_sql || l_select_sql || ' ) ';
Write_Debug('l_update_sql: '|| l_update_sql);
cursor_update := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_update, l_update_sql, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE( cursor_update, ':e', G_PS_ERROR ); --Bug 13637215 BINDING
DBMS_SQL.BIND_VARIABLE( cursor_update, ':d', G_DATA_SET_ID ); --Bug 13637215 BINDING
DBMS_SQL.BIND_VARIABLE( cursor_update, ':f', G_FROM_LINE_NUMBER ); --Bug 13637215 BINDING
DBMS_SQL.BIND_VARIABLE( cursor_update, ':t', G_TO_LINE_NUMBER ); --Bug 13637215 BINDING
DBMS_SQL.BIND_VARIABLE( cursor_update, ':p', G_PS_IN_PROCESS ); --Bug 13637215 BINDING
DBMS_SQL.BIND_VARIABLE( cursor_update, ':o', l_object_id ); --Bug 13637215 BINDING
DBMS_SQL.BIND_VARIABLE( cursor_update, ':u', l_user_name ); --Bug 13637215 BINDING
DBMS_SQL.BIND_VARIABLE( cursor_update, ':g', l_group_info ); --Bug 13637215 BINDING
DBMS_SQL.BIND_VARIABLE( cursor_update, ':c', l_company_info ); --Bug 13637215 BINDING
cursor_execute := DBMS_SQL.EXECUTE(cursor_update);
DBMS_SQL.CLOSE_CURSOR(cursor_update);
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
);
IF DBMS_SQL.IS_OPEN(cursor_update) THEN
DBMS_SQL.CLOSE_CURSOR(cursor_update);
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
);