[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT application_id
INTO g_ego_application_id
FROM fnd_application
WHERE application_short_name = 'EGO';
UPDATE ego_attr_groups_interface
SET transaction_id = mtl_system_items_interface_s.nextval,
transaction_type = Upper(transaction_type),
application_id = g_ego_application_id,
created_by = G_USER_ID,
creation_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID,
request_id = G_REQUEST_ID,
program_application_id = G_PROG_APPL_ID,
program_id = G_PROGRAM_ID,
program_update_date = SYSDATE
WHERE transaction_id IS NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
SELECT COUNT(1) INTO G_AG_COUNT
FROM ego_attr_groups_interface
WHERE process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface
SET transaction_id = mtl_system_items_interface_s.nextval,
transaction_type = Upper(transaction_type),
application_id = g_ego_application_id,
created_by = G_USER_ID,
creation_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID,
request_id = G_REQUEST_ID,
program_application_id = G_PROG_APPL_ID,
program_id = G_PROGRAM_ID,
program_update_date = SYSDATE
WHERE transaction_id IS NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
SELECT COUNT(1) INTO G_DL_COUNT
FROM ego_attr_groups_dl_interface
WHERE process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_group_cols_intf
SET transaction_id = mtl_system_items_interface_s.nextval,
transaction_type = Upper(transaction_type),
application_id = g_ego_application_id,
created_by = G_USER_ID,
creation_date = SYSDATE,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID,
request_id = G_REQUEST_ID,
program_application_id = G_PROG_APPL_ID,
program_id = G_PROGRAM_ID,
program_update_date = SYSDATE
WHERE transaction_id IS NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
SELECT COUNT(1) INTO G_ATTR_COUNT
FROM ego_attr_group_cols_intf
WHERE process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if TRANSACTION_TYPE passed is incorrect*/
G_MESSAGE_NAME := 'EGO_TRANS_TYPE_INVALID';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_AG_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_AG,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_groups_interface
WHERE (transaction_type IS NULL
OR transaction_type NOT IN (G_OPR_CREATE,G_OPR_UPDATE,G_OPR_DELETE,G_OPR_SYNC))
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_interface
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE (transaction_type IS NULL
OR transaction_type NOT IN (G_OPR_CREATE,G_OPR_UPDATE,G_OPR_DELETE,G_OPR_SYNC))
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_DL_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_DL,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_groups_dl_interface
WHERE (transaction_type IS NULL
OR transaction_type NOT IN (G_OPR_CREATE,G_OPR_UPDATE,G_OPR_DELETE,G_OPR_SYNC))
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE (transaction_type IS NULL
OR transaction_type NOT IN (G_OPR_CREATE,G_OPR_UPDATE,G_OPR_DELETE,G_OPR_SYNC))
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_ATTR_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_ATTR,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_group_cols_intf
WHERE (transaction_type IS NULL
OR transaction_type NOT IN (G_OPR_CREATE,G_OPR_UPDATE,G_OPR_DELETE,G_OPR_SYNC))
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_group_cols_intf
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE (transaction_type IS NULL
OR transaction_type NOT IN (G_OPR_CREATE,G_OPR_UPDATE,G_OPR_DELETE,G_OPR_SYNC))
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if internal name or the display name for AG is not
provided in the CREATE flow*/
G_MESSAGE_NAME := 'EGO_AG_MANDATORY';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT eagi.transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_AG_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_AG,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_groups_interface eagi
WHERE (attr_group_name IS NULL
OR attr_group_disp_name IS NULL)
AND transaction_id IS NOT NULL
AND transaction_type = G_OPR_CREATE
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_interface
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE (attr_group_name IS NULL
OR attr_group_disp_name IS NULL)
AND transaction_id IS NOT NULL
AND transaction_type = G_OPR_CREATE
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if Attribute Group Type in the AG interface table is other than EGO_ITEMMGMT_GROUP*/
G_MESSAGE_NAME := 'EGO_AG_TYPE_INVALID';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT eagi.transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_AG_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_ATTR,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_groups_interface eagi
WHERE attr_group_type <> G_EGO_ITEMMGMT_GROUP
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_interface
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE attr_group_type <> G_EGO_ITEMMGMT_GROUP
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if Attribute Group Type in the DL interface is other than EGO_ITEMMGMT_GROUP*/
G_MESSAGE_NAME := 'EGO_AG_TYPE_INVALID';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT eagi.transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_DL_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_DL,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_groups_dl_interface eagi
WHERE attr_group_type <> G_EGO_ITEMMGMT_GROUP
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE attr_group_type <> G_EGO_ITEMMGMT_GROUP
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if Attribute Group Type in the DL interface is other than EGO_ITEMMGMT_GROUP*/
G_MESSAGE_NAME := 'EGO_DL_MANDATORY';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT eagi.transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_DL_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_DL,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_groups_dl_interface eagi
WHERE (attr_group_type IS NULL
OR attr_group_name IS NULL
OR data_level_name IS NULL)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE (attr_group_type IS NULL
OR attr_group_name IS NULL
OR data_level_name IS NULL)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if internal name is not existing in the UPDATE flow.
Updates the AG interface table*/
G_MESSAGE_NAME := 'EGO_AG_NOT_EXIST';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT eagi.transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_AG_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_AG,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_groups_interface eagi
WHERE (attr_group_id IS NULL
OR attr_group_name IS NULL)
AND transaction_id IS NOT NULL
AND transaction_type IN (G_OPR_UPDATE,G_OPR_DELETE)
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_interface
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE (attr_group_id IS NULL
OR attr_group_name IS NULL)
AND transaction_id IS NOT NULL
AND transaction_type IN (G_OPR_UPDATE,G_OPR_DELETE)
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if internal name is not existing. Updates the DL
interface table*/
G_MESSAGE_NAME := 'EGO_AG_NOT_EXIST';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT eagdi.transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_DL_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_DL,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_groups_dl_interface eagdi
WHERE (eagdi.attr_group_id IS NULL
OR eagdi.attr_group_name IS NULL)
AND eagdi.transaction_id IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM ego_attr_groups_interface eagi
WHERE eagi.attr_group_name = eagdi.attr_group_name
AND eagi.transaction_type = G_OPR_CREATE
AND eagi.process_status = G_PROCESS_RECORD)
AND eagdi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface eagdi
SET eagdi.process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE (eagdi.attr_group_id IS NULL
OR eagdi.attr_group_name IS NULL)
AND eagdi.transaction_id IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM ego_attr_groups_interface eagi
WHERE eagi.attr_group_name = eagdi.attr_group_name
AND eagi.transaction_type = G_OPR_CREATE
AND eagi.process_status = G_PROCESS_RECORD)
AND eagdi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if for the CREATE flow if there already exists an AG with
the same internal attribute group name*/
G_MESSAGE_NAME := 'EGO_EF_ATTR_GRP_EXIST';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT eagi.transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_AG_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_AG,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_groups_interface eagi
WHERE EXISTS (SELECT 1
FROM EGO_FND_DSC_FLX_CTX_EXT efdfce
WHERE efdfce.DESCRIPTIVE_FLEX_CONTEXT_CODE = eagi.attr_group_name
AND efdfce.DESCRIPTIVE_FLEXFIELD_NAME = G_EGO_ITEMMGMT_GROUP
AND efdfce.APPLICATION_ID = G_EGO_APPLICATION_ID)
AND eagi.transaction_id IS NOT NULL
AND eagi.transaction_type = G_OPR_CREATE
AND eagi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_interface eagi
SET eagi.process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE EXISTS (SELECT 1
FROM EGO_FND_DSC_FLX_CTX_EXT efdfce
WHERE efdfce.DESCRIPTIVE_FLEX_CONTEXT_CODE = eagi.attr_group_name
AND efdfce.DESCRIPTIVE_FLEXFIELD_NAME = G_EGO_ITEMMGMT_GROUP
AND efdfce.APPLICATION_ID = G_EGO_APPLICATION_ID)
AND eagi.transaction_id IS NOT NULL
AND eagi.transaction_type = G_OPR_CREATE
AND eagi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if for the CREATE flow if there doesn't exist any DL
in the interface table*/
G_MESSAGE_NAME := 'EGO_EF_DL_REQD_AG';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT eagi.transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_AG_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_AG,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_groups_interface eagi
WHERE NOT EXISTS (SELECT 1
FROM ego_attr_groups_dl_interface eagdi
WHERE eagdi.attr_group_name = eagi.attr_group_name
AND eagdi.transaction_type = eagi.transaction_type
AND eagdi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID)))
AND eagi.transaction_id IS NOT NULL
AND eagi.transaction_type = G_OPR_CREATE
AND eagi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_interface eagi
SET eagi.process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE NOT EXISTS (SELECT 1
FROM ego_attr_groups_dl_interface eagdi
WHERE eagdi.attr_group_name = eagi.attr_group_name
AND eagdi.transaction_type = eagi.transaction_type
AND eagdi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID)))
AND eagi.transaction_id IS NOT NULL
AND eagi.transaction_type = G_OPR_CREATE
AND eagi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if Data Level passed is already enabled in the system*/
G_MESSAGE_NAME := 'EGO_EF_DL_AG_EXISTS';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT eagi.transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_DL_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_DL,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_groups_dl_interface eagi
WHERE EXISTS (SELECT 1
FROM EGO_ATTR_GROUP_DL eagdl
WHERE eagdl.attr_group_id = eagi.attr_group_id
AND eagdl.data_level_id = (SELECT data_level_id FROM ego_data_level_b
WHERE data_level_name=eagi.data_level_name
AND attr_group_type = G_EGO_ITEMMGMT_GROUP
AND application_id = G_EGO_APPLICATION_ID
))
AND eagi.transaction_id IS NOT NULL
AND eagi.transaction_type = G_OPR_CREATE
AND eagi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface eagi
SET eagi.process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE EXISTS (SELECT 1
FROM EGO_ATTR_GROUP_DL eagdl
WHERE eagdl.attr_group_id = eagi.attr_group_id
AND eagdl.data_level_id = (SELECT data_level_id FROM ego_data_level_b
WHERE data_level_name=eagi.data_level_name
AND attr_group_type = G_EGO_ITEMMGMT_GROUP
AND application_id = G_EGO_APPLICATION_ID
))
AND eagi.transaction_id IS NOT NULL
AND eagi.transaction_type = G_OPR_CREATE
AND eagi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if Data Level passed is incorrect*/
G_MESSAGE_NAME := 'EGO_DL_NOT_EXIST';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_DL_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_DL,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_groups_dl_interface
WHERE (data_level_id IS NULL
OR data_level_name IS NULL)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE (data_level_id IS NULL
OR data_level_name IS NULL)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if the AG is of type Variant and Business entity is not ITEM or the Style to SKU is not NULL*/
G_MESSAGE_NAME := 'EGO_VAR_DL_SKU';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT eagdi.transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_DL_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_DL,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_groups_dl_interface eagdi
WHERE EXISTS (SELECT 1
FROM ego_fnd_dsc_flx_ctx_ext eagd
WHERE eagd.attr_group_id = eagdi.attr_group_id
AND eagd.variant = 'Y'
/*Added for bug 9719196*/
UNION ALL
SELECT 1
FROM ego_attr_groups_interface
WHERE attr_group_name = eagdi.attr_group_name
AND transaction_type = G_OPR_CREATE
AND variant = 'Y'
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID))
/*End of bug 9719196*/ )
AND (eagdi.data_level_name <> G_DL_ITEM_LEVEL
OR eagdi.defaulting IS NOT NULL)
AND eagdi.transaction_id IS NOT NULL
AND eagdi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface eagdi
SET eagdi.process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE EXISTS (SELECT 1
FROM ego_fnd_dsc_flx_ctx_ext eagd
WHERE eagd.attr_group_id = eagdi.attr_group_id
AND eagd.variant = 'Y'
/*Added for bug 9719196*/
UNION ALL
SELECT 1
FROM ego_attr_groups_interface
WHERE attr_group_name = eagdi.attr_group_name
AND transaction_type = G_OPR_CREATE
AND variant = 'Y'
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID))
/*End of bug 9719196*/ )
AND (eagdi.data_level_name <> G_DL_ITEM_LEVEL
OR eagdi.defaulting IS NOT NULL)
AND eagdi.transaction_id IS NOT NULL
AND eagdi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if AG is of Single Row or MultiRow Business and the business entity is not any of the below
ITEM_LEVEL, ITEM_REVISION_LEVEL, ITEM_ORG, ITEM_SUP, ITEM_SUP_SITE, ITEM_SUP_SITE_ORG*/
G_MESSAGE_NAME := 'EGO_DL_NOT_EXIST';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT eagdi.transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_AG_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_AG,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_groups_dl_interface eagdi
WHERE EXISTS (SELECT 1
FROM ego_fnd_dsc_flx_ctx_ext eagd
WHERE eagd.attr_group_id = eagdi.attr_group_id
AND Nvl(eagd.variant,'N') = 'N')
AND eagdi.data_level_name NOT IN (G_DL_ITEM_LEVEL,G_DL_ITEM_REV_LEVEL,G_DL_ITEM_ORG,G_DL_ITEM_SUP,
G_DL_ITEM_SUP_SITE,G_DL_ITEM_SUP_SITE_ORG)
AND eagdi.transaction_id IS NOT NULL
AND eagdi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface eagdi
SET eagdi.process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE EXISTS (SELECT 1
FROM ego_fnd_dsc_flx_ctx_ext eagd
WHERE eagd.attr_group_id = eagdi.attr_group_id
AND Nvl(eagd.variant,'N') = 'N')
AND eagdi.data_level_name NOT IN (G_DL_ITEM_LEVEL,G_DL_ITEM_REV_LEVEL,G_DL_ITEM_ORG,G_DL_ITEM_SUP,
G_DL_ITEM_SUP_SITE,G_DL_ITEM_SUP_SITE_ORG)
AND eagdi.transaction_id IS NOT NULL
AND eagdi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if AG is of Single Row or MultiRow Business flag an error if the business entity and the
Style to SKU combination is invalid*/
G_MESSAGE_NAME := 'EGO_MULROW_SKU';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT eagdi.transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_DL_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_DL,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_groups_dl_interface eagdi
WHERE EXISTS (SELECT 1
FROM ego_fnd_dsc_flx_ctx_ext eagd
WHERE eagd.attr_group_id = eagdi.attr_group_id
AND Nvl(eagd.variant,'N') = 'N')
AND ((eagdi.data_level_name IN (G_DL_ITEM_LEVEL,G_DL_ITEM_REV_LEVEL,G_DL_ITEM_ORG,G_DL_ITEM_SUP,
G_DL_ITEM_SUP_SITE,G_DL_ITEM_SUP_SITE_ORG)
AND eagdi.defaulting NOT IN ('D','I'))
OR eagdi.data_level_name = G_DL_ITEM_REV_LEVEL
AND eagdi.defaulting IS NOT NULL)
AND eagdi.transaction_id IS NOT NULL
AND eagdi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface eagdi
SET eagdi.process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE EXISTS (SELECT 1
FROM ego_fnd_dsc_flx_ctx_ext eagd
WHERE eagd.attr_group_id = eagdi.attr_group_id
AND Nvl(eagd.variant,'N') = 'N')
AND ((eagdi.data_level_name IN (G_DL_ITEM_LEVEL,G_DL_ITEM_REV_LEVEL,G_DL_ITEM_ORG,G_DL_ITEM_SUP,
G_DL_ITEM_SUP_SITE,G_DL_ITEM_SUP_SITE_ORG)
AND eagdi.defaulting NOT IN ('D','I'))
OR eagdi.data_level_name = G_DL_ITEM_REV_LEVEL
AND eagdi.defaulting IS NOT NULL)
AND eagdi.transaction_id IS NOT NULL
AND eagdi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if View Privilege is incorrect*/
G_MESSAGE_NAME := 'EGO_VIEW_PRIV_NOT_EXIST';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_DL_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_DL,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_groups_dl_interface
WHERE ((view_privilege_id IS NULL
AND view_privilege_name IS NOT NULL
and view_privilege_name <> G_NULL_CHAR)
OR (view_privilege_name IS NULL
AND view_privilege_id IS NOT NULL
AND view_privilege_id <> G_NULL_NUM))
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE ((view_privilege_id IS NULL
AND view_privilege_name IS NOT NULL
and view_privilege_name <> G_NULL_CHAR)
OR (view_privilege_name IS NULL
AND view_privilege_id IS NOT NULL
AND view_privilege_id <> G_NULL_NUM))
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if Edit Privilege is incorrect*/
G_MESSAGE_NAME := 'EGO_EDIT_PRIV_NOT_EXIST';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_DL_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_DL,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_groups_dl_interface
WHERE ((edit_privilege_id IS NULL
AND edit_privilege_name IS NOT NULL
AND edit_privilege_name <> G_NULL_CHAR)
OR (edit_privilege_name IS NULL
AND edit_privilege_id IS NOT NULL
AND edit_privilege_id <> G_NULL_NUM)) -- Bug 12394675
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE ((edit_privilege_id IS NULL
AND edit_privilege_name IS NOT NULL)
OR (edit_privilege_name IS NULL
AND edit_privilege_id IS NOT NULL))
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if Defaulting passed is incorrect*/
G_MESSAGE_NAME := 'EGO_DEFAULTING_INVALID';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_DL_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_DL,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_groups_dl_interface eagdl
WHERE data_level_name <> 'ITEM_REVISION_LEVEL'
AND transaction_type <> G_OPR_DELETE
AND (defaulting IS NULL
OR defaulting_name IS NULL)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID))
AND NOT EXISTS /*Modified the where clause for bug 9886739*/
(SELECT 1
FROM EGO_FND_DSC_FLX_CTX_EXT efdfce
WHERE efdfce.DESCRIPTIVE_FLEX_CONTEXT_CODE = eagdl.attr_group_name
AND efdfce.DESCRIPTIVE_FLEXFIELD_NAME = G_EGO_ITEMMGMT_GROUP
AND efdfce.APPLICATION_ID = G_EGO_APPLICATION_ID
AND efdfce.VARIANT = 'Y')
AND NOT EXISTS
(SELECT 1
FROM ego_attr_groups_interface
WHERE ATTR_GROUP_NAME = eagdl.attr_group_name
AND ATTR_GROUP_TYPE = G_EGO_ITEMMGMT_GROUP
AND variant = 'Y');
UPDATE ego_attr_groups_dl_interface eagdl
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE data_level_name <> 'ITEM_REVISION_LEVEL'
AND transaction_type <> G_OPR_DELETE
AND (defaulting IS NULL
OR defaulting_name IS NULL)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID))
AND NOT EXISTS /*Modified the where clause for bug 9886739*/
(SELECT 1
FROM EGO_FND_DSC_FLX_CTX_EXT efdfce
WHERE efdfce.DESCRIPTIVE_FLEX_CONTEXT_CODE = eagdl.attr_group_name
AND efdfce.DESCRIPTIVE_FLEXFIELD_NAME = G_EGO_ITEMMGMT_GROUP
AND efdfce.APPLICATION_ID = G_EGO_APPLICATION_ID
AND efdfce.VARIANT = 'Y')
AND NOT EXISTS
(SELECT 1
FROM ego_attr_groups_interface
WHERE ATTR_GROUP_NAME = eagdl.attr_group_name
AND ATTR_GROUP_TYPE = G_EGO_ITEMMGMT_GROUP
AND variant = 'Y');
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if AG has associations and user is trying to delete a DL*/
G_MESSAGE_NAME := 'EGO_EF_ASSOCS_EXIST3';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_DL_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_DL,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_groups_dl_interface eagdl
WHERE transaction_type = G_OPR_DELETE
AND EXISTS (SELECT 1
FROM EGO_OBJ_AG_ASSOCS_B
WHERE ATTR_GROUP_ID = eagdl.attr_group_id
AND ENABLED_FLAG = 'Y')
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface eagdl
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE transaction_type = G_OPR_DELETE
AND EXISTS (SELECT 1
FROM EGO_OBJ_AG_ASSOCS_B
WHERE ATTR_GROUP_ID = eagdl.attr_group_id
AND ENABLED_FLAG = 'Y')
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if AG has associations and user is trying to delete the AG*/
G_MESSAGE_NAME := 'EGO_EF_ASSOCS_EXIST1';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_AG_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_AG,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_groups_interface eagdl
WHERE transaction_type = G_OPR_DELETE
AND EXISTS (SELECT 1
FROM EGO_OBJ_AG_ASSOCS_B
WHERE ATTR_GROUP_ID = eagdl.attr_group_id
AND ENABLED_FLAG = 'Y')
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_interface eagdl
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE transaction_type = G_OPR_DELETE
AND EXISTS (SELECT 1
FROM EGO_OBJ_AG_ASSOCS_B
WHERE ATTR_GROUP_ID = eagdl.attr_group_id
AND ENABLED_FLAG = 'Y')
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if Attribute Group Type in the Attribute interface table is other than EGO_ITEMMGMT_GROUP*/
G_MESSAGE_NAME := 'EGO_AG_TYPE_INVALID';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT eagi.transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_ATTR_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_ATTR,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_group_cols_intf eagi
WHERE attr_group_type <> G_EGO_ITEMMGMT_GROUP
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_group_cols_intf
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE attr_group_type <> G_EGO_ITEMMGMT_GROUP
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if the attribute with the internal name is already present in the system*/
G_MESSAGE_NAME := 'EGO_EF_INTERNAL_NAME_UNIQUE';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT eagi.transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_ATTR_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_ATTR,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_group_cols_intf eagi
WHERE EXISTS (SELECT 1
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = G_EGO_APPLICATION_ID
AND DESCRIPTIVE_FLEXFIELD_NAME = G_EGO_ITEMMGMT_GROUP
AND DESCRIPTIVE_FLEX_CONTEXT_Code = eagi.attr_group_name
AND END_USER_COLUMN_NAME = eagi.internal_name
)
AND transaction_type = G_OPR_CREATE
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_group_cols_intf eagi
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE EXISTS (SELECT 1
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = G_EGO_APPLICATION_ID
AND DESCRIPTIVE_FLEXFIELD_NAME = G_EGO_ITEMMGMT_GROUP
AND DESCRIPTIVE_FLEX_CONTEXT_Code = eagi.attr_group_name
AND END_USER_COLUMN_NAME = eagi.internal_name
)
AND transaction_type = G_OPR_CREATE
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if Attribute group does exist in the system or
as a create flow in the AG interface table. Updates the DL interface table*/
G_MESSAGE_NAME := 'EGO_AG_NOT_EXIST';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_ATTR_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_ATTR,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_group_cols_intf eagci
WHERE (eagci.attr_group_id IS NULL
OR eagci.attr_group_name IS NULL)
AND eagci.transaction_id IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM ego_attr_groups_interface eagi
WHERE eagi.attr_group_name = eagci.attr_group_name
AND eagi.transaction_type = G_OPR_CREATE
AND eagi.process_status = G_PROCESS_RECORD)
AND eagci.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_group_cols_intf eagci
SET eagci.process_status = G_ERROR_RECORD,
eagci.last_updated_by = G_USER_ID,
eagci.last_update_date = SYSDATE,
eagci.last_update_login = G_LOGIN_ID
WHERE (eagci.attr_group_id IS NULL
OR eagci.attr_group_name IS NULL)
AND eagci.transaction_id IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM ego_attr_groups_interface eagi
WHERE eagi.attr_group_name = eagci.attr_group_name
AND eagi.transaction_type = G_OPR_CREATE
AND eagi.process_status = G_PROCESS_RECORD)
AND eagci.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/*Sets the error EGO_ATTR_NOT_EXISTS if the attr_id or internal_name is null in the update flow*/
G_MESSAGE_NAME := 'EGO_ATTR_NOT_EXISTS';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_ATTR_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_ATTR,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_group_cols_intf eagci
WHERE ((eagci.attr_id IS NULL
AND eagci.internal_name IS NOT NULL)
OR (eagci.internal_name IS NULL
AND eagci.attr_id IS NOT NULL))
AND eagci.transaction_type <> G_OPR_CREATE
AND eagci.transaction_id IS NOT NULL
AND eagci.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_group_cols_intf eagci
SET eagci.process_status = G_ERROR_RECORD,
eagci.last_updated_by = G_USER_ID,
eagci.last_update_date = SYSDATE,
eagci.last_update_login = G_LOGIN_ID
WHERE ((eagci.attr_id IS NULL
AND eagci.internal_name IS NOT NULL)
OR (eagci.internal_name IS NULL
AND eagci.attr_id IS NOT NULL))
AND eagci.transaction_type <> G_OPR_CREATE
AND eagci.transaction_id IS NOT NULL
AND eagci.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_ATTR_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_ATTR,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_group_cols_intf eagci
WHERE ((eagci.flex_value_set_id IS NULL
AND eagci.flex_value_set_name IS NOT NULL
AND eagci.flex_value_set_name <> G_NULL_CHAR)
OR (eagci.flex_value_set_name IS NULL
AND eagci.flex_value_set_id IS NOT NULL
AND eagci.flex_value_set_id <> G_NULL_NUM))
AND eagci.transaction_id IS NOT NULL
AND eagci.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_group_cols_intf eagci
SET eagci.process_status = G_ERROR_RECORD,
eagci.last_updated_by = G_USER_ID,
eagci.last_update_date = SYSDATE,
eagci.last_update_login = G_LOGIN_ID
WHERE ((eagci.flex_value_set_id IS NULL
AND eagci.flex_value_set_name IS NOT NULL
AND eagci.flex_value_set_name <> G_NULL_CHAR)
OR (eagci.flex_value_set_name IS NULL
AND eagci.flex_value_set_id IS NOT NULL
AND eagci.flex_value_set_id <> G_NULL_NUM))
AND eagci.transaction_id IS NOT NULL
AND eagci.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if AG has associations and user is trying to delete the attributes*/
G_MESSAGE_NAME := 'EGO_EF_ASSOCS_EXIST2';
INSERT INTO mtl_interface_errors
(transaction_id,
unique_id,
organization_id,
column_name,
table_name,
bo_identifier,
entity_identifier,
message_name,
error_message,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT transaction_id,
mtl_system_items_interface_s.nextval,
NULL,
NULL,
G_ENTITY_ATTR_TAB,
G_BO_IDENTIFIER_AG,
G_ENTITY_ATTR,
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
Nvl(last_update_date,SYSDATE),
Nvl(last_updated_by,G_USER_ID),
Nvl(creation_date,SYSDATE),
Nvl(created_by,G_USER_ID),
Nvl(last_update_login,G_USER_ID),
G_REQUEST_ID,
Nvl(program_application_id,G_PROG_APPL_ID),
Nvl(program_id,G_PROGRAM_ID),
Nvl(program_update_date,SYSDATE)
FROM ego_attr_group_cols_intf eagdl
WHERE transaction_type = G_OPR_DELETE
AND EXISTS (SELECT 1
FROM EGO_OBJ_AG_ASSOCS_B
WHERE ATTR_GROUP_ID = eagdl.attr_group_id
AND ENABLED_FLAG = 'Y')
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_group_cols_intf eagdl
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE transaction_type = G_OPR_DELETE
AND EXISTS (SELECT 1
FROM EGO_OBJ_AG_ASSOCS_B
WHERE ATTR_GROUP_ID = eagdl.attr_group_id
AND ENABLED_FLAG = 'Y')
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_interface eagi
SET eagi.attr_group_id = (SELECT attr_group_id
FROM ego_fnd_dsc_flx_ctx_ext
WHERE application_id = g_ego_application_id
AND descriptive_flexfield_name = eagi.attr_group_type
AND descriptive_flex_context_code = eagi.attr_group_name),
eagi.transaction_type = decode(transaction_type,G_OPR_SYNC,G_OPR_UPDATE,transaction_type),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE eagi.transaction_type <> G_OPR_CREATE
AND eagi.attr_group_id IS NULL
AND eagi.attr_group_name IS NOT NULL
AND eagi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_interface eagi
SET eagi.transaction_type = decode(attr_group_id,null,G_OPR_CREATE,G_OPR_UPDATE),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE eagi.transaction_type NOT IN (G_OPR_CREATE,G_OPR_DELETE)
AND eagi.attr_group_id IS NULL
AND eagi.attr_group_name IS NOT NULL
AND eagi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface eagdi
SET eagdi.attr_group_id = (SELECT attr_group_id
FROM ego_fnd_dsc_flx_ctx_ext
WHERE application_id = g_ego_application_id
AND descriptive_flexfield_name = eagdi.attr_group_type
AND descriptive_flex_context_code = eagdi.attr_group_name),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE eagdi.attr_group_id IS NULL
AND eagdi.attr_group_name IS NOT NULL
AND eagdi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface eagdl
SET eagdl.view_privilege_id = (SELECT function_id
FROM fnd_form_functions
WHERE function_name = eagdl.view_privilege_name),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE eagdl.view_privilege_id IS NULL
AND eagdl.view_privilege_name IS NOT NULL
AND eagdl.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface eagdl
SET view_privilege_id = (SELECT function_id
FROM fnd_form_functions_vl
WHERE user_function_name = eagdl.user_view_priv_name),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE eagdl.view_privilege_id IS NULL
AND eagdl.user_view_priv_name IS NOT NULL
AND eagdl.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface eagdl
SET edit_privilege_id = (SELECT function_id
FROM fnd_form_functions
WHERE function_name = eagdl.edit_privilege_name),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE eagdl.edit_privilege_id IS NULL
AND eagdl.edit_privilege_name IS NOT NULL
AND eagdl.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface eagdl
SET edit_privilege_id = (SELECT function_id
FROM fnd_form_functions_vl
WHERE user_function_name = eagdl.user_edit_priv_name),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE eagdl.edit_privilege_id IS NULL
AND eagdl.user_edit_priv_name IS NOT NULL
AND eagdl.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface eagdi
SET eagdi.data_level_id = (SELECT data_level_id
FROM ego_data_level_b
WHERE attr_group_type = eagdi.attr_group_type
AND application_id = g_ego_application_id
AND data_level_name = eagdi.data_level_name),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE eagdi.data_level_id IS NULL
AND eagdi.data_level_name IS NOT NULL
AND eagdi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/*Sets the SYNC transaction type to UPDATE*/
UPDATE ego_attr_groups_dl_interface eagdi
SET eagdi.transaction_type = G_OPR_UPDATE,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE eagdi.data_level_id IS NOT NULL
AND eagdi.attr_group_id IS NOT NULL
AND transaction_type = G_OPR_SYNC
AND EXISTS (SELECT 1
FROM ego_attr_group_dl eagdl
WHERE eagdl.attr_group_id = eagdi.attr_group_id
AND eagdl.data_level_id = eagdi.data_level_id)
AND eagdi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface eagdi
SET eagdi.transaction_type = G_OPR_CREATE,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE eagdi.data_level_id IS NOT NULL
AND eagdi.attr_group_id IS NOT NULL
AND transaction_type = G_OPR_SYNC
AND NOT EXISTS (SELECT 1
FROM ego_attr_group_dl eagdl
WHERE eagdl.attr_group_id = eagdi.attr_group_id
AND eagdl.data_level_id = eagdi.data_level_id)
AND eagdi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface eagdi
SET eagdi.transaction_type = G_OPR_CREATE,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE eagdi.attr_group_id IS NULL
AND transaction_type = G_OPR_SYNC
AND eagdi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_group_cols_intf eagci
SET eagci.attr_group_id = (SELECT attr_group_id
FROM ego_fnd_dsc_flx_ctx_ext
WHERE application_id = g_ego_application_id
AND descriptive_flexfield_name = eagci.attr_group_type
AND descriptive_flex_context_code = eagci.attr_group_name),
eagci.last_updated_by = G_USER_ID,
eagci.last_update_date = SYSDATE,
eagci.last_update_login = G_LOGIN_ID
WHERE eagci.attr_group_id IS NULL
AND eagci.attr_group_name IS NOT NULL
AND eagci.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_group_cols_intf
SET application_id = g_ego_application_id,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE application_id IS NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/*Sets the Attr_id for the update and sync flow*/
UPDATE ego_attr_group_cols_intf eagci
SET eagci.attr_id = (SELECT efdcue.attr_id
FROM fnd_descr_flex_column_usages fdfcu,
ego_fnd_df_col_usgs_ext efdcue
WHERE fdfcu.application_id = efdcue.application_id
AND fdfcu.descriptive_flexfield_name = efdcue.descriptive_flexfield_name
AND fdfcu.descriptive_flex_context_code = efdcue.descriptive_flex_context_code
AND fdfcu.application_column_name = efdcue.application_column_name
AND fdfcu.application_id = g_ego_application_id
AND fdfcu.descriptive_flexfield_name = eagci.attr_group_type
AND fdfcu.descriptive_flex_context_code = eagci.attr_group_name
AND fdfcu.end_user_column_name = eagci.internal_name)
WHERE eagci.attr_id IS NULL
AND eagci.internal_name IS NOT NULL
AND eagci.transaction_type IN (G_OPR_UPDATE,G_OPR_SYNC,G_OPR_DELETE)
AND eagci.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_group_cols_intf eagci
SET eagci.flex_value_set_id = (SELECT ffvs.flex_value_set_id
FROM fnd_flex_value_sets ffvs
WHERE ffvs.flex_value_set_name = eagci.flex_value_set_name)
WHERE eagci.flex_value_set_id IS NULL
AND eagci.flex_value_set_name IS NOT NULL
AND eagci.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
/*Sets the Sync type to UPDATE*/
UPDATE ego_attr_group_cols_intf
SET transaction_type = G_OPR_UPDATE
WHERE transaction_type = G_OPR_SYNC
AND attr_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_group_cols_intf
SET transaction_type = G_OPR_CREATE
WHERE transaction_type = G_OPR_SYNC
AND attr_id IS NULL
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_interface eagi
SET eagi.attr_group_name = (SELECT attr_group_name
FROM ego_fnd_dsc_flx_ctx_ext
WHERE application_id = g_ego_application_id
AND descriptive_flexfield_name = eagi.attr_group_type
AND attr_group_id = eagi.attr_group_id),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE eagi.transaction_type = G_OPR_UPDATE
AND eagi.attr_group_id IS NOT NULL
AND eagi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_interface
SET multi_row = 'N',
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE multi_row IS NULL
AND transaction_type = G_OPR_CREATE
AND process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface eagdi
SET eagdi.attr_group_name = (SELECT attr_group_name
FROM ego_fnd_dsc_flx_ctx_ext
WHERE application_id = g_ego_application_id
AND descriptive_flexfield_name = eagdi.attr_group_type
AND attr_group_id = eagdi.attr_group_id),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE eagdi.attr_group_id IS NOT NULL
AND eagdi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface eagdl
SET (view_privilege_name,user_view_priv_name) = (SELECT function_name, user_function_name
FROM fnd_form_functions_vl
WHERE function_id = eagdl.view_privilege_id),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE eagdl.view_privilege_id IS NOT NULL
AND eagdl.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface eagdl
SET (edit_privilege_name,user_edit_priv_name) = (SELECT function_name, user_function_name
FROM fnd_form_functions_vl
WHERE function_id = eagdl.edit_privilege_id),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE eagdl.edit_privilege_id IS NOT NULL
AND eagdl.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface eagdl
SET eagdl.defaulting = (SELECT lookup_code
FROM fnd_lookup_values
WHERE lookup_type = 'EGO_EF_AG_DL_BEHAVIOR'
AND meaning = eagdl.defaulting_name
AND language = Userenv('LANG')),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE eagdl.defaulting IS NULL
AND eagdl.defaulting_name IS NOT NULL
AND eagdl.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface eagdl
SET eagdl.defaulting_name = (SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = 'EGO_EF_AG_DL_BEHAVIOR'
AND lookup_code = eagdl.defaulting
AND language = Userenv('LANG')),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE eagdl.defaulting IS NOT NULL
AND eagdl.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_groups_dl_interface eagdi
SET eagdi.data_level_name = (SELECT data_level_name
FROM ego_data_level_b
WHERE attr_group_type = eagdi.attr_group_type
AND application_id = g_ego_application_id
AND data_level_id = eagdi.data_level_id),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE eagdi.data_level_id IS NOT NULL
AND eagdi.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_group_cols_intf eagci
SET eagci.attr_group_name = (SELECT descriptive_flex_context_code
FROM ego_fnd_dsc_flx_ctx_ext
WHERE application_id = g_ego_application_id
AND descriptive_flexfield_name = eagci.attr_group_type
AND attr_group_id = eagci.attr_group_id),
eagci.last_updated_by = G_USER_ID,
eagci.last_update_date = SYSDATE,
eagci.last_update_login = G_LOGIN_ID
WHERE eagci.attr_group_id IS NOT NULL
AND eagci.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_group_cols_intf eagci
SET eagci.internal_name = (SELECT fdfcu.end_user_column_name
FROM fnd_descr_flex_column_usages fdfcu,
ego_fnd_df_col_usgs_ext efdcue
WHERE fdfcu.application_id = efdcue.application_id
AND fdfcu.descriptive_flexfield_name = efdcue.descriptive_flexfield_name
AND fdfcu.descriptive_flex_context_code = efdcue.descriptive_flex_context_code
AND fdfcu.application_column_name = efdcue.application_column_name
AND fdfcu.application_id = g_ego_application_id
AND fdfcu.descriptive_flexfield_name = eagci.attr_group_type
AND fdfcu.descriptive_flex_context_code = eagci.attr_group_name
AND efdcue.attr_id = eagci.attr_id)
WHERE eagci.attr_id IS NOT NULL
AND eagci.transaction_type IN (G_OPR_UPDATE,G_OPR_SYNC)
AND eagci.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
UPDATE ego_attr_group_cols_intf eagci
SET eagci.flex_value_set_name = (SELECT ffvs.flex_value_set_name
FROM fnd_flex_value_sets ffvs
WHERE ffvs.flex_value_set_id = eagci.flex_value_set_id)
WHERE eagci.flex_value_set_id IS NOT NULL
AND eagci.process_status = G_PROCESS_RECORD
AND ((G_SET_PROCESS_ID IS NULL)
OR (set_process_id = G_SET_PROCESS_ID));
SELECT application_id
INTO g_ego_application_id
FROM fnd_application
WHERE application_short_name = 'EGO';
delete_ag_none_dl(x_return_status => x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
G_TOKEN_TABLE.DELETE;
AND P_ag_tbl(i).transaction_type = 'UPDATE') THEN
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Update AG');
ego_ext_fwk_pub.Update_attribute_group(p_api_version => 1.0
,p_application_id => g_ego_application_id
,p_attr_group_type => P_ag_tbl(i).attr_group_type
,p_internal_name => P_ag_tbl(i).attr_group_name
,p_display_name => P_ag_tbl(i).attr_group_disp_name
,p_attr_group_desc => P_ag_tbl(i).description
,p_security_type => 'P' -- we always use PUBLIC for now
/*Changes made for bug 9719202*/
,p_multi_row_attrib_group => null--P_ag_tbl(i).multi_row
,p_variant_attrib_group => null--P_ag_tbl(i).variant
,p_num_of_cols => P_ag_tbl(i).num_of_cols
,p_num_of_rows => P_ag_tbl(i).num_of_rows
/*End of comment for bug 9719202*/
,p_owning_company_id => -100
,p_owner => G_USER_ID
,p_commit => fnd_api.g_false
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
G_TOKEN_TABLE(4).Token_Value := 'Update_attribute_group';
G_TOKEN_TABLE.DELETE;
AND P_ag_tbl(i).transaction_type = 'DELETE') THEN
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Delete AG');
ego_ext_fwk_pub.Delete_attribute_group(p_api_version => 1.0
,p_application_id => g_ego_application_id
,p_attr_group_type => P_ag_tbl(i).attr_group_type
,p_attr_group_name => P_ag_tbl(i).attr_group_name
,p_commit => fnd_api.g_false
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
G_TOKEN_TABLE(4).Token_Value := 'Delete_attribute_group';
G_TOKEN_TABLE.DELETE;
IF (P_agdl_tbl(i).process_status = G_PROCESS_RECORD AND P_agdl_tbl(i).transaction_type IN (G_OPR_CREATE,G_OPR_UPDATE)) THEN
/*Check for invalid combination of business entity*/
IF (p_agdl_tbl(i).data_level_name = G_DL_ITEM_LEVEL) THEN
DECLARE
lv_count NUMBER := 0;
SELECT count(1) INTO lv_count
FROM EGO_ATTR_GROUP_DL eagdl
WHERE eagdl.attr_group_id = p_agdl_tbl(i).attr_group_id
AND eagdl.data_level_id IN (SELECT data_level_id FROM ego_data_level_b
WHERE data_level_name IN (G_DL_ITEM_REV_LEVEL,G_DL_ITEM_ORG)
AND attr_group_type = G_EGO_ITEMMGMT_GROUP
AND application_id = G_EGO_APPLICATION_ID
);
SELECT count(1) INTO lv_count
FROM EGO_ATTR_GROUP_DL eagdl
WHERE eagdl.attr_group_id = p_agdl_tbl(i).attr_group_id
AND eagdl.data_level_id IN (SELECT data_level_id FROM ego_data_level_b
WHERE data_level_name IN (G_DL_ITEM_LEVEL,G_DL_ITEM_ORG)
AND attr_group_type = G_EGO_ITEMMGMT_GROUP
AND application_id = G_EGO_APPLICATION_ID
);
SELECT count(1) INTO lv_count
FROM EGO_ATTR_GROUP_DL eagdl
WHERE eagdl.attr_group_id = p_agdl_tbl(i).attr_group_id
AND eagdl.data_level_id IN (SELECT data_level_id FROM ego_data_level_b
WHERE data_level_name IN (G_DL_ITEM_LEVEL,G_DL_ITEM_REV_LEVEL)
AND attr_group_type = G_EGO_ITEMMGMT_GROUP
AND application_id = G_EGO_APPLICATION_ID
);
SELECT count(1) INTO lv_count
FROM EGO_ATTR_GROUP_DL
WHERE attr_group_id = P_agdl_tbl(i).attr_group_id
AND data_level_id = P_agdl_tbl(i).data_level_id;
p_last_updated_by => G_USER_ID,
p_last_update_date => SYSDATE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
G_TOKEN_TABLE.DELETE;
ELSIF (P_agdl_tbl(i).process_status = G_PROCESS_RECORD AND P_agdl_tbl(i).transaction_type = G_OPR_DELETE) THEN
BEGIN
DELETE FROM EGO_ATTR_GROUP_DL
WHERE attr_group_id = P_agdl_tbl(i).attr_group_id
AND data_level_id = P_agdl_tbl(i).data_level_id;
SELECT application_id
INTO g_ego_application_id
FROM fnd_application
WHERE application_short_name = 'EGO';
G_TOKEN_TABLE.DELETE;
AND P_attr_tbl(i).transaction_type = 'UPDATE') THEN
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Update Attribute');
ego_ext_fwk_pub.Update_attribute(p_api_version => 1.0
,p_application_id => P_attr_tbl(i).application_id
,p_attr_group_type => P_attr_tbl(i).attr_group_type
,p_attr_group_name => P_attr_tbl(i).attr_group_name
,p_internal_name => P_attr_tbl(i).internal_name
,p_display_name => P_attr_tbl(i).display_name
,p_description => P_attr_tbl(i).description
,p_sequence => P_attr_tbl(i).SEQUENCE
,p_required => P_attr_tbl(i).required_flag
,p_searchable => P_attr_tbl(i).search_flag
,p_column => P_attr_tbl(i).application_column_name
,p_value_set_id => P_attr_tbl(i).flex_value_set_id
,p_info_1 => P_attr_tbl(i).info_1
,p_default_value => P_attr_tbl(i).default_value
,p_unique_key_flag => P_attr_tbl(i).unique_key_flag
,p_enabled => P_attr_tbl(i).enabled_flag
,p_display => P_attr_tbl(i).display_code
,p_owner => G_USER_ID
,p_lud => SYSDATE
,p_init_msg_list => NULL
,p_commit => fnd_api.g_false
,p_is_nls_mode => NULL
,p_uom_class => P_attr_tbl(i).uom_class
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
G_TOKEN_TABLE(4).Token_Value := 'Update_attribute';
G_TOKEN_TABLE.DELETE;
AND P_attr_tbl(i).transaction_type = 'DELETE') THEN
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Delete Attribute');
ego_ext_fwk_pub.Delete_attribute(p_api_version => 1.0
,p_application_id => P_attr_tbl(i).application_id
,p_attr_group_type => P_attr_tbl(i).attr_group_type
,p_attr_group_name => P_attr_tbl(i).attr_group_name
,p_attr_name => P_attr_tbl(i).internal_name
,p_init_msg_list => NULL
,p_commit => fnd_api.g_false
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
G_TOKEN_TABLE(4).Token_Value := 'Delete_attribute';
G_TOKEN_TABLE.DELETE;
SELECT *
FROM ego_attr_groups_interface
WHERE process_status = G_PROCESS_RECORD
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id);
SELECT *
FROM ego_attr_groups_dl_interface
WHERE process_status = G_PROCESS_RECORD
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id);
SELECT *
FROM ego_attr_group_cols_intf
WHERE process_status = G_PROCESS_RECORD
AND (p_set_process_id IS NULL OR set_process_id = p_set_process_id);
update_intf_attr_groups(p_ag_tbl => l_ego_ag_tbl,x_return_status => x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
update_intf_data_level(p_agdl_tbl => l_ego_agdl_tbl,x_return_status => x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
delete_ag_none_dl(x_return_status => x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
update_intf_attribute(p_attr_tbl => l_ego_attr_tbl,x_return_status => x_return_status, x_return_msg => x_return_msg); IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN RETURN; END IF;
SELECT mtl_system_items_interface_s.nextval
INTO P_ag_tbl(i).transaction_id
FROM dual;
/*Sets the Attribute ID if present for Update and Sync flow. If not present then flags an error EGO_AG_INVALID for UPDATE flow
and for sync flow updates the transaction type to CREATE*/
IF ((P_ag_tbl(i).transaction_type = G_OPR_UPDATE
OR P_ag_tbl(i).transaction_type = G_OPR_SYNC)
AND P_ag_tbl(i).attr_group_id IS NULL
AND P_ag_tbl(i).attr_group_name IS NOT NULL) THEN
BEGIN
SELECT attr_group_id,
'UPDATE'
INTO P_ag_tbl(i).attr_group_id,P_ag_tbl(i).transaction_type
FROM ego_fnd_dsc_flx_ctx_ext
WHERE application_id = g_ego_application_id
AND descriptive_flexfield_name = P_ag_tbl(i).attr_group_type
AND descriptive_flex_context_code = P_ag_tbl(i).attr_group_name;
SELECT mtl_system_items_interface_s.nextval
INTO P_agdl_tbl(i).transaction_id
FROM dual;
SELECT function_id
INTO P_agdl_tbl(i).view_privilege_id
FROM fnd_form_functions
WHERE function_name = P_agdl_tbl(i).view_privilege_name;
SELECT function_id
INTO P_agdl_tbl(i).edit_privilege_id
FROM fnd_form_functions
WHERE function_name = P_agdl_tbl(i).edit_privilege_name;
SELECT data_level_id
INTO P_agdl_tbl(i).data_level_id
FROM ego_data_level_b
WHERE attr_group_type = P_agdl_tbl(i).attr_group_type
AND application_id = g_ego_application_id
AND data_level_name = P_agdl_tbl(i).data_level_name;
/*Sets the SYNC Transaction Type to CREATE or UPDATE*/
IF (P_agdl_tbl(i).data_level_id IS NOT NULL
AND P_agdl_tbl(i).attr_group_id IS NOT NULL
AND P_agdl_tbl(i).transaction_type = G_OPR_SYNC) THEN
BEGIN
SELECT 'Y'
INTO lv_flag
FROM ego_attr_group_dl
WHERE data_level_id = P_agdl_tbl(i).data_level_id
AND attr_group_id = P_agdl_tbl(i).attr_group_id;
P_agdl_tbl(i).transaction_type := G_OPR_UPDATE;
SELECT attr_group_id
INTO P_attr_tbl(i).attr_group_id
FROM ego_fnd_dsc_flx_ctx_ext
WHERE application_id = g_ego_application_id
AND descriptive_flexfield_name = P_attr_tbl(i).attr_group_type
AND descriptive_flex_context_code = P_attr_tbl(i).attr_group_name;
/*Sets the Attr_id for the update and sync flow. Also handles convert SYNC to CREATE/UPDATE flow*/
IF (P_attr_tbl(i).attr_id IS NULL
AND P_attr_tbl(i).internal_name IS NOT NULL
AND (P_attr_tbl(i).transaction_type = G_OPR_UPDATE
OR P_attr_tbl(i).transaction_type = G_OPR_SYNC)) THEN
BEGIN
SELECT efdcue.attr_id
INTO P_attr_tbl(i).attr_id
FROM fnd_descr_flex_column_usages fdfcu,
ego_fnd_df_col_usgs_ext efdcue
WHERE fdfcu.application_id = efdcue.application_id
AND fdfcu.descriptive_flexfield_name = efdcue.descriptive_flexfield_name
AND fdfcu.descriptive_flex_context_code = efdcue.descriptive_flex_context_code
AND fdfcu.application_column_name = efdcue.application_column_name
AND fdfcu.application_id = g_ego_application_id
AND fdfcu.descriptive_flexfield_name = P_attr_tbl(i).attr_group_type
AND fdfcu.descriptive_flex_context_code = P_attr_tbl(i).attr_group_name
AND fdfcu.end_user_column_name = P_attr_tbl(i).internal_name;
P_attr_tbl(i).transaction_type := G_OPR_UPDATE;
SELECT ffvs.flex_value_set_id
INTO P_attr_tbl(i).flex_value_set_id
FROM fnd_flex_value_sets ffvs
WHERE ffvs.flex_value_set_name = P_attr_tbl(i).flex_value_set_name;
SELECT *
FROM ego_fnd_dsc_flx_ctx_ext ed
WHERE ed.attr_group_id = p_attr_group_id;
IF (P_ag_tbl(i).transaction_type = G_OPR_UPDATE
AND ( ( nvl(P_ag_tbl(i).multi_row,'N') <> nvl(l_old_ag_record.multi_row,'N') ) OR
( nvl(P_ag_tbl(i).variant,'N') <> nvl(l_old_ag_record.variant,'N') )
)
) THEN
P_ag_tbl(i).process_status := G_ERROR_RECORD;
IF (p_agdl_tbl(i).transaction_type = G_OPR_DELETE) THEN
DECLARE
lv_flag NUMBER;
SELECT 1 INTO lv_flag
FROM EGO_ATTR_GROUP_DL
WHERE attr_group_id = p_agdl_tbl(i).attr_group_id
AND data_level_id = p_agdl_tbl(i).data_level_id;
SELECT COUNT(1) INTO lv_count
FROM EGO_ATTR_GROUP_DL
WHERE attr_group_id = p_agdl_tbl(i).attr_group_id;
||'Cannot delete the business entity since there is only one business entity');
||'Cannot delete the business entity since its not associated to the AG');
SELECT attr_group_id INTO p_agdl_tbl(i).attr_group_id
FROM ego_fnd_dsc_flx_ctx_ext
WHERE application_id = G_EGO_APPLICATION_ID
AND descriptive_flexfield_name = p_agdl_tbl(i).attr_group_type
AND descriptive_flex_context_code = p_agdl_tbl(i).attr_group_name;
ELSIF (p_attr_tbl(i).transaction_type = G_OPR_DELETE) THEN
NULL;
SELECT 'Y' INTO lv_flag
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = G_EGO_APPLICATION_ID
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_tbl(i).attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_Code = p_attr_tbl(i).attr_group_name
AND COLUMN_SEQ_NUM = p_attr_tbl(i).sequence;
/*Checks for existing sequence number - UPDATE flow*/ --Added for 9625957
IF (p_attr_tbl(i).sequence IS NOT NULL AND p_attr_tbl(i).transaction_type = G_OPR_UPDATE) THEN
DECLARE
l_sequence NUMBER;
SELECT COLUMN_SEQ_NUM
INTO l_sequence
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = G_EGO_APPLICATION_ID
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_tbl(i).attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_Code = p_attr_tbl(i).attr_group_name
AND END_USER_COLUMN_NAME = p_attr_tbl(i).internal_name;
-- If the sequence is being updated to a NEW non-null value,
-- check for uniqueness
SELECT COUNT(*)
INTO l_sequence
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = G_EGO_APPLICATION_ID
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_tbl(i).attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_Code = p_attr_tbl(i).attr_group_name
AND COLUMN_SEQ_NUM = p_attr_tbl(i).sequence;
||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'SEQUENCE already exists in the system - UPDATE');
SELECT 'Y' INTO lv_flag
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = G_EGO_APPLICATION_ID
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_tbl(i).attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_Code = p_attr_tbl(i).attr_group_name
AND application_column_name = p_attr_tbl(i).application_column_name;
/*Checks for existing column name - UPDATE flow*/ --Added for 9625957
IF (p_attr_tbl(i).sequence IS NOT NULL AND p_attr_tbl(i).transaction_type = G_OPR_UPDATE) THEN
DECLARE
lv_column VARCHAR2(40);
SELECT application_column_name
INTO lv_column
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = G_EGO_APPLICATION_ID
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_tbl(i).attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_Code = p_attr_tbl(i).attr_group_name
AND END_USER_COLUMN_NAME = p_attr_tbl(i).internal_name;
-- If the sequence is being updated to a NEW non-null value,
-- check for uniqueness
SELECT COUNT(*)
INTO lv_count
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = G_EGO_APPLICATION_ID
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_tbl(i).attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_Code = p_attr_tbl(i).attr_group_name
AND application_column_name = p_attr_tbl(i).application_column_name;
||p_attr_tbl(i).attr_group_name||','||p_attr_tbl(i).internal_name||'). ' ||'SEQUENCE already exists in the system - UPDATE');
SELECT COLUMN_TYPE , WIDTH
INTO l_col_data_type, l_col_width
FROM FND_COLUMNS
WHERE COLUMN_NAME = p_attr_tbl(i).application_column_name
AND TABLE_ID = (SELECT TABLE_ID
FROM FND_TABLES
WHERE TABLE_NAME = l_table_name);
IF (p_attr_tbl(i).transaction_type <> G_OPR_DELETE AND
((p_attr_tbl(i).data_type = G_CHAR_DATA_TYPE OR
p_attr_tbl(i).data_type = G_TRANS_TEXT_DATA_TYPE) AND
l_col_data_type <> 'V') OR
(p_attr_tbl(i).data_type = G_NUMBER_DATA_TYPE AND l_col_data_type <> 'N') OR
((p_attr_tbl(i).data_type = G_DATE_DATA_TYPE OR
p_attr_tbl(i).data_type = G_DATE_TIME_DATA_TYPE) AND
l_col_data_type <> 'D')) THEN
/***
TO DO: right now we can't verify that TransText Attributes use TL-type columns,
because we can't rely on the column being named 'TL_EXT_ATTR%' and we aren't
using FND_COLUMNS's TRANSLATE_FLAG column yet; but we should be, and we should
IF (p_attr_tbl(i).transaction_type <> G_OPR_DELETE) THEN
-- whoever owns the table didn't seed the column correctly
p_attr_tbl(i).process_status := G_ERROR_RECORD;
G_TOKEN_TABLE.DELETE;
G_TOKEN_TABLE.DELETE;
G_TOKEN_TABLE.DELETE;
G_TOKEN_TABLE.DELETE;
G_TOKEN_TABLE.DELETE;
SELECT Upper(P_ag_tbl(i).transaction_type)
INTO P_ag_tbl(i).transaction_type
FROM dual;
OR P_ag_tbl(i).transaction_type = G_OPR_UPDATE
OR P_ag_tbl(i).transaction_type = G_OPR_DELETE
OR P_ag_tbl(i).transaction_type = G_OPR_SYNC) THEN
NULL;
SELECT Upper(P_agdl_tbl(i).transaction_type)
INTO P_agdl_tbl(i).transaction_type
FROM dual;
OR P_agdl_tbl(i).transaction_type = G_OPR_UPDATE
OR P_agdl_tbl(i).transaction_type = G_OPR_SYNC) THEN
NULL;
SELECT attr_group_id
INTO P_agdl_tbl(i).attr_group_id
FROM ego_fnd_dsc_flx_ctx_ext
WHERE application_id = g_ego_application_id
AND descriptive_flexfield_name = P_agdl_tbl(i).attr_group_type
AND descriptive_flex_context_code = P_agdl_tbl(i).attr_group_name;
IF (P_agdl_tbl(i).transaction_type = G_OPR_UPDATE) THEN
P_agdl_tbl(i).process_status := G_ERROR_RECORD;
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Please insert at least one business entity when creating an Attribute Group');
AND P_ag_tbl(i).transaction_type = G_OPR_UPDATE) THEN
BEGIN
SELECT descriptive_flex_context_code
INTO P_ag_tbl(i).attr_group_name
FROM ego_fnd_dsc_flx_ctx_ext
WHERE application_id = g_ego_application_id
AND descriptive_flexfield_name = P_ag_tbl(i).attr_group_type
AND descriptive_flex_context_code = P_ag_tbl(i).attr_group_id;
AND P_agdl_tbl(i).transaction_type = G_OPR_UPDATE) THEN
BEGIN
SELECT descriptive_flex_context_code
INTO P_agdl_tbl(i).attr_group_name
FROM ego_fnd_dsc_flx_ctx_ext
WHERE application_id = g_ego_application_id
AND descriptive_flexfield_name = P_agdl_tbl(i).attr_group_type
AND descriptive_flex_context_code = P_agdl_tbl(i).attr_group_id;
SELECT function_name
INTO P_agdl_tbl(i).view_privilege_name
FROM fnd_form_functions
WHERE function_id = P_agdl_tbl(i).view_privilege_id;
SELECT function_name
INTO P_agdl_tbl(i).edit_privilege_name
FROM fnd_form_functions
WHERE function_id = P_agdl_tbl(i).edit_privilege_id;
AND P_agdl_tbl(i).transaction_type <> G_OPR_DELETE) THEN
BEGIN
SELECT meaning
INTO P_agdl_tbl(i).defaulting_name
FROM fnd_lookup_values
WHERE lookup_type = 'EGO_EF_AG_DL_BEHAVIOR'
AND lookup_code = P_agdl_tbl(i).defaulting
AND language = Userenv('LANG');
and P_agdl_tbl(i).transaction_type <> G_OPR_DELETE) THEN
BEGIN
SELECT lookup_code
INTO P_agdl_tbl(i).defaulting_name
FROM fnd_lookup_values
WHERE lookup_type = 'EGO_EF_AG_DL_BEHAVIOR'
AND meaning = P_agdl_tbl(i).defaulting_name
AND language = Userenv('LANG');
SELECT data_level_name
INTO P_agdl_tbl(i).data_level_name
FROM ego_data_level_b
WHERE attr_group_type = P_agdl_tbl(i).attr_group_type
AND application_id = g_ego_application_id
AND data_level_id = P_agdl_tbl(i).data_level_id;
SELECT Nvl(variant,'N')
INTO lv_flag
FROM ego_fnd_dsc_flx_ctx_ext
WHERE attr_group_id = P_agdl_tbl(i).attr_group_id;
SELECT Nvl(variant,'N')
INTO lv_flag
FROM ego_fnd_dsc_flx_ctx_ext
WHERE attr_group_id = P_agdl_tbl(i).attr_group_id;
SELECT Nvl(variant,'N')
INTO lv_flag
FROM ego_fnd_dsc_flx_ctx_ext
WHERE attr_group_id = P_agdl_tbl(i).attr_group_id;
SELECT Upper(P_attr_tbl(i).transaction_type)
INTO P_attr_tbl(i).transaction_type
FROM dual;
OR P_attr_tbl(i).transaction_type = G_OPR_UPDATE
OR P_attr_tbl(i).transaction_type = G_OPR_DELETE
OR P_attr_tbl(i).transaction_type = G_OPR_SYNC) THEN
NULL;
SELECT descriptive_flex_context_code
INTO P_attr_tbl(i).attr_group_name
FROM ego_fnd_dsc_flx_ctx_ext
WHERE application_id = g_ego_application_id
AND descriptive_flexfield_name = P_attr_tbl(i).attr_group_type
AND attr_group_id = P_attr_tbl(i).attr_group_id;
SELECT fdfcu.end_user_column_name
INTO P_attr_tbl(i).internal_name
FROM fnd_descr_flex_column_usages fdfcu,
ego_fnd_df_col_usgs_ext efdcue
WHERE fdfcu.application_id = efdcue.application_id
AND fdfcu.descriptive_flexfield_name = efdcue.descriptive_flexfield_name
AND fdfcu.descriptive_flex_context_code = efdcue.descriptive_flex_context_code
AND fdfcu.application_column_name = efdcue.application_column_name
AND fdfcu.application_id = g_ego_application_id
AND fdfcu.descriptive_flexfield_name = P_attr_tbl(i).attr_group_type
AND fdfcu.descriptive_flex_context_code = P_attr_tbl(i).attr_group_name
AND efdcue.attr_id = P_attr_tbl(i).attr_id;
P_attr_tbl(i).transaction_type := G_OPR_UPDATE;
SELECT ffvs.flex_value_set_name
INTO P_attr_tbl(i).flex_value_set_name
FROM fnd_flex_value_sets ffvs
WHERE ffvs.flex_value_set_id = P_attr_tbl(i).flex_value_set_id;
/*This procedure is used to update the attribute group interface table
Used in the interface flow.
p_ag_tbl IN OUT NOCOPY Attribute group plsql table
x_return_status OUT NOCOPY parameter that returns the status*/
PROCEDURE update_intf_attr_groups
(p_ag_tbl IN OUT NOCOPY ego_metadata_pub.ego_attr_groups_tbl,
x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
IS
lv_proc VARCHAR2(30) := 'update_intf_attr_groups';
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering update_intf_attr_groups');
UPDATE ego_attr_groups_interface
SET ROW = p_ag_tbl(i)
WHERE transaction_id = trans_id(i);
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit update_intf_attr_groups');
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'update_intf_attr_groups Exception when others'||SQLERRM);
x_return_msg := 'ego_ag_bulkload_pvt.update_intf_attr_groups - '||SQLERRM;
END update_intf_attr_groups;
/*This procedure is used to update the attribute group data level interface table
Used in the interface flow.
p_agdl_tbl IN OUT NOCOPY Attribute group data level plsql table
x_return_status OUT NOCOPY parameter that returns the status*/
PROCEDURE update_intf_data_level
(p_agdl_tbl IN OUT NOCOPY ego_metadata_pub.ego_attr_groups_dl_tbl,
x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
IS
lv_proc VARCHAR2(30) := 'update_intf_data_level';
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering update_intf_data_level');
UPDATE ego_attr_groups_dl_interface
SET ROW = p_agdl_tbl(i)
WHERE transaction_id = trans_id(i);
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit update_intf_data_level');
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'update_intf_data_level Exception when others'||SQLERRM);
x_return_msg := 'ego_ag_bulkload_pvt.update_intf_data_level - '||SQLERRM;
END update_intf_data_level;
/*This procedure is used to update the attributes interface table
Used in the Interface flow.
p_attr_tbl IN OUT NOCOPY Attributes plsql table
x_return_status OUT NOCOPY parameter that returns the status*/
PROCEDURE update_intf_attribute
(p_attr_tbl IN OUT NOCOPY ego_metadata_pub.ego_attr_group_cols_tbl,
x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
IS
lv_proc VARCHAR2(30) := 'update_intf_attribute';
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering update_intf_attribute');
UPDATE ego_attr_group_cols_intf
SET ROW = P_attr_tbl(i)
WHERE transaction_id = trans_id(i);
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit update_intf_attribute');
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'update_intf_attribute Exception when others'||SQLERRM);
x_return_msg := 'ego_ag_bulkload_pvt.update_intf_attribute - '||SQLERRM;
END update_intf_attribute;
/*This procedure is used to delete processed records from the attribute group's interface table
Used in the interface flow.
x_set_process_id IN Set process id
x_return_status OUT NOCOPY parameter that returns the status*/
PROCEDURE delete_processed_attr_groups(
x_set_process_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
IS
lv_proc VARCHAR2(30) := 'delete_processed_attr_groups';
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering delete_processed_attr_groups');
DELETE FROM ego_attr_groups_interface
WHERE process_status = G_SUCCESS_RECORD
AND (x_set_process_id IS NULL
OR set_process_id = x_set_process_id);
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit delete_processed_attr_groups');
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'delete_processed_attr_groups Exception when others'||SQLERRM);
x_return_msg := 'ego_ag_bulkload_pvt.delete_processed_attr_groups - '||SQLERRM;
END delete_processed_attr_groups;
/*This procedure is used to delete processed records from the AG Data level's interface table
Used in the interface flow.
x_set_process_id IN Set process id
x_return_status OUT NOCOPY parameter that returns the status*/
PROCEDURE delete_processed_data_level(
x_set_process_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
IS
lv_proc VARCHAR2(30) := 'delete_processed_data_level';
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering delete_processed_data_level');
DELETE FROM ego_attr_groups_dl_interface
WHERE process_status = G_SUCCESS_RECORD
AND (x_set_process_id IS NULL
OR set_process_id = x_set_process_id);
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit delete_processed_data_level');
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'delete_processed_data_level Exception when others'||SQLERRM);
x_return_msg := 'ego_ag_bulkload_pvt.delete_processed_data_level - '||SQLERRM;
END delete_processed_data_level;
/*This procedure is used to delete processed records from the Attribute's interface table
Used in the Interface flow.
x_set_process_id IN Set process id
x_return_status OUT NOCOPY parameter that returns the status*/
PROCEDURE delete_processed_attributes(
x_set_process_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
IS
lv_proc VARCHAR2(30) := 'delete_processed_attributes';
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Entering delete_processed_attributes');
DELETE FROM ego_attr_group_cols_intf
WHERE process_status = G_SUCCESS_RECORD
AND (x_set_process_id IS NULL
OR set_process_id = x_set_process_id);
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exit delete_processed_attributes');
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'delete_processed_attributes Exception when others'||SQLERRM);
x_return_msg := 'ego_ag_bulkload_pvt.delete_processed_attributes - '||SQLERRM;
END delete_processed_attributes;
/*This procedure is used in the update flow to handle null values for AG
Used in the interface and API flow.
p_ag_tbl IN OUT NOCOPY Attribute groups plsql table
x_return_status OUT NOCOPY parameter that returns the status
x_return_msg OUT NOCOPY parameter that returns the error message*/
PROCEDURE handle_null_ag(
p_ag_tbl IN OUT NOCOPY ego_metadata_pub.ego_attr_groups_tbl,
x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
IS
lv_display_name VARCHAR2(40); /*Added for bug 9738246*/
AND p_ag_tbl(i).transaction_type = G_OPR_UPDATE) THEN
BEGIN
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Smt 1');
SELECT NUM_OF_COLS, NUM_OF_ROWS, MULTI_ROW, VARIANT INTO lv_num_of_cols, lv_num_of_rows, lv_multi_row, lv_variant
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_ag_tbl(i).attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_ag_tbl(i).attr_group_name
AND APPLICATION_ID = G_EGO_APPLICATION_ID;
SELECT DESCRIPTIVE_FLEX_CONTEXT_NAME, DESCRIPTION INTO lv_display_name, lv_description
FROM FND_DESCR_FLEX_CONTEXTS_TL
WHERE APPLICATION_ID = G_EGO_APPLICATION_ID
AND DESCRIPTIVE_FLEXFIELD_NAME = p_ag_tbl(i).attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_ag_tbl(i).attr_group_name
AND USERENV('LANG') = LANGUAGE;
AND p_agdl_tbl(i).transaction_type = G_OPR_UPDATE) THEN
BEGIN
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Smt 1');
SELECT defaulting ,view_privilege_id,edit_privilege_id,raise_pre_event,raise_post_event
INTO lv_defaulting, lv_view_privilege_id ,lv_edit_privilege_id,lv_raise_pre_event, lv_raise_post_event
FROM EGO_ATTR_GROUP_DL
WHERE attr_group_id = p_agdl_tbl(i).attr_group_id
AND data_level_id = p_agdl_tbl(i).data_level_id;
AND p_attr_tbl(i).transaction_type = G_OPR_UPDATE) THEN
BEGIN
SELECT FLEX_VALUE_SET_ID,DEFAULT_VALUE INTO lv_value_set_id, lv_default_value
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = G_EGO_APPLICATION_ID
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_tbl(i).attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_tbl(i).attr_group_name
AND APPLICATION_COLUMN_NAME = p_attr_tbl(i).application_column_name;
SELECT INFO_1, UOM_CLASS INTO lv_info_1, lv_uom_class
FROM EGO_FND_DF_COL_USGS_EXT
WHERE APPLICATION_ID = G_EGO_APPLICATION_ID
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_tbl(i).attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_tbl(i).attr_group_name
AND APPLICATION_COLUMN_NAME = p_attr_tbl(i).application_column_name;
/*This procedure is used to delete AG existing in the production table without a single DL associated
Used in the Interface flow and API flow.
x_return_status OUT NOCOPY parameter that returns the status
x_return_msg OUT NOCOPY parameter that returns the error message*/
PROCEDURE delete_ag_none_dl(
x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
IS
CURSOR cur_ag_none_dl IS
SELECT attr_group_id
FROM ego_fnd_dsc_flx_ctx_ext efd
WHERE application_id = G_EGO_APPLICATION_ID
AND DESCRIPTIVE_FLEXFIELD_NAME = G_EGO_ITEMMGMT_GROUP
AND NOT EXISTS (SELECT 1
FROM EGO_ATTR_GROUP_DL
WHERE attr_group_id = efd.attr_group_id);
lv_proc VARCHAR2(30) := 'delete_ag_none_dl';
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Enters delete_ag_none_dl');
EGO_EXT_FWK_PUB.Delete_Attribute_Group (
p_api_version => 1.0
,p_attr_group_id => rec.attr_group_id
,p_init_msg_list => fnd_api.g_FALSE
,p_commit => fnd_api.g_FALSE
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'Exits delete_ag_none_dl');
write_debug('ego_ag_bulkload_pvt.'||lv_proc||' - '||'delete_ag_none_dl Exception when others'||SQLERRM);
x_return_msg := 'ego_ag_bulkload_pvt.delete_ag_none_dl - '||SQLERRM;
END delete_ag_none_dl;
SELECT EXT_TL_TABLE_NAME
INTO l_table_name
FROM EGO_ATTR_GROUP_TYPES_V
WHERE APPLICATION_ID = p_application_id
AND ATTR_GROUP_TYPE = p_attr_group_type;
SELECT APPLICATION_TABLE_NAME
INTO l_table_name
FROM FND_DESCRIPTIVE_FLEXS
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type;
l_dynamic_sql:='SELECT CHANGE_TL_TABLE_NAME'||
' FROM ENG_PENDING_CHANGE_CTX'||
' WHERE APPLICATION_ID = :1'||--p_application_id
' AND CHANGE_ATTRIBUTE_GROUP_TYPE =:2' ;--p_attr_group_type;
l_dynamic_sql:='SELECT CHANGE_B_TABLE_NAME'||
' FROM ENG_PENDING_CHANGE_CTX'||
' WHERE APPLICATION_ID = :1'||--p_application_id
' AND CHANGE_ATTRIBUTE_GROUP_TYPE =:2' ;--p_attr_group_type;