I’m trying to filter records by subsidiary, but the TransactionAccountingLine (GL impact) table doesn’t expose that field directly.
Is there any recommended way to retrieve the subsidiary information from this table?

I attempted the following code, but it returned 0 results:

Code:

else if (table === ‘glimpact’) {
const formattedStart = convertDateToMMDDYYYY(startDate);
const formattedEnd = convertDateToMMDDYYYY(endDate);

const whereParts = [];
if (startDate && endDate) {
whereParts.push(`t.lastmodifieddate BETWEEN TO_DATE(‘${formattedStart}’, ‘MM/DD/YYYY’) AND TO_DATE(‘${formattedEnd}’, ‘MM/DD/YYYY’)`);
}
if (type) {
whereParts.push(`t.type = ‘${type.replace(/’/g, “””)}’`);
}
if (subsidiaryFilterRaw) {
whereParts.push(`tl.subsidiary IN ${subsidiaryFilterRaw}`);
}

const whereClause = whereParts.length > 0 ? `WHERE ${whereParts.join(” AND “)}` : “”;

const fields = [
“gil.*”, “t.trandate”, “t.type”, “t.tranid”, “tl.subsidiary AS glimpact_subsidiary”,
“TO_CHAR(t.lastmodifieddate, ‘YYYY-MM-DD HH24:MI:SS’) AS lastmodified_ts”
];

queryStr = `
WITH ordered AS (
SELECT
${fields.join(“, “)},
ROW_NUMBER() OVER (ORDER BY gil.transactionline) AS rn,
COUNT(*) OVER () AS total_count
FROM TransactionAccountingLine gil
JOIN transaction t ON gil.transaction = t.id
JOIN transactionline tl ON gil.transaction = tl.transaction AND gil.transactionline = tl.id
${whereClause}
)
SELECT * FROM ordered
WHERE rn BETWEEN ${offset + 1} AND ${offset + limit}
`;
}