1 PACKAGE BODY ENI_DBI_PCO_LOAD_PKG AS
2 /* $Header: ENIPCOLB.pls 120.3 2006/03/31 01:11:38 lparihar noship $ */
3
4 -- Global variables
5 g_eni_schema VARCHAR2(30);
6 g_bom_schema VARCHAR2(30);
7 l_status VARCHAR2(30);
8 l_industry VARCHAR2(30);
9
10 TYPE g_conc_request_struct IS RECORD (
11 request_id number,
12 request_phase VARCHAR2(20));
13
14 TYPE g_conc_request_varray IS VARRAY(10) OF g_conc_request_struct;
15 g_conc_request_array g_conc_request_varray;
16 g_actual_workers number;
17 g_batch_size number;
18 g_actual_recs_to_process number;
19 g_recs_per_worker number;
20 g_organization_id NUMBER;
21 g_number_of_batches NUMBER;
22 g_recs_per_batch number;
23 g_collection_date DATE;
24
25 procedure validate_set_parameters(
26 p_num_workers in number,
27 p_batch_size in number) is
28 begin
29
30 SELECT COUNT(*)
31 INTO g_actual_recs_to_process
32 FROM eni_dbi_pco_worker_assignments;
33
34 -- Set the batch size
35
36 IF (NVL(p_batch_size,0) < 50) THEN
37 g_batch_size := 50;
38 ELSIF (p_batch_size > 200) THEN
39 g_batch_size := 200;
40 END IF;
41 g_number_of_batches := ceil(g_actual_recs_to_process/g_batch_size);
42 g_recs_per_batch := ceil(g_actual_recs_to_process/g_number_of_batches);
43 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch size in validate = ' || g_batch_size);
44
45 -- Set the number of workers
46 g_actual_workers := least(ceil(g_actual_recs_to_process/g_batch_size), least(p_num_workers, 10)); --FND_PROFILE.get('DBI_MAX_WORKERS'));
47 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Second Batch size in validate = ' || g_batch_size);
48
49 IF (g_actual_workers = 0) THEN
50 g_actual_workers := 1;
51 END IF;
52
53 end validate_set_parameters;
54
55 procedure collect_modified_bills IS
56
57 -- The cursor c_modified_bill_of_materials stores all the Bills that have been recorded in the
58 -- logs MLOG$_BOM_INVENTORY_COMPON and MLOG$_BOM_BILL_OF_MATERIAL.
59
60 CURSOR c_modified_bill_of_materials(last_collected_date IN date)
61 IS
62 -- Modified for bug # 3669751
63 SELECT UNIQUE -- Collects all modified common/non-common bills whose orgs are in org temp table
64 bbom.assembly_item_id AS assembly_item_id,
65 bbom.organization_id AS organization_id
66 FROM
67 mlog$_bom_components_b mbic,
68 bom_structures_b bbom
69 WHERE
70 mbic.bill_sequence_id = bbom.bill_sequence_id and
71 bbom.alternate_bom_designator IS NULL and
72 bbom.bill_sequence_id = bbom.common_bill_sequence_id and
73 exists (select 1 from bom_structures_b bbom_common
74 where bbom_common.common_bill_sequence_id = bbom.bill_sequence_id
75 and bbom_common.organization_id IN
76 (select * from eni_dbi_part_count_org_temp))
77 and mbic.snaptime$$ > NVL(last_collected_date,mbic.snaptime$$)
78 UNION -- Collects all deleted/modified bills whose orgs are in the org temp table
79 SELECT UNIQUE
80 bbom.assembly_item_id AS assembly_item_id,
81 bbom.organization_id AS organization_id
82 FROM
83 mlog$_bom_structures_b bbom -- Bug # 3394284
84 WHERE
85 bbom.dmltype$$ <> 'I' and
86 bbom.alternate_bom_designator IS NULL and
87 bbom.bill_sequence_id = bbom.common_bill_sequence_id and
88 bbom.organization_id IN (select * from eni_dbi_part_count_org_temp)
89 UNION -- This query collects all the common bills for the organizations in the temp table
90 SELECT UNIQUE -- Collects any newly commoned bills
91 bbom.assembly_item_id AS assembly_item_id,
92 bbom.organization_id AS organization_id
93 FROM
94 mlog$_bom_structures_b mlog_bbom, bom_structures_b bbom
95 WHERE
96 mlog_bbom.alternate_bom_designator IS NULL and
97 mlog_bbom.bill_sequence_id <> mlog_bbom.common_bill_sequence_id and
98 mlog_bbom.organization_id IN (select * from eni_dbi_part_count_org_temp) and
99 mlog_bbom.common_bill_sequence_id = bbom.bill_sequence_id;
100
101 o_error_msg varchar2(1000);
102 o_error_code varchar2(1000);
103 l_last_collected_date date;
104 begin
105
106 IF(FND_INSTALLATION.GET_APP_INFO('ENI', l_status, l_industry, g_eni_schema))
107 THEN NULL;
108 END IF;
109
110 l_last_collected_date := fnd_date.displayDT_to_date(BIS_COLLECTION_UTILITIES.get_last_refresh_period('ENI_DBI_PART_COUNT_F'));
111
112 dbms_mview.refresh('ENI_DBI_BOM_COMPONENTS_MV1','F');
113
114 select sysdate into g_collection_date from dual;
115 execute immediate 'TRUNCATE TABLE '||g_eni_schema||'.ENI_DBI_PCO_WORKER_ASSIGNMENTS';
116
117 -- Loop to Implode all the Modified Bills.
118 FOR r_modified_bill_of_materials IN c_modified_bill_of_materials(l_last_collected_date) LOOP
119 bompimpl.imploder_userexit(
120 sequence_id => 1,
121 eng_mfg_flag => 2,
122 org_id => r_modified_bill_of_materials.organization_id,
123 impl_flag => 2,
124 display_option => 2,
125 levels_to_implode => 60,
126 item_id => r_modified_bill_of_materials.assembly_item_id,
127 impl_date => TO_CHAR(sysdate,'YYYY/MM/DD HH24:MI'),
128 err_msg => o_error_msg,
129 err_code => o_error_code);
130
131 --Remove duplicates from BOM_IMPLOSION_TEMP
132 --as ENI_DBI_PCO_WORKER_ASSIGNMENTS already has rows
133 DELETE FROM bom_implosion_temp bit
134 WHERE EXISTS (SELECT NULL
135 FROM eni_dbi_pco_worker_assignments p
136 WHERE p.assembly_item_id = bit.parent_item_id
137 and p.organization_id = bit.organization_id);
138
139 -- Storing the implosion results into a table.
140 INSERT INTO eni_dbi_pco_worker_assignments
141 (
142 assembly_item_id,
143 organization_id,
144 pto_flag,
145 bom_type,
146 worker_id,
147 incr_status
148 )
149 (SELECT unique
150 bit.parent_item_id AS assembly_item_id,
151 bit.organization_id AS organization_id,
152 (select msi.pick_components_flag from mtl_system_items_b msi
153 where msi.inventory_item_id = bit.parent_item_id and
154 msi.organization_id = bit.organization_id) AS pick_components_flag,
155 (select msi.bom_item_type from mtl_system_items_b msi
156 where msi.inventory_item_id = bit.parent_item_id and
157 msi.organization_id = bit.organization_id) AS bom_item_type,
158 NULL as worker_id,
159 0 as incr_status
160 FROM
161 BOM_IMPLOSION_TEMP bit, BOM_BILL_OF_MATERIALS bbom
162 WHERE
163 bit.parent_item_id = bbom.assembly_item_id
164 AND bit.organization_id = bbom.organization_id
165 AND bbom.bill_sequence_id = bbom.common_bill_sequence_id
166 AND bbom.alternate_bom_designator IS NULL);
167
168 DELETE FROM bom_implosion_temp;
169 END LOOP; -- Completed Implosion all the Modified Bills.
170 COMMIT;
171 dbms_mview.refresh('ENI_DBI_BOM_COMPONENTS_MV2','F');
172
173 end collect_modified_bills;
174
175 procedure assign_worker_ids(p_collect_mode IN VARCHAR2) is
176 begin
177 IF(FND_INSTALLATION.GET_APP_INFO('ENI', l_status, l_industry, g_eni_schema))
178 THEN NULL;
179 END IF;
180
181 IF (p_collect_mode = 'INIT') THEN
182 -- Distributing the work equally among all the workers i.e., assigning equal number of Bills
183 -- to all the workers for explosion.
184 execute immediate 'TRUNCATE TABLE '||g_eni_schema||'.ENI_DBI_PCO_WORKER_ASSIGNMENTS';
185
186 IF (g_organization_id IS NULL) THEN
187 -- Insert statement to insert into the worker assigments table when
188 -- no organization is selected
189 INSERT INTO eni_dbi_pco_worker_assignments
190 (
191 assembly_item_id,
192 organization_id,
193 pto_flag,
194 bom_type,
195 worker_id,
196 incr_status
197 )
198 (select
199 bbom.assembly_item_id AS inventory_item_id,
200 bbom.organization_id,
201 msi.pick_components_flag AS pto_flag,
202 msi.bom_item_type AS bom_type,
203 NULL worker_id,
204 0 AS incr_status
205 from bom_structures_b bbom,mtl_system_items_b msi -- Bug # 3394284
206 where bill_sequence_id = common_bill_sequence_id and
207 bbom.alternate_bom_designator IS NULL and
208 bbom.organization_id = msi.organization_id and
209 bbom.assembly_item_id = msi.inventory_item_id and
210 msi.bom_item_type <> 2);
211 ELSE
212 -- Modified for bug # 3669751
213 -- Statement to insert into the worker assignments table when
214 -- an organization is selected.
215 -- All the common / non-common bills in the organization selected are inserted.
216 INSERT INTO eni_dbi_pco_worker_assignments
217 (
218 assembly_item_id,
219 organization_id,
220 pto_flag,
221 bom_type,
222 worker_id,
223 incr_status
224 )
225 (select UNIQUE
226 NVL(bbom.common_assembly_item_id,assembly_item_id) AS inventory_item_id,
227 NVL(bbom.common_organization_id,bbom.organization_id) AS organization_id,
228 msi.pick_components_flag AS pto_flag,
229 msi.bom_item_type AS bom_type,
230 NULL worker_id,
231 0 AS incr_status
232 from bom_structures_b bbom,mtl_system_items_b msi
233 where
234 bbom.alternate_bom_designator IS NULL and
235 bbom.organization_id = g_organization_id and
236 NVL(bbom.common_organization_id,bbom.organization_id) = msi.organization_id and
237 NVL(bbom.common_assembly_item_id,bbom.assembly_item_id) = msi.inventory_item_id and
238 msi.bom_item_type <> 2 );
239 COMMIT; -- Commit the bills to be collected into the worker assignments table
240
241 DELETE FROM eni_dbi_part_count_f
242 WHERE (assembly_item_id,organization_id) IN
243 (SELECT assembly_item_id, organization_id
244 FROM eni_dbi_pco_worker_assignments);
245 COMMIT;
246 END IF;
247
248 SELECT sysdate into g_collection_date from dual;
249
250 ELSIF (p_collect_mode = 'INCR') THEN
251 ENI_DBI_PCO_LOAD_PKG.collect_modified_bills;
252 -- procedure collects all the modified bills' information for incremental collection.
253 END IF;
254
255 end assign_worker_ids;
256
257 procedure launch_workers(p_collect_mode IN VARCHAR2) is
258 l_request_id number;
259 begin
260 g_conc_request_array := g_conc_request_varray(null,null,null,null,null,null,
261 null,null,null,null);
262
263 fnd_profile.put('CONC_SINGLE_THREAD','N');
264
265 for i in 1..g_actual_workers LOOP
266 l_request_id := FND_REQUEST.SUBMIT_REQUEST
267 (
268 application => 'ENI', -- Application short name
269 program => 'ENI_DBI_PCO_LOAD_WORKER', -- concurrent program short name
270 description => null, -- description (optional)
271 start_time => sysdate,
272 sub_request => false, -- called from another conc. request
273 argument1 => i,
274 argument2 => g_batch_size,
275 argument3 => p_collect_mode);
276 commit;
277 g_conc_request_array(i).request_id := l_request_id;
278 g_conc_request_array(i).request_phase := 'NORMAL';
279 end loop;
280
281 end launch_workers;
282
283 PROCEDURE wait_for_workers(p_collect_mode IN VARCHAR2) IS
284
285 l_request_flag boolean;
286 o_phase varchar2(100);
287 o_status varchar2(100);
288 o_dev_phase varchar2(100);
289 o_dev_status varchar2(100);
290 o_message varchar2(100);
291 l_done_flag varchar2(1);
292 l_error_occured NUMBER := 0;
293 l_error_code NUMBER;
294
295 BEGIN
296
297 while (true) loop
298
299 for i in 1 .. g_actual_workers loop
300
301 -- Find out the phase of this request id.
302
303 l_request_flag := fnd_concurrent.get_request_status(g_conc_request_array(i).request_id,
304 null,
305 null,
306 o_phase,
307 o_status,
308 o_dev_phase,
309 o_dev_status,
310 o_message);
311
312 -- set the phase of this request id in the array
313 g_conc_request_array(i).request_phase := o_phase;
314
315 end loop;
316 l_done_flag := 'Y';
317 for i in 1..g_actual_workers loop
318 if ( g_conc_request_array(i).request_phase <> 'Completed') then
319 l_done_flag := 'N';
320 end if;
321 end loop;
322 if l_done_flag = 'Y' then
323 exit;
324 end if;
325 end loop;
326
327 SELECT COUNT(*)
328 INTO l_error_occured
329 FROM ENI_DBI_PCO_WORKER_ASSIGNMENTS
330 WHERE incr_status = -1;
331
332 IF (l_error_occured <> 0) THEN
333 FND_FILE.PUT_LINE(FND_FILE.LOG,'Incremental Collection has errored out.');
334 RETURN;
335 END IF;
336
337 IF (p_collect_mode = 'INIT') THEN -- when initial collection, complete refresh the MV
338
339 DBMS_MVIEW.REFRESH('ENI_DBI_PART_COUNT_MV','C');
340
341 ELSE -- When Incremental collection
342
343 -- Deleting from part count fact table the data for all the modified Bills.
344 DELETE FROM ENI_DBI_PART_COUNT_F
345 WHERE
346 (assembly_item_id,organization_id) IN
347 (SELECT assembly_item_id, organization_id FROM eni_dbi_pco_worker_assignments);
348 commit;
349
350 INSERT INTO ENI_DBI_PART_COUNT_F(
351 assembly_item_id,
352 organization_id,
353 item_id_fk,
354 component_item_id,
355 effectivity_date,
356 disable_date,
357 bom_level,
358 ITEM_CATALOG_GROUP_ID
359 )
360 (SELECT assembly_item_id,
361 organization_id,
362 '-1',
363 component_item_id,
364 effectivity_date,
365 disable_date,
366 bom_level,
367 -1
368 FROM ENI_DBI_PART_COUNT_INCR_TEMP);
369 COMMIT;
370 BEGIN
371 DBMS_MVIEW.REFRESH('ENI_DBI_PART_COUNT_MV','F');
372 /* Bug 5130157
373 Full refresh might raise error ORA 12034
374 catch it and execute a full refresh instead
375 */
376 EXCEPTION
377 WHEN OTHERS THEN
378 l_error_code := SQLCODE;
379 IF l_error_code = -12034 THEN
380 DBMS_MVIEW.REFRESH('ENI_DBI_PART_COUNT_MV','C');
381 ELSE
382 RAISE;
383 END IF;
384 END;
385 END IF;
386
387 END WAIT_FOR_WORKERS;
388
389 procedure cleanup is
390 begin
391
392 IF(FND_INSTALLATION.GET_APP_INFO('ENI', l_status, l_industry, g_eni_schema))
393 THEN NULL;
394 END IF;
395
396 -- Truncate all the temporary tables used.
397 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||g_eni_schema||'.ENI_DBI_PCO_WORKER_ASSIGNMENTS';
398 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||g_eni_schema||'.ENI_DBI_PART_COUNT_INCR_TEMP';
399 -- DELETE FROM mlog$_bom_components_b;
400 -- DELETE FROM mlog$_bom_structures_b;
401
402 end cleanup;
403
404 procedure process_incident_interface_g(
405 o_err_code out nocopy varchar2,
406 o_err_msg out nocopy varchar2,
407 p_num_workers in number default 1,
408 p_organization_id IN NUMBER default NULL,
409 p_batch_size in number default 10,
410 p_collect_mode IN VARCHAR2 default 'INIT',
411 p_purge_fact IN VARCHAR2 default 'YES'
412 ) IS
413 l_num_recs_to_process NUMBER;
414 l_worker_id NUMBER;
415 s_id NUMBER(10);
416 start_time date;
417 end_time date;
418 time_taken number(20,10);
419 l_error_msg varchar2(1000);
420 l_error_code varchar2(1000);
421 worker_id NUMBER(2);
422 l_org_exists NUMBER;
423
424 begin
425
426 -- Initialize variables
427 g_organization_id := p_organization_id;
428 g_actual_workers := p_num_workers;
429 g_batch_size := NVL(p_batch_size,1);
430
431 FND_FILE.PUT_LINE(FND_FILE.LOG, 'collection mode parameter is = ' || p_collect_mode);
432 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch size = ' || g_batch_size);
433
434 IF(FND_INSTALLATION.GET_APP_INFO('ENI', l_status, l_industry, g_eni_schema))
435 THEN NULL;
436 END IF;
437
438 IF (p_collect_mode = 'INIT') THEN
439 IF ((p_purge_fact = 'YES') OR (p_organization_id IS NULL)) THEN
440
441 IF (BIS_COLLECTION_UTILITIES.SETUP(p_object_name => 'ENI_DBI_PART_COUNT_F') = false) then
442 RAISE_APPLICATION_ERROR(-20000,l_error_msg);
443 END IF;
444
445 execute immediate 'TRUNCATE TABLE '||g_eni_schema||'.eni_dbi_part_count_f';
446 execute immediate 'TRUNCATE TABLE '||g_eni_schema||'.eni_dbi_part_count_org_temp';
447
448 DBMS_MVIEW.REFRESH('ENI_DBI_BOM_COMPONENTS_MV1','C');
449 DBMS_MVIEW.REFRESH('ENI_DBI_BOM_COMPONENTS_MV2','C');
450
451 IF (p_organization_id IS NULL) THEN
452 INSERT INTO eni_dbi_part_count_org_temp
453 (organization_id)
454 (SELECT organization_id from hr_all_organization_units);
455 COMMIT;
456 ELSE
457 INSERT INTO eni_dbi_part_count_org_temp
458 (organization_id) VALUES (p_organization_id);
459 COMMIT;
460 END IF;
461 ELSE
462 -- delete from eni_dbi_part_count_f
463 -- where organization_id = p_organization_id;
464 -- COMMIT;
465
466 SELECT NVL((SELECT 1 from eni_dbi_part_count_org_temp
467 where organization_id = p_organization_id),-1)
468 INTO l_org_exists
469 FROM DUAL;
470 IF (l_org_exists = -1) THEN
471 INSERT INTO eni_dbi_part_count_org_temp
472 (organization_id) VALUES (p_organization_id);
473 COMMIT;
474 END IF;
475 END IF;
476
477 ELSE
478 execute immediate 'TRUNCATE TABLE '||g_eni_schema||'.eni_dbi_part_count_incr_temp';
479 IF (BIS_COLLECTION_UTILITIES.SETUP(p_object_name => 'ENI_DBI_PART_COUNT_F') = false) then
480 RAISE_APPLICATION_ERROR(-20000,l_error_msg);
481 END IF;
482 END IF;
483
484 -- Assign worker ids to the interface table records
485 ENI_DBI_PCO_LOAD_PKG.assign_worker_ids(p_collect_mode);
486 FND_FILE.PUT_LINE(FND_FILE.LOG, 'assign worker ids');
487 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Started Execution...............');
488
489 SELECT count(*)
490 INTO l_num_recs_to_process
491 FROM eni_dbi_pco_worker_assignments;
492
493 IF (l_num_recs_to_process <= 0) THEN
494 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No Bills to work upon');
495 RETURN;
496 ELSE
497 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number of Bills to work upon is:' || l_num_recs_to_process);
498 END IF;
499
500 -- Validate the parameters and reset them if required
501 ENI_DBI_PCO_LOAD_PKG.validate_set_parameters(p_num_workers,p_batch_size);
502 FND_FILE.PUT_LINE(FND_FILE.LOG, 'validate_set_parameters');
503 FND_FILE.PUT_LINE(FND_FILE.LOG, 'after validate Batch size = ' || g_batch_size);
504
505
506 -- Launch workers
507 ENI_DBI_PCO_LOAD_PKG.launch_workers(p_collect_mode);
508 FND_FILE.PUT_LINE(FND_FILE.LOG, 'launch workers..');
509
510 -- Wait for the workers to complete the collection.
511 IF (p_collect_mode = 'INCR') THEN
512 ENI_DBI_PCO_LOAD_PKG.wait_for_workers('INCR');
513 ENI_DBI_PCO_LOAD_PKG.cleanup;
514 FND_FILE.PUT_LINE(FND_FILE.LOG, 'wait for workers is complete');
515 BIS_COLLECTION_UTILITIES.WRAPUP(
516 p_status => true,
517 p_period_from => sysdate,
518 p_period_to => g_collection_date);
519 ELSE
520 ENI_DBI_PCO_LOAD_PKG.wait_for_workers('INIT');
521 IF ((p_purge_fact = 'YES') OR (p_organization_id IS NULL)) THEN
522 BIS_COLLECTION_UTILITIES.WRAPUP(
523 p_status => true,
524 p_period_from => sysdate,
525 p_period_to => g_collection_date);
526 END IF;
527 END IF;
528
529 END process_incident_interface_g;
530
531 PROCEDURE part_count_collect_worker(
532 o_error_msg OUT NOCOPY VARCHAR2,
533 o_error_code OUT NOCOPY VARCHAR2,
534 p_worker_id IN NUMBER DEFAULT 1,
535 p_batch_size IN NUMBER DEFAULT 50,
536 p_collect_mode IN VARCHAR2 DEFAULT 'INIT'
537 )IS
538 l_error NUMBER := 0;
539 start_time DATE;
540 end_time DATE;
541 time_taken NUMBER(20,10);
542 s_id NUMBER(10);
543 p_error_occured NUMBER := 0;
544 -- the set of items in mtl_system_items_b
545 -- which have BOM_ENABLED_FLAG set to 'Y'.
546 begin
547
548 IF(FND_INSTALLATION.GET_APP_INFO('ENI', l_status, l_industry, g_eni_schema))
549 THEN NULL;
550 END IF;
551
552
553 WHILE (TRUE) LOOP
554 -- Updating the rows to be worked on by the worker by setting worker_id.
555
556 SELECT count(*)
557 INTO l_error
558 FROM eni_Dbi_pco_worker_assignments
559 WHERE incr_status = -1;
560
561 IF (l_error > 0) THEN
562 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error has occured during the collection');
563 o_error_code := 2;
564 o_error_msg := 'Error';
565 EXIT;
566 END IF;
567
568 UPDATE eni_dbi_pco_worker_assignments
569 SET worker_id = p_worker_id , incr_status = 1
570 WHERE worker_id IS NULL AND incr_status = 0
571 AND rownum <= p_batch_size;
572
573 IF (SQL%ROWCOUNT = 0) THEN
574 EXIT;
575 ELSE
576 COMMIT;
577 ENI_DBI_PCO_LOAD_PKG.part_count_collect(p_worker_id,p_collect_mode,p_error_occured);
578
579 -- Updating the status of the rows worked upon to 0.
580 UPDATE eni_dbi_pco_worker_assignments
581 SET incr_status = 0
582 WHERE worker_id = p_worker_id AND incr_status = 1;
583 COMMIT;
584
585 IF (NVL(p_error_occured,0) = 1) THEN
586 o_error_code := 1;
587 o_error_msg := 'Warning';
588 ELSIF (NVL(p_error_occured,0) = 2) THEN
589 o_error_code := 2;
590 o_error_msg := 'Error occured';
591 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||g_eni_schema||'.ENI_DBI_PART_COUNT_F';
592 END IF;
593 END IF;
594 END LOOP;
595
596 EXCEPTION
597 WHEN OTHERS THEN
598 o_error_code := 2;
599 o_error_msg := SQLERRM;
600 eni_dbi_util_pkg.log('An error prevented the initial part count collection from completing successfully');
601 eni_dbi_util_pkg.log(o_error_code||':'||o_error_msg);
602 ROLLBACK;
603 END part_count_collect_worker;
604
605 -- This is the procedure to collect the bills assigned to the worker p_worker_id into Part Count Fact.
606 PROCEDURE part_count_collect(
607 p_worker_id IN NUMBER,
608 p_collect_mode IN VARCHAR2 DEFAULT 'INIT',
609 o_error_occured OUT NOCOPY NUMBER
610 ) IS
611 CURSOR c_bom_enabled_items(p_worker_id IN NUMBER) IS
612 SELECT
613 assembly_item_id,
614 organization_id,
615 pto_flag,
616 bom_type
617 FROM
618 eni_dbi_pco_worker_assignments worker_bills
619 WHERE
620 worker_bills.worker_id = p_worker_id and
621 worker_bills.incr_status = 1 ;
622 l_grp_id NUMBER := 0;
623 l_inventory_item_id NUMBER;
624 l_org_id NUMBER;
625 l_bill_sequence_id NUMBER;
626 l_incr_flag NUMBER := 0;
627 l_item_org VARCHAR2(50);
628 l_error_msg VARCHAR2(1000);
629 l_error_code VARCHAR2(1000);
630
631 BEGIN
632
633 IF(FND_INSTALLATION.GET_APP_INFO('BOM', l_status, l_industry, g_bom_schema))
634 THEN NULL;
635 END IF;
636
637 IF(FND_INSTALLATION.GET_APP_INFO('ENI', l_status, l_industry, g_eni_schema))
638 THEN NULL;
639 END IF;
640
641 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||g_bom_schema||'.BOM_EXPLOSION_TEMP';
642 FOR r_bom_enabled_items IN c_bom_enabled_items(p_worker_id) LOOP
643 l_org_id := r_bom_enabled_items.organization_id;
644 l_inventory_item_id := r_bom_enabled_items.assembly_item_id;
645 l_grp_id := l_grp_id + 1;
646 -- run the bom exploder for this item
647 IF (NOT((r_bom_enabled_items.pto_flag = 'Y') and (r_bom_enabled_items.bom_type = 2))) THEN
648 -- Modified the call to bom exploder to call the procedure explode as per the BOM bug fix # 3575617
649 bompexpl.explode(
650 org_id => l_org_id,
651 bom_or_eng => 2,
652 std_comp_flag => 2,
653 grp_id => l_grp_id,
654 levels_to_explode => 60,
655 item_id => l_inventory_item_id,
656 rev_date => '1900/01/01 1:00', -- Made changes as per the bug # 3575617
657 explode_option => 1,
658 err_msg => l_error_msg,
659 error_code => l_error_code);
660
661 IF (l_error_code <> 0) OR l_error_msg IS NOT NULL THEN
662 -- Error in Explosion
663 SELECT bbom.bill_sequence_id INTO l_bill_sequence_id
664 FROM BOM_STRUCTURES_B bbom -- Bug # 3394284
665 WHERE bbom.assembly_item_id = l_inventory_item_id
666 AND bbom.organization_id = l_org_id
667 AND bbom.alternate_bom_designator IS NULL
668 AND bbom.bill_sequence_id = bbom.common_bill_sequence_id;
669 ENI_DBI_UTIL_PKG.LOG('Error occured during the explosion of Item-Org ' || l_inventory_item_id || '-' || l_org_id ||' of Bill_Sequence_id' || l_bill_sequence_id);
670 ENI_DBI_UTIL_PKG.LOG(l_error_code||':'||l_error_msg);
671
672 IF (l_error_msg <> 'BOM_MAX_LEVELS') THEN -- Modified as part of the fix for the Bug # 3140363
673 l_error_code := 2; -- Modified as part of the fix for the Bug # 3127260
674 o_error_occured := 2;
675 -- Error
676 UPDATE eni_dbi_pco_worker_assignments
677 SET incr_status = -1
678 WHERE
679 worker_id = p_worker_id AND
680 assembly_item_id = l_inventory_item_id AND
681 organization_id = l_org_id;
682 COMMIT;
683 EXECUTE IMMEDIATE 'truncate table '||g_eni_schema||'.eni_dbi_part_count_f';
684 RETURN; -- Returning with out collecting remaining bills due to error
685 ELSE
686 ENI_DBI_UTIL_PKG.LOG('Increase the MAX_BOM_LEVEL value in the profile of the Organization ' || l_org_id);
687 ENI_DBI_UTIL_PKG.LOG('If the MAX_BOM_LEVEL value is 59, then this is max number of levels possible for explosion.');
688 l_error_code := 1; -- Warning
689 o_error_occured := 1;
690 END IF; -- BOM_MAX_LEVELS check end
691
692 END IF; -- Explosion error check end
693 END IF; -- end of explosion for the current item-org
694 END LOOP; -- end of explosion of all the Bills
695
696 FOR l_temp_var IN 1..1 LOOP
697 BEGIN
698 IF (p_collect_mode = 'INIT') THEN
699 INSERT /*+ APPEND */ INTO eni_dbi_part_count_f
700 (
701 assembly_item_id,
702 organization_id,
703 item_id_fk,
704 component_item_id,
705 effectivity_date,
706 disable_date,
707 bom_level,
708 ITEM_CATALOG_GROUP_ID
709 )
710 SELECT
711 b1.top_item_id,
712 b1.organization_id,
713 '-1',
714 b1.component_item_id AS component_item_id,
715 trunc(b1.effectivity_date),
716 trunc(NVL(b1.disable_date,to_date('1-1-2085','dd-mm-yyyy'))),
717 b1.plan_level,
718 -1
719 FROM
720 bom_explosion_temp b1,mtl_system_items_b i
721 WHERE
722 b1.component_sequence_id IS NOT NULL and
723 b1.bom_item_type <> 2 and
724 -- Filtering out the Option classes items which
725 -- donot have Bills attached to them.
726 -- Bug 3968305: use wip_supply_type at component-level, not item-level
727 b1.wip_supply_type <> 6 and
728 -- Filtering out the phantom items.
729 b1.component_item_id = i.inventory_item_id and
730 b1.organization_id = i.organization_id and
731 not exists (
732 select 1 from bom_bill_of_materials bbom
733 where bbom.assembly_item_id = b1.component_item_id and
734 bbom.organization_id = b1.organization_id) and
735 (NOT(((1,'PTO') IN (select i3.bom_item_type,i3.item_type
736 from mtl_system_items_b i3
737 where i3.inventory_item_id = b1.top_item_id and
738 i3.organization_id = b1.organization_id)) and
739 ((2,'POC') IN (select i2.bom_item_type,i2.item_type
740 from mtl_system_items_b i2
741 where i2.inventory_item_id = b1.assembly_item_id and
742 i2.organization_id = b1.organization_id))));
743 ELSE
744 INSERT /*+ APPEND */ INTO eni_dbi_part_count_incr_temp
745 (
746 assembly_item_id,
747 organization_id,
748 component_item_id,
749 effectivity_date,
750 disable_date,
751 bom_level
752 )
753 SELECT
754 b1.top_item_id,
755 b1.organization_id,
756 b1.component_item_id AS component_item_id,
757 trunc(b1.effectivity_date),
758 trunc(NVL(b1.disable_date,to_date('1-1-2085','dd-mm-yyyy'))),
759 b1.plan_level
760 FROM
761 bom_explosion_temp b1,mtl_system_items_b i
762 WHERE
763 b1.component_sequence_id IS NOT NULL and
764 b1.bom_item_type <> 2 and
765 -- Filtering out the Option classes items which
766 -- donot have Bills attached to them.
767 -- Bug 3968305: use wip_supply_type at component-level, not item-level
768 b1.wip_supply_type <> 6 and
769 -- Filtering out the phantom items.
770 b1.component_item_id = i.inventory_item_id and
771 b1.organization_id = i.organization_id and
772 not exists (
773 select 1 from bom_bill_of_materials bbom
774 where bbom.assembly_item_id = b1.component_item_id and
775 bbom.organization_id = b1.organization_id) and
776 (NOT(((1,'PTO') IN (select i3.bom_item_type,i3.item_type
777 from mtl_system_items_b i3
778 where i3.inventory_item_id = b1.top_item_id and
779 i3.organization_id = b1.organization_id)) and
780 ((2,'POC') IN (select i2.bom_item_type,i2.item_type
781 from mtl_system_items_b i2
782 where i2.inventory_item_id = b1.assembly_item_id and
783 i2.organization_id = b1.organization_id))));
784 END IF;
785 EXCEPTION
786 WHEN OTHERS THEN
787 l_error_msg := SQLERRM;
788 l_error_code := 2;
789 -- Modified as part of the fix for the Bug # 3127260
790 FND_FILE.PUT_LINE(FND_FILE.LOG,'The following error has occured while inserting into the Part Count fact table');
791 ENI_DBI_UTIL_PKG.LOG(l_error_code||':'||l_error_msg);
792 ENI_DBI_UTIL_PKG.LOG('An error prevented the INCREMENTAL part count collection from completing successfully');
793 EXECUTE IMMEDIATE 'truncate table '||g_eni_schema||'.eni_dbi_part_count_f';
794 RETURN; -- Return from the for loop after the explosion.
795 END;
796 END LOOP;
797
798 EXCEPTION
799 WHEN OTHERS THEN
800 l_error_code := 2;
801 l_error_msg := SQLERRM;
802 ENI_DBI_UTIL_PKG.LOG('An error prevented the initial part count collection from completing successfully');
803 ENI_DBI_UTIL_PKG.LOG(l_error_code||':'||l_error_msg);
804
805 END PART_COUNT_COLLECT; -- End of the procedure definition
806
807 END ENI_DBI_PCO_LOAD_PKG;
[Home] [Help]