[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';
SELECT object_id
INTO G_OBJECT_ID
FROM fnd_objects
WHERE obj_name = 'EGO_ITEM';
UPDATE ego_pages_interface
SET transaction_id = mtl_system_items_interface_s.nextval,
transaction_type = Upper(transaction_type),
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_PAGES_COUNT
FROM ego_pages_interface
WHERE process_status = G_PROCESS_RECORD
AND ( ( G_SET_PROCESS_ID IS NULL )
OR ( set_process_id = G_SET_PROCESS_ID ) );
UPDATE ego_page_entries_interface
SET transaction_id = mtl_system_items_interface_s.nextval,
transaction_type = Upper(transaction_type),
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_PAGE_ENTRIES_COUNT
FROM ego_page_entries_interface
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_PG_TAB,
G_BO_IDENTIFIER_PG,
G_ENTITY_PG,
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_pages_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_pages_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_ENT_TAB,
G_BO_IDENTIFIER_PG,
G_ENTITY_ENT,
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_page_entries_interface
WHERE (transaction_type IS NULL
OR transaction_type NOT IN ( G_OPR_CREATE, G_OPR_UPDATE, G_OPR_SYNC, G_OPR_DELETE ))
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_page_entries_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_SYNC, G_OPR_DELETE ))
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_pages_interface epi
SET classification_code = EGO_ICC_BULKLOAD_PVT.Get_Catalog_Group_Id(classification_name,'FIND_COMBINATION'),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE classification_name IS NOT NULL
AND classification_code IS NULL
AND process_status = G_PROCESS_RECORD
AND ( ( G_SET_PROCESS_ID IS NULL )
OR ( set_process_id = G_SET_PROCESS_ID ) );
/*Get the page ID and set SYNC to UPDATE*/
lv_smt := 2;
UPDATE ego_pages_interface epi
SET page_id = (SELECT page_id
FROM ego_pages_b
WHERE object_id = G_OBJECT_ID
AND internal_name = epi.internal_name
AND classification_code = epi.classification_code),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE transaction_type <> G_OPR_CREATE
AND ( ( page_id IS NULL
AND internal_name IS NOT NULL
AND classification_code IS NOT NULL )
OR page_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 ) );
/*Set SYNC to CREATE and UPDATE*/
lv_smt := 3;
UPDATE ego_pages_interface epi
SET transaction_type = decode(page_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 transaction_type = G_OPR_SYNC
AND process_status = G_PROCESS_RECORD
AND ( ( G_SET_PROCESS_ID IS NULL )
OR ( set_process_id = G_SET_PROCESS_ID ) );
UPDATE ego_page_entries_interface epi
SET classification_code = EGO_ICC_BULKLOAD_PVT.Get_Catalog_Group_Id(classification_name,'FIND_COMBINATION'),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE classification_name IS NOT NULL
AND classification_code 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_page_entries_interface epi
SET page_id = (SELECT page_id
FROM ego_pages_b
WHERE object_id = G_OBJECT_ID
AND internal_name = epi.internal_name
AND classification_code IN ( SELECT PARENT_CATALOG_GROUP_ID FROM EGO_ITEM_CAT_DENORM_HIER
WHERE CHILD_CATALOG_GROUP_ID = epi.classification_code)),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE page_id IS NULL
AND internal_name IS NOT NULL
AND classification_code 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_page_entries_interface epi
SET old_attr_group_id = (SELECT attr_group_id
FROM ego_fnd_dsc_flx_ctx_ext
WHERE application_id = (SELECT application_id
FROM fnd_application
WHERE application_short_name = 'EGO')
AND descriptive_flexfield_name IN ('EGO_ITEMMGMT_GROUP','EGO_MASTER_ITEMS') /*Added EGO_MASTER_ITEMS for bug 9950697*/
AND descriptive_flex_context_code = epi.old_attr_group_name),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE old_attr_group_id IS NULL
AND old_attr_group_name 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_page_entries_interface epi
SET new_attr_group_id = (SELECT attr_group_id
FROM ego_fnd_dsc_flx_ctx_ext
WHERE application_id = (SELECT application_id
FROM fnd_application
WHERE application_short_name = 'EGO')
AND descriptive_flexfield_name IN ('EGO_ITEMMGMT_GROUP','EGO_MASTER_ITEMS') /*Added EGO_MASTER_ITEMS for bug 9950697*/
AND descriptive_flex_context_code = epi.new_attr_group_name),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE new_attr_group_id IS NULL
AND new_attr_group_name 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_page_entries_interface epi
SET old_association_id = (SELECT association_id
FROM EGO_OBJ_AG_ASSOCS_B
WHERE classification_code IN ( SELECT PARENT_CATALOG_GROUP_ID
FROM EGO_ITEM_CAT_DENORM_HIER
WHERE CHILD_CATALOG_GROUP_ID = epi.classification_code)
AND attr_group_id = epi.old_attr_group_id
AND data_level = (SELECT data_level
FROM ego_pages_b
WHERE page_id = epi.page_id)),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE old_association_id IS NULL
AND old_attr_group_id IS NOT NULL
AND classification_code 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_page_entries_interface epi
SET new_association_id = (SELECT association_id
FROM EGO_OBJ_AG_ASSOCS_B
WHERE classification_code IN ( SELECT PARENT_CATALOG_GROUP_ID
FROM EGO_ITEM_CAT_DENORM_HIER
WHERE CHILD_CATALOG_GROUP_ID = epi.classification_code)
AND attr_group_id = epi.new_attr_group_id
AND data_level = (SELECT data_level
FROM ego_pages_b
WHERE page_id = epi.page_id)),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE new_association_id IS NULL
AND new_attr_group_id IS NOT NULL
AND classification_code IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ( ( G_SET_PROCESS_ID IS NULL )
OR ( set_process_id = G_SET_PROCESS_ID ) );
/*Sets the SYNC to UPDATE*/
lv_smt := 7;
UPDATE ego_page_entries_interface epi
SET transaction_type = G_OPR_UPDATE,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE EXISTS (SELECT 1
FROM ego_page_entries_b
WHERE page_id = epi.page_id
AND association_id = epi.old_association_id)
AND transaction_type = G_OPR_SYNC
AND process_status = G_PROCESS_RECORD
AND ( ( G_SET_PROCESS_ID IS NULL )
OR ( set_process_id = G_SET_PROCESS_ID ) );
UPDATE ego_page_entries_interface epi
SET transaction_type = G_OPR_CREATE,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE transaction_type = G_OPR_SYNC
AND process_status = G_PROCESS_RECORD
AND ( ( G_SET_PROCESS_ID IS NULL )
OR ( set_process_id = G_SET_PROCESS_ID ) );
UPDATE ego_pages_interface epi
SET (internal_name, classification_code) = (SELECT internal_name, classification_code
FROM ego_pages_v
WHERE page_id = epi.page_id),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE page_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_pages_interface epi
SET classification_name = (SELECT icc_kfv.concatenated_segments
FROM mtl_item_catalog_groups_kfv icc_kfv
WHERE icc_kfv.item_catalog_group_id = epi.classification_code),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE classification_code 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_page_entries_interface epi
SET ( internal_name ) = (SELECT internal_name
FROM ego_pages_b
WHERE page_id = epi.page_id),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE page_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_page_entries_interface epi
SET classification_name = (SELECT icc_kfv.concatenated_segments
FROM mtl_item_catalog_groups_kfv icc_kfv
WHERE icc_kfv.item_catalog_group_id = epi.classification_code),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE classification_code 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_page_entries_interface epi
SET ( old_attr_group_id, old_attr_group_name ) = (SELECT attr_group_id,
attr_group_name
FROM ego_obj_attr_grp_assocs_v
WHERE association_id = epi.old_association_id),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE old_association_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_page_entries_interface epi
SET ( new_attr_group_id, new_attr_group_name ) = (SELECT attr_group_id,
attr_group_name
FROM ego_obj_attr_grp_assocs_v
WHERE association_id = epi.new_association_id),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE new_association_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_page_entries_interface epi
SET ( old_association_id, old_attr_group_name ) = (SELECT association_id,
attr_group_name
FROM ego_obj_attr_grp_assocs_v
WHERE attr_group_id = epi.old_attr_group_id
AND classification_code = epi.classification_code),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE old_attr_group_id IS NOT NULL
AND old_attr_group_name IS NULL
AND old_association_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_page_entries_interface epi
SET ( new_association_id, new_attr_group_name ) = (SELECT association_id,
attr_group_name
FROM ego_obj_attr_grp_assocs_v
WHERE attr_group_id = epi.new_attr_group_id),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE new_attr_group_id IS NOT NULL
AND new_attr_group_name IS NULL
AND new_association_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 internal_name, classification_code INTO p_pg_tbl(i).internal_name, p_pg_tbl(i).classification_code
FROM ego_pages_v
WHERE page_id = p_pg_tbl(i).page_id;
SELECT icc_kfv.concatenated_segments INTO p_pg_tbl(i).classification_name
FROM mtl_item_catalog_groups_kfv icc_kfv
WHERE icc_kfv.item_catalog_group_id = p_pg_tbl(i).classification_code;
SELECT internal_name, classification_code INTO p_ent_tbl(i).internal_name, p_ent_tbl(i).classification_code
FROM ego_pages_v
WHERE page_id = p_ent_tbl(i).page_id;
SELECT icc_kfv.concatenated_segments INTO p_ent_tbl(i).classification_name
FROM mtl_item_catalog_groups_kfv icc_kfv
WHERE icc_kfv.item_catalog_group_id = p_ent_tbl(i).classification_code;
SELECT attr_group_id,attr_group_name INTO p_ent_tbl(i).old_attr_group_id, p_ent_tbl(i).old_attr_group_name
FROM ego_obj_attr_grp_assocs_v
WHERE association_id = p_ent_tbl(i).old_association_id;
SELECT attr_group_id,attr_group_name INTO p_ent_tbl(i).new_attr_group_id, p_ent_tbl(i).new_attr_group_name
FROM ego_obj_attr_grp_assocs_v
WHERE association_id = p_ent_tbl(i).new_association_id;
SELECT association_id,attr_group_name INTO p_ent_tbl(i).old_association_id, p_ent_tbl(i).old_attr_group_name
FROM ego_obj_attr_grp_assocs_v
WHERE attr_group_id = p_ent_tbl(i).old_attr_group_id
AND classification_code = p_ent_tbl(i).classification_code;
SELECT association_id,attr_group_name INTO p_ent_tbl(i).new_association_id, p_ent_tbl(i).new_attr_group_name
FROM ego_obj_attr_grp_assocs_v
WHERE attr_group_id = p_ent_tbl(i).new_attr_group_id
AND classification_code = p_ent_tbl(i).classification_code;
/*Get the page ID and set SYNC to UPDATE*/
IF (p_pg_tbl(i).transaction_type <> G_OPR_CREATE) THEN
IF (p_pg_tbl(i).page_id IS NULL AND p_pg_tbl(i).internal_name IS NOT NULL AND p_pg_tbl(i).classification_code IS NOT NULL) THEN
BEGIN
SELECT page_id INTO p_pg_tbl(i).page_id
FROM ego_pages_b
WHERE object_id = G_OBJECT_ID
AND internal_name = p_pg_tbl(i).internal_name
AND classification_code = p_pg_tbl(i).classification_code;
p_pg_tbl(i).transaction_type := G_OPR_UPDATE;
SELECT page_id INTO p_ent_tbl(i).page_id
FROM ego_pages_b
WHERE object_id = G_OBJECT_ID
AND internal_name = p_ent_tbl(i).internal_name
AND classification_code = p_ent_tbl(i).classification_code;
SELECT attr_group_id INTO p_ent_tbl(i).old_attr_group_id
FROM ego_fnd_dsc_flx_ctx_ext
WHERE application_id = (SELECT application_id
FROM fnd_application
WHERE application_short_name = 'EGO')
AND descriptive_flexfield_name IN ('EGO_ITEMMGMT_GROUP','EGO_MASTER_ITEMS') /*Added EGO_MASTER_ITEMS for bug 9950697*/
AND descriptive_flex_context_code = p_ent_tbl(i).old_attr_group_name;
SELECT attr_group_id INTO p_ent_tbl(i).new_attr_group_id
FROM ego_fnd_dsc_flx_ctx_ext
WHERE application_id = (SELECT application_id
FROM fnd_application
WHERE application_short_name = 'EGO')
AND descriptive_flexfield_name IN ('EGO_ITEMMGMT_GROUP','EGO_MASTER_ITEMS') /*Added EGO_MASTER_ITEMS for bug 9950697*/
AND descriptive_flex_context_code = p_ent_tbl(i).new_attr_group_name;
SELECT association_id INTO p_ent_tbl(i).old_association_id
FROM EGO_OBJ_AG_ASSOCS_B
WHERE classification_code = p_ent_tbl(i).classification_code
AND attr_group_id = p_ent_tbl(i).old_attr_group_id;
SELECT association_id INTO p_ent_tbl(i).new_association_id
FROM EGO_OBJ_AG_ASSOCS_B
WHERE classification_code = p_ent_tbl(i).classification_code
AND attr_group_id = p_ent_tbl(i).new_attr_group_id;
/*Sets the SYNC to CREATE or UPDATE*/
IF (p_ent_tbl(i).transaction_type = G_OPR_SYNC) THEN
BEGIN
SELECT 'Y' INTO lv_flag
FROM ego_page_entries_b
WHERE page_id = p_ent_tbl(i).page_id
AND association_id = p_ent_tbl(i).old_association_id;
p_ent_tbl(i).transaction_type := G_OPR_UPDATE;
p_ent_tbl(i).transaction_type := G_OPR_DELETE;
/* Update the interface records with process_status 3 and insert into
MTL_INTERFACE_ERRORS if the ICC name passed is invalid*/
G_MESSAGE_NAME := 'EGO_ICC_ID_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_PG_TAB,
G_BO_IDENTIFIER_PG,
G_ENTITY_PG,
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_pages_interface epi
WHERE (classification_code IS NULL OR classification_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_pages_interface
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE (classification_code IS NULL OR classification_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 page does not exist for update and delete flow*/
G_MESSAGE_NAME := 'EGO_EF_ATTR_PAGE_NOT_FOUND';
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_PG_TAB,
G_BO_IDENTIFIER_PG,
G_ENTITY_PG,
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_pages_interface epi
WHERE (page_id IS NULL OR internal_name IS NULL)
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_pages_interface
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE (page_id IS NULL OR internal_name IS NULL)
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 the page with the internal name already exists in the system*/
G_MESSAGE_NAME := 'EGO_INTERNAL_NAME_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_PG_TAB,
G_BO_IDENTIFIER_PG,
G_ENTITY_PG,
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_pages_interface epi
WHERE EXISTS (SELECT 1
FROM EGO_PAGES_B
WHERE internal_name = epi.internal_name
AND classification_code = epi.classification_code)
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_pages_interface epi
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 EGO_PAGES_B
WHERE internal_name = epi.internal_name
AND classification_code = epi.classification_code)
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 SEQUENCE IS already existing for the ICC*/
G_MESSAGE_NAME := 'EGO_PG_SEQ_DUP';
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_PG_TAB,
G_BO_IDENTIFIER_PG,
G_ENTITY_PG,
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_pages_interface epi
WHERE EXISTS (SELECT 1
FROM ego_pages_b
WHERE classification_code = epi.classification_code
AND SEQUENCE = epi.SEQUENCE
AND page_id <> nvl(epi.page_id,-1))
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_pages_interface epi
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 ego_pages_b
WHERE classification_code = epi.classification_code
AND SEQUENCE = epi.SEQUENCE
AND page_id <> nvl(epi.page_id,-1))
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 flow is UPDATE or DELETE and the page entry does not exist*/
G_MESSAGE_NAME := 'EGO_PG_ENT_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_ENT_TAB,
G_BO_IDENTIFIER_PG,
G_ENTITY_ENT,
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_page_entries_interface epei
WHERE NOT EXISTS (SELECT 1
FROM ego_page_entries_b
WHERE page_id = epei.page_id
AND association_id = epei.old_association_id
AND classification_code = epei.classification_code)
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_page_entries_interface epi
SET 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_page_entries_b
WHERE page_id = epi.page_id
AND association_id = epi.old_association_id
AND classification_code = epi.classification_code)
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 SEQ already exists in the page*/
G_MESSAGE_NAME := 'EGO_PAGE_ENTRY_SAME_SEQ';
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_ENT_TAB,
G_BO_IDENTIFIER_PG,
G_ENTITY_ENT,
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_page_entries_interface epi
WHERE EXISTS (SELECT 1
FROM ego_page_entries_b
WHERE page_id = epi.page_id
AND sequence = epi.sequence)
AND transaction_id IS NOT NULL
AND transaction_type = G_OPR_CREATE /*Added this for bug 9733398*/
AND process_status = G_PROCESS_RECORD
AND ( ( G_SET_PROCESS_ID IS NULL )
OR ( set_process_id = G_SET_PROCESS_ID ) );
UPDATE ego_page_entries_interface epi
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 ego_page_entries_b
WHERE page_id = epi.page_id
AND sequence = epi.sequence)
AND transaction_id IS NOT NULL
AND transaction_type = G_OPR_CREATE /*Added this for bug 9733398*/
AND process_status = G_PROCESS_RECORD
AND ( ( G_SET_PROCESS_ID IS NULL )
OR ( set_process_id = G_SET_PROCESS_ID ) );
/*For the update or delete flow if the page is not existing in the system then Error out*/
IF(p_pg_tbl(i).transaction_type <> G_OPR_CREATE AND p_pg_tbl(i).page_id IS NULL) THEN
x_return_status := G_RET_STS_ERROR;
SELECT 'Y' INTO lv_flag
FROM ego_pages_b
WHERE classification_code = p_pg_tbl(i).classification_code
AND SEQUENCE = p_pg_tbl(i).SEQUENCE
AND page_id <> NVL(p_pg_tbl(i).page_id,-1);
SELECT Upper(p_pg_tbl(i).transaction_type)
INTO p_pg_tbl(i).transaction_type
FROM dual;
OR p_pg_tbl(i).transaction_type = G_OPR_UPDATE
OR p_pg_tbl(i).transaction_type = G_OPR_DELETE
OR p_pg_tbl(i).transaction_type = G_OPR_SYNC) THEN
NULL;
/*Error out if flow is UPDATE or DELETE and the page entry does not exist*/
IF (p_ent_tbl(i).transaction_type <> G_OPR_CREATE) THEN
BEGIN
SELECT 'Y' INTO lv_flag
FROM ego_page_entries_b
WHERE page_id = p_ent_tbl(i).page_id
AND association_id = p_ent_tbl(i).old_association_id
AND classification_code = p_ent_tbl(i).classification_code;
SELECT 'Y' INTO lv_flag
FROM ego_page_entries_b
WHERE page_id = p_ent_tbl(i).page_id
AND sequence = p_ent_tbl(i).sequence;
SELECT Upper(p_ent_tbl(i).transaction_type)
INTO p_ent_tbl(i).transaction_type
FROM dual;
OR p_ent_tbl(i).transaction_type = G_OPR_UPDATE
OR p_ent_tbl(i).transaction_type = G_OPR_DELETE
OR p_ent_tbl(i).transaction_type = G_OPR_SYNC) THEN
NULL;
ELSIF (p_pg_tbl(i).transaction_type <> G_OPR_DELETE) THEN
write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - Err_msg_TID: '||p_pg_tbl(i).transaction_id||' (PG,DL) = ('
||p_pg_tbl(i).internal_name||','||p_pg_tbl(i).data_level||'). ' ||'Data Level passed is invalid');
SELECT COUNT(1) INTO lv_count
FROM EGO_PAGE_ENTRIES_B
WHERE page_id = p_ent_tbl(i).page_id
AND association_id = p_ent_tbl(i).old_association_id;
SELECT *
FROM ego_pages_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_page_entries_interface
WHERE process_status = G_PROCESS_RECORD
AND (p_set_process_id IS NULL
OR set_process_id = p_set_process_id);
update_intf_pages(p_pg_tbl => l_ego_pg_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_pg_entries(p_ent_tbl => l_ego_ent_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 object_id
INTO G_OBJECT_ID
FROM fnd_objects
WHERE obj_name = 'EGO_ITEM';
SELECT object_id
INTO G_OBJECT_ID
FROM fnd_objects
WHERE obj_name = 'EGO_ITEM';
SELECT count(1) INTO lv_count_pg
FROM EGO_PAGES_B
WHERE internal_name = P_pg_tbl(i).internal_name
AND classification_code = P_pg_tbl(i).classification_code;
SELECT count(1) INTO lv_count_seq
FROM ego_pages_b
WHERE classification_code = P_pg_tbl(i).classification_code
AND SEQUENCE = P_pg_tbl(i).SEQUENCE;
G_TOKEN_TABLE.DELETE;
AND P_pg_tbl(i).transaction_type = G_OPR_UPDATE ) THEN
write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Update Page');
ego_ext_fwk_pub.Update_page ( p_api_version => 1.0,
p_page_id => P_pg_tbl(i).page_id,
p_internal_name => P_pg_tbl(i).internal_name,
p_display_name => P_pg_tbl(i).display_name,
p_description => P_pg_tbl(i).description,
p_sequence => P_pg_tbl(i).SEQUENCE,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_is_nls_mode => 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_page';
G_TOKEN_TABLE.DELETE;
AND P_pg_tbl(i).transaction_type = G_OPR_DELETE ) THEN
write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Delete Page');
ego_ext_fwk_pub.Delete_page ( p_api_version => 1.0,
p_object_id => G_OBJECT_ID,
p_classification_code => P_pg_tbl(i).classification_code,
p_internal_name => P_pg_tbl(i).internal_name,
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);
G_TOKEN_TABLE(4).Token_Value := 'Delete_page';
G_TOKEN_TABLE.DELETE;
SELECT count(1) INTO lv_count_ag
FROM EGO_PAGE_ENTRIES_B
WHERE page_id = P_ent_tbl(i).page_id
AND classification_code = P_ent_tbl(i).classification_code
AND association_id = P_ent_tbl(i).old_association_id;
SELECT count(1) INTO lv_count_seq
FROM ego_page_entries_b
WHERE page_id = P_ent_tbl(i).page_id
AND sequence = P_ent_tbl(i).sequence;
G_TOKEN_TABLE.DELETE;
AND P_ent_tbl(i).transaction_type = G_OPR_UPDATE ) THEN
write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Update Page Entity');
SELECT count(1) INTO lv_count_seq
FROM ego_page_entries_b
WHERE page_id = P_ent_tbl(i).page_id
AND sequence = P_ent_tbl(i).sequence
AND association_id <> P_ent_tbl(i).old_association_id;
ego_ext_fwk_pub.Update_page_entry ( p_api_version => 1.0,
p_page_id => P_ent_tbl(i).page_id,
p_new_association_id => NVL(P_ent_tbl(i).new_association_id,P_ent_tbl(i).old_association_id),
p_old_association_id => P_ent_tbl(i).old_association_id,
p_sequence => P_ent_tbl(i).SEQUENCE,
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);
G_TOKEN_TABLE(4).Token_Value := 'Update_page_entry';
G_TOKEN_TABLE.DELETE;
AND P_ent_tbl(i).transaction_type = G_OPR_DELETE ) THEN
write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Delete Page Entity');
ego_ext_fwk_pub.Delete_page_entry ( p_api_version => 1.0,
p_page_id => P_ent_tbl(i).page_id,
p_association_id => P_ent_tbl(i).old_association_id,
p_classification_code => P_ent_tbl(i).classification_code,
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);
G_TOKEN_TABLE(4).Token_Value := 'Delete_page_entry';
G_TOKEN_TABLE.DELETE;
/*This procedure is used to update the Pages interface table.
Used in the interface flow.
p_pg_tbl IN OUT NOCOPY Pages table
x_return_status OUT NOCOPY parameter that returns the status
x_return_msg OUT NOCOPY parameter that returns the error message*/
PROCEDURE update_intf_pages (p_pg_tbl IN OUT NOCOPY ego_metadata_pub.ego_pg_tbl,
x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
IS
lv_proc VARCHAR2(30) := 'update_intf_pages';
write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering update_intf_pages');
UPDATE ego_pages_interface
SET ROW = P_pg_tbl(i)
WHERE transaction_id = trans_id(i);
write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit update_intf_pages');
write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'update_intf_pages Exception when others'||SQLERRM);
x_return_msg := 'ego_pages_bulkload_pvt.update_intf_pages - '||SQLERRM;
END update_intf_pages;
/*This procedure is used to update the page entries interface table.
Used in the interface flow.
p_ent_tbl IN OUT NOCOPY Page Entries table
x_return_status OUT NOCOPY parameter that returns the status
x_return_msg OUT NOCOPY parameter that returns the error message*/
PROCEDURE update_intf_pg_entries (p_ent_tbl IN OUT NOCOPY ego_metadata_pub.ego_ent_tbl,
x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
IS
lv_proc VARCHAR2(30) := 'update_intf_pg_entries';
write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering update_intf_pg_entries');
UPDATE ego_page_entries_interface
SET ROW = P_ent_tbl(i)
WHERE transaction_id = trans_id(i);
write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Exit update_intf_pg_entries');
write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'update_intf_pg_entries Exception when others'||SQLERRM);
x_return_msg := 'ego_pages_bulkload_pvt.update_intf_pg_entries - '||SQLERRM;
END update_intf_pg_entries;
/*This procedure is used to delete processed records from the pages interface
Used in the interface flow.
x_set_process_id IN Set Process ID
x_return_status OUT NOCOPY parameter that returns the status
x_return_msg OUT NOCOPY parameter that returns the error message*/
PROCEDURE delete_processed_pages(
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_pages';
write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering delete_processed_pages');
DELETE FROM ego_pages_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_pages_bulkload_pvt.'||lv_proc||' - '||'Exit delete_processed_pages');
write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'delete_processed_pages Exception when others'||SQLERRM);
x_return_msg := 'ego_pages_bulkload_pvt.delete_processed_pages - '||SQLERRM;
END delete_processed_pages;
/*This procedure is used to deleted processed records from the page entries interface
Used in the interface flow.
x_set_process_id IN Set Process ID
x_return_status OUT NOCOPY parameter that returns the status
x_return_msg OUT NOCOPY parameter that returns the error message*/
PROCEDURE delete_processed_pg_entries(
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_pg_entries';
write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Entering delete_processed_pg_entries');
DELETE FROM ego_page_entries_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_pages_bulkload_pvt.'||lv_proc||' - '||'Exit delete_processed_pg_entries');
write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'delete_processed_pg_entries Exception when others'||SQLERRM);
x_return_msg := 'ego_pages_bulkload_pvt.delete_processed_pg_entries - '||SQLERRM;
END delete_processed_pg_entries;
/*This procedure is used in the update flow to handle null values for PG
Used in the interface and API flow.
p_pg_tbl IN OUT NOCOPY Pages 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_pg(
p_pg_tbl IN OUT NOCOPY ego_metadata_pub.ego_pg_tbl,
x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
IS
lv_sequence NUMBER;
AND p_pg_tbl(i).transaction_type = G_OPR_UPDATE) THEN
BEGIN
write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Smt 1');
SELECT sequence INTO lv_sequence
FROM EGO_PAGES_B
WHERE page_id = p_pg_tbl(i).page_id;
SELECT display_name, description INTO lv_display_name, lv_description
FROM EGO_PAGES_TL
WHERE page_id = p_pg_tbl(i).page_id
AND USERENV('LANG') = LANGUAGE;
/*This procedure is used in the update flow to handle null values for PG Entries
Used in the interface and API flow.
p_ent_tbl IN OUT NOCOPY Page Entries 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_pg_entries(
p_ent_tbl IN OUT NOCOPY ego_metadata_pub.ego_ent_tbl,
x_return_status OUT NOCOPY VARCHAR2, x_return_msg OUT NOCOPY VARCHAR2)
IS
lv_sequence NUMBER;
AND p_ent_tbl(i).transaction_type = G_OPR_UPDATE) THEN
BEGIN
write_debug('ego_pages_bulkload_pvt.'||lv_proc||' - '||'Smt 1');
SELECT sequence INTO lv_sequence
FROM EGO_PAGE_ENTRIES_B
WHERE page_id = p_ent_tbl(i).page_id
AND association_id = p_ent_tbl(i).old_association_id;