SELECT Distinct shmt_no, shmt_id, cnsg_id, shpr_id, shmt_item_count,
source_oloc_id, destination_oloc_id, shmt_note, shmt_created_date,
shmt_doc_ref_no, styp_type,shmt_damage, shmt_is_invoice_attached, shmt_is_packing_list_attached,
shmt_recd_created_date, shmt_recd_modified_date,
shmt_timestamp, rcpt_id, shmt_has_chrg_share_rule,
rcpt_modified_date, rcpt_timestamp,
styp_is_active, styp_modified_date, styp_timestamp, ssta_id,
shps_modified_date, shps_timestamp, cnsg_cust_id, cnsg_name, cnsg_address, cnsg_city,
cnsg_state, cnsg_country, cnsg_postal_code, cnsg_telephone1,
cnsg_telephone2, cnsg_cell_phone, cnsg_modified_date,
cnsg_timestamp, shpr_cust_id, shpr_name,
shpr_address, shpr_city, shpr_country, shpr_state, shpr_postal_code,
shpr_telephone1, shpr_telephone2, shpr_cell_phone, shpr_modified_date,
shpr_timestamp, source_oloc_name, destination_oloc_name , destination_oloc_currency,
source_oloc_short_name ,destination_oloc_short_name ,source_oloc_currency, dlrl_bltype, dlpl_id,
dlrl_id, dlpl_name, dlpl_area_code, dlpl_state, shmt_dangerous,
oloc_id, dlpl_is_default, dlpl_is_active, dlpl_modified_date,
dlpl_timestamp , dtyp_id, dtyp_name , deli_place_oloc_id,
source_port_id, destination_port_id, shmt_transp_mode, shmt_loading_mode,
shmt_shipping_mode, shmt_shipping_direction, source_port_name, destination_port_name, created_oloc_name, styp_id
FROM (SELECT Shipments.shmt_no, Shipments.shmt_id, Shipments.cnsg_id, Shipments.shpr_id, Shipments.shmt_item_count, Shipments.source_oloc_id,
Shipments.source_port_id, Shipments.destination_port_id, Shipments.shmt_transp_mode, Shipments.shmt_loading_mode,
Shipments.shmt_shipping_mode, Shipments.shmt_shipping_direction, Shipments.oloc_id, Shipments.destination_oloc_id,
Shipments.shmt_dangerous, Shipments.shmt_note, Shipments.shmt_created_date, Shipments.shmt_doc_ref_no, Shipment_Types.styp_type, Shipments.styp_id,
Shipments.shmt_damage, Shipments.shmt_is_invoice_attached, Shipments.shmt_is_packing_list_attached, Shipments.shmt_recd_created_date,
Shipments.shmt_recd_modified_date, CONVERT(Varchar, CAST(Shipments.shmt_timestamp AS datetime), 13) AS shmt_timestamp, Receipts.rcpt_id,
Shipments.shmt_has_chrg_share_rule, Receipts.rcpt_modified_date, CONVERT(Varchar, CAST(Receipts.rcpt_timestamp AS datetime), 13)
AS rcpt_timestamp, Shipment_Types.styp_is_active, Shipment_Types.styp_modified_date, CONVERT(Varchar,
CAST(Shipment_Types.styp_timestamp AS datetime), 13) AS styp_timestamp, Shipment_Shipment_States.ssta_id,
Shipment_Shipment_States.shps_modified_date, CONVERT(Varchar, CAST(Shipment_Shipment_States.shps_timestamp AS datetime), 13)
AS shps_timestamp, Consignees.cnsg_cust_id, Consignees.cnsg_name, Consignees.cnsg_address, Consignees.cnsg_city,
Consignees.cnsg_state, Consignees.cnsg_country, Consignees.cnsg_postal_code, Consignees.cnsg_telephone1, Consignees.cnsg_telephone2,
Consignees.cnsg_cell_phone, Consignees.cnsg_modified_date, CONVERT(Varchar, CAST(Consignees.cnsg_timestamp AS datetime), 13)
AS cnsg_timestamp, Shippers.shpr_cust_id, Shippers.shpr_name, Shippers.shpr_address, Shippers.shpr_city, Shippers.shpr_country,
Shippers.shpr_state, Shippers.shpr_postal_code, Shippers.shpr_telephone1, Shippers.shpr_telephone2, Shippers.shpr_cell_phone,
Shippers.shpr_modified_date, CONVERT(Varchar, CAST(Shippers.shpr_timestamp AS datetime), 13) AS shpr_timestamp,
OFFICE_LOCATIONS_1.oloc_name AS source_oloc_name, Office_Locations.oloc_name AS destination_oloc_name,
OFFICE_LOCATIONS_1.oloc_short_name AS source_oloc_short_name, Office_Locations.oloc_short_name AS destination_oloc_short_name,
OFFICE_LOCATIONS_1.oloc_currency AS source_oloc_currency, Office_Locations.oloc_currency AS destination_oloc_currency,
vw_Shmt_itms.jobs_id, vw_Shmt_itms.jobs_no, vw_Shmt_itms.coit_id, Delivery_Place_Relations.dlrl_bltype,
ISNULL(Delivery_Place_Relations.dlpl_id, 0) AS dlpl_id, Delivery_Place_Relations.dlrl_id, Delivery_Places.dlpl_name,
Delivery_Places.dlpl_area_code, Delivery_Places.dlpl_state, Delivery_Places.oloc_id AS deli_place_oloc_id, Delivery_Places.dlpl_is_default,
Delivery_Places.dlpl_is_active, Delivery_Places.dlpl_modified_date, CONVERT(Varchar, CAST(Delivery_Places.dlpl_timestamp AS datetime), 13)
AS dlpl_timestamp, Delivery_Types.dtyp_name, Shipments.dtyp_id, Ports.port_name AS source_port_name,
Ports_1.port_name AS destination_port_name, Office_Locations_2.oloc_name AS created_oloc_name
FROM Office_Locations AS OFFICE_LOCATIONS_1 RIGHT OUTER JOIN
Ports RIGHT OUTER JOIN
Shipments INNER JOIN
Receipts ON Shipments.shmt_id = Receipts.shmt_id INNER JOIN
Shipment_Types ON Shipments.styp_id = Shipment_Types.styp_id LEFT OUTER JOIN
Shipment_Shipment_States ON Shipments.shmt_id = Shipment_Shipment_States.shmt_id INNER JOIN
Shippers ON Shipments.shpr_id = Shippers.shpr_id INNER JOIN
Consignees ON Shipments.cnsg_id = Consignees.cnsg_id INNER JOIN
Office_Locations ON Shipments.destination_oloc_id = Office_Locations.oloc_id INNER JOIN
vw_Shmt_itms ON Shipments.shmt_id = vw_Shmt_itms.shmt_id INNER JOIN
Ports AS Ports_1 ON Shipments.destination_port_id = Ports_1.port_id LEFT OUTER JOIN
Office_Locations AS Office_Locations_2 ON Shipments.oloc_id = Office_Locations_2.oloc_id ON Ports.port_id = Shipments.source_port_id ON
OFFICE_LOCATIONS_1.oloc_id = Shipments.source_oloc_id LEFT OUTER JOIN
Delivery_Types ON Shipments.dtyp_id = Delivery_Types.dtyp_id LEFT JOIN
Delivery_Place_Relations ON Shipments.shmt_id = Delivery_Place_Relations.dlrl_blitem_id AND
Delivery_Place_Relations.dlrl_bltype = ' ApprovalItemType.SHIPMENT.ToString() ' LEFT JOIN
Delivery_Places ON Delivery_Place_Relations.dlpl_id = Delivery_Places.dlpl_id ) AS TAB ;