[Home] [Help]
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
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(
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
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.
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,
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
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,
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,
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;
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.');
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,
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:
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;
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');
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:
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:
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;
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