|
|
(3 intermediate revisions by the same user not shown) |
Line 7: |
Line 7: |
| ===Lista=== | | ===Lista=== |
| | | |
− | [[file:Centralizator.png|500px]] | + | [[file:Lista 4 comenzi clienti pe gestiune livrare si agenti.png|500px]] |
| | | |
| ===Filtre=== | | ===Filtre=== |
Line 24: |
Line 24: |
| | | |
| ===Coloane=== | | ===Coloane=== |
− | ;Clientcomanda | + | ;ClientId |
− | ;Client livrare | + | ;Grupaj |
− | ;CodClient | + | ;Agent |
− | ;Data comanda | + | ;GestiuneLivrare |
− | ;DataComanda | + | ;CodProdus |
− | ;Numar comanda
| |
− | ;NumarComanda
| |
− | ;Stare
| |
− | ;Observatii
| |
− | ;Explicatii
| |
| ;Produs | | ;Produs |
− | ;CodProdus
| |
| ;Cantitate | | ;Cantitate |
− | ;Pret | + | ;ClientComanda |
− | ;PretRedus | + | ;CodClient |
− | ;Valuta
| |
− | ;Cantitate facturata
| |
− | ;Cantitate nefacturata
| |
− | ;Numar card
| |
− | ;Numar auto
| |
− | ;Client card
| |
− | ;Valoare comandata
| |
− | ;ValoareComandata
| |
− | ;Valoare facturata
| |
− | ;Serie
| |
− | ;NelivratFlag
| |
− | ;NevalidatFlag
| |
− | ;Numar comenzi
| |
− | ;Persoana introducere
| |
− | ;Persoana validare
| |
− | ;Luna introducere
| |
− | ;An introducere
| |
− | ;An
| |
− | ;Luna
| |
− | , gprod.DenumireGrupaV as [Grupa produs]
| |
− | , sp.CursValutar as [Curs valutar]
| |
− | , sp.CursValutarLista as [Curs Valutar Lista]
| |
− | , sp.Cant * isnull(sp.PretValutaRedus, sp.PretValuta)*ISNULL(sp.CursValutar,dbo.Getcurs(sp.ValutaId,isnull(sc.SorderDate,GETDATE()))) as [Valoare pozitie]
| |
− | , sc.DataLivrare as [Data livrare comanda]
| |
− | , CONVERT(VARCHAR(10),sc.DataLivrare,103) as DataLivrareComanda
| |
− | , sp.DataLivrare as [Data livrare produs]
| |
− |
| |
− |
| |
− | , CASE WHEN sp.PretLista = sp.PretValuta
| |
− | OR sp.PretLista = 0
| |
− | THEN '0'
| |
− | ELSE inv.Numarzecimale(2 , ROUND(( ( sp.PretValuta*sp.CursValutar - sp.PretLista*sp.CursValutarLista ) / (sp.PretLista*sp.CursValutarLista) ) * 100 , 2))
| |
− | END AS Adaos
| |
− | , CASE WHEN ISNULL(@Setare417,0)=0
| |
− | THEN
| |
− | CASE WHEN sp.PretValuta=sp.PretValutaRedus
| |
− | or sp.PretValuta=0
| |
− | THEN '0'
| |
− | ELSE
| |
− | inv.NumarZecimale(2,round(((sp.Pretvaluta-sp.PRetVAlutaREdus)/sp.PretVAluta)*100,0))
| |
− | END
| |
− | ELSE
| |
− | CASE WHEN sp.PretValuta = sp.PretValutaRedus
| |
− | OR sp.PretValuta= 0
| |
− | THEN '0'
| |
− | ELSE inv.NumarZecimale(2,round((((sp.PretValuta-sp.PretValutaRedus)*sp.CursValutar)/(sp.PretLista*sp.CursValutarLista))*100.00,0 ))
| |
− | END
| |
− | END AS [Reducere]
| |
− | /*,CASE WHEN sp.IntrPozId IS NOT NULL
| |
− | THEN sp.Cant
| |
− | ELSE 0 END As [Cantitate rezervata]*/
| |
− | , sp.CantRezervata / ISNULL(NULLIF(pum.Factor, 0),1) as [Cantitate rezervata]
| |
− |
| |
− | , sc.SOClientNumber as [Numar comanda Extern]
| |
− | , sp.attr1
| |
− | , sc.Tipdoc AS [Tip document]
| |
− | , case when ISNULL(LPC.IncludeTva,0) = 0 and COALESCE(sc.ValoareComandaPoz,sc.ValoareComanda,0) * sc.CursValutar < ISNULL(lpc.ValMinCmd,@Setare470) * dbo.GetCursReferinta(ISNULL(ValMinCmdValuta,pf.ValutaDefaultId),sc.SOrderDate,sc.ValutaId)
| |
− | then ISNULL(lpc.ValMinCmd,@Setare470) * dbo.GetCursReferinta(ISNULL(lpc.ValMinCmdValuta,pf.ValutaDefaultId),sc.SOrderDate,sc.ValutaId)
| |
− | when isnull(LPC.IncludeTva,0) = 1 and COALESCE(sc.ValoareComandaCuTvaPoz,sc.ValoareComandaCuTva,0) * sc.CursValutar < ISNULL(lpc.ValMinCmd,@Setare470) * dbo.GetCursReferinta(ISNULL(ValMinCmdValuta,pf.ValutaDefaultId),sc.SOrderDate,sc.ValutaId)
| |
− | then ISNULL(lpc.ValMinCmd,@Setare470) * dbo.GetCursReferinta(ISNULL(lpc.ValMinCmdValuta,pf.ValutaDefaultId),sc.SOrderDate,sc.ValutaId)
| |
− | else 0
| |
− | end as [Valoare mai mica decat valoarea minima]
| |
− | , tpp.PortofoliuProdus as [Portofoliu produs]
| |
− | , ag.DenumireUnitate as Agent
| |
− | , sc.DataValidare as [Data validare]
| |
− | , sc.DataIntroducere as [Data introducere]
| |
− | , sc.DataDevalidare as [Data devalidare]
| |
− | , datediff(d,sc.datavalidare,sc.DataIntroducere) as [Diferenta data validare - data introducere]
| |
− | , u.DenumirePartener
| |
− | , coalesce(sc.adresalivrare, unn.adresa, u.Adresa) as [Adresa livrare]
| |
− | , mc.DenumireModColaborare as [Mod colaborare]
| |
− | , tc.DenumireContact as [Persoana contact]
| |
− | , tc.Functia as [Functia contact]
| |
− | , tc.Adresa as [Adresa contact]
| |
− | , tc.Telefon1 as [Telefon1 contact]
| |
− | , tc.Telefon2 as [Telefon2 contact]
| |
− | , tc.Mobil as [Mobil contact]
| |
− | , tc.Fax as [Fax contact]
| |
− | , tc.Email1 as [Email1 contact]
| |
− | , tc.Email2 as [Email2 contact]
| |
− | , mp.DenumireModPlata AS[Mod plata]
| |
− | , ttr.DenumireTraseu AS [Traseu]
| |
− | , case when pr.TipItem = 'P' then 'Produs' else 'Serviciu' end as [Tip produs]
| |
− | , furn.DenumireUnitate as [Furnizor preferat]
| |
− | , sp.CantCmdFurn / ISNULL(NULLIF(pum.Factor, 0),1) as [Cantitate comandata la furnizor]
| |
− | , sp.DenumireProdusAfisare as [Denumire produs afisare]
| |
− | , sp.CodProdusAfisare as [Cod produs afisare]
| |
− | , sp.CodBareAfisare as [Cod bare afisare]
| |
− | , CONVERT(varchar(5),sc.DataLivrare,108) as [Ora livrare]
| |
− | , sc.NrPozitii as [Nr pozitii]
| |
− | , sc.Explicatii as [Observatii interne]
| |
− | , sp.CantLansata / ISNULL(NULLIF(pum.Factor, 0),1) as [Cantitate lansata in productie]
| |
− | , sc.DenumireTipClient as [Tip client]
| |
− | , pro.NrProiect AS [Nr. proiect]
| |
− | , pro.DenumireProiect AS [Proiect]
| |
− | , prp.DenumireProdus AS [Produs parinte]
| |
− | , um.UM as UM
| |
− | , ctr.NumarContract AS [Nr contract antet]
| |
− | , sp.PretLista as [Pret lista]
| |
− | , sp.PretLista*sp.Cant as [Valoare lista]
| |
− | , sp.ValutaListaId as [Valuta lista]
| |
− | , sp.Adaos as [Adaos% pozitie]
| |
− | , (sp.Cant*sp.PretLista)-(sp.Cant*sp.PretValuta) as [Valoare Adaos pozitie]
| |
− | , isnull(sc.TermenPlata,tp.Denumire) as [Termen plata]
| |
− | , sc.DataAnulare as [Data anulare]
| |
− | , sp.Observatii as [Observatii pozitie]
| |
− | , cliv.DenumireContact as [Persoana contact livrare]
| |
− | , cliv.Functia as [Functia contact livrare]
| |
− | , cliv.Adresa as [Adresa contact livrare]
| |
− | , cliv.Telefon1 as [Telefon1 contact livrare]
| |
− | , cliv.Telefon2 as [Telefon2 contact livrare]
| |
− | , cliv.Mobil as [Mobil contact livrare]
| |
− | , cliv.Fax as [Fax contact livrare]
| |
− | , cliv.Email1 as [Email1 contact livrare]
| |
− | , cliv.Email2 as [Email2 contact livrare]
| |
− | , sc.Transportator as [Transportator text]
| |
− | , sc.RulajAsset as [Rulaj asset]
| |
− | --modificare
| |
− | , sc.MotivAnulare as [Motiv anulare]
| |
− | , cli.Cli_Attr1 AS [Atribut1 Client]
| |
− | , frm.CodFirma AS [Cod firma]
| |
− | , tl.TipLivrare AS [Tip livrare]
| |
− | , COALESCE(sp.AdresaLivrare,clp.adresa, sc.adresalivrare, unn.adresa, u.Adresa) as [Adresa livrare pozitie]
| |
− | , CONVERT(varchar(5),sc.DataIntroducere,108) AS [Ora introducere]
| |
− | , CONVERT(varchar(5),sc.DataValidare,108) AS [Ora validare]
| |
− |
| |
− | , pl.DenumireUnitate AS [Punct de lucru]
| |
− | , d.Txt AS Document
| |
− | , ierarh.Nivel as [Ierarhie produs]
| |
− | , isnull(sp.valoareamanuntredus, sp.cant*isnull(sp.pretamanuntredus,sp.pretamanunt)) AS [Valoare amanunt]
| |
− | -- , sf.Stoc / ISNULL(NULLIF(pum.Factor, 0),1) as [Stoc fara custodii]
| |
− | , u.DenumireJudet as [Judet client]
| |
− | , u.DenumireLocalitate as [Localitate client]
| |
− | , pr.GreutateNeta*sp.Cant as [Greutateneta]
| |
− | , gr.DenumireGestiune as [GestiuneLivrare]
| |
− | , fbr.denumirefabricant as [Fabricant]
| |
− | , pl.DenumireUnitate as [Producator]
| |
− | , CASE WHEN mp.factura = 1 then @Factura_txt
| |
− | WHEN mp.aviz = 1 then @Aviz_txt
| |
− | WHEN mp.chitanta = 1 then @Chitanta_txt
| |
− | END AS [Tip mod plata]
| |
− | , sp.Cantitate2 AS Cantitate2
| |
− | , sp.Observatii AS [Explicatii produs]
| |
− | , um2.UM AS Um2
| |
− | , pr.attr2 AS Attr2
| |
− | , pr.attr4 AS Attr4
| |
− | , pr.Volum AS Volum
| |
− | , pr.Densitate AS Densitate ----ss
| |
− | , CONVERT(VARCHAR(10), sc.DataEstimataFinalizare, 103) + ' ' + CONVERT(VARCHAR(5), DataEstimataFinalizare, 108) AS DataEstimataFinalizare
| |
− | , CONVERT(VARCHAR(10), sc.DataEstimataLivrare, 103) + ' ' + CONVERT(VARCHAR(5), DataEstimataLivrare, 108) AS DataEstimataLivrare
| |
− | , CONVERT(VARCHAR(10), sc.DataEstimataPlecare, 103) + ' ' + CONVERT(VARCHAR(5), DataEstimataPlecare, 108) AS DataEstimataPlecare
| |
− | , CONVERT(VARCHAR(10), sc.DataEstimataSosire, 103) + ' ' + CONVERT(VARCHAR(5), DataEstimataSosire, 108) AS DataEstimataSosire
| |
− | , CONVERT(VARCHAR(10), sc.DataProductie, 103) + ' ' + CONVERT(VARCHAR(5), DataProductie, 108) AS DataProductie
| |
− | , bl.BusinessLine [Linie de business document]
| |
− | , sc.DataExpirare
| |
− | , sc.DataLivrare2
| |
− | , sc.DataRezervareDeLa
| |
− | , sc.DataRezervarePanaLa
| |
− | , sc.DataEstimataFinalizare2
| |
− | , right('0'+cast(datepart(hh,sc.DataIntroducere) as varchar(2)),2) + '-' + right('0'+cast(datepart(hh,sc.DataIntroducere)as varchar(2))+1,2) [Interval orar]
| |
− | , zon.Zona
| |
− | , case when isnull(sc.GenerareFactura,0) =1 then 'X' else 'O' end as GenerareFactura
| |
− | , case when isnull(sc.GenerareBonFiscal,0) =1 then 'X' else 'O' end as GenerareBonFiscal
| |
− | , case when isnull(sc.GenerareBonConsum,0) =1 then 'X' else 'O' end as GenerareBonConsum
| |
− | -- , s.Stoc / ISNULL(NULLIF(pum.Factor, 0),1) as Stoc
| |
− | , sp.sorderpozid
| |
− | , sp.sordercapid
| |
− | , sc.sofer
| |
− | ;Comunicat
| |
− | ;Asteptare bucatarie
| |
− | ;Productie
| |
− | ;Asteptare livrare
| |
− | ;Timp transport de la plecare
| |
− | ;Total
| |
− | ;IG5
| |
− | ;IG10
| |
− | ;IG15
| |
− | ;IT5
| |
− | ;IT10
| |
− | ;IT15
| |
− | ;Grupaj
| |
| ;CodAgent | | ;CodAgent |
| ;DataStart | | ;DataStart |
− | ;DataEnd | + | ;DataEnd |
| ;GrupajF | | ;GrupajF |
− | ;produsid | + | ;DataEmitere |
− | ;Furnizor | + | ;NumeOperator |
− | ;Discount | + | ;ProdusId |
− | ;CodFiscalClient
| |
− | ;AdresaClient
| |
− | ;CodGestiune
| |
− | ;Numeoperator
| |
− | ;Dataemitere
| |