SELECT     TOP (100) PERCENT dbo.so_ORDERS_va.PON
  dbo.so_ORDERS_va.One_Source_Id_Parent + dbo.so_ORDER_NICKNAMES_va.One_Source_Id_Child AS One_Source_ID,
  dbo.so_CUSTOMERS_va.Customer,
  NULL AS Action,
  CASE
    WHEN (Cancel_Date_Parent > CAST('01/01/1900' AS DATE)) THEN 'YES'
    WHEN (Cancel_Date_Child > CAST('01/01/1900' AS DATE)) THEN 'YES'
    ELSE 'NO'
  END AS Canceled,
  dbo.so_ORDER_NICKNAMES_va.BTN,
  dbo.so_ORDER_PRODUCTS_va.Service_Order_Number,
  dbo.so_ORDER_PRODUCTS_va.CCF,
  dbo.so_PRODUCT_NICKNAMES_va.Product_Nickname,
  dbo.so_ORDER_PRODUCTS_va.Order_Product_Id,
  dbo.so_ORDER_PRODUCTS_va.Order_Sequence,
  dbo.so_ORDERS_va.Internal_Support_Rep_Id,
  dbo.so_ORDERS_va.Agent_Id,
  dbo.so_PRODUCTS_va.Product_Description,
  dbo.so_INTERNAL_SUPPORT_REPS_va.Internal_Support_Rep_Name
FROM
  dbo.so_INTERNAL_SUPPORT_REPS_va
  RIGHT OUTER JOIN dbo.so_ORDERS_va ON dbo.so_INTERNAL_SUPPORT_REPS_va.Internal_Support_Rep_Id = dbo.so_ORDERS_va.Internal_Support_Rep_Id
  LEFT OUTER JOIN dbo.so_CUSTOMERS_va ON dbo.so_INTERNAL_SUPPORT_REPS_va.Internal_Support_Rep_Id = dbo.so_CUSTOMERS_va.Internal_Support_Rep_Id AND dbo.so_ORDERS_va.Customer_Id = dbo.so_CUSTOMERS_va.Customer_Id
  LEFT OUTER JOIN dbo.so_PRODUCTS_va
  RIGHT OUTER JOIN dbo.so_ORDER_PRODUCTS_va ON dbo.so_PRODUCTS_va.Product_Id = dbo.so_ORDER_PRODUCTS_va.Product_Id
  RIGHT OUTER JOIN dbo.so_ORDER_NICKNAMES_va ON dbo.so_ORDER_PRODUCTS_va.Order_Nickname_Id = dbo.so_ORDER_NICKNAMES_va.Order_Nickname_Id
  LEFT OUTER JOIN dbo.so_PRODUCT_NICKNAMES_va ON dbo.so_ORDER_NICKNAMES_va.Product_Nickname_Id = dbo.so_PRODUCT_NICKNAMES_va.Product_Nickname_Id ON
  dbo.so_ORDERS_va.Order_Id = dbo.so_ORDER_NICKNAMES_va.Order_Id
ORDER BY
      One_Source_ID DESC

GO