SELECT '1' AS C0, 'H' AS C1, 1 AS ORDERBY, CASE WHEN (EXISTS (SELECT 1 FROM LIGNE WHERE GL_QTEFACT < 0 AND GL_NATUREPIECEG = GP_NATUREPIECEG AND GL_SOUCHE = GP_SOUCHE AND GL_NUMERO = GP_NUMERO AND GL_INDICEG = GP_INDICEG AND GL_TYPELIGNE = 'ART' AND GL_TYPEARTICLE = 'MAR') AND (SELECT COUNT(MLR_REMISE) FROM LIGNEREMISE WHERE MLR_NATUREPIECEG = GP_NATUREPIECEG AND MLR_SOUCHE = GP_SOUCHE AND MLR_NUMERO = GP_NUMERO AND MLR_INDICEG = GP_INDICEG) = 0) THEN 'NORMAL RETURN' WHEN (EXISTS (SELECT 1 FROM LIGNE WHERE GL_QTEFACT < 0 AND GL_NATUREPIECEG = GP_NATUREPIECEG AND GL_SOUCHE = GP_SOUCHE AND GL_NUMERO = GP_NUMERO AND GL_INDICEG = GP_INDICEG AND GL_TYPELIGNE = 'ART' AND GL_TYPEARTICLE = 'MAR') AND (SELECT COUNT(MLR_REMISE) FROM LIGNEREMISE WHERE MLR_NATUREPIECEG = GP_NATUREPIECEG AND MLR_SOUCHE = GP_SOUCHE AND MLR_NUMERO = GP_NUMERO AND MLR_INDICEG = GP_INDICEG) <> 0) THEN 'DISCOUNT RETURN' WHEN ((SELECT COUNT(GL_NUMLIGNE) FROM LIGNE WHERE GP_SOUCHE = GL_SOUCHE AND GP_NUMERO = GL_NUMERO AND GP_NATUREPIECEG = GL_NATUREPIECEG AND GL_TYPELIGNE = 'ART' AND GL_TYPEARTICLE = 'MAR' AND GL_QTEFACT < 0) >= 1 AND (SELECT COUNT(GL_NUMLIGNE) FROM LIGNE WHERE GP_SOUCHE = GL_SOUCHE AND GP_NUMERO = GL_NUMERO AND GP_NATUREPIECEG = GL_NATUREPIECEG AND GL_TYPELIGNE = 'ART' AND GL_TYPEARTICLE = 'MAR' AND GL_QTEFACT > 0) >= 1 AND (SELECT COUNT(MLR_REMISE) FROM LIGNEREMISE WHERE MLR_NATUREPIECEG = GP_NATUREPIECEG AND MLR_SOUCHE = GP_SOUCHE AND MLR_NUMERO = GP_NUMERO AND MLR_INDICEG = GP_INDICEG) <> 0) THEN 'DISCOUNT EXCHANGE' WHEN ((SELECT COUNT(GL_NUMLIGNE) FROM LIGNE WHERE GP_SOUCHE = GL_SOUCHE AND GP_NUMERO = GL_NUMERO AND GP_NATUREPIECEG = GL_NATUREPIECEG AND GL_TYPELIGNE = 'ART' AND GL_TYPEARTICLE = 'MAR' AND GL_QTEFACT < 0) >= 1 AND (SELECT COUNT(GL_NUMLIGNE) FROM LIGNE WHERE GP_SOUCHE = GL_SOUCHE AND GP_NUMERO = GL_NUMERO AND GP_NATUREPIECEG = GL_NATUREPIECEG AND GL_TYPELIGNE = 'ART' AND GL_TYPEARTICLE = 'MAR' AND GL_QTEFACT > 0) >= 1 AND (SELECT COUNT(MLR_REMISE) FROM LIGNEREMISE WHERE MLR_NATUREPIECEG = GP_NATUREPIECEG AND MLR_SOUCHE = GP_SOUCHE AND MLR_NUMERO = GP_NUMERO AND MLR_INDICEG = GP_INDICEG) = 0) THEN 'NORMAL EXCHANGE' WHEN ((SELECT COUNT(GL_NUMLIGNE) FROM LIGNE WHERE GP_SOUCHE = GL_SOUCHE AND GP_NUMERO = GL_NUMERO AND GP_NATUREPIECEG = GL_NATUREPIECEG AND GL_TYPELIGNE = 'ART' AND GL_TYPEARTICLE = 'MAR' AND GL_QTEFACT < 0) = 0 AND (SELECT COUNT(GL_NUMLIGNE) FROM LIGNE WHERE GP_SOUCHE = GL_SOUCHE AND GP_NUMERO = GL_NUMERO AND GP_NATUREPIECEG = GL_NATUREPIECEG AND GL_TYPELIGNE = 'ART' AND GL_TYPEARTICLE = 'MAR' AND GL_QTEFACT > 0) >= 1 AND (SELECT COUNT(MLR_REMISE) FROM LIGNEREMISE WHERE MLR_NATUREPIECEG = GP_NATUREPIECEG AND MLR_SOUCHE = GP_SOUCHE AND MLR_NUMERO = GP_NUMERO AND MLR_INDICEG = GP_INDICEG) = 0) THEN 'NORMAL SALE' WHEN ((SELECT COUNT(GL_NUMLIGNE) FROM LIGNE WHERE GP_SOUCHE = GL_SOUCHE AND GP_NUMERO = GL_NUMERO AND GP_NATUREPIECEG = GL_NATUREPIECEG AND GL_TYPELIGNE = 'ART' AND GL_TYPEARTICLE = 'MAR' AND GL_QTEFACT < 0) = 0 AND (SELECT COUNT(GL_NUMLIGNE) FROM LIGNE WHERE GP_SOUCHE = GL_SOUCHE AND GP_NUMERO = GL_NUMERO AND GP_NATUREPIECEG = GL_NATUREPIECEG AND GL_TYPELIGNE = 'ART' AND GL_TYPEARTICLE = 'MAR' AND GL_QTEFACT > 0) >= 1 AND (SELECT COUNT(MLR_REMISE) FROM LIGNEREMISE WHERE MLR_NATUREPIECEG = GP_NATUREPIECEG AND MLR_SOUCHE = GP_SOUCHE AND MLR_NUMERO = GP_NUMERO AND MLR_INDICEG = GP_INDICEG) <> 0) THEN 'NORMAL DISCOUNT' ELSE 'OTHER' END AS TYPE_VENTE, CAST(CASE WHEN ISNULL(YTC_TABLELIBRETIERS2, '') <> '' THEN 'YES' ELSE 'NO' END AS VARCHAR(50)) AS IDENTIFIED_CUSTOMER, CAST((SELECT COUNT(GL_ARTICLE) FROM LIGNE LEFT JOIN ZLIGNESQRCODE ON ZLQ_NATUREPIECEG = GL_NATUREPIECEG AND ZLQ_SOUCHE = GL_SOUCHE AND ZLQ_NUMERO = GL_NUMERO AND ZLQ_INDICEG = GL_INDICEG AND ZLQ_NUMLIGNE = GL_NUMLIGNE LEFT JOIN ZQRCODEITEMS ON ZBA_QRCODE = ZLQ_QRCODE WHERE GL_NATUREPIECEG = GP_NATUREPIECEG AND GL_SOUCHE = GP_SOUCHE AND GL_NUMERO = GP_NUMERO AND GL_INDICEG = GP_INDICEG AND GL_TYPELIGNE = 'ART' AND GL_TYPEARTICLE = 'MAR') AS VARCHAR(50)) AS NB_SALESLINESV2, CAST((SELECT COUNT(DISTINCT GL_ARTICLE) FROM LIGNE WHERE GL_NATUREPIECEG = GP_NATUREPIECEG AND GL_SOUCHE = GP_SOUCHE AND GL_NUMERO = GP_NUMERO AND GL_INDICEG = GP_INDICEG AND GL_TYPELIGNE = 'ART' AND GL_TYPEARTICLE = 'MAR') AS VARCHAR(50)) AS NB_TAXLINES, CAST((SELECT COUNT(DISTINCT GPE_MODEPAIE) FROM PIEDECHE WHERE GP_NATUREPIECEG = GPE_NATUREPIECEG AND GP_SOUCHE = GPE_SOUCHE AND GP_NUMERO = GPE_NUMERO AND GP_INDICEG = GPE_INDICEG AND GPE_MODEPAIE <> '004') AS VARCHAR(50)) AS NB_PYAMENT, (SELECT COUNT(MLR_REMISE) FROM LIGNEREMISE WHERE MLR_NATUREPIECEG = GP_NATUREPIECEG AND MLR_SOUCHE = GP_SOUCHE AND MLR_NUMERO = GP_NUMERO AND MLR_INDICEG = GP_INDICEG) AS NB_DISCOUNT, CAST(ET_FILIALE AS VARCHAR(50)) AS DATAAREAID, CAST(GP_REFINTERNE AS VARCHAR(50)) AS TRANSACTIONNUMBER, CAST(ET_CHARLIBRE1 AS VARCHAR(50)) AS OPERATINGUNITNUMBER, CAST(GP_ETABLISSEMENT AS VARCHAR(50)) AS TERMINAL, CAST(GP_DTSVCREATE AS VARCHAR(50)) AS TRANSACTIONDATE, CAST(CASE WHEN ISNULL(YTC_TABLELIBRETIERS2, '') <> '' THEN GP_TIERS ELSE '' END AS VARCHAR(50)) AS H_CUSTOMERACCOUNT, CAST(GP_NUMERO AS VARCHAR(50)) AS H_RRECEIPTID, CAST(GP_HEURECREATION AS VARCHAR(50)) AS H_BUSINESSDATE, 'SALESORDER' AS H_TRANSACTIONORDERTYPE, 'SALES' AS H_TRANSACTIONTYPE, CAST(YX4.YX_LIBELLE AS VARCHAR(50)) AS H_TAXCALCULATIONTYPE, CASE WHEN EXISTS (SELECT 1 FROM LIGNE L WHERE L.GL_QTEFACT < 0 AND L.GL_NATUREPIECEG = GP_NATUREPIECEG AND L.GL_SOUCHE = GP_SOUCHE AND L.GL_NUMERO = GP_NUMERO AND L.GL_INDICEG = GP_INDICEG) THEN 'YES' ELSE 'NO' END AS H_SALEISRETURNSALE, CAST(GP_REPRESENTANT AS VARCHAR(50)) AS H_STAFF, 'EN-US' AS H_LANGUAGEID, CAST(GP_DEVISE AS VARCHAR(50)) AS H_CURRENCY, '100' AS H_EXCHANGERATE, CAST(GDE_CHARLIBRE1 AS VARCHAR(50)) AS H_WAREHOUSE, CAST((SELECT COUNT(DISTINCT L1.GL_ARTICLE) FROM LIGNE L1 WHERE L1.GL_NATUREPIECEG = GP_NATUREPIECEG AND L1.GL_SOUCHE = GP_SOUCHE AND L1.GL_NUMERO = GP_NUMERO AND L1.GL_INDICEG = GP_INDICEG AND L1.GL_TYPELIGNE = 'ART' AND L1.GL_TYPEARTICLE = 'MAR') AS VARCHAR(50)) AS H_NUMBEROFITEMLINES, CAST((SELECT SUM(ABS(ISNULL(L2.GL_QTEFACT, 0))) FROM LIGNE L2 WHERE L2.GL_NATUREPIECEG = GP_NATUREPIECEG AND L2.GL_SOUCHE = GP_SOUCHE AND L2.GL_NUMERO = GP_NUMERO AND L2.GL_INDICEG = GP_INDICEG AND L2.GL_TYPELIGNE = 'ART' AND L2.GL_TYPEARTICLE = 'MAR') AS VARCHAR(50)) AS H_NUMBEROFITEMS, CAST((SELECT COUNT(DISTINCT GPE_MODEPAIE) FROM PIEDECHE WHERE GP_NATUREPIECEG = GPE_NATUREPIECEG AND GP_SOUCHE = GPE_SOUCHE AND GP_NUMERO = GPE_NUMERO AND GP_INDICEG = GPE_INDICEG AND GPE_MODEPAIE <> '004') AS VARCHAR(50)) AS H_NUMBEROFPAYMENTLINES, CAST((SELECT SUM(-1 * L.GL_MONTANTHT) FROM LIGNE L WHERE L.GL_NATUREPIECEG = GP_NATUREPIECEG AND L.GL_SOUCHE = GP_SOUCHE AND L.GL_NUMERO = GP_NUMERO AND L.GL_INDICEG = GP_INDICEG) AS VARCHAR(50)) AS H_NETPRICE, CAST(-1 * GP_TOTALHT AS VARCHAR(50)) AS H_NETAMOUNT, CAST(-1 * GP_TOTALTTC AS VARCHAR(50)) AS H_GROSSAMOUNT, CAST((SELECT SUM(ABS(MLR_MONTANTHT)) FROM LIGNEREMISE WHERE MLR_NATUREPIECEG = GP_NATUREPIECEG AND MLR_SOUCHE = GP_SOUCHE AND MLR_NUMERO = GP_NUMERO AND MLR_INDICEG = GP_INDICEG) AS VARCHAR(50)) AS H_DISCOUNTAMOUNT, CAST((SELECT SUM(ABS(MLR_MONTANTHT)) FROM LIGNEREMISE WHERE MLR_NATUREPIECEG = GP_NATUREPIECEG AND MLR_SOUCHE = GP_SOUCHE AND MLR_NUMERO = GP_NUMERO AND MLR_INDICEG = GP_INDICEG) AS VARCHAR(50)) AS H_DISCOUNTAMOUNTWITHOUTTAX, CAST((SELECT SUM(ABS(MLR_MONTANTHT)) FROM LIGNEREMISE WHERE MLR_NATUREPIECEG = GP_NATUREPIECEG AND MLR_SOUCHE = GP_SOUCHE AND MLR_NUMERO = GP_NUMERO AND MLR_INDICEG = GP_INDICEG) AS VARCHAR(50)) AS H_TOTALDISCOUNTAMOUNT, '0.00' AS H_TOTALMANUALDISCOUNTAMOUNT, CAST((SELECT TOP 1 ABS(MLR_REMISE) FROM LIGNEREMISE WHERE MLR_NATUREPIECEG = GP_NATUREPIECEG AND MLR_SOUCHE = GP_SOUCHE AND MLR_NUMERO = GP_NUMERO AND MLR_INDICEG = GP_INDICEG) AS VARCHAR(50)) AS H_TOTALMANUALDISCOUNTPERCENTAGE, CAST((SELECT SUM(GPE_MONTANTECHE) FROM PIEDECHE WHERE GP_NATUREPIECEG = GPE_NATUREPIECEG AND GP_SOUCHE = GPE_SOUCHE AND GP_NUMERO = GPE_NUMERO AND GP_INDICEG = GPE_INDICEG) AS VARCHAR(50)) AS H_PAYMENTAMOUNT, CAST(GP_TOTALTTC AS VARCHAR(50)) AS H_AMOUNTPOSTEDTOACCOUNT, '' AS L_LINENUMBER, '' AS L_ORIGINALITEMSALESTAXGROUP, '' AS L_NETPRICE, '' AS L_CATEGORYHIERARCHYNAME, '' AS L_UNIT, '' AS L_CURRENCY, '' AS L_NETAMOUNTINCLUSIVETAX, '' AS L_NETAMOUNT, '' AS L_UNITQUANTITY, '' AS L_BUSINESSDATE, '' AS L_CATEGORYNAME, '' AS L_KEYBOARDPRODUCTENTRY, '' AS L_ITEMID, '' AS L_WAREHOUSE, '' AS L_IVENTBATCHID, '' AS L_SALESTAXAMOUNT, '' AS L_QUANTITY, '' AS L_PRICE, '' AS L_SALESTAXGROUP, '' AS L_ORIGINALSALESTAXGROUP, '' AS L_ITEMSALESTAXGROUP, '' AS L_DISCOUNTAMOUNTWITHOUTTAX, '' AS L_TOTALDISCOUNT, '' AS L_TOTALDISCOUNTPERCENTAGE, '' AS L_CASHDISCOUNTAMOUNT, '' AS L_LINEDISCOUNT, '' AS L_LINEMANUALDISCOUNTAMOUNT, '' AS L_LINEMANUALDISCOUNTPERCENTAGE, '' AS L_ISLINEDISCOUNTED, '' AS L_ISRETURNNOSALE, '' AS L_RETURNOPERATINGUNITNUMBER, '' AS T_SALESLINENUMBER, '' AS T_TAXCODE, '' AS T_TAXPERCENTAGE, '' AS T_TAXAMOUNT, '' AS P_LINENUMBER, '' AS P_STORE, '' AS P_RECEIPTID, '' AS P_TENDERTYPE, '' AS P_AMOUNTTENDERED, '' AS P_AMOUNTINTENDEREDCURRENCY, '' AS P_AMOUNTINACCOUNTINGCURRENCY, '' AS P_REFUNDABLEAMOUNT, '' AS P_AMOUNTTENDEREDADJUSTMENT, '' AS P_LINKEDPAYMENTLINENUMBER, '' AS P_QUANTITY, '' AS P_CURRENCYCODE, '' AS P_EXCHANGERATEINTENDEREDCURRENCY, '' AS P_EXCHANGERATEINACCOUNTINGCURRENCY, '' AS P_TRANSACTIONSTATUS, '' AS P_STAFF, '' AS P_ISPREPAYMENT, '' AS P_ISCHANGELINE, '' AS P_ISLINKEDREFUND, '' AS P_ISPAYMENTCAPTURED, '' AS D_SALESLINENUMBER, '' AS D_LINENUMBER, '' AS D_DISCOUNTCOST, '' AS D_DISCOUNTORIGINTYPE, '' AS D_MANUALDISCOUNTTYPE, '' AS D_DISCOUNTPERCENTAGE, '' AS D_CUSTOMERDISCOUNTTYPE, '' AS D_DEALPRICE, '' AS D_DISCOUNTAMOUNT, '' AS D_EFFECTIVEAMOUNT, CAST(GP_NATUREPIECEG AS VARCHAR(50)) AS C38, CAST(GP_SOUCHE AS VARCHAR(50)) AS C39, CAST(GP_NUMERO AS VARCHAR(50)) AS C40, CAST(GP_INDICEG AS VARCHAR(50)) AS C41, CAST(GP_ETATEXPORT AS VARCHAR(50)) AS C42, CAST(GP_REFINTERNE AS VARCHAR(50)) AS C44 FROM PIECE LEFT JOIN ETABLISS ON GP_ETABLISSEMENT = ET_ETABLISSEMENT LEFT JOIN CHOIXEXT YX4 ON ET_LIBREET4 = YX4.YX_CODE AND YX4.YX_TYPE = 'EL4' LEFT JOIN DEPOTS ON GDE_DEPOT = GP_DEPOT LEFT JOIN TIERS ON T_TIERS = GP_TIERS AND T_NATUREAUXI = 'CLI' LEFT JOIN TIERSCOMPL ON YTC_AUXILIAIRE = T_AUXILIAIRE AND YTC_TIERS = GP_TIERS WHERE GP_NATUREPIECEG = 'FFO' AND GP_TICKETANNULE = '-' AND GP_VIVANTE = 'X' AND GP_SUPPRIME <> 'X' AND 'MAR' IN (SELECT GL_TYPEARTICLE FROM LIGNE WHERE GL_NUMERO = GP_NUMERO AND GL_SOUCHE = GP_SOUCHE AND GL_INDICEG = GP_INDICEG AND GL_NATUREPIECEG = GP_NATUREPIECEG) AND 'ART' IN (SELECT GL_TYPELIGNE FROM LIGNE WHERE GL_NUMERO = GP_NUMERO AND GL_SOUCHE = GP_SOUCHE AND GL_INDICEG = GP_INDICEG AND GL_NATUREPIECEG = GP_NATUREPIECEG) AND '004' NOT IN (SELECT GPE_MODEPAIE FROM PIEDECHE WHERE GP_NATUREPIECEG = GPE_NATUREPIECEG AND GP_SOUCHE = GPE_SOUCHE AND GP_NUMERO = GPE_NUMERO AND GP_INDICEG = GPE_INDICEG) AND GP_ETATEXPORT <> 'EXP' AND GP_REFINTERNE IN ('0050001610001', '0050001610002') UNION ALL SELECT '2' AS C0, 'L' AS C1, 2 AS ORDERBY, '' AS TYPE_VENTE, '' AS IDENTIFIED_CUSTOMER, '' AS NB_SALESLINESV2, '' AS NB_TAXLINES, '' AS NB_PYAMENT, '' AS NB_DISCOUNT, CAST(ET_FILIALE AS VARCHAR(50)) AS DATAAREAID, CAST(GP_REFINTERNE AS VARCHAR(50)) AS TRANSACTIONNUMBER, CAST(ET_CHARLIBRE1 AS VARCHAR(50)) AS OPERATINGUNITNUMBER, CAST(GP_ETABLISSEMENT AS VARCHAR(50)) AS TERMINAL, CAST(GP_DTSVCREATE AS VARCHAR(50)) AS TRANSACTIONDATE, '' AS H_CUSTOMERACCOUNT, '' AS H_RRECEIPTID, '' AS H_BUSINESSDATE, '' AS H_TRANSACTIONORDERTYPE, '' AS H_TRANSACTIONTYPE, '' AS H_TAXCALCULATIONTYPE, '' AS H_SALEISRETURNSALE, '' AS H_STAFF, '' AS H_LANGUAGEID, '' AS H_CURRENCY, '' AS H_EXCHANGERATE, '' AS H_WAREHOUSE, '' AS H_NUMBEROFITEMLINES, '' AS H_NUMBEROFITEMS, '' AS H_NUMBEROFPAYMENTLINES, '' AS H_NETPRICE, '' AS H_NETAMOUNT, '' AS H_GROSSAMOUNT, '' AS H_DISCOUNTAMOUNT, '' AS H_DISCOUNTAMOUNTWITHOUTTAX, '' AS H_TOTALDISCOUNTAMOUNT, '' AS H_TOTALMANUALDISCOUNTAMOUNT, '' AS H_TOTALMANUALDISCOUNTPERCENTAGE, '' AS H_PAYMENTAMOUNT, '' AS H_AMOUNTPOSTEDTOACCOUNT, CAST(GL_NUMLIGNE AS VARCHAR(50)) AS L_LINENUMBER, 'FULL' AS L_ORIGINALITEMSALESTAXGROUP, CAST(((GL_QTEFACT * GL_PUHT) * (-1)) AS VARCHAR(50)) AS L_NETPRICE, 'RETAIL CATGOREY' AS L_CATEGORYHIERARCHYNAME, 'EA' AS L_UNIT, CAST(GL_DEVISE AS VARCHAR(50)) AS L_CURRENCY, CAST((GL_TOTALHT * (-1)) AS VARCHAR(50)) AS L_NETAMOUNTINCLUSIVETAX, CAST((GL_TOTALHT * (-1)) AS VARCHAR(50)) AS L_NETAMOUNT, CAST((GL_QTEFACT * (-1)) AS VARCHAR(50)) AS L_UNITQUANTITY, CAST(GP_HEURECREATION AS VARCHAR(50)) AS L_BUSINESSDATE, 'RETAIL CATGOREY' AS L_CATEGORYNAME, 'YES' AS L_KEYBOARDPRODUCTENTRY, CAST(GL_CODEARTICLE AS VARCHAR(50)) AS L_ITEMID, CAST(GDE_CHARLIBRE1 AS VARCHAR(50)) AS L_WAREHOUSE, CAST(ZBA_BATCHNUMBER AS VARCHAR(50)) AS L_IVENTBATCHID, CAST((GL_TOTALTAXE1 * (-1)) AS VARCHAR(50)) AS L_SALESTAXAMOUNT, CAST((GL_QTEFACT * (-1)) AS VARCHAR(50)) AS L_QUANTITY, CAST((GL_PUHT * (-1)) AS VARCHAR(50)) AS L_PRICE, CAST(YX1.YX_LIBELLE AS VARCHAR(50)) AS L_SALESTAXGROUP, CAST(YX1.YX_LIBELLE AS VARCHAR(50)) AS L_ORIGINALSALESTAXGROUP, 'FULL' AS L_ITEMSALESTAXGROUP, CAST((SELECT ABS(SUM(MLR_MONTANTHT)) FROM LIGNEREMISE WHERE MLR_NATUREPIECEG = GL_NATUREPIECEG AND MLR_SOUCHE = GL_SOUCHE AND MLR_NUMERO = GL_NUMERO AND MLR_INDICEG = GL_INDICEG) AS VARCHAR(50)) AS L_DISCOUNTAMOUNTWITHOUTTAX, CAST((SELECT ABS(SUM(MLR_MONTANTHT)) FROM LIGNEREMISE WHERE MLR_NATUREPIECEG = GL_NATUREPIECEG AND MLR_SOUCHE = GL_SOUCHE AND MLR_NUMERO = GL_NUMERO AND MLR_INDICEG = GL_INDICEG) AS VARCHAR(50)) AS L_TOTALDISCOUNT, CAST((SELECT TOP 1 ABS(MLR_REMISE) FROM LIGNEREMISE WHERE MLR_NATUREPIECEG = GL_NATUREPIECEG AND MLR_SOUCHE = GL_SOUCHE AND MLR_NUMERO = GL_NUMERO AND MLR_INDICEG = GL_INDICEG) AS VARCHAR(50)) AS L_TOTALDISCOUNTPERCENTAGE, CAST((SELECT ABS(SUM(MLR_MONTANTHT)) FROM LIGNEREMISE WHERE MLR_NATUREPIECEG = GL_NATUREPIECEG AND MLR_SOUCHE = GL_SOUCHE AND MLR_NUMERO = GL_NUMERO AND MLR_INDICEG = GL_INDICEG) AS VARCHAR(50)) AS L_CASHDISCOUNTAMOUNT, '0.00' AS L_LINEDISCOUNT, '0.00' AS L_LINEMANUALDISCOUNTAMOUNT, '0.00' AS L_LINEMANUALDISCOUNTPERCENTAGE, 'NO' AS L_ISLINEDISCOUNTED, CASE WHEN GL_QTEFACT < 0 THEN 'YES' WHEN GL_QTEFACT > 0 THEN 'NO' ELSE '' END AS L_ISRETURNNOSALE, ISNULL((SELECT ET_CHARLIBRE1 FROM ETABLISS WHERE ET_ETABLISSEMENT = (SELECT TOP 1 P.GP_ETABLISSEMENT FROM PIECE P WHERE P.GP_SOUCHE = GL_SOUCHE AND P.GP_INDICEG = GL_INDICEG AND P.GP_NATUREPIECEG = 'FFO' AND P.GP_NUMERO = (SELECT TOP 1 GLP.GLP_NUMERO FROM LIAISONPIECE GLP WHERE GLP.GLP_SOUCHE = GL_SOUCHE AND GLP.GLP_CODESITE = GL_SOUCHE AND GLP.GLP_NATUREPIECEG = 'FFO' AND GLP.GLP_INDICEG = GL_INDICEG AND GLP.GLP_RANG = 'O' AND GLP.GLP_NUMLIEN = (SELECT TOP 1 GLPP.GLP_NUMLIEN FROM LIAISONPIECE GLPP WHERE GLPP.GLP_NUMERO = GL_NUMERO AND GLPP.GLP_SOUCHE = GL_SOUCHE AND GLPP.GLP_CODESITE = GL_SOUCHE AND GLPP.GLP_NATUREPIECEG = GL_NATUREPIECEG AND GLPP.GLP_INDICEG = GL_INDICEG ORDER BY GLPP.GLP_NUMLIEN)))), '') AS L_RETURNOPERATINGUNITNUMBER, '' AS T_SALESLINENUMBER, '' AS T_TAXCODE, '' AS T_TAXPERCENTAGE, '' AS T_TAXAMOUNT, '' AS P_LINENUMBER, '' AS P_STORE, '' AS P_RECEIPTID, '' AS P_TENDERTYPE, '' AS P_AMOUNTTENDERED, '' AS P_AMOUNTINTENDEREDCURRENCY, '' AS P_AMOUNTINACCOUNTINGCURRENCY, '' AS P_REFUNDABLEAMOUNT, '' AS P_AMOUNTTENDEREDADJUSTMENT, '' AS P_LINKEDPAYMENTLINENUMBER, '' AS P_QUANTITY, '' AS P_CURRENCYCODE, '' AS P_EXCHANGERATEINTENDEREDCURRENCY, '' AS P_EXCHANGERATEINACCOUNTINGCURRENCY, '' AS P_TRANSACTIONSTATUS, '' AS P_STAFF, '' AS P_ISPREPAYMENT, '' AS P_ISCHANGELINE, '' AS P_ISLINKEDREFUND, '' AS P_ISPAYMENTCAPTURED, '' AS D_SALESLINENUMBER, '' AS D_LINENUMBER, '' AS D_DISCOUNTCOST, '' AS D_DISCOUNTORIGINTYPE, '' AS D_MANUALDISCOUNTTYPE, '' AS D_DISCOUNTPERCENTAGE, '' AS D_CUSTOMERDISCOUNTTYPE, '' AS D_DEALPRICE, '' AS D_DISCOUNTAMOUNT, '' AS D_EFFECTIVEAMOUNT, CAST(GP_NATUREPIECEG AS VARCHAR(50)) AS C38, CAST(GP_SOUCHE AS VARCHAR(50)) AS C39, CAST(GP_NUMERO AS VARCHAR(50)) AS C40, CAST(GP_INDICEG AS VARCHAR(50)) AS C41, CAST(GP_ETATEXPORT AS VARCHAR(50)) AS C42, CAST(GP_REFINTERNE AS VARCHAR(50)) AS C44 FROM LIGNE LEFT JOIN PIECE ON GP_NATUREPIECEG = GL_NATUREPIECEG AND GP_SOUCHE = GL_SOUCHE AND GP_NUMERO = GL_NUMERO AND GP_INDICEG = GL_INDICEG LEFT JOIN ZLIGNESQRCODE ON ZLQ_NATUREPIECEG = GL_NATUREPIECEG AND ZLQ_SOUCHE = GL_SOUCHE AND ZLQ_NUMERO = GL_NUMERO AND ZLQ_INDICEG = GL_INDICEG AND ZLQ_NUMLIGNE = GL_NUMLIGNE LEFT JOIN ZQRCODEITEMS ON ZBA_QRCODE = ZLQ_QRCODE LEFT JOIN ETABLISS ON GP_ETABLISSEMENT = ET_ETABLISSEMENT LEFT JOIN DEPOTS ON GDE_DEPOT = GL_DEPOT LEFT JOIN CHOIXEXT YX1 ON ET_LIBREET1 = YX1.YX_CODE AND YX1.YX_TYPE = 'EL1' LEFT JOIN TIERS ON T_TIERS = GP_TIERS AND T_NATUREAUXI = 'CLI' LEFT JOIN TIERSCOMPL ON YTC_AUXILIAIRE = T_AUXILIAIRE AND YTC_TIERS = GP_TIERS WHERE GP_NATUREPIECEG = 'FFO' AND GP_TICKETANNULE = '-' AND GP_VIVANTE = 'X' AND GP_SUPPRIME <> 'X' AND GL_TYPEARTICLE = 'MAR' AND GL_TYPELIGNE = 'ART' AND '004' NOT IN (SELECT GPE_MODEPAIE FROM PIEDECHE WHERE GP_NATUREPIECEG = GPE_NATUREPIECEG AND GP_SOUCHE = GPE_SOUCHE AND GP_NUMERO = GPE_NUMERO AND GP_INDICEG = GPE_INDICEG) AND GP_ETATEXPORT <> 'EXP' AND GP_REFINTERNE IN ('0050001610001', '0050001610002') UNION ALL SELECT '3' AS C0, 'T' AS C1, 3 AS ORDERBY, '' AS TYPE_VENTE, '' AS IDENTIFIED_CUSTOMER, '' AS NB_SALESLINESV2, '' AS NB_TAXLINES, '' AS NB_PYAMENT, '' AS NB_DISCOUNT, CAST(ET_FILIALE AS VARCHAR(50)) AS DATAAREAID, CAST(GP_REFINTERNE AS VARCHAR(50)) AS TRANSACTIONNUMBER, CAST(ET_CHARLIBRE1 AS VARCHAR(50)) AS OPERATINGUNITNUMBER, CAST(GP_ETABLISSEMENT AS VARCHAR(50)) AS TERMINAL, CAST(MAX(GP_DTSVCREATE) AS VARCHAR(50)) AS TRANSACTIONDATE, '' AS H_CUSTOMERACCOUNT, '' AS H_RRECEIPTID, CAST(MAX(GP_HEURECREATION) AS VARCHAR(50)) AS H_BUSINESSDATE, '' AS H_TRANSACTIONORDERTYPE, '' AS H_TRANSACTIONTYPE, '' AS H_TAXCALCULATIONTYPE, '' AS H_SALEISRETURNSALE, '' AS H_STAFF, '' AS H_LANGUAGEID, '' AS H_CURRENCY, '' AS H_EXCHANGERATE, '' AS H_WAREHOUSE, '' AS H_NUMBEROFITEMLINES, '' AS H_NUMBEROFITEMS, '' AS H_NUMBEROFPAYMENTLINES, '' AS H_NETPRICE, '' AS H_NETAMOUNT, '' AS H_GROSSAMOUNT, '' AS H_DISCOUNTAMOUNT, '' AS H_DISCOUNTAMOUNTWITHOUTTAX, '' AS H_TOTALDISCOUNTAMOUNT, '' AS H_TOTALMANUALDISCOUNTAMOUNT, '' AS H_TOTALMANUALDISCOUNTPERCENTAGE, '' AS H_PAYMENTAMOUNT, '' AS H_AMOUNTPOSTEDTOACCOUNT, '' AS L_LINENUMBER, '' AS L_ORIGINALITEMSALESTAXGROUP, '' AS L_NETPRICE, '' AS L_CATEGORYHIERARCHYNAME, '' AS L_UNIT, '' AS L_CURRENCY, '' AS L_NETAMOUNTINCLUSIVETAX, '' AS L_NETAMOUNT, '' AS L_UNITQUANTITY, '' AS L_BUSINESSDATE, '' AS L_CATEGORYNAME, '' AS L_KEYBOARDPRODUCTENTRY, '' AS L_ITEMID, '' AS L_WAREHOUSE, '' AS L_IVENTBATCHID, '' AS L_SALESTAXAMOUNT, '' AS L_QUANTITY, '' AS L_PRICE, '' AS L_SALESTAXGROUP, '' AS L_ORIGINALSALESTAXGROUP, '' AS L_ITEMSALESTAXGROUP, '' AS L_DISCOUNTAMOUNTWITHOUTTAX, '' AS L_TOTALDISCOUNT, '' AS L_TOTALDISCOUNTPERCENTAGE, '' AS L_CASHDISCOUNTAMOUNT, '' AS L_LINEDISCOUNT, '' AS L_LINEMANUALDISCOUNTAMOUNT, '' AS L_LINEMANUALDISCOUNTPERCENTAGE, '' AS L_ISLINEDISCOUNTED, '' AS L_ISRETURNNOSALE, '' AS L_RETURNOPERATINGUNITNUMBER, CAST(GL_NUMLIGNE AS VARCHAR(50)) AS T_SALESLINENUMBER, CAST(YX3.YX_LIBELLE AS VARCHAR(50)) AS T_TAXCODE, CAST(GPB_TAUXTAXE AS VARCHAR(50)) AS T_TAXPERCENTAGE, CAST(GL_TOTALTAXE1 AS VARCHAR(50)) AS T_TAXAMOUNT, '' AS P_LINENUMBER, '' AS P_STORE, '' AS P_RECEIPTID, '' AS P_TENDERTYPE, '' AS P_AMOUNTTENDERED, '' AS P_AMOUNTINTENDEREDCURRENCY, '' AS P_AMOUNTINACCOUNTINGCURRENCY, '' AS P_REFUNDABLEAMOUNT, '' AS P_AMOUNTTENDEREDADJUSTMENT, '' AS P_LINKEDPAYMENTLINENUMBER, '' AS P_QUANTITY, '' AS P_CURRENCYCODE, '' AS P_EXCHANGERATEINTENDEREDCURRENCY, '' AS P_EXCHANGERATEINACCOUNTINGCURRENCY, '' AS P_TRANSACTIONSTATUS, '' AS P_STAFF, '' AS P_ISPREPAYMENT, '' AS P_ISCHANGELINE, '' AS P_ISLINKEDREFUND, '' AS P_ISPAYMENTCAPTURED, '' AS D_SALESLINENUMBER, '' AS D_LINENUMBER, '' AS D_DISCOUNTCOST, '' AS D_DISCOUNTORIGINTYPE, '' AS D_MANUALDISCOUNTTYPE, '' AS D_DISCOUNTPERCENTAGE, '' AS D_CUSTOMERDISCOUNTTYPE, '' AS D_DEALPRICE, '' AS D_DISCOUNTAMOUNT, '' AS D_EFFECTIVEAMOUNT, CAST(GP_NATUREPIECEG AS VARCHAR(50)) AS C38, CAST(GP_SOUCHE AS VARCHAR(50)) AS C39, CAST(GP_NUMERO AS VARCHAR(50)) AS C40, CAST(GP_INDICEG AS VARCHAR(50)) AS C41, CAST(GP_ETATEXPORT AS VARCHAR(50)) AS C42, CAST(GP_REFINTERNE AS VARCHAR(50)) AS C44 FROM LIGNE LEFT JOIN PIECE ON GP_NATUREPIECEG = GL_NATUREPIECEG AND GP_SOUCHE = GL_SOUCHE AND GP_NUMERO = GL_NUMERO AND GP_INDICEG = GL_INDICEG LEFT JOIN ETABLISS ON GP_ETABLISSEMENT = ET_ETABLISSEMENT LEFT JOIN CHOIXEXT YX3 ON ET_LIBREET3 = YX3.YX_CODE AND YX3.YX_TYPE = 'EL3' LEFT JOIN PIEDBASE ON GP_NATUREPIECEG = GPB_NATUREPIECEG AND GP_SOUCHE = GPB_SOUCHE AND GP_NUMERO = GPB_NUMERO AND GP_INDICEG = GPB_INDICEG WHERE GP_NATUREPIECEG = 'FFO' AND GP_TICKETANNULE = '-' AND GP_VIVANTE = 'X' AND GP_SUPPRIME <> 'X' AND GL_TYPELIGNE = 'ART' AND GL_TYPEARTICLE = 'MAR' AND '004' NOT IN (SELECT GPE_MODEPAIE FROM PIEDECHE WHERE GP_NATUREPIECEG = GPE_NATUREPIECEG AND GP_SOUCHE = GPE_SOUCHE AND GP_NUMERO = GPE_NUMERO AND GP_INDICEG = GPE_INDICEG) AND GP_ETATEXPORT <> 'EXP' AND GP_REFINTERNE IN ('0050001610001', '0050001610002') GROUP BY ET_FILIALE, GP_REFINTERNE, GP_ETABLISSEMENT, GP_NUMERO, GL_NUMLIGNE, GPB_TAUXTAXE, GL_TOTALTAXE1, YX3.YX_LIBELLE, GP_NATUREPIECEG, GP_SOUCHE, GP_INDICEG, ET_CHARLIBRE1, GP_ETATEXPORT UNION ALL SELECT '4' AS C0, 'P' AS C1, 4 AS ORDERBY, '' AS TYPE_VENTE, '' AS IDENTIFIED_CUSTOMER, '' AS NB_SALESLINESV2, '' AS NB_TAXLINES, '' AS NB_PYAMENT, '' AS NB_DISCOUNT, CAST(ET_FILIALE AS VARCHAR(50)) AS DATAAREAID, CAST(GP_REFINTERNE AS VARCHAR(50)) AS TRANSACTIONNUMBER, CAST(ET_CHARLIBRE1 AS VARCHAR(50)) AS OPERATINGUNITNUMBER, CAST(GP_ETABLISSEMENT AS VARCHAR(50)) AS TERMINAL, '' AS TRANSACTIONDATE, '' AS H_CUSTOMERACCOUNT, '' AS H_RRECEIPTID, '' AS H_BUSINESSDATE, '' AS H_TRANSACTIONORDERTYPE, '' AS H_TRANSACTIONTYPE, '' AS H_TAXCALCULATIONTYPE, '' AS H_SALEISRETURNSALE, '' AS H_STAFF, '' AS H_LANGUAGEID, '' AS H_CURRENCY, '' AS H_EXCHANGERATE, '' AS H_WAREHOUSE, '' AS H_NUMBEROFITEMLINES, '' AS H_NUMBEROFITEMS, '' AS H_NUMBEROFPAYMENTLINES, '' AS H_NETPRICE, '' AS H_NETAMOUNT, '' AS H_GROSSAMOUNT, '' AS H_DISCOUNTAMOUNT, '' AS H_DISCOUNTAMOUNTWITHOUTTAX, '' AS H_TOTALDISCOUNTAMOUNT, '' AS H_TOTALMANUALDISCOUNTAMOUNT, '' AS H_TOTALMANUALDISCOUNTPERCENTAGE, '' AS H_PAYMENTAMOUNT, '' AS H_AMOUNTPOSTEDTOACCOUNT, '' AS L_LINENUMBER, '' AS L_ORIGINALITEMSALESTAXGROUP, '' AS L_NETPRICE, '' AS L_CATEGORYHIERARCHYNAME, '' AS L_UNIT, '' AS L_CURRENCY, '' AS L_NETAMOUNTINCLUSIVETAX, '' AS L_NETAMOUNT, '' AS L_UNITQUANTITY, '' AS L_BUSINESSDATE, '' AS L_CATEGORYNAME, '' AS L_KEYBOARDPRODUCTENTRY, '' AS L_ITEMID, '' AS L_WAREHOUSE, '' AS L_IVENTBATCHID, '' AS L_SALESTAXAMOUNT, '' AS L_QUANTITY, '' AS L_PRICE, '' AS L_SALESTAXGROUP, '' AS L_ORIGINALSALESTAXGROUP, '' AS L_ITEMSALESTAXGROUP, '' AS L_DISCOUNTAMOUNTWITHOUTTAX, '' AS L_TOTALDISCOUNT, '' AS L_TOTALDISCOUNTPERCENTAGE, '' AS L_CASHDISCOUNTAMOUNT, '' AS L_LINEDISCOUNT, '' AS L_LINEMANUALDISCOUNTAMOUNT, '' AS L_LINEMANUALDISCOUNTPERCENTAGE, '' AS L_ISLINEDISCOUNTED, '' AS L_ISRETURNNOSALE, '' AS L_RETURNOPERATINGUNITNUMBER, '' AS T_SALESLINENUMBER, '' AS T_TAXCODE, '' AS T_TAXPERCENTAGE, '' AS T_TAXAMOUNT, CAST(GPE_NUMECHE AS VARCHAR(50)) AS P_LINENUMBER, CAST(GPE_ETABLISSEMENT AS VARCHAR(50)) AS P_STORE, CAST(GPE_NUMERO AS VARCHAR(50)) AS P_RECEIPTID, CAST(RIGHT(GPE_MODEPAIE, 1) AS VARCHAR(50)) AS P_TENDERTYPE, CAST(GPE_MONTANTECHE AS VARCHAR(50)) AS P_AMOUNTTENDERED, CAST(GPE_MONTANTDEV AS VARCHAR(50)) AS P_AMOUNTINTENDEREDCURRENCY, CAST(GPE_MONTANTDEV AS VARCHAR(50)) AS P_AMOUNTINACCOUNTINGCURRENCY, CAST(GPE_MONTANTECHE AS VARCHAR(50)) AS P_REFUNDABLEAMOUNT, '0.00' AS P_AMOUNTTENDEREDADJUSTMENT, '0.00' AS P_LINKEDPAYMENTLINENUMBER, CAST(COUNT(GPE_NUMECHE) AS VARCHAR(50)) AS P_QUANTITY, CAST(GPE_DEVISE AS VARCHAR(50)) AS P_CURRENCYCODE, CAST((GPE_COTATIONESP * 100) AS VARCHAR(50)) AS P_EXCHANGERATEINTENDEREDCURRENCY, CAST((GPE_COTATION * 100) AS VARCHAR(50)) AS P_EXCHANGERATEINACCOUNTINGCURRENCY, 'POSTED' AS P_TRANSACTIONSTATUS, CAST(GP_REPRESENTANT AS VARCHAR(50)) AS P_STAFF, 'NO' AS P_ISPREPAYMENT, 'NO' AS P_ISCHANGELINE, 'NO' AS P_ISLINKEDREFUND, 'YES' AS P_ISPAYMENTCAPTURED, '' AS D_SALESLINENUMBER, '' AS D_LINENUMBER, '' AS D_DISCOUNTCOST, '' AS D_DISCOUNTORIGINTYPE, '' AS D_MANUALDISCOUNTTYPE, '' AS D_DISCOUNTPERCENTAGE, '' AS D_CUSTOMERDISCOUNTTYPE, '' AS D_DEALPRICE, '' AS D_DISCOUNTAMOUNT, '' AS D_EFFECTIVEAMOUNT, CAST(GP_NATUREPIECEG AS VARCHAR(50)) AS C38, CAST(GP_SOUCHE AS VARCHAR(50)) AS C39, CAST(GP_NUMERO AS VARCHAR(50)) AS C40, CAST(GP_INDICEG AS VARCHAR(50)) AS C41, CAST(GP_ETATEXPORT AS VARCHAR(50)) AS C42, CAST(GP_REFINTERNE AS VARCHAR(50)) AS C44 FROM PIEDECHE LEFT JOIN PIECE ON GP_NATUREPIECEG = GPE_NATUREPIECEG AND GP_SOUCHE = GPE_SOUCHE AND GP_NUMERO = GPE_NUMERO AND GP_INDICEG = GPE_INDICEG LEFT JOIN ETABLISS ON GP_ETABLISSEMENT = ET_ETABLISSEMENT LEFT JOIN TIERS ON T_TIERS = GP_TIERS AND T_NATUREAUXI = 'CLI' LEFT JOIN TIERSCOMPL ON YTC_AUXILIAIRE = T_AUXILIAIRE AND YTC_TIERS = GP_TIERS WHERE GP_NATUREPIECEG = 'FFO' AND GP_TICKETANNULE = '-' AND GP_VIVANTE = 'X' AND GP_SUPPRIME <> 'X' AND 'MAR' IN (SELECT GL_TYPEARTICLE FROM LIGNE WHERE GL_NUMERO = GP_NUMERO AND GL_SOUCHE = GP_SOUCHE AND GL_INDICEG = GP_INDICEG AND GL_NATUREPIECEG = GP_NATUREPIECEG) AND 'ART' IN (SELECT GL_TYPELIGNE FROM LIGNE WHERE GL_NUMERO = GP_NUMERO AND GL_SOUCHE = GP_SOUCHE AND GL_INDICEG = GP_INDICEG AND GL_NATUREPIECEG = GP_NATUREPIECEG) AND GPE_MODEPAIE <> '004' AND GP_ETATEXPORT <> 'EXP' AND GP_REFINTERNE IN ('0050001610001', '0050001610002') GROUP BY ET_FILIALE, GP_REFINTERNE, GP_ETABLISSEMENT, GPE_NUMERO, GPE_NUMECHE, GPE_ETABLISSEMENT, GPE_MONTANTDEV, ET_CHARLIBRE1, RIGHT(GPE_MODEPAIE, 1), GPE_MONTANTECHE, GPE_DEVISE, GPE_COTATION, GPE_COTATIONESP, GP_REPRESENTANT, GP_NUMERO, GP_NATUREPIECEG, GP_SOUCHE, GP_INDICEG, GP_ETATEXPORT UNION ALL SELECT '5' AS C0, 'D' AS C1, 5 AS ORDERBY, '' AS TYPE_VENTE, '' AS IDENTIFIED_CUSTOMER, '' AS NB_SALESLINESV2, '' AS NB_TAXLINES, '' AS NB_PYAMENT, '' AS NB_DISCOUNT, CAST(ET_FILIALE AS VARCHAR(50)) AS DATAAREAID, CAST(GP_REFINTERNE AS VARCHAR(50)) AS TRANSACTIONNUMBER, CAST(ET_CHARLIBRE1 AS VARCHAR(50)) AS OPERATINGUNITNUMBER, CAST(GP_ETABLISSEMENT AS VARCHAR(50)) AS TERMINAL, '' AS TRANSACTIONDATE, '' AS H_CUSTOMERACCOUNT, '' AS H_RRECEIPTID, '' AS H_BUSINESSDATE, '' AS H_TRANSACTIONORDERTYPE, '' AS H_TRANSACTIONTYPE, '' AS H_TAXCALCULATIONTYPE, '' AS H_SALEISRETURNSALE, '' AS H_STAFF, '' AS H_LANGUAGEID, '' AS H_CURRENCY, '' AS H_EXCHANGERATE, '' AS H_WAREHOUSE, '' AS H_NUMBEROFITEMLINES, '' AS H_NUMBEROFITEMS, '' AS H_NUMBEROFPAYMENTLINES, '' AS H_NETPRICE, '' AS H_NETAMOUNT, '' AS H_GROSSAMOUNT, '' AS H_DISCOUNTAMOUNT, '' AS H_DISCOUNTAMOUNTWITHOUTTAX, '' AS H_TOTALDISCOUNTAMOUNT, '' AS H_TOTALMANUALDISCOUNTAMOUNT, '' AS H_TOTALMANUALDISCOUNTPERCENTAGE, '' AS H_PAYMENTAMOUNT, '' AS H_AMOUNTPOSTEDTOACCOUNT, '' AS L_LINENUMBER, '' AS L_ORIGINALITEMSALESTAXGROUP, '' AS L_NETPRICE, '' AS L_CATEGORYHIERARCHYNAME, '' AS L_UNIT, '' AS L_CURRENCY, '' AS L_NETAMOUNTINCLUSIVETAX, '' AS L_NETAMOUNT, '' AS L_UNITQUANTITY, '' AS L_BUSINESSDATE, '' AS L_CATEGORYNAME, '' AS L_KEYBOARDPRODUCTENTRY, '' AS L_ITEMID, '' AS L_WAREHOUSE, '' AS L_IVENTBATCHID, '' AS L_SALESTAXAMOUNT, '' AS L_QUANTITY, '' AS L_PRICE, '' AS L_SALESTAXGROUP, '' AS L_ORIGINALSALESTAXGROUP, '' AS L_ITEMSALESTAXGROUP, '' AS L_DISCOUNTAMOUNTWITHOUTTAX, '' AS L_TOTALDISCOUNT, '' AS L_TOTALDISCOUNTPERCENTAGE, '' AS L_CASHDISCOUNTAMOUNT, '' AS L_LINEDISCOUNT, '' AS L_LINEMANUALDISCOUNTAMOUNT, '' AS L_LINEMANUALDISCOUNTPERCENTAGE, '' AS L_ISLINEDISCOUNTED, '' AS L_ISRETURNNOSALE, '' AS L_RETURNOPERATINGUNITNUMBER, '' AS T_SALESLINENUMBER, '' AS T_TAXCODE, '' AS T_TAXPERCENTAGE, '' AS T_TAXAMOUNT, '' AS P_LINENUMBER, '' AS P_STORE, '' AS P_RECEIPTID, '' AS P_TENDERTYPE, '' AS P_AMOUNTTENDERED, '' AS P_AMOUNTINTENDEREDCURRENCY, '' AS P_AMOUNTINACCOUNTINGCURRENCY, '' AS P_REFUNDABLEAMOUNT, '' AS P_AMOUNTTENDEREDADJUSTMENT, '' AS P_LINKEDPAYMENTLINENUMBER, '' AS P_QUANTITY, '' AS P_CURRENCYCODE, '' AS P_EXCHANGERATEINTENDEREDCURRENCY, '' AS P_EXCHANGERATEINACCOUNTINGCURRENCY, '' AS P_TRANSACTIONSTATUS, '' AS P_STAFF, '' AS P_ISPREPAYMENT, '' AS P_ISCHANGELINE, '' AS P_ISLINKEDREFUND, '' AS P_ISPAYMENTCAPTURED, CAST(MLR_NUMLIGNE AS VARCHAR(20)) AS D_SALESLINENUMBER, CAST(MLR_NUMLIGNE AS VARCHAR(20)) AS D_LINENUMBER, CAST(SUM(MLR_MONTANTHT) AS VARCHAR(50)) AS D_DISCOUNTCOST, CAST(CASE WHEN MLR_ORGREMISE = '003' THEN 'MANUAL' WHEN MLR_ORGREMISE IN ('006', '007') THEN 'CUSTOMER' ELSE '' END AS VARCHAR(50)) AS D_DISCOUNTORIGINTYPE, 'TOTALDISCOUNTPERCENT' AS D_MANUALDISCOUNTTYPE, CAST(MLR_REMISE AS VARCHAR(50)) AS D_DISCOUNTPERCENTAGE, 'NONE' AS D_CUSTOMERDISCOUNTTYPE, '0.00' AS D_DEALPRICE, CAST(SUM(MLR_MONTANTHT) AS VARCHAR(50)) AS D_DISCOUNTAMOUNT, CAST(SUM(MLR_MONTANTHT) AS VARCHAR(50)) AS D_EFFECTIVEAMOUNT, CAST(GP_NATUREPIECEG AS VARCHAR(50)) AS C38, CAST(GP_SOUCHE AS VARCHAR(50)) AS C39, CAST(GP_NUMERO AS VARCHAR(50)) AS C40, CAST(GP_INDICEG AS VARCHAR(50)) AS C41, CAST(GP_ETATEXPORT AS VARCHAR(50)) AS C42, CAST(GP_REFINTERNE AS VARCHAR(50)) AS C44 FROM LIGNEREMISE LEFT JOIN PIECE ON GP_NATUREPIECEG = MLR_NATUREPIECEG AND GP_SOUCHE = MLR_SOUCHE AND GP_NUMERO = MLR_NUMERO AND GP_INDICEG = MLR_INDICEG LEFT JOIN ETABLISS ON GP_ETABLISSEMENT = ET_ETABLISSEMENT WHERE GP_NATUREPIECEG = 'FFO' AND GP_TICKETANNULE = '-' AND GP_VIVANTE = 'X' AND GP_SUPPRIME <> 'X' AND 'MAR' IN (SELECT GL_TYPEARTICLE FROM LIGNE WHERE GL_NUMERO = GP_NUMERO AND GL_SOUCHE = GP_SOUCHE AND GL_INDICEG = GP_INDICEG AND GL_NATUREPIECEG = GP_NATUREPIECEG) AND 'ART' IN (SELECT GL_TYPELIGNE FROM LIGNE WHERE GL_NUMERO = GP_NUMERO AND GL_SOUCHE = GP_SOUCHE AND GL_INDICEG = GP_INDICEG AND GL_NATUREPIECEG = GP_NATUREPIECEG) AND '004' NOT IN (SELECT GPE_MODEPAIE FROM PIEDECHE WHERE GP_NATUREPIECEG = GPE_NATUREPIECEG AND GP_SOUCHE = GPE_SOUCHE AND GP_NUMERO = GPE_NUMERO AND GP_INDICEG = GPE_INDICEG) AND GP_ETATEXPORT <> 'EXP' AND GP_REFINTERNE IN ('0050001610001', '0050001610002') GROUP BY ET_FILIALE, GP_ETABLISSEMENT, GP_REFINTERNE, ET_CHARLIBRE1, MLR_NUMLIGNE, MLR_REMISE, MLR_ORGREMISE, GP_NATUREPIECEG, GP_SOUCHE, GP_NUMERO, GP_INDICEG, GP_ETATEXPORT ORDER BY TRANSACTIONNUMBER, ORDERBY