SELECT * FROM (
SELECT
ITMSM.YEAR_ YIL,
ITMSM.MONTH_ AY,
I.CODE URUNKODU,
I.NAME URUNADI,
SUM(ITMSM.SALES_AMOUNT) SATIS_MIKTAR
FROM
LV_119_01_STINVENS ITMSM
WITH(NOLOCK)
LEFT JOIN LG_119_ITEMS I WITH(NOLOCK) ON I.LOGICALREF=ITMSM.STOCKREF
WHERE
(ITMSM.INVENNO = -1) AND (( ITMSM.MONTH_ >= 1 ) AND ( ITMSM.MONTH_ <= 12 ) ) AND (ITMSM.MTRLINC = 0) AND (ITMSM.VIRTUALINVEN = 0)
GROUP BY ITMSM.YEAR_,
ITMSM.MONTH_,I.CODE,I.NAME ) AS T ORDER BY YIL,AY,URUNADI
Tag: ITEMS
LOGO ÜRÜN BARKOD VE SATIŞ FİYATI LİSTESİ SQL
select
ISNULL(B.BARCODE,'') ProductCode,
ISNULL(I.NAME,'') ProductName,
ISNULL((SELECT TOP 1 ROUND(PRICE,2) FROM LG_320_PRCLIST P WITH(NOLOCK) WHERE CARDREF = I.LOGICALREF AND ACTIVE = 0 AND PTYPE = 2 AND CLIENTCODE = '' order by P.LOGICALREF DESC ),0) Price
FROM dbo.LG_320_ITEMS I WITH(NOLOCK) LEFT JOIN LG_320_UNITBARCODE B ON B.ITEMREF=I.LOGICALREF WHERE I.CODE<>'ÿ'AND ISNULL(B.BARCODE,'') <> ''
LOGO SQL MALZEME EKSTRE
/*TEST =
select * from dbo.SELECT_MALZEMEEXTRE_201('01/01/2017',getdate(),'01-YK-0001')*/
/****** Object: UserDefinedFunction [dbo].[SELECT_MALZEMEEXTRE_201] Script Date: 18.8.2020 09:09:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[SELECT_MALZEMEEXTRE_201]
(
@BAT DATETIME,
@BIT DATETIME,
@MALZEMEKODU VARCHAR(50)
)
RETURNS TABLE
AS
RETURN
SELECT
X.AMBAR,
x.[Stfıche_Referans] ,
x.[Stlıne_Referans] ,
x.[Fiş No],
x.[Fiş Türü],
x.[Belge No],
x.[Stfıche Tarıh],
x.[Stlıne Tarıh],
x.[Ftıme],
x.[Stok Kodu],
x.[Stok Adı],
x.[Bırım],
x.[Giriş Çıkış],
x.[Miktar],
X.STFICHELNNO,
x.Kalan,
x.[Tutar]
FROM (
SELECT
(SELECT NAME FROM L_CAPIWHOUSE CAPIWHOUSE WHERE CAPIWHOUSE.NR = STLINE.SOURCEINDEX AND CAPIWHOUSE.FIRMNR = 201 ) AMBAR,
STFICHE.LOGICALREF [STFICHE_REFERANS],
STLINE.LOGICALREF [STLINE_REFERANS],
[Fiş No]=STFICHE.FICHENO,
[Fiş Türü]=CASE STFICHE.TRCODE
WHEN 1 then 'Satınalma İrsaliyesi'
WHEN 2 then 'Perakende Satış İade İrsaliyesi'
WHEN 3 then 'Toptan Satış İade İrsaliyesi'
WHEN 4 then 'Konsinye Çıkış İade İrsaliyesi'
WHEN 5 then 'Konsinye Giriş İrsaliyesi'
WHEN 6 then 'Satınalma İade İrsaliyesi'
WHEN 7 then 'Perakende Satış İrsaliyesi'
WHEN 8 then 'Toptan Satış İrsaliyesi'
WHEN 9 then 'Konsinye Çıkış İrsaliyesi'
WHEN 10 then 'Konsinye Giriş İade İrsaliyesi'
WHEN 11 then 'Fire Fişi'
WHEN 12 then 'Sarf Fişi'
WHEN 13 then 'Üretimden Giriş Fişi'
WHEN 14 then 'Devir Fişi'
WHEN 25 then 'Ambar Fişi'
WHEN 26 then 'Muhtahsil İrsaliyesi'
WHEN 50 then 'Sayım Fazlası Fişi'
WHEN 51 then 'Sayım Eksiği Fişi' ELSE '' END,
[Belge No]=STFICHE.DOCODE,
[Stfıche Tarıh]=STFICHE.DATE_,
[Stlıne Tarıh]=STLINE.DATE_,
[Ftıme]=STLINE.FTIME,
[Stok Kodu]=ITEMS.CODE,
[Stok Adı]=ITEMS.NAME,
[Bırım]=BIRIM.CODE,
[Giriş Çıkış]= CASE WHEN STLINE.IOCODE IN(1,2) THEN 'Giriş' when STLINE.IOCODE IN(3,4) THEN 'Çıkış' else '' end,
[Miktar]=CAST((CASE WHEN STLINE.IOCODE IN(1,2) THEN 1 ELSE -1 end) * STLINE.AMOUNT*(CASE WHEN ISNULL(UINFO2,0)=0 THEN 1 ELSE UINFO2 END)/(CASE WHEN ISNULL(UINFO1,0)=0 THEN 1 ELSE UINFO1 END) AS float),
STLINE.STFICHELNNO,
0.00 KALAN,
[Tutar]=(CASE WHEN STLINE.IOCODE IN(1,2) THEN 1 ELSE -1 end) * STLINE.VATMATRAH
FROM LG_201_01_STFICHE STFICHE WITH (NOLOCK)
LEFT OUTER JOIN LG_201_01_STLINE STLINE WITH (NOLOCK) ON STFICHE.LOGICALREF=STLINE.STFICHEREF
LEFT OUTER JOIN LG_201_ITEMS ITEMS WITH (NOLOCK) ON STLINE.STOCKREF =ITEMS.LOGICALREF
LEFT OUTER JOIN LG_201_UNITSETL BIRIM WITH (NOLOCK) ON BIRIM.UNITSETREF= ITEMS.UNITSETREF AND BIRIM.MAINUNIT=1
WHERE
STFICHE.CANCELLED=0 AND STLINE.CANCELLED=0
AND STLINE.LINETYPE=0
AND STLINE.IOCODE IN (1,2,3,4)
AND ITEMS.CODE=@MALZEMEKODU
AND STLINE.LPRODSTAT=0
AND (STLINE.DATE_>=@BAT AND STLINE.DATE_<=@BIT )
)
AS X
GO
LOGO SQL BELIRLI TARIH ARASI MALZEMELERIN SATISLARI
SELECT
ITMSC.CODE,
ITMSC.NAME,
ISNULL(SUM(AMOUNT),0)
FROM
LG_118_01_STLINE STRNS WITH(NOLOCK) LEFT OUTER JOIN
LG_118_01_STFICHE STFIC WITH(NOLOCK) ON (STRNS.STFICHEREF = STFIC.LOGICALREF) LEFT OUTER JOIN
LG_118_CLCARD CLNTC WITH(NOLOCK) ON (STRNS.CLIENTREF = CLNTC.LOGICALREF) LEFT OUTER JOIN
LG_118_ITEMS ITMSC WITH(NOLOCK) ON (STRNS.STOCKREF = ITMSC.LOGICALREF) LEFT OUTER JOIN
LG_118_UNITSETL USLINE WITH(NOLOCK) ON (STRNS.UOMREF = USLINE.LOGICALREF) LEFT OUTER JOIN
LG_118_UNITSETF UNITSET WITH(NOLOCK) ON (ITMSC.UNITSETREF = UNITSET.LOGICALREF) LEFT OUTER JOIN
LG_118_VARIANT VARIANT WITH(NOLOCK) ON (STRNS.VARIANTREF = VARIANT.LOGICALREF)
WHERE
/*(ITMSC.CODE LIKE 'COL00008') AND*/
((STFIC.DATE_ >= GETDATE()-1000) AND (STFIC.DATE_ <= GETDATE())) AND
(STFIC.CANCELLED = 0) AND (STFIC.TRCODE IN (2,3,4,7,8,9)) AND
(STFIC.STATUS IN (0,1)) AND
(STRNS.CPSTFLAG <> 1) AND
(STRNS.LINETYPE IN (0,1,5,6,8,9,10)) AND
(STFIC.DEVIR IN (0,1)) AND
((STFIC.TRCODE IN (2,3,4,7,8,9,35,36,37,38,39))OR(STFIC.TRCODE IN (1,5,6,10,26,30,31,32,33,34)))
GROUP BY ITMSC.CODE,ITMSC.NAME
LOGO INVDEFDE OLMAYAN ITEMLERI SQLDEN EKLEME
INVDEF TABLOSU : URUNLERIN GENEL OLARAK AMBAR BAZLI NEGATIF SEVIYEYE DUSUP DUSMEMESININ BILGISINI ICERIR
INSERT INTO LG_101_INVDEF (INVENNO,ITEMREF,MINLEVEL,MAXLEVEL,SAFELEVEL,LOCATIONREF, PERCLOSEDATE,
ABCCODE,MINLEVELCTRL,MAXLEVELCTRL,SAFELEVELCTRL,NEGLEVELCTRL,IOCTRL,VARIANTREF,OUTCTRL)
SELECT W.NR INVENNO, I.LOGICALREF ITEMREF, 0 MINLEVEL,0 MAXLEVEL, 0 SAFELEVEL, 0 LOCATIONREF, NULL PERCLOSEDATE,
0 ABCCODE, 0 MINLEVELCTRL, 0 MAXLEVELCTRL, 0 SAFELEVELCTRL, 0 NEGLEVELCTRL, 0 IOCTRL, 0 VARIANTREF, 0 OUTCTRL FROM L_CAPIWHOUSE W
CROSS JOIN LG_101_ITEMS I
WHERE FIRMNR=101 AND I.CARDTYPE=1 AND I.LOGICALREF NOT IN ( select ID.ITEMREF from LG_101_INVDEF ID)
LOGO TIGER 3 SQL AY , AMBAR VE MALZEME BAZLI STOK DURUM RAPORU
CREATE view [dbo].[BM_211_STOKAYLIK] as
SELECT TOP 100 PERCENT tarih, ambar,'0' [isyeri],
CASE ITEMS2.CARDTYPE
WHEN 1 THEN
'TICARI MAL'
WHEN 2 THEN
'KARMA KOLI'
WHEN 3 THEN
'DEPOZITOLU MAL'
WHEN 4 THEN
'SABIT KIYMET'
WHEN 10 THEN
'HAMMADDE'
WHEN 11 THEN
'YARI MAMUL'
WHEN 12 THEN
'MAMUL'
WHEN 13 THEN
'TUKETIM MALI'
WHEN 20 THEN
'GENEL MALZEME SINIFI'
WHEN 21 THEN
'TABLOLU MALZEME SINIFI'
ELSE
CONVERT(VARCHAR(20), CARDTYPE)
END AS mal_tur,
ITEMS2.CODE mal_kod, ITEMS2.NAME mal_ad ,BIRIM.CODE birim ,
ISNULL(
(
SELECT SUM(ONHAND)
FROM LV_211_01_STINVTOT STINVTOT
WHERE STINVTOT.STOCKREF = ITEMS2.LOGICALREF
AND STINVTOT.INVENNO = ambarnr
-- AND STINVTOT.DATE_ <= DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(m, 0, tarih)), 0)
AND YEAR(STINVTOT.DATE_) * 100 + MONTH(STINVTOT.DATE_)<=tarih
),
0
) stok
FROM (
SELECT DISTINCT tarih ,whouse.NR ambarnr, WHOUSE.NAME ambar,MALZEMELER.STOCKREF FROM (
select DISTINCT YEAR(STLINE.DATE_) * 100 + MONTH(STLINE.DATE_) tarih from LG_211_01_STLINE STLINE
WHERE YEAR(STLINE.DATE_) * 100 + MONTH(STLINE.DATE_) <=YEAR(GETDATE())*100+MONTH(GETDATE())
) AS T
CROSS JOIN L_CAPIWHOUSE WHOUSE
CROSS JOIN LG_211_ITEMS ITEMS
LEFT JOIN
(
SELECT STLINE.STOCKREF , STLINE.SOURCEINDEX
FROM LG_211_01_STLINE STLINE WITH (NOLOCK)
LEFT JOIN LG_211_01_STFICHE STFIC
ON STLINE.STFICHEREF = STFIC.LOGICALREF
WHERE (
(STFIC.TRCODE IN ( 2, 3, 4, 7, 8, 9, 35, 36, 37, 38, 39 ))
OR (STFIC.CLIENTREF = 0)
OR (STFIC.TRCODE IN ( 1, 5, 6, 10, 26, 30, 31, 32, 33, 34 ))
OR (STFIC.CLIENTREF = 0)
OR (STFIC.TRCODE IN ( 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 50, 51 ))
)
AND (STFIC.CANCELLED = 0)
AND STLINE.LINETYPE IN ( 0, 1, 5, 6, 8, 9, 10 )
AND
(
(STFIC.STATUS IN ( 0, 1 ))
OR (STFIC.TRCODE IN ( 11, 12, 13, 14, 25, 26, 50, 51 ))
)
AND
(
(STFIC.STATUS IN ( 0, 1 ))
OR (STFIC.TRCODE = 25)
)
AND
(
(STFIC.PRODSTAT = 0)
OR (STFIC.TRCODE IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 ))
)
AND (STFIC.DEVIR IN ( 0, 1 ))
) as MALZEMELER ON MALZEMELER.STOCKREF=ITEMS.LOGICALREF AND MALZEMELER.SOURCEINDEX=NR
WHERE WHOUSE.FIRMNR=211
--ORDER BY tarih
) AS T2 LEFT JOIN LG_211_ITEMS ITEMS2 ON STOCKREF=ITEMS2.LOGICALREF
LEFT OUTER JOIN LG_211_UNITSETL BIRIM WITH (NOLOCK)
ON BIRIM.UNITSETREF = ITEMS2.UNITSETREF
AND BIRIM.MAINUNIT = 1
order by tarih
GO
LOGO SQL MALZEME BIRIM DURUMLARI
CREATE VIEW BM_MALZEME_BIRIM_DURUMLARI AS
SELECT DISTINCT LOGICALREF,M.ANAMALZEMEKOD,M.ANAMZELEMEAD, M.BIRIMKODU,M.BIRIMADI,
ISNULL((SELECT DISTINCT CONVERT(VARCHAR,UOMREF) FROM LG_210_01_STLINE S WHERE S.UOMREF=M.LOGICALREF AND S.LINETYPE=0),'YOK') STLINEDAVARMI,
ISNULL((SELECT DISTINCT CONVERT(VARCHAR,UOMREF) FROM LG_210_01_ORFLINE S WHERE S.UOMREF=M.LOGICALREF AND S.LINETYPE=0),'YOK') ORFLINEDAVARMI,
CASE WHEN ISNULL((SELECT DISTINCT CONVERT(VARCHAR,UOMREF) FROM LG_210_01_STLINE S WHERE S.UOMREF=M.LOGICALREF AND S.LINETYPE=0),'YOK') ='YOK'
AND ISNULL((SELECT DISTINCT CONVERT(VARCHAR,UOMREF) FROM LG_210_01_ORFLINE S WHERE S.UOMREF=M.LOGICALREF AND S.LINETYPE=0),'YOK')='YOK' THEN 'SIL' ELSE 'SILME'
END
SILINEBILIR
FROM (SELECT
UNITSETL.CODE BIRIMKODU,
UNITSETL.NAME BIRIMADI,
(SELECT CODE FROM LG_210_UNITSETF UNITSETF WHERE UNITSETF.LOGICALREF = UNITSETL.UNITSETREF) ANAMALZEMEKOD,
(SELECT UNITSETF.NAME FROM LG_210_UNITSETF UNITSETF WHERE UNITSETF.LOGICALREF = UNITSETL.UNITSETREF) ANAMZELEMEAD,
LOGICALREF, CODE, NAME, UNITSETREF, LINENR, MAINUNIT, CONVFACT1, CONVFACT2, WIDTH, LENGTH, HEIGHT, AREA, VOLUME_, WEIGHT, WIDTHREF, LENGTHREF, HEIGHTREF, AREAREF, VOLUMEREF, WEIGHTREF, DIVUNIT, MEASURECODE, GLOBALCODE
FROM
LG_210_UNITSETL UNITSETL WITH(NOLOCK) ) M
Blog of Sem 