[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** rollup and updates of the header item.
** Some Rollup functions like Top GTIN affect only the Top Item
***********************************************************************/
/*#
* This method will be used for computing the net_weight attributes value
* The method does not have any parameters, but it will have access to the
* attribute map or the current item in process.
* This should not be confused with the actual rollup function. This function
* helps the derivation of the net_weight attribute for that particulat item
* whereas rollup function of net_weight would take into consideration all the
* child components of the current item.
*
* @rep:scope private
* @rep:lifecycle active
* @rep:displayname Compute Net weight
*/
PROCEDURE Compute_Net_Weight(x_attribute_value IN OUT NOCOPY VARCHAR2
,p_component_sequence_id IN OUT NOCOPY NUMBER)
IS
BEGIN
--
-- for an item the net wt is whatever is there in the table entry
-- until it is over-written by a rollup function
-- at the parent level, but there is no special rule of
-- deriving the unit weight of a component
--
null;
SELECT 'Parent'
FROM bom_components_b comp
,bom_structures_b parent
WHERE comp.component_sequence_id = p_component_sequence_id
AND comp.bill_sequence_id = parent.bill_sequence_id
AND EXISTS (SELECT bill_sequence_id
FROM bom_structures_b comp_struct
WHERE comp_struct.pk1_value = comp.pk1_value
AND nvl(comp_struct.pk2_value, 'xxxxxxxxxxxxxxx') = nvl(comp.pk2_value,'xxxxxxxxxxxxxxx')
AND comp_struct.obj_name = comp.obj_name
AND comp_struct.alternate_bom_designator = parent.alternate_bom_designator
);
SELECT is_trade_item_a_consumer_unit
, customer_order_enabled_flag
, Bom_Rollup_Pub.Is_UCCNet_Enabled(p_item_id, p_organization_id) is_uccnet
FROM ego_items_v
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id;
SELECT is_trade_item_a_consumer_unit
, customer_order_enabled_flag
, Bom_Rollup_Pub.Is_UCCNet_Enabled(bic.component_item_id, p_organization_id) is_uccnet
FROM ego_items_v
, bom_components_b bic
WHERE inventory_item_id = bic.component_item_id
AND organization_id = p_organization_id
AND bic.component_sequence_id = p_component_sequence_id;
* This at present will do the update of the multirow attrs
* Fixme once this is working
*
* @rep:scope private
* @rep:lifecycle active
* @rep:displayname Rollup Net weight
*/
PROCEDURE Compute_Multi_Row_Attrs
(p_header_item_id IN NUMBER DEFAULT NULL
,p_organization_id IN NUMBER DEFAULT NULL
,p_validate IN VARCHAR2
,p_halt_on_error IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_error_message OUT NOCOPY VARCHAR2
)
IS
l_pk_column_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
SELECT
item_catalog_group_id
FROM
mtl_system_items_b
WHERE
inventory_item_id = p_header_item_id
AND organization_id = p_organization_id;
SELECT DISTINCT ATTR_GROUP_TYPE
, ATTR_GROUP_NAME
FROM
EGO_ATTRS_V A
WHERE
A.APPLICATION_ID = 431 --PERF BUG 4932131
AND A.ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_MULTI_ATTRS'
AND A.EDIT_IN_HIERARCHY_CODE IN ('LP');
SELECT DISTINCT AG.ATTR_GROUP_ID
FROM
EGO_ATTRS_V A, EGO_ATTR_GROUPS_V AG
WHERE
A.APPLICATION_ID = AG.APPLICATION_ID
AND A.ATTR_GROUP_TYPE = AG.ATTR_GROUP_TYPE
AND A.ATTR_GROUP_NAME = AG.ATTR_GROUP_NAME
AND A.ATTR_GROUP_TYPE = 'EGO_ITEM_GTIN_MULTI_ATTRS'
AND A.EDIT_IN_HIERARCHY_CODE IN ('LP')
AND A.ATTR_ID = p_attr_id;
l_transaction_type := 'DELETE';
IF (l_transaction_type <> 'DELETE'
AND Bom_Rollup_Pub.g_attr_diffs IS NOT NULL
AND Bom_Rollup_Pub.g_attr_diffs.count > 0) THEN
FOR i IN Bom_Rollup_Pub.g_attr_diffs.FIRST .. Bom_Rollup_Pub.g_attr_diffs.LAST
LOOP
Bom_Rollup_Pub.g_attr_diffs(i).NEW_ATTR_VALUE_STR :=
Bom_Rollup_Pub.g_attr_diffs(i).OLD_ATTR_VALUE_STR;
, p_message => 'Calling Update Attributes');
EGO_GTIN_PVT.Update_Attributes
( p_pk_column_name_value_pairs => l_pk_column_values
, p_class_code_name_value_pairs => Bom_Rollup_Pub.g_class_code_name_value_pairs
, p_data_level_name_value_pairs => Bom_Rollup_Pub.g_data_level_name_value_pairs
, p_attr_diffs => l_cur_attr_diff_tbl
, p_transaction_type => l_transaction_type
, p_attr_group_id => c1.attr_group_id
, x_error_message => l_error_message);
, p_message => 'After Calling Update Attributes' || l_error_message);
SELECT
item_catalog_group_id
FROM
mtl_system_items_b
WHERE
inventory_item_id = p_item_id
AND organization_id = p_organization_id;
EGO_GTIN_PVT.Update_Attribute
( p_inventory_item_id => p_header_item_id
, p_organization_id => p_organization_id
, p_attr_name => l_attr_name
, p_attr_new_value_num => to_number(l_unit_weight)
, p_attr_new_value_uom => l_net_weight_uom
, x_return_status => x_return_status
, x_errorcode => l_errorcode
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
EGO_GTIN_PVT.Update_Attribute
( p_inventory_item_id => p_header_item_id
, p_organization_id => p_organization_id
, p_attr_name => 'Is_Trade_Item_Info_Private'
, p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS'
, p_attr_group_name => 'Gtin_Unit_Indicator'
, p_attr_new_value_str => l_private_flag
, x_return_status => x_return_status
, x_errorcode => l_errorcode
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
EGO_GTIN_PVT.Update_Attribute
( p_inventory_item_id => p_header_item_id
, p_organization_id => p_organization_id
, p_attr_name => 'Retail_Brand_Owner_Name'
, p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS'
, p_attr_group_name => 'Trade_Item_Description'
, p_attr_new_value_str => l_brand_owner_name
, x_return_status => x_return_status
, x_errorcode => l_errorcode
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
EGO_GTIN_PVT.Update_Attribute
( p_inventory_item_id => p_header_item_id
, p_organization_id => p_organization_id
, p_attr_name => 'Retail_Brand_Owner_Gln'
, p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS'
, p_attr_group_name => 'Trade_Item_Description'
, p_attr_new_value_str => l_brand_owner_gln
, x_return_status => x_return_status
, x_errorcode => l_errorcode
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
EGO_GTIN_PVT.Update_Attribute
( p_inventory_item_id => p_header_item_id
, p_organization_id => p_organization_id
, p_attr_name => 'Functional_Name'
, p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS'
, p_attr_group_name => 'Trade_Item_Description'
, p_attr_new_value_str => l_functional_name
, x_return_status => x_return_status
, x_errorcode => l_errorcode
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
EGO_GTIN_PVT.Update_Attribute
( p_inventory_item_id => p_header_item_id
, p_organization_id => p_organization_id
, p_attr_name => 'Sub_Brand'
, p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS'
, p_attr_group_name => 'Trade_Item_Description'
, p_attr_new_value_str => l_sub_brand
, x_return_status => x_return_status
, x_errorcode => l_errorcode
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
EGO_GTIN_PVT.Update_Attribute
( p_inventory_item_id => p_header_item_id
, p_organization_id => p_organization_id
, p_attr_name => 'Top_Gtin'
, p_attr_new_value_str => l_top_gtin_flag
, x_return_status => x_return_status
, x_errorcode => l_errorcode
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
EGO_GTIN_PVT.Update_Attribute
( p_inventory_item_id => p_header_item_id
, p_organization_id => p_organization_id
, p_attr_name => 'Uccnet_Storage_Temp_Min'
, p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS'
, p_attr_group_name => 'Temperature_Information'
, p_attr_new_value_num => l_sh_temp_min
, x_return_status => x_return_status
, x_errorcode => l_errorcode
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
EGO_GTIN_PVT.Update_Attribute
( p_inventory_item_id => p_header_item_id
, p_organization_id => p_organization_id
, p_attr_name => 'Uccnet_Storage_Temp_Max'
, p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS'
, p_attr_group_name => 'Temperature_Information'
, p_attr_new_value_num => l_sh_temp_max
, x_return_status => x_return_status
, x_errorcode => l_errorcode
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
EGO_GTIN_PVT.Update_Attribute
( p_inventory_item_id => p_header_item_id
, p_organization_id => p_organization_id
, p_attr_name => 'Uom_Storage_Handling_Temp_Min'
, p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS'
, p_attr_group_name => 'Temperature_Information'
, p_attr_new_value_str => l_sh_temp_min_uom
, x_return_status => x_return_status
, x_errorcode => l_errorcode
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
EGO_GTIN_PVT.Update_Attribute
( p_inventory_item_id => p_header_item_id
, p_organization_id => p_organization_id
, p_attr_name => 'Uom_Storage_Handling_Temp_Max'
, p_attr_group_type => 'EGO_ITEM_GTIN_ATTRS'
, p_attr_group_name => 'Temperature_Information'
, p_attr_new_value_str => l_sh_temp_max_uom
, x_return_status => x_return_status
, x_errorcode => l_errorcode
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);