[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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_batch_id
AND uai.PROCESS_STATUS = 1
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_batch_id
AND uai.PROCESS_STATUS = 1
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 uai.DATA_LEVEL_ID = Nvl ((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')
),
(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_batch_id
AND uai.PROCESS_STATUS = 1
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 eagd, EGO_FND_DSC_FLX_CTX_EXT ag_ext
WHERE eagd.ATTR_GROUP_ID = ag_ext.ATTR_GROUP_ID
AND ag_ext.APPLICATION_ID = 431
AND ag_ext.DESCRIPTIVE_FLEXFIELD_NAME = NVL(uai.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
AND ag_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE = uai.ATTR_GROUP_INT_NAME
)
WHERE uai.DATA_SET_ID = p_batch_id
AND uai.PROCESS_STATUS = 1
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 eagd, EGO_FND_DSC_FLX_CTX_EXT ag_ext
WHERE eagd.ATTR_GROUP_ID = ag_ext.ATTR_GROUP_ID
AND ag_ext.APPLICATION_ID = 431
AND ag_ext.DESCRIPTIVE_FLEXFIELD_NAME = NVL(uai.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
AND ag_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE = uai.ATTR_GROUP_INT_NAME
) = 1;
UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI
SET INVENTORY_ITEM_ID = NVL((SELECT INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_KFV MSIK
WHERE MSIK.CONCATENATED_SEGMENTS = MIRI.ITEM_NUMBER
AND MSIK.ORGANIZATION_ID = MIRI.ORGANIZATION_ID
),
(SELECT INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE (MSII.ITEM_NUMBER = MIRI.ITEM_NUMBER OR MSII.SOURCE_SYSTEM_REFERENCE = MIRI.SOURCE_SYSTEM_REFERENCE)
AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
AND MSII.ORGANIZATION_ID = MIRI.ORGANIZATION_ID
AND MSII.SET_PROCESS_ID = MIRI.SET_PROCESS_ID
AND MSII.PROCESS_FLAG = 1
AND ROWNUM = 1
))
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 1
AND INVENTORY_ITEM_ID IS NULL
AND (ITEM_NUMBER IS NOT NULL OR SOURCE_SYSTEM_REFERENCE IS NOT NULL);
Debug_Conc_Log('Resolve_PKs_For_Child: Updated Revisions '||SQL%ROWCOUNT);
UPDATE MTL_ITEM_CATEGORIES_INTERFACE MICI
SET INVENTORY_ITEM_ID = NVL((SELECT INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_KFV MSIK
WHERE MSIK.CONCATENATED_SEGMENTS = MICI.ITEM_NUMBER
AND MSIK.ORGANIZATION_ID = MICI.ORGANIZATION_ID
),
(SELECT INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE (MSII.ITEM_NUMBER = MICI.ITEM_NUMBER OR MSII.SOURCE_SYSTEM_REFERENCE = MICI.SOURCE_SYSTEM_REFERENCE)
AND MSII.SOURCE_SYSTEM_ID = MICI.SOURCE_SYSTEM_ID
AND MSII.ORGANIZATION_ID = MICI.ORGANIZATION_ID
AND MSII.SET_PROCESS_ID = MICI.SET_PROCESS_ID
AND MSII.PROCESS_FLAG = 1
AND ROWNUM = 1
))
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 1
AND INVENTORY_ITEM_ID IS NULL
AND (ITEM_NUMBER IS NOT NULL OR SOURCE_SYSTEM_REFERENCE IS NOT NULL);
Debug_Conc_Log('Resolve_PKs_For_Child: Updated Categories '||SQL%ROWCOUNT);
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
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI.ATTR_GROUP_INT_NAME)
WHERE UAI.DATA_SET_ID = p_batch_id
AND UAI.PROCESS_STATUS = 1
AND UAI.ATTR_GROUP_ID IS NULL;
Debug_Conc_Log('Resolve_PKs_For_Child: Updated Attribute Group ID '||SQL%ROWCOUNT);
UPDATE EGO_ITM_USR_ATTR_INTRFC EIUAI
SET INVENTORY_ITEM_ID = NVL((SELECT INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_KFV MSIK
WHERE MSIK.CONCATENATED_SEGMENTS = EIUAI.ITEM_NUMBER
AND MSIK.ORGANIZATION_ID = EIUAI.ORGANIZATION_ID
),
--Bug 9660659 - Removing the OR in this query, making two queries - Bug 9660659
/*
(SELECT INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE (MSII.ITEM_NUMBER = EIUAI.ITEM_NUMBER OR MSII.SOURCE_SYSTEM_REFERENCE = EIUAI.SOURCE_SYSTEM_REFERENCE)
AND MSII.SOURCE_SYSTEM_ID = EIUAI.SOURCE_SYSTEM_ID
AND MSII.ORGANIZATION_ID = EIUAI.ORGANIZATION_ID
AND MSII.SET_PROCESS_ID = EIUAI.DATA_SET_ID
AND MSII.PROCESS_FLAG = 1
AND ROWNUM = 1
))
*/
NVL( (SELECT INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE (MSII.ITEM_NUMBER = EIUAI.ITEM_NUMBER)
AND MSII.SOURCE_SYSTEM_ID = EIUAI.SOURCE_SYSTEM_ID
AND MSII.ORGANIZATION_ID = EIUAI.ORGANIZATION_ID
AND MSII.SET_PROCESS_ID = EIUAI.DATA_SET_ID
AND MSII.PROCESS_FLAG = 1
AND ROWNUM = 1) ,
(SELECT INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE (MSII.SOURCE_SYSTEM_REFERENCE = EIUAI.SOURCE_SYSTEM_REFERENCE)
AND MSII.SOURCE_SYSTEM_ID = EIUAI.SOURCE_SYSTEM_ID
AND MSII.ORGANIZATION_ID = EIUAI.ORGANIZATION_ID
AND MSII.SET_PROCESS_ID = EIUAI.DATA_SET_ID
AND MSII.PROCESS_FLAG = 1
AND ROWNUM = 1)
) -- End of inner NVL
) --End of outer NVL
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 1
AND INVENTORY_ITEM_ID IS NULL
AND (ITEM_NUMBER IS NOT NULL OR SOURCE_SYSTEM_REFERENCE IS NOT NULL);
Debug_Conc_Log('Resolve_PKs_For_Child: Updated User Attrs '||SQL%ROWCOUNT);
UPDATE EGO_ITEM_ASSOCIATIONS_INTF EIAI
SET INVENTORY_ITEM_ID = NVL((SELECT INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_KFV MSIK
WHERE MSIK.CONCATENATED_SEGMENTS = EIAI.ITEM_NUMBER
AND MSIK.ORGANIZATION_ID = EIAI.ORGANIZATION_ID
),
(SELECT INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE (MSII.ITEM_NUMBER = EIAI.ITEM_NUMBER OR MSII.SOURCE_SYSTEM_REFERENCE = EIAI.SOURCE_SYSTEM_REFERENCE)
AND MSII.SOURCE_SYSTEM_ID = EIAI.SOURCE_SYSTEM_ID
AND MSII.ORGANIZATION_ID = EIAI.ORGANIZATION_ID
AND MSII.SET_PROCESS_ID = EIAI.BATCH_ID
AND MSII.PROCESS_FLAG = 1
AND ROWNUM = 1
))
WHERE BATCH_ID = p_batch_id
AND PROCESS_FLAG = 1
AND INVENTORY_ITEM_ID IS NULL
AND (ITEM_NUMBER IS NOT NULL OR SOURCE_SYSTEM_REFERENCE IS NOT NULL);
Debug_Conc_Log('Resolve_PKs_For_Child: Updated Intersections '||SQL%ROWCOUNT);
UPDATE EGO_ITM_USR_ATTR_INTRFC EIUAI
SET REVISION_ID = NVL((SELECT REVISION_ID
FROM MTL_ITEM_REVISIONS_B MIRB
WHERE MIRB.INVENTORY_ITEM_ID = EIUAI.INVENTORY_ITEM_ID
AND MIRB.ORGANIZATION_ID = EIUAI.ORGANIZATION_ID
AND MIRB.REVISION = EIUAI.REVISION
),
(SELECT REVISION_ID
FROM MTL_ITEM_REVISIONS_INTERFACE MIRI
WHERE MIRI.INVENTORY_ITEM_ID = EIUAI.INVENTORY_ITEM_ID
AND MIRI.ORGANIZATION_ID = EIUAI.ORGANIZATION_ID
AND MIRI.REVISION = EIUAI.REVISION
AND MIRI.SET_PROCESS_ID = EIUAI.DATA_SET_ID
AND MIRI.PROCESS_FLAG = 1
AND ROWNUM = 1
))
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 1
AND REVISION_ID IS NULL
AND REVISION IS NOT NULL
AND INVENTORY_ITEM_ID IS NOT NULL;
Debug_Conc_Log('Resolve_PKs_For_Child: Updated Revision_id for User Attrs '||SQL%ROWCOUNT);
l_template_table_sql :=' SELECT * '||
' FROM ( '||
' SELECT MIN(CATALOG_LEVEL) OVER (PARTITION BY ROOT_CLASS, ATTRIBUTE_ID) MIN_LEVEL '||
' ,ROOT_CLASS CLASSIFICATION_CODE '||
' ,CATALOG_LEVEL '||
' ,TEMPLATE_ID,ATTRIBUTE_GROUP_ID,ATTRIBUTE_ID,ENABLED_FLAG,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN '||
' ,ROW_NUMBER,ATTRIBUTE_STRING_VALUE,ATTRIBUTE_NUMBER_VALUE,ATTRIBUTE_DATE_VALUE,ATTRIBUTE_TRANSLATED_VALUE,ATTRIBUTE_UOM_CODE '||
' ,REQUEST_ID,PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,DATA_LEVEL_ID '||
' FROM EGO_TEMPL_ATTRIBUTES TEMPL, '||
' (SELECT CONNECT_BY_ROOT ITEM_CATALOG_GROUP_ID ROOT_CLASS, ITEM_CATALOG_GROUP_ID, PARENT_CATALOG_GROUP_ID, LEVEL CATALOG_LEVEL '||
' FROM MTL_ITEM_CATALOG_GROUPS_B '||
' CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID= ITEM_CATALOG_GROUP_ID '||
' START WITH ITEM_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID '||
' ) CATALOG '||
' WHERE TEMPL.CLASSIFICATION_CODE= CATALOG.ITEM_CATALOG_GROUP_ID '||
' AND ENABLED_FLAG = ''Y'' '||
' ) '||
' WHERE MIN_LEVEL = CATALOG_LEVEL ';
l_class_code_hierarchy_sql := ' SELECT item_catalog_group_id FROM MTL_ITEM_CATALOG_GROUPS_B TEMPL CONNECT BY PRIOR TEMPL.PARENT_CATALOG_GROUP_ID = TEMPL.ITEM_CATALOG_GROUP_ID START WITH TEMPL.ITEM_CATALOG_GROUP_ID = ENTITIES.ITEM_CATALOG_GROUP_ID ';
,p_program_update_date => SYSDATE
,p_current_user_party_id => p_user_id
,p_target_entity_sql => p_entity_sql
,p_process_status => '2' -- Bug 10263673
,p_class_code_hierarchy_sql => l_class_code_hierarchy_sql
,p_hierarchy_template_tbl_sql => l_template_table_sql
,x_return_status => l_return_status
,x_errorcode => l_errorcode
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
,p_program_update_date => SYSDATE
,p_current_user_party_id => p_user_id
,p_target_entity_sql => p_gdsn_entity_sql
,p_process_status => '2' -- Bug 10263673
,p_class_code_hierarchy_sql => l_class_code_hierarchy_sql
,p_hierarchy_template_tbl_sql => l_template_table_sql
,x_return_status => l_return_status
,x_errorcode => l_errorcode
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
,p_program_update_date => SYSDATE
,p_current_user_party_id => p_user_id
,p_target_entity_sql => p_gdsn_entity_sql
,p_process_status => '2' -- Bug 10263673
,p_class_code_hierarchy_sql => l_class_code_hierarchy_sql
,p_hierarchy_template_tbl_sql => l_template_table_sql
,x_return_status => l_return_status
,x_errorcode => l_errorcode
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
SELECT ATTR_GROUP_TYPE, DATA_LEVEL_ID, DATA_LEVEL_NAME
FROM EGO_DATA_LEVEL_B
WHERE ATTR_GROUP_TYPE IN ('EGO_ITEMMGMT_GROUP', 'EGO_ITEM_GTIN_ATTRS', 'EGO_ITEM_GTIN_MULTI_ATTRS')
AND APPLICATION_ID = 431
AND DATA_LEVEL_NAME IN ( 'ITEM_LEVEL', 'ITEM_REVISION_LEVEL', 'ITEM_ORG' );
SELECT TEMPLATE_ID
BULK COLLECT INTO l_template_table
FROM EGO_IMPORT_COPY_OPTIONS
WHERE BATCH_ID = p_batch_id
AND COPY_OPTION = 'APPLY_TEMPLATE'
ORDER BY TEMPLATE_SEQUENCE DESC;
l_sql_part1 := 'SELECT ' || l_template_table(i) || ' AS TEMPLATE_ID, ';
/* Bug 9201112. Update the query l_entity_sql to considers ITEM_ORG Data Level also. */
/* Bug 9678667 - Added the hint */
--pnagasur:Query modified below for Bug 13394823
l_entity_sql := q'#
SELECT
MIRI.TEMPLATE_ID,
MSIB.INVENTORY_ITEM_ID ,
MSIB.ORGANIZATION_ID,
MSIB.ITEM_CATALOG_GROUP_ID,
NULL AS PK1_VALUE,
NULL AS PK2_VALUE,
NULL AS PK3_VALUE,
NULL AS PK4_VALUE,
NULL AS PK5_VALUE,
MIRI.REVISION_ID, #' ||
l_item_rev_dl_id || q'# AS DATA_LEVEL_ID
FROM
MTL_ITEM_REVISIONS_INTERFACE MIRI,
MTL_SYSTEM_ITEMS_B MSIB
WHERE MSIB.ITEM_CATALOG_GROUP_ID IS NOT NULL
AND MIRI.TEMPLATE_ID IS NOT NULL
AND MIRI.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND MIRI.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
AND MIRI.SET_PROCESS_ID = #' || p_batch_id || q'#
AND MIRI.PROCESS_FLAG = 1
AND NOT EXISTS
(SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.INVENTORY_ITEM_ID = MIRI.INVENTORY_ITEM_ID
AND MSII.ORGANIZATION_ID = MIRI.ORGANIZATION_ID
AND MSII.SET_PROCESS_ID = MIRI.SET_PROCESS_ID
AND MSII.PROCESS_FLAG = MIRI.PROCESS_FLAG)
UNION ALL
SELECT /*+ LEADING(MSII) USE_NL_WITH_INDEX(MIRI, MTL_ITEM_REVS_INTERFACE_N2 ) */
MSII.TEMPLATE_ID,
MSII.INVENTORY_ITEM_ID ,
MSII.ORGANIZATION_ID,
MSII.ITEM_CATALOG_GROUP_ID,
NULL AS PK1_VALUE,
NULL AS PK2_VALUE,
NULL AS PK3_VALUE,
NULL AS PK4_VALUE,
NULL AS PK5_VALUE,
(CASE WHEN MIRI.REVISION_Id IS NULL
THEN (SELECT Max(revision_id) FROM
(SELECT revision_id, INVENTORY_ITEM_ID, ORGANIZATION_ID, dense_rank() over (PARTITION BY INVENTORY_ITEM_ID, ORGANIZATION_ID ORDER BY EFFECTIVITY_DATE DESC ) dr
FROM MTL_ITEM_REVISIONS_B mirb1
WHERE mirb1.EFFECTIVITY_DATE <= SYSDATE )
WHERE INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = MSII.ORGANIZATION_ID
AND dr =1
)
ELSE MIRI.REVISION_ID
END) REVISION_ID, #' ||
l_item_rev_dl_id || q'# AS DATA_LEVEL_ID
FROM
MTL_SYSTEM_ITEMS_INTERFACE MSII,
MTL_ITEM_REVISIONS_INTERFACE MIRI
WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
AND MSII.TEMPLATE_ID IS NOT NULL
AND MIRI.INVENTORY_ITEM_ID(+) = MSII.INVENTORY_ITEM_ID
AND MIRI.ORGANIZATION_ID(+) = MSII.ORGANIZATION_ID
AND MIRI.SET_PROCESS_ID(+) = MSII.SET_PROCESS_ID
AND MIRI.PROCESS_FLAG(+) = 1
AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
AND MSII.PROCESS_FLAG = 1
UNION ALL
SELECT
MSII.TEMPLATE_ID,
MSII.INVENTORY_ITEM_ID ,
MSII.ORGANIZATION_ID,
MSII.ITEM_CATALOG_GROUP_ID,
NULL AS PK1_VALUE,
NULL AS PK2_VALUE,
NULL AS PK3_VALUE,
NULL AS PK4_VALUE,
NULL AS PK5_VALUE,
NULL AS REVISION_ID, #' ||
l_item_dl_id || q'# AS DATA_LEVEL_ID
FROM
MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
AND MSII.TEMPLATE_ID IS NOT NULL
AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
AND MSII.PROCESS_FLAG = 1
UNION ALL
SELECT
MSII.TEMPLATE_ID,
MSII.INVENTORY_ITEM_ID ,
MSII.ORGANIZATION_ID,
MSII.ITEM_CATALOG_GROUP_ID,
NULL AS PK1_VALUE,
NULL AS PK2_VALUE,
NULL AS PK3_VALUE,
NULL AS PK4_VALUE,
NULL AS PK5_VALUE,
NULL AS REVISION_ID, #' ||
l_item_org_dl_id || q'# AS DATA_LEVEL_ID
FROM
MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
AND MSII.TEMPLATE_ID IS NOT NULL
AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
AND MSII.PROCESS_FLAG = 1 #';
SELECT
MSII.TEMPLATE_ID,
MSII.INVENTORY_ITEM_ID ,
MSII.ORGANIZATION_ID,
MSII.ITEM_CATALOG_GROUP_ID,
NULL AS PK1_VALUE,
NULL AS PK2_VALUE,
NULL AS PK3_VALUE,
NULL AS PK4_VALUE,
NULL AS PK5_VALUE,
NULL AS REVISION_ID, #' ||
l_item_gtin_dl_id || q'# AS DATA_LEVEL_ID
FROM
MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
AND MSII.TEMPLATE_ID IS NOT NULL
AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
AND MSII.PROCESS_FLAG = 1
AND NVL(MSII.GDSN_OUTBOUND_ENABLED_FLAG, 'N') = 'Y'
UNION ALL
SELECT
MSII.TEMPLATE_ID,
MSII.INVENTORY_ITEM_ID ,
MSII.ORGANIZATION_ID,
MSII.ITEM_CATALOG_GROUP_ID,
NULL AS PK1_VALUE,
NULL AS PK2_VALUE,
NULL AS PK3_VALUE,
NULL AS PK4_VALUE,
NULL AS PK5_VALUE,
NULL AS REVISION_ID, #' ||
l_item_gtin_multi_dl_id || q'# AS DATA_LEVEL_ID
FROM
MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
AND MSII.TEMPLATE_ID IS NOT NULL
AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
AND NVL(MSII.GDSN_OUTBOUND_ENABLED_FLAG, 'N') = 'Y'
AND MSII.PROCESS_FLAG = 1 #';
SELECT
edlb.DATA_LEVEL_ID,
edlb.DATA_LEVEL_NAME
FROM
EGO_IMPORT_COPY_OPTIONS eico,
EGO_DATA_LEVEL_B edlb,
EGO_FND_DSC_FLX_CTX_EXT ag_ext
WHERE eico.COPY_OPTION LIKE 'COPY_ATTR_GROUP%'
AND eico.BATCH_ID = p_batch_id
AND edlb.DATA_LEVEL_NAME = RTRIM(SUBSTR(eico.COPY_OPTION, INSTR(eico.COPY_OPTION, ':')+1))
AND edlb.APPLICATION_ID = 431
AND edlb.APPLICATION_ID = ag_ext.APPLICATION_ID
AND edlb.ATTR_GROUP_TYPE = ag_ext.DESCRIPTIVE_FLEXFIELD_NAME
AND ag_ext.ATTR_GROUP_ID = eico.ATTR_GROUP_ID
GROUP BY edlb.DATA_LEVEL_ID, edlb.DATA_LEVEL_NAME
UNION
SELECT
DATA_LEVEL_ID,
DATA_LEVEL_NAME
FROM EGO_DATA_LEVEL_B
WHERE APPLICATION_ID = 431
AND ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
AND DATA_LEVEL_NAME = 'ITEM_REVISION_LEVEL';
l_rev_id_sql := q'#(SELECT MAX(miri.REVISION_ID) KEEP (DENSE_RANK FIRST ORDER BY miri.REVISION)
FROM MTL_ITEM_REVISIONS_INTERFACE miri
WHERE miri.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
AND miri.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND miri.SET_PROCESS_ID = msii.SET_PROCESS_ID
AND miri.PROCESS_FLAG = 1
) AS REVISION_ID, #';
SELECT
ROWNUM AS ROW_IDENTIFIER,
msii.INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID,
msii.ORGANIZATION_ID AS ORGANIZATION_ID,
msii.SET_PROCESS_ID AS DATA_SET_ID,
msii.ORGANIZATION_CODE,
msii.ITEM_NUMBER,
msii.ITEM_CATALOG_GROUP_ID, #' || l_rev_id_sql || q'#
NULL AS REVISION,
ext_prod.DATA_LEVEL_ID,
ext_prod.PK1_VALUE,
ext_prod.PK2_VALUE,
ext_prod.PK3_VALUE,
ext_prod.PK4_VALUE,
ext_prod.PK5_VALUE,
NULL AS CHANGE_ID,
NULL AS CHANGE_LINE_ID,
msii.SOURCE_SYSTEM_ID,
msii.SOURCE_SYSTEM_REFERENCE,
msii.BUNDLE_ID,
msii.TRANSACTION_ID,
ext_prod.ATTR_GROUP_ID,
ext_prod.EXTENSION_ID, #' || G_PROD_COL_LIST || q'#
FROM
EGO_IMPORT_COPY_OPTIONS eico,
MTL_SYSTEM_ITEMS_INTERFACE msii,
EGO_MTL_SY_ITEMS_EXT_VL ext_prod
WHERE eico.COPY_OPTION = 'COPY_ATTR_GROUP:#' || i.DATA_LEVEL_NAME || q'#'
AND eico.BATCH_ID = #' || p_batch_id || q'#
AND eico.BATCH_ID = msii.SET_PROCESS_ID
AND msii.PROCESS_FLAG = 1
AND msii.COPY_ITEM_ID = ext_prod.INVENTORY_ITEM_ID
AND msii.ORGANIZATION_ID = ext_prod.ORGANIZATION_ID #' || l_rev_where_clause || q'#
AND ext_prod.DATA_LEVEL_ID = #' || i.DATA_LEVEL_ID || l_pk_where || q'#
AND ext_prod.ATTR_GROUP_ID = eico.ATTR_GROUP_ID #';
SELECT
ext_prod.ATTR_GROUP_ID
FROM
EGO_IMPORT_COPY_OPTIONS eico,
MTL_SYSTEM_ITEMS_INTERFACE msii,
EGO_MTL_SY_ITEMS_EXT_VL ext_prod
WHERE eico.COPY_OPTION = 'COPY_ATTR_GROUP:#' || i.DATA_LEVEL_NAME || q'#'
AND eico.BATCH_ID = #' || p_batch_id || q'#
AND eico.BATCH_ID = msii.SET_PROCESS_ID
AND msii.PROCESS_FLAG = 1
AND msii.COPY_ITEM_ID = ext_prod.INVENTORY_ITEM_ID
AND msii.ORGANIZATION_ID = ext_prod.ORGANIZATION_ID #' || l_rev_where_clause || q'#
AND ext_prod.DATA_LEVEL_ID = #' || i.DATA_LEVEL_ID || l_pk_where || q'#
AND ext_prod.ATTR_GROUP_ID = eico.ATTR_GROUP_ID #';
SELECT ATTR_GROUP_TYPE, DATA_LEVEL_ID, DATA_LEVEL_NAME
FROM EGO_DATA_LEVEL_B
WHERE ATTR_GROUP_TYPE IN ('EGO_ITEMMGMT_GROUP', 'EGO_ITEM_GTIN_ATTRS', 'EGO_ITEM_GTIN_MULTI_ATTRS')
AND APPLICATION_ID = 431
AND DATA_LEVEL_NAME IN ( 'ITEM_LEVEL', 'ITEM_REVISION_LEVEL', 'ITEM_ORG' );
SELECT
msii.TRANSACTION_ID,
msii.INVENTORY_ITEM_ID,
msii.ORGANIZATION_ID,
NULL AS REVISION_ID,
msii.ITEM_CATALOG_GROUP_ID,
msii.ITEM_NUMBER,
msii.ORGANIZATION_CODE,
NULL AS PK1_VALUE,
NULL AS PK2_VALUE,
NULL AS PK3_VALUE,
NULL AS PK4_VALUE,
NULL AS PK5_VALUE,
#' || l_item_dl_id || q'# AS DATA_LEVEL_ID
FROM
MTL_SYSTEM_ITEMS_INTERFACE msii, MTL_PARAMETERS mp
WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
AND msii.ITEM_CATALOG_GROUP_ID IS NOT NULL
AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
AND msii.TRANSACTION_TYPE = 'CREATE'
AND NVL(msii.STYLE_ITEM_FLAG, 'Y') = 'Y'
AND msii.ORGANIZATION_ID = mp.ORGANIZATION_ID
AND mp.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID
UNION ALL
SELECT
msii.TRANSACTION_ID,
msii.INVENTORY_ITEM_ID,
msii.ORGANIZATION_ID,
NULL AS REVISION_ID,
msii.ITEM_CATALOG_GROUP_ID,
msii.ITEM_NUMBER,
msii.ORGANIZATION_CODE,
NULL AS PK1_VALUE,
NULL AS PK2_VALUE,
NULL AS PK3_VALUE,
NULL AS PK4_VALUE,
NULL AS PK5_VALUE,
#' || l_item_org_dl_id || q'# AS DATA_LEVEL_ID
FROM
MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
AND msii.ITEM_CATALOG_GROUP_ID IS NOT NULL
AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
AND msii.TRANSACTION_TYPE = 'CREATE'
AND NVL(msii.STYLE_ITEM_FLAG, 'Y') = 'Y'
UNION ALL
SELECT /*+ leading(miri) use_nl_with_index(msii, MTL_SYSTEM_ITEMS_INTERFACE_N1) */
miri.TRANSACTION_ID,
miri.INVENTORY_ITEM_ID,
miri.ORGANIZATION_ID,
miri.REVISION_ID,
msii.ITEM_CATALOG_GROUP_ID,
miri.ITEM_NUMBER,
miri.ORGANIZATION_CODE,
NULL AS PK1_VALUE,
NULL AS PK2_VALUE,
NULL AS PK3_VALUE,
NULL AS PK4_VALUE,
NULL AS PK5_VALUE,
#' || l_item_rev_dl_id || q'# AS DATA_LEVEL_ID
FROM
MTL_SYSTEM_ITEMS_INTERFACE msii,
MTL_ITEM_REVISIONS_INTERFACE miri
WHERE miri.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
AND miri.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND msii.ITEM_CATALOG_GROUP_ID IS NOT NULL
AND miri.SET_PROCESS_ID = msii.SET_PROCESS_ID
AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
AND NVL(msii.STYLE_ITEM_FLAG, 'Y') = 'Y'
AND msii.TRANSACTION_TYPE = 'CREATE'
AND miri.SET_PROCESS_ID = #' || p_batch_id || q'#
AND miri.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'# #';
EGO_USER_ATTRS_BULK_PVT.Insert_Default_Val_Rows (
p_api_version =>1.0
,p_application_id =>431
,p_attr_group_type =>'EGO_ITEMMGMT_GROUP'
,p_object_name =>'EGO_ITEM'
,p_interface_table_name =>'EGO_ITM_USR_ATTR_INTRFC'
,p_data_set_id => p_batch_id
,p_target_entity_sql => l_entity_sql
,p_attr_groups_to_exclude => l_exclude_ag_sql
,p_additional_class_Code_query => 'SELECT PARENT_CATALOG_GROUP_ID FROM EGO_ITEM_CAT_DENORM_HIER WHERE CHILD_CATALOG_GROUP_ID = ENTITY.ITEM_CATALOG_GROUP_ID '
,p_extra_column_names => 'PROG_INT_CHAR1 '
,p_extra_column_values => ' ''EXT_DEFAULT_VAL_ROW'' '
,x_return_status => l_return_status
,x_msg_data => l_err_msg);
Debug_Conc_Log('Do_AGLevel_UDA_Defaulting: Done EGO_USER_ATTRS_BULK_PVT.Insert_Default_Val_Rows l_return_status, l_err_msg='||l_return_status||','||l_err_msg);
DELETE FROM EGO_ITM_USR_ATTR_INTRFC ATTRS
-- WHERE ATTRS.DATA_SET_ID = p_data_set_id
WHERE ATTRS.DATA_SET_ID = p_batch_id
AND ATTRS.ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
-- Bug 13434831 : Start
-- Need to check for data_level_id instead of revision_id
-- AND ATTRS.REVISION_ID IS NULL
AND ATTRS.DATA_LEVEL_ID = l_item_dl_id
-- Bug 13434831 : End
--AND ATTRS.PROCESS_STATUS = G_PS_IN_PROCESS
AND ATTRS.PROCESS_STATUS = l_g_ps_in_process
AND ATTRS.PROG_INT_CHAR1 = 'EXT_DEFAULT_VAL_ROW'
AND EXISTS (SELECT 'X'
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = ATTRS.ORGANIZATION_ID
AND ORGANIZATION_ID <> MASTER_ORGANIZATION_ID);
DELETE EGO_ITM_USR_ATTR_INTRFC EIUAI
WHERE EIUAI.DATA_SET_ID = p_batch_id
AND EIUAI.PROCESS_STATUS = l_g_ps_in_process
AND EIUAI.ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
AND EIUAI.PROG_INT_CHAR1 = 'EXT_DEFAULT_VAL_ROW'
AND (SELECT VARIANT FROM EGO_FND_DSC_FLX_CTX_EXT EXT
WHERE APPLICATION_ID = 431
AND EIUAI.ATTR_GROUP_INT_NAME = DESCRIPTIVE_FLEX_CONTEXT_CODE
AND DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_ITEMMGMT_GROUP') = 'Y'
AND EXISTS (SELECT 1 FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.INVENTORY_ITEM_ID = EIUAI.INVENTORY_ITEM_ID
AND MSII.ORGANIZATION_ID = EIUAI.ORGANIZATION_ID
AND MSII.SET_PROCESS_ID = EIUAI.DATA_SET_ID
AND NVL(MSII.STYLE_ITEM_FLAG, 'N') = 'Y'
AND MSII.PROCESS_FLAG = p_msii_miri_process_flag ) ;
SELECT
msii.TRANSACTION_ID,
msii.INVENTORY_ITEM_ID,
msii.ORGANIZATION_ID,
NULL AS REVISION_ID,
msii.ITEM_CATALOG_GROUP_ID,
msii.ITEM_NUMBER,
msii.ORGANIZATION_CODE,
NULL AS PK1_VALUE,
NULL AS PK2_VALUE,
NULL AS PK3_VALUE,
NULL AS PK4_VALUE,
NULL AS PK5_VALUE,
#' || l_item_gtin_dl_id || q'# AS DATA_LEVEL_ID
FROM
MTL_SYSTEM_ITEMS_INTERFACE msii, MTL_PARAMETERS mp
WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
AND msii.ITEM_CATALOG_GROUP_ID IS NOT NULL
AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
AND NVL(msii.GDSN_OUTBOUND_ENABLED_FLAG, 'N') = 'Y'
AND NVL(msii.STYLE_ITEM_FLAG, 'Y') = 'Y'
AND msii.TRANSACTION_TYPE = 'CREATE'
AND msii.ORGANIZATION_ID = mp.ORGANIZATION_ID
AND mp.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID
UNION ALL
SELECT
msii.TRANSACTION_ID,
msii.INVENTORY_ITEM_ID,
msii.ORGANIZATION_ID,
NULL AS REVISION_ID,
msii.ITEM_CATALOG_GROUP_ID,
msii.ITEM_NUMBER,
msii.ORGANIZATION_CODE,
NULL AS PK1_VALUE,
NULL AS PK2_VALUE,
NULL AS PK3_VALUE,
NULL AS PK4_VALUE,
NULL AS PK5_VALUE,
#' || l_item_gtin_multi_dl_id || q'# AS DATA_LEVEL_ID
FROM
MTL_SYSTEM_ITEMS_INTERFACE msii, MTL_PARAMETERS mp
WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
AND msii.ITEM_CATALOG_GROUP_ID IS NOT NULL
AND NVL(msii.GDSN_OUTBOUND_ENABLED_FLAG, 'N') = 'Y'
AND NVL(msii.STYLE_ITEM_FLAG, 'Y') = 'Y'
AND msii.TRANSACTION_TYPE = 'CREATE'
AND msii.ORGANIZATION_ID = mp.ORGANIZATION_ID
AND mp.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID
AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'# #';
EGO_USER_ATTRS_BULK_PVT.Insert_Default_Val_Rows (
p_api_version =>1.0
,p_application_id =>431
,p_attr_group_type =>'EGO_ITEM_GTIN_ATTRS'
,p_object_name =>'EGO_ITEM'
,p_interface_table_name =>'EGO_ITM_USR_ATTR_INTRFC'
,p_data_set_id => p_batch_id
,p_target_entity_sql => l_entity_sql
,p_attr_groups_to_exclude => l_exclude_ag_sql
,p_additional_class_Code_query => 'SELECT PARENT_CATALOG_GROUP_ID FROM EGO_ITEM_CAT_DENORM_HIER WHERE CHILD_CATALOG_GROUP_ID = ENTITY.ITEM_CATALOG_GROUP_ID '
,p_extra_column_names => 'PROG_INT_CHAR1'
,p_extra_column_values => ' ''EXT_DEFAULT_VAL_ROW'' '
,x_return_status => l_return_status
,x_msg_data => l_err_msg);
Debug_Conc_Log('Do_AGLevel_UDA_Defaulting: Done EGO_USER_ATTRS_BULK_PVT.Insert_Default_Val_Rows l_return_status, l_err_msg='||l_return_status||','||l_err_msg);
SELECT
eiai.TRANSACTION_ID,
eiai.INVENTORY_ITEM_ID,
eiai.ORGANIZATION_ID,
NULL AS REVISION_ID,
( NVL( (SELECT ITEM_CATALOG_GROUP_ID
FROM MTL_SYSTEM_ITEMS_B msib
WHERE msib.INVENTORY_ITEM_ID = eiai.INVENTORY_ITEM_ID
AND msib.ORGANIZATION_ID = eiai.ORGANIZATION_ID
),
(SELECT ITEM_CATALOG_GROUP_ID
FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE msii.INVENTORY_ITEM_ID = eiai.INVENTORY_ITEM_ID
AND msii.ORGANIZATION_ID = eiai.ORGANIZATION_ID
AND msii.SET_PROCESS_ID = eiai.BATCH_ID
AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
AND ROWNUM = 1
)
)
) AS ITEM_CATALOG_GROUP_ID,
eiai.ITEM_NUMBER,
eiai.ORGANIZATION_CODE,
eiai.PK1_VALUE,
eiai.PK2_VALUE,
NULL AS PK3_VALUE,
NULL AS PK4_VALUE,
NULL AS PK5_VALUE,
eiai.DATA_LEVEL_ID
FROM
EGO_ITEM_ASSOCIATIONS_INTF eiai
WHERE eiai.BATCH_ID = #' || p_batch_id || q'#
AND NVL(eiai.STYLE_ITEM_FLAG, 'Y') = 'Y'
AND eiai.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'# #';
EGO_USER_ATTRS_BULK_PVT.Insert_Default_Val_Rows (
p_api_version =>1.0
,p_application_id =>431
,p_attr_group_type =>'EGO_ITEMMGMT_GROUP'
,p_object_name =>'EGO_ITEM'
,p_interface_table_name =>'EGO_ITM_USR_ATTR_INTRFC'
,p_data_set_id => p_batch_id
,p_target_entity_sql => l_entity_sql
,p_attr_groups_to_exclude => l_exclude_ag_sql
,p_additional_class_Code_query => 'SELECT PARENT_CATALOG_GROUP_ID FROM EGO_ITEM_CAT_DENORM_HIER WHERE CHILD_CATALOG_GROUP_ID = ENTITY.ITEM_CATALOG_GROUP_ID '
,p_extra_column_names => 'PROG_INT_CHAR1 '
,p_extra_column_values => ' ''EXT_DEFAULT_VAL_ROW'' '
,x_return_status => l_return_status
,x_msg_data => l_err_msg);
Debug_Conc_Log('Do_AGLevel_UDA_Defaulting: For Intersections - Done EGO_USER_ATTRS_BULK_PVT.Insert_Default_Val_Rows l_return_status, l_err_msg='||l_return_status||','||l_err_msg);
SELECT NVL(SELECTION_FLAG, 'N') INTO l_copy_first
FROM EGO_IMPORT_COPY_OPTIONS
WHERE BATCH_ID = p_batch_id
AND COPY_OPTION = 'COPY_FIRST';
SELECT TEMPLATE_ID
BULK COLLECT INTO l_template_table
FROM EGO_IMPORT_COPY_OPTIONS
WHERE BATCH_ID = p_batch_id
AND COPY_OPTION = 'APPLY_TEMPLATE'
ORDER BY TEMPLATE_SEQUENCE DESC;
INSERT INTO MTL_ITEM_CATEGORIES_INTERFACE
(
INVENTORY_ITEM_ID,
ITEM_NUMBER,
ORGANIZATION_ID,
CATEGORY_SET_ID,
CATEGORY_ID,
PROCESS_FLAG,
SET_PROCESS_ID,
TRANSACTION_TYPE,
SOURCE_SYSTEM_ID,
CREATED_BY
)
SELECT
msik.INVENTORY_ITEM_ID,
msik.CONCATENATED_SEGMENTS AS ITEM_NUMBER,
msik.ORGANIZATION_ID,
mici.CATEGORY_SET_ID,
mici.CATEGORY_ID,
1 PROCESS_FLAG,
p_batch_id,
'CREATE',
l_pdh_ss_id,
-99
FROM
MTL_ITEM_CATEGORIES_INTERFACE mici,
MTL_SYSTEM_ITEMS_KFV msik
WHERE mici.SET_PROCESS_ID = p_batch_id
AND msik.STYLE_ITEM_ID = mici.INVENTORY_ITEM_ID
AND msik.ORGANIZATION_ID = mici.ORGANIZATION_ID
AND mici.REQUEST_ID = l_request_id
AND mici.TRANSACTION_TYPE = 'CREATE'
AND mici.PROCESS_FLAG = 7
AND msik.STYLE_ITEM_FLAG = 'N'
AND NOT EXISTS (SELECT 1 FROM MTL_DEFAULT_CATEGORY_SETS dcs WHERE dcs.CATEGORY_SET_ID = mici.CATEGORY_SET_ID)
AND NOT EXISTS (SELECT 1
FROM MTL_ITEM_CATEGORIES mic
WHERE mic.CATEGORY_SET_ID = mici.CATEGORY_SET_ID
AND mic.CATEGORY_ID = mici.CATEGORY_ID
AND mic.INVENTORY_ITEM_ID = msik.INVENTORY_ITEM_ID
AND mic.ORGANIZATION_ID = msik.ORGANIZATION_ID)
AND NOT EXISTS (SELECT NULL
FROM MTL_ITEM_CATEGORIES_INTERFACE mici1
WHERE mici1.SET_PROCESS_ID = p_batch_id
AND mici1.PROCESS_FLAG = 1
AND (mici1.INVENTORY_ITEM_ID = msik.INVENTORY_ITEM_ID OR mici1.ITEM_NUMBER = msik.CONCATENATED_SEGMENTS)
AND mici1.ORGANIZATION_ID = msik.ORGANIZATION_ID
AND (mici1.CATEGORY_SET_ID = mici.CATEGORY_SET_ID
OR mici1.CATEGORY_SET_NAME = (SELECT mcs.CATEGORY_SET_NAME
FROM MTL_CATEGORY_SETS_VL mcs
WHERE mcs.CATEGORY_SET_ID = mici.CATEGORY_SET_ID
)
)
AND (mici1.CATEGORY_ID = mici.CATEGORY_ID
OR mici1.CATEGORY_NAME = (SELECT mc.CONCATENATED_SEGMENTS
FROM MTL_CATEGORIES_KFV mc
WHERE mc.CATEGORY_ID = mici.CATEGORY_ID
)
)
);
Debug_Conc_Log('Copy_New_Cat_Assgns_From_Style: Inserted records count='||SQL%ROWCOUNT);
INSERT INTO MTL_ITEM_CATEGORIES_INTERFACE
(
INVENTORY_ITEM_ID,
ITEM_NUMBER,
ORGANIZATION_ID,
CATEGORY_SET_ID,
CATEGORY_ID,
PROCESS_FLAG,
SET_PROCESS_ID,
TRANSACTION_TYPE
)
SELECT
MSII.INVENTORY_ITEM_ID,
MSII.ITEM_NUMBER,
MSII.ORGANIZATION_ID,
STYLE_CATS.CATEGORY_SET_ID,
STYLE_CATS.CATEGORY_ID,
1 PROCESS_FLAG,
MSII.SET_PROCESS_ID,
'CREATE'
FROM
MTL_SYSTEM_ITEMS_INTERFACE MSII,
MTL_PARAMETERS O,
MTL_ITEM_CATEGORIES STYLE_CATS
WHERE MSII.SET_PROCESS_ID = p_batch_id
AND MSII.PROCESS_FLAG = 1
AND MSII.TRANSACTION_TYPE = 'CREATE'
AND ((NVL(p_skus_only, 'Y') = 'Y' AND MSII.STYLE_ITEM_FLAG = 'N')
OR (NVL(p_skus_only, 'Y') = 'N' AND NVL(MSII.STYLE_ITEM_FLAG, 'Y') = 'Y')
)
AND MSII.ORGANIZATION_ID = O.ORGANIZATION_ID
AND O.ORGANIZATION_ID = O.MASTER_ORGANIZATION_ID
AND NOT EXISTS (SELECT 1 FROM MTL_DEFAULT_CATEGORY_SETS DCS WHERE DCS.CATEGORY_SET_ID = STYLE_CATS.CATEGORY_SET_ID)
AND STYLE_CATS.INVENTORY_ITEM_ID = MSII.COPY_ITEM_ID
AND STYLE_CATS.ORGANIZATION_ID = MSII.ORGANIZATION_ID
AND NOT EXISTS (SELECT NULL
FROM MTL_ITEM_CATEGORIES_INTERFACE MICI
WHERE MICI.SET_PROCESS_ID = MSII.SET_PROCESS_ID
AND MICI.PROCESS_FLAG = MSII.PROCESS_FLAG
AND (MICI.ITEM_NUMBER = MSII.ITEM_NUMBER OR MICI.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID)
AND MICI.ORGANIZATION_ID = MSII.ORGANIZATION_ID
AND MICI.CATEGORY_SET_ID = STYLE_CATS.CATEGORY_SET_ID
AND MICI.CATEGORY_ID = STYLE_CATS.CATEGORY_ID
);
Debug_Conc_Log('Copy_Category_Assignments: Inserted records count='||SQL%ROWCOUNT);
SELECT NVL(SELECTION_FLAG, 'N') INTO l_copy_first
FROM EGO_IMPORT_COPY_OPTIONS
WHERE BATCH_ID = p_batch_id
AND COPY_OPTION = 'COPY_FIRST';
SELECT NVL(SELECTION_FLAG, 'N') INTO l_copy_org
FROM EGO_IMPORT_COPY_OPTIONS
WHERE BATCH_ID = p_batch_id
AND COPY_OPTION = 'COPY_ORG_ASSIGNMENTS';
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
(
SET_PROCESS_ID,
PROCESS_FLAG,
TRANSACTION_TYPE,
INVENTORY_ITEM_ID,
ITEM_NUMBER,
ORGANIZATION_ID,
STYLE_ITEM_ID,
STYLE_ITEM_FLAG,
SOURCE_SYSTEM_ID,
SOURCE_SYSTEM_REFERENCE,
COPY_ITEM_ID
)
SELECT
MSII.SET_PROCESS_ID,
MSII.PROCESS_FLAG,
'CREATE',
MSII.INVENTORY_ITEM_ID,
MSII.ITEM_NUMBER,
MSI.ORGANIZATION_ID,
MSI.STYLE_ITEM_ID,
MSI.STYLE_ITEM_FLAG,
MSII.SOURCE_SYSTEM_ID,
MSII.SOURCE_SYSTEM_REFERENCE,
MSII.COPY_ITEM_ID
FROM
MTL_SYSTEM_ITEMS_INTERFACE MSII,
MTL_SYSTEM_ITEMS_B MSI,
MTL_PARAMETERS MP
WHERE MSII.SET_PROCESS_ID = p_batch_id
AND MSII.PROCESS_FLAG = 1
AND MSII.TRANSACTION_TYPE = 'CREATE'
AND MSII.COPY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP.MASTER_ORGANIZATION_ID <> MP.ORGANIZATION_ID
AND NOT EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2
WHERE MSII2.SET_PROCESS_ID = MSII.SET_PROCESS_ID
AND MSII2.PROCESS_FLAG = MSII.PROCESS_FLAG
AND (MSII2.ITEM_NUMBER = MSII.ITEM_NUMBER OR MSII2.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID)
AND MSII2.ORGANIZATION_ID = MSI.ORGANIZATION_ID
);
SELECT NVL(SELECTION_FLAG, 'N') INTO l_copy_cat
FROM EGO_IMPORT_COPY_OPTIONS
WHERE BATCH_ID = p_batch_id
--AND REQUEST_ID = l_request_id
AND COPY_OPTION = 'COPY_CAT_ASSIGNMENTS';
FOR i IN ( SELECT COPY_OPTION, NVL(SELECTION_FLAG, 'N') SELECTION_FLAG
FROM EGO_IMPORT_COPY_OPTIONS
WHERE BATCH_ID = p_batch_id
AND COPY_OPTION IN ('COPY_SUPPLIER_ASSIGNMENTS',
'COPY_SUPPLIER_SITE_ASSIGNMENTS',
'COPY_SUPPLIER_SITE_ORG_ASSIGNMENTS')
)
LOOP
IF i.COPY_OPTION = 'COPY_SUPPLIER_ASSIGNMENTS' THEN
l_copy_sup := i.SELECTION_FLAG;
l_copy_sup_site := i.SELECTION_FLAG;
l_copy_sup_site_org := i.SELECTION_FLAG;
SELECT COPY_ITEM_ID INTO l_src_item_id
FROM MTL_SYSTEM_ITEMS_INTERFACE
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 1
AND COPY_ITEM_ID IS NOT NULL
AND ROWNUM = 1;
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_LEVEL', 'ITEM_ORG', 'ITEM_SUP', 'ITEM_SUP_SITE', 'ITEM_SUP_SITE_ORG', 'ITEM_REVISION_LEVEL');
SELECT
ROWNUM AS ROW_IDENTIFIER,
msii.INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID,
msii.ORGANIZATION_ID AS ORGANIZATION_ID,
msii.SET_PROCESS_ID AS DATA_SET_ID,
msii.ITEM_NUMBER,
msii.ORGANIZATION_CODE,
ext_prod.ITEM_CATALOG_GROUP_ID,
ext_prod.ATTR_GROUP_ID,
ext_prod.DATA_LEVEL_ID,
NULL AS REVISION_ID,
NULL AS REVISION,
NULL AS PK1_VALUE,
NULL AS PK2_VALUE,
NULL AS PK3_VALUE,
NULL AS PK4_VALUE,
NULL AS PK5_VALUE,
NULL AS CHANGE_ID,
NULL AS CHANGE_LINE_ID,
msii.SOURCE_SYSTEM_ID,
msii.SOURCE_SYSTEM_REFERENCE,
msii.BUNDLE_ID,
msii.TRANSACTION_ID,
ext_prod.EXTENSION_ID, #' || G_PROD_COL_LIST || q'#
FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
EGO_MTL_SY_ITEMS_EXT_VL ext_prod,
MTL_PARAMETERS mp
WHERE msii.STYLE_ITEM_FLAG = 'N'
AND msii.SET_PROCESS_ID = #' || p_batch_id || q'#
AND msii.TRANSACTION_TYPE = 'CREATE'
AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
AND msii.STYLE_ITEM_ID = ext_prod.INVENTORY_ITEM_ID
AND msii.ORGANIZATION_ID = ext_prod.ORGANIZATION_ID
AND msii.ORGANIZATION_ID = mp.ORGANIZATION_ID
AND mp.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID
AND ext_prod.DATA_LEVEL_ID = #' || l_item_dl_id || q'#
AND EXISTS (SELECT NULL
FROM EGO_ATTR_GROUP_DL eagd
WHERE eagd.ATTR_GROUP_ID = ext_prod.ATTR_GROUP_ID
AND eagd.DATA_LEVEL_ID = ext_prod.DATA_LEVEL_ID
AND NVL(eagd.DEFAULTING, 'D') = 'D'
) #';
SELECT
ext_prod.ATTR_GROUP_ID
FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
EGO_MTL_SY_ITEMS_EXT_B ext_prod,
MTL_PARAMETERS mp
WHERE msii.STYLE_ITEM_FLAG = 'N'
AND msii.SET_PROCESS_ID = #' || p_batch_id || q'#
AND msii.TRANSACTION_TYPE = 'CREATE'
AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
AND msii.STYLE_ITEM_ID = ext_prod.INVENTORY_ITEM_ID
AND msii.ORGANIZATION_ID = ext_prod.ORGANIZATION_ID
AND msii.ORGANIZATION_ID = mp.ORGANIZATION_ID
AND mp.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID
AND ext_prod.DATA_LEVEL_ID = #' || l_item_dl_id || q'#
AND EXISTS (SELECT NULL
FROM EGO_ATTR_GROUP_DL eagd
WHERE eagd.ATTR_GROUP_ID = ext_prod.ATTR_GROUP_ID
AND eagd.DATA_LEVEL_ID = ext_prod.DATA_LEVEL_ID
AND NVL(eagd.DEFAULTING, 'D') = 'D'
) #';
SELECT
ROWNUM AS ROW_IDENTIFIER,
msii.INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID,
msii.ORGANIZATION_ID AS ORGANIZATION_ID,
msii.SET_PROCESS_ID AS DATA_SET_ID,
msii.ITEM_NUMBER,
msii.ORGANIZATION_CODE,
ext_prod.ITEM_CATALOG_GROUP_ID,
ext_prod.ATTR_GROUP_ID,
ext_prod.DATA_LEVEL_ID,
(SELECT MAX(miri.REVISION_ID) KEEP (DENSE_RANK FIRST ORDER BY miri.REVISION)
FROM MTL_ITEM_REVISIONS_INTERFACE miri
WHERE miri.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
AND miri.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND miri.SET_PROCESS_ID = msii.SET_PROCESS_ID
AND miri.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
) AS REVISION_ID,
NULL AS REVISION,
NULL AS PK1_VALUE,
NULL AS PK2_VALUE,
NULL AS PK3_VALUE,
NULL AS PK4_VALUE,
NULL AS PK5_VALUE,
NULL AS CHANGE_ID,
NULL AS CHANGE_LINE_ID,
msii.SOURCE_SYSTEM_ID,
msii.SOURCE_SYSTEM_REFERENCE,
msii.BUNDLE_ID,
msii.TRANSACTION_ID,
ext_prod.EXTENSION_ID, #' || G_PROD_COL_LIST || q'#
FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
EGO_MTL_SY_ITEMS_EXT_VL ext_prod,
MTL_PARAMETERS mp
WHERE msii.STYLE_ITEM_FLAG = 'N'
AND msii.SET_PROCESS_ID = #' || p_batch_id || q'#
AND msii.TRANSACTION_TYPE = 'CREATE'
AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
AND msii.STYLE_ITEM_ID = ext_prod.INVENTORY_ITEM_ID
AND msii.ORGANIZATION_ID = ext_prod.ORGANIZATION_ID
AND msii.ORGANIZATION_ID = mp.ORGANIZATION_ID
AND mp.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID
AND ext_prod.DATA_LEVEL_ID = #' || l_item_rev_dl_id || q'#
AND ext_prod.REVISION_ID = (SELECT Max(revision_id) FROM
(SELECT revision_id, INVENTORY_ITEM_ID, ORGANIZATION_ID, dense_rank() over (PARTITION BY INVENTORY_ITEM_ID, ORGANIZATION_ID ORDER BY EFFECTIVITY_DATE DESC ) dr
FROM MTL_ITEM_REVISIONS_B mirb1
WHERE mirb1.EFFECTIVITY_DATE <= SYSDATE )
WHERE INVENTORY_ITEM_ID = ext_prod.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = ext_prod.ORGANIZATION_ID
AND dr =1
)
AND EXISTS (SELECT NULL
FROM EGO_ATTR_GROUP_DL eagd
WHERE eagd.ATTR_GROUP_ID = ext_prod.ATTR_GROUP_ID
AND eagd.DATA_LEVEL_ID = ext_prod.DATA_LEVEL_ID
AND NVL(eagd.DEFAULTING, 'D') = 'D'
) #';
SELECT
ext_prod.ATTR_GROUP_ID
FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
EGO_MTL_SY_ITEMS_EXT_B ext_prod,
MTL_PARAMETERS mp
WHERE msii.STYLE_ITEM_FLAG = 'N'
AND msii.SET_PROCESS_ID = #' || p_batch_id || q'#
AND msii.TRANSACTION_TYPE = 'CREATE'
AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
AND msii.STYLE_ITEM_ID = ext_prod.INVENTORY_ITEM_ID
AND msii.ORGANIZATION_ID = ext_prod.ORGANIZATION_ID
AND msii.ORGANIZATION_ID = mp.ORGANIZATION_ID
AND mp.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID
AND ext_prod.DATA_LEVEL_ID = #' || l_item_rev_dl_id || q'#
AND ext_prod.REVISION_ID = (SELECT Max(revision_id) FROM
(SELECT revision_id, INVENTORY_ITEM_ID, ORGANIZATION_ID, dense_rank() over (PARTITION BY INVENTORY_ITEM_ID, ORGANIZATION_ID ORDER BY EFFECTIVITY_DATE DESC ) dr
FROM MTL_ITEM_REVISIONS_B mirb1
WHERE mirb1.EFFECTIVITY_DATE <= SYSDATE )
WHERE INVENTORY_ITEM_ID = ext_prod.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = ext_prod.ORGANIZATION_ID
AND dr =1
)
AND EXISTS (SELECT NULL
FROM EGO_ATTR_GROUP_DL eagd
WHERE eagd.ATTR_GROUP_ID = ext_prod.ATTR_GROUP_ID
AND eagd.DATA_LEVEL_ID = ext_prod.DATA_LEVEL_ID
AND NVL(eagd.DEFAULTING, 'D') = 'D'
) #';
SELECT
ROWNUM AS ROW_IDENTIFIER,
miri.INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID,
miri.ORGANIZATION_ID AS ORGANIZATION_ID,
miri.SET_PROCESS_ID AS DATA_SET_ID,
miri.ITEM_NUMBER,
miri.ORGANIZATION_CODE,
ext_prod.ITEM_CATALOG_GROUP_ID,
ext_prod.ATTR_GROUP_ID,
ext_prod.DATA_LEVEL_ID,
miri.REVISION_ID,
miri.REVISION,
NULL AS PK1_VALUE,
NULL AS PK2_VALUE,
NULL AS PK3_VALUE,
NULL AS PK4_VALUE,
NULL AS PK5_VALUE,
NULL AS CHANGE_ID,
NULL AS CHANGE_LINE_ID,
miri.SOURCE_SYSTEM_ID,
miri.SOURCE_SYSTEM_REFERENCE,
NULL AS BUNDLE_ID,
miri.TRANSACTION_ID,
ext_prod.EXTENSION_ID, #' || G_PROD_COL_LIST || q'#
FROM MTL_ITEM_REVISIONS_INTERFACE miri,
EGO_MTL_SY_ITEMS_EXT_VL ext_prod
WHERE miri.SET_PROCESS_ID = #' || p_batch_id || q'#
AND miri.TRANSACTION_TYPE = 'CREATE'
AND miri.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
AND miri.INVENTORY_ITEM_ID = ext_prod.INVENTORY_ITEM_ID
AND miri.ORGANIZATION_ID = ext_prod.ORGANIZATION_ID
AND ext_prod.DATA_LEVEL_ID = #' || l_item_rev_dl_id || q'#
AND ext_prod.REVISION_ID = (SELECT Max(revision_id) FROM
(SELECT revision_id, INVENTORY_ITEM_ID, ORGANIZATION_ID, dense_rank() over (PARTITION BY INVENTORY_ITEM_ID, ORGANIZATION_ID ORDER BY EFFECTIVITY_DATE DESC ) dr
FROM MTL_ITEM_REVISIONS_B mirb1
WHERE mirb1.EFFECTIVITY_DATE <= SYSDATE )
WHERE INVENTORY_ITEM_ID = ext_prod.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = ext_prod.ORGANIZATION_ID
AND dr =1
)
AND NOT EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE msii.SET_PROCESS_ID = miri.SET_PROCESS_ID
AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
AND msii.TRANSACTION_TYPE = 'CREATE'
AND msii.INVENTORY_ITEM_ID = miri.INVENTORY_ITEM_ID
AND msii.ORGANIZATION_ID = miri.ORGANIZATION_ID
) #';
SELECT
ext_prod.ATTR_GROUP_ID
FROM MTL_ITEM_REVISIONS_INTERFACE miri,
EGO_MTL_SY_ITEMS_EXT_B ext_prod
WHERE miri.SET_PROCESS_ID = #' || p_batch_id || q'#
AND miri.TRANSACTION_TYPE = 'CREATE'
AND miri.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
AND miri.INVENTORY_ITEM_ID = ext_prod.INVENTORY_ITEM_ID
AND miri.ORGANIZATION_ID = ext_prod.ORGANIZATION_ID
AND ext_prod.DATA_LEVEL_ID = #' || l_item_rev_dl_id || q'#
AND ext_prod.REVISION_ID = (SELECT Max(revision_id) FROM
(SELECT revision_id, INVENTORY_ITEM_ID, ORGANIZATION_ID, dense_rank() over (PARTITION BY INVENTORY_ITEM_ID, ORGANIZATION_ID ORDER BY EFFECTIVITY_DATE DESC ) dr
FROM MTL_ITEM_REVISIONS_B mirb1
WHERE mirb1.EFFECTIVITY_DATE <= SYSDATE )
WHERE INVENTORY_ITEM_ID = ext_prod.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = ext_prod.ORGANIZATION_ID
AND dr =1
)
AND NOT EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE msii.SET_PROCESS_ID = miri.SET_PROCESS_ID
AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
AND msii.TRANSACTION_TYPE = 'CREATE'
AND msii.INVENTORY_ITEM_ID = miri.INVENTORY_ITEM_ID
AND msii.ORGANIZATION_ID = miri.ORGANIZATION_ID
) #';
SELECT
ROWNUM AS ROW_IDENTIFIER,
msii.INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID,
msii.ORGANIZATION_ID AS ORGANIZATION_ID,
msii.SET_PROCESS_ID AS DATA_SET_ID,
msii.ITEM_NUMBER,
msii.ORGANIZATION_CODE,
ext_prod.ITEM_CATALOG_GROUP_ID,
ext_prod.ATTR_GROUP_ID,
ext_prod.DATA_LEVEL_ID,
NULL AS REVISION_ID,
NULL AS REVISION,
NULL AS PK1_VALUE,
NULL AS PK2_VALUE,
NULL AS PK3_VALUE,
NULL AS PK4_VALUE,
NULL AS PK5_VALUE,
NULL AS CHANGE_ID,
NULL AS CHANGE_LINE_ID,
msii.SOURCE_SYSTEM_ID,
msii.SOURCE_SYSTEM_REFERENCE,
msii.BUNDLE_ID,
msii.TRANSACTION_ID,
ext_prod.EXTENSION_ID, #' || G_PROD_COL_LIST || q'#
FROM
MTL_SYSTEM_ITEMS_INTERFACE msii,
EGO_MTL_SY_ITEMS_EXT_VL ext_prod,
MTL_PARAMETERS mp
WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
AND msii.TRANSACTION_TYPE = 'CREATE'
AND msii.ORGANIZATION_ID = mp.ORGANIZATION_ID
AND ext_prod.DATA_LEVEL_ID = #' || l_item_org_dl_id || q'#
AND ext_prod.INVENTORY_ITEM_ID = NVL(msii.STYLE_ITEM_ID,msii.INVENTORY_ITEM_ID)
AND ext_prod.ORGANIZATION_ID = NVL2(msii.STYLE_ITEM_ID,msii.ORGANIZATION_ID,mp.MASTER_ORGANIZATION_ID)
AND EXISTS (SELECT 1
FROM EGO_ATTR_GROUP_DL eagd
WHERE eagd.ATTR_GROUP_ID = ext_prod.ATTR_GROUP_ID
AND eagd.DATA_LEVEL_ID = ext_prod.DATA_LEVEL_ID
AND ( NVL(eagd.DEFAULTING, 'D') = 'D' OR msii.STYLE_ITEM_ID IS NULL )
)
AND NOT EXISTS (SELECT 1
FROM EGO_FND_DSC_FLX_CTX_EXT fl_ctx
WHERE fl_ctx.DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_ITEMMGMT_GROUP'
AND fl_ctx.DESCRIPTIVE_FLEX_CONTEXT_CODE IN ('ItemDetailImage', 'ItemDetailDesc')
AND ext_prod.ATTR_GROUP_ID = fl_ctx.ATTR_GROUP_ID
)#';
SELECT
ext_prod.ATTR_GROUP_ID
FROM
MTL_SYSTEM_ITEMS_INTERFACE msii,
EGO_MTL_SY_ITEMS_EXT_B ext_prod,
MTL_PARAMETERS mp
WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
AND msii.PROCESS_FLAG = #' || p_msii_miri_process_flag || q'#
AND msii.TRANSACTION_TYPE = 'CREATE'
AND msii.ORGANIZATION_ID = mp.ORGANIZATION_ID
AND ext_prod.DATA_LEVEL_ID = #' || l_item_org_dl_id || q'#
AND ext_prod.INVENTORY_ITEM_ID = NVL(msii.STYLE_ITEM_ID,msii.INVENTORY_ITEM_ID)
AND ext_prod.ORGANIZATION_ID = NVL2(msii.STYLE_ITEM_ID,msii.ORGANIZATION_ID,mp.MASTER_ORGANIZATION_ID)
AND EXISTS (SELECT 1
FROM EGO_ATTR_GROUP_DL eagd
WHERE eagd.ATTR_GROUP_ID = ext_prod.ATTR_GROUP_ID
AND eagd.DATA_LEVEL_ID = ext_prod.DATA_LEVEL_ID
AND ( NVL(eagd.DEFAULTING, 'D') = 'D' OR msii.STYLE_ITEM_ID IS NULL )
)
AND NOT EXISTS (SELECT 1
FROM EGO_FND_DSC_FLX_CTX_EXT fl_ctx
WHERE fl_ctx.DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_ITEMMGMT_GROUP'
AND fl_ctx.DESCRIPTIVE_FLEX_CONTEXT_CODE IN ('ItemDetailImage', 'ItemDetailDesc')
AND ext_prod.ATTR_GROUP_ID = fl_ctx.ATTR_GROUP_ID
)#';
UPDATE EGO_ITEM_ASSOCIATIONS_INTF eiai
SET (STYLE_ITEM_FLAG, STYLE_ITEM_ID) = (SELECT
msi.STYLE_ITEM_FLAG,
msi.STYLE_ITEM_ID
FROM MTL_SYSTEM_ITEMS_B msi, MTL_PARAMETERS mp
WHERE msi.INVENTORY_ITEM_ID = eiai.INVENTORY_ITEM_ID
AND eiai.ORGANIZATION_ID = mp.ORGANIZATION_ID
AND msi.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID
)
WHERE eiai.BATCH_ID = p_batch_id
AND eiai.PROCESS_FLAG = 1
AND eiai.TRANSACTION_TYPE = 'CREATE'
AND EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_B msi2, MTL_PARAMETERS mp2
WHERE msi2.INVENTORY_ITEM_ID = eiai.INVENTORY_ITEM_ID
AND eiai.ORGANIZATION_ID = mp2.ORGANIZATION_ID
AND msi2.ORGANIZATION_ID = mp2.MASTER_ORGANIZATION_ID
);
UPDATE EGO_ITEM_ASSOCIATIONS_INTF eiai
SET (STYLE_ITEM_FLAG, STYLE_ITEM_ID) = (SELECT msii.STYLE_ITEM_FLAG , msii.STYLE_ITEM_ID
FROM MTL_SYSTEM_ITEMS_INTERFACE msii, MTL_PARAMETERS mp
WHERE msii.INVENTORY_ITEM_ID = eiai.INVENTORY_ITEM_ID
AND eiai.ORGANIZATION_ID = mp.ORGANIZATION_ID
AND msii.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID
AND msii.SET_PROCESS_ID = eiai.BATCH_ID
AND msii.PROCESS_FLAG = p_msii_miri_process_flag
AND ROWNUM = 1)
WHERE eiai.BATCH_ID = p_batch_id
AND eiai.PROCESS_FLAG = 1
AND eiai.TRANSACTION_TYPE = 'CREATE'
AND EXISTS (SELECT 1
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2, MTL_PARAMETERS mp2
WHERE MSII2.INVENTORY_ITEM_ID = eiai.INVENTORY_ITEM_ID
AND MSII2.ORGANIZATION_ID = mp2.MASTER_ORGANIZATION_ID
AND eiai.ORGANIZATION_ID = mp2.ORGANIZATION_ID
AND MSII2.SET_PROCESS_ID = eiai.BATCH_ID
AND MSII2.PROCESS_FLAG = p_msii_miri_process_flag );
Debug_Conc_Log('Default_User_Attrs_From_Prod: Updated Style_item_id, Style_item_flag in EGO_ITEM_ASSOCIATIONS_INTF');
SELECT
ROWNUM AS ROW_IDENTIFIER,
eiai.INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID,
eiai.ORGANIZATION_ID AS ORGANIZATION_ID,
eiai.BATCH_ID AS DATA_SET_ID,
eiai.ITEM_NUMBER,
eiai.ORGANIZATION_CODE,
ext_prod.ITEM_CATALOG_GROUP_ID,
ext_prod.ATTR_GROUP_ID,
eiai.DATA_LEVEL_ID,
NULL AS REVISION_ID,
NULL AS REVISION,
eiai.PK1_VALUE,
eiai.PK2_VALUE,
eiai.PK3_VALUE,
eiai.PK4_VALUE,
eiai.PK5_VALUE,
NULL AS CHANGE_ID,
NULL AS CHANGE_LINE_ID,
eiai.SOURCE_SYSTEM_ID,
eiai.SOURCE_SYSTEM_REFERENCE,
eiai.BUNDLE_ID,
eiai.TRANSACTION_ID,
ext_prod.EXTENSION_ID, #' || G_PROD_COL_LIST || q'#
FROM
EGO_ITEM_ASSOCIATIONS_INTF eiai,
EGO_MTL_SY_ITEMS_EXT_VL ext_prod
WHERE eiai.BATCH_ID = #' || p_batch_id || q'#
AND eiai.TRANSACTION_TYPE = 'CREATE'
AND eiai.DATA_LEVEL_ID = #' || l_item_sup_dl_id || q'#
AND eiai.PROCESS_FLAG = 1
AND eiai.ORGANIZATION_ID = ext_prod.ORGANIZATION_ID
AND (ext_prod.INVENTORY_ITEM_ID, ext_prod.DATA_LEVEL_ID, NVL(ext_prod.PK1_VALUE, -99)) IN
(SELECT
Nvl(MAX(eia.INVENTORY_ITEM_ID), eiai.INVENTORY_ITEM_ID),
NVL(MAX(eia.DATA_LEVEL_ID), #' || l_item_dl_id || q'#),
NVL(MAX(eia.PK1_VALUE), -99)
FROM EGO_ITEM_ASSOCIATIONS eia
WHERE eia.INVENTORY_ITEM_ID = eiai.STYLE_ITEM_ID
AND eia.ORGANIZATION_ID = eiai.ORGANIZATION_ID
AND eia.DATA_LEVEL_ID = #' || l_item_sup_dl_id || q'#
AND eia.PK1_VALUE = eiai.PK1_VALUE
)
AND EXISTS (SELECT 1
FROM EGO_ATTR_GROUP_DL eagd
WHERE eagd.ATTR_GROUP_ID = ext_prod.ATTR_GROUP_ID
AND eagd.DATA_LEVEL_ID = eiai.DATA_LEVEL_ID
AND ( NVL(eagd.DEFAULTING, 'D') = 'D' OR eiai.STYLE_ITEM_ID IS NULL )
) #';
SELECT
ext_prod.ATTR_GROUP_ID
FROM
EGO_ITEM_ASSOCIATIONS_INTF eiai,
EGO_MTL_SY_ITEMS_EXT_B ext_prod
WHERE eiai.BATCH_ID = #' || p_batch_id || q'#
AND eiai.TRANSACTION_TYPE = 'CREATE'
AND eiai.DATA_LEVEL_ID = #' || l_item_sup_dl_id || q'#
AND eiai.PROCESS_FLAG = 1
AND eiai.ORGANIZATION_ID = ext_prod.ORGANIZATION_ID
AND (ext_prod.INVENTORY_ITEM_ID, ext_prod.DATA_LEVEL_ID, NVL(ext_prod.PK1_VALUE, -99)) IN
(SELECT
Nvl(MAX(eia.INVENTORY_ITEM_ID), eiai.INVENTORY_ITEM_ID),
NVL(MAX(eia.DATA_LEVEL_ID), #' || l_item_dl_id || q'#),
NVL(MAX(eia.PK1_VALUE), -99)
FROM EGO_ITEM_ASSOCIATIONS eia
WHERE eia.INVENTORY_ITEM_ID = eiai.STYLE_ITEM_ID
AND eia.ORGANIZATION_ID = eiai.ORGANIZATION_ID
AND eia.DATA_LEVEL_ID = #' || l_item_sup_dl_id || q'#
AND eia.PK1_VALUE = eiai.PK1_VALUE
)
AND EXISTS (SELECT 1
FROM EGO_ATTR_GROUP_DL eagd
WHERE eagd.ATTR_GROUP_ID = ext_prod.ATTR_GROUP_ID
AND eagd.DATA_LEVEL_ID = eiai.DATA_LEVEL_ID
AND ( NVL(eagd.DEFAULTING, 'D') = 'D' OR eiai.STYLE_ITEM_ID IS NULL )
) #';
SELECT
ROWNUM AS ROW_IDENTIFIER,
eiai.INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID,
eiai.ORGANIZATION_ID AS ORGANIZATION_ID,
eiai.BATCH_ID AS DATA_SET_ID,
eiai.ITEM_NUMBER,
eiai.ORGANIZATION_CODE,
ext_prod.ITEM_CATALOG_GROUP_ID,
ext_prod.ATTR_GROUP_ID,
eiai.DATA_LEVEL_ID,
NULL AS REVISION_ID,
NULL AS REVISION,
eiai.PK1_VALUE,
eiai.PK2_VALUE,
eiai.PK3_VALUE,
eiai.PK4_VALUE,
eiai.PK5_VALUE,
NULL AS CHANGE_ID,
NULL AS CHANGE_LINE_ID,
eiai.SOURCE_SYSTEM_ID,
eiai.SOURCE_SYSTEM_REFERENCE,
eiai.BUNDLE_ID,
eiai.TRANSACTION_ID,
ext_prod.EXTENSION_ID, #' || G_PROD_COL_LIST || q'#
FROM
EGO_ITEM_ASSOCIATIONS_INTF eiai,
EGO_MTL_SY_ITEMS_EXT_VL ext_prod
WHERE eiai.BATCH_ID = #' || p_batch_id || q'#
AND eiai.TRANSACTION_TYPE = 'CREATE'
AND eiai.DATA_LEVEL_ID = #' || l_item_sup_site_dl_id || q'#
AND eiai.PROCESS_FLAG = 1
AND eiai.ORGANIZATION_ID = ext_prod.ORGANIZATION_ID
AND (ext_prod.INVENTORY_ITEM_ID,
ext_prod.DATA_LEVEL_ID,
ext_prod.PK1_VALUE,
NVL(ext_prod.PK2_VALUE, -99)) IN
(SELECT
NVL(MAX(eia.INVENTORY_ITEM_ID), eiai.INVENTORY_ITEM_ID),
NVL(MAX(eia.DATA_LEVEL_ID), #' || l_item_sup_dl_id || q'#),
NVL(MAX(eia.PK1_VALUE), eiai.PK1_VALUE),
NVL(MAX(eia.PK2_VALUE), -99)
FROM EGO_ITEM_ASSOCIATIONS eia
WHERE eia.INVENTORY_ITEM_ID = eiai.STYLE_ITEM_ID
AND eia.ORGANIZATION_ID = eiai.ORGANIZATION_ID
AND eia.DATA_LEVEL_ID = #' || l_item_sup_site_dl_id || q'#
AND eia.PK1_VALUE = eiai.PK1_VALUE
AND eia.PK2_VALUE = eiai.PK2_VALUE
)
AND EXISTS (SELECT 1
FROM EGO_ATTR_GROUP_DL eagd
WHERE eagd.ATTR_GROUP_ID = ext_prod.ATTR_GROUP_ID
AND eagd.DATA_LEVEL_ID = eiai.DATA_LEVEL_ID
AND ( NVL(eagd.DEFAULTING, 'D') = 'D' OR eiai.STYLE_ITEM_ID IS NULL )
) #';
SELECT
ext_prod.ATTR_GROUP_ID
FROM
EGO_ITEM_ASSOCIATIONS_INTF eiai,
EGO_MTL_SY_ITEMS_EXT_B ext_prod
WHERE eiai.BATCH_ID = #' || p_batch_id || q'#
AND eiai.TRANSACTION_TYPE = 'CREATE'
AND eiai.DATA_LEVEL_ID = #' || l_item_sup_site_dl_id || q'#
AND eiai.PROCESS_FLAG = 1
AND eiai.ORGANIZATION_ID = ext_prod.ORGANIZATION_ID
AND (ext_prod.INVENTORY_ITEM_ID,
ext_prod.DATA_LEVEL_ID,
ext_prod.PK1_VALUE,
NVL(ext_prod.PK2_VALUE, -99)) IN
(SELECT
NVL(MAX(eia.INVENTORY_ITEM_ID), eiai.INVENTORY_ITEM_ID),
NVL(MAX(eia.DATA_LEVEL_ID), #' || l_item_sup_dl_id || q'#),
NVL(MAX(eia.PK1_VALUE), eiai.PK1_VALUE),
NVL(MAX(eia.PK2_VALUE), -99)
FROM EGO_ITEM_ASSOCIATIONS eia
WHERE eia.INVENTORY_ITEM_ID = eiai.STYLE_ITEM_ID
AND eia.ORGANIZATION_ID = eiai.ORGANIZATION_ID
AND eia.DATA_LEVEL_ID = #' || l_item_sup_site_dl_id || q'#
AND eia.PK1_VALUE = eiai.PK1_VALUE
AND eia.PK2_VALUE = eiai.PK2_VALUE
)
AND EXISTS (SELECT 1
FROM EGO_ATTR_GROUP_DL eagd
WHERE eagd.ATTR_GROUP_ID = ext_prod.ATTR_GROUP_ID
AND eagd.DATA_LEVEL_ID = eiai.DATA_LEVEL_ID
AND ( NVL(eagd.DEFAULTING, 'D') = 'D' OR eiai.STYLE_ITEM_ID IS NULL )
) #';
SELECT
ROWNUM AS ROW_IDENTIFIER,
eiai.INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID,
eiai.ORGANIZATION_ID AS ORGANIZATION_ID,
eiai.BATCH_ID AS DATA_SET_ID,
eiai.ITEM_NUMBER,
eiai.ORGANIZATION_CODE,
ext_prod.ITEM_CATALOG_GROUP_ID,
ext_prod.ATTR_GROUP_ID,
eiai.DATA_LEVEL_ID,
NULL AS REVISION_ID,
NULL AS REVISION,
eiai.PK1_VALUE,
eiai.PK2_VALUE,
eiai.PK3_VALUE,
eiai.PK4_VALUE,
eiai.PK5_VALUE,
NULL AS CHANGE_ID,
NULL AS CHANGE_LINE_ID,
eiai.SOURCE_SYSTEM_ID,
eiai.SOURCE_SYSTEM_REFERENCE,
eiai.BUNDLE_ID,
eiai.TRANSACTION_ID,
ext_prod.EXTENSION_ID, #' || G_PROD_COL_LIST || q'#
FROM
EGO_ITEM_ASSOCIATIONS_INTF eiai,
EGO_MTL_SY_ITEMS_EXT_VL ext_prod,
MTL_PARAMETERS mp
WHERE eiai.BATCH_ID = #' || p_batch_id || q'#
AND eiai.TRANSACTION_TYPE = 'CREATE'
AND eiai.DATA_LEVEL_ID = #' || l_item_sup_site_org_dl_id || q'#
AND eiai.PROCESS_FLAG = 1
AND ext_prod.INVENTORY_ITEM_ID = NVL(eiai.STYLE_ITEM_ID, eiai.INVENTORY_ITEM_ID)
AND eiai.ORGANIZATION_ID = mp.ORGANIZATION_ID
AND (ext_prod.INVENTORY_ITEM_ID,
ext_prod.DATA_LEVEL_ID,
ext_prod.PK1_VALUE,
ext_prod.PK2_VALUE,
ext_prod.ORGANIZATION_ID) IN
(SELECT
NVL(MAX(eia.INVENTORY_ITEM_ID), eiai.INVENTORY_ITEM_ID),
NVL(MAX(eia.DATA_LEVEL_ID), #' || l_item_sup_site_dl_id || q'#),
NVL(MAX(eia.PK1_VALUE), eiai.PK1_VALUE),
NVL(MAX(eia.PK2_VALUE), eiai.PK2_VALUE),
DECODE(MAX(eia.DATA_LEVEL_ID), NULL, mp.MASTER_ORGANIZATION_ID, eiai.ORGANIZATION_ID)
FROM EGO_ITEM_ASSOCIATIONS eia
WHERE eia.INVENTORY_ITEM_ID = eiai.STYLE_ITEM_ID
AND eia.ORGANIZATION_ID = eiai.ORGANIZATION_ID
AND eia.DATA_LEVEL_ID = #' || l_item_sup_site_org_dl_id || q'#
AND eia.PK1_VALUE = eiai.PK1_VALUE
AND eia.PK2_VALUE = eiai.PK2_VALUE
)
AND EXISTS (SELECT 1
FROM EGO_ATTR_GROUP_DL eagd
WHERE eagd.ATTR_GROUP_ID = ext_prod.ATTR_GROUP_ID
AND eagd.DATA_LEVEL_ID = eiai.DATA_LEVEL_ID
AND ( NVL(eagd.DEFAULTING, 'D') = 'D' OR eiai.STYLE_ITEM_ID IS NULL )
) #';
SELECT
ext_prod.ATTR_GROUP_ID
FROM
EGO_ITEM_ASSOCIATIONS_INTF eiai,
EGO_MTL_SY_ITEMS_EXT_B ext_prod,
MTL_PARAMETERS mp
WHERE eiai.BATCH_ID = #' || p_batch_id || q'#
AND eiai.TRANSACTION_TYPE = 'CREATE'
AND eiai.DATA_LEVEL_ID = #' || l_item_sup_site_org_dl_id || q'#
AND eiai.PROCESS_FLAG = 1
AND ext_prod.INVENTORY_ITEM_ID = NVL(eiai.STYLE_ITEM_ID, eiai.INVENTORY_ITEM_ID)
AND eiai.ORGANIZATION_ID = mp.ORGANIZATION_ID
AND (ext_prod.INVENTORY_ITEM_ID,
ext_prod.DATA_LEVEL_ID,
ext_prod.PK1_VALUE,
ext_prod.PK2_VALUE,
ext_prod.ORGANIZATION_ID) IN
(SELECT
NVL(MAX(eia.INVENTORY_ITEM_ID), eiai.INVENTORY_ITEM_ID),
NVL(MAX(eia.DATA_LEVEL_ID), #' || l_item_sup_site_dl_id || q'#),
NVL(MAX(eia.PK1_VALUE), eiai.PK1_VALUE),
NVL(MAX(eia.PK2_VALUE), eiai.PK2_VALUE),
DECODE(MAX(eia.DATA_LEVEL_ID), NULL, mp.MASTER_ORGANIZATION_ID, eiai.ORGANIZATION_ID)
FROM EGO_ITEM_ASSOCIATIONS eia
WHERE eia.INVENTORY_ITEM_ID = eiai.STYLE_ITEM_ID
AND eia.ORGANIZATION_ID = eiai.ORGANIZATION_ID
AND eia.DATA_LEVEL_ID = #' || l_item_sup_site_org_dl_id || q'#
AND eia.PK1_VALUE = eiai.PK1_VALUE
AND eia.PK2_VALUE = eiai.PK2_VALUE
)
AND EXISTS (SELECT 1
FROM EGO_ATTR_GROUP_DL eagd
WHERE eagd.ATTR_GROUP_ID = ext_prod.ATTR_GROUP_ID
AND eagd.DATA_LEVEL_ID = eiai.DATA_LEVEL_ID
AND ( NVL(eagd.DEFAULTING, 'D') = 'D' OR eiai.STYLE_ITEM_ID IS NULL )
) #';
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_LEVEL', 'ITEM_ORG', 'ITEM_SUP', 'ITEM_SUP_SITE', 'ITEM_SUP_SITE_ORG');
SELECT /*+ leading(msii) use_nl_with_index(ext_intf, EGO_ITM_USR_ATTR_INTRFC_N2) */
ext_intf.TRANSACTION_ID,
msii.SET_PROCESS_ID AS DATA_SET_ID,
msii.ORGANIZATION_ID,
msii.ORGANIZATION_CODE,
msii.INVENTORY_ITEM_ID,
msii.ITEM_NUMBER,
ext_intf.ITEM_CATALOG_GROUP_ID,
NULL AS REVISION_ID,
NULL AS REVISION,
NULL AS PK1_VALUE,
NULL AS PK2_VALUE,
NULL AS PK3_VALUE,
NULL AS PK4_VALUE,
NULL AS PK5_VALUE,
ext_intf.ROW_IDENTIFIER + msii.ORGANIZATION_ID AS ROW_IDENTIFIER,
ext_intf.ATTR_GROUP_TYPE,
ext_intf.ATTR_GROUP_INT_NAME,
ext_intf.ATTR_GROUP_ID,
ext_intf.ATTR_INT_NAME,
ext_intf.ATTR_VALUE_STR,
ext_intf.ATTR_VALUE_NUM,
ext_intf.ATTR_VALUE_DATE,
ext_intf.ATTR_VALUE_UOM,
ext_intf.CHANGE_ID,
ext_intf.CHANGE_LINE_ID,
ext_intf.SOURCE_SYSTEM_ID,
ext_intf.SOURCE_SYSTEM_REFERENCE,
ext_intf.BUNDLE_ID,
ext_intf.DATA_LEVEL_ID
FROM
MTL_SYSTEM_ITEMS_INTERFACE msii,
EGO_ITM_USR_ATTR_INTRFC ext_intf,
MTL_PARAMETERS mp,
EGO_FND_DSC_FLX_CTX_EXT ag_ext
WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
AND msii.PROCESS_FLAG = 1
AND msii.TRANSACTION_TYPE = 'CREATE'
AND msii.STYLE_ITEM_ID IS NULL
AND EXISTS (SELECT /*+ push_subq */ 1 FROM MTL_PARAMETERS mp1
WHERE mp1.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND mp1.ORGANIZATION_ID <> mp1.MASTER_ORGANIZATION_ID
)
AND msii.ORGANIZATION_ID = mp.ORGANIZATION_ID
AND msii.SET_PROCESS_ID = ext_intf.DATA_SET_ID
AND ext_intf.PROCESS_STATUS in (1,2) /* Bug 9923555 */
AND ext_intf.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
AND ext_intf.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID
AND ag_ext.DESCRIPTIVE_FLEXFIELD_NAME = NVL(ext_intf.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
AND ag_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE = ext_intf.ATTR_GROUP_INT_NAME
AND ag_ext.APPLICATION_ID = 431
AND NVL(ag_ext.VARIANT, 'N') = 'N'
AND ext_intf.DATA_LEVEL_ID = #' || l_item_org_dl_id;
l_ag_sql := 'SELECT NULL AS ATTR_GROUP_ID FROM DUAL WHERE 1 = 2';
SELECT
ext_intf.TRANSACTION_ID,
eiai.BATCH_ID AS DATA_SET_ID,
eiai.ORGANIZATION_ID,
eiai.ORGANIZATION_CODE,
eiai.INVENTORY_ITEM_ID,
eiai.ITEM_NUMBER,
ext_intf.ITEM_CATALOG_GROUP_ID,
NULL AS REVISION_ID,
NULL AS REVISION,
eiai.PK1_VALUE,
eiai.PK2_VALUE,
eiai.PK3_VALUE,
eiai.PK4_VALUE,
eiai.PK5_VALUE,
ext_intf.ROW_IDENTIFIER + eiai.PK1_VALUE AS ROW_IDENTIFIER,
ext_intf.ATTR_GROUP_TYPE,
ext_intf.ATTR_GROUP_INT_NAME,
ext_intf.ATTR_GROUP_ID,
ext_intf.ATTR_INT_NAME,
ext_intf.ATTR_VALUE_STR,
ext_intf.ATTR_VALUE_NUM,
ext_intf.ATTR_VALUE_DATE,
ext_intf.ATTR_VALUE_UOM,
ext_intf.CHANGE_ID,
ext_intf.CHANGE_LINE_ID,
ext_intf.SOURCE_SYSTEM_ID,
ext_intf.SOURCE_SYSTEM_REFERENCE,
ext_intf.BUNDLE_ID,
eiai.DATA_LEVEL_ID
FROM
EGO_ITEM_ASSOCIATIONS_INTF eiai,
EGO_ITM_USR_ATTR_INTRFC ext_intf,
EGO_FND_DSC_FLX_CTX_EXT ag_ext
WHERE eiai.BATCH_ID = #' || p_batch_id || q'#
AND eiai.TRANSACTION_TYPE = 'CREATE'
AND eiai.DATA_LEVEL_ID = #' || l_item_sup_dl_id || q'#
AND eiai.PROCESS_FLAG = 1
AND eiai.BATCH_ID = ext_intf.DATA_SET_ID
AND ext_intf.PROCESS_STATUS in (1,2) /* Bug 9923555 */
AND eiai.INVENTORY_ITEM_ID = ext_intf.INVENTORY_ITEM_ID
AND eiai.ORGANIZATION_ID = ext_intf.ORGANIZATION_ID
AND ag_ext.DESCRIPTIVE_FLEXFIELD_NAME = NVL(ext_intf.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
AND ag_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE = ext_intf.ATTR_GROUP_INT_NAME
AND ag_ext.APPLICATION_ID = 431
AND NVL(ag_ext.VARIANT, 'N') = 'N'
AND ext_intf.DATA_LEVEL_ID = #' || l_item_dl_id || q'#
AND EXISTS (SELECT NULL
FROM EGO_ATTR_GROUP_DL eagd
WHERE eagd.ATTR_GROUP_ID = ag_ext.ATTR_GROUP_ID
AND eagd.DATA_LEVEL_ID = #' || l_item_sup_dl_id || q'#
AND (NVL(eagd.DEFAULTING, 'D') = 'D' OR eiai.STYLE_ITEM_ID IS NULL)
)
AND NOT EXISTS (SELECT NULL
FROM EGO_ITEM_ASSOCIATIONS eia
WHERE eia.INVENTORY_ITEM_ID = eiai.STYLE_ITEM_ID
AND eia.ORGANIZATION_ID = eiai.ORGANIZATION_ID
AND eia.DATA_LEVEL_ID = #' || l_item_sup_dl_id || q'#
AND eia.PK1_VALUE = eiai.PK1_VALUE
)#';
l_ag_sql := 'SELECT NULL AS ATTR_GROUP_ID FROM DUAL WHERE 1 = 2';
SELECT
ext_intf.TRANSACTION_ID,
eiai.BATCH_ID AS DATA_SET_ID,
eiai.ORGANIZATION_ID,
eiai.ORGANIZATION_CODE,
eiai.INVENTORY_ITEM_ID,
eiai.ITEM_NUMBER,
ext_intf.ITEM_CATALOG_GROUP_ID,
NULL AS REVISION_ID,
NULL AS REVISION,
eiai.PK1_VALUE,
eiai.PK2_VALUE,
eiai.PK3_VALUE,
eiai.PK4_VALUE,
eiai.PK5_VALUE,
ext_intf.ROW_IDENTIFIER + eiai.PK2_VALUE AS ROW_IDENTIFIER,
ext_intf.ATTR_GROUP_TYPE,
ext_intf.ATTR_GROUP_INT_NAME,
ext_intf.ATTR_GROUP_ID,
ext_intf.ATTR_INT_NAME,
ext_intf.ATTR_VALUE_STR,
ext_intf.ATTR_VALUE_NUM,
ext_intf.ATTR_VALUE_DATE,
ext_intf.ATTR_VALUE_UOM,
ext_intf.CHANGE_ID,
ext_intf.CHANGE_LINE_ID,
ext_intf.SOURCE_SYSTEM_ID,
ext_intf.SOURCE_SYSTEM_REFERENCE,
ext_intf.BUNDLE_ID,
eiai.DATA_LEVEL_ID
FROM
EGO_ITEM_ASSOCIATIONS_INTF eiai,
EGO_ITM_USR_ATTR_INTRFC ext_intf,
EGO_FND_DSC_FLX_CTX_EXT ag_ext
WHERE eiai.BATCH_ID = #' || p_batch_id || q'#
AND eiai.TRANSACTION_TYPE = 'CREATE'
AND eiai.DATA_LEVEL_ID = #' || l_item_sup_site_dl_id || q'#
AND eiai.PROCESS_FLAG = 1
AND eiai.BATCH_ID = ext_intf.DATA_SET_ID
AND ext_intf.PROCESS_STATUS in (1,2) /* Bug 9923555 */
AND eiai.INVENTORY_ITEM_ID = ext_intf.INVENTORY_ITEM_ID
AND eiai.ORGANIZATION_ID = ext_intf.ORGANIZATION_ID
AND ext_intf.DATA_LEVEL_ID = #' || l_item_sup_dl_id || q'#
AND ext_intf.PK1_VALUE = eiai.PK1_VALUE
AND ext_intf.PK2_VALUE IS NULL
AND ag_ext.DESCRIPTIVE_FLEXFIELD_NAME = NVL(ext_intf.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
AND ag_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE = ext_intf.ATTR_GROUP_INT_NAME
AND ag_ext.APPLICATION_ID = 431
AND NVL(ag_ext.VARIANT, 'N') = 'N'
AND EXISTS (SELECT NULL
FROM EGO_ATTR_GROUP_DL eagd
WHERE eagd.ATTR_GROUP_ID = ag_ext.ATTR_GROUP_ID
AND eagd.DATA_LEVEL_ID = #' || l_item_sup_site_dl_id || q'#
AND (NVL(eagd.DEFAULTING, 'D') = 'D' OR eiai.STYLE_ITEM_ID IS NULL)
)
AND NOT EXISTS (SELECT NULL
FROM EGO_ITEM_ASSOCIATIONS eia
WHERE eia.INVENTORY_ITEM_ID = eiai.STYLE_ITEM_ID
AND eia.ORGANIZATION_ID = eiai.ORGANIZATION_ID
AND eia.DATA_LEVEL_ID = #' || l_item_sup_site_dl_id || q'#
AND eia.PK1_VALUE = eiai.PK1_VALUE
AND eia.PK2_VALUE = eiai.PK2_VALUE
)#';
l_ag_sql := 'SELECT NULL AS ATTR_GROUP_ID FROM DUAL WHERE 1 = 2';
SELECT
ext_intf.TRANSACTION_ID,
eiai.BATCH_ID AS DATA_SET_ID,
eiai.ORGANIZATION_ID,
eiai.ORGANIZATION_CODE,
eiai.INVENTORY_ITEM_ID,
eiai.ITEM_NUMBER,
ext_intf.ITEM_CATALOG_GROUP_ID,
NULL AS REVISION_ID,
NULL AS REVISION,
eiai.PK1_VALUE,
eiai.PK2_VALUE,
eiai.PK3_VALUE,
eiai.PK4_VALUE,
eiai.PK5_VALUE,
ext_intf.ROW_IDENTIFIER + eiai.PK2_VALUE + eiai.ORGANIZATION_ID AS ROW_IDENTIFIER,
ext_intf.ATTR_GROUP_TYPE,
ext_intf.ATTR_GROUP_INT_NAME,
ext_intf.ATTR_GROUP_ID,
ext_intf.ATTR_INT_NAME,
ext_intf.ATTR_VALUE_STR,
ext_intf.ATTR_VALUE_NUM,
ext_intf.ATTR_VALUE_DATE,
ext_intf.ATTR_VALUE_UOM,
ext_intf.CHANGE_ID,
ext_intf.CHANGE_LINE_ID,
ext_intf.SOURCE_SYSTEM_ID,
ext_intf.SOURCE_SYSTEM_REFERENCE,
ext_intf.BUNDLE_ID,
eiai.DATA_LEVEL_ID
FROM
EGO_ITEM_ASSOCIATIONS_INTF eiai,
EGO_ITM_USR_ATTR_INTRFC ext_intf,
MTL_PARAMETERS mp,
EGO_FND_DSC_FLX_CTX_EXT ag_ext
WHERE eiai.BATCH_ID = #' || p_batch_id || q'#
AND eiai.TRANSACTION_TYPE = 'CREATE'
AND eiai.DATA_LEVEL_ID = #' || l_item_sup_site_org_dl_id || q'#
AND eiai.PROCESS_FLAG = 1
AND eiai.BATCH_ID = ext_intf.DATA_SET_ID
AND ext_intf.PROCESS_STATUS in (1,2) /* Bug 9923555 */
AND eiai.INVENTORY_ITEM_ID = ext_intf.INVENTORY_ITEM_ID
AND eiai.ORGANIZATION_ID = mp.ORGANIZATION_ID
AND mp.MASTER_ORGANIZATION_ID = ext_intf.ORGANIZATION_ID
AND ext_intf.DATA_LEVEL_ID = #' || l_item_sup_site_dl_id || q'#
AND ext_intf.PK1_VALUE = eiai.PK1_VALUE
AND ext_intf.PK2_VALUE = eiai.PK2_VALUE
AND ag_ext.DESCRIPTIVE_FLEXFIELD_NAME = NVL(ext_intf.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
AND ag_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE = ext_intf.ATTR_GROUP_INT_NAME
AND ag_ext.APPLICATION_ID = 431
AND NVL(ag_ext.VARIANT, 'N') = 'N'
AND EXISTS (SELECT NULL
FROM EGO_ATTR_GROUP_DL eagd
WHERE eagd.ATTR_GROUP_ID = ag_ext.ATTR_GROUP_ID
AND eagd.DATA_LEVEL_ID = #' || l_item_sup_site_org_dl_id || q'#
AND (NVL(eagd.DEFAULTING, 'D') = 'D' OR eiai.STYLE_ITEM_ID IS NULL)
)
AND NOT EXISTS (SELECT NULL
FROM EGO_ITEM_ASSOCIATIONS eia
WHERE eia.INVENTORY_ITEM_ID = eiai.STYLE_ITEM_ID
AND eia.ORGANIZATION_ID = eiai.ORGANIZATION_ID
AND eia.DATA_LEVEL_ID = #' || l_item_sup_site_org_dl_id || q'#
AND eia.PK1_VALUE = eiai.PK1_VALUE
AND eia.PK2_VALUE = eiai.PK2_VALUE
)#';
l_ag_sql := 'SELECT NULL AS ATTR_GROUP_ID FROM DUAL WHERE 1 = 2';
INSERT INTO MTL_ITEM_CATEGORIES_INTERFACE
(
INVENTORY_ITEM_ID,
ITEM_NUMBER,
ORGANIZATION_ID,
CATEGORY_SET_ID,
CATEGORY_ID,
PROCESS_FLAG,
SET_PROCESS_ID,
TRANSACTION_TYPE
)
SELECT
MSII.INVENTORY_ITEM_ID,
MSII.ITEM_NUMBER,
MSII.ORGANIZATION_ID,
STYLE_CATS.CATEGORY_SET_ID,
STYLE_CATS.CATEGORY_ID,
1 PROCESS_FLAG,
MSII.SET_PROCESS_ID,
'CREATE'
FROM
MTL_SYSTEM_ITEMS_INTERFACE MSII,
MTL_PARAMETERS O,
MTL_ITEM_CATEGORIES STYLE_CATS
WHERE MSII.SET_PROCESS_ID = p_batch_id
AND MSII.PROCESS_FLAG = p_msii_miri_process_flag -- Bug 12635842
AND MSII.TRANSACTION_TYPE = 'CREATE'
AND MSII.STYLE_ITEM_FLAG = 'N'
AND MSII.ORGANIZATION_ID = O.ORGANIZATION_ID
AND O.ORGANIZATION_ID = O.MASTER_ORGANIZATION_ID
AND NOT EXISTS (SELECT 1
FROM MTL_DEFAULT_CATEGORY_SETS DCS
WHERE DCS.CATEGORY_SET_ID = STYLE_CATS.CATEGORY_SET_ID
)
AND STYLE_CATS.INVENTORY_ITEM_ID = MSII.STYLE_ITEM_ID
AND STYLE_CATS.ORGANIZATION_ID = MSII.ORGANIZATION_ID;
Commenting the below update query here and plaing it before INSERT_FUN_GEN_SETUP_UDAS in the procedure Preprocess_Import
so that for all the user entered records will set PROG_INT_NUM4 = 0.
Also the fix done for the bug 9678667 is reverted.
*/
-- to identify the records that are not inserted by defaulting APIs
-- UPDATE /*+ INDEX(EGO_ITM_USR_ATTR_INTRFC, EGO_ITM_USR_ATTR_INTRFC_N3) */ /* Bug 9678667 */
/* EGO_ITM_USR_ATTR_INTRFC
SET PROG_INT_NUM4 = 0
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS in (1,2); -- Bug 9742469
SELECT TO_CHAR(LAST_MESSAGE_TIMESTAMP, G_NLS_DATE_FORMAT) INTO l_prod_timestamp
FROM EGO_INBOUND_MSG_EXT
WHERE SOURCE_SYSTEM_ID = p_item_detail_tbl(i).SOURCE_SYSTEM_ID
AND INVENTORY_ITEM_ID = p_item_detail_tbl(i).INVENTORY_ITEM_ID
AND ORGANIZATION_ID = p_item_detail_tbl(i).ORGANIZATION_ID
AND DATA_LEVEL_ID = p_item_detail_tbl(i).DATA_LEVEL_ID
AND NVL(SUPPLIER_ID, -1) = NVL(p_item_detail_tbl(i).SUPPLIER_ID, -1)
AND NVL(SUPPLIER_SITE_ID, -1) = NVL(p_item_detail_tbl(i).SUPPLIER_SITE_ID, -1);
INSERT INTO MTL_INTERFACE_ERRORS
(
TRANSACTION_ID,
UNIQUE_ID,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
COLUMN_NAME,
TABLE_NAME,
MESSAGE_NAME,
ERROR_MESSAGE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
VALUES
(
p_item_detail_tbl(i).TRANSACTION_ID,
NULL,
p_item_detail_tbl(i).ORGANIZATION_ID,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_login_id,
NULL,
p_intf_table_name,
NULL,
l_msg_text,
p_req_id,
p_prog_appid,
p_prog_id,
SYSDATE
);
SELECT NVL(ENABLED_FOR_DATA_POOL, 'N') INTO l_enabled_for_data_pool
FROM EGO_IMPORT_OPTION_SETS
WHERE BATCH_ID = p_batch_id;
SELECT DATA_LEVEL_ID INTO l_item_master_dl_id
FROM EGO_DATA_LEVEL_B
WHERE ATTR_GROUP_TYPE = 'EGO_MASTER_ITEMS'
AND APPLICATION_ID = 431
AND DATA_LEVEL_NAME = 'ITEM_ORG';
UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
SET PROCESS_FLAG = 6,
REQUEST_ID = l_request_id,
PROGRAM_ID = l_prog_id,
PROGRAM_APPLICATION_ID = l_prog_appid,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_user_id
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 1
AND EXISTS (SELECT NULL
FROM EGO_INBOUND_MSG_EXT eime
WHERE eime.SOURCE_SYSTEM_ID = msii.SOURCE_SYSTEM_ID
AND eime.DATA_LEVEL_ID = l_item_master_dl_id
AND eime.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
AND eime.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND eime.LAST_MESSAGE_TIMESTAMP > msii.MESSAGE_TIMESTAMP
)
RETURNING
TRANSACTION_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
ITEM_NUMBER,
SOURCE_SYSTEM_REFERENCE,
SOURCE_SYSTEM_ID,
l_item_master_dl_id,
-1,
-1,
PROCESS_FLAG
BULK COLLECT INTO l_item_detail_tbl;
Debug_Conc_Log('Validate_Timestamp_With_Prod: Updated MSII rowcount='||SQL%ROWCOUNT);
UPDATE EGO_ITM_USR_ATTR_INTRFC eiuai
SET PROCESS_STATUS = 6,
REQUEST_ID = l_request_id,
PROGRAM_ID = l_prog_id,
PROGRAM_APPLICATION_ID = l_prog_appid,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_user_id
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 1
AND ROW_IDENTIFIER IN (SELECT eiuai2.ROW_IDENTIFIER
FROM EGO_INBOUND_MSG_EXT eime, EGO_ITM_USR_ATTR_INTRFC eiuai2, MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE eiuai2.DATA_SET_ID = eiuai.DATA_SET_ID
AND eiuai2.PROCESS_STATUS = 1
AND eime.SOURCE_SYSTEM_ID = eiuai2.SOURCE_SYSTEM_ID
AND eime.DATA_LEVEL_ID = eiuai2.DATA_LEVEL_ID
AND eime.INVENTORY_ITEM_ID = eiuai2.INVENTORY_ITEM_ID
AND eime.ORGANIZATION_ID = eiuai2.ORGANIZATION_ID
AND NVL(eime.SUPPLIER_ID, -99) = NVL(eiuai2.PK1_VALUE, -99)
AND NVL(eime.SUPPLIER_SITE_ID, -99) = NVL(eiuai2.PK2_VALUE, -99)
AND eiuai2.DATA_SET_ID = msii.SET_PROCESS_ID
AND msii.PROCESS_FLAG = 6
AND eiuai2.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
AND eiuai2.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND eime.LAST_MESSAGE_TIMESTAMP > msii.MESSAGE_TIMESTAMP
GROUP BY eiuai2.ROW_IDENTIFIER
)
RETURNING
TRANSACTION_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
ITEM_NUMBER,
SOURCE_SYSTEM_REFERENCE,
SOURCE_SYSTEM_ID,
DATA_LEVEL_ID,
PK1_VALUE,
PK2_VALUE,
PROCESS_STATUS
BULK COLLECT INTO l_item_detail_tbl;
Debug_Conc_Log('Validate_Timestamp_With_Prod: Updated EGO_ITM_USR_ATTR_INTRFC rowcount='||SQL%ROWCOUNT);
UPDATE EGO_ITEM_ASSOCIATIONS_INTF eiai
SET PROCESS_FLAG = 6,
REQUEST_ID = l_request_id,
PROGRAM_ID = l_prog_id,
PROGRAM_APPLICATION_ID = l_prog_appid,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_user_id
WHERE BATCH_ID = p_batch_id
AND PROCESS_FLAG = 1
AND EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE eiai.BATCH_ID = msii.SET_PROCESS_ID
AND msii.PROCESS_FLAG = 6
AND eiai.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
AND eiai.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND eiai.BUNDLE_ID = msii.BUNDLE_ID
)
RETURNING
TRANSACTION_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
ITEM_NUMBER,
SOURCE_SYSTEM_REFERENCE,
SOURCE_SYSTEM_ID,
DATA_LEVEL_ID,
PK1_VALUE,
PK2_VALUE,
PROCESS_FLAG
BULK COLLECT INTO l_item_detail_tbl;
Debug_Conc_Log('Validate_Timestamp_With_Prod: Updated EGO_ITEM_ASSOCIATIONS_INTF rowcount='||SQL%ROWCOUNT);
UPDATE MTL_ITEM_CATEGORIES_INTERFACE mici
SET PROCESS_FLAG = 6,
REQUEST_ID = l_request_id,
PROGRAM_ID = l_prog_id,
PROGRAM_APPLICATION_ID = l_prog_appid,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_user_id
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 1
AND EXISTS (SELECT NULL
FROM EGO_INBOUND_MSG_EXT eime, MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE mici.SET_PROCESS_ID = msii.SET_PROCESS_ID
AND msii.PROCESS_FLAG = 6
AND mici.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
AND mici.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND eime.SOURCE_SYSTEM_ID = mici.SOURCE_SYSTEM_ID
AND eime.DATA_LEVEL_ID = l_item_master_dl_id
AND eime.INVENTORY_ITEM_ID = mici.INVENTORY_ITEM_ID
AND eime.ORGANIZATION_ID = mici.ORGANIZATION_ID
AND eime.LAST_MESSAGE_TIMESTAMP > msii.MESSAGE_TIMESTAMP
);
Debug_Conc_Log('Validate_Timestamp_With_Prod: Updated MTL_ITEM_CATEGORIES_INTERFACE rowcount='||SQL%ROWCOUNT);
UPDATE BOM_BILL_OF_MTLS_INTERFACE bbmi
SET PROCESS_FLAG = 6,
REQUEST_ID = l_request_id,
PROGRAM_ID = l_prog_id,
PROGRAM_APPLICATION_ID = l_prog_appid,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_user_id
WHERE BATCH_ID = p_batch_id
AND PROCESS_FLAG = 1
AND EXISTS (SELECT NULL
FROM EGO_INBOUND_MSG_EXT eime, MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE bbmi.BATCH_ID = msii.SET_PROCESS_ID
AND msii.PROCESS_FLAG = 6
AND bbmi.ASSEMBLY_ITEM_ID = msii.INVENTORY_ITEM_ID
AND bbmi.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND eime.SOURCE_SYSTEM_ID = msii.SOURCE_SYSTEM_ID
AND eime.DATA_LEVEL_ID = l_item_master_dl_id
AND eime.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
AND eime.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND eime.LAST_MESSAGE_TIMESTAMP > msii.MESSAGE_TIMESTAMP
);
Debug_Conc_Log('Validate_Timestamp_With_Prod: Updated BOM_BILL_OF_MTLS_INTERFACE rowcount='||SQL%ROWCOUNT);
UPDATE BOM_INVENTORY_COMPS_INTERFACE bici
SET PROCESS_FLAG = 6,
REQUEST_ID = l_request_id,
PROGRAM_ID = l_prog_id,
PROGRAM_APPLICATION_ID = l_prog_appid,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_user_id
WHERE BATCH_ID = p_batch_id
AND PROCESS_FLAG = 1
AND EXISTS (SELECT NULL
FROM EGO_INBOUND_MSG_EXT eime, MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE bici.BATCH_ID = msii.SET_PROCESS_ID
AND msii.PROCESS_FLAG = 6
AND bici.COMPONENT_ITEM_ID = msii.INVENTORY_ITEM_ID
AND bici.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND eime.SOURCE_SYSTEM_ID = msii.SOURCE_SYSTEM_ID
AND eime.DATA_LEVEL_ID = l_item_master_dl_id
AND eime.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
AND eime.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND eime.LAST_MESSAGE_TIMESTAMP > msii.MESSAGE_TIMESTAMP
);
Debug_Conc_Log('Validate_Timestamp_With_Prod: Updated BOM_INVENTORY_COMPS_INTERFACE rowcount='||SQL%ROWCOUNT);
PROCEDURE Insert_fun_gen_setup_udas
(p_batch_id IN NUMBER)
IS
CURSOR cat_in_batch(p_batch_id NUMBER) IS
SELECT DISTINCT msii.item_catalog_group_id AS item_catalog_group_id
FROM mtl_system_items_interface msii,
mtl_parameters mp
WHERE msii.set_process_id = p_batch_id
AND msii.process_flag = 1
AND msii.transaction_type = 'CREATE'
AND msii.item_catalog_group_id IS NOT NULL
AND msii.item_catalog_group_id <> -1
AND Nvl(msii.style_item_flag,'N') = 'N'
AND msii.organization_id = mp.organization_id
AND mp.organization_id = mp.master_organization_id;
l_last_updated_by NUMBER;
Debug_conc_log('INSERT_FUN_GEN_SETUP_UDAS: Starting. First set org_code into MSII.');
UPDATE mtl_system_items_interface msii
SET msii.organization_code = (SELECT mp.organization_code
FROM mtl_parameters mp
WHERE mp.organization_id = msii.organization_id)
WHERE SET_PROCESS_ID = P_BATCH_ID;
UPDATE mtl_system_items_interface msii
SET msii.organization_code = (SELECT mp.organization_code
FROM mtl_parameters mp
WHERE mp.organization_id = msii.organization_id);
Debug_conc_log('INSERT_FUN_GEN_SETUP_UDAS: Done setting org_code into MSII. Now looping through distinct ICC in this batch.');
Debug_conc_log('INSERT_FUN_GEN_SETUP_UDAS: Processing ICC: '
||j.item_catalog_group_id);
SELECT To_number(Substr(item_num_action_id,Instr(item_num_action_id,'$$',2) + 2)) AS item_num_action_id,
To_number(Substr(item_desc_action_id,Instr(item_desc_action_id,'$$',2) + 2)) AS item_desc_action_id
INTO l_num_action_id,l_desc_action_id
FROM (SELECT Min(CASE
WHEN item_desc_gen_method = 'F'
AND (PRIOR item_desc_gen_method IS NULL
OR PRIOR item_desc_gen_method = 'I')
THEN '$$'
||Lpad(LEVEL,6,'0')
||'$$'
||item_desc_action_id
WHEN item_desc_gen_method IN ('U','S')
THEN '$$'
||Lpad(LEVEL,6,'0')
||'$$'
ELSE NULL
END) item_desc_action_id,
Min(CASE
WHEN item_num_gen_method = 'F'
AND (PRIOR item_num_gen_method IS NULL
OR PRIOR item_num_gen_method = 'I')
THEN '$$'
||Lpad(LEVEL,6,'0')
||'$$'
||item_num_action_id
WHEN item_num_gen_method IN ('U','S')
THEN '$$'
||Lpad(LEVEL,6,'0')
||'$$'
ELSE NULL
END) item_num_action_id
FROM mtl_item_catalog_groups_b
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id START WITH item_catalog_group_id = j.item_catalog_group_id);
Debug_conc_log('INSERT_FUN_GEN_SETUP_UDAS: l_num_action_id: '
||l_num_action_id
||', l_desc_action_id: '
||l_desc_action_id);
SELECT ag_ext.attr_group_id,
fl_col.descriptive_flex_context_code,
fl_col.end_user_column_name,
attr_ext.data_type,
To_number(Decode(attr_ext.data_type,'N',fl_col.default_value,
NULL)),
Decode(attr_ext.data_type,'A',fl_col.default_value,
'C',fl_col.default_value,
NULL),
Decode(attr_ext.data_type,'X',ego_user_attrs_bulk_pvt.Get_date(fl_col.default_value,NULL),
'Y',ego_user_attrs_bulk_pvt.Get_date(fl_col.default_value,NULL),
NULL)
BULK COLLECT INTO l_attr_group_id,l_attr_group_name,l_attr_name,l_data_type,
l_n_def_value,l_c_def_value,l_d_def_value
FROM ego_mappings_b MAP,
fnd_descr_flex_column_usages fl_col,
ego_fnd_df_col_usgs_ext attr_ext,
ego_fnd_dsc_flx_ctx_ext ag_ext,
ego_attr_group_dl ag_dl
WHERE MAP.mapped_obj_type = 'A'
AND fl_col.application_id = MAP.mapped_to_group_pk1
AND fl_col.descriptive_flexfield_name = MAP.mapped_to_group_pk2
AND fl_col.descriptive_flex_context_code = MAP.mapped_to_group_pk3
AND fl_col.end_user_column_name = MAP.mapped_attribute
AND ag_ext.application_id = fl_col.application_id
AND ag_ext.descriptive_flexfield_name = fl_col.descriptive_flexfield_name
AND ag_ext.descriptive_flex_context_code = fl_col.descriptive_flex_context_code
AND attr_ext.application_id = fl_col.application_id
AND attr_ext.descriptive_flexfield_name = fl_col.descriptive_flexfield_name
AND attr_ext.descriptive_flex_context_code = fl_col.descriptive_flex_context_code
AND attr_ext.application_column_name = fl_col.application_column_name
AND ag_ext.attr_group_id = ag_dl.attr_group_id
AND ag_dl.data_level_id = 43101
AND fl_col.default_value IS NOT NULL
AND fl_col.enabled_flag = 'Y'
AND fl_col.descriptive_flexfield_name = 'EGO_ITEMMGMT_GROUP'
AND fl_col.application_id = 431
AND ((l_num_action_id IS NOT NULL
AND l_desc_action_id IS NOT NULL
AND (To_number(MAP.mapped_obj_pk1_val) IN (l_num_action_id,l_desc_action_id)))
OR (l_num_action_id IS NOT NULL
AND l_desc_action_id IS NULL
AND To_number(MAP.mapped_obj_pk1_val) = l_num_action_id)
OR (l_num_action_id IS NULL
AND l_desc_action_id IS NOT NULL
AND To_number(MAP.mapped_obj_pk1_val) = l_desc_action_id));
Debug_conc_log('INSERT_FUN_GEN_SETUP_UDAS: Done getting FG attrs for l_num_action_id: '
||l_num_action_id
||', l_desc_action_id: '
||l_desc_action_id);
Debug_conc_log('INSERT_FUN_GEN_SETUP_UDAS: Processing l_attr_name: '
||l_attr_name(i)
||', l_attr_group_id: '
||l_attr_group_id(i)
||', l_attr_group_name: '
||l_attr_group_name(i)
||', l_data_type: '
||l_data_type(i)
||', l_n_def_value: '
||l_n_def_value(i)
||', l_c_def_value: '
||l_c_def_value(i)
||', l_d_def_value: '
||l_d_def_value(i));
SELECT Count(* )
INTO l_ag_exits_count
FROM ego_itm_usr_attr_intrfc
WHERE data_set_id = p_batch_id
AND attr_group_int_name = l_attr_group_name(i)
AND process_status = 2 -- Bug 12553744
AND data_level_id = 43101;
Debug_conc_log('INSERT_FUN_GEN_SETUP_UDAS: l_ag_exits_count :'||l_ag_exits_count); -- Bug 12553744
INSERT INTO ego_itm_usr_attr_intrfc
(process_status,
data_set_id,
row_identifier,
attr_group_int_name,
attr_int_name,
attr_value_num,
attr_value_str,
attr_value_date,
transaction_type,
inventory_item_id,
organization_id,
/* Bug 13720439 - Start
source_system_reference,
source_system_id,
item_number,
organization_code,
*/ -- Bug 13720439 - End
data_level_id,
/* Bug 13720439 - Start
pk1_value,
pk2_value,
revision_id,
*/ -- Bug 13720439 - End
item_catalog_group_id,
attr_group_type,
attr_group_id,
request_id, -- Bug 10112500
created_by,
creation_date,
last_updated_by,
last_update_date)
SELECT 2, /* Bug 12553744 */
data_set_id,
row_identifier,
l_attr_group_name(i),
l_attr_name(i),
l_n_def_value(i),
l_c_def_value(i),
ego_user_attrs_bulk_pvt.Get_date(l_d_def_value(i)),
'CREATE',
a.inventory_item_id,
a.organization_id,
/* Bug 13720439 - Start
a.source_system_reference,
a.source_system_id,
a.item_number,
a.organization_code,
*/ -- Bug 13720439 - End
data_level_id,
/* Bug 13720439 - Start
pk1_value,
pk2_value,
revision_id,
*/ -- Bug 13720439 - End
a.item_catalog_group_id,
attr_group_type,
attr_group_id,
FND_GLOBAL.CONC_REQUEST_ID, -- Bug 10112500
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE
FROM ego_itm_usr_attr_intrfc a,
/* Bug 10112500 - Start */
mtl_system_items_interface msii,
mtl_parameters mp
/* Bug 10112500 - End */
WHERE NOT EXISTS (SELECT NULL
FROM ego_itm_usr_attr_intrfc b
WHERE data_set_id = a.data_set_id
AND b.attr_int_name = l_attr_name(i)
AND b.attr_group_int_name = a.attr_group_int_name
-- AND b.transaction_type = a.transaction_type -- Bug 13720439
AND b.data_level_id = a.data_level_id
AND a.row_identifier = b.row_identifier)
AND data_set_id = p_batch_id
AND attr_group_int_name = l_attr_group_name(i)
AND data_level_id = 43101
AND process_status = 2 /* Bug 10263673, changing the value 2 as by the time this code gets executed all the records will be in status 2 */
/* Bug 10112500 - Start */
AND msii.set_process_id = p_batch_id
AND msii.process_flag = 1
AND Nvl(msii.style_item_flag,'N') = 'N'
AND msii.organization_id = mp.organization_id
AND mp.organization_id = mp.master_organization_id
AND msii.item_catalog_group_id = j.item_catalog_group_id
AND msii.transaction_type = 'CREATE'
AND (a.inventory_item_id = msii.inventory_item_id
OR a.item_number = msii.item_number
OR a.source_system_reference = msii.source_system_reference)
AND (a.organization_id = msii.organization_id
OR a.organization_code = msii.organization_code)
/* Bug 10112500 - End */
GROUP BY data_set_id,
row_identifier,
a.inventory_item_id,
a.organization_id,
/* Bug 13720439 - Start
a.source_system_reference,
a.source_system_id,
a.item_number,
a.organization_code,
*/ -- Bug 13720439 - End
data_level_id,
/* Bug 13720439 - Start
pk1_value,
pk2_value,
revision_id,
*/ -- Bug 13720439 - End
a.item_catalog_group_id,
attr_group_id,
attr_group_type;
Debug_conc_log('INSERT_FUN_GEN_SETUP_UDAS: Inserted '||SQL%ROWCOUNT||' rows into ego_itm_usr_attr_intrfc ' ); -- Bug 12553744
SELECT Nvl(Max(row_identifier),1) -- Bug 9820607
INTO l_row_id
FROM ego_itm_usr_attr_intrfc
WHERE data_set_id = p_batch_id;
Debug_conc_log('INSERT_FUN_GEN_SETUP_UDAS: l_row_id : '||l_row_id); -- Bug 12553744
INSERT INTO ego_itm_usr_attr_intrfc
(process_status,
data_set_id,
row_identifier,
attr_group_int_name,
attr_int_name,
attr_value_num,
attr_value_str,
attr_value_date,
transaction_type,
inventory_item_id,
organization_id,
source_system_reference,
item_number,
organization_code,
data_level_id,
attr_group_type,
attr_group_id,
item_catalog_group_id, /* Bug 12553744 */
request_id, /* Bug 10112500 */
created_by,
creation_date,
last_updated_by,
last_update_date)
/* Bug 9678667 : Added Below hint */
SELECT /*+ leading(mp) use_hash(msii) */
2, /* Bug 12553744 */
p_batch_id,
l_row_id + ROWNUM,
l_attr_group_name(i),
l_attr_name(i),
l_n_def_value(i),
l_c_def_value(i),
ego_user_attrs_bulk_pvt.Get_date(l_d_def_value(i)),
'CREATE',
inventory_item_id,
msii.organization_id,
source_system_reference,
item_number,
msii.organization_code,
43101,
'EGO_ITEMMGMT_GROUP',
l_attr_group_id(i),
j.item_catalog_group_id, /* Bug 12553744 */
FND_GLOBAL.CONC_REQUEST_ID, /* Bug 10112500 */
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE
FROM mtl_system_items_interface msii,
mtl_parameters mp
WHERE msii.set_process_id = p_batch_id
AND msii.process_flag = 1
AND msii.transaction_type = 'CREATE' -- Bug 10112500
AND Nvl(msii.style_item_flag,'N') = 'N'
AND msii.organization_id = mp.organization_id
AND mp.organization_id = mp.master_organization_id
AND msii.item_catalog_group_id = j.item_catalog_group_id
AND NOT EXISTS ((SELECT /*+ index(B, EGO_ITM_USR_ATTR_INTRFC_N1) */ -- Bug 9678667
NULL
FROM ego_itm_usr_attr_intrfc b
WHERE data_set_id = p_batch_id
AND b.attr_int_name = l_attr_name(i)
AND b.attr_group_int_name = l_attr_group_name(i)
/* Fix for bug#9660659 - Start */
/*AND (b.inventory_item_id = msii.inventory_item_id
OR b.item_number = msii.item_number
OR b.source_system_reference = msii.source_system_reference)
AND (b.organization_id = msii.organization_id
OR b.organization_code = msii.organization_code)
AND b.data_level_id = 43101);
(SELECT NULL
FROM ego_itm_usr_attr_intrfc b
WHERE data_set_id = p_batch_id
AND b.attr_int_name = l_attr_name(i)
AND b.attr_group_int_name = l_attr_group_name(i)
AND (b.item_number = msii.item_number)
AND (b.organization_id = msii.organization_id
OR b.organization_code = msii.organization_code)
AND b.process_status = 2 /* Bug 10263673, changing the value 2 as by the time this code gets executed all the records will be in status 2 */
AND b.data_level_id = 43101)
UNION ALL
(SELECT NULL
FROM ego_itm_usr_attr_intrfc b
WHERE data_set_id = p_batch_id
AND b.attr_int_name = l_attr_name(i)
AND b.attr_group_int_name = l_attr_group_name(i)
AND b.source_system_id = msii.source_system_id -- Bug 9678667
AND (b.source_system_reference = msii.source_system_reference)
AND (b.organization_id = msii.organization_id
OR b.organization_code = msii.organization_code)
AND b.process_status = 2 /* Bug 10263673, changing the value 2 as by the time this code gets executed all the records will be in status 2 */
AND b.data_level_id = 43101));
Debug_conc_log('INSERT_FUN_GEN_SETUP_UDAS: Inserted '||SQL%ROWCOUNT||' rows into ego_itm_usr_attr_intrfc ' ); -- Bug 12553744
END insert_fun_gen_setup_udas;
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET DATA_SET_ID = -999
WHERE PROCESS_STATUS = 1;
SELECT NVL(ENABLED_FOR_DATA_POOL, 'N')
INTO l_enabled_for_data_pool
FROM EGO_IMPORT_OPTION_SETS
WHERE BATCH_ID = l_batch_id; -- bug 12693246 changed from p_batch_id to l_batch_id
SELECT 'Y' INTO l_copy_option_exists
FROM EGO_IMPORT_COPY_OPTIONS
WHERE BATCH_ID = l_batch_id -- bug 12693246 changed from p_batch_id to l_batch_id
AND ROWNUM = 1;
to identify the records that are not inserted by defaulting APIs i.e to identify the records entered by user.
*/
UPDATE /*+ INDEX(EGO_ITM_USR_ATTR_INTRFC, EGO_ITM_USR_ATTR_INTRFC_N3) */
EGO_ITM_USR_ATTR_INTRFC
SET PROG_INT_NUM4 = 0
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 1;
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 = 2
AND DATA_SET_ID = p_batch_id
AND TRANSACTION_TYPE = 'CREATE'
AND PROG_INT_CHAR1 IN ('FROM_INTF', 'FROM_PROD');
SELECT
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
ITEM_NUMBER,
STYLE_ITEM_FLAG,
STYLE_ITEM_ID,
SOURCE_SYSTEM_ID,
ITEM_CATALOG_GROUP_ID,
ITEM_CATALOG_GROUP_NAME,
STYLE_ITEM_NUMBER --bug 14319402, add style item number to check for existing style record in MSII
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 1
AND STYLE_ITEM_FLAG IN ('N', 'Y')
AND UPPER(TRANSACTION_TYPE) IN ('CREATE', 'SYNC')
AND EXISTS (SELECT NULL
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = MSII.ORGANIZATION_ID
AND MP.ORGANIZATION_ID <> MP.MASTER_ORGANIZATION_ID)
UNION
SELECT
MSI.ORGANIZATION_ID AS ORGANIZATION_ID,
MSI.INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID,
MSI.CONCATENATED_SEGMENTS AS ITEM_NUMBER,
'Y' AS STYLE_ITEM_FLAG,
MSI.INVENTORY_ITEM_ID AS STYLE_ITEM_ID,
c_source_system_id AS SOURCE_SYSTEM_ID,
MSI.ITEM_CATALOG_GROUP_ID,
NULL,
NULL
FROM MTL_SYSTEM_ITEMS_KFV MSI, MTL_SYSTEM_ITEMS_INTERFACE MSII, MTL_PARAMETERS MP
WHERE MSII.SET_PROCESS_ID = p_batch_id
AND MSII.PROCESS_FLAG = 1
AND MSII.STYLE_ITEM_FLAG = 'N'
AND UPPER(MSII.TRANSACTION_TYPE) = 'CREATE'
AND MSII.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
AND MSII.STYLE_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND EXISTS (SELECT NULL FROM MTL_PARAMETERS MP1
WHERE MSI.ORGANIZATION_ID = MP1.ORGANIZATION_ID
AND MP1.ORGANIZATION_ID <> MP1.MASTER_ORGANIZATION_ID)
GROUP BY MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, MSI.CONCATENATED_SEGMENTS, MSI.INVENTORY_ITEM_ID, MSI.ITEM_CATALOG_GROUP_ID;
SELECT SOURCE_SYSTEM_ID INTO l_source_system_id
FROM EGO_IMPORT_BATCHES_B
WHERE BATCH_ID = p_batch_id;
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
(
SET_PROCESS_ID,
PROCESS_FLAG,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
ITEM_NUMBER,
STYLE_ITEM_FLAG,
STYLE_ITEM_ID,
SOURCE_SYSTEM_ID,
TRANSACTION_TYPE,
ITEM_CATALOG_GROUP_ID,
ITEM_CATALOG_GROUP_NAME,
CREATED_BY
)
SELECT
p_batch_id, --SET_PROCESS_ID
1, -- PROCESS_FLAG
i.ORGANIZATION_ID,
MSI.INVENTORY_ITEM_ID,
MSI.CONCATENATED_SEGMENTS,
MSI.STYLE_ITEM_FLAG,
MSI.STYLE_ITEM_ID,
l_source_system_id,
'CREATE',
MSI.ITEM_CATALOG_GROUP_ID,
NULL,
-99
FROM MTL_SYSTEM_ITEMS_KFV MSI, MTL_PARAMETERS MP
WHERE MSI.STYLE_ITEM_FLAG = 'N'
AND MSI.STYLE_ITEM_ID = i.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
AND NOT EXISTS (SELECT 1
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.SET_PROCESS_ID = p_batch_id
AND MSII.PROCESS_FLAG = 1
AND MSII.ORGANIZATION_ID = i.ORGANIZATION_ID
AND (MSII.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID OR MSII.ITEM_NUMBER = MSI.CONCATENATED_SEGMENTS)
)
AND NOT EXISTS (SELECT 1
FROM MTL_SYSTEM_ITEMS_B MSIB
WHERE MSIB.ORGANIZATION_ID = i.ORGANIZATION_ID
AND MSIB.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID);
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
(
SET_PROCESS_ID,
PROCESS_FLAG,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
ITEM_NUMBER,
STYLE_ITEM_FLAG,
STYLE_ITEM_ID,
SOURCE_SYSTEM_ID,
TRANSACTION_TYPE,
ITEM_CATALOG_GROUP_ID,
ITEM_CATALOG_GROUP_NAME,
CREATED_BY
)
SELECT
p_batch_id, --SET_PROCESS_ID
1, -- PROCESS_FLAG
i.ORGANIZATION_ID,
MSII.INVENTORY_ITEM_ID,
MSII.ITEM_NUMBER,
MSII.STYLE_ITEM_FLAG,
MSII.STYLE_ITEM_ID,
MSII.SOURCE_SYSTEM_ID,
'CREATE',
MSII.ITEM_CATALOG_GROUP_ID,
MSII.ITEM_CATALOG_GROUP_NAME,
-99
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII, MTL_PARAMETERS MP
WHERE MSII.SET_PROCESS_ID = p_batch_id
AND MSII.PROCESS_FLAG = 1
AND MSII.STYLE_ITEM_FLAG = 'N'
AND UPPER(MSII.TRANSACTION_TYPE) = 'CREATE'
AND MSII.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
AND MSII.STYLE_ITEM_ID = i.INVENTORY_ITEM_ID
AND NOT EXISTS (SELECT 1
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2
WHERE MSII2.SET_PROCESS_ID = p_batch_id
AND MSII2.PROCESS_FLAG = 1
AND MSII2.ORGANIZATION_ID = i.ORGANIZATION_ID
AND (MSII2.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID OR MSII2.ITEM_NUMBER = MSII.ITEM_NUMBER)
);
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
(
SET_PROCESS_ID,
PROCESS_FLAG,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
ITEM_NUMBER,
STYLE_ITEM_FLAG,
STYLE_ITEM_ID,
SOURCE_SYSTEM_ID,
TRANSACTION_TYPE,
ITEM_CATALOG_GROUP_ID,
ITEM_CATALOG_GROUP_NAME,
CREATED_BY
)
SELECT
p_batch_id, --SET_PROCESS_ID
1, -- PROCESS_FLAG
i.ORGANIZATION_ID,
i.STYLE_ITEM_ID,
I.STYLE_ITEM_NUMBER,
'Y',
NULL,
l_source_system_id,
'CREATE',
i.ITEM_CATALOG_GROUP_ID,
i.ITEM_CATALOG_GROUP_NAME,
-99
FROM DUAL
WHERE NOT EXISTS (SELECT 1
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2
WHERE MSII2.SET_PROCESS_ID = p_batch_id
AND MSII2.PROCESS_FLAG = 1
AND MSII2.ORGANIZATION_ID = i.ORGANIZATION_ID
AND (MSII2.INVENTORY_ITEM_ID = i.STYLE_ITEM_ID OR MSII2.ITEM_NUMBER = I.STYLE_ITEM_NUMBER) --bug 14319402, need to check for item number as well, since inventory_item_id may be null now
)
AND NOT EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_B MSIB
WHERE MSIB.INVENTORY_ITEM_ID = i.STYLE_ITEM_ID
AND MSIB.ORGANIZATION_ID = i.ORGANIZATION_ID);
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET PROCESS_FLAG = 10
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 1
AND STYLE_ITEM_FLAG = 'N';
UPDATE MTL_ITEM_REVISIONS_INTERFACE miri
SET PROCESS_FLAG = 10
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 1
AND EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE msii.SET_PROCESS_ID = miri.SET_PROCESS_ID
AND msii.PROCESS_FLAG = 10
AND ( (msii.INVENTORY_ITEM_ID = miri.INVENTORY_ITEM_ID OR msii.ITEM_NUMBER = miri.ITEM_NUMBER) OR
(msii.SOURCE_SYSTEM_ID = miri.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = miri.SOURCE_SYSTEM_REFERENCE)
)
AND msii.ORGANIZATION_ID = miri.ORGANIZATION_ID
UNION ALL
SELECT NULL FROM MTL_SYSTEM_ITEMS_KFV msik
WHERE (msik.INVENTORY_ITEM_ID = miri.INVENTORY_ITEM_ID OR msik.CONCATENATED_SEGMENTS = miri.ITEM_NUMBER)
AND msik.ORGANIZATION_ID = miri.ORGANIZATION_ID
AND msik.STYLE_ITEM_FLAG = 'N'
);
UPDATE MTL_ITEM_CATEGORIES_INTERFACE mici
SET PROCESS_FLAG = 10
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 1
AND EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE msii.SET_PROCESS_ID = mici.SET_PROCESS_ID
AND msii.PROCESS_FLAG = 10
AND ( (msii.INVENTORY_ITEM_ID = mici.INVENTORY_ITEM_ID OR msii.ITEM_NUMBER = mici.ITEM_NUMBER) OR
(msii.SOURCE_SYSTEM_ID = mici.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = mici.SOURCE_SYSTEM_REFERENCE)
)
AND msii.ORGANIZATION_ID = mici.ORGANIZATION_ID
UNION ALL
SELECT NULL FROM MTL_SYSTEM_ITEMS_KFV msik
WHERE (msik.INVENTORY_ITEM_ID = mici.INVENTORY_ITEM_ID OR msik.CONCATENATED_SEGMENTS = mici.ITEM_NUMBER)
AND msik.ORGANIZATION_ID = mici.ORGANIZATION_ID
AND msik.STYLE_ITEM_FLAG = 'N'
);
UPDATE EGO_ITM_USR_ATTR_INTRFC eiuai
SET PROCESS_STATUS = 1.5
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 1
AND EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE msii.SET_PROCESS_ID = eiuai.DATA_SET_ID
AND msii.PROCESS_FLAG = 10
AND ( (msii.INVENTORY_ITEM_ID = eiuai.INVENTORY_ITEM_ID OR msii.ITEM_NUMBER = eiuai.ITEM_NUMBER) OR
(msii.SOURCE_SYSTEM_ID = eiuai.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = eiuai.SOURCE_SYSTEM_REFERENCE)
)
AND msii.ORGANIZATION_ID = eiuai.ORGANIZATION_ID
UNION ALL
SELECT NULL FROM MTL_SYSTEM_ITEMS_KFV msik
WHERE (msik.INVENTORY_ITEM_ID = eiuai.INVENTORY_ITEM_ID OR msik.CONCATENATED_SEGMENTS = eiuai.ITEM_NUMBER)
AND msik.ORGANIZATION_ID = eiuai.ORGANIZATION_ID
AND msik.STYLE_ITEM_FLAG = 'N'
);
UPDATE EGO_AML_INTF eai
SET PROCESS_FLAG = 10
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_FLAG = 1
AND EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE msii.SET_PROCESS_ID = eai.DATA_SET_ID
AND msii.PROCESS_FLAG = 10
AND ( (msii.INVENTORY_ITEM_ID = eai.INVENTORY_ITEM_ID OR msii.ITEM_NUMBER = eai.ITEM_NUMBER) OR
(msii.SOURCE_SYSTEM_ID = eai.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = eai.SOURCE_SYSTEM_REFERENCE)
)
AND msii.ORGANIZATION_ID = eai.ORGANIZATION_ID
UNION ALL
SELECT NULL FROM MTL_SYSTEM_ITEMS_KFV msik
WHERE (msik.INVENTORY_ITEM_ID = eai.INVENTORY_ITEM_ID OR msik.CONCATENATED_SEGMENTS = eai.ITEM_NUMBER)
AND msik.ORGANIZATION_ID = eai.ORGANIZATION_ID
AND msik.STYLE_ITEM_FLAG = 'N'
);
UPDATE EGO_ITEM_ASSOCIATIONS_INTF eiai
SET PROCESS_FLAG = 10
WHERE BATCH_ID = p_batch_id
AND PROCESS_FLAG = 1
AND EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE msii.SET_PROCESS_ID = eiai.BATCH_ID
AND msii.PROCESS_FLAG = 10
AND ( (msii.INVENTORY_ITEM_ID = eiai.INVENTORY_ITEM_ID OR msii.ITEM_NUMBER = eiai.ITEM_NUMBER) OR
(msii.SOURCE_SYSTEM_ID = eiai.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = eiai.SOURCE_SYSTEM_REFERENCE)
)
AND msii.ORGANIZATION_ID = eiai.ORGANIZATION_ID
UNION ALL
SELECT NULL FROM MTL_SYSTEM_ITEMS_KFV msik
WHERE (msik.INVENTORY_ITEM_ID = eiai.INVENTORY_ITEM_ID OR msik.CONCATENATED_SEGMENTS = eiai.ITEM_NUMBER)
AND msik.ORGANIZATION_ID = eiai.ORGANIZATION_ID
AND msik.STYLE_ITEM_FLAG = 'N'
);
UPDATE EGO_ITEM_PEOPLE_INTF eipi
SET PROCESS_STATUS = 10
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 1
AND EXISTS (SELECT NULL FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE msii.SET_PROCESS_ID = eipi.DATA_SET_ID
AND msii.PROCESS_FLAG = 10
AND ( (msii.INVENTORY_ITEM_ID = eipi.INVENTORY_ITEM_ID OR msii.ITEM_NUMBER = eipi.ITEM_NUMBER) OR
(msii.SOURCE_SYSTEM_ID = eipi.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = eipi.SOURCE_SYSTEM_REFERENCE)
)
AND msii.ORGANIZATION_ID = eipi.ORGANIZATION_ID
UNION ALL
SELECT NULL FROM MTL_SYSTEM_ITEMS_KFV msik
WHERE (msik.INVENTORY_ITEM_ID = eipi.INVENTORY_ITEM_ID OR msik.CONCATENATED_SEGMENTS = eipi.ITEM_NUMBER)
AND msik.ORGANIZATION_ID = eipi.ORGANIZATION_ID
AND msik.STYLE_ITEM_FLAG = 'N'
);
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET PROCESS_FLAG = 1
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 10
AND STYLE_ITEM_FLAG = 'N';
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET STYLE_ITEM_ID = (SELECT MSIK.INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_KFV MSIK
WHERE MSIK.CONCATENATED_SEGMENTS = MSII.STYLE_ITEM_NUMBER
AND MSIK.ORGANIZATION_ID = MSII.ORGANIZATION_ID
)
WHERE SET_PROCESS_ID = p_batch_id
AND STYLE_ITEM_NUMBER IS NOT NULL
AND STYLE_ITEM_ID IS NULL
AND STYLE_ITEM_FLAG = 'N'
AND PROCESS_FLAG = 1;
* or UPDATE already */
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET COPY_ITEM_ID = STYLE_ITEM_ID,
COPY_ORGANIZATION_ID = ORGANIZATION_ID,
TEMPLATE_ID = DECODE(TRANSACTION_TYPE,'CREATE',NULL,TEMPLATE_ID), -- change for bug 12376516
TEMPLATE_NAME = DECODE(TRANSACTION_TYPE,'CREATE',NULL,TEMPLATE_NAME) -- change for bug 12376516
WHERE SET_PROCESS_ID = p_batch_id
AND STYLE_ITEM_ID IS NOT NULL
AND STYLE_ITEM_FLAG = 'N'
AND PROCESS_FLAG = 1;
UPDATE MTL_ITEM_REVISIONS_INTERFACE miri
SET PROCESS_FLAG = 1
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 10;
UPDATE MTL_ITEM_CATEGORIES_INTERFACE mici
SET PROCESS_FLAG = 1
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 10;
UPDATE EGO_ITM_USR_ATTR_INTRFC eiuai
SET PROCESS_STATUS = 1
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 1.5;
UPDATE EGO_AML_INTF eai
SET PROCESS_FLAG = 1
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_FLAG = 10;
UPDATE EGO_ITEM_ASSOCIATIONS_INTF eiai
SET PROCESS_FLAG = 1
WHERE BATCH_ID = p_batch_id
AND PROCESS_FLAG = 10;
UPDATE EGO_ITEM_PEOPLE_INTF eipi
SET PROCESS_STATUS = 1
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 10;
SELECT SELECTION_FLAG INTO l_copy_people_option
FROM EGO_IMPORT_COPY_OPTIONS
WHERE BATCH_ID = p_batch_id
AND COPY_OPTION = 'COPY_PEOPLE'
AND ROWNUM = 1;
SELECT MENU_ID INTO l_menu_id
FROM FND_MENUS
WHERE MENU_NAME = 'EGO_ITEM_OWNER';
SELECT 'HZ_PARTY:'||PARTY_ID INTO l_party_key
FROM EGO_USER_V
WHERE USER_ID = FND_GLOBAL.USER_ID;
SELECT OBJECT_ID INTO l_object_id
FROM FND_OBJECTS
WHERE OBJ_NAME = 'EGO_ITEM';
SELECT
msii.INVENTORY_ITEM_ID,
msii.ORGANIZATION_ID,
menus.MENU_NAME,
grants.GRANTEE_TYPE,
grants.GRANTEE_KEY,
grants.END_DATE
FROM
MTL_SYSTEM_ITEMS_INTERFACE msii,
FND_GRANTS grants,
FND_MENUS menus
WHERE msii.SET_PROCESS_ID = #' || p_batch_id || q'#
AND msii.PROCESS_FLAG = 7
AND msii.TRANSACTION_TYPE = 'CREATE'
AND msii.REQUEST_ID = #' || l_request_id || q'#
AND menus.MENU_ID = grants.MENU_ID
AND grants.INSTANCE_TYPE = 'INSTANCE'
AND grants.INSTANCE_PK1_VALUE = #' || l_item_id_sql || q'#
AND grants.INSTANCE_PK2_VALUE = TO_CHAR(msii.ORGANIZATION_ID)
AND grants.OBJECT_ID = #' || l_object_id || q'#
AND NVL(grants.END_DATE, SYSDATE) >= SYSDATE
AND NOT ( grants.MENU_ID = #' || l_menu_id || q'# AND grants.GRANTEE_KEY = '#' || l_party_key || q'#' ) #';
SELECT DISTINCT
msik.INVENTORY_ITEM_ID,
intf.ORGANIZATION_ID,
menus.MENU_NAME,
intf.GRANTEE_TYPE,
DECODE(intf.GRANTEE_TYPE, 'USER', 'HZ_PARTY:'||TO_CHAR(intf.GRANTEE_PARTY_ID),
'GROUP', 'HZ_GROUP:'||TO_CHAR(intf.GRANTEE_PARTY_ID),
'COMPANY','HZ_COMPANY:'||TO_CHAR(intf.GRANTEE_PARTY_ID),
'GLOBAL', intf.GRANTEE_TYPE,
TO_CHAR(intf.GRANTEE_PARTY_ID)
) GRANTEE_KEY,
intf.END_DATE
FROM
MTL_SYSTEM_ITEMS_KFV msik,
EGO_ITEM_PEOPLE_INTF intf,
MTL_PARAMETERS mp,
FND_MENUS menus
WHERE intf.DATA_SET_ID = p_batch_id
AND intf.PROCESS_STATUS = 4
AND intf.REQUEST_ID = cp_request_id
AND intf.TRANSACTION_TYPE = 'CREATE'
AND msik.STYLE_ITEM_ID = intf.INVENTORY_ITEM_ID
AND msik.ORGANIZATION_ID = mp.ORGANIZATION_ID
AND mp.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID
AND intf.INTERNAL_ROLE_ID = menus.MENU_ID
AND NOT EXISTS (SELECT 1 FROM FND_GRANTS fg
WHERE fg.INSTANCE_TYPE = 'INSTANCE'
AND fg.INSTANCE_PK1_VALUE = To_Char(msik.INVENTORY_ITEM_ID)
AND fg.INSTANCE_PK2_VALUE = TO_CHAR(intf.ORGANIZATION_ID)
AND fg.OBJECT_ID = cp_object_id
AND NVL(fg.END_DATE, SYSDATE) >= SYSDATE
AND fg.MENU_ID = menus.MENU_ID
AND fg.GRANTEE_TYPE = intf.GRANTEE_TYPE
AND fg.GRANTEE_KEY = 'HZ_PARTY:'||intf.GRANTEE_PARTY_ID
);
SELECT OBJECT_ID INTO l_object_id
FROM FND_OBJECTS
WHERE OBJ_NAME = 'EGO_ITEM';
SELECT
msii.INVENTORY_ITEM_ID AS DEST_ITEM_ID,
msii.ORGANIZATION_ID,
msii.STYLE_ITEM_ID AS SOURCE_ITEM_ID,
(SELECT MAX(mirb.REVISION_ID)
FROM MTL_ITEM_REVISIONS_B mirb
WHERE mirb.INVENTORY_ITEM_ID = msii.STYLE_ITEM_ID
AND mirb.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND mirb.EFFECTIVITY_DATE <= SYSDATE
) AS SOURCE_REVISION_ID,
(SELECT MAX(mirb.REVISION_ID)
FROM MTL_ITEM_REVISIONS_B mirb
WHERE mirb.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
AND mirb.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND mirb.EFFECTIVITY_DATE <= SYSDATE
) AS DEST_REVISION_ID
FROM
MTL_SYSTEM_ITEMS_INTERFACE msii,
MTL_PARAMETERS mp
WHERE msii.SET_PROCESS_ID = p_batch_id
AND msii.PROCESS_FLAG = 7
AND msii.TRANSACTION_TYPE = 'CREATE'
AND msii.REQUEST_ID = c_request_id
AND msii.ORGANIZATION_ID = mp.ORGANIZATION_ID
AND mp.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID
AND msii.STYLE_ITEM_FLAG = 'N';
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET ITEM_NUMBER = p_item_number,
SEGMENT1 = NULL,
SEGMENT2 = NULL,
SEGMENT3 = NULL,
SEGMENT4 = NULL,
SEGMENT5 = NULL,
SEGMENT6 = NULL,
SEGMENT7 = NULL,
SEGMENT8 = NULL,
SEGMENT9 = NULL,
SEGMENT10 = NULL,
SEGMENT11 = NULL,
SEGMENT12 = NULL,
SEGMENT13 = NULL,
SEGMENT14 = NULL,
SEGMENT15 = NULL,
SEGMENT16 = NULL,
SEGMENT17 = NULL,
SEGMENT18 = NULL,
SEGMENT19 = NULL,
SEGMENT20 = NULL
WHERE PROCESS_FLAG = 1
AND SET_PROCESS_ID = p_batch_id
AND SOURCE_SYSTEM_ID = p_ss_id
AND ( ITEM_NUMBER = p_old_item_number OR SOURCE_SYSTEM_REFERENCE = p_ss_ref )
RETURNING ROWID BULK COLLECT INTO l_row_id_tbl;
UPDATE MTL_ITEM_REVISIONS_INTERFACE
SET ITEM_NUMBER = p_item_number
WHERE PROCESS_FLAG = 1
AND SET_PROCESS_ID = p_batch_id
AND SOURCE_SYSTEM_ID = p_ss_id
AND SOURCE_SYSTEM_REFERENCE = p_ss_ref;
UPDATE MTL_ITEM_REVISIONS_INTERFACE
SET ITEM_NUMBER = p_item_number
WHERE PROCESS_FLAG = 1
AND SET_PROCESS_ID = p_batch_id
AND ITEM_NUMBER = p_old_item_number;
UPDATE MTL_ITEM_CATEGORIES_INTERFACE
SET ITEM_NUMBER = p_item_number
WHERE PROCESS_FLAG = 1
AND SET_PROCESS_ID = p_batch_id
AND SOURCE_SYSTEM_ID = p_ss_id
AND ( ITEM_NUMBER = p_old_item_number OR SOURCE_SYSTEM_REFERENCE = p_ss_ref );
UPDATE EGO_ITEM_PEOPLE_INTF
SET ITEM_NUMBER = p_item_number
WHERE PROCESS_STATUS = 1
AND DATA_SET_ID = p_batch_id
AND SOURCE_SYSTEM_ID = p_ss_id
AND ( ITEM_NUMBER = p_old_item_number OR SOURCE_SYSTEM_REFERENCE = p_ss_ref );
UPDATE /*+ index(EGO_ITM_USR_ATTR_INTRFC, EGO_ITM_USR_ATTR_INTRFC_N5) */ EGO_ITM_USR_ATTR_INTRFC /* Bug 9678667 - Added the hint */
SET ITEM_NUMBER = p_item_number
WHERE PROCESS_STATUS = 2
AND DATA_SET_ID = p_batch_id
AND SOURCE_SYSTEM_ID = p_ss_id
AND SOURCE_SYSTEM_REFERENCE = p_ss_ref;
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET ITEM_NUMBER = p_item_number
WHERE PROCESS_STATUS = 2
AND DATA_SET_ID = p_batch_id
AND ITEM_NUMBER = p_old_item_number;
UPDATE EGO_AML_INTF
SET ITEM_NUMBER = p_item_number
WHERE PROCESS_FLAG = 1
AND DATA_SET_ID = p_batch_id
AND SOURCE_SYSTEM_ID = p_ss_id
AND ( ITEM_NUMBER = p_old_item_number OR SOURCE_SYSTEM_REFERENCE = p_ss_ref );
UPDATE BOM_BILL_OF_MTLS_INTERFACE
SET ITEM_NUMBER = p_item_number
WHERE PROCESS_FLAG = 1
AND BATCH_ID = p_batch_id
AND ( ITEM_NUMBER = p_old_item_number OR SOURCE_SYSTEM_REFERENCE = p_ss_ref );
UPDATE BOM_INVENTORY_COMPS_INTERFACE
SET COMPONENT_ITEM_NUMBER = p_item_number
WHERE PROCESS_FLAG = 1
AND BATCH_ID = p_batch_id
AND ( COMPONENT_ITEM_NUMBER = p_old_item_number OR COMP_SOURCE_SYSTEM_REFERENCE = p_ss_ref );
UPDATE BOM_INVENTORY_COMPS_INTERFACE
SET ASSEMBLY_ITEM_NUMBER = p_item_number
WHERE PROCESS_FLAG = 1
AND BATCH_ID = p_batch_id
AND ( ASSEMBLY_ITEM_NUMBER = p_old_item_number OR PARENT_SOURCE_SYSTEM_REFERENCE = p_ss_ref );
UPDATE BOM_SUB_COMPS_INTERFACE
SET ASSEMBLY_ITEM_NUMBER = p_item_number
WHERE PROCESS_FLAG = 1
AND BATCH_ID = p_batch_id
AND ( ASSEMBLY_ITEM_NUMBER = p_old_item_number OR PARENT_SOURCE_SYSTEM_REFERENCE = p_ss_ref );
UPDATE BOM_SUB_COMPS_INTERFACE
SET COMPONENT_ITEM_NUMBER = p_item_number
WHERE PROCESS_FLAG = 1
AND BATCH_ID = p_batch_id
AND ( COMPONENT_ITEM_NUMBER = p_old_item_number OR COMP_SOURCE_SYSTEM_REFERENCE = p_ss_ref );
UPDATE BOM_SUB_COMPS_INTERFACE
SET SUBSTITUTE_COMP_NUMBER = p_item_number
WHERE PROCESS_FLAG = 1
AND BATCH_ID = p_batch_id
AND ( SUBSTITUTE_COMP_NUMBER = p_old_item_number OR SUBCOM_SOURCE_SYSTEM_REFERENCE = p_ss_ref );
UPDATE BOM_REF_DESGS_INTERFACE
SET ASSEMBLY_ITEM_NUMBER = p_item_number
WHERE PROCESS_FLAG = 1
AND BATCH_ID = p_batch_id
AND ( ASSEMBLY_ITEM_NUMBER = p_old_item_number OR PARENT_SOURCE_SYSTEM_REFERENCE = p_ss_ref );
UPDATE BOM_REF_DESGS_INTERFACE
SET COMPONENT_ITEM_NUMBER = p_item_number
WHERE PROCESS_FLAG = 1
AND BATCH_ID = p_batch_id
AND ( COMPONENT_ITEM_NUMBER = p_old_item_number OR COMP_SOURCE_SYSTEM_REFERENCE = p_ss_ref );
UPDATE BOM_COMPONENT_OPS_INTERFACE
SET ASSEMBLY_ITEM_NUMBER = p_item_number
WHERE PROCESS_FLAG = 1
AND BATCH_ID = p_batch_id
AND ( ASSEMBLY_ITEM_NUMBER = p_old_item_number OR PARENT_SOURCE_SYSTEM_REFERENCE = p_ss_ref );
UPDATE BOM_COMPONENT_OPS_INTERFACE
SET COMPONENT_ITEM_NUMBER = p_item_number
WHERE PROCESS_FLAG = 1
AND BATCH_ID = p_batch_id
AND ( COMPONENT_ITEM_NUMBER = p_old_item_number OR COMP_SOURCE_SYSTEM_REFERENCE = p_ss_ref );
, p_selection_flag IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR check_template_name (cp_template_name VARCHAR2) IS
SELECT template_id
FROM mtl_item_templates
WHERE template_name = cp_template_name;
SELECT 1 FROM mtl_system_items_interface
WHERE set_process_id = p_batch_id
AND process_flag = 1;
l_select_flag VARCHAR2(1);
IF p_selection_flag IS NULL OR p_selection_flag NOT IN ('Y', 'N') THEN
x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
x_msg_data := 'Invalid Copy Option and Selection Flag combination';
l_select_flag := p_selection_flag;
INSERT INTO ego_import_copy_options
( batch_id,
copy_option,
template_id,
template_sequence,
attr_group_id,
attach_category_id,
selection_flag,
creation_date,
created_by,
last_update_date,
last_updated_by
)
VALUES
( p_batch_id,
p_copy_option,
l_template_id,
l_sequence,
null,
null,
l_select_flag,
l_sysdate,
l_user_id,
l_sysdate,
l_user_id
);
SELECT 1
FROM EGO_ATTRS_V
WHERE UPPER(ATTR_GROUP_TYPE) = UPPER(p_attr_group_type)
AND UPPER(ATTR_GROUP_NAME) = UPPER(p_attr_group_name)
AND UPPER(ATTR_NAME) = UPPER(p_attr_name)
AND APPLICATION_ID = 431;
SELECT 1
FROM ego_attr_group_dl
WHERE data_level_id IN (SELECT data_level_id FROM ego_data_level_b
WHERE attr_group_type = p_attr_group_type
AND data_level_name = p_data_level_name
AND application_id = 431)
AND attr_group_id IN (SELECT attr_group_id FROM ego_attr_groups_v
WHERE attr_group_type = p_attr_group_type
AND attr_group_name = p_attr_group_name
AND application_id = 431);
SELECT style_item_flag,mtl_system_items_interface_s.NEXTVAL,
item_catalog_group_id
FROM mtl_system_items_interface
WHERE item_number = p_item_number
AND organization_id = p_organization_id
AND set_process_id = p_batch_id
AND process_flag = 1;
SELECT variant FROM ego_obj_attr_grp_assocs_v
WHERE attr_group_type = p_attr_group_type
AND attr_group_name = p_attr_group_name
AND application_id = 431;
SELECT MAX(ROW_IDENTIFIER), ITEM_CATALOG_GROUP_ID
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE ITEM_NUMBER = p_item_number
AND ORGANIZATION_ID = p_organization_id
AND DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 2
AND ATTR_GROUP_TYPE = p_attr_group_type
AND ATTR_GROUP_INT_NAME = p_attr_group_name
GROUP BY ROW_IDENTIFIER,ITEM_CATALOG_GROUP_ID ;
INSERT INTO EGO_ITM_USR_ATTR_INTRFC
(
PROCESS_STATUS
,TRANSACTION_ID
,DATA_SET_ID
,TRANSACTION_TYPE
,ITEM_NUMBER
,ORGANIZATION_ID
,ITEM_CATALOG_GROUP_ID
,SOURCE_SYSTEM_ID
,SOURCE_SYSTEM_REFERENCE
,ROW_IDENTIFIER
,ATTR_GROUP_TYPE
,ATTR_GROUP_INT_NAME
,ATTR_GROUP_ID
,ATTR_INT_NAME
,DATA_LEVEL_NAME
,ATTR_VALUE_STR
,ATTR_VALUE_NUM
,ATTR_VALUE_DATE
,ATTR_DISP_VALUE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
)
VALUES
(
2
,MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL
,p_batch_id
,'SYNC'
,p_item_number
,p_organization_id
,NVL(l_grp_item_catalog_group_id,l_item_catalog_group_id)
,EGO_IMPORT_PVT.get_pdh_source_system_id
,null
,NVL(l_grp_row_identifier,l_row_identifier)
,p_attr_group_type
,p_attr_group_name
,null
,p_attr_name
,p_data_level_name
,l_attr_value_str
,l_attr_value_num
,l_attr_value_date
,l_attr_disp_value
,l_user_id
,l_sysdate
,l_user_id
,l_sysdate
);
SELECT error_message BULK COLLECT INTO l_item_err_table
FROM mtl_interface_errors
WHERE transaction_id IN ( SELECT transaction_id
FROM mtl_system_items_interface
WHERE set_process_id = p_batch_id
AND process_flag = 3);
SELECT error_message BULK COLLECT INTO l_rev_err_table
FROM mtl_interface_errors
WHERE transaction_id IN ( SELECT transaction_id
FROM mtl_item_revisions_interface
WHERE set_process_id = p_batch_id
AND process_flag = 3);
SELECT error_message BULK COLLECT INTO l_uda_err_table
FROM mtl_interface_errors
WHERE transaction_id IN ( SELECT transaction_id
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE data_set_id = p_batch_id
AND process_status = 3);
SELECT NVL(ENABLED_FOR_DATA_POOL, 'N') INTO l_enabled_for_data_pool
FROM EGO_IMPORT_OPTION_SETS
WHERE BATCH_ID = p_batch_id;
SELECT DATA_LEVEL_ID INTO l_item_master_dl_id
FROM EGO_DATA_LEVEL_B
WHERE ATTR_GROUP_TYPE = 'EGO_MASTER_ITEMS'
AND APPLICATION_ID = 431
AND DATA_LEVEL_NAME = 'ITEM_ORG';
UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
SET PROCESS_FLAG = 14
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 4
AND BUNDLE_ID = (SELECT MAX(BUNDLE_ID) KEEP (DENSE_RANK FIRST ORDER BY MESSAGE_TIMESTAMP DESC )
FROM MTL_SYSTEM_ITEMS_INTERFACE msii2
WHERE msii2.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
AND msii2.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND msii2.SET_PROCESS_ID = msii.SET_PROCESS_ID
AND msii2.PROCESS_FLAG = msii.PROCESS_FLAG
);
Debug_Conc_Log('Validate_Timestamp_In_Batch: Updated MSII to 14, rowcount='||SQL%ROWCOUNT);
UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
SET PROCESS_FLAG = DECODE(PROCESS_FLAG, 4, 16, 4)
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG IN ( 4, 14 );
UPDATE MTL_ITEM_REVISIONS_INTERFACE miri
SET PROCESS_FLAG = 6,
REQUEST_ID = l_request_id,
PROGRAM_ID = l_prog_id,
PROGRAM_APPLICATION_ID = l_prog_appid,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_user_id
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 1
AND ROWID <> (SELECT MIN(miri2.ROWID)
FROM MTL_SYSTEM_ITEMS_INTERFACE msii, MTL_ITEM_REVISIONS_INTERFACE miri2
WHERE msii.INVENTORY_ITEM_ID = miri2.INVENTORY_ITEM_ID
AND msii.ORGANIZATION_ID = miri2.ORGANIZATION_ID
AND msii.SET_PROCESS_ID = miri2.SET_PROCESS_ID
AND msii.SET_PROCESS_ID = miri.SET_PROCESS_ID
AND msii.INVENTORY_ITEM_ID = miri.INVENTORY_ITEM_ID
AND msii.ORGANIZATION_ID = miri.ORGANIZATION_ID
AND msii.PROCESS_FLAG = 16
AND miri2.PROCESS_FLAG = 1
AND msii.TRANSACTION_TYPE = 'CREATE'
AND miri2.TRANSACTION_TYPE = 'CREATE'
);
Debug_Conc_Log('Validate_Timestamp_In_Batch: Updated MSII to 16,4, rowcount='||SQL%ROWCOUNT);
UPDATE EGO_ITM_USR_ATTR_INTRFC eiuai
SET PROCESS_STATUS = 6,
REQUEST_ID = l_request_id,
PROGRAM_ID = l_prog_id,
PROGRAM_APPLICATION_ID = l_prog_appid,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_user_id
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 2
AND EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE msii.SET_PROCESS_ID = eiuai.DATA_SET_ID
AND msii.PROCESS_FLAG = 16
AND eiuai.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
AND eiuai.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND eiuai.BUNDLE_ID = msii.BUNDLE_ID
)
RETURNING
TRANSACTION_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
ITEM_NUMBER,
SOURCE_SYSTEM_REFERENCE,
SOURCE_SYSTEM_ID,
DATA_LEVEL_ID,
PK1_VALUE,
PK2_VALUE,
PROCESS_STATUS
BULK COLLECT INTO l_item_detail_tbl;
Debug_Conc_Log('Validate_Timestamp_In_Batch: Updated EGO_ITM_USR_ATTR_INTRFC rowcount='||SQL%ROWCOUNT);
UPDATE EGO_ITEM_ASSOCIATIONS_INTF eiai
SET PROCESS_FLAG = 6,
REQUEST_ID = l_request_id,
PROGRAM_ID = l_prog_id,
PROGRAM_APPLICATION_ID = l_prog_appid,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_user_id
WHERE BATCH_ID = p_batch_id
AND PROCESS_FLAG = 1
AND EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE msii.SET_PROCESS_ID = eiai.BATCH_ID
AND msii.PROCESS_FLAG = 16
AND eiai.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
AND eiai.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND eiai.BUNDLE_ID = msii.BUNDLE_ID
)
RETURNING
TRANSACTION_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
ITEM_NUMBER,
SOURCE_SYSTEM_REFERENCE,
SOURCE_SYSTEM_ID,
DATA_LEVEL_ID,
PK1_VALUE,
PK2_VALUE,
PROCESS_FLAG
BULK COLLECT INTO l_item_detail_tbl;
Debug_Conc_Log('Validate_Timestamp_In_Batch: Updated EGO_ITEM_ASSOCIATIONS_INTF rowcount='||SQL%ROWCOUNT);
UPDATE MTL_ITEM_CATEGORIES_INTERFACE mici
SET PROCESS_FLAG = 6,
REQUEST_ID = l_request_id,
PROGRAM_ID = l_prog_id,
PROGRAM_APPLICATION_ID = l_prog_appid,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_user_id
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 1
AND EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE msii.SET_PROCESS_ID = mici.SET_PROCESS_ID
AND msii.PROCESS_FLAG = 16
AND mici.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
AND mici.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND mici.BUNDLE_ID = msii.BUNDLE_ID
);
Debug_Conc_Log('Validate_Timestamp_In_Batch: Updated MTL_ITEM_CATEGORIES_INTERFACE rowcount='||SQL%ROWCOUNT);
UPDATE BOM_BILL_OF_MTLS_INTERFACE bbmi
SET PROCESS_FLAG = 6,
REQUEST_ID = l_request_id,
PROGRAM_ID = l_prog_id,
PROGRAM_APPLICATION_ID = l_prog_appid,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_user_id
WHERE BATCH_ID = p_batch_id
AND PROCESS_FLAG = 1
AND EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE msii.SET_PROCESS_ID = bbmi.BATCH_ID
AND msii.PROCESS_FLAG = 16
AND bbmi.ASSEMBLY_ITEM_ID = msii.INVENTORY_ITEM_ID
AND bbmi.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND bbmi.BUNDLE_ID = msii.BUNDLE_ID
);
Debug_Conc_Log('Validate_Timestamp_In_Batch: Updated BOM_BILL_OF_MTLS_INTERFACE rowcount='||SQL%ROWCOUNT);
UPDATE BOM_INVENTORY_COMPS_INTERFACE bici
SET PROCESS_FLAG = 6,
REQUEST_ID = l_request_id,
PROGRAM_ID = l_prog_id,
PROGRAM_APPLICATION_ID = l_prog_appid,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_user_id
WHERE BATCH_ID = p_batch_id
AND PROCESS_FLAG = 1
AND EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE msii.SET_PROCESS_ID = bici.BATCH_ID
AND msii.PROCESS_FLAG = 16
AND bici.COMPONENT_ITEM_ID = msii.INVENTORY_ITEM_ID
AND bici.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND bici.BUNDLE_ID = msii.BUNDLE_ID
);
Debug_Conc_Log('Validate_Timestamp_In_Batch: Updated BOM_INVENTORY_COMPS_INTERFACE rowcount='||SQL%ROWCOUNT);
UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
SET PROCESS_FLAG = 6,
REQUEST_ID = l_request_id,
PROGRAM_ID = l_prog_id,
PROGRAM_APPLICATION_ID = l_prog_appid,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_user_id
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 16
RETURNING
TRANSACTION_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
ITEM_NUMBER,
SOURCE_SYSTEM_REFERENCE,
SOURCE_SYSTEM_ID,
l_item_master_dl_id,
-1,
-1,
PROCESS_FLAG
BULK COLLECT INTO l_item_detail_tbl;
Debug_Conc_Log('Validate_Timestamp_In_Batch: Updated MSII to 6, rowcount='||SQL%ROWCOUNT);
PROCEDURE Update_Timestamp_In_Prod(retcode OUT NOCOPY VARCHAR2,
errbuf OUT NOCOPY VARCHAR2,
p_batch_id IN NUMBER)
IS
l_item_master_dl_id NUMBER;
Debug_Conc_Log('Update_Timestamp_In_Prod: Starting p_batch_id='||p_batch_id);
SELECT NVL(ENABLED_FOR_DATA_POOL, 'N') INTO l_enabled_for_data_pool
FROM EGO_IMPORT_OPTION_SETS
WHERE BATCH_ID = p_batch_id;
Debug_Conc_Log('Update_Timestamp_In_Prod: exception='||SQLERRM);
Debug_Conc_Log('Update_Timestamp_In_Prod: l_enabled_for_data_pool='||l_enabled_for_data_pool);
Debug_Conc_Log('Update_Timestamp_In_Prod: Done');
SELECT DATA_LEVEL_ID INTO l_item_master_dl_id
FROM EGO_DATA_LEVEL_B
WHERE ATTR_GROUP_TYPE = 'EGO_MASTER_ITEMS'
AND APPLICATION_ID = 431
AND DATA_LEVEL_NAME = 'ITEM_ORG';
Debug_Conc_Log('Update_Timestamp_In_Prod: No data found for EGO_MASTER_ITEMS');
Debug_Conc_Log('Update_Timestamp_In_Prod: l_item_master_dl_id='||l_item_master_dl_id);
USING ( SELECT
SOURCE_SYSTEM_ID,
l_item_master_dl_id AS DATA_LEVEL_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
NULL AS SUPPLIER_ID,
NULL AS SUPPLIER_SITE_ID,
MAX(MESSAGE_TIMESTAMP) AS MESSAGE_TIMESTAMP
FROM MTL_SYSTEM_ITEMS_INTERFACE
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 7
AND REQUEST_ID = l_request_id
GROUP BY SOURCE_SYSTEM_ID, l_item_master_dl_id, INVENTORY_ITEM_ID, ORGANIZATION_ID
UNION ALL
SELECT
eiuai.SOURCE_SYSTEM_ID,
eiuai.DATA_LEVEL_ID,
eiuai.INVENTORY_ITEM_ID,
eiuai.ORGANIZATION_ID,
eiuai.PK1_VALUE AS SUPPLIER_ID,
eiuai.PK2_VALUE AS SUPPLIER_SITE_ID,
MAX(msii.MESSAGE_TIMESTAMP) AS MESSAGE_TIMESTAMP
FROM EGO_ITM_USR_ATTR_INTRFC eiuai, MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE eiuai.DATA_SET_ID = p_batch_id
AND eiuai.PROCESS_STATUS = 4
AND eiuai.REQUEST_ID = l_request_id
AND msii.SET_PROCESS_ID = eiuai.DATA_SET_ID
AND msii.INVENTORY_ITEM_ID = eiuai.INVENTORY_ITEM_ID
AND msii.ORGANIZATION_ID = eiuai.ORGANIZATION_ID
AND msii.BUNDLE_ID = eiuai.BUNDLE_ID
GROUP BY eiuai.SOURCE_SYSTEM_ID, eiuai.DATA_LEVEL_ID, eiuai.INVENTORY_ITEM_ID, eiuai.ORGANIZATION_ID, eiuai.PK1_VALUE, eiuai.PK2_VALUE
) intf
ON ( eime.SOURCE_SYSTEM_ID = intf.SOURCE_SYSTEM_ID
AND eime.DATA_LEVEL_ID = intf.DATA_LEVEL_ID
AND eime.INVENTORY_ITEM_ID = intf.INVENTORY_ITEM_ID
AND eime.ORGANIZATION_ID = intf.ORGANIZATION_ID
AND NVL(eime.SUPPLIER_ID, -1) = NVL(intf.SUPPLIER_ID, -1)
AND NVL(eime.SUPPLIER_SITE_ID, -1) = NVL(intf.SUPPLIER_SITE_ID, -1)
)
WHEN MATCHED THEN
UPDATE SET LAST_MESSAGE_TIMESTAMP = intf.MESSAGE_TIMESTAMP,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_user_id
WHEN NOT MATCHED THEN
INSERT
(
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SOURCE_SYSTEM_ID,
DATA_LEVEL_ID,
SUPPLIER_ID,
SUPPLIER_SITE_ID,
LAST_MESSAGE_TIMESTAMP,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
intf.INVENTORY_ITEM_ID,
intf.ORGANIZATION_ID,
intf.SOURCE_SYSTEM_ID,
intf.DATA_LEVEL_ID,
intf.SUPPLIER_ID,
intf.SUPPLIER_SITE_ID,
intf.MESSAGE_TIMESTAMP,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id
);
Debug_Conc_Log('Update_Timestamp_In_Prod: Done merging');
Debug_Conc_Log('Update_Timestamp_In_Prod: Done Successfully');
Debug_Conc_Log('Update_Timestamp_In_Prod: Error - '||SQLERRM);
END Update_Timestamp_In_Prod;
SELECT DISTINCT ATTACH_CATEGORY_ID
FROM EGO_IMPORT_COPY_OPTIONS
WHERE COPY_OPTION = 'COPY_ATTCH_CATEGORY'
AND BATCH_ID = p_batch_id;
SELECT msii.ORGANIZATION_ID AS DEST_ORG_ID,
msii.INVENTORY_ITEM_ID AS DEST_ITEM_ID,
msii.COPY_ORGANIZATION_ID AS SOURCE_ORG_ID,
msii.COPY_ITEM_ID AS SOURCE_ITEM_ID
FROM MTL_SYSTEM_ITEMS_INTERFACE msii,
MTL_PARAMETERS mp
WHERE msii.SET_PROCESS_ID = p_batch_id
AND msii.PROCESS_FLAG = 7
AND msii.TRANSACTION_TYPE = 'CREATE'
AND msii.REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID
AND msii.ORGANIZATION_ID = mp.ORGANIZATION_ID
AND mp.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID
AND ( msii.STYLE_ITEM_FLAG = 'Y'
OR msii.STYLE_ITEM_FLAG IS NULL );
* production table, then delete that entry.
*/
PROCEDURE Clean_Dirty_SKUs( retcode OUT NOCOPY VARCHAR2,
errbuf OUT NOCOPY VARCHAR2,
p_batch_id IN NUMBER )
IS
l_item_dl_id NUMBER;
SELECT OBJECT_ID INTO l_item_object_id
FROM FND_OBJECTS
WHERE OBJ_NAME = 'EGO_ITEM';
SELECT DATA_LEVEL_ID INTO l_item_dl_id
FROM EGO_DATA_LEVEL_B
WHERE ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
AND APPLICATION_ID = 431
AND DATA_LEVEL_NAME = 'ITEM_LEVEL';
DELETE FROM EGO_SKU_VARIANT_ATTR_USAGES esvas
WHERE EXISTS (SELECT NULL
FROM
EGO_FND_DSC_FLX_CTX_EXT ag_ext,
EGO_OBJ_AG_ASSOCS_B assoc,
MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE msii.INVENTORY_ITEM_ID = esvas.SKU_ITEM_ID
AND msii.STYLE_ITEM_ID = esvas.STYLE_ITEM_ID
AND msii.ORGANIZATION_ID = esvas.ORGANIZATION_ID
AND msii.SET_PROCESS_ID = p_batch_id
AND msii.REQUEST_ID = l_request_id
AND msii.STYLE_ITEM_FLAG = 'N'
AND ag_ext.VARIANT = 'Y'
AND assoc.ATTR_GROUP_ID = ag_ext.ATTR_GROUP_ID
AND assoc.CLASSIFICATION_CODE = TO_CHAR(msii.ITEM_CATALOG_GROUP_ID)
AND assoc.OBJECT_ID = l_item_object_id
AND assoc.DATA_LEVEL_ID = l_item_dl_id
AND NOT EXISTS (SELECT NULL
FROM EGO_MTL_SY_ITEMS_EXT_B ext
WHERE ext.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND ext.INVENTORY_ITEM_ID = msii.INVENTORY_ITEM_ID
AND ext.ATTR_GROUP_ID = ag_ext.ATTR_GROUP_ID
AND ext.DATA_LEVEL_ID = assoc.DATA_LEVEL_ID
)
);
Debug_Conc_Log('Clean_Dirty_SKUs: Deleted '||SQL%ROWCOUNT||' rows.');
select UNIQUE SOURCE_SYSTEM_REFERENCE
from MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE PROCESS_FLAG = 0
AND SET_PROCESS_ID = p_batch_id
AND ORGANIZATION_ID IS NOT NULL;
select RANK() OVER ( ORDER BY ATTR_VALUE_DATE desc
)rnk, transaction_id,bundle_id
from EGO_ITM_USR_ATTR_INTRFC where transaction_id IN(SELECT transaction_id
FROM
( SELECT
msii.transaction_id
FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE PROCESS_FLAG = 0
AND SET_PROCESS_ID = p_batch_id
AND SOURCE_SYSTEM_REFERENCE =source_system_ref_check
AND ORGANIZATION_ID IS NOT NULL
)
) and ATTR_GROUP_INT_NAME = 'ORDERING_INFO' and ATTR_INT_NAME='LAST_MODIFIED_DATE'order by ATTR_VALUE_DATE desc;
delete FROM BOM_INVENTORY_COMPS_INTERFACE A
WHERE exists (SELECT 'x' FROM BOM_INVENTORY_COMPS_INTERFACE B
WHERE
B.PARENT_SOURCE_SYSTEM_REFERENCE = A.PARENT_SOURCE_SYSTEM_REFERENCE
and B.COMP_SOURCE_SYSTEM_REFERENCE = A.COMP_SOURCE_SYSTEM_REFERENCE
and
(B.OPERATION_SEQ_NUM = A.OPERATION_SEQ_NUM OR (B.OPERATION_SEQ_NUM IS NULL AND A.OPERATION_SEQ_NUM IS NULL))
and
(B.EFFECTIVITY_DATE= A.EFFECTIVITY_DATE OR (B.EFFECTIVITY_DATE IS NULL AND A.EFFECTIVITY_DATE IS NULL))
and B.PROCESS_FLAG = A.PROCESS_FLAG
and B.batch_id = A.batch_id -- fix for bug#9132730
and B.rowid <> A.rowid)
and A.process_flag=1
and BATCH_ID=p_batch_id and ALTERNATE_BOM_DESIGNATOR = 'PIM_PBOM_S' and bundle_id = I2.bundle_id;
delete FROM BOM_BILL_OF_MTLS_INTERFACE A
WHERE
exists (SELECT 'x' FROM BOM_BILL_OF_MTLS_INTERFACE B
WHERE
B.SOURCE_SYSTEM_REFERENCE = A.SOURCE_SYSTEM_REFERENCE
and B.PROCESS_FLAG = A.PROCESS_FLAG
and B.batch_id = A.batch_id -- fix for bug#9132730
and A.rowid <> B.rowid
)
and A.process_flag=1
and BATCH_ID=p_batch_id and ALTERNATE_BOM_DESIGNATOR = 'PIM_PBOM_S' and bundle_id = I2.bundle_id;
delete from mtl_system_items_interface where transaction_id = I2.transaction_id and SOURCE_SYSTEM_REFERENCE=I1.SOURCE_SYSTEM_REFERENCE;
delete from EGO_ITM_USR_ATTR_INTRFC where transaction_id = I2.transaction_id and SOURCE_SYSTEM_REFERENCE=I1.SOURCE_SYSTEM_REFERENCE;
delete from EGO_ITEM_ASSOCIATIONS_INTF where transaction_id = I2.transaction_id and SOURCE_SYSTEM_REFERENCE=I1.SOURCE_SYSTEM_REFERENCE;
delete from mtl_item_categories_interface where transaction_id = I2.transaction_id and SOURCE_SYSTEM_REFERENCE=I1.SOURCE_SYSTEM_REFERENCE;
delete from mtl_system_items_interface where transaction_id = I2.transaction_id and SOURCE_SYSTEM_REFERENCE=I1.SOURCE_SYSTEM_REFERENCE;
delete from EGO_ITM_USR_ATTR_INTRFC where transaction_id = I2.transaction_id and SOURCE_SYSTEM_REFERENCE=I1.SOURCE_SYSTEM_REFERENCE;
delete from EGO_ITEM_ASSOCIATIONS_INTF where transaction_id = I2.transaction_id and SOURCE_SYSTEM_REFERENCE=I1.SOURCE_SYSTEM_REFERENCE;
delete from mtl_item_categories_interface where transaction_id = I2.transaction_id and SOURCE_SYSTEM_REFERENCE=I1.SOURCE_SYSTEM_REFERENCE;