[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT bill_sequence_id
FROM bom_structures_b
WHERE assembly_item_id = p_item_id
AND organization_id = p_org_id
AND alternate_bom_designator = 'PIM_PBOM_S';
SELECT 'Exist'
FROM bom_components_b
WHERE bill_sequence_id = l_bill_seq_id
AND (disable_date IS NULL OR disable_date > sysdate);
SELECT count(DISTINCT egi.trade_item_descriptor) distinct_trade_count, count(DISTINCT egi.gtin) gtin_count,
count(egi.trade_item_descriptor) total_trade_count
FROM bom_components_b bic, ego_items_v egi
WHERE bic.bill_sequence_id = p_bill_sequence_id AND
bic.pk1_value = egi.inventory_item_id AND
bic.pk2_value = egi.organization_id AND
bic.effectivity_date <= SYSDATE AND
nvl(bic.disable_date, SYSDATE+1) > SYSDATE AND
egi.trade_item_descriptor IS NOT NULL AND
/* Code added for bug 7435503*/
bic.IMPLEMENTATION_DATE IS NOT null;
SELECT gtin,trade_item_descriptor,publication_status, top_gtin, primary_uom_code
INTO l_parent_gtin, l_parent_tid, l_publication_status, l_top_gtin, l_parent_uom_code
FROM ego_items_v
WHERE inventory_item_id = p_assembly_item_id AND
organization_id = p_organization_id;
SELECT gtin, trade_item_descriptor, primary_uom_code
INTO l_component_gtin, l_component_tid, l_child_uom_code
FROM ego_items_v
WHERE inventory_item_id = p_component_item_id AND
organization_id = p_organization_id;
SELECT
bill_sequence_id INTO l_bill_sequence_id
FROM
bom_structures_b bsb, bom_structure_types_b bstb
WHERE
assembly_item_id = p_assembly_item_id
AND organization_id = p_organization_id
AND bsb.structure_type_id = bstb.structure_type_id
AND bstb.structure_type_name = 'Packaging Hierarchy'
AND bsb.is_preferred = 'Y';
SELECT bill_sequence_id INTO l_bill_sequence_id FROM bom_structures_b WHERE
assembly_item_id = p_assembly_item_id AND
organization_id = p_organization_id AND
alternate_bom_designator = p_alternate_bom_code ;
PROCEDURE Update_Top_GTIN( p_organization_id IN NUMBER,
p_component_item_id IN NUMBER,
p_parent_item_id in NUMBER := NULL,
p_structure_name in VARCHAR2 := NULL) IS
is_preferred_flag BOOLEAN := FALSE;
SELECT
alternate_bom_designator
FROM
bom_structures_b
WHERE
assembly_item_id = p_assembly_item_id
AND organization_id = p_organization_id
AND alternate_bom_designator = p_structure_name
AND is_Preferred = 'Y';
UPDATE EGO_ITEM_GTN_ATTRS_B
SET top_gtin = null
WHERE inventory_item_id = p_component_item_id AND
organization_id = p_organization_id;
SELECT primary_uom_code INTO l_component_uom FROM mtl_system_items_b WHERE
inventory_item_id = p_component_item_id AND
organization_id = p_organization_id;
SELECT primary_uom_code INTO l_assembly_uom FROM mtl_system_items_b WHERE
inventory_item_id = p_assembly_item_id AND
organization_id = p_organization_id;
IF p_transaction_type IN ('CREATE','DELETE')
THEN
IF (Bom_Rollup_Pub.Is_UCCNet_Enabled(p_item_id, p_organization_id) = 'Y')
THEN
Bom_Rollup_Pub.Add_Rollup_Function
( p_Object_Name => 'EGO_ITEM'
, p_Rollup_Action => Bom_Rollup_Pub.G_COMPUTE_NET_WEIGHT
, p_DML_Function => 'Bom_Compute_Functions.Set_Net_Weight'
, p_DML_Delayed_Write => 'N'
, x_Rollup_Action_Map => l_rollup_map
);
IF p_transaction_type IN ('UPDATE')
THEN
Bom_Rollup_Pub.Add_Rollup_Function
( p_Object_Name => 'EGO_ITEM'
, p_Rollup_Action => Bom_Rollup_Pub.G_COMPUTE_NET_WEIGHT
, p_DML_Function => 'Bom_Compute_Functions.Set_Net_Weight'
, p_DML_Delayed_Write => 'N'
, x_Rollup_Action_Map => l_rollup_map
);
IF p_transaction_type = 'DELETE' AND
p_parent_item_id IS NOT NULL
THEN
Bom_Rollup_Pub.g_attr_diffs := null;
PROCEDURE UPDATE_REG_PUB_UPDATE_DATES (p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_update_reg IN VARCHAR2 := 'N',
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
BEGIN
EGO_GTIN_PVT.UPDATE_REG_PUB_UPDATE_DATES (p_inventory_item_id => p_inventory_item_id,
p_organization_id => p_organization_id,
p_update_reg => p_update_reg,
p_commit => p_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT primary_uom_code
INTO l_src_uom_code
FROM MTL_SYSTEM_ITEMS_B
WHERE inventory_item_id = p_source_item_id
and organization_id = p_src_org_id;
SELECT primary_uom_code
INTO l_dest_uom_code
FROM MTL_SYSTEM_ITEMS_B
WHERE inventory_item_id = p_destn_item_id
and organization_id = p_dest_org_id;
SELECT count(DISTINCT uom_class)
into l_total
FROM mtl_units_of_measure_vl
WHERE uom_code = p_src_uom_code OR uom_code = p_dest_uom_code;
SELECT count(DISTINCT ega.storage_handling_temp_max) storage_temp_max_cnt,
count(DISTINCT ega.storage_handling_temp_min) storage_temp_min_cnt,
count(DISTINCT ega.uom_storage_handling_temp_max) uom_storage_temp_max_cnt,
count(DISTINCT ega.uom_storage_handling_temp_min) uom_storage_temp_min_cnt,
count(DISTINCT ega.brand_owner_gln) brand_owner_gln_cnt,
count(DISTINCT ega.brand_owner_name) brand_owner_name_cnt,
count(DISTINCT ega.sub_brand) sub_brand_cnt,
count(DISTINCT egal.functional_name) functional_name_cnt,
count(DISTINCT msi.weight_uom_code) weight_uom_code_cnt
FROM bom_explosions_all be,
ego_item_gtn_attrs_b ega,
ego_item_gtn_attrs_tl egal,
mtl_system_items_b msi
WHERE be.group_id = p_group_id
AND be.trade_item_descriptor = 'BASE_UNIT_OR_EACH'
AND be.component_item_id = msi.inventory_item_id
AND be.common_organization_id = msi.organization_id
AND msi.inventory_item_id = ega.inventory_item_id
AND msi.organization_id = ega.organization_id
AND ega.extension_id = egal.extension_id
AND egal.language = userenv('LANG');
SELECT DISTINCT concat(ega.manufacturer,ega.name_of_manufacturer)
BULK COLLECT INTO l_mfg_table
FROM bom_explosions_all be,
ego_gtin_mfg_attrs_v ega
WHERE be.group_id = p_group_id
AND be.trade_item_descriptor = 'BASE_UNIT_OR_EACH'
AND be.component_item_id = ega.inventory_item_id
AND be.common_organization_id = ega.organization_id;
dbms_output.put_line(substr('SELECT 1 INTO l_result FROM dual WHERE EXISTS
(SELECT null FROM bom_explosions_all be, ego_gtin_mfg_attrs_v ega
WHERE be.group_id = '||p_group_id||' AND be.trade_item_descriptor = '||''''||'BASE_UNIT_OR_EACH'||''''||
' AND be.component_item_id = ega.inventory_item_id AND be.common_organization_id = ega.organization_id
AND concat(ega.manufacturer,ega.name_of_manufacturer) IN ('||l_where_clause||')'||
' GROUP BY be.component_item_id HAVING count(*) <>'||l_total_mfrs||')',1,250));
EXECUTE IMMEDIATE 'SELECT 1 FROM dual WHERE EXISTS
(SELECT null FROM bom_explosions_all be, ego_gtin_mfg_attrs_v ega
WHERE be.group_id = '||p_group_id||' AND be.trade_item_descriptor = '||''''||'BASE_UNIT_OR_EACH'||''''||
' AND be.component_item_id = ega.inventory_item_id AND be.common_organization_id = ega.organization_id
AND concat(ega.manufacturer,ega.name_of_manufacturer) IN ('||l_where_clause||')'||
' GROUP BY be.component_item_id HAVING count(*) <> '||l_total_mfrs||' )' INTO l_result;