VLMS订单流

--委托单号
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%'
)