127 lines
4.0 KiB
TypeScript
127 lines
4.0 KiB
TypeScript
// this query pulls all the inventory except the inv locations.
|
|
|
|
export const totalInvNoRn = `
|
|
select
|
|
x.idartikelVarianten as av,
|
|
x.ArtikelVariantenAlias as Alias
|
|
--x.Lfdnr as RunningNumber,
|
|
,round(sum(EinlagerungsMengeVPKSum),0) as Total_Pallets
|
|
,sum(EinlagerungsMengeSum) as Total_PalletQTY
|
|
,round(sum(VerfuegbareMengeVPKSum),0) as Avalible_Pallets
|
|
,sum(VerfuegbareMengeSum) as Avaliable_PalletQTY
|
|
,sum(case when c.Description LIKE '%COA%' then GesperrteMengeVPKSum else 0 end) as COA_Pallets
|
|
,sum(case when c.Description LIKE '%COA%' then GesperrteMengeSum else 0 end) as COA_QTY
|
|
,sum(case when c.Description NOT LIKE '%COA%' or x.IdMainDefect = -1 then GesperrteMengeVPKSum else 0 end) as Held_Pallets
|
|
,sum(case when c.Description NOT LIKE '%COA%' or x.IdMainDefect = -1 then GesperrteMengeSum else 0 end) as Held_QTY
|
|
,sum(case when x.WarenLagerLagerTyp = 8 then VerfuegbareMengeSum else 0 end) as Consigment
|
|
,IdProdPlanung as Lot
|
|
----,IdAdressen,
|
|
,x.AdressBez
|
|
,x.IdLagerAbteilung as 'location'
|
|
,x.LagerAbteilungKurzBez
|
|
,x.IdWarenlager as warehouseID
|
|
,x.WarenLagerKurzBez as warehouseName
|
|
--,*
|
|
from [AlplaPROD_test1].dbo.[V_LagerPositionenBarcodes] (nolock) x
|
|
|
|
left join
|
|
[AlplaPROD_test1].dbo.T_EtikettenGedruckt as l(nolock) on
|
|
x.Lfdnr = l.Lfdnr AND l.Lfdnr > 1
|
|
|
|
left join
|
|
|
|
(SELECT *
|
|
FROM [AlplaPROD_test1].[dbo].[T_BlockingDefects] where Active = 1) as c
|
|
on x.IdMainDefect = c.IdBlockingDefect
|
|
/*
|
|
The data below will be controlled by the user in excell by default everything will be passed over
|
|
IdAdressen = 3
|
|
*/
|
|
where /*IdArtikelTyp = 1 and */x.IdWarenlager not in (6, 1)
|
|
|
|
group by x.idartikelVarianten, ArtikelVariantenAlias, c.Description
|
|
--,IdAdressen
|
|
,x.AdressBez
|
|
,IdProdPlanung
|
|
,x.IdLagerAbteilung
|
|
,x.LagerAbteilungKurzBez
|
|
,x.IdWarenlager
|
|
,x.WarenLagerKurzBez
|
|
--, x.Lfdnr
|
|
order by x.IdArtikelVarianten
|
|
|
|
`;
|
|
|
|
export const totalInvRn = `
|
|
select x.idartikelVarianten as av,
|
|
ArtikelVariantenAlias as Alias,
|
|
x.Lfdnr as RunningNumber,
|
|
round(sum(EinlagerungsMengeVPKSum),0) as Total_Pallets,
|
|
sum(EinlagerungsMengeSum) as Total_PalletQTY,
|
|
round(sum(VerfuegbareMengeVPKSum),0) as Avalible_Pallets,
|
|
sum(VerfuegbareMengeSum) as Avaliable_PalletQTY,
|
|
sum(case when c.Description LIKE '%COA%' then GesperrteMengeVPKSum else 0 end) as COA_Pallets,
|
|
sum(case when c.Description LIKE '%COA%' then GesperrteMengeSum else 0 end) as COA_QTY,
|
|
sum(case when c.Description NOT LIKE '%COA%' or x.IdMainDefect = -1 then GesperrteMengeVPKSum else 0 end) as Held_Pallets,
|
|
sum(case when c.Description NOT LIKE '%COA%' or x.IdMainDefect = -1 then GesperrteMengeSum else 0 end) as Held_QTY
|
|
,IdProdPlanung as Lot
|
|
,IdAdressen,
|
|
x.AdressBez
|
|
,x.lagerabteilungkurzbez as location
|
|
,lot.machine
|
|
--,lot.planstart
|
|
--,lot.planend
|
|
,produktionsdatummin
|
|
,'728'
|
|
+ RIGHT(CAST(YEAR(produktionsdatummin) AS varchar(4)), 1)
|
|
+ CAST(DATEDIFF(DAY, DATEFROMPARTS(YEAR(produktionsdatummin), 1, 1), produktionsdatummin) + 1 AS varchar(3))
|
|
+ CAST(lot.machine AS varchar(10)) as batch
|
|
--,*
|
|
from [AlplaPROD_test1].dbo.[V_LagerPositionenBarcodes] (nolock) x
|
|
|
|
left join
|
|
[AlplaPROD_test1].dbo.T_EtikettenGedruckt as l(nolock) on
|
|
x.Lfdnr = l.Lfdnr AND l.Lfdnr > 1
|
|
|
|
left join
|
|
|
|
(SELECT *
|
|
FROM [AlplaPROD_test1].[dbo].[T_BlockingDefects] where Active = 1) as c
|
|
on x.IdMainDefect = c.IdBlockingDefect
|
|
|
|
/*
|
|
get lot and machine info
|
|
*/
|
|
left join
|
|
(select location as machine,
|
|
runningnumber as lot
|
|
,planstart
|
|
,planend
|
|
--,*
|
|
from [test1_AlplaPROD2.0_Read].[productionScheduling].[ProductionLot] (nolock) x
|
|
|
|
left join
|
|
[test1_AlplaPROD2.0_Read].[masterData].[Machine] (nolock) m on
|
|
m.id = x.machineid) as lot on
|
|
lot.lot = IdProdPlanung
|
|
/*
|
|
The data below will be controlled by the user in excell by default everything will be passed over
|
|
IdAdressen = 3
|
|
*/
|
|
where IdArtikelTyp = 1 and x.IdWarenlager not in (6, 1)
|
|
|
|
group by x.idartikelVarianten, ArtikelVariantenAlias, c.Description, IdAdressen,
|
|
x.AdressBez , x.Lfdnr,
|
|
IdProdPlanung -- this will be flagged as being removed when we do historical.
|
|
,x.lagerabteilungkurzbez
|
|
,lot.machine
|
|
--,lot.planstart
|
|
--,lot.planend
|
|
,produktionsdatummin
|
|
order by x.IdArtikelVarianten
|
|
`;
|
|
|
|
const totalInvValue = ``;
|
|
|
|
const totalInvValueRn = ``;
|