Unity : 2009 Aylık BS formu sorgusu

SELECT C.CODE AS CARI, C.DEFINITION_ AS ACIKLAMA, C.TAXNR AS VERGI_NO, C.TAXOFFICE AS VERGI_DAIRESI, C.TELNRS1,
SUM(INV.NETTOTAL - INV.TOTALVAT) AS TUTAR, COUNT(INV.LOGICALREF) AS BELGE_SAYISI,MONTH(DATE_) AS AY
FROM dbo.LG_444_01_INVOICE INV INNER JOIN
dbo.LG_444_CLCARD C ON INV.CLIENTREF = C.LOGICALREF
WHERE (INV.TRCODE IN (6, 7, 8, 9,14)) AND (INV.CANCELLED = 0) AND INV.DECPRDIFF=0
GROUP BY C.CODE, C.DEFINITION_, C.TAXNR, C.TAXOFFICE, C.TELNRS1,MONTH(DATE_)
HAVING (SUM(INV.NETTOTAL - INV.TOTALVAT) >= 8000)

Unity : 2009 Aylık BA formu sorgusu

SELECT C.CODE CARI
, C.DEFINITION_ ACIKLAMA
,C.TAXNR AS VERGI_NO
,C.TAXOFFICE AS VERGI_DAIRESI
,C.TELNRS1
, SUM(INV.nettotal-INV.totalvat) AS TUTAR
,COUNT(INV.LOGICALREF) AS BELGE_SAYISI
,MONTH(DATE_) AS AY

FROM LG_444_01_INVOICE INV, LG_444_CLCARD C
WHERE INV.CLIENTREF =C.LOGICALREF
and ((INV.TRCODE IN (1,2,3,4)) OR
(INV.TRCODE = 14 AND INV.DECPRDIFF=1))

AND INV.CANCELLED=0
group BY C.CODE,C.DEFINITION_,C.TAXNR,C.TAXOFFICE,C.TELNRS1,MONTH(DATE_)
HAVING SUM(INV.nettotal-INV.totalvat)>=8000

Error message "Reserved 7713" in MS-Access

This message in ODBC error. Invalid SQL returned by ODBC

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