Perfiles de Artículos (alp_cat_inv_perfiles_items)
1. Modulo
Inventarios (INV)
2. Descripción
Esta vista analítica consolida la relación de artículos (productos) asignados a cada perfil comercial, detallando sus atributos de empaque (item_uom, item_quantity, item_upc) y vigencias.
La vista realiza la validación de inventario al cruzar los artículos permitidos contra las dos organizaciones de inventario (almacenes org1_code y org2_code) asignadas al perfil comercial del cliente. De esta forma, el sistema puede determinar si un almacén específico está autorizado para surtir un artículo determinado dentro del perfil.
3. Columnas
| No. | Columna | Descripción |
|---|---|---|
| 1 | perfil_code | Código del perfil comercial asignado (ALP_PERFIL). |
| 2 | perfil | Descripción del perfil comercial del cliente. |
| 3 | org_code | Código de la organización de inventario (Almacén/CEDIS). |
| 4 | item_code | Código de artículo único en Oracle Fusion ERP. |
| 5 | item | Descripción o nombre comercial del artículo. |
| 6 | item_uom | Unidad de medida base del artículo para el perfil (item_uom). |
| 7 | item_quantity | Cantidad de equivalencia de empaque o volumen. |
| 8 | item_upc | Código de barras estándar (UPC/EAN) asociado al artículo. |
| 9 | perfil_enabled | Indicador de estado del perfil comercial (Activo/Inactivo). |
| 10 | perfil_start_date | Fecha de inicio de vigencia de la asignación del perfil. |
| 11 | perfil_end_date | Fecha de término de vigencia de la asignación del perfil. |
| 12 | item_enabled | Indicador de estado del artículo dentro del perfil. |
| 13 | item_start_date | Fecha de inicio de vigencia de la relación del artículo. |
| 14 | item_end_date | Fecha de término de vigencia de la relación del artículo. |
4. Objesto Databricks - Oracle
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 | crm_crm_hz_customeraccountsiteextractpvo | hz_cust_acct_sites_all | Sitios y sucursales vinculados a las cuentas de los clientes. |
| 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_scmrcs_invorgparametersextractpvo | inv_org_parameters | Parámetros de configuración de las organizaciones de inventario. |
5. Querys
- Databricks DEV
- Databricks UAT
- Databricks PROD
- Oracle
with alp_lookup as
( select flv.lookuptype lookup_type
, flv.lookupcode lookup_code
, flvt.meaning
, flv.tag
, flv.viewapplicationid view_application_id
, flv.attribute1
, flv.attribute4
, flv.attribute5
, flv.enabledflag enabled_flag
, flv.startdateactive start_date_active
, flv.enddateactive end_date_active
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.viewapplicationid = flvt.viewapplicationid
and flvt.language = 'E')
, alp_perfil as
( select hcasa.attribute19 perfil_code
, get(split(hcasa.attribute20, ','),0) tipo_pedido1
, get(split(hcasa.attribute20, ','),1) tipo_pedido2
from cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_customeraccountsiteextractpvo_dev hcasa
join alp_lookup al1
on hcasa.attribute19 = al1.lookup_code
where al1.lookup_type = 'ALP_PERFIL')
, alp_perfil_orgs as
( select ap.perfil_code
, iop1.organizationcode org1_code
, iop2.organizationcode org2_code
from alp_perfil ap
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_scmrcs_invorgparametersextractpvo_dev iop1
on ELEMENT_AT(FILTER(SPLIT(ap.tipo_pedido1, '_'), x -> x <> ''),2) = iop1.organizationcode
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_scmrcs_invorgparametersextractpvo_dev iop2
on ELEMENT_AT(FILTER(SPLIT(ap.tipo_pedido2, '_'), x -> x <> ''),2) = iop2.organizationcode
group by ap.perfil_code
, iop1.organizationcode
, iop2.organizationcode)
, alp_items as
( select alp.lookup_code perfil_code
, alp.meaning perfil
, iop.organizationcode org_code
, esib.itembasepeoitemnumber item_code
, esit.itemtranslationpeodescription item
, ali.attribute1 item_uom
, ali.attribute4 item_quantity
, ali.attribute5 item_upc
, alp.enabled_flag perfil_enabled
, alp.start_date_active perfil_start_date
, alp.end_date_active perfil_end_date
, ali.enabled_flag item_enabled
, ali.start_date_active item_start_date
, ali.end_date_active item_end_date
from alp_lookup alp
join alp_lookup ali
on alp.lookup_code = ali.lookup_type
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemextractpvo_dev esib
on upper(trim(ali.lookup_code)) = esib.itembasepeoitemnumber
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemtranslationextractpvo_dev esit
on esib.itembasepeoorganizationid = esit.itemtranslationpeoorganizationid
and esib.itembasepeoinventoryitemid = esit.itemtranslationpeoinventoryitemid
and esit.itemtranslationpeolanguage = 'E'
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_scmrcs_invorgparametersextractpvo_dev iop
on esib.itembasepeoorganizationid = iop.organizationid
where alp.lookup_type = 'ALP_PERFIL')
select ai.perfil_code
, ai.perfil
, ai.org_code
, ai.item_code
, ai.item
, ai.item_uom
, ai.item_quantity
, ai.item_upc
, ai.perfil_enabled
, ai.perfil_start_date
, ai.perfil_end_date
, ai.item_enabled
, ai.item_start_date
, ai.item_end_date
from alp_items ai
join alp_perfil_orgs apo
on ai.perfil_code = apo.perfil_code
and ai.org_code = apo.org1_code
union
select ai.perfil_code
, ai.perfil
, ai.org_code
, ai.item_code
, ai.item
, ai.item_uom
, ai.item_quantity
, ai.item_upc
, ai.perfil_enabled
, ai.perfil_start_date
, ai.perfil_end_date
, ai.item_enabled
, ai.item_start_date
, ai.item_end_date
from alp_items ai
join alp_perfil_orgs apo
on ai.perfil_code = apo.perfil_code
and ai.org_code = apo.org2_code
order by 1
, 3
, 5
, 7
with alp_lookup as
( select flv.lookuptype lookup_type
, flv.lookupcode lookup_code
, flvt.meaning
, flv.tag
, flv.viewapplicationid view_application_id
, flv.attribute1
, flv.attribute4
, flv.attribute5
, flv.enabledflag enabled_flag
, flv.startdateactive start_date_active
, flv.enddateactive end_date_active
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.viewapplicationid = flvt.viewapplicationid
and flvt.language = 'E')
, alp_perfil as
( select hcasa.attribute19 perfil_code
, get(split(hcasa.attribute20, ','),0) tipo_pedido1
, get(split(hcasa.attribute20, ','),1) tipo_pedido2
from cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_customeraccountsiteextractpvo_stg hcasa
join alp_lookup al1
on hcasa.attribute19 = al1.lookup_code
where al1.lookup_type = 'ALP_PERFIL')
, alp_perfil_orgs as
( select ap.perfil_code
, iop1.organizationcode org1_code
, iop2.organizationcode org2_code
from alp_perfil ap
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_scmrcs_invorgparametersextractpvo_stg iop1
on ELEMENT_AT(FILTER(SPLIT(ap.tipo_pedido1, '_'), x -> x <> ''),2) = iop1.organizationcode
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_scmrcs_invorgparametersextractpvo_stg iop2
on ELEMENT_AT(FILTER(SPLIT(ap.tipo_pedido2, '_'), x -> x <> ''),2) = iop2.organizationcode
group by ap.perfil_code
, iop1.organizationcode
, iop2.organizationcode)
, alp_items as
( select alp.lookup_code perfil_code
, alp.meaning perfil
, iop.organizationcode org_code
, esib.itembasepeoitemnumber item_code
, esit.itemtranslationpeodescription item
, ali.attribute1 item_uom
, ali.attribute4 item_quantity
, ali.attribute5 item_upc
, alp.enabled_flag perfil_enabled
, alp.start_date_active perfil_start_date
, alp.end_date_active perfil_end_date
, ali.enabled_flag item_enabled
, ali.start_date_active item_start_date
, ali.end_date_active item_end_date
from alp_lookup alp
join alp_lookup ali
on alp.lookup_code = ali.lookup_type
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemextractpvo_stg esib
on upper(trim(ali.lookup_code)) = esib.itembasepeoitemnumber
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemtranslationextractpvo_stg esit
on esib.itembasepeoorganizationid = esit.itemtranslationpeoorganizationid
and esib.itembasepeoinventoryitemid = esit.itemtranslationpeoinventoryitemid
and esit.itemtranslationpeolanguage = 'E'
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_scmrcs_invorgparametersextractpvo_stg iop
on esib.itembasepeoorganizationid = iop.organizationid
where alp.lookup_type = 'ALP_PERFIL')
select ai.perfil_code
, ai.perfil
, ai.org_code
, ai.item_code
, ai.item
, ai.item_uom
, ai.item_quantity
, ai.item_upc
, ai.perfil_enabled
, ai.perfil_start_date
, ai.perfil_end_date
, ai.item_enabled
, ai.item_start_date
, ai.item_end_date
from alp_items ai
join alp_perfil_orgs apo
on ai.perfil_code = apo.perfil_code
and ai.org_code = apo.org1_code
union
select ai.perfil_code
, ai.perfil
, ai.org_code
, ai.item_code
, ai.item
, ai.item_uom
, ai.item_quantity
, ai.item_upc
, ai.perfil_enabled
, ai.perfil_start_date
, ai.perfil_end_date
, ai.item_enabled
, ai.item_start_date
, ai.item_end_date
from alp_items ai
join alp_perfil_orgs apo
on ai.perfil_code = apo.perfil_code
and ai.org_code = apo.org2_code
order by 1
, 3
, 5
, 7
with alp_lookup as
( select flv.lookuptype lookup_type
, flv.lookupcode lookup_code
, flvt.meaning
, flv.tag
, flv.viewapplicationid view_application_id
, flv.attribute1
, flv.attribute4
, flv.attribute5
, flv.enabledflag enabled_flag
, flv.startdateactive start_date_active
, flv.enddateactive end_date_active
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.viewapplicationid = flvt.viewapplicationid
and flvt.language = 'E')
, alp_perfil as
( select hcasa.attribute19 perfil_code
, get(split(hcasa.attribute20, ','),0) tipo_pedido1
, get(split(hcasa.attribute20, ','),1) tipo_pedido2
from cat_master_oracle_fusion.sch_gold_layer.crm_crm_hz_customeraccountsiteextractpvo_prd hcasa
join alp_lookup al1
on hcasa.attribute19 = al1.lookup_code
where al1.lookup_type = 'ALP_PERFIL')
, alp_perfil_orgs as
( select ap.perfil_code
, iop1.organizationcode org1_code
, iop2.organizationcode org2_code
from alp_perfil ap
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_scmrcs_invorgparametersextractpvo_prd iop1
on ELEMENT_AT(FILTER(SPLIT(ap.tipo_pedido1, '_'), x -> x <> ''),2) = iop1.organizationcode
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_scmrcs_invorgparametersextractpvo_prd iop2
on ELEMENT_AT(FILTER(SPLIT(ap.tipo_pedido2, '_'), x -> x <> ''),2) = iop2.organizationcode
group by ap.perfil_code
, iop1.organizationcode
, iop2.organizationcode)
, alp_items as
( select alp.lookup_code perfil_code
, alp.meaning perfil
, iop.organizationcode org_code
, esib.itembasepeoitemnumber item_code
, esit.itemtranslationpeodescription item
, ali.attribute1 item_uom
, ali.attribute4 item_quantity
, ali.attribute5 item_upc
, alp.enabled_flag perfil_enabled
, alp.start_date_active perfil_start_date
, alp.end_date_active perfil_end_date
, ali.enabled_flag item_enabled
, ali.start_date_active item_start_date
, ali.end_date_active item_end_date
from alp_lookup alp
join alp_lookup ali
on alp.lookup_code = ali.lookup_type
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemextractpvo_prd esib
on upper(trim(ali.lookup_code)) = esib.itembasepeoitemnumber
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_egp_itemtranslationextractpvo_prd esit
on esib.itembasepeoorganizationid = esit.itemtranslationpeoorganizationid
and esib.itembasepeoinventoryitemid = esit.itemtranslationpeoinventoryitemid
and esit.itemtranslationpeolanguage = 'E'
join cat_master_oracle_fusion.sch_gold_layer.fscm_scm_scmrcs_invorgparametersextractpvo_prd iop
on esib.itembasepeoorganizationid = iop.organizationid
where alp.lookup_type = 'ALP_PERFIL')
select ai.perfil_code
, ai.perfil
, ai.org_code
, ai.item_code
, ai.item
, ai.item_uom
, ai.item_quantity
, ai.item_upc
, ai.perfil_enabled
, ai.perfil_start_date
, ai.perfil_end_date
, ai.item_enabled
, ai.item_start_date
, ai.item_end_date
from alp_items ai
join alp_perfil_orgs apo
on ai.perfil_code = apo.perfil_code
and ai.org_code = apo.org1_code
union
select ai.perfil_code
, ai.perfil
, ai.org_code
, ai.item_code
, ai.item
, ai.item_uom
, ai.item_quantity
, ai.item_upc
, ai.perfil_enabled
, ai.perfil_start_date
, ai.perfil_end_date
, ai.item_enabled
, ai.item_start_date
, ai.item_end_date
from alp_items ai
join alp_perfil_orgs apo
on ai.perfil_code = apo.perfil_code
and ai.org_code = apo.org2_code
order by 1
, 3
, 5
, 7
WITH alp_perfil AS
( SELECT hcasa.attribute19 perfil_code
, REGEXP_SUBSTR(hcasa.attribute20, '[^,]+', 1, 1) tipo_pedido1
, REGEXP_SUBSTR(hcasa.attribute20, '[^,]+', 1, 2) tipo_pedido2
FROM hz_cust_acct_sites_all hcasa
, fnd_lookup_values flv
WHERE hcasa.attribute19 = flv.lookup_code
AND flv.lookup_type = 'ALP_PERFILES'
AND flv.language = 'E')
, alp_perfil_orgs AS
( SELECT ap.perfil_code
, iop1.organization_code org1_code
, iop2.organization_code org2_code
FROM alp_perfil ap
, inv_org_parameters iop1
, inv_org_parameters iop2
WHERE REGEXP_SUBSTR(ap.tipo_pedido1, '[^_]+', 1, 2) = iop1.organization_code
AND REGEXP_SUBSTR(ap.tipo_pedido2, '[^_]+', 1, 2) = iop2.organization_code(+)
GROUP BY ap.perfil_code
, iop1.organization_code
, iop2.organization_code)
, alp_items AS
( SELECT flvp.lookup_code perfil_code
, flvp.meaning perfil
, iop.organization_code org_code
, esib.item_number item_code
, esit.description item
, flvi.attribute1 item_uom
, flvi.attribute4 item_quantity
, flvi.attribute5 item_upc
, flvp.enabled_flag perfil_enabled
, flvp.start_date_active perfil_start_date
, flvp.end_date_active perfil_end_date
, flvi.enabled_flag item_enabled
, flvi.start_date_active item_start_date
, flvi.end_date_active item_end_date
FROM fnd_lookup_values flvp
, fnd_lookup_values flvi
, egp_system_items_b esib
, egp_system_items_tl esit
, inv_org_parameters iop
WHERE flvp.lookup_code = flvi.lookup_type
AND flvp.view_application_id = flvi.view_application_id
AND flvp.language = flvi.language
AND UPPER(TRIM(flvi.lookup_code)) = esib.item_number
AND esib.organization_id = iop.organization_id
AND esib.organization_id = esit.organization_id
AND esib.inventory_item_id = esit.inventory_item_id
AND flvp.lookup_type = 'ALP_PERFILES'
AND flvp.language = 'E'
AND esit.language = 'E')
SELECT ai.perfil_code
, ai.perfil
, ai.org_code
, ai.item_code
, ai.item
, ai.item_uom
, ai.item_quantity
, ai.item_upc
, ai.perfil_enabled
, ai.perfil_start_date
, ai.perfil_end_date
, ai.item_enabled
, ai.item_start_date
, ai.item_end_date
FROM alp_items ai
, alp_perfil_orgs apo
WHERE ai.perfil_code = apo.perfil_code
AND ai.org_code = apo.org1_code
UNION
SELECT ai.perfil_code
, ai.perfil
, ai.org_code
, ai.item_code
, ai.item
, ai.item_uom
, ai.item_quantity
, ai.item_upc
, ai.perfil_enabled
, ai.perfil_start_date
, ai.perfil_end_date
, ai.item_enabled
, ai.item_start_date
, ai.item_end_date
FROM alp_items ai
, alp_perfil_orgs apo
WHERE ai.perfil_code = apo.perfil_code
AND ai.org_code = apo.org2_code
ORDER BY 1
, 3
, 5
, 7