Cumparari:Pivot:Comenzi
Coloane
- Numar comanda client
- Data comanda client
- Luna comanda furnizor
- An comanda furnizor
- Client
- Furnizor
- Numar comanda furnizor
- Numar confirmare
- Data confirmare
- Numar comanda de la furnizor
- Data comanda de la furnizor
- Data comanda furnizor
- Data incarcare
- Agent
- Cantitate comanda client
- Valoare comanda client
- Valoare vanzare - Valuta
- Cantitate comanda furnizor
- Cantitate receptionata furnizor
- Diferente cantitate flag
- Cantitate nereceptionata
- Valoare nereceptionata
- Valoarea achizitie nereceptionata - Valuta curs
- Produs
- UM
- Cod produs furnizor
, COALESCE(ocp.DenProdusFurnizor,fp.DenumireProdusFurnizor,p.DenProdusFabricant) AS [Denumire produs furnizor]
, sp.PretValuta AS [Pret vanzare]
, sp.PretValutaRedus AS [Pret vanzare redus]
, sp.ValutaID AS [Valuta vanzare]
, sp.PretLista AS [Pret lista]
, pp.PretValuta AS [Pret comanda furnizor]
, case when ip.PretIntrareValuta<>pp.PretValuta then 1 else 0 end as [Diferente pret flag]
, pp.PretValuta * pp.Cant AS [Valoare comanda furnizor]
, pp.PretValuta*pp.Cant --(pp.PretValuta + ISNULL(pp.CostTransport,0)) * pp.Cant * CASE WHEN ISNULL(pc.ValutaId,@valutaDefaultId) <> ISNULL(@ValutaID,@valutaDefaultid) THEN ISNULL(dbo.GetCurs(pc.ValutaId,getdate()),1) / ISNULL(NULLIF(dbo.GetCurs(@ValutaId,getdate()),0),1) ELSE 1 END + (ISNULL(pp.CostTransport,0) * CASE WHEN ISNULL(pp.ValutaTransportId,@valutaDefaultId) <> ISNULL(pp.ValutaTransportId,@valutaDefaultid) THEN ISNULL(dbo.GetCurs(pp.ValutaTransportId,getdate()),1) / ISNULL(NULLIF(dbo.GetCurs(@ValutaId,getdate()),0),1) ELSE 1 END) AS [Valoare achizitie - Valuta curs]--DA , pp.PretValuta * pp.Cant -- (pp.PretValuta + ISNULL(pp.CostTransport,0)) * pp.Cant * CASE WHEN ISNULL(pc.ValutaId,@valutaDefaultId) <> ISNULL(@ValutaID,@valutaDefaultid) THEN ISNULL(dbo.GetCurs(pc.ValutaId,pc.PorderDate),1) / ISNULL(NULLIF(dbo.GetCurs(@ValutaId,pc.PorderDate),0),1) ELSE 1 END + (ISNULL(pp.CostTransport,0) * CASE WHEN ISNULL(pp.ValutaTransportId,@valutaDefaultId) <> ISNULL(@ValutaID,@valutaDefaultid) THEN ISNULL(dbo.GetCurs(pp.ValutaTransportId,pc.PorderDate),1) / ISNULL(NULLIF(dbo.GetCurs(@ValutaId,pc.PorderDate),0),1) ELSE 1 END) AS [Valoare achizitie - Valuta] , pp.Cant * isnull(sp.Pretvalutaredus,sp.pretvaluta) * CASE WHEN ISNULL(sp.ValutaId,@ValutaDefaultID) <> ISNULL(@ValutaId,@ValutaDefaultId) THEN ISNULL(sp.CursValutar,1) / ISNULL(NULLIF(dbo.GetCurs(@ValutaId,pc.PorderDate),0),1) ELSE 1 END - (pp.PretValuta + ISNULL(pp.CostTransport,0)) * pp.Cant * CASE WHEN ISNULL(pc.ValutaId,@valutaDefaultId) <> ISNULL(@ValutaID,@valutaDefaultid) THEN ISNULL(dbo.GetCurs(pc.ValutaId,pc.PorderDate),1) / ISNULL(NULLIF(dbo.GetCurs(@ValutaId,pc.PorderDate),0),1) ELSE 1 END AS [Adaos]
, pc.ValutaId AS [Valuta comanda furnizor]
, ISNULL(cll.Adresa,) + ISNULL('LC:' + sp.AdresaLivrare,) + ISNULL('LF:' + pp.AdresaLivrare,) AS [Adresa livrare]
, isnull(pp.DataLivrare,pc.DataLivrare) AS [Termen livrare]
, ISNULL(sc.Observatii + ' ',) + ISNULL(sp.Observatii,) AS [Observatii comanda client]
, ic.DataDVI AS [Data DVI]
, ic.NumarDVI AS [Numar DVI]
, ic.NumarFactIntr AS [Numar factura]
, ic.DataFactIntr AS [Data factura]
, ISNULL(ic.[DataReceptie], ic.DataIntr) AS [Data receptie]
, ip.CantIntrare AS [Cantitate intrata]
, ip.SerieIntrare AS [Serie intrare]
, ip.DataExpirare AS [Data expirare]
, ip.LotIntrare AS [Lot intrare]
, ip.DataFabricare AS [Data fabricare]
, ip.PretIntrareValuta AS [Pret intrare valuta]
, ip.ValutaId AS [Valuta intrare]
, ip.TaxeVamale AS [Taxe vamale]
, ip.ComisionVamal AS [Comision vamal]
, scf.Stare AS [Stare comanda furnizor]
, scc.Stare AS [Stare comanda client]
, CASE WHEN pp.POrderPozId IS NULL
THEN 1
ELSE 0
END AS [De comandat]
, fr.DenumireUnitate AS [Furnizor comanda]
, ISNULL(pp.Observatii+' ',) + ISNULL(pc.Observatii , ) AS [Observatii comanda furnizor]
, pp.CostAsigurare AS [Cost asigurare]
, pp.ValutaAsigurareId AS [Valuta cost asigurare]
, pp.CostTransport AS [Cost transport]
, pp.ValutaTransportId AS [Valuta cost transport]
, pp.CostImpachetare AS [Cost impachetare]
, pp.ValutaImpachetareId AS [Valuta cost impachetare]
, pp.NrTracking AS [Numar tracking]
, trsp.DenumireUnitate AS Transportator
, ocp.DiscountOff
, ocp.DiscountOff2
, ocp.PretLista AS PretLista
, sp.CantLivrata AS [Cantitate livrata]
, p.Cod AS [Cod Produs]
, isnull(pp.PretValuta,0)-isnull(ip.PretIntrareValutaRedus,0) as [Diferenta pret comanda factura]
, pro1.DenumireProiect as [Proiect client] , pro2.DenumireProiect as [Proiect furnizor] , CLF.DenumireUnitate AS [Client comanda furnizor] , cpr.DenumireCategorieProdus AS [Categorie Produs] , spr.DenumireSubcategorieProdus AS [Subcategorie Produs] , tpr.DenumireTipProdus AS [Tip Produs] , gpr.DenumireGrupaV AS [Grupa Produs] , t.DenumireTara AS [Tara de origine]
, pc.ObservatiiInterne AS [Observatii interne]
, us.DenumireUnitate as [Persoana introducere] , g.DenumireGestiune AS [Gestiune] , tp.Denumire AS [Termen plata] , tp.NumarZile AS [Numar zile termen plata] , DATEADD(dd,tp.NumarZile,isnull(pp.DataLivrare,pc.DataLivrare)) AS [Data Scadenta] , @ValutaId AS [Valuta filtru] , ISNULL(cc.CentruCost + ' ',) + ISNULL(cc.DenCentruCost,) as [Centru cost] , pp.ObservatiiInterne AS [Observatii interne comanda furnizor]
, pro2.NrProiect AS [Numar proiect furnizor]
, pp.Cant *(cast(isnull(p.bucatium,1) as decimal(22,8))) AS [Cantitate bucati um] , umb.um as [Um buc] , p.bucatium as [Bucati um] , ip.CantIntrare *(cast(isnull(p.bucatium,1) as decimal(22,8))) AS [Cantitate buc UM intrare ] , (pp.cant-isnull(pp.CantReceptionata,0)) *(cast(isnull(p.bucatium,1) as decimal(22,8))) as [Cantitate buc UM nereceptionata] , Isnull(pp.DataLivrare,pc.DataLivrare) as [Data Livrare] , ISNULL(cle.CodConditieLivrareExterna + ' - ', ) + cle.DenumireConditieLivrareExterna as [Conditii livrare externa] , ISNULL(mt.CodModalitateTransport + ' - ', ) + mt.DenumireModalitateTransport as [Modalitate transport] , g2.DenumireGestiune AS [Gestiune intrare] ,p.Cod4 ,p.Cod5 ,sc.Observatii2 ,pp.Attr1 ,pp.GreutateBruta as [Greutate bruta] ,pp.GreutateNeta as [Greutate neta] ,taraorig.DenumireTara as [Tara origine comanda]
- Incadrare vamala
