[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*)
INTO l_dummy_number
FROM ego_itm_usr_attr_intrfc
WHERE data_set_id = p_data_set_id
AND process_status = p_process_status
AND transaction_type = p_transaction_type
AND data_level_id = p_data_level_id;
FOR cr in (SELECT * FROM ego_itm_usr_attr_intrfc
WHERE data_set_id = p_data_set_id
AND process_status = p_process_status
AND transaction_type = p_transaction_type
AND data_level_id = p_data_level_id
ORDER BY inventory_item_id, organization_id, data_level_id,
revision_id, pk1_value, pk2_value, attr_group_id,
attr_int_name, row_identifier
) LOOP
code_debug(p_log_level => 0
,p_module => 'Copy_data_to_Intf'
,p_message => p_count||' - '||cr.inventory_item_id||', '||
cr.organization_id||', '||cr.data_level_id||', '||cr.revision_id||', '||
cr.pk1_value||', '||cr.pk2_value||', '||cr.attr_group_id||', '||
cr.attr_group_int_name||', '||cr.attr_int_name||', '||
cr.attr_value_str||', '||cr.attr_value_num||', '||cr.row_identifier);
SELECT count(*) abc
into l_dummy_number
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE data_set_id = p_data_set_id
-- AND process_status = 1
-- AND transaction_type = p_transaction_type
-- AND data_level_id = p_data_level_id
AND attr_int_name = 'QualityManual'
HAVING count(*) > 2;
SELECT *
FROM ego_itm_usr_attr_intrfc
WHERE data_set_id = p_data_set_id;
SELECT ITEM_CATALOG_GROUP_ID, PARENT_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_catalog_group_id;
SELECT CONCATENATED_SEGMENTS
INTO l_token_table(1).TOKEN_VALUE
FROM MTL_ITEM_CATALOG_GROUPS_KFV
WHERE ITEM_CATALOG_GROUP_ID = p_catalog_group_id;
(SELECT 1
FROM MTL_SYSTEM_ITEMS_INTERFACE msii_e
WHERE msii_e.TRANSACTION_TYPE = ''CREATE''
AND msii_e.PROCESS_FLAG = 1
AND msii_e.SET_PROCESS_ID = UAI2.DATA_SET_ID
AND msii_e.INVENTORY_ITEM_ID = UAI2.INVENTORY_ITEM_ID
AND msii_e.ORGANIZATION_ID = UAI2.ORGANIZATION_ID)';
SELECT DISTINCT
ORGANIZATION_ID
,ITEM_NUMBER
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE DATA_SET_ID = cp_data_set_id
AND PROCESS_STATUS = G_PS_IN_PROCESS
AND ITEM_NUMBER IS NOT NULL
AND INVENTORY_ITEM_ID IS NULL;
SELECT DISTINCT
PROCESS_STATUS
,ORGANIZATION_CODE
,ORGANIZATION_ID
,ITEM_NUMBER
,INVENTORY_ITEM_ID
,ATTR_GROUP_ID
,ATTR_GROUP_INT_NAME
,REVISION
,REVISION_ID
,ITEM_CATALOG_GROUP_ID
,TRANSACTION_ID
,ATTR_GROUP_TYPE
,PROG_INT_NUM1
,PROG_INT_NUM2
,PROG_INT_NUM3
,ATTR_VALUE_STR
,ATTR_VALUE_NUM
,ATTR_VALUE_DATE
,ATTR_DISP_VALUE
,PK1_VALUE
,PK2_VALUE
,DATA_LEVEL_NAME
,USER_DATA_LEVEL_NAME
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE DATA_SET_ID = cp_data_set_id
AND PROCESS_STATUS IN (G_PS_BAD_ORG_ID,
G_PS_BAD_ORG_CODE,
G_PS_BAD_ITEM_ID,
G_PS_BAD_ITEM_NUMBER,
G_PS_BAD_REVISION_ID,
G_PS_BAD_REVISION_CODE,
G_PS_BAD_CATALOG_GROUP_ID,
G_PS_BAD_ATTR_GROUP_ID,
G_PS_BAD_ATTR_GROUP_NAME,
G_PS_CHG_POLICY_NOT_ALLOWED,
G_PS_DATA_LEVEL_INCORRECT,
G_PS_BAD_DATA_LEVEL,
G_PS_BAD_SUPPLIER,
G_PS_BAD_SUPPLIER_SITE,
G_PS_BAD_SUPPLIER_SITE_ORG,
G_PS_BAD_STYLE_VAR_VALUE_SET,
G_PS_VAR_VSET_CHG_NOT_ALLOWED,
G_PS_SKU_VAR_VALUE_NOT_UPD,
G_PS_INH_ATTR_FOR_SKU_NOT_UPD
);
SELECT TRANSACTION_ID
,PROCESS_STATUS
,ORGANIZATION_CODE
,ITEM_NUMBER
,ATTR_GROUP_INT_NAME
,ROW_IDENTIFIER
,ATTR_INT_NAME
,ATTR_VALUE_STR
,ATTR_VALUE_NUM
,ATTR_VALUE_DATE
,ATTR_DISP_VALUE
,TRANSACTION_TYPE
,ORGANIZATION_ID
,INVENTORY_ITEM_ID
,ITEM_CATALOG_GROUP_ID
,REVISION_ID
,ATTR_GROUP_ID
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE DATA_SET_ID = cp_data_set_id
AND PROCESS_STATUS = G_PS_IN_PROCESS
ORDER BY ORGANIZATION_ID,
INVENTORY_ITEM_ID,
(DECODE (UPPER(TRANSACTION_TYPE),
'DELETE', 1,
'UPDATE', 2,
'SYNC', 3,
'CREATE', 4, 5)),
ROW_IDENTIFIER,
ATTR_GROUP_INT_NAME;
SELECT ITEM_CATALOG_GROUP_ID
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE DATA_SET_ID = cp_data_set_id
AND PROCESS_STATUS = G_PS_IN_PROCESS
GROUP BY ITEM_CATALOG_GROUP_ID;
SELECT DATA_LEVEL_ID, DATA_LEVEL_NAME
FROM EGO_DATA_LEVEL_B
WHERE ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
AND APPLICATION_ID = 431
AND DATA_LEVEL_NAME IN ('ITEM_SUP', 'ITEM_SUP_SITE', 'ITEM_SUP_SITE_ORG');
SELECT DATA_LEVEL_ID, DATA_LEVEL_NAME, ATTR_GROUP_TYPE
FROM EGO_DATA_LEVEL_B
WHERE APPLICATION_ID = 431
AND ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP';
SELECT
COUNT(DATA_SET_ID)
INTO l_rec_count
FROM EGO_ITM_USR_ATTR_INTRFC intf
WHERE DATA_SET_ID = p_data_set_id
AND (PROCESS_STATUS IS NULL OR PROCESS_STATUS IN (G_PS_TO_BE_PROCESSED, G_PS_IN_PROCESS, G_PS_STYLE_VARIANT_IN_PROCESS) );
SELECT 'HZ_PARTY:'||TO_CHAR(PERSON_ID)
INTO G_HZ_PARTY_ID
FROM EGO_PEOPLE_V
WHERE USER_NAME = G_USER_NAME;
' (SELECT 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 = UAI2.ITEM_CATALOG_GROUP_ID)' ||
' UNION ALL ' ||
' (SELECT UAI2.ITEM_CATALOG_GROUP_ID FROM DUAL)' ;
SELECT COUNT(data_set_id)
INTO l_rec_count
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE data_set_id = p_data_set_id;
SELECT OBJECT_ID
INTO G_ITEM_OBJECT_ID
FROM FND_OBJECTS
WHERE OBJ_NAME = G_ITEM_NAME;
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET PROCESS_STATUS = G_PS_IN_PROCESS
,ITEM_CATALOG_GROUP_ID = NULL
,PROG_INT_NUM1 = NULL
,PROG_INT_NUM2 = NULL
,PROG_INT_NUM3 = NULL
,PROG_INT_CHAR1 = 'N'
,PROG_INT_CHAR2 = 'N'
,REQUEST_ID = G_REQUEST_ID
,PROGRAM_APPLICATION_ID = G_PROGAM_APPLICATION_ID
,PROGRAM_ID = G_PROGAM_ID
,PROGRAM_UPDATE_DATE = SYSDATE
,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
,TRANSACTION_TYPE = UPPER(NVL(TRANSACTION_TYPE,EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE))
WHERE DATA_SET_ID = p_data_set_id
AND (PROCESS_STATUS IS NULL OR PROCESS_STATUS = G_PS_TO_BE_PROCESSED);
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET UAI.ORGANIZATION_ID =
(SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_CODE = UAI.ORGANIZATION_CODE)
WHERE UAI.DATA_SET_ID = p_data_set_id
AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI.ORGANIZATION_CODE IS NOT NULL
AND UAI.ORGANIZATION_ID IS NULL;
/* AND EXISTS (SELECT MP2.ORGANIZATION_ID
FROM MTL_PARAMETERS MP2
WHERE MP2.ORGANIZATION_CODE = UAI.ORGANIZATION_CODE
AND ((UAI.REVISION_ID IS NOT NULL
OR
UAI.REVISION IS NOT NULL)
OR
MP2.ORGANIZATION_ID = MP2.MASTER_ORGANIZATION_ID));*/
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET UAI.PROCESS_STATUS = G_PS_BAD_ORG_CODE
WHERE UAI.DATA_SET_ID = p_data_set_id
AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI.ROW_IDENTIFIER IN (SELECT DISTINCT
UAI2.ROW_IDENTIFIER
FROM EGO_ITM_USR_ATTR_INTRFC UAI2
WHERE UAI2.DATA_SET_ID = p_data_set_id
AND UAI2.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI2.ORGANIZATION_ID IS NULL);
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET UAI.PROCESS_STATUS = G_PS_BAD_ITEM_ID
WHERE UAI.DATA_SET_ID = p_data_set_id
AND UAI.PROCESS_STATUS IN (G_PS_IN_PROCESS, G_PS_STYLE_VARIANT_IN_PROCESS)
AND UAI.INVENTORY_ITEM_ID IS NOT NULL
AND NOT EXISTS (SELECT 'X'
FROM MTL_SYSTEM_ITEMS_B MSIB
WHERE MSIB.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID = UAI.ORGANIZATION_ID);
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET UAI.PROCESS_STATUS = G_PS_BAD_ITEM_ID
WHERE UAI.DATA_SET_ID = p_data_set_id
AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI.INVENTORY_ITEM_ID IS NOT NULL
AND NOT EXISTS (SELECT 'X'
FROM MTL_SYSTEM_ITEMS_B MSIB
WHERE MSIB.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID = UAI.ORGANIZATION_ID
UNION ALL
SELECT 'X'
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
AND MSII.ORGANIZATION_ID = UAI.ORGANIZATION_ID
AND MSII.SET_PROCESS_ID = UAI.DATA_SET_ID
AND MSII.PROCESS_FLAG = 1
AND MSII.TRANSACTION_TYPE = 'CREATE');
UPDATE EGO_ITM_USR_ATTR_INTRFC intrfc
SET INVENTORY_ITEM_ID =
(SELECT INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_B_KFV
WHERE CONCATENATED_SEGMENTS = intrfc.ITEM_NUMBER
AND ORGANIZATION_ID = intrfc.ORGANIZATION_ID)
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = G_PS_IN_PROCESS
AND ITEM_NUMBER IS NOT NULL
AND INVENTORY_ITEM_ID IS NULL;
UPDATE EGO_ITM_USR_ATTR_INTRFC intrfc
SET INVENTORY_ITEM_ID =
NVL( (SELECT INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_B_KFV
WHERE CONCATENATED_SEGMENTS = intrfc.ITEM_NUMBER
AND ORGANIZATION_ID = intrfc.ORGANIZATION_ID
),
(SELECT INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE msii.SET_PROCESS_ID = intrfc.DATA_SET_ID
AND msii.PROCESS_FLAG = 1
AND (msii.ITEM_NUMBER = intrfc.ITEM_NUMBER OR msii.SOURCE_SYSTEM_REFERENCE = intrfc.SOURCE_SYSTEM_REFERENCE)
AND msii.ORGANIZATION_ID = intrfc.ORGANIZATION_ID
AND ROWNUM = 1
)
)
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = G_PS_IN_PROCESS
AND (ITEM_NUMBER IS NOT NULL OR SOURCE_SYSTEM_REFERENCE IS NOT NULL)
AND INVENTORY_ITEM_ID IS NULL;
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET UAI.PROCESS_STATUS = G_PS_BAD_ITEM_NUMBER
WHERE UAI.DATA_SET_ID = p_data_set_id
AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI.ROW_IDENTIFIER IN (SELECT DISTINCT
UAI2.ROW_IDENTIFIER
FROM EGO_ITM_USR_ATTR_INTRFC UAI2
WHERE UAI2.DATA_SET_ID = p_data_set_id
AND UAI2.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI2.INVENTORY_ITEM_ID IS NULL);
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET UAI.PROCESS_STATUS = G_PS_BAD_REVISION_ID
WHERE UAI.DATA_SET_ID = p_data_set_id
AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI.REVISION_ID IS NOT NULL
AND NOT EXISTS (SELECT 'X'
FROM MTL_ITEM_REVISIONS MIR
WHERE MIR.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
AND MIR.ORGANIZATION_ID = UAI.ORGANIZATION_ID
AND MIR.REVISION_ID = UAI.REVISION_ID);
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET UAI.PROCESS_STATUS = G_PS_BAD_REVISION_ID
WHERE UAI.DATA_SET_ID = p_data_set_id
AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI.REVISION_ID IS NOT NULL
AND NOT EXISTS (SELECT 'X'
FROM MTL_ITEM_REVISIONS MIR
WHERE MIR.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
AND MIR.ORGANIZATION_ID = UAI.ORGANIZATION_ID
AND MIR.REVISION_ID = UAI.REVISION_ID
UNION ALL
SELECT 'X'
FROM MTL_ITEM_REVISIONS_INTERFACE miri
WHERE miri.SET_PROCESS_ID = UAI.DATA_SET_ID
AND miri.PROCESS_FLAG = 1
AND miri.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
AND miri.ORGANIZATION_ID = UAI.ORGANIZATION_ID
AND miri.REVISION_ID = UAI.REVISION_ID
);
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET UAI.REVISION_ID = (SELECT MIR.REVISION_ID
FROM MTL_ITEM_REVISIONS MIR
WHERE MIR.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
AND MIR.ORGANIZATION_ID = UAI.ORGANIZATION_ID
AND MIR.REVISION = UAI.REVISION)
WHERE UAI.DATA_SET_ID = p_data_set_id
AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI.REVISION IS NOT NULL
AND UAI.REVISION_ID IS NULL
AND EXISTS (SELECT MIR2.REVISION_ID
FROM MTL_ITEM_REVISIONS MIR2
WHERE MIR2.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
AND MIR2.ORGANIZATION_ID = UAI.ORGANIZATION_ID
AND MIR2.REVISION = UAI.REVISION);
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET UAI.REVISION_ID = NVL( (SELECT MIR.REVISION_ID
FROM MTL_ITEM_REVISIONS MIR
WHERE MIR.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
AND MIR.ORGANIZATION_ID = UAI.ORGANIZATION_ID
AND MIR.REVISION = UAI.REVISION
),
(SELECT miri.REVISION_ID
FROM MTL_ITEM_REVISIONS_INTERFACE miri
WHERE miri.SET_PROCESS_ID = UAI.DATA_SET_ID
AND miri.PROCESS_FLAG = 1
AND miri.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
AND miri.ORGANIZATION_ID = UAI.ORGANIZATION_ID
AND miri.REVISION = UAI.REVISION
AND ROWNUM = 1
)
)
WHERE UAI.DATA_SET_ID = p_data_set_id
AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI.REVISION IS NOT NULL
AND UAI.REVISION_ID IS NULL
AND EXISTS (SELECT 'X'
FROM MTL_ITEM_REVISIONS MIR2
WHERE MIR2.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
AND MIR2.ORGANIZATION_ID = UAI.ORGANIZATION_ID
AND MIR2.REVISION = UAI.REVISION
UNION ALL
SELECT 'X'
FROM MTL_ITEM_REVISIONS_INTERFACE miri
WHERE miri.SET_PROCESS_ID = UAI.DATA_SET_ID
AND miri.PROCESS_FLAG = 1
AND miri.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
AND miri.ORGANIZATION_ID = UAI.ORGANIZATION_ID
AND miri.REVISION = UAI.REVISION
);
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET UAI.PROCESS_STATUS = G_PS_BAD_REVISION_CODE
WHERE UAI.DATA_SET_ID = p_data_set_id
AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI.ROW_IDENTIFIER IN (SELECT DISTINCT
UAI2.ROW_IDENTIFIER
FROM EGO_ITM_USR_ATTR_INTRFC UAI2
WHERE UAI2.DATA_SET_ID = p_data_set_id
AND UAI2.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI2.REVISION IS NOT NULL
AND UAI2.REVISION_ID IS NULL);
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET (UAI.ITEM_CATALOG_GROUP_ID, UAI.PROG_INT_CHAR1,
UAI.PROG_INT_NUM2, UAI.PROG_INT_NUM3)
= (SELECT NVL(MSI.ITEM_CATALOG_GROUP_ID,-1), NVL(MSI.APPROVAL_STATUS,'A'),
MSI.LIFECYCLE_ID, MSI.CURRENT_PHASE_ID
FROM MTL_SYSTEM_ITEMS_B MSI
WHERE MSI.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = UAI.ORGANIZATION_ID
),
PROG_INT_CHAR2 =
NVL((SELECT 'Y'
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
AND MSII.ORGANIZATION_ID = UAI.ORGANIZATION_ID
AND MSII.REQUEST_ID = UAI.REQUEST_ID
AND MSII.TRANSACTION_TYPE = 'CREATE'
AND MSII.PROCESS_FLAG = 7
AND MSII.SET_PROCESS_ID = UAI.DATA_SET_ID
),PROG_INT_CHAR2)
WHERE UAI.DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = G_PS_IN_PROCESS
AND EXISTS (SELECT MSI.ITEM_CATALOG_GROUP_ID
FROM MTL_SYSTEM_ITEMS_B MSI
WHERE MSI.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = UAI.ORGANIZATION_ID);
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET (UAI.ITEM_CATALOG_GROUP_ID, UAI.PROG_INT_CHAR1,
UAI.PROG_INT_NUM2, UAI.PROG_INT_NUM3)
= (SELECT NVL(MSI.ITEM_CATALOG_GROUP_ID,-1), NVL(MSI.APPROVAL_STATUS,'A'),
MSI.LIFECYCLE_ID, MSI.CURRENT_PHASE_ID
FROM MTL_SYSTEM_ITEMS_B MSI
WHERE MSI.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = UAI.ORGANIZATION_ID
),
PROG_INT_CHAR2 =
NVL((SELECT 'Y'
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
AND MSII.ORGANIZATION_ID = UAI.ORGANIZATION_ID
AND MSII.TRANSACTION_TYPE = 'CREATE'
AND MSII.PROCESS_FLAG = 1
AND MSII.SET_PROCESS_ID = UAI.DATA_SET_ID
AND ROWNUM = 1
),PROG_INT_CHAR2)
WHERE UAI.DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = G_PS_IN_PROCESS
AND EXISTS (SELECT MSI.ITEM_CATALOG_GROUP_ID
FROM MTL_SYSTEM_ITEMS_B MSI
WHERE MSI.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = UAI.ORGANIZATION_ID);
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET (UAI.ITEM_CATALOG_GROUP_ID,
UAI.PROG_INT_NUM2, UAI.PROG_INT_NUM3)
= (SELECT
NVL( NVL(UAI.ITEM_CATALOG_GROUP_ID, MSII.ITEM_CATALOG_GROUP_ID), -1),
NVL( UAI.PROG_INT_NUM2, MSII.LIFECYCLE_ID),
NVL( UAI.PROG_INT_NUM3, MSII.CURRENT_PHASE_ID)
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
AND MSII.ORGANIZATION_ID = UAI.ORGANIZATION_ID
AND MSII.SET_PROCESS_ID = UAI.DATA_SET_ID
AND MSII.PROCESS_FLAG = 1
AND ROWNUM = 1
),
PROG_INT_CHAR2 =
NVL((SELECT 'Y'
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
AND MSII.ORGANIZATION_ID = UAI.ORGANIZATION_ID
AND MSII.TRANSACTION_TYPE = 'CREATE'
AND MSII.PROCESS_FLAG = 1
AND MSII.SET_PROCESS_ID = UAI.DATA_SET_ID
AND ROWNUM = 1
),PROG_INT_CHAR2)
WHERE UAI.DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = G_PS_IN_PROCESS
AND EXISTS (SELECT 'X'
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2
WHERE MSII2.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
AND MSII2.ORGANIZATION_ID = UAI.ORGANIZATION_ID
AND MSII2.SET_PROCESS_ID = UAI.DATA_SET_ID
AND MSII2.PROCESS_FLAG = 1
);
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET UAI.PROCESS_STATUS = G_PS_BAD_CATALOG_GROUP_ID
WHERE UAI.DATA_SET_ID = p_data_set_id
AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI.ROW_IDENTIFIER IN (SELECT DISTINCT
UAI2.ROW_IDENTIFIER
FROM EGO_ITM_USR_ATTR_INTRFC UAI2
WHERE UAI2.DATA_SET_ID = p_data_set_id
AND UAI2.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI2.ITEM_CATALOG_GROUP_ID IS NULL);
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET ATTR_GROUP_TYPE = NVL(ATTR_GROUP_TYPE,(SELECT DESCRIPTIVE_FLEXFIELD_NAME
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE APPLICATION_ID = 431
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI.ATTR_GROUP_INT_NAME
AND ROWNUM = 1))
,PROCESS_STATUS = DECODE((SELECT COUNT(DESCRIPTIVE_FLEXFIELD_NAME)
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE APPLICATION_ID = 431
AND (UAI.ATTR_GROUP_TYPE IS NULL OR UAI.ATTR_GROUP_TYPE=DESCRIPTIVE_FLEXFIELD_NAME)
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI.ATTR_GROUP_INT_NAME),
1,PROCESS_STATUS
,G_PS_BAD_ATTR_GROUP_NAME)
WHERE UAI.DATA_SET_ID = p_data_set_id
AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI.ATTR_GROUP_ID IS NULL;
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET UAI.PROCESS_STATUS = G_PS_BAD_CATALOG_GROUP_ID
WHERE UAI.DATA_SET_ID = p_data_set_id
AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI.ATTR_GROUP_ID IS NOT NULL
AND NOT EXISTS
( SELECT 'X'
FROM EGO_FND_DSC_FLX_CTX_EXT FLX_EXT
WHERE APPLICATION_ID = 431
AND DESCRIPTIVE_FLEXFIELD_NAME = UAI.ATTR_GROUP_TYPE --l_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI.ATTR_GROUP_INT_NAME
AND ATTR_GROUP_ID = UAI.ATTR_GROUP_ID);
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET ATTR_GROUP_ID = (SELECT ATTR_GROUP_ID
FROM EGO_FND_DSC_FLX_CTX_EXT FLX_EXT
WHERE APPLICATION_ID = 431
AND DESCRIPTIVE_FLEXFIELD_NAME = UAI.ATTR_GROUP_TYPE --l_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI.ATTR_GROUP_INT_NAME)
WHERE UAI.DATA_SET_ID = p_data_set_id
AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI.ATTR_GROUP_ID IS NULL;
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET UAI.PROCESS_STATUS = G_PS_BAD_ATTR_GROUP_NAME
WHERE UAI.DATA_SET_ID = p_data_set_id
AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI.ATTR_GROUP_ID IS NULL;
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET REVISION = NULL, REVISION_ID = NULL
WHERE ROWID IN (SELECT I.ROWID
FROM EGO_OBJ_AG_ASSOCS_B A,
EGO_ITM_USR_ATTR_INTRFC I
WHERE A.CLASSIFICATION_CODE = I.ITEM_CATALOG_GROUP_ID
AND A.OBJECT_ID = G_ITEM_OBJECT_ID
AND A.ATTR_GROUP_ID = I.ATTR_GROUP_ID
AND A.DATA_LEVEL = 'ITEM_LEVEL'
AND I.DATA_SET_ID = p_data_set_id
AND I.PROCESS_STATUS = G_PS_IN_PROCESS);
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET UAI.PROCESS_STATUS = G_PS_BAD_ORG_ID
WHERE UAI.DATA_SET_ID = p_data_set_id
AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI.ROW_IDENTIFIER IN
(SELECT DISTINCT UAI2.ROW_IDENTIFIER
FROM EGO_ITM_USR_ATTR_INTRFC UAI2
WHERE UAI2.DATA_SET_ID = p_data_set_id
AND UAI2.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI2.ORGANIZATION_ID IS NOT NULL
AND NOT EXISTS
(SELECT 'X'
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = UAI2.ORGANIZATION_ID
AND (UAI2.REVISION_ID IS NOT NULL
OR
UAI2.REVISION IS NOT NULL
OR
MP.MASTER_ORGANIZATION_ID = UAI2.ORGANIZATION_ID
)
)
);
UPDATE EGO_ITM_USR_ATTR_INTRFC uai
SET uai.DATA_LEVEL_ID = (SELECT edlb.DATA_LEVEL_ID
FROM EGO_DATA_LEVEL_B edlb
WHERE edlb.DATA_LEVEL_NAME = uai.DATA_LEVEL_NAME
AND edlb.APPLICATION_ID = 431
AND edlb.ATTR_GROUP_TYPE = NVL(uai.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
)
WHERE uai.DATA_SET_ID = p_data_set_id
AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
AND uai.DATA_LEVEL_NAME IS NOT NULL
AND uai.DATA_LEVEL_ID IS NULL;
UPDATE EGO_ITM_USR_ATTR_INTRFC uai
SET uai.DATA_LEVEL_ID = (SELECT edlv.DATA_LEVEL_ID
FROM EGO_DATA_LEVEL_VL edlv
WHERE edlv.USER_DATA_LEVEL_NAME = uai.USER_DATA_LEVEL_NAME
AND edlv.APPLICATION_ID = 431
AND edlv.ATTR_GROUP_TYPE = NVL(uai.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
)
WHERE uai.DATA_SET_ID = p_data_set_id
AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
AND uai.USER_DATA_LEVEL_NAME IS NOT NULL
AND uai.DATA_LEVEL_NAME IS NULL
AND uai.DATA_LEVEL_ID IS NULL;
UPDATE EGO_ITM_USR_ATTR_INTRFC uai
SET DATA_LEVEL_ID = (SELECT DATA_LEVEL_ID
FROM EGO_ATTR_GROUP_DL
WHERE ATTR_GROUP_ID = uai.ATTR_GROUP_ID
)
WHERE uai.DATA_SET_ID = p_data_set_id
AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
AND uai.DATA_LEVEL_ID IS NULL
AND uai.DATA_LEVEL_NAME IS NULL
AND uai.USER_DATA_LEVEL_NAME IS NULL
AND (SELECT COUNT(*)
FROM EGO_ATTR_GROUP_DL
WHERE ATTR_GROUP_ID = uai.ATTR_GROUP_ID) = 1;
UPDATE EGO_ITM_USR_ATTR_INTRFC uai
SET uai.PROCESS_STATUS = G_PS_BAD_DATA_LEVEL
WHERE uai.DATA_SET_ID = p_data_set_id
AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
uai2.ROW_IDENTIFIER
FROM EGO_ITM_USR_ATTR_INTRFC uai2
WHERE uai2.DATA_SET_ID = p_data_set_id
AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
AND NOT EXISTS (SELECT NULL
FROM EGO_DATA_LEVEL_B edlb
WHERE edlb.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
)
);
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET UAI.PROCESS_STATUS = G_PS_BAD_ORG_ID
WHERE UAI.DATA_SET_ID = p_data_set_id
AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI.DATA_LEVEL_ID IN (43101, 43103,43104,43107,43108)
AND EXISTS (SELECT MP2.ORGANIZATION_ID
FROM MTL_PARAMETERS MP2
WHERE MP2.ORGANIZATION_ID = UAI.ORGANIZATION_ID
AND MP2.ORGANIZATION_ID <> MP2.MASTER_ORGANIZATION_ID);
UPDATE EGO_ITM_USR_ATTR_INTRFC uai
SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER
WHERE uai.DATA_SET_ID = p_data_set_id
AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
UAI2.ROW_IDENTIFIER
FROM EGO_ITM_USR_ATTR_INTRFC uai2
WHERE uai2.DATA_SET_ID = p_data_set_id
AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
AND uai2.DATA_LEVEL_ID = l_item_sup_dl_id
AND ( uai2.PK1_VALUE IS NULL
OR
( uai2.PK1_VALUE IS NOT NULL
AND uai2.PK2_VALUE IS NOT NULL
)
OR
( uai2.PK1_VALUE IS NOT NULL
AND uai2.PK2_VALUE IS NULL
AND NOT EXISTS (SELECT NULL
FROM EGO_ITEM_ASSOCIATIONS eia
WHERE eia.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
AND eia.ORGANIZATION_ID = uai2.ORGANIZATION_ID
AND eia.PK1_VALUE = uai2.PK1_VALUE
AND eia.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
)
)
)
);
UPDATE EGO_ITM_USR_ATTR_INTRFC uai
SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER
WHERE uai.DATA_SET_ID = p_data_set_id
AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
UAI2.ROW_IDENTIFIER
FROM EGO_ITM_USR_ATTR_INTRFC uai2
WHERE uai2.DATA_SET_ID = p_data_set_id
AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
AND uai2.DATA_LEVEL_ID = l_item_sup_dl_id
AND ( uai2.PK1_VALUE IS NULL
OR
( uai2.PK1_VALUE IS NOT NULL
AND uai2.PK2_VALUE IS NOT NULL
)
OR
( uai2.PK1_VALUE IS NOT NULL
AND uai2.PK2_VALUE IS NULL
AND NOT EXISTS (SELECT NULL
FROM EGO_ITEM_ASSOCIATIONS eia
WHERE eia.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
AND eia.ORGANIZATION_ID = uai2.ORGANIZATION_ID
AND eia.PK1_VALUE = uai2.PK1_VALUE
AND eia.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
UNION ALL
SELECT NULL
FROM EGO_ITEM_ASSOCIATIONS_INTF eiai
WHERE eiai.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
AND eiai.ORGANIZATION_ID = uai2.ORGANIZATION_ID
AND eiai.PK1_VALUE = uai2.PK1_VALUE
AND eiai.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
AND eiai.BATCH_ID = uai2.DATA_SET_ID
AND eiai.PROCESS_FLAG = 1
)
)
)
);
UPDATE EGO_ITM_USR_ATTR_INTRFC uai
SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE
WHERE uai.DATA_SET_ID = p_data_set_id
AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
UAI2.ROW_IDENTIFIER
FROM EGO_ITM_USR_ATTR_INTRFC uai2
WHERE uai2.DATA_SET_ID = p_data_set_id
AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
AND uai2.DATA_LEVEL_ID = l_item_sup_site_dl_id
AND ( uai2.PK1_VALUE IS NULL
OR
uai2.PK2_VALUE IS NULL
OR
( uai2.PK1_VALUE IS NOT NULL
AND uai2.PK2_VALUE IS NOT NULL
AND NOT EXISTS (SELECT NULL
FROM EGO_ITEM_ASSOCIATIONS eia
WHERE eia.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
AND eia.ORGANIZATION_ID = uai2.ORGANIZATION_ID
AND eia.PK1_VALUE = uai2.PK1_VALUE
AND eia.PK2_VALUE = uai2.PK2_VALUE
AND eia.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
)
)
)
);
UPDATE EGO_ITM_USR_ATTR_INTRFC uai
SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE
WHERE uai.DATA_SET_ID = p_data_set_id
AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
UAI2.ROW_IDENTIFIER
FROM EGO_ITM_USR_ATTR_INTRFC uai2
WHERE uai2.DATA_SET_ID = p_data_set_id
AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
AND uai2.DATA_LEVEL_ID = l_item_sup_site_dl_id
AND ( uai2.PK1_VALUE IS NULL
OR
uai2.PK2_VALUE IS NULL
OR
( uai2.PK1_VALUE IS NOT NULL
AND uai2.PK2_VALUE IS NOT NULL
AND NOT EXISTS (SELECT NULL
FROM EGO_ITEM_ASSOCIATIONS eia
WHERE eia.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
AND eia.ORGANIZATION_ID = uai2.ORGANIZATION_ID
AND eia.PK1_VALUE = uai2.PK1_VALUE
AND eia.PK2_VALUE = uai2.PK2_VALUE
AND eia.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
UNION ALL
SELECT NULL
FROM EGO_ITEM_ASSOCIATIONS_INTF eiai
WHERE eiai.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
AND eiai.ORGANIZATION_ID = uai2.ORGANIZATION_ID
AND eiai.PK1_VALUE = uai2.PK1_VALUE
AND eiai.PK2_VALUE = uai2.PK2_VALUE
AND eiai.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
AND eiai.BATCH_ID = uai2.DATA_SET_ID
AND eiai.PROCESS_FLAG = 1
)
)
)
);
UPDATE EGO_ITM_USR_ATTR_INTRFC uai
SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE_ORG
WHERE uai.DATA_SET_ID = p_data_set_id
AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
UAI2.ROW_IDENTIFIER
FROM EGO_ITM_USR_ATTR_INTRFC uai2
WHERE uai2.DATA_SET_ID = p_data_set_id
AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
AND uai2.DATA_LEVEL_ID = l_item_sup_site_org_dl_id
AND ( uai2.PK1_VALUE IS NULL
OR
uai2.PK2_VALUE IS NULL
OR
( uai2.PK1_VALUE IS NOT NULL
AND uai2.PK2_VALUE IS NOT NULL
AND NOT EXISTS (SELECT NULL
FROM EGO_ITEM_ASSOCIATIONS eia
WHERE eia.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
AND eia.ORGANIZATION_ID = uai2.ORGANIZATION_ID
AND eia.PK1_VALUE = uai2.PK1_VALUE
AND eia.PK2_VALUE = uai2.PK2_VALUE
AND eia.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
)
)
)
);
UPDATE EGO_ITM_USR_ATTR_INTRFC uai
SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE_ORG
WHERE uai.DATA_SET_ID = p_data_set_id
AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
UAI2.ROW_IDENTIFIER
FROM EGO_ITM_USR_ATTR_INTRFC uai2
WHERE uai2.DATA_SET_ID = p_data_set_id
AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
AND uai2.DATA_LEVEL_ID = l_item_sup_site_org_dl_id
AND ( uai2.PK1_VALUE IS NULL
OR
uai2.PK2_VALUE IS NULL
OR
( uai2.PK1_VALUE IS NOT NULL
AND uai2.PK2_VALUE IS NOT NULL
AND NOT EXISTS (SELECT NULL
FROM EGO_ITEM_ASSOCIATIONS eia
WHERE eia.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
AND eia.ORGANIZATION_ID = uai2.ORGANIZATION_ID
AND eia.PK1_VALUE = uai2.PK1_VALUE
AND eia.PK2_VALUE = uai2.PK2_VALUE
AND eia.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
UNION ALL
SELECT NULL
FROM EGO_ITEM_ASSOCIATIONS_INTF eiai
WHERE eiai.INVENTORY_ITEM_ID = uai2.INVENTORY_ITEM_ID
AND eiai.ORGANIZATION_ID = uai2.ORGANIZATION_ID
AND eiai.PK1_VALUE = uai2.PK1_VALUE
AND eiai.PK2_VALUE = uai2.PK2_VALUE
AND eiai.DATA_LEVEL_ID = uai2.DATA_LEVEL_ID
AND eiai.BATCH_ID = uai2.DATA_SET_ID
AND eiai.PROCESS_FLAG = 1
)
)
)
);
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET PROCESS_STATUS = G_PS_SKU_VAR_VALUE_NOT_UPD
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = G_PS_IN_PROCESS
AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
FROM
EGO_ITM_USR_ATTR_INTRFC intf,
EGO_FND_DSC_FLX_CTX_EXT ag_ext,
MTL_SYSTEM_ITEMS_B msib
WHERE intf.DATA_SET_ID = p_data_set_id
AND intf.PROCESS_STATUS = G_PS_IN_PROCESS
AND intf.ATTR_GROUP_ID = ag_ext.ATTR_GROUP_ID
AND ag_ext.VARIANT = 'Y'
AND intf.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID
AND intf.ORGANIZATION_ID = msib.ORGANIZATION_ID
AND msib.STYLE_ITEM_FLAG = 'N'
AND EXISTS (SELECT NULL
FROM EGO_MTL_SY_ITEMS_EXT_B ext_prod
WHERE ext_prod.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID
AND ext_prod.ORGANIZATION_ID = msib.ORGANIZATION_ID
AND ext_prod.ATTR_GROUP_ID = ag_ext.ATTR_GROUP_ID
)
);
EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Marked all SKU records to error, if trying to update variant value, count='||SQL%ROWCOUNT, 1);
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET PROCESS_STATUS = G_PS_INH_ATTR_FOR_SKU_NOT_UPD
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = G_PS_IN_PROCESS
AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
FROM
EGO_ITM_USR_ATTR_INTRFC intf,
EGO_ATTR_GROUP_DL eagd
WHERE intf.DATA_SET_ID = p_data_set_id
AND intf.PROCESS_STATUS = G_PS_IN_PROCESS
AND intf.ATTR_GROUP_ID = eagd.ATTR_GROUP_ID
AND intf.DATA_LEVEL_ID = eagd.DATA_LEVEL_ID
AND NVL(eagd.DEFAULTING, 'D') = 'I'
AND EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE msii.SET_PROCESS_ID = intf.DATA_SET_ID
AND msii.PROCESS_FLAG = 1
AND msii.INVENTORY_ITEM_ID = intf.INVENTORY_ITEM_ID
AND msii.ORGANIZATION_ID = intf.ORGANIZATION_ID
AND msii.STYLE_ITEM_FLAG = 'N'
UNION ALL
SELECT NULL FROM MTL_SYSTEM_ITEMS_B msib
WHERE intf.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID
AND intf.ORGANIZATION_ID = msib.ORGANIZATION_ID
AND msib.STYLE_ITEM_FLAG = 'N'
)
);
EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Marked all SKU records to error, if trying to update inherited attribute value, count='||SQL%ROWCOUNT, 1);
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET (UAI.PROG_INT_NUM3)
= NVL((SELECT MIR.CURRENT_PHASE_ID
FROM MTL_ITEM_REVISIONS MIR
WHERE MIR.REVISION_ID = UAI.REVISION_ID
), UAI.PROG_INT_NUM3),
PROG_INT_CHAR2 =
NVL((SELECT 'Y'
FROM MTL_ITEM_REVISIONS_INTERFACE MIRI
WHERE MIRI.REVISION_ID = UAI.REVISION_ID
AND MIRI.request_id = UAI.REQUEST_ID
AND MIRI.TRANSACTION_TYPE = 'CREATE'
AND MIRI.PROCESS_FLAG = 7
AND ROWNUM = 1
),PROG_INT_CHAR2)
WHERE UAI.DATA_SET_ID = p_data_set_id
AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI.REVISION_ID IS NOT NULL
AND UAI.ITEM_CATALOG_GROUP_ID IS NOT NULL;
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET UAI.PROG_INT_NUM3
= NVL((CASE WHEN EXISTS (SELECT 1
FROM MTL_ITEM_REVISIONS mir
WHERE mir.REVISION_ID = UAI.REVISION_ID
)
THEN (SELECT mir1.CURRENT_PHASE_ID
FROM MTL_ITEM_REVISIONS mir1
WHERE mir1.REVISION_ID = UAI.REVISION_ID
)
ELSE (SELECT miri.CURRENT_PHASE_ID
FROM MTL_ITEM_REVISIONS_INTERFACE miri
WHERE miri.REVISION_ID = UAI.REVISION_ID
AND miri.SET_PROCESS_ID = UAI.DATA_SET_ID
AND miri.PROCESS_FLAG = 1
AND ROWNUM = 1
)
END
), UAI.PROG_INT_NUM3),
PROG_INT_CHAR2 =
NVL((SELECT 'Y'
FROM MTL_ITEM_REVISIONS_INTERFACE MIRI1
WHERE MIRI1.REVISION_ID = UAI.REVISION_ID
AND MIRI1.SET_PROCESS_ID = UAI.DATA_SET_ID
AND MIRI1.TRANSACTION_TYPE = 'CREATE'
AND MIRI1.PROCESS_FLAG = 1
AND ROWNUM = 1
),PROG_INT_CHAR2)
WHERE UAI.DATA_SET_ID = p_data_set_id
AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI.REVISION_ID IS NOT NULL
AND UAI.ITEM_CATALOG_GROUP_ID IS NOT NULL;
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET UAI.PROG_INT_NUM1 =
(SELECT ic.item_catalog_group_id
FROM mtl_item_catalog_groups_b ic
WHERE EXISTS
( SELECT olc.object_classification_code CatalogId
FROM ego_obj_type_lifecycles olc
WHERE olc.object_id = G_ITEM_OBJECT_ID
AND olc.lifecycle_id = UAI.PROG_INT_NUM2
AND olc.object_classification_code = ic.item_catalog_group_id
)
AND ROWNUM = 1
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
START WITH item_catalog_group_id = UAI.item_catalog_group_id
)
WHERE UAI.DATA_SET_ID = p_data_set_id
AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI.ITEM_CATALOG_GROUP_ID IS NOT NULL
AND UAI.PROG_INT_NUM2 IS NOT NULL
AND UAI.PROG_INT_CHAR1 = 'A'
AND UAI.PROG_INT_CHAR2 = 'N';
' UPDATE EGO_ITM_USR_ATTR_INTRFC UAI '||
' SET UAI.PROCESS_STATUS = :1 '||
' WHERE UAI.DATA_SET_ID = :2 '||
' AND UAI.PROCESS_STATUS = :3 '||
' AND UAI.ROW_IDENTIFIER IN '||
' (SELECT DISTINCT UAI2.ROW_IDENTIFIER '||
' FROM EGO_ITM_USR_ATTR_INTRFC UAI2, ENG_CHANGE_POLICIES_V ECP '||
' WHERE UAI2.DATA_SET_ID = :4 '||
' AND UAI2.PROCESS_STATUS = :5 '||
' AND UAI2.ITEM_CATALOG_GROUP_ID IS NOT NULL '||
' AND UAI2.PROG_INT_NUM2 IS NOT NULL '||
' AND UAI2.PROG_INT_CHAR1 = ''A''' ||
' AND UAI2.PROG_INT_CHAR2 = ''N''' ||
' AND ECP.ATTRIBUTE_OBJECT_NAME = ''EGO_CATALOG_GROUP'' '||
' AND ECP.ATTRIBUTE_CODE = ''ATTRIBUTE_GROUP'' '||
' AND ECP.POLICY_OBJECT_NAME = ''CATALOG_LIFECYCLE_PHASE'' '||
' AND ECP.POLICY_CHAR_VALUE IS NOT NULL '||
' AND ECP.POLICY_CHAR_VALUE = :6 '||
' AND ECP.ATTRIBUTE_NUMBER_VALUE = UAI2.ATTR_GROUP_ID '||
' AND ECP.POLICY_OBJECT_PK1_VALUE = TO_CHAR(UAI2.PROG_INT_NUM1) '||
' AND ECP.POLICY_OBJECT_PK2_VALUE = TO_CHAR(UAI2.PROG_INT_NUM2) '||
' AND ECP.POLICY_OBJECT_PK3_VALUE = TO_CHAR(UAI2.PROG_INT_NUM3) '||
' AND DATA_LEVEL_ID IN ( SELECT DATA_LEVEL_ID FROM EGO_DATA_LEVEL_B '||
' WHERE APPLICATION_ID = ''431'' '||
' AND DATA_LEVEL_NAME IN ( ''ITEM_LEVEL'', ''ITEM_REVISION_LEVEL'', ''ITEM_ORG'')) '||
' )';
UPDATE EGO_ITM_USR_ATTR_INTRFC INTF
SET PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
WHERE INTF.DATA_SET_ID = p_data_set_id
AND INTF.PROCESS_STATUS = G_PS_IN_PROCESS
AND EXISTS (SELECT NULL
FROM EGO_FND_DSC_FLX_CTX_EXT AG_EXT, MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE AG_EXT.VARIANT = 'Y'
AND AG_EXT.DESCRIPTIVE_FLEXFIELD_NAME = NVL(INTF.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
AND AG_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = INTF.ATTR_GROUP_INT_NAME
AND INTF.ORGANIZATION_ID = MSII.ORGANIZATION_ID
AND MSII.STYLE_ITEM_FLAG = 'Y'
AND INTF.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
UNION ALL
SELECT NULL
FROM EGO_FND_DSC_FLX_CTX_EXT AG_EXT1, MTL_SYSTEM_ITEMS_B MSIB
WHERE AG_EXT1.VARIANT = 'Y'
AND AG_EXT1.DESCRIPTIVE_FLEXFIELD_NAME = NVL(INTF.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
AND AG_EXT1.DESCRIPTIVE_FLEX_CONTEXT_CODE = INTF.ATTR_GROUP_INT_NAME
AND INTF.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MSIB.STYLE_ITEM_FLAG = 'Y'
AND INTF.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
);
EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Updated Style records count='||SQL%ROWCOUNT, 1);
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET PROCESS_STATUS = G_PS_VAR_VSET_CHG_NOT_ALLOWED
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
FROM EGO_ITM_USR_ATTR_INTRFC INTF, MTL_SYSTEM_ITEMS_KFV MSIK
WHERE INTF.DATA_SET_ID = p_data_set_id
AND INTF.PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
AND MSIK.STYLE_ITEM_ID = INTF.INVENTORY_ITEM_ID
AND MSIK.ORGANIZATION_ID = INTF.ORGANIZATION_ID
);
EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Updated records to error where SKU exists for style, count='||SQL%ROWCOUNT, 1);
UPDATE EGO_ITM_USR_ATTR_INTRFC INTF
SET ATTR_VALUE_NUM = (SELECT VALUE_SET_ID FROM EGO_VALUE_SETS_V VS
WHERE VALUE_SET_NAME = INTF.ATTR_DISP_VALUE)
WHERE INTF.DATA_SET_ID = p_data_set_id
AND INTF.PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
AND INTF.ATTR_DISP_VALUE IS NOT NULL;
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET PROCESS_STATUS = G_PS_BAD_STYLE_VAR_VALUE_SET
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
FROM EGO_ITM_USR_ATTR_INTRFC INTF
WHERE INTF.DATA_SET_ID = p_data_set_id
AND INTF.PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
AND NOT EXISTS (SELECT NULL
FROM FND_DESCR_FLEX_COLUMN_USAGES FL_COL
WHERE FL_COL.DESCRIPTIVE_FLEXFIELD_NAME = INTF.ATTR_GROUP_TYPE
AND FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE = INTF.ATTR_GROUP_INT_NAME
AND FL_COL.END_USER_COLUMN_NAME = INTF.ATTR_INT_NAME
AND FL_COL.APPLICATION_ID = 431
AND (INTF.ATTR_VALUE_NUM = FL_COL.FLEX_VALUE_SET_ID
OR INTF.ATTR_VALUE_NUM IN (SELECT VS.VALUE_SET_ID
FROM EGO_VALUE_SET_EXT VS
WHERE VS.PARENT_VALUE_SET_ID = FL_COL.FLEX_VALUE_SET_ID)
)
)
);
USING (SELECT
INTF.INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID,
INTF.ATTR_VALUE_NUM AS VALUE_SET_ID,
ATTR.ATTR_ID AS ATTRIBUTE_ID
FROM
EGO_ITM_USR_ATTR_INTRFC INTF,
EGO_FND_DF_COL_USGS_EXT ATTR,
FND_DESCR_FLEX_COLUMN_USAGES FL_COL,
MTL_SYSTEM_ITEMS_B MSIB
WHERE INTF.ATTR_GROUP_TYPE = ATTR.DESCRIPTIVE_FLEXFIELD_NAME
AND INTF.ATTR_GROUP_INT_NAME = ATTR.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND ATTR.APPLICATION_ID = 431
AND ATTR.APPLICATION_ID = FL_COL.APPLICATION_ID
AND ATTR.DESCRIPTIVE_FLEXFIELD_NAME = FL_COL.DESCRIPTIVE_FLEXFIELD_NAME
AND ATTR.DESCRIPTIVE_FLEX_CONTEXT_CODE = FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND ATTR.APPLICATION_COLUMN_NAME = FL_COL.APPLICATION_COLUMN_NAME
AND INTF.ATTR_INT_NAME = FL_COL.END_USER_COLUMN_NAME
AND INTF.DATA_SET_ID = p_data_set_id
AND INTF.PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
AND MSIB.INVENTORY_ITEM_ID = INTF.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID = INTF.ORGANIZATION_ID) INTRFC
ON (ESVAV.INVENTORY_ITEM_ID = INTRFC.INVENTORY_ITEM_ID
AND ESVAV.ATTRIBUTE_ID = INTRFC.ATTRIBUTE_ID)
WHEN MATCHED THEN
UPDATE SET ESVAV.VALUE_SET_ID = INTRFC.VALUE_SET_ID
WHEN NOT MATCHED THEN
INSERT
(
INVENTORY_ITEM_ID,
VALUE_SET_ID,
ATTRIBUTE_ID,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
VALUES
(
INTRFC.INVENTORY_ITEM_ID,
INTRFC.VALUE_SET_ID,
INTRFC.ATTRIBUTE_ID,
l_login_id,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id
);
EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Inserted value sets to EGO_STYLE_VARIANT_ATTR_VS, for styles, count='||SQL%ROWCOUNT, 1);
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET PROCESS_STATUS = G_PS_SUCCESS
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS;
SELECT concatenated_segments
INTO l_current_category_name
FROM MTL_ITEM_CATALOG_GROUPS_KFV
WHERE ITEM_CATALOG_GROUP_ID = error_rec.prog_int_num1;
SELECT PEP.NAME
INTO l_current_life_cycle
FROM PA_EGO_LIFECYCLES_V PEP
WHERE PEP.PROJ_ELEMENT_ID = error_rec.prog_int_num2;
SELECT PEP.NAME
INTO l_current_phase_name
FROM PA_EGO_PHASES_V PEP
WHERE PEP.PROJ_ELEMENT_ID = error_rec.prog_int_num3;
SELECT PEP.NAME
INTO l_current_phase_name
FROM PA_EGO_PHASES_V PEP
WHERE PEP.PROJ_ELEMENT_ID = error_rec.prog_int_num3;
l_token_table.DELETE();
l_dynamic_sql := ' UPDATE EGO_ITM_USR_ATTR_INTRFC'||
' SET PROCESS_STATUS = '||G_PS_GENERIC_ERROR||
' WHERE DATA_SET_ID = :1'||
' AND (PROCESS_STATUS IN ('||G_PS_BAD_ORG_ID||', '||
G_PS_BAD_ORG_CODE||', '||
G_PS_BAD_ITEM_ID||', '||
G_PS_BAD_ITEM_NUMBER||', '||
G_PS_BAD_REVISION_ID||', '||
G_PS_BAD_REVISION_CODE||', '||
G_PS_BAD_CATALOG_GROUP_ID||', '||
G_PS_BAD_ATTR_GROUP_ID||', '||
G_PS_BAD_ATTR_GROUP_NAME||', '||
G_PS_CHG_POLICY_NOT_ALLOWED||', '||
G_PS_BAD_DATA_LEVEL||', '||
G_PS_BAD_SUPPLIER||', '||
G_PS_BAD_SUPPLIER_SITE||', '||
G_PS_BAD_SUPPLIER_SITE_ORG||', '||
G_PS_BAD_STYLE_VAR_VALUE_SET||', '||
G_PS_VAR_VSET_CHG_NOT_ALLOWED||', '||
G_PS_SKU_VAR_VALUE_NOT_UPD||', '||
G_PS_INH_ATTR_FOR_SKU_NOT_UPD||', '||
G_PS_DATA_LEVEL_INCORRECT||' ) )';
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET PROCESS_STATUS = G_PS_BAD_SKU_VAR_VALUE
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = G_PS_IN_PROCESS
AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
FROM
EGO_FND_DSC_FLX_CTX_EXT AG_EXT,
EGO_ITM_USR_ATTR_INTRFC INTF,
EGO_STYLE_VARIANT_ATTR_VS SVA,
EGO_FND_DF_COL_USGS_EXT ATTR,
FND_DESCR_FLEX_COLUMN_USAGES FL_COL,
MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE INTF.DATA_SET_ID = p_data_set_id
AND INTF.PROCESS_STATUS = G_PS_IN_PROCESS
AND INTF.ATTR_GROUP_TYPE = ATTR.DESCRIPTIVE_FLEXFIELD_NAME
AND INTF.ATTR_GROUP_INT_NAME = ATTR.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND ATTR.APPLICATION_ID = 431
AND ATTR.APPLICATION_ID = FL_COL.APPLICATION_ID
AND ATTR.DESCRIPTIVE_FLEXFIELD_NAME = FL_COL.DESCRIPTIVE_FLEXFIELD_NAME
AND ATTR.DESCRIPTIVE_FLEX_CONTEXT_CODE = FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND ATTR.APPLICATION_COLUMN_NAME = FL_COL.APPLICATION_COLUMN_NAME
AND AG_EXT.APPLICATION_ID = ATTR.APPLICATION_ID
AND AG_EXT.DESCRIPTIVE_FLEXFIELD_NAME = ATTR.DESCRIPTIVE_FLEXFIELD_NAME
AND AG_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = ATTR.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND Nvl(AG_EXT.VARIANT, 'N') = 'Y'
AND INTF.ATTR_INT_NAME = FL_COL.END_USER_COLUMN_NAME
AND SVA.ATTRIBUTE_ID = ATTR.ATTR_ID
AND SVA.INVENTORY_ITEM_ID = MSII.STYLE_ITEM_ID
AND SVA.VALUE_SET_ID <> FL_COL.FLEX_VALUE_SET_ID
AND INTF.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
AND INTF.ORGANIZATION_ID = MSII.ORGANIZATION_ID
AND (CASE WHEN ATTR.DATA_TYPE IN ('A', 'C') THEN INTF.ATTR_VALUE_STR
WHEN ATTR.DATA_TYPE = 'N' THEN TO_CHAR(INTF.ATTR_VALUE_NUM)
WHEN ATTR.DATA_TYPE IN ('X', 'Y') THEN TO_CHAR(INTF.ATTR_VALUE_DATE)
ELSE NULL
END) NOT IN ( SELECT val.FLEX_VALUE
FROM
FND_FLEX_VALUES_VL val,
EGO_VS_VALUES_DISP_ORDER disp_order,
EGO_VALUE_SET_EXT ext
WHERE val.FLEX_VALUE_ID = disp_order.VALUE_SET_VALUE_ID
AND disp_order.VALUE_SET_ID = SVA.VALUE_SET_ID
AND val.FLEX_VALUE_SET_ID = ext.PARENT_VALUE_SET_ID
AND ext.VALUE_SET_ID = disp_order.VALUE_SET_ID
AND Nvl(val.ENABLED_FLAG,'Y') = 'Y'
AND Nvl(val.START_DATE_ACTIVE, SYSDATE) <= SYSDATE
AND Nvl(val.END_DATE_ACTIVE, SYSDATE) >= SYSDATE
)
);
FOR i IN (SELECT PROCESS_STATUS, TRANSACTION_ID, ATTR_VALUE_STR, ATTR_VALUE_NUM, ATTR_VALUE_DATE, ATTR_DISP_VALUE, ITEM_NUMBER
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = G_PS_BAD_SKU_VAR_VALUE
)
LOOP
RETCODE := L_CONC_RET_STS_WARNING;
l_token_table.DELETE();
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET PROCESS_STATUS = G_PS_GENERIC_ERROR
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = G_PS_BAD_SKU_VAR_VALUE;
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET UAI.PROCESS_STATUS = G_PS_CHG_POLICY_CO_REQUIRED
WHERE UAI.DATA_SET_ID = p_data_set_id
AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI.PROG_INT_CHAR2 = 'N' OR UAI.DATA_LEVEL_ID = 43106;--BugFix:6315828(for revisions even in case revision is created in the batch
UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
SET UAI.PROCESS_STATUS = G_PS_CHG_POLICY_CO_REQUIRED
WHERE UAI.DATA_SET_ID = p_data_set_id
AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
AND UAI.ITEM_CATALOG_GROUP_ID = catalog_groups.ITEM_CATALOG_GROUP_ID;
DELETE FROM EGO_ITM_USR_ATTR_INTRFC
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = G_PS_IN_PROCESS;
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET PROCESS_STATUS = G_PS_SUCCESS
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = G_PS_IN_PROCESS;
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET PROCESS_STATUS = G_PS_GENERIC_ERROR
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = G_PS_IN_PROCESS;
SELECT TRANSACTION_ID
INTO l_err_reporting_transaction_id
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE DATA_SET_ID = p_data_set_id
AND ROWNUM = 1;
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET PROCESS_STATUS = G_PS_GENERIC_ERROR
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = G_PS_IN_PROCESS;
SELECT TRANSACTION_ID
INTO l_err_reporting_transaction_id
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE DATA_SET_ID = p_data_set_id
AND ROWNUM = 1;
l_insert_cols VARCHAR2(32767);
l_select_sql VARCHAR2(32767);
SELECT
attr_ext.ATTR_ID,
attr_col.END_USER_COLUMN_NAME AS ATTR_NAME,
attr_ext.DATA_TYPE AS DATA_TYPE_CODE,
attr_ext.APPLICATION_COLUMN_NAME AS DATABASE_COLUMN,
attr_ext.UOM_CLASS AS UOM_CLASS,
ag_ext.MULTI_ROW
FROM
FND_DESCR_FLEX_COLUMN_USAGES attr_col,
EGO_FND_DF_COL_USGS_EXT attr_ext,
EGO_FND_DSC_FLX_CTX_EXT ag_ext
WHERE ag_ext.ATTR_GROUP_ID = c_attr_group_id
AND ag_ext.DESCRIPTIVE_FLEXFIELD_NAME = attr_ext.DESCRIPTIVE_FLEXFIELD_NAME
AND ag_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE = attr_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND ag_ext.APPLICATION_ID = attr_ext.APPLICATION_ID
AND attr_ext.APPLICATION_ID = attr_col.APPLICATION_ID
AND attr_ext.DESCRIPTIVE_FLEXFIELD_NAME = attr_col.DESCRIPTIVE_FLEXFIELD_NAME
AND attr_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE = attr_col.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND attr_ext.APPLICATION_COLUMN_NAME = attr_col.APPLICATION_COLUMN_NAME
AND attr_col.ENABLED_FLAG = 'Y';
SELECT NVL(MAX(row_identifier),0)
INTO l_max_row_identifier
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE DATA_SET_ID = p_dest_data_set_id;
l_insert_cols := q'#
( TRANSACTION_ID,
PROCESS_STATUS,
DATA_SET_ID,
TRANSACTION_TYPE,
ORGANIZATION_ID,
ORGANIZATION_CODE,
INVENTORY_ITEM_ID,
ITEM_NUMBER,
ITEM_CATALOG_GROUP_ID,
DATA_LEVEL_ID,
REVISION_ID,
REVISION,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
ROW_IDENTIFIER,
ATTR_GROUP_TYPE,
ATTR_GROUP_INT_NAME,
ATTR_GROUP_ID,
ATTR_INT_NAME,
ATTR_VALUE_STR,
ATTR_VALUE_NUM,
ATTR_VALUE_DATE,
ATTR_VALUE_UOM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
CHANGE_ID,
CHANGE_LINE_ID,
SOURCE_SYSTEM_ID,
SOURCE_SYSTEM_REFERENCE,
PROG_INT_CHAR1,
PROG_INT_NUM4,
BUNDLE_ID ) #';
l_select_sql := q'#
( SELECT
src.TRANSACTION_ID,
#'|| p_dest_process_status || q'# AS PROCESS_STATUS,
#'|| p_dest_data_set_id || q'# AS DATA_SET_ID,
#'|| l_dest_transaction_type|| q'# AS TRANSACTION_TYPE,
src.ORGANIZATION_ID,
src.ORGANIZATION_CODE,
src.INVENTORY_ITEM_ID,
src.ITEM_NUMBER,
src.ITEM_CATALOG_GROUP_ID,
src.DATA_LEVEL_ID,
src.REVISION_ID,
src.REVISION,
src.PK1_VALUE,
src.PK2_VALUE,
src.PK3_VALUE,
src.PK4_VALUE,
src.PK5_VALUE,
#'|| l_max_row_identifier || q'# + src.ROW_IDENTIFIER AS ROW_IDENTIFIER,
src.ATTR_GROUP_TYPE,
src.ATTR_GROUP_INT_NAME,
src.ATTR_GROUP_ID,
src.ATTR_INT_NAME,
src.ATTR_VALUE_STR,
src.ATTR_VALUE_NUM,
src.ATTR_VALUE_DATE,
src.ATTR_VALUE_UOM,
#' || G_USER_ID || q'# AS CREATED_BY,
SYSDATE AS CREATION_DATE,
#' || G_USER_ID || q'# AS LAST_UPDATED_BY,
SYSDATE AS LAST_UPDATE_DATE,
#' || G_LOGIN_ID || q'# AS LAST_UPDATE_LOGIN,
#' || G_REQUEST_ID || q'# AS REQUEST_ID,
src.CHANGE_ID,
src.CHANGE_LINE_ID,
src.SOURCE_SYSTEM_ID,
src.SOURCE_SYSTEM_REFERENCE,
#'|| l_prog_int_char1_value|| q'# AS PROG_INT_CHAR1,
#'|| l_prog_int_num4_value|| q'# AS PROG_INT_NUM4,
src.BUNDLE_ID
FROM (#' || p_source_entity_sql || q'#) src, EGO_FND_DSC_FLX_CTX_EXT ag_ext
WHERE ag_ext.DESCRIPTIVE_FLEXFIELD_NAME = NVL(src.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
AND ag_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE = src.ATTR_GROUP_INT_NAME
AND ag_ext.APPLICATION_ID = 431
AND ( ag_ext.MULTI_ROW= 'N'
OR NOT EXISTS (SELECT NULL FROM EGO_ITM_USR_ATTR_INTRFC intfx
WHERE intfx.DATA_SET_ID = src.DATA_SET_ID
AND intfx.PROCESS_STATUS = #'|| p_dest_process_status || q'#
AND intfx.INVENTORY_ITEM_ID = src.INVENTORY_ITEM_ID
AND intfx.ORGANIZATION_ID = src.ORGANIZATION_ID
AND intfx.DATA_LEVEL_ID = src.DATA_LEVEL_ID
AND NVL(intfx.BUNDLE_ID, -1) = NVL(src.BUNDLE_ID, -1)
AND NVL(intfx.REVISION_ID, -1) = NVL(src.REVISION_ID, -1)
AND NVL(intfx.PK1_VALUE, -1) = NVL(src.PK1_VALUE, -1)
AND NVL(intfx.PK2_VALUE, -1) = NVL(src.PK2_VALUE, -1)
AND NVL(intfx.PK3_VALUE, -1) = NVL(src.PK3_VALUE, -1)
AND NVL(intfx.PK4_VALUE, -1) = NVL(src.PK4_VALUE, -1)
AND NVL(intfx.PK5_VALUE, -1) = NVL(src.PK5_VALUE, -1)
AND NVL(intfx.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP') = NVL(src.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
AND intfx.ATTR_GROUP_INT_NAME = src.ATTR_GROUP_INT_NAME
)
)
) source #';
UPDATE SET
intf.ATTR_VALUE_STR = source.ATTR_VALUE_STR,
intf.ATTR_VALUE_NUM = source.ATTR_VALUE_NUM,
intf.ATTR_VALUE_UOM = source.ATTR_VALUE_UOM,
intf.ATTR_VALUE_DATE = source.ATTR_VALUE_DATE,
intf.PROG_INT_NUM4 = 3
WHERE NVL(intf.PROG_INT_NUM4, -1) <> 0
#';
INSERT #' || l_insert_cols || q'# VALUES
( source.TRANSACTION_ID,
source.PROCESS_STATUS,
source.DATA_SET_ID,
source.TRANSACTION_TYPE,
source.ORGANIZATION_ID,
source.ORGANIZATION_CODE,
source.INVENTORY_ITEM_ID,
source.ITEM_NUMBER,
source.ITEM_CATALOG_GROUP_ID,
source.DATA_LEVEL_ID,
source.REVISION_ID,
source.REVISION,
source.PK1_VALUE,
source.PK2_VALUE,
source.PK3_VALUE,
source.PK4_VALUE,
source.PK5_VALUE,
source.ROW_IDENTIFIER,
source.ATTR_GROUP_TYPE,
source.ATTR_GROUP_INT_NAME,
source.ATTR_GROUP_ID,
source.ATTR_INT_NAME,
source.ATTR_VALUE_STR,
source.ATTR_VALUE_NUM,
source.ATTR_VALUE_DATE,
source.ATTR_VALUE_UOM,
source.CREATED_BY,
source.CREATION_DATE,
source.LAST_UPDATED_BY,
source.LAST_UPDATE_DATE,
source.LAST_UPDATE_LOGIN,
source.REQUEST_ID,
source.CHANGE_ID,
source.CHANGE_LINE_ID,
source.SOURCE_SYSTEM_ID,
source.SOURCE_SYSTEM_REFERENCE,
source.PROG_INT_CHAR1,
source.PROG_INT_NUM4,
source.BUNDLE_ID )
#';
l_select_sql || l_where_clause || l_when_matched || l_when_not_matched;
l_attr_group_sql := 'SELECT DISTINCT ATTR_GROUP_ID FROM ( '|| p_source_attr_groups_sql ||' ) ';
/(SELECT CONVERSION_RATE
FROM MTL_UOM_CONVERSIONS
WHERE UOM_CLASS = '#' || i.UOM_CLASS ||q'#'
AND UOM_CODE = src.#' || l_dummy_char || q'#
AND ROWNUM = 1)#';
l_select_sql := q'#
SELECT
src.TRANSACTION_ID,
#'|| p_dest_process_status || q'# AS PROCESS_STATUS,
#'|| p_dest_data_set_id || q'# AS DATA_SET_ID,
#'|| l_dest_transaction_type|| q'# AS TRANSACTION_TYPE,
src.ORGANIZATION_ID,
src.ORGANIZATION_CODE,
src.INVENTORY_ITEM_ID,
src.ITEM_NUMBER,
src.ITEM_CATALOG_GROUP_ID,
src.DATA_LEVEL_ID,
src.REVISION_ID,
src.REVISION,
src.PK1_VALUE,
src.PK2_VALUE,
src.PK3_VALUE,
src.PK4_VALUE,
src.PK5_VALUE,
#'|| l_max_row_identifier || q'# + src.ROW_IDENTIFIER AS ROW_IDENTIFIER,
ag_ext.DESCRIPTIVE_FLEXFIELD_NAME,
ag_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE,
ag_ext.ATTR_GROUP_ID,
attr_col.END_USER_COLUMN_NAME, #' ||
l_attr_value_str_sql ||
l_attr_value_num_sql ||
l_attr_value_date_sql ||
l_attr_value_uom_sql ||
G_USER_ID || q'# AS CREATED_BY,
SYSDATE AS CREATION_DATE,
#' || G_USER_ID || q'# AS LAST_UPDATED_BY,
SYSDATE AS LAST_UPDATE_DATE,
#' || G_LOGIN_ID || q'# AS LAST_UPDATE_LOGIN,
#' || G_REQUEST_ID || q'# AS REQUEST_ID,
src.CHANGE_ID,
src.CHANGE_LINE_ID,
src.SOURCE_SYSTEM_ID,
src.SOURCE_SYSTEM_REFERENCE,
#'|| l_prog_int_char1_value|| q'# AS PROG_INT_CHAR1,
#'|| l_prog_int_num4_value|| q'# AS PROG_INT_NUM4,
src.BUNDLE_ID
FROM (#' || p_source_entity_sql || q'#) src,
FND_DESCR_FLEX_COLUMN_USAGES attr_col,
EGO_FND_DSC_FLX_CTX_EXT ag_ext #';
AND NOT EXISTS (SELECT NULL FROM EGO_ITM_USR_ATTR_INTRFC intf
WHERE intf.DATA_SET_ID = src.DATA_SET_ID
AND intf.PROCESS_STATUS = #' || p_dest_process_status || q'#
AND intf.INVENTORY_ITEM_ID = src.INVENTORY_ITEM_ID
AND intf.ORGANIZATION_ID = src.ORGANIZATION_ID
AND intf.DATA_LEVEL_ID = src.DATA_LEVEL_ID
AND NVL(intf.BUNDLE_ID, -1) = NVL(src.BUNDLE_ID, -1)
AND NVL(intf.REVISION_ID, -1) = NVL(src.REVISION_ID, -1)
AND NVL(intf.PK1_VALUE, -1) = NVL(src.PK1_VALUE, -1)
AND NVL(intf.PK2_VALUE, -1) = NVL(src.PK2_VALUE, -1)
AND NVL(intf.PK3_VALUE, -1) = NVL(src.PK3_VALUE, -1)
AND NVL(intf.PK4_VALUE, -1) = NVL(src.PK4_VALUE, -1)
AND NVL(intf.PK5_VALUE, -1) = NVL(src.PK5_VALUE, -1)
AND NVL(intf.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP') = ag_ext.DESCRIPTIVE_FLEXFIELD_NAME
AND intf.ATTR_GROUP_INT_NAME = ag_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE #';
l_dynamic_sql := 'INSERT INTO EGO_ITM_USR_ATTR_INTRFC ' || l_insert_cols || l_select_sql || l_where_clause;
Debug_Conc_Log('Copy_data_to_Intf: Inserted '||SQL%ROWCOUNT||' rows');
Debug_Conc_Log('Copy_data_to_Intf: Done inserting');
UPDATE ego_itm_usr_attr_intrfc
SET PROG_INT_NUM1 = NULL
,PROG_INT_NUM2 = NULL
,PROG_INT_NUM3 = NULL
,PROG_INT_CHAR1 = 'N'
,PROG_INT_CHAR2 = 'N'
,REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID
,PROGRAM_APPLICATION_ID = FND_GLOBAL.PROG_APPL_ID
,PROGRAM_ID = FND_GLOBAL.CONC_PROGRAM_ID
,PROGRAM_UPDATE_DATE = SYSDATE
WHERE PROCESS_STATUS = p_dest_process_status
AND DATA_SET_ID = p_dest_data_set_id
AND TRANSACTION_TYPE = p_dest_transaction_type
AND PROG_INT_CHAR1 IN ('FROM_INTF', 'FROM_PROD');