https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24190
--- Comment #3 from Andrew Isherwood <andrew.isherw...@ptfs-europe.com> --- Associated reports: 1. Order line cancellations SELECT o.ordernumber AS 'Order number', ba.basketname AS 'Basket', f.budget_name AS 'Fund', CONCAT(b.firstname, ' ', b.surname) AS 'Cancelled by', a.timestamp AS 'Cancelled timestamp' FROM action_logs a INNER JOIN borrowers b ON a.user = b.borrowernumber INNER JOIN aqorders o ON a.object = o.ordernumber INNER JOIN aqbasket ba ON o.basketno = ba.basketno LEFT JOIN aqbudgets f ON o.budget_id = f.budget_id WHERE a.module='ACQUISITIONS' AND a.action = 'CANCEL_ORDER' AND date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>> 2. Order line creations SELECT o.ordernumber AS 'Order number', ba.basketname AS 'Basket', f.budget_name AS 'Fund', CONCAT(b.firstname, ' ', b.surname) AS 'Created by', a.timestamp AS 'Created timestamp' FROM action_logs a INNER JOIN borrowers b ON a.user = b.borrowernumber INNER JOIN aqorders o ON a.object = o.ordernumber INNER JOIN aqbasket ba ON o.basketno = ba.basketno LEFT JOIN aqbudgets f ON o.budget_id = f.budget_id WHERE a.module='ACQUISITIONS' AND a.action = 'CREATE_ORDER' AND date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>> 3. Invoice adjustment additions SELECT f.budget_name AS 'Fund', i.invoicenumber, FORMAT(SUBSTRING(a.info, 1, 10), 2) AS 'Amount', ia.note AS 'Note', CASE WHEN SUBSTRING(a.info, 101, 10) = 0 THEN 'No' ELSE 'Yes' END AS 'Encumber open', av.lib AS 'Reason', CONCAT(b.firstname, ' ', b.surname) AS 'Created by', a.timestamp AS 'Created timestamp' FROM action_logs a INNER JOIN borrowers b ON a.user = b.borrowernumber INNER JOIN aqinvoice_adjustments ia ON a.object = ia.adjustment_id INNER JOIN aqinvoices i ON i.invoiceid = ia.invoiceid LEFT JOIN aqbudgets f ON SUBSTRING(a.info, 91, 10) = f.budget_id LEFT JOIN authorised_values av ON av.category = 'ADJ_REASON' AND av.authorised_value = TRIM(LEADING ' ' FROM SUBSTRING(a.info, 11, 80)) WHERE a.module='ACQUISITIONS' AND a.action = 'CREATE_INVOICE_ADJUSTMENT' AND date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>> 4. Invoice adjustment amendments SELECT i.invoicenumber AS 'Invoice number', f_before.budget_name AS 'Fund before', f_after.budget_name AS 'Fund after', FORMAT(SUBSTRING(a.info, 131, 10), 2) AS 'Amount before', FORMAT(SUBSTRING(a.info, 1, 10), 2) AS 'Amount after', ia.note AS 'Note', CASE WHEN SUBSTRING(a.info, 121, 10) = 0 THEN 'No' ELSE 'Yes' END AS 'Encumber open before', CASE WHEN SUBSTRING(a.info, 101, 10) = 0 THEN 'No' ELSE 'Yes' END AS 'Encumber open after', av_before.lib AS 'Reason before', av_after.lib AS 'Reason after', CONCAT(b.firstname, ' ', b.surname) AS 'Created by', a.timestamp AS 'Created timestamp' FROM action_logs a INNER JOIN borrowers b ON a.user = b.borrowernumber INNER JOIN aqinvoice_adjustments ia ON a.object = ia.adjustment_id INNER JOIN aqinvoices i ON i.invoiceid = ia.invoiceid LEFT JOIN aqbudgets f_before ON SUBSTRING(a.info, 111, 10) = f_before.budget_id LEFT JOIN aqbudgets f_after ON SUBSTRING(a.info, 91, 10) = f_after.budget_id LEFT JOIN authorised_values av_before ON av_before.category = 'ADJ_REASON' AND av_before.authorised_value = TRIM(LEADING ' ' FROM SUBSTRING(a.info, 141, 80)) LEFT JOIN authorised_values av_after ON av_after.category = 'ADJ_REASON' AND av_after.authorised_value = TRIM(LEADING ' ' FROM SUBSTRING(a.info, 11, 80)) WHERE a.module='ACQUISITIONS' AND a.action = 'UPDATE_INVOICE_ADJUSTMENT' AND date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>> 5. Invoice adjustment deletions SELECT i.invoicenumber AS 'Invoice number', f.budget_name AS 'Fund', FORMAT(SUBSTRING(a.info, 31, 10), 2) AS 'Amount', CASE WHEN SUBSTRING(a.info, 21, 10) = 0 THEN 'No' ELSE 'Yes' END AS 'Encumber open', av.lib AS 'Reason', CONCAT(b.firstname, ' ', b.surname) AS 'Deleted by', a.timestamp AS 'Deleted timestamp', SUBSTRING(a.info, 31, 10) FROM action_logs a INNER JOIN borrowers b ON a.user = b.borrowernumber INNER JOIN aqinvoices i ON i.invoiceid = SUBSTRING(a.info, 1, 10) LEFT JOIN aqbudgets f ON SUBSTRING(a.info, 11, 10) = f.budget_id LEFT JOIN authorised_values av ON av.category = 'ADJ_REASON' AND av.authorised_value = TRIM(LEADING ' ' FROM SUBSTRING(a.info, 41, 80)) WHERE a.module='ACQUISITIONS' AND a.action = 'DELETE_INVOICE_ADJUSTMENT' AND date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>> 6. Order line receipts against an invoice SELECT o.ordernumber AS 'Order number', FORMAT(SUBSTRING(a.info, 1, 10), 0) AS 'Quantity received', f.budget_name AS 'Fund', FORMAT(SUBSTRING(a.info, 21, 10), 2) AS 'Tax rate', FORMAT(SUBSTRING(a.info, 31, 10), 2) AS 'Replacement price', FORMAT(SUBSTRING(a.info, 41, 10), 2) AS 'Actual cost', CONCAT(b.firstname, ' ', b.surname) AS 'Received by', a.timestamp AS 'Received timestamp' FROM action_logs a INNER JOIN borrowers b ON a.user = b.borrowernumber INNER JOIN aqorders o ON a.object = o.ordernumber LEFT JOIN aqbudgets f ON SUBSTRING(a.info, 11, 10) = f.budget_id WHERE a.module='ACQUISITIONS' AND a.action = 'RECEIVE_ORDER' AND date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>> 7. Budget Adjustments SELECT SUBSTR(a.info, 31, 10) AS 'Budget start before', SUBSTR(a.info, 1, 10) AS 'Budget start after', SUBSTR(a.info, 41, 10) AS 'Budget end before', SUBSTR(a.info, 11, 10) AS 'Budget end after', FORMAT(SUBSTR(a.info, 51, 10), 2) AS 'Total amount before', FORMAT(SUBSTR(a.info, 21, 10), 2) AS 'Total amount after', FORMAT(SUBSTR(a.info, 61, 10), 2) AS 'Difference', CONCAT(b.firstname, ' ', b.surname) AS 'Modified by', a.timestamp AS 'Modified timestamp' FROM action_logs a INNER JOIN borrowers b ON a.user = b.borrowernumber WHERE a.module='ACQUISITIONS' AND a.action = 'MODIFY_BUDGET' AND date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>> 8. Fund adjustments SELECT a.object AS 'Fund ID', FORMAT(SUBSTR(a.info, 31, 10), 2) AS 'Amount before', FORMAT(SUBSTR(a.info, 1, 10) ,2 ) AS 'Amount after', FORMAT(SUBSTR(a.info, 41, 10), 2) AS 'Warn at % before', FORMAT(SUBSTR(a.info, 11, 10), 2) AS 'Warn at % after', FORMAT(SUBSTR(a.info, 51, 10), 2) AS 'Warn at amount before', FORMAT(SUBSTR(a.info, 21, 10), 2) AS 'Warn at amount after', FORMAT(SUBSTR(a.info, 61, 10), 2) AS 'Difference', CONCAT(b.firstname, ' ', b.surname) AS 'Modified by', a.timestamp AS 'Modified timestamp' FROM action_logs a INNER JOIN borrowers b ON a.user = b.borrowernumber WHERE a.module='ACQUISITIONS' AND a.action = 'MODIFY_FUND' AND date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>> 9. Order release date (1) SELECT ba.basketname AS "Basket name", bs.name AS "Vendor name", bi.title AS 'Title', bi.author AS 'Author', o.quantity AS 'Quantity', FORMAT(o.listprice, 2) AS 'Vendor price', FORMAT(o.rrp, 2) AS 'Retail price', FORMAT(o.ecost, 2) AS 'Budgeted cost', FORMAT(o.unitprice, 2) AS 'Actual cost', bu.budget_name AS 'Fund', CONCAT(b.firstname, ' ', b.surname) AS 'Basket closed by', a.timestamp AS 'Basket closed timestamp' FROM action_logs a INNER JOIN borrowers b ON a.user = b.borrowernumber INNER JOIN aqbasket ba ON a.object = ba.basketno INNER JOIN aqorders o ON o.basketno = ba.basketno INNER JOIN aqbooksellers bs ON ba.booksellerid = bs.id LEFT JOIN aqbudgets bu ON bu.budget_id = o.budget_id LEFT JOIN biblio bi ON bi.biblionumber = o.biblionumber WHERE a.module='ACQUISITIONS' AND a.action = 'CLOSE_BASKET' AND date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>> AND a.object = <<Basket ID>> 10. Order release date (2) SELECT o.ordernumber AS 'Order number', b.basketname AS 'Basket', bu.budget_name AS 'Fund', bi.title AS 'Title', bi.author AS 'Author', CONCAT(bo.firstname, ' ', bo.surname) AS 'Sent by', e.transfer_date AS 'Sent date' FROM edifact_messages e LEFT JOIN aqbasket b ON e.basketno = b.basketno LEFT JOIN aqorders o ON o.basketno = b.basketno LEFT JOIN action_logs a ON e.basketno = a.object LEFT JOIN biblio bi ON o.biblionumber = bi.biblionumber LEFT JOIN borrowers bo ON a.user = bo.borrowernumber INNER JOIN aqbudgets bu ON o.budget_id = bu.budget_id WHERE e.message_type='ORDER' AND e.status = 'Sent' AND a.module = 'ACQUISITIONS' AND a.action = 'APPROVE_BASKET' AND date(e.transfer_date) BETWEEN <<Starting timestamp|date>> AND <<Ending timestamp|date>> AND e.basketno = <<Basket ID>> -- You are receiving this mail because: You are watching all bug changes. _______________________________________________ Koha-bugs mailing list Koha-bugs@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/