Malzeme Kartı özelliklerine otomatik kayıt açmak. (LG_XXX_CHARASGN)

Unity'de malzeme kartında bulunan "Malzeme Özellikleri" tabına otomatik olarak kayıt eklemek için aşağıdaki cursor'u kullanabilirsiniz.





Kullanımı:



EXEC [dbo].[SP_CHARASGN_INSERT] '508' ,'822096031A0330640000208'

Açıklaması:
İlk parametre firma numarası, ikinci parametre ise malzeme kodudur.


SQL Kodu:
CREATE PROCEDURE [dbo].[SP_CHARASGN_INSERT]

@FIRMNR VARCHAR(5),
@NEWCODE VARCHAR(30)
AS
EXEC(
'DECLARE
@MAXREF INT,
@MAXREF2 INT,
@MAXREF3 INT,
@LOGICALREF INT,
@LINENR INT,
@CHARVALREF INT,
@CHARCODEREF INT,
@CODE VARCHAR(50),
@CHARVAL VARCHAR(50),
@SPECODE VARCHAR(50),
@CYPHCODE VARCHAR(50)

DECLARE CARI CURSOR FOR

SELECT LOGICALREF,CODE FROM LG_' + @FIRMNR + '_ITEMS (NOLOCK) WHERE LOGICALREF NOT IN (
SELECT ITEMREF FROM LG_' + @FIRMNR + '_CHARASGN) AND CARDTYPE NOT IN (20,22) AND ACTIVE =0 AND CODE =''' + @NEWCODE + '''
ORDER BY CODE

OPEN CARI
FETCH NEXT FROM CARI
INTO @LOGICALREF,@CODE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @LINENR=1
WHILE @LINENR<>5
BEGIN
SELECT @MAXREF=ISNULL(MAX(LASTLREF),0) + 1 FROM LG_' + @FIRMNR + '_CHARASGNSEQ WHERE ID=1
UPDATE LG_' + @FIRMNR + '_CHARASGNSEQ SET LASTLREF=@MAXREF WHERE ID=1
SET @CHARCODEREF = @LINENR + 1
IF @LINENR=1 --BRAND
BEGIN
SELECT @CHARVAL=NAME FROM [10.0.0.26].[BURSA1].[DBO].LG_BRAND WHERE CODE=SUBSTRING(@CODE,4,3)
END
IF @LINENR=2 --TICKET
BEGIN
SELECT @CHARVAL=NAME FROM [10.0.0.26].[BURSA1].[DBO].LG_TICKETNO WHERE CODE=SUBSTRING(@CODE,7,3)
END
IF @LINENR=3 --METER
BEGIN
SELECT @CHARVAL=NAME FROM [10.0.0.26].[BURSA1].[DBO].LG_METRAJ WHERE CODE=SUBSTRING(@CODE,10,4)
END
IF @LINENR=4 --COLOR
BEGIN
SET @CHARVAL=SUBSTRING(@CODE,19,5)
END
SET @CHARVALREF = 0

SELECT @CHARVALREF=LOGICALREF FROM LG_' + @FIRMNR + '_CHARVAL WHERE CHARCODEREF=@CHARCODEREF AND CODE =@CHARVAL
IF @CHARVALREF = 0
BEGIN
SELECT @MAXREF2=ISNULL(MAX(LASTLREF),0) + 1 FROM LG_' + @FIRMNR + '_CHARVALSEQ WHERE ID=1
UPDATE LG_' + @FIRMNR + '_CHARVALSEQ SET LASTLREF=@MAXREF2 WHERE ID=1

SELECT @MAXREF3=ISNULL(MAX(VALNO),0) + 1 FROM LG_' + @FIRMNR + '_CHARVAL WHERE CHARCODEREF=@CHARCODEREF
INSERT INTO LG_' + @FIRMNR + '_CHARVAL (LOGICALREF,CHARCODEREF,VALNO,CODE,NAME)
VALUES (@MAXREF2,@CHARCODEREF,@MAXREF3,@CHARVAL,@CHARVAL)
SELECT @CHARVALREF=LOGICALREF FROM LG_' + @FIRMNR + '_CHARVAL WHERE CHARCODEREF=@CHARCODEREF AND CODE =@CHARVAL
END

INSERT INTO LG_' + @FIRMNR + '_CHARASGN (LOGICALREF,ITEMREF,CHARCODEREF,CHARVALREF,LINENR,MATRIXLOC,PRIORITY)
VALUES (@MAXREF,@LOGICALREF,@CHARCODEREF,@CHARVALREF,@LINENR,0,0)
SET @LINENR = @LINENR + 1
END
FETCH NEXT FROM CARI
INTO @LOGICALREF,@CODE
END
CLOSE CARI
DEALLOCATE CARI')