[Home] [Help]
22: -- l_base_lang := Get_Base_Language;
23: --
24: -- HISTORY
25: --========================================================================== */
26: /* PURPOSE: Apply fixes to qc_spec_mst for migration to Patch 11.5.1J */
27: /* to fix overlapping spec tests from and to dates */
28: /* when the same test or assay_code */
29: /* occurs in a spec more than once */
30: /* AUTHOR: Brenda Stone OPM Development */
70: CURSOR c_specs IS
71: SELECT a.spec_hdr_id,
72: a.qcassy_typ_id,
73: a.assay_code
74: FROM qc_spec_mst a,
75: qc_spec_mst b
76: WHERE a.migration_status is NULL
77: AND a.spec_hdr_id = b.spec_hdr_id
78: AND a.QC_SPEC_ID <> b.QC_SPEC_ID
71: SELECT a.spec_hdr_id,
72: a.qcassy_typ_id,
73: a.assay_code
74: FROM qc_spec_mst a,
75: qc_spec_mst b
76: WHERE a.migration_status is NULL
77: AND a.spec_hdr_id = b.spec_hdr_id
78: AND a.QC_SPEC_ID <> b.QC_SPEC_ID
79: AND a.QCASSY_TYP_ID = b.QCASSY_TYP_ID
89: s.qc_spec_id,
90: s.assay_code,
91: from_date,
92: to_date
93: FROM qc_spec_mst s
94: WHERE s.spec_hdr_id = l_spec_hdr_id
95: AND (( s.assay_code = l_assay_code)
96: or ( s.qcassy_typ_id = l_qcassy_typ_id ))
97: AND migration_status is NULL
113: and s.sample_id = r.sample_id;
114:
115: CURSOR c_chk_overlaps IS
116: SELECT s.qc_spec_id
117: FROM qc_spec_mst s
118: WHERE (( s.from_date > r_min_rslt_date
119: and s.to_date > o_to_date )
120: OR ( s.from_date < r_min_rslt_date
121: and s.to_date < o_to_date )
134: -- gmd_p_fs_context sets the formula security context
135: --
136: gmd_p_fs_context.set_additional_attr;
137:
138: UPDATE qc_spec_mst
139: SET old_from_date = from_date,
140: old_to_date = to_date
141: WHERE old_from_date is NULL ;
142: COMMIT;
140: old_to_date = to_date
141: WHERE old_from_date is NULL ;
142: COMMIT;
143:
144: UPDATE qc_spec_mst
145: SET to_date = ( to_date - 1/86400 ),
146: from_date = ( from_date - 1/86400 )
147: WHERE old_from_date is NULL ;
148: COMMIT;
148: COMMIT;
149:
150:
151: /* Update Migration_status to 'NM' for delete specs with no results */
152: UPDATE qc_spec_mst s
153: set migration_status = 'NM'
154: where s.delete_mark = 1
155: and s.migration_status is NULL
156: and not exists (
167: set old_qc_spec_id = qc_spec_id ,
168: qc_spec_id = NULL
169: where qc_spec_id = (
170: SELECT r.qc_spec_id
171: from qc_spec_mst s
172: where s.qc_spec_id = r.qc_spec_id
173: and s.qcassy_typ_id <> r.qcassy_typ_id);
174: COMMIT;
175:
244: THEN
245: /* Both dup spec tests have results, set migration_status = 'DR' */
246: /* for both spec tests and corresponding results and the result's */
247: /* sample */
248: UPDATE qc_spec_mst
249: SET migration_status = 'DR'
250: WHERE qc_spec_id = o_qc_spec_id
251: or qc_spec_id = last_qc_spec_id;
252: UPDATE qc_rslt_mst
265: /* The last spec test has results and the current spec test */
266: /* does not have results; Therefore, the last spec test is */
267: /* migrated and the current spec test is not migrated; it's */
268: /* migration_status = 'DN' */
269: UPDATE qc_spec_mst
270: SET migration_status = 'DN'
271: WHERE qc_spec_id = o_qc_spec_id;
272:
273: END IF;
274: /* CLOSE c_results; */
275: ELSE
276: /* The last spec test did not have results; therefore it is safe */
277: /* to migrate the current spec test */
278: UPDATE qc_spec_mst
279: SET migration_status = 'DN'
280: WHERE qc_spec_id = last_qc_spec_id;
281: END IF;
282: ELSE
341: END IF;
342: END IF;
343: IF new_mig_status = 'UM'
344: THEN
345: UPDATE qc_spec_mst
346: SET migration_status = 'UM'
347: WHERE spec_hdr_id = l_spec_hdr_id;
348: UPDATE qc_rslt_mst
349: SET migration_status = 'UM'
352: UPDATE qc_smpl_mst s
353: SET migration_status = 'UM'
354: WHERE s.sample_id IN (
355: select sample_id
356: from qc_spec_mst sp,
357: qc_rslt_mst r
358: where sp.spec_hdr_id = l_spec_hdr_id
359: and r.qc_spec_id = sp.qc_spec_id);
360: commit;
362: commit;
363: new_mig_status := NULL;
364: GMA_MIGRATION.gma_insert_message (
365: p_run_id => p_migration_id,
366: p_table_name => 'QC_SPEC_MST',
367: p_DB_ERROR => '',
368: p_param1 => 'Overlapping spec test dates ',
369: p_param2 => 'And spec tests has results ',
370: p_param3 => 'Migration_status set to UM',
376: p_position => '',
377: p_base_message => '');
378: COMMIT;
379: ELSE
380: UPDATE qc_spec_mst
381: SET to_date = new_to_date
382: WHERE qc_spec_id = last_qc_spec_id;
383: COMMIT;
384: END IF;
403: /* insert into bfs values
404: ('From_date = To_date; o_qc_spec_id = '||
405: o_qc_spec_id );
406: commit; */
407: UPDATE qc_spec_mst
408: SET from_date = to_date
409: WHERE qc_spec_id = o_qc_spec_id;
410: COMMIT;
411: ELSE
414:
415: OPEN c_chk_overlaps;
416: FETCH c_chk_overlaps INTO l_overlap_qc_spec_id;
417: IF c_chk_overlaps%NOTFOUND THEN
418: UPDATE qc_spec_mst
419: SET from_date = r_min_rslt_date
420: WHERE qc_spec_id = o_qc_spec_id;
421: COMMIT;
422: ELSE
420: WHERE qc_spec_id = o_qc_spec_id;
421: COMMIT;
422: ELSE
423: /* Unable to resolve overlapping dates, write msg to log */
424: UPDATE qc_spec_mst
425: SET migration_status = 'UM'
426: WHERE qc_spec_id = o_qc_spec_id;
427: commit;
428: new_mig_status := NULL;
427: commit;
428: new_mig_status := NULL;
429: GMA_MIGRATION.gma_insert_message (
430: p_run_id => p_migration_id,
431: p_table_name => 'QC_SPEC_MST',
432: p_DB_ERROR => '',
433: p_param1 => 'qc_spec_id = '||o_qc_spec_id,
434: p_param2 => '',
435: p_param3 => 'Migration_status set to UM',
477: WHEN OTHERS THEN
478: x_return_status := 'U';
479: GMA_MIGRATION.gma_insert_message (
480: p_run_id => p_migration_id,
481: p_table_name => 'QC_SPEC_MST',
482: p_DB_ERROR => sqlerrm,
483: p_param1 => '',
484: p_param2 => '',
485: p_param3 => '',
1785: -- PROCEDURE:
1786: -- insert_temp_table_rows
1787: --
1788: -- DESCRIPTION:
1789: -- This Global temprary table gmd_qc_spec_mst_gtmp along with
1790: -- gmd_spec_mapping_gtmp is used to improve Specification migration performance.
1791: --
1792: -- PARAMETERS:
1793: -- p_spec_hdr_id - All qc_spec_mst records for spec_hdr_id are inserted in
1789: -- This Global temprary table gmd_qc_spec_mst_gtmp along with
1790: -- gmd_spec_mapping_gtmp is used to improve Specification migration performance.
1791: --
1792: -- PARAMETERS:
1793: -- p_spec_hdr_id - All qc_spec_mst records for spec_hdr_id are inserted in
1794: -- Global temprary table gmd_qc_spec_mst_gtmp for performance.
1795: --
1796: -- SYNOPSIS:
1797: -- insert_temp_table_rows;
1790: -- gmd_spec_mapping_gtmp is used to improve Specification migration performance.
1791: --
1792: -- PARAMETERS:
1793: -- p_spec_hdr_id - All qc_spec_mst records for spec_hdr_id are inserted in
1794: -- Global temprary table gmd_qc_spec_mst_gtmp for performance.
1795: --
1796: -- SYNOPSIS:
1797: -- insert_temp_table_rows;
1798: --
1803: IS
1804:
1805: Begin
1806:
1807: Insert Into gmd_qc_spec_mst_gtmp
1808: (
1809: QC_SPEC_ID,
1810: QCASSY_TYP_ID,
1811: ORGN_CODE,
2009: SPEC_HDR_ID,
2010: OLD_FROM_DATE,
2011: OLD_TO_DATE,
2012: MIGRATION_STATUS
2013: FROM qc_spec_mst
2014: WHERE spec_hdr_id = p_spec_hdr_id;
2015:
2016: END insert_temp_table_rows;
2017:
2047: -- M. Grosser 08-Oct-2002 Set sampling_plan_id to NULL in validity rules
2048: -- C. Nagar 18-Dec-2002 Bug 2714197 - Fetch recipe id, no, and version based on
2049: -- batch id
2050: -- B. Stone 7-Aug-2003 Bug 3088400 - Order spec tests by preference
2051: -- field in qc_spec_mst
2052: -- Bug 3084500 - Print_spec_ind, print_result_ind
2053: -- fields in gmd_spec_tests are set to 'Y' when
2054: -- qc_spec_mst.print_coa_shipped = 1 (yes); value
2055: -- remains Null otherwise.
2050: -- B. Stone 7-Aug-2003 Bug 3088400 - Order spec tests by preference
2051: -- field in qc_spec_mst
2052: -- Bug 3084500 - Print_spec_ind, print_result_ind
2053: -- fields in gmd_spec_tests are set to 'Y' when
2054: -- qc_spec_mst.print_coa_shipped = 1 (yes); value
2055: -- remains Null otherwise.
2056: -- B. Stone 25-Aug-2003 Bug - 3097029; expression error type requires
2057: -- at least one action code specified;
2058: -- if none specified, set to NULL
2075: -- to retrieve the tests for a version.
2076: -- 3) Changed logic for deriving l_version_end_date
2077: -- 4) Removed check-dup logic
2078: --
2079: -- PK Bug 4226263 07-June-2005 Created temporary tables gmd_qc_spec_mst_gtmp
2080: -- and gmd_spec_mapping_gtmp. These tables contain subset of
2081: -- data being migrated . These tables are used for performance
2082: -- Improvement. Varioys cusors are changed to use gmd_qc_spec_mst_gtmp
2083: -- instead of qc_spec_mst and gmd_spec_mapping_gtmp instead of gmd_spec_mapping
2078: --
2079: -- PK Bug 4226263 07-June-2005 Created temporary tables gmd_qc_spec_mst_gtmp
2080: -- and gmd_spec_mapping_gtmp. These tables contain subset of
2081: -- data being migrated . These tables are used for performance
2082: -- Improvement. Varioys cusors are changed to use gmd_qc_spec_mst_gtmp
2083: -- instead of qc_spec_mst and gmd_spec_mapping_gtmp instead of gmd_spec_mapping
2084: -- Suitable code changes are made to insert data into these tables.
2085: -- Uday Phadtare Bug 5025951. Default display_precision and report_precision from
2086: -- user configured profiles. If profile is NULL precision is considered as zero.
2079: -- PK Bug 4226263 07-June-2005 Created temporary tables gmd_qc_spec_mst_gtmp
2080: -- and gmd_spec_mapping_gtmp. These tables contain subset of
2081: -- data being migrated . These tables are used for performance
2082: -- Improvement. Varioys cusors are changed to use gmd_qc_spec_mst_gtmp
2083: -- instead of qc_spec_mst and gmd_spec_mapping_gtmp instead of gmd_spec_mapping
2084: -- Suitable code changes are made to insert data into these tables.
2085: -- Uday Phadtare Bug 5025951. Default display_precision and report_precision from
2086: -- user configured profiles. If profile is NULL precision is considered as zero.
2087: --=========================================================================== */
2134: l_copied_text_code NUMBER;
2135:
2136: -- Cursors c_get_start_and_end, c_get_version_end_date_a, c_get_version_end_date_b, c_get_creation
2137: -- c_get_last_update, c_get_spec_details, (c_get_coa_inds as well ?????) would be based on temporary table
2138: -- gmd_qc_spec_mst_gtmp
2139:
2140: /* Get SYSDATE */
2141: CURSOR c_get_sysdate IS
2142: SELECT sysdate FROM DUAL;
2143:
2144: /* Get the ids of the spec header groupings that have not been migrated */
2145: CURSOR c_get_spec_header IS
2146: SELECT DISTINCT spec_hdr_id, item_id, orgn_code
2147: FROM qc_spec_mst
2148: WHERE migration_status is NULL;
2149:
2150: hdr_rec c_get_spec_header%ROWTYPE;
2151:
2158:
2159: /* Get the earliest start and latest end date */
2160: CURSOR c_get_start_and_end IS
2161: SELECT min(from_date), max(to_date)
2162: FROM gmd_qc_spec_mst_gtmp
2163: WHERE spec_hdr_id = hdr_rec.spec_hdr_id
2164: AND migration_status is NULL;
2165:
2166: /* Get the creation info for the spec version */
2165:
2166: /* Get the creation info for the spec version */
2167: CURSOR c_get_creation IS
2168: SELECT creation_date, created_by
2169: FROM gmd_qc_spec_mst_gtmp
2170: WHERE from_date <= l_start_date and
2171: to_date >= l_version_end_date and
2172: spec_hdr_id = hdr_rec.spec_hdr_id and
2173: migration_status is NULL
2176:
2177: /* Get the last_updated info for the spec version */
2178: CURSOR c_get_last_update IS
2179: SELECT last_update_date, last_updated_by, last_update_login
2180: FROM gmd_qc_spec_mst_gtmp
2181: WHERE from_date <= l_start_date and
2182: to_date >= l_version_end_date and
2183: spec_hdr_id = hdr_rec.spec_hdr_id and
2184: migration_status is NULL
2187:
2188: /* Find the end date of the version (earliest end date in group) */
2189: CURSOR c_get_version_end_date_a IS
2190: SELECT min(to_date)
2191: FROM gmd_qc_spec_mst_gtmp
2192: ----from_date <= l_start_date and
2193: WHERE to_date >= l_start_date
2194: and spec_hdr_id = hdr_rec.spec_hdr_id
2195: and migration_status is NULL;
2195: and migration_status is NULL;
2196:
2197: CURSOR c_get_version_end_date_b IS
2198: SELECT min(from_date) - 1/86400
2199: FROM gmd_qc_spec_mst_gtmp
2200: WHERE from_date > l_start_date
2201: and spec_hdr_id = hdr_rec.spec_hdr_id
2202: and migration_status is NULL;
2203:
2319: attribute28,
2320: attribute29,
2321: attribute30,
2322: attribute_category
2323: FROM gmd_qc_spec_mst_gtmp
2324: WHERE from_date <= l_start_date and
2325: to_date >= l_version_end_date and
2326: spec_hdr_id = hdr_rec.spec_hdr_id and
2327: migration_status is NULL
2401: SELECT
2402: max(print_coa_shipped) as print_coa_shipped,
2403: max(print_coa_invoiced) as print_coa_invoiced,
2404: max(vendor_coa_required) as vendor_coa_required
2405: FROM gmd_qc_spec_mst_gtmp s ,
2406: gmd_spec_mapping_gtmp m
2407: WHERE m.spec_id = l_spec_id
2408: AND s.qc_spec_id = m.qc_spec_id ;
2409: coa_rec c_get_coa_inds%ROWTYPE;
2434: END;
2435:
2436: GMA_MIGRATION.gma_insert_message (
2437: p_run_id => p_migration_id,
2438: p_table_name => 'QC_SPEC_MST',
2439: p_DB_ERROR => '',
2440: p_param1 => '',
2441: p_param2 => '',
2442: p_param3 => '',
2554: /* While there are spec header groupings that have not been migrated */
2555: -- Header Loop
2556: WHILE c_get_spec_header%FOUND LOOP
2557:
2558: -- Insert into temp table here all qc_spec_mst rows for hdr_id
2559: -- Cursors c_get_start_and_end, c_get_version_end_date_a, c_get_version_end_date_b, c_get_creation
2560: -- c_get_last_update, c_get_spec_details, (c_get_coa_inds as well ?????) would be based on temporary table
2561:
2562: insert_temp_table_rows(hdr_rec.spec_hdr_id);
3640: END LOOP; /* Where start_date < end_date */
3641:
3642:
3643: /* Set status of records to migrated */
3644: UPDATE qc_spec_mst
3645: SET migration_status = 'MO'
3646: WHERE spec_hdr_id = hdr_rec.spec_hdr_id
3647: and migration_status is NULL;
3648:
3659:
3660:
3661: GMA_MIGRATION.gma_insert_message (
3662: p_run_id => p_migration_id,
3663: p_table_name => 'QC_SPEC_MST',
3664: p_DB_ERROR => '',
3665: p_param1 => '',
3666: p_param2 => '',
3667: p_param3 => '',
3681: x_return_status := 'U';
3682: /* ROLLBACK TO SAVEPOINT Specification_Group; */
3683: GMA_MIGRATION.gma_insert_message (
3684: p_run_id => p_migration_id,
3685: p_table_name => 'QC_SPEC_MST',
3686: p_DB_ERROR => sqlerrm,
3687: p_param1 => sd.qc_spec_id,
3688: p_param2 => l_spec_id,
3689: p_param3 => sd.qcassy_typ_id,
3696: p_base_message => 'Failed to migrate specifications due to '||sqlerrm);
3697:
3698: GMA_MIGRATION.gma_insert_message (
3699: p_run_id => p_migration_id,
3700: p_table_name => 'QC_SPEC_MST',
3701: p_DB_ERROR => '',
3702: p_param1 => '',
3703: p_param2 => '',
3704: p_param3 => '',