[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT REVISION
FROM MTL_ITEM_REVISIONS_B MIR
WHERE INVENTORY_ITEM_ID = p_item_id
AND ORGANIZATION_ID = p_org_id
AND MIR.EFFECTIVITY_DATE <= p_eff_dt;
SELECT MSI.ITEM_TYPE, LOOKUP.MEANING ,MSI.CONCATENATED_SEGMENTS
INTO l_parent_item_type,l_parent_item_type_name,l_parent_name
FROM MTL_SYSTEM_ITEMS_KFV MSI ,FND_COMMON_LOOKUPS LOOKUP
WHERE
INVENTORY_ITEM_ID = p_parent_item_id AND ORGANIZATION_ID = p_organization_id
AND MSI.ITEM_TYPE = LOOKUP.LOOKUP_CODE(+)
AND LOOKUP.LOOKUP_TYPE(+) = 'ITEM_TYPE'
AND LOOKUP.ENABLED_FLAG(+) = 'Y'
AND (LOOKUP.START_DATE_ACTIVE IS NULL OR LOOKUP.START_DATE_ACTIVE < SYSDATE)
AND (LOOKUP.END_DATE_ACTIVE IS NULL OR LOOKUP.END_DATE_ACTIVE > SYSDATE);
SELECT MSI.ITEM_TYPE, LOOKUP.MEANING ,MSI.CONCATENATED_SEGMENTS
INTO l_child_item_type , l_child_item_type_name,l_child_name
FROM MTL_SYSTEM_ITEMS_KFV MSI ,FND_COMMON_LOOKUPS LOOKUP
WHERE
INVENTORY_ITEM_ID = p_child_item_id AND ORGANIZATION_ID = p_organization_id
AND MSI.ITEM_TYPE = LOOKUP.LOOKUP_CODE(+)
AND LOOKUP.LOOKUP_TYPE(+) = 'ITEM_TYPE'
AND LOOKUP.ENABLED_FLAG(+) = 'Y'
AND (LOOKUP.START_DATE_ACTIVE IS NULL OR LOOKUP.START_DATE_ACTIVE < SYSDATE)
AND (LOOKUP.END_DATE_ACTIVE IS NULL OR LOOKUP.END_DATE_ACTIVE > SYSDATE);
SELECT TEMPLATES.CUSTOMIZATION_CODE INTO l_customization_code
FROM FND_COMMON_LOOKUPS LOOKUP,
EGO_CRITERIA_TEMPLATES_V TEMPLATES
WHERE LOOKUP.LOOKUP_TYPE = 'ITEM_TYPE'
AND LOOKUP.ENABLED_FLAG = 'Y'
AND (LOOKUP.START_DATE_ACTIVE IS NULL OR LOOKUP.START_DATE_ACTIVE < SYSDATE)
AND (LOOKUP.END_DATE_ACTIVE IS NULL OR LOOKUP.END_DATE_ACTIVE > SYSDATE)
AND LOOKUP.LOOKUP_CODE = TEMPLATES.CLASSIFICATION1
AND TEMPLATES.CUSTOMIZATION_APPLICATION_ID = 702
AND TEMPLATES.REGION_APPLICATION_ID = 702
AND TEMPLATES.REGION_CODE = 'BOM_ITEM_TYPE_REGION'
AND LOOKUP_CODE = l_parent_item_type
AND TEMPLATES.CLASSIFICATION1 = l_parent_item_type;
SELECT 'Y' INTO l_return_status FROM DUAL
WHERE l_child_item_type IN
(
SELECT VALUE_VARCHAR2 FROM EGO_CRITERIA_V
WHERE
CUSTOMIZATION_APPLICATION_ID = 702 AND
REGION_APPLICATION_ID = 702 AND
REGION_CODE = 'BOM_ITEM_TYPE_REGION'
AND CUSTOMIZATION_CODE = l_customization_code
);
Error_Handler.Translate_And_Insert_Messages
( p_mesg_token_tbl => l_mesg_token_tbl
, p_application_id => 'BOM'
);
PROCEDURE Check_Entity_Delete
( x_return_status IN OUT NOCOPY VARCHAR2
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, p_bom_component_rec IN Bom_Bo_Pub.Bom_Comps_Rec_Type
, p_bom_Comp_Unexp_Rec IN Bom_Bo_Pub.Bom_Comps_Unexposed_Rec_Type
)
IS
BEGIN
NULL;
END Check_Entity_Delete;
SELECT count(*) INTO l_total FROM bom_inventory_components WHERE
bill_sequence_id = p_bom_comp_unexp_rec.bill_sequence_id
and sysdate between effectivity_date and
nvl(disable_date,sysdate + 1);
SELECT bom_item_type
INTO l_Bom_comp_item_type
FROM mtl_system_items
WHERE inventory_item_id = p_component_item_id
AND organization_id = p_organization_id;
SELECT process_enabled_flag
INTO l_OPM_org
FROM mtl_parameters
WHERE organization_id = p_organization_id;
l_token_tbl.DELETE(2);
SELECT 'X' date_available FROM sys.dual
WHERE EXISTS (
SELECT 1 from BOM_Inventory_Components
WHERE Component_Item_Id = X_Member_Item_Id
AND Bill_Sequence_Id = X_Bill_Sequence_Id
AND Operation_Seq_Num = X_Operation_Seq_Num
AND Component_Sequence_Id <> X_Comp_Seq_Id
AND (( RowId <> X_RowID ) or
(X_RowId IS NULL))
AND ( X_Disable_Date IS NULL
OR ( Trunc(X_Disable_Date) >
Trunc(Effectivity_Date)
)
)
AND ( Trunc(X_Effectivity_Date) <
Trunc(Disable_Date)
OR Disable_Date IS NULL
)
);
SELECT 'X' unit_available FROM sys.dual
WHERE EXISTS (
SELECT 1 from BOM_Inventory_Components
WHERE Component_Item_Id = X_Member_Item_Id
AND Bill_Sequence_Id = X_Bill_Sequence_Id
AND Operation_Seq_Num = X_Operation_Seq_Num
AND DISABLE_DATE IS NULL --bug:5347036 Consider only enabled components
AND Component_Sequence_Id <> X_Comp_Seq_Id
AND (RowId <> X_RowID
OR X_RowId IS NULL)
AND (X_To_End_Item_Number IS NULL
OR X_To_End_Item_Number >
From_End_Item_Unit_Number)
AND (X_From_End_Item_Number <
To_End_Item_Unit_Number
OR To_End_Item_Unit_Number IS NULL
)
);
g_Token_Tbl.DELETE(2);
g_Token_Tbl.DELETE(3);
g_token_tbl.delete(2);
g_token_tbl.delete(2);
g_Token_Tbl.DELETE(2);
g_token_tbl.delete(3);
g_token_tbl.delete(2);
g_token_tbl.delete(2);
g_token_tbl.delete(2);
SELECT product_family_item_id
INTO Pf_Item_Id
FROM mtl_system_items_b
WHERE inventory_item_id = g_rev_comp_Unexp_rec.component_item_id
AND organization_id = g_rev_comp_Unexp_rec.organization_id;
SELECT 'Valid'
INTO l_dummy
FROM bom_bill_of_materials
WHERE assembly_item_id =
g_rev_comp_unexp_rec.component_item_id
AND organization_id =
g_rev_comp_unexp_rec.organization_id
AND rownum < 2; -- bug 2986752
SELECT locator_type
FROM mtl_item_sub_ast_trk_val_v
WHERE inventory_item_id = g_rev_comp_Unexp_rec.component_item_id
AND organization_id = g_rev_comp_Unexp_rec.organization_id
AND secondary_inventory_name =
g_rev_component_rec.supply_subinventory;
SELECT locator_type
FROM mtl_item_sub_trk_val_v
WHERE inventory_item_id = g_rev_comp_Unexp_rec.component_item_id
AND organization_id = g_rev_comp_Unexp_rec.organization_id
AND secondary_inventory_name =
g_rev_component_rec.supply_subinventory;
SELECT locator_type
FROM mtl_sub_ast_trk_val_v
WHERE organization_id = g_rev_comp_Unexp_rec.organization_id
AND secondary_inventory_name =
g_rev_component_rec.supply_subinventory;
SELECT locator_type
FROM mtl_subinventories_trk_val_v
WHERE organization_id = g_rev_comp_Unexp_rec.organization_id
AND secondary_inventory_name =
g_rev_component_rec.supply_subinventory;
SELECT DECODE(restrict_subinventories_code, 1, 'Y', 'N'),
inventory_asset_flag
INTO l_RestrictSubInventory,
l_InventoryAsset
FROM mtl_system_items
WHERE inventory_item_id = g_rev_comp_Unexp_rec.component_item_id
AND organization_id = g_rev_comp_Unexp_rec.organization_id;
SELECT 'checking for duplicates' dummy
FROM sys.dual
WHERE EXISTS (
SELECT null
FROM mtl_item_locations
WHERE organization_id =
g_rev_comp_Unexp_rec.organization_id
AND inventory_location_id =
g_rev_comp_Unexp_rec.supply_locator_id
AND subinventory_code <>
g_rev_component_rec.supply_subinventory
);
SELECT stock_locator_control_code
INTO l_org_locator_control
FROM mtl_parameters
WHERE organization_id = g_rev_comp_Unexp_rec.organization_id;
SELECT location_control_code
INTO l_item_locator_control
FROM mtl_system_items
WHERE organization_id = g_rev_comp_Unexp_rec.organization_id
AND inventory_item_id = g_rev_comp_Unexp_rec.component_item_id;
SELECT RESTRICT_LOCATORS_CODE
INTO l_item_loc_restricted
FROM mtl_system_items
WHERE organization_id = g_rev_comp_Unexp_rec.organization_id
AND inventory_item_id = g_rev_comp_Unexp_rec.component_item_id;
SELECT 'Valid'
INTO l_dummy
FROM mtl_item_locations mil,
mtl_secondary_locators msl
WHERE msl.inventory_item_id =
g_rev_comp_Unexp_rec.component_item_id
AND msl.organization_id =
g_rev_comp_Unexp_rec.organization_id
AND msl.subinventory_code =
g_rev_component_rec.supply_subinventory
AND msl.secondary_locator =
g_rev_comp_Unexp_rec.supply_locator_id
AND mil.inventory_location_id =
msl.secondary_locator
AND mil.organization_id =
msl.organization_id
AND NVL(mil.disable_date, SYSDATE+1) >
SYSDATE ;
SELECT 'Valid'
INTO l_dummy
FROM mtl_item_locations mil
WHERE mil.subinventory_code =
g_rev_component_rec.supply_subinventory
AND mil.inventory_location_id =
g_rev_comp_Unexp_rec.supply_locator_id
AND mil.organization_id =
g_rev_comp_Unexp_rec.organization_id
AND NVL(mil.DISABLE_DATE, SYSDATE+1) >
SYSDATE;
SELECT alternate_bom_designator
FROM bom_bill_of_materials
WHERE bill_sequence_id = g_rev_comp_unexp_rec.bill_sequence_id;
SELECT 'x'
FROM bom_operation_sequences bos, bom_operational_routings bor
WHERE bor.common_routing_sequence_id = bos.routing_sequence_id
AND bor.organization_id = g_rev_comp_unexp_rec.organization_id
AND bor.assembly_item_id = g_rev_comp_unexp_rec.revised_item_id
AND nvl(bor.alternate_routing_designator,
nvl(l_alternate_bom_designator, 'NONE')) =
nvl(l_alternate_bom_designator, 'NONE');
SELECT 'Valid' valid_op_seq
FROM
/* bom_operational_routings bor,
bom_operation_sequences bos
WHERE bor.assembly_item_id =
g_rev_comp_Unexp_rec.revised_item_id
AND bor.organization_id =
g_rev_comp_Unexp_rec.organization_id
AND NVL(bor.alternate_routing_designator, 'NONE') =
NVL(g_rev_component_rec.alternate_bom_code, 'NONE')
AND bos.routing_sequence_id = bor.common_routing_sequence_id
*/
bom_operation_sequences bos
WHERE
bos.routing_sequence_id =
(
select common_routing_sequence_id
from bom_operational_routings
where assembly_item_id = g_rev_comp_Unexp_rec.revised_item_id
and organization_id = g_rev_comp_Unexp_rec.organization_id
and nvl(alternate_routing_designator,
nvl(g_rev_component_rec.alternate_bom_code, 'NONE')) =
nvl(g_rev_component_rec.alternate_bom_code, 'NONE')
and (g_rev_component_rec.alternate_bom_code is null
or (g_rev_component_rec.alternate_bom_code is not null
and (alternate_routing_designator =
g_rev_component_rec.alternate_bom_code
or not exists
(select null
from bom_operational_routings bor2
where bor2.assembly_item_id =
g_rev_comp_Unexp_rec.revised_item_id
and bor2.organization_id = g_rev_comp_Unexp_rec.organization_id
and bor2.alternate_routing_designator =
g_rev_component_rec.alternate_bom_code
)
)
)
)
)
AND bos.operation_seq_num =
decode(g_rev_component_rec.new_operation_sequence_number,
NULL,
g_rev_component_rec.Operation_Sequence_Number,
g_rev_component_rec.new_Operation_sequence_number
)
-- commented following AND condition for bug 7339077
-- AND nvl(trunc(disable_date), trunc(sysdate)+1) > trunc(sysdate)
-- added following AND conditon for bug 7339077
AND nvl(disable_date, trunc(sysdate)+1) >= sysdate
AND ( ( p_eco_for_production = 2
AND nvl(bos.eco_for_production, 2) <> 1
)
OR ( p_eco_for_production = 1
AND ( bos.implementation_date IS NOT NULL
OR ( bos.revised_item_sequence_id
= g_rev_comp_unexp_rec.revised_item_sequence_id)
)
)
) ;
SELECT 'Already Used' op_seq_used
FROM bom_inventory_components bic
WHERE bic.bill_sequence_id =
g_rev_comp_Unexp_rec.bill_sequence_id
AND bic.component_item_id =
g_rev_comp_Unexp_rec.component_item_id
AND bic.operation_seq_num =
decode(g_rev_component_rec.new_operation_sequence_number,
NULL,
g_rev_component_rec.operation_sequence_number,
g_rev_component_rec.new_operation_sequence_number
)
/* Added extra condition to accomodate bill components
*/
AND bic.component_sequence_id <>
g_rev_comp_Unexp_rec.component_sequence_id
/*added extra condition to avoid validation against the same comp*/
AND (trunc(bic.effectivity_date)
< trunc(g_rev_component_rec.start_effective_date)
AND nvl(trunc(bic.disable_date),
trunc(g_rev_component_rec.start_effective_date) + 2)
> trunc(g_rev_component_rec.start_effective_date));
SELECT 'Already Used' op_seq_used
FROM bom_inventory_components bic
WHERE bic.bill_sequence_id =
g_rev_comp_Unexp_rec.bill_sequence_id
AND bic.component_item_id =
g_rev_comp_Unexp_rec.component_item_id
AND bic.operation_seq_num =
decode(g_rev_component_rec.new_operation_sequence_number,
NULL,
g_rev_component_rec.operation_sequence_number,
g_rev_component_rec.new_operation_sequence_number
)
AND DECODE(g_rev_component_rec.new_effectivity_date,
NULL,
g_rev_component_rec.start_effective_date,
g_rev_component_rec.new_effectivity_date
) between bic.effectivity_date AND NVL(bic.disable_date, SYSDATE)
AND bic.component_sequence_id <> g_rev_comp_unexp_rec.component_sequence_id
/* Added extra condition to accomodate bill components
*/
AND (bic.from_end_item_unit_number
<= g_rev_component_rec.from_end_item_unit_number
AND NVL(bic.to_end_item_unit_number,
g_rev_component_rec.from_end_item_unit_number)
>= g_rev_component_rec.from_end_item_unit_number);
SELECT 'Already Exists' op_seq_exists
FROM bom_component_operations bco
WHERE bco.component_sequence_id =
g_rev_comp_Unexp_rec.component_sequence_id
AND bco.operation_seq_num =
decode(g_rev_component_rec.new_operation_sequence_number,
NULL,
g_rev_component_rec.operation_sequence_number,
g_rev_component_rec.new_operation_sequence_number
);
-- Effectivity date so it cannot be inserted. So return an error
-- hence, this function will return a false.
l_eco_for_production := NVL(Bom_Globals.Get_Eco_For_Production,2) ;
SELECT 'Already Used' unit_num_used
FROM bom_inventory_components bic
WHERE bic.bill_sequence_id =
g_rev_comp_Unexp_rec.bill_sequence_id
AND bic.component_item_id =
g_rev_comp_Unexp_rec.component_item_id
AND bic.operation_seq_num =
decode(g_rev_component_rec.new_operation_sequence_number,
NULL,
g_rev_component_rec.operation_sequence_number,
g_rev_component_rec.new_operation_sequence_number
)
AND DECODE(g_rev_component_rec.new_effectivity_date,
NULL,
g_rev_component_rec.start_effective_date,
g_rev_component_rec.new_effectivity_date
) between bic.effectivity_date AND NVL(bic.disable_date, SYSDATE)
AND bic.component_sequence_id <> g_rev_comp_unexp_rec.component_sequence_id
/* Added extra condition to accomodate bill components
*/
AND (( bic.from_end_item_unit_number
<= DECODE(g_rev_component_rec.new_from_end_item_unit_number,FND_API.G_MISS_NUM,
g_rev_component_rec.from_end_item_unit_number,
NULL, g_rev_component_rec.from_end_item_unit_number,
g_rev_component_rec.new_from_end_item_unit_number
)
AND NVL(bic.to_end_item_unit_number,g_rev_component_rec.from_end_item_unit_number)
>= g_rev_component_rec.from_end_item_unit_number
)
OR
( bic.from_end_item_unit_number
> DECODE(g_rev_component_rec.new_from_end_item_unit_number,FND_API.G_MISS_NUM,
g_rev_component_rec.from_end_item_unit_number,
NULL, g_rev_component_rec.from_end_item_unit_number,
g_rev_component_rec.new_from_end_item_unit_number
)
AND NVL(bic.to_end_item_unit_number,g_rev_component_rec.from_end_item_unit_number)
<= g_rev_component_rec.from_end_item_unit_number
)
);
SELECT 'Exist' there_Exist
FROM bom_component_operations bco,
bom_components_b comp
WHERE bco.component_sequence_id = comp.component_sequence_id
AND comp.component_sequence_id = g_rev_comp_Unexp_rec.component_sequence_id
AND comp.optional = 1
AND g_rev_component_rec.Optional = 2;
SELECT 'Valid' is_Valid
FROM mtl_system_items assy,
mtl_system_items comp
WHERE assy.organization_id = g_rev_comp_Unexp_rec.organization_id
AND assy.inventory_item_id =
g_rev_comp_Unexp_rec.revised_item_id
AND comp.organization_id = g_rev_comp_Unexp_rec.organization_id
AND comp.inventory_item_id =
g_rev_comp_Unexp_rec.component_item_id
AND ( ( assy.bom_item_type IN ( Bom_Globals.G_PLANNING, Bom_Globals.G_STANDARD)
AND g_rev_component_rec.optional = 2 /* NO */
)
OR
( assy.bom_item_type IN ( Bom_Globals.G_MODEL, Bom_Globals.G_OPTION_CLASS)
AND assy.pick_components_flag = 'Y'
/* PTO Model or PTO Option Class */
AND comp.bom_item_type = Bom_Globals.G_STANDARD
AND comp.replenish_to_order_flag = 'Y'
AND comp.base_item_id IS NULL
AND g_rev_component_rec.Optional = 1
)
);
SELECT 1
FROM BOM_BILL_OF_MATERIALS bom
WHERE bom.source_bill_sequence_id =
g_rev_comp_Unexp_rec.bill_sequence_id
AND bom.organization_id <>
g_rev_comp_Unexp_rec.organization_id
AND NOT EXISTS (SELECT 1
FROM MTL_SYSTEM_ITEMS msi
WHERE msi.organization_id =
bom.organization_id
AND msi.inventory_item_id =
g_rev_comp_Unexp_rec.component_item_id
AND msi.bom_enabled_flag = 'Y' -- Uncommented for bug 5925020
);
SELECT 1
FROM BOM_BILL_OF_MATERIALS bom
WHERE bom.source_bill_sequence_id =
g_rev_comp_Unexp_rec.bill_sequence_id
AND bom.organization_id <> g_rev_comp_Unexp_rec.organization_id
AND NOT EXISTS (SELECT 1
FROM MTL_SYSTEM_ITEMS msi
WHERE msi.organization_id = bom.organization_id
AND msi.inventory_item_id =
g_rev_comp_Unexp_rec.component_item_id
);
SELECT 1
FROM BOM_BILL_OF_MATERIALS bom
WHERE bom.source_bill_sequence_id =
g_rev_comp_Unexp_rec.bill_sequence_id
AND bom.organization_id <> g_rev_comp_Unexp_rec.organization_id
AND NOT EXISTS (SELECT 1
FROM MTL_SYSTEM_ITEMS msi
WHERE msi.organization_id = bom.organization_id
AND msi.inventory_item_id =
g_rev_comp_Unexp_rec.component_item_id
AND msi.bom_enabled_flag = 'Y' -- Uncommented for bug 5925020
AND (( bom.assembly_type = 1 AND
((msi.eng_item_flag = 'N' and l_allow_eng_comps = '2' ) or l_allow_eng_comps = '1' ) -- Bug 6274872
)
OR bom.assembly_type = 2
)
);
SELECT bill_Sequence_id
FROM BOM_BILL_OF_MATERIALS
WHERE common_bill_Sequence_id <> source_bill_sequence_id
AND source_bill_sequence_id = g_rev_comp_Unexp_rec.bill_sequence_id
AND organization_id <> g_rev_comp_Unexp_rec.organization_id;
SELECT 1
FROM BOM_OPERATION_SEQUENCES bos, BOM_OPERATIONAL_ROUTINGS bor, BOM_BILL_OF_MATERIALS bom
WHERE (bor.routing_sequence_id = bos.routing_sequence_id
AND bor.assembly_item_id = bom.assembly_item_id
AND bor.organization_id = bom.organization_id
AND nvl(bor.alternate_routing_designator, 'XXX') = nvl(bom.alternate_bom_designator, 'XXX')
AND bom.bill_sequence_id = p_bill_seq_id
AND bos.operation_sequence_id <> g_rev_component_rec.New_Operation_Sequence_Number)
OR g_rev_component_rec.New_Operation_Sequence_Number = 1;*/
SELECT 1
FROM bom_bill_of_materials
WHERE assembly_item_id = g_Rev_Comp_Unexp_Rec.revised_item_id
AND organization_id = g_Rev_Comp_Unexp_Rec.Organization_Id
AND NVL(alternate_bom_designator, 'NONE') = 'NONE';
SELECT 1
FROM bom_bill_of_materials
WHERE assembly_item_id = p_revied_item_id
AND organization_id = p_organization_id
AND NVL(alternate_bom_designator, 'NONE') = 'NONE';
SELECT 'Rev Item is only Eco for altenate routing'
FROM ENG_REVISED_ITEMS eri
, BOM_OPERATIONAL_ROUTINGS bor
WHERE bor.alternate_routing_designator IS NOT NULL
AND eri.routing_sequence_id = bor.routing_sequence_id(+)
AND eri.routing_sequence_id IS NOT NULL
AND eri.bill_sequence_id IS NULL
AND NVL(eri.from_end_item_unit_number, 'NONE')
= NVL(p_from_end_item_number, 'NONE')
AND NVL(eri.new_item_revision,'NULL') = NVL(p_new_item_revision ,'NULL')
AND NVL(eri.new_routing_revision,'NULL') = NVL(p_new_routing_revsion,'NULL')
AND TRUNC(eri.scheduled_date) = TRUNC(p_effective_date)
AND eri.change_notice = p_change_notice
AND eri.organization_id = p_organization_id
AND eri.revised_item_id = p_revised_item_id ;
SELECT 'Cmp does not exist'
FROM SYS.DUAL
WHERE EXISTS (SELECT NULL
FROM WIP_DISCRETE_JOBS wdj
WHERE (wdj.status_type <> 1
OR
NOT EXISTS(SELECT NULL
FROM WIP_REQUIREMENT_OPERATIONS wro
WHERE (wro.operation_seq_num = p_operation_seq_num
OR p_operation_seq_num = 1)
AND wro.inventory_item_id = p_rev_comp_item_id
AND wro.wip_entity_id = wdj.wip_entity_id)
)
AND wdj.lot_number = p_lot_number
AND wdj.organization_id = p_organization_id
AND wdj.primary_item_id = p_rev_item_id
) ;
SELECT 'Cmp does not exist'
FROM SYS.DUAL
WHERE EXISTS (SELECT NULL
FROM WIP_DISCRETE_JOBS wdj
, WIP_ENTITIES we
, WIP_ENTITIES we1
, WIP_ENTITIES we2
WHERE (wdj.status_type <> 1
OR
NOT EXISTS (SELECT NULL
FROM WIP_REQUIREMENT_OPERATIONS wro
WHERE (wro.operation_seq_num = p_operation_seq_num
OR p_operation_seq_num = 1)
AND wro.inventory_item_id = p_rev_comp_item_id
AND wro.wip_entity_id = wdj.wip_entity_id)
)
AND wdj.wip_entity_id = we.wip_entity_id
AND we.organization_Id = p_organization_id
AND we.wip_entity_name >= we1.wip_entity_name
AND we.wip_entity_name <= we2.wip_entity_name
AND we1.wip_entity_id = p_from_wip_entity_id
AND we2.wip_entity_id = NVL(p_to_wip_entity_id, p_from_wip_entity_id)
) ;
SELECT 'Cmp does not exist'
FROM SYS.DUAL
WHERE EXISTS (SELECT NULL
FROM WIP_DISCRETE_JOBS wdj
WHERE (wdj.status_type <> 1
OR
NOT EXISTS(SELECT NULL
FROM WIP_REQUIREMENT_OPERATIONS wro
WHERE (wro.operation_seq_num = p_operation_seq_num
OR p_operation_seq_num = 1)
AND wro.inventory_item_id = p_rev_comp_item_id
AND wro.wip_entity_id = wdj.wip_entity_id)
)
AND wdj.wip_entity_id = p_from_wip_entity_id
) ;
SELECT 'Y' into l_deref_bom
FROM BOM_BILL_OF_MATERIALS
WHERE bill_sequence_id = common_bill_sequence_id
AND bill_sequence_id <> nvl(source_bill_sequence_id, common_bill_sequence_id)
AND bill_sequence_id = p_bill_sequence_id;
SELECT 'Y'
INTO l_dummy
FROM BOM_STRUCTURES_B
WHERE BILL_SEQUENCE_ID = p_bill_sequence_id
AND BILL_SEQUENCE_ID <> SOURCE_BILL_SEQUENCE_ID;
SELECT count(component_sequence_id) number_of_desgs
FROM bom_reference_designators
WHERE component_sequence_id =
p_rev_comp_unexp_rec.component_sequence_id;
SELECT revised_item_id, change_notice, organization_id
FROM eng_revised_items
WHERE revised_item_sequence_id =
p_rev_comp_Unexp_rec.revised_item_sequence_id;
SELECT 'Valid'
FROM eng_revised_items eri
WHERE eri.revised_item_sequence_id =
p_rev_comp_Unexp_rec.revised_item_sequence_id
AND eri.bill_sequence_id IS NULL
AND NOT EXISTS (SELECT 1
FROM bom_bill_of_materials bom
WHERE bom.bill_sequence_id =
p_rev_comp_Unexp_rec.bill_sequence_id
);
SELECT 'Valid'
FROM BOM_inventory_components
WHERE item_num = p_rev_component_rec.item_sequence_number
AND component_item_id <> p_rev_comp_unexp_rec.component_item_id
AND trunc(effectivity_date) <=
trunc(p_rev_component_rec.start_effective_date)
AND nvl(trunc(disable_date),
trunc(p_rev_component_rec.start_effective_date) + 1) >=
trunc(p_rev_component_rec.start_effective_date)
AND bill_sequence_id = p_Rev_Comp_Unexp_rec.bill_sequence_id;
SELECT 'Valid'
FROM BOM_inventory_components
WHERE item_num = p_rev_component_rec.item_sequence_number
AND component_item_id <> p_rev_comp_unexp_rec.component_item_id
AND disable_date is NULL
AND from_end_item_unit_number <=
p_rev_component_rec.from_end_item_unit_number
AND NVL(to_end_item_unit_number,
p_rev_component_rec.from_end_item_unit_number) >=
p_rev_component_rec.from_end_item_unit_number
AND bill_sequence_id = p_Rev_Comp_Unexp_rec.bill_sequence_id;
SELECT distinct 'I'
FROM fnd_product_installations
-- WHERE application_id = 300 -- Order Entry
WHERE application_id = 660 -- ONT: Order Management
AND status = 'I';
SELECT replenish_to_order_flag, pick_components_flag
FROM mtl_system_items
WHERE inventory_item_id =
p_rev_comp_unexp_rec.revised_item_id
AND organization_id = p_rev_comp_unexp_rec.organization_id;
SELECT effectivity_control
FROM mtl_system_items
WHERE inventory_item_id = p_rev_comp_unexp_rec.revised_item_id
AND organization_id = p_rev_comp_unexp_rec.organization_id;
SELECT To_End_Item_Unit_Number
FROM BOM_Inventory_Components
WHERE component_sequence_id =
g_rev_comp_unexp_rec.old_component_sequence_id;
SELECT 'Old Comp is invalid'
FROM SYS.DUAL
WHERE NOT EXISTS ( SELECT NULL
FROM BOM_INVENTORY_COMPONENTS ic
WHERE TRUNC(ic.effectivity_date) <=
TRUNC(p_rev_component_rec.start_effective_date)
AND NVL(ic.disable_date,
TRUNC(p_rev_component_rec.start_effective_date)+1)
> p_rev_component_rec.start_effective_date
AND NVL(ic.disable_date , SYSDATE + 1) > SYSDATE
AND NVL(ic.revised_item_sequence_id, -999)
<> NVL(p_rev_comp_unexp_rec.revised_item_sequence_id, -100)
AND NOT EXISTS (SELECT NULL
FROM bom_inventory_components ic2
WHERE ic2.revised_item_sequence_id
= NVL(p_rev_comp_unexp_rec.revised_item_sequence_id,
-888)
AND decode(ic2.implementation_date,
null,
ic2.old_component_sequence_id,
ic2.component_sequence_id) =
decode(ic.implementation_date,
null,
ic.old_component_sequence_id,
ic.component_sequence_id)
AND ic2.component_sequence_id <>
p_rev_comp_unexp_rec.component_sequence_id
)
AND (( p_eco_for_production = 2
AND NVL(ic.eco_for_production, 2) <> 1 )
OR (p_eco_for_production = 1
AND ic.implementation_date IS NOT NULL
)
)
AND ic.component_sequence_id = p_old_comp_seq_id
) ;
IF (p_rev_component_rec.transaction_type = Bom_Globals.G_OPR_UPDATE) THEN
If(p_rev_component_rec.Basis_type = 2) THEN
select count(*) into l_total_rds from bom_reference_designators
where nvl(p_rev_component_rec.acd_type,1) <>3 and component_sequence_id=p_Rev_Comp_Unexp_Rec.component_sequence_id;
SELECT assy.bom_item_type,
assy.pick_components_flag,
assy.replenish_to_order_flag,
assy.wip_supply_type,
DECODE(NVL(assy.base_item_id, 0), 0 , 'N', 'Y'),
assy.eng_item_flag,
assy.atp_components_flag,
assy.atp_flag,
assy.bom_enabled_flag,
assy.effectivity_control, --2044133
assy.tracking_quantity_ind,
comp.bom_item_type,
comp.pick_components_flag,
comp.replenish_to_order_flag,
comp.wip_supply_type,
DECODE(NVL(comp.base_item_id, 0), 0 , 'N', 'Y'),
comp.eng_item_flag,
comp.atp_components_flag,
comp.atp_flag,
comp.bom_enabled_flag,
comp.ato_forecast_control,
comp.effectivity_control, --2044133
comp.tracking_quantity_ind
INTO g_Assy_Item_Type,
g_Assy_PTO_flag,
g_Assy_ATO_flag,
g_Assy_Wip_Supply_Type,
g_Assy_Config,
g_Assy_Eng_Flag,
g_Assy_ATP_Comp_flag,
g_Assy_ATP_Check_flag,
g_Assy_Bom_Enabled_flag,
g_Assy_Effectivity_Control, --2044133
G_Assy_Tracking_Quantity_Ind,
g_Comp_Item_Type,
g_Comp_PTO_flag,
g_Comp_ATO_flag,
g_Comp_Wip_Supply_Type,
g_Comp_Config,
g_Comp_Eng_Flag,
g_Comp_ATP_Comp_flag,
g_Comp_ATP_Check_flag,
g_Comp_Bom_Enabled_flag,
g_Comp_ATO_Forecast_Control,
g_Comp_Effectivity_Control, --2044133
G_Comp_Tracking_Quantity_Ind
FROM mtl_system_items assy,
mtl_system_items comp
WHERE assy.organization_id = g_rev_Comp_Unexp_Rec.Organization_Id
AND assy.inventory_item_id = g_rev_Comp_Unexp_Rec.revised_item_id
AND comp.organization_id = g_rev_Comp_Unexp_Rec.Organization_Id
AND comp.inventory_item_id = g_rev_Comp_Unexp_Rec.Component_item_id;
select assembly_type
into g_Assy_Assembly_Type
--bug: 4161794. Introduced new global variable to hold value of assembly type
-- of the header.
from bom_bill_of_materials
where bill_sequence_id = p_rev_comp_Unexp_rec.bill_sequence_id;
g_token_tbl.delete;
g_token_tbl.delete;
g_token_tbl.delete;
g_token_tbl.delete;
g_token_tbl.delete;
g_token_tbl.delete;
SELECT 'Valid'
INTO l_dummy
FROM bom_bill_of_materials bom
WHERE bom.bill_sequence_id = p_rev_comp_unexp_rec.bill_sequence_id
AND bom.source_bill_sequence_id <> bom.bill_sequence_id
AND nvl(p_rev_component_rec.acd_type, 1) in (1,3);
-- Check if ACD type is not Disable or Update
IF( nvl(p_rev_component_rec.acd_type, 1) NOT IN (2 , 3)) THEN -- Change or Disable
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Message_name => 'BOM_COMP_ITEM_BOM_NOT_ENABLED'
, p_token_tbl => g_token_tbl
);
g_Token_Tbl.Delete;
g_token_tbl.delete;
p_rev_component_rec.Transaction_Type = BOM_GLOBALS.G_OPR_UPDATE
THEN
--
-- Verify that the user is not trying to Update non-updateable columns
--
IF p_Old_Rev_Component_Rec.Shipping_Allowed <>
p_rev_component_rec.shipping_allowed
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'BOM_SHIP_ALLOWED_NOT_UPDATE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_token_tbl => g_token_tbl
);
AND p_rev_component_rec.transaction_type = BOM_Globals.G_OPR_UPDATE
THEN
--if the change on common bom is only in the wip attributes,
--allow it.
NULL;
-- ACD Type not updateable
--
IF p_rev_component_rec.acd_type <>
p_old_rev_component_rec.acd_type AND
Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_ECO_BO
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'BOM_ACD_TYPE_NOT_UPDATEABLE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_token_tbl => g_token_tbl
);
-- Verify that the user is not trying to update a component which
-- is Disabled on the ECO
--
IF p_old_rev_component_rec.acd_type = 3 AND
Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_ECO_BO
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'BOM_COMPONENT_DISABLED'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_token_tbl => g_token_tbl
);
-- User cannot update to_end_item_unit_number when the component
-- is disabled.
IF NVL(p_rev_component_rec.acd_type, 1) = 3 AND
p_rev_component_rec.to_end_item_unit_number <>
p_old_rev_component_rec.to_end_item_unit_number
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'BOM_DISABLE_TOUNIT_NONUPD'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_token_tbl => g_token_tbl
);
END IF; /* Operation UPDATE ENDS */
(BOM_GLOBALS.G_OPR_CREATE, BOM_GLOBALS.G_OPR_UPDATE)
THEN
/*********************************************************************
--
-- Verify yield factor
-- IF Component is Option Class or bill is planning
-- then yield must be 1
-- If yield is >0 and less than 1 then give a warning.
--
*********************************************************************/
IF ((p_control_rec.caller_type = 'FORM' AND
p_control_rec.validation_controller = 'YIELD')
OR
p_control_rec.caller_type <> 'FORM')
AND
p_rev_component_rec.projected_yield <> 1 THEN
IF g_assy_item_type = 3 -- Planning parent
OR
g_comp_item_type = 2 -- Option Class component
THEN
IF FND_MSG_PUB.Check_Msg_Level
(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
g_token_tbl(2).token_name :=
'REVISED_ITEM_NAME';
g_token_tbl.delete(2);
g_token_tbl.delete(2);
for updates and creates */
IF (G_Assy_Tracking_Quantity_Ind <>'P' or G_Comp_Tracking_Quantity_Ind <>'P') then
Error_Handler.Add_Error_Token
( p_Message_Name => 'BOM_DUAL_UOM_ITEMS'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_message_type => 'E'
);
-- for both operations Create and Update
--
-- If the transaction type is CREATE, then the new_effective_date column is
-- ignored.
-- but if the transaction type is update, then the new_effectivity_date can be
-- used to update the effectivity date of a future effective component to bring it closer
-- in its effectivity cyle. In this the validation for Insert and Update will differ
-- If the new_effectivity_date col is not null and missing then it must be greater or =
-- to SYSDATE.
-- Also, disable_date must be greater or equal to the new_effective_date
IF p_rev_component_rec.transaction_type in (BOM_GLOBALS.G_OPR_CREATE,
BOM_GLOBALS.G_OPR_UPDATE) AND
p_rev_component_rec.disable_date <
p_rev_component_rec.start_effective_date THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'BOM_COMP_DIS_DATE_INVALID'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => g_Token_Tbl
);
ELSIF p_rev_component_rec.transaction_type = BOM_GLOBALS.G_OPR_UPDATE AND
p_rev_component_rec.new_effectivity_date IS NOT NULL AND
p_rev_component_rec.new_effectivity_date <> FND_API.G_MISS_DATE AND
(
-- p_rev_component_rec.new_effectivity_date < SYSDATE OR -- Bug3281414
p_rev_component_rec.disable_date < p_rev_component_rec.new_effectivity_date
)
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'BOM_COMP_DIS_DATE_INVALID'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => g_Token_Tbl
);
-- Verify that if the user is trying to create or update rev. comp
-- to quantity related when the quantity_per_assembly is fractional
--
*********************************************************************/
IF round(p_rev_component_rec.quantity_per_assembly) <>
p_rev_component_rec.quantity_per_assembly AND
p_rev_component_rec.quantity_related = 1
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'BOM_QTY_REL_QTY_FRACTIONAL'
, p_Mesg_Token_Tbl=> l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl=> l_Mesg_Token_Tbl
, p_Token_Tbl => g_Token_Tbl
);
SELECT 'Component Implemented'
INTO l_dummy
FROM bom_inventory_components
WHERE component_sequence_id =
p_rev_comp_Unexp_rec.old_component_sequence_id
AND implementation_date IS NOT NULL;
BOM_Globals.G_OPR_UPDATE
) AND
(
NVL(p_old_rev_component_rec.operation_sequence_number, 1) <>
NVL(p_rev_component_rec.new_operation_sequence_number, 1) AND
p_rev_component_rec.new_operation_sequence_number <> FND_API.G_MISS_NUM
)
)
THEN
/*************************************************************
--
-- If Operation_Seq_Num is not 1 then there must be a routing
-- for the revised item.
-- Added by AS on 08/20/99 to accomodate calls from the
-- ECO form to perform this validation.
*************************************************************/
IF NOT Check_Routing_Exists
THEN
IF FND_MSG_PUB.Check_Msg_Level
(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'BOM_ONLY_ONE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => g_Token_Tbl
);
-- case of Creates and in Case of Updates new_operation_sequence
-- must be valid if the user is trying to update
-- operation_sequence_number
**************************************************************/
l_result := Check_Op_Seq(l_is_item_unit_controlled);
g_Token_Tbl.DELETE(2);
g_Token_Tbl.DELETE(2);
SELECT operation_seq_num
INTO l_result
FROM bom_inventory_components
WHERE component_sequence_id =
p_rev_comp_unexp_rec.old_component_sequence_id
AND operation_seq_num =
p_Rev_Component_rec.old_operation_sequence_number;
g_Token_Tbl.DELETE(2);
else if txn type is update then pass new_effectivity_date.
*/
IF p_rev_component_rec.transaction_type = BOM_GLOBALS.G_OPR_CREATE THEN
l_new_compare_date := p_rev_component_rec.start_effective_date;
ELSIF p_rev_component_rec.transaction_type = BOM_GLOBALS.G_OPR_UPDATE THEN
l_new_compare_date := p_rev_component_rec.new_effectivity_date;
p_rev_component_rec.Transaction_Type = BOM_GLOBALS.G_OPR_UPDATE
)
AND
NVL(p_Old_rev_component_rec.optional, 0) <>
p_rev_component_rec.optional
)
)
THEN
l_Result := Check_PTO_ATO_for_Optional;
p_rev_component_rec.Transaction_Type = BOM_GLOBALS.G_OPR_UPDATE
)
AND
NVL(p_Old_rev_component_rec.planning_percent, 0) <>
p_rev_component_rec.planning_percent
)
)
THEN
l_Result := Check_Planning_Percent;
g_Token_Tbl.DELETE(2);
g_Token_Tbl.DELETE(2);
g_Token_Tbl.DELETE(3);
p_rev_component_rec.Transaction_Type = BOM_GLOBALS.G_OPR_UPDATE
)
AND
( NVL(p_Old_rev_component_rec.required_for_revenue, 0) <>
p_rev_component_rec.required_for_revenue OR
NVL(p_old_rev_component_rec.required_to_ship, 0) <>
p_rev_component_rec.required_to_ship
)
)
)
THEN
l_Result := Chk_Req_For_Rev_Or_Shp;
g_Token_Tbl.DELETE(2);
g_Token_Tbl.DELETE(2);
g_Token_Tbl.DELETE(2);
BOM_GLOBALS.G_OPR_UPDATE
) AND
NVL(p_old_rev_component_rec.check_atp, 0) <>
p_rev_component_rec.check_atp
AND p_rev_component_rec.check_atp = 1 -- Added by MK on 11/13/00
)
)
THEN
l_result := Check_ATP;
g_Token_Tbl.DELETE(2);
p_rev_component_rec.Transaction_Type = BOM_GLOBALS.G_OPR_UPDATE
)
AND
NVL(p_rev_component_rec.mutually_exclusive, 2) = 1
THEN
l_result := Check_Mutually_Exclusive;
p_rev_component_rec.Transaction_Type = BOM_GLOBALS.G_OPR_UPDATE
))
AND
NVL(p_Old_rev_component_rec.wip_supply_type, 0) <>
p_rev_component_rec.wip_supply_type
)
AND
NOT Check_Supply_Type
( p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl )
THEN
l_return_status := FND_API.G_RET_STS_ERROR;
p_rev_component_rec.Transaction_Type = BOM_GLOBALS.G_OPR_UPDATE
) AND
p_rev_component_rec.minimum_allowed_quantity is not null
AND
NOT Check_Min_Quantity THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'BOM_MIN_QUANTITY_INVALID'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => g_Token_Tbl
);
p_rev_component_rec.Transaction_Type = BOM_GLOBALS.G_OPR_UPDATE
) AND
p_rev_component_rec.maximum_allowed_quantity IS NOT NULL
AND
NOT Check_Max_Quantity THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'BOM_MAX_QUANTITY_INVALID'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => g_Token_Tbl
);
p_rev_component_rec.Transaction_Type = BOM_GLOBALS.G_OPR_UPDATE
) AND
(Is_OE_Installed = 'I'
AND ( ( Is_Item_PTO = 'Y'
) AND
(round(p_rev_component_rec.quantity_per_assembly)
<> p_rev_component_rec.quantity_per_assembly)
)
)
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'BOM_COMP_QTY_FRACTIONAL'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => g_Token_Tbl
);
p_rev_component_rec.Transaction_Type = BOM_GLOBALS.G_OPR_UPDATE
)
AND
NVL(p_Old_rev_component_rec.supply_subinventory, 'NONE') <>
NVL(p_rev_component_rec.supply_subinventory, 'NONE')
)
)
AND
NOT Check_Supply_SubInventory THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'BOM_SUBINV_INVALID'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
);
p_rev_component_rec.Transaction_Type = BOM_GLOBALS.G_OPR_UPDATE
)
AND
NVL(p_Old_rev_comp_unexp_rec.supply_locator_id, 0) <>
NVL(p_rev_comp_unexp_rec.supply_locator_id, 0)
)
)
AND
NOT Check_Locators
THEN
-- dbms_output.put_line('Locators check returned with an error-' ||
-- to_char(l_locator_control));
g_Token_Tbl.DELETE(2);
g_Token_Tbl.Delete;
END IF; -- Operation in UPDATE or CREATE
p_rev_component_rec.transaction_type = BOM_Globals.G_OPR_DELETE
THEN
IF p_rev_comp_unexp_rec.Delete_Group_Name IS NULL OR
p_rev_comp_unexp_rec.Delete_Group_Name = FND_API.G_MISS_CHAR
THEN
Error_Handler.Add_Error_Token
( p_message_name => 'BOM_DG_NAME_MISSING'
, p_mesg_token_tbl => l_mesg_token_tbl
, x_mesg_token_tbl => l_mesg_token_tbl
);
('Check if Delete Group is missing . . . ' || l_return_status) ;
SELECT STRUCTURE_TYPE_NAME
INTO
l_Structure_Type_Name
FROM BOM_STRUCTURE_TYPES_B STRUCT_TYPE,
BOM_STRUCTURES_B BOM_STRUCT
WHERE BOM_STRUCT.STRUCTURE_TYPE_ID = STRUCT_TYPE.STRUCTURE_TYPE_ID
AND BOM_STRUCT.BILL_SEQUENCE_ID = g_Rev_Comp_Unexp_Rec.BILL_SEQUENCE_ID;
CURSOR c_Geteffcontrol IS SELECT effectivity_control FROM mtl_system_items
WHERE inventory_item_id = p_rev_comp_unexp_rec.component_item_id AND
organization_id = l_org_id;
-- Check if the user is trying to create/update a record with
-- missing value when the column value is required.
--
IF p_rev_component_rec.item_sequence_number = FND_API.G_MISS_NUM
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'BOM_ITEM_NUM_MISSING'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => g_Token_Tbl
);
g_token_tbl.delete(2);
SELECT 'x' INTO l_dummy FROM mtl_system_items WHERE
inventory_item_id = p_rev_comp_unexp_rec.component_item_id
AND organization_id = p_rev_comp_unexp_rec.organization_id
AND rounding_control_type = 1;
g_token_tbl.DELETE;
* Procedure : Check_Entity_Delete
* Parameters IN : Revised Component Exposed Column Record
* Revised Component unexposed column record
* Parameters OUT: Message Token Table
* Return Status
* Procedure : Will check if a component can be deleted.
*******************************************************************/
PROCEDURE Check_Entity_Delete
( x_return_status IN OUT NOCOPY VARCHAR2
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
, p_Rev_Comp_Unexp_Rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
)
IS
l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
BOM_GLOBALS.G_OPR_DELETE
THEN
BEGIN
SELECT 'Component cancelled'
INTO l_dummy
FROM sys.dual
WHERE NOT EXISTS
(SELECT 1 from bom_inventory_components
WHERE component_sequence_id =
p_rev_comp_Unexp_rec.component_sequence_id
)
AND EXISTS (SELECT 1 from eng_revised_components
WHERE component_sequence_id =
p_rev_comp_Unexp_rec.component_sequence_id);
-- if not exception is raised then record is deleted.
-- so raise an error.
--
IF FND_MSG_PUB.Check_Msg_Level
(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name=> 'BOM_COMP_CANCELLED'
, p_Mesg_Token_Tbl=> l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl=> l_Mesg_Token_Tbl
, p_Token_Tbl => g_Token_Tbl
);
END Check_Entity_Delete;
* error if the operation is UPDATE and record DOES NOT
* EXIST.
* In case of UPDATE if record exists, then the procedure
* will return old record in the old entity parameters
* with a success status.
*********************************************************************/
PROCEDURE Check_Existence
( p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
, p_rev_comp_unexp_rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
, x_old_rev_component_rec IN OUT NOCOPY Bom_Bo_Pub.Rev_Component_Rec_Type
, x_old_rev_comp_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status IN OUT NOCOPY VARCHAR2
)
IS
l_token_tbl Error_Handler.Token_Tbl_Type;
(BOM_Globals.G_OPR_UPDATE, BOM_Globals.G_OPR_DELETE)
THEN
Error_Handler.Add_Error_Token
( x_Mesg_token_tbl => l_Mesg_Token_Tbl
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_message_name => 'BOM_REV_COMP_DOESNOT_EXIST'
, p_token_tbl => l_token_tbl
);
Bom_Globals.G_OPR_UPDATE;
SELECT revised_item_sequence_id
FROM bom_inventory_components
WHERE component_item_id = p_rev_comp_unexp_rec.component_item_id
AND bill_sequence_id = p_rev_comp_unexp_rec.bill_sequence_id
AND operation_seq_num =
p_rev_component_rec.operation_sequence_number
AND effectivity_date = p_rev_component_rec.start_effective_date;
-- In case of an update, based on the revised item information
-- Bill Sequence Id and Revised Item Sequence Id is queried from
-- the database. The revised item sequence id can however be
-- different from that in the database and should be checked
-- and given an error.
*************************************************************/
IF p_rev_component_rec.transaction_type IN
(BOM_Globals.G_OPR_UPDATE, BOM_Globals.G_OPR_DELETE,
BOM_Globals.G_OPR_CANCEL)
THEN
FOR Component IN c_GetComponent LOOP
IF NVL(Component.revised_item_sequence_id, 0) <>
NVL(p_rev_comp_unexp_rec.revised_item_sequence_id,0)
THEN
l_Token_Tbl(1).token_name :=
'REVISED_COMPONENT_NAME';
SELECT 1
FROM sys.dual
WHERE NOT EXISTS
( SELECT component_sequence_id
FROM bom_inventory_components
WHERE component_item_id = p_component_item_id
AND bill_sequence_id = p_bill_sequence_id
AND effectivity_date = p_effectivity_date
AND operation_seq_num = p_operation_seq_num
) AND
EXISTS
( SELECT component_sequence_id
FROM eng_revised_components
WHERE component_item_id = p_component_item_id
AND bill_sequence_id = p_bill_sequence_id
AND effectivity_date = p_effectivity_date
AND operation_sequence_num = p_operation_seq_num
);
SELECT component_item_id
FROM bom_inventory_components
WHERE component_item_id = p_component_item_id
AND bill_sequence_id = p_bill_sequence_id
AND effectivity_date = p_effectivity_date
AND operation_seq_num = p_operation_seq_num
AND acd_type = 3;
SELECT effectivity_control
FROM mtl_system_items
WHERE inventory_item_id = p_component_item_id
AND organization_id = p_organization_id;
SELECT bom_item_type
INTO l_rev_comp_item_type
FROM mtl_system_items
WHERE inventory_item_id = p_component_item_id
AND organization_id = p_organization_id;
l_token_tbl.DELETE(2);
l_token_tbl.DELETE;
l_token_tbl.delete;
p_Delete_Group_Name IN VARCHAR2 := NULL,
p_eco_name IN VARCHAR2 := NULL,
p_comments IN VARCHAR2 := NULL,
p_pick_components IN NUMBER := NULL,
p_revised_item_sequence_id IN NUMBER := NULL,
p_old_operation_sequence_num IN NUMBER := NULL,
p_old_component_sequence_id IN NUMBER := NULL,
p_old_effectivity_date IN DATE := NULL,
p_old_rec_item_sequence_number IN NUMBER := NULL,
p_Old_Rec_shipping_Allowed IN NUMBER := NULL,
p_Old_rec_supply_locator_id IN NUMBER := NULL,
p_Old_rec_supply_subinventory IN VARCHAR2 := NULL,
p_old_rec_check_atp IN NUMBER := NULL,
p_old_rec_acd_type IN NUMBER := NULL,
p_old_rec_to_end_item_unit_num IN VARCHAR2 := NULL,
p_original_system_reference IN VARCHAR2 := NULL,
p_rowid IN VARCHAR2 := NULL,
x_return_status IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
x_error_message IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2) IS
l_rev_component_rec Bom_Bo_Pub.Rev_Component_Rec_Type;
l_rev_comp_unexp_rec.Delete_Group_Name :=
p_Delete_Group_Name ;
Error_Handler.Translate_And_Insert_Messages
( p_mesg_token_tbl => l_mesg_token_tbl
, p_application_id => 'BOM'
);
Error_Handler.Translate_And_Insert_Messages
( p_mesg_token_tbl => l_mesg_token_tbl
, p_application_id => 'BOM'
);
Error_Handler.Translate_And_Insert_Messages
( p_mesg_token_tbl => l_mesg_token_tbl
, p_application_id => 'BOM'
);
Error_Handler.Translate_And_Insert_Messages
( p_mesg_token_tbl => l_mesg_token_tbl
, p_application_id => 'BOM'
);