CREATE VIEW [dbo]. [MND_VLK07_SIPARISTESLIM] AS
SELECT STHAR_TARIH AS TARIH,S.FISNO,C.CARI_ISIM,S.STOK_KODU,
SS.STOK_ADI,STHAR_GCMIK AS MIKTAR,
FIRMA_DOVTUT AS GELEN_MIKTAR,(STHAR_GCMIK-FIRMA_DOVTUT) AS BAKIYE,S.STHAR_TESTAR AS TESLIM_TARIHI,
(SELECT MIN(STHAR_TARIH) FROM TBLSTHAR WHERE STHAR_SIPNUM=S.FISNO)AS GELDIGI_TARIH,
DATEDIFF(DAY, S.STHAR_TESTAR,(SELECT MIN(STHAR_TARIH) FROM TBLSTHAR WHERE STHAR_SIPNUM=S.FISNO)) AS GECIKME
FROM TBLSIPATRA S,TBLCASABIT C,TBLSTSABIT SS
WHERE S.STHAR_ACIKLAMA=C.CARI_KOD AND S.STOK_KODU=SS.STOK_KODU
AND STHAR_GCKOD='G'
SELECT TARIH AS SIPARIS_TARIHI,FISNO AS SIPARIS_NUMARASI,CARI_ISIM AS SIPARIS_VERILEN,
STOK_KODU AS URUN_KODU,STOK_ADI AS URUN_ADI,MIKTAR AS SIPARIS_EDILEN_MIKTAR,
GELEN_MIKTAR,BAKIYE AS GELMEYEN_MIKTAR,TESLIM_TARIHI,
GELDIGI_TARIH
FROM MND_VLK07_SIPARISTESLIM WHERE BAKIYE>0 AND
DATEDIFF(DAY,TESLIM_TARIHI,(SELECT GETDATE()))>=-1
ORDER BY TESLIM_TARIHI DESC
USE [MND2010]
GO
/****** Object: View [dbo].[MND_VLK05_CEKSEN] Script Date: 01/30/2010 08:56:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[MND_VLK05_CEKSEN] AS
SELECT SUBE_KODU,SC_NO,SC_VERENK,SC_VERILENK,
PLASIYER_KODU FROM TBLMCEK
UNION ALL
SELECT SUBE_KODU,SC_NO,SC_VERENK,SC_VERILENK,
PLASIYER_KODU FROM TBLMSEN
USE [MND2010]
GO
/****** Object: View [dbo].[MND_VLK06_CEKSEN] Script Date: 01/30/2010 08:56:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[MND_VLK06_CEKSEN] AS
SELECT *,(SELECT TOP 1 PLASIYER_KODU FROM MND_VLK05_CEKSEN WHERE SC_NO=V.SC_NO AND SC_VERENK=V.VEREN_KODU)AS PLASIYER
FROM CEKSENDEKOTR V
USE [MND2010]
GO
/****** Object: View [dbo].[MND_VLK01_FATURA] Script Date: 01/30/2010 09:26:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[MND_VLK01_FATURA] AS SELECT V.FISNO,V.STHAR_TARIH,TBLCASABIT.CARI_ISIM
,V.STHAR_CARIKOD,TBLSTSABIT.STOK_ADI,
V.STHAR_NF,V.STHAR_GCMIK,V.STHAR_KDV,
(V.STHAR_GCMIK * V.STHAR_NF * V.STHAR_KDV / 100) AS KDV_TUT,
(((CASE WHEN V.STHAR_GCKOD='G' THEN V.STHAR_GCMIK ELSE 0 END) *
V.STHAR_NF) + ((CASE WHEN V.STHAR_GCKOD='G' THEN V.STHAR_GCMIK ELSE 0 END) * V.STHAR_NF * V.STHAR_KDV / 100)) AS ALIS_TOPLAM,
(((CASE WHEN V.STHAR_GCKOD='C' THEN V.STHAR_GCMIK ELSE 0 END) *
V.STHAR_NF) + ((CASE WHEN V.STHAR_GCKOD='C' THEN V.STHAR_GCMIK ELSE 0 END) * V.STHAR_NF * V.STHAR_KDV / 100)) AS SATIS_TOPLAM,
(CASE WHEN V.STHAR_GCKOD='G' THEN V.STHAR_GCMIK ELSE 0 END) AS GIRIS_MIKTAR,
(CASE WHEN V.STHAR_GCKOD='C' THEN V.STHAR_GCMIK ELSE 0 END) AS CIKIS_MIKTAR,TBLSTSABIT.OLCU_BR1,
V.STHAR_FTIRSIP,
V.STHAR_GCKOD,V.STHAR_HTUR,
CASE WHEN V.STHAR_FTIRSIP='1' AND V.STHAR_GCKOD='C' AND V.STHAR_HTUR='J' THEN 'SATFAT'
WHEN V.STHAR_FTIRSIP='2' AND V.STHAR_GCKOD='G' AND V.STHAR_HTUR='J' THEN 'ALFAT'
WHEN V.STHAR_FTIRSIP='2' AND V.STHAR_GCKOD='G' AND V.STHAR_HTUR='L' THEN 'SATISTAN_IADE'
WHEN V.STHAR_FTIRSIP='1' AND V.STHAR_GCKOD='C' AND V.STHAR_HTUR='L' THEN 'ALISTAN_IADE'
WHEN V.STHAR_FTIRSIP='1' AND V.STHAR_GCKOD='C' AND V.STHAR_HTUR='K' THEN 'KAPALI_FATURA'
WHEN V.STHAR_FTIRSIP='1' AND V.STHAR_GCKOD='C' AND V.STHAR_HTUR='I' THEN 'MUHTELIF_FATURA'
ELSE '' END AS BELGE_TIPI,TBLSTSABIT.GRUP_KODU,
((CASE WHEN V.STHAR_GCKOD='G' THEN V.STHAR_GCMIK ELSE 0 END) *
V.STHAR_NF) AS NETTUTAR,
(SELECT TOP 1 ACIK1 FROM TBLFATUEK F WHERE F.FATIRSNO=V.FISNO AND F.FKOD=V.STHAR_FTIRSIP) as ACIK1,
(SELECT TOP 1 ACIK2 FROM TBLFATUEK F WHERE F.FATIRSNO=V.FISNO AND F.FKOD=V.STHAR_FTIRSIP) as ACIK2
FROM TBLSTHAR V INNER JOIN TBLSTSABIT ON V.STOK_KODU=TBLSTSABIT.STOK_KODU
INNER JOIN TBLCASABIT ON TBLCASABIT.CARI_KOD=V.STHAR_CARIKOD
USE [MND2010]
GO
/****** Object: View [dbo].[MND_VLK08_FATURAKOLONBAZI] Script Date: 02/01/2010 12:49:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[MND_VLK08_FATURAKOLONBAZI] AS
SELECT FISNO,COUNT(DISTINCT PROJE_KODU) AS SAYI,
ROUND(CONVERT(varchar(15),SUM(CONVERT(decimal(2, 0),PROJE_KODU))/COUNT(PROJE_KODU)),0) AS PROJE
FROM TBLSTHAR V WHERE STHAR_FTIRSIP IN ('1','2')
GROUP BY FISNO
INSERT INTO TBLMUHMAS(AY_KODU,MAS_FISNO,MASACIK1,MASACIK2,
FISTIP,SUBE_KODU,KAYITYAPANKUL,
KAYITTARIHI)
SELECT AY_KODU,MAS_FISNO,MASACIK1,MASACIK2,
FISTIP,SUBE_KODU,KAYITYAPANKUL,
KAYITTARIHI
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:A.xls;Extended Properties=Excel 8.0')...[TBLMUHMAS$] V
WHERE V.MAS_FISNO IS NOT NULL
INSERT INTO TBLMUHFIS(AY_KODU,FISNO,SIRA,HES_KOD,TARIH,BA
,ACIKLAMA,TUTAR,MIKTAR,DOVIZTIP,
DOVIZTUT,EVRAKTARIHI,
SUBE_KODU,SUBELI,KAYITYAPANKUL,
KAYITTARIHI)
SELECT AY_KODU,FISNO,SIRA,HES_KOD,TARIH,BA,
ACIKLAMA,TUTAR,MIKTAR,DOVIZTIP,DOVIZTUT,
EVRAKTARIHI,
SUBE_KODU,SUBELI,KAYITYAPANKUL,
KAYITTARIHI
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:A.xls;Extended Properties=Excel 8.0')...[TBLMUHFIS$] V
WHERE FISNO IS NOT NULL OR TUTAR IS NOT NULL OR TUTAR<>'0'
USE [MND2010]
GO
/****** Object: View [dbo].[MND_VLK02_HAMSAYISI] Script Date: 02/03/2010 08:27:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[MND_VLK02_HAMSAYISI] AS
SELECT STHAR_ACIKLAMA,FISNO,COUNT(STOK_KODU)AS HAM_MIKTAR FROM TBLSTHAR WHERE STHAR_HTUR='C' AND STHAR_GCKOD='C'
GROUP BY STHAR_ACIKLAMA,FISNO
USE [MND2010]
GO
/****** Object: View [dbo].[MND_VLK03_URETIM] Script Date: 02/03/2010 08:24:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[MND_VLK03_URETIM] AS
SELECT V.STOK_KODU AS HAM_KOD,STHAR_GCMIK AS HAM_MIK,STHAR_TARIH,STHAR_ACIKLAMA AS MAMUL,FISNO,
((SELECT URETSON_MIKTAR FROM TBLSTOKURS WHERE URETSON_MAMUL=V.STHAR_ACIKLAMA AND URETSON_FISNO=
V.FISNO)/(SELECT HAM_MIKTAR FROM MND_VLK02_HAMSAYISI WHERE STHAR_ACIKLAMA=V.STHAR_ACIKLAMA AND
FISNO=V.FISNO)) AS CARPAN,
(SELECT STOK_ADI FROM TBLSTSABIT WHERE STOK_KODU=V.STOK_KODU) AS HAM_ADI,
(SELECT STOK_ADI FROM TBLSTSABIT WHERE STOK_KODU=V.STHAR_ACIKLAMA) AS MAMUL_ADI,
(SELECT GRUP_KODU FROM TBLSTSABIT WHERE STOK_KODU=V.STHAR_ACIKLAMA) AS GRUP_KODU,
(SELECT TOP 1 STHAR_NF FROM TBLSTHAR WHERE STOK_KODU=V.STOK_KODU AND STHAR_GCKOD='G' AND STHAR_HTUR='J' AND
STHAR_TARIH IN (SELECT MAX(STHAR_TARIH) FROM TBLSTHAR WHERE STOK_KODU=V.STOK_KODU AND STHAR_GCKOD='G'
AND STHAR_HTUR='J')) AS FAT_FIYAT,
(SELECT TOP 1 STHAR_NF FROM TBLSTHAR WHERE STOK_KODU=V.STOK_KODU AND STHAR_GCKOD='G' AND STHAR_HTUR='A' AND
INCKEYNO IN (SELECT MAX(INCKEYNO) FROM TBLSTHAR WHERE STOK_KODU=V.STOK_KODU AND STHAR_GCKOD='G'
AND STHAR_HTUR='A')) AS DEV_FIYAT
FROM TBLSTHAR V
WHERE STHAR_HTUR='C' AND STHAR_GCKOD='C'
USE [MND2010]
GO
/****** Object: View [dbo].[MND_VLK04_URETIM] Script Date: 02/03/2010 08:24:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[MND_VLK04_URETIM] AS
SELECT *,CASE WHEN FAT_FIYAT IS NULL THEN DEV_FIYAT ELSE FAT_FIYAT END AS FIYAT,
((CASE WHEN FAT_FIYAT IS NULL THEN DEV_FIYAT ELSE FAT_FIYAT END)*HAM_MIK) AS TUTAR
FROM MND_VLK03_URETIM
USE [MND2010]
GO
/****** Object: View [dbo].[MND_VLK03_URETIM] Script Date: 02/03/2010 12:54:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[MND_VLK03_URETIM] AS
SELECT V.STOK_KODU AS HAM_KOD,STHAR_GCMIK AS HAM_MIK,STHAR_TARIH,STHAR_ACIKLAMA AS MAMUL,FISNO,
((SELECT STHAR_GCMIK FROM TBLSTHAR WHERE STOK_KODU=V.STHAR_ACIKLAMA AND FISNO=
V.FISNO AND STHAR_GCKOD='G' AND
STHAR_HTUR='C' )/(SELECT HAM_MIKTAR FROM MND_VLK02_HAMSAYISI WHERE STHAR_ACIKLAMA=V.STHAR_ACIKLAMA AND
FISNO=V.FISNO)) AS CARPAN,
(SELECT STOK_ADI FROM TBLSTSABIT WHERE STOK_KODU=V.STOK_KODU) AS HAM_ADI,
(SELECT STOK_ADI FROM TBLSTSABIT WHERE STOK_KODU=V.STHAR_ACIKLAMA) AS MAMUL_ADI,
(SELECT GRUP_KODU FROM TBLSTSABIT WHERE STOK_KODU=V.STHAR_ACIKLAMA) AS GRUP_KODU,
(SELECT TOP 1 STHAR_NF FROM TBLSTHAR WHERE STOK_KODU=V.STOK_KODU AND STHAR_GCKOD='G' AND STHAR_HTUR='J' AND
STHAR_TARIH IN (SELECT MAX(STHAR_TARIH) FROM TBLSTHAR WHERE STOK_KODU=V.STOK_KODU AND STHAR_GCKOD='G'
AND STHAR_HTUR='J')) AS FAT_FIYAT,
(SELECT TOP 1 STHAR_NF FROM TBLSTHAR WHERE STOK_KODU=V.STOK_KODU AND STHAR_GCKOD='G' AND STHAR_HTUR='A' AND
INCKEYNO IN (SELECT MAX(INCKEYNO) FROM TBLSTHAR WHERE STOK_KODU=V.STOK_KODU AND STHAR_GCKOD='G'
AND STHAR_HTUR='A')) AS DEV_FIYAT
FROM TBLSTHAR V
WHERE STHAR_HTUR='C' AND STHAR_GCKOD='C'
ALTER VIEW [dbo].[MND_VLK04_URETIM] AS
SELECT *,CASE WHEN FAT_FIYAT IS NULL THEN DEV_FIYAT ELSE FAT_FIYAT END AS FIYAT,
((CASE WHEN FAT_FIYAT IS NULL THEN DEV_FIYAT ELSE FAT_FIYAT END)*HAM_MIK) AS TUTAR,
(SELECT KOD_2 FROM TBLSTSABIT WHERE STOK_KODU=V.MAMUL) AS KOD_2,
(SELECT KOD_5 FROM TBLSTSABIT WHERE STOK_KODU=V.MAMUL) AS KOD_5,
CASE WHEN (SELECT KOD_5 FROM TBLSTSABIT WHERE STOK_KODU=V.MAMUL) IS NULL
OR (SELECT KOD_5 FROM TBLSTSABIT WHERE STOK_KODU=V.MAMUL)='' THEN
(SELECT KOD_2 FROM TBLSTSABIT WHERE STOK_KODU=V.MAMUL) ELSE (SELECT KOD_5 FROM TBLSTSABIT WHERE STOK_KODU=V.MAMUL) END AS KOD
FROM MND_VLK03_URETIM V
SELECT * FROM TBLCASABITEK
DELETE FROM DENEME..TBLCAHAR
delete FROM DENEME..TBLCASABIT
delete FROM DENEME..TBLCASABITEK
INSERT INTO DENEME..TBLCASABIT(SUBE_KODU, ISLETME_KODU, CARI_KOD, CARI_TEL, CARI_IL, ULKE_KODU, CARI_ISIM,
CARI_TIP, GRUP_KODU, RAPOR_KODU1, RAPOR_KODU2, RAPOR_KODU3, RAPOR_KODU4, RAPOR_KODU5, CARI_ADRES, CARI_ILCE,
VERGI_DAIRESI, VERGI_NUMARASI, FAX, POSTAKODU, DETAY_KODU, NAKLIYE_KATSAYISI, RISK_SINIRI, TEMINATI, CARISK,
CCRISK, SARISK, SCRISK, CM_BORCT, CM_ALACT, CM_RAP_TARIH, KOSULKODU, ISKONTO_ORANI, VADE_GUNU, LISTE_FIATI,
ACIK1, ACIK2, ACIK3, M_KOD, DOVIZ_TIPI, DOVIZ_TURU, HESAPTUTMASEKLI, DOVIZLIMI, UPDATE_KODU, PLASIYER_KODU,
LOKALDEPO, EMAIL, WEB, KURFARKIBORC, KURFARKIALAC, S_YEDEK1, S_YEDEK2, F_YEDEK1, F_YEDEK2, C_YEDEK1,
C_YEDEK2, B_YEDEK1, I_YEDEK1, L_YEDEK1, FIYATGRUBU, KAYITYAPANKUL, KAYITTARIHI, DUZELTMEYAPANKUL,
DUZELTMETARIHI, ODEMETIPI, ONAYTIPI, ONAYNUM, MUSTERIBAZIKDV, AGIRLIK_ISK)
SELECT '-1', ISLETME_KODU, CARI_KOD, CARI_TEL, CARI_IL, ULKE_KODU, CARI_ISIM,
CARI_TIP, GRUP_KODU, RAPOR_KODU1, RAPOR_KODU2, RAPOR_KODU3, RAPOR_KODU4, RAPOR_KODU5, CARI_ADRES, CARI_ILCE,
VERGI_DAIRESI, VERGI_NUMARASI, FAX, POSTAKODU, DETAY_KODU, NAKLIYE_KATSAYISI, RISK_SINIRI, TEMINATI, CARISK,
CCRISK, SARISK, SCRISK, CM_BORCT, CM_ALACT, CM_RAP_TARIH, KOSULKODU, ISKONTO_ORANI, VADE_GUNU, LISTE_FIATI,
ACIK1, ACIK2, ACIK3, M_KOD, DOVIZ_TIPI, DOVIZ_TURU, HESAPTUTMASEKLI, DOVIZLIMI, UPDATE_KODU, PLASIYER_KODU,
LOKALDEPO, EMAIL, WEB, KURFARKIBORC, KURFARKIALAC, S_YEDEK1, S_YEDEK2, F_YEDEK1, F_YEDEK2, C_YEDEK1,
C_YEDEK2, B_YEDEK1, I_YEDEK1, L_YEDEK1, FIYATGRUBU, KAYITYAPANKUL, KAYITTARIHI, DUZELTMEYAPANKUL,
DUZELTMETARIHI, ODEMETIPI, ONAYTIPI, ONAYNUM, MUSTERIBAZIKDV, AGIRLIK_ISK FROM MND2009..TBLCASABIT
INSERT INTO DENEME..TBLCASABITEK(CARI_KOD)
SELECT CARI_KOD FROM TBLCASABIT WHERE CARI_KOD NOT IN (SELECT CARI_KOD FROM DENEME..TBLCASABITEK)
INSERT INTO DENEME..TBLCAHAR(SUBE_KODU, CARI_KOD, TARIH, VADE_TARIHI, BELGE_NO, ACIKLAMA, HKA, BORC,
ALACAK, BAKIYE, DOVIZ_TURU, DOVIZ_TUTAR, RAPOR_KODU, F9SC, HAREKET_TURU, MIKTAR, ILAVE_RAPOR_KODU,
UPDATE_KODU, KAPATILMIS_TUTAR, ODEME_GUNU, FIRMA_DOVIZ_TIPI, FIRMA_DOVIZ_TUTARI, PLASIYER_KODU,
ENT_REF_KEY, RAPOR_KODU2, DUZELTMETARIHI, S_YEDEK1, S_YEDEK2, F_YEDEK1, F_YEDEK2,
C_YEDEK1, C_YEDEK2, B_YEDEK1, I_YEDEK1, L_YEDEK1, D_YEDEK1, ONAYTIPI, ONAYNUM)
SELECT SUBE_KODU, CARI_KOD, TARIH, VADE_TARIHI, BELGE_NO, ACIKLAMA, HKA, BORC,
ALACAK, BAKIYE, DOVIZ_TURU, DOVIZ_TUTAR, RAPOR_KODU, F9SC, HAREKET_TURU, MIKTAR, ILAVE_RAPOR_KODU,
UPDATE_KODU, KAPATILMIS_TUTAR, ODEME_GUNU, FIRMA_DOVIZ_TIPI, FIRMA_DOVIZ_TUTARI, PLASIYER_KODU,
ENT_REF_KEY, RAPOR_KODU2, DUZELTMETARIHI, S_YEDEK1, S_YEDEK2, F_YEDEK1, F_YEDEK2,
C_YEDEK1, C_YEDEK2, B_YEDEK1, I_YEDEK1, L_YEDEK1, D_YEDEK1, ONAYTIPI, ONAYNUM
FROM MND2009..TBLCAHAR
---select * FROM TBLCAHAR
UPDATE DENEME..TBLCAHAR SET VADE_TARIHI=TARIH WHERE HAREKET_TURU<>'A'
CREATE VIEW [MND_VLK09_DEKONT] AS
SELECT
(SELECT KOD FROM TBLDEKOTRA WHERE SERI_NO='FT' AND (KOD LIKE '320%' OR KOD LIKE'120%') AND
C_M='C' AND DEKONT_NO=V.DEKONT_NO AND PROJE_KODU='1' AND SUBE_KODU=V.SUBE_KODU) AS MND
,*
FROM TBLDEKOTRA V
WHERE SERI_NO='FT' AND
(KOD LIKE '7%' OR KOD LIKE '6%' OR KOD LIKE '2%') AND PROJE_KODU='1'
7-6-2
SELECT * FROM TBLDEKOTRA WHERE SERI_NO='FT' AND
C_M='C' AND DEKONT_NO='3' AND (KOD LIKE '3%' OR KOD LIKE'2%')
SELECT * FROM TBLDEKOTRA WHERE SERI_NO='FT'
UPDATE TBLDEKOTRA SET TBLDEKOTRA.BFORMCARI_KODU=MND_VLK09_DEKONT.MND
FROM TBLDEKOTRA,MND_VLK09_DEKONT
WHERE TBLDEKOTRA.INCKEYNO=MND_VLK09_DEKONT.INCKEYNO
UPDATE TBLDEKOTRA SET TBLDEKOTRA.YEDEK1='E'
FROM TBLDEKOTRA,MND_VLK09_DEKONT
WHERE TBLDEKOTRA.INCKEYNO=MND_VLK09_DEKONT.INCKEYNO
SELECT * FROM MND_VLK09_DEKONT WHERE INCKEYNO='22'
SELECT * FROM TBLDEKOTRA WHERE SERI_NO='FT'
INSERT INTO TBLMALIGRUP(GRUPKOD,ISIM,ANAKOD,TUR,HANGIOLCUBIRIMI,
HAMORTSATFI, BRHAMMADDE, BRAMBALAJ, BRISCSUR, BRENERJI, BRAMORT, BRYARD,
BRYEDEK, BRMALGIDER_1, BRMALGIDER_2, BRMALGIDER_3, BRMALGIDER_4, BRMALGIDER_5,
YARIMAMULHESKOD, MAMULHESKOD, HAMMADSARFHES, AMBALAJHES, SATMALMALIYET, ILKMADMALZYANS,
AMBALAJYANS, ISCIYANS1, ISCIYANS2, ENERJIYANS, AMORTYANS, YARDSERYANS, YEDPARCAYANS,
MALGIDER1_YANS, MALGIDER2_YANS, MALGIDER3_YANS,
AYBASISTOK, AYSONUSTOK, PROJE_KODU, KAYITYAPANKUL,
KAYITTARIHI)
SELECT GRUPKOD,CONVERT(VARCHAR(50),ISIM),ANAKOD,TUR,HANGIOLCUBIRIMI,
HAMORTSATFI, BRHAMMADDE, BRAMBALAJ, BRISCSUR, BRENERJI, BRAMORT, BRYARD,
BRYEDEK, BRMALGIDER_1, BRMALGIDER_2, BRMALGIDER_3, BRMALGIDER_4, BRMALGIDER_5,
YARIMAMULHESKOD, MAMULHESKOD, HAMMADSARFHES, AMBALAJHES, SATMALMALIYET, ILKMADMALZYANS,
AMBALAJYANS, ISCIYANS1, ISCIYANS2, ENERJIYANS, AMORTYANS, YARDSERYANS, YEDPARCAYANS,
MALGIDER1_YANS, MALGIDER2_YANS, MALGIDER3_YANS,
AYBASISTOK, AYSONUSTOK, PROJE_KODU,KAYITYAPANKUL, KAYITTARIHI
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:B.xls;Extended Properties=Excel 8.0')...[TBLMALIGRUP$] V
UPDATE TBLSTSABITEK SET TBLSTSABITEK.TUR='M' WHERE STOK_KODU IN
(SELECT STOK_KODU FROM TBLSTSABIT WHERE GRUP_KODU='MM')
UPDATE TBLSTSABITEK SET TBLSTSABITEK.TUR='Y' WHERE STOK_KODU IN
(SELECT STOK_KODU FROM TBLSTSABIT WHERE GRUP_KODU='YM')
UPDATE TBLSTSABITEK SET TBLSTSABITEK.TUR='I' WHERE STOK_KODU IN
(SELECT STOK_KODU FROM TBLSTSABIT WHERE GRUP_KODU='HM' AND KOD_1='D.HAMMAD')
UPDATE TBLSTSABITEK SET TBLSTSABITEK.TUR='I' WHERE STOK_KODU IN
(SELECT STOK_KODU FROM TBLSTSABIT WHERE GRUP_KODU='HM' AND KOD_1='Y.HAMMAD')
UPDATE TBLSTSABITEK SET TBLSTSABITEK.TUR='B' WHERE STOK_KODU IN
(SELECT STOK_KODU FROM TBLSTSABIT WHERE GRUP_KODU='HM' AND KOD_1='AMBALAJ')
UPDATE TBLSTSABITEK SET MGRUP=STOK_KODU WHERE STOK_KODU IN (SELECT GRUPKOD FROM TBLMALIGRUP)
ALTER VIEW [dbo].[MND_VLK01_FATURA] AS SELECT V.FISNO,V.STHAR_TARIH,TBLCASABIT.CARI_ISIM,V.STHAR_CARIKOD,TBLSTSABIT.STOK_ADI,V.STHAR_NF,V.STHAR_GCMIK,V.STHAR_KDV,
(V.STHAR_GCMIK * V.STHAR_NF * V.STHAR_KDV / 100) AS KDV_TUT,
(((CASE WHEN V.STHAR_GCKOD='G' THEN V.STHAR_GCMIK ELSE 0 END) *
V.STHAR_NF) + ((CASE WHEN V.STHAR_GCKOD='G' THEN V.STHAR_GCMIK ELSE 0 END) * V.STHAR_NF * V.STHAR_KDV / 100)) AS ALIS_TOPLAM,
(((CASE WHEN V.STHAR_GCKOD='C' THEN V.STHAR_GCMIK ELSE 0 END) *
V.STHAR_NF) + ((CASE WHEN V.STHAR_GCKOD='C' THEN V.STHAR_GCMIK ELSE 0 END) * V.STHAR_NF * V.STHAR_KDV / 100)) AS SATIS_TOPLAM,
(CASE WHEN V.STHAR_GCKOD='G' THEN V.STHAR_GCMIK ELSE 0 END) AS GIRIS_MIKTAR,
(CASE WHEN V.STHAR_GCKOD='C' THEN V.STHAR_GCMIK ELSE 0 END) AS CIKIS_MIKTAR,TBLSTSABIT.OLCU_BR1,V.STHAR_FTIRSIP,
V.STHAR_GCKOD,V.STHAR_HTUR,
CASE WHEN V.STHAR_FTIRSIP='1' AND V.STHAR_GCKOD='C' AND V.STHAR_HTUR='J' THEN 'SATFAT'
WHEN V.STHAR_FTIRSIP='2' AND V.STHAR_GCKOD='G' AND V.STHAR_HTUR='J' THEN 'ALFAT'
WHEN V.STHAR_FTIRSIP='2' AND V.STHAR_GCKOD='G' AND V.STHAR_HTUR='L' THEN 'SATISTAN_IADE'
WHEN V.STHAR_FTIRSIP='1' AND V.STHAR_GCKOD='C' AND V.STHAR_HTUR='L' THEN 'ALISTAN_IADE'
WHEN V.STHAR_FTIRSIP='1' AND V.STHAR_GCKOD='C' AND V.STHAR_HTUR='K' THEN 'KAPALI_FATURA'
WHEN V.STHAR_FTIRSIP='1' AND V.STHAR_GCKOD='C' AND V.STHAR_HTUR='I' THEN 'MUHTELIF_FATURA'
ELSE '' END AS BELGE_TIPI,TBLSTSABIT.GRUP_KODU,
((CASE WHEN V.STHAR_GCKOD='G' THEN V.STHAR_GCMIK ELSE 0 END) *
V.STHAR_NF) AS NETTUTAR,
(SELECT TOP 1 ACIK1 FROM TBLFATUEK F WHERE F.FATIRSNO=V.FISNO AND F.FKOD=V.STHAR_FTIRSIP) as ACIK1,
(SELECT TOP 1 ACIK2 FROM TBLFATUEK F WHERE F.FATIRSNO=V.FISNO AND F.FKOD=V.STHAR_FTIRSIP) as ACIK2,
V.PLASIYER_KODU,V.SUBE_KODU
FROM TBLSTHAR V INNER JOIN TBLSTSABIT ON V.STOK_KODU=TBLSTSABIT.STOK_KODU
INNER JOIN TBLCASABIT ON TBLCASABIT.CARI_KOD=V.STHAR_CARIKOD
ALTER VIEW [dbo].[MND_VLK19_SIPARISTESLIM] AS
SELECT STHAR_TARIH AS TARIH,S.FISNO,C.CARI_ISIM,S.STOK_KODU,SS.STOK_ADI,STHAR_GCMIK AS MIKTAR,
FIRMA_DOVTUT AS GELEN_MIKTAR,(STHAR_GCMIK-FIRMA_DOVTUT) AS BAKIYE,S.STHAR_TESTAR AS TESLIM_TARIHI,
(SELECT MIN(STHAR_TARIH) FROM TBLSTHAR WHERE STHAR_SIPNUM=S.FISNO)AS GELDIGI_TARIH,
DATEDIFF(DAY, S.STHAR_TESTAR,(SELECT MIN(STHAR_TARIH) FROM TBLSTHAR WHERE STHAR_SIPNUM=S.FISNO)) AS GECIKME
FROM TBLSIPATRA S,TBLCASABIT C,TBLSTSABIT SS
WHERE S.STHAR_ACIKLAMA=C.CARI_KOD AND S.STOK_KODU=SS.STOK_KODU
AND STHAR_GCKOD='G'
CREATE VIEW [dbo].[MND_VLK14_BABS] AS
SELECT SERI_NO, CONVERT(VARCHAR,DEKONT_NO) AS NUMARA, SUBE_KODU, TARIH, CARI, SATIS_KDV, ALIS_KDV, TUTAR, TOPTUTAR, PROJE
,(SELECT CARI_ISIM FROM TBLCASABIT WHERE CARI_KOD=V.CARI)AS CARI_ISIM,
(SELECT TOP 1 PLASIYER FROM TBLDEKOTRA WHERE SUBE_KODU=V.SUBE_KODU AND SERI_NO=V.SERI_NO
AND DEKONT_NO=V.DEKONT_NO AND PLASIYER IS NOT NULL) AS PLASIYER
FROM MND_VLK11_BABS V WHERE TUTAR<>0 AND (CARI LIKE '120%' OR CARI LIKE '320%')
AND PROJE='1'
UNION ALL
SELECT 'ALFAT',FATIRS_NO,SUBE_KODU,TARIH,CARI_KODU,'0',KDV,(GENELTOPLAM-KDV),GENELTOPLAM,PROJE_KODU,
(SELECT CARI_ISIM FROM TBLCASABIT WHERE CARI_KOD=F.CARI_KODU),PLA_KODU FROM TBLFATUIRS F
WHERE FTIRSIP='2'
USE [MND2010]
GO
/****** Object: View [dbo].[_01_MND_HAMMADDE_DEPO] Script Date: 02/17/2010 07:57:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[MND_VLK15_HAMMADDE_DEPO]
AS
SELECT dbo.TBLSTHAR.FISNO, dbo.TBLSTHAR.STHAR_TARIH, dbo.TBLSTHAR.STOK_KODU,
CASE WHEN TBLSTHAR.STHAR_GCKOD = 'G' THEN TBLSTHAR.STHAR_GCMIK ELSE '0' END AS GIRIS_MIKTARI,
CASE WHEN TBLSTHAR.STHAR_GCKOD = 'C' THEN TBLSTHAR.STHAR_GCMIK ELSE '0' END AS CIKIS_MIKTARI,
CASE WHEN TBLSTHAR.STHAR_GCKOD = 'G' THEN TBLSTHAR.STHAR_GCMIK * TBLSTHAR.STHAR_NF ELSE '0' END AS GIRIS_TUTARI,
CASE WHEN TBLSTHAR.STHAR_GCKOD = 'C' THEN TBLSTHAR.STHAR_GCMIK * TBLSTHAR.STHAR_NF ELSE '0' END AS CIKIS_TUTARI,
dbo.TBLSTHAR.STHAR_NF, dbo.TBLSTHAR.STHAR_BF, CASE WHEN TBLSTHAR.STHAR_FTIRSIP = '9' THEN
(SELECT CONVERT(VARCHAR(10), S.DEPO_KODU)
FROM TBLSTHAR S INNER JOIN
TBLSTOKDP D ON D .DEPO_KODU = S.DEPO_KODU
WHERE S.STHAR_FTIRSIP = 8 AND S.SIRA = TBLSTHAR.SIRA AND S.STHAR_TARIH = TBLSTHAR.STHAR_TARIH AND
S.FISNO = TBLSTHAR.FISNO AND S.STOK_KODU = TBLSTHAR.STOK_KODU) ELSE CONVERT(VARCHAR(10), TBLSTHAR.DEPO_KODU)
END AS KAYNAK_DEPOKODU, CASE WHEN TBLSTHAR.STHAR_FTIRSIP = '9' THEN
(SELECT TOP 1 D .DEPO_ISMI + '(' + CONVERT(VARCHAR(10), S.DEPO_KODU) + ')'
FROM TBLSTHAR S INNER JOIN
TBLSTOKDP D ON D .DEPO_KODU = S.DEPO_KODU
WHERE S.STHAR_FTIRSIP = 8 AND S.SIRA = TBLSTHAR.SIRA AND S.STHAR_TARIH = TBLSTHAR.STHAR_TARIH AND
S.FISNO = TBLSTHAR.FISNO AND S.STOK_KODU = TBLSTHAR.STOK_KODU)
ELSE dbo.TBLSTOKDP.DEPO_ISMI + '(' + CONVERT(VARCHAR(10), TBLSTHAR.DEPO_KODU) + ')' END AS KAYNAK_DEPO,
dbo.TBLSTHAR.STHAR_FTIRSIP, CASE WHEN TBLSTHAR.STHAR_FTIRSIP IS NULL
THEN TBLSTHAR.DEPO_KODU ELSE DAT_G.DEPO_KODU END AS HEDEF_DEPOKODU,
CASE WHEN TBLSTHAR.STHAR_FTIRSIP = '8' THEN TBLSTOKDP_1.DEPO_ISMI + '(' + CONVERT(VARCHAR(10), DAT_G.DEPO_KODU)
+ ')' WHEN TBLSTHAR.STHAR_FTIRSIP = '9' THEN dbo.TBLSTOKDP.DEPO_ISMI + '(' + CONVERT(VARCHAR(10), dbo.TBLSTHAR.DEPO_KODU)
+ ')' WHEN TBLSTHAR.STHAR_FTIRSIP = '1' THEN DAT_G.CARI_ISIM WHEN TBLSTHAR.STHAR_FTIRSIP = '2' THEN DAT_G.CARI_ISIM WHEN TBLSTHAR.STHAR_FTIRSIP
= '4' THEN DAT_G.CARI_ISIM WHEN TBLSTHAR.STHAR_FTIRSIP IS NULL THEN dbo.TBLSTOKDP.DEPO_ISMI + '(' + CONVERT(VARCHAR(10),
dbo.TBLSTHAR.DEPO_KODU) + ')' ELSE '' END AS HEDEF_DEPO,
CASE WHEN TBLSTHAR.STHAR_FTIRSIP = '1' THEN 'S.FATURA' WHEN TBLSTHAR.STHAR_FTIRSIP IN ('8', '9')
THEN 'DEP.ARA.TRANS.' WHEN TBLSTHAR.STHAR_FTIRSIP = '2' THEN 'A.FATURA' WHEN TBLSTHAR.STHAR_FTIRSIP = '4' THEN 'A.IRSALIYE' WHEN TBLSTHAR.STHAR_FTIRSIP
IS NULL THEN 'DEVİR' ELSE '' END AS BELGE_TURU, dbo.TBLSTHAR.SUBE_KODU, dbo.TBLSTHAR.STHAR_CARIKOD,
dbo.TBLSTSABIT.OLCU_BR1
FROM dbo.TBLSTHAR INNER JOIN
dbo.TBLSTOKDP ON dbo.TBLSTHAR.DEPO_KODU = dbo.TBLSTOKDP.DEPO_KODU LEFT OUTER JOIN
dbo.TBLSTSABIT ON dbo.TBLSTHAR.STOK_KODU = dbo.TBLSTSABIT.STOK_KODU LEFT OUTER JOIN
(SELECT TBLSTHAR_1.DEPO_KODU, TBLSTHAR_1.FISNO, TBLSTHAR_1.STHAR_TARIH, TBLSTHAR_1.STHAR_FTIRSIP,
TBLSTHAR_1.STOK_KODU, TBLSTHAR_1.SIRA, TBLSTHAR_1.STHAR_ACIKLAMA, dbo.TBLCASABIT.CARI_ISIM
FROM dbo.TBLSTHAR AS TBLSTHAR_1 LEFT OUTER JOIN
dbo.TBLCASABIT ON TBLSTHAR_1.STHAR_ACIKLAMA = dbo.TBLCASABIT.CARI_KOD) AS DAT_G ON
dbo.TBLSTHAR.STHAR_TARIH = DAT_G.STHAR_TARIH AND dbo.TBLSTHAR.FISNO = DAT_G.FISNO AND
dbo.TBLSTHAR.STOK_KODU = DAT_G.STOK_KODU AND dbo.TBLSTHAR.SIRA = DAT_G.SIRA AND DAT_G.STHAR_FTIRSIP IN ('9', '1', '2', '4')
LEFT OUTER JOIN
dbo.TBLSTOKDP AS TBLSTOKDP_1 ON DAT_G.DEPO_KODU = TBLSTOKDP_1.DEPO_KODU
WHERE (dbo.TBLSTHAR.DEPO_KODU IN ('1')) AND (dbo.TBLSTHAR.STHAR_FTIRSIP NOT IN ('B'))
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "TBLSTHAR"
Begin Extent =
Top = 6
Left = 38
Bottom = 114
Right = 215
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "TBLSTOKDP"
Begin Extent =
Top = 6
Left = 253
Bottom = 114
Right = 435
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "TBLSTSABIT"
Begin Extent =
Top = 114
Left = 38
Bottom = 222
Right = 258
End
DisplayFlags = 280
TopColumn = 11
End
Begin Table = "DAT_G"
Begin Extent =
Top = 114
Left = 296
Bottom = 222
Right = 466
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "TBLSTOKDP_1"
Begin Extent =
Top = 222
Left = 38
Bottom = 330
Right = 220
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'_01_MND_HAMMADDE_DEPO'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'Or = 1350
End
End
End
' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'_01_MND_HAMMADDE_DEPO'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'VIEW', @level1name=N'_01_MND_HAMMADDE_DEPO'
CREATE VIEW [MND_VLK15_HAMMADDE_STOKHAREKET] AS
SELECT STOK_KODU,(SELECT STOK_ADI FROM TBLSTSABIT WHERE STOK_KODU=V.STOK_KODU) AS STOK_ADI,
FISNO,STHAR_GCMIK AS MIKTAR,CASE WHEN STHAR_GCKOD='G' THEN 'GIRIS' WHEN STHAR_GCKOD='C' THEN 'CIKIS' ELSE '' END AS
G_C,STHAR_TARIH AS TARIH,DEPO_KODU,STHAR_ACIKLAMA AS ACIKLAMA,
CASE WHEN STHAR_HTUR='A' THEN 'DEVIR' WHEN STHAR_HTUR='B' THEN 'DEPO TRANSFERİ' WHEN
STHAR_HTUR='C' THEN 'URETIM' WHEN STHAR_HTUR='H' THEN 'IRSALIYE' WHEN STHAR_HTUR='J'
THEN 'ACIK FATURA' WHEN STHAR_HTUR='L' THEN 'IADE' ELSE STHAR_HTUR END AS TIP FROM TBLSTHAR V
WHERE DEPO_KODU='1'
CREATE VIEW [MND_VLK21_PLASIYERSIZDEKONT] AS
Select SERI_NO,NUMARA,SUBE_KODU,CARI,(SELECT PLASIYER_KODU FROM TBLCASABIT WHERE CARI_KOD=V.CARI)AS PLASIYER
from MND_VLK14_BABS V WHERE CARI LIKE '120%' AND PLASIYER=''
UPDATE TBLDEKOTRA SET TBLDEKOTRA.PLASIYER=V.PLASIYER
FROM TBLDEKOTRA,MND_VLK21_PLASIYERSIZDEKONT V
WHERE TBLDEKOTRA.SERI_NO=V.SERI_NO AND TBLDEKOTRA.DEKONT_NO=V.NUMARA
AND TBLDEKOTRA.SUBE_KODU=V.SUBE_KODU AND TBLDEKOTRA.KOD=V.CARI AND TBLDEKOTRA.C_M='C'
AND KOD LIKE '120%'
SELECT * INTO Y3STOKUPDATE
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:B.xls;Extended Properties=Excel 8.0')...[Sayfa1$] V
where STOK_KODU IS NOT NULL
SELECT * FROM YSTOKUPDATE
UPDATE TBLSTSABIT SET KOD_1=V.KOD_1,KOD_2=V.KOD_2,KOD_3=V.KOD_3,KOD_4=V.KOD_4
FROM TBLSTSABIT,Y3STOKUPDATE V
WHERE TBLSTSABIT.STOK_KODU=V.STOK_KODU
