SELECT INV.DATE_ AS [FATURA TARIHI],INV.FICHENO AS [FATURA NO],
CLC.CODE AS [CARI HESAP KODU],CLC.DEFINITION_ AS [CARI HESAP AÇIKLAMASI],
STF.FICHENO AS [İRS.NO],STF.DATE_ AS [İRS.TARİHİ],
ITM.CODE AS [ÜRÜN KODU],ITM.NAME AS [ÜRÜN AÇIKLAMASI],
UNIT.NAME AS [BİRİMİ],
STL.AMOUNT AS [MİKTARI],STL.PRICE AS [BİRİM FİYATI],
(STL.VATAMNT+STL.VATMATRAH) AS [SATIR TUTARI]
FROM LG_506_01_INVOICE INV
RIGHT OUTER JOIN LG_506_01_STFICHE STF ON INV.LOGICALREF = STF.INVOICEREF
LEFT OUTER JOIN LG_506_01_STLINE STL ON STF.LOGICALREF = STL.STFICHEREF
LEFT OUTER JOIN LG_506_CLCARD CLC ON INV.CLIENTREF = CLC.LOGICALREF
LEFT OUTER JOIN LG_506_ITEMS ITM ON STL.STOCKREF = ITM.LOGICALREF
LEFT OUTER JOIN LG_506_UNITSETL UNIT ON STL.UOMREF = UNIT.LOGICALREF
WHERE --INV.FICHENO='00022'
CLC.LOGICALREF=681
UNION
SELECT DISTINCT '' AS [FATURA TARIHI],'' AS [FATURA NO],
CLC.CODE AS [CARI HESAP KODU],CLC.DEFINITION_ AS [CARI HESAP AÇIKLAMASI],
STF.FICHENO AS [İRS.NO],STF.DATE_ AS [İRS.TARİHİ],
ITM.CODE AS [ÜRÜN KODU],ITM.NAME AS [ÜRÜN AÇIKLAMASI],
UNIT.NAME AS [BİRİMİ],
STL.AMOUNT AS [MİKTARI],STL.PRICE AS [BİRİM FİYATI],
(STL.VATAMNT+STL.VATMATRAH) AS [SATIR TUTARI]
FROM LG_506_01_STFICHE STF
LEFT OUTER JOIN LG_506_01_STLINE STL ON STF.LOGICALREF = STL.STFICHEREF
LEFT OUTER JOIN LG_506_CLCARD CLC ON STF.CLIENTREF = CLC.LOGICALREF
LEFT OUTER JOIN LG_506_ITEMS ITM ON STL.STOCKREF = ITM.LOGICALREF
LEFT OUTER JOIN LG_506_UNITSETL UNIT ON STL.UOMREF = UNIT.LOGICALREF
WHERE STF.BILLED=0 AND CLC.LOGICALREF=681
ORDER BY STF.DATE_