[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT inventory_item_id
FROM gr_item_general_mig
WHERE item_code = p_item_code
AND organization_id = p_organization_id;
SELECT alpha_value
FROM gr_item_properties
WHERE label_code = '14002'
AND property_id = 'UNCLSS'
AND item_code = p_item_code;
SELECT hazard_class_id
FROM po_hazard_classes
WHERE hazard_class = l_hazard_class;
SELECT 'UN'||TO_CHAR(number_value)
FROM gr_item_properties
WHERE label_code = '14001'
AND property_id = 'UNNUMB'
AND item_code = p_item_code;
SELECT un_number_id
FROM po_un_numbers
WHERE un_number = l_un_number;
l_recs_inserted NUMBER:=0;
SELECT organization_id
FROM sy_orgn_mst_b
WHERE master_organization_id IS NULL and
regulatory_org_ind = 'Y'and
organization_id is not null; --in case the org is not migrated then this indicates that
SELECT organization_id
FROM sy_orgn_mst_b
WHERE master_organization_id IS NOT NULL and
regulatory_org_ind = 'Y' and
organization_id is not null;
SELECT 1
FROM gr_item_general;
/* Select master orgs that have not yet been migrated - master orgs get migrated first*/
OPEN c_get_master_orgs;
INSERT INTO gr_item_general_mig
(
item_code,
organization_id,
inventory_item_id,
migration_ind
)
SELECT
a.item_code,
l_organization_id,
NULL,
NULL
FROM gr_item_general a
WHERE NOT EXISTS (SELECT 1 from gr_item_general_mig b
WHERE b.item_code = a.item_code and
b.organization_id = l_organization_id);
l_migration_count:= l_migration_count + l_recs_inserted;
/* Select master orgs that have not yet been migrated - master orgs get migrated first*/
OPEN c_get_child_orgs;
INSERT INTO gr_item_general_mig
(
item_code,
organization_id,
inventory_item_id,
migration_ind
)
SELECT
a.item_code,
l_organization_id,
NULL,
NULL
FROM gr_item_general a
WHERE NOT EXISTS (SELECT 1 from gr_item_general_mig b
WHERE b.item_code = a.item_code and
b.organization_id = l_organization_id);
SELECT item_code, organization_id
FROM gr_item_general_mig
WHERE migration_ind is NULL;
SELECT category_id
FROM fnd_document_categories
WHERE name = v_category_name;
SELECT inventory_item_id
FROM mtl_system_items_b
WHERE segment1 = l_mig_rec.item_code and
organization_id = l_mig_rec.organization_id;
SELECT *
FROM gr_item_general
WHERE item_code = l_mig_rec.item_code;
SELECT *
FROM gr_multilingual_name_tl
WHERE language in (SELECT language_code
FROM fnd_languages
WHERE language_code <> userenv('LANG')
AND installed_flag in ('I','B')) and
label_code = '11007' and
item_code = l_mig_rec.item_code;
SELECT *
FROM gr_generic_items_b
WHERE item_code = l_mig_rec.item_code;
SELECT item_id
FROM ic_item_mst_b
WHERE item_no = l_related_rec.item_no;
SELECT *
FROM fnd_attached_documents
WHERE entity_name = 'GR_ITEM_GENERAL' and
pk1_value = l_mig_rec.item_code;
/* Select item/organization combinations that have not yet been migrated */
OPEN c_get_mig_rec;
UPDATE mtl_system_items_b
SET hazardous_material_flag = 'Y',
cas_number = l_reg_item_rec.primary_cas_number,
hazard_class_id = l_hazard_class_id,
un_number_id = l_un_number_id
WHERE organization_id = l_mig_rec.organization_id and
inventory_item_id = l_inventory_item_id;
INSERT INTO gr_item_explosion_properties
(
organization_id,
inventory_item_id,
actual_hazard,
ingredient_flag,
explode_ingredient_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
l_mig_rec.organization_id,
l_inventory_item_id,
l_reg_item_rec.ACTUAL_HAZARD,
l_reg_item_rec.INGREDIENT_FLAG,
l_reg_item_rec.EXPLODE_INGREDIENT_FLAG,
l_reg_item_rec.CREATED_BY,
l_reg_item_rec.CREATION_DATE,
l_reg_item_rec.LAST_UPDATED_BY,
l_reg_item_rec.LAST_UPDATE_DATE,
l_reg_item_rec.LAST_UPDATE_LOGIN
);
/* Update the descriptions with the values from Regulatory */
UPDATE mtl_system_items_tl
SET description = l_translated_rec.name_description,
source_lang = l_translated_rec.source_lang,
creation_date = l_translated_rec.creation_date,
created_by = l_translated_rec.created_by,
last_update_date = l_translated_rec.last_update_date,
last_updated_by = l_translated_rec.last_updated_by,
last_update_login = l_translated_rec.last_update_login
WHERE language = l_translated_rec.language and
organization_id = l_mig_rec.organization_id and
inventory_item_id = l_inventory_item_id;
INSERT INTO gr_inv_item_properties
(
organization_id,
inventory_item_id,
sequence_number,
property_id,
label_code,
number_value,
alpha_value,
date_value,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
SELECT
l_mig_rec.organization_id,
l_inventory_item_id,
sequence_number,
property_id,
label_code,
number_value,
alpha_value,
date_value,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
FROM gr_item_properties
WHERE label_code <> '14001' and
label_code <> '14002' and
item_code = l_reg_item_rec.item_code;
UPDATE fnd_documents
SET category_id = l_inv_category_id
WHERE document_id = l_attachment_rec.document_id;
UPDATE fnd_documents_tl
SET doc_attribute_category = 'MSDS_INV_ITEM'
WHERE document_id = l_attachment_rec.document_id;
select fnd_attached_documents_s.nextval
into l_attached_doc_id
from sys.dual;
FND_ATTACHED_DOCUMENTS_PKG.Insert_Row(
X_Rowid => l_rowid,
X_attached_document_id => l_attached_doc_id,
X_document_id => l_attachment_rec.document_id,
X_creation_date => l_attachment_rec.creation_date,
X_created_by => l_attachment_rec.created_by,
X_last_update_date => l_attachment_rec.last_update_date,
X_last_updated_by => l_attachment_rec.last_updated_by,
X_last_update_login => l_attachment_rec.last_update_login,
X_seq_num => l_attachment_rec.seq_num,
X_entity_name => 'MTL_SYSTEM_ITEMS',
X_column1 => NULL,
X_pk1_value => l_mig_rec.organization_id,
X_pk2_value => l_inventory_item_id,
X_pk3_value => NULL,
X_pk4_value => NULL,
X_pk5_value => NULL,
X_automatically_added_flag => l_attachment_rec.automatically_added_flag,
X_request_id => l_attachment_rec.request_id,
X_datatype_id => NULL,
X_category_id => l_attachment_rec.category_id,
X_security_type => NULL,
X_security_id => NULL,
X_publish_flag => NULL,
X_storage_type => NULL,
X_usage_type => NULL,
X_language => NULL,
X_description => NULL,
X_file_name => NULL,
X_media_id => l_media_id,
X_attribute_category => l_attachment_rec.attribute_category,
X_attribute1 => l_attachment_rec.attribute1,
X_attribute2 => l_attachment_rec.attribute2,
X_attribute3 => l_attachment_rec.attribute3,
X_attribute4 => l_attachment_rec.attribute4,
X_attribute5 => l_attachment_rec.attribute5,
X_attribute6 => l_attachment_rec.attribute6,
X_attribute7 => l_attachment_rec.attribute7,
X_attribute8 => l_attachment_rec.attribute8,
X_attribute9 => l_attachment_rec.attribute9,
X_attribute10 => l_attachment_rec.attribute10,
X_attribute11 => l_attachment_rec.attribute11,
X_attribute12 => l_attachment_rec.attribute12,
X_attribute13 => l_attachment_rec.attribute13,
X_attribute14 => l_attachment_rec.attribute14,
X_attribute15 => l_attachment_rec.attribute15,
X_create_doc => 'N');
MTL_RELATED_ITEMS_PKG.Insert_Row (
X_Rowid => l_rowid,
X_Inventory_Item_Id => l_inventory_item_id,
X_Organization_Id => l_mig_rec.organization_id,
X_Related_Item_Id => l_related_item_id,
X_Relationship_Type_Id => 19,
X_Reciprocal_Flag => 'N',
X_Planning_Enabled_Flag => 'N',
X_Start_Date => l_related_rec.creation_date,
X_End_Date => NULL,
X_Attr_Context => NULL,
X_Attr_Char1 => NULL,
X_Attr_Char2 => NULL,
X_Attr_Char3 => NULL,
X_Attr_Char4 => NULL,
X_Attr_Char5 => NULL,
X_Attr_Char6 => NULL,
X_Attr_Char7 => NULL,
X_Attr_Char8 => NULL,
X_Attr_Char9 => NULL,
X_Attr_Char10 => NULL,
X_Attr_Num1 => NULL,
X_Attr_Num2 => NULL,
X_Attr_Num3 => NULL,
X_Attr_Num4 => NULL,
X_Attr_Num5 => NULL,
X_Attr_Num6 => NULL,
X_Attr_Num7 => NULL,
X_Attr_Num8 => NULL,
X_Attr_Num9 => NULL,
X_Attr_Num10 => NULL,
X_Attr_Date1 => NULL,
X_Attr_Date2 => NULL,
X_Attr_Date3 => NULL,
X_Attr_Date4 => NULL,
X_Attr_Date5 => NULL,
X_Attr_Date6 => NULL,
X_Attr_Date7 => NULL,
X_Attr_Date8 => NULL,
X_Attr_Date9 => NULL,
X_Attr_Date10 => NULL,
X_Last_Update_Date => l_related_rec.last_update_date,
X_Last_Updated_By => l_related_rec.last_updated_by,
X_Creation_Date => l_related_rec.creation_date,
X_Created_By => l_related_rec.created_by,
X_Last_Update_Login => l_related_rec.last_update_login,
X_Object_Version_Number => NULL
);
UPDATE gr_item_general_mig
SET migration_ind = 1,
inventory_item_id = l_inventory_item_id
WHERE item_code = l_mig_rec.item_code and
organization_id = l_mig_rec. organization_id;
SELECT a.item_code
FROM gr_item_general a
WHERE EXISTS (SELECT 1
FROM gr_item_concentrations b
WHERE b.item_code = a.item_code) and
a.formula_source_indicator = 'S';
SELECT profile_option_value
FROM fnd_profile_options a, fnd_profile_option_values b
WHERE b.level_id = 10001 and
a.profile_option_id = b.profile_option_id and
a.profile_option_name = v_profile_name;
SELECT std_um
FROM sy_uoms_typ
WHERE um_type = v_um_type;
SELECT *
FROM gr_item_concentrations
WHERE migration_ind is NULL and
item_code = l_item_code;
SELECT MAX(formula_vers) + 1
FROM fm_form_mst_b
WHERE formula_no = l_item_code;
SELECT MAX(recipe_version) + 1
FROM gmd_recipes_b
WHERE recipe_no = l_item_code;
SELECT organization_id
FROM sy_orgn_mst
WHERE orgn_code = v_org_code;
SELECT primary_uom_code
FROM mtl_system_items_b
WHERE organization_id = v_organization_id and
inventory_item_id = v_inventory_item_id;
SELECT gem5_formula_id_s.NEXTVAL
FROM SYS.DUAL;
SELECT gem5_formulaline_id_s.NEXTVAL
FROM SYS.DUAL;
SELECT gmd_recipe_id_s.NEXTVAL
FROM SYS.DUAL;
SELECT gmd_recipe_validity_id_s.NEXTVAL
FROM SYS.DUAL;
/* Select items that have a formula source of Standalone */
OPEN c_get_items;
/* Select items that have a formula source of Standalone */
--Bug# 5293938 Since its in a loop close it before reopening it.
IF c_get_item_concentrations%ISOPEN THEN
CLOSE c_get_item_concentrations;
FM_FORM_MST_MLS.INSERT_ROW(
X_ROWID => l_rowid,
X_FORMULA_ID => l_formula_id,
X_MASTER_FORMULA_ID => NULL,
X_OWNER_ORGANIZATION_ID => l_owner_org_id,
X_TOTAL_INPUT_QTY => 100,
X_TOTAL_OUTPUT_QTY => 100,
X_YIELD_UOM => l_uom,
X_FORMULA_STATUS => '700',
X_OWNER_ID => l_conc_rec.last_updated_by,
X_PROJECT_ID => NULL,
X_TEXT_CODE => NULL,
X_DELETE_MARK => 0,
X_FORMULA_NO => l_item_code,
X_FORMULA_VERS => l_formula_vers,
X_FORMULA_TYPE => 0,
X_IN_USE => NULL,
X_INACTIVE_IND => 0,
X_SCALE_TYPE => 0,
X_FORMULA_CLASS => NULL,
X_FMCONTROL_CLASS => NULL,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_ATTRIBUTE16 => NULL,
X_ATTRIBUTE17 => NULL,
X_ATTRIBUTE18 => NULL,
X_ATTRIBUTE19 => NULL,
X_ATTRIBUTE20 => NULL,
X_ATTRIBUTE21 => NULL,
X_ATTRIBUTE22 => NULL,
X_ATTRIBUTE23 => NULL,
X_ATTRIBUTE24 => NULL,
X_ATTRIBUTE25 => NULL,
X_ATTRIBUTE26 => NULL,
X_ATTRIBUTE27 => NULL,
X_ATTRIBUTE28 => NULL,
X_ATTRIBUTE29 => NULL,
X_ATTRIBUTE30 => NULL,
X_FORMULA_DESC1 => l_text || ' ' || l_item_code,
X_FORMULA_DESC2 => NULL,
X_CREATION_DATE => l_conc_rec.creation_date,
X_CREATED_BY => l_conc_rec.created_by,
X_LAST_UPDATE_DATE => l_conc_rec.last_update_date,
X_LAST_UPDATED_BY => l_conc_rec.last_updated_by,
X_LAST_UPDATE_LOGIN => l_conc_rec.last_update_login);
INSERT INTO fm_matl_dtl
(
FORMULALINE_ID,
FORMULA_ID,
LINE_TYPE,
LINE_NO,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
QTY,
DETAIL_UOM,
RELEASE_TYPE,
SCRAP_FACTOR,
SCALE_TYPE,
PHANTOM_TYPE,
REWORK_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CONTRIBUTE_STEP_QTY_IND,
CONTRIBUTE_YIELD_IND
)
VALUES
(
l_formulaline_id,
l_formula_id,
1,
1,
l_prod_item_id,
l_owner_org_id,
100,
l_uom,
0,
0,
1,
0,
0,
l_conc_rec.CREATED_BY,
l_conc_rec.CREATION_DATE,
l_conc_rec.LAST_UPDATE_DATE,
l_conc_rec.LAST_UPDATED_BY,
l_conc_rec.LAST_UPDATE_LOGIN,
'Y',
'Y'
);
INSERT INTO fm_matl_dtl
(
FORMULALINE_ID,
FORMULA_ID,
LINE_TYPE,
LINE_NO,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
QTY,
DETAIL_UOM,
RELEASE_TYPE,
SCRAP_FACTOR,
SCALE_TYPE,
PHANTOM_TYPE,
REWORK_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CONTRIBUTE_STEP_QTY_IND,
CONTRIBUTE_YIELD_IND
)
VALUES
(
l_formulaline_id,
l_formula_id,
-1,
l_line_no,
l_ing_item_id,
l_owner_org_id,
l_conc_rec.concentration_percentage,
l_uom,
0,
0,
1,
0,
0,
l_conc_rec.CREATED_BY,
l_conc_rec.CREATION_DATE,
l_conc_rec.LAST_UPDATE_DATE,
l_conc_rec.LAST_UPDATED_BY,
l_conc_rec.LAST_UPDATE_LOGIN,
'Y',
'Y'
);
GMD_RECIPES_MLS.INSERT_ROW(
X_ROWID => l_rowid,
X_RECIPE_ID => l_recipe_id,
X_OWNER_ID => l_conc_rec.last_updated_by,
X_OWNER_LAB_TYPE => NULL,
X_DELETE_MARK => 0,
X_TEXT_CODE => NULL,
X_RECIPE_NO => l_item_code,
X_RECIPE_VERSION => l_recipe_vers,
X_OWNER_ORGANIZATION_ID => l_owner_org_id,
X_CREATION_ORGANIZATION_ID => l_owner_org_id,
X_FORMULA_ID => l_formula_id,
X_ROUTING_ID => NULL,
X_PROJECT_ID => NULL,
X_RECIPE_STATUS => '700',
X_RECIPE_TYPE => 1,
X_ENHANCED_PI_IND => NULL,
X_CALCULATE_STEP_QUANTITY => 0,
X_PLANNED_PROCESS_LOSS => NULL,
X_CONTIGUOUS_IND => NULL,
X_RECIPE_DESCRIPTION => l_text || ' ' || l_item_code,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_ATTRIBUTE16 => NULL,
X_ATTRIBUTE17 => NULL,
X_ATTRIBUTE18 => NULL,
X_ATTRIBUTE19 => NULL,
X_ATTRIBUTE20 => NULL,
X_ATTRIBUTE21 => NULL,
X_ATTRIBUTE22 => NULL,
X_ATTRIBUTE23 => NULL,
X_ATTRIBUTE24 => NULL,
X_ATTRIBUTE25 => NULL,
X_ATTRIBUTE26 => NULL,
X_ATTRIBUTE27 => NULL,
X_ATTRIBUTE28 => NULL,
X_ATTRIBUTE29 => NULL,
X_ATTRIBUTE30 => NULL,
X_CREATION_DATE => l_conc_rec.creation_date,
X_CREATED_BY => l_conc_rec.created_by,
X_LAST_UPDATE_DATE => l_conc_rec.last_update_date,
X_LAST_UPDATED_BY => l_conc_rec.last_updated_by,
X_LAST_UPDATE_LOGIN => l_conc_rec.last_update_login,
X_FIXED_PROCESS_LOSS => 0 , /* 7582454*/
X_FIXED_PROCESS_LOSS_UOM => NULL /* 7582454*/
);
INSERT INTO gmd_recipe_validity_rules
(
RECIPE_VALIDITY_RULE_ID,
RECIPE_ID,
ORGN_CODE,
RECIPE_USE,
PREFERENCE,
START_DATE,
END_DATE,
MIN_QTY,
MAX_QTY,
STD_QTY,
DETAIL_UOM,
INV_MIN_QTY,
INV_MAX_QTY,
DELETE_MARK,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
VALIDITY_RULE_STATUS,
LAB_TYPE,
ORGANIZATION_ID,
INVENTORY_ITEM_ID
)
VALUES
(
l_validity_rule_id,
l_recipe_id,
NULL,
3,
1,
l_conc_rec.creation_date,
NULL,
100,
100,
100,
l_uom,
l_inv_qty,
l_inv_qty,
0,
l_conc_rec.CREATED_BY,
l_conc_rec.CREATION_DATE,
l_conc_rec.LAST_UPDATE_DATE,
l_conc_rec.LAST_UPDATED_BY,
l_conc_rec.LAST_UPDATE_LOGIN,
700,
NULL,
l_owner_org_id,
l_prod_item_id
);
UPDATE gr_item_concentrations
SET migration_ind = 1
WHERE item_code = l_item_code;
PROCEDURE update_dispatch_history
(
p_migration_run_id IN NUMBER,
p_commit IN VARCHAR2,
x_failure_count OUT NOCOPY NUMBER
) IS
/* ------------- LOCAL VARIABLES ------------------- */
l_seq NUMBER;
SELECT profile_option_value
FROM fnd_profile_options a, fnd_profile_option_values b
WHERE b.level_id = 10001 and
a.profile_option_id = b.profile_option_id and
a.profile_option_name = 'GR_ORGN_DEFAULT';
SELECT organization_id
FROM sy_orgn_mst_b
WHERE orgn_code = v_orgn_code;
SELECT dispatch_history_id, item, document_id
FROM gr_dispatch_history
WHERE organization_id is NULL;
SELECT doc_attribute5
FROM fnd_documents_tl
WHERE language = userenv('LANG') and
document_id = l_dispatch_rec.document_id;
p_context => 'UPDATE_DISPATCH_HISTORY',
p_param1 => NULL,
p_param2 => NULL,
p_param3 => NULL,
p_param4 => NULL,
p_param5 => NULL,
p_db_error => NULL,
p_app_short_name => 'GMA');
UPDATE gr_dispatch_history
SET organization_id = l_org_id,
inventory_item_id = l_inv_item_id
WHERE dispatch_history_id = l_dispatch_rec.dispatch_history_id;
p_context => 'UPDATE_DISPATCH_HISTORY',
p_param1 => l_org_id,
p_param2 => l_dispatch_rec.item,
p_param3 => NULL,
p_param4 => NULL,
p_param5 => NULL,
p_db_error => SQLERRM,
p_app_short_name => 'GR');
p_context => 'UPDATE_DISPATCH_HISTORY',
p_param1 => NULL,
p_param2 => NULL,
p_param3 => NULL,
p_param4 => NULL,
p_param5 => NULL,
p_db_error => SQLERRM,
p_app_short_name => 'GMA');
p_context => 'UPDATE_DISPATCH_HISTORY',
p_param1 => l_migration_count,
p_param2 => x_failure_count,
p_param3 => NULL,
p_param4 => NULL,
p_param5 => NULL,
p_db_error => NULL,
p_app_short_name => 'GMA');
p_context => 'UPDATE_DISPATCH_HISTORY',
p_param1 => NULL,
p_param2 => NULL,
p_param3 => NULL,
p_param4 => NULL,
p_param5 => NULL,
p_db_error => SQLERRM,
p_app_short_name => 'GMA');
p_context => 'UPDATE_DISPATCH_HISTORY',
p_param1 => x_failure_count,
p_param2 => NULL,
p_param3 => NULL,
p_param4 => NULL,
p_param5 => NULL,
p_db_error => NULL,
p_app_short_name => 'GMA');
END update_dispatch_history;