71 lines
2.6 KiB
SQL
71 lines
2.6 KiB
SQL
-- Satınalma Faturaları Detaylı Döküm
|
||
|
||
|
||
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=2 THEN 'Perakende Satış İade Faturası'
|
||
WHEN INVOICE.TRCODE=3 THEN 'Toptan Satış İade Faturası'
|
||
WHEN INVOICE.TRCODE=4 THEN 'Alınan Hizmet Faturası'
|
||
WHEN INVOICE.TRCODE=5 THEN 'Alınan Proforma Faturası'
|
||
WHEN INVOICE.TRCODE=6 THEN 'Satınalma İade Faturası'
|
||
WHEN INVOICE.TRCODE=7 THEN 'Perakende Satış Faturası'
|
||
WHEN INVOICE.TRCODE=8 THEN 'Toptan Satış Faturası'
|
||
WHEN INVOICE.TRCODE=9 THEN 'Verilen Hizmet Faturası'
|
||
WHEN INVOICE.TRCODE=10 THEN 'Verilen Proforma Faturası'
|
||
WHEN INVOICE.TRCODE=12 THEN 'Alınan Vade Farkı Faturası'
|
||
WHEN INVOICE.TRCODE=13 THEN 'Satınalma Fiyat Farkı Faturası'
|
||
WHEN INVOICE.TRCODE=14 THEN 'Satış 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_ >= '2016-01-01 00:00:00.000'
|
||
--AND INVOICE.DATE_ <= '2016-12-31 23:59:59.999'
|
||
ORDER BY INVOICE.DATE_,
|
||
INVOICE.SOURCEINDEX,
|
||
INVOICE.FICHENO
|