Tag: Sql

SQL STUFF

On By semt20In LOGO ERP, LOGO OBJECTSLeave a comment
 



/****** Object:  UserDefinedFunction [dbo].[BM_210_AYRINTILI_SARFET_FNC]    Script Date: 6.04.2019 12:05:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[BM_210_AYRINTILI_SARFET_FNC]
(
@DATE DATETIME
)
RETURNS @RESULTTABLE TABLE
(
  TARIH NVARCHAR(MAX),
LOGICALREF INT,
PROJECTREF INT,
SOURCEINDEX INT,
Ambar NVARCHAR(50),
Proje NVARCHAR(50),
[Proje Açıklaması] NVARCHAR(150),
[Proje L2] NVARCHAR(150),
[Proje L2 Açıklaması] NVARCHAR(150),
[Proje L3] NVARCHAR(150),
[Proje L3 Açıklaması] NVARCHAR(150),
[Proje L4] NVARCHAR(150),
[Proje L4 Açıklaması] NVARCHAR(150),
[Aktivite Kodu] NVARCHAR(150),
[Aktivite Adı] NVARCHAR(150),
[Malzeme Özel Kodu] NVARCHAR(150),
[Malzeme Özel Kodu Açıklaması] NVARCHAR(150),
[Malzeme Kodu] NVARCHAR(150),
[Malzeme Adı] NVARCHAR(150),
[Birim] NVARCHAR(150),
[Sarf Miktar] FLOAT,
[Sarf Matrah] FLOAT,
KDV FLOAT
)
AS
BEGIN

INSERT INTO @RESULTTABLE
SELECT
STUFF((SELECT DISTINCT ',' + QUOTENAME( FORMAT( S2.DATE_, 'dd/MM/yyyy', 'tr-TR')) FROM
BV_210_01_STINVTOT S2
WHERE S2.INVENNO > -1
AND S2.DATE_ <= @DATE
AND S2.PROJECTREF = BV_210_01_STINVTOT.PROJECTREF AND S2.STOCKREF=BV_210_01_STINVTOT.STOCKREF 

            FOR XML PATH(''), TYPE

            ).value('.', 'NVARCHAR(MAX)')

        ,1,1,''),
0 AS LOGICALREF
,PROJECTREF
,INVENNO AS SOURCEINDEX
,(SELECT NAME FROM L_CAPIWHOUSE WHERE NR = INVENNO AND FIRMNR = 201) AS Ambar
--,(SELECT LEFT(CODE, 4) FROM LG_210_PROJECT WHERE LOGICALREF = PROJECTREF) AS [Proje]
,(SELECT CODE FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL1 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje]
,(SELECT NAME FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL1 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje Açıklaması]
,(SELECT CODE FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL2 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje L2]
,(SELECT NAME FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL2 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje L2 Açıklaması]
,(SELECT CODE FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL3 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje L3]
,(SELECT NAME FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL3 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje L3 Açıklaması]
,(SELECT CODE FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL4 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje L4]
,(SELECT NAME FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL4 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje L4 Açıklaması]
,(SELECT CODE FROM LG_210_PROJECT WHERE LOGICALREF = PROJECTREF) AS [Aktivite Kodu]
,(SELECT NAME FROM LG_210_PROJECT WHERE LOGICALREF = PROJECTREF) AS [Aktivite Adı]
,(SELECT SPECODE FROM LG_210_ITEMS WHERE LOGICALREF = STOCKREF) AS [Malzeme Özel Kodu]
,(SELECT TOP 1 DEFINITION_ FROM LG_210_SPECODES WHERE CODETYPE = 1 AND SPECODETYPE = 1 AND SPECODE = (SELECT SPECODE FROM LG_210_ITEMS WHERE LOGICALREF = STOCKREF)) AS [Malzeme Özel Kodu Açıklaması]
,(SELECT CODE FROM LG_210_ITEMS WHERE LOGICALREF = STOCKREF) AS [Malzeme Kodu]
,(SELECT NAME FROM LG_210_ITEMS WHERE LOGICALREF = STOCKREF) AS [Malzeme Adı]
,(SELECT CODE FROM LG_210_UNITSETF WHERE LOGICALREF = (SELECT UNITSETREF FROM LG_210_ITEMS WHERE LOGICALREF = STOCKREF)) AS [Birim]
,ROUND((SUM(ONHAND)), 4) AS [Sarf Miktar]
,ROUND((SUM(VATMATRAH)), 4) [Sarf Matrah]
,ISNULL((SELECT TOP 1 VAT FROM LG_210_01_STLINE WHERE TRCODE = 1 AND CANCELLED = 0 AND LG_210_01_STLINE.STOCKREF = BV_210_01_STINVTOT.STOCKREF ORDER BY DATE_ DESC), -1) AS KDV
FROM
BV_210_01_STINVTOT
WHERE INVENNO > -1
AND DATE_ <= @DATE
AND PROJECTREF > 0
AND (SELECT  S2.STFICHEREF FROM LG_201_01_STLINE S2 WHERE S2.TRCODE = 6 AND S2.STOCKREF = BV_201_01_STINVTOT.STOCKREF AND S2.DATE_ <= @DATE),

AND STFICHEREF IN (SELECT F.LOGICALREF FROM  LG_210_01_STFICHE F WHERE F.SPECODE'IFS')
GROUP BY INVENNO, STOCKREF, PROJECTREF , DATE_
HAVING SUM(ONHAND) > 0

RETURN
END

GO

sql fetch

On By semt20In LOGO ERP, LOGO OBJECTSLeave a comment 

--FATURALAR IPTALLERIN YERINE
DECLARE @TSQL varchar(8000), @HASTAKODU VARCHAR(10), @FATURATARIH VARCHAR(10)
DECLARE TENAY2ZV_VIEW_IPTAL_FATURA_LOG CURSOR FOR
SELECT DISTINCT FATURATARIH,HASTAKODU FROM ZV_VIEW_IPTAL_FATURA_LOG
OPEN TENAY2ZV_VIEW_IPTAL_FATURA_LOG
FETCH NEXT FROM TENAY2ZV_VIEW_IPTAL_FATURA_LOG INTO @FATURATARIH,@HASTAKODU

WHILE @@FETCH_STATUS = 0
BEGIN
--SELECT  @HASTAKODU = '31588'
--SELECT  @FATURATARIH = '2018-12-10'
--TABLOYU YARAT ONCE : SONRA ALTTAKI SELECT  @TSQL = ' SELECT * into ZV_LOGO_FATURALANMISLAR_TEMP_IPTALYERINE FROM OPENQUERY(TENAY,''SELECT * FROM V_LOGO_SATISFATURASI WHERE HASTAKODU = ''''' + @HASTAKODU + ''''' AND TARIH='''''+@FATURATARIH+'''''  '')'
  SELECT  @TSQL = 'INSERT INTO ZV_LOGO_FATURALANMISLAR_TEMP_IPTALYERINE SELECT * ,''0''  FROM OPENQUERY(TENAY,''SELECT * FROM V_LOGO_SATISFATURASI WHERE HASTAKODU = ''''' + @HASTAKODU + ''''' AND TARIH='''''+@FATURATARIH+'''''  '') AS T WHERE NOT EXISTS (Select ID From ZV_LOGO_FATURALANMISLAR_TEMP_IPTALYERINE WHERE ZV_LOGO_FATURALANMISLAR_TEMP_IPTALYERINE.ID = T.ID)'
 EXEC (@TSQL)
    FETCH NEXT FROM TENAY2ZV_VIEW_IPTAL_FATURA_LOG
    INTO @FATURATARIH, @HASTAKODU
END
CLOSE TENAY2ZV_VIEW_IPTAL_FATURA_LOG
DEALLOCATE TENAY2ZV_VIEW_IPTAL_FATURA_LOG
GO

Sql toplu hareket görenleri kasmadan silme

On By semt20In SqlLeave a comment 
declare @counter int
declare @numOfRecords int
declare @batchsize int




set @numOfRecords = 700924 --burdaki rakam databasei acip tables yazisini secip f7 basinca rowcountu en cok olan rakam

set @counter = 0
set @batchsize = 2500

set rowcount @batchsize
while @counter < (@numOfRecords/@batchsize) +1
begin
set @counter = @counter + 1
delete Erp_InventoryReceiptItem

update Erp_Invoice set InventoryReceiptId = null

update Erp_InventoryReceiptAttachment set  InventoryReceiptId = null

delete Erp_InventoryReceiptAttachment

delete Erp_InvoiceAttachment

delete Erp_WorkOrderProduction
UPDATE  Erp_InventoryReceiptItem set InventoryReceiptId=null
delete Erp_InventoryReceipt

update Erp_InventoryReceipt set InvoiceId=null
delete Erp_Invoice

delete Erp_BankAccountTotal

delete Erp_BankCredit
update Erp_CurrentAccountReceipt set BankReceiptId=null
delete Erp_BankReceiptItem

update Erp_ChequeReceipt set BankReceiptId=null

delete Erp_ChequeReceiptItem

delete Erp_ChequeReceiptAttachment

delete Erp_ChequeReceipt

delete Erp_BankReceiptAttachment

delete Erp_BankReceipt

delete Erp_CashTotalItem

delete Erp_CashTotal

delete Erp_Cheque

delete Erp_OrderReceiptItem

delete Erp_OrderReceiptAttachment

delete Erp_OrderReceipt

delete Erp_ContractItem

delete Erp_ContractAttachment

delete Erp_Contract

update Erp_InventoryReceipt set CurrentAccountReceiptId = null
delete Erp_CurrentAccountReceiptItem

delete Erp_CurrentAccountReceiptAttachment

delete Erp_CurrentAccountReceipt

delete Erp_CurrentAccountTotal

delete Erp_QuotationReceiptItem

delete Erp_QuotationReceiptAttachment

delete Erp_QuotationReceipt

delete Erp_DemandReceiptItem

delete Erp_DemandReceiptAttachment

delete Erp_DemandReceipt

delete Erp_WorkOrderItem

delete Erp_WorkOrderAttachment

delete Erp_WorkOrderExplanation

delete Erp_WorkOrder

delete Erp_GLReceiptItem
update Erp_InventoryReceipt set GLReceiptId=null

delete Erp_GLReceipt

Delete Erp_InventoryTotal

delete Erp_ReceiptPaymentItem

delete Meta_ForexRate

delete Erp_ServiceTotal

Delete Erp_GLAccountTotal

delete Erp_BankAccountTotal
update Erp_InventoryReceipt set PosReceiptId=null
delete erp_pos
delete Erp_InventoryReceipt
delete RPL_TaskTarget
delete Rpl_TaskItem
delete RPL_Task
delete Rpl_Xref
delete [LiveHareketler].[dbo].[Replication]
end
set rowcount 0

truncate table Log_Transaction

shrink işlemi:
USE LiveHareketler;
GO
ALTER DATABASE LiveHareketler
SET RECOVERY SIMPLE;
GO

--Datadakı log dosyasını shrınk yap yani database sağ tıkla task shrink files log->reorganize page 0 ve tamam ve sonrasındada yine databasee sağ tık task shrink database ok

ALTER DATABASE LiveHareketler
SET RECOVERY FULL;
GO

Live Hareket Goren istenmeyen malzemeleri toplu olarak sqlden silme

On By semt20In sentez, SqlLeave a comment 
Silinmesi isdenilen malzemelerin recidleri () lerin icine yazilir




--update Erp_InventoryPriceList set UnitSetItemId = null where InventoryId in
--()

--delete Erp_InventoryPriceList where InventoryId in
--()

--delete Erp_RecipeItem from Erp_RecipeItem RI 
--where RecipeId = (Select RecId from Erp_Recipe R where R.RecId = RI.RecipeId and R.InventoryId in
--()
--)

--delete Erp_Recipe  where InventoryId in
--()

--update Erp_Inventory Set RecipeUnitItemId = null where RecId in
--()

--update Erp_InventoryUnitItemSize set InventoryId = null where InventoryId in
--()

--delete Erp_InventoryUnitItemSize where InventoryId in
--()

--delete Erp_InventoryBarcode where InventoryId in
--()

--delete Erp_InventoryWarehouse where InventoryId in
--()

--delete Erp_Project where InventoryId in
--()

--delete Erp_FixedAssetDepreciation where InventoryId in
--()

--delete Erp_InventoryExplanation where InventoryId in
--()

--delete Erp_InventoryAlternative  where InventoryId in
--()

--delete Erp_FixedAssetExpense  where InventoryId in
--()

--delete Erp_InventoryAttachment where InventoryId in
--()

--delete Erp_InitialCostItem from Erp_InitialCostItem IC 
--where InitialCostId = (Select RecId from Erp_InitialCost I where I.RecId = IC.InitialCostId and I.InventoryId in
--()
--)

--delete Erp_InitialCost where InventoryId in
--()

--delete Erp_RecipeItem where InventoryId in
--()

--delete Erp_RouteItem from Erp_RouteItem RI where RouteId = (select RecId from Erp_Route R where RI.RouteId=R.RecId and R.InventoryId in
--()
--)

--delete Erp_Route where InventoryId in
--()

--delete Erp_Inventory where RecId in
--()