Nakit Hareketler icin bir tablom var.
Kod: Tümünü seç
CREATE GENERATOR GEN_TBL_FIN_NAKIT_ID;
CREATE TABLE TBL_FIN_NAKIT (
ID INTEGER NOT NULL,
ODEYEN_ID INTEGER,
TAHSILDAR_ID INTEGER,
TARIH DATE DEFAULT 'NOW',
ACIKLAMA VARCHAR(100),
MIKTAR_ODEYEN DECIMAL(12,2) NOT NULL,
MIKTAR_TAHSILDAR DECIMAL(12,2) NOT NULL,
CREATEDON TIMESTAMP DEFAULT 'NOW' NOT NULL,
CREATEDBY INTEGER DEFAULT 1 NOT NULL,
LASTUPDATEON TIMESTAMP DEFAULT 'NOW' NOT NULL,
LASTUPDATEBY INTEGER DEFAULT 1 NOT NULL
);
Kod: Tümünü seç
CREATE OR ALTER VIEW V_FIN_HESAP(
TRNSID,
HESAP,
CL_NAME,
TRNSDATE,
FLAG,
TRNSTYPE,
TRNSAMOUNT,
CARPAN)
AS
select TRS.ID, TRS.hesap_id, C.CLIENT, TRS.TARIH, 'CHF' as flag, 'TAHSILAT' as trnstype ,TRS.miktar as trnsamount,TRS.carpan from TBL_FIN_TRANSACTIONS TRS left join TBL_FIN_CLIENTS C on C.id = TRS.cari_id where TRS.islem_tipi='TAHSILAT'
UNION
select TRS.ID, TRS.hesap_id, C.CLIENT, TRS.TARIH, 'CHF' as flag, 'ODEME' as trnstype ,TRS.miktar as trnsamount,TRS.carpan from TBL_FIN_TRANSACTIONS TRS left join TBL_FIN_CLIENTS C on C.id = TRS.cari_id where TRS.islem_tipi='ODEME'
UNION
select NKT.ID, NKT.ODEYEN_ID, 'TRANSFER - '||(select hesap_adi from TBL_FIN_HESAPLAR where id=NKT.tahsildar_id)||' *** '||NKT.aciklama, NKT.TARIH, 'NKT' as flag, 'ODEME' as trnstype ,NKT.MIKTAR_ODEYEN as trnsamount,-1 from TBL_FIN_NAKIT NKT left join TBL_FIN_HESAPLAR C on C.id = NKT.odeyen_id
UNION
select NKT.ID, NKT.TAHSILDAR_ID, 'TRANSFER - '||(select hesap_adi from TBL_FIN_HESAPLAR where id=NKT.odeyen_id)||' *** '||NKT.aciklama, NKT.TARIH, 'NKT' as flag, 'TAHSILAT' as trnstype ,NKT.miktar_tahsildar as trnsamount,1 from TBL_FIN_NAKIT NKT left join TBL_FIN_HESAPLAR C on C.id = NKT.tahsildar_id
UNION
select AF.ID, AF.HESAP, 'ACILIS FISI - '||C.HESAP_ADI, AF.TARIH, 'ACL' as flag, 'TAHSILAT' as trnstype ,AF.miktar as trnsamount,AF.carpan from TBL_FIN_ACILIS AF left join TBL_FIN_HESAPLAR C on C.id = AF.HESAP WHERE AF.flag = 'NKT' and AF.carpan=1
UNION
select AF.ID, AF.HESAP, 'ACILIS FISI - '||C.HESAP_ADI, AF.TARIH, 'ACL' as flag, 'ODEME' as trnstype ,AF.miktar as trnsamount,AF.carpan from TBL_FIN_ACILIS AF left join TBL_FIN_HESAPLAR C on C.id = AF.HESAP WHERE AF.flag = 'NKT' and AF.carpan=-1
;
Kod: Tümünü seç
SELECT HES.*, GIRIS, CIKIS,BAKIYE FROM TBL_FIN_HESAPLAR HES
left join (select HESAP, sum(TRNSAMOUNT) as GIRIS from V_FIN_HESAP where TRNSTYPE='TAHSILAT' group by HESAP) as VHARG
on HES.ID=VHARG.HESAP
left join (select HESAP, sum(TRNSAMOUNT) as CIKIS from V_FIN_HESAP where TRNSTYPE='ODEME' group by HESAP) as VHARC
on HES.ID=VHARC.HESAP
left join (select HESAP, sum(TRNSAMOUNT*CARPAN) as BAKIYE from V_FIN_HESAP group by HESAP) as VHARB
on HES.ID=VHARB.HESAP
ORDER BY HESAP_ADI
Sorunun cozumu icin yardimci olursaniz memnun olurum