Scribd
Upload
16 views8 pages

Rebates 1007

The document is a SQL query that retrieves detailed inventory transaction data including product codes, names, quantities, costs, and various rebate percentages for a specified period and vendor. It includes multiple joins to gather relevant information from different tables related to inventory movements and agreements. The query also calculates total costs and various financial metrics based on the inventory transactions.

Uploaded by

miguel8888
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
16 views8 pages

Rebates 1007

The document is a SQL query that retrieves detailed inventory transaction data including product codes, names, quantities, costs, and various rebate percentages for a specified period and vendor. It includes multiple joins to gather relevant information from different tables related to inventory movements and agreements. The query also calculates total costs and various financial metrics based on the inventory transactions.

Uploaded by

miguel8888
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 8

Select PRD.

prd_lvl_number "Código de Producto",


PRD.prd_full_name "Nombre de Prd.",
inv.trans_uom "Unidad",
inv.trans_qty "Cantidad",
inv.trans_cost "Costo Unitario",
decode(inv.inv_eff_cst,
' ',
0,
'-',
inv.trans_ext_cost * -1,
inv.trans_ext_cost) costo_total,

e.inv_type_desc "Tipo de inventario",


c.tbl_code_name "Balance de inventario",
org.des_cadena,
org.org_lvl_number "Local",
org.org_name_full "Nombre de local",
inv.trans_date "Fecha Movimiento",
to_char(inv.posted_date_time, 'dd/mm/yyyy hh24:mi:ss') "Fecha Registro",

m.inv_mrpt_code "Cod Mov. Maestro",


m.inv_mrpt_desc "Descripción Mov. Maestro",
d.inv_drpt_code "Cod. Mov. Detalle",
d.inv_drpt_desc "Descripción Mov. Detalle",
inv.trans_ref "Documento",
PRD.cod_area "Area",
PRD.des_area "Descripción Area",
PRD.cod_sec "Seccion",
PRD.des_sec "Descripción Seccion",
PRD.cod_cat "Linea",
PRD.des_cat "Descripción Linea",
PRD.cod_fam "Familia",
PRD.des_fam "Descripción Familia",
PRD.cod_sfam "Subfamilia",
PRD.des_sfam "Descripción Sub Familia",
a.trans_user "Usuario",
vpc.vendor_number "Código Proveedor",
vpc.vendor_name "Nombre Proveedor",
to_char(nvl(inv.pmg_po_number,'0'),'FM9999999999') "Orden de Compra",
PRD.cod_tipneg||'-'||PRD.des_tipneg "Tipo de Producto",
to_char(inv.audit_number,'FM9999999999999') "Audit_Number",

XX.REB_PCNT "Rebate%",
XX.REB_PCNT / 100 * ( decode(inv.inv_eff_cst,
' ',
0,
'-',
inv.trans_ext_cost * -1,
inv.trans_ext_cost)) "Rebate",
xx.GIC_PCNT "GIC%",
XX.GIC_PCNT / 100 * ( decode(inv.inv_eff_cst,
' ',
0,
'-',
inv.trans_ext_cost * -1,
inv.trans_ext_cost)) "GIC",
xx.GOC_PCNT "GOC%",
XX.GOC_PCNT / 100 * ( decode(inv.inv_eff_cst,
' ',
0,
'-',
inv.trans_ext_cost * -1,
inv.trans_ext_cost)) "GOC",
xx.FPROM_PCNT "F.Prom%",
XX.FPROM_PCNT / 100 * ( decode(inv.inv_eff_cst,
' ',
0,
'-',
inv.trans_ext_cost * -1,
inv.trans_ext_cost)) "Fondo Prom",
xx.NDEV_PCNT "No dev%",
XX.NDEV_PCNT / 100 * ( decode(inv.inv_eff_cst,
' ',
0,
'-',
inv.trans_ext_cost * -1,
inv.trans_ext_cost)) "No Devolucion",
xx.BLOG_PCNT "Bon.Log%",
XX.blog_PCNT / 100 * ( decode(inv.inv_eff_cst,
' ',
0,
'-',
inv.trans_ext_cost * -1,
inv.trans_ext_cost)) "Bonif.Log"
from (select X.Movid,
Sum(REB_PCNT) REB_PCNT,
Sum(GIC_PCNT) GIC_PCNT,
Sum(GOC_PCNT) GOC_PCNT,
Sum(FPROM_PCNT) FPROM_PCNT,
Sum(NDEV_PCNT) NDEV_PCNT,
Sum(BLOG_PCT) BLOG_PCNT
from (select i.rowid MovID,
(RBM.vpc_alw_pcnt) REB_PCNT,
0 GIC_PCNT,
0 GOC_PCNT,
0 FPROM_PCNT,
0 NDEV_PCNT,
0 BLOG_PCT
from invaudee i
inner join (select distinct y.inv_mrpt_code, y.inv_drpt_code --
SACA TODOS LOS MOVIMIENTOS VALIDOS PARA REBATES
from vpcevnee t, rbmvcdcd y
where t.vpc_evnt_key = y.vpc_evnt_key) r
on r.inv_mrpt_code = i.inv_mrpt_code
and r.inv_drpt_code = i.inv_drpt_code
inner join ifhprdmst p
on p.prd_lvl_child = i.trans_prd_child
inner join ifhorgmst o
on o.org_lvl_child = i.trans_org_child
and o.tienda_piloto = 'T'
inner join vpcmstee v
on v.vpc_tech_key = i.vpc_tech_key
inner join tblflcee c
on c.tbl_code = trim(i.inv_eff_bal)
and c.tbl_field = 'INV_BALANCE_EFF'
inner join ifhrbmhst RBM -- INI query acuerdos a procesar
on RBM.vpc_evnt_key = 214 --RBMATE COMPRAS
and RBM.id_backup='@PERIODO YYYYMM@'
-- and RBM.id_backup = '202205'
and RBM.vpc_tech_key = i.vpc_tech_key
and (RBM.prd_lvl_child = p.prd_lvl_child or
RBM.prd_lvl_child = p.area_lvl_child or
RBM.prd_lvl_child = p.secc_lvl_child or
RBM.prd_lvl_child = p.cat_lvl_child or
RBM.prd_lvl_child = p.fam_lvl_child or
RBM.prd_lvl_child = p.sfam_lvl_child)
and (RBM.org_lvl_child = o.org_lvl_child or
RBM.org_lvl_child = o.org_child_cadena or
RBM.org_lvl_child = o.org_child_formato)
where
--to_char(i.trans_date, 'YYYYMM') = '@PERIODO YYYYMM@'
i.trans_date between
to_date('@PERIODO YYYYMM@'||'01','yyyymmdd') and last_day
(to_date('@PERIODO YYYYMM@'||'01','yyyymmdd'))
-- to_date('202205' || '01', 'yyyymmdd') and
--(to_date(to_char(To_number('202205') + 1) || '01', 'yyyymmdd')
- 1)
and v.vendor_number = @COD PROVEEDOR@

UNION ALL
select i.rowid MovID,
0 REB_PCNT,
(RBM.vpc_alw_pcnt) GIC_PCNT,
0 GOC_PCNT,
0 FPROM_PCNT,
0 NDEV_PCNT,
0 BLOG_PCT
from invaudee i
inner join (select distinct y.inv_mrpt_code, y.inv_drpt_code --
SACA TODOS LOS MOVIMIENTOS VALIDOS PARA REBATES
from vpcevnee t, rbmvcdcd y
where t.vpc_evnt_key = y.vpc_evnt_key) r
on r.inv_mrpt_code = i.inv_mrpt_code
and r.inv_drpt_code = i.inv_drpt_code
inner join ifhprdmst p
on p.prd_lvl_child = i.trans_prd_child
inner join ifhorgmst o
on o.org_lvl_child = i.trans_org_child
and o.tienda_piloto = 'T'
inner join vpcmstee v
on v.vpc_tech_key = i.vpc_tech_key
inner join tblflcee c
on c.tbl_code = trim(i.inv_eff_bal)
and c.tbl_field = 'INV_BALANCE_EFF'

inner join ifhrbmhst RBM -- INI query acuerdos a procesar


on RBM.vpc_evnt_key = 219 --GIC
and RBM.id_backup = '@PERIODO YYYYMM@'
--and RBM.id_backup = '202205'
and RBM.vpc_tech_key = i.vpc_tech_key
and (RBM.prd_lvl_child = p.prd_lvl_child or
RBM.prd_lvl_child = p.area_lvl_child or
RBM.prd_lvl_child = p.secc_lvl_child or
RBM.prd_lvl_child = p.cat_lvl_child or
RBM.prd_lvl_child = p.fam_lvl_child or
RBM.prd_lvl_child = p.sfam_lvl_child)
and (RBM.org_lvl_child = o.org_lvl_child or
RBM.org_lvl_child = o.org_child_cadena or
RBM.org_lvl_child = o.org_child_formato)
where
-- to_char(i.trans_date, 'YYYYMM') = '@PERIODO YYYYMM@'
i.trans_date between
to_date('@PERIODO YYYYMM@'||'01','yyyymmdd') and last_day
(to_date('@PERIODO YYYYMM@'||'01','yyyymmdd'))
-- to_date('202205' || '01', 'yyyymmdd') and
-- (to_date(to_char(To_number('202205') + 1) || '01',
-- 'yyyymmdd') - 1)
and v.vendor_number = @COD PROVEEDOR@
-- and v.vendor_number = 2010011361
--group by i.audit_number

UNION ALL
select i.rowid MOVID,
0 REB_PCNT,
0 GIC_PCNT,
(RBM.vpc_alw_pcnt) GOC_PCNT,
0 FPROM_PCNT,
0 NDEV_PCNT,
0 BLOG_PCT
from invaudee i
inner join (select distinct y.inv_mrpt_code, y.inv_drpt_code --
SACA TODOS LOS MOVIMIENTOS VALIDOS PARA REBATES
from vpcevnee t, rbmvcdcd y
where t.vpc_evnt_key = y.vpc_evnt_key) r
on r.inv_mrpt_code = i.inv_mrpt_code
and r.inv_drpt_code = i.inv_drpt_code
inner join ifhprdmst p
on p.prd_lvl_child = i.trans_prd_child
inner join ifhorgmst o
on o.org_lvl_child = i.trans_org_child
and o.tienda_piloto = 'T'
inner join vpcmstee v
on v.vpc_tech_key = i.vpc_tech_key
inner join tblflcee c
on c.tbl_code = trim(i.inv_eff_bal)
and c.tbl_field = 'INV_BALANCE_EFF'

inner join ifhrbmhst RBM -- INI query acuerdos a procesar


on RBM.vpc_evnt_key = 212 --GOC
and RBM.id_backup = '@PERIODO YYYYMM@'
-- and RBM.id_backup = '202205'
and RBM.vpc_tech_key = i.vpc_tech_key
and (RBM.prd_lvl_child = p.prd_lvl_child or
RBM.prd_lvl_child = p.area_lvl_child or
RBM.prd_lvl_child = p.secc_lvl_child or
RBM.prd_lvl_child = p.cat_lvl_child or
RBM.prd_lvl_child = p.fam_lvl_child or
RBM.prd_lvl_child = p.sfam_lvl_child)
and (RBM.org_lvl_child = o.org_lvl_child or
RBM.org_lvl_child = o.org_child_cadena or
RBM.org_lvl_child = o.org_child_formato)
where
-- to_char(i.trans_date, 'YYYYMM') = '@PERIODO YYYYMM@'
i.trans_date between
to_date('@PERIODO YYYYMM@'||'01','yyyymmdd') and last_day
(to_date('@PERIODO YYYYMM@'||'01','yyyymmdd'))
-- to_date('202205' || '01', 'yyyymmdd') and
-- (to_date(to_char(To_number('202205') + 1) || '01',
-- 'yyyymmdd') - 1)
and v.vendor_number = @COD PROVEEDOR@
-- group by i.audit_number

UNION ALL
select i.rowid MOVID,
0 REB_PCNT,
0 GIC_PCNT,
0 GOC_PCNT,
(RBM.vpc_alw_pcnt) FPROM_PCNT,
0 NDEV_PCNT,
0 BLOG_PCT
from invaudee i
inner join (select distinct y.inv_mrpt_code, y.inv_drpt_code --
SACA TODOS LOS MOVIMIENTOS VALIDOS PARA REBATES
from vpcevnee t, rbmvcdcd y
where t.vpc_evnt_key = y.vpc_evnt_key) r
on r.inv_mrpt_code = i.inv_mrpt_code
and r.inv_drpt_code = i.inv_drpt_code
inner join ifhprdmst p
on p.prd_lvl_child = i.trans_prd_child
inner join ifhorgmst o
on o.org_lvl_child = i.trans_org_child
and o.tienda_piloto = 'T'
inner join vpcmstee v
on v.vpc_tech_key = i.vpc_tech_key
inner join tblflcee c
on c.tbl_code = trim(i.inv_eff_bal)
and c.tbl_field = 'INV_BALANCE_EFF'

inner join ifhrbmhst RBM -- INI query acuerdos a procesar


on RBM.vpc_evnt_key = 221 --Fondo Promocional FPROM
and RBM.id_backup='@PERIODO YYYYMM@'
-- and RBM.id_backup = '202205'
and RBM.vpc_tech_key = i.vpc_tech_key
and (RBM.prd_lvl_child = p.prd_lvl_child or
RBM.prd_lvl_child = p.area_lvl_child or
RBM.prd_lvl_child = p.secc_lvl_child or
RBM.prd_lvl_child = p.cat_lvl_child or
RBM.prd_lvl_child = p.fam_lvl_child or
RBM.prd_lvl_child = p.sfam_lvl_child)
and (RBM.org_lvl_child = o.org_lvl_child or
RBM.org_lvl_child = o.org_child_cadena or
RBM.org_lvl_child = o.org_child_formato)
where
--to_char(i.trans_date, 'YYYYMM') = '@PERIODO YYYYMM@'
i.trans_date between
to_date('@PERIODO YYYYMM@'||'01','yyyymmdd') and last_day
(to_date('@PERIODO YYYYMM@'||'01','yyyymmdd'))
-- to_date('202205' || '01', 'yyyymmdd') and
--(to_date(to_char(To_number('202205') + 1) || '01',
-- 'yyyymmdd') - 1)
and v.vendor_number = @COD PROVEEDOR@
-- group by i.audit_number
UNION ALL
select i.rowid MOVID,
0 REB_PCNT,
0 GIC_PCNT,
0 GOC_PCNT,
0 FPROM_PCNT,
(RBM.vpc_alw_pcnt) NDEV_PCNT,
0 BLOG_PCT
from invaudee i
inner join (select distinct y.inv_mrpt_code, y.inv_drpt_code --
SACA TODOS LOS MOVIMIENTOS VALIDOS PARA REBATES
from vpcevnee t, rbmvcdcd y
where t.vpc_evnt_key = y.vpc_evnt_key) r
on r.inv_mrpt_code = i.inv_mrpt_code
and r.inv_drpt_code = i.inv_drpt_code
inner join ifhprdmst p
on p.prd_lvl_child = i.trans_prd_child
inner join ifhorgmst o
on o.org_lvl_child = i.trans_org_child
and o.tienda_piloto = 'T'
inner join vpcmstee v
on v.vpc_tech_key = i.vpc_tech_key
inner join tblflcee c
on c.tbl_code = trim(i.inv_eff_bal)
and c.tbl_field = 'INV_BALANCE_EFF'

inner join ifhrbmhst RBM -- INI query acuerdos a procesar


on RBM.vpc_evnt_key = 213 --No Devolucion NDEV
and RBM.id_backup='@PERIODO YYYYMM@'
-- and RBM.id_backup = '202205'
and RBM.vpc_tech_key = i.vpc_tech_key
and (RBM.prd_lvl_child = p.prd_lvl_child or
RBM.prd_lvl_child = p.area_lvl_child or
RBM.prd_lvl_child = p.secc_lvl_child or
RBM.prd_lvl_child = p.cat_lvl_child or
RBM.prd_lvl_child = p.fam_lvl_child or
RBM.prd_lvl_child = p.sfam_lvl_child)
and (RBM.org_lvl_child = o.org_lvl_child or
RBM.org_lvl_child = o.org_child_cadena or
RBM.org_lvl_child = o.org_child_formato)
where
--to_char(i.trans_date, 'YYYYMM') = '@PERIODO YYYYMM@'
i.trans_date between
to_date('@PERIODO YYYYMM@'||'01','yyyymmdd') and last_day
(to_date('@PERIODO YYYYMM@'||'01','yyyymmdd'))
-- to_date('202205' || '01', 'yyyymmdd') and
-- (to_date(to_char(To_number('202205') + 1) || '01',
-- 'yyyymmdd') - 1)
and v.vendor_number = @COD PROVEEDOR@
-- group by i.audit_number

UNION ALL
select i.rowid MOVID,
0 REB_PCNT,
0 GIC_PCNT,
0 GOC_PCNT,
0 FPROM_PCNT,
0 NDEV_PCNT,
(RBM.vpc_alw_pcnt) BLOG_PCNT
from invaudee i
inner join (select distinct y.inv_mrpt_code, y.inv_drpt_code --
SACA TODOS LOS MOVIMIENTOS VALIDOS PARA REBATES
from vpcevnee t, rbmvcdcd y
where t.vpc_evnt_key = y.vpc_evnt_key) r
on r.inv_mrpt_code = i.inv_mrpt_code
and r.inv_drpt_code = i.inv_drpt_code
inner join ifhprdmst p
on p.prd_lvl_child = i.trans_prd_child
inner join ifhorgmst o
on o.org_lvl_child = i.trans_org_child
and o.tienda_piloto = 'T'
inner join vpcmstee v
on v.vpc_tech_key = i.vpc_tech_key
inner join tblflcee c
on c.tbl_code = trim(i.inv_eff_bal)
and c.tbl_field = 'INV_BALANCE_EFF'

inner join ifhrbmhst RBM -- INI query acuerdos a procesar


on RBM.vpc_evnt_key = 216 --Bonificacion Logistica BONLOG
and RBM.id_backup='@PERIODO YYYYMM@'
-- and RBM.id_backup = '202205'
and RBM.vpc_tech_key = i.vpc_tech_key
and (RBM.prd_lvl_child = p.prd_lvl_child or
RBM.prd_lvl_child = p.area_lvl_child or
RBM.prd_lvl_child = p.secc_lvl_child or
RBM.prd_lvl_child = p.cat_lvl_child or
RBM.prd_lvl_child = p.fam_lvl_child or
RBM.prd_lvl_child = p.sfam_lvl_child)
and (RBM.org_lvl_child = o.org_lvl_child or
RBM.org_lvl_child = o.org_child_cadena or
RBM.org_lvl_child = o.org_child_formato)
where
--to_char(i.trans_date, 'YYYYMM') = '@PERIODO YYYYMM@'
i.trans_date between
to_date('@PERIODO YYYYMM@'||'01','yyyymmdd') and last_day
(to_date('@PERIODO YYYYMM@'||'01','yyyymmdd'))
-- to_date('202205' || '01', 'yyyymmdd') and
-- (to_date(to_char(To_number('202205') + 1) || '01',
-- 'yyyymmdd') - 1)
and v.vendor_number = @COD PROVEEDOR@
) X --Extraccion individual de movimientos y descuentos
group by X.MOVID) XX --DEscuentos sumados consolidados por movimiento
(INVAUDEEE.ROWID)
inner join Invaudee Inv
on inv.rowid = XX.MOVID
inner join IFHPRDMST Prd
on prd.prd_lvl_child = inv.trans_prd_child
inner join ifhorgmst Org
on org.org_lvl_child = inv.trans_org_child
and org.tienda_piloto = 'T'

inner join vpcmstee vpc


on vpc.vpc_tech_key = inv.vpc_tech_key
inner join invtrmee m
on m.inv_mrpt_code = inv.inv_mrpt_code
inner join invtrdee d
on d.inv_mrpt_code = inv.inv_mrpt_code
and d.inv_drpt_code = inv.inv_drpt_code
inner join invtypee e
on e.inv_type_code = inv.trans_type_code
inner join tblflcee c
on c.tbl_code = trim(inv.inv_eff_bal)
and c.tbl_field = 'INV_BALANCE_EFF'
inner join invahree a
on a.trans_session = inv.trans_session

You might also like