Page History

SELECT 
"Firma"= '$(vFirmNumber)', 
"Donem"= '$(vPeriodNumber)',
CAPIFIRM.NAME AS "Firma_Adı",
STLINE.LOGICALREF AS "KAYIT_ADRESI",
-----------------
case when STLINE.INVOICEREF<>0 THEN 'Faturalaşmış' ELSE 'Faturalaşmamış' END AS "FATURALASMIS_MI",
STLINE.TRCODE AS "FATURA_TURU_NO",

FATURA_TURU= CASE INVOICE.TRCODE
    when 1 then 'MAL_ALIM_FATURASI'
       when 2 then 'Parekende Satış İade Faturası'
       when 3 then 'Toptan Satış İade Faturası'
       when 4 then 'Alınan Hizmet Faturası'
       when 5 then 'Alınan Proforma Faturası'
       when 6 then 'Alım İade Faturası'
       when 7 then 'Parekende Satış Faturası'
       when 8 then 'Toptan Satış Faturası'
       when 9 then 'Verilen Hizmet Faturası'
       when 10 then 'Verilen Proforma Faturası'
       when 13 then 'Alınan Fiyat Farkı Faturası'
       when 14 then 'Verilen Fiyat Farkı Faturası'
       when 26 then 'Mustahsil Faturası'
       when 35 then 'Depozito Devir Fişi'
       when 36 then 'Depozito Satış Fişi'
       when 37 then 'Değişim Fişi'
       ELSE CONVERT(VARCHAR,INVOICE.TRCODE) END,


STFICHE.FICHENO AS "IRSALIYE_NO",
INVOICE.FICHENO AS "FATURA_NO",
INVOICE.DATE_ AS "TARIH",
INVOICE.DOCODE AS "BELGE_NO",
"CARI_HESAP_KODU"=CLCARD.CODE ,
"CARI_HESAP_UNVANI"= CLCARD.DEFINITION_ ,
CLCARD.TAXOFFICE AS "CARI_HESAP_VERGI_DAIRESI",
CLCARD.TAXNR AS "CARI_HESAP_VERGI_NUMARASI",
CLCARD.SPECODE AS "CH_OZEL_KODU",
CLCARD.SPECODE2 AS "CH_OZEL_KODU2",
CLCARD.SPECODE3 AS "CH_OZEL_KODU3",
CLCARD.SPECODE4 AS "CH_OZEL_KODU4",
CLCARD.SPECODE5 AS "CH_OZEL_KODU5",
CLCARD.CYPHCODE AS "CH_YETKI_KODU",
CLCARD.CITY AS "CH_SEHIR",
CLCARD.COUNTRY AS "CH_ULKE",
CLCARD.TOWN AS "CH_ILCE",
ISNULL (PAYPLANS.CODE,'') AS "ODEME_PLANI_KODU",
ISNULL (PAYPLANS.DEFINITION_,'') AS "ODEME_PLANI_ACIKLAMASI",
L_CAPIDIV.NAME AS "ISYERI", L_CAPIDIV.NR AS "ISYERI_NO",
L_CAPIDEPT.NAME AS "BOLUM", L_CAPIDEPT.NR AS "BOLUM_NO",
L_CAPIFACTORY.NAME AS "FABRIKA",
L_CAPIWHOUSE.NAME AS "AMBAR",
INVOICE.SPECODE AS "FATURA_OZEL_KODU",
INVOICE.CYPHCODE AS "FATURA_YETKI_KODU",
STFICHE.SPECODE AS "IRSALIYE_OZEL_KODU",
STFICHE.CYPHCODE AS "IRSALIYE_YETKI_KODU",
ISNULL(LG_SLSMAN.CODE,'') AS "SATIS_ELEMANI_KODU",
ISNULL(LG_SLSMAN.DEFINITION_,'') AS "SATIS_ELEMANI_ADI",

CASE WHEN STLINE.LINETYPE IN(4,11) THEN SRVCARD.SPECODE ELSE ITEMS.SPECODE END AS "MALZEME_OZEL_KODU",
ITEMS.SPECODE2 AS "MALZEME_OZEL_KODU2",
ITEMS.SPECODE3 AS "MALZEME_OZEL_KODU3",
ITEMS.SPECODE4 AS "MALZEME_OZEL_KODU4",
ITEMS.SPECODE5 AS "MALZEME_OZEL_KODU5",
CASE WHEN STLINE.LINETYPE IN(4,11) THEN SRVCARD.CYPHCODE ELSE ITEMS.CYPHCODE END AS "MALZEME_YETKI_KODU",

ITEMS.EXPCTGNO AS "IHRACAT_KATEGORI_NO",
STLINE.SPECODE AS SATIR_OZEL_KODU,
STLINE.SPECODE2 AS SATIR_OZEL_KODU2,

"SATIR_TURU"=CASE STLINE.LINETYPE
             WHEN 0 THEN  'Stok'
             WHEN 1 THEN  'Promosyon'
             WHEN 2 THEN  'İndirim'
             WHEN 3 THEN  'Masraf'
             WHEN 4 THEN  'Hizmet'
             WHEN 5 THEN  'Depozito'
             WHEN 6 THEN  'Karmakoli'
             WHEN 7 THEN  'Karmakoli Satırı'
             WHEN 8 THEN  'Sabit Kıymet'
             WHEN 11 THEN 'Fason'
             ELSE CONVERT(VARCHAR,STLINE.LINETYPE) END,

AY=CASE MONTH(STLINE.DATE_)
   WHEN 1 THEN '01_Ocak'
   WHEN 2 THEN '02_Şubat'
   WHEN 3 THEN '03_Mart'
   WHEN 4 THEN '04_Nisan'
   WHEN 5 THEN '05_Mayıs'
   WHEN 6 THEN '06_Haziran'
   WHEN 7 THEN '07_Temmuz'
   WHEN 8 THEN '08_Ağustos'
   WHEN 9 THEN '09_Eylül'
   WHEN 10 THEN '10_Ekim'
   WHEN 11 THEN '11_Kasım'
   WHEN 12 THEN '12_Aralık'
   END,

ISNULL(STGRP.SPECODE, converT(VARCHAR, ITEMS.STGRPCODE)) AS "STOK_GRUP_KODU",
ISNULL(STGRP.DEFINITION_, NULL) AS "STOK_GRUP_ADI",
"STOK_KODU"=CASE WHEN STLINE.LINETYPE IN(4,11) THEN SRVCARD.CODE ELSE ITEMS.CODE END,
"STOK_ADI"=CASE WHEN STLINE.LINETYPE IN(4,11) THEN SRVCARD.DEFINITION_ ELSE ITEMS.NAME END,
"MIKTAR"=CASE WHEN INVOICE.TRCODE=14 THEN 0 ELSE CONVERT(DECIMAL(18,3), STLINE.AMOUNT) END,
"NET_MIKTAR"= CASE WHEN INVOICE.TRCODE=14 THEN 0 WHEN STLINE.TRCODE IN (2,3)
THEN CONVERT(DECIMAL(18,3), STLINE.AMOUNT)*-1
ELSE CONVERT(DECIMAL(18,3), STLINE.AMOUNT) END,
CONVERT(DECIMAL(18,3), STLINE.PRICE)* (CASE WHEN STLINE.LINETYPE=1 THEN 0 ELSE 1 END) *(CASE WHEN INVOICE.TRCODE=14 AND INVOICE.DECPRDIFF=1 THEN -1 ELSE 1 END)AS "BIRIM_FIYAT",
CONVERT(DECIMAL(18,3), CASE WHEN STLINE.TRCODE IN (2,3) THEN STLINE.TOTAL*-1 ELSE STLINE.TOTAL END) * (CASE WHEN STLINE.LINETYPE=1 THEN 0 ELSE 1 END) *(CASE WHEN INVOICE.TRCODE=14 AND INVOICE.DECPRDIFF=1 THEN -1 ELSE 1 END)AS "TUTAR",
CASE WHEN STLINE.LINETYPE=7 THEN STLINE.TOTAL/KK.TOTAL*KK.DISTDISC ELSE STLINE.DISTDISC END
       *  CASE WHEN STLINE.TRCODE IN (2,3) THEN -1 ELSE 1 END
       * (CASE WHEN STLINE.LINETYPE=1 THEN 0 ELSE 1 END)AS "INDIRIM",
CONVERT(DECIMAL(18,3), CASE WHEN STLINE.TRCODE IN (2,3) THEN -1 ELSE 1 END)* (CASE WHEN STLINE.LINETYPE=1 THEN STLINE.LINENET END) AS "PROMOSYON",
CASE WHEN STLINE.LINETYPE=7 THEN STLINE.TOTAL/KK.TOTAL*KK.DISTEXP ELSE STLINE.DISTEXP END
       *  CASE WHEN STLINE.TRCODE IN (2,3) THEN -1 ELSE 1 END
       * (CASE WHEN STLINE.LINETYPE=1 THEN 0 ELSE 1 END) AS "MASRAF",
"KDV_ORANI"=STLINE.VAT,
CASE WHEN STLINE.LINETYPE=7 THEN STLINE.VATMATRAH ELSE CONVERT(DECIMAL(18,3), STLINE.LINENET) END * (CASE WHEN STLINE.LINETYPE=1 THEN 0 ELSE 1 END) *(CASE WHEN INVOICE.TRCODE IN (2,3) THEN -1 ELSE 1 END)*(CASE WHEN INVOICE.TRCODE=14 AND INVOICE.DECPRDIFF=1 THEN -1 ELSE 1 END) AS "NET_SATIS_TUTARI",

CASE WHEN STLINE.LINETYPE=6 THEN KKS.VATMATRAH ELSE CONVERT(DECIMAL(18, 3), STLINE.VATMATRAH) END * (CASE WHEN STLINE.LINETYPE=1 THEN 0 ELSE 1 END)*(CASE WHEN INVOICE.TRCODE IN (2,3) THEN -1 ELSE 1 END)*(CASE WHEN INVOICE.TRCODE=14 AND INVOICE.DECPRDIFF=1 THEN -1 ELSE 1 END)AS "KDV_MATRAHI",
INVOICE.REPORTRATE AS "RAPORLAMA_DOVIZ_KURU",
"RD_BIRIM_FIYATI"=CASE STLINE.REPORTRATE WHEN 0 THEN 0 ELSE STLINE.PRICE/STLINE.REPORTRATE*(CASE WHEN STLINE.TRCODE IN (2,3) THEN -1 ELSE 1 END)* (CASE WHEN STLINE.LINETYPE=1 THEN 0 ELSE 1 END) *(CASE WHEN INVOICE.TRCODE=14 AND INVOICE.DECPRDIFF=1 THEN -1 ELSE 1 END) END,
"RD_TUTARI"=CASE STLINE.REPORTRATE WHEN 0 THEN 0 ELSE STLINE.TOTAL/STLINE.REPORTRATE*(CASE WHEN STLINE.TRCODE IN (2,3) THEN -1 ELSE 1 END) END * (CASE WHEN STLINE.LINETYPE=1 THEN 0 ELSE 1 END)*(CASE WHEN INVOICE.TRCODE=14 AND INVOICE.DECPRDIFF=1 THEN -1 ELSE 1 END),
"RD_NET_SATIR_TUTARI"=CASE STLINE.REPORTRATE WHEN 0 THEN 0 ELSE STLINE.LINENET/STLINE.REPORTRATE*(CASE WHEN STLINE.TRCODE IN (2,3) THEN -1 ELSE 1 END) END * (CASE WHEN STLINE.LINETYPE=1 THEN 0 ELSE 1 END)*(CASE WHEN INVOICE.TRCODE=14 AND INVOICE.DECPRDIFF=1 THEN -1 ELSE 1 END),
STLINE.DIFFPRICE * (CASE WHEN STLINE.LINETYPE=1 THEN 0 ELSE 1 END)AS "FIYAT_FARKI",
STLINE.DIFFPRCOST * (CASE WHEN STLINE.LINETYPE=1 THEN 0 ELSE 1 END)AS "FIYAT_FARKI_MALIYETI",
CASE WHEN INVOICE.TRCODE=14 AND STLINE.DECPRDIFF=0 THEN 'ARTIRILACAK' WHEN STLINE.DECPRDIFF=1 THEN 'AZALTILACAK' ELSE '' END AS "FIYAT_FARKI_ACIKLAMA",
UNITSETL.CODE AS "BIRIM_KODU",
UNITSETF.CODE AS "BIRIM_SETI_KODU",
ISNULL(ORFICHE.FICHENO,'') AS "SIPARIS_FIS_NO",
ISNULL(ORFICHE.DATE_,NULL) AS "SIPARIS_FIS_TARIHI",
ISNULL(PAYPLANS2.CODE,'') AS "SATIR_ODEME_PLANI_KODU",
ISNULL(PAYPLANS2.DEFINITION_,'') AS "SATIR_ODEME_PLANI_ACIKLAMASI",
STLINE.OUTCOST AS "CIKIS_MALIYETI",
CIKIS_MALIYETI_RD= (CASE WHEN STLINE.IOCODE IN (3,4) THEN (CASE WHEN ITEMS.TRACKTYPE=1 THEN STLINE.UINFO2 ELSE STLINE.AMOUNT*STLINE.UINFO2 END*STLINE.OUTCOSTCURR) ELSE 0 END),
STLINE.LINEEXP AS "SATIR_ACIKLAMASI",
CLC1.CODE AS "SEVKIYAT_HESABI_KODU",
CLC1.DEFINITION_ AS "SEVKIYAT_HESABI_UNVANI",
SHIPINFO.CODE AS "SEVLIYAT_ADRESI_KODU",
SHIPINFO.NAME AS "SEVKIYAT_ADRESI_ACIKLAMASI",
STFICHE.DOCTRACKINGNR AS "DOKUMAN_IZLEME_NUMARASI",
STFICHE.SHPTYPCOD AS "SEVKIYAT_TURU",

STFICHE.SHPAGNCOD AS "TASIYICI_KODU",
STFICHE.TRACKNR AS "PAKET_KOLI_NO",
STFICHE.DISTORDERREF AS "DAGITIM_EMRI_REFERANSI",
"HIZMET_ACIKLAMASI"=
CASE WHEN STLINE.LINETYPE IN(4,11) THEN
(SELECT DEFINITION_
FROM  $(vFirmDbName).dbo.LG_$(vFirmNumber)_SRVCARD (NOLOCK)
WHERE (LOGICALREF = STLINE.STOCKREF))
ELSE NULL END,
STLINE.INVOICEREF AS "FATURA_REF",
STFICHE.LOGICALREF AS "STFICHE_REF",
RIGHT('00' + ISNULL(CLCARD.CITYCODE,''),2) AS PLAKA,
CONVERT(DECIMAL(18,3), CASE WHEN INVOICE.TRCODE IN (2,3) THEN STLINE.LINENET*-1 ELSE STLINE.LINENET END) AS "SATIR_NET_TUTARI",
ISNULL(SSLSMAN.CODE,'') AS "SATIS_ELEMANI_KODU_SATIR",
ISNULL(SSLSMAN.DEFINITION_,'') AS "SATIS_ELEMANI_ADI_SATIR",
"ISLEM_DVZ_TURU"=ID_KUR.CURCODE,
"ISLEM_DVZ_BIRIM_FIYATI"=CASE STLINE.TRRATE WHEN 0 THEN 0 ELSE STLINE.PRICE/STLINE.TRRATE END*(CASE WHEN STLINE.LINETYPE=1 OR INVOICE.TRCODE=14 THEN 0 ELSE 1 END),

"ISLEM_DVZ_TUTARI"=CASE STLINE.TRRATE WHEN 0 THEN 0 ELSE STLINE.TOTAL/STLINE.TRRATE END*(CASE WHEN STLINE.LINETYPE=1 THEN 0 WHEN STLINE.DECPRDIFF=0 AND INVOICE.TRCODE=14 THEN -1 ELSE 1 END)*(case WHEN STLINE.TRCODE IN (2,3) THEN -1 ELSE 1 END),
"ISLEM_DVZ_NET_ALIM_TUTARI"=CASE STLINE.TRRATE WHEN 0 THEN 0 ELSE STLINE.LINENET/STLINE.TRRATE END*(CASE WHEN STLINE.LINETYPE=1 THEN 0 WHEN STLINE.DECPRDIFF=0 AND INVOICE.TRCODE=14 THEN -1 ELSE 1 END)*(case WHEN STLINE.TRCODE IN (2,3) THEN -1 ELSE 1 END),
AYNO=RIGHT('0' + CONVERT(VARCHAR,MONTH(STLINE.DATE_)),2),
YIL=YEAR(STLINE.DATE_),
PROJECT_LINE.CODE AS "PROJE_KODU_SATIR",
PROJECT_LINE.NAME AS "PROJE_ADI_SATIR",
PROJECT.CODE AS "PROJE_KODU_GENEL", 
PROJECT.NAME AS "PROJE_ADI_GENEL", 
PROJECT.SPECODE AS "PROJE_OZEL_KODU_GENEL", 
PROJECT.CYPHCODE AS "PROJE_YETKI_KODU_GENEL",
STLINE.LOGICALREF AS STLINE_LOGREF,
STFICHE.LOGICALREF AS STFICHE_LOGREF, 
INVOICE.LOGICALREF AS INVOICE_LOGREF,
EMCENTER.CODE AS "MASRAF_MERK_KODU",
EMCENTER.DEFINITION_ AS "MASRAF_MERK_ACIKLAMA"


FROM $(vFirmDbName).dbo.LG_$(vFirmNumber)_$(vPeriodNumber)_STLINE STLINE (NOLOCK)
LEFT JOIN $(vFirmDbName).dbo.LG_$(vFirmNumber)_$(vPeriodNumber)_STFICHE STFICHE WITH (NOLOCK) ON STLINE.STFICHEREF = STFICHE.LOGICALREF
LEFT JOIN $(vFirmDbName).dbo.LG_$(vFirmNumber)_$(vPeriodNumber)_INVOICE INVOICE WITH (NOLOCK) ON INVOICE.LOGICALREF=STLINE.INVOICEREF
LEFT JOIN $(vFirmDbName).dbo.LG_$(vFirmNumber)_CLCARD CLCARD (NOLOCK) ON CLCARD.LOGICALREF=(CASE WHEN STLINE.LINETYPE IN (0,4,11) THEN INVOICE.CLIENTREF ELSE STFICHE.CLIENTREF END)
LEFT JOIN $(vFirmDbName).dbo.LG_$(vFirmNumber)_PAYPLANS PAYPLANS (NOLOCK) ON PAYPLANS.LOGICALREF=STFICHE.PAYDEFREF
LEFT JOIN $(vMasterDbName).[dbo].L_CAPIDIV L_CAPIDIV (NOLOCK) ON L_CAPIDIV.NR=INVOICE.BRANCH AND L_CAPIDIV.FIRMNR='$(vFirmNumber)'
LEFT JOIN $(vMasterDbName).[dbo].L_CAPIDEPT L_CAPIDEPT (NOLOCK) ON L_CAPIDEPT.NR=INVOICE.DEPARTMENT AND L_CAPIDEPT.FIRMNR='$(vFirmNumber)'
LEFT JOIN $(vMasterDbName).dbo.L_CAPIFIRM CAPIFIRM (NOLOCK) ON CAPIFIRM.NR='$(vFirmNumber)' 
LEFT JOIN $(vMasterDbName).[dbo].L_CAPIFACTORY L_CAPIFACTORY (NOLOCK)ON L_CAPIFACTORY.NR=INVOICE.FACTORYNR AND L_CAPIFACTORY.FIRMNR='$(vFirmNumber)'
LEFT JOIN $(vMasterDbName).[dbo].L_CAPIWHOUSE L_CAPIWHOUSE (NOLOCK)ON L_CAPIWHOUSE.NR=STLINE.SOURCEINDEX AND L_CAPIWHOUSE.FIRMNR='$(vFirmNumber)'
LEFT JOIN $(vFirmDbName).dbo.LG_$(vFirmNumber)_SHIPINFO SHIPINFO (NOLOCK) ON SHIPINFO.LOGICALREF=STFICHE.SHIPINFOREF
LEFT JOIN $(vMasterDbName).[dbo].LG_SLSMAN LG_SLSMAN ON INVOICE.SALESMANREF = LG_SLSMAN.LOGICALREF AND (LG_SLSMAN.FIRMNR='$(vFirmNumber)' OR LG_SLSMAN.FIRMNR='-1')
LEFT JOIN $(vFirmDbName).dbo.LG_$(vFirmNumber)_CLCARD CLC1 (NOLOCK) ON STFICHE.RECVREF=CLC1.LOGICALREF
LEFT JOIN $(vFirmDbName).dbo.LG_$(vFirmNumber)_ITEMS ITEMS (NOLOCK) ON ITEMS.LOGICALREF=STLINE.STOCKREF AND STLINE.LINETYPE IN (0,1,6,8)
LEFT JOIN $(vFirmDbName).dbo.LG_$(vFirmNumber)_SRVCARD SRVCARD (NOLOCK) ON SRVCARD.LOGICALREF=STLINE.STOCKREF AND STLINE.LINETYPE IN (4,11)
LEFT JOIN $(vFirmDbName).dbo.LG_$(vFirmNumber)_SPECODES STGRP (NOLOCK) ON STGRP.SPECODE=ITEMS.STGRPCODE collate database_default AND STGRP.CODETYPE=4 AND STGRP.SPECODETYPE=0 AND STLINE.LINETYPE=0
LEFT JOIN $(vFirmDbName).dbo.LG_$(vFirmNumber)_ITMUNITA ITMUNITA (NOLOCK) ON ITMUNITA.UNITLINEREF=STLINE.UOMREF AND ITMUNITA.ITEMREF=STLINE.STOCKREF AND ITMUNITA.VARIANTREF=STLINE.VARIANTREF
LEFT JOIN $(vFirmDbName).dbo.LG_$(vFirmNumber)_UNITSETL UNITSETL (NOLOCK) ON STLINE.UOMREF = UNITSETL.LOGICALREF
LEFT JOIN $(vFirmDbName).dbo.LG_$(vFirmNumber)_UNITSETF UNITSETF (NOLOCK) ON STLINE.USREF = UNITSETF.LOGICALREF
LEFT JOIN $(vFirmDbName).dbo.LG_$(vFirmNumber)_$(vPeriodNumber)_ORFLINE ORFLINE (NOLOCK) ON STLINE.ORDTRANSREF = ORFLINE.LOGICALREF
LEFT JOIN $(vFirmDbName).dbo.LG_$(vFirmNumber)_$(vPeriodNumber)_ORFICHE ORFICHE (NOLOCK) ON STLINE.ORDFICHEREF = ORFICHE.LOGICALREF
LEFT JOIN $(vFirmDbName).dbo.LG_$(vFirmNumber)_PROJECT PROJECT (NOLOCK) ON INVOICE.PROJECTREF = PROJECT.LOGICALREF
LEFT JOIN $(vFirmDbName).dbo.LG_$(vFirmNumber)_PROJECT PROJECT_LINE (NOLOCK) ON STLINE.PROJECTREF = PROJECT_LINE.LOGICALREF
LEFT JOIN $(vFirmDbName).dbo.LG_$(vFirmNumber)_PAYPLANS PAYPLANS2 (NOLOCK) ON PAYPLANS2.LOGICALREF=STLINE.PAYDEFREF
LEFT JOIN $(vMasterDbName).[dbo].L_CURRENCYLIST L_CURRENCYLIST (NOLOCK) ON L_CURRENCYLIST.CURTYPE=STLINE.TRCURR and L_CURRENCYLIST.FIRMNR='$(vFirmNumber)'
LEFT JOIN $(vMasterDbName).[dbo].LG_SLSMAN SSLSMAN (NOLOCK) ON STLINE.SALESMANREF = SSLSMAN.LOGICALREF AND (SSLSMAN.FIRMNR='$(vFirmNumber)' OR SSLSMAN.FIRMNR='-1')
LEFT JOIN $(vMasterDbName).[dbo].L_CURRENCYLIST ID_KUR (NOLOCK) ON ID_KUR.CURTYPE=STLINE.TRCURR AND ID_KUR.FIRMNR='$(vFirmNumber)'
LEFT JOIN $(vFirmDbName).dbo.LG_$(vFirmNumber)_EMCENTER EMCENTER (NOLOCK) ON EMCENTER.LOGICALREF=STLINE.CENTERREF
LEFT JOIN $(vFirmDbName).dbo.LG_$(vFirmNumber)_$(vPeriodNumber)_STLINE KK (NOLOCK) ON STLINE.LINETYPE=7 AND KK.LINETYPE=6 AND KK.INVOICEREF=STLINE.INVOICEREF AND KK.STOCKREF=STLINE.PREVLINEREF AND  KK.INVOICELNNO =STLINE.PREVLINENO
       LEFT JOIN
             (SELECT INVOICEREF, PREVLINEREF, PREVLINENO, SUM(VATMATRAH) AS VATMATRAH, sum(VATAMNT) as VATAMNT, CONVERT(DECIMAL(18,2), SUM(VATAMNT)/sum(VATMATRAH)) AS VAT, SUM(TOTAL) AS TOTAL
             FROM $(vFirmDbName).dbo.LG_$(vFirmNumber)_$(vPeriodNumber)_STLINE STLINE (NOLOCK)
             WHERE       LINETYPE IN (7)
             GROUP BY INVOICEREF, PREVLINEREF, PREVLINENO
             ) KKS ON KKS.INVOICEREF=STLINE.INVOICEREF AND STLINE.INVOICELNNO=KKS.PREVLINENO AND STLINE.STOCKREF=KKS.PREVLINEREF
WHERE INVOICE.CANCELLED=0
AND STLINE.LINETYPE IN (0,1,4,6,8,11)

  AND INVOICE.TRCODE IN (2,3,7,8,9,14)
AND (STFICHE.BILLED=1 OR STFICHE.BILLED IS NULL)