[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Bug 9653987. Update x_return_status and x_return_msg to send them back to the calling function. */
PROCEDURE delete_processed_functions(p_set_process_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_return_msg OUT NOCOPY VARCHAR2) IS
l_proc_name VARCHAR2(30) := 'delete_processed_functions';
DELETE FROM ego_functions_interface WHERE (p_set_process_id IS NULL OR set_process_id=p_set_process_id)
AND process_status=G_SUCCESS_RECORD;
DELETE FROM ego_func_params_interface WHERE (p_set_process_id IS NULL OR set_process_id=p_set_process_id)
AND process_status=G_SUCCESS_RECORD;
END delete_processed_functions;
AND func_header_rec.transaction_type <> G_UPDATE_TRANSACTION
AND func_header_rec.transaction_type <> G_DELETE_TRANSACTION
AND func_header_rec.transaction_type <> G_SYNC_TRANSACTION)) THEN
write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_header_rec.transaction_id ||
': (FID, FNAME) = (' || func_header_rec.function_id ||
', '|| func_header_rec.internal_name || '): Invalid Transaction Type.');
/* Convert SYNC to CREATE/UPDATE. Validate function_id and internal_name for SYNC, UPDATE and DELETE transaction types. */
IF ( func_header_rec.transaction_type = G_UPDATE_TRANSACTION
OR func_header_rec.transaction_type = G_SYNC_TRANSACTION
OR func_header_rec.transaction_type = G_DELETE_TRANSACTION ) THEN
IF ( func_header_rec.function_id IS NOT NULL ) THEN
BEGIN
invalid_function_id := 0;
SELECT internal_name
INTO func_header_rec.internal_name
FROM ego_functions_b
WHERE ( function_id = func_header_rec.function_id );
func_header_rec.transaction_type:=G_UPDATE_TRANSACTION;
SELECT function_id
INTO func_header_rec.function_id
FROM ego_functions_b
WHERE ( internal_name = func_header_rec.internal_name );
func_header_rec.transaction_type:=G_UPDATE_TRANSACTION;
SELECT 1
INTO valid_function_type
FROM fnd_lookup_values
WHERE ( lookup_type = 'EGO_EF_FUNCTION_TYPE'
AND language = Userenv('LANG')
AND lookup_code = func_header_rec.function_type );
SELECT 1
INTO duplicate_internal_name
FROM ego_functions_b
WHERE ( internal_name = func_header_rec.internal_name );
/* Validations for UPDATE transaction type. */
ELSIF ( func_header_rec.transaction_type = G_UPDATE_TRANSACTION
AND func_header_rec.process_status <> G_ERROR_RECORD ) THEN
/* Fetch the existing data from base tables and merge it with interface table row func_header_rec. */
SELECT *
INTO temporary_record
FROM ego_functions_b
WHERE ( function_id = func_header_rec.function_id );
SELECT *
INTO temporary_record_tl
FROM ego_functions_tl
WHERE ( function_id = func_header_rec.function_id )
AND language = Userenv('LANG');
/* Validations for DELETE transaction type. */
ELSIF ( func_header_rec.transaction_type = G_DELETE_TRANSACTION
AND func_header_rec.process_status <> G_ERROR_RECORD ) THEN
BEGIN
/* 1. Check if the function is used for Item Number or Description generation of the ICC. */
SELECT 1
INTO function_is_used
FROM dual
WHERE EXISTS (SELECT *
FROM ego_actions_b
WHERE ( function_id = func_header_rec.function_id ));
SELECT 1 INTO function_is_used
FROM dual
WHERE EXISTS (SELECT * FROM ego_action_displays_b WHERE (prompt_function_id=func_header_rec.function_id
AND visibility_func_id=func_header_rec.function_id));
/* Insert into base table, if a row does not have any errors. */
IF ( func_header_rec.process_status <> G_ERROR_RECORD ) THEN
IF (func_header_rec.transaction_type=G_CREATE_TRANSACTION) then
SELECT ego_functions_s.nextval
INTO func_header_rec.function_id
FROM dual;
INSERT INTO ego_functions_b
(function_id,
internal_name,
function_type,
function_info_1,
function_info_2,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (func_header_rec.function_id,
func_header_rec.internal_name,
func_header_rec.function_type,
func_header_rec.function_info_1,
func_header_rec.function_info_2,
G_USER_ID,
SYSDATE,
G_USER_ID,
SYSDATE,
G_LOGIN_ID);
INSERT INTO ego_functions_tl
(function_id,
display_name,
description,
language,
source_lang,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
SELECT func_header_rec.function_id,
func_header_rec.display_name,
func_header_rec.description,
language_code,
Userenv('LANG'),
G_USER_ID,
SYSDATE,
G_USER_ID,
SYSDATE,
G_LOGIN_ID
FROM fnd_languages l
WHERE installed_flag IN ( 'I', 'B' );
ELSIF ( func_header_rec.transaction_type = G_UPDATE_TRANSACTION ) THEN
UPDATE ego_functions_b
SET function_type = func_header_rec.function_type,
function_info_1 = func_header_rec.function_info_1,
function_info_2 = func_header_rec.function_info_2,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE ( function_id = func_header_rec.function_id );
UPDATE ego_functions_tl
SET display_name = func_header_rec.display_name,
description = func_header_rec.description,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE ( function_id = func_header_rec.function_id )
AND Userenv('LANG') IN ( language, source_lang );
ELSIF (func_header_rec.transaction_type = G_DELETE_TRANSACTION) THEN
DELETE ego_functions_b WHERE (function_id=func_header_rec.function_id);
DELETE ego_functions_tl WHERE (function_id=func_header_rec.function_id);
/* Bug 9647937. Delete rows from ego_func_params_tl table, before deleting rows from
ego_func_params_b table. */
DELETE ego_func_params_tl WHERE func_param_id IN (SELECT func_param_id FROM ego_func_params_b
WHERE (function_id=func_header_rec.function_id));
DELETE ego_func_params_b WHERE (function_id=func_header_rec.function_id);
PROCEDURE update_intfc_functions(ego_func_tbl_values IN OUT NOCOPY ego_metadata_pub.ego_function_tbl_type) IS
l_proc_name VARCHAR2(30) := 'update_intfc_functions';
ego_func_tbl_values(i).last_update_date := SYSDATE;
ego_func_tbl_values(i).program_update_date := SYSDATE;
/* Update the interface table back. */
FORALL i IN 1 .. ego_func_tbl_values.COUNT
UPDATE ego_functions_interface
SET ROW = ego_func_tbl_values(i)
WHERE ( transaction_id = transaction_id_table(i) );
END update_intfc_functions;
UPDATE ego_functions_interface
SET transaction_id = mtl_system_items_interface_s.nextval,
transaction_type = Upper(transaction_type),
last_update_date = sysdate,
last_updated_by = G_USER_ID,
request_id = G_REQUEST_ID,
program_application_id = G_PROGRAM_APPLICATION_ID,
program_id = G_PROGRAM_ID,
program_update_date = SYSDATE
WHERE ( (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD AND transaction_id IS NULL);
SELECT Count(*) INTO record_count
FROM ego_functions_interface
WHERE ( (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD );
update_intfc_functions(ego_func_tbl_values);
/* Error out the rows with Transaction Type null and other than CREATE, UPDATE, DELETE, SYNC */
INSERT INTO mtl_interface_errors
(unique_id,
transaction_id,
table_name,
message_name,
error_message,
bo_identifier,
entity_identifier,
message_type,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT mtl_system_items_interface_s.nextval,
transaction_id,
G_FUNCTIONS_TAB,
message_name,
message_text,
G_BO_IDENTIFIER_ICC,
G_ENTITY_ICC_FN,
fnd_api.g_ret_sts_error,
SYSDATE,
G_USER_ID,
G_USER_ID,
SYSDATE,
G_LOGIN_ID,
G_REQUEST_ID,
G_PROGRAM_APPLICATION_ID,
G_PROGRAM_ID,
SYSDATE
FROM ego_functions_interface
WHERE ( transaction_type NOT IN ( G_CREATE_TRANSACTION, G_UPDATE_TRANSACTION, G_SYNC_TRANSACTION, G_DELETE_TRANSACTION )
OR transaction_type IS NULL )
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD;
UPDATE ego_functions_interface
SET process_status = G_ERROR_RECORD, last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
WHERE ( transaction_type NOT IN ( G_CREATE_TRANSACTION, G_UPDATE_TRANSACTION, G_SYNC_TRANSACTION, G_DELETE_TRANSACTION )
OR transaction_type IS NULL )
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD;
/* For UPDATE and DELETE transactions, validate function_id if it is not null. */
message_name := 'EGO_EF_FUNC_ID_ERR';
INSERT INTO mtl_interface_errors
(unique_id,
transaction_id,
table_name,
message_name,
error_message,
bo_identifier,
entity_identifier,
message_type,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT mtl_system_items_interface_s.nextval,
transaction_id,
G_FUNCTIONS_TAB,
message_name,
message_text,
G_BO_IDENTIFIER_ICC,
G_ENTITY_ICC_FN,
fnd_api.g_ret_sts_error,
SYSDATE,
G_USER_ID,
G_USER_ID,
SYSDATE,
G_LOGIN_ID,
G_REQUEST_ID,
G_PROGRAM_APPLICATION_ID,
G_PROGRAM_ID,
SYSDATE
FROM ego_functions_interface i
WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
AND NOT EXISTS (SELECT function_id
FROM ego_functions_b b
WHERE ( b.function_id = i.function_id ))
AND function_id IS NOT NULL
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD;
UPDATE ego_functions_interface i
SET process_status = G_ERROR_RECORD,last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
AND NOT EXISTS (SELECT *
FROM ego_functions_b b
WHERE ( b.function_id = i.function_id ))
AND function_id IS NOT NULL
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD;
/* For UPDATE and DELETE transactions, validate internal_name if it is not null and
function_id is not provided. */
message_name := 'EGO_EF_FUNC_INT_NAME_INVL';
INSERT INTO mtl_interface_errors
(unique_id,
transaction_id,
table_name,
message_name,
error_message,
bo_identifier,
entity_identifier,
message_type,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT mtl_system_items_interface_s.nextval,
transaction_id,
G_FUNCTIONS_TAB,
message_name,
message_text,
G_BO_IDENTIFIER_ICC,
G_ENTITY_ICC_FN,
fnd_api.g_ret_sts_error,
SYSDATE,
G_USER_ID,
G_USER_ID,
SYSDATE,
G_LOGIN_ID,
G_REQUEST_ID,
G_PROGRAM_APPLICATION_ID,
G_PROGRAM_ID,
SYSDATE
FROM ego_functions_interface i
WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
AND NOT EXISTS (SELECT internal_name
FROM ego_functions_b b
WHERE ( b.internal_name = i.internal_name )
UNION
SELECT internal_name
FROM ego_functions_interface ii
WHERE ( ii.internal_name = i.internal_name )
AND transaction_type = G_CREATE_TRANSACTION
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD)
AND function_id IS NULL
AND internal_name IS NOT NULL
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD;
UPDATE ego_functions_interface i
SET process_status = G_ERROR_RECORD, last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
AND NOT EXISTS (SELECT internal_name
FROM ego_functions_b b
WHERE ( b.internal_name = i.internal_name )
UNION
SELECT internal_name
FROM ego_functions_interface ii
WHERE ( ii.internal_name = i.internal_name )
AND transaction_type = G_CREATE_TRANSACTION
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD)
AND function_id IS NULL
AND internal_name IS NOT NULL
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD;
/* For UPDATE and DELETE transactions, error out rows that do not have both function_id and internal_name. */
message_name := 'EGO_EF_FUNC_ID_INT_NAME_ERR';
INSERT INTO mtl_interface_errors
(unique_id,
transaction_id,
table_name,
message_name,
error_message,
bo_identifier,
entity_identifier,
message_type,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT mtl_system_items_interface_s.nextval,
transaction_id,
G_FUNCTIONS_TAB,
message_name,
message_text,
G_BO_IDENTIFIER_ICC,
G_ENTITY_ICC_FN,
fnd_api.g_ret_sts_error,
SYSDATE,
G_USER_ID,
G_USER_ID,
SYSDATE,
G_LOGIN_ID,
G_REQUEST_ID,
G_PROGRAM_APPLICATION_ID,
G_PROGRAM_ID,
SYSDATE
FROM ego_functions_interface
WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
AND function_id IS NULL
AND internal_name IS NULL
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD;
UPDATE ego_functions_interface i
SET process_status = G_ERROR_RECORD, last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
AND function_id IS NULL
AND internal_name IS NULL
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD;
AND func_param_rec.transaction_type <> G_UPDATE_TRANSACTION
AND func_param_rec.transaction_type <> G_DELETE_TRANSACTION
AND func_param_rec.transaction_type <> G_SYNC_TRANSACTION)) THEN
write_debug(G_PCK_NAME||'.'||l_proc_name||'->'|| ' err_msg: TID = ' || func_param_rec.transaction_id ||
': (FID, FNAME, FPID, FPNAME) = (' || func_param_rec.function_id ||
', '|| func_param_rec.function_internal_name || ', '|| func_param_rec.func_param_id ||
', '||func_param_rec.internal_name||'): Invalid Transaction Type.');
SELECT internal_name
INTO func_param_rec.function_internal_name
FROM ego_functions_b
WHERE ( function_id = func_param_rec.function_id );
SELECT function_id
INTO func_param_rec.function_id
FROM ego_functions_b
WHERE ( internal_name = func_param_rec.function_internal_name );
/* Convert SYNC to CREATE/UPDATE. Validate func_parm_id and internal_name for SYNC, UPDATE and DELETE transaction types.
We need to consider Function ID also to validate. */
IF ( (func_param_rec.transaction_type = G_UPDATE_TRANSACTION
OR func_param_rec.transaction_type = G_SYNC_TRANSACTION
OR func_param_rec.transaction_type = G_DELETE_TRANSACTION)
AND func_param_rec.process_status <> G_ERROR_RECORD ) THEN
IF ( func_param_rec.func_param_id IS NOT NULL ) THEN
BEGIN
invalid_func_param_id := 0;
SELECT internal_name
INTO func_param_rec.internal_name
FROM ego_func_params_b
WHERE ( function_id = func_param_rec.function_id
AND func_param_id = func_param_rec.func_param_id );
func_param_rec.transaction_type:=G_UPDATE_TRANSACTION;
SELECT func_param_id
INTO func_param_rec.func_param_id
FROM ego_func_params_b
WHERE ( function_id = func_param_rec.function_id
AND internal_name = func_param_rec.internal_name );
func_param_rec.transaction_type:=G_UPDATE_TRANSACTION;
SELECT function_type
INTO x_function_type
FROM ego_functions_b
WHERE ( function_id = func_param_rec.function_id );
SELECT 1
INTO valid_data_type
FROM fnd_lookup_values
WHERE ( lookup_type = x_lookup_code
AND language = Userenv('LANG')
AND lookup_code = func_param_rec.data_type );
SELECT 1
INTO valid_param_type
FROM fnd_lookup_values
WHERE ( lookup_type = 'EGO_EF_FUNC_PARAM_TYPE'
AND language = Userenv('LANG')
AND lookup_code = func_param_rec.param_type );
SELECT 1
INTO duplicate_internal_name
FROM ego_func_params_b
WHERE ( function_id = func_param_rec.function_id
AND internal_name = func_param_rec.internal_name );
SELECT 1
INTO duplicate_sequence
FROM ego_func_params_b
WHERE ( function_id = func_param_rec.function_id
AND SEQUENCE = func_param_rec.SEQUENCE );
ELSIF ( func_param_rec.transaction_type = G_UPDATE_TRANSACTION
AND func_param_rec.process_status <> G_ERROR_RECORD ) THEN
SELECT *
INTO temporary_record
FROM ego_func_params_b
WHERE ( func_param_id = func_param_rec.func_param_id );
SELECT *
INTO temporary_record_tl
FROM ego_func_params_tl
WHERE ( func_param_id = func_param_rec.func_param_id )
AND language = Userenv('LANG');
SELECT 1
INTO duplicate_sequence
FROM ego_func_params_b
WHERE ( function_id = func_param_rec.function_id
AND SEQUENCE = func_param_rec.SEQUENCE );
/* No futher validations required for DELETE transaction. */
END IF;
SELECT ego_func_params_s.nextval
INTO func_param_rec.func_param_id
FROM dual;
INSERT INTO ego_func_params_b
(function_id,
func_param_id,
SEQUENCE,
internal_name,
data_type,
param_type,
created_by,
creation_date,
last_updated_by,
last_update_login,
last_update_date)
VALUES (func_param_rec.function_id,
func_param_rec.func_param_id,
func_param_rec.SEQUENCE,
func_param_rec.internal_name,
func_param_rec.data_type,
func_param_rec.param_type,
G_USER_ID,
SYSDATE,
G_USER_ID,
G_LOGIN_ID,
SYSDATE);
INSERT INTO ego_func_params_tl
(func_param_id,
display_name,
language,
source_lang,
created_by,
creation_date,
last_updated_by,
last_update_login,
last_update_date)
SELECT func_param_rec.func_param_id,
func_param_rec.display_name,
language_code,
Userenv('LANG'),
G_USER_ID,
SYSDATE,
G_USER_ID,
G_LOGIN_ID,
SYSDATE
FROM fnd_languages l
WHERE installed_flag IN ( 'I', 'B' );
ELSIF ( func_param_rec.transaction_type = G_UPDATE_TRANSACTION ) THEN
UPDATE ego_func_params_b
SET SEQUENCE = func_param_rec.SEQUENCE,
data_type = func_param_rec.data_type,
param_type = func_param_rec.param_type,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
last_update_date = SYSDATE
WHERE ( func_param_id = func_param_rec.func_param_id );
UPDATE ego_func_params_tl
SET display_name = func_param_rec.display_name,
last_updated_by = G_USER_ID,
last_update_login = G_LOGIN_ID,
last_update_date = SYSDATE
WHERE ( func_param_id = func_param_rec.func_param_id )
AND Userenv('LANG') IN ( language, source_lang );
ELSIF (func_param_rec.transaction_type = G_DELETE_TRANSACTION ) THEN
/* No validations for DELETE transaction. */
DELETE ego_func_params_b
WHERE ( func_param_id = func_param_rec.func_param_id );
DELETE ego_func_params_tl
WHERE ( func_param_id = func_param_rec.func_param_id );
DELETE ego_mappings_b
WHERE ( func_param_id = func_param_rec.func_param_id );
PROCEDURE update_intfc_func_params(ego_func_param_tbl_values IN OUT NOCOPY ego_metadata_pub.ego_func_param_tbl_type)
IS
l_proc_name VARCHAR2(30) := 'update_intfc_func_params';
ego_func_param_tbl_values(i).last_update_date := SYSDATE;
ego_func_param_tbl_values(i).program_update_date := SYSDATE;
/* Update the interface table back */
FORALL i IN 1 .. ego_func_param_tbl_values.COUNT
UPDATE ego_func_params_interface
SET ROW = ego_func_param_tbl_values(i)
WHERE ( transaction_id = transaction_id_table(i) );
END update_intfc_func_params;
UPDATE ego_func_params_interface
SET transaction_id = mtl_system_items_interface_s.nextval,
transaction_type = Upper(transaction_type),
last_update_date = SYSDATE,
last_updated_by = G_USER_ID,
request_id = G_REQUEST_ID,
program_application_id = G_PROGRAM_APPLICATION_ID,
program_id = G_PROGRAM_ID,
program_update_date = SYSDATE
WHERE ( (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD AND transaction_id IS NULL);
SELECT Count(*) INTO record_count
FROM ego_func_params_interface
WHERE ( (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD );
update_intfc_func_params(ego_func_param_tbl_values);
/* Error out the rows with Transaction Type null and other than CREATE, UPDATE, DELETE, SYNC */
message_name := 'EGO_TRANS_TYPE';
INSERT INTO mtl_interface_errors
(unique_id,
transaction_id,
table_name,
message_name,
error_message,
bo_identifier,
entity_identifier,
message_type,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT mtl_system_items_interface_s.nextval,
transaction_id,
G_FUNC_PARAMS_TAB,
message_name,
message_text,
g_bo_identifier_icc,
G_ENTITY_ICC_FN_PARAM,
fnd_api.g_ret_sts_error,
SYSDATE,
G_USER_ID,
G_USER_ID,
SYSDATE,
G_LOGIN_ID,
G_REQUEST_ID,
G_PROGRAM_APPLICATION_ID,
G_PROGRAM_ID,
SYSDATE
FROM ego_func_params_interface
WHERE ( transaction_type NOT IN ( G_CREATE_TRANSACTION, G_UPDATE_TRANSACTION, G_SYNC_TRANSACTION, G_DELETE_TRANSACTION )
OR transaction_type IS NULL )
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD;
UPDATE ego_func_params_interface
SET process_status = G_ERROR_RECORD, last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
WHERE ( transaction_type NOT IN ( G_CREATE_TRANSACTION, G_UPDATE_TRANSACTION, G_SYNC_TRANSACTION, G_DELETE_TRANSACTION )
OR transaction_type IS NULL )
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD;
INSERT INTO mtl_interface_errors
(unique_id,
transaction_id,
table_name,
message_name,
error_message,
bo_identifier,
entity_identifier,
message_type,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT mtl_system_items_interface_s.nextval,
transaction_id,
G_FUNC_PARAMS_TAB,
message_name,
message_text,
g_bo_identifier_icc,
G_ENTITY_ICC_FN_PARAM,
fnd_api.g_ret_sts_error,
SYSDATE,
G_USER_ID,
G_USER_ID,
SYSDATE,
G_LOGIN_ID,
G_REQUEST_ID,
G_PROGRAM_APPLICATION_ID,
G_PROGRAM_ID,
SYSDATE
FROM ego_func_params_interface i
WHERE NOT EXISTS (SELECT *
FROM ego_functions_b b
WHERE ( b.function_id = i.function_id ))
AND function_id IS NOT NULL
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD;
UPDATE ego_func_params_interface i
SET process_status = G_ERROR_RECORD,last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
WHERE NOT EXISTS (SELECT *
FROM ego_functions_b b
WHERE ( b.function_id = i.function_id ))
AND function_id IS NOT NULL
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD;
INSERT INTO mtl_interface_errors
(unique_id,
transaction_id,
table_name,
message_name,
error_message,
bo_identifier,
entity_identifier,
message_type,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT mtl_system_items_interface_s.nextval,
transaction_id,
G_FUNC_PARAMS_TAB,
message_name,
message_text,
g_bo_identifier_icc,
G_ENTITY_ICC_FN_PARAM,
fnd_api.g_ret_sts_error,
SYSDATE,
G_USER_ID,
G_USER_ID,
SYSDATE,
G_LOGIN_ID,
G_REQUEST_ID,
G_PROGRAM_APPLICATION_ID,
G_PROGRAM_ID,
SYSDATE
FROM ego_func_params_interface i
WHERE NOT EXISTS (SELECT internal_name
FROM ego_functions_b b
WHERE ( b.internal_name = i.function_internal_name )
)
AND function_id IS NULL
AND function_internal_name IS NOT NULL
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD;
UPDATE ego_func_params_interface i
SET process_status = G_ERROR_RECORD, last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
WHERE NOT EXISTS (SELECT internal_name
FROM ego_functions_b b
WHERE ( b.internal_name = i.function_internal_name )
)
AND function_id IS NULL
AND function_internal_name IS NOT NULL
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD;
INSERT INTO mtl_interface_errors
(unique_id,
transaction_id,
table_name,
message_name,
error_message,
bo_identifier,
entity_identifier,
message_type,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT mtl_system_items_interface_s.nextval,
transaction_id,
G_FUNC_PARAMS_TAB,
message_name,
message_text,
g_bo_identifier_icc,
G_ENTITY_ICC_FN_PARAM,
fnd_api.g_ret_sts_error,
SYSDATE,
G_USER_ID,
G_USER_ID,
SYSDATE,
G_LOGIN_ID,
G_REQUEST_ID,
G_PROGRAM_APPLICATION_ID,
G_PROGRAM_ID,
SYSDATE
FROM ego_func_params_interface
WHERE function_id IS NULL
AND function_internal_name IS NULL
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD;
UPDATE ego_func_params_interface i
SET process_status = G_ERROR_RECORD, last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
WHERE function_id IS NULL
AND function_internal_name IS NULL
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD;
/* For UPDATE and DELETE transactions, validate Parameter ID if it is not null. */
message_name := 'EGO_EF_FP_ID_ERR';
INSERT INTO mtl_interface_errors
(unique_id,
transaction_id,
table_name,
message_name,
error_message,
bo_identifier,
entity_identifier,
message_type,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT mtl_system_items_interface_s.nextval,
transaction_id,
G_FUNC_PARAMS_TAB,
message_name,
message_text,
g_bo_identifier_icc,
G_ENTITY_ICC_FN_PARAM,
fnd_api.g_ret_sts_error,
SYSDATE,
G_USER_ID,
G_USER_ID,
SYSDATE,
G_LOGIN_ID,
G_REQUEST_ID,
G_PROGRAM_APPLICATION_ID,
G_PROGRAM_ID,
SYSDATE
FROM ego_func_params_interface i
WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
AND NOT EXISTS (SELECT *
FROM ego_func_params_b b
WHERE ( b.func_param_id = i.func_param_id )
AND ((b.function_id = i.function_id AND i.function_id IS NOT NULL) OR
(b.function_id IN (SELECT function_id FROM ego_functions_b
WHERE (internal_name=i.function_internal_name))
AND i.function_id IS NULL AND i.function_internal_name IS NOT NULL)
) )
AND func_param_id IS NOT NULL
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD;
UPDATE ego_func_params_interface i
SET process_status = G_ERROR_RECORD,last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
AND NOT EXISTS (SELECT *
FROM ego_func_params_b b
WHERE ( b.func_param_id = i.func_param_id )
AND ((b.function_id = i.function_id AND i.function_id IS NOT NULL) OR
(b.function_id IN (SELECT function_id FROM ego_functions_b
WHERE (internal_name=i.function_internal_name))
AND i.function_id IS NULL AND i.function_internal_name IS NOT NULL)
) )
AND func_param_id IS NOT NULL
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD;
/* For UPDATE and DELETE transactions, validate Parameter Name if it is not null
and Parameter Id is null. */
message_name := 'EGO_EF_FP_INT_NAME_INVL';
INSERT INTO mtl_interface_errors
(unique_id,
transaction_id,
table_name,
message_name,
error_message,
bo_identifier,
entity_identifier,
message_type,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT mtl_system_items_interface_s.nextval,
transaction_id,
G_FUNC_PARAMS_TAB,
message_name,
message_text,
g_bo_identifier_icc,
G_ENTITY_ICC_FN_PARAM,
fnd_api.g_ret_sts_error,
SYSDATE,
G_USER_ID,
G_USER_ID,
SYSDATE,
G_LOGIN_ID,
G_REQUEST_ID,
G_PROGRAM_APPLICATION_ID,
G_PROGRAM_ID,
SYSDATE
FROM ego_func_params_interface i
WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
AND NOT EXISTS (SELECT internal_name
FROM ego_func_params_b b
WHERE ( b.internal_name = i.internal_name )
AND ((b.function_id = i.function_id AND i.function_id IS NOT NULL) OR
(b.function_id IN (SELECT function_id FROM ego_functions_b
WHERE (internal_name=i.function_internal_name))
AND i.function_id IS NULL AND i.function_internal_name IS NOT NULL)
)
UNION
SELECT internal_name
FROM ego_func_params_interface ii
WHERE (ii.internal_name = i.internal_name)
AND ((ii.function_id = i.function_id AND i.function_id IS NOT NULL) OR
(ii.function_internal_name = i.function_internal_name
AND i.function_id IS NULL AND i.function_internal_name IS NOT NULL)
)
AND transaction_type = G_CREATE_TRANSACTION
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD
)
AND func_param_id IS NULL
AND internal_name IS NOT NULL
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD;
UPDATE ego_func_params_interface i
SET process_status = G_ERROR_RECORD,last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
AND NOT EXISTS (SELECT internal_name
FROM ego_func_params_b b
WHERE ( b.internal_name = i.internal_name )
AND ((b.function_id = i.function_id AND i.function_id IS NOT NULL) OR
(b.function_id IN (SELECT function_id FROM ego_functions_b
WHERE (internal_name=i.function_internal_name))
AND i.function_id IS NULL AND i.function_internal_name IS NOT NULL)
)
UNION
SELECT internal_name
FROM ego_func_params_interface ii
WHERE (ii.internal_name = i.internal_name)
AND ((ii.function_id = i.function_id AND i.function_id IS NOT NULL) OR
(ii.function_internal_name = i.function_internal_name
AND i.function_id IS NULL AND i.function_internal_name IS NOT NULL)
)
AND transaction_type = G_CREATE_TRANSACTION
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD
)
AND func_param_id IS NULL
AND internal_name IS NOT NULL
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD;
/* For UPDATE and DELETE transactions, error out rows that do not have both func_param_id and internal_name. */
message_name := 'EGO_EF_FP_ID_INT_NAME_ERR';
INSERT INTO mtl_interface_errors
(unique_id,
transaction_id,
table_name,
message_name,
error_message,
bo_identifier,
entity_identifier,
message_type,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT mtl_system_items_interface_s.nextval,
transaction_id,
G_FUNC_PARAMS_TAB,
message_name,
message_text,
g_bo_identifier_icc,
G_ENTITY_ICC_FN_PARAM,
fnd_api.g_ret_sts_error,
SYSDATE,
G_USER_ID,
G_USER_ID,
SYSDATE,
G_LOGIN_ID,
G_REQUEST_ID,
G_PROGRAM_APPLICATION_ID,
G_PROGRAM_ID,
SYSDATE
FROM ego_func_params_interface
WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
AND func_param_id IS NULL
AND internal_name IS NULL
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD;
UPDATE ego_func_params_interface
SET process_status = G_ERROR_RECORD,last_update_date=sysdate, last_updated_by=G_USER_ID,program_update_date=SYSDATE
WHERE transaction_type IN ( G_UPDATE_TRANSACTION, G_DELETE_TRANSACTION )
AND func_param_id IS NULL
AND internal_name IS NULL
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id)
AND process_status = G_PROCESS_RECORD;