ENGINATICSSYSTEM_ADMINISTRATORSYSADMIN8E2FF36EDEA679D2E0530100007F1FF2YesYselect 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dualUSF498BCC3BF767E56E0530100007FA2D3with
bom_tree as
(
select /*+ materialize */ distinct
bbom.bill_sequence_id
from
bom_bill_of_materials bbom
,bom_inventory_components bic
where
bbom.common_bill_sequence_id = bic.bill_sequence_id (+) and
bbom.assembly_type = 1 and -- BOM
nvl(bbom.effectivity_control,1) < 3 and
nvl(bic.eco_for_production,2) = 2 and
--
&lp_effective_display_clause1
&lp_effective_display_clause2
--
nvl(bbom.alternate_bom_designator,'?') = nvl(:p_alternate_bom,'?') and
-- exclude common boms for the moment
bbom.bill_sequence_id = bbom.common_bill_sequence_id and
bbom.common_organization_id is null and
bbom.common_assembly_item_id is null
connect by nocycle
prior decode(:p_explode_bom,'Y',bic.component_item_id,null) = bbom.assembly_item_id and
prior decode(:p_explode_bom,'Y',bbom.organization_id,null) = bbom.organization_id and
prior decode(:p_explode_bom,'Y',nvl(bbom.alternate_bom_designator,'?'),null) = nvl(bbom.alternate_bom_designator,'?')
start with
bbom.bill_sequence_id in
(select /*+ materialize */
bbom.bill_sequence_id
from
mtl_parameters mp,
mtl_system_items_kfv msik,
bom_bill_of_materials bbom
where
mp.organization_id = msik.organization_id and
msik.organization_id = bbom.organization_id and
msik.inventory_item_id = bbom.assembly_item_id and
msik.bom_enabled_flag = 'Y' and
msik.bom_item_type != 5 and -- Exclude Product Family
bbom.assembly_type = 1 and -- BOM
nvl(bbom.effectivity_control,1) < 3 and
nvl(bbom.alternate_bom_designator,'?') = nvl(:p_alternate_bom,'?') and
-- exclude common boms
bbom.bill_sequence_id = bbom.common_bill_sequence_id and
bbom.common_organization_id is null and
bbom.common_assembly_item_id is null and
mp.organization_code = :p_source_organization_code and
1=1
)
),
bom as
(
select
mp.organization_code,
msiv1.concatenated_segments assembly_item,
msiv1.description assembly_description,
bbom.specific_assembly_comment bill_comment,
xxen_util.meaning(decode(bbom.implementation_date,null,2,1),'SYS_YES_NO',700) bill_implemented_flag,
bbom.implementation_date bill_implementation_date,
bbom.effectivity_control effectivity_control_code,
xxen_util.meaning(bbom.effectivity_control,'MTL_EFFECTIVITY_CONTROL',700) effectivity_control,
bbom.alternate_bom_designator alternate_bom,
xxen_util.meaning(nvl2(bbom.common_assembly_item_id,1,2),'SYS_YES_NO',700) common_bill_flag,
--
msiv2.concatenated_segments component_item,
msiv2.description component_description,
bic.item_num item_seq,
bic.operation_seq_num operation_seq,
bic.component_quantity,
bic.effectivity_date date_effective_from,
bic.disable_date date_effective_to,
bic.from_end_item_unit_number end_item_unit_number_from,
bic.to_end_item_unit_number end_item_unit_number_to,
nvl(bic.basis_type,1) basis_type,
xxen_util.meaning(nvl(bic.basis_type,1),'BOM_BASIS_TYPE',700) basis,
xxen_util.meaning(decode(bic.implementation_date,null,2,1),'SYS_YES_NO',700) component_implemented_flag,
bic.implementation_date component_implementation_date,
--
bbom.bill_sequence_id,
bbom.organization_id,
bbom.assembly_item_id,
bic.component_sequence_id,
bic.component_item_id,
mp.master_organization_id
from
bom_bill_of_materials bbom
,bom_inventory_components bic
,mtl_system_items_vl msiv1
,mtl_system_items_vl msiv2
,mtl_parameters mp
where
bbom.common_bill_sequence_id = bic.bill_sequence_id (+) and
bbom.organization_id = mp.organization_id and
bbom.assembly_item_id = msiv1.inventory_item_id and
bbom.organization_id = msiv1.organization_id and
bic.component_item_id = msiv2.inventory_item_id (+) and
bbom.organization_id = nvl(msiv2.organization_id,bbom.organization_id) and
--
bbom.bill_sequence_id in (select bom_tree.bill_sequence_id from bom_tree) and
--
&lp_effective_display_clause1
&lp_effective_display_clause2
--
nvl(bic.eco_for_production,2) = 2
),
bom_subst as
( --from bom_substitute_components_v
select
bic2.component_sequence_id,
bsc.substitute_component_id,
msiv.concatenated_segments substitute_item,
msiv.description substitute_description,
bsc.substitute_item_quantity substitute_quantity,
xxen_util.meaning(bsc.enforce_int_requirements,'BOM_ENFORCE_INT_REQUIREMENTS',700) subst_integer_requirements
from
bom_substitute_components bsc,
bom_bill_of_materials bbom,
bom_inventory_components bic1,
bom_inventory_components bic2, /* current component record */
mtl_system_items_vl msiv /* component being referenced */
where
bsc.component_sequence_id = bic1.component_sequence_id and
bic1.bill_sequence_id = bbom.bill_sequence_id and
bsc.substitute_component_id = msiv.inventory_item_id and
bbom.organization_id = msiv.organization_id and
nvl(bsc.acd_type, 1) <> 3 and
bic2.bill_sequence_id = bic1.bill_sequence_id and
decode(bic2.implementation_date, null, bic2.old_component_sequence_id, bic2.component_sequence_id) = decode(bic1.implementation_date, null, bic1.old_component_sequence_id, bic1.component_sequence_id) and
bic1.effectivity_date =
(select
max(bic3.effectivity_date)
from
bom_substitute_components bsc3,
bom_inventory_components bic3
where
bic3.component_sequence_id = bsc3.component_sequence_id and
decode(bic3.implementation_date, null, bic3.old_component_sequence_id, bic3.component_sequence_id) = decode(bic1.implementation_date, null, bic1.old_component_sequence_id, bic1.component_sequence_id) and
bic3.bill_sequence_id = bic1.bill_sequence_id and
bsc3.substitute_component_id = bsc.substitute_component_id and
bic3.effectivity_date <= bic2.effectivity_date
)
)
--
-- Main Query Starts Here
select /*+ push_pred(bom_subst) */
--process--
case when nvl2(:p_autopopulate_upload_status,'Y','N') = 'Y' then xxen_upload.action_meaning(xxen_upload.action_update) else null end action_,
case when nvl2(:p_autopopulate_upload_status,'Y','N') = 'Y' then xxen_upload.status_meaning(xxen_upload.status_new) else null end status_,
case when nvl2(:p_autopopulate_upload_status,'Y','N') = 'Y' then xxen_util.description('U_EXCEL_MSG_VALIDATION_PENDING', 'XXEN_REPORT_TRANSLATIONS', 0) else null end message_,
null request_id_,
null modified_columns_,
to_char(null) row_id,
:p_enable_attrs_update enable_attrs_update,
case when nvl2(:p_autopopulate_upload_status,'Y','N') = 'Y' then xxen_util.meaning('Y','YES_NO',0) else null end create_common_bom,
boms.source_organization,
boms.assembly_item,
boms.assembly_description,
boms.alternate_bom,
boms.target_organization,
boms.is_item_defined_in_tgt,
boms.is_bom_defined_in_tgt,
boms.is_common_bom_in_tgt,
boms.comp_items_not_defined_in_tgt || nvl2(comp_items_not_defined_in_tgt,nvl2(subst_items_not_defined_in_tgt,',',null),null) || subst_items_not_defined_in_tgt comp_items_not_defined_in_tgt,
boms.bill_sequence_id,
boms.assembly_item_id,
boms.source_organization_id,
boms.target_organization_id
from
(
select
bom.organization_code source_organization,
bom.assembly_item,
bom.assembly_description,
bom.alternate_bom,
--
mp.organization_code target_organization,
--
nvl((select 'Yes' from mtl_system_items_vl msiv2 where msiv2.organization_id = mp.organization_id and msiv2.inventory_item_id = bom.assembly_item_id),'No') is_item_defined_in_tgt,
nvl((select 'Yes' from bom_bill_of_materials bbom2 where bbom2.organization_id = mp.organization_id and bbom2.assembly_item_id = bom.assembly_item_id and nvl(bbom2.alternate_bom_designator,'?') = nvl(bom.alternate_bom,'?') and rownum <= 1),'No') is_bom_defined_in_tgt,
nvl((select 'Yes' from bom_bill_of_materials bbom2 where bbom2.organization_id = mp.organization_id and bbom2.common_organization_id = bom.organization_id and bbom2.common_assembly_item_id = bom.assembly_item_id and rownum <= 1),'No') is_common_bom_in_tgt,
(select distinct
listagg(msiv.concatenated_segments,',') within group (order by msiv.concatenated_segments)
from bom_inventory_components bic,
mtl_system_items_vl msiv
where bic.bill_sequence_id = bom.bill_sequence_id and
msiv.organization_id = bom.organization_id and
msiv.inventory_item_id = bic.component_item_id and
not exists (select null from mtl_system_items_vl msiv2 where msiv2.organization_id = mp.organization_id and msiv2.inventory_item_id = bic.component_item_id)
) comp_items_not_defined_in_tgt,
(select distinct
listagg(msiv.concatenated_segments,',') within group (order by msiv.concatenated_segments)
from bom_inventory_components bic,
bom_substitute_components bsc,
mtl_system_items_vl msiv
where bic.bill_sequence_id = bom.bill_sequence_id and
bsc.component_sequence_id = bic.component_sequence_id and
msiv.organization_id = bom.organization_id and
msiv.inventory_item_id = bsc.substitute_component_id and
not exists (select null from mtl_system_items_vl msiv2 where msiv2.organization_id = mp.organization_id and msiv2.inventory_item_id = bsc.substitute_component_id)
) subst_items_not_defined_in_tgt,
--
bom.bill_sequence_id,
bom.assembly_item_id,
bom.organization_id source_organization_id,
mp.organization_id target_organization_id
from
bom,
mtl_parameters mp
where
mp.master_organization_id = bom.master_organization_id and
mp.organization_id != bom.organization_id and
mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and
2=2
union -- substitutes
select
bom.organization_code source_organization,
bom_subst.substitute_item,
bom_subst.substitute_description,
bbom.alternate_bom_designator alternate_bom,
--
mp.organization_code target_organization,
--
nvl((select 'Yes' from mtl_system_items_vl msiv2 where msiv2.organization_id = mp.organization_id and msiv2.inventory_item_id = bom_subst.substitute_component_id),'No') is_item_defined_in_tgt,
nvl((select 'Yes' from bom_bill_of_materials bbom2 where bbom2.organization_id = mp.organization_id and bbom2.assembly_item_id = bom_subst.substitute_component_id and rownum <= 1),'No') is_bom_defined_in_tgt,
nvl((select 'Yes' from bom_bill_of_materials bbom2 where bbom2.organization_id = mp.organization_id and bbom2.common_organization_id = bbom.organization_id and bbom2.common_assembly_item_id = bbom.assembly_item_id and rownum <= 1),'No') is_common_bom_in_tgt,
(select distinct
listagg(msiv.concatenated_segments,',') within group (order by msiv.concatenated_segments)
from bom_inventory_components bic,
mtl_system_items_vl msiv
where bic.bill_sequence_id = bbom.bill_sequence_id and
msiv.organization_id = bbom.organization_id and
msiv.inventory_item_id = bom_subst.substitute_component_id and
not exists (select null from mtl_system_items_vl msiv2 where msiv2.organization_id = mp.organization_id and msiv2.inventory_item_id = bom_subst.substitute_component_id)
) comp_items_not_defined_in_tgt,
(select distinct
listagg(msiv.concatenated_segments,',') within group (order by msiv.concatenated_segments)
from bom_inventory_components bic,
bom_substitute_components bsc,
mtl_system_items_vl msiv
where bic.bill_sequence_id = bbom.bill_sequence_id and
bsc.component_sequence_id = bic.component_sequence_id and
msiv.organization_id = bbom.organization_id and
msiv.inventory_item_id = bsc.substitute_component_id and
not exists (select null from mtl_system_items_vl msiv2 where msiv2.organization_id = mp.organization_id and msiv2.inventory_item_id = bsc.substitute_component_id)
) subst_items_not_defined_in_tgt,
--
bbom.bill_sequence_id,
bom_subst.substitute_component_id,
bom.organization_id source_organization_id,
mp.organization_id target_organization_id
from
bom,
bom_subst,
bom_bill_of_materials bbom,
mtl_parameters mp
where
:p_explode_bom = 'Y' and
bom.component_sequence_id = bom_subst.component_sequence_id and
bbom.organization_id = bom.organization_id and
bbom.assembly_item_id = bom_subst.substitute_component_id and
mp.master_organization_id = bom.master_organization_id and
mp.organization_id != bom.organization_id and
mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and
2=2
) boms
where
:p_display_option = :p_display_option and
:p_scope = :p_scope and
3=3YU(xxen_util.lookup_code(:Common_Bom_Scope,'BOM_ORG_CHOICE',700) = 3) or
(xxen_util.lookup_code(:Common_Bom_Scope,'BOM_ORG_CHOICE',700) = 2 and :Target_Hierarchy is not null) or
(xxen_util.lookup_code(:Common_Bom_Scope,'BOM_ORG_CHOICE',700) = 1 and :Target_Organization is not null)
Axxen_bom_upload.upload_common_bom_recordUSBOM Common Bill of Materials UploadBOM Common Bill of Materials Upload
============================================
The upload can be used to create Common BOM from the BOMS defined in the specified source organization in one or more target organizations as determined by the Scope Parameter:
All - All Organizations
BOMs will made Common to all other Organizations that share the same Master Organization as the Source Organization and to which the current responsibility has access to.
Hierarchy – Organization Hierarchy
BOMs will made Common to all Organizations the current responsibility has access to and which are below the Source Organization in the specified Hierarchy.
Organization – Specific Organizations
BOMs will be made Common to the specified Target Organizations. The Target Organizations must share the same Master Organization as the Source Organization.
Unlike the Oracle standard Create Common BOM process which requires all sub-component BOMS and substitute component BOMs to be made common individually, this upload process will iterate through the sub-assemblies and substitute assemblies and make them common in the Target Organization if they have not already been defined.
A prerequisite however, as with the BOM Bill of Materials Upload (Amazon), is that all the items are already assigned in the Target Organization.
If any component Items are not defined in the Target Organization, these will be identified in the upload Excel.
Usage
=====
- Specify the Source Organization in which the BOMS are defined
- Specify the Scope to determine the Target Organizations
- Use the Report Parameters to select the BOMS to be made common
- The generated Excel will contain one row per BOM and Target Organization combination
- The generated Excel Identifies if the BOM Item is defined in the Target Organization, if it already exists as a BOM in the Target Organization, if it is a Common BOM in the target organization already, and if the BOM has any component items which are not defined in the Target Organization. These will prevent the BOM upload process from attempting to make the BOM common in the target organization.
- In the generated Excel, set the ‘Create Common Bom’ column to Yes against the BOM/Target Organization combinations to be made Common.
- Save and upload the Excel to process the selections made back into Oracle
- After upload, a new Excel is generated showing the success/error status of the creation of the Common BOMS in each target organization.
Templates
=========
Common BOM Upload Template
In this template, the user must review and manually select the BOM/Target Organization combinations to be made Common. Setting the ‘Create Common Bom’ flag against a BOM/Target Organization combination will trigger the row for update and processing during upload.
Automatic Common BOM Upload Template
In this template, the excel is generated with the ‘Create Common Bom’ flag set against all BOM/Target Organization combinations and the rows already flagged for update and processing. In this template the user can opt out of creating a common BOM for specific BOM/Target Organization combinations by either de-selecting (clearing) the ‘Create Common Bom’ flag column or by deleting the row from the spreadsheet.
Please enter a Target Hierarchy when Scope = Hierarchy, and a Target Organization when Scope = Single OrganizationEnginaticsR12 onlyUpload&lp_effective_display_clause1&lp_effective_display_clause21=12=23=3:p_alternate_bom:p_autopopulate_upload_status:p_display_option:p_enable_attrs_update:p_explode_bom:p_scope:p_source_organization_code1-30:p_display_option_codeNumberselect
lookup_code
from
mfg_lookups
where lookup_type='BOM_INQUIRY_DISPLAY_TYPE'
and meaning = :$flex$.DisplayUSDisplay Option210:p_source_organization_codeLOV customYselect
mp.organization_code value,
ood.organization_name description
from
mtl_parameters mp,
org_organization_definitions ood
where
mp.organization_id=mp.master_organization_id and
mp.organization_id=ood.organization_id and
nvl(ood.disable_date,sysdate)>=sysdate and
-- exists not a common bom
exists (select null from bom_bill_of_materials bbom where bbom.organization_id = mp.organization_id and bbom.bill_sequence_id = bbom.common_bill_sequence_id)
order by
mp.organization_codeselect nvl(
(
select
x.organization_code
from
(select
min(mp2.organization_code) organization_code,
min(mp2.organization_id) organization_id,
count(distinct mp2.master_organization_id) cnt
from
mtl_parameters mp2
where
mp2.organization_id = mp2.master_organization_id and
exists (select null from mtl_parameters mp3 where mp3.master_organization_id = mp2.organization_id and mp3.organization_id != mp2.organization_id) and
exists (select null from bom_bill_of_materials bbom where bbom.organization_id = mp2.organization_id and bbom.common_assembly_item_id is not null)
) x
where
x.cnt = 1
),
(
select
mp.organization_code
from
mtl_parameters mp,
mtl_parameters mp2
where
mp.organization_id = mp2.master_organization_id and
mp2.organization_id = fnd_profile.value('MFG_ORGANIZATION_ID')
)
)
from
dualYUSSource Organization Code320:p_scopeLOV customYselect
ml.meaning value,
ml.description
from
mfg_lookups ml
where
ml.lookup_type = 'BOM_ORG_CHOICE'
order by
valueYUSCommon BOM Scope4302=2mp.organization_id in
(
select
poe.organization_id_child
from
per_org_structure_elements poe
connect by
poe.organization_id_parent = prior poe.organization_id_child and
poe.org_structure_version_id = prior poe.org_structure_version_id
start with
poe.organization_id_parent =
(select
mp.organization_id
from mtl_parameters mp
where mp.organization_code = :p_source_organization_code
)
and poe.org_structure_version_id =
(select
posv.org_structure_version_id
from
per_org_structure_versions posv,
per_organization_structures pos
where
posv.organization_structure_id = pos.organization_structure_id and
sysdate between posv.date_from and nvl(posv.date_to,sysdate) and
ltrim(rtrim(pos.name)) = ltrim(rtrim(:p_organization_hierarchy)) and
(pos.business_group_id = to_number(fnd_profile.value('PER_BUSINESS_GROUP_ID')) or pos.business_group_id is null)
)
)LOV customYselect
pos.name value,
null description
from
mtl_parameters mp,
per_organization_structures pos
where
mp.organization_code = :$flex$.source_organization_code and
pos.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID') and
inv_orghierarchy_pvt.org_hierarchy_access(pos.name) = 'Y' and
inv_orghierarchy_pvt.org_hierarchy_level_access(pos.name,mp.organization_id) = 'Y' and
xxen_util.lookup_code(:$flex$.Common_Bom_Scope,'BOM_ORG_CHOICE',700) = 2
order by
valueUSTarget Hierarchy5402=2mp.organization_code = :p_target_organizationLOV customYselect
ood.organization_code value,
ood.organization_name description
from
mtl_parameters mps,
mtl_parameters mpt,
org_organization_definitions ood
where
mps.organization_code = :$flex$.source_organization_code and
mpt.master_organization_id = mpt.master_organization_id and
mpt.organization_id != mps.organization_id and
ood.organization_id = mpt.organization_id and
nvl(ood.disable_date,sysdate+1) > sysdate and
xxen_util.lookup_code(:$flex$.Common_Bom_Scope,'BOM_ORG_CHOICE',700) = 1
order by
valueUSTarget Organization650:p_enable_attrs_updateLOV OracleBOM_SRS_YES_NOYselect
lookup_code id,
meaning value,
null description
from
fnd_lookups
where lookup_type='YES_NO'
order by meaningNYUSEnable Attributes Update7601=1msik.concatenated_segments=:p_assemblyLOV customYYselect
msiv.concatenated_segments value,
msiv.description||' ('||mp.organization_code||')' description
from
mtl_system_items_vl msiv,
mtl_parameters mp
where
mp.organization_code = :$flex$.source_organization_code and
msiv.bom_enabled_flag = 'Y' and
msiv.bom_item_type != 5 and
msiv.eng_item_flag = 'N' and
msiv.organization_id=mp.organization_id and
exists
(select
null
from
bom_bill_of_materials bbom
where
bbom.assembly_type = 1 and -- BOM
nvl(bbom.effectivity_control,1) <= 3 and
bbom.bill_sequence_id = bbom.common_bill_sequence_id and -- exclude common boms
bbom.common_organization_id is null and
bbom.common_assembly_item_id is null and
bbom.alternate_bom_designator is null and -- exclude alternate designators for now
bbom.organization_id = msiv.organization_id and
bbom.assembly_item_id = msiv.inventory_item_id
)
order by
mp.organization_code,
msiv.concatenated_segmentsUSAssembly870:p_alternate_bomLOV customYselect
badvv.alternate_designator_code id,
badvv.alternate_designator_code value,
badvv.alternate_designator_code description
from
bom_alt_designators_val_v badvv
where
badvv.organization_id = -1 or
badvv.organization_id = (select mp.organization_id from mtl_parameters mp where mp.organization_code = :$flex$.organization_code)
order by
valueUSAlternate BOM980:p_explode_bomLOV OracleYes_NoYselect
lookup_code id,
meaning value,
null description
from
fnd_lookups
where fnd_lookups.lookup_type='YES_NO'
order by value,descriptionYYUSShow Sub-Assemblies10903=3boms.is_bom_defined_in_tgt = 'No'LOV OracleYes_NoYselect
lookup_code id,
meaning value,
null description
from
fnd_lookups
where fnd_lookups.lookup_type='YES_NO'
order by value,descriptionYYYUSShow Missing BOMS only113=33=3NUSShow Missing BOMS only12100:p_autopopulate_upload_statusLOVYes8E2FF36EDEA679D2E0530100007F1FF2Yselect 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dualUSAuto Populate Upload Columns13110:p_implemented_onlyLOV OracleYes_NoYselect
lookup_code id,
meaning value,
null description
from
fnd_lookups
where fnd_lookups.lookup_type='YES_NO'
order by value,descriptionYYUSImplemented Only14&lp_effective_display_clause1-- displayed components and effectivity control
-- implemented
( (nvl(:p_implemented_only,'Y') = 'Y' and bbom.implementation_date is not null and bic.implementation_date is not null) or
(nvl(:p_implemented_only,'Y') = 'N')
) and
-- effectivity
( ( bbom.effectivity_control = 1 and
( (nvl(:p_display_option_code,3) = 1) or --All -- Value Set BOM_SRS_INQUIRY_DISPLAY_TYPE Lookup BOM_INQUIRY_DISPLAY_TYPE View App 700
(nvl(:p_display_option_code,3) = 2 and nvl(:p_effective_date,sysdate) >= bic.effectivity_date and nvl(:p_effective_date,sysdate) < nvl(bic.disable_date, nvl(:p_effective_date,sysdate)+1)) or -- Current
(nvl(:p_display_option_code,3) = 3 and nvl(:p_effective_date,sysdate) < nvl(bic.disable_date,nvl(:p_effective_date,sysdate)+1)) --Future and Current
)
) or
( bbom.effectivity_control <> 1 and
( (nvl(:p_display_option_code,3) = 1) or -- All
(nvl(:p_display_option_code,3) <> 1 and bic.disable_date is null) -- Current, Future and Current
)
)
) andUSImplemented Only15&lp_effective_display_clause2( ( bbom.effectivity_control = 1 and
( ( nvl(:p_implemented_only,'Y') = 'N' and
( nvl(:p_display_option_code,3) = 1 or --All
( nvl(:p_display_option_code,3) = 2 and -- Current
bic.effectivity_date =
(select
max(bic2.effectivity_date)
from
bom_inventory_components bic2
where
bic2.bill_sequence_id = bbom.bill_sequence_id and
bic2.component_item_id = bic.component_item_id and
( decode(bic2.implementation_date, null, bic2.old_component_sequence_id, bic2.component_sequence_id) =
decode(bic.implementation_date, null, bic.old_component_sequence_id, bic.component_sequence_id) or
bic2.operation_seq_num = bic.operation_seq_num
) and
bic2.effectivity_date <= nvl(:p_effective_date,sysdate) and
not exists
(select
null
from
bom_inventory_components bic3
where
bic3.bill_sequence_id = bbom.bill_sequence_id and
bic3.old_component_sequence_id = bic.component_sequence_id and
bic3.acd_type in (2,3) and
bic3.disable_date < nvl(:p_effective_date,sysdate)
)
)
) or
( nvl(:p_display_option_code,3) = 3 and -- Future and Current
bic.effectivity_date =
(select
max(bic2.effectivity_date)
from
bom_inventory_components bic2
where
bic2.bill_sequence_id = bbom.bill_sequence_id and
bic2.component_item_id = bic.component_item_id and
( decode(bic2.implementation_date, null, bic2.old_component_sequence_id, bic2.component_sequence_id) =
decode(bic.implementation_date, null, bic.old_component_sequence_id, bic.component_sequence_id) or
bic2.operation_seq_num = bic.operation_seq_num
) and
bic2.effectivity_date <= nvl(:p_effective_date,sysdate) and
not exists
(select
null
from
bom_inventory_components bic3
where
bic3.bill_sequence_id = bbom.bill_sequence_id and
bic3.old_component_sequence_id = bic.component_sequence_id and
bic3.acd_type in (2,3) and
bic3.disable_date < nvl(:p_effective_date,sysdate)
)
) or
bic.effectivity_date > nvl(:p_effective_date,sysdate)
)
)
) or
( nvl(:p_implemented_only,'Y') = 'Y' and
bic.implementation_date is not null
)
)
) or
( bbom.effectivity_control <> 1 and
( (nvl(:p_implemented_only,'Y') = 'N') or
(nvl(:p_implemented_only,'Y') = 'Y' and bic.implementation_date is not null)
)
)
) and
USImplemented Only16120:p_display_optionLOV customselect
meaning value,
null description
from
mfg_lookups
where lookup_type='BOM_INQUIRY_DISPLAY_TYPE'
order by value,descriptionxxen_util.meaning(3,'BOM_INQUIRY_DISPLAY_TYPE',700)YUSDisplay17130:p_effective_dateDateTimeSYSDATEYUSEffective Date:$flex$.Common_Bom_ScopeCommon BOM ScopeTarget Hierarchy:$flex$.Common_Bom_ScopeCommon BOM ScopeTarget Organization:$flex$.DisplayDisplayDisplay Option:$flex$.source_organization_codeSource Organization CodeAssembly:$flex$.source_organization_codeSource Organization CodeTarget Hierarchy:$flex$.source_organization_codeSource Organization CodeTarget Organization3ED7FCC96996AB3CC7BFE965BEB174FAAutomatic Common BOM Upload TemplateSYSADMIN4ALTERNATE_BOM-7.0008ALTERNATE_BOM_DESIGNATOR3ASSEMBLY_DESCRIPTION2ASSEMBLY_ITEM-2.0003ASSEMBLY_SPN-3.0004BILL_COMMENT-3.0008COMPONENT_DESCRIPTION-3.001COMPONENT_ITEM-3.0011COMPONENT_QUANTITY-3.0009COMPONENT_SPN10COMP_ITEMS_NOT_DEFINED_IN_TGT6CREATE_COMMON_BOM-3.0012DATE_EFFECTIVE_FROM-16.0017DATE_EFFECTIVE_FROM_OLD-3.0013DATE_EFFECTIVE_TO-9.001EFFECTIVITY_CONTROL-19.002END_ITEM_UNIT_NUMBER_FROM_OLD-17.0018FROM_END_ITEM_UNIT_NUMBER8IS_BOM_DEFINED_IN_TGT9IS_COMMON_BOM_IN_TGT7IS_ITEM_DEFINED_IN_TGT-3.0006ITEM_SEQ-3.0007OPERATION_SEQ-11.0012OPERATION_SEQ_OLD1SOURCE_ORGANIZATION-3.0014SUBSTITUTE_DESCRIPTION-3.0015SUBSTITUTE_ITEM-19.0021SUBSTITUTE_ITEM_OLD-3.0016SUBSTITUTE_QUANTITY5TARGET_ORGANIZATION-17.0019TO_END_ITEM_UNIT_NUMBERAuto Populate Upload ColumnsYSSite6B9869028970624056D7C3BA7885CDD4Common BOM Upload TemplateSYSADMIN4ALTERNATE_BOM-7.0008ALTERNATE_BOM_DESIGNATOR3ASSEMBLY_DESCRIPTION2ASSEMBLY_ITEM-2.0003ASSEMBLY_SPN-3.0004BILL_COMMENT-3.0008COMPONENT_DESCRIPTION-3.001COMPONENT_ITEM-3.0011COMPONENT_QUANTITY-3.0009COMPONENT_SPN10COMP_ITEMS_NOT_DEFINED_IN_TGT6CREATE_COMMON_BOM-3.0012DATE_EFFECTIVE_FROM-16.0017DATE_EFFECTIVE_FROM_OLD-3.0013DATE_EFFECTIVE_TO-9.001EFFECTIVITY_CONTROL-19.002END_ITEM_UNIT_NUMBER_FROM_OLD-17.0018FROM_END_ITEM_UNIT_NUMBER8IS_BOM_DEFINED_IN_TGT9IS_COMMON_BOM_IN_TGT7IS_ITEM_DEFINED_IN_TGT-3.0006ITEM_SEQ-3.0007OPERATION_SEQ-11.0012OPERATION_SEQ_OLD1SOURCE_ORGANIZATION-3.0014SUBSTITUTE_DESCRIPTION-3.0015SUBSTITUTE_ITEM-19.0021SUBSTITUTE_ITEM_OLD-3.0016SUBSTITUTE_QUANTITY5TARGET_ORGANIZATION-17.0019TO_END_ITEM_UNIT_NUMBERSSiteSYSADMIN3ED7FCC96996AB3CC7BFE965BEB174FA6B9869028970624056D7C3BA7885CDD41ACTION_Charvarchar2Y2STATUS_Charvarchar23MESSAGE_Charvarchar24REQUEST_ID_Charvarchar2YY5MODIFIED_COLUMNS_Charvarchar2Y6ROW_IDCharvarchar2YY7ENABLE_ATTRS_UPDATECharvarchar2:$flex$.enable_attributes_updateYY8CREATE_COMMON_BOMLOV customvarchar2Yselect xxen_util.meaning('Y','YES_NO',0) value, null description from dual9SOURCE_ORGANIZATIONCharvarchar2Y10ASSEMBLY_ITEMCharvarchar2Y11ASSEMBLY_DESCRIPTIONCharvarchar212ALTERNATE_BOMCharvarchar2Y13TARGET_ORGANIZATIONCharvarchar2Y14IS_ITEM_DEFINED_IN_TGTCharvarchar2Y15IS_BOM_DEFINED_IN_TGTCharvarchar2Y16IS_COMMON_BOM_IN_TGTCharvarchar217COMP_ITEMS_NOT_DEFINED_IN_TGTCharvarchar218BILL_SEQUENCE_IDNumbernumberYY19ASSEMBLY_ITEM_IDNumbernumberYY20SOURCE_ORGANIZATION_IDNumbernumberYY21TARGET_ORGANIZATION_IDNumbernumberYYU1action_inCharACTION_CODE_U2status_outCharSTATUS_CODE_U3message_outCharMESSAGE_U4request_id_outCharREQUEST_ID_U5p_row_idin/outCharROW_IDU6p_source_organization_idinNumberSOURCE_ORGANIZATION_IDU7p_target_organization_idinNumberTARGET_ORGANIZATION_IDU8p_assembly_item_idinNumberASSEMBLY_ITEM_IDU9p_bill_sequence_idinNumberBILL_SEQUENCE_IDU10p_alternate_bom_designatorinCharALTERNATE_BOMU11p_enable_attrs_updateinCharENABLE_ATTRS_UPDATEU12p_source_organization_codeinCharSOURCE_ORGANIZATIONU13p_target_organization_codeinCharTARGET_ORGANIZATIONU14p_assembly_iteminCharASSEMBLY_ITEMU15p_create_common_bom_flaginCharCREATE_COMMON_BOMOrder By source_organization,
target_organization,
decode(is_bom_defined_in_tgt,'Yes',2,1),
assembly_item,
assembly_description,
alternate_bomSuccess SQLselect
xu.action_,
xu.status_,
xu.message_,
xu.request_id_ request_id_,
xu.modified_columns_,
xu.row_id,
xu.enable_attrs_update,
--
decode(xu.status_code_,xxen_upload.status_success,to_char(null),xu.create_common_bom) create_common_bom,
--
xu.source_organization,
xu.assembly_item,
xu.assembly_description,
xu.alternate_bom,
--
xu.target_organization,
--
nvl((select 'Yes' from mtl_system_items_vl msiv2 where msiv2.organization_id = to_number(xu.target_organization_id) and msiv2.inventory_item_id = to_number(xu.assembly_item_id)),'No') is_item_defined_in_tgt,
nvl((select 'Yes' from bom_bill_of_materials bbom2 where bbom2.organization_id = to_number(xu.target_organization_id) and bbom2.assembly_item_id = to_number(xu.assembly_item_id) and nvl(bbom2.alternate_bom_designator,'?') = nvl(xu.alternate_bom,'?') and rownum <= 1),'No') is_bom_defined_in_tgt,
nvl((select 'Yes' from bom_bill_of_materials bbom2 where bbom2.organization_id = to_number(xu.target_organization_id) and bbom2.common_organization_id = to_number(xu.source_organization_id) and bbom2.common_assembly_item_id = to_number(xu.assembly_item_id) and rownum <= 1),'No') is_common_bom_in_tgt,
ltrim(rtrim(
(select distinct
listagg(msiv.concatenated_segments,',') within group (order by msiv.concatenated_segments)
from bom_inventory_components bic,
mtl_system_items_vl msiv
where bic.bill_sequence_id = to_number(xu.bill_sequence_id) and
msiv.organization_id = to_number(xu.source_organization_id) and
msiv.inventory_item_id = bic.component_item_id and
not exists (select null from mtl_system_items_vl msiv2 where msiv2.organization_id = to_number(xu.target_organization_id) and msiv2.inventory_item_id = bic.component_item_id)
) || ',' ||
(select distinct
listagg(msiv.concatenated_segments,',') within group (order by msiv.concatenated_segments)
from bom_inventory_components bic,
bom_substitute_components bsc,
mtl_system_items_vl msiv
where bic.bill_sequence_id = to_number(xu.bill_sequence_id) and
bsc.component_sequence_id = bic.component_sequence_id and
msiv.organization_id = to_number(xu.source_organization_id) and
msiv.inventory_item_id = bsc.substitute_component_id and
not exists (select null from mtl_system_items_vl msiv2 where msiv2.organization_id = to_number(xu.target_organization_id) and msiv2.inventory_item_id = bsc.substitute_component_id)
),','),',') comp_items_not_defined_in_tgt,
--
to_number(xu.bill_sequence_id),
to_number(xu.assembly_item_id),
to_number(xu.source_organization_id),
to_number(xu.target_organization_id)
from
xxen_bom_common_bill_of_8384_u xuDefault value:$flex$.enable_attributes_updateEnable Attributes Update|:p_enable_attrs_update|ENABLE_ATTRS_UPDATE