/****** Object: UserDefinedFunction [dbo].[MG2S_GetDossiersDoc] Script Date: 26/10/2017 11:33:20 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[MG2S_GetDossiersDoc](@ParamDossier NVARCHAR(250)) RETURNS TABLE AS RETURN SELECT CAST("v"."ObjType" AS NVARCHAR(250)) AS "ObjType", "v"."DocEntry", "DocNum", "DocDate", "LineNum", CAST("LineStatus" AS NVARCHAR(250)) AS "LineStatus", CAST("DocStatus" AS NVARCHAR(250)) AS "DocStatus", CAST("v"."CardCode" AS NVARCHAR(250)) AS "CardCode", CAST(CASE WHEN COALESCE("v"."CardName", '') <> '' THEN "v"."CardName" ELSE T1."CardName" END AS NVARCHAR(250)) AS "CardName", CAST("Project" AS NVARCHAR(250)) AS "Project", CAST("OcrCode" AS NVARCHAR(250)) AS "OcrCode", CAST("Fournisseur" AS NVARCHAR(250)) AS "Fournisseur", CAST("ItemCode" AS NVARCHAR(250)) AS "ItemCode", CAST("Dscription" AS NVARCHAR(250)) AS "Dscription", "Quantity", "LineTotal", "VatSum", "FretTotal", "FretVat", "ItemFret" , "ItemFretVat" , CAST("U_MG2S_Dossier" AS NVARCHAR(250)) AS "U_MG2S_Dossier", CAST("Confirmed" AS NVARCHAR(250)) AS "Confirmed", CAST("WhsCode" AS NVARCHAR(250)) AS "WhsCode", "U_MG2S_FourName", "GrossBuyPr", "U_MG2S_BaseAchat", "U_MG2S_BaseTime", "SpecificRegr", "D"."U_FraisInt" AS "FraisInt", "D"."U_ActClot" AS "ActClot", "D"."U_AchSto" AS "AchSto", "D"."U_ActEnCour" AS "ActEnCour" FROM ( -- Ventes : Commandes SELECT T1."ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", T0."LineStatus", T1."DocStatus", T1."CardCode", T1."CardName", T0."Project", T0."OcrCode", T0."U_MG2S_Four" AS "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS "ItemCode", T0."Dscription", CASE WHEN "LineStatus" = 'C' THEN T0."Quantity" ELSE T0."OpenQty" END AS "Quantity", ((T0."LineTotal" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * (CASE WHEN "LineStatus" = 'C' OR "DocType" = 'S' THEN 1.00 ELSE (T0."OpenQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "LineTotal", ((T0."VatSum"* ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * (CASE WHEN "LineStatus" = 'C' OR "DocType" = 'S' THEN 1.00 ELSE (T0."OpenQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "VatSum", (COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * (CASE WHEN "LineStatus" = 'C' OR "DocType" = 'S' THEN 1.00 ELSE (T0."OpenQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "FretTotal", (COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * (CASE WHEN "LineStatus" = 'C' OR "DocType" = 'S' THEN 1.00 ELSE (T0."OpenQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "FretVat", (COALESCE(T3."ItemFret", 0)) * (CASE WHEN "LineStatus" = 'C' OR "DocType" = 'S' THEN 1.00 ELSE (T0."OpenQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "ItemFret", (COALESCE(T3."ItemVatSum", 0)) * (CASE WHEN "LineStatus" = 'C' OR "DocType" = 'S' THEN 1.00 ELSE (T0."OpenQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "ItemFretVat", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T0."U_MG2S_FourName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM RDR1 T0 INNER JOIN ORDR T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from RDR2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum") T2 ON T0."DocEntry" = T2."DocEntry" AND T0."LineNum" = T2."LineNum" LEFT JOIN SDOne_VentilationFret T3 ON T3."ObjType" = 17 AND T3."LineNum" = T0."LineNum" AND T3."DocEntry" = T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM RDR1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL -- Ventes : Commandes SELECT T1."ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", 'C' AS "LineStatus", 'O' AS "DocStatus", T1."CardCode", T1."CardName", T0."Project", T0."OcrCode", T0."U_MG2S_Four" AS "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS "ItemCode", T0."Dscription", T0."Quantity" - T0."OpenQty" AS "Quantity", ((T0."LineTotal" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * ((T0."Quantity" - T0."OpenQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "LineTotal", ((T0."VatSum" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * ((T0."Quantity" - T0."OpenQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "VatSum", (COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * ((T0."Quantity" - T0."OpenQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "FretTotal", (COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * ((T0."Quantity" - T0."OpenQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "FretVat", (COALESCE(T3."ItemFret", 0)) * ((T0."Quantity" - T0."OpenQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "ItemFret", (COALESCE(T3."ItemVatSum", 0)) * ((T0."Quantity" - T0."OpenQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "ItemFretVat", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T0."U_MG2S_FourName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM RDR1 T0 INNER JOIN ORDR T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from RDR2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum")T2 ON T0."DocEntry" = "T2"."DocEntry" AND T0."LineNum" = T2."LineNum" LEFT JOIN SDOne_VentilationFret T3 ON T3."ObjType" = 17 AND T3."LineNum" = T0."LineNum" AND T3."DocEntry" = T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND (T0."LineStatus" = 'O') AND (T1."DocStatus" = 'O') AND (T0."OpenQty" <> T0."Quantity") AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM RDR1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL --Vente : Devis SELECT T1."ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", T0."LineStatus", T1."DocStatus", T1."CardCode", T1."CardName", T0."Project", T0."OcrCode", T0."U_MG2S_Four" AS "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS "ItemCode", T0."Dscription", CASE WHEN "LineStatus" = 'C' THEN T0."Quantity" ELSE T0."OpenQty" END AS "Quantity", ((T0."LineTotal" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * (CASE WHEN "LineStatus" = 'C' OR "DocType" = 'S' THEN 1.00 ELSE (T0."OpenQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "LineTotal", ((T0."VatSum" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * (CASE WHEN "LineStatus" = 'C' OR "DocType" = 'S' THEN 1.00 ELSE (T0."OpenQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "VatSum", (COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * (CASE WHEN "LineStatus" = 'C' OR "DocType" = 'S' THEN 1.00 ELSE (T0."OpenQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "FretTotal", (COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * (CASE WHEN "LineStatus" = 'C' OR "DocType" = 'S' THEN 1.00 ELSE (T0."OpenQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "FretVat", (COALESCE(T3."ItemFret", 0)) * (CASE WHEN "LineStatus" = 'C' OR "DocType" = 'S' THEN 1.00 ELSE (T0."OpenQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "ItemFret", (COALESCE(T3."ItemVatSum", 0)) * (CASE WHEN "LineStatus" = 'C' OR "DocType" = 'S' THEN 1.00 ELSE (T0."OpenQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "ItemVatSum", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T0."U_MG2S_FourName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM QUT1 T0 INNER JOIN OQUT T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from QUT2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum")T2 ON T0."DocEntry" = T2."DocEntry" AND T0."LineNum" = T2."LineNum" LEFT JOIN SDOne_VentilationFret T3 ON T3."ObjType" = 23 AND T3."LineNum" = T0."LineNum" AND T3."DocEntry" = T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM QUT1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL SELECT T1."ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", 'C' AS "LineStatus", 'O' AS "DocStatus", T1."CardCode", T1."CardName", T0."Project", T0."OcrCode", T0."U_MG2S_Four" AS "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS "ItemCode", T0."Dscription", T0."Quantity" - T0."OpenQty" AS "Quantity", ((T0."LineTotal" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * ((T0."Quantity" - T0."OpenQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "LineTotal", ((T0."VatSum" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * ((T0."Quantity" - T0."OpenQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "VatSum", (COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * ((T0."Quantity" - T0."OpenQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "FretTotal", (COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * ((T0."Quantity" - T0."OpenQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "FretVatSum", (COALESCE(T3."ItemFret", 0)) * ((T0."Quantity" - T0."OpenQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "ItemFret", (COALESCE(T3."ItemVatSum", 0)) * ((T0."Quantity" - T0."OpenQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "ItemVatSum", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T0."U_MG2S_FourName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM QUT1 T0 INNER JOIN OQUT T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from QUT2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum")T2 ON T0."DocEntry" = "T2"."DocEntry" AND T0."LineNum" = T2."LineNum" LEFT JOIN SDOne_VentilationFret T3 ON T3."ObjType" = 23 AND T3."LineNum" = T0."LineNum" AND T3."DocEntry" = T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND (T0."LineStatus" = 'O') AND (T1."DocStatus" = 'O') AND (T0."OpenQty" <> T0."Quantity") AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM QUT1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL -- Ventes BLs SELECT T1."ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", T0."LineStatus", T1."DocStatus", T1."CardCode", T1."CardName", T0."Project", T0."OcrCode", T0."U_MG2S_Four" AS "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS "ItemCode", T0."Dscription", CASE WHEN "LineStatus" = 'C' THEN T0."Quantity" ELSE T0."OpenCreQty" END AS "Quantity", ((T0."LineTotal" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * (CASE WHEN "LineStatus" = 'C' OR "DocType" = 'S' THEN 1.00 ELSE (T0."OpenCreQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "LineTotal", ((T0."VatSum"* ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * (CASE WHEN "LineStatus" = 'C' OR "DocType" = 'S' THEN 1.00 ELSE (T0."OpenCreQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "VatSum", (COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * (CASE WHEN "LineStatus" = 'C' OR "DocType" = 'S' THEN 1.00 ELSE (T0."OpenCreQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "FretTotal", (COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * (CASE WHEN "LineStatus" = 'C' OR "DocType" = 'S' THEN 1.00 ELSE (T0."OpenCreQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "fretvat", (COALESCE(T3."ItemFret", 0)) * (CASE WHEN "LineStatus" = 'C' OR "DocType" = 'S' THEN 1.00 ELSE (T0."OpenCreQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "ItemFret", (COALESCE(T3."ItemVatSum", 0)) * (CASE WHEN "LineStatus" = 'C' OR "DocType" = 'S' THEN 1.00 ELSE (T0."OpenCreQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "Itemfretvat", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T0."U_MG2S_FourName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM DLN1 T0 INNER JOIN ODLN T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from DLN2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum") T2 ON T0."DocEntry" = T2."DocEntry" AND T0."LineNum" = T2."LineNum" LEFT JOIN SDOne_VentilationFret T3 ON T3."ObjType" = 15 AND T3."LineNum" = T0."LineNum" AND T3."DocEntry" = T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM DLN1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL SELECT T1."ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", 'C' AS "LineStatus", 'O' AS "DocStatus", T1."CardCode", T1."CardName", T0."Project", T0."OcrCode", T0."U_MG2S_Four" AS "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS "ItemCode", T0."Dscription", T0."Quantity" - T0."OpenCreQty" AS "Quantity", ((T0."LineTotal" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * ((T0."Quantity" - T0."OpenCreQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "LineTotal", ((T0."VatSum"* ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * ((T0."Quantity" - T0."OpenCreQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "VatSum", (COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * ((T0."Quantity" - T0."OpenCreQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "FRETTotal", (COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * ((T0."Quantity" - T0."OpenCreQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "FRETVatSum", (COALESCE(T3."ItemFret", 0)) * ((T0."Quantity" - T0."OpenCreQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "ItemFret", (COALESCE(T3."ItemVatSum", 0)) * ((T0."Quantity" - T0."OpenCreQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "ItemVatSum", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T0."U_MG2S_FourName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM DLN1 T0 INNER JOIN ODLN T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from DLN2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum")T2 ON T0."DocEntry" = "T2"."DocEntry" AND T0."LineNum" = T2."LineNum" LEFT JOIN SDOne_VentilationFret T3 ON T3."ObjType" = 15 AND T3."LineNum" = T0."LineNum" AND T3."DocEntry" = T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND (T0."LineStatus" = 'O') AND (T1."DocStatus" = 'O') AND (T0."OpenCreQty" <> T0."Quantity") AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM DLN1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL -- Ventes : Retours SELECT T1."ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", T0."LineStatus", T1."DocStatus", T1."CardCode", T1."CardName", T0."Project", T0."OcrCode", T0."U_MG2S_Four" AS "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS "ItemCode", T0."Dscription", T0."Quantity", ((T0."LineTotal" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) AS "LineTotal", ((T0."VatSum" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) AS "VatSum", (COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) AS "FretTotal", (COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) AS "FretVat", (COALESCE(T3."ItemFret", 0)) AS "ItemFretTotal", (COALESCE(T3."ItemVatSum", 0)) AS "ItemFretVat", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T0."U_MG2S_FourName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM RDN1 T0 INNER JOIN ORDN T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from RDN2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum")T2 ON T0."DocEntry" = T2."DocEntry" AND T0."LineNum" = T2."LineNum" LEFT JOIN SDOne_VentilationFret T3 ON T3."ObjType" = 16 AND T3."LineNum" = T0."LineNum" AND T3."DocEntry" = T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM RDN1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL --Ventes : Factures SELECT T1."ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", T0."LineStatus", T1."DocStatus", T1."CardCode", T1."CardName", T0."Project", T0."OcrCode", T0."U_MG2S_Four" AS "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS "ItemCode", T0."Dscription", T0."OpenQty" AS "Quantity", ((T0."LineTotal" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) AS "LineTotal", ((T0."VatSum"* ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) AS "VatSum", (COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) AS "FretTotal", (COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) AS "FretVat", (COALESCE(T3."ItemFret", 0)) AS "ItemFretTotal", (COALESCE(T3."ItemVatSum", 0)) AS "ItemFretVat", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T0."U_MG2S_FourName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM INV1 T0 INNER JOIN OINV T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from INV2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum")T2 ON T0."DocEntry" = "T2"."DocEntry" AND T0."LineNum" = T2."LineNum" LEFT JOIN SDOne_VentilationFret T3 ON T3."ObjType" = 13 AND T3."LineNum" = T0."LineNum" AND T3."DocEntry" = T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM INV1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL --Ventes : Avoirs SELECT CAST(T1."ObjType" AS NVARCHAR(10)) + CASE WHEN T0."BaseType" = '203' THEN '-Acp' ELSE '-' END AS "ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", T0."LineStatus", T1."DocStatus", T1."CardCode", T1."CardName", T0."Project", T0."OcrCode", T0."U_MG2S_Four" AS "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS "ItemCode", T0."Dscription", T0."Quantity", ((T0."LineTotal" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) AS "LineTotal", ((T0."VatSum"* ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) AS "VatSum", (COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) AS "FretTotal", (COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) AS "FretVat", (COALESCE(T3."ItemFret", 0)) AS "ItemFretTotal", (COALESCE(T3."ItemVatSum", 0)) AS "ItemFretVat", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T0."U_MG2S_FourName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM RIN1 T0 INNER JOIN ORIN T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from RIN2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum")T2 ON T0."DocEntry" = "T2"."DocEntry" AND T0."LineNum" = T2."LineNum" LEFT JOIN SDOne_VentilationFret T3 ON T3."ObjType" = 14 AND T3."LineNum" = T0."LineNum" AND T3."DocEntry" = T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM RIN1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL --Paiements Entrant SELECT CAST(T1."ObjType" AS NVARCHAR(10)) + '-' + CASE WHEN T1."PaidSum" = T1."DocTotal" THEN 'RE' ELSE CASE WHEN "CreateTran" = 'Y' THEN 'F' ELSE 'D' END END AS "ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", T0."LineStatus", T1."DocStatus", T1."CardCode", T1."CardName", T0."Project", T0."OcrCode", T0."U_MG2S_Four" AS "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS "ItemCode", T0."Dscription", CASE WHEN T0."LineStatus" = 'O' AND T1."DocStatus" = 'O' THEN T0."OpenQty" ELSE T0."Quantity" END AS "Quantity", ((T0."LineTotal" + COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * (T1."DpmPrcnt" / 100) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR (T1."PaidSum" <> 0 AND T1."PaidSum" <> T1."DocTotal") THEN T1."DocTotal" - T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) AS "LineTotal", ((T0."VatSum" + COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR (T1."PaidSum" <> 0 AND T1."PaidSum" <> T1."DocTotal") THEN T1."DocTotal" - T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) * (T1."DpmPrcnt" / 100) AS "VatSum", ((COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * (T1."DpmPrcnt" / 100) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR (T1."PaidSum" <> 0 AND T1."PaidSum" <> T1."DocTotal") THEN T1."DocTotal" - T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) AS "FretTotal", ((COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR (T1."PaidSum" <> 0 AND T1."PaidSum" <> T1."DocTotal") THEN T1."DocTotal" - T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) * (T1."DpmPrcnt" / 100) AS "FretVatSum", ((COALESCE(T3."ItemFret", 0)) * (T1."DpmPrcnt" / 100) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR (T1."PaidSum" <> 0 AND T1."PaidSum" <> T1."DocTotal") THEN T1."DocTotal" - T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) AS "ItemFret", ((COALESCE(T3."ItemVatSum", 0)) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR (T1."PaidSum" <> 0 AND T1."PaidSum" <> T1."DocTotal") THEN T1."DocTotal" - T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) * (T1."DpmPrcnt" / 100) AS "ItemVatSum", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T0."U_MG2S_FourName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM DPI1 T0 INNER JOIN ODPI T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from DPI2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum")T2 ON T0."DocEntry" = "T2"."DocEntry" AND T0."LineNum" = T2."LineNum" LEFT JOIN SDOne_VentilationFret T3 ON T3."ObjType" = 203 AND T3."LineNum" = T0."LineNum" AND T3."DocEntry" = T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM DPI1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL SELECT CAST(T1."ObjType" AS NVARCHAR(10)) + '-' + CASE WHEN T1."PaidSum" <> 0 THEN 'RE' ELSE CASE WHEN "CreateTran" = 'Y' THEN 'F' ELSE 'D' END END AS "ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", 'C' AS "LineStatus", 'C' AS "DocStatus", T1."CardCode", T1."CardName", T0."Project", T0."OcrCode", T0."U_MG2S_Four" AS "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS "ItemCode", T0."Dscription", CASE WHEN T0."LineStatus" = 'O' AND T1."DocStatus" = 'O' THEN T0."OpenQty" ELSE T0."Quantity" END AS "Quantity", ((T0."LineTotal" + COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * (T1."DpmPrcnt" / 100) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR T1."PaidSum" <> 0 THEN T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) AS "LineTotal", ((T0."VatSum" + COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR T1."PaidSum" <> 0 THEN T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) * (T1."DpmPrcnt" / 100) AS "VatSum", ((COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * (T1."DpmPrcnt" / 100) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR T1."PaidSum" <> 0 THEN T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) AS "FRETTotal", ((COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR T1."PaidSum" <> 0 THEN T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) * (T1."DpmPrcnt" / 100) AS "FretVatSum", ((COALESCE(T3."ItemFret", 0)) * (T1."DpmPrcnt" / 100) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR T1."PaidSum" <> 0 THEN T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) AS "ItemFret", ((COALESCE(T3."ItemVatSum", 0)) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR T1."PaidSum" <> 0 THEN T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) * (T1."DpmPrcnt" / 100) AS "ItemVatSum", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T0."U_MG2S_FourName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM DPI1 T0 INNER JOIN ODPI T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from DPI2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum") T2 ON T0."DocEntry" = "T2"."DocEntry" AND T0."LineNum" = T2."LineNum" LEFT JOIN SDOne_VentilationFret T3 ON T3."ObjType" = 203 AND T3."LineNum" = T0."LineNum" AND T3."DocEntry" = T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') AND T1."PaidSum" <> 0 AND T1."PaidSum" <> T1."DocTotal" AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM DPI1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL --Achats : Appel d'offres SELECT T1."ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", T0."LineStatus", T1."DocStatus", T1."CardCode", T1."CardName",T0."Project", T0."OcrCode", T1."CardCode" "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS Expr1, T0."Dscription", (CASE WHEN "LineStatus"= 'C' THEN T0."Quantity" ELSE T0."OpenQty" END) AS "Quantity", ((T0."LineTotal"*((100-COALESCE(T1."DiscPrcnt",0))/100))+COALESCE(T2."LineTotal",0)+COALESCE(T3."FretTotal",0)) * (CASE WHEN "LineStatus" = 'C' Or "DocType" = 'S' THEN 1.00 ELSE (T0."OpenQty" / case when T0."Quantity"=0 then 1 else T0."Quantity" end) END) AS "LineTotal", ((T0."VatSum"*((100-COALESCE(T1."DiscPrcnt",0))/100))+COALESCE(T2."VatSum",0)+COALESCE(T3."VatSum",0)) * (CASE WHEN "LineStatus" = 'C' Or "DocType" = 'S' THEN 1.00 ELSE (T0."OpenQty" / case when T0."Quantity"=0 then 1 else T0."Quantity" end) END) AS "VatSum", (COALESCE(T2."LineTotal",0)+COALESCE(T3."FretTotal",0)) * (CASE WHEN "LineStatus" = 'C' Or "DocType" = 'S' THEN 1.00 ELSE (T0."OpenQty" / case when T0."Quantity"=0 then 1 else T0."Quantity" end) END) AS "FretTotal", (COALESCE(T2."VatSum",0)+COALESCE(T3."VatSum",0)) * (CASE WHEN "LineStatus" = 'C' Or "DocType" = 'S' THEN 1.00 ELSE (T0."OpenQty" / case when T0."Quantity"=0 then 1 else T0."Quantity" end) END) AS "fretVat", (COALESCE(T3."ItemFret",0)) * (CASE WHEN "LineStatus" = 'C' Or "DocType" = 'S' THEN 1.00 ELSE (T0."OpenQty" / case when T0."Quantity"=0 then 1 else T0."Quantity" end) END) AS "ItemFret", (COALESCE(T3."ItemVatSum",0)) * (CASE WHEN "LineStatus" = 'C' Or "DocType" = 'S' THEN 1.00 ELSE (T0."OpenQty" / case when T0."Quantity"=0 then 1 else T0."Quantity" end) END) AS "ItemVat", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T0."U_MG2S_FourName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM PQT1 T0 INNER JOIN OPQT AS T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from PQT2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum") T2 ON T0."DocEntry" = "T2"."DocEntry" AND T0."LineNum" = T2."LineNum" Left Join SDOne_VentilationFret T3 ON T3."ObjType" = 540000006 AND T3."LineNum"=T0."LineNum" and T3."DocEntry"=T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier or @ParamDossier = '') AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM PQT1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL --Achats : Commandes SELECT T1."ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", T0."LineStatus", T1."DocStatus", T1."CardCode", T1."CardName", T0."Project", T0."OcrCode", T1."CardCode" AS "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS "ItemCode", T0."Dscription", CASE WHEN T0."LineStatus" = 'C' THEN T0."Quantity" ELSE T0."OpenQty" END AS "Quantity", ((T0."LineTotal" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * (CASE WHEN (T0."LineStatus" = 'C' OR T1."DocType" = 'S') THEN 1 ELSE (T0."OpenQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "LineTotal", ((T0."VatSum" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * (CASE WHEN (T0."LineStatus" = 'C' OR T1."DocType" = 'S') THEN 1 ELSE (T0."OpenQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "VatSum", (COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * (CASE WHEN (T0."LineStatus" = 'C' OR T1."DocType" = 'S') THEN 1 ELSE (T0."OpenQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "FretTotal", (COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * (CASE WHEN (T0."LineStatus" = 'C' OR T1."DocType" = 'S') THEN 1 ELSE (T0."OpenQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "FretVatSum", (COALESCE(T3."ItemFret", 0)) * (CASE WHEN (T0."LineStatus" = 'C' OR T1."DocType" = 'S') THEN 1 ELSE (T0."OpenQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "ItemFret", (COALESCE(T3."ItemVatSum", 0)) * (CASE WHEN (T0."LineStatus" = 'C' OR T1."DocType" = 'S') THEN 1 ELSE (T0."OpenQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "ItemVatSum", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T1."CardName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM POR1 T0 INNER JOIN OPOR T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from POR2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum") T2 ON T0."DocEntry" = "T2"."DocEntry" AND T0."LineNum" = T2."LineNum" LEFT JOIN SDOne_VentilationFret T3 ON T3."ObjType" = 22 AND T3."LineNum" = T0."LineNum" AND T3."DocEntry" = T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM POR1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL SELECT T1."ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", 'C' AS "LineStatus", 'O' AS "DocStatus", T1."CardCode", T1."CardName", T0."Project", T0."OcrCode", T1."CardCode" AS "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS "ItemCode", T0."Dscription", T0."Quantity" - T0."OpenQty" AS "Quantity", ((T0."LineTotal" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * ((T0."Quantity" - T0."OpenQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "LineTotal", ((T0."VatSum" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * ((T0."Quantity" - T0."OpenQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "VatSum", (COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * ((T0."Quantity" - T0."OpenQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "FretTotal", (COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * ((T0."Quantity" - T0."OpenQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "FretVat", (COALESCE(T3."ItemFret", 0)) * ((T0."Quantity" - T0."OpenQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "ItemFret", (COALESCE(T3."ItemVatSum", 0)) * ((T0."Quantity" - T0."OpenQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "ItemVatSum", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T1."CardName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM POR1 T0 INNER JOIN OPOR T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from POR2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum") T2 ON T0."DocEntry" = "T2"."DocEntry" AND T0."LineNum" = T2."LineNum" LEFT JOIN SDOne_VentilationFret T3 ON T3."ObjType" = 22 AND T3."LineNum" = T0."LineNum" AND T3."DocEntry" = T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND T0."LineStatus" = '0' AND T0."OpenQty" <> T0."Quantity" AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM POR1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL --Achats : R�c�ptions de marchandises SELECT T1."ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", T0."LineStatus", T1."DocStatus", T1."CardCode", T1."CardName", T0."Project", T0."OcrCode", T1."CardCode" AS "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS "ItemCode", T0."Dscription", CASE WHEN T0."LineStatus" = 'C' THEN T0."Quantity" ELSE T0."OpenCreQty" END AS "Quantity", ((T0."LineTotal" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * (CASE WHEN (T0."LineStatus" = 'C' OR T1."DocType" = 'S') THEN 1 ELSE (T0."OpenCreQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "LineTotal", ((T0."VatSum"* ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * (CASE WHEN (T0."LineStatus" = 'C' OR T1."DocType" = 'S') THEN 1 ELSE (T0."OpenCreQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "VatSum", (COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * (CASE WHEN (T0."LineStatus" = 'C' OR T1."DocType" = 'S') THEN 1 ELSE (T0."OpenCreQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "FretTotal", (COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * (CASE WHEN (T0."LineStatus" = 'C' OR T1."DocType" = 'S') THEN 1 ELSE (T0."OpenCreQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "FretVatSum", (COALESCE(T3."ItemFret", 0)) * (CASE WHEN (T0."LineStatus" = 'C' OR T1."DocType" = 'S') THEN 1 ELSE (T0."OpenCreQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "ItemFret", (COALESCE(T3."ItemVatSum", 0)) * (CASE WHEN (T0."LineStatus" = 'C' OR T1."DocType" = 'S') THEN 1 ELSE (T0."OpenCreQty" / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) END) AS "ItemVatSum", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T1."CardName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM PDN1 T0 INNER JOIN OPDN T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from PDN2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum") T2 ON T0."DocEntry" = "T2"."DocEntry" AND T0."LineNum" = T2."LineNum" LEFT JOIN SDOne_VentilationFret T3 ON T3."ObjType" = 20 AND T3."LineNum" = T0."LineNum" AND T3."DocEntry" = T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM PDN1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL SELECT T1."ObjType" + 'DC' AS "ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", "LineStatus", "DocStatus", T1."CardCode", T1."CardName", T0."Project", T0."OcrCode", T1."CardCode" AS "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS "ItemCode", T0."Dscription", CASE WHEN T0."LineStatus" = 'C' THEN T0."Quantity" ELSE T0."OpenCreQty" END AS "Quantity", ((T0."LineTotal" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) AS "LineTotal", ((T0."VatSum" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) AS "VatSum", (COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) AS "FretTotal", (COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) AS "FretVat", (COALESCE(T3."ItemFret", 0)) AS "ItemFretTotal", (COALESCE(T3."ItemVatSum", 0)) AS "ItemFretVat", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T1."CardName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM PDN1 T0 INNER JOIN OPDN T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from PDN2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum") T2 ON T0."DocEntry" = "T2"."DocEntry" AND T0."LineNum" = T2."LineNum" LEFT JOIN SDOne_VentilationFret T3 ON T3."ObjType" = 20 AND T3."LineNum" = T0."LineNum" AND T3."DocEntry" = T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') AND T0."LineStatus" = 'C' AND T0."BaseType" <> 18 AND NOT EXISTS(SELECT 1 FROM PCH1 WHERE "BaseEntry" = T0."DocEntry" AND "BaseLine" = T0."LineNum" AND "BaseType" = '20') AND NOT EXISTS(SELECT 1 FROM RPD1 WHERE "BaseEntry" = T0."DocEntry" AND "BaseLine" = T0."LineNum" AND "BaseType" = '20') AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM PDN1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL SELECT T1."ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", 'C' AS "LineStatus", 'O' AS "DocStatus", T1."CardCode", T1."CardName", T0."Project", T0."OcrCode", T1."CardCode" AS "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS "ItemCode", T0."Dscription", T0."Quantity" - T0."OpenCreQty" AS "Quantity", ((T0."LineTotal" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * ((T0."Quantity" - T0."OpenCreQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "LineTotal", ((T0."VatSum"* ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * ((T0."Quantity" - T0."OpenCreQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "VatSum", (COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * ((T0."Quantity" - T0."OpenCreQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "FretTotal", (COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * ((T0."Quantity" - T0."OpenCreQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "FretVat", (COALESCE(T3."ItemFret", 0)) * ((T0."Quantity" - T0."OpenCreQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "ItemFret", (COALESCE(T3."ItemVatSum", 0)) * ((T0."Quantity" - T0."OpenCreQty") / CASE WHEN T0."Quantity" = 0 THEN 1 ELSE T0."Quantity" END) AS "ItemVatSum", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T1."CardName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM PDN1 T0 INNER JOIN OPDN T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from PDN2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum") T2 ON T0."DocEntry" = "T2"."DocEntry" AND T0."LineNum" = T2."LineNum" LEFT JOIN SDOne_VentilationFret T3 ON T3."ObjType" = 20 AND T3."LineNum" = T0."LineNum" AND T3."DocEntry" = T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND T0."LineStatus" = 'O' AND T1."DocStatus" = 'O' AND T0."OpenCreQty" <> T0."Quantity" AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM PDN1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL --Achats : Retours SELECT T1."ObjType" + 'D' AS "ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", "LineStatus", "DocStatus", T1."CardCode", T1."CardName", T0."Project", T0."OcrCode", T1."CardCode" AS "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS "ItemCode", T0."Dscription", CASE WHEN T0."LineStatus" = 'C' THEN T0."Quantity" ELSE T0."OpenQty" END AS "Quantity", ((T0."LineTotal" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) AS "LineTotal", ((T0."VatSum" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) AS "VatSum", (COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) AS "FretTotal", (COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) AS "FretVat", (COALESCE(T3."ItemFret", 0)) AS "ItemFretTotal", (COALESCE(T3."ItemVatSum", 0)) AS "ItemFretVat", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T1."CardName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM RPD1 T0 INNER JOIN ORPD T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from RPD2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum") T2 ON T0."DocEntry" = "T2"."DocEntry" AND T0."LineNum" = T2."LineNum" LEFT JOIN SDOne_VentilationFret T3 ON T3."ObjType" = 21 AND T3."LineNum" = T0."LineNum" AND T3."DocEntry" = T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') AND T0."LineStatus" = 'C' AND T0."BaseType" <> '20' AND NOT EXISTS(SELECT 1 FROM RPC1 WHERE "BaseEntry" = T0."DocEntry" AND "BaseLine" = T0."LineNum" AND "BaseType" = '21') AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM RPD1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL SELECT T1."ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", T0."LineStatus", T1."DocStatus", T1."CardCode", T1."CardName", T0."Project", T0."OcrCode", T1."CardCode" AS "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS "ItemCode", T0."Dscription", T0."Quantity", ((T0."LineTotal" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) AS "LineTotal", ((T0."VatSum" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) AS "VatSum", (COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) AS "FretTotal", (COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) AS "FretVat", (COALESCE(T3."ItemFret", 0)) AS "ItemFretTotal", (COALESCE(T3."ItemVatSum", 0)) AS "ItemFretVat", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T1."CardName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM RPD1 T0 INNER JOIN ORPD T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from RPD2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum") T2 ON T0."DocEntry" = "T2"."DocEntry" AND T0."LineNum" = T2."LineNum" LEFT JOIN SDOne_VentilationFret T3 ON T3."ObjType" = 21 AND T3."LineNum" = T0."LineNum" AND T3."DocEntry" = T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') AND T0."LineStatus" = 'C' AND T0."BaseType" = '20' AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM RPD1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL --Achats : Factures SELECT T1."ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", T0."LineStatus", T1."DocStatus", T1."CardCode", T1."CardName", T0."Project", T0."OcrCode", T1."CardCode" AS "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS "ItemCode", T0."Dscription", T0."OpenQty" AS "Quantity", ((T0."LineTotal" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) AS "LineTotal", ((T0."VatSum" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) AS "VatSum", (COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) AS "FretTotal", (COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) AS "FretVat", (COALESCE(T3."ItemFret", 0)) AS "ItemFretTotal", (COALESCE(T3."ItemVatSum", 0)) AS "ItemFretVat", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T1."CardName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM PCH1 T0 INNER JOIN OPCH T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from PCH2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum")T2 ON T0."DocEntry" = "T2"."DocEntry" AND T0."LineNum" = T2."LineNum" LEFT JOIN SDOne_VentilationFret T3 ON T3."ObjType" = 18 AND T3."LineNum" = T0."LineNum" AND T3."DocEntry" = T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM PCH1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL --Achats : Avoirs SELECT CAST(T1."ObjType" AS NVARCHAR(10)) + CASE WHEN T0."BaseType" = '204' THEN '-Acp' ELSE '-' END AS "ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", T0."LineStatus", T1."DocStatus", T1."CardCode", T1."CardName", T0."Project", T0."OcrCode", T1."CardCode" AS "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS "ItemCode", T0."Dscription", T0."OpenQty" AS "Quantity", ((T0."LineTotal" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) AS "LineTotal", ((T0."VatSum" * ((100 - COALESCE(T1."DiscPrcnt", 0)) / 100)) + COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) AS "VatSum", (COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) AS "FretTotal", (COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) AS "FretVat", (COALESCE(T3."ItemFret", 0)) AS "ItemFretTotal", (COALESCE(T3."ItemVatSum", 0)) AS "ItemFretVat", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T1."CardName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM RPC1 T0 INNER JOIN ORPC T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from RPC2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum")T2 ON T0."DocEntry" = "T2"."DocEntry" AND T0."LineNum" = T2."LineNum" LEFT JOIN SDOne_VentilationFret T3 ON T3."ObjType" = 19 AND T3."LineNum" = T0."LineNum" AND T3."DocEntry" = T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM RPC1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL -- Paiements Sortants SELECT CAST(T1."ObjType" AS NVARCHAR(10)) + '-' + CASE WHEN T1."PaidSum" = T1."DocTotal" THEN 'RE' ELSE CASE WHEN "CreateTran" = 'Y' THEN 'F' ELSE 'D' END END AS "ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", T0."LineStatus", T1."DocStatus", T1."CardCode", T1."CardName", T0."Project", T0."OcrCode", T0."U_MG2S_Four" AS "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS "ItemCode", T0."Dscription", CASE WHEN T0."LineStatus" = 'O' AND T1."DocStatus" = 'O' THEN T0."OpenQty" ELSE T0."Quantity" END AS "Quantity", ((T0."LineTotal" + COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * (T1."DpmPrcnt" / 100) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR (T1."PaidSum" <> 0 AND T1."PaidSum" <> T1."DocTotal") THEN T1."DocTotal" - T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) AS "LineTotal", ((T0."VatSum" + COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR (T1."PaidSum" <> 0 AND T1."PaidSum" <> T1."DocTotal") THEN T1."DocTotal" - T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) * (T1."DpmPrcnt" / 100) AS "VatSum", ((COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * (T1."DpmPrcnt" / 100) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR (T1."PaidSum" <> 0 AND T1."PaidSum" <> T1."DocTotal") THEN T1."DocTotal" - T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) AS "FretTotal", ((COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR (T1."PaidSum" <> 0 AND T1."PaidSum" <> T1."DocTotal") THEN T1."DocTotal" - T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) * (T1."DpmPrcnt" / 100) AS "FretVat", ((COALESCE(T3."ItemFret", 0)) * (T1."DpmPrcnt" / 100) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR (T1."PaidSum" <> 0 AND T1."PaidSum" <> T1."DocTotal") THEN T1."DocTotal" - T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) AS "ItemFret", ((COALESCE(T3."ItemVatSum", 0)) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR (T1."PaidSum" <> 0 AND T1."PaidSum" <> T1."DocTotal") THEN T1."DocTotal" - T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) * (T1."DpmPrcnt" / 100) AS "ItemVatSum", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T0."U_MG2S_FourName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM DPO1 T0 INNER JOIN ODPO T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from DPO2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum") T2 ON T0."DocEntry" = "T2"."DocEntry" AND T0."LineNum" = T2."LineNum" LEFT JOIN SDOne_VentilationFret T3 ON T3."ObjType" = 204 AND T3."LineNum" = T0."LineNum" AND T3."DocEntry" = T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM DPO1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL SELECT CAST(T1."ObjType" AS NVARCHAR(10)) + '-' + CASE WHEN T1."PaidSum" <> 0 THEN 'RE' ELSE CASE WHEN "CreateTran" = 'Y' THEN 'F' ELSE 'D' END END AS "ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", 'C' AS "LineStatus", 'C' AS "DocStatus", T1."CardCode", T1."CardName", T0."Project", T0."OcrCode", T0."U_MG2S_Four" AS "Fournisseur", CASE WHEN T1."DocType" = 'S' THEN T0."AcctCode" ELSE T0."ItemCode" END AS "ItemCode", T0."Dscription", CASE WHEN T0."LineStatus" = 'O' AND T1."DocStatus" = 'O' THEN T0."OpenQty" ELSE T0."Quantity" END AS "Quantity", ((T0."LineTotal" + COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * (T1."DpmPrcnt" / 100) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR T1."PaidSum" <> 0 THEN T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) AS "LineTotal", ((T0."VatSum" + COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR T1."PaidSum" <> 0 THEN T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) * (T1."DpmPrcnt" / 100) AS "VatSum", ((COALESCE(T2."LineTotal", 0) + COALESCE(T3."FretTotal", 0)) * (T1."DpmPrcnt" / 100) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR T1."PaidSum" <> 0 THEN T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) AS "FretTotal", ((COALESCE(T2."VatSum", 0) + COALESCE(T3."VatSum", 0)) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR T1."PaidSum" <> 0 THEN T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) * (T1."DpmPrcnt" / 100) AS "FretVatSum", ((COALESCE(T3."ItemFret", 0)) * (T1."DpmPrcnt" / 100) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR T1."PaidSum" <> 0 THEN T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) AS "ItemFret", ((COALESCE(T3."ItemVatSum", 0)) * (CASE WHEN (T0."LineStatus" = 'O' AND T1."DocStatus" = 'O') OR T1."PaidSum" <> 0 THEN T1."PaidSum" ELSE T1."DocTotal" END / CASE WHEN T1."DocTotal" = 0 THEN 1 ELSE T1."DocTotal" END)) * (T1."DpmPrcnt" / 100) AS "ItemVatSum", T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T0."U_MG2S_FourName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) "SpecificRegr" FROM DPO1 T0 INNER JOIN ODPO T1 ON T0."DocEntry" = T1."DocEntry" LEFT JOIN (SELECT "DocEntry", "LineNum", SUM("LineTotal")"LineTotal", SUM("VatSum")"VatSum" from DPO2 T1 INNER JOIN OEXD T0 ON T0."ExpnsCode"= T1."ExpnsCode" and T0."U_MG2S_SD1_Include"='Y' GROUP BY "DocEntry", "LineNum")T2 ON T0."DocEntry" = "T2"."DocEntry" AND T0."LineNum" = T2."LineNum" LEFT JOIN SDOne_VentilationFret T3 ON T3."ObjType" = 204 AND T3."LineNum" = T0."LineNum" AND T3."DocEntry" = T0."DocEntry" LEFT JOIN OITM ON T0."ItemCode" = OITM."ItemCode" WHERE (T1."CANCELED" = 'N') AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') AND T1."PaidSum" <> 0 AND T1."PaidSum" <> T1."DocTotal" AND (COALESCE(NULLIF(T0."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' OR NOT EXISTS (SELECT 1 FROM DPO1 T10 LEFT JOIN OITM ON T10."ItemCode" = OITM."ItemCode" WHERE T10."DocEntry"=T0."DocEntry" AND COALESCE(NULLIF(T10."U_MG2S_SD1_FraisVent",'-'), OITM."U_MG2S_SD1_FraisVent")='N' )) UNION ALL --Paiements Entrants SELECT "ObjType", "DocEntry", "DocNum", "DocDate", 0 AS "LineNum", 'C' AS "LineStatus", 'C' AS "DocStatus", "CardCode", "CardName", "PrjCode" AS "Project", '' AS "OcrCode", '' AS "Fournisseur", '' AS "ItemCode", "JrnlMemo" AS "Dscription", 1 AS "Quantity", "NoDocSum" AS "LineTotal", 0 AS "VatSum", 0,0,0,0, "U_MG2S_Dossier", 'Y' AS "Confirmed", NULL AS "WhsCode", NULL AS "U_MG2S_FourName", NULL AS "GrossBuyPr", NULL AS "U_MG2S_BaseAchat", NULL AS "U_MG2S_BaseTime", NULL AS "SpecificRegr" FROM ORCT T1 WHERE ("Canceled" = 'N') AND (COALESCE("U_MG2S_Dossier", '') <> '') AND (COALESCE("U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') UNION ALL --Paiements Sortants SELECT "ObjType", "DocEntry", "DocNum", "DocDate", 0 AS "LineNum", 'C' AS "LineStatus", 'C' AS "DocStatus", "CardCode", "CardName", "PrjCode" AS "Project", '' AS "OcrCode", '' AS "Fournisseur", '' AS "ItemCode", "JrnlMemo" AS "Dscription", 1 AS "Quantity", "NoDocSum" AS "LineTotal", 0 AS "VatSum", 0,0,0,0, "U_MG2S_Dossier", 'Y' AS "Confirmed", NULL AS "WhsCode", NULL AS "U_MG2S_FourName", NULL AS "GrossBuyPr", NULL AS "U_MG2S_BaseAchat", NULL AS "U_MG2S_BaseTime", NULL AS "SpecificRegr" FROM OVPM T1 WHERE ("Canceled" = 'N') AND (COALESCE("U_MG2S_Dossier", '') <> '') AND (COALESCE("U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') UNION ALL --Stocks : Entr�e de marchandises SELECT T1."ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", 'C' AS "LineStatus", 'C' AS "DocStatus", '' AS "CardCode", '' AS "CardName", T0."Project", T0."OcrCode", T0."U_MG2S_Four" AS "Fournisseur", T0."ItemCode", T0."Dscription", T0."Quantity", T0."LineTotal", 0 AS "VatSum", 0,0,0,0, T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T0."U_MG2S_FourName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) AS "SpecificRegr" FROM IGN1 T0 INNER JOIN OIGN T1 ON T0."DocEntry" = T1."DocEntry" WHERE (T1."CANCELED" = 'N') AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') UNION ALL --Stocks : Sorties de marchandises SELECT T1."ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", 'C' AS "LineStatus", 'C' AS "DocStatus", '' AS "CardCode", '' AS "CardName", T0."Project", T0."OcrCode", T0."U_MG2S_Four" AS "Fournisseur", T0."ItemCode", T0."Dscription", T0."Quantity", T0."LineTotal", 0 AS "VatSum", 0,0,0,0, T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T0."U_MG2S_FourName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) AS "SpecificRegr" FROM IGE1 T0 INNER JOIN OIGE T1 ON T0."DocEntry" = T1."DocEntry" WHERE (T1."CANCELED" = 'N') AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') UNION ALL --Stocks : Transferts de stocks SELECT T1."ObjType", T1."DocEntry", T1."DocNum", T1."DocDate", T0."LineNum", 'C' AS "LineStatus", 'C' AS "DocStatus", '' AS "CardCode", '' AS "CardName", T0."Project", T0."OcrCode", T0."U_MG2S_Four" AS "Fournisseur", T0."ItemCode", T0."Dscription", T0."Quantity", T0."LineTotal", 0 AS "VatSum", 0,0,0,0, T0."U_MG2S_Dossier", T1."Confirmed", T0."WhsCode", T0."U_MG2S_FourName", T0."GrossBuyPr", T0."U_MG2S_BaseAchat", T0."U_MG2S_BaseTime", CAST('' AS NVARCHAR(100)) AS "SpecificRegr" FROM WTR1 T0 INNER JOIN OWTR T1 ON T0."DocEntry" = T1."DocEntry" WHERE (T1."CANCELED" = 'N') AND (COALESCE(T0."U_MG2S_Dossier", '') <> '') AND (COALESCE(T0."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') UNION ALL --SD-One : ValConso SELECT '-1' AS "ObjType", 0 AS "DocEntry", 0 AS "DocNum", "v"."DocDate", 0 AS "LineNum", 'O' AS "LineStatus", 'O' AS "DocStatus", '' AS "CardCode", '' AS "CardName", '' AS "Project", '' AS "OcrCode", '' AS "Fournisseur", "v"."ItemCode", T3."ItemName" AS "Dscription", SUM("v"."Quantity") AS "Quantity", SUM("v"."Quantity" * "v"."Valeur") AS "LineTotal", 0 AS "VatSum", 0,0,0,0, "v"."U_MG2S_Dossier", 'Y' AS "Confirmed", NULL AS "WhsCode", NULL AS "U_MG2S_FourName", NULL AS "GrossBuyPr", NULL AS "U_MG2S_BaseAchat", NULL AS "U_MG2S_BaseTime", NULL AS "SpecificRegr" FROM "MG2S_SDOne_ValConso"(@ParamDossier) "v" INNER JOIN OITM T3 ON T3."ItemCode" = "v"."ItemCode" WHERE (T3."InvntItem" = 'Y') AND (COALESCE("U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') GROUP BY "v"."ItemCode", T3."ItemName", "v"."DocDate", "v"."U_MG2S_Dossier" HAVING (SUM("v"."Quantity") > 0) UNION ALL --SD-One : Temps SELECT '-2' AS "ObjType", T1."ClgCode" AS "DocEntry", T1."ClgCode" AS "DocNum", T1."Recontact" AS "DocDate", 0 AS "LineNum", CASE WHEN "Closed" = 'N' THEN 'O' ELSE 'C' END AS "LineStatus", CASE WHEN "Closed" = 'N' THEN 'O' ELSE 'C' END AS "DocStatus", T1."CardCode", '' AS "CardName", '' AS "Project", '' AS "OcrCode", '' AS "Fournisseur", '' AS "ItemCode", '' AS "Dscription", CASE WHEN T3."U_Trajet" = 'Y' THEN T1."U_MG2S_Trajet" ELSE 0 END + CASE WHEN T3."U_Effectif" = 'Y' THEN T1."U_MG2S_Effectif" ELSE 0 END + CASE WHEN T3."U_Pause" = 'Y' THEN T1."U_MG2S_Pause" ELSE 0 END AS "Quantity", (CASE WHEN T3."U_Trajet" = 'Y' THEN T1."U_MG2S_Trajet" ELSE 0 END + CASE WHEN T3."U_Effectif" = 'Y' THEN T1."U_MG2S_Effectif" ELSE 0 END + CASE WHEN T3."U_Pause" = 'Y' THEN T1."U_MG2S_Pause" ELSE 0 END) * T1."U_MG2S_Taux" AS "LineTotal", 0 AS "VatSum", 0,0,0,0, T1."U_MG2S_Dossier", 'Y' AS "Confirmed", NULL AS "WhsCode", NULL AS "U_MG2S_FourName", NULL AS "GrossBuyPr", NULL AS "U_MG2S_BaseAchat", NULL AS "U_MG2S_BaseTime", NULL AS "SpecificRegr" FROM OCLG T1 INNER JOIN "@MG2S_DOSSIER" T2 ON T1."U_MG2S_Dossier" = T2."Code" INNER JOIN "@MG2S_PARAMTEMPS" T3 ON T2."U_TypeDossier" = T3."U_TypeDossier" WHERE (COALESCE(T1."U_MG2S_Dossier", '') = @ParamDossier OR @ParamDossier = '') UNION ALL --SD-One : Frais SELECT '-3' AS "ObjType", "Code" AS "DocEntry", "Code" AS "DocNum", "U_Date" AS "DocDate", 0 AS "LineNum", 'O' AS "LineStatus", 'O' AS "DocStatus", "U_Descriptif" AS "CardCode", '' AS "CardName", '' AS "Project", '' AS "OcrCode", '' AS "Fournisseur", "U_Type" AS "ItemCode", '' AS "Dscription", 1 AS "Quantity", "U_Prix" AS "LineTotal", 0 AS "VatSum", 0,0,0,0, "U_Dossier" AS "U_MG2S_Dossier", 'Y' AS "Confirmed", NULL AS "WhsCode", NULL AS "U_MG2S_FourName", NULL AS "GrossBuyPr", NULL AS "U_MG2S_BaseAchat", NULL AS "U_MG2S_BaseTime", NULL AS "SpecificRegr" FROM "@MG2S_FRAIS" WHERE (COALESCE("U_Dossier", '') = @ParamDossier OR @ParamDossier = '') ) "v" LEFT JOIN OCRD T1 ON T1."CardCode" = "v"."CardCode" AND COALESCE("v"."CardName", '') = '' INNER JOIN "@MG2S_DOSSIER" D on "v"."U_MG2S_Dossier" = D.Name