[Koha-bugs] [Bug 24190] Add additional Acquisition logging

[bugzilla-daemon at bugs.koha-community.org]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=24190

--- Comment #4 from Andrew Isherwood <andrew.isherwood at ptfs-europe.com> ---
1. Order line cancellations:
----------------------------
SELECT
    o.ordernumber AS 'Order number',
    bi.title AS 'Title',
    bi.author AS 'Author',
    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
LEFT JOIN biblio bi ON o.biblionumber = bi.biblionumber
WHERE
    a.module='ACQUISITIONS' AND
    a.action = 'CANCEL_ORDER' AND
    date(a.timestamp) BETWEEN <<Starting timestamp|date>> AND <<Ending
timestamp|date>>

Order line creations:
---------------------
SELECT
    o.ordernumber AS 'Order number',
    ba.basketname AS 'Basket',
    bi.title AS 'Title',
    bi.author AS 'Author',
    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
LEFT JOIN biblio bi ON o.biblionumber = bi.biblionumber
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.