📄 Tam SQL Kodu (Referans)
Analiz eklerken buradan kod parçalarını kopyalayabilirsiniz
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
➕ Yeni Kod Analizi Ekle
📋 Mevcut Analizler (0)
Henüz kod analizi eklenmemiş.
Yukarıdaki formu kullanarak ilk analizi ekleyin.