getKeteranganTransaksi

 avatar
unknown
mysql
10 months ago
3.0 kB
65
Indexable
CREATE FUNCTION `getKeteranganTransaksi`(`PJENIS` INT, `PREF` INT) RETURNS varchar(250) CHARSET latin1
    DETERMINISTIC
BEGIN
	DECLARE VDESC, VDESKRIPSI VARCHAR(250);
	
	SET VDESC = "-";
	IF PJENIS = 23 THEN 
		SELECT CONCAT("Tujuan : ",r.DESKRIPSI) INTO VDESKRIPSI
		FROM inventory.pengiriman_detil p, inventory.pengiriman pn, master.ruangan r
		WHERE r.ID = pn.TUJUAN and pn.NOMOR = p.PENGIRIMAN and p.ID=PREF;
		IF FOUND_ROWS() > 0 THEN
			RETURN VDESKRIPSI;
		END IF;
	END IF;
	
	IF PJENIS = 20 THEN 
		SELECT CONCAT("Asal : ",r.DESKRIPSI) INTO VDESKRIPSI
		FROM inventory.pengiriman_detil p, inventory.pengiriman pn, master.ruangan r
		WHERE r.ID = pn.ASAL and pn.NOMOR = p.PENGIRIMAN and p.ID=PREF;
		IF FOUND_ROWS() > 0 THEN
			RETURN VDESKRIPSI;
		END IF;
	END IF;
	
	IF PJENIS = 53 THEN 
		SELECT CONCAT("Alasan : ", r.DESKRIPSI) INTO VDESKRIPSI
		FROM inventory.transaksi_koreksi t
		LEFT JOIN master.referensi r ON r.ID = t.ALASAN AND r.JENIS = 900601
		, inventory.transaksi_koreksi_detil d
		WHERE d.KOREKSI = t.ID AND d.ID = PREF;
		IF FOUND_ROWS() > 0 THEN
			RETURN VDESKRIPSI;
		END IF;
	END IF;
	
	IF PJENIS = 54 THEN 
		SELECT CONCAT("Alasan : ", r.DESKRIPSI) INTO VDESKRIPSI
		FROM inventory.transaksi_koreksi t
		LEFT JOIN master.referensi r ON r.ID = t.ALASAN AND r.JENIS = 900602
		, inventory.transaksi_koreksi_detil d
		WHERE d.KOREKSI = t.ID AND d.ID = PREF;
		IF FOUND_ROWS() > 0 THEN
			RETURN VDESKRIPSI;
		END IF;
	END IF;
	
	IF PJENIS = 21 THEN 
		SELECT CONCAT("PBF : ", s.NAMA) INTO VDESKRIPSI
		FROM inventory.penerimaan_barang p
		LEFT JOIN inventory.penyedia s ON s.ID = p.REKANAN
		, inventory.penerimaan_barang_detil d
		WHERE d.PENERIMAAN = p.ID AND d.ID = PREF;
		IF FOUND_ROWS() > 0 THEN
			RETURN VDESKRIPSI;
		END IF;
	END IF;
	
	IF PJENIS = 55 THEN 
		SELECT CONCAT("Asal : ", s.NAMA) INTO VDESKRIPSI
		FROM inventory.hibah h
		LEFT JOIN inventory.penyedia s ON s.ID = h.ASAL
		, inventory.hibah_detil d
		WHERE d.HIBAH = h.ID AND d.ID = PREF;
		IF FOUND_ROWS() > 0 THEN
			RETURN VDESKRIPSI;
		END IF;
	END IF;
	
	IF PJENIS = 57 THEN 
		SELECT CONCAT("Asal : ", s.NAMA) INTO VDESKRIPSI
		FROM inventory.hibah h
		LEFT JOIN inventory.penyedia s ON s.ID = h.ASAL
		, inventory.hibah_detil d
		WHERE d.HIBAH = h.ID AND d.ID = PREF;
		IF FOUND_ROWS() > 0 THEN
			RETURN VDESKRIPSI;
		END IF;
	END IF;
	
	IF PJENIS = 56 THEN 
		SELECT CONCAT("Tujuan : ", t.NAMA) INTO VDESKRIPSI
		FROM inventory.hibah h
		LEFT JOIN inventory.tujuan_hibah t ON t.ID = h.ASAL
		, inventory.hibah_detil d
		WHERE d.HIBAH = h.ID AND d.ID = PREF;
		IF FOUND_ROWS() > 0 THEN
			RETURN VDESKRIPSI;
		END IF;		
	END IF;
	
	IF PJENIS = 58 THEN 
		SELECT CONCAT("Tujuan : ", t.NAMA) INTO VDESKRIPSI
		FROM inventory.hibah h
		LEFT JOIN inventory.tujuan_hibah t ON t.ID = h.ASAL
		, inventory.hibah_detil d
		WHERE d.HIBAH = h.ID AND d.ID = PREF;
		IF FOUND_ROWS() > 0 THEN
			RETURN VDESKRIPSI;
		END IF;		
	END IF;
	
	RETURN VDESC;
END
Editor is loading...
Leave a Comment