17,089
社区成员
发帖
与我相关
我的任务
分享
INSERT INTO T030_SHIPMENT_REQ_IMP_ERR
(T030_CREATE_YMD,
T030_UPDATE_YMD,
T030_TRAN_KBN,
T030_TANTO_CD,
T030_TERM_CD,
T030_WAREH_CD,
T030_OWNER_CD,
T030_GET_ID,
T030_SEND_NO,
T030_ERR_LINE,
T030_ERR_COL,
T030_ERR_CD,
T030_ERR_TXT,
T030_ERR_VAL,
T030_MEMO)
SELECT TO_CHAR(SYSDATE, 'yyyyMMddhh24miss'),
TO_CHAR(SYSDATE, 'yyyyMMddhh24miss'),
'1',
'System',
null,
AR.T501_WAREH_CD,
AR.T501_OWNER_CD,
'99999999999',
T501_SLIP_NO,
AR.num,
'',
'1',
'行番号に値がありません',
AR.T501_LINE_NO,
''
from (SELECT rownum as num,
T501_WAREH_CD,
T501_OWNER_CD,
T501_LINE_NO,
T501_SLIP_NO
FROM T501_AEON_ORDER
LEFT JOIN M029_DESTINATION ON T501_AEON_ORDER.T501_WAREH_CD =
M029_DESTINATION.M029_WAREH_CD
AND T501_AEON_ORDER.T501_OWNER_CD =
M029_DESTINATION.M029_OWNER_CD
AND RTRIM(T501_AEON_ORDER.T501_CUSTOM_CD2) =
RTRIM(M029_DESTINATION.M029_CUSTOM_CD)
AND RTRIM(T501_AEON_ORDER.T501_SHOP_CD) =
RTRIM(M029_DESTINATION.M029_SHIP_TO_CD)
AND M029_DESTINATION.M029_TRAN_KBN < '3'
WHERE T501_WAREH_CD = p_Wareh_CD
AND T501_OWNER_CD = p_owner_CD
AND T501_CUSTOM_CD2 = p_custom_cd2
AND T501_SHOP_CD = p_shop_cd
AND T501_DELI_PLAN_YMD = p_deli_plan_YMD
AND T501_ORDER_KBN = p_order_kbn
AND T501_RESULT_FLG = '2'
AND T501_TRAN_KBN < '3'
ORDER BY T501_CUSTOM_CD2,
T501_SHOP_CD,
T501_DELI_PLAN_YMD,
T501_ORDER_KBN,
T501_SLIP_NO,
T501_LINE_NO) AR
WHERE AR.T501_LINE_NO IS NULL;
SELECT
'System'
FROM T501_AEON_ORDER
LEFT JOIN M029_DESTINATION ON T501_AEON_ORDER.T501_WAREH_CD =
M029_DESTINATION.M029_WAREH_CD
AND T501_AEON_ORDER.T501_OWNER_CD =
M029_DESTINATION.M029_OWNER_CD
AND RTRIM(T501_AEON_ORDER.T501_CUSTOM_CD2) =
RTRIM(M029_DESTINATION.M029_CUSTOM_CD)
AND RTRIM(T501_AEON_ORDER.T501_SHOP_CD) =
RTRIM(M029_DESTINATION.M029_SHIP_TO_CD)
AND M029_DESTINATION.M029_TRAN_KBN < '3'
LEFT JOIN M028_CUSTOM ON T501_AEON_ORDER.T501_WAREH_CD =
M028_WAREH_CD
AND T501_AEON_ORDER.T501_OWNER_CD =
M028_OWNER_CD
AND M028_CUSTOM_CD =
RPAD(T501_AEON_ORDER.T501_CUSTOM_CD2,
12,
' ')
WHERE ROWNUM = 1
AND T501_WAREH_CD = p_wareh_cd
AND T501_OWNER_CD = p_owner_cd
AND T501_CUSTOM_CD2 = p_custom_cd2
AND T501_SHOP_CD = p_shop_cd
AND T501_DELI_PLAN_YMD = p_deli_plan_ymd
AND T501_ORDER_KBN = p_order_kbn
AND T501_RESULT_FLG = '2'
AND T501_TRAN_KBN < '3'
---------------------------------------
AND T501_AEON_ORDER.T501_SLIP_NO NOT IN
(SELECT T030_SHIPMENT_REQ_IMP_ERR.T030_Send_No
FROM T030_SHIPMENT_REQ_IMP_ERR
WHERE T030_SHIPMENT_REQ_IMP_ERR.T030_GET_ID = p_get_id)
AND T501_AEON_ORDER.T501_SLIP_NO IN
(SELECT column_value FROM TABLE(CAST(p_slip_NO_array AS T_AEON_SLIP_NO)))
---------------------------------------
ORDER BY T501_CUSTOM_CD2,
T501_SHOP_CD,
T501_DELI_PLAN_YMD,
T501_ORDER_KBN,
T501_SLIP_NO,
T501_LINE_NO;