Kaynak: KB-v1
08_SupportPlaybooks/Processes/PROC-148.md←Surecler/*.doc(destek süreç dokümanı; parola/IP/terminal maskelenmiş). Gerçeklik: DOCUMENTED — kodla doğrulanmış değil.
ERP Bakiye Borç Raporu
Kimlik: PROC-148 · Ürün: SQL Server · Kategori: Veritabanı bakımı (SQL) · Katalog: ERP Bakiye Borç Raporu
Kaynak: AifaSoft destek süreç dokümanı (Word). Şifre, kullanıcı adı, terminal no ve IP bilgileri kaynak aşamasında maskelenmiştir; görseller resimli süreç kitabındadır.
BAKİYE BORÇ RAPORU
-------------------QUERY----------------
USE [ERPVISNEV2]
GO
/****** Object: UserDefinedFunction [Reports].[BakiyeBorcListesi] Script Date: 4.11.2021 10:47:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Function [Reports].[BakiyeBorcListesi](@SubeId int,@EndVade date,@HesapId int)
returns table
as
return
Select top 10000 @EndVade as Vade,HesapAdi,Br.Kodu,Telefon1 as Telefon,
--Case When Bakiye <0 Then Bakiye Else 0 End as Alacak,
Case When Bakiye >0 Then Bakiye Else 0 End as Borc
--Bakiye as NET
from
(
Select HesapId,BirimId,Sum(Bakiye) as Bakiye
from(
Select HesapId,BirimId,Bakiye
from(
Select BI.HesapId,BI.BirimId,NetBakiye as Bakiye--Isnull(I.Vade,BI.Tarih) as Vade
from Cari.BakiyeIslem BI
Left Join Cari.Islemler I on I.Id=BI.IslemId
where I.SubeId=@SubeId and (I.HesapId=@HesapId or @HesapId=0) and Isdeleted=0
--IsNull(I.Vade,InsertDate)<=@EndVade and
)B
)A
group by HesapId,BirimId
having Sum(Bakiye)\<>0
)D
Left Join Cari.Hesaplar H on H.Id=D.HesapId
Left Join Sistem.Birimler Br on Br.Id =D.BirimId
order by HesapAdi ,Br.SiraNo
GO
------------------LAYOUT------------------
INSERT INTO AifaDev.Reports(RefNo,CreatedDate,ReportName,[Description],Developer,[SQL],[Password],[IsGrid],[IsPivot],[IsReport],[GroupId]) Values('0425C007031C47CE8866C2C25009988E','2021-05-05','BakiyeBorcListesi','','Cihan T.','Select * from Reports.BakiyeBorcListesi(@SubeId ,@EndVade ,@HesapId) ','','True','False','False','0')
Declare @ReportId int =(SELECT SCOPE_IDENTITY())
Insert Into AifaDev.ReportsFilter(ReportId,SortIndex,FieldName,Caption,ControlType,SQL,DisplayMember,ValueMember,IsBeetwen) Values(@ReportId,'0','EndVade','Tarih','DateEdit','','','','False')
Insert Into AifaDev.ReportsFilter(ReportId,SortIndex,FieldName,Caption,ControlType,SQL,DisplayMember,ValueMember,IsBeetwen) Values(@ReportId,'0','HesapId','HesapAdi','LookUpEdit','select Id,HesapAdi from Cari.Hesaplar where IsDeleted=0','HesapAdi','Id','False')
Insert Into AifaDev.ReportLayouts(ReportId,LayoutType,LayoutName,Layout) Values(@ReportId,'0','Genel Görünüm','<XtraSerializer version="1.0" application="View">
\<property name="#LayoutVersion" />
\<property name="VertScrollTipFieldName" />
\<property name="PreviewFieldName" />
\<property name="GroupPanelText">Gruplandırmak Istediğiniz Sutunu Buraya Taşıyınız....\</property>
\<property name="NewItemRowText" />
\<property name="LevelIndent">-1\</property>
\<property name="PreviewIndent">-1\</property>
\<property name="PreviewLineCount">-1\</property>
\<property name="ScrollStyle">LiveVertScroll, LiveHorzScroll\</property>
\<property name="FocusRectStyle">CellFocus\</property>
\<property name="HorzScrollStep">3\</property>
\<property name="ActiveFilterEnabled">true\</property>
\<property name="ViewCaptionHeight">-1\</property>
\<property name="Columns" iskey="true" value="7">
\<property name="Item1" isnull="true" iskey="true">
\<property name="Width">65\</property>
\<property name="Visible">true\</property>
\<property name="ColumnEditName" />
\<property name="VisibleIndex">0\</property>
\<property name="Name">colVade\</property>
\<property name="FieldName">Vade\</property>
\</property>
\<property name="Item2" isnull="true" iskey="true">
\<property name="Width">153\</property>
\<property name="Visible">true\</property>
\<property name="ColumnEditName" />
\<property name="VisibleIndex">1\</property>
\<property name="Name">colHesapAdi\</property>
\<property name="FieldName">HesapAdi\</property>
\</property>
\<property name="Item3" isnull="true" iskey="true">
\<property name="Width">50\</property>
\<property name="Visible">true\</property>
\<property name="ColumnEditName" />
\<property name="VisibleIndex">2\</property>
\<property name="Name">colKodu\</property>
\<property name="FieldName">Kodu\</property>
\</property>
\<property name="Item4" isnull="true" iskey="true">
\<property name="Width">56\</property>
\<property name="Visible">true\</property>
\<property name="ColumnEditName" />
\<property name="VisibleIndex">3\</property>
\<property name="Name">colTelefon\</property>
\<property name="FieldName">Telefon\</property>
\</property>
\<property name="Item5" isnull="true" iskey="true">
\<property name="DisplayFormat" isnull="true" iskey="true">
\<property name="FormatString">n2\</property>
\<property name="FormatType">Numeric\</property>
\</property>
\<property name="Width">93\</property>
\<property name="ColumnEditName" />
\<property name="Name">colAlacak\</property>
\<property name="FieldName">Alacak\</property>
\</property>
\<property name="Item6" isnull="true" iskey="true">
\<property name="DisplayFormat" isnull="true" iskey="true">
\<property name="FormatString">n2\</property>
\<property name="FormatType">Numeric\</property>
\</property>
\<property name="Width">65\</property>
\<property name="Visible">true\</property>
\<property name="ColumnEditName" />
\<property name="VisibleIndex">4\</property>
\<property name="Name">colBorc\</property>
\<property name="FieldName">Borc\</property>
\</property>
\<property name="Item7" isnull="true" iskey="true">
\<property name="DisplayFormat" isnull="true" iskey="true">
\<property name="FormatString">n2\</property>
\<property name="FormatType">Numeric\</property>
\</property>
\<property name="Width">142\</property>
\<property name="ColumnEditName" />
\<property name="Name">colNET\</property>
\<property name="FieldName">NET\</property>
\<property name="Summary" iskey="true" value="1">
\<property name="Item1" isnull="true" iskey="true">
\<property name="FieldName">NET\</property>
\<property name="Tag" isnull="true" />
\<property name="DisplayFormat">SUM={0:n2}\</property>
\<property name="SummaryType">Sum\</property>
\</property>
\</property>
\</property>
\</property>
\<property name="ViewCaption"> \</property>
\<property name="BorderStyle">Simple\</property>
\<property name="SynchronizeClones">true\</property>
\<property name="DetailTabHeaderLocation">Top\</property>
\<property name="Name">GrdView\</property>
\<property name="DetailHeight">350\</property>
\<property name="ChildGridLevelName" />
\<property name="GroupFormat">{1}\</property>
\<property name="GroupRowHeight">-1\</property>
\<property name="OptionsBehavior" isnull="true" iskey="true">
\<property name="Editable">false\</property>
\<property name="AllowIncrementalSearch">true\</property>
\</property>
\<property name="OptionsSelection" isnull="true" iskey="true">
\<property name="EnableAppearanceFocusedCell">false\</property>
\</property>
\<property name="Appearance" isnull="true" iskey="true">
\<property name="ViewCaption" iskey="true" value="ViewCaption">
\<property name="Options" isnull="true" iskey="true">
\<property name="UseTextOptions">true\</property>
\</property>
\<property name="TextOptions" isnull="true" iskey="true">
\<property name="HAlignment">Near\</property>
\</property>
\</property>
\<property name="SelectedRow" iskey="true" value="SelectedRow">
\<property name="Options" isnull="true" iskey="true">
\<property name="UseBackColor">true\</property>
\</property>
\</property>
\</property>
\<property name="OptionsPrint" isnull="true" iskey="true">
\<property name="ExpandAllDetails">true\</property>
\</property>
\<property name="OptionsView" isnull="true" iskey="true">
\<property name="ShowFooter">true\</property>
\<property name="ShowGroupPanel">false\</property>
\<property name="EnableAppearanceOddRow">true\</property>
\<property name="EnableAppearanceEvenRow">true\</property>
\<property name="ShowIndicator">false\</property>
\<property name="ShowAutoFilterRow">true\</property>
\<property name="ColumnAutoWidth">false\</property>
\</property>
\<property name="RowSeparatorHeight">0\</property>
\<property name="RowHeight">-1\</property>
\<property name="VertScrollVisibility">Auto\</property>
\<property name="HorzScrollVisibility">Auto\</property>
\<property name="FixedLineWidth">2\</property>
\<property name="FooterPanelHeight">-1\</property>
\<property name="IndicatorWidth">-1\</property>
\<property name="ColumnPanelRowHeight">-1\</property>
\<property name="ActiveFilterString" />
\<property name="GroupSummary" iskey="true" value="0" />
\<property name="FormatConditions" iskey="true" value="4">
\<property name="Item1" isnull="true" iskey="true">
\<property name="ColumnName">colNET\</property>
\<property name="Appearance" isnull="true" iskey="true">
\<property name="Options" isnull="true" iskey="true">
\<property name="UseForeColor">true\</property>
\<property name="UseBackColor">true\</property>
\<property name="UseFont">true\</property>
\</property>
\<property name="ForeColor">Black\</property>
\<property name="BackColor">YellowGreen\</property>
\<property name="Font">Tahoma, 8.25pt, style=Bold\</property>
\</property>
\<property name="Condition">Expression\</property>
\<property name="Expression">[NET] < 0\</property>
\</property>
\<property name="Item2" isnull="true" iskey="true">
\<property name="ColumnName">colNET\</property>
\<property name="Appearance" isnull="true" iskey="true">
\<property name="Options" isnull="true" iskey="true">
\<property name="UseForeColor">true\</property>
\<property name="UseBackColor">true\</property>
\<property name="UseFont">true\</property>
\</property>
\<property name="ForeColor">White\</property>
\<property name="BackColor">OrangeRed\</property>
\<property name="Font">Tahoma, 8.25pt, style=Bold\</property>
\</property>
\<property name="Condition">Expression\</property>
\<property name="Expression">[NET] > 0\</property>
\</property>
\<property name="Item3" isnull="true" iskey="true">
\<property name="ColumnName" />
\<property name="Condition">Expression\</property>
\</property>
\<property name="Item4" isnull="true" iskey="true">
\<property name="ColumnName" />
\<property name="Condition">Expression\</property>
\</property>
\</property>
\<property name="GroupSummarySortInfoState" />
\<property name="FindFilterText" />
\<property name="FindPanelVisible">false\</property>
\</XtraSerializer>')
Kaynaklar
- SRC-PROC-DOC — süreç dokümanı #148