产品中心DB组业务、技术问题汇总
上海伯俊软件科技有限公司产品中心DB组业务、技术问题汇总1 业务相关1.1 OMS1.1.1 分配物流公司错误1.配置判断：首先确认是店铺物流规则还是仓库物流规则（系统参数）判断想送达的物流公司是否满足该区域（物流区域设置）判断对应的物流规则是否维护该物流公司（物流规则）判断店铺物流规则/仓库物流规则是否维护该物流规则（店铺物流规则/仓库物流规）2.存储过程：sp_esp_matchexpress单步调试，存储过程会返回物流规则，如果调试出来发现有物流规则，则判断是否因为其他原因导致物流被覆盖或者该物流配置修改过（重新占单会覆盖物流公司BUG）,如果调试出来没有大概率是配置问题3.历史处理（1）因为该物流公司不满足该区域，未设置可送达 by hxq（2）存储过程bug 分配后的物流会被sp_oms_transnormalordfoatsk重新修改为null by hxq（3）转换中间表存储过程没有把物流转换下来，平台二次退单后没有转换物流公司到线上订单的逻辑 by hxq1.1.2 库存锁订单，分销库存表冻结量为负数问题排查1.业务逻辑：锁库存即在WING发生库存占用释放时及时同步给BOS进行OMS冻结量的占用释放2.存储过程：传占库存存储过程（根据占单流水的自动任务）sp_otb_onlineorderout_read传释放存储过程（出库调用）sp_otb_occupy_create_retail3.历史处理(1)查询负冻结量库存select * from fa_storage a where a.qty_oms<0;-43162 6570(2)查询其中一个的分销释放流水select oms_ebonum,sum(cqty) from oms_storage_freeitem a where a.c_store_id=43162 and a.m_productalias_id=6570 group by a.oms_ebonum having sum(a.cqty)<>0;-1506124(3)查询具体单子select * from oms_storage_freeitem a where a.oms_ebonum=1506124;(4)查询oms在单select * from t_omsorderoccupyhistory a where a.omsonlineorderid=1506124;1.1.3 分配发货仓1.配置判断：该实体仓对应的虚拟仓是否关联该店铺（虚拟店仓管理-店铺管理）该实体仓可送达区域是否满足（实体店仓管理）2.存储过程：sp_oms_distrordsendstore单步调试，如果调试出来有实体仓说明客户修改过配置或之后因为什么程序被覆盖如果没有调试出来，将存储过程重命名在里面用wmconcat函数查询临时表放入变量查看该实体仓因为那个逻辑被排除3.历史处理（1）实体仓对应的可送达区域不满足 by hxq(2) 实体仓分配优先级排序,以下顺序执行，注意重点是排序，说明是存在最起码满足省区域条件的实体仓： - by chenmina.如果是总仓优先条件。店铺对应供货仓、供货仓对应实体仓库存、及实体仓配送区域都满足情况按实体仓优先级选择，数字越大优先级越高（ORDER BY weight DESC）b.店铺对应供货仓、供货仓对应实体仓库存满足，实体仓配送区域不做判断选择库存深度最大的发货仓c.以上两种逻辑都分配不到发货仓按缺货默认仓、库存深度、优先级的顺序排序（ORDER BY nvl(a.isdefaultwhenoos, 0) DESC, a.stk_qtyfee DESC,a.weight DESC）1.1.4 转换天猫换货单/退换货单金额不对1.业务判断：查看系统日志，判断是否有人修改过金额退换的商品应退金额取的是退的商品的原单成交价2.存储过程：sp_oms_addtbexchange（换货中间表生成退换货单）sp_oms_addrefundorder（退换货单生成换货订单）找到对应的生成订单的逻辑，把SELECT拿出来，查出结果查，看金额是不是正常，正确的话考虑是不是其他地方修改，不正常的话修改逻辑1.1.5 订单锁库存冻结量问题检查-对比单据和oms库存表在单是否一致WITH oms_occu AS (SELECT iesstoreid, sku, SUM(a.changenum) oms_occu_num, xmlagg(xmlparse(content a.omsonlineorderid | : | b.orderstatus | , wellformed) ORDER BY a.omsonlineorderid) .getclobval() oms_orderid FROM t_omsorderoccupyhistory a LEFT JOIN t_omsonlineorder b ON a.omsonlineorderid = b.id WHERE b.orderstatus IN (1, 2, 3, 4, 21, 50) GROUP BY iesstoreid, sku HAVING SUM(a.changenum) <> 0),jit_occu AS (SELECT c.id iesstoreid, b.sku, SUM(b.x_num) jit_occu_num, xmlagg(xmlparse(content a.id | : | a.status | , wellformed) ORDER BY a.id) .getclobval() jit_orderid FROM t_omsjitdistribution a LEFT JOIN t_omsjitdistributionitem b ON a.id = b.omsjitdistributionid INNER JOIN t_iesstore c ON a.ieswarehouseid = c.ieswarehouseid WHERE a.status IN (1, 3, 4) GROUP BY c.id, b.sku),vipcom_occu AS (SELECT iesstoreid, a.barcode sku, SUM(a.changenum) oms_occu_num, xmlagg(xmlparse(content a.omsvipcomorderid | : | b.status | , wellformed) ORDER BY a.omsvipcomorderid) .getclobval() vipcom_orderid FROM t_omsvipcomoccupyhistory a LEFT JOIN t_omsvipcomorder b ON a.omsvipcomorderid = b.id WHERE b.status IN (1, 2, 0) GROUP BY iesstoreid, a.barcode HAVING SUM(a.changenum) <> 0),oxo_occu AS (SELECT iesstoreid, a.barcode sku, SUM(a.changenum) oms_occu_num, xmlagg(xmlparse(content a.omsvipoxoorderid | : | b.status | , wellformed) ORDER BY a.omsvipoxoorderid) .getclobval() oxo_orderid FROM t_omsoxoordoccupyhistory a LEFT JOIN t_omsvipoxoorder b ON a.omsvipoxoorderid = b.id WHERE b.status IN (1, 2, 0, 4, 3, 5) GROUP BY iesstoreid, a.barcode HAVING SUM(a.changenum) <> 0),t_oms_jit_occu AS (SELECT k.id, k.iesstoreid, k.sku, nvl(a.oms_occu_num, 0) oms_occu_num, nvl(b.jit_occu_num, 0) jit_occu_num, nvl(c.oms_occu_num, 0) vipcom_occu_num, nvl(d.oms_occu_num, 0) oxo_occu_num, nvl(a.oms_occu_num, 0) + nvl(b.jit_occu_num, 0) occu_num, nvl(c.oms_occu_num, 0) + nvl(d.oms_occu_num, 0) other_occu_num, a.oms_orderid, c.vipcom_orderid, d.oxo_orderid, b.jit_orderid, k.omsqtyout, k.otherqtyout FROM t_iesstock k LEFT JOIN oms_occu a ON a.iesstoreid = k.iesstoreid AND a.sku = k.sku LEFT JOIN jit_occu b ON k.iesstoreid = b.iesstoreid AND k.sku = b.sku LEFT JOIN vipcom_occu c ON k.iesstoreid = c.iesstoreid AND k.sku = c.sku LEFT JOIN oxo_occu d ON k.iesstoreid = d.iesstoreid AND k.sku = d.sku)SELECT b.rowid, a.id, a.iesstoreid, a.sku, OMSQTYOUT占单情况： desc1, a.oms_orderid, a.jit_orderid, a.oms_occu_num, a.jit_occu_num, nvl(a.occu_num, 0) occu_num, a.omsqtyout, OTHERQTYOUT占单情况： desc2, a.vipcom_orderid, a.oxo_orderid, a.vipcom_occu_
