Difference between revisions of "Factura client - Model - Nota culegere depozit detalii"

From docs
Jump to: navigation, search
 
(3 intermediate revisions by the same user not shown)
Line 68: Line 68:
 
;Furnizor
 
;Furnizor
 
;PunctDeLucru
 
;PunctDeLucru
, MAX(adreseF.AdresaPartener) AS AdresaFirma
+
;AdresaFirma
, MAX(f.Antet) AS Antet
+
;Antet
    , MAX(f.Footer) AS Footer
+
;Footer
 
+
;produsid
, MAX(isnull(p.produsid,det.ProdusId)) as produsid
+
;UmId
, MAX(det.Ordine) AS
+
;TipItem
, MAX(p.UmId) AS UmId
+
;DenumireProdus
, MAX(p.TipItem) AS TipItem
+
;Cod
, MAX(CASE @Setare391
+
;DenumireProdusFaraCod
WHEN 0 THEN isnull(p.DenumireProdus,pcp.DenumireProdus)
+
;attr1
            WHEN 1 THEN COALESCE(NULLIF(p.cod, ''), pcp.Cod, '') + ' ' +  isnull(p.DenumireProdus,pcp.DenumireProdus)
+
;Fabricant
            WHEN 2 THEN  isnull(p.DenumireProdus,pcp.DenumireProdus) + ' ' + COALESCE(NULLIF(p.Cod, ''),pcp.Cod, '')
+
;UmPoz
        END) AS DenumireProdus
+
;UM
, MAX(p.Cod) as Cod
+
;UMBax
, MAX(isnull(p.DenumireProdus,det.DenumireProdus)) AS DenumireProdusFaraCod
+
;BucPePalet
, MAX(p.attr1) AS attr1
+
;BucPeBax
    ,  MAX(fab.DenumireFabricant) as Fabricant
+
;UM1
 
+
;UMVanzare
, MAX(um.UM) AS UmPoz
+
;Stoc
, MAX(ump.UM) AS UM
+
;Cant     
, MAX(umbax.UM) AS UMBax
+
;Cantitate   
, MAX(p.BucPalet) AS BucPePalet
+
;UMAlternativ
, MAX(p.bucatiBAX) AS BucPeBax
+
;CantFactor
, MAX(ISNULL(um.UM, ump.UM) )AS UM1
+
;CantAlternativ
, MAX(COALESCE(um2.UM, um.UM, ump.UM)) AS UMVanzare
+
;LotIntrare
 
+
;DataExpirare
, MAX(ISNULL(st.Stoc, 0)) AS Stoc
+
;SerieIntrare
, inv.numarzecimale(@Setare350, SUM(det.CantIesire)) AS Cant     
+
;GestiuneLivrare
, inv.numarzecimale(@Setare350, SUM(det.CantIesire)) AS Cantitate   
+
;CodGestiune
+
;TermenLivrarePoz
, MAX(ISNULL(um.UM,ump.UM)) AS UMAlternativ
+
;DataLivrarePoz
,  SUM(det.CantIesire/ISNULL(NULLIF(pum.Factor, 0), 1)) CantFactor
+
;ObservatiiInternePoz
, inv.NumarZecimale(@setare350, SUM(det.CantIesire/ ISNULL(pum.Factor, 1))) AS CantAlternativ
+
;GreutateNETA
 
+
;GreutateBRUTA
+
;ValoarePozitii
, MAX(ISNULL(det.LotIntrare,'')) AS LotIntrare
+
;CantitateMeniu
, ISNULL(CONVERT(varchar(10),MAX(det.DataExpirare),103),'') AS DataExpirare
+
;Meniu
, MAX(ISNULL(det.SerieIntrare,'')) AS SerieIntrare
+
;PretamanuntMeniu
, MAX(ISNULL(gr.DenumireGestiune,'')) AS GestiuneLivrare
+
;Cantitate2
, MAX(ISNULL(gr.CodGestiune,'')) AS CodGestiune
+
;GrNetProd
, MAX(det.TermenLivrare) AS TermenLivrarePoz
+
;Agent
, CONVERT(VARCHAR(10),MAX(det.DataLivrare),103) AS DataLivrarePoz
+
;TelefonFurnizor
, MAX(det.ObservatiiInterne) AS ObservatiiInternePoz
+
;DenumireTara
--, MAX(det.Observatii) AS ObservatiiPoz
+
;denin
, SUM(det.CantIesire * p.GreutateNeta) AS GreutateNETA
+
;f
, SUM(det.CantIesire * p.Greutate) AS GreutateBRUTA
+
;DataLivrareCapPoz
+
;Referinta
, MAX(oc.ValoareAmanunt) AS ValoarePozitii
+
;Greutate
+
;VolumBax
, SUM(s2.Cant) AS CantitateMeniu
+
;DRankRow
, MAX(ISNULL(s2.DenumireProdusAfisare,p2.DenumireProdus)) AS Meniu
+
;DRankOrder
, MAX(ISNULL(s2.PretAmanuntRedus,s2.PretAmanunt)) AS PretamanuntMeniu
+
;AdresaLivrare_Antet
+
;Denumire_ContactLivrare
, SUM(isnull(det.Cantitate2, det.CantIesire)) as Cantitate2
+
;Email_ContactLivrare
, MAX(p.GreutateNeta) AS GrNetProd
+
;Telefon_ContactLivrare
,  MAX(ua.DenumireUnitate) AS Agent
+
;Descriere
, MAX(ISNULL(ctf.Telefon1,ctf.Telefon2)) AS TelefonFurnizor
+
;IesCapIdCB
, MAX(uc.DenumireTara) AS DenumireTara
+
;Locator
,  row_number () over( order by CASE WHEN max(oc.TipOrdine) = 1 THEN  CASE @Setare391 WHEN 0 THEN max(p.DenumireProdus) + ISNULL('<br/>' + max(p.DenProdusFabricant) , '')
+
;NumarFacturaOriginala
WHEN 1 THEN max(ISNULL(p.Cod , '')) + ' ' + max(p.DenumireProdus) + ISNULL('<br/>' + max(p.DenProdusFabricant)  , '')
+
;ClientFacturaOriginala
WHEN 2 THEN max(p.DenumireProdus) + ' ' + max(ISNULL(p.Cod , ''))+ ISNULL('<br/>' + max(p.DenProdusFabricant)  , '')
+
;ContineDetalii
END END,
+
;ordinePrincipale
CASE WHEN max(oc.TipOrdine) = 2 THEN max(det.SOrderPozId)
+
;ordineDetalii
WHEN max(oc.TipOrdine) = 3 THEN max(det.Ordine)
+
;ParentiIesPozId
ELSE max(det.Ordine) END) as denin
+
; SumaPretValuta
 
+
;CuChitanta
, CASE
+
;DataValidare
WHEN MAX(oc.TipOrdine) = 2 THEN MAX(det.SOrderPozId)
+
;Client
WHEN MAX(oc.TipOrdine) = 3 THEN MAX(det.Ordine)
+
;Client_Unitate
ELSE MAX(det.Ordine)
+
;Client_Partener
END as f
+
;Cont_Client
, ISNULL(CONVERT(VARCHAR(10),MAX(det.DataLivrare),103),CONVERT(VARCHAR(10),MAX(oc.DataLivrare),103)) AS DataLivrareCapPoz
+
;Banca_Client
  -- , CASE WHEN MAX(ofc.OfertaClientId )IS NULL THEN ''
+
;Conturi_Client
--ELSE ISNULL(@L_Oferta + ': ', '') + ISNULL(MAX(ofc.NumarOferta) + ' - ', '') + ISNULL('-' + CONVERT(VARCHAR(10), MAX(ofc.DataOferta), 103), '') + '<br/>'
+
;CodFiscal_Client
--END +
+
;CodFiscal_Unitate
,CASE WHEN MAX(oc.ContractClientId) IS NULL THEN ''
+
;AdresaFaraJudet_client
ELSE ISNULL(@L_Contract + ': ', '') + ISNULL(MAX(contr.NumarContract) + ' - ', '') + ISNULL(CONVERT(VARCHAR(10), MAX(contr.DataContract), 103), '') + '<br/>'
+
;DenumireLocalitate_Client
END +     
+
;DenumireJudet_Client
CASE WHEN (MAX(cap.IesCapId) IS NOT NULL AND MAX(cap.DocId) = 5)
+
;NrRegComertului_Client
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), '') + '<br/>'
+
;Agent
ELSE '' END +
+
;TelefonAgent
CASE WHEN (MAX(cap.IesCapId) IS NOT NULL AND MAX(cap.DocId) = 21)
+
;CentruCostPoz
        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), '') + '<br/>'
+
;Client_Adresa
        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), '') + '<br/>'
 
        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), '') + '<br/>'
 
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), '') + '<br/>'
 
        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), '') + '<br/>'
 
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('<br/>' + MAX(p.DenProdusFabricant) , '')
 
WHEN 1 THEN ISNULL(MAX(p.Cod), '') + ' ' + MAX(p.DenumireProdus )+ ISNULL('<br/>' + MAX(p.DenProdusFabricant) , '')
 
WHEN 2 THEN MAX(p.DenumireProdus) + ' ' + ISNULL(MAX(p.Cod) , '') + ISNULL('<br/>' + 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_AdresaLivrare
 
;Client_Capitalsocial
 
;Client_Capitalsocial

Latest revision as of 17:07, 30 August 2022


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
Email
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
TermenLivrarePoz
DataLivrarePoz
ObservatiiInternePoz
GreutateNETA
GreutateBRUTA
ValoarePozitii
CantitateMeniu
Meniu
PretamanuntMeniu
Cantitate2
GrNetProd
Agent
TelefonFurnizor
DenumireTara
denin
f
DataLivrareCapPoz
Referinta
Greutate
VolumBax
DRankRow
DRankOrder
AdresaLivrare_Antet
Denumire_ContactLivrare
Email_ContactLivrare
Telefon_ContactLivrare
Descriere
IesCapIdCB
Locator
NumarFacturaOriginala
ClientFacturaOriginala
ContineDetalii
ordinePrincipale
ordineDetalii
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