Kod: Tümünü seç
order by olaylılı, raporno, ekraporno desc rows 100 Kod: Tümünü seç
order by OLAYYILI DESC ROWS 100Kod: Tümünü seç
ORDER BY OLAYYILI DESC, RAPORNO DESC, EKRAPORNO ROWS 100Kod: Tümünü seç
SET TERM ^^ ;
CREATE PROCEDURE SP_BANKA_EKSTRE (
PRM_USR_ID Integer,
PRM_B_TARIH TimeStamp,
PRM_S_TARIH TimeStamp)
AS
DECLARE VARIABLE INT_BNK_ID INTEGER ;
DECLARE VARIABLE INT_BNS_ID INTEGER ;
DECLARE VARIABLE INT_BNH_ID INTEGER ;
BEGIN
FOR
SELECT BNK_ID, BNS_ID, BNH_ID
FROM RAP_BANKA_EKSTRE_BASLIK
WHERE USR_ID = :PRM_USR_ID
ORDER BY RPR_ID
INTO :INT_BNK_ID, :INT_BNS_ID, :INT_BNH_ID
DO
BEGIN
EXECUTE PROCEDURE SP_BANKA_EKSTRESI_TEKLI (:INT_BNK_ID, :INT_BNS_ID, :
INT_BNH_ID, :PRM_B_TARIH, :PRM_S_TARIH, :PRM_USR_ID) ;
END
END ^^
SET TERM ; ^^
Kod: Tümünü seç
SET TERM ^^ ;
CREATE PROCEDURE SP_BANKA_EKSTRESI_TEKLI (
PRM_BNK_ID Integer,
PRM_BNS_ID Integer,
PRM_BNH_ID Integer,
PRM_B_TARIH TimeStamp,
PRM_S_TARIH TimeStamp,
PRM_USR_ID Integer)
AS
DECLARE VARIABLE DBL_BAKIYE DOUBLE PRECISION ;
DECLARE VARIABLE DBL_BORC_TOPLAM DOUBLE PRECISION ;
DECLARE VARIABLE DBL_ALACAK_TOPLAM DOUBLE PRECISION ;
DECLARE VARIABLE INT_BHR_ID INTEGER ;
DECLARE VARIABLE STR_BFT_KOD VARCHAR(15) ;
DECLARE VARIABLE STR_BFT_ACIKLAMA VARCHAR(50) ;
DECLARE VARIABLE STR_BHR_FIS_NO VARCHAR(15) ;
DECLARE VARIABLE DT_BHR_TARIH TIMESTAMP ;
DECLARE VARIABLE STR_BHR_ACIKLAMA VARCHAR(50) ;
DECLARE VARIABLE SINT_BHR_BA_TIP SMALLINT ;
DECLARE VARIABLE DBL_BHR_TUTAR DOUBLE PRECISION ;
DECLARE VARIABLE DBL_BORC DOUBLE PRECISION ;
DECLARE VARIABLE DBL_ALACAK DOUBLE PRECISION ;
begin
/* ÖNCE DEVİR BAKİYESİNİ AL*/
SELECT SUM(BHR_BA_TIP * BHR_TUTAR),
SUM( CASE WHEN BHR_BA_TIP = 1 THEN BHR_TUTAR ELSE 0 END ),
SUM( CASE WHEN BHR_BA_TIP = -1 THEN BHR_TUTAR ELSE 0 END )
FROM TBL_BANKA_HAREKET
WHERE BNH_ID = :PRM_BNH_ID
AND BHR_TARIH < :PRM_B_TARIH
INTO :DBL_BAKIYE, :DBL_BORC_TOPLAM, :DBL_ALACAK_TOPLAM ;
IF (DBL_BAKIYE IS NULL) THEN
BEGIN
DBL_BAKIYE = 0 ;
END
IF (DBL_BORC_TOPLAM IS NULL) THEN
BEGIN
DBL_BORC_TOPLAM = 0 ;
END
IF (DBL_ALACAK_TOPLAM IS NULL) THEN
BEGIN
DBL_ALACAK_TOPLAM = 0 ;
END
/* ÖNCEKİ TARİHE AİT KAYIT YOK DEMEKTİR, YANİ SIFIR BAKİYE İLE BAŞLAYACAK */
INSERT INTO RAP_BANKA_EKSTRE (USR_ID, BNK_ID, BNS_ID, BNH_ID, RPR_REC_TIP, RPR_ID, BHR_ID, BFT_KOD, BFT_ACIKLAMA,
BHR_FIS_NO, BHR_TARIH, BHR_ACIKLAMA, BHR_BORC, BHR_ALACAK, RPR_BAKIYE)
VALUES (:PRM_USR_ID, :PRM_BNK_ID, :PRM_BNS_ID, :PRM_BNH_ID, 0, GEN_ID(GEN_R04_ID, 1), 0, '', '',
'', :PRM_B_TARIH, 'ÖNCEKİ TARİHTEN DEVREDEN', :DBL_BORC_TOPLAM, :DBL_ALACAK_TOPLAM, :DBL_BAKIYE) ;
/* ŞİMDİ O TARİHTEN SONRAKİ HAREKETLERİ AL BAKALIM */
FOR
SELECT B.BHR_ID, F.BFT_KOD, F.BFT_ACIKLAMA, B.BHR_FIS_NO,
B.BHR_TARIH, B.BHR_ACIKLAMA, B.BHR_BA_TIP, B.BHR_TUTAR
FROM TBL_BANKA_HAREKET B
INNER JOIN TBL_BANKA_FIS_TIP F ON B.BFT_ID = F.BFT_ID
WHERE B.BNH_ID = :PRM_BNH_ID
AND B.BHR_TARIH >= :PRM_B_TARIH
AND B.BHR_TARIH <= :PRM_S_TARIH
ORDER BY B.BNH_ID, B.BHR_TARIH
INTO :INT_BHR_ID, :STR_BFT_KOD, :STR_BFT_ACIKLAMA, :STR_BHR_FIS_NO,
:DT_BHR_TARIH, :STR_BHR_ACIKLAMA, :SINT_BHR_BA_TIP, :DBL_BHR_TUTAR
DO
BEGIN
IF (SINT_BHR_BA_TIP = 1) THEN
BEGIN
/* HAREKET BORÇ HAREKETİDİR */
DBL_BORC = DBL_BHR_TUTAR ;
DBL_ALACAK = 0 ;
END
ELSE
BEGIN
/* HAREKET ALACAK HAREKETİDİR*/
DBL_BORC = 0 ;
DBL_ALACAK = DBL_BHR_TUTAR ;
END
DBL_BAKIYE = DBL_BAKIYE + (SINT_BHR_BA_TIP * DBL_BHR_TUTAR) ;
INSERT INTO RAP_BANKA_EKSTRE (USR_ID, BNK_ID, BNS_ID, BNH_ID, RPR_REC_TIP, RPR_ID, BHR_ID, BFT_KOD, BFT_ACIKLAMA,
BHR_FIS_NO, BHR_TARIH, BHR_ACIKLAMA, BHR_BORC, BHR_ALACAK, RPR_BAKIYE)
VALUES (:PRM_USR_ID, :PRM_BNK_ID, :PRM_BNS_ID, :PRM_BNH_ID, 1, GEN_ID(GEN_R04_ID, 1), :INT_BHR_ID, :STR_BFT_KOD, :STR_BFT_ACIKLAMA,
:STR_BHR_FIS_NO, :DT_BHR_TARIH, :STR_BHR_ACIKLAMA, :DBL_BORC, :DBL_ALACAK, :DBL_BAKIYE) ;
END
end ^^
SET TERM ; ^^
Kod: Tümünü seç
CREATE TABLE RAP_BANKA_EKSTRE_BASLIK
(
RPR_ID DMN_SAYAC NOT NULL,
USR_ID DMN_SAYAC ,
BNK_ID DMN_SAYAC ,
BNS_ID DMN_SAYAC ,
BNH_ID DMN_SAYAC ,
CONSTRAINT PK_RAB_BANKA_EKSTRE_BASLIK PRIMARY KEY (RPR_ID)
);
CREATE ASC INDEX IX_RAP_BANKA_EKSTRE_BASLIK_01 ON RAP_BANKA_EKSTRE_BASLIK (USR_ID);
Kod: Tümünü seç
CREATE TABLE RAP_BANKA_EKSTRE
(
RPR_ID DMN_SAYAC NOT NULL,
USR_ID DMN_SAYAC ,
BNK_ID DMN_SAYAC ,
BNS_ID DMN_SAYAC ,
BNH_ID DMN_SAYAC ,
RPR_REC_TIP DMN_KUCUKTAMSAYI ,
BHR_ID DMN_SAYAC ,
BFT_KOD DMN_KOD ,
BFT_ACIKLAMA DMN_ACIKLAMA ,
BHR_FIS_NO DMN_KOD ,
BHR_TARIH DMN_TARIH ,
BHR_ACIKLAMA DMN_ACIKLAMA ,
BHR_BORC DMN_SAYI ,
BHR_ALACAK DMN_SAYI ,
RPR_BAKIYE DMN_SAYI ,
CONSTRAINT PK_RAP_BANKA_EKSTRE PRIMARY KEY (RPR_ID)
);
Kod: Tümünü seç
CREATE TABLE TBL_BANKA_HAREKET
(
BHR_ID DMN_SAYAC NOT NULL,
FRM_ID DMN_SAYAC ,
BNK_ID DMN_SAYAC ,
BNH_ID DMN_SAYAC ,
CRK_ID DMN_SAYAC ,
KSA_ID DMN_SAYAC ,
BFT_ID DMN_SAYAC ,
BHR_FIS_NO DMN_KOD ,
BHR_TARIH DMN_TARIH ,
BHR_ACIKLAMA DMN_ACIKLAMA ,
BHR_TUTAR DMN_SAYI ,
BHR_BA_TIP DMN_KUCUKTAMSAYI ,
BHR_KAPATILAN_TUTAR DMN_SAYI ,
BHR_DUMMY_CARI DMN_KUCUKTAMSAYI ,
BHR_DUMMY_KASA DMN_KUCUKTAMSAYI ,
BHR_OTOMATIK DMN_KUCUKTAMSAYI ,
BHR_MODUL_ID DMN_KUCUKTAMSAYI ,
BHR_REC_ID DMN_SAYAC ,
EKB_ID DMN_SAYAC ,
INS_USER DMN_KOD ,
INS_TIME DMN_TARIH ,
UPD_USER DMN_KOD ,
UPD_TIME DMN_TARIH ,
ARC_REC DMN_KUCUKTAMSAYI ,
ARC_USER DMN_KOD ,
ARC_TIME DMN_TARIH ,
CONSTRAINT PK_TBL_BANKA_HAREKET PRIMARY KEY (BHR_ID)
);
ALTER TABLE TBL_BANKA_HAREKET ADD CONSTRAINT FK_TBL_BHR_TBL_BANKA
FOREIGN KEY (BNK_ID) REFERENCES TBL_BANKA
(BNK_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
;
ALTER TABLE TBL_BANKA_HAREKET ADD CONSTRAINT FK_TBL_BHR_TBL_BFT
FOREIGN KEY (BFT_ID) REFERENCES TBL_BANKA_FIS_TIP
(BFT_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
;
ALTER TABLE TBL_BANKA_HAREKET ADD CONSTRAINT FK_TBL_BHR_TBL_BNH
FOREIGN KEY (BNH_ID) REFERENCES TBL_BANKA_HESAP
(BNH_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
;
ALTER TABLE TBL_BANKA_HAREKET ADD CONSTRAINT FK_TBL_BHR_TBL_CARI_KART
FOREIGN KEY (CRK_ID) REFERENCES TBL_CARI_KART
(CRK_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
;
ALTER TABLE TBL_BANKA_HAREKET ADD CONSTRAINT FK_TBL_BHR_TBL_FIRMA
FOREIGN KEY (FRM_ID) REFERENCES TBL_FIRMA
(FRM_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
;
ALTER TABLE TBL_BANKA_HAREKET ADD CONSTRAINT FK_TBL_BHR_TBL_KASA
FOREIGN KEY (KSA_ID) REFERENCES TBL_KASA
(KSA_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
;
CREATE ASC INDEX IX_TBL_BANKA_HAREKET_01 ON TBL_BANKA_HAREKET (FRM_ID, CRK_ID, BHR_TARIH, BHR_FIS_NO);
CREATE ASC INDEX IX_TBL_BANKA_HAREKET_02 ON TBL_BANKA_HAREKET (FRM_ID, KSA_ID, BHR_TARIH, BHR_FIS_NO);
CREATE ASC INDEX IX_TBL_BANKA_HAREKET_03 ON TBL_BANKA_HAREKET (BNH_ID, BHR_TARIH, BHR_FIS_NO);
CREATE ASC INDEX IX_TBL_BANKA_HAREKET_04 ON TBL_BANKA_HAREKET (BHR_MODUL_ID, BHR_REC_ID);
SET TERM ^^ ;
CREATE TRIGGER TRG_TBL_BANKA_HAREKET_AD FOR TBL_BANKA_HAREKET ACTIVE AFTER DELETE POSITION 0 AS
begin
IF (OLD.BHR_DUMMY_CARI = 0) THEN
BEGIN
/* */
/* CARİ HAREKETTEN SİLİYORUZ */
/* */
DELETE FROM TBL_CARI_HAREKET
WHERE CHR_MODUL_ID = 7
AND CHR_REC_ID = OLD.BHR_ID ;
END
IF (OLD.BHR_DUMMY_KASA = 0) THEN
BEGIN
/* */
/* CARİ HAREKETTEN SİLİYORUZ */
/* */
DELETE FROM TBL_KASA_HAREKET
WHERE KHR_MODUL_ID = 7
AND KHR_REC_ID = OLD.BHR_ID ;
END
/* */
/* BANKA HESABINA İŞLEYECEĞİZ */
/* */
IF (OLD.BHR_BA_TIP = 1) THEN
BEGIN
UPDATE TBL_BANKA_HESAP
SET BNH_BORC = BNH_BORC - OLD.BHR_TUTAR
WHERE BNH_ID = OLD.BNH_ID ;
END
ELSE
BEGIN
UPDATE TBL_BANKA_HESAP
SET BNH_ALACAK = BNH_ALACAK - OLD.BHR_TUTAR
WHERE BNH_ID = OLD.BNH_ID ;
END
end ^^
SET TERM ; ^^
SET TERM ^^ ;
CREATE TRIGGER TRG_TBL_BANKA_HAREKET_AI FOR TBL_BANKA_HAREKET ACTIVE AFTER INSERT POSITION 0 AS
DECLARE VARIABLE INT_CFT_ID INTEGER;
DECLARE VARIABLE INT_KFT_ID INTEGER;
DECLARE VARIABLE SINT_BA_TIP SMALLINT;
begin
IF (NEW.BHR_DUMMY_CARI = 0) THEN
BEGIN
/* */
/* CARİ HAREKETİ İŞLİYORUZ */
/* */
SELECT CFT_ID
FROM TBL_BANKA_FIS_TIP
WHERE BFT_ID = NEW.BFT_ID
INTO :INT_CFT_ID ;
SELECT CFT_BA_TIP
FROM TBL_CARI_FIS_TIP
WHERE CFT_ID = :INT_CFT_ID
INTO :SINT_BA_TIP ;
INSERT INTO TBL_CARI_HAREKET (CHR_ID, FRM_ID, CFT_ID, CHR_FIS_NO, CRK_ID, CHR_ACIKLAMA, CHR_BA_TIP,
CHR_TARIH, CHR_VADE_TARIH, CHR_TUTAR, CHR_KAPATILAN_TUTAR, CHR_MODUL_ID, CHR_REC_ID,
CHR_OTOMATIK, INS_USER, INS_TIME)
VALUES (GEN_ID(GEN_CHR_ID,1), NEW.FRM_ID, :INT_CFT_ID, NEW.BHR_FIS_NO, NEW.CRK_ID,
SUBSTRING('[OTO] - ' || NEW.BHR_ACIKLAMA FROM 1 FOR 50), :SINT_BA_TIP,
NEW.BHR_TARIH, NEW.BHR_TARIH, NEW.BHR_TUTAR, NEW.BHR_KAPATILAN_TUTAR, 7, NEW.BHR_ID,
1, NEW.INS_USER, CURRENT_TIMESTAMP) ;
END
IF (NEW.BHR_DUMMY_KASA = 0) THEN
BEGIN
/* */
/* KASA HAREKETE İŞLİYORUZ */
/* */
SELECT KFT_ID
FROM TBL_BANKA_FIS_TIP
WHERE BFT_ID = NEW.BFT_ID
INTO :INT_KFT_ID ;
SELECT KFT_BA_TIP
FROM TBL_KASA_FIS_TIP
WHERE KFT_ID = :INT_KFT_ID
INTO :SINT_BA_TIP ;
INSERT INTO TBL_KASA_HAREKET (KHR_ID, FRM_ID, KSA_ID, KFT_ID, KHR_FIS_NO,
BNH_ID, KHR_ACIKLAMA, KHR_BA_TIP,
KHR_TARIH, KHR_TUTAR, KHR_KAPATILAN_TUTAR, KHR_MODUL_ID, KHR_REC_ID,
KHR_OTOMATIK, KHR_DUMMY_CARI, INS_USER, INS_TIME)
VALUES (GEN_ID(GEN_KHR_ID,1), NEW.FRM_ID, NEW.KSA_ID, :INT_KFT_ID, NEW.BHR_FIS_NO,
NEW.BNH_ID, SUBSTRING('[OTO] - ' || NEW.BHR_ACIKLAMA FROM 1 FOR 50), :SINT_BA_TIP,
NEW.BHR_TARIH, NEW.BHR_TUTAR, NEW.BHR_KAPATILAN_TUTAR, 7, NEW.BHR_ID,
1, 1, NEW.INS_USER, CURRENT_TIMESTAMP) ;
END
/* */
/* BANKA HESABINA İŞLEYECEĞİZ */
/* */
IF (NEW.BHR_BA_TIP = 1) THEN
BEGIN
UPDATE TBL_BANKA_HESAP
SET BNH_BORC = BNH_BORC + NEW.BHR_TUTAR
WHERE BNH_ID = NEW.BNH_ID ;
END
ELSE
BEGIN
UPDATE TBL_BANKA_HESAP
SET BNH_ALACAK = BNH_ALACAK + NEW.BHR_TUTAR
WHERE BNH_ID = NEW.BNH_ID ;
END
end ^^
SET TERM ; ^^
SET TERM ^^ ;
CREATE TRIGGER TRG_TBL_BANKA_HAREKET_AU FOR TBL_BANKA_HAREKET ACTIVE AFTER UPDATE POSITION 0 AS
DECLARE VARIABLE INT_CFT_ID INTEGER;
DECLARE VARIABLE INT_KFT_ID INTEGER;
DECLARE VARIABLE SINT_BA_TIP SMALLINT;
begin
IF (OLD.BHR_DUMMY_CARI = 0) THEN
BEGIN
/* */
/* CARİ HAREKETİ GÜNCELLİYORUZ */
/* */
SELECT CFT_ID
FROM TBL_BANKA_FIS_TIP
WHERE BFT_ID = NEW.BFT_ID
INTO :INT_CFT_ID ;
SELECT CFT_BA_TIP
FROM TBL_CARI_FIS_TIP
WHERE CFT_ID = :INT_CFT_ID
INTO :SINT_BA_TIP ;
UPDATE TBL_CARI_HAREKET
SET CHR_TARIH = NEW.BHR_TARIH,
CHR_VADE_TARIH = NEW.BHR_TARIH,
CHR_TUTAR = NEW.BHR_TUTAR,
CHR_KAPATILAN_TUTAR = NEW.BHR_KAPATILAN_TUTAR,
CHR_BA_TIP = :SINT_BA_TIP,
CHR_ACIKLAMA = SUBSTRING('[OTO] - ' || NEW.BHR_ACIKLAMA FROM 1 FOR 50),
UPD_USER = NEW.UPD_USER,
UPD_TIME = CURRENT_TIMESTAMP
WHERE CHR_MODUL_ID = 7
AND CHR_REC_ID = NEW.BHR_ID ;
END
IF (OLD.BHR_DUMMY_KASA = 0) THEN
BEGIN
/* */
/* KASA HAREKETİNİ GÜNCELLİYORUZ */
/* */
SELECT KFT_ID
FROM TBL_BANKA_FIS_TIP
WHERE BFT_ID = NEW.BFT_ID
INTO :INT_KFT_ID ;
SELECT KFT_BA_TIP
FROM TBL_KASA_FIS_TIP
WHERE KFT_ID = :INT_KFT_ID
INTO :SINT_BA_TIP ;
UPDATE TBL_KASA_HAREKET
SET KSA_ID = NEW.KSA_ID,
KHR_TARIH = NEW.BHR_TARIH,
KHR_TUTAR = NEW.BHR_TUTAR,
KHR_KAPATILAN_TUTAR = NEW.BHR_KAPATILAN_TUTAR,
KHR_BA_TIP = :SINT_BA_TIP,
KHR_ACIKLAMA = SUBSTRING('[OTO] - ' || NEW.BHR_ACIKLAMA FROM 1 FOR 50),
UPD_USER = NEW.UPD_USER,
UPD_TIME = CURRENT_TIMESTAMP
WHERE KHR_MODUL_ID = 7
AND KHR_REC_ID = NEW.BHR_ID ;
END
/* */
/* BANKA HESABINA İŞLEYECEĞİZ */
/* */
IF (OLD.BHR_BA_TIP = 1) THEN
BEGIN
/* FİŞ TİPİ DEĞİŞMEZ İSE BORÇ ALACAK TİPİ DE DEĞİŞMEZ */
/* BU SEBEPLE NEW.BHR_BA_TIP'İ KONTROL ETMEMİZE GEREK YOK */
UPDATE TBL_BANKA_HESAP
SET BNH_BORC = (BNH_BORC - OLD.BHR_TUTAR) + NEW.BHR_TUTAR
WHERE BNH_ID = OLD.BNH_ID ;
END
ELSE
BEGIN
UPDATE TBL_BANKA_HESAP
SET BNH_ALACAK = (BNH_ALACAK - OLD.BHR_TUTAR) + NEW.BHR_TUTAR
WHERE BNH_ID = OLD.BNH_ID ;
END
END ^^
SET TERM ; ^^
SET TERM ^^ ;
CREATE TRIGGER TRG_TBL_BANKA_HAREKET_BH FOR TBL_BANKA_HAREKET ACTIVE BEFORE INSERT OR UPDATE POSITION 0 AS
begin
NEW.BHR_FIS_NO = UPPER(NEW.BHR_FIS_NO) ;
NEW.BHR_ACIKLAMA = UPPER(NEW.BHR_ACIKLAMA) ;
end ^^
SET TERM ; ^^
Kod: Tümünü seç
CREATE TABLE TBL_BANKA
(
BNK_ID DMN_SAYAC NOT NULL,
BNK_KOD DMN_KOD ,
BNK_ACIKLAMA DMN_ACIKLAMA ,
SYS_REC DMN_KUCUKTAMSAYI ,
INS_USER DMN_KOD ,
INS_TIME DMN_TARIH ,
UPD_USER DMN_KOD ,
UPD_TIME DMN_TARIH ,
CONSTRAINT PK_TBL_BANKA PRIMARY KEY (BNK_ID)
);
SET TERM ^^ ;
CREATE TRIGGER TRG_TBL_BANKA_BD FOR TBL_BANKA ACTIVE BEFORE DELETE POSITION 0 AS
begin
IF (OLD.SYS_REC = 1) THEN
BEGIN
EXCEPTION EXP_SYS_REC_CANT_DELETE;
END
end ^^
SET TERM ; ^^
SET TERM ^^ ;
CREATE TRIGGER TRG_TBL_BANKA_BH FOR TBL_BANKA ACTIVE BEFORE INSERT OR UPDATE POSITION 1 AS
begin
NEW.BNK_KOD = UPPER(NEW.BNK_KOD) ;
NEW.BNK_ACIKLAMA = UPPER(NEW.BNK_ACIKLAMA) ;
end ^^
SET TERM ; ^^
SET TERM ^^ ;
CREATE TRIGGER TRG_TBL_BANKA_BU FOR TBL_BANKA ACTIVE BEFORE UPDATE POSITION 0 AS
begin
IF (OLD.SYS_REC = 1) THEN
BEGIN
EXCEPTION EXP_SYS_REC_CANT_UPDATE;
END
end ^^
SET TERM ; ^^
Kod: Tümünü seç
CREATE TABLE TBL_BANKA_SUBE
(
BNS_ID DMN_SAYAC NOT NULL,
BNK_ID DMN_SAYAC ,
BNS_KOD DMN_KOD ,
BNS_ACIKLAMA DMN_ACIKLAMA ,
INS_USER DMN_KOD ,
INS_TIME DMN_TARIH ,
UPD_USER DMN_KOD ,
UPD_TIME DMN_TARIH ,
CONSTRAINT PK_TBL_BANKA_SUBE PRIMARY KEY (BNS_ID)
);
ALTER TABLE TBL_BANKA_SUBE ADD CONSTRAINT FK_TBL_BNS_TBL_BANKA
FOREIGN KEY (BNK_ID) REFERENCES TBL_BANKA
(BNK_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
;
SET TERM ^^ ;
CREATE TRIGGER TRG_TBL_BANKA_SUBE_BH FOR TBL_BANKA_SUBE ACTIVE BEFORE INSERT OR UPDATE POSITION 1 AS
begin
NEW.BNS_KOD = UPPER(NEW.BNS_KOD) ;
NEW.BNS_ACIKLAMA = UPPER(NEW.BNS_ACIKLAMA) ;
end ^^
SET TERM ; ^^
Kod: Tümünü seç
CREATE TABLE TBL_BANKA_HESAP_TIP
(
BHT_ID DMN_SAYAC NOT NULL,
BHT_KOD DMN_KOD ,
BHT_ACIKLAMA DMN_ACIKLAMA ,
SYS_REC DMN_KUCUKTAMSAYI ,
INS_USER DMN_KOD ,
INS_TIME DMN_TARIH ,
UPD_USER DMN_KOD ,
UPD_TIME DMN_TARIH ,
CONSTRAINT PK_TBL_BANKA_HESAP_TIP PRIMARY KEY (BHT_ID)
);
SET TERM ^^ ;
CREATE TRIGGER TRG_TBL_BANKA_HESAP_TIP_BD FOR TBL_BANKA_HESAP_TIP ACTIVE BEFORE DELETE POSITION 0 AS
begin
IF (OLD.SYS_REC = 1) THEN
BEGIN
EXCEPTION EXP_SYS_REC_CANT_DELETE;
END
end ^^
SET TERM ; ^^
SET TERM ^^ ;
CREATE TRIGGER TRG_TBL_BANKA_HESAP_TIP_BH FOR TBL_BANKA_HESAP_TIP ACTIVE BEFORE INSERT OR UPDATE POSITION 1 AS
begin
NEW.BHT_KOD = UPPER(NEW.BHT_KOD) ;
NEW.BHT_ACIKLAMA = UPPER(NEW.BHT_ACIKLAMA) ;
end ^^
SET TERM ; ^^
SET TERM ^^ ;
CREATE TRIGGER TRG_TBL_BANKA_HESAP_TIP_BU FOR TBL_BANKA_HESAP_TIP ACTIVE BEFORE UPDATE POSITION 0 AS
begin
IF (OLD.SYS_REC = 1) THEN
BEGIN
EXCEPTION EXP_SYS_REC_CANT_UPDATE;
END
end ^^
SET TERM ; ^^
Kod: Tümünü seç
CREATE TABLE TBL_BANKA_HESAP
(
BNH_ID DMN_SAYAC NOT NULL,
FRM_ID DMN_SAYAC ,
BNK_ID DMN_SAYAC ,
BNS_ID DMN_SAYAC ,
BHT_ID DMN_SAYAC ,
BNH_KOD DMN_KOD ,
BNH_ACIKLAMA DMN_ACIKLAMA ,
BNH_IBAN_NO DMN_ACIKLAMA ,
BNH_SAHIP DMN_ACIKLAMA ,
BNH_BORC DMN_SAYI ,
BNH_ALACAK DMN_SAYI ,
INS_USER DMN_KOD ,
INS_TIME DMN_TARIH ,
UPD_USER DMN_KOD ,
UPD_TIME DMN_TARIH ,
CONSTRAINT PK_TBL_BANKA_HESAP PRIMARY KEY (BNH_ID)
);
ALTER TABLE TBL_BANKA_HESAP ADD CONSTRAINT FK_TBL_BNH_TBL_BANKA
FOREIGN KEY (BNK_ID) REFERENCES TBL_BANKA
(BNK_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
;
ALTER TABLE TBL_BANKA_HESAP ADD CONSTRAINT FK_TBL_BNH_TBL_BHT
FOREIGN KEY (BHT_ID) REFERENCES TBL_BANKA_HESAP_TIP
(BHT_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
;
ALTER TABLE TBL_BANKA_HESAP ADD CONSTRAINT FK_TBL_BNH_TBL_BNS
FOREIGN KEY (BNS_ID) REFERENCES TBL_BANKA_SUBE
(BNS_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
;
ALTER TABLE TBL_BANKA_HESAP ADD CONSTRAINT FK_TBL_BNH_TBL_FIRMA
FOREIGN KEY (FRM_ID) REFERENCES TBL_FIRMA
(FRM_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
;
SET TERM ^^ ;
CREATE TRIGGER TRG_TBL_BANKA_HESAP_BH FOR TBL_BANKA_HESAP ACTIVE BEFORE INSERT OR UPDATE POSITION 0 AS
begin
NEW.BNH_KOD = UPPER(NEW.BNH_KOD) ;
NEW.BNH_ACIKLAMA = UPPER(NEW.BNH_ACIKLAMA) ;
NEW.BNH_IBAN_NO = UPPER(NEW.BNH_IBAN_NO) ;
NEW.BNH_SAHIP = UPPER(NEW.BNH_SAHIP) ;
end ^^
SET TERM ; ^^
Kod: Tümünü seç
CREATE TABLE TBL_BANKA_FIS_TIP
(
BFT_ID DMN_SAYAC NOT NULL,
BFT_KOD DMN_KOD ,
BFT_ACIKLAMA DMN_ACIKLAMA ,
BFT_BA_TIP DMN_KUCUKTAMSAYI ,
BFT_KARSI_BFT_ID DMN_SAYAC ,
CFT_ID DMN_SAYAC ,
KFT_ID DMN_SAYAC ,
SYS_REC DMN_KUCUKTAMSAYI ,
INS_USER DMN_KOD ,
INS_TIME DMN_TARIH ,
UPD_USER DMN_KOD ,
UPD_TIME DMN_TARIH ,
CONSTRAINT PK_TBL_BANKA_FIS_TIP PRIMARY KEY (BFT_ID)
);
SET TERM ^^ ;
CREATE TRIGGER TRG_TBL_BANKA_FIS_TIP_BD FOR TBL_BANKA_FIS_TIP ACTIVE BEFORE DELETE POSITION 0 AS
begin
IF (OLD.SYS_REC = 1) THEN
BEGIN
EXCEPTION EXP_SYS_REC_CANT_DELETE;
END
end ^^
SET TERM ; ^^
SET TERM ^^ ;
CREATE TRIGGER TRG_TBL_BANKA_FIS_TIP_BH FOR TBL_BANKA_FIS_TIP ACTIVE BEFORE INSERT OR UPDATE POSITION 1 AS
begin
NEW.BFT_KOD = UPPER(NEW.BFT_KOD) ;
NEW.BFT_ACIKLAMA = UPPER(NEW.BFT_ACIKLAMA) ;
end ^^
SET TERM ; ^^
SET TERM ^^ ;
CREATE TRIGGER TRG_TBL_BANKA_FIS_TIP_BU FOR TBL_BANKA_FIS_TIP ACTIVE BEFORE UPDATE POSITION 0 AS
begin
IF (OLD.SYS_REC = 1) THEN
BEGIN
EXCEPTION EXP_SYS_REC_CANT_UPDATE;
END
end ^^
SET TERM ; ^^