Tiger'da Cari Hesaba Muhasebe hesap kodunu otomatik olarak aktarmak

Tiger'da Cari Hesaba Muhasebe hesap kodunu otomatik olarak aktarmak için aşağıdaki SQL scriptini çalıştırabilirsiniz.



DECLARE @carilref INT
DECLARE @carimuhlref INT
DECLARE @muhlref INT
DECLARE @carikodu varchar(50)
DECLARE @definiti varchar(50)
DECLARE @muhasebekodu varchar(50)

DECLARE @carimuhlref2 INT
DECLARE @muhlref2 INT
DECLARE @lastCarimuhSeq INT

DECLARE @eksikkayitsayisi INT



DECLARE processes CURSOR FOR
SELECT CARI.LOGICALREF,CARI.CODE AS CARI_KODU,CARIMUH.LOGICALREF AS CARIMUH_LREF,MUH.CODE AS MUHASEBE_KODU FROM LG_107_CLCARD CARI WITH (NOLOCK)
LEFT OUTER JOIN LG_107_CRDACREF CARIMUH WITH (NOLOCK) ON CARIMUH.CARDREF=CARI.LOGICALREF AND CARIMUH.TRCODE = 5
LEFT OUTER JOIN LG_107_EMUHACC MUH WITH (NOLOCK) ON CARIMUH.ACCOUNTREF=MUH.LOGICALREF
WHERE (MUH.CODE<>CARI.CODE OR MUH.CODE IS NULL) and SUBSTRING(CARI.CODE,5,2) = '47' AND CARI.CARDTYPE<>22 and CARI.CODE NOT LIKE '25%' ORDER BY CARI.CODE

OPEN processes
FETCH NEXT FROM processes
INTO @carilref,@carikodu,@carimuhlref,@muhasebekodu
WHILE @@FETCH_STATUS = 0
BEGIN
--ilk olarak LG_107_CRDACREF tablosunda kayıt var mı kontrol edilecek. eğer varsa ve muhasebe kodu yok veya hatalıysa muhasebe tablosundan
--kayıt aktarılacak.
set @muhlref2 = 0

SELECT @muhlref2=LOGICALREF FROM LG_107_EMUHACC where CODE = @carikodu
if @muhlref2 = 0
begin
insert into #EksikKayitTableGintem (Firma,CariKodu) values (107,@carikodu)
end
else
begin
if @carimuhlref IS NOT NULL
begin

UPDATE [LG_107_CRDACREF]
SET
[TRCODE] = 5
,[ACCOUNTREF] = @muhlref2
WHERE [LOGICALREF] = @carimuhlref
print 'update' + cast(@carimuhlref as varchar(20))
end
ELSE
BEGIN
print 'insert' + cast(@carimuhlref as varchar(20))
SELECT @lastCarimuhSeq = LASTLREF from LG_107_CRDACREFSEQ where ID=1
UPDATE LG_107_CRDACREFSEQ SET LASTLREF = @lastCarimuhSeq + 1 WHERE ID=1
INSERT INTO [LG_107_CRDACREF]
([LOGICALREF],[TRCODE],[CARDREF],[TYP],[ACCOUNTREF],[CENTERREF],[SITEID],[RECSTATUS],[ORGLOGICREF],[PROJECTREF])
VALUES (@lastCarimuhSeq ,5 ,@carilref,1 ,@muhlref2 ,1 ,0,0,0 ,0)
END
end

--PRINT cast(@muhlref2 as varchar(20)) + '-' + @carikodu


FETCH NEXT FROM processes
INTO @carilref,@carikodu,@carimuhlref,@muhasebekodu
END
CLOSE processes
DEALLOCATE processes