[Home] [Help]
49: G_DEBUG_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
50: G_LOG_TIMESTAMP_FORMAT CONSTANT VARCHAR2( 30 ) := 'dd-mon-yyyy hh:mi:ss.ff';
51:
52: G_CURRENT_DEBUG_LEVEL NUMBER;
53: G_TABLE_NAME VARCHAR2(30) := 'EGO_ITM_USR_ATTR_INTRFC';--BUG 5352217
54:
55: -------------------------------------
56: -- Pragma for Data Set ID function --
57: -------------------------------------
130: ,p_count IN NUMBER
131: ) IS
132: -- PRAGMA AUTONOMOUS_TRANSACTION;
133:
134: l_rec ego_itm_usr_attr_intrfc%ROWTYPE;
135: l_dummy_number NUMBER;
136: BEGIN
137: NULL;
138: /*
137: NULL;
138: /*
139: SELECT count(*)
140: INTO l_dummy_number
141: FROM ego_itm_usr_attr_intrfc
142: WHERE data_set_id = p_data_set_id
143: AND process_status = p_process_status
144: AND transaction_type = p_transaction_type
145: AND data_level_id = p_data_level_id;
154: ' inventory_item_id, organization_id, data_level_id, revision_id, pk1_value, pk2_value, '||
155: ' attr_group_id, attr_group_int_name, attr_int_name, attr_value_str, attr_value_num, '||
156: ' row_identifier');
157: IF p_count < 100 THEN
158: FOR cr in (SELECT * FROM ego_itm_usr_attr_intrfc
159: WHERE data_set_id = p_data_set_id
160: AND process_status = p_process_status
161: AND transaction_type = p_transaction_type
162: AND data_level_id = p_data_level_id
177:
178: BEGIN
179: SELECT count(*) abc
180: into l_dummy_number
181: FROM EGO_ITM_USR_ATTR_INTRFC
182: WHERE data_set_id = p_data_set_id
183: -- AND process_status = 1
184: -- AND transaction_type = p_transaction_type
185: -- AND data_level_id = p_data_level_id
204: ----------------------------------------------------------------------
205: PROCEDURE write_records (p_data_set_id IN NUMBER
206: ,p_module IN VARCHAR2
207: ,p_message IN VARCHAR2) IS
208: l_rec ego_itm_usr_attr_intrfc%ROWTYPE;
209: CURSOR get_records IS
210: SELECT *
211: FROM ego_itm_usr_attr_intrfc
212: WHERE data_set_id = p_data_set_id;
207: ,p_message IN VARCHAR2) IS
208: l_rec ego_itm_usr_attr_intrfc%ROWTYPE;
209: CURSOR get_records IS
210: SELECT *
211: FROM ego_itm_usr_attr_intrfc
212: WHERE data_set_id = p_data_set_id;
213: begin
214: FOR cr IN get_records LOOP
215: code_debug (p_log_level => G_DEBUG_LEVEL_STATEMENT
543: IS
544: SELECT DISTINCT
545: ORGANIZATION_ID
546: ,ITEM_NUMBER
547: FROM EGO_ITM_USR_ATTR_INTRFC
548: WHERE DATA_SET_ID = cp_data_set_id
549: AND PROCESS_STATUS = G_PS_IN_PROCESS
550: AND ITEM_NUMBER IS NOT NULL
551: AND INVENTORY_ITEM_ID IS NULL;
579: ,PK1_VALUE
580: ,PK2_VALUE
581: ,DATA_LEVEL_NAME
582: ,USER_DATA_LEVEL_NAME
583: FROM EGO_ITM_USR_ATTR_INTRFC
584: WHERE DATA_SET_ID = cp_data_set_id
585: AND PROCESS_STATUS IN (G_PS_BAD_ORG_ID,
586: G_PS_BAD_ORG_CODE,
587: G_PS_BAD_ITEM_ID,
624: ,INVENTORY_ITEM_ID
625: ,ITEM_CATALOG_GROUP_ID
626: ,REVISION_ID
627: ,ATTR_GROUP_ID
628: FROM EGO_ITM_USR_ATTR_INTRFC
629: WHERE DATA_SET_ID = cp_data_set_id
630: AND PROCESS_STATUS = G_PS_IN_PROCESS
631: ORDER BY ORGANIZATION_ID,
632: INVENTORY_ITEM_ID,
643: --------------------------------------------------------------------------
644: CURSOR distinct_catalaog_groups (cp_data_set_id IN NUMBER)
645: IS
646: SELECT ITEM_CATALOG_GROUP_ID
647: FROM EGO_ITM_USR_ATTR_INTRFC
648: WHERE DATA_SET_ID = cp_data_set_id
649: AND PROCESS_STATUS = G_PS_IN_PROCESS
650: GROUP BY ITEM_CATALOG_GROUP_ID;
651:
679:
680: /*CURSOR c_row_identfier (cp_data_set_id IN NUMBER)
681: IS
682: SELECT distinct ROW_IDENTIFIER
683: FROM EGO_ITM_USR_ATTR_INTRFC
684: WHERE DATA_SET_ID = cp_data_set_id
685: AND PROCESS_STATUS = G_PS_IN_PROCESS;
686:
687: CURSOR c_com_attr_groups (cp_data_set_id IN NUMBER, cp_row_identifier NUMBER)
701: ,ITEM_CATALOG_GROUP_ID
702: ,REVISION_ID
703: ,ATTR_GROUP_ID
704: ,ATTR_GROUP_TYPE
705: FROM EGO_ITM_USR_ATTR_INTRFC
706: WHERE DATA_SET_ID = cp_data_set_id
707: AND PROCESS_STATUS = G_PS_IN_PROCESS
708: AND ROW_IDENTIFIER = cp_row_identifier
709: ORDER BY ORGANIZATION_ID,
739: --If there are no rows to process for this data_set_id, return success.
740: SELECT
741: COUNT(DATA_SET_ID)
742: INTO l_rec_count
743: FROM EGO_ITM_USR_ATTR_INTRFC intf
744: WHERE DATA_SET_ID = p_data_set_id
745: AND (PROCESS_STATUS IS NULL OR PROCESS_STATUS IN (G_PS_TO_BE_PROCESSED, G_PS_IN_PROCESS, G_PS_STYLE_VARIANT_IN_PROCESS) );
746:
747: IF (l_rec_count = 0) THEN
820: -------------------------------------------------------------------
821: /*6602290 : Stats gather through profile : Stats are gathered in EGOVIMPB
822: SELECT COUNT(data_set_id)
823: INTO l_rec_count
824: FROM EGO_ITM_USR_ATTR_INTRFC
825: WHERE data_set_id = p_data_set_id;
826: IF l_rec_count > 50 THEN
827: FND_STATS.Gather_Table_Stats(
828: ownname => 'EGO'
825: WHERE data_set_id = p_data_set_id;
826: IF l_rec_count > 50 THEN
827: FND_STATS.Gather_Table_Stats(
828: ownname => 'EGO'
829: ,tabname => 'EGO_ITM_USR_ATTR_INTRFC'
830: ,cascade => TRUE
831: );
832: END IF;
833: */
854: -- update Concurrent Request information for better user tracking --
855: -- and update the "WHO" columns on the assumption that the current --
856: -- user is also the person who loaded this data set into the table --
857: ---------------------------------------------------------------------
858: UPDATE EGO_ITM_USR_ATTR_INTRFC
859: SET PROCESS_STATUS = G_PS_IN_PROCESS
860: ,ITEM_CATALOG_GROUP_ID = NULL
861: ,PROG_INT_NUM1 = NULL
862: ,PROG_INT_NUM2 = NULL
877: AND (PROCESS_STATUS IS NULL OR PROCESS_STATUS = G_PS_TO_BE_PROCESSED);
878:
879: --Fix for Bug:13716796
880: --Provide default value for PROG_INT_CHAR2 if it's value is NULL
881: UPDATE EGO_ITM_USR_ATTR_INTRFC
882: SET PROG_INT_CHAR2 = 'N'
883: WHERE DATA_SET_ID = p_data_set_id
884: AND PROG_INT_CHAR2 IS NULL
885: AND PROCESS_STATUS =G_PS_IN_PROCESS;
891:
892: IF (nvl(fnd_profile.value('EGO_ENABLE_GATHER_STATS'),'N') >= 'Y') THEN
893:
894: FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_schema,
895: TABNAME => 'EGO_ITM_USR_ATTR_INTRFC',
896: CASCADE => True);
897: END IF;
898:
899: END IF;
923: -- IDs for those rows where the user didn't pass in an Org ID; --
924: -- as above, if the AG is associated at the Rev level then the --
925: -- Orgs don't have to be Master Orgs. --
926: -----------------------------------------------------------------
927: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
928: SET UAI.ORGANIZATION_ID =
929: (SELECT MP.ORGANIZATION_ID
930: FROM MTL_PARAMETERS MP
931: WHERE MP.ORGANIZATION_CODE = UAI.ORGANIZATION_CODE)
946: -- Finally, mark as errors all rows that are in the same logical Attribute --
947: -- Group row as any row whose Org Code doesn't correspond to a valid Master --
948: -- Org ID (marking errors as we go avoids further processing of bad rows) --
949: ------------------------------------------------------------------------------
950: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
951: SET UAI.PROCESS_STATUS = G_PS_BAD_ORG_CODE
952: WHERE UAI.DATA_SET_ID = p_data_set_id
953: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
954: AND UAI.ROW_IDENTIFIER IN (SELECT DISTINCT
952: WHERE UAI.DATA_SET_ID = p_data_set_id
953: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
954: AND UAI.ROW_IDENTIFIER IN (SELECT DISTINCT
955: UAI2.ROW_IDENTIFIER
956: FROM EGO_ITM_USR_ATTR_INTRFC UAI2
957: WHERE UAI2.DATA_SET_ID = p_data_set_id
958: AND UAI2.PROCESS_STATUS = G_PS_IN_PROCESS
959: AND UAI2.ORGANIZATION_ID IS NULL);
960:
974: -- Items; if any row has an invalid Item ID, error it out along with all --
975: -- other rows in its logical Attribute Group row --
976: ----------------------------------------------------------------------------
977: IF p_validate_only = FND_API.G_FALSE THEN
978: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
979: SET UAI.PROCESS_STATUS = G_PS_BAD_ITEM_ID
980: WHERE UAI.DATA_SET_ID = p_data_set_id
981: AND UAI.PROCESS_STATUS IN (G_PS_IN_PROCESS, G_PS_STYLE_VARIANT_IN_PROCESS)
982: AND UAI.INVENTORY_ITEM_ID IS NOT NULL
984: FROM MTL_SYSTEM_ITEMS_B MSIB
985: WHERE MSIB.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
986: AND MSIB.ORGANIZATION_ID = UAI.ORGANIZATION_ID);
987: ELSE
988: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
989: SET UAI.PROCESS_STATUS = G_PS_BAD_ITEM_ID
990: WHERE UAI.DATA_SET_ID = p_data_set_id
991: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
992: AND UAI.INVENTORY_ITEM_ID IS NOT NULL
1011: -- combination once and then set the Item ID we find --
1012: -- to all rows with the same Item Number and Org ID) --
1013: -------------------------------------------------------
1014: IF p_validate_only = FND_API.G_FALSE THEN
1015: UPDATE EGO_ITM_USR_ATTR_INTRFC intrfc
1016: SET INVENTORY_ITEM_ID =
1017: (SELECT INVENTORY_ITEM_ID
1018: FROM MTL_SYSTEM_ITEMS_B_KFV
1019: WHERE CONCATENATED_SEGMENTS = intrfc.ITEM_NUMBER
1024: AND INVENTORY_ITEM_ID IS NULL;
1025: ELSE
1026: -- Bug 13434831 : Start (Performance Change)
1027: /*
1028: UPDATE EGO_ITM_USR_ATTR_INTRFC intrfc
1029: SET INVENTORY_ITEM_ID =
1030: NVL( (SELECT INVENTORY_ITEM_ID
1031: FROM MTL_SYSTEM_ITEMS_B_KFV
1032: WHERE CONCATENATED_SEGMENTS = intrfc.ITEM_NUMBER
1046: AND (ITEM_NUMBER IS NOT NULL OR SOURCE_SYSTEM_REFERENCE IS NOT NULL)
1047: AND INVENTORY_ITEM_ID IS NULL;
1048: */
1049:
1050: UPDATE EGO_ITM_USR_ATTR_INTRFC INTRFC
1051: SET INVENTORY_ITEM_ID = NVL((SELECT INVENTORY_ITEM_ID
1052: FROM MTL_SYSTEM_ITEMS_KFV MSIK
1053: WHERE MSIK.CONCATENATED_SEGMENTS = INTRFC.ITEM_NUMBER
1054: AND MSIK.ORGANIZATION_ID = INTRFC.ORGANIZATION_ID
1079: -- As with the Organization step, we mark as errors all rows --
1080: -- that share the same logical Attribute Group row with any --
1081: -- rows where we didn't end up with a valid Inventory Item ID --
1082: ----------------------------------------------------------------
1083: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1084: SET UAI.PROCESS_STATUS = G_PS_BAD_ITEM_NUMBER
1085: WHERE UAI.DATA_SET_ID = p_data_set_id
1086: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1087: AND UAI.ROW_IDENTIFIER IN (SELECT DISTINCT
1085: WHERE UAI.DATA_SET_ID = p_data_set_id
1086: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1087: AND UAI.ROW_IDENTIFIER IN (SELECT DISTINCT
1088: UAI2.ROW_IDENTIFIER
1089: FROM EGO_ITM_USR_ATTR_INTRFC UAI2
1090: WHERE UAI2.DATA_SET_ID = p_data_set_id
1091: AND UAI2.PROCESS_STATUS = G_PS_IN_PROCESS
1092: AND UAI2.INVENTORY_ITEM_ID IS NULL);
1093:
1105: -- if any row has an invalid Revision ID, error it out along --
1106: -- with all other rows in its logical Attribute Group row --
1107: ---------------------------------------------------------------
1108: IF p_validate_only = FND_API.G_FALSE THEN
1109: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1110: SET UAI.PROCESS_STATUS = G_PS_BAD_REVISION_ID
1111: WHERE UAI.DATA_SET_ID = p_data_set_id
1112: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1113: AND UAI.REVISION_ID IS NOT NULL
1116: WHERE MIR.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1117: AND MIR.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1118: AND MIR.REVISION_ID = UAI.REVISION_ID);
1119: ELSE
1120: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1121: SET UAI.PROCESS_STATUS = G_PS_BAD_REVISION_ID
1122: WHERE UAI.DATA_SET_ID = p_data_set_id
1123: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1124: AND UAI.REVISION_ID IS NOT NULL
1143: -- the user passed in the Revision (note that by "Revision", --
1144: -- we mean the Revision *Code* and not the Revision Label) --
1145: ----------------------------------------------------------------
1146: IF p_validate_only = FND_API.G_FALSE THEN
1147: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1148: SET UAI.REVISION_ID = (SELECT MIR.REVISION_ID
1149: FROM MTL_ITEM_REVISIONS MIR
1150: WHERE MIR.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1151: AND MIR.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1159: WHERE MIR2.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1160: AND MIR2.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1161: AND MIR2.REVISION = UAI.REVISION);
1162: ELSE
1163: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1164: SET UAI.REVISION_ID = NVL( (SELECT MIR.REVISION_ID
1165: FROM MTL_ITEM_REVISIONS MIR
1166: WHERE MIR.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1167: AND MIR.ORGANIZATION_ID = UAI.ORGANIZATION_ID
1203: -- row with any rows where we started with a Revision and didn't end --
1204: -- up with a valid Revision ID (because many rows may not have either --
1205: -- a Revision or a Revision ID, and that is not an error condition) --
1206: -------------------------------------------------------------------------
1207: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1208: SET UAI.PROCESS_STATUS = G_PS_BAD_REVISION_CODE
1209: WHERE UAI.DATA_SET_ID = p_data_set_id
1210: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1211: AND UAI.ROW_IDENTIFIER IN (SELECT DISTINCT
1209: WHERE UAI.DATA_SET_ID = p_data_set_id
1210: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1211: AND UAI.ROW_IDENTIFIER IN (SELECT DISTINCT
1212: UAI2.ROW_IDENTIFIER
1213: FROM EGO_ITM_USR_ATTR_INTRFC UAI2
1214: WHERE UAI2.DATA_SET_ID = p_data_set_id
1215: AND UAI2.PROCESS_STATUS = G_PS_IN_PROCESS
1216: AND UAI2.REVISION IS NOT NULL
1217: AND UAI2.REVISION_ID IS NULL);
1227:
1228: -- write_records(p_data_set_id => p_data_set_id, p_module => l_api_name, p_message => 'After init');
1229: -- Fix for bug#9660659
1230: IF p_validate_only = FND_API.G_FALSE THEN
1231: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1232: SET (UAI.ITEM_CATALOG_GROUP_ID, UAI.PROG_INT_CHAR1,
1233: UAI.PROG_INT_NUM2, UAI.PROG_INT_NUM3)
1234: = (SELECT NVL(MSI.ITEM_CATALOG_GROUP_ID,-1), NVL(MSI.APPROVAL_STATUS,'A'),
1235: MSI.LIFECYCLE_ID, MSI.CURRENT_PHASE_ID
1256: ELSE
1257: -- Bug 11719885 : Start
1258: -- For ICC, check the value in MSII first then check in MSIB
1259: -- (as in case of ICC change for an item, the correct value will be present in MSII)
1260: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1261: SET (UAI.ITEM_CATALOG_GROUP_ID,
1262: UAI.PROG_INT_NUM2, UAI.PROG_INT_NUM3)
1263: = (SELECT
1264: NVL(UAI.ITEM_CATALOG_GROUP_ID, MSII.ITEM_CATALOG_GROUP_ID),
1291: AND MSII2.PROCESS_FLAG = 1
1292: );
1293:
1294:
1295: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1296: SET (UAI.ITEM_CATALOG_GROUP_ID, UAI.PROG_INT_CHAR1,
1297: UAI.PROG_INT_NUM2, UAI.PROG_INT_NUM3)
1298: = (SELECT NVL(Nvl(UAI.ITEM_CATALOG_GROUP_ID,MSI.ITEM_CATALOG_GROUP_ID),-1) ,
1299: NVL(MSI.APPROVAL_STATUS,'A'),
1318: FROM MTL_SYSTEM_ITEMS_B MSI
1319: WHERE MSI.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1320: AND MSI.ORGANIZATION_ID = UAI.ORGANIZATION_ID);
1321: /*
1322: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1323: SET (UAI.ITEM_CATALOG_GROUP_ID, UAI.PROG_INT_CHAR1,
1324: UAI.PROG_INT_NUM2, UAI.PROG_INT_NUM3)
1325: = (SELECT MSI.ITEM_CATALOG_GROUP_ID, NVL(MSI.APPROVAL_STATUS,'A'), -- Fix by bug 11782276
1326: MSI.LIFECYCLE_ID, MSI.CURRENT_PHASE_ID
1345: WHERE MSI.INVENTORY_ITEM_ID = UAI.INVENTORY_ITEM_ID
1346: AND MSI.ORGANIZATION_ID = UAI.ORGANIZATION_ID);
1347:
1348: -- If item is not found in production then get the values from interface
1349: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1350: SET (UAI.ITEM_CATALOG_GROUP_ID,
1351: UAI.PROG_INT_NUM2, UAI.PROG_INT_NUM3)
1352: = (SELECT
1353: NVL( NVL(UAI.ITEM_CATALOG_GROUP_ID, MSII.ITEM_CATALOG_GROUP_ID), -1),
1387: ----------------------------------------------------------------------------
1388: -- Mark as errors all rows that share the same logical Attribute Group --
1389: -- row with any rows where we didn't end up with a valid Catalog Group ID --
1390: ----------------------------------------------------------------------------
1391: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1392: SET UAI.PROCESS_STATUS = G_PS_BAD_CATALOG_GROUP_ID
1393: WHERE UAI.DATA_SET_ID = p_data_set_id
1394: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1395: AND UAI.ROW_IDENTIFIER IN (SELECT DISTINCT
1393: WHERE UAI.DATA_SET_ID = p_data_set_id
1394: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1395: AND UAI.ROW_IDENTIFIER IN (SELECT DISTINCT
1396: UAI2.ROW_IDENTIFIER
1397: FROM EGO_ITM_USR_ATTR_INTRFC UAI2
1398: WHERE UAI2.DATA_SET_ID = p_data_set_id
1399: AND UAI2.PROCESS_STATUS = G_PS_IN_PROCESS
1400: AND UAI2.ITEM_CATALOG_GROUP_ID IS NULL);
1401:
1402: IF (p_is_id_validations_reqd = FND_API.G_TRUE) THEN /* Fix for bug#9660659 */
1403: ---------------------------------------------------------
1404: -- Find the Attr Group Type for Attribute Name --
1405: ---------------------------------------------------------
1406: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1407: SET ATTR_GROUP_TYPE = NVL(ATTR_GROUP_TYPE,(SELECT DESCRIPTIVE_FLEXFIELD_NAME
1408: FROM EGO_FND_DSC_FLX_CTX_EXT
1409: WHERE APPLICATION_ID = 431
1410: AND DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI.ATTR_GROUP_INT_NAME
1429: ----------------------------------------------------------------------------
1430: -- Note: Attribute Internal Name take precidence over Attribute Group Id --
1431: ----------------------------------------------------------------------------
1432: -- to do check the performance cost 35
1433: -- UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1434: -- SET UAI.PROCESS_STATUS = G_PS_BAD_ATTR_GROUP_ID
1435: -- WHERE UAI.DATA_SET_ID = p_data_set_id
1436: -- AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1437: -- AND UAI.ATTR_GROUP_ID IS NOT NULL
1441: -- AND DESCRIPTIVE_FLEXFIELD_NAME = l_attr_group_type
1442: -- AND DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI.ATTR_GROUP_INT_NAME);
1443:
1444: -- to do check the performance cost 3
1445: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1446: SET UAI.PROCESS_STATUS = G_PS_BAD_CATALOG_GROUP_ID
1447: WHERE UAI.DATA_SET_ID = p_data_set_id
1448: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1449: AND UAI.ATTR_GROUP_ID IS NOT NULL
1460: ---------------------------------------------------------
1461: IF (p_debug_level > 0) THEN
1462: EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Attr Group ID conversion', 0);
1463: END IF;
1464: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1465: SET ATTR_GROUP_ID = (SELECT ATTR_GROUP_ID
1466: FROM EGO_FND_DSC_FLX_CTX_EXT FLX_EXT
1467: WHERE APPLICATION_ID = 431
1468: AND DESCRIPTIVE_FLEXFIELD_NAME = UAI.ATTR_GROUP_TYPE --l_attr_group_type
1474: ------------------------------------------------------------------------------
1475: -- Mark as errors all rows that didn't end up with a valid Attr Group ID --
1476: ------------------------------------------------------------------------------
1477:
1478: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1479: SET UAI.PROCESS_STATUS = G_PS_BAD_ATTR_GROUP_NAME
1480: WHERE UAI.DATA_SET_ID = p_data_set_id
1481: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1482: AND UAI.ATTR_GROUP_ID IS NULL;
1485: -------------------------------------------------------------------------
1486: -- Erase Revision information for Attr Groups associated at ITEM_LEVEL --
1487: -------------------------------------------------------------------------
1488: /*
1489: UPDATE EGO_ITM_USR_ATTR_INTRFC
1490: SET REVISION = NULL, REVISION_ID = NULL
1491: WHERE ROWID IN (SELECT I.ROWID
1492: FROM EGO_OBJ_AG_ASSOCS_B A,
1493: EGO_ITM_USR_ATTR_INTRFC I
1489: UPDATE EGO_ITM_USR_ATTR_INTRFC
1490: SET REVISION = NULL, REVISION_ID = NULL
1491: WHERE ROWID IN (SELECT I.ROWID
1492: FROM EGO_OBJ_AG_ASSOCS_B A,
1493: EGO_ITM_USR_ATTR_INTRFC I
1494: WHERE A.CLASSIFICATION_CODE = I.ITEM_CATALOG_GROUP_ID
1495: AND A.OBJECT_ID = G_ITEM_OBJECT_ID
1496: AND A.ATTR_GROUP_ID = I.ATTR_GROUP_ID
1497: AND A.DATA_LEVEL = 'ITEM_LEVEL'
1511: end loop;
1512: Debug_Conc_Log('l_item_data_level_id_str: '||l_item_data_level_id_str);
1513:
1514: l_erase_revision_sql :=
1515: q'#UPDATE EGO_ITM_USR_ATTR_INTRFC S
1516: SET REVISION = NULL, REVISION_ID = NULL
1517: WHERE EXISTS (SELECT A.ROWID
1518: FROM EGO_OBJ_AG_ASSOCS_B A
1519: WHERE A.CLASSIFICATION_CODE = S.ITEM_CATALOG_GROUP_ID
1536: -- if any row has an invalid Org ID, error it out along with all other rows --
1537: -- in its logical Attribute Group row (because it won't make sense to keep --
1538: -- processing the errored-out row's companions without the errored-out row) --
1539: ------------------------------------------------------------------------------
1540: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1541: SET UAI.PROCESS_STATUS = G_PS_BAD_ORG_ID
1542: WHERE UAI.DATA_SET_ID = p_data_set_id
1543: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1544: AND UAI.ROW_IDENTIFIER IN
1542: WHERE UAI.DATA_SET_ID = p_data_set_id
1543: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
1544: AND UAI.ROW_IDENTIFIER IN
1545: (SELECT DISTINCT UAI2.ROW_IDENTIFIER
1546: FROM EGO_ITM_USR_ATTR_INTRFC UAI2
1547: WHERE UAI2.DATA_SET_ID = p_data_set_id
1548: AND UAI2.PROCESS_STATUS = G_PS_IN_PROCESS
1549: AND UAI2.ORGANIZATION_ID IS NOT NULL
1550: AND NOT EXISTS
1568: -- where data_level_id is not populated --
1569: ----------------------------------------------------
1570: -- Bug 13434831 : Start (Performance Change)
1571: /*
1572: UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1573: SET uai.DATA_LEVEL_ID = (SELECT edlb.DATA_LEVEL_ID
1574: FROM EGO_DATA_LEVEL_B edlb
1575: WHERE edlb.DATA_LEVEL_NAME = uai.DATA_LEVEL_NAME
1576: AND edlb.APPLICATION_ID = 431
1587: -- data_level_id where data_level_id is not --
1588: -- populated and data_level_name is also not populated --
1589: ----------------------------------------------------------
1590:
1591: UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1592: SET uai.DATA_LEVEL_ID = (SELECT edlv.DATA_LEVEL_ID
1593: FROM EGO_DATA_LEVEL_VL edlv
1594: WHERE edlv.USER_DATA_LEVEL_NAME = uai.USER_DATA_LEVEL_NAME
1595: AND edlv.APPLICATION_ID = 431
1602: AND uai.DATA_LEVEL_ID IS NULL;
1603: */
1604:
1605: -- Consolidating above two queries into single query as below.
1606: UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1607: SET uai.DATA_LEVEL_ID = Nvl ((SELECT edlb.DATA_LEVEL_ID
1608: FROM EGO_DATA_LEVEL_B edlb
1609: WHERE edlb.DATA_LEVEL_NAME = uai.DATA_LEVEL_NAME
1610: AND edlb.APPLICATION_ID = 431
1627: -- If all data level columns are null, then check if the --
1628: -- attribute group is associated at only one level, then --
1629: -- put that data level id here. --
1630: -----------------------------------------------------------
1631: UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1632: SET DATA_LEVEL_ID = (SELECT DATA_LEVEL_ID
1633: FROM EGO_ATTR_GROUP_DL
1634: WHERE ATTR_GROUP_ID = uai.ATTR_GROUP_ID
1635: )
1646: -------------------------------------------------
1647: -- Now, mark all the rows that does not have a --
1648: -- valid data_level_id populated --
1649: -------------------------------------------------
1650: UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1651: SET uai.PROCESS_STATUS = G_PS_BAD_DATA_LEVEL
1652: WHERE uai.DATA_SET_ID = p_data_set_id
1653: AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1654: AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1652: WHERE uai.DATA_SET_ID = p_data_set_id
1653: AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1654: AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1655: uai2.ROW_IDENTIFIER
1656: FROM EGO_ITM_USR_ATTR_INTRFC uai2
1657: WHERE uai2.DATA_SET_ID = p_data_set_id
1658: AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
1659: AND NOT EXISTS (SELECT NULL
1660: FROM EGO_DATA_LEVEL_B edlb
1666: -- Validating the Orag Id to be master org for --
1667: -- item, supplier and supplier site level --
1668: --------------------------------------------------
1669:
1670: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
1671: --SET UAI.PROCESS_STATUS = G_PS_BAD_ORG_ID
1672: -- bug 8649262 Restore to Pre-R12C behavior, mark with Generic Error so
1673: -- this line will not be processed
1674: SET UAI.PROCESS_STATUS = G_PS_GENERIC_ERROR
1700: -- Next, validate the Item Supplier attrs. Validate that the --
1701: -- pk1_value exists in ego_item_associations for this item --
1702: ----------------------------------------------------------------
1703: IF p_validate_only = FND_API.G_FALSE THEN
1704: UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1705: SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER
1706: WHERE uai.DATA_SET_ID = p_data_set_id
1707: AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1708: AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1706: WHERE uai.DATA_SET_ID = p_data_set_id
1707: AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1708: AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1709: UAI2.ROW_IDENTIFIER
1710: FROM EGO_ITM_USR_ATTR_INTRFC uai2
1711: WHERE uai2.DATA_SET_ID = p_data_set_id
1712: AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
1713: AND uai2.DATA_LEVEL_ID = l_item_sup_dl_id
1714: AND ( uai2.PK1_VALUE IS NULL
1731: );
1732: ELSE
1733: -- Bug 13434831 : Start (Performance Change)
1734: /*
1735: UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1736: SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER
1737: WHERE uai.DATA_SET_ID = p_data_set_id
1738: AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1739: AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1737: WHERE uai.DATA_SET_ID = p_data_set_id
1738: AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1739: AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1740: UAI2.ROW_IDENTIFIER
1741: FROM EGO_ITM_USR_ATTR_INTRFC uai2
1742: WHERE uai2.DATA_SET_ID = p_data_set_id
1743: AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
1744: AND uai2.DATA_LEVEL_ID = l_item_sup_dl_id
1745: AND ( uai2.PK1_VALUE IS NULL
1771: )
1772: );
1773: */
1774: -- above query is modifed a below.
1775: UPDATE ego_itm_usr_attr_intrfc uai
1776: SET uai.process_status = G_PS_BAD_SUPPLIER
1777: WHERE uai.data_set_id = p_data_set_id
1778: AND uai.process_status = G_PS_IN_PROCESS
1779: AND uai.data_level_id = l_item_sup_dl_id
1807: -- Next, validate the Item Supplier site attrs. Validate that --
1808: -- the pk2_value exists in ego_item_associations for this item --
1809: -----------------------------------------------------------------
1810: IF p_validate_only = FND_API.G_FALSE THEN
1811: UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1812: SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE
1813: WHERE uai.DATA_SET_ID = p_data_set_id
1814: AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1815: AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1813: WHERE uai.DATA_SET_ID = p_data_set_id
1814: AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1815: AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1816: UAI2.ROW_IDENTIFIER
1817: FROM EGO_ITM_USR_ATTR_INTRFC uai2
1818: WHERE uai2.DATA_SET_ID = p_data_set_id
1819: AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
1820: AND uai2.DATA_LEVEL_ID = l_item_sup_site_dl_id
1821: AND ( uai2.PK1_VALUE IS NULL
1837: );
1838: ELSE
1839: -- Bug 13434831 : Start (Performance Change)
1840: /*
1841: UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1842: SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE
1843: WHERE uai.DATA_SET_ID = p_data_set_id
1844: AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1845: AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1843: WHERE uai.DATA_SET_ID = p_data_set_id
1844: AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1845: AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1846: UAI2.ROW_IDENTIFIER
1847: FROM EGO_ITM_USR_ATTR_INTRFC uai2
1848: WHERE uai2.DATA_SET_ID = p_data_set_id
1849: AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
1850: AND uai2.DATA_LEVEL_ID = l_item_sup_site_dl_id
1851: AND ( uai2.PK1_VALUE IS NULL
1877: )
1878: );
1879: */
1880:
1881: UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1882: SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE
1883: WHERE uai.DATA_SET_ID = p_data_set_id
1884: AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1885: AND uai.DATA_LEVEL_ID = l_item_sup_site_dl_id
1919: -- that the pk1,pk2_value along with the organization_id exists --
1920: -- in ego_item_associations for this item --
1921: ------------------------------------------------------------------
1922: IF p_validate_only = FND_API.G_FALSE THEN
1923: UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1924: SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE_ORG
1925: WHERE uai.DATA_SET_ID = p_data_set_id
1926: AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1927: AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1925: WHERE uai.DATA_SET_ID = p_data_set_id
1926: AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1927: AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1928: UAI2.ROW_IDENTIFIER
1929: FROM EGO_ITM_USR_ATTR_INTRFC uai2
1930: WHERE uai2.DATA_SET_ID = p_data_set_id
1931: AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
1932: AND uai2.DATA_LEVEL_ID = l_item_sup_site_org_dl_id
1933: AND ( uai2.PK1_VALUE IS NULL
1949: );
1950: ELSE
1951: -- Bug 13434831 : Start (Performance Change)
1952: /*
1953: UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1954: SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE_ORG
1955: WHERE uai.DATA_SET_ID = p_data_set_id
1956: AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1957: AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1955: WHERE uai.DATA_SET_ID = p_data_set_id
1956: AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1957: AND uai.ROW_IDENTIFIER IN (SELECT DISTINCT
1958: UAI2.ROW_IDENTIFIER
1959: FROM EGO_ITM_USR_ATTR_INTRFC uai2
1960: WHERE uai2.DATA_SET_ID = p_data_set_id
1961: AND uai2.PROCESS_STATUS = G_PS_IN_PROCESS
1962: AND uai2.DATA_LEVEL_ID = l_item_sup_site_org_dl_id
1963: AND ( uai2.PK1_VALUE IS NULL
1988: )
1989: )
1990: );
1991: */
1992: UPDATE EGO_ITM_USR_ATTR_INTRFC uai
1993: SET uai.PROCESS_STATUS = G_PS_BAD_SUPPLIER_SITE_ORG
1994: WHERE uai.DATA_SET_ID = p_data_set_id
1995: AND uai.PROCESS_STATUS = G_PS_IN_PROCESS
1996: AND uai.DATA_LEVEL_ID = l_item_sup_site_org_dl_id
2029: -- Mark as errors all rows that share the same logical Attribute Group --
2030: -- Variant attribute values for existing SKUs can not be updated --
2031: --------------------------------------------------------------------------------
2032: IF p_validate_only = FND_API.G_TRUE THEN
2033: UPDATE EGO_ITM_USR_ATTR_INTRFC
2034: SET PROCESS_STATUS = G_PS_SKU_VAR_VALUE_NOT_UPD
2035: WHERE DATA_SET_ID = p_data_set_id
2036: AND PROCESS_STATUS = G_PS_IN_PROCESS
2037: AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
2035: WHERE DATA_SET_ID = p_data_set_id
2036: AND PROCESS_STATUS = G_PS_IN_PROCESS
2037: AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
2038: FROM
2039: EGO_ITM_USR_ATTR_INTRFC intf,
2040: EGO_FND_DSC_FLX_CTX_EXT ag_ext,
2041: MTL_SYSTEM_ITEMS_B msib
2042: WHERE intf.DATA_SET_ID = p_data_set_id
2043: AND intf.PROCESS_STATUS = G_PS_IN_PROCESS
2060: --------------------------------------------------------------------------------
2061: -- Mark as errors all rows that share the same logical Attribute Group --
2062: -- Inherited attribute values can not be processed for SKUs --
2063: --------------------------------------------------------------------------------
2064: UPDATE EGO_ITM_USR_ATTR_INTRFC
2065: SET PROCESS_STATUS = G_PS_INH_ATTR_FOR_SKU_NOT_UPD
2066: WHERE DATA_SET_ID = p_data_set_id
2067: AND PROCESS_STATUS = G_PS_IN_PROCESS
2068: AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
2066: WHERE DATA_SET_ID = p_data_set_id
2067: AND PROCESS_STATUS = G_PS_IN_PROCESS
2068: AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
2069: FROM
2070: EGO_ITM_USR_ATTR_INTRFC intf,
2071: EGO_ATTR_GROUP_DL eagd
2072: WHERE intf.DATA_SET_ID = p_data_set_id
2073: AND intf.PROCESS_STATUS = G_PS_IN_PROCESS
2074: AND intf.ATTR_GROUP_ID = eagd.ATTR_GROUP_ID
2094: ---------------------------------------
2095: -- Set Lifecycle of the revision items.
2096: ---------------------------------------
2097: IF p_validate_only = FND_API.G_FALSE THEN
2098: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
2099: SET (UAI.PROG_INT_NUM3)
2100: = NVL((SELECT MIR.CURRENT_PHASE_ID
2101: FROM MTL_ITEM_REVISIONS MIR
2102: WHERE MIR.REVISION_ID = UAI.REVISION_ID
2114: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
2115: AND UAI.REVISION_ID IS NOT NULL
2116: AND UAI.ITEM_CATALOG_GROUP_ID IS NOT NULL;
2117: ELSE
2118: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
2119: SET UAI.PROG_INT_NUM3
2120: = NVL((CASE WHEN EXISTS (SELECT 1
2121: FROM MTL_ITEM_REVISIONS mir
2122: WHERE mir.REVISION_ID = UAI.REVISION_ID
2152:
2153: ----------------------------------------------
2154: -- Set the actual cc where LC is associated --
2155: ----------------------------------------------
2156: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
2157: SET UAI.PROG_INT_NUM1 =
2158: (SELECT ic.item_catalog_group_id
2159: FROM mtl_item_catalog_groups_b ic
2160: WHERE EXISTS
2176: AND UAI.PROG_INT_CHAR2 = 'N';
2177: -- write_records(p_data_set_id => p_data_set_id, p_module => l_api_name, p_message => 'After hier init');
2178:
2179: l_policy_check_sql :=
2180: ' UPDATE EGO_ITM_USR_ATTR_INTRFC UAI '||
2181: ' SET UAI.PROCESS_STATUS = :1 '||
2182: ' WHERE UAI.DATA_SET_ID = :2 '||
2183: ' AND UAI.PROCESS_STATUS = :3 '||
2184: ' AND UAI.ROW_IDENTIFIER IN '||
2182: ' WHERE UAI.DATA_SET_ID = :2 '||
2183: ' AND UAI.PROCESS_STATUS = :3 '||
2184: ' AND UAI.ROW_IDENTIFIER IN '||
2185: ' (SELECT DISTINCT UAI2.ROW_IDENTIFIER '||
2186: ' FROM EGO_ITM_USR_ATTR_INTRFC UAI2, ENG_CHANGE_POLICIES_V ECP '||
2187: ' WHERE UAI2.DATA_SET_ID = :4 '||
2188: ' AND UAI2.PROCESS_STATUS = :5 '||
2189: ' AND UAI2.ITEM_CATALOG_GROUP_ID IS NOT NULL '||
2190: ' AND UAI2.PROG_INT_NUM2 IS NOT NULL '||
2240: EGO_USER_ATTRS_DATA_PVT.Debug_Msg('Starting Style value set processing', 1);
2241:
2242: -- Fix for bug#9336604
2243: -- Added index hint
2244: UPDATE /*+ index(INTF, EGO_ITM_USR_ATTR_INTRFC_N3 ) */ EGO_ITM_USR_ATTR_INTRFC INTF
2245: SET PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
2246: WHERE INTF.DATA_SET_ID = p_data_set_id
2247: AND INTF.PROCESS_STATUS = G_PS_IN_PROCESS
2248: AND EXISTS (SELECT NULL
2271: -------------------------------------------------------------------------------
2272: -- Mark as errors all rows that share the same logical Attribute Group --
2273: -- Associated value set can not be changed if any sku exists for the style --
2274: -------------------------------------------------------------------------------
2275: UPDATE EGO_ITM_USR_ATTR_INTRFC
2276: SET PROCESS_STATUS = G_PS_VAR_VSET_CHG_NOT_ALLOWED
2277: WHERE DATA_SET_ID = p_data_set_id
2278: AND PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
2279: AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
2276: SET PROCESS_STATUS = G_PS_VAR_VSET_CHG_NOT_ALLOWED
2277: WHERE DATA_SET_ID = p_data_set_id
2278: AND PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
2279: AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
2280: FROM EGO_ITM_USR_ATTR_INTRFC INTF, MTL_SYSTEM_ITEMS_KFV MSIK
2281: WHERE INTF.DATA_SET_ID = p_data_set_id
2282: AND INTF.PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
2283: AND MSIK.STYLE_ITEM_ID = INTF.INVENTORY_ITEM_ID
2284: AND MSIK.ORGANIZATION_ID = INTF.ORGANIZATION_ID
2290: -- Converting all the value set names entered by user to value set id --
2291: -- Value set name is expected in ATTR_DISP_VALUE column, we convert it to --
2292: -- value set id and store it in ATTR_VALUE_NUM col. --
2293: -------------------------------------------------------------------------------
2294: UPDATE EGO_ITM_USR_ATTR_INTRFC INTF
2295: SET ATTR_VALUE_NUM = (SELECT VALUE_SET_ID FROM EGO_VALUE_SETS_V VS
2296: WHERE VALUE_SET_NAME = INTF.ATTR_DISP_VALUE)
2297: WHERE INTF.DATA_SET_ID = p_data_set_id
2298: AND INTF.PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
2304: -- Mark as errors all rows that share the same logical Attribute Group --
2305: -- If not a valid value set i.e. Only value set associated at attribute level --
2306: -- or its child value set can be associated. --
2307: --------------------------------------------------------------------------------
2308: UPDATE EGO_ITM_USR_ATTR_INTRFC
2309: SET PROCESS_STATUS = G_PS_BAD_STYLE_VAR_VALUE_SET
2310: WHERE DATA_SET_ID = p_data_set_id
2311: AND PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
2312: AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
2309: SET PROCESS_STATUS = G_PS_BAD_STYLE_VAR_VALUE_SET
2310: WHERE DATA_SET_ID = p_data_set_id
2311: AND PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
2312: AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
2313: FROM EGO_ITM_USR_ATTR_INTRFC INTF
2314: WHERE INTF.DATA_SET_ID = p_data_set_id
2315: AND INTF.PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS
2316: AND NOT EXISTS (SELECT NULL
2317: FROM FND_DESCR_FLEX_COLUMN_USAGES FL_COL
2339: INTF.INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID,
2340: INTF.ATTR_VALUE_NUM AS VALUE_SET_ID,
2341: ATTR.ATTR_ID AS ATTRIBUTE_ID
2342: FROM
2343: EGO_ITM_USR_ATTR_INTRFC INTF,
2344: EGO_FND_DF_COL_USGS_EXT ATTR,
2345: FND_DESCR_FLEX_COLUMN_USAGES FL_COL,
2346: MTL_SYSTEM_ITEMS_B MSIB
2347: WHERE INTF.ATTR_GROUP_TYPE = ATTR.DESCRIPTIVE_FLEXFIELD_NAME
2389: ---------------------------------------------------------------------------------
2390: -- Marking all these records as processed --
2391: -- All the records that are in status G_PS_STYLE_VARIANT_IN_PROCESS to success --
2392: ---------------------------------------------------------------------------------
2393: UPDATE EGO_ITM_USR_ATTR_INTRFC
2394: SET PROCESS_STATUS = G_PS_SUCCESS
2395: WHERE DATA_SET_ID = p_data_set_id
2396: AND PROCESS_STATUS = G_PS_STYLE_VARIANT_IN_PROCESS;
2397:
2486: l_error_attr_name := l_err_value;
2487: l_mark_error_record := TRUE;
2488: END IF;
2489: IF (l_mark_error_record) THEN
2490: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
2491: SET UAI.PROCESS_STATUS = G_COM_VALDN_FAIL
2492: WHERE UAI.DATA_SET_ID = p_data_set_id
2493: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
2494: AND UAI.ROW_IDENTIFIER = l_error_row_identifier
2852: -- aren't passed as a bind variable doesn't matter, because --
2853: -- the statement won't get parsed more than once anyway). --
2854: ---------------------------------------------------------------
2855:
2856: l_dynamic_sql := ' UPDATE EGO_ITM_USR_ATTR_INTRFC'||
2857: ' SET PROCESS_STATUS = '||G_PS_GENERIC_ERROR||
2858: ' WHERE DATA_SET_ID = :1'||
2859: ' AND (PROCESS_STATUS IN ('||G_PS_BAD_ORG_ID||', '||
2860: G_PS_BAD_ORG_CODE||', '||
2881:
2882: -- Telco Item UDA validation --
2883: /*IF (profile_value = 'Y') THEN
2884:
2885: l_dynamic_sqlt := ' UPDATE EGO_ITM_USR_ATTR_INTRFC'||
2886: ' SET PROCESS_STATUS = '||G_PS_GENERIC_ERROR||
2887: ' WHERE DATA_SET_ID = :1'||
2888: ' AND (PROCESS_STATUS IN ('||G_COM_VALDN_FAIL||') )';
2889:
2948: IF (l_return = TRUE) THEN
2949: SELECT
2950: COUNT(DATA_SET_ID)
2951: INTO l_rec_count
2952: FROM EGO_ITM_USR_ATTR_INTRFC intf
2953: WHERE DATA_SET_ID = p_data_set_id
2954: AND (PROCESS_STATUS IS NULL OR PROCESS_STATUS IN (G_PS_TO_BE_PROCESSED, G_PS_IN_PROCESS, G_PS_STYLE_VARIANT_IN_PROCESS) );
2955:
2956: IF (l_rec_count = 0) THEN
2996: l_item_gtin_multi_count := 0;
2997:
2998: for i in (SELECT Count(1) as ag_type_count,
2999: attr_group_type
3000: FROM EGO_ITM_USR_ATTR_INTRFC
3001: WHERE DATA_SET_ID = p_data_set_id
3002: AND attr_group_type IN ('EGO_ITEM_GTIN_MULTI_ATTRS', 'EGO_ITEM_GTIN_ATTRS', 'EGO_ITEMMGMT_GROUP')
3003: GROUP BY attr_Group_type)
3004: loop
3014: -- Commenting the below query and getting the counts for 3 AG types using single query as above.
3015: /*
3016: SELECT Count(1)
3017: INTO l_item_mgmt_count
3018: FROM EGO_ITM_USR_ATTR_INTRFC
3019: WHERE data_set_id = p_data_set_id
3020: AND attr_group_type = 'EGO_ITEMMGMT_GROUP'
3021: AND ROWNUM = 1;
3022: */
3030: ,p_application_id => 431 --IN NUMBER
3031: ,p_attr_group_type => 'EGO_ITEMMGMT_GROUP' --IN VARCHAR2
3032: ,p_object_name => G_ITEM_NAME --IN VARCHAR2
3033: ,p_hz_party_id => G_HZ_PARTY_ID --IN VARCHAR2
3034: ,p_interface_table_name => 'EGO_ITM_USR_ATTR_INTRFC' --IN VARCHAR2
3035: ,p_data_set_id => p_data_set_id --IN NUMBER
3036: ,p_entity_id => G_ENTITY_ID --IN NUMBER
3037: ,p_entity_index => l_entity_index_counter --IN NUMBER
3038: ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
3073: -- --
3074: -- This validation needs to be done after the normal UDA validations are over --
3075: ---------------------------------------------------------------------------------
3076:
3077: UPDATE EGO_ITM_USR_ATTR_INTRFC
3078: SET PROCESS_STATUS = G_PS_BAD_SKU_VAR_VALUE
3079: WHERE DATA_SET_ID = p_data_set_id
3080: AND PROCESS_STATUS = G_PS_IN_PROCESS
3081: AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
3080: AND PROCESS_STATUS = G_PS_IN_PROCESS
3081: AND ROW_IDENTIFIER IN (SELECT ROW_IDENTIFIER
3082: FROM
3083: EGO_FND_DSC_FLX_CTX_EXT AG_EXT,
3084: EGO_ITM_USR_ATTR_INTRFC INTF,
3085: EGO_STYLE_VARIANT_ATTR_VS SVA,
3086: EGO_FND_DF_COL_USGS_EXT ATTR,
3087: FND_DESCR_FLEX_COLUMN_USAGES FL_COL,
3088: MTL_SYSTEM_ITEMS_INTERFACE MSII
3129:
3130: ---------------------------------------------------------------------------------
3131: -- Adding validation errors for Style/SKU related validations --
3132: ---------------------------------------------------------------------------------
3133: FOR i IN (SELECT /*+ INDEX(EGO_ITM_USR_ATTR_INTRFC, EGO_ITM_USR_ATTR_INTRFC_N3) */ /* Bug 9678667 */
3134: PROCESS_STATUS, TRANSACTION_ID, ATTR_VALUE_STR, ATTR_VALUE_NUM, ATTR_VALUE_DATE, ATTR_DISP_VALUE, ITEM_NUMBER
3135: FROM EGO_ITM_USR_ATTR_INTRFC
3136: WHERE DATA_SET_ID = p_data_set_id
3137: AND PROCESS_STATUS = G_PS_BAD_SKU_VAR_VALUE
3131: -- Adding validation errors for Style/SKU related validations --
3132: ---------------------------------------------------------------------------------
3133: FOR i IN (SELECT /*+ INDEX(EGO_ITM_USR_ATTR_INTRFC, EGO_ITM_USR_ATTR_INTRFC_N3) */ /* Bug 9678667 */
3134: PROCESS_STATUS, TRANSACTION_ID, ATTR_VALUE_STR, ATTR_VALUE_NUM, ATTR_VALUE_DATE, ATTR_DISP_VALUE, ITEM_NUMBER
3135: FROM EGO_ITM_USR_ATTR_INTRFC
3136: WHERE DATA_SET_ID = p_data_set_id
3137: AND PROCESS_STATUS = G_PS_BAD_SKU_VAR_VALUE
3138: )
3139: LOOP
3164:
3165: ---------------------------------------------------
3166: -- Marking all these rows to process_status = 3 --
3167: ---------------------------------------------------
3168: UPDATE EGO_ITM_USR_ATTR_INTRFC
3169: SET PROCESS_STATUS = G_PS_GENERIC_ERROR
3170: WHERE DATA_SET_ID = p_data_set_id
3171: AND PROCESS_STATUS = G_PS_BAD_SKU_VAR_VALUE;
3172:
3181: -- Commenting the below query as we got count for AG type EGO_ITEM_GTIN_ATTRS already
3182: /*
3183: SELECT Count(1)
3184: INTO l_item_gtin_count
3185: FROM EGO_ITM_USR_ATTR_INTRFC
3186: WHERE data_set_id = p_data_set_id
3187: AND attr_group_type = 'EGO_ITEM_GTIN_ATTRS'
3188: AND ROWNUM = 1;
3189: */
3197: ,p_application_id => 431 --IN NUMBER
3198: ,p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS' --IN VARCHAR2
3199: ,p_object_name => G_ITEM_NAME --IN VARCHAR2
3200: ,p_hz_party_id => G_HZ_PARTY_ID --IN VARCHAR2
3201: ,p_interface_table_name => 'EGO_ITM_USR_ATTR_INTRFC' --IN VARCHAR2
3202: ,p_data_set_id => p_data_set_id --IN NUMBER
3203: ,p_entity_id => G_ENTITY_ID --IN NUMBER
3204: ,p_entity_index => l_entity_index_counter --IN NUMBER
3205: ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
3237: -- Commenting the below query as we got count for AG type EGO_ITEM_GTIN_MULTI_ATTRS already
3238: /*
3239: SELECT Count(1)
3240: INTO l_item_gtin_multi_count
3241: FROM EGO_ITM_USR_ATTR_INTRFC
3242: WHERE data_set_id = p_data_set_id
3243: AND attr_group_type = 'EGO_ITEM_GTIN_MULTI_ATTRS'
3244: AND ROWNUM = 1;
3245: */
3253: ,p_application_id => 431 --IN NUMBER
3254: ,p_attr_group_type => 'EGO_ITEM_GTIN_MULTI_ATTRS' --IN VARCHAR2
3255: ,p_object_name => G_ITEM_NAME --IN VARCHAR2
3256: ,p_hz_party_id => G_HZ_PARTY_ID --IN VARCHAR2
3257: ,p_interface_table_name => 'EGO_ITM_USR_ATTR_INTRFC' --IN VARCHAR2
3258: ,p_data_set_id => p_data_set_id --IN NUMBER
3259: ,p_entity_id => G_ENTITY_ID --IN NUMBER
3260: ,p_entity_index => l_entity_index_counter --IN NUMBER
3261: ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
3300: p_api_version =>1.0
3301: ,p_application_id =>431
3302: ,p_attr_group_type =>'EGO_ITEMMGMT_GROUP'
3303: ,p_object_name =>'EGO_ITEM'
3304: ,p_interface_table_name =>'EGO_ITM_USR_ATTR_INTRFC'
3305: ,p_data_set_id =>p_data_set_id
3306: ,p_new_status =>4
3307: ,p_commit =>p_commit
3308: ,x_return_status =>l_return_status
3336: p_api_version =>1.0
3337: ,p_application_id =>431
3338: ,p_attr_group_type =>'EGO_ITEM_GTIN_ATTRS'
3339: ,p_object_name =>'EGO_ITEM'
3340: ,p_interface_table_name =>'EGO_ITM_USR_ATTR_INTRFC'
3341: ,p_data_set_id =>p_data_set_id
3342: ,p_new_status =>4
3343: ,p_commit =>p_commit
3344: ,x_return_status =>l_return_status
3369: p_api_version =>1.0
3370: ,p_application_id =>431
3371: ,p_attr_group_type =>'EGO_ITEM_GTIN_MULTI_ATTRS'
3372: ,p_object_name =>'EGO_ITEM'
3373: ,p_interface_table_name =>'EGO_ITM_USR_ATTR_INTRFC'
3374: ,p_data_set_id =>p_data_set_id
3375: ,p_new_status =>4
3376: ,p_commit =>p_commit
3377: ,x_return_status =>l_return_status
3415: --------------------------------------------------------------------------------------
3416: l_add_all_to_cm := EGO_IMPORT_PVT.getAddAllToChangeFlag(p_batch_id => p_data_set_id);
3417: EGO_USER_ATTRS_DATA_PVT.Debug_Msg(' Add all to Change flag is '||l_add_all_to_cm, 0);
3418: IF( l_add_all_to_cm = 'Y' ) THEN
3419: /*UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
3420: SET UAI.PROCESS_STATUS = G_PS_CHG_POLICY_CO_REQUIRED
3421: WHERE UAI.DATA_SET_ID = p_data_set_id
3422: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
3423: AND UAI.PROG_INT_CHAR2 = 'N' OR UAI.DATA_LEVEL_ID = 43106;--BugFix:6315828(for revisions even in case revision is created in the batch
3422: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
3423: AND UAI.PROG_INT_CHAR2 = 'N' OR UAI.DATA_LEVEL_ID = 43106;--BugFix:6315828(for revisions even in case revision is created in the batch
3424: --the attrs should go through the CO int his case.*/
3425: -- Bug 9705689 adding condition to the filter for revision level updated item
3426: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
3427: SET UAI.PROCESS_STATUS = G_PS_CHG_POLICY_CO_REQUIRED
3428: WHERE UAI.DATA_SET_ID = p_data_set_id
3429: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
3430: AND UAI.PROG_INT_CHAR2 = 'N';
3428: WHERE UAI.DATA_SET_ID = p_data_set_id
3429: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
3430: AND UAI.PROG_INT_CHAR2 = 'N';
3431:
3432: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
3433: SET UAI.PROCESS_STATUS = G_PS_CHG_POLICY_CO_REQUIRED
3434: WHERE UAI.DATA_LEVEL_ID = 43106
3435: AND UAI.DATA_SET_ID = p_data_set_id
3436: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
3444: AND MSII.PROCESS_FLAG IN (1, 7)
3445: AND MSII.TRANSACTION_TYPE = 'UPDATE'
3446: );
3447:
3448: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
3449: SET UAI.TRANSACTION_TYPE = 'CREATE'
3450: WHERE UAI.DATA_LEVEL_ID = 43106
3451: AND UAI.DATA_SET_ID = p_data_set_id
3452: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
3486: --------------------------------------------------------------
3487: FOR catalog_groups IN distinct_catalaog_groups(p_data_set_id)
3488: LOOP
3489: IF (INVIDIT3.CHECK_NPR_CATALOG(catalog_groups.ITEM_CATALOG_GROUP_ID)) THEN
3490: UPDATE EGO_ITM_USR_ATTR_INTRFC UAI
3491: SET UAI.PROCESS_STATUS = G_PS_CHG_POLICY_CO_REQUIRED
3492: WHERE UAI.DATA_SET_ID = p_data_set_id
3493: AND UAI.PROCESS_STATUS = G_PS_IN_PROCESS
3494: AND UAI.ITEM_CATALOG_GROUP_ID = catalog_groups.ITEM_CATALOG_GROUP_ID;
3527: ,p_application_id => 431 --IN NUMBER
3528: ,p_attr_group_type => 'EGO_ITEMMGMT_GROUP' --IN VARCHAR2
3529: ,p_object_name => G_ITEM_NAME --IN VARCHAR2
3530: ,p_hz_party_id => G_HZ_PARTY_ID --IN VARCHAR2
3531: ,p_interface_table_name => 'EGO_ITM_USR_ATTR_INTRFC' --IN VARCHAR2
3532: ,p_data_set_id => p_data_set_id --IN NUMBER
3533: ,p_entity_id => G_ENTITY_ID --IN NUMBER
3534: ,p_entity_index => l_entity_index_counter --IN NUMBER
3535: ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
3566: ,p_application_id => 431 --IN NUMBER
3567: ,p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS' --IN VARCHAR2
3568: ,p_object_name => G_ITEM_NAME --IN VARCHAR2
3569: ,p_hz_party_id => G_HZ_PARTY_ID --IN VARCHAR2
3570: ,p_interface_table_name => 'EGO_ITM_USR_ATTR_INTRFC' --IN VARCHAR2
3571: ,p_data_set_id => p_data_set_id --IN NUMBER
3572: ,p_entity_id => G_ENTITY_ID --IN NUMBER
3573: ,p_entity_index => l_entity_index_counter --IN NUMBER
3574: ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
3606: ,p_application_id => 431 --IN NUMBER
3607: ,p_attr_group_type => 'EGO_ITEM_GTIN_MULTI_ATTRS' --IN VARCHAR2
3608: ,p_object_name => G_ITEM_NAME --IN VARCHAR2
3609: ,p_hz_party_id => G_HZ_PARTY_ID --IN VARCHAR2
3610: ,p_interface_table_name => 'EGO_ITM_USR_ATTR_INTRFC' --IN VARCHAR2
3611: ,p_data_set_id => p_data_set_id --IN NUMBER
3612: ,p_entity_id => G_ENTITY_ID --IN NUMBER
3613: ,p_entity_index => l_entity_index_counter --IN NUMBER
3614: ,p_entity_code => G_ENTITY_CODE --IN VARCHAR2
3663: -----------------------------------------------
3664: -- Delete all successful rows from the table --
3665: -- (they're the only rows still in process) --
3666: -----------------------------------------------
3667: DELETE FROM EGO_ITM_USR_ATTR_INTRFC
3668: WHERE DATA_SET_ID = p_data_set_id
3669: AND PROCESS_STATUS = G_PS_IN_PROCESS;
3670: ELSE
3671: ----------------------------------------------
3671: ----------------------------------------------
3672: -- Mark all rows we've processed as success --
3673: -- if they weren't marked as failure above --
3674: ----------------------------------------------
3675: UPDATE EGO_ITM_USR_ATTR_INTRFC
3676: SET PROCESS_STATUS = G_PS_SUCCESS
3677: WHERE DATA_SET_ID = p_data_set_id
3678: AND PROCESS_STATUS = G_PS_IN_PROCESS;
3679: END IF;
3708:
3709: ----------------------------------------
3710: -- Mark all rows in process as errors --
3711: ----------------------------------------
3712: UPDATE EGO_ITM_USR_ATTR_INTRFC
3713: SET PROCESS_STATUS = G_PS_GENERIC_ERROR
3714: WHERE DATA_SET_ID = p_data_set_id
3715: AND PROCESS_STATUS = G_PS_IN_PROCESS;
3716:
3720: ---------------------------------------------------------------------
3721: IF SQL%ROWCOUNT > 0 THEN
3722: SELECT TRANSACTION_ID
3723: INTO l_err_reporting_transaction_id
3724: FROM EGO_ITM_USR_ATTR_INTRFC
3725: WHERE DATA_SET_ID = p_data_set_id
3726: AND ROWNUM = 1;
3727: ELSE
3728: l_err_reporting_transaction_id := -1;
3761:
3762: ----------------------------------------
3763: -- Mark all rows in process as errors --
3764: ----------------------------------------
3765: UPDATE EGO_ITM_USR_ATTR_INTRFC
3766: SET PROCESS_STATUS = G_PS_GENERIC_ERROR
3767: WHERE DATA_SET_ID = p_data_set_id
3768: AND PROCESS_STATUS = G_PS_IN_PROCESS;
3769:
3773: ---------------------------------------------------------------------
3774: IF SQL%ROWCOUNT > 0 THEN
3775: SELECT TRANSACTION_ID
3776: INTO l_err_reporting_transaction_id
3777: FROM EGO_ITM_USR_ATTR_INTRFC
3778: WHERE DATA_SET_ID = p_data_set_id
3779: AND ROWNUM = 1;
3780: ELSE
3781: l_err_reporting_transaction_id := -1;
3998:
3999: BEGIN
4000: SELECT NVL(MAX(row_identifier),0)
4001: INTO l_max_row_identifier
4002: FROM EGO_ITM_USR_ATTR_INTRFC
4003: WHERE DATA_SET_ID = p_dest_data_set_id;
4004: EXCEPTION
4005: WHEN OTHERS THEN
4006: l_max_row_identifier := 0;
4098: WHERE ag_ext.DESCRIPTIVE_FLEXFIELD_NAME = NVL(src.ATTR_GROUP_TYPE, 'EGO_ITEMMGMT_GROUP')
4099: AND ag_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE = src.ATTR_GROUP_INT_NAME
4100: AND ag_ext.APPLICATION_ID = 431
4101: AND ( ag_ext.MULTI_ROW= 'N'
4102: OR NOT EXISTS (SELECT NULL FROM EGO_ITM_USR_ATTR_INTRFC intfx
4103: WHERE intfx.DATA_SET_ID = src.DATA_SET_ID
4104: AND intfx.PROCESS_STATUS = #'|| p_dest_process_status || q'#
4105: AND intfx.INVENTORY_ITEM_ID = src.INVENTORY_ITEM_ID
4106: AND intfx.ORGANIZATION_ID = src.ORGANIZATION_ID
4190: source.PROG_INT_CHAR1,
4191: source.PROG_INT_NUM4,
4192: source.BUNDLE_ID )
4193: #';
4194: l_dynamic_sql := 'MERGE INTO EGO_ITM_USR_ATTR_INTRFC intf USING ' ||
4195: l_select_sql || l_where_clause || l_when_matched || l_when_not_matched;
4196:
4197: Debug_Conc_Log('Copy_data_to_Intf: l_dynamic_sql='||l_dynamic_sql);
4198: EXECUTE IMMEDIATE l_dynamic_sql;
4310: AND ag_ext.DESCRIPTIVE_FLEXFIELD_NAME = attr_col.DESCRIPTIVE_FLEXFIELD_NAME
4311: AND ag_ext.DESCRIPTIVE_FLEX_CONTEXT_CODE = attr_col.DESCRIPTIVE_FLEX_CONTEXT_CODE
4312: AND ag_ext.APPLICATION_ID = attr_col.APPLICATION_ID
4313: AND attr_col.ENABLED_FLAG = 'Y'
4314: AND NOT EXISTS (SELECT NULL FROM EGO_ITM_USR_ATTR_INTRFC intf
4315: WHERE intf.DATA_SET_ID = src.DATA_SET_ID
4316: AND intf.PROCESS_STATUS = #' || p_dest_process_status || q'#
4317: AND intf.INVENTORY_ITEM_ID = src.INVENTORY_ITEM_ID
4318: AND intf.ORGANIZATION_ID = src.ORGANIZATION_ID
4340: '|| l_attr_num_where_sql || ' IS NOT NULL
4341: )';
4342:
4343:
4344: l_dynamic_sql := 'INSERT INTO EGO_ITM_USR_ATTR_INTRFC ' || l_insert_cols || l_select_sql || l_where_clause;
4345: Debug_Conc_Log('Copy_data_to_Intf: l_dynamic_sql='||l_dynamic_sql);
4346: EXECUTE IMMEDIATE l_dynamic_sql;
4347: Debug_Conc_Log('Copy_data_to_Intf: Inserted '||SQL%ROWCOUNT||' rows');
4348: END LOOP; -- attr_group_id LOOP
4353:
4354: /* Fix for bug#9678667 - Start: Commenting the below code - the below code should be done after this api call */
4355: /*
4356: IF p_dest_process_status = G_PS_IN_PROCESS THEN
4357: UPDATE ego_itm_usr_attr_intrfc
4358: SET PROG_INT_NUM1 = NULL
4359: ,PROG_INT_NUM2 = NULL
4360: ,PROG_INT_NUM3 = NULL
4361: ,PROG_INT_CHAR1 = 'N'