Security Notice

This document has been auto-published from the internal knowledge base. All passwords, API keys, tokens, and IP addresses have been redacted for security.

CargoWise Database Schema + Power BI Model — Comprehensive Specialist

Scope: CW database schema, 45 Power Query (M) queries, 67 DAX measures, 28 calculated columns, 8 calculated tables, full Power BI model relationships, production SQL patterns, and n8n workflow queries.

Last updated: 2026-02-24

Table of Contents

1. Connection — DB credentials, endpoints
2. Naming Convention — CW column prefix patterns
3. Core Forwarding Tables — JobConsol, JobShipment, JobConShipLink
4. Customs Tables — JobDeclaration, CusEntryNum
5. Transport & Container Tables — JobConsolTransport, JobContainer
6. Organization Tables — OrgHeader, OrgAddress
7. Document & Milestone Tables — JobDocAddress, JobRequiredDocument, JobMilestone
8. Financial Tables — JobHeader, JobCharge, AccTransactionHeader, AccTransactionLines
9. Reference Tables — GlbStaff, RefUNLOCO, RefVessel, RefContainer
10. Customs Parts & Tariff Tables — OrgSupplierPart, CusClassPartPivot, etc.
11. Relationship Diagram — CW entity tree (ASCII)
12. BL Match Query — Production pattern (Consol → Shipment → Declaration)
13. AN Audit / AP Invoice Queries — n8n workflow SQL
14. Planned Sales MC Queries — Not yet built
15. Power BI / DAX Queries (Q1–Q45) — Full Power Query M scripts with SQL cores
16. Power Query Utility Functions — FxIsGarbage, FxGetCleanNotes, FxStripRTF, FxDecompress
17. Common Port Codes — UN/LOCODE reference
18. Power BI Model — Tables, relationships, DAX measures, calculated columns/tables

Connection

• Type: Microsoft SQL Server
• Server: jecprd.wisegrid.net / Database: OdysseyJECPRD
• n8n Credential: [CREDENTIAL_ID] ("Microsoft SQL account")
• Environment: JEC / PRD / LAX (Enterprise/Server/Company)
• eAdaptor: https://jecprdservices.wisegrid.net/eAdaptor
• Power BI: Connects via Sql.Database("jecprd.wisegrid.net", "OdysseyJECPRD") in Power Query
• Used by: AN Upload (hJdFaSEsRZc1VtuF), AN Audit (7 wf), AP Invoice (YYUjYczdRH8kIfAG), WSI Management Report (Power BI)
• PDF Schema Reference: ~/Downloads/BD CargoWise One All Diagrams.pdf (84 pages, full ER diagrams)
• .pbit Extract: ~/OneDrive/Escritorio/Managment Report 121925/ (DataModelSchema, DAXQueries)

Naming Convention

• All columns: {2-letter prefix}_{ColumnName} (e.g., OH_Code, JK_PK, JS_HouseBill)
• Primary keys: {prefix}_PK (UID/GUID)
• Foreign keys: {prefix}_{FK_prefix} or {prefix}_{FK_prefix}_{Role} (e.g., JE_OH_Importer)
• Natural keys: {prefix}_NK{Description} (e.g., JK_RL_NKLoadPort → RefUNLOCO code)
• Timestamps: {prefix}_SystemCreateTimeUtc, {prefix}_SystemLastEditTimeUtc
• Booleans: {prefix}_Is{Name} (BIT)
• Data types: UID=GUID, VC=varchar, NVC=nvarchar, C=char, MN=money, DC=decimal, DT=datetime, BIT=bit, SI=smallint, I=int, VB=varbinary

---

Core Forwarding Tables

JobConsol (JK) — Master Consolidation (~80+ columns)

The top-level shipping record. Contains the Master Bill of Lading (MBL).

Column	Type	Notes
JK_PK	UID	Primary key
JK_UniqueConsignRef	VC(20)	eAdaptor TargetKey (ForwardingConsol)
JK_MasterBillNum	VC(35)	Master Bill of Lading — primary BL match field
JK_MasterBillIssueDate	DT	MBL issue date
JK_BookingReference	VC(35)	Carrier booking ref
JK_ConsolMode	C(3)	FCL/LCL/AIR
JK_ConsolStatus	VC(3)	Status code
JK_TransportMode	C(3)	SEA/AIR/ROD/RAL
JK_Phase	VC(3)	Operational phase
JK_ReleaseType	VC(3)	Release method
JK_RL_NKLoadPort	VC(5)	Port of loading (UN/LOCODE)
JK_RL_NKDischargePort	VC(5)	Port of discharge
JK_RL_NKFirstForeignPort	VC(5)	First foreign port
JK_RL_NKLastForeignPort	VC(5)	Last foreign port
JK_RL_NKPortOfFirstArrival	VC(5)	First arrival port
JK_DateFirstForeignPort	DT	Date at first foreign port
JK_DateLastForeignPort	DT	Date at last foreign port
JK_DatePortOfFirstArrival	DT	Date at first arrival port
JK_ShippedOnBoardDate	DT	On board date
JK_ConsolCutOffDate	DT	Cut-off date
JK_OH_Creditor	UID	FK → OrgHeader (shipping line/creditor)
JK_OA_ShippingLineAddress	UID	FK → OrgAddress
JK_OA_CoLoadAddress	UID	FK → OrgAddress
JK_OA_SendingForwarderAddress	UID	FK → OrgAddress (agent)
JK_IsHazardous	BIT	Hazardous cargo flag
JK_IsCancelled	BIT	Cancelled flag
JK_IsValid	BIT	Valid record flag
JK_CustomsReference	VC(35)	Customs ref
JK_AgentsReference	VC(35)	Agent ref
JK_CoLoadMasterBill	VC(35)	Co-load MBL
JK_JK_MasterConsol	UID	FK → self (parent consol)
JK_PrepaidCollect	C(3)	Prepaid/Collect

JobShipment (JS) — House Shipment (~100+ columns)

Individual house shipments within a consol. Contains the House Bill (HBL).

Column	Type	Notes
JS_PK	UID	Primary key
JS_UniqueConsignRef	VC(20)	eAdaptor TargetKey (ForwardingShipment)
JS_HouseBill	VC(20)	House Bill of Lading — BL match field
JS_HouseBillIssueDate	DT	HBL issue date
JS_BookingReference	VC(20)	Booking ref
JS_ShipmentStatus	VC(3)	Status
JS_ShipmentType	VC(3)	Type
JS_TransportMode	C(3)	SEA/AIR/ROD/RAL
JS_Phase	VC(3)	Phase
JS_INCO	C(3)	Incoterm (FOB/CIF/EXW/etc.)
JS_ReleaseType	C(3)	Release type
JS_PackingMode	C(3)	FCL/LCL
JS_RL_NKOrigin	VC(5)	Origin UN/LOCODE
JS_RL_NKDestination	VC(5)	Destination
JS_RL_NKLoadPort	VC(5)	Load port
JS_RL_NKDischargePort	VC(5)	Discharge port
JS_RL_NKPlaceOfReceipt	VC(5)	Place of receipt
JS_RL_NKPlaceOfDischarge	VC(5)	Place of discharge
JS_E_DEP	DT	Estimated departure
JS_E_ARV	DT	Estimated arrival
JS_A_RCV	DT	Actual receipt
JS_A_BKD	DT	Actual booked
JS_GoodsDescription	VC(35)	Goods description
JS_GoodsValue	MN	Goods value
JS_RX_NKGoodsValueCurr	VC(3)	Currency
JS_InsuranceValue	MN	Insurance value
JS_ActualWeight	DC(9,3)	Actual weight
JS_ActualChargeable	DC(9,3)	Chargeable weight
JS_TotalPackageCount	I	Total packages
JS_OuterPacks	I	Outer packs
JS_OH_ImportBroker	UID	FK → OrgHeader
JS_OH_ExportBroker	UID	FK → OrgHeader
JS_OH_DeliveryAgent	UID	FK → OrgHeader
JS_IsCancelled	BIT	Cancelled
JS_IsValid	BIT	Valid

JobConShipLink (JN) — Consol ↔ Shipment Link (6 columns)

Simple pivot table linking consols to shipments (many-to-many).

Column	Type	Notes
JN_PK	UID	Primary key
JN_JK	UID	FK → JobConsol.JK_PK
JN_JS	UID	FK → JobShipment.JS_PK

---

Customs Tables

JobDeclaration (JE) — Customs Declaration (131 columns)

Customs entry records. Very large table with importer, consignee, vessel, and regulatory data.

Column	Type	Notes
JE_PK	UID	Primary key
JE_DeclarationReference	VC(35)	eAdaptor TargetKey (CustomsDeclaration)
JE_HouseBill	VC(35)	House BL — BL match field
JE_MasterBill	VC(35)	Master BL — BL match field
JE_VesselName	VC(35)	Vessel name
JE_VoyageFlightNo	VC(10)	Voyage/flight
JE_JS	UID	FK → JobShipment.JS_PK
JE_OH_Importer	UID	FK → OrgHeader (importer org)
JE_OH_Consignee	UID	FK → OrgHeader
JE_OH_Supplier	UID	FK → OrgHeader
JE_OH_Buyer	UID	FK → OrgHeader
JE_OH_ShippingLine	UID	FK → OrgHeader
JE_OH_Forwarder	UID	FK → OrgHeader
JE_OH_ExternalBroker	UID	FK → OrgHeader
JE_EntryDate	DT	Entry date
JE_EntryStatus	VC(3)	Entry status
JE_DateOfArrival	DT	Arrival date
JE_DateOfFirstArrival	DT	First arrival
JE_TransportMode	VC(3)	Transport mode
JE_CarrierCode	VC(4)	SCAC code
JE_RL_NKPortOfLoading	VC(5)	Load port
JE_RL_NKPortOfArrival	VC(5)	Arrival port
JE_RL_NKPortOfFirstArrival	VC(5)	First arrival port
JE_RL_NKOrigin	VC(5)	Origin
JE_RL_NKFinalDestination	VC(5)	Final destination
JE_ShipmentIncoTerm	VC(3)	Incoterm
JE_GoodsDescription	VC(128)	Goods description
JE_TotalWeight	DC(9,3)	Total weight
JE_TotalVolume	DC(9,3)	Total volume
JE_TotalNoOfPacks	I	Total packs
JE_ContainerCount	SI	Container count
JE_ContainerMode	VC(3)	Container mode
JE_Folio	VC(20)	Folio number
JE_IsCancelled	BIT	Cancelled

CusEntryNum (CE) — Customs Entry Numbers (18 columns)

Column	Type	Notes
CE_PK	UID	Primary key
CE_EntryNum	VC(35)	Entry number
CE_EntryType	VC(3)	Entry type
CE_EntryStatus	VC(3)	Status
CE_Category	VC(3)	Category
CE_ParentID	UID	Parent PK
CE_ParentTable	VC(35)	'JobDeclaration'
CE_IssueDate	DT	Issue date
CE_ExpiryDate	DT	Expiry date

---

Transport & Container Tables

JobConsolTransport (JW) — Transport Legs (53 columns)

Vessel/flight/rail legs. Multiple legs per consol (pre-carriage, main, on-carriage).

Column	Type	Notes
JW_PK	UID	Primary key
JW_ParentGUID	UID	Parent PK (Consol or Shipment)
JW_ParentType	VC(3)	'CON' (Consol) or 'SHP' (Shipment)
JW_Vessel	VC(35)	Vessel name
JW_VoyageFlight	VC(10)	Voyage/flight number
JW_TransportMode	C(3)	SEA, AIR, RAL, ROD
JW_TransportType	C(3)	Transport type
JW_ETD	DT	Estimated departure
JW_ETA	DT	Estimated arrival
JW_ATD	DT	Actual departure
JW_ATA	DT	Actual arrival
JW_STD	DT	Scheduled departure
JW_STA	DT	Scheduled arrival
JW_LegOrder	TI	Leg sequence number
JW_Status	VC(3)	Leg status
JW_RL_NKLoadPort	VC(5)	Load port (UN/LOCODE)
JW_RL_NKDiscPort	VC(5)	Discharge port
JW_CarrierBookingReference	VC(35)	Booking ref on this leg
JW_OA_CarrierAddress	UID	FK → OrgAddress
JW_OA_ArrivalLocation	UID	FK → OrgAddress
JW_OA_DepartureLocation	UID	FK → OrgAddress
JW_TerminalCutOff	DT	Terminal cut-off
JW_DocumentaryCutOff	DT	Doc cut-off
JW_VGMCutOff	DT	VGM cut-off
JW_IsValid	BIT	Valid

JobContainer (JC) — Container Details (133 columns)

NOTE: Table name is JobContainer, NOT JobConsolContainer. This is confirmed by the PDF schema.

Column	Type	Notes
JC_PK	UID	Primary key
JC_JK	UID	FK → JobConsol.JK_PK (direct FK, not ParentGUID!)
JC_ContainerNum	VC(20)	Container number (e.g., MSCU1234567)
JC_ContainerMode	C(3)	FCL/LCL
JC_ContainerStatus	VC(3)	Status
JC_ContainerCount	SI	Count
JC_RC	UID	FK → RefContainer (container type/size)
JC_SealNum	VC(20)	Seal number
JC_SealParty	VC(3)	Seal issuer
JC_GrossWeight	DC(9,3)	Gross weight
JC_GrossWeightUQ	VC(2)	Weight unit
JC_TareWeight	DC(9,3)	Tare weight
JC_GrossVolume	DC(9,3)	Volume
JC_GrossVolumeUQ	VC(2)	Volume unit
JC_ReleaseNum	VC(20)	Release number
JC_MarksAndNumbers	VC	Marks & numbers
JC_OH_ShippingLine	UID	FK → OrgHeader
JC_OH_CFSClient	UID	FK → OrgHeader
JC_FCLWharfGateIn	DT	Gate in timestamp
JC_FCLOnBoardVessel	DT	On board timestamp
JC_FCLUnloadFromVessel	DT	Unloaded timestamp
JC_FCLAvailable	DT	Available for pickup
JC_FCLWharfGateOut	DT	Gate out timestamp
JC_EmptyRequired	DT	Empty required date
JC_EmptyReturnedBy	DT	Empty return deadline
JC_EmptyReadyForReturn	DT	Ready for return
JC_IsValid	BIT	Valid
JC_IsDamaged	BIT	Damaged flag
JC_IsEmptyContainer	BIT	Empty flag

---

Organization Tables

OrgHeader (OH) — Organization Master (104 columns)

Companies, agents, carriers, etc. Many self-referencing FKs for relationships.

Column	Type	Notes
OH_PK	UID	Primary key
OH_Code	NVC(12)	Org code — links to Salesforce CWCode__c
OH_FullName	NVC(100)	Full organization name
OH_IsActive	BIT	Active flag
OH_Category	C(3)	Category
OH_Language	VC(7)	Language
OH_RL_NKClosestPort	VC(5)	Nearest port
OH_IsConsignee	BIT	Is consignee
OH_IsConsignor	BIT	Is consignor (shipper)
OH_IsForwarder	BIT	Is forwarder
OH_IsBroker	BIT	Is customs broker
OH_IsShippingLine	BIT	Is shipping line
OH_IsLocalTransport	BIT	Is local transport
OH_IsRailProvider	BIT	Is rail provider
OH_IsSalesLead	BIT	Is sales lead
OH_IsCompetitor	BIT	Is competitor
OH_Client	UID	FK → self (client org)
OH_Creditor	UID	FK → self (creditor)
OH_Debtor	UID	FK → self (debtor)
OH_Importer	UID	FK → self (importer)
OH_Owner	UID	FK → self (owner)
OH_ScreeningStatus	C(3)	Compliance screening

Known CWCode mappings (SF Account → CW):

SF Account	CWCode__c
JC Sales	JCSALELAX
Liberty Paper	DDOFFILAX
MULIA INC	MULIACEK
Twenty Four 7 Global	TWEFOUCRU
Acer America	ACEAMESJC

OrgAddress (OA) — Organization Addresses (66 columns)

Column	Type	Notes
OA_PK	UID	Primary key
OA_OH	UID	FK → OrgHeader.OH_PK
OA_Code	NVC(25)	Address code
OA_Address1	NVC(50)	Street line 1
OA_Address2	NVC(50)	Street line 2
OA_City	NVC(50)	City
OA_State	NVC(25)	State/province
OA_PostCode	NVC(10)	Postal code
OA_RN_NKCountryCode	VC(2)	Country (ISO 2-letter)
OA_Phone	VC(20)	Phone
OA_Fax	VC(20)	Fax
OA_Email	NVC(254)	Email
OA_Mobile	VC(20)	Mobile
OA_GeoLocation	GPY	Lat/lng
OA_RL_NKRelatedPortCode	VC(5)	Related port
OA_IsActive	BIT	Active

---

Document & Milestone Tables

JobDocAddress (E2) — Document Addresses

Links organizations to jobs by address role (consignee, shipper, etc.).

Column	Type	Notes
E2_ParentID	UID	Parent PK (JS, JK, JE)
E2_ParentTableCode	VC(3)	'JS', 'JK', 'JE'
E2_AddressType	VC(3)	'CED'=Consignee, 'SHP'=Shipper, etc.
E2_OA_Address	UID	FK → OrgAddress.OA_PK

JobRequiredDocument (EQ) — Document Requirements (25 columns)

Tracks required/received documents per job.

Column	Type	Notes
EQ_PK	UID	Primary key
EQ_ParentID	UID	Parent PK
EQ_ParentTableCode	VC(3)	'JS', 'JK', 'JE'
EQ_DocType	VC(3)	'CAN', 'ARN', 'RAN', etc.
EQ_DocCategory	VC(3)	Category
EQ_DocDescription	NVC(35)	Description
EQ_DocNumber	NVC(15)	Document number
EQ_DateReceived	DT	When received
EQ_ValidToDate	DT	Validity expiry
EQ_IsValid	BIT	Valid

JobMilestone (JM) — Milestone Events

Tracks operational milestones per job. Used for telex release check (TLR code).

Column	Type	Notes
JM_PK	UID	Primary key
Code field	VC	Event code (TLR=Telex Release, DEP=Departed, etc.)
Parent fields	UID	Links to job records

Note: JM columns not fully documented in PDF text. Schema is in diagram-only pages.

---

Financial Tables

JobHeader (JH) — Job Header (46 columns)

Links charges to consols/shipments. One JH per commercial job.

Column	Type	Notes
JH_PK	UID	Primary key
JH_JobNum	VC(35)	Job number
JH_ParentID	UID	Parent (consol/shipment) PK
JH_ParentTableCode	VC(3)	'JK', 'JS', 'JE'
JH_Status	VC(3)	Job status
JH_HeaderType	VC(3)	Header type
JH_Description	NVC(128)	Description
JH_GS_NKRepOps	VC(3)	Ops rep (staff code)
JH_GS_NKRepSales	VC(3)	Sales rep (staff code)
JH_OA_LocalChargesAddr	UID	FK → OrgAddress (local client address)
JH_GB	UID	FK → GlbBranch
JH_GC	UID	FK → GlbCompany
JH_GE	UID	FK → GlbDepartment
JH_IsActive	BIT	Active flag
JH_IsValid	BIT	Valid flag
JH_SystemCreateTimeUtc	DT	Created

JobCharge (JR) — Job Charges (95 columns)

Revenue and cost lines. Very detailed — AP invoices, AR invoices, rates, taxes.

Column	Type	Notes
JR_PK	UID	Primary key
JR_JH	UID	FK → JobHeader.JH_PK
JR_AC	UID	FK → AccChargeCode (charge code)
JR_Desc	NVC(1024)	Charge description
JR_LineType	C(3)	Line type
JR_ChargeType	VC(3)	Charge type
JR_LocalCostAmt	MN	Local cost amount
JR_LocalSellAmt	MN	Local sell amount
JR_OSCostAmt	MN	OS cost amount
JR_OSSellAmt	MN	OS sell amount
JR_OSCostExRate	DC(18,9)	Cost exchange rate
JR_OSSellExRate	DC(18,9)	Sell exchange rate
JR_EstimatedCost	MN	Estimated cost
JR_EstimatedRevenue	MN	Estimated revenue
JR_RX_NKCostCurrency	VC(3)	Cost currency
JR_RX_NKSellCurrency	VC(3)	Sell currency
JR_APInvoiceNum	VC(38)	AP invoice number
JR_APInvoiceDate	DT	AP invoice date
JR_APLinePostingStatus	VC(3)	AP posting status
JR_ARLinePostingStatus	VC(3)	AR posting status
JR_AL_APLine	UID	FK → AccTransactionLines (AP)
JR_AL_ARLine	UID	FK → AccTransactionLines (AR)
JR_OH_CostAccount	UID	FK → OrgHeader (vendor)
JR_OH_SellAccount	UID	FK → OrgHeader (customer)
JR_PaymentDate	DT	Payment date
JR_PaymentType	C(3)	Payment type

AccTransactionHeader (AH) — Accounting Header (90 columns)

AP/AR invoice and payment records.

Column	Type	Notes
AH_PK	UID	Primary key
AH_TransactionNum	VC(38)	Transaction/invoice number
AH_TransactionType	C(3)	Type (AP/AR/JNL/etc.)
AH_TransactionCategory	VC(3)	Category
AH_TransactionReference	VC(20)	Reference
AH_Ledger	C(2)	Ledger (AP/AR/GL)
AH_Desc	NVC(128)	Description
AH_JobNumber	VC(35)	Related job number
AH_JH	UID	FK → JobHeader
AH_OH	UID	FK → OrgHeader (debtor/creditor)
AH_InvoiceDate	DT	Invoice date
AH_InvoiceAmount	MN	Invoice amount
AH_GSTAmount	MN	Tax amount
AH_LocalTotal	MN	Local total
AH_OSTotal	MN	OS total
AH_OutstandingAmount	MN	Outstanding
AH_DueDate	DT	Due date
AH_PostDate	DT	Post date
AH_PostPeriod	I	Period
AH_RX_NKTransactionCurrency	VC(3)	Currency
AH_ExchangeRate	DC(18,9)	Exchange rate
AH_IsCancelled	BIT	Cancelled
AH_InvoiceApproved	BIT	Approved
AH_MatchStatus	VC(3)	Match status
AH_FullyPaidDate	DT	Fully paid date
AH_Source	UID	FK → self (source transaction)

AccTransactionLines (AL) — Accounting Lines (56 columns)

NOTE: Table name is plural AccTransactionLines, confirmed by FK references in PDF.

Column	Type	Notes
AL_PK	UID	Primary key
AL_AH	UID	FK → AccTransactionHeader.AH_PK
AL_JH	UID	FK → JobHeader
AL_OH	UID	FK → OrgHeader
AL_AC	UID	FK → AccChargeCode
AL_LineType	C(3)	Line type
AL_Desc	NVC(1024)	Description
AL_LineAmount	MN	Local amount
AL_OSAmount	MN	OS amount
AL_UnitPrice	MN	Unit price
AL_UnitQty	I	Quantity
AL_GSTVAT	MN	Tax amount
AL_PostDate	DT	Post date
AL_PostPeriod	I	Period
AL_RX_NKTransactionCurrency	VC(3)	Currency
AL_ExchangeRate	DC(18,9)	Exchange rate
AL_WithholdingTax	MN	WHT amount

---

Reference Tables

GlbStaff (GS) — Staff Master (132 columns)

Key Columns	Type	Notes
GS_PK	UID	Primary key
GS_Code	VC(3)	Staff code (3 chars) — used in NK references
GS_FullName	NVC(256)	Full name
GS_EmailAddress	NVC(254)	Email
GS_IsActive	BIT	Active
GS_IsSalesRep	BIT	Sales rep flag
GS_IsOperational	BIT	Ops flag
GS_Title	NVC(128)	Job title

RefUNLOCO (RL) — UN Location Codes (43 columns)

Key Columns	Type	Notes
RL_PK	UID	Primary key
RL_Code	C(5)	UN/LOCODE (e.g., USLAX)
RL_PortName	VC(35)	Port name
RL_RN_NKCountryCode	VC(2)	Country
RL_HasSeaport	BIT	Has seaport
RL_HasAirport	BIT	Has airport
RL_HasRail	BIT	Has rail
RL_GeoLocation	GPY	Coordinates
RL_IATA	VC(3)	IATA code

RefVessel (RV) — Vessel Registry (25 columns)

Key Columns	Type	Notes
RV_PK	UID	Primary key
RV_Code	VC(35)	Vessel name/code
RV_CarrierCode	VC(4)	SCAC
RV_LloydsNumber	C(7)	IMO/Lloyds number
RV_OH	UID	FK → OrgHeader (carrier org)

RefContainer (RC) — Container Types (31 columns)

Key Columns	Type	Notes
RC_PK	UID	Primary key
RC_Code	VC(10)	Type code
RC_Description	VC(35)	Description
RC_ContainerType	VC(3)	DRY/REF/OT/FR
RC_IsHighCube	BIT	High cube flag
RC_TEU	DC(5,2)	TEU equivalent
RC_Length	DC(9,3)	Length
RC_Height	DC(9,3)	Height
RC_Width	DC(9,3)	Width
RC_TareWeight	DC(9,3)	Tare weight
RC_GrossWeight	DC(9,3)	Max gross weight
RC_CubicCapacity	DC(9,3)	Cubic capacity

---

Customs Parts & Tariff Tables (from Parts Query)

OrgSupplierPart (OP) — Supplier Part Master

Column	Type	Notes
OP_PK	UID	Primary key
OP_PartNum	VC	Part number
OP_Desc	VC	Part description
OP_Weight	DC	Gross weight
OP_NetWeight	DC	Net weight
OP_WeightUQ	VC	Weight unit
OP_Cubic	DC	Cubic measure
OP_CubicUQ	VC	Cubic unit
OP_StockKeepingUnit	VC	SKU

OrgPartRelation (OU) — Part-Org Relationship

Column	Type	Notes
OU_OH	UID	FK → OrgHeader.OH_PK
OU_OP	UID	FK → OrgSupplierPart.OP_PK
OU_Relationship	VC	'OWN' (owner), 'SUP' (supplier)

OrgPartUnit (OF) — Part Unit Packaging

Column	Type	Notes
OF_OP	UID	FK → OrgSupplierPart.OP_PK
OF_QuantityInParent	I	Units per parent package
OF_PackType	VC	Package type
OF_ParentPackType	VC	Parent package type

CusClassPartPivot (CI) — Customs Classification (HTS codes)

Column	Type	Notes
CI_PK	UID	Primary key
CI_OP	UID	FK → OrgSupplierPart.OP_PK
CI_OH	UID	FK → OrgHeader.OH_PK
CI_ChildType	VC	'HTI' (HTS Import), 'HTE' (HTS Export), 'SHB' (Schedule B)
CI_TariffNum	VC	Tariff number
CI_SupplementalTariff	VC	Supplemental tariff
CI_RN_NKCountryOfExport	VC	Country of export
CI_RN_NKCountryOfOrigin	VC	Country of origin

CusLineTariffDetail (BZ) — Tariff Line Details

Column	Type	Notes
BZ_ParentID	UID	FK → CusClassPartPivot.CI_PK
BZ_Type	VC	'AT1'
BZ_Tariff	VC	Additional tariff number

---

Additional Container Milestone Columns (from production queries)

These columns on JobContainer (JC) track the full container lifecycle:

Column	Type	Notes
JC_ContainerYardEmptyPickupGateOut	DT	Empty container picked up from yard
JC_ArrivalCartageComplete	DT	Final delivery completed
JC_ContainerYardEmptyReturnGateIn	DT	Empty container returned to yard

Already documented: JC_FCLWharfGateIn, JC_FCLOnBoardVessel, JC_FCLUnloadFromVessel, JC_FCLAvailable, JC_FCLWharfGateOut

---

Production SQL Files (Real Queries)

These files contain actual CW queries used in production. Located in OneDrive and Downloads:

File	Path	Lines	Content
Status Query	~/OneDrive - WSI/Microsoft Teams Chat Files/Status Query.txt	118	Full shipment+container+party+transport query
Shipment Legs Query	~/OneDrive - WSI/Microsoft Teams Chat Files/Shipment Legs Query.txt	24	Transport legs query
Container With Status	~/OneDrive - WSI/Microsoft Teams Chat Files/Container With Status.txt	97	Container milestone tracking
Container With Geo	~/Downloads/Container_070725.txt	118	Container query with RefUNLOCO lat/lon joins
Parts Query	~/OneDrive/Documents/Parts Query.txt	68	Customs parts/tariff with HTS codes
Cost/AP Query	~/.claude/paste-cache/e8e7c8c666244b61.txt	—	JobCharge with AP/AR posting status

---

Relationship Diagram

OrgHeader (OH)
  └─ OrgAddress (OA) ── OA_OH → OH_PK
       └─ JobDocAddress (E2) ── E2_OA_Address → OA_PK

OrgSupplierPart (OP)
  ├─ OrgPartRelation (OU) ── OU_OP → OP_PK (+ OU_OH → OH_PK)
  ├─ OrgPartUnit (OF) ── OF_OP → OP_PK
  └─ CusClassPartPivot (CI) ── CI_OP → OP_PK (+ CI_OH → OH_PK)
       └─ CusLineTariffDetail (BZ) ── BZ_ParentID → CI_PK

JobConsol (JK)
  ├─ JobConShipLink (JN) ── JN_JK → JK_PK
  │    └─ JobShipment (JS) ── JN_JS → JS_PK
  │         └─ JobDeclaration (JE) ── JE_JS → JS_PK
  │              └─ CusEntryNum (CE) ── CE_ParentID → JE_PK
  ├─ JobConsolTransport (JW) ── JW_ParentGUID → JK_PK (ParentType='CON')
  ├─ JobContainer (JC) ── JC_JK → JK_PK
  └─ JobRequiredDocument (EQ) ── EQ_ParentID → JK_PK

JobHeader (JH) ── JH_ParentID → JK_PK or JS_PK
  └─ JobCharge (JR) ── JR_JH → JH_PK
       ├─ JR_AL_APLine → AccTransactionLines.AL_PK
       └─ JR_AL_ARLine → AccTransactionLines.AL_PK

AccTransactionHeader (AH)
  └─ AccTransactionLines (AL) ── AL_AH → AH_PK

---

BL Match Query — Production Pattern

Priority: Consol → Shipment → Declaration. Used by AN Upload + AP Invoice.

Key Logic

1. Space normalization: REPLACE(field, ' ', '')
2. SCAC prefix strip: If BL > 4 chars, also try without first 4 (e.g., MEDU1234567 → 1234567)
3. HasCAN flag: Checks JobRequiredDocument for existing CAN doc type
4. OUTER APPLY pattern: LEFT JOIN with TOP 1 subqueries
5. n8n variable: Production uses {{ $json.data.bill_of_lading }} instead of @BillOfLading

Production vs Documented Differences

The actual n8n export (~/Downloads/Arrival Notice Upload(eAdaptor).json) has 3 extra matching clauses not shown below:

• ShipmentSearch also matches on MasterData.MasterBill (3 extra OR clauses with SCAC stripping)
• DeclarationSearch also does SCAC-prefix stripping on JE_HouseBill and JE_MasterBill
• ConsolSearch EXISTS clause also does reverse SCAC stripping (when CW field > 4 chars)

Full SQL (Production)

WITH ConsolSearch AS (
    SELECT TOP 1
        'ForwardingConsol' AS TargetType,
        JK.JK_UniqueConsignRef AS TargetKey,
        CAST(NULL AS VARCHAR(35)) AS EntryNumber,
        HouseBillData.HouseBill,
        JK.JK_MasterBillNum AS MasterBill,
        JK.JK_UniqueConsignRef AS ConsolNumber,
        VesselData.VesselName,
        VesselData.VoyageFlightNo,
        ImporterData.Importer,
        JK.JK_PK AS RecordPK,
        'JK' AS ParentTableCode
    FROM JobConsol JK
    OUTER APPLY (
        SELECT TOP 1 JW.JW_Vessel AS VesselName, JW.JW_VoyageFlight AS VoyageFlightNo
        FROM JobConsolTransport JW
        WHERE JW.JW_ParentGUID = JK.JK_PK AND JW.JW_ParentType = 'CON' AND JW.JW_Vessel IS NOT NULL
        ORDER BY JW.JW_ETD
    ) VesselData
    OUTER APPLY (
        SELECT TOP 1 JS.JS_HouseBill AS HouseBill
        FROM JobConShipLink JN JOIN JobShipment JS ON JS.JS_PK = JN.JN_JS
        WHERE JN.JN_JK = JK.JK_PK AND JS.JS_HouseBill IS NOT NULL AND JS.JS_HouseBill <> ''
    ) HouseBillData
    OUTER APPLY (
        SELECT TOP 1 OH.OH_FullName AS Importer
        FROM JobConShipLink JN
        JOIN JobShipment JS ON JS.JS_PK = JN.JN_JS
        JOIN JobDocAddress JDA ON JDA.E2_ParentID = JS.JS_PK AND JDA.E2_ParentTableCode = 'JS' AND JDA.E2_AddressType = 'CED'
        JOIN OrgAddress OA ON OA.OA_PK = JDA.E2_OA_Address
        JOIN OrgHeader OH ON OH.OH_PK = OA.OA_OH
        WHERE JN.JN_JK = JK.JK_PK
    ) ImporterData
    WHERE (
        REPLACE(JK.JK_MasterBillNum, ' ', '') = REPLACE(@BillOfLading, ' ', '')
        OR (LEN(REPLACE(@BillOfLading, ' ', '')) > 4 AND REPLACE(JK.JK_MasterBillNum, ' ', '') = RIGHT(REPLACE(@BillOfLading, ' ', ''), LEN(REPLACE(@BillOfLading, ' ', '')) - 4))
        OR (LEN(REPLACE(JK.JK_MasterBillNum, ' ', '')) > 4 AND REPLACE(@BillOfLading, ' ', '') = RIGHT(REPLACE(JK.JK_MasterBillNum, ' ', ''), LEN(REPLACE(JK.JK_MasterBillNum, ' ', '')) - 4))
    )
    OR EXISTS (
        SELECT 1 FROM JobConShipLink JN JOIN JobShipment JS ON JS.JS_PK = JN.JN_JS
        WHERE JN.JN_JK = JK.JK_PK
        AND (
            REPLACE(JS.JS_HouseBill, ' ', '') = REPLACE(@BillOfLading, ' ', '')
            OR (LEN(REPLACE(@BillOfLading, ' ', '')) > 4 AND REPLACE(JS.JS_HouseBill, ' ', '') = RIGHT(REPLACE(@BillOfLading, ' ', ''), LEN(REPLACE(@BillOfLading, ' ', '')) - 4))
        )
    )
),
ShipmentSearch AS (
    SELECT TOP 1
        'ForwardingShipment' AS TargetType,
        JS.JS_UniqueConsignRef AS TargetKey,
        CAST(NULL AS VARCHAR(35)) AS EntryNumber,
        JS.JS_HouseBill AS HouseBill,
        MasterData.MasterBill, MasterData.ConsolNumber,
        VesselData.VesselName, VesselData.VoyageFlightNo,
        ImporterData.Importer, JS.JS_PK AS RecordPK, 'JS' AS ParentTableCode
    FROM JobShipment JS
    OUTER APPLY (
        SELECT TOP 1 JK.JK_MasterBillNum AS MasterBill, JK.JK_UniqueConsignRef AS ConsolNumber
        FROM JobConShipLink JN JOIN JobConsol JK ON JK.JK_PK = JN.JN_JK WHERE JN.JN_JS = JS.JS_PK
    ) MasterData
    OUTER APPLY (
        SELECT TOP 1 JW.JW_Vessel AS VesselName, JW.JW_VoyageFlight AS VoyageFlightNo
        FROM JobConShipLink JN JOIN JobConsol JK ON JK.JK_PK = JN.JN_JK
        JOIN JobConsolTransport JW ON JW.JW_ParentGUID = JK.JK_PK AND JW.JW_ParentType = 'CON'
        WHERE JN.JN_JS = JS.JS_PK AND JW.JW_Vessel IS NOT NULL ORDER BY JW.JW_ETD
    ) VesselData
    OUTER APPLY (
        SELECT TOP 1 OH.OH_FullName AS Importer
        FROM JobDocAddress JDA JOIN OrgAddress OA ON OA.OA_PK = JDA.E2_OA_Address
        JOIN OrgHeader OH ON OH.OH_PK = OA.OA_OH
        WHERE JDA.E2_ParentID = JS.JS_PK AND JDA.E2_ParentTableCode = 'JS' AND JDA.E2_AddressType = 'CED'
    ) ImporterData
    WHERE REPLACE(JS.JS_HouseBill, ' ', '') = REPLACE(@BillOfLading, ' ', '')
       OR (LEN(REPLACE(@BillOfLading, ' ', '')) > 4 AND REPLACE(JS.JS_HouseBill, ' ', '') = RIGHT(REPLACE(@BillOfLading, ' ', ''), LEN(REPLACE(@BillOfLading, ' ', '')) - 4))
),
DeclarationSearch AS (
    SELECT TOP 1
        'CustomsDeclaration' AS TargetType,
        JE.JE_DeclarationReference AS TargetKey,
        CE.CE_EntryNum AS EntryNumber,
        JE.JE_HouseBill AS HouseBill, JE.JE_MasterBill AS MasterBill,
        CAST(NULL AS VARCHAR(35)) AS ConsolNumber,
        JE.JE_VesselName AS VesselName, JE.JE_VoyageFlightNo AS VoyageFlightNo,
        OH.OH_FullName AS Importer, JE.JE_PK AS RecordPK, 'JE' AS ParentTableCode
    FROM JobDeclaration JE
    LEFT JOIN CusEntryNum CE ON CE.CE_ParentID = JE.JE_PK AND CE.CE_ParentTable = 'JobDeclaration'
    LEFT JOIN OrgHeader OH ON OH.OH_PK = JE.JE_OH_Importer
    WHERE (REPLACE(JE.JE_HouseBill, ' ', '') = REPLACE(@BillOfLading, ' ', '')
        OR REPLACE(JE.JE_MasterBill, ' ', '') = REPLACE(@BillOfLading, ' ', ''))
      AND JE.JE_DeclarationReference IS NOT NULL AND JE.JE_DeclarationReference <> ''
)
SELECT TOP 1 TargetType, TargetKey, EntryNumber, HouseBill, MasterBill, ConsolNumber,
    VesselName, VoyageFlightNo, Importer,
    CASE WHEN EXISTS (
        SELECT 1 FROM JobRequiredDocument EQ
        WHERE EQ.EQ_ParentID = Combined.RecordPK AND EQ.EQ_ParentTableCode = Combined.ParentTableCode AND EQ.EQ_DocType = 'CAN'
    ) THEN 1 ELSE 0 END AS HasCAN
FROM (
    SELECT * FROM ConsolSearch
    UNION ALL SELECT * FROM ShipmentSearch WHERE NOT EXISTS (SELECT 1 FROM ConsolSearch)
    UNION ALL SELECT * FROM DeclarationSearch WHERE NOT EXISTS (SELECT 1 FROM ConsolSearch) AND NOT EXISTS (SELECT 1 FROM ShipmentSearch)
) Combined

---

AN Audit Sub-Workflow Queries

CW Comparison (TM1SzEe34ssgXRKc)

Fetches vessel, carrier, POL, POD, consignee from CW for comparison with parsed AN data.

• Compares fields → generates mismatch actions: update_cw, stop_processing, compare_only
• POD mismatch = critical stop

ETA & Routing (Zj3MAJ9wZa4QlDnX)

Fetches transport legs from JobConsolTransport.

• Variance = AN ETA − original ETA
• ≤3 days → delay notice; >3 days → courtesy notice
• Detects RAIL routing (JW_TransportMode = 'RAL') → adjusts ETA +7 days

Container Audit (kjaov3J6MaXvCJWG)

Per-container validation of packs/size against CW JobContainer.

• Checks for duplicates
• Container not found in CW = critical stop

Financial Audit (KtoUCulKnYfG9DKE, FRT only)

Checks AP existence (AccTransactionHeader), telex release (JobMilestone TLR code), cost variance (estimated vs local), posting eligibility.

---

AP Invoice Processor Queries (YYUjYczdRH8kIfAG)

8 parallel CW checks:

1. Find Purchasing Party — Match vendor in OrgHeader by name/code
2. Find by BL/Container — Consol/Shipment lookup (BL match pattern + container search)
3. Check Duplicate Invoice — AccTransactionHeader by invoice number
4. Check Payment Status — Posted AP lines in AccTransactionLines
5. Check Amount Tolerance — Expected vs actual cost from JobCharge
6. Check Currency Mismatch — Invoice currency vs CW charge currency
7. Check Tax IVA — 16% math validation (code node, no SQL)
8. Check Shipment Dates — ETD/ETA reasonableness from JobConsolTransport

Find Purchasing Party SQL (Node f1a00005)

SELECT TOP 10
    JH_JobNum,
    JR_Desc AS Descr,
    JR_LocalCostAmt AS Cost,
    CASE WHEN JR_AL_APLine IS NULL THEN 'No' ELSE 'Yes' END AS CostPosted,
    ap.AL_PostDate AS CostPostedDate,
    deb.OH_Code AS DebtorCode,
    deb.OH_FullName AS DebtorName,
    loc.OH_Code AS LocalClientCode,
    loc.OH_FullName AS LocalClientName,
    Ops.GS_FullName AS OpsRep,
    Vta.GS_FullName AS SalesRep
FROM jobheader
LEFT JOIN glbStaff Ops ON Ops.GS_Code = JH_GS_NKRepOps
LEFT JOIN glbStaff Vta ON Vta.GS_Code = JH_GS_NKRepSales
LEFT JOIN JobCharge ON JR_JH = JH_PK
LEFT JOIN AccTransactionLines ap ON ap.AL_PK = JR_AL_APLine
LEFT JOIN AccTransactionLines ar ON ar.AL_PK = JR_AL_ARLine
LEFT JOIN AccTransactionHeader ari ON ari.AH_PK = ap.AL_AH
LEFT JOIN OrgAddress locCli ON locCli.OA_PK = JH_OA_LocalChargesAddr
LEFT JOIN OrgHeader loc ON loc.OH_PK = locCli.OA_OH
LEFT JOIN OrgHeader deb ON deb.OH_PK = JR_OH_SellAccount
WHERE JH_SystemCreateTimeUtc >= DATEADD(YEAR, -5, GETDATE())
  AND (deb.OH_FullName LIKE '%{{ vendor_name }}%' OR deb.OH_Code = '{{ vendor_code }}')

Duplicate Invoice Check SQL (Node f1a00016)

SELECT TOP 1 AH.AH_PK, AH.AH_PostDate
FROM AccTransactionHeader AH
WHERE AH.AH_TransactionNum = @invoiceNumber AND AH.AH_OH = @vendorKey

Amount Tolerance Check SQL (Node f1a00018)

SELECT SUM(AL.AL_LineAmount) AS expected_amount
FROM AccTransactionLines AL
  JOIN AccTransactionHeader AH ON AL.AL_AH = AH.AH_PK
WHERE AH.AH_JH = @jobHeaderKey AND AH.AH_TransactionType = 'AP'

---

Planned Sales MC Queries (NOT YET BUILT)

Profit/Revenue per Salesperson per Month

SELECT
  OH.OH_Code, OH.OH_FullName AS Customer,
  GS.GS_Code AS SalesRepCode, GS.GS_FullName AS SalesRep,
  FORMAT(AH.AH_PostDate, 'yyyy-MM') AS PeriodDate,
  SUM(CASE WHEN AL.AL_LineType = 'REV' THEN AL.AL_LocalAmount ELSE 0 END) AS Revenue,
  SUM(CASE WHEN AL.AL_LineType IN ('REV','CST') THEN AL.AL_LocalAmount ELSE 0 END) AS Profit,
  COUNT(DISTINCT AH.AH_JH) AS FileCount,
  SUM(CASE WHEN AH.AH_OutstandingAmount > 0 THEN AH.AH_OutstandingAmount ELSE 0 END) AS InvoiceBalance,
  SUM(CASE WHEN AH.AH_OutstandingAmount > 0 AND AH.AH_DueDate < GETDATE()
      THEN AH.AH_OutstandingAmount ELSE 0 END) AS OverdueAmount
FROM AccTransactionHeader AH
JOIN AccTransactionLines AL ON AH.AH_PK = AL.AL_AH
JOIN OrgHeader OH ON AH.AH_OH = OH.OH_PK
LEFT JOIN glbStaff GS ON AH.AH_GS_SalesRep = GS.GS_PK
WHERE AH.AH_PostDate >= DATEADD(month, -12, GETDATE())
GROUP BY OH.OH_Code, OH.OH_FullName, GS.GS_Code, GS.GS_FullName, FORMAT(AH.AH_PostDate, 'yyyy-MM')

Containers/TEUs per Salesperson per Month

SELECT
  GS.GS_Code AS SalesRepCode,
  FORMAT(JK.JK_DatePortOfFirstArrival, 'yyyy-MM') AS PeriodDate,
  COUNT(DISTINCT JK.JK_UniqueConsignRef) AS BLCount,
  COUNT(JC.JC_ContainerNum) AS Containers,
  SUM(CASE WHEN RC.RC_TEU IS NOT NULL THEN RC.RC_TEU
       WHEN JC.JC_ContainerMode LIKE '%20%' THEN 1
       WHEN JC.JC_ContainerMode LIKE '%40%' THEN 2 ELSE 1 END) AS TEUs,
  COUNT(DISTINCT OH.OH_Code) AS CustomerCount
FROM JobConsol JK
JOIN JobConShipLink JN ON JK.JK_PK = JN.JN_JK
JOIN JobShipment JS ON JN.JN_JS = JS.JS_PK
LEFT JOIN JobContainer JC ON JK.JK_PK = JC.JC_JK
LEFT JOIN RefContainer RC ON JC.JC_RC = RC.RC_PK
LEFT JOIN OrgHeader OH ON JS.JS_OH_ImportBroker = OH.OH_PK
LEFT JOIN glbStaff GS ON JS.JS_OH_ExportBroker = GS.GS_PK
WHERE JK.JK_DatePortOfFirstArrival >= DATEADD(month, -12, GETDATE())
  AND JK.JK_TransportMode = 'SEA'
GROUP BY GS.GS_Code, FORMAT(JK.JK_DatePortOfFirstArrival, 'yyyy-MM')

Account Bridge — CWCode to OH_Code

SELECT OH.OH_Code, OH.OH_FullName, GS.GS_FullName AS SalesRep
FROM OrgHeader OH
LEFT JOIN glbStaff GS ON OH.OH_GS_SalesRep = GS.GS_PK
WHERE OH.OH_Code IS NOT NULL AND OH.OH_IsActive = 1

Note: These planned queries use some column names from design docs (AH_GS_SalesRep, OH_GS_SalesRep, JS_OH_Consignee) that may need verification against production DB. The handoff docs sometimes use simplified names.

---

Additional Columns Discovered (from plan files)

Table	Column	Type	Notes
JobHeader	JH_OA_LocalChargesAddr	UID	FK → OrgAddress (local client)
AccTransactionHeader	AH_GS_SalesRep	UID?	FK → GlbStaff (needs verification)
OrgHeader	OH_GS_SalesRep	UID?	FK → GlbStaff (needs verification)

---

n8n SQL Query Patterns

• Always prefix expressions with = in n8n MS SQL nodes
• Use {{ $json.field }} or {{ $('NodeName').item.json.field }} for variables
• TOP 1 for single-record returns
• OUTER APPLY for LEFT JOIN with TOP 1 subqueries
• Cast NULLs explicitly: CAST(NULL AS VARCHAR(35))
• n8n credential ID: [CREDENTIAL_ID]

---

Table Name Clarifications (RESOLVED)

Referenced As	Actual Table Name	Confirmed By
JobConsolContainer	JobContainer (JC)	PDF schema — JC_JK FK to JobConsol
AccTransactionLine (singular)	AccTransactionLines (plural)	PDF FK references
JobDocAddress	E2 prefix (not in PDF text, diagram-only)	CLAUDE.md + production SQL
JobMilestone	JM prefix (not in PDF text, diagram-only)	CLAUDE.md + production SQL

---

Power BI / DAX Queries (WSI Management Report)

Query 1: WSI Management Report (Main)

Source: Power Query (M) in Power BI Connection: jecprd.wisegrid.net / OdysseyJECPRDPurpose: Core financial/operational report — revenue, cost, profit per job with sales rep, debtor, agents, payment status

SQL Core

SELECT
    JH_JobNum,
    JH_IsActive,
    JH_IsValid,

    -- Representatives
    Ops.GS_FullName              AS OpsRep,
    Vta.GS_Code                  AS SalesCode,
    Vta.GS_FullName              AS SalesRep,

    -- Charge Details
    JR_Desc                      AS Descr,
    JR_LocalCostAmt              AS Cost,
    CASE
        WHEN JR_AL_APLine IS NULL THEN 'No'
        ELSE 'Yes'
    END                          AS CostPosted,
    ap.AL_PostDate               AS CostPostedDate,
    ari.AH_FullyPaidDate         AS PaidDate,
    JR_LocalSellAmt              AS Revenue,
    CASE
        WHEN JR_AL_ARLine IS NULL THEN 'No'
        ELSE 'Yes'
    END                          AS RevPosted,
    CAST(ar.AL_PostDate AS DATE) AS PostedDate,

    -- Debtor and Local Client
    deb.OH_Code                  AS DebtorCode,
    deb.OH_FullName              AS DebtorName,
    loc.OH_Code                  AS LocalClientCode,
    loc.OH_FullName              AS LocalClientName,

    -- AR Info
    ari.AH_Ledger                AS Ledger,
    ari.AH_TransactionNum        AS TransaccNum,
    ari.AH_OutstandingAmount     AS InvoiceBalance,

    -- Profit
    (-JR_LocalCostAmt + JR_LocalSellAmt) AS Profit,

    -- Agents
    SA.OH_Code                   AS SendAgentCode,
    SA.OH_FullName               AS SendAgentName,
    RA.OH_Code                   AS ReceiveAgentCode,
    RA.OH_FullName               AS ReceiveAgentName,

    -- Job & Shipment Info
    jobheader.JH_A_JCL,
    jobheader.JH_Status,
    JobShipment.JS_TransportMode,
    JobShipment.JS_PackingMode    AS TrasnportationType

FROM jobheader

-- Staff Links
LEFT JOIN glbStaff Ops        ON Ops.GS_Code = JH_GS_NKRepOps
LEFT JOIN glbStaff Vta        ON Vta.GS_Code = JH_GS_NKRepSales

-- Charges & Financials
LEFT JOIN JobCharge           ON JR_JH = JH_PK
LEFT JOIN AccTransactionLines ap ON ap.AL_PK = JR_AL_APLine
LEFT JOIN AccTransactionLines ar ON ar.AL_PK = JR_AL_ARLine
LEFT JOIN AccTransactionHeader ari ON ari.AH_PK = ar.AL_AH

-- Local Client & Debtor
LEFT JOIN OrgAddress locCli   ON locCli.OA_PK = JH_OA_LocalChargesAddr
LEFT JOIN OrgHeader loc       ON loc.OH_PK = locCli.OA_OH
LEFT JOIN OrgHeader deb       ON deb.OH_PK = JR_OH_SellAccount

-- Shipment & Consol Info
LEFT JOIN JobShipment         ON JS_PK = JH_ParentID
LEFT JOIN JobConShipLink      ON JN_JS = JS_PK
LEFT JOIN JobConsol           ON JK_PK = JN_JK

-- Agents
LEFT JOIN OrgAddress sendAgt  ON sendAgt.OA_PK = JK_OA_SendingForwarderAddress
LEFT JOIN OrgHeader SA        ON SA.OH_PK = sendAgt.OA_OH
LEFT JOIN OrgAddress receivAgt ON receivAgt.OA_PK = JK_OA_ReceivingForwarderAddress
LEFT JOIN OrgHeader RA        ON RA.OH_PK = receivAgt.OA_OH

WHERE JH_SystemCreateTimeUtc >= DATEADD(YEAR, -5, GETDATE())

Power Query Transforms (M)

1. Sales rep cleanup: "Peter Ku" → "House Account", "PKU" → "Home"
2. TransportationMode: JS_TransportMode if not null, else prefix logic: B→Brokerage, W→WorkItems, else→Other
3. Null handling: Cost/Revenue→0, SalesCode→"Home", SalesRep→"House Account", TrasnportationType→"Other", Credit Terms/Limit→0, InvoiceBalance→0
4. Join: Company and Credit Terms table (by DebtorCode=OrgCode) → Credit Limit, Credit Terms
5. RevenuePosted: Revenue only if RevPosted="Yes", else 0
6. Profit: RevenuePosted − Cost (only counts posted revenue)
7. Group by JH_JobNum: SUM(Cost, Revenue, Profit), MAX(text fields), FIRST(dates/status)
8. Type: For SEA→PackingMode (FCL/LCL), else→TransportationMode
9. PaymentStatus: InvoiceBalance > 0 → "Not Paid", else "Paid"
10. PaymentDays: ABS(PaidDate − PostedDate) in days
11. Join AllDates table (by JH_JobNum=Ref) → ETD, ETA
12. Join JobNotes table (by JH_JobNum=Reference) → AllNotes
13. Join JobNotes_Types table (by JH_JobNum=Reference) → LossApproval flag (default "FALSE")

External Tables Referenced (Power BI, not CW SQL)

Table	Join Key	Fields Used
Company and Credit Terms	DebtorCode = OrgCode	Credit Limit, Credit Terms
AllDates	JH_JobNum = Ref	ETD, ETA
JobNotes	JH_JobNum = Reference	AllNotes
JobNotes_Types	JH_JobNum = Reference	LossApproval

New Columns Discovered

Table	Column	Type	Notes
JobConsol	JK_OA_ReceivingForwarderAddress	UID	FK → OrgAddress (receiving agent)
JobHeader	JH_A_JCL	DT	Job Close date
JobHeader	JH_Status	VC(3)	Job status code
AccChargeCode	AC_PK	UID	Primary key
AccChargeCode	AC_Code	VC	Charge code (e.g., OFR, THC, DOC)
JobCharge	JR_AC	UID	FK → AccChargeCode.AC_PK

Query 2: AP/Cost (original, no ChargeCode)

Purpose: Cost side only — flipped to negative, appended with AR query for combined P&L view Pattern: CTE "Cost" → flip Cost * -1 as Value → append with AR query → join AllDates

SQL Core

WITH Cost AS (
    SELECT
        JH_JobNum, JH_IsActive, JH_IsValid,
        Ops.GS_FullName AS OpsRep,
        Vta.GS_Code AS SalesCode, Vta.GS_FullName AS SalesRep,
        JR_Desc AS Descr,
        JR_LocalCostAmt AS Cost,
        CASE WHEN JR_AL_APLine IS NULL THEN 'No' ELSE 'Yes' END AS CostPosted,
        ap.AL_PostDate AS CostPostedDate,
        ari.AH_FullyPaidDate AS PaidDate,
        deb.OH_Code AS DebtorCode, deb.OH_FullName AS DebtorName,
        loc.OH_Code AS LocalClientCode, loc.OH_FullName AS LocalClientName
    FROM jobheader
    LEFT JOIN glbStaff Ops ON Ops.GS_Code = JH_GS_NKRepOps
    LEFT JOIN glbStaff Vta ON Vta.GS_Code = JH_GS_NKRepSales
    LEFT JOIN JobCharge ON JR_JH = JH_PK
    LEFT JOIN AccTransactionLines ap ON ap.AL_PK = JR_AL_APLine
    LEFT JOIN AccTransactionLines ar ON ar.AL_PK = JR_AL_ARLine
    LEFT JOIN AccTransactionHeader ari ON ari.AH_PK = ap.AL_AH
    LEFT JOIN OrgAddress locCli ON locCli.OA_PK = JH_OA_LocalChargesAddr
    LEFT JOIN OrgHeader loc ON loc.OH_PK = locCli.OA_OH
    LEFT JOIN OrgHeader deb ON deb.OH_PK = JR_OH_SellAccount
    WHERE JH_SystemCreateTimeUtc >= DATEADD(YEAR, -5, GETDATE())
)
SELECT * FROM Cost;

Power Query Transforms

1. Null Cost → 0
2. Add Value = Cost * -1 (flip sign for combined view)
3. Remove original Cost column
4. Rename CostPostedDate → PostedDate, CostPosted → Posted
5. Append with AR query (Query 3)
6. Remove AR-only columns (Revenue, RevPosted, Ledger, TransaccNum, InvoiceBalance)
7. Join AllDates → ETD, ETA

Query 2b: AP/Cost with ChargeCode

Purpose: Same as Query 2 but adds AccChargeCode join for charge-level breakdown, groups by JH_JobNum + ChargeCode

SQL Core (diff from Query 2)

-- Same as Query 2, plus:
    LEFT JOIN AccChargeCode accCode ON accCode.AC_PK = JobCharge.JR_AC
-- Adds: accCode.AC_Code AS ChargeCode

Power Query Transforms (diff from Query 2)

• Same flip/append pattern as Query 2
• Group by {JH_JobNum, ChargeCode}: SUM(Value), MAX(ETA, ETD, LocalClientName, SalesRepCode, OpsRep)
• Join JobNotes → AllNotes
• Join JobNotes_Types → LossApproval

Query 3: AR/Revenue

Purpose: Revenue side — AR posting status, invoice balance, outstanding amounts. Used as append target by Cost queries.

SQL Core

WITH Revenue AS (
    SELECT
        JH_JobNum, JH_IsActive, JH_IsValid,
        Ops.GS_FullName AS OpsRep,
        Vta.GS_Code AS SalesCode, Vta.GS_FullName AS SalesRep,
        JR_Desc AS Descr,
        JR_LocalSellAmt AS Revenue,
        CASE WHEN JR_AL_ARLine IS NULL THEN 'No' ELSE 'Yes' END AS RevPosted,
        CAST(ar.AL_PostDate AS date) AS PostedDate,
        deb.OH_Code AS DebtorCode, deb.OH_FullName AS DebtorName,
        loc.OH_Code AS LocalClientCode, loc.OH_FullName AS LocalClientName,
        ari.AH_Ledger AS Ledger,
        ari.AH_TransactionNum AS TransaccNum,
        ari.AH_OutstandingAmount AS InvoiceBalance,
        accCode.AC_Code AS ChargeCode
    FROM jobheader
    LEFT JOIN glbStaff Ops ON Ops.GS_Code = JH_GS_NKRepOps
    LEFT JOIN glbStaff Vta ON Vta.GS_Code = JH_GS_NKRepSales
    LEFT JOIN JobCharge ON JR_JH = JH_PK
    LEFT JOIN AccTransactionLines ar ON ar.AL_PK = JR_AL_ARLine
    LEFT JOIN AccTransactionHeader ari ON ari.AH_PK = ar.AL_AH
    LEFT JOIN OrgAddress locCli ON locCli.OA_PK = JH_OA_LocalChargesAddr
    LEFT JOIN OrgHeader loc ON loc.OH_PK = locCli.OA_OH
    LEFT JOIN OrgHeader deb ON deb.OH_PK = JR_OH_SellAccount
    LEFT JOIN AccChargeCode accCode ON accCode.AC_PK = JobCharge.JR_AC
    WHERE JH_SystemCreateTimeUtc >= DATEADD(YEAR, -5, GETDATE())
)
SELECT * FROM Revenue;

Power Query Transforms

1. Add Value = Revenue (as-is, positive)
2. Null Value → 0
3. (This query is appended TO by Cost queries — see Query 2/2b)

Query 4: AP/Cost (raw, no transforms)

Purpose: Raw cost data, no Power Query transforms — direct SQL output

SQL Core

Same as Query 2 (no ChargeCode).

Query 5: JobNotes (full, with decompression)

Purpose: Extracts notes from StmNote for both JobShipment and JobDeclaration, decompresses binary note data, groups by reference. Used as join target by main report queries.

SQL Core

SELECT
    X.Reference,
    X.SourceTable,
    X.ST_Description,
    X.ST_NoteText AS ShortCodeText,
    X.ST_NoteData,
    X.ST_SystemCreateUser,
    X.ST_SystemCreateTimeUtc,
    X.ST_SystemLastEditUser,
    X.ST_SystemLastEditTimeUtc
FROM (
    SELECT
        JS.JS_UniqueConsignRef AS Reference,
        'JobShipment'          AS SourceTable,
        ST.ST_Description,
        CAST(ST.ST_NoteText AS VARCHAR(MAX)) AS ST_NoteText,
        ST.ST_NoteData,
        ST.ST_SystemCreateUser,
        ST.ST_SystemCreateTimeUtc,
        ST.ST_SystemLastEditUser,
        ST.ST_SystemLastEditTimeUtc
    FROM JobShipment JS
    JOIN StmNote ST
        ON ST.ST_ParentID = JS.JS_PK
        AND ST.ST_Table = 'JobShipment'

    UNION ALL

    SELECT
        JE.JE_DeclarationReference AS Reference,
        'JobDeclaration'           AS SourceTable,
        ST.ST_Description,
        CAST(ST.ST_NoteText AS VARCHAR(MAX)) AS ST_NoteText,
        ST.ST_NoteData,
        ST.ST_SystemCreateUser,
        ST.ST_SystemCreateTimeUtc,
        ST.ST_SystemLastEditUser,
        ST.ST_SystemLastEditTimeUtc
    FROM JobDeclaration JE
    JOIN StmNote ST
        ON ST.ST_ParentID = JE.JE_PK
        AND ST.ST_Table = 'JobDeclaration'
) X
ORDER BY X.Reference;

Power Query Transforms

1. Decompress: FxGetCleanNotes([ST_NoteData]) — custom M function that decompresses binary ST_NoteData
2. Filter: Remove rows where CleanedNoteBody is empty (images/garbage)
3. Build note block: Combine Description (+ SourceTable), ShortCodeText, CleanedNoteBody, Create user/date, LastModified user/date — separated by line breaks
4. Group by Reference: Count notes, list distinct SourceTables, combine all note blocks with *********** separator

New Table: StmNote (ST) — Notes/Memos

Column	Type	Notes
ST_ParentID	UID	FK → any parent (JS_PK, JE_PK, etc.)
ST_Table	VC	Parent table name ('JobShipment', 'JobDeclaration', etc.)
ST_Description	VC	Note type/category description
ST_NoteText	TEXT	Short code text (cast to VARCHAR(MAX))
ST_NoteData	VB	Compressed binary — requires decompression (Power BI: FxGetCleanNotes)
ST_SystemCreateUser	VC	Created by user
ST_SystemCreateTimeUtc	DT	Created timestamp
ST_SystemLastEditUser	VC	Last edited by user
ST_SystemLastEditTimeUtc	DT	Last edit timestamp

Query 5b: JobNotes (simplified, text only)

Purpose: Lightweight version — just Reference, SourceTable, Description, NoteText (no binary decompression)

SQL Core

SELECT JS.JS_UniqueConsignRef AS Reference,
       'JobShipment' AS SourceTable,
       ST.ST_Description AS NoteDescription,
       ST.ST_NoteText AS NoteText
FROM JobShipment JS
JOIN StmNote ST ON ST.ST_ParentID = JS.JS_PK AND ST.ST_Table = 'JobShipment'

UNION ALL

SELECT JE.JE_DeclarationReference AS Reference,
       'JobDeclaration' AS SourceTable,
       ST.ST_Description,
       ST.ST_NoteText
FROM JobDeclaration JE
JOIN StmNote ST ON ST.ST_ParentID = JE.JE_PK AND ST.ST_Table = 'JobDeclaration';

Query 6: JobNotes_Types (note type flag pivot)

Purpose: Pivots StmNote descriptions into boolean TRUE/FALSE columns per reference. Used by main report for LossApproval flag.

SQL Core

SELECT
    X.Reference,
    X.SourceTable,
    CASE WHEN MAX(CASE WHEN X.ST_Description = 'Agent Notes' THEN 1 ELSE 0 END) = 1 THEN 'TRUE' ELSE 'FALSE' END AS AgentNotes,
    CASE WHEN MAX(CASE WHEN X.ST_Description = 'Booking Notes' THEN 1 ELSE 0 END) = 1 THEN 'TRUE' ELSE 'FALSE' END AS BookingNotes,
    CASE WHEN MAX(CASE WHEN X.ST_Description = 'Client Visible Job Notes' THEN 1 ELSE 0 END) = 1 THEN 'TRUE' ELSE 'FALSE' END AS ClientVisibleJobNotes,
    CASE WHEN MAX(CASE WHEN X.ST_Description = 'Detailed Goods Description' THEN 1 ELSE 0 END) = 1 THEN 'TRUE' ELSE 'FALSE' END AS DetailedGoodsDescription,
    CASE WHEN MAX(CASE WHEN X.ST_Description = 'Import Delivery Instructions' THEN 1 ELSE 0 END) = 1 THEN 'TRUE' ELSE 'FALSE' END AS ImportDeliveryInstructions,
    CASE WHEN MAX(CASE WHEN X.ST_Description = 'Internal Work Notes' THEN 1 ELSE 0 END) = 1 THEN 'TRUE' ELSE 'FALSE' END AS InternalWorkNotes,
    CASE WHEN MAX(CASE WHEN X.ST_Description = 'Loss Approval' THEN 1 ELSE 0 END) = 1 THEN 'TRUE' ELSE 'FALSE' END AS LossApproval,
    CASE WHEN MAX(CASE WHEN X.ST_Description = 'Marks & Numbers' THEN 1 ELSE 0 END) = 1 THEN 'TRUE' ELSE 'FALSE' END AS MarksAndNumbers,
    CASE WHEN MAX(CASE WHEN X.ST_Description = 'Sailing Schedule' THEN 1 ELSE 0 END) = 1 THEN 'TRUE' ELSE 'FALSE' END AS SailingSchedule,
    CASE WHEN MAX(CASE WHEN X.ST_Description = 'Special Instructions' THEN 1 ELSE 0 END) = 1 THEN 'TRUE' ELSE 'FALSE' END AS SpecialInstructions,
    CASE WHEN MAX(CASE WHEN X.ST_Description NOT IN (
        'Agent Notes', 'Booking Notes', 'Client Visible Job Notes',
        'Detailed Goods Description', 'Import Delivery Instructions',
        'Internal Work Notes', 'Loss Approval', 'Marks & Numbers',
        'Sailing Schedule', 'Special Instructions'
    ) THEN 1 ELSE 0 END) = 1 THEN 'TRUE' ELSE 'FALSE' END AS OtherNotes
FROM (
    SELECT DISTINCT
        JS.JS_UniqueConsignRef AS Reference, 'JobShipment' AS SourceTable, ST.ST_Description
    FROM JobShipment JS
    JOIN StmNote ST ON ST.ST_ParentID = JS.JS_PK AND ST.ST_Table = 'JobShipment'
    UNION ALL
    SELECT DISTINCT
        JE.JE_DeclarationReference AS Reference, 'JobDeclaration' AS SourceTable, ST.ST_Description
    FROM JobDeclaration JE
    JOIN StmNote ST ON ST.ST_ParentID = JE.JE_PK AND ST.ST_Table = 'JobDeclaration'
) X
GROUP BY X.Reference, X.SourceTable;

Known StmNote Descriptions (ST_Description values)

Agent Notes, Booking Notes, Client Visible Job Notes, Detailed Goods Description, Import Delivery Instructions, Internal Work Notes, Loss Approval, Marks & Numbers, Sailing Schedule, Special Instructions

Query 7: Customs Declaration Report (Brokerage)

Purpose: Customs/brokerage operational report — declarations with dates, routing, cargo release status, docs received, commercial invoice counts. Parses JE_AddInfo delimited field for schedule dates and country of export.

SQL Core

WITH InvLineCounts AS (
    SELECT ji.ji_jz, COUNT(*) AS InvLinCount
    FROM JobComInvoiceLine ji
    GROUP BY ji.ji_jz
)
SELECT
    JE.JE_SystemCreateTimeUtc,
    JE.JE_DeclarationReference,
    JE.JE_OH_Importer, JE.JE_OH_Supplier,
    Importer.OH_FullName AS ImporterName,
    JE.JE_MessageStatus,
    JE.JE_EntrySubmittedDate,
    JE.JE_EntryAuthorisationDate,
    JE.JE_OH_ShippingLine, JE.JE_OH_Forwarder,
    JE.JE_RL_NKOrigin, JE.JE_RL_NKPortOfLoading,
    JE.JE_ExportDate,
    JE.JE_RL_NKPortOfArrival, JE.JE_DateOfArrival,
    JE.JE_RL_NKFinalDestination, JE.JE_DateAtFinalDestination,
    JE.JE_VesselName, JE.JE_MasterBill, JE.JE_HouseBill,
    JE.JE_ContainerCount, JE.JE_MessageType,

    -- Parse JE_AddInfo delimited field (*key=value* format)
    -- SchDArrival
    CASE WHEN CHARINDEX('*SchDArrival=', '*' + JE.JE_AddInfo + '*') > 0 THEN
        SUBSTRING('*' + JE.JE_AddInfo + '*',
            CHARINDEX('*SchDArrival=', '*' + JE.JE_AddInfo + '*') + 13,
            CHARINDEX('*', '*' + JE.JE_AddInfo + '*',
                CHARINDEX('*SchDArrival=', '*' + JE.JE_AddInfo + '*') + 13)
            - (CHARINDEX('*SchDArrival=', '*' + JE.JE_AddInfo + '*') + 13))
    ELSE NULL END AS SchDArrival,

    -- SchDLoading
    CASE WHEN CHARINDEX('*SchDLoading=', '*' + JE.JE_AddInfo + '*') > 0 THEN
        SUBSTRING('*' + JE.JE_AddInfo + '*',
            CHARINDEX('*SchDLoading=', '*' + JE.JE_AddInfo + '*') + 13,
            CHARINDEX('*', '*' + JE.JE_AddInfo + '*',
                CHARINDEX('*SchDLoading=', '*' + JE.JE_AddInfo + '*') + 13)
            - (CHARINDEX('*SchDLoading=', '*' + JE.JE_AddInfo + '*') + 13))
    ELSE NULL END AS SchDLoading,

    -- UC_NKCountryOfExport
    CASE WHEN CHARINDEX('*UC_NKCountryOfExport=', '*' + JE.JE_AddInfo + '*') > 0 THEN
        SUBSTRING('*' + JE.JE_AddInfo + '*',
            CHARINDEX('*UC_NKCountryOfExport=', '*' + JE.JE_AddInfo + '*') + 22,
            CHARINDEX('*', '*' + JE.JE_AddInfo + '*',
                CHARINDEX('*UC_NKCountryOfExport=', '*' + JE.JE_AddInfo + '*') + 22)
            - (CHARINDEX('*UC_NKCountryOfExport=', '*' + JE.JE_AddInfo + '*') + 22))
    ELSE NULL END AS UC_NKCountryOfExport,

    -- Cargo Release Status
    CASE
        WHEN CHARINDEX('*CertifyCargoRelease=Y*', '*' + JE.JE_AddInfo + '*') > 0 THEN 'Released'
        WHEN CHARINDEX('*CertifyCargoRelease=N*', '*' + JE.JE_AddInfo + '*') > 0 THEN 'Not Released'
        ELSE 'Unknown'
    END AS CargoReleaseStatusDesc,

    -- Docs Received (Commercial Invoice or Clearance Doc Set)
    CASE WHEN MAX(CASE WHEN EQ.EQ_DocDescription IN ('Commercial Invoice', 'Clearance Doc Set') THEN 1 ELSE 0 END) = 1
        THEN 'Yes' ELSE 'No'
    END AS DocsReceived,

    COUNT(DISTINCT JZ.JZ_InvoiceNumber) AS InvH,
    ISNULL(SUM(ILC.InvLinCount), 0) AS InvLin

FROM JobDeclaration JE
LEFT JOIN OrgHeader Importer ON JE.JE_OH_Importer = Importer.OH_PK
LEFT JOIN JobDocsAndCartage JDC ON JDC.JP_ParentID = JE.JE_PK
LEFT JOIN JobRequiredDocument EQ ON EQ.EQ_ParentID = JDC.JP_PK
LEFT JOIN JobComInvoiceHeader JZ ON JZ.JZ_JE = JE.JE_PK AND JZ.JZ_InvoiceNumber != 'All Invoices'
LEFT JOIN InvLineCounts ILC ON ILC.ji_jz = JZ.JZ_PK
WHERE JE.JE_SystemCreateTimeUtc >= '2022-04-01'
  AND JE.JE_IsCancelled = 'FALSE'
GROUP BY
    JE.JE_SystemCreateTimeUtc, JE.JE_DeclarationReference,
    JE.JE_OH_Importer, JE.JE_OH_Supplier, Importer.OH_FullName,
    JE.JE_MessageStatus, JE.JE_EntrySubmittedDate, JE.JE_EntryAuthorisationDate,
    JE.JE_OH_ShippingLine, JE.JE_OH_Forwarder,
    JE.JE_RL_NKOrigin, JE.JE_RL_NKPortOfLoading, JE.JE_ExportDate,
    JE.JE_RL_NKPortOfArrival, JE.JE_DateOfArrival,
    JE.JE_RL_NKFinalDestination, JE.JE_DateAtFinalDestination,
    JE.JE_VesselName, JE.JE_MasterBill, JE.JE_HouseBill,
    JE.JE_ContainerCount, JE.JE_MessageType, JE.JE_AddInfo;

Power Query Transforms

1. Add ExportCountryCode = first 2 chars of JE_RL_NKOrigin
2. Filter: JE_OH_Importer not null
3. Filter: JE_MessageType = "IMP" (imports only)
4. Join NoContainerDeclaration table (by DeclarationReference) → ContainerCount override

New Tables Discovered

Table	Prefix	Notes
JobComInvoiceHeader	JZ	Commercial invoice headers, FK JZ_JE → JobDeclaration
JobComInvoiceLine	JI	Commercial invoice lines, FK JI_JZ → JobComInvoiceHeader
JobDocsAndCartage	JP	Docs & cartage link, FK JP_ParentID → JobDeclaration

New JobDeclaration Columns Discovered

Column	Type	Notes
JE_MessageStatus	VC	ACE/ABI message status
JE_EntrySubmittedDate	DT	Date entry submitted to customs
JE_EntryAuthorisationDate	DT	Date entry authorized/released
JE_ExportDate	DT	Export date
JE_DateAtFinalDestination	DT	Date at final destination
JE_MessageType	VC	IMP/EXP message type
JE_AddInfo	VC	Delimited field — *key=value* format with SchDArrival, SchDLoading, UC_NKCountryOfExport, CertifyCargoRelease

JE_AddInfo Known Keys

Key	Example Value	Notes
SchDArrival	date string	Scheduled arrival date
SchDLoading	date string	Scheduled loading date
UC_NKCountryOfExport	2-letter code	Country of export
CertifyCargoRelease	Y/N	Cargo release certified

Query 8: NoContainerDeclaration (CusContainer count)

Purpose: Counts valid containers per declaration from CusContainer table — used as override for JE_ContainerCount (which can be inaccurate). Joined by Query 7.

SQL Core

SELECT
    jd.JE_DeclarationReference,
    COUNT(cc.CO_PK) AS ContainerCount
FROM dbo.CusContainer cc
LEFT JOIN dbo.JobDeclaration jd ON cc.CO_JE = jd.JE_PK
WHERE cc.CO_IsValid = 1
GROUP BY jd.JE_DeclarationReference

New Table: CusContainer (CO) — Customs Containers

Column	Type	Notes
CO_PK	UID	Primary key
CO_JE	UID	FK → JobDeclaration.JE_PK
CO_IsValid	BIT	Valid flag

Note: This is separate from JobContainer (JC) which is on the forwarding/consol side. CusContainer is on the customs/declaration side.

Query 9: Parts & Tariff (HTS codes)

Purpose: Supplier parts with HTS classification, packaging, and weight data. Uses implicit join on OrgHeader.

SQL Core

SELECT
    OP_PartNum, OP_Desc,
    OH_Code, OH_FullName,
    OU_Relationship,
    OP_Weight AS Gross, OP_NetWeight, OP_WeightUQ,
    OP_Cubic, OP_CubicUQ, OP_StockKeepingUnit,
    OF_QuantityInParent, OF_PackType, OF_ParentPackType,
    CI_ChildType,
    CASE
        WHEN CI_ChildType = 'HTI' THEN 'HTS IMPORT'
        WHEN CI_ChildType = 'HTE' THEN 'HTS EXPORT'
        WHEN CI_ChildType = 'SHB' THEN 'SCHEDULE B'
    END AS TypeDesc,
    CI_TariffNum, CI_SupplementalTariff,
    CI_RN_NKCountryOfExport, CI_RN_NKCountryOfOrigin
FROM OrgHeader, OrgPartRelation
    LEFT JOIN OrgSupplierPart ON OrgSupplierPart.OP_PK = OrgPartRelation.OU_OP
    LEFT JOIN OrgPartUnit ON OrgPartUnit.OF_OP = OrgSupplierPart.OP_PK
    LEFT JOIN CusClassPartPivot ON CusClassPartPivot.CI_OP = OP_PK AND CI_CI_Parent IS NOT NULL
WHERE OrgPartRelation.OU_OH = OH_PK

New Column Discovered

Table	Column	Type	Notes
CusClassPartPivot	CI_CI_Parent	UID	FK → self (parent classification) — filter IS NOT NULL gets leaf-level tariffs only

Query 10: ISF Filing Report

Purpose: ISF (Importer Security Filing / 10+2) data with MBL, OBL, importer, carrier, routing. Filters out cancelled and REUSE records, deduplicates by JobReference.

SQL Core

SELECT
    BF_JobReference,
    mbl.BB_BillNum AS MBLNo,
    obl.BB_BillNum AS OBNo,
    importer.OH_Code AS ImporterCode,
    importer.OH_FullName AS ImporterName,
    consol.JW_RL_NKLoadPort,
    consol.JW_RL_NKDiscPort,
    consol.JW_ETD,
    consol.JW_ETA,
    carrier.OH_Code AS CarrierCode,
    carrier.OH_FullName AS CarrierName,
    BF_IsCancelled,
    BF_OwnerReference
FROM cusISFHeader
LEFT JOIN cusISFBill mbl ON mbl.BB_BF = BF_PK AND mbl.BB_BillType = 'MB'
LEFT JOIN cusISFBill obl ON obl.BB_BF = BF_PK AND obl.BB_BillType = 'OB'
LEFT JOIN OrgHeader importer ON BF_OH_Importer = OH_PK
LEFT JOIN JobConsolTransport consol ON JW_ParentGuid = BF_PK
    AND JW_ParentType = 'ISF' AND JW_TransportType = 'MAI'
LEFT JOIN OrgHeader carrier ON carrier.OH_PK IN (
    SELECT OA_OH FROM OrgAddress WHERE OA_PK = consol.JW_OA_CarrierAddress
)

Power Query Transforms

1. Filter: BF_JobReference not null and not empty
2. Distinct by BF_JobReference
3. Filter: BF_IsCancelled = false AND BF_OwnerReference != "REUSE"

New Tables Discovered

cusISFHeader (BF) — ISF Filing Header

Column	Type	Notes
BF_PK	UID	Primary key
BF_JobReference	VC	Job reference (links to CW job)
BF_OH_Importer	UID	FK → OrgHeader (importer)
BF_IsCancelled	BIT	Cancelled flag
BF_OwnerReference	VC	Owner ref — "REUSE" = template/reused record

cusISFBill (BB) — ISF Bill Numbers

Column	Type	Notes
BB_BF	UID	FK → cusISFHeader.BF_PK
BB_BillNum	VC	Bill of lading number
BB_BillType	VC	'MB' = Master BL, 'OB' = Ocean/Original BL

Key Pattern: JobConsolTransport reused for ISF

JW_ParentType = 'ISF' — the transport legs table is polymorphic (also used for 'CON' consols, 'SHP' shipments). JW_TransportType = 'MAI' filters to main leg only.

Query 11: Container/TEU Report (enhanced, with trade lanes)

Purpose: Container count + TEU calculation per job, with customer/shipper/agent, origin/destination, grouped by JobNum. Includes trade lane names via UNLOCO lookup.

SQL Core

SELECT
    JH_JobNum, JS_HouseBill, JK_UniqueConsignRef,
    OrgHeaderCLI.OH_FullName AS Customer,
    OrgHeaderCLI.OH_Code AS CustomerCode,
    OrgHeaderSHP.OH_FullName AS Shipper,
    JC_ContainerNum, RC_Code,
    JS_RL_NKOrigin, JS_RL_NKDestination,
    JS_E_DEP, JS_E_ARV,
    OrgHeaderAGT.OH_FullName AS Agent,
    JH_GS_NKRepSales AS SalesRep,
    JH_GS_NKRepOps AS OpsRep,
    JobShipment.JS_TransportMode AS TransportMode,
    JobShipment.JS_PackingMode AS TrasnportationType
FROM JobShipment
LEFT JOIN JobConShipLink ON JN_JS = JS_PK
LEFT JOIN JobConsol ON JK_PK = JN_JK
JOIN JobContainer ON JC_JK = JK_PK
LEFT JOIN RefContainer ON RC_PK = JC_RC

-- Customer (Consignee) via JobDocAddress
LEFT JOIN (
    dbo.JobDocAddress JobDocAddressCLI
    LEFT JOIN dbo.OrgAddress OrgAddressCLI ON JobDocAddressCLI.E2_OA_Address = OrgAddressCLI.OA_PK
    LEFT JOIN dbo.OrgHeader OrgHeaderCLI ON OrgAddressCLI.OA_OH = OrgHeaderCLI.OH_PK
) ON JobDocAddressCLI.E2_ParentID = JS_PK AND JobDocAddressCLI.E2_AddressType = 'CED'

-- Shipper via JobDocAddress
LEFT JOIN (
    dbo.JobDocAddress JobDocAddressSHP
    LEFT JOIN dbo.OrgAddress OrgAddressSHP ON JobDocAddressSHP.E2_OA_Address = OrgAddressSHP.OA_PK
    LEFT JOIN dbo.OrgHeader OrgHeaderSHP ON OrgAddressSHP.OA_OH = OrgHeaderSHP.OH_PK
) ON JobDocAddressSHP.E2_ParentID = JS_PK AND JobDocAddressSHP.E2_AddressType = 'CRD'

-- Agent via SendingForwarderAddress
LEFT JOIN (
    dbo.OrgAddress OrgAddressAGT
    LEFT JOIN dbo.OrgHeader OrgHeaderAGT ON OrgHeaderAGT.OH_PK = OrgAddressAGT.OA_OH
) ON OrgAddressAGT.OA_PK = JK_OA_SendingForwarderAddress

LEFT JOIN JobHeader ON JH_ParentId = JS_PK
WHERE JH_SystemCreateTimeUtc >= DATEADD(YEAR, -5, GETDATE())

Power Query Transforms

1. Filter: RC_Code not null, TrasnportationType != "LCL"
2. Filter: JH_JobNum not null/empty
3. TEU calculation:
   • LCL → 0, Air → 0, ROA → 0
   • RC_Code contains "40" → 2, "20" → 1, "45" → 2.25, else → 0
4. Group by JH_JobNum: MAX(text fields), SUM(TEU), COUNT(rows)
5. Port code fix: CNQIN → CNTAO (Qingdao consolidation)
6. Empty SalesRep → "WSI"
7. Add Trade Lane = Origin + "-" + Destination
8. Join UNLOCO-Destination and UNLOCO-Origin tables → get port name + country for readable trade lane

JobDocAddress Address Types Used

Code	Role	Notes
CED	Consignee/Customer	Consignee address
CRD	Shipper/Consignor	Creditor/shipper address
SHP	Shipper	(used in other queries)

External Tables Referenced (Power BI)

Table	Join Key	Fields
UNLOCO-Destination	JS_RL_NKDestination = RL_Code	Port Name, Country
UNLOCO-Origin	JS_RL_NKOrigin = RL_Code	Port Name Country

Key Join Pattern: JobDocAddress subquery joins

LEFT JOIN (JobDocAddress LEFT JOIN OrgAddress LEFT JOIN OrgHeader)
  ON E2_ParentID = JS_PK AND E2_AddressType = 'XXX'

This pattern is used to get customer (CED), shipper (CRD), and potentially other address roles.

Query 12: AllDates (ETD source)

Purpose: Extracts ETD dates per job — one of the data sources for the AllDates Power BI table. Renamed to Date/Type columns for UNION with ETA source.

SQL Core

SELECT JH_JobNum, JS_E_DEP, 'ETD', OrgHeaderCLI.OH_Code AS CustomerCode
FROM JobShipment
LEFT JOIN JobConShipLink ON JN_JS = JS_PK
LEFT JOIN JobConsol ON JK_PK = JN_JK
JOIN JobContainer ON JC_JK = JK_PK
LEFT JOIN RefContainer ON RC_PK = JC_RC
LEFT JOIN (dbo.JobDocAddress JobDocAddressCLI
    LEFT JOIN dbo.OrgAddress OrgAddressCLI ON JobDocAddressCLI.E2_OA_Address = OrgAddressCLI.OA_PK
    LEFT JOIN dbo.OrgHeader OrgHeaderCLI ON OrgAddressCLI.OA_OH = OrgHeaderCLI.OH_PK
) ON JobDocAddressCLI.E2_ParentID = JS_PK AND JobDocAddressCLI.E2_AddressType = 'CED'
LEFT JOIN (dbo.JobDocAddress JobDocAddressSHP
    LEFT JOIN dbo.OrgAddress OrgAddressSHP ON JobDocAddressSHP.E2_OA_Address = OrgAddressSHP.OA_PK
    LEFT JOIN dbo.OrgHeader OrgHeaderSHP ON OrgAddressSHP.OA_OH = OrgHeaderSHP.OH_PK
) ON JobDocAddressSHP.E2_ParentID = JS_PK AND JobDocAddressSHP.E2_AddressType = 'CRD'
LEFT JOIN (dbo.OrgAddress OrgAddressAGT
    LEFT JOIN OrgHeader OrgHeaderAGT ON OrgHeaderAGT.OH_PK = OrgAddressAGT.OA_OH
) ON OrgAddressAGT.OA_PK = JK_OA_SendingForwarderAddress
LEFT JOIN JobHeader ON JH_ParentId = JS_PK
WHERE JH_SystemCreateTimeUtc >= DATEADD(YEAR, -5, GETDATE())

Power Query Transforms

1. Rename JS_E_DEP → Date, literal 'ETD' column → Type
2. (Likely UNIONed with an ETA query to form the full AllDates table, with Ref = JH_JobNum)

Query 13: Company and Credit Terms

Purpose: Organization credit limits and AR payment terms. This is the Company and Credit Terms Power BI table joined by the main report (Query 1).

SQL Core

SELECT
    oh.OH_PK AS OrgID,
    oh.OH_Code AS OrgCode,
    oh.OH_FullName AS OrgName,
    ob.OB_ARCreditLimit AS CreditLimit,
    py.PY_JobType,
    py.PY_GB_Branch,
    py.PY_GE_Department,
    py.PY_Direction,
    py.PY_TransportMode,
    py.PY_InvoiceClass,
    py.PY_InvoiceTerm AS Terms,
    py.PY_InvoiceDays AS TermDays
FROM OrgHeader oh
JOIN OrgCompanyData ob ON ob.OB_OH = oh.OH_PK
LEFT JOIN OrgARTerms py ON py.PY_OB = ob.OB_PK
WHERE oh.OH_IsActive = 1
ORDER BY oh.OH_Code, py.PY_JobType;

Power Query Transforms

1. Group by {OrgCode, OrgName, OrgID}: MAX(CreditLimit), MAX(TermDays)
2. Null Credit Terms → 0

New Tables Discovered

OrgCompanyData (OB) — Organization Company/Financial Data

Column	Type	Notes
OB_PK	UID	Primary key
OB_OH	UID	FK → OrgHeader.OH_PK
OB_ARCreditLimit	MN	AR credit limit

OrgARTerms (PY) — AR Payment Terms

Column	Type	Notes
PY_PK	UID	Primary key
PY_OB	UID	FK → OrgCompanyData.OB_PK
PY_JobType	VC	Job type filter
PY_GB_Branch	UID	FK → GlbBranch
PY_GE_Department	UID	FK → GlbDepartment
PY_Direction	VC	Direction (IMP/EXP)
PY_TransportMode	VC	Transport mode filter
PY_InvoiceClass	VC	Invoice class
PY_InvoiceTerm	VC	Payment term description
PY_InvoiceDays	I	Net days for payment

Query 14: UNLOCO Lookup (with lat/lon)

Purpose: RefUNLOCO port reference with coordinates. Powers the UNLOCO-Destination and UNLOCO-Origin Power BI tables used by Query 11 for trade lane names.

SQL Core

SELECT
    RL_Code,
    RL_RN_NKCountryCode,
    RL_PortName,
    RL_GeoLocation.Lat AS Latitud,
    RL_GeoLocation.Long AS Longitud
FROM RefUNLOCO

Power Query Transforms

1. Add Port Name Country = RL_PortName + ", " + RL_RN_NKCountryCode

Note on GeoLocation

RL_GeoLocation is a SQL Server geography type (GPY). Access lat/lon via .Lat and .Long properties in SQL.

Query 15: JobConsol Raw (consol-level data)

Purpose: Raw consol records — MBL, routing, dates, transport mode. No transforms.

SQL Core

SELECT
    JK_UniqueConsignRef, JK_TransportMode, JK_MasterBillNum,
    JK_DateLastForeignPort, JK_DatePortOfFirstArrival,
    JK_RL_NKLoadPort, JK_RL_NKDischargePort,
    JK_RL_NKLastForeignPort, JK_RL_NKPortOfFirstArrival,
    JK_SystemCreateTimeUtc, JK_SystemLastEditTimeUtc
FROM JobConsol
-- WHERE JK_TransportMode = 'SEA'  (commented out — all modes)

Query 16: Customs Bond Charges

Purpose: Customs bond charge lines (CUSBOND charge code), with WorkItem summary for context. Calculates profit per line, classifies Annual Bond vs Other.

SQL Core

SELECT
    AC.AC_Code AS ChargeCode,
    JH.JH_JobNum AS JobNum,
    OH.OH_FullName AS LocalClient,
    JC.JR_Desc AS LineChargeDescription,
    JC.JR_LocalCostAmt AS LineLocalCost,
    JC.JR_LocalSellAmt AS LineLocalSell,
    WI.WKI_Summary,
    JC.JR_APInvoiceDate AS InvoiceDate
FROM JobCharge JC
JOIN JobHeader JH ON JC.JR_JH = JH.JH_PK
LEFT JOIN OrgAddress OA ON JH.JH_OA_LocalChargesAddr = OA.OA_PK
LEFT JOIN OrgHeader OH ON OA.OA_OH = OH.OH_PK
LEFT JOIN WorkItem WI ON WI.WKI_WorkItemNumber = JH.JH_JobNum
LEFT JOIN AccChargeCode AC ON JC.JR_AC = AC_PK

Power Query Transforms

1. Add Profit = LineLocalSell - LineLocalCost
2. Add Type = "Annual Bond" if description contains "Annual Bond", else "Other"
3. Filter: ChargeCode = "CUSBOND"

New Table: WorkItem (WKI)

Column	Type	Notes
WKI_WorkItemNumber	VC	Work item number — matches JH_JobNum for W-prefix jobs
WKI_Summary	VC	Work item summary/description

WorkItem links to JobHeader via WKI_WorkItemNumber = JH_JobNum (string match, not FK).

Query 17: Customs Bond Details

Purpose: Bond registry — bond numbers, types, surety, amounts, effective/expiry dates per organization. Filtered to non-empty bond numbers.

SQL Core

SELECT
    PW.PW_ApplicationCode,
    PW.PW_ActivityCode,
    PW.PW_BondType,
    PW.PW_BondNumber,
    PW.PW_SuretyCode,
    PW.PW_BondAmount,
    PW.PW_BondEffectiveDate,
    PW.PW_BondExpiryDate,
    PW.PW_BondFiledPort,
    PW.PW_SystemCreateTimeUtc,
    OH.OH_Code,
    OH.OH_FullName
FROM CusBondDetail PW
LEFT JOIN OrgHeader OH ON PW.PW_ParentID = OH.OH_PK;

Power Query Transforms

1. Filter: PW_BondNumber not empty

New Table: CusBondDetail (PW) — Customs Bond Registry

Column	Type	Notes
PW_ParentID	UID	FK → OrgHeader.OH_PK (bond holder)
PW_ApplicationCode	VC	Application code
PW_ActivityCode	VC	Activity code
PW_BondType	VC	Bond type (continuous, single entry, etc.)
PW_BondNumber	VC	Bond number
PW_SuretyCode	VC	Surety company code
PW_BondAmount	MN	Bond amount
PW_BondEffectiveDate	DT	Effective date
PW_BondExpiryDate	DT	Expiry date
PW_BondFiledPort	VC	Port where bond was filed
PW_SystemCreateTimeUtc	DT	Created timestamp

Query 18: Declaration Docs & Invoice Counts (monthly)

Purpose: Per-declaration document descriptions with commercial invoice header/line counts. Filtered by arrival date range (parameterized month).

SQL Core

SELECT
    JE_DeclarationReference,
    EQ_DocDescription,
    COUNT(JZ_InvoiceNumber) AS InvH,
    (SELECT COUNT(JI_description)
     FROM JobComInvoiceLine WHERE JI_JZ = JZ_PK) AS InvLin
FROM JobDeclaration
LEFT JOIN JobDocsAndCartage ON JP_ParentID = JE_PK
LEFT JOIN JobRequiredDocument ON EQ_ParentID = JP_PK
LEFT JOIN JobComInvoiceHeader ON JZ_JE = JE_PK
    AND JZ_InvoiceNumber != 'All Invoices'
WHERE JE_DateOfArrival >= '2025-04-01'
  AND JE_DateOfArrival <= '2025-04-30'
GROUP BY JE_DeclarationReference, EQ_DocDescription, JZ_PK

Key Pattern

• JobDocsAndCartage (JP) sits between JobDeclaration and JobRequiredDocument — docs are NOT direct children of declarations, they go through the JP intermediary
• Correlated subquery for invoice line count per header (JZ_PK)
• JZ_InvoiceNumber != 'All Invoices' — filters out the summary/rollup row

New Column: JobComInvoiceLine

Column	Type	Notes
JI_Description	VC	Line item description
JI_JZ	UID	FK → JobComInvoiceHeader.JZ_PK

Query 19: Power of Attorney (POA) Documents

Purpose: POA documents per organization — tracks which clients have POA on file and when received. Grouped to get latest date per org + doc description.

SQL Core

SELECT OH_FullName, EQ_DocDescription, EQ_DateReceived
FROM OrgHeader
LEFT JOIN JobRequiredDocument ON EQ_ParentID = OH_PK
WHERE EQ_DocType = 'POA'
ORDER BY OH_FullName

Power Query Transforms

1. Group by {OH_FullName, EQ_DocDescription}: MAX(EQ_DateReceived)

Key Pattern

JobRequiredDocument can link directly to OrgHeader (EQ_ParentID = OH_PK) — not just jobs. This means EQ is truly polymorphic: parents can be JS, JK, JE, JP, or OH.

Query 20: Order References (Declaration + Shipment)

Purpose: Aggregates owner/order references per declaration and shipment. Used for PO number lookup in reports.

SQL Core

-- Declaration owner references
SELECT
    JE_DeclarationReference AS Reference,
    STRING_AGG(JE_OwnerRef, ', ') AS AggregatedData,
    'DeclarationOwnerRef' AS DataType
FROM dbo.JobDeclaration
GROUP BY JE_DeclarationReference

UNION ALL

-- Shipment order references (via JobOrderItem)
SELECT
    js.JS_UniqueConsignRef AS Reference,
    STRING_AGG(joi.JT_OrderReference, ', ') AS AggregatedData,
    'ShipmentOrderRef' AS DataType
FROM dbo.JobOrderItem joi
INNER JOIN dbo.JobDocsAndCartage jdc ON joi.JT_JP = jdc.JP_PK
INNER JOIN dbo.JobShipment js ON jdc.JP_ParentID = js.JS_PK
WHERE js.JS_SystemCreateTimeUtc >= DATEADD(YEAR, -5, GETDATE())
GROUP BY js.JS_UniqueConsignRef;

Power Query Transforms

1. Add OrderReferences = "Ref: " + AggregatedData
2. Filter: OrderReferences not empty, AggregatedData not empty
3. Distinct by

New Tables/Columns Discovered

Table	Column	Type	Notes
JobDeclaration	JE_OwnerRef	VC	Owner/PO reference on declaration
JobOrderItem	JT_JP	UID	FK → JobDocsAndCartage.JP_PK
JobOrderItem	JT_OrderReference	VC	Purchase order / order reference

JobOrderItem (JT) links to shipments through JobDocsAndCartage (JP), not directly.

Query 21: JobDocsAndCartage (SELECT *)

Purpose: Full dump of JobDocsAndCartage table for schema discovery. Lowercases PK for join compatibility.

SQL Core

SELECT * FROM JobDocsAndCartage

Power Query Transforms

1. Lowercase JP_PK (text conversion for case-insensitive joins)

TODO: Document full JP column list from result set.

Query 22: Declaration Docs & Invoice Counts (optimized CTE)

Purpose: Same as Query 18 but optimized — uses CTE for invoice line counts instead of correlated subquery. Groups to one row per declaration with DocsReceived flag.

SQL Core

WITH InvLineCounts AS (
    SELECT ji.ji_jz, COUNT(*) AS InvLinCount
    FROM JobComInvoiceLine ji
    GROUP BY ji.ji_jz
)
SELECT
    je.je_declarationreference,
    MAX(CASE
        WHEN eq.eq_docdescription IN ('Commercial Invoice', 'Clearance Doc Set') THEN 'Yes'
        ELSE NULL
    END) AS DocsReceived,
    COUNT(DISTINCT jz.jz_invoicenumber) AS InvH,
    SUM(ISNULL(ilc.InvLinCount, 0)) AS InvLin
FROM JobDeclaration je
LEFT JOIN JobDocsAndCartage jdc ON jdc.jp_parentid = je.je_pk
LEFT JOIN JobRequiredDocument eq ON eq.eq_parentid = jdc.jp_pk
LEFT JOIN JobComInvoiceHeader jz ON jz.jz_je = je.je_pk
    AND jz.jz_invoicenumber != 'All Invoices'
LEFT JOIN InvLineCounts ilc ON ilc.ji_jz = jz.jz_pk
WHERE je.je_dateofarrival >= '2025-04-01'
  AND je.je_dateofarrival <= '2025-04-30'
GROUP BY je.je_declarationreference;

Cleaner than Query 18 — one row per declaration, no correlated subquery.

Query 23: Air Shipment Report (weight/volume)

Query 24: LCL Shipment Report (weight/volume)

Purpose: Shipment-level weight, volume, chargeable weight, packs with customer/shipper/agent. Same SQL pattern, different WHERE filter. Air = JS_TransportMode = 'AIR', LCL = JS_TransportMode = 'SEA' AND JS_PackingMode = 'LCL'. LCL version also adds JS_ShipmentStatus.

SQL Core (shared)

SELECT
    JH_JobNum, JS_HouseBill, JK_UniqueConsignRef,
    OrgHeaderCLI.OH_FullName AS Customer,
    OrgHeaderCLI.OH_Code AS CustomerCode,
    OrgHeaderSHP.OH_FullName AS Shipper,
    JobShipment.JS_ActualWeight AS Weight,
    JobShipment.JS_UnitOfWeight AS WeightType,
    JobShipment.JS_ActualVolume AS Volume,
    JobShipment.JS_UnitOfVolume AS VolumeType,
    JobShipment.JS_ActualChargeable AS ChargeableWeight,
    JS_RL_NKOrigin, JS_RL_NKDestination,
    JS_E_DEP, JS_E_ARV,
    OrgHeaderAGT.OH_FullName AS Agent,
    JH_GS_NKRepSales AS SalesRep,
    JH_GS_NKRepOps AS OpsRep,
    JobShipment.JS_TransportMode AS TransportMode,
    JobShipment.JS_PackingMode AS TrasnportationType,
    JobShipment.JS_GoodsDescription AS GoodsDescription,
    JobShipment.JS_OuterPacks AS Packs,
    JobShipment.JS_F3_NKPackType AS PackType
    -- LCL version also adds: JS_ShipmentStatus
FROM JobShipment
LEFT JOIN JobConShipLink ON JN_JS = JS_PK
LEFT JOIN JobConsol ON JK_PK = JN_JK
LEFT JOIN (JobDocAddress LEFT JOIN OrgAddress LEFT JOIN OrgHeader)
    ON E2_ParentID = JS_PK AND E2_AddressType = 'CED'  -- Customer
LEFT JOIN (JobDocAddress LEFT JOIN OrgAddress LEFT JOIN OrgHeader)
    ON E2_ParentID = JS_PK AND E2_AddressType = 'CRD'  -- Shipper
LEFT JOIN (OrgAddress LEFT JOIN OrgHeader)
    ON OA_PK = JK_OA_SendingForwarderAddress  -- Agent
LEFT JOIN JobHeader ON JH_ParentId = JS_PK

-- Air: WHERE JS_TransportMode = 'AIR'
-- LCL: WHERE JS_TransportMode = 'SEA' AND JS_PackingMode = 'LCL'
WHERE JH_SystemCreateTimeUtc >= DATEADD(YEAR, -5, GETDATE())

Power Query Transforms (shared)

1. CNQIN → CNTAO origin fix
2. Empty SalesRep → "WSI"
3. Weight conversion:
   • Weight_KGS: KG as-is, LB * 2.2046
   • Weight_LBS: LB as-is, KG / 2.2046
4. Validation Notes: Checks WeightType is KG or LB, VolumeType is M3 — flags "Wrong WeightType/VolumeType" if not
5. Volume check: VolumeType = M3 → Volume, else 0 (CBMs)

New JobShipment Columns Discovered

Column	Type	Notes
JS_ActualVolume	DC(9,3)	Actual volume
JS_UnitOfWeight	VC	Weight unit (KG, LB)
JS_UnitOfVolume	VC	Volume unit (M3, FT3)
JS_F3_NKPackType	VC	Pack type code (NK reference)

Transport Mode Variants (all use same SQL/M pattern)

Query	WHERE Filter	Extra Transforms
23	JS_TransportMode = 'AIR'	—
24	JS_TransportMode = 'SEA' AND JS_PackingMode = 'LCL'	Adds JS_ShipmentStatus
25	JS_TransportMode = 'ROA' AND JS_PackingMode = 'LTL'	Distinct by JH_JobNum, adds shipments=1 count
26	JS_TransportMode = 'ROA' AND JS_PackingMode = 'FTL'	Adds Trade Lane column

Query 27: Customs Line Detail (tariff, duty, customs value)

Purpose: Invoice line-level customs data — tariff numbers, supplemental tariff, customs value, duty amounts parsed from JI_AddInfo. Joins RefCountry for country names.

SQL Core

SELECT
    JD.JE_PK AS DeclarationNumber,
    JD.JE_MasterBill,
    JD.JE_DateOfArrival,
    JCI.JI_CustomsQuantity,
    JCI.JI_LinePrice,
    JCI.JI_Description,
    JCI.JI_Tariff AS Tariff,

    -- Parse JI_AddInfo (*key=value* format)
    CASE WHEN CHARINDEX('SupTariff=', JCI.JI_AddInfo) > 0 THEN
        SUBSTRING(JCI.JI_AddInfo,
            CHARINDEX('SupTariff=', JCI.JI_AddInfo) + 10,
            CHARINDEX('*', JCI.JI_AddInfo + '*',
                CHARINDEX('SupTariff=', JCI.JI_AddInfo))
            - CHARINDEX('SupTariff=', JCI.JI_AddInfo) - 10)
    ELSE NULL END AS SupTariff,

    CASE WHEN CHARINDEX('CustomsValue=', JCI.JI_AddInfo) > 0 THEN
        SUBSTRING(JCI.JI_AddInfo,
            CHARINDEX('CustomsValue=', JCI.JI_AddInfo) + 13,
            CHARINDEX('*', JCI.JI_AddInfo + '*',
                CHARINDEX('CustomsValue=', JCI.JI_AddInfo))
            - CHARINDEX('CustomsValue=', JCI.JI_AddInfo) - 13)
    ELSE NULL END AS CustomsValue,

    CASE WHEN CHARINDEX('Duty=', JCI.JI_AddInfo) > 0 THEN
        SUBSTRING(JCI.JI_AddInfo,
            CHARINDEX('Duty=', JCI.JI_AddInfo) + 5,
            CHARINDEX('*', JCI.JI_AddInfo + '*',
                CHARINDEX('Duty=', JCI.JI_AddInfo))
            - CHARINDEX('Duty=', JCI.JI_AddInfo) - 5)
    ELSE NULL END AS Duty,

    JCI.JI_AddInfo,
    COE.RN_Desc AS CountryOfExport,
    COO.RN_Desc AS CountryOfOrigin

FROM JobDeclaration JD
JOIN JobComInvoiceHeader JZ ON JZ.JZ_JE = JD.JE_PK
JOIN JobComInvoiceLine JCI ON JCI.JI_JZ = JZ.JZ_PK
LEFT JOIN RefCountry COE ON COE.RN_Code = JCI.JI_RN_NKCountryOfExport
LEFT JOIN RefCountry COO ON COO.RN_Code = JCI.JI_RN_NKCountryOfOrigin

New Tables/Columns Discovered

RefCountry (RN) — Country Reference

Column	Type	Notes
RN_Code	VC(2)	ISO 2-letter country code
RN_Desc	VC	Country name

JobComInvoiceLine — additional columns

Column	Type	Notes
JI_CustomsQuantity	DC	Customs quantity
JI_LinePrice	MN	Line price
JI_Tariff	VC	HTS tariff number
JI_AddInfo	VC	Delimited field — *key=value* format
JI_RN_NKCountryOfExport	VC(2)	Country of export (ISO)
JI_RN_NKCountryOfOrigin	VC(2)	Country of origin (ISO)

JI_AddInfo Known Keys

Key	Notes
SupTariff	Supplemental tariff number
CustomsValue	Customs declared value
Duty	Duty amount

Pattern: Both JE_AddInfo (Query 7) and JI_AddInfo use the same *key=value* delimited format. Same CHARINDEX/SUBSTRING parsing technique.

Query 28: Customs Line Detail (expanded — manufacturer, seller, importer)

Purpose: Same tariff/duty parsing as Query 27, plus manufacturer and seller address details. Filtered to authorized entries only. Adds 8-digit tariff truncation.

SQL Core (additions to Query 27)

-- Same base as Query 27, plus:
    importer.oh_fullname AS ImporterName,

    -- Manufacturer (from declaration-level address)
    manufacturer.oh_fullname AS ManufacturerName,
    manufacturer_addr.oa_address1, oa_address2, oa_city,
    oa_state, oa_postcode, oa_rn_nkcountrycode,

    -- Seller (from invoice line-level address)
    seller.oh_fullname AS SellerName,
    seller_addr.oa_address1, oa_address2, oa_city,
    oa_state, oa_postcode, oa_rn_nkcountrycode,

    jd.je_rl_nkorigin AS PortOfLoading,
    jd.je_rl_nkportofarrival AS PortOfArrival,
    jd.je_rl_nkfinaldestination AS FinalDestination

-- Additional joins:
LEFT JOIN OrgHeader importer ON importer.oh_pk = jd.je_oh_importer
LEFT JOIN OrgAddress seller_addr ON seller_addr.oa_pk = jci.ji_oa_seller
LEFT JOIN OrgHeader seller ON seller.oh_pk = seller_addr.oa_oh
LEFT JOIN OrgAddress manufacturer_addr ON manufacturer_addr.oa_pk = jd.je_oa_manufactureraddress
LEFT JOIN OrgHeader manufacturer ON manufacturer.oh_pk = manufacturer_addr.oa_oh

Power Query Transforms

1. Filter: JE_EntryAuthorisationDate not null (authorized entries only)
2. Add 8-Digits_Tariffs = first 8 chars of Tariff

New Columns Discovered

Table	Column	Type	Notes
JobComInvoiceLine	JI_OA_Seller	UID	FK → OrgAddress (seller address)
JobDeclaration	JE_OA_ManufacturerAddress	UID	FK → OrgAddress (manufacturer)

Query 29: DateTime Helper (Power BI utility)

Purpose: Generates current UTC date/time — used as refresh timestamp in Power BI report.

M Code (no SQL)

DateTimeZone.UtcNow() → split into DateTime, Date, Time columns

Query 30: Staff Directory

Purpose: Staff list with role classification, active status, agent flag, and primary contact address via OrgContact chain.

SQL Core

SELECT
    GS.GS_Code AS StaffCode,
    GS.GS_FullName AS FullName,
    GS.GS_Title AS Title,
    GS.GS_EmailAddress AS EmailAddress,
    OI.OI_Address AS ContactAddress,
    CASE
        WHEN GS.GS_IsSalesRep = 1 THEN 'SalesRep'
        WHEN GS.GS_IsOperational = 1 THEN 'Operational'
        WHEN GS.GS_IsController = 1 THEN 'Controller'
        ELSE 'Other'
    END AS Role,
    CASE
        WHEN GS.GS_IsActive = 1 AND GS.GS_IsValid = 1 THEN 'Active'
        ELSE 'Inactive'
    END AS Status,
    CASE
        WHEN GS.GS_FullName LIKE '%(AGT%' ESCAPE '\'
          OR GS.GS_FullName LIKE '%AGT%' THEN 'Yes'
        ELSE 'No'
    END AS Agent
FROM GlbStaff GS
LEFT JOIN OrgContact OC ON OC.OC_PER = GS.GS_PER
LEFT JOIN OrgContactItem OI ON OI.OI_OC = OC.OC_PK AND OI.OI_IsPrimary = 1
ORDER BY GS.GS_FullName;

New Tables/Columns Discovered

GlbStaff — additional columns

Column	Type	Notes
GS_PER	UID	FK → Person record (links to OrgContact)
GS_IsController	BIT	Controller role flag
GS_IsValid	BIT	Valid flag

OrgContact (OC) — Contact Records

Column	Type	Notes
OC_PK	UID	Primary key
OC_PER	UID	FK → Person (same as GS_PER)

OrgContactItem (OI) — Contact Details

Column	Type	Notes
OI_OC	UID	FK → OrgContact.OC_PK
OI_Address	VC	Contact address (email, phone, etc.)
OI_IsPrimary	BIT	Primary contact flag

Query 31: Organization Details (EIN, address, staff assignments)

Purpose: Organization master with EIN (customs reg), main address, and sales/service rep via staff assignments. Joined with an OrganizationsSummarized table for role flags.

SQL Core

SELECT
    OH_Code, OH_FullName,
    OK_CustomsRegNo AS EIN,
    OA_Address1, OA_Address2, OA_City, OA_State, OA_PostCode,
    OA_RN_NKCountryCode,
    PZ_AddressType,
    Vta.GS_FullName AS SalesRep
FROM OrgHeader
LEFT JOIN OrgCusCode ON OrgCusCode.OK_OH = OH_PK AND OK_CodeType = 'EIN'
LEFT JOIN OrgAddress ON OrgAddress.OA_OH = OH_PK
LEFT JOIN OrgAddressCapability ON OrgAddressCapability.PZ_OA = OA_PK
LEFT JOIN OrgStaffAssignments OSA ON OSA.O8_OH = OH_PK
LEFT JOIN GlbStaff Vta ON Vta.GS_Code = OSA.O8_GS_NKPersonResponsible
WHERE PZ_IsMainAddress = 1

Power Query Transforms

1. Distinct by OH_Code
2. Rename OH_FullName → Customer, SalesRep → CustomerService
3. Join OrganizationsSummarized table → OH_IsActive, SAL, EW1, CUS, SSP role flags
4. Rename OrganizationsSummarized.SAL → "Sales Rep"

New Tables Discovered

OrgCusCode (OK) — Customs Registration Codes

Column	Type	Notes
OK_OH	UID	FK → OrgHeader.OH_PK
OK_CustomsRegNo	VC	Registration number (EIN, DUNS, etc.)
OK_CodeType	VC	Code type: 'EIN', 'DUNS', etc.

OrgAddressCapability (PZ) — Address Capabilities

Column	Type	Notes
PZ_OA	UID	FK → OrgAddress.OA_PK
PZ_AddressType	VC	Address type
PZ_IsMainAddress	BIT	Main address flag

OrgStaffAssignments (O8) — Org ↔ Staff Assignments

Column	Type	Notes
O8_OH	UID	FK → OrgHeader.OH_PK
O8_GS_NKPersonResponsible	VC(3)	Staff code (NK → GlbStaff.GS_Code)

Query 32: Sales Rep List (filtered staff)

Purpose: Active sales reps only — for Sales MC dropdown/assignment. Simplified version of Query 30 (no OrgContact joins). Classifies managers vs users.

SQL Core

SELECT
    GS.GS_Code AS StaffCode, GS.GS_FullName AS FullName,
    GS.GS_Title AS Title, GS.GS_EmailAddress AS EmailAddress,
    CASE
        WHEN GS.GS_IsSalesRep = 1 THEN 'SalesRep'
        WHEN GS.GS_IsOperational = 1 THEN 'Operational'
        WHEN GS.GS_IsController = 1 THEN 'Controller'
        ELSE 'Other'
    END AS Role,
    CASE
        WHEN GS.GS_IsActive = 1 AND GS.GS_IsValid = 1 THEN 'Active'
        ELSE 'Inactive'
    END AS Status,
    CASE
        WHEN GS.GS_FullName LIKE '%(AGT%' ESCAPE '\'
          OR GS.GS_FullName LIKE '%AGT%' THEN 'Yes'
        ELSE 'No'
    END AS Agent
FROM GlbStaff GS
ORDER BY GS.GS_FullName

Power Query Transforms

1. Filter: Role = "SalesRep"
2. Email fix: matthew.mcgrath@triumph.ca → matthew_mcgrath@triumph.ca
3. Add Type: "Manager" for John Su, Thomas Tae Kim, Jeff Chang, or AGT-containing names; else "User"
4. Filter out: StaffCode != "PKU" (Peter Ku / House Account)

Query 33: Document Distribution List (email routing)

Purpose: Email distribution list per organization for a document group (MSC = Arrival Notice). Splits emails into external (To), internal @go-wsi.com (BCC), and CC recipients. Falls back to CC list if no direct contact emails exist.

SQL Core

;WITH Base AS (
    SELECT OH.OH_PK, OH.OH_Code, OH.OH_FullName,
        OD.OD_DocumentGroup, OC.OC_ContactName, OC.OC_Email,
        ODR.ODR_EmailAddress
    FROM OrgContact OC
    LEFT JOIN OrgDocument OD ON OC.OC_PK = OD.OD_OC
    LEFT JOIN OrgHeader OH ON OC.OC_OH = OH.OH_PK
    LEFT JOIN OrgDocumentCopyRecipient ODR ON ODR.ODR_OD = OD.OD_PK
    WHERE OD.OD_DocumentGroup = 'MSC'
),
Grp AS (
    SELECT OH_PK, OH_Code, OH_FullName, OD_DocumentGroup,
        MIN(OC_ContactName) AS ContactName,
        MAX(CASE WHEN OC_Email IS NOT NULL THEN 1 ELSE 0 END) AS HasAnyOCEmail
    FROM Base GROUP BY OH_PK, OH_Code, OH_FullName, OD_DocumentGroup
),
AggExternal AS (
    -- All non-@go-wsi.com emails → To field
    SELECT b.OH_PK, b.OD_DocumentGroup,
        STRING_AGG(x.Email, '; ') AS ExternalEmails
    FROM (SELECT DISTINCT OH_PK, OD_DocumentGroup, OC_Email AS Email
          FROM Base WHERE OC_Email IS NOT NULL
            AND UPPER(OC_Email) NOT LIKE '%@GO-WSI.COM%') x
    JOIN Base b ON b.OH_PK = x.OH_PK AND b.OD_DocumentGroup = x.OD_DocumentGroup
    GROUP BY b.OH_PK, b.OD_DocumentGroup
),
AggInternal AS (
    -- All @go-wsi.com emails → BCC field
    SELECT b.OH_PK, b.OD_DocumentGroup,
        STRING_AGG(x.Email, '; ') AS InternalEmails
    FROM (SELECT DISTINCT OH_PK, OD_DocumentGroup, OC_Email AS Email
          FROM Base WHERE OC_Email IS NOT NULL
            AND UPPER(OC_Email) LIKE '%@GO-WSI.COM%') x
    JOIN Base b ON b.OH_PK = x.OH_PK AND b.OD_DocumentGroup = x.OD_DocumentGroup
    GROUP BY b.OH_PK, b.OD_DocumentGroup
),
AggCC AS (
    -- Copy recipients → CC field
    SELECT b.OH_PK, b.OD_DocumentGroup,
        STRING_AGG(x.Email, '; ') AS CCEmails
    FROM (SELECT DISTINCT OH_PK, OD_DocumentGroup, ODR_EmailAddress AS Email
          FROM Base WHERE ODR_EmailAddress IS NOT NULL) x
    JOIN Base b ON b.OH_PK = x.OH_PK AND b.OD_DocumentGroup = x.OD_DocumentGroup
    GROUP BY b.OH_PK, b.OD_DocumentGroup
)
SELECT g.OH_Code, g.OH_FullName, g.ContactName,
    -- If no OC_Email exists, fall back to CC list as To
    COALESCE(CASE WHEN g.HasAnyOCEmail = 0 THEN cc.CCEmails
                  ELSE ex.ExternalEmails END, '') AS OC_Email,
    COALESCE(CASE WHEN g.HasAnyOCEmail = 0 THEN ''
                  ELSE cc.CCEmails END, '') AS CC,
    COALESCE(inl.InternalEmails, '') AS BCC,
    g.OD_DocumentGroup
FROM Grp g
LEFT JOIN AggExternal ex ON ex.OH_PK = g.OH_PK AND ex.OD_DocumentGroup = g.OD_DocumentGroup
LEFT JOIN AggInternal inl ON inl.OH_PK = g.OH_PK AND inl.OD_DocumentGroup = g.OD_DocumentGroup
LEFT JOIN AggCC cc ON cc.OH_PK = g.OH_PK AND cc.OD_DocumentGroup = g.OD_DocumentGroup
ORDER BY g.OH_FullName;

Power Query Transforms

1. Filter: OD_DocumentGroup = "MSC"
2. Null OC_Email → empty string
3. Null BCC → empty string

New Tables Discovered

OrgDocument (OD) — Document Distribution Rules

Column	Type	Notes
OD_PK	UID	Primary key
OD_OC	UID	FK → OrgContact.OC_PK
OD_DocumentGroup	VC	Document group code (MSC=Arrival Notice, INV=Invoice, etc.)

OrgContact — additional columns

Column	Type	Notes
OC_OH	UID	FK → OrgHeader.OH_PK (organization)
OC_ContactName	VC	Contact person name
OC_Email	VC	Contact email

OrgDocumentCopyRecipient (ODR) — CC Recipients

Column	Type	Notes
ODR_OD	UID	FK → OrgDocument.OD_PK
ODR_EmailAddress	VC	CC email address

Document Group Codes (OD_DocumentGroup)

Code	Document Type
MSC	Arrival Notice / Shipping docs

More codes likely exist (INV, BOL, etc.) — need to query DISTINCT to discover all.

---

Power Query Utility Functions

FxIsGarbage — Garbage Text Detector

Purpose: Detects corrupted/unreadable text (diamond replacement chars, vertical tab bytes). Used to filter note rows before display.

(TextInput as nullable text) as logical =>
let
    IsGarbage =
        if TextInput = null or TextInput = "" then false
        else if Text.Contains(TextInput, "�") then true        // Diamond replacement char
        else if Text.Contains(TextInput, "#(000B)") then true  // Vertical tab (box char)
        else false
in IsGarbage

FxGetCleanNotes — Binary Note Decompressor

Purpose: Decompresses ST_NoteData (binary/compressed RTF) from StmNote table. Tries multiple byte offsets for different compression formats, then strips RTF markup to plain text.

(BinaryInput as nullable binary) as text =>
let
    Output = if BinaryInput = null then "" else
    let
        // Try decompression at offsets: 6 (PZ+ZLib), 4 (Standard PZ), 0 (Raw), 2
        TryDecompress = (skip) =>
            try Text.FromBinary(
                Binary.Decompress(Binary.Range(BinaryInput, skip), Compression.Deflate)
            ) otherwise null,

        RawRTF =
            if TryDecompress(6) <> null then TryDecompress(6)
            else if TryDecompress(4) <> null then TryDecompress(4)
            else if TryDecompress(0) <> null then TryDecompress(0)
            else if TryDecompress(2) <> null then TryDecompress(2)
            else "FAILED_DECOMPRESSION",

        // RTF Cleaning: remove \pard, convert \par→newline, \tab→spaces,
        // strip {header/font tables}, remove \commands, compact whitespace
        FinalOutput = ... // (full RTF strip pipeline)
    in FinalOutput
in
    if Output <> null and Text.Contains(Output, "�") then
        "**Not readable text Probably an Image**"
    else Output

Key Technical Notes

• CW stores notes as Deflate-compressed RTF in ST_NoteData (varbinary)
• Byte offset varies: 6 bytes for PZ+ZLib header, 4 for standard PZ, 0 for raw Deflate
• After decompression, output is RTF markup that needs stripping
• Images stored in notes return garbage text → detected by diamond char �

---

Additional Power BI Queries (continued)

Query 34: Effective Manufacturer per Declaration

Purpose: Resolves manufacturer name per declaration using COALESCE priority: invoice line → invoice header → declaration level. Aggregates distinct manufacturers per declaration with STRING_AGG.

SQL Core

SELECT
    x.JE_DeclarationReference,
    STRING_AGG(x.OH_FullName, CHAR(13) + CHAR(10)) AS EffectiveManufacturer_OH_FullName_List
FROM (
    SELECT DISTINCT
        JE.JE_DeclarationReference,
        OH_EFF.OH_FullName
    FROM JobDeclaration JE
    LEFT JOIN JobComInvoiceHeader JZ ON JZ.JZ_JE = JE.JE_PK
    LEFT JOIN JobComInvoiceLine JI ON JI.JI_JZ = JZ.JZ_PK
    LEFT JOIN OrgAddress OA_LINE ON OA_LINE.OA_PK = JI.JI_OA_ManufacturerAddress
    LEFT JOIN OrgAddress OA_HDR ON OA_HDR.OA_PK = JZ.JZ_OA_ManufacturerAddress
    LEFT JOIN OrgAddress OA_DECL ON OA_DECL.OA_PK = JE.JE_OA_ManufacturerAddress
    INNER JOIN OrgHeader OH_EFF ON OH_EFF.OH_PK = COALESCE(
        OA_LINE.OA_OH,   -- Priority 1: Invoice line manufacturer
        OA_HDR.OA_OH,    -- Priority 2: Invoice header manufacturer
        OA_DECL.OA_OH    -- Priority 3: Declaration manufacturer
    )
) AS x
GROUP BY x.JE_DeclarationReference;

Power Query Transforms

1. Rename aggregated list → "Manufacturer"

Manufacturer Address Priority

JI_OA_ManufacturerAddress (line) → JZ_OA_ManufacturerAddress (header) → JE_OA_ManufacturerAddress (declaration)

New Columns Discovered

Table	Column	Type	Notes
JobComInvoiceLine	JI_OA_ManufacturerAddress	UID	FK → OrgAddress (line-level manufacturer)
JobComInvoiceHeader	JZ_OA_ManufacturerAddress	UID	FK → OrgAddress (header-level manufacturer)

JE_OA_ManufacturerAddress was already documented in Query 28.

Query 35: Transport Legs by Declaration (pivoted by leg type)

Purpose: Pivots transport legs per declaration into columns by category: Feeder Ocean, Main Ocean, Rail, Trucking. Each category gets LoadPort, DiscPort, ETD, ETA, ATD, ATA, Vessel — pipe-delimited if multiple.

SQL Core

;WITH Legs AS (
    SELECT
        JE.JE_DeclarationReference,
        JW.JW_RL_NKLoadPort, JW.JW_RL_NKDiscPort,
        JW.JW_ETD, JW.JW_ETA, JW.JW_ATD, JW.JW_ATA, JW.JW_Vessel,
        CASE
            WHEN JW.JW_TransportMode = 'SEA' AND JW.JW_TransportType = 'PRE' THEN 'FeederOceanLeg'
            WHEN JW.JW_TransportMode = 'SEA' AND JW.JW_TransportType = 'MAI' THEN 'MainOceanLeg'
            WHEN JW.JW_TransportMode = 'RAI' AND JW.JW_TransportType = 'OTH' THEN 'RailLeg'
            WHEN JW.JW_TransportMode = 'ROA' THEN 'TruckingLeg'
            ELSE 'Other'
        END AS LegCategory
    FROM JobConsolTransport JW
    INNER JOIN JobDeclaration JE
        ON JE.JE_PK = JW.JW_ParentGUID AND JW.JW_ParentType = 'DEC'
    WHERE JW.JW_TransportMode <> 'AIR'
)
SELECT L.JE_DeclarationReference,
    -- Per category: STRING_AGG for LoadPort, DiscPort, ETD, ETA, ATD, ATA, Vessel
    -- (28 columns total: 7 fields × 4 categories)
    STRING_AGG(CASE WHEN L.LegCategory = 'FeederOceanLeg' THEN L.JW_RL_NKLoadPort END, ' | ') AS Feeder_LoadPort,
    -- ... (same pattern for all 28 columns)
    STRING_AGG(CASE WHEN L.LegCategory = 'TruckingLeg' THEN L.JW_Vessel END, ' | ') AS Truck_Vessel
FROM Legs L
GROUP BY L.JE_DeclarationReference
ORDER BY L.JE_DeclarationReference;

Key Patterns

• JW_ParentType = 'DEC' — transport legs linked to declarations (not just consols/ISF)
• JW_TransportType values: 'PRE' = Pre-carriage/feeder, 'MAI' = Main leg, 'OTH' = Other
• JW_TransportMode values: 'SEA', 'RAI', 'ROA', 'AIR'
• Leg categorization: Mode + Type combo determines category
• Dates formatted as ISO 8601 (CONVERT(varchar(23), ..., 126))

Updated JW_ParentType Values

Value	Parent Table	Notes
CON	JobConsol	Forwarding consol
SHP	JobShipment	Forwarding shipment
ISF	cusISFHeader	ISF filing
DEC	JobDeclaration	Customs declaration

Query 36: Declaration Shipment Tracking (full dashboard)

Purpose: Comprehensive shipment tracking per declaration — combines declaration header, CusContainer, JobContainer milestones, entry instructions, parties, reps. Joined with transport legs (Query 35) and manufacturer (Query 34). Generates LatestStatus, Historical timeline, LatestActionDate, DaysSince columns via complex Power Query M logic.

SQL Core

SELECT
    JE.JE_PK AS DeclarationID, JE_IsValid, JE_IsCancelled,
    JE.JE_DeclarationReference AS DeclarationNumber,
    JE.JE_ManifestNumber AS ManifestNumber,
    JE.JE_MasterBill, JE.JE_HouseBill,
    JE.JE_TotalWeight, JE.JE_TotalVolume,
    JE.JE_TotalWeightUnit, JE.JE_TotalVolumeUnit,
    JE.JE_ExportDate AS JS_E_DEP,
    JE.JE_TransportMode,
    JE.JE_RL_NKPortOfLoading, JE.JE_RL_NKPortOfArrival,
    JE_DateOfArrival AS JS_E_ARV,
    JE.JE_VesselName, JE.JE_VoyageFlightNo, JE.JE_CarrierCode AS SCACCode,

    -- Container milestones (from JobContainer)
    JC.JC_ContainerYardEmptyPickupGateOut,
    JC.JC_FCLWharfGateIn, JC.JC_FCLOnBoardVessel,
    JC.JC_FCLUnloadFromVessel, JC.JC_FCLWharfGateOut,
    JC.JC_ArrivalCartageComplete,
    JC.JC_ContainerYardEmptyReturnGateIn,
    JC.JC_DepartureCartageAdvised, JC.JC_DepartureCartageComplete,
    JC.JC_DepartureEstimatedPickup, JC.JC_DepartureSlotDateTime,
    JC.JC_ArrivalCTOStorageStartDate,

    -- Parties
    OrgHeaderCLI.OH_FullName AS Importer, OrgHeaderCLI.OH_Code AS ImporterCode,
    OrgHeaderAGT.OH_FullName AS DeclaringAgent,
    JH.JH_GS_NKRepSales AS SalesRep, JH.JH_GS_NKRepOps AS OpsRep,

    -- Entry Instruction
    CEI.CEI_Procedure, CEI.CEI_Description, CEI.CEI_DateForDuty,

    -- CusContainer details
    CO.CO_ContainerNumber, CO.CO_FCL_LCL_AIr AS ContainerMode,
    RC.RC_Code AS ContainerCode, RC.RC_ContainerType

FROM JobDeclaration JE
LEFT JOIN CusContainer CO ON CO.CO_JE = JE.JE_PK
LEFT JOIN RefContainer RC ON RC.RC_PK = CO.CO_RC
LEFT JOIN JobContainer JC ON JC.JC_ContainerNum = CO.CO_ContainerNumber
LEFT JOIN OrgHeader OrgHeaderCLI ON OrgHeaderCLI.OH_PK = JE.JE_OH_Importer
LEFT JOIN OrgHeader OrgHeaderAGT ON OrgHeaderAGT.OH_PK = JE.JE_OH_Forwarder
LEFT JOIN JobHeader JH ON JH.JH_ParentID = JE.JE_PK
LEFT JOIN CusEntryInstruction CEI ON CEI.CEI_JE = JE.JE_PK
WHERE JE.JE_TransportMode IN ('RAI', 'SEA') AND JE.JE_ContainerMode = 'CNT';

Power Query Transforms (key steps)

1. Filter: not cancelled
2. Group by DeclarationID (MAX for all fields)
3. Join Transposed_LegStatus_Declarations (Query 35) → 28 leg columns
4. LatestStatus: Builds priority-ordered message list from all milestone/leg dates, picks most recent
5. Historical: Same logic but formats all events as timeline (date + action, newline-separated)
6. LatestActionDate: Date of most recent event
7. DaysSince: Days between now and LatestActionDate
8. ATALastCY: Rail ATA if exists, else Main ATA
9. Join JobOrderItem (Query 20) → OrderReferences
10. Join DeclarationShippers (Query 34) → Manufacturer as Shipper
11. Add ShipmentType = "Declaration"

New Tables/Columns Discovered

CusEntryInstruction (CEI) — Entry Instructions

Column	Type	Notes
CEI_JE	UID	FK → JobDeclaration.JE_PK
CEI_Procedure	VC	Entry procedure code
CEI_Description	VC	Instruction description
CEI_DateForDuty	DT	Date for duty payment

CusContainer — additional columns

Column	Type	Notes
CO_ContainerNumber	VC	Container number
CO_FCL_LCL_AIr	VC	FCL/LCL/AIR mode
CO_RC	UID	FK → RefContainer.RC_PK

JobContainer — additional milestone columns

Column	Type	Notes
JC_DepartureCartageAdvised	DT	Departure cartage advised
JC_DepartureCartageComplete	DT	Departure cartage completed
JC_DepartureEstimatedPickup	DT	Estimated pickup datetime
JC_DepartureSlotDateTime	DT	Departure slot booked
JC_ArrivalCTOStorageStartDate	DT	CTO storage start date

JobDeclaration — additional columns

Column	Type	Notes
JE_ManifestNumber	VC	Manifest number
JE_TotalWeightUnit	VC	Weight unit
JE_TotalVolumeUnit	VC	Volume unit
JE_ContainerMode	VC	CNT/BBK container mode

Milestone Priority Order (for LatestStatus)

Empty returned > Container gate out > Truck ATA > Truck ETA > Truck ATD > Truck ETD >
Rail ATA > Rail ETA > Rail ATD > Rail ETD > Offloaded > Main ATA > Main ETA >
Main ATD > Main ETD > Feeder ATA > Feeder ETA > Feeder ATD > Feeder ETD >
Onboard > Full ingate > Empty picked up

Query 37: Transport Legs with Geo (Declaration legs + lat/lon)

Purpose: Raw transport legs for declarations, joined with UNLOCO lat/lon for map visualization.

SQL Core

SELECT
    JW.JW_LegOrder, JW.JW_TransportMode, JW.JW_TransportType,
    JW.JW_VoyageFlight, JW.JW_Vessel,
    JW.JW_OA_CarrierAddress AS CarrierOrgAddress,
    JW.JW_RL_NKLoadPort, JW.JW_RL_NKDiscPort,
    JW.JW_ETD, JW.JW_ETA, JW.JW_ATD, JW.JW_ATA,
    JW.JW_ParentType, JW.JW_ParentGUID
FROM dbo.JobConsolTransport AS JW
WHERE JW.JW_LegOrder IS NOT NULL AND JW_ParentType = 'DEC'
ORDER BY JW.JW_ParentType, JW.JW_ParentGUID, JW.JW_LegOrder;

Power Query Transforms

1. Lowercase + trim JW_ParentGUID for join compatibility
2. Join JobDeclaration-Raw by JE_PK → get DeclarationReference
3. Filter: ParentType = "DEC"
4. Join UNLOCO-Origin → Lat/Long for LoadPort
5. Join UNLOCO-Destination → Lat/Long for DiscPort

Query 38: Consol Transport Legs (Pivoted by CON Parent Type)

Purpose: Same pattern as Query 35 but for Consols instead of Declarations. Pivots transport legs by category (Feeder/Main/Rail/Trucking) using JW_ParentType = 'CON' and groups by JK_UniqueConsignRef.

SQL Core

WITH Legs AS (
    SELECT
        JK.JK_UniqueConsignRef,
        JW.JW_RL_NKLoadPort, JW.JW_RL_NKDiscPort,
        JW.JW_ETD, JW.JW_ETA, JW.JW_ATD, JW.JW_ATA, JW.JW_Vessel,
        CASE
            WHEN JW.JW_TransportMode = 'SEA' AND JW.JW_TransportType = 'PRE' THEN 'FeederOceanLeg'
            WHEN JW.JW_TransportMode = 'SEA' AND JW.JW_TransportType = 'MAI' THEN 'MainOceanLeg'
            WHEN JW.JW_TransportMode = 'RAI' AND JW.JW_TransportType = 'OTH' THEN 'RailLeg'
            WHEN JW.JW_TransportMode = 'ROA'                                  THEN 'TruckingLeg'
            ELSE 'Other'
        END AS LegCategory
    FROM JobConsolTransport AS JW
    INNER JOIN JobConsol AS JK
        ON JK.JK_PK = JW.JW_ParentGUID AND JW.JW_ParentType = 'CON'
    WHERE JW.JW_TransportMode <> 'AIR'
)
SELECT
    L.JK_UniqueConsignRef,
    -- Feeder: LoadPort, DiscPort, ETD, ETA, ATD, ATA, Vessel (STRING_AGG with ' | ')
    -- Main:   LoadPort, DiscPort, ETD, ETA, ATD, ATA, Vessel
    -- Rail:   LoadPort, DiscPort, ETD, ETA, ATD, ATA, Vessel
    -- Truck:  LoadPort, DiscPort, ETD, ETA, ATD, ATA, Vessel
    STRING_AGG(CASE WHEN L.LegCategory = 'FeederOceanLeg' THEN L.JW_RL_NKLoadPort END, ' | ') AS Feeder_LoadPort,
    -- ... (28 STRING_AGG columns total, 7 per leg category)
FROM Legs AS L
GROUP BY L.JK_UniqueConsignRef
ORDER BY L.JK_UniqueConsignRef;

Power Query Transforms

1. Sort by JK_UniqueConsignRef ascending

Key Difference from Query 35

• Query 35: JW_ParentType = 'DEC' → groups by JE_DeclarationReference
• Query 38: JW_ParentType = 'CON' → groups by JK_UniqueConsignRef

---

Query 39: Max Leg Order per Consol

Purpose: Finds the last/final transport leg for each SEA consol. Used for map visualization to show final destination.

SQL Core

SELECT
    MAX(JW.JW_LegOrder) AS MaxLegOrder,
    JK.JK_TransportMode,
    JK.JK_UniqueConsignRef
FROM JobConsolTransport AS JW
LEFT JOIN JobConsol AS JK
       ON UPPER(JW.JW_ParentGUID) = UPPER(JK.JK_PK)
WHERE JW.JW_ParentType = 'CON'
  AND JK.JK_TransportMode = 'SEA'
GROUP BY JK.JK_TransportMode, JK.JK_UniqueConsignRef;

Power Query Transforms

1. Merge with Legs query (Query 40) by {MaxLegOrder, JK_UniqueConsignRef} → {JW_LegOrder, JK_UniqueConsignRef}
2. Expand: JW_TransportMode, JW_TransportType, JW_VoyageFlight, CarrierOrgAddress, LoadPort, DiscPort, ETD/ETA/ATD/ATA, Vessel, Origin Lat/Long, Destination Lat/Long

---

Query 40: Transport Legs Raw (Consol-based with Geo)

Purpose: Raw transport legs for all parent types, with UNLOCO lat/lon joins for map visualization. Consol-based variant of Query 37.

SQL Core

SELECT
    JW.JW_LegOrder, JW.JW_TransportMode, JW.JW_TransportType,
    JW.JW_VoyageFlight,
    JW.JW_OA_CarrierAddress AS CarrierOrgAddress,
    JW.JW_RL_NKLoadPort, JW.JW_RL_NKDiscPort,
    JW.JW_ETD, JW.JW_ETA, JW.JW_ATD, JW.JW_ATA,
    JW.JW_Vessel, JW.JW_ParentType, JW.JW_ParentGUID,
    JK.JK_UniqueConsignRef
FROM JobConsolTransport JW
LEFT JOIN JobConsol JK ON JW.JW_ParentType = 'JK' AND JW.JW_ParentGUID = UPPER(JK.JK_PK)
WHERE JW.JW_LegOrder IS NOT NULL
ORDER BY JW.JW_ParentType, JW.JW_ParentGUID, JW.JW_LegOrder;

Power Query Transforms

1. Merge with Raw Jobconsol by JW_ParentGUID → JK_PK → expand JK_UniqueConsignRef
2. Remove duplicate JK_UniqueConsignRef from SQL
3. Join UNLOCO-Origin by JW_RL_NKLoadPort → Lat/Long
4. Join UNLOCO-Destination by JW_RL_NKDiscPort → Lat/Long

Note

• SQL uses JW_ParentType = 'JK' (different from 'CON') — may be legacy; Power Query re-joins consol anyway

---

Query 41: FCL/BCN Container Status with Revised Milestone Logic

Purpose: Container-level status tracking for FCL shipments. Produces 4 computed status columns using improved ToDate helper for safe date handling.

SQL Core

SELECT
    JH_JobNum, JS_HouseBill, JK_UniqueConsignRef,
    -- Parties
    OrgHeaderCLI.OH_FullName AS Customer, OrgHeaderCLI.OH_Code AS CustomerCode,
    OrgHeaderSHP.OH_FullName AS Shipper, OrgHeaderAGT.OH_FullName AS Agent,
    -- Container & Shipment Info
    JC_ContainerNum, RC_Code, JC_GrossWeight,
    JS_RL_NKOrigin, JS_RL_NKDestination, JS_E_DEP, JS_E_ARV,
    -- Export Process
    JC_ContainerYardEmptyPickupGateOut, JC_FCLWharfGateIn, JC_FCLOnBoardVessel,
    -- Import Process
    JC_FCLUnloadFromVessel, JC_FCLWharfGateOut,
    JC_ArrivalCartageComplete, JC_ContainerYardEmptyReturnGateIn,
    -- Reps
    JH_GS_NKRepSales AS SalesRep, JH_GS_NKRepOps AS OpsRep,
    -- Transport
    JS_TransportMode AS TransportMode, JS_PackingMode AS TrasnportationType
FROM JobShipment
LEFT JOIN JobConShipLink ON JN_JS = JS_PK
LEFT JOIN JobConsol ON JK_PK = JN_JK
JOIN JobContainer ON JC_JK = JK_PK
LEFT JOIN RefContainer ON RC_PK = JC_RC
-- Party joins: CED (Customer), CRD (Shipper), Agent via JK_OA_SendingForwarderAddress
LEFT JOIN JobHeader ON JH_ParentId = JS_PK

Power Query Transforms — Status Columns

Status Revised (full text with dates):

ToDate helper: (v as any) as nullable date => try Date.From(v) otherwise null
Priority chain (latest milestone first):
  EmptyReturn → Outgated → Unloaded → Arrived (ETA<=today) → Loaded → Ingated → EmptyOut
  Fallback: ETD>=today → ETA>=today → ETD<today → ETA<today → "No Status"
Format: "{Action} on MM/dd/yy"

OnlyStatus (label only):

Empty Returned | Outgated | Unloaded from Vessel | Vessel Arrived |
Loaded on Vessel | Ingated | Empty Outgated | Departs (Future) |
Vessel Arrives | Departed | Arrived | No Status

DateStatus (date only): Corresponding date for the matched milestone

StatusOrder (numeric priority):

Empty Returned=10, Outgated=9, Unloaded=8, Vessel Arrives=7,
Departs (Future)=6, Loaded=5, Ingated=4, Empty Outgated=3,
Arrived=2, Departed=1, No Status=0

New Container Milestone Columns

Column	Notes
JC_ContainerYardEmptyPickupGateOut	Empty container picked up from yard
JC_FCLWharfGateIn	Full container gated into port
JC_FCLOnBoardVessel	Loaded onto vessel
JC_FCLUnloadFromVessel	Discharged from vessel
JC_FCLWharfGateOut	Gated out of port (outgated)
JC_ArrivalCartageComplete	Delivered to consignee
JC_ContainerYardEmptyReturnGateIn	Empty returned to yard
JC_GrossWeight	Container gross weight

---

Query 42: FCL Shipment Tracking Dashboard (Combined)

Purpose: Massive combined dashboard query merging shipment-based FCL container tracking with pivoted transport legs, historical timeline, and latest status. Appends declaration-based tracking. Filters to 60-day ETA window.

SQL Core

Same as Query 41 plus:

• JK_MasterBillNum, JS_IsValid, JS_IsCancelled, JS_HBLContainerPackModeOverride
• JC_ArrivalCTOStorageStartDate

Power Query Transforms

1. Base SQL fetch (same join pattern as Query 41)
2. Merge with Transposed_LegStatus (pivoted legs from Query 38) by JK_UniqueConsignRef
3. LatestStatus column: Priority-ordered message list from all milestone dates + leg dates, picks most recent achieved event
4. Historical column: All events formatted as timeline (date + action, newline-separated, newest first)
5. LatestActionDate: Date of the most recent event
6. LastestAction: Label of most recent event
7. ATALastCY: Rail ATA if exists, else Main ATA
8. Merge with UNLOCO for port name lookups
9. Append with declaration-based container tracking (Query 36 pattern)
10. Filter: Main_ETA within last 60 days
11. Add ShipmentType = "Shipment" vs "Declaration"

Milestone Priority (for LatestStatus)

Empty returned > Container gate out > Truck ATA > Truck ETA > Truck ATD > Truck ETD >
Rail ATA > Rail ETA > Rail ATD > Rail ETD > Offloaded > Main ATA > Main ETA >
Main ATD > Main ETD > Feeder ATA > Feeder ETA > Feeder ATD > Feeder ETD >
Onboard > Full ingate > Empty picked up

---

Query 43: Active Contacts

Purpose: List all active contacts with org affiliation. Simple lookup table.

SQL Core

SELECT
    OC.OC_PK, OC.OC_ContactName, OC.OC_Email, OC.OC_Phone,
    OC.OC_Mobile, OC.OC_Title, OC.OC_JobCategory,
    OH.OH_Code AS OrgCode, OH.OH_FullName AS OrgName
FROM OrgContact OC
LEFT JOIN OrgHeader OH ON OC.OC_OH = OH.OH_PK
WHERE OC.OC_IsActive = 1
ORDER BY OC.OC_ContactName;

Power Query Transforms

None — raw SQL output.

---

Query 44: FCL Container/TEU Report with Parties (Grouped)

Purpose: Aggregated TEU report by job number. Filters to SEA/FCL only. Groups containers and calculates TEU breakdown by container type.

SQL Core

Same base query as Query 41 (without milestone columns, without JC_GrossWeight).

Power Query Transforms

1. Filter: RC_Code not null
2. Filter: TransportMode = "SEA" AND TrasnportationType <> "LCL"
3. Filter: JH_JobNum not null/empty
4. TEU calculation: 40HC→2, 20GP→1, 45HC→2.25, 40R→2, contains "40"→2, else 1
5. Container type flags: 40HC, 20GP, 45HC, 40GP (exact/contains match → 1/0)
6. Reefer flag (RF): RC_Code contains "RF" or "RH" or "RE" → 1
7. Group by JH_JobNum:
   • JS_E_DEP, JS_E_ARV: Max date
   • Agent, SalesRep, OpsRep, Customer, CustomerCode: Max (first non-null)
   • Containers: RowCount of distinct rows
   • TEUS: Sum of TEU
   • 40HC, 40GP, 20GP, 45HC, Reefer: Sum
   • POL, POD: Max origin/destination
8. Replace empty SalesRep with "WSI"

---

Query 45: Org Staff Assignments Dynamic Pivot

Purpose: Dynamic SQL that pivots top 4 most-used roles from OrgStaffAssignments into columns. Shows responsible person per role for each active consignee/consignor org.

SQL Core

DECLARE @sql NVARCHAR(MAX);
DECLARE @columns NVARCHAR(MAX);

-- Get top 4 most used roles
SELECT @columns = STRING_AGG(QUOTENAME(O8_Role), ', ')
FROM (
    SELECT TOP 4 O8_Role
    FROM OrgStaffAssignments
    WHERE O8_Role IS NOT NULL
    GROUP BY O8_Role
    ORDER BY COUNT(*) DESC
) AS TopRoles;

SET @sql = '
WITH OrgBase AS (
    SELECT oh.OH_PK, oh.OH_Code, oh.OH_FullName,
           oh.OH_IsConsignee, oh.OH_IsConsignor, oh.OH_IsActive
    FROM orgheader oh
    LEFT JOIN orgCusCode ok ON ok.OK_OH = oh.OH_PK AND ok.OK_CodeType = ''EIN''
    LEFT JOIN orgAddress oa ON oa.OA_OH = oh.OH_PK
    LEFT JOIN OrgAddressCapability pz ON pz.PZ_OA = oa.OA_PK
    WHERE pz.PZ_IsMainAddress = 1 AND oh.OH_IsActive = 1
      AND (oh.OH_IsConsignee = 1 OR oh.OH_IsConsignor = 1)
),
RoleSource AS (
    SELECT osa.O8_OH AS OH_PK, osa.O8_Role,
           gs.GS_FullName AS ResponsiblePersonName
    FROM OrgStaffAssignments osa
    LEFT JOIN GlbStaff gs ON gs.GS_Code = osa.O8_GS_NKPersonResponsible
    WHERE osa.O8_Role IS NOT NULL
),
RolePivot AS (
    SELECT OH_PK, ' + @columns + '
    FROM RoleSource
    PIVOT (MAX(ResponsiblePersonName) FOR O8_Role IN (' + @columns + ')) AS RoleResult
)
SELECT ob.OH_Code, ob.OH_FullName, ob.OH_IsConsignee, ob.OH_IsConsignor,
       ob.OH_IsActive, ' + @columns + '
FROM OrgBase ob
LEFT JOIN RolePivot rp ON rp.OH_PK = ob.OH_PK
ORDER BY ob.OH_Code';

EXEC sp_executesql @sql;

Power Query Transforms

1. Remove duplicate rows by OH_Code (Table.Distinct)

Key Patterns

• Dynamic PIVOT with sp_executesql — columns determined at runtime from data
• STRING_AGG(QUOTENAME(...)) for column list — safely builds bracketed column names
• Escaped single quotes (''EIN'') inside dynamic SQL string
• Joins: OrgStaffAssignments → GlbStaff (by GS_Code = O8_GS_NKPersonResponsible)

---

Common Port Codes (UN/LOCODE)

Code	Port
USLAX	Los Angeles
MXZLO	Manzanillo
MXVER	Veracruz
CNSHA	Shanghai
CNYTN	Yantian
JPYOK	Yokohama

---

Power BI Model — Relationships & Measures (from .pbit export)

Model Tables (170 total, ~85 are auto LocalDateTables)

Data Tables (with measures):

Table	Cols	Measures	Description
APAR	39	17	Combined AP/AR P&L (main financial table)
JobsMain	20	18	FCL shipment master with YoY/YTD/PY comparisons
ContainersSummarized	19	13	Container TEU aggregations
AirJobs	29	5	Air freight shipments
LCL	29	5	LCL shipments (CBMs)
TruckingShipments-FTL	28	3	FTL trucking
TruckingShipments-LTL	28	1	LTL trucking
Sales Users	8	2	USERNAME()/USERPRINCIPALNAME() for RLS
Salary	1	2	Salary/commission calc
StatusOrder2	2	2	Container status highlight logic
USJobDeclaration	33	1	Owner="WSI" constant

Data Tables (no measures):

Table	Cols	Description
Container Status Updates	100	FCL tracking dashboard (Q42)
Raw Jobconsol	116	Full JobConsol dump
JobCartage	58	Docs & cartage (Q21)
Line Items	34	Customs line detail
Invoice	31	AR invoice detail
Raw Container	29	CusContainer raw
Organizations	17	Org with sales rep
Organizations w Contacts	16	Org + contact info
AR	19	AR transactions
AP-Appended-AR-Post	18	Combined AP+AR by post date
AP	15	AP transactions
Jobs	15	Shipment base
DateSructure	13	Date/year/month/type structure
ISF	13	ISF filing data
Lines Catalog	13	Tariff line catalog
Tradelane	13	Trade lane analysis
USCustomsBonds	12	Bond details
Consol	11	Consol summary
ARLineProfitAnnualBond	10	Bond-related AR profit
APARbyChargeCode	10	AP/AR by charge code
OrganizationsSummarized	9	Org aggregations
Parts	20	HTS parts/tariff
Profit-REvenue-Structure	6	Revenue structure
UNLOCO-Origin	6	Origin port lat/lon
UNLOCO-Destination	6	Destination port lat/lon
Company and Credit Terms	5	Credit terms lookup
DocsShipmentMain	4	Declaration→shipment docs
DocPOA	4	POA documents
JobOrderItem	4	Order references
JobNotes	4	Job notes
DimDate	4	Calendar dimension
Measure Selection	4	Dynamic measure picker
Managers-View	4	Manager view
DatabaseRefresh	3	Refresh timestamp
APAR Parameters	3	Filter parameters
Managers	2	Manager names
MSCReport	7	MSC report
MSCReport-OLD	6	Legacy MSC report
Owner	1	Constant
StatusOrder	1	Status sort order

Key Relationships (non-date, business logic)

Star Schema Hub: DateSructure[JH_JobNum]

From Table	From Column	Direction
Jobs	JH_JobNum	bothDirections
JobsMain	JH_JobNum	bothDirections
APAR	JH_JobNum	bothDirections
AirJobs	JH_JobNum	singleDirection
LCL	JH_JobNum	singleDirection
TruckingShipments-FTL	JH_JobNum	bothDirections
TruckingShipments-LTL	JH_JobNum	bothDirections
Container Status Updates	JH_JobNum	bothDirections
ContainersSummarized	JH_JobNum	bothDirections (via DateSructure)
Invoice	JH_JobNum	bothDirections
AP	JH_JobNum	bothDirections
AR	JH_JobNum	bothDirections
AP-Appended-AR-Post	JH_JobNum	bothDirections

DateSructure[JH_JobNum] → USJobDeclaration[JE_DeclarationReference] (singleDirection) DateSructure[JH_JobNum] → ISF[BF_JobReference] (singleDirection) DateSructure[CustomerCode] → Organizations[OH_Code] (bothDirections) DateSructure[DateValue] → DimDate[Date] (singleDirection)

Organization relationships:

• APAR[DebtorCode] → Company and Credit Terms[OrgCode]
• APAR[SalesRep] → Managers[Name]
• Organizations[Sales Rep] → Sales Users[FullName]
• OrganizationsSummarized[OH_Code] → Organizations[OH_Code]
• Organizations[OH_Code] → Organizations w Contacts[OH_Code] (bothDirections)
• Parts[OH_Code] → Organizations w Contacts[OH_Code]
• USCustomsBonds[OH_Code] → Organizations w Contacts[OH_Code]
• MSCReport-OLD[OH_Code] → Organizations w Contacts[OH_Code] (bothDirections)

Consol relationships:

• JobsMain[JK_UniqueConsignRef] → Consol[JK_UniqueConsignRef]
• TruckingShipments-LTL[JK_UniqueConsignRef] → Raw Jobconsol[JK_UniqueConsignRef]
• TruckingShipments-FTL[JK_UniqueConsignRef] → Consol[JK_UniqueConsignRef] (inactive)

Port lookups:

• JobsMain[JS_RL_NKOrigin] → UNLOCO-Origin[RL_Code] (bothDirections)
• JobsMain[JS_RL_NKDestination] → UNLOCO-Destination[RL_Code] (bothDirections)

Document relationships:

• DocsShipmentMain[JE_DeclarationReference] → USJobDeclaration[JE_DeclarationReference]
• USCustomsBonds[OH_FullName] → DocPOA[OH_FullName]
• APAR[JH_JobNum] → JobNotes[Reference] (bothDirections)

Financial:

• Profit-REvenue-Structure[JH_JobNum] → JobsMain[JH_JobNum]
• AR[TransaccNum] → Invoice[TransaccNum] (inactive)
• Jobs[JH_JobNum] → JobsMain[JH_JobNum] (bothDirections)

DAX Measures (67 total)

APAR Table (17 measures) — Financial KPIs

// Core aggregations
Total Profit       = SUM('APAR'[Profit])
Total Revenue      = SUM('APAR'[Revenue])
Number of Files    = DISTINCTCOUNTNOBLANK(APAR[JH_JobNum])
Total Shipments    = DISTINCTCOUNTNOBLANK(APAR[JH_JobNum])
Profit Percentage  = SUM(APAR[profit]) / SUM(APAR[Revenue])

// Per-rep averages
Avg Profit by Salesrep          = DIVIDE(SUM(APAR[Profit]), DISTINCTCOUNTNOBLANK(APAR[SalesRep]))
Number of Files Divide By Sales Rep = DIVIDE([Number of Files], DISTINCTCOUNTNOBLANK(APAR[SalesRep]))

// Payment status
AR   = CALCULATE([Total Revenue], FILTER(APAR, APAR[PaymentStatus] IN {"Not Paid","NotPaid"}))
Paid = CALCULATE([Total Revenue], FILTER(APAR, APAR[PaymentStatus] = "Paid"))

// Time intelligence
Profit PM   = CALCULATE([Total Profit], PREVIOUSMONTH(DimDate[Date]))
Profit LY   = CALCULATE([Total Profit], PREVIOUSYEAR(DimDate[Date]))
Profit PY   = CALCULATE([Total Profit], PREVIOUSYEAR(DimDate[Date]))
Profit SPLY = CALCULATE([Total Profit], SAMEPERIODLASTYEAR(VALUES(DimDate[Date])))
Total Shipments LY   = CALCULATE([Total Shipments], PREVIOUSYEAR(DimDate[Date]))
Total Shipments PM   = CALCULATE([Total Shipments], PREVIOUSMONTH(DimDate[Date]))
Total Shipments SPLY = CALCULATE([Total Shipments], SAMEPERIODLASTYEAR(DimDate[Date]))

// Lookup
CreditTerms = IF(ISBLANK(SELECTEDVALUE('Company and Credit Terms'[Credit Terms])), "0",
              SELECTEDVALUE('Company and Credit Terms'[Credit Terms]))

ContainersSummarized Table (13 measures) — TEU KPIs

Total Containers  = SUM(ContainersSummarized[Contianers])
Total TEUS        = SUM(ContainersSummarized[TEUS])
BL Count          = CALCULATE(DISTINCTCOUNT(DateSructure[JH_JobNum]))
Total Customer    = DISTINCTCOUNT(ContainersSummarized[Customer])
20 GP = CALCULATE(SUM(ContainersSummarized[20GP]))
40 GP = CALCULATE(SUM(ContainersSummarized[40GP]))
40 HC = CALCULATE(SUM(ContainersSummarized[40hc]))
45 HC = CALCULATE(SUM(ContainersSummarized[45hc]))
TEUS Divided by Sales Rep = DIVIDE([Total TEUS],
    CALCULATE(DISTINCTCOUNTNOBLANK(Organizations[Sales Rep]),
    FILTER(Organizations, [Total TEUS] > 0)))
TEUS LY   = CALCULATE([Total TEUS], PREVIOUSYEAR(DimDate[Date]))
TEUS PM   = CALCULATE([Total TEUS], PREVIOUSMONTH(DimDate[Date]))
TEUS SPLY = CALCULATE([Total TEUS], SAMEPERIODLASTYEAR(VALUES(DimDate[Date])))

JobsMain Table (18 measures) — Dynamic YoY/YTD/PY

// Direct counts
FCL # of Customer    = DISTINCTCOUNTNOBLANK(JobsMain[CustomerCode])
FCL # of Shipments   = DISTINCTCOUNTNOBLANK(JobsMain[JH_JobNum])
FCL # of ConsignRef  = DISTINCTCOUNTNOBLANK(JobsMain[JK_UniqueConsignRef])

// Previous Year pattern (same for all 6 KPIs):
Total Profit PY =
    VAR _year = MAX(DateSructure[Year])
    VAR _type = VALUES(DateSructure[Type])
    RETURN CALCULATE([Total Profit],
        FILTER(ALLEXCEPT(DateSructure, DateSructure[Type]),
            DateSructure[Year] = _year - 1))
// Also: Total Revenue PY, Number of Files PY, Total TEUS PY, Air Freight PY, LCL CBMs PY

// YTD pattern (same for all 6 KPIs):
Total Profit YTD =
    VAR _LastDate = MAX(DateSructure[Date])
    VAR _StartDate = CALCULATE(MIN(DateSructure[Date]),
        FILTER(APAR, [Total Profit] > 0), ALLSELECTED(DateSructure))
    RETURN CALCULATE([Total Profit],
        FILTER(ALLEXCEPT(DateSructure, DateSructure[Type]),
            DateSructure[Date] >= _StartDate && DateSructure[Date] <= _LastDate))
// Also: Total Revenue YTD, Number of Files YTD, Total TEUS YTD, Air Freight YTD, LCL CBMs YTD

// Dynamic measure selector (driven by 'Measure Selection' slicer):
PY = SWITCH(TRUE(),
    SELECTEDVALUE('Measure Selection'[Column]) = "Total Profit", [Total Profit PY],
    = "Total Revenue", [Total Revenue PY],
    = "Number of Files", [Number of Files PY],
    = "TEUS", [Total TEUS PY],
    = "Air Freight", [Air Freight PY],
    = "LCL CBMs", [LCL CBMs PY])
// Also: YTD (same SWITCH pattern)

// YoY% = DIVIDE([metric] - [metric PY], [metric PY], 0) — dynamic via Measure Selection

AirJobs Table (5 measures)

Air Freight = CALCULATE(SUM(AirJobs[ChargeableWeight]))
Air Freight Divided by Sales Rep = DIVIDE([Air Freight],
    CALCULATE(DISTINCTCOUNTNOBLANK(Organizations[Sales Rep]),
    FILTER(Organizations, [Air Freight] > 0)))
Air Freight LY   = CALCULATE([Air Freight], PREVIOUSYEAR(DimDate[Date]))
Air Freight PM   = CALCULATE([Air Freight], PREVIOUSMONTH(DimDate[Date]))
Air Freight SPLY = CALCULATE([Air Freight], SAMEPERIODLASTYEAR(DimDate[Date]))

LCL Table (5 measures)

LCL CBMs = SUM(LCL[CBMs])
LCL CBMs Divided by Sales Rep = DIVIDE([LCL CBMs],
    CALCULATE(DISTINCTCOUNTNOBLANK(Organizations[Sales Rep]),
    FILTER(Organizations, [LCL CBMs] > 0)))
LCL CBMs LY   = CALCULATE([LCL CBMs], PREVIOUSYEAR(DimDate[Date]))
LCL CBMs PM   = CALCULATE([LCL CBMs], PREVIOUSMONTH(DimDate[Date]))
LCL CBMs SPLY = CALCULATE([LCL CBMs], SAMEPERIODLASTYEAR(DimDate[Date]))

Other Measures

// TruckingShipments-FTL (3)
ShipmentFTL    = CALCULATE(DISTINCTCOUNT('TruckingShipments-FTL'[JH_JobNum]))
NoCustomersFTL = CALCULATE(DISTINCTCOUNT('TruckingShipments-FTL'[Customer]))
No of Trades   = CALCULATE(DISTINCTCOUNT('TruckingShipments-FTL'[Trade Lane]))

// TruckingShipments-LTL (1)
ShipmentLTL = SUM('TruckingShipments-LTL'[shipments])

// StatusOrder2 (2) — container status visual highlight
Line Highlight =
    VAR _selected_status = SELECTEDVALUE('Container Status'[OnlyStatus])
    VAR selected_status_Order = CALCULATE(MAX(StatusOrder2[StatusOrder]),
        'Container Status'[OnlyStatus] = _selected_status)
    RETURN IF(SELECTEDVALUE(StatusOrder2[StatusOrder]) <= selected_status_Order, [Dummy])
MStatusOrder = SELECTEDVALUE(StatusOrder2[StatusOrder])

// Salary (2)
Quarerly Value = (SELECTEDVALUE('Salary'[Salary])) / 12 * 4
Commission     = Salary[Quarerly Value]

// Sales Users (2) — Row-Level Security
UserName       = USERNAME()
userPrinciple  = USERPRINCIPALNAME()

// USJobDeclaration (1)
Owner = "WSI"

DAX Query Files (9 queries from Power BI Query View)

File	Type	Tables Used
Query 1	Sample	TOPN(100, 'USJobDeclaration')
Query 2	Container Status visual	Container Status filtered BCN/FCL, ARV >= 2025-01-01
Query 3	Org by sales rep	Organizations filtered Michael Mendoza, Thomas Tae Kim
Query 4	Duplicate of Q3	Same as Query 3
Query 5	Container Status by customer	Container Status filtered BCN/FCL + specific customers
Query 6	Duplicate of Q3	Same as Query 3
Query 7	Container tracking dashboard	Container Status Updates with LatestCY, DaysSince, Combined fields
Query 8	Container mode/type summary	Container Status Updates grouped by mode/type/customer
Query 9	Database refresh check	DatabaseRefresh datetime/time/date

Key DAX Patterns

1. Dynamic Measure Selection: Measure Selection slicer table drives SWITCH in PY/YTD/YoY% measures
2. PY pattern: VAR _year = MAX(DateSructure[Year]) → FILTER(ALLEXCEPT(...), Year = _year-1)
3. YTD pattern: Calculates start date from first non-zero data point, not calendar year start
4. Per-rep division: DIVIDE([metric], CALCULATE(DISTINCTCOUNTNOBLANK(Organizations[Sales Rep]), FILTER(Organizations, [metric] > 0)))
5. Time intelligence: Uses DimDate[Date] with PREVIOUSYEAR/PREVIOUSMONTH/SAMEPERIODLASTYEAR
6. Row-Level Security: Sales Users table with USERNAME()/USERPRINCIPALNAME()
7. Container status highlight: StatusOrder2 with visual conditional formatting via Line Highlight measure
8. Overdue/aging: Calculated columns in APAR/DateSructure use DATEDIFF against credit terms

Calculated Tables (8)

-- Salary: salary slider
GENERATESERIES(50000, 200000, 1000)

-- Managers: hardcoded manager list
DATATABLE("Name", STRING, "Role", STRING,
  {{"John Su","Manager"},{"Tae Kim","Manager"},{"Jeff Chang","Manager"},
   {"Peter Ku","Manager"},{"WSI","Manager"},{"Jimmy Yin","Sales"}})

-- Owner: constant
DATATABLE("Owner", STRING, {{"WSI"}})

-- Measure Selection: dynamic KPI slicer
{("Total Profit", NAMEOF([Total Profit]), 0),
 ("Total Revenue", NAMEOF([Total Revenue]), 1),
 ("Number of Files", NAMEOF('APAR'[Number of Files]), 2),
 ("TEUS", NAMEOF('ContainersSummarized'[Total TEUS]), 3),
 ("Air Freight", NAMEOF('AirJobs'[Air Freight]), 4),
 ("LCL CBMs", NAMEOF('LCL'[LCL CBMs]), 5),
 ("No of LTL", NAMEOF('TruckingShipments-LTL'[ShipmentLTL]), 6),
 ("No of FTL", NAMEOF('TruckingShipments-FTL'[ShipmentFTL]), 7)}

-- APAR Parameters: AR filter
{("AR", NAMEOF('APAR'[AR]), 0)}

-- DimDate: calendar from data range
CALENDAR(MIN(DateSructure[Date]), MAX(DateSructure[Date]))

-- StatusOrder2: container status pipeline (matches Q41 StatusOrder)
DATATABLE("StatusName", STRING, "StatusOrder", INTEGER,
  {{"Empty Returned",10},{"Outgated",9},{"Unloaded from Vessel",8},
   {"Vessel Arrives",7},{"Departs (Future)",6},{"Loaded on Vessel",5},
   {"Ingated",4},{"Empty Outgated",3},{"Arrived",2},{"Departed",1},{"No Status",0}})

Calculated Columns (28 across business tables)

APAR (6 calc cols):

Column	DAX
RunningDays	IF(APAR[PaymentStatus]="Paid", 0, DATEDIFF(APAR[PostedDate].[Date], NOW(), DAY))
DaysOverDue	IF(PaymentStatus="Paid", 0, DATEDIFF(PostedDate, UTCNOW(), DAY) - CreditTerms)
GroupAgent(3chrs)	LEFT(APAR[SendAgentName], 3)
OverDue	IF(DaysOverDue > 1, "Y", "N") — checks paid status first
OverdueAmount	IF(OverDue="Y", InvoiceBalance, 0)
Date	DATEVALUE(APAR[PostedDate])

DateSructure (8 calc cols):

Column	DAX
Year	YEAR([Date])
Month Id	MONTH([Date])
Month	FORMAT([Date], "Mmm")
Week Id	WEEKNUM([Date])
Week Name	"W " & [Week Id]
DateValue	DATEVALUE([Date])
Past Due	SWITCH aging buckets: Not Due, +1, +15, +30, +45, +60, +90, 91>
Past Due Id	Numeric 1-7 for aging bucket sorting

Container Status Updates (2 calc cols):

Column	DAX
Action	If ATALastCY past today → "Pending Outgate" (unless already Outgated/Empty Returned)
Date	Picks ATALastCY or LatestActionDate based on "Pending Outgate" logic

USJobDeclaration (2 calc cols):

• JE_DateOfArrival (bins) — first of month binning
• EntrySubmited — "Y"/"N" based on EntrySubmittedDate

Organizations (1): TestEmail — hardcoded test email for ACER → user@example.com, else manuel.chavez@go-wsi.com

AirJobs (2): JobCount = DISTINCTCOUNT, CustomerName (20) = LEFT(Customer, 25)

LCL (1): Customer (25) = LEFT(Customer, 25)

ContainersSummarized (1): GroupAgent(3chrs) = LEFT(Agent, 3)

DocPOA (1): YearsSinceReceived = DATEDIFF(DateReceived, TODAY(), YEAR)

DimDate (3): Year, Month Id, Month — same pattern as DateSructure

Measure Selection (1): Column = CONVERT(value, STRING)

Inactive Relationships (8)

From	To	Reason
Jobs[SalesRep] → Sales Users[StaffCode]	Superseded by Organizations[Sales Rep] → Sales Users[FullName]
TruckingShipments-LTL[JK_UniqueConsignRef] → Consol	Uses Raw Jobconsol instead
TruckingShipments-FTL[JK_UniqueConsignRef] → Consol	Same
TruckingShipments-FTL[JK_UniqueConsignRef] → Raw Jobconsol	Ambiguous path
Container Status Updates[JH_JobNum] → ContainersSummarized	Goes through DateSructure instead
Container Status Updates[JK_UniqueConsignRef] → Consol	Goes through DateSructure instead
OrganizationsSummarized[OH_Code] → Organizations w Contacts	Active path via Organizations
AR[TransaccNum] → Invoice[TransaccNum]	Possibly unused/legacy