Kaynak: KB-v1
08_SupportPlaybooks/Processes/PROC-96.md←Surecler/*.doc(destek süreç dokümanı; parola/IP/terminal maskelenmiş). Gerçeklik: DOCUMENTED — kodla doğrulanmış değil.
ERPGOLDV2 GENİŞ BİLANÇO RAPORU
Kimlik: PROC-96 · Ürün: ERPGOLDV2 · Kategori: Rapor ve bilanço · Katalog: ERP Geniş Bilanço 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.
Doküman TipiERPGOLDV2Doküman NoHazırlayanCihan Burak TURGUTYayın Tarihi08.10.2021Revize YapanEmrecan KARAMANRevizyon Tarihi06.05.2022
QUERY
ALTER Function [Reports].[GenisBilanco](@SubeId int ,@Tarih date)
returns table
as
return
Select
Sb.Id as SubeId, Sb.Kodu as SUBE,Cinsi,env.Birim as KODU,ALACAK,BORC,(ALACAK+BORC) as NET,Fn.Bol(ALACAK*K1.Alis, K2.Alis) as ALACAK2,FN.Bol(BORC*K1.Alis,K2.Alis) as BORC2,
Case When Cinsi \<>'GIDER' and Cinsi\<>'GELIR' Then Fn.Bol((ALACAK+BORC) * K1.Alis,K2.alis) End as NET2,Brm.Kodu as BIRIM
From
(
Select Cinsi,SubeId,Birim,Sum(ALACAK) as ALACAK,SUM(BORC) as BORC From
(
Select Cinsi,SubeId,Birim,Sum(ALACAK) as ALACAK,SUM(BORC) as BORC From
(
Select 'BAKIYE' as Cinsi, 1 AS SubeId,K.AnaBirim as Birim,HEsapId,
Case when SUM(Bakiye)>0 Then Sum(Bakiye) Else 0 End as ALACAK,
Case when SUM(Bakiye)<0 Then Sum(Bakiye) Else 0 End as BORC From
(
Select
B.Tarih,HesapTuru,B.IslemId,B.HesapType,B.HesapId,B.KasaId,AnaBirimId,AnaBirim ,CONVERT(Decimal(18,2), Bakiye * Carpan) as Bakiye From Cari.BakiyeIslem B
Left Join Cari.Hesaplar H on H.Id =B.HesapId
Left Join
(
Select K.Id,K.SiraNo,K.Kodu,IsComputed,K.IsStatic,K.AnaBirimId,K.AnaBirim,K.Carpan From
(
Select Id,SiraNo,Kodu,IsComputed ,IsStatic,AnaBirimId,AnaBirim,Carpan From Sistem.Birimler Where IsDeleted ='False'
UNION ALL
Select -1,-1,'TAKOZ',Convert(bit,1),Convert(bit,1),1,'HAS',0.6
) K
) Brm on B.BirimId =Brm.Id
Left Join Sistem.Kasalar K on K.Id =B.KasaId Where (K.SubeId=@SubeId or @SubeId=0 )and B.Tarih <=@Tarih
) K
Where HesapTuru <>4
Group By HesapId,AnaBirim
)L
Group By Birim,Cinsi,SubeId
----------------CİNSİ STOK OLANLAR---------------
UNION ALL
---NAKIT STOKLARI
Select 'STOK',SubeId, AnaBirim ,Case when Sum(Miktar)>0 then Sum(Miktar) Else 0 End as ALACAK,Case when Sum(Miktar)<0 Then Sum(Miktar) Else 0 End as BORC From Stok.GetNakitStoklari (@SubeId,0,@Tarih ) Group by SubeId,AnaBirim
UNION ALL
select 'STOK',SubeId,Birim,Tutar,0 from Stok.GetEmtiaStoklari (@SubeId)
UNION ALL
---MADEN STOKLARI
--Select 'STOK',SubeId,Anabirim,Has From Stok.GetMadenStoklari (1,0,getdate(),'False')
Select t.A,T.SubeId,t.Birim,Case when Sum(t.Has)>0 Then SUm(t.Has) Else 0 End as ALACAK,Case when SUm(t.Has)<0 then Sum(t.HAS) Else 0 End as BORC From Stok.GetMadenStoklari (@SubeId,0,@Tarih,'False')
CROSS APPLY (VALUES('STOK' ,SubeId,AnaBirim, Has ), ('ISCILIK' ,SubeId,Birim , Maliyet)) t(A ,SubeId,Birim, Has)
Group By t.SubeId,t.Birim,T.A
UNION ALL
Select t.*,0 From Stok.GetCesniStoklari (@SubeId,0,@Tarih)
CROSS APPLY (VALUES('CESNI',SubeId,'GUM', GUM), ('CESNI',SubeId,'HAS', Has)) t(A,SubeId,Birim, Has)
------------CİNSİ TAKOZ OLANLAR------------------
UNION ALL
---TAKOZ STOKLARI
SELECT 'TAKOZ',t.*,0 FROM Stok.GetTakozSirketStoklari (0,1,@Tarih)
CROSS APPLY (VALUES(SubeId,'HAS',Case When R='False' Then Miktar * 0.6 Else AuHas end), (SubeId, 'GUM', AgHas ), (SubeId, 'PLT', PltHas ), (SubeId, 'PLD', PldHas )) t(SubeId,Birim, Has) Where Adet \<>0
----------CİNSİ MALZEME (GENEL STOK)------------
UNION ALL
Select 'MALZEME',1,Birim,Maliyet,0 From Stok.GetGenelStoklar(@SubeId,@Tarih)
----------CİNSİ GİDER GELİR (HİZMET)-----------
UNION ALL
Select Case When IslemTuru=0 Then 'GIDER' else 'GELIR' End as Cinsi,K.SubeId,Birim,
SUM(Case When Yon='-' Then Tutar Else 0 End) as ALACAK,
SUM(Case When Yon='+' Then Tutar*-1 Else 0 End) as BORC
From Cari.Islemler I Left Join Sistem.Kasalar K on K.Id =KasaId
Left Join Sistem.Subeler S on S.Id=K.SubeId
Where IslemKodu =5 and I.IsDeleted ='False' and (Tarih>S.RevCostDate And Tarih =@Tarih) and (K.SubeId=@SubeId or @SubeId=0)
Group by K.SubeId,Birim,IslemTuru
UNION ALL
Select Case When SUM(Bakiye *-1)<0 Then 'GIDER' else 'GELIR' End as Cinsi, H.SubeId ,Br.Kodu as Birim,
SUM(Case When Bakiye>0 Then Bakiye Else 0 End) as ALACAK,
SUM(Case When Bakiye<0 Then Bakiye*-1 Else 0 End) as BORC
From Cari.BakiyeIslem B
Left Join Cari.Hesaplar H on H.Id =B.HesapId
Left Join Sistem.Birimler Br on Br.Id =B.BirimId
Where H.HesapTuru =4
Group by Br.Kodu ,H.SubeId
) A
Group by Cinsi,SubeId,Birim
) Env
Left Join Sistem.Subeler Sb on Sb.Id =SubeId
Left Join Sistem.Birimler Brm on Brm.Id=Sb.BilancoBirimId
Left Join Sistem.GetKurListesi (getdate()) K1 on K1.Birim = Env.Birim
Left Join Sistem.GetKurListesi (getdate()) K2 on K2.Birim =Brm.Kodu
LAYOUT
INSERT INTO AifaDev.Reports(RefNo,CreatedDate,ReportName,[Description],Developer,[SQL],[Password],[IsGrid],[IsPivot],[IsReport],[GroupId]) Values('D54CAA03B543473B885F5A684D036DDC','2021-03-22','Genis Bilanco','','M-B','Select * from Reports.GenisBilanco(@SubeId,@Tarih)','','True','False','False','12')
Declare @ReportId int =(SELECT SCOPE_IDENTITY())
Insert Into AifaDev.ReportsFilter(ReportId,SortIndex,FieldName,Caption,ControlType,SQL,DisplayMember,ValueMember,IsBeetwen) Values(@ReportId,'0','Tarih','Tarih','DateEdit','','','','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="PreviewLineCount">-1\</property>
\<property name="PreviewIndent">-1\</property>
\<property name="HorzScrollStep">3\</property>
\<property name="FocusRectStyle">CellFocus\</property>
\<property name="ScrollStyle">LiveVertScroll, LiveHorzScroll\</property>
\<property name="LevelIndent">-1\</property>
\<property name="VertScrollTipFieldName" />
\<property name="ChildGridLevelName" />
\<property name="GroupFormat">{1}{2}\</property>
\<property name="NewItemRowText" />
\<property name="GroupPanelText">Gruplandırmak Istediğiniz Sutunu Buraya Taşıyınız....\</property>
\<property name="PreviewFieldName" />
\<property name="SynchronizeClones">true\</property>
\<property name="BorderStyle">Simple\</property>
\<property name="ViewCaption"> \</property>
\<property name="DetailHeight">350\</property>
\<property name="Name">GrdView\</property>
\<property name="DetailTabHeaderLocation">Top\</property>
\<property name="ActiveFilterEnabled">true\</property>
\<property name="Columns" iskey="true" value="11">
\<property name="Item1" isnull="true" iskey="true">
\<property name="ColumnEditName" />
\<property name="Name">colSubeId\</property>
\<property name="FieldName">SubeId\</property>
\</property>
\<property name="Item2" isnull="true" iskey="true">
\<property name="ColumnEditName" />
\<property name="Width">67\</property>
\<property name="Name">colSUBE\</property>
\<property name="FieldName">SUBE\</property>
\</property>
\<property name="Item3" isnull="true" iskey="true">
\<property name="ColumnEditName" />
\<property name="Visible">true\</property>
\<property name="VisibleIndex">0\</property>
\<property name="Width">86\</property>
\<property name="Caption">CİNSİ\</property>
\<property name="Name">colCinsi\</property>
\<property name="FieldName">Cinsi\</property>
\</property>
\<property name="Item4" isnull="true" iskey="true">
\<property name="ColumnEditName" />
\<property name="Visible">true\</property>
\<property name="VisibleIndex">0\</property>
\<property name="Width">100\</property>
\<property name="AppearanceCell" isnull="true" iskey="true">
\<property name="TextOptions" isnull="true" iskey="true">
\<property name="HAlignment">Center\</property>
\</property>
\<property name="Options" isnull="true" iskey="true">
\<property name="UseTextOptions">true\</property>
\</property>
\</property>
\<property name="Name">colKODU\</property>
\<property name="FieldName">KODU\</property>
\</property>
\<property name="Item5" isnull="true" iskey="true">
\<property name="ColumnEditName" />
\<property name="Visible">true\</property>
\<property name="VisibleIndex">1\</property>
\<property name="DisplayFormat" isnull="true" iskey="true">
\<property name="FormatString">n2\</property>
\<property name="FormatType">Numeric\</property>
\</property>
\<property name="Width">115\</property>
\<property name="Name">colALACAK\</property>
\<property name="FieldName">ALACAK\</property>
\</property>
\<property name="Item6" isnull="true" iskey="true">
\<property name="ColumnEditName" />
\<property name="Visible">true\</property>
\<property name="VisibleIndex">2\</property>
\<property name="DisplayFormat" isnull="true" iskey="true">
\<property name="FormatString">n2\</property>
\<property name="FormatType">Numeric\</property>
\</property>
\<property name="Width">107\</property>
\<property name="Name">colBORC\</property>
\<property name="FieldName">BORC\</property>
\</property>
\<property name="Item7" isnull="true" iskey="true">
\<property name="ColumnEditName" />
\<property name="Visible">true\</property>
\<property name="VisibleIndex">3\</property>
\<property name="DisplayFormat" isnull="true" iskey="true">
\<property name="FormatString">n2\</property>
\<property name="FormatType">Numeric\</property>
\</property>
\<property name="Width">124\</property>
\<property name="Name">colNET\</property>
\<property name="FieldName">NET\</property>
\</property>
\<property name="Item8" isnull="true" iskey="true">
\<property name="ColumnEditName" />
\<property name="Visible">true\</property>
\<property name="VisibleIndex">4\</property>
\<property name="DisplayFormat" isnull="true" iskey="true">
\<property name="FormatString">n2\</property>
\<property name="FormatType">Numeric\</property>
\</property>
\<property name="Width">124\</property>
\<property name="Caption">ALACAK\</property>
\<property name="Name">colALACAK2\</property>
\<property name="FieldName">ALACAK2\</property>
\</property>
\<property name="Item9" isnull="true" iskey="true">
\<property name="ColumnEditName" />
\<property name="Visible">true\</property>
\<property name="VisibleIndex">5\</property>
\<property name="DisplayFormat" isnull="true" iskey="true">
\<property name="FormatString">n2\</property>
\<property name="FormatType">Numeric\</property>
\</property>
\<property name="Width">125\</property>
\<property name="Caption">BORC\</property>
\<property name="Name">colBORC2\</property>
\<property name="FieldName">BORC2\</property>
\</property>
\<property name="Item10" isnull="true" iskey="true">
\<property name="ColumnEditName" />
\<property name="Visible">true\</property>
\<property name="VisibleIndex">6\</property>
\<property name="DisplayFormat" isnull="true" iskey="true">
\<property name="FormatString">n2\</property>
\<property name="FormatType">Numeric\</property>
\</property>
\<property name="Width">123\</property>
\<property name="Caption">NET\</property>
\<property name="Name">colNET2\</property>
\<property name="FieldName">NET2\</property>
\<property name="Summary" iskey="true" value="1">
\<property name="Item1" isnull="true" iskey="true">
\<property name="DisplayFormat">{0:n2}\</property>
\<property name="SummaryType">Sum\</property>
\<property name="Tag" isnull="true" />
\<property name="FieldName">NET2\</property>
\</property>
\</property>
\</property>
\<property name="Item11" isnull="true" iskey="true">
\<property name="ColumnEditName" />
\<property name="Visible">true\</property>
\<property name="VisibleIndex">7\</property>
\<property name="Width">55\</property>
\<property name="AppearanceCell" isnull="true" iskey="true">
\<property name="TextOptions" isnull="true" iskey="true">
\<property name="HAlignment">Center\</property>
\</property>
\<property name="Options" isnull="true" iskey="true">
\<property name="UseTextOptions">true\</property>
\</property>
\</property>
\<property name="Caption">BİRİM\</property>
\<property name="Name">colBIRIM\</property>
\<property name="FieldName">BIRIM\</property>
\</property>
\</property>
\<property name="ViewCaptionHeight">-1\</property>
\<property name="OptionsMenu" isnull="true" iskey="true">
\<property name="ShowGroupSummaryEditorItem">true\</property>
\</property>
\<property name="OptionsPrint" isnull="true" iskey="true">
\<property name="ExpandAllDetails">true\</property>
\</property>
\<property name="OptionsView" isnull="true" iskey="true">
\<property name="ShowGroupPanel">false\</property>
\<property name="ShowFooter">true\</property>
\<property name="ShowIndicator">false\</property>
\<property name="EnableAppearanceEvenRow">true\</property>
\<property name="EnableAppearanceOddRow">true\</property>
\<property name="GroupFooterShowMode">VisibleAlways\</property>
\<property name="ColumnAutoWidth">false\</property>
\</property>
\<property name="OptionsSelection" isnull="true" iskey="true">
\<property name="EnableAppearanceFocusedCell">false\</property>
\</property>
\<property name="OptionsBehavior" isnull="true" iskey="true">
\<property name="Editable">false\</property>
\<property name="AllowIncrementalSearch">true\</property>
\<property name="AutoExpandAllGroups">true\</property>
\</property>
\<property name="FixedLineWidth">2\</property>
\<property name="VertScrollVisibility">Auto\</property>
\<property name="RowSeparatorHeight">0\</property>
\<property name="IndicatorWidth">-1\</property>
\<property name="ColumnPanelRowHeight">-1\</property>
\<property name="HorzScrollVisibility">Auto\</property>
\<property name="FooterPanelHeight">-1\</property>
\<property name="Appearance" isnull="true" iskey="true">
\<property name="ViewCaption" iskey="true" value="ViewCaption">
\<property name="TextOptions" isnull="true" iskey="true">
\<property name="HAlignment">Near\</property>
\</property>
\<property name="Options" isnull="true" iskey="true">
\<property name="UseTextOptions">true\</property>
\</property>
\</property>
\<property name="HeaderPanel" iskey="true" value="HeaderPanel">
\<property name="TextOptions" isnull="true" iskey="true">
\<property name="HAlignment">Center\</property>
\</property>
\<property name="FontStyleDelta">Bold\</property>
\<property name="Options" isnull="true" iskey="true">
\<property name="UseFont">true\</property>
\<property name="UseTextOptions">true\</property>
\</property>
\</property>
\<property name="Row" iskey="true" value="Row">
\<property name="Font">Tahoma, 12pt\</property>
\<property name="Options" isnull="true" iskey="true">
\<property name="UseFont">true\</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="RowHeight">-1\</property>
\<property name="GroupRowHeight">-1\</property>
\<property name="GroupSummary" iskey="true" value="4">
\<property name="Item1" isnull="true" iskey="true">
\<property name="DisplayFormat">{0:n2}\</property>
\<property name="SummaryType">Sum\</property>
\<property name="FieldName">ALACAK2\</property>
\<property name="Tag" isnull="true" />
\<property name="ShowInGroupColumnFooterName">colALACAK2\</property>
\</property>
\<property name="Item2" isnull="true" iskey="true">
\<property name="DisplayFormat">{0:n2}\</property>
\<property name="SummaryType">Sum\</property>
\<property name="FieldName">BORC2\</property>
\<property name="Tag" isnull="true" />
\<property name="ShowInGroupColumnFooterName">colBORC2\</property>
\</property>
\<property name="Item3" isnull="true" iskey="true">
\<property name="DisplayFormat"> ({0:n2})\</property>
\<property name="SummaryType">Sum\</property>
\<property name="FieldName">NET2\</property>
\<property name="Tag" isnull="true" />
\<property name="ShowInGroupColumnFooterName" />
\</property>
\<property name="Item4" isnull="true" iskey="true">
\<property name="DisplayFormat">{0:n2}\</property>
\<property name="SummaryType">Sum\</property>
\<property name="FieldName">NET2\</property>
\<property name="Tag" isnull="true" />
\<property name="ShowInGroupColumnFooterName">colNET2\</property>
\</property>
\</property>
\<property name="FormatConditions" iskey="true" value="4">
\<property name="Item1" isnull="true" iskey="true">
\<property name="Expression">[NET] > 0\</property>
\<property name="Condition">Expression\</property>
\<property name="ColumnName">colNET\</property>
\<property name="Appearance" isnull="true" iskey="true">
\<property name="BackColor">YellowGreen\</property>
\<property name="Options" isnull="true" iskey="true">
\<property name="UseBackColor">true\</property>
\</property>
\</property>
\</property>
\<property name="Item2" isnull="true" iskey="true">
\<property name="Expression">[NET] < 0\</property>
\<property name="Condition">Expression\</property>
\<property name="ColumnName">colNET\</property>
\<property name="Appearance" isnull="true" iskey="true">
\<property name="BackColor">OrangeRed\</property>
\<property name="Options" isnull="true" iskey="true">
\<property name="UseBackColor">true\</property>
\<property name="UseForeColor">true\</property>
\</property>
\<property name="ForeColor">White\</property>
\</property>
\</property>
\<property name="Item3" isnull="true" iskey="true">
\<property name="Expression">[NET] > 0\</property>
\<property name="Condition">Expression\</property>
\<property name="ColumnName">colNET2\</property>
\<property name="Appearance" isnull="true" iskey="true">
\<property name="BackColor">YellowGreen\</property>
\<property name="Options" isnull="true" iskey="true">
\<property name="UseBackColor">true\</property>
\</property>
\</property>
\</property>
\<property name="Item4" isnull="true" iskey="true">
\<property name="Expression">[NET] < 0\</property>
\<property name="Condition">Expression\</property>
\<property name="ColumnName">colNET2\</property>
\<property name="Appearance" isnull="true" iskey="true">
\<property name="BackColor">OrangeRed\</property>
\<property name="Options" isnull="true" iskey="true">
\<property name="UseBackColor">true\</property>
\<property name="UseForeColor">true\</property>
\</property>
\<property name="ForeColor">White\</property>
\</property>
\</property>
\</property>
\<property name="SortInfo">~Xtra#Base64AAEAAAD/////AQAAAAAAAAAMAgAAAF5EZXZFeHByZXNzLlh0cmFHcmlkLnYxNC4xLCBWZXJzaW9uPTE0LjEuMTAuMCwgQ3VsdHVyZT1uZXV0cmFsLCBQdWJsaWNLZXlUb2tlbj1iODhkMTc1NGQ3MDBlNDlhBQEAAAA4RGV2RXhwcmVzcy5YdHJhR3JpZC5Db2x1bW5zLkdyaWRDb2x1bW5Tb3J0SW5mb0NvbGxlY3Rpb24EAAAACmdyb3VwQ291bnQFY2xvbmUPY2xvbmVHcm91cENvdW50E0NvbGxlY3Rpb25CYXNlK2xpc3QAAwADCLMBU3lzdGVtLkNvbGxlY3Rpb25zLkdlbmVyaWMuTGlzdGAxW1tEZXZFeHByZXNzLlh0cmFHcmlkLkNvbHVtbnMuR3JpZENvbHVtblNvcnRJbmZvLCBEZXZFeHByZXNzLlh0cmFHcmlkLnYxNC4xLCBWZXJzaW9uPTE0LjEuMTAuMCwgQ3VsdHVyZT1uZXV0cmFsLCBQdWJsaWNLZXlUb2tlbj1iODhkMTc1NGQ3MDBlNDlhXV0IHFN5c3RlbS5Db2xsZWN0aW9ucy5BcnJheUxpc3QCAAAAAQAAAAkDAAAAAAAAAAkEAAAABAMAAACzAVN5c3RlbS5Db2xsZWN0aW9ucy5HZW5lcmljLkxpc3RgMVtbRGV2RXhwcmVzcy5YdHJhR3JpZC5Db2x1bW5zLkdyaWRDb2x1bW5Tb3J0SW5mbywgRGV2RXhwcmVzcy5YdHJhR3JpZC52MTQuMSwgVmVyc2lvbj0xNC4xLjEwLjAsIEN1bHR1cmU9bmV1dHJhbCwgUHVibGljS2V5VG9rZW49Yjg4ZDE3NTRkNzAwZTQ5YV1dAwAAAAZfaXRlbXMFX3NpemUIX3ZlcnNpb24EAAAwRGV2RXhwcmVzcy5YdHJhR3JpZC5Db2x1bW5zLkdyaWRDb2x1bW5Tb3J0SW5mb1tdAgAAAAgICQUAAAAAAAAAAAAAAAQEAAAAHFN5c3RlbS5Db2xsZWN0aW9ucy5BcnJheUxpc3QDAAAABl9pdGVtcwVfc2l6ZQhfdmVyc2lvbgUAAAgICQYAAAABAAAACAAAAAcFAAAAAAEAAAAAAAAABC5EZXZFeHByZXNzLlh0cmFHcmlkLkNvbHVtbnMuR3JpZENvbHVtblNvcnRJbmZvAgAAABAGAAAABAAAAAkHAAAADQMMCAAAABlEZXZFeHByZXNzLlh0cmFHcmlkLnYxNC4xBQcAAAAuRGV2RXhwcmVzcy5YdHJhR3JpZC5Db2x1bW5zLkdyaWRDb2x1bW5Tb3J0SW5mbwIAAAAJU29ydE9yZGVyCkNvbHVtbk5hbWUAAQgIAAAAAQAAAAYJAAAACGNvbENpbnNpCw==\</property>
\<property name="ActiveFilterString" />
\<property name="GroupSummarySortInfoState" />
\<property name="FindFilterText" />
\<property name="FindPanelVisible">false\</property>
\</XtraSerializer>')
Kaynaklar
- SRC-PROC-DOC — süreç dokümanı #96