[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 #26 from Andrew Isherwood <andrew.isherwood at ptfs-europe.com> ---
Revised reports:

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>>

2. 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(JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.adjustment')), 2) AS 'Amount',
    ia.note AS 'Note',
    CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.adjustment')) = 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 JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_id')) =
f.budget_id
LEFT JOIN authorised_values av ON av.category = 'ADJ_REASON' AND
av.authorised_value = JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.reason'))
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(JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.adjustment_old')), 2) AS
'Amount before',
    FORMAT(JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.adjustment')), 2) AS 'Amount
after',
    ia.note AS 'Note',
    CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.encumber_open_old')) = 0
THEN 'No' ELSE 'Yes' END AS 'Encumber open before',
    CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.encumber_open')) = 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 JSON_UNQUOTE(JSON_EXTRACT(a.info,
'$.budget_id_old')) = f_before.budget_id
LEFT JOIN aqbudgets f_after ON JSON_UNQUOTE(JSON_EXTRACT(a.info,
'$.budget_id')) = f_after.budget_id
LEFT JOIN authorised_values av_before ON av_before.category = 'ADJ_REASON' AND
av_before.authorised_value = JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.reason_old'))
LEFT JOIN authorised_values av_after ON av_after.category = 'ADJ_REASON' AND
av_after.authorised_value = JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.reason'))
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(JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.adjustment')), 2) AS 'Amount',
    CASE WHEN JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.encumber_open')) = 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'
FROM
    action_logs a
INNER JOIN borrowers b ON a.user = b.borrowernumber
INNER JOIN aqinvoices i ON i.invoiceid = JSON_UNQUOTE(JSON_EXTRACT(a.info,
'$.invoiceid'))
LEFT JOIN aqbudgets f ON JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_id')) =
f.budget_id
LEFT JOIN authorised_values av ON av.category = 'ADJ_REASON' AND
av.authorised_value = JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.reason'))
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(JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.quantityrec')), 0) AS 'Quantity
received',
    f.budget_name AS 'Fund',
    FORMAT(JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.tax_rate')), 2) AS 'Tax rate',
    FORMAT(JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.replacementprice')), 2) AS
'Replacement price',
    FORMAT(JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.unitprice')), 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 JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.bookfund')) =
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
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_period_startdate_old')) AS
'Budget start before',
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_period_startdate')) AS 'Budget
start after',
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_period_enddate_old')) AS
'Budget end before',
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_period_enddate')) AS 'Budget
end after',
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_period_total_old')) AS 'Total
amount before',
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_period_total')) AS 'Total
amount after',
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_period_total_change')) 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',
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_amount_old')) AS 'Amount
before',
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_amount_new')) AS 'Amount
after',
        JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_encumb_old')) AS 'Warn at %
before',
        JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_encumb_new')) AS 'Warn at %
after',
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_expend_old')) AS 'Warn at
amount before',
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_expend_new')) AS 'Warn at
amount after',
    JSON_UNQUOTE(JSON_EXTRACT(a.info, '$.budget_amount_change')) 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.