Tag: HAREKET

LOGO ERP SQL HAREKET GÖRMÜŞ MALZEMENİN BİRİMİNİ ÇEVİRME HAREKETLER DAHIL

On By semt20In LOGO ERP, SqlLeave a comment 
/*MALZEME */SELECT LOGICALREF FROM dbo.LG_211_ITEMS WITH(NOLOCK) WHERE  CODE='M-4299-04-CL-SP' /*MALZEMENIN LOGICALREFINI BULMAK ICIN=1367*/
/*BIRIM*/ SELECT LOGICALREF,UNITSETREF,CODE,NAME FROM LG_211_UNITSETL WITH(NOLOCK) WHERE MAINUNIT=1 AND NAME='ADET' /*HANGI MALZEME BIRIMI OLACAK LOGICALREF=55 UNITSETREF=6*/

/*GUNCELLEME ISLEMI*/
update LG_211_ITEMS set UNITSETREF = 6 /*BIRIMIN UNITSETREFI*/ where LOGICALREF = 1367 /*MALZEMENIN LOGICALREFI*/
update LG_211_ITMUNITA set UNITLINEREF = 55 /*BIRIMIN LOGICALREFI*/ where ITEMREF = 1367 /*MALZEMENIN LOGICALREFI*/

/*RISKLI:GERIYE DONUK HAREKETLERI GUNCELLEMEK ICIN (TALEP,SIPARIS VE IRSALIYE)*/
update LG_211_01_STLINE set UOMREF = 55 /*BIRIMIN LOGICALREFI*/ , USREF = 6 /*BIRIMIN UNITSETREFI*/ where Stockref = 1367 /*MALZEMENIN LOGICALREFI*/
update LG_211_01_ORFLINE set UOMREF = 55 /*BIRIMIN LOGICALREFI*/ , USREF = 6 /*BIRIMIN UNITSETREFI*/ where Stockref = 1367 /*MALZEMENIN LOGICALREFI*/
update LG_211_01_DEMANDLINE  set UOMREF = 55 /*BIRIMIN LOGICALREFI*/ , USREF = 6 /*BIRIMIN UNITSETREFI*/ where Stockref = 1367 /*MALZEMENIN LOGICALREFI*/

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
--()