Factura client - Model - Nota culegere depozit detalii

From docs
Revision as of 16:49, 30 August 2022 by Ana Marinescu (talk | contribs)
Jump to: navigation, search


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
   ,	MAX(uc.DenumireLocalitate) AS DenumireLocalitate
   ,	MAX(dbo.GetAdresa(NULL, judC.DenumireJudet, locC.DenumireLocalitate, unC.Strada, unC.NumarStrada, unC.Bloc, unC.Scara, unC.Etaj, unC.Apartament)) AS Adresa
   ,	MAX(uC.AdresaFaraJudet) AS AdresaFaraJudet
   ,	MAX(partC.DenumirePartener) AS DenumirePartener	
   ,	MAX(partC.NrRegComertului) AS NrRegComertului

--, partC.CodFiscal , MAX(ISNULL(partC.AtributFiscal,) + partC.CodFiscal) as CodFiscal 

   ,	MAX(partC.CodFiscal) AS CodFiscal1
   ,	MAX(partC.Strada) AS Strada
   ,	MAX(partC.NumarStrada) AS NumarStrada
   ,	MAX(partC.Bloc) AS Bloc
   ,	MAX(partC.Scara) AS 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

---- PRODUSE , MAX(isnull(p.produsid,det.ProdusId)) as produsid , MAX(det.Ordine) AS Ordine --, MAX(det.NumarPozitie) AS NumarPozitie , 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

--****** Client partener 

       ,   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
       ,   CASE WHEN ISNULL(@setare425,0) = 1
                THEN CASE WHEN MAX(adr_client_unitate.AdresaUnitatePrefixata) =MAX(adr_client_unitate.AdresaPartenerPrefixata)
                          THEN 
                          ELSE 'Adresa livrare: ' + MAX(adr_client_unitate.AdresaUnitatePrefixata) + ''
                     END
                ELSE CASE WHEN MAX(adr_client_unitate.AdresaUnitate) = MAX(adr_client_unitate.AdresaPartener) THEN 
                          ELSE 'Adresa livrare: ' + MAX(ISNULL(oc.AdresaLivrare,
                                                                   adr_client_unitate.AdresaUnitate)) + ''
                     END
           END AS Client_AdresaLivrare
       ,   MAX(ISNULL(partener.Capitalsocial,0)) AS Client_Capitalsocial

--FURNIZORUL 

       ,   CASE WHEN ISNULL(@Setare425,0) = 1 THEN MAX(adr_furnizor_unitate.AdresaPartenerPrefixata)
                ELSE MAX(ISNULL(oc.FurnizorAdresa,adr_furnizor_unitate.AdresaPartener))
           END AS Furnizor_Adresa
       ,   CASE WHEN @Setare370 = '0'
                THEN CASE WHEN ISNULL(@Setare425,0) = 1
                          THEN CASE WHEN MAX(adr_furnizor_unitate.AdresaPartenerPrefixata) = MAX(adr_furnizor_unitate.AdresaUnitatePrefixata)
                                    THEN 
                                    ELSE 'Punct lucru: ' + MAX(adr_furnizor_unitate.AdresaUnitatePrefixata) + ''
                               END
                          ELSE CASE WHEN MAX(adr_furnizor_unitate.AdresaPartenerPrefixata)= MAX(adr_furnizor_unitate.AdresaUnitatePrefixata)
                                    THEN 
                                    ELSE 'Punct lucru: ' + MAX(adr_furnizor_unitate.AdresaUnitatePrefixata) + ''
                               END
                     END
                ELSE @Setare370
           END AS Furnizor_AdresaLivrare

, MAX(det.PretCost) AS PretCost , SUM(ISNULL(det.CantIesire * ( det.PretVanzareRedus - det.PretVanzare ),0)) AS DiscountPozitie 

          ,   MAX(oc.Discount) AS Discount
       ,   SUM(ISNULL(det.CantIesire * ( det.PretVanzareRedus - det.PretVanzare ) * det.Tva / 100.0,0)) AS TvaDiscountPozitie

, max(inv.NumarZecimale(@setare247,round(det.PretVanzare,cast(@setare247 AS INT)))) AS PretVanzare 

       ,   MAX(inv.NumarZecimale(@setare247,
                             ROUND(det.PretVanzareRedus * CASE WHEN oc.DataValidare IS NULL
                                                                  THEN ISNULL(1 - oc.Discount / 100,1)
                                                                  ELSE 1
                                                             END,cast(@setare247 AS INT)))) AS PretVanzareRedus

, MAX(ISNULL(det.PretAmanuntRedus,det.PretAmanunt)) as PretVanzareCuTVA 

       ,   SUM(ROUND(det.CantIesire * det.PretVanzare,CAST(@setare351 AS INT))) AS ValoarePozitie
       ,   SUM(ROUND(det.CantIesire * det.PretVanzare * det.Tva / 100.00,@setare351)) AS ValoareTvaPozitie

       ,   SUM(ROUND(det.CantIesire * det.PretVanzare + det.CantIesire * det.PretVanzare
                                       * det.Tva / 100.00,@setare351)) AS TotalPlataPePozitie

,SUM(ROUND((det.CantIesire) * (ISNULL(det.PretVanzareRedus, det.PretVanzare)),cast(@setare247 AS INT))) as ValoarePozitieReducere ,SUM(ROUND(det.CantIesire * ISNULL(det.PretVanzareRedus, det.PretVanzare) * (det.Tva)/100,cast(@setare247 AS INT))) as ValoareTVAPozitieReducere --,CASE WHEN ISNULL(det.PretVanzare ,0)=0 THEN 0 ELSE MAX(1 - (det.PretVanzareRedus / det.PretVanzare) * 100) END as DiscountProcent ,MAX(ROUND(det.PretVanzareRedus,cast(@setare247 AS INT))) AS PretVanzareReducere ,MAX(ROUND(det.PretVanzare,cast(@setare247 AS INT))) AS PretVanzareBrut

Retrieved from "https://docs.plurivaerp.com/index.php?title=Factura_client_-_Model_-_Nota_culegere_depozit_detalii&oldid=41232"