Estructuras de Artículos (alp_cat_inv_structure_items)
1. Módulo
Inventarios (INV) / Manufactura (SCM)
2. Descripción
Catálogo de Estructuras y Listas de Materiales (BOM - Bill of Materials) jerárquicas y multinivel. Permite explotar recursivamente la composición completa de los productos terminados, subensambles, mermas, reemplazos y componentes en Databricks y Oracle Cloud.
3. Columnas
| No. | Columna | Descripción |
|---|---|---|
| 1 | org_id | Identificador único de la organización del producto raíz. |
| 2 | item_id | Identificador único del artículo raíz en inventarios. |
| 3 | org_code | Código o nomenclatura de la organización de manufactura. |
| 4 | structure | Código del artículo principal que encabeza la estructura (producto terminado raíz). |
| 5 | structure_name | Nombre alternativo de la lista de materiales (Alternate BOM Designator). |
| 6 | structure_description | Descripción comercial traducida del artículo de la estructura principal. |
| 7 | item_sequence | Secuencia jerárquica formateada por nivel (ej. 10.20.10). |
| 8 | item_code | Código o SKU del componente/ingrediente actual en este nivel. |
| 9 | item | Descripción traducida del artículo componente. |
| 10 | replaces | Código del artículo al cual sustituye o reemplaza este componente (en caso de sustitutos). |
| 11 | quantity | Cantidad requerida del componente para la estructura. |
| 12 | inverse_quantity | Factor de proporción inversa truncado a 5 decimales. |
| 13 | structure_item_type_code | Código del tipo de artículo en la estructura (estándar, modelo, etc.). |
| 14 | structure_item_type | Descripción legible del tipo de componente. |
| 15 | enforce_integer_requirement_code | Código que define si se exige cantidad entera. |
| 16 | enforce_integer_requirement | Regla descriptiva de requerimiento entero. |
| 17 | basis_code | Código del tipo de base de consumo (por lote o por unidad). |
| 18 | basis | Descripción legible de la base de consumo. |
| 19 | planning_percent | Porcentaje de planeación asignado al componente. |
| 20 | yield | Factor de rendimiento o merma del componente. |
| 21 | comments | Observaciones y comentarios registrados en la línea del componente. |
| 22 | component_uom_code | Código de la unidad de medida primaria del componente. |
| 23 | component_uom | Nombre traducido de la unidad de medida. |
| 24 | find_number | Número de secuencia u operación donde se consume el componente. |
| 25 | nivel | Nivel jerárquico dentro del árbol de explosión (1 = Raíz directa, 2 = Subensamble, etc.). |
| 26 | ruta | Trazabilidad completa de la jerarquía separada por barras (`Raíz |
| 27 | item_padre | Código del artículo padre directo del cual depende este componente. |
4. Objetos Databricks - Oracle
Nota de Arquitectura: Convención de Nomenclatura
Las entidades listadas en este documento corresponden a la capa final de datos en Databricks. Para identificar la persistencia y el aislamiento de los datos según el ciclo de vida del software, se implementa la siguiente estrategia de sufijos por entorno:
- 🥉 Desarrollo (DEV): Sufijo
_dev(ej.nombre_tabla_dev). - 🥈 Pruebas (UAT): Sufijo
_stg(ej.nombre_tabla_stg). - 🥇 Producción (PROD): Sufijo
_prd(ej.nombre_tabla_prd).
| No. | Databricks | Oracle Fusion | Descripción Operativa / Módulo |
|---|---|---|---|
| 1 | egp_components_b | egp_components_b | Registra el detalle de componentes, cantidades, mermas y secuencias de cada lista de materiales. |
| 2 | fscm_fin_aes_lookupvaluesextractpvo | fnd_lookup_values | Catálogo maestro de códigos Lookup generales. |
| 3 | fscm_fin_aes_lookupvaluestlextractpvo | fnd_lookup_values_tl | Traducciones multilingües de códigos Lookup generales. |
| 4 | fscm_scm_egp_itemextractpvo | egp_system_items_b | Catálogo maestro de artículos, SKUs e insumos. |
| 5 | fscm_scm_egp_itemtranslationextractpvo | egp_system_items_tl | Nombres y descripciones comerciales traducidas de los artículos. |
| 6 | fscm_scm_egp_structuresextractpvo | egp_structures_b | Cabeceras de estructuras de manufactura y listas de materiales (BOM). |
| 7 | fscm_scm_scmrcs_invorgparametersextractpvo | inv_org_parameters | Configuración de parámetros por organización de inventarios. |
| 8 | inv_units_of_measure | inv_units_of_measure | Catálogo de unidades de medida estándar de inventario. |
5. Consultas
- Databricks DEV
- Databricks UAT
- Databricks PROD
- Oracle
WITH RECURSIVE alp_lookups AS
( SELECT flv.lookuptype
, flv.lookupcode
, flvt.meaning
FROM cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluesextractpvo_dev flv
JOIN cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo_dev flvt
ON flv.lookuptype = flvt.lookuptype
AND flv.lookupcode = flvt.lookupcode
AND flv.setid = flvt.setid
AND flv.viewapplicationid = flvt.viewapplicationid
AND flvt.language = 'E'
WHERE flv.lookuptype IN ('EGP_BOM_ITEM_TYPE', 'EGP_SUB_COMP_ENFORCE_INT_REQ', 'EGP_BOM_BASIS_TYPE'))
, alp_bom_base AS
( SELECT esb.structurespeopk2value father_org_id
, esb.structurespeopk1value father_item_id
, iop.organizationcode org_code
, esib.itembasepeoitemnumber structure
, esb.structurespeoalternatebomdesignator structure_name
, esit.itemtranslationpeodescription structure_description
, CAST(ecb.item_num AS BIGINT) item_sequence
, esibi.itembasepeoitemnumber item_code
, esiti.itemtranslationpeodescription item
, esibr.itembasepeoitemnumber replaces
, ecb.component_quantity quantity
, FLOOR((ecb.quantity_related / ecb.component_quantity) * 100000) / 100000 inverse_quantity
, esibi.itembasepeobomitemtype structure_item_type_code
, al1.meaning structure_item_type
, ecb.enforce_int_requirements enforce_integer_requirement_code
, al2.meaning enforce_integer_requirement
, ecb.basis_type basis_code
, al3.meaning basis
, ecb.planning_factor planning_percent
, ecb.component_yield_factor yield
, ecb.component_remarks comments
, ecb.primary_uom_code component_uom_code
, iuom.unit_of_measure component_uom
, ecb.operation_seq_num find_number
, ecb.pk2_value son_org_id
, ecb.pk1_value son_item_id
FROM cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_structuresextractpvo_dev esb
JOIN cat_master_oracle_fusion.sch_gold_layer.egp_components_b_dev ecb
ON esb.structurespeobillsequenceid = ecb.bill_sequence_id
AND ecb.disable_date IS NULL
AND ecb.implementation_date IS NOT NULL
JOIN cat_master_oracle_fusion.sch_gold_layer.fscm_scm_scmrcs_invorgparametersextractpvo_dev iop
ON esb.structurespeopk2value = iop.organizationid
LEFT JOIN cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemextractpvo_dev esibr
ON ecb.replaced_for_component = esibr.itembasepeoinventoryitemid
AND ecb.pk2_value = esibr.itembasepeoorganizationid
JOIN cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemextractpvo_dev esib
ON esb.structurespeopk1value = esib.itembasepeoinventoryitemid
AND esb.structurespeopk2value = esib.itembasepeoorganizationid
JOIN cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemtranslationextractpvo_dev esit
ON esib.itembasepeoinventoryitemid = esit.itemtranslationpeoinventoryitemid
AND esib.itembasepeoorganizationid = esit.itemtranslationpeoorganizationid
AND esit.itemtranslationpeolanguage = 'E'
JOIN cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemextractpvo_dev esibi
ON ecb.pk1_value = esibi.itembasepeoinventoryitemid
AND ecb.pk2_value = esibi.itembasepeoorganizationid
JOIN cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemtranslationextractpvo_dev esiti
ON esibi.itembasepeoinventoryitemid = esiti.itemtranslationpeoinventoryitemid
AND esibi.itembasepeoorganizationid = esiti.itemtranslationpeoorganizationid
AND esiti.itemtranslationpeolanguage = 'E'
JOIN cat_master_oracle_fusion.sch_gold_layer.inv_units_of_measure_dev iuom
ON ecb.primary_uom_code = iuom.uom_code
JOIN alp_lookups al1
ON esibi.itembasepeobomitemtype = al1.lookupcode
AND al1.lookuptype = 'EGP_BOM_ITEM_TYPE'
JOIN alp_lookups al2
ON ecb.enforce_int_requirements = al2.lookupcode
AND al2.lookuptype = 'EGP_SUB_COMP_ENFORCE_INT_REQ'
JOIN alp_lookups al3
ON ecb.basis_type = al3.lookupcode
AND al3.lookuptype = 'EGP_BOM_BASIS_TYPE')
, alp_bom_recursive AS
( SELECT b.father_org_id
, b.father_item_id
, b.org_code
, b.structure root_structure
, b.structure_name root_structure_name
, b.structure_description root_structure_description
, b.item_sequence
, CAST(b.item_sequence AS STRING) item_sequence_hierarchy
, b.item_code
, b.item
, b.replaces
, b.quantity
, b.inverse_quantity
, b.structure_item_type_code
, b.structure_item_type
, b.enforce_integer_requirement_code
, b.enforce_integer_requirement
, b.basis_code
, b.basis
, b.planning_percent
, b.yield
, b.comments
, b.component_uom_code
, b.component_uom
, b.find_number
, 1 nivel
, b.structure item_padre
, ARRAY(b.structure) ruta_array
, ARRAY(b.structure) estructuras_visitadas
, b.son_org_id
, b.son_item_id
FROM alp_bom_base b
UNION ALL
SELECT h.father_org_id
, h.father_item_id
, r.org_code
, r.root_structure
, r.root_structure_name
, r.root_structure_description
, h.item_sequence
, CAST(CONCAT(r.item_sequence_hierarchy, '.', CAST(h.item_sequence AS STRING)) AS STRING) item_sequence_hierarchy
, h.item_code
, h.item
, h.replaces
, h.quantity
, h.inverse_quantity
, h.structure_item_type_code
, h.structure_item_type
, h.enforce_integer_requirement_code
, h.enforce_integer_requirement
, h.basis_code
, h.basis
, h.planning_percent
, h.yield
, h.comments
, h.component_uom_code
, h.component_uom
, h.find_number
, r.nivel + 1 nivel
, r.item_code item_padre
, CONCAT(r.ruta_array, ARRAY(r.item_code)) ruta_array
, CONCAT(r.estructuras_visitadas, ARRAY(h.structure)) estructuras_visitadas
, h.son_org_id
, h.son_item_id
FROM alp_bom_recursive r
JOIN alp_bom_base h
ON h.structure_name = r.root_structure_name
AND h.father_org_id = r.son_org_id
AND h.father_item_id = r.son_item_id
WHERE NOT array_contains(r.estructuras_visitadas, h.structure))
SELECT father_org_id org_id
, father_item_id item_id
, org_code
, root_structure structure
, root_structure_name structure_name
, root_structure_description structure_description
, item_sequence_hierarchy item_sequence
, item_code
, item
, replaces
, quantity
, inverse_quantity
, structure_item_type_code
, structure_item_type
, enforce_integer_requirement_code
, enforce_integer_requirement
, basis_code
, basis
, planning_percent
, yield
, comments
, component_uom_code
, component_uom
, find_number
, nivel
, array_join(ruta_array, ' | ') ruta
, item_padre
FROM alp_bom_recursive
GROUP BY father_org_id
, father_item_id
, org_code
, root_structure
, root_structure_name
, root_structure_description
, item_sequence_hierarchy
, item_code
, item
, replaces
, quantity
, inverse_quantity
, structure_item_type_code
, structure_item_type
, enforce_integer_requirement_code
, enforce_integer_requirement
, basis_code
, basis
, planning_percent
, yield
, comments
, component_uom_code
, component_uom
, find_number
, nivel
, array_join(ruta_array, ' | ')
, item_padre
ORDER BY root_structure
, root_structure_name
, item_sequence_hierarchy
WITH RECURSIVE alp_lookups AS
( SELECT flv.lookuptype
, flv.lookupcode
, flvt.meaning
FROM cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluesextractpvo_stg flv
JOIN cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo_stg flvt
ON flv.lookuptype = flvt.lookuptype
AND flv.lookupcode = flvt.lookupcode
AND flv.setid = flvt.setid
AND flv.viewapplicationid = flvt.viewapplicationid
AND flvt.language = 'E'
WHERE flv.lookuptype IN ('EGP_BOM_ITEM_TYPE', 'EGP_SUB_COMP_ENFORCE_INT_REQ', 'EGP_BOM_BASIS_TYPE'))
, alp_bom_base AS
( SELECT esb.structurespeopk2value father_org_id
, esb.structurespeopk1value father_item_id
, iop.organizationcode org_code
, esib.itembasepeoitemnumber structure
, esb.structurespeoalternatebomdesignator structure_name
, esit.itemtranslationpeodescription structure_description
, CAST(ecb.item_num AS BIGINT) item_sequence
, esibi.itembasepeoitemnumber item_code
, esiti.itemtranslationpeodescription item
, esibr.itembasepeoitemnumber replaces
, ecb.component_quantity quantity
, FLOOR((ecb.quantity_related / ecb.component_quantity) * 100000) / 100000 inverse_quantity
, esibi.itembasepeobomitemtype structure_item_type_code
, al1.meaning structure_item_type
, ecb.enforce_int_requirements enforce_integer_requirement_code
, al2.meaning enforce_integer_requirement
, ecb.basis_type basis_code
, al3.meaning basis
, ecb.planning_factor planning_percent
, ecb.component_yield_factor yield
, ecb.component_remarks comments
, ecb.primary_uom_code component_uom_code
, iuom.unit_of_measure component_uom
, ecb.operation_seq_num find_number
, ecb.pk2_value son_org_id
, ecb.pk1_value son_item_id
FROM cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_structuresextractpvo_stg esb
JOIN cat_master_oracle_fusion.sch_gold_layer.egp_components_b_stg ecb
ON esb.structurespeobillsequenceid = ecb.bill_sequence_id
AND ecb.disable_date IS NULL
AND ecb.implementation_date IS NOT NULL
JOIN cat_master_oracle_fusion.sch_gold_layer.fscm_scm_scmrcs_invorgparametersextractpvo_stg iop
ON esb.structurespeopk2value = iop.organizationid
LEFT JOIN cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemextractpvo_stg esibr
ON ecb.replaced_for_component = esibr.itembasepeoinventoryitemid
AND ecb.pk2_value = esibr.itembasepeoorganizationid
JOIN cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemextractpvo_stg esib
ON esb.structurespeopk1value = esib.itembasepeoinventoryitemid
AND esb.structurespeopk2value = esib.itembasepeoorganizationid
JOIN cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemtranslationextractpvo_stg esit
ON esib.itembasepeoinventoryitemid = esit.itemtranslationpeoinventoryitemid
AND esib.itembasepeoorganizationid = esit.itemtranslationpeoorganizationid
AND esit.itemtranslationpeolanguage = 'E'
JOIN cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemextractpvo_stg esibi
ON ecb.pk1_value = esibi.itembasepeoinventoryitemid
AND ecb.pk2_value = esibi.itembasepeoorganizationid
JOIN cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemtranslationextractpvo_stg esiti
ON esibi.itembasepeoinventoryitemid = esiti.itemtranslationpeoinventoryitemid
AND esibi.itembasepeoorganizationid = esiti.itemtranslationpeoorganizationid
AND esiti.itemtranslationpeolanguage = 'E'
JOIN cat_master_oracle_fusion.sch_gold_layer.inv_units_of_measure_stg iuom
ON ecb.primary_uom_code = iuom.uom_code
JOIN alp_lookups al1
ON esibi.itembasepeobomitemtype = al1.lookupcode
AND al1.lookuptype = 'EGP_BOM_ITEM_TYPE'
JOIN alp_lookups al2
ON ecb.enforce_int_requirements = al2.lookupcode
AND al2.lookuptype = 'EGP_SUB_COMP_ENFORCE_INT_REQ'
JOIN alp_lookups al3
ON ecb.basis_type = al3.lookupcode
AND al3.lookuptype = 'EGP_BOM_BASIS_TYPE')
, alp_bom_recursive AS
( SELECT b.father_org_id
, b.father_item_id
, b.org_code
, b.structure root_structure
, b.structure_name root_structure_name
, b.structure_description root_structure_description
, b.item_sequence
, CAST(b.item_sequence AS STRING) item_sequence_hierarchy
, b.item_code
, b.item
, b.replaces
, b.quantity
, b.inverse_quantity
, b.structure_item_type_code
, b.structure_item_type
, b.enforce_integer_requirement_code
, b.enforce_integer_requirement
, b.basis_code
, b.basis
, b.planning_percent
, b.yield
, b.comments
, b.component_uom_code
, b.component_uom
, b.find_number
, 1 nivel
, b.structure item_padre
, ARRAY(b.structure) ruta_array
, ARRAY(b.structure) estructuras_visitadas
, b.son_org_id
, b.son_item_id
FROM alp_bom_base b
UNION ALL
SELECT h.father_org_id
, h.father_item_id
, r.org_code
, r.root_structure
, r.root_structure_name
, r.root_structure_description
, h.item_sequence
, CAST(CONCAT(r.item_sequence_hierarchy, '.', CAST(h.item_sequence AS STRING)) AS STRING) item_sequence_hierarchy
, h.item_code
, h.item
, h.replaces
, h.quantity
, h.inverse_quantity
, h.structure_item_type_code
, h.structure_item_type
, h.enforce_integer_requirement_code
, h.enforce_integer_requirement
, h.basis_code
, h.basis
, h.planning_percent
, h.yield
, h.comments
, h.component_uom_code
, h.component_uom
, h.find_number
, r.nivel + 1 nivel
, r.item_code item_padre
, CONCAT(r.ruta_array, ARRAY(r.item_code)) ruta_array
, CONCAT(r.estructuras_visitadas, ARRAY(h.structure)) estructuras_visitadas
, h.son_org_id
, h.son_item_id
FROM alp_bom_recursive r
JOIN alp_bom_base h
ON h.structure_name = r.root_structure_name
AND h.father_org_id = r.son_org_id
AND h.father_item_id = r.son_item_id
WHERE NOT array_contains(r.estructuras_visitadas, h.structure))
SELECT father_org_id org_id
, father_item_id item_id
, org_code
, root_structure structure
, root_structure_name structure_name
, root_structure_description structure_description
, item_sequence_hierarchy item_sequence
, item_code
, item
, replaces
, quantity
, inverse_quantity
, structure_item_type_code
, structure_item_type
, enforce_integer_requirement_code
, enforce_integer_requirement
, basis_code
, basis
, planning_percent
, yield
, comments
, component_uom_code
, component_uom
, find_number
, nivel
, array_join(ruta_array, ' | ') ruta
, item_padre
FROM alp_bom_recursive
GROUP BY father_org_id
, father_item_id
, org_code
, root_structure
, root_structure_name
, root_structure_description
, item_sequence_hierarchy
, item_code
, item
, replaces
, quantity
, inverse_quantity
, structure_item_type_code
, structure_item_type
, enforce_integer_requirement_code
, enforce_integer_requirement
, basis_code
, basis
, planning_percent
, yield
, comments
, component_uom_code
, component_uom
, find_number
, nivel
, array_join(ruta_array, ' | ')
, item_padre
ORDER BY root_structure
, root_structure_name
, item_sequence_hierarchy
WITH RECURSIVE alp_lookups AS
( SELECT flv.lookuptype
, flv.lookupcode
, flvt.meaning
FROM cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluesextractpvo_prd flv
JOIN cat_master_oracle_fusion.sch_gold_layer.fscm_fin_aes_lookupvaluestlextractpvo_prd flvt
ON flv.lookuptype = flvt.lookuptype
AND flv.lookupcode = flvt.lookupcode
AND flv.setid = flvt.setid
AND flv.viewapplicationid = flvt.viewapplicationid
AND flvt.language = 'E'
WHERE flv.lookuptype IN ('EGP_BOM_ITEM_TYPE', 'EGP_SUB_COMP_ENFORCE_INT_REQ', 'EGP_BOM_BASIS_TYPE'))
, alp_bom_base AS
( SELECT esb.structurespeopk2value father_org_id
, esb.structurespeopk1value father_item_id
, iop.organizationcode org_code
, esib.itembasepeoitemnumber structure
, esb.structurespeoalternatebomdesignator structure_name
, esit.itemtranslationpeodescription structure_description
, CAST(ecb.item_num AS BIGINT) item_sequence
, esibi.itembasepeoitemnumber item_code
, esiti.itemtranslationpeodescription item
, esibr.itembasepeoitemnumber replaces
, ecb.component_quantity quantity
, FLOOR((ecb.quantity_related / ecb.component_quantity) * 100000) / 100000 inverse_quantity
, esibi.itembasepeobomitemtype structure_item_type_code
, al1.meaning structure_item_type
, ecb.enforce_int_requirements enforce_integer_requirement_code
, al2.meaning enforce_integer_requirement
, ecb.basis_type basis_code
, al3.meaning basis
, ecb.planning_factor planning_percent
, ecb.component_yield_factor yield
, ecb.component_remarks comments
, ecb.primary_uom_code component_uom_code
, iuom.unit_of_measure component_uom
, ecb.operation_seq_num find_number
, ecb.pk2_value son_org_id
, ecb.pk1_value son_item_id
FROM cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_structuresextractpvo_prd esb
JOIN cat_master_oracle_fusion.sch_gold_layer.egp_components_b_prd ecb
ON esb.structurespeobillsequenceid = ecb.bill_sequence_id
AND ecb.disable_date IS NULL
AND ecb.implementation_date IS NOT NULL
JOIN cat_master_oracle_fusion.sch_gold_layer.fscm_scm_scmrcs_invorgparametersextractpvo_prd iop
ON esb.structurespeopk2value = iop.organizationid
LEFT JOIN cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemextractpvo_prd esibr
ON ecb.replaced_for_component = esibr.itembasepeoinventoryitemid
AND ecb.pk2_value = esibr.itembasepeoorganizationid
JOIN cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemextractpvo_prd esib
ON esb.structurespeopk1value = esib.itembasepeoinventoryitemid
AND esb.structurespeopk2value = esib.itembasepeoorganizationid
JOIN cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemtranslationextractpvo_prd esit
ON esib.itembasepeoinventoryitemid = esit.itemtranslationpeoinventoryitemid
AND esib.itembasepeoorganizationid = esit.itemtranslationpeoorganizationid
AND esit.itemtranslationpeolanguage = 'E'
JOIN cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemextractpvo_prd esibi
ON ecb.pk1_value = esibi.itembasepeoinventoryitemid
AND ecb.pk2_value = esibi.itembasepeoorganizationid
JOIN cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemtranslationextractpvo_prd esiti
ON esibi.itembasepeoinventoryitemid = esiti.itemtranslationpeoinventoryitemid
AND esibi.itembasepeoorganizationid = esiti.itemtranslationpeoorganizationid
AND esiti.itemtranslationpeolanguage = 'E'
JOIN cat_master_oracle_fusion.sch_gold_layer.inv_units_of_measure_prd iuom
ON ecb.primary_uom_code = iuom.uom_code
JOIN alp_lookups al1
ON esibi.itembasepeobomitemtype = al1.lookupcode
AND al1.lookuptype = 'EGP_BOM_ITEM_TYPE'
JOIN alp_lookups al2
ON ecb.enforce_int_requirements = al2.lookupcode
AND al2.lookuptype = 'EGP_SUB_COMP_ENFORCE_INT_REQ'
JOIN alp_lookups al3
ON ecb.basis_type = al3.lookupcode
AND al3.lookuptype = 'EGP_BOM_BASIS_TYPE')
, alp_bom_recursive AS
( SELECT b.father_org_id
, b.father_item_id
, b.org_code
, b.structure root_structure
, b.structure_name root_structure_name
, b.structure_description root_structure_description
, b.item_sequence
, CAST(b.item_sequence AS STRING) item_sequence_hierarchy
, b.item_code
, b.item
, b.replaces
, b.quantity
, b.inverse_quantity
, b.structure_item_type_code
, b.structure_item_type
, b.enforce_integer_requirement_code
, b.enforce_integer_requirement
, b.basis_code
, b.basis
, b.planning_percent
, b.yield
, b.comments
, b.component_uom_code
, b.component_uom
, b.find_number
, 1 nivel
, b.structure item_padre
, ARRAY(b.structure) ruta_array
, ARRAY(b.structure) estructuras_visitadas
, b.son_org_id
, b.son_item_id
FROM alp_bom_base b
UNION ALL
SELECT h.father_org_id
, h.father_item_id
, r.org_code
, r.root_structure
, r.root_structure_name
, r.root_structure_description
, h.item_sequence
, CAST(CONCAT(r.item_sequence_hierarchy, '.', CAST(h.item_sequence AS STRING)) AS STRING) item_sequence_hierarchy
, h.item_code
, h.item
, h.replaces
, h.quantity
, h.inverse_quantity
, h.structure_item_type_code
, h.structure_item_type
, h.enforce_integer_requirement_code
, h.enforce_integer_requirement
, h.basis_code
, h.basis
, h.planning_percent
, h.yield
, h.comments
, h.component_uom_code
, h.component_uom
, h.find_number
, r.nivel + 1 nivel
, r.item_code item_padre
, CONCAT(r.ruta_array, ARRAY(r.item_code)) ruta_array
, CONCAT(r.estructuras_visitadas, ARRAY(h.structure)) estructuras_visitadas
, h.son_org_id
, h.son_item_id
FROM alp_bom_recursive r
JOIN alp_bom_base h
ON h.structure_name = r.root_structure_name
AND h.father_org_id = r.son_org_id
AND h.father_item_id = r.son_item_id
WHERE NOT array_contains(r.estructuras_visitadas, h.structure))
SELECT father_org_id org_id
, father_item_id item_id
, org_code
, root_structure structure
, root_structure_name structure_name
, root_structure_description structure_description
, item_sequence_hierarchy item_sequence
, item_code
, item
, replaces
, quantity
, inverse_quantity
, structure_item_type_code
, structure_item_type
, enforce_integer_requirement_code
, enforce_integer_requirement
, basis_code
, basis
, planning_percent
, yield
, comments
, component_uom_code
, component_uom
, find_number
, nivel
, array_join(ruta_array, ' | ') ruta
, item_padre
FROM alp_bom_recursive
GROUP BY father_org_id
, father_item_id
, org_code
, root_structure
, root_structure_name
, root_structure_description
, item_sequence_hierarchy
, item_code
, item
, replaces
, quantity
, inverse_quantity
, structure_item_type_code
, structure_item_type
, enforce_integer_requirement_code
, enforce_integer_requirement
, basis_code
, basis
, planning_percent
, yield
, comments
, component_uom_code
, component_uom
, find_number
, nivel
, array_join(ruta_array, ' | ')
, item_padre
ORDER BY root_structure
, root_structure_name
, item_sequence_hierarchy
WITH alp_bom_base AS
( SELECT iop.organization_id father_org_id
, esib.inventory_item_id father_item_id
, iop.organization_code org_code
, esib.item_number structure
, esb.alternate_bom_designator structure_name
, esit.description structure_description
, ecb.item_num item_sequence
, esibi.item_number item_code
, esiti.description item
, esibr.item_number replaces
, ecb.component_quantity quantity
, TRUNC(ecb.quantity_related / ecb.component_quantity, 5) inverse_quantity
, esibi.bom_item_type structure_item_type_code
, flv1.meaning structure_item_type
, ecb.enforce_int_requirements enforce_integer_requirement_code
, flv2.meaning enforce_integer_requirement
, ecb.basis_type basis_code
, flv3.meaning basis
, ecb.planning_factor planning_percent
, ecb.component_yield_factor yield
, ecb.component_remarks comments
, ecb.primary_uom_code component_uom_code
, iuom.unit_of_measure component_uom
, ecb.operation_seq_num find_number
, ecb.pk2_value son_org_id
, ecb.pk1_value son_item_id
FROM egp_structures_b esb
, egp_components_b ecb
, inv_org_parameters iop
, egp_system_items_b esib
, egp_system_items_tl esit
, egp_system_items_b esibi
, egp_system_items_tl esiti
, egp_system_items_b esibr
, inv_units_of_measure iuom
, fnd_lookup_values flv1
, fnd_lookup_values flv2
, fnd_lookup_values flv3
WHERE esb.bill_sequence_id = ecb.bill_sequence_id
AND esb.pk2_value = iop.organization_id
AND esb.pk2_value = esib.organization_id
AND esb.pk1_value = esib.inventory_item_id
AND esib.organization_id = esit.organization_id
AND esib.inventory_item_id = esit.inventory_item_id
AND ecb.pk2_value = esibi.organization_id
AND ecb.pk1_value = esibi.inventory_item_id
AND esibi.organization_id = esiti.organization_id
AND esibi.inventory_item_id = esiti.inventory_item_id
AND ecb.pk2_value = esibr.organization_id(+)
AND ecb.replaced_for_component = esibr.inventory_item_id(+)
AND ecb.primary_uom_code = iuom.uom_code
AND esibi.bom_item_type = flv1.lookup_code
AND ecb.enforce_int_requirements = flv2.lookup_code
AND ecb.basis_type = flv3.lookup_code
AND flv1.lookup_type = 'EGP_BOM_ITEM_TYPE'
AND flv2.lookup_type = 'EGP_SUB_COMP_ENFORCE_INT_REQ'
AND flv3.lookup_type = 'EGP_BOM_BASIS_TYPE'
AND esit.language = 'E'
AND esiti.language = 'E'
AND flv1.language = 'E'
AND flv2.language = 'E'
AND flv3.language = 'E'
AND ecb.disable_date IS NULL
AND ecb.implementation_date IS NOT NULL)
SELECT b.father_org_id org_id
, b.father_item_id item_id
, b.org_code
, CONNECT_BY_ROOT structure structure
, CONNECT_BY_ROOT structure_name structure_name
, CONNECT_BY_ROOT structure_description structure_description
, SUBSTR(SYS_CONNECT_BY_PATH(item_sequence, '.'),2) item_sequence
, b.item_code
, b.item
, b.replaces
, b.quantity
, b.inverse_quantity
, b.structure_item_type_code
, b.structure_item_type
, b.enforce_integer_requirement_code
, b.enforce_integer_requirement
, b.basis_code
, b.basis
, b.planning_percent
, b.yield
, b.comments
, b.component_uom_code
, b.component_uom
, b.find_number
, level nivel
, SUBSTR(SYS_CONNECT_BY_PATH(item_code, ' | '),4) ruta
, NVL(PRIOR item_code, b.structure) item_padre
FROM alp_bom_base b
START WITH b.structure = 'LESA810'
AND b.structure_name = 'Primary'
CONNECT BY NOCYCLE
PRIOR son_org_id = father_org_id
AND PRIOR son_item_id = father_item_id
AND NVL(PRIOR structure_name, 'PRINCIPAL') = NVL(structure_name, 'PRINCIPAL')