SET ROWCOUNT 0
SELECT
LGMAIN.LOGICALREF, LGMAIN.DATE_, LGMAIN.LINEEXP, LGMAIN.AMOUNT, LGMAIN.DEMPEGGEDAMNT, LGMAIN.SPECODE,
LGMAIN.STFICHEREF, LGMAIN.TRCODE, LGMAIN.SOURCEINDEX, LGMAIN.SOURCEWSREF, LGMAIN.LINETYPE, LGMAIN.IOCODE,
LGMAIN.UOMREF, LGMAIN.TOTAL, LGMAIN.PRCURR, LGMAIN.PRPRICE, LGMAIN.PRICE, LGMAIN.STATUS, LGMAIN.LINENET,
LGMAIN.DISTDISC, LGMAIN.DESTINDEX, LGMAIN.INVOICEREF, LGMAIN.BILLED, LGMAIN.REPORTRATE, LGMAIN.PROJECTREF,
LGMAIN.DIFFPRICE, STFIC.CANCELLED, STFIC.BILLED, STFIC.ACCOUNTED, STFIC.FICHENO, STFIC.CLIENTREF,
STFIC.SOURCEINDEX, STFIC.SOURCEWSREF, STFIC.BRANCH, STFIC.COMPBRANCH, STFIC.TRCODE, STFIC.FACTORYNR,
STFIC.COMPFACTORY, STFIC.DEPARTMENT, STFIC.COMPDEPARTMENT, STFIC.SPECODE, STFIC.PRODSTAT, STFIC.UGIRTRACKINGNO,
STFIC.DOCODE, STFIC.GRPCODE, INVFC.ENTEGSET, PAYPL.CODE, LGMAIN.STOCKREF, LGMAIN.VARIANTREF, LGMAIN.FTIME
FROM
LG_006_01_STLINE LGMAIN WITH(NOLOCK) LEFT OUTER JOIN LG_006_01_STFICHE STFIC WITH(NOLOCK) ON (LGMAIN.STFICHEREF = STFIC.LOGICALREF) LEFT OUTER JOIN LG_006_01_INVOICE INVFC WITH(NOLOCK) ON (STFIC.INVOICEREF = INVFC.LOGICALREF) LEFT OUTER JOIN LG_006_PAYPLANS PAYPL WITH(NOLOCK) ON (LGMAIN.PAYDEFREF = PAYPL.LOGICALREF)
WHERE
(LGMAIN.LINETYPE IN (0, 1, 5, 6, 8, 9, 11))
AND (LGMAIN.STFICHEREF <> 0) AND (LGMAIN.STFICHEREF = STFIC.LOGICALREF)
AND (((STFIC.GRPCODE = 1)) OR ((STFIC.GRPCODE = 2))
OR ((STFIC.GRPCODE = 3)) OR (STFIC.GRPCODE = 0)) AND
(LGMAIN.DATE_ <= CONVERT(dateTime, '12-31-2020', 101)) AND
(LGMAIN.DATE_ >= CONVERT(dateTime, '1-1-2006', 101))
ORDER BY
LGMAIN.STOCKREF, LGMAIN.VARIANTREF, LGMAIN.DATE_, LGMAIN.FTIME, LGMAIN.IOCODE, LGMAIN.SOURCEINDEX, LGMAIN.TRCODE, LGMAIN.LINETYPE, LGMAIN.LOGICALREF
go