[Home] [Help]
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
1794: -- Global temprary table gmd_qc_spec_mst_gtmp for performance.
2070: -- tests are displayed in the same order, by
2071: -- assay_code.
2072: -- 2) Changed the Where clause in c_get_coa_inds
2073: -- to access rows for a version from
2074: -- GMD_SPEC_MAPPING table instead of using dates
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: --
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
2084: -- Suitable code changes are made to insert data into these tables.
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: --=========================================================================== */
2394: specification vers.
2395: */
2396: /* Cursor to select coa indicators within a spec version */
2397: -- Bug 3934121; changed the Where clause to access rows for a version
2398: -- from GMD_SPEC_MAPPING table instead of using dates
2399: -- to retrieve the tests for a version.
2400: CURSOR c_get_coa_inds IS
2401: SELECT
2402: max(print_coa_shipped) as print_coa_shipped,
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;
2410:
2956: sd.last_update_login
2957: );
2958:
2959: /* Insert record into mapping table */
2960: INSERT INTO gmd_spec_mapping
2961: (
2962: qc_spec_id,
2963: spec_id,
2964: test_id,
2977: l_start_date,
2978: l_version_end_date
2979: );
2980:
2981: INSERT INTO gmd_spec_mapping_gtmp
2982: (
2983: qc_spec_id,
2984: spec_id,
2985: test_id,
3910: /* Get the spec validity rule from the mapping table */
3911: /* Bug 3376111; Removed qc_rec_type from where clause */
3912: CURSOR c_get_mapping IS
3913: SELECT spec_vr_id, spec_id
3914: FROM gmd_spec_mapping map, qc_rslt_mst rslt
3915: WHERE smpl_rec.sample_date >= map.start_date
3916: and smpl_rec.sample_date <= map.end_date
3917: -- and map.qc_spec_id = NVL(rslt.qc_spec_id,0) --Bug 3486120
3918: and map.qc_spec_id = rslt.qc_spec_id
3929: SELECT 1
3930: FROM qc_rslt_mst r
3931: WHERE r.sample_id = smpl_rec.sample_id;
3932:
3933: /* Bug 3376111; Derive the spec from gmd_spec_mapping with the most
3934: tests found in qc_rslt_mst for the sample */
3935: CURSOR c_get_sm_specs IS
3936: SELECT spec_id, spec_vr_id, count(*) cnt
3937: FROM gmd_spec_mapping sm,
3933: /* Bug 3376111; Derive the spec from gmd_spec_mapping with the most
3934: tests found in qc_rslt_mst for the sample */
3935: CURSOR c_get_sm_specs IS
3936: SELECT spec_id, spec_vr_id, count(*) cnt
3937: FROM gmd_spec_mapping sm,
3938: qc_rslt_mst r
3939: WHERE r.sample_id = smpl_rec.sample_id
3940: and sm.qc_spec_id = r.qc_spec_id
3941: -- B3486120 Removed condition below