Permisi Para Master, mohon jika ada yang tau nih,...mohon bantuannya tentang Store Prosedure Mysql.
Gini aku buat prosedure piutang degan menggunakan Cursor.
dengan menggunakan 3 tabel (invoice, pembayaran cash dan trasfer bank) jdi saya buat 2 Select (invoice) dan (pembayaran cas + pembayaran bank) namun kendalanya data yang ditampilkan dari (pembayaran cas + pembayaran bank) kok double ya mas, berikut script SP nya mas :

CREATE PROCEDURE bkup_project.spAssign(IN kdcust char(10))

 SQL SECURITY INVOKER

BEGIN 

 DECLARE done INT DEFAULT FALSE;

 DECLARE done1 INT DEFAULT FALSE;

 DECLARE kon, kon2,nourut INT DEFAULT 0;

 DECLARE n INTeger DEFAULT 0;

 DECLARE saldoA double (12,2);

 DECLARE selisihtgl, selisihtgl2 integer DEFAULT 0;

 DECLARE saldoB, saldoSisaNota double(12,2) DEFAULT 0;

 DECLARE saldoSisaPembayaran,konsaldo,totalSaldo double(12,2) DEFAULT 0 ;

 DECLARE tgljual_temp, temp_stgl datetime;

 DECLARE a, a2, konNoPem, temp_nopem, temp2_nopem char(15);

 DECLARE b, c, b2, c2 double(12,2);

 DECLARE d, d2, temp_tglnopem, temp2_tglnopem datetime;

 DECLARE CMD1 CURSOR FOR SELECT invoiceheader.noinvoice as nopem, invoiceheader.total AS debet, 0 AS kredit, invoiceheader.tanggal FROM invoiceheader WHERE invoiceheader.kdcust=kdcust ORDER BY tanggal, nopem;

 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

 DROP TABLE IF EXISTS temp_assign;

 CREATE TEMPORARY TABLE temp_assign (no_urut integer,nopem char(15),tglnopem datetime, TglBayar datetime, Debet double, Kredit double, Saldo double, 

 TglSelisih integer, nid integer, saldooA double, saldooB double,SaldoSisa double,SaldoSisaPembayaran double,

 TotalSaldo double);

 [code]

OPEN CMD1;
read_loop1: LOOP

FETCH CMD1 INTO a, b, c, d;
set saldoA = b;
SET saldoSisaNota = saldoA;
SET konNoPem = a;
SET temp_tglnopem = d;
SET konSaldo = saldoSisaNota;
set totalsaldo = totalsaldo +b ;
IF done = TRUE THEN
set kon2 = 1;
end IF;
IF kon2 = 0 then
set nourut=nourut + 1;
INSERT INTO temp_assign (no_urut,Nopem, TglNopem, Debet, Kredit, Saldo, TglSelisih, nid, saldooA, saldooB, SaldoSisa,SaldoSisaPembayaran,TotalSaldo)
VALUES (nourut,a, d, b, c, saldoA, -DATEDIFF(d, NOW()), n, saldoA, saldoB, saldoSisaNota,saldoSisaPembayaran,totalsaldo);
END IF;

IF saldoSisaNota < saldoSisaPembayaran THEN
SET selisihtgl2 = DATEDIFF(temp2_tglnopem, d);
SET c2 = saldoSisaPembayaran;
SET saldoA = saldoA - saldoSisaPembayaran;
SET saldoSisaPembayaran = saldoSisaPembayaran-saldoSisaNota;
SET saldoSisaNota =0;
set d = temp2_tglnopem;
IF saldosisapembayaran <> 0 then
set totalsaldo = totalsaldo;
else
set totalsaldo = totalsaldo - c2;
end if ;
SET nourut=nourut + 1;
INSERT INTO temp_assign (no_urut,Nopem, TglNopem, TglBayar, Debet, Kredit, Saldo, TglSelisih, nid, saldooA, saldooB, SaldoSisa,SaldoSisaPembayaran,TotalSaldo)
VALUES (nourut,temp_nopem, temp_tglnopem, d, 0, c2, saldoA, DATEDIFF(d, temp_tglnopem), n, saldoA, saldoB, saldoSisaNota,saldoSisaPembayaran,totalsaldo);

ELSEIF saldoSisaNota > saldoSisaPembayaran THEN
SET selisihtgl2 = -DATEDIFF(temp_tglnopem, temp2_tglnopem);
SET c2 = saldoSisaPembayaran;
set saldoA = saldoA - c2;
SET saldoSisaNota =saldoSisaNota-saldoSisaPembayaran;

IF konSaldo <> saldoSisaNota then
set saldoSisaPembayaran=0;
set nourut=nourut + 1;
INSERT INTO temp_assign (no_urut,Nopem, TglNopem, TglBayar, Debet, Kredit, Saldo, TglSelisih, nid, saldooA, saldooB, SaldoSisa,SaldoSisaPembayaran,TotalSaldo)
VALUES (nourut,temp_nopem, temp_tglnopem, temp2_tglnopem, 0, c2, saldoA, selisihtgl2, n, saldoA, saldoB,saldoSisaNota,saldoSisaPembayaran,totalsaldo);
END IF;

IF saldoSisaNota < 0 THEN
set saldoA = - saldoA;
SET saldoSisaNota = 0;
end IF ;

ELSEIF saldoSisaNota = saldoSisaPembayaran THEN
SET selisihtgl2 = DATEDIFF(temp2_tglnopem, d);
SET c2 = saldoSisaPembayaran;
SET saldoA = saldoA - saldoSisaPembayaran;
SET saldoSisaPembayaran=0;
SET saldoSisaNota =0;
SET d = temp2_tglnopem;
SET totalsaldo = totalsaldo;
SET nourut=nourut + 1;
INSERT INTO temp_assign (no_urut,Nopem, TglNopem, TglBayar, Debet, Kredit, Saldo, TglSelisih, nid, saldooA, saldooB,SaldoSisa,SaldoSisaPembayaran,TotalSaldo)
VALUES (nourut,temp_nopem, temp_tglnopem, d, 0, c2, saldoA, DATEDIFF(d, temp_tglnopem), n,saldoA, saldoB,saldoSisaNota,saldoSisaPembayaran,totalsaldo);
END IF;
-- END CMD1---

SET tgljual_temp = d;
IF saldoA > 0 AND saldoSisaPembayaran <= 0 THEN
SET saldoB = saldoA;
BEGIN
DECLARE CMD2 CURSOR FOR SELECT DISTINCT bkmheader.nobkm AS nopem,0 AS debet, bkmheader.nombayar AS kredit, bkmheader.tanggal as tanggal
FROM bkmheader WHERE bkmheader.custkode=kdcust
UNION ALL
SELECT DISTINCT bbmheader.nobukti AS nopem, 0 AS debet, bbmheader.totbayar AS kredit, bbmheader.tanggal as tanggal
FROM bbmheader WHERE bbmheader.custkode=kdcust;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;
OPEN CMD2;

read_loop2: LOOP

FETCH CMD2 INTO a2, b2, c2, d2;

IF saldoB <= 0 OR done1 = true THEN
LEAVE read_loop2;
END IF;
SET n= n + 1;
SET saldoB = saldoB - c2;
SET selisihtgl = -DATEDIFF(d, d2);
IF saldoB <0 THEN
SET saldoSisaPembayaran = -saldoB;
end IF;
SET saldoSisaNota = saldosisanota - c2;
IF saldosisanota < 0 then
SET saldosisanota = 0;
END IF;

SET totalsaldo = totalsaldo - c2;
SET nourut=nourut + 1;
INSERT INTO temp_assign (no_urut,Nopem, TglNopem, TglBayar, Debet, Kredit, Saldo, TglSelisih, nid, saldooA, saldooB, SaldoSisa, SaldoSisaPembayaran,TotalSaldo)
VALUES (nourut,a2, temp_tglnopem, d2, 0, c2, saldoB, DATEDIFF(d2, temp_tglnopem),n , saldoA, saldoB, saldoSisaNota,saldoSisaPembayaran,totalsaldo);

SET temp_nopem = a2;
SET temp2_tglnopem = d2;
END LOOP;
CLOSE CMD2;
END;
END IF ;

IF done = TRUE THEN
SET kon2 = 1;
LEAVE read_loop1;
END IF;
END LOOP;
CLOSE CMD1;
SELECT * FROM temp_assign;
END[/code]