1.dms库存
    image.png
    2.dmp

    1. select count(1)
    2. --select o.orderno,c.orderno,c.contractno,
    3.  --      o.dealerallowancedate,o.driveracceptime,
    4.  --      c.salesdate,c.salescanceldate
    5. from
    6. (select * from
    7. (select orderno,dealerallowancedate,driveracceptime,operate_type,
    8.         row_number() over(partition by orderno order by updatetime desc ,load_time desc)rn
    9.         from
    10.     dl.tg_tact_snd_orderno_tb
    11.     where cast(batch_id as string) like '22%'
    12.     and length(cast(batch_id as string)) = 9)aa
    13.     where rn=1 and operate_type<>'D')o
    14. left join
    15. (select * from
    16. (select orderno,contractno,salesdate,salescanceldate,operate_type,
    17.         row_number() over(partition by contractno order by updatetime desc ,load_time desc)rn
    18.     from
    19.     dl.tg_tact_vhc_order_tb
    20.     where cast(batch_id as string) like '22%'
    21.     and length(cast(batch_id as string)) = 9)aa
    22.     where rn=1 and operate_type<>'D')c
    23. on o.orderno=c.orderno
    24. where 1=1
    25. and trim(o.dealerallowancedate)<>''
    26. and trim(o.driveracceptime)<>''
    27. and nvl(c.salesdate,'')=''
    28. and nvl(c.salescanceldate,'')=''

    image.png

    image.png
    image.png
    旧：台账表中发生调拨的
    image.png
    select orderno,frameno,contractno,dealercode,salesdate,salescanceldate,canceldate,
    operate_type,updatetime,load_time
    from dl.tg_tact_vhc_order_tb where frameno=’LVGCJE237HG188031’;
    旧：这个订单发生了调拨
    image.png
    新dms:发生调拨，厂家订单表dealer_code不会变
    image.png
    image.png
    旧台账表中，发生调拨，新起一条记录，dlrcode变为目标店，supplier原店
    image.png
    调拨明细怎么查？
    客户订单表对应的店是否就是最终销售店，如发生调拨，则是调拨目标店？
    dms：发生调拨，客户订单销售店是调拨目标店
    image.png

    ：
    (一).供货商问题-旧台账表数据探查结果（根据数据判断）：

    • 首次下单：supplier为00000，dlrcode为下单店，orderno为对应下单店的厂家订单
    • 发生调拨：supplier为原店，dlrcode为目标店，orderno为空

    image.png
    (二).dms写旧台账表现状(以下现状导致ids统计库存与dms不一致)：

    • dms的supplier(未维护)写旧表supplier
    • dms车辆台账表中sales_dealer_code(向厂家下单时的店，发生调拨，且销售不会变为调拨目标店)写旧表dlrcode
    • dms台账表中sales_date(有维护，但是存在部分数据异常-客户订单表中有销售日，台账表中无销售日)写入旧表salesdate

    (三).解决：

    1. 未调拨，供货商: 00000，
    2. 发生调拨，旧表供货商: 向厂家下单时的店 ,旧表dlrcode: 调拨目标店，orderno跟随dms厂家订单表中dvp_order_number
    3. dms解决台账表中销售日异常数据问题，dmp同时取台账表中的sales_date与客户订单表中的sales_date写入旧台账表salesdate

    店头库存：4/21 20:52查询
    image.png
    4/21
    select * from dl.tg_tact_snd_orderno_tb_0421 where frameno = ‘LVGD656F4MG044831’ order by updatetime desc;
    这个单历史数据为空
    image.png
    4/22
    数据流各层数据一致
    image.png

    1. select '旧dl表' as '表',count(distinct orderno)
    2. from dl.tg_tact_snd_orderno_tb_0421
    3.            where cast(batch_id as string)  like '22%'
    4.     and length(cast(batch_id as string)) = 9
    5. union all
    6. select '新ods中间表' as '表',count(distinct dvp_order_number)
    7. from ods.ods_dms_cs_dvp_order_collect
    8. union all
    9. select '新ods表' as '表', count(distinct dvp_order_number)
    10. from ods.ods_dms_cs_dvp_order
    11. union all
    12. select '新dl表' as '表', count(distinct dvp_order_number)
    13. from dl.tg_dms_t_dvp_order_base where biz_date<'20220422'
    14. ;

    image.png

    select '旧dl表' as '表',count(distinct orderno)
from dl.tg_tact_vhc_ledjer_tb_0421
           where cast(batch_id as string)  like '22%'
    and length(cast(batch_id as string)) = 9
union all
select '新ods中间表' as '表',count(distinct dvp_order_number)
from ods.ods_dms_cs_vhcl_ledger_collect
union all
select '新ods表' as '表', count(distinct dvp_order_number)
from ods.ods_dms_cs_vhcl_ledger
union all
select '新dl表' as '表', count(distinct dvp_order_number)
from dl.tg_dms_t_vhcl_ledger where biz_date<'20220422';