[Home] [Help]
6: select GMD_QC_SPEC_HDR_ID_S.nextval from dual;
7:
8: cursor get_spec IS
9: select *
10: from qc_spec_mst
11: order by orgn_code, whse_code, location, lot_id,
12: cust_id, order_org_id, ship_to_site_id, vendor_id,
13: batch_id, formula_id, formulaline_id, routing_id, routingstep_id,
14: charge, oprn_id, item_id, spec_hdr_id
14: charge, oprn_id, item_id, spec_hdr_id
15: for update of spec_hdr_id NOWAIT;
16:
17: seq_id NUMBER;
18: l_o qc_spec_mst.orgn_code%TYPE;
19: l_w qc_spec_mst.whse_code%TYPE;
20: l_l qc_spec_mst.location%TYPE;
21: l_i qc_spec_mst.item_id%TYPE := -1;
22: l_lot qc_spec_mst.lot_id%TYPE ;
15: for update of spec_hdr_id NOWAIT;
16:
17: seq_id NUMBER;
18: l_o qc_spec_mst.orgn_code%TYPE;
19: l_w qc_spec_mst.whse_code%TYPE;
20: l_l qc_spec_mst.location%TYPE;
21: l_i qc_spec_mst.item_id%TYPE := -1;
22: l_lot qc_spec_mst.lot_id%TYPE ;
23: l_c qc_spec_mst.cust_id%TYPE;
16:
17: seq_id NUMBER;
18: l_o qc_spec_mst.orgn_code%TYPE;
19: l_w qc_spec_mst.whse_code%TYPE;
20: l_l qc_spec_mst.location%TYPE;
21: l_i qc_spec_mst.item_id%TYPE := -1;
22: l_lot qc_spec_mst.lot_id%TYPE ;
23: l_c qc_spec_mst.cust_id%TYPE;
24: l_ou qc_spec_mst.order_org_id%TYPE;
17: seq_id NUMBER;
18: l_o qc_spec_mst.orgn_code%TYPE;
19: l_w qc_spec_mst.whse_code%TYPE;
20: l_l qc_spec_mst.location%TYPE;
21: l_i qc_spec_mst.item_id%TYPE := -1;
22: l_lot qc_spec_mst.lot_id%TYPE ;
23: l_c qc_spec_mst.cust_id%TYPE;
24: l_ou qc_spec_mst.order_org_id%TYPE;
25: l_sh qc_spec_mst.ship_to_site_id%TYPE;
18: l_o qc_spec_mst.orgn_code%TYPE;
19: l_w qc_spec_mst.whse_code%TYPE;
20: l_l qc_spec_mst.location%TYPE;
21: l_i qc_spec_mst.item_id%TYPE := -1;
22: l_lot qc_spec_mst.lot_id%TYPE ;
23: l_c qc_spec_mst.cust_id%TYPE;
24: l_ou qc_spec_mst.order_org_id%TYPE;
25: l_sh qc_spec_mst.ship_to_site_id%TYPE;
26: l_v qc_spec_mst.vendor_id%TYPE;
19: l_w qc_spec_mst.whse_code%TYPE;
20: l_l qc_spec_mst.location%TYPE;
21: l_i qc_spec_mst.item_id%TYPE := -1;
22: l_lot qc_spec_mst.lot_id%TYPE ;
23: l_c qc_spec_mst.cust_id%TYPE;
24: l_ou qc_spec_mst.order_org_id%TYPE;
25: l_sh qc_spec_mst.ship_to_site_id%TYPE;
26: l_v qc_spec_mst.vendor_id%TYPE;
27: l_b qc_spec_mst.batch_id%TYPE;
20: l_l qc_spec_mst.location%TYPE;
21: l_i qc_spec_mst.item_id%TYPE := -1;
22: l_lot qc_spec_mst.lot_id%TYPE ;
23: l_c qc_spec_mst.cust_id%TYPE;
24: l_ou qc_spec_mst.order_org_id%TYPE;
25: l_sh qc_spec_mst.ship_to_site_id%TYPE;
26: l_v qc_spec_mst.vendor_id%TYPE;
27: l_b qc_spec_mst.batch_id%TYPE;
28: l_f qc_spec_mst.formula_id%TYPE;
21: l_i qc_spec_mst.item_id%TYPE := -1;
22: l_lot qc_spec_mst.lot_id%TYPE ;
23: l_c qc_spec_mst.cust_id%TYPE;
24: l_ou qc_spec_mst.order_org_id%TYPE;
25: l_sh qc_spec_mst.ship_to_site_id%TYPE;
26: l_v qc_spec_mst.vendor_id%TYPE;
27: l_b qc_spec_mst.batch_id%TYPE;
28: l_f qc_spec_mst.formula_id%TYPE;
29: l_fl qc_spec_mst.formulaline_id%TYPE;
22: l_lot qc_spec_mst.lot_id%TYPE ;
23: l_c qc_spec_mst.cust_id%TYPE;
24: l_ou qc_spec_mst.order_org_id%TYPE;
25: l_sh qc_spec_mst.ship_to_site_id%TYPE;
26: l_v qc_spec_mst.vendor_id%TYPE;
27: l_b qc_spec_mst.batch_id%TYPE;
28: l_f qc_spec_mst.formula_id%TYPE;
29: l_fl qc_spec_mst.formulaline_id%TYPE;
30: l_r qc_spec_mst.routing_id%TYPE;
23: l_c qc_spec_mst.cust_id%TYPE;
24: l_ou qc_spec_mst.order_org_id%TYPE;
25: l_sh qc_spec_mst.ship_to_site_id%TYPE;
26: l_v qc_spec_mst.vendor_id%TYPE;
27: l_b qc_spec_mst.batch_id%TYPE;
28: l_f qc_spec_mst.formula_id%TYPE;
29: l_fl qc_spec_mst.formulaline_id%TYPE;
30: l_r qc_spec_mst.routing_id%TYPE;
31: l_rs qc_spec_mst.routingstep_id%TYPE;
24: l_ou qc_spec_mst.order_org_id%TYPE;
25: l_sh qc_spec_mst.ship_to_site_id%TYPE;
26: l_v qc_spec_mst.vendor_id%TYPE;
27: l_b qc_spec_mst.batch_id%TYPE;
28: l_f qc_spec_mst.formula_id%TYPE;
29: l_fl qc_spec_mst.formulaline_id%TYPE;
30: l_r qc_spec_mst.routing_id%TYPE;
31: l_rs qc_spec_mst.routingstep_id%TYPE;
32: l_ch qc_spec_mst.charge%TYPE;
25: l_sh qc_spec_mst.ship_to_site_id%TYPE;
26: l_v qc_spec_mst.vendor_id%TYPE;
27: l_b qc_spec_mst.batch_id%TYPE;
28: l_f qc_spec_mst.formula_id%TYPE;
29: l_fl qc_spec_mst.formulaline_id%TYPE;
30: l_r qc_spec_mst.routing_id%TYPE;
31: l_rs qc_spec_mst.routingstep_id%TYPE;
32: l_ch qc_spec_mst.charge%TYPE;
33: l_op qc_spec_mst.oprn_id%TYPE;
26: l_v qc_spec_mst.vendor_id%TYPE;
27: l_b qc_spec_mst.batch_id%TYPE;
28: l_f qc_spec_mst.formula_id%TYPE;
29: l_fl qc_spec_mst.formulaline_id%TYPE;
30: l_r qc_spec_mst.routing_id%TYPE;
31: l_rs qc_spec_mst.routingstep_id%TYPE;
32: l_ch qc_spec_mst.charge%TYPE;
33: l_op qc_spec_mst.oprn_id%TYPE;
34:
27: l_b qc_spec_mst.batch_id%TYPE;
28: l_f qc_spec_mst.formula_id%TYPE;
29: l_fl qc_spec_mst.formulaline_id%TYPE;
30: l_r qc_spec_mst.routing_id%TYPE;
31: l_rs qc_spec_mst.routingstep_id%TYPE;
32: l_ch qc_spec_mst.charge%TYPE;
33: l_op qc_spec_mst.oprn_id%TYPE;
34:
35: BEGIN
28: l_f qc_spec_mst.formula_id%TYPE;
29: l_fl qc_spec_mst.formulaline_id%TYPE;
30: l_r qc_spec_mst.routing_id%TYPE;
31: l_rs qc_spec_mst.routingstep_id%TYPE;
32: l_ch qc_spec_mst.charge%TYPE;
33: l_op qc_spec_mst.oprn_id%TYPE;
34:
35: BEGIN
36:
29: l_fl qc_spec_mst.formulaline_id%TYPE;
30: l_r qc_spec_mst.routing_id%TYPE;
31: l_rs qc_spec_mst.routingstep_id%TYPE;
32: l_ch qc_spec_mst.charge%TYPE;
33: l_op qc_spec_mst.oprn_id%TYPE;
34:
35: BEGIN
36:
37: /******* Update all 3 main qc tables with QC_REC_TYPE ********/
41: -- CR we need all the following stmt to update the tables to 'Z' without any
42: -- condition
43: -- Bug 3697857; Added Where clause where qc_rec_type is NULL
44:
45: update qc_spec_mst
46: set qc_rec_type = 'Z'
47: where qc_rec_type is NULL
48: ;
49:
149: l_ch := each_spec.charge;
150: l_op := each_spec.oprn_id;
151:
152: IF each_spec.spec_hdr_id IS NULL THEN
153: update qc_spec_mst
154: set spec_hdr_id = seq_id
155: where CURRENT OF get_spec;
156: END IF; -- if spec hdr id is NULL , give it a valid value.
157: END LOOP;
191: SELECT a.spec_hdr_id,
192: a.qc_spec_id,
193: a.from_date,
194: a.to_date
195: FROM qc_spec_mst a
196: WHERE migration_status is NULL
197: AND a.from_date > a.to_date;
198:
199: CURSOR c_samples_date(p_qc_spec_id NUMBER) IS
211: a.to_date,
212: b.from_date,
213: b.to_date l_b_to_date,
214: b.qc_spec_id l_b_qc_spec_id
215: FROM qc_spec_mst a,
216: qc_spec_mst b
217: WHERE a.migration_status is NULL
218: AND b.migration_status is NULL
219: AND a.spec_hdr_id = b.spec_hdr_id
212: b.from_date,
213: b.to_date l_b_to_date,
214: b.qc_spec_id l_b_qc_spec_id
215: FROM qc_spec_mst a,
216: qc_spec_mst b
217: WHERE a.migration_status is NULL
218: AND b.migration_status is NULL
219: AND a.spec_hdr_id = b.spec_hdr_id
220: AND a.QC_SPEC_ID <> b.QC_SPEC_ID
250: AND b.qc_spec_id = p_b_qc_spec_id;
251:
252: CURSOR c_null_vendor_id_spec IS
253: SELECT sp.qc_spec_id, sp.vendor_id
254: FROM qc_spec_mst sp, po_vend_mst v
255: WHERE sp.vendor_id IS NOT NULL
256: AND sp.vendor_id = v.vendor_id
257: AND v.of_vendor_id IS NULL
258: AND sp.migration_status IS NULL;
258: AND sp.migration_status IS NULL;
259:
260: CURSOR c_deleted_w_results (p_sysdate date) IS
261: SELECT sp.qc_spec_id dlt_qc_spec_id
262: FROM qc_spec_mst sp
263: WHERE sp.delete_mark = 1
264: AND sp.migration_status is null
265: and sp.to_date > p_sysdate; -- CR Use the variable for
266: -- SYSDATE and not nested SQL
331: SET old_qc_spec_id = qc_spec_id ,
332: qc_spec_id = NULL
333: WHERE qc_spec_id = (
334: SELECT r.qc_spec_id
335: from qc_spec_mst s
336: WHERE s.qc_spec_id = r.qc_spec_id
337: and s.qcassy_typ_id <> r.qcassy_typ_id);
338:
339:
348: -- Note: NEVER EVER UPDATE old_from_date, old_to_date, updating it in both the
349: -- modes.
350: */
351:
352: UPDATE qc_spec_mst
353: SET old_from_date = from_date,
354: old_to_date = to_date,
355: to_date = (to_date - 1/86400),
356: from_date = (from_date+1/86400)
359: l_position :=30;
360:
361: /* Case 3. Update Migration_status to 'DL' for delete specs with no results
362: ** */
363: UPDATE qc_spec_mst s
364: set migration_status = 'DL'
365: where s.delete_mark = 1
366: and s.migration_status is NULL
367: and not exists (
375: -- with results and TO_DATE > sysdate or today's date */
376:
377: FOR l_dlt_specs IN c_deleted_w_results (l_sysdate) LOOP
378: l_position :=35;
379: UPDATE qc_spec_mst
380: SET to_date = l_22hrs
381: -- CR get this into a variable
382: WHERE qc_spec_id = l_dlt_specs.dlt_qc_spec_id;
383: END LOOP;
396: --Bug start 3542894
397: l_position := 51;
398: GMA_MIGRATION.gma_insert_message (
399: p_run_id => migration_id,
400: p_table_name => 'QC_SPEC_MST',
401: p_DB_ERROR => '',
402: p_param1 => '',
403: p_param2 => '',
404: p_param3 => '',
413: FOR l_samples_date IN c_samples_date(l_spec_less_from_date.qc_spec_id) LOOP
414: l_position :=52;
415: IF (l_samples_date.min_date IS NOT NULL) THEN
416: IF (l_data_fix) THEN
417: UPDATE qc_spec_mst
418: SET from_date = l_samples_date.min_date,
419: to_date = l_samples_date.max_date
420: WHERE qc_spec_id = l_spec_less_from_date.qc_spec_id;
421: END IF;
420: WHERE qc_spec_id = l_spec_less_from_date.qc_spec_id;
421: END IF;
422: GMA_MIGRATION.gma_insert_message (
423: p_run_id => migration_id,
424: p_table_name => 'QC_SPEC_MST',
425: p_DB_ERROR => '',
426: p_param1 => l_spec_less_from_date.qc_spec_id,
427: p_param2 => l_spec_less_from_date.from_date,
428: p_param3 => l_spec_less_from_date.to_date,
439: -- Bug 3587546; Changed to only mark the row with the from_date > to_date
440: -- to not migrate, instead of all the spec_hdr_id's qc_spec_id's
441: -- along with the corrsponding samples and results.
442: IF (l_data_fix) THEN
443: UPDATE qc_spec_mst
444: SET migration_status = 'WD'
445: WHERE qc_spec_id = l_spec_less_from_date.qc_spec_id;
446: END IF;
447: GMA_MIGRATION.gma_insert_message (
445: WHERE qc_spec_id = l_spec_less_from_date.qc_spec_id;
446: END IF;
447: GMA_MIGRATION.gma_insert_message (
448: p_run_id => migration_id,
449: p_table_name => 'QC_SPEC_MST',
450: p_DB_ERROR => '',
451: p_param1 => l_spec_less_from_date.qc_spec_id,
452: p_param2 => l_spec_less_from_date.from_date,
453: p_param3 => l_spec_less_from_date.to_date,
464: END LOOP;
465: l_position:=59;
466: GMA_MIGRATION.gma_insert_message (
467: p_run_id => migration_id,
468: p_table_name => 'QC_SPEC_MST',
469: p_DB_ERROR => '',
470: p_param1 => '',
471: p_param2 => '',
472: p_param3 => '',
487: LOOP
488: -- The spec vendor_id does not have corresponding of_vendor_id
489: GMA_MIGRATION.gma_insert_message (
490: p_run_id => migration_id,
491: p_table_name => 'QC_SPEC_MST',
492: p_DB_ERROR => '',
493: p_param1 => l_null_vendor_id_spec.qc_spec_id,
494: p_param2 => l_null_vendor_id_spec.vendor_id,
495: p_param3 => '',
502: p_base_message => '');
503:
504: -- Mark the Spec, Samples, and Results as VI (Vendor Invalid)
505:
506: UPDATE qc_spec_mst
507: SET migration_status = 'VI'
508: WHERE migration_status IS NULL
509: AND spec_hdr_id in (SELECT spec_hdr_id
510: FROM qc_spec_mst
506: UPDATE qc_spec_mst
507: SET migration_status = 'VI'
508: WHERE migration_status IS NULL
509: AND spec_hdr_id in (SELECT spec_hdr_id
510: FROM qc_spec_mst
511: WHERE qc_spec_id = l_null_vendor_id_spec.qc_spec_id
512: )
513: ;
514:
517: WHERE migration_status IS NULL
518: AND sample_id IN (SELECT sample_id
519: FROM qc_rslt_mst
520: WHERE qc_spec_id in (SELECT qc_spec_id
521: FROM qc_spec_mst
522: WHERE spec_hdr_id in (select
523: spec_hdr_id
524: from
525: qc_spec_mst
521: FROM qc_spec_mst
522: WHERE spec_hdr_id in (select
523: spec_hdr_id
524: from
525: qc_spec_mst
526: where
527: qc_spec_id = l_null_vendor_id_spec.qc_spec_id
528: )
529: )
535: WHERE migration_status IS NULL
536: AND sample_id in (SELECT sample_id
537: FROM qc_rslt_mst
538: WHERE qc_spec_id in (SELECT qc_spec_id
539: FROM qc_spec_mst
540: WHERE spec_hdr_id in (select
541: spec_hdr_id
542: from
543: qc_spec_mst
539: FROM qc_spec_mst
540: WHERE spec_hdr_id in (select
541: spec_hdr_id
542: from
543: qc_spec_mst
544: where
545: qc_spec_id = l_null_vendor_id_spec.qc_spec_id
546: )
547: )
554:
555: l_position :=60;
556: GMA_MIGRATION.gma_insert_message (
557: p_run_id => migration_id,
558: p_table_name => 'QC_SPEC_MST',
559: p_DB_ERROR => '',
560: p_param1 => '',
561: p_param2 => '',
562: p_param3 => '',
662: -- Only spec A used for samples
663: -- Decrease Spec B's from_date
664: -------------------------------------
665: IF (l_data_fix) THEN
666: UPDATE qc_spec_mst
667: SET from_date = l_specs.to_date + 1/86400
668: WHERE qc_spec_id = l_specs.l_b_qc_spec_id;
669: END IF;
670: l_position :=120;
691: -- Only spec B used for samples
692: -- Decrease Spec A's to_date
693: -------------------------------------
694: IF (l_data_fix) THEN
695: UPDATE qc_spec_mst
696: SET to_date = l_specs.from_date - 1/86400
697: WHERE qc_spec_id = l_specs.l_a_qc_spec_id;
698: END IF;
699: l_position :=130;
721: -- Descreasing Spec A's to_date
722: -------------------------------------
723: l_position :=140;
724: IF (l_data_fix) THEN
725: UPDATE qc_spec_mst
726: SET to_date = l_specs.from_date - 1/86400
727: WHERE qc_spec_id = l_specs.l_a_qc_spec_id;
728: END IF;
729:
758: IF (l_a_max_date < l_b_min_date) THEN
759:
760: l_position :=170;
761: IF (l_data_fix) THEN
762: UPDATE qc_spec_mst
763: SET to_date = l_a_max_date
764: WHERE qc_spec_id = l_specs.l_a_qc_spec_id;
765:
766:
763: SET to_date = l_a_max_date
764: WHERE qc_spec_id = l_specs.l_a_qc_spec_id;
765:
766:
767: UPDATE qc_spec_mst
768: SET from_date = l_b_min_date
769: WHERE qc_spec_id = l_specs.l_b_qc_spec_id;
770: END IF;
771:
815: -- cases run...
816: IF (unable_to_resolve OR is_total_overlap) THEN
817: -- IF (l_data_fix) THEN
818: l_position :=200;
819: UPDATE qc_spec_mst
820: SET migration_status = 'OL'
821: WHERE spec_hdr_id in (SELECT spec_hdr_id
822: FROM qc_spec_mst
823: WHERE qc_spec_id in (l_specs.l_a_qc_spec_id,
818: l_position :=200;
819: UPDATE qc_spec_mst
820: SET migration_status = 'OL'
821: WHERE spec_hdr_id in (SELECT spec_hdr_id
822: FROM qc_spec_mst
823: WHERE qc_spec_id in (l_specs.l_a_qc_spec_id,
824: l_specs.l_b_qc_spec_id))
825: AND migration_status IS NULL;
826:
829: SET migration_status = 'OL'
830: WHERE sample_id IN (SELECT sample_id
831: FROM qc_rslt_mst
832: WHERE qc_spec_id in (SELECT qc_spec_id
833: FROM qc_spec_mst
834: WHERE spec_hdr_id in (select
835: spec_hdr_id
836: from
837: qc_spec_mst
833: FROM qc_spec_mst
834: WHERE spec_hdr_id in (select
835: spec_hdr_id
836: from
837: qc_spec_mst
838: where
839: qc_spec_id in
840: (l_specs.l_a_qc_spec_id,
841: l_specs.l_b_qc_spec_id))));
846: SET migration_status = 'OL'
847: WHERE sample_id in (SELECT sample_id
848: FROM qc_rslt_mst
849: WHERE qc_spec_id in (SELECT qc_spec_id
850: FROM qc_spec_mst
851: WHERE spec_hdr_id in (select
852: spec_hdr_id
853: from
854: qc_spec_mst
850: FROM qc_spec_mst
851: WHERE spec_hdr_id in (select
852: spec_hdr_id
853: from
854: qc_spec_mst
855: where
856: qc_spec_id in
857: (l_specs.l_a_qc_spec_id,
858: l_specs.l_b_qc_spec_id))));
877: WHEN OTHERS THEN
878: x_return_status := 'U';
879: GMA_MIGRATION.gma_insert_message (
880: p_run_id => migration_id,
881: p_table_name => 'QC_SPEC_MST',
882: p_DB_ERROR => sqlerrm,
883: p_param1 => '',
884: p_param2 => '',
885: p_param3 => '',