[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT a.spec_hdr_id,
a.qcassy_typ_id,
a.assay_code
FROM qc_spec_mst a,
qc_spec_mst b
WHERE a.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
GROUP BY a.spec_hdr_id, a.qcassy_typ_id,
a.assay_code
order by a.spec_hdr_id,a.qcassy_typ_id,
a.assay_code;
SELECT spec_hdr_id,
s.qc_spec_id,
s.assay_code,
from_date,
to_date
FROM qc_spec_mst s
WHERE s.spec_hdr_id = l_spec_hdr_id
AND (( s.assay_code = l_assay_code)
or ( s.qcassy_typ_id = l_qcassy_typ_id ))
AND migration_status is NULL
ORDER BY from_date ;
SELECT min(s.sample_date),
max(s.sample_date)
FROM qc_rslt_mst r,
qc_smpl_mst s
WHERE r.qc_spec_id = c_qc_spec_id
and s.sample_id = r.sample_id;
SELECT min(s.sample_date)
FROM qc_rslt_mst r,
qc_smpl_mst s
WHERE r.qc_spec_id = o_qc_spec_id
and s.sample_id = r.sample_id;
SELECT s.qc_spec_id
FROM qc_spec_mst s
WHERE (( s.from_date > r_min_rslt_date
and s.to_date > o_to_date )
OR ( s.from_date < r_min_rslt_date
and s.to_date < o_to_date )
OR ( s.from_date > r_min_rslt_date
and s.to_date < o_to_date ))
AND s.spec_hdr_id = l_spec_hdr_id
AND s.qcassy_typ_id = l_qcassy_typ_id
and s.qc_spec_id <> o_qc_spec_id
AND migration_status is NULL
;
UPDATE qc_spec_mst
SET old_from_date = from_date,
old_to_date = to_date
WHERE old_from_date is NULL ;
UPDATE qc_spec_mst
SET to_date = ( to_date - 1/86400 ),
from_date = ( from_date - 1/86400 )
WHERE old_from_date is NULL ;
/* Update Migration_status to 'NM' for delete specs with no results */
UPDATE qc_spec_mst s
set migration_status = 'NM'
where s.delete_mark = 1
and s.migration_status is NULL
and not exists (
select *
from qc_rslt_mst r
where s.qc_spec_id = r.qc_spec_id );
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);
/* INSERT into bfs_msg
values ( 'Dup Dates, qc_spec_id = '||o_qc_spec_id ||
' o_from_date = '|| o_from_date ||
' o_to_date = '||o_to_date );
insert into bfs_msg
values ( 'last_qc_spec_id = '||last_qc_spec_id ||
'r_max_cr_date = '|| r_max_cr_date );
insert into bfs_msg
values ( 'o_qc_spec_id = '||o_qc_spec_id ||
' r_max_cr_date = '|| r_max_cr_date );
UPDATE qc_spec_mst
SET migration_status = 'DR'
WHERE qc_spec_id = o_qc_spec_id
or qc_spec_id = last_qc_spec_id;
UPDATE qc_rslt_mst
SET migration_status = 'DR'
WHERE qc_spec_id = o_qc_spec_id
or qc_spec_id = last_qc_spec_id;
UPDATE qc_smpl_mst
SET migration_status = 'DR'
WHERE sample_id IN (
select sample_id
from qc_rslt_mst
where qc_spec_id = o_qc_spec_id
or qc_spec_id = last_qc_spec_id);
UPDATE qc_spec_mst
SET migration_status = 'DN'
WHERE qc_spec_id = o_qc_spec_id;
UPDATE qc_spec_mst
SET migration_status = 'DN'
WHERE qc_spec_id = last_qc_spec_id;
/* insert into bfs_msg
values ( 'last_qc_spec_id = '||last_qc_spec_id ||
' l_max_cr_date = '|| l_max_cr_date );
/* insert into bfs_msg
values ( 'before: CR_DATE is <> NULL: o_from_date_2hr = '||
to_char(o_from_date_2hr, 'DD-MON-YYYY HH24:MI:SS')||
' l_max_cr_date = '||
to_char( l_max_cr_date, 'DD-MON-YYYY HH24:MI:SS') );
/* insert into bfs_msg
values ( 'CR_DATE is <> NULL: o_from_date_2hr = '||
o_from_date_2hr ||
' l_max_cr_date = '|| l_max_cr_date );
/* insert into bfs_msg
values ( 'o_from_date_2hr = '||o_from_date_2hr ||
' l_max_cr_date = '|| l_max_cr_date );
UPDATE qc_spec_mst
SET migration_status = 'UM'
WHERE spec_hdr_id = l_spec_hdr_id;
UPDATE qc_rslt_mst
SET migration_status = 'UM'
WHERE qc_spec_id = o_qc_spec_id
or qc_spec_id = last_qc_spec_id;
UPDATE qc_smpl_mst s
SET migration_status = 'UM'
WHERE s.sample_id IN (
select sample_id
from qc_spec_mst sp,
qc_rslt_mst r
where sp.spec_hdr_id = l_spec_hdr_id
and r.qc_spec_id = sp.qc_spec_id);
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_SPEC_MST',
p_DB_ERROR => '',
p_param1 => 'Overlapping spec test dates ',
p_param2 => 'And spec tests has results ',
p_param3 => 'Migration_status set to UM',
p_param4 => 'Following qc_spec_id = '||o_qc_spec_id,
p_param5 => '',
p_message_token => 'Unable to migrate spec test'||last_qc_spec_id,
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
UPDATE qc_spec_mst
SET to_date = new_to_date
WHERE qc_spec_id = last_qc_spec_id;
/* insert into bfs values
( ' qc_spec_id = '||o_qc_spec_id ||'; o_from_date = '||
/* insert into bfs values
( 'r_min_rslt_date = '|| r_min_rslt_date );
/* If results are not found then the FROM_DATE is updated to */
/* the TO_DATE to eliminate the overlap of dates */
CLOSE c_rslt_date;
/* insert into bfs values
('From_date = To_date; o_qc_spec_id = '||
UPDATE qc_spec_mst
SET from_date = to_date
WHERE qc_spec_id = o_qc_spec_id;
/* Results are found and the FROM_DATE is updated to the */
/* earliest RESULT_DATE */
OPEN c_chk_overlaps;
UPDATE qc_spec_mst
SET from_date = r_min_rslt_date
WHERE qc_spec_id = o_qc_spec_id;
UPDATE qc_spec_mst
SET migration_status = 'UM'
WHERE qc_spec_id = o_qc_spec_id;
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_SPEC_MST',
p_DB_ERROR => '',
p_param1 => 'qc_spec_id = '||o_qc_spec_id,
p_param2 => '',
p_param3 => 'Migration_status set to UM',
p_param4 => 'Overlapping dates with qc_spec_id',
p_param5 => l_overlap_qc_spec_id,
p_message_token => 'Unable to migrate spec test',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
GMA_MIGRATION.gma_insert_message (
p_run_id => p_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 => '',
p_base_message => 'Failed to complete spec test dates chk '||sqlerrm);
SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B';
INSERT INTO gmd_test_classes_b
(
test_class,
delete_mark,
text_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
)
SELECT
assay_class,
delete_mark,
text_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
'',
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
FROM gmd_qc_assay_class
WHERE decode(migration_status,NULL,'NM') <> 'MO';
INSERT INTO gmd_test_classes_tl
(
test_class,
language,
test_class_desc,
source_lang,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
SELECT
assay_class,
l_base_lang,
assay_class_desc,
l_base_lang,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
FROM gmd_qc_assay_class
WHERE decode(migration_status,NULL,'NM') <> 'MO';
/* Updated record status to migrated */
UPDATE gmd_qc_assay_class
SET migration_status = 'MO';
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'GMD_QC_ASSAY_CLASS',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => l_rec_count,
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS_RW',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'GMD_QC_ASSAY_CLASS',
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 => '',
p_base_message => 'Failed to migrate assay classes due to '||sqlerrm);
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'GMD_QC_ASSAY_CLASS',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => '',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
INSERT INTO gmd_actions_b
(
action_code,
action_interval,
delete_mark,
text_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
)
SELECT
action_code,
action_interval,
delete_mark,
text_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
FROM qc_actn_mst_bak
WHERE decode(migration_status,NULL,'NM') <> 'MO';
/* Insert action code description in translation table */
INSERT INTO gmd_actions_tl
(
action_code,
language,
action_desc,
source_lang,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
SELECT
action_code,
l_base_lang,
action_desc,
l_base_lang,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
FROM qc_actn_mst_bak
WHERE decode(migration_status,NULL,'NM') <> 'MO';
/* Updated record status to migrated */
UPDATE qc_actn_mst_bak
SET migration_status = 'MO';
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_ACTN_MST_BAK',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => l_rec_count,
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS_RW',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '') ;
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_ACTN_MST_BAK',
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 => '',
p_base_message => 'Failed to migrate action codes due to '||sqlerrm);
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_ACTN_MST',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => '',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
INSERT INTO gmd_hold_reasons_b
(
qchold_res_code,
delete_mark,
text_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
)
SELECT
qchold_res_code,
delete_mark,
text_code,
creation_date,
created_by,
SYSDATE,
last_updated_by,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
FROM qc_hres_mst_bak
WHERE decode(migration_status,NULL,'NM') <> 'MO';
INSERT INTO gmd_hold_reasons_tl
(
qchold_res_code,
language,
qchold_res_desc,
source_lang,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
SELECT
qchold_res_code,
l_base_lang,
qchold_res_desc,
l_base_lang,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
FROM qc_hres_mst_bak
WHERE decode(migration_status,NULL,'NM') <> 'MO';
/* Updated record status to migrated */
UPDATE qc_hres_mst_bak
SET migration_status = 'MO';
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_HRES_MST_BAK',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => l_rec_count,
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS_RW',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_HRES_MST_BAK',
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 => '',
p_base_message => 'Failed to migrate hold reasons due to '||sqlerrm);
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_HRES_MST_BAK',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => '',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
INSERT INTO gmd_qc_tests_b
(
test_id,
test_code,
test_method_id,
test_type,
test_unit,
test_oprn_id,
test_oprn_line_id,
test_provider_code,
test_class,
min_value_num,
max_value_num,
below_spec_min,
above_spec_max,
above_spec_min,
below_spec_max,
exp_error_type,
below_min_action_code,
above_max_action_code,
above_min_action_code,
below_max_action_code,
expression,
display_precision,
report_precision,
priority,
delete_mark,
text_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
)
SELECT
qcassy_typ_id,
orgn_code||assay_code,
0,
decode(assay_type,0,'U',1,'N',2,'V',3,'T',4,'L'),
qcunit_code,
test_oprn_id,
test_oprn_line_id,
test_provider_code,
assay_class,
min_valid,
max_valid,
outside_spec_min,
outside_spec_max,
inside_spec_min,
inside_spec_max,
decode(error_val_type,'NUM','N','PCT','P',NULL),
outside_min_action_code,
outside_max_action_code,
inside_min_action_code,
inside_max_action_code,
NULL,
decode(assay_type,1,G_display_precision,4,G_display_precision,NULL), --Bug 5025951
decode(assay_type,1,G_report_precision,4, G_report_precision, NULL), --Bug 5025951
'5N',
delete_mark,
text_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
FROM gmd_tests_b
WHERE decode(migration_status,NULL,'NM') <> 'MO';
UPDATE gmd_qc_tests_b
set exp_error_type = NULL
where below_min_action_code is null
and above_max_action_code is null
and above_min_action_code is null
and below_max_action_code is null
and exp_error_type is not null;
/* Updated record status to migrated */
UPDATE gmd_tests_b
SET migration_status = 'MO';
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'GMD_TESTS_B',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => l_rec_count,
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS_RW',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'GMD_TESTS_B',
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 => '',
p_base_message => 'Failed to migrate tests base due to '||sqlerrm);
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'GMD_TESTS_B',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => '',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
INSERT INTO gmd_qc_tests_tl
(
test_id,
language,
test_desc,
source_lang,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
SELECT
qcassy_typ_id,
language,
assay_desc,
source_lang,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
FROM gmd_tests_tl
WHERE decode(migration_status,NULL,'NM') <> 'MO';
/* Updated record status to migrated */
UPDATE gmd_tests_tl
SET migration_status = 'MO';
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'GMD_TESTS_TL',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => l_rec_count,
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS_RW',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'GMD_TESTS_TL',
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 => '',
p_base_message => 'Failed to migrate tests translated due to '||sqlerrm);
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'GMD_TESTS_TL',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => '',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
INSERT INTO gmd_qc_test_values_b
(
test_value_id,
test_id,
value_char,
min_num,
max_num,
text_range_seq,
expression_ref_test_id,
text_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
)
SELECT
qcassy_val_id,
qcassy_typ_id,
assay_value,
value_num_min,
value_num_max,
assay_value_range_order,
'',
text_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
FROM gmd_test_values_b
WHERE decode(migration_status,NULL,'NM') <> 'MO';
/* Updated record status to migrated */
UPDATE gmd_test_values_b
SET migration_status = 'MO';
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'GMD_TEST_VALUES_B',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => l_rec_count,
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS_RW',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'GMD_TEST_VALUES_B',
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 => '',
p_base_message => 'Failed to migrate test values base due to '||sqlerrm);
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'GMD_TEST_VALUES_B',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => '',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
INSERT INTO gmd_qc_test_values_tl
(
test_value_id,
language,
test_value_desc,
display_label_numeric_range,
source_lang,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
SELECT
t.qcassy_val_id,
t.language,
t.value_desc,
DECODE(b.value_desc,NULL,b.assay_value,NULL),
t.source_lang,
t.creation_date,
t.created_by,
t.last_update_date,
t.last_updated_by,
t.last_update_login
FROM gmd_test_values_tl t,
gmd_test_values_b b
WHERE t.qcassy_val_id = b.qcassy_val_id AND
decode(t.migration_status,NULL,'NM') <> 'MO';
/* Updated record status to migrated */
UPDATE gmd_test_values_tl
SET migration_status = 'MO';
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'GMD_TEST_VALUES_TL',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => l_rec_count,
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS_RW',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'GMD_TEST_VALUES_TL',
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 => '',
p_base_message => 'Failed to migrate test values translated due to '||sqlerrm);
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'GMD_TEST_VALUES_TL',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => '',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
PROCEDURE insert_temp_table_rows(p_spec_hdr_id IN NUMBER)
IS
Begin
Insert Into gmd_qc_spec_mst_gtmp
(
QC_SPEC_ID,
QCASSY_TYP_ID,
ORGN_CODE,
ITEM_ID,
LOT_ID,
WHSE_CODE,
LOCATION,
FORMULA_ID,
FORMULALINE_ID,
ROUTING_ID,
ROUTINGSTEP_ID,
OPRN_ID,
DOC_TYPE,
DOC_ID,
DOCLINE_ID,
CUST_ID,
CUST_SPECIFICATION,
CUST_CERTIFICATION,
VENDOR_ID,
VENDOR_SPECIFICATION,
VENDOR_CERTIFICATION,
BATCH_ID,
ASSAY_CODE,
TEXT_SPEC,
TARGET_SPEC,
MIN_SPEC,
MAX_SPEC,
QCUNIT_CODE,
FROM_DATE,
TO_DATE,
OUTACTION_CODE,
OUTACTION_INTERVAL,
PREFERENCE,
PRINT_COA_SHIPPED,
PRINT_COA_INVOICED,
VENDOR_COA_REQUIRED,
TEST_OPRN_ID,
TEST_OPRN_LINE_ID,
TEST_PROVIDER_CODE,
DELETE_MARK,
TEXT_CODE,
TRANS_CNT,
CREATION_DATE,
LAST_UPDATE_LOGIN,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
ROUTINGSTEP_NO,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
ATTRIBUTE21,
ATTRIBUTE22,
ATTRIBUTE23,
ATTRIBUTE24,
ATTRIBUTE25,
ATTRIBUTE26,
ATTRIBUTE27,
ATTRIBUTE28,
ATTRIBUTE29,
ATTRIBUTE30,
ATTRIBUTE_CATEGORY,
OPM_CUST_ID,
CHARGE,
OUTSIDE_SPEC_MIN,
INSIDE_SPEC_MIN,
INSIDE_SPEC_MAX,
OUTSIDE_SPEC_MAX,
ERROR_VAL_TYPE,
OUTSIDE_MIN_ACTION_CODE,
INSIDE_MIN_ACTION_CODE,
INSIDE_MAX_ACTION_CODE,
OUTSIDE_MAX_ACTION_CODE,
MIN_CHAR,
MAX_CHAR,
ORDER_HEADER_ID,
ORDER_LINE_NO,
ORDER_ORG_ID,
QC_REC_TYPE,
SHIP_TO_SITE_ID,
SPEC_HDR_ID,
OLD_FROM_DATE,
OLD_TO_DATE,
MIGRATION_STATUS
)
SELECT
QC_SPEC_ID,
QCASSY_TYP_ID,
ORGN_CODE,
ITEM_ID,
LOT_ID,
WHSE_CODE,
LOCATION,
FORMULA_ID,
FORMULALINE_ID,
ROUTING_ID,
ROUTINGSTEP_ID,
OPRN_ID,
DOC_TYPE,
DOC_ID,
DOCLINE_ID,
CUST_ID,
CUST_SPECIFICATION,
CUST_CERTIFICATION,
VENDOR_ID,
VENDOR_SPECIFICATION,
VENDOR_CERTIFICATION,
BATCH_ID,
ASSAY_CODE,
TEXT_SPEC,
TARGET_SPEC,
MIN_SPEC,
MAX_SPEC,
QCUNIT_CODE,
FROM_DATE,
TO_DATE,
OUTACTION_CODE,
OUTACTION_INTERVAL,
PREFERENCE,
PRINT_COA_SHIPPED,
PRINT_COA_INVOICED,
VENDOR_COA_REQUIRED,
TEST_OPRN_ID,
TEST_OPRN_LINE_ID,
TEST_PROVIDER_CODE,
DELETE_MARK,
TEXT_CODE,
TRANS_CNT,
CREATION_DATE,
LAST_UPDATE_LOGIN,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
ROUTINGSTEP_NO,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
ATTRIBUTE21,
ATTRIBUTE22,
ATTRIBUTE23,
ATTRIBUTE24,
ATTRIBUTE25,
ATTRIBUTE26,
ATTRIBUTE27,
ATTRIBUTE28,
ATTRIBUTE29,
ATTRIBUTE30,
ATTRIBUTE_CATEGORY,
OPM_CUST_ID,
CHARGE,
OUTSIDE_SPEC_MIN,
INSIDE_SPEC_MIN,
INSIDE_SPEC_MAX,
OUTSIDE_SPEC_MAX,
ERROR_VAL_TYPE,
OUTSIDE_MIN_ACTION_CODE,
INSIDE_MIN_ACTION_CODE,
INSIDE_MAX_ACTION_CODE,
OUTSIDE_MAX_ACTION_CODE,
MIN_CHAR,
MAX_CHAR,
ORDER_HEADER_ID,
ORDER_LINE_NO,
ORDER_ORG_ID,
QC_REC_TYPE,
SHIP_TO_SITE_ID,
SPEC_HDR_ID,
OLD_FROM_DATE,
OLD_TO_DATE,
MIGRATION_STATUS
FROM qc_spec_mst
WHERE spec_hdr_id = p_spec_hdr_id;
END insert_temp_table_rows;
SELECT sysdate FROM DUAL;
SELECT DISTINCT spec_hdr_id, item_id, orgn_code
FROM qc_spec_mst
WHERE migration_status is NULL;
/* Select the item no associated to spec */
CURSOR c_get_item_no IS
SELECT item_no, lot_ctl
FROM ic_item_mst
WHERE item_id = hdr_rec.item_id;
SELECT min(from_date), max(to_date)
FROM gmd_qc_spec_mst_gtmp
WHERE spec_hdr_id = hdr_rec.spec_hdr_id
AND migration_status is NULL;
SELECT creation_date, created_by
FROM gmd_qc_spec_mst_gtmp
WHERE from_date <= l_start_date and
to_date >= l_version_end_date and
spec_hdr_id = hdr_rec.spec_hdr_id and
migration_status is NULL
ORDER BY creation_date;
/* Get the last_updated info for the spec version */
CURSOR c_get_last_update IS
SELECT last_update_date, last_updated_by, last_update_login
FROM gmd_qc_spec_mst_gtmp
WHERE from_date <= l_start_date and
to_date >= l_version_end_date and
spec_hdr_id = hdr_rec.spec_hdr_id and
migration_status is NULL
ORDER BY last_update_date desc;
update_rec c_get_last_update%ROWTYPE;
SELECT min(to_date)
FROM gmd_qc_spec_mst_gtmp
----from_date <= l_start_date and
WHERE to_date >= l_start_date
and spec_hdr_id = hdr_rec.spec_hdr_id
and migration_status is NULL;
SELECT min(from_date) - 1/86400
FROM gmd_qc_spec_mst_gtmp
WHERE from_date > l_start_date
and spec_hdr_id = hdr_rec.spec_hdr_id
and migration_status is NULL;
SELECT gmd_qc_spec_id_s.nextval
FROM SYS.DUAL;
SELECT gmd_qc_spec_vr_id_s.nextval
FROM SYS.DUAL;
/* Cursor to select detail records associated with a spec version */
/* Bug 3241005; Cursor c_get_spec_details is modified to retrieve spec tests
SELECT qc_spec_id,
qcassy_typ_id,
orgn_code,
item_id,
lot_id,
whse_code,
location,
formula_id,
formulaline_id,
routing_id,
routingstep_id,
routingstep_no,
oprn_id,
doc_type,
doc_id,
docline_id,
cust_id,
cust_specification,
cust_certification,
vendor_id,
vendor_specification,
vendor_certification,
batch_id,
text_spec,
target_spec,
min_spec,
max_spec,
qcunit_code,
from_date,
to_date,
outaction_code,
outaction_interval,
print_coa_shipped,
print_coa_invoiced,
vendor_coa_required,
test_oprn_id,
test_oprn_line_id,
test_provider_code,
charge,
min_char,
max_char,
outside_spec_min,
outside_spec_max,
inside_spec_min,
inside_spec_max,
error_val_type,
outside_min_action_code,
outside_max_action_code,
inside_min_action_code,
inside_max_action_code,
order_header_id,
order_line_no,
order_org_id,
qc_rec_type,
ship_to_site_id,
delete_mark,
text_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
FROM gmd_qc_spec_mst_gtmp
WHERE from_date <= l_start_date and
to_date >= l_version_end_date and
spec_hdr_id = hdr_rec.spec_hdr_id and
migration_status is NULL
ORDER BY assay_code;
SELECT lot_no, sublot_no
FROM ic_lots_mst
WHERE lot_id = v_lot_id;
SELECT r.recipe_id, r.recipe_no, r.recipe_version
FROM gmd_recipes r,
gmd_recipe_validity_rules feff,
gme_batch_header bh
WHERE bh.batch_id = v_batch_id
AND bh.recipe_validity_rule_id = feff.recipe_validity_rule_id
AND feff.recipe_id = r.recipe_id;
SELECT routing_no, routing_vers
FROM gmd_routings_b
WHERE routing_id = v_routing_id;
SELECT formula_no, formula_vers
FROM fm_form_mst_b
WHERE formula_id = v_formula_id;
SELECT oprn_no, oprn_vers
FROM gmd_operations_b
WHERE oprn_id = v_oprn_id;
/* Cursor to select coa indicators within a spec version */
-- Bug 3934121; changed the Where clause to access rows for a version
SELECT
max(print_coa_shipped) as print_coa_shipped,
max(print_coa_invoiced) as print_coa_invoiced,
max(vendor_coa_required) as vendor_coa_required
FROM gmd_qc_spec_mst_gtmp s ,
gmd_spec_mapping_gtmp m
WHERE m.spec_id = l_spec_id
AND s.qc_spec_id = m.qc_spec_id ;
SELECT 'K+'
from all_tab_columns
where table_name='GMD_SPECIFICATIONS_B'
and column_name='SPEC_TYPE'
and owner = l_gmd;
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_SPEC_MST',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => '',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
l_sql_stmt := 'INSERT INTO gmd_specifications_b'
|| '('
|| 'spec_id,'
|| 'spec_name,'
|| 'spec_vers,'
|| 'item_id,'
|| 'spec_status,'
|| 'owner_orgn_code,'
|| 'owner_id,'
|| 'delete_mark,'
|| 'creation_date,'
|| 'created_by,'
|| 'last_update_date,'
|| 'last_updated_by,'
|| 'last_update_login'
|| ')'
|| 'VALUES'
|| '('
|| ':l_spec_id,'
|| ':l_spec_name,'
|| ':l_spec_version,'
|| ':item_id,'
|| ':l_spec_status,'
|| ':l_owner_orgn_code,'
|| ':last_updated_by,'
|| ':delete_mark,'
|| ':creation_date,'
|| ':created_by,'
|| ':last_update_date,'
|| ':last_updated_by2,'
|| ':last_update_login'
|| ')'
;
l_sql_stmt := 'INSERT INTO gmd_specifications_b'
|| '('
|| 'spec_id,'
|| 'spec_name,'
|| 'spec_vers,'
|| 'item_id,'
|| 'spec_status,'
|| 'owner_orgn_code,'
|| 'owner_id,'
|| 'delete_mark,'
|| 'creation_date,'
|| 'created_by,'
|| 'last_update_date,'
|| 'last_updated_by,'
|| 'last_update_login,'
|| 'spec_type'
|| ')'
|| 'VALUES'
|| '('
|| ':l_spec_id,'
|| ':l_spec_name,'
|| ':l_spec_version,'
|| ':item_id,'
|| ':l_spec_status,'
|| ':l_owner_orgn_code,'
|| ':last_updated_by,'
|| ':delete_mark,'
|| ':creation_date,'
|| ':created_by,'
|| ':last_update_date,'
|| ':last_updated_by,'
|| ':last_update_login,'
|| ':spec_type'
|| ')'
;
END IF; /* Insert into SPEC_B */
insert_temp_table_rows(hdr_rec.spec_hdr_id);
/* Select the item no associated to spec */
OPEN c_get_item_no;
/* Check for duplicate tests selected for the spec */
l_spec_hdr_id := hdr_rec.spec_hdr_id;
/* Get the earliest start and latest end date as well as creation and last update date */
OPEN c_get_start_and_end;
/* Select the creation info for the spec version */
OPEN c_get_creation;
/* Select the update info for the spec version */
OPEN c_get_last_update;
FETCH c_get_last_update into update_rec;
CLOSE c_get_last_update;
l_owner_orgn_code := TRIM(FND_PROFILE.value_specific('GEMMS_DEFAULT_ORGN',update_rec.last_updated_by));
update_rec.last_updated_by,
0,
create_rec.creation_date,
create_rec.created_by,
update_rec.last_update_date,
update_rec.last_updated_by,
update_rec.last_update_login;
update_rec.last_updated_by,
0,
create_rec.creation_date,
create_rec.created_by,
update_rec.last_update_date,
update_rec.last_updated_by,
update_rec.last_update_login,
'I';
END IF; /* Insert into SPEC_B */
INSERT INTO gmd_specifications_tl
(
spec_id,
language,
spec_desc,
source_lang,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES
(
l_spec_id,
l_base_lang,
l_spec_name,
l_base_lang,
create_rec.creation_date,
create_rec.created_by,
update_rec.last_update_date,
update_rec.last_updated_by,
update_rec.last_update_login
);
/* Insert record into new spec detail table */
-- Bug 3588513; Changed decode for PRINT_SPEC_IND and PRINT_RESULT_IND to include
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'GMD_SPEC_TESTS_B',
p_DB_ERROR => '',
p_param1 => l_spec_id,
p_param2 => sd.qcassy_typ_id,
p_param3 => sd.text_code,
p_param4 => '',
p_param5 => '',
p_message_token => 'TEXT_CODE_NOT_COPIED',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
INSERT INTO gmd_spec_tests_b
(
spec_id,
test_id,
test_method_id,
seq,
test_qty,
test_uom,
target_value_char,
target_value_num,
min_value_num,
max_value_num,
min_value_char,
max_value_char,
test_replicate,
below_spec_min,
above_spec_max,
above_spec_min,
below_spec_max,
exp_error_type,
below_min_action_code,
above_max_action_code,
above_min_action_code,
below_max_action_code,
out_of_spec_action,
use_to_control_step,
check_result_interval,
optional_ind,
display_precision,
report_precision,
test_priority,
retest_lot_expiry_ind,
text_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category,
print_spec_ind,
print_result_ind
)
VALUES
(
l_spec_id,
sd.qcassy_typ_id,
'0',
l_test_seq,
NULL,
NULL,
sd.text_spec,
sd.target_spec,
sd.min_spec,
sd.max_spec,
sd.min_char,
sd.max_char,
'1',
sd.outside_spec_min,
sd.outside_spec_max,
sd.inside_spec_min,
sd.inside_spec_max,
decode(sd.error_val_type,NULL,
decode(sd.outside_min_action_code,NULL,
decode( sd.outside_max_action_code,NULL,
decode(sd.inside_min_action_code,NULL,
decode(sd.inside_max_action_code,NULL,NULL,sd.error_val_type),
sd.error_val_type),sd.error_val_type),sd.error_val_type ),
'PCT','P','NUM','N',NULL),
-- DECODE(sd.error_val_type,'PCT','P','NUM','N',NULL),
sd.outside_min_action_code,
sd.outside_max_action_code,
sd.inside_min_action_code,
sd.inside_max_action_code,
sd.outaction_code,
'Y',
sd.outaction_interval,
'',
decode(sd.text_spec,NULL,G_display_precision,NULL), --Bug 5025951
decode(sd.text_spec,NULL,G_report_precision, NULL), --Bug 5025951
'5N',
l_retest_lot_exp_ind,
sd.text_code,
sd.creation_date,
sd.created_by,
sd.last_update_date,
sd.last_updated_by,
sd.last_update_login,
sd.attribute1,
sd.attribute2,
sd.attribute3,
sd.attribute4,
sd.attribute5,
sd.attribute6,
sd.attribute7,
sd.attribute8,
sd.attribute9,
sd.attribute10,
sd.attribute11,
sd.attribute12,
sd.attribute13,
sd.attribute14,
sd.attribute15,
sd.attribute16,
sd.attribute17,
sd.attribute18,
sd.attribute19,
sd.attribute20,
sd.attribute21,
sd.attribute22,
sd.attribute23,
sd.attribute24,
sd.attribute25,
sd.attribute26,
sd.attribute27,
sd.attribute28,
sd.attribute29,
sd.attribute30,
sd.attribute_category,
DECODE(sd.print_coa_shipped,0,
DECODE(sd.print_coa_invoiced,0,NULL,1,'Y',NULL),1,'Y',NULL),
DECODE(sd.print_coa_shipped,0,
DECODE(sd.print_coa_invoiced,0,NULL,1,'Y',NULL),1,'Y',NULL)
);
/* Insert dummy record into gmd_spec_tests_tl table
to ensure that the view will work
*/
INSERT INTO gmd_spec_tests_tl
(
spec_id,
test_id,
language,
test_display,
source_lang,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
l_spec_id,
sd.qcassy_typ_id,
l_base_lang,
'',
l_base_lang,
sd.creation_date,
sd.created_by,
sd.last_updated_by,
sd.last_update_date,
sd.last_update_login
);
/* Insert record into mapping table */
INSERT INTO gmd_spec_mapping
(
qc_spec_id,
spec_id,
test_id,
qc_rec_type,
spec_vr_id,
start_date,
end_date
)
VALUES
(
sd.qc_spec_id,
l_spec_id,
sd.qcassy_typ_id,
sd.qc_rec_type,
l_spec_vr_id,
l_start_date,
l_version_end_date
);
INSERT INTO gmd_spec_mapping_gtmp
(
qc_spec_id,
spec_id,
test_id,
qc_rec_type,
spec_vr_id,
start_date,
end_date
)
VALUES
(
sd.qc_spec_id,
l_spec_id,
sd.qcassy_typ_id,
sd.qc_rec_type,
l_spec_vr_id,
l_start_date,
l_version_end_date
);
END LOOP; /* Inserting detail records for spec version */
/* UPDATE gmd_spec_tests_b
set exp_error_type = NULL
where below_min_action_code is null
and above_max_action_code is null
and above_min_action_code is null
and below_max_action_code is null
and exp_error_type is not null; */
/* Select coa indicators within a spec version */
OPEN c_get_coa_inds;
INSERT INTO gmd_wip_spec_vrs
(
spec_vr_id,
spec_id,
orgn_code,
sampling_plan_id,
batch_id,
recipe_id,
recipe_no,
recipe_version,
formula_id,
formulaline_id,
formula_no,
formula_vers,
routing_id,
routing_no,
routing_vers,
oprn_id,
oprn_no,
oprn_vers,
step_id,
step_no,
charge,
spec_vr_status,
lot_optional_on_sample,
start_date,
end_date,
sample_inv_trans_ind,
control_lot_attrib_ind,
out_of_spec_lot_status,
in_spec_lot_status,
control_batch_step_ind,
coa_type,
coa_at_ship_ind,
coa_at_invoice_ind,
coa_req_from_supl_ind,
text_code,
delete_mark,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
)
VALUES
(
l_spec_vr_id,
l_spec_id,
sd.orgn_code,
'',
sd.batch_id,
l_recipe_id,
l_recipe_no,
l_recipe_version,
sd.formula_id,
sd.formulaline_id,
l_formula_no,
l_formula_vers,
sd.routing_id,
l_routing_no,
l_routing_vers,
sd.oprn_id,
l_oprn_no,
l_oprn_vers,
sd.routingstep_id,
sd.routingstep_no,
sd.charge,
l_spec_status,
'',
l_start_date,
l_version_end_date,
'',
'',
'',
'',
'',
'',
DECODE(coa_rec.print_coa_shipped,0,NULL,1,'Y',NULL),
DECODE(coa_rec.print_coa_invoiced,0,NULL,1,'Y',NULL),
DECODE(coa_rec.vendor_coa_required,0,NULL,1,'Y',NULL),
'',
'0',
create_rec.creation_date,
create_rec.created_by,
update_rec.last_update_date,
update_rec.last_updated_by,
update_rec.last_update_login,
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
''
);
INSERT INTO gmd_inventory_spec_vrs
(
spec_vr_id,
spec_id,
orgn_code,
sampling_plan_id,
lot_id,
lot_no,
sublot_no,
whse_code,
location,
spec_vr_status,
lot_optional_on_sample,
start_date,
end_date,
sample_inv_trans_ind,
control_lot_attrib_ind,
out_of_spec_lot_status,
in_spec_lot_status,
coa_type,
coa_at_ship_ind,
coa_at_invoice_ind,
coa_req_from_supl_ind,
text_code,
delete_mark,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
)
VALUES
(
l_spec_vr_id,
l_spec_id,
sd.orgn_code,
'',
sd.lot_id,
l_lot_no,
l_sublot_no,
sd.whse_code,
sd.location,
l_spec_status,
'',
l_start_date,
l_version_end_date,
'',
'',
'',
'',
'',
DECODE(coa_rec.print_coa_shipped,0,NULL,1,'Y',NULL),
DECODE(coa_rec.print_coa_invoiced,0,NULL,1,'Y',NULL),
DECODE(coa_rec.vendor_coa_required,0,NULL,1,'Y',NULL),
'',
'0',
create_rec.creation_date,
create_rec.created_by,
update_rec.last_update_date,
update_rec.last_updated_by,
update_rec.last_update_login,
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
''
);
INSERT INTO gmd_customer_spec_vrs
(
spec_vr_id,
spec_id,
orgn_code,
sampling_plan_id,
cust_id,
order_id,
order_line_id,
order_line,
ship_to_site_id,
org_id,
lot_optional_on_sample,
spec_vr_status,
start_date,
end_date,
sample_inv_trans_ind,
coa_type,
coa_at_ship_ind,
coa_at_invoice_ind,
coa_req_from_supl_ind,
text_code,
delete_mark,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
)
VALUES
(
l_spec_vr_id,
l_spec_id,
sd.orgn_code,
'',
sd.cust_id,
sd.order_header_id,
'',
sd.order_line_no,
sd.ship_to_site_id,
sd.order_org_id,
'',
l_spec_status,
l_start_date,
l_version_end_date,
'',
'',
DECODE(coa_rec.print_coa_shipped,0,NULL,1,'Y',NULL),
DECODE(coa_rec.print_coa_invoiced,0,NULL,1,'Y',NULL),
DECODE(coa_rec.vendor_coa_required,0,NULL,1,'Y',NULL),
'',
'0',
create_rec.creation_date,
create_rec.created_by,
update_rec.last_update_date,
update_rec.last_updated_by,
update_rec.last_update_login,
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
''
);
INSERT INTO gmd_supplier_spec_vrs
(
spec_vr_id,
spec_id,
orgn_code,
sampling_plan_id,
supplier_id,
supplier_site_id,
po_header_id,
po_line_id,
lot_optional_on_sample,
spec_vr_status,
start_date,
end_date,
sample_inv_trans_ind,
coa_type,
coa_at_ship_ind,
coa_at_invoice_ind,
coa_req_from_supl_ind,
text_code,
delete_mark,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
)
VALUES
(
l_spec_vr_id,
l_spec_id,
sd.orgn_code,
'',
l_supplier_id,
l_supplier_site_id,
'',
'',
'',
l_spec_status,
l_start_date,
l_version_end_date,
'',
'',
DECODE(coa_rec.print_coa_shipped,0,NULL,1,'Y',NULL),
DECODE(coa_rec.print_coa_invoiced,0,NULL,1,'Y',NULL),
DECODE(coa_rec.vendor_coa_required,0,NULL,1,'Y',NULL),
'',
'0',
create_rec.creation_date,
create_rec.created_by,
update_rec.last_update_date,
update_rec.last_updated_by,
update_rec.last_update_login,
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
''
);
UPDATE qc_spec_mst
SET migration_status = 'MO'
WHERE spec_hdr_id = hdr_rec.spec_hdr_id
and migration_status is NULL;
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_SPEC_MST',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => '',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_SPEC_MST',
p_DB_ERROR => sqlerrm,
p_param1 => sd.qc_spec_id,
p_param2 => l_spec_id,
p_param3 => sd.qcassy_typ_id,
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_MIGRATION_DB_ERROR',
p_message_type => 'E',
p_line_no => '1',
p_position => '',
p_base_message => 'Failed to migrate specifications due to '||sqlerrm);
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_SPEC_MST',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => '',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
SELECT lot_no, sublot_no
FROM ic_lots_mst
WHERE lot_id = v_lot_id;
SELECT feff.recipe_id
FROM gmd_recipe_validity_rules feff,
gme_batch_header bh
WHERE bh.batch_id = v_batch_id
AND bh.recipe_validity_rule_id = feff.recipe_validity_rule_id;
SELECT gmd_qc_sampling_event_id_s.nextval
FROM SYS.DUAL;
SELECT gmd_qc_event_spec_disp_id_s.nextval
FROM SYS.DUAL;
/* Select sample data that has not been migrated */
CURSOR c_get_samples IS
SELECT sample_id,
orgn_code,
sample_no,
sample_desc,
batch_id,
formula_id,
formulaline_id,
routing_id,
routingstep_id,
oprn_id,
item_id,
lot_id,
whse_code,
location,
cust_id,
vendor_id,
sample_date,
sampled_by,
sample_qty,
sample_um,
external_id,
sample_status,
sample_final_approver,
sample_test_approver,
storage_whse,
storage_location,
sample_source,
charge,
order_header_id,
order_line_id,
order_line,
order_org_id,
qc_rec_type,
ship_to_site_id,
delete_mark,
text_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
FROM qc_smpl_mst
WHERE migration_status is NULL ;
SELECT spec_vr_id, spec_id
FROM gmd_spec_mapping map, qc_rslt_mst rslt
WHERE smpl_rec.sample_date >= map.start_date
and smpl_rec.sample_date <= map.end_date
-- and map.qc_spec_id = NVL(rslt.qc_spec_id,0) --Bug 3486120
and map.qc_spec_id = rslt.qc_spec_id
and rslt.sample_id = smpl_rec.sample_id;
SELECT 1
FROM qc_rslt_mst r
WHERE r.sample_id = smpl_rec.sample_id
AND r.qc_spec_id is not null ;
SELECT 1
FROM qc_rslt_mst r
WHERE r.sample_id = smpl_rec.sample_id;
SELECT spec_id, spec_vr_id, count(*) cnt
FROM gmd_spec_mapping sm,
qc_rslt_mst r
WHERE r.sample_id = smpl_rec.sample_id
and sm.qc_spec_id = r.qc_spec_id
-- B3486120 Removed condition below
-- and l_r_tests_cnt = ( select count(*)
-- from qc_rslt_mst r
-- where r.sample_id = smpl_rec.sample_id
-- and r.qc_spec_id is not null )
group by spec_id, spec_vr_id
order by cnt desc;
SELECT count(*)
FROM qc_smpl_mst
WHERE orgn_code = smpl_rec.orgn_code
and sample_no = smpl_rec.sample_no;
SELECT 'K+'
from all_tab_columns
where table_name='GMD_SAMPLING_EVENTS'
and column_name='SAMPLE_TYPE'
and owner = l_gmd;
SELECT 'K+'
from all_tab_columns
where table_name='GMD_SAMPLES'
and column_name='SAMPLE_TYPE'
and owner = l_gmd;
Select User_id from fnd_user where user_name = l_user;
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_SMPL_MST',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => '',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
l_sql_stmt1 := 'INSERT INTO gmd_sampling_events '
|| '('
|| 'item_id,'
|| 'sampling_event_id,'
|| 'original_spec_vr_id,'
|| 'complete_ind,'
|| 'disposition,'
|| 'source,'
|| 'sample_req_cnt,'
|| 'sample_taken_cnt,'
|| 'batch_id,'
|| 'recipe_id,'
|| 'formula_id,'
|| 'formulaline_id,'
|| 'routing_id,'
|| 'step_id,'
|| 'oprn_id,'
|| 'lot_id,'
|| 'lot_no,'
|| 'sublot_no,'
|| 'whse_code,'
|| 'location,'
|| 'cust_id,'
|| 'supplier_id,'
|| 'charge,'
|| 'order_id,'
|| 'order_line_id,'
|| 'org_id,'
|| 'ship_to_site_id,'
|| 'creation_date,'
|| 'created_by,'
|| 'last_update_date,'
|| 'last_updated_by,'
|| 'last_update_login '
|| ')'
|| ' VALUES '
|| '( '
|| ':item_id,'
|| ':sampling_event_id,'
|| ':spec_vr_id,'
|| ':l_y,'
|| ':disposition,'
|| ':qc_rec_type,'
|| ':l_1,'
|| ':l_2,'
|| ':batch_id,'
|| ':recipe_id,'
|| ':formula_id,'
|| ':formulaline_id,'
|| ':routing_id,'
|| ':routingstep_id,'
|| ':oprn_id,'
|| ':lot_id,'
|| ':lot_no,'
|| ':sublot_no,'
|| ':whse_code,'
|| ':location,'
|| ':cust_id,'
|| ':supplier_id,'
|| ':charge,'
|| ':order_header_id,'
|| ':order_line_id,'
|| ':order_org_id,'
|| ':ship_to_site_id,'
|| ':creation_date,'
|| ':created_by,'
|| ':last_update_date,'
|| ':last_updated_by,'
|| ':last_update_login '
|| ' )';
l_sql_stmt1 := 'INSERT INTO gmd_sampling_events'
|| '('
|| 'item_id,'
|| 'sampling_event_id,'
|| 'original_spec_vr_id,'
|| 'complete_ind,'
|| 'disposition,'
|| 'source,'
|| 'sample_req_cnt,'
|| 'sample_taken_cnt,'
|| 'batch_id,'
|| 'recipe_id,'
|| 'formula_id,'
|| 'formulaline_id,'
|| 'routing_id,'
|| 'step_id,'
|| 'oprn_id,'
|| 'lot_id,'
|| 'lot_no,'
|| 'sublot_no,'
|| 'whse_code,'
|| 'location,'
|| 'cust_id,'
|| 'supplier_id,'
|| 'charge,'
|| 'order_id,'
|| 'order_line_id,'
|| 'org_id,'
|| 'ship_to_site_id,'
|| 'creation_date,'
|| 'created_by,'
|| 'last_update_date,'
|| 'last_updated_by,'
|| 'last_update_login,'
|| 'sample_type '
|| ')'
|| ' VALUES '
|| '( '
|| ':item_id,'
|| ':sampling_event_id,'
|| ':spec_vr_id,'
|| ':l_y,'
|| ':disposition,'
|| ':qc_rec_type,'
|| ':l_1,'
|| ':l_2,'
|| ':batch_id,'
|| ':recipe_id,'
|| ':formula_id,'
|| ':formulaline_id,'
|| ':routing_id,'
|| ':routingstep_id,'
|| ':oprn_id,'
|| ':lot_id,'
|| ':lot_no,'
|| ':sublot_no,'
|| ':whse_code,'
|| ':location,'
|| ':cust_id,'
|| ':supplier_id,'
|| ':charge,'
|| ':order_header_id,'
|| ':order_line_id,'
|| ':order_org_id,'
|| ':ship_to_site_id,'
|| ':creation_date,'
|| ':created_by,'
|| ':last_update_date,'
|| ':last_updated_by,'
|| ':last_update_login,'
|| ':l_i'
|| ' ) ';
l_sql_stmt2 := 'INSERT INTO gmd_samples'
|| '('
||'sample_id,'
||'orgn_code,'
||'sample_no,'
||'sample_desc,'
||'sample_disposition,'
||'sampling_event_id,'
||'source,'
||'batch_id,'
||'recipe_id,'
||'formula_id,'
||'formulaline_id,'
||'routing_id,'
||'step_id,'
||'oprn_id,'
||'item_id,'
||'lot_id,'
||'lot_no,'
||'sublot_no,'
||'whse_code,'
||'location,'
||'cust_id,'
||'supplier_id,'
||'date_drawn,'
||'sampler_id,'
||'sample_qty,'
||'sample_uom,'
||'external_id,'
||'inv_approver_id,'
||'sample_approver_id,'
||'storage_whse,'
||'storage_location,'
||'source_comment,'
||'charge,'
||'order_id,'
||'order_line_id,'
||'org_id,'
||'ship_to_site_id,'
||'priority,'
||'delete_mark,'
||'creation_date,'
||'created_by,'
||'last_update_date,'
||'last_updated_by,'
||'last_update_login,'
||'attribute1,'
||'attribute2,'
||'attribute3,'
||'attribute4,'
||'attribute5,'
||'attribute6,'
||'attribute7,'
||'attribute8,'
||'attribute9,'
||'attribute10,'
||'attribute11,'
||'attribute12,'
||'attribute13,'
||'attribute14,'
||'attribute15,'
||'attribute16,'
||'attribute17,'
||'attribute18,'
||'attribute19,'
||'attribute20,'
||'attribute21,'
||'attribute22,'
||'attribute23,'
||'attribute24,'
||'attribute25,'
||'attribute26,'
||'attribute27,'
||'attribute28,'
||'attribute29,'
||'attribute30,'
||'attribute_category'
||')'
||' VALUES '
||'( '
||':sample_id,'
||':orgn_code,'
||':l_sample_no,'
||':sample_desc,'
||':l_disposition,'
||':l_sampling_event_id,'
||':qc_rec_type,'
||':batch_id,'
||':l_recipe_id,'
||':formula_id,'
||':formulaline_id,'
||':routing_id,'
||':routingstep_id,'
||':oprn_id,'
||':item_id,'
||':lot_id,'
||':l_lot_no,'
||':l_sublot_no,'
||':whse_code,'
||':location,'
||':cust_id,'
||':l_supplier_id,'
||':sample_date,'
||':sampled_by,'
||':sample_qty,'
||':sample_um,'
||':external_id,'
||':sample_final_approver,'
||':sample_test_approver,'
||':storage_whse,'
||':storage_location,'
||':sample_source,'
||':charge,'
||':order_header_id,'
||':order_line_id,'
||':order_org_id,'
||':ship_to_site_id,'
||':l_prty,'
||':delete_mark,'
||':creation_date,'
||':created_by,'
||':last_update_date,'
||':last_updated_by,'
||':last_update_login,'
||':attribute1,'
||':attribute2,'
||':attribute3,'
||':attribute4,'
||':attribute5,'
||':attribute6,'
||':attribute7,'
||':attribute8,'
||':attribute9,'
||':attribute10,'
||':attribute11,'
||':attribute12,'
||':attribute13,'
||':attribute14,'
||':attribute15,'
||':attribute16,'
||':attribute17,'
||':attribute18,'
||':attribute19,'
||':attribute20,'
||':attribute21,'
||':attribute22,'
||':attribute23,'
||':attribute24,'
||':attribute25,'
||':attribute26,'
||':attribute27,'
||':attribute28,'
||':attribute29,'
||':attribute30,'
||':attribute_category'
||' ) ';
l_sql_stmt2 := 'INSERT INTO gmd_samples'
|| '('
||'sample_id,'
||'orgn_code,'
||'sample_no,'
||'sample_desc,'
||'sample_disposition,'
||'sampling_event_id,'
||'source,'
||'batch_id,'
||'recipe_id,'
||'formula_id,'
||'formulaline_id,'
||'routing_id,'
||'step_id,'
||'oprn_id,'
||'item_id,'
||'lot_id,'
||'lot_no,'
||'sublot_no,'
||'whse_code,'
||'location,'
||'cust_id,'
||'supplier_id,'
||'date_drawn,'
||'sampler_id,'
||'sample_qty,'
||'sample_uom,'
||'external_id,'
||'inv_approver_id,'
||'sample_approver_id,'
||'storage_whse,'
||'storage_location,'
||'source_comment,'
||'charge,'
||'order_id,'
||'order_line_id,'
||'org_id,'
||'ship_to_site_id,'
||'priority,'
||'delete_mark,'
||'text_code,'
||'creation_date,'
||'created_by,'
||'last_update_date,'
||'last_updated_by,'
||'last_update_login,'
||'sample_type,'
||'attribute1,'
||'attribute2,'
||'attribute3,'
||'attribute4,'
||'attribute5,'
||'attribute6,'
||'attribute7,'
||'attribute8,'
||'attribute9,'
||'attribute10,'
||'attribute11,'
||'attribute12,'
||'attribute13,'
||'attribute14,'
||'attribute15,'
||'attribute16,'
||'attribute17,'
||'attribute18,'
||'attribute19,'
||'attribute20,'
||'attribute21,'
||'attribute22,'
||'attribute23,'
||'attribute24,'
||'attribute25,'
||'attribute26,'
||'attribute27,'
||'attribute28,'
||'attribute29,'
||'attribute30,'
||'attribute_category'
||')'
||' VALUES '
||'( '
||':sample_id,'
||':orgn_code,'
||':l_sample_no,'
||':sample_desc,'
||':l_disposition,'
||':l_sampling_event_id,'
||':qc_rec_type,'
||':batch_id,'
||':l_recipe_id,'
||':formula_id,'
||':formulaline_id,'
||':routing_id,'
||':routingstep_id,'
||':oprn_id,'
||':item_id,'
||':lot_id,'
||':l_lot_no,'
||':l_sublot_no,'
||':whse_code,'
||':location,'
||':cust_id,'
||':l_supplier_id,'
||':sample_date,'
||':sampled_by,'
||':sample_qty,'
||':sample_um,'
||':external_id,'
||':sample_final_approver,'
||':sample_test_approver,'
||':storage_whse,'
||':storage_location,'
||':sample_source,'
||':charge,'
||':order_header_id,'
||':order_line_id,'
||':order_org_id,'
||':ship_to_site_id,'
||':l_prty,'
||':delete_mark,'
||':text_code,'
||':creation_date,'
||':created_by,'
||':last_update_date,'
||':last_updated_by,'
||':last_update_login,'
||':l_i,'
||':attribute1,'
||':attribute2,'
||':attribute3,'
||':attribute4,'
||':attribute5,'
||':attribute6,'
||':attribute7,'
||':attribute8,'
||':attribute9,'
||':attribute10,'
||':attribute11,'
||':attribute12,'
||':attribute13,'
||':attribute14,'
||':attribute15,'
||':attribute16,'
||':attribute17,'
||':attribute18,'
||':attribute19,'
||':attribute20,'
||':attribute21,'
||':attribute22,'
||':attribute23,'
||':attribute24,'
||':attribute25,'
||':attribute26,'
||':attribute27,'
||':attribute28,'
||':attribute29,'
||':attribute30,'
||':attribute_category'
||' )';
/* Select sample data that has not been migrated */
OPEN c_get_samples;
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_SMPL_MST',
p_DB_ERROR => '',
p_param1 => 'Sample with results have with spec tests',
p_param2 => 'Cannot match migrated spec ',
p_param3 => 'Sample_id = '||smpl_rec.sample_id,
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS_RW',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
smpl_rec.last_update_date,
smpl_rec.last_updated_by,
smpl_rec.last_update_login;
smpl_rec.last_update_date,
smpl_rec.last_updated_by,
smpl_rec.last_update_login,
'I';
/* Insert record into new sample table */
-- B3883674 Use appropriate variable
IF l_samples_level IS NULL THEN
-- IF l_patch_level IS NULL THEN
-- Customer is on J
EXECUTE IMMEDIATE l_sql_stmt2 USING
smpl_rec.sample_id,
smpl_rec.orgn_code,
l_sample_no,
smpl_rec.sample_desc,
l_disposition,
l_sampling_event_id,
smpl_rec.qc_rec_type,
smpl_rec.batch_id,
l_recipe_id,
smpl_rec.formula_id,
smpl_rec.formulaline_id,
smpl_rec.routing_id,
smpl_rec.routingstep_id,
smpl_rec.oprn_id,
smpl_rec.item_id,
smpl_rec.lot_id,
l_lot_no,
l_sublot_no,
smpl_rec.whse_code,
smpl_rec.location,
smpl_rec.cust_id,
l_supplier_id,
smpl_rec.sample_date,
smpl_rec.sampled_by,
smpl_rec.sample_qty,
smpl_rec.sample_um,
smpl_rec.external_id,
l_inv_approver, -- 4898620
l_sample_approver, -- 4898620
smpl_rec.storage_whse,
smpl_rec.storage_location,
smpl_rec.sample_source,
smpl_rec.charge,
smpl_rec.order_header_id,
smpl_rec.order_line_id,
smpl_rec.order_org_id,
smpl_rec.ship_to_site_id,
l_prty,
smpl_rec.delete_mark,
smpl_rec.creation_date,
smpl_rec.created_by,
smpl_rec.last_update_date,
smpl_rec.last_updated_by,
smpl_rec.last_update_login ,
smpl_rec.attribute1,
smpl_rec.attribute2,
smpl_rec.attribute3,
smpl_rec.attribute4,
smpl_rec.attribute5,
smpl_rec.attribute6,
smpl_rec.attribute7,
smpl_rec.attribute8,
smpl_rec.attribute9,
smpl_rec.attribute10,
smpl_rec.attribute11,
smpl_rec.attribute12,
smpl_rec.attribute13,
smpl_rec.attribute14,
smpl_rec.attribute15,
smpl_rec.attribute16,
smpl_rec.attribute17,
smpl_rec.attribute18,
smpl_rec.attribute19,
smpl_rec.attribute20,
smpl_rec.attribute21,
smpl_rec.attribute22,
smpl_rec.attribute23,
smpl_rec.attribute24,
smpl_rec.attribute25,
smpl_rec.attribute26,
smpl_rec.attribute27,
smpl_rec.attribute28,
smpl_rec.attribute29,
smpl_rec.attribute30,
smpl_rec.attribute_category;
smpl_rec.delete_mark,
smpl_rec.text_code,
smpl_rec.creation_date,
smpl_rec.created_by,
smpl_rec.last_update_date,
smpl_rec.last_updated_by,
smpl_rec.last_update_login,
'I',
smpl_rec.attribute1,
smpl_rec.attribute2,
smpl_rec.attribute3,
smpl_rec.attribute4,
smpl_rec.attribute5,
smpl_rec.attribute6,
smpl_rec.attribute7,
smpl_rec.attribute8,
smpl_rec.attribute9,
smpl_rec.attribute10,
smpl_rec.attribute11,
smpl_rec.attribute12,
smpl_rec.attribute13,
smpl_rec.attribute14,
smpl_rec.attribute15,
smpl_rec.attribute16,
smpl_rec.attribute17,
smpl_rec.attribute18,
smpl_rec.attribute19,
smpl_rec.attribute20,
smpl_rec.attribute21,
smpl_rec.attribute22,
smpl_rec.attribute23,
smpl_rec.attribute24,
smpl_rec.attribute25,
smpl_rec.attribute26,
smpl_rec.attribute27,
smpl_rec.attribute28,
smpl_rec.attribute29,
smpl_rec.attribute30,
smpl_rec.attribute_category;
INSERT INTO gmd_event_spec_disp
(
event_spec_disp_id,
sampling_event_id,
spec_id,
spec_vr_id,
disposition,
spec_used_for_lot_attrib_ind,
delete_mark,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES
(
gmd_qc_event_spec_disp_id_s.nextval, --Bug 3486120, changed it for performance
l_sampling_event_id,
l_spec_id,
l_spec_vr_id,
l_disposition,
'Y',
'0',
smpl_rec.creation_date,
smpl_rec.created_by,
smpl_rec.last_update_date,
smpl_rec.last_updated_by,
smpl_rec.last_update_login
) RETURNING event_spec_disp_id INTO l_event_spec_disp_id; --Bug 3486120, changed it for perm.
INSERT INTO gmd_sample_spec_disp
(
event_spec_disp_id,
sample_id,
disposition,
delete_mark,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES
(
l_event_spec_disp_id,
smpl_rec.sample_id,
l_disposition,
'0',
smpl_rec.creation_date,
smpl_rec.created_by,
smpl_rec.last_update_date,
smpl_rec.last_updated_by,
smpl_rec.last_update_login
);
UPDATE qc_smpl_mst
SET migration_status = 'MO'
WHERE sample_id = smpl_rec.sample_id;
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_SMPL_MST',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => l_rec_count,
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS_RW',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_SMPL_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 => '',
p_base_message => 'Failed to migrate samples due to '||sqlerrm);
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_SMPL_MST',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => '',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
/* Select results data that has not been migrated */
CURSOR c_get_results IS
SELECT qcassy_typ_id,
qc_result_id,
qc_spec_id,
sample_id,
orgn_code,
result_date,
assay_code,
text_result,
num_result,
qcunit_code,
accept_anyway,
final_mark,
test_provider_code,
assay_tester,
assay_retest,
wf_response,
item_id,
lot_id,
whse_code,
location,
cust_id,
vendor_id,
charge,
qc_rec_type,
ship_to_site_id,
delete_mark,
text_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
FROM qc_rslt_mst
WHERE migration_status is NULL
ORDER BY assay_code;
SELECT d.event_spec_disp_id, d.spec_id
FROM gmd_event_spec_disp d, gmd_samples s
WHERE d.sampling_event_id = s.sampling_event_id and
s.sample_id = rslt_rec.sample_id;
SELECT /*+ INDEX ( gmd_results gmd.gmd_results_n1 ) */
NVL(max(seq),0) + 10
FROM gmd_results
WHERE sample_id = rslt_rec.sample_id;
SELECT 1
FROM gmd_spec_tests_b
WHERE spec_id = pspec_id
AND test_id = ptest_id;
SELECT NVL(max(seq),0) + 10
FROM gmd_spec_tests_b
WHERE spec_id = pspec_id;
SELECT retest_lot_expiry_ind
FROM gmd_spec_tests_b
WHERE spec_id = pspec_id;
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_RSLT_MST',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => '',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
/* Select results data that has not been migrated */
OPEN c_get_results;
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_RSLT_MST',
p_DB_ERROR => '',
p_param1 => ' Spec_Id = '||id_rec.spec_id,
p_param2 => ' Test_ID = '||rslt_rec.qcassy_typ_id,
p_param3 => ' Result_Id = '||rslt_rec.qc_result_id ,
p_param4 => ' ',
p_param5 => '',
p_base_message => 'Result Test changed to additional test, '||
'since the Spec and Test do not match',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_message_token => 'CHANGED_TO_ADDITIONAL_TEST');
END IF; /* Test is not part of selected spec */
/* Insert record into new results table */
INSERT INTO gmd_results
(
result_id,
sample_id,
test_id,
seq,
test_replicate_cnt,
qc_lab_orgn_code,
result_value_num,
result_value_char,
result_date,
test_kit_item_id,
test_kit_lot_no,
test_kit_sublot_no,
tester,
tester_id,
test_provider_code,
assay_retest,
text_code,
delete_mark,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30,
attribute_category
)
VALUES
(
rslt_rec.qc_result_id,
rslt_rec.sample_id,
rslt_rec.qcassy_typ_id,
l_seq,
'1',
l_qc_lab_orgn_code,
rslt_rec.num_result,
rslt_rec.text_result,
rslt_rec.result_date,
'',
'',
'',
rslt_rec.assay_tester,
'',
rslt_rec.test_provider_code,
rslt_rec.assay_retest,
rslt_rec.text_code,
rslt_rec.delete_mark,
rslt_rec.creation_date,
rslt_rec.created_by,
rslt_rec.last_update_date,
rslt_rec.last_updated_by,
rslt_rec.last_update_login,
rslt_rec.attribute1,
rslt_rec.attribute2,
rslt_rec.attribute3,
rslt_rec.attribute4,
rslt_rec.attribute5,
rslt_rec.attribute6,
rslt_rec.attribute7,
rslt_rec.attribute8,
rslt_rec.attribute9,
rslt_rec.attribute10,
rslt_rec.attribute11,
rslt_rec.attribute12,
rslt_rec.attribute13,
rslt_rec.attribute14,
rslt_rec.attribute15,
rslt_rec.attribute16,
rslt_rec.attribute17,
rslt_rec.attribute18,
rslt_rec.attribute19,
rslt_rec.attribute20,
rslt_rec.attribute21,
rslt_rec.attribute22,
rslt_rec.attribute23,
rslt_rec.attribute24,
rslt_rec.attribute25,
rslt_rec.attribute26,
rslt_rec.attribute27,
rslt_rec.attribute28,
rslt_rec.attribute29,
rslt_rec.attribute30,
rslt_rec.attribute_category
);
INSERT INTO gmd_spec_results
(
event_spec_disp_id,
result_id,
evaluation_ind,
in_spec_ind,
value_in_report_precision,
additional_test_ind,
delete_mark,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
VALUES
(
id_rec.event_spec_disp_id,
rslt_rec.qc_result_id,
l_evaluation_ind,
l_in_spec_ind,
rslt_rec.num_result,
l_additional_test_ind,
'0',
rslt_rec.creation_date,
rslt_rec.created_by,
rslt_rec.last_update_date,
rslt_rec.last_updated_by,
rslt_rec.last_update_login
);
/* GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_RSLT_MST',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => '',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'aFTER insert into GMD_SPEC_RESULTS',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
UPDATE qc_rslt_mst
SET migration_status = 'MO'
WHERE qc_result_id = rslt_rec.qc_result_id;
END LOOP; /* Number or records selected */
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_RSLT_MST',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => l_rec_count,
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS_RW',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_RSLT_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 => '',
p_base_message => 'Failed to migrate results due to '||sqlerrm);
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'QC_RSLT_MST',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => '',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
p_message_type => 'P',
p_line_no => '1',
p_position => '',
p_base_message => '');
select r.sample_id, r.test_id, count(*) cnt
from GMD_RESULTS r
group by r.sample_id, r.test_id
having count(*) > 1;
select r.result_id, r.result_date
from GMD_RESULTS r
where r.sample_id = l_sample_id
and r.test_id = l_test_id
order by decode ( r.result_date, NULL,
to_date( '01-01-2040', 'DD-MM-YYYY' ),
r.creation_date) asc;
SELECT *
FROM gmd_samples s
WHERE delete_mark= 0 AND
NOT EXISTS (SELECT 's' from gmd_results r
where s.sample_id = r.sample_id);
SELECT original_spec_vr_id
FROM gmd_sampling_events
WHERE sampling_event_id = psampling_event_id;
SELECT /*+ INDEX(ESD GMD_EVENT_SPEC_DISP_N1) */
s.sample_id ip_sample, esd.sampling_event_id ip_sampling_event,
esd.event_spec_disp_id ip_event_spec
FROM gmd_samples s,
gmd_event_spec_disp esd
WHERE S.sampling_event_id = ESD.sampling_event_id
AND S.SAMPLE_DISPOSITION = '2I'
and NOT EXISTS
( SELECT /*+ INDEX(SR GMD_SPEC_RESULTS_PK) */
1
FROM gmd_spec_results sr
WHERE SR.EVENT_SPEC_DISP_ID = esd.EVENT_SPEC_DISP_ID
AND SR.EVALUATION_IND IS NULL );
SELECT 1
FROM gmd_results r,
gmd_spec_results sr
WHERE r.sample_id = psample_id
AND sr.result_id = r.result_id
AND sr.evaluation_ind is null ;
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'Create_Sample_Results',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => '',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'STARTED',
p_message_type => 'I',
p_line_no => '',
p_position => NULL,
p_base_message => '');
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'GMD_SPEC_RESULTS',
p_DB_ERROR => sqlerrm,
p_param1 => 'l_spec_vr_id= '||l_spec_vr_id,
p_param2 => 'nores_rec.sample_id= '||nores_rec.sample_id,
p_param3 => 'nores_rec.lot_retest_ind= '||nores_rec.lot_retest_ind,
p_param4 => 'nores_rec.sampling_event_id= '||nores_rec.sampling_event_id,
p_param5 => '',
p_message_token => 'GMA_MIGRATION_DB_ERROR',
p_message_type => 'E',
p_line_no => '1',
p_position => '',
p_base_message => 'Failed to migrate results due to '||sqlerrm);
END LOOP; /* Number or records selected */
select sysdate into l_date from dual ;
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'Create_Sample_Results',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => '',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'STARTED - Checking for Completed Samples',
p_message_type => 'I',
p_line_no => '',
p_position => NULL,
p_base_message => '');
update gmd_samples
set sample_disposition = '3C'
where sample_id = l_ip_samples.ip_sample;
update gmd_sample_spec_disp
set disposition = '3C'
where event_spec_disp_id = l_ip_samples.ip_event_spec ;
update gmd_event_spec_disp
set disposition = '3C'
where event_spec_disp_id = l_ip_samples.ip_event_spec ;
update gmd_sampling_events
set disposition = '3C'
where sampling_event_id = l_ip_samples.ip_sampling_event;
UPDATE gmd_results r
set test_replicate_cnt = l_rep_cnt
where result_id = l_result_id;
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'Create_Sample_Results',
p_DB_ERROR => '',
p_param1 => '',
p_param2 => '',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => 'ENDED - Checking for Completed Samples',
p_message_type => 'I',
p_line_no => '',
p_position => NULL,
p_base_message => '');
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'SAMPLE_RESULTS',
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 => '',
p_base_message => '');
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'GMD_WIP_SPEC_VRS',
p_DB_ERROR => '',
p_param1 => 'Nulling out end date on wip vrs ',
p_param2 => 'Where end date is SY$MAX_DATE ',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => '',
p_message_type => 'P',
p_line_no => '',
p_position => '',
p_base_message => '');
update gmd_wip_spec_vrs
set end_date = NULL
where trunc(end_date + 1) >= l_max_date
and SPEC_VR_STATUS = 700;
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'GMD_INVENTORY_SPEC_VRS',
p_DB_ERROR => '',
p_param1 => 'Nulling out end date on Inventory vrs ',
p_param2 => 'Where end date is SY$MAX_DATE ',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => '',
p_message_type => 'P',
p_line_no => '',
p_position => '',
p_base_message => '');
update gmd_inventory_spec_vrs
set end_date = NULL
where trunc(end_date + 1) >= l_max_date
and SPEC_VR_STATUS = 700;
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'GMD_CUSTOMER_SPEC_VRS',
p_DB_ERROR => '',
p_param1 => 'Nulling out end date on customer vrs ',
p_param2 => 'Where end date is SY$MAX_DATE ',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => '',
p_message_type => 'P',
p_line_no => '',
p_position => '',
p_base_message => '');
update gmd_customer_spec_vrs
set end_date = NULL
where trunc(end_date + 1) >= l_max_date
and SPEC_VR_STATUS = 700;
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'GMD_SUPPLIER_SPEC_VRS',
p_DB_ERROR => '',
p_param1 => 'Nulling out end date on supplier vrs ',
p_param2 => 'Where end date is SY$MAX_DATE ',
p_param3 => '',
p_param4 => '',
p_param5 => '',
p_message_token => '',
p_message_type => 'P',
p_line_no => '',
p_position => '',
p_base_message => '');
update gmd_supplier_spec_vrs
set end_date = NULL
where trunc(end_date + 1) >= l_max_date
and SPEC_VR_STATUS = 700;
GMA_MIGRATION.gma_insert_message (
p_run_id => p_migration_id,
p_table_name => 'GMD_***_SPEC_VRS',
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 => '',
p_base_message => '');