SIM手持验证脚本
系统运维原创SIM大约 5 分钟约 1596 字
1. 永续订单
1.1. 查看是否是SIM供应商
供应商无效
SELECT T.ID_SPR, T.NM_SPR
FROM PA_SPR T
WHERE T.ST_CRT_PO_IND = 'Y'
AND T.STATUS = 'A'
AND T.ID_SPR = 供应商编号
1.2. 判断是否存在进行中的退货单
此供应商下存在退货单! 2代表进行中的退货单
SELECT ID,TO_CHAR(CREATE_DATE,'YYYY-MM-DD') CREATE_DATE FROM "RETURN"
WHERE SOURCE_ID = 供应商编号
AND STORE_ID= 门店编号
AND STATUS = 2
1.3. 根据供应商查询今日未完成的永续订单
此供应商今日存在未完成订单！
SELECT T.ORDER_ID,T.SOURCE_ID
FROM RK_ORDERS T
WHERE T.ORDER_ID LIKE 'SIM%'
AND T.ID_STR_RT =门店编号}
AND T.SOURCE_ID =供应商编号
AND T.STATUS='100'
AND TRUNC(T.CREATE_DATE) = TRUNC(SYSDATE)
ORDER BY T.CREATE_DATE DESC
1.4. 验证商品是否存在
商品不存在
SELECT T.ID_ITM, T.DE_ITM
FROM AS_ITM T
WHERE T.ID_ITM IN (SELECT IM.ID_ITM
FROM AS_ITM IM
WHERE IM.ID_ITM = 商品编号
AND IM.ITEM_LEVEL = IM.TRANS_LEVEL
UNION ALL
SELECT IM.ID_PRT_ITM ID_ITM
FROM AS_ITM IM
WHERE IM.ID_ITM =商品编号
AND IM.ITEM_LEVEL > IM.TRANS_LEVEL
)
AND T.ITEM_LEVEL = T.TRANS_LEVEL
AND T.STATUS = 'A'
AND T.ITEM_TYPE = '0'
1.5. 永续订单查看商品是否有效
商品不在订单中！
SELECT T.ID_ITM,
T.RK_LONG_DESC,
T.RP_SLS_CRT,
(100 + T.CD_VAT) * TO_NUMBER(SUBSTR(T.TY_PRC_RT, 4)) / 100 TY_PRC_RT,
T.CD_VAT ,
T.LU_UOM,
T.ID_SPR,
C.UOM_DESC,
RISC.CASE_SIZE,
T.SC_ITM
FROM AS_ITM_RTL_STR T,UOM_CLASS C,RK_ITEM_SUPP_COUNTRY RISC
WHERE T.ITEM_TYPE = '0'
AND T.ID_STR_RT = 门店编号
AND T.ID_SPR = 供应商编号
AND T.ID_ITM =商品编号
AND TRIM(T.LU_UOM) = TRIM(C.UOM)
AND RISC.ID_SPR = T.ID_SPR
AND T.ID_ITM = RISC.ID_ITM
2. 直送订单
2.1. 查询看订单是否存在以及状态
订单不存在! 订单已完成收货!
SELECT T.ORDER_ID,
T.SOURCE_ID,
T.STATUS,
SP.NM_SPR,
T.COMMENT_DESC
FROM RK_ORDERS T, PA_SPR SP
WHERE T.ORDER_ID =订单号
AND T.SOURCE_ID = SP.ID_SPR
AND T.ID_STR_RT = 门店编号 AND T.ID_SPR = 供应商
2.2. 判断是否存在进行中的退货单
此供应商下存在退货单! 2代表进行中的退货单
SELECT ID,TO_CHAR(CREATE_DATE,'YYYY-MM-DD') CREATE_DATE FROM "RETURN"
WHERE SOURCE_ID = #{sourceId}
AND STORE_ID= #{storeId}
AND STATUS = 2
2.3. 查看单条商品明细
商品不在订单中
SELECT T.ID_ITM ITEM_ID,
T.RK_LONG_DESC DE_ITM,
RISC.CASE_SIZE PACK_SIZE,
T.LU_UOM LU_UOM_DFT,
S.ORDER_ID,
S.SHIPMENT_ID,
RSI.QUANTITY_EXPECTED,
RSI.QUANTITY_RECEIVED,
RSI.RECEIPT_DOC_ID,
RSI.UNIT_COST,
UC.UOM_DESC,
T.SC_ITM
FROM AS_ITM_RTL_STR T,
RK_SHIPMENTS S,
RK_SHIPMENT_ITEM RSI,
RK_ITEM_SUPP_COUNTRY RISC,
UOM_CLASS UC
WHERE T.ID_STR_RT = 门店编号
AND T.ID_ITM = 商品编号
AND T.ID_SPR = S.FROM_LOCATION_ID
AND S.FROM_LOCATION_TYPE = 'SP'
AND T.ITEM_TYPE = '0'
AND S.ORDER_ID =订单编号
AND S.SHIPMENT_ID = RSI.SHIPMENT_ID
AND RSI.ITEM_ID = 商品编号
AND S.STATUS = 1
AND RISC.ID_ITM = T.ID_ITM
AND RISC.ID_SPR = T.ID_SPR
AND TRIM(UC.UOM) = TRIM(T.LU_UOM)
2.4. 验证商品是否存在
商品不存在
SELECT T.ID_ITM, T.DE_ITM
FROM AS_ITM T
WHERE T.ID_ITM IN (SELECT IM.ID_ITM
FROM AS_ITM IM
WHERE IM.ID_ITM = 商品编号
AND IM.ITEM_LEVEL = IM.TRANS_LEVEL
UNION ALL
SELECT IM.ID_PRT_ITM ID_ITM
FROM AS_ITM IM
WHERE IM.ID_ITM =商品编号
AND IM.ITEM_LEVEL > IM.TRANS_LEVEL
3. 配送
3.1. 查询看订单是否存在
订单不存在! 订单已完成收货!
SELECT T.SHIPMENT_ID,
T.STATUS,
T.FROM_LOCATION_ID,
T.ASN_ID,
WS.NM_ORGN FROM_NM
FROM RK_SHIPMENTS T, BMP_WH_ST WS
WHERE T.ASN_ID = 配送单
AND T.STORE_ID = 门店编号
AND T.FROM_LOCATION_ID = WS.ID_STR_RT
3.2. 判断是否存在进行中的退货单
此供应商下存在退货单! 2代表进行中的退货单
SELECT ID,TO_CHAR(CREATE_DATE,'YYYY-MM-DD') CREATE_DATE FROM "RETURN"
WHERE SOURCE_ID = #{sourceId}
AND STORE_ID= #{storeId}
AND STATUS = 2
3.3. 验证商品是否存在
商品不存在
SELECT T.ID_ITM, T.DE_ITM
FROM AS_ITM T
WHERE T.ID_ITM IN (SELECT IM.ID_ITM
FROM AS_ITM IM
WHERE IM.ID_ITM = 商品编号
AND IM.ITEM_LEVEL = IM.TRANS_LEVEL
UNION ALL
SELECT IM.ID_PRT_ITM ID_ITM
FROM AS_ITM IM
WHERE IM.ID_ITM =商品编号
AND IM.ITEM_LEVEL > IM.TRANS_LEVEL
3.4. 查看单条商品明细
商品无效！
SELECT T.ID_ITM ITEM_ID,
T.RK_LONG_DESC DE_ITM,
RISC.CASE_SIZE PACK_SIZE,
S.ORDER_ID,
S.SHIPMENT_ID,
T.LU_UOM LU_UOM_DFT,
RSI.QUANTITY_EXPECTED,
RSI.QUANTITY_RECEIVED,
RSI.RECEIPT_DOC_ID,
UC.UOM_DESC,
T.SC_ITM
FROM AS_ITM_RTL_STR T,
RK_SHIPMENTS S,
RK_SHIPMENT_ITEM RSI,
RK_ITEM_SUPP_COUNTRY RISC,
UOM_CLASS UC
WHERE T.ID_ITM = RSI.ITEM_ID
AND S.FROM_LOCATION_TYPE = 'WH'
AND T.ITEM_TYPE = '0'
AND S.SHIPMENT_ID = RSI.SHIPMENT_ID
AND S.STATUS = 1
AND RISC.ID_ITM = T.ID_ITM
AND RISC.ID_SPR = T.ID_SPR
AND TRIM(UC.UOM) = TRIM(T.LU_UOM)
AND RSI.ITEM_ID = #{itemId}
AND T.ID_STR_RT = #{idStrRt}
AND S.SHIPMENT_ID = #{shipmentId}
4. 调拨单
4.1. 验证商品是否存在
商品不存在
SELECT T.ID_ITM, T.DE_ITM
FROM AS_ITM T
WHERE T.ID_ITM IN (SELECT IM.ID_ITM
FROM AS_ITM IM
WHERE IM.ID_ITM = 商品编号
AND IM.ITEM_LEVEL = IM.TRANS_LEVEL
UNION ALL
SELECT IM.ID_PRT_ITM ID_ITM
FROM AS_ITM IM
WHERE IM.ID_ITM =商品编号
AND IM.ITEM_LEVEL > IM.TRANS_LEVEL
4.2. 查看单条商品明细
门店商品无效！
SELECT T.ID_ITM,
A.RK_LONG_DESC,
(T.TOTAL_QUANTITY - T.RESERVED_QUANTITY - T.RTV_QUANTITY) TOTAL_QUANTITY,
A.LU_UOM,
C.CASE_SIZE,
U.UOM_DESC,
A.SC_ITM
FROM RK_STORE_ITEM_SOH T,
AS_ITM_RTL_STR A,
RK_ITEM_SUPP_COUNTRY C,
UOM_CLASS U
WHERE T.ID_STR_RT = 门店编号
AND T.ID_ITM= 商品编号
AND T.ID_ITM = A.ID_ITM
AND T.ID_STR_RT = A.ID_STR_RT
AND A.ITEM_TYPE = '0'
AND A.ID_ITM = C.ID_ITM
AND A.ID_SPR = C.ID_SPR
AND TRIM(A.LU_UOM) = TRIM(U.UOM)
5. 退货订单
5.1. 查询看订单是否存在
退货订单不存在! 该订单取消或已发运!
SELECT ret.ID ID,
ret.EXTERNAL_ID EXTERNAL_ID,
ret.SOURCE_ID SOURCE_ID,
spr.NM_ORGN sourceIdName,
to_char(ret.CREATE_DATE, 'yyyy-mm-dd') CREATE_DATE,
ret.status STATUS,
to_char(ret.CREATE_DATE, 'yyyy-mm-dd') returnDate,
to_char(ret.SHIP_DATE, 'yyyy-mm-dd') SHIP_DATE,
to_char(ret.NOT_AFTER_DATE, 'yyyy-mm-dd') NOT_AFTER_DATE,
ret.TYPE TYPE,
ret.CREATE_USER CREATE_USER,
nvl((SELECT count(1) returnQuantity FROM RETURN_LINE_ITEM a WHERE a.RETURN_ID = ret.id), 0) returnQuantity,
nvl((select count(1) selectQuantity from RETURN_LINE_ITEM x WHERE x.RETURN_ID = ret.id AND x.QUANTITY != 0), 0) selectQuantity
FROM "RETURN" ret,
(SELECT to_char(b.ID_STR_RT) ID_STR_RT, to_char(b.NM_ORGN) NM_ORGN from BMP_WH_ST b
union all
SELECT to_char(p.ID_SPR) ID_STR_RT, to_char(p.NM_SPR) NM_ORGN from PA_SPR p) spr
WHERE ret.store_id = #{storeId, jdbcType = INTEGER}
AND ret.id = #{id, jdbcType = NUMERIC}
AND ret.SOURCE_ID = spr.ID_STR_RT
5.2. 验证商品是否存在
商品不存在！
select rli.*,
ai.de_itm itemIdName,
uc.UOM_DESC itemUom,
uc.uom luUom,
rcd.CODE_DESC reasonName,
decode(uc.uom_class,'MASS','1','0') uomDesc,
to_char(rli.quantity) quantityString,
il.SC_ITM scItm
from RETURN_LINE_ITEM rli,
"RETURN" r,
as_itm_rtl_str il,
uom_class uc,
RK_CODE_DETAIL rcd,
as_itm ai
where rli.return_id = r.id
and rli.item_id = il.id_itm
and r.store_id = il.id_str_rt
and r.source_id = il.id_spr
and uc.uom = trim(ai.lu_uom_dft)
and rcd.code_id = rli.reason_id
and 'RTVR' = rcd.code_type
and ai.id_itm = il.id_itm
and r.id = #{returnId,jdbcType = NUMERIC}
and rli.item_id in
(select t.id_itm
from as_itm t
where t.id_itm = #{itemId, jdbcType = VARCHAR}
and t.item_level = t.trans_level
union all
select t.id_prt_itm
from as_itm t
where t.id_itm = #{itemId, jdbcType = VARCHAR}
and t.item_level > t.trans_level)
and il.item_type = '0'
and r.type = 1
union all
select rli.*,
ai.de_itm itemIdName,
uc.UOM_DESC itemUom,
uc.uom luUom,
rcd.CODE_DESC reasonName,
decode(uc.uom_class,'MASS','1','0') uomDesc,
to_char(rli.quantity) quantityString,
il.SC_ITM scItm
from RETURN_LINE_ITEM rli,
"RETURN" r,
as_itm_rtl_str il,
uom_class uc,
RK_CODE_DETAIL rcd,
as_itm ai
where rli.return_id = r.id
and rli.item_id = il.id_itm
and r.store_id = il.id_str_rt
and uc.uom = trim(ai.lu_uom_dft)
and rcd.code_id = rli.reason_id
and 'RTWR' = rcd.code_type
and ai.id_itm = il.id_itm
and r.id = #{returnId,jdbcType = NUMERIC}
and rli.item_id in
(select t.id_itm from as_itm t where t.id_itm = #{itemId, jdbcType = VARCHAR}
and t.item_level = t.trans_level
union all
select t.id_prt_itm from as_itm t where t.id_itm = #{itemId, jdbcType = VARCHAR}
and t.item_level > t.trans_level )
and il.item_type = '0'
and r.type = 2
6. 商品信息查询
6.1. 商品基本信息
暂未查询到商品信息
SELECT
itm_str.ID_STR_RT,
bmp_st.NM_ORGN idStrRtName,
itm_str.SC_ITM_SLS,
decode((TO_NUMBER(SUBSTR(itm_str.RP_PR_SLS,instr(itm_str.RP_PR_SLS,'Y',1,1)+1,length(itm_str.RP_PR_SLS)))),0,'0.00',trim(to_char((TO_NUMBER(SUBSTR(itm_str.RP_PR_SLS,instr(itm_str.RP_PR_SLS,'Y',1,1)+1,length(itm_str.RP_PR_SLS)))),'99999999999999.99'))) RP_PR_SLS,
itm_str.ID_ITM,
itm_str.rk_long_desc,
itm_str.CD_VAT,
decode((TO_NUMBER(SUBSTR(itm_str.TY_PRC_RT,instr(itm_str.TY_PRC_RT,'Y',1,1)+1,length(itm_str.TY_PRC_RT)))),0,'0.0000',trim(to_char((TO_NUMBER(SUBSTR(itm_str.TY_PRC_RT,instr(itm_str.TY_PRC_RT,'Y',1,1)+1,length(itm_str.TY_PRC_RT)))),'99999999999999.9999'))) TY_PRC_RT,
decode(((TO_NUMBER(itm_str.CD_VAT)/100+1)*(TO_NUMBER(SUBSTR(itm_str.TY_PRC_RT,instr(itm_str.TY_PRC_RT,'Y',1,1)+1,length(itm_str.TY_PRC_RT))))),0,'0.00',trim(to_char(((TO_NUMBER(itm_str.CD_VAT)/100+1)*(TO_NUMBER(SUBSTR(itm_str.TY_PRC_RT,instr(itm_str.TY_PRC_RT,'Y',1,1)+1,length(itm_str.TY_PRC_RT))))),'99999999999999.99'))) hanShuiJinJia,
itm_str.ID_SPR,
pa_spr.NM_SPR idSprName,
itm_str.LU_UOM
from AS_ITM_RTL_STR itm_str,
BMP_WH_ST bmp_st,
PA_SPR pa_spr
where
itm_str.ID_STR_RT = bmp_st.ID_STR_RT
and itm_str.ID_SPR = pa_spr.ID_SPR
and itm_str.id_str_rt = #{idStrRt}
and itm_str.id_itm = #{idItm}
and itm_str.id_itm in (select im.id_itm
from as_itm im
where im.id_itm = #{idItm}
union all
select im.id_prt_itm
from as_itm im
where im.id_itm = #{idItm})
6.2. 商品属性查询
暂未查询到商品信息
select item_id itemId,
uda_id udaId,
uda_desc udaDesc,
value_desc valueDesc
from item_uda_view t
<where>
t.ITEM_ID = #{itemId}
and t.UDA_ID in (1,2,6,7,22,24)
</where>
7. 门店切换
7.1. 门店列表查询
查找门店失败
SELECT
ac_st.CACHED CACHED,
ac_st.STORE_ID STORE_ID,
bmp_st.NM_ORGN storeIdName,
ac_st.USER_ID USER_ID,
ac_st.ID ID
FROM
ac_user_store ac_st,
BMP_WH_ST bmp_st
WHERE
ac_st.USER_ID = #{userId,jdbcType=VARCHAR}
and ac_st.STORE_ID = bmp_st.ID_STR_RT