SQL Sorgu Logo Tiger 3 Satış Faturalarında Maliyet Tablosu

SELECT

INVOICE.FICHENO AS FATURA_NO,

INVOICE.DOCODE AS BELGE_NO,

CLCARD.SPECODE2 AS CARİ_OZELKOD2,

CLCARD.DEFINITION_ AS CARİ_ADI,

INVOICE.SPECODE AS CARİ_ÖZELKOD1,

LG_SLSMAN.CODE AS SATIŞ_ELEMAN_KODU,

STLINE.AMOUNT*STLINE.PRICE AS TL_TOPLAM_TUTAR,

STLINE.AMOUNT*STLINE.OUTCOST AS TL_MALİYET,

STLINE.AMOUNT*STLINE.PRICE-STLINE.AMOUNT*STLINE.OUTCOST AS TL_KAR,

"RD_TUTARI"=CASE STLINE.REPORTRATE WHEN 0 THEN 0 ELSE STLINE.TOTAL/STLINE.REPORTRATE*(CASE WHEN STLINE.TRCODE IN (6) THEN -1 ELSE 1 END) END * (CASE WHEN STLINE.LINETYPE=1 THEN 0 ELSE 1 END),

STLINE.AMOUNT*STLINE.OUTCOSTCURR AS RD_ÇıkışMaliyeti,

"RD_KAR"=CASE STLINE.REPORTRATE WHEN 0 THEN 0 ELSE STLINE.TOTAL/STLINE.REPORTRATE*(CASE WHEN STLINE.TRCODE IN (6) THEN -1 ELSE 1 END) END * (CASE WHEN STLINE.LINETYPE=1 THEN 0 ELSE 1 END)-STLINE.AMOUNT*STLINE.OUTCOSTCURR

FROM

LG_001_01_STLINE STLINE

LEFT JOIN LG_001_01_STFICHE STFICHE WITH (NOLOCK) ON STLINE.STFICHEREF = STFICHE.LOGICALREF

LEFT JOIN LG_001_01_INVOICE INVOICE WITH (NOLOCK) ON INVOICE.LOGICALREF=STFICHE.INVOICEREF

LEFT JOIN LG_001_CLCARD CLCARD (NOLOCK) ON CLCARD.LOGICALREF=STFICHE.CLIENTREF

LEFT JOIN LG_001_PAYPLANS PAYPLANS (NOLOCK) ON PAYPLANS.LOGICALREF=STFICHE.PAYDEFREF

LEFT JOIN L_TRADGRP (NOLOCK) ON L_TRADGRP.GCODE=STFICHE.TRADINGGRP

LEFT JOIN L_CAPIDIV  (NOLOCK) ON L_CAPIDIV.NR=STFICHE.BRANCH AND  L_CAPIDIV.FIRMNR='001'

LEFT JOIN L_CAPIDEPT (NOLOCK) ON L_CAPIDEPT.NR=STFICHE.DEPARTMENT AND L_CAPIDEPT.FIRMNR='001'

LEFT JOIN L_CAPIFACTORY (NOLOCK)ON L_CAPIFACTORY.NR=STFICHE.FACTORYNR AND L_CAPIFACTORY.FIRMNR='001'

LEFT JOIN L_CAPIWHOUSE (NOLOCK)ON L_CAPIWHOUSE.NR=STFICHE.SOURCEINDEX AND L_CAPIWHOUSE.FIRMNR='001'

LEFT JOIN LG_001_SHIPINFO SHIPINFO (NOLOCK) ON SHIPINFO.LOGICALREF=STFICHE.SHIPINFOREF

LEFT JOIN LG_SLSMAN ON STFICHE.SALESMANREF = LG_SLSMAN.LOGICALREF AND LG_SLSMAN.FIRMNR='001'

LEFT JOIN LG_001_CLCARD CLC1 (NOLOCK) ON STFICHE.RECVREF=CLC1.LOGICALREF

LEFT JOIN LG_001_CLCARD CLC2 (NOLOCK) ON STFICHE.SHIPINFOREF=CLC2.LOGICALREF

LEFT JOIN L_SHPTYPES (NOLOCK) ON L_SHPTYPES.SCODE=STFICHE.SHPTYPCOD

LEFT JOIN L_SHPAGENT (NOLOCK) ON L_SHPAGENT.CODE=STFICHE.SHPAGNCOD

LEFT JOIN LG_001_ITEMS ITEMS (NOLOCK) ON ITEMS.LOGICALREF=STLINE.STOCKREF AND STLINE.LINETYPE IN (0,8)

LEFT JOIN LG_001_SRVCARD SRVCARD (NOLOCK) ON SRVCARD.LOGICALREF=STLINE.STOCKREF AND STLINE.LINETYPE=4

LEFT JOIN LG_001_ITMUNITA ITMUNITA (NOLOCK) ON ITMUNITA.UNITLINEREF=STLINE.UOMREF AND ITMUNITA.ITEMREF=STLINE.STOCKREF

LEFT JOIN LG_001_UNITSETL UNITSETL  (NOLOCK) ON STLINE.UOMREF = UNITSETL.LOGICALREF

LEFT JOIN LG_001_UNITSETF UNITSETF  (NOLOCK) ON STLINE.USREF = UNITSETF.LOGICALREF

LEFT JOIN LG_001_SPECODES SPECODES (NOLOCK) ON SPECODES.SPECODE=STLINE.DELVRYCODE AND SPECODES.CODETYPE=3 AND SPECODES.SPECODETYPE=0

LEFT JOIN LG_001_SPECODES SPECODE (NOLOCK) ON SPECODE.SPECODE=STLINE.SPECODE AND SPECODES.CODETYPE=1 AND SPECODES.SPECODETYPE=19

LEFT JOIN LG_001_01_ORFLINE ORFLINE  (NOLOCK) ON STLINE.ORDTRANSREF = ORFLINE.LOGICALREF

LEFT JOIN LG_001_01_ORFICHE ORFICHE  (NOLOCK) ON STLINE.ORDFICHEREF = ORFICHE.LOGICALREF

LEFT JOIN LG_001_PAYPLANS PAYPLANS2 (NOLOCK) ON PAYPLANS2.LOGICALREF=STLINE.PAYDEFREF

LEFT JOIN L_CURRENCYLIST (NOLOCK) ON L_CURRENCYLIST.CURTYPE=STLINE.TRCURR and L_CURRENCYLIST.FIRMNR='001'

LEFT JOIN LG_001_01_SLTRANS SLTRANS (NOLOCK) ON SLTRANS.STFICHEREF=STLINE.LOGICALREF

LEFT JOIN LG_001_FAREGIST FAREGIST (NOLOCK) ON FAREGIST.LOGICALREF=STLINE.FAREGREF

WHERE

INVOICE.CANCELLED=0

AND STLINE.LINETYPE IN (0,2,4,8)

AND INVOICE.TRCODE IN (2,3,7,8,9,10,14)

AND (STFICHE.BILLED=1 OR STFICHE.BILLED IS NULL)

AND INVOICE.TRCODE='8'

ORDER BY INVOICE.FICHENO