Difference between revisions of "Cumparari:Pivot:Comenzi"
Ema Seiciu (talk | contribs) |
|||
Line 7: | Line 7: | ||
[[file:Cumparari-Pivot-Comenzi.png|800px]] | [[file:Cumparari-Pivot-Comenzi.png|800px]] | ||
+ | |||
+ | |||
+ | ===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 |
Revision as of 16:34, 5 March 2024
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