Logo Destek
Logo Plus Acil Destek (Plus versiyonlarına artık buradan destek verilecektir.) => Tiger 3 Enterprise => Konuyu başlatan: ehliyet - 10.11.2016, 11:25
-
Merhaba,
Aşağıda bir query hazırladım ama bir yerde takıldım.
Satış ve Alış faturalarını bir tabloda görmek adına bir tablo hazırlamak istiyorum.
Ama hizmet kodu alanında takıldım.
Fatura eğer satış faturası ise items tablosundan ama hizmet faturası ise svrcard tablsusundan veri almam lazım sanırım ama beceremedim.
Yazdığım kodu aşağıda paylaşıyorum çözüm olursa herkese lazım olur diye düşünüyorum.
Alan ya boş geliyor yada eksik bigli geliyor. Çünkü kesilen fatura hizmet mi satış mı trcode dan ayırıyorum ama stok kodunu ayıramadım.
SELECT
LG_036_01_STLINE.DATE_ AS TARIH ,
LG_036_01_INVOICE.FICHENO AS BELGENO,
LG_036_01_INVOICE.DOCODE AS FISNUMARASI,
LG_036_CLCARD.CODE AS CARIKOD,
LG_036_CLCARD.DEFINITION_ AS CARI_ACIKLAMASI ,
LG_036_CLCARD.TAXOFFICE AS VERGİDAİRESİ ,
LG_036_CLCARD.TAXNR AS VERDİNO ,
LG_036_01_STLINE.TRCODE AS FISTURU,
QDM_INVOICETRCODES.INVOICETRDEFINITION AS FATURA_TURU,
MALZEMEHIZMETKODU= CASE
WHEN LG_036_01_STLINE.TRCODE IN(4,3,1) THEN I.CODE
WHEN LG_036_01_STLINE.TRCODE IN(7,8,1) THEN C.CODE
ELSE -1
END ,
--LG_036_ITEMS.CODE AS MALZEMEHIZMETKODU,
--LG_036_ITEMS.NAME AS MALZEMEHIZMETACIKLAMASI,
LG_036_01_STLINE.AMOUNT AS MIKTAR ,
LG_036_UNITSETL.NAME AS BIRIM ,
LG_036_01_STLINE.VATMATRAH AS MATRAHI,
LG_036_01_STLINE.VAT AS KDVORANI,
LG_036_01_STLINE.VATAMNT AS KDVTUTARI ,
LG_036_01_INVOICE.NETTOTAL AS NETTUTAR,
LG_036_EMUHACC.CODE AS MUHASEBEKODU,
LG_036_EMUHACC.DEFINITION_ AS MUHASEBEKOCUACIKLAMASI
FROM LG_036_01_STLINE
LEFT OUTER JOIN LG_036_CLCARD ON LG_036_CLCARD.LOGICALREF = LG_036_01_STLINE.CLIENTREF
LEFT OUTER JOIN LG_036_01_INVOICE ON LG_036_01_STLINE.INVOICEREF = LG_036_01_INVOICE.LOGICALREF
LEFT OUTER JOIN LG_036_UNITSETL ON LG_036_01_STLINE.UOMREF = LG_036_UNITSETL.LOGICALREF
LEFT OUTER JOIN QDM_INVOICETRCODES ON LG_036_01_STLINE.TRCODE = QDM_INVOICETRCODES.INVOICETRCODE
LEFT OUTER JOIN LG_036_EMUHACC ON LG_036_01_STLINE.ACCOUNTREF = LG_036_EMUHACC.LOGICALREF
LEFT OUTER JOIN LG_036_ITEMS ON LG_036_01_STLINE.STOCKREF = LG_036_ITEMS.LOGICALREF
LEFT OUTER JOIN LG_036_SRVCARD C ON LG_036_01_STLINE.ACCOUNTREF = C.LOGICALREF
LEFT OUTER JOIN LG_036_ITEMS I ON LG_036_01_STLINE.STOCKREF = I.LOGICALREF
Şimdiden teşekkür ederim.
-
case eklemeniz gerekiyor:
(CASE WHEN F.TRCODE=1 THEN M.NAME WHEN F.TRCODE=4 THEN H.DEFINITION_ END) AS [MALZEME / HİZMET ADI],
bu arada f fatura tablosu, m malzemeler tablosu, h hizmet kartları...
-
Aşağıdaki şekilde yaptım veriler geldi ama doğruluğunu test edemedim henüz. Teşekkür ederim. Bilgi için. Çok işime yaradı.
USE TIGER
SELECT
LG_036_01_STLINE.DATE_ AS TARIH ,
LG_036_01_INVOICE.FICHENO AS BELGENO,
LG_036_01_INVOICE.DOCODE AS FISNUMARASI,
LG_036_CLCARD.CODE AS CARIKOD,
LG_036_CLCARD.DEFINITION_ AS CARI_ACIKLAMASI ,
LG_036_CLCARD.TAXOFFICE AS VERGİDAİRESİ ,
LG_036_CLCARD.TAXNR AS VERDİNO ,
LG_036_01_STLINE.TRCODE AS FISTURU,
QDM_INVOICETRCODES.INVOICETRDEFINITION AS FATURA_TURU,
MALZEMEHIZMETKODU= CASE
WHEN LG_036_01_STLINE.TRCODE IN(4,3,1) THEN C.CODE
WHEN LG_036_01_STLINE.TRCODE IN(7,8,1) THEN I.CODE
ELSE ' '
END ,
MALZEMEHIZMETACIKLAMASI = CASE
WHEN LG_036_01_STLINE.TRCODE IN(4,3,1) THEN C.DEFINITION_
WHEN LG_036_01_STLINE.TRCODE IN(7,8,1) THEN I.NAME
ELSE ' '
END ,
LG_036_01_STLINE.AMOUNT AS MIKTAR ,
LG_036_UNITSETL.NAME AS BIRIM ,
LG_036_01_STLINE.VATMATRAH AS MATRAHI,
LG_036_01_STLINE.VAT AS KDVORANI,
LG_036_01_STLINE.VATAMNT AS KDVTUTARI ,
LG_036_01_INVOICE.NETTOTAL AS NETTUTAR,
LG_036_EMUHACC.CODE AS MUHASEBEKODU,
LG_036_EMUHACC.DEFINITION_ AS MUHASEBEKOCUACIKLAMASI
FROM LG_036_01_STLINE
LEFT OUTER JOIN LG_036_CLCARD ON LG_036_CLCARD.LOGICALREF = LG_036_01_STLINE.CLIENTREF
LEFT OUTER JOIN LG_036_01_INVOICE ON LG_036_01_STLINE.INVOICEREF = LG_036_01_INVOICE.LOGICALREF
LEFT OUTER JOIN LG_036_UNITSETL ON LG_036_01_STLINE.UOMREF = LG_036_UNITSETL.LOGICALREF
LEFT OUTER JOIN QDM_INVOICETRCODES ON LG_036_01_STLINE.TRCODE = QDM_INVOICETRCODES.INVOICETRCODE
LEFT OUTER JOIN LG_036_EMUHACC ON LG_036_01_STLINE.ACCOUNTREF = LG_036_EMUHACC.LOGICALREF
LEFT OUTER JOIN LG_036_ITEMS ON LG_036_01_STLINE.STOCKREF = LG_036_ITEMS.LOGICALREF
LEFT OUTER JOIN LG_036_SRVCARD C ON LG_036_01_STLINE.STOCKREF = C.LOGICALREF
LEFT OUTER JOIN LG_036_ITEMS I ON LG_036_01_STLINE.STOCKREF = I.LOGICALREF
WHERE LG_036_01_STLINE.INVOICEREF = '113'