SELECT *
FROM
(
SELECT DISTINCT
*,
CASE
WHEN ACCODE_EMFLINE = ACCODE_STLINE + ',' + ACCODE_STLINE1 THEN
ACCODE_STLINE + ',' + ACCODE_STLINE1
WHEN ACCODE_EMFLINE = ACCODE_STLINE1 + ',' + ACCODE_STLINE THEN
ACCODE_STLINE1 + ',' + ACCODE_STLINE
WHEN ACCODE_STLINE1 IS NULL THEN
ACCODE_STLINE
ELSE
''
END SIRALA
FROM
(
SELECT
(
SELECT INVOICE.FICHENO
FROM LG_201_01_INVOICE INVOICE
WHERE INVOICE.LOGICALREF = stline.INVOICEREF
) FISNO,
stline.STOCKREF,
stline.LINETYPE,
(
SELECT emuhacc.CODE
FROM LG_201_EMUHACC emuhacc
WHERE emuhacc.LOGICALREF = stline.ACCOUNTREF
) ACCODE_STLINE,
(
SELECT emuhacc.CODE
FROM LG_201_EMUHACC emuhacc
WHERE emuhacc.LOGICALREF = stline.VATACCREF
) ACCODE_STLINE1,
CASE
WHEN stline.LINETYPE = 4 THEN
STUFF(
(
SELECT DISTINCT
',' + ACCOUNTCODE
FROM LG_201_01_EMFLINE S2
WHERE S2.SOURCEFREF = stline.INVOICEREF
AND S2.LINEEXP LIKE '%' +
(
SELECT ITEMS.DEFINITION_
FROM LG_201_SRVCARD ITEMS
WHERE ITEMS.LOGICALREF = stline.STOCKREF
) + '%'
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),
1,
1,
''
)
ELSE
STUFF(
(
SELECT DISTINCT
',' + S2.ACCOUNTCODE
FROM LG_201_01_EMFLINE S2
WHERE S2.SOURCEFREF = stline.INVOICEREF
AND S2.LINEEXP LIKE '%' +
(
SELECT ITEMS.NAME
FROM LG_201_ITEMS ITEMS
WHERE ITEMS.LOGICALREF = stline.STOCKREF
) + '%'
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),
1,
1,
''
)
END ACCODE_EMFLINE
FROM LG_201_01_STLINE stline
WHERE BILLED = 1
) AS T
) AS C
WHERE C.SIRALA = ''
AND ACCODE_EMFLINE IS NOT NULL
AND FISNO IS NOT NULL;
Tag: muhasebe
LOGO TANIMLI ALANLAR TABLOSU
MODULENR LINEINFO
2 MALZEME FİŞLERİ
3 MUHASEBE HESABI
4 FATURA
5 CARİ HESAP
6 MALZEME
7 MUHASEBE FİŞİ
8 SİPARİŞ
9 İRSALİYELER
10 SABİT KIYMET KAYDI
12 ÇEK SENET BORDROLARI
40 SERİ LOT TANIMLARI
58 ÜRÜN REÇETESİ
59 TALEP FİŞLERİ
71 KASA İŞLEMLERİ
72 ÇEKLER / SENETLER
73 MALZEME SINIF FİYATLARI
260 EMİR
261 TEKLİF
262 SÖZLEŞME
263 CARİ HESAP FİŞLERİ
264 BANKALAR
265 BANKA HESAPLARI
266 BANKA FİŞLERİ
2 MALZEME FİŞLERİ
3 MUHASEBE HESABI
4 FATURA
5 CARİ HESAP
6 MALZEME
7 MUHASEBE FİŞİ
8 SİPARİŞ
9 İRSALİYELER
10 SABİT KIYMET KAYDI
12 ÇEK SENET BORDROLARI
40 SERİ LOT TANIMLARI
58 ÜRÜN REÇETESİ
59 TALEP FİŞLERİ
71 KASA İŞLEMLERİ
72 ÇEKLER / SENETLER
73 MALZEME SINIF FİYATLARI
260 EMİR
261 TEKLİF
262 SÖZLEŞME
263 CARİ HESAP FİŞLERİ
264 BANKALAR
265 BANKA HESAPLARI
266 BANKA FİŞLERİ
SYS’de girilen tanımlı ve zorunlu alanlar ile ilgili bilgi ve örnekler aşağıdadır;
L_DEFNFLDSD :SYS’de tanımlanan alanların tutulduğu tablodur.
MODULENR : Hangi modüle ait tanımlı alan olduğunu gösterir. (Fatura 4 , Malzeme Kartı 6)
LEVEL_ : Ek alanın türünü gösterir (0 – Başlık ek alanı, 1 – Satır ek alanı)
NR : Ek alan numarası
TYP : Veri tipi (1 – Metin, 2 – Sayısal, 3 – Liste, 4 – Tarih)
L_MANDFLDS : SYS’de tanımlanan alanların Zorunlu olup olmadığını gösteren tablo. (MANDATORY=1 ise zorunlu alan)
LG_CATEGLISTS : Liste seçimli alanlarda liste içeriği bu tabloda tutuluyor.
LG_002_DEFNFLDSCARDV : Kartlardaki tanımlı alanların tutulduğu tablodur. (Malzeme Kartı, Cari Hesap Kartı, Muhasebe Hesap Kartı)
LG_002_01_DEFNFLDSTRANV : Fiş ve fiş satırlarındaki tanımlı alanların tutulduğu tablodur. (Fatura,İrsaliye,Sipariş,Malzeme Fişleri,Muhasebe Fişleri)
Kart ve fişlerde girilen bilgiler DEFNFLDSTRANV,DEFNFLDSCARDV tablolarındaki TEXTFLD1,TEXTFLD2,TEXTFLD3,…. tablolarında tutulmaktadır.
Diğer TEXTFLD ve NUMFLD ile başlayan alanlar girdiğimiz verileri tutuyor.
———————————————————————————————————————————–
FATURA TANIMLI ALAN;
Fatura tanımlı alanları L_DEFNFLDSD tablosunda tutulmaktadır.
MODULENR :4 – Fatura
INVAICE Tablosundaki LOGICALREF alanı DEFNFLDSD Tablosundaki PARENTREF alanına eşittir.
ÖRNEK:
– _SQLINFO(“TEXTFLDS1″,”LG_001_01_DEFNFLDSTRANV”,”MODULENR=4 AND PARENTREF=’”+STR(R23.logicalRef)+”‘”)
————————————————————————————————————————————-
CARİ HESAP KARTI TANIMLI ALAN;
Cari Hesap Kartı tanımlı alanlar LG_001_DEFNFLDSCARDV tablosunda tutulmaktadır.
MODULENR :5
CLCARD Tablosundaki LOGICALREF alanı DEFNFLDSCARDV Tablosundaki PARENTREFalanına eşittir.
ÖRNEK:
_SQLINFO(“TEXTFLDS1″,”LG_001_DEFNFLDSCARDV”,”MODULENR=5 AND PARENTREF=’”+STR(R1.logicalRef)+”‘”)
———————————————————————————————————————————–
MALZEME KARTI TANIMLI ALAN;
Malzeme Kartı tanımlı alanlar LG_001_DEFNFLDSCARDV tablosunda tutulmaktadır.
MODULENR :6
ITEMS Tablosundaki LOGICALREF alanı DEFNFLDSCARDV Tablosundaki PARENTREFalanına eşittir.
ÖRNEK:
_SQLINFO(“TEXTFLDS1″,”LG_001_DEFNFLDSCARDV”,”MODULENR=6 AND PARENTREF=’”+STR(R1.logicalRef)+”‘”)
——————————————————————————————————————————-
MALZEME FİŞİ TANIMLI ALAN;
Muhasebe Fişi tanımlı alanlar LG_001_01_DEFNFLDSTRANV tablosunda tutulmaktadır.
MODULENR :2
STFICHE Tablosundaki LOGICALREF alanı DEFNFLDSTRANV Tablosundaki PARENTREFalanına eşittir.
ÖRNEK:
_SQLINFO(“TEXTFLDS1″,”LG_001_01_DEFNFLDSTRANV”,”MODULENR=2 AND PARENTREF=’”+STR(R2.logicalRef)+”‘”)
——————————————————————————————————————————-
MUHASEBE HESAP PLAN KARTI TANIMLI ALAN;
Muhasebe Hesap Kartı tanımlı alanlar LG_001_DEFNFLDSCARDV tablosunda tutulmaktadır.
MODULENR :3
EMUHACC Tablosundaki LOGICALREF alanı DEFNFLDSCARDV Tablosundaki PARENTREFalanına eşittir.
ÖRNEK:
_SQLINFO(“TEXTFLDS1″,”LG_001_DEFNFLDSCARDV”,”MODULENR=3 AND PARENTREF=’”+STR(R1.logicalRef)+”‘”)
—————————————————————————————————————————–
MUHASEBE FİŞİ TANIMLI ALAN;
Muhasebe Fişi tanımlı alanlar LG_001_01_DEFNFLDSTRANV tablosunda tutulmaktadır.
MODULENR :7
EMFICHE Tablosundaki LOGICALREF alanı DEFNFLDSTRANV Tablosundaki PARENTREFalanına eşittir.
ÖRNEK:
_SQLINFO(“TEXTFLDS1″,”LG_001_01_DEFNFLDSTRANV”,”MODULENR=7 AND PARENTREF=’”+STR(R1.logicalRef)+”‘”)
FİŞ SATIRLARI İÇİN
_SQLINFO(“TEXTFLDS1″,”LG_003_01_DEFNFLDSTRANV”,”MODULENR=7 AND OWNERREF=’”+STR(R2.logicalRef)+”‘”)
—————————————————————————————————————————-
İRSALİYE TANIMLI ALANLAR;
İrsaliye Fişlerindeki tanımlı alanlar LG_001_01_DEFNFLDSTRANV tablosunda tutulmaktadır.
MODULENR:9
STFICHE Tablosundaki LOGICALREF alanı DEFNFLDSTRANV Tablosundaki PARENTREFalanına eşittir.
ÖRNEK:
_SQLINFO(“TEXTFLDS1″,”LG_001_01_DEFNFLDSTRANV”,”MODULENR=9 AND PARENTREF=’”+STR(R2.logicalRef)+”‘”)
————————————————————————————————————————–
FATURA İÇERİSİNDE İRSALİYE TANIMLI ALANLAR;
İrsaliye Fişlerindeki tanımlı alanlar LG_001_01_DEFNFLDSTRANV tablosunda tutulmaktadır.
MODULENR:9
STFICHE Tablosundaki LOGICALREF alanı DEFNFLDSTRANV Tablosundaki PARENTREFalanına eşittir.
ÖRNEK:
_SQLINFO(“D.TEXTFLDS1”,”LG_001_01_DEFNFLDSTRANV D, LG_001_01_STFICHE S “,”D.MODULENR=9 AND D.PARENTREF=S.LOGICALREF AND S.INVOICEREF=’”+STR(R23.logicalRef)+”‘”)
————————————————————————————————————————-
SİPARİŞ TANIMLI ALANLAR;
Sipariş Fişlerindeki tanımlı alanlar LG_001_01_DEFNFLDSTRANV tablosunda tutulmaktadır.
MODULENR:8
ORFICHE Tablosundaki LOGICALREF alanı DEFNFLDSTRANV Tablosundaki PARENTREFalanına eşittir.
ÖRNEK:
_SQLINFO(“TEXTFLDS1″,”LG_001_01_DEFNFLDSTRANV”,”MODULENR=8 AND PARENTREF=’”+STR(R2.logicalRef)+”‘”)
LOGO MUHASEBE BAKİYE LİSTESİ
SET ROWCOUNT 0
SELECT GLACC.CODE,
GLACC.DEFINITION_,
SUM(GLSUM.DEBIT) DEBIT,SUM(GLSUM.CREDIT) CREDIT,SUM(GLSUM.DEBIT-GLSUM.CREDIT) BAKIYE
FROM
LV_101_01_EMUHTOT GLSUM WITH(NOLOCK) LEFT OUTER JOIN LG_101_EMUHACC GLACC WITH(NOLOCK) ON (GLSUM.ACCOUNTREF = GLACC.LOGICALREF)
WHERE
((GLSUM.MONTH_ -1) OR (GLSUM.YEAR_ = 2019)) AND ((GLSUM.MONTH_ -2) OR (GLSUM.YEAR_ = 2019)) AND (( (GLSUM.YEAR_ = 2019) AND( (GLSUM.MONTH_ >= 1) OR (GLSUM.MONTH_ = -1) ) ) OR ( (GLSUM.YEAR_ = 2019) AND(GLSUM.MONTH_ <= 12) )) AND ((GLSUM.TOTTYPE 8) OR (GLSUM.CURRTYP > 0) ) AND (GLSUM.TOTTYPE IN (1)) AND ((GLSUM.TOTTYPE 8) OR (GLSUM.CURRTYP = GLACC.CCURRENCY))
GROUP BY
GLACC.CODE,DEFINITION_,GLSUM.ACCOUNTREF,GLSUM.TOTTYPE
ORDER BY CODE
Sentez Live Dev – Sql Queryden Hareketlerin Silinmesi
Aşağıdaki sorgu liveda (peşin satışsız,ncrsız) kartlar dışında hareketleri siler:(malzeme fişleri ,irsaliyeler,faturalar,teklifler,sipariş,muhasebe fişi,banka çek fişleri).
Sildikten sonra shrink yapılmalı aşağıda nasıl yapıldığı anlatılmıştır.Yaklaşık 14 gblık datayı 700mba düşürdü.
Sildikten sonra shrink yapılmalı aşağıda nasıl yapıldığı anlatılmıştır.Yaklaşık 14 gblık datayı 700mba düşürdü.
delete Erp_InventoryReceiptItem
update Erp_Invoice set InventoryReceiptId = null
update Erp_InventoryReceiptAttachment set InventoryReceiptId = null
delete Erp_InventoryReceiptAttachment
delete Erp_InvoiceAttachment
delete Erp_WorkOrderProduction
delete Erp_InventoryReceipt
delete Erp_Invoice
delete Erp_BankAccountTotal
delete Erp_BankCredit
delete Erp_BankReceiptItem
update Erp_ChequeReceipt set BankReceiptId=null
delete Erp_ChequeReceiptItem
delete Erp_ChequeReceiptAttachment
delete Erp_ChequeReceipt
delete Erp_BankReceiptAttachment
delete Erp_BankReceipt
delete Erp_CashTotalItem
delete Erp_CashTotal
delete Erp_Cheque
delete Erp_OrderReceiptItem
delete Erp_OrderReceiptAttachment
delete Erp_OrderReceipt
delete Erp_ContractItem
delete Erp_ContractAttachment
delete Erp_Contract
delete Erp_CurrentAccountReceiptItem
delete Erp_CurrentAccountReceiptAttachment
delete Erp_CurrentAccountReceipt
delete Erp_CurrentAccountTotal
delete Erp_QuotationReceiptItem
delete Erp_QuotationReceiptAttachment
delete Erp_QuotationReceipt
delete Erp_DemandReceiptItem
delete Erp_DemandReceiptAttachment
delete Erp_DemandReceipt
delete Erp_WorkOrderItem
delete Erp_WorkOrderAttachment
delete Erp_WorkOrderExplanation
delete Erp_WorkOrder
delete Erp_GLReceiptItem
delete Erp_GLReceipt
Delete Erp_InventoryTotal
delete Erp_ReceiptPaymentItem
delete Meta_ForexRate
delete Erp_ServiceTotal
Delete Erp_GLAccountTotal
delete Erp_BankAccountTotal
truncate table Log_Transaction
shrink işlemi:
USE LiveHareketler;
GO
ALTER DATABASE LiveHareketler
SET RECOVERY SIMPLE;
GO
--Datadakı log dosyasını shrınk yap yani database sağ tıkla task shrink files log->reorganize page 0 ve tamam ve sonrasındada yine databasee sağ tık task shrink database ok
ALTER DATABASE LiveHareketler
SET RECOVERY FULL;
GO
Sentez Dev Mizan Excele
excelde
data -> from other source -> from microsoft query>new data source ilişklendirmeyi yap sorguyada aşağıdakileri yapıştır…
data -> from other source -> from microsoft query>new data source ilişklendirmeyi yap sorguyada aşağıdakileri yapıştır…
profilerda mizanı çekerken kod:
exec sp_executesql N' if object_id(''tempdb..#GLReceiptBalanceTmp'') is not null drop table #GLReceiptBalanceTmp create table #GLReceiptBalanceTmp ( CompanyId int,AccountId int, AccountCode nvarchar(25) collate database_default, AccountName nvarchar(50) collate database_default ,Debit decimal(28,8),Credit decimal(28,8),DebitBalance decimal(28,8),CreditBalance decimal(28,8) ,ForexDebit decimal(28,8),ForexCredit decimal(28,8),ForexDebitBalance decimal(28,8),ForexCreditBalance decimal(28,8) ,ForexCalculateDebit decimal(28,8),ForexCalculateCredit decimal(28,8),ForexCalculateDebitBalance decimal(28,8),ForexCalculateCreditBalance decimal(28,8) ,InflowQty decimal(28,8),OutflowQty decimal(28,8)) insert into #GLReceiptBalanceTmp (CompanyId,AccountId,AccountCode,AccountName,Debit,Credit,DebitBalance,CreditBalance,ForexDebit,ForexCredit,ForexDebitBalance,ForexCreditBalance ,ForexCalculateDebit,ForexCalculateCredit,ForexCalculateDebitBalance,ForexCalculateCreditBalance,InflowQty,OutflowQty) select A.CompanyId,A.RecId AccountId,A.AccountCode AccountCode,A.AccountName AccountName,isnull(sum(AR.Debit),0) Debit,0 Credit ,case when isnull(sum(AR.Debit),0)>isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Debit),0)-isnull(sum(AR.Credit),0)),2) else 0 end DebitBalance ,case when isnull(sum(AR.Debit),0)<isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Credit),0)-isnull(sum(AR.Debit),0)),2) else 0 end CreditBalance ,isnull(sum(AR.ForexAmount),0)ForexDebit,0 ForexCredit,isnull(sum(AR.ForexAmount),0)ForexDebitBalance,0 ForexCreditBalance ,0 ForexCalculateDebit,0 ForexCalculateCredit,0 ForexCalculateDebitBalance,0 ForexCalculateCreditBalance ,isnull(sum(AR.Quantity),0)InflowQty,0 OutflowQty from Erp_GLAccount A with (nolock) inner join Erp_GLReceiptItem AR with (nolock) on (A.RecId=AR.AccountId) left join Erp_GLReceipt ARB with (nolock) on (ARB.RecId=AR.GLReceiptId) left join Erp_CostCenter CC with (nolock) on (CC.RecId=AR.CostCenterId) left join Erp_Project P with (nolock) on (P.RecId=AR.ProjectId) where A.CompanyId = 5 and (A.IsDeleted is null or A.IsDeleted = 0) and (AR.IsDeleted is null or AR.IsDeleted = 0) and AR.Debit 0 and isnull(AR.ReceiptType,0) in (1 , 2 , 3 , 4 , 5) and AR.ReceiptDate between @0ARErp_GLReceiptItemReceiptDate1 and @0ARErp_GLReceiptItemReceiptDate2 group by A.CompanyId,A.RecId,A.AccountCode,A.AccountName
union all select A.CompanyId,A.RecId AccountId,A.AccountCode AccountCode,A.AccountName AccountName,0 Debit,isnull(sum(AR.Credit),0) Credit ,case when isnull(sum(AR.Debit),0)>isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Debit),0)-isnull(sum(AR.Credit),0)),2) else 0 end DebitBalance ,case when isnull(sum(AR.Debit),0)<isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Credit),0)-isnull(sum(AR.Debit),0)),2) else 0 end CreditBalance ,0 ForexDebit,isnull(sum(AR.ForexAmount),0) ForexCredit,0 ForexDebitBalance,isnull(sum(AR.ForexAmount),0) ForexCreditBalance ,0 ForexCalculateDebit,0 ForexCalculateCredit,0 ForexCalculateDebitBalance,0 ForexCalculateCreditBalance ,0 InflowQty,isnull(sum(AR.Quantity),0) OutflowQty from Erp_GLAccount A with (nolock) inner join Erp_GLReceiptItem AR with (nolock) on (A.RecId=AR.AccountId) left join Erp_GLReceipt ARB with (nolock) on (ARB.RecId=AR.GLReceiptId) left join Erp_CostCenter CC with (nolock) on (CC.RecId=AR.CostCenterId) left join Erp_Project P with (nolock) on (P.RecId=AR.ProjectId) where A.CompanyId = 5 and (A.IsDeleted is null or A.IsDeleted = 0) and (AR.IsDeleted is null or AR.IsDeleted = 0) and AR.Credit 0 and isnull(AR.ReceiptType,0) in (1 , 2 , 3 , 4 , 5) and AR.ReceiptDate between @0ARErp_GLReceiptItemReceiptDate1 and @0ARErp_GLReceiptItemReceiptDate2 group by A.CompanyId,A.RecId,A.AccountCode,A.AccountName if object_id(''tempdb..#GLAccountBalanceTmp'') is not null drop table #GLAccountBalanceTmp create table #GLAccountBalanceTmp ( CompanyId int,AccountId int, AccountCode nvarchar(25) collate database_default, AccountName nvarchar(50) collate database_default ,Debit decimal(28,8),Credit decimal(28,8),DebitBalance decimal(28,8),CreditBalance decimal(28,8) ,DBalance decimal(28,8),CBalance decimal(28,8),ForexDebit decimal(28,8),ForexCredit decimal(28,8) ,ForexDebitBalance decimal(28,8),ForexCreditBalance decimal(28,8),FDBalance decimal(28,8),FCBalance decimal(28,8) ,ForexCalculateDebit decimal(28,8),ForexCalculateCredit decimal(28,8) ,ForexCalculateDebitBalance decimal(28,8),ForexCalculateCreditBalance decimal(28,8),FCDBalance decimal(28,8),FCCBalance decimal(28,8) ,InflowQty decimal(28,8),OutflowQty decimal(28,8)) insert into #GLAccountBalanceTmp (CompanyId,AccountId,AccountCode,AccountName,Debit,Credit,DebitBalance,CreditBalance,DBalance,CBalance,ForexDebit,ForexCredit ,ForexDebitBalance,ForexCreditBalance,FDBalance,FCBalance,ForexCalculateDebit,ForexCalculateCredit,ForexCalculateDebitBalance,ForexCalculateCreditBalance ,FCDBalance,FCCBalance,InflowQty,OutflowQty) select CompanyId,AccountId,AccountCode,AccountName,isnull(sum(Debit),0)Debit,isnull(sum(Credit),0)Credit,isnull(sum(DebitBalance),0)DebitBalance,isnull(sum(CreditBalance),0)CreditBalance ,case when isnull(sum(DebitBalance),0)>isnull(sum(CreditBalance),0) then round(isnull(sum(DebitBalance),0)-isnull(sum(CreditBalance),0),2) else 0 end DBalance ,case when isnull(sum(DebitBalance),0)isnull(sum(ForexCreditBalance),0) then round(isnull(sum(ForexDebitBalance),0)-isnull(sum(ForexCreditBalance),0),2) else 0 end FDBalance ,case when isnull(sum(ForexDebitBalance),0)isnull(sum(ForexCalculateCreditBalance),0) then round(isnull(sum(ForexCalculateDebitBalance),0)-isnull(sum(ForexCalculateCreditBalance),0),2) else 0 end FCDBalance ,case when isnull(sum(ForexCalculateDebitBalance),0)<isnull(sum(ForexCalculateCreditBalance),0) then round(isnull(sum(ForexCalculateCreditBalance),0)-isnull(sum(ForexCalculateDebitBalance),0),2) else 0 end FCCBalance ,isnull(sum(InflowQty),0)InflowQty,isnull(sum(OutflowQty),0)OutflowQty from #GLReceiptBalanceTmp group by CompanyId,AccountId,AccountCode,AccountName having round(isnull(sum(Debit),0),2) round(isnull(sum(Credit),0),2) select A.CompanyId,'''' [Şirket],isnull((select MF.ForexCode from Meta_Forex MF where MF.RecId=A.ForexId),'''') [Dvz],'''' [HDvz],A.AccountCode [Hesap Kodu],A.AccountName [Hesap Adı],A.SpecialCode [Özel Kod],0.0 [Devir Borç],0.0 [Devir Alacak],sum(isnull(B.Debit,0)) [Borç],sum(isnull(B.Credit,0)) [Alacak],case when sum(isnull(B.DBalance,0)) > sum(isnull(B.CBalance,0)) then sum(isnull(B.DBalance,0)) - sum(isnull(B.CBalance,0)) when sum(isnull(B.DBalance,0)) sum(isnull(B.CBalance,0)) then ''BB'' when sum(isnull(B.DBalance,0)) sum(isnull(B.FCBalance,0)) then round(sum(isnull(B.FDBalance,0)) - sum(isnull(B.FCBalance,0)),2) when sum(isnull(B.FDBalance,0)) sum(isnull(B.FCBalance,0)) then ''BB'' when sum(isnull(B.FDBalance,0)) sum(isnull(B.FCCBalance,0)) then round(sum(isnull(B.FCDBalance,0)) - sum(isnull(B.FCCBalance,0)),2)when sum(isnull(B.FCDBalance,0)) sum(isnull(B.FCCBalance,0)) then ''BB'' when sum(isnull(B.FCDBalance,0)) < sum(isnull(B.FCCBalance,0)) then ''AB'' else '''' end [Hesaplanan Döviz Bakiye Tipi],sum(isnull(B.FCDBalance,0)) [Hesaplanan Döviz Borç Bakiye],sum(isnull(B.FCCBalance,0)) [Hesaplanan Döviz Alacak Bakiye],sum(isnull(B.InflowQty,0)) [Giriş Miktarı],sum(isnull(B.OutflowQty,0)) [Çıkış Miktarı],sum(isnull(B.InflowQty,0)) - sum(isnull(B.OutflowQty,0)) [Kalan],-1 CAccount from Erp_GLAccount A with (nolock) left join #GLAccountBalanceTmp B on (B.AccountId in(select AA.RecId from Erp_GLAccount AA where AA.CompanyId=A.CompanyId and AA.AccountCode like rtrim(A.AccountCode)+''%'' )) where A.CompanyId = 5 and (A.IsDeleted is null or A.IsDeleted = 0) group by A.CompanyId,A.ForexId,A.AccountCode,A.AccountName,A.SpecialCode having round(isnull(sum(B.Debit),0),2) round(isnull(sum(B.Credit),0),2) order by A.AccountCode
if object_id(''tempdb..#GLReceiptTmp'') is not null drop table #GLReceiptTmp if object_id(''tempdb..#GLReceiptBalanceTmp'') is not null drop table #GLReceiptBalanceTmp if object_id(''tempdb..#GLAccountBalanceTmp'') is not null drop table #GLAccountBalanceTmp ',N'@0ARErp_GLReceiptItemReceiptDate1 datetime,@0ARErp_GLReceiptItemReceiptDate2 datetime',@0ARErp_GLReceiptItemReceiptDate1='2017-01-01 00:00:00',@0ARErp_GLReceiptItemReceiptDate2='2018-12-31 23:59:00'
Blog of Sem 