SQL - Netsis SQL

Report sul trasferimento tra magazzino degli ultimi 7 giorni basato sugli ordini (Netsis SQL)

Le materie prime inviate alla produzione tramite Trasferimento tra magazzini vengono segnalate a Netsiste, in base all'ordine, tramite il codice SQL specificato di seguito.

================================================== ==============================

SELEZIONA 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 COME UTENTE, dbo.TBLFATUEK.ACIK5 COME DATA
DA dbo.TBLSTHAR SINISTRA ESTERNO JOIN
dbo.TBLFATUEK SU 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)

Copia cartoncino Triger (SQL)

Assicura che una copia di qualsiasi carta stock registrata su Netsis tramite il trigger specificato di seguito venga automaticamente copiata in un database diverso. Fornisce grande comodità all'utente nei processi creati a scopo di test.

================================================== ==============================
CREA TRIGGER [dbo]. [TBLSTSABIT_I] ON [dbo]. [TBLSTSABIT]
PER INSERTO
COME
INIZIO
INSERIRE NEL RICAMBIO..TBLSTSABIT
SELEZIONARE
I SUBE_KO era ISLETME_KO era STOK_KO era URETICI_KO era STOK_A era GRUP_KO, KOD_1, KOD_2, KOD_3, KOD_4, KOD_5 era SATICI_KO, OLCU_BR1, OLCU_BR2, PAY_1, FIAT_KO, KOD_4, KOD_5 era SATICI_KO, OLCU_BR1, OLCU_BR2, PAY_1, FIAT_KO, OLCAYS_BR1, PAYI_SU, KODI_BR, ASGAY2 RISK_SURE di ZAMAN_BIRI me, SATIS_FIAT1, SATIS_FIAT2, SATIS_FIAT3, SATIS_FIAT4, SAT_DOV_TIP, DOV_ALIS_FIAT, DOV_MAL_FIAT, DOV_SATIS_FIAT era MUH_DETAYKO, BIRIM_AGIRLIK, NAKLIYET_TUT, KDV_OR Thani, ALIS_DOV_TIP era DEPO_KO, DOV_T è, URET_OLCU_B è, componenti di prodotti, FORMUL_TOPLA era UPDATE_KO, MAX_ISKONTO , ECZACI_KAR di QUANTITÀ, MAL_FAZLA di KDV_TENZIL_OR che, serrature, ONCEKI_KOD, SONRAKI_KOD, Barcode1, BARKOD2, BARKOD3 era ALIS_KDV_KO, ALIS_FIAT1, ALIS_FIAT2, ALIS_FIAT3, ALIS_FIAT4, LOT_SIZ a, MIN_SIP_MIKT è, SABIT_SIP_ARALIK, SIP_POLITIKA di 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_TAKIP_KUL R ESKI_RECET, let OTOMATIK_URET, ALFKOD, SAFKOD, KODTUR di S_YEDEK1, S_YEDEK2, F_YEDEK3, F_YEDEK4, C_YEDEK5, C_YEDEK6, B_YEDEK7, I_YEDEK_, L_YEDEK_B_SERKI_, SERKI_KI_, SERKI_KI_, SERKI_KI_, SERKI_B, SERKI I FIYATKO, FIYATSIRA, SARAI PIANIFICATO, LOT_SIZECUSTOM è, MIN_SIP_MIKTARCUSTOM era GUMRUKTARIFEKO era ABCKO era PERFORMANSKO, SATICISIPKILIT, MUSTERISIPKILIT, SATINALMAKILIT, FIGLIO, non era APPROVAZIONE, FIGLIO, SABATO di BAGLISTOKKOD, YAPKOD, ALISTALTEKKILIT, SATISTALTEKKILIT
DA INSERITO DOVE STOK_KODU NON È IN (SELEZIONA STOK_KODU DA BACKUP.TBLSTSABIT)
--DOVE LASCIARE (INSERTED.CARI_KOD, 2) = '04'
FINE

Apri rapporto ordini fornitore ordinato mensilmente (Netsis SQL)

Un semplice e comprensibile rapporto mensile sequenziale sull'ordine del fornitore di Netsiste è presentato sul seguente codice SQL, che è l'output della query che ha assunto il codice visivo mostrato.

================================================== ==============================

SELEZIONA 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
DA TBLSIPATRA
DOVE FIRMA_DOVTUT <STHAR_GCMIK AND STHAR_FTIRSIP = '7' AND STHAR_HTUR = 'H'

Rapporto di analisi finanziaria corrente (Netsis SQL)

Con il seguente codice SQL, è disponibile il rapporto sull'analisi corrente dell'acquirente e del venditore e puoi effettuare molte analisi diverse con la tabella pivot Excel che verrà creata sul codice.

================================================== ==============================

SELEZIONA 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) COME CIKISMO,
(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) OPPURE
(TBLSTSABIT.B_YEDEK7 = 2) THEN 'E' ELSE 'H' END) COME KITSTOGU,
(CASE WHEN TBLSTHAR.L_YEDEK9 = - 1 THEN 'K' ELSE (CASE WHEN TBLSTHAR.L_YEDEK9> 0 THEN 'B' ELSE 'N' END) COME 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
DA dbo.TBLSTHAR INNER JOIN
dbo.TBLSTSABIT SU dbo.TBLSTHAR.STOK_KODU = dbo.TBLSTSABIT.STOK_KODU LEFT OUTER JOIN
dbo.TBLSTGRUP SU dbo.TBLSTSABIT.GRUP_KODU = dbo.TBLSTGRUP.GRUP_KOD LEFT OUTER JOIN
dbo.TBLCASABIT SU 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 SU dbo.TBLSTSABIT.KOD_1 = dbo.TBLSTOKKOD1.GRUP_KOD LEFT OUTER JOIN
dbo.TBLCARIKOD1 SU dbo.TBLCASABIT.RAPOR_KODU1 = dbo.TBLCARIKOD1.GRUP_KOD LEFT OUTER JOIN
dbo.TBLCARIKOD2 SU dbo.TBLCASABIT.RAPOR_KODU2 = dbo.TBLCARIKOD2.GRUP_KOD LEFT OUTER JOIN
dbo.TBLSTOKKOD2 SU dbo.TBLSTSABIT.KOD_2 = dbo.TBLSTOKKOD2.GRUP_KOD LEFT OUTER JOIN
dbo.TBLPROJE SU 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
DOVE (dbo.TBLSTHAR.STHAR_GCKOD = 'C')

Rapporto di importazione (Netsis SQL)

Fornisce un rapporto dettagliato degli elenchi di fornitori di importazione aperti in Netsist sul codice SQL specificato di seguito.

================================================== ==============================

SELEZIONA SM.FATIRS_NO COME ORDER_NO, CS.CARI_ISIM COME SUPPLIER_NAME,
SS.STOK_ADI COME STOCK_NAME,
SS.STOK_KODU AS STOCK_CODE, SP.STHAR_TARIH AS ORDER_DATE, SP.D_YEDEK10 AS [DATA DI ARRIVO RICHIESTO],
SP.STHAR_TESTAR AS SUPPLIER_CONFIRMED_ARRIVAL_DATE, SP.STHAR_GCMIK AS ORDERED_QUANTITY, ISNULL
((SELECT SUM (STHAR_GCMIK) AS Expr1
DA dbo.TBLSTHAR AS SH CON (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
DA dbo.TBLSIPAMAS AS SM CON (NOLOCK) INNER JOIN
dbo.TBLSIPATRA AS SP CON (NOLOCK) SU SP.FISNO = SM.FATIRS_NO E SP.STHAR_ACIKLAMA = SM.CARI_KODU E SP.SUBE_KODU = SM.SUBE_KODU E
SP.STHAR_FTIRSIP = SM.FTIRSIP INNER JOIN
dbo.TBLCASABIT COME CS CON (NOLOCK) SU 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
DOVE (SP.STHAR_HTUR = 'H') AND (SM.TIPI = 6)

Ricezione dei risultati delle query per posta trasferendoli automaticamente in Excel (SQL)

È possibile eseguire qualsiasi query sul codice SQL specificato di seguito e trasferire automaticamente i risultati in Excel tramite posta. Inoltre, se si definisce il codice specificato come Lavori su SQL Server, i report verranno inviati automaticamente tramite posta elettronica agli orari specificati.

================================================== ==============================

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'sql',
@recipients = "ercanozyanar@gmail.com",
@subject = "EMAIL TITLE TO SEND",
@body = "INVIA TESTO MAIL",
@importance = 'Normale',
@sensitivity = 'Normale',
@file_attachments = null,
@query = 'seleziona * da 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