CREATE OR REPLACE VIEW `view_journal_table`
AS
SELECT DISTINCT
`a`.`ID` ,`a`.`companyID` ,`a`.`referenceNo` AS `referenceNo`, 'comp_cash_sales' AS `tableName`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo`
FROM
(`comp_cash_sales` `a` JOIN `comp_journal` `b`)
WHERE
((CONVERT(`a`.`journalNo` USING utf8) = `b`.`journalID`) AND (`a`.`is_delete` = 0) AND a.companyID = b.companyID)
UNION
SELECT DISTINCT
`a`.`ID` ,`a`.`companyID` ,`a`.`referenceNo` AS `referenceNo`, 'comp_payment' AS `tableName`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo`
FROM
(`comp_payment` `a` JOIN `comp_journal` `b`)
WHERE
((CONVERT(`a`.`journalNo` USING utf8) = `b`.`journalID`) AND (`a`.`is_delete` = 0) AND a.companyID = b.companyID)
UNION
SELECT DISTINCT
`a`.`ID` ,`a`.`companyID` ,`a`.`referenceNo` AS `referenceNo`, 'comp_payment_others' AS `tableName`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo`
FROM
(`comp_payment_others` `a` JOIN `comp_journal` `b`)
WHERE
((CONVERT(`a`.`journalNo` USING utf8) = `b`.`journalID`) AND (`a`.`is_delete` = 0) AND a.companyID = b.companyID)
UNION
SELECT DISTINCT
`a`.`ID` ,`a`.`companyID` ,`a`.`formNo` AS `referenceNo`, 'comp_purchase_order' AS `tableName`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo`
FROM
(`comp_purchase_order` `a` JOIN `comp_journal` `b`)
WHERE
((CONVERT(`a`.`journalNo` USING utf8) = `b`.`journalID`) AND (`a`.`is_delete` = 0) AND a.companyID = b.companyID)
UNION
SELECT DISTINCT
`a`.`ID` ,`a`.`companyID` ,`a`.`referenceNo` AS `referenceNo`, 'comp_receive_payment' AS `tableName`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo`
FROM
(`comp_receive_payment` `a` JOIN `comp_journal` `b`)
WHERE
((CONVERT(`a`.`journalNo` USING utf8) = `b`.`journalID`) AND (`a`.`is_delete` = 0) AND a.companyID = b.companyID)
UNION
SELECT DISTINCT
`a`.`ID` ,`a`.`companyID` ,`a`.`referenceNo` AS `referenceNo`, 'comp_receive_payment_others' AS `tableName`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo`
FROM
(`comp_receive_payment_others` `a` JOIN `comp_journal` `b`)
WHERE
((CONVERT(`a`.`journalNo` USING utf8) = `b`.`journalID`) AND (`a`.`is_delete` = 0) AND a.companyID = b.companyID)
UNION
SELECT DISTINCT
`a`.`ID` ,`a`.`companyID` ,`b`.`formNo` AS `referenceNo`, 'comp_sales_creditnote' AS `tableName`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo`
FROM
(`comp_sales_creditnote` `a` JOIN `comp_journal` `b`)
WHERE
((CONVERT(`a`.`journalNo` USING utf8) = `b`.`journalID`) AND (`a`.`is_delete` = 0) AND a.companyID = b.companyID)
UNION
SELECT DISTINCT
`a`.`ID` ,`a`.`companyID` ,`b`.`formNo` AS `referenceNo`, 'comp_sales_debitnote' AS `tableName`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo`
FROM
(`comp_sales_debitnote` `a` JOIN `comp_journal` `b`)
WHERE
((CONVERT(`a`.`journalNo` USING utf8) = `b`.`journalID`) AND (`a`.`is_delete` = 0) AND a.companyID = b.companyID)
UNION
SELECT DISTINCT
`a`.`ID` ,`a`.`companyID` ,`a`.`customerPoNo` AS `referenceNo`, 'comp_sales_invoice' AS `tableName`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo`
FROM
(`comp_sales_invoice` `a` JOIN `comp_journal` `b`)
WHERE
((CONVERT(`a`.`journalNo` USING utf8) = `b`.`journalID`) AND (`a`.`is_delete` = 0) AND a.companyID = b.companyID)
UNION
SELECT DISTINCT
`a`.`ID` ,`a`.`companyID` ,`b`.`formNo` AS `referenceNo`, 'comp_supplier_creditnote' AS `tableName`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo`
FROM
(`comp_supplier_creditnote` `a` JOIN `comp_journal` `b`)
WHERE
((CONVERT(`a`.`journalNo` USING utf8) = `b`.`journalID`) AND (`a`.`is_delete` = 0) AND a.companyID = b.companyID)
UNION
SELECT DISTINCT
`a`.`ID` ,`a`.`companyID` ,`b`.`formNo` AS `referenceNo`, 'comp_supplier_debitnote' AS `tableName`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo`
FROM
(`comp_supplier_debitnote` `a` JOIN `comp_journal` `b`)
WHERE
((CONVERT(`a`.`journalNo` USING utf8) = `b`.`journalID`) AND (`a`.`is_delete` = 0) AND a.companyID = b.companyID)
UNION
SELECT DISTINCT
`a`.`ID` ,`a`.`companyID` ,`a`.`supplierInvoiceNo` AS `referenceNo`, 'comp_supplier_invoice' AS `tableName`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo`
FROM
(`comp_supplier_invoice` `a` JOIN `comp_journal` `b`)
WHERE
((CONVERT(`a`.`journalNo` USING utf8) = `b`.`journalID`) AND (`a`.`is_delete` = 0) AND a.companyID = b.companyID)
UNION
SELECT DISTINCT
`a`.`ID` ,`a`.`companyID` ,`b`.`formNo` AS `referenceNo`, 'comp_delivery_order' AS `tableName`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo`
FROM
(`comp_delivery_order` `a` JOIN `comp_journal` `b`)
WHERE
(CONVERT(`a`.`journalNo` USING utf8) = `b`.`journalID` AND a.companyID = b.companyID)
UNION
SELECT DISTINCT
`a`.`ID` ,`a`.`companyID` ,`a`.`reference` AS `referenceNo`, 'comp_gst_transaction' AS `tableName`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo`
FROM
(`comp_gst_transaction` `a` JOIN `comp_journal` `b`)
WHERE
(CONVERT(`a`.`journalNo` USING utf8) = `b`.`journalID` AND a.companyID = b.companyID)
UNION
SELECT DISTINCT
`a`.`ID` ,`a`.`companyID` ,`a`.`supplierDoNo` AS `referenceNo`, 'comp_receive_item' AS `tableName`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo`
FROM
(`comp_receive_item` `a` JOIN `comp_journal` `b`)
WHERE
(CONVERT(`a`.`journalNo` USING utf8) = `b`.`journalID` AND a.companyID = b.companyID)
UNION
SELECT DISTINCT
`a`.`ID` ,`a`.`companyID` ,`c`.`name` AS `referenceNo`, 'zakat_summary' AS `tableName`,`b`.`journalID` AS `journalID`,`b`.`formNo` AS `formNo`
FROM
(`zakat_summary` `a` ,`comp_journal` `b`, zakat_center c)
WHERE
(CONVERT(`a`.`journalNo` USING utf8) = `b`.`journalID` AND a.centerID = c.ID AND a.companyID = b.companyID)
UNION
SELECT DISTINCT
`a`.`ID`, `a`.`companyID`, '' AS `referenceNo`, 'comp_sales_bad_debt' AS `tableName`, `b`.`journalID` AS `journalID`, `b`.`formNo` AS `formNo`
FROM
(`comp_sales_bad_debt` `a` JOIN `comp_journal` `b`)
WHERE
(CONVERT(`a`.`journalNo` USING UTF8) = `b`.`journalID` AND `a`.`companyID` = `b`.`companyID`)
UNION
SELECT DISTINCT
`a`.`ID`, `a`.`companyID`, '' AS `referenceNo`, 'comp_sales_debt_recover' AS `tableName`, `b`.`journalID` AS `journalID`, `b`.`formNo` AS `formNo`
FROM
(`comp_sales_debt_recover` `a` JOIN `comp_journal` `b`)
WHERE
(CONVERT(`a`.`journalNo` USING UTF8) = `b`.`journalID` AND `a`.`companyID` = `b`.`companyID`)
UNION
SELECT DISTINCT
`a`.`ID`, `a`.`companyID`, '' AS `referenceNo`, 'comp_purchase_bad_debt' AS `tableName`, `b`.`journalID` AS `journalID`, `b`.`formNo` AS `formNo`
FROM
(`comp_purchase_bad_debt` `a` JOIN `comp_journal` `b`)
WHERE
(CONVERT(`a`.`journalNo` USING UTF8) = `b`.`journalID` AND `a`.`companyID` = `b`.`companyID`)
UNION
SELECT DISTINCT
`a`.`ID`, `a`.`companyID`, '' AS `referenceNo`, 'comp_purchase_debt_recover' AS `tableName`, `b`.`journalID` AS `journalID`, `b`.`formNo` AS `formNo`
FROM
(`comp_purchase_debt_recover` `a` JOIN `comp_journal` `b`)
WHERE
(CONVERT(`a`.`journalNo` USING UTF8) = `b`.`journalID` AND `a`.`companyID` = `b`.`companyID`);