Difference between revisions of "Factura client - Model - Nota culegere depozit detalii"
Line 233: | Line 233: | ||
, MAX(tblCentreCost.CentruCost) AS CentruCostPoz | , MAX(tblCentreCost.CentruCost) AS CentruCostPoz | ||
-- , MAX(InfoUserFacturare.CI) AS DenCentruCost | -- , MAX(InfoUserFacturare.CI) AS DenCentruCost | ||
− | + | ||
, CASE WHEN ISNULL(@Setare425,0) = 1 THEN MAX(adr_client_unitate.AdresaPartenerPrefixata) | , CASE WHEN ISNULL(@Setare425,0) = 1 THEN MAX(adr_client_unitate.AdresaPartenerPrefixata) | ||
ELSE MAX(ISNULL(oc.ClientAdresa,adr_client_unitate.AdresaPartener)) | ELSE MAX(ISNULL(oc.ClientAdresa,adr_client_unitate.AdresaPartener)) | ||
END AS Client_Adresa | 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 | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− |
Revision as of 16:52, 30 August 2022
- Documente sistem
- Factura client
- Nota culegere depozit detalii
- Factura client
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
, 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