I have the query below. What else I need to consider to match the result in the Standard Report?
SELECT A.item AS "ITEM NAME", SUM(A.amount) AS "INV. VALUE", SUM(A.item_count) AS "ON HAND" FROM (
SELECT Transactions.trandate, Subsidiaries.name AS "subsidiary", Items.item_id, Items.name AS "item", Transaction_lines.amount, Transaction_lines.item_count FROM Transaction_lines
INNER JOIN Subsidiaries ON Subsidiaries.subsidiary_id = Transaction_lines.subsidiary_id
INNER JOIN Transactions ON Transactions.transaction_id = Transaction_lines.transaction_id
INNER JOIN Items ON Items.item_id = Transaction_lines.item_id
INNER JOIN Accounts ON Accounts.account_id = Transaction_lines.account_id
LEFT JOIN (
SELECT Transactions.transaction_id, Transactions.transaction_type FROM TRANSACTIONS
) Created_from ON Created_from.transaction_id = Transactions.created_from_id
WHERE
Accounts.type_name IN ('Other Current Asset')
AND Transactions.is_non_posting = 'No'
AND (Transactions.transaction_type IN ('Inventory Adjustment', 'Inventory Worksheet', 'Item Fulfillment', 'Item Receipt', 'Assembly Build', 'Assembly Unbuild'))
UNION ALL
SELECT Transactions.trandate, Subsidiaries.name AS "subsidiary", Items.item_id, Items.name AS "item", Transaction_lines.amount, Transaction_lines.item_count FROM Transaction_lines
INNER JOIN Subsidiaries ON Subsidiaries.subsidiary_id = Transaction_lines.subsidiary_id
INNER JOIN Transactions ON Transactions.transaction_id = Transaction_lines.transaction_id
INNER JOIN Items ON Items.item_id = Transaction_lines.item_id
INNER JOIN Accounts ON Accounts.account_id = Transaction_lines.account_id
LEFT JOIN (
SELECT Transactions.transaction_id, Transactions.transaction_type FROM TRANSACTIONS
) Created_from ON Created_from.transaction_id = Transactions.created_from_id
WHERE
Accounts.type_name IN ('Other Current Asset')
AND Transactions.is_non_posting = 'No'
AND (Transactions.transaction_type IN ('Cash Sale') AND (Transactions.created_from_id IS NULL OR Created_from.transaction_type <> 'Sales Order'))
UNION ALL
SELECT Transactions.trandate, Subsidiaries.name AS "subsidiary", Items.item_id, Items.name AS "item", Transaction_lines.amount, Transaction_lines.item_count FROM Transaction_lines
INNER JOIN Subsidiaries ON Subsidiaries.subsidiary_id = Transaction_lines.subsidiary_id
INNER JOIN Transactions ON Transactions.transaction_id = Transaction_lines.transaction_id
INNER JOIN Items ON Items.item_id = Transaction_lines.item_id
INNER JOIN Accounts ON Accounts.account_id = Transaction_lines.account_id
LEFT JOIN (
SELECT Transactions.transaction_id, Transactions.transaction_type FROM TRANSACTIONS
) Created_from ON Created_from.transaction_id = Transactions.created_from_id
WHERE
Accounts.type_name IN ('Other Current Asset')
AND Transactions.is_non_posting = 'No'
AND (Transactions.transaction_type IN ('Invoice') AND (Transactions.created_from_id IS NULL OR Created_from.transaction_type <> 'Sales Order'))
UNION ALL
SELECT Transactions.trandate, Subsidiaries.name AS "subsidiary", Items.item_id, Items.name AS "item", Transaction_lines.amount, Transaction_lines.item_count FROM Transaction_lines
INNER JOIN Subsidiaries ON Subsidiaries.subsidiary_id = Transaction_lines.subsidiary_id
INNER JOIN Transactions ON Transactions.transaction_id = Transaction_lines.transaction_id
INNER JOIN Items ON Items.item_id = Transaction_lines.item_id
INNER JOIN Accounts ON Accounts.account_id = Transaction_lines.account_id
LEFT JOIN (
SELECT Transactions.transaction_id, Transactions.transaction_type FROM TRANSACTIONS
) Created_from ON Created_from.transaction_id = Transactions.created_from_id
WHERE
Accounts.type_name IN ('Other Current Asset')
AND Transactions.is_non_posting = 'No'
AND (Transactions.transaction_type IN ('Bill') AND (Transactions.created_from_id IS NULL OR Created_from.transaction_type <> 'Purchase Order'))
UNION ALL
SELECT Transactions.trandate, Subsidiaries.name AS "subsidiary", Items.item_id, Items.name AS "item", Transaction_lines.amount, Transaction_lines.item_count FROM Transaction_lines
INNER JOIN Subsidiaries ON Subsidiaries.subsidiary_id = Transaction_lines.subsidiary_id
INNER JOIN Transactions ON Transactions.transaction_id = Transaction_lines.transaction_id
INNER JOIN Items ON Items.item_id = Transaction_lines.item_id
INNER JOIN Accounts ON Accounts.account_id = Transaction_lines.account_id
LEFT JOIN (
SELECT Transactions.transaction_id, Transactions.transaction_type FROM TRANSACTIONS
) Created_from ON Created_from.transaction_id = Transactions.created_from_id
WHERE
Accounts.type_name IN ('Other Current Asset')
AND Transactions.is_non_posting = 'No'
AND (Transactions.transaction_type IN ('Credit Memo') AND (Transactions.created_from_id IS NULL OR (Created_from.transaction_type <> 'Return Authorisation' AND Created_from.transaction_type <> 'Return Authorization')))
UNION ALL
SELECT Transactions.trandate, Subsidiaries.name AS "subsidiary", Items.item_id, Items.name AS "item", Transaction_lines.amount, Transaction_lines.item_count FROM Transaction_lines
INNER JOIN Subsidiaries ON Subsidiaries.subsidiary_id = Transaction_lines.subsidiary_id
INNER JOIN Transactions ON Transactions.transaction_id = Transaction_lines.transaction_id
INNER JOIN Items ON Items.item_id = Transaction_lines.item_id
INNER JOIN Accounts ON Accounts.account_id = Transaction_lines.account_id
LEFT JOIN (
SELECT Transactions.transaction_id, Transactions.transaction_type FROM TRANSACTIONS
) Created_from ON Created_from.transaction_id = Transactions.created_from_id
WHERE
Accounts.type_name IN ('Other Current Asset')
AND Transactions.is_non_posting = 'No'
AND (Transactions.transaction_type IN ('Customer Refund') AND (Transactions.created_from_id IS NULL OR (Created_from.transaction_type <> 'Return Authorisation' AND Created_from.transaction_type <> 'Return Authorization')))
UNION ALL
SELECT Transactions.trandate, Subsidiaries.name AS "subsidiary", Items.item_id, Items.name AS "item", Transaction_lines.amount, Transaction_lines.item_count FROM Transaction_lines
INNER JOIN Subsidiaries ON Subsidiaries.subsidiary_id = Transaction_lines.subsidiary_id
INNER JOIN Transactions ON Transactions.transaction_id = Transaction_lines.transaction_id
INNER JOIN Items ON Items.item_id = Transaction_lines.item_id
INNER JOIN Accounts ON Accounts.account_id = Transaction_lines.account_id
LEFT JOIN (
SELECT Transactions.transaction_id, Transactions.transaction_type FROM TRANSACTIONS
) Created_from ON Created_from.transaction_id = Transactions.created_from_id
WHERE
Accounts.type_name IN ('Other Current Asset')
AND Transactions.is_non_posting = 'No'
AND (Transactions.transaction_type IN ('Cash Refund') AND (Transactions.created_from_id IS NULL OR (Created_from.transaction_type <> 'Return Authorisation' AND Created_from.transaction_type <> 'Return Authorization')))
UNION ALL
SELECT Transactions.trandate, Subsidiaries.name AS "subsidiary", Items.item_id, Items.name AS "item", Transaction_lines.amount, Transaction_lines.item_count FROM Transaction_lines
INNER JOIN Subsidiaries ON Subsidiaries.subsidiary_id = Transaction_lines.subsidiary_id
INNER JOIN Transactions ON Transactions.transaction_id = Transaction_lines.transaction_id
INNER JOIN Items ON Items.item_id = Transaction_lines.item_id