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