[Home] [Help]
166: BEGIN
167: Debug_Conc_Log('Resolve_PKs_For_Child: Starting ');
168: UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI
169: SET INVENTORY_ITEM_ID = NVL((SELECT INVENTORY_ITEM_ID
170: FROM MTL_SYSTEM_ITEMS_KFV MSIK
171: WHERE MSIK.CONCATENATED_SEGMENTS = MIRI.ITEM_NUMBER
172: AND MSIK.ORGANIZATION_ID = MIRI.ORGANIZATION_ID
173: ),
174: (SELECT INVENTORY_ITEM_ID
188: Debug_Conc_Log('Resolve_PKs_For_Child: Updated Revisions '||SQL%ROWCOUNT);
189:
190: UPDATE MTL_ITEM_CATEGORIES_INTERFACE MICI
191: SET INVENTORY_ITEM_ID = NVL((SELECT INVENTORY_ITEM_ID
192: FROM MTL_SYSTEM_ITEMS_KFV MSIK
193: WHERE MSIK.CONCATENATED_SEGMENTS = MICI.ITEM_NUMBER
194: AND MSIK.ORGANIZATION_ID = MICI.ORGANIZATION_ID
195: ),
196: (SELECT INVENTORY_ITEM_ID
227: -- Bug 13434831 : End (Performance Change)
228:
229: UPDATE EGO_ITM_USR_ATTR_INTRFC EIUAI
230: SET INVENTORY_ITEM_ID = NVL((SELECT INVENTORY_ITEM_ID
231: FROM MTL_SYSTEM_ITEMS_KFV MSIK
232: WHERE MSIK.CONCATENATED_SEGMENTS = EIUAI.ITEM_NUMBER
233: AND MSIK.ORGANIZATION_ID = EIUAI.ORGANIZATION_ID
234: ),
235: --Bug 9660659 - Removing the OR in this query, making two queries - Bug 9660659
270: Debug_Conc_Log('Resolve_PKs_For_Child: Updated User Attrs '||SQL%ROWCOUNT);
271:
272: UPDATE EGO_ITEM_ASSOCIATIONS_INTF EIAI
273: SET INVENTORY_ITEM_ID = NVL((SELECT INVENTORY_ITEM_ID
274: FROM MTL_SYSTEM_ITEMS_KFV MSIK
275: WHERE MSIK.CONCATENATED_SEGMENTS = EIAI.ITEM_NUMBER
276: AND MSIK.ORGANIZATION_ID = EIAI.ORGANIZATION_ID
277: ),
278: (SELECT INVENTORY_ITEM_ID
1693: l_pdh_ss_id,
1694: -99
1695: FROM
1696: MTL_ITEM_CATEGORIES_INTERFACE mici,
1697: MTL_SYSTEM_ITEMS_KFV msik
1698: WHERE mici.SET_PROCESS_ID = p_batch_id
1699: AND msik.STYLE_ITEM_ID = mici.INVENTORY_ITEM_ID
1700: AND msik.ORGANIZATION_ID = mici.ORGANIZATION_ID
1701: AND mici.REQUEST_ID = l_request_id
4927: c_source_system_id AS SOURCE_SYSTEM_ID,
4928: MSI.ITEM_CATALOG_GROUP_ID,
4929: NULL,
4930: NULL
4931: FROM MTL_SYSTEM_ITEMS_KFV MSI, MTL_SYSTEM_ITEMS_INTERFACE MSII, MTL_PARAMETERS MP
4932: WHERE MSII.SET_PROCESS_ID = p_batch_id
4933: AND MSII.PROCESS_FLAG = 1
4934: AND MSII.STYLE_ITEM_FLAG = 'N'
4935: AND UPPER(MSII.TRANSACTION_TYPE) = 'CREATE'
4993: 'CREATE',
4994: MSI.ITEM_CATALOG_GROUP_ID,
4995: NULL,
4996: -99
4997: FROM MTL_SYSTEM_ITEMS_KFV MSI, MTL_PARAMETERS MP
4998: WHERE MSI.STYLE_ITEM_FLAG = 'N'
4999: AND MSI.STYLE_ITEM_ID = i.INVENTORY_ITEM_ID
5000: AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
5001: AND MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
5158: (msii.SOURCE_SYSTEM_ID = miri.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = miri.SOURCE_SYSTEM_REFERENCE)
5159: )
5160: AND msii.ORGANIZATION_ID = miri.ORGANIZATION_ID
5161: UNION ALL
5162: SELECT NULL FROM MTL_SYSTEM_ITEMS_KFV msik
5163: WHERE (msik.INVENTORY_ITEM_ID = miri.INVENTORY_ITEM_ID OR msik.CONCATENATED_SEGMENTS = miri.ITEM_NUMBER)
5164: AND msik.ORGANIZATION_ID = miri.ORGANIZATION_ID
5165: AND msik.STYLE_ITEM_FLAG = 'N'
5166: );
5182: (msii.SOURCE_SYSTEM_ID = mici.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = mici.SOURCE_SYSTEM_REFERENCE)
5183: )
5184: AND msii.ORGANIZATION_ID = mici.ORGANIZATION_ID
5185: UNION ALL
5186: SELECT NULL FROM MTL_SYSTEM_ITEMS_KFV msik
5187: WHERE (msik.INVENTORY_ITEM_ID = mici.INVENTORY_ITEM_ID OR msik.CONCATENATED_SEGMENTS = mici.ITEM_NUMBER)
5188: AND msik.ORGANIZATION_ID = mici.ORGANIZATION_ID
5189: AND msik.STYLE_ITEM_FLAG = 'N'
5190: );
5210: (msii.SOURCE_SYSTEM_ID = eiuai.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = eiuai.SOURCE_SYSTEM_REFERENCE)
5211: )
5212: AND msii.ORGANIZATION_ID = eiuai.ORGANIZATION_ID
5213: UNION ALL
5214: SELECT NULL FROM MTL_SYSTEM_ITEMS_KFV msik
5215: WHERE (msik.INVENTORY_ITEM_ID = eiuai.INVENTORY_ITEM_ID OR msik.CONCATENATED_SEGMENTS = eiuai.ITEM_NUMBER)
5216: AND msik.ORGANIZATION_ID = eiuai.ORGANIZATION_ID
5217: AND msik.STYLE_ITEM_FLAG = 'N'
5218: );
5234: (msii.SOURCE_SYSTEM_ID = eai.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = eai.SOURCE_SYSTEM_REFERENCE)
5235: )
5236: AND msii.ORGANIZATION_ID = eai.ORGANIZATION_ID
5237: UNION ALL
5238: SELECT NULL FROM MTL_SYSTEM_ITEMS_KFV msik
5239: WHERE (msik.INVENTORY_ITEM_ID = eai.INVENTORY_ITEM_ID OR msik.CONCATENATED_SEGMENTS = eai.ITEM_NUMBER)
5240: AND msik.ORGANIZATION_ID = eai.ORGANIZATION_ID
5241: AND msik.STYLE_ITEM_FLAG = 'N'
5242: );
5258: (msii.SOURCE_SYSTEM_ID = eiai.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = eiai.SOURCE_SYSTEM_REFERENCE)
5259: )
5260: AND msii.ORGANIZATION_ID = eiai.ORGANIZATION_ID
5261: UNION ALL
5262: SELECT NULL FROM MTL_SYSTEM_ITEMS_KFV msik
5263: WHERE (msik.INVENTORY_ITEM_ID = eiai.INVENTORY_ITEM_ID OR msik.CONCATENATED_SEGMENTS = eiai.ITEM_NUMBER)
5264: AND msik.ORGANIZATION_ID = eiai.ORGANIZATION_ID
5265: AND msik.STYLE_ITEM_FLAG = 'N'
5266: );
5278: (msii.SOURCE_SYSTEM_ID = eipi.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = eipi.SOURCE_SYSTEM_REFERENCE)
5279: )
5280: AND msii.ORGANIZATION_ID = eipi.ORGANIZATION_ID
5281: UNION ALL
5282: SELECT NULL FROM MTL_SYSTEM_ITEMS_KFV msik
5283: WHERE (msik.INVENTORY_ITEM_ID = eipi.INVENTORY_ITEM_ID OR msik.CONCATENATED_SEGMENTS = eipi.ITEM_NUMBER)
5284: AND msik.ORGANIZATION_ID = eipi.ORGANIZATION_ID
5285: AND msik.STYLE_ITEM_FLAG = 'N'
5286: );
5317:
5318: IF SQL%ROWCOUNT > 0 THEN
5319: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
5320: SET STYLE_ITEM_ID = (SELECT MSIK.INVENTORY_ITEM_ID
5321: FROM MTL_SYSTEM_ITEMS_KFV MSIK
5322: WHERE MSIK.CONCATENATED_SEGMENTS = MSII.STYLE_ITEM_NUMBER
5323: AND MSIK.ORGANIZATION_ID = MSII.ORGANIZATION_ID
5324: )
5325: WHERE SET_PROCESS_ID = p_batch_id
5639: TO_CHAR(intf.GRANTEE_PARTY_ID)
5640: ) GRANTEE_KEY,
5641: intf.END_DATE
5642: FROM
5643: MTL_SYSTEM_ITEMS_KFV msik,
5644: EGO_ITEM_PEOPLE_INTF intf,
5645: MTL_PARAMETERS mp,
5646: FND_MENUS menus
5647: WHERE intf.DATA_SET_ID = p_batch_id