1 PACKAGE BODY EAM_GENEALOGY_IMPORT_PVT as
2 /* $Header: EAMVGEIB.pls 120.2 2006/07/11 13:31:48 kmurthy noship $*/
3
4 -- Start of comments
5 -- API name : Load_Genealogy
6 -- Type : Private
7 -- Function :
8 -- Pre-reqs : None.
9 -- Parameters :
10 -- IN p_batch_id IN NUMBER Required,
11 -- p_purge_option IN VARCHAR2 Optional Default = 'N'
12 -- OUT ERRBUF OUT VARCHAR2,
13 -- RETCODE OUT VARCHAR2
14 --
15 -- Version Initial version 1.0 Kenichi Nagumo
16 --
17 -- Notes : This public API imports asset genealogy into
18 -- MTL_OBJECT_GENEALOGY
19 --
20 -- End of comments
21
22 -- global variable to turn on/off debug logging.
23 G_DEBUG VARCHAR2(1) := NVL(fnd_profile.value('EAM_DEBUG'), 'N');
24
25 PROCEDURE Load_Genealogy
26 (ERRBUF OUT NOCOPY VARCHAR2,
27 RETCODE OUT NOCOPY VARCHAR2,
28 p_batch_id IN NUMBER,
29 p_purge_option IN VARCHAR2 := 'N'
30 ) IS
31
32 l_retcode Number;
33 CONC_STATUS BOOLEAN;
34
35 BEGIN
36
37 l_retcode := import_genealogy(p_batch_id, p_purge_option);
38
39 if l_retcode = 1 then
40 IF G_DEBUG = 'Y' THEN
41 fnd_file.put_line(FND_FILE.LOG, 'Completed Successfully.');
42 fnd_file.put_line(FND_FILE.LOG, Current_Error_Code);
43 END IF;
44 RETCODE := 'Success';
45 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',Current_Error_Code);
46 elsif l_retcode = 3 then
47 IF G_DEBUG = 'Y' THEN
48 fnd_file.put_line(FND_FILE.LOG, 'Completed with Warning.');
49 fnd_file.put_line(FND_FILE.LOG, Current_Error_Code);
50 END IF;
51 RETCODE := 'Warning';
52 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',Current_Error_Code);
53 else
54 IF G_DEBUG = 'Y' THEN
55 fnd_file.put_line(FND_FILE.LOG, 'Completed with Error.');
56 fnd_file.put_line(FND_FILE.LOG, Current_Error_Code);
57 END IF;
58 RETCODE := 'Error';
59 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
60 end if;
61
62 END Load_Genealogy;
63
64
65 PROCEDURE Launch_Worker
66 (
67 p_group_id NUMBER,
68 p_batch_id NUMBER,
69 p_purge_option VARCHAR2,
70 p_count NUMBER
71 ) IS
72 l_request_id NUMBER := 0;
73 BEGIN
74
75 IF G_DEBUG = 'Y' THEN
76 fnd_file.put_line(FND_FILE.LOG, 'Submitting Import Worker '||p_group_id
77 || ' to process ' || p_count || ' genealogy');
78 END IF;
79
80 COMMIT;
81
82 l_request_id := FND_REQUEST.submit_request(
83 'EAM',
84 'EAMGEIMW',
85 NULL,
86 NULL,
87 FALSE,
88 p_group_id,
89 p_purge_option
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'
99 AND mogi.batch_id = p_batch_id
100 AND mogi.group_id = p_group_id;
101 COMMIT;
102
103 IF G_DEBUG = 'Y' THEN
104 fnd_file.put_line(FND_FILE.LOG, 'Failed to Launch the Process.');
105 fnd_file.new_line(FND_FILE.LOG,1);
106 END IF;
107
108 RAISE fnd_api.g_exc_error;
109 ELSE
110 IF G_DEBUG = 'Y' THEN
111 fnd_file.put_line(FND_FILE.LOG, 'Import Worker '||p_group_id||' successfully submitted.');
112 fnd_file.new_line(FND_FILE.LOG,1);
113 END IF;
114 END IF;
115 END Launch_Worker;
116
117
118
119
120 FUNCTION Import_Genealogy
121 (
122 p_batch_id IN NUMBER,
123 p_purge_option IN VARCHAR2 := 'N'
124 ) RETURN Number IS
125
126 l_max_rows_to_process NUMBER := 500;
127 l_counter NUMBER := 0;
128 l_num_genealogy NUMBER := 0;
129 l_num_workers NUMBER := 0;
130 l_num_genealogy_to_workers NUMBER := 0;
131 error_number NUMBER := NULL;
132 error_message VARCHAR2(2000) := NULL;
133 error_counter NUMBER := 0;
134 curr_error VARCHAR2(9) := 'APP-00000';
135
136 l_group_id NUMBER := 0;
137 l_success NUMBER := 1;
138
139 CURSOR genealogy_rows_cur IS
140 SELECT
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
150 AND mogi.error_message IS NULL
151 GROUP BY
152 mogi.organization_id,
153 mogi.inventory_item_id,
154 mogi.serial_number
155 ORDER BY
156 mogi.organization_id,
157 mogi.inventory_item_id,
158 mogi.serial_number;
159
160 BEGIN
161
162 IF G_DEBUG = 'Y' THEN
163 fnd_file.put_line(FND_FILE.LOG,'Batch Id = '||p_batch_id);
164 fnd_file.put_line(FND_FILE.LOG,'Purge Option = '||p_purge_option);
165 fnd_file.put_line(FND_FILE.LOG,'Max Rows to process = '||l_max_rows_to_process);
166 fnd_file.new_line(FND_FILE.LOG, 1);
167 END IF;
168
169
170 -- Validate Mode Values:
171 -- 0: Create
172 -- 1: Update
173
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
183 AND mogi.import_mode NOT IN (0,1)
184 AND mogi.process_status = 'P';
185
186
187 -- validate that the genealogy origin of all entries is 3
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
197 and genealogy_origin <> 3
198 and process_status = 'P';
199
200 -- validate that the object type of all entries is 2
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
210 and (object_type <> 2
211 or parent_object_type <> 2)
212 and process_status = 'P';
213
214 COMMIT;
215
216 -- validate that the genealogy type of all entries is 5
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
226 and genealogy_type <> 5
227 and process_status = 'P';
228
229 COMMIT;
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 )
239 <>
240 ( select mp.maint_organization_id from mtl_parameters mp
241 where mp.organization_code = mogi.parent_organization_code )
242 AND process_status = 'P';
243
244 COMMIT;
245
246 -- Validate Organization Code:
247 -- If both organization_code and organization_id is provided, organization_id will be used.
248
249 IF G_DEBUG = 'Y' THEN
250 fnd_file.put_line(FND_FILE.LOG,'Validating 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
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)
277 AND process_status = 'P';
278
279 COMMIT;
280
281 -- Validate Parent Organization Code:
282 -- If both organization_code and organization_id is provided, organization_id will be used.
283
284 IF G_DEBUG = 'Y' THEN
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
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)
311 AND process_status = 'P';
312
313 COMMIT;
314
315
316 -- Validate Parent Organization Code:
317 -- If both organization_code and organization_id is provided, organization_id will be used.
318
319 IF G_DEBUG = 'Y' THEN
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)
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)
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)
345 AND process_status = 'P';
346
347
348 IF G_DEBUG = 'Y' THEN
349 fnd_file.put_line(FND_FILE.LOG, 'Start record pre-processing. Time now is ' ||
353 FOR genealogy IN genealogy_rows_cur
350 to_char(sysdate, 'Month DD, YYYY HH24:MI:SS'));
351 END IF;
352
354 LOOP
355
356 --get a group id for worker
357 if (l_counter = 0) then
358 SELECT MTL_OBJECT_GEN_INTERFACE_S.nextval
359 INTO l_group_id
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
369 AND mogi.inventory_item_id = genealogy.inventory_item_id
370 AND mogi.serial_number = genealogy.serial_number
371 AND mogi.batch_id = p_batch_id
372 AND mogi.group_id IS NULL;
373
374 l_counter := l_counter + genealogy.total;
375 l_num_genealogy := l_num_genealogy + genealogy.total;
376
377 IF (l_counter > l_max_rows_to_process) THEN
378 Launch_Worker(l_group_id, p_batch_id, p_purge_option, l_counter);
379 l_num_genealogy_to_workers := l_num_genealogy_to_workers + l_counter;
380 l_counter := 0;
381 l_num_workers := l_num_workers + 1;
382 END IF;
383
384 END LOOP;
385
386 if (l_num_genealogy > l_num_genealogy_to_workers) then -- we still have more to process
387 Launch_Worker(l_group_id, p_batch_id, p_purge_option, l_counter);
388 l_num_genealogy_to_workers := l_num_genealogy_to_workers + l_counter;
389 l_num_workers := l_num_workers + 1;
390 end if;
391
392 IF G_DEBUG = 'Y' THEN
393 fnd_file.new_line(FND_FILE.LOG,1);
394
395 fnd_file.put_line(FND_FILE.LOG, 'Stop record pre-processing. Time now is ' ||
396 to_char(sysdate, 'Month DD, YYYY HH24:MI:SS'));
397 fnd_file.new_line(FND_FILE.LOG,1);
398 END IF;
399
400 IF G_DEBUG = 'Y' THEN
401 fnd_file.put_line(FND_FILE.LOG, l_num_workers ||' Import Workers are processing '
402 ||l_num_genealogy_to_workers || ' Genealogy');
403 END IF;
404
405 if not (l_num_genealogy_to_workers = l_num_genealogy) then
406 IF G_DEBUG = 'Y' THEN
407 fnd_file.put_line(FND_FILE.LOG, 'WARNING: There is a discrepancy. Total Genealogy ('
408 || l_num_genealogy || ') does not match total given to workers ('
409 || l_num_genealogy_to_workers || ')');
410 fnd_file.new_line(FND_FILE.LOG,1);
411
412 END IF;
413
414 end if;
415
416 COMMIT;
417 Return l_success;
418
419 EXCEPTION
420
421 WHEN ERROR THEN
422
423 l_success := 3;
424 error_counter := error_counter + 1;
425 error_number := SQLCODE;
426 error_message := SUBSTR(SQLERRM, 1, 512);
427
428 IF G_DEBUG = 'Y' THEN
429 fnd_file.put_line(FND_FILE.LOG, error_number);
430 fnd_file.put_line(FND_FILE.LOG, error_message);
431 fnd_file.new_line(FND_FILE.LOG,1);
432 END IF;
433
434
435 COMMIT;
436 Return l_success;
437
438 WHEN OTHERS THEN
439
440 l_success := 2;
441 error_counter := error_counter + 1;
442 error_number := SQLCODE;
443 error_message := SUBSTR(SQLERRM, 1, 512);
444
445 IF G_DEBUG = 'Y' THEN
446 fnd_file.put_line(FND_FILE.LOG, error_number);
447 fnd_file.put_line(FND_FILE.LOG, error_message);
448 fnd_file.new_line(FND_FILE.LOG,1);
449 END IF;
450
451 COMMIT;
452 Return l_success;
453
454 END Import_Genealogy;
455
456 END EAM_GENEALOGY_IMPORT_PVT;
[Home] [Help]