DROP PROCEDURE IF EXISTS `sp_GetPaidBadDebtInvoices`;

DELIMITER //

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_GetPaidBadDebtInvoices`(
	IN `Type` VARCHAR(50),
	IN `CompanyID` INT,
	IN `EntityID` INT,
	IN `RecoveredDate` DATE
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
	DECLARE TaxID INT;
	DECLARE TaxCode VARCHAR(10);
	DROP TEMPORARY TABLE IF EXISTS Results;
	CREATE TEMPORARY TABLE Results (
		BadDebtID INT,
		BadDebtDate DATE,
		InvoiceID INT,
		InvoiceNo VARCHAR(128),
		InvoiceDate DATE,
		OutstandingAmount DECIMAL(15,5),
		BadDebtAmount DECIMAL(15,5),
		Paid DECIMAL(15,5),
		Paid_GST DECIMAL(15,5),
		DueDays INT,
		TaxID INT,
		TaxCode VARCHAR(10),
		IsRecovered INT,
		IsHistorical INT
	);
	IF `Type` = 'customer' THEN
		BLOCK1: BEGIN
			DECLARE Done INT DEFAULT FALSE;
			DECLARE InvoiceID INT;
			DECLARE InvoiceAmount DECIMAL(15,5);
			DECLARE InvoiceTaxAmount DECIMAL(15,5);
			DECLARE BadDebtID INT;
			DECLARE BadDebtDate DATE;
			DECLARE Paid DECIMAL(15,5) DEFAULT 0;
			DECLARE Paid_GST DECIMAL(15,5) DEFAULT 0;
			DECLARE IsRecovered INT;
			DECLARE CURSOR_SalesInvoices CURSOR FOR
			SELECT
				comp_sales_invoice.ID,
				comp_sales_invoice.totalamount,
				comp_sales_invoice.totalTaxAmount,
				comp_sales_bad_debt.ID,
				comp_sales_bad_debt.transDate
			FROM
				comp_sales_invoice LEFT JOIN comp_sales_bad_debt
				ON comp_sales_bad_debt.ID = comp_sales_invoice.badDebtID LEFT JOIN comp_journal j1
				ON j1.journalID = CONVERT(comp_sales_invoice.journalNo USING UTF8) AND
				j1.companyID = comp_sales_invoice.companyID LEFT JOIN comp_journal j2
				ON j2.journalID = CONVERT(comp_sales_bad_debt.journalNo USING UTF8) AND
				j2.companyID = comp_sales_bad_debt.companyID
			WHERE
				comp_sales_invoice.companyID = `CompanyID` AND
				comp_sales_invoice.customerID = `EntityID` AND
				(comp_sales_invoice.updateAble = 0 AND comp_sales_invoice.is_delete = 0) AND
				(comp_sales_bad_debt.updateAble = 0 AND comp_sales_bad_debt.is_delete = 0) AND
				(j1.`status` = j1.is_delete = 0) AND
				(j2.`status` = j2.is_delete = 0);
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = TRUE;
			
			SELECT tax_master.ID, tax_master.code
			INTO TaxID, TaxCode
			FROM tax_master
			WHERE tax_master.companyID = `CompanyID` AND tax_master.code = 'AJS';
			
			OPEN CURSOR_SalesInvoices;
			FETCH CURSOR_SalesInvoices INTO InvoiceID, InvoiceAmount, InvoiceTaxAmount, BadDebtID, BadDebtDate;
			read_loop: LOOP
				IF Done THEN
					LEAVE read_loop;
				END IF;
				
				SET Paid_GST = 0;
				SET IsRecovered = 0;
				
				SET Paid = COALESCE((
					SELECT
						CAST(COALESCE(SUM(comp_receive_payment_detail.amountApplied), 0) AS DECIMAL(15,5))
					FROM
						comp_receive_payment_detail LEFT JOIN comp_receive_payment
						ON comp_receive_payment.ID = comp_receive_payment_detail.receivePaymentID LEFT JOIN comp_journal j1
						ON j1.journalID = CONVERT(comp_receive_payment.journalNo USING UTF8) AND
						j1.companyID = comp_receive_payment.companyID
					WHERE
						comp_receive_payment_detail.invoiceID = InvoiceID AND
						(comp_receive_payment.receivePaymentDate BETWEEN BadDebtDate AND RecoveredDate) AND
						(comp_receive_payment.updateAble = 0 AND comp_receive_payment.is_delete = 0) AND
						(j1.`status` = 1 AND j1.is_delete = 0) AND
						comp_receive_payment_detail.debtRecoverID = 0
					GROUP BY
						comp_receive_payment_detail.invoiceID
				), 0);
				
				SET Paid = Paid + COALESCE((
					SELECT
						CAST(COALESCE(SUM(comp_sales_creditnote.totalAmount), 0) AS DECIMAL(15,5))
					FROM
						comp_sales_creditnote LEFT JOIN comp_journal j1
						ON j1.journalID = CONVERT(comp_sales_creditnote.journalNo USING UTF8) AND
						j1.companyID = comp_sales_creditnote.companyID
					WHERE
						comp_sales_creditnote.salesInvoiceID = InvoiceID AND
						(comp_sales_creditnote.creditNoteDate BETWEEN BadDebtDate AND RecoveredDate) AND
						(comp_sales_creditnote.updateAble = 0 AND comp_sales_creditnote.is_delete = 0) AND
						(j1.`status` = 1 AND j1.is_delete = 0) AND
						comp_sales_creditnote.debtRecoverID = 0
					GROUP BY
						comp_sales_creditnote.salesInvoiceID
				), 0);
				
				IF Paid > 0 THEN
					SET Paid_GST = (COALESCE(Paid / InvoiceAmount * InvoiceTaxAmount, 0) );
				END IF;
				
				IF EXISTS(
					SELECT
						*
					FROM
						comp_receive_payment_detail LEFT JOIN comp_receive_payment
						ON comp_receive_payment.ID = comp_receive_payment_detail.receivePaymentID LEFT JOIN comp_journal j1
						ON j1.journalID = CONVERT(comp_receive_payment.journalNo USING UTF8) AND
						j1.companyID = comp_receive_payment.companyID
					WHERE
						comp_receive_payment_detail.invoiceID = InvoiceID AND
						(comp_receive_payment.receivePaymentDate BETWEEN BadDebtDate AND RecoveredDate) AND
						(comp_receive_payment.updateAble = 0 AND comp_receive_payment.is_delete = 0) AND
						(j1.`status` = 1 AND j1.is_delete = 0) AND
						comp_receive_payment_detail.debtRecoverID > 0
				) THEN
					SET IsRecovered = 1;
				END IF;
				
				INSERT INTO Results
				SELECT
					comp_sales_invoice.badDebtID,
					comp_sales_bad_debt.transDate,
					comp_sales_invoice.ID,
					CONCAT(comp_sales_invoice.formNo, '[', LPAD(comp_sales_invoice.serialNo, 8, '0'), ']'),
					comp_sales_invoice.salesInvoiceDate,
					CAST(comp_sales_invoice.totalReceivable AS DECIMAL(15,5)),
					CAST(comp_sales_invoice.totalReceivable / comp_sales_invoice.totalamount * comp_sales_invoice.totalTaxAmount AS DECIMAL(15,5)),
					Paid,
					Paid_GST,
					FLOOR((UNIX_TIMESTAMP(BadDebtDate) - UNIX_TIMESTAMP(comp_sales_invoice.salesInvoiceDate))/60/60/24),
					TaxID,
					TaxCode,
					IsRecovered,
					0
				FROM
					comp_sales_invoice LEFT JOIN comp_sales_bad_debt
					ON comp_sales_bad_debt.ID = comp_sales_invoice.badDebtID
				WHERE
					comp_sales_invoice.ID = InvoiceID;
				FETCH CURSOR_SalesInvoices INTO InvoiceID, InvoiceAmount, InvoiceTaxAmount, BadDebtID, BadDebtDate;
			END LOOP read_loop;
			CLOSE CURSOR_SalesInvoices;
			SET Done = FALSE;
		END BLOCK1;
		BLOCK2: BEGIN
			DECLARE Done INT DEFAULT FALSE;
			DECLARE InvoiceID INT;
			DECLARE InvoiceAmount DECIMAL(15,5);
			DECLARE InvoiceTaxAmount DECIMAL(15,5);
			DECLARE BadDebtID INT;
			DECLARE BadDebtDate DATE;
			DECLARE Paid DECIMAL(15,5) DEFAULT 0;
			DECLARE Paid_GST DECIMAL(15,5) DEFAULT 0;
			DECLARE IsRecovered INT;
			DECLARE CURSOR_HistoricalInvoices CURSOR FOR
			SELECT
				comp_historical_customer_invoice.ID,
				comp_historical_customer_invoice.totalAmount,
				comp_historical_customer_invoice.totalTaxAmount,
				comp_sales_bad_debt.ID,
				comp_sales_bad_debt.transDate
			FROM
				comp_historical_customer_invoice LEFT JOIN comp_sales_bad_debt
				ON comp_sales_bad_debt.ID = comp_historical_customer_invoice.badDebtID
			WHERE
				comp_historical_customer_invoice.companyID = `CompanyID` AND
				comp_historical_customer_invoice.customerID = `EntityID` AND
				comp_historical_customer_invoice.is_delete = 0;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = TRUE;
			
			SELECT tax_master.ID, tax_master.code
			INTO TaxID, TaxCode
			FROM tax_master
			WHERE tax_master.companyID = `CompanyID` AND tax_master.code = 'AJS';
			
			OPEN CURSOR_HistoricalInvoices;
			FETCH CURSOR_HistoricalInvoices INTO InvoiceID, InvoiceAmount, InvoiceTaxAmount, BadDebtID, BadDebtDate;
			read_loop: LOOP
				IF Done THEN
					LEAVE read_loop;
				END IF;
				
				SET Paid_GST = 0;
				SET IsRecovered = 0;
				
				SET Paid = COALESCE((
					SELECT
						CAST(COALESCE(SUM(comp_receive_payment_detail_historical_invoice.amountApplied), 0) AS DECIMAL(15,5))
					FROM
						comp_receive_payment_detail_historical_invoice LEFT JOIN comp_receive_payment
						ON comp_receive_payment.ID = comp_receive_payment_detail_historical_invoice.paymentID LEFT JOIN comp_journal j1
						ON j1.journalID = CONVERT(comp_receive_payment.journalNo USING UTF8) AND
						j1.companyID = comp_receive_payment.companyID
					WHERE
						comp_receive_payment_detail_historical_invoice.historicalinvoiceID = InvoiceID AND
						(comp_receive_payment.receivePaymentDate BETWEEN BadDebtDate AND RecoveredDate) AND
						(comp_receive_payment.updateAble = 0 AND comp_receive_payment.is_delete = 0) AND
						(j1.`status` = 1 AND j1.is_delete = 0) AND
						comp_receive_payment_detail_historical_invoice.debtRecoverID = 0
					GROUP BY
						comp_receive_payment_detail_historical_invoice.historicalinvoiceID
				), 0);
				IF Paid > 0 THEN
					SET Paid_GST = (COALESCE(Paid / InvoiceAmount * InvoiceTaxAmount, 0) );
				END IF;
				
				IF EXISTS(
					SELECT
						*
					FROM
						comp_receive_payment_detail_historical_invoice LEFT JOIN comp_receive_payment
						ON comp_receive_payment.ID = comp_receive_payment_detail_historical_invoice.paymentID LEFT JOIN comp_journal j1
						ON j1.journalID = CONVERT(comp_receive_payment.journalNo USING UTF8) AND
						j1.companyID = comp_receive_payment.companyID
					WHERE
						comp_receive_payment_detail_historical_invoice.historicalinvoiceID = InvoiceID AND
						(comp_receive_payment.receivePaymentDate BETWEEN BadDebtDate AND RecoveredDate) AND
						(comp_receive_payment.updateAble = 0 AND comp_receive_payment.is_delete = 0) AND
						(j1.`status` = 1 AND j1.is_delete = 0) AND
						comp_receive_payment_detail_historical_invoice.debtRecoverID > 0
				) THEN
					SET IsRecovered = 1;
				END IF;
				
				INSERT INTO Results
				SELECT
					comp_historical_customer_invoice.badDebtID,
					comp_sales_bad_debt.transDate,
					comp_historical_customer_invoice.ID,
					comp_historical_customer_invoice.customerPoNo,
					comp_historical_customer_invoice.salesInvoiceDate,
					CAST(comp_historical_customer_invoice.totalReceivable AS DECIMAL(15,5)),
					CAST(comp_historical_customer_invoice.totalReceivable / comp_historical_customer_invoice.totalAmount * comp_historical_customer_invoice.totalTaxAmount AS DECIMAL(15,5)),
					Paid,
					Paid_GST,
					FLOOR((UNIX_TIMESTAMP(BadDebtDate) - UNIX_TIMESTAMP(comp_historical_customer_invoice.salesInvoiceDate))/60/60/24),
					TaxID,
					TaxCode,
					IsRecovered,
					1
				FROM
					comp_historical_customer_invoice LEFT JOIN comp_sales_bad_debt
					ON comp_sales_bad_debt.ID = comp_historical_customer_invoice.badDebtID
				WHERE
					comp_historical_customer_invoice.ID = InvoiceID;
				FETCH CURSOR_HistoricalInvoices INTO InvoiceID, InvoiceAmount, InvoiceTaxAmount, BadDebtID, BadDebtDate;
			END LOOP read_loop;
			CLOSE CURSOR_HistoricalInvoices;
			SET Done = FALSE;
		END BLOCK2;
	ELSEIF `Type` = 'supplier' THEN
		BLOCK1: BEGIN
			DECLARE Done INT DEFAULT FALSE;
			DECLARE InvoiceID INT;
			DECLARE InvoiceAmount DECIMAL(15,5);
			DECLARE InvoiceTaxAmount DECIMAL(15,5);
			DECLARE BadDebtID INT;
			DECLARE BadDebtDate DATE;
			DECLARE Paid DECIMAL(15,5) DEFAULT 0;
			DECLARE Paid_GST DECIMAL(15,5) DEFAULT 0;
			DECLARE IsRecovered INT;
			DECLARE CURSOR_SalesInvoices CURSOR FOR
			SELECT
				comp_supplier_invoice.ID,
				comp_supplier_invoice.totalAmount,
				comp_supplier_invoice.totalTaxAmount,
				comp_purchase_bad_debt.ID,
				comp_purchase_bad_debt.transDate
			FROM
				comp_supplier_invoice LEFT JOIN comp_purchase_bad_debt
				ON comp_purchase_bad_debt.ID = comp_supplier_invoice.badDebtID LEFT JOIN comp_journal j1
				ON j1.journalID = CONVERT(comp_supplier_invoice.journalNo USING UTF8) AND
				j1.companyID = comp_supplier_invoice.companyID LEFT JOIN comp_journal j2
				ON j2.journalID = CONVERT(comp_purchase_bad_debt.journalNo USING UTF8) AND
				j2.companyID = comp_purchase_bad_debt.companyID
			WHERE
				comp_supplier_invoice.companyID = `CompanyID` AND
				comp_supplier_invoice.supplierID = `EntityID` AND
				(comp_supplier_invoice.updateAble = 0 AND comp_supplier_invoice.is_delete = 0) AND
				(comp_purchase_bad_debt.updateAble = 0 AND comp_purchase_bad_debt.is_delete = 0) AND
				(j1.`status` = j1.is_delete = 0) AND
				(j2.`status` = j2.is_delete = 0);
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = TRUE;
			
			SELECT tax_master.ID, tax_master.code
			INTO TaxID, TaxCode
			FROM tax_master
			WHERE tax_master.companyID = `CompanyID` AND tax_master.code = 'AJS';
			
			OPEN CURSOR_SalesInvoices;
			FETCH CURSOR_SalesInvoices INTO InvoiceID, InvoiceAmount, InvoiceTaxAmount, BadDebtID, BadDebtDate;
			read_loop: LOOP
				IF Done THEN
					LEAVE read_loop;
				END IF;
				
				SET Paid_GST = 0;
				SET IsRecovered = 0;
				
				SET Paid = COALESCE((
					SELECT
						CAST(COALESCE(SUM(comp_payment_detail.amountApplied), 0) AS DECIMAL(15,5))
					FROM
						comp_payment_detail LEFT JOIN comp_payment
						ON comp_payment.ID = comp_payment_detail.paymentID LEFT JOIN comp_journal j1
						ON j1.journalID = CONVERT(comp_payment.journalNo USING UTF8) AND
						j1.companyID = comp_payment.companyID
					WHERE
						comp_payment_detail.purchaseInvoiceID = InvoiceID AND
						(comp_payment.receivePaymentDate BETWEEN BadDebtDate AND RecoveredDate) AND
						(comp_payment.updateAble = 0 AND comp_payment.is_delete = 0) AND
						(j1.`status` = 1 AND j1.is_delete = 0) AND
						comp_payment_detail.debtRecoverID = 0
					GROUP BY
						comp_payment_detail.purchaseInvoiceID
				), 0);
				
				SET Paid = Paid + COALESCE((
					SELECT
						CAST(COALESCE(SUM(comp_supplier_debitnote.totalAmount), 0) AS DECIMAL(15,5))
					FROM
						comp_supplier_debitnote LEFT JOIN comp_journal j1
						ON j1.journalID = CONVERT(comp_supplier_debitnote.journalNo USING UTF8) AND
						j1.companyID = comp_supplier_debitnote.companyID
					WHERE
						comp_supplier_debitnote.supplierInvoiceID = InvoiceID AND
						(comp_supplier_debitnote.debitNoteDate BETWEEN BadDebtDate AND RecoveredDate) AND
						(comp_supplier_debitnote.updateAble = 0 AND comp_supplier_debitnote.is_delete = 0) AND
						(j1.`status` = 1 AND j1.is_delete = 0) AND
						comp_supplier_debitnote.debtRecoverID = 0
					GROUP BY
						comp_supplier_debitnote.supplierInvoiceID
				), 0);
				
				IF Paid > 0 THEN
					SET Paid_GST = (COALESCE(Paid / InvoiceAmount * InvoiceTaxAmount, 0) );
				END IF;
				
				IF EXISTS(
					SELECT
						*
					FROM
						comp_receive_payment_detail LEFT JOIN comp_receive_payment
						ON comp_receive_payment.ID = comp_receive_payment_detail.receivePaymentID LEFT JOIN comp_journal j1
						ON j1.journalID = CONVERT(comp_receive_payment.journalNo USING UTF8) AND
						j1.companyID = comp_receive_payment.companyID
					WHERE
						comp_receive_payment_detail.invoiceID = InvoiceID AND
						(comp_receive_payment.receivePaymentDate BETWEEN BadDebtDate AND RecoveredDate) AND
						(comp_receive_payment.updateAble = 0 AND comp_receive_payment.is_delete = 0) AND
						(j1.`status` = 1 AND j1.is_delete = 0) AND
						comp_receive_payment_detail.debtRecoverID > 0
				) THEN
					SET IsRecovered = 1;
				END IF;
				
				INSERT INTO Results
				SELECT
					comp_supplier_invoice.badDebtID,
					comp_purchase_bad_debt.transDate,
					comp_supplier_invoice.ID,
					CONCAT(comp_supplier_invoice.formNo, '[', LPAD(comp_supplier_invoice.serialNo, 8, '0'), ']'),
					comp_supplier_invoice.invoiceDate,
					CAST(comp_supplier_invoice.totalPayable AS DECIMAL(15,5)),
					CAST(comp_supplier_invoice.totalPayable / comp_supplier_invoice.totalAmount * comp_supplier_invoice.totalTaxAmount AS DECIMAL(15,5)),
					Paid,
					Paid_GST,
					FLOOR((UNIX_TIMESTAMP(BadDebtDate) - UNIX_TIMESTAMP(comp_supplier_invoice.invoiceDate))/60/60/24),
					TaxID,
					TaxCode,
					IsRecovered,
					0
				FROM
					comp_supplier_invoice LEFT JOIN comp_purchase_bad_debt
					ON comp_purchase_bad_debt.ID = comp_supplier_invoice.badDebtID
				WHERE
					comp_supplier_invoice.ID = InvoiceID;
				FETCH CURSOR_SalesInvoices INTO InvoiceID, InvoiceAmount, InvoiceTaxAmount, BadDebtID, BadDebtDate;
			END LOOP read_loop;
			CLOSE CURSOR_SalesInvoices;
			SET Done = FALSE;
		END BLOCK1;
		BLOCK2: BEGIN
			DECLARE Done INT DEFAULT FALSE;
			DECLARE InvoiceID INT;
			DECLARE InvoiceAmount DECIMAL(15,5);
			DECLARE InvoiceTaxAmount DECIMAL(15,5);
			DECLARE BadDebtID INT;
			DECLARE BadDebtDate DATE;
			DECLARE Paid DECIMAL(15,5) DEFAULT 0;
			DECLARE Paid_GST DECIMAL(15,5) DEFAULT 0;
			DECLARE IsRecovered INT;
			DECLARE CURSOR_HistoricalInvoices CURSOR FOR
			SELECT
				comp_historical_supplier_invoice.ID,
				comp_historical_supplier_invoice.totalAmount,
				comp_historical_supplier_invoice.totalTaxAmount,
				comp_purchase_bad_debt.ID,
				comp_purchase_bad_debt.transDate
			FROM
				comp_historical_supplier_invoice LEFT JOIN comp_purchase_bad_debt
				ON comp_purchase_bad_debt.ID = comp_historical_supplier_invoice.badDebtID
			WHERE
				comp_historical_supplier_invoice.companyID = `CompanyID` AND
				comp_historical_supplier_invoice.supplierID = `EntityID` AND
				comp_historical_supplier_invoice.is_delete = 0;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = TRUE;
			
			SELECT tax_master.ID, tax_master.code
			INTO TaxID, TaxCode
			FROM tax_master
			WHERE tax_master.companyID = `CompanyID` AND tax_master.code = 'AJP';
			
			OPEN CURSOR_HistoricalInvoices;
			FETCH CURSOR_HistoricalInvoices INTO InvoiceID, InvoiceAmount, InvoiceTaxAmount, BadDebtID, BadDebtDate;
			read_loop: LOOP
				IF Done THEN
					LEAVE read_loop;
				END IF;
				
				SET Paid_GST = 0;
				SET IsRecovered = 0;
				
				SET Paid = COALESCE((
					SELECT
						CAST(COALESCE(SUM(comp_payment_detail_historical_invoice.amountApplied), 0) AS DECIMAL(15,5))
					FROM
						comp_payment_detail_historical_invoice LEFT JOIN comp_payment
						ON comp_payment.ID = comp_payment_detail_historical_invoice.paymentID LEFT JOIN comp_journal j1
						ON j1.journalID = CONVERT(comp_payment.journalNo USING UTF8) AND
						j1.companyID = comp_payment.companyID
					WHERE
						comp_payment_detail_historical_invoice.historicalinvoiceID = InvoiceID AND
						(comp_payment.paymentDate BETWEEN BadDebtDate AND RecoveredDate) AND
						(comp_payment.updateAble = 0 AND comp_payment.is_delete = 0) AND
						(j1.`status` = 1 AND j1.is_delete = 0) AND
						comp_payment_detail_historical_invoice.debtRecoverID = 0
					GROUP BY
						comp_payment_detail_historical_invoice.historicalinvoiceID
				), 0);
				IF Paid > 0 THEN
					SET Paid_GST = (COALESCE(Paid / InvoiceAmount * InvoiceTaxAmount, 0) );
				END IF;
				
				IF EXISTS(
					SELECT
						CAST(COALESCE(SUM(comp_payment_detail_historical_invoice.amountApplied), 0) AS DECIMAL(15,5))
					FROM
						comp_payment_detail_historical_invoice LEFT JOIN comp_payment
						ON comp_payment.ID = comp_payment_detail_historical_invoice.paymentID LEFT JOIN comp_journal j1
						ON j1.journalID = CONVERT(comp_payment.journalNo USING UTF8) AND
						j1.companyID = comp_payment.companyID
					WHERE
						comp_payment_detail_historical_invoice.historicalinvoiceID = InvoiceID AND
						(comp_payment.paymentDate BETWEEN BadDebtDate AND RecoveredDate) AND
						(comp_payment.updateAble = 0 AND comp_payment.is_delete = 0) AND
						(j1.`status` = 1 AND j1.is_delete = 0) AND
						comp_payment_detail_historical_invoice.debtRecoverID > 0
				) THEN
					SET IsRecovered = 1;
				END IF;
				
				INSERT INTO Results
				SELECT
					comp_historical_supplier_invoice.badDebtID,
					comp_purchase_bad_debt.transDate,
					comp_historical_supplier_invoice.ID,
					comp_historical_supplier_invoice.supplierInvoiceNo,
					comp_historical_supplier_invoice.invoiceDate,
					CAST(comp_historical_supplier_invoice.totalPayable AS DECIMAL(15,5)),
					CAST(comp_historical_supplier_invoice.totalPayable / comp_historical_supplier_invoice.totalAmount * comp_historical_supplier_invoice.totalTaxAmount AS DECIMAL(15,5)),
					Paid,
					Paid_GST,
					FLOOR((UNIX_TIMESTAMP(BadDebtDate) - UNIX_TIMESTAMP(comp_historical_supplier_invoice.invoiceDate))/60/60/24),
					TaxID,
					TaxCode,
					IsRecovered,
					1
				FROM
					comp_historical_supplier_invoice LEFT JOIN comp_purchase_bad_debt
					ON comp_purchase_bad_debt.ID = comp_historical_supplier_invoice.badDebtID
				WHERE
					comp_historical_supplier_invoice.ID = InvoiceID;
				FETCH CURSOR_HistoricalInvoices INTO InvoiceID, InvoiceAmount, InvoiceTaxAmount, BadDebtID, BadDebtDate;
			END LOOP read_loop;
			CLOSE CURSOR_HistoricalInvoices;
			SET Done = FALSE;
		END BLOCK2;
	END IF;
	SELECT
		*,
		DATE_FORMAT(BadDebtDate, '%d-%m-%Y') AS BadDebt_Date
	FROM
		Results
	WHERE
		Paid > 0
	ORDER BY InvoiceDate ASC;
END

//