INSERT OVERWRITE TABLE dm_prod_domain_delivery_b2b_qd (
select
'Y' AS edw_valid_flag,
'Y' AS edw_data_source,
now () AS edw_create_date,
now () AS edw_last_update,
t20.contract_number,
t20.line_number,
t20.meaning1,
t20.contract_type_name1,
t20.quantity,
t20.contract_type_name,
t20.meaning,
t20.master_code,
t20.actual_effect_date,
t20.issue_date,
t20.order_header_id,
t20.customer_sku,
t20.customer_sku_desc,
t20.icc_sbom,
t20.icc_gbom,
t20.gname,
t20.contract_number_cor,
t20.contract_gbom,
t20.contract_line_id,
t20.salesrep_name,
t20.salesdep_name,
t20.order_line_id,
t20.b2b_line_id,
sum(t20.scan_qty) as scan_qty,
t20.sfjd,
t21.overall_unit_name, ---机型名称
t21.device_type, ---机型
NVL (T31.product_model_class, t30.jx_dl) AS jx_dl
FROM
(
select --家端
DISTINCT '家端' AS sfjd,
t1.contract_number as contract_number, --合同号
t1.line_number as line_number, --行号
t1.meaning1 as meaning1, --行状态
t1.contract_type_name1 as contract_type_name1, --行类型
t1.quantity as quantity, --行数量
t1.contract_type_name as contract_type_name, --合同类型
t1.meaning as meaning, --合同状态
t1.master_code AS master_code, --产品大类
t1.actual_effect_date as actual_effect_date, --合同生效日期
t1.issue_date as issue_date, --合同发货完成日期
t2.order_header_id as order_header_id, --订单头ID
t3.customer_sku as customer_sku, --客户物料代码
t3.customer_sku_desc as customer_sku_desc, --客户物料名称
t3.sbom as icc_sbom, --icc销售物料代码
t3.gbom as icc_gbom, --icc生产物料代码
t7.description as gname, --生产物料代码名称
case
when t1.conclude_mode_code = 'ZZ' then CONCAT_WS ('', t1.contract_number, 'Z1')
else t1.contract_number
end as contract_number_cor, --股份合同号
case
when t3.customer_sku is not null then t4.gbom
else t5.item_code
end as contract_gbom, --合同GBOM
cast(T1.contract_line_id as double) as contract_line_id, --合同行ID
t6.l4_org_cn_name as salesrep_name, --销售处名称
t6.l2_org_cn_name as salesdep_name, --事业部名称
t3.order_line_id as order_line_id, --订单行
t1.b2b_line_id AS b2b_line_id, --合同B2B订单行ID
nvl (t5.scan_qty, 0) as scan_qty --发货数
from
dm_prod_domain_delivery_otc_quantity_req t1
left join ods_icc_b2b_order_header t2 on t2.contract_number = t1.contract_number
left join ods_icc_b2b_order_line t3 on t3.order_line_id = t1.b2b_line_id
left join dm_prod_domain_delivery_sg_relationship t4 on t4.sbom = t3.sbom
left join (
select
t25.contract_number,
T25.item_code,
t25.delivery_line_id,
t25.item_code,
sum(t25.scan_qty) as scan_qty
from
edw_sup_inv_iwms_do_list_detail t25
group by
t25.contract_number,
t25.delivery_line_id,
t25.item_code
) t5 on T5.delivery_line_id = cast(T1.contract_line_id as double)
left join edw_contract_related_info t6 on t6.contract_number = t1.contract_number
left join dm_prod_domain_delivery_gbom_name t7 on t7.segment1 = case
when t3.customer_sku is not null then t4.gbom
else t5.item_code
end
where
t3.gbom is null
union
select DISTINCT
'非家端' AS sfjd,
t1.contract_number as contract_number, --合同号
t1.line_number as line_number, --行号
t1.meaning1 as meaning1, --行状态
t1.contract_type_name1 as contract_type_name1, --行类型
t1.quantity as quantity, --行数量
t1.contract_type_name as contract_type_name, --合同类型
t1.meaning as meaning, --合同状态
t1.master_code AS master_code, --产品大类
t1.actual_effect_date as actual_effect_date, --合同生效日期
t1.issue_date as issue_date, --合同发货完成日期
t2.order_header_id as order_header_id, --订单头ID
t3.customer_sku as customer_sku, --客户物料代码
t3.customer_sku_desc as customer_sku_desc, --客户物料名称
t3.sbom as icc_sbom, --icc销售物料代码
t3.gbom as icc_gbom, --icc生产物料代码
t7.description as gname, --生产物料代码名称
case
when t1.conclude_mode_code = 'ZZ' then CONCAT_WS ('', t1.contract_number, 'Z1')
else t1.contract_number
end as contract_number_cor, --股份合同号
case
when t3.customer_sku is not null then t3.gbom
else t5.item_code
end as contract_gbom, --合同GBOM
cast(T1.contract_line_id as double) as contract_line_id, --合同行ID
t6.l4_org_cn_name as salesrep_name, --销售处名称
t6.l2_org_cn_name as salesdep_name, --事业部名称
t3.order_line_id as order_line_id, --订单行
t1.b2b_line_id AS b2b_line_id, --合同B2B订单行ID
nvl (t5.scan_qty, 0) as scan_qty --发货数
from
dm_prod_domain_delivery_otc_quantity_req t1
left join ods_icc_b2b_order_header t2 on t2.contract_number = t1.contract_number
left join ods_icc_b2b_order_line t3 on t3.order_line_id = t1.b2b_line_id
left join (
select
t25.contract_number,
T25.item_code,
t25.delivery_line_id,
t25.item_code,
sum(t25.scan_qty) as scan_qty
from
edw_sup_inv_iwms_do_list_detail t25
group by
t25.contract_number,
t25.delivery_line_id,
t25.item_code
) t5 on T5.delivery_line_id = cast(T1.contract_line_id as double)
left join edw_contract_related_info t6 on t6.contract_number = t1.contract_number
left join dm_prod_domain_delivery_gbom_name t7 on t7.segment1 = case
when t3.customer_sku is not null then t3.gbom
else t5.item_code
end
where
t3.gbom is not null
) t20
left join (
select DISTINCT
t222.prod_class,
t222.item_no,
t222.overall_unit_name, ---机型名称
t222.device_type ---机型
from
dm_prcd_ods_scp_ba_overall_unit_head1 t222
) t21 on t20.contract_gbom = t21.item_no
AND t21.prod_class = T20.master_code
left join dm_prod_mps_supply_cycle_tzz t30 on t21.device_type = t30.jd_name
left join ods_scp_aps_product_model t31 on t31.product_model_name = t21.device_type
group by
t20.contract_number,
t20.line_number,
t20.meaning1,
t20.contract_type_name1,
t20.quantity,
t20.contract_type_name,
t20.meaning,
t20.master_code,
t20.actual_effect_date,
t20.issue_date,
t20.order_header_id,
t20.customer_sku,
t20.customer_sku_desc,
t20.icc_sbom,
t20.icc_gbom,
t20.gname,
t20.contract_number_cor,
t20.contract_gbom,
t20.contract_line_id,
t20.salesrep_name,
t20.salesdep_name,
t20.order_line_id,
t20.b2b_line_id,
t20.sfjd,
t21.overall_unit_name, ---机型名称
t21.device_type, ---机型
NVL (T31.product_model_class, t30.jx_dl)
)
本文检测到视频解码过程中的Interpolation和Deblocking问题，并推荐使用X264、JM、P264等解决方案进行修正。
扫一扫
2633
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
