Estructuras de Artículos (`alp_cat_inv_structure_items`)

1. Modulo

Inventarios (INV)

2. Descripción

Catálogo de Estructuras y Listas de Materiales (BOM) en Oracle Cloud

3. Columnas

No.	Columna	Descripción
1	`org_id`	Identificador único de la organización.
2	`item_id`	Identificador único del artículo base en el inventario.
3	`org_code`	Código corto o nomenclatura de la organización.
4	`structure`	Número de artículo que define la estructura principal (artículo padre).
5	`structure_name`	Nombre alternativo de la lista de materiales (BOM) o estructura.
6	`structure_description`	Descripción traducida del artículo de la estructura principal.
7	`item_sequence`	Número de secuencia del componente dentro de la estructura.
8	`item_code`	Código o número del artículo componente.
9	`item`	Descripción traducida del artículo componente.
10	`start_date`	Fecha de inicio de vigencia/efectividad del componente en la estructura.
11	`end_date`	Fecha de fin de vigencia o deshabilitación del componente.
12	`quantity`	Cantidad requerida del componente en la estructura.
13	`inverse_quantity`	Cálculo truncado a 5 decimales de la proporción inversa de la cantidad.
14	`structure_item_type_code`	Código del tipo de artículo en la estructura (ej. estándar, modelo, opción).
15	`structure_item_type`	Significado o descripción amigable del tipo de artículo de la estructura.
16	`enforce_integer_requirement_code`	Código que define si se exige que la cantidad del componente sea un número entero.
17	`enforce_integer_requirement`	Descripción de la regla de obligatoriedad de requerimiento entero.
18	`basis_code`	Código del tipo de base para el cálculo del componente (ej. por lote o por unidad).
19	`basis`	Descripción o significado del tipo de base.
20	`planning_percent`	Porcentaje o factor de planeación asignado al componente.
21	`yield`	Factor de rendimiento o merma del componente (representa la eficiencia).
22	`component_uom_code`	Código de la unidad de medida primaria del componente.
23	`component_uom`	Nombre completo de la unidad de medida del componente.
24	`find_number`	Número de secuencia de la operación donde se consume el componente.

4. Objesto 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`	Almacena la información detallada de cada componente que integra una Lista de Materiales (BOM / Estructura de Producto.
2	fscm_fin_aes_lookupvaluesextractpvo	`fnd_lookup_values`	Catálogo maestro de códigos y Lookups generales del sistema financiero.
3	fscm_fin_aes_lookupvaluestlextractpvo	`fnd_lookup_values_tl`	Traducciones de los códigos Lookup generales de finanzas.
4	fscm_scm_egp_itemextractpvo	`egp_system_items_b`	Maestro de artículos, productos y SKUs (System Items).
5	fscm_scm_egp_itemtranslationextractpvo	`egp_system_items_tl`	Descripciones y nombres comerciales traducidos de los SKUs.
6	fscm_scm_egp_structuresextractpvo	`egp_structures_b`	Estructuras de productos que definen la composición y los componentes asociados a cada artículo.
7	fscm_scm_scmrcs_invorgparametersextractpvo	`inv_org_parameters`	Parámetros de configuración de las organizaciones de inventario.
8	inv_units_of_measure	`inv_units_of_measure`	Unidades de medida de inventarios (Piezas, Litros, Kilos).

5. Querys

Databricks DEV
Databricks UAT
Databricks PROD
Oracle

with    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')
select  iop.organizationid                                                          org_id
,   esib.itembasepeoinventoryitemid                                             item_id 
,   iop.organizationcode                                                        org_code
,   esib.itembasepeoitemnumber                                                  structure
,   esb.structurespeoalternatebomdesignator                                     structure_name
,   esit.itemtranslationpeodescription                                          structure_description
,   ecb.item_num                                                                item_sequence
,   esibi.itembasepeoitemnumber                                                 item_code
,   esiti.itemtranslationpeodescription                                         item
,   ecb.effectivity_date                                                        start_date
,   ecb.disable_date                                                            end_date
,   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.primary_uom_code                                                        component_uom_code
,   iuom.unit_of_measure                                                        component_uom
,   ecb.operation_seq_num                                                       find_number
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
join    cat_master_oracle_fusion.sch_gold_layer.fscm_scm_scmrcs_invorgparametersextractpvo_dev  iop  
    on      esb.structurespeopk2value           =   iop.organizationid
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'
order by    esib.itembasepeoitemnumber
,       ecb.item_num

with    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')
select  iop.organizationid                                                          org_id
,   esib.itembasepeoinventoryitemid                                             item_id 
,   iop.organizationcode                                                        org_code
,   esib.itembasepeoitemnumber                                                  structure
,   esb.structurespeoalternatebomdesignator                                     structure_name
,   esit.itemtranslationpeodescription                                          structure_description
,   ecb.item_num                                                                item_sequence
,   esibi.itembasepeoitemnumber                                                 item_code
,   esiti.itemtranslationpeodescription                                         item
,   ecb.effectivity_date                                                        start_date
,   ecb.disable_date                                                            end_date
,   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.primary_uom_code                                                        component_uom_code
,   iuom.unit_of_measure                                                        component_uom
,   ecb.operation_seq_num                                                       find_number
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
join    cat_master_oracle_fusion.sch_gold_layer.fscm_scm_scmrcs_invorgparametersextractpvo_stg  iop  
    on      esb.structurespeopk2value           =   iop.organizationid
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'
order by    esib.itembasepeoitemnumber
,       ecb.item_num    

with    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')
select  iop.organizationid                                                          org_id
,   esib.itembasepeoinventoryitemid                                             item_id 
,   iop.organizationcode                                                        org_code
,   esib.itembasepeoitemnumber                                                  structure
,   esb.structurespeoalternatebomdesignator                                     structure_name
,   esit.itemtranslationpeodescription                                          structure_description
,   ecb.item_num                                                                item_sequence
,   esibi.itembasepeoitemnumber                                                 item_code
,   esiti.itemtranslationpeodescription                                         item
,   ecb.effectivity_date                                                        start_date
,   ecb.disable_date                                                            end_date
,   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.primary_uom_code                                                        component_uom_code
,   iuom.unit_of_measure                                                        component_uom
,   ecb.operation_seq_num                                                       find_number
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
join    cat_master_oracle_fusion.sch_gold_layer.fscm_scm_scmrcs_invorgparametersextractpvo_prd  iop  
    on      esb.structurespeopk2value           =   iop.organizationid
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'
order by    esib.itembasepeoitemnumber
,       ecb.item_num 

SELECT  iop.organization_id                                     org_id
,   esib.inventory_item_id                                  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
,   ecb.effectivity_date                                    start_date
,   ecb.disable_date                                        end_date
,   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.primary_uom_code                                    component_uom_code
,   iuom.unit_of_measure                                    component_uom
,   ecb.operation_seq_num                                   find_number
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
,   fnd_lookup_values     flv1
,   fnd_lookup_values     flv2
,   fnd_lookup_values     flv3
,   inv_units_of_measure  iuom
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     esibi.bom_item_type           = flv1.lookup_code
AND     ecb.enforce_int_requirements  = flv2.lookup_code
AND     ecb.basis_type                = flv3.lookup_code
AND     ecb.primary_uom_code          = iuom.uom_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     flv1.language                 = 'E'
AND     flv2.language                 = 'E'
AND     flv3.language                 = 'E'
ORDER BY  esib.item_number
,     ecb.item_num

1. Modulo​

2. Descripción​

3. Columnas​

4. Objesto Databricks - Oracle​

5. Querys​

1. Modulo

2. Descripción

3. Columnas

4. Objesto Databricks - Oracle

5. Querys