Scribd
Upload
107 views8 pages

Consultas Xra Facturar

The document contains SQL queries for retrieving data from a JDA server including receipts, dispatches, inventory status changes, and inventory levels. It includes selects across multiple tables to gather details on parts, locations, orders, shipments, and status mappings for clients. Field values are parameterized to filter the results for a specific client and date range.

Uploaded by

JUBAL FRANCISCO MORENO HERNANDEZ
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
107 views8 pages

Consultas Xra Facturar

The document contains SQL queries for retrieving data from a JDA server including receipts, dispatches, inventory status changes, and inventory levels. It includes selects across multiple tables to gather details on parts, locations, orders, shipments, and status mappings for clients. Field values are parameterized to filter the results for a specific client and date range.

Uploaded by

JUBAL FRANCISCO MORENO HERNANDEZ
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

/*Recibos Comando servidor JDA*/

publish data
where client_id = 'DIAGEO'
and rango = " t.dispatch_dte between to_date('20200916000010' ) and
to_date('20201015235959')"
|
[select t.dispatch_dte,
t.trlr_num,
t.trlr_cod,
r.client_id,
r.po_num,
r.invtyp,
rl.prtnum,
prtdsc.lngdsc,
sum(rl.rcvqty) rcvqty,
rl.rcvsts invsts,
rl.lotnum,
rl.inv_attr_str7,
round(sum(cast(rl.rcvqty as dec) / cast(pfc.untqty as dec)), 2) cajas,
r.supnum,
rm.tohsta,
r.orgref,
r.wh_id,
r.invnum,
uc.werks
from trlr t
inner
join rcvtrk rt
on t.trlr_id = rt.trlr_id
inner
join rcvinv r
on rt.trknum = r.trknum
and rt.wh_id = r.wh_id
inner
join rcvlin rl
on r.wh_id = rl.wh_id
and r.trknum = rl.trknum
and r.client_id = rl.client_id
and r.invnum = rl.invnum
inner
join prtmst
on prtmst.wh_id_tmpl = rl.wh_id
and prtmst.prt_client_id = rl.prt_client_id
and prtmst.prtnum = rl.prtnum
inner
join prtdsc
on prtdsc.colnam = 'prtnum|prt_client_id|wh_id_tmpl'
and prtdsc.colval = /*=varchar(*/ rl.prtnum || '|' || rl.prt_client_id || '|'
|| rl.wh_id /*=)*/
and prtdsc.locale_id = nvl(@locale_id, @@locale_id)
left outer
join prtftp pf
on pf.wh_id = rl.wh_id
and pf.prt_client_id = rl.prt_client_id
and pf.prtnum = rl.prtnum
and pf.defftp_flg = 1
left outer
join prtftp_dtl pfc
on pfc.wh_id = pf.wh_id
and pfc.prt_client_id = pf.prt_client_id
and pfc.prtnum = pf.prtnum
and pfc.ftpcod = pf.ftpcod
and pfc.cas_flg = 1
left outer
join (select rm1.wh_id,
rm1.client_id,
rm1.invnum po_num,
rm1.prtnum,
max(rm1.tohsta) tohsta
from rimlin rm1
group by rm1.wh_id,
rm1.client_id,
rm1.invnum,
rm1.prtnum) rm
on rl.wh_id = rm.wh_id
and rl.client_id = rm.client_id
and r.po_num = rm.po_num
and rl.prtnum = rm.prtnum
left outer
join uc_inv_mov_status_map uc
on rl.client_id = uc.client_id
and rl.wh_id = uc.wh_id
and rl.rcvsts = uc.fr_invsts_wms
where t.trlr_cod = 'RCV'
and t.trlr_stat = 'D'
and rl.rcvqty > 0
and @+t.trlr_num
and @+t.carcod
and @+t.trlr_typ
and @rango:raw
and @+r.wh_id
and r.client_id = @client_id
and @+r.po_num
and @+r.po_num^orden_factura
and @+r.supnum
and @+r.invtyp
and @+rl.prtnum
and @+rl.rcvsts
and @+rl.lotnum
and @+rl.inv_attr_str7
and @+prtmst.prtstyle
group by t.dispatch_dte,
t.trlr_num,
t.trlr_cod,
r.client_id,
r.po_num,
r.invtyp,
rl.prtnum,
prtdsc.lngdsc,
rl.rcvsts,
rl.lotnum,
rl.inv_attr_str7,
r.supnum,
rm.tohsta,
r.orgref,
r.wh_id,
r.invnum,
uc.werks
order by t.dispatch_dte,
r.invnum,
rl.prtnum]

-----------------------------------------------------------------------------------
----------------------------------------------------------------------------
/*Despachos Comando servidor JDA*/
publish data
where client_id = 'DIAGEO'
and rango = " t.dispatch_dte between to_date('20200916000010' ) and
to_date('20201015235959')"
|
[select t.dispatch_dte,
t.trlr_num,
t.trlr_cod,
ord.client_id,
ord.ordnum,
decode(ord.ordtyp, 'NAL', decode(uc.werks, 'CF1', 'NAL', 'TRS'),
ord.ordtyp) AS ordtyp,
ord_line.prtnum,
prtdsc.lngdsc,
sum(iv.untqty) untqty,
iv.invsts,
iv.lotnum,
iv.inv_attr_str7,
round(sum(cast(iv.untqty as dec) / cast(iv.untcas as dec)), 2) cajas,
ord.stcust,
adrmst.adrnam,
ord_note.nottxt idoc,
on1.nottxt notas_envio,
ord.wh_id,
uc.werks
from trlr t
inner
join carhdr c
on t.carcod = c.carcod
inner
join car_move cm
on t.trlr_id = cm.trlr_id
and t.stoloc_wh_id = cm.wh_id
inner
join stop s
on cm.car_move_id = s.car_move_id
inner
join shipment sh
on s.stop_id = sh.stop_id
and cm.wh_id = sh.wh_id
inner
join shipment_line sl
on sh.ship_id = sl.ship_id
and sh.wh_id = sl.wh_id
inner
join ord
on sl.wh_id = ord.wh_id
and sl.client_id = ord.client_id
and sl.ordnum = ord.ordnum
inner
join adrmst
on ord.st_adr_id = adrmst.adr_id
left outer
join ord_note
on ord.wh_id = ord_note.wh_id
and ord.client_id = ord_note.client_id
and ord.ordnum = ord_note.ordnum
and ord_note.nottyp = 'DOCNUM'
left outer
join ord_note on1
on ord.wh_id = on1.wh_id
and ord.client_id = on1.client_id
and ord.ordnum = on1.ordnum
and on1.nottyp = 'S'
and on1.nottxt like '%tick%'
inner
join ord_line
on ord_line.wh_id = sl.wh_id
and ord_line.ordnum = sl.ordnum
and ord_line.client_id = sl.client_id
and ord_line.ordlin = sl.ordlin
and ord_line.ordsln = sl.ordsln
inner
join prtmst
on prtmst.wh_id_tmpl = ord_line.wh_id
and prtmst.prt_client_id = ord_line.prt_client_id
and prtmst.prtnum = ord_line.prtnum
left outer
join invdtl iv
on sl.ship_line_id = iv.ship_line_id
inner
join prtdsc
on prtdsc.colnam = 'prtnum|prt_client_id|wh_id_tmpl'
and prtdsc.colval = ord_line.prtnum || '|' || ord_line.prt_client_id || '|' ||
ord_line.wh_id
and prtdsc.locale_id = nvl(@locale_id, @@locale_id)
left outer
join uc_inv_mov_status_map uc
on ord.client_id = uc.client_id
and ord.wh_id = uc.wh_id
and iv.invsts = uc.fr_invsts_wms
where t.trlr_cod = 'SHIP'
and t.trlr_stat = 'D'
and @+t.trlr_num
and @+t.carcod
and @+t.trlr_typ
and @rango:raw
and @+ord.wh_id
and ord.client_id = @client_id
and @+ord.ordnum
and @+ord.stcust
and @+ord.ordtyp
and @+iv.prtnum
and @+iv.invsts
and @+iv.lotnum
and @+iv.inv_attr_str7
and @+ord_line.ordnum^orden_factura
and @+prtmst.prtstyle
group by t.dispatch_dte,
t.trlr_num,
t.trlr_cod,
ord.client_id,
ord.ordnum,
ord.ordtyp,
ord_line.prtnum,
prtdsc.lngdsc,
iv.invsts,
iv.lotnum,
iv.inv_attr_str7,
ord.stcust,
adrmst.adrnam,
ord_note.nottxt,
ord.wh_id,
on1.nottxt,
uc.werks
order by t.dispatch_dte,
ord.ordnum,
ord_line.prtnum]

-----------------------------------------------------------------------------------
----------------------------------------------------------------------------
/*Cambio de estado de inventario REN->A & SEST->A Comando servidor JDA*/
publish data
where client_id = 'DIAGEO'
and rango = " d.trndte between to_date('20200916000010' ) and
to_date('20201015235959')"
|
/*Modificaciones de Inventario*/
[select to_char(d.trndte, 'yyyy-mm-dd') trndte,
d.actcod,
d.movref,
d.prt_client_id,
d.prtnum,
prtdsc.lngdsc,
sum(d.trnqty) trnqty,
d.frinvs,
d.toinvs,
d.lotnum,
d.inv_attr_str7,
d.wh_id,
d.reacod,
uc.werks
from dlytrn d
inner
join prtdsc
on prtdsc.colnam = 'prtnum|prt_client_id|wh_id_tmpl'
and prtdsc.colval = /*=varchar(*/ d.prtnum || '|' || d.prt_client_id || '|' ||
d.wh_id /*=)*/
and prtdsc.locale_id = nvl(@locale_id, @@locale_id)
left outer
join uc_inv_mov_status_map uc
on d.prt_client_id = uc.client_id
and d.wh_id = uc.wh_id
and d.frinvs = uc.fr_invsts_wms
where @+d.wh_id
and d.actcod = 'INVSTSCHG'
and d.prt_client_id = @client_id
and @+d.prtnum
and @+d.movref
and @+d.oprcod
and @+d.actcod
and d.frinvs in ('REN', 'NCR', 'SEST', 'AVSE', 'NCSE')
and d.toinvs in ('A', 'AV', 'VC', 'NC', 'PV')
and @+d.lotnum
and @+d.inv_attr_str7
and @+d.usr_id
and @rango:raw
and @+d.var_nam
group by to_char(d.trndte, 'yyyy-mm-dd'),
d.actcod,
d.movref,
d.prt_client_id,
d.prtnum,
prtdsc.lngdsc,
d.frinvs,
d.toinvs,
d.lotnum,
d.inv_attr_str7,
d.wh_id,
d.reacod,
uc.werks
order by 1,
5]

-----------------------------------------------------------------------------------
---------------

/*Consulta inventario POP Comando servidor JDA*/


[select iv.wh_id,
dw.lngdsc,
iv.stoloc,
iv.prtnum,
d.lngdsc desc_parte,
sum(iv.untqty) saldo
from inventory_view iv,
locmst l,
aremst a,
prtdsc d,
dscmst dw
where d.colval = iv.prtnum || '|' || iv.prt_client_id || '|' || iv.wh_id
and d.locale_id = @@locale_id
and a.wh_id = l.wh_id
and l.arecod = a.arecod
and l.wh_id = iv.wh_id
and l.stoloc = iv.stoloc
and dw.colnam = 'wh_id'
and dw.colval = iv.wh_id
and dw.locale_id = 'ES-ES'
and iv.prt_client_id = 'DIAGEO'
and a.fwiflg = 1
AND (iv.prtnum like 'POP' or iv.invsts = 'PUB')
group by iv.wh_id,
dw.lngdsc,
iv.stoloc,
iv.prtnum,
d.lngdsc
order by iv.wh_id,
iv.stoloc]
-----------------------------------------------------------------------------------
---------------

/*Consulta para almacenamiento SQL SERVER*/

SELECT a.fecha_corte,
a.wh_id,
wh.nombre desc_wh_id,
wh.ciudad_nombre,
a.prt_client_id,
a.prtnum,
b.nombre desc_producto,
a.stoloc,
sum(a.untqty) cantidad,
a.invsts,
i.lngdsc desc_invsts,
cj.untqty undxcj,
pl.untqty undxpl,
a.orgcod,
o.lngdsc,
a.prtstyle,
b.dept_cod,
uc.werks
FROM [eWms].[dbo].[saldos_inventario] a INNER JOIN
eWms.dbo.productos b ON
a.prtnum=b.prtnum
AND a.prt_client_id=b.prt_client_id LEFT OUTER JOIN
eWms.dbo.productos_medidas cj ON
a.wh_id=cj.wh_id
AND a.prt_client_id=cj.prt_client_id
AND a.prtnum=cj.prtnum
AND cj.cas_flg=1 LEFT OUTER JOIN
eWms.dbo.productos_medidas pl ON
a.wh_id=pl.wh_id
AND a.prt_client_id=pl.prt_client_id
AND a.prtnum=pl.prtnum
AND pl.pal_flg=1 INNER JOIN
eWms.dbo.bodegas wh ON
a.wh_id=wh.wh_id LEFT OUTER JOIN
tactic_config.dbo.dscmst i ON
a.invsts=i.colval
AND i.colnam='invsts'
AND i.locale_id='ES-ES' LEFT OUTER JOIN
tactic_config.dbo.dscmst o ON
a.orgcod=o.colval
AND o.colnam='orgcod'
AND o.locale_id='ES-ES' LEFT OUTER JOIN
[57DBWMS05].[ttcwmsprdnew].[dbo].[uc_inv_mov_status_map] uc ON
a.wh_id=uc.wh_id
AND a.prt_client_id=uc.client_id
AND a.invsts=uc.fr_invsts_wms
where a.prt_client_id='DIAGEO'
and a.fecha_corte between '09-16-2020' and '10-15-2020'
group by a.fecha_corte,
a.wh_id,
wh.nombre,
wh.ciudad_nombre,
a.prt_client_id,
a.prtnum,
b.nombre,
a.stoloc,
a.invsts,
i.lngdsc,
cj.untqty,
pl.untqty,
a.orgcod,
o.lngdsc,
a.prtstyle,
b.dept_cod,
uc.werks

-----------------------------------------------------------------------------------
---------------------------------
/*Ordenes de trabajo DDA Consulta Ordenes de Trabajo X Almacen JDA*/

CLiente: Diageo
bodega: Todas

-----------------------------------------------------------------------------------
---------------------------------
https://tacticlogistics.sharepoint.com/:f:/g/ErD45hytyrxLiIG6Ak5y0cMB55JZmuQJJkC8dR
zeVFuh2Q?e=whxf5L
Password: Diageo.2019
-----------------------------------------------------------------------------------
---------------------------------

You might also like