gta

GTA

Global Transaction Activities - A central analytical model that consolidates financial and shipment information from CargoWise, McLeod, and WorldPort into unified tables for business intelligence and reporting.

JobCount

An indicator field where JobCount=1 represents order records (for counting shipments) and JobCount=0 represents transaction records (for financial data). SUM(JobCount) gives total unique job/company combinations.

CAM

Common Analytical Model - The standardization layer between source systems and GTA that unifies data models, applies business rules, creates consistent keys, and handles temporal validity.

REV

Revenue - Recognized income from completed and invoiced services. Always positive values. Available in all systems (CW, MC, WP).

WIP

Work in Progress - Services delivered but not yet invoiced (future revenue). Positive values. ONLY available in CargoWise, not in McLeod or WorldPort.

CST

Costs - Actual expenses incurred and paid. Stored as NEGATIVE values. Available in all systems.

ACR

Accrued Costs - Services received but not yet paid (future costs). Stored as NEGATIVE values. ONLY available in CargoWise.

TransactionDepartment

The department field tied to specific charges/transactions. CRITICAL for OneStream reconciliation. Set to 'N/A' for Order records. This is the field to use for financial system matching.

JobDepartment

The operational department responsible for the overall job/shipment. NOT used for financial reconciliation. For WorldPort, maps to WFS, RDS, or FDR based on vertical.

RevWipAmountInterCo

Intercompany revenue - transactions between AIT entities that must be subtracted from gross revenue to avoid double-counting in consolidated reports.

Orders and Transactions tables

Orders (JobCount=1) provides one row per job per company for accurate shipment counting. Transactions (JobCount=0) has multiple rows per job for detailed financial tracking by date/station.

JobNum uniqueness in GTA

A single shipment (JobNum) can be handled by multiple companies (e.g., USA and MEX entities). Therefore, JobNum + CompanyCode together form the unique identifier.

Currency conversion in GTA

Local amounts are stored in original currency. USD amounts are calculated using MONTH-END exchange rates (not transaction date rates). This standardizes reporting across all entities.

Double elimination

The combination of intercompany revenue AND duty amounts that must be eliminated in consolidated reporting. Calculated as RevWipAmountInterCoDutyBoth.

Transactions grouping

Transactions are grouped by: JobNum + CompanyCode + RecordedDate + Station. This creates multiple rows per job to capture financial detail across different dates and locations.

Query for unique shipments in August 2025

SELECT SUM(JobCount) as ShipmentCount FROM gta.consolidated WHERE TransactionRecDatePeriod = '202508'; -- OR -- SELECT COUNT(*) FROM gta.orders WHERE TransactionRecDatePeriod = '202508';

Field to check for revenue mismatch

Use TransactionDepartment (NOT JobDepartment) and ensure you're filtering WHERE TransactionDepartment != 'N/A'. Also verify you're using TransactionRecDatePeriod for the period and subtracting intercompany revenue if needed.

Duplicate rows when joining Orders and Transactions

You're likely joining on JobNum alone. ALWAYS join on both: ON o.JobNum = t.JobNum AND o.CompanyCode = t.CompanyCode.

Net revenue calculation excluding intercompany transactions

SUM(REVAmountUSD + WIPAmountUSD - RevWipAmountUSDInterCo) as NetRevenue.

COUNT(*) on transactions table discrepancy

Transactions table has multiple rows per shipment (different dates/stations).

COUNT(DISTINCT CONCAT(JobNum, CompanyCode))

A method to count unique shipments by combining JobNum and CompanyCode.

Orders table

A table used for counting only, with all financial amounts intentionally set to 0.

Transactions or Consolidated

Tables used for retrieving actual financial data.

Cash basis accounting

An accounting method used by McLeod and WorldPort where WIP and ACR are always 0.

JobNum + CompanyCode

The two fields that together make a unique key in GTA.

gta.consolidated

The table recommended for most queries as it includes both order and transaction records.

TransactionRecDatePeriod

The date (YYYYMM) when the transaction was recorded for accounting.

JobRecognitionDate

The date when the job was completed.

Local amounts (REVAmountLocal)

Amounts in original currency before conversion.

USD amounts (REVAmountUSD)

Amounts converted using month-end rates for consolidated reporting.

A shipment spans July and August. How many rows in Consolidated?

Minimum 4 rows: At least 2 transaction rows (JobCount=0) for different months, plus 1-2 order rows (JobCount=1) depending on how many companies are involved.

What is the Daily refresh schedule for GTA

Bronze sources refresh at 12:30 AM CT, GTA processes at 4:00 AM CT daily.

CargoWise (CW)

An international freight system that supports WIP and ACR.

McLeod (MC)

A domestic trucking system that uses cash basis accounting and does not support WIP or ACR.

WorldPort (WP)

A warehouse operations system that uses cash basis accounting and does not support WIP or ACR.

N/A in TransactionDepartment

Indicates that order records (JobCount=1) do not have transaction-level departments.

Total Costs = CSTAmountUSD + ACRAmountUSD

Formula for calculating total costs, both values are negative.

SourceSystem field

Field used to identify records: 'CW' for CargoWise, 'MC' for McLeod, 'WP' for WorldPort.

Find cost overruns

Identify where actual costs exceed accrued costs by 25% for completed jobs.

5 Golden Rules of GTA

1. Always join on JobNum + CompanyCode; 2. Use Consolidated for most queries; 3. JobCount: 0=financials, 1=counting; 4. TransactionDepartment for reconciliation; 5. Costs are negative, revenues positive.

ABS() function

Function used to make costs positive for display.

Profit margin percentage

Calculated as Margin% = (Profit / Revenue) * 100, where Profit = Revenue + Costs.

STRING_AGG(DISTINCT CompanyCode, ', ')

SQL function to aggregate distinct CompanyCodes into a single string.

HAVING COUNT(DISTINCT CompanyCode) > 1

Condition used to find jobs handled by multiple companies.

How do you handle multi-currency in GTA?

Local amounts (REVAmountLocal) are in original currency.

USD amounts (REVAmountUSD) are converted using month-end rates. Always use USD fields for consolidated reporting.