Difference between revisions of "Factura client - Model - Nota culegere depozit detalii"
Line 89: | Line 89: | ||
;Cant | ;Cant | ||
;Cantitate | ;Cantitate | ||
− | + | ;UMAlternativ | |
− | + | ;CantFactor | |
− | + | ;CantAlternativ | |
− | + | ;LotIntrare | |
− | + | ;DataExpirare | |
− | + | ;SerieIntrare | |
− | + | ;GestiuneLivrare | |
− | + | ;CodGestiune | |
− | |||
− | |||
− | |||
, MAX(det.TermenLivrare) AS TermenLivrarePoz | , MAX(det.TermenLivrare) AS TermenLivrarePoz | ||
, CONVERT(VARCHAR(10),MAX(det.DataLivrare),103) AS DataLivrarePoz | , CONVERT(VARCHAR(10),MAX(det.DataLivrare),103) AS DataLivrarePoz | ||
Line 191: | Line 188: | ||
+ 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 | + 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 | ,ISNULL(MAX(det.ParentIesPozId), MAX(cp.IesPozId)) as ParentiIesPozId | ||
− | + | ; SumaPretValuta | |
− | + | ;CuChitanta | |
− | + | ;DataValidare | |
− | + | ;Client | |
− | + | ;Client_Unitate | |
− | |||
;Client_Partener | ;Client_Partener | ||
;Cont_Client | ;Cont_Client |
Revision as of 17:03, 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
- DenumireLocalitate
- Adresa
- AdresaFaraJudet
- DenumirePartener
- NrRegComertului
- CodFiscal
- CodFiscal1
- Strada
- NumarStrada
- Bloc
- Scara
- Etaj
- Apartament
- Cont
- Banca
- NumarContract
- DataContract
- CodClient
- fax
- Mobil
- telefon
- PersoanaContact
- Furnizor
- PunctDeLucru
- AdresaFirma
- Antet
- Footer
- produsid
- UmId
- TipItem
- DenumireProdus
- Cod
- DenumireProdusFaraCod
- attr1
- Fabricant
- UmPoz
- UM
- UMBax
- BucPePalet
- BucPeBax
- UM1
- UMVanzare
- Stoc
- Cant
- Cantitate
- UMAlternativ
- CantFactor
- CantAlternativ
- LotIntrare
- DataExpirare
- SerieIntrare
- GestiuneLivrare
- 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
- SumaPretValuta
- CuChitanta
- DataValidare
- Client
- Client_Unitate
- Client_Partener
- Cont_Client
- Banca_Client
- Conturi_Client
- CodFiscal_Client
- CodFiscal_Unitate
- AdresaFaraJudet_client
- DenumireLocalitate_Client
- DenumireJudet_Client
- NrRegComertului_Client
- Agent
- TelefonAgent
- CentruCostPoz
- Client_Adresa
- Client_AdresaLivrare
- Client_Capitalsocial
- Furnizor_Adresa
- Furnizor_AdresaLivrare
- PretCost
- DiscountPozitie
- Discount
- TvaDiscountPozitie
- PretVanzare
- PretVanzareRedus
- PretVanzareCuTVA
- ValoarePozitie
- ValoareTvaPozitie
- TotalPlataPePozitie
- ValoarePozitieReducere
- ValoareTVAPozitieReducere
- PretVanzareReducere
- PretVanzareBrut