[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1 INTO l_err_rec
FROM EGO_TRANS_ATTRS_VERS_INTF
WHERE item_catalog_group_id= p_item_catalog_group_id
AND icc_version_number = p_icc_version_number_intf
AND process_status=G_ERROR_RECORD --3
AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id))
AND ROWNUM=1;
SELECT Count(1) INTO l_rec_exists
FROM EGO_TRANS_ATTRS_VERS_INTF
WHERE item_catalog_group_id= p_item_catalog_group_id
AND icc_version_number= p_icc_version_number_intf
AND process_status=G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id))
AND ROWNUM=1;
SELECT application_id
INTO G_APPLICATION_ID
FROM fnd_application
WHERE application_short_name = G_APP_NAME;
UPDATE EGO_TRANS_ATTRS_VERS_INTF
SET transaction_id = mtl_system_items_interface_s.nextval,
transaction_type = Upper(transaction_type),
created_by = Nvl(created_by,g_user_id),
creation_date = Nvl(creation_date,SYSDATE),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID,
/* bug 9752139*/
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 ((p_set_process_id IS NULL) OR (set_process_id = p_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,
'EGO_TRANS_ATTRS_VERS_INTF',
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
G_BO_IDENTIFIER,
G_ENTITY_IDENTIFIER,
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_LOGIN_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_TRANS_ATTRS_VERS_INTF
WHERE ((transaction_type is NULL) or (transaction_type NOT IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC)))
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
UPDATE EGO_TRANS_ATTRS_VERS_INTF
SET process_status =G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE ((transaction_type is NULL) or (transaction_type NOT IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC))) /* bug 9752139 */
AND transaction_id IS NOT NULL
AND process_status=G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_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,
'EGO_TRANS_ATTRS_VERS_INTF',
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
G_BO_IDENTIFIER,
G_ENTITY_IDENTIFIER,
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_LOGIN_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_TRANS_ATTRS_VERS_INTF
WHERE transaction_type IN (G_UPDATE,G_DELETE,G_SYNC)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
UPDATE EGO_TRANS_ATTRS_VERS_INTF
SET process_status =G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE transaction_type IN (G_UPDATE,G_DELETE,G_SYNC)
AND transaction_id IS NOT NULL
AND process_status=G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_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,
'EGO_TRANS_ATTRS_VERS_INTF',
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
G_BO_IDENTIFIER,
G_ENTITY_IDENTIFIER,
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_LOGIN_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_TRANS_ATTRS_VERS_INTF
WHERE transaction_type IN (G_CREATE)
AND transaction_id IS NOT NULL
AND icc_version_number=0
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
UPDATE EGO_TRANS_ATTRS_VERS_INTF
SET process_status =G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE transaction_type IN (G_CREATE)
AND transaction_id IS NOT NULL
AND icc_version_number=0
AND process_status=G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_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,
'EGO_TRANS_ATTRS_VERS_INTF',
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
G_BO_IDENTIFIER,
G_ENTITY_IDENTIFIER,
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_LOGIN_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_TRANS_ATTRS_VERS_INTF
WHERE item_catalog_group_id IS NULL
AND item_catalog_group_name IS NOT NULL
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
UPDATE EGO_TRANS_ATTRS_VERS_INTF
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE item_catalog_group_id IS NULL
AND item_catalog_group_name IS NOT NULL
AND transaction_id IS NOT NULL
AND process_status=G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_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,
'EGO_TRANS_ATTRS_VERS_INTF',
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
G_BO_IDENTIFIER,
G_ENTITY_IDENTIFIER,
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_LOGIN_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_TRANS_ATTRS_VERS_INTF
WHERE item_catalog_group_id IS NULL
AND item_catalog_group_name IS NULL
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
UPDATE EGO_TRANS_ATTRS_VERS_INTF
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE item_catalog_group_id IS NULL
AND item_catalog_group_name IS NULL
AND transaction_id IS NOT NULL
AND process_status=G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_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,
'EGO_TRANS_ATTRS_VERS_INTF',
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
G_BO_IDENTIFIER,
G_ENTITY_IDENTIFIER,
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_LOGIN_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_TRANS_ATTRS_VERS_INTF ETAVI
WHERE ETAVI.item_catalog_group_id IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM mtl_item_catalog_groups micg
WHERE micg.ITEM_CATALOG_GROUP_ID=ETAVI.ITEM_CATALOG_GROUP_ID)
AND ETAVI.transaction_id IS NOT NULL
AND ETAVI.process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (ETAVI.set_process_id=p_set_process_id));
UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVI
SET ETAVI.process_status = G_ERROR_RECORD,
ETAVI.last_updated_by = G_USER_ID,
ETAVI.last_update_date = SYSDATE,
ETAVI.last_update_login = G_LOGIN_ID
WHERE ETAVI.item_catalog_group_id IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM mtl_item_catalog_groups micg
WHERE micg.ITEM_CATALOG_GROUP_ID=ETAVI.ITEM_CATALOG_GROUP_ID)
AND ETAVI.transaction_id IS NOT NULL
AND ETAVI.process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (ETAVI.set_process_id=p_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,
'EGO_TRANS_ATTRS_VERS_INTF',
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
G_BO_IDENTIFIER,
G_ENTITY_IDENTIFIER,
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_LOGIN_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_TRANS_ATTRS_VERS_INTF
WHERE value_set_id IS NULL
AND Value_set_name IS NOT NULL
AND transaction_type IN (G_CREATE,G_UPDATE,G_SYNC)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
UPDATE EGO_TRANS_ATTRS_VERS_INTF
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE value_set_id IS NULL
AND Value_set_name IS NOT NULL
AND transaction_type IN (G_CREATE,G_UPDATE,G_SYNC)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_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,
'EGO_TRANS_ATTRS_VERS_INTF',
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
G_BO_IDENTIFIER,
G_ENTITY_IDENTIFIER,
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_LOGIN_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_TRANS_ATTRS_VERS_INTF ETAVT
WHERE value_set_id IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM fnd_flex_value_sets ffvs
WHERE ffvs.flex_value_set_id = ETAVT.value_set_id)
AND transaction_type IN (G_CREATE,G_UPDATE,G_SYNC)
AND ETAVT.transaction_id IS NOT NULL
AND ETAVT.process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (ETAVT.set_process_id=p_set_process_id));
UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
SET ETAVT.process_status =G_ERROR_RECORD,
ETAVT.last_updated_by = G_USER_ID,
ETAVT.last_update_date = SYSDATE,
ETAVT.last_update_login = G_LOGIN_ID
WHERE ETAVT.value_set_id IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM fnd_flex_value_sets
WHERE flex_value_set_id = ETAVT.value_set_id)
AND transaction_type IN (G_CREATE,G_UPDATE,G_SYNC)
AND ETAVT.transaction_id IS NOT NULL
AND ETAVT.process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (ETAVT.set_process_id=p_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,
'EGO_TRANS_ATTRS_VERS_INTF',
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
G_BO_IDENTIFIER,
G_ENTITY_IDENTIFIER,
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_LOGIN_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_TRANS_ATTRS_VERS_INTF
WHERE attr_id IS NULL
AND attr_name IS NOT NULL
AND transaction_type IN (G_UPDATE,G_DELETE)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
UPDATE EGO_TRANS_ATTRS_VERS_INTF
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE attr_id IS NULL
AND attr_name IS NOT NULL
AND transaction_type IN (G_UPDATE,G_DELETE)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
/* converting sync to create or update */
/* if attr_name is given with sync and not exists */
UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
SET transaction_type = G_CREATE,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE attr_id IS NULL
AND attr_name IS NOT NULL
AND transaction_type =G_SYNC
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (ETAVT.set_process_id=p_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,
'EGO_TRANS_ATTRS_VERS_INTF',
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
G_BO_IDENTIFIER,
G_ENTITY_IDENTIFIER,
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_LOGIN_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_TRANS_ATTRS_VERS_INTF ETAVT
WHERE attr_id IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM EGO_TRANS_ATTR_VERS_B ETAVB
WHERE ETAVB.attr_id = ETAVT.attr_id
AND ETAVB.item_catalog_group_id=ETAVT.item_catalog_group_id
AND ETAVB.icc_version_number=0)
AND ETAVT.transaction_type IN (G_UPDATE,G_DELETE,G_SYNC)
AND ETAVT.transaction_id IS NOT NULL
AND ETAVT.process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (ETAVT.set_process_id=p_set_process_id));
UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
SET ETAVT.process_status = G_ERROR_RECORD,
ETAVT.last_updated_by = G_USER_ID,
ETAVT.last_update_date = SYSDATE,
ETAVT.last_update_login = G_LOGIN_ID
WHERE attr_id IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM EGO_TRANS_ATTR_VERS_B ETAVB
WHERE ETAVB.attr_id = ETAVT.attr_id
AND ETAVB.item_catalog_group_id=ETAVT.item_catalog_group_id
AND ETAVB.icc_version_number=0)
AND ETAVT.transaction_type IN (G_UPDATE,G_DELETE,G_SYNC)
AND ETAVT.transaction_id IS NOT NULL
AND ETAVT.process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (ETAVT.set_process_id=p_set_process_id));
ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Checking if attr_name given with sync - convert to Update');
UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
SET transaction_type = G_UPDATE,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE attr_id IS NOT NULL
AND EXISTS (
SELECT 1
FROM EGO_TRANS_ATTR_VERS_B ETAVB
WHERE ETAVB.attr_id = ETAVT.attr_id
AND ETAVB.item_catalog_group_id=ETAVT.item_catalog_group_id
AND ETAVB.icc_version_number=0)
--AND attr_name IS NOT NULL
AND transaction_type =G_SYNC
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (ETAVT.set_process_id=p_set_process_id));
update as here we are sure we got the attr_id so populating attr_name
if not given*/
UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
SET attr_name= (SELECT attr_name
FROM ego_attrs_v EAV
WHERE EAV.attr_id= ETAVT.attr_id
)
WHERE ETAVT.attr_id IS NOT NULL
AND ETAVT.attr_display_name IS NOT NULL
AND ETAVT.transaction_type IN (G_UPDATE)
AND ETAVT.process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id));
UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
SET (uom_class,
default_value,
rejected_value,
required_flag,
readonly_flag,
hidden_flag,
searchable_flag,
check_eligibility,
value_set_id,
attr_display_name,
sequence) =
(SELECT nvl(a.uom_class,b.uom_class),
nvl(a.default_value,b.default_value),
nvl(a.rejected_value,b.rejected_value),
nvl(a.required_flag,b.required_flag),
nvl(a.readonly_flag,b.readonly_flag),
nvl(a.hidden_flag,b.hidden_flag),
nvl(a.searchable_flag,b.searchable_flag),
nvl(a.check_eligibility,b.check_eligibility),
nvl(a.value_set_id,b.value_set_id),
nvl(a.attr_display_name,b.attr_display_name),
b.sequence
FROM EGO_TRANS_ATTRS_VERS_INTF a,EGO_TRANS_ATTR_VERS_B b
WHERE a.attr_id=b.attr_id
AND a.item_catalog_group_id=b.item_catalog_group_id
AND b.icc_version_number=0
AND a.attr_id=ETAVT.attr_id)
WHERE attr_id IS NOT NULL
AND ETAVT.attr_name IS NOT NULL
AND ETAVT.transaction_type IN (G_UPDATE)
AND ETAVT.process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id = p_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,
'EGO_TRANS_ATTRS_VERS_INTF',
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
G_BO_IDENTIFIER,
G_ENTITY_IDENTIFIER,
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_LOGIN_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_TRANS_ATTRS_VERS_INTF
WHERE attr_id IS NULL
AND attr_display_name IS NOT NULL
AND transaction_type IN (G_UPDATE,G_DELETE)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
UPDATE EGO_TRANS_ATTRS_VERS_INTF
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE attr_id IS NULL
AND attr_display_name IS NOT NULL
AND transaction_type IN (G_UPDATE,G_DELETE)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
/* converting sync to create or update */
/* if attr_display_name is given with sync and not exists */
UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
SET transaction_type = G_CREATE,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE attr_id IS NULL
AND attr_display_name IS NOT NULL
AND transaction_type =G_SYNC
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (ETAVT.set_process_id=p_set_process_id));
UPDATE EGO_TRANS_ATTRS_VERS_INTF
SET process_status =G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE attr_id IS NULL
AND attr_display_name IS NULL
AND attr_name IS NULL
AND transaction_type IN (G_UPDATE,G_DELETE,G_SYNC)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
/* Association id not able to convert in case of upate,delete,sync
or association id provided doesnt exists in table*/
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,
'EGO_TRANS_ATTRS_VERS_INTF',
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
G_BO_IDENTIFIER,
G_ENTITY_IDENTIFIER,
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_LOGIN_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_TRANS_ATTRS_VERS_INTF
WHERE attr_id IS NOT NULL
AND item_catalog_group_id IS NOT NULL
AND association_id IS NULL
AND transaction_type IN (G_UPDATE,G_DELETE,G_SYNC)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
UPDATE EGO_TRANS_ATTRS_VERS_INTF
SET process_status =G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE attr_id IS NOT NULL
AND item_catalog_group_id IS NOT NULL
AND association_id IS NULL
AND transaction_type IN (G_UPDATE,G_DELETE,G_SYNC)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_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,
'EGO_TRANS_ATTRS_VERS_INTF',
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
G_BO_IDENTIFIER,
G_ENTITY_IDENTIFIER,
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_LOGIN_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_TRANS_ATTRS_VERS_INTF ETAVT
WHERE attr_id IS NOT NULL
AND item_catalog_group_id IS NOT NULL
AND association_id IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM EGO_TRANS_ATTR_VERS_B ETAVB
WHERE ETAVB.association_id = ETAVT.association_id
AND ETAVB.item_Catalog_group_id= ETAVT.item_Catalog_group_id
AND ETAVB.icc_version_number=0)
AND ETAVT.transaction_type IN (G_UPDATE,G_DELETE,G_SYNC)
AND ETAVT.transaction_id IS NOT NULL
AND ETAVT.process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (ETAVT.set_process_id=p_set_process_id));
UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
SET process_status =G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE attr_id IS NOT NULL
AND item_catalog_group_id IS NOT NULL
AND association_id IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM EGO_TRANS_ATTR_VERS_B ETAVB
WHERE ETAVB.association_id = ETAVT.association_id
AND ETAVB.item_Catalog_group_id= ETAVT.item_Catalog_group_id
AND ETAVB.icc_version_number=0)
AND transaction_type IN (G_UPDATE,G_DELETE,G_SYNC)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_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,
'EGO_TRANS_ATTRS_VERS_INTF',
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
G_BO_IDENTIFIER,
G_ENTITY_IDENTIFIER,
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_LOGIN_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_TRANS_ATTRS_VERS_INTF
WHERE transaction_type IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC)
AND transaction_id IS NOT NULL
AND ((required_flag IS NOT NULL) OR (readonly_flag IS NOT NULL)
OR (hidden_flag IS NOT NULL) OR (searchable_flag IS NOT NULL)
OR (check_eligibility IS NOT NULL))
AND ( (required_flag NOT IN ('Y','N')) OR (readonly_flag NOT IN ('Y','N')) OR (hidden_flag NOT IN ('Y','N')) OR (searchable_flag NOT IN ('Y','N'))
OR (check_eligibility NOT IN ('Y','N')))
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE ((required_flag IS NOT NULL) OR (readonly_flag IS NOT NULL)
OR (hidden_flag IS NOT NULL) OR (searchable_flag IS NOT NULL)
OR (check_eligibility IS NOT NULL))
AND ((Upper(required_flag) NOT IN ('Y','N')) OR (Upper(readonly_flag) NOT IN ('Y','N'))
OR (Upper(hidden_flag) NOT IN ('Y','N')) OR (Upper(searchable_flag) NOT IN ('Y','N'))
OR (Upper(check_eligibility) NOT IN ('Y','N')))
AND transaction_type IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_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,
'EGO_TRANS_ATTRS_VERS_INTF',
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
G_BO_IDENTIFIER,
G_ENTITY_IDENTIFIER,
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_LOGIN_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_TRANS_ATTRS_VERS_INTF
WHERE transaction_type IN (G_CREATE)
AND transaction_id IS NOT NULL
AND data_type IS NOT NULL
AND Upper(data_type) NOT IN ('C','A','N','X','Y')
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE data_type IS NOT NULL
AND Upper(data_type) NOT IN ('C','A','N','X','Y')
AND transaction_type IN (G_CREATE)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_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,
'EGO_TRANS_ATTRS_VERS_INTF',
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
G_BO_IDENTIFIER,
G_ENTITY_IDENTIFIER,
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_LOGIN_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_TRANS_ATTRS_VERS_INTF
WHERE transaction_type IN (G_CREATE)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id))
AND ((Association_id IS NOT NULL) OR (Attr_id IS NOT NULL));
UPDATE EGO_TRANS_ATTRS_VERS_INTF
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE transaction_type IN (G_CREATE)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id))
AND ((Association_id IS NOT NULL) OR (Attr_id IS NOT NULL));
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,
'EGO_TRANS_ATTRS_VERS_INTF',
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
G_BO_IDENTIFIER,
G_ENTITY_IDENTIFIER,
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_LOGIN_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_TRANS_ATTRS_VERS_INTF
WHERE transaction_type IN (G_CREATE)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id))
AND ((attr_name IS NULL) OR (attr_display_name IS NULL) OR (SEQUENCE IS NULL)) ;
UPDATE EGO_TRANS_ATTRS_VERS_INTF
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE transaction_type IN (G_CREATE)
AND transaction_id IS NOT NULL
AND process_status=G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id))
AND ((attr_name IS NULL) OR (attr_display_name IS NULL) OR (SEQUENCE IS NULL));
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,
'EGO_TRANS_ATTRS_VERS_INTF',
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
G_BO_IDENTIFIER,
G_ENTITY_IDENTIFIER,
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_LOGIN_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_TRANS_ATTRS_VERS_INTF
WHERE transaction_type IN (G_CREATE)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id))
AND icc_version_number IS NULL;
UPDATE EGO_TRANS_ATTRS_VERS_INTF
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE transaction_type IN (G_CREATE)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id))
AND icc_version_number IS NULL;
UPDATE EGO_TRANS_ATTRS_VERS_INTF
SET data_type = G_CHAR_DATA_TYPE
WHERE transaction_type=G_CREATE
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id))
AND data_type IS NULL;
UPDATE EGO_TRANS_ATTRS_VERS_INTF
SET display_flag = 'T'
WHERE transaction_type=G_CREATE
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id));
UPDATE EGO_TRANS_ATTRS_VERS_INTF
SET Metadata_level = 'ICC'
WHERE transaction_type=G_CREATE
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id));
/*Assigning icc_version_no as 0 if not provided in update and delete*/
UPDATE EGO_TRANS_ATTRS_VERS_INTF
SET icc_version_number = 0
WHERE transaction_type IN (G_UPDATE,G_DELETE)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id))
AND icc_version_number IS NULL;
ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Icc_Version_Number greater than zero not allowed while UPDATE and DELETE');
/* Icc_Version_Number greater than zero not allowed while UPDATE and DELETE*/
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,
'EGO_TRANS_ATTRS_VERS_INTF',
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
G_BO_IDENTIFIER,
G_ENTITY_IDENTIFIER,
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_LOGIN_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_TRANS_ATTRS_VERS_INTF
WHERE transaction_type IN (G_UPDATE,G_DELETE)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id))
AND icc_version_number>0;
/* Icc_Version_Number greater than zero not allowed while UPDATE and DELETE*/
UPDATE EGO_TRANS_ATTRS_VERS_INTF
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE transaction_type IN (G_UPDATE,G_DELETE)
AND transaction_id IS NOT NULL
AND process_status=G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id))
AND Nvl(icc_version_number,0)>0;
ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Association_id and attr_id should not be null for DELETE');
/*Association_id and attr_id should not be null for DELETE*/
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,
'EGO_TRANS_ATTRS_VERS_INTF',
G_MESSAGE_NAME,
G_MESSAGE_TEXT,
G_BO_IDENTIFIER,
G_ENTITY_IDENTIFIER,
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_LOGIN_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_TRANS_ATTRS_VERS_INTF
WHERE transaction_type IN (G_DELETE)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id))
AND ((association_id IS NULL) OR (attr_id IS NULL));
/*Association_id and attr_id should not be null for DELETE */
UPDATE EGO_TRANS_ATTRS_VERS_INTF
SET process_status = G_ERROR_RECORD,
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login = G_LOGIN_ID
WHERE transaction_type IN (G_DELETE)
AND transaction_id IS NOT NULL
AND process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id))
AND ((association_id IS NULL) OR (attr_id IS NULL));
UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
SET item_catalog_group_id = p_item_catalog_group_id
WHERE ETAVT.item_catalog_group_name IS NOT NULL
AND ETAVT.item_catalog_group_id IS NULL
AND ETAVT.process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id))
AND Upper(ETAVT.item_catalog_group_name) =Upper(p_item_catalog_group_name); -- added to make it ICC specific.
UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
SET item_catalog_group_id = (SELECt icc_kfv.item_catalog_group_id
FROM mtl_item_catalog_groups_kfv icc_kfv
where Upper(icc_kfv.concatenated_segments) = Upper(ETAVT.item_catalog_group_name)
)
WHERE ETAVT.item_catalog_group_name IS NOT NULL
AND ETAVT.item_catalog_group_id IS NULL
--AND ETAVT.transaction_type IN (G_CREATE,G_UPDATE,G_DELETE_G_SYNC)
AND ETAVT.process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id));*/
UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
SET value_set_id = ( SELECT flex_value_set_id
FROM fnd_flex_value_sets
WHERE Upper(flex_value_set_name) = Upper(ETAVT.value_set_name)
)
WHERE ETAVT.value_set_name IS NOT NULL
AND ETAVT.value_set_id IS NULL
AND ETAVT.transaction_type IN (G_CREATE,G_UPDATE,G_SYNC)
AND ETAVT.process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id));
UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
SET (attr_id/*,attr_display_name,sequence*/) = ( SELECT attr_id/*,attr_display_name,sequence*/
FROM ego_trans_attr_vers_b
WHERE attr_id IN ( SELECT efdcue.attr_id
FROM fnd_descr_flex_column_usages fdfcu,
ego_fnd_df_col_usgs_ext efdcue
WHERE fdfcu.application_id = efdcue.application_id
AND fdfcu.descriptive_flexfield_name = efdcue.descriptive_flexfield_name
AND fdfcu.descriptive_flex_context_code = efdcue.descriptive_flex_context_code
AND fdfcu.application_column_name = efdcue.application_column_name
AND fdfcu.application_id = G_APPLICATION_ID
AND fdfcu.descriptive_flexfield_name = 'EGO_ITEM_TRANS_ATTR_GROUP'
AND fdfcu.descriptive_flex_context_code IN (SELECT attr_group_name
FROM EGO_OBJ_ATTR_GRP_ASSOCS_V
WHERE association_id in (SELECT association_id
FROM EGO_OBJ_AG_ASSOCS_B
WHERE classification_code=ETAVT.item_catalog_group_id)
AND ATTR_GROUP_TYPE= 'EGO_ITEM_TRANS_ATTR_GROUP'
)
AND Upper(fdfcu.end_user_column_name) = Upper(ETAVT.attr_name)
)
AND item_catalog_group_id=ETAVT.item_catalog_group_id
AND icc_version_number=0 -- we only allow update on draft
)
WHERE ETAVT.attr_name IS NOT NULL
AND ETAVT.attr_id IS NULL
AND ETAVT.transaction_type IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC)
AND ETAVT.process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id));
UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
SET attr_id = ( SELECT attr_id
FROM EGO_TRANS_ATTR_VERS_B
WHERE Upper(attr_display_name) = Upper(ETAVT.attr_display_name)
AND item_catalog_group_id=ETAVT.item_catalog_group_id
AND icc_version_number=0
)
WHERE ETAVT.attr_display_name IS NOT NULL
AND ETAVT.attr_id IS NULL
AND ETAVT.transaction_type IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC)
AND ETAVT.process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id));
UPDATE EGO_TRANS_ATTRS_VERS_INTF ETAVT
SET association_id = ( SELECT association_id
FROM EGO_TRANS_ATTR_VERS_B
WHERE attr_id= ETAVT.attr_id
AND item_catalog_group_id=ETAVT.item_catalog_group_id
AND icc_version_number=0
)
WHERE ETAVT.attr_id IS NOT NULL
AND ETAVT.item_catalog_group_id IS NOT NULL
AND ETAVT.association_id IS NULL
AND ETAVT.icc_version_number=0
AND ETAVT.transaction_type IN (G_UPDATE,G_DELETE,G_SYNC)
AND ETAVT.process_status = G_PROCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id));
SELECT *
FROM ego_trans_attrs_vers_intf
WHERE ((p_set_process_id IS NULL) OR (set_process_id = p_set_process_id))
AND transaction_id IS NOT NULL
AND process_status=G_PROCESS_RECORD
AND item_catalog_group_id=p_item_catalog_group_id /* for integration with version*/
AND icc_version_number= p_icc_version_number_intf
ORDER BY transaction_type,icc_version_number;
p_ta_intf_tbl(i).Last_Updated_By,
p_ta_intf_tbl(i).Last_Update_Date,
p_ta_intf_tbl(i).Last_Update_Login,
p_ta_intf_tbl(i).Program_Application_Id,
p_ta_intf_tbl(i).Program_Id,
p_ta_intf_tbl(i).Program_Update_Date,
p_ta_intf_tbl(i).Request_Id,
p_ta_intf_tbl(i).Item_Catalog_Group_Id,
p_ta_intf_tbl(i).Attr_Name,
p_ta_intf_tbl(i).Attr_Display_Name,
p_ta_intf_tbl(i).Data_Type,
p_ta_intf_tbl(i).display_flag,
p_ta_intf_tbl(i).Value_Set_Name
);
SELECT Decode(p_ta_intf_tbl(i).transaction_type,'CREATE','Create_Transaction_Attribute',
'UPDATE','Update_Transaction_Attribute',
'DELETE','Delete_Transaction_Attribute') INTO G_TOKEN_TBL(4).Token_Value
FROM dual;
so messages will also get rollback. So if it get added to stack we can print and insert
to interface_error table again*/
Error_Handler.Add_Error_Message
(
p_message_name => 'EGO_ENTITY_API_FAILED'
,p_application_id => G_APP_NAME
,p_message_type => G_RET_STS_ERROR
,p_entity_code => G_Entity_Identifier
,p_row_identifier => p_ta_intf_tbl(i).transaction_id
,p_table_name => G_Table_Name
,p_token_tbl => G_TOKEN_TBL
,p_addto_fnd_stack=> 'Y'
);
G_TOKEN_TBL.DELETE;
/* if record successful then update the intf table with success(7)*/
IF G_FLOW_TYPE=G_EGO_MD_INTF THEN
Update_Intf_Trans_Attrs(p_ta_intf_tbl => p_ta_intf_tbl,
x_return_status => l_return_status,
x_return_msg => x_return_msg);
SELECT application_id INTO G_APPLICATION_ID
FROM fnd_application
WHERE application_short_name=G_APP_NAME;
SELECT mtl_system_items_interface_s.NEXTVAL,Upper(p_ta_intf_tbl(i).transaction_type)
INTO p_ta_intf_tbl(i).transaction_id,p_ta_intf_tbl(i).transaction_type
FROM dual;
IF p_ta_intf_tbl(i).transaction_type NOT IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC) THEN
ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Transaction Type '
||p_ta_intf_tbl(i).transaction_type
||'Is not Valid');
IF (p_ta_intf_tbl(i).transaction_type IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC)
AND p_ta_intf_tbl(i).item_catalog_group_id IS NULL
AND p_ta_intf_tbl(i).item_catalog_group_name IS NOT NULL) THEN
BEGIN
SELECt icc_kfv.item_catalog_group_id INTO p_ta_intf_tbl(i).item_catalog_group_id
FROM mtl_item_catalog_groups_kfv icc_kfv
WHERE Upper(icc_kfv.concatenated_segments) = Upper(p_ta_intf_tbl(i).item_catalog_group_name);
IF (p_ta_intf_tbl(i).transaction_type IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC)
AND p_ta_intf_tbl(i).value_set_id IS NULL
AND p_ta_intf_tbl(i).value_set_name IS NOT NULL) THEN
BEGIN
SELECT flex_value_set_id INTO p_ta_intf_tbl(i).value_set_id
FROM fnd_flex_value_sets
WHERE Upper(flex_value_set_name) = Upper(p_ta_intf_tbl(i).value_set_name);
IF (p_ta_intf_tbl(i).transaction_type IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC)
AND p_ta_intf_tbl(i).attr_id IS NULL
AND p_ta_intf_tbl(i).attr_name IS NOT NULL) THEN
BEGIN
SELECT attr_id INTO p_ta_intf_tbl(i).attr_id
FROM EGO_TRANS_ATTR_VERS_B
WHERE attr_id IN ( SELECT efdcue.attr_id
FROM fnd_descr_flex_column_usages fdfcu,
ego_fnd_df_col_usgs_ext efdcue
WHERE fdfcu.application_id = efdcue.application_id
AND fdfcu.descriptive_flexfield_name = efdcue.descriptive_flexfield_name
AND fdfcu.descriptive_flex_context_code = efdcue.descriptive_flex_context_code
AND fdfcu.application_column_name = efdcue.application_column_name
AND fdfcu.application_id = G_APPLICATION_ID
AND fdfcu.descriptive_flexfield_name = 'EGO_ITEM_TRANS_ATTR_GROUP'
AND fdfcu.descriptive_flex_context_code IN (SELECT attr_group_name
FROM EGO_OBJ_ATTR_GRP_ASSOCS_V
WHERE association_id in (SELECT association_id
FROM EGO_OBJ_AG_ASSOCS_B
WHERE classification_code=p_ta_intf_tbl(i).item_catalog_group_id)
AND ATTR_GROUP_TYPE= 'EGO_ITEM_TRANS_ATTR_GROUP'
)
AND Upper(fdfcu.end_user_column_name) = Upper(p_ta_intf_tbl(i).attr_name)
)
AND item_catalog_group_id=p_ta_intf_tbl(i).item_catalog_group_id
AND icc_version_number=0; -- we only allow update on draft;
IF (p_ta_intf_tbl(i).transaction_type IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC)
AND p_ta_intf_tbl(i).attr_id IS NULL
AND p_ta_intf_tbl(i).attr_display_name IS NOT NULL
AND p_ta_intf_tbl(i).attr_name IS NULL)/* extra condition becoz if attr_name is given then i could have*/
THEN /* resolved sync and get the attr_id in previour attr_name to attr_id*/
BEGIN
SELECT attr_id INTO p_ta_intf_tbl(i).attr_id
FROM EGO_TRANS_ATTR_VERS_B
WHERE Upper(attr_display_name) = Upper(p_ta_intf_tbl(i).attr_display_name)
AND item_catalog_group_id=p_ta_intf_tbl(i).item_catalog_group_id
AND icc_version_number=0;
/* Getting associaton_id from icc_id and attr_id, need this for update and delete*/
IF (p_ta_intf_tbl(i).transaction_type IN (G_UPDATE,G_DELETE,G_SYNC)
AND p_ta_intf_tbl(i).attr_id IS NOT NULL
AND p_ta_intf_tbl(i).item_catalog_group_id IS NOT NULL
AND p_ta_intf_tbl(i).association_id is NULL) THEN
BEGIN
SELECT association_id INTO p_ta_intf_tbl(i).association_id
FROM EGO_TRANS_ATTR_VERS_B
WHERE attr_id= p_ta_intf_tbl(i).attr_id
AND item_catalog_group_id= p_ta_intf_tbl(i).item_catalog_group_id
AND icc_version_number=0;
IF p_ta_intf_tbl(i).transaction_type IN (G_CREATE,G_UPDATE,G_DELETE,G_SYNC) THEN
IF (p_ta_intf_tbl(i).item_catalog_group_id IS NULL
AND p_ta_intf_tbl(i).item_catalog_group_name IS NULL) THEN /*if both ICC_ID and ICC_NAME is NULL*/
ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'ERR : ICC Id and ICC_NAME both NULL ');
SELECT item_catalog_group_id INTO l_id_exists
FROM mtl_item_catalog_groups micg
WHERE micg.ITEM_CATALOG_GROUP_ID=p_ta_intf_tbl(i).item_catalog_group_id;
IF p_ta_intf_tbl(i).transaction_type IN (G_CREATE,G_UPDATE,G_SYNC) THEN
/* if given value_set_id not exists */
IF (p_ta_intf_tbl(i).value_set_id IS NOT NULL) THEN
BEGIN
SELECT flex_value_set_id INTO l_id_exists
FROM fnd_flex_value_sets ffvs
WHERE ffvs.flex_value_set_id = p_ta_intf_tbl(i).value_set_id ;
END IF;-- If Transaction_type in except delete
ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'attr_id Validations and conversion of sync to create/update ');
IF p_ta_intf_tbl(i).transaction_type IN (G_UPDATE,G_DELETE,G_SYNC) THEN
/* if given attr_id not exists */
IF (p_ta_intf_tbl(i).attr_id IS NOT NULL) THEN
BEGIN
SELECT attr_id INTO l_id_exists
FROM EGO_TRANS_ATTR_VERS_B ETAVB
WHERE ETAVB.attr_id = p_ta_intf_tbl(i).attr_id
AND item_catalog_group_id=p_ta_intf_tbl(i).item_catalog_group_id
AND icc_version_number=0;
p_ta_intf_tbl(i).transaction_type := G_UPDATE;
/* assigning defaults required for update*/
IF p_ta_intf_tbl(i).attr_display_name IS NOT NULL THEN
BEGIN
SELECT attr_name INTO p_ta_intf_tbl(i).attr_name
FROM ego_attrs_v EAV
WHERE EAV.attr_id= p_ta_intf_tbl(i).attr_id;
SELECT nvl(p_ta_intf_tbl(i).uom_class,b.uom_class),nvl(p_ta_intf_tbl(i).default_value,b.default_value),
nvl(p_ta_intf_tbl(i).rejected_value,b.rejected_value),nvl(p_ta_intf_tbl(i).required_flag,b.required_flag),
nvl(p_ta_intf_tbl(i).readonly_flag,b.readonly_flag),nvl(p_ta_intf_tbl(i).hidden_flag,b.hidden_flag),
nvl(p_ta_intf_tbl(i).searchable_flag,b.searchable_flag), nvl(p_ta_intf_tbl(i).check_eligibility,b.check_eligibility),
nvl(p_ta_intf_tbl(i).value_set_id,b.value_set_id), nvl(p_ta_intf_tbl(i).attr_display_name,b.attr_display_name),b.SEQUENCE
INTO
p_ta_intf_tbl(i).uom_class,p_ta_intf_tbl(i).default_value,p_ta_intf_tbl(i).rejected_value,
p_ta_intf_tbl(i).required_flag,p_ta_intf_tbl(i).readonly_flag,p_ta_intf_tbl(i).hidden_flag,
p_ta_intf_tbl(i).searchable_flag, p_ta_intf_tbl(i).check_eligibility,
p_ta_intf_tbl(i).value_set_id,p_ta_intf_tbl(i).attr_display_name,p_ta_intf_tbl(i).SEQUENCE
FROM EGO_TRANS_ATTR_VERS_B b
WHERE b.item_catalog_group_id=p_ta_intf_tbl(i).item_catalog_group_id
AND b.icc_version_number=0
AND b.attr_id= p_ta_intf_tbl(i).attr_id;
ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'ALL Attr_id, Attr_name and Attr_Display Name cannot be NULL for UPDATE,DEL and SYNC');
SELECT association_id INTO l_id_exists
FROM EGO_TRANS_ATTR_VERS_B ETAVB
WHERE ETAVB.association_id = p_ta_intf_tbl(i).association_id
AND item_catalog_group_id= p_ta_intf_tbl(i).item_catalog_group_id
AND icc_version_number=0
AND attr_id=p_ta_intf_tbl(i).attr_id;
ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Icc_Version_Number greater than zero not allowed while UPDATE and DELETE');
/* Both UPDATE and DELETE NOT SYNC */
IF p_ta_intf_tbl(i).transaction_type IN (G_UPDATE, G_DELETE) THEN
IF (p_ta_intf_tbl(i).icc_version_number>0) then /*Icc_Version_Number greater than zero not allowed while UPDATE and DELETE*/
ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Icc_Version_Number greater than zero not allowed while UPDATE and DELETE');
ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Association_id and attr_id should not be null for DELETE');
/* DELETE*/
IF p_ta_intf_tbl(i).transaction_type IN (G_DELETE) THEN
IF ((p_ta_intf_tbl(i).association_id IS NULL)
OR (p_ta_intf_tbl(i).attr_id IS NULL)) THEN /*Association_id and attr_id should not be null for DELETE */
ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'ERR : Association_id and attr_id should not be null for DELETE');
SELECT nvl(vers.start_active_date,SYSDATE)
FROM ego_mtl_catalog_grp_vers_b vers
WHERE vers.item_catalog_group_id = p_item_catalog_group_id
AND vers.version_seq_id = p_version_seq_id;
SELECT cat.parent_catalog_group_id
FROM mtl_item_catalog_groups_b cat
WHERE cat.item_catalog_group_id = p_item_catalog_group_id;
SELECT version_seq_id
FROM (SELECT version_seq_id
FROM ego_mtl_catalog_grp_vers_b cat_vers
WHERE cat_vers.item_catalog_group_id = p_item_catalog_group_id
AND cat_vers.start_active_date <= p_active_date
AND (cat_vers.end_active_date IS NULL
OR cat_vers.end_active_date >= p_active_date)
ORDER BY version_seq_id DESC)
WHERE rownum = 1;
SELECT heir.item_catalog_group_id,
heir.lev
FROM (SELECT item_catalog_group_id ,
level lev
FROM mtl_item_catalog_groups_b micg
START WITH item_catalog_group_id = p_item_catalog_group_id
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
) heir
WHERE heir.lev > 1 -- only parents
ORDER BY heir.lev; -- if same attribute exists in more than one parent, pick it from the least level
SELECT ta.attr_id,
ta.association_id
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.metadata_level = 'ICC'
AND ta.item_catalog_group_id = c_item_catalog_group_id
AND ta.icc_version_number = c_latest_ver
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'
AND flex_col.end_user_column_name = p_attr_name;
ELSIF p_ta_intf_rec.transaction_type=G_UPDATE THEN
ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entering UPDATE');
SELECT 1 INTO l_is_child_icc
FROM mtl_item_catalog_groups
WHERE ITEM_CATALOG_GROUP_ID=p_ta_intf_rec.item_catalog_group_id
AND PARENT_CATALOG_GROUP_ID IS NOT NULL;
SELECT 1 INTO l_is_ta_there
FROM EGO_TRANS_ATTR_VERS_B
WHERE item_catalog_group_id=p_ta_intf_rec.item_catalog_group_id
AND icc_version_number=p_ta_intf_rec.icc_version_number
AND attr_id= p_ta_intf_rec.attr_id;
IF l_is_ta_there=1 THEN /* if there then usual update */
ego_transaction_attrs_pvt.Update_Transaction_Attribute (
p_api_version => p_api_version,
p_tran_attrs_tbl => l_ego_ta_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
/* calling API to Update TA */
ego_transaction_attrs_pvt.Update_Transaction_Attribute (
p_api_version => p_api_version,
p_tran_attrs_tbl => l_ego_ta_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
ELSIF p_ta_intf_rec.transaction_type=G_DELETE THEN
ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entering DELETE');
/* calling API to Delete TA */
ego_transaction_attrs_pvt.Delete_Transaction_Attribute (
p_api_version => p_api_version,
p_association_id => l_ego_ta_tbl(1).associationid,
p_attr_id => l_ego_ta_tbl(1).attrid,
--p_tran_attrs_tbl => l_ego_ta_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
PROCEDURE Update_Intf_Trans_Attrs(
p_ta_intf_tbl IN OUT NOCOPY TA_Intf_Tbl,
x_return_status OUT NOCOPY VARCHAR2,
x_return_msg OUT NOCOPY VARCHAR2)
IS
l_proc_name VARCHAR2(200):='Update_Intf_Trans_Attrs';
ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entering Update_Intf_Trans_Attrs');
UPDATE EGO_TRANS_ATTRS_VERS_INTF
SET ROW= p_ta_intf_tbl(i) -- bug 9701271
WHERE
transaction_id = trans_id(i);
ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'End Update_Intf_Trans_Attrs');
ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Exception Update_Intf_Trans_Attrs');
END Update_Intf_Trans_Attrs;
PROCEDURE Update_Intf_Err_Trans_Attrs(
p_set_process_id IN NUMBER,
p_item_catalog_group_id IN NUMBER,
p_icc_version_number_intf IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_return_msg OUT NOCOPY VARCHAR2)
IS
l_proc_name VARCHAR2(200) :='Update_Intf_Err_Trans_Attrs';
ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entering Update_Intf_Err_Trans_Attrs');
UPDATE EGO_TRANS_ATTRS_VERS_INTF
SET process_status= G_ERROR_RECORD
WHERE item_catalog_group_id = p_item_catalog_group_id
AND icc_version_number = p_icc_version_number_intf
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'End Update_Intf_Err_Trans_Attrs');
ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Exception Update_Intf_Err_Trans_Attrs');
END Update_Intf_Err_Trans_Attrs;
while running cp, if user says delete all processed records then this will get called */
PROCEDURE Delete_Processed_Trans_Attrs(
p_set_process_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_return_msg OUT NOCOPY VARCHAR2
)
IS
l_proc_name varchar2(200):='Delete_Processed_Trans_Attrs';
ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Entering Delete_Processed_Trans_Attrs');
DELETE FROM EGO_TRANS_ATTRS_VERS_INTF
WHERE process_status = G_SUCCESS_RECORD
AND ((p_set_process_id IS NULL) OR (set_process_id=p_set_process_id));
ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'End Delete_Processed_Trans_Attrs');
ego_metadata_bulkload_pvt.Write_debug(G_PKG_NAME||'.'||l_proc_name||'->'||'Exception Delete_Processed_Trans_Attrs');
END Delete_Processed_Trans_Attrs;
SELECT item_catalog_group_id,
icc_version_NUMBER ,
SEQUENCE ,
attr_display_name ,
attr_name ,
attr_id ,
lev
FROM
(SELECT versions.item_catalog_group_id,
versions.icc_version_NUMBER ,
versions.SEQUENCE ,
attrs.attr_display_name ,
attrs.attr_name ,
attrs.attr_id ,
hier.lev
FROM ego_obj_AG_assocs_b assocs ,
ego_attrs_v attrs ,
ego_attr_groups_v ag ,
EGO_TRANS_ATTR_VERS_B versions ,
mtl_item_catalog_groups_kfv icv ,
(SELECT item_catalog_group_id ,
LEVEL lev
FROM mtl_item_catalog_groups_b START
WITH item_catalog_group_id = p_item_cat_group_id CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
) hier
WHERE ag.attr_group_type = 'EGO_ITEM_TRANS_ATTR_GROUP'
AND assocs.attr_group_id = ag.attr_group_id
AND assocs.classification_code = TO_CHAR(hier.item_catalog_group_id)
AND attrs.attr_group_name = ag.attr_group_name
AND TO_CHAR(icv.item_catalog_group_id) = assocs.classification_code
AND TO_CHAR(versions.association_id) = assocs.association_id
AND TO_CHAR(versions.item_catalog_group_id) = assocs.classification_code
AND attrs.attr_id = versions.attr_id
)
WHERE
(
(
LEV = 1
AND ICC_VERSION_NUMBER = p_icc_version_number
)
OR
(
LEV <> 1
AND
(
item_catalog_group_id, ICC_VERSION_NUMBER
)
IN
(SELECT item_catalog_group_id,
VERSION_SEQ_ID
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE start_active_date <=
(SELECT NVL(start_active_date,SYSDATE)
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
AND VERSION_SEQ_ID = p_icc_version_number
)
AND NVL(end_active_date, sysdate) >=
(SELECT NVL(start_active_date,SYSDATE)
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
AND VERSION_SEQ_ID = p_icc_version_number
)
AND version_seq_id > 0
)
)
); --end CURSOR cur_list
SELECT *
FROM
(SELECT *
FROM
(SELECT versions.item_catalog_group_id,
versions.ICC_VERSION_NUMBER ,
versions.ATTR_ID ,
versions.SEQUENCE ,
versions.attr_display_name ,
versions.metadata_level ,
attrs.attr_name ,
Hier.lev
FROM EGO_TRANS_ATTR_VERS_B VERSIONS,
EGO_ATTRS_V ATTRS ,
(SELECT ITEM_CATALOG_GROUP_ID ,
LEVEL LEV
FROM MTL_ITEM_CATALOG_GROUPS_B START
WITH ITEM_CATALOG_GROUP_ID = p_item_cat_group_id CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID =ITEM_CATALOG_GROUP_ID
) HIER
WHERE HIER.ITEM_CATALOG_GROUP_ID = versions.item_catalog_group_id
AND attrs.attr_id = versions.attr_id
AND attrs.attr_group_type ='EGO_ITEM_TRANS_ATTR_GROUP'
AND versions.metadata_level ='ICC'
)
WHERE
(
(
LEV = 1
AND ICC_VERSION_number = p_icc_version_number
)
OR
(
LEV <> 1
AND
(
item_catalog_group_id, ICC_VERSION_NUMBER
)
IN
(SELECT item_catalog_group_id,
VERSION_SEQ_ID
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE
(
item_catalog_group_id,start_active_date
)
IN
(SELECT item_catalog_group_id,
MAX(start_active_date) start_active_date
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE NVL(end_active_date, sysdate) >=
(SELECT NVL(start_active_date,SYSDATE)
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
AND VERSION_SEQ_ID = p_icc_version_number
)
AND version_seq_id > 0
AND start_active_date <=
(SELECT NVL(start_active_date,SYSDATE)
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
AND VERSION_SEQ_ID = p_icc_version_number
)
GROUP BY item_catalog_group_id
HAVING MAX(start_active_date)<=
(SELECT NVL(start_active_date,SYSDATE)
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
AND VERSION_SEQ_ID = p_icc_version_number
)
)
)
)
)
)
WHERE
(
lev,attr_id
)
IN
(SELECT MIN(lev),
attr_id
FROM
(SELECT versions.item_catalog_group_id,
versions.ICC_VERSION_NUMBER ,
versions.ATTR_ID ,
versions.SEQUENCE ,
versions.attr_display_name ,
versions.metadata_level ,
Hier.lev
FROM EGO_TRANS_ATTR_VERS_B VERSIONS,
(SELECT ITEM_CATALOG_GROUP_ID ,
LEVEL LEV
FROM MTL_ITEM_CATALOG_GROUPS_B
START WITH ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID =ITEM_CATALOG_GROUP_ID
) HIER
WHERE HIER.ITEM_CATALOG_GROUP_ID = versions.item_catalog_group_id
AND versions.metadata_level ='ICC'
AND versions.attr_display_name IS NOT NULL
)
WHERE
(
(
LEV =1
AND ICC_VERSION_number = p_icc_version_number
)
OR
(
LEV <> 1
AND
(
item_catalog_group_id, ICC_VERSION_NUMBER
)
IN
(SELECT item_catalog_group_id,
VERSION_SEQ_ID
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE
(
item_catalog_group_id,start_active_date
)
IN
(SELECT item_catalog_group_id,
MAX(start_active_date) start_active_date
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE NVL(end_active_date, sysdate) >=
(SELECT NVL(start_active_date,SYSDATE)
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
AND VERSION_SEQ_ID = p_icc_version_number
)
AND version_seq_id > 0
AND start_active_date <=
(SELECT NVL(start_active_date,SYSDATE)
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
AND VERSION_SEQ_ID = p_icc_version_number
)
GROUP BY item_catalog_group_id
HAVING MAX(start_active_date)<=
(SELECT NVL(start_active_date,SYSDATE)
FROM EGO_MTL_CATALOG_GRP_VERS_B
WHERE ITEM_CATALOG_GROUP_ID = p_item_cat_group_id
AND VERSION_SEQ_ID = p_icc_version_number
)
)
)
)
--AND metadata_level ='ICC'
)
GROUP BY attr_id
)
AND attr_id=l_attr_id
AND attr_id<>Nvl(p_attr_id,0000); --end cur_metadata