--委托单号
SELECT * FROM TmsBizOrder WHERE BizOrderNO = 'Biz2012052400038'
--任务单号
SELECT * FROM TmsTaskOrder WHERE BizOrderNO = 'Biz2012052400038'
--运输单号
SELECT * FROM TmsTruckOrder WHERE TaskOrderNO IN
(
SELECT TaskOrderNO FROM TmsTaskOrder WHERE BizOrderNO = 'Biz2012052400038'
)
--报关信息表
SELECT * FROM CosCustomsOrder WHERE TaskOrderNO IN
(
SELECT TaskOrderNO FROM TmsTaskOrder WHERE BizOrderNO = 'Biz2012052400038'
)
--发货人信息
SELECT * FROM TmsConsignerInfo WHERE TaskOrderNO IN
(
SELECT TaskOrderNO FROM TmsTaskOrder WHERE BizOrderNO = 'Biz2012052400038'
)
--跟踪信息表
SELECT * FROM TmsTraceInfo WHERE TruckOrderNO IN
(
SELECT TruckOrderNO FROM TmsTruckOrder WHERE TaskOrderNO IN
(
SELECT TaskOrderNO FROM TmsTaskOrder WHERE BizOrderNO = 'Biz2012052400038'
)
)
--报关信息表
SELECT * FROM CosCustomsOrder WHERE TaskOrderNO IN
(
SELECT TaskOrderNO FROM TmsTaskOrder WHERE BizOrderNO = 'Biz2012052400038'
)
--报关文件表
SELECT * FROM CosCustomsDocument WHERE CustomsOrderNo IN
(
SELECT CustomsOrderNo FROM CosCustomsOrder WHERE TaskOrderNO IN
(
SELECT TaskOrderNO FROM TmsTaskOrder WHERE BizOrderNO = 'Biz2012052400038'
)
)
--报关异常表
SELECT * FROM CosCustomsOrderException WHERE CustomsOrderNo IN
(
SELECT CustomsOrderNo FROM CosCustomsOrder WHERE TaskOrderNO IN
(
SELECT TaskOrderNO FROM TmsTaskOrder WHERE BizOrderNO = 'Biz2012052400038'
)
)
--报关打印文件表
--SELECT * FROM CosCustomsPrintOrder WHERE CustomsOrderNo IN
--(
-- SELECT CustomsOrderNo FROM CosCustomsOrder WHERE TaskOrderNO IN
-- (
-- SELECT TaskOrderNO FROM TmsTaskOrder WHERE BizOrderNO = 'Biz2012052400038'
-- )
--)
--调度主表
SELECT * FROM TmsDispatchOrder WHERE TruckOrderNO IN
(
SELECT TruckOrderNO FROM dbo.TmsTruckOrder WHERE TaskOrderNO IN
(
SELECT TaskOrderNO FROM TmsTaskOrder WHERE BizOrderNO = 'Biz2012052400038'
)
)
--发货人信息
SELECT * FROM TmsConsignerInfo WHERE TaskOrderNO IN
(
SELECT TaskOrderNO FROM TmsTaskOrder WHERE BizOrderNO = 'Biz2012052400038'
)
--收货人信息
SELECT * FROM TmsConsigneeInfo WHERE TaskOrderNO IN
(
SELECT TaskOrderNO FROM TmsTaskOrder WHERE BizOrderNO = 'Biz2012052400038'
)
--收货人货物明细
SELECT top 10 * FROM dbo.TmsCargoItem WHERE ConsignerInfoID IN
(
SELECT ConsigneeInfoID FROM TmsConsigneeInfo WHERE TaskOrderNO IN
(
SELECT TaskOrderNO FROM TmsTaskOrder WHERE BizOrderNO = 'Biz2012052400038'
)
)
SELECT * FROM dbo.TmsCargoItem where po = '6869534' and ConsignerInfoID = '0A3E978B-B1EA-4B29-9D24-2D8DD8542994'
--发货人明细
SELECT * FROM dbo.TmsCargoItem WHERE ConsignerInfoID IN
(
SELECT ConsignerInfoID FROM TmsConsignerInfo WHERE TaskOrderNO IN
(
SELECT TaskOrderNO FROM TmsTaskOrder WHERE BizOrderNO = 'Biz2012052400038'
)
)
--日志表
SELECT * FROM TmsBusinessLog WHERE ObjectID = 'Biz2012052400038'
--文件表
SELECT * FROM TmsDocumentInfo WHERE TruckOrderNO IN
(
SELECT TruckOrderNO FROM dbo.TmsTruckOrder WHERE TaskOrderNO IN
(
SELECT TaskOrderNO FROM TmsTaskOrder WHERE BizOrderNO = 'Biz2012052400038'
)
)
--跟踪信息
SELECT * FROM TmsTraceInfo WHERE TruckOrderNO IN
(
SELECT TruckOrderNO FROM dbo.TmsTruckOrder WHERE TaskOrderNO IN
(
SELECT TaskOrderNO FROM TmsTaskOrder WHERE BizOrderNO = 'Biz2012052400038'
)
)ORDER BY TraceIndex
--异常信息
SELECT * FROM dbo.TmsTruckOrderException WHERE TruckOrderNO IN
(
SELECT TruckOrderNO FROM dbo.TmsTruckOrder WHERE TaskOrderNO IN
(
SELECT TaskOrderNO FROM TmsTaskOrder WHERE BizOrderNO = 'Biz2012052400038'
)
)
--附件列表
SELECT * FROM TmsAttachFile WHERE TruckOrderNO IN
(
SELECT TruckOrderNO FROM dbo.TmsTruckOrder WHERE TaskOrderNO IN
(
SELECT TaskOrderNO FROM TmsTaskOrder WHERE BizOrderNO = 'Biz2012052400038'
)
)
--删除信息
SELECT * FROM dbo.TmsBizDeleteInfo WHERE BizOrderNO = 'Biz2012052400038'
--监装表
SELECT * FROM dbo.TmsDataInfo WHERE TruckOrderNO IN
(
SELECT TruckOrderNO FROM dbo.TmsTruckOrder WHERE TaskOrderNO IN
(
SELECT TaskOrderNO FROM TmsTaskOrder WHERE BizOrderNO = 'Biz2012052400038'
)
)
SELECT * FROM dbo.TmsCharge
--应收应付
SELECT * FROM dbo.TmsCharge WHERE ObjectNO IN
(
SELECT TruckOrderNO FROM dbo.TmsTruckOrder WHERE TaskOrderNO IN
(
SELECT TaskOrderNO FROM TmsTaskOrder WHERE BizOrderNO = 'Biz2012052400038'
)
UNION ALL
SELECT CustomsOrderNo FROM dbo.CosCustomsOrder WHERE TaskOrderNO IN
(
SELECT TaskOrderNO FROM TmsTaskOrder WHERE BizOrderNO = 'Biz2012052400038'
)
)
--计费单号
SELECT * FROM FbsChargeMaster WHERE BizOrderNO = 'Biz2012052400038'
--明细
SELECT * FROM FbsChargeDetail WHERE ChargeMasterNo In
(
SELECT ChargeMasterNo FROM FbsChargeMaster WHERE BizOrderNO = 'Biz2012052400038'
)
--车辆实时跟踪信息
SELECT * FROM dbo.TmsSubSectionTraceInfo WHERE TruckOrderNO IN
(
SELECT TruckOrderNO FROM dbo.TmsTruckOrder WHERE TaskOrderNO IN
(
SELECT TaskOrderNO FROM TmsTaskOrder WHERE BizOrderNO = 'Biz2012052400038'
)
) ORDER BY TRACEINDEX
--查询对应路线的费用
SELECT * FROM dbo.TmsStandardTariff WHERE TransportLineID IN
(
SELECT TransportLineID FROM dbo.BasTransportLine WHERE LineDescription = '深圳机场-惠州黄埠-深圳盐田'
)
SELECT * FROM FbsBalanceMaster --结算主表
SELECT * FROM FbsBalanceDetail --结算明细表
SELECT * FROM FbsChargeMaster --应收应付主表
SELECT * FROM FbsChargeDetail --应收应付明细表
SELECT * FROM BasTransportLine --运输线路表
SELECT * FROM TmsDriver --司机资料
SELECT * FROM TmsTruck --车辆信息
SELECT * FROM BasChassis --车辆信息表
SELECT * FROM BasDriver --司机表
SELECT * FROM BasBerth --车位表
SELECT * FROM BasCity --城市表
SELECT * FROM BasCountry --国家编码
SELECT * FROM BasCurrencyRate --汇率表
SELECT * FROM BasDataCategory WHERE DataCategoryCode = 'ChargeType' --数据类别
SELECT * FROM BasDataDictionary WHERE DataCategoryID IN --数据字典
(
SELECT DataCategoryID FROM BasDataCategory WHERE DataCategoryCode = 'ChargeType' --数据类别
)
SELECT * FROM BasExceptionclass --异常类别
SELECT * FROM BasExceptionTrack --异常跟踪
SELECT * FROM BasOrganization --组织结构
SELECT * FROM BasPort --港口信息
SELECT * FROM BasProvince --区域部门
SELECT * FROM dbo.BasLocation --区域表
SELECT * FROM BasQuay --码头
SELECT * FROM BasReportCondition --报表条件
SELECT * FROM BasReportField --报表字段
SELECT * FROM BasReportType --报表类型
SELECT * FROM BasReportTemplate --报表模版
SELECT * FROM BasTruck --牌照表
SELECT * FROM BasTruckOilWear --油耗表
SELECT * FROM BasUnit --单位表
SELECT * FROM Country --国家编码表
SELECT * FROM DeleteLog --删除日志
SELECT * FROM ExchangeRate --汇率表
SELECT * FROM FbsChargeItem --费用项目类别
SELECT * FROM BasTrackItem --跟踪项目类别
SELECT * FROM FbsCredenceMaster --凭证主表
SELECT * FROM FbsCredenceDetail --凭证明细表
SELECT * FROM FbsInvoiceMaster --发票主表
SELECT * FROM FbsInvoiceDetail --发票明细表
SELECT * FROM FbsSystemBankAccount --银行帐号表
SELECT * FROM TmsTruckCost --运输卡车费用表
SELECT * FROM TmsTruckOrderException --运输单异常表
SELECT * FROM dbo.BasDeliveryPlace --区域表
SELECT * FROM dbo.TmsDocumentItem --文件类型 重柜纸
SELECT * FROM dbo.Base_Chassis --拖架
3A0D8599-8992-4D50-9777-EA67602158E3
SELECT * FROM dbo.CrmCustomer WHERE CustomerId = '3A0D8599-8992-4D50-9777-EA67602158E3' --客户表和工厂表 都是同一个表
SELECT * FROM dbo.CrmContact --客户联系人
SELECT * FROM dbo.CrmCustomerActivity --客户活动
SELECT * FROM dbo.CrmBalanceUnit --客户所关联的结算单位 ？？讨论：是否采取WMS的关联方式
SELECT * FROM WMS.dbo.CrmCustomerRelation --客户所关联的结算单位 WMS系统
SELECT * FROM dbo.CrmExchangeRate --客户关联的汇率
SELECT * FROM dbo.CrmBankAccount --客户所关联的银行
SELECT * FROM dbo.CrmCustomerInFactory --客户所关联的工厂
SELECT * FROM dbo.CrmCusAndOrgRelation --客户与子客户的关系 ?? 讨论：这个表是不是可以不建
SELECT TOP 10 * FROM dbo.BasDeliveryPlace WHERE DeliveryPlaceID = 'C44196E9-DFB2-41C8-B7EC-BC01D6B22691' --地名
--报表模板
SELECT * FROM dbo.BasReportType WHERE ReportTypeName like '%CustomsCustomerCharge%'
SELECT * FROM dbo.BasReportField WHERE ReportTypeId in
(
SELECT ReportTypeId FROM dbo.BasReportType WHERE ReportTypeName like '%CustomsCustomerCharge%'
)