State of the Subsidiary

SELECT
    tl.subsidiary                                           AS sub,
    ap.periodname                                           AS period,
    a.accttype                                              AS accttype,
    CASE WHEN t.memo LIKE 'Beg Balance%' THEN 1 ELSE 0 END  AS begbal,
    ROUND(SUM(tal.amount), 2)                               AS amount
FROM transactionaccountingline tal
JOIN transaction t        ON t.id = tal.transaction
JOIN transactionline tl   ON tl.transaction = tal.transaction
                         AND tl.id = tal.transactionline
JOIN account a            ON a.id = tal.account
JOIN accountingperiod ap  ON ap.id = t.postingperiod
WHERE t.posting = 'T'
  AND tal.posting = 'T'
  AND tl.subsidiary IN (1, 2, 3)
  AND a.accttype IN ('Income', 'COGS', 'Expense')
  AND ap.isquarter = 'F' AND ap.isyear = 'F'
  AND ap.startdate >= TO_DATE('2025-01-01', 'YYYY-MM-DD')
  AND ap.startdate <  TO_DATE('2026-10-01', 'YYYY-MM-DD')
GROUP BY tl.subsidiary, ap.periodname, a.accttype,
         CASE WHEN t.memo LIKE 'Beg Balance%' THEN 1 ELSE 0 END
-- Reducer: revenue = -amount on Income; YTD = periods Jan..Sep; op lens = begbal = 0.

SELECT
    tl.subsidiary             AS sub,
    a.accttype                AS accttype,
    a.acctnumber              AS acctnumber,
    a.fullname                AS acctname,
    ROUND(SUM(tal.amount), 2) AS amount
FROM transactionaccountingline tal
JOIN transaction t       ON t.id = tal.transaction
JOIN transactionline tl  ON tl.transaction = tal.transaction
                        AND tl.id = tal.transactionline
JOIN account a           ON a.id = tal.account
WHERE t.posting = 'T'
  AND tal.posting = 'T'
  AND tl.subsidiary IN (1, 2, 3)
GROUP BY tl.subsidiary, a.accttype, a.acctnumber, a.fullname

SELECT
    tl.subsidiary                          AS sub,
    t.id                                   AS tid,
    t.tranid                               AS tranid,
    t.trandate                             AS trandate,
    t.duedate                              AS duedate,
    TRUNC(SYSDATE) - TRUNC(t.duedate)      AS days_past_due,
    t.foreignamountunpaid                  AS unpaid,
    t.foreigntotal                         AS total,
    c.id                                   AS custid,
    c.companyname                          AS custname,
    c.entityid                             AS entityid
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id AND tl.mainline = 'T'
JOIN customer c         ON c.id = t.entity
WHERE t.type = 'CustInvc'
  AND t.foreignamountunpaid > 0
  AND tl.subsidiary IN (1, 2, 3)
-- Buckets: <=0 current · 1-30 · 31-60 · 61-90 · >90. A/P version: t.type = 'VendBill', JOIN vendor v.

SELECT
    tl.subsidiary                        AS sub,
    t.status                             AS status,
    COUNT(*)                             AS cnt,
    ROUND(SUM(ABS(t.foreigntotal)), 2)   AS total,
    SUM(CASE WHEN t.status = 'A'
              AND TRUNC(SYSDATE) - TRUNC(t.trandate) > 30 THEN 1 ELSE 0 END) AS stale_pending_appr
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id AND tl.mainline = 'T'
WHERE t.type = 'SalesOrd'
  AND tl.subsidiary IN (1, 2, 3)
  AND t.trandate >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
GROUP BY tl.subsidiary, t.status

SELECT tl.subsidiary AS sub, t.type AS ttype, COUNT(*) AS cnt
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id AND tl.mainline = 'T'
WHERE tl.subsidiary IN (1, 2, 3)
  AND t.trandate >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
GROUP BY tl.subsidiary, t.type

SELECT
    tl.subsidiary                        AS sub,
    loc.id                               AS loc_id,
    loc.name                             AS loc_name,
    t.type                               AS ttype,
    COUNT(DISTINCT t.id)                 AS txns,
    ROUND(SUM(ABS(tl.netamount)), 2)     AS sales
FROM transaction t
JOIN transactionline tl  ON tl.transaction = t.id
LEFT JOIN location loc   ON loc.id = tl.location
WHERE t.type IN ('CustInvc', 'CashSale')
  AND tl.mainline = 'F' AND tl.taxline = 'F'
  AND tl.subsidiary IN (1, 2, 3)
  AND t.trandate >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
GROUP BY tl.subsidiary, loc.id, loc.name, t.type

SELECT
    tl.subsidiary                        AS sub,
    cl.id                                AS class_id,
    cl.name                              AS class_name,
    COUNT(*)                             AS lines,
    ROUND(SUM(ABS(tl.netamount)), 2)     AS sales
FROM transaction t
JOIN transactionline tl        ON tl.transaction = t.id
LEFT JOIN classification cl    ON cl.id = tl.class
WHERE t.type IN ('CustInvc', 'CashSale')
  AND tl.mainline = 'F' AND tl.taxline = 'F'
  AND tl.subsidiary IN (1, 2, 3)
  AND t.trandate >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
GROUP BY tl.subsidiary, cl.id, cl.name

SELECT
    tl.subsidiary                 AS sub,
    c.id                          AS custid,
    c.companyname                 AS custname,
    c.entityid                    AS entityid,
    COUNT(*)                      AS txns,
    ROUND(SUM(t.foreigntotal), 2) AS sales
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id AND tl.mainline = 'T'
JOIN customer c         ON c.id = t.entity
WHERE t.type IN ('CustInvc', 'CashSale')
  AND tl.subsidiary IN (1, 2, 3)
  AND t.trandate >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
GROUP BY tl.subsidiary, c.id, c.companyname, c.entityid

SELECT
    tl.subsidiary                        AS sub,
    i.id                                 AS item_id,
    i.itemid                             AS itemid,
    i.displayname                        AS displayname,
    i.itemtype                           AS itemtype,
    ROUND(SUM(ABS(tl.netamount)), 2)     AS sales,
    ROUND(SUM(ABS(tl.quantity)), 2)      AS qty
FROM transaction t
JOIN transactionline tl ON tl.transaction = t.id
JOIN item i             ON i.id = tl.item
WHERE t.type IN ('CustInvc', 'CashSale')
  AND tl.mainline = 'F' AND tl.taxline = 'F'
  AND tl.subsidiary IN (1, 2, 3)
  AND t.trandate >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
GROUP BY tl.subsidiary, i.id, i.itemid, i.displayname, i.itemtype

-- Customers
SELECT c.subsidiary AS sub, COUNT(*) AS active_customers,
       SUM(CASE WHEN c.terms IS NULL THEN 1 ELSE 0 END)       AS no_terms,
       SUM(CASE WHEN c.email IS NULL THEN 1 ELSE 0 END)       AS no_email,
       SUM(CASE WHEN c.salesrep IS NULL THEN 1 ELSE 0 END)    AS no_salesrep,
       SUM(CASE WHEN c.creditlimit IS NULL THEN 1 ELSE 0 END) AS no_creditlimit,
       SUM(CASE WHEN c.datecreated >= TO_DATE('2026-01-01','YYYY-MM-DD') THEN 1 ELSE 0 END) AS created_2026
FROM customer c
WHERE c.isinactive = 'F' AND c.subsidiary IN (1, 2, 3)
GROUP BY c.subsidiary;

-- Vendors
SELECT v.subsidiary AS sub, COUNT(*) AS active_vendors,
       SUM(CASE WHEN v.terms IS NULL THEN 1 ELSE 0 END) AS no_terms,
       SUM(CASE WHEN v.email IS NULL THEN 1 ELSE 0 END) AS no_email
FROM vendor v
WHERE v.isinactive = 'F' AND v.subsidiary IN (1, 2, 3)
GROUP BY v.subsidiary;

-- Employees
SELECT e.subsidiary AS sub, COUNT(*) AS active_employees,
       SUM(CASE WHEN e.department IS NULL THEN 1 ELSE 0 END) AS no_department
FROM employee e
WHERE e.isinactive = 'F' AND e.subsidiary IN (1, 2, 3)
GROUP BY e.subsidiary;

-- Step 1: totals by location (joining location with a subsidiary filter raised
--         "Invalid or unsupported search", so locations are mapped separately)
SELECT iil.location                                   AS loc_id,
       COUNT(DISTINCT iil.item)                       AS skus,
       ROUND(SUM(iil.quantityonhand), 2)              AS qty_on_hand,
       ROUND(SUM(iil.onhandvaluemli), 2)              AS on_hand_value,
       SUM(CASE WHEN iil.quantityonhand < 0 THEN 1 ELSE 0 END) AS negative_qty_rows
FROM inventoryitemlocations iil
WHERE iil.quantityonhand <> 0
GROUP BY iil.location;

-- Step 2: location → subsidiary
SELECT loc.id, loc.name, loc.subsidiary
FROM location loc
WHERE loc.id IN (1, 3, 5, 8, 12)
ORDER BY loc.id;

SELECT
    tl.subsidiary             AS sub,
    a.acctnumber              AS acctnumber,
    a.fullname                AS acctname,
    ROUND(SUM(tal.amount), 2) AS amount
FROM transactionaccountingline tal
JOIN transaction t        ON t.id = tal.transaction
JOIN transactionline tl   ON tl.transaction = tal.transaction
                         AND tl.id = tal.transactionline
JOIN account a            ON a.id = tal.account
JOIN accountingperiod ap  ON ap.id = t.postingperiod
WHERE t.posting = 'T'
  AND tal.posting = 'T'
  AND tl.subsidiary IN (2, 3)
  AND a.accttype = 'Expense'
  AND ap.isquarter = 'F' AND ap.isyear = 'F'
  AND ap.startdate >= TO_DATE('2026-01-01', 'YYYY-MM-DD')
  AND ap.startdate <  TO_DATE('2026-10-01', 'YYYY-MM-DD')
  AND NVL(t.memo, '-') NOT LIKE 'Beg Balance%'
GROUP BY tl.subsidiary, a.acctnumber, a.fullname

SELECT
    tl.subsidiary AS sub,
    CASE WHEN t.memo LIKE 'Beg Balance%' THEN 'begbal' ELSE 'oper' END AS bucket,
    ROUND(SUM(tal.amount), 2) AS amount
FROM transactionaccountingline tal
JOIN transaction t       ON t.id = tal.transaction
JOIN transactionline tl  ON tl.transaction = tal.transaction
                        AND tl.id = tal.transactionline
JOIN account a           ON a.id = tal.account
WHERE t.posting = 'T'
  AND tal.posting = 'T'
  AND tl.subsidiary IN (1, 2, 3)
  AND a.accttype = 'AcctRec'
GROUP BY tl.subsidiary,
         CASE WHEN t.memo LIKE 'Beg Balance%' THEN 'begbal' ELSE 'oper' END

SELECT a.acctnumber, a.fullname, t.type, COUNT(*) AS lines, ROUND(SUM(tal.amount), 2) AS amount
FROM transactionaccountingline tal
JOIN transaction t        ON t.id = tal.transaction
JOIN transactionline tl   ON tl.transaction = tal.transaction AND tl.id = tal.transactionline
JOIN account a            ON a.id = tal.account
JOIN accountingperiod ap  ON ap.id = t.postingperiod
WHERE t.posting = 'T' AND tal.posting = 'T'
  AND tl.subsidiary = 2
  AND a.accttype = 'Expense'
  AND ap.id = 183                       -- Sep 2026
  AND NVL(t.memo, '-') NOT LIKE 'Beg Balance%'
GROUP BY a.acctnumber, a.fullname, t.type
ORDER BY amount DESC
FETCH FIRST 6 ROWS ONLY
-- Result: 6260 Training Expense $53,050 (2 VendBill lines), 6060 Advertising $11,770,
--         6655 Computer-Office $3,451.11, 6610 Rent $2,880 (Journal), 6671 Telephone $2,875.92 …

Each brief was identical apart from the subsidiary id and contained the 17 investigations listed in section A, the verified account facts, and the required summary shape (sections A–K including verbatim SQL). Lesson recorded: when a subagent must aggregate GL, the brief should carry the exact tl.id = tal.transactionline join template rather than describing it.

"Velocity Indigo" — futuristic, professional; confident, progressive voice with actionable insights. Headlines Poppins/Manrope Bold 28–34pt; subheads Poppins Medium 20–22pt; body Inter 12pt with generous spacing. Palette: base White #FFFFFF, text Jet Black #0E0E0E, accent Indigo #4B56D2 for key metrics and data points, neutral Cool Gray #9CA3AF. Indigo-border callouts for insights; cool-gray info boxes for risks and assumptions; sleek bar/line charts with indigo highlight and subtle indigo gradients; optional AI signal motif; company name bottom-right.