← Detay Sayfası
Nesne Düzenle
|
sp_UpdateItemFromTemplate
İlgili Uygulama
-- Seçiniz --
SQL Server
Nesne Türü
View
Stored Procedure
Trigger
Job
Nesne Adı
Bu nesne dönem devrinden etkileniyor mu? (Örn: İçinde _01_ gibi sabit dönem kodu var mı?)
Genel Kullanım Amacı ve Notlar
Tam SQL Kodu
CREATE PROCEDURE dbo.sp_UpdateItemFromTemplate @pLogicalRef INT, -- Güncellenecek kaydin LOGICALREF'i @pUpdateItmunita BIT = 0, -- 1 ise, ITMUNITA ekleme/güncelleme blogunu da çalistirir @pBarcode NVARCHAR(50) = NULL -- ITMUNITA için barkod (opsiyonel) AS BEGIN SET NOCOUNT ON; BEGIN TRY BEGIN TRAN; /* Hedef kayit var mi? */ IF NOT EXISTS (SELECT 1 FROM LG_011_ITEMS WITH (HOLDLOCK, UPDLOCK) WHERE LOGICALREF = @pLogicalRef) BEGIN RAISERROR (N'Güncellenecek LOGICALREF bulunamadi.', 16, 1); ROLLBACK TRAN; RETURN; END /* Sablon var mi? */ IF NOT EXISTS (SELECT 1 FROM LG_011_ITEMS WHERE LOGICALREF = 30802) BEGIN RAISERROR (N'LOGICALREF=30802 sablon kaydi bulunamadi.', 16, 1); ROLLBACK TRAN; RETURN; END DECLARE @Now DATETIME = GETDATE(); DECLARE @h SMALLINT = DATEPART(HOUR, @Now); DECLARE @m SMALLINT = DATEPART(MINUTE, @Now); DECLARE @s SMALLINT = DATEPART(SECOND, @Now); /* ————— LG_011_ITEMS UPDATE ————— Sablondan kopyalanacak alanlar: T.* (temel alanlar) Istisnalar: bos/NULL/otomatik alanlar asagida tek tek set ediliyor. Dokunulmayacak alanlar (senin istegin): CODE, NAME, ACTIVE, CARDTYPE, VAT, RECSTATUS, RETURNVAT, SELLVAT, RETURNPRVAT, SELLPRVAT, UNITSETREF, MTRLBRWS, SALESBRWS, PURCHBRWS */ UPDATE U SET /* — SABLONDAN KOPYALANANLAR — (örnek ana gruplar) */ CLASSTYPE = T.CLASSTYPE, PAYMENTREF = T.PAYMENTREF, TRACKTYPE = T.TRACKTYPE, LOCTRACKING = T.LOCTRACKING, TOOL = T.TOOL, AUTOINCSL = T.AUTOINCSL, DIVLOTSIZE = T.DIVLOTSIZE, SHELFLIFE = T.SHELFLIFE, SHELFDATE = T.SHELFDATE, DOMINANTREFS1 = T.DOMINANTREFS1, DOMINANTREFS2 = T.DOMINANTREFS2, DOMINANTREFS3 = T.DOMINANTREFS3, DOMINANTREFS4 = T.DOMINANTREFS4, DOMINANTREFS5 = T.DOMINANTREFS5, DOMINANTREFS6 = T.DOMINANTREFS6, DOMINANTREFS7 = T.DOMINANTREFS7, DOMINANTREFS8 = T.DOMINANTREFS8, DOMINANTREFS9 = T.DOMINANTREFS9, DOMINANTREFS10 = T.DOMINANTREFS10, DOMINANTREFS11 = T.DOMINANTREFS11, DOMINANTREFS12 = T.DOMINANTREFS12, IMAGEINC = T.IMAGEINC, TEXTINC = T.TEXTINC, DEPRTYPE = T.DEPRTYPE, DEPRRATE = T.DEPRRATE, DEPRDUR = T.DEPRDUR, SALVAGEVAL = T.SALVAGEVAL, REVALFLAG = T.REVALFLAG, REVDEPRFLAG = T.REVDEPRFLAG, PARTDEP = T.PARTDEP, DEPRTYPE2 = T.DEPRTYPE2, DEPRRATE2 = T.DEPRRATE2, DEPRDUR2 = T.DEPRDUR2, REVALFLAG2 = T.REVALFLAG2, REVDEPRFLAG2 = T.REVDEPRFLAG2, PARTDEP2 = T.PARTDEP2, /* — SABLON grubundan kalanlar — */ QCCSETREF = T.QCCSETREF, DISTAMOUNT = T.DISTAMOUNT, SITEID = T.SITEID, UNIVID = T.UNIVID, DISTLOTUNITS = T.DISTLOTUNITS, COMBLOTUNITS = T.COMBLOTUNITS, WFSTATUS = T.WFSTATUS, DISTPOINT = T.DISTPOINT, CAMPPOINT = T.CAMPPOINT, CANUSEINTRNS = T.CANUSEINTRNS, ISONR = T.ISONR, GROUPNR = T.GROUPNR, PRODCOUNTRY = T.PRODCOUNTRY, ADDTAXREF = T.ADDTAXREF, QPRODAMNT = T.QPRODAMNT, QPRODUOM = T.QPRODUOM, QPRODSRCINDEX = T.QPRODSRCINDEX, EXTACCESSFLAGS = T.EXTACCESSFLAGS, PACKET = T.PACKET, SALVAGEVAL2 = T.SALVAGEVAL2, LOGOID = T.LOGOID, LIDCONFIRMED = T.LIDCONFIRMED, B2CCODE = T.B2CCODE, MARKREF = T.MARKREF, IMAGE2INC = T.IMAGE2INC, AVRWHDURATION = T.AVRWHDURATION, EXTCARDFLAGS = T.EXTCARDFLAGS, MINORDAMOUNT = T.MINORDAMOUNT, FREIGHTPLACE = T.FREIGHTPLACE, FREIGHTTYPCODE1 = T.FREIGHTTYPCODE1, FREIGHTTYPCODE2 = T.FREIGHTTYPCODE2, FREIGHTTYPCODE3 = T.FREIGHTTYPCODE3, FREIGHTTYPCODE4 = T.FREIGHTTYPCODE4, FREIGHTTYPCODE5 = T.FREIGHTTYPCODE5, FREIGHTTYPCODE6 = T.FREIGHTTYPCODE6, FREIGHTTYPCODE7 = T.FREIGHTTYPCODE7, FREIGHTTYPCODE8 = T.FREIGHTTYPCODE8, FREIGHTTYPCODE9 = T.FREIGHTTYPCODE9, FREIGHTTYPCODE10 = T.FREIGHTTYPCODE10, STATECODE = T.STATECODE, STATENAME = T.STATENAME, EXPCATEGORY = T.EXPCATEGORY, LOSTFACTOR = T.LOSTFACTOR, TEXTINCENG = T.TEXTINCENG, EANBARCODE = T.EANBARCODE, DEPRCLASSTYPE = T.DEPRCLASSTYPE, WFLOWCRDREF = T.WFLOWCRDREF, LOWLEVELCODES2 = T.LOWLEVELCODES2, LOWLEVELCODES3 = T.LOWLEVELCODES3, LOWLEVELCODES4 = T.LOWLEVELCODES4, LOWLEVELCODES5 = T.LOWLEVELCODES5, LOWLEVELCODES6 = T.LOWLEVELCODES6, LOWLEVELCODES7 = T.LOWLEVELCODES7, LOWLEVELCODES8 = T.LOWLEVELCODES8, LOWLEVELCODES9 = T.LOWLEVELCODES9, LOWLEVELCODES10 = T.LOWLEVELCODES10, ORGLOGOID = T.ORGLOGOID, QPRODDEPART = T.QPRODDEPART, CANCONFIGURE = T.CANCONFIGURE, CHARSETREF = T.CHARSETREF, CANDEDUCT = T.CANDEDUCT, CONSCODEREF = T.CONSCODEREF, EXPENSE = T.EXPENSE, ORIGIN = T.ORIGIN, NAME2 = T.NAME2, COMPKDVUSE = T.COMPKDVUSE, USEDINPERIODS = T.USEDINPERIODS, EXIMTAX1 = T.EXIMTAX1, EXIMTAX2 = T.EXIMTAX2, EXIMTAX3 = T.EXIMTAX3, EXIMTAX4 = T.EXIMTAX4, EXIMTAX5 = T.EXIMTAX5, PRODUCTLEVEL = T.PRODUCTLEVEL, APPSPEVATMATRAH = T.APPSPEVATMATRAH, NAME3 = T.NAME3, FACOSTKEYS = T.FACOSTKEYS, KKLINESDISABLE = T.KKLINESDISABLE, APPROVE = T.APPROVE, GLOBALID = T.GLOBALID, SALEDEDUCTPART1 = T.SALEDEDUCTPART1, SALEDEDUCTPART2 = T.SALEDEDUCTPART2, PURCDEDUCTPART1 = T.PURCDEDUCTPART1, PURCDEDUCTPART2 = T.PURCDEDUCTPART2, CATEGORYID = T.CATEGORYID, CATEGORYNAME = T.CATEGORYNAME, KEYWORD1 = T.KEYWORD1, KEYWORD2 = T.KEYWORD2, KEYWORD3 = T.KEYWORD3, KEYWORD4 = T.KEYWORD4, KEYWORD5 = T.KEYWORD5, DEMANDMEETSORTFLD4 = T.DEMANDMEETSORTFLD4, DEMANDMEETSORTFLD5 = T.DEMANDMEETSORTFLD5, DEMANDMEETSORTFLD3 = T.DEMANDMEETSORTFLD3, DEMANDMEETSORTFLD1 = T.DEMANDMEETSORTFLD1, DEMANDMEETSORTFLD2 = T.DEMANDMEETSORTFLD2, PROJECTREF = T.PROJECTREF, DEDUCTCODE = T.DEDUCTCODE, QPRODSUBSRCINDEX = T.QPRODSUBSRCINDEX, QPRODSUBDEPART = T.QPRODSUBDEPART, QPRODSUBUOM = T.QPRODSUBUOM, NAME4 = T.NAME4, QPRODSUBAMNT = T.QPRODSUBAMNT, CPACODE = T.CPACODE, PUBLICCOUNTRYREF = T.PUBLICCOUNTRYREF, MULTIADDTAX = T.MULTIADDTAX, PORDAMNTTOLERANCE = T.PORDAMNTTOLERANCE, SORDAMNTTOLERANCE = T.SORDAMNTTOLERANCE, UETDSUNCODE = T.UETDSUNCODE, UETDSTRANSPORTMODE= T.UETDSTRANSPORTMODE, UETDSLOADUNIT = T.UETDSLOADUNIT, UETDSUNDEF = T.UETDSUNDEF, UETDSLOADTYPEDEF = T.UETDSLOADTYPEDEF, NOTIFYCRDREF = T.NOTIFYCRDREF, LEVELCONTROL = T.LEVELCONTROL, UETDSLOADTYPE = T.UETDSLOADTYPE, UETDSTRANSPORTTYPE= T.UETDSTRANSPORTTYPE, TSENR = T.TSENR, PAYERACTIVE = T.PAYERACTIVE, PAYERID = T.PAYERID, EXIMREGTYPREF = T.EXIMREGTYPREF, PURCHDEDUCTCODE = T.PURCHDEDUCTCODE, PAYERSALESPRICE = T.PAYERSALESPRICE, PAYERSUBTITLE = T.PAYERSUBTITLE, PAYERNAME = T.PAYERNAME, PAYERPURCHPRICE = T.PAYERPURCHPRICE, PAYERBARCODE = T.PAYERBARCODE, MOLDMAINTNUMBER = T.MOLDMAINTNUMBER, MOLDFACTOR = T.MOLDFACTOR, MOLDMAINTLIFE = T.MOLDMAINTLIFE, MOLDMAINTLIFETYPE = T.MOLDMAINTLIFETYPE, MOLDUSAGELIFE = T.MOLDUSAGELIFE, FAUSEFULLIFECODE2 = T.FAUSEFULLIFECODE2, FAUSEFULLIFECODE = T.FAUSEFULLIFECODE, MOLDLIFETRACKTYPE = T.MOLDLIFETRACKTYPE, MOLD = T.MOLD, MOLDLIFEASRATIO = T.MOLDLIFEASRATIO, FORECASTCODE = T.FORECASTCODE, GAINTYPE = T.GAINTYPE, NODISCOUNT = T.NODISCOUNT, SALESLIMITQUANTITY= T.SALESLIMITQUANTITY, OBTAINTYPE = T.OBTAINTYPE, MOLDMAINTTYPE = T.MOLDMAINTTYPE, MOLDMAINTPERUNIT = T.MOLDMAINTPERUNIT, MOLDMAINTPERIOD = T.MOLDMAINTPERIOD, /* — ISTISNALAR — */ STGRPCODE = '', -- bos (varchar) PRODUCERCODE = '', SPECODE = '', CYPHCODE = '', GTIPCODE = '', EXPCTGNO = '', SPECODE2 = '', SPECODE3 = '', SPECODE4 = '', SPECODE5 = '', GUID = '', CAPIBLOCK_MODIFIEDBY = NULL, -- bos sayisal CAPIBLOCK_MODIFIEDDATE = NULL, -- bos tarih CAPIBLOCK_MODIFIEDHOUR = NULL, CAPIBLOCK_MODIFIEDMIN = NULL, CAPIBLOCK_MODIFIEDSEC = NULL, ORGLOGICREF = 0, LOWLEVELCODES1 = 0, APPROVEDATE = NULL, -- NULL olmasi gereken MOLDMAINTBEGDATE = NULL, -- NULL olmasi gereken CAPIBLOCK_CREATEDBY = 1, -- otomatik degerler CAPIBLOCK_CREADEDDATE = @Now, CAPIBLOCK_CREATEDHOUR = @h, CAPIBLOCK_CREATEDMIN = @m, CAPIBLOCK_CREATEDSEC = @s FROM LG_011_ITEMS U JOIN LG_011_ITEMS T ON T.LOGICALREF = 30802 WHERE U.LOGICALREF = @pLogicalRef; /* ——— Opsiyonel: ITMUNITA ekleme/güncelleme ——— Eger @pUpdateItmunita = 1 ve @pBarcode doluysa: - Kayitta yoksa ekler - Varsa birinci satirin barkodunu günceller */ IF @pUpdateItmunita = 1 AND ISNULL(@pBarcode,'') <> '' BEGIN IF NOT EXISTS (SELECT 1 FROM LG_011_ITMUNITA WHERE ITEMREF = @pLogicalRef AND LINENR = 1) BEGIN INSERT INTO LG_011_ITMUNITA (ITEMREF, LINENR, UNITLINEREF, BARCODE, MTRLCLAS, PURCHCLAS, SALESCLAS, RECSTATUS) VALUES (@pLogicalRef, 1, 23, @pBarcode, 1, 1, 1, 2); END ELSE BEGIN UPDATE LG_011_ITMUNITA SET BARCODE = @pBarcode, UNITLINEREF = 23, MTRLCLAS = 1, PURCHCLAS = 1, SALESCLAS = 1, RECSTATUS = 2 WHERE ITEMREF = @pLogicalRef AND LINENR = 1; END END COMMIT TRAN; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRAN; DECLARE @ErrMsg NVARCHAR(4000) = ERROR_MESSAGE(); RAISERROR (@ErrMsg, 16, 1); END CATCH END
Oluşturulma: 23.12.2025 07:15
Son Güncelleme: 23.12.2025 17:28
İptal
Nesneyi Sil
Değişiklikleri Kaydet