SELECT "Sub18__联接3"."外部入库单号" as "外部入库单号", "Sub18__联接3"."头状态" as "头状态", "Sub18__联接3"."最后修改日期" as "最后修改日期", "Sub18__联接3"."收货地址" as "收货地址", "Sub18__联接3"."创建日期" as "创建日期"
, "Sub18__联接3"."数据平台调度时间_1" as "数据平台调度时间_1", "Sub18__联接3"."质检周期" as "质检周期", "Sub18__联接3"."上架周期" as "上架周期", "Sub18__联接3"."前缀编码产品线" as "前缀编码产品线", "Sub18__联接3"."物料大类" as "物料大类"
, "Sub18__联接3"."板规1" as "板规1", "Sub18__联接3"."编码_1" as "编码_1", "Sub18__联接3"."产品线" as "产品线", "Sub18__联接3"."采购模式" as "采购模式", "Sub18__联接3"."月" as "月"
, "Sub18__联接3"."日" as "日", "Sub18__联接3"."供应商CODE" as "供应商CODE", "Sub18__联接3"."供应商名称" as "供应商名称", "Sub18__联接3"."ASN单号" as "ASN单号", "Sub18__联接3"."是否外检" as "是否外检"
, "Sub18__联接3"."交货地点" as "交货地点", "Sub18__联接3"."发运时间" as "发运时间", "Sub18__联接3"."月份" as "月份", "Sub18__联接3"."天数" as "天数", "Sub18__联接3"."状态" as "状态"
, "Sub18__联接3"."收货状态" as "收货状态", "Sub18__联接3"."编码" as "编码", "Sub18__联接3"."物料编码" as "物料编码", "Sub18__联接3"."编号" as "编号", "Sub18__联接3"."条码" as "条码"
, "Sub18__联接3"."发货数量" as "发货数量", "Sub18__联接3"."收货数量" as "收货数量", "Sub18__联接3"."箱件数" as "箱件数", "Sub18__联接3"."数据平台调度时间" as "数据平台调度时间", "Sub18__联接3"."发货月日" as "发货月日"
, "Sub18__联接3"."前缀&产品线" as "前缀&产品线", "Sub18__联接3"."产品型号" as "产品型号", "Sub18__联接3"."前缀编码" as "前缀编码", "Sub18__联接3"."储备类型" as "储备类型", "Sub18__联接3"."编码&供应商" as "编码&供应商"
, "Sub18__联接3"."编码供应商" as "编码供应商", "Sub18__联接3"."中类" as "中类", "Sub18__联接3"."编码板规" as "编码板规", "Sub18__联接3"."预计到货时间" as "预计到货时间", "Sub18__联接3"."产地" as "产地"
, (CASE
WHEN "Sub18__联接3"."物料大类"='采购电源' OR "Sub18__联接3"."物料大类"='自研电源' OR "Sub18__联接3"."物料大类"='数据线/线缆' THEN '朱亮'
WHEN "Sub18__联接3"."物料大类"='包材' THEN '吴文霞'
WHEN "Sub18__联接3"."物料大类"='皮套/PC壳' OR "Sub18__联接3"."物料大类"='结构件' OR "Sub18__联接3"."物料大类"='组件' THEN '舒利文'
ELSE '李晨辉'
END) as "责任人", (CASE
WHEN "Sub18__联接3"."编码板规" IS NOT NULL THEN (("Sub18__联接3"."箱件数" / "Sub18__联接3"."编码板规") + 0.0)
ELSE (("Sub18__联接3"."箱件数" / "Sub18__联接3"."板规1") + 0.0)
END) as "板数"
FROM (SELECT Sub17__QryName."外部入库单号" as "外部入库单号", Sub17__QryName."头状态" as "头状态", Sub17__QryName."最后修改日期" as "最后修改日期", Sub17__QryName."收货地址" as "收货地址", Sub17__QryName."创建日期" as "创建日期"
, Sub17__QryName."数据平台调度时间_1" as "数据平台调度时间_1", Sub17__QryName."质检周期" as "质检周期", Sub17__QryName."上架周期" as "上架周期", Sub17__QryName."编码供应商" as "编码供应商", Sub17__QryName."中类" as "中类"
, Sub17__QryName."编码板规" as "编码板规", Sub17__QryName."前缀编码产品线" as "前缀编码产品线", Sub17__QryName."物料大类" as "物料大类", Sub17__QryName."板规1" as "板规1", Sub17__QryName."编码_1" as "编码_1"
, Sub17__QryName."产品线" as "产品线", Sub17__QryName."采购模式" as "采购模式", Sub17__QryName."月" as "月", Sub17__QryName."日" as "日", Sub17__QryName."供应商CODE" as "供应商CODE"
, Sub17__QryName."供应商名称" as "供应商名称", Sub17__QryName."ASN单号" as "ASN单号", Sub17__QryName."是否外检" as "是否外检", Sub17__QryName."交货地点" as "交货地点", Sub17__QryName."发运时间" as "发运时间"
, Sub17__QryName."月份" as "月份", Sub17__QryName."天数" as "天数", Sub17__QryName."状态" as "状态", Sub17__QryName."收货状态" as "收货状态", Sub17__QryName."编码" as "编码"
, Sub17__QryName."物料编码" as "物料编码", Sub17__QryName."编号" as "编号", Sub17__QryName."条码" as "条码", Sub17__QryName."发货数量" as "发货数量", Sub17__QryName."收货数量" as "收货数量"
, Sub17__QryName."箱件数" as "箱件数", Sub17__QryName."数据平台调度时间" as "数据平台调度时间", Sub17__QryName."发货月日" as "发货月日", Sub17__QryName."前缀&产品线" as "前缀&产品线", Sub17__QryName."产品型号" as "产品型号"
, Sub17__QryName."前缀编码" as "前缀编码", Sub17__QryName."储备类型" as "储备类型", Sub17__QryName."编码&供应商" as "编码&供应商", Sub17__QryName."预计到货时间" as "预计到货时间", Sub17__QryName."产地" as "产地"
FROM (SELECT "Sub15__联接"."供应商名称" as "供应商名称", "Sub15__联接"."日" as "日", "Sub15__联接"."条码" as "条码", "Sub15__联接"."预计到货时间" as "预计到货时间", "Sub15__联接"."编码_1" as "编码_1"
, "Sub15__联接"."月份" as "月份", "Sub15__联接"."箱件数" as "箱件数", "Sub15__联接"."编码板规" as "编码板规", "Sub15__联接"."产地" as "产地", "Sub15__联接"."产品型号" as "产品型号"
, "Sub15__联接"."发运时间" as "发运时间", "Sub15__联接"."发货月日" as "发货月日", "Sub15__联接"."收货状态" as "收货状态", "Sub15__联接"."物料大类" as "物料大类", "Sub15__联接"."天数" as "天数"
, Sub16__QryName."最后修改日期" as "最后修改日期", "Sub15__联接"."发货数量" as "发货数量", "Sub15__联接"."编码" as "编码", "Sub15__联接"."数据平台调度时间" as "数据平台调度时间", Sub16__QryName."头状态" as "头状态"
, "Sub15__联接"."储备类型" as "储备类型", "Sub15__联接"."中类" as "中类", "Sub15__联接"."前缀编码产品线" as "前缀编码产品线", Sub16__QryName."外部入库单号" as "外部入库单号", "Sub15__联接"."物料编码" as "物料编码"
, "Sub15__联接"."编号" as "编号", "Sub15__联接"."前缀编码" as "前缀编码", "Sub15__联接"."编码&供应商" as "编码&供应商", "Sub15__联接"."ASN单号" as "ASN单号", "Sub15__联接"."月" as "月"
, "Sub15__联接"."收货数量" as "收货数量", "Sub15__联接"."编码供应商" as "编码供应商", Sub16__QryName."质检周期" as "质检周期", "Sub15__联接"."前缀&产品线" as "前缀&产品线", "Sub15__联接"."供应商CODE" as "供应商CODE"
, "Sub15__联接"."交货地点" as "交货地点", "Sub15__联接"."产品线" as "产品线", "Sub15__联接"."状态" as "状态", "Sub15__联接"."板规1" as "板规1", Sub16__QryName."上架周期" as "上架周期"
, Sub16__QryName."创建日期" as "创建日期", Sub16__QryName."数据平台调度时间" as "数据平台调度时间_1", "Sub15__联接"."是否外检" as "是否外检", "Sub15__联接"."采购模式" as "采购模式", Sub16__QryName."收货地址" as "收货地址"
FROM ( SELECT "Sub14__联接_BASE"."编码供应商" as "编码供应商", "Sub14__联接_BASE"."中类" as "中类", "Sub14__联接_BASE"."编码板规" as "编码板规", "Sub14__联接_BASE"."前缀编码产品线" as "前缀编码产品线", "Sub14__联接_BASE"."物料大类" as "物料大类"
, "Sub14__联接_BASE"."板规1" as "板规1", "Sub14__联接_BASE"."编码_1" as "编码_1", "Sub14__联接_BASE"."产品线" as "产品线", "Sub14__联接_BASE"."采购模式" as "采购模式", "Sub14__联接_BASE"."月" as "月"
, "Sub14__联接_BASE"."日" as "日", "Sub14__联接_BASE"."供应商CODE" as "供应商CODE", "Sub14__联接_BASE"."供应商名称" as "供应商名称", "Sub14__联接_BASE"."ASN单号" as "ASN单号", "Sub14__联接_BASE"."是否外检" as "是否外检"
, "Sub14__联接_BASE"."交货地点" as "交货地点", "Sub14__联接_BASE"."发运时间" as "发运时间", "Sub14__联接_BASE"."月份" as "月份", "Sub14__联接_BASE"."天数" as "天数", "Sub14__联接_BASE"."状态" as "状态"
, "Sub14__联接_BASE"."收货状态" as "收货状态", "Sub14__联接_BASE"."编码" as "编码", "Sub14__联接_BASE"."物料编码" as "物料编码", "Sub14__联接_BASE"."编号" as "编号", "Sub14__联接_BASE"."条码" as "条码"
, "Sub14__联接_BASE"."发货数量" as "发货数量", "Sub14__联接_BASE"."收货数量" as "收货数量", "Sub14__联接_BASE"."箱件数" as "箱件数", "Sub14__联接_BASE"."数据平台调度时间" as "数据平台调度时间", "Sub14__联接_BASE"."发货月日" as "发货月日"
, "Sub14__联接_BASE"."前缀&产品线" as "前缀&产品线", "Sub14__联接_BASE"."产品型号" as "产品型号", "Sub14__联接_BASE"."前缀编码" as "前缀编码", "Sub14__联接_BASE"."储备类型" as "储备类型", "Sub14__联接_BASE"."编码&供应商" as "编码&供应商"
, "Sub14__联接_BASE"."预计到货时间" as "预计到货时间", "Sub14__联接_BASE"."产地" as "产地"
FROM (SELECT "Sub12__联接2"."前缀&产品线" as "前缀&产品线", Sub13__QryName."编码供应商" as "编码供应商", "Sub12__联接2"."天数" as "天数", "Sub12__联接2"."编码" as "编码", Sub13__QryName."产地" as "产地"
, "Sub12__联接2"."物料编码" as "物料编码", "Sub12__联接2"."收货数量" as "收货数量", Sub13__QryName."中类" as "中类", "Sub12__联接2"."交货地点" as "交货地点", "Sub12__联接2"."箱件数" as "箱件数"
, "Sub12__联接2"."数据平台调度时间" as "数据平台调度时间", "Sub12__联接2"."产品线" as "产品线", "Sub12__联接2"."发货数量" as "发货数量", "Sub12__联接2"."发货月日" as "发货月日", "Sub12__联接2"."板规1" as "板规1"
, "Sub12__联接2"."物料大类" as "物料大类", "Sub12__联接2"."收货状态" as "收货状态", "Sub12__联接2"."日" as "日", "Sub12__联接2"."产品型号" as "产品型号", "Sub12__联接2"."发运时间" as "发运时间"
, "Sub12__联接2"."编号" as "编号", "Sub12__联接2"."供应商名称" as "供应商名称", "Sub12__联接2"."前缀编码" as "前缀编码", "Sub12__联接2"."预计到货时间" as "预计到货时间", "Sub12__联接2"."ASN单号" as "ASN单号"
, "Sub12__联接2"."储备类型" as "储备类型", "Sub12__联接2"."状态" as "状态", Sub13__QryName."编码板规" as "编码板规", "Sub12__联接2"."编码_1" as "编码_1", "Sub12__联接2"."供应商CODE" as "供应商CODE"
, "Sub12__联接2"."月份" as "月份", "Sub12__联接2"."月" as "月", "Sub12__联接2"."编码&供应商" as "编码&供应商", "Sub12__联接2"."前缀编码产品线" as "前缀编码产品线", "Sub12__联接2"."条码" as "条码"
, "Sub12__联接2"."是否外检" as "是否外检", "Sub12__联接2"."采购模式" as "采购模式"
FROM ( SELECT Sub10__QryName."前缀编码产品线" as "前缀编码产品线", Sub10__QryName."物料大类" as "物料大类", Sub10__QryName."板规1" as "板规1", Sub10__QryName."编码_1" as "编码_1", Sub10__QryName."产品线" as "产品线"
, Sub10__QryName."采购模式" as "采购模式", Sub10__QryName."月" as "月", Sub10__QryName."日" as "日", Sub10__QryName."供应商CODE" as "供应商CODE", Sub10__QryName."供应商名称" as "供应商名称"
, Sub10__QryName."ASN单号" as "ASN单号", Sub10__QryName."是否外检" as "是否外检", Sub10__QryName."交货地点" as "交货地点", Sub10__QryName."发运时间" as "发运时间", Sub10__QryName."月份" as "月份"
, Sub10__QryName."天数" as "天数", Sub10__QryName."状态" as "状态", Sub10__QryName."收货状态" as "收货状态", Sub10__QryName."编码" as "编码", Sub10__QryName."物料编码" as "物料编码"
, Sub10__QryName."编号" as "编号", Sub10__QryName."条码" as "条码", Sub10__QryName."发货数量" as "发货数量", Sub10__QryName."收货数量" as "收货数量", Sub10__QryName."箱件数" as "箱件数"
, Sub10__QryName."数据平台调度时间" as "数据平台调度时间", Sub10__QryName."发货月日" as "发货月日", Sub10__QryName."前缀&产品线" as "前缀&产品线", Sub10__QryName."产品型号" as "产品型号", Sub10__QryName."前缀编码" as "前缀编码"
, Sub10__QryName."储备类型" as "储备类型", CONCAT(Sub10__QryName."编码",Sub10__QryName."供应商CODE") as "编码&供应商", Sub10__QryName."预计到货时间" as "预计到货时间"
FROM (SELECT "Sub8__联接1"."收货状态" as "收货状态", Sub9__QryName."前缀编码" as "前缀编码", "Sub8__联接1"."发运时间" as "发运时间", Sub9__QryName."前缀编码产品线" as "前缀编码产品线", "Sub8__联接1"."编码_1" as "编码_1"
, "Sub8__联接1"."状态" as "状态", "Sub8__联接1"."供应商CODE" as "供应商CODE", "Sub8__联接1"."月份" as "月份", "Sub8__联接1"."数据平台调度时间" as "数据平台调度时间", "Sub8__联接1"."收货数量" as "收货数量"
, "Sub8__联接1"."交货地点" as "交货地点", "Sub8__联接1"."条码" as "条码", "Sub8__联接1"."日" as "日", "Sub8__联接1"."箱件数" as "箱件数", "Sub8__联接1"."编码" as "编码"
, "Sub8__联接1"."天数" as "天数", "Sub8__联接1"."预计到货时间" as "预计到货时间", "Sub8__联接1"."产品型号" as "产品型号", "Sub8__联接1"."采购模式" as "采购模式", "Sub8__联接1"."产品线" as "产品线"
, "Sub8__联接1"."是否外检" as "是否外检", "Sub8__联接1"."储备类型" as "储备类型", "Sub8__联接1"."供应商名称" as "供应商名称", Sub9__QryName."物料大类" as "物料大类", "Sub8__联接1"."编号" as "编号"
, "Sub8__联接1"."ASN单号" as "ASN单号", "Sub8__联接1"."月" as "月", Sub9__QryName."板规1" as "板规1", "Sub8__联接1"."物料编码" as "物料编码", "Sub8__联接1"."发货数量" as "发货数量"
, "Sub8__联接1"."前缀&产品线" as "前缀&产品线", "Sub8__联接1"."发货月日" as "发货月日"
FROM ( SELECT Sub6__QryName."月" as "月", Sub6__QryName."日" as "日", Sub6__QryName."供应商CODE" as "供应商CODE", Sub6__QryName."供应商名称" as "供应商名称", Sub6__QryName."ASN单号" as "ASN单号"
, Sub6__QryName."是否外检" as "是否外检", Sub6__QryName."交货地点" as "交货地点", Sub6__QryName."发运时间" as "发运时间", Sub6__QryName."月份" as "月份", Sub6__QryName."天数" as "天数"
, Sub6__QryName."状态" as "状态", Sub6__QryName."收货状态" as "收货状态", Sub6__QryName."编码" as "编码", Sub6__QryName."物料编码" as "物料编码", Sub6__QryName."编号" as "编号"
, Sub6__QryName."条码" as "条码", Sub6__QryName."发货数量" as "发货数量", Sub6__QryName."收货数量" as "收货数量", Sub6__QryName."箱件数" as "箱件数", Sub6__QryName."数据平台调度时间" as "数据平台调度时间"
, Sub6__QryName."发货月日" as "发货月日", Sub6__QryName."编码_1" as "编码_1", Sub6__QryName."产品线" as "产品线", Sub6__QryName."采购模式" as "采购模式", Sub6__QryName."产品型号" as "产品型号"
, CONCAT(Sub6__QryName."编号",Sub6__QryName."产品线") as "前缀&产品线", Sub6__QryName."储备类型" as "储备类型", Sub6__QryName."预计到货时间" as "预计到货时间"
FROM (SELECT Sub4__QryName."数据平台调度时间" as "数据平台调度时间", Sub4__QryName."月份" as "月份", Sub4__QryName."是否外检" as "是否外检", Sub4__QryName."条码" as "条码", Sub5__QryName."产品线" as "产品线"
, Sub4__QryName."物料编码" as "物料编码", Sub5__QryName."编码" as "编码_1", Sub5__QryName."储备类型" as "储备类型", Sub5__QryName."产品型号" as "产品型号", Sub4__QryName."供应商CODE" as "供应商CODE"
, Sub4__QryName."状态" as "状态", Sub4__QryName."日" as "日", Sub4__QryName."发货数量" as "发货数量", Sub4__QryName."发货月日" as "发货月日", Sub4__QryName."收货状态" as "收货状态"
, Sub4__QryName."发运时间" as "发运时间", Sub4__QryName."预计到货时间" as "预计到货时间", Sub4__QryName."编号" as "编号", Sub4__QryName."天数" as "天数", Sub4__QryName."箱件数" as "箱件数"
, Sub5__QryName."采购模式" as "采购模式", Sub4__QryName."编码" as "编码", Sub4__QryName."收货数量" as "收货数量", Sub4__QryName."ASN单号" as "ASN单号", Sub4__QryName."供应商名称" as "供应商名称"
, Sub4__QryName."月" as "月", Sub4__QryName."交货地点" as "交货地点"
FROM ( SELECT "月", "日", "供应商CODE", "供应商名称", "ASN单号"
, "是否外检", "交货地点", "发运时间", "月份", "天数"
, "状态", "收货状态", "编码", "物料编码", "编号"
, "条码", "发货数量", "收货数量", "箱件数", "数据平台调度时间"
, CONCAT(CONCAT("月", '.'), "日") as "发货月日", "预计到货时间"
FROM (
SELECT LPAD(CAST(月份 AS VARCHAR(2)), 2, '0') as 月
, LPAD(CAST(天数 AS VARCHAR(2)), 2, '0') as 日
, *
FROM (
SELECT 供应商CODE, 供应商名称, ASN单号, 是否外检, 交货地点
, 发运时间, MONTH(发运时间) as 月份, DAYOFMONTH(发运时间) as 天数
, 状态
, CASE
WHEN 状态 = 'FULL RECEIVED' THEN '已收货'
WHEN 状态 = 'PART RECEIVED' THEN '收货中'
ELSE '待收货'
END as 收货状态, VENDOR_ITEM_CODE as 编码, 物料编码, 编号, 条码
, 发货数量, 收货数量, 箱件数, 预计到货时间, 数据平台调度时间
FROM asn_shipment_line_v
WHERE 交货地点 LIKE '%CrossDock%'
AND 1 = 1
) 主表
) ) Sub4__QryName
LEFT OUTER JOIN
( SELECT "编码", "产品线", "采购模式", "产品型号", "储备类型"
FROM (select "编码", "产品线", "采购模式", "储备类型", "产品型号" from file_zfbl_cpxjb_slw
) Sub2__QryName ) Sub5__QryName ON Sub4__QryName."编码" = Sub5__QryName."编码"
) Sub6__QryName ) "Sub8__联接1"
LEFT OUTER JOIN
( SELECT "前缀编码产品线", "物料大类", "板规1", "前缀编码"
FROM (select 前缀编码产品线,前缀编码,物料大类,CAST(板规 as FLOAT) as 板规1 from file_zfbl_cpxbg_slw
) Sub7__QryName ) Sub9__QryName ON "Sub8__联接1"."前缀&产品线" = Sub9__QryName."前缀编码产品线"
) Sub10__QryName ) "Sub12__联接2"
LEFT OUTER JOIN
( SELECT 编码供应商 as "编码供应商", 中类 as "中类", CAST(板规 AS FLOAT) as "编码板规", 产地 as "产地"
FROM file_zfbl_bmjbg_slw ) Sub13__QryName ON "Sub12__联接2"."编码&供应商" = Sub13__QryName."编码供应商"
) "Sub14__联接_BASE" ) "Sub15__联接"
LEFT OUTER JOIN
( SELECT "外部入库单号", "头状态", "最后修改日期", "收货地址", "创建日期"
, "数据平台调度时间", "质检周期", "上架周期"
FROM (select
外部入库单号,
收货地址,
头状态,
最后修改日期,
创建日期,
数据平台调度时间,
CASE
when 头状态 = '收货完成' then TIMESTAMPDIFF(HOUR,最后修改日期,数据平台调度时间)
else ' '
END as 质检周期,
CASE
when 头状态 = '质检完成' then TIMESTAMPDIFF(HOUR,最后修改日期,数据平台调度时间)
else ' '
END as 上架周期
from
cwms3_receipt_h_rmcc_v
where
收货地址 LIKE '%CrossDock%'
) Sub1__QryName ) Sub16__QryName ON "Sub15__联接"."ASN单号" = Sub16__QryName."外部入库单号"
) Sub17__QryName
) "Sub18__联接3" 在这个代码上添加质检不合格的时间
最新发布
扫一扫
1163
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
