|
|
Line 97: |
Line 97: |
| ;GestiuneLivrare | | ;GestiuneLivrare |
| ;CodGestiune | | ;CodGestiune |
− | , MAX(det.TermenLivrare) AS TermenLivrarePoz
| + | ;TermenLivrarePoz |
− | , CONVERT(VARCHAR(10),MAX(det.DataLivrare),103) AS DataLivrarePoz
| + | ;DataLivrarePoz |
− | , MAX(det.ObservatiiInterne) AS ObservatiiInternePoz
| + | ;ObservatiiInternePoz |
− | --, MAX(det.Observatii) AS ObservatiiPoz
| + | ;GreutateNETA |
− | , SUM(det.CantIesire * p.GreutateNeta) AS GreutateNETA
| + | ;GreutateBRUTA |
− | , SUM(det.CantIesire * p.Greutate) AS GreutateBRUTA
| + | ;ValoarePozitii |
− |
| + | ;CantitateMeniu |
− | , MAX(oc.ValoareAmanunt) AS ValoarePozitii
| + | ;Meniu |
− |
| + | ;PretamanuntMeniu |
− | , SUM(s2.Cant) AS CantitateMeniu
| + | ;Cantitate2 |
− | , MAX(ISNULL(s2.DenumireProdusAfisare,p2.DenumireProdus)) AS Meniu
| + | ;GrNetProd |
− | , MAX(ISNULL(s2.PretAmanuntRedus,s2.PretAmanunt)) AS PretamanuntMeniu
| + | ;Agent |
− |
| + | ;TelefonFurnizor |
− | , SUM(isnull(det.Cantitate2, det.CantIesire)) as Cantitate2
| + | ;DenumireTara |
− | , MAX(p.GreutateNeta) AS GrNetProd
| + | ;denin |
− | , MAX(ua.DenumireUnitate) AS Agent
| + | ;f |
− | , MAX(ISNULL(ctf.Telefon1,ctf.Telefon2)) AS TelefonFurnizor
| + | ;DataLivrareCapPoz |
− | , MAX(uc.DenumireTara) AS DenumireTara
| + | ;Referinta |
− | , 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) , '')
| + | ;Greutate |
− | WHEN 1 THEN max(ISNULL(p.Cod , '')) + ' ' + max(p.DenumireProdus) + ISNULL('<br/>' + max(p.DenProdusFabricant) , '')
| + | ;VolumBax |
− | WHEN 2 THEN max(p.DenumireProdus) + ' ' + max(ISNULL(p.Cod , ''))+ ISNULL('<br/>' + max(p.DenProdusFabricant) , '')
| + | ;DRankRow |
− | END END,
| + | ;DRankOrder |
− | CASE WHEN max(oc.TipOrdine) = 2 THEN max(det.SOrderPozId)
| + | ;AdresaLivrare_Antet |
− | WHEN max(oc.TipOrdine) = 3 THEN max(det.Ordine)
| + | ;Denumire_ContactLivrare |
− | ELSE max(det.Ordine) END) as denin
| + | ;Email_ContactLivrare |
− | | + | ;Telefon_ContactLivrare |
− | , CASE
| + | ;Descriere |
− | WHEN MAX(oc.TipOrdine) = 2 THEN MAX(det.SOrderPozId)
| + | ;IesCapIdCB |
− | WHEN MAX(oc.TipOrdine) = 3 THEN MAX(det.Ordine)
| + | ;Locator |
− | ELSE MAX(det.Ordine)
| + | ;NumarFacturaOriginala |
− | END as f
| + | ;ClientFacturaOriginala |
− | , ISNULL(CONVERT(VARCHAR(10),MAX(det.DataLivrare),103),CONVERT(VARCHAR(10),MAX(oc.DataLivrare),103)) AS DataLivrareCapPoz
| + | ;ContineDetalii |
− | -- , CASE WHEN MAX(ofc.OfertaClientId )IS NULL THEN ''
| + | ;ordinePrincipale |
− | --ELSE ISNULL(@L_Oferta + ': ', '') + ISNULL(MAX(ofc.NumarOferta) + ' - ', '') + ISNULL('-' + CONVERT(VARCHAR(10), MAX(ofc.DataOferta), 103), '') + '<br/>'
| + | ;ordineDetalii |
− | --END +
| + | ;ParentiIesPozId |
− | ,CASE WHEN MAX(oc.ContractClientId) IS NULL THEN ''
| |
− | ELSE ISNULL(@L_Contract + ': ', '') + ISNULL(MAX(contr.NumarContract) + ' - ', '') + ISNULL(CONVERT(VARCHAR(10), MAX(contr.DataContract), 103), '') + '<br/>'
| |
− | 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), '') + '<br/>'
| |
− | 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), '') + '<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
| |
| ; SumaPretValuta | | ; SumaPretValuta |
| ;CuChitanta | | ;CuChitanta |