USE `sakti`;

DROP FUNCTION IF EXISTS `sakti`.`getAdkReferensi`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `sakti`.`getAdkReferensi`()
BEGIN
	DECLARE VKODERS CHAR(20);
	DECLARE VKODEUAKPB CHAR(50);
	DECLARE VUSERUAKPB CHAR(150);
	
	SELECT ID, KODE_UAKPB, USER_INTERKONEKSI INTO VKODERS, VKODEUAKPB, VUSERUAKPB FROM uakpb WHERE STATUS = 1 LIMIT 1;
	SELECT
			CONCAT(
				CONCAT(VKODERS,LPAD(b.ID,10,'0')), ';'
				, b.KODE_PSEDIA, VKODEUAKPB, ';'
				, VUSERUAKPB , ';' 
				, b.TANGGAL , '.00;' 
				, VKODEUAKPB, ';' 
				, LEFT(b.KODE_PSEDIA,10), ';' 
				, b.KODE_PSEDIA, ';' 
				, TRIM(b.NAMA), ';' 
				, IF(s.NAMA IS NULL,'-',s.NAMA), ';' 
				, '', ';' 
				, '', ';' 
				, '', ';' 
				, ''
			) record_txt
			, CONCAT("ADK_REFERENSI_",VKODERS) nama_file_txt
	FROM inventory.barang b
	LEFT JOIN inventory.satuan s ON s.ID = b.SATUAN
	WHERE b.`STATUS` = 1 AND LENGTH(b.KODE_PSEDIA) = 16;
END//
DELIMITER ;