Faturalardaki Stok Kodu ve adı ile alakalı.

[ehliyet]

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.

[Koray Ü.]

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ı...

[ehliyet]

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'