[Home] [Help]
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
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,
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.
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;
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;