[Home] [Help]
124: );
125:
126: IF (l_request_id = 0 OR l_request_id IS NULL) then
127: -- failed to launch the process
128: UPDATE mtl_eam_asset_num_interface meani
129: SET meani.error_code = 9999,
130: meani.process_flag = 'E',
131: meani.error_message = 'Failed to submit worker '||p_group_id
132: WHERE meani.process_flag = 'P'
188: SELECT meani.current_organization_id,
189: meani.inventory_item_id,
190: meani.serial_number,
191: count(*) as total
192: FROM MTL_EAM_ASSET_NUM_INTERFACE meani
193: WHERE meani.batch_id = p_batch_id
194: AND meani.interface_group_id IS NULL
195: AND meani.process_flag = 'P'
196: AND meani.error_code IS NULL
202:
203: -- Cursor for picking out invalid Scope in meani
204: CURSOR invalid_scope_asset_cur IS
205: SELECT meani.interface_header_id
206: FROM MTL_EAM_ASSET_NUM_INTERFACE meani
207: WHERE meani.batch_id = p_batch_id
208: AND meani.import_scope NOT IN (0,1,2)
209: AND meani.process_flag = 'P'
210: FOR UPDATE;
213:
214: -- Cursor for picking out invalid Mode in meani
215: CURSOR invalid_mode_asset_cur IS
216: SELECT meani.interface_header_id
217: FROM MTL_EAM_ASSET_NUM_INTERFACE meani
218: WHERE meani.batch_id = p_batch_id
219: AND meani.import_mode NOT IN (0,1)
220: AND meani.process_flag = 'P'
221: FOR UPDATE;
223:
224: -- Cursor for picking out NULL organization in meani
225: CURSOR null_org_asset_cur IS
226: SELECT meani.interface_header_id
227: FROM MTL_EAM_ASSET_NUM_INTERFACE meani
228: WHERE batch_id = p_batch_id
229: AND (current_organization_id is null
230: OR organization_code is null)
231: AND process_flag = 'P'
261:
262: -- Use Cursor to pick out rows with invalid Scope to synchronize the conditions
263: -- for updating the row in meani and the corresponding rows in meavi
264: /* ==
265: UPDATE mtl_eam_asset_num_interface meani
266: SET meani.process_flag = 'E',
267: meani.error_code = 9999,
268: meani.error_message = 'Incorrect Scope Value'
269: WHERE meani.batch_id = p_batch_id
276: IF invalid_scope_asset_cur%NOTFOUND
277: THEN
278: EXIT;
279: ELSE
280: UPDATE mtl_eam_asset_num_interface meani
281: SET meani.process_flag = 'E',
282: meani.error_code = 9999,
283: meani.error_message = 'Incorrect Scope Value'
284: WHERE CURRENT OF invalid_scope_asset_cur;
287: -- Flag corresponding rows in meavi as Error as well.
288: UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
289: SET meavi.error_number = 9999,
290: meavi.process_status = 'E',
291: meavi.error_message = 'Corresponding row in MTL_EAM_ASSET_NUM_INTERFACE has invalid Scope value'
292: WHERE meavi.process_status = 'P'
293: AND meavi.interface_header_id = invalid_scope_asset_rec.interface_header_id;
294: END IF;
295: END LOOP;
307:
308: -- Use Cursor to pick out rows with invalid Mode to synchronize the conditions
309: -- for updating the row in meani and the corresponding rows in meavi
310: /* ==
311: UPDATE mtl_eam_asset_num_interface meani
312: SET meani.process_flag = 'E',
313: meani.error_code = 9999,
314: meani.error_message = 'Incorrect Mode Value'
315: WHERE meani.batch_id = p_batch_id
323: IF invalid_mode_asset_cur%NOTFOUND
324: THEN
325: EXIT;
326: ELSE
327: UPDATE mtl_eam_asset_num_interface meani
328: SET meani.process_flag = 'E',
329: meani.error_code = 9999,
330: meani.error_message = 'Incorrect Mode Value'
331: WHERE CURRENT OF invalid_mode_asset_cur;
333: -- Flag corresponding rows in meavi as Error as well.
334: UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
335: SET meavi.error_number = 9999,
336: meavi.process_status = 'E',
337: meavi.error_message = 'Corresponding row in MTL_EAM_ASSET_NUM_INTERFACE has invalid Mode value'
338: WHERE meavi.process_status = 'P'
339: AND meavi.interface_header_id = invalid_mode_asset_rec.interface_header_id;
340: END IF;
341: END LOOP;
349: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_module,'Validating Organization Code.');
350: END IF;
351:
352:
353: UPDATE mtl_eam_asset_num_interface meani
354: SET organization_code= (select organization_code from mtl_parameters
355: where organization_id = meani.current_organization_id
356: and maint_organization_id is NOT NULL)
357: WHERE batch_id = p_batch_id
357: WHERE batch_id = p_batch_id
358: AND current_organization_id is not null
359: AND process_flag = 'P';
360:
361: UPDATE mtl_eam_asset_num_interface meani
362: SET current_organization_id= (select organization_id from mtl_parameters
363: where organization_code = meani.organization_code
364: and maint_organization_id is NOT NULL)
365: WHERE batch_id = p_batch_id
369:
370: -- Use Cursor to pick out rows with NULL org to synchronize the conditions
371: -- for updating the row in meani and the corresponding rows in meavi
372: /* ==
373: UPDATE mtl_eam_asset_num_interface
374: SET process_flag = 'E' ,
375: error_code = 9999,
376: error_message = 'Invalid Organization. Check that it is NOT NULL, EXISTS and is EAM ENABLED'
377: WHERE batch_id = p_batch_id
387: IF null_org_asset_cur%NOTFOUND
388: THEN
389: EXIT;
390: ELSE
391: UPDATE mtl_eam_asset_num_interface
392: SET process_flag = 'E' ,
393: error_code = 9999,
394: error_message = 'Invalid Organization. Check that it is NOT NULL, EXISTS and is EAM ENABLED'
395: WHERE CURRENT OF null_org_asset_cur;
397: -- Flag corresponding rows in meavi as Error as well.
398: UPDATE MTL_EAM_ATTR_VAL_INTERFACE meavi
399: SET meavi.error_number = 9999,
400: meavi.process_status = 'E',
401: meavi.error_message = 'Corresponding row in MTL_EAM_ASSET_NUM_INTERFACE has invalid organzation'
402: WHERE meavi.process_status = 'P'
403: AND meavi.interface_header_id = null_org_asset_rec.interface_header_id;
404: END IF;
405: END LOOP;
417: FOR asset IN asset_rows_cur
418: LOOP
419: --get a group id for worker
420: if (l_counter = 0) then
421: SELECT MTL_EAM_ASSET_NUM_INTERFACE_S.nextval
422: INTO l_group_id
423: FROM dual;
424: end if;
425: --have to update after each record ...
422: INTO l_group_id
423: FROM dual;
424: end if;
425: --have to update after each record ...
426: UPDATE mtl_eam_asset_num_interface meani
427: SET meani.interface_group_id = l_group_id,
428: meani.process_flag = 'R'
429: WHERE meani.process_flag = 'P'
430: AND meani.current_organization_id = asset.current_organization_id