SQL Sorgu Logo SQL den excelle irsaliye bilgileri çekme

Aşağıdaki kod ile excelle irsaliye bilgilerini çekebilirsiniz.



SELECT YEAR(INV.DATE_) YIL,MONTH(INV.DATE_)AY,
"FIS TURU"=CASE WHEN STL.TRCODE IN (1) THEN 'Alış İrsaliyesi'
WHEN STL.TRCODE IN (8) THEN 'Satış İrsaliyesi'
WHEN STL.TRCODE IN (7) THEN 'Satış İade'
WHEN STL.TRCODE IN (6) THEN 'Satınalma İade İrsaliyesi' END,
AY=CASE MONTH(STL.DATE_)
WHEN 1 THEN '01.OCAK'
WHEN 2 THEN '02.ŞUBAT'
WHEN 3 THEN '03.MART'
WHEN 4 THEN '04.NİSAN'
WHEN 5 THEN '05.MAYIS'
WHEN 6 THEN '06.HAZİRAN'
WHEN 7 THEN '07.TEMMUZ'
WHEN 8 THEN '08.AĞUSTOS'
WHEN 9 THEN '09.EYLÜL'
WHEN 10 THEN '10.EKİM'
WHEN 11 THEN '11.KASIM'
WHEN 12 THEN '12.ARALIK'
ELSE 'DÝÐER'
END ,

"FATURA NO"=INV.FICHENO,"FATURA TARIHI"=INV.DATE_,
"IRSALIYE NO"=STF.FICHENO,"IRSALIYE TARIHI"=STL.DATE_,
"SATICI KODU"=CLC.CODE,"SATICI ADI"=CLC.DEFINITION_,
"MALZEME KODU"=ITM.CODE,"MALZEME ACIKLAMA"=ITM.NAME,"MALZEME ACIKLAMA2"=ITM.NAME2,
"MALZEME OK1"=ITM.SPECODE,"MALZEME OK2"=ITM.SPECODE2,"MALZEME OK3"=ITM.SPECODE3,"MALZEME OK4"=ITM.SPECODE4,"MALZEME OK5"=ITM.SPECODE5,
"MALZEME GRUP KODU"=ITM.STGRPCODE,"MALZEME YETKI KODU"=ITM.CYPHCODE,

"MIKTAR"=CASE WHEN STL.TRCODE IN (1) THEN AMOUNT ELSE AMOUNT*-1 END,
"BIRIM"=UNI.CODE,
"FIYAT"=STL.PRICE,
"TUTAR"=CASE WHEN STL.TRCODE IN (1) THEN STL.TOTAL ELSE STL.TOTAL*-1 END,
"NET TUTAR"=CASE WHEN STL.TRCODE IN (1) THEN STL.LINENET ELSE STL.LINENET*-1 END,
"NET FIYAT"=CASE WHEN STL.TRCODE IN (1) THEN STL.LINENET/STL.AMOUNT ELSE STL.LINENET/STL.AMOUNT END,

"FD FIYAT"= CASE WHEN PRRATE<>0 THEN((LINENET/AMOUNT)/PRRATE) ELSE '' END,
"FD DOVIZ TURU"=CASE PRCURR WHEN 0 THEN 'TL' WHEN 1 THEN 'USD'WHEN 20 THEN 'EUR' WHEN 160 THEN 'TL' ELSE '' END,
"SON FD FIYAT"= (SELECT TOP 1 CASE WHEN PRRATE<>0 THEN (LINENET/AMOUNT)/PRRATE ELSE '' END FROM LG_020_04_STLINE STL2 WHERE STL2.LINETYPE=0 AND STL2.STOCKREF=STL.STOCKREF AND STL2.CLIENTREF=STL.CLIENTREF
AND STL2.LINENET<>0 AND TRCODE=1 ORDER BY STL2.DATE_ DESC),
"SON FD DOVIZ TURU"=CASE (SELECT TOP 1 PRCURR FROM LG_020_04_STLINE STL2 WHERE STL2.LINETYPE=0 AND STL2.STOCKREF=STL.STOCKREF AND STL2.CLIENTREF=STL.CLIENTREF
AND STL2.LINENET<>0 AND TRCODE=1 ORDER BY STL2.DATE_ DESC)
WHEN 0 THEN 'TL' WHEN 1 THEN 'USD'WHEN 20 THEN 'EUR' WHEN 160 THEN 'TL' ELSE '' END,

PRRATE,
"LISTE FIYAT"=ISNULL((SELECT TOP 1 PRICE FROM LG_020_PRCLIST PRC WHERE PTYPE=1 AND PRC.CARDREF=STL.STOCKREF AND STL.DATE_ BETWEEN PRC.BEGDATE AND PRC.ENDDATE
AND PRC.CURRENCY=STL.TRCURR ),0),
"LISTE TUTAR"=ISNULL((SELECT TOP 1 PRICE FROM LG_020_PRCLIST PRC WHERE PTYPE=1 AND PRC.CARDREF=STL.STOCKREF AND STL.DATE_ BETWEEN PRC.BEGDATE AND PRC.ENDDATE
AND PRC.CURRENCY=STL.TRCURR ),0)*STL.AMOUNT,


"AMBAR NO"=STL.SOURCEINDEX,"AMBAR ADI"=WHO.NAME,
"ISLEM DOVIZI"=CASE STL.TRCURR WHEN 1 THEN 'USD' WHEN 20 THEN 'EUR' ELSE 'TL' END,
"ID NET TUTAR"=CASE WHEN STL.TRCODE IN (7,8,35) AND STL.TRRATE <>0 THEN STL.LINENET/STL.TRRATE
WHEN STL.TRCODE IN (2,3) AND STL.TRRATE <>0 THEN (STL.LINENET*-1)/STL.TRRATE
WHEN STL.TRCODE IN (7,8,35) AND STL.TRRATE =0 THEN STL.LINENET
WHEN STL.TRCODE IN (2,3) AND STL.TRRATE =0 THEN (STL.LINENET*-1) END,

"ID NET MALIYET"=CASE WHEN STL.TRCODE IN (7,8,35) AND STL.TRRATE <>0 THEN (STL.OUTCOST*STL.AMOUNT)/STL.TRRATE
WHEN STL.TRCODE IN (2,3) AND STL.TRRATE <>0 THEN ((STL.OUTCOST*STL.AMOUNT)*-1)/STL.TRRATE
WHEN STL.TRCODE IN (7,8,35) AND STL.TRRATE =0 THEN STL.OUTCOST*STL.AMOUNT
WHEN STL.TRCODE IN (2,3) AND STL.TRRATE =0 THEN ((STL.OUTCOST*STL.AMOUNT)*-1) END


FROM LG_020_04_STLINE STL
LEFT JOIN LG_020_ITEMS ITM ON ITM.LOGICALREF=STL.STOCKREF
LEFT JOIN LG_020_04_INVOICE INV ON INV.LOGICALREF=STL.INVOICEREF
LEFT JOIN LG_020_04_STFICHE STF ON STF.LOGICALREF=STL.STFICHEREF
LEFT JOIN LG_020_CLCARD CLC ON CLC.LOGICALREF=STL.CLIENTREF
LEFT JOIN LG_SLSMAN SLS ON SLS.LOGICALREF=STL.SALESMANREF
LEFT JOIN LG_020_UNITSETL UNI ON UNI.LOGICALREF=STL.UOMREF
LEFT JOIN L_CAPIWHOUSE WHO ON WHO.NR=STL.SOURCEINDEX AND WHO.FIRMNR=211
WHERE STL.TRCODE IN (7,8,1,6) AND LINETYPE IN (0,1) AND LINENET<>0