Factura client - Model - Nota culegere depozit detalii

• Documente sistem
  • Factura client
    • Nota culegere depozit detalii

Campuri

IesCapId

DocId

ClientId

FirmaId

TipLivrareId

ModPlataId

NumarIes

NumarComanda

DataLivrare

DataLivrareComanda

OraLivrare

DataFactIes

DataIes

ObservatiiComanda

AdresaLivrare

TipLivrare

DenumireModPlata

ObservatiiTipDoc

TipDoc

DenumireTraseu

Stare

UserValidare

MobilCap

EmailContactComanda

TelContactComanda

ContactComanda

PersoanaContactCap

Mesaj

Locatie

CodLocatie

Proiect

Denumireproiect

Tipproiect

PartenerId

DenumireUnitate

Clientcomanda

DenumireJudet

DenumireLocalitate

Adresa

AdresaFaraJudet

DenumirePartener

NrRegComertului

CodFiscal

CodFiscal1

Strada

NumarStrada

Bloc

Scara

   ,	MAX(partC.Etaj) AS Etaj
   ,	MAX(partC.Apartament) AS Apartament    

, MAX(contBancarP.Cont) AS Cont

   ,	MAX(contBancarP.DenumireBanca) AS Banca

, MAX(@NumarContract) AS NumarContract

   ,	MAX(@DataContract) AS DataContract

, MAX(@CodClient) AS CodClient , MAX(contC.Fax) AS fax , MAX(contC.Mobil) AS Mobil , MAX(ISNULL(contC.Telefon1, contC.Telefon2)) AS telefon , MAX(contC.Email1) AS Email

   ,	MAX(ISNULL(contC.NumeContact, ) + ISNULL(' ' + contC.PrenumeContact, )) AS PersoanaContact

-- -- FURNIZOR , MAX(uf.DenumireUnitate) AS Furnizor , MAX(uf.DenumireUnitate) AS PunctDeLucru , MAX(adreseF.AdresaPartener) AS AdresaFirma , MAX(f.Antet) AS Antet

   ,	MAX(f.Footer) AS Footer

, MAX(isnull(p.produsid,det.ProdusId)) as produsid , MAX(det.Ordine) AS , MAX(p.UmId) AS UmId , MAX(p.TipItem) AS TipItem , MAX(CASE @Setare391 WHEN 0 THEN isnull(p.DenumireProdus,pcp.DenumireProdus)

           WHEN 1 THEN COALESCE(NULLIF(p.cod, ), pcp.Cod, ) + ' ' +  isnull(p.DenumireProdus,pcp.DenumireProdus)
           WHEN 2 THEN  isnull(p.DenumireProdus,pcp.DenumireProdus) + ' ' + COALESCE(NULLIF(p.Cod, ),pcp.Cod, )
       END) AS DenumireProdus

, MAX(p.Cod) as Cod , MAX(isnull(p.DenumireProdus,det.DenumireProdus)) AS DenumireProdusFaraCod , MAX(p.attr1) AS attr1

   ,   MAX(fab.DenumireFabricant) as Fabricant

, MAX(um.UM) AS UmPoz , MAX(ump.UM) AS UM , MAX(umbax.UM) AS UMBax , MAX(p.BucPalet) AS BucPePalet , MAX(p.bucatiBAX) AS BucPeBax , MAX(ISNULL(um.UM, ump.UM) )AS UM1 , MAX(COALESCE(um2.UM, um.UM, ump.UM)) AS UMVanzare

, MAX(ISNULL(st.Stoc, 0)) AS Stoc , inv.numarzecimale(@Setare350, SUM(det.CantIesire)) AS Cant , inv.numarzecimale(@Setare350, SUM(det.CantIesire)) AS Cantitate

, MAX(ISNULL(um.UM,ump.UM)) AS UMAlternativ , SUM(det.CantIesire/ISNULL(NULLIF(pum.Factor, 0), 1)) CantFactor , inv.NumarZecimale(@setare350, SUM(det.CantIesire/ ISNULL(pum.Factor, 1))) AS CantAlternativ

, MAX(ISNULL(det.LotIntrare,)) AS LotIntrare , ISNULL(CONVERT(varchar(10),MAX(det.DataExpirare),103),) AS DataExpirare , MAX(ISNULL(det.SerieIntrare,)) AS SerieIntrare , MAX(ISNULL(gr.DenumireGestiune,)) AS GestiuneLivrare , MAX(ISNULL(gr.CodGestiune,)) AS CodGestiune , MAX(det.TermenLivrare) AS TermenLivrarePoz , CONVERT(VARCHAR(10),MAX(det.DataLivrare),103) AS DataLivrarePoz , MAX(det.ObservatiiInterne) AS ObservatiiInternePoz --, MAX(det.Observatii) AS ObservatiiPoz , SUM(det.CantIesire * p.GreutateNeta) AS GreutateNETA , SUM(det.CantIesire * p.Greutate) AS GreutateBRUTA

, MAX(oc.ValoareAmanunt) AS ValoarePozitii

, SUM(s2.Cant) AS CantitateMeniu , MAX(ISNULL(s2.DenumireProdusAfisare,p2.DenumireProdus)) AS Meniu , MAX(ISNULL(s2.PretAmanuntRedus,s2.PretAmanunt)) AS PretamanuntMeniu

, SUM(isnull(det.Cantitate2, det.CantIesire)) as Cantitate2 , MAX(p.GreutateNeta) AS GrNetProd , MAX(ua.DenumireUnitate) AS Agent , MAX(ISNULL(ctf.Telefon1,ctf.Telefon2)) AS TelefonFurnizor , MAX(uc.DenumireTara) AS DenumireTara , row_number () over( order by CASE WHEN max(oc.TipOrdine) = 1 THEN CASE @Setare391 WHEN 0 THEN max(p.DenumireProdus) + ISNULL('
' + max(p.DenProdusFabricant) , ) WHEN 1 THEN max(ISNULL(p.Cod , )) + ' ' + max(p.DenumireProdus) + ISNULL('
' + max(p.DenProdusFabricant) , ) WHEN 2 THEN max(p.DenumireProdus) + ' ' + max(ISNULL(p.Cod , ))+ ISNULL('
' + max(p.DenProdusFabricant) , ) END END, CASE WHEN max(oc.TipOrdine) = 2 THEN max(det.SOrderPozId) WHEN max(oc.TipOrdine) = 3 THEN max(det.Ordine) ELSE max(det.Ordine) END) as denin

, CASE WHEN MAX(oc.TipOrdine) = 2 THEN MAX(det.SOrderPozId) WHEN MAX(oc.TipOrdine) = 3 THEN MAX(det.Ordine) ELSE MAX(det.Ordine) END as f , ISNULL(CONVERT(VARCHAR(10),MAX(det.DataLivrare),103),CONVERT(VARCHAR(10),MAX(oc.DataLivrare),103)) AS DataLivrareCapPoz

  -- , CASE WHEN MAX(ofc.OfertaClientId )IS NULL THEN 

--ELSE ISNULL(@L_Oferta + ': ', ) + ISNULL(MAX(ofc.NumarOferta) + ' - ', ) + ISNULL('-' + CONVERT(VARCHAR(10), MAX(ofc.DataOferta), 103), ) + '
' --END + ,CASE WHEN MAX(oc.ContractClientId) IS NULL THEN ELSE ISNULL(@L_Contract + ': ', ) + ISNULL(MAX(contr.NumarContract) + ' - ', ) + ISNULL(CONVERT(VARCHAR(10), MAX(contr.DataContract), 103), ) + '
' END + CASE WHEN (MAX(cap.IesCapId) IS NOT NULL AND MAX(cap.DocId) = 5) THEN ISNULL(@L_Factura + ': ', ) + COALESCE(MAX(cap.NumarIes), MAX(cap.NumarFactIes), MAX(cap.NrBonFiscal), ) + ISNULL('-' + CONVERT(VARCHAR(10), ISNULL(MAX(cap.DataIes),MAX(cap.DataFactIes)), 103), ) + '
' ELSE END + CASE WHEN (MAX(cap.IesCapId) IS NOT NULL AND MAX(cap.DocId) = 21) THEN ISNULL(@L_Bon + ': ', ) + COALESCE(MAX(cap.NrBonFiscal),MAX(cap.NumarIes), MAX(cap.NumarFactIes), ) + ISNULL('-' + CONVERT(VARCHAR(10), ISNULL(MAX(cap.DataIes),MAX(cap.DataFactIes)), 103), ) + '
' ELSE END + CASE WHEN (MAX(cap.IesCapId) IS NOT NULL AND MAX(cap.DocId) = 6) THEN ISNULL(@L_FacturaExt + ': ', ) + COALESCE(MAX(cap.NumarIes), MAX(cap.NumarFactIes), ) + ISNULL('-' + CONVERT(VARCHAR(10), ISNULL(MAX(cap.DataIes),MAX(cap.DataFactIes)), 103), ) + '
' ELSE END + CASE WHEN (MAX(cap.IesCapId) IS NOT NULL AND MAX(cap.DocId) = 8) THEN ISNULL(@L_Aviz + ': ', ) + COALESCE(MAX(cap.NumarIes), MAX(cap.NumarFactIes), ) + ISNULL('-' + CONVERT(VARCHAR(10), ISNULL(MAX(cap.DataIes),MAX(cap.DataFactIes)), 103), ) + '
' ELSE END + CASE WHEN (MAX(cap.IesCapId) IS NOT NULL AND MAX(cap.DocId)=13) THEN ISNULL(@L_BonConsum + ': ',) + ISNULL(MAX(cap.NumarIes),) + ISNULL('-' + CONVERT(VARCHAR(10),MAX(cap.DataIes),103), ) + '
' ELSE END + CASE WHEN (max(cap.IesCapId) IS NOT NULL AND max(cap.DocId) = 46) THEN ISNULL(@L_Proforma + ': ', ) + COALESCE(max(cap.NumarIes), max(cap.NumarFactIes), ) + ISNULL('-' + CONVERT(VARCHAR(10), ISNULL(MAX(cap.DataIes),MAX(cap.DataFactIes)), 103), ) + '
' ELSE END

AS Referinta , MAX(p.Greutate) AS Greutate , MAX(p.VolumBax) AS VolumBax

, DENSE_RANK() OVER (ORDER BY CASE @Setare391 WHEN 0 THEN ISNULL(NULLIF(MAX(det.DenumireProdusAfisare), ), MAX(p.DenumireProdus)) WHEN 1 THEN COALESCE(NULLIF(MAX(det.CodProdusAfisare), ), MAX(p.Cod), ) + ' ' + ISNULL(NULLIF(MAX(det.DenumireProdusAfisare), ), MAX(p.DenumireProdus)) WHEN 2 THEN ISNULL(NULLIF(MAX(det.DenumireProdusAfisare), ), MAX(p.DenumireProdus)) + ' ' + COALESCE(NULLIF(MAX(det.CodProdusAfisare), ), MAX(p.Cod), ) END ) AS DRankRow

, DENSE_RANK () over( order by CASE WHEN MAX(oc.TipOrdine) = 1 THEN CASE @Setare391 WHEN 0 THEN MAX(p.DenumireProdus) + ISNULL('
' + MAX(p.DenProdusFabricant) , ) WHEN 1 THEN ISNULL(MAX(p.Cod), ) + ' ' + MAX(p.DenumireProdus )+ ISNULL('
' + MAX(p.DenProdusFabricant) , ) WHEN 2 THEN MAX(p.DenumireProdus) + ' ' + ISNULL(MAX(p.Cod) , ) + ISNULL('
' + MAX(p.DenProdusFabricant) , ) END END, CASE WHEN MAX(oc.TipOrdine) = 2 THEN MAX(det.SOrderPozId) WHEN MAX(oc.TipOrdine) = 3 THEN MAX(det.Ordine) ELSE MAX(det.Ordine) END) as DRankOrder

, MAX(uan.Adresa) AS AdresaLivrare_Antet , MAX(contactliv.DenumireContact) AS Denumire_ContactLivrare , ISNULL(MAX(contactliv.Email1),MAX(contactliv.Email2)) AS Email_ContactLivrare , COALESCE(MAX(contactliv.Telefon1),MAX(contactliv.Telefon2),MAX(contactliv.Mobil)) AS Telefon_ContactLivrare

, MAX(p.Descriere) AS Descriere , CAST('*' +REPLACE(ISNULL(UPPER(MAX(doc.DocCode)), ), '_', )+ CAST(MAX(oc.IesCapId) AS NVARCHAR(MAX)) + '*' AS NVARCHAR(MAX)) AS IesCapIdCB , ISNULL(MAX(loc.Locator), MAX(lr.Locatie)) AS Locator , MAX(caporig.NumarIes) AS NumarFacturaOriginala , MAX(clorig.DenumireUnitate) AS ClientFacturaOriginala ,ISNULL(MAX(CAST(cp.ContineDetalii AS VARCHAR(10))),0) AS ContineDetalii , DENSE_RANK() OVER(ORDER BY ISNULL(MAX(cp.sorderpozid),MAX(det.sorderpozid)) ) as ordinePrincipale , CAST(DENSE_RANK() OVER(ORDER BY ISNULL(MAX(cp.sorderpozid),MAX(det.sorderpozid))) AS VARCHAR(MAX)) + case when ROW_NUMBER() OVER(PARTITION BY ISNULL(MAX(cp.sorderpozid),MAX(det.sorderpozid)) ORDER BY MAX(det.sorderpozid)) - 1 <>0 then +'.' + CAST(ROW_NUMBER() OVER(PARTITION BY ISNULL(MAX(cp.sorderpozid),MAX(det.sorderpozid)) ORDER BY MAX(det.sorderpozid)) - 1 AS VARCHAR(MAX)) else end AS ordineDetalii ,ISNULL(MAX(det.ParentIesPozId), MAX(cp.IesPozId)) as ParentiIesPozId ,case when ROW_NUMBER() OVER(PARTITION BY ISNULL(MAX(cp.iespozid),MAX(det.iespozid)) ORDER BY MAX(det.iespozid)) - 1 <>0 then (SUM(det.CANTiesire) * MAX(det.PretIntrareValuta)) end as SumaPretValuta

, MAX(CAST(ISNULL(oc.CuChitanta,0) AS INT)) AS CuChitanta , CONVERT(VARCHAR(50),MAX(oc.DataValidare),103) AS DataValidare , MAX(ISNULL(oc.ClientDenumire,UC.DenumirePartener)) AS Client

   ,  MAX(UC.DenumireUnitate1) AS Client_Unitate
       ,   MAX(UC.DenumirePartener) AS Client_Partener
       ,   MAX(ISNULL(oc.ClientCont,Partener.Cont)) AS Cont_Client
       ,   MAX(ISNULL(oc.ClientBanca,Partener.DenumireBanca)) AS Banca_Client

, MAX(dbo.ConturiPartener(UC.PartenerID,@SYS_LANGID,null)) AS Conturi_Client

         /*, MAX(CASE WHEN ISNULL(Partener.AtributFiscal, ) = 'R' THEN 'RO'
                    ELSE ISNULL(Partener.AtributFiscal, )
               END + CAST(Partener.CodFiscal AS VARCHAR(100))) AS CodFiscal_Client*/
       ,   MAX(ISNULL(oc.ClientCnpCui,CASE ISNULL(partener.AtributFiscal,)
                                                 WHEN 'R' THEN 'RO'
                                                 ELSE LTRIM(RTRIM(ISNULL(Partener.AtributFiscal,)))
                                               END + CAST(Partener.CodFiscal AS VARCHAR(100)))) AS CodFiscal_Client

, MAX(ISNULL(oc.UnitateCnpCui,CASE ISNULL(partener.AtributFiscal,)

                                                 WHEN 'R' THEN 'RO'
                                                 ELSE LTRIM(RTRIM(ISNULL(Partener.AtributFiscal,)))
                                               END + CAST(UC.CodFiscal AS VARCHAR(100)))) AS CodFiscal_Unitate
       ,   MAX(UC.AdresaFaraJudet) AS AdresaFaraJudet_client
       ,   MAX(Partener.DenumireLocalitate) AS DenumireLocalitate_Client
       ,   MAX(Partener.DenumireJudet) AS DenumireJudet_Client
       ,   MAX(COALESCE(oc.NrRegComPctLucru,oc.ClientNrRegCom,Partener.NrRegComertului)) AS NrRegComertului_Client
       ,	MAX(vwUnitati_Agent.DenumireUnitate) AS Agent

, MAX(COALESCE(contact_Agent.Mobil,contact_Agent.Telefon1,contact_Agent.Telefon2)) as TelefonAgent

       ,   MAX(tblCentreCost.CentruCost) AS CentruCostPoz
      -- ,   MAX(InfoUserFacturare.CI) AS DenCentruCost

       ,   CASE WHEN ISNULL(@Setare425,0) = 1 THEN MAX(adr_client_unitate.AdresaPartenerPrefixata)
                ELSE MAX(ISNULL(oc.ClientAdresa,adr_client_unitate.AdresaPartener))
           END AS Client_Adresa

Client_AdresaLivrare

Client_Capitalsocial

Furnizor_Adresa

Furnizor_AdresaLivrare

PretCost

DiscountPozitie

Discount

TvaDiscountPozitie

PretVanzare

PretVanzareRedus

PretVanzareCuTVA

ValoarePozitie

ValoareTvaPozitie

TotalPlataPePozitie

ValoarePozitieReducere

ValoareTVAPozitieReducere

PretVanzareReducere

PretVanzareBrut