LOGO YAZIM SATIN ALMA FATURALARI DÖKÜMÜ DETAYLI SQL SORGU ÖRNEK
SELECT
CONVERT(nvarchar(20), INVOICE.DATE_,104) as Tarih,
--INVOICE.DATE_ AS [Tarih],
CLCARD.CODE as [Cari Kod],
CLCARD.DEFINITION_ as [Cari Ad],
INVOICE.SERIALCODE [Seri No],
INVOICE.FICHENO [Fiş No],
COALESCE(
CASE STLINE.UINFO1
WHEN 0 THEN STLINE.AMOUNT
ELSE STLINE.AMOUNT*STLINE.UINFO2/STLINE.UINFO1
END, STLINE.AMOUNT/1000) [Miktar],
UNT.CODE [Birim],
CASE WHEN STLINE.LINETYPE=4 THEN SRV.CODE ELSE INV.CODE END AS [Malzeme Kod],
CASE WHEN STLINE.LINETYPE=4 THEN SRV.DEFINITION_ ELSE INV.NAME END AS [Malzeme Ad],
CASE WHEN STLINE.LINETYPE=4 THEN '' ELSE INV.STGRPCODE END AS [Malzeme Grup],
CASE WHEN STLINE.LINETYPE=4 THEN SRV.SPECODE ELSE INV.SPECODE END AS [Malzeme Özel Kod],
STLINE.PRICE [Birim Fiyat],
STLINE.VATMATRAH [KDV Matrah],
STLINE.VATAMNT [KDV Tutar],
STLINE.VAT [KDV %],
(CASE WHEN INVOICE.TRCODE=1 THEN 'Satınalma Faturası'
WHEN INVOICE.TRCODE=4 THEN 'Alınan Hizmet Faturası'
WHEN INVOICE.TRCODE=5 THEN 'Alınan Proforma Fatura'
WHEN INVOICE.TRCODE=6 THEN 'Satınalma İade Faturası'
WHEN INVOICE.TRCODE=12 THEN 'Alınan Vade Farkı Faturası'
WHEN INVOICE.TRCODE=13THEN 'Satınalma Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=26 THEN 'Müstahsil Makbuzu' END
) as [Fatura Tipi],
CASE WHEN CLCARD.ISPERSCOMP=1 THEN CLCARD.TCKNO ELSE CLCARD.TAXNR END AS [Cari VNO],
CLCARD.TAXOFFICE as [Cari VDaire],
EMUH.CODE as [Muhasebe Hesap Planı Kod],
EMUH.DEFINITION_ as [Muhasebe Hesap Planı Ad],
RTRIM(INVOICE.GENEXP1+' '+INVOICE.GENEXP2+' '+INVOICE.GENEXP3+' '+INVOICE.GENEXP4) AS [Açıklama]
FROM LG_171_01_INVOICE AS INVOICE WITH (NOLOCK)
RIGHT OUTER JOIN LG_171_01_STLINE AS STLINE ON INVOICE.LOGICALREF = STLINE.INVOICEREF
RIGHT OUTER JOIN LG_171_ITEMS INV ON INV.LOGICALREF=STLINE.STOCKREF
LEFT JOIN LG_171_SRVCARD SRV ON SRV.LOGICALREF=STLINE.STOCKREF
RIGHT OUTER JOIN LG_171_CLCARD CLCARD ON CLCARD.LOGICALREF=INVOICE.CLIENTREF
LEFT JOIN LG_171_EMUHACC EMUH ON EMUH.LOGICALREF=STLINE.ACCOUNTREF
RIGHT OUTER JOIN LG_171_UNITSETL UNT ON UNT.UNITSETREF=INV.UNITSETREF AND LINENR=1
WHERE(INVOICE.TRCODE IN(1,4,5,6,12,13,26))
--AND INVOICE.DATE_ >= '2020-01-01 00:00:00.000'
--AND INVOICE.DATE_ <= '2020-12-31 23:59:59.999'
ORDER BY INVOICE.DATE_,
INVOICE.SOURCEINDEX,
INVOICE.FICHENO
LOGO YAZIM STOK DEĞER RAPORU DETAYLI SQL SORGU ÖRNEK
SELECT LG_102_ITEMS.CODE AS 'KODU', LG_102_ITEMS.NAME, LG_102_04_STINVTOT.INVENNO AS 'AMBAR', LG_102_04_STINVTOT.ONHAND AS 'M. STOK', LG_102_ITEMS.SPECODE AS 'Ö. KOD', LG_102_ITEMS.SPECODE2 AS 'Ö.KOD2', LG_102_ITEMS.STGRPCODE AS 'GRUP KODU', LG_102_ITEMS.CLASSTYPE AS 'M.TİPİ', LG_102_ITEMS.ACTIVE
FROM ERCAGLAYAN_GO3.dbo.LG_102_04_STINVTOT LG_102_04_STINVTOT, ERCAGLAYAN_GO3.dbo.LG_102_ITEMS LG_102_ITEMS
WHERE LG_102_ITEMS.LOGICALREF = LG_102_04_STINVTOT.STOCKREF AND ((LG_102_ITEMS.CODE Like 'K%'))
===============================================
SELECT M.PRODUCERCODE, M.CODE, M.NAME, S.ONHAND AS 'STOK'
FROM LG_102_ITEMS AS M INNER JOIN
LG_102_04_GNTOTST AS S ON S.STOCKREF = M.LOGICALREF
WHERE (S.INVENNO = 0)
ORDER BY M.CODE
LOGO YAZIM MUHASEBE BAĞLANTISI OLMAYAN FATURALAR SQL SORGU ÖRNEK
SELECT * FROM dbo.LG_102_04_INVOICE
WHERE dbo.LG_102_04_INVOICE.ACCFICHEREF
NOT IN (SELECT LOGICALREF FROM dbo.LG_102_04_EMFLINE)
LOGO YAZIM MALZEME HAREKETLERİ TOPLAMI HANGİ ÜRÜN KAÇ ADET KAÇ TL YE SATILMIŞ SQL SORU ÖRNEK
SELECT * FROM
(SELECT
I.CARDTYPE "Malzeme Türü",
I.CODE "Malzeme Kodu",
I.NAME "Malzeme Adı",
I.SPECODE "Malzeme Özel Kodu",
I.CYPHCODE "Malzeme Yetki Kodu",
U.CODE "Birim Seti ",
T.CODE "Birim Kodu",
SUM(SIGN(L.IOCODE-1)*L.AMOUNT) "Satış Miktarı",
SUM(SIGN(4-L.IOCODE)*L.AMOUNT) "İade Miktarı",
SUM(SIGN(L.IOCODE-1)*L.TOTAL) "Satış Tutarı"
FROM
"LOGO_GO3"..LG_102_04_STLINE L
LEFT OUTER JOIN "ERCAGLAYAN_GO3"..LG_102_04_STFICHE F ON L.STFICHEREF=F.LOGICALREF
LEFT OUTER JOIN "ERCAGLAYAN_GO3"..LG_102_ITEMS I ON L.STOCKREF=I.LOGICALREF AND L.LINETYPE NOT IN (1,2,3,4)
LEFT OUTER JOIN "ERCAGLAYAN_GO3"..LG_102_UNITSETF U ON I.UNITSETREF=U.LOGICALREF
LEFT OUTER JOIN "ERCAGLAYAN_GO3"..LG_102_UNITSETL T ON U.LOGICALREF=T.UNITSETREF
WHERE
I.LOGICALREF IS NOT NULL
AND F.GRPCODE=2
AND T.LINENR=1
GROUP BY
I.CARDTYPE,
I.CODE,
I.NAME,
U.CODE,
T.CODE,
I.SPECODE,
I.CYPHCODE
) AS DYNMQRY
ORDER BY DYNMQRY.[Malzeme Kodu] ASC, DYNMQRY.[Malzeme Adı] ASC
LOGO YAZILIM TÜM MALZEMELERİN FİYAT BİLGİSİ VE STOK MİKTARI SQL SORGU ÖRNEK
SELECT
I.CODE AS 'URUN_KODU' ,
I.NAME AS 'URUN_ADI',
I.STGRPCODE AS 'GRUP KODU',
I.SPECODE AS 'ÖZEL KODU',
I.LOGICALREF ,
ISNULL(PR.PRICE,0)AS FIYAT,
ISNULL(PR.CURRENCY,0) AS DOVIZ,
sUM ((CASE S.TRCODE WHEN 1 THEN S.AMOUNT*UINFO2 ELSE 0 END )+ (CASE S.TRCODE WHEN 13 THEN S.AMOUNT*UINFO2 ELSE 0 END)+
(CASE F.TRCODE WHEN 3 THEN S.AMOUNT*UINFO2 ELSE 0 END )+(CASE S.TRCODE WHEN 14 THEN S.AMOUNT*UINFO2 ELSE 0 END)+
(CASE S.TRCODE WHEN 50 THEN S.AMOUNT*UINFO2 ELSE 0 END)) +
SUM((CASE F.TRCODE WHEN 8 THEN S.AMOUNT*UINFO2 ELSE 0 END)+(CASE S.TRCODE WHEN 6 THEN S.AMOUNT*UINFO2 ELSE 0 END)+
(CASE S.TRCODE WHEN 12 THEN S.AMOUNT*UINFO2 ELSE 0 END)+(CASE S.TRCODE WHEN 11 THEN S.AMOUNT*UINFO2 ELSE 0 END)+
(CASE S.TRCODE WHEN 51 THEN S.AMOUNT*UINFO2 ELSE 0 END)+(CASE S.TRCODE WHEN 20 THEN S.AMOUNT*UINFO2 ELSE 0 END)) AS 'STOK'
from LG_016_ITEMS AS I LEFT JOIN
LG_016_01_STLINE AS S ON I.LOGICALREF=S.STOCKREF AND I.ACTIVE='0' and S.CANCELLED='0' LEFT JOIN
LG_016_01_INVOICE AS F ON S.INVOICEREF=F.LOGICALREF LEFT JOIN
LG_016_PRCLIST AS PR ON I.LOGICALREF=PR.CARDREF and PR.PTYPE=2 LEFT JOIN
LG_016_01_STINVTOT AS ST ON I.LOGICALREF=ST.STOCKREF
GROUP BY I.CODE,I.NAME,I.STGRPCODE,I.SPECODE,PR.PRICE,PR.CURRENCY,PR.PRIORITY,I.LOGICALREF,PR.PRICE
ORDER BY URUN_KODU ASC
====================================================================
SELECT
I.CODE AS 'URUN_KODU' ,
I.NAME AS 'URUN_ADI',
I.STGRPCODE AS 'GRUP KODU',
I.SPECODE AS 'ÖZEL KODU',
I.LOGICALREF ,
ISNULL(PR.PRICE,0)AS FIYAT,
ISNULL(PR.CURRENCY,0) AS DOVIZ,
sUM ((CASE S.TRCODE WHEN 1 THEN S.AMOUNT*UINFO2 ELSE 0 END )+ (CASE S.TRCODE WHEN 13 THEN S.AMOUNT*UINFO2 ELSE 0 END)+
(CASE F.TRCODE WHEN 3 THEN S.AMOUNT*UINFO2 ELSE 0 END )+(CASE S.TRCODE WHEN 14 THEN S.AMOUNT*UINFO2 ELSE 0 END)+
(CASE S.TRCODE WHEN 50 THEN S.AMOUNT*UINFO2 ELSE 0 END)) +
SUM((CASE F.TRCODE WHEN 8 THEN S.AMOUNT*UINFO2 ELSE 0 END)+(CASE S.TRCODE WHEN 6 THEN S.AMOUNT*UINFO2 ELSE 0 END)+
(CASE S.TRCODE WHEN 12 THEN S.AMOUNT*UINFO2 ELSE 0 END)+(CASE S.TRCODE WHEN 11 THEN S.AMOUNT*UINFO2 ELSE 0 END)+
(CASE S.TRCODE WHEN 51 THEN S.AMOUNT*UINFO2 ELSE 0 END)+(CASE S.TRCODE WHEN 20 THEN S.AMOUNT*UINFO2 ELSE 0 END)) AS 'STOK'
from LG_102_ITEMS AS I LEFT JOIN
LG_102_04_STLINE AS S ON I.LOGICALREF=S.STOCKREF AND I.ACTIVE='0' and S.CANCELLED='0' LEFT JOIN
LG_102_04_INVOICE AS F ON S.INVOICEREF=F.LOGICALREF LEFT JOIN
LG_102_PRCLIST AS PR ON I.LOGICALREF=PR.CARDREF and PR.PTYPE=2 LEFT JOIN
LG_102_04_STINVTOT AS ST ON I.LOGICALREF=ST.STOCKREF
GROUP BY I.CODE,I.NAME,I.STGRPCODE,I.SPECODE,PR.PRICE,PR.CURRENCY,PR.PRIORITY,I.LOGICALREF,PR.PRICE
ORDER BY URUN_KODU ASC
