[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
349: SET old_qc_spec_id = qc_spec_id ,
350: qc_spec_id = NULL
351: WHERE qc_spec_id = (
352: SELECT r.qc_spec_id
353: from qc_spec_mst s
354: WHERE s.qc_spec_id = r.qc_spec_id
355: and s.qcassy_typ_id <> r.qcassy_typ_id);
356:
357:
366: -- Note: NEVER EVER UPDATE old_from_date, old_to_date, updating it in both the
367: -- modes.
368: */
369:
370: UPDATE qc_spec_mst
371: SET old_from_date = from_date,
372: old_to_date = to_date,
373: to_date = (to_date - 1/86400),
374: from_date = (from_date+1/86400)
377: l_position :=30;
378:
379: /* Case 3. Update Migration_status to 'DL' for delete specs with no results
380: ** */
381: UPDATE qc_spec_mst s
382: set migration_status = 'DL'
383: where s.delete_mark = 1
384: and s.migration_status is NULL
385: and not exists (
393: -- with results and TO_DATE > sysdate or today's date */
394:
395: FOR l_dlt_specs IN c_deleted_w_results (l_sysdate) LOOP
396: l_position :=35;
397: UPDATE qc_spec_mst
398: SET to_date = l_22hrs
399: -- CR get this into a variable
400: WHERE qc_spec_id = l_dlt_specs.dlt_qc_spec_id;
401: END LOOP;
417:
418: /*-- replaced below with GMA_COMMON_LOGGING.gma_migration_central_log to make it not an error but informational
419: GMA_MIGRATION.gma_insert_message (
420: p_run_id => migration_id,
421: p_table_name => 'QC_SPEC_MST',
422: p_DB_ERROR => '',
423: p_param1 => '',
424: p_param2 => '',
425: p_param3 => '',
435: GMA_COMMON_LOGGING.gma_migration_central_log (
436: p_run_id => migration_id,
437: p_log_level => FND_LOG.LEVEL_EVENT, -- = informational ,
438: p_message_token => 'LESS_FROM_START',
439: p_table_name => 'QC_SPEC_MST',
440: p_context => '',
441: p_param1 => NULL,
442: p_param2 => NULL,
443: p_param3 => NULL,
456: FOR l_samples_date IN c_samples_date(l_spec_less_from_date.qc_spec_id) LOOP
457: l_position :=52;
458: IF (l_samples_date.min_date IS NOT NULL) THEN
459: IF (l_data_fix) THEN
460: UPDATE qc_spec_mst
461: SET from_date = l_samples_date.min_date,
462: to_date = l_samples_date.max_date
463: WHERE qc_spec_id = l_spec_less_from_date.qc_spec_id;
464: END IF;
463: WHERE qc_spec_id = l_spec_less_from_date.qc_spec_id;
464: END IF;
465: GMA_MIGRATION.gma_insert_message (
466: p_run_id => migration_id,
467: p_table_name => 'QC_SPEC_MST',
468: p_DB_ERROR => '',
469: p_param1 => l_spec_less_from_date.qc_spec_id,
470: p_param2 => l_spec_less_from_date.from_date,
471: p_param3 => l_spec_less_from_date.to_date,
482: -- Bug 3587546; Changed to only mark the row with the from_date > to_date
483: -- to not migrate, instead of all the spec_hdr_id's qc_spec_id's
484: -- along with the corrsponding samples and results.
485: IF (l_data_fix) THEN
486: UPDATE qc_spec_mst
487: SET migration_status = 'WD'
488: WHERE qc_spec_id = l_spec_less_from_date.qc_spec_id;
489: END IF;
490: GMA_MIGRATION.gma_insert_message (
488: WHERE qc_spec_id = l_spec_less_from_date.qc_spec_id;
489: END IF;
490: GMA_MIGRATION.gma_insert_message (
491: p_run_id => migration_id,
492: p_table_name => 'QC_SPEC_MST',
493: p_DB_ERROR => '',
494: p_param1 => l_spec_less_from_date.qc_spec_id,
495: p_param2 => l_spec_less_from_date.from_date,
496: p_param3 => l_spec_less_from_date.to_date,
510: /*-- replaced below with GMA_COMMON_LOGGING.gma_migration_central_log to make it not an error but informational
511:
512: GMA_MIGRATION.gma_insert_message (
513: p_run_id => migration_id,
514: p_table_name => 'QC_SPEC_MST',
515: p_DB_ERROR => '',
516: p_param1 => '',
517: p_param2 => '',
518: p_param3 => '',
528: GMA_COMMON_LOGGING.gma_migration_central_log (
529: p_run_id => migration_id,
530: p_log_level => FND_LOG.LEVEL_EVENT, -- = informational ,
531: p_message_token => 'LESS_FROM_END',
532: p_table_name => 'QC_SPEC_MST',
533: p_context => '',
534: p_param1 => NULL,
535: p_param2 => NULL,
536: p_param3 => NULL,
553: LOOP
554: -- The spec vendor_id does not have corresponding of_vendor_id
555: GMA_MIGRATION.gma_insert_message (
556: p_run_id => migration_id,
557: p_table_name => 'QC_SPEC_MST',
558: p_DB_ERROR => '',
559: p_param1 => l_null_vendor_id_spec.qc_spec_id,
560: p_param2 => l_null_vendor_id_spec.vendor_id,
561: p_param3 => '',
568: p_base_message => '');
569:
570: -- Mark the Spec, Samples, and Results as VI (Vendor Invalid)
571:
572: UPDATE qc_spec_mst
573: SET migration_status = 'VI'
574: WHERE migration_status IS NULL
575: AND spec_hdr_id in (SELECT spec_hdr_id
576: FROM qc_spec_mst
572: UPDATE qc_spec_mst
573: SET migration_status = 'VI'
574: WHERE migration_status IS NULL
575: AND spec_hdr_id in (SELECT spec_hdr_id
576: FROM qc_spec_mst
577: WHERE qc_spec_id = l_null_vendor_id_spec.qc_spec_id
578: )
579: ;
580:
583: WHERE migration_status IS NULL
584: AND sample_id IN (SELECT sample_id
585: FROM qc_rslt_mst
586: WHERE qc_spec_id in (SELECT qc_spec_id
587: FROM qc_spec_mst
588: WHERE spec_hdr_id in (select
589: spec_hdr_id
590: from
591: qc_spec_mst
587: FROM qc_spec_mst
588: WHERE spec_hdr_id in (select
589: spec_hdr_id
590: from
591: qc_spec_mst
592: where
593: qc_spec_id = l_null_vendor_id_spec.qc_spec_id
594: )
595: )
601: WHERE migration_status IS NULL
602: AND sample_id in (SELECT sample_id
603: FROM qc_rslt_mst
604: WHERE qc_spec_id in (SELECT qc_spec_id
605: FROM qc_spec_mst
606: WHERE spec_hdr_id in (select
607: spec_hdr_id
608: from
609: qc_spec_mst
605: FROM qc_spec_mst
606: WHERE spec_hdr_id in (select
607: spec_hdr_id
608: from
609: qc_spec_mst
610: where
611: qc_spec_id = l_null_vendor_id_spec.qc_spec_id
612: )
613: )
623: /*-- replaced below with GMA_COMMON_LOGGING.gma_migration_central_log to make it not an error but informational
624:
625: GMA_MIGRATION.gma_insert_message (
626: p_run_id => migration_id,
627: p_table_name => 'QC_SPEC_MST',
628: p_DB_ERROR => '',
629: p_param1 => '',
630: p_param2 => '',
631: p_param3 => '',
640: GMA_COMMON_LOGGING.gma_migration_central_log (
641: p_run_id => migration_id,
642: p_log_level => FND_LOG.LEVEL_EVENT, -- = informational ,
643: p_message_token => 'OVERLAP_START',
644: p_table_name => 'QC_SPEC_MST',
645: p_context => '',
646: p_param1 => NULL,
647: p_param2 => NULL,
648: p_param3 => NULL,
747: -- Only spec A used for samples
748: -- Decrease Spec B's from_date
749: -------------------------------------
750: IF (l_data_fix) THEN
751: UPDATE qc_spec_mst
752: SET from_date = l_specs.to_date + 1/86400
753: WHERE qc_spec_id = l_specs.l_b_qc_spec_id;
754: END IF;
755: l_position :=120;
776: -- Only spec B used for samples
777: -- Decrease Spec A's to_date
778: -------------------------------------
779: IF (l_data_fix) THEN
780: UPDATE qc_spec_mst
781: SET to_date = l_specs.from_date - 1/86400
782: WHERE qc_spec_id = l_specs.l_a_qc_spec_id;
783: END IF;
784: l_position :=130;
806: -- Descreasing Spec A's to_date
807: -------------------------------------
808: l_position :=140;
809: IF (l_data_fix) THEN
810: UPDATE qc_spec_mst
811: SET to_date = l_specs.from_date - 1/86400
812: WHERE qc_spec_id = l_specs.l_a_qc_spec_id;
813: END IF;
814:
843: IF (l_a_max_date < l_b_min_date) THEN
844:
845: l_position :=170;
846: IF (l_data_fix) THEN
847: UPDATE qc_spec_mst
848: SET to_date = l_a_max_date
849: WHERE qc_spec_id = l_specs.l_a_qc_spec_id;
850:
851:
848: SET to_date = l_a_max_date
849: WHERE qc_spec_id = l_specs.l_a_qc_spec_id;
850:
851:
852: UPDATE qc_spec_mst
853: SET from_date = l_b_min_date
854: WHERE qc_spec_id = l_specs.l_b_qc_spec_id;
855: END IF;
856:
900: -- cases run...
901: IF (unable_to_resolve OR is_total_overlap) THEN
902: -- IF (l_data_fix) THEN
903: l_position :=200;
904: UPDATE qc_spec_mst
905: SET migration_status = 'OL'
906: WHERE spec_hdr_id in (SELECT spec_hdr_id
907: FROM qc_spec_mst
908: WHERE qc_spec_id in (l_specs.l_a_qc_spec_id,
903: l_position :=200;
904: UPDATE qc_spec_mst
905: SET migration_status = 'OL'
906: WHERE spec_hdr_id in (SELECT spec_hdr_id
907: FROM qc_spec_mst
908: WHERE qc_spec_id in (l_specs.l_a_qc_spec_id,
909: l_specs.l_b_qc_spec_id))
910: AND migration_status IS NULL;
911:
914: SET migration_status = 'OL'
915: WHERE sample_id IN (SELECT sample_id
916: FROM qc_rslt_mst
917: WHERE qc_spec_id in (SELECT qc_spec_id
918: FROM qc_spec_mst
919: WHERE spec_hdr_id in (select
920: spec_hdr_id
921: from
922: qc_spec_mst
918: FROM qc_spec_mst
919: WHERE spec_hdr_id in (select
920: spec_hdr_id
921: from
922: qc_spec_mst
923: where
924: qc_spec_id in
925: (l_specs.l_a_qc_spec_id,
926: l_specs.l_b_qc_spec_id))));
931: SET migration_status = 'OL'
932: WHERE sample_id in (SELECT sample_id
933: FROM qc_rslt_mst
934: WHERE qc_spec_id in (SELECT qc_spec_id
935: FROM qc_spec_mst
936: WHERE spec_hdr_id in (select
937: spec_hdr_id
938: from
939: qc_spec_mst
935: FROM qc_spec_mst
936: WHERE spec_hdr_id in (select
937: spec_hdr_id
938: from
939: qc_spec_mst
940: where
941: qc_spec_id in
942: (l_specs.l_a_qc_spec_id,
943: l_specs.l_b_qc_spec_id))));
980: WHEN OTHERS THEN
981: x_return_status := 'U';
982: GMA_MIGRATION.gma_insert_message (
983: p_run_id => migration_id,
984: p_table_name => 'QC_SPEC_MST',
985: p_DB_ERROR => sqlerrm,
986: p_param1 => '',
987: p_param2 => '',
988: p_param3 => '',