[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM BOM_SMALL_IMPL_TEMP;
SELECT
VALUE
FROM
V$PARAMETER
WHERE
NAME = 'utl_file_dir';
PROCEDURE Handle_Attribute_Updates
( p_Header_Item_Id IN NUMBER
, p_Organization_Id IN NUMBER
, p_Header_Attrs_Flag IN VARCHAR2
, p_action_map IN Bom_Rollup_Pub.Rollup_Action_Map
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- l_indx NUMBER;
END Handle_Attribute_Updates;
, p_parent_item_id IN NUMBER := NULL -- passed in if add/delete comp case
, p_component_item_id IN NUMBER := NULL -- passed in if add/delete comp case
, p_alternate_bom_code IN VARCHAR2
, p_validate IN VARCHAR2
, p_halt_on_error IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR l_LowLevelCode_csr is
SELECT
nvl(max(plan_level), -1) depth
FROM
bom_explosions_all
WHERE
group_id = p_rollup_id;
SELECT
MTL.ROWID row_id,
MTL.INVENTORY_ITEM_ID inventory_item_id,
nvl(UNIT_WEIGHT,0) unit_weight,
LLC.assembly_item_id parent_item_id
FROM
Mtl_System_Items_b MTL,
bom_explosions_all LLC
WHERE
LLC.group_id = p_rollup_id
AND LLC.plan_level = p_level
AND MTL.INVENTORY_ITEM_ID = LLC.pk1_value
AND LLC.obj_name IS NULL -- EGO_ITEM
AND MTL.ORGANIZATION_ID = p_organization_id;
SELECT MTL2.UNIT_WEIGHT unit_weight
,gtn_attrs.gross_weight gross_weight
--UOM ROLLUP
, MTL2.WEIGHT_UOM_CODE NET_WT_UOM
, gtn_attrs.UOM_GROSS_WEIGHT GROSS_WT_UOM
--UOM ROLLUP
,component_sequence_id
,com.component_quantity
,com.bill_sequence_id
,com.component_item_id
,gtn_attrs.is_trade_item_info_private
,gtn_attrs.brand_owner_name brand_owner_name
,gtn_attrs.brand_owner_gln brand_owner_gln
,gtn_attrs.sub_brand sub_brand
,gtn_attrs_tl.functional_name functional_name
,0 manufacturer_id
,' ' manufacturer_gln
,mtl2.customer_order_enabled_flag
,gtn_attrs.is_trade_item_a_consumer_unit
,mtl1.customer_order_enabled_flag parent_cust_ord_enabled_flag
,gtn_attrs_parent.is_trade_item_a_consumer_unit parent_is_item_cons_unit
,gtn_attrs.storage_handling_temp_min
,gtn_attrs.uom_storage_handling_temp_min
,gtn_attrs.storage_handling_temp_max
,gtn_attrs.uom_storage_handling_temp_max
,mtl2.trade_item_descriptor tiud
,gtn_attrs.inventory_item_id inv_id
,gtn_attrs_parent.inventory_item_id inv_id1
FROM mtl_system_items MTL2,
bom_inventory_components COM,
mtl_system_items MTL1,
bom_bill_of_materials BOM,
ego_item_gtn_attrs_b gtn_attrs,
ego_item_gtn_attrs_tl gtn_attrs_tl,
ego_item_gtn_attrs_b gtn_attrs_parent
WHERE
NVL(BOM.ALTERNATE_BOM_DESIGNATOR,'XXXXXXXXXXX') =
NVL(p_alternate_bom_code,'XXXXXXXXXXX')
AND COM.BILL_SEQUENCE_ID = BOM.COMMON_BILL_SEQUENCE_ID
AND BOM.ORGANIZATION_ID = p_organization_id
AND BOM.ASSEMBLY_ITEM_ID = p_item_id
AND MTL1.INVENTORY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
AND MTL1.ORGANIZATION_ID = BOM.ORGANIZATION_ID
AND MTL2.INVENTORY_ITEM_ID = COM.COMPONENT_ITEM_ID
AND MTL2.ORGANIZATION_ID = BOM.ORGANIZATION_ID
AND COM.IMPLEMENTATION_DATE IS NOT NULL
AND NVL(COM.ECO_FOR_PRODUCTION,2) = 2
AND COM.COMPONENT_QUANTITY > 0
AND NOT (mtl1.replenish_to_order_flag = 'Y'
AND mtl1.bom_item_type = 4
AND mtl1.base_item_id IS NOT NULL
AND MTL2.BOM_ITEM_TYPE IN (1,2)
)
AND ( COM.DISABLE_DATE IS NULL
OR
COM.DISABLE_DATE > p_eff_date
)
AND ( ( MTL1.EFFECTIVITY_CONTROL <> 1
AND p_unit_number is NOT NULL
AND COM.DISABLE_DATE IS NULL
AND p_unit_number BETWEEN COM.FROM_END_ITEM_UNIT_NUMBER
AND NVL(COM.TO_END_ITEM_UNIT_NUMBER, p_unit_number)
)
OR
(
MTL1.EFFECTIVITY_CONTROL = 1
AND COM.EFFECTIVITY_DATE <= p_eff_date
)
)
AND mtl2.inventory_item_id = gtn_attrs.inventory_item_id (+)
AND mtl2.organization_id = gtn_attrs.organization_id (+)
AND mtl1.inventory_item_id = gtn_attrs_parent.inventory_item_id (+) -- attributes of the parent
AND mtl1.organization_id = gtn_attrs_parent.organization_id (+) -- attributes of the parent
AND gtn_attrs_tl.inventory_item_id(+) = gtn_attrs.inventory_item_id
AND gtn_attrs_tl.organization_id(+)= gtn_attrs.organization_id
AND gtn_attrs_tl.extension_id(+)= gtn_attrs.extension_id
AND gtn_attrs_tl.language(+) = USERENV('LANG')
FOR UPDATE OF
mtl2.unit_weight
,gtn_attrs.gross_weight
--UOM ROLLUP
, mtl2.WEIGHT_UOM_CODE
,gtn_attrs.UOM_GROSS_WEIGHT
--UOM ROLLUP
,gtn_attrs.top_gtin
,gtn_attrs.brand_owner_name
,gtn_attrs.brand_owner_gln
,gtn_attrs_tl.functional_name
,gtn_attrs.storage_handling_temp_min
,gtn_attrs.uom_storage_handling_temp_min
,gtn_attrs.storage_handling_temp_max
,gtn_attrs.uom_storage_handling_temp_max
NOWAIT;
SELECT inventory_item_id
FROM ego_item_gtn_attrs_b
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
SELECT GDSN_OUTBOUND_ENABLED_FLAG
FROM mtl_system_items_b
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
l_Header_Attrs_Map.DELETE; -- clean and start
l_component_seq_tbl.DELETE;
l_component_seq_attrs_tbl.DELETE;
l_Header_Attrs_Map.DELETE; -- clean and start
l_component_seq_tbl.DELETE;
l_component_seq_attrs_tbl.DELETE;
l_component_attrs.DELETE; -- this is the attributes table of a component stored in attrs_map
SELECT item_catalog_group_id
INTO l_catalog_id
FROM mtl_system_items_b
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
SELECT EGO_EXTFWK_S.NEXTVAL INTO l_ext_id FROM dual;
EGO_ITEM_GTN_ATTRS_PKG.INSERT_ROW (
x_ROWID => x_row_id
,x_EXTENSION_ID => l_ext_id
,x_REQUEST_ID => null
,x_DELIVERY_TO_MRKT_TEMP_MIN => null
,x_UOM_DELIVERY_TO_MRKT_TEMP_MI => null
,x_SUB_BRAND => null
,x_UOM_DEL_TO_DIST_CNTR_TEMP_MI => null
,x_DELIVERY_TO_MRKT_TEMP_MAX => null
,x_UOM_DELIVERY_TO_MRKT_TEMP_MA => null
,x_INVENTORY_ITEM_ID => p_item_id
,x_ORGANIZATION_ID => p_organization_id
,x_ITEM_CATALOG_GROUP_ID => l_catalog_id
,x_REVISION_ID => null
,x_IS_TRADE_ITEM_A_CONSUMER_UNI => null
,x_IS_TRADE_ITEM_INFO_PRIVATE => null
,x_GROSS_WEIGHT => null
,x_UOM_GROSS_WEIGHT => null
,x_EFFECTIVE_DATE => sysdate
,x_CANCELED_DATE => null
,x_DISCONTINUED_DATE => null
,x_END_AVAILABILITY_DATE_TIME => null
,x_START_AVAILABILITY_DATE_TIME => null
,x_BRAND_NAME => null
,x_IS_TRADE_ITEM_A_BASE_UNIT => null
,x_IS_TRADE_ITEM_A_VARIABLE_UNI => null
,x_IS_PACK_MARKED_WITH_EXP_DATE => null
,x_IS_PACK_MARKED_WITH_GREEN_DO => null
,x_IS_PACK_MARKED_WITH_INGRED => null
,x_IS_PACKAGE_MARKED_AS_REC => null
,x_IS_PACKAGE_MARKED_RET => null
,x_STACKING_FACTOR => null
,x_STACKING_WEIGHT_MAXIMUM => null
,x_UOM_STACKING_WEIGHT_MAXIMUM => null
,x_ORDERING_LEAD_TIME => null
,x_UOM_ORDERING_LEAD_TIME => null
,x_ORDER_QUANTITY_MAX => null
,x_ORDER_QUANTITY_MIN => null
,x_ORDER_QUANTITY_MULTIPLE => null
,x_ORDER_SIZING_FACTOR => null
,x_EFFECTIVE_START_DATE => null
,x_CATALOG_PRICE => null
,x_EFFECTIVE_END_DATE => null
,x_SUGGESTED_RETAIL_PRICE => null
,x_MATERIAL_SAFETY_DATA_SHEET_N => null
,x_HAS_BATCH_NUMBER => null
,x_IS_NON_SOLD_TRADE_RET_FLAG => null
,x_IS_TRADE_ITEM_MAR_REC_FLAG => null
,x_DIAMETER => null
,x_UOM_DIAMETER => null
,x_DRAINED_WEIGHT => null
,x_UOM_DRAINED_WEIGHT => null
,x_GENERIC_INGREDIENT => null
,x_GENERIC_INGREDIENT_STRGTH => null
,x_UOM_GENERIC_INGREDIENT_STRGT => null
,x_INGREDIENT_STRENGTH => null
,x_IS_NET_CONTENT_DEC_FLAG => null
,x_NET_CONTENT => null
,x_UOM_NET_CONTENT => null
,x_PEG_HORIZONTAL => null
,x_UOM_PEG_HORIZONTAL => null
,x_PEG_VERTICAL => null
,x_UOM_PEG_VERTICAL => null
,x_CONSUMER_AVAIL_DATE_TIME => null
,x_DEL_TO_DIST_CNTR_TEMP_MAX => null
,x_UOM_DEL_TO_DIST_CNTR_TEMP_MA => null
,x_DEL_TO_DIST_CNTR_TEMP_MIN => null
,x_TRADE_ITEM_DESCRIPTOR => null
,x_EANUCC_CODE => null
,x_EANUCC_TYPE => null
,x_RETAIL_PRICE_ON_TRADE_ITEM => null
,x_QUANTITY_OF_COMP_LAY_ITEM => null
,x_QUANITY_OF_ITEM_IN_LAYER => null
,x_QUANTITY_OF_ITEM_INNER_PACK => null
,x_TARGET_MARKET_DESC => null
,x_QUANTITY_OF_INNER_PACK => null
,x_BRAND_OWNER_GLN => null
,x_BRAND_OWNER_NAME => null
,x_STORAGE_HANDLING_TEMP_MAX => null
,x_UOM_STORAGE_HANDLING_TEMP_MA => null
,x_STORAGE_HANDLING_TEMP_MIN => null
,x_UOM_STORAGE_HANDLING_TEMP_MI => null
,x_TRADE_ITEM_COUPON => null
,x_DEGREE_OF_ORIGINAL_WORT => null
,x_FAT_PERCENT_IN_DRY_MATTER => null
,x_PERCENT_OF_ALCOHOL_BY_VOL => null
,x_ISBN_NUMBER => null
,x_ISSN_NUMBER => null
,x_IS_INGREDIENT_IRRADIATED => null
,x_IS_RAW_MATERIAL_IRRADIATED => null
,x_IS_TRADE_ITEM_GENETICALLY_MO => null
,x_IS_TRADE_ITEM_IRRADIATED => null
,x_PUBLICATION_STATUS => null
,x_TOP_GTIN => null
,x_SECURITY_TAG_LOCATION => null
,x_URL_FOR_WARRANTY => null
,x_NESTING_INCREMENT => null
,x_UOM_NESTING_INCREMENT => null
,x_IS_TRADE_ITEM_RECALLED => null
,x_MODEL_NUMBER => null
,x_PIECES_PER_TRADE_ITEM => null
,x_UOM_PIECES_PER_TRADE_ITEM => null
,x_DEPT_OF_TRNSPRT_DANG_GOODS_N => null
,x_RETURN_GOODS_POLICY => null
,x_IS_OUT_OF_BOX_PROVIDED => null
,x_REGISTRATION_UPDATE_DATE => null
,x_TP_NEUTRAL_UPDATE_DATE => null
,x_IS_BARCODE_SYMBOLOGY_DERIVAB => null
,x_INVOICE_NAME => null
,x_DESCRIPTIVE_SIZE => null
,x_FUNCTIONAL_NAME => null
,x_TRADE_ITEM_FORM_DESCRIPTION => null
,x_WARRANTY_DESCRIPTION => null
,x_TRADE_ITEM_FINISH_DESCRIPTIO => null
,x_DESCRIPTION_SHORT => null
,x_CREATION_DATE => sysdate
,x_CREATED_BY => l_user_id
,x_LAST_UPDATE_DATE => sysdate
,x_LAST_UPDATED_BY => l_user_id
,x_LAST_UPDATE_LOGIN => l_login_id);
l_Header_Attrs_Map.DELETE; -- clean and start
Handle_Attribute_Updates( p_Header_Item_Id => l_assy_rec.inventory_item_id
, p_organization_id => p_organization_id
, p_header_attrs_flag => 'N'
, p_action_map => p_action_map
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
Handle_Attribute_Updates( p_Header_Item_Id => l_assy_rec.inventory_item_id
, p_organization_id => p_organization_id
, p_header_attrs_flag => 'Y'
, p_action_map => p_action_map
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT impl.parent_item_id
, impl.organization_id
, impl.parent_alternate_designator
, structure_type_id
FROM bom_implosions_v impl
where
impl.sequence_id = p_sequence_id
and impl.top_item_flag='Y'
and ( current_level = 0
OR EXISTS(SELECT 1
FROM bom_structures_b bom
WHERE bom.assembly_item_id = impl.parent_item_id
AND bom.organization_id = impl.organization_id
AND nvl(bom.alternate_bom_designator,'xxxxxxxxxxx') =
nvl(impl.parent_alternate_designator,'xxxxxxxxxxx')
AND bom.is_preferred = 'Y'
AND bom.structure_type_id = p_structure_type_id
AND impl.structure_type_id = p_structure_type_id
)
);
SELECT
alternate_bom_designator
FROM
bom_structures_b
WHERE
assembly_item_id = p_assembly_item_id
AND organization_id = p_org_id
AND structure_type_id = p_struct_type_id
AND is_Preferred = 'Y';
SELECT
COUNT(COMPONENT_SEQUENCE_ID) INTO l_parents_for_pk1
from
BOM_SMALL_IMPL_TEMP
WHERE
LOWEST_ITEM_ID = l_rollup_item_id
AND ORGANIZATION_ID = p_organization_id
AND CURRENT_LEVEL > 0
AND SEQUENCE_ID = l_Sequence; --changed to 0 as the sql won't work
SELECT
ALTERNATE_BOM_DESIGNATOR INTO l_alternate_bom_code
FROM
BOM_STRUCTURES_B
WHERE
ASSEMBLY_ITEM_ID = l_rollup_item_id
AND ORGANIZATION_ID = p_organization_id
AND STRUCTURE_TYPE_ID = p_structure_type_id
AND IS_PREFERRED = 'Y';
UPDATE
BOM_SMALL_IMPL_TEMP
SET
TOP_ITEM_FLAG ='Y',
ALTERNATE_DESIGNATOR = l_alternate_bom_code,
STRUCTURE_TYPE_ID = p_structure_type_id
WHERE
CURRENT_LEVEL = 0
AND SEQUENCE_ID = l_Sequence
AND LOWEST_ITEM_ID = l_rollup_item_id
AND ORGANIZATION_ID = p_organization_id ;
UPDATE
BOM_SMALL_IMPL_TEMP
SET
TOP_ITEM_FLAG ='Y'
WHERE
CURRENT_LEVEL = 0
AND SEQUENCE_ID = l_Sequence
AND LOWEST_ITEM_ID = l_rollup_item_id
AND ORGANIZATION_ID = p_organization_id ;
l_Top_Item_Attrs_Map.DELETE;
l_Header_Attrs_Map.DELETE;
SELECT parent_item_id
, organization_id
FROM bom_implosions_v
WHERE sequence_id = p_sequence_id
AND top_item_flag = 'Y';
l_Top_Item_Attrs_Map.DELETE;
l_Header_Attrs_Map.DELETE;
SELECT structure_type_id
FROM bom_structure_types_B
WHERE structure_type_name = p_structure_type_name;
* completes, if it is an attribute that is updated for the immediate
* parent, then the computation function needs to set the attribute value
* for the parent so that the rollup process can issue an call for
* affected parent.
**************************************************************************/
PROCEDURE Set_Parent_Attribute
( p_attribute_name IN VARCHAR2
, p_attribute_value IN VARCHAR2
)
IS
l_header_attr_cnt NUMBER := l_Header_Attrs_Map.COUNT;
* end issue an appropriate call to update the affected Item.
**************************************************************************/
PROCEDURE Set_Top_Item_Attribute
( p_attribute_name IN VARCHAR2
, p_attribute_value IN VARCHAR2
)
IS
l_header_attr_cnt NUMBER := l_Top_Item_Attrs_Map.COUNT + 1;
SELECT 'X' assignment_present
FROM mtl_default_category_sets a
, mtl_item_categories b
WHERE a.functional_area_id = 12
AND a.category_set_id = b.category_set_id
AND rownum = 1
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT trade_item_descriptor
FROM ego_items_v
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT TRADE_ITEM_DESCRIPTOR INTO l_pack_type
FROM MTL_SYSTEM_ITEMS_B
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;