[Home] [Help]
90: );
91:
92: IF (l_request_id = 0 OR l_request_id IS NULL) then
93: -- failed to launch the process
94: UPDATE mtl_object_genealogy_interface mogi
95: SET mogi.error_code = 9999,
96: mogi.process_status = 'E',
97: mogi.error_message = 'Failed to submit worker for Group ID = '||p_group_id
98: WHERE mogi.process_status = 'P'
141: mogi.organization_id,
142: mogi.inventory_item_id,
143: mogi.serial_number,
144: count(*) as total
145: FROM mtl_object_genealogy_interface mogi
146: WHERE mogi.batch_id = p_batch_id
147: AND mogi.group_id IS NULL
148: AND mogi.process_status = 'P'
149: AND mogi.error_code IS NULL
174: IF G_DEBUG = 'Y' THEN
175: fnd_file.put_line(FND_FILE.LOG,'Validating Mode Values.');
176: END IF;
177:
178: UPDATE mtl_object_genealogy_interface mogi
179: SET mogi.process_status = 'E',
180: mogi.error_code = 9999,
181: mogi.error_message = 'Incorrect Mode Value'
182: WHERE mogi.batch_id = p_batch_id
188: IF G_DEBUG = 'Y' THEN
189: fnd_file.put_line(FND_FILE.LOG, 'Validating Genealogy Origin');
190: END IF;
191:
192: UPDATE MTL_OBJECT_GENEALOGY_INTERFACE
193: SET process_status = 'E',
194: error_code = 9999,
195: error_message = 'Incorrent Genealogy Origin'
196: WHERE batch_id = p_batch_id
201: IF G_DEBUG = 'Y' THEN
202: fnd_file.put_line(FND_FILE.LOG,'Validating Object Type');
203: END IF;
204:
205: UPDATE MTL_OBJECT_GENEALOGY_INTERFACE
206: SET process_status = 'E',
207: error_code = 9999,
208: error_message = 'Incorrent Object Type'
209: WHERE batch_id = p_batch_id
217: IF G_DEBUG = 'Y' THEN
218: fnd_file.put_line(FND_FILE.LOG,'Validating Genealogy Type');
219: END IF;
220:
221: UPDATE MTL_OBJECT_GENEALOGY_INTERFACE
222: SET process_status = 'E',
223: error_code = 9999,
224: error_message = 'Incorrent Genealogy Type'
225: WHERE batch_id = p_batch_id
230:
231: -- Validate Organization Code:
232: -- Organization and Parent Organization have to be the same.
233:
234: UPDATE mtl_object_genealogy_interface mogi
235: SET process_status = 'E' , error_code = 9999, error_message = 'Invalid Organization. Organization and Parent Organization have to be the same.'
236: WHERE batch_id = p_batch_id
237: AND ( select mp.maint_organization_id from mtl_parameters mp
238: where mp.organization_code = mogi.organization_code )
251: fnd_file.new_line(FND_FILE.LOG,1);
252: END IF;
253:
254:
255: UPDATE mtl_object_genealogy_interface mogi
256: SET organization_code= (select organization_code from mtl_parameters
257: where organization_id = mogi.organization_id
258: and maint_organization_id is not null)
259: WHERE batch_id = p_batch_id
259: WHERE batch_id = p_batch_id
260: AND organization_id is not null
261: AND process_status = 'P';
262:
263: UPDATE mtl_object_genealogy_interface mogi
264: SET organization_id= (select organization_id from mtl_parameters
265: where organization_code = mogi.organization_code
266: and maint_organization_id is not null)
267: WHERE batch_id = p_batch_id
268: AND organization_id is null
269: AND organization_code is not null
270: AND process_status = 'P';
271:
272: UPDATE mtl_object_genealogy_interface mogi
273: SET process_status = 'E' , error_code = 9999, error_message = 'Invalid Organization. Check that it is NOT NULL, EXISTS and is EAM ENABLED'
274: WHERE batch_id = p_batch_id
275: AND (organization_id is null
276: OR organization_code is null)
285: fnd_file.put_line(FND_FILE.LOG,'Validating Parent Organization Code.');
286: fnd_file.new_line(FND_FILE.LOG,1);
287: END IF;
288:
289: UPDATE mtl_object_genealogy_interface mogi
290: SET parent_organization_code= (select organization_code from mtl_parameters
291: where organization_id = mogi.parent_organization_id
292: and maint_organization_id is not null)
293: WHERE batch_id = p_batch_id
293: WHERE batch_id = p_batch_id
294: AND parent_organization_id is not null
295: AND process_status = 'P';
296:
297: UPDATE mtl_object_genealogy_interface mogi
298: SET parent_organization_id= (select organization_id from mtl_parameters
299: where organization_code = mogi.parent_organization_code
300: and maint_organization_id is not null)
301: WHERE batch_id = p_batch_id
302: AND parent_organization_id is null
303: AND parent_organization_code is not null
304: AND process_status = 'P';
305:
306: UPDATE mtl_object_genealogy_interface mogi
307: SET process_status = 'E' , error_code = 9999, error_message = 'Invalid Parent Organization. Check that it is NOT NULL, EXISTS and is EAM ENABLED'
308: WHERE batch_id = p_batch_id
309: AND (parent_organization_id is null
310: OR parent_organization_code is null)
320: fnd_file.put_line(FND_FILE.LOG,'Validating Parent Organization Id.');
321: fnd_file.new_line(FND_FILE.LOG,1);
322: END IF;
323:
324: UPDATE mtl_object_genealogy_interface mogi
325: SET object_id = (select gen_object_id from mtl_serial_numbers
326: where current_organization_id = mogi.organization_id
327: and inventory_item_id = mogi.inventory_item_id
328: and serial_number = mogi.serial_number)
328: and serial_number = mogi.serial_number)
329: WHERE batch_id = p_batch_id
330: AND process_status = 'P';
331:
332: UPDATE mtl_object_genealogy_interface mogi
333: SET parent_object_id = (select gen_object_id from mtl_serial_numbers
334: where current_organization_id = mogi.parent_organization_id
335: and inventory_item_id = mogi.parent_inventory_item_id
336: and serial_number = mogi.parent_serial_number)
336: and serial_number = mogi.parent_serial_number)
337: WHERE batch_id = p_batch_id
338: AND process_status = 'P';
339:
340: UPDATE mtl_object_genealogy_interface mogi
341: SET process_status = 'E' , error_code = 9999, error_message = 'Invalid Serial Number. Check that it exists in the organization.'
342: WHERE batch_id = p_batch_id
343: AND (object_id is null
344: OR parent_object_id is null)
360: FROM dual;
361: end if;
362:
363: --have to update after each record ...
364: UPDATE mtl_object_genealogy_interface mogi
365: SET mogi.group_id = l_group_id,
366: mogi.process_status = 'R'
367: WHERE mogi.process_status = 'P'
368: AND mogi.organization_id = genealogy.organization_id