Catalogo alp_cat_inv_items
1. Modulo
Inventarios (INV)
2. Descripción
Catálogo maestro de artículos, SKUs, productos terminados, materias primas y unidades de medida.
3. Columnas
| # | Nombre de Columna | Descripción |
|---|---|---|
| 1 | item_id | Identificador único del artículo |
| 2 | org_id | Identificador de la organización |
| 3 | org_code | Código de la organización |
| 4 | org_name | Nombre de la organización |
| 5 | item | Número de artículo |
| 6 | description | Descripción corta |
| 7 | long_description | Descripción larga |
| 8 | item_type | Tipo de artículo (clasificación) |
| 9 | status | Estatus del artículo |
| 10 | lifecycle_phase | Fase del ciclo de vida |
| 11 | primary_uom_code | Código de la unidad de medida primaria |
| 12 | primary_uom_code_sat | Código de UOM primario (SAT) |
| 13 | primary_uom | Unidad de medida primaria |
| 14 | clave_anterior | Clave anterior del artículo |
| 15 | factor_kilolitros | Factor de conversión a kilolitros |
| 16 | cantidad_estandar | Cantidad estándar |
| 17 | articulo_granel | Indicador de artículo a granel |
| 18 | clave_producto_sat | Clave de producto ante el SAT |
| 19 | factor_leche | Factor de conversión de leche |
| 20 | formato_de_envasado | Formato de envasado |
| 21 | sabor | Sabor del producto |
| 22 | tipo_negocio | Tipo de negocio |
| 23 | area_impresion_etiqueta | Área de impresión para etiqueta |
| 24 | localizador_rendimiento | Localizador para rendimiento |
| 25 | localizador_suministro | Localizador para suministro |
| 26 | subinventario_rendimiento | Subinventario para rendimiento |
| 27 | cajas_por_cama | Cantidad de cajas por cama |
| 28 | cajas_por_tarima | Cantidad de cajas por tarima |
| 29 | camas_por_tarima | Cantidad de camas por tarima |
| 30 | piezas_por_caja | Piezas por caja |
| 31 | piezas_por_tarima | Piezas por tarima |
| 32 | tipo_de_caja | Tipo de caja |
| 33 | tipo_de_envase | Tipo de envase |
| 34 | tipo_de_tarima | Tipo de tarima |
| 35 | fraccion_arancelaria | Fracción arancelaria |
| 36 | objeto_de_impuesto | Objeto de impuesto |
| 37 | tiempo_de_cuarentena | Tiempo de cuarentena |
| 38 | subinventario_suministro | Subinventario de suministro |
| 39 | planta_origen | Planta de origen |
| 40 | facturacion_granel | Indicador de facturación a granel |
| 41 | contenido_neto_num | Contenido neto (numérico) |
| 42 | presentacion_de_venta_400 | Presentación de venta |
| 43 | vida_de_anaquel | Vida de anaquel |
| 44 | familia_as400 | Familia en sistema AS400 |
| 45 | porcentaje_grasa | Porcentaje de grasa |
| 46 | contenido_neto | Contenido neto |
| 47 | alp_admon_ventas_familia | Familia (Admon Ventas) |
| 48 | desc_alp_admon_ventas_familia | Descripción Familia (Admon Ventas) |
| 49 | alp_admon_ventas_presentacion | Presentación (Admon Ventas) |
| 50 | desc_alp_admon_ventas_presentacion | Descripción Presentación (Admon Ventas) |
| 51 | alp_producciones_familia | Familia (Producciones) |
| 52 | desc_alp_producciones_familia | Descripción Familia (Producciones) |
| 53 | alp_producciones_subfamilia | Subfamilia (Producciones) |
| 54 | desc_alp_producciones_subfamilia | Descripción Subfamilia (Producciones) |
| 55 | alp_producciones_presentacion | Presentación (Producciones) |
| 56 | desc_alp_producciones_presentacion | Descripción Presentación (Producciones) |
| 57 | alp_sop_macrocategoria | Macrocategoría (SOP) |
| 58 | desc_alp_sop_macrocategoria | Descripción Macrocategoría (SOP) |
| 59 | alp_sop_subcategoria | Subcategoría (SOP) |
| 60 | desc_alp_sop_subcategoria | Descripción Subcategoría (SOP) |
| 61 | alp_sop_producto_presentacion | Presentación Producto (SOP) |
| 62 | desc_alp_sop_producto_presentacion | Descripción Presentación Producto (SOP) |
| 63 | alp_sop_submarca | Submarca (SOP) |
| 64 | desc_alp_sop_submarca | Descripción Submarca (SOP) |
| 65 | alp_sop_subcategoria_comercial | Subcategoría Comercial (SOP) |
| 66 | desc_alp_sop_subcategoria_comercial | Descripción Subcategoría Comercial (SOP) |
| 67 | alp_cat_compras | Categoría de compras |
| 68 | alp_cst_cat | Categoría de costos |
| 69 | alp_inventarios | Categoría de inventarios |
| 70 | alp_cuenta_leche | Categoría cuenta de leche |
| 71 | alp_desperdicios | Categoría desperdicios |
| 72 | alp_reporte_cuenta_de_leche | Categoría reporte cuenta de leche |
| 73 | sku | SKU base |
| 74 | sku_caja | SKU por caja |
| 75 | sku_tarima | SKU por tarima |
| 76 | sku_charola | SKU por charola |
| 77 | manufactura_book_code | Código de libro de costos |
| 78 | manufactura_book | Nombre de libro de costos |
| 79 | manufactura_val_unit_code | Código de unidad de valuación |
| 80 | manufactura_cost | Costo de manufactura |
| 81 | manufactura_uom_code | UOM de manufactura |
| 82 | manufactura_currency | Moneda de manufactura |
| 83 | nombre_estructura | Nombre de la estructura (BOM) |
| 84 | nombre_proceso | Nombre del proceso |
| 85 | facturacion_impuesto_sobre_venta_code | Código de impuesto sobre venta |
| 86 | facturacion_impuesto_sobre_venta | Tasa de impuesto sobre venta |
4. Querys
- Databricks DEV
- Databricks UAT
- Databricks PROD
- Oracle
with alp_category as
( select mic.organizationid org_id
, mic.inventoryitemid item_id
, mcst.category_set_name category
, mcb.categorybasepeocategorycode code
from cat_master_oracle_fusion.sch_gold_layer.egp_category_sets_tl mcst
join cat_master_oracle_fusion.sch_gold_layer.fscm_egp_itemcategory mic
on mcst.category_set_id = mic.catalogbasepeocategorysetid
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_categoryextractpvo mcb
on mic.catalogbasepeodefaultcategoryid = mcb.categorybasepeocategoryid
where mcst.language = 'E')
, alp_costos as
( select row_number()
over( partition by nvl(csc.organization_id,iop.organizationid)
, csc.inventory_item_id
order by csc.scenario_id desc) reg
, nvl(csc.organization_id,iop.organizationid) org_id
, csc.inventory_item_id item_id
, ccbb.costbookbpeocostbookcode cost_book_code
, ccbt.costbooktlpeocostbookdesc cost_book
, cvub.valuationunitbpeovalunitcode cost_val_unit_code
, floor(csc.total_cost * 1e6) / 1e6 cost_manofactura
, csc.uom_code cost_uom_code
, csc.currency_code cost_currency
from cat_master_oracle_fusion.sch_gold_layer.cst_std_costs csc
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_cst_costbookextractpvo ccbb
on csc.cost_book_id = ccbb.costbookbpeocostbookid
and ccbb.costbookbpeocostbookcode = 'PRINCIPAL ESTANDAR'
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_cst_costbooktlextractpvo ccbt
on ccbb.costbookbpeocostbookid = ccbt.costbooktlpeocostbookid
and ccbt.costbooktlpeolanguage = 'E'
join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units haou
on csc.cost_org_id = haou.organization_id
and haou.name = 'GPLP COSTO MANF'
left join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_cst_valuationunitextractpvo cvub
on csc.val_unit_id = cvub.valuationunitbpeovalunitid
left join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_scmrcs_invorgparametersextractpvo iop
on split(cvub.valuationunitbpeovalunitcode, '-')[0] = iop.organizationcode
where current_date() between csc.effective_start_date
and csc.effective_end_date)
, alp_ot as
( select row_number()
over( partition by wwd.organizationid
, wwd.inventoryitemid
order by wwd.workdefinitionid desc) reg
, wwd.organizationid organization_id
, wwd.inventoryitemid inventory_item_id
, wwd.workdefinitionheadername work_definition_header_name
, wwd.statuscode status_code
, egs.structurespeoalternatebomdesignator alternate_bom_designator
from cat_master_oracle_fusion.sch_gold_layer.fscm_scm_wis_workdefinitionextractpvo wwd
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_structuresextractpvo egs
on wwd.billsequenceid = egs.structurespeobillsequenceid
where nvl(wwd.inactivedate,current_date()) >= current_date())
, alp_cat_fnd_flex_fields as
( select ffvs.flex_value_set_id
, ffv.valueid flex_value_id
, ffvs.flex_value_set_name
, ffv.value
, ffvt.description
, ffv.enabledflag enabled_flag
, ffv.startdateactive start_date
, ffv.enddateactive end_date
from cat_master_oracle_fusion.sch_gold_layer.fnd_flex_value_sets ffvs
join cat_master_oracle_fusion.sch_gold_layer.fscm_as_valuesetvaluespvo ffv
on ffvs.flex_value_set_id = ffv.valuesetid
join cat_master_oracle_fusion.sch_gold_layer.fscm_as_valuesetvaluestlpvo ffvt
on ffv.valueid = ffvt.valueid
and ffvt.language = 'E')
, alp_ego_item_eff_b as
( select row_number()
over( partition by organization_id
, inventory_item_id
, context_code
order by eff_line_id desc) reg
, organization_id
, inventory_item_id
, context_code
, attribute_number1
, attribute_number2
, attribute_number3
from cat_master_oracle_fusion.sch_gold_layer.ego_item_eff_b
where current_date() between version_start_date
and nvl(version_end_date,current_date()))
, alp_skus as
( select row_number()
over( partition by inventory_item_id
, uom_code
order by item_relationship_id desc) reg
, inventory_item_id item_id
, uom_code uom
, cross_reference sku
from cat_master_oracle_fusion.sch_gold_layer.egp_item_relationships_b
where sub_type = 'SKU')
select esyb.itembasepeoinventoryitemid item_id
, esyb.itembasepeoorganizationid org_id
, mp.organizationcode org_code
, haou.name org_name
, esyb.itembasepeoitemnumber item
, esyt.itemtranslationpeodescription description
, esyt.itemtranslationpeolongdescription long_description
, eict.itemclasstranpeoitemclassname item_type
, eist.inventory_item_status_name status
, esyb.itembasepeocurrentphasecode lifecycle_phase
, esyb.itembasepeoprimaryuomcode primary_uom_code
, iuom.attribute1 primary_uom_code_sat
, iuom.unit_of_measure primary_uom
, esyb.itembasepeoattribute1 clave_anterior
, esyb.itembasepeoattributenumber1 factor_kilolitros
, esyb.itembasepeoattributenumber2 cantidad_estandar
, esyb.itembasepeoattribute2 articulo_granel
, esyb.itembasepeoattribute3 clave_producto_sat
, esyb.itembasepeoattributenumber3 factor_leche
, esyb.itembasepeoattribute4 formato_de_envasado
, esyb.itembasepeoattribute5 sabor
, esyb.itembasepeoattribute6 tipo_negocio
, esyb.itembasepeoattribute7 area_impresion_etiqueta
, esyb.itembasepeoattribute8 localizador_rendimiento
, esyb.itembasepeoattribute9 localizador_suministro
, esyb.itembasepeoattribute10 subinventario_rendimiento
, esyb.itembasepeoattribute11 cajas_por_cama
, esyb.itembasepeoattribute12 cajas_por_tarima
, esyb.itembasepeoattribute13 camas_por_tarima
, esyb.itembasepeoattribute14 piezas_por_caja
, esyb.itembasepeoattribute15 piezas_por_tarima
, esyb.itembasepeoattribute16 tipo_de_caja
, esyb.itembasepeoattribute17 tipo_de_envase
, esyb.itembasepeoattribute18 tipo_de_tarima
, esyb.itembasepeoattribute19 fraccion_arancelaria
, esyb.itembasepeoattribute20 objeto_de_impuesto
, esyb.itembasepeoattribute21 tiempo_de_cuarentena
, esyb.itembasepeoattribute22 subinventario_suministro
, esyb.itembasepeoattribute23 planta_origen
, esyb.itembasepeoattribute24 facturacion_granel
, esyb.itembasepeoattributenumber4 contenido_neto_num
, esyb.itembasepeoattributenumber5 presentacion_de_venta_400
, esyb.itembasepeoattributenumber6 vida_de_anaquel
, esyb.itembasepeoattribute25 familia_as400
, esyb.itembasepeoattributenumber7 porcentaje_grasa
, esyb.itembasepeoattribute26 contenido_neto
, aav.attribute_number1 alp_admon_ventas_familia
, aavf1.description desc_alp_admon_ventas_familia
, aav.attribute_number2 alp_admon_ventas_presentacion
, aavf2.description desc_alp_admon_ventas_presentacion
, ap.attribute_number1 alp_producciones_familia
, ap1.description desc_alp_producciones_familia
, ap.attribute_number2 alp_producciones_subfamilia
, ap2.description desc_alp_producciones_subfamilia
, ap.attribute_number3 alp_producciones_presentacion
, ap3.description desc_alp_producciones_presentacion
, aso.attribute_char1 alp_sop_macrocategoria
, aso1.description desc_alp_sop_macrocategoria
, aso.attribute_char2 alp_sop_subcategoria
, aso2.description desc_alp_sop_subcategoria
, aso.attribute_number1 alp_sop_producto_presentacion
, aso3.description desc_alp_sop_producto_presentacion
, aso.attribute_char3 alp_sop_submarca
, aso4.description desc_alp_sop_submarca
, aso.attribute_number2 alp_sop_subcategoria_comercial
, aso5.description desc_alp_sop_subcategoria_comercial
, ac1.code alp_cat_compras
, ac2.code alp_cst_cat
, ac3.code alp_inventarios
, ac4.code alp_cuenta_leche
, ac5.code alp_desperdicios
, ac6.code alp_reporte_cuenta_de_leche
, eirb.sku
, eirbc.sku sku_caja
, eirbt.sku sku_tarima
, eirbch.sku sku_charola
, ac.cost_book_code manufactura_book_code
, ac.cost_book manufactura_book
, ac.cost_val_unit_code manufactura_val_unit_code
, ac.cost_manofactura manufactura_cost
, ac.cost_uom_code manufactura_uom_code
, ac.cost_currency manufactura_currency
, ao.work_definition_header_name nombre_estructura
, ao.alternate_bom_designator nombre_proceso
, esyb.itembasepeotaxcode facturacion_impuesto_sobre_venta_code
, tax.taxratespercentagerate facturacion_impuesto_sobre_venta
from cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemextractpvo esyb
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemtranslationextractpvo esyt
on esyb.itembasepeoorganizationid = esyt.itemtranslationpeoorganizationid
and esyb.itembasepeoinventoryitemid = esyt.itemtranslationpeoinventoryitemid
and esyt.itemtranslationpeolanguage = 'E'
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemclasstranslationextractpvo eict
on esyb.itembasepeoitemcataloggroupid = eict.itemclasstranpeoitemclassid
and eict.itemclasstranpeolanguage = 'E'
join cat_master_oracle_fusion.sch_gold_layer.egp_item_status_tl eist
on esyb.itembasepeoinventoryitemstatuscode = eist.inventory_item_status_code
and eist.language = 'E'
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_scmrcs_invorgparametersextractpvo mp
on esyb.itembasepeoorganizationid = mp.organizationid
join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units haou
on mp.organizationid = haou.organization_id
left join alp_skus eirb
on esyb.itembasepeoinventoryitemid = eirb.item_id
and eirb.uom IS NULL
and eirb.reg = 1
left join alp_skus eirbc
on esyb.itembasepeoinventoryitemid = eirbc.item_id
and eirbc.uom = 'CJA'
and eirbc.reg = 1
left join alp_skus eirbt
on esyb.itembasepeoinventoryitemid = eirbt.item_id
and eirbt.uom = 'TRM'
and eirbt.reg = 1
left join alp_skus eirbch
on esyb.itembasepeoinventoryitemid = eirbch.item_id
and eirbch.uom = 'TCH'
and eirbch.reg = 1
left join alp_ego_item_eff_b aav
on esyb.itembasepeoorganizationid = aav.organization_id
and esyb.itembasepeoinventoryitemid = aav.inventory_item_id
and aav.reg = 1
and aav.context_code = 'ALP_ADMON_VTA'
left join cat_master_oracle_fusion.sch_gold_layer.inv_units_of_measure_vl iuom
on esyb.itembasepeoprimaryuomcode = iuom.uom_code
left join alp_ot ao
on esyb.itembasepeoorganizationid = ao.organization_id
and esyb.itembasepeoinventoryitemid = ao.inventory_item_id
and ao.reg = 1
left join alp_cat_fnd_flex_fields aavf1
on aav.attribute_number1 = aavf1.value
and aavf1.flex_value_set_name = 'ALP_ADMON_VENTAS_FAMILIA'
left join alp_cat_fnd_flex_fields aavf2
on aav.attribute_number2 = aavf2.value
and aavf2.flex_value_set_name = 'ALP_ADMON_VENTAS_PRESENTACION'
left join alp_ego_item_eff_b ap
on esyb.itembasepeoorganizationid = ap.organization_id
and esyb.itembasepeoinventoryitemid = ap.inventory_item_id
and ap.reg = 1
and ap.context_code = 'ALP_PRODUCCIONES'
left join alp_cat_fnd_flex_fields ap1
on ap.attribute_number1 = ap1.value
and ap1.flex_value_set_name = 'ALP_PRODUCCIONES_FAMILIA'
left join alp_cat_fnd_flex_fields ap2
on ap.attribute_number2 = ap2.value
and ap2.flex_value_set_name = 'ALP_PRODUCCIONES_SUBFAMILIA'
left join alp_cat_fnd_flex_fields ap3
on ap.attribute_number3 = ap3.value
and ap3.flex_value_set_name = 'ALP_PRODUCCIONES_PRESENTACION'
left join cat_master_oracle_fusion.sch_gold_layer.ego_item_eff_b aso
on esyb.itembasepeoorganizationid = aso.organization_id
and esyb.itembasepeoinventoryitemid = aso.inventory_item_id
and aso.context_code = 'ALP_SOP'
left join alp_cat_fnd_flex_fields aso1
on aso.attribute_char1 = aso1.value
and aso1.flex_value_set_name = 'ALP_SOP_MACROCATEGORIA'
left join alp_cat_fnd_flex_fields aso2
on aso.attribute_char2 = aso2.value
and aso2.flex_value_set_name = 'ALP_SOP_SUBCATEGORIA'
left join alp_cat_fnd_flex_fields aso3
on aso.attribute_number1 = aso3.value
and aso3.flex_value_set_name = 'ALP_SOP_PRODUCTO_PRESENTACION'
left join alp_cat_fnd_flex_fields aso4
on aso.attribute_char3 = aso4.value
and aso4.flex_value_set_name = 'ALP_SOP_SUBMARCA'
left join alp_cat_fnd_flex_fields aso5
on aso.attribute_number2 = aso5.value
and aso5.flex_value_set_name = 'ALP_SOP_SUBCATEGORIA_COMERCIAL'
left join alp_category ac1
on esyb.itembasepeoinventoryitemid = ac1.item_id
and esyb.itembasepeoorganizationid = ac1.org_id
and ac1.category = 'ALP_CAT_COMPRAS'
left join alp_category ac2
on esyb.itembasepeoinventoryitemid = ac2.item_id
and esyb.itembasepeoorganizationid = ac2.org_id
and ac2.category = 'ALP_CST_CAT'
left join alp_category ac3
on esyb.itembasepeoinventoryitemid = ac3.item_id
and esyb.itembasepeoorganizationid = ac3.org_id
and ac3.category = 'ALP_INVENTARIOS'
left join alp_category ac4
on esyb.itembasepeoinventoryitemid = ac4.item_id
and esyb.itembasepeoorganizationid = ac4.org_id
and ac4.category = 'ALP_CUENTA_LECHE'
left join alp_category ac5
on esyb.itembasepeoinventoryitemid = ac5.item_id
and esyb.itembasepeoorganizationid = ac5.org_id
and ac5.category = 'ALP_DESPERDICIOS'
left join alp_category ac6
on esyb.itembasepeoinventoryitemid = ac6.item_id
and esyb.itembasepeoorganizationid = ac6.org_id
and ac6.category = 'ALP_REPORTE_CUENTA_DE_LECHE'
left join alp_costos ac
on esyb.itembasepeoorganizationid = ac.org_id
and esyb.itembasepeoinventoryitemid = ac.item_id
and ac.reg = 1
left join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_zx_taxratesextractpvo tax
on esyb.itembasepeotaxcode = tax.taxratestaxratecode
and current_date() between tax.taxrateseffectivefrom
and nvl(tax.taxrateseffectiveto,current_date())
where eict.itemclasstranpeoitemclassname = 'PTE'
with alp_category as
( select mic.organizationid org_id
, mic.inventoryitemid item_id
, mcst.category_set_name category
, mcb.categorybasepeocategorycode code
from cat_master_oracle_fusion.sch_gold_layer.egp_category_sets_tl_stg mcst
join cat_master_oracle_fusion.sch_gold_layer.fscm_egp_itemcategory_stg mic
on mcst.category_set_id = mic.catalogbasepeocategorysetid
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_categoryextractpvo_stg mcb
on mic.catalogbasepeodefaultcategoryid = mcb.categorybasepeocategoryid
where mcst.language = 'E')
, alp_costos as
( select row_number()
over( partition by nvl(csc.organization_id,iop.organizationid)
, csc.inventory_item_id
order by csc.scenario_id desc) reg
, nvl(csc.organization_id,iop.organizationid) org_id
, csc.inventory_item_id item_id
, ccbb.costbookbpeocostbookcode cost_book_code
, ccbt.costbooktlpeocostbookdesc cost_book
, cvub.valuationunitbpeovalunitcode cost_val_unit_code
, floor(csc.total_cost * 1e6) / 1e6 cost_manofactura
, csc.uom_code cost_uom_code
, csc.currency_code cost_currency
from cat_master_oracle_fusion.sch_gold_layer.cst_std_costs_stg csc
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_cst_costbookextractpvo_stg ccbb
on csc.cost_book_id = ccbb.costbookbpeocostbookid
and ccbb.costbookbpeocostbookcode = 'PRINCIPAL ESTANDAR'
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_cst_costbooktlextractpvo_stg ccbt
on ccbb.costbookbpeocostbookid = ccbt.costbooktlpeocostbookid
and ccbt.costbooktlpeolanguage = 'E'
join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_stg haou
on csc.cost_org_id = haou.organization_id
and haou.name = 'GPLP COSTO MANF'
left join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_cst_valuationunitextractpvo_stg cvub
on csc.val_unit_id = cvub.valuationunitbpeovalunitid
left join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_scmrcs_invorgparametersextractpvo_stg iop
on split(cvub.valuationunitbpeovalunitcode, '-')[0] = iop.organizationcode
where current_date() between csc.effective_start_date
and csc.effective_end_date)
, alp_ot as
( select row_number()
over( partition by wwd.organizationid
, wwd.inventoryitemid
order by wwd.workdefinitionid desc) reg
, wwd.organizationid organization_id
, wwd.inventoryitemid inventory_item_id
, wwd.workdefinitionheadername work_definition_header_name
, wwd.statuscode status_code
, egs.structurespeoalternatebomdesignator alternate_bom_designator
from cat_master_oracle_fusion.sch_gold_layer.fscm_scm_wis_workdefinitionextractpvo_stg wwd
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_structuresextractpvo_stg egs
on wwd.billsequenceid = egs.structurespeobillsequenceid
where nvl(wwd.inactivedate,current_date()) >= current_date())
, alp_cat_fnd_flex_fields as
( select ffvs.flex_value_set_id
, ffv.valueid flex_value_id
, ffvs.flex_value_set_name
, ffv.value
, ffvt.description
, ffv.enabledflag enabled_flag
, ffv.startdateactive start_date
, ffv.enddateactive end_date
from cat_master_oracle_fusion.sch_gold_layer.fnd_flex_value_sets_stg ffvs
join cat_master_oracle_fusion.sch_gold_layer.fscm_as_valuesetvaluespvo_stg ffv
on ffvs.flex_value_set_id = ffv.valuesetid
join cat_master_oracle_fusion.sch_gold_layer.fscm_as_valuesetvaluestlpvo_stg ffvt
on ffv.valueid = ffvt.valueid
and ffvt.language = 'E')
, alp_ego_item_eff_b as
( select row_number()
over( partition by organization_id
, inventory_item_id
, context_code
order by eff_line_id desc) reg
, organization_id
, inventory_item_id
, context_code
, attribute_number1
, attribute_number2
, attribute_number3
from cat_master_oracle_fusion.sch_gold_layer.ego_item_eff_b_stg
where current_date() between version_start_date
and nvl(version_end_date,current_date()))
, alp_skus as
( select row_number()
over( partition by inventory_item_id
, uom_code
order by item_relationship_id desc) reg
, inventory_item_id item_id
, uom_code uom
, cross_reference sku
from cat_master_oracle_fusion.sch_gold_layer.egp_item_relationships_b_stg
where sub_type = 'SKU')
select esyb.itembasepeoinventoryitemid item_id
, esyb.itembasepeoorganizationid org_id
, mp.organizationcode org_code
, haou.name org_name
, esyb.itembasepeoitemnumber item
, esyt.itemtranslationpeodescription description
, esyt.itemtranslationpeolongdescription long_description
, eict.itemclasstranpeoitemclassname item_type
, eist.inventory_item_status_name status
, esyb.itembasepeocurrentphasecode lifecycle_phase
, esyb.itembasepeoprimaryuomcode primary_uom_code
, iuom.attribute1 primary_uom_code_sat
, iuom.unit_of_measure primary_uom
, esyb.itembasepeoattribute1 clave_anterior
, esyb.itembasepeoattributenumber1 factor_kilolitros
, esyb.itembasepeoattributenumber2 cantidad_estandar
, esyb.itembasepeoattribute2 articulo_granel
, esyb.itembasepeoattribute3 clave_producto_sat
, esyb.itembasepeoattributenumber3 factor_leche
, esyb.itembasepeoattribute4 formato_de_envasado
, esyb.itembasepeoattribute5 sabor
, esyb.itembasepeoattribute6 tipo_negocio
, esyb.itembasepeoattribute7 area_impresion_etiqueta
, esyb.itembasepeoattribute8 localizador_rendimiento
, esyb.itembasepeoattribute9 localizador_suministro
, esyb.itembasepeoattribute10 subinventario_rendimiento
, esyb.itembasepeoattribute11 cajas_por_cama
, esyb.itembasepeoattribute12 cajas_por_tarima
, esyb.itembasepeoattribute13 camas_por_tarima
, esyb.itembasepeoattribute14 piezas_por_caja
, esyb.itembasepeoattribute15 piezas_por_tarima
, esyb.itembasepeoattribute16 tipo_de_caja
, esyb.itembasepeoattribute17 tipo_de_envase
, esyb.itembasepeoattribute18 tipo_de_tarima
, esyb.itembasepeoattribute19 fraccion_arancelaria
, esyb.itembasepeoattribute20 objeto_de_impuesto
, esyb.itembasepeoattribute21 tiempo_de_cuarentena
, esyb.itembasepeoattribute22 subinventario_suministro
, esyb.itembasepeoattribute23 planta_origen
, esyb.itembasepeoattribute24 facturacion_granel
, esyb.itembasepeoattributenumber4 contenido_neto_num
, esyb.itembasepeoattributenumber5 presentacion_de_venta_400
, esyb.itembasepeoattributenumber6 vida_de_anaquel
, esyb.itembasepeoattribute25 familia_as400
, esyb.itembasepeoattributenumber7 porcentaje_grasa
, esyb.itembasepeoattribute26 contenido_neto
, aav.attribute_number1 alp_admon_ventas_familia
, aavf1.description desc_alp_admon_ventas_familia
, aav.attribute_number2 alp_admon_ventas_presentacion
, aavf2.description desc_alp_admon_ventas_presentacion
, ap.attribute_number1 alp_producciones_familia
, ap1.description desc_alp_producciones_familia
, ap.attribute_number2 alp_producciones_subfamilia
, ap2.description desc_alp_producciones_subfamilia
, ap.attribute_number3 alp_producciones_presentacion
, ap3.description desc_alp_producciones_presentacion
, aso.attribute_char1 alp_sop_macrocategoria
, aso1.description desc_alp_sop_macrocategoria
, aso.attribute_char2 alp_sop_subcategoria
, aso2.description desc_alp_sop_subcategoria
, aso.attribute_number1 alp_sop_producto_presentacion
, aso3.description desc_alp_sop_producto_presentacion
, aso.attribute_char3 alp_sop_submarca
, aso4.description desc_alp_sop_submarca
, aso.attribute_number2 alp_sop_subcategoria_comercial
, aso5.description desc_alp_sop_subcategoria_comercial
, ac1.code alp_cat_compras
, ac2.code alp_cst_cat
, ac3.code alp_inventarios
, ac4.code alp_cuenta_leche
, ac5.code alp_desperdicios
, ac6.code alp_reporte_cuenta_de_leche
, eirb.sku
, eirbc.sku sku_caja
, eirbt.sku sku_tarima
, eirbch.sku sku_charola
, ac.cost_book_code manufactura_book_code
, ac.cost_book manufactura_book
, ac.cost_val_unit_code manufactura_val_unit_code
, ac.cost_manofactura manufactura_cost
, ac.cost_uom_code manufactura_uom_code
, ac.cost_currency manufactura_currency
, ao.work_definition_header_name nombre_estructura
, ao.alternate_bom_designator nombre_proceso
, esyb.itembasepeotaxcode facturacion_impuesto_sobre_venta_code
, tax.taxratespercentagerate facturacion_impuesto_sobre_venta
from cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemextractpvo_stg esyb
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemtranslationextractpvo_stg esyt
on esyb.itembasepeoorganizationid = esyt.itemtranslationpeoorganizationid
and esyb.itembasepeoinventoryitemid = esyt.itemtranslationpeoinventoryitemid
and esyt.itemtranslationpeolanguage = 'E'
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemclasstranslationextractpvo_stg eict
on esyb.itembasepeoitemcataloggroupid = eict.itemclasstranpeoitemclassid
and eict.itemclasstranpeolanguage = 'E'
join cat_master_oracle_fusion.sch_gold_layer.egp_item_status_tl_stg eist
on esyb.itembasepeoinventoryitemstatuscode = eist.inventory_item_status_code
and eist.language = 'E'
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_scmrcs_invorgparametersextractpvo_stg mp
on esyb.itembasepeoorganizationid = mp.organizationid
join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_stg haou
on mp.organizationid = haou.organization_id
left join alp_skus eirb
on esyb.itembasepeoinventoryitemid = eirb.item_id
and eirb.uom IS NULL
and eirb.reg = 1
left join alp_skus eirbc
on esyb.itembasepeoinventoryitemid = eirbc.item_id
and eirbc.uom = 'CJA'
and eirbc.reg = 1
left join alp_skus eirbt
on esyb.itembasepeoinventoryitemid = eirbt.item_id
and eirbt.uom = 'TRM'
and eirbt.reg = 1
left join alp_skus eirbch
on esyb.itembasepeoinventoryitemid = eirbch.item_id
and eirbch.uom = 'TCH'
and eirbch.reg = 1
left join alp_ego_item_eff_b aav
on esyb.itembasepeoorganizationid = aav.organization_id
and esyb.itembasepeoinventoryitemid = aav.inventory_item_id
and aav.reg = 1
and aav.context_code = 'ALP_ADMON_VTA'
left join cat_master_oracle_fusion.sch_gold_layer.inv_units_of_measure_vl_stg iuom
on esyb.itembasepeoprimaryuomcode = iuom.uom_code
left join alp_ot ao
on esyb.itembasepeoorganizationid = ao.organization_id
and esyb.itembasepeoinventoryitemid = ao.inventory_item_id
and ao.reg = 1
left join alp_cat_fnd_flex_fields aavf1
on aav.attribute_number1 = aavf1.value
and aavf1.flex_value_set_name = 'ALP_ADMON_VENTAS_FAMILIA'
left join alp_cat_fnd_flex_fields aavf2
on aav.attribute_number2 = aavf2.value
and aavf2.flex_value_set_name = 'ALP_ADMON_VENTAS_PRESENTACION'
left join alp_ego_item_eff_b ap
on esyb.itembasepeoorganizationid = ap.organization_id
and esyb.itembasepeoinventoryitemid = ap.inventory_item_id
and ap.reg = 1
and ap.context_code = 'ALP_PRODUCCIONES'
left join alp_cat_fnd_flex_fields ap1
on ap.attribute_number1 = ap1.value
and ap1.flex_value_set_name = 'ALP_PRODUCCIONES_FAMILIA'
left join alp_cat_fnd_flex_fields ap2
on ap.attribute_number2 = ap2.value
and ap2.flex_value_set_name = 'ALP_PRODUCCIONES_SUBFAMILIA'
left join alp_cat_fnd_flex_fields ap3
on ap.attribute_number3 = ap3.value
and ap3.flex_value_set_name = 'ALP_PRODUCCIONES_PRESENTACION'
left join cat_master_oracle_fusion.sch_gold_layer.ego_item_eff_b_stg aso
on esyb.itembasepeoorganizationid = aso.organization_id
and esyb.itembasepeoinventoryitemid = aso.inventory_item_id
and aso.context_code = 'ALP_SOP'
left join alp_cat_fnd_flex_fields aso1
on aso.attribute_char1 = aso1.value
and aso1.flex_value_set_name = 'ALP_SOP_MACROCATEGORIA'
left join alp_cat_fnd_flex_fields aso2
on aso.attribute_char2 = aso2.value
and aso2.flex_value_set_name = 'ALP_SOP_SUBCATEGORIA'
left join alp_cat_fnd_flex_fields aso3
on aso.attribute_number1 = aso3.value
and aso3.flex_value_set_name = 'ALP_SOP_PRODUCTO_PRESENTACION'
left join alp_cat_fnd_flex_fields aso4
on aso.attribute_char3 = aso4.value
and aso4.flex_value_set_name = 'ALP_SOP_SUBMARCA'
left join alp_cat_fnd_flex_fields aso5
on aso.attribute_number2 = aso5.value
and aso5.flex_value_set_name = 'ALP_SOP_SUBCATEGORIA_COMERCIAL'
left join alp_category ac1
on esyb.itembasepeoinventoryitemid = ac1.item_id
and esyb.itembasepeoorganizationid = ac1.org_id
and ac1.category = 'ALP_CAT_COMPRAS'
left join alp_category ac2
on esyb.itembasepeoinventoryitemid = ac2.item_id
and esyb.itembasepeoorganizationid = ac2.org_id
and ac2.category = 'ALP_CST_CAT'
left join alp_category ac3
on esyb.itembasepeoinventoryitemid = ac3.item_id
and esyb.itembasepeoorganizationid = ac3.org_id
and ac3.category = 'ALP_INVENTARIOS'
left join alp_category ac4
on esyb.itembasepeoinventoryitemid = ac4.item_id
and esyb.itembasepeoorganizationid = ac4.org_id
and ac4.category = 'ALP_CUENTA_LECHE'
left join alp_category ac5
on esyb.itembasepeoinventoryitemid = ac5.item_id
and esyb.itembasepeoorganizationid = ac5.org_id
and ac5.category = 'ALP_DESPERDICIOS'
left join alp_category ac6
on esyb.itembasepeoinventoryitemid = ac6.item_id
and esyb.itembasepeoorganizationid = ac6.org_id
and ac6.category = 'ALP_REPORTE_CUENTA_DE_LECHE'
left join alp_costos ac
on esyb.itembasepeoorganizationid = ac.org_id
and esyb.itembasepeoinventoryitemid = ac.item_id
and ac.reg = 1
left join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_zx_taxratesextractpvo_stg tax
on esyb.itembasepeotaxcode = tax.taxratestaxratecode
and current_date() between tax.taxrateseffectivefrom
and nvl(tax.taxrateseffectiveto,current_date())
where eict.itemclasstranpeoitemclassname = 'PTE'
with alp_category as
( select mic.organizationid org_id
, mic.inventoryitemid item_id
, mcst.category_set_name category
, mcb.categorybasepeocategorycode code
from cat_master_oracle_fusion.sch_gold_layer.egp_category_sets_tl_prd mcst
join cat_master_oracle_fusion.sch_gold_layer.fscm_egp_itemcategory_prd mic
on mcst.category_set_id = mic.catalogbasepeocategorysetid
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_categoryextractpvo_prd mcb
on mic.catalogbasepeodefaultcategoryid = mcb.categorybasepeocategoryid
where mcst.language = 'E')
, alp_costos as
( select row_number()
over( partition by nvl(csc.organization_id,iop.organizationid)
, csc.inventory_item_id
order by csc.scenario_id desc) reg
, nvl(csc.organization_id,iop.organizationid) org_id
, csc.inventory_item_id item_id
, ccbb.costbookbpeocostbookcode cost_book_code
, ccbt.costbooktlpeocostbookdesc cost_book
, cvub.valuationunitbpeovalunitcode cost_val_unit_code
, floor(csc.total_cost * 1e6) / 1e6 cost_manofactura
, csc.uom_code cost_uom_code
, csc.currency_code cost_currency
from cat_master_oracle_fusion.sch_gold_layer.cst_std_costs_prd csc
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_cst_costbookextractpvo_prd ccbb
on csc.cost_book_id = ccbb.costbookbpeocostbookid
and ccbb.costbookbpeocostbookcode = 'PRINCIPAL ESTANDAR'
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_cst_costbooktlextractpvo_prd ccbt
on ccbb.costbookbpeocostbookid = ccbt.costbooktlpeocostbookid
and ccbt.costbooktlpeolanguage = 'E'
join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_prd haou
on csc.cost_org_id = haou.organization_id
and haou.name = 'GPLP COSTO MANF'
left join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_cst_valuationunitextractpvo_prd cvub
on csc.val_unit_id = cvub.valuationunitbpeovalunitid
left join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_scmrcs_invorgparametersextractpvo_prd iop
on split(cvub.valuationunitbpeovalunitcode, '-')[0] = iop.organizationcode
where current_date() between csc.effective_start_date
and csc.effective_end_date)
, alp_ot as
( select row_number()
over( partition by wwd.organizationid
, wwd.inventoryitemid
order by wwd.workdefinitionid desc) reg
, wwd.organizationid organization_id
, wwd.inventoryitemid inventory_item_id
, wwd.workdefinitionheadername work_definition_header_name
, wwd.statuscode status_code
, egs.structurespeoalternatebomdesignator alternate_bom_designator
from cat_master_oracle_fusion.sch_gold_layer.fscm_scm_wis_workdefinitionextractpvo_prd wwd
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_structuresextractpvo_prd egs
on wwd.billsequenceid = egs.structurespeobillsequenceid
where nvl(wwd.inactivedate,current_date()) >= current_date())
, alp_cat_fnd_flex_fields as
( select ffvs.flex_value_set_id
, ffv.valueid flex_value_id
, ffvs.flex_value_set_name
, ffv.value
, ffvt.description
, ffv.enabledflag enabled_flag
, ffv.startdateactive start_date
, ffv.enddateactive end_date
from cat_master_oracle_fusion.sch_gold_layer.fnd_flex_value_sets_prd ffvs
join cat_master_oracle_fusion.sch_gold_layer.fscm_as_valuesetvaluespvo_prd ffv
on ffvs.flex_value_set_id = ffv.valuesetid
join cat_master_oracle_fusion.sch_gold_layer.fscm_as_valuesetvaluestlpvo_prd ffvt
on ffv.valueid = ffvt.valueid
and ffvt.language = 'E')
, alp_ego_item_eff_b as
( select row_number()
over( partition by organization_id
, inventory_item_id
, context_code
order by eff_line_id desc) reg
, organization_id
, inventory_item_id
, context_code
, attribute_number1
, attribute_number2
, attribute_number3
from cat_master_oracle_fusion.sch_gold_layer.ego_item_eff_b_prd
where current_date() between version_start_date
and nvl(version_end_date,current_date()))
, alp_skus as
( select row_number()
over( partition by inventory_item_id
, uom_code
order by item_relationship_id desc) reg
, inventory_item_id item_id
, uom_code uom
, cross_reference sku
from cat_master_oracle_fusion.sch_gold_layer.egp_item_relationships_b_prd
where sub_type = 'SKU')
select esyb.itembasepeoinventoryitemid item_id
, esyb.itembasepeoorganizationid org_id
, mp.organizationcode org_code
, haou.name org_name
, esyb.itembasepeoitemnumber item
, esyt.itemtranslationpeodescription description
, esyt.itemtranslationpeolongdescription long_description
, eict.itemclasstranpeoitemclassname item_type
, eist.inventory_item_status_name status
, esyb.itembasepeocurrentphasecode lifecycle_phase
, esyb.itembasepeoprimaryuomcode primary_uom_code
, iuom.attribute1 primary_uom_code_sat
, iuom.unit_of_measure primary_uom
, esyb.itembasepeoattribute1 clave_anterior
, esyb.itembasepeoattributenumber1 factor_kilolitros
, esyb.itembasepeoattributenumber2 cantidad_estandar
, esyb.itembasepeoattribute2 articulo_granel
, esyb.itembasepeoattribute3 clave_producto_sat
, esyb.itembasepeoattributenumber3 factor_leche
, esyb.itembasepeoattribute4 formato_de_envasado
, esyb.itembasepeoattribute5 sabor
, esyb.itembasepeoattribute6 tipo_negocio
, esyb.itembasepeoattribute7 area_impresion_etiqueta
, esyb.itembasepeoattribute8 localizador_rendimiento
, esyb.itembasepeoattribute9 localizador_suministro
, esyb.itembasepeoattribute10 subinventario_rendimiento
, esyb.itembasepeoattribute11 cajas_por_cama
, esyb.itembasepeoattribute12 cajas_por_tarima
, esyb.itembasepeoattribute13 camas_por_tarima
, esyb.itembasepeoattribute14 piezas_por_caja
, esyb.itembasepeoattribute15 piezas_por_tarima
, esyb.itembasepeoattribute16 tipo_de_caja
, esyb.itembasepeoattribute17 tipo_de_envase
, esyb.itembasepeoattribute18 tipo_de_tarima
, esyb.itembasepeoattribute19 fraccion_arancelaria
, esyb.itembasepeoattribute20 objeto_de_impuesto
, esyb.itembasepeoattribute21 tiempo_de_cuarentena
, esyb.itembasepeoattribute22 subinventario_suministro
, esyb.itembasepeoattribute23 planta_origen
, esyb.itembasepeoattribute24 facturacion_granel
, esyb.itembasepeoattributenumber4 contenido_neto_num
, esyb.itembasepeoattributenumber5 presentacion_de_venta_400
, esyb.itembasepeoattributenumber6 vida_de_anaquel
, esyb.itembasepeoattribute25 familia_as400
, esyb.itembasepeoattributenumber7 porcentaje_grasa
, esyb.itembasepeoattribute26 contenido_neto
, aav.attribute_number1 alp_admon_ventas_familia
, aavf1.description desc_alp_admon_ventas_familia
, aav.attribute_number2 alp_admon_ventas_presentacion
, aavf2.description desc_alp_admon_ventas_presentacion
, ap.attribute_number1 alp_producciones_familia
, ap1.description desc_alp_producciones_familia
, ap.attribute_number2 alp_producciones_subfamilia
, ap2.description desc_alp_producciones_subfamilia
, ap.attribute_number3 alp_producciones_presentacion
, ap3.description desc_alp_producciones_presentacion
, aso.attribute_char1 alp_sop_macrocategoria
, aso1.description desc_alp_sop_macrocategoria
, aso.attribute_char2 alp_sop_subcategoria
, aso2.description desc_alp_sop_subcategoria
, aso.attribute_number1 alp_sop_producto_presentacion
, aso3.description desc_alp_sop_producto_presentacion
, aso.attribute_char3 alp_sop_submarca
, aso4.description desc_alp_sop_submarca
, aso.attribute_number2 alp_sop_subcategoria_comercial
, aso5.description desc_alp_sop_subcategoria_comercial
, ac1.code alp_cat_compras
, ac2.code alp_cst_cat
, ac3.code alp_inventarios
, ac4.code alp_cuenta_leche
, ac5.code alp_desperdicios
, ac6.code alp_reporte_cuenta_de_leche
, eirb.sku
, eirbc.sku sku_caja
, eirbt.sku sku_tarima
, eirbch.sku sku_charola
, ac.cost_book_code manufactura_book_code
, ac.cost_book manufactura_book
, ac.cost_val_unit_code manufactura_val_unit_code
, ac.cost_manofactura manufactura_cost
, ac.cost_uom_code manufactura_uom_code
, ac.cost_currency manufactura_currency
, ao.work_definition_header_name nombre_estructura
, ao.alternate_bom_designator nombre_proceso
, esyb.itembasepeotaxcode facturacion_impuesto_sobre_venta_code
, tax.taxratespercentagerate facturacion_impuesto_sobre_venta
from cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemextractpvo_prd esyb
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemtranslationextractpvo_prd esyt
on esyb.itembasepeoorganizationid = esyt.itemtranslationpeoorganizationid
and esyb.itembasepeoinventoryitemid = esyt.itemtranslationpeoinventoryitemid
and esyt.itemtranslationpeolanguage = 'E'
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemclasstranslationextractpvo_prd eict
on esyb.itembasepeoitemcataloggroupid = eict.itemclasstranpeoitemclassid
and eict.itemclasstranpeolanguage = 'E'
join cat_master_oracle_fusion.sch_gold_layer.egp_item_status_tl_prd eist
on esyb.itembasepeoinventoryitemstatuscode = eist.inventory_item_status_code
and eist.language = 'E'
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_scmrcs_invorgparametersextractpvo_prd mp
on esyb.itembasepeoorganizationid = mp.organizationid
join cat_master_oracle_fusion.sch_gold_layer.hr_all_organization_units_prd haou
on mp.organizationid = haou.organization_id
left join alp_skus eirb
on esyb.itembasepeoinventoryitemid = eirb.item_id
and eirb.uom IS NULL
and eirb.reg = 1
left join alp_skus eirbc
on esyb.itembasepeoinventoryitemid = eirbc.item_id
and eirbc.uom = 'CJA'
and eirbc.reg = 1
left join alp_skus eirbt
on esyb.itembasepeoinventoryitemid = eirbt.item_id
and eirbt.uom = 'TRM'
and eirbt.reg = 1
left join alp_skus eirbch
on esyb.itembasepeoinventoryitemid = eirbch.item_id
and eirbch.uom = 'TCH'
and eirbch.reg = 1
left join alp_ego_item_eff_b aav
on esyb.itembasepeoorganizationid = aav.organization_id
and esyb.itembasepeoinventoryitemid = aav.inventory_item_id
and aav.reg = 1
and aav.context_code = 'ALP_ADMON_VTA'
left join cat_master_oracle_fusion.sch_gold_layer.inv_units_of_measure_vl_prd iuom
on esyb.itembasepeoprimaryuomcode = iuom.uom_code
left join alp_ot ao
on esyb.itembasepeoorganizationid = ao.organization_id
and esyb.itembasepeoinventoryitemid = ao.inventory_item_id
and ao.reg = 1
left join alp_cat_fnd_flex_fields aavf1
on aav.attribute_number1 = aavf1.value
and aavf1.flex_value_set_name = 'ALP_ADMON_VENTAS_FAMILIA'
left join alp_cat_fnd_flex_fields aavf2
on aav.attribute_number2 = aavf2.value
and aavf2.flex_value_set_name = 'ALP_ADMON_VENTAS_PRESENTACION'
left join alp_ego_item_eff_b ap
on esyb.itembasepeoorganizationid = ap.organization_id
and esyb.itembasepeoinventoryitemid = ap.inventory_item_id
and ap.reg = 1
and ap.context_code = 'ALP_PRODUCCIONES'
left join alp_cat_fnd_flex_fields ap1
on ap.attribute_number1 = ap1.value
and ap1.flex_value_set_name = 'ALP_PRODUCCIONES_FAMILIA'
left join alp_cat_fnd_flex_fields ap2
on ap.attribute_number2 = ap2.value
and ap2.flex_value_set_name = 'ALP_PRODUCCIONES_SUBFAMILIA'
left join alp_cat_fnd_flex_fields ap3
on ap.attribute_number3 = ap3.value
and ap3.flex_value_set_name = 'ALP_PRODUCCIONES_PRESENTACION'
left join cat_master_oracle_fusion.sch_gold_layer.ego_item_eff_b_prd aso
on esyb.itembasepeoorganizationid = aso.organization_id
and esyb.itembasepeoinventoryitemid = aso.inventory_item_id
and aso.context_code = 'ALP_SOP'
left join alp_cat_fnd_flex_fields aso1
on aso.attribute_char1 = aso1.value
and aso1.flex_value_set_name = 'ALP_SOP_MACROCATEGORIA'
left join alp_cat_fnd_flex_fields aso2
on aso.attribute_char2 = aso2.value
and aso2.flex_value_set_name = 'ALP_SOP_SUBCATEGORIA'
left join alp_cat_fnd_flex_fields aso3
on aso.attribute_number1 = aso3.value
and aso3.flex_value_set_name = 'ALP_SOP_PRODUCTO_PRESENTACION'
left join alp_cat_fnd_flex_fields aso4
on aso.attribute_char3 = aso4.value
and aso4.flex_value_set_name = 'ALP_SOP_SUBMARCA'
left join alp_cat_fnd_flex_fields aso5
on aso.attribute_number2 = aso5.value
and aso5.flex_value_set_name = 'ALP_SOP_SUBCATEGORIA_COMERCIAL'
left join alp_category ac1
on esyb.itembasepeoinventoryitemid = ac1.item_id
and esyb.itembasepeoorganizationid = ac1.org_id
and ac1.category = 'ALP_CAT_COMPRAS'
left join alp_category ac2
on esyb.itembasepeoinventoryitemid = ac2.item_id
and esyb.itembasepeoorganizationid = ac2.org_id
and ac2.category = 'ALP_CST_CAT'
left join alp_category ac3
on esyb.itembasepeoinventoryitemid = ac3.item_id
and esyb.itembasepeoorganizationid = ac3.org_id
and ac3.category = 'ALP_INVENTARIOS'
left join alp_category ac4
on esyb.itembasepeoinventoryitemid = ac4.item_id
and esyb.itembasepeoorganizationid = ac4.org_id
and ac4.category = 'ALP_CUENTA_LECHE'
left join alp_category ac5
on esyb.itembasepeoinventoryitemid = ac5.item_id
and esyb.itembasepeoorganizationid = ac5.org_id
and ac5.category = 'ALP_DESPERDICIOS'
left join alp_category ac6
on esyb.itembasepeoinventoryitemid = ac6.item_id
and esyb.itembasepeoorganizationid = ac6.org_id
and ac6.category = 'ALP_REPORTE_CUENTA_DE_LECHE'
left join alp_costos ac
on esyb.itembasepeoorganizationid = ac.org_id
and esyb.itembasepeoinventoryitemid = ac.item_id
and ac.reg = 1
left join cat_master_oracle_fusion.sch_gold_layer.fscm_fin_zx_taxratesextractpvo_prd tax
on esyb.itembasepeotaxcode = tax.taxratestaxratecode
and current_date() between tax.taxrateseffectivefrom
and nvl(tax.taxrateseffectiveto,current_date())
where eict.itemclasstranpeoitemclassname = 'PTE'
WITH alp_flex AS
( SELECT ffvs.flex_value_set_name ff
, ffv.flex_value fv
, ffvt.description fvd
FROM fnd_flex_value_sets ffvs
, fnd_flex_values ffv
, fnd_flex_values_tl ffvt
WHERE ffvs.flex_value_set_id = ffv.flex_value_set_id
AND ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = 'E'
AND ffvs.flex_value_set_name IN ( 'ALP_PRODUCCIONES_FAMILIA'
, 'ALP_PRODUCCIONES_SUBFAMILIA'
, 'ALP_PRODUCCIONES_PRESENTACION'
, 'ALP_SOP_MACROCATEGORIA'
, 'ALP_SOP_SUBCATEGORIA'
, 'ALP_SOP_PRODUCTO_PRESENTACION'
, 'ALP_SOP_SUBMARCA'
, 'ALP_SOP_SUBCATEGORIA_COMERCIAL'
, 'ALP_ADMON_VENTAS_FAMILIA'
, 'ALP_ADMON_VENTAS_PRESENTACION'))
, alp_groups AS
( SELECT eieb.organization_id org_id
, eieb.inventory_item_id item_id
, eieb.context_code grupo
, NULL category_code
, CASE eieb.context_code
WHEN 'ALP_ADMON_VTA' THEN eieb.attribute_number1
ELSE NULL
END alp_admon_ventas_familia
, CASE eieb.context_code
WHEN 'ALP_ADMON_VTA' THEN af_aav_1.fvd
ELSE NULL
END desc_alp_admon_ventas_familia
, CASE eieb.context_code
WHEN 'ALP_ADMON_VTA' THEN eieb.attribute_number2
ELSE NULL
END alp_admon_ventas_presentacion
, CASE eieb.context_code
WHEN 'ALP_ADMON_VTA' THEN af_aav_2.fvd
ELSE NULL
END desc_alp_admon_ventas_presentacion
, CASE eieb.context_code
WHEN 'ALP_PRODUCCIONES' THEN eieb.attribute_number1
ELSE NULL
END alp_producciones_familia
, CASE eieb.context_code
WHEN 'ALP_PRODUCCIONES' THEN af_ap_1.fvd
ELSE NULL
END desc_alp_producciones_familia
, CASE eieb.context_code
WHEN 'ALP_PRODUCCIONES' THEN eieb.attribute_number2
ELSE NULL
END alp_producciones_subfamilia
, CASE eieb.context_code
WHEN 'ALP_PRODUCCIONES' THEN af_ap_2.fvd
ELSE NULL
END desc_alp_producciones_subfamilia
, CASE eieb.context_code
WHEN 'ALP_PRODUCCIONES' THEN eieb.attribute_number3
ELSE NULL
END alp_producciones_presentacion
, CASE eieb.context_code
WHEN 'ALP_PRODUCCIONES' THEN af_ap_3.fvd
ELSE NULL
END desc_alp_producciones_presentacion
, CASE eieb.context_code
WHEN 'ALP_SOP' THEN eieb.attribute_char1
ELSE NULL
END alp_sop_macrocategoria
, CASE eieb.context_code
WHEN 'ALP_SOP' THEN af_as_1.fvd
ELSE NULL
END desc_alp_sop_macrocategoria
, CASE eieb.context_code
WHEN 'ALP_SOP' THEN eieb.attribute_char2
ELSE NULL
END alp_sop_subcategoria
, CASE eieb.context_code
WHEN 'ALP_SOP' THEN af_as_2.fvd
ELSE NULL
END desc_alp_sop_subcategoria
, CASE eieb.context_code
WHEN 'ALP_SOP' THEN eieb.attribute_number1
ELSE NULL
END alp_sop_producto_presentacion
, CASE eieb.context_code
WHEN 'ALP_SOP' THEN af_as_3.fvd
ELSE NULL
END desc_alp_sop_producto_presentacion
, CASE eieb.context_code
WHEN 'ALP_SOP' THEN eieb.attribute_char3
ELSE NULL
END alp_sop_submarca
, CASE eieb.context_code
WHEN 'ALP_SOP' THEN af_as_4.fvd
ELSE NULL
END desc_alp_sop_submarca
, CASE eieb.context_code
WHEN 'ALP_SOP' THEN eieb.attribute_number2
ELSE NULL
END alp_sop_subcategoria_comercial
, CASE eieb.context_code
WHEN 'ALP_SOP' THEN af_as_5.fvd
ELSE NULL
END desc_alp_sop_subcategoria_comercial
FROM ego_item_eff_b eieb
, alp_flex af_aav_1
, alp_flex af_aav_2
, alp_flex af_ap_1
, alp_flex af_ap_2
, alp_flex af_ap_3
, alp_flex af_as_1
, alp_flex af_as_2
, alp_flex af_as_3
, alp_flex af_as_4
, alp_flex af_as_5
WHERE eieb.attribute_number1 = af_aav_1.fv(+)
AND eieb.attribute_number2 = af_aav_2.fv(+)
AND eieb.attribute_number1 = af_ap_1.fv(+)
AND eieb.attribute_number2 = af_ap_2.fv(+)
AND eieb.attribute_number3 = af_ap_3.fv(+)
AND eieb.attribute_char1 = af_as_1.fv(+)
AND eieb.attribute_char2 = af_as_2.fv(+)
AND eieb.attribute_number1 = af_as_3.fv(+)
AND eieb.attribute_char3 = af_as_4.fv(+)
AND eieb.attribute_number2 = af_as_5.fv(+)
AND af_aav_1.ff(+) = 'ALP_ADMON_VENTAS_FAMILIA'
AND af_aav_2.ff(+) = 'ALP_ADMON_VENTAS_PRESENTACION'
AND af_ap_1.ff(+) = 'ALP_PRODUCCIONES_FAMILIA'
AND af_ap_2.ff(+) = 'ALP_PRODUCCIONES_SUBFAMILIA'
AND af_ap_3.ff(+) = 'ALP_PRODUCCIONES_PRESENTACION'
AND af_as_1.ff(+) = 'ALP_SOP_MACROCATEGORIA'
AND af_as_2.ff(+) = 'ALP_SOP_SUBCATEGORIA'
AND af_as_3.ff(+) = 'ALP_SOP_PRODUCTO_PRESENTACION'
AND af_as_4.ff(+) = 'ALP_SOP_SUBMARCA'
AND af_as_5.ff(+) = 'ALP_SOP_SUBCATEGORIA_COMERCIAL'
UNION ALL
SELECT eic.organization_id org_id
, eic.inventory_item_id item_id
, ecst.category_set_name grupo
, ecb.category_code
, NULL alp_admon_ventas_familia
, NULL desc_alp_admon_ventas_familia
, NULL alp_admon_ventas_presentacion
, NULL desc_alp_admon_ventas_presentacion
, NULL alp_producciones_familia
, NULL desc_alp_producciones_familia
, NULL alp_producciones_subfamilia
, NULL desc_alp_producciones_subfamilia
, NULL alp_producciones_presentacion
, NULL desc_alp_producciones_presentacion
, NULL alp_sop_macrocategoria
, NULL desc_alp_sop_macrocategoria
, NULL alp_sop_subcategoria
, NULL desc_alp_sop_subcategoria
, NULL alp_sop_producto_presentacion
, NULL desc_alp_sop_producto_presentacion
, NULL alp_sop_submarca
, NULL desc_alp_sop_submarca
, NULL alp_sop_subcategoria_comercial
, NULL desc_alp_sop_subcategoria_comercial
FROM egp_item_categories eic
, egp_category_sets_tl ecst
, egp_categories_b ecb
WHERE eic.category_set_id = ecst.category_set_id
AND eic.category_id = ecb.category_id
AND ecst.language = 'E')
, alp_costos as
( SELECT row_number()
over( partition by nvl(csc.organization_id,iop.organization_id)
, csc.inventory_item_id
order by csc.scenario_id desc) reg
, nvl(csc.organization_id,iop.organization_id) org_id
, csc.inventory_item_id item_id
, ccbb.cost_book_code
, ccbt.cost_book_desc cost_book
, cvub.val_unit_code cost_val_unit_code
, TRUNC(csc.total_cost,6) cost_manofactura
, csc.uom_code cost_uom_code
, csc.currency_code cost_currency
FROM cst_std_costs csc
, cst_val_units_b cvub
, cst_val_units_tl cvut
, cst_cost_books_b ccbb
, cst_cost_books_tl ccbt
, hr_all_organization_units haou
, egp_system_items_b esyb
, inv_org_parameters iop
WHERE csc.val_unit_id = cvub.val_unit_id
AND cvub.val_unit_id = cvut.val_unit_id
AND csc.cost_book_id = ccbb.cost_book_id
AND ccbb.cost_book_id = ccbt.cost_book_id
AND csc.cost_org_id = haou.organization_id
AND csc.inventory_item_id = esyb.inventory_item_id
AND esyb.organization_id = iop.organization_id
AND cvut.language = 'E'
AND ccbt.language = 'E'
AND ccbb.cost_book_code = 'PRINCIPAL ESTANDAR'
AND haou.name = 'GPLP COSTO MANF'
AND iop.organization_code = 'OMA'
AND SYSDATE BETWEEN csc.effective_start_date
AND csc.effective_end_date)
, alp_ot as
( SELECT ROW_NUMBER()
OVER( PARTITION BY wwd.organization_id
, wwd.inventory_item_id
ORDER BY wwd.work_definition_id desc) reg
, wwd.organization_id organization_id
, wwd.inventory_item_id inventory_item_id
, wwd.work_definition_header_name work_definition_header_name
, wwd.status_code status_code
, esb.alternate_bom_designator alternate_bom_designator
FROM wis_work_definitions wwd
, egp_system_items_b esib
, egp_structures_b esb
WHERE wwd.organization_id = esib.organization_id
AND wwd.inventory_item_id = esib.inventory_item_id
AND wwd.bill_sequence_id = esb.bill_sequence_id
AND NVL(wwd.inactive_date,SYSDATE) >= SYSDATE)
SELECT iop.organization_code org_code
, haou.name org_name
, esyb.item_number item
, esit.description description
, esit.long_description
, eist.inventory_item_status_name user_item_type
, esyb.current_phase_code lifecycle_phase
, esyb.primary_uom_code
, esyb.attribute1 clave_anterior
, esyb.attribute_number1 factor_kilolitros
, esyb.attribute2 articulo_granel
, esyb.attribute_number2 cantidad_estandar
, esyb.attribute3 clave_producto_sat
, esyb.attribute_number3 factor_leche
, esyb.attribute4 formato_de_envasado
, esyb.attribute5 sabor
, esyb.attribute6 tipo_negocio
, esyb.attribute7 area_impresion_etiqueta
, esyb.attribute8 localizador_rendimiento
, esyb.attribute9 localizador_suministro
, esyb.attribute10 subinventario_rendimiento
, esyb.attribute11 cajas_por_cama
, esyb.attribute12 cajas_por_tarima
, esyb.attribute13 piezas_por_caja
, esyb.attribute14 piezas_por_tarima
, esyb.attribute16 tipo_de_caja
, esyb.attribute17 tipo_de_envase
, esyb.attribute18 tipo_de_tarima
, esyb.attribute19 fraccion_arancelaria
, esyb.attribute20 objeto_de_impuesto
, esyb.attribute21 tiempo_de_cuarentena
, esyb.attribute22 subinventario_suministro
, esyb.attribute23 planta_origen
, esyb.attribute24 facturacion_granel
, esyb.attribute_number4 contenido_neto_num
, esyb.attribute_number5 presentacion_de_venta_num
, esyb.attribute_number6 vida_de_anaquel
, esyb.attribute25 familia_as400
, esyb.attribute_number7 porcentaje_grasa
, esyb.attribute26 contenido_neto
, ag.grupo
, ag.category_code
, ag.alp_admon_ventas_familia
, ag.desc_alp_admon_ventas_familia
, ag.alp_admon_ventas_presentacion
, ag.desc_alp_admon_ventas_presentacion
, ag.alp_producciones_familia
, ag.desc_alp_producciones_familia
, ag.alp_producciones_subfamilia
, ag.desc_alp_producciones_subfamilia
, ag.alp_producciones_presentacion
, ag.desc_alp_producciones_presentacion
, ag.alp_sop_macrocategoria
, ag.desc_alp_sop_macrocategoria
, ag.alp_sop_subcategoria
, ag.desc_alp_sop_subcategoria
, ag.alp_sop_producto_presentacion
, ag.desc_alp_sop_producto_presentacion
, ag.alp_sop_submarca
, ag.desc_alp_sop_submarca
, ag.alp_sop_subcategoria_comercial
, ag.desc_alp_sop_subcategoria_comercial
, ac1.category_code alp_cat_compras
, ac2.category_code alp_cst_cat
, ac3.category_code alp_inventarios
, ac4.category_code alp_cuenta_leche
, ac5.category_code alp_desperdicios
, ac6.category_code alp_reporte_cuenta_de_leche
, eirb.cross_reference sku
, eirbc.cross_reference sku_caja
, eirbt.cross_reference sku_tarima
, eirbch.cross_reference sku_charola
, ac.cost_book_code manufactura_book_code
, ac.cost_book manufactura_book
, ac.cost_val_unit_code manufactura_val_unit_code
, ac.cost_manofactura manufactura_cost
, ac.cost_uom_code manufactura_uom_code
, ac.cost_currency manufactura_currency
, ao.work_definition_header_name nombre_estructura
, ao.alternate_bom_designator nombre_proceso
, esyb.tax_code facturacion_impuesto_sobre_venta_code
, ( SELECT percentage_rate
FROM zx_rates_b
WHERE tax_rate_code = esyb.tax_code) facturacion_impuesto_sobre_venta
FROM egp_system_items_b esyb
, egp_system_items_tl esit
, inv_org_parameters iop
, hr_all_organization_units haou
, alp_groups ag
, egp_item_relationships_b eirb
, egp_item_relationships_b eirbc
, egp_item_relationships_b eirbt
, egp_item_relationships_b eirbch
, egp_item_classes_tl eict
, egp_item_status_tl eist
, alp_groups ac1
, alp_groups ac2
, alp_groups ac3
, alp_groups ac4
, alp_groups ac5
, alp_groups ac6
, alp_costos ac
, alp_ot ao
WHERE esyb.organization_id = esit.organization_id
AND esyb.inventory_item_id = esit.inventory_item_id
AND esyb.organization_id = iop.organization_id
AND iop.organization_id = haou.organization_id
AND esyb.organization_id = ag.org_id(+)
AND esyb.inventory_item_id = ag.item_id(+)
AND esyb.inventory_item_id = eirb.inventory_item_id(+)
AND esyb.inventory_item_id = eirbc.inventory_item_id(+)
AND esyb.inventory_item_id = eirbt.inventory_item_id(+)
AND esyb.inventory_item_id = eirbch.inventory_item_id(+)
AND esyb.item_catalog_group_id = eict.item_class_id(+)
AND esyb.inventory_item_status_code = eist.inventory_item_status_code
AND esyb.inventory_item_id = ac1.item_id(+)
AND esyb.organization_id = ac1.org_id(+)
AND esyb.inventory_item_id = ac2.item_id(+)
AND esyb.organization_id = ac2.org_id(+)
AND esyb.inventory_item_id = ac3.item_id(+)
AND esyb.organization_id = ac3.org_id(+)
AND esyb.inventory_item_id = ac4.item_id(+)
AND esyb.organization_id = ac4.org_id(+)
AND esyb.inventory_item_id = ac5.item_id(+)
AND esyb.organization_id = ac5.org_id(+)
AND esyb.inventory_item_id = ac6.item_id(+)
AND esyb.organization_id = ac6.org_id(+)
AND esyb.inventory_item_id = ac.item_id(+)
AND esyb.organization_id = ac.org_id(+)
AND esyb.organization_id = ao.organization_id(+)
AND esyb.inventory_item_id = ao.inventory_item_id(+)
AND ac.reg(+) = 1
AND ao.reg(+) = 1
AND esit.language = 'E'
AND eict.language(+) = 'E'
AND eist.language = 'E'
AND eirb.sub_type(+) = 'SKU'
AND eirbc.sub_type(+) = 'SKU'
AND eirbc.uom_code(+) = 'CJA'
AND eirbt.sub_type(+) = 'SKU'
AND eirbt.uom_code(+) = 'TRM'
AND eirbch.sub_type(+) = 'SKU'
AND eirbch.uom_code(+) = 'TCH'
AND eirb.uom_code IS NULL
AND ac1.category_code(+) = 'ALP_CAT_COMPRAS'
AND ac2.category_code(+) = 'ALP_CST_CAT'
AND ac3.category_code(+) = 'ALP_INVENTARIOS'
AND ac4.category_code(+) = 'ALP_CUENTA_LECHE'
AND ac5.category_code(+) = 'ALP_DESPERDICIOS'
AND ac6.category_code(+) = 'ALP_REPORTE_CUENTA_DE_LECHE'
AND iop.organization_code = 'OMA'
ORDER BY iop.organization_code
, esyb.item_number
, ag.grupo