[Home] [Help]
143: BEGIN
144: Debug_Conc_Log('Resolve_PKs_For_Child: Starting ');
145: UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI
146: SET INVENTORY_ITEM_ID = NVL((SELECT INVENTORY_ITEM_ID
147: FROM MTL_SYSTEM_ITEMS_KFV MSIK
148: WHERE MSIK.CONCATENATED_SEGMENTS = MIRI.ITEM_NUMBER
149: AND MSIK.ORGANIZATION_ID = MIRI.ORGANIZATION_ID
150: ),
151: (SELECT INVENTORY_ITEM_ID
165: Debug_Conc_Log('Resolve_PKs_For_Child: Updated Revisions '||SQL%ROWCOUNT);
166:
167: UPDATE MTL_ITEM_CATEGORIES_INTERFACE MICI
168: SET INVENTORY_ITEM_ID = NVL((SELECT INVENTORY_ITEM_ID
169: FROM MTL_SYSTEM_ITEMS_KFV MSIK
170: WHERE MSIK.CONCATENATED_SEGMENTS = MICI.ITEM_NUMBER
171: AND MSIK.ORGANIZATION_ID = MICI.ORGANIZATION_ID
172: ),
173: (SELECT INVENTORY_ITEM_ID
198: Debug_Conc_Log('Resolve_PKs_For_Child: Updated Attribute Group ID '||SQL%ROWCOUNT);
199:
200: UPDATE EGO_ITM_USR_ATTR_INTRFC EIUAI
201: SET INVENTORY_ITEM_ID = NVL((SELECT INVENTORY_ITEM_ID
202: FROM MTL_SYSTEM_ITEMS_KFV MSIK
203: WHERE MSIK.CONCATENATED_SEGMENTS = EIUAI.ITEM_NUMBER
204: AND MSIK.ORGANIZATION_ID = EIUAI.ORGANIZATION_ID
205: ),
206: (SELECT INVENTORY_ITEM_ID
220: Debug_Conc_Log('Resolve_PKs_For_Child: Updated User Attrs '||SQL%ROWCOUNT);
221:
222: UPDATE EGO_ITEM_ASSOCIATIONS_INTF EIAI
223: SET INVENTORY_ITEM_ID = NVL((SELECT INVENTORY_ITEM_ID
224: FROM MTL_SYSTEM_ITEMS_KFV MSIK
225: WHERE MSIK.CONCATENATED_SEGMENTS = EIAI.ITEM_NUMBER
226: AND MSIK.ORGANIZATION_ID = EIAI.ORGANIZATION_ID
227: ),
228: (SELECT INVENTORY_ITEM_ID
1523: l_pdh_ss_id,
1524: -99
1525: FROM
1526: MTL_ITEM_CATEGORIES_INTERFACE mici,
1527: MTL_SYSTEM_ITEMS_KFV msik
1528: WHERE mici.SET_PROCESS_ID = p_batch_id
1529: AND msik.STYLE_ITEM_ID = mici.INVENTORY_ITEM_ID
1530: AND msik.ORGANIZATION_ID = mici.ORGANIZATION_ID
1531: AND mici.REQUEST_ID = l_request_id
4230: MSI.INVENTORY_ITEM_ID AS STYLE_ITEM_ID,
4231: c_source_system_id AS SOURCE_SYSTEM_ID,
4232: MSI.ITEM_CATALOG_GROUP_ID,
4233: NULL
4234: FROM MTL_SYSTEM_ITEMS_KFV MSI, MTL_SYSTEM_ITEMS_INTERFACE MSII, MTL_PARAMETERS MP
4235: WHERE MSII.SET_PROCESS_ID = p_batch_id
4236: AND MSII.PROCESS_FLAG = 1
4237: AND MSII.STYLE_ITEM_FLAG = 'N'
4238: AND UPPER(MSII.TRANSACTION_TYPE) = 'CREATE'
4296: 'CREATE',
4297: MSI.ITEM_CATALOG_GROUP_ID,
4298: NULL,
4299: -99
4300: FROM MTL_SYSTEM_ITEMS_KFV MSI, MTL_PARAMETERS MP
4301: WHERE MSI.STYLE_ITEM_FLAG = 'N'
4302: AND MSI.STYLE_ITEM_ID = i.INVENTORY_ITEM_ID
4303: AND MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID
4304: AND MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
4459: (msii.SOURCE_SYSTEM_ID = miri.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = miri.SOURCE_SYSTEM_REFERENCE)
4460: )
4461: AND msii.ORGANIZATION_ID = miri.ORGANIZATION_ID
4462: UNION ALL
4463: SELECT NULL FROM MTL_SYSTEM_ITEMS_KFV msik
4464: WHERE (msik.INVENTORY_ITEM_ID = miri.INVENTORY_ITEM_ID OR msik.CONCATENATED_SEGMENTS = miri.ITEM_NUMBER)
4465: AND msik.ORGANIZATION_ID = miri.ORGANIZATION_ID
4466: AND msik.STYLE_ITEM_FLAG = 'N'
4467: );
4483: (msii.SOURCE_SYSTEM_ID = mici.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = mici.SOURCE_SYSTEM_REFERENCE)
4484: )
4485: AND msii.ORGANIZATION_ID = mici.ORGANIZATION_ID
4486: UNION ALL
4487: SELECT NULL FROM MTL_SYSTEM_ITEMS_KFV msik
4488: WHERE (msik.INVENTORY_ITEM_ID = mici.INVENTORY_ITEM_ID OR msik.CONCATENATED_SEGMENTS = mici.ITEM_NUMBER)
4489: AND msik.ORGANIZATION_ID = mici.ORGANIZATION_ID
4490: AND msik.STYLE_ITEM_FLAG = 'N'
4491: );
4510: (msii.SOURCE_SYSTEM_ID = eiuai.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = eiuai.SOURCE_SYSTEM_REFERENCE)
4511: )
4512: AND msii.ORGANIZATION_ID = eiuai.ORGANIZATION_ID
4513: UNION ALL
4514: SELECT NULL FROM MTL_SYSTEM_ITEMS_KFV msik
4515: WHERE (msik.INVENTORY_ITEM_ID = eiuai.INVENTORY_ITEM_ID OR msik.CONCATENATED_SEGMENTS = eiuai.ITEM_NUMBER)
4516: AND msik.ORGANIZATION_ID = eiuai.ORGANIZATION_ID
4517: AND msik.STYLE_ITEM_FLAG = 'N'
4518: );
4534: (msii.SOURCE_SYSTEM_ID = eai.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = eai.SOURCE_SYSTEM_REFERENCE)
4535: )
4536: AND msii.ORGANIZATION_ID = eai.ORGANIZATION_ID
4537: UNION ALL
4538: SELECT NULL FROM MTL_SYSTEM_ITEMS_KFV msik
4539: WHERE (msik.INVENTORY_ITEM_ID = eai.INVENTORY_ITEM_ID OR msik.CONCATENATED_SEGMENTS = eai.ITEM_NUMBER)
4540: AND msik.ORGANIZATION_ID = eai.ORGANIZATION_ID
4541: AND msik.STYLE_ITEM_FLAG = 'N'
4542: );
4558: (msii.SOURCE_SYSTEM_ID = eiai.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = eiai.SOURCE_SYSTEM_REFERENCE)
4559: )
4560: AND msii.ORGANIZATION_ID = eiai.ORGANIZATION_ID
4561: UNION ALL
4562: SELECT NULL FROM MTL_SYSTEM_ITEMS_KFV msik
4563: WHERE (msik.INVENTORY_ITEM_ID = eiai.INVENTORY_ITEM_ID OR msik.CONCATENATED_SEGMENTS = eiai.ITEM_NUMBER)
4564: AND msik.ORGANIZATION_ID = eiai.ORGANIZATION_ID
4565: AND msik.STYLE_ITEM_FLAG = 'N'
4566: );
4578: (msii.SOURCE_SYSTEM_ID = eipi.SOURCE_SYSTEM_ID AND msii.SOURCE_SYSTEM_REFERENCE = eipi.SOURCE_SYSTEM_REFERENCE)
4579: )
4580: AND msii.ORGANIZATION_ID = eipi.ORGANIZATION_ID
4581: UNION ALL
4582: SELECT NULL FROM MTL_SYSTEM_ITEMS_KFV msik
4583: WHERE (msik.INVENTORY_ITEM_ID = eipi.INVENTORY_ITEM_ID OR msik.CONCATENATED_SEGMENTS = eipi.ITEM_NUMBER)
4584: AND msik.ORGANIZATION_ID = eipi.ORGANIZATION_ID
4585: AND msik.STYLE_ITEM_FLAG = 'N'
4586: );
4611:
4612: IF SQL%ROWCOUNT > 0 THEN
4613: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
4614: SET STYLE_ITEM_ID = (SELECT MSIK.INVENTORY_ITEM_ID
4615: FROM MTL_SYSTEM_ITEMS_KFV MSIK
4616: WHERE MSIK.CONCATENATED_SEGMENTS = MSII.STYLE_ITEM_NUMBER
4617: AND MSIK.ORGANIZATION_ID = MSII.ORGANIZATION_ID
4618: )
4619: WHERE SET_PROCESS_ID = p_batch_id
4889: TO_CHAR(intf.GRANTEE_PARTY_ID)
4890: ) GRANTEE_KEY,
4891: intf.END_DATE
4892: FROM
4893: MTL_SYSTEM_ITEMS_KFV msik,
4894: EGO_ITEM_PEOPLE_INTF intf,
4895: MTL_PARAMETERS mp,
4896: FND_MENUS menus
4897: WHERE intf.DATA_SET_ID = p_batch_id