[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_TRANS_TYPE_UPDATE CONSTANT VARCHAR2(10) := 'UPDATE';
G_TRANS_TYPE_DELETE CONSTANT VARCHAR2(10) := 'DELETE';
SELECT PARTY_ID INTO l_party_id
FROM ego_user_v
WHERE USER_ID = FND_GLOBAL.USER_ID;
*For handling the case when user insert data through BackEnd and tries to import
*data using Import WorkBench.
*
*Done for Bug 5352143
*/
PROCEDURE Err_null_ssxref_ssid( p_data_set_id IN NUMBER )
IS
l_msg_text VARCHAR2(4000);
SELECT
ORGANIZATION_ID,
TRANSACTION_ID,
PROCESS_FLAG
FROM MTL_SYSTEM_ITEMS_INTERFACE
WHERE SET_PROCESS_ID = p_data_set_id
AND PROCESS_FLAG IN (33379, 33389);
SELECT
ORGANIZATION_ID,
TRANSACTION_ID,
PROCESS_FLAG
FROM MTL_ITEM_REVISIONS_INTERFACE
WHERE SET_PROCESS_ID = p_data_set_id
AND PROCESS_FLAG IN (33379, 33389);
SELECT
ORGANIZATION_ID,
TRANSACTION_ID,
PROCESS_FLAG
FROM MTL_ITEM_CATEGORIES_INTERFACE
WHERE SET_PROCESS_ID = p_data_set_id
AND PROCESS_FLAG IN (33379, 33389);
SELECT
ORGANIZATION_ID,
TRANSACTION_ID,
PROCESS_STATUS
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS IN (33379, 33389);
SELECT
ORGANIZATION_ID,
TRANSACTION_ID,
PROCESS_STATUS
FROM EGO_ITEM_PEOPLE_INTF
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS IN (33379, 33389);
SELECT
ORGANIZATION_ID,
TRANSACTION_ID,
PROCESS_FLAG
FROM EGO_AML_INTF
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_FLAG IN (33379, 33389);
SELECT
ORGANIZATION_ID,
TRANSACTION_ID,
PROCESS_FLAG
FROM EGO_ITEM_ASSOCIATIONS_INTF
WHERE BATCH_ID = p_data_set_id
AND PROCESS_FLAG IN (33379, 33389);
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET PROCESS_FLAG = (CASE WHEN MSII.SOURCE_SYSTEM_REFERENCE IS NULL THEN 33379 ELSE 33389 END),
TRANSACTION_ID = NVL(TRANSACTION_ID, MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
PROGRAM_APPLICATION_ID = l_prog_appid,
PROGRAM_ID = l_prog_id,
REQUEST_ID = l_request_id
WHERE SET_PROCESS_ID = p_data_set_id
AND PROCESS_FLAG = 0
AND (MSII.SOURCE_SYSTEM_REFERENCE IS NULL
OR MSII.SOURCE_SYSTEM_ID <>
(SELECT SOURCE_SYSTEM_ID
FROM EGO_IMPORT_BATCHES_B
WHERE BATCH_ID = MSII.SET_PROCESS_ID
)
);
UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI
SET PROCESS_FLAG = (CASE WHEN MIRI.SOURCE_SYSTEM_REFERENCE IS NULL THEN 33379 ELSE 33389 END),
TRANSACTION_ID = NVL(TRANSACTION_ID, MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
PROGRAM_APPLICATION_ID = l_prog_appid,
PROGRAM_ID = l_prog_id,
REQUEST_ID = l_request_id
WHERE SET_PROCESS_ID = p_data_set_id
AND PROCESS_FLAG = 0
AND ( MIRI.SOURCE_SYSTEM_REFERENCE IS NULL
OR MIRI.SOURCE_SYSTEM_ID <>
(SELECT SOURCE_SYSTEM_ID
FROM EGO_IMPORT_BATCHES_B
WHERE BATCH_ID = MIRI.SET_PROCESS_ID
)
);
UPDATE MTL_ITEM_CATEGORIES_INTERFACE MICI
SET PROCESS_FLAG = (CASE WHEN MICI.SOURCE_SYSTEM_REFERENCE IS NULL THEN 33379 ELSE 33389 END),
TRANSACTION_ID = NVL(TRANSACTION_ID, MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
PROGRAM_APPLICATION_ID = l_prog_appid,
PROGRAM_ID = l_prog_id,
REQUEST_ID = l_request_id
WHERE SET_PROCESS_ID = p_data_set_id
AND PROCESS_FLAG = 0
AND (MICI.SOURCE_SYSTEM_REFERENCE IS NULL
OR MICI.SOURCE_SYSTEM_ID <>
(SELECT SOURCE_SYSTEM_ID
FROM EGO_IMPORT_BATCHES_B
WHERE BATCH_ID = MICI.SET_PROCESS_ID
)
);
UPDATE EGO_ITM_USR_ATTR_INTRFC EIUAI
SET PROCESS_STATUS = (CASE WHEN EIUAI.SOURCE_SYSTEM_REFERENCE IS NULL THEN 33379 ELSE 33389 END),
TRANSACTION_ID = NVL(TRANSACTION_ID, MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
PROGRAM_APPLICATION_ID = l_prog_appid,
PROGRAM_ID = l_prog_id,
REQUEST_ID = l_request_id
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = 0
AND (EIUAI.SOURCE_SYSTEM_REFERENCE IS NULL
OR EIUAI.SOURCE_SYSTEM_ID <>
(SELECT SOURCE_SYSTEM_ID
FROM EGO_IMPORT_BATCHES_B
WHERE BATCH_ID = EIUAI.DATA_SET_ID
)
);
UPDATE EGO_ITEM_PEOPLE_INTF EIPI
SET PROCESS_STATUS = (CASE WHEN EIPI.SOURCE_SYSTEM_REFERENCE IS NULL THEN 33379 ELSE 33389 END),
TRANSACTION_ID = NVL(TRANSACTION_ID, MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
PROGRAM_APPLICATION_ID = l_prog_appid,
PROGRAM_ID = l_prog_id,
REQUEST_ID = l_request_id
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = 0
AND (EIPI.SOURCE_SYSTEM_REFERENCE IS NULL
OR EIPI.SOURCE_SYSTEM_ID <>
(SELECT SOURCE_SYSTEM_ID
FROM EGO_IMPORT_BATCHES_B
WHERE BATCH_ID = EIPI.DATA_SET_ID
)
);
UPDATE EGO_AML_INTF EAI
SET PROCESS_FLAG = (CASE WHEN EAI.SOURCE_SYSTEM_REFERENCE IS NULL THEN 33379 ELSE 33389 END),
TRANSACTION_ID = NVL(TRANSACTION_ID, MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
PROGRAM_APPLICATION_ID = l_prog_appid,
PROGRAM_ID = l_prog_id,
REQUEST_ID = l_request_id
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_FLAG = 0
AND (EAI.SOURCE_SYSTEM_REFERENCE IS NULL
OR EAI.SOURCE_SYSTEM_ID <>
(SELECT SOURCE_SYSTEM_ID
FROM EGO_IMPORT_BATCHES_B
WHERE BATCH_ID = EAI.DATA_SET_ID
)
);
UPDATE EGO_ITEM_ASSOCIATIONS_INTF EIAI
SET PROCESS_FLAG = (CASE WHEN EIAI.SOURCE_SYSTEM_REFERENCE IS NULL THEN 33379 ELSE 33389 END),
TRANSACTION_ID = NVL(TRANSACTION_ID, MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
PROGRAM_APPLICATION_ID = l_prog_appid,
PROGRAM_ID = l_prog_id,
REQUEST_ID = l_request_id
WHERE BATCH_ID = p_data_set_id
AND PROCESS_FLAG = 0
AND (EIAI.SOURCE_SYSTEM_REFERENCE IS NULL
OR EIAI.SOURCE_SYSTEM_ID <>
(SELECT SOURCE_SYSTEM_ID
FROM EGO_IMPORT_BATCHES_B
WHERE BATCH_ID = EIAI.BATCH_ID
)
);
Debug_Conc_Log( 'Err_null_ssxref_ssid - Started inserting Errors' );
Debug_Conc_Log( 'Err_null_ssxref_ssid - Finished inserting Errors' );
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET PROCESS_FLAG =3
WHERE PROCESS_FLAG IN (33379, 33389)
AND SET_PROCESS_ID = p_data_set_id;
UPDATE MTL_ITEM_REVISIONS_INTERFACE
SET PROCESS_FLAG =3
WHERE PROCESS_FLAG IN (33379, 33389)
AND SET_PROCESS_ID = p_data_set_id;
UPDATE MTL_ITEM_CATEGORIES_INTERFACE
SET PROCESS_FLAG =3
WHERE PROCESS_FLAG IN (33379, 33389)
AND SET_PROCESS_ID = p_data_set_id;
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET PROCESS_STATUS =3
WHERE PROCESS_STATUS IN (33379, 33389)
AND DATA_SET_ID = p_data_set_id;
UPDATE EGO_ITEM_PEOPLE_INTF
SET PROCESS_STATUS =3
WHERE PROCESS_STATUS IN (33379, 33389)
AND DATA_SET_ID = p_data_set_id;
UPDATE EGO_AML_INTF
SET PROCESS_FLAG =3
WHERE PROCESS_FLAG IN (33379, 33389)
AND DATA_SET_ID = p_data_set_id;
UPDATE EGO_ITEM_ASSOCIATIONS_INTF
SET PROCESS_FLAG =3
WHERE PROCESS_FLAG IN (33379, 33389)
AND BATCH_ID = p_data_set_id;
SELECT ITEM_NUM_SEQ_NAME, PREFIX, SUFFIX, ITEM_NUM_GEN_METHOD
INTO l_seq_name, l_prefix, l_suffix, l_gen_method
FROM
(
SELECT ICC.ITEM_NUM_SEQ_NAME, ICC.PREFIX, ICC.SUFFIX, ICC.ITEM_NUM_GEN_METHOD
FROM MTL_ITEM_CATALOG_GROUPS_B ICC
WHERE ICC.ITEM_NUM_GEN_METHOD IS NOT NULL
AND ICC.ITEM_NUM_GEN_METHOD <> 'I'
CONNECT BY PRIOR ICC.PARENT_CATALOG_GROUP_ID = ICC.ITEM_CATALOG_GROUP_ID
START WITH ICC.ITEM_CATALOG_GROUP_ID = p_catalog_group_id
ORDER BY LEVEL ASC
)
WHERE ROWNUM = 1;
l_sql := 'SELECT '''||l_prefix||'''||'||l_seq_name||'.NEXTVAL||'''||l_suffix||''' FROM DUAL';
SELECT COUNT(1) INTO l_records
FROM MTL_SYSTEM_ITEMS_INTERFACE
WHERE SET_PROCESS_ID = p_data_set_id;
SELECT COUNT(1) INTO l_records
FROM MTL_ITEM_REVISIONS_INTERFACE
WHERE SET_PROCESS_ID = p_data_set_id;
SELECT COUNT(1) INTO l_records
FROM MTL_ITEM_CATEGORIES_INTERFACE
WHERE SET_PROCESS_ID = p_data_set_id;
SELECT COUNT(1) INTO l_records
FROM EGO_ITEM_PEOPLE_INTF
WHERE DATA_SET_ID = p_data_set_id;
SELECT COUNT(1) INTO l_records
FROM EGO_AML_INTF
WHERE DATA_SET_ID = p_data_set_id;
SELECT COUNT(1) INTO l_records
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE DATA_SET_ID = p_data_set_id;
SELECT COUNT(1) INTO l_records
FROM EGO_ITEM_ASSOCIATIONS_INTF
WHERE BATCH_ID = p_data_set_id;
SELECT MP.ORGANIZATION_ID,
BA.SOURCE_SYSTEM_ID
INTO l_org_id
, l_ss_id
FROM MTL_PARAMETERS MP
, EGO_IMPORT_BATCHES_B ba
WHERE BA.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND BA.BATCH_ID = p_batch_id;
SELECT sub.*
,EXT.DATA_TYPE DATA_TYPE_CODE
FROM
( SELECT
EIUAI.ROWID rid
, COUNT( * ) OVER ( PARTITION BY
ITEM_NUMBER
, ATTR_GROUP_INT_NAME
, ATTR_INT_NAME
, COALESCE( EIUAI.ORGANIZATION_ID,
( SELECT ORGANIZATION_ID
FROM MTL_PARAMETERS P
WHERE P.ORGANIZATION_CODE = EIUAI.ORGANIZATION_CODE
),
( SELECT ORGANIZATION_ID
FROM MTL_ITEM_REVISIONS_B R
WHERE R.REVISION_ID = EIUAI.REVISION_ID
),
NVL2( ORGANIZATION_CODE, cp_master_org_id, NULL )
)
, NVL( eiuai.REVISION,
( SELECT r.REVISION
FROM MTL_ITEM_REVISIONS_B r
WHERE r.REVISION_ID = eiuai.REVISION_ID
)
)
, NVL( eiuai.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
)
cnt
, RANK() OVER ( ORDER BY
ITEM_NUMBER
, ATTR_GROUP_INT_NAME
, ATTR_INT_NAME
, COALESCE( EIUAI.ORGANIZATION_ID,
( SELECT ORGANIZATION_ID
FROM MTL_PARAMETERS P
WHERE P.ORGANIZATION_CODE = EIUAI.ORGANIZATION_CODE
),
( SELECT ORGANIZATION_ID
FROM MTL_ITEM_REVISIONS_B R
WHERE R.REVISION_ID = EIUAI.REVISION_ID
),
NVL2( ORGANIZATION_CODE, cp_master_org_id, NULL )
)
, NVL( eiuai.REVISION,
( SELECT r.REVISION
FROM MTL_ITEM_REVISIONS_B r
WHERE r.REVISION_ID = eiuai.REVISION_ID
)
)
, NVL( eiuai.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
)
rnk
, eiuai.*
FROM EGO_ITM_USR_ATTR_INTRFC EIUAI, EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 1
AND ITEM_NUMBER IS NOT NULL
AND FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = NVL( EIUAI.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
AND FL_CTX_EXT.APPLICATION_ID = 431
AND FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = EIUAI.ATTR_GROUP_INT_NAME
AND FL_CTX_EXT.MULTI_ROW = 'N'
AND EXISTS( SELECT NULL
FROM EGO_ATTR_GROUP_DL A
WHERE A.DATA_LEVEL_ID = cp_rev_dl_id
AND A.ATTR_GROUP_ID = FL_CTX_EXT.ATTR_GROUP_ID
)
) sub
,FND_DESCR_FLEX_COLUMN_USAGES FL_COL
,EGO_FND_DF_COL_USGS_EXT EXT
WHERE sub.CNT > 1
AND FL_COL.APPLICATION_ID = 431
AND FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE = sub.ATTR_GROUP_INT_NAME
AND FL_COL.END_USER_COLUMN_NAME = sub.ATTR_INT_NAME
AND FL_COL.DESCRIPTIVE_FLEXFIELD_NAME = SUB.ATTR_GROUP_TYPE
AND EXT.APPLICATION_ID = FL_COL.APPLICATION_ID
AND EXT.DESCRIPTIVE_FLEXFIELD_NAME = FL_COL.DESCRIPTIVE_FLEXFIELD_NAME
AND EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND EXT.APPLICATION_COLUMN_NAME = FL_COL.APPLICATION_COLUMN_NAME
ORDER BY rnk, sub.last_update_date DESC, interface_table_unique_id DESC ;
SELECT sub.*
,EXT.DATA_TYPE DATA_TYPE_CODE
FROM
( SELECT
EIUAI.ROWID rid
, COUNT( * ) OVER ( PARTITION BY
SOURCE_SYSTEM_ID
, SOURCE_SYSTEM_REFERENCE
, ATTR_GROUP_INT_NAME
, ATTR_INT_NAME
, COALESCE( EIUAI.ORGANIZATION_ID,
( SELECT ORGANIZATION_ID
FROM MTL_PARAMETERS P
WHERE P.ORGANIZATION_CODE = EIUAI.ORGANIZATION_CODE
),
( SELECT ORGANIZATION_ID
FROM MTL_ITEM_REVISIONS_B R
WHERE R.REVISION_ID = EIUAI.REVISION_ID
),
NVL2( ORGANIZATION_CODE, cp_master_org_id, NULL )
)
, NVL( eiuai.REVISION,
( SELECT r.REVISION
FROM MTL_ITEM_REVISIONS_B r
WHERE r.REVISION_ID = eiuai.REVISION_ID
)
)
, NVL( eiuai.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
)
cnt
, RANK() OVER ( ORDER BY
SOURCE_SYSTEM_ID
, SOURCE_SYSTEM_REFERENCE
, ATTR_GROUP_INT_NAME
, ATTR_INT_NAME
, COALESCE( EIUAI.ORGANIZATION_ID,
( SELECT ORGANIZATION_ID
FROM MTL_PARAMETERS P
WHERE P.ORGANIZATION_CODE = EIUAI.ORGANIZATION_CODE
),
( SELECT ORGANIZATION_ID
FROM MTL_ITEM_REVISIONS_B R
WHERE R.REVISION_ID = EIUAI.REVISION_ID
),
NVL2( ORGANIZATION_CODE, cp_master_org_id, NULL )
)
, NVL( eiuai.REVISION,
( SELECT r.REVISION
FROM MTL_ITEM_REVISIONS_B r
WHERE r.REVISION_ID = eiuai.REVISION_ID
)
)
, NVL( eiuai.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
)
rnk
, eiuai.*
FROM EGO_ITM_USR_ATTR_INTRFC EIUAI, EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 0
AND FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = NVL( EIUAI.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
AND FL_CTX_EXT.APPLICATION_ID = 431
AND FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = EIUAI.ATTR_GROUP_INT_NAME
AND FL_CTX_EXT.MULTI_ROW = 'N'
AND EXISTS( SELECT NULL
FROM EGO_ATTR_GROUP_DL A
WHERE A.DATA_LEVEL_ID = cp_rev_dl_id
AND A.ATTR_GROUP_ID = FL_CTX_EXT.ATTR_GROUP_ID
)
) sub
,FND_DESCR_FLEX_COLUMN_USAGES FL_COL
,EGO_FND_DF_COL_USGS_EXT EXT
WHERE sub.CNT > 1
AND FL_COL.APPLICATION_ID = 431
AND FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE = sub.ATTR_GROUP_INT_NAME
AND FL_COL.END_USER_COLUMN_NAME = sub.ATTR_INT_NAME
AND FL_COL.DESCRIPTIVE_FLEXFIELD_NAME = SUB.ATTR_GROUP_TYPE
AND EXT.APPLICATION_ID = FL_COL.APPLICATION_ID
AND EXT.DESCRIPTIVE_FLEXFIELD_NAME = FL_COL.DESCRIPTIVE_FLEXFIELD_NAME
AND EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND EXT.APPLICATION_COLUMN_NAME = FL_COL.APPLICATION_COLUMN_NAME
ORDER BY rnk, sub.last_update_date DESC, interface_table_unique_id DESC ;
SELECT DATA_LEVEL_ID INTO l_rev_dl_id
FROM EGO_DATA_LEVEL_B
WHERE ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
AND APPLICATION_ID = 431
AND DATA_LEVEL_NAME = 'ITEM_REVISION_LEVEL';
WHEN l_merged_rows( l_mrow_ix ).TRANSACTION_TYPE = G_TRANS_TYPE_UPDATE
OR l_candidate_trans = G_TRANS_TYPE_UPDATE THEN G_TRANS_TYPE_UPDATE
ELSE NULL -- INVALID transaction types encountered so far ...
END;
SELECT 'if l_merged_rows( l_mrow_ix ).' ||column_name || ' is null then l_merged_rows( l_mrow_ix ).' || column_name || ' := l_old_rows( orow_ix ).' || column_name || '; end if; '
, 'INTERFACE_TABLE_UNIQUE_ID' -- should be handled by INSERT trigger
-- who columns
, 'LAST_UPDATE_DATE'
, 'CREATION_DATE'
, 'CREATED_BY'
, 'LAST_UPDATED_BY'
, 'LAST_UPDATE_LOGIN'
-- XXX: exclude concurrent processing columns?
)
ORDER BY COLUMN_NAME ASC
*/
if l_merged_rows( l_mrow_ix ).ATTR_GROUP_TYPE is null then l_merged_rows( l_mrow_ix ).ATTR_GROUP_TYPE := l_old_rows( orow_ix ).ATTR_GROUP_TYPE; end if;
if l_merged_rows( l_mrow_ix ).PROGRAM_UPDATE_DATE is null then l_merged_rows( l_mrow_ix ).PROGRAM_UPDATE_DATE := l_old_rows( orow_ix ).PROGRAM_UPDATE_DATE; end if;
DELETE FROM EGO_ITM_USR_ATTR_INTRFC
WHERE ROWID = l_old_rowids( rid_ix );
Debug_Conc_Log( l_proc_log_prefix || 'Inserting ' || l_merged_rows.COUNT || ' merged rows ...' );
INSERT INTO EGO_ITM_USR_ATTR_INTRFC
VALUES l_merged_rows( row_index );
SELECT sub.*
,EXT.DATA_TYPE DATA_TYPE_CODE
FROM
( SELECT
eiuai.ROWID rid
, COUNT( * ) OVER ( PARTITION BY
ITEM_NUMBER
, ATTR_GROUP_INT_NAME
, ATTR_INT_NAME
, DATA_LEVEL_ID
, PK1_VALUE
, PK2_VALUE
, PK3_VALUE
, PK4_VALUE
, PK5_VALUE
, COALESCE ( eiuai.ORGANIZATION_ID
, ( SELECT ORGANIZATION_ID
FROM MTL_PARAMETERS p
WHERE p.ORGANIZATION_CODE = eiuai.ORGANIZATION_CODE
)
, NVL2( ORGANIZATION_CODE, cp_master_org_id, NULL )
)
, NVL( ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
)
cnt
, RANK() OVER ( ORDER BY
ITEM_NUMBER
, ATTR_GROUP_INT_NAME
, ATTR_INT_NAME
, DATA_LEVEL_ID
, PK1_VALUE
, PK2_VALUE
, PK3_VALUE
, PK4_VALUE
, PK5_VALUE
, COALESCE ( eiuai.ORGANIZATION_ID
, ( SELECT ORGANIZATION_ID
FROM MTL_PARAMETERS p
WHERE p.ORGANIZATION_CODE = eiuai.ORGANIZATION_CODE
)
, NVL2( ORGANIZATION_CODE, cp_master_org_id, NULL )
)
, NVL( ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
)
rnk
, eiuai.*
FROM EGO_ITM_USR_ATTR_INTRFC eiuai, EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 1
AND FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = NVL( EIUAI.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
AND FL_CTX_EXT.APPLICATION_ID = 431
AND FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = EIUAI.ATTR_GROUP_INT_NAME
AND FL_CTX_EXT.MULTI_ROW = 'N'
AND EXISTS( SELECT NULL
FROM EGO_ATTR_GROUP_DL A, EGO_DATA_LEVEL_B DL
WHERE DL.APPLICATION_ID = 431
AND DL.ATTR_GROUP_TYPE = FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME
AND DL.DATA_LEVEL_NAME IN ( 'ITEM_LEVEL' , 'ITEM_ORG', 'ITEM_SUP', 'ITEM_SUP_SITE', 'ITEM_SUP_SITE_ORG' )
AND DL.DATA_LEVEL_ID = DL.DATA_LEVEL_ID
AND A.ATTR_GROUP_ID = FL_CTX_EXT.ATTR_GROUP_ID
)
) sub
,FND_DESCR_FLEX_COLUMN_USAGES FL_COL
,EGO_FND_DF_COL_USGS_EXT EXT
WHERE sub.CNT > 1
AND FL_COL.APPLICATION_ID = 431
AND FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE = sub.ATTR_GROUP_INT_NAME
AND FL_COL.END_USER_COLUMN_NAME = sub.ATTR_INT_NAME
AND FL_COL.DESCRIPTIVE_FLEXFIELD_NAME = SUB.ATTR_GROUP_TYPE
AND EXT.APPLICATION_ID = FL_COL.APPLICATION_ID
AND EXT.DESCRIPTIVE_FLEXFIELD_NAME = FL_COL.DESCRIPTIVE_FLEXFIELD_NAME
AND EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND EXT.APPLICATION_COLUMN_NAME = FL_COL.APPLICATION_COLUMN_NAME
ORDER BY rnk, sub.last_update_date DESC, interface_table_unique_id DESC ;
SELECT sub.*
,EXT.DATA_TYPE DATA_TYPE_CODE
FROM
( SELECT
EIUAI.ROWID rid
, COUNT( * ) OVER ( PARTITION BY
SOURCE_SYSTEM_ID
, SOURCE_SYSTEM_REFERENCE
, ATTR_GROUP_INT_NAME
, ATTR_INT_NAME
, DATA_LEVEL_ID
, PK1_VALUE
, PK2_VALUE
, PK3_VALUE
, PK4_VALUE
, PK5_VALUE
, COALESCE ( eiuai.ORGANIZATION_ID
, ( SELECT ORGANIZATION_ID
FROM MTL_PARAMETERS p
WHERE p.ORGANIZATION_CODE = eiuai.ORGANIZATION_CODE
)
, NVL2( ORGANIZATION_CODE, cp_master_org_id, NULL )
)
, NVL( ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
)
cnt
, RANK() OVER ( ORDER BY
SOURCE_SYSTEM_ID
, SOURCE_SYSTEM_REFERENCE
, ATTR_GROUP_INT_NAME
, ATTR_INT_NAME
, DATA_LEVEL_ID
, PK1_VALUE
, PK2_VALUE
, PK3_VALUE
, PK4_VALUE
, PK5_VALUE
, COALESCE ( eiuai.ORGANIZATION_ID
, ( SELECT ORGANIZATION_ID
FROM MTL_PARAMETERS p
WHERE p.ORGANIZATION_CODE = eiuai.ORGANIZATION_CODE
)
, NVL2( ORGANIZATION_CODE, cp_master_org_id, NULL )
)
, NVL( ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
)
rnk
, eiuai.*
FROM EGO_ITM_USR_ATTR_INTRFC eiuai, EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 0
AND FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = NVL( EIUAI.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
AND FL_CTX_EXT.APPLICATION_ID = 431
AND FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = EIUAI.ATTR_GROUP_INT_NAME
AND FL_CTX_EXT.MULTI_ROW = 'N'
AND EXISTS( SELECT NULL
FROM EGO_ATTR_GROUP_DL A, EGO_DATA_LEVEL_B DL
WHERE DL.APPLICATION_ID = 431
AND DL.ATTR_GROUP_TYPE = FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME
AND DL.DATA_LEVEL_NAME IN ( 'ITEM_LEVEL' , 'ITEM_ORG', 'ITEM_SUP', 'ITEM_SUP_SITE', 'ITEM_SUP_SITE_ORG' )
AND DL.DATA_LEVEL_ID = DL.DATA_LEVEL_ID
AND A.ATTR_GROUP_ID = FL_CTX_EXT.ATTR_GROUP_ID
)
) sub
,FND_DESCR_FLEX_COLUMN_USAGES FL_COL
,EGO_FND_DF_COL_USGS_EXT EXT
WHERE sub.CNT > 1
AND FL_COL.APPLICATION_ID = 431
AND FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE = sub.ATTR_GROUP_INT_NAME
AND FL_COL.END_USER_COLUMN_NAME = sub.ATTR_INT_NAME
AND FL_COL.DESCRIPTIVE_FLEXFIELD_NAME = SUB.ATTR_GROUP_TYPE
AND EXT.APPLICATION_ID = FL_COL.APPLICATION_ID
AND EXT.DESCRIPTIVE_FLEXFIELD_NAME = FL_COL.DESCRIPTIVE_FLEXFIELD_NAME
AND EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND EXT.APPLICATION_COLUMN_NAME = FL_COL.APPLICATION_COLUMN_NAME
ORDER BY rnk, sub.last_update_date DESC, interface_table_unique_id DESC ;
WHEN l_merged_rows( l_mrow_ix ).TRANSACTION_TYPE = G_TRANS_TYPE_UPDATE
OR l_candidate_trans = G_TRANS_TYPE_UPDATE THEN G_TRANS_TYPE_UPDATE
ELSE NULL -- INVALID transaction types encountered so far ...
END;
SELECT 'if l_merged_rows( l_mrow_ix ).' ||column_name || ' is null then l_merged_rows( l_mrow_ix ).' || column_name || ' := l_old_rows( orow_ix ).' || column_name || '; end if; '
, 'INTERFACE_TABLE_UNIQUE_ID' -- should be handled by INSERT trigger
-- who columns
, 'LAST_UPDATE_DATE'
, 'CREATION_DATE'
, 'CREATED_BY'
, 'LAST_UPDATED_BY'
, 'LAST_UPDATE_LOGIN'
-- XXX: exclude concurrent processing columns?
)
ORDER BY COLUMN_NAME ASC
*/
if l_merged_rows( l_mrow_ix ).ATTR_GROUP_TYPE is null then l_merged_rows( l_mrow_ix ).ATTR_GROUP_TYPE := l_old_rows( orow_ix ).ATTR_GROUP_TYPE; end if;
if l_merged_rows( l_mrow_ix ).PROGRAM_UPDATE_DATE is null then l_merged_rows( l_mrow_ix ).PROGRAM_UPDATE_DATE := l_old_rows( orow_ix ).PROGRAM_UPDATE_DATE; end if;
DELETE FROM EGO_ITM_USR_ATTR_INTRFC
WHERE ROWID = l_old_rowids( rid_ix );
INSERT INTO EGO_ITM_USR_ATTR_INTRFC
VALUES l_merged_rows( row_index );
SELECT ROWID rid
, 0 cnt
, 0 rnk
, 'N' excluded_flag
, msii.*
FROM MTL_SYSTEM_ITEMS_INTERFACE msii;
SELECT *
FROM
( SELECT
ROWID rid,
COUNT( * ) OVER ( PARTITION BY ITEM_NUMBER
, ORGANIZATION_ID
)
cnt
, RANK() OVER ( ORDER BY ITEM_NUMBER
, ORGANIZATION_ID
)
rnk
, null EXCLUDED_FLAG
, msii.*
FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE PROCESS_FLAG = 1
AND SET_PROCESS_ID = p_batch_id
AND ( SOURCE_SYSTEM_ID IS NULL
OR SOURCE_SYSTEM_ID = G_PDH_SOURCE_SYSTEM_ID
)
AND ITEM_NUMBER IS NOT NULL
AND ORGANIZATION_ID IS NOT NULL
AND EXISTS ( SELECT NULL
FROM MTL_PARAMETERS mp
WHERE mp.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND mp.MASTER_ORGANIZATION_ID = l_org_id
)
)
sub
WHERE sub.cnt > 1
ORDER BY rnk, last_update_date DESC NULLS LAST, interface_table_unique_id DESC NULLS LAST;
SELECT *
FROM
( SELECT
ROWID rid,
COUNT( * ) OVER ( PARTITION BY SOURCE_SYSTEM_ID
, SOURCE_SYSTEM_REFERENCE
, ORGANIZATION_ID
)
cnt
, RANK() OVER ( ORDER BY SOURCE_SYSTEM_ID
, SOURCE_SYSTEM_REFERENCE
, ORGANIZATION_ID
)
rnk
, ( SELECT 'Y' FROM DUAL
WHERE EXISTS (
SELECT NULL FROM EGO_IMPORT_EXCLUDED_SS_ITEMS
WHERE SOURCE_SYSTEM_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE
AND SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
)
)
EXCLUDED_FLAG
, msii.*
FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE PROCESS_FLAG = 0
AND SET_PROCESS_ID = p_batch_id
AND SOURCE_SYSTEM_ID = l_ss_id
AND SOURCE_SYSTEM_REFERENCE IS NOT NULL
AND ORGANIZATION_ID IS NOT NULL
AND EXISTS ( SELECT NULL
FROM MTL_PARAMETERS mp
WHERE mp.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND mp.MASTER_ORGANIZATION_ID = l_org_id
)
)
sub
WHERE sub.cnt > 1
ORDER BY rnk, last_update_date DESC NULLS LAST, interface_table_unique_id DESC NULLS LAST;
WHEN l_merged_rows( l_mrow_ix ).TRANSACTION_TYPE = G_TRANS_TYPE_UPDATE
OR l_candidate_trans = G_TRANS_TYPE_UPDATE THEN G_TRANS_TYPE_UPDATE
ELSE NULL -- INVALID transaction types encountered so far ...
END;
SELECT 'if l_merged_rows( l_mrow_ix ).' ||column_name || ' is null then l_merged_rows( l_mrow_ix ).' || column_name || ' := old_row.' || column_name || '; end if; '
, 'INTERFACE_TABLE_UNIQUE_ID' -- handled by INSERT trigger
, 'CONFIRM_STATUS' -- should always be left null after a MERGE is performed
-- who columns
, 'LAST_UPDATE_DATE'
, 'CREATION_DATE'
, 'CREATED_BY'
, 'LAST_UPDATED_BY'
, 'LAST_UPDATE_LOGIN'
-- XXX: exclude concurrent processing columns?
)
order by column_name asc;
if l_merged_rows( l_mrow_ix ).ALLOW_ITEM_DESC_UPDATE_FLAG is null then l_merged_rows( l_mrow_ix ).ALLOW_ITEM_DESC_UPDATE_FLAG := old_row.ALLOW_ITEM_DESC_UPDATE_FLAG; end if;
if l_merged_rows( l_mrow_ix ).PROGRAM_UPDATE_DATE is null then l_merged_rows( l_mrow_ix ).PROGRAM_UPDATE_DATE := old_row.PROGRAM_UPDATE_DATE; end if;
if l_merged_rows( l_mrow_ix ).WH_UPDATE_DATE is null then l_merged_rows( l_mrow_ix ).WH_UPDATE_DATE := old_row.WH_UPDATE_DATE; end if;
DELETE FROM MTL_SYSTEM_ITEMS_INTERFACE
WHERE ROWID = l_old_rowids( rid_ix );
Debug_Conc_Log( l_proc_log_prefix || 'Inserting ' || l_merged_rows.COUNT || ' merged rows ...' );
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
VALUES l_merged_rows( row_index );
SELECT
rowid rid
, FIRST_VALUE( ROWID ) OVER ( PARTITION BY
source_system_id
, source_system_reference
, organization_id
, revision
ORDER BY
last_update_date desc nulls last
, interface_table_unique_id desc nulls last
) master_rid
, RANK( ) OVER ( PARTITION BY
source_system_id
, source_system_reference
, organization_id
, revision
ORDER BY
last_update_date desc nulls last
, interface_table_unique_id desc nulls last
) local_rank
, sub.*
FROM
( SELECT
COUNT( * ) OVER ( PARTITION BY
source_system_id
, source_system_reference
, organization_id
, revision
)
cnt
, miri.*
FROM mtl_item_revisions_interface miri
WHERE PROCESS_FLAG = 0
and SET_PROCESS_ID = p_batch_id
and SOURCE_SYSTEM_ID = cp_ss_id
and SOURCE_SYSTEM_REFERENCE IS NOT NULL
AND ORGANIZATION_ID IS NOT NULL
and EXISTS ( SELECT null
FROM mtl_parameters mp
WHERE mp.ORGANIZATION_ID = miri.ORGANIZATION_ID
and mp.MASTER_ORGANIZATION_ID = cp_master_org_id
)
)
sub
WHERE sub.cnt > 1
ORDER BY master_rid, local_rank;
SELECT
rowid rid
, first_value( rowid ) over ( PARTITION BY
item_number
, organization_id
, revision
ORDER BY
last_update_date desc nulls last
, interface_table_unique_id desc nulls last
) master_rid
, rank( ) over ( PARTITION BY
item_number
, organization_id
, revision
ORDER BY
last_update_date desc nulls last
, interface_table_unique_id desc nulls last
) local_rank
, sub.*
FROM
( select
count( * ) over ( PARTITION BY
item_number
, organization_id
, revision
)
cnt
, miri.*
FROM MTL_ITEM_REVISIONS_INTERFACE miri
WHERE PROCESS_FLAG = 1
AND SET_PROCESS_ID = p_batch_id
AND ITEM_NUMBER IS NOT NULL
AND ORGANIZATION_ID IS NOT NULL
AND ( SOURCE_SYSTEM_ID IS NULL
OR SOURCE_SYSTEM_ID = G_PDH_SOURCE_SYSTEM_ID
)
AND EXISTS ( SELECT null
FROM mtl_parameters mp
WHERE mp.organization_id = miri.organization_id
AND mp.master_organization_id = cp_master_org_id
)
)
sub
WHERE sub.cnt > 1
ORDER BY master_rid, local_rank;
UPDATE MTL_ITEM_REVISIONS_INTERFACE miri
SET REVISION = NVL( ( SELECT r.REVISION
FROM MTL_ITEM_REVISIONS_B r
WHERE r.REVISION_ID = miri.REVISION_ID
AND r.ORGANIZATION_ID = miri.ORGANIZATION_ID
)
, REVISION
)
WHERE REVISION_ID IS NOT NULL
AND SET_PROCESS_ID = p_batch_id
AND REVISION IS NULL;
WHEN l_merged_rows( l_mrow_ix ).TRANSACTION_TYPE = G_TRANS_TYPE_UPDATE
OR l_candidate_trans = G_TRANS_TYPE_UPDATE THEN G_TRANS_TYPE_UPDATE
ELSE NULL -- INVALID transaction types encountered ...
END;
SELECT 'if l_merged_rows( l_mrow_ix ).' ||column_name || ' is null then l_merged_rows( l_mrow_ix ).' || column_name || ' := l_old_rows( orow_ix ).' || column_name || '; end if; '
, 'LAST_UPDATE_DATE'
, 'CREATION_DATE'
, 'CREATED_BY'
, 'LAST_UPDATED_BY'
, 'LAST_UPDATE_LOGIN'
-- XXX: exclude concurrent processing columns?
)
ORDER BY COLUMN_NAME ASC
*/
if l_merged_rows( l_mrow_ix ).ATTRIBUTE1 is null then l_merged_rows( l_mrow_ix ).ATTRIBUTE1 := l_old_rows( orow_ix ).ATTRIBUTE1; end if;
if l_merged_rows( l_mrow_ix ).PROGRAM_UPDATE_DATE is null then l_merged_rows( l_mrow_ix ).PROGRAM_UPDATE_DATE := l_old_rows( orow_ix ).PROGRAM_UPDATE_DATE; end if;
DELETE FROM MTL_ITEM_REVISIONS_INTERFACE
WHERE ROWID = l_old_rowids( rid_ix );
Debug_Conc_Log( l_proc_log_prefix || 'Inserting ' || l_merged_rows.COUNT || ' merged rows ...' );
INSERT INTO MTL_ITEM_REVISIONS_INTERFACE
VALUES l_merged_rows( row_index );
SELECT *
FROM
(
SELECT
ROWID rid,
Count(*) OVER ( PARTITION BY ITEM_NUMBER,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
BUNDLE_ID,
DATA_LEVEL_ID,
ORGANIZATION_ID )
CNT,
Rank() OVER ( ORDER BY ITEM_NUMBER,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
BUNDLE_ID,
DATA_LEVEL_ID,
ORGANIZATION_ID )
RNK,
eiai.*
FROM EGO_ITEM_ASSOCIATIONS_INTF eiai
WHERE BATCH_ID = p_batch_id
AND PROCESS_FLAG = 1
)
WHERE CNT > 1
ORDER BY rnk, last_update_date DESC;
SELECT *
FROM
(
SELECT
ROWID rid,
Count(*) OVER ( PARTITION BY SOURCE_SYSTEM_ID,
SOURCE_SYSTEM_REFERENCE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
BUNDLE_ID,
DATA_LEVEL_ID,
ORGANIZATION_ID )
CNT,
Rank() OVER ( ORDER BY SOURCE_SYSTEM_ID,
SOURCE_SYSTEM_REFERENCE,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
BUNDLE_ID,
DATA_LEVEL_ID,
ORGANIZATION_ID )
RNK,
eiai.*
FROM EGO_ITEM_ASSOCIATIONS_INTF eiai
WHERE BATCH_ID = p_batch_id
AND PROCESS_FLAG = 0
)
WHERE CNT > 1
ORDER BY rnk, last_update_date DESC;
l_new_rows( l_new_row_index ).PROGRAM_UPDATE_DATE := l_old_rows( l_index ).PROGRAM_UPDATE_DATE;
l_new_rows( l_new_row_index ).LAST_UPDATED_BY := l_old_rows( l_index ).LAST_UPDATED_BY;
l_new_rows( l_new_row_index ).LAST_UPDATE_DATE := l_old_rows( l_index ).LAST_UPDATE_DATE;
l_new_rows( l_new_row_index ).LAST_UPDATE_LOGIN := l_old_rows( l_index ).LAST_UPDATE_LOGIN;
WHEN l_new_rows( l_new_row_index ).TRANSACTION_TYPE = G_TRANS_TYPE_UPDATE
OR l_current_merged_tran_type = G_TRANS_TYPE_UPDATE
THEN G_TRANS_TYPE_UPDATE
ELSE NULL -- Transaction type is not valid.
END;
UPDATE EGO_ITEM_ASSOCIATIONS_INTF
SET PRIMARY_FLAG = 'N'
WHERE PRIMARY_FLAG IS NULL
AND BATCH_ID = p_batch_id;
UPDATE EGO_ITEM_ASSOCIATIONS_INTF
SET PRIMARY_FLAG = 'N'
WHERE PRIMARY_FLAG IS NULL
AND BATCH_ID = p_batch_id;
DELETE FROM EGO_ITEM_ASSOCIATIONS_INTF
WHERE ROWID = l_old_row_ids( rid_ix );
INSERT INTO EGO_ITEM_ASSOCIATIONS_INTF
VALUES l_new_rows( row_index );
SELECT ROWID rid
, 0 cnt
, 0 rnk
, msii.*
FROM MTL_SYSTEM_ITEMS_INTERFACE msii;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET ITEM_NUMBER = (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = MSII.ORGANIZATION_ID)
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 1
AND INVENTORY_ITEM_ID IS NOT NULL
AND ITEM_NUMBER IS NULL;
SELECT *
FROM
(SELECT
ROWID rid,
COUNT( * ) OVER ( PARTITION BY ITEM_NUMBER, ORGANIZATION_ID) cnt,
RANK() OVER ( ORDER BY ITEM_NUMBER, ORGANIZATION_ID) rnk,
msii.*
FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE PROCESS_FLAG = 1
AND SET_PROCESS_ID = p_batch_id
AND ITEM_NUMBER IS NOT NULL
AND ORGANIZATION_ID IS NOT NULL
AND EXISTS
(SELECT NULL
FROM MTL_PARAMETERS mp
WHERE mp.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND mp.MASTER_ORGANIZATION_ID = l_org_id
)
) sub
WHERE sub.cnt > 1
ORDER BY rnk, last_update_date DESC NULLS LAST, interface_table_unique_id DESC NULLS LAST;
l_merged_rows( l_mrow_ix ).LAST_UPDATED_BY := l_new_rows( i ).LAST_UPDATED_BY;
l_merged_rows( l_mrow_ix ).PROGRAM_UPDATE_DATE := SYSDATE;
l_merged_rows( l_mrow_ix ).LAST_UPDATED_BY := l_new_rows( i ).LAST_UPDATED_BY;
l_merged_rows( l_mrow_ix ).PROGRAM_UPDATE_DATE := SYSDATE;
l_new_rows.DELETE;
l_new_rows( l_new_row_idx ).LAST_UPDATED_BY := old_row.LAST_UPDATED_BY;
WHEN l_merged_row.TRANSACTION_TYPE = G_TRANS_TYPE_UPDATE
OR l_candidate_trans = G_TRANS_TYPE_UPDATE THEN G_TRANS_TYPE_UPDATE
ELSE NULL -- INVALID transaction types encountered so far ...
END;
if l_merged_row.ALLOW_ITEM_DESC_UPDATE_FLAG is null then l_merged_row.ALLOW_ITEM_DESC_UPDATE_FLAG := old_row.ALLOW_ITEM_DESC_UPDATE_FLAG; end if;
if l_merged_row.WH_UPDATE_DATE is null then l_merged_row.WH_UPDATE_DATE := old_row.WH_UPDATE_DATE; end if;
DELETE FROM MTL_SYSTEM_ITEMS_INTERFACE
WHERE ROWID = l_old_rowids( rid_ix );
Debug_Conc_Log( l_proc_log_prefix || 'Inserting ' || l_merged_rows.COUNT || ' merged rows ...' );
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
VALUES l_merged_rows( row_index );
SELECT *
FROM
(SELECT
ROWID rid,
COUNT( * ) OVER ( PARTITION BY ITEM_NUMBER, ORGANIZATION_ID, REVISION ) cnt,
RANK() OVER ( ORDER BY ITEM_NUMBER, ORGANIZATION_ID, REVISION ) rnk,
miri.*
FROM MTL_ITEM_REVISIONS_INTERFACE miri
WHERE PROCESS_FLAG = 1
AND SET_PROCESS_ID = p_batch_id
AND ITEM_NUMBER IS NOT NULL
AND ORGANIZATION_ID IS NOT NULL
AND REVISION IS NOT NULL
AND EXISTS
(SELECT NULL
FROM MTL_PARAMETERS mp
WHERE mp.ORGANIZATION_ID = miri.ORGANIZATION_ID
AND mp.MASTER_ORGANIZATION_ID = p_master_org_id
)
) sub
WHERE sub.cnt > 1
ORDER BY rnk, last_update_date DESC NULLS LAST, interface_table_unique_id DESC NULLS LAST;
UPDATE MTL_ITEM_REVISIONS_INTERFACE miri
SET REVISION = NVL( ( SELECT r.REVISION
FROM MTL_ITEM_REVISIONS_B r
WHERE r.REVISION_ID = miri.REVISION_ID
AND r.ORGANIZATION_ID = miri.ORGANIZATION_ID
)
, REVISION
)
WHERE REVISION_ID IS NOT NULL
AND SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 1
AND REVISION IS NULL;
UPDATE MTL_ITEM_REVISIONS_INTERFACE miri
SET ITEM_NUMBER = (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = miri.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = miri.ORGANIZATION_ID)
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 1
AND INVENTORY_ITEM_ID IS NOT NULL
AND ITEM_NUMBER IS NULL;
l_merged_rows( l_mrow_ix ).LAST_UPDATED_BY := l_new_rows( i ).LAST_UPDATED_BY;
l_merged_rows( l_mrow_ix ).PROGRAM_UPDATE_DATE := SYSDATE;
l_new_rows.DELETE;
l_new_rows( l_new_row_idx ).LAST_UPDATED_BY := l_old_rows( idx ).LAST_UPDATED_BY;
WHEN l_merged_row.TRANSACTION_TYPE = G_TRANS_TYPE_UPDATE
OR l_candidate_trans = G_TRANS_TYPE_UPDATE THEN G_TRANS_TYPE_UPDATE
ELSE NULL -- INVALID transaction types encountered ...
END;
l_merged_rows( l_mrow_ix ).LAST_UPDATED_BY := l_new_rows( i ).LAST_UPDATED_BY;
l_merged_rows( l_mrow_ix ).PROGRAM_UPDATE_DATE := SYSDATE;
DELETE FROM MTL_ITEM_REVISIONS_INTERFACE
WHERE ROWID = l_old_rowids( rid_ix );
Debug_Conc_Log( l_proc_log_prefix || 'Inserting ' || l_merged_rows.COUNT || ' merged rows ...' );
INSERT INTO MTL_ITEM_REVISIONS_INTERFACE
VALUES l_merged_rows( row_index );
SELECT *
FROM
(SELECT
ROWID rid,
COUNT( * ) OVER ( PARTITION BY ITEM_NUMBER, ORGANIZATION_ID, CATEGORY_SET_NAME, CATEGORY_NAME ) cnt,
RANK() OVER ( ORDER BY ITEM_NUMBER, ORGANIZATION_ID, CATEGORY_SET_NAME, CATEGORY_NAME ) rnk,
mici.*
FROM MTL_ITEM_CATEGORIES_INTERFACE mici
WHERE PROCESS_FLAG = 1
AND SET_PROCESS_ID = p_batch_id
AND ITEM_NUMBER IS NOT NULL
AND ORGANIZATION_ID IS NOT NULL
AND CATEGORY_SET_NAME IS NOT NULL
AND CATEGORY_NAME IS NOT NULL
AND EXISTS
(SELECT NULL
FROM MTL_PARAMETERS mp
WHERE mp.ORGANIZATION_ID = mici.ORGANIZATION_ID
AND mp.MASTER_ORGANIZATION_ID = p_master_org_id
)
) sub
WHERE sub.cnt > 1
ORDER BY rnk, last_update_date DESC NULLS LAST;
UPDATE MTL_ITEM_CATEGORIES_INTERFACE mici
SET CATEGORY_SET_NAME = NVL( mici.CATEGORY_SET_NAME,
( SELECT mcs.CATEGORY_SET_NAME
FROM MTL_CATEGORY_SETS mcs
WHERE mcs.CATEGORY_SET_ID = mici.CATEGORY_SET_ID
)
),
CATEGORY_NAME = NVL( mici.CATEGORY_NAME,
( SELECT mc.CONCATENATED_SEGMENTS
FROM MTL_CATEGORIES_KFV mc
WHERE mc.CATEGORY_ID = mici.CATEGORY_ID
)
)
WHERE ( ( CATEGORY_NAME IS NULL AND CATEGORY_ID IS NOT NULL )
OR ( CATEGORY_SET_NAME IS NULL AND CATEGORY_SET_ID IS NOT NULL )
)
AND SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 1;
UPDATE MTL_ITEM_CATEGORIES_INTERFACE mici
SET ITEM_NUMBER = (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = mici.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = mici.ORGANIZATION_ID)
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 1
AND INVENTORY_ITEM_ID IS NOT NULL
AND ITEM_NUMBER IS NULL;
l_merged_rows( l_mrow_ix ).LAST_UPDATED_BY := l_new_rows( i ).LAST_UPDATED_BY;
l_merged_rows( l_mrow_ix ).PROGRAM_UPDATE_DATE := SYSDATE;
l_new_rows.DELETE;
l_new_rows( l_new_row_idx ).LAST_UPDATED_BY := l_old_rows( idx ).LAST_UPDATED_BY;
WHEN l_merged_row.TRANSACTION_TYPE = G_TRANS_TYPE_UPDATE
OR l_candidate_trans = G_TRANS_TYPE_UPDATE THEN G_TRANS_TYPE_UPDATE
ELSE NULL -- INVALID transaction types encountered ...
END;
l_merged_rows( l_mrow_ix ).LAST_UPDATED_BY := l_new_rows( i ).LAST_UPDATED_BY;
l_merged_rows( l_mrow_ix ).PROGRAM_UPDATE_DATE := SYSDATE;
DELETE FROM MTL_ITEM_CATEGORIES_INTERFACE
WHERE ROWID = l_old_rowids( rid_ix );
Debug_Conc_Log( l_proc_log_prefix || 'Inserting ' || l_merged_rows.COUNT || ' merged rows ...' );
INSERT INTO MTL_ITEM_CATEGORIES_INTERFACE
VALUES l_merged_rows( row_index );
SELECT sub.*
, attrs.DATA_TYPE_CODE
FROM
( SELECT
EIUAI.ROWID rid
, COUNT( * ) OVER ( PARTITION BY
ITEM_NUMBER
, ATTR_GROUP_INT_NAME
, ATTR_INT_NAME
, DATA_LEVEL_ID
, PK1_VALUE
, PK2_VALUE
, PK3_VALUE
, PK4_VALUE
, PK5_VALUE
, ORGANIZATION_ID
, NVL( ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
)
cnt
, RANK() OVER ( ORDER BY
ITEM_NUMBER
, ATTR_GROUP_INT_NAME
, ATTR_INT_NAME
, DATA_LEVEL_ID
, PK1_VALUE
, PK2_VALUE
, PK3_VALUE
, PK4_VALUE
, PK5_VALUE
, ORGANIZATION_ID
, NVL( ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
)
rnk
, eiuai.*
FROM EGO_ITM_USR_ATTR_INTRFC eiuai, EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 1
AND ITEM_NUMBER IS NOT NULL
AND ORGANIZATION_ID IS NOT NULL
AND FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = NVL( EIUAI.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
AND FL_CTX_EXT.APPLICATION_ID = 431
AND FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = EIUAI.ATTR_GROUP_INT_NAME
AND FL_CTX_EXT.MULTI_ROW = 'N'
AND EXISTS( SELECT NULL
FROM EGO_ATTR_GROUP_DL A, EGO_DATA_LEVEL_B DL
WHERE DL.APPLICATION_ID = 431
AND DL.ATTR_GROUP_TYPE = FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME
AND DL.DATA_LEVEL_NAME IN ( 'ITEM_LEVEL' , 'ITEM_ORG', 'ITEM_SUP', 'ITEM_SUP_SITE', 'ITEM_SUP_SITE_ORG' )
AND DL.DATA_LEVEL_ID = DL.DATA_LEVEL_ID
AND A.ATTR_GROUP_ID = FL_CTX_EXT.ATTR_GROUP_ID
)
) sub
, EGO_ATTRS_V attrs
WHERE sub.CNT > 1
AND attrs.APPLICATION_ID = 431
AND attrs.ATTR_GROUP_NAME = sub.ATTR_GROUP_INT_NAME
AND attrs.ATTR_NAME = sub.ATTR_INT_NAME
AND attrs.ATTR_GROUP_TYPE = NVL( sub.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
ORDER BY rnk, last_update_date DESC, interface_table_unique_id DESC;
UPDATE EGO_ITM_USR_ATTR_INTRFC eiuai
SET ITEM_NUMBER = (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = eiuai.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = eiuai.ORGANIZATION_ID)
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 1
AND INVENTORY_ITEM_ID IS NOT NULL
AND ITEM_NUMBER IS NULL;
l_merged_rows( l_mrow_ix ).LAST_UPDATED_BY := l_new_rows( i ).LAST_UPDATED_BY;
l_merged_rows( l_mrow_ix ).PROGRAM_UPDATE_DATE := SYSDATE;
l_new_rows.DELETE;
l_new_rows( l_new_row_idx ).LAST_UPDATED_BY := l_old_rows( idx ).LAST_UPDATED_BY;
WHEN l_merged_row.TRANSACTION_TYPE = G_TRANS_TYPE_UPDATE
OR l_candidate_trans = G_TRANS_TYPE_UPDATE THEN G_TRANS_TYPE_UPDATE
ELSE NULL -- INVALID transaction types encountered so far ...
END;
l_merged_rows( l_mrow_ix ).LAST_UPDATED_BY := l_new_rows( i ).LAST_UPDATED_BY;
l_merged_rows( l_mrow_ix ).PROGRAM_UPDATE_DATE := SYSDATE;
DELETE FROM EGO_ITM_USR_ATTR_INTRFC
WHERE ROWID = l_old_rowids( rid_ix );
INSERT INTO EGO_ITM_USR_ATTR_INTRFC
VALUES l_merged_rows( row_index );
SELECT sub.*
, attrs.DATA_TYPE_CODE
FROM
( SELECT
eiuai.ROWID rid
, COUNT( * ) OVER ( PARTITION BY
ITEM_NUMBER
, ATTR_GROUP_INT_NAME
, ATTR_INT_NAME
, ORGANIZATION_ID
, REVISION
, NVL( ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
)
cnt
, RANK() OVER ( ORDER BY
ITEM_NUMBER
, ATTR_GROUP_INT_NAME
, ATTR_INT_NAME
, ORGANIZATION_ID
, REVISION
, NVL( ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
)
rnk
, eiuai.*
FROM EGO_ITM_USR_ATTR_INTRFC eiuai, EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 1
AND ITEM_NUMBER IS NOT NULL
AND ORGANIZATION_ID IS NOT NULL
AND FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = NVL( EIUAI.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
AND FL_CTX_EXT.APPLICATION_ID = 431
AND FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = EIUAI.ATTR_GROUP_INT_NAME
AND FL_CTX_EXT.MULTI_ROW = 'N'
AND EXISTS( SELECT NULL
FROM EGO_ATTR_GROUP_DL A
WHERE A.DATA_LEVEL_ID = cp_rev_dl_id
AND A.ATTR_GROUP_ID = FL_CTX_EXT.ATTR_GROUP_ID
)
) sub
, EGO_ATTRS_V attrs
WHERE sub.CNT > 1
AND attrs.APPLICATION_ID = 431
AND attrs.ATTR_GROUP_NAME = sub.ATTR_GROUP_INT_NAME
AND attrs.ATTR_NAME = sub.ATTR_INT_NAME
AND attrs.ATTR_GROUP_TYPE = NVL( sub.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
ORDER BY rnk, last_update_date DESC, interface_table_unique_id DESC;
SELECT DATA_LEVEL_ID INTO l_rev_dl_id
FROM EGO_DATA_LEVEL_B
WHERE ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
AND APPLICATION_ID = 431
AND DATA_LEVEL_NAME = 'ITEM_REVISION_LEVEL';
UPDATE EGO_ITM_USR_ATTR_INTRFC eiuai
SET ITEM_NUMBER = (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = eiuai.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = eiuai.ORGANIZATION_ID)
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 1
AND INVENTORY_ITEM_ID IS NOT NULL
AND ITEM_NUMBER IS NULL;
l_merged_rows( l_mrow_ix ).LAST_UPDATED_BY := l_new_rows( i ).LAST_UPDATED_BY;
l_merged_rows( l_mrow_ix ).PROGRAM_UPDATE_DATE := SYSDATE;
l_new_rows.DELETE;
l_new_rows( l_new_row_idx ).LAST_UPDATED_BY := l_old_rows( idx ).LAST_UPDATED_BY;
WHEN l_merged_row.TRANSACTION_TYPE = G_TRANS_TYPE_UPDATE
OR l_candidate_trans = G_TRANS_TYPE_UPDATE THEN G_TRANS_TYPE_UPDATE
ELSE NULL -- INVALID transaction types encountered so far ...
END;
l_merged_rows( l_mrow_ix ).LAST_UPDATED_BY := l_new_rows( i ).LAST_UPDATED_BY;
l_merged_rows( l_mrow_ix ).PROGRAM_UPDATE_DATE := SYSDATE;
DELETE FROM EGO_ITM_USR_ATTR_INTRFC
WHERE ROWID = l_old_rowids( rid_ix );
INSERT INTO EGO_ITM_USR_ATTR_INTRFC
VALUES l_merged_rows( row_index );
* this will update the process flag of rows with process flag = 111
*/
PROCEDURE Demerge_Batch_After_Import(
ERRBUF OUT NOCOPY VARCHAR2
, RETCODE OUT NOCOPY VARCHAR2
, p_batch_id IN NUMBER
)
IS
l_proc_log_prefix VARCHAR2(50) := 'Demerge_Batch_After_Import - ';
UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
SET (PROCESS_FLAG, INVENTORY_ITEM_ID) =
(SELECT PROCESS_FLAG, INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_INTERFACE msii_merged
WHERE msii_merged.ITEM_NUMBER = msii.ITEM_NUMBER
AND msii_merged.ORGANIZATION_ID = msii.ORGANIZATION_ID
AND msii_merged.REQUEST_ID = msii.REQUEST_ID
AND msii_merged.SET_PROCESS_ID = msii.SET_PROCESS_ID
AND msii_merged.PROCESS_FLAG <> 111)
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 111;
UPDATE MTL_ITEM_REVISIONS_INTERFACE miri
SET (PROCESS_FLAG, INVENTORY_ITEM_ID) =
(SELECT PROCESS_FLAG, INVENTORY_ITEM_ID
FROM MTL_ITEM_REVISIONS_INTERFACE miri_merged
WHERE miri_merged.ITEM_NUMBER = miri.ITEM_NUMBER
AND miri_merged.ORGANIZATION_ID = miri.ORGANIZATION_ID
AND miri_merged.REVISION = miri.REVISION
AND miri_merged.REQUEST_ID = miri.REQUEST_ID
AND miri_merged.SET_PROCESS_ID = miri.SET_PROCESS_ID
AND miri_merged.PROCESS_FLAG <> 111)
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 111;
UPDATE MTL_ITEM_CATEGORIES_INTERFACE mici
SET (PROCESS_FLAG, INVENTORY_ITEM_ID) =
(SELECT PROCESS_FLAG, INVENTORY_ITEM_ID
FROM MTL_ITEM_CATEGORIES_INTERFACE mici_merged
WHERE mici_merged.ITEM_NUMBER = mici.ITEM_NUMBER
AND mici_merged.ORGANIZATION_ID = mici.ORGANIZATION_ID
AND mici_merged.CATEGORY_SET_NAME = mici.CATEGORY_SET_NAME
AND mici_merged.CATEGORY_NAME = mici.CATEGORY_NAME
AND mici_merged.REQUEST_ID = mici.REQUEST_ID
AND mici_merged.SET_PROCESS_ID = mici.SET_PROCESS_ID
AND mici_merged.PROCESS_FLAG <> 111)
WHERE SET_PROCESS_ID = p_batch_id
AND PROCESS_FLAG = 111;
UPDATE EGO_ITM_USR_ATTR_INTRFC eiuai
SET (PROCESS_STATUS, INVENTORY_ITEM_ID) =
(SELECT PROCESS_STATUS, INVENTORY_ITEM_ID
FROM EGO_ITM_USR_ATTR_INTRFC eiuai_merged
WHERE eiuai_merged.ITEM_NUMBER = eiuai.ITEM_NUMBER
AND eiuai_merged.ORGANIZATION_ID = eiuai.ORGANIZATION_ID
AND eiuai_merged.ATTR_GROUP_INT_NAME = eiuai.ATTR_GROUP_INT_NAME
AND eiuai_merged.ATTR_INT_NAME = eiuai.ATTR_INT_NAME
AND NVL(eiuai_merged.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP') = NVL(eiuai.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
AND eiuai_merged.REVISION = eiuai.REVISION
AND eiuai_merged.REQUEST_ID = eiuai.REQUEST_ID
AND eiuai_merged.DATA_SET_ID = eiuai.DATA_SET_ID
AND eiuai_merged.PROCESS_STATUS <> 7)
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 7
AND REVISION IS NOT NULL;
UPDATE EGO_ITM_USR_ATTR_INTRFC eiuai
SET (PROCESS_STATUS, INVENTORY_ITEM_ID) =
(SELECT PROCESS_STATUS, INVENTORY_ITEM_ID
FROM EGO_ITM_USR_ATTR_INTRFC eiuai_merged
WHERE eiuai_merged.ITEM_NUMBER = eiuai.ITEM_NUMBER
AND eiuai_merged.ORGANIZATION_ID = eiuai.ORGANIZATION_ID
AND eiuai_merged.ATTR_GROUP_INT_NAME = eiuai.ATTR_GROUP_INT_NAME
AND eiuai_merged.ATTR_INT_NAME = eiuai.ATTR_INT_NAME
AND Nvl(eiuai_merged.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP') = Nvl(eiuai.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
AND eiuai_merged.REVISION IS NULL
AND eiuai_merged.REQUEST_ID = eiuai.REQUEST_ID
AND eiuai_merged.DATA_SET_ID = eiuai.DATA_SET_ID
AND eiuai_merged.PROCESS_STATUS <> 7)
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 7
AND REVISION IS NULL;
SELECT
intf.INVENTORY_ITEM_ID,
intf.ORGANIZATION_ID,
intf.ITEM_NUMBER,
intf.ORGANIZATION_CODE,
intf.PRIMARY_UOM_CODE,
intf.GLOBAL_TRADE_ITEM_NUMBER,
intf.GTIN_DESCRIPTION,
intf.TRANSACTION_ID,
c.CROSS_REFERENCE EXISTING_GTIN,
c.DESCRIPTION EXISTING_GTIN_DESC,
c.CROSS_REFERENCE_ID
FROM MTL_SYSTEM_ITEMS_INTERFACE intf, MTL_PARAMETERS p, MTL_CROSS_REFERENCES c
WHERE intf.SET_PROCESS_ID = p_data_set_id
AND intf.ORGANIZATION_ID = p.ORGANIZATION_ID
AND p.ORGANIZATION_ID = p.MASTER_ORGANIZATION_ID
AND intf.PROCESS_FLAG IN (5,7)
AND intf.GLOBAL_TRADE_ITEM_NUMBER IS NOT NULL
AND c.INVENTORY_ITEM_ID(+) = intf.INVENTORY_ITEM_ID
AND c.UOM_CODE(+) = intf.PRIMARY_UOM_CODE
AND intf.REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID
AND c.CROSS_REFERENCE_TYPE(+) = 'GTIN';
SELECT CONCATENATED_SEGMENTS INTO l_existing_gtin
FROM MTL_SYSTEM_ITEMS_KFV msik, MTL_CROSS_REFERENCES_B mcr, MTL_PARAMETERS mp
WHERE msik.INVENTORY_ITEM_ID = mcr.INVENTORY_ITEM_ID
AND msik.ORGANIZATION_ID = mp.ORGANIZATION_ID
AND mp.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID
AND mcr.CROSS_REFERENCE_TYPE = 'GTIN'
AND mcr.CROSS_REFERENCE = i.GLOBAL_TRADE_ITEM_NUMBER
AND mcr.UOM_CODE = msik.PRIMARY_UOM_CODE
AND ROWNUM = 1;
MTL_CROSS_REFERENCES_PKG.INSERT_ROW(
P_SOURCE_SYSTEM_ID => NULL,
P_START_DATE_ACTIVE => NULL,
P_END_DATE_ACTIVE => NULL,
P_OBJECT_VERSION_NUMBER => NULL,
P_UOM_CODE => i.PRIMARY_UOM_CODE,
P_REVISION_ID => NULL,
P_EPC_GTIN_SERIAL => NULL,
P_INVENTORY_ITEM_ID => i.INVENTORY_ITEM_ID,
P_ORGANIZATION_ID => NULL,
P_CROSS_REFERENCE_TYPE => 'GTIN',
P_CROSS_REFERENCE => i.GLOBAL_TRADE_ITEM_NUMBER,
P_ORG_INDEPENDENT_FLAG => 'Y',
P_REQUEST_ID => l_request_id,
P_ATTRIBUTE1 => NULL,
P_ATTRIBUTE2 => NULL,
P_ATTRIBUTE3 => NULL,
P_ATTRIBUTE4 => NULL,
P_ATTRIBUTE5 => NULL,
P_ATTRIBUTE6 => NULL,
P_ATTRIBUTE7 => NULL,
P_ATTRIBUTE8 => NULL,
P_ATTRIBUTE9 => NULL,
P_ATTRIBUTE10 => NULL,
P_ATTRIBUTE11 => NULL,
P_ATTRIBUTE12 => NULL,
P_ATTRIBUTE13 => NULL,
P_ATTRIBUTE14 => NULL,
P_ATTRIBUTE15 => NULL,
P_ATTRIBUTE_CATEGORY => NULL,
P_DESCRIPTION => i.GTIN_DESCRIPTION,
P_CREATION_DATE => SYSDATE,
P_CREATED_BY => l_user_id,
P_LAST_UPDATE_DATE => SYSDATE,
P_LAST_UPDATED_BY => l_user_id,
P_LAST_UPDATE_LOGIN => l_login_id,
P_PROGRAM_APPLICATION_ID => l_prog_appid,
P_PROGRAM_ID => l_prog_id,
P_PROGRAM_UPDATE_DATE => SYSDATE,
X_CROSS_REFERENCE_ID => l_xref_id);
Debug_Conc_Log('Existing GTIN found. Description needs to be updated.');
UPDATE MTL_CROSS_REFERENCES_TL
SET DESCRIPTION = i.GTIN_DESCRIPTION,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = l_login_id,
SOURCE_LANG = USERENV('LANG')
WHERE CROSS_REFERENCE_ID = i.CROSS_REFERENCE_ID
AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
Debug_Conc_Log('Description updated for GTIN='||i.EXISTING_GTIN);
Debug_Conc_Log('GTIN can not be updated');
FND_MESSAGE.Set_Name('EGO', 'EGO_GTIN_NOT_UPDATEABLE');
SELECT ROWID, INVENTORY_ITEM_ID INTO l_rowid, l_item_id
FROM MTL_CROSS_REFERENCES_B mcr
WHERE mcr.SOURCE_SYSTEM_ID = p_source_system_id
AND mcr.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND mcr.CROSS_REFERENCE = p_source_system_reference
AND (mcr.END_DATE_ACTIVE IS NULL OR mcr.END_DATE_ACTIVE > SYSDATE);
UPDATE MTL_CROSS_REFERENCES_B
SET
END_DATE_ACTIVE = SYSDATE,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = l_login_id,
PROGRAM_APPLICATION_ID = l_prog_appid,
PROGRAM_ID = l_prog_id,
REQUEST_ID = l_request_id,
PROGRAM_UPDATE_DATE = SYSDATE
WHERE ROWID = l_rowid;
MTL_CROSS_REFERENCES_PKG.INSERT_ROW(
P_SOURCE_SYSTEM_ID => p_source_system_id,
P_START_DATE_ACTIVE => SYSDATE,
P_END_DATE_ACTIVE => NULL,
P_OBJECT_VERSION_NUMBER => NULL,
P_UOM_CODE => NULL,
P_REVISION_ID => NULL,
P_EPC_GTIN_SERIAL => NULL,
P_INVENTORY_ITEM_ID => p_inventory_item_id,
P_ORGANIZATION_ID => NULL,
P_CROSS_REFERENCE_TYPE => 'SS_ITEM_XREF',
P_CROSS_REFERENCE => p_source_system_reference,
P_ORG_INDEPENDENT_FLAG => 'Y',
P_REQUEST_ID => l_request_id,
P_ATTRIBUTE1 => NULL,
P_ATTRIBUTE2 => NULL,
P_ATTRIBUTE3 => NULL,
P_ATTRIBUTE4 => NULL,
P_ATTRIBUTE5 => NULL,
P_ATTRIBUTE6 => NULL,
P_ATTRIBUTE7 => NULL,
P_ATTRIBUTE8 => NULL,
P_ATTRIBUTE9 => NULL,
P_ATTRIBUTE10 => NULL,
P_ATTRIBUTE11 => NULL,
P_ATTRIBUTE12 => NULL,
P_ATTRIBUTE13 => NULL,
P_ATTRIBUTE14 => NULL,
P_ATTRIBUTE15 => NULL,
P_ATTRIBUTE_CATEGORY => NULL,
P_DESCRIPTION => p_source_system_reference_desc,
P_CREATION_DATE => SYSDATE,
P_CREATED_BY => l_user_id,
P_LAST_UPDATE_DATE => SYSDATE,
P_LAST_UPDATED_BY => l_user_id,
P_LAST_UPDATE_LOGIN => l_login_id,
P_PROGRAM_APPLICATION_ID => l_prog_appid,
P_PROGRAM_ID => l_prog_id,
P_PROGRAM_UPDATE_DATE => SYSDATE,
X_CROSS_REFERENCE_ID => l_xref_id);
SELECT
SOURCE_SYSTEM_ID,
SOURCE_SYSTEM_REFERENCE,
SOURCE_SYSTEM_REFERENCE_DESC,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
TRANSACTION_ID,
TRANSACTION_TYPE
FROM MTL_SYSTEM_ITEMS_INTERFACE msi
WHERE msi.PROCESS_FLAG IN (5, 7)
AND msi.SET_PROCESS_ID = p_data_set_id
AND msi.SOURCE_SYSTEM_ID IS NOT NULL
AND msi.SOURCE_SYSTEM_REFERENCE IS NOT NULL
AND msi.REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID
AND NOT EXISTS (SELECT NULL
FROM MTL_CROSS_REFERENCES_B mcr
WHERE mcr.SOURCE_SYSTEM_ID = msi.SOURCE_SYSTEM_ID
AND mcr.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND mcr.CROSS_REFERENCE = msi.SOURCE_SYSTEM_REFERENCE
AND mcr.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID
AND (mcr.END_DATE_ACTIVE IS NULL OR mcr.END_DATE_ACTIVE > SYSDATE));
SELECT
msi.SOURCE_SYSTEM_ID,
msi.SOURCE_SYSTEM_REFERENCE,
msi.SOURCE_SYSTEM_REFERENCE_DESC,
msi.INVENTORY_ITEM_ID,
msi.ORGANIZATION_ID,
msi.TRANSACTION_ID,
msi.TRANSACTION_TYPE,
mcr.CROSS_REFERENCE_ID
FROM MTL_SYSTEM_ITEMS_INTERFACE msi, MTL_CROSS_REFERENCES mcr
WHERE msi.PROCESS_FLAG IN (5, 7)
AND msi.SET_PROCESS_ID = p_data_set_id
AND msi.SOURCE_SYSTEM_ID IS NOT NULL
AND msi.SOURCE_SYSTEM_REFERENCE IS NOT NULL
AND msi.REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID
AND mcr.SOURCE_SYSTEM_ID = msi.SOURCE_SYSTEM_ID
AND mcr.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND mcr.CROSS_REFERENCE = msi.SOURCE_SYSTEM_REFERENCE
AND mcr.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID
AND msi.SOURCE_SYSTEM_REFERENCE_DESC IS NOT NULL
AND NVL(mcr.DESCRIPTION, msi.SOURCE_SYSTEM_REFERENCE_DESC||'##') <> msi.SOURCE_SYSTEM_REFERENCE_DESC
AND UPPER(msi.TRANSACTION_TYPE) <> 'CREATE'
AND (mcr.END_DATE_ACTIVE IS NULL OR mcr.END_DATE_ACTIVE > SYSDATE);
SELECT
SOURCE_SYSTEM_ID,
SOURCE_SYSTEM_REFERENCE,
SOURCE_SYSTEM_REFERENCE_DESC,
INVENTORY_ITEM_ID,
ITEM_NUMBER,
ORGANIZATION_ID,
TRANSACTION_ID,
TRANSACTION_TYPE,
ROWID AS ROW_ID
FROM MTL_SYSTEM_ITEMS_INTERFACE msi
WHERE msi.PROCESS_FLAG IN (0, 11)
AND msi.SET_PROCESS_ID = p_data_set_id
AND msi.SOURCE_SYSTEM_ID IS NOT NULL
AND msi.SOURCE_SYSTEM_REFERENCE IS NOT NULL
AND msi.CONFIRM_STATUS IN ('CM', 'CC')
AND NOT EXISTS (SELECT NULL
FROM MTL_CROSS_REFERENCES_B mcr
WHERE mcr.SOURCE_SYSTEM_ID = msi.SOURCE_SYSTEM_ID
AND mcr.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND mcr.CROSS_REFERENCE = msi.SOURCE_SYSTEM_REFERENCE
AND mcr.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID
AND (mcr.END_DATE_ACTIVE IS NULL OR mcr.END_DATE_ACTIVE > SYSDATE));
SELECT
msi.SOURCE_SYSTEM_ID,
msi.SOURCE_SYSTEM_REFERENCE,
msi.SOURCE_SYSTEM_REFERENCE_DESC,
msi.INVENTORY_ITEM_ID,
msi.ITEM_NUMBER,
msi.ORGANIZATION_ID,
msi.TRANSACTION_ID,
msi.TRANSACTION_TYPE,
mcr.CROSS_REFERENCE_ID,
msi.ROWID AS ROW_ID
FROM MTL_SYSTEM_ITEMS_INTERFACE msi, MTL_CROSS_REFERENCES mcr
WHERE msi.PROCESS_FLAG IN (0, 11)
AND msi.SET_PROCESS_ID = p_data_set_id
AND msi.SOURCE_SYSTEM_ID IS NOT NULL
AND msi.SOURCE_SYSTEM_REFERENCE IS NOT NULL
AND msi.CONFIRM_STATUS IN ('CM', 'CC')
AND mcr.SOURCE_SYSTEM_ID = msi.SOURCE_SYSTEM_ID
AND mcr.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND mcr.CROSS_REFERENCE = msi.SOURCE_SYSTEM_REFERENCE
AND mcr.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID
AND msi.SOURCE_SYSTEM_REFERENCE_DESC IS NOT NULL
AND NVL(mcr.DESCRIPTION, msi.SOURCE_SYSTEM_REFERENCE_DESC||'##') <> msi.SOURCE_SYSTEM_REFERENCE_DESC
AND UPPER(msi.TRANSACTION_TYPE) <> 'CREATE'
AND (mcr.END_DATE_ACTIVE IS NULL OR mcr.END_DATE_ACTIVE > SYSDATE);
SELECT b.SOURCE_SYSTEM_ID, NVL(opt.IMPORT_XREF_ONLY, 'N')
INTO l_ss_id, l_import_xref_only
FROM EGO_IMPORT_BATCHES_B b, EGO_IMPORT_OPTION_SETS opt
WHERE b.BATCH_ID = p_data_set_id
AND b.BATCH_ID = opt.BATCH_ID;
UPDATE MTL_CROSS_REFERENCES_TL
SET DESCRIPTION = i.SOURCE_SYSTEM_REFERENCE_DESC,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
SOURCE_LANG = USERENV('LANG')
WHERE CROSS_REFERENCE_ID = i.CROSS_REFERENCE_ID
AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET PROCESS_FLAG = 7,
TRANSACTION_TYPE = 'UPDATE',
PROGRAM_APPLICATION_ID = l_prog_appid,
PROGRAM_ID = l_prog_id,
REQUEST_ID = l_request_id,
PROGRAM_UPDATE_DATE = SYSDATE
WHERE ROWID = i.ROW_ID;
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET PROCESS_STATUS = 4,
PROGRAM_APPLICATION_ID = l_prog_appid,
PROGRAM_ID = l_prog_id,
REQUEST_ID = l_request_id,
PROGRAM_UPDATE_DATE = SYSDATE
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = 0
AND SOURCE_SYSTEM_ID = i.SOURCE_SYSTEM_ID
AND SOURCE_SYSTEM_REFERENCE = i.SOURCE_SYSTEM_REFERENCE;
UPDATE EGO_AML_INTF
SET PROCESS_FLAG = 7,
PROGRAM_APPLICATION_ID = l_prog_appid,
PROGRAM_ID = l_prog_id,
REQUEST_ID = l_request_id,
PROGRAM_UPDATE_DATE = SYSDATE
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_FLAG = 0
AND SOURCE_SYSTEM_ID = i.SOURCE_SYSTEM_ID
AND SOURCE_SYSTEM_REFERENCE = i.SOURCE_SYSTEM_REFERENCE;
SELECT INVENTORY_ITEM_ID INTO l_inventory_item_id
FROM MTL_SYSTEM_ITEMS_B_KFV
WHERE CONCATENATED_SEGMENTS = i.ITEM_NUMBER
AND ORGANIZATION_ID = i.ORGANIZATION_ID;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET PROCESS_FLAG = 7,
TRANSACTION_TYPE = 'UPDATE',
PROGRAM_APPLICATION_ID = l_prog_appid,
PROGRAM_ID = l_prog_id,
REQUEST_ID = l_request_id,
PROGRAM_UPDATE_DATE = SYSDATE
WHERE ROWID = i.ROW_ID;
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET PROCESS_STATUS = 4,
PROGRAM_APPLICATION_ID = l_prog_appid,
PROGRAM_ID = l_prog_id,
REQUEST_ID = l_request_id,
PROGRAM_UPDATE_DATE = SYSDATE
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = 0
AND SOURCE_SYSTEM_ID = i.SOURCE_SYSTEM_ID
AND SOURCE_SYSTEM_REFERENCE = i.SOURCE_SYSTEM_REFERENCE;
UPDATE EGO_AML_INTF
SET PROCESS_FLAG = 7,
PROGRAM_APPLICATION_ID = l_prog_appid,
PROGRAM_ID = l_prog_id,
REQUEST_ID = l_request_id,
PROGRAM_UPDATE_DATE = SYSDATE
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_FLAG = 0
AND SOURCE_SYSTEM_ID = i.SOURCE_SYSTEM_ID
AND SOURCE_SYSTEM_REFERENCE = i.SOURCE_SYSTEM_REFERENCE;
UPDATE MTL_CROSS_REFERENCES_TL
SET DESCRIPTION = i.SOURCE_SYSTEM_REFERENCE_DESC,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
SOURCE_LANG = USERENV('LANG')
WHERE CROSS_REFERENCE_ID = i.CROSS_REFERENCE_ID
AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
SELECT
MP.ORGANIZATION_ID,
MP.ORGANIZATION_CODE
INTO l_org_id, l_org_code
FROM MTL_PARAMETERS MP,
EGO_IMPORT_BATCHES_B BA
WHERE BA.ORGANIZATION_ID = MP.ORGANIZATION_ID AND
BA.BATCH_ID = p_data_set_id;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET PROCESS_FLAG = 0,
ORGANIZATION_ID = l_org_id,
ORGANIZATION_CODE = l_org_code,
INVENTORY_ITEM_ID = p_inventory_item_id,
ITEM_NUMBER = ( SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = p_inventory_item_id AND ORGANIZATION_ID = p_organization_id
),
CONFIRM_STATUS =
CASE
WHEN CONFIRM_STATUS IN ( G_CONF_XREF_FAKE, G_CONF_MATCH_FAKE, G_FAKE_CONF_STATUS_FLAG
, G_UNCONF_NO_MATCH_FAKE, G_UNCONF_MULTI_MATCH_FAKE, G_UNCONF_SINGLE_MATCH_FAKE )
THEN ( CASE p_status
WHEN G_CONF_XREF THEN G_CONF_XREF_FAKE
WHEN G_CONF_MATCH THEN G_CONF_MATCH_FAKE
WHEN G_UNCONF_SIGL_MATCH THEN G_UNCONF_SINGLE_MATCH_FAKE
WHEN G_UNCONF_MULT_MATCH THEN G_UNCONF_MULTI_MATCH_FAKE
WHEN G_UNCONF_NONE_MATCH THEN G_UNCONF_NO_MATCH_FAKE
ELSE p_status
END
)
ELSE p_status
END
WHERE
SET_PROCESS_ID = p_data_set_id AND
SOURCE_SYSTEM_ID = p_source_system_id AND
SOURCE_SYSTEM_REFERENCE = p_source_system_reference AND
(PROCESS_FLAG = 0 OR PROCESS_FLAG IS NULL) AND
( (ORGANIZATION_ID IS NULL AND ORGANIZATION_CODE IS NULL) OR
ORGANIZATION_ID = l_org_id OR
(ORGANIZATION_ID IS NULL AND ORGANIZATION_CODE = l_org_code)
);
SELECT 'x' INTO l_temp
FROM MTL_ITEM_REVISIONS_INTERFACE MIRI
WHERE SET_PROCESS_ID = p_batch_id AND
PROCESS_FLAG = 0 AND
SOURCE_SYSTEM_ID = p_source_system_id AND
SOURCE_SYSTEM_REFERENCE = p_source_system_reference AND
(ORGANIZATION_ID = p_organization_id OR ORGANIZATION_CODE = p_organization_code) AND
REVISION IS NOT NULL AND
Upper(REVISION) NOT IN ( SELECT B.REVISION FROM MTL_ITEM_REVISIONS_B B, MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.SOURCE_SYSTEM_REFERENCE = MIRI.SOURCE_SYSTEM_REFERENCE
AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
AND (MSII.ORGANIZATION_ID = MIRI.ORGANIZATION_ID
OR MSII.ORGANIZATION_CODE = MIRI.ORGANIZATION_CODE)
AND MSII.SET_PROCESS_ID = MIRI.SET_PROCESS_ID
AND MSII.PROCESS_FLAG = 0
AND B.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
) AND
EFFECTIVITY_DATE IS NOT NULL AND
UPPER(TRANSACTION_TYPE) = 'CREATE' AND
ROWNUM < 2;
SELECT MSII.SOURCE_SYSTEM_ID,
MSII.SOURCE_SYSTEM_REFERENCE,
MSII.SET_PROCESS_ID,
MSII.BUNDLE_ID
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.PROCESS_FLAG = 0
AND MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.BUNDLE_ID = p_bundle_id;
SELECT NVL(ENABLED_FOR_DATA_POOL, 'N') INTO l_is_batch_GDSN_enabled
FROM EGO_IMPORT_OPTION_SETS
WHERE BATCH_ID = p_data_set_id;
SELECT
MP.ORGANIZATION_ID,
MP.ORGANIZATION_CODE,
NVL(OPT.REVISION_IMPORT_POLICY, 'L')
INTO l_organization_id, l_organization_code, l_batch_rev_policy
FROM MTL_PARAMETERS MP,
EGO_IMPORT_BATCHES_B ba,
EGO_IMPORT_OPTION_SETS OPT
WHERE BA.ORGANIZATION_ID = MP.ORGANIZATION_ID AND
BA.BATCH_ID = OPT.BATCH_ID AND
BA.BATCH_ID = p_data_set_id;
SELECT ITEM_NUMBER,
NVL(DESCRIPTION,
( SELECT ATTRIBUTE_VALUE
FROM MTL_ITEM_TEMPLATES_VL TEMP, MTL_ITEM_TEMPL_ATTRIBUTES ATTR
WHERE ( (MSII.TEMPLATE_ID IS NOT NULL AND TEMP.TEMPLATE_ID = MSII.TEMPLATE_ID)
OR (MSII.TEMPLATE_ID IS NULL AND TEMP.TEMPLATE_NAME = MSII.TEMPLATE_NAME)
) AND
(TEMP.CONTEXT_ORGANIZATION_ID = MSII.ORGANIZATION_ID OR TEMP.CONTEXT_ORGANIZATION_ID IS NULL) AND
TEMP.TEMPLATE_ID = ATTR.TEMPLATE_ID AND
ATTR.ENABLED_FLAG = 'Y' AND
ATTR.ATTRIBUTE_NAME LIKE 'MTL_SYSTEM_ITEMS.DESCRIPTION')),
NVL(ITEM_CATALOG_GROUP_ID,
( SELECT MICG.ITEM_CATALOG_GROUP_ID
FROM MTL_ITEM_CATALOG_GROUPS_B_KFV MICG
WHERE MICG.CONCATENATED_SEGMENTS = MSII.ITEM_CATALOG_GROUP_NAME)),
CONFIRM_STATUS,
NVL(REVISION_IMPORT_POLICY, l_batch_rev_policy),
MSII.STYLE_ITEM_FLAG
INTO l_pdh_item_number, l_pdh_description, l_pdh_item_catalog_group_id, l_confirm_status, l_instance_rev_policy, l_style_item_flag
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII, EGO_IMPORT_BATCHES_B BATCH
WHERE MSII.SET_PROCESS_ID = p_data_set_id AND
MSII.SOURCE_SYSTEM_ID = p_source_system_id AND
MSII.SOURCE_SYSTEM_REFERENCE = p_source_system_reference AND
MSII.ORGANIZATION_ID = BATCH.ORGANIZATION_ID AND
BATCH.BATCH_ID = p_data_set_id AND
PROCESS_FLAG = 0 AND
( ( p_bundle_id IS NULL AND MSII.BUNDLE_ID IS NULL ) --R12C:
OR MSII.BUNDLE_ID = p_bundle_id
) AND
ROWNUM = 1;
SELECT
DECODE(ICC.ITEM_NUM_GEN_METHOD,
NULL, DECODE(ICC.PARENT_CATALOG_GROUP_ID, NULL, 'U', 'I'),
ICC.ITEM_NUM_GEN_METHOD),
DECODE(ICC.ITEM_DESC_GEN_METHOD,
NULL, DECODE(ICC.PARENT_CATALOG_GROUP_ID, NULL, 'U', 'I'),
ICC.ITEM_DESC_GEN_METHOD)
FROM MTL_ITEM_CATALOG_GROUPS_B ICC
CONNECT BY PRIOR ICC.PARENT_CATALOG_GROUP_ID = ICC.ITEM_CATALOG_GROUP_ID
START WITH ICC.ITEM_CATALOG_GROUP_ID = p_item_catalog_group_id;
SELECT rowid rid
FROM mtl_system_items_interface
WHERE set_process_id = p_set_id
AND process_flag = 0
AND organization_id /*bug 6402904 parse for child items also*/
IN (SELECT organization_id
FROM mtl_parameters mp
WHERE mp.master_organization_id = p_master_org_id)
AND ( confirm_status IS NULL
OR confirm_status = G_FAKE_CONF_STATUS_FLAG
)
AND item_number IS null
FOR UPDATE OF item_number;
SELECT rowid rid
FROM mtl_system_items_interface
WHERE set_process_id = p_set_id
AND process_flag = 1
AND item_number IS null
FOR UPDATE OF item_number;
* resolve the SYNC transaction_type into 'CREATE' or 'UPDATE'
* using the same logic as INVPOPIB.pls for a PDH batch
*/
PROCEDURE UPDATE_ITEM_SYNC_RECORDS_PDH
(p_set_id IN NUMBER,
p_org_id IN NUMBER
) IS
CURSOR c_items_table IS
SELECT rowid
, organization_id
, inventory_item_id
, item_number
, transaction_id
, transaction_type
FROM mtl_system_items_interface
WHERE set_process_id = p_set_id
AND organization_id = p_org_id
AND process_flag = 1
AND UPPER(transaction_type) = 'SYNC'
FOR UPDATE OF transaction_type;
SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = cp_item_id;
UPDATE mtl_system_items_interface
SET transaction_type = 'UPDATE'
WHERE rowid = item_record.rowid;
UPDATE mtl_system_items_interface
SET transaction_type = 'CREATE', inventory_item_id = NULL
WHERE rowid = item_record.rowid;
END UPDATE_ITEM_SYNC_RECORDS_PDH;
l_insert_date DATE;
SELECT
MP.ORGANIZATION_ID,
MP.ORGANIZATION_CODE,
BA.SOURCE_SYSTEM_ID,
NVL(OPT.IMPORT_XREF_ONLY, 'N'),
NVL(ENABLED_FOR_DATA_POOL, 'N')
INTO l_org_id, l_org_code, l_ss_id, l_import_xref_only, l_enabled_for_data_pool
FROM MTL_PARAMETERS MP,
EGO_IMPORT_BATCHES_B BA,
EGO_IMPORT_OPTION_SETS OPT
WHERE BA.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND BA.BATCH_ID = p_data_set_id
AND BA.BATCH_ID = OPT.BATCH_ID;
SELECT mp.MASTER_ORGANIZATION_ID, mp1.ORGANIZATION_CODE
INTO l_org_id, l_org_code
FROM MTL_PARAMETERS mp, MTL_PARAMETERS mp1
WHERE mp.ORGANIZATION_ID = TO_NUMBER(l_org)
AND mp.MASTER_ORGANIZATION_ID = mp1.ORGANIZATION_ID;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET ORGANIZATION_ID =
CASE
WHEN ORGANIZATION_CODE IS NOT NULL
THEN (SELECT ORGANIZATION_ID FROM MTL_PARAMETERS WHERE ORGANIZATION_CODE = MSII.ORGANIZATION_CODE)
ELSE l_org_id
END
WHERE SET_PROCESS_ID = p_data_set_id AND
(PROCESS_FLAG IS NULL OR PROCESS_FLAG IN (0, 1)) AND
ORGANIZATION_ID IS NULL;
UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI
SET ORGANIZATION_ID =
CASE
WHEN ORGANIZATION_CODE IS NOT NULL
THEN (SELECT ORGANIZATION_ID FROM MTL_PARAMETERS WHERE ORGANIZATION_CODE = MIRI.ORGANIZATION_CODE)
ELSE l_org_id
END
WHERE SET_PROCESS_ID = p_data_set_id AND
(PROCESS_FLAG IS NULL OR PROCESS_FLAG IN (0, 1)) AND
ORGANIZATION_ID IS NULL;
UPDATE MTL_ITEM_CATEGORIES_INTERFACE MICI
SET ORGANIZATION_ID =
CASE
WHEN ORGANIZATION_CODE IS NOT NULL
THEN (SELECT ORGANIZATION_ID FROM MTL_PARAMETERS WHERE ORGANIZATION_CODE = MICI.ORGANIZATION_CODE)
ELSE l_org_id
END
WHERE SET_PROCESS_ID = p_data_set_id AND
(PROCESS_FLAG IS NULL OR PROCESS_FLAG IN (0, 1)) AND
ORGANIZATION_ID IS NULL;
UPDATE EGO_ITM_USR_ATTR_INTRFC EIUAI
SET ORGANIZATION_ID =
CASE
WHEN ORGANIZATION_CODE IS NOT NULL
THEN (SELECT ORGANIZATION_ID FROM MTL_PARAMETERS WHERE ORGANIZATION_CODE = EIUAI.ORGANIZATION_CODE)
ELSE l_org_id
END
WHERE DATA_SET_ID = p_data_set_id AND
(PROCESS_STATUS IS NULL OR PROCESS_STATUS IN (0, 1)) AND
ORGANIZATION_ID IS NULL;
UPDATE EGO_ITEM_PEOPLE_INTF EIPI
SET ORGANIZATION_ID =
CASE
WHEN ORGANIZATION_CODE IS NOT NULL
THEN (SELECT ORGANIZATION_ID FROM MTL_PARAMETERS WHERE ORGANIZATION_CODE = EIPI.ORGANIZATION_CODE)
ELSE l_org_id
END
WHERE DATA_SET_ID = p_data_set_id AND
(PROCESS_STATUS IS NULL OR PROCESS_STATUS IN (0, 1)) AND
ORGANIZATION_ID IS NULL;
UPDATE EGO_AML_INTF EAI
SET ORGANIZATION_ID =
CASE
WHEN ORGANIZATION_CODE IS NOT NULL
THEN (SELECT ORGANIZATION_ID FROM MTL_PARAMETERS WHERE ORGANIZATION_CODE = EAI.ORGANIZATION_CODE)
ELSE l_org_id
END
WHERE DATA_SET_ID = p_data_set_id AND
(PROCESS_FLAG IS NULL OR PROCESS_FLAG IN (0, 1)) AND
ORGANIZATION_ID IS NULL;
UPDATE EGO_ITEM_ASSOCIATIONS_INTF EIAI
SET ORGANIZATION_ID =
CASE
WHEN ORGANIZATION_CODE IS NOT NULL
THEN (SELECT ORGANIZATION_ID FROM MTL_PARAMETERS WHERE ORGANIZATION_CODE = EIAI.ORGANIZATION_CODE)
ELSE l_org_id
END
WHERE BATCH_ID = p_data_set_id AND
(PROCESS_FLAG IS NULL OR PROCESS_FLAG IN (0, 1)) AND
ORGANIZATION_ID IS NULL;
UPDATE EGO_ITEM_ASSOCIATIONS_INTF EIAI
SET DATA_LEVEL_ID = (SELECT DATA_LEVEL_ID
FROM EGO_DATA_LEVEL_B EDLB
WHERE EDLB.APPLICATION_ID = 431
AND EDLB.ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
AND EDLB.DATA_LEVEL_NAME = EIAI.DATA_LEVEL_NAME
)
WHERE BATCH_ID = p_data_set_id
AND (PROCESS_FLAG IS NULL OR PROCESS_FLAG IN (0, 1))
AND DATA_LEVEL_ID IS NULL
AND DATA_LEVEL_NAME IS NOT NULL;
UPDATE EGO_ITM_USR_ATTR_INTRFC uai
SET uai.DATA_LEVEL_ID = (SELECT edlb.DATA_LEVEL_ID
FROM EGO_DATA_LEVEL_B edlb
WHERE edlb.DATA_LEVEL_NAME = uai.DATA_LEVEL_NAME
AND edlb.APPLICATION_ID = 431
AND edlb.ATTR_GROUP_TYPE = NVL(uai.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
)
WHERE uai.DATA_SET_ID = p_data_set_id
AND (uai.PROCESS_STATUS IS NULL OR uai.PROCESS_STATUS IN (0, 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_data_set_id
AND (uai.PROCESS_STATUS IS NULL OR uai.PROCESS_STATUS IN (0, 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 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_data_set_id
AND (uai.PROCESS_STATUS IS NULL OR uai.PROCESS_STATUS IN (0, 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 EGO_ITEM_ASSOCIATIONS_INTF eiai
SET PK1_VALUE = ( SELECT VENDOR_ID
FROM AP_SUPPLIERS aas
WHERE aas.SEGMENT1 = eiai.SUPPLIER_NUMBER
)
WHERE eiai.BATCH_ID = p_data_set_id
AND (eiai.PROCESS_FLAG IS NULL OR eiai.PROCESS_FLAG IN (0, 1) )
AND eiai.PK1_VALUE IS NULL
AND eiai.SUPPLIER_NUMBER IS NOT NULL;
UPDATE EGO_ITEM_ASSOCIATIONS_INTF eiai
SET PK1_VALUE = ( SELECT VENDOR_ID
FROM AP_SUPPLIERS aas
WHERE aas.VENDOR_NAME = eiai.SUPPLIER_NAME
)
WHERE eiai.BATCH_ID = p_data_set_id
AND (eiai.PROCESS_FLAG IS NULL OR eiai.PROCESS_FLAG IN (0, 1) )
AND eiai.PK1_VALUE IS NULL
AND eiai.SUPPLIER_NAME IS NOT NULL
AND eiai.SUPPLIER_NUMBER IS NULL;
UPDATE EGO_ITEM_ASSOCIATIONS_INTF eiai
SET PK2_VALUE = ( SELECT VENDOR_SITE_ID
FROM AP_SUPPLIER_SITES_ALL asa
WHERE asa.VENDOR_ID = eiai.PK1_VALUE
AND asa.VENDOR_SITE_CODE = eiai.SUPPLIER_SITE_NAME
AND asa.ORG_ID = FND_PROFILE.Value('ORG_ID')
)
WHERE eiai.BATCH_ID = p_data_set_id
AND (eiai.PROCESS_FLAG IS NULL OR eiai.PROCESS_FLAG IN (0, 1) )
AND eiai.PK2_VALUE IS NULL
AND eiai.SUPPLIER_SITE_NAME IS NOT NULL;
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_data_set_id
AND STYLE_ITEM_NUMBER IS NOT NULL
AND STYLE_ITEM_ID IS NULL
AND STYLE_ITEM_FLAG = 'N'
AND (PROCESS_FLAG IS NULL OR PROCESS_FLAG IN (0, 1)) ;
Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - PDH batch, update item_number if inventory_item_id is populated' );
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET item_number = (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = MSII.ORGANIZATION_ID)
WHERE SET_PROCESS_ID = p_data_set_id
AND PROCESS_FLAG = 1
AND ITEM_NUMBER IS NULL
AND INVENTORY_ITEM_ID IS NOT NULL;
UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI
SET item_number = (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = MIRI.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = MIRI.ORGANIZATION_ID)
WHERE SET_PROCESS_ID = p_data_set_id
AND PROCESS_FLAG = 1
AND ITEM_NUMBER IS NULL
AND INVENTORY_ITEM_ID IS NOT NULL;
UPDATE MTL_ITEM_CATEGORIES_INTERFACE MICI
SET item_number = (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = MICI.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = MICI.ORGANIZATION_ID)
WHERE SET_PROCESS_ID = p_data_set_id
AND PROCESS_FLAG = 1
AND ITEM_NUMBER IS NULL
AND INVENTORY_ITEM_ID IS NOT NULL;
UPDATE EGO_ITM_USR_ATTR_INTRFC EIUAI
SET item_number = (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = EIUAI.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = EIUAI.ORGANIZATION_ID)
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = 1
AND ITEM_NUMBER IS NULL
AND INVENTORY_ITEM_ID IS NOT NULL;
UPDATE EGO_AML_INTF EAI
SET item_number = (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = EAI.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = EAI.ORGANIZATION_ID)
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_FLAG = 1
AND ITEM_NUMBER IS NULL
AND INVENTORY_ITEM_ID IS NOT NULL;
UPDATE EGO_ITEM_PEOPLE_INTF MIPI
SET item_number = (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = MIPI.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = MIPI.ORGANIZATION_ID)
WHERE DATA_SET_ID = p_data_set_id
AND PROCESS_STATUS = 1
AND ITEM_NUMBER IS NULL
AND INVENTORY_ITEM_ID IS NOT NULL;
UPDATE EGO_ITEM_ASSOCIATIONS_INTF EIAI
SET item_number = (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = EIAI.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = EIAI.ORGANIZATION_ID)
WHERE BATCH_ID = p_data_set_id
AND PROCESS_FLAG = 1
AND ITEM_NUMBER IS NULL
AND INVENTORY_ITEM_ID IS NOT NULL;
l_insert_date := SYSDATE;
INSERT INTO
MTL_SYSTEM_ITEMS_INTERFACE MSII
( transaction_type, process_flag, set_process_id, organization_id, source_system_id
, confirm_status, inventory_item_id, item_number
, CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
, ITEM_CATALOG_GROUP_ID )
SELECT G_TRANS_TYPE_SYNC, 1, p_data_set_id, EIUAI.organization_id, l_ss_id
, G_FAKE_CONF_STATUS_FLAG
, MAX( EIUAI.inventory_item_id ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, LAST_UPDATE_DATE NULLS LAST )
, EIUAI.item_number
, l_insert_date -- CREATION_DATE
, l_insert_date -- LAST_UPDATE_DATE
, MAX( EIUAI.created_by ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) CREATED_BY
, MAX( EIUAI.last_updated_by ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) UPDATED_BY
, MAX( EIUAI.last_update_login ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) LAST_UPDATE_LOGIN
, MAX( EIUAI.item_catalog_group_id ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) ITEM_CATALOG_GROUP_ID
FROM EGO_ITM_USR_ATTR_INTRFC EIUAI
-- , MTL_PARAMETERS MP
WHERE
data_set_id = p_data_set_id
AND process_status = 1
AND EIUAI.organization_id = l_org_id
-- AND EIUAI.organization_id = MP.organization_id
-- AND MP.master_organization_id = l_org_id
AND NOT EXISTS
(
select null from mtl_system_items_interface
where set_process_id = EIUAI.data_set_id
and item_number = EIUAI.item_number
and process_flag = EIUAI.process_status
and organization_id = l_org_id
)
GROUP BY EIUAI.ITEM_NUMBER, EIUAI.organization_id;
Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - PDH batch, parent rows inserted for UDAs: ' || SQL%ROWCOUNT );
INSERT INTO
MTL_SYSTEM_ITEMS_INTERFACE MSII
( transaction_type, process_flag, set_process_id, organization_id, source_system_id
, confirm_status, inventory_item_id, item_number
, CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
SELECT G_TRANS_TYPE_SYNC, 1, p_data_set_id, l_org_id, l_ss_id
, G_FAKE_CONF_STATUS_FLAG
, MAX( EAI.inventory_item_id ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, LAST_UPDATE_DATE NULLS LAST )
, EAI.item_number
, l_insert_date -- CREATION_DATE
, l_insert_date -- LAST_UPDATE_DATE
, MAX( EAI.created_by ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) CREATED_BY
, MAX( EAI.last_updated_by ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) UPDATED_BY
, MAX( EAI.last_update_login ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) LAST_UPDATE_LOGIN
FROM EGO_AML_INTF EAI
-- , MTL_PARAMETERS MP
WHERE
EAI.data_set_id = p_data_set_id
AND EAI.process_flag = 1
AND EAI.organization_id = l_org_id
-- AND EAI.organization_id = MP.organization_id
-- AND MP.master_organization_id = l_org_id
AND NOT EXISTS
(
select null from mtl_system_items_interface
where set_process_id = EAI.data_set_id
and item_number = EAI.item_number
and process_flag = EAI.process_flag
and organization_id = l_org_id
)
GROUP BY EAI.ITEM_NUMBER;
Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - PDH batch, parent rows inserted for AMLs: ' || SQL%ROWCOUNT );
INSERT INTO
MTL_SYSTEM_ITEMS_INTERFACE MSII
( transaction_type, process_flag, set_process_id, organization_id, source_system_id
, confirm_status, inventory_item_id, item_number
, CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
SELECT G_TRANS_TYPE_SYNC, 1, p_data_set_id, EIAI.organization_id, l_ss_id
, G_FAKE_CONF_STATUS_FLAG
, MAX( EIAI.inventory_item_id ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, LAST_UPDATE_DATE NULLS LAST )
, EIAI.item_number
, l_insert_date -- CREATION_DATE
, l_insert_date -- LAST_UPDATE_DATE
, MAX( EIAI.created_by ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) CREATED_BY
, MAX( EIAI.last_updated_by ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) UPDATED_BY
, MAX( EIAI.last_update_login ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) LAST_UPDATE_LOGIN
FROM EGO_ITEM_ASSOCIATIONS_INTF EIAI
WHERE
batch_id = p_data_set_id
AND process_flag = 1
AND EIAI.organization_id = l_org_id
AND NOT EXISTS
(
select null from mtl_system_items_interface
where set_process_id = EIAI.batch_id
and item_number = EIAI.item_number
and process_flag = EIAI.process_flag
and organization_id = l_org_id
)
GROUP BY EIAI.ITEM_NUMBER, EIAI.organization_id;
Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - PDH batch, parent rows inserted for ASSOCIATIONS: ' || SQL%ROWCOUNT );
INSERT INTO
MTL_SYSTEM_ITEMS_INTERFACE MSII
( transaction_type, process_flag, set_process_id, organization_id, source_system_id
, confirm_status, inventory_item_id, item_number
, CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
SELECT G_TRANS_TYPE_SYNC, 1, p_data_set_id, MICI.organization_id, l_ss_id
, G_FAKE_CONF_STATUS_FLAG
, MAX( MICI.inventory_item_id ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, LAST_UPDATE_DATE NULLS LAST )
, MICI.item_number
, l_insert_date -- CREATION_DATE
, l_insert_date -- LAST_UPDATE_DATE
, MAX( MICI.created_by ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) CREATED_BY
, MAX( MICI.last_updated_by ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) UPDATED_BY
, MAX( MICI.last_update_login ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) LAST_UPDATE_LOGIN
FROM MTL_ITEM_CATEGORIES_INTERFACE MICI
WHERE
set_process_id = p_data_set_id
AND process_flag = 1
AND MICI.organization_id = l_org_id
AND NOT EXISTS
(
select null from mtl_system_items_interface
where set_process_id = MICI.set_process_id
and item_number = MICI.item_number
and process_flag = MICI.process_flag
and organization_id = l_org_id
)
GROUP BY MICI.ITEM_NUMBER, MICI.organization_id;
Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - PDH batch, parent rows inserted for CATEGORY ASSIGNMENT rows: ' || SQL%ROWCOUNT );
UPDATE_ITEM_SYNC_RECORDS_PDH(p_data_set_id, l_org_id);
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET CONFIRM_STATUS = NULL
WHERE
set_process_id = p_data_set_id
AND process_flag = 1
AND organization_id = l_org_id
AND UPPER( transaction_type ) = G_TRANS_TYPE_CREATE
AND confirm_status = G_FAKE_CONF_STATUS_FLAG;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET PROCESS_FLAG = 0
WHERE SET_PROCESS_ID = p_data_set_id AND
PROCESS_FLAG IS NULL;
UPDATE MTL_ITEM_REVISIONS_INTERFACE
SET PROCESS_FLAG = 0
WHERE SET_PROCESS_ID = p_data_set_id AND
PROCESS_FLAG IS NULL;
UPDATE MTL_ITEM_CATEGORIES_INTERFACE
SET PROCESS_FLAG = 0
WHERE SET_PROCESS_ID = p_data_set_id AND
PROCESS_FLAG IS NULL;
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET PROCESS_STATUS = 0
WHERE DATA_SET_ID = p_data_set_id AND
PROCESS_STATUS IS NULL;
UPDATE EGO_ITEM_PEOPLE_INTF
SET PROCESS_STATUS = 0
WHERE DATA_SET_ID = p_data_set_id AND
PROCESS_STATUS IS NULL;
UPDATE EGO_AML_INTF
SET PROCESS_FLAG = 0
WHERE DATA_SET_ID = p_data_set_id AND
PROCESS_FLAG IS NULL;
UPDATE EGO_ITEM_ASSOCIATIONS_INTF
SET PROCESS_FLAG = 0
WHERE BATCH_ID = p_data_set_id AND
PROCESS_FLAG IS NULL;
l_insert_date := SYSDATE;
INSERT INTO
MTL_SYSTEM_ITEMS_INTERFACE MSII
( transaction_type, process_flag, set_process_id, organization_id, source_system_id, source_system_reference, bundle_id
, confirm_status, inventory_item_id, item_number
, CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
, ITEM_CATALOG_GROUP_ID )
SELECT G_TRANS_TYPE_SYNC, 0, p_data_set_id, EIUAI.organization_id
, EIUAI.source_system_id, EIUAI.source_system_reference, EIUAI.bundle_id
, G_FAKE_CONF_STATUS_FLAG
, MAX( EIUAI.inventory_item_id ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, ITEM_NUMBER NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) ITEM_ID
, MAX( EIUAI.item_number ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, ITEM_NUMBER NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) ITEM_NUMBER
, l_insert_date -- CREATION_DATE
, l_insert_date -- LAST_UPDATE_DATE
, MAX( EIUAI.created_by ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, ITEM_NUMBER NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) CREATED_BY
, MAX( EIUAI.last_updated_by ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, ITEM_NUMBER NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) UPDATED_BY
, MAX( EIUAI.last_update_login ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, ITEM_NUMBER NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) LAST_UPDATE_LOGIN
, MAX( EIUAI.item_catalog_group_id ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, ITEM_NUMBER NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) ITEM_CATALOG_GROUP_ID
FROM EGO_ITM_USR_ATTR_INTRFC EIUAI
-- , MTL_PARAMETERS MP
WHERE
data_set_id = p_data_set_id
AND process_status = 0
AND EIUAI.organization_id = l_org_id
-- AND EIUAI.organization_id = MP.organization_id
-- AND MP.master_organization_id = l_org_id
AND source_system_id = l_ss_id
AND source_system_reference IS NOT NULL
AND NOT EXISTS
(
select null from mtl_system_items_interface
where set_process_id = EIUAI.data_set_id
and source_system_id = EIUAI.source_system_id
and source_system_reference = EIUAI.source_system_reference
and process_flag = EIUAI.process_status
and organization_id = l_org_id
)
GROUP BY EIUAI.source_system_id, EIUAI.source_system_reference, EIUAI.organization_id, EIUAI.BUNDLE_ID;
Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - Source System batch, parent rows inserted for UDAs: ' || SQL%ROWCOUNT );
INSERT INTO
MTL_SYSTEM_ITEMS_INTERFACE MSII
( transaction_type, process_flag, set_process_id, organization_id, source_system_id, source_system_reference
, source_system_reference_desc, confirm_status, inventory_item_id, item_number
, CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
SELECT G_TRANS_TYPE_SYNC, 0, p_data_set_id, l_org_id
, EAI.source_system_id, EAI.source_system_reference
, MAX( EAI.source_system_reference_desc ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, ITEM_NUMBER NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) SOURCE_SYSTEM_REFERENCE_DESC
, G_FAKE_CONF_STATUS_FLAG
, MAX( EAI.inventory_item_id ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, ITEM_NUMBER NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) ITEM_ID
, MAX( EAI.item_number ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, ITEM_NUMBER NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) ITEM_NUMBER
, l_insert_date -- CREATION_DATE
, l_insert_date -- LAST_UPDATE_DATE
, MAX( EAI.created_by ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, ITEM_NUMBER NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) CREATED_BY
, MAX( EAI.last_updated_by ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, ITEM_NUMBER NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) UPDATED_BY
, MAX( EAI.last_update_login ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, ITEM_NUMBER NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) LAST_UPDATE_LOGIN
FROM EGO_AML_INTF EAI
-- , MTL_PARAMETERS MP
WHERE
EAI.data_set_id = p_data_set_id
AND EAI.process_flag = 0
AND EAI.organization_id = l_org_id
-- AND EAI.organization_id = MP.organization_id
-- AND MP.master_organization_id = l_org_id
AND source_system_id = l_ss_id
AND source_system_reference IS NOT NULL
AND NOT EXISTS
(
select null from mtl_system_items_interface
where set_process_id = EAI.data_set_id
and source_system_id = EAI.source_system_id
and source_system_reference = EAI.source_system_reference
and process_flag = EAI.process_flag
and organization_id = l_org_id
)
GROUP BY EAI.source_system_id, EAI.source_system_reference;
Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - Source System batch, parent rows inserted for AMLs: ' || SQL%ROWCOUNT );
INSERT INTO
MTL_SYSTEM_ITEMS_INTERFACE MSII
( transaction_type, process_flag, set_process_id, organization_id, source_system_id, source_system_reference, bundle_id
, confirm_status, inventory_item_id, item_number
, CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
SELECT G_TRANS_TYPE_SYNC, 0, p_data_set_id, l_org_id
, EIAI.source_system_id, EIAI.source_system_reference, EIAI.bundle_id
, G_FAKE_CONF_STATUS_FLAG
, MAX( EIAI.inventory_item_id ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, ITEM_NUMBER NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) ITEM_ID
, MAX( EIAI.item_number ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, ITEM_NUMBER NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) ITEM_NUMBER
, l_insert_date -- CREATION_DATE
, l_insert_date -- LAST_UPDATE_DATE
, MAX( EIAI.created_by ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, ITEM_NUMBER NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) CREATED_BY
, MAX( EIAI.last_updated_by ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, ITEM_NUMBER NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) UPDATED_BY
, MAX( EIAI.last_update_login ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, ITEM_NUMBER NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) LAST_UPDATE_LOGIN
FROM EGO_ITEM_ASSOCIATIONS_INTF EIAI
WHERE
EIAI.batch_id = p_data_set_id
AND EIAI.process_flag = 0
AND EIAI.organization_id = l_org_id
AND source_system_id = l_ss_id
AND source_system_reference IS NOT NULL
AND NOT EXISTS
(
select null from mtl_system_items_interface
where set_process_id = EIAI.batch_id
and source_system_id = EIAI.source_system_id
and source_system_reference = EIAI.source_system_reference
and process_flag = EIAI.process_flag
and organization_id = l_org_id
)
GROUP BY EIAI.source_system_id, EIAI.source_system_reference, EIAI.bundle_id;
Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - Source System batch, parent rows inserted for ASSOCIATIONS: ' || SQL%ROWCOUNT );
INSERT INTO
MTL_SYSTEM_ITEMS_INTERFACE MSII
( transaction_type, process_flag, set_process_id, organization_id, source_system_id, source_system_reference, bundle_id
, confirm_status, inventory_item_id, item_number
, CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
SELECT G_TRANS_TYPE_SYNC, 0, p_data_set_id, l_org_id
, MICI.source_system_id, MICI.source_system_reference, MICI.bundle_id
, G_FAKE_CONF_STATUS_FLAG
, MAX( MICI.inventory_item_id ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, ITEM_NUMBER NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) ITEM_ID
, MAX( MICI.item_number ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, ITEM_NUMBER NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) ITEM_NUMBER
, l_insert_date -- CREATION_DATE
, l_insert_date -- LAST_UPDATE_DATE
, MAX( MICI.created_by ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, ITEM_NUMBER NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) CREATED_BY
, MAX( MICI.last_updated_by ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, ITEM_NUMBER NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) UPDATED_BY
, MAX( MICI.last_update_login ) KEEP ( DENSE_RANK FIRST ORDER BY INVENTORY_ITEM_ID NULLS LAST, ITEM_NUMBER NULLS LAST, LAST_UPDATE_DATE NULLS LAST ) LAST_UPDATE_LOGIN
FROM MTL_ITEM_CATEGORIES_INTERFACE MICI
WHERE
MICI.set_process_id = p_data_set_id
AND MICI.process_flag = 0
AND MICI.organization_id = l_org_id
AND source_system_id = l_ss_id
AND source_system_reference IS NOT NULL
AND NOT EXISTS
(
select null from mtl_system_items_interface
where set_process_id = MICI.set_process_id
and source_system_id = MICI.source_system_id
and source_system_reference = MICI.source_system_reference
and process_flag = MICI.process_flag
and organization_id = l_org_id
)
GROUP BY MICI.source_system_id, MICI.source_system_reference, MICI.bundle_id;
Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - Source System batch, parent rows inserted for CAT Assignments: ' || SQL%ROWCOUNT );
UPDATE
MTL_SYSTEM_ITEMS_INTERFACE MSII
SET CONFIRM_STATUS = CASE
WHEN CONFIRM_STATUS IS NULL THEN G_EXCLUDED
ELSE G_FAKE_EXCLUDED
END
WHERE
SET_PROCESS_ID = p_data_set_id
AND ( CONFIRM_STATUS IS NULL
OR CONFIRM_STATUS = G_FAKE_CONF_STATUS_FLAG ) -- bug5303685: exclusion check for fake rows
AND PROCESS_FLAG = 0
AND ORGANIZATION_ID = l_org_id
AND EXISTS
( SELECT NULL
FROM EGO_IMPORT_EXCLUDED_SS_ITEMS
WHERE SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
AND SOURCE_SYSTEM_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE
);
UPDATE
MTL_SYSTEM_ITEMS_INTERFACE MSII
SET CONFIRM_STATUS = G_EXCLUDED
WHERE
SET_PROCESS_ID = p_data_set_id
AND CONFIRM_STATUS IS NULL
AND PROCESS_FLAG = 0
AND ORGANIZATION_ID = l_org_id
AND EXISTS
( SELECT NULL
FROM EGO_IMPORT_EXCLUDED_SS_ITEMS
WHERE SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
AND SOURCE_SYSTEM_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE
);
UPDATE_ITEM_SYNC_RECORDS(p_data_set_id, l_org_id);
UPDATE
MTL_SYSTEM_ITEMS_INTERFACE MSII
SET TRANSACTION_TYPE = UPPER( TRANSACTION_TYPE )
, INVENTORY_ITEM_ID =
(SELECT INVENTORY_ITEM_ID
FROM MTL_CROSS_REFERENCES_B XREF
WHERE XREF.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF' AND
XREF.CROSS_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE AND
XREF.SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID AND
SYSDATE BETWEEN NVL(XREF.START_DATE_ACTIVE, SYSDATE - 1) AND NVL(XREF.END_DATE_ACTIVE, SYSDATE + 1) AND #'
|| l_security_predicate || q'# AND
ROWNUM < = 1)
WHERE
SET_PROCESS_ID = :p_data_set_id AND
PROCESS_FLAG = 0 AND
ORGANIZATION_ID = :l_org_id AND
( CONFIRM_STATUS IS NULL
OR CONFIRM_STATUS = '#'||G_FAKE_CONF_STATUS_FLAG||q'#'
)
AND
( INVENTORY_ITEM_ID IS NULL OR NOT EXISTS
(SELECT 1
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
) )
AND UPPER( TRANSACTION_TYPE ) = 'UPDATE' #';
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET REVISION_IMPORT_POLICY =
CASE
WHEN EXISTS -- check the revision interface table
(
SELECT NULL
FROM MTL_ITEM_REVISIONS_INTERFACE
WHERE SET_PROCESS_ID = MSII.SET_PROCESS_ID
AND SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
AND SOURCE_SYSTEM_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE
AND ORGANIZATION_ID = MSII.ORGANIZATION_ID
AND PROCESS_FLAG = MSII.PROCESS_FLAG
AND
(
REVISION_ID IS NOT NULL
OR REVISION IS NOT NULL
)
)
THEN 'S'
WHEN EXISTS -- check the user attrs interface table
(
SELECT NULL
FROM EGO_ITM_USR_ATTR_INTRFC USR_ATTR
WHERE DATA_SET_ID = MSII.SET_PROCESS_ID
AND SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
AND SOURCE_SYSTEM_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE
AND ORGANIZATION_ID = MSII.ORGANIZATION_ID
AND PROCESS_STATUS = MSII.PROCESS_FLAG
AND
(
REVISION_ID IS NOT NULL
OR REVISION IS NOT NULL
)
)
THEN 'S'
ELSE NULL
END
WHERE SET_PROCESS_ID = p_data_set_id
AND ORGANIZATION_ID = l_org_id
AND PROCESS_FLAG = 0
AND ( REVISION_IMPORT_POLICY = 'S' OR REVISION_IMPORT_POLICY IS NULL )
--AND CONFIRM_STATUS IS NULL
;
UPDATE -- for non-fake rows
MTL_SYSTEM_ITEMS_INTERFACE MSII
SET
CONFIRM_STATUS =
CASE
WHEN UPPER( TRANSACTION_TYPE ) = G_TRANS_TYPE_CREATE AND l_import_xref_only = 'N' THEN G_CONF_NEW
WHEN INVENTORY_ITEM_ID IS NOT NULL THEN G_CONF_XREF -- TRANSACTION_TYPE is 'UPDATE'
ELSE G_UNCONF_NONE_MATCH
END
, TRANSACTION_TYPE = UPPER( TRANSACTION_TYPE )
WHERE
SET_PROCESS_ID = p_data_set_id AND
PROCESS_FLAG = 0 AND
ORGANIZATION_ID = l_org_id AND
CONFIRM_STATUS IS NULL;
UPDATE -- for fake rows
MTL_SYSTEM_ITEMS_INTERFACE MSII
SET
CONFIRM_STATUS =
CASE
WHEN UPPER( TRANSACTION_TYPE ) = G_TRANS_TYPE_CREATE AND l_import_xref_only = 'N' THEN G_CONF_NEW
WHEN INVENTORY_ITEM_ID IS NOT NULL THEN G_CONF_XREF_FAKE -- TRANSACTION_TYPE is 'UPDATE'
ELSE G_UNCONF_NO_MATCH_FAKE
END
, TRANSACTION_TYPE = UPPER( TRANSACTION_TYPE )
WHERE
SET_PROCESS_ID = p_data_set_id AND
PROCESS_FLAG = 0 AND
ORGANIZATION_ID = l_org_id AND
CONFIRM_STATUS = G_FAKE_CONF_STATUS_FLAG;
UPDATE
MTL_SYSTEM_ITEMS_INTERFACE MSII
SET
INVENTORY_ITEM_ID =
CASE
WHEN CONFIRM_STATUS IN ( G_CONF_XREF, G_CONF_MATCH, G_UNCONF_SIGL_MATCH, G_UNCONF_MULT_MATCH
, G_CONF_XREF_FAKE, G_CONF_MATCH_FAKE, G_UNCONF_SINGLE_MATCH_FAKE, G_UNCONF_MULTI_MATCH_FAKE )
THEN INVENTORY_ITEM_ID
-- if it's 'CN', 'UN', 'EX' or NULL, erase
ELSE NULL
END
WHERE
SET_PROCESS_ID = p_data_set_id AND
PROCESS_FLAG = 0 AND
ORGANIZATION_ID = l_org_id;
UPDATE
MTL_SYSTEM_ITEMS_INTERFACE MSII
SET ITEM_CATALOG_GROUP_ID = (SELECT MICG.ITEM_CATALOG_GROUP_ID
FROM MTL_ITEM_CATALOG_GROUPS_B_KFV MICG
WHERE MICG.CONCATENATED_SEGMENTS = MSII.ITEM_CATALOG_GROUP_NAME)
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 0
AND MSII.ORGANIZATION_ID = l_org_id
AND MSII.CONFIRM_STATUS IN ( G_CONF_NEW, G_CONF_XREF, G_CONF_MATCH, G_CONF_XREF_FAKE, G_CONF_MATCH_FAKE )
AND MSII.ITEM_CATALOG_GROUP_ID IS NULL
AND MSII.ITEM_CATALOG_GROUP_NAME IS NOT NULL;
SELECT ROW_IDENTIFIER FROM
(
SELECT
ROW_IDENTIFIER,
COUNT( DISTINCT ROW_IDENTIFIER )
OVER ( PARTITION BY
NVL(SOURCE_SYSTEM_ID, cp_ss_id),
NVL(ITEM_NUMBER, SOURCE_SYSTEM_REFERENCE),
ATTR_GROUP_INT_NAME,
ATTR_INT_NAME,
DATA_LEVEL_ID,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
ORGANIZATION_ID,
NVL( ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
) cnt
FROM EGO_ITM_USR_ATTR_INTRFC eiuai
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = p_process_status
AND (ITEM_NUMBER IS NOT NULL OR SOURCE_SYSTEM_REFERENCE IS NOT NULL)
AND DATA_LEVEL_ID IS NOT NULL
AND EXISTS( SELECT NULL
FROM EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT
WHERE FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = NVL( eiuai.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
AND FL_CTX_EXT.APPLICATION_ID = 431
AND FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = eiuai.ATTR_GROUP_INT_NAME
AND FL_CTX_EXT.MULTI_ROW = 'N'
)
)
WHERE CNT > 1;
SELECT ROW_ID, MAX_ROW_IDENTIFIER FROM
(
SELECT
ROWID ROW_ID,
COUNT( DISTINCT ROW_IDENTIFIER ) OVER ( PARTITION BY
NVL(SOURCE_SYSTEM_ID, cp_ss_id),
NVL(ITEM_NUMBER, SOURCE_SYSTEM_REFERENCE),
ATTR_GROUP_INT_NAME,
DATA_LEVEL_ID,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
ORGANIZATION_ID,
NVL( ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
) cnt,
MAX( ROW_IDENTIFIER ) OVER ( PARTITION BY
NVL(SOURCE_SYSTEM_ID, cp_ss_id),
NVL(ITEM_NUMBER, SOURCE_SYSTEM_REFERENCE),
ATTR_GROUP_INT_NAME,
DATA_LEVEL_ID,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
ORGANIZATION_ID,
NVL( ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
) MAX_ROW_IDENTIFIER
FROM EGO_ITM_USR_ATTR_INTRFC eiuai
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = p_process_status
AND (ITEM_NUMBER IS NOT NULL OR SOURCE_SYSTEM_REFERENCE IS NOT NULL)
AND DATA_LEVEL_ID IS NOT NULL
AND EXISTS( SELECT NULL
FROM EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT
WHERE FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = NVL( eiuai.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
AND FL_CTX_EXT.APPLICATION_ID = 431
AND FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = eiuai.ATTR_GROUP_INT_NAME
AND FL_CTX_EXT.MULTI_ROW = 'N'
)
)
WHERE CNT > 1;
SELECT DISTINCT
ORGANIZATION_ID,
ITEM_NUMBER,
SOURCE_SYSTEM_REFERENCE,
INVENTORY_ITEM_ID,
ATTR_GROUP_INT_NAME,
TRANSACTION_ID
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 3.475;
SELECT SOURCE_SYSTEM_ID INTO l_ss_id
FROM EGO_IMPORT_BATCHES_B
WHERE BATCH_ID = p_batch_id;
UPDATE EGO_ITM_USR_ATTR_INTRFC eiuai
SET ITEM_NUMBER = (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = eiuai.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = eiuai.ORGANIZATION_ID)
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = p_process_status
AND INVENTORY_ITEM_ID IS NOT NULL
AND ITEM_NUMBER IS NULL;
UPDATE EGO_ITM_USR_ATTR_INTRFC eiuai
SET REVISION = ( SELECT R.REVISION
FROM MTL_ITEM_REVISIONS_B R
WHERE R.REVISION_ID = eiuai.REVISION_ID
)
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = p_process_status
AND REVISION_ID IS NOT NULL
AND REVISION IS NULL;
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET process_status = 3.475
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = p_process_status
AND ROW_IDENTIFIER = l_unmerged_row_idents(idx);
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET
PROCESS_STATUS = 3,
REQUEST_ID = l_request_id
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 3.475;
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET ROW_IDENTIFIER = l_row_idents(idx)
WHERE ROWID = l_row_ids(idx);
Debug_Conc_Log('Clean_Up_UDA_Row_Idents_Pre - Updated - '||SQL%ROWCOUNT||' rows');
SELECT ROW_IDENTIFIER FROM
(
SELECT
ROW_IDENTIFIER,
COUNT( DISTINCT ROW_IDENTIFIER )
OVER ( PARTITION BY
INVENTORY_ITEM_ID,
ATTR_GROUP_INT_NAME,
ATTR_INT_NAME,
DATA_LEVEL_ID,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
ORGANIZATION_ID,
NVL( ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
) cnt
FROM EGO_ITM_USR_ATTR_INTRFC eiuai
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = p_process_status
AND INVENTORY_ITEM_ID IS NOT NULL
AND DATA_LEVEL_ID IS NOT NULL
AND EXISTS( SELECT NULL
FROM EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT
WHERE FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = NVL( eiuai.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
AND FL_CTX_EXT.APPLICATION_ID = 431
AND FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = eiuai.ATTR_GROUP_INT_NAME
AND FL_CTX_EXT.MULTI_ROW = 'N'
)
)
WHERE CNT > 1;
SELECT ROW_ID, MAX_ROW_IDENTIFIER FROM
(
SELECT
ROWID ROW_ID,
COUNT( DISTINCT ROW_IDENTIFIER ) OVER ( PARTITION BY
INVENTORY_ITEM_ID,
ATTR_GROUP_INT_NAME,
DATA_LEVEL_ID,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
ORGANIZATION_ID,
NVL( ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
) cnt,
MAX( ROW_IDENTIFIER ) OVER ( PARTITION BY
INVENTORY_ITEM_ID,
ATTR_GROUP_INT_NAME,
DATA_LEVEL_ID,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
ORGANIZATION_ID,
NVL( ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
) MAX_ROW_IDENTIFIER
FROM EGO_ITM_USR_ATTR_INTRFC eiuai
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = p_process_status
AND INVENTORY_ITEM_ID IS NOT NULL
AND DATA_LEVEL_ID IS NOT NULL
AND EXISTS( SELECT NULL
FROM EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT
WHERE FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = NVL( eiuai.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
AND FL_CTX_EXT.APPLICATION_ID = 431
AND FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = eiuai.ATTR_GROUP_INT_NAME
AND FL_CTX_EXT.MULTI_ROW = 'N'
)
)
WHERE CNT > 1;
SELECT DISTINCT
ORGANIZATION_ID,
ITEM_NUMBER,
SOURCE_SYSTEM_REFERENCE,
INVENTORY_ITEM_ID,
ATTR_GROUP_INT_NAME,
TRANSACTION_ID
FROM EGO_ITM_USR_ATTR_INTRFC
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 3.475;
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET process_status = 3.475
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = p_process_status
AND ROW_IDENTIFIER = l_unmerged_row_idents(idx);
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET
PROCESS_STATUS = 3,
REQUEST_ID = l_request_id
WHERE DATA_SET_ID = p_batch_id
AND PROCESS_STATUS = 3.475;
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET ROW_IDENTIFIER = l_row_idents(idx)
WHERE ROWID = l_row_ids(idx);
Debug_Conc_Log('Clean_Up_UDA_Row_Idents_Post - Updated - '||SQL%ROWCOUNT||' rows');
SELECT
ROWID,
SOURCE_SYSTEM_ID,
SOURCE_SYSTEM_REFERENCE,
ITEM_NUMBER,
ORGANIZATION_CODE,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
TRANSACTION_TYPE,
TRANSACTION_ID
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE SET_PROCESS_ID = p_data_set_id
AND PROCESS_FLAG = 33390;
SELECT KFV.CONCATENATED_SEGMENTS ITEM_NUMBER,
MSII.ORGANIZATION_CODE,
MSII.ORGANIZATION_ID,
MSII.TRANSACTION_ID
FROM MTL_SYSTEM_ITEMS_B_KFV KFV,
MTL_SYSTEM_ITEMS_INTERFACE MSII,
MTL_CROSS_REFERENCES MCR
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MCR.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND MSII.SOURCE_SYSTEM_ID = MCR.SOURCE_SYSTEM_ID
AND MSII.ORGANIZATION_ID = KFV.ORGANIZATION_ID
AND MCR.CROSS_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE
AND MCR.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID
AND SYSDATE BETWEEN NVL(MCR.START_DATE_ACTIVE, SYSDATE-1) AND NVL(MCR.END_DATE_ACTIVE, SYSDATE + 1)
AND MSII.PROCESS_FLAG = 33391;
SELECT
BA.SOURCE_SYSTEM_ID,
BA.ORGANIZATION_ID,
NVL(OPT.REVISION_IMPORT_POLICY, 'L'),
NVL(opt.IMPORT_XREF_ONLY, 'N'),
NVL(opt.ENABLED_FOR_DATA_POOL, 'N')
INTO l_ss_id, l_org_id, l_import_policy, l_import_xref_only, l_enabled_for_data_pool
FROM EGO_IMPORT_BATCHES_B BA, EGO_IMPORT_OPTION_SETS OPT
WHERE BA.BATCH_ID = p_data_set_id AND BA.BATCH_ID = OPT.BATCH_ID;
SELECT mp.MASTER_ORGANIZATION_ID
INTO l_org_id
FROM MTL_PARAMETERS mp
WHERE mp.ORGANIZATION_ID = TO_NUMBER(l_org);
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET
REQUEST_ID = l_request_id
, PROGRAM_APPLICATION_ID = l_prog_appid
, PROGRAM_ID = l_prog_id
, PROGRAM_UPDATE_DATE = SYSDATE
, PROCESS_FLAG = CASE -- bug 5283663: never leave fake rows in status 1
WHEN UPPER( TRANSACTION_TYPE ) <> G_TRANS_TYPE_CREATE THEN 7
ELSE 3
END
WHERE
set_process_id = p_data_set_id
AND organization_id = l_org_id
AND confirm_status = G_FAKE_CONF_STATUS_FLAG
-- AND UPPER( TRANSACTION_TYPE ) <> G_TRANS_TYPE_CREATE -- bug 5283663: never leave fake rows in status 1
;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
SET (STYLE_ITEM_FLAG, STYLE_ITEM_ID, INVENTORY_ITEM_ID) = (SELECT
DECODE(MAX(NVL(msik.STYLE_ITEM_FLAG, '$NULL$')),
'$NULL$', msii.STYLE_ITEM_FLAG,
NULL, msii.STYLE_ITEM_FLAG,
'N', 'N',
'Y', 'Y'
),
NVL(MAX(msik.STYLE_ITEM_ID), msii.STYLE_ITEM_ID),
NVL(msii.INVENTORY_ITEM_ID, MAX(msik.INVENTORY_ITEM_ID))
FROM MTL_SYSTEM_ITEMS_KFV msik, MTL_PARAMETERS mp
WHERE (msii.INVENTORY_ITEM_ID = msik.INVENTORY_ITEM_ID
OR msii.ITEM_NUMBER = msik.CONCATENATED_SEGMENTS)
AND msik.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID
AND msii.ORGANIZATION_ID = mp.ORGANIZATION_ID
)
WHERE SET_PROCESS_ID = p_data_set_id
AND PROCESS_FLAG = 1;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET PROCESS_FLAG = 33390
WHERE SET_PROCESS_ID = :p_data_set_id
AND PROCESS_FLAG = 0
AND ORGANIZATION_ID = :org_id
AND CONFIRM_STATUS IN ('CC', 'CM', 'CFC', 'CFM')
AND EXISTS
(SELECT 1
FROM MTL_CROSS_REFERENCES_VL APPLYSEC
WHERE APPLYSEC.CROSS_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE
AND APPLYSEC.SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
AND APPLYSEC.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND APPLYSEC.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
AND (APPLYSEC.DESCRIPTION <> MSII.SOURCE_SYSTEM_REFERENCE_DESC OR
(APPLYSEC.DESCRIPTION IS NULL AND MSII.SOURCE_SYSTEM_REFERENCE_DESC IS NOT NULL))
AND SYSDATE BETWEEN NVL(APPLYSEC.START_DATE_ACTIVE, SYSDATE-1) AND NVL(APPLYSEC.END_DATE_ACTIVE, SYSDATE + 1)
AND NOT #' || l_security_predicate || ' )';
source system reference desc is being updated');
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET PROCESS_FLAG = 33391
WHERE SET_PROCESS_ID = :p_data_set_id
AND PROCESS_FLAG = 0
AND ORGANIZATION_ID = :org_id
AND CONFIRM_STATUS IN ('CC', 'CM', 'CFC', 'CFM')
AND EXISTS ( SELECT 1
FROM MTL_CROSS_REFERENCES_B APPLYSEC
WHERE APPLYSEC.CROSS_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE
AND APPLYSEC.SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
AND APPLYSEC.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND APPLYSEC.INVENTORY_ITEM_ID <> MSII.INVENTORY_ITEM_ID
AND SYSDATE BETWEEN NVL(APPLYSEC.START_DATE_ACTIVE, SYSDATE-1) AND NVL(APPLYSEC.END_DATE_ACTIVE, SYSDATE + 1)
AND NOT #' || l_security_predicate || ' )';
UPDATE MTL_SYSTEM_ITEMS_INTERFACE APPLYSEC
SET PROCESS_FLAG = 33390
WHERE SET_PROCESS_ID = :p_data_set_id
AND PROCESS_FLAG = 0
AND ORGANIZATION_ID = :org_id
AND CONFIRM_STATUS IN ('CC', 'CM', 'CFC', 'CFM')
AND NOT #' || l_security_predicate || q'#
AND APPLYSEC.INVENTORY_ITEM_ID IS NOT NULL
AND LNNVL (APPLYSEC.INVENTORY_ITEM_ID =
(SELECT XREF.INVENTORY_ITEM_ID
FROM MTL_CROSS_REFERENCES_B XREF
WHERE XREF.CROSS_REFERENCE = APPLYSEC.SOURCE_SYSTEM_REFERENCE
AND XREF.SOURCE_SYSTEM_ID = APPLYSEC.SOURCE_SYSTEM_ID
AND XREF.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND SYSDATE BETWEEN NVL(XREF.START_DATE_ACTIVE, SYSDATE-1) AND NVL(XREF.END_DATE_ACTIVE, SYSDATE + 1))) #';
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET PROCESS_FLAG = 3
WHERE SET_PROCESS_ID = p_data_set_id
AND PROCESS_FLAG IN (33390, 33391)
AND CONFIRM_STATUS IN ( 'CM', 'CC', 'CN', G_CONF_XREF_FAKE, G_CONF_MATCH_FAKE )
AND ORGANIZATION_ID = l_org_id;
UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI
SET TRANSACTION_TYPE = (
SELECT
(CASE
WHEN MSII.CONFIRM_STATUS = 'CN'
THEN 'CREATE'
WHEN (MSII.CONFIRM_STATUS IN ( 'CM', 'CC', G_CONF_XREF_FAKE, G_CONF_MATCH_FAKE )
AND EXISTS (SELECT NULL
FROM MTL_ITEM_REVISIONS_B MIR
WHERE MIR.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
AND MIR.ORGANIZATION_ID = MIRI.ORGANIZATION_ID
AND MIR.REVISION = MIRI.REVISION) )
THEN 'UPDATE'
ELSE 'CREATE'
END)
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 0
AND MSII.CONFIRM_STATUS IN ('CN', 'CM', 'CC', G_CONF_XREF_FAKE, G_CONF_MATCH_FAKE)
AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = MIRI.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = l_org_id
AND ROWNUM = 1
)
WHERE MIRI.PROCESS_FLAG = 0
AND MIRI.SET_PROCESS_ID = p_data_set_id
AND EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 0
AND MSII.CONFIRM_STATUS IN ( 'CN', 'CM', 'CC', G_CONF_XREF_FAKE, G_CONF_MATCH_FAKE )
AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = MIRI.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = l_org_id);
UPDATE
MTL_SYSTEM_ITEMS_INTERFACE MSII
SET CONFIRM_STATUS = (
CASE
WHEN (MSII.DESCRIPTION IS NULL
AND NOT EXISTS
(SELECT NULL
FROM MTL_ITEM_TEMPLATES_VL TEMP,
MTL_ITEM_TEMPL_ATTRIBUTES ATTR
WHERE ((MSII.TEMPLATE_ID IS NOT NULL AND TEMP.TEMPLATE_ID = MSII.TEMPLATE_ID)
OR (MSII.TEMPLATE_ID IS NULL AND TEMP.TEMPLATE_NAME = MSII.TEMPLATE_NAME)
)
AND (TEMP.CONTEXT_ORGANIZATION_ID = MSII.ORGANIZATION_ID OR TEMP.CONTEXT_ORGANIZATION_ID IS NULL)
AND TEMP.TEMPLATE_ID = ATTR.TEMPLATE_ID
AND ATTR.ENABLED_FLAG = 'Y'
AND ATTR.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.DESCRIPTION'
)
AND 'F' <>
(SELECT DECODE(MSII.STYLE_ITEM_FLAG, 'Y', 'ZZZ', (MAX(ICC.ITEM_DESC_GEN_METHOD) KEEP (DENSE_RANK FIRST ORDER BY LEVEL) ) ) AS ITEM_DESC_GEN_METHOD
FROM MTL_ITEM_CATALOG_GROUPS_B ICC
WHERE ICC.ITEM_DESC_GEN_METHOD IS NOT NULL
AND ICC.ITEM_DESC_GEN_METHOD <> 'I'
CONNECT BY PRIOR ICC.PARENT_CATALOG_GROUP_ID = ICC.ITEM_CATALOG_GROUP_ID
START WITH ICC.ITEM_CATALOG_GROUP_ID = MSII.ITEM_CATALOG_GROUP_ID
)
)
THEN CONFIRM_STATUS
WHEN
(MSII.ITEM_NUMBER IS NULL AND MSII.ITEM_CATALOG_GROUP_ID IS NULL)
THEN CONFIRM_STATUS
WHEN
( MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
AND MSII.ITEM_NUMBER IS NULL
AND (SELECT DECODE(MSII.STYLE_ITEM_FLAG, 'Y', 'ZZZ', (MAX(ICC.ITEM_NUM_GEN_METHOD) KEEP (DENSE_RANK FIRST ORDER BY LEVEL) ) ) AS ITEM_NUM_GEN_METHOD
FROM MTL_ITEM_CATALOG_GROUPS_B ICC
WHERE ICC.ITEM_NUM_GEN_METHOD IS NOT NULL
AND ICC.ITEM_NUM_GEN_METHOD <> 'I'
CONNECT BY PRIOR ICC.PARENT_CATALOG_GROUP_ID = ICC.ITEM_CATALOG_GROUP_ID
START WITH ICC.ITEM_CATALOG_GROUP_ID = MSII.ITEM_CATALOG_GROUP_ID
) NOT IN ('S', 'F')
)
THEN CONFIRM_STATUS
ELSE CONFIRM_STATUS||'R'
END
)
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 0
AND MSII.ORGANIZATION_ID = l_org_id
AND MSII.CONFIRM_STATUS = 'CN';
UPDATE
MTL_SYSTEM_ITEMS_INTERFACE MSII
SET ITEM_NUMBER = EGO_IMPORT_PVT.GET_NEXT_ITEM_NUMBER(MSII.ITEM_CATALOG_GROUP_ID)
WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
AND MSII.ITEM_NUMBER IS NULL
AND MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 0
AND MSII.CONFIRM_STATUS = 'CNR'
AND MSII.ORGANIZATION_ID = l_org_id
AND 'S' = ( SELECT MAX( ICC.ITEM_NUM_GEN_METHOD ) KEEP (DENSE_RANK FIRST ORDER BY LEVEL)
FROM MTL_ITEM_CATALOG_GROUPS_B ICC
WHERE ICC.ITEM_NUM_GEN_METHOD IS NOT NULL
AND ICC.ITEM_NUM_GEN_METHOD <> 'I'
CONNECT BY PRIOR ICC.PARENT_CATALOG_GROUP_ID = ICC.ITEM_CATALOG_GROUP_ID
START WITH ICC.ITEM_CATALOG_GROUP_ID = MSII.ITEM_CATALOG_GROUP_ID
);
UPDATE
MTL_SYSTEM_ITEMS_INTERFACE MSII
SET CONFIRM_STATUS = (
CASE
WHEN
(NVL(MSII.REVISION_IMPORT_POLICY, l_import_policy) = 'N' AND
1 <> (SELECT COUNT(*)
FROM MTL_ITEM_REVISIONS_INTERFACE MIRI
WHERE MIRI.SET_PROCESS_ID = MSII.SET_PROCESS_ID
AND MIRI.PROCESS_FLAG = 0
AND MIRI.SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
AND MIRI.SOURCE_SYSTEM_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE
AND MIRI.ORGANIZATION_ID = MSII.ORGANIZATION_ID
AND MIRI.REVISION IS NOT NULL
AND MIRI.EFFECTIVITY_DATE IS NOT NULL
AND UPPER(MIRI.TRANSACTION_TYPE) = 'CREATE'))
THEN CONFIRM_STATUS
ELSE CONFIRM_STATUS||'R'
END
)
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 0
AND MSII.ORGANIZATION_ID = l_org_id
AND MSII.CONFIRM_STATUS IN ('CC', 'CM');
UPDATE
MTL_SYSTEM_ITEMS_INTERFACE MSII
SET CONFIRM_STATUS = (
CASE
WHEN ( NVL(MSII.REVISION_IMPORT_POLICY, l_import_policy) = 'N' AND
1 <> (SELECT COUNT(*)
FROM MTL_ITEM_REVISIONS_INTERFACE MIRI
WHERE MIRI.SET_PROCESS_ID = MSII.SET_PROCESS_ID
AND MIRI.PROCESS_FLAG = 0
AND MIRI.SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
AND MIRI.SOURCE_SYSTEM_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE
AND MIRI.ORGANIZATION_ID = MSII.ORGANIZATION_ID
AND MIRI.REVISION IS NOT NULL
AND MIRI.EFFECTIVITY_DATE IS NOT NULL
AND UPPER(MIRI.TRANSACTION_TYPE) = G_TRANS_TYPE_CREATE )
)
THEN CONFIRM_STATUS
ELSE G_FAKE_MATCH_READY
END
)
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 0
AND MSII.ORGANIZATION_ID = l_org_id
AND MSII.CONFIRM_STATUS IN ( G_CONF_MATCH_FAKE, G_CONF_XREF_FAKE );
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET PROCESS_FLAG = 33391
WHERE SET_PROCESS_ID = :p_data_set_id
AND PROCESS_FLAG = 0
AND ORGANIZATION_ID = :org_id
AND CONFIRM_status = 'CNR'
AND EXISTS ( SELECT 1 FROM MTL_CROSS_REFERENCES_B APPLYSEC
WHERE APPLYSEC.CROSS_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE
AND APPLYSEC.SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
AND APPLYSEC.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND SYSDATE BETWEEN NVL(APPLYSEC.START_DATE_ACTIVE, SYSDATE-1) AND NVL(APPLYSEC.END_DATE_ACTIVE, SYSDATE + 1)
AND NOT #' || l_security_predicate || ' )';
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET PROCESS_FLAG = 33390
WHERE SET_PROCESS_ID = :p_data_set_id
AND PROCESS_FLAG = 0
AND ORGANIZATION_ID = :org_id
AND CONFIRM_STATUS IN ('CCR', 'CMR', 'FMR' )
AND EXISTS
(SELECT 1
FROM MTL_CROSS_REFERENCES_VL APPLYSEC
WHERE APPLYSEC.CROSS_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE
AND APPLYSEC.SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
AND APPLYSEC.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND APPLYSEC.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
AND (APPLYSEC.DESCRIPTION <> MSII.SOURCE_SYSTEM_REFERENCE_DESC OR
(APPLYSEC.DESCRIPTION IS NULL AND MSII.SOURCE_SYSTEM_REFERENCE_DESC IS NOT NULL))
AND SYSDATE BETWEEN NVL(APPLYSEC.START_DATE_ACTIVE, SYSDATE-1) AND NVL(APPLYSEC.END_DATE_ACTIVE, SYSDATE + 1)
AND NOT #' || l_security_predicate || ' )';
source system reference desc is being updated' );
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET PROCESS_FLAG = 33391
WHERE SET_PROCESS_ID = :p_data_set_id
AND PROCESS_FLAG = 0
AND ORGANIZATION_ID = :org_id
AND CONFIRM_STATUS IN ( 'CCR', 'CMR', 'FMR' )
AND EXISTS ( SELECT 1
FROM MTL_CROSS_REFERENCES_B APPLYSEC
WHERE APPLYSEC.CROSS_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE
AND APPLYSEC.SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
AND APPLYSEC.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND APPLYSEC.INVENTORY_ITEM_ID <> MSII.INVENTORY_ITEM_ID
AND SYSDATE BETWEEN NVL(APPLYSEC.START_DATE_ACTIVE, SYSDATE-1) AND NVL(APPLYSEC.END_DATE_ACTIVE, SYSDATE + 1)
AND NOT #' || l_security_predicate || ' )';
UPDATE MTL_SYSTEM_ITEMS_INTERFACE APPLYSEC
SET PROCESS_FLAG = 33390
WHERE SET_PROCESS_ID = :p_data_set_id
AND PROCESS_FLAG = 0
AND ORGANIZATION_ID = :org_id
AND CONFIRM_STATUS IN ( 'CCR', 'CMR', 'FMR' )
AND NOT #' || l_security_predicate || q'#
AND APPLYSEC.INVENTORY_ITEM_ID IS NOT NULL
AND LNNVL (APPLYSEC.INVENTORY_ITEM_ID =
(SELECT XREF.INVENTORY_ITEM_ID
FROM MTL_CROSS_REFERENCES_B XREF
WHERE XREF.CROSS_REFERENCE = APPLYSEC.SOURCE_SYSTEM_REFERENCE
AND XREF.SOURCE_SYSTEM_ID = APPLYSEC.SOURCE_SYSTEM_ID
AND XREF.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND SYSDATE BETWEEN NVL(XREF.START_DATE_ACTIVE, SYSDATE-1) AND NVL(XREF.END_DATE_ACTIVE, SYSDATE + 1))) #';
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET PROCESS_FLAG = 1
, TRANSACTION_TYPE = CASE
WHEN CONFIRM_STATUS = 'CNR' THEN G_TRANS_TYPE_CREATE
ELSE G_TRANS_TYPE_UPDATE
END
-- ELETUCHY Bug 5316904: Should not attempt to change ICC from source system batch
, ITEM_CATALOG_GROUP_ID = CASE
WHEN CONFIRM_STATUS = 'CNR' THEN ITEM_CATALOG_GROUP_ID
ELSE NULL
END
, ITEM_CATALOG_GROUP_NAME = CASE
WHEN CONFIRM_STATUS = 'CNR' THEN ITEM_CATALOG_GROUP_NAME
ELSE NULL
END
WHERE SET_PROCESS_ID = p_data_set_id
AND PROCESS_FLAG = 0
AND CONFIRM_STATUS IN ( 'CNR', 'CMR', 'CCR' )
AND ORGANIZATION_ID = l_org_id;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET PROCESS_FLAG = 1,
TRANSACTION_TYPE = G_TRANS_TYPE_UPDATE,
CONFIRM_STATUS = G_FAKE_CONF_STATUS_FLAG
WHERE SET_PROCESS_ID = p_data_set_id
AND PROCESS_FLAG = 0
AND CONFIRM_STATUS = G_FAKE_MATCH_READY
AND ORGANIZATION_ID = l_org_id;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET (PROCESS_FLAG, TRANSACTION_TYPE, INVENTORY_ITEM_ID, ITEM_NUMBER, ITEM_CATALOG_GROUP_ID, ITEM_CATALOG_GROUP_NAME, STYLE_ITEM_FLAG, STYLE_ITEM_ID ) =
(SELECT
1,
(CASE
WHEN MSII2.CONFIRM_STATUS = 'CNR'
THEN 'CREATE'
ELSE 'SYNC'
END),
MSII2.INVENTORY_ITEM_ID,
MSII2.ITEM_NUMBER,
MSII2.ITEM_CATALOG_GROUP_ID,
MSII2.ITEM_CATALOG_GROUP_NAME,
NVL(MSII.STYLE_ITEM_FLAG, MSII2.STYLE_ITEM_FLAG),
NVL(MSII.STYLE_ITEM_ID, MSII2.STYLE_ITEM_ID)
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2
WHERE MSII2.SET_PROCESS_ID = p_data_set_id
AND MSII2.PROCESS_FLAG = 1
AND MSII2.SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
AND MSII2.SOURCE_SYSTEM_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE
AND MSII2.CONFIRM_STATUS IN ( 'CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
AND MSII2.ORGANIZATION_ID = l_org_id
AND ROWNUM = 1
)
WHERE MSII.PROCESS_FLAG = 0
AND MSII.CONFIRM_STATUS IS NULL
AND MSII.SET_PROCESS_ID = p_data_set_id
AND EXISTS (SELECT NULL FROM MTL_PARAMETERS mp
WHERE mp.ORGANIZATION_ID = MSII.ORGANIZATION_ID
AND mp.MASTER_ORGANIZATION_ID = l_org_id
AND mp.MASTER_ORGANIZATION_ID <> mp.ORGANIZATION_ID
)
AND EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2
WHERE MSII2.SET_PROCESS_ID = p_data_set_id
AND MSII2.PROCESS_FLAG = 1
AND MSII2.CONFIRM_STATUS IN ( 'CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
AND MSII2.SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
AND MSII2.SOURCE_SYSTEM_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE
AND MSII2.ORGANIZATION_ID = l_org_id);
UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI
SET (PROCESS_FLAG, TRANSACTION_TYPE, INVENTORY_ITEM_ID, ITEM_NUMBER) =
(SELECT
1,
(CASE
WHEN MSII.CONFIRM_STATUS = 'CNR'
THEN 'CREATE'
WHEN MIRI.TRANSACTION_TYPE <> 'SYNC'
THEN MIRI.TRANSACTION_TYPE
WHEN EXISTS (SELECT 1
FROM MTL_ITEM_REVISIONS_B MIR
WHERE MIR.INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
AND MIR.ORGANIZATION_ID = MIRI.ORGANIZATION_ID
AND MIR.REVISION = MIRI.REVISION)
THEN 'UPDATE'
ELSE 'CREATE'
END),
MSII.INVENTORY_ITEM_ID,
MSII.ITEM_NUMBER
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 1
AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = MIRI.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = l_org_id
AND ROWNUM = 1
)
WHERE MIRI.PROCESS_FLAG = 0
AND MIRI.SET_PROCESS_ID = p_data_set_id
AND EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 1
AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = MIRI.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = l_org_id);
UPDATE MTL_ITEM_CATEGORIES_INTERFACE MICI
SET (PROCESS_FLAG, TRANSACTION_TYPE, INVENTORY_ITEM_ID, ITEM_NUMBER) =
(SELECT
1,
(CASE
WHEN MSII.CONFIRM_STATUS = 'CNR'
THEN 'CREATE'
ELSE MICI.TRANSACTION_TYPE
END),
MSII.INVENTORY_ITEM_ID,
MSII.ITEM_NUMBER
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 1
AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
AND MSII.SOURCE_SYSTEM_ID = MICI.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = MICI.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = l_org_id
AND ROWNUM = 1
)
WHERE MICI.PROCESS_FLAG = 0
AND MICI.SET_PROCESS_ID = p_data_set_id
AND EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 1
AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
AND MSII.SOURCE_SYSTEM_ID = MICI.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = MICI.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = l_org_id);
UPDATE EGO_ITM_USR_ATTR_INTRFC ATTRS
SET (PROCESS_STATUS, TRANSACTION_TYPE, INVENTORY_ITEM_ID, ITEM_NUMBER, ITEM_CATALOG_GROUP_ID) =
(SELECT
1,
(CASE
WHEN UPPER(ATTRS.TRANSACTION_TYPE) = G_TRANS_TYPE_DELETE THEN G_TRANS_TYPE_DELETE
WHEN MSII.CONFIRM_STATUS = 'CNR' THEN G_TRANS_TYPE_CREATE
ELSE G_TRANS_TYPE_SYNC
END),
MSII.INVENTORY_ITEM_ID,
MSII.ITEM_NUMBER,
NVL(ATTRS.ITEM_CATALOG_GROUP_ID, MSII.ITEM_CATALOG_GROUP_ID)
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 1
AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
AND MSII.SOURCE_SYSTEM_ID = ATTRS.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = ATTRS.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = l_org_id
AND ROWNUM = 1
)
WHERE ATTRS.PROCESS_STATUS = 0
AND ATTRS.DATA_SET_ID = p_data_set_id
AND EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 1
AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
AND MSII.SOURCE_SYSTEM_ID = ATTRS.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = ATTRS.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = l_org_id);
UPDATE EGO_ITM_USR_ATTR_INTRFC ATTRS
SET REVISION = (
CASE (SELECT
CASE
WHEN MSII.CONFIRM_STATUS IN ('CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
AND (MSII.REVISION_IMPORT_POLICY = 'L' OR (MSII.REVISION_IMPORT_POLICY IS NULL AND l_import_policy = 'L'))
THEN '1'
WHEN MSII.CONFIRM_STATUS IN ('CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
AND (MSII.REVISION_IMPORT_POLICY = 'N' OR (MSII.REVISION_IMPORT_POLICY IS NULL AND l_import_policy = 'N'))
THEN '2'
WHEN MSII.CONFIRM_STATUS = 'CNR'
THEN '3'
END
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 1
AND MSII.CONFIRM_STATUS IN ( 'CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
AND MSII.SOURCE_SYSTEM_ID = ATTRS.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = ATTRS.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = l_org_id
)
WHEN '1'
THEN (SELECT MAX(REVISION) KEEP (DENSE_RANK FIRST ORDER BY EFFECTIVITY_DATE DESC)
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = ATTRS.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = ATTRS.ORGANIZATION_ID)
WHEN '2'
THEN (SELECT REVISION
FROM MTL_ITEM_REVISIONS_INTERFACE MIRI
WHERE MIRI.SET_PROCESS_ID = ATTRS.DATA_SET_ID
AND MIRI.SOURCE_SYSTEM_ID = ATTRS.SOURCE_SYSTEM_ID
AND MIRI.SOURCE_SYSTEM_REFERENCE = ATTRS.SOURCE_SYSTEM_REFERENCE
AND MIRI.ORGANIZATION_ID = ATTRS.ORGANIZATION_ID
AND MIRI.REVISION IS NOT NULL
AND MIRI.EFFECTIVITY_DATE IS NOT NULL
AND UPPER(MIRI.TRANSACTION_TYPE) = 'CREATE'
AND ROWNUM = 1
)
WHEN '3'
THEN (SELECT STARTING_REVISION
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = ATTRS.ORGANIZATION_ID
)
END)
WHERE ATTRS.PROCESS_STATUS = 1
AND ATTRS.DATA_SET_ID = p_data_set_id
AND ATTRS.REVISION IS NULL
AND EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 1
AND MSII.CONFIRM_STATUS IN ( 'CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
AND MSII.SOURCE_SYSTEM_ID = ATTRS.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = ATTRS.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = l_org_id)
AND EXISTS (SELECT NULL
FROM EGO_OBJ_AG_ASSOCS_B A, EGO_FND_DSC_FLX_CTX_EXT EXT
WHERE A.ATTR_GROUP_ID = EXT.ATTR_GROUP_ID
AND A.OBJECT_ID = (SELECT OBJECT_ID FROM FND_OBJECTS WHERE OBJ_NAME = 'EGO_ITEM')
AND EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = ATTRS.ATTR_GROUP_INT_NAME
AND EXT.DESCRIPTIVE_FLEXFIELD_NAME = NVL(ATTRS.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
AND EXT.APPLICATION_ID = 431
AND A.DATA_LEVEL = 'ITEM_REVISION_LEVEL');
UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI
SET (REVISION, TRANSACTION_TYPE) =
(SELECT
(CASE
WHEN (EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 1
AND MSII.CONFIRM_STATUS IN ('CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
AND (MSII.REVISION_IMPORT_POLICY = 'L' OR (MSII.REVISION_IMPORT_POLICY IS NULL AND l_import_policy = 'L'))
AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = MIRI.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = l_org_id)
)
THEN (SELECT MAX(REVISION) KEEP (DENSE_RANK FIRST ORDER BY EFFECTIVITY_DATE DESC)
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = MIRI.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = MIRI.ORGANIZATION_ID)
WHEN (EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 1
AND MSII.CONFIRM_STATUS = 'CNR'
AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = MIRI.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = l_org_id)
)
THEN (SELECT STARTING_REVISION
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = MIRI.ORGANIZATION_ID
)
END) AS REVISION,
'SYNC' AS TRANSACTION_TYPE
FROM DUAL)
WHERE MIRI.PROCESS_FLAG = 1
AND MIRI.SET_PROCESS_ID = p_data_set_id
AND MIRI.REVISION IS NULL
AND EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 1
AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = MIRI.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = l_org_id);
UPDATE EGO_ITEM_PEOPLE_INTF EIPI
SET (PROCESS_STATUS, TRANSACTION_TYPE, INVENTORY_ITEM_ID, ITEM_NUMBER) =
(SELECT
1,
(CASE
WHEN MSII.CONFIRM_STATUS = 'CNR'
THEN 'CREATE'
ELSE 'SYNC'
END),
MSII.INVENTORY_ITEM_ID,
MSII.ITEM_NUMBER
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 1
AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
AND MSII.SOURCE_SYSTEM_ID = EIPI.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = EIPI.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = l_org_id
AND ROWNUM = 1
)
WHERE EIPI.PROCESS_STATUS = 0
AND EIPI.DATA_SET_ID = p_data_set_id
AND EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 1
AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
AND MSII.SOURCE_SYSTEM_ID = EIPI.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = EIPI.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = l_org_id);
UPDATE EGO_AML_INTF EAI
SET (PROCESS_FLAG, TRANSACTION_TYPE, INVENTORY_ITEM_ID, ITEM_NUMBER) =
(SELECT
1,
(CASE
WHEN UPPER(EAI.TRANSACTION_TYPE) = G_TRANS_TYPE_DELETE THEN G_TRANS_TYPE_DELETE
WHEN MSII.CONFIRM_STATUS = 'CNR' THEN G_TRANS_TYPE_CREATE
ELSE G_TRANS_TYPE_SYNC
END),
MSII.INVENTORY_ITEM_ID,
MSII.ITEM_NUMBER
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 1
AND MSII.CONFIRM_STATUS IN ( 'CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
AND MSII.SOURCE_SYSTEM_ID = EAI.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = EAI.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = l_org_id
AND ROWNUM = 1
)
WHERE EAI.PROCESS_FLAG = 0
AND EAI.DATA_SET_ID = p_data_set_id
AND EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 1
AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
AND MSII.SOURCE_SYSTEM_ID = EAI.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = EAI.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = l_org_id);
UPDATE EGO_ITEM_ASSOCIATIONS_INTF EIAI
SET (PROCESS_FLAG, TRANSACTION_TYPE, INVENTORY_ITEM_ID, ITEM_NUMBER) =
(SELECT
1,
(CASE
WHEN MSII.CONFIRM_STATUS = 'CNR'
THEN 'CREATE'
ELSE EIAI.TRANSACTION_TYPE
END),
MSII.INVENTORY_ITEM_ID,
MSII.ITEM_NUMBER
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 1
AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
AND MSII.SOURCE_SYSTEM_ID = EIAI.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = EIAI.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = l_org_id
AND ROWNUM = 1
)
WHERE EIAI.PROCESS_FLAG = 0
AND EIAI.BATCH_ID = p_data_set_id
AND EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.SET_PROCESS_ID = p_data_set_id
AND MSII.PROCESS_FLAG = 1
AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
AND MSII.SOURCE_SYSTEM_ID = EIAI.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = EIAI.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = l_org_id);
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET (PROCESS_FLAG, TRANSACTION_TYPE, INVENTORY_ITEM_ID) =
(SELECT
1,
NVL(MSII.TRANSACTION_TYPE, 'SYNC'),
MCR.INVENTORY_ITEM_ID
FROM MTL_CROSS_REFERENCES_B MCR
WHERE MCR.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND MCR.CROSS_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE
AND MCR.SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
AND SYSDATE BETWEEN NVL(MCR.START_DATE_ACTIVE, SYSDATE - 1) AND NVL(MCR.END_DATE_ACTIVE, SYSDATE + 1)
AND ROWNUM = 1
)
WHERE MSII.SET_PROCESS_ID = :p_data_set_id
AND MSII.PROCESS_FLAG = 0
-- PICK ONLY CHILD ITEMS
AND EXISTS (SELECT NULL
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = :l_org_id
AND MP.ORGANIZATION_ID <> MP.MASTER_ORGANIZATION_ID
AND MP.ORGANIZATION_ID = MSII.ORGANIZATION_ID)
-- CHILD MUST NOT HAVE A MASTER RECORD IN CURRENT BATCH
AND NOT EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2
WHERE MSII2.PROCESS_FLAG in (0, 1, 33390,33391)
AND MSII2.SET_PROCESS_ID = :p_data_set_id1
AND MSII2.SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
AND MSII2.SOURCE_SYSTEM_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE
AND MSII2.ORGANIZATION_ID = :l_org_id1
)
-- HAS A CROSS REFERENCE
AND EXISTS (SELECT NULL
FROM MTL_CROSS_REFERENCES_B MCR
WHERE MCR.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND MCR.CROSS_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE
AND MCR.SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
AND SYSDATE BETWEEN NVL(MCR.START_DATE_ACTIVE, SYSDATE - 1) AND NVL(MCR.END_DATE_ACTIVE, SYSDATE + 1)
#'||l_security_predicate||')';
UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI
SET (PROCESS_FLAG, TRANSACTION_TYPE, INVENTORY_ITEM_ID) =
(SELECT
1,
NVL(MIRI.TRANSACTION_TYPE, 'SYNC'),
MCR.INVENTORY_ITEM_ID
FROM MTL_CROSS_REFERENCES_B MCR
WHERE MCR.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND MCR.CROSS_REFERENCE = MIRI.SOURCE_SYSTEM_REFERENCE
AND MCR.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
AND (MCR.START_DATE_ACTIVE < SYSDATE OR MCR.START_DATE_ACTIVE IS NULL)
AND (MCR.END_DATE_ACTIVE > SYSDATE OR MCR.END_DATE_ACTIVE IS NULL)
AND ROWNUM = 1
)
WHERE MIRI.SET_PROCESS_ID = :p_data_set_id
AND MIRI.PROCESS_FLAG = 0
-- MUST NOT HAVE A MASTER ITEM RECORD IN CURRENT BATCH
AND NOT EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.PROCESS_FLAG IN (0, 1, 33390,33391)
AND MSII.SET_PROCESS_ID = :p_data_set_id1
AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = MIRI.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = :l_org_id
)
-- HAS A CROSS REFERENCE
AND EXISTS (SELECT NULL
FROM MTL_CROSS_REFERENCES_B MCR
WHERE MCR.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND MCR.CROSS_REFERENCE = MIRI.SOURCE_SYSTEM_REFERENCE
AND MCR.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
AND (MCR.START_DATE_ACTIVE < SYSDATE OR MCR.START_DATE_ACTIVE IS NULL)
AND (MCR.END_DATE_ACTIVE > SYSDATE OR MCR.END_DATE_ACTIVE IS NULL)
#'||l_security_predicate||')';
UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI
SET (REVISION, TRANSACTION_TYPE) =
(SELECT MAX(REVISION) KEEP (DENSE_RANK FIRST ORDER BY EFFECTIVITY_DATE DESC), 'UPDATE'
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = MIRI.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = MIRI.ORGANIZATION_ID)
WHERE MIRI.SET_PROCESS_ID = :p_data_set_id
AND MIRI.PROCESS_FLAG = 1
AND MIRI.REVISION IS NULL
-- MUST NOT HAVE A MASTER ITEM RECORD IN CURRENT BATCH
AND NOT EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.PROCESS_FLAG IN (0, 1, 33390,33391)
AND MSII.SET_PROCESS_ID = :p_data_set_id1
AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = MIRI.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = :l_org_id
)
-- HAS A CROSS REFERENCE
AND EXISTS (SELECT NULL
FROM MTL_CROSS_REFERENCES_B MCR
WHERE MCR.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND MCR.CROSS_REFERENCE = MIRI.SOURCE_SYSTEM_REFERENCE
AND MCR.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
AND (MCR.START_DATE_ACTIVE < SYSDATE OR MCR.START_DATE_ACTIVE IS NULL)
AND (MCR.END_DATE_ACTIVE > SYSDATE OR MCR.END_DATE_ACTIVE IS NULL)
#'||l_security_predicate||')';
UPDATE MTL_ITEM_CATEGORIES_INTERFACE MICI
SET (PROCESS_FLAG, TRANSACTION_TYPE, INVENTORY_ITEM_ID) =
(SELECT
1,
MICI.TRANSACTION_TYPE,
MCR.INVENTORY_ITEM_ID
FROM MTL_CROSS_REFERENCES_B MCR
WHERE MCR.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND MCR.CROSS_REFERENCE = MICI.SOURCE_SYSTEM_REFERENCE
AND MCR.SOURCE_SYSTEM_ID = MICI.SOURCE_SYSTEM_ID
AND (MCR.START_DATE_ACTIVE < SYSDATE OR MCR.START_DATE_ACTIVE IS NULL)
AND (MCR.END_DATE_ACTIVE > SYSDATE OR MCR.END_DATE_ACTIVE IS NULL)
AND ROWNUM = 1
)
WHERE MICI.SET_PROCESS_ID = :p_data_set_id
AND MICI.PROCESS_FLAG = 0
-- MUST NOT HAVE A MASTER ITEM RECORD IN CURRENT BATCH
AND NOT EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.PROCESS_FLAG IN (0, 1, 33390,33391)
AND MSII.SET_PROCESS_ID = :p_data_set_id1
AND MSII.SOURCE_SYSTEM_ID = MICI.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = MICI.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = :l_org_id
)
-- HAS A CROSS REFERENCE
AND EXISTS (SELECT NULL
FROM MTL_CROSS_REFERENCES_B MCR
WHERE MCR.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND MCR.CROSS_REFERENCE = MICI.SOURCE_SYSTEM_REFERENCE
AND MCR.SOURCE_SYSTEM_ID = MICI.SOURCE_SYSTEM_ID
AND (MCR.START_DATE_ACTIVE < SYSDATE OR MCR.START_DATE_ACTIVE IS NULL)
AND (MCR.END_DATE_ACTIVE > SYSDATE OR MCR.END_DATE_ACTIVE IS NULL)
#'||l_security_predicate||')';
UPDATE EGO_ITEM_PEOPLE_INTF EIPI
SET (PROCESS_STATUS, TRANSACTION_TYPE, INVENTORY_ITEM_ID) =
(SELECT
1,
NVL(EIPI.TRANSACTION_TYPE, 'SYNC'),
MCR.INVENTORY_ITEM_ID
FROM MTL_CROSS_REFERENCES_B MCR
WHERE MCR.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND MCR.CROSS_REFERENCE = EIPI.SOURCE_SYSTEM_REFERENCE
AND MCR.SOURCE_SYSTEM_ID = EIPI.SOURCE_SYSTEM_ID
AND (MCR.START_DATE_ACTIVE < SYSDATE OR MCR.START_DATE_ACTIVE IS NULL)
AND (MCR.END_DATE_ACTIVE > SYSDATE OR MCR.END_DATE_ACTIVE IS NULL)
AND ROWNUM = 1
)
WHERE EIPI.DATA_SET_ID = :p_data_set_id
AND EIPI.PROCESS_STATUS = 0
-- MUST NOT HAVE A MASTER ITEM RECORD IN CURRENT BATCH
AND NOT EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.PROCESS_FLAG IN (0, 1, 33390,33391)
AND MSII.SET_PROCESS_ID = :p_data_set_id1
AND MSII.SOURCE_SYSTEM_ID = EIPI.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = EIPI.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = :l_org_id
)
-- HAS A CROSS REFERENCE
AND EXISTS (SELECT NULL
FROM MTL_CROSS_REFERENCES_B MCR
WHERE MCR.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND MCR.CROSS_REFERENCE = EIPI.SOURCE_SYSTEM_REFERENCE
AND MCR.SOURCE_SYSTEM_ID = EIPI.SOURCE_SYSTEM_ID
AND (MCR.START_DATE_ACTIVE < SYSDATE OR MCR.START_DATE_ACTIVE IS NULL)
AND (MCR.END_DATE_ACTIVE > SYSDATE OR MCR.END_DATE_ACTIVE IS NULL)
#'||l_security_predicate||')';
UPDATE EGO_ITM_USR_ATTR_INTRFC EIUAI
SET (PROCESS_STATUS, TRANSACTION_TYPE, INVENTORY_ITEM_ID) =
(SELECT
1,
NVL(EIUAI.TRANSACTION_TYPE, 'SYNC'),
MCR.INVENTORY_ITEM_ID
FROM MTL_CROSS_REFERENCES_B MCR
WHERE MCR.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND MCR.CROSS_REFERENCE = EIUAI.SOURCE_SYSTEM_REFERENCE
AND MCR.SOURCE_SYSTEM_ID = EIUAI.SOURCE_SYSTEM_ID
AND (MCR.START_DATE_ACTIVE < SYSDATE OR MCR.START_DATE_ACTIVE IS NULL)
AND (MCR.END_DATE_ACTIVE > SYSDATE OR MCR.END_DATE_ACTIVE IS NULL)
AND ROWNUM = 1
)
WHERE EIUAI.DATA_SET_ID = :p_data_set_id
AND EIUAI.PROCESS_STATUS = 0
-- MUST NOT HAVE A MASTER ITEM RECORD IN CURRENT BATCH
AND NOT EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.PROCESS_FLAG IN (0, 1, 33390, 33391)
AND MSII.SET_PROCESS_ID = :p_data_set_id1
AND MSII.SOURCE_SYSTEM_ID = EIUAI.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = EIUAI.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = :l_org_id
)
-- HAS A CROSS REFERENCE
AND EXISTS (SELECT NULL
FROM MTL_CROSS_REFERENCES_B MCR
WHERE MCR.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND MCR.CROSS_REFERENCE = EIUAI.SOURCE_SYSTEM_REFERENCE
AND MCR.SOURCE_SYSTEM_ID = EIUAI.SOURCE_SYSTEM_ID
AND (MCR.START_DATE_ACTIVE < SYSDATE OR MCR.START_DATE_ACTIVE IS NULL)
AND (MCR.END_DATE_ACTIVE > SYSDATE OR MCR.END_DATE_ACTIVE IS NULL)
#'||l_security_predicate||')';
UPDATE EGO_AML_INTF EAI
SET (PROCESS_FLAG, TRANSACTION_TYPE, INVENTORY_ITEM_ID) =
(SELECT
1,
NVL(EAI.TRANSACTION_TYPE, 'SYNC'),
MCR.INVENTORY_ITEM_ID
FROM MTL_CROSS_REFERENCES_B MCR
WHERE MCR.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND MCR.CROSS_REFERENCE = EAI.SOURCE_SYSTEM_REFERENCE
AND MCR.SOURCE_SYSTEM_ID = EAI.SOURCE_SYSTEM_ID
AND (MCR.START_DATE_ACTIVE < SYSDATE OR MCR.START_DATE_ACTIVE IS NULL)
AND (MCR.END_DATE_ACTIVE > SYSDATE OR MCR.END_DATE_ACTIVE IS NULL)
AND ROWNUM = 1
)
WHERE EAI.DATA_SET_ID = :p_data_set_id
AND EAI.PROCESS_FLAG = 0
-- MUST NOT HAVE A MASTER ITEM RECORD IN CURRENT BATCH
AND NOT EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.PROCESS_FLAG IN (0, 1, 33390, 33391)
AND MSII.SET_PROCESS_ID = :p_data_set_id1
AND MSII.SOURCE_SYSTEM_ID = EAI.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = EAI.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = :l_org_id
)
-- HAS A CROSS REFERENCE
AND EXISTS (SELECT NULL
FROM MTL_CROSS_REFERENCES_B MCR
WHERE MCR.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND MCR.CROSS_REFERENCE = EAI.SOURCE_SYSTEM_REFERENCE
AND MCR.SOURCE_SYSTEM_ID = EAI.SOURCE_SYSTEM_ID
AND (MCR.START_DATE_ACTIVE < SYSDATE OR MCR.START_DATE_ACTIVE IS NULL)
AND (MCR.END_DATE_ACTIVE > SYSDATE OR MCR.END_DATE_ACTIVE IS NULL)
#'||l_security_predicate||')';
UPDATE EGO_ITEM_ASSOCIATIONS_INTF EIAI
SET (PROCESS_FLAG, TRANSACTION_TYPE, INVENTORY_ITEM_ID) =
(SELECT
1,
EIAI.TRANSACTION_TYPE,
MCR.INVENTORY_ITEM_ID
FROM MTL_CROSS_REFERENCES_B MCR
WHERE MCR.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND MCR.CROSS_REFERENCE = EIAI.SOURCE_SYSTEM_REFERENCE
AND MCR.SOURCE_SYSTEM_ID = EIAI.SOURCE_SYSTEM_ID
AND (MCR.START_DATE_ACTIVE < SYSDATE OR MCR.START_DATE_ACTIVE IS NULL)
AND (MCR.END_DATE_ACTIVE > SYSDATE OR MCR.END_DATE_ACTIVE IS NULL)
AND ROWNUM = 1
)
WHERE EIAI.BATCH_ID = :p_data_set_id
AND EIAI.PROCESS_FLAG = 0
-- MUST NOT HAVE A MASTER ITEM RECORD IN CURRENT BATCH
AND NOT EXISTS (SELECT NULL
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.PROCESS_FLAG IN (0, 1, 33390,33391)
AND MSII.SET_PROCESS_ID = :p_data_set_id1
AND MSII.SOURCE_SYSTEM_ID = EIAI.SOURCE_SYSTEM_ID
AND MSII.SOURCE_SYSTEM_REFERENCE = EIAI.SOURCE_SYSTEM_REFERENCE
AND MSII.ORGANIZATION_ID = :l_org_id
)
-- HAS A CROSS REFERENCE
AND EXISTS (SELECT NULL
FROM MTL_CROSS_REFERENCES_B MCR
WHERE MCR.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND MCR.CROSS_REFERENCE = EIAI.SOURCE_SYSTEM_REFERENCE
AND MCR.SOURCE_SYSTEM_ID = EIAI.SOURCE_SYSTEM_ID
AND (MCR.START_DATE_ACTIVE < SYSDATE OR MCR.START_DATE_ACTIVE IS NULL)
AND (MCR.END_DATE_ACTIVE > SYSDATE OR MCR.END_DATE_ACTIVE IS NULL)
#'||l_security_predicate||')';
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET REQUEST_ID = CASE PROCESS_FLAG
WHEN 33390 THEN l_request_id
WHEN 33391 THEN l_request_id
ELSE REQUEST_ID
END,
PROCESS_FLAG = CASE PROCESS_FLAG
WHEN 33390 THEN 3
WHEN 33391 THEN 3
ELSE PROCESS_FLAG
END,
CONFIRM_STATUS = SUBSTR(CONFIRM_STATUS, 1, 2)
WHERE SET_PROCESS_ID = p_data_set_id
AND PROCESS_FLAG IN (1, 33390, 33391)
AND CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR')
AND ORGANIZATION_ID = l_org_id;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET REQUEST_ID = l_request_id
, PROCESS_FLAG = CASE PROCESS_FLAG
WHEN 33390 THEN 3
WHEN 33391 THEN 3
ELSE 7
END
WHERE SET_PROCESS_ID = p_data_set_id
AND PROCESS_FLAG IN (1, 33390, 33391)
AND CONFIRM_STATUS IN ( G_FAKE_CONF_STATUS_FLAG, G_CONF_MATCH_FAKE, G_CONF_XREF_FAKE )
AND ORGANIZATION_ID = l_org_id;
Debug_Conc_Log( 'Resolve_Child_Entities - Stamped request_id and final process flag on ' || SQL%ROWCOUNT || ' inserted rows' );
UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
SET (STYLE_ITEM_FLAG, STYLE_ITEM_ID) = (SELECT
DECODE(MAX(NVL(msik.STYLE_ITEM_FLAG, '$NULL$')),
'$NULL$', msii.STYLE_ITEM_FLAG,
NULL, msii.STYLE_ITEM_FLAG,
'N', 'N',
'Y', 'Y'
),
NVL(MAX(msik.STYLE_ITEM_ID), msii.STYLE_ITEM_ID)
FROM MTL_SYSTEM_ITEMS_KFV msik, MTL_PARAMETERS mp
WHERE (msii.INVENTORY_ITEM_ID = msik.INVENTORY_ITEM_ID
OR msii.ITEM_NUMBER = msik.CONCATENATED_SEGMENTS)
AND msik.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID
AND msii.ORGANIZATION_ID = mp.ORGANIZATION_ID
)
WHERE SET_PROCESS_ID = p_data_set_id
AND PROCESS_FLAG = 1;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET REQUEST_ID = p_request_id
WHERE MSII.ROWID = p_target_rowid
AND PROCESS_FLAG = 7
AND ( SOURCE_SYSTEM_ID IS NOT NULL
OR SOURCE_SYSTEM_REFERENCE IS NOT NULL
);
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET REQUEST_ID = p_request_id
WHERE ROWID = G_MSII_REIMPORT_ROWS( l_index );
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET REQUEST_ID = p_request_id
WHERE ROWID = p_target_rowid
RETURNING ORGANIZATION_ID, TRANSACTION_ID
INTO l_org_id, l_transaction_id;
SELECT BATCH.SOURCE_SYSTEM_ID
, BATCH.ORGANIZATION_ID
, NVL( OPT.IMPORT_XREF_ONLY, 'N' )
INTO l_batch_ss_id
, l_org_id
, l_ssxref_only
FROM EGO_IMPORT_BATCHES_B BATCH, EGO_IMPORT_OPTION_SETS OPT
WHERE BATCH.BATCH_ID = p_batch_id
AND BATCH.ORGANIZATION_ID = p_organization_id
AND BATCH.BATCH_ID = OPT.BATCH_ID;
SAVEPOINT do_prepare_master_row; -- ENSURES THAT THE UPDATES ARE AN ATOMIC UNIT WITHIN THE TRANSACTION
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET PROCESS_FLAG = CASE PROCESS_FLAG
WHEN 3 THEN 1
-- WHEN 7 THEN G_IOI_STAMP_REQUEST_ID_FLAG
ELSE PROCESS_FLAG END
WHERE SET_PROCESS_ID = p_batch_id
AND ORGANIZATION_ID = p_organization_id
AND MSII.ROWID = p_target_rowid
AND PROCESS_FLAG IN ( 3, 7 )
AND ( SOURCE_SYSTEM_ID IS NULL
OR SOURCE_SYSTEM_ID = l_batch_ss_id
)
RETURNING
MSII.PROCESS_FLAG
, MSII.REQUEST_ID
, MSII.INVENTORY_ITEM_ID
, MSII.ITEM_NUMBER
, MSII.ORGANIZATION_ID
INTO
l_process_flag
, l_request_id
, l_item_id
, l_item_number
, l_org_id;
x_return_code := 10; -- update statement found too many matching master rows, or not enough
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET PROCESS_FLAG = 1
WHERE ( INVENTORY_ITEM_ID = l_item_id
OR ( ITEM_NUMBER = l_item_number
AND INVENTORY_ITEM_ID IS NULL
)
)
AND PROCESS_FLAG = 3
AND SET_PROCESS_ID = p_batch_id
AND REQUEST_ID = l_request_id
AND ORGANIZATION_ID IN ( SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = l_org_id
AND MP.ORGANIZATION_ID <> l_org_id
);
UPDATE MTL_ITEM_REVISIONS_INTERFACE miri
SET PROCESS_FLAG = 1
WHERE ( INVENTORY_ITEM_ID = l_item_id
OR ( ITEM_NUMBER = l_item_number
AND INVENTORY_ITEM_ID IS NULL
)
)
AND PROCESS_FLAG = 3
AND SET_PROCESS_ID = p_batch_id
AND REQUEST_ID = l_request_id
AND ORGANIZATION_ID IN ( SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = l_org_id
);
UPDATE MTL_ITEM_CATEGORIES_INTERFACE mici
SET PROCESS_FLAG = 1
WHERE ( INVENTORY_ITEM_ID = l_item_id
OR ( ITEM_NUMBER = l_item_number
AND INVENTORY_ITEM_ID IS NULL
)
)
AND PROCESS_FLAG = 3
AND SET_PROCESS_ID = p_batch_id
AND REQUEST_ID = l_request_id
AND ORGANIZATION_ID IN ( SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = l_org_id
);
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET PROCESS_STATUS = 1
WHERE ( INVENTORY_ITEM_ID = l_item_id
OR ( ITEM_NUMBER = l_item_number
AND INVENTORY_ITEM_ID IS NULL
)
)
AND PROCESS_STATUS = 3
AND DATA_SET_ID = p_batch_id
AND REQUEST_ID = l_request_id
AND ORGANIZATION_ID IN ( SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = l_org_id
);
UPDATE EGO_ITEM_PEOPLE_INTF eipi
SET PROCESS_STATUS = 1
WHERE ( INVENTORY_ITEM_ID = l_item_id
OR ( ITEM_NUMBER = l_item_number
AND INVENTORY_ITEM_ID IS NULL
)
)
AND PROCESS_STATUS = 3
AND DATA_SET_ID = p_batch_id
AND REQUEST_ID = l_request_id
AND ORGANIZATION_ID IN ( SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = l_org_id
);
UPDATE EGO_AML_INTF
SET PROCESS_FLAG = 1
WHERE ( INVENTORY_ITEM_ID = l_item_id
OR ( ITEM_NUMBER = l_item_number
AND INVENTORY_ITEM_ID IS NULL
)
)
AND PROCESS_FLAG = 3
AND DATA_SET_ID = p_batch_id
AND REQUEST_ID = l_request_id
AND ORGANIZATION_ID IN ( SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = l_org_id
);
UPDATE EGO_ITEM_ASSOCIATIONS_INTF
SET PROCESS_FLAG = 1
WHERE ( INVENTORY_ITEM_ID = l_item_id
OR ( ITEM_NUMBER = l_item_number
AND INVENTORY_ITEM_ID IS NULL
)
)
AND PROCESS_FLAG = 3
AND BATCH_ID = p_batch_id
AND REQUEST_ID = l_request_id
AND ORGANIZATION_ID IN ( SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = l_org_id
);
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET PROCESS_FLAG = 1
WHERE ITEM_NUMBER = l_item_number
AND PROCESS_FLAG = 3
AND SET_PROCESS_ID = p_batch_id
AND REQUEST_ID = l_request_id
AND ORGANIZATION_ID IN ( SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = l_org_id
AND MP.ORGANIZATION_ID <> l_org_id
);
UPDATE MTL_ITEM_REVISIONS_INTERFACE miri
SET PROCESS_FLAG = 1
WHERE ITEM_NUMBER = l_item_number
AND PROCESS_FLAG = 3
AND SET_PROCESS_ID = p_batch_id
AND REQUEST_ID = l_request_id
AND ORGANIZATION_ID IN ( SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = l_org_id
);
UPDATE MTL_ITEM_CATEGORIES_INTERFACE mici
SET PROCESS_FLAG = 1
WHERE ITEM_NUMBER = l_item_number
AND PROCESS_FLAG = 3
AND SET_PROCESS_ID = p_batch_id
AND REQUEST_ID = l_request_id
AND ORGANIZATION_ID IN ( SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = l_org_id
);
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET PROCESS_STATUS = 1
WHERE ITEM_NUMBER = l_item_number
AND PROCESS_STATUS = 3
AND DATA_SET_ID = p_batch_id
AND REQUEST_ID = l_request_id
AND ORGANIZATION_ID IN ( SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = l_org_id
);
UPDATE EGO_ITEM_PEOPLE_INTF eipi
SET PROCESS_STATUS = 1
WHERE ITEM_NUMBER = l_item_number
AND PROCESS_STATUS = 3
AND DATA_SET_ID = p_batch_id
AND REQUEST_ID = l_request_id
AND ORGANIZATION_ID IN ( SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = l_org_id
);
UPDATE EGO_AML_INTF
SET PROCESS_FLAG = 1
WHERE ITEM_NUMBER = l_item_number
AND PROCESS_FLAG = 3
AND DATA_SET_ID = p_batch_id
AND REQUEST_ID = l_request_id
AND ORGANIZATION_ID IN ( SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = l_org_id
);
UPDATE EGO_ITEM_ASSOCIATIONS_INTF
SET PROCESS_FLAG = 1
WHERE ITEM_NUMBER = l_item_number
AND PROCESS_FLAG = 3
AND BATCH_ID = p_batch_id
AND REQUEST_ID = l_request_id
AND ORGANIZATION_ID IN ( SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = l_org_id
);
SAVEPOINT do_prepare_master_row; -- ENSURES THAT THE UPDATES ARE AN ATOMIC UNIT WITHIN THE TRANSACTION
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET PROCESS_FLAG = CASE PROCESS_FLAG
WHEN 3 THEN l_reimport_process_status
-- WHEN 7 THEN G_IOI_STAMP_REQUEST_ID_FLAG
ELSE PROCESS_FLAG END
WHERE SET_PROCESS_ID = p_batch_id
AND ORGANIZATION_ID = p_organization_id
AND MSII.ROWID = p_target_rowid
AND PROCESS_FLAG IN ( 3, 7 )
AND SOURCE_SYSTEM_ID IS NOT NULL
AND SOURCE_SYSTEM_REFERENCE IS NOT NULL
RETURNING
MSII.PROCESS_FLAG
, MSII.REQUEST_ID
, MSII.SOURCE_SYSTEM_ID
, MSII.SOURCE_SYSTEM_REFERENCE
, MSII.SOURCE_SYSTEM_REFERENCE_DESC
, MSII.ORGANIZATION_ID
, MSII.INVENTORY_ITEM_ID
, MSII.ITEM_NUMBER
INTO
l_process_flag
, l_request_id
, l_ss_id
, l_ssr
, l_ssr_desc
, l_org_id
, l_item_id
, l_item_number;
SELECT ORGANIZATION_CODE
INTO l_org_code
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = l_org_id;
SELECT MSIK.CONCATENATED_SEGMENTS
, MSIK.INVENTORY_ITEM_ID
, MCR.DESCRIPTION
INTO l_xref_item_number
, l_xref_item_id
, l_xref_desc
FROM MTL_CROSS_REFERENCES MCR,
MTL_SYSTEM_ITEMS_KFV MSIK
WHERE MCR.INVENTORY_ITEM_ID = MSIK.INVENTORY_ITEM_ID
AND MSIK.ORGANIZATION_ID = l_org_id
AND MCR.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
AND MCR.SOURCE_SYSTEM_ID = l_ss_id
AND MCR.CROSS_REFERENCE = l_ssr
AND SYSDATE BETWEEN NVL(MCR.START_DATE_ACTIVE, SYSDATE-1) AND NVL(MCR.END_DATE_ACTIVE, SYSDATE+1);
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET PROCESS_FLAG = 3
WHERE ROWID = p_target_rowid;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET PROCESS_FLAG = 3
WHERE ROWID = p_target_rowid;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET PROCESS_FLAG = 3
WHERE ROWID = p_target_rowid;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET PROCESS_FLAG = 3
WHERE ROWID = p_target_rowid;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET PROCESS_FLAG = 1
WHERE SOURCE_SYSTEM_ID = l_ss_id
AND SOURCE_SYSTEM_REFERENCE = l_ssr
AND PROCESS_FLAG = 3
AND SET_PROCESS_ID = p_batch_id
AND REQUEST_ID = l_request_id
AND ORGANIZATION_ID IN ( SELECT MP.ORGANIZATION_ID -- org assignment
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = l_org_id
AND MP.ORGANIZATION_ID <> l_org_id
);
UPDATE MTL_ITEM_REVISIONS_INTERFACE miri
SET PROCESS_FLAG = 1
WHERE SOURCE_SYSTEM_ID = l_ss_id
AND SOURCE_SYSTEM_REFERENCE = l_ssr
AND PROCESS_FLAG = 3
AND SET_PROCESS_ID = p_batch_id
AND REQUEST_ID = l_request_id
AND ORGANIZATION_ID IN ( SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = l_org_id
);
UPDATE MTL_ITEM_CATEGORIES_INTERFACE mici
SET PROCESS_FLAG = 1
WHERE SOURCE_SYSTEM_ID = l_ss_id
AND SOURCE_SYSTEM_REFERENCE = l_ssr
AND PROCESS_FLAG = 3
AND SET_PROCESS_ID = p_batch_id
AND REQUEST_ID = l_request_id
AND ORGANIZATION_ID IN ( SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = l_org_id
);
UPDATE EGO_ITM_USR_ATTR_INTRFC
SET PROCESS_STATUS = 1
WHERE SOURCE_SYSTEM_ID = l_ss_id
AND SOURCE_SYSTEM_REFERENCE = l_ssr
AND PROCESS_STATUS = 3
AND DATA_SET_ID = p_batch_id
AND REQUEST_ID = l_request_id
AND ORGANIZATION_ID IN ( SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = l_org_id
);
UPDATE EGO_ITEM_PEOPLE_INTF eipi
SET PROCESS_STATUS = 1
WHERE SOURCE_SYSTEM_ID = l_ss_id
AND SOURCE_SYSTEM_REFERENCE = l_ssr
AND PROCESS_STATUS = 3
AND DATA_SET_ID = p_batch_id
AND REQUEST_ID = l_request_id
AND ORGANIZATION_ID IN ( SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = l_org_id
);
UPDATE EGO_AML_INTF
SET PROCESS_FLAG = 1
WHERE SOURCE_SYSTEM_ID = l_ss_id
AND SOURCE_SYSTEM_REFERENCE = l_ssr
AND PROCESS_FLAG = 3
AND DATA_SET_ID = p_batch_id
AND REQUEST_ID = l_request_id
AND ORGANIZATION_ID IN ( SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = l_org_id
);
UPDATE EGO_ITEM_ASSOCIATIONS_INTF
SET PROCESS_FLAG = 1
WHERE SOURCE_SYSTEM_ID = l_ss_id
AND SOURCE_SYSTEM_REFERENCE = l_ssr
AND PROCESS_FLAG = 3
AND BATCH_ID = p_batch_id
AND REQUEST_ID = l_request_id
AND ORGANIZATION_ID IN ( SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = l_org_id
);
WHEN OTHERS THEN -- unanticipated error ( probably during update )
ROLLBACK TO do_prepare_master_row;
SELECT
(CASE WHEN ( (crimp.PHASE_CODE IN ('P', 'I', 'R') AND (crimp.REQUESTED_START_DATE <= SYSDATE) )
OR crmatch.PHASE_CODE IN ('P', 'I', 'R')
)
THEN 'P'
ELSE impbat.BATCH_STATUS
END
) BATCH_STATUS
INTO l_batch_status
FROM
EGO_IMPORT_BATCHES_B impbat,
FND_CONCURRENT_REQUESTS crimp,
FND_CONCURRENT_REQUESTS crmatch
WHERE impbat.BATCH_ID = p_batch_id
AND impbat.LAST_MATCH_REQUEST_ID = crmatch.REQUEST_ID (+)
AND impbat.LAST_IMPORT_REQUEST_ID = crimp.REQUEST_ID (+);
SELECT MAX( EIUAI.REVISION ) INTO l_rev_code
FROM EGO_ITM_USR_ATTR_INTRFC EIUAI
WHERE EIUAI.SOURCE_SYSTEM_REFERENCE = p_source_system_reference
AND EIUAI.SOURCE_SYSTEM_ID = p_source_system_id
AND EIUAI.ORGANIZATION_ID = p_organization_id
AND EIUAI.DATA_SET_ID = p_batch_id
AND EIUAI.REVISION IS NOT NULL
AND EIUAI.PROCESS_STATUS IN ( 3,4 )
AND EIUAI.REQUEST_ID = p_request_id
AND EXISTS( SELECT NULL -- SEE DEFINITION OF EGO_OBJ_ATTR_GRP_ASSOCS_V
FROM EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT
, EGO_OBJ_AG_ASSOCS_B A
WHERE
-- CHECK FOR REVISION LEVEL GROUPS ONLY
A.DATA_LEVEL = 'ITEM_REVISION_LEVEL'
AND FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = NVL( EIUAI.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
AND FL_CTX_EXT.APPLICATION_ID = 431
AND FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = EIUAI.ATTR_GROUP_INT_NAME
AND A.ATTR_GROUP_ID = FL_CTX_EXT.ATTR_GROUP_ID
-- TO ENSURE A(OBJECT_ID, CLASS_CODE, ATTR_GROUP) IDX USED:
AND A.CLASSIFICATION_CODE IS NOT NULL
AND A.OBJECT_ID IS NOT NULL
AND ROWNUM = 1
);
SELECT MAX( EIUAI.REVISION ) INTO l_rev_code
FROM EGO_ITM_USR_ATTR_INTRFC EIUAI
WHERE EIUAI.SOURCE_SYSTEM_REFERENCE = p_source_system_reference
AND EIUAI.SOURCE_SYSTEM_ID = p_source_system_id
AND EIUAI.ORGANIZATION_ID = p_organization_id
AND EIUAI.DATA_SET_ID = p_batch_id
AND EIUAI.REVISION IS NOT NULL
AND EIUAI.PROCESS_STATUS = 0
AND EXISTS( SELECT NULL -- SEE DEFINITION OF EGO_OBJ_ATTR_GRP_ASSOCS_V
FROM EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT
, EGO_OBJ_AG_ASSOCS_B A
WHERE
-- CHECK FOR REVISION LEVEL GROUPS ONLY
A.DATA_LEVEL = 'ITEM_REVISION_LEVEL'
AND FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = NVL( EIUAI.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
AND FL_CTX_EXT.APPLICATION_ID = 431
AND FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = EIUAI.ATTR_GROUP_INT_NAME
AND A.ATTR_GROUP_ID = FL_CTX_EXT.ATTR_GROUP_ID
-- TO ENSURE A(OBJECT_ID, CLASS_CODE, ATTR_GROUP) IDX USED:
AND A.CLASSIFICATION_CODE IS NOT NULL
AND A.OBJECT_ID IS NOT NULL
AND ROWNUM = 1
);
SELECT MAX( EIUAI.REVISION )
INTO l_rev_code
FROM EGO_ITM_USR_ATTR_INTRFC EIUAI
WHERE EIUAI.ITEM_NUMBER = p_item_number
AND EIUAI.ORGANIZATION_ID = p_organization_id
AND EIUAI.DATA_SET_ID = p_batch_id
AND EIUAI.REVISION IS NOT NULL
AND EIUAI.PROCESS_STATUS IN ( 3, 4 )
AND EIUAI.REQUEST_ID = p_request_id
AND EXISTS( SELECT NULL -- SEE DEFINITION OF EGO_OBJ_ATTR_GRP_ASSOCS_V
FROM EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT
, EGO_OBJ_AG_ASSOCS_B A
WHERE
-- CHECK FOR REVISION LEVEL GROUPS ONLY
A.DATA_LEVEL = 'ITEM_REVISION_LEVEL'
AND FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = NVL( EIUAI.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
AND FL_CTX_EXT.APPLICATION_ID = 431
AND FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = EIUAI.ATTR_GROUP_INT_NAME
AND A.ATTR_GROUP_ID = FL_CTX_EXT.ATTR_GROUP_ID
-- TO ENSURE A(OBJECT_ID, CLASS_CODE, ATTR_GROUP) IDX USED:
AND A.CLASSIFICATION_CODE IS NOT NULL
AND A.OBJECT_ID IS NOT NULL
AND ROWNUM = 1
);
SELECT MAX( EIUAI.REVISION )
INTO l_rev_code
FROM EGO_ITM_USR_ATTR_INTRFC EIUAI
WHERE EIUAI.ITEM_NUMBER = p_item_number
AND EIUAI.ORGANIZATION_ID = p_organization_id
AND EIUAI.DATA_SET_ID = p_batch_id
AND EIUAI.REVISION IS NOT NULL
AND EIUAI.PROCESS_STATUS = 1
AND EXISTS( SELECT NULL -- SEE DEFINITION OF EGO_OBJ_ATTR_GRP_ASSOCS_V
FROM EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT
, EGO_OBJ_AG_ASSOCS_B A
WHERE
-- CHECK FOR REVISION LEVEL GROUPS ONLY
A.DATA_LEVEL = 'ITEM_REVISION_LEVEL'
AND FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = NVL( EIUAI.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
AND FL_CTX_EXT.APPLICATION_ID = 431
AND FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = EIUAI.ATTR_GROUP_INT_NAME
AND A.ATTR_GROUP_ID = FL_CTX_EXT.ATTR_GROUP_ID
-- TO ENSURE A(OBJECT_ID, CLASS_CODE, ATTR_GROUP) IDX USED:
AND A.CLASSIFICATION_CODE IS NOT NULL
AND A.OBJECT_ID IS NOT NULL
AND ROWNUM = 1
);
SELECT MAX( EIUAI.REVISION )
INTO l_rev_code
FROM EGO_ITM_USR_ATTR_INTRFC EIUAI
WHERE
( ( EIUAI.INVENTORY_ITEM_ID IS NULL
AND EIUAI.ITEM_NUMBER = p_item_number
)
OR EIUAI.INVENTORY_ITEM_ID = p_inventory_item_id
)
AND EIUAI.ORGANIZATION_ID = p_organization_id
AND EIUAI.DATA_SET_ID = p_batch_id
AND EIUAI.REVISION IS NOT NULL
AND EIUAI.PROCESS_STATUS IN ( 3, 4 )
AND EIUAI.REQUEST_ID = p_request_id
AND EXISTS( SELECT NULL -- SEE DEFINITION OF EGO_OBJ_ATTR_GRP_ASSOCS_V
FROM EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT
, EGO_OBJ_AG_ASSOCS_B A
WHERE
-- CHECK FOR REVISION LEVEL GROUPS ONLY
A.DATA_LEVEL = 'ITEM_REVISION_LEVEL'
AND FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = NVL( EIUAI.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
AND FL_CTX_EXT.APPLICATION_ID = 431
AND FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = EIUAI.ATTR_GROUP_INT_NAME
AND A.ATTR_GROUP_ID = FL_CTX_EXT.ATTR_GROUP_ID
-- TO ENSURE A(OBJECT_ID, CLASS_CODE, ATTR_GROUP) IDX USED:
AND A.CLASSIFICATION_CODE IS NOT NULL
AND A.OBJECT_ID IS NOT NULL
AND ROWNUM = 1
);
SELECT MAX( EIUAI.REVISION )
INTO l_rev_code
FROM EGO_ITM_USR_ATTR_INTRFC EIUAI
WHERE
( ( EIUAI.INVENTORY_ITEM_ID IS NULL
AND EIUAI.ITEM_NUMBER = p_item_number
)
OR EIUAI.INVENTORY_ITEM_ID = p_inventory_item_id
)
AND EIUAI.ORGANIZATION_ID = p_organization_id
AND EIUAI.DATA_SET_ID = p_batch_id
AND EIUAI.REVISION IS NOT NULL
AND EIUAI.PROCESS_STATUS = 1
AND EXISTS( SELECT NULL -- SEE DEFINITION OF EGO_OBJ_ATTR_GRP_ASSOCS_V
FROM EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT
, EGO_OBJ_AG_ASSOCS_B A
WHERE
-- CHECK FOR REVISION LEVEL GROUPS ONLY
A.DATA_LEVEL = 'ITEM_REVISION_LEVEL'
AND FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = NVL( EIUAI.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE )
AND FL_CTX_EXT.APPLICATION_ID = 431
AND FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = EIUAI.ATTR_GROUP_INT_NAME
AND A.ATTR_GROUP_ID = FL_CTX_EXT.ATTR_GROUP_ID
-- TO ENSURE A(OBJECT_ID, CLASS_CODE, ATTR_GROUP) IDX USED:
AND A.CLASSIFICATION_CODE IS NOT NULL
AND A.OBJECT_ID IS NOT NULL
AND ROWNUM = 1
);
SELECT MAX( REVISION ) KEEP ( DENSE_RANK FIRST ORDER BY MIRI.EFFECTIVITY_DATE DESC NULLS LAST , MIRI.REVISION DESC NULLS LAST )
INTO l_revision
FROM
MTL_ITEM_REVISIONS_INTERFACE MIRI
WHERE MIRI.SET_PROCESS_id = p_batch_id
AND MIRI.SOURCE_SYSTEM_ID = p_source_system_id
AND MIRI.SOURCE_SYSTEM_REFERENCE = p_source_system_reference
AND MIRI.ORGANIZATION_ID = p_organization_id
AND MIRI.PROCESS_FLAG = 0;
SELECT MAX( REVISION ) KEEP ( DENSE_RANK FIRST ORDER BY MIRI.EFFECTIVITY_DATE DESC NULLS LAST , MIRI.REVISION DESC NULLS LAST )
INTO l_revision
FROM
MTL_ITEM_REVISIONS_INTERFACE MIRI
WHERE
MIRI.SET_PROCESS_id = p_batch_id
AND MIRI.SOURCE_SYSTEM_ID = p_source_system_id
AND MIRI.SOURCE_SYSTEM_REFERENCE = p_source_system_reference
AND MIRI.ORGANIZATION_ID = p_organization_id
AND MIRI.REQUEST_ID = p_request_id
AND MIRI.PROCESS_FLAG IN (3, 7);
SELECT MAX( REVISION ) KEEP ( DENSE_RANK FIRST
ORDER BY MIRI.EFFECTIVITY_DATE DESC NULLS LAST , MIRI.REVISION DESC NULLS LAST )
INTO l_revision
FROM MTL_ITEM_REVISIONS_INTERFACE MIRI
WHERE MIRI.SET_PROCESS_id = p_batch_id
AND MIRI.ITEM_NUMBER = p_item_number
AND MIRI.ORGANIZATION_ID = p_organization_id
AND MIRI.PROCESS_FLAG = 1;
SELECT MAX( REVISION ) KEEP ( DENSE_RANK FIRST
ORDER BY MIRI.EFFECTIVITY_DATE DESC NULLS LAST , MIRI.REVISION DESC NULLS LAST )
INTO l_revision
FROM MTL_ITEM_REVISIONS_INTERFACE MIRI
WHERE MIRI.SET_PROCESS_id = p_batch_id
AND MIRI.ITEM_NUMBER = p_item_number
AND MIRI.ORGANIZATION_ID = p_organization_id
AND MIRI.REQUEST_ID = p_request_id
AND MIRI.PROCESS_FLAG IN (3, 7);
SELECT MAX( REVISION ) KEEP ( DENSE_RANK FIRST
ORDER BY MIRI.EFFECTIVITY_DATE DESC NULLS LAST , MIRI.REVISION DESC NULLS LAST )
INTO l_revision
FROM MTL_ITEM_REVISIONS_INTERFACE MIRI
WHERE MIRI.SET_PROCESS_id = p_batch_id
AND ( ( MIRI.INVENTORY_ITEM_ID IS NULL AND MIRI.ITEM_NUMBER = p_item_number )
OR MIRI.INVENTORY_ITEM_ID = p_inventory_item_id
)
AND MIRI.ORGANIZATION_ID = p_organization_id
AND MIRI.PROCESS_FLAG = 1;
SELECT MAX( REVISION ) KEEP ( DENSE_RANK FIRST
ORDER BY MIRI.EFFECTIVITY_DATE DESC NULLS LAST , MIRI.REVISION DESC NULLS LAST )
INTO l_revision
FROM MTL_ITEM_REVISIONS_INTERFACE MIRI
WHERE MIRI.SET_PROCESS_id = p_batch_id
AND ( ( MIRI.INVENTORY_ITEM_ID IS NULL AND MIRI.ITEM_NUMBER = p_item_number )
OR MIRI.INVENTORY_ITEM_ID = p_inventory_item_id
)
AND MIRI.ORGANIZATION_ID = p_organization_id
AND PROCESS_FLAG IN ( 3, 7 );
SELECT NVL( EIUAI.ATTR_DISP_VALUE, EIUAI.ATTR_VALUE_STR ) INTO l_return_value
FROM EGO_ITM_USR_ATTR_INTRFC EIUAI
WHERE
NVL( EIUAI.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE ) = p_attr_group_type
AND EIUAI.ATTR_GROUP_INT_NAME = p_attr_group_name
AND EIUAI.ATTR_INT_NAME = p_attr_name
AND EIUAI.SOURCE_SYSTEM_REFERENCE = p_source_system_reference
AND EIUAI.SOURCE_SYSTEM_ID = p_source_system_id
AND EIUAI.ORGANIZATION_ID = p_organization_id
AND EIUAI.DATA_SET_ID = p_batch_id
AND EIUAI.REVISION = l_revision_code
AND rownum < 2
;
SELECT NVL( EIUAI.ATTR_DISP_VALUE, TO_CHAR( EIUAI.ATTR_VALUE_NUM ) ) INTO l_return_value
FROM EGO_ITM_USR_ATTR_INTRFC EIUAI
WHERE
NVL( EIUAI.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE ) = p_attr_group_type
AND EIUAI.ATTR_GROUP_INT_NAME = p_attr_group_name
AND EIUAI.ATTR_INT_NAME = p_attr_name
AND EIUAI.SOURCE_SYSTEM_REFERENCE = p_source_system_reference
AND EIUAI.SOURCE_SYSTEM_ID = p_source_system_id
AND EIUAI.ORGANIZATION_ID = p_organization_id
AND EIUAI.DATA_SET_ID = p_batch_id
AND EIUAI.REVISION = l_revision_code
AND rownum < 2
;
SELECT NVL( EIUAI.ATTR_DISP_VALUE, TO_CHAR( EIUAI.ATTR_VALUE_DATE ) ) INTO l_return_value
FROM EGO_ITM_USR_ATTR_INTRFC EIUAI
WHERE
NVL( EIUAI.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE ) = p_attr_group_type
AND EIUAI.ATTR_GROUP_INT_NAME = p_attr_group_name
AND EIUAI.ATTR_INT_NAME = p_attr_name
AND EIUAI.SOURCE_SYSTEM_REFERENCE = p_source_system_reference
AND EIUAI.SOURCE_SYSTEM_ID = p_source_system_id
AND EIUAI.ORGANIZATION_ID = p_organization_id
AND EIUAI.DATA_SET_ID = p_batch_id
AND EIUAI.REVISION = l_revision_code
AND rownum < 2
;
SELECT NVL( EIUAI.ATTR_DISP_VALUE, EIUAI.ATTR_VALUE_STR ) INTO l_return_value
FROM EGO_ITM_USR_ATTR_INTRFC EIUAI
WHERE
NVL( EIUAI.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE ) = p_attr_group_type
AND EIUAI.ATTR_GROUP_INT_NAME = p_attr_group_name
AND EIUAI.ATTR_INT_NAME = p_attr_name
AND EIUAI.SOURCE_SYSTEM_REFERENCE = p_source_system_reference
AND EIUAI.SOURCE_SYSTEM_ID = p_source_system_id
AND EIUAI.ORGANIZATION_ID = p_organization_id
AND EIUAI.DATA_SET_ID = p_batch_id
AND EIUAI.REVISION IS NULL
AND rownum < 2
;
SELECT NVL( EIUAI.ATTR_DISP_VALUE, TO_CHAR( EIUAI.ATTR_VALUE_NUM ) ) INTO l_return_value
FROM EGO_ITM_USR_ATTR_INTRFC EIUAI
WHERE
NVL( EIUAI.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE ) = p_attr_group_type
AND EIUAI.ATTR_GROUP_INT_NAME = p_attr_group_name
AND EIUAI.ATTR_INT_NAME = p_attr_name
AND EIUAI.SOURCE_SYSTEM_REFERENCE = p_source_system_reference
AND EIUAI.SOURCE_SYSTEM_ID = p_source_system_id
AND EIUAI.ORGANIZATION_ID = p_organization_id
AND EIUAI.DATA_SET_ID = p_batch_id
AND EIUAI.REVISION IS NULL
AND rownum < 2
;
SELECT NVL( EIUAI.ATTR_DISP_VALUE, TO_CHAR( EIUAI.ATTR_VALUE_DATE ) ) INTO l_return_value
FROM EGO_ITM_USR_ATTR_INTRFC EIUAI
WHERE
NVL( EIUAI.ATTR_GROUP_TYPE, G_DEFAULT_ATTR_GROUP_TYPE ) = p_attr_group_type
AND EIUAI.ATTR_GROUP_INT_NAME = p_attr_group_name
AND EIUAI.ATTR_INT_NAME = p_attr_name
AND EIUAI.SOURCE_SYSTEM_REFERENCE = p_source_system_reference
AND EIUAI.SOURCE_SYSTEM_ID = p_source_system_id
AND EIUAI.ORGANIZATION_ID = p_organization_id
AND EIUAI.DATA_SET_ID = p_batch_id
AND EIUAI.REVISION IS NULL
AND rownum < 2
;
l_select_sql VARCHAR2( 200 );
l_select_sql := CASE
WHEN p_attr_type = G_DATE_DATA_TYPE OR p_attr_type = G_DATE_TIME_DATA_TYPE THEN
'DECODE( EIUAI.ATTR_VALUE_DATE'
||', NULL, ' || l_miss_str
||', '|| EGO_USER_ATTRS_BULK_PVT.G_NULL_DATE_VAL ||', '|| l_miss_str
||', TO_CHAR( EIUAI.ATTR_VALUE_DATE, '''|| l_date_format || ''')'
||')'
WHEN p_attr_type = G_NUMBER_DATA_TYPE THEN
-- ignore UOM column completely ... the assumption is that this
-- code is called for junk values only
'DECODE( EIUAI.ATTR_VALUE_NUM'
||', NULL, ' || l_miss_str
||', TO_CHAR( EIUAI.ATTR_VALUE_NUM )'
||')'
WHEN p_attr_type = G_TEXT_DATA_TYPE THEN
'NVL( EIUAI.ATTR_VALUE_STR, '|| l_miss_str || ')'
END;
l_select_sql := ' EIUAI.ATTR_DISP_VALUE ';
SELECT ' || l_select_sql
||' FROM EGO_ITM_USR_ATTR_INTRFC EIUAI '
||' WHERE '
||' NVL( EIUAI.ATTR_GROUP_TYPE, :default_attr_grp ) = :attr_grp_type'
||' AND EIUAI.ATTR_GROUP_INT_NAME = :attr_grp'
||' AND EIUAI.ATTR_INT_NAME = :attr '
|| l_join_sql
||' AND EIUAI.ORGANIZATION_ID = :org_id '
||' AND EIUAI.DATA_SET_ID = :batch_id '
|| l_do_procd_rows_sql
||' AND DECODE( EIUAI.REVISION, :rev_code, 1, 0 ) = 1 '
||' AND rownum = 1 '
;
l_select_sql VARCHAR2( 300 );
l_select_sql := ' EIUAI.ATTR_VALUE_DATE ';
l_select_sql := ' NVL2( EIUAI.ATTR_DISP_VALUE '
|| ' , EGO_IMPORT_PVT.wrapped_to_date( EIUAI.ATTR_DISP_VALUE ) '
|| ' , CASE WHEN EIUAI.ATTR_VALUE_DATE IS NULL '
|| 'OR EIUAI.ATTR_VALUE_DATE = TO_DATE(''1'',''J'') THEN ' || G_EXCEL_MISS_DATE_STR
|| ' ELSE EIUAI.ATTR_VALUE_DATE END'
|| ' ) '
;
SELECT ' || l_select_sql
||' FROM EGO_ITM_USR_ATTR_INTRFC EIUAI '
||' WHERE '
||' NVL( EIUAI.ATTR_GROUP_TYPE, :default_attr_grp ) = :attr_grp_type'
||' AND EIUAI.ATTR_GROUP_INT_NAME = :attr_grp'
||' AND EIUAI.ATTR_INT_NAME = :attr '
|| l_join_sql
||' AND EIUAI.ORGANIZATION_ID = :org_id '
||' AND EIUAI.DATA_SET_ID = :batch_id '
|| l_do_procd_rows_sql
||' AND DECODE( EIUAI.REVISION, :rev_code, 1, 0 ) = 1 '
||' AND rownum = 1 '
;
l_select_sql VARCHAR2( 200 );
l_select_sql := ' EIUAI.ATTR_VALUE_STR ';
l_select_sql := ' COALESCE( EIUAI.ATTR_DISP_VALUE, EIUAI.ATTR_VALUE_STR, '''|| G_EXCEL_MISS_CHAR_VAL ||''' ) ';
SELECT ' || l_select_sql
|| '
FROM EGO_ITM_USR_ATTR_INTRFC EIUAI
WHERE
NVL( EIUAI.ATTR_GROUP_TYPE, :default_attr_grp ) = :attr_grp_type
AND EIUAI.ATTR_GROUP_INT_NAME = :attr_grp
AND EIUAI.ATTR_INT_NAME = :attr '
|| l_join_sql
|| '
AND EIUAI.ORGANIZATION_ID = :org_id
AND EIUAI.DATA_SET_ID = :batch_id '
|| l_do_procd_rows_sql
/* || '
AND EIUAI.REVISION '
|| CASE l_revision_code
WHEN NULL THEN 'IS NULL '
ELSE '= :rev_code '
END */
|| '
AND DECODE( EIUAI.REVISION, :rev_code, 1, 0 ) = 1
AND rownum = 1
';
l_select_sql VARCHAR2( 200 );
l_select_sql := ' EIUAI.ATTR_VALUE_NUM ';
l_select_sql := ' NVL2( EIUAI.ATTR_DISP_VALUE '
|| ' , to_number( EIUAI.ATTR_DISP_VALUE ) '
|| ' , NVL( EIUAI.ATTR_VALUE_NUM, ' || G_EXCEL_MISS_NUM_STR
|| ' ) ) '
;
SELECT ' || l_select_sql
|| ', ATTR_VALUE_UOM
, ATTR_UOM_DISP_VALUE
FROM EGO_ITM_USR_ATTR_INTRFC EIUAI
WHERE
NVL( EIUAI.ATTR_GROUP_TYPE, :default_attr_grp ) = :attr_grp_type
AND EIUAI.ATTR_GROUP_INT_NAME = :attr_grp
AND EIUAI.ATTR_INT_NAME = :attr '
|| l_join_sql
|| '
AND EIUAI.ORGANIZATION_ID = :org_id
AND EIUAI.DATA_SET_ID = :batch_id '
|| l_do_procd_rows_sql
|| '
AND DECODE( EIUAI.REVISION, :rev_code, 1, 0 ) = 1
AND rownum = 1
';
SELECT uom_code INTO l_uom_code
FROM mtl_units_of_measure_vl
WHERE unit_of_measure = p_from_uom_value;
l_update_flag BOOLEAN;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET ORGANIZATION_ID =
NVL(ORGANIZATION_ID,
(SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_CODE = MSII.ORGANIZATION_CODE))
,ITEM_CATALOG_GROUP_ID =
NVL(ITEM_CATALOG_GROUP_ID,
(SELECT MICG.ITEM_CATALOG_GROUP_ID
FROM MTL_ITEM_CATALOG_GROUPS_B_KFV MICG
WHERE MICG.CONCATENATED_SEGMENTS = MSII.ITEM_CATALOG_GROUP_NAME))
WHERE SET_PROCESS_ID = p_batch_id
AND SOURCE_SYSTEM_ID = p_src_system_id
AND SOURCE_SYSTEM_REFERENCE = p_src_system_ref
AND PROCESS_FLAG = 0;
UPDATE EGO_AML_INTF EAI
SET ORGANIZATION_ID =
NVL(ORGANIZATION_ID,
(SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_CODE = EAI.ORGANIZATION_CODE))
WHERE DATA_SET_ID = p_batch_id
AND SOURCE_SYSTEM_ID = p_src_system_id
AND SOURCE_SYSTEM_REFERENCE = p_src_system_ref
AND PROCESS_FLAG = 0;
UPDATE EGO_ITM_USR_ATTR_INTRFC EIUAI
SET ORGANIZATION_ID =
NVL(ORGANIZATION_ID,
(SELECT MP.ORGANIZATION_ID
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_CODE = EIUAI.ORGANIZATION_CODE))
WHERE DATA_SET_ID = p_batch_id
AND SOURCE_SYSTEM_ID = p_src_system_id
AND SOURCE_SYSTEM_REFERENCE = p_src_system_ref
AND PROCESS_STATUS = 0;
SELECT MSII.SOURCE_SYSTEM_ID SOURCE_SYSTEM_ID
,MSII.SOURCE_SYSTEM_REFERENCE SOURCE_SYSTEM_REFERENCE
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
WHERE MSII.SET_PROCESS_ID = p_batch_id
AND MSII.PROCESS_FLAG = 0
AND MSII.CONFIRM_STATUS IN ('US', 'UM', 'UN');
SELECT CONFIRM_SINGLE_MATCH
INTO l_confirm_single_match
FROM EGO_IMPORT_BATCH_DETAILS_V
WHERE BATCH_ID = p_batch_id;
* For every unprocessed master record in a batch which has transaction_type value of 'UPDATE' or 'SYNC'
* if it has no inventory_item_id
* if it has an item number, parse it into segments.
* determine if an item exists with these segments in MSI and if true, populate inventory_item_id
* else it has inventory_item_id
* determine if an item exists with this inventory_item_id in MSI
*
* if the current transaction_type is 'SYNC'
* change it to 'UPDATE' if there is a way to resolve it to an existing item.
* change it to 'CREATE' if there is no way to resolve it to an existing item
*/
PROCEDURE UPDATE_ITEM_SYNC_RECORDS
(p_set_id IN NUMBER
,p_org_id IN NUMBER
) IS
CURSOR c_items_table IS
SELECT rowid
,organization_id
,inventory_item_id
,item_number
,transaction_id
,transaction_type
FROM mtl_system_items_interface
WHERE set_process_id = p_set_id
AND process_flag = 0
AND organization_id = p_org_id
AND ( confirm_status IS NULL
OR confirm_status = G_FAKE_CONF_STATUS_FLAG ) -- for bug 5136989
AND UPPER(transaction_type) in ('SYNC', 'UPDATE')
FOR UPDATE OF transaction_type;
SELECT 1
FROM mtl_system_items_b
WHERE inventory_item_id = cp_item_id;
UPDATE mtl_system_items_interface
SET transaction_type = 'UPDATE'
WHERE rowid = item_record.rowid;
UPDATE mtl_system_items_interface
SET transaction_type = 'CREATE', inventory_item_id = NULL
WHERE rowid = item_record.rowid;
UPDATE mtl_system_items_interface
SET transaction_type = 'UPDATE', inventory_item_id = NULL
WHERE rowid = item_record.rowid;
END UPDATE_ITEM_SYNC_RECORDS;
SELECT CONFIRM_STATUS
FROM MTL_SYSTEM_ITEMS_INTERFACE MSII, EGO_IMPORT_BATCHES_B BA
WHERE MSII.SET_PROCESS_ID = p_batch_id
AND MSII.SOURCE_SYSTEM_ID = p_source_system_id
AND MSII.SOURCE_SYSTEM_REFERENCE = p_source_system_reference
AND MSII.ORGANIZATION_ID = BA.ORGANIZATION_ID
AND BA.BATCH_ID = p_batch_id
AND NVL(MSII.PROCESS_FLAG, 0) < 1
AND ROWNUM < 2;
SELECT 1 INTO l_item_in_matching_table
FROM EGO_ITEM_MATCHES
WHERE BATCH_ID = p_batch_id
AND SOURCE_SYSTEM_ID = p_source_system_id
AND SOURCE_SYSTEM_REFERENCE = p_source_system_reference
AND INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id;
SELECT decode(count(1), 0, G_UNCONF_NONE_MATCH, 1, G_UNCONF_SIGL_MATCH, G_UNCONF_MULT_match)
INTO l_resolved_status
FROM EGO_ITEM_MATCHES
WHERE BATCH_ID = p_batch_id
AND SOURCE_SYSTEM_ID = p_source_system_id
AND SOURCE_SYSTEM_REFERENCE = p_source_system_reference;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET CONFIRM_STATUS = l_resolved_status, INVENTORY_ITEM_ID = p_inventory_item_id
WHERE SET_PROCESS_ID = p_batch_id
AND SOURCE_SYSTEM_ID = p_source_system_id
AND SOURCE_SYSTEM_REFERENCE = p_source_system_reference
AND ORGANIZATION_ID = (SELECT ORGANIZATION_id FROM EGO_IMPORT_BATCHES_B WHERE BATCH_ID = p_batch_id)
AND NVL(PROCESS_FLAG, 0) < 1;
DELETE FROM EGO_IMPORT_EXCLUDED_SS_ITEMS
WHERE SOURCE_SYSTEM_ID = p_source_system_id
AND SOURCE_SYSTEM_REFERENCE = p_source_system_reference;
UPDATE SET LAST_UPDATED_BY = FND_GLOBAL.USER_ID, LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID, OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
WHEN NOT matched THEN
INSERT VALUES (p_source_system_id, p_source_system_reference, 1, FND_GLOBAL.USER_ID, SYSDATE, FND_GLOBAL.USER_ID, SYSDATE, FND_GLOBAL.LOGIN_ID);
SELECT NVL(ADD_ALL_TO_CHANGE_FLAG,'N')
INTO l_add_all_change_flag
FROM EGO_IMPORT_OPTION_SETS
WHERE BATCH_ID = p_batch_id;
SELECT MEANING INTO l_meaning
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = p_lookup_type
AND LOOKUP_CODE = p_lookup_code;
SELECT
COUNT(1) CNT ,
COUNT(
CASE
WHEN PROCESS_FLAG = 7
AND UPPER(TRANSACTION_TYPE)= 'CREATE'
THEN 1
ELSE NULL
END
) AS NEW_CNT ,
COUNT(
CASE
WHEN PROCESS_FLAG = 7
AND UPPER(TRANSACTION_TYPE)= 'UPDATE'
THEN 1
ELSE NULL
END
) UPDATE_CNT
FROM MTL_SYSTEM_ITEMS_INTERFACE
WHERE SET_PROCESS_ID = p_set_process_id
AND ( (ORGANIZATION_ID is not null and ORGANIZATION_ID = p_organization_id )
OR
(ORGANIZATION_ID is null and ORGANIZATION_CODE = p_organization_code )
);
SELECT
COUNT(1) CNT ,
COUNT(
CASE
WHEN PROCESS_FLAG = 7
AND UPPER(TRANSACTION_TYPE)= 'CREATE'
THEN 1
ELSE NULL
END
) AS NEW_CNT ,
COUNT(
CASE
WHEN PROCESS_FLAG = 7
AND UPPER(TRANSACTION_TYPE)= 'UPDATE'
THEN 1
ELSE NULL
END
) UPDATE_CNT
FROM MTL_SYSTEM_ITEMS_INTERFACE msii
WHERE SET_PROCESS_ID = p_set_process_id
AND EXISTS
(
SELECT NULL
FROM MTL_PARAMETERS mp
WHERE mp.MASTER_ORGANIZATION_ID = p_organization_id
AND mp.ORGANIZATION_ID <> mp.MASTER_ORGANIZATION_ID
AND ( (msii.ORGANIZATION_ID IS NOT NULL AND mp.ORGANIZATION_ID = msii.ORGANIZATION_ID)
OR
(msii.ORGANIZATION_ID IS NULL AND mp.ORGANIZATION_CODE = msii.ORGANIZATION_CODE)
)
);
SELECT
COUNT(1) AS CNT ,
COUNT(
CASE
WHEN PROCESS_FLAG = 7
AND UPPER(TRANSACTION_TYPE)= 'CREATE'
THEN 1
ELSE NULL
END
) AS NEW_CNT ,
COUNT(
CASE
WHEN PROCESS_FLAG = 7
AND UPPER(TRANSACTION_TYPE)= 'UPDATE'
THEN 1
ELSE NULL
END
) UPDATE_CNT
FROM MTL_ITEM_REVISIONS_INTERFACE miri
WHERE SET_PROCESS_ID = p_set_process_id
AND EXISTS
(
SELECT NULL
FROM MTL_PARAMETERS mp
WHERE mp.MASTER_ORGANIZATION_ID = p_organization_id
AND ( (miri.ORGANIZATION_ID IS NOT NULL AND mp.ORGANIZATION_ID = miri.ORGANIZATION_ID)
OR
(miri.ORGANIZATION_ID IS NULL AND mp.ORGANIZATION_CODE = miri.ORGANIZATION_CODE)
)
);
SELECT
COUNT(
CASE
WHEN UPPER(TRANSACTION_TYPE) <> 'NO_OP'
THEN 1
ELSE NULL
END
) AS CNT ,
COUNT(
CASE
WHEN PROCESS_FLAG = 7
AND UPPER(TRANSACTION_TYPE)= 'CREATE'
THEN 1
ELSE NULL
END
) AS NEW_CNT ,
COUNT(
CASE
WHEN PROCESS_FLAG = 7
AND UPPER(TRANSACTION_TYPE) IN ( 'UPDATE', 'DELETE' )
THEN 1
ELSE NULL
END
) UPDATE_CNT
FROM BOM_INVENTORY_COMPS_INTERFACE bici
WHERE bici.BATCH_ID = p_set_process_id
AND EXISTS
(
SELECT NULL
FROM MTL_PARAMETERS mp
WHERE mp.MASTER_ORGANIZATION_ID = p_organization_id
AND ( (bici.ORGANIZATION_ID IS NOT NULL AND mp.ORGANIZATION_ID = bici.ORGANIZATION_ID)
OR
(bici.ORGANIZATION_ID IS NULL AND mp.ORGANIZATION_CODE = bici.ORGANIZATION_CODE)
)
);
SELECT
COUNT(1) AS CNT ,
COUNT(
CASE
WHEN PROCESS_STATUS = 4
AND UPPER(TRANSACTION_TYPE)= 'CREATE'
THEN 1
ELSE NULL
END
) AS NEW_CNT ,
COUNT(
CASE
WHEN PROCESS_STATUS = 4
AND ( UPPER(TRANSACTION_TYPE) IN ('UPDATE', 'DELETE') )
THEN 1
ELSE NULL
END
) UPDATE_CNT
FROM EGO_ITEM_PEOPLE_INTF eipi
WHERE eipi.DATA_SET_ID = p_set_process_id
AND EXISTS
(
SELECT NULL
FROM MTL_PARAMETERS mp
WHERE mp.MASTER_ORGANIZATION_ID = p_organization_id
AND ( (eipi.ORGANIZATION_ID IS NOT NULL AND mp.ORGANIZATION_ID = eipi.ORGANIZATION_ID)
OR
(eipi.ORGANIZATION_ID IS NULL AND mp.ORGANIZATION_CODE = eipi.ORGANIZATION_CODE)
)
) ;
SELECT
COUNT(1) AS CNT ,
COUNT(
CASE
WHEN PROCESS_FLAG = 7
AND UPPER(TRANSACTION_TYPE)= 'CREATE'
THEN 1
ELSE NULL
END
) AS NEW_CNT ,
COUNT(
CASE
WHEN PROCESS_FLAG = 7
AND ( UPPER(TRANSACTION_TYPE) IN ( 'UPDATE','DELETE') )
THEN 1
ELSE NULL
END
) UPDATE_CNT
FROM MTL_ITEM_CATEGORIES_INTERFACE mici
WHERE mici.SET_PROCESS_ID = p_set_process_id
AND EXISTS
(
SELECT NULL
FROM MTL_PARAMETERS mp
WHERE mp.MASTER_ORGANIZATION_ID = p_organization_id
AND ( (mici.ORGANIZATION_ID IS NOT NULL AND mp.ORGANIZATION_ID = mici.ORGANIZATION_ID)
OR
(mici.ORGANIZATION_ID IS NULL AND mp.ORGANIZATION_CODE = mici.ORGANIZATION_CODE)
)
);
SELECT
COUNT(1) AS CNT ,
COUNT(
CASE
WHEN PROCESS_FLAG = 7
AND UPPER(TRANSACTION_TYPE)= 'CREATE'
THEN 1
ELSE NULL
END
) AS NEW_CNT ,
COUNT(
CASE
WHEN PROCESS_FLAG = 7
AND UPPER(TRANSACTION_TYPE)= 'UPDATE'
THEN 1
ELSE NULL
END
) UPDATE_CNT
FROM EGO_AML_INTF eai
WHERE eai.DATA_SET_ID = p_set_process_id
AND EXISTS
(
SELECT NULL
FROM MTL_PARAMETERS mp
WHERE mp.MASTER_ORGANIZATION_ID = p_organization_id
AND ( (eai.ORGANIZATION_ID IS NOT NULL AND mp.ORGANIZATION_ID = eai.ORGANIZATION_ID)
OR
(eai.ORGANIZATION_ID IS NULL AND mp.ORGANIZATION_CODE = eai.ORGANIZATION_CODE)
)
);
SELECT
COUNT(1) AS CNT ,
COUNT(
CASE
WHEN PROCESS_FLAG = 7
AND UPPER(TRANSACTION_TYPE)= 'CREATE'
THEN 1
ELSE NULL
END
) AS NEW_CNT ,
COUNT(
CASE
WHEN PROCESS_FLAG = 7
AND UPPER(TRANSACTION_TYPE)= 'UPDATE'
THEN 1
ELSE NULL
END
) UPDATE_CNT
FROM BOM_COMPONENT_OPS_INTERFACE bcoi
WHERE bcoi.BATCH_ID = p_set_process_id
AND EXISTS
(
SELECT NULL
FROM MTL_PARAMETERS mp
WHERE mp.MASTER_ORGANIZATION_ID = p_organization_id
AND ( (bcoi.ORGANIZATION_ID IS NOT NULL AND mp.ORGANIZATION_ID = bcoi.ORGANIZATION_ID)
OR
(bcoi.ORGANIZATION_ID IS NULL AND mp.ORGANIZATION_CODE = bcoi.ORGANIZATION_CODE)
)
);
SELECT
COUNT(1) AS CNT ,
COUNT(
CASE
WHEN PROCESS_FLAG = 7
AND UPPER(TRANSACTION_TYPE)= 'CREATE'
THEN 1
ELSE NULL
END
) AS NEW_CNT ,
COUNT(
CASE
WHEN PROCESS_FLAG = 7
AND UPPER(TRANSACTION_TYPE)= 'UPDATE'
THEN 1
ELSE NULL
END
) UPDATE_CNT
FROM BOM_REF_DESGS_INTERFACE brdi
WHERE brdi.BATCH_ID = p_set_process_id
AND EXISTS
(
SELECT NULL
FROM MTL_PARAMETERS mp
WHERE mp.MASTER_ORGANIZATION_ID = p_organization_id
AND ( (brdi.ORGANIZATION_ID IS NOT NULL AND mp.ORGANIZATION_ID = brdi.ORGANIZATION_ID)
OR
(brdi.ORGANIZATION_ID IS NULL AND mp.ORGANIZATION_CODE = brdi.ORGANIZATION_CODE)
)
);
SELECT
COUNT(1) AS CNT ,
COUNT(
CASE
WHEN PROCESS_FLAG = 7
AND UPPER(TRANSACTION_TYPE)= 'CREATE'
THEN 1
ELSE NULL
END
) AS NEW_CNT ,
COUNT(
CASE
WHEN PROCESS_FLAG = 7
AND UPPER(TRANSACTION_TYPE)= 'UPDATE'
THEN 1
ELSE NULL
END
) UPDATE_CNT
FROM BOM_SUB_COMPS_INTERFACE bsci
WHERE bsci.BATCH_ID = p_set_process_id
AND EXISTS
(
SELECT NULL
FROM MTL_PARAMETERS mp
WHERE mp.MASTER_ORGANIZATION_ID = p_organization_id
AND ( (bsci.ORGANIZATION_ID IS NOT NULL AND mp.ORGANIZATION_ID = bsci.ORGANIZATION_ID)
OR
(bsci.ORGANIZATION_ID IS NULL AND mp.ORGANIZATION_CODE = bsci.ORGANIZATION_CODE)
)
);
SELECT
COUNT(
CASE
WHEN UPPER(TRANSACTION_TYPE) <> 'NO_OP'
THEN 1
ELSE NULL
END
) AS CNT ,
COUNT(
CASE
WHEN PROCESS_FLAG = 7
AND UPPER(TRANSACTION_TYPE)= 'CREATE'
THEN 1
ELSE NULL
END
) AS NEW_CNT ,
COUNT(
CASE
WHEN PROCESS_FLAG = 7
AND UPPER(TRANSACTION_TYPE)= 'UPDATE'
THEN 1
ELSE NULL
END
) UPDATE_CNT
FROM BOM_BILL_OF_MTLS_INTERFACE bomi
WHERE bomi.BATCH_ID = p_set_process_id
AND EXISTS
(
SELECT NULL
FROM MTL_PARAMETERS mp
WHERE mp.MASTER_ORGANIZATION_ID = p_organization_id
AND ( (bomi.ORGANIZATION_ID IS NOT NULL AND mp.ORGANIZATION_ID = bomi.ORGANIZATION_ID)
OR
(bomi.ORGANIZATION_ID IS NULL AND mp.ORGANIZATION_CODE = bomi.ORGANIZATION_CODE)
)
);
SELECT
edlb.DATA_LEVEL_NAME AS DATA_LEVEL_NAME,
COUNT(1) AS CNT ,
COUNT(
CASE
WHEN PROCESS_FLAG = 7
AND UPPER(TRANSACTION_TYPE)= 'CREATE'
THEN 1
ELSE NULL
END
) AS NEW_CNT ,
COUNT(
CASE
WHEN PROCESS_FLAG = 7
AND UPPER(TRANSACTION_TYPE)= 'UPDATE'
THEN 1
ELSE NULL
END
) UPDATE_CNT
FROM EGO_ITEM_ASSOCIATIONS_INTF eiai, EGO_DATA_LEVEL_B edlb
WHERE eiai.BATCH_ID = p_set_process_id
AND eiai.DATA_LEVEL_ID = edlb.DATA_LEVEL_ID
AND EXISTS
(
SELECT NULL
FROM MTL_PARAMETERS mp
WHERE mp.MASTER_ORGANIZATION_ID = p_organization_id
AND ( (eiai.ORGANIZATION_ID IS NOT NULL AND mp.ORGANIZATION_ID = eiai.ORGANIZATION_ID)
OR
(eiai.ORGANIZATION_ID IS NULL AND mp.ORGANIZATION_CODE = eiai.ORGANIZATION_CODE)
)
)
GROUP BY edlb.DATA_LEVEL_NAME;
SELECT LOOKUP_CODE,
MEANING,
TAG
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_TYPE='EGO_PDH_ENTITY_TYPES'
AND VIEW_APPLICATION_ID = 0
AND SECURITY_GROUP_ID = fnd_global.lookup_security_group(LOOKUP_TYPE, VIEW_APPLICATION_ID);
SELECT NVL(opt.IMPORT_XREF_ONLY, 'N') INTO l_import_xref_only
FROM EGO_IMPORT_BATCHES_B b, EGO_IMPORT_OPTION_SETS opt
WHERE b.BATCH_ID = p_set_process_id
AND b.BATCH_ID = opt.BATCH_ID;
l_row_type.UPDATE_CNT := i.UPDATE_CNT;
l_row_type.AFTER_IMPORT_TOTAL := i.NEW_CNT + i.UPDATE_CNT;
l_row_type.UPDATE_CNT := i.UPDATE_CNT;
l_row_type.AFTER_IMPORT_TOTAL := i.NEW_CNT + i.UPDATE_CNT;
l_row_type.UPDATE_CNT := i.UPDATE_CNT;
l_row_type.AFTER_IMPORT_TOTAL := i.NEW_CNT + i.UPDATE_CNT;
l_row_type.UPDATE_CNT := i.UPDATE_CNT;
l_row_type.AFTER_IMPORT_TOTAL := i.NEW_CNT + i.UPDATE_CNT;
l_row_type.UPDATE_CNT := i.UPDATE_CNT;
l_row_type.AFTER_IMPORT_TOTAL := i.NEW_CNT + i.UPDATE_CNT;
l_row_type.UPDATE_CNT := i.UPDATE_CNT;
l_row_type.AFTER_IMPORT_TOTAL := i.NEW_CNT + i.UPDATE_CNT;
l_row_type.UPDATE_CNT := 0;
l_row_type.UPDATE_CNT := i.UPDATE_CNT;
l_row_type.AFTER_IMPORT_TOTAL := i.NEW_CNT + i.UPDATE_CNT;
l_row_type.UPDATE_CNT := i.UPDATE_CNT;
l_row_type.AFTER_IMPORT_TOTAL := i.NEW_CNT + i.UPDATE_CNT;
l_row_type.UPDATE_CNT := i.UPDATE_CNT;
l_row_type.AFTER_IMPORT_TOTAL := i.NEW_CNT + i.UPDATE_CNT;
l_row_type.UPDATE_CNT := i.UPDATE_CNT;
l_row_type.AFTER_IMPORT_TOTAL := i.NEW_CNT + i.UPDATE_CNT;
l_row_type.UPDATE_CNT := i.UPDATE_CNT;
l_row_type.AFTER_IMPORT_TOTAL := i.NEW_CNT + i.UPDATE_CNT;
l_row_type.UPDATE_CNT := i.UPDATE_CNT;
l_row_type.AFTER_IMPORT_TOTAL := i.NEW_CNT + i.UPDATE_CNT;
* This method updates the request_ids to ego_import_batches_b table.
*/
PROCEDURE Update_Request_Id_To_Batch (
p_import_request_id IN NUMBER,
p_match_request_id IN NUMBER,
p_batch_id IN NUMBER)
IS
BEGIN
IF ( NVL(p_import_request_id, 0) > 0 ) OR ( NVL(p_match_request_id, 0) > 0 ) THEN
UPDATE EGO_IMPORT_BATCHES_B
SET LAST_MATCH_REQUEST_ID = DECODE(p_match_request_id, NULL, LAST_MATCH_REQUEST_ID, 0, LAST_MATCH_REQUEST_ID, p_match_request_id),
LAST_IMPORT_REQUEST_ID = DECODE(p_import_request_id, NULL, LAST_IMPORT_REQUEST_ID, 0, LAST_IMPORT_REQUEST_ID, p_import_request_id)
WHERE BATCH_ID = p_batch_id;
END Update_Request_Id_To_Batch;