-- Manufacturing Quick Report view - GENERATED by `npm run erp:mfg-view` from the header AND detail tables of this database.
-- Do not edit by hand: change DETAIL_OVERRIDES in backend/src/services/mfgViewBuilder.ts and generate it again.
IF OBJECT_ID('dbo.v_manufacturing_quick', 'V') IS NOT NULL DROP VIEW dbo.v_manufacturing_quick;
GO
CREATE VIEW dbo.v_manufacturing_quick AS
WITH txn AS (
  SELECT tbl, code, name FROM (VALUES
    ('fAdjhed', '?', N'Lami. FG Adjustments Addition Entry'),
    ('fAdjhed', '?', N'Return Accept Quantity In Entry'),
    ('fAdjhed', '14', N'Return Reject Quantity In Entry'),
    ('fAdjhed', '15', N'FG Adjustments Addition Entry'),
    ('fAdjhed', '?', N'FG Adjustments Deduction Entry'),
    ('fAdjhed', '?', N'SF Adjustments Deduction Entry'),
    ('fmfgjbtrnhed', '?', N'Lami. FG Job Transfer'),
    ('fmfgjbtrnhed', '93', N'FG Job Transfer'),
    ('fmWstInHed', '17', N'FG To Waste'),
    ('fmWstInHed', '43', N'Wastage In Job Wise'),
    ('fIssRHed', '33', N'Acceptance Note'),
    ('fIssRHed', '?', N'INK Return Entry'),
    ('fRIssHed', '34', N'Issue - Coco'),
    ('fRIssHed', '?', N'Issue Extra - Coco'),
    ('fmpipAshed', '48', N'Pipe Build Assembly'),
    ('fmpipAshed', '77', N'Pipe Waste Build Allocation'),
    ('FiRIssHed', '64', N'INK Issue Note'),
    ('FiRIssHed', '?', N'Issue Note'),
    ('flRIssHed', '68', N'Issue Note - Standard (Lami)'),
    ('flRIssHed', '?', N'Issue Note - Extra (Lami)'),
    ('fmStTrnhed', '80', N'Site Transfer FG'),
    ('fmStTrnhed', '96', N'Site Transfer WIP'),
    ('frDispHed', '?', N'Sample Gate Pass'),
    ('frDispHed', '?', N'Dispatch - Stretch Film'),
    ('frDispHed', '54', N'Dispatch - Local Sales'),
    ('frDispHed', '56', N'Dispatch - Walk In'),
    ('frDispHed', '?', N'Dispatch - Pipes'),
    ('frDispHed', '62', N'Dispatch Walking-Pipe'),
    ('frDispHed', '72', N'Dispatch Note - Coco'),
    ('frDispHed', '86', N'Dispatch Note - Lamination'),
    ('frDispHed', '?', N'Dispatch Note - Trading'),
    ('frDispHed', '?', N'Dispatch Return Note'),
    ('frDispHed', '?', N'Dispatch Ret. Note-Pipe')
  ) v(tbl, code, name)
)
SELECT
    CAST('fCsMatHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Customer Mat. In' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fCsMatHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(COALESCE(NULLIF(RTRIM(deb.[DebName]), ''), RTRIM(h.[DebCode])) AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(RTRIM(h.[RefNo1]) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(COALESCE(NULLIF(RTRIM(h.[ItemCode]), ''), NULLIF(RTRIM(d.[ItemCode]), '')) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(CASE WHEN d.[RefNo] IS NULL THEN h.[Qty] ELSE d.[Qty] END AS numeric(18,4)) AS [qtyKg],
    CAST(NULL AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(h.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(NULL AS nvarchar(200)) AS [stage],
    CAST(RTRIM(h.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[comcode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fCsMatHed] h
  LEFT JOIN dbo.[fCsMatDet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.[fDebtor] deb ON deb.[DebCode] = h.[DebCode]
  LEFT JOIN dbo.fItems it ON it.ItemCode = COALESCE(d.[ItemCode], h.[ItemCode])

  UNION ALL

  SELECT
    CAST('fSoBldAsHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'SO Build Assembly' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fSoBldAsHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(COALESCE(NULLIF(RTRIM(h.[RefNo1]), ''), NULLIF(RTRIM(d.[RefNo1]), '')) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(COALESCE(NULLIF(RTRIM(h.[ItemCode]), ''), NULLIF(RTRIM(d.[ItemCode]), '')) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(RTRIM(h.[machcode]) AS nvarchar(200)) AS [machine],
    CAST(CASE WHEN (d.[RefNo] IS NULL OR d.[_ln] = 1) THEN h.[QtyW] END AS numeric(18,4)) AS [qtyKg],
    CAST(CASE WHEN d.[RefNo] IS NULL THEN h.[Qty] ELSE d.[Qty] END AS numeric(18,4)) AS [pieces],
    CAST(COALESCE(NULLIF(RTRIM(h.[SiteCode]), ''), NULLIF(RTRIM(d.[SiteCode]), '')) AS nvarchar(200)) AS [site],
    CAST(RTRIM(h.[StagCode]) AS nvarchar(200)) AS [stage],
    CAST(COALESCE(NULLIF(RTRIM(h.[LocCode]), ''), NULLIF(RTRIM(d.[LocCode]), '')) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fSoBldAsHed] h
  LEFT JOIN (SELECT x.*, ROW_NUMBER() OVER (PARTITION BY x.[RefNo] ORDER BY x.[RecordId]) AS [_ln] FROM dbo.[fSoBldAsDet] x) d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = COALESCE(d.[ItemCode], h.[ItemCode])

  UNION ALL

  SELECT
    CAST('fmSFgTranHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Finish Good Trn. SO' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fmSFgTranHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(COALESCE(NULLIF(RTRIM(deb.[DebName]), ''), RTRIM(h.[DebCode])) AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(RTRIM(h.[RefNo1]) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(COALESCE(NULLIF(RTRIM(h.[ItemCode]), ''), NULLIF(RTRIM(d.[ItemCode]), '')) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(CASE WHEN d.[RefNo] IS NULL THEN h.[Qty] ELSE d.[Qty] END AS numeric(18,4)) AS [qtyKg],
    CAST(CASE WHEN (d.[RefNo] IS NULL OR d.[_ln] = 1) THEN h.[QtyPc] END AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(h.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(RTRIM(h.[StagCode]) AS nvarchar(200)) AS [stage],
    CAST(RTRIM(h.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(NULL AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fmSFgTranHed] h
  LEFT JOIN (SELECT x.*, ROW_NUMBER() OVER (PARTITION BY x.[RefNo] ORDER BY x.[RecordId]) AS [_ln] FROM dbo.[fmSFgTranDet] x) d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.[fDebtor] deb ON deb.[DebCode] = h.[DebCode]
  LEFT JOIN dbo.fItems it ON it.ItemCode = COALESCE(d.[ItemCode], h.[ItemCode])

  UNION ALL

  SELECT
    CAST('fAdjhed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(COALESCE(m.name, N'Adjustment Entry') AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fAdjhed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(RTRIM(d.[RefNo1]) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(RTRIM(d.[ItemCode]) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(d.[Qty] AS numeric(18,4)) AS [qtyKg],
    CAST(d.[QtyPc] AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(h.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(NULL AS nvarchar(200)) AS [stage],
    CAST(RTRIM(h.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[comcode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fAdjhed] h
  LEFT JOIN dbo.[fAdjDet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = d.[ItemCode]
  LEFT JOIN txn m ON m.tbl = 'fAdjhed' AND m.code = RTRIM(h.[TxnType])

  UNION ALL

  SELECT
    CAST('fmfgjbtrnhed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(COALESCE(m.name, N'FG Job Transfer') AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fmfgjbtrnhed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(COALESCE(NULLIF(RTRIM(h.[RefNo1]), ''), NULLIF(RTRIM(d.[RefNo1]), '')) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(COALESCE(NULLIF(RTRIM(h.[ItemCode]), ''), NULLIF(RTRIM(d.[ItemCode]), '')) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(CASE WHEN d.[RefNo] IS NULL THEN h.[Qty] ELSE d.[Qty] END AS numeric(18,4)) AS [qtyKg],
    CAST(CASE WHEN d.[RefNo] IS NULL THEN h.[QtyPc] ELSE d.[Qtypc] END AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(d.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(RTRIM(d.[StagCode]) AS nvarchar(200)) AS [stage],
    CAST(COALESCE(NULLIF(RTRIM(h.[LocCode]), ''), NULLIF(RTRIM(d.[LocCode]), '')) AS nvarchar(200)) AS [location],
    CAST(NULL AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fmfgjbtrnhed] h
  LEFT JOIN dbo.[fmfgjbtrndet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = COALESCE(d.[ItemCode], h.[ItemCode])
  LEFT JOIN txn m ON m.tbl = 'fmfgjbtrnhed' AND m.code = RTRIM(h.[TxnType])

  UNION ALL

  SELECT
    CAST('fAdjRolHed:' + RTRIM(h.[RefNo]) AS nvarchar(200)) AS [lineId],
    CAST(N'WIP Adjustments Addition Entry' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fAdjRolHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(RTRIM(h.[RefNo1]) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(RTRIM(h.[ItemCode]) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(CASE WHEN h.IsKg = 1 THEN h.Qty END AS numeric(18,4)) AS [qtyKg],
    CAST(CASE WHEN h.IsKg = 1 THEN NULL ELSE h.Qty END AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(h.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(RTRIM(h.[StagCode]) AS nvarchar(200)) AS [stage],
    CAST(RTRIM(h.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(NULL AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fAdjRolHed] h
  LEFT JOIN dbo.fItems it ON it.ItemCode = h.[ItemCode]

  UNION ALL

  SELECT
    CAST('fmWstInHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(COALESCE(m.name, N'Waste In') AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fmWstInHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(COALESCE(NULLIF(RTRIM(deb.[DebName]), ''), RTRIM(h.[DebCode])) AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(COALESCE(NULLIF(RTRIM(h.[RefNo1]), ''), NULLIF(RTRIM(d.[RefNo1]), '')) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(COALESCE(NULLIF(RTRIM(h.[ItemCode]), ''), NULLIF(RTRIM(d.[ItemCode]), '')) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(COALESCE(NULLIF(RTRIM(h.[MachCode]), ''), NULLIF(RTRIM(d.[machcode]), '')) AS nvarchar(200)) AS [machine],
    CAST(CASE WHEN d.[RefNo] IS NULL THEN h.[Qty] ELSE d.[Qty] END AS numeric(18,4)) AS [qtyKg],
    CAST(CASE WHEN d.[RefNo] IS NULL THEN h.[QtyPc] ELSE d.[Qtypc] END AS numeric(18,4)) AS [pieces],
    CAST(COALESCE(NULLIF(RTRIM(h.[SiteCode]), ''), NULLIF(RTRIM(d.[SiteCode]), '')) AS nvarchar(200)) AS [site],
    CAST(COALESCE(NULLIF(RTRIM(h.[StagCode]), ''), NULLIF(RTRIM(d.[StagCode]), '')) AS nvarchar(200)) AS [stage],
    CAST(COALESCE(NULLIF(RTRIM(h.[LocCode]), ''), NULLIF(RTRIM(d.[LocCode]), '')) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fmWstInHed] h
  LEFT JOIN dbo.[fmWstInDet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.[fDebtor] deb ON deb.[DebCode] = h.[DebCode]
  LEFT JOIN dbo.fItems it ON it.ItemCode = COALESCE(d.[ItemCode], h.[ItemCode])
  LEFT JOIN txn m ON m.tbl = 'fmWstInHed' AND m.code = RTRIM(h.[TxnType])

  UNION ALL

  SELECT
    CAST('fLmpHed:' + RTRIM(h.[RefNo]) AS nvarchar(200)) AS [lineId],
    CAST(N'Lumps Crush Process' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fLmpHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(NULL AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(RTRIM(h.[ItemCode]) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(RTRIM(h.[machcode]) AS nvarchar(200)) AS [machine],
    CAST(h.[Qty] AS numeric(18,4)) AS [qtyKg],
    CAST(NULL AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(h.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(NULL AS nvarchar(200)) AS [stage],
    CAST(RTRIM(h.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fLmpHed] h
  LEFT JOIN dbo.fItems it ON it.ItemCode = h.[ItemCode]

  UNION ALL

  SELECT
    CAST('fmPipOrdhed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Sales Order - Pipes' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fmPipOrdhed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(COALESCE(NULLIF(RTRIM(deb.[DebName]), ''), RTRIM(h.[DebCode])) AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(RTRIM(h.[RefNo1]) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(RTRIM(d.[Itemcode]) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(d.[Qty] AS numeric(18,4)) AS [qtyKg],
    CAST(NULL AS numeric(18,4)) AS [pieces],
    CAST(NULL AS nvarchar(200)) AS [site],
    CAST(NULL AS nvarchar(200)) AS [stage],
    CAST(RTRIM(h.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fmPipOrdhed] h
  LEFT JOIN dbo.[fmPipOrdDet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.[fDebtor] deb ON deb.[DebCode] = h.[DebCode]
  LEFT JOIN dbo.fItems it ON it.ItemCode = d.[Itemcode]

  UNION ALL

  SELECT
    CAST('fIssHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Wastage Transfer' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fIssHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(RTRIM(h.[RefNo1]) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(RTRIM(d.[ItemCode]) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(RTRIM(d.[machcode]) AS nvarchar(200)) AS [machine],
    CAST(d.[Qty] AS numeric(18,4)) AS [qtyKg],
    CAST(NULL AS numeric(18,4)) AS [pieces],
    CAST(COALESCE(NULLIF(RTRIM(h.[SiteCode]), ''), NULLIF(RTRIM(d.[SiteCode]), '')) AS nvarchar(200)) AS [site],
    CAST(RTRIM(d.[stagcode]) AS nvarchar(200)) AS [stage],
    CAST(COALESCE(NULLIF(RTRIM(h.[LocCode]), ''), NULLIF(RTRIM(d.[LocCode]), '')) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fIssHed] h
  LEFT JOIN dbo.[FIssDet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = d.[ItemCode]

  UNION ALL

  SELECT
    CAST('fIssRHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(COALESCE(m.name, N'Acceptance / Return Note') AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fIssRHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(RTRIM(h.[RefNo1]) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(RTRIM(d.[ItemCode]) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(d.[Qty] AS numeric(18,4)) AS [qtyKg],
    CAST(NULL AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(h.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(NULL AS nvarchar(200)) AS [stage],
    CAST(RTRIM(h.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fIssRHed] h
  LEFT JOIN dbo.[FIssrDet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = d.[ItemCode]
  LEFT JOIN txn m ON m.tbl = 'fIssRHed' AND m.code = RTRIM(h.[TxnType])

  UNION ALL

  SELECT
    CAST('fRIssHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(COALESCE(m.name, N'Issue - Coco') AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fRIssHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(RTRIM(h.[Refno1]) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(RTRIM(d.[ItemCode]) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(d.[Qty] AS numeric(18,4)) AS [qtyKg],
    CAST(NULL AS numeric(18,4)) AS [pieces],
    CAST(COALESCE(NULLIF(RTRIM(h.[SiteCode]), ''), NULLIF(RTRIM(d.[SiteCode]), '')) AS nvarchar(200)) AS [site],
    CAST(NULL AS nvarchar(200)) AS [stage],
    CAST(COALESCE(NULLIF(RTRIM(h.[LocCode]), ''), NULLIF(RTRIM(d.[LocCode]), '')) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fRIssHed] h
  LEFT JOIN dbo.[frIssDet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = d.[ItemCode]
  LEFT JOIN txn m ON m.tbl = 'fRIssHed' AND m.code = RTRIM(h.[TxnType])

  UNION ALL

  SELECT
    CAST('fAccpHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Acceptance Note' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fAccpHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(RTRIM(h.[RefNo1]) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(RTRIM(d.[ItemCode]) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(d.[Qty] AS numeric(18,4)) AS [qtyKg],
    CAST(NULL AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(h.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(NULL AS nvarchar(200)) AS [stage],
    CAST(RTRIM(h.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fAccpHed] h
  LEFT JOIN dbo.[fAccpDet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = d.[ItemCode]

  UNION ALL

  SELECT
    CAST('fmBuAshed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Build Assembly - Cocopeat' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fmBuAshed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(RTRIM(h.[RefNo1]) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(COALESCE(NULLIF(RTRIM(h.[ItemCode]), ''), NULLIF(RTRIM(d.[ItemCode]), '')) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(RTRIM(h.[MachCode]) AS nvarchar(200)) AS [machine],
    CAST(CASE WHEN (d.[RefNo] IS NULL OR d.[_ln] = 1) THEN h.[AloQtyKg] END AS numeric(18,4)) AS [qtyKg],
    CAST(CASE WHEN d.[RefNo] IS NULL THEN h.[AloQty] ELSE d.[QtyPc] END AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(d.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(COALESCE(NULLIF(RTRIM(h.[PrvStagCode]), ''), NULLIF(RTRIM(d.[StagCode]), '')) AS nvarchar(200)) AS [stage],
    CAST(COALESCE(NULLIF(RTRIM(h.[LocCode]), ''), NULLIF(RTRIM(d.[LocCode]), '')) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fmBuAshed] h
  LEFT JOIN (SELECT x.*, ROW_NUMBER() OVER (PARTITION BY x.[RefNo] ORDER BY x.[RecordId]) AS [_ln] FROM dbo.[fmBuAsDet] x) d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = COALESCE(d.[ItemCode], h.[ItemCode])

  UNION ALL

  SELECT
    CAST('fmJbTrnhed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Job Transfer' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fmJbTrnhed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(COALESCE(NULLIF(RTRIM(h.[RefNo1]), ''), NULLIF(RTRIM(d.[RefNo1]), '')) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(COALESCE(NULLIF(RTRIM(h.[ItemCode]), ''), NULLIF(RTRIM(d.[ItemCode]), '')) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(CASE WHEN d.[RefNo] IS NULL THEN h.[Qty] ELSE d.[Qty] END AS numeric(18,4)) AS [qtyKg],
    CAST(CASE WHEN d.[RefNo] IS NULL THEN h.[QtyPc] ELSE d.[Qtypc] END AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(d.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(RTRIM(d.[StagCode]) AS nvarchar(200)) AS [stage],
    CAST(COALESCE(NULLIF(RTRIM(h.[LocCode]), ''), NULLIF(RTRIM(d.[LocCode]), '')) AS nvarchar(200)) AS [location],
    CAST(NULL AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fmJbTrnhed] h
  LEFT JOIN dbo.[fmJbTrnDet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = COALESCE(d.[ItemCode], h.[ItemCode])

  UNION ALL

  SELECT
    CAST('fmFgTranHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Finish Good Transfer' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fmFgTranHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(COALESCE(NULLIF(RTRIM(deb.[DebName]), ''), RTRIM(h.[DebCode])) AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(COALESCE(NULLIF(RTRIM(h.[RefNo1]), ''), NULLIF(RTRIM(d.[RefNo1]), '')) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(RTRIM(d.[ItemCode]) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(CASE WHEN d.[RefNo] IS NULL THEN h.[Qty] ELSE d.[Qty] END AS numeric(18,4)) AS [qtyKg],
    CAST(CASE WHEN d.[RefNo] IS NULL THEN h.[QtyPc] ELSE d.[QtyPc] END AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(d.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(RTRIM(h.[StagCode]) AS nvarchar(200)) AS [stage],
    CAST(RTRIM(d.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(NULL AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fmFgTranHed] h
  LEFT JOIN dbo.[fmFgTranDet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.[fDebtor] deb ON deb.[DebCode] = h.[DebCode]
  LEFT JOIN dbo.fItems it ON it.ItemCode = d.[ItemCode]

  UNION ALL

  SELECT
    CAST('fmWstAshed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Wastage Build Assembly' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fmWstAshed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(NULL AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(COALESCE(NULLIF(RTRIM(h.[ItemCode]), ''), NULLIF(RTRIM(d.[ItemCode]), '')) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(RTRIM(h.[MachCode]) AS nvarchar(200)) AS [machine],
    CAST(NULL AS numeric(18,4)) AS [qtyKg],
    CAST(CASE WHEN (d.[RefNo] IS NULL OR d.[_ln] = 1) THEN h.[AloQty] END AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(d.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(RTRIM(h.[StagCode]) AS nvarchar(200)) AS [stage],
    CAST(RTRIM(d.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fmWstAshed] h
  LEFT JOIN (SELECT x.*, ROW_NUMBER() OVER (PARTITION BY x.[RefNo] ORDER BY x.[RecordId]) AS [_ln] FROM dbo.[fmWstAsDet] x) d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = COALESCE(d.[ItemCode], h.[ItemCode])

  UNION ALL

  SELECT
    CAST('fWstHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Wastage Purchase' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fWstHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(NULL AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(RTRIM(d.[ItemCode]) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(d.[Qty] AS numeric(18,4)) AS [qtyKg],
    CAST(NULL AS numeric(18,4)) AS [pieces],
    CAST(COALESCE(NULLIF(RTRIM(h.[SiteCode]), ''), NULLIF(RTRIM(d.[SiteCode]), '')) AS nvarchar(200)) AS [site],
    CAST(NULL AS nvarchar(200)) AS [stage],
    CAST(COALESCE(NULLIF(RTRIM(h.[LocCode]), ''), NULLIF(RTRIM(d.[LocCode]), '')) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fWstHed] h
  LEFT JOIN dbo.[fWstDet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = d.[ItemCode]

  UNION ALL

  SELECT
    CAST('fmSfAshed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Stretch Film Build Assembly' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fmSfAshed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(NULL AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(COALESCE(NULLIF(RTRIM(h.[ItemCode]), ''), NULLIF(RTRIM(d.[ItemCode]), '')) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(RTRIM(h.[MachCode]) AS nvarchar(200)) AS [machine],
    CAST(NULL AS numeric(18,4)) AS [qtyKg],
    CAST(CASE WHEN (d.[RefNo] IS NULL OR d.[_ln] = 1) THEN h.[AloQty] END AS numeric(18,4)) AS [pieces],
    CAST(COALESCE(NULLIF(RTRIM(h.[SiteCode]), ''), NULLIF(RTRIM(d.[SiteCode]), '')) AS nvarchar(200)) AS [site],
    CAST(RTRIM(h.[StagCode]) AS nvarchar(200)) AS [stage],
    CAST(COALESCE(NULLIF(RTRIM(h.[LocCode]), ''), NULLIF(RTRIM(d.[LocCode]), '')) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fmSfAshed] h
  LEFT JOIN (SELECT x.*, ROW_NUMBER() OVER (PARTITION BY x.[RefNo] ORDER BY x.[RecordId]) AS [_ln] FROM dbo.[fmSfAsDet] x) d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = COALESCE(d.[ItemCode], h.[ItemCode])

  UNION ALL

  SELECT
    CAST('fmJmTrnhed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Jumbo Roll Transfer' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fmJmTrnhed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(RTRIM(d.[refno1]) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(COALESCE(NULLIF(RTRIM(h.[ItemCode]), ''), NULLIF(RTRIM(d.[ItemCode]), '')) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(RTRIM(h.[MachCode]) AS nvarchar(200)) AS [machine],
    CAST(d.[qtykg] AS numeric(18,4)) AS [qtyKg],
    CAST(CASE WHEN (d.[RefNo] IS NULL OR d.[_ln] = 1) THEN h.[AloQty] END AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(d.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(RTRIM(h.[StagCode]) AS nvarchar(200)) AS [stage],
    CAST(RTRIM(d.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fmJmTrnhed] h
  LEFT JOIN (SELECT x.*, ROW_NUMBER() OVER (PARTITION BY x.[RefNo] ORDER BY x.[RecordId]) AS [_ln] FROM dbo.[fmJmTrnDet] x) d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = COALESCE(d.[ItemCode], h.[ItemCode])

  UNION ALL

  SELECT
    CAST('fmMixAshed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Mixer Build Assembly' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fmMixAshed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(NULL AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(COALESCE(NULLIF(RTRIM(h.[ItemCode]), ''), NULLIF(RTRIM(d.[ItemCode]), '')) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(RTRIM(h.[MachCode]) AS nvarchar(200)) AS [machine],
    CAST(NULL AS numeric(18,4)) AS [qtyKg],
    CAST(CASE WHEN (d.[RefNo] IS NULL OR d.[_ln] = 1) THEN h.[AloQty] END AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(d.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(RTRIM(h.[StagCode]) AS nvarchar(200)) AS [stage],
    CAST(RTRIM(d.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fmMixAshed] h
  LEFT JOIN (SELECT x.*, ROW_NUMBER() OVER (PARTITION BY x.[RefNo] ORDER BY x.[RecordId]) AS [_ln] FROM dbo.[fmMixAsDet] x) d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = COALESCE(d.[ItemCode], h.[ItemCode])

  UNION ALL

  SELECT
    CAST('fmpipAshed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(COALESCE(m.name, N'Pipe Build Assembly') AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fmpipAshed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(NULL AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(COALESCE(NULLIF(RTRIM(h.[ItemCode]), ''), NULLIF(RTRIM(d.[ItemCode]), '')) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(RTRIM(h.[machcode]) AS nvarchar(200)) AS [machine],
    CAST(CASE WHEN (d.[RefNo] IS NULL OR d.[_ln] = 1) THEN h.[TotWeight] END AS numeric(18,4)) AS [qtyKg],
    CAST(CASE WHEN (d.[RefNo] IS NULL OR d.[_ln] = 1) THEN h.[TotQty] END AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(d.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(RTRIM(h.[StagCode]) AS nvarchar(200)) AS [stage],
    CAST(RTRIM(d.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fmpipAshed] h
  LEFT JOIN (SELECT x.*, ROW_NUMBER() OVER (PARTITION BY x.[RefNo] ORDER BY x.[RecordId]) AS [_ln] FROM dbo.[fmPipAsdet] x) d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = COALESCE(d.[ItemCode], h.[ItemCode])
  LEFT JOIN txn m ON m.tbl = 'fmpipAshed' AND m.code = RTRIM(h.[TxnType])

  UNION ALL

  SELECT
    CAST('fmlsAshed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Local Sale Build Assembly' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fmlsAshed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(RTRIM(h.[RefNo1]) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(COALESCE(NULLIF(RTRIM(h.[ItemCode]), ''), NULLIF(RTRIM(d.[ItemCode]), '')) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(RTRIM(h.[MachCode]) AS nvarchar(200)) AS [machine],
    CAST(CASE WHEN (d.[RefNo] IS NULL OR d.[_ln] = 1) THEN h.[AloQtyKg] END AS numeric(18,4)) AS [qtyKg],
    CAST(CASE WHEN d.[RefNo] IS NULL THEN h.[AloQty] ELSE d.[QtyPc] END AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(d.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(COALESCE(NULLIF(RTRIM(h.[PrvStagCode]), ''), NULLIF(RTRIM(d.[StagCode]), '')) AS nvarchar(200)) AS [stage],
    CAST(COALESCE(NULLIF(RTRIM(h.[LocCode]), ''), NULLIF(RTRIM(d.[LocCode]), '')) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fmlsAshed] h
  LEFT JOIN (SELECT x.*, ROW_NUMBER() OVER (PARTITION BY x.[RefNo] ORDER BY x.[RecordId]) AS [_ln] FROM dbo.[fmLsAsDet] x) d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = COALESCE(d.[ItemCode], h.[ItemCode])

  UNION ALL

  SELECT
    CAST('fAgloHed:' + RTRIM(h.[RefNo]) AS nvarchar(200)) AS [lineId],
    CAST(N'Aglo Process' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fAgloHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(NULL AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(RTRIM(h.[ItemCode]) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(RTRIM(h.[machcode]) AS nvarchar(200)) AS [machine],
    CAST(h.[Qty] AS numeric(18,4)) AS [qtyKg],
    CAST(NULL AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(h.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(NULL AS nvarchar(200)) AS [stage],
    CAST(RTRIM(h.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fAgloHed] h
  LEFT JOIN dbo.fItems it ON it.ItemCode = h.[ItemCode]

  UNION ALL

  SELECT
    CAST('fmInkAshed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Special Ink Build Assembly' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fmInkAshed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(NULL AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(COALESCE(NULLIF(RTRIM(h.[ItemCode]), ''), NULLIF(RTRIM(d.[ItemCode]), '')) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(RTRIM(h.[MachCode]) AS nvarchar(200)) AS [machine],
    CAST(NULL AS numeric(18,4)) AS [qtyKg],
    CAST(CASE WHEN (d.[RefNo] IS NULL OR d.[_ln] = 1) THEN h.[AloQty] END AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(d.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(RTRIM(h.[StagCode]) AS nvarchar(200)) AS [stage],
    CAST(RTRIM(d.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fmInkAshed] h
  LEFT JOIN (SELECT x.*, ROW_NUMBER() OVER (PARTITION BY x.[RefNo] ORDER BY x.[RecordId]) AS [_ln] FROM dbo.[fmInkAsDet] x) d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = COALESCE(d.[ItemCode], h.[ItemCode])

  UNION ALL

  SELECT
    CAST('FiRIssHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(COALESCE(m.name, N'Issue Note') AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('FiRIssHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(RTRIM(h.[Refno1]) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(RTRIM(d.[ItemCode]) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(d.[Qty] AS numeric(18,4)) AS [qtyKg],
    CAST(NULL AS numeric(18,4)) AS [pieces],
    CAST(COALESCE(NULLIF(RTRIM(h.[SiteCode]), ''), NULLIF(RTRIM(d.[SiteCode]), '')) AS nvarchar(200)) AS [site],
    CAST(NULL AS nvarchar(200)) AS [stage],
    CAST(COALESCE(NULLIF(RTRIM(h.[LocCode]), ''), NULLIF(RTRIM(d.[LocCode]), '')) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[FiRIssHed] h
  LEFT JOIN dbo.[FiRIssDet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = d.[ItemCode]
  LEFT JOIN txn m ON m.tbl = 'FiRIssHed' AND m.code = RTRIM(h.[TxnType])

  UNION ALL

  SELECT
    CAST('fiAccpHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'INK Acceptance Note' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fiAccpHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(RTRIM(h.[RefNo1]) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(RTRIM(d.[ItemCode]) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(d.[Qty] AS numeric(18,4)) AS [qtyKg],
    CAST(NULL AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(h.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(NULL AS nvarchar(200)) AS [stage],
    CAST(RTRIM(h.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fiAccpHed] h
  LEFT JOIN dbo.[fiAccpDet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = d.[ItemCode]

  UNION ALL

  SELECT
    CAST('flRIssHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(COALESCE(m.name, N'Issue Note (Lami)') AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('flRIssHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(NULL AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(RTRIM(d.[ItemCode]) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(d.[Qty] AS numeric(18,4)) AS [qtyKg],
    CAST(NULL AS numeric(18,4)) AS [pieces],
    CAST(COALESCE(NULLIF(RTRIM(h.[SiteCode]), ''), NULLIF(RTRIM(d.[SiteCode]), '')) AS nvarchar(200)) AS [site],
    CAST(RTRIM(h.[StagCode]) AS nvarchar(200)) AS [stage],
    CAST(COALESCE(NULLIF(RTRIM(h.[LocCode]), ''), NULLIF(RTRIM(d.[LocCode]), '')) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[flRIssHed] h
  LEFT JOIN dbo.[flrissdet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = d.[ItemCode]
  LEFT JOIN txn m ON m.tbl = 'flRIssHed' AND m.code = RTRIM(h.[TxnType])

  UNION ALL

  SELECT
    CAST('fBldAsmbHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Build Assembly' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fBldAsmbHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(COALESCE(NULLIF(RTRIM(h.[RefNo1]), ''), NULLIF(RTRIM(d.[RefNo1]), '')) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(RTRIM(d.[ItemCode]) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(RTRIM(h.[machcode]) AS nvarchar(200)) AS [machine],
    CAST(d.[Qty] AS numeric(18,4)) AS [qtyKg],
    CAST(NULL AS numeric(18,4)) AS [pieces],
    CAST(COALESCE(NULLIF(RTRIM(h.[FSiteCode]), ''), NULLIF(RTRIM(d.[SiteCode]), '')) AS nvarchar(200)) AS [site],
    CAST(NULL AS nvarchar(200)) AS [stage],
    CAST(COALESCE(NULLIF(RTRIM(h.[FLocCode]), ''), NULLIF(RTRIM(d.[LocCode]), '')) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fBldAsmbHed] h
  LEFT JOIN dbo.[fBldAsmbDet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = d.[ItemCode]

  UNION ALL

  SELECT
    CAST('flrIssRHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Issue Return (Lami)' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('flrIssRHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(RTRIM(h.[RefNo1]) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(RTRIM(d.[ItemCode]) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(d.[Qty] AS numeric(18,4)) AS [qtyKg],
    CAST(NULL AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(h.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(NULL AS nvarchar(200)) AS [stage],
    CAST(RTRIM(h.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[flrIssRHed] h
  LEFT JOIN dbo.[flrissrdet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = d.[ItemCode]

  UNION ALL

  SELECT
    CAST('fmLspAsHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Packing & Other Build Assembly' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fmLspAsHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(NULL AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(COALESCE(NULLIF(RTRIM(h.[ItemCode]), ''), NULLIF(RTRIM(d.[ItemCode]), '')) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(RTRIM(h.[machcode]) AS nvarchar(200)) AS [machine],
    CAST(NULL AS numeric(18,4)) AS [qtyKg],
    CAST(CASE WHEN (d.[RefNo] IS NULL OR d.[_ln] = 1) THEN h.[AloQty] END AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(d.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(RTRIM(h.[StagCode]) AS nvarchar(200)) AS [stage],
    CAST(RTRIM(d.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fmLspAsHed] h
  LEFT JOIN (SELECT x.*, ROW_NUMBER() OVER (PARTITION BY x.[RefNo] ORDER BY x.[RecordId]) AS [_ln] FROM dbo.[fmLspAsdet] x) d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = COALESCE(d.[ItemCode], h.[ItemCode])

  UNION ALL

  SELECT
    CAST('FMixOHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Mix INK Out Note' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('FMixOHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(RTRIM(h.[RefNo1]) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(RTRIM(d.[ItemCode]) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(d.[Qty] AS numeric(18,4)) AS [qtyKg],
    CAST(NULL AS numeric(18,4)) AS [pieces],
    CAST(COALESCE(NULLIF(RTRIM(h.[SiteCode]), ''), NULLIF(RTRIM(d.[SiteCode]), '')) AS nvarchar(200)) AS [site],
    CAST(NULL AS nvarchar(200)) AS [stage],
    CAST(COALESCE(NULLIF(RTRIM(h.[LocCode]), ''), NULLIF(RTRIM(d.[LocCode]), '')) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[FMixOHed] h
  LEFT JOIN dbo.[FMixODet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = d.[ItemCode]

  UNION ALL

  SELECT
    CAST('fMixHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Mix INK In Entry' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fMixHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(RTRIM(h.[jobno]) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(RTRIM(d.[ItemCode]) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(d.[Qty] AS numeric(18,4)) AS [qtyKg],
    CAST(NULL AS numeric(18,4)) AS [pieces],
    CAST(COALESCE(NULLIF(RTRIM(h.[SiteCode]), ''), NULLIF(RTRIM(d.[SiteCode]), '')) AS nvarchar(200)) AS [site],
    CAST(NULL AS nvarchar(200)) AS [stage],
    CAST(RTRIM(h.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(NULL AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fMixHed] h
  LEFT JOIN dbo.[fMixDet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = d.[ItemCode]

  UNION ALL

  SELECT
    CAST('fmStTrnhed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(COALESCE(m.name, N'Site Transfer') AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fmStTrnhed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(COALESCE(NULLIF(RTRIM(h.[RefNo1]), ''), NULLIF(RTRIM(d.[RefNo1]), '')) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(COALESCE(NULLIF(RTRIM(h.[ItemCode]), ''), NULLIF(RTRIM(d.[ItemCode]), '')) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(CASE WHEN d.[RefNo] IS NULL THEN h.[Qty] ELSE d.[Qty] END AS numeric(18,4)) AS [qtyKg],
    CAST(CASE WHEN d.[RefNo] IS NULL THEN h.[QtyPc] ELSE d.[Qtypc] END AS numeric(18,4)) AS [pieces],
    CAST(COALESCE(NULLIF(RTRIM(h.[sitecode]), ''), NULLIF(RTRIM(d.[SiteCode]), '')) AS nvarchar(200)) AS [site],
    CAST(COALESCE(NULLIF(RTRIM(h.[stagcode]), ''), NULLIF(RTRIM(d.[StagCode]), '')) AS nvarchar(200)) AS [stage],
    CAST(COALESCE(NULLIF(RTRIM(h.[LocCode]), ''), NULLIF(RTRIM(d.[LocCode]), '')) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[comcode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fmStTrnhed] h
  LEFT JOIN dbo.[fmStTrnDet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = COALESCE(d.[ItemCode], h.[ItemCode])
  LEFT JOIN txn m ON m.tbl = 'fmStTrnhed' AND m.code = RTRIM(h.[TxnType])

  UNION ALL

  SELECT
    CAST('fmRFgTranHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Roll FGTransfer' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fmRFgTranHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(COALESCE(NULLIF(RTRIM(deb.[DebName]), ''), RTRIM(h.[DebCode])) AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(COALESCE(NULLIF(RTRIM(h.[RefNo1]), ''), NULLIF(RTRIM(d.[RefNo1]), '')) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(RTRIM(d.[ItemCode]) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(CASE WHEN d.[RefNo] IS NULL THEN h.[Qty] ELSE d.[Qty] END AS numeric(18,4)) AS [qtyKg],
    CAST(CASE WHEN d.[RefNo] IS NULL THEN h.[QtyPc] ELSE d.[QtyPc] END AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(d.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(RTRIM(h.[StagCode]) AS nvarchar(200)) AS [stage],
    CAST(RTRIM(d.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(NULL AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fmRFgTranHed] h
  LEFT JOIN dbo.[fmRFgTranDet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.[fDebtor] deb ON deb.[DebCode] = h.[DebCode]
  LEFT JOIN dbo.fItems it ON it.ItemCode = d.[ItemCode]

  UNION ALL

  SELECT
    CAST('fmRStTrnhed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Site Transfer Entry-SF' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fmRStTrnhed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(COALESCE(NULLIF(RTRIM(h.[RefNo1]), ''), NULLIF(RTRIM(d.[RefNo1]), '')) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(COALESCE(NULLIF(RTRIM(h.[ItemCode]), ''), NULLIF(RTRIM(d.[ItemCode]), '')) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(CASE WHEN d.[RefNo] IS NULL THEN h.[Qty] ELSE d.[Qty] END AS numeric(18,4)) AS [qtyKg],
    CAST(CASE WHEN d.[RefNo] IS NULL THEN h.[QtyPc] ELSE d.[Qtypc] END AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(d.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(RTRIM(d.[StagCode]) AS nvarchar(200)) AS [stage],
    CAST(COALESCE(NULLIF(RTRIM(h.[LocCode]), ''), NULLIF(RTRIM(d.[LocCode]), '')) AS nvarchar(200)) AS [location],
    CAST(NULL AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fmRStTrnhed] h
  LEFT JOIN dbo.[fmRStTrnDet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = COALESCE(d.[ItemCode], h.[ItemCode])

  UNION ALL

  SELECT
    CAST('fOpnhed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Job Wise Opening Balance' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fOpnhed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(COALESCE(NULLIF(RTRIM(deb.[DebName]), ''), RTRIM(h.[DebCode])) AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(COALESCE(NULLIF(RTRIM(h.[JobNo]), ''), NULLIF(RTRIM(d.[JobNo]), '')) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(RTRIM(d.[ItemCode]) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(NULL AS numeric(18,4)) AS [qtyKg],
    CAST(NULL AS numeric(18,4)) AS [pieces],
    CAST(COALESCE(NULLIF(RTRIM(h.[SiteCode]), ''), NULLIF(RTRIM(d.[SiteCode]), '')) AS nvarchar(200)) AS [site],
    CAST(RTRIM(h.[StagCode]) AS nvarchar(200)) AS [stage],
    CAST(COALESCE(NULLIF(RTRIM(h.[LocCode]), ''), NULLIF(RTRIM(d.[LocCode]), '')) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fOpnhed] h
  LEFT JOIN dbo.[fstkin] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.[fDebtor] deb ON deb.[DebCode] = h.[DebCode]
  LEFT JOIN dbo.fItems it ON it.ItemCode = d.[ItemCode]

  UNION ALL

  SELECT
    CAST('fmLFgTranHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'FG Transfer (Lami)' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fmLFgTranHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(COALESCE(NULLIF(RTRIM(deb.[DebName]), ''), RTRIM(h.[DebCode])) AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(RTRIM(h.[RefNo1]) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(COALESCE(NULLIF(RTRIM(h.[ItemCode]), ''), NULLIF(RTRIM(d.[ItemCode]), '')) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(CASE WHEN d.[RefNo] IS NULL THEN h.[Qty] ELSE d.[Qty] END AS numeric(18,4)) AS [qtyKg],
    CAST(CASE WHEN (d.[RefNo] IS NULL OR d.[_ln] = 1) THEN h.[QtyPc] END AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(h.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(RTRIM(h.[StagCode]) AS nvarchar(200)) AS [stage],
    CAST(RTRIM(h.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(NULL AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fmLFgTranHed] h
  LEFT JOIN (SELECT x.*, ROW_NUMBER() OVER (PARTITION BY x.[RefNo] ORDER BY x.[RecordId]) AS [_ln] FROM dbo.[fmLFgTranDet] x) d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.[fDebtor] deb ON deb.[DebCode] = h.[DebCode]
  LEFT JOIN dbo.fItems it ON it.ItemCode = COALESCE(d.[ItemCode], h.[ItemCode])

  UNION ALL

  SELECT
    CAST('flmWstInHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Waste In - Lami' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('flmWstInHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(NULL AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(RTRIM(d.[ItemCode]) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(d.[Qty] AS numeric(18,4)) AS [qtyKg],
    CAST(NULL AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(h.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(NULL AS nvarchar(200)) AS [stage],
    CAST(RTRIM(h.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[flmWstInHed] h
  LEFT JOIN dbo.[flmWstInDet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = d.[ItemCode]

  UNION ALL

  SELECT
    CAST('fmSizTranHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Pipe Size Transfer' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fmSizTranHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(NULL AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(COALESCE(NULLIF(RTRIM(h.[ItemCode]), ''), NULLIF(RTRIM(d.[ItemCode]), '')) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(CASE WHEN (d.[RefNo] IS NULL OR d.[_ln] = 1) THEN h.[TotWeight] END AS numeric(18,4)) AS [qtyKg],
    CAST(CASE WHEN (d.[RefNo] IS NULL OR d.[_ln] = 1) THEN h.[TotQty] END AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(d.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(RTRIM(h.[StagCode]) AS nvarchar(200)) AS [stage],
    CAST(RTRIM(d.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[ComCode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fmSizTranHed] h
  LEFT JOIN (SELECT x.*, ROW_NUMBER() OVER (PARTITION BY x.[RefNo] ORDER BY x.[RecordId]) AS [_ln] FROM dbo.[fmSizTrandet] x) d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = COALESCE(d.[ItemCode], h.[ItemCode])

  UNION ALL

  SELECT
    CAST('fmPStTrnhed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(N'Site Transfer Entry-Pipe' AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('fmPStTrnhed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(NULL AS nvarchar(200)) AS [customer],
    CAST(NULL AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(COALESCE(NULLIF(RTRIM(h.[RefNo1]), ''), NULLIF(RTRIM(d.[RefNo1]), '')) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(COALESCE(NULLIF(RTRIM(h.[ItemCode]), ''), NULLIF(RTRIM(d.[ItemCode]), '')) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(CASE WHEN d.[RefNo] IS NULL THEN h.[Qty] ELSE d.[Qty] END AS numeric(18,4)) AS [qtyKg],
    CAST(CASE WHEN d.[RefNo] IS NULL THEN h.[QtyPc] ELSE d.[Qtypc] END AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(d.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(RTRIM(d.[StagCode]) AS nvarchar(200)) AS [stage],
    CAST(COALESCE(NULLIF(RTRIM(h.[LocCode]), ''), NULLIF(RTRIM(d.[LocCode]), '')) AS nvarchar(200)) AS [location],
    CAST(NULL AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[fmPStTrnhed] h
  LEFT JOIN dbo.[fmPStTrnDet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.fItems it ON it.ItemCode = COALESCE(d.[ItemCode], h.[ItemCode])

  UNION ALL

  SELECT
    CAST('frDispHed:' + RTRIM(h.[RefNo]) + ISNULL(':' + CAST(d.[RecordId] AS varchar(30)), '') AS nvarchar(200)) AS [lineId],
    CAST(COALESCE(m.name, N'Dispatch') AS nvarchar(200)) AS [type],
    CAST(RTRIM(h.[TxnType]) AS nvarchar(200)) AS [txnType],
    CAST('frDispHed' AS nvarchar(200)) AS [source],
    CAST(RTRIM(h.[RefNo]) AS nvarchar(200)) AS [reference],
    CAST(COALESCE(NULLIF(RTRIM(deb.[DebName]), ''), RTRIM(h.[DebCode])) AS nvarchar(200)) AS [customer],
    CAST(RTRIM(h.[RefNo1]) AS nvarchar(200)) AS [invoiceNo],
    CAST(h.[TxnDate] AS date) AS [date],
    CAST(RTRIM(d.[RefNo1]) AS nvarchar(200)) AS [jobNo],
    CAST(RTRIM(h.[ManuRef]) AS nvarchar(200)) AS [manualNo],
    CAST(RTRIM(d.[ItemCode]) AS nvarchar(200)) AS [itemCode],
    CAST(RTRIM(it.ItemName) AS nvarchar(200)) AS [itemName],
    CAST(NULL AS nvarchar(200)) AS [machine],
    CAST(COALESCE(NULLIF(d.[QtyKG], 0), d.[Qty]) AS numeric(18,4)) AS [qtyKg],
    CAST(NULL AS numeric(18,4)) AS [pieces],
    CAST(RTRIM(h.[SiteCode]) AS nvarchar(200)) AS [site],
    CAST(NULL AS nvarchar(200)) AS [stage],
    CAST(RTRIM(h.[LocCode]) AS nvarchar(200)) AS [location],
    CAST(RTRIM(h.[comcode]) AS nvarchar(200)) AS [company],
    CAST(RTRIM(h.[CostCode]) AS nvarchar(200)) AS [costCentre]
  FROM dbo.[frDispHed] h
  LEFT JOIN dbo.[frDispDet] d ON d.[RefNo] = h.[RefNo]
  LEFT JOIN dbo.[fDebtor] deb ON deb.[DebCode] = h.[DebCode]
  LEFT JOIN dbo.fItems it ON it.ItemCode = d.[ItemCode]
  LEFT JOIN txn m ON m.tbl = 'frDispHed' AND m.code = RTRIM(h.[TxnType])
;
GO

SELECT TOP 20 * FROM dbo.v_manufacturing_quick ORDER BY [date] DESC;