SELECT 
    v.VBELN as order_number,
    v.ERDAT as created_date,
    v.KUNNR as customer,
    k.NAME1 as customer_name,
    p.MATNR as material,
    p.KWMENG as quantity,
    p.NETWR as net_value,
    p.WAERK as currency
FROM VBAK v
JOIN VBAP p ON v.VBELN = p.VBELN
JOIN KNA1 k ON v.KUNNR = k.KUNNR
WHERE v.GBSTK != 'C'
ORDER BY v.ERDAT DESC;

SELECT 
    pv.vendor_name,
    ai.invoice_num,
    ai.invoice_date,
    ai.invoice_amount,
    ai.amount_paid,
    ai.invoice_amount - ai.amount_paid as balance,
    ai.payment_status_flag
FROM ap_invoices_all ai
JOIN poz_suppliers pv ON ai.vendor_id = pv.vendor_id
WHERE ai.invoice_date >= ADD_MONTHS(SYSDATE, -6)
ORDER BY ai.invoice_amount DESC;

SELECT 
    gcc.segment1 as company,
    gcc.segment2 as account,
    gb.period_name,
    gb.begin_balance_dr - gb.begin_balance_cr as opening_balance,
    gb.period_net_dr - gb.period_net_cr as net_activity,
    (gb.begin_balance_dr + gb.period_net_dr) - 
    (gb.begin_balance_cr + gb.period_net_cr) as closing_balance
FROM gl_balances gb
JOIN gl_code_combinations gcc ON gb.code_combination_id = gcc.code_combination_id
WHERE gb.actual_flag = 'A'
  AND gb.period_name = 'MAR-24'
ORDER BY gcc.segment2;

SELECT 
    c.companyName as customer,
    COUNT(DISTINCT t.id) as transaction_count,
    SUM(tl.netAmount) as total_revenue,
    MAX(t.tranDate) as last_purchase
FROM customer c
JOIN transaction t ON c.id = t.entity
JOIN transactionLine tl ON t.id = tl.transaction
WHERE t.type = 'SalesOrd'
  AND t.tranDate >= ADD_MONTHS(SYSDATE, -12)
GROUP BY c.companyName
ORDER BY total_revenue DESC
FETCH FIRST 20 ROWS ONLY;

SELECT 
    ph.PurchId as po_number,
    ph.OrderAccount as vendor_id,
    v.Name as vendor_name,
    pl.ItemId as item,
    pl.PurchQty as quantity,
    pl.PurchPrice as unit_price,
    pl.LineAmount as line_total,
    ph.PurchStatus as status
FROM PurchTable ph
JOIN PurchLine pl ON ph.PurchId = pl.PurchId
JOIN VendTable v ON ph.OrderAccount = v.AccountNum
WHERE ph.PurchStatus IN (1, 2)
ORDER BY ph.CreatedDateTime DESC;

SELECT 
    rp.name as customer,
    am.name as invoice_number,
    am.invoice_date,
    am.invoice_date_due as due_date,
    am.amount_total,
    am.amount_residual as balance_due,
    CURRENT_DATE - am.invoice_date_due as days_overdue
FROM account_move am
JOIN res_partner rp ON am.partner_id = rp.id
WHERE am.move_type = 'out_invoice'
  AND am.payment_state != 'paid'
  AND am.state = 'posted'
ORDER BY days_overdue DESC;