[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select GMD_QC_SPEC_HDR_ID_S.nextval from dual;
select *
from qc_spec_mst
order by orgn_code, whse_code, location, lot_id,
cust_id, order_org_id, ship_to_site_id, vendor_id,
batch_id, formula_id, formulaline_id, routing_id, routingstep_id,
charge, oprn_id, item_id, spec_hdr_id
for update of spec_hdr_id NOWAIT;
/******* Update all 3 main qc tables with QC_REC_TYPE ********/
-- CR we need all the following stmt to update the tables to 'Z' without any
-- condition
-- Bug 3697857; Added Where clause where qc_rec_type is NULL
update qc_spec_mst
set qc_rec_type = 'Z'
where qc_rec_type is NULL
;
update qc_smpl_mst
set qc_rec_type = 'Z'
where qc_rec_type is NULL
;
update qc_rslt_mst
set qc_rec_type = 'Z'
where qc_rec_type is NULL
;
/******* Update spec table with spec_hdr_id ********/
/******* This id associates all the assays ********/
/******* in a spec together. ********/
/*** Should not matter if this is run before or after cust_id migration ***/
/*** removed WHERE clause in cursor GET_SPEC. If this procedure is run ***/
/*** more than once, all rows have to be considered in cases where ***/
/*** an assay was added to a spec - existing assays had hdr id, new ***/
/*** assay did not. (This should not happen, unless a user runs this ***/
/*** procedure the 1st time, then allows users to enter data before ***/
/*** the new versions of the forms are implemented.) ***/
/*** ***/
/*** This procedure should NOT be run after the new forms are implemented, ***/
/*** if users have entered order-specific customer specs. ***/
for each_spec in get_spec LOOP
IF each_spec.spec_hdr_id IS NULL THEN
-- this procedure must be re-runable. Only set spec_hdr_id
-- for rows which do not already have a valid hdr id.
-- dbms_output.put_line ('l ' || l_lot || ' ' || each_spec.lot_id
-- ||' c ' || l_c || ' ' || each_spec.cust_id
-- ||' s ' || seq_id);
update qc_spec_mst
set spec_hdr_id = seq_id
where CURRENT OF get_spec;
SELECT sysdate from dual;
SELECT sysdate - 80000 / 86400 from dual;
SELECT a.spec_hdr_id,
a.qc_spec_id,
a.from_date,
a.to_date
FROM qc_spec_mst a
WHERE migration_status is NULL
AND a.from_date > a.to_date;
SELECT min(a.sample_date) min_date, max(a.sample_date) max_date
FROM qc_smpl_mst a, qc_rslt_mst b
WHERE a.sample_id = b.sample_id
AND b.qc_spec_id = p_qc_spec_id;
SELECT a.spec_hdr_id,
a.qc_spec_id l_a_qc_spec_id,
a.qcassy_typ_id,
a.assay_code,
a.to_date,
b.from_date,
b.to_date l_b_to_date,
b.qc_spec_id l_b_qc_spec_id
FROM qc_spec_mst a,
qc_spec_mst b
WHERE a.migration_status is NULL
AND b.migration_status is NULL
AND a.spec_hdr_id = b.spec_hdr_id
AND a.QC_SPEC_ID <> b.QC_SPEC_ID
AND a.QCASSY_TYP_ID = b.QCASSY_TYP_ID
AND b.from_date <= a.to_date
-- CN
AND b.from_date >= a.from_date
/*GROUP BY a.spec_hdr_id, a.qcassy_typ_id,
a.assay_code, a.to_date,
b.from_date,
b.qc_spec_id*/
order by a.spec_hdr_id,a.qcassy_typ_id;
SELECT max(a.sample_date) max_sample_date, count(*) cnt
FROM qc_smpl_mst a, qc_rslt_mst b
WHERE a.sample_date between p_from_date and p_to_date
AND a.sample_id = b.sample_id
AND b.qc_spec_id = p_a_qc_spec_id;
SELECT min(a.sample_date) min_sample_date, count(*) cnt
FROM qc_smpl_mst a, qc_rslt_mst b
WHERE a.sample_date between p_from_date and p_to_date
AND a.sample_id = b.sample_id
AND b.qc_spec_id = p_b_qc_spec_id;
SELECT sp.qc_spec_id, sp.vendor_id
FROM qc_spec_mst sp, po_vend_mst v
WHERE sp.vendor_id IS NOT NULL
AND sp.vendor_id = v.vendor_id
AND v.of_vendor_id IS NULL
AND sp.migration_status IS NULL;
CURSOR c_deleted_w_results (p_sysdate date) IS
SELECT sp.qc_spec_id dlt_qc_spec_id
FROM qc_spec_mst sp
WHERE sp.delete_mark = 1
AND sp.migration_status is null
and sp.to_date > p_sysdate; -- CR Use the variable for
UPDATE qc_rslt_mst r
SET old_qc_spec_id = qc_spec_id ,
qc_spec_id = NULL
WHERE qc_spec_id = (
SELECT r.qc_spec_id
from qc_spec_mst s
WHERE s.qc_spec_id = r.qc_spec_id
and s.qcassy_typ_id <> r.qcassy_typ_id);
UPDATE qc_spec_mst
SET old_from_date = from_date,
old_to_date = to_date,
to_date = (to_date - 1/86400),
from_date = (from_date+1/86400)
WHERE old_from_date IS NULL;
/* Case 3. Update Migration_status to 'DL' for delete specs with no results
** */
UPDATE qc_spec_mst s
set migration_status = 'DL'
where s.delete_mark = 1
and s.migration_status is NULL
and not exists (
select 1
from qc_rslt_mst r
where s.qc_spec_id = r.qc_spec_id );
/* Case 3.5 Update TO_DATE to today's date - 1 day for deleted spec
-- with results and TO_DATE > sysdate or today's date */
FOR l_dlt_specs IN c_deleted_w_results (l_sysdate) LOOP
l_position :=35;
UPDATE qc_spec_mst
SET to_date = l_22hrs
-- CR get this into a variable
WHERE qc_spec_id = l_dlt_specs.dlt_qc_spec_id;
GMA_MIGRATION.gma_insert_message (
p_run_id => migration_id,
p_table_name => 'QC_SPEC_MST',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => '',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'LESS_FROM_START', -- pal token not mapped
p_message_type => 'LT', -- this is not even used in proc below
p_line_no => '1',
p_position => l_position,
p_base_message => '');
UPDATE qc_spec_mst
SET from_date = l_samples_date.min_date,
to_date = l_samples_date.max_date
WHERE qc_spec_id = l_spec_less_from_date.qc_spec_id;
GMA_MIGRATION.gma_insert_message (
p_run_id => migration_id,
p_table_name => 'QC_SPEC_MST',
p_DB_ERROR => '',
p_param1 => l_spec_less_from_date.qc_spec_id,
p_param2 => l_spec_less_from_date.from_date,
p_param3 => l_spec_less_from_date.to_date,
p_param4 => '',
p_param5 => '',
p_message_token => 'LESS_FROM_RESOLVED',
p_message_type => 'WD',
p_line_no => '1',
p_position => l_position,
p_base_message => '');
UPDATE qc_spec_mst
SET migration_status = 'WD'
WHERE qc_spec_id = l_spec_less_from_date.qc_spec_id;
GMA_MIGRATION.gma_insert_message (
p_run_id => migration_id,
p_table_name => 'QC_SPEC_MST',
p_DB_ERROR => '',
p_param1 => l_spec_less_from_date.qc_spec_id,
p_param2 => l_spec_less_from_date.from_date,
p_param3 => l_spec_less_from_date.to_date,
p_param4 => '',
p_param5 => '',
p_message_token => 'LESS_FROM_UNRESOLVED',
p_message_type => 'WD',
p_line_no => '1',
p_position => l_position,
p_base_message => '');
GMA_MIGRATION.gma_insert_message (
p_run_id => migration_id,
p_table_name => 'QC_SPEC_MST',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => '',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'LESS_FROM_END', -- pal token not mapped
p_message_type => 'WD', -- not even used in base routine
p_line_no => '1',
p_position => l_position,
p_base_message => ''); */
GMA_MIGRATION.gma_insert_message (
p_run_id => migration_id,
p_table_name => 'QC_SPEC_MST',
p_DB_ERROR => '',
p_param1 => l_null_vendor_id_spec.qc_spec_id,
p_param2 => l_null_vendor_id_spec.vendor_id,
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'SPEC_VENDOR_INVALID_NOTRESOLVED',
p_message_type => 'VI',
p_line_no => '1',
p_position => l_position,
p_base_message => '');
UPDATE qc_spec_mst
SET migration_status = 'VI'
WHERE migration_status IS NULL
AND spec_hdr_id in (SELECT spec_hdr_id
FROM qc_spec_mst
WHERE qc_spec_id = l_null_vendor_id_spec.qc_spec_id
)
;
UPDATE qc_smpl_mst
SET migration_status = 'VI'
WHERE migration_status IS NULL
AND sample_id IN (SELECT sample_id
FROM qc_rslt_mst
WHERE qc_spec_id in (SELECT qc_spec_id
FROM qc_spec_mst
WHERE spec_hdr_id in (select
spec_hdr_id
from
qc_spec_mst
where
qc_spec_id = l_null_vendor_id_spec.qc_spec_id
)
)
)
;
UPDATE qc_rslt_mst
SET migration_status = 'VI'
WHERE migration_status IS NULL
AND sample_id in (SELECT sample_id
FROM qc_rslt_mst
WHERE qc_spec_id in (SELECT qc_spec_id
FROM qc_spec_mst
WHERE spec_hdr_id in (select
spec_hdr_id
from
qc_spec_mst
where
qc_spec_id = l_null_vendor_id_spec.qc_spec_id
)
)
)
;
GMA_MIGRATION.gma_insert_message (
p_run_id => migration_id,
p_table_name => 'QC_SPEC_MST',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => '',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'OVERLAP_START', -- pal token not mapped
p_message_type => 'OL', -- not even used in base routine
p_line_no => '1',
p_position => l_position,
p_base_message => ''); */
GMA_MIGRATION.gma_insert_message (
p_run_id => migration_id,
p_table_name => 'QC_SPEC_TEST',
p_DB_ERROR => '',
p_param1 => l_specs.l_a_qc_spec_id,
p_param2 => l_specs.l_b_qc_spec_id,
p_param3 => l_specs.from_date,
p_param4 => l_specs.to_date,
p_param5 => '',
p_message_token => 'TOTAL_OVERLAP',
p_message_type => 'OL',
p_line_no => '1',
p_position => l_position,
p_base_message => 'Qc_spec_ids '||l_specs.l_a_qc_spec_id||' and
'||l_specs.l_b_qc_spec_id||' has totally overlapping dates');
EXIT; --Not required as aggregate func. in select
EXIT; --Not required as aggreagte func. in select
UPDATE qc_spec_mst
SET from_date = l_specs.to_date + 1/86400
WHERE qc_spec_id = l_specs.l_b_qc_spec_id;
GMA_MIGRATION.gma_insert_message (
p_run_id => migration_id,
p_table_name => 'QC_SPEC_TEST',
p_DB_ERROR => '',
p_param1 => l_specs.l_a_qc_spec_id,
p_param2 => l_specs.l_b_qc_spec_id,
p_param3 => l_specs.from_date,
p_param4 => l_specs.to_date,
p_param5 => '',
p_message_token => 'OVERLAP_RESOLVED',
p_message_type => 'OL',
p_line_no => '1',
p_position => l_position,
p_base_message => 'In A not in B');
UPDATE qc_spec_mst
SET to_date = l_specs.from_date - 1/86400
WHERE qc_spec_id = l_specs.l_a_qc_spec_id;
GMA_MIGRATION.gma_insert_message (
p_run_id => migration_id,
p_table_name => 'QC_SPEC_TEST',
p_DB_ERROR => '',
p_param1 => l_specs.l_a_qc_spec_id,
p_param2 => l_specs.l_b_qc_spec_id,
p_param3 => l_specs.from_date,
p_param4 => l_specs.to_date,
p_param5 => '',
p_message_token => 'OVERLAP_RESOLVED',
p_message_type => 'OL',
p_line_no => '1',
p_position => l_position,
p_base_message => 'Not in A in B');
UPDATE qc_spec_mst
SET to_date = l_specs.from_date - 1/86400
WHERE qc_spec_id = l_specs.l_a_qc_spec_id;
GMA_MIGRATION.gma_insert_message (
p_run_id => migration_id,
p_table_name => 'QC_SPEC_TEST',
p_DB_ERROR => '',
p_param1 => l_specs.l_a_qc_spec_id,
p_param2 => l_specs.l_b_qc_spec_id,
p_param3 => l_specs.from_date,
p_param4 => l_specs.to_date,
p_param5 => '',
p_message_token => 'OVERLAP_RESOLVED',
p_message_type => 'OL',
p_line_no => '1',
p_position => l_position,
p_base_message => 'not in A not in B');
UPDATE qc_spec_mst
SET to_date = l_a_max_date
WHERE qc_spec_id = l_specs.l_a_qc_spec_id;
UPDATE qc_spec_mst
SET from_date = l_b_min_date
WHERE qc_spec_id = l_specs.l_b_qc_spec_id;
GMA_MIGRATION.gma_insert_message (
p_run_id => migration_id,
p_table_name => 'QC_SPEC_TEST',
p_DB_ERROR => '',
p_param1 => l_specs.l_a_qc_spec_id,
p_param2 => l_specs.l_b_qc_spec_id,
p_param3 => l_specs.from_date,
p_param4 => l_specs.to_date,
p_param5 => '',
p_message_token => 'OVERLAP_RESOLVED',
p_message_type => 'OL',
p_line_no => '1',
p_position => l_position,
p_base_message => 'In A in B');
GMA_MIGRATION.gma_insert_message (
p_run_id => migration_id,
p_table_name => 'QC_SPEC_TEST',
p_DB_ERROR => '',
p_param1 => l_specs.l_a_qc_spec_id,
p_param2 => l_specs.l_b_qc_spec_id,
p_param3 => l_specs.from_date,
p_param4 => l_specs.to_date,
p_param5 => '',
p_message_token => 'NOT_RESOLVED',
p_message_type => 'OL',
p_line_no => '1',
p_position => l_position,
p_base_message => 'Samples dates are there in qc_spec_ids
'||l_specs.l_a_qc_spec_id||','||l_specs.l_b_qc_spec_id||' for overlapped dates
between '||l_specs.from_date||' and '||l_specs.to_date);
UPDATE qc_spec_mst
SET migration_status = 'OL'
WHERE spec_hdr_id in (SELECT spec_hdr_id
FROM qc_spec_mst
WHERE qc_spec_id in (l_specs.l_a_qc_spec_id,
l_specs.l_b_qc_spec_id))
AND migration_status IS NULL;
UPDATE qc_smpl_mst
SET migration_status = 'OL'
WHERE sample_id IN (SELECT sample_id
FROM qc_rslt_mst
WHERE qc_spec_id in (SELECT qc_spec_id
FROM qc_spec_mst
WHERE spec_hdr_id in (select
spec_hdr_id
from
qc_spec_mst
where
qc_spec_id in
(l_specs.l_a_qc_spec_id,
l_specs.l_b_qc_spec_id))));
UPDATE qc_rslt_mst
SET migration_status = 'OL'
WHERE sample_id in (SELECT sample_id
FROM qc_rslt_mst
WHERE qc_spec_id in (SELECT qc_spec_id
FROM qc_spec_mst
WHERE spec_hdr_id in (select
spec_hdr_id
from
qc_spec_mst
where
qc_spec_id in
(l_specs.l_a_qc_spec_id,
l_specs.l_b_qc_spec_id))));
GMA_MIGRATION.gma_insert_message (
p_run_id => migration_id,
p_table_name => 'QC_SPEC_MST',
p_DB_ERROR => sqlerrm,
p_param1 => '',
p_param2 => '',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_MIGRATION_DB_ERROR',
p_message_type => 'E',
p_line_no => '1',
p_position => l_position,
p_base_message => 'Validation DB ERROR '||sqlerrm);