Ar Transactions

AR Transaction Detail

Generate full transaction history for invoices from FO_PRODUCTION_DB.GOLD_FINANCIAL.gold_fct_ar_aging_detail.

Input Parameters

Gather from the user (at least one required):

• invoice_number: Specific invoice(s) to report on (can be comma-separated list)
• owner_code: Owner code to filter all invoices and their transactions
• company_code: Optional, default "200" (Formentera Operations LLC)

Query Structure

Query gold_fct_ar_aging_detail (transaction grain) joined with gold_fct_ar_aging_summary for invoice_date:

SELECT
  d.owner_code,
  d.owner_name,
  d.invoice_number,
  CASE
    WHEN d.transaction_type = 'INV' THEN 'Invoice'
    WHEN d.transaction_type = 'PMT' THEN 'Payment'
    WHEN d.transaction_type = 'ADJ' THEN 'Adjustment'
    WHEN d.transaction_type = 'NET' THEN 'Netting'
    ELSE d.transaction_type
  END AS source,
  d.transaction_date,
  d.transaction_type AS type,
  d.reference_id AS reference,
  d.transaction_amount AS amount,
  d.running_balance AS invoice_balance,
  d.posted_flag AS posted,
  d.voucher_code AS voucher
FROM FO_PRODUCTION_DB.GOLD_FINANCIAL.GOLD_FCT_AR_AGING_DETAIL d
WHERE d.include_record = 1 AND d.company_code = :company_code
  AND (d.invoice_number IN (:invoice_numbers) OR d.owner_code IN (:owner_codes))
ORDER BY d.owner_name, d.invoice_number, d.sort_order