Order-Based Last 7-Day Inter-Warehouse Transfer Report (Netsis SQL)
Raw materials sent to production via Transfer Between Warehouses are reported to Netsiste, based on the order, over the SQL code specified below.
================================================== ==============================
SELECT dbo.TBLSTHAR.FISNO, dbo.TBLSTHAR.STOK_KODU, dbo.TBLSTHAR.STHAR_GCMIK, dbo.TBLSTHAR.STHAR_GCKOD,
dbo.TBLSTHAR.STHAR_TARIH, dbo.TBLSTHAR.DEPO_KODU, dbo.TBLFATUEK.ACIK1 AS DEPO_SIPARISNO, dbo.TBLFATUEK.ACIK2 AS URETIM_SIPARISNO,
dbo.TBLFATUEK.ACIK4 AS USER, dbo.TBLFATUEK.ACIK5 AS DATE
FROM dbo.TBLSTHAR LEFT OUTER JOIN
dbo.TBLFATUEK ON dbo.TBLSTHAR.FISNO = dbo.TBLFATUEK.FATIRSNO
WHERE (dbo.TBLSTHAR.STHAR_FTIRSIP = '8') OR
(dbo.TBLSTHAR.STHAR_FTIRSIP = '9')
GROUP BY dbo.TBLSTHAR.FISNO, dbo.TBLSTHAR.STOK_KODU, dbo.TBLSTHAR.STHAR_GCMIK, dbo.TBLSTHAR.STHAR_GCKOD, dbo.TBLSTHAR.STHAR_TARIH,
dbo.TBLSTHAR.DEPO_KODU, dbo.TBLFATUEK.ACIK1, dbo.TBLFATUEK.ACIK4, dbo.TBLFATUEK.ACIK5, dbo.TBLFATUEK.ACIK2
HAVING (dbo.TBLSTHAR.STHAR_TARIH> DATEADD (day, DATEDIFF (day, 7, GETDATE ()), 0)) AND (dbo.TBLSTHAR.DEPO_Code = 30)
Card Stock Copy Triger (SQL)
It ensures that a copy of any stock card registered to Netsis over the trigger specified below is automatically copied under a different database. It provides great convenience to the user in the processes created for test purposes.
================================================== ==============================
CREATE TRIGGER [dbo]. [TBLSTSABIT_I] ON [dbo]. [TBLSTSABIT]
FOR INSERT
AS
BEGIN
INSERT INTO SPARE..TBLSTSABIT
SELECT
I SUBE_KO was ISLETME_KO was STOK_KO was URETICI_KO was STOK_A was GRUP_KO, KOD_1, KOD_2, KOD_3, KOD_4, KOD_5 was SATICI_KO, OLCU_BR1, OLCU_BR2, PAY_1, PAYDA_1, OLCU_BR3, PAY2, PAYDA2, FIAT_BIRI me, AZAMI_STOK, ASGARI_STOK, TEMIN_SURE of KUL_MIK, RISK_SURE of ZAMAN_BIRI me, SATIS_FIAT1, SATIS_FIAT2, SATIS_FIAT3, SATIS_FIAT4, SAT_DOV_TIP, DOV_ALIS_FIAT, DOV_MAL_FIAT, DOV_SATIS_FIAT was MUH_DETAYKO, BIRIM_AGIRLIK, NAKLIYET_TUT, KDV_OR Thani, ALIS_DOV_TIP was DEPO_KO, DOV_T is, URET_OLCU_B is, Components of products, FORMUL_TOPLA he was UPDATE_KO, MAX_ISKONTO, ECZACI_KAR of QUANTITY, MAL_FAZLA of KDV_TENZIL_OR that, LOCKS, ONCEKI_KOD, SONRAKI_KOD, Barcode1, BARKOD2, BARKOD3 was ALIS_KDV_KO, ALIS_FIAT1, ALIS_FIAT2, ALIS_FIAT3, ALIS_FIAT4, LOT_SIZ to, MIN_SIP_MIKT is, SABIT_SIP_ARALIK, SIP_POLITIKA of OZELLIK_KODU1, OZELLIK_KODU2, OZELLIK_KODU3, OZELLIK_KODU4, OZELLIK_KODU5, OPSIYON_KODU1, OPSIYON_KODU2, OPSIYON_KODU3, OPSIYON_KODU4, OPTION_ CODE5, BILESEN_OP_ CODE, SIP_VER_MAL, ELDE_BUL_MAL, YEAR_TAH_KUL_MIK, EKON_SIP_MIKTA R ESKI_RECET, let OTOMATIK_URET, ALFKOD, SAFKOD, KODTUR of S_YEDEK1, S_YEDEK2, F_YEDEK3, F_YEDEK4, C_YEDEK5, C_YEDEK6, B_YEDEK7, I_YEDEK8, L_YEDEK9, D_YEDEK10, 'E', 'E', SERI_BAK, SERI_MIK, SERI_GIR_OT, SERI_CIK_OT, SERI_BASLANGIC, I FIYATKO, FIYATSIRA, you will PLANNED, LOT_SIZECUSTOM is, MIN_SIP_MIKTARCUSTOM it was GUMRUKTARIFEKO was ABCKO was PERFORMANSKO, SATICISIPKILIT, MUSTERISIPKILIT, SATINALMAKILIT, SATISKILIT, EN, HEIGHT, wIDTH, SIPLIMITV it was SONSTOKKO, ONAYTIP of approval, FIKTIF_ not, CONFIGURE, SBOMVARM of BAGLISTOKKOD, YAPKOD, ALISTALTEKKILIT, SATISTALTEKKILIT
FROM INSERTED WHERE STOK_KODU NOT IN (SELECT STOK_KODU FROM BACKUP.TBLSTSABIT)
--WHERE LEFT (INSERTED.CARI_KOD, 2) = '04'
END
Open Vendor Order Report Monthly Sorted (Netsis SQL)
A simple and understandable monthly sequential report on the Netsiste Vendor Order is presented over the following SQL code. It is the output of the query taken over the visual code shown.
================================================== ==============================
SELECT ROW_NUMBER () OVER (ORDER BY STHAR_TARIH DESC) AS Sequence, CONVERT (VARCHAR (10), STHAR_TARIH, 103) AS Order_Date, CONVERT (VARCHAR (10),
STHAR_TESTAR, 103) AS Delivery_Date, FISNO AS Sip_No, STOK_KODU AS Stock_Code, CONVERT (varchar (128), cast (STHAR_NF AS money), 1) AS Br_Price,
CONVERT (varchar (128), cast (STHAR_GCMIK AS money), 1) AS Order_Quantity, CONVERT (varchar (128), cast (FIRMA_DOVTUT AS money), 1) AS Delivery_Delivery,
CONVERT (varchar (128), cast (STHAR_GCMIK - FIRMA_DOVTUT AS money), 1) AS Remainder
FROM TBLSIPATRA
WHERE FIRMA_DOVTUT <STHAR_GCMIK AND STHAR_FTIRSIP = '7' AND STHAR_HTUR = 'H'
Current Finance Analysis Report (Netsis SQL)
With the following SQL code, Buyer and Seller Current Analysis Report is available, and you can make many different analyzes with the excel pivot table that will be created over the code.
================================================== ==============================
SELECT dbo.TBLSTHAR.SUBE_Code, dbo.TBLSTHAR.STOK_KODU, dbo.TBLSTSABIT.STOK_NAME, dbo.TBLSTSABIT.GRUP_Code AS SGRUP_Code,
dbo.TBLSTGRUP.GRUP_ISIM AS SGRUP_ISIM, dbo.TBLSTSABIT.KOD_1 AS SKOD_1, dbo.TBLSTOKKOD1.GRUP_ISIM AS SKOD1_ISIM,
dbo.TBLSTSABIT.KOD_2 AS SKOD_2, dbo.TBLSTOKKOD2.GRUP_ISIM AS SKOD2_ISIM, dbo.TBLSTSABIT.SELLER_KODU, dbo.TBLSTHAR.STHAR_ACIKLAMA,
dbo.TBLCASABIT.CARI_ISIM, dbo.TBLCASABIT.CARI_TIP, dbo.TBLCASABIT.GRUP_KODU AS CGRUP_KODU, dbo.TBLCASABIT.RAPOR_KODU1 AS CKOD_1,
dbo.TBLCARIKOD1.GRUP_ISIM AS CKOD1_ISIM, dbo.TBLCASABIT.RAPOR_KODU2 AS CKOD_2, dbo.TBLCASABIT.CARI_IL, ​​dbo.TBLCASABIT.CARI_ILCE,
dbo.TBLSTHAR.STHAR_HTUR, dbo.TBLSTHAR.STHAR_KOD1, dbo.TBLSTHAR.STHAR_KOD2, dbo.TBLSTHAR.STHAR_TARIH, dbo.TBLSTHAR.FISNO AS STHAR_FISNO,
dbo.TBLSTHAR.AMBAR_KABULNO, dbo.TBLSTHAR.IRSALIYE_NO, dbo.TBLSTHAR.STHAR_ODEGUN, dbo.TBLSTHAR.STHAR_BF, dbo.TBLSTHAR.STHAR_NF,
dbo.TBLSTHAR.STHAR_IAF, (dbo.TBLSTHAR.STHAR_BF - dbo.TBLSTHAR.STHAR_NF) * dbo.TBLSTHAR.STHAR_GCMIK AS ISKONTOTUT,
dbo.TBLSTHAR.STHAR_SATISK, dbo.TBLSTHAR.STHAR_MALFISK, dbo.TBLSTSABIT.OLCU_BR1, dbo.TBLSTSABIT.OLCU_BR2, dbo.TBLSTSABIT.OLCU_BR3,
(CASE WHEN TBLSTHAR.STHAR_GCKOD = 'C' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END) AS CIKISM,
(CASE WHEN TBLSTHAR.STHAR_GCKOD = 'C' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END)
* (CASE WHEN TBLSTSABIT.PAYDA_1 = 0 THEN 0 ELSE (TBLSTSABIT.PAY_1 / TBLSTSABIT.PAYDA_1) END) AS CIKISM2,
(CASE WHEN TBLSTHAR.STHAR_GCKOD = 'C' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END)
* (CASE WHEN TBLSTSABIT.PAYDA2 = 0 THEN 0 ELSE (TBLSTSABIT.PAY2 / TBLSTSABIT.PAYDA2) END) AS CIKISM3,
(CASE WHEN TBLSTHAR.STHAR_GCKOD = 'C' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END) * dbo.TBLSTHAR.STHAR_NF AS NETCIKISTUT,
(CASE WHEN TBLSTHAR.STHAR_GCKOD = 'C' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END) * dbo.TBLSTHAR.STHAR_BF AS BRUTCIKISTUT,
dbo.TBLSTHAR.STHAR_KDV, dbo.TBLSTHAR.STHAR_GCMIK * dbo.TBLSTHAR.STHAR_NF * dbo.TBLSTHAR.STHAR_KDV / 100 AS KDV_TUT,
dbo.TBLSTHAR.STHAR_SIPNUM, dbo.TBLSTHAR.STHAR_DOVTIP, dbo.TBLSTHAR.STHAR_DOVFIAT,
dbo.TBLSTHAR.STHAR_GCMIK * dbo.TBLSTHAR.STHAR_DOVFIAT AS DOVIZ_ AMOUNT,
(CASE WHEN TBLSTHAR.STHAR_GCKOD = 'C' THEN TBLSTHAR.STHAR_GCMIK2 ELSE 0 END) AS SECOND_CIKIS_MIKTAR,
(CASE WHEN TBLSTHAR.STHAR_GCKOD = 'C' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END)
* dbo.TBLSTHAR.STHAR_NF + (CASE WHEN TBLSTHAR.STHAR_GCKOD = 'C' THEN TBLSTHAR.STHAR_GCMIK ELSE 0 END)
* dbo.TBLSTHAR.STHAR_NF * dbo.TBLSTHAR.STHAR_KDV / 100 AS SATIS_TOPLAM, dbo.TBLSTHAR.CEVRIM,
(CASE WHEN TBLSTHAR.STHAR_GCKOD = 'C' THEN (TBLSTHAR.CEVRIM * TBLSTHAR.STHAR_GCMIK * TBLSTHAR.STHAR_NF) ELSE 0 END) AS WAITING_ADATI,
dbo.TBLSTSABITEK.TUR, dbo.TBLSTSABITEK.MGRUP AS MRUP, dbo.TBLSTSABITEK.INGISIM, dbo.TBLSTSABITEK.KULL1N, dbo.TBLSTSABITEK.KULL2N,
dbo.TBLSTSABITEK.KULL1S, dbo.TBLSTSABITEK.KULL2S, dbo.TBLSTHAR.DEPO_Code, dbo.TBLSTHAR.PROMATION_Code, dbo.TBLSTHAR.BAGLANTI_NO,
dbo.TBLSTHAR.VADE_TARIHI, dbo.TBLSTHAR.LISTE_NO, dbo.TBLSTHAR.LISTE_FIAT, dbo.TBLCARIKOD2.GRUP_NAME AS CKOD2_NAME,
dbo.TBLSTHAR.PLASIYER_KODU AS PLAS_KOD, dbo.TBLSTHAR.PROJE_KODU, dbo.TBLSTHAR.EKALAN_NEDEN, dbo.TBLSTHAR.EKALAN,
dbo.TBLSTHAR.EKALAN1 AS EKALAN2, dbo.TBLSTHAR.MUH_KODU AS MUHKODU, dbo.TBLPROJE.PROJE_ACIKLAMA, (CASE WHEN (TBLSTSABIT.B_YEDEK7 = 1) OR
(TBLSTSABIT.B_YEDEK7 = 2) THEN 'E' ELSE 'H' END) AS KITSTOGU,
(CASE WHEN TBLSTHAR.L_YEDEK9 = - 1 THEN 'K' ELSE (CASE WHEN TBLSTHAR.L_YEDEK9> 0 THEN 'B' ELSE 'N' END) END) AS KITH MARKET,
dbo.TBLSTSABIT.KOD_3 AS KOD3, dbo.TBLSTSABIT.KOD_4 AS KOD4, dbo.TBLSTSABIT.KOD_5 AS KOD5, dbo.TBLCASABIT.RAPOR_KODU3 AS CKOD_3,
dbo.TBLCASABIT.RAPOR_KODU4 AS CKOD_4, dbo.TBLCASABIT.RAPOR_KODU5 AS CKOD_5, dbo.TBLCASABIT.PLASIYER_KODU AS CPLSKOD,
dbo.TBLSTSABIT.BUNIM_WEIGHT, dbo.TBLFATUIRS.FAT_ALTM1, dbo.TBLFATUIRS.FAT_ALTM2, dbo.TBLFATUIRS.EXPORTTYPE, dbo.TBLFATUIRS.EXGUMRUKNO,
dbo.TBLFATUIRS.EXGUMTARIH, dbo.TBLFATUIRS.EXFIILITARIH, dbo.TBLFATUIRS.EXPORTREFNO, dbo.TBLSTHAR.IRSALIYE_TARIH, dbo.TBLSTHAR.STHAR_TESTAR,
dbo.TBLFATUIRS.FATIRS_NO, dbo.TBLFATUIRS.TARIH AS FTIRSTARIH
FROM dbo.TBLSTHAR INNER JOIN
dbo.TBLSTSABIT ON dbo.TBLSTHAR.STOK_KODU = dbo.TBLSTSABIT.STOK_KODU LEFT OUTER JOIN
dbo.TBLSTGRUP ON dbo.TBLSTSABIT.GRUP_KODU = dbo.TBLSTGRUP.GRUP_KOD LEFT OUTER JOIN
dbo.TBLCASABIT ON dbo.TBLSTHAR.STHAR_ACIKLAMA = dbo.TBLCASABIT.CARI_KOD INNER JOIN
dbo.TBLSTSABITEK ON dbo.TBLSTSABIT.STOK_KODU = dbo.TBLSTSABITEK.STOK_KODU LEFT OUTER JOIN
dbo.TBLSTOKKOD1 ON dbo.TBLSTSABIT.KOD_1 = dbo.TBLSTOKKOD1.GRUP_KOD LEFT OUTER JOIN
dbo.TBLCARIKOD1 ON dbo.TBLCASABIT.RAPOR_KODU1 = dbo.TBLCARIKOD1.GRUP_KOD LEFT OUTER JOIN
dbo.TBLCARIKOD2 ON dbo.TBLCASABIT.RAPOR_KODU2 = dbo.TBLCARIKOD2.GRUP_KOD LEFT OUTER JOIN
dbo.TBLSTOKKOD2 ON dbo.TBLSTSABIT.KOD_2 = dbo.TBLSTOKKOD2.GRUP_KOD LEFT OUTER JOIN
dbo.TBLPROJE ON dbo.TBLSTHAR.PROJE_KODU = dbo.TBLPROJE.PROJE_KODU FULL OUTER JOIN
dbo.TBLFATUIRS ON dbo.TBLSTHAR.FISNO = dbo.TBLFATUIRS.FATIRS_NO AND dbo.TBLSTHAR.STHAR_ACIKLAMA = dbo.TBLFATUIRS.CARI_KODU
WHERE (dbo.TBLSTHAR.STHAR_GCKOD = 'C')
Import Report (Netsis SQL)
It provides a detailed report of the Import Vendor Lists opened in Netsist over the SQL code specified below.
================================================== ==============================
SELECT SM.FATIRS_NO AS ORDER_NO, CS.CARI_ISIM AS SUPPLIER_NAME,
SS.STOK_ADI AS STOCK_NAME,
SS.STOK_KODU AS STOCK_CODE, SP.STHAR_TARIH AS ORDER_DATE, SP.D_YEDEK10 AS [REQUIRED ARRIVAL DATE],
SP.STHAR_TESTAR AS SUPPLIER_CONFIRMED_ARRIVAL_DATE, SP.STHAR_GCMIK AS ORDERED_QUANTITY, ISNULL
((SELECT SUM (STHAR_GCMIK) AS Expr1
FROM dbo.TBLSTHAR AS SH WITH (NOLOCK)
WHERE (STHAR_SIPNUM = SM.FATIRS_NO) AND (STRA_SIPKONT = SP.SIRA) AND (STOK_KODU = SP.STOK_KODU) AND (STHAR_FTIRSIP IN ('2', '4'))), 0)
AS RECEIVED_QUANTITY, SP.STHAR_GCMIK, SM.TIPI
FROM dbo.TBLSIPAMAS AS SM WITH (NOLOCK) INNER JOIN
dbo.TBLSIPATRA AS SP WITH (NOLOCK) ON SP.FISNO = SM.FATIRS_NO AND SP.STHAR_ACIKLAMA = SM.CARI_KODU AND SP.SUBE_KODU = SM.SUBE_KODU AND
SP.STHAR_FTIRSIP = SM.FTIRSIP INNER JOIN
dbo.TBLCASABIT AS CS WITH (NOLOCK) ON CS.CARI_KOD = SM.CARI_KODU INNER JOIN
dbo.TBLSTSABIT AS SS WITH (NOLOCK) ON SS.STOK_KODU = SP.STOK_KODU LEFT OUTER JOIN
PILOT.dbo.EGEM_10_DEPO_BAKIYE AS PLT ON SS.STOK_KODU = PLT.STOK_KODU
WHERE (SP.STHAR_HTUR = 'H') AND (SM.TIPI = 6)
Receiving Query Results by Mail by automatically transferring them to Excel (SQL)
You can run any query over the SQL code specified below and automatically transfer the results to Excel via mail. In addition, if you define the specified code as Jobs on SQL Server, the reports will be sent to you automatically by e-mail at the times you specify.
================================================== ==============================
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'sql',
@recipients = 'ercanozyanar@gmail.com',
@subject = 'EMAIL TITLE TO SEND',
@body = 'SEND MAIL TEXT',
@importance = 'Normal',
@sensitivity = 'Normal',
@file_attachments = null,
@query = 'select * from TEST',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'TEXT.XLS',
@query_result_header = 1,
@query_result_width = 600,
@query_result_separator = '',
@exclude_query_output = 1,
@append_query_error = 1,
@query_result_no_padding = 1