[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT (*)
INTO total_inventory_components
FROM bom_inventory_components
WHERE bill_sequence_id = from_bill_seq_id
AND NVL (eco_for_production, 2) = 2
AND ((x_unit_assembly = 'N'
AND ((display_option = 1) -- ALL
OR (display_option = 2
AND (effectivity_date <= revision_date
AND (disable_date > revision_date
OR disable_date IS NULL
)
)
)
OR -- CURRENT
(display_option = 3
AND (disable_date > revision_date
OR disable_date IS NULL
)
)
) -- CURRENT + FUTURE
)
OR (x_unit_assembly = 'Y'
AND ((display_option = 1) -- ALL
OR (display_option = 2
AND disable_date IS NULL
AND (from_end_item_unit_number <= unit_number
AND (to_end_item_unit_number >= unit_number
OR to_end_item_unit_number IS NULL
)
)
)
OR -- CURRENT
(display_option = 3
AND disable_date IS NULL
AND (to_end_item_unit_number >= unit_number
OR to_end_item_unit_number IS NULL
)
)
) -- CURRENT + FUTURE
)
);
SELECT COUNT (*)
INTO total_assembly_comments
FROM fnd_attached_documents
WHERE entity_name = 'BOM_BILL_OF_MATERIALS'
AND pk1_value = to_char(from_bill_seq_id);
SELECT COUNT (*)
INTO total_reference_designators
FROM bom_reference_designators brd,
bom_inventory_components bic
WHERE bic.bill_sequence_id = from_bill_seq_id
AND NVL (bic.eco_for_production, 2) = 2
AND bic.component_sequence_id = brd.component_sequence_id
AND ((x_unit_assembly = 'N'
AND ((display_option = 1) -- ALL
OR (display_option = 2
AND (bic.effectivity_date <= revision_date
AND (bic.disable_date > revision_date
OR bic.disable_date IS NULL
)
)
)
OR -- CURRENT
(display_option = 3
AND (bic.disable_date > revision_date
OR bic.disable_date IS NULL
)
) -- CURRENT + FUTURE
)
)
OR (x_unit_assembly = 'Y'
AND ((display_option = 1) -- ALL
OR (display_option = 2
AND bic.disable_date IS NULL
AND (bic.from_end_item_unit_number <= unit_number
AND (bic.to_end_item_unit_number >=
unit_number
OR bic.to_end_item_unit_number IS NULL
)
)
)
OR -- CURRENT
(display_option = 3
AND bic.disable_date IS NULL
AND (bic.to_end_item_unit_number >= unit_number
OR bic.to_end_item_unit_number IS NULL
)
)
) -- CURRENT + FUTURE
)
);
SELECT COUNT (*)
INTO total_substitute_components
FROM bom_substitute_components bsc,
bom_inventory_components bic
WHERE bic.bill_sequence_id = from_bill_seq_id
AND NVL (bic.eco_for_production, 2) = 2
AND bic.component_sequence_id = bsc.component_sequence_id
AND ((x_unit_assembly = 'N'
AND ((display_option = 1) -- ALL
OR (display_option = 2
AND (bic.effectivity_date <= revision_date
AND (bic.disable_date > revision_date
OR bic.disable_date IS NULL
)
)
)
OR -- CURRENT
(display_option = 3
AND (bic.disable_date > revision_date
OR bic.disable_date IS NULL
)
)
) -- CURRENT + FUTURE
)
OR (x_unit_assembly = 'Y'
AND ((display_option = 1) -- ALL
OR (display_option = 2
AND bic.disable_date IS NULL
AND (bic.from_end_item_unit_number <= unit_number
AND (bic.to_end_item_unit_number >=
unit_number
OR bic.to_end_item_unit_number IS NULL
)
)
)
OR -- CURRENT
(display_option = 3
AND bic.disable_date IS NULL
AND (bic.to_end_item_unit_number >= unit_number
OR bic.to_end_item_unit_number IS NULL
)
)
) -- CURRENT + FUTURE
)
);
SELECT COUNT (*)
INTO total_component_operations
FROM bom_inventory_components bic,
bom_component_operations bco
WHERE bic.bill_sequence_id = from_bill_seq_id
AND NVL (bic.eco_for_production, 2) = 2
AND bic.component_sequence_id = bco.component_sequence_id
AND ((x_unit_assembly = 'N'
AND ((display_option = 1) -- ALL
OR (display_option = 2
AND (bic.effectivity_date <= revision_date
AND (bic.disable_date > revision_date
OR bic.disable_date IS NULL
)
)
)
OR -- CURRENT
(display_option = 3
AND (bic.disable_date > revision_date
OR bic.disable_date IS NULL
)
)
) -- CURRENT + FUTURE
)
OR (x_unit_assembly = 'Y'
AND ((display_option = 1) -- ALL
OR (display_option = 2
AND bic.disable_date IS NULL
AND (bic.from_end_item_unit_number <= unit_number
AND (bic.to_end_item_unit_number >=
unit_number
OR bic.to_end_item_unit_number IS NULL
)
)
)
OR -- CURRENT
(display_option = 3
AND bic.disable_date IS NULL
AND (bic.to_end_item_unit_number >= unit_number
OR bic.to_end_item_unit_number IS NULL
)
)
) -- CURRENT + FUTURE
)
);
SELECT REVISION
FROM (
SELECT revision
FROM mtl_item_revisions_b mir
WHERE mir.inventory_item_id = p_item_id
AND mir.organization_id = p_org_id
AND mir.effectivity_date <= p_rev_date
ORDER BY mir.effectivity_date DESC)
WHERE ROWNUM < 2;
SELECT 'T'
INTO l_return
FROM DUAL
WHERE EXISTS (
SELECT revision_id
FROM mtl_item_revisions_b
WHERE inventory_item_id = p_from_item_id
AND organization_id = p_from_org_id
AND revision_id = p_revision_id);
SELECT NVL (MAX (minor_revision_id), 0)
INTO l_minor_rev_id
FROM ego_minor_revisions
WHERE obj_name = 'EGO_ITEM'
AND pk1_value = p_item_id
AND NVL (pk2_value, '-1') = NVL (p_org_id, '-1')
AND NVL (pk3_value, '-1') = NVL (p_revision_id, '-1');
SELECT tmirb.revision_id
INTO l_revision_id
FROM mtl_item_revisions_b fmirb,
mtl_item_revisions_b tmirb
WHERE tmirb.inventory_item_id = p_item_id
AND tmirb.organization_id = p_org_id
AND tmirb.revision = fmirb.revision
AND fmirb.revision_id = p_revision_id;
SELECT CONCAT (TO_CHAR (effectivity_date, 'yyyymmddhh24miss'),
TO_CHAR (NVL (p_end_item_minor_rev_id, 9999999999999999))
)
INTO l_rev_code
FROM mtl_item_revisions_b
WHERE revision_id = p_end_item_rev_id;
SELECT 1
FROM DUAL
WHERE EXISTS (
SELECT NULL
FROM bom_structures_b bom,
bom_inventory_components bic
WHERE bom.organization_id <> to_org_id
AND bom.common_bill_sequence_id = to_sequence_id
AND bic.bill_sequence_id = to_sequence_id
AND NOT EXISTS (
SELECT NULL
FROM mtl_system_items cmsi,
mtl_system_items bmsi
WHERE cmsi.organization_id = bom.organization_id
AND cmsi.inventory_item_id =
bic.component_item_id
AND bmsi.organization_id = bom.organization_id
AND bmsi.inventory_item_id =
bom.assembly_item_id
AND cmsi.eng_item_flag =
DECODE (bom.assembly_type,
1, 'N',
2, cmsi.eng_item_flag
)
AND cmsi.bom_enabled_flag = 'Y'
AND cmsi.inventory_item_id <>
bom.assembly_item_id
AND ((bmsi.bom_item_type = 1
AND cmsi.bom_item_type <> 3
)
OR (bmsi.bom_item_type = 2
AND cmsi.bom_item_type <> 3
)
OR (bmsi.bom_item_type = 3)
OR (bmsi.bom_item_type = 4
AND (cmsi.bom_item_type = 4
OR (cmsi.bom_item_type IN (1, 2)
AND cmsi.replenish_to_order_flag =
'Y'
AND bmsi.base_item_id IS NOT NULL
AND bmsi.replenish_to_order_flag =
'Y'
)
)
)
)
AND (bmsi.bom_item_type = 3
OR bmsi.pick_components_flag = 'Y'
OR cmsi.pick_components_flag = 'N'
)
AND (bmsi.bom_item_type = 3
OR NVL (cmsi.bom_item_type, 4) <> 2
OR (cmsi.bom_item_type = 2
AND ((bmsi.pick_components_flag = 'Y'
AND cmsi.pick_components_flag =
'Y'
)
OR (bmsi.replenish_to_order_flag =
'Y'
AND cmsi.replenish_to_order_flag =
'Y'
)
)
)
)
AND NOT (bmsi.bom_item_type = 4
AND bmsi.pick_components_flag = 'Y'
AND cmsi.bom_item_type = 4
AND cmsi.replenish_to_order_flag = 'Y'
)));
SELECT revision_id,
revision
FROM mtl_item_revisions_b mir
WHERE mir.inventory_item_id = p_item_id
AND mir.organization_id = p_org_id
AND mir.effectivity_date <= p_rev_date
AND ROWNUM < 2
-- AND (P_IMPL_FLAG = 2 OR (P_IMPL_FLAG = 1 AND mir.implementation_date IS NOT NULL) )
ORDER BY mir.effectivity_date DESC;
SELECT NVL (MAX (minor_revision_id), 0) minor_revision_id
FROM ego_minor_revisions
WHERE obj_name = 'EGO_ITEM'
AND pk1_value = p_pk1_value
AND NVL (pk2_value, '-1') = NVL (p_pk2_value, '-1')
AND NVL (pk3_value, '-1') = NVL (p_pk3_value, '-1');
SELECT concatenated_segments,
primary_uom_code
FROM mtl_system_items_b_kfv
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
SELECT CONCAT (TO_CHAR (r.effectivity_date, 'yyyymmddhh24miss'),
maxr.minor_rev_id
) mrev_code
FROM mtl_item_revisions_b r,
(SELECT NVL (MAX (minor_revision_id), 0) minor_rev_id
FROM ego_minor_revisions
WHERE obj_name = 'EGO_ITEM' --p_obj_name
AND pk1_value = p_pk1_value
AND NVL (pk2_value, '-1') = NVL (p_pk2_value, '-1')
AND NVL (pk3_value, '-1') =
NVL (TO_CHAR (p_item_rev_id), '-1')) maxr
WHERE revision_id = p_item_rev_id;
SELECT bic.component_sequence_id,
bic.component_item_id,
bic.operation_seq_num
FROM bom_inventory_components bic,
mtl_system_items msi
WHERE bic.bill_sequence_id = p_from_sequence_id
AND bic.component_item_id = msi.inventory_item_id
AND bic.component_item_id <> p_to_item_id
AND NVL (bic.eco_for_production, 2) = 2
AND msi.organization_id = p_to_org_id
-- AND MSI.BOM_ENABLED_FLAG = 'Y'
AND ((p_direction = eng_to_bom
AND msi.eng_item_flag = 'N')
OR (p_direction <> eng_to_bom)
)
AND ((p_unit_assembly = 'N'
AND ((p_display_option = 1) -- ALL
OR (p_display_option = 2
AND (effectivity_date <= p_rev_date
AND
-- Added condition of sysdate for Bug 2161841
( (disable_date > p_rev_date
AND disable_date > SYSDATE
)
OR disable_date IS NULL
)
)
)
OR -- CURRENT
(p_display_option = 3
AND
-- Added condition of sysdate for Bug 2161841
( (disable_date > p_rev_date
AND disable_date > SYSDATE
)
OR disable_date IS NULL
)
)
) -- CURRENT + FUTURE
)
OR (p_unit_assembly = 'Y'
AND ((p_display_option = 1) -- ALL
OR (p_display_option = 2
AND disable_date IS NULL
AND (from_end_item_unit_number <= p_unit_number
AND (to_end_item_unit_number >=
p_unit_number
OR to_end_item_unit_number IS NULL
)
)
)
OR -- CURRENT
(p_display_option = 3
AND disable_date IS NULL
AND (to_end_item_unit_number >= p_unit_number
OR to_end_item_unit_number IS NULL
)
)
) -- CURRENT + FUTURE
)
)
AND ((p_base_item_flag = -1
AND p_itm_type = 4
AND msi.bom_item_type = 4
)
OR p_base_item_flag <> -1
OR p_itm_type <> 4
)
AND implementation_date IS NOT NULL
AND component_sequence_id = p_comp_seq_num;
SELECT component_sequence_id
FROM bom_inventory_components bic
WHERE bill_sequence_id = p_to_bill_sequence_id
AND component_item_id = p_copied_comp_item_id
AND operation_seq_num = p_copied_op_seq_num;
SELECT bcb1.component_sequence_id from_component_seq_id,
bcb2.component_sequence_id to_sequence_id
FROM bom_components_b bcb1,
bom_components_b bcb2
WHERE bcb1.bill_sequence_id = p_from_seq_id
AND bcb1.component_sequence_id = bcb2.created_by
AND bcb2.bill_sequence_id = p_to_seq_id;
SELECT eec.change_id,
bcb.component_sequence_id
FROM eng_engineering_changes eec,
bom_components_b bcb
WHERE eec.change_notice = p_change_notice
AND eec.organization_id = p_local_org_id
AND bcb.bill_sequence_id = p_bill_seq_id;
SELECT bev.comp_fixed_rev_code
FROM bom_copy_explosions_v bev
WHERE bev.sort_order = p_parent_sort_order;
SELECT effectivity_date
FROM mtl_item_revisions_b
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND revision = p_revision;
SELECT DATA_LEVEL_ID
FROM EGO_DATA_LEVEL_B
WHERE DATA_LEVEL_NAME = p_data_level_name;
SELECT common_bill_sequence_id,
NVL (common_organization_id, organization_id)
INTO x_from_sequence_id,
x_from_org_id
FROM bom_structures_b
WHERE bill_sequence_id = x_from_sequence_id;
SELECT structure_type_id, effectivity_control
INTO l_str_type, l_from_eff_ctrl
FROM bom_structures_b
WHERE bill_sequence_id = from_sequence_id;
SELECT atp_components_flag,
replenish_to_order_flag,
DECODE (base_item_id, NULL, -1, 0),
bom_item_type,
item_catalog_group_id
INTO atp_comp_flag,
rto_flag,
base_item_flag,
itm_type,
itm_cat_grp_id
FROM mtl_system_items
WHERE organization_id = to_org_id
AND inventory_item_id = to_item_id;
SELECT application_id INTO l_app_id
FROM fnd_application WHERE application_short_name = 'BOM';
select default_context_field_name, context_column_name into l_context, l_context_column
from FND_DESCRIPTIVE_FLEXS where application_id = l_app_id and
descriptive_flexfield_name = 'BOM_INVENTORY_COMPONENTS';
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT from_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (from_item_id,
from_org_id,
rev_date
),
l_error_msg_tbl(l_msg_count).message_text,
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM dual;
INSERT INTO bom_components_b
(shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
acd_type,
component_sequence_id,
old_component_sequence_id,
bill_sequence_id,
request_id,
program_application_id,
program_id,
program_update_date,
wip_supply_type,
pick_components,
supply_subinventory,
supply_locator_id,
operation_lead_time_percent,
revised_item_sequence_id,
cost_factor,
operation_seq_num,
component_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
item_num,
component_quantity,
component_yield_factor,
component_remarks,
effectivity_date,
change_notice,
implementation_date,
disable_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
planning_factor,
quantity_related,
so_basis,
optional,
mutually_exclusive_options,
include_in_cost_rollup,
check_atp,
bom_item_type,
from_end_item_unit_number,
to_end_item_unit_number,
optional_on_model,
--BUGFIX 2740820
parent_bill_seq_id, --BUGFIX 2740820
model_comp_seq_id, --BUGFIX 2740820
plan_level,
--BUGFIX 2740820
enforce_int_requirements, --BUGFIX 2991472
from_object_revision_id,
from_minor_revision_id,
pk1_value,
pk2_value,
auto_request_material,
-- Bug 3662214 : Added following 4 fields
suggested_vendor_name,
vendor_id,
unit_price,
from_end_item_rev_id,
to_end_item_rev_id,
from_end_item_minor_rev_id,
to_end_item_minor_rev_id,
component_item_revision_id,
component_minor_revision_id,
basis_type,
to_object_revision_id,
to_minor_revision_id
)
SELECT bic.shipping_allowed,
bic.required_to_ship,
bic.required_for_revenue,
bic.include_on_ship_docs,
bic.include_on_bill_docs,
--bic.low_quantity,
--bic.high_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.low_quantity,
DECODE(BIC.low_quantity,null,null, --Added this inner Deocde for Bug 6847530
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.low_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure))) Comp_low_qty,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.high_quantity,
DECODE(BIC.high_quantity,null,null, --Added this inner Deocde for Bug 6847530
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.high_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure))) Comp_high_qty,
x_acd_type,
bom_inventory_components_s.NEXTVAL,
DECODE (x_acd_type,
NULL, NULL,
bom_inventory_components_s.CURRVAL
),
to_sequence_id,
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate,
bic.wip_supply_type,
DECODE (rto_flag, 'Y', 2, bic.pick_components),
DECODE (x_from_org_id,
to_org_id, bic.supply_subinventory,
DECODE( l_default_wip_params, 1, msi.wip_supply_subinventory, NULL )
),
DECODE (x_from_org_id,
to_org_id, bic.supply_locator_id,
DECODE( l_default_wip_params, 1, msi.wip_supply_locator_id, NULL )
),
bic.operation_lead_time_percent,
x_rev_item_seq_id,
bic.cost_factor,
bic.operation_seq_num,
bic.component_item_id,
SYSDATE,
user_id,
SYSDATE,
bic.component_sequence_id,
/*NULL comment for bug8431772,change NULL to user_id*/user_id,
bic.item_num,
--bic.component_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.component_quantity,
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.component_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure)) Comp_qty,
bic.component_yield_factor,
bic.component_remarks,
-- R12 TTM ENH
CASE
-- The WHEN sequence is important
-- For When display option is set to 2 then what ever comps are target date we need to
-- take that
-- ECO conditions should be in sync with BomCopyStructureAM
-- for creating revised item by grouping based on effectivity
WHEN display_option = 2 AND bill_or_eco = 2
THEN x_effectivity_date
WHEN display_option = 2
THEN trgt_comps_eff_date
WHEN bill_or_eco = 1 -- Inline and explosion date is past
AND ( bic.effectivity_date < trgt_comps_eff_date AND rev_date < trgt_comps_eff_date )
-- Explosion in the Past and Effectivity Date is also in the past, then the components
-- which are past effective will be effective from trgt_comps_eff_date
THEN trgt_comps_eff_date
WHEN bill_or_eco = 1 -- Inline and explosion date is future
AND ( bic.effectivity_date = rev_date AND rev_date > trgt_comps_eff_date )
-- Explosion in the future and Effectivity Date is also in the future, then the components
-- which are effective at the explosion time alone will be effective from trgt_comps_eff_date
THEN trgt_comps_eff_date
-- Past effective components should be target data effective
WHEN bill_or_eco = 1
AND bic.effectivity_date < trgt_comps_eff_date
THEN trgt_comps_eff_date
ELSE
bic.effectivity_date
END AS effectivity_date,
x_e_change_notice,
-- Implementation date will be NULL for ECO flow and SYSDATE for inline copy
DECODE (bill_or_eco, 2, TO_DATE (NULL), SYSDATE),
CASE
-- For current never disable the components
WHEN display_option = 2
THEN TO_DATE (NULL)
-- Past disabled components will be copied with disable date as null
WHEN bill_or_eco = 2 AND ( bic.disable_date < x_effectivity_date )
THEN TO_DATE (NULL)
-- Past disabled components will be copied with disable date as null
WHEN bill_or_eco = 1 AND ( bic.disable_date < trgt_comps_eff_date )
THEN TO_DATE (NULL)
ELSE
-- Future disabled components should be disabled as per the disable date of component
bic.disable_date
END AS disable_date,
decode(l_prof_val, null, bic.attribute_category, l_prof_val), --introduced for bug 10078346 --bic.attribute_category,
bic.attribute1,
bic.attribute2,
bic.attribute3,
bic.attribute4,
bic.attribute5,
bic.attribute6,
bic.attribute7,
bic.attribute8,
bic.attribute9,
bic.attribute10,
bic.attribute11,
bic.attribute12,
bic.attribute13,
bic.attribute14,
bic.attribute15,
bic.planning_factor,
bic.quantity_related,
bic.so_basis,
bic.optional,
bic.mutually_exclusive_options,
bic.include_in_cost_rollup,
bic.check_atp,
msi.bom_item_type,
to_char(NULL) AS from_end_item_unit_number, -- Date Eff Bill will not have from_end_item_unit_numbers
to_char(NULL) AS to_end_item_unit_number, -- Date Eff Bill will not have to_end_item_unit_numbers
bic.optional_on_model,
--BUGFIX 2740820
bic.parent_bill_seq_id, --BUGFIX 2740820
bic.model_comp_seq_id,
--BUGFIX 2740820
bic.plan_level, --BUGFIX 2740820
bic.enforce_int_requirements,
-- Either Fixed or Floating rev, the components will be from when its created, current item rev
l_current_item_rev_id,
-- Minor rev is not supported. Populated the first minor rev
0,
bic.component_item_id,
to_org_id,
bic.auto_request_material,
-- Bug 3662214 : Added following 4 fields
bic.suggested_vendor_name,
bic.vendor_id,
bic.unit_price,
to_number(NULL) AS from_end_item_rev_id, -- From End Item Rev Ids won't be set for Date Eff Bill
to_number(NULL) AS to_end_item_rev_id, -- To End Item Rev Ids won't be set for Date Eff Bill
-- For Minor rev Ids
0 AS from_end_item_minor_rev_id,
0 AS to_end_item_minor_rev_id,
(
SELECT tmirb.revision_id
FROM mtl_item_revisions_b fmirb,
mtl_item_revisions_b tmirb
WHERE tmirb.inventory_item_id = bic.component_item_id
AND tmirb.organization_id = to_org_id
AND tmirb.revision = fmirb.revision
AND fmirb.revision_id = bic.component_item_revision_id
) AS component_item_revision_id,
CASE
WHEN bic.component_item_revision_id IS NULL
THEN to_number(NULL)
ELSE
-- Minor revision is not supported
0
END AS component_minor_revision_id,
bic.basis_type,
CASE
WHEN l_fixed_rev IS NOT NULL
-- For fixed rev copy the components as fixed rev
THEN l_to_item_rev_id
ELSE
to_number(NULL)
END AS to_object_revision_id,
CASE
WHEN l_fixed_rev IS NOT NULL
THEN 0
ELSE
to_number(NULL)
END AS to_minor_revision_id
FROM bom_components_b bic,
mtl_system_items msi,
MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
bom_copy_explosions_v bev
WHERE bic.bill_sequence_id = x_from_sequence_id
AND bic.component_item_id = msi.inventory_item_id
AND bic.component_item_id <> to_item_id
AND NVL (bic.eco_for_production, 2) = 2
AND msi.organization_id = to_org_id
AND msi.bom_enabled_flag = 'Y' -- Added for the bug 13963236
AND MSI.inventory_item_id = AA.inventory_item_id -- Added corresponding to Bug 6510185
AND AA.organization_id = from_org_id -- Added corresponding to Bug 6510185
AND ((direction = eng_to_bom
AND msi.eng_item_flag = 'N')
OR (direction <> eng_to_bom)
)
AND ((base_item_flag = -1
AND itm_type = 4
AND msi.bom_item_type = 4
)
OR base_item_flag <> -1
OR itm_type <> 4
)
AND ((bic.implementation_date IS NOT NULL)
OR (bic.implementation_date IS NULL
AND bic.change_notice = context_eco
AND ( bic.acd_type = 1 OR bic.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb
WHERE bcb.old_component_sequence_id = bic.component_sequence_id
AND bcb.change_notice = context_eco
AND bcb.acd_type = 3
AND bcb.effectivity_date <= trgt_comps_eff_date
AND bcb.implementation_date IS NULL
AND bcb.bill_sequence_id = bic.bill_sequence_id
)
AND 'T' = bev.access_flag
AND 'T' =
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bic.component_item_id),
TO_CHAR (to_org_id),
bom_exploder_pub.get_ego_user
)
AND bic.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bic.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
)
AND EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
ego_criteria_v ecv,
mtl_system_items_b msibs -- to assembly item
WHERE ecv.customization_application_id = 702
AND ecv.region_application_id = 702
AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
AND ecv.customization_code = ectv.customization_code
AND flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
AND ecv.value_varchar2 = msi.item_type -- Component
UNION ALL
SELECT 1
FROM DUAL
WHERE NOT EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
mtl_system_items_b msibs -- to assembly item
WHERE flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
)
);
INSERT INTO bom_components_b
(shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
acd_type,
component_sequence_id,
old_component_sequence_id,
bill_sequence_id,
request_id,
program_application_id,
program_id,
program_update_date,
wip_supply_type,
pick_components,
supply_subinventory,
supply_locator_id,
operation_lead_time_percent,
revised_item_sequence_id,
cost_factor,
operation_seq_num,
component_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
item_num,
component_quantity,
component_yield_factor,
component_remarks,
effectivity_date,
change_notice,
implementation_date,
disable_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
planning_factor,
quantity_related,
so_basis,
optional,
mutually_exclusive_options,
include_in_cost_rollup,
check_atp,
bom_item_type,
from_end_item_unit_number,
to_end_item_unit_number,
optional_on_model,
--BUGFIX 2740820
parent_bill_seq_id, --BUGFIX 2740820
model_comp_seq_id, --BUGFIX 2740820
plan_level,
--BUGFIX 2740820
enforce_int_requirements, --BUGFIX 2991472
from_object_revision_id,
from_minor_revision_id,
pk1_value,
pk2_value,
auto_request_material,
-- Bug 3662214 : Added following 4 fields
suggested_vendor_name,
vendor_id,
unit_price,
from_end_item_rev_id,
to_end_item_rev_id,
from_end_item_minor_rev_id,
to_end_item_minor_rev_id,
component_item_revision_id,
component_minor_revision_id,
basis_type,
to_object_revision_id,
to_minor_revision_id
)
SELECT bic.shipping_allowed,
bic.required_to_ship,
bic.required_for_revenue,
bic.include_on_ship_docs,
bic.include_on_bill_docs,
--bic.low_quantity, -- Commented for bug-6510185
--bic.high_quantity, -- Commented for bug-6510185
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.low_quantity,
DECODE(BIC.low_quantity,null,null, --Added this inner Deocde for Bug 6847530
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.low_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure))) Comp_low_qty,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.high_quantity,
DECODE(BIC.high_quantity,null,null, --Added this inner Deocde for Bug 6847530
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.high_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure))) Comp_high_qty,
x_acd_type,
bom_inventory_components_s.NEXTVAL,
DECODE (x_acd_type,
NULL, NULL,
bom_inventory_components_s.CURRVAL
),
to_sequence_id,
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate,
bic.wip_supply_type,
DECODE (rto_flag, 'Y', 2, bic.pick_components),
DECODE (x_from_org_id,
to_org_id, bic.supply_subinventory,
DECODE( l_default_wip_params, 1, msi.wip_supply_subinventory, NULL )
),
DECODE (x_from_org_id,
to_org_id, bic.supply_locator_id,
DECODE( l_default_wip_params, 1, msi.wip_supply_locator_id, NULL )
),
bic.operation_lead_time_percent,
x_rev_item_seq_id,
bic.cost_factor,
bic.operation_seq_num,
bic.component_item_id,
SYSDATE,
user_id,
SYSDATE,
bic.component_sequence_id,
/*NULL comment for bug8431772,change NULL to user_id*/user_id,
bic.item_num,
--bic.component_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.component_quantity,
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.component_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure)) Comp_qty,
bic.component_yield_factor,
bic.component_remarks,
-- R12 TTM ENH
-- For Unit/Serial it eff date will be sysdate
sysdate AS effectivity_date,
x_e_change_notice,
-- Implementation date will be NULL for ECO flow and SYSDATE for inline copy
DECODE (bill_or_eco, 2, TO_DATE (NULL), SYSDATE),
-- For Unit/Serial Eff disable date will be null
to_date(NULL) AS disable_date,
-- Bug 4208139 Currently only CURRENT components are copied.(11.5.10-E)
decode(l_prof_val, null, bic.attribute_category, l_prof_val), --introduced for bug 10078346 bic.attribute_category,
bic.attribute1,
bic.attribute2,
bic.attribute3,
bic.attribute4,
bic.attribute5,
bic.attribute6,
bic.attribute7,
bic.attribute8,
bic.attribute9,
bic.attribute10,
bic.attribute11,
bic.attribute12,
bic.attribute13,
bic.attribute14,
bic.attribute15,
bic.planning_factor,
bic.quantity_related,
bic.so_basis,
bic.optional,
bic.mutually_exclusive_options,
bic.include_in_cost_rollup,
--DECODE(atp_comp_flag, 'Y', CHECK_ATP, 2), fixed bug 2249375
bic.check_atp,
msi.bom_item_type,
CASE
WHEN bic.from_end_item_unit_number IS NULL
THEN to_char(NULL)
-- ECO conditions should be in sync with BomCopyStructureAM
-- for creating revised item by grouping based on effectivity
WHEN display_option = 2 AND bill_or_eco = 2
THEN x_unit_number
WHEN display_option = 2
THEN trgt_comps_unit_number
WHEN bill_or_eco = 1 -- Inline and explosion unit number is smaller
AND ( bic.from_end_item_unit_number < trgt_comps_unit_number AND unit_number < trgt_comps_unit_number )
-- Explosion unit number is smaller and from_end_item_unit_number is also smaller, then the components
-- which are with smaller unit number effective will be effective from trgt_comps_unit_number
THEN trgt_comps_unit_number
WHEN bill_or_eco = 1 -- Inline explosion unit number is greater
AND ( bic.from_end_item_unit_number = unit_number AND unit_number > trgt_comps_unit_number )
-- Explosion unit number it greater and from_end_item_unit_number is also greater, then the components
-- which are effective on explosion unit number will be effective from trgt_comps_unit_number
THEN trgt_comps_unit_number
-- Past effective should be effective from the target unit number
WHEN bill_or_eco = 2
AND bic.from_end_item_unit_number < x_unit_number
THEN trgt_comps_unit_number
ELSE
bic.from_end_item_unit_number
END AS from_end_item_unit_number,
CASE
-- For current never disable components
WHEN display_option = 2
THEN to_char(NULL)
WHEN bill_or_eco = 2 AND ( bic.to_end_item_unit_number < x_unit_number )
-- Disabled components should be copied with to end item unit number as null
THEN to_char(NULL)
WHEN bill_or_eco = 1 AND ( bic.to_end_item_unit_number < trgt_comps_unit_number )
-- Disabled components should be copied with to end item unit number as null
THEN to_char(NULL)
ELSE
-- Future disabled components should be disabled as per the to_end_item_unit_number of component
bic.to_end_item_unit_number
END AS to_end_item_unit_number,
bic.optional_on_model,
--BUGFIX 2740820
bic.parent_bill_seq_id, --BUGFIX 2740820
bic.model_comp_seq_id,
--BUGFIX 2740820
bic.plan_level, --BUGFIX 2740820
bic.enforce_int_requirements,
-- Either Fixed or Floating rev, the components will be from when its created, current item rev
l_current_item_rev_id,
-- Minor rev is not supported. Populated the first minor rev
0,
bic.component_item_id,
to_org_id,
bic.auto_request_material,
-- Bug 3662214 : Added following 4 fields
bic.suggested_vendor_name,
bic.vendor_id,
bic.unit_price,
to_number(NULL) AS from_end_item_rev_id,
to_number(NULL) AS to_end_item_rev_id,
-- For Minor rev Ids
0 AS from_end_item_minor_rev_id,
0 AS to_end_item_minor_rev_id,
(
SELECT tmirb.revision_id
FROM mtl_item_revisions_b fmirb,
mtl_item_revisions_b tmirb
WHERE tmirb.inventory_item_id = bic.component_item_id
AND tmirb.organization_id = to_org_id
AND tmirb.revision = fmirb.revision
AND fmirb.revision_id = bic.component_item_revision_id
) AS component_item_revision_id,
CASE
WHEN bic.component_item_revision_id IS NULL
THEN to_number(NULL)
ELSE
-- Minor revision is not supported
0
END AS component_minor_revision_id,
bic.basis_type,
CASE
WHEN l_fixed_rev IS NOT NULL
-- For fixed rev copy the components as fixed rev
THEN l_to_item_rev_id
ELSE
to_number(NULL)
END AS to_object_revision_id,
CASE
WHEN l_fixed_rev IS NOT NULL
THEN 0
ELSE
to_number(NULL)
END AS to_minor_revision_id
FROM bom_components_b bic,
mtl_system_items msi,
MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
bom_copy_explosions_v bev
WHERE bic.bill_sequence_id = x_from_sequence_id
AND bic.component_item_id = msi.inventory_item_id
AND bic.component_item_id <> to_item_id
AND NVL (bic.eco_for_production, 2) = 2
AND msi.organization_id = to_org_id
AND MSI.BOM_ENABLED_FLAG = 'Y' -- Bug 3595979 -- Uncommented for the bug 13963236
AND MSI.inventory_item_id = AA.inventory_item_id -- Added corresponding to Bug 6510185
AND AA.organization_id = from_org_id -- Added corresponding to Bug 6510185
AND ((direction = eng_to_bom
AND msi.eng_item_flag = 'N')
OR (direction <> eng_to_bom)
)
AND ((base_item_flag = -1
AND itm_type = 4
AND msi.bom_item_type = 4
)
OR base_item_flag <> -1
OR itm_type <> 4
)
AND ((bic.implementation_date IS NOT NULL)
OR (bic.implementation_date IS NULL
AND bic.change_notice = context_eco
AND ( bic.acd_type = 1 OR bic.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb
WHERE bcb.old_component_sequence_id = bic.component_sequence_id
AND bcb.change_notice = context_eco
AND bcb.acd_type = 3
AND bcb.effectivity_date <= trgt_comps_eff_date
AND bcb.implementation_date IS NULL
AND bcb.bill_sequence_id = bic.bill_sequence_id
)
AND 'T' = bev.access_flag
AND 'T' =
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bic.component_item_id),
TO_CHAR (to_org_id),
bom_exploder_pub.get_ego_user
)
AND bic.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bic.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
)
AND EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
ego_criteria_v ecv,
mtl_system_items_b msibs -- to assembly item
WHERE ecv.customization_application_id = 702
AND ecv.region_application_id = 702
AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
AND ecv.customization_code = ectv.customization_code
AND flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
AND ecv.value_varchar2 = msi.item_type -- Component
UNION ALL
SELECT 1
FROM DUAL
WHERE NOT EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
mtl_system_items_b msibs -- to assembly item
WHERE flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
)
);
INSERT INTO bom_components_b
(shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
acd_type,
component_sequence_id,
old_component_sequence_id,
bill_sequence_id,
request_id,
program_application_id,
program_id,
program_update_date,
wip_supply_type,
pick_components,
supply_subinventory,
supply_locator_id,
operation_lead_time_percent,
revised_item_sequence_id,
cost_factor,
operation_seq_num,
component_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
item_num,
component_quantity,
component_yield_factor,
component_remarks,
effectivity_date,
change_notice,
implementation_date,
disable_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
planning_factor,
quantity_related,
so_basis,
optional,
mutually_exclusive_options,
include_in_cost_rollup,
check_atp,
bom_item_type,
from_end_item_unit_number,
to_end_item_unit_number,
optional_on_model,
--BUGFIX 2740820
parent_bill_seq_id, --BUGFIX 2740820
model_comp_seq_id, --BUGFIX 2740820
plan_level,
--BUGFIX 2740820
enforce_int_requirements, --BUGFIX 2991472
from_object_revision_id,
from_minor_revision_id,
pk1_value,
pk2_value,
auto_request_material,
-- Bug 3662214 : Added following 4 fields
suggested_vendor_name,
vendor_id,
unit_price,
from_end_item_rev_id,
to_end_item_rev_id,
from_end_item_minor_rev_id,
to_end_item_minor_rev_id,
component_item_revision_id,
component_minor_revision_id,
basis_type,
to_object_revision_id,
to_minor_revision_id
)
SELECT bic.shipping_allowed,
bic.required_to_ship,
bic.required_for_revenue,
bic.include_on_ship_docs,
bic.include_on_bill_docs,
--bic.low_quantity, -- Commented for bug-6510185
--bic.high_quantity, -- Commented for bug-6510185
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.low_quantity,
DECODE(BIC.low_quantity,null,null, --Added this inner Deocde for Bug 6847530
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.low_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure))) Comp_low_qty,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.high_quantity,
DECODE(BIC.high_quantity,null,null, --Added this inner Deocde for Bug 6847530
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.high_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure))) Comp_high_qty,
x_acd_type,
bom_inventory_components_s.NEXTVAL,
DECODE (x_acd_type,
NULL, NULL,
bom_inventory_components_s.CURRVAL
),
to_sequence_id,
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate,
bic.wip_supply_type,
DECODE (rto_flag, 'Y', 2, bic.pick_components),
DECODE (x_from_org_id,
to_org_id, bic.supply_subinventory,
DECODE( l_default_wip_params, 1, msi.wip_supply_subinventory, NULL )
),
DECODE (x_from_org_id,
to_org_id, bic.supply_locator_id,
DECODE( l_default_wip_params, 1, msi.wip_supply_locator_id, NULL )
),
bic.operation_lead_time_percent,
x_rev_item_seq_id,
bic.cost_factor,
bic.operation_seq_num,
bic.component_item_id,
SYSDATE,
user_id,
SYSDATE,
bic.component_sequence_id,
/*NULL comment for bug8431772,change NULL to user_id*/user_id,
bic.item_num,
--bic.component_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.component_quantity,
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.component_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure)) Comp_qty,
bic.component_yield_factor,
bic.component_remarks,
-- R12 TTM ENH
-- For Rev Eff Structure the eff date will be sysdate
sysdate AS effectivity_date,
x_e_change_notice,
-- Implementation date will be NULL for ECO flow and SYSDATE for inline copy
DECODE (bill_or_eco, 2, TO_DATE (NULL), SYSDATE),
-- For Rev Eff structure the disable date will be null
to_date(NULL) AS disable_date,
-- Bug 4208139 Currently only CURRENT components are copied.(11.5.10-E)
--DECODE(bill_or_eco,2,to_date(NULL),GREATEST(IMPLEMENTATION_DATE,SYSDATE)),
--DECODE(bill_or_eco,2,to_date(NULL), DECODE(GREATEST(DISABLE_DATE,SYSDATE),SYSDATE, NULL, DISABLE_DATE)),
decode(l_prof_val, null, bic.attribute_category, l_prof_val), --introduced for bug 10078346 bic.attribute_category,
bic.attribute1,
bic.attribute2,
bic.attribute3,
bic.attribute4,
bic.attribute5,
bic.attribute6,
bic.attribute7,
bic.attribute8,
bic.attribute9,
bic.attribute10,
bic.attribute11,
bic.attribute12,
bic.attribute13,
bic.attribute14,
bic.attribute15,
bic.planning_factor,
bic.quantity_related,
bic.so_basis,
bic.optional,
bic.mutually_exclusive_options,
bic.include_in_cost_rollup,
bic.check_atp,
msi.bom_item_type,
to_char(NULL) AS from_end_item_unit_number,
to_char(NULL) AS to_end_item_unit_number,
bic.optional_on_model,
--BUGFIX 2740820
bic.parent_bill_seq_id, --BUGFIX 2740820
bic.model_comp_seq_id,
--BUGFIX 2740820
bic.plan_level, --BUGFIX 2740820
bic.enforce_int_requirements,
-- Either Fixed or Floating rev, the components will be from when its created, current item rev
l_current_item_rev_id,
-- Minor rev is not supported. Populated the first minor rev
0,
bic.component_item_id,
to_org_id,
bic.auto_request_material,
-- Bug 3662214 : Added following 4 fields
bic.suggested_vendor_name,
bic.vendor_id,
bic.unit_price,
CASE
WHEN bic.from_end_item_rev_id IS NULL
THEN NULL
-- ECO conditions should be in sync with BomCopyStructureAM
-- for creating revised item by grouping based on effectivity
WHEN display_option = 2 AND bill_or_eco = 2
THEN eco_end_item_rev_id
WHEN display_option = 2
THEN trgt_comps_end_item_rev_id
WHEN bill_or_eco = 1 -- Inline and explosion rev is past or smaller
-- Explosion rev is smaller and from_end_item_rev is also smaller, then the components
-- which are with smaller revision effective will be effective from eco_end_item_rev_id
AND bic.from_end_item_rev_id IS NOT NULL
AND trgt_comps_end_item_rev_id IS NOT NULL
AND p_end_item_rev_id IS NOT NULL
AND ( (
SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE REVISION_ID = bic.from_end_item_rev_id
) < (
SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE REVISION_ID = trgt_comps_end_item_rev_id
)
AND
(
SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE REVISION_ID = p_end_item_rev_id
) < (
SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE REVISION_ID = trgt_comps_end_item_rev_id
)
)
THEN trgt_comps_end_item_rev_id
WHEN bill_or_eco = 1 -- Inline and explosion rev is future and greater
AND bic.from_end_item_rev_id IS NOT NULL
-- Explosion rev is greater and from_end_item_rev is also greater, then the components
-- which are effective on exploded revision will be effective from trgt_comps_end_item_rev_id
AND trgt_comps_end_item_rev_id IS NOT NULL
AND p_end_item_rev_id IS NOT NULL
AND ( (
SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE REVISION_ID = bic.from_end_item_rev_id
) = (
SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE REVISION_ID = trgt_comps_end_item_rev_id
)
AND
(
SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE REVISION_ID = p_end_item_rev_id
) > (
SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE REVISION_ID = trgt_comps_end_item_rev_id
)
)
THEN trgt_comps_end_item_rev_id
-- Past Revision Effective should be effective from target revision
WHEN bill_or_eco = 1
AND eco_end_item_rev_id IS NOT NULL
AND (
SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE REVISION_ID = bic.from_end_item_rev_id
) < (
SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE REVISION_ID = eco_end_item_rev_id
)
THEN trgt_comps_end_item_rev_id
ELSE
NVL((
SELECT tmirb.revision_id
FROM mtl_item_revisions_b fmirb,
mtl_item_revisions_b tmirb
WHERE tmirb.inventory_item_id = to_item_id
AND tmirb.organization_id = to_org_id
AND tmirb.revision = fmirb.revision
AND fmirb.revision_id = bic.from_end_item_rev_id
),trgt_comps_end_item_rev_id)
END AS from_end_item_rev_id,
CASE
WHEN ( bic.to_end_item_rev_id IS NULL OR display_option = 2)
THEN NULL
WHEN bill_or_eco = 1
AND
(
SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE REVISION_ID = bic.to_end_item_rev_id
) <
(
SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE REVISION_ID = trgt_comps_end_item_rev_id
)
THEN NULL
ELSE
(
SELECT tmirb.revision_id
FROM mtl_item_revisions_b fmirb,
mtl_item_revisions_b tmirb
WHERE tmirb.inventory_item_id = to_item_id
AND tmirb.organization_id = to_org_id
AND tmirb.revision = fmirb.revision
AND fmirb.revision_id = bic.to_end_item_rev_id
)
-- When No Item Rev Exists for the to item then populate to item rev as also null
END AS to_end_item_rev_id,
-- For Minor rev Ids
0 AS from_end_item_minor_rev_id,
0 AS to_end_item_minor_rev_id,
(
SELECT tmirb.revision_id
FROM mtl_item_revisions_b fmirb,
mtl_item_revisions_b tmirb
WHERE tmirb.inventory_item_id = bic.component_item_id
AND tmirb.organization_id = to_org_id
AND tmirb.revision = fmirb.revision
AND fmirb.revision_id = bic.component_item_revision_id
) AS component_item_revision_id,
CASE
WHEN bic.component_item_revision_id IS NULL
THEN to_number(NULL)
ELSE
-- Minor revision is not supported
0
END AS component_minor_revision_id,
bic.basis_type,
CASE
WHEN l_fixed_rev IS NOT NULL
-- For fixed rev copy the components as fixed rev
THEN l_to_item_rev_id
ELSE
to_number(NULL)
END AS to_object_revision_id,
CASE
WHEN l_fixed_rev IS NOT NULL
THEN 0
ELSE
to_number(NULL)
END AS to_minor_revision_id
FROM bom_components_b bic,
mtl_system_items msi,
MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
bom_copy_explosions_v bev
WHERE bic.bill_sequence_id = x_from_sequence_id
AND bic.component_item_id = msi.inventory_item_id
AND bic.component_item_id <> to_item_id
AND NVL (bic.eco_for_production, 2) = 2
AND msi.organization_id = to_org_id
AND MSI.inventory_item_id = AA.inventory_item_id -- Added corresponding to Bug 6510185
AND AA.organization_id = from_org_id -- Added corresponding to Bug 6510185
AND MSI.BOM_ENABLED_FLAG = 'Y' --Bug 3595979 -- Uncommented for the bug 13963236
AND ((direction = eng_to_bom
AND msi.eng_item_flag = 'N')
OR (direction <> eng_to_bom)
)
AND ((base_item_flag = -1
AND itm_type = 4
AND msi.bom_item_type = 4
)
OR base_item_flag <> -1
OR itm_type <> 4
)
AND ((bic.implementation_date IS NOT NULL)
OR (bic.implementation_date IS NULL
AND bic.change_notice = context_eco
AND ( bic.acd_type = 1 OR bic.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb
WHERE bcb.old_component_sequence_id = bic.component_sequence_id
AND bcb.change_notice = context_eco
AND bcb.acd_type = 3
AND bcb.effectivity_date <= trgt_comps_eff_date
AND bcb.implementation_date IS NULL
AND bcb.bill_sequence_id = bic.bill_sequence_id
)
AND 'T' = bev.access_flag
AND 'T' =
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bic.component_item_id),
TO_CHAR (to_org_id),
bom_exploder_pub.get_ego_user
)
AND bic.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bic.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
)
AND EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
ego_criteria_v ecv,
mtl_system_items_b msibs -- to assembly item
WHERE ecv.customization_application_id = 702
AND ecv.region_application_id = 702
AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
AND ecv.customization_code = ectv.customization_code
AND flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
AND ecv.value_varchar2 = msi.item_type -- Component
UNION ALL
SELECT 1
FROM DUAL
WHERE NOT EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
mtl_system_items_b msibs -- to assembly item
WHERE flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
)
);
INSERT INTO bom_components_b
(shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
acd_type,
component_sequence_id,
old_component_sequence_id,
bill_sequence_id,
request_id,
program_application_id,
program_id,
program_update_date,
wip_supply_type,
pick_components,
supply_subinventory,
supply_locator_id,
operation_lead_time_percent,
revised_item_sequence_id,
cost_factor,
operation_seq_num,
component_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
item_num,
component_quantity,
component_yield_factor,
component_remarks,
effectivity_date,
change_notice,
implementation_date,
disable_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
planning_factor,
quantity_related,
so_basis,
optional,
mutually_exclusive_options,
include_in_cost_rollup,
check_atp,
bom_item_type,
from_end_item_unit_number,
to_end_item_unit_number,
optional_on_model,
--BUGFIX 2740820
parent_bill_seq_id, --BUGFIX 2740820
model_comp_seq_id, --BUGFIX 2740820
plan_level,
--BUGFIX 2740820
enforce_int_requirements, --BUGFIX 2991472
from_object_revision_id,
from_minor_revision_id,
pk1_value,
pk2_value,
auto_request_material,
-- Bug 3662214 : Added following 4 fields
suggested_vendor_name,
vendor_id,
unit_price,
from_end_item_rev_id,
to_end_item_rev_id,
from_end_item_minor_rev_id,
to_end_item_minor_rev_id,
component_item_revision_id,
component_minor_revision_id,
basis_type,
to_object_revision_id,
to_minor_revision_id
)
SELECT bic.shipping_allowed,
bic.required_to_ship,
bic.required_for_revenue,
bic.include_on_ship_docs,
bic.include_on_bill_docs,
--bic.low_quantity,
--bic.high_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.low_quantity,
DECODE(BIC.low_quantity,null,null, --Added this inner Deocde for Bug 6847530
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.low_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure))) Comp_low_qty,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.high_quantity,
DECODE(BIC.high_quantity,null,null, --Added this inner Deocde for Bug 6847530
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.high_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure))) Comp_high_qty,
x_acd_type,
bom_inventory_components_s.NEXTVAL,
DECODE (x_acd_type,
NULL, NULL,
bom_inventory_components_s.CURRVAL
),
to_sequence_id,
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate,
bic.wip_supply_type,
DECODE (rto_flag, 'Y', 2, bic.pick_components),
DECODE (x_from_org_id,
to_org_id, bic.supply_subinventory,
DECODE( l_default_wip_params, 1, msi.wip_supply_subinventory, NULL )
),
DECODE (x_from_org_id,
to_org_id, bic.supply_locator_id,
DECODE( l_default_wip_params, 1, msi.wip_supply_locator_id, NULL )
),
bic.operation_lead_time_percent,
x_rev_item_seq_id,
bic.cost_factor,
bic.operation_seq_num,
bic.component_item_id,
SYSDATE,
user_id,
SYSDATE,
bic.component_sequence_id,
/*NULL comment for bug8431772,change NULL to user_id*/user_id,
bic.item_num,
-- bic.component_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.component_quantity,
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.component_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure)) Comp_qty,
bic.component_yield_factor,
bic.component_remarks,
-- R12 TTM ENH
CASE
-- The WHEN sequence is important
-- For When display option is set to 2 then what ever comps are targer date we need to
-- take that
-- ECO conditions should be in sync with BomCopyStructureAM
-- for creating revised item by grouping based on effectivity
WHEN display_option = 2 AND bill_or_eco = 2
THEN x_effectivity_date
WHEN display_option = 2
THEN trgt_comps_eff_date
-- Rev to Date conversion with current and future option
-- Convert the dates based on from item's revision
WHEN bill_or_eco = 1 -- Inline and explosion date is past
AND bic.from_end_item_rev_id IS NOT NULL
AND p_end_item_rev_id IS NOT NULL
AND (
(
SELECT fmirb.effectivity_date
FROM mtl_item_revisions_b fmirb
WHERE fmirb.revision_id = bic.from_end_item_rev_id
) < trgt_comps_eff_date
AND (
SELECT fmirb.effectivity_date
FROM mtl_item_revisions_b fmirb
WHERE fmirb.revision_id = p_end_item_rev_id
) < trgt_comps_eff_date
)
-- Explosion in the Past and Effectivity Date is also in the past, then the components
-- which are past effective will be effective from trgt_comps_eff_date
THEN trgt_comps_eff_date
WHEN bill_or_eco = 1 -- Inline and explosion date is future
AND bic.from_end_item_rev_id IS NOT NULL
AND p_end_item_rev_id IS NOT NULL
AND bic.from_end_item_rev_id = p_end_item_rev_id -- Future Exploded Rev
AND (
SELECT fmirb.effectivity_date
FROM mtl_item_revisions_b fmirb
WHERE fmirb.revision_id = p_end_item_rev_id
) > trgt_comps_eff_date
-- Explosion in the future and Effectivity Rev is also in the future, then the components
-- which are effective at the explosion rev alone will be effective from trgt_comps_eff_date
THEN trgt_comps_eff_date
-- Past effective components should be target data effective
WHEN bill_or_eco = 1
AND bic.from_end_item_rev_id IS NOT NULL
AND (
SELECT fmirb.effectivity_date
FROM mtl_item_revisions_b fmirb
WHERE fmirb.revision_id = bic.from_end_item_rev_id
) < trgt_comps_eff_date
THEN trgt_comps_eff_date
ELSE
(
SELECT fmirb.effectivity_date
FROM mtl_item_revisions_b fmirb
WHERE fmirb.revision_id = bic.from_end_item_rev_id
)
END AS effectivity_date,
x_e_change_notice,
-- Implementation date will be NULL for ECO flow and SYSDATE for inline copy
DECODE (bill_or_eco, 2, TO_DATE (NULL), SYSDATE),
CASE
WHEN ( bic.to_end_item_rev_id IS NULL OR display_option = 2 )
THEN TO_DATE (NULL)
-- Past disabled components will be copied with disable date as null
WHEN bill_or_eco = 2
AND (
( SELECT fmirb.effectivity_date
FROM mtl_item_revisions_b fmirb
WHERE fmirb.revision_id = bic.to_end_item_rev_id
) < x_effectivity_date
)
THEN TO_DATE (NULL)
-- Past disabled components will be copied with disable date as null
WHEN bill_or_eco = 1
AND (
( SELECT fmirb.effectivity_date
FROM mtl_item_revisions_b fmirb
WHERE fmirb.revision_id = bic.to_end_item_rev_id
) < trgt_comps_eff_date
)
THEN TO_DATE (NULL)
-- Future disabled components should be disabled as per the disable date of component
ELSE
(
SELECT fmirb.effectivity_date
FROM mtl_item_revisions_b fmirb
WHERE fmirb.revision_id = bic.to_end_item_rev_id
)
END AS disable_date,
decode(l_prof_val, null, bic.attribute_category, l_prof_val), --introduced for bug 10078346 bic.attribute_category,
bic.attribute1,
bic.attribute2,
bic.attribute3,
bic.attribute4,
bic.attribute5,
bic.attribute6,
bic.attribute7,
bic.attribute8,
bic.attribute9,
bic.attribute10,
bic.attribute11,
bic.attribute12,
bic.attribute13,
bic.attribute14,
bic.attribute15,
bic.planning_factor,
bic.quantity_related,
bic.so_basis,
bic.optional,
bic.mutually_exclusive_options,
bic.include_in_cost_rollup,
bic.check_atp,
msi.bom_item_type,
to_char(NULL) AS from_end_item_unit_number, -- Date Eff Bill will not have from_end_item_unit_numbers
to_char(NULL) AS to_end_item_unit_number, -- Date Eff Bill will not have to_end_item_unit_numbers
bic.optional_on_model,
--BUGFIX 2740820
bic.parent_bill_seq_id, --BUGFIX 2740820
bic.model_comp_seq_id,
--BUGFIX 2740820
bic.plan_level, --BUGFIX 2740820
bic.enforce_int_requirements,
-- Either Fixed or Floating rev, the components will be from when its created, current item rev
l_current_item_rev_id,
-- Minor rev is not supported. Populated the first minor rev
0,
bic.component_item_id,
to_org_id,
bic.auto_request_material,
-- Bug 3662214 : Added following 4 fields
bic.suggested_vendor_name,
bic.vendor_id,
bic.unit_price,
to_number(NULL) AS from_end_item_rev_id, -- From End Item Rev Ids won't be set for Date Eff Bill
to_number(NULL) AS to_end_item_rev_id, -- To End Item Rev Ids won't be set for Date Eff Bill
-- For Minor rev Ids
0 AS from_end_item_minor_rev_id,
0 AS to_end_item_minor_rev_id,
(
SELECT tmirb.revision_id
FROM mtl_item_revisions_b fmirb,
mtl_item_revisions_b tmirb
WHERE tmirb.inventory_item_id = bic.component_item_id
AND tmirb.organization_id = to_org_id
AND tmirb.revision = fmirb.revision
AND fmirb.revision_id = bic.component_item_revision_id
) AS component_item_revision_id,
CASE
WHEN bic.component_item_revision_id IS NULL
THEN to_number(NULL)
ELSE
-- Minor revision is not supported
0
END AS component_minor_revision_id,
bic.basis_type,
CASE
WHEN l_fixed_rev IS NOT NULL
-- For fixed rev copy the components as fixed rev
THEN l_to_item_rev_id
ELSE
to_number(NULL)
END AS to_object_revision_id,
CASE
WHEN l_fixed_rev IS NOT NULL
THEN 0
ELSE
to_number(NULL)
END AS to_minor_revision_id
FROM bom_components_b bic,
mtl_system_items msi,
MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
bom_copy_explosions_v bev
WHERE bic.bill_sequence_id = x_from_sequence_id
AND bic.component_item_id = msi.inventory_item_id
AND bic.component_item_id <> to_item_id
AND NVL (bic.eco_for_production, 2) = 2
AND msi.organization_id = to_org_id
AND msi.bom_enabled_flag = 'Y' -- Added for the bug 13963236
AND MSI.inventory_item_id = AA.inventory_item_id -- Added corresponding to Bug 6510185
AND AA.organization_id = from_org_id -- Added corresponding to Bug 6510185
AND ((direction = eng_to_bom
AND msi.eng_item_flag = 'N')
OR (direction <> eng_to_bom)
)
AND ((base_item_flag = -1
AND itm_type = 4
AND msi.bom_item_type = 4
)
OR base_item_flag <> -1
OR itm_type <> 4
)
AND ((bic.implementation_date IS NOT NULL)
OR (bic.implementation_date IS NULL
AND bic.change_notice = context_eco
AND ( bic.acd_type = 1 OR bic.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb
WHERE bcb.old_component_sequence_id = bic.component_sequence_id
AND bcb.change_notice = context_eco
AND bcb.acd_type = 3
AND bcb.effectivity_date <= trgt_comps_eff_date
AND bcb.implementation_date IS NULL
AND bcb.bill_sequence_id = bic.bill_sequence_id
)
AND 'T' = bev.access_flag
AND 'T' =
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bic.component_item_id),
TO_CHAR (to_org_id),
bom_exploder_pub.get_ego_user
)
AND bic.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bic.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
)
AND EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
ego_criteria_v ecv,
mtl_system_items_b msibs -- to assembly item
WHERE ecv.customization_application_id = 702
AND ecv.region_application_id = 702
AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
AND ecv.customization_code = ectv.customization_code
AND flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
AND ecv.value_varchar2 = msi.item_type -- Component
UNION ALL
SELECT 1
FROM DUAL
WHERE NOT EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
mtl_system_items_b msibs -- to assembly item
WHERE flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
)
);
INSERT INTO bom_components_b
(shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
acd_type,
component_sequence_id,
old_component_sequence_id,
bill_sequence_id,
request_id,
program_application_id,
program_id,
program_update_date,
wip_supply_type,
pick_components,
supply_subinventory,
supply_locator_id,
operation_lead_time_percent,
revised_item_sequence_id,
cost_factor,
operation_seq_num,
component_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
item_num,
component_quantity,
component_yield_factor,
component_remarks,
effectivity_date,
change_notice,
implementation_date,
disable_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
planning_factor,
quantity_related,
so_basis,
optional,
mutually_exclusive_options,
include_in_cost_rollup,
check_atp,
bom_item_type,
from_end_item_unit_number,
to_end_item_unit_number,
optional_on_model,
--BUGFIX 2740820
parent_bill_seq_id, --BUGFIX 2740820
model_comp_seq_id, --BUGFIX 2740820
plan_level,
--BUGFIX 2740820
enforce_int_requirements, --BUGFIX 2991472
-- COMPONENT_ITEM_REVISION_ID,
from_object_revision_id,
from_minor_revision_id,
-- FROM_BILL_REVISION_ID,
pk1_value,
pk2_value,
auto_request_material,
-- Bug 3662214 : Added following 4 fields
suggested_vendor_name,
vendor_id,
unit_price,
from_end_item_rev_id,
to_end_item_rev_id,
from_end_item_minor_rev_id,
to_end_item_minor_rev_id,
component_item_revision_id,
component_minor_revision_id,
basis_type,
to_object_revision_id,
to_minor_revision_id
)
SELECT bic.shipping_allowed,
bic.required_to_ship,
bic.required_for_revenue,
bic.include_on_ship_docs,
bic.include_on_bill_docs,
-- bic.low_quantity,
-- bic.high_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.low_quantity,
DECODE(BIC.low_quantity,null,null, --Added this inner Deocde for Bug 6847530
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.low_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure))) Comp_low_qty,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.high_quantity,
DECODE(BIC.high_quantity,null,null, --Added this inner Deocde for Bug 6847530
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.high_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure))) Comp_high_qty,
x_acd_type,
bom_inventory_components_s.NEXTVAL,
DECODE (x_acd_type,
NULL, NULL,
bom_inventory_components_s.CURRVAL
),
to_sequence_id,
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate,
bic.wip_supply_type,
DECODE (rto_flag, 'Y', 2, bic.pick_components),
DECODE (x_from_org_id,
to_org_id, bic.supply_subinventory,
DECODE( l_default_wip_params, 1, msi.wip_supply_subinventory, NULL )
),
DECODE (x_from_org_id,
to_org_id, bic.supply_locator_id,
DECODE( l_default_wip_params, 1, msi.wip_supply_locator_id, NULL )
),
bic.operation_lead_time_percent,
x_rev_item_seq_id,
bic.cost_factor,
bic.operation_seq_num,
bic.component_item_id,
SYSDATE,
user_id,
SYSDATE,
bic.component_sequence_id,
/*NULL comment for bug8431772,change NULL to user_id*/user_id,
bic.item_num,
--bic.component_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.component_quantity,
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.component_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure)) Comp_qty,
bic.component_yield_factor,
bic.component_remarks,
-- R12 TTM ENH
-- For Rev Eff Structure the eff date will be sysdate
sysdate AS effectivity_date,
x_e_change_notice,
-- Implementation date will be NULL for ECO flow and SYSDATE for inline copy
DECODE (bill_or_eco, 2, TO_DATE (NULL), SYSDATE),
-- For Rev Eff structure the disable date will be null
to_date(NULL) AS disable_date,
decode(l_prof_val, null, bic.attribute_category, l_prof_val), --introduced for bug 10078346 bic.attribute_category,
bic.attribute1,
bic.attribute2,
bic.attribute3,
bic.attribute4,
bic.attribute5,
bic.attribute6,
bic.attribute7,
bic.attribute8,
bic.attribute9,
bic.attribute10,
bic.attribute11,
bic.attribute12,
bic.attribute13,
bic.attribute14,
bic.attribute15,
bic.planning_factor,
bic.quantity_related,
bic.so_basis,
bic.optional,
bic.mutually_exclusive_options,
bic.include_in_cost_rollup,
bic.check_atp,
msi.bom_item_type,
to_char(NULL) AS from_end_item_unit_number,
to_char(NULL) AS to_end_item_unit_number,
bic.optional_on_model,
--BUGFIX 2740820
bic.parent_bill_seq_id, --BUGFIX 2740820
bic.model_comp_seq_id,
--BUGFIX 2740820
bic.plan_level, --BUGFIX 2740820
bic.enforce_int_requirements,
-- Either Fixed or Floating rev, the components will be from when its created, current item rev
l_current_item_rev_id,
-- Minor rev is not supported. Populated the first minor rev
0,
bic.component_item_id,
to_org_id,
bic.auto_request_material,
-- Bug 3662214 : Added following 4 fields
bic.suggested_vendor_name,
bic.vendor_id,
bic.unit_price,
CASE
-- ECO conditions should be in sync with BomCopyStructureAM
-- for creating revised item by grouping based on effectivity
WHEN display_option = 2 AND bill_or_eco = 2
THEN eco_end_item_rev_id
WHEN display_option = 2
THEN trgt_comps_end_item_rev_id
WHEN bill_or_eco = 1 -- Inline and explosion rev is past or smaller
-- Explosion Date is past
-- which are with smaller revision effective will be effective from eco_end_item_rev_id
AND EXISTS
(
SELECT tmirb.REVISION
FROM MTL_ITEM_REVISIONS_B tmirb
WHERE tmirb.REVISION_ID = trgt_comps_end_item_rev_id
AND tmirb.revision > get_current_item_rev(from_item_id, from_org_id, bic.effectivity_date)
)
AND EXISTS
(
SELECT tmirb.REVISION
FROM MTL_ITEM_REVISIONS_B tmirb
WHERE tmirb.REVISION_ID = trgt_comps_end_item_rev_id
AND tmirb.revision > get_current_item_rev(from_item_id, from_org_id, rev_date)
)
THEN trgt_comps_end_item_rev_id
WHEN bill_or_eco = 1 -- Inline and explosion Date is future
-- Explosion rev is greater and from_end_item_rev is also greater, then the components
-- which are effective on exploded revision will be effective from trgt_comps_end_item_rev_id
AND trgt_comps_end_item_rev_id IS NOT NULL
AND bic.effectivity_date = rev_date
AND EXISTS
(
SELECT tmirb.REVISION
FROM MTL_ITEM_REVISIONS_B tmirb
WHERE tmirb.REVISION_ID = trgt_comps_end_item_rev_id
AND tmirb.revision < get_current_item_rev(from_item_id, from_org_id, rev_date)
)
THEN trgt_comps_end_item_rev_id
-- Past Effective should be effective from target revision
WHEN bill_or_eco = 1
AND EXISTS
(
SELECT tmirb.REVISION
FROM MTL_ITEM_REVISIONS_B tmirb
WHERE tmirb.REVISION_ID = trgt_comps_end_item_rev_id
AND tmirb.revision > get_current_item_rev(from_item_id, from_org_id, bic.effectivity_date)
)
THEN trgt_comps_end_item_rev_id
ELSE
(
SELECT tmirb.revision_id
FROM mtl_item_revisions_b tmirb
WHERE tmirb.inventory_item_id = to_item_id
AND tmirb.organization_id = to_org_id
AND tmirb.revision = get_current_item_rev(from_item_id, from_org_id, bic.effectivity_date)
)
END AS from_end_item_rev_id,
CASE
WHEN ( bic.disable_date IS NULL OR display_option = 2)
THEN to_number(NULL)
WHEN bill_or_eco = 1
AND bic.disable_date IS NOT NULL
AND EXISTS
(
SELECT tmirb.revision_id
FROM mtl_item_revisions_b tmirb
WHERE tmirb.inventory_item_id = to_item_id
AND tmirb.organization_id = to_org_id
AND tmirb.revision = get_current_item_rev( from_item_id, from_org_id, bic.disable_date)
)
AND EXISTS
(
SELECT mirb.REVISION
FROM MTL_ITEM_REVISIONS_B mirb
WHERE mirb.REVISION_ID = trgt_comps_end_item_rev_id
AND mirb.revision > get_current_item_rev( from_item_id, from_org_id, bic.disable_date)
)
THEN to_number(NULL)
WHEN
bic.disable_date IS NOT NULL
THEN
(
SELECT tmirb.revision_id
FROM mtl_item_revisions_b tmirb
WHERE tmirb.inventory_item_id = to_item_id
AND tmirb.organization_id = to_org_id
AND tmirb.revision = get_current_item_rev( from_item_id, from_org_id, bic.disable_date)
)
-- When No Item Rev Exists for the to item then populate to item rev as also null
ELSE
to_number(NULL)
END AS to_end_item_rev_id,
-- For Minor rev Ids
0 AS from_end_item_minor_rev_id,
0 AS to_end_item_minor_rev_id,
(
SELECT tmirb.revision_id
FROM mtl_item_revisions_b fmirb,
mtl_item_revisions_b tmirb
WHERE tmirb.inventory_item_id = bic.component_item_id
AND tmirb.organization_id = to_org_id
AND tmirb.revision = fmirb.revision
AND fmirb.revision_id = bic.component_item_revision_id
) AS component_item_revision_id,
CASE
WHEN bic.component_item_revision_id IS NULL
THEN to_number(NULL)
ELSE
-- Minor revision is not supported
0
END AS component_minor_revision_id,
bic.basis_type,
CASE
WHEN l_fixed_rev IS NOT NULL
-- For fixed rev copy the components as fixed rev
THEN l_to_item_rev_id
ELSE
to_number(NULL)
END AS to_object_revision_id,
CASE
WHEN l_fixed_rev IS NOT NULL
THEN 0
ELSE
to_number(NULL)
END AS to_minor_revision_id
FROM bom_components_b bic,
mtl_system_items msi,
MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
bom_copy_explosions_v bev
WHERE bic.bill_sequence_id = x_from_sequence_id
AND bic.component_item_id = msi.inventory_item_id
AND bic.component_item_id <> to_item_id
AND NVL (bic.eco_for_production, 2) = 2
AND msi.organization_id = to_org_id
AND MSI.inventory_item_id = AA.inventory_item_id -- Added corresponding to Bug 6510185
AND AA.organization_id = from_org_id -- Added corresponding to Bug 6510185
AND MSI.BOM_ENABLED_FLAG = 'Y' --Bug 3595979 -- Uncommented for the bug 13963236
AND ((direction = eng_to_bom
AND msi.eng_item_flag = 'N')
OR (direction <> eng_to_bom)
)
AND ((base_item_flag = -1
AND itm_type = 4
AND msi.bom_item_type = 4
)
OR base_item_flag <> -1
OR itm_type <> 4
)
AND ((bic.implementation_date IS NOT NULL)
OR (bic.implementation_date IS NULL
AND bic.change_notice = context_eco
AND ( bic.acd_type = 1 OR bic.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb
WHERE bcb.old_component_sequence_id = bic.component_sequence_id
AND bcb.change_notice = context_eco
AND bcb.acd_type = 3
AND bcb.effectivity_date <= trgt_comps_eff_date
AND bcb.implementation_date IS NULL
AND bcb.bill_sequence_id = bic.bill_sequence_id
)
AND 'T' = bev.access_flag
AND 'T' =
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bic.component_item_id),
TO_CHAR (to_org_id),
bom_exploder_pub.get_ego_user
)
AND bic.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bic.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
)
AND EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
ego_criteria_v ecv,
mtl_system_items_b msibs -- to assembly item
WHERE ecv.customization_application_id = 702
AND ecv.region_application_id = 702
AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
AND ecv.customization_code = ectv.customization_code
AND flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
AND ecv.value_varchar2 = msi.item_type -- Component
UNION ALL
SELECT 1
FROM DUAL
WHERE NOT EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
mtl_system_items_b msibs -- to assembly item
WHERE flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
)
);
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
l_error_msg_tbl(l_msg_count).message_text,
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb
WHERE bcb.component_sequence_id = l_from_comps(l_index);
SELECT COUNT (*)
INTO l_no_access_comp_cnt
FROM bom_components_b bcb,
mtl_system_items_b_kfv msbk1,
bom_copy_explosions_v bev
WHERE bcb.bill_sequence_id = x_from_sequence_id
AND bcb.component_item_id = msbk1.inventory_item_id
AND bcb.component_item_id <> to_item_id
AND 'T' <>
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bcb.component_item_id),
TO_CHAR (from_org_id),
bom_exploder_pub.get_ego_user
)
AND msbk1.organization_id = from_org_id
AND bcb.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND ((bcb.implementation_date IS NOT NULL)
OR (bcb.implementation_date IS NULL
AND bcb.change_notice = context_eco
AND ( bcb.acd_type = 1 OR bcb.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb1
WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
AND bcb1.change_notice = context_eco
AND bcb1.acd_type = 3
AND bcb1.effectivity_date <= trgt_comps_eff_date
AND bcb1.implementation_date IS NULL
AND bcb1.bill_sequence_id = bcb.bill_sequence_id
)
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bcb.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
);
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT from_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (from_item_id,
from_org_id,
rev_date
),
get_cnt_message ('BOM_COPY_ERR_COMP_NO_ACCESS',
msbk1.concatenated_segments,
TO_NUMBER (l_no_access_comp_cnt)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM mtl_system_items_b_kfv msbk1
WHERE msbk1.inventory_item_id = from_item_id
AND msbk1.organization_id = from_org_id;
SELECT COUNT (*)
INTO l_no_access_comp_cnt
FROM bom_components_b bcb,
mtl_system_items_b_kfv msbk1,
bom_copy_explosions_v bev
WHERE bcb.bill_sequence_id = x_from_sequence_id
AND bcb.component_item_id = msbk1.inventory_item_id
AND bcb.component_item_id <> to_item_id
AND 'T' <>
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bcb.component_item_id),
TO_CHAR (to_org_id),
bom_exploder_pub.get_ego_user
)
AND msbk1.organization_id = from_org_id
AND bcb.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND ((bcb.implementation_date IS NOT NULL)
OR (bcb.implementation_date IS NULL
AND bcb.change_notice = context_eco
AND ( bcb.acd_type = 1 OR bcb.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb1
WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
AND bcb1.change_notice = context_eco
AND bcb1.acd_type = 3
AND bcb1.effectivity_date <= trgt_comps_eff_date
AND bcb1.implementation_date IS NULL
AND bcb1.bill_sequence_id = bcb.bill_sequence_id
)
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bcb.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
);
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT from_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (from_item_id,
from_org_id,
rev_date
),
get_cnt_message ('BOM_COPY_ERR_CMPDEST_NO_ACCESS',
msbk1.concatenated_segments,
TO_NUMBER (l_no_access_comp_cnt)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM mtl_system_items_b_kfv msbk1
WHERE msbk1.inventory_item_id = from_item_id
AND msbk1.organization_id = from_org_id;
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
GET_MESSAGE ('BOM_COPY_ERR_ENG_COMP_MFG_BILL',
bom_globals.get_item_name(bcb.component_item_id, from_org_id),
bom_globals.get_item_name(to_item_id, from_org_id)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
mtl_system_items_b msib, -- component
bom_copy_explosions_v bev
WHERE bcb.bill_sequence_id = x_from_sequence_id
AND bcb.component_item_id = msib.inventory_item_id
AND bcb.component_item_id <> to_item_id
AND msib.organization_id = to_org_id
AND (direction = eng_to_bom
AND msib.eng_item_flag = 'Y')
AND bcb.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND ((bcb.implementation_date IS NOT NULL)
OR (bcb.implementation_date IS NULL
AND bcb.change_notice = context_eco
AND ( bcb.acd_type = 1 OR bcb.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb1
WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
AND bcb1.change_notice = context_eco
AND bcb1.acd_type = 3
AND bcb1.effectivity_date <= trgt_comps_eff_date
AND bcb1.implementation_date IS NULL
AND bcb1.bill_sequence_id = bcb.bill_sequence_id
)
AND 'T' = bev.access_flag
AND 'T' =
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bcb.component_item_id),
TO_CHAR (to_org_id),
bom_exploder_pub.get_ego_user
)
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bcb.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
);
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
GET_MESSAGE ('BOM_COPY_ERR_COMP_FOR_WIP_JOB',
bom_globals.get_item_name(bcb.component_item_id, from_org_id),
bom_globals.get_item_name(to_item_id, from_org_id)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
bom_copy_explosions_v bev
WHERE bcb.bill_sequence_id = x_from_sequence_id
AND bcb.component_item_id <> to_item_id
AND bcb.eco_for_production <> 2
AND bcb.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND ((bcb.implementation_date IS NOT NULL)
OR (bcb.implementation_date IS NULL
AND bcb.change_notice = context_eco
AND ( bcb.acd_type = 1 OR bcb.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb1
WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
AND bcb1.change_notice = context_eco
AND bcb1.acd_type = 3
AND bcb1.effectivity_date <= trgt_comps_eff_date
AND bcb1.implementation_date IS NULL
AND bcb1.bill_sequence_id = bcb.bill_sequence_id
)
AND 'T' = bev.access_flag
AND 'T' =
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bcb.component_item_id),
TO_CHAR (to_org_id),
bom_exploder_pub.get_ego_user
)
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bcb.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
);
/* This message need not be logged at all. When impl only is selected there won't be
any unimplemented component or if there is a context eco, we need not log the message, because
unimplemented components will be copied as implemented components
IF ( context_eco IS NULL AND bill_or_eco = 1 )
THEN
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (msbk1.inventory_item_id,
from_org_id,
rev_date
),
GET_MESSAGE ('BOM_COPY_ERR_UNIMPL_COMP',
msbk1.concatenated_segments,
msbk2.concatenated_segments
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E'
FROM bom_components_b bcb,
mtl_system_items_b_kfv msbk1,
mtl_system_items_b_kfv msbk2,
bom_copy_explosions_v bev
WHERE bcb.bill_sequence_id = x_from_sequence_id
AND bcb.component_item_id = msbk1.inventory_item_id
AND bcb.component_item_id <> to_item_id
AND bcb.implementation_date IS NULL
AND msbk1.organization_id = to_org_id
AND bcb.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND msbk2.inventory_item_id = to_item_id
AND msbk2.organization_id = to_org_id;
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
GET_MESSAGE ('BOM_COPY_ERR_COMP_NOT_STANDARD',
bom_globals.get_item_name(bcb.component_item_id, from_org_id),
bom_globals.get_item_name(to_item_id, from_org_id)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
mtl_system_items_b msib,
bom_copy_explosions_v bev
WHERE bcb.bill_sequence_id = x_from_sequence_id
AND bcb.component_item_id = msib.inventory_item_id
AND bcb.component_item_id <> to_item_id
AND bcb.implementation_date IS NOT NULL
AND msib.organization_id = to_org_id
AND bcb.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND (base_item_flag = -1
AND itm_type = 4
AND msib.bom_item_type <> 4
)
AND ((bcb.implementation_date IS NOT NULL)
OR (bcb.implementation_date IS NULL
AND bcb.change_notice = context_eco
AND ( bcb.acd_type = 1 OR bcb.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb1
WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
AND bcb1.change_notice = context_eco
AND bcb1.acd_type = 3
AND bcb1.effectivity_date <= trgt_comps_eff_date
AND bcb1.implementation_date IS NULL
AND bcb1.bill_sequence_id = bcb.bill_sequence_id
)
AND 'T' = bev.access_flag
AND 'T' =
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bcb.component_item_id),
TO_CHAR (to_org_id),
bom_exploder_pub.get_ego_user
)
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bcb.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
);
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL, -- MSBK1.CONCATENATED_SEGMENTS,
get_current_item_rev(bcb.component_item_id, from_org_id, rev_date),
check_component_type_rules(bcb.component_item_id,
to_item_id, to_org_id),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
bom_copy_explosions_v bev
WHERE bcb.bill_sequence_id = x_from_sequence_id
AND bcb.component_item_id <> to_item_id
AND bcb.implementation_date IS NOT NULL
AND bcb.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND ((bcb.implementation_date IS NOT NULL)
OR (bcb.implementation_date IS NULL
AND bcb.change_notice = context_eco
AND ( bcb.acd_type = 1 OR bcb.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb1
WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
AND bcb1.change_notice = context_eco
AND bcb1.acd_type = 3
AND bcb1.effectivity_date <= trgt_comps_eff_date
AND bcb1.implementation_date IS NULL
AND bcb1.bill_sequence_id = bcb.bill_sequence_id
)
AND 'T' = bev.access_flag
AND 'T' =
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bcb.component_item_id),
TO_CHAR (to_org_id),
bom_exploder_pub.get_ego_user
)
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bcb.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
)
AND check_component_type_rules(bcb.component_item_id,
to_item_id, to_org_id) IS NOT NULL; -- Component Type validation fails
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
GET_MESSAGE (
'BOM_CPY_REV_CHANGE_POLICY_ERR',
bom_globals.get_item_name(bcb.component_item_id, from_org_id),
bom_globals.get_item_name(to_item_id, from_org_id)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
mtl_system_items_b msib
WHERE bcb.bill_sequence_id = to_sequence_id
AND bcb.component_item_id = msib.inventory_item_id
AND msib.organization_id = to_org_id
AND 'Y' <>
bom_globals.check_change_policy_range(
to_item_id,
to_org_id,
NULL, -- p_start_revision
NULL, -- p_end_revision
NULL, -- p_start_rev_id
NULL, -- p_end_rev_id
bcb.effectivity_date, -- p_effective_date
bcb.disable_date, -- p_disable_date
bom_globals.get_change_policy_val(to_item_id, to_org_id,
BOM_Revisions.Get_Item_Revision_Id_Fn('ALL','ALL',to_org_id,
to_item_id, NVL(x_effectivity_date, trgt_comps_eff_date)),
null, -- rev id
p_trgt_str_type_id), -- p_current_chg_pol
p_trgt_str_type_id, -- p_structure_type_id
l_use_eco_flag -- p_use_eco
);
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
GET_MESSAGE (
'BOM_CPY_REV_CHANGE_POLICY_ERR',
bom_globals.get_item_name(bcb.component_item_id, from_org_id),
bom_globals.get_item_name(to_item_id, from_org_id)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
mtl_system_items_b msib
WHERE bcb.bill_sequence_id = to_sequence_id
AND bcb.component_item_id = msib.inventory_item_id
AND msib.organization_id = to_org_id
AND 'Y' <>
bom_globals.check_change_policy_range(
to_item_id,
to_org_id,
NULL, -- p_start_revision
NULL, -- p_end_revision
bcb.from_end_item_rev_id, -- p_start_rev_id
bcb.to_end_item_rev_id, -- p_end_rev_id
NULL, -- p_effective_date
NULL, -- p_disable_date
bom_globals.get_change_policy_val(to_item_id, to_org_id,
NVL(eco_end_item_rev_id, trgt_comps_end_item_rev_id),
null, -- rev id
p_trgt_str_type_id), -- p_current_chg_pol
p_trgt_str_type_id, -- p_structure_type_id
l_use_eco_flag -- p_use_eco
);
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
GET_MESSAGE
('BOM_COPY_ERR_COMP_REV_DIFF',
bom_globals.get_item_name(bcb.component_item_id, from_org_id),
bom_globals.get_item_name(to_item_id, from_org_id),
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
bom_copy_explosions_v bev
WHERE bcb.bill_sequence_id = x_from_sequence_id
AND bcb.component_item_id <> to_item_id
AND bcb.implementation_date IS NOT NULL
AND bcb.component_sequence_id = bev.component_sequence_id
-- Error needs to be logged only for fixed revision components
AND bcb.component_item_revision_id IS NOT NULL
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND ((bcb.implementation_date IS NOT NULL)
OR (bcb.implementation_date IS NULL
AND bcb.change_notice = context_eco
AND ( bcb.acd_type = 1 OR bcb.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb1
WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
AND bcb1.change_notice = context_eco
AND bcb1.acd_type = 3
AND bcb1.effectivity_date <= trgt_comps_eff_date
AND bcb1.implementation_date IS NULL
AND bcb1.bill_sequence_id = bcb.bill_sequence_id
)
AND 'T' = bev.access_flag
AND 'T' =
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bcb.component_item_id),
TO_CHAR (to_org_id),
bom_exploder_pub.get_ego_user
)
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bcb.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
)
AND NOT EXISTS (
SELECT tmirb.revision_id
FROM mtl_item_revisions_b fmirb,
mtl_item_revisions_b tmirb
WHERE tmirb.inventory_item_id = bcb.component_item_id
AND tmirb.organization_id = to_org_id
AND tmirb.revision = fmirb.revision
AND fmirb.revision_id =
bcb.component_item_revision_id);
INSERT INTO bom_components_b
(shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
acd_type,
component_sequence_id,
old_component_sequence_id,
bill_sequence_id,
request_id,
program_application_id,
program_id,
program_update_date,
wip_supply_type,
pick_components,
supply_subinventory,
supply_locator_id,
operation_lead_time_percent,
revised_item_sequence_id,
cost_factor,
operation_seq_num,
component_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
item_num,
component_quantity,
component_yield_factor,
component_remarks,
effectivity_date,
change_notice,
implementation_date,
disable_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
planning_factor,
quantity_related,
so_basis,
optional,
mutually_exclusive_options,
include_in_cost_rollup,
check_atp,
bom_item_type,
from_end_item_unit_number,
to_end_item_unit_number,
optional_on_model,
--BUGFIX 2740820
parent_bill_seq_id, --BUGFIX 2740820
model_comp_seq_id, --BUGFIX 2740820
plan_level,
--BUGFIX 2740820
enforce_int_requirements, --BUGFIX 2991472
pk1_value,
/* Added to prevent BOMTSTRC from */
pk2_value,
/* giving errors while defaulting */
auto_request_material,
-- Bug 3662214 : Added following 4 fields
suggested_vendor_name,
vendor_id,
unit_price,
basis_type
)
SELECT shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
-- low_quantity,
-- high_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.low_quantity,
DECODE(BIC.low_quantity,null,null, --Added this inner Deocde for Bug 6847530
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.low_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure))) Comp_low_qty,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.high_quantity,
DECODE(BIC.high_quantity,null,null, --Added this inner Deocde for Bug 6847530
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.high_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure))) Comp_high_qty,
x_acd_type,
bom_inventory_components_s.NEXTVAL,
DECODE (x_acd_type,
NULL, NULL,
bom_inventory_components_s.CURRVAL
),
to_sequence_id,
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate,
bic.wip_supply_type,
DECODE (rto_flag, 'Y', 2, pick_components),
DECODE (x_from_org_id,
to_org_id, supply_subinventory,
DECODE( l_default_wip_params, 1, msi.wip_supply_subinventory, NULL )
),
DECODE (x_from_org_id,
to_org_id, supply_locator_id,
DECODE( l_default_wip_params, 1, msi.wip_supply_locator_id, NULL )
),
operation_lead_time_percent,
x_rev_item_seq_id,
cost_factor,
operation_seq_num,
component_item_id,
SYSDATE,
user_id,
SYSDATE,
component_sequence_id,
/*NULL comment for bug8431772,change NULL to user_id*/user_id,
item_num,
-- component_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.component_quantity,
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.component_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure)) Comp_qty,
component_yield_factor,
component_remarks,
-- Bug 2161841
DECODE (bill_or_eco,
2, x_effectivity_date,
GREATEST (effectivity_date, SYSDATE)
),
--This is replaced by the next line to handle the bug 1636829
--DECODE(bill_or_eco,2,X_EFFECTIVITY_DATE,EFFECTIVITY_DATE),
-- Bug 2161841
-- GREATEST(EFFECTIVITY_DATE,SYSDATE),This was the orig line, was modified for eco's as per bug 315166
x_e_change_notice,
DECODE (bill_or_eco,
2, TO_DATE (NULL),
implementation_date
),
DECODE (bill_or_eco, 2, TO_DATE (NULL), disable_date),
decode(l_prof_val, null, bic.attribute_category, l_prof_val), --introduced for bug 10078346 bic.attribute_category,
bic.attribute1,
bic.attribute2,
bic.attribute3,
bic.attribute4,
bic.attribute5,
bic.attribute6,
bic.attribute7,
bic.attribute8,
bic.attribute9,
bic.attribute10,
bic.attribute11,
bic.attribute12,
bic.attribute13,
bic.attribute14,
bic.attribute15,
planning_factor,
quantity_related,
so_basis,
optional,
mutually_exclusive_options,
include_in_cost_rollup,
--DECODE(atp_comp_flag, 'Y', CHECK_ATP, 2), fixed bug 2249375
check_atp,
msi.bom_item_type,
DECODE (bill_or_eco,
2, x_unit_number,
bic.from_end_item_unit_number
),
bic.to_end_item_unit_number,
bic.optional_on_model,
--BUGFIX 2740820
bic.parent_bill_seq_id, --BUGFIX 2740820
bic.model_comp_seq_id,
--BUGFIX 2740820
bic.plan_level, --BUGFIX 2740820
bic.enforce_int_requirements,
--BUGFIX 2991472
bic.component_item_id,
to_org_id,
bic.auto_request_material,
-- Bug 3662214 : Added following 4 fields
bic.suggested_vendor_name,
bic.vendor_id,
bic.unit_price,
bic.basis_type
FROM bom_inventory_components bic,
mtl_system_items msi,
MTL_SYSTEM_ITEMS AA -- Added corresponding to Bug 6510185
WHERE bic.bill_sequence_id = x_from_sequence_id
AND bic.component_item_id = msi.inventory_item_id
AND bic.component_item_id <> to_item_id
AND NVL (bic.eco_for_production, 2) = 2
AND msi.organization_id = to_org_id
AND MSI.inventory_item_id = AA.inventory_item_id -- Added corresponding to Bug 6510185
AND AA.organization_id = from_org_id -- Added corresponding to Bug 6510185
AND MSI.BOM_ENABLED_FLAG = 'Y' --Bug 3595979 -- Uncommented for the bug 13963236
AND ((direction = eng_to_bom
AND msi.eng_item_flag = 'N')
OR (direction <> eng_to_bom)
)
AND ((x_unit_assembly = 'N'
AND ((display_option = 1) -- ALL
OR (display_option = 2
AND (effectivity_date <= rev_date
AND
-- Added condition of sysdate for Bug 2161841
( (disable_date > rev_date
AND disable_date > SYSDATE
)
OR disable_date IS NULL
)
)
)
OR -- CURRENT
(display_option = 3
AND
-- Added condition of sysdate for Bug 2161841
( (disable_date > rev_date
AND disable_date > SYSDATE
)
OR disable_date IS NULL
)
)
) -- CURRENT + FUTURE
)
OR (x_unit_assembly = 'Y'
AND ((display_option = 1) -- ALL
OR (display_option = 2
AND disable_date IS NULL
AND (from_end_item_unit_number <= unit_number
AND (to_end_item_unit_number >=
unit_number
OR to_end_item_unit_number IS NULL
)
)
)
OR -- CURRENT
(display_option = 3
AND disable_date IS NULL
AND (to_end_item_unit_number >= unit_number
OR to_end_item_unit_number IS NULL
)
)
) -- CURRENT + FUTURE
)
)
AND ((base_item_flag = -1
AND itm_type = 4
AND msi.bom_item_type = 4
)
OR base_item_flag <> -1
OR itm_type <> 4
)
AND implementation_date IS NOT NULL;
UPDATE BOM_COMPONENTS_B
SET COMPONENT_ITEM_ID = replacement_items_arr(j),
COMPONENT_ITEM_REVISION_ID = replacement_items_rev_ids_arr(j)
WHERE BILL_SEQUENCE_ID = to_sequence_id
AND CREATED_BY = replace_comps_arr(j);
SELECT common_routing_sequence_id
INTO to_rtg_seq_id
FROM bom_operational_routings
WHERE organization_id = to_org_id
AND assembly_item_id = to_item_id
AND (NVL (alternate_routing_designator, 'NONE') =
NVL (to_alternate, 'NONE')
OR (to_alternate IS NOT NULL
AND alternate_routing_designator IS NULL
AND NOT EXISTS (
SELECT NULL
FROM bom_operational_routings bor2
WHERE bor2.organization_id = to_org_id
AND bor2.assembly_item_id = to_item_id
AND bor2.alternate_routing_designator =
to_alternate)
)
);
UPDATE bom_inventory_components
SET operation_seq_num = 1
WHERE bill_sequence_id = to_sequence_id;
UPDATE bom_inventory_components bic
SET operation_seq_num = 1
WHERE bill_sequence_id = to_sequence_id
AND NOT EXISTS (
SELECT NULL
FROM bom_operation_sequences bos
WHERE routing_sequence_id = to_rtg_seq_id
AND bos.operation_seq_num = bic.operation_seq_num);
SELECT COUNT (*)
INTO dummy
FROM bom_inventory_components bic
WHERE bic.bill_sequence_id = to_sequence_id
AND EXISTS (
SELECT NULL
FROM bom_inventory_components bic2
WHERE bic2.bill_sequence_id = to_sequence_id
AND bic2.ROWID <> bic.ROWID
AND bic2.operation_seq_num = bic.operation_seq_num
AND bic2.component_item_id = bic.component_item_id
AND bic2.disable_date IS NULL
AND (bic.to_end_item_unit_number IS NULL
OR (bic.to_end_item_unit_number >=
bic2.from_end_item_unit_number
)
)
AND (bic2.to_end_item_unit_number IS NULL
OR (bic.from_end_item_unit_number <=
bic2.to_end_item_unit_number
)
));
SELECT COUNT (*)
INTO dummy
FROM bom_components_b bcb
WHERE bcb.bill_sequence_id = to_sequence_id
AND EXISTS (
SELECT NULL
FROM bom_components_b bcb2
WHERE bcb2.bill_sequence_id = to_sequence_id
AND bcb2.ROWID <> bcb.ROWID
AND bcb2.operation_seq_num = bcb.operation_seq_num
AND bcb2.component_item_id = bcb.component_item_id
AND bcb2.disable_date IS NULL
AND (bcb.to_end_item_rev_id IS NULL
OR (get_minor_rev_code
(bcb.to_end_item_rev_id,
bcb.to_end_item_minor_rev_id
) >=
get_minor_rev_code
(bcb2.from_end_item_rev_id,
bcb2.from_end_item_minor_rev_id
)
)
)
AND (bcb2.to_end_item_rev_id IS NULL
OR (get_minor_rev_code
(bcb.from_end_item_rev_id,
bcb.from_end_item_minor_rev_id
) >=
get_minor_rev_code
(bcb2.to_end_item_rev_id,
bcb2.to_end_item_minor_rev_id
)
)
));
SELECT COUNT (*)
INTO dummy
FROM bom_inventory_components bic
WHERE bic.bill_sequence_id = to_sequence_id
AND EXISTS (
SELECT NULL
FROM bom_inventory_components bic2
WHERE bic2.bill_sequence_id = to_sequence_id
AND bic2.ROWID <> bic.ROWID
AND bic2.operation_seq_num = bic.operation_seq_num
AND bic2.component_item_id = bic.component_item_id
AND bic2.effectivity_date <= bic.effectivity_date
AND NVL (bic2.disable_date,
bic.effectivity_date + 1
) > bic.effectivity_date);
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
GET_MESSAGE ('BOM_COPY_ERR_NO_PLANNING_COMPS',
bom_globals.get_item_name(bcb.component_item_id, from_org_id),
bom_globals.get_item_name(to_item_id, from_org_id)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
mtl_system_items_b msib1,
mtl_system_items_b msib2
WHERE bcb.bill_sequence_id = to_sequence_id
AND (msib1.bom_item_type = planning
AND msib2.bom_item_type <> planning
)
AND msib2.inventory_item_id = to_item_id
AND msib2.organization_id = to_org_id
AND msib1.inventory_item_id = bcb.component_item_id
AND msib1.organization_id = to_org_id;
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
GET_MESSAGE ('BOM_COPY_ERR_NO_OPT_MODEL_COMP',
bom_globals.get_item_name(bcb.component_item_id, from_org_id),
bom_globals.get_item_name(to_item_id, from_org_id)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
mtl_system_items_b msib1,
mtl_system_items_b msib2
WHERE bcb.bill_sequence_id = to_sequence_id
AND (msib1.bom_item_type IN (model, option_class)
AND msib2.bom_item_type = STANDARD
AND msib2.base_item_id IS NULL
)
AND msib2.inventory_item_id = to_item_id
AND msib2.organization_id = to_org_id
AND msib1.inventory_item_id = bcb.component_item_id
AND msib1.organization_id = to_org_id;
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
GET_MESSAGE ('BOM_COPY_ERR_NO_ATO_OPT_COMPS',
bom_globals.get_item_name(bcb.component_item_id, from_org_id),
bom_globals.get_item_name(to_item_id, from_org_id)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
mtl_system_items_b msib1, -- Comp
mtl_system_items_b msib2 -- Structure
WHERE bcb.bill_sequence_id = to_sequence_id
AND (msib1.replenish_to_order_flag = 'Y'
AND msib1.bom_item_type = option_class
AND msib2.pick_components_flag = 'Y'
)
AND msib2.inventory_item_id = to_item_id
AND msib2.organization_id = to_org_id
AND msib1.inventory_item_id = bcb.component_item_id
AND msib1.organization_id = to_org_id;
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
GET_MESSAGE ('BOM_COPY_ERR_NO_ATO_STD_COMPS',
bom_globals.get_item_name(bcb.component_item_id, from_org_id),
bom_globals.get_item_name(to_item_id, from_org_id)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
mtl_system_items_b msib1, -- Comp
mtl_system_items_b msib2 -- Structure
WHERE bcb.bill_sequence_id = to_sequence_id
AND (msib1.replenish_to_order_flag = 'Y'
AND msib1.bom_item_type = STANDARD
AND msib2.pick_components_flag = 'Y'
AND msib2.bom_item_type = STANDARD
)
AND msib2.inventory_item_id = to_item_id
AND msib2.organization_id = to_org_id
AND msib1.inventory_item_id = bcb.component_item_id
AND msib1.organization_id = to_org_id;
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
GET_MESSAGE ('BOM_COPY_ERR_NO_PTO_COMPS',
bom_globals.get_item_name(bcb.component_item_id, from_org_id),
bom_globals.get_item_name(to_item_id, from_org_id)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
mtl_system_items_b msib1, -- Comp
mtl_system_items_b msib2 -- Structure
WHERE bcb.bill_sequence_id = to_sequence_id
AND (msib1.pick_components_flag = 'Y'
AND msib2.replenish_to_order_flag = 'Y'
)
AND msib2.inventory_item_id = to_item_id
AND msib2.organization_id = to_org_id
AND msib1.inventory_item_id = bcb.component_item_id
AND msib1.organization_id = to_org_id;
DELETE FROM bom_inventory_components bic
WHERE bic.bill_sequence_id = to_sequence_id
AND EXISTS (
SELECT NULL
FROM mtl_system_items msi1, -- bom
mtl_system_items msi2 -- component
WHERE ((msi2.bom_item_type = planning
AND msi1.bom_item_type <> planning
)
OR (msi2.bom_item_type IN (model, option_class)
AND msi1.bom_item_type = STANDARD
AND msi1.base_item_id IS NULL
)
OR (msi2.replenish_to_order_flag = 'Y'
AND msi2.bom_item_type = option_class
AND msi1.pick_components_flag = 'Y'
)
OR (
(nvl(fnd_profile.value('BOM:MANDATORY_ATO_IN_PTO'), 2) <> 1)
and
(msi2.replenish_to_order_flag = 'Y'
AND msi2.bom_item_type = STANDARD
AND msi1.pick_components_flag = 'Y'
AND msi1.bom_item_type = STANDARD
)
)--modified for BOM ER 9904085
OR (msi2.pick_components_flag = 'Y'
AND msi1.replenish_to_order_flag = 'Y'
)
)
AND msi1.inventory_item_id = to_item_id
AND msi1.organization_id = to_org_id
AND msi2.inventory_item_id = bic.component_item_id
AND msi2.organization_id = to_org_id);
UPDATE bom_components_b bic
SET bic.wip_supply_type = phantom
WHERE bic.bill_sequence_id = to_sequence_id
AND EXISTS (
SELECT NULL
FROM mtl_system_items msi1, -- assembly
mtl_system_items msi2 -- component
WHERE msi2.bom_item_type IN (model, option_class)
AND msi2.inventory_item_id = bic.component_item_id
AND msi2.organization_id = to_org_id
AND msi1.inventory_item_id = to_item_id
AND msi1.organization_id = to_org_id);
UPDATE bom_components_b bic
SET bic.optional = 1
WHERE bic.bill_sequence_id = to_sequence_id
AND nvl(fnd_profile.value('BOM:MANDATORY_ATO_IN_PTO'), 2) <> 1 --added for BOM ER 9904085
AND EXISTS (
SELECT NULL
FROM mtl_system_items msi1, -- assembly
mtl_system_items msi2 -- component
WHERE msi2.base_item_id IS NULL
AND msi2.replenish_to_order_flag = 'Y'
AND msi2.bom_item_type = STANDARD
AND msi1.pick_components_flag = 'Y'
AND msi1.bom_item_type = model
AND msi2.inventory_item_id = bic.component_item_id
AND msi2.organization_id = to_org_id
AND msi1.inventory_item_id = to_item_id
AND msi1.organization_id = to_org_id);
UPDATE bom_components_b bic
SET bic.optional = 1
WHERE bic.bill_sequence_id = to_sequence_id
AND EXISTS (
SELECT NULL
FROM mtl_system_items msi1, -- assembly
mtl_system_items msi2 -- component
WHERE msi2.base_item_id IS NULL
AND msi2.replenish_to_order_flag = 'Y'
AND msi2.bom_item_type = STANDARD
AND msi1.pick_components_flag = 'Y'
AND msi1.bom_item_type = option_class
AND msi2.inventory_item_id = bic.component_item_id
AND msi2.organization_id = to_org_id
AND msi1.inventory_item_id = to_item_id
AND msi1.organization_id = to_org_id);
INSERT INTO bom_reference_designators
(component_reference_designator,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
ref_designator_comment,
change_notice,
component_sequence_id,
acd_type,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
SELECT component_reference_designator,
SYSDATE,
user_id,
SYSDATE,
user_id,
user_id, /*NULL,bug fix:8639515*/
ref_designator_comment,
x_e_change_notice,
bic.component_sequence_id,
x_acd_type,
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate,
brd.attribute_category,
brd.attribute1,
brd.attribute2,
brd.attribute3,
brd.attribute4,
brd.attribute5,
brd.attribute6,
brd.attribute7,
brd.attribute8,
brd.attribute9,
brd.attribute10,
brd.attribute11,
brd.attribute12,
brd.attribute13,
brd.attribute14,
brd.attribute15
FROM bom_reference_designators brd,
bom_components_b bic,
bom_copy_explosions_v bev
WHERE bic.bill_sequence_id = to_sequence_id
AND NVL (bic.eco_for_production, 2) = 2
AND brd.component_sequence_id = bic.created_by
AND NVL (brd.acd_type, 1) <> 3
AND bic.created_by = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order;
INSERT INTO bom_reference_designators
(component_reference_designator,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
ref_designator_comment,
change_notice,
component_sequence_id,
acd_type,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
SELECT component_reference_designator,
SYSDATE,
user_id,
SYSDATE,
user_id,
user_id,/*NULL,Bugfix:8639515*/
ref_designator_comment,
x_e_change_notice,
bic.component_sequence_id,
x_acd_type,
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate,
brd.attribute_category,
brd.attribute1,
brd.attribute2,
brd.attribute3,
brd.attribute4,
brd.attribute5,
brd.attribute6,
brd.attribute7,
brd.attribute8,
brd.attribute9,
brd.attribute10,
brd.attribute11,
brd.attribute12,
brd.attribute13,
brd.attribute14,
brd.attribute15
FROM bom_reference_designators brd,
bom_inventory_components bic
WHERE bic.bill_sequence_id = to_sequence_id
AND NVL (bic.eco_for_production, 2) = 2
AND brd.component_sequence_id = bic.created_by
AND NVL (brd.acd_type, 1) <> 3;
INSERT INTO bom_substitute_components
(substitute_component_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
substitute_item_quantity,
component_sequence_id,
acd_type,
change_notice,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
enforce_int_requirements
)
SELECT substitute_component_id,
SYSDATE,
user_id,
SYSDATE,
user_id,
/*NULL comment for bug8431772,change NULL to user_id*/user_id,
--substitute_item_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,Bsc.substitute_item_quantity,
inv_convert.INV_UM_CONVERT(bsc.substitute_component_id,
NULL,
Bsc.substitute_item_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure)) Sub_Comp_qty,
bic.component_sequence_id,
x_acd_type,
x_e_change_notice,
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate,
bsc.attribute_category,
bsc.attribute1,
bsc.attribute2,
bsc.attribute3,
bsc.attribute4,
bsc.attribute5,
bsc.attribute6,
bsc.attribute7,
bsc.attribute8,
bsc.attribute9,
bsc.attribute10,
bsc.attribute11,
bsc.attribute12,
bsc.attribute13,
bsc.attribute14,
bsc.attribute15,
bsc.enforce_int_requirements
FROM bom_substitute_components bsc,
bom_components_b bic,
mtl_system_items msi,
MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
bom_copy_explosions_v bev
WHERE bic.bill_sequence_id = to_sequence_id
AND NVL (bic.eco_for_production, 2) = 2
AND bsc.component_sequence_id = bic.created_by
AND NVL (bsc.acd_type, 1) <> 3
AND ((direction = eng_to_bom
AND msi.eng_item_flag = 'N')
OR (direction <> eng_to_bom)
)
AND msi.inventory_item_id = bsc.substitute_component_id
AND msi.organization_id = to_org_id
AND msi.bom_enabled_flag = 'Y' -- Added for the bug 13963236
AND bic.created_by = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND MSI.inventory_item_id = AA.inventory_item_id -- Added corresponding to Bug 6510185
AND AA.organization_id = from_org_id -- Added corresponding to Bug 6510185
AND EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
ego_criteria_v ecv,
mtl_system_items_b msibs -- to assembly item
WHERE ecv.customization_application_id = 702
AND ecv.region_application_id = 702
AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
AND ecv.customization_code = ectv.customization_code
AND flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
AND ecv.value_varchar2 = msi.item_type -- Substitute Component
UNION ALL
SELECT 1
FROM DUAL
WHERE NOT EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
mtl_system_items_b msibs -- to assembly item
WHERE flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
)
);
INSERT INTO bom_substitute_components
(substitute_component_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
substitute_item_quantity,
component_sequence_id,
acd_type,
change_notice,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
enforce_int_requirements
)
SELECT substitute_component_id,
SYSDATE,
user_id,
SYSDATE,
user_id,
/*NULL comment for bug8431772,change NULL to user_id*/user_id,
--substitute_item_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,Bsc.substitute_item_quantity,
inv_convert.INV_UM_CONVERT(bsc.substitute_component_id,
NULL,
Bsc.substitute_item_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure)) Sub_Comp_qty,
bic.component_sequence_id,
x_acd_type,
x_e_change_notice,
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate,
bsc.attribute_category,
bsc.attribute1,
bsc.attribute2,
bsc.attribute3,
bsc.attribute4,
bsc.attribute5,
bsc.attribute6,
bsc.attribute7,
bsc.attribute8,
bsc.attribute9,
bsc.attribute10,
bsc.attribute11,
bsc.attribute12,
bsc.attribute13,
bsc.attribute14,
bsc.attribute15,
bsc.enforce_int_requirements
FROM bom_substitute_components bsc,
bom_inventory_components bic,
mtl_system_items msi,
MTL_SYSTEM_ITEMS AA -- Added corresponding to Bug 6510185
WHERE bic.bill_sequence_id = to_sequence_id
AND NVL (bic.eco_for_production, 2) = 2
AND bsc.component_sequence_id = bic.created_by
AND NVL (bsc.acd_type, 1) <> 3
AND ((direction = eng_to_bom
AND msi.eng_item_flag = 'N')
OR (direction <> eng_to_bom)
)
AND msi.inventory_item_id = bsc.substitute_component_id
AND msi.organization_id = to_org_id
AND msi.bom_enabled_flag = 'Y' -- Added for the bug 13963236
AND MSI.inventory_item_id = AA.inventory_item_id -- Added corresponding to Bug 6510185
AND AA.organization_id = from_org_id; -- Added corresponding to Bug 6510185;
INSERT INTO bom_component_operations
(comp_operation_seq_id,
operation_seq_num,
operation_sequence_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
component_sequence_id,
bill_sequence_id,
consuming_operation_flag,
consumption_quantity,
supply_subinventory,
supply_locator_id,
wip_supply_type,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bom_component_operations_s.NEXTVAL,
bco.operation_seq_num,
bos.operation_sequence_id,
SYSDATE,
user_id,
SYSDATE,
user_id,
/*NULL comment for bug8431772,change NULL to user_id*/user_id,
bic.component_sequence_id,
bic.bill_sequence_id,
bco.consuming_operation_flag,
bco.consumption_quantity,
DECODE (x_from_org_id,
to_org_id, bco.supply_subinventory,
DECODE( l_default_wip_params, 1, bic.supply_subinventory, NULL )
),
DECODE (x_from_org_id,
to_org_id, bco.supply_locator_id,
DECODE( l_default_wip_params, 1, bic.supply_locator_id, NULL )
),
bco.wip_supply_type,
bco.attribute_category,
bco.attribute1,
bco.attribute2,
bco.attribute3,
bco.attribute4,
bco.attribute5,
bco.attribute6,
bco.attribute7,
bco.attribute8,
bco.attribute9,
bco.attribute10,
bco.attribute11,
bco.attribute12,
bco.attribute13,
bco.attribute14,
bco.attribute15,
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_component_operations bco,
bom_components_b bic,
bom_operation_sequences bos,
bom_copy_explosions_v bev
WHERE bic.bill_sequence_id = to_sequence_id
AND NVL (bic.eco_for_production, 2) = 2
AND bco.component_sequence_id = bic.created_by
AND bos.routing_sequence_id = to_rtg_seq_id
AND bos.operation_seq_num = bco.operation_seq_num
AND bic.created_by = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order;
INSERT INTO bom_component_operations
(comp_operation_seq_id,
operation_seq_num,
operation_sequence_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
component_sequence_id,
bill_sequence_id,
consuming_operation_flag,
consumption_quantity,
supply_subinventory,
supply_locator_id,
wip_supply_type,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bom_component_operations_s.NEXTVAL,
bco.operation_seq_num,
bos.operation_sequence_id,
SYSDATE,
user_id,
SYSDATE,
user_id,
/*NULL comment for bug8431772,change NULL to user_id*/user_id,
bic.component_sequence_id,
bic.bill_sequence_id,
bco.consuming_operation_flag,
bco.consumption_quantity,
DECODE (x_from_org_id,
to_org_id, bco.supply_subinventory,
DECODE( l_default_wip_params, 1, bic.supply_subinventory, NULL )
),
DECODE (x_from_org_id,
to_org_id, bco.supply_locator_id,
DECODE( l_default_wip_params, 1, bic.supply_locator_id, NULL )
),
bco.wip_supply_type,
bco.attribute_category,
bco.attribute1,
bco.attribute2,
bco.attribute3,
bco.attribute4,
bco.attribute5,
bco.attribute6,
bco.attribute7,
bco.attribute8,
bco.attribute9,
bco.attribute10,
bco.attribute11,
bco.attribute12,
bco.attribute13,
bco.attribute14,
bco.attribute15,
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_component_operations bco,
bom_inventory_components bic,
bom_operation_sequences bos
WHERE bic.bill_sequence_id = to_sequence_id
AND NVL (bic.eco_for_production, 2) = 2
AND bco.component_sequence_id = bic.created_by
AND bos.routing_sequence_id = to_rtg_seq_id
AND bos.operation_seq_num = bco.operation_seq_num;
INSERT INTO bom_dependent_desc_elements
(bill_sequence_id,
element_name,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id
)
SELECT to_sequence_id,
bdde.element_name,
SYSDATE,
user_id,
SYSDATE,
user_id,
user_id,
NULL,
fnd_global.conc_program_id,
sysdate,
fnd_global.conc_request_id
FROM bom_dependent_desc_elements bdde
WHERE bdde.bill_sequence_id = x_from_sequence_id
AND ((itm_type = model
AND EXISTS (
SELECT NULL
FROM mtl_descriptive_elements mde
WHERE mde.item_catalog_group_id = itm_cat_grp_id
AND mde.element_name = bdde.element_name)
)
OR itm_type = option_class
);
x_last_update_login => '',
x_program_application_id => '',
x_program_id => fnd_global.conc_program_id,
x_request_id => fnd_global.conc_request_id
);
UPDATE bom_components_b bic
SET created_by = user_id
WHERE bic.bill_sequence_id = to_sequence_id;
SELECT COUNT (*)
INTO copy_comts
FROM fnd_attached_documents
WHERE entity_name = 'BOM_BILL_OF_MATERIALS'
AND pk1_value = to_char(to_sequence_id); --Bug 12569030, Added to_char
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
VALUES (to_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (to_item_id,
from_org_id,
SYSDATE
),
fnd_message.get,
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
);
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
VALUES (to_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (to_item_id,
from_org_id,
SYSDATE
),
fnd_message.get,
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
);
SELECT component_item_id
FROM bom_copy_explosions_v bs
WHERE bs.sort_order <> c_sort_order
AND bs.GROUP_ID = c_explode_group_id
START WITH bs.sort_order = c_sort_order
AND bs.GROUP_ID = c_explode_group_id
CONNECT BY PRIOR bs.parent_sort_order = bs.sort_order
AND bs.GROUP_ID = c_explode_group_id;
SELECT concatenated_segments
INTO l_component_name
FROM mtl_system_items_b_kfv msbk
WHERE msbk.inventory_item_id = component_rec.component_item_id
AND msbk.organization_id = p_org_id;
l_program_update_date DATE;
SELECT msibk.inventory_item_id,
concatenated_segments item_number,
-- c_org_id organization_id,
-- c_org_code organization_code,
msibk.primary_uom_code,
msibk.secondary_uom_code,
msibk.dual_uom_deviation_high,
msibk.dual_uom_deviation_low,
msibk.secondary_default_ind,
msibk.tracking_quantity_ind,
msibk.ont_pricing_qty_source
FROM mtl_system_items_b_kfv msibk,
bom_copy_explosions_v bev
WHERE msibk.inventory_item_id = bev.component_item_id
AND msibk.organization_id = bev.organization_id
AND (bev.implementation_date IS NOT NULL
OR (bev.implementation_date IS NULL
AND bev.change_notice = c_context_eco
AND bev.acd_type = 1
)
)
/*
Pass trgt_comps_eff_date and do this validation
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb
WHERE bcb.old_component_sequence_id = bev.component_sequence_id
AND bcb.change_notice = context_eco
AND bcb.acd_type = 3
AND bcb.effectivity_date <= trgt_comps_eff_date
AND bcb.implementation_date IS NULL
AND bcb.bill_sequence_id = bic.bill_sequence_id
)
*/
AND NOT EXISTS (
SELECT 1
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id = bev.component_item_id
AND msib.organization_id = c_org_id)
AND ( NOT EXISTS (
-- Direct Component Action is exclude
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = p_to_org_id
AND bcsa.component_sequence_id = bev.component_sequence_id
AND bcsa.component_exception_action = 1
)
OR NOT EXISTS (
-- Structure Action is exclude
SELECT 1
FROM bom_copy_structure_actions bcsa, bom_copy_explosions_v bcev
WHERE bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = p_to_org_id
AND bcsa.structure_exception_action = 1
AND bcsa.component_sequence_id = bcev.component_sequence_id
START WITH bcev.bill_sequence_id = bev.bill_sequence_id
CONNECT BY PRIOR bcev.bill_sequence_id = bcev.comp_bill_seq_id
AND bcev.bill_sequence_id <> bev.top_bill_sequence_id
)
)
;
SELECT mmsib.primary_uom_code, 1 l_not_exists, concatenated_segments,
mmsib.secondary_uom_code, mmsib.dual_uom_deviation_high, mmsib.dual_uom_deviation_low,
mmsib.secondary_default_ind, mmsib.tracking_quantity_ind, mmsib.ont_pricing_qty_source
FROM mtl_system_items_b_kfv mmsib
WHERE mmsib.inventory_item_id = p_item_id
AND mmsib.organization_id = p_master_org_id
AND NOT EXISTS
( SELECT 1
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id = mmsib.inventory_item_id
AND msib.organization_id = p_org_id
);
l_program_update_date := SYSDATE;
INSERT INTO mtl_interface_errors
( unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
VALUES (l_item_org_assign_tbl(l_index).inventory_item_id,
p_from_org_id,
p_copy_request_id,
NULL,
get_current_item_rev(l_item_org_assign_tbl(l_index).inventory_item_id,
p_from_org_id,
SYSDATE
),
l_item_org_assign_tbl(l_index).error_code,
'BOM_COPY',
SYSDATE,
p_usr_id,
SYSDATE,
p_usr_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
);
DELETE FROM bom_copy_structure_actions
WHERE copy_request_id IN (
SELECT copy_request_id
FROM bom_copy_structure_request
WHERE request_status = p_request_status);
DELETE FROM bom_copy_organization_list
WHERE copy_request_id IN (SELECT copy_request_id
FROM bom_copy_structure_request
WHERE request_status = p_request_status);
DELETE FROM bom_copy_structure_request
WHERE request_status = p_request_status;
DELETE FROM mtl_interface_errors mie
WHERE EXISTS (
SELECT 1
FROM bom_copy_structure_request bscr
WHERE bscr.copy_request_id = mie.transaction_id
AND bscr.request_status = p_request_status
AND mie.bo_identifier = 'BOM_COPY');
SELECT oav.organization_code, oav.organization_name,
CASE
WHEN EXISTS ( SELECT 1
FROM bom_structures_b bsb
WHERE bsb.organization_id = oav.organization_id
AND bsb.assembly_item_id = p_item_id
AND NVL(bsb.alternate_bom_designator,bom_globals.get_primary_ui) = NVL(p_structure_name,bom_globals.get_primary_ui)
AND bsb.bill_sequence_id = bsb.common_bill_sequence_id
) THEN
1 -- Structure already exists
WHEN EXISTS ( SELECT 1
FROM bom_structures_b bsb
WHERE bsb.organization_id = oav.organization_id
AND bsb.assembly_item_id = p_item_id
AND NVL(bsb.alternate_bom_designator,bom_globals.get_primary_ui) = NVL(p_structure_name,bom_globals.get_primary_ui)
AND bsb.bill_sequence_id <> bsb.common_bill_sequence_id
) THEN
2 -- Structure already exists and it is common structure
ELSE
0 -- Structure does not exist
END AS structure_exists,
NVL((SELECT assembly_type
FROM bom_structures_b bsb
WHERE bsb.organization_id = x_org_list_tbl(l_index)
AND bsb.assembly_item_id = p_item_id
AND NVL(bsb.alternate_bom_designator,bom_globals.get_primary_ui) = NVL(p_structure_name,bom_globals.get_primary_ui)
),2) AS assembly_type,
(SELECT revision
FROM (SELECT revision
FROM mtl_item_revisions_b mir
WHERE inventory_item_id = p_item_id
AND organization_id = x_org_list_tbl(l_index)
AND mir.effectivity_date <= p_effectivity_date
ORDER BY effectivity_date DESC, revision DESC)
WHERE rownum < 2) AS current_item_rev,
(SELECT revision_id
FROM (SELECT revision_id
FROM mtl_item_revisions_b mir
WHERE inventory_item_id = p_item_id
AND organization_id = x_org_list_tbl(l_index)
AND mir.effectivity_date <= p_effectivity_date
ORDER BY effectivity_date DESC, revision DESC)
WHERE rownum < 2) AS current_item_rev_id,
(SELECT revision_label
FROM (SELECT revision_label
FROM mtl_item_revisions_b mir
WHERE inventory_item_id = p_item_id
AND organization_id = x_org_list_tbl(l_index)
AND mir.effectivity_date <= p_effectivity_date
ORDER BY effectivity_date DESC, revision DESC)
WHERE rownum < 2) AS current_item_rev_label,
CASE
WHEN EXISTS
(
SELECT 1
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id = p_item_id
AND msib.organization_id = x_org_list_tbl(l_index)
) THEN
'Y'
ELSE
'N'
END AS item_exists
INTO x_org_code_tbl(l_index), x_org_name_tbl(l_index),
x_org_structure_tbl(l_index), x_assembly_type_tbl(l_index),
x_item_rev_tbl(l_index), x_item_rev_id_tbl(l_index),
x_item_rev_lbl_tbl(l_index), x_item_exists_tbl(l_index)
FROM org_access_view oav
WHERE oav.organization_id = x_org_list_tbl(l_index)
AND oav.responsibility_id = fnd_profile.value('RESP_ID')
AND oav.resp_application_id = fnd_profile.value('RESP_APPL_ID');
SELECT mp.organization_code
FROM mtl_system_items_b msib,
mtl_parameters mp,
bom_copy_organization_list bcol
WHERE msib.inventory_item_id = cp_item_id
AND msib.organization_id = mp.organization_id
AND bcol.copy_request_id = cp_copy_request_id
AND bcol.organization_id = mp.organization_id;
SELECT mp.organization_code
FROM mtl_parameters mp,
bom_copy_organization_list bcol,
bom_structures_b bsb,
bom_copy_structure_request bcsr
WHERE bsb.assembly_item_id = cp_item_id
AND bcsr.copy_request_id = cp_copy_request_id
AND bsb.organization_id = bcol.organization_id
AND NVL (bsb.alternate_bom_designator, bom_globals.get_primary_ui) =
NVL (bcsr.trgt_structure_name, bom_globals.get_primary_ui)
AND bcol.copy_request_id = bcsr.copy_request_id
AND bcol.organization_id = mp.organization_id;
SELECT mp.organization_code
FROM mtl_parameters mp,
bom_copy_organization_list bcol,
bom_structures_b bsb,
bom_copy_structure_request bcsr
WHERE bsb.bill_sequence_id <> bsb.source_bill_sequence_id
AND bsb.assembly_item_id = cp_item_id
AND bcsr.copy_request_id = cp_copy_request_id
AND bsb.organization_id = bcol.organization_id
AND NVL (bsb.alternate_bom_designator, bom_globals.get_primary_ui) =
NVL (bcsr.trgt_structure_name, bom_globals.get_primary_ui)
AND bcol.copy_request_id = bcsr.copy_request_id
AND bcol.organization_id = mp.organization_id;
SELECT mp.organization_code
FROM mtl_parameters mp,
bom_copy_organization_list bcol
WHERE bcol.copy_request_id = cp_copy_request_id
AND bcol.organization_id = mp.organization_id
AND NOT EXISTS (
SELECT 1
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id = cp_item_id
AND msib.organization_id = bcol.organization_id);
SELECT mp.organization_code
FROM mtl_parameters mp,
bom_copy_organization_list bcol
WHERE bcol.copy_request_id = cp_copy_request_id
AND bcol.organization_id = mp.organization_id
AND bcol.common_structure = 'N'
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_request bcsr,
bom_structures_b bsb
WHERE bsb.assembly_item_id = cp_item_id
AND bcsr.copy_request_id = bcol.copy_request_id
AND bsb.organization_id = bcol.organization_id
AND NVL (bsb.alternate_bom_designator, bom_globals.get_primary_ui) =
NVL (bcsr.trgt_structure_name, bom_globals.get_primary_ui));
SELECT mp.organization_code
FROM mtl_parameters mp,
bom_copy_organization_list bcol
WHERE bcol.copy_request_id = cp_copy_request_id
AND bcol.organization_id = mp.organization_id
AND bcol.common_structure = 'Y'
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_request bcsr,
bom_structures_b bsb
WHERE bcsr.copy_request_id = bcol.copy_request_id
AND bsb.organization_id = bcol.organization_id
AND bsb.assembly_item_id = cp_item_id
AND NVL (bsb.alternate_bom_designator, bom_globals.get_primary_ui) =
NVL (bcsr.trgt_structure_name, bom_globals.get_primary_ui));
PROCEDURE update_created_by (
p_user_id IN NUMBER
,p_to_bill_sequence_id IN NUMBER )
IS
BEGIN
bom_globals.g_skip_bomtbicx := 'Y';
UPDATE bom_components_b bic
SET created_by = p_user_id
WHERE bic.bill_sequence_id = p_to_bill_sequence_id;
SELECT 1
FROM DUAL
WHERE EXISTS (
SELECT NULL
FROM bom_structures_b bom,
bom_inventory_components bic
WHERE bom.organization_id <> to_org_id
AND bom.common_bill_sequence_id = to_sequence_id
AND bic.bill_sequence_id = to_sequence_id
AND NOT EXISTS (
SELECT NULL
FROM mtl_system_items cmsi,
mtl_system_items bmsi
WHERE cmsi.organization_id = bom.organization_id
AND cmsi.inventory_item_id =
bic.component_item_id
AND bmsi.organization_id = bom.organization_id
AND bmsi.inventory_item_id =
bom.assembly_item_id
AND cmsi.eng_item_flag =
DECODE (bom.assembly_type,
1, 'N',
2, cmsi.eng_item_flag
)
AND cmsi.bom_enabled_flag = 'Y'
AND cmsi.inventory_item_id <>
bom.assembly_item_id
AND ((bmsi.bom_item_type = 1
AND cmsi.bom_item_type <> 3
)
OR (bmsi.bom_item_type = 2
AND cmsi.bom_item_type <> 3
)
OR (bmsi.bom_item_type = 3)
OR (bmsi.bom_item_type = 4
AND (cmsi.bom_item_type = 4
OR (cmsi.bom_item_type IN (1, 2)
AND cmsi.replenish_to_order_flag =
'Y'
AND bmsi.base_item_id IS NOT NULL
AND bmsi.replenish_to_order_flag =
'Y'
)
)
)
)
AND (bmsi.bom_item_type = 3
OR bmsi.pick_components_flag = 'Y'
OR cmsi.pick_components_flag = 'N'
)
AND (bmsi.bom_item_type = 3
OR NVL (cmsi.bom_item_type, 4) <> 2
OR (cmsi.bom_item_type = 2
AND ((bmsi.pick_components_flag = 'Y'
AND cmsi.pick_components_flag =
'Y'
)
OR (bmsi.replenish_to_order_flag =
'Y'
AND cmsi.replenish_to_order_flag =
'Y'
)
)
)
)
AND NOT (bmsi.bom_item_type = 4
AND bmsi.pick_components_flag = 'Y'
AND cmsi.bom_item_type = 4
AND cmsi.replenish_to_order_flag = 'Y'
)));
SELECT revision_id,
revision
FROM mtl_item_revisions_b mir
WHERE mir.inventory_item_id = p_item_id
AND mir.organization_id = p_org_id
AND mir.effectivity_date <= p_rev_date
AND ROWNUM < 2
ORDER BY mir.effectivity_date DESC;
SELECT NVL (MAX (minor_revision_id), 0) minor_revision_id
FROM ego_minor_revisions
WHERE obj_name = 'EGO_ITEM'
AND pk1_value = p_pk1_value
AND NVL (pk2_value, '-1') = NVL (p_pk2_value, '-1')
AND NVL (pk3_value, '-1') = NVL (p_pk3_value, '-1');
SELECT concatenated_segments,
primary_uom_code
FROM mtl_system_items_b_kfv
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
SELECT bcb1.component_sequence_id from_component_seq_id,
bcb2.component_sequence_id to_sequence_id
FROM bom_components_b bcb1,
bom_components_b bcb2
WHERE bcb1.bill_sequence_id = p_from_seq_id
AND bcb1.component_sequence_id = bcb2.created_by
AND bcb2.bill_sequence_id = p_to_seq_id
AND bcb2.component_sequence_id > p_last_copied_comp_seq_id;
SELECT eec.change_id,
bcb.component_sequence_id
FROM eng_engineering_changes eec,
bom_components_b bcb
WHERE eec.change_notice = p_change_notice
AND eec.organization_id = p_local_org_id
AND bcb.bill_sequence_id = p_bill_seq_id;
SELECT bev.comp_fixed_rev_code
FROM bom_copy_explosions_v bev
WHERE bev.sort_order = p_parent_sort_order;
SELECT effectivity_date
FROM mtl_item_revisions_b
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND revision = p_revision;
SELECT max(bcb.component_sequence_id)
FROM bom_components_b bcb
WHERE bcb.bill_sequence_id = p_bill_seq_id;
SELECT DATA_LEVEL_ID
FROM EGO_DATA_LEVEL_B
WHERE DATA_LEVEL_NAME = p_data_level_name;
SELECT common_bill_sequence_id,
NVL (common_organization_id, organization_id)
INTO x_from_sequence_id,
x_from_org_id
FROM bom_structures_b
WHERE bill_sequence_id = x_from_sequence_id;
SELECT structure_type_id, effectivity_control
INTO l_str_type, l_from_eff_ctrl
FROM bom_structures_b
WHERE bill_sequence_id = from_sequence_id;
SELECT atp_components_flag,
replenish_to_order_flag,
DECODE (base_item_id, NULL, -1, 0),
bom_item_type,
item_catalog_group_id
INTO atp_comp_flag,
rto_flag,
base_item_flag,
itm_type,
itm_cat_grp_id
FROM mtl_system_items
WHERE organization_id = to_org_id
AND inventory_item_id = to_item_id;
SELECT application_id INTO l_app_id
FROM fnd_application WHERE application_short_name = 'BOM';
select default_context_field_name, context_column_name into l_context, l_context_column
from FND_DESCRIPTIVE_FLEXS where application_id = l_app_id and
descriptive_flexfield_name = 'BOM_INVENTORY_COMPONENTS';
--update attribute category only when reference field has a value of $PROFILE.profile_name
l_prof_check := substr(l_context, 1, 12);
INSERT INTO bom_components_b
(shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
acd_type,
component_sequence_id,
old_component_sequence_id,
bill_sequence_id,
request_id,
program_application_id,
program_id,
program_update_date,
wip_supply_type,
pick_components,
supply_subinventory,
supply_locator_id,
operation_lead_time_percent,
revised_item_sequence_id,
cost_factor,
operation_seq_num,
component_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
item_num,
component_quantity,
component_yield_factor,
component_remarks,
effectivity_date,
change_notice,
implementation_date,
disable_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
planning_factor,
quantity_related,
so_basis,
optional,
mutually_exclusive_options,
include_in_cost_rollup,
check_atp,
bom_item_type,
from_end_item_unit_number,
to_end_item_unit_number,
optional_on_model,
--BUGFIX 2740820
parent_bill_seq_id, --BUGFIX 2740820
model_comp_seq_id, --BUGFIX 2740820
plan_level,
--BUGFIX 2740820
enforce_int_requirements, --BUGFIX 2991472
from_object_revision_id,
from_minor_revision_id,
pk1_value,
pk2_value,
auto_request_material,
-- Bug 3662214 : Added following 4 fields
suggested_vendor_name,
vendor_id,
unit_price,
from_end_item_rev_id,
to_end_item_rev_id,
from_end_item_minor_rev_id,
to_end_item_minor_rev_id,
component_item_revision_id,
component_minor_revision_id,
basis_type,
to_object_revision_id,
to_minor_revision_id
)
SELECT bic.shipping_allowed,
bic.required_to_ship,
bic.required_for_revenue,
bic.include_on_ship_docs,
bic.include_on_bill_docs,
-- bic.low_quantity,
-- bic.high_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.low_quantity,
DECODE(BIC.low_quantity,null,null, --Added this inner Deocde for Bug 6847530
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.low_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure))) Comp_low_qty,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.high_quantity,
DECODE(BIC.high_quantity,null,null, --Added this inner Deocde for Bug 6847530
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.high_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure))) Comp_high_qty,
x_acd_type,
bom_inventory_components_s.NEXTVAL,
DECODE (x_acd_type,
NULL, NULL,
bom_inventory_components_s.CURRVAL
),
to_sequence_id,
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate,
bic.wip_supply_type,
DECODE (rto_flag, 'Y', 2, bic.pick_components),
DECODE (x_from_org_id,
to_org_id, bic.supply_subinventory,
DECODE( l_default_wip_params, 1, msi.wip_supply_subinventory, NULL )
),
DECODE (x_from_org_id,
to_org_id, bic.supply_locator_id,
DECODE( l_default_wip_params, 1, msi.wip_supply_locator_id, NULL )
),
bic.operation_lead_time_percent,
x_rev_item_seq_id,
bic.cost_factor,
bic.operation_seq_num,
bic.component_item_id,
SYSDATE,
user_id,
SYSDATE,
bic.component_sequence_id,
/*NULL comment for bug8431772,change NULL to user_id*/user_id,
bic.item_num,
-- bic.component_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.component_quantity,
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.component_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure)) Comp_qty,
bic.component_yield_factor,
bic.component_remarks,
-- R12 TTM ENH
x_effectivity_date,
x_e_change_notice,
-- Implementation date will be NULL for ECO flow
TO_DATE (NULL),
CASE
-- This flag will be set when current and future option is selected with
-- copy through ECO
WHEN bic.disable_date IS NOT NULL
AND bic.disable_date > x_effectivity_date
THEN bic.disable_date
ELSE
TO_DATE (NULL)
END AS disable_date,
decode(l_prof_val, null, bic.attribute_category, l_prof_val), --changed for bug 10078346 --bic.attribute_category,
bic.attribute1,
bic.attribute2,
bic.attribute3,
bic.attribute4,
bic.attribute5,
bic.attribute6,
bic.attribute7,
bic.attribute8,
bic.attribute9,
bic.attribute10,
bic.attribute11,
bic.attribute12,
bic.attribute13,
bic.attribute14,
bic.attribute15,
bic.planning_factor,
bic.quantity_related,
bic.so_basis,
bic.optional,
bic.mutually_exclusive_options,
bic.include_in_cost_rollup,
bic.check_atp,
msi.bom_item_type,
to_char(NULL) AS from_end_item_unit_number, -- Date Eff Bill will not have from_end_item_unit_numbers
to_char(NULL) AS to_end_item_unit_number, -- Date Eff Bill will not have to_end_item_unit_numbers
bic.optional_on_model,
--BUGFIX 2740820
bic.parent_bill_seq_id, --BUGFIX 2740820
bic.model_comp_seq_id,
--BUGFIX 2740820
bic.plan_level, --BUGFIX 2740820
bic.enforce_int_requirements,
-- Either Fixed or Floating rev, the components will be from when its created, current item rev
l_current_item_rev_id,
-- Minor rev is not supported. Populated the first minor rev
0,
bic.component_item_id,
to_org_id,
bic.auto_request_material,
-- Bug 3662214 : Added following 4 fields
bic.suggested_vendor_name,
bic.vendor_id,
bic.unit_price,
to_number(NULL) AS from_end_item_rev_id, -- From End Item Rev Ids won't be set for Date Eff Bill
to_number(NULL) AS to_end_item_rev_id, -- To End Item Rev Ids won't be set for Date Eff Bill
-- For Minor rev Ids
0 AS from_end_item_minor_rev_id,
0 AS to_end_item_minor_rev_id,
(
SELECT tmirb.revision_id
FROM mtl_item_revisions_b fmirb,
mtl_item_revisions_b tmirb
WHERE tmirb.inventory_item_id = bic.component_item_id
AND tmirb.organization_id = to_org_id
AND tmirb.revision = fmirb.revision
AND fmirb.revision_id = bic.component_item_revision_id
) AS component_item_revision_id,
CASE
WHEN bic.component_item_revision_id IS NULL
THEN to_number(NULL)
ELSE
-- Minor revision is not supported
0
END AS component_minor_revision_id,
bic.basis_type,
CASE
WHEN l_fixed_rev IS NOT NULL
-- For fixed rev copy the components as fixed rev
THEN l_to_item_rev_id
ELSE
to_number(NULL)
END AS to_object_revision_id,
CASE
WHEN l_fixed_rev IS NOT NULL
THEN 0
ELSE
to_number(NULL)
END AS to_minor_revision_id
FROM bom_components_b bic,
mtl_system_items msi,
MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
bom_copy_explosions_v bev
WHERE bic.bill_sequence_id = x_from_sequence_id
AND bic.component_item_id = msi.inventory_item_id
AND bic.component_item_id <> to_item_id
AND NVL (bic.eco_for_production, 2) = 2
AND msi.organization_id = to_org_id
AND MSI.inventory_item_id = AA.inventory_item_id -- Added corresponding to Bug 6510185
AND AA.organization_id = from_org_id -- Added corresponding to Bug 6510185
AND msi.bom_enabled_flag = 'Y' -- Added for the bug 14238677
AND ((direction = eng_to_bom
AND msi.eng_item_flag = 'N')
OR (direction <> eng_to_bom)
)
AND ((base_item_flag = -1
AND itm_type = 4
AND msi.bom_item_type = 4
)
OR base_item_flag <> -1
OR itm_type <> 4
)
AND ((bic.implementation_date IS NOT NULL)
OR (bic.implementation_date IS NULL
AND bic.change_notice = context_eco
AND ( bic.acd_type = 1 OR bic.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb
WHERE bcb.old_component_sequence_id = bic.component_sequence_id
AND bcb.change_notice = context_eco
AND bcb.acd_type = 3
AND bcb.effectivity_date <= x_effectivity_date
AND bcb.implementation_date IS NULL
AND bcb.bill_sequence_id = bic.bill_sequence_id
)
AND 'T' = bev.access_flag
AND 'T' =
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bic.component_item_id),
TO_CHAR (to_org_id),
bom_exploder_pub.get_ego_user
)
AND bic.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bic.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
)
AND ( ( p_cpy_past_eff_comps = 'Y' AND bev.effectivity_date <= rev_date) -- For first revised item we can have past eff comps as eff on the target date
OR ( p_cpy_past_eff_comps = 'N' AND bev.effectivity_date = rev_date )
)
AND EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
ego_criteria_v ecv,
mtl_system_items_b msibs -- to assembly item
WHERE ecv.customization_application_id = 702
AND ecv.region_application_id = 702
AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
AND ecv.customization_code = ectv.customization_code
AND flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
AND ecv.value_varchar2 = msi.item_type -- Component
UNION ALL
SELECT 1
FROM DUAL
WHERE NOT EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
mtl_system_items_b msibs -- to assembly item
WHERE flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
)
);
INSERT INTO bom_components_b
(shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
acd_type,
component_sequence_id,
old_component_sequence_id,
bill_sequence_id,
request_id,
program_application_id,
program_id,
program_update_date,
wip_supply_type,
pick_components,
supply_subinventory,
supply_locator_id,
operation_lead_time_percent,
revised_item_sequence_id,
cost_factor,
operation_seq_num,
component_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
item_num,
component_quantity,
component_yield_factor,
component_remarks,
effectivity_date,
change_notice,
implementation_date,
disable_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
planning_factor,
quantity_related,
so_basis,
optional,
mutually_exclusive_options,
include_in_cost_rollup,
check_atp,
bom_item_type,
from_end_item_unit_number,
to_end_item_unit_number,
optional_on_model,
--BUGFIX 2740820
parent_bill_seq_id, --BUGFIX 2740820
model_comp_seq_id, --BUGFIX 2740820
plan_level,
--BUGFIX 2740820
enforce_int_requirements, --BUGFIX 2991472
from_object_revision_id,
from_minor_revision_id,
pk1_value,
pk2_value,
auto_request_material,
-- Bug 3662214 : Added following 4 fields
suggested_vendor_name,
vendor_id,
unit_price,
from_end_item_rev_id,
to_end_item_rev_id,
from_end_item_minor_rev_id,
to_end_item_minor_rev_id,
component_item_revision_id,
component_minor_revision_id,
basis_type,
to_object_revision_id,
to_minor_revision_id
)
SELECT bic.shipping_allowed,
bic.required_to_ship,
bic.required_for_revenue,
bic.include_on_ship_docs,
bic.include_on_bill_docs,
--bic.low_quantity,
--bic.high_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.low_quantity,
DECODE(BIC.low_quantity,null,null, --Added this inner Deocde for Bug 6847530
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.low_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure))) Comp_low_qty,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.high_quantity,
DECODE(BIC.high_quantity,null,null, --Added this inner Deocde for Bug 6847530
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.high_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure))) Comp_high_qty,
x_acd_type,
bom_inventory_components_s.NEXTVAL,
DECODE (x_acd_type,
NULL, NULL,
bom_inventory_components_s.CURRVAL
),
to_sequence_id,
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate,
bic.wip_supply_type,
DECODE (rto_flag, 'Y', 2, bic.pick_components),
DECODE (x_from_org_id,
to_org_id, bic.supply_subinventory,
DECODE( l_default_wip_params, 1, msi.wip_supply_subinventory, NULL )
),
DECODE (x_from_org_id,
to_org_id, bic.supply_locator_id,
DECODE( l_default_wip_params, 1, msi.wip_supply_locator_id, NULL )
),
bic.operation_lead_time_percent,
x_rev_item_seq_id,
bic.cost_factor,
bic.operation_seq_num,
bic.component_item_id,
SYSDATE,
user_id,
SYSDATE,
bic.component_sequence_id,
/*NULL comment for bug8431772,change NULL to user_id*/user_id,
bic.item_num,
-- bic.component_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.component_quantity,
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.component_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure)) Comp_qty,
bic.component_yield_factor,
bic.component_remarks,
-- R12 TTM ENH
-- For Unit/Serial it eff date will be sysdate
sysdate AS effectivity_date,
x_e_change_notice,
-- Implementation date will be NULL for ECO flow and SYSDATE for inline copy
TO_DATE (NULL),
-- For Unit/Serial Eff disable date will be null
to_date(NULL) AS disable_date,
decode(l_prof_val, null, bic.attribute_category, l_prof_val), --changed for bug 10078346 --bic.attribute_category,
bic.attribute1,
bic.attribute2,
bic.attribute3,
bic.attribute4,
bic.attribute5,
bic.attribute6,
bic.attribute7,
bic.attribute8,
bic.attribute9,
bic.attribute10,
bic.attribute11,
bic.attribute12,
bic.attribute13,
bic.attribute14,
bic.attribute15,
bic.planning_factor,
bic.quantity_related,
bic.so_basis,
bic.optional,
bic.mutually_exclusive_options,
bic.include_in_cost_rollup,
bic.check_atp,
msi.bom_item_type,
x_unit_number,
CASE
WHEN bic.to_end_item_unit_number IS NOT NULL
AND bic.to_end_item_unit_number > x_unit_number
THEN bic.to_end_item_unit_number
ELSE
to_char(NULL)
END AS to_end_item_unit_number,
bic.optional_on_model,
--BUGFIX 2740820
bic.parent_bill_seq_id, --BUGFIX 2740820
bic.model_comp_seq_id,
--BUGFIX 2740820
bic.plan_level, --BUGFIX 2740820
bic.enforce_int_requirements,
-- Either Fixed or Floating rev, the components will be from when its created, current item rev
l_current_item_rev_id,
-- Minor rev is not supported. Populated the first minor rev
0,
bic.component_item_id,
to_org_id,
bic.auto_request_material,
-- Bug 3662214 : Added following 4 fields
bic.suggested_vendor_name,
bic.vendor_id,
bic.unit_price,
to_number(NULL) AS from_end_item_rev_id,
to_number(NULL) AS to_end_item_rev_id,
-- For Minor rev Ids
0 AS from_end_item_minor_rev_id,
0 AS to_end_item_minor_rev_id,
(
SELECT tmirb.revision_id
FROM mtl_item_revisions_b fmirb,
mtl_item_revisions_b tmirb
WHERE tmirb.inventory_item_id = bic.component_item_id
AND tmirb.organization_id = to_org_id
AND tmirb.revision = fmirb.revision
AND fmirb.revision_id = bic.component_item_revision_id
) AS component_item_revision_id,
CASE
WHEN bic.component_item_revision_id IS NULL
THEN to_number(NULL)
ELSE
-- Minor revision is not supported
0
END AS component_minor_revision_id,
bic.basis_type,
CASE
WHEN l_fixed_rev IS NOT NULL
-- For fixed rev copy the components as fixed rev
THEN l_to_item_rev_id
ELSE
to_number(NULL)
END AS to_object_revision_id,
CASE
WHEN l_fixed_rev IS NOT NULL
THEN 0
ELSE
to_number(NULL)
END AS to_minor_revision_id
FROM bom_components_b bic,
mtl_system_items msi,
MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
bom_copy_explosions_v bev
WHERE bic.bill_sequence_id = x_from_sequence_id
AND bic.component_item_id = msi.inventory_item_id
AND bic.component_item_id <> to_item_id
AND NVL (bic.eco_for_production, 2) = 2
AND msi.organization_id = to_org_id
AND MSI.inventory_item_id = AA.inventory_item_id -- Added corresponding to Bug 6510185
AND AA.organization_id = from_org_id -- Added corresponding to Bug 6510185
AND msi.bom_enabled_flag = 'Y' -- Added for the bug 14238677
AND ((direction = eng_to_bom
AND msi.eng_item_flag = 'N')
OR (direction <> eng_to_bom)
)
AND ((base_item_flag = -1
AND itm_type = 4
AND msi.bom_item_type = 4
)
OR base_item_flag <> -1
OR itm_type <> 4
)
AND ((bic.implementation_date IS NOT NULL)
OR (bic.implementation_date IS NULL
AND bic.change_notice = context_eco
AND ( bic.acd_type = 1 OR bic.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb
WHERE bcb.old_component_sequence_id = bic.component_sequence_id
AND bcb.change_notice = context_eco
AND bcb.acd_type = 3
AND bcb.effectivity_date <= x_effectivity_date
AND bcb.implementation_date IS NULL
AND bcb.bill_sequence_id = bic.bill_sequence_id
)
AND 'T' = bev.access_flag
AND 'T' =
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bic.component_item_id),
TO_CHAR (to_org_id),
bom_exploder_pub.get_ego_user
)
AND bic.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bic.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
)
AND ( ( p_cpy_past_eff_comps = 'Y' AND bev.from_end_item_unit_number <= unit_number) -- For first revised item we can have past eff comps as eff on the target date
OR ( p_cpy_past_eff_comps = 'N' AND bev.from_end_item_unit_number = unit_number )
)
AND EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
ego_criteria_v ecv,
mtl_system_items_b msibs -- to assembly item
WHERE ecv.customization_application_id = 702
AND ecv.region_application_id = 702
AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
AND ecv.customization_code = ectv.customization_code
AND flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
AND ecv.value_varchar2 = msi.item_type -- Component
UNION ALL
SELECT 1
FROM DUAL
WHERE NOT EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
mtl_system_items_b msibs -- to assembly item
WHERE flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
)
);
INSERT INTO bom_components_b
(shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
acd_type,
component_sequence_id,
old_component_sequence_id,
bill_sequence_id,
request_id,
program_application_id,
program_id,
program_update_date,
wip_supply_type,
pick_components,
supply_subinventory,
supply_locator_id,
operation_lead_time_percent,
revised_item_sequence_id,
cost_factor,
operation_seq_num,
component_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
item_num,
component_quantity,
component_yield_factor,
component_remarks,
effectivity_date,
change_notice,
implementation_date,
disable_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
planning_factor,
quantity_related,
so_basis,
optional,
mutually_exclusive_options,
include_in_cost_rollup,
check_atp,
bom_item_type,
from_end_item_unit_number,
to_end_item_unit_number,
optional_on_model,
--BUGFIX 2740820
parent_bill_seq_id, --BUGFIX 2740820
model_comp_seq_id, --BUGFIX 2740820
plan_level,
--BUGFIX 2740820
enforce_int_requirements, --BUGFIX 2991472
from_object_revision_id,
from_minor_revision_id,
pk1_value,
pk2_value,
auto_request_material,
-- Bug 3662214 : Added following 4 fields
suggested_vendor_name,
vendor_id,
unit_price,
from_end_item_rev_id,
to_end_item_rev_id,
from_end_item_minor_rev_id,
to_end_item_minor_rev_id,
component_item_revision_id,
component_minor_revision_id,
basis_type,
to_object_revision_id,
to_minor_revision_id
)
SELECT bic.shipping_allowed,
bic.required_to_ship,
bic.required_for_revenue,
bic.include_on_ship_docs,
bic.include_on_bill_docs,
--bic.low_quantity,
--bic.high_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.low_quantity,
DECODE(BIC.low_quantity,null,null, --Added this inner Deocde for Bug 6847530
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.low_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure))) Comp_low_qty,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.high_quantity,
DECODE(BIC.high_quantity,null,null, --Added this inner Deocde for Bug 6847530
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.high_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure))) Comp_high_qty,
x_acd_type,
bom_inventory_components_s.NEXTVAL,
DECODE (x_acd_type,
NULL, NULL,
bom_inventory_components_s.CURRVAL
),
to_sequence_id,
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate,
bic.wip_supply_type,
DECODE (rto_flag, 'Y', 2, bic.pick_components),
DECODE (x_from_org_id,
to_org_id, bic.supply_subinventory,
DECODE( l_default_wip_params, 1, msi.wip_supply_subinventory, NULL )
),
DECODE (x_from_org_id,
to_org_id, bic.supply_locator_id,
DECODE( l_default_wip_params, 1, msi.wip_supply_locator_id, NULL )
),
bic.operation_lead_time_percent,
x_rev_item_seq_id,
bic.cost_factor,
bic.operation_seq_num,
bic.component_item_id,
SYSDATE,
user_id,
SYSDATE,
bic.component_sequence_id,
/*NULL comment for bug8431772,change NULL to user_id*/user_id,
bic.item_num,
--bic.component_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.component_quantity,
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.component_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure)) Comp_qty,
bic.component_yield_factor,
bic.component_remarks,
-- R12 TTM ENH
-- For Rev Eff Structure the eff date will be sysdate
sysdate AS effectivity_date,
x_e_change_notice,
-- Implementation date will be NULL for ECO flow and SYSDATE for inline copy
TO_DATE (NULL),
-- For Rev Eff structure the disable date will be null
to_date(NULL) AS disable_date,
decode(l_prof_val, null, bic.attribute_category, l_prof_val), --changed for bug 10078346 --bic.attribute_category,
bic.attribute1,
bic.attribute2,
bic.attribute3,
bic.attribute4,
bic.attribute5,
bic.attribute6,
bic.attribute7,
bic.attribute8,
bic.attribute9,
bic.attribute10,
bic.attribute11,
bic.attribute12,
bic.attribute13,
bic.attribute14,
bic.attribute15,
bic.planning_factor,
bic.quantity_related,
bic.so_basis,
bic.optional,
bic.mutually_exclusive_options,
bic.include_in_cost_rollup,
--DECODE(atp_comp_flag, 'Y', CHECK_ATP, 2), fixed bug 2249375
bic.check_atp,
msi.bom_item_type,
to_char(NULL) AS from_end_item_unit_number,
to_char(NULL) AS to_end_item_unit_number,
bic.optional_on_model,
--BUGFIX 2740820
bic.parent_bill_seq_id, --BUGFIX 2740820
bic.model_comp_seq_id,
--BUGFIX 2740820
bic.plan_level, --BUGFIX 2740820
bic.enforce_int_requirements,
-- Either Fixed or Floating rev, the components will be from when its created, current item rev
l_current_item_rev_id,
-- Minor rev is not supported. Populated the first minor rev
0,
bic.component_item_id,
to_org_id,
bic.auto_request_material,
-- Bug 3662214 : Added following 4 fields
bic.suggested_vendor_name,
bic.vendor_id,
bic.unit_price,
x_end_item_rev_id,
-- This release we are not supporting transformation -- 14 Mar 2005
-- This case is to handle the revised item creation from TTM flow
-- This is the first case
CASE
WHEN bic.to_end_item_rev_id IS NOT NULL
AND (
EXISTS
(
SELECT tmirb.revision_id
FROM mtl_item_revisions_b fmirb,
mtl_item_revisions_b tmirb
WHERE tmirb.inventory_item_id = to_item_id
AND tmirb.organization_id = to_org_id
AND tmirb.revision = fmirb.revision
AND fmirb.revision_id = bic.to_end_item_rev_id
)
AND
(
SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE REVISION_ID = bic.to_end_item_rev_id
) >
(
SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE REVISION_ID = x_end_item_rev_id
)
)
THEN
(
SELECT tmirb.revision_id
FROM mtl_item_revisions_b fmirb,
mtl_item_revisions_b tmirb
WHERE tmirb.inventory_item_id = to_item_id
AND tmirb.organization_id = to_org_id
AND tmirb.revision = fmirb.revision
AND fmirb.revision_id = bic.to_end_item_rev_id
)
ELSE
to_number(NULL)
END AS to_end_item_rev_id,
-- For Minor rev Ids
0 AS from_end_item_minor_rev_id,
0 AS to_end_item_minor_rev_id,
(
SELECT tmirb.revision_id
FROM mtl_item_revisions_b fmirb,
mtl_item_revisions_b tmirb
WHERE tmirb.inventory_item_id = bic.component_item_id
AND tmirb.organization_id = to_org_id
AND tmirb.revision = fmirb.revision
AND fmirb.revision_id = bic.component_item_revision_id
) AS component_item_revision_id,
CASE
WHEN bic.component_item_revision_id IS NULL
THEN to_number(NULL)
ELSE
-- Minor revision is not supported
0
END AS component_minor_revision_id,
bic.basis_type,
CASE
WHEN l_fixed_rev IS NOT NULL
-- For fixed rev copy the components as fixed rev
THEN l_to_item_rev_id
ELSE
to_number(NULL)
END AS to_object_revision_id,
CASE
WHEN l_fixed_rev IS NOT NULL
THEN 0
ELSE
to_number(NULL)
END AS to_minor_revision_id
FROM bom_components_b bic,
mtl_system_items msi,
MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
bom_copy_explosions_v bev
WHERE bic.bill_sequence_id = x_from_sequence_id
AND bic.component_item_id = msi.inventory_item_id
AND bic.component_item_id <> to_item_id
AND NVL (bic.eco_for_production, 2) = 2
AND msi.organization_id = to_org_id
AND MSI.inventory_item_id = AA.inventory_item_id -- Added corresponding to Bug 6510185
AND AA.organization_id = from_org_id -- Added corresponding to Bug 6510185
AND msi.bom_enabled_flag = 'Y' -- Added for the bug 14238677
AND ((direction = eng_to_bom
AND msi.eng_item_flag = 'N')
OR (direction <> eng_to_bom)
)
AND ((base_item_flag = -1
AND itm_type = 4
AND msi.bom_item_type = 4
)
OR base_item_flag <> -1
OR itm_type <> 4
)
AND ((bic.implementation_date IS NOT NULL)
OR (bic.implementation_date IS NULL
AND bic.change_notice = context_eco
AND ( bic.acd_type = 1 OR bic.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb
WHERE bcb.old_component_sequence_id = bic.component_sequence_id
AND bcb.change_notice = context_eco
AND bcb.acd_type = 3
AND bcb.effectivity_date <= x_effectivity_date
AND bcb.implementation_date IS NULL
AND bcb.bill_sequence_id = bic.bill_sequence_id
)
AND 'T' = bev.access_flag
AND 'T' =
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bic.component_item_id),
TO_CHAR (to_org_id),
bom_exploder_pub.get_ego_user
)
AND bic.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bic.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
)
AND ( p_cpy_past_eff_comps = 'Y' AND ( ( SELECT mirb.revision
FROM mtl_item_revisions_b mirb
WHERE mirb.revision_id = bev.from_end_item_rev_id
)
<=
(
SELECT mirb.revision
FROM mtl_item_revisions_b mirb
WHERE mirb.revision_id = p_end_item_rev_id
)
) -- For first revised item we can have past eff comps as eff on the target date
OR ( p_cpy_past_eff_comps = 'N' AND bev.from_end_item_rev_id = p_end_item_rev_id )
)
AND EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
ego_criteria_v ecv,
mtl_system_items_b msibs -- to assembly item
WHERE ecv.customization_application_id = 702
AND ecv.region_application_id = 702
AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
AND ecv.customization_code = ectv.customization_code
AND flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
AND ecv.value_varchar2 = msi.item_type -- Component
UNION ALL
SELECT 1
FROM DUAL
WHERE NOT EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
mtl_system_items_b msibs -- to assembly item
WHERE flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
)
);
INSERT INTO bom_components_b
(shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
acd_type,
component_sequence_id,
old_component_sequence_id,
bill_sequence_id,
request_id,
program_application_id,
program_id,
program_update_date,
wip_supply_type,
pick_components,
supply_subinventory,
supply_locator_id,
operation_lead_time_percent,
revised_item_sequence_id,
cost_factor,
operation_seq_num,
component_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
item_num,
component_quantity,
component_yield_factor,
component_remarks,
effectivity_date,
change_notice,
implementation_date,
disable_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
planning_factor,
quantity_related,
so_basis,
optional,
mutually_exclusive_options,
include_in_cost_rollup,
check_atp,
bom_item_type,
from_end_item_unit_number,
to_end_item_unit_number,
optional_on_model,
--BUGFIX 2740820
parent_bill_seq_id, --BUGFIX 2740820
model_comp_seq_id, --BUGFIX 2740820
plan_level,
--BUGFIX 2740820
enforce_int_requirements, --BUGFIX 2991472
from_object_revision_id,
from_minor_revision_id,
pk1_value,
pk2_value,
auto_request_material,
-- Bug 3662214 : Added following 4 fields
suggested_vendor_name,
vendor_id,
unit_price,
from_end_item_rev_id,
to_end_item_rev_id,
from_end_item_minor_rev_id,
to_end_item_minor_rev_id,
component_item_revision_id,
component_minor_revision_id,
basis_type,
to_object_revision_id,
to_minor_revision_id
)
SELECT bic.shipping_allowed,
bic.required_to_ship,
bic.required_for_revenue,
bic.include_on_ship_docs,
bic.include_on_bill_docs,
--bic.low_quantity,
--bic.high_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.low_quantity,
DECODE(BIC.low_quantity,null,null, --Added this inner Deocde for Bug 6847530
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.low_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure))) Comp_low_qty,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.high_quantity,
DECODE(BIC.high_quantity,null,null, --Added this inner Deocde for Bug 6847530
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.high_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure))) Comp_high_qty,
x_acd_type,
bom_inventory_components_s.NEXTVAL,
DECODE (x_acd_type,
NULL, NULL,
bom_inventory_components_s.CURRVAL
),
to_sequence_id,
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate,
bic.wip_supply_type,
DECODE (rto_flag, 'Y', 2, bic.pick_components),
DECODE (x_from_org_id,
to_org_id, bic.supply_subinventory,
DECODE( l_default_wip_params, 1, msi.wip_supply_subinventory, NULL )
),
DECODE (x_from_org_id,
to_org_id, bic.supply_locator_id,
DECODE( l_default_wip_params, 1, msi.wip_supply_locator_id, NULL )
),
bic.operation_lead_time_percent,
x_rev_item_seq_id,
bic.cost_factor,
bic.operation_seq_num,
bic.component_item_id,
SYSDATE,
user_id,
SYSDATE,
bic.component_sequence_id,
/*NULL comment for bug8431772,change NULL to user_id*/user_id,
bic.item_num,
--bic.component_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.component_quantity,
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.component_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure)) Comp_qty,
bic.component_yield_factor,
bic.component_remarks,
x_effectivity_date,
x_e_change_notice,
-- Implementation date will be NULL for ECO flow and SYSDATE for inline copy
TO_DATE (NULL),
CASE
WHEN bic.to_end_item_rev_id IS NULL
THEN to_date(NULL)
-- This flag will be set when current and future option is selected with
-- copy through ECO
WHEN bic.to_end_item_rev_id IS NOT NULL
AND (
(
SELECT fmirb.effectivity_date
FROM mtl_item_revisions_b fmirb
WHERE fmirb.revision_id = bic.to_end_item_rev_id
) > x_effectivity_date
)
THEN (
SELECT fmirb.effectivity_date
FROM mtl_item_revisions_b fmirb
WHERE fmirb.revision_id = bic.to_end_item_rev_id
)
-- Past disabled components will be copied with disable date as null
WHEN (
( SELECT fmirb.effectivity_date
FROM mtl_item_revisions_b fmirb
WHERE fmirb.revision_id = bic.to_end_item_rev_id
) < x_effectivity_date
)
THEN TO_DATE (NULL)
-- Past disabled components will be copied with disable date as null
ELSE
-- Future disabled components should be disabled as per the disable date of component
bic.disable_date
END AS disable_date,
decode(l_prof_val, null, bic.attribute_category, l_prof_val), --changed for bug 10078346 --bic.attribute_category,
bic.attribute1,
bic.attribute2,
bic.attribute3,
bic.attribute4,
bic.attribute5,
bic.attribute6,
bic.attribute7,
bic.attribute8,
bic.attribute9,
bic.attribute10,
bic.attribute11,
bic.attribute12,
bic.attribute13,
bic.attribute14,
bic.attribute15,
bic.planning_factor,
bic.quantity_related,
bic.so_basis,
bic.optional,
bic.mutually_exclusive_options,
bic.include_in_cost_rollup,
bic.check_atp,
msi.bom_item_type,
to_char(NULL) AS from_end_item_unit_number, -- Date Eff Bill will not have from_end_item_unit_numbers
to_char(NULL) AS to_end_item_unit_number, -- Date Eff Bill will not have to_end_item_unit_numbers
bic.optional_on_model,
--BUGFIX 2740820
bic.parent_bill_seq_id, --BUGFIX 2740820
bic.model_comp_seq_id,
--BUGFIX 2740820
bic.plan_level, --BUGFIX 2740820
bic.enforce_int_requirements,
-- Either Fixed or Floating rev, the components will be from when its created, current item rev
l_current_item_rev_id,
-- Minor rev is not supported. Populated the first minor rev
0,
bic.component_item_id,
to_org_id,
bic.auto_request_material,
-- Bug 3662214 : Added following 4 fields
bic.suggested_vendor_name,
bic.vendor_id,
bic.unit_price,
to_number(NULL) AS from_end_item_rev_id, -- From End Item Rev Ids won't be set for Date Eff Bill
to_number(NULL) AS to_end_item_rev_id, -- To End Item Rev Ids won't be set for Date Eff Bill
-- For Minor rev Ids
0 AS from_end_item_minor_rev_id,
0 AS to_end_item_minor_rev_id,
(
SELECT tmirb.revision_id
FROM mtl_item_revisions_b fmirb,
mtl_item_revisions_b tmirb
WHERE tmirb.inventory_item_id = bic.component_item_id
AND tmirb.organization_id = to_org_id
AND tmirb.revision = fmirb.revision
AND fmirb.revision_id = bic.component_item_revision_id
) AS component_item_revision_id,
CASE
WHEN bic.component_item_revision_id IS NULL
THEN to_number(NULL)
ELSE
-- Minor revision is not supported
0
END AS component_minor_revision_id,
bic.basis_type,
CASE
WHEN l_fixed_rev IS NOT NULL
-- For fixed rev copy the components as fixed rev
THEN l_to_item_rev_id
ELSE
to_number(NULL)
END AS to_object_revision_id,
CASE
WHEN l_fixed_rev IS NOT NULL
THEN 0
ELSE
to_number(NULL)
END AS to_minor_revision_id
FROM bom_components_b bic,
mtl_system_items msi,
MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
bom_copy_explosions_v bev
WHERE bic.bill_sequence_id = x_from_sequence_id
AND bic.component_item_id = msi.inventory_item_id
AND bic.component_item_id <> to_item_id
AND NVL (bic.eco_for_production, 2) = 2
AND msi.organization_id = to_org_id
AND MSI.inventory_item_id = AA.inventory_item_id -- Added corresponding to Bug 6510185
AND AA.organization_id = from_org_id -- Added corresponding to Bug 6510185
AND msi.bom_enabled_flag = 'Y' -- Added for the bug 14238677
AND ((direction = eng_to_bom
AND msi.eng_item_flag = 'N')
OR (direction <> eng_to_bom)
)
AND ((base_item_flag = -1
AND itm_type = 4
AND msi.bom_item_type = 4
)
OR base_item_flag <> -1
OR itm_type <> 4
)
AND ((bic.implementation_date IS NOT NULL)
OR (bic.implementation_date IS NULL
AND bic.change_notice = context_eco
AND ( bic.acd_type = 1 OR bic.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb
WHERE bcb.old_component_sequence_id = bic.component_sequence_id
AND bcb.change_notice = context_eco
AND bcb.acd_type = 3
AND bcb.effectivity_date <= x_effectivity_date
AND bcb.implementation_date IS NULL
AND bcb.bill_sequence_id = bic.bill_sequence_id
)
AND 'T' = bev.access_flag
AND 'T' =
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bic.component_item_id),
TO_CHAR (to_org_id),
bom_exploder_pub.get_ego_user
)
AND bic.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bic.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
)
AND ( ( p_cpy_past_eff_comps = 'Y' AND ( SELECT mirb.effectivity_date
FROM mtl_item_revisions_b mirb
WHERE mirb.revision_id = bev.from_end_item_rev_id
) <= ( SELECT mirb.effectivity_date
FROM mtl_item_revisions_b mirb
WHERE mirb.revision_id = p_end_item_rev_id
)) -- For first revised item we can have past eff comps as eff on the target date
OR ( p_cpy_past_eff_comps = 'N' AND ( SELECT mirb.effectivity_date
FROM mtl_item_revisions_b mirb
WHERE mirb.revision_id = bev.from_end_item_rev_id
) = ( SELECT mirb.effectivity_date
FROM mtl_item_revisions_b mirb
WHERE mirb.revision_id = p_end_item_rev_id
) )
)
AND EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
ego_criteria_v ecv,
mtl_system_items_b msibs -- to assembly item
WHERE ecv.customization_application_id = 702
AND ecv.region_application_id = 702
AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
AND ecv.customization_code = ectv.customization_code
AND flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
AND ecv.value_varchar2 = msi.item_type -- Component
UNION ALL
SELECT 1
FROM DUAL
WHERE NOT EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
mtl_system_items_b msibs -- to assembly item
WHERE flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
)
);
INSERT INTO bom_components_b
(shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
include_on_bill_docs,
low_quantity,
high_quantity,
acd_type,
component_sequence_id,
old_component_sequence_id,
bill_sequence_id,
request_id,
program_application_id,
program_id,
program_update_date,
wip_supply_type,
pick_components,
supply_subinventory,
supply_locator_id,
operation_lead_time_percent,
revised_item_sequence_id,
cost_factor,
operation_seq_num,
component_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
item_num,
component_quantity,
component_yield_factor,
component_remarks,
effectivity_date,
change_notice,
implementation_date,
disable_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
planning_factor,
quantity_related,
so_basis,
optional,
mutually_exclusive_options,
include_in_cost_rollup,
check_atp,
bom_item_type,
from_end_item_unit_number,
to_end_item_unit_number,
optional_on_model,
--BUGFIX 2740820
parent_bill_seq_id, --BUGFIX 2740820
model_comp_seq_id, --BUGFIX 2740820
plan_level,
--BUGFIX 2740820
enforce_int_requirements, --BUGFIX 2991472
from_object_revision_id,
from_minor_revision_id,
pk1_value,
pk2_value,
auto_request_material,
-- Bug 3662214 : Added following 4 fields
suggested_vendor_name,
vendor_id,
unit_price,
from_end_item_rev_id,
to_end_item_rev_id,
from_end_item_minor_rev_id,
to_end_item_minor_rev_id,
component_item_revision_id,
component_minor_revision_id,
basis_type,
to_object_revision_id,
to_minor_revision_id
)
SELECT bic.shipping_allowed,
bic.required_to_ship,
bic.required_for_revenue,
bic.include_on_ship_docs,
bic.include_on_bill_docs,
-- bic.low_quantity,
-- bic.high_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.low_quantity,
DECODE(BIC.low_quantity,null,null, --Added this inner Deocde for Bug 6847530
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.low_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure))) Comp_low_qty,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.high_quantity,
DECODE(BIC.high_quantity,null,null, --Added this inner Deocde for Bug 6847530
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.high_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure))) Comp_high_qty,
x_acd_type,
bom_inventory_components_s.NEXTVAL,
DECODE (x_acd_type,
NULL, NULL,
bom_inventory_components_s.CURRVAL
),
to_sequence_id,
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate,
bic.wip_supply_type,
DECODE (rto_flag, 'Y', 2, bic.pick_components),
DECODE (x_from_org_id,
to_org_id, bic.supply_subinventory,
DECODE( l_default_wip_params, 1, msi.wip_supply_subinventory, NULL )
),
DECODE (x_from_org_id,
to_org_id, bic.supply_locator_id,
DECODE( l_default_wip_params, 1, msi.wip_supply_locator_id, NULL )
),
bic.operation_lead_time_percent,
x_rev_item_seq_id,
bic.cost_factor,
bic.operation_seq_num,
bic.component_item_id,
SYSDATE,
user_id,
SYSDATE,
bic.component_sequence_id,
/*NULL comment for bug8431772,change NULL to user_id*/user_id,
bic.item_num,
--bic.component_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,BIC.component_quantity,
inv_convert.INV_UM_CONVERT(BIC.component_item_id,
NULL,
BIC.component_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure)) Comp_qty,
bic.component_yield_factor,
bic.component_remarks,
-- R12 TTM ENH
-- For Rev Eff Structure the eff date will be sysdate
sysdate AS effectivity_date,
x_e_change_notice,
-- Implementation date will be NULL for ECO flow
TO_DATE (NULL),
-- For Rev Eff structure the disable date will be null
to_date(NULL) AS disable_date,
decode(l_prof_val, null, bic.attribute_category, l_prof_val), --changed for bug 10078346 --bic.attribute_category,
bic.attribute1,
bic.attribute2,
bic.attribute3,
bic.attribute4,
bic.attribute5,
bic.attribute6,
bic.attribute7,
bic.attribute8,
bic.attribute9,
bic.attribute10,
bic.attribute11,
bic.attribute12,
bic.attribute13,
bic.attribute14,
bic.attribute15,
bic.planning_factor,
bic.quantity_related,
bic.so_basis,
bic.optional,
bic.mutually_exclusive_options,
bic.include_in_cost_rollup,
bic.check_atp,
msi.bom_item_type,
to_char(NULL) AS from_end_item_unit_number,
to_char(NULL) AS to_end_item_unit_number,
bic.optional_on_model,
--BUGFIX 2740820
bic.parent_bill_seq_id, --BUGFIX 2740820
bic.model_comp_seq_id,
--BUGFIX 2740820
bic.plan_level, --BUGFIX 2740820
bic.enforce_int_requirements,
-- Either Fixed or Floating rev, the components will be from when its created, current item rev
l_current_item_rev_id,
-- Minor rev is not supported. Populated the first minor rev
0,
bic.component_item_id,
to_org_id,
bic.auto_request_material,
-- Bug 3662214 : Added following 4 fields
bic.suggested_vendor_name,
bic.vendor_id,
bic.unit_price,
eco_end_item_rev_id,
CASE
WHEN bic.disable_date IS NOT NULL
AND EXISTS
(
SELECT tmirb.revision_id
FROM mtl_item_revisions_b tmirb
WHERE tmirb.inventory_item_id = to_item_id
AND tmirb.organization_id = to_org_id
AND tmirb.revision = get_current_item_rev(from_item_id, from_org_id, bic.disable_date)
)
AND EXISTS
(
SELECT mirb.REVISION
FROM MTL_ITEM_REVISIONS_B mirb
WHERE mirb.REVISION_ID = eco_end_item_rev_id
AND mirb.revision < get_current_item_rev(from_item_id, from_org_id, bic.disable_date)
)
THEN
(
SELECT tmirb.revision_id
FROM mtl_item_revisions_b tmirb
WHERE tmirb.inventory_item_id = to_item_id
AND tmirb.organization_id = to_org_id
AND tmirb.revision = get_current_item_rev(from_item_id, from_org_id, bic.disable_date)
)
ELSE
to_number(NULL)
END AS to_end_item_rev_id,
-- For Minor rev Ids
0 AS from_end_item_minor_rev_id,
0 AS to_end_item_minor_rev_id,
(
SELECT tmirb.revision_id
FROM mtl_item_revisions_b fmirb,
mtl_item_revisions_b tmirb
WHERE tmirb.inventory_item_id = bic.component_item_id
AND tmirb.organization_id = to_org_id
AND tmirb.revision = fmirb.revision
AND fmirb.revision_id = bic.component_item_revision_id
) AS component_item_revision_id,
CASE
WHEN bic.component_item_revision_id IS NULL
THEN to_number(NULL)
ELSE
-- Minor revision is not supported
0
END AS component_minor_revision_id,
bic.basis_type,
CASE
WHEN l_fixed_rev IS NOT NULL
-- For fixed rev copy the components as fixed rev
THEN l_to_item_rev_id
ELSE
to_number(NULL)
END AS to_object_revision_id,
CASE
WHEN l_fixed_rev IS NOT NULL
THEN 0
ELSE
to_number(NULL)
END AS to_minor_revision_id
FROM bom_components_b bic,
mtl_system_items msi,
MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
bom_copy_explosions_v bev
WHERE bic.bill_sequence_id = x_from_sequence_id
AND bic.component_item_id = msi.inventory_item_id
AND bic.component_item_id <> to_item_id
AND NVL (bic.eco_for_production, 2) = 2
AND msi.organization_id = to_org_id
AND MSI.inventory_item_id = AA.inventory_item_id -- Added corresponding to Bug 6510185
AND AA.organization_id = from_org_id -- Added corresponding to Bug 6510185
AND msi.bom_enabled_flag = 'Y' -- Added for the bug 14238677
AND ((direction = eng_to_bom
AND msi.eng_item_flag = 'N')
OR (direction <> eng_to_bom)
)
AND ((base_item_flag = -1
AND itm_type = 4
AND msi.bom_item_type = 4
)
OR base_item_flag <> -1
OR itm_type <> 4
)
AND ((bic.implementation_date IS NOT NULL)
OR (bic.implementation_date IS NULL
AND bic.change_notice = context_eco
AND ( bic.acd_type = 1 OR bic.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb
WHERE bcb.old_component_sequence_id = bic.component_sequence_id
AND bcb.change_notice = context_eco
AND bcb.acd_type = 3
AND bcb.effectivity_date <= x_effectivity_date
AND bcb.implementation_date IS NULL
AND bcb.bill_sequence_id = bic.bill_sequence_id
)
AND 'T' = bev.access_flag
AND 'T' =
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bic.component_item_id),
TO_CHAR (to_org_id),
bom_exploder_pub.get_ego_user
)
AND bic.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bic.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
)
AND ( ( p_cpy_past_eff_comps = 'Y' AND bev.effectivity_date <= rev_date) -- For first revised item we can have past eff comps as eff on the target date
OR ( p_cpy_past_eff_comps = 'N' AND bev.effectivity_date = rev_date )
)
AND EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
ego_criteria_v ecv,
mtl_system_items_b msibs -- to assembly item
WHERE ecv.customization_application_id = 702
AND ecv.region_application_id = 702
AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
AND ecv.customization_code = ectv.customization_code
AND flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
AND ecv.value_varchar2 = msi.item_type -- Component
UNION ALL
SELECT 1
FROM DUAL
WHERE NOT EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
mtl_system_items_b msibs -- to assembly item
WHERE flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
)
);
SELECT COUNT (*)
INTO l_no_access_comp_cnt
FROM bom_components_b bcb,
mtl_system_items_b_kfv msbk1,
bom_copy_explosions_v bev
WHERE bcb.bill_sequence_id = x_from_sequence_id
AND bcb.component_item_id = msbk1.inventory_item_id
AND bcb.component_item_id <> to_item_id
AND 'T' <>
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bcb.component_item_id),
TO_CHAR (from_org_id),
bom_exploder_pub.get_ego_user
)
AND msbk1.organization_id = from_org_id
AND bcb.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND ((bcb.implementation_date IS NOT NULL)
OR (bcb.implementation_date IS NULL
AND bcb.change_notice = context_eco
AND ( bcb.acd_type = 1 OR bcb.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb1
WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
AND bcb1.change_notice = context_eco
AND bcb1.acd_type = 3
AND bcb1.effectivity_date <= x_effectivity_date
AND bcb1.implementation_date IS NULL
AND bcb1.bill_sequence_id = bcb.bill_sequence_id
)
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bcb.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
)
AND ( ( l_from_eff_ctrl = 1
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.effectivity_date <= rev_date) -- For first revised item we can have past eff comps as eff on the target date
OR ( p_cpy_past_eff_comps = 'N' AND bcb.effectivity_date = rev_date ) )
) OR
( ( l_from_eff_ctrl = 2 OR l_from_eff_ctrl = 3 )
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.from_end_item_unit_number <= unit_number)
OR ( p_cpy_past_eff_comps = 'N' AND bcb.from_end_item_unit_number = unit_number ) )
) OR
( l_from_eff_ctrl = 4
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.from_end_item_rev_id <= p_end_item_rev_id)
OR ( p_cpy_past_eff_comps = 'N' AND bcb.from_end_item_rev_id = p_end_item_rev_id ) )
)
);
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT from_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (from_item_id,
from_org_id,
rev_date
),
get_cnt_message ('BOM_COPY_ERR_COMP_NO_ACCESS',
msbk1.concatenated_segments,
TO_NUMBER (l_no_access_comp_cnt)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
mtl_system_items_b_kfv msbk1,
bom_copy_explosions_v bev
WHERE msbk1.inventory_item_id = from_item_id
AND msbk1.organization_id = from_org_id
AND bcb.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND ( ( l_from_eff_ctrl = 1
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.effectivity_date <= rev_date) -- For first revised item we can have past eff comps as eff on the target date
OR ( p_cpy_past_eff_comps = 'N' AND bcb.effectivity_date = rev_date ) )
) OR
( ( l_from_eff_ctrl = 2 OR l_from_eff_ctrl = 3 )
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.from_end_item_unit_number <= unit_number)
OR ( p_cpy_past_eff_comps = 'N' AND bcb.from_end_item_unit_number = unit_number ) )
) OR
( l_from_eff_ctrl = 4
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.from_end_item_rev_id <= p_end_item_rev_id)
OR ( p_cpy_past_eff_comps = 'N' AND bcb.from_end_item_rev_id = p_end_item_rev_id ) )
)
);
SELECT COUNT (*)
INTO l_no_access_comp_cnt
FROM bom_components_b bcb,
mtl_system_items_b_kfv msbk1,
bom_copy_explosions_v bev
WHERE bcb.bill_sequence_id = x_from_sequence_id
AND bcb.component_item_id = msbk1.inventory_item_id
AND bcb.component_item_id <> to_item_id
AND 'T' <>
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bcb.component_item_id),
TO_CHAR (to_org_id),
bom_exploder_pub.get_ego_user
)
AND msbk1.organization_id = from_org_id
AND bcb.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND ((bcb.implementation_date IS NOT NULL)
OR (bcb.implementation_date IS NULL
AND bcb.change_notice = context_eco
AND ( bcb.acd_type = 1 OR bcb.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb1
WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
AND bcb1.change_notice = context_eco
AND bcb1.acd_type = 3
AND bcb1.effectivity_date <= x_effectivity_date
AND bcb1.implementation_date IS NULL
AND bcb1.bill_sequence_id = bcb.bill_sequence_id
)
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bcb.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
)
AND ( ( l_from_eff_ctrl = 1
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.effectivity_date <= rev_date) -- For first revised item we can have past eff comps as eff on the target date
OR ( p_cpy_past_eff_comps = 'N' AND bcb.effectivity_date = rev_date ) )
) OR
( ( l_from_eff_ctrl = 2 OR l_from_eff_ctrl = 3 )
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.from_end_item_unit_number <= unit_number)
OR ( p_cpy_past_eff_comps = 'N' AND bcb.from_end_item_unit_number = unit_number ) )
) OR
( l_from_eff_ctrl = 4
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.from_end_item_rev_id <= p_end_item_rev_id)
OR ( p_cpy_past_eff_comps = 'N' AND bcb.from_end_item_rev_id = p_end_item_rev_id ) )
)
);
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT from_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (from_item_id,
from_org_id,
rev_date
),
get_cnt_message ('BOM_COPY_ERR_CMPDEST_NO_ACCESS',
msbk1.concatenated_segments,
TO_NUMBER (l_no_access_comp_cnt)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
mtl_system_items_b_kfv msbk1,
bom_copy_explosions_v bev
WHERE msbk1.inventory_item_id = from_item_id
AND msbk1.organization_id = from_org_id
AND bcb.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND ( ( l_from_eff_ctrl = 1
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.effectivity_date <= rev_date) -- For first revised item we can have past eff comps as eff on the target date
OR ( p_cpy_past_eff_comps = 'N' AND bcb.effectivity_date = rev_date ) )
) OR
( ( l_from_eff_ctrl = 2 OR l_from_eff_ctrl = 3 )
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.from_end_item_unit_number <= unit_number)
OR ( p_cpy_past_eff_comps = 'N' AND bcb.from_end_item_unit_number = unit_number ) )
) OR
( l_from_eff_ctrl = 4
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.from_end_item_rev_id <= p_end_item_rev_id)
OR ( p_cpy_past_eff_comps = 'N' AND bcb.from_end_item_rev_id = p_end_item_rev_id ) )
)
);
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
GET_MESSAGE ('BOM_COPY_ERR_ENG_COMP_MFG_BILL',
bom_globals.get_item_name(bcb.component_item_id, from_org_id),
bom_globals.get_item_name(to_item_id, from_org_id)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
mtl_system_items_b msib1, -- component
bom_copy_explosions_v bev
WHERE bcb.bill_sequence_id = x_from_sequence_id
AND bcb.component_item_id = msib1.inventory_item_id
AND bcb.component_item_id <> to_item_id
AND msib1.organization_id = to_org_id
AND (direction = eng_to_bom
AND msib1.eng_item_flag = 'Y')
AND bcb.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND ((bcb.implementation_date IS NOT NULL)
OR (bcb.implementation_date IS NULL
AND bcb.change_notice = context_eco
AND ( bcb.acd_type = 1 OR bcb.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb1
WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
AND bcb1.change_notice = context_eco
AND bcb1.acd_type = 3
AND bcb1.effectivity_date <= x_effectivity_date
AND bcb1.implementation_date IS NULL
AND bcb1.bill_sequence_id = bcb.bill_sequence_id
)
AND 'T' = bev.access_flag
AND 'T' =
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bcb.component_item_id),
TO_CHAR (to_org_id),
bom_exploder_pub.get_ego_user
)
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bcb.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
)
AND ( ( l_from_eff_ctrl = 1
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.effectivity_date <= rev_date) -- For first revised item we can have past eff comps as eff on the target date
OR ( p_cpy_past_eff_comps = 'N' AND bcb.effectivity_date = rev_date ) )
) OR
( ( l_from_eff_ctrl = 2 OR l_from_eff_ctrl = 3 )
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.from_end_item_unit_number <= unit_number)
OR ( p_cpy_past_eff_comps = 'N' AND bcb.from_end_item_unit_number = unit_number ) )
) OR
( l_from_eff_ctrl = 4
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.from_end_item_rev_id <= p_end_item_rev_id)
OR ( p_cpy_past_eff_comps = 'N' AND bcb.from_end_item_rev_id = p_end_item_rev_id ) )
)
);
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
GET_MESSAGE ('BOM_COPY_ERR_COMP_FOR_WIP_JOB',
bom_globals.get_item_name(bcb.component_item_id, from_org_id),
bom_globals.get_item_name(to_item_id, from_org_id)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
bom_copy_explosions_v bev
WHERE bcb.bill_sequence_id = x_from_sequence_id
AND bcb.component_item_id <> to_item_id
AND bcb.eco_for_production <> 2
AND bcb.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND ((bcb.implementation_date IS NOT NULL)
OR (bcb.implementation_date IS NULL
AND bcb.change_notice = context_eco
AND ( bcb.acd_type = 1 OR bcb.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb1
WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
AND bcb1.change_notice = context_eco
AND bcb1.acd_type = 3
AND bcb1.effectivity_date <= x_effectivity_date
AND bcb1.implementation_date IS NULL
AND bcb1.bill_sequence_id = bcb.bill_sequence_id
)
AND 'T' = bev.access_flag
AND 'T' =
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bcb.component_item_id),
TO_CHAR (to_org_id),
bom_exploder_pub.get_ego_user
)
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bcb.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
)
AND ( ( l_from_eff_ctrl = 1
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.effectivity_date <= rev_date) -- For first revised item we can have past eff comps as eff on the target date
OR ( p_cpy_past_eff_comps = 'N' AND bcb.effectivity_date = rev_date ) )
) OR
( ( l_from_eff_ctrl = 2 OR l_from_eff_ctrl = 3 )
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.from_end_item_unit_number <= unit_number)
OR ( p_cpy_past_eff_comps = 'N' AND bcb.from_end_item_unit_number = unit_number ) )
) OR
( l_from_eff_ctrl = 4
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.from_end_item_rev_id <= p_end_item_rev_id)
OR ( p_cpy_past_eff_comps = 'N' AND bcb.from_end_item_rev_id = p_end_item_rev_id ) )
)
);
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
GET_MESSAGE ('BOM_COPY_ERR_COMP_NOT_STANDARD',
bom_globals.get_item_name(bcb.component_item_id, from_org_id),
bom_globals.get_item_name(to_item_id, from_org_id)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
mtl_system_items_b msib1,
bom_copy_explosions_v bev
WHERE bcb.bill_sequence_id = x_from_sequence_id
AND bcb.component_item_id = msib1.inventory_item_id
AND bcb.component_item_id <> to_item_id
AND bcb.implementation_date IS NOT NULL
AND msib1.organization_id = to_org_id
AND bcb.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND ((bcb.implementation_date IS NOT NULL)
OR (bcb.implementation_date IS NULL
AND bcb.change_notice = context_eco
AND ( bcb.acd_type = 1 OR bcb.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb1
WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
AND bcb1.change_notice = context_eco
AND bcb1.acd_type = 3
AND bcb1.effectivity_date <= x_effectivity_date
AND bcb1.implementation_date IS NULL
AND bcb1.bill_sequence_id = bcb.bill_sequence_id
)
AND 'T' = bev.access_flag
AND 'T' =
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bcb.component_item_id),
TO_CHAR (to_org_id),
bom_exploder_pub.get_ego_user
)
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bcb.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
)
AND (base_item_flag = -1
AND itm_type = 4
AND msib1.bom_item_type <> 4
)
AND ( ( l_from_eff_ctrl = 1
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.effectivity_date <= rev_date) -- For first revised item we can have past eff comps as eff on the target date
OR ( p_cpy_past_eff_comps = 'N' AND bcb.effectivity_date = rev_date ) )
) OR
( ( l_from_eff_ctrl = 2 OR l_from_eff_ctrl = 3 )
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.from_end_item_unit_number <= unit_number)
OR ( p_cpy_past_eff_comps = 'N' AND bcb.from_end_item_unit_number = unit_number ) )
) OR
( l_from_eff_ctrl = 4
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.from_end_item_rev_id <= p_end_item_rev_id)
OR ( p_cpy_past_eff_comps = 'N' AND bcb.from_end_item_rev_id = p_end_item_rev_id ) )
)
);
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev(bcb.component_item_id, from_org_id, rev_date),
check_component_type_rules(bcb.component_item_id,
to_item_id, to_org_id),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
bom_copy_explosions_v bev
WHERE bcb.bill_sequence_id = x_from_sequence_id
AND bcb.component_item_id <> to_item_id
AND bcb.implementation_date IS NOT NULL
AND bcb.component_sequence_id = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND ((bcb.implementation_date IS NOT NULL)
OR (bcb.implementation_date IS NULL
AND bcb.change_notice = context_eco
AND ( bcb.acd_type = 1 OR bcb.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb1
WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
AND bcb1.change_notice = context_eco
AND bcb1.acd_type = 3
AND bcb1.effectivity_date <= x_effectivity_date
AND bcb1.implementation_date IS NULL
AND bcb1.bill_sequence_id = bcb.bill_sequence_id
)
AND 'T' = bev.access_flag
AND 'T' =
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bcb.component_item_id),
TO_CHAR (to_org_id),
bom_exploder_pub.get_ego_user
)
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bcb.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
)
AND check_component_type_rules(bcb.component_item_id,
to_item_id, to_org_id) IS NOT NULL -- Component Type validation fails
AND ( ( l_from_eff_ctrl = 1
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.effectivity_date <= rev_date) -- For first revised item we can have past eff comps as eff on the target date
OR ( p_cpy_past_eff_comps = 'N' AND bcb.effectivity_date = rev_date ) )
) OR
( ( l_from_eff_ctrl = 2 OR l_from_eff_ctrl = 3 )
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.from_end_item_unit_number <= unit_number)
OR ( p_cpy_past_eff_comps = 'N' AND bcb.from_end_item_unit_number = unit_number ) )
) OR
( l_from_eff_ctrl = 4
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.from_end_item_rev_id <= p_end_item_rev_id)
OR ( p_cpy_past_eff_comps = 'N' AND bcb.from_end_item_rev_id = p_end_item_rev_id ) )
)
);
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
GET_MESSAGE (
'BOM_CPY_REV_CHANGE_POLICY_ERR',
bom_globals.get_item_name(bcb.component_item_id, from_org_id),
bom_globals.get_item_name(to_item_id, from_org_id)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
mtl_system_items_b msib
WHERE bcb.bill_sequence_id = to_sequence_id
AND bcb.component_item_id = msib.inventory_item_id
AND msib.organization_id = to_org_id
AND 'Y' <>
bom_globals.check_change_policy_range(
to_item_id,
to_org_id,
NULL, -- p_start_revision
NULL, -- p_end_revision
NULL, -- p_start_rev_id
NULL, -- p_end_rev_id
bcb.effectivity_date, -- p_effective_date
bcb.disable_date, -- p_disable_date
bom_globals.get_change_policy_val(to_item_id, to_org_id,
BOM_Revisions.Get_Item_Revision_Id_Fn('ALL','ALL',to_org_id,
to_item_id,x_effectivity_date),
null, -- rev id
p_trgt_str_type_id), -- p_current_chg_pol
p_trgt_str_type_id, -- p_structure_type_id
'Y' -- p_use_eco
);
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
GET_MESSAGE (
'BOM_CPY_REV_CHANGE_POLICY_ERR',
bom_globals.get_item_name(bcb.component_item_id, from_org_id),
bom_globals.get_item_name(to_item_id, from_org_id)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
mtl_system_items_b msib
WHERE bcb.bill_sequence_id = to_sequence_id
AND bcb.component_item_id = msib.inventory_item_id
AND msib.organization_id = to_org_id
AND 'Y' <>
bom_globals.check_change_policy_range(
to_item_id,
to_org_id,
NULL, -- p_start_revision
NULL, -- p_end_revision
bcb.from_end_item_rev_id, -- p_start_rev_id
bcb.to_end_item_rev_id, -- p_end_rev_id
NULL, -- p_effective_date
NULL, -- p_disable_date
bom_globals.get_change_policy_val(to_item_id, to_org_id,
eco_end_item_rev_id,
null, -- rev id
p_trgt_str_type_id), -- p_current_chg_pol
p_trgt_str_type_id, -- p_structure_type_id
'Y' -- p_use_eco
);
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
GET_MESSAGE
('BOM_COPY_ERR_COMP_REV_DIFF',
bom_globals.get_item_name(bcb.component_item_id, from_org_id),
bom_globals.get_item_name(to_item_id, from_org_id),
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
bom_copy_explosions_v bev
WHERE bcb.bill_sequence_id = x_from_sequence_id
AND bcb.component_item_id <> to_item_id
AND bcb.implementation_date IS NOT NULL
AND bcb.component_sequence_id = bev.component_sequence_id
-- Error needs to be logged only for fixed revision components
AND bcb.component_item_revision_id IS NOT NULL
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND ((bcb.implementation_date IS NOT NULL)
OR (bcb.implementation_date IS NULL
AND bcb.change_notice = context_eco
AND ( bcb.acd_type = 1 OR bcb.acd_type = 2 )
)
)
AND NOT EXISTS -- Bug 5151332 Disabled components should not get copied in ECO context
(
SELECT 1
FROM bom_components_b bcb1
WHERE bcb1.old_component_sequence_id = bcb.component_sequence_id
AND bcb1.change_notice = context_eco
AND bcb1.acd_type = 3
AND bcb1.effectivity_date <= x_effectivity_date
AND bcb1.implementation_date IS NULL
AND bcb1.bill_sequence_id = bcb.bill_sequence_id
)
AND 'T' = bev.access_flag
AND 'T' =
bom_security_pub.check_item_privilege
('EGO_VIEW_ITEM',
TO_CHAR (bcb.component_item_id),
TO_CHAR (to_org_id),
bom_exploder_pub.get_ego_user
)
AND NOT EXISTS (
SELECT 1
FROM bom_copy_structure_actions bcsa
WHERE bcsa.component_sequence_id =
bcb.component_sequence_id
AND bcsa.copy_request_id = p_copy_request_id
AND bcsa.organization_id = to_org_id
AND ( bcsa.component_exception_action = 1 OR bcsa.component_exception_action = 3)
-- Component Action is exclude or enable
-- we need not copy.
)
AND NOT EXISTS (
SELECT tmirb.revision_id
FROM mtl_item_revisions_b fmirb,
mtl_item_revisions_b tmirb
WHERE tmirb.inventory_item_id = bcb.component_item_id
AND tmirb.organization_id = to_org_id
AND tmirb.revision = fmirb.revision
AND fmirb.revision_id =
bcb.component_item_revision_id)
AND (
( l_from_eff_ctrl = 4
AND ( ( p_cpy_past_eff_comps = 'Y' AND bcb.from_end_item_rev_id <= p_end_item_rev_id)
OR ( p_cpy_past_eff_comps = 'N' AND bcb.from_end_item_rev_id = p_end_item_rev_id ) )
)
);
SELECT common_routing_sequence_id
INTO to_rtg_seq_id
FROM bom_operational_routings
WHERE organization_id = to_org_id
AND assembly_item_id = to_item_id
AND (NVL (alternate_routing_designator, 'NONE') =
NVL (to_alternate, 'NONE')
OR (to_alternate IS NOT NULL
AND alternate_routing_designator IS NULL
AND NOT EXISTS (
SELECT NULL
FROM bom_operational_routings bor2
WHERE bor2.organization_id = to_org_id
AND bor2.assembly_item_id = to_item_id
AND bor2.alternate_routing_designator =
to_alternate)
)
);
UPDATE bom_inventory_components
SET operation_seq_num = 1
WHERE bill_sequence_id = to_sequence_id;
UPDATE bom_inventory_components bic
SET operation_seq_num = 1
WHERE bill_sequence_id = to_sequence_id
AND NOT EXISTS (
SELECT NULL
FROM bom_operation_sequences bos
WHERE routing_sequence_id = to_rtg_seq_id
AND bos.operation_seq_num = bic.operation_seq_num);
SELECT COUNT (*)
INTO dummy
FROM bom_inventory_components bic
WHERE bic.bill_sequence_id = to_sequence_id
AND EXISTS (
SELECT NULL
FROM bom_inventory_components bic2
WHERE bic2.bill_sequence_id = to_sequence_id
AND bic2.ROWID <> bic.ROWID
AND bic2.operation_seq_num = bic.operation_seq_num
AND bic2.component_item_id = bic.component_item_id
AND bic2.disable_date IS NULL
AND (bic.to_end_item_unit_number IS NULL
OR (bic.to_end_item_unit_number >=
bic2.from_end_item_unit_number
)
)
AND (bic2.to_end_item_unit_number IS NULL
OR (bic.from_end_item_unit_number <=
bic2.to_end_item_unit_number
)
))
AND bic.revised_item_sequence_id = rev_item_seq_id;
SELECT COUNT (*)
INTO dummy
FROM bom_components_b bcb
WHERE bcb.bill_sequence_id = to_sequence_id
AND EXISTS (
SELECT NULL
FROM bom_components_b bcb2
WHERE bcb2.bill_sequence_id = to_sequence_id
AND bcb2.ROWID <> bcb.ROWID
AND bcb2.operation_seq_num = bcb.operation_seq_num
AND bcb2.component_item_id = bcb.component_item_id
AND bcb2.disable_date IS NULL
AND (bcb.to_end_item_rev_id IS NULL
OR (get_minor_rev_code
(bcb.to_end_item_rev_id,
bcb.to_end_item_minor_rev_id
) >=
get_minor_rev_code
(bcb2.from_end_item_rev_id,
bcb2.from_end_item_minor_rev_id
)
)
)
AND (bcb2.to_end_item_rev_id IS NULL
OR (get_minor_rev_code
(bcb.from_end_item_rev_id,
bcb.from_end_item_minor_rev_id
) >=
get_minor_rev_code
(bcb2.to_end_item_rev_id,
bcb2.to_end_item_minor_rev_id
)
)
))
AND bcb.revised_item_sequence_id = rev_item_seq_id;
SELECT COUNT (*)
INTO dummy
FROM bom_inventory_components bic
WHERE bic.bill_sequence_id = to_sequence_id
AND EXISTS (
SELECT NULL
FROM bom_inventory_components bic2
WHERE bic2.bill_sequence_id = to_sequence_id
AND bic2.ROWID <> bic.ROWID
AND bic2.operation_seq_num = bic.operation_seq_num
AND bic2.component_item_id = bic.component_item_id
AND bic2.effectivity_date <= bic.effectivity_date
AND NVL (bic2.disable_date,
bic.effectivity_date + 1
) > bic.effectivity_date)
AND bic.revised_item_sequence_id = rev_item_seq_id;
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
GET_MESSAGE ('BOM_COPY_ERR_NO_PLANNING_COMPS',
bom_globals.get_item_name(bcb.component_item_id, from_org_id),
bom_globals.get_item_name(to_item_id, from_org_id)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
mtl_system_items_b msib1,
mtl_system_items_b msib2
WHERE bcb.bill_sequence_id = to_sequence_id
AND (msib1.bom_item_type = planning
AND msib2.bom_item_type <> planning
)
AND msib2.inventory_item_id = to_item_id
AND msib2.organization_id = to_org_id
AND msib1.inventory_item_id = bcb.component_item_id
AND msib1.organization_id = to_org_id
AND bcb.revised_item_sequence_id = rev_item_seq_id;
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
GET_MESSAGE ('BOM_COPY_ERR_NO_OPT_MODEL_COMP',
bom_globals.get_item_name(bcb.component_item_id, from_org_id),
bom_globals.get_item_name(to_item_id, from_org_id)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
mtl_system_items_b msib1,
mtl_system_items_b msib2
WHERE bcb.bill_sequence_id = to_sequence_id
AND (msib1.bom_item_type IN (model, option_class)
AND msib2.bom_item_type = STANDARD
AND msib2.base_item_id IS NULL
)
AND msib2.inventory_item_id = to_item_id
AND msib2.organization_id = to_org_id
AND msib1.inventory_item_id = bcb.component_item_id
AND msib1.organization_id = to_org_id
AND bcb.revised_item_sequence_id = rev_item_seq_id;
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
GET_MESSAGE ('BOM_COPY_ERR_NO_ATO_OPT_COMPS',
bom_globals.get_item_name(bcb.component_item_id, from_org_id),
bom_globals.get_item_name(to_item_id, from_org_id)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
mtl_system_items_b msib1, -- Comp
mtl_system_items_b msib2 -- Structure
WHERE bcb.bill_sequence_id = to_sequence_id
AND (msib1.replenish_to_order_flag = 'Y'
AND msib1.bom_item_type = option_class
AND msib2.pick_components_flag = 'Y'
)
AND msib2.inventory_item_id = to_item_id
AND msib2.organization_id = to_org_id
AND msib1.inventory_item_id = bcb.component_item_id
AND msib1.organization_id = to_org_id
AND bcb.revised_item_sequence_id = rev_item_seq_id;
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
GET_MESSAGE ('BOM_COPY_ERR_NO_ATO_STD_COMPS',
bom_globals.get_item_name(bcb.component_item_id, from_org_id),
bom_globals.get_item_name(to_item_id, from_org_id)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
mtl_system_items_b msib1, -- Comp
mtl_system_items_b msib2 -- Structure
WHERE bcb.bill_sequence_id = to_sequence_id
AND (msib1.replenish_to_order_flag = 'Y'
AND msib1.bom_item_type = STANDARD
AND msib2.pick_components_flag = 'Y'
AND msib2.bom_item_type = STANDARD
)
AND msib2.inventory_item_id = to_item_id
AND msib2.organization_id = to_org_id
AND msib1.inventory_item_id = bcb.component_item_id
AND msib1.organization_id = to_org_id
AND bcb.revised_item_sequence_id = rev_item_seq_id;
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bcb.component_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (bcb.component_item_id,
from_org_id,
rev_date
),
GET_MESSAGE ('BOM_COPY_ERR_NO_PTO_COMPS',
bom_globals.get_item_name(bcb.component_item_id, from_org_id),
bom_globals.get_item_name(to_item_id, from_org_id)
),
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_components_b bcb,
mtl_system_items_b msib1, -- Comp
mtl_system_items_b msib2 -- Structure
WHERE bcb.bill_sequence_id = to_sequence_id
AND (msib1.pick_components_flag = 'Y'
AND msib2.replenish_to_order_flag = 'Y'
)
AND msib2.inventory_item_id = to_item_id
AND msib2.organization_id = to_org_id
AND msib1.inventory_item_id = bcb.component_item_id
AND msib1.organization_id = to_org_id
AND bcb.revised_item_sequence_id = rev_item_seq_id;
DELETE FROM bom_inventory_components bic
WHERE bic.bill_sequence_id = to_sequence_id
AND EXISTS (
SELECT NULL
FROM mtl_system_items msi1, -- bom
mtl_system_items msi2 -- component
WHERE ((msi2.bom_item_type = planning
AND msi1.bom_item_type <> planning
)
OR (msi2.bom_item_type IN (model, option_class)
AND msi1.bom_item_type = STANDARD
AND msi1.base_item_id IS NULL
)
OR (msi2.replenish_to_order_flag = 'Y'
AND msi2.bom_item_type = option_class
AND msi1.pick_components_flag = 'Y'
)
OR (msi2.replenish_to_order_flag = 'Y'
AND msi2.bom_item_type = STANDARD
AND msi1.pick_components_flag = 'Y'
AND msi1.bom_item_type = STANDARD
)
OR (msi2.pick_components_flag = 'Y'
AND msi1.replenish_to_order_flag = 'Y'
)
)
AND msi1.inventory_item_id = to_item_id
AND msi1.organization_id = to_org_id
AND msi2.inventory_item_id = bic.component_item_id
AND msi2.organization_id = to_org_id)
AND bic.revised_item_sequence_id = rev_item_seq_id;
UPDATE bom_components_b bic
SET bic.wip_supply_type = phantom
WHERE bic.bill_sequence_id = to_sequence_id
AND EXISTS (
SELECT NULL
FROM mtl_system_items msi1, -- assembly
mtl_system_items msi2 -- component
WHERE msi2.bom_item_type IN (model, option_class)
AND msi2.inventory_item_id = bic.component_item_id
AND msi2.organization_id = to_org_id
AND msi1.inventory_item_id = to_item_id
AND msi1.organization_id = to_org_id)
AND bic.revised_item_sequence_id = rev_item_seq_id;
UPDATE bom_components_b bic
SET bic.optional = 1
WHERE bic.bill_sequence_id = to_sequence_id
AND EXISTS (
SELECT NULL
FROM mtl_system_items msi1, -- assembly
mtl_system_items msi2 -- component
WHERE msi2.base_item_id IS NULL
AND msi2.replenish_to_order_flag = 'Y'
AND msi2.bom_item_type = STANDARD
AND msi1.pick_components_flag = 'Y'
AND msi1.bom_item_type IN (model, option_class)
AND msi2.inventory_item_id = bic.component_item_id
AND msi2.organization_id = to_org_id
AND msi1.inventory_item_id = to_item_id
AND msi1.organization_id = to_org_id)
AND bic.revised_item_sequence_id = rev_item_seq_id;
INSERT INTO bom_reference_designators
(component_reference_designator,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
ref_designator_comment,
change_notice,
component_sequence_id,
acd_type,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
)
SELECT component_reference_designator,
SYSDATE,
user_id,
SYSDATE,
user_id,
user_id,/*NULL,bugfix:8639515*/
ref_designator_comment,
x_e_change_notice,
bic.component_sequence_id,
x_acd_type,
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate,
brd.attribute_category,
brd.attribute1,
brd.attribute2,
brd.attribute3,
brd.attribute4,
brd.attribute5,
brd.attribute6,
brd.attribute7,
brd.attribute8,
brd.attribute9,
brd.attribute10,
brd.attribute11,
brd.attribute12,
brd.attribute13,
brd.attribute14,
brd.attribute15
FROM bom_reference_designators brd,
bom_components_b bic,
bom_copy_explosions_v bev
WHERE bic.bill_sequence_id = to_sequence_id
AND NVL (bic.eco_for_production, 2) = 2
AND brd.component_sequence_id = bic.created_by
AND NVL (brd.acd_type, 1) <> 3
AND bic.created_by = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND bic.revised_item_sequence_id = rev_item_seq_id;
INSERT INTO bom_substitute_components
(substitute_component_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
substitute_item_quantity,
component_sequence_id,
acd_type,
change_notice,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
enforce_int_requirements
)
SELECT substitute_component_id,
SYSDATE,
user_id,
SYSDATE,
user_id,
NULL,
--substitute_item_quantity,
DECODE(MSI.primary_unit_of_measure, -- Added corresponding to Bug 6510185
AA.primary_unit_of_measure,Bsc.substitute_item_quantity,
inv_convert.INV_UM_CONVERT(bsc.substitute_component_id,
NULL,
Bsc.substitute_item_quantity,
NULL,
NULL,
AA.primary_unit_of_measure,
MSI.primary_unit_of_measure)) Sub_Comp_qty,
bic.component_sequence_id,
x_acd_type,
x_e_change_notice,
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate,
bsc.attribute_category,
bsc.attribute1,
bsc.attribute2,
bsc.attribute3,
bsc.attribute4,
bsc.attribute5,
bsc.attribute6,
bsc.attribute7,
bsc.attribute8,
bsc.attribute9,
bsc.attribute10,
bsc.attribute11,
bsc.attribute12,
bsc.attribute13,
bsc.attribute14,
bsc.attribute15,
bsc.enforce_int_requirements
FROM bom_substitute_components bsc,
bom_components_b bic,
mtl_system_items msi,
MTL_SYSTEM_ITEMS AA , -- Added corresponding to Bug 6510185
bom_copy_explosions_v bev
WHERE bic.bill_sequence_id = to_sequence_id
AND NVL (bic.eco_for_production, 2) = 2
AND bsc.component_sequence_id = bic.created_by
AND NVL (bsc.acd_type, 1) <> 3
AND ((direction = eng_to_bom
AND msi.eng_item_flag = 'N')
OR (direction <> eng_to_bom)
)
AND msi.inventory_item_id = bsc.substitute_component_id
AND msi.organization_id = to_org_id
AND bic.created_by = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND bic.revised_item_sequence_id = rev_item_seq_id
AND MSI.inventory_item_id = AA.inventory_item_id -- Added corresponding to Bug 6510185
AND AA.organization_id = from_org_id -- Added corresponding to Bug 6510185
AND EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
ego_criteria_v ecv,
mtl_system_items_b msibs -- to assembly item
WHERE ecv.customization_application_id = 702
AND ecv.region_application_id = 702
AND ecv.region_code = 'BOM_ITEM_TYPE_REGION'
AND ecv.customization_code = ectv.customization_code
AND flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
AND ecv.value_varchar2 = msi.item_type -- Substitute Component
UNION ALL
SELECT 1
FROM DUAL
WHERE NOT EXISTS
(
SELECT 1
FROM fnd_lookup_values_vl flv,
ego_criteria_templates_v ectv,
mtl_system_items_b msibs -- to assembly item
WHERE flv.lookup_type = 'ITEM_TYPE'
AND flv.enabled_flag = 'Y'
AND (flv.start_date_active IS NULL OR flv.start_date_active < sysdate)
AND (flv.end_date_active IS NULL OR flv.end_date_active > sysdate)
AND flv.lookup_code = ectv.classification1
AND ectv.customization_application_id = 702
AND ectv.region_application_id = 702
AND ectv.region_code = 'BOM_ITEM_TYPE_REGION'
AND flv.lookup_code = msibs.item_type
AND msibs.inventory_item_id = to_item_id
AND msibs.organization_id = to_org_id
)
);
INSERT INTO bom_component_operations
(comp_operation_seq_id,
operation_seq_num,
operation_sequence_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
component_sequence_id,
bill_sequence_id,
consuming_operation_flag,
consumption_quantity,
supply_subinventory,
supply_locator_id,
wip_supply_type,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT bom_component_operations_s.NEXTVAL,
bco.operation_seq_num,
bos.operation_sequence_id,
SYSDATE,
user_id,
SYSDATE,
user_id,
/*NULL comment for bug8431772,change NULL to user_id*/user_id,
bic.component_sequence_id,
bic.bill_sequence_id,
bco.consuming_operation_flag,
bco.consumption_quantity,
DECODE (x_from_org_id,
to_org_id, bco.supply_subinventory,
DECODE( l_default_wip_params, 1, bic.supply_subinventory, NULL )
),
DECODE (x_from_org_id,
to_org_id, bco.supply_locator_id,
DECODE( l_default_wip_params, 1, bic.supply_locator_id, NULL )
),
bco.wip_supply_type,
bco.attribute_category,
bco.attribute1,
bco.attribute2,
bco.attribute3,
bco.attribute4,
bco.attribute5,
bco.attribute6,
bco.attribute7,
bco.attribute8,
bco.attribute9,
bco.attribute10,
bco.attribute11,
bco.attribute12,
bco.attribute13,
bco.attribute14,
bco.attribute15,
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
FROM bom_component_operations bco,
bom_components_b bic,
bom_operation_sequences bos,
bom_copy_explosions_v bev
WHERE bic.bill_sequence_id = to_sequence_id
AND NVL (bic.eco_for_production, 2) = 2
AND bco.component_sequence_id = bic.created_by
AND bos.routing_sequence_id = to_rtg_seq_id
AND bos.operation_seq_num = bco.operation_seq_num
AND bic.created_by = bev.component_sequence_id
AND bev.bill_sequence_id = from_sequence_id
AND bev.parent_sort_order = p_parent_sort_order
AND bic.revised_item_sequence_id = rev_item_seq_id;
INSERT INTO bom_dependent_desc_elements
(bill_sequence_id,
element_name,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
program_application_id,
program_id,
program_update_date,
request_id
)
SELECT to_sequence_id,
bdde.element_name,
SYSDATE,
user_id,
SYSDATE,
user_id,
user_id,
NULL,
fnd_global.conc_program_id,
sysdate,
fnd_global.conc_request_id
FROM bom_dependent_desc_elements bdde
WHERE bdde.bill_sequence_id = x_from_sequence_id
AND ((itm_type = model
AND EXISTS (
SELECT NULL
FROM mtl_descriptive_elements mde
WHERE mde.item_catalog_group_id = itm_cat_grp_id
AND mde.element_name = bdde.element_name)
)
OR itm_type = option_class
);
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
VALUES (to_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (to_item_id,
from_org_id,
SYSDATE
),
fnd_message.get,
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
);
INSERT INTO mtl_interface_errors
(unique_id,
organization_id,
transaction_id,
table_name,
column_name,
error_message,
bo_identifier,
last_update_date,
last_updated_by,
creation_date,
created_by,
message_type,
request_id,
program_application_id,
program_id,
program_update_date
)
VALUES (to_item_id,
to_org_id,
p_copy_request_id,
NULL,
get_current_item_rev (to_item_id,
from_org_id,
SYSDATE
),
fnd_message.get,
'BOM_COPY',
SYSDATE,
user_id,
SYSDATE,
user_id,
'E',
fnd_global.conc_request_id,
NULL,
fnd_global.conc_program_id,
sysdate
);
x_last_update_login => '',
x_program_application_id => '',
x_program_id => fnd_global.conc_program_id,
x_request_id => fnd_global.conc_request_id
);
-- Purpose : To validate the components and insert error messages
-- to errors table if required.
-- End of comments
FUNCTION check_component_type_rules(p_component_item_id IN NUMBER,
p_assembly_item_id IN NUMBER,
p_org_id IN NUMBER
) RETURN VARCHAR2
IS
l_return_status VARCHAR2(1);
SELECT bic.component_item_id component_item_id
, bic.change_notice change_notice
, bic.implementation_date implementation_date
FROM bom_inventory_components bic,
bom_structures_b bsb
WHERE bic.bill_sequence_id = bsb.bill_sequence_id
AND bsb.bill_sequence_id = p_bill_seq_id
--Bug 10331803. Should not validate currently disabled components.
AND (bic.disable_date is null OR bic.disable_date > NVL(BOM_EXPLODER_PUB.Get_Explosion_Date, SYSDATE))
AND not exists
(
SELECT 'x'
FROM mtl_system_items s1,
mtl_system_items s2
WHERE s1.organization_id = p_org_id
AND s1.inventory_item_id = bic.component_item_id
and s2.organization_id = bsb.organization_id
and s2.inventory_item_id = bsb.assembly_item_id
AND ((bsb.assembly_type = 1 AND s1.eng_item_flag = 'N')
OR (bsb.assembly_type = 2))
AND s1.inventory_item_id <> bsb.assembly_item_id
AND ((s2.bom_item_type = 1 AND s1.bom_item_type <> 3)
OR (s2.bom_item_type = 2 AND s1.bom_item_type <> 3)
OR (s2.bom_item_type = 3)
OR (s2.bom_item_type = 4
AND (s1.bom_item_type = 4
OR
( s1.bom_item_type IN (2, 1)
AND s1.replenish_to_order_flag = 'Y'
AND s2.base_item_id IS NOT NULL
AND s2.replenish_to_order_flag = 'Y' ))))
AND (s2.bom_item_type = 3
OR
s2.pick_components_flag = 'Y'
OR
s1.pick_components_flag = 'N')
AND (s2.bom_item_type = 3
OR
NVL(s1.bom_item_type, 4) <> 2
OR
(s1.bom_item_type = 2
AND (( s2.pick_components_flag = 'Y'
AND s1.pick_components_flag = 'Y')
OR ( s2.replenish_to_order_flag = 'Y'
AND s1.replenish_to_order_flag = 'Y'))))
AND( (nvl(fnd_profile.VALUE('BOM:MANDATORY_ATO_IN_PTO'),2) <> 1
AND
NOT(s2.bom_item_type = 4 AND s2.pick_components_flag = 'Y' AND s1.bom_item_type = 4 AND s1.replenish_to_order_flag = 'Y')
)
OR
(nvl(fnd_profile.VALUE('BOM:MANDATORY_ATO_IN_PTO'),2) = 1)
)
/* BOM ER 9904085,10175288,ATO Item under PTO Model*/
AND( ((nvl(fnd_profile.VALUE('BOM:MANDATORY_ATO_IN_PTO'), 2) <> 1)
AND
(NOT(s2.bom_item_type = 1 AND s2.pick_components_flag = 'Y' AND nvl(bic.optional, 1) = 2 AND s1.bom_item_type = 4 AND s1.replenish_to_order_flag = 'Y'))
)
OR
(nvl(fnd_profile.VALUE('BOM:MANDATORY_ATO_IN_PTO'), 2) = 1)
)
/* END BOM ER 9904085,10175288,ATO Item under PTO Model*/
);