History Log Quick Report

Here is an example of a Quick Report query that assembles all changes into specific records:

SELECT "Change ID", "Timestamp", "Change Type", "Changed By", 
    SUBSTR("Address ID", 2, LENGTH("Address ID") - 2) AS "Address ID",
    SUBSTR("Company", 2, LENGTH("Company") - 2) AS "Company",
    SUBSTR("Payment Method", 2, LENGTH("Payment Method") - 2) AS "Payment Method",
    SUBSTR("Supplier ID", 2, LENGTH("Supplier ID") - 2) AS "Supplier ID",
    NVL(SI.Name, '(Supplier Not Found)') AS "Supplier Name", "Changes"
    FROM
    (SELECT HL.Log_Id AS "Change ID", TO_CHAR(HL.Time_Stamp, 'YYYY-MM-DD HH:MM:SS') AS "Timestamp", HL.History_Type AS "Change Type", 
        HL.Username AS "Changed By", REGEXP_SUBSTR(Keys, '\=(.*?)\^', 1, 1) AS "Address ID", 
        REGEXP_SUBSTR(Keys, '\=(.*?)\^', 1, 2) AS "Company", REGEXP_SUBSTR(Keys, '\=(.*?)\^', 1, 3) AS "Supplier ID",
        REGEXP_SUBSTR(Keys, '\=(.*?)\^', 1, 5) AS "Payment Method", HLA.Changes AS "Changes"
        FROM History_Log HL INNER JOIN
            (SELECT Log_Id,
                LISTAGG(Column_Name || ': From {' || Old_Value || '} to {' || New_Value || '}', CHR(10))
                WITHIN GROUP (ORDER BY Column_Name) AS Changes FROM History_Log_Attribute GROUP BY Log_Id) HLA ON HL.Log_Id = HLA.Log_Id
        WHERE Module = 'PAYLED'
        AND LU_Name = 'PaymentAddress'
        AND Table_Name = 'PAYMENT_ADDRESS_TAB'
        AND Keys LIKE '%PARTY_TYPE=SUPPLIER%'
        AND TRUNC(HL.Time_Stamp) BETWEEN TO_DATE('&From', 'MM/DD/YY') AND TO_DATE('&To', 'MM/DD/YY') 
        ORDER BY HL.Time_Stamp) H
    LEFT OUTER JOIN Supplier_Info SI ON SUBSTR(H."Supplier ID", 2, LENGTH(H."Supplier ID") - 2) = SI.Supplier_ID

This is specific to changes to the PaymentAddress LU, so a lot of the query is specific to that. It also uses LISTAGG() to put all changes into one field. Works fairly well.

Hopefully this will be helpful as you create a query, though you may not need it to be this complicated…

Good luck,

Joe Kaufman