[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT profile_option_value
FROM fnd_profile_options A, fnd_profile_option_values B
WHERE a.profile_option_id = b.profile_option_id
AND a.profile_option_name = p_profile_name
AND level_id = 10001;
SELECT status_id
FROM ic_lots_sts
WHERE lot_status = p_lot_status;
select gem5_text_code_s.nextval from dual;
SELECT *
FROM qc_text_tbl
WHERE text_code = p_text_code;
Insert Text Header
====================================*/
INSERT INTO qc_text_hdr
(
TEXT_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_text_code,
0,
SYSDATE,
0,
SYSDATE,
NULL
);
Insert qc_text_tbl.
====================================*/
OPEN get_text_dtl;
Insert a Copy.
=============================*/
INSERT INTO qc_text_tbl
(
TEXT_CODE,
LANG_CODE,
PARAGRAPH_CODE,
SUB_PARACODE,
LINE_NO,
LAST_UPDATED_BY,
CREATED_BY,
LAST_UPDATE_DATE,
CREATION_DATE,
LAST_UPDATE_LOGIN,
TEXT
)
VALUES
(
l_text_code,
l_text_rec.lang_code,
l_text_rec.paragraph_code,
l_text_rec.sub_paracode,
l_text_rec.line_no,
0,
0,
SYSDATE,
SYSDATE,
NULL,
l_text_rec.text
);
Insert to TL Table.
=============================*/
GMA_QC_TEXT_TBL_PKG.INSERT_ROW (
X_ROWID => l_rowid,
X_TEXT_CODE => l_text_code,
X_LANG_CODE => l_text_rec.lang_code,
X_PARAGRAPH_CODE => l_text_rec.paragraph_code,
X_SUB_PARACODE => l_text_rec.sub_paracode,
X_LINE_NO => l_text_rec.line_no,
X_TEXT => l_text_rec.text,
X_LAST_UPDATED_BY => 0,
X_CREATED_BY => 0,
X_LAST_UPDATE_DATE => SYSDATE,
X_CREATION_DATE => SYSDATE,
X_LAST_UPDATE_LOGIN => NULL);
SELECT organization_id, subinventory_ind_flag, loct_ctl
FROM ic_whse_mst
WHERE whse_code = p_whse_code;
SELECT locator_id
FROM ic_loct_mst
WHERE whse_code = p_whse_code
AND location = p_location;
SELECT subinventory_code, organization_id
FROM mtl_item_locations
WHERE inventory_location_id = p_locator_id;
SELECT icw.whse_code, icw.orgn_code, icw.organization_id
FROM ic_whse_mst icw
WHERE ( icw.subinventory_ind_flag <> 'Y'
OR icw.subinventory_ind_flag IS NULL)
AND NOT EXISTS (SELECT organization_id
FROM gmd_quality_config
WHERE organization_id = icw.organization_id)
AND icw.organization_id IS NOT NULL;
SELECT qc.*
FROM gmd_quality_config qc, sy_orgn_mst m
WHERE m.orgn_code = p_orgn_code
AND m.organization_id = qc.organization_id;
INSERT INTO gmd_quality_config(
ORGN_CODE,
CONTROL_BATCH_STEP_IND,
CONTROL_LOT_ATTRIB_IND,
OUT_OF_SPEC_LOT_STATUS,
IN_SPEC_LOT_STATUS,
SAMPLE_INV_TRANS_IND,
API_ROUND_TRUN_IND,
LOT_OPTIONAL_ON_SAMPLE,
TEXT_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
INV_TRANS_REASON_CODE,
RESAMPLE_ACTION_CODE,
RETEST_ACTION_CODE,
CHOOSE_SPEC,
AUTO_SAMPLE_IND,
DELAYED_LOT_ENTRY,
ORGANIZATION_ID,
OUT_OF_SPEC_LOT_STATUS_ID,
IN_SPEC_LOT_STATUS_ID,
TRANSACTION_REASON_ID,
QUALITY_LAB_IND,
EXACT_SPEC_MATCH_IND,
INCLUDE_OPTIONAL_TEST_RSLT_IND,
SPEC_VERSION_CONTROL_IND,
SAMPLE_LAST_ASSIGNED,
SAMPLE_ASSIGNMENT_TYPE,
SS_ASSIGNMENT_TYPE,
SS_LAST_ASSIGNED,
MIGRATED_IND
)
VALUES (
NULL,
l_config_rec.CONTROL_BATCH_STEP_IND,
l_config_rec.CONTROL_LOT_ATTRIB_IND,
l_config_rec.OUT_OF_SPEC_LOT_STATUS,
l_config_rec.IN_SPEC_LOT_STATUS,
l_config_rec.SAMPLE_INV_TRANS_IND,
l_config_rec.API_ROUND_TRUN_IND,
l_config_rec.LOT_OPTIONAL_ON_SAMPLE,
NULL,
SYSDATE,
0,
0,
SYSDATE,
NULL,
l_config_rec.INV_TRANS_REASON_CODE,
l_config_rec.RESAMPLE_ACTION_CODE,
l_config_rec.RETEST_ACTION_CODE,
l_config_rec.CHOOSE_SPEC,
l_config_rec.AUTO_SAMPLE_IND,
l_config_rec.DELAYED_LOT_ENTRY,
get_whse_info.organization_id,
l_config_rec.OUT_OF_SPEC_LOT_STATUS_ID,
l_config_rec.IN_SPEC_LOT_STATUS_ID,
l_config_rec.TRANSACTION_REASON_ID,
l_config_rec.QUALITY_LAB_IND,
l_config_rec.EXACT_SPEC_MATCH_IND,
l_config_rec.INCLUDE_OPTIONAL_TEST_RSLT_IND,
l_config_rec.SPEC_VERSION_CONTROL_IND,
l_config_rec.SAMPLE_LAST_ASSIGNED,
l_config_rec.SAMPLE_ASSIGNMENT_TYPE,
l_config_rec.SS_ASSIGNMENT_TYPE,
l_config_rec.SS_LAST_ASSIGNED,
1
);
NEXT_CONFIG_INSERT EXCEPTION;
SELECT test_method_id, test_kit_item_id, test_kit_inv_item_id,
test_kit_organization_id
FROM gmd_test_methods_b
WHERE migrated_ind IS NULL;
SELECT *
FROM gmd_quality_config
WHERE orgn_code IS NOT NULL
AND migrated_ind IS NULL;
SELECT *
FROM gmd_quality_config
WHERE orgn_code IS NULL
AND migrated_ind IS NULL
AND organization_id IS NULL;
SELECT count(1)
FROM gmd_quality_config
WHERE (orgn_code IS NOT NULL AND migrated_ind IS NULL);
SELECT m.organization_id
FROM sy_orgn_mst_b m
WHERE NOT EXISTS
(SELECT organization_id FROM gmd_quality_config WHERE organization_id = m.organization_id)
AND m.organization_id IS NOT NULL;
SELECT assignment_type, last_assigned
FROM sy_docs_seq
WHERE doc_type = v_doc_type
AND orgn_code = v_orgn_code;
SELECT plant_ind
FROM sy_orgn_mst
WHERE orgn_code = p_orgn_code;
SELECT orgn_code
FROM sy_orgn_mst
WHERE organization_id = v_organization_id;
SELECT *
FROM gmd_quality_config
WHERE migrated_ind = 1;
SELECT orgn_code
FROM ic_whse_mst
WHERE subinventory_ind_flag = 'Y'
AND mtl_organization_id = p_organization_id;
SELECT sample_assignment_type, sample_last_assigned
FROM gmd_quality_config
WHERE orgn_code = p_orgn_code;
SELECT sampling_plan_id, frequency_type, frequency_per
FROM gmd_sampling_plans_b
WHERE migrated_ind IS NULL;
SELECT organization_id
FROM gmd_quality_config
WHERE organization_id = p_org_id;
UPDATE gmd_test_methods_b
SET test_kit_inv_item_id = l_inventory_item_id,
test_kit_organization_id = l_test_org,
migrated_ind = 1
WHERE test_method_id = v_test_method.test_method_id;
Update the Row.
=====================================*/
UPDATE gmd_quality_config
SET organization_id = l_organization_id,
quality_lab_ind = l_lab_ind,
transaction_reason_id = l_reason_id,
in_spec_lot_status_id = l_in_spec_status_id,
out_of_spec_lot_status_id = l_out_spec_status_id,
exact_spec_match_ind = P_exact_match,
include_optional_test_rslt_ind = P_inc_opt,
spec_version_control_ind = P_version_control,
sample_assignment_type = l_smpl_assignment_type,
sample_last_assigned = l_smpl_last_assigned,
ss_assignment_type = l_stbl_assignment_type,
ss_last_assigned = l_stbl_last_assigned,
migrated_ind = 1
WHERE orgn_code = v_qual_config.orgn_code;
p_context => 'Quality Setup - quality config update',
p_token1 => 'ORG',
p_token2 => 'ONAME',
p_token3 => 'ROWK',
p_token4 => 'ROWV',
p_param1 => v_qual_config.orgn_code,
p_param2 => 'ORGN_CODE',
p_param3 => 'ORGN_CODE',
p_param4 => v_qual_config.orgn_code,
p_app_short_name => 'GMD');
p_context => 'Quality Setup - quality config update',
p_token1 => 'REASON',
p_token2 => 'ROWK',
p_token3 => 'ROWV',
p_param1 => v_qual_config.inv_trans_reason_code,
p_param2 => 'orgn_code',
p_param3 => v_qual_config.orgn_code,
p_app_short_name => 'GMD');
p_context => 'Quality Setup - quality config update',
p_token1 => 'STAT',
p_token2 => 'ROWK',
p_token3 => 'ROWV',
p_param1 => v_qual_config.in_spec_lot_status,
p_param2 => 'orgn_code',
p_param3 => v_qual_config.orgn_code,
p_app_short_name => 'GMD');
p_context => 'Quality Setup - quality config update',
p_token1 => 'STAT',
p_token2 => 'ROWK',
p_token3 => 'ROWV',
p_param1 => v_qual_config.out_of_spec_lot_status,
p_param2 => 'orgn_code',
p_param3 => v_qual_config.orgn_code,
p_app_short_name => 'GMD');
p_context => 'Quality Setup - quality config update',
p_token1 => 'ORG',
p_token2 => 'ROWK',
p_token3 => 'ROWV',
p_param1 => v_qual_config.orgn_code,
p_param2 => 'orgn_code',
p_param3 => v_qual_config.orgn_code,
p_app_short_name => 'GMD');
p_context => 'Quality Setup - quality config update',
p_db_error => SQLERRM,
p_app_short_name => 'GMA');
RAISE NEXT_CONFIG_INSERT;
INSERT INTO gmd_quality_config(
ORGN_CODE,
CONTROL_BATCH_STEP_IND,
CONTROL_LOT_ATTRIB_IND,
OUT_OF_SPEC_LOT_STATUS,
IN_SPEC_LOT_STATUS,
SAMPLE_INV_TRANS_IND,
API_ROUND_TRUN_IND,
LOT_OPTIONAL_ON_SAMPLE,
TEXT_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
INV_TRANS_REASON_CODE,
RESAMPLE_ACTION_CODE,
RETEST_ACTION_CODE,
CHOOSE_SPEC,
AUTO_SAMPLE_IND,
DELAYED_LOT_ENTRY,
ORGANIZATION_ID,
OUT_OF_SPEC_LOT_STATUS_ID,
IN_SPEC_LOT_STATUS_ID,
TRANSACTION_REASON_ID,
QUALITY_LAB_IND,
EXACT_SPEC_MATCH_IND,
INCLUDE_OPTIONAL_TEST_RSLT_IND,
SPEC_VERSION_CONTROL_IND,
SAMPLE_LAST_ASSIGNED,
SAMPLE_ASSIGNMENT_TYPE,
SS_ASSIGNMENT_TYPE,
SS_LAST_ASSIGNED,
MIGRATED_IND
)
VALUES (
NULL,
l_config_rec.CONTROL_BATCH_STEP_IND,
l_config_rec.CONTROL_LOT_ATTRIB_IND,
l_config_rec.OUT_OF_SPEC_LOT_STATUS,
l_config_rec.IN_SPEC_LOT_STATUS,
l_config_rec.SAMPLE_INV_TRANS_IND,
l_config_rec.API_ROUND_TRUN_IND,
l_config_rec.LOT_OPTIONAL_ON_SAMPLE,
l_text_code,
SYSDATE,
0,
0,
SYSDATE,
NULL,
l_config_rec.INV_TRANS_REASON_CODE,
l_config_rec.RESAMPLE_ACTION_CODE,
l_config_rec.RETEST_ACTION_CODE,
l_config_rec.CHOOSE_SPEC,
l_config_rec.AUTO_SAMPLE_IND,
l_config_rec.DELAYED_LOT_ENTRY,
l_organization_id,
l_out_spec_status_id,
l_in_spec_status_id,
l_reason_id,
l_lab_ind,
P_exact_match,
P_inc_opt,
P_version_control,
l_smpl_last_assigned,
l_smpl_assignment_type,
l_stbl_assignment_type,
l_stbl_last_assigned,
1
);
WHEN NEXT_CONFIG_INSERT THEN
NULL;
Update the Null Row if all config
rows successfully migrated.
=====================================*/
OPEN check_config_mig;
UPDATE gmd_quality_config
SET migrated_ind = 1
WHERE orgn_code IS NULL and migrated_ind IS NULL;
UPDATE gmd_quality_config
SET sample_assignment_type = l_p_assign_type,
sample_last_assigned = l_p_last_assign
WHERE organization_id = l_config.organization_id;
UPDATE gmd_sampling_plans_b
SET frequency_per = l_sampling_uom,
migrated_ind = 1
WHERE sampling_plan_id = l_sampling_plan.sampling_plan_id;
SELECT *
FROM gmd_samples
WHERE migrated_ind IS NULL
AND orgn_code IS NOT NULL;
SELECT sublot_ctl
FROM ic_item_mst_b
WHERE item_id = v_item_id;
SELECT sampling_event_id
FROM gmd_sampling_events
WHERE migrated_ind IS NULL;
SELECT organization_id, inventory_item_id, revision, lot_number,
subinventory, locator_id, parent_lot_number, org_id, material_detail_id
FROM gmd_samples
WHERE sampling_event_id = v_sampling_event_id
AND (migrated_ind = 1 OR organization_id IS NOT NULL);
SELECT result_id, qc_lab_orgn_code, lab_organization_id,
test_kit_item_id, test_kit_lot_no, test_kit_sublot_no,
test_kit_inv_item_id
FROM gmd_results
WHERE migrated_ind IS NULL;
SELECT org_id INTO l_org_id
FROM po_vendor_sites_all
WHERE vendor_site_id = v_samp_rec.supplier_site_id;
SELECT material_detail_id INTO l_material_detail_id
FROM gme_material_details
WHERE batch_id = v_samp_rec.batch_id
AND formulaline_id = v_samp_rec.formulaline_id;
Update gmd_samples.
==========================*/
UPDATE gmd_samples
SET organization_id = l_organization_id,
subinventory = l_subinventory,
locator_id = l_locator_id,
inventory_item_id = l_inventory_item_id,
parent_lot_number = l_parent_lot_number,
lab_organization_id = l_lab_organization_id,
lot_number = l_lot_number,
source_subinventory = l_source_subinv, -- Bug# 5261810
source_locator_id = l_source_locator_id, -- Bug# 5261810
storage_organization_id = l_storage_org_id,
storage_subinventory = l_store_subinv,
storage_locator_id = l_storage_locator_id,
org_id = l_org_id, -- Bug# 5261810
material_detail_id = l_material_detail_id, -- Bug# 5261810
migrated_ind = 1
WHERE sample_id = v_samp_rec.sample_id;
Log number of updates to gmd_samples.
==============================================*/
LOG_SAMPLE_COUNTS(p_migration_run_id, GMD_QC_MIG12.g_progress_ind);
Update gmd_sampling_events.
===============================*/
UPDATE gmd_sampling_events
SET organization_id = l_samp_data.organization_id,
inventory_item_id = l_samp_data.inventory_item_id,
lot_number = l_samp_data.lot_number,
subinventory = l_samp_data.subinventory,
locator_id = l_samp_data.locator_id,
revision = l_samp_data.revision,
parent_lot_number = l_samp_data.parent_lot_number,
org_id = l_samp_data.org_id, -- Bug# 5261810
material_detail_id = l_samp_data.material_detail_id, -- Bug# 5261810
migrated_ind = 1
WHERE sampling_event_id = v_samp_event.sampling_event_id;
Log number of updates to gmd_sampling_event.
==============================================*/
LOG_SAMPLE_COUNTS(p_migration_run_id, GMD_QC_MIG12.g_progress_ind);
Update gmd_results.
==========================*/
UPDATE gmd_results
SET test_kit_lot_number = l_lot_number,
test_kit_inv_item_id = l_inventory_item_id,
migrated_ind = 1
WHERE result_id = v_result.result_id;
Log number of updates to gmd_results.
==============================================*/
LOG_SAMPLE_COUNTS(p_migration_run_id, GMD_QC_MIG12.g_progress_ind);
ISPEC_DELETE_INVSPEC EXCEPTION;
WIP_SPEC_DELETE EXCEPTION;
CUST_SPEC_DELETE EXCEPTION;
SUP_SPEC_DELETE EXCEPTION;
MON_SPEC_DELETE EXCEPTION;
select rows that have not migrated.
Org id not null means either it was
migrated or row was created post-migration.
=======================================*/
-- Bug# 5097457
-- Removed owner_organization_id IS NULL from the where clause and added owner_orgn_code IS NOT NULL.
CURSOR get_specs IS
SELECT item_id, owner_organization_id, owner_orgn_code, spec_id
FROM gmd_specifications_b
WHERE migrated_ind IS NULL
AND owner_orgn_code IS NOT NULL;
SELECT * from gmd_inventory_spec_vrs
WHERE migrated_ind IS NULL
ORDER BY orgn_code;
SELECT item_id
FROM gmd_specifications_b
WHERE spec_id = p_spec_id;
SELECT sublot_ctl
FROM ic_item_mst_b
WHERE item_id = l_item_id;
SELECT spec_vr_id
FROM gmd_inventory_spec_vrs
WHERE orgn_code = l_sample_orgn_code
AND whse_code = l_inv_spec.whse_code
AND location = l_inv_spec.location
AND lot_no = l_inv_spec.lot_no
AND sublot_no = l_inv_spec.sublot_no;*/
SELECT spec_vr_id
FROM gmd_inventory_spec_vrs
WHERE orgn_code = l_sample_orgn_code
AND spec_id = l_inv_spec.spec_id
AND ( (whse_code IS NULL AND l_inv_spec.whse_code IS NULL) OR
(whse_code = l_inv_spec.whse_code)
)
AND ( (location IS NULL AND l_inv_spec.location IS NULL) OR
(location = l_inv_spec.location)
)
AND ( (lot_no IS NULL AND l_inv_spec.lot_no IS NULL) OR
(lot_no = l_inv_spec.lot_no)
)
AND ( (sublot_no IS NULL AND l_inv_spec.sublot_no IS NULL) OR
(sublot_no = l_inv_spec.sublot_no)
);
SELECT DISTINCT gse.orgn_code, gse.organization_id
FROM gmd_sampling_events gse, gmd_event_spec_disp gesd,
gmd_inventory_spec_vrs gisv
WHERE gse.sampling_event_id = gesd.sampling_event_id
AND gesd.spec_vr_id = gisv.spec_vr_id
AND gisv.spec_vr_id = v_spec_id;
SELECT * from gmd_wip_spec_vrs
WHERE migrated_ind IS NULL
--AND organization_id IS NULL
ORDER BY orgn_code;
SELECT DISTINCT gse.orgn_code, gse.organization_id
FROM gmd_sampling_events gse, gmd_event_spec_disp gesd,
gmd_wip_spec_vrs gwsv
WHERE gse.sampling_event_id = gesd.sampling_event_id
AND gesd.spec_vr_id = gwsv.spec_vr_id
AND gwsv.spec_vr_id = v_spec_id;
SELECT spec_vr_id
FROM gmd_wip_spec_vrs
WHERE orgn_code = l_sample_orgn_code;*/
SELECT spec_vr_id
FROM gmd_wip_spec_vrs
WHERE orgn_code = l_sample_orgn_code
AND spec_id = l_wip_spec.spec_id
AND ( (batch_id IS NULL AND l_wip_spec.batch_id IS NULL) OR
(batch_id = l_wip_spec.batch_id)
)
AND ( (recipe_id IS NULL AND l_wip_spec.recipe_id IS NULL) OR
(recipe_id = l_wip_spec.recipe_id)
)
AND ( (recipe_no IS NULL AND l_wip_spec.recipe_no IS NULL) OR
(recipe_no = l_wip_spec.recipe_no)
)
AND ( (formula_id IS NULL AND l_wip_spec.formula_id IS NULL) OR
(formula_id = l_wip_spec.formula_id)
)
AND ( (formula_no IS NULL AND l_wip_spec.formula_no IS NULL) OR
(formula_no = l_wip_spec.formula_no)
)
AND ( (formulaline_id IS NULL AND l_wip_spec.formulaline_id IS NULL) OR
(formulaline_id = l_wip_spec.formulaline_id)
)
AND ( (routing_id IS NULL AND l_wip_spec.routing_id IS NULL) OR
(routing_id = l_wip_spec.routing_id)
)
AND ( (routing_no IS NULL AND l_wip_spec.routing_no IS NULL) OR
(routing_no = l_wip_spec.routing_no)
)
AND ( (step_id IS NULL AND l_wip_spec.step_id IS NULL) OR
(step_id = l_wip_spec.step_id)
)
AND ( (oprn_id IS NULL AND l_wip_spec.oprn_id IS NULL) OR
(oprn_id = l_wip_spec.oprn_id)
)
AND ( (oprn_no IS NULL AND l_wip_spec.oprn_no IS NULL) OR
(oprn_no = l_wip_spec.oprn_no)
)
AND ( (charge IS NULL AND l_wip_spec.charge IS NULL) OR
(charge = l_wip_spec.charge)
);
SELECT * from gmd_customer_spec_vrs
WHERE migrated_ind IS NULL
ORDER BY orgn_code;
SELECT DISTINCT gse.orgn_code, gse.organization_id
FROM gmd_sampling_events gse, gmd_event_spec_disp gesd,
gmd_customer_spec_vrs gcsv
WHERE gse.sampling_event_id = gesd.sampling_event_id
AND gesd.spec_vr_id = gcsv.spec_vr_id
AND gcsv.spec_vr_id = v_spec_id;
SELECT spec_vr_id
FROM gmd_customer_spec_vrs
WHERE orgn_code = l_sample_orgn_code;*/
SELECT spec_vr_id
FROM gmd_customer_spec_vrs
WHERE orgn_code = l_sample_orgn_code
AND spec_id = l_cust_spec.spec_id
AND ( (cust_id IS NULL AND l_cust_spec.cust_id IS NULL) OR
(cust_id = l_cust_spec.cust_id)
)
AND ( (org_id IS NULL AND l_cust_spec.org_id IS NULL) OR
(org_id = l_cust_spec.org_id)
)
AND ( (order_id IS NULL AND l_cust_spec.order_id IS NULL) OR
(order_id = l_cust_spec.order_id)
)
AND ( (order_line IS NULL AND l_cust_spec.order_line IS NULL) OR
(order_line = l_cust_spec.order_line)
)
AND ( (order_line_id IS NULL AND l_cust_spec.order_line_id IS NULL) OR
(order_line_id = l_cust_spec.order_line_id)
)
AND ( (ship_to_site_id IS NULL AND l_cust_spec.ship_to_site_id IS NULL) OR
(ship_to_site_id = l_cust_spec.ship_to_site_id)
);
SELECT *
FROM gmd_supplier_spec_vrs
WHERE migrated_ind IS NULL
ORDER BY orgn_code;
SELECT DISTINCT gse.orgn_code, gse.organization_id
FROM gmd_sampling_events gse, gmd_event_spec_disp gesd,
gmd_supplier_spec_vrs gssv
WHERE gse.sampling_event_id = gesd.sampling_event_id
AND gesd.spec_vr_id = gssv.spec_vr_id
AND gssv.spec_vr_id = v_spec_id;
SELECT spec_vr_id
FROM gmd_supplier_spec_vrs
WHERE orgn_code = l_sample_orgn_code;*/
SELECT spec_vr_id
FROM gmd_supplier_spec_vrs
WHERE orgn_code = l_sample_orgn_code
AND spec_id = l_supl_spec.spec_id
AND ( (supplier_id IS NULL AND l_supl_spec.supplier_id IS NULL) OR
(supplier_id = l_supl_spec.supplier_id)
)
AND ( (supplier_site_id IS NULL AND l_supl_spec.supplier_site_id IS NULL) OR
(supplier_site_id = l_supl_spec.supplier_site_id)
)
AND ( (po_header_id IS NULL AND l_supl_spec.po_header_id IS NULL) OR
(po_header_id = l_supl_spec.po_header_id)
)
AND ( (po_line_id IS NULL AND l_supl_spec.po_line_id IS NULL) OR
(po_line_id = l_supl_spec.po_line_id)
);
SELECT *
FROM gmd_monitoring_spec_vrs
WHERE migrated_ind IS NULL
AND rule_type = 'L'
ORDER BY loct_orgn_code;
SELECT DISTINCT gse.orgn_code, gse.organization_id
FROM gmd_sampling_events gse, gmd_event_spec_disp gesd,
gmd_monitoring_spec_vrs gmsv
WHERE gse.sampling_event_id = gesd.sampling_event_id
AND gesd.spec_vr_id = gmsv.spec_vr_id
AND gmsv.spec_vr_id = v_spec_id;
SELECT spec_vr_id
FROM gmd_monitoring_spec_vrs
WHERE loct_orgn_code = l_sample_orgn_code
AND whse_code = l_mon_spec.whse_code
AND location = l_mon_spec.location;*/
SELECT spec_vr_id
FROM gmd_monitoring_spec_vrs
WHERE loct_orgn_code = l_sample_orgn_code
AND spec_id = l_mon_spec.spec_id
AND ( (whse_code IS NULL AND l_mon_spec.whse_code IS NULL) OR
(whse_code = l_mon_spec.whse_code)
)
AND ( (location IS NULL AND l_mon_spec.location IS NULL) OR
(location = l_mon_spec.location)
);
Update gmd_specifications_b.
==================================*/
-- Bug# 5097457
-- Added grade_code in the update statement
UPDATE gmd_specifications_b
SET inventory_item_id = l_spec_inv_item_id,
owner_organization_id = l_owner_org_id,
grade_code = grade,
migrated_ind = 1
WHERE spec_id = v_specs_b.spec_id;
Update gmd_inventory_spec_vrs
================================*/
UPDATE gmd_inventory_spec_vrs
SET organization_id = l_organization_id,
lot_number = l_lot_number,
parent_lot_number = l_parent_lot_number,
subinventory = l_subinventory,
locator_id = l_locator_id,
out_of_spec_lot_status_id = l_out_spec_status_id,
in_spec_lot_status_id = l_in_spec_status_id,
migrated_ind = 1
WHERE spec_vr_id = l_inv_spec.spec_vr_id;
Log the spec record and Delete it
as it is not tied to any Sample.
========================================*/
GMA_COMMON_LOGGING.gma_migration_central_log (
p_run_id => p_migration_run_id,
p_log_level => FND_LOG.LEVEL_EVENT,
p_message_token => 'GMD_MIG_SPEC_DELETE',
p_table_name => NULL,
p_context => 'Quality Specifications - gmd_inventory_spec_vrs',
p_token1 => 'TAB',
p_token2 => 'ORG',
p_token3 => 'LOT',
p_token4 => 'SUBLOT',
p_param1 => 'gmd_inventory_spec_vrs',
p_param2 => NVL(l_inv_spec.orgn_code,' '),
p_param3 => NVL(l_inv_spec.lot_no,' '),
p_param4 => NVL(l_inv_spec.sublot_no,' '),
p_app_short_name => 'GMD');
DELETE gmd_inventory_spec_vrs
WHERE spec_vr_id = l_inv_spec.spec_vr_id;
RAISE ISPEC_DELETE_INVSPEC; -- goes to next invspec record.
UPDATE gmd_inventory_spec_vrs
SET organization_id = l_sample_organization_id,
orgn_code = l_sample_orgn_code,
lot_number = l_lot_number,
parent_lot_number = l_parent_lot_number,
subinventory = l_subinventory,
locator_id = l_locator_id,
out_of_spec_lot_status_id = l_out_spec_status_id,
in_spec_lot_status_id = l_in_spec_status_id,
migrated_ind = 1
WHERE spec_vr_id = l_inv_spec.spec_vr_id;
INSERT INTO gmd_inventory_spec_vrs (
SPEC_VR_ID,
SPEC_ID,
ORGN_CODE,
LOT_ID,
LOT_NO,
SUBLOT_NO,
WHSE_CODE,
LOCATION,
SPEC_VR_STATUS,
START_DATE,
END_DATE,
SAMPLING_PLAN_ID,
SAMPLE_INV_TRANS_IND,
CONTROL_LOT_ATTRIB_IND,
LOT_OPTIONAL_ON_SAMPLE,
IN_SPEC_LOT_STATUS,
OUT_OF_SPEC_LOT_STATUS,
CONTROL_BATCH_STEP_IND,
COA_TYPE,
COA_AT_SHIP_IND,
COA_AT_INVOICE_IND,
COA_REQ_FROM_SUPL_IND,
DELETE_MARK,
TEXT_CODE,
ATTRIBUTE_CATEGORY,
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,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
AUTO_SAMPLE_IND,
DELAYED_LOT_ENTRY,
MIGRATED_IND,
ERECORD_SPEC_VR_ID,
ORGANIZATION_ID,
LOT_NUMBER,
PARENT_LOT_NUMBER,
SUBINVENTORY,
LOCATOR_ID,
OUT_OF_SPEC_LOT_STATUS_ID,
IN_SPEC_LOT_STATUS_ID
)
VALUES
(
gmd_qc_spec_vr_id_s.nextval,
l_inv_spec.SPEC_ID,
l_sample_orgn_code,
l_inv_spec.LOT_ID,
l_inv_spec.LOT_NO,
l_inv_spec.SUBLOT_NO,
l_inv_spec.WHSE_CODE,
l_inv_spec.LOCATION,
l_inv_spec.SPEC_VR_STATUS,
l_inv_spec.START_DATE,
l_inv_spec.END_DATE,
l_inv_spec.SAMPLING_PLAN_ID,
l_inv_spec.SAMPLE_INV_TRANS_IND,
l_inv_spec.CONTROL_LOT_ATTRIB_IND,
l_inv_spec.LOT_OPTIONAL_ON_SAMPLE,
l_inv_spec.IN_SPEC_LOT_STATUS,
l_inv_spec.OUT_OF_SPEC_LOT_STATUS,
l_inv_spec.CONTROL_BATCH_STEP_IND,
l_inv_spec.COA_TYPE,
l_inv_spec.COA_AT_SHIP_IND,
l_inv_spec.COA_AT_INVOICE_IND,
l_inv_spec.COA_REQ_FROM_SUPL_IND,
l_inv_spec.DELETE_MARK,
l_text_code,
l_inv_spec.ATTRIBUTE_CATEGORY,
l_inv_spec.ATTRIBUTE1, l_inv_spec.ATTRIBUTE2, l_inv_spec.ATTRIBUTE3, l_inv_spec.ATTRIBUTE4,
l_inv_spec.ATTRIBUTE5, l_inv_spec.ATTRIBUTE6, l_inv_spec.ATTRIBUTE7, l_inv_spec.ATTRIBUTE8,
l_inv_spec.ATTRIBUTE9, l_inv_spec.ATTRIBUTE10, l_inv_spec.ATTRIBUTE11, l_inv_spec.ATTRIBUTE12,
l_inv_spec.ATTRIBUTE13, l_inv_spec.ATTRIBUTE14, l_inv_spec.ATTRIBUTE15, l_inv_spec.ATTRIBUTE16,
l_inv_spec.ATTRIBUTE17, l_inv_spec.ATTRIBUTE18, l_inv_spec.ATTRIBUTE19, l_inv_spec.ATTRIBUTE20,
l_inv_spec.ATTRIBUTE21, l_inv_spec.ATTRIBUTE22, l_inv_spec.ATTRIBUTE23, l_inv_spec.ATTRIBUTE24,
l_inv_spec.ATTRIBUTE25, l_inv_spec.ATTRIBUTE26, l_inv_spec.ATTRIBUTE27, l_inv_spec.ATTRIBUTE28,
l_inv_spec.ATTRIBUTE29, l_inv_spec.ATTRIBUTE30,
SYSDATE,
0,
0,
SYSDATE,
NULL,
l_inv_spec.AUTO_SAMPLE_IND,
l_inv_spec.DELAYED_LOT_ENTRY,
1,
l_inv_spec.SPEC_VR_ID,
l_sample_organization_id,
l_lot_number,
l_parent_lot_number,
l_subinventory,
l_locator_id,
l_out_spec_status_id,
l_in_spec_status_id
);
WHEN ISPEC_DELETE_INVSPEC THEN
NULL;
SELECT material_detail_id INTO l_material_detail_id
FROM gme_material_details
WHERE batch_id = l_wip_spec.batch_id
AND formulaline_id = l_wip_spec.formulaline_id;
Update gmd_wip_spec_vrs.
=====================================*/
UPDATE gmd_wip_spec_vrs
SET out_of_spec_lot_status_id = l_out_spec_status_id,
in_spec_lot_status_id = l_in_spec_status_id,
organization_id = l_organization_id,
material_detail_id = l_material_detail_id, -- Bug# 5482253
migrated_ind = 1
WHERE spec_vr_id = l_wip_spec.spec_vr_id;
Log the spec record and Delete it.
========================================*/
/*==========================================
This is not marked as an error.
==========================================*/
DELETE gmd_wip_spec_vrs
WHERE spec_vr_id = l_wip_spec.spec_vr_id;
RAISE WIP_SPEC_DELETE;
Update gmd_wip_spec_vrs.
=====================================*/
UPDATE gmd_wip_spec_vrs
SET out_of_spec_lot_status_id = l_out_spec_status_id,
in_spec_lot_status_id = l_in_spec_status_id,
organization_id = l_sample_organization_id,
orgn_code = l_sample_orgn_code,
material_detail_id = l_material_detail_id, -- Bug# 5482253
migrated_ind = 1
WHERE spec_vr_id = l_wip_spec.spec_vr_id;
INSERT INTO gmd_wip_spec_vrs
(
SPEC_VR_ID,
SPEC_ID,
ORGN_CODE,
BATCH_ID,
RECIPE_ID,
RECIPE_NO,
RECIPE_VERSION,
FORMULA_ID,
FORMULALINE_ID,
FORMULA_NO,
FORMULA_VERS,
ROUTING_ID,
ROUTING_NO,
ROUTING_VERS,
STEP_ID,
STEP_NO,
OPRN_ID,
OPRN_NO,
OPRN_VERS,
CHARGE,
SPEC_VR_STATUS,
START_DATE,
END_DATE,
SAMPLING_PLAN_ID,
SAMPLE_INV_TRANS_IND,
LOT_OPTIONAL_ON_SAMPLE,
CONTROL_LOT_ATTRIB_IND,
OUT_OF_SPEC_LOT_STATUS,
IN_SPEC_LOT_STATUS,
COA_TYPE,
CONTROL_BATCH_STEP_IND,
COA_AT_SHIP_IND,
COA_AT_INVOICE_IND,
COA_REQ_FROM_SUPL_IND,
DELETE_MARK,
TEXT_CODE,
ATTRIBUTE_CATEGORY,
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,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
AUTO_SAMPLE_IND,
DELAYED_LOT_ENTRY,
MIGRATED_IND,
ERECORD_SPEC_VR_ID,
ORGANIZATION_ID,
OUT_OF_SPEC_LOT_STATUS_ID,
IN_SPEC_LOT_STATUS_ID,
MATERIAL_DETAIL_ID -- Bug# 5482253
)
VALUES
(
gmd_qc_spec_vr_id_s.nextval,
l_wip_spec.SPEC_ID,
l_sample_orgn_code,
l_wip_spec.BATCH_ID,
l_wip_spec.RECIPE_ID,
l_wip_spec.RECIPE_NO,
l_wip_spec.RECIPE_VERSION,
l_wip_spec.FORMULA_ID,
l_wip_spec.FORMULALINE_ID,
l_wip_spec.FORMULA_NO,
l_wip_spec.FORMULA_VERS,
l_wip_spec.ROUTING_ID,
l_wip_spec.ROUTING_NO,
l_wip_spec.ROUTING_VERS,
l_wip_spec.STEP_ID,
l_wip_spec.STEP_NO,
l_wip_spec.OPRN_ID,
l_wip_spec.OPRN_NO,
l_wip_spec.OPRN_VERS,
l_wip_spec.CHARGE,
l_wip_spec.SPEC_VR_STATUS,
l_wip_spec.START_DATE,
l_wip_spec.END_DATE,
l_wip_spec.SAMPLING_PLAN_ID,
l_wip_spec.SAMPLE_INV_TRANS_IND,
l_wip_spec.LOT_OPTIONAL_ON_SAMPLE,
l_wip_spec.CONTROL_LOT_ATTRIB_IND,
l_wip_spec.OUT_OF_SPEC_LOT_STATUS,
l_wip_spec.IN_SPEC_LOT_STATUS,
l_wip_spec.COA_TYPE,
l_wip_spec.CONTROL_BATCH_STEP_IND,
l_wip_spec.COA_AT_SHIP_IND,
l_wip_spec.COA_AT_INVOICE_IND,
l_wip_spec.COA_REQ_FROM_SUPL_IND,
l_wip_spec.DELETE_MARK,
l_text_code,
l_wip_spec.ATTRIBUTE_CATEGORY,
l_wip_spec.ATTRIBUTE1, l_wip_spec.ATTRIBUTE2, l_wip_spec.ATTRIBUTE3, l_wip_spec.ATTRIBUTE4,
l_wip_spec.ATTRIBUTE5, l_wip_spec.ATTRIBUTE6, l_wip_spec.ATTRIBUTE7, l_wip_spec.ATTRIBUTE8,
l_wip_spec.ATTRIBUTE9, l_wip_spec.ATTRIBUTE10, l_wip_spec.ATTRIBUTE11, l_wip_spec.ATTRIBUTE12,
l_wip_spec.ATTRIBUTE13, l_wip_spec.ATTRIBUTE14, l_wip_spec.ATTRIBUTE15, l_wip_spec.ATTRIBUTE16,
l_wip_spec.ATTRIBUTE17, l_wip_spec.ATTRIBUTE18, l_wip_spec.ATTRIBUTE19, l_wip_spec.ATTRIBUTE20,
l_wip_spec.ATTRIBUTE21, l_wip_spec.ATTRIBUTE22, l_wip_spec.ATTRIBUTE23, l_wip_spec.ATTRIBUTE24,
l_wip_spec.ATTRIBUTE25, l_wip_spec.ATTRIBUTE26, l_wip_spec.ATTRIBUTE27, l_wip_spec.ATTRIBUTE28,
l_wip_spec.ATTRIBUTE29, l_wip_spec.ATTRIBUTE30,
SYSDATE,
0,
0,
SYSDATE,
NULL,
l_wip_spec.AUTO_SAMPLE_IND,
l_wip_spec.DELAYED_LOT_ENTRY,
1,
l_wip_spec.SPEC_VR_ID,
l_sample_organization_id,
l_out_spec_status_id,
l_in_spec_status_id,
l_material_detail_id -- Bug# 5482253
);
WHEN WIP_SPEC_DELETE THEN
GMA_COMMON_LOGGING.gma_migration_central_log (
p_run_id => p_migration_run_id,
p_log_level => FND_LOG.LEVEL_EVENT,
p_message_token => 'GMD_MIG_COMMON_SPEC_DELETE',
p_table_name => NULL,
p_context => 'Quality Specifications - gmd_wip_spec_vrs',
p_token1 => 'TAB',
p_param1 => 'gmd_wip_spec_vrs',
p_app_short_name => 'GMD');
Update gmd_customer_spec_vrs.
====================================*/
UPDATE gmd_customer_spec_vrs
SET migrated_ind = 1
WHERE spec_vr_id = l_cust_spec.spec_vr_id;
Log the spec record and Delete it.
========================================*/
/*==========================================
This is not marked as an error.
==========================================*/
DELETE gmd_customer_spec_vrs
WHERE spec_vr_id = l_wip_spec.spec_vr_id;
RAISE CUST_SPEC_DELETE;
Update gmd_customer_spec_vrs.
=====================================*/
UPDATE gmd_customer_spec_vrs
SET orgn_code = l_sample_orgn_code,
organization_id = l_sample_organization_id,
migrated_ind = 1
WHERE spec_vr_id = l_cust_spec.spec_vr_id;
INSERT INTO gmd_customer_spec_vrs
(
SPEC_VR_ID,
SPEC_ID,
ORGN_CODE,
CUST_ID,
ORDER_ID,
ORDER_LINE,
ORDER_LINE_ID,
SHIP_TO_SITE_ID,
ORG_ID,
SPEC_VR_STATUS,
START_DATE,
END_DATE,
SAMPLING_PLAN_ID,
SAMPLE_INV_TRANS_IND,
LOT_OPTIONAL_ON_SAMPLE,
COA_TYPE,
COA_AT_SHIP_IND,
COA_AT_INVOICE_IND,
COA_REQ_FROM_SUPL_IND,
DELETE_MARK,
TEXT_CODE,
ATTRIBUTE_CATEGORY,
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,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
MIGRATED_IND,
ERECORD_SPEC_VR_ID,
ORGANIZATION_ID
)
VALUES
(
gmd_qc_spec_vr_id_s.nextval,
l_cust_spec.SPEC_ID,
l_sample_orgn_code,
l_cust_spec.CUST_ID,
l_cust_spec.ORDER_ID,
l_cust_spec.ORDER_LINE,
l_cust_spec.ORDER_LINE_ID,
l_cust_spec.SHIP_TO_SITE_ID,
l_cust_spec.ORG_ID,
l_cust_spec.SPEC_VR_STATUS,
l_cust_spec.START_DATE,
l_cust_spec.END_DATE,
l_cust_spec.SAMPLING_PLAN_ID,
l_cust_spec.SAMPLE_INV_TRANS_IND,
l_cust_spec.LOT_OPTIONAL_ON_SAMPLE,
l_cust_spec.COA_TYPE,
l_cust_spec.COA_AT_SHIP_IND,
l_cust_spec.COA_AT_INVOICE_IND,
l_cust_spec.COA_REQ_FROM_SUPL_IND,
l_cust_spec.DELETE_MARK,
l_text_code,
l_cust_spec.ATTRIBUTE_CATEGORY,
l_cust_spec.ATTRIBUTE1, l_cust_spec.ATTRIBUTE2, l_cust_spec.ATTRIBUTE3, l_cust_spec.ATTRIBUTE4,
l_cust_spec.ATTRIBUTE5, l_cust_spec.ATTRIBUTE6, l_cust_spec.ATTRIBUTE7, l_cust_spec.ATTRIBUTE8,
l_cust_spec.ATTRIBUTE9, l_cust_spec.ATTRIBUTE10, l_cust_spec.ATTRIBUTE11, l_cust_spec.ATTRIBUTE12,
l_cust_spec.ATTRIBUTE13, l_cust_spec.ATTRIBUTE14, l_cust_spec.ATTRIBUTE15, l_cust_spec.ATTRIBUTE16,
l_cust_spec.ATTRIBUTE17, l_cust_spec.ATTRIBUTE18, l_cust_spec.ATTRIBUTE19, l_cust_spec.ATTRIBUTE20,
l_cust_spec.ATTRIBUTE21, l_cust_spec.ATTRIBUTE22, l_cust_spec.ATTRIBUTE23, l_cust_spec.ATTRIBUTE24,
l_cust_spec.ATTRIBUTE25, l_cust_spec.ATTRIBUTE26, l_cust_spec.ATTRIBUTE27, l_cust_spec.ATTRIBUTE28,
l_cust_spec.ATTRIBUTE29, l_cust_spec.ATTRIBUTE30,
SYSDATE,
0,
0,
SYSDATE,
NULL,
1,
l_cust_spec.SPEC_VR_ID,
l_sample_organization_id
);
WHEN CUST_SPEC_DELETE THEN
GMA_COMMON_LOGGING.gma_migration_central_log (
p_run_id => p_migration_run_id,
p_log_level => FND_LOG.LEVEL_ERROR,
p_message_token => 'GMD_MIG_COMMON_SPEC_DELETE',
p_table_name => NULL,
p_context => 'Quality Specifications - gmd_customer_spec_vrs',
p_token1 => 'TAB',
p_param1 => 'gmd_customer_spec_vrs',
p_app_short_name => 'GMD');
-- Added code to select org_id for Supplier VRs
l_supl_org_id := NULL;
SELECT org_id INTO l_supl_org_id
FROM po_vendor_sites_all
WHERE vendor_site_id = l_supl_spec.supplier_site_id;
Update gmd_supplier_spec_vrs.
=====================================*/
-- Bug# 5482253
-- Added org_id in the update statement
UPDATE gmd_supplier_spec_vrs
SET out_of_spec_lot_status_id = l_out_spec_status_id,
in_spec_lot_status_id = l_in_spec_status_id,
org_id = l_supl_org_id,
migrated_ind = 1
WHERE spec_vr_id = l_supl_spec.spec_vr_id;
Log the spec record and Delete it.
========================================*/
/*==========================================
This is not marked as an error.
==========================================*/
DELETE gmd_supplier_spec_vrs
WHERE spec_vr_id = l_supl_spec.spec_vr_id;
RAISE SUP_SPEC_DELETE;
-- Added org_id in the update statement
UPDATE gmd_supplier_spec_vrs
SET out_of_spec_lot_status_id = l_out_spec_status_id,
in_spec_lot_status_id = l_in_spec_status_id,
organization_id = l_sample_organization_id,
orgn_code = l_sample_orgn_code,
org_id = l_supl_org_id,
migrated_ind = 1
WHERE spec_vr_id = l_supl_spec.spec_vr_id;
INSERT INTO gmd_supplier_spec_vrs
(
PO_HEADER_ID,
SPEC_VR_ID,
SPEC_ID,
ORGN_CODE,
SUPPLIER_ID,
SUPPLIER_SITE_ID,
PO_LINE_ID,
SPEC_VR_STATUS,
START_DATE,
END_DATE,
SAMPLING_PLAN_ID,
SAMPLE_INV_TRANS_IND,
LOT_OPTIONAL_ON_SAMPLE,
COA_TYPE,
COA_AT_SHIP_IND,
COA_AT_INVOICE_IND,
COA_REQ_FROM_SUPL_IND,
DELETE_MARK,
TEXT_CODE,
ATTRIBUTE_CATEGORY,
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,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
AUTO_SAMPLE_IND,
CONTROL_LOT_ATTRIB_IND,
IN_SPEC_LOT_STATUS,
OUT_OF_SPEC_LOT_STATUS,
DELAYED_LOT_ENTRY,
MIGRATED_IND,
ERECORD_SPEC_VR_ID,
ORGANIZATION_ID,
ORG_ID,
OUT_OF_SPEC_LOT_STATUS_ID,
IN_SPEC_LOT_STATUS_ID
)
VALUES
(
l_supl_spec.PO_HEADER_ID,
gmd_qc_spec_vr_id_s.nextval,
l_supl_spec.SPEC_ID,
l_sample_orgn_code,
l_supl_spec.SUPPLIER_ID,
l_supl_spec.SUPPLIER_SITE_ID,
l_supl_spec.PO_LINE_ID,
l_supl_spec.SPEC_VR_STATUS,
l_supl_spec.START_DATE,
l_supl_spec.END_DATE,
l_supl_spec.SAMPLING_PLAN_ID,
l_supl_spec.SAMPLE_INV_TRANS_IND,
l_supl_spec.LOT_OPTIONAL_ON_SAMPLE,
l_supl_spec.COA_TYPE,
l_supl_spec.COA_AT_SHIP_IND,
l_supl_spec.COA_AT_INVOICE_IND,
l_supl_spec.COA_REQ_FROM_SUPL_IND,
l_supl_spec.DELETE_MARK,
l_text_code,
l_supl_spec.ATTRIBUTE_CATEGORY,
l_supl_spec.ATTRIBUTE1, l_supl_spec.ATTRIBUTE2, l_supl_spec.ATTRIBUTE3, l_supl_spec.ATTRIBUTE4,
l_supl_spec.ATTRIBUTE5, l_supl_spec.ATTRIBUTE6, l_supl_spec.ATTRIBUTE7, l_supl_spec.ATTRIBUTE8,
l_supl_spec.ATTRIBUTE9, l_supl_spec.ATTRIBUTE10, l_supl_spec.ATTRIBUTE11, l_supl_spec.ATTRIBUTE12,
l_supl_spec.ATTRIBUTE13, l_supl_spec.ATTRIBUTE14, l_supl_spec.ATTRIBUTE15, l_supl_spec.ATTRIBUTE16,
l_supl_spec.ATTRIBUTE17, l_supl_spec.ATTRIBUTE18, l_supl_spec.ATTRIBUTE19, l_supl_spec.ATTRIBUTE20,
l_supl_spec.ATTRIBUTE21, l_supl_spec.ATTRIBUTE22, l_supl_spec.ATTRIBUTE23, l_supl_spec.ATTRIBUTE24,
l_supl_spec.ATTRIBUTE25, l_supl_spec.ATTRIBUTE26, l_supl_spec.ATTRIBUTE27, l_supl_spec.ATTRIBUTE28,
l_supl_spec.ATTRIBUTE29, l_supl_spec.ATTRIBUTE30,
SYSDATE,
0,
0,
SYSDATE,
NULL,
l_supl_spec.AUTO_SAMPLE_IND,
l_supl_spec.CONTROL_LOT_ATTRIB_IND,
l_supl_spec.IN_SPEC_LOT_STATUS,
l_supl_spec.OUT_OF_SPEC_LOT_STATUS,
l_supl_spec.DELAYED_LOT_ENTRY,
1,
l_supl_spec.SPEC_VR_ID,
l_sample_organization_id,
l_supl_org_id, -- Bug# 5482253
l_out_spec_status_id,
l_in_spec_status_id
);
WHEN SUP_SPEC_DELETE THEN
GMA_COMMON_LOGGING.gma_migration_central_log (
p_run_id => p_migration_run_id,
p_log_level => FND_LOG.LEVEL_ERROR,
p_message_token => 'GMD_MIG_COMMON_SPEC_DELETE',
p_table_name => NULL,
p_context => 'Quality Specifications - gmd_supplier_spec_vrs',
p_token1 => 'TAB',
p_param1 => 'gmd_supplier_spec_vrs',
p_app_short_name => 'GMD');
UPDATE gmd_monitoring_spec_vrs
SET locator_organization_id = l_organization_id,
subinventory = l_subinventory,
locator_id = l_locator_id,
migrated_ind = 1
WHERE spec_vr_id = l_mon_spec.spec_vr_id;
Log the spec record and Delete it.
========================================*/
/*==========================================
This is not marked as an error.
==========================================*/
DELETE gmd_monitoring_spec_vrs
WHERE spec_vr_id = l_mon_spec.spec_vr_id;
RAISE MON_SPEC_DELETE;
UPDATE gmd_monitoring_spec_vrs
SET locator_organization_id = l_sample_organization_id,
loct_orgn_code = l_sample_orgn_code,
subinventory = l_subinventory,
locator_id = l_locator_id,
migrated_ind = 1
WHERE spec_vr_id = l_mon_spec.spec_vr_id;
INSERT INTO gmd_monitoring_spec_vrs
(
SPEC_VR_ID,
SPEC_ID,
RULE_TYPE,
LOCT_ORGN_CODE,
WHSE_CODE,
LOCATION,
RESOURCES,
RESOURCE_ORGN_CODE,
RESOURCE_INSTANCE_ID,
SPEC_VR_STATUS,
START_DATE,
END_DATE,
SAMPLING_PLAN_ID,
DELETE_MARK,
TEXT_CODE,
ATTRIBUTE_CATEGORY,
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,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
MIGRATED_IND,
ERECORD_SPEC_VR_ID,
LOCATOR_ORGANIZATION_ID,
SUBINVENTORY,
LOCATOR_ID,
RESOURCE_ORGANIZATION_ID
)
VALUES
(
gmd_qc_spec_vr_id_s.nextval,
l_mon_spec.SPEC_ID,
l_mon_spec.RULE_TYPE,
l_sample_orgn_code,
l_mon_spec.WHSE_CODE,
l_mon_spec.LOCATION,
l_mon_spec.RESOURCES,
l_mon_spec.RESOURCE_ORGN_CODE,
l_mon_spec.RESOURCE_INSTANCE_ID,
l_mon_spec.SPEC_VR_STATUS,
l_mon_spec.START_DATE,
l_mon_spec.END_DATE,
l_mon_spec.SAMPLING_PLAN_ID,
l_mon_spec.DELETE_MARK,
l_text_code,
l_mon_spec.ATTRIBUTE_CATEGORY,
l_mon_spec.ATTRIBUTE1, l_mon_spec.ATTRIBUTE2, l_mon_spec.ATTRIBUTE3, l_mon_spec.ATTRIBUTE4,
l_mon_spec.ATTRIBUTE5, l_mon_spec.ATTRIBUTE6, l_mon_spec.ATTRIBUTE7, l_mon_spec.ATTRIBUTE8,
l_mon_spec.ATTRIBUTE9, l_mon_spec.ATTRIBUTE10, l_mon_spec.ATTRIBUTE11, l_mon_spec.ATTRIBUTE12,
l_mon_spec.ATTRIBUTE13, l_mon_spec.ATTRIBUTE14, l_mon_spec.ATTRIBUTE15, l_mon_spec.ATTRIBUTE16,
l_mon_spec.ATTRIBUTE17, l_mon_spec.ATTRIBUTE18, l_mon_spec.ATTRIBUTE19, l_mon_spec.ATTRIBUTE20,
l_mon_spec.ATTRIBUTE21, l_mon_spec.ATTRIBUTE22, l_mon_spec.ATTRIBUTE23, l_mon_spec.ATTRIBUTE24,
l_mon_spec.ATTRIBUTE25, l_mon_spec.ATTRIBUTE26, l_mon_spec.ATTRIBUTE27, l_mon_spec.ATTRIBUTE28,
l_mon_spec.ATTRIBUTE29, l_mon_spec.ATTRIBUTE30,
SYSDATE,
0,
0,
SYSDATE,
NULL,
1,
l_mon_spec.SPEC_VR_ID,
l_sample_organization_id,
l_subinventory,
l_locator_id,
l_mon_spec.RESOURCE_ORGANIZATION_ID
);
WHEN MON_SPEC_DELETE THEN
GMA_COMMON_LOGGING.gma_migration_central_log (
p_run_id => p_migration_run_id,
p_log_level => FND_LOG.LEVEL_ERROR,
p_message_token => 'GMD_MIG_COMMON_SPEC_DELETE',
p_table_name => NULL,
p_context => 'Quality Specifications - gmd_monitoring_spec_vrs',
p_token1 => 'TAB',
p_param1 => 'gmd_monitoring_spec_vrs',
p_app_short_name => 'GMD');
SELECT orgn_code, organization_id, item_id, qc_lab_orgn_code,
lab_organization_id, ss_id
FROM gmd_stability_studies_b
WHERE migrated_ind IS NULL
AND orgn_code IS NOT NULL;
SELECT storage_plan_detail_id, whse_code, location
FROM gmd_storage_plan_details
WHERE migrated_ind IS NULL
AND storage_organization_id IS NULL;
SELECT distinct gssb.orgn_code, gsv.package_id
FROM gmd_stability_studies_b gssb, gmd_ss_variants gsv
WHERE gssb.ss_id = gsv.ss_id
AND gsv.package_id = p_package_id;
SELECT *
FROM gmd_ss_storage_package
WHERE migrated_ind IS NULL
AND organization_id IS NULL;
SELECT gmd_qc_ss_stor_pack_id_s.nextval
FROM DUAL;
SELECT default_stability_study_org
FROM gmd_migrate_parms;
SELECT plant_code, source_organization_id,
lot_id, ss_id, source_id
FROM gmd_ss_material_sources
WHERE migrated_ind IS NULL;
SELECT orgn_code, item_id
FROM gmd_stability_studies_b
WHERE ss_id = p_ss_id;
SELECT variant_id, storage_whse_code, storage_location,
storage_organization_id, storage_subinventory, storage_locator_id
FROM gmd_ss_variants
WHERE migrated_ind IS NULL;
Select also where organization id is
null to not affect records added after
the migration in case migration is
run again out of sequence.
=========================================*/
CURSOR get_ss_history IS
SELECT storage_history_id, whse_code, location,
organization_id, subinventory, locator_id
FROM gmd_ss_storage_history
WHERE migrated_ind IS NULL
AND organization_id IS NULL;
Update gmd_storage_plan_details.
===================================*/
UPDATE gmd_storage_plan_details
SET storage_organization_id = l_plan_org_id,
storage_subinventory = l_plan_subinv,
storage_locator_id = l_plan_locator_id,
migrated_ind = 1
WHERE storage_plan_detail_id = l_plan_det.storage_plan_detail_id;
Log number of updates to gmd_storage_plan_details.
=====================================================*/
LOG_SS_COUNTS(p_migration_run_id, GMD_QC_MIG12.g_progress_ind);
UPDATE gmd_stability_studies_b
SET inventory_item_id = l_inventory_item_id,
organization_id = l_stab_org_id,
migrated_ind = 1
WHERE ss_id = l_stab_rec.ss_id;
-- so that the lot_number field is updated with l_lot_number.
/*ELSE
-- Bug# 5462876
-- if the item is not sublot controlled assign lot_number instead of parent_lot_number. Added nvl to the below statement.
l_lot_number := NVL(l_parent_lot_number, l_lot_number); */
Update gmd_ss_material_sources.
==================================*/
UPDATE gmd_ss_material_sources
SET source_organization_id = l_plant_org_id,
lot_number = l_lot_number,
migrated_ind = 1
WHERE source_id = l_matl_rec.source_id
AND ss_id = l_matl_rec.ss_id;
Update gmd_ss_variants.
*===============================*/
UPDATE gmd_ss_variants
SET storage_organization_id = l_storage_organization_id,
storage_subinventory = l_storage_subinventory,
storage_locator_id = l_storage_locator_id,
migrated_ind = 1
WHERE variant_id = l_var_rec.variant_id;
stability studies update this record with the
orgn from the first stability study. For any
other stability studies create a new
gmd_ss_storage_package record.
=================================================*/
OPEN get_stab_study_org(l_store_pack.package_id);
Update gmd_ss_storage_package
================================*/
UPDATE gmd_ss_storage_package
SET organization_id = l_def_ss_org_id,
inventory_item_id = l_store_pack_invitem_id,
migrated_ind = 1
WHERE package_id = l_store_pack.package_id;
Update gmd_ss_storage_package
using Stability study org.
================================*/
UPDATE gmd_ss_storage_package
SET organization_id = l_ss_org_id,
inventory_item_id = l_store_pack_invitem_id,
migrated_ind = 1
WHERE package_id = l_store_pack.package_id;
Insert gmd_ss_storage_package.
================================*/
OPEN get_pack_seq;
INSERT INTO gmd_ss_storage_package (
PACKAGE_ID,
FORMULA_ID,
QUANTITY,
UOM,
TEXT_CODE,
ATTRIBUTE_CATEGORY,
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,
DELETE_MARK,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ITEM_ID,
PACKAGE_NAME,
MIGRATED_IND,
ORGANIZATION_ID,
QUANTITY_UOM,
INVENTORY_ITEM_ID
)
VALUES (
l_package_id,
l_store_pack.FORMULA_ID,
l_store_pack.QUANTITY,
l_store_pack.UOM,
l_text_code,
l_store_pack.ATTRIBUTE_CATEGORY,
l_store_pack.ATTRIBUTE1, l_store_pack.ATTRIBUTE2, l_store_pack.ATTRIBUTE3,
l_store_pack.ATTRIBUTE4, l_store_pack.ATTRIBUTE5, l_store_pack.ATTRIBUTE6,
l_store_pack.ATTRIBUTE7, l_store_pack.ATTRIBUTE8, l_store_pack.ATTRIBUTE9,
l_store_pack.ATTRIBUTE10, l_store_pack.ATTRIBUTE11, l_store_pack.ATTRIBUTE12,
l_store_pack.ATTRIBUTE13, l_store_pack.ATTRIBUTE14, l_store_pack.ATTRIBUTE15,
l_store_pack.ATTRIBUTE16, l_store_pack.ATTRIBUTE17, l_store_pack.ATTRIBUTE18,
l_store_pack.ATTRIBUTE19, l_store_pack.ATTRIBUTE20, l_store_pack.ATTRIBUTE21,
l_store_pack.ATTRIBUTE22, l_store_pack.ATTRIBUTE23, l_store_pack.ATTRIBUTE24,
l_store_pack.ATTRIBUTE25, l_store_pack.ATTRIBUTE26, l_store_pack.ATTRIBUTE27,
l_store_pack.ATTRIBUTE28, l_store_pack.ATTRIBUTE29, l_store_pack.ATTRIBUTE30,
l_store_pack.DELETE_MARK,
SYSDATE,
0,
0,
SYSDATE,
0,
l_store_pack.ITEM_ID,
l_store_pack.PACKAGE_NAME,
1,
l_ss_org_id,
l_store_pack.QUANTITY_UOM,
l_store_pack.INVENTORY_ITEM_ID
);
Update gmd_ss_variants to point
to new package record.
================================*/
UPDATE gmd_ss_variants
SET package_id = l_package_id
WHERE package_id = l_store_pack.package_id
AND ss_id IN (select ss_id from gmd_stability_studies_b where orgn_code = l_stab_orgn_code);
Update gmd_ss_storage_history.
*===================================*/
UPDATE gmd_ss_storage_history
SET subinventory = l_hist_subinventory,
locator_id = l_hist_locator_id,
organization_id = l_hist_org_id,
migrated_ind = 1
WHERE storage_history_id = l_hist_rec.storage_history_id;