[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT object_id
FROM fnd_objects
WHERE obj_name = G_ITEM_OBJ_NAME;
INSERT INTO MTL_INTERFACE_ERRORS
(
TRANSACTION_ID
,UNIQUE_ID
,ORGANIZATION_ID
,COLUMN_NAME
,TABLE_NAME
,MESSAGE_NAME
,ERROR_MESSAGE
,bo_identifier
,ENTITY_IDENTIFIER
,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_ICC_HEADER_TAB
,l_msg_name
,l_msg_text
,G_BO_IDENTIFIER_ICC
,G_ENTITY_ICC_HEADER
,l_sysdate
,G_USER_ID
,l_sysdate
,G_USER_ID
,G_LOGIN_ID
,G_CONC_REQUEST_ID
,G_PROG_APPL_ID
,G_PROGRAM_ID
,l_sysdate
FROM MTL_ITEM_CAT_GRPS_INTERFACE MICGI
WHERE ( (MICGI.transaction_type IS NULL )
OR
(MICGI.transaction_type NOT IN ( G_TTYPE_CREATE , G_TTYPE_UPDATE
,G_TTYPE_SYNC)
)
)
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( MICGI.set_process_id = G_SET_PROCESS_ID)
)
AND process_status = G_PROCESS_STATUS_INITIAL
;
UPDATE MTL_ITEM_CAT_GRPS_INTERFACE MICGI
SET micgi.process_status = G_PROCESS_STATUS_ERROR
WHERE ( micgi.transaction_type IS NULL OR
micgi.transaction_type NOT IN ( G_TTYPE_CREATE , G_TTYPE_UPDATE
,G_TTYPE_SYNC)
)
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( MICGI.set_process_id = G_SET_PROCESS_ID)
)
AND MICGI.process_status = G_PROCESS_STATUS_INITIAL
;
INSERT INTO MTL_INTERFACE_ERRORS
(
TRANSACTION_ID
,UNIQUE_ID
,ORGANIZATION_ID
,COLUMN_NAME
,TABLE_NAME
,MESSAGE_NAME
,ERROR_MESSAGE
,bo_identifier
,ENTITY_IDENTIFIER
,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_ICC_HEADER_TAB
,l_msg_name
,l_msg_text
,G_BO_IDENTIFIER_ICC
,G_ENTITY_ICC_AG_ASSOC
,l_sysdate
,G_USER_ID
,l_sysdate
,G_USER_ID
,G_LOGIN_ID
,G_CONC_REQUEST_ID
,G_PROG_APPL_ID
,G_PROGRAM_ID
,l_sysdate
FROM EGO_ATTR_GRPS_ASSOC_INTERFACE eagai
WHERE ( eagai.transaction_type IS NULL OR
eagai.transaction_type NOT IN ( G_TTYPE_CREATE
,G_TTYPE_DELETE)
)
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( EAGAI.set_process_id = G_SET_PROCESS_ID)
)
AND process_status = G_PROCESS_STATUS_INITIAL
;
UPDATE EGO_ATTR_GRPS_ASSOC_INTERFACE eagai
SET eagai.process_status = G_PROCESS_STATUS_ERROR
WHERE ( eagai.transaction_type IS NULL OR
eagai.transaction_type NOT IN ( G_TTYPE_CREATE
, G_TTYPE_DELETE)
)
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( eagai.set_process_id = G_SET_PROCESS_ID)
)
AND eagai.process_status = G_PROCESS_STATUS_INITIAL
;
INSERT INTO MTL_INTERFACE_ERRORS
(
TRANSACTION_ID
,UNIQUE_ID
,ORGANIZATION_ID
,COLUMN_NAME
,TABLE_NAME
,MESSAGE_NAME
,ERROR_MESSAGE
,bo_identifier
,ENTITY_IDENTIFIER
,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_FUNC_PARAM_MAP_TAB
,l_msg_name
,l_msg_text
,G_BO_IDENTIFIER_ICC
,G_ENTITY_ICC_FN_PARAM_MAP
,l_sysdate
,G_USER_ID
,l_sysdate
,G_USER_ID
,G_LOGIN_ID
,G_CONC_REQUEST_ID
,G_PROG_APPL_ID
,G_PROGRAM_ID
,l_sysdate
FROM EGO_FUNC_PARAMS_MAP_INTERFACE efpmi
WHERE ( efpmi.transaction_type IS NULL OR
efpmi.transaction_type NOT IN ( G_TTYPE_CREATE , G_TTYPE_UPDATE ,
G_TTYPE_SYNC , G_TTYPE_DELETE)
)
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( EFPMI.set_process_id = G_SET_PROCESS_ID)
)
AND process_status = G_PROCESS_STATUS_INITIAL
;
UPDATE EGO_FUNC_PARAMS_MAP_INTERFACE efpmi
SET efpmi.process_status = G_PROCESS_STATUS_ERROR
WHERE ( efpmi.transaction_type IS NULL OR
efpmi.transaction_type NOT IN ( G_TTYPE_CREATE , G_TTYPE_UPDATE
,G_TTYPE_SYNC, G_TTYPE_DELETE)
)
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( efpmi.set_process_id = G_SET_PROCESS_ID)
)
AND efpmi.process_status = G_PROCESS_STATUS_INITIAL
;
INSERT INTO MTL_INTERFACE_ERRORS
(
TRANSACTION_ID
,UNIQUE_ID
,ORGANIZATION_ID
,COLUMN_NAME
,TABLE_NAME
,MESSAGE_NAME
,ERROR_MESSAGE
,bo_identifier
,ENTITY_IDENTIFIER
,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_ICC_VERS_TAB
,l_msg_name
,l_msg_text
,G_BO_IDENTIFIER_ICC
,G_ENTITY_ICC_VERSION
,l_sysdate
,G_USER_ID
,l_sysdate
,G_USER_ID
,G_LOGIN_ID
,G_CONC_REQUEST_ID
,G_PROG_APPL_ID
,G_PROGRAM_ID
,l_sysdate
FROM EGO_ICC_VERS_INTERFACE EIVI
WHERE ( EIVI.transaction_type IS NULL OR
EIVI.transaction_type NOT IN ( G_TTYPE_CREATE )
)
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( EIVI.set_process_id = G_SET_PROCESS_ID)
)
AND EIVI.process_status = G_PROCESS_STATUS_INITIAL
;
UPDATE EGO_ICC_VERS_INTERFACE EIVI
SET EIVI.process_status = G_PROCESS_STATUS_ERROR
WHERE ( EIVI.transaction_type IS NULL OR
EIVI.transaction_type <> G_TTYPE_CREATE
)
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( EIVI.set_process_id = G_SET_PROCESS_ID)
)
AND EIVI.process_status = G_PROCESS_STATUS_INITIAL
;
INSERT INTO MTL_INTERFACE_ERRORS
(
TRANSACTION_ID
,UNIQUE_ID
,ORGANIZATION_ID
,COLUMN_NAME
,TABLE_NAME
,MESSAGE_NAME
,ERROR_MESSAGE
,bo_identifier
,ENTITY_IDENTIFIER
,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_ICC_HEADER_TAB
,l_msg_name
,l_msg_text
,G_BO_IDENTIFIER_ICC
,G_ENTITY_ICC_HEADER
,l_sysdate
,G_USER_ID
,l_sysdate
,G_USER_ID
,G_LOGIN_ID
,G_CONC_REQUEST_ID
,G_PROG_APPL_ID
,G_PROGRAM_ID
,l_sysdate
FROM MTL_ITEM_CAT_GRPS_INTERFACE MICGI
WHERE
( micgi.item_catalog_name is NULL AND
micgi.item_catalog_group_id is NULL AND
micgi.segment1 is NULL AND micgi.segment14 is NULL AND
micgi.segment2 is NULL AND micgi.segment15 is NULL AND
micgi.segment3 is NULL AND micgi.segment16 is NULL AND
micgi.segment4 is NULL AND micgi.segment17 is NULL AND
micgi.segment5 is NULL AND micgi.segment18 is NULL AND
micgi.segment6 is NULL AND micgi.segment19 is NULL AND
micgi.segment7 is NULL AND micgi.segment20 is NULL AND
micgi.segment8 is NULL AND
micgi.segment9 is NULL AND
micgi.segment10 is NULL AND
micgi.segment11 is NULL AND
micgi.segment12 is NULL AND
micgi.segment13 is NULL
)
AND micgi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( MICGI.set_process_id = G_SET_PROCESS_ID)
)
;
write_debug ( l_proc_name, 'rows inserted=>'||SQL%ROWCOUNT);
UPDATE MTL_ITEM_CAT_GRPS_INTERFACE micgi
SET micgi.process_status = G_PROCESS_STATUS_ERROR
WHERE ( micgi.item_catalog_name is NULL AND
micgi.item_catalog_group_id is NULL AND
micgi.segment1 is NULL AND micgi.segment14 is NULL AND
micgi.segment2 is NULL AND micgi.segment15 is NULL AND
micgi.segment3 is NULL AND micgi.segment16 is NULL AND
micgi.segment4 is NULL AND micgi.segment17 is NULL AND
micgi.segment5 is NULL AND micgi.segment18 is NULL AND
micgi.segment6 is NULL AND micgi.segment19 is NULL AND
micgi.segment7 is NULL AND micgi.segment20 is NULL AND
micgi.segment8 is NULL AND
micgi.segment9 is NULL AND
micgi.segment10 is NULL AND
micgi.segment11 is NULL AND
micgi.segment12 is NULL AND
micgi.segment13 is NULL
)
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( MICGI.set_process_id = G_SET_PROCESS_ID)
)
AND micgi.process_status = G_PROCESS_STATUS_INITIAL
;
INSERT INTO MTL_INTERFACE_ERRORS
(
TRANSACTION_ID
,UNIQUE_ID
,ORGANIZATION_ID
,COLUMN_NAME
,TABLE_NAME
,MESSAGE_NAME
,ERROR_MESSAGE
,bo_identifier
,ENTITY_IDENTIFIER
,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_ICC_HEADER_TAB
,l_msg_name
,l_msg_text
,G_BO_IDENTIFIER_ICC
,G_ENTITY_ICC_AG_ASSOC
,l_sysdate
,G_USER_ID
,l_sysdate
,G_USER_ID
,G_LOGIN_ID
,G_CONC_REQUEST_ID
,G_PROG_APPL_ID
,G_PROGRAM_ID
,l_sysdate
FROM EGO_ATTR_GRPS_ASSOC_INTERFACE eagai
WHERE ( (eagai.item_catalog_group_id IS NULL AND
eagai.item_catalog_name IS NULL )
OR
( eagai.attr_group_name IS NULL AND
eagai.attr_group_id IS NULL )
)
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( eagai.set_process_id = G_SET_PROCESS_ID)
)
AND eagai.process_status = G_PROCESS_STATUS_INITIAL
;
UPDATE EGO_ATTR_GRPS_ASSOC_INTERFACE eagai
SET eagai.process_status = G_PROCESS_STATUS_ERROR
WHERE ( (eagai.item_catalog_group_id IS NULL AND
eagai.item_catalog_name IS NULL )
OR
( eagai.attr_group_name IS NULL AND
eagai.attr_group_id IS NULL )
)
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( eagai.set_process_id = G_SET_PROCESS_ID)
)
AND eagai.process_status = G_PROCESS_STATUS_INITIAL
;
INSERT INTO MTL_INTERFACE_ERRORS
(
TRANSACTION_ID
,UNIQUE_ID
,ORGANIZATION_ID
,COLUMN_NAME
,TABLE_NAME
,MESSAGE_NAME
,ERROR_MESSAGE
,bo_identifier
,ENTITY_IDENTIFIER
,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_FUNC_PARAM_MAP_TAB
,l_msg_name
,l_msg_text
,G_BO_IDENTIFIER_ICC
,G_ENTITY_ICC_FN_PARAM_MAP
,l_sysdate
,G_USER_ID
,l_sysdate
,G_USER_ID
,G_LOGIN_ID
,G_CONC_REQUEST_ID
,G_PROG_APPL_ID
,G_PROGRAM_ID
,l_sysdate
FROM EGO_FUNC_PARAMS_MAP_INTERFACE efpmi
WHERE ( (efpmi.item_catalog_group_id IS NULL AND
efpmi.item_catalog_name IS NULL)
OR
(
efpmi.function_name IS NULL AND
efpmi.function_id IS NULL )
OR
(
efpmi.parameter_name IS NULL AND
efpmi.parameter_id IS NULL )
)
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( efpmi.set_process_id = G_SET_PROCESS_ID)
)
AND efpmi.process_status = G_PROCESS_STATUS_INITIAL
;
UPDATE EGO_FUNC_PARAMS_MAP_INTERFACE efpmi
SET efpmi.process_status = G_PROCESS_STATUS_ERROR
WHERE ( (efpmi.item_catalog_group_id IS NULL AND
efpmi.item_catalog_name IS NULL)
OR
(
efpmi.function_name IS NULL AND
efpmi.function_id IS NULL )
OR
(
efpmi.parameter_name IS NULL AND
efpmi.parameter_id IS NULL )
)
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( efpmi.set_process_id = G_SET_PROCESS_ID)
)
AND efpmi.process_status = G_PROCESS_STATUS_INITIAL
;
INSERT INTO MTL_INTERFACE_ERRORS
(
TRANSACTION_ID
,UNIQUE_ID
,ORGANIZATION_ID
,COLUMN_NAME
,TABLE_NAME
,MESSAGE_NAME
,ERROR_MESSAGE
,bo_identifier
,ENTITY_IDENTIFIER
,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_ICC_VERS_TAB
,l_msg_name
,l_msg_text
,G_BO_IDENTIFIER_ICC
,G_ENTITY_ICC_VERSION
,l_sysdate
,G_USER_ID
,l_sysdate
,G_USER_ID
,G_LOGIN_ID
,G_CONC_REQUEST_ID
,G_PROG_APPL_ID
,G_PROGRAM_ID
,l_sysdate
FROM EGO_ICC_VERS_INTERFACE eivi
where (
( G_SET_PROCESS_ID IS NULL )
OR
( eivi.set_process_id = G_SET_PROCESS_ID)
)
AND eivi.process_status = G_PROCESS_STATUS_INITIAL
;
UPDATE EGO_ICC_VERS_INTERFACE eivi
SET eivi.process_status = G_PROCESS_STATUS_ERROR
where (
( G_SET_PROCESS_ID IS NULL )
OR
( eivi.set_process_id = G_SET_PROCESS_ID)
)
AND eivi.process_status = G_PROCESS_STATUS_INITIAL
;
INSERT INTO MTL_INTERFACE_ERRORS
(
TRANSACTION_ID
,UNIQUE_ID
,ORGANIZATION_ID
,COLUMN_NAME
,TABLE_NAME
,MESSAGE_NAME
,ERROR_MESSAGE
,bo_identifier
,ENTITY_IDENTIFIER
,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_ICC_VERS_TAB
,l_msg_name
,l_msg_text
,G_BO_IDENTIFIER_ICC
,G_ENTITY_ICC_VERSION
,l_sysdate
,G_USER_ID
,l_sysdate
,G_USER_ID
,G_LOGIN_ID
,G_CONC_REQUEST_ID
,G_PROG_APPL_ID
,G_PROGRAM_ID
,l_sysdate
FROM EGO_ICC_VERS_INTERFACE eivi
where (
( eivi.item_catalog_name IS NULL AND
eivi.item_catalog_group_id IS NULL
)
)
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( eivi.set_process_id = G_SET_PROCESS_ID)
)
AND eivi.process_status = G_PROCESS_STATUS_INITIAL
;
UPDATE EGO_ICC_VERS_INTERFACE eivi
SET eivi.process_status = G_PROCESS_STATUS_ERROR
where (
( eivi.item_catalog_name IS NULL AND
eivi.item_catalog_group_id IS NULL
)
)
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( eivi.set_process_id = G_SET_PROCESS_ID)
)
AND eivi.process_status = G_PROCESS_STATUS_INITIAL
;
INSERT INTO MTL_INTERFACE_ERRORS
(
TRANSACTION_ID
,UNIQUE_ID
,ORGANIZATION_ID
,COLUMN_NAME
,TABLE_NAME
,MESSAGE_NAME
,ERROR_MESSAGE
,BO_IDENTIFIER
,ENTITY_IDENTIFIER
,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_ICC_VERS_TAB
,l_msg_name
,l_msg_text
,G_BO_IDENTIFIER_ICC
,G_ENTITY_ICC_VERSION
,l_sysdate
,G_USER_ID
,l_sysdate
,G_USER_ID
,G_LOGIN_ID
,G_CONC_REQUEST_ID
,G_PROG_APPL_ID
,G_PROGRAM_ID
,l_sysdate
FROM ego_icc_vers_interface eivi
WHERE 1=1
AND ---( eivi.ver_seq_no <= 0 ---- commented bug 9752139, moved to row by row
--- OR
eivi.ver_seq_no IS NULL
---)
AND eivi.transaction_id IS NOT NULL
AND eivi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( eivi.set_process_id = G_SET_PROCESS_ID)
);
UPDATE EGO_ICC_VERS_INTERFACE eivi
SET eivi.process_status = G_PROCESS_STATUS_ERROR
WHERE 1=1
AND ---( eivi.ver_seq_no <= 0 ---- commented bug 9752139, moved to row by row
--- OR
eivi.ver_seq_no IS NULL
---)
AND eivi.transaction_id IS NOT NULL
AND eivi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( eivi.set_process_id = G_SET_PROCESS_ID)
);
UPDATE mtl_item_cat_grps_interface micgi
SET item_catalog_name = ( select icc_kfv.concatenated_segments
from mtl_item_catalog_groups_kfv icc_kfv
where icc_kfv.item_catalog_group_id = micgi.item_catalog_group_id
)
WHERE micgi.item_catalog_group_id IS NOT NULL
AND micgi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( MICGI.set_process_id = G_SET_PROCESS_ID)
)
;
UPDATE mtl_item_cat_grps_interface micgi
SET micgi.item_catalog_group_id = ( select icc_kfv.item_catalog_group_id
from mtl_item_catalog_groups_kfv icc_kfv
where icc_kfv.concatenated_segments = micgi.item_catalog_name
)
WHERE micgi.item_catalog_name IS NOT NULL
AND micgi.item_catalog_group_id IS NULL
AND micgi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( MICGI.set_process_id = G_SET_PROCESS_ID)
)
;
INSERT INTO MTL_INTERFACE_ERRORS
(
TRANSACTION_ID
,UNIQUE_ID
,ORGANIZATION_ID
,COLUMN_NAME
,TABLE_NAME
,MESSAGE_NAME
,ERROR_MESSAGE
,BO_IDENTIFIER
,ENTITY_IDENTIFIER
,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_ICC_HEADER_TAB
,l_msg_name
,l_msg_text
,G_BO_IDENTIFIER_ICC
,G_ENTITY_ICC_HEADER
,l_sysdate
,G_USER_ID
,l_sysdate
,G_USER_ID
,G_LOGIN_ID
,G_CONC_REQUEST_ID
,G_PROG_APPL_ID
,G_PROGRAM_ID
,l_sysdate
FROM mtl_item_cat_grps_interface micgi
WHERE micgi.item_catalog_name IS NULL
AND micgi.item_catalog_group_id IS NOT NULL
AND micgi.transaction_id IS NOT NULL
AND micgi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( micgi.set_process_id = G_SET_PROCESS_ID)
) ;
UPDATE MTL_ITEM_CAT_GRPS_INTERFACE micgi
set process_status = G_PROCESS_STATUS_ERROR
WHERE micgi.item_catalog_group_id is NOT NULL
AND micgi.item_catalog_name IS NULL
and micgi.transaction_id IS NOT NULL
AND micgi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( MICGI.set_process_id = G_SET_PROCESS_ID)
)
;
UPDATE mtl_item_cat_grps_interface micgi
SET parent_catalog_group_name = ( select icc_kfv.concatenated_segments
from mtl_item_catalog_groups_kfv icc_kfv
where icc_kfv.item_catalog_group_id = micgi.parent_catalog_group_id
)
WHERE micgi.parent_catalog_group_id IS NOT NULL
AND micgi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( MICGI.set_process_id = G_SET_PROCESS_ID)
)
;
UPDATE mtl_item_cat_grps_interface micgi
SET micgi.parent_catalog_group_id = ( select icc_kfv.item_catalog_group_id
from mtl_item_catalog_groups_kfv icc_kfv
where icc_kfv.concatenated_segments = micgi.parent_catalog_group_name
)
WHERE micgi.parent_catalog_group_name IS NOT NULL
AND micgi.parent_catalog_group_id IS NULL
AND micgi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( MICGI.set_process_id = G_SET_PROCESS_ID)
)
;
INSERT INTO MTL_INTERFACE_ERRORS
(
TRANSACTION_ID
,UNIQUE_ID
,ORGANIZATION_ID
,COLUMN_NAME
,TABLE_NAME
,MESSAGE_NAME
,ERROR_MESSAGE
,BO_IDENTIFIER
,ENTITY_IDENTIFIER
,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_ICC_HEADER_TAB
,l_msg_name
,l_msg_text
,G_BO_IDENTIFIER_ICC
,G_ENTITY_ICC_HEADER
,l_sysdate
,G_USER_ID
,l_sysdate
,G_USER_ID
,G_LOGIN_ID
,G_CONC_REQUEST_ID
,G_PROG_APPL_ID
,G_PROGRAM_ID
,l_sysdate
FROM mtl_item_cat_grps_interface micgi
WHERE micgi.parent_catalog_group_name IS NULL
AND micgi.parent_catalog_group_id IS NOT NULL
AND micgi.transaction_id IS NOT NULL
AND micgi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( micgi.set_process_id = G_SET_PROCESS_ID)
) ;
UPDATE MTL_ITEM_CAT_GRPS_INTERFACE micgi
set process_status = G_PROCESS_STATUS_ERROR
WHERE micgi.parent_catalog_group_name IS NULL
AND micgi.parent_catalog_group_id is NOT NULL
and micgi.transaction_id IS NOT NULL
AND micgi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( MICGI.set_process_id = G_SET_PROCESS_ID)
)
;
UPDATE EGO_ATTR_GRPS_ASSOC_INTERFACE EAGAI
SET attr_group_name = ( select eagv.attr_group_name
from ego_attr_groups_v EAGv
where EAGv.attr_group_id = EAGAI.attr_group_id
)
WHERE eagai.attr_group_id IS NOT NULL
AND eagai.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( eagai.set_process_id = G_SET_PROCESS_ID)
)
;
UPDATE EGO_ATTR_GRPS_ASSOC_INTERFACE EAGAI
SET attr_group_id = ( select eagv.attr_group_id
from ego_attr_groups_v EAGv
where EAGv.attr_group_name = EAGAI.attr_group_name
)
WHERE eagai.attr_group_id IS NULL
AND eagai.attr_group_name IS NOT NULL
AND eagai.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( eagai.set_process_id = G_SET_PROCESS_ID)
)
;
UPDATE EGO_ATTR_GRPS_ASSOC_INTERFACE EAGAI
SET eagai.item_catalog_name = ( select icc_kfv.concatenated_segments
from mtl_item_catalog_groups_kfv icc_kfv
where icc_kfv.item_catalog_group_id = eagai.item_catalog_group_id
)
WHERE eagai.item_catalog_group_id IS NOT NULL
AND eagai.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( eagai.set_process_id = G_SET_PROCESS_ID)
)
;
UPDATE EGO_ATTR_GRPS_ASSOC_INTERFACE EAGAI
SET eagai.item_catalog_group_id = ( select icc_kfv.item_catalog_group_id
from mtl_item_catalog_groups_kfv icc_kfv
where icc_kfv.concatenated_segments = eagai.item_catalog_name
)
WHERE eagai.item_catalog_name IS NOT NULL
AND eagai.item_catalog_group_id IS NULL
AND eagai.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( eagai.set_process_id = G_SET_PROCESS_ID)
)
;
INSERT INTO MTL_INTERFACE_ERRORS
(
TRANSACTION_ID
,UNIQUE_ID
,ORGANIZATION_ID
,COLUMN_NAME
,TABLE_NAME
,MESSAGE_NAME
,ERROR_MESSAGE
,BO_IDENTIFIER
,ENTITY_IDENTIFIER
,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_ICC_AG_ASSOC_TAB
,l_msg_name
,l_msg_text
,G_BO_IDENTIFIER_ICC
,G_ENTITY_ICC_AG_ASSOC
,l_sysdate
,G_USER_ID
,l_sysdate
,G_USER_ID
,G_LOGIN_ID
,G_CONC_REQUEST_ID
,G_PROG_APPL_ID
,G_PROGRAM_ID
,l_sysdate
FROM EGO_ATTR_GRPS_ASSOC_INTERFACE eagai
WHERE eagai.item_catalog_name IS NULL
OR eagai.item_catalog_group_id IS NULL
AND eagai.transaction_id IS NOT NULL
AND eagai.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( eagai.set_process_id = G_SET_PROCESS_ID)
);
INSERT INTO MTL_INTERFACE_ERRORS
(
TRANSACTION_ID
,UNIQUE_ID
,ORGANIZATION_ID
,COLUMN_NAME
,TABLE_NAME
,MESSAGE_NAME
,ERROR_MESSAGE
,bo_identifier
,ENTITY_IDENTIFIER
,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_ICC_AG_ASSOC_TAB
,l_msg_name2
,l_msg_text2
,G_BO_IDENTIFIER_ICC
,G_ENTITY_ICC_AG_ASSOC
,l_sysdate
,G_USER_ID
,l_sysdate
,G_USER_ID
,G_LOGIN_ID
,G_CONC_REQUEST_ID
,G_PROG_APPL_ID
,G_PROGRAM_ID
,l_sysdate
FROM EGO_ATTR_GRPS_ASSOC_INTERFACE eagai
WHERE ( eagai.attr_group_name IS NULL
OR
eagai.attr_group_id IS NULL
)
AND eagai.transaction_id IS NOT NULL
AND eagai.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( eagai.set_process_id = G_SET_PROCESS_ID)
)
;
UPDATE EGO_ATTR_GRPS_ASSOC_INTERFACE eagai
set process_status = G_PROCESS_STATUS_ERROR
WHERE eagai.item_catalog_name IS NULL
OR eagai.item_catalog_group_id IS NULL
OR eagai.attr_group_name IS NULL
OR eagai.attr_group_id IS NULL
AND eagai.transaction_id IS NOT NULL
AND eagai.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( eagai.set_process_id = G_SET_PROCESS_ID)
)
;
UPDATE EGO_FUNC_PARAMS_MAP_INTERFACE efpmi
SET efpmi.item_catalog_name = ( select icc_kfv.concatenated_segments
from mtl_item_catalog_groups_kfv icc_kfv
where icc_kfv.item_catalog_group_id = efpmi.item_catalog_group_id
)
WHERE efpmi.item_catalog_group_id IS NOT NULL
AND efpmi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( efpmi.set_process_id = G_SET_PROCESS_ID)
)
;
UPDATE EGO_FUNC_PARAMS_MAP_INTERFACE efpmi
SET efpmi.item_catalog_group_id = ( select icc_kfv.item_catalog_group_id
from mtl_item_catalog_groups_kfv icc_kfv
where icc_kfv.concatenated_segments = efpmi.item_catalog_name
)
WHERE efpmi.item_catalog_name IS NOT NULL
AND efpmi.item_catalog_group_id IS NULL
AND efpmi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( efpmi.set_process_id = G_SET_PROCESS_ID)
)
;
UPDATE EGO_FUNC_PARAMS_MAP_INTERFACE efpmi
set efpmi.attr_group_name =( SELECT agv.attr_group_id
FROM ego_attr_groups_v agv,
ego_obj_attr_grp_assocs_v oagv,
ego_catalog_groups_v cg
WHERE oagv.attr_group_id = agv.attr_group_id
AND agv.attr_group_id = EFPMI.ATTR_GROUP_ID
AND cg.catalog_group_id = oagv.classification_code
AND oagv.object_id = g_item_obj_id
and oagv.data_level_int_name = l_item_data_level
AND oagv.classification_code
IN (
SELECT TO_CHAR (item_catalog_group_id)
FROM mtl_item_catalog_groups_b
CONNECT BY PRIOR parent_catalog_group_id =
item_catalog_group_id
START WITH item_catalog_group_id = EFPMI.ITEM_CATALOG_GROUP_ID
)
)
WHERE efpmi.attr_group_id IS NOT NULL
AND efpmi.item_catalog_group_id is not null
AND efpmi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( efpmi.set_process_id = G_SET_PROCESS_ID)
)
;
UPDATE EGO_FUNC_PARAMS_MAP_INTERFACE efpmi
set efpmi.attr_group_id = ( SELECT agv.attr_group_id
FROM ego_attr_groups_v agv,
ego_obj_attr_grp_assocs_v oagv,
ego_catalog_groups_v cg
WHERE oagv.attr_group_id = agv.attr_group_id
AND agv.attr_group_name = EFPMI.ATTR_GROUP_NAME
AND cg.catalog_group_id = oagv.classification_code
AND oagv.object_id = g_item_obj_id
and oagv.data_level_int_name = l_item_data_level
AND oagv.classification_code
IN (
SELECT TO_CHAR (item_catalog_group_id)
FROM mtl_item_catalog_groups_b
CONNECT BY PRIOR parent_catalog_group_id =
item_catalog_group_id
START WITH item_catalog_group_id = EFPMI.ITEM_CATALOG_GROUP_ID
)
)
WHERE efpmi.attr_group_name IS NOT NULL
AND efpmi.attr_group_id IS NULL
AND efpmi.item_catalog_group_id IS NOT NULL
AND efpmi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( efpmi.set_process_id = G_SET_PROCESS_ID)
)
;
UPDATE EGO_FUNC_PARAMS_MAP_INTERFACE efpmi
set efpmi.attr_name = ( SELECT eav.attr_name
FROM ego_attrs_v eav
WHERE eav.attr_id = efpmi.attr_id
AND eav.attr_group_name = efpmi.attr_group_name
)
WHERE efpmi.attr_name IS NULL
AND efpmi.attr_id IS NOT NULL
AND efpmi.attr_group_name IS NOT NULL
AND efpmi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( efpmi.set_process_id = G_SET_PROCESS_ID)
)
;
UPDATE EGO_FUNC_PARAMS_MAP_INTERFACE efpmi
set efpmi.attr_id = ( select eav.attr_id
from ego_attrs_v eav
where eav.attr_name = efpmi.attr_name
and eav.attr_group_name = efpmi.attr_group_name
)
WHERE efpmi.attr_id is null
AND efpmi.attr_name IS NOT NULL
AND efpmi.attr_group_name IS NOT NULL
AND efpmi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( efpmi.set_process_id = G_SET_PROCESS_ID)
)
;
UPDATE EGO_FUNC_PARAMS_MAP_INTERFACE efpmi
set efpmi.function_name = ( select efv.internal_name
from ego_functions_v efv
where efv.function_id = efv.function_id
)
WHERE efpmi.function_name IS NULL
AND efpmi.function_id IS NOT NULL
AND efpmi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( efpmi.set_process_id = G_SET_PROCESS_ID)
)
;
UPDATE EGO_FUNC_PARAMS_MAP_INTERFACE efpmi
set efpmi.function_id = ( select efv.function_id
from ego_functions_v efv
where efv.internal_name = efpmi.function_name
)
WHERE efpmi.function_id IS NULL
AND efpmi.function_name IS NOT NULL
AND efpmi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( efpmi.set_process_id = G_SET_PROCESS_ID)
)
;
UPDATE EGO_FUNC_PARAMS_MAP_INTERFACE efpmi
set efpmi.parameter_id = ( select efpb.func_param_id
from ego_func_params_b efpb
where efpb.internal_name = efpmi.parameter_name
and efpb.function_id = efpmi.function_id
)
WHERE efpmi.parameter_id IS NULL
AND efpmi.parameter_name IS NOT NULL
AND efpmi.function_id IS NOT NULL
AND efpmi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( efpmi.set_process_id = G_SET_PROCESS_ID)
)
;
UPDATE EGO_FUNC_PARAMS_MAP_INTERFACE efpmi
set efpmi.parameter_name = ( select efpb.internal_name
from ego_func_params_b efpb
where efpb.func_param_id = efpmi.parameter_id
and efpb.function_id = efpmi.function_id
)
WHERE efpmi.parameter_name IS NULL
AND efpmi.parameter_id IS NOT NULL
AND efpmi.function_id IS NOT NULL
AND efpmi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( efpmi.set_process_id = G_SET_PROCESS_ID)
)
;
INSERT INTO MTL_INTERFACE_ERRORS
(
TRANSACTION_ID
,UNIQUE_ID
,ORGANIZATION_ID
,COLUMN_NAME
,TABLE_NAME
,MESSAGE_NAME
,ERROR_MESSAGE
,bo_identifier
,ENTITY_IDENTIFIER
,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_FUNC_PARAM_MAP_TAB
,l_msg_name
,l_msg_text
,G_BO_IDENTIFIER_ICC
,G_ENTITY_ICC_FN_PARAM_MAP
,l_sysdate
,G_USER_ID
,l_sysdate
,G_USER_ID
,G_LOGIN_ID
,G_CONC_REQUEST_ID
,G_PROG_APPL_ID
,G_PROGRAM_ID
,l_sysdate
FROM EGO_FUNC_PARAMS_MAP_INTERFACE efpmi
WHERE efpmi.item_catalog_name IS NULL
OR efpmi.item_catalog_group_id IS NULL
AND efpmi.transaction_id IS NOT NULL
AND efpmi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( efpmi.set_process_id = G_SET_PROCESS_ID)
);
INSERT INTO MTL_INTERFACE_ERRORS
(
TRANSACTION_ID
,UNIQUE_ID
,ORGANIZATION_ID
,COLUMN_NAME
,TABLE_NAME
,MESSAGE_NAME
,ERROR_MESSAGE
,bo_identifier
,ENTITY_IDENTIFIER
,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_FUNC_PARAM_MAP_TAB
,l_msg_name2
,l_msg_text2
,G_BO_IDENTIFIER_ICC
,G_ENTITY_ICC_FN_PARAM_MAP
,l_sysdate
,G_USER_ID
,l_sysdate
,G_USER_ID
,G_LOGIN_ID
,G_CONC_REQUEST_ID
,G_PROG_APPL_ID
,G_PROGRAM_ID
,l_sysdate
FROM EGO_FUNC_PARAMS_MAP_INTERFACE efpmi
WHERE efpmi.attr_group_name IS NULL
OR efpmi.attr_group_id IS NULL
AND efpmi.transaction_id IS NOT NULL
AND efpmi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( efpmi.set_process_id = G_SET_PROCESS_ID)
);
INSERT INTO MTL_INTERFACE_ERRORS
(
TRANSACTION_ID
,UNIQUE_ID
,ORGANIZATION_ID
,COLUMN_NAME
,TABLE_NAME
,MESSAGE_NAME
,ERROR_MESSAGE
,bo_identifier
,ENTITY_IDENTIFIER
,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_FUNC_PARAM_MAP_TAB
,l_msg_name3
,l_msg_text3
,G_BO_IDENTIFIER_ICC
,G_ENTITY_ICC_FN_PARAM_MAP
,l_sysdate
,G_USER_ID
,l_sysdate
,G_USER_ID
,G_LOGIN_ID
,G_CONC_REQUEST_ID
,G_PROG_APPL_ID
,G_PROGRAM_ID
,l_sysdate
FROM EGO_FUNC_PARAMS_MAP_INTERFACE efpmi
WHERE efpmi.attr_name IS NULL
OR efpmi.attr_id IS NULL
AND efpmi.transaction_id IS NOT NULL
AND efpmi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( efpmi.set_process_id = G_SET_PROCESS_ID)
)
;
INSERT INTO MTL_INTERFACE_ERRORS
(
TRANSACTION_ID
,UNIQUE_ID
,ORGANIZATION_ID
,COLUMN_NAME
,TABLE_NAME
,MESSAGE_NAME
,ERROR_MESSAGE
,bo_identifier
,ENTITY_IDENTIFIER
,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_FUNC_PARAM_MAP_TAB
,l_msg_name3
,l_msg_text3
,G_BO_IDENTIFIER_ICC
,G_ENTITY_ICC_FN_PARAM_MAP
,l_sysdate
,G_USER_ID
,l_sysdate
,G_USER_ID
,G_LOGIN_ID
,G_CONC_REQUEST_ID
,G_PROG_APPL_ID
,G_PROGRAM_ID
,l_sysdate
FROM EGO_FUNC_PARAMS_MAP_INTERFACE efpmi
WHERE efpmi.function_name IS NULL
OR efpmi.function_id IS NULL
AND efpmi.transaction_id IS NOT NULL
AND efpmi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( efpmi.set_process_id = G_SET_PROCESS_ID)
)
;
INSERT INTO MTL_INTERFACE_ERRORS
(
TRANSACTION_ID
,UNIQUE_ID
,ORGANIZATION_ID
,COLUMN_NAME
,TABLE_NAME
,MESSAGE_NAME
,ERROR_MESSAGE
,bo_identifier
,ENTITY_IDENTIFIER
,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_FUNC_PARAM_MAP_TAB
,l_msg_name5
,l_msg_text5
,G_BO_IDENTIFIER_ICC
,G_ENTITY_ICC_FN_PARAM_MAP
,l_sysdate
,G_USER_ID
,l_sysdate
,G_USER_ID
,G_LOGIN_ID
,G_CONC_REQUEST_ID
,G_PROG_APPL_ID
,G_PROGRAM_ID
,l_sysdate
FROM EGO_FUNC_PARAMS_MAP_INTERFACE efpmi
WHERE efpmi.parameter_name IS NULL
OR efpmi.parameter_id IS NULL
AND efpmi.transaction_id IS NOT NULL
AND efpmi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( efpmi.set_process_id = G_SET_PROCESS_ID)
);
UPDATE EGO_FUNC_PARAMS_MAP_INTERFACE efpmi
SET efpmi.process_status = G_PROCESS_STATUS_ERROR
WHERE efpmi.attr_name IS NULL
OR efpmi.attr_group_name IS NULL
OR efpmi.item_catalog_name IS NULL
OR efpmi.attr_id IS NULL
OR efpmi.attr_group_id IS NULL
OR efpmi.item_catalog_group_ID IS NULL
OR efpmi.function_id IS NULL
OR efpmi.function_name IS NULL
OR efpmi.parameter_id IS NULL
OR efpmi.parameter_name IS NULL
AND efpmi.transaction_id IS NOT NULL
AND efpmi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( efpmi.set_process_id = G_SET_PROCESS_ID)
)
;
UPDATE EGO_ICC_VERS_INTERFACE eivi
SET eivi.item_catalog_name = ( select icc_kfv.concatenated_segments
from mtl_item_catalog_groups_kfv icc_kfv
where icc_kfv.item_catalog_group_id = eivi.item_catalog_group_id
)
WHERE eivi.item_catalog_group_id IS NOT NULL
AND eivi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( eivi.set_process_id = G_SET_PROCESS_ID)
)
;
write_debug(l_proc_name, 'updated name');
UPDATE ego_icc_vers_interface eivi
SET eivi.item_catalog_group_id = ( select icc_kfv.item_catalog_group_id
from mtl_item_catalog_groups_kfv icc_kfv
where icc_kfv.concatenated_segments = eivi.item_catalog_name
)
WHERE eivi.item_catalog_name IS NOT NULL
AND eivi.item_catalog_group_id IS NULL
AND eivi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( eivi.set_process_id = G_SET_PROCESS_ID)
)
;
write_debug(l_proc_name, 'updated id');
INSERT INTO MTL_INTERFACE_ERRORS
(
TRANSACTION_ID
,UNIQUE_ID
,ORGANIZATION_ID
,COLUMN_NAME
,TABLE_NAME
,MESSAGE_NAME
,ERROR_MESSAGE
,BO_IDENTIFIER
,ENTITY_IDENTIFIER
,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_ICC_VERS_TAB
,l_msg_name
,l_msg_text
,G_BO_IDENTIFIER_ICC
,G_ENTITY_ICC_VERSION
,l_sysdate
,G_USER_ID
,l_sysdate
,G_USER_ID
,G_LOGIN_ID
,G_CONC_REQUEST_ID
,G_PROG_APPL_ID
,G_PROGRAM_ID
,l_sysdate
FROM ego_icc_vers_interface eivi
WHERE ( eivi.item_catalog_group_id IS NULL
OR
eivi.item_catalog_name IS NULL
)
AND eivi.transaction_id IS NOT NULL
AND eivi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( eivi.set_process_id = G_SET_PROCESS_ID)
) ;
write_debug(l_proc_name, 'inserted into errors=>'||SQL%ROWCOUNT);
UPDATE ego_icc_vers_interface eivi
set process_status = G_PROCESS_STATUS_ERROR
WHERE ( eivi.item_catalog_group_id IS NULL
OR
eivi.item_catalog_name IS NULL
)
AND eivi.transaction_id IS NOT NULL
AND eivi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( eivi.set_process_id = G_SET_PROCESS_ID)
)
;
write_debug(l_proc_name, 'updated error to intf rows affected=>'||SQL%ROWCOUNT);
SELECT item_catalog_group_id
INTO l_ccid
FROM mtl_Item_Catalog_Groups_b
WHERE item_catalog_group_id = p_catalog_grp_id;
SELECT 1
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_CODE = p_code
AND LOOKUP_TYPE = p_lookup
AND LANGUAGE = USERENV('LANG');
SELECT cat.item_num_action_id, cat.item_desc_action_id
FROM mtl_item_catalog_groups_b cat
WHERE cat.item_catalog_group_id = p_item_catalog_group_id;
SELECT act.function_id
FROM ego_actions_b act
WHERE act.object_id = p_object_id
AND act.classification_code = p_classification_code
AND act.action_id = p_action_id;
SELECT 1
FROM ego_mappings_b b
WHERE b.mapped_obj_pk1_val = to_char(p_action_id)
AND b.function_id = p_function_id
AND b.func_param_id = p_func_param_id;
select FUNC_PARAM_ID, internal_name
from ego_func_params_b efpb
where efpb.function_id = p_function_id
and efpb.internal_name = nvl(p_param_name, efpb.internal_name)
and efpb.func_param_id = nvl(p_param_id ,efpb.func_param_id)
;
l_token_table.delete;
p_func_param_assoc_rec.transaction_type := G_TTYPE_UPDATE;
IF p_func_param_assoc_rec.transaction_type = G_TTYPE_UPDATE AND (NOT l_mapping_found) THEN
x_return_status := G_RET_STS_ERROR;
l_token_table.DELETE;
p_func_param_assoc_rec.transaction_type := G_TTYPE_UPDATE;
l_token_table.DELETE;
l_token_table.DELETE;
SELECT function_id
FROM ego_functions_v
where ( function_id = nvl(p_func_id, function_id)
AND
internal_name = nvl(p_func_name, internal_name)
) ;
SELECT 1
FROM EGO_FUNC_PARAMS_B
WHERE function_id = p_cur_func_id
AND param_type = 'R'
AND data_type IN ('O', 'N', 'V', 'I', 'L', 'S')
;
l_token_table.DELETE;
l_token_table.DELETE;
l_token_table.DELETE;
l_token_table.DELETE;
l_token_table.DELETE;
l_token_table.delete;
ELSIF p_icc_rec.transaction_type = G_TTYPE_UPDATE
AND ( p_icc_rec.parent_catalog_group_id = G_MISS_NUM --- trying to NULL out the parent
OR
( p_icc_rec.parent_catalog_group_id IS NULL --- parent ICC already exists check
AND
g_old_icc_rec.parent_catalog_group_id IS NULL
)
) THEN
x_return_status := G_RET_STS_ERROR;
l_token_table.delete;
l_token_table.DELETE;
l_token_table.delete;
ELSIF p_icc_rec.transaction_type = G_TTYPE_UPDATE
AND ( p_icc_rec.parent_catalog_group_id = G_MISS_NUM --- trying to NULL out the parent
OR
( p_icc_rec.parent_catalog_group_id IS NULL --- parent ICC already exists check
AND
g_old_icc_rec.parent_catalog_group_id IS NULL
)
) THEN
x_return_status := G_RET_STS_ERROR;
l_token_table.delete;
SELECT change_order_type_id
FROM eng_change_order_types_vl
where ( change_order_type_id = nvl(p_change_order_type_id, change_order_type_id)
AND
change_order_type = nvl(p_change_order_type_name, change_order_type)
)
;
l_token_table.DELETE;
l_token_table.delete;
l_token_table.delete;
l_token_table.delete;
l_token_table.delete;
ELSIF p_icc_rec.transaction_type = G_TTYPE_UPDATE
AND ( p_icc_rec.parent_catalog_group_id = G_MISS_NUM --- trying to NULL out the parent
OR
( p_icc_rec.parent_catalog_group_id IS NULL --- parent ICC already exists check
AND
g_old_icc_rec.parent_catalog_group_id IS NULL
)
) THEN
x_return_status := G_RET_STS_ERROR;
l_token_table.delete;
SELECT *
INTO p_old_icc_rec
FROM mtl_item_catalog_groups_b
WHERE item_catalog_group_id = p_icc_id;
SELECT data_level_id
FROM ego_data_level_b
WHERE data_level_id = NVL(p_data_level_id, data_level_id)
AND data_level_name = NVL (p_data_level_name , data_level_name)
;
select association_id
from ego_obj_ag_assocs_b
where attr_group_id = p_attr_group_id
and classification_code = to_char(p_icc_id)
and object_id = ( select object_id from fnd_objects where obj_name = G_ITEM_OBJ_NAME)
and rownum = 1
;
SELECT oagv.association_id
FROM ego_attr_groups_v agv,
ego_obj_attr_grp_assocs_v oagv,
ego_catalog_groups_v cg
WHERE oagv.attr_group_id = agv.attr_group_id
AND agv.attr_group_id = p_attr_group_id
AND cg.catalog_group_id = oagv.classification_code
AND oagv.object_id = g_item_obj_id
AND oagv.classification_code IN (
SELECT TO_CHAR (item_catalog_group_id)
FROM mtl_item_catalog_groups_b
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
START WITH item_catalog_group_id = p_icc_id
)
;
select application_id
into G_EGO_APPL_ID
from fnd_application
where application_short_name = G_APPL_NAME;
SELECT 1
from ego_fnd_dsc_flx_ctx_ext
WHERE descriptive_flexfield_name = G_SEEDED_AG_Type
AND application_id = g_ego_appl_id
and attr_group_id = p_attr_grp_id
;
SELECT 1
FROM EGO_MTL_CATALOG_GRP_VERS_B b
WHERE b.item_catalog_group_id = p_icc_id
AND version_seq_id <> 0;
SELECT 1
FROM EGO_MTL_CATALOG_GRP_VERS_B b
WHERE b.item_catalog_group_id = p_icc_id
AND p_date BETWEEN start_active_date
AND NVL(end_active_date, p_date+1)
AND version_seq_id <> 0;
SELECT 1
FROM EGO_MTL_CATALOG_GRP_VERS_B b
WHERE b.item_catalog_group_id = p_icc_id
AND version_seq_id <> 0;
p_icc_rec.transaction_type := G_TTYPE_UPDATE;
IF p_icc_rec.transaction_type = G_TTYPE_UPDATE AND NOT Check_Catalog_CCID (p_icc_rec.item_catalog_group_id) THEN
l_return_status := G_RET_STS_ERROR;
ELSIF p_icc_rec.transaction_type = G_TTYPE_UPDATE AND Check_Catalog_CCID (p_icc_rec.item_catalog_group_id) THEN
--- Get the details for existing ICC , required for validations
---
Fetch_Old_ICC_Dtls (g_old_icc_rec, p_icc_rec.item_catalog_group_id);
write_debug (l_proc_name, 'inserting error for parent');
l_token_table.delete();
write_debug (l_proc_name, 'inserting error for parent, parent no released version');
l_token_table.delete();
IF p_icc_rec.transaction_type = G_TTYPE_UPDATE
AND Has_Released_Version(p_icc_rec.item_catalog_group_id)
AND G_P4TP_PROFILE_ENABLED
AND NVL(g_old_icc_rec.PARENT_CATALOG_GROUP_ID, -99999) <> p_icc_rec.parent_catalog_group_id THEN
ERROR_HANDLER.Add_Error_Message(
p_message_name => 'EGO_ICC_DIS_UPD'
,p_application_id => G_APPL_NAME
,p_token_tbl => l_token_table
,p_row_identifier => p_icc_rec.transaction_id
,p_entity_code => l_entity_code
,p_table_name => G_ENTITY_ICC_HEADER_TAB
);
IF p_icc_rec.transaction_type = G_TTYPE_UPDATE
AND Has_OnlyDraft_Version(p_icc_rec.item_catalog_group_id)
AND G_P4TP_PROFILE_ENABLED
AND g_old_icc_rec.PARENT_CATALOG_GROUP_ID <> p_icc_rec.parent_catalog_group_id THEN
ERROR_HANDLER.Add_Error_Message(
p_message_name => 'EGO_ICC_DIS_UPD'
,p_application_id => G_APPL_NAME
,p_token_tbl => l_token_table
,p_row_identifier => p_icc_rec.transaction_id
,p_entity_code => l_entity_code
,p_table_name => G_ENTITY_ICC_HEADER_TAB
);
p_message_name => 'EGO_CANNOT_UPDATE_SEEDED_AG'
,p_application_id => G_APPL_NAME
,p_row_identifier => p_ag_assoc_rec.transaction_id
,p_entity_code => l_entity_code
,p_table_name => G_ENTITY_ICC_AG_ASSOC_TAB
);
l_token_table.DELETE;
ELSIF (l_association_id IS NULL and p_ag_assoc_rec.transaction_type IN ( G_TTYPE_DELETE ) ) THEN
l_token_table(1).TOKEN_NAME := 'AG_NAME';
l_token_table.DELETE;
l_token_table.DELETE;
l_token_table.DELETE;
l_token_table.DELETE;
l_token_table.DELETE;
FOR rec_dt_chk in ( SELECT 1
FROM ego_mtl_catalog_grp_vers_b
WHERE version_seq_id <> 0
AND start_active_date = p_icc_ver_rec.start_date
AND item_catalog_group_id = p_icc_ver_rec.item_catalog_group_id
) LOOP
---
--- Version seq already exists in the system
---
l_token_table(1).TOKEN_NAME := 'START_DATE';
l_token_table.DELETE;
EGO_TA_BULKLOAD_PVT.Update_Intf_Err_Trans_Attrs(
p_set_process_id => G_SET_PROCESS_ID,
p_item_catalog_group_id => p_icc_ver_rec.item_catalog_group_id,
p_icc_version_number_intf => p_icc_ver_rec.ver_seq_no,
x_return_status => l_return_status2,
x_return_msg => x_return_msg);
l_token_table.DELETE;
SELECT party_id
INTO l_party_id
from ego_user_v
where user_id = p_user_id;
SELECT party_id
INTO l_party_id
from ego_user_v
where user_name = G_DEFAULT_USER_NAME;
INSERT INTO EGO_MTL_CATALOG_GRP_VERS_B
(item_catalog_group_id,
version_seq_id,
version_description,
start_active_date,
end_active_date,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES
( p_item_catalog_id,
0,
l_msg_text,
null,
null,
G_USER_ID,
l_sysdate,
G_USER_ID,
l_sysdate,
G_LOGIN_ID);
INSERT INTO EGO_OBJECT_LOCK
( lock_id,
object_name,
pk1_value,
locking_party_id,
lock_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES ( EGO_OBJECT_LOCK_S.NEXTVAL,
l_obj_name,
p_item_catalog_id,
l_party_id,
'L',
G_USER_ID,
l_sysdate,
G_USER_ID,
l_sysdate,
G_LOGIN_ID);
G_TOKEN_TBL.DELETE;
ELSIF p_operation = G_TTYPE_UPDATE THEN
EGO_EXT_FWK_PUB.Update_Action (
p_api_version => 1.0
,p_action_id => p_action_id
,p_sequence => l_sequence
,p_action_name => l_action_name
,p_description => null
,p_function_id => p_function_id
,p_enable_key_attrs => p_enable_key_attrs
,p_security_privilege_id => null
,p_init_msg_list => FND_API.G_false
,p_commit => FND_API.G_FALSE
,x_return_status => x_return_status
,x_errorcode => l_error_code
,x_msg_count => l_msg_count
,x_msg_data => x_return_msg
);
write_debug(l_proc_name, 'action update func id=>'||p_function_id);
write_debug(l_proc_name, 'UPDATE action=>'||x_return_msg);
ELSIF p_operation = G_TTYPE_DELETE THEN
EGO_EXT_FWK_PUB.Delete_Action (
p_api_version => 1.0
,p_action_id => p_action_id
,p_init_msg_list => FND_API.g_false
,p_commit => FND_API.g_false
,x_return_status => x_return_status
,x_errorcode => l_error_code
,x_msg_count => l_msg_count
,x_msg_data => x_return_msg
);
EGO_EXT_FWK_PUB.Delete_Action_Display (
p_api_version => 1.0
,p_action_id => p_action_id
,p_init_msg_list => FND_API.g_false
,p_commit => FND_API.g_false
,x_return_status => x_return_status
,x_errorcode => l_error_code
,x_msg_count => l_msg_count
,x_msg_data => x_return_msg
);
write_debug(l_proc_name, 'action DELETE STS=>'||x_return_status);
write_debug(l_proc_name, 'delete action=>'||x_return_msg);
write_debug(l_proc_name, 'DELETE action=>'||x_return_msg);
SELECT action_id
FROM ego_actions_b
WHERE function_id = p_function_id
and classification_code = to_char(p_icc_id)
and object_id = p_object_id
;
SELECT function_id, enable_key_attributes
FROM ego_actions_b
WHERE action_id = p_action_id
and classification_code = to_char(p_icc_id)
and object_id = p_object_id
;
PROCEDURE Delete_Old_Num_Gen_Method
IS
BEGIN
Process_Function_Actions ( p_operation => G_TTYPE_DELETE --- delete the earlier action associated
,p_function_id => l_old_num_func_id
,p_icc_id => g_old_icc_rec.item_catalog_group_id
,p_transaction_id => p_icc_rec.transaction_id
,p_enable_key_attrs => NULL
,p_action_id => g_old_icc_rec.ITEM_NUM_ACTION_ID
,x_action_id => l_action_id --- dummy
,x_return_status => x_return_status
,x_return_msg => x_return_msg
);
END Delete_Old_Num_Gen_Method;
PROCEDURE Delete_Old_Desc_Gen_Method
IS
BEGIN
Process_Function_Actions ( p_operation => G_TTYPE_DELETE --- delete the earlier action associated
,p_function_id => l_old_desc_func_id
,p_icc_id => g_old_icc_rec.item_catalog_group_id
,p_transaction_id => p_icc_rec.transaction_id
,p_enable_key_attrs => p_icc_rec.enable_key_attrs_num
,p_action_id => g_old_icc_rec.ITEM_DESC_ACTION_ID
,x_action_id => l_action_id --- dummy
,x_return_status => x_return_status
,x_return_msg => x_return_msg
);
END Delete_Old_Desc_Gen_Method;
IF p_icc_rec.transaction_type = G_TTYPE_UPDATE THEN
--
-- Bug 12394675. Issue #4. Enable ket attributes are not getting
-- sync-ed. If user passed null then for update get value from old
-- function.
-- sreharih. Tue Apr 26 15:44:49 PDT 2011
--
--- Get the existing number generation function id
---
FOR rec_get_function IN cur_get_function ( g_old_icc_rec.item_num_action_id
, g_old_icc_rec.item_catalog_group_id
, l_object_id
) LOOP
l_old_num_func_id := rec_get_function.function_id;
IF p_icc_rec.transaction_type = G_TTYPE_UPDATE THEN
write_debug(l_proc_name, 'UPDATE transaction');
write_debug(l_proc_name, 'UPDATE transaction 1');
write_debug(l_proc_name, 'update of action NUM func id=>'||p_icc_rec.item_num_function_id);
Process_Function_Actions ( p_operation => G_TTYPE_UPDATE
,p_function_id => p_icc_rec.item_num_function_id
,p_icc_id => p_icc_rec.item_catalog_group_id
,p_transaction_id => p_icc_rec.transaction_id
,p_enable_key_attrs => p_icc_rec.enable_key_attrs_num
,p_action_id => g_old_icc_rec.item_num_action_id
,x_action_id => p_icc_rec.item_num_action_id
,x_return_status => x_return_status
,x_return_msg => x_return_msg
);
p_operation => G_TTYPE_UPDATE
,p_function_id => l_old_num_func_id
,p_icc_id => p_icc_rec.item_catalog_group_id
,p_transaction_id => p_icc_rec.transaction_id
,p_enable_key_attrs => p_icc_rec.enable_key_attrs_num
,p_action_id => g_old_icc_rec.item_num_action_id
,x_action_id => p_icc_rec.item_num_action_id
,x_return_status => x_return_status
,x_return_msg => x_return_msg
);
write_debug(l_proc_name, 'UPDATE transaction 2');
Delete_Old_Num_Gen_Method;
Process_Function_Actions ( p_operation => G_TTYPE_UPDATE
,p_function_id => p_icc_rec.item_desc_function_id
,p_icc_id => p_icc_rec.item_catalog_group_id
,p_transaction_id => p_icc_rec.transaction_id
,p_enable_key_attrs => p_icc_rec.enable_key_attrs_num
,p_action_id => g_old_icc_rec.item_desc_action_id
,x_action_id => p_icc_rec.item_desc_action_id
,x_return_status => x_return_status
,x_return_msg => x_return_msg
);
p_operation => G_TTYPE_UPDATE
,p_function_id => l_old_desc_func_id
,p_icc_id => p_icc_rec.item_catalog_group_id
,p_transaction_id => p_icc_rec.transaction_id
,p_enable_key_attrs => p_icc_rec.enable_key_attrs_desc
,p_action_id => g_old_icc_rec.item_desc_action_id
,x_action_id => p_icc_rec.item_desc_action_id
,x_return_status => x_return_status
,x_return_msg => x_return_msg
);
Delete_Old_Desc_Gen_Method;
PROCEDURE Update_Func_ICC_Hdr_Cols ( p_col_name IN VARCHAR2
,p_icc_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_return_msg OUT NOCOPY VARCHAR2
)
is
l_proc_name VARCHAR2(30) := 'Update_Func_ICC_Hdr_Cols';
UPDATE MTL_ITEM_CATALOG_GROUPS_B
SET
ITEM_NUM_ACTION_ID = decode ( p_col_name, G_NUM_GEN_FUNCTION , null, ITEM_NUM_ACTION_ID)
, ITEM_DESC_ACTION_ID = decode ( p_col_name, G_DESC_GEN_FUNCTION , null, ITEM_DESC_ACTION_ID)
,last_updated_by = G_USER_ID
,last_update_date = SYSDATE
,last_update_login = G_LOGIN_ID
WHERE item_catalog_group_id = p_icc_id
;
END Update_Func_ICC_Hdr_Cols;
PROCEDURE Update_Other_ICC_Hdr_Cols ( p_icc_rec IN ego_icc_rec_type
,x_return_status OUT NOCOPY VARCHAR2
,x_return_msg OUT NOCOPY VARCHAR2
)
is
l_proc_name VARCHAR2(30) := 'Update_Other_ICC_Hdr_Cols';
UPDATE MTL_ITEM_CATALOG_GROUPS_B
SET
ITEM_NUM_GEN_METHOD = p_icc_rec.item_num_gen_method_type
,PREFIX = p_icc_rec.prefix
,STARTING_NUMBER = p_icc_rec.starting_number
, INCREMENT_BY = p_icc_rec.increment_by
, SUFFIX = p_icc_rec.suffix
, ITEM_NUM_SEQ_NAME = p_icc_rec.item_num_seq_name
, ITEM_NUM_ACTION_ID = p_icc_rec.item_num_action_id
, ITEM_DESC_GEN_METHOD = p_icc_rec.item_desc_gen_method_type
, ITEM_DESC_ACTION_ID = p_icc_rec.item_desc_action_id
, NEW_ITEM_REQUEST_REQD = p_icc_rec.new_item_request_type
, NEW_ITEM_REQ_CHANGE_TYPE_ID = p_icc_rec.new_item_req_change_type_id
--, ITEM_NUM_SEQ_NAME
,last_updated_by = G_USER_ID
,last_update_date = SYSDATE
,last_update_login = G_LOGIN_ID
WHERE item_catalog_group_id = p_icc_rec.item_catalog_group_id
;
END Update_Other_ICC_Hdr_Cols;
PROCEDURE update_icc_id_name ( p_icc_name IN VARCHAR2, p_icc_id IN NUMBER)
IS
l_proc_name VARCHAR2(50) := 'update_icc_id_name';
UPDATE EGO_ICC_VERS_INTERFACE eivi
SET eivi.item_catalog_name = ( select icc_kfv.concatenated_segments
from mtl_item_catalog_groups_kfv icc_kfv
where icc_kfv.item_catalog_group_id = eivi.item_catalog_group_id
)
WHERE eivi.item_catalog_group_id = p_icc_id
AND eivi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( eivi.set_process_id = G_SET_PROCESS_ID)
) ;
UPDATE ego_icc_vers_interface eivi
SET eivi.item_catalog_group_id = ( select icc_kfv.item_catalog_group_id
from mtl_item_catalog_groups_kfv icc_kfv
where icc_kfv.concatenated_segments = eivi.item_catalog_name
)
WHERE eivi.item_catalog_name = P_ICC_NAME
AND eivi.item_catalog_group_id IS NULL
AND eivi.process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( eivi.set_process_id = G_SET_PROCESS_ID)
)
;
END update_icc_id_name;
IF p_icc_rec.transaction_type = G_TTYPE_UPDATE THEN
Populate_Null_Cols ( p_entity => G_ENTITY_ICC_HEADER
, p_icc_rec => p_icc_rec
, p_ag_assoc_rec => g_null_ag_assoc_rec
, p_func_param_assoc_rec => g_null_func_params_rec
, x_return_status => x_return_status
, x_return_msg => x_return_msg
);
Update_Other_ICC_Hdr_Cols ( p_icc_rec => p_icc_rec
,x_return_status => x_return_status
,x_return_msg => x_return_msg
);
ELSIF p_icc_rec.transaction_type = G_TTYPE_UPDATE THEN
NULL;
UPDATE_ICC_ID_NAME ( p_icc_id => p_icc_rec.item_catalog_group_id, p_icc_name => p_icc_rec.item_catalog_name);
IF p_ag_assoc_rec.transaction_type = G_TTYPE_DELETE THEN
EGO_EXT_FWK_PUB.Delete_Association (
p_api_version => 1.0
,p_association_id => p_ag_assoc_rec.association_id
,p_init_msg_list => fnd_api.g_FALSE
,p_commit => fnd_api.g_FALSE
,p_force => fnd_api.g_FALSE
,x_return_status => x_return_status
,x_errorcode => l_error_code
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
write_debug('EGO_EXT_FWK_PUB.Delete_Association', l_msg_data);
p_message_text => 'EGO_EXT_FWK_PUB.Delete_Association'||x_return_msg
,p_application_id => G_APPL_NAME
,p_row_identifier => p_ag_assoc_rec.transaction_id
,p_entity_code => l_entity_code
,p_table_name => G_ENTITY_ICC_HEADER_TAB
);
p_message_text => 'EGO_EXT_FWK_PUB.Delete_Association'||FND_MSG_PUB.get
,p_application_id => G_APPL_NAME
,p_row_identifier => p_ag_assoc_rec.transaction_id
,p_entity_code => l_entity_code
,p_table_name => G_ENTITY_ICC_HEADER_TAB
);
IF p_ag_assoc_rec.transaction_type = G_TTYPE_UPDATE THEN
--- nothing to update as only enabled flag, view privilege id , edit privilege id can be updated
--- enabled flag is not available through UI, privilege ids are not used as of now
null;
SELECT COUNT(1) count
from ego_func_params_b
where function_id = p_function_id;
ELSIF p_func_param_assoc_rec.transaction_type = G_TTYPE_UPDATE THEN
EGO_EXT_FWK_PUB.Update_Mapping (
p_api_version => 1.0
,p_function_id => p_func_param_assoc_rec.function_id
,p_mapped_obj_type => l_mapped_obj_type
,p_mapped_obj_pk1_value => l_action_id
,p_func_param_id => p_func_param_assoc_rec.parameter_id
,p_attr_group_id => p_func_param_assoc_rec.attr_group_id
,p_mapping_value => p_func_param_assoc_rec.attr_name
,p_new_func_param_id => p_func_param_assoc_rec.parameter_id
,p_new_mapping_value => p_func_param_assoc_rec.attr_name
,p_mapped_uom_parameter => p_func_param_assoc_rec.mapped_uom_parameter
,p_value_uom_source => p_func_param_assoc_rec.uom_param_value_type
,p_fixed_uom => p_func_param_assoc_rec.fixed_uom_value
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_FALSE
,x_return_status => X_RETURN_STATUS
,x_errorcode => l_error_code
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
ELSIF p_func_param_assoc_rec.transaction_type = G_TTYPE_DELETE THEN
EGO_EXT_FWK_PUB.Delete_Func_Param_Mapping (
p_api_version => 1.0
,p_function_id => p_func_param_assoc_rec.function_id
,p_mapped_obj_type => l_mapped_obj_type
,p_mapped_obj_pk1_value => l_action_id
,p_func_param_id => p_func_param_assoc_rec.parameter_id
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_FALSE
,x_return_status => X_RETURN_STATUS
,x_errorcode => l_error_code
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
SELECT version_seq_id
FROM (
SELECT version_seq_id
FROM ego_mtl_catalog_grp_vers_b ver
WHERE ver.item_catalog_group_id = p_item_catalog_group_id
ORDER BY ver.version_seq_id DESC
)
WHERE ROWNUM = 1;
SELECT *
FROM ego_trans_attr_vers_b ta
WHERE ta.item_catalog_group_id = p_item_catalog_group_id
AND ta.icc_version_number = p_version_seq_id
AND ta.metadata_level = 'ICC'
ORDER BY ta.sequence;
SELECT *
FROM ego_trans_attrs_vers_intf ta_int
WHERE ta_int.item_catalog_group_id = p_item_catalog_group_id
AND ta_int.icc_version_number = p_version_seq_id
AND ((ta_int.set_process_id IS NULL AND p_set_process_id IS NULL) OR
(ta_int.set_process_id = p_set_process_id))
AND ta_int.process_status = G_PROCESS_STATUS_INITIAL
AND ta_int.metadata_level = 'ICC'
ORDER BY ta_int.sequence;
SELECT flex_col.end_user_column_name
FROM ego_trans_attr_vers_b ta,
ego_fnd_df_col_usgs_ext attr ,
fnd_descr_flex_column_usages flex_col
WHERE 1=1
AND ta.item_catalog_group_id = p_item_catalog_group_id
AND ta.icc_version_number = p_version_seq_id
AND ta.attr_id = p_attr_id
AND ta.association_id = p_association_id
AND ta.metadata_level = 'ICC'
AND ta.attr_id = attr.attr_id
AND attr.descriptive_flex_context_code = flex_col.descriptive_flex_context_code
AND attr.descriptive_flexfield_name = flex_col.descriptive_flexfield_name
AND attr.application_column_name = flex_col.application_column_name
AND flex_col.descriptive_flexfield_name = 'EGO_ITEM_TRANS_ATTR_GROUP';
select max(version_seq_id)
into l_ver_id
from EGO_MTL_CATALOG_GRP_VERS_B
where item_catalog_group_id = p_icc_vers_rec.item_catalog_group_id
;
select COUNT(1)
into l_count
from EGO_MTL_CATALOG_GRP_VERS_B
where item_catalog_group_id = p_icc_vers_rec.item_catalog_group_id
;
SELECT max(start_active_date)
INTO l_date
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE item_catalog_group_id = p_icc_vers_rec.item_catalog_group_id
AND version_seq_id <> 0
;
write_debug(l_proc_name, 'Start insert');
INSERT into EGO_MTL_CATALOG_GRP_VERS_B
(item_catalog_group_id,
version_seq_id,
version_description,
start_active_date,
end_active_date,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES
(p_icc_vers_rec.item_catalog_group_id,
l_max_seq_id + 1,
p_icc_vers_rec.description,
p_start_date,
p_end_date,
G_USER_ID,
SYSDATE,
G_USER_ID,
SYSDATE,
G_LOGIN_ID);
write_debug(l_proc_name, 'inserted rows ver max seq seq=>'||l_max_seq_id||' dt'||p_icc_vers_rec.start_date
||'*'||p_icc_vers_rec.end_date);
l_token_table.DELETE;
EGO_TA_BULKLOAD_PVT.Update_Intf_Err_Trans_Attrs(
p_set_process_id => G_SET_PROCESS_ID,
p_item_catalog_group_id => p_icc_vers_rec.item_catalog_group_id,
p_icc_version_number_intf => p_icc_vers_rec.ver_seq_no,
x_return_status => l_return_status,
x_return_msg => x_return_msg);
PROCEDURE Update_existing_Version ( p_ver_start_date IN DATE
,p_new_end_date IN DATE
)
IS
BEGIN
UPDATE EGO_MTL_CATALOG_GRP_VERS_B
set end_active_date = p_new_end_date
, last_updated_by = G_USER_ID
, last_update_date = SYSDATE
, last_update_login = G_LOGIN_ID
WHERE start_active_date = p_ver_start_date
and item_catalog_group_id = p_icc_vers_rec.item_catalog_group_id
and version_seq_id <> 0;
END Update_existing_Version;
SELECT min(start_active_date) min_higher_date
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE item_catalog_group_id = p_icc_vers_rec.item_catalog_group_id
AND start_active_date > p_date
--AND p_date < nvl(end_active_date, p_date - 1)
AND version_seq_id <> 0
;
SELECT max(start_active_date) max_lower_date
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE item_catalog_group_id = p_icc_vers_rec.item_catalog_group_id
AND start_active_date < p_date
AND version_seq_id <> 0
;
l_token_table.DELETE;
l_token_table.DELETE;
Update_existing_Version ( p_ver_start_date => l_max_start_date
,p_new_end_date => p_icc_vers_rec.start_date - 1/(24*60*60)
);
Update_existing_Version ( p_ver_start_date => l_lower_date
,p_new_end_date => p_icc_vers_rec.start_date - 1/(24*60*60)
);
PROCEDURE Update_Interface_Table ( p_entity IN VARCHAR2
, p_icc_tbl IN ego_icc_tbl_type DEFAULT g_null_icc_tbl
, p_ag_assoc_tbl IN ego_ag_assoc_tbl_type DEFAULT g_null_ag_assoc_tbl
, p_func_assoc_tbl IN ego_func_param_map_tbl_type DEFAULT g_null_func_param_map_tbl
, p_icc_vers_tbl IN ego_icc_vers_tbl_type DEFAULT g_null_icc_vers_tbl
)
IS
l_proc_name VARCHAR2(30) := 'Update_Interface_Table';
UPDATE MTL_ITEM_CAT_GRPS_INTERFACE
SET
ROW = p_icc_tbl(i) -- bug 9701271
WHERE transaction_id = l_transaction_id_tbl(i) -- bug 9701271
AND process_status = G_PROCESS_STATUS_INITIAL
;
UPDATE EGO_ATTR_GRPS_ASSOC_INTERFACE
SET ROW = p_ag_assoc_tbl(i) -- bug 9701271
WHERE transaction_id = l_transaction_id_tbl(i)
AND process_status = G_PROCESS_STATUS_INITIAL
;
UPDATE EGO_FUNC_PARAMS_MAP_INTERFACE
SET ROW = p_func_assoc_tbl(i) -- bug 9701271
WHERE transaction_id = l_transaction_id_tbl(i)
AND process_status = G_PROCESS_STATUS_INITIAL
;
UPDATE EGO_ICC_VERS_INTERFACE
set ROW = p_icc_vers_tbl(i)
WHERE transaction_id = l_transaction_id_tbl(i)
AND process_status = G_PROCESS_STATUS_INITIAL
;
write_debug( l_proc_name, 'rows update =>'||SQL%ROWCOUNT);
END Update_Interface_Table;
SELECT COUNT(1) AS count_prod
FROM EGO_FUNC_PARAMS_B
where function_id = p_function_id
;
SELECT COUNT(1) AS count_map
FROM EGO_MAPPINGS_B
where function_id = p_function_id
and mapped_obj_type = 'A'
and mapped_obj_pk1_val = ( select action_id
from ego_actions_b
where function_id = p_function_id
and classification_code = to_char(p_icc_id)
)
;
SELECT G_NUM_GEN_FUNCTION column_name
FROM mtl_item_catalog_groups_b
WHERE item_catalog_group_id = p_icc_id
AND item_num_action_id = p_action_id
UNION ALL
SELECT G_DESC_GEN_FUNCTION column_name
FROM mtl_item_catalog_groups_b
WHERE item_catalog_group_id = p_icc_id
AND item_desc_action_id = p_action_id
;
IF G_Flow_Type = G_EGO_MD_INTF THEN --- if called by API then update the interface table with the process status
Update_Interface_Table (p_entity => p_entity, p_icc_tbl => p_icc_tbl);
IF G_Flow_Type = G_EGO_MD_INTF THEN --- if called by API then update the interface table with the process status
Update_Interface_Table (p_entity => p_entity, p_ag_assoc_tbl => p_ag_assoc_tbl);
FOR rec_icc_func IN ( select item_catalog_group_id
, function_id
, item_catalog_name
, function_name
, min(transaction_id) transaction_id
, count(1) as count
from table ( cast (l_func_params_tbl as ego_FN_Param_Obj_Tbl_Type))
where process_status = G_RET_STS_SUCCESS
group by item_catalog_group_id , function_id , item_catalog_name, function_name
) LOOP
--- For every icc and function_id in params table
--- get counts from interface table, get total parameters for the function from production
---
FOR rec_prod IN cur_prod_param_count ( rec_icc_func.FUNCTION_ID)
LOOP
l_count_frm_func_params := rec_prod.count_prod;
l_token_table.DELETE;
SELECT action_id
into l_action_id
FROM ego_actions_b
WHERE function_id = rec_icc_func.function_id
AND classification_code = to_char(rec_icc_func.item_catalog_group_id)
;
Update_Func_ICC_Hdr_Cols ( p_col_name => rec_icc_chk.column_name
,p_icc_id => rec_icc_func.item_catalog_group_id
,x_return_status => l_return_status
,x_return_msg => x_return_msg
);
Process_Function_Actions ( p_operation => G_TTYPE_DELETE
,p_action_id => l_action_id
,x_action_id => l_dummy
,x_return_status => l_return_status
,x_return_msg => x_return_msg
);
IF G_Flow_Type = G_EGO_MD_INTF THEN --- if called by API then update the interface table with the process status
Update_Interface_Table (p_entity => p_entity, p_func_assoc_tbl => p_func_assoc_tbl);
IF G_Flow_Type = G_EGO_MD_INTF THEN --- if called by API then update the interface table with the process status
Update_Interface_Table (p_entity => p_entity, p_icc_vers_tbl => p_icc_vers_tbl);
CURSOR cur_select_recs
is
select micgi.* , 0 icc_level
from MTL_ITEM_CAT_GRPS_INTERFACE micgi
where (PARENT_CATALOG_GROUP_NAME IS NULL
OR PARENT_CATALOG_GROUP_ID IS NOT NULL
)
AND ( ( G_SET_PROCESS_ID IS NULL )
OR
( set_process_id = G_SET_PROCESS_ID)
)
AND process_status = G_PROCESS_STATUS_INITIAL
;
CURSOR cur_select_orph_recs
is
select micgi.*
from MTL_ITEM_CAT_GRPS_INTERFACE micgi
where (PARENT_CATALOG_GROUP_NAME IS NOT NULL
AND PARENT_CATALOG_GROUP_ID IS NULL
)
AND ( ( G_SET_PROCESS_ID IS NULL )
OR
( set_process_id = G_SET_PROCESS_ID)
)
AND process_status = G_PROCESS_STATUS_INITIAL
;
CURSOR cur_select_child_recs ( p_level number)
is
select MICGI.* , p_level+1 icc_level
from MTL_ITEM_CAT_GRPS_INTERFACE MICGI
where parent_catalog_group_name
in (
select item_catalog_name
from table ( cast (l_icc_hier_tbl as ego_ICC_Hier_Tbl_Type) ) a
where a.icc_level = p_level
)
AND ( ( G_SET_PROCESS_ID IS NULL )
OR
( set_process_id = G_SET_PROCESS_ID)
)
;
l_cur_child_recs cur_select_child_recs%rowtype;
select item_catalog_name , icc_level
from table ( cast (l_icc_hier_tbl as ego_ICC_Hier_Tbl_Type ))
;
for rec_records in cur_select_recs loop
i:= i+1;
FOR rec_child_rec in cur_select_child_recs ( l_level) loop
l_icc_hier_tbl.extend;
FOR rec_orphan_icc IN cur_select_orph_recs
LOOP
l_icc_hier_tbl.extend;
PROCEDURE Update_ICC_Name_Frm_Segs
IS
--- these records have passed the bulk validate phase
---
CURSOR cur_update_name_frm_segs
IS
select micgi.*
from MTL_ITEM_CAT_GRPS_INTERFACE micgi
where item_catalog_name IS NULL
AND item_catalog_group_id IS NULL
AND ( ( G_SET_PROCESS_ID IS NULL )
OR
( set_process_id = G_SET_PROCESS_ID)
)
AND process_status = G_PROCESS_STATUS_INITIAL
FOR UPDATE OF ITEM_CATALOG_NAME;
l_proc_name VARCHAR2(40) := 'Update_ICC_Name_Frm_Segs';
FOR rec_icc_name_update in cur_update_name_frm_segs
LOOP
--
-- The segments in the seg_list array are sorted in display order.
-- i.e. sorted by segment number.
--
l_segment_array(1) := CASE rec_icc_name_update.SEGMENT1 WHEN G_MISS_CHAR THEN NULL ELSE rec_icc_name_update.SEGMENT1 END;
l_segment_array(2) := CASE rec_icc_name_update.SEGMENT2 WHEN G_MISS_CHAR THEN NULL ELSE rec_icc_name_update.SEGMENT2 END;
l_segment_array(3) := CASE rec_icc_name_update.SEGMENT3 WHEN G_MISS_CHAR THEN NULL ELSE rec_icc_name_update.SEGMENT3 END;
l_segment_array(4) := CASE rec_icc_name_update.SEGMENT4 WHEN G_MISS_CHAR THEN NULL ELSE rec_icc_name_update.SEGMENT4 END;
l_segment_array(5) := CASE rec_icc_name_update.SEGMENT5 WHEN G_MISS_CHAR THEN NULL ELSE rec_icc_name_update.SEGMENT5 END;
l_segment_array(6) := CASE rec_icc_name_update.SEGMENT6 WHEN G_MISS_CHAR THEN NULL ELSE rec_icc_name_update.SEGMENT6 END;
l_segment_array(7) := CASE rec_icc_name_update.SEGMENT7 WHEN G_MISS_CHAR THEN NULL ELSE rec_icc_name_update.SEGMENT7 END;
l_segment_array(8) := CASE rec_icc_name_update.SEGMENT8 WHEN G_MISS_CHAR THEN NULL ELSE rec_icc_name_update.SEGMENT8 END;
l_segment_array(9) := CASE rec_icc_name_update.SEGMENT9 WHEN G_MISS_CHAR THEN NULL ELSE rec_icc_name_update.SEGMENT9 END;
l_segment_array(10) := CASE rec_icc_name_update.SEGMENT10 WHEN G_MISS_CHAR THEN NULL ELSE rec_icc_name_update.SEGMENT10 END;
l_segment_array(11) := CASE rec_icc_name_update.SEGMENT11 WHEN G_MISS_CHAR THEN NULL ELSE rec_icc_name_update.SEGMENT11 END;
l_segment_array(12) := CASE rec_icc_name_update.SEGMENT12 WHEN G_MISS_CHAR THEN NULL ELSE rec_icc_name_update.SEGMENT12 END;
l_segment_array(13) := CASE rec_icc_name_update.SEGMENT13 WHEN G_MISS_CHAR THEN NULL ELSE rec_icc_name_update.SEGMENT13 END;
l_segment_array(14) := CASE rec_icc_name_update.SEGMENT14 WHEN G_MISS_CHAR THEN NULL ELSE rec_icc_name_update.SEGMENT14 END;
l_segment_array(15) := CASE rec_icc_name_update.SEGMENT15 WHEN G_MISS_CHAR THEN NULL ELSE rec_icc_name_update.SEGMENT15 END;
l_segment_array(16) := CASE rec_icc_name_update.SEGMENT16 WHEN G_MISS_CHAR THEN NULL ELSE rec_icc_name_update.SEGMENT16 END;
l_segment_array(17) := CASE rec_icc_name_update.SEGMENT17 WHEN G_MISS_CHAR THEN NULL ELSE rec_icc_name_update.SEGMENT17 END;
l_segment_array(18) := CASE rec_icc_name_update.SEGMENT18 WHEN G_MISS_CHAR THEN NULL ELSE rec_icc_name_update.SEGMENT18 END;
l_segment_array(19) := CASE rec_icc_name_update.SEGMENT19 WHEN G_MISS_CHAR THEN NULL ELSE rec_icc_name_update.SEGMENT19 END;
l_segment_array(20) := CASE rec_icc_name_update.SEGMENT20 WHEN G_MISS_CHAR THEN NULL ELSE rec_icc_name_update.SEGMENT20 END;
update MTL_ITEM_CAT_GRPS_INTERFACE
set item_catalog_name = l_concat
WHERE CURRENT OF cur_update_name_frm_segs
;
END Update_ICC_Name_Frm_Segs;
SELECT *
FROM MTL_ITEM_CAT_GRPS_INTERFACE
WHERE (
( G_SET_PROCESS_ID IS NULL )
OR
( set_process_id = G_SET_PROCESS_ID)
)
AND process_status = G_PROCESS_STATUS_INITIAL
AND item_catalog_name IN (
SELECT item_catalog_name
FROM table ( cast (l_ego_icc_hier_tbl as ego_ICC_Hier_Tbl_Type) ) a
WHERE a.icc_level = p_level
)
;
SELECT *
FROM MTL_ITEM_CAT_GRPS_INTERFACE
WHERE (
( G_SET_PROCESS_ID IS NULL )
OR
( set_process_id = G_SET_PROCESS_ID)
)
AND process_status = G_PROCESS_STATUS_INITIAL
;
SELECT *
FROM EGO_ATTR_GRPS_ASSOC_INTERFACE
WHERE (
( G_SET_PROCESS_ID IS NULL )
OR
( set_process_id = G_SET_PROCESS_ID)
)
AND process_status = G_PROCESS_STATUS_INITIAL ;
SELECT *
FROM EGO_ICC_VERS_INTERFACE
WHERE process_status = G_PROCESS_STATUS_INITIAL
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( set_process_id = G_SET_PROCESS_ID)
)
AND (
( p_icc_id IS NULL )
OR
( item_catalog_group_id = p_icc_id)
)
AND (
( p_icc_name IS NULL )
OR
( item_catalog_name = p_icc_name)
)
;
SELECT *
FROM ego_func_params_map_interface
WHERE (
( G_SET_PROCESS_ID IS NULL )
OR
( set_process_id = G_SET_PROCESS_ID)
)
AND process_status = G_PROCESS_STATUS_INITIAL;
SELECT MAX(l.lock_id) lock_id
FROM EGO_OBJECT_LOCK l
WHERE l.pk1_value = p_icc_id
AND l.object_name = G_ENTITY_ICC_LOCK
;
Update_ICC_Name_Frm_Segs;
write_debug(l_proc_name,'Inside header update ');
UPDATE MTL_ITEM_CAT_GRPS_INTERFACE
SET transaction_id = mtl_system_items_interface_s.nextval
,transaction_type = upper(transaction_type)
,creation_date = nvl(creation_date, l_sysdate)
,created_by = NVL(created_by , G_USER_ID)
,last_updated_by = G_USER_ID
,last_update_login = G_LOGIN_ID
,last_update_date = l_sysdate
,request_id = G_CONC_REQUEST_ID
,program_application_id = G_PROG_APPL_ID
,program_id = G_PROGRAM_ID
,program_update_date = l_sysdate
WHERE (
( G_SET_PROCESS_ID IS NULL )
OR
( set_process_id = G_SET_PROCESS_ID)
)
AND transaction_id IS NULL
AND process_status = G_PROCESS_STATUS_INITIAL
;
write_debug(l_proc_name,'start ag assoc update ');
UPDATE EGO_ATTR_GRPS_ASSOC_INTERFACE
SET transaction_id = mtl_system_items_interface_s.nextval
,transaction_type = upper(transaction_type)
,creation_date = nvl(creation_date, l_sysdate)
,created_by = NVL(created_by , G_USER_ID)
,last_updated_by = G_USER_ID
,last_update_login = G_LOGIN_ID
,LAST_UPDATE_DATE = l_sysdate
,request_id = G_CONC_REQUEST_ID
,program_application_id = G_PROG_APPL_ID
,program_id = G_PROGRAM_ID
,program_update_date = l_sysdate
WHERE (
( G_SET_PROCESS_ID IS NULL )
OR
( set_process_id = G_SET_PROCESS_ID)
)
AND transaction_id IS NULL
AND process_status = G_PROCESS_STATUS_INITIAL
;
write_debug(l_proc_name,'start func param update ');
UPDATE EGO_FUNC_PARAMS_MAP_INTERFACE
SET transaction_id = mtl_system_items_interface_s.nextval
,transaction_type = upper(transaction_type)
,creation_date = nvl(creation_date, l_sysdate)
,created_by = NVL(created_by , G_USER_ID)
,last_updated_by = G_USER_ID
,last_update_login = G_LOGIN_ID
,LAST_UPDATE_DATE = l_sysdate
,request_id = G_CONC_REQUEST_ID
,program_application_id = G_PROG_APPL_ID
,program_id = G_PROGRAM_ID
,program_update_date = l_sysdate
WHERE (
( G_SET_PROCESS_ID IS NULL )
OR
( set_process_id = G_SET_PROCESS_ID)
)
AND transaction_id IS NULL
AND process_status = G_PROCESS_STATUS_INITIAL
;
write_debug(l_proc_name,'start icc vers update ');
UPDATE EGO_ICC_VERS_INTERFACE
SET transaction_id = mtl_system_items_interface_s.nextval
,transaction_type = upper(transaction_type)
,creation_date = nvl(creation_date, l_sysdate)
,created_by = NVL(created_by , G_USER_ID)
,last_updated_by = G_USER_ID
,last_update_login = G_LOGIN_ID
,LAST_UPDATE_DATE = l_sysdate
,request_id = G_CONC_REQUEST_ID
,program_application_id = G_PROG_APPL_ID
,program_id = G_PROGRAM_ID
,program_update_date = l_sysdate
WHERE (
( G_SET_PROCESS_ID IS NULL )
OR
( set_process_id = G_SET_PROCESS_ID)
)
AND transaction_id IS NULL
AND process_status = G_PROCESS_STATUS_INITIAL
;
SELECT count(1) count
FROM MTL_ITEM_CAT_GRPS_INTERFACE
WHERE (
( G_SET_PROCESS_ID IS NULL )
OR
( set_process_id = G_SET_PROCESS_ID)
)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_STATUS_INITIAL
;
SELECT count(1) count
FROM EGO_ATTR_GRPS_ASSOC_INTERFACE
WHERE (
( G_SET_PROCESS_ID IS NULL )
OR
( set_process_id = G_SET_PROCESS_ID)
)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_STATUS_INITIAL
;
SELECT count(1) count
FROM EGO_FUNC_PARAMS_MAP_INTERFACE
WHERE (
( G_SET_PROCESS_ID IS NULL )
OR
( set_process_id = G_SET_PROCESS_ID)
)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_STATUS_INITIAL
;
SELECT count(1) count
FROM EGO_ICC_VERS_INTERFACE
WHERE (
( G_SET_PROCESS_ID IS NULL )
OR
( set_process_id = G_SET_PROCESS_ID)
)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_STATUS_INITIAL
;
PROCEDURE Delete_Processed_ICC ( p_set_process_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_return_msg OUT NOCOPY VARCHAR2
)
IS
l_proc_name VARCHAR2(30) := 'Delete_Processed_ICC';
DELETE FROM MTL_ITEM_CAT_GRPS_INTERFACE
WHERE transaction_id IS NOT NULL
AND process_status = G_PROCESS_STATUS_SUCCESS
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( set_process_id = G_SET_PROCESS_ID)
);
DELETE FROM EGO_ATTR_GRPS_ASSOC_INTERFACE
WHERE transaction_id IS NOT NULL
AND process_status = G_PROCESS_STATUS_SUCCESS
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( set_process_id = G_SET_PROCESS_ID)
);
DELETE FROM EGO_ICC_VERS_INTERFACE
WHERE transaction_id IS NOT NULL
AND process_status = G_PROCESS_STATUS_SUCCESS
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( set_process_id = G_SET_PROCESS_ID)
);
DELETE FROM ego_func_params_map_interface
WHERE transaction_id IS NOT NULL
AND process_status = G_PROCESS_STATUS_SUCCESS
AND (
( G_SET_PROCESS_ID IS NULL )
OR
( set_process_id = G_SET_PROCESS_ID)
);
END Delete_Processed_Icc;