데이터베이스 질문과 답변
안녕하세요. 전산실에서 일하고 있는 비전공 개발자 입니다.
다름이 아니라 회사에서 아래의 쿼리를 사용 하고 있습니다.
전체 실적에 대해서 아래의 쿼리를 사용 하기에 속도 개선을 위해서 JOIN 방식으로 수정 하고 싶습니다.
어떻게 해야 될지 감이 잡히지 않아서 문의 드립니다.
WITH TARGET AS
(
SELECT NULL MOVE_TRX_ID, NULL JOB_NO, NULL MOVE_TRX_TYPE, NULL MOVE_TRX_DATE, NULL FROM_OP_SEQ_NO, NULL FROM_OPERATION_ID, NULL FROM_OPERATION_CODE FROM DUAL UNION ALL
SELECT 1 ,'JOB-A0001' ,'RELEASE' ,TO_DATE(20200713112143, 'YYYYMMDDHH24MISS') ,NULL ,NULL ,NULL FROM DUAL UNION ALL
SELECT 2 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200720130833, 'YYYYMMDDHH24MISS') ,100 ,335 ,'L05010' FROM DUAL UNION ALL
SELECT 3 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200720193823, 'YYYYMMDDHH24MISS') ,100 ,335 ,'L05010' FROM DUAL UNION ALL
SELECT -4 ,'JOB-A0001' ,'SPLIT' ,TO_DATE(20200721015400, 'YYYYMMDDHH24MISS') ,100 ,335 ,'L05010' FROM DUAL UNION ALL
SELECT 5 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200721015434, 'YYYYMMDDHH24MISS') ,100 ,335 ,'L05010' FROM DUAL UNION ALL
SELECT 6 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200721015512, 'YYYYMMDDHH24MISS') ,100 ,335 ,'L05010' FROM DUAL UNION ALL
SELECT 7 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200721015512, 'YYYYMMDDHH24MISS') ,100 ,335 ,'L05010' FROM DUAL UNION ALL
SELECT 8 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200721024005, 'YYYYMMDDHH24MISS') ,300 ,479 ,'V02010' FROM DUAL UNION ALL
SELECT 9 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200721030127, 'YYYYMMDDHH24MISS') ,300 ,479 ,'V02010' FROM DUAL UNION ALL
SELECT 10 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200721034141, 'YYYYMMDDHH24MISS') ,300 ,479 ,'V02010' FROM DUAL UNION ALL
SELECT 11 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200721034146, 'YYYYMMDDHH24MISS') ,300 ,479 ,'V02010' FROM DUAL UNION ALL
SELECT 12 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200721034146, 'YYYYMMDDHH24MISS') ,300 ,479 ,'V02010' FROM DUAL UNION ALL
SELECT 13 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200721062811, 'YYYYMMDDHH24MISS') ,400 ,486 ,'V06010' FROM DUAL UNION ALL
SELECT 14 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200721062842, 'YYYYMMDDHH24MISS') ,400 ,486 ,'V06010' FROM DUAL UNION ALL
SELECT 15 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200721070209, 'YYYYMMDDHH24MISS') ,400 ,486 ,'V06010' FROM DUAL UNION ALL
SELECT 16 ,'JOB-A0001' ,'REJECT' ,TO_DATE(20200721070209, 'YYYYMMDDHH24MISS') ,400 ,486 ,'V06010' FROM DUAL UNION ALL
SELECT 17 ,'JOB-A0001' ,'SCRAP' ,TO_DATE(20200721070209, 'YYYYMMDDHH24MISS') ,400 ,486 ,'V06010' FROM DUAL UNION ALL
SELECT 18 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200721070217, 'YYYYMMDDHH24MISS') ,400 ,486 ,'V06010' FROM DUAL UNION ALL
SELECT 19 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200721070217, 'YYYYMMDDHH24MISS') ,400 ,486 ,'V06010' FROM DUAL UNION ALL
SELECT 20 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200721105256, 'YYYYMMDDHH24MISS') ,500 ,128 ,'E04010' FROM DUAL UNION ALL
SELECT 21 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200721105516, 'YYYYMMDDHH24MISS') ,500 ,128 ,'E04010' FROM DUAL UNION ALL
SELECT 22 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200721105754, 'YYYYMMDDHH24MISS') ,500 ,128 ,'E04010' FROM DUAL UNION ALL
SELECT 23 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200721105917, 'YYYYMMDDHH24MISS') ,500 ,128 ,'E04010' FROM DUAL UNION ALL
SELECT 24 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200721105917, 'YYYYMMDDHH24MISS') ,500 ,128 ,'E04010' FROM DUAL UNION ALL
SELECT 25 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200721110000, 'YYYYMMDDHH24MISS') ,600 ,152 ,'E08020' FROM DUAL UNION ALL
SELECT 26 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200721135457, 'YYYYMMDDHH24MISS') ,600 ,152 ,'E08020' FROM DUAL UNION ALL
SELECT 27 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200721135502, 'YYYYMMDDHH24MISS') ,600 ,152 ,'E08020' FROM DUAL UNION ALL
SELECT 28 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200721135521, 'YYYYMMDDHH24MISS') ,600 ,152 ,'E08020' FROM DUAL UNION ALL
SELECT 29 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200721135521, 'YYYYMMDDHH24MISS') ,600 ,152 ,'E08020' FROM DUAL UNION ALL
SELECT 30 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200721135527, 'YYYYMMDDHH24MISS') ,700 ,151 ,'E08010' FROM DUAL UNION ALL
SELECT 31 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200721135608, 'YYYYMMDDHH24MISS') ,700 ,151 ,'E08010' FROM DUAL UNION ALL
SELECT 32 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200721135615, 'YYYYMMDDHH24MISS') ,700 ,151 ,'E08010' FROM DUAL UNION ALL
SELECT 33 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200721135639, 'YYYYMMDDHH24MISS') ,700 ,151 ,'E08010' FROM DUAL UNION ALL
SELECT 34 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200721135639, 'YYYYMMDDHH24MISS') ,700 ,151 ,'E08010' FROM DUAL UNION ALL
SELECT 35 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200721161306, 'YYYYMMDDHH24MISS') ,800 ,122 ,'E01020' FROM DUAL UNION ALL
SELECT 36 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200721163706, 'YYYYMMDDHH24MISS') ,800 ,122 ,'E01020' FROM DUAL UNION ALL
SELECT 37 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200721175016, 'YYYYMMDDHH24MISS') ,800 ,122 ,'E01020' FROM DUAL UNION ALL
SELECT 38 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200721175041, 'YYYYMMDDHH24MISS') ,800 ,122 ,'E01020' FROM DUAL UNION ALL
SELECT 39 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200721175041, 'YYYYMMDDHH24MISS') ,800 ,122 ,'E01020' FROM DUAL UNION ALL
SELECT 40 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200721175050, 'YYYYMMDDHH24MISS') ,900 ,145 ,'E06110' FROM DUAL UNION ALL
SELECT 41 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200721175112, 'YYYYMMDDHH24MISS') ,900 ,145 ,'E06110' FROM DUAL UNION ALL
SELECT 42 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200721190506, 'YYYYMMDDHH24MISS') ,900 ,145 ,'E06110' FROM DUAL UNION ALL
SELECT 43 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200721193240, 'YYYYMMDDHH24MISS') ,900 ,145 ,'E06110' FROM DUAL UNION ALL
SELECT 44 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200721193240, 'YYYYMMDDHH24MISS') ,900 ,145 ,'E06110' FROM DUAL UNION ALL
SELECT 45 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200721202028, 'YYYYMMDDHH24MISS') ,1000 ,165 ,'E11080' FROM DUAL UNION ALL
SELECT 46 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200722100755, 'YYYYMMDDHH24MISS') ,1000 ,165 ,'E11080' FROM DUAL UNION ALL
SELECT 47 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200722104654, 'YYYYMMDDHH24MISS') ,1000 ,165 ,'E11080' FROM DUAL UNION ALL
SELECT 48 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200722141543, 'YYYYMMDDHH24MISS') ,1000 ,165 ,'E11080' FROM DUAL UNION ALL
SELECT 49 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200722141543, 'YYYYMMDDHH24MISS') ,1000 ,165 ,'E11080' FROM DUAL UNION ALL
SELECT 50 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200722143114, 'YYYYMMDDHH24MISS') ,1100 ,126 ,'E03010' FROM DUAL UNION ALL
SELECT 51 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200723023647, 'YYYYMMDDHH24MISS') ,1100 ,126 ,'E03010' FROM DUAL UNION ALL
SELECT 52 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200723023718, 'YYYYMMDDHH24MISS') ,1100 ,126 ,'E03010' FROM DUAL UNION ALL
SELECT 53 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200723023730, 'YYYYMMDDHH24MISS') ,1100 ,126 ,'E03010' FROM DUAL UNION ALL
SELECT 54 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200723023730, 'YYYYMMDDHH24MISS') ,1100 ,126 ,'E03010' FROM DUAL UNION ALL
SELECT 55 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200723092603, 'YYYYMMDDHH24MISS') ,1200 ,475 ,'T01010' FROM DUAL UNION ALL
SELECT 56 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200723092647, 'YYYYMMDDHH24MISS') ,1200 ,475 ,'T01010' FROM DUAL UNION ALL
SELECT 57 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200723092734, 'YYYYMMDDHH24MISS') ,1200 ,475 ,'T01010' FROM DUAL UNION ALL
SELECT 58 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200723112855, 'YYYYMMDDHH24MISS') ,1200 ,475 ,'T01010' FROM DUAL UNION ALL
SELECT 59 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200723112855, 'YYYYMMDDHH24MISS') ,1200 ,475 ,'T01010' FROM DUAL UNION ALL
SELECT 60 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200723121639, 'YYYYMMDDHH24MISS') ,1300 ,131 ,'E05030' FROM DUAL UNION ALL
SELECT 61 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200724190344, 'YYYYMMDDHH24MISS') ,1300 ,131 ,'E05030' FROM DUAL UNION ALL
SELECT 62 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200724193350, 'YYYYMMDDHH24MISS') ,1300 ,131 ,'E05030' FROM DUAL UNION ALL
SELECT 63 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200724193354, 'YYYYMMDDHH24MISS') ,1300 ,131 ,'E05030' FROM DUAL UNION ALL
SELECT 64 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200724193354, 'YYYYMMDDHH24MISS') ,1300 ,131 ,'E05030' FROM DUAL UNION ALL
SELECT 65 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200724202549, 'YYYYMMDDHH24MISS') ,1350 ,600000010,'E05035' FROM DUAL UNION ALL
SELECT 66 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200724202557, 'YYYYMMDDHH24MISS') ,1350 ,600000010,'E05035' FROM DUAL UNION ALL
SELECT 67 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200724224309, 'YYYYMMDDHH24MISS') ,1350 ,600000010,'E05035' FROM DUAL UNION ALL
SELECT 68 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200724224330, 'YYYYMMDDHH24MISS') ,1350 ,600000010,'E05035' FROM DUAL UNION ALL
SELECT 69 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200724224330, 'YYYYMMDDHH24MISS') ,1350 ,600000010,'E05035' FROM DUAL UNION ALL
SELECT 70 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200725014749, 'YYYYMMDDHH24MISS') ,1400 ,263 ,'H13010' FROM DUAL UNION ALL
SELECT 71 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200725014754, 'YYYYMMDDHH24MISS') ,1400 ,263 ,'H13010' FROM DUAL UNION ALL
SELECT 72 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200725014816, 'YYYYMMDDHH24MISS') ,1400 ,263 ,'H13010' FROM DUAL UNION ALL
SELECT 73 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200725014819, 'YYYYMMDDHH24MISS') ,1400 ,263 ,'H13010' FROM DUAL UNION ALL
SELECT 74 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200725014819, 'YYYYMMDDHH24MISS') ,1400 ,263 ,'H13010' FROM DUAL UNION ALL
SELECT 75 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200725085734, 'YYYYMMDDHH24MISS') ,1500 ,252 ,'H08070' FROM DUAL UNION ALL
SELECT 76 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200725085742, 'YYYYMMDDHH24MISS') ,1500 ,252 ,'H08070' FROM DUAL UNION ALL
SELECT 77 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200725085749, 'YYYYMMDDHH24MISS') ,1500 ,252 ,'H08070' FROM DUAL UNION ALL
SELECT 78 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200725085752, 'YYYYMMDDHH24MISS') ,1500 ,252 ,'H08070' FROM DUAL UNION ALL
SELECT 79 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200725085752, 'YYYYMMDDHH24MISS') ,1500 ,252 ,'H08070' FROM DUAL UNION ALL
SELECT 80 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200725085754, 'YYYYMMDDHH24MISS') ,1600 ,251 ,'H08060' FROM DUAL UNION ALL
SELECT 81 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200725085801, 'YYYYMMDDHH24MISS') ,1600 ,251 ,'H08060' FROM DUAL UNION ALL
SELECT 82 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200725085819, 'YYYYMMDDHH24MISS') ,1600 ,251 ,'H08060' FROM DUAL UNION ALL
SELECT 83 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200725085821, 'YYYYMMDDHH24MISS') ,1600 ,251 ,'H08060' FROM DUAL UNION ALL
SELECT 84 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200725085821, 'YYYYMMDDHH24MISS') ,1600 ,251 ,'H08060' FROM DUAL UNION ALL
SELECT 85 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200725085823, 'YYYYMMDDHH24MISS') ,1700 ,377 ,'M03040' FROM DUAL UNION ALL
SELECT 86 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200725085827, 'YYYYMMDDHH24MISS') ,1700 ,377 ,'M03040' FROM DUAL UNION ALL
SELECT 87 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200725112643, 'YYYYMMDDHH24MISS') ,1700 ,377 ,'M03040' FROM DUAL UNION ALL
SELECT 88 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200725112647, 'YYYYMMDDHH24MISS') ,1700 ,377 ,'M03040' FROM DUAL UNION ALL
SELECT 89 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200725112647, 'YYYYMMDDHH24MISS') ,1700 ,377 ,'M03040' FROM DUAL UNION ALL
SELECT 90 ,'JOB-A0001' ,'RESOURCE_CHANGE' ,TO_DATE(20200725135816, 'YYYYMMDDHH24MISS') ,1800 ,190 ,'H01030' FROM DUAL UNION ALL
SELECT 91 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200725180729, 'YYYYMMDDHH24MISS') ,1800 ,190 ,'H01030' FROM DUAL UNION ALL
SELECT 92 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200725180729, 'YYYYMMDDHH24MISS') ,1800 ,190 ,'H01030' FROM DUAL UNION ALL
SELECT 93 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200726050239, 'YYYYMMDDHH24MISS') ,1800 ,190 ,'H01030' FROM DUAL UNION ALL
SELECT 94 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200726075119, 'YYYYMMDDHH24MISS') ,1800 ,190 ,'H01030' FROM DUAL UNION ALL
SELECT 95 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200726075119, 'YYYYMMDDHH24MISS') ,1800 ,190 ,'H01030' FROM DUAL UNION ALL
SELECT 96 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200726083705, 'YYYYMMDDHH24MISS') ,1900 ,185 ,'G08010' FROM DUAL UNION ALL
SELECT 97 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200726202502, 'YYYYMMDDHH24MISS') ,1900 ,185 ,'G08010' FROM DUAL UNION ALL
SELECT 98 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200726204212, 'YYYYMMDDHH24MISS') ,1900 ,185 ,'G08010' FROM DUAL UNION ALL
SELECT 99 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200726204226, 'YYYYMMDDHH24MISS') ,1900 ,185 ,'G08010' FROM DUAL UNION ALL
SELECT 100 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200726204226, 'YYYYMMDDHH24MISS') ,1900 ,185 ,'G08010' FROM DUAL UNION ALL
SELECT 101 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200726234822, 'YYYYMMDDHH24MISS') ,2000 ,176 ,'G04040' FROM DUAL UNION ALL
SELECT 102 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200726234835, 'YYYYMMDDHH24MISS') ,2000 ,176 ,'G04040' FROM DUAL UNION ALL
SELECT 103 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200727005318, 'YYYYMMDDHH24MISS') ,2000 ,176 ,'G04040' FROM DUAL UNION ALL
SELECT 104 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200727005324, 'YYYYMMDDHH24MISS') ,2000 ,176 ,'G04040' FROM DUAL UNION ALL
SELECT 105 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200727005324, 'YYYYMMDDHH24MISS') ,2000 ,176 ,'G04040' FROM DUAL UNION ALL
SELECT 106 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200727005328, 'YYYYMMDDHH24MISS') ,2100 ,133 ,'E05050' FROM DUAL UNION ALL
SELECT 107 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200727005334, 'YYYYMMDDHH24MISS') ,2100 ,133 ,'E05050' FROM DUAL UNION ALL
SELECT 108 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200727035943, 'YYYYMMDDHH24MISS') ,2100 ,133 ,'E05050' FROM DUAL UNION ALL
SELECT 109 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200727035946, 'YYYYMMDDHH24MISS') ,2100 ,133 ,'E05050' FROM DUAL UNION ALL
SELECT 110 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200727035946, 'YYYYMMDDHH24MISS') ,2100 ,133 ,'E05050' FROM DUAL UNION ALL
SELECT 111 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200727042030, 'YYYYMMDDHH24MISS') ,2200 ,384 ,'M04050' FROM DUAL UNION ALL
SELECT 112 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200727042047, 'YYYYMMDDHH24MISS') ,2200 ,384 ,'M04050' FROM DUAL UNION ALL
SELECT 113 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200727051513, 'YYYYMMDDHH24MISS') ,2200 ,384 ,'M04050' FROM DUAL UNION ALL
SELECT 114 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200727051609, 'YYYYMMDDHH24MISS') ,2200 ,384 ,'M04050' FROM DUAL UNION ALL
SELECT 115 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200727051609, 'YYYYMMDDHH24MISS') ,2200 ,384 ,'M04050' FROM DUAL UNION ALL
SELECT 116 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200727052527, 'YYYYMMDDHH24MISS') ,2300 ,387 ,'M05030' FROM DUAL UNION ALL
SELECT 117 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200727130900, 'YYYYMMDDHH24MISS') ,2300 ,387 ,'M05030' FROM DUAL UNION ALL
SELECT 118 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200727141425, 'YYYYMMDDHH24MISS') ,2300 ,387 ,'M05030' FROM DUAL UNION ALL
SELECT 119 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200727141430, 'YYYYMMDDHH24MISS') ,2300 ,387 ,'M05030' FROM DUAL UNION ALL
SELECT 120 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200727141430, 'YYYYMMDDHH24MISS') ,2300 ,387 ,'M05030' FROM DUAL UNION ALL
SELECT 121 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200727145640, 'YYYYMMDDHH24MISS') ,2400 ,382 ,'M04030' FROM DUAL UNION ALL
SELECT 122 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200727151104, 'YYYYMMDDHH24MISS') ,2400 ,382 ,'M04030' FROM DUAL UNION ALL
SELECT 123 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200727175426, 'YYYYMMDDHH24MISS') ,2400 ,382 ,'M04030' FROM DUAL UNION ALL
SELECT 124 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200727175522, 'YYYYMMDDHH24MISS') ,2400 ,382 ,'M04030' FROM DUAL UNION ALL
SELECT 125 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200727175522, 'YYYYMMDDHH24MISS') ,2400 ,382 ,'M04030' FROM DUAL UNION ALL
SELECT 126 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200727183156, 'YYYYMMDDHH24MISS') ,2500 ,553 ,'Z09120' FROM DUAL UNION ALL
SELECT 127 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200727183229, 'YYYYMMDDHH24MISS') ,2500 ,553 ,'Z09120' FROM DUAL UNION ALL
SELECT 128 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200728031723, 'YYYYMMDDHH24MISS') ,2500 ,553 ,'Z09120' FROM DUAL UNION ALL
SELECT 129 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200728031753, 'YYYYMMDDHH24MISS') ,2500 ,553 ,'Z09120' FROM DUAL UNION ALL
SELECT 130 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200728031753, 'YYYYMMDDHH24MISS') ,2500 ,553 ,'Z09120' FROM DUAL UNION ALL
SELECT 131 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200728052458, 'YYYYMMDDHH24MISS') ,2600 ,277 ,'I04020' FROM DUAL UNION ALL
SELECT 132 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200728052458, 'YYYYMMDDHH24MISS') ,2600 ,277 ,'I04020' FROM DUAL UNION ALL
SELECT 133 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200728064723, 'YYYYMMDDHH24MISS') ,2600 ,277 ,'I04020' FROM DUAL UNION ALL
SELECT 134 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200728064723, 'YYYYMMDDHH24MISS') ,2600 ,277 ,'I04020' FROM DUAL UNION ALL
SELECT 135 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200728064723, 'YYYYMMDDHH24MISS') ,2600 ,277 ,'I04020' FROM DUAL UNION ALL
SELECT 136 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200728064726, 'YYYYMMDDHH24MISS') ,2700 ,276 ,'I04010' FROM DUAL UNION ALL
SELECT 137 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200728064726, 'YYYYMMDDHH24MISS') ,2700 ,276 ,'I04010' FROM DUAL UNION ALL
SELECT 138 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200728072557, 'YYYYMMDDHH24MISS') ,2700 ,276 ,'I04010' FROM DUAL UNION ALL
SELECT 139 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200728072557, 'YYYYMMDDHH24MISS') ,2700 ,276 ,'I04010' FROM DUAL UNION ALL
SELECT 140 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200728072557, 'YYYYMMDDHH24MISS') ,2700 ,276 ,'I04010' FROM DUAL UNION ALL
SELECT 141 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200728072602, 'YYYYMMDDHH24MISS') ,2800 ,277 ,'I04020' FROM DUAL UNION ALL
SELECT 142 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200728072602, 'YYYYMMDDHH24MISS') ,2800 ,277 ,'I04020' FROM DUAL UNION ALL
SELECT 143 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200728102146, 'YYYYMMDDHH24MISS') ,2800 ,277 ,'I04020' FROM DUAL UNION ALL
SELECT 144 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200728102146, 'YYYYMMDDHH24MISS') ,2800 ,277 ,'I04020' FROM DUAL UNION ALL
SELECT 145 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200728102146, 'YYYYMMDDHH24MISS') ,2800 ,277 ,'I04020' FROM DUAL UNION ALL
SELECT 146 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200728102147, 'YYYYMMDDHH24MISS') ,2900 ,276 ,'I04010' FROM DUAL UNION ALL
SELECT 147 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200728102147, 'YYYYMMDDHH24MISS') ,2900 ,276 ,'I04010' FROM DUAL UNION ALL
SELECT 148 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200728122814, 'YYYYMMDDHH24MISS') ,2900 ,276 ,'I04010' FROM DUAL UNION ALL
SELECT 149 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200728122814, 'YYYYMMDDHH24MISS') ,2900 ,276 ,'I04010' FROM DUAL UNION ALL
SELECT 150 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200728122814, 'YYYYMMDDHH24MISS') ,2900 ,276 ,'I04010' FROM DUAL UNION ALL
SELECT 151 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200728131411, 'YYYYMMDDHH24MISS') ,3000 ,291 ,'K01010' FROM DUAL UNION ALL
SELECT 152 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200728133639, 'YYYYMMDDHH24MISS') ,3000 ,291 ,'K01010' FROM DUAL UNION ALL
SELECT 153 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200728142848, 'YYYYMMDDHH24MISS') ,3000 ,291 ,'K01010' FROM DUAL UNION ALL
SELECT 154 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200728142853, 'YYYYMMDDHH24MISS') ,3000 ,291 ,'K01010' FROM DUAL UNION ALL
SELECT 155 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200728142853, 'YYYYMMDDHH24MISS') ,3000 ,291 ,'K01010' FROM DUAL UNION ALL
SELECT 156 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200728143818, 'YYYYMMDDHH24MISS') ,3100 ,400 ,'M06120' FROM DUAL UNION ALL
SELECT 157 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200728143831, 'YYYYMMDDHH24MISS') ,3100 ,400 ,'M06120' FROM DUAL UNION ALL
SELECT 158 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200728155829, 'YYYYMMDDHH24MISS') ,3100 ,400 ,'M06120' FROM DUAL UNION ALL
SELECT 159 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200728155858, 'YYYYMMDDHH24MISS') ,3100 ,400 ,'M06120' FROM DUAL UNION ALL
SELECT 160 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200728155858, 'YYYYMMDDHH24MISS') ,3100 ,400 ,'M06120' FROM DUAL UNION ALL
SELECT 161 ,'JOB-A0001' ,'RESOURCE_CHANGE' ,TO_DATE(20200728163223, 'YYYYMMDDHH24MISS') ,3200 ,114 ,'B01020' FROM DUAL UNION ALL
SELECT 162 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200728163239, 'YYYYMMDDHH24MISS') ,3200 ,114 ,'B01020' FROM DUAL UNION ALL
SELECT 163 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200728163320, 'YYYYMMDDHH24MISS') ,3200 ,114 ,'B01020' FROM DUAL UNION ALL
SELECT 164 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200728181852, 'YYYYMMDDHH24MISS') ,3200 ,114 ,'B01020' FROM DUAL UNION ALL
SELECT 165 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200728181907, 'YYYYMMDDHH24MISS') ,3200 ,114 ,'B01020' FROM DUAL UNION ALL
SELECT 166 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200728181907, 'YYYYMMDDHH24MISS') ,3200 ,114 ,'B01020' FROM DUAL UNION ALL
SELECT 167 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200728184912, 'YYYYMMDDHH24MISS') ,3250 ,442 ,'Q06010' FROM DUAL UNION ALL
SELECT 168 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200728184912, 'YYYYMMDDHH24MISS') ,3250 ,442 ,'Q06010' FROM DUAL UNION ALL
SELECT 169 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200728184917, 'YYYYMMDDHH24MISS') ,3250 ,442 ,'Q06010' FROM DUAL UNION ALL
SELECT 170 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200728184917, 'YYYYMMDDHH24MISS') ,3250 ,442 ,'Q06010' FROM DUAL UNION ALL
SELECT 171 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200728184917, 'YYYYMMDDHH24MISS') ,3250 ,442 ,'Q06010' FROM DUAL UNION ALL
SELECT 172 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200728201344, 'YYYYMMDDHH24MISS') ,3300 ,302 ,'K06010' FROM DUAL UNION ALL
SELECT 173 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200728201344, 'YYYYMMDDHH24MISS') ,3300 ,302 ,'K06010' FROM DUAL UNION ALL
SELECT 174 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200729043711, 'YYYYMMDDHH24MISS') ,3300 ,302 ,'K06010' FROM DUAL UNION ALL
SELECT 175 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200729043711, 'YYYYMMDDHH24MISS') ,3300 ,302 ,'K06010' FROM DUAL UNION ALL
SELECT 176 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200729043711, 'YYYYMMDDHH24MISS') ,3300 ,302 ,'K06010' FROM DUAL UNION ALL
SELECT 177 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200729054116, 'YYYYMMDDHH24MISS') ,3400 ,308 ,'K09010' FROM DUAL UNION ALL
SELECT 178 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200729054116, 'YYYYMMDDHH24MISS') ,3400 ,308 ,'K09010' FROM DUAL UNION ALL
SELECT 179 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200729054156, 'YYYYMMDDHH24MISS') ,3400 ,308 ,'K09010' FROM DUAL UNION ALL
SELECT 180 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200729083803, 'YYYYMMDDHH24MISS') ,3400 ,308 ,'K09010' FROM DUAL UNION ALL
SELECT 181 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200729083803, 'YYYYMMDDHH24MISS') ,3400 ,308 ,'K09010' FROM DUAL UNION ALL
SELECT 182 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200729084206, 'YYYYMMDDHH24MISS') ,3500 ,372 ,'M01020' FROM DUAL UNION ALL
SELECT 183 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200729084246, 'YYYYMMDDHH24MISS') ,3500 ,372 ,'M01020' FROM DUAL UNION ALL
SELECT 184 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200729095841, 'YYYYMMDDHH24MISS') ,3500 ,372 ,'M01020' FROM DUAL UNION ALL
SELECT 185 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200729095845, 'YYYYMMDDHH24MISS') ,3500 ,372 ,'M01020' FROM DUAL UNION ALL
SELECT 186 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200729095845, 'YYYYMMDDHH24MISS') ,3500 ,372 ,'M01020' FROM DUAL UNION ALL
SELECT 187 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200729095849, 'YYYYMMDDHH24MISS') ,3600 ,310 ,'K09030' FROM DUAL UNION ALL
SELECT 188 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200729095849, 'YYYYMMDDHH24MISS') ,3600 ,310 ,'K09030' FROM DUAL UNION ALL
SELECT 189 ,'JOB-A0001' ,'CANCEL_RUN_START' ,TO_DATE(20200729103406, 'YYYYMMDDHH24MISS') ,3600 ,310 ,'K09030' FROM DUAL UNION ALL
SELECT 190 ,'JOB-A0001' ,'CANCEL_RECEIPT' ,TO_DATE(20200729103406, 'YYYYMMDDHH24MISS') ,3600 ,310 ,'K09030' FROM DUAL UNION ALL
SELECT 191 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200729190930, 'YYYYMMDDHH24MISS') ,3600 ,310 ,'K09030' FROM DUAL UNION ALL
SELECT 192 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200729190930, 'YYYYMMDDHH24MISS') ,3600 ,310 ,'K09030' FROM DUAL UNION ALL
SELECT 193 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200729190936, 'YYYYMMDDHH24MISS') ,3600 ,310 ,'K09030' FROM DUAL UNION ALL
SELECT 194 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200730025636, 'YYYYMMDDHH24MISS') ,3600 ,310 ,'K09030' FROM DUAL UNION ALL
SELECT 195 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200730025636, 'YYYYMMDDHH24MISS') ,3600 ,310 ,'K09030' FROM DUAL UNION ALL
SELECT 196 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200730033714, 'YYYYMMDDHH24MISS') ,3650 ,392 ,'M06040' FROM DUAL UNION ALL
SELECT 197 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200730033720, 'YYYYMMDDHH24MISS') ,3650 ,392 ,'M06040' FROM DUAL UNION ALL
SELECT 198 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200730033729, 'YYYYMMDDHH24MISS') ,3650 ,392 ,'M06040' FROM DUAL UNION ALL
SELECT 199 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200730033737, 'YYYYMMDDHH24MISS') ,3650 ,392 ,'M06040' FROM DUAL UNION ALL
SELECT 200 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200730033737, 'YYYYMMDDHH24MISS') ,3650 ,392 ,'M06040' FROM DUAL UNION ALL
SELECT 201 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200730035538, 'YYYYMMDDHH24MISS') ,3700 ,437 ,'Q03010' FROM DUAL UNION ALL
SELECT 202 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200730035538, 'YYYYMMDDHH24MISS') ,3700 ,437 ,'Q03010' FROM DUAL UNION ALL
SELECT 203 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200730044557, 'YYYYMMDDHH24MISS') ,3700 ,437 ,'Q03010' FROM DUAL UNION ALL
SELECT 204 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200730044557, 'YYYYMMDDHH24MISS') ,3700 ,437 ,'Q03010' FROM DUAL UNION ALL
SELECT 205 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200730044557, 'YYYYMMDDHH24MISS') ,3700 ,437 ,'Q03010' FROM DUAL UNION ALL
SELECT 206 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200730045914, 'YYYYMMDDHH24MISS') ,3800 ,449 ,'Q08010' FROM DUAL UNION ALL
SELECT 207 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200730045946, 'YYYYMMDDHH24MISS') ,3800 ,449 ,'Q08010' FROM DUAL UNION ALL
SELECT 208 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200730064740, 'YYYYMMDDHH24MISS') ,3800 ,449 ,'Q08010' FROM DUAL UNION ALL
SELECT 209 ,'JOB-A0001' ,'REJECT' ,TO_DATE(20200730064740, 'YYYYMMDDHH24MISS') ,3800 ,449 ,'Q08010' FROM DUAL UNION ALL
SELECT 210 ,'JOB-A0001' ,'SCRAP' ,TO_DATE(20200730064740, 'YYYYMMDDHH24MISS') ,3800 ,449 ,'Q08010' FROM DUAL UNION ALL
SELECT 211 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200730064745, 'YYYYMMDDHH24MISS') ,3800 ,449 ,'Q08010' FROM DUAL UNION ALL
SELECT 212 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200730064745, 'YYYYMMDDHH24MISS') ,3800 ,449 ,'Q08010' FROM DUAL UNION ALL
SELECT 213 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200730070227, 'YYYYMMDDHH24MISS') ,4000 ,472 ,'S03030' FROM DUAL UNION ALL
SELECT 214 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200730070227, 'YYYYMMDDHH24MISS') ,4000 ,472 ,'S03030' FROM DUAL UNION ALL
SELECT 215 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200730070508, 'YYYYMMDDHH24MISS') ,4000 ,472 ,'S03030' FROM DUAL UNION ALL
SELECT 216 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200730070508, 'YYYYMMDDHH24MISS') ,4000 ,472 ,'S03030' FROM DUAL UNION ALL
SELECT 217 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200730070508, 'YYYYMMDDHH24MISS') ,4000 ,472 ,'S03030' FROM DUAL UNION ALL
SELECT 218 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200730083823, 'YYYYMMDDHH24MISS') ,4050 ,100000182,'Z09075' FROM DUAL UNION ALL
SELECT 219 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200730083823, 'YYYYMMDDHH24MISS') ,4050 ,100000182,'Z09075' FROM DUAL UNION ALL
SELECT 220 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200730083835, 'YYYYMMDDHH24MISS') ,4050 ,100000182,'Z09075' FROM DUAL UNION ALL
SELECT 221 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200730083835, 'YYYYMMDDHH24MISS') ,4050 ,100000182,'Z09075' FROM DUAL UNION ALL
SELECT 222 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200730083835, 'YYYYMMDDHH24MISS') ,4050 ,100000182,'Z09075' FROM DUAL UNION ALL
SELECT 223 ,'JOB-A0001' ,'RECEIPT' ,TO_DATE(20200730105551, 'YYYYMMDDHH24MISS') ,4100 ,473 ,'S03040' FROM DUAL UNION ALL
SELECT 224 ,'JOB-A0001' ,'RUN_START' ,TO_DATE(20200730105551, 'YYYYMMDDHH24MISS') ,4100 ,473 ,'S03040' FROM DUAL UNION ALL
SELECT 225 ,'JOB-A0001' ,'RUN_END' ,TO_DATE(20200730112436, 'YYYYMMDDHH24MISS') ,4100 ,473 ,'S03040' FROM DUAL UNION ALL
SELECT 226 ,'JOB-A0001' ,'TOMOVE' ,TO_DATE(20200730112436, 'YYYYMMDDHH24MISS') ,4100 ,473 ,'S03040' FROM DUAL UNION ALL
SELECT 227 ,'JOB-A0001' ,'WIP_COMPLETE' ,TO_DATE(20200730112436, 'YYYYMMDDHH24MISS') ,4100 ,473 ,'S03040' FROM DUAL
)
, OP_DESC AS
(
SELECT FROM_OPERATION_ID AS OPERATION_ID, FROM_OPERATION_CODE AS OPERATION_CODE, FROM_OPERATION_CODE || '_DESC' AS OPERATION_DESCRIPTION
FROM ( SELECT DISTINCT FROM_OPERATION_ID, FROM_OPERATION_CODE
FROM TARGET
WHERE FROM_OPERATION_ID IS NOT NULL )
ORDER BY FROM_OPERATION_ID
)
SELECT MOVE_TRX_ID
, JOB_NO
, MOVE_TRX_TYPE
--, MOVE_TRX_DATE
, MOVE_TRX_DATE
, FROM_OP_SEQ_NO
, FROM_OPERATION_ID
, FROM_OPERATION_CODE
, FROM_OPERATION_DESCRIPTION
, ACT_RECEIPT_DATE2 -- 다음공정 최초인수일시
, ACT_RECEIPT_DATE3 -- 다음공정 최종인수일시
, FROM_OP_SEQ_NO2 -- 다음 공정 순번
, OPERATION_CODE2 -- 다음 공정 코드
, OPERATION_DESCRIPTION2 -- 다음 공정명
FROM(
SELECT MOVE_TRX_ID
, JOB_NO
, MOVE_TRX_TYPE
, MOVE_TRX_DATE
, FROM_OP_SEQ_NO
, FROM_OPERATION_ID
, B.OPERATION_CODE AS FROM_OPERATION_CODE
, B.OPERATION_DESCRIPTION AS FROM_OPERATION_DESCRIPTION
, OPERATION_CODE
, OPERATION_DESCRIPTION
, TO_CHAR((SELECT MOVE_TRX_DATE
FROM (SELECT MOVE_TRX_DATE, MOVE_TRX_ID, JOB_NO
FROM TARGET
WHERE MOVE_TRX_TYPE = 'RECEIPT'
ORDER BY MOVE_TRX_ID )
WHERE JOB_NO = WMT.JOB_NO
AND MOVE_TRX_ID > WMT.MOVE_TRX_ID
AND ROWNUM = 1), 'YYYY-MM-DD hh24:mi') AS ACT_RECEIPT_DATE2 -- 다음공정 최초인수일시
, TO_CHAR((SELECT MOVE_TRX_DATE
FROM (SELECT MOVE_TRX_DATE, MOVE_TRX_ID, JOB_NO, FROM_OP_SEQ_NO
FROM TARGET
WHERE MOVE_TRX_TYPE = 'RECEIPT'
ORDER BY MOVE_TRX_ID DESC)
WHERE JOB_NO = WMT.JOB_NO
AND MOVE_TRX_ID > WMT.MOVE_TRX_ID
AND FROM_OP_SEQ_NO = (SELECT FROM_OP_SEQ_NO
FROM (SELECT MOVE_TRX_DATE, MOVE_TRX_ID, JOB_NO, FROM_OP_SEQ_NO
FROM TARGET
WHERE MOVE_TRX_TYPE = 'RECEIPT'
ORDER BY MOVE_TRX_ID )
WHERE JOB_NO = WMT.JOB_NO
AND MOVE_TRX_ID > WMT.MOVE_TRX_ID
AND ROWNUM = 1)
AND ROWNUM = 1), 'YYYY-MM-DD hh24:mi') AS ACT_RECEIPT_DATE3 -- 다음공정 최종인수일시
, (SELECT FROM_OP_SEQ_NO
FROM (SELECT MOVE_TRX_DATE, MOVE_TRX_ID, JOB_NO, FROM_OP_SEQ_NO
FROM TARGET
WHERE MOVE_TRX_TYPE = 'RECEIPT'
ORDER BY MOVE_TRX_ID )
WHERE JOB_NO = WMT.JOB_NO
AND MOVE_TRX_ID > WMT.MOVE_TRX_ID
AND ROWNUM = 1) AS FROM_OP_SEQ_NO2 -- 다음 공정 순번
, (SELECT OPERATION_CODE
FROM (SELECT A.MOVE_TRX_DATE, A.MOVE_TRX_ID, A.JOB_NO, A.FROM_OP_SEQ_NO, B.OPERATION_CODE
FROM TARGET A LEFT OUTER JOIN OP_DESC B ON A.FROM_OPERATION_ID = B.OPERATION_ID
WHERE A.MOVE_TRX_TYPE = 'RECEIPT'
ORDER BY A.MOVE_TRX_ID )
WHERE JOB_NO = WMT.JOB_NO
AND MOVE_TRX_ID > WMT.MOVE_TRX_ID
AND ROWNUM = 1) AS OPERATION_CODE2 -- 다음 공정 코드
, (SELECT OPERATION_DESCRIPTION
FROM (SELECT A.MOVE_TRX_DATE, A.MOVE_TRX_ID, A.JOB_NO, A.FROM_OP_SEQ_NO, B.OPERATION_DESCRIPTION
FROM TARGET A LEFT OUTER JOIN OP_DESC B ON A.FROM_OPERATION_ID = B.OPERATION_ID
WHERE A.MOVE_TRX_TYPE = 'RECEIPT'
ORDER BY A.MOVE_TRX_ID )
WHERE JOB_NO = WMT.JOB_NO
AND MOVE_TRX_ID > WMT.MOVE_TRX_ID
AND ROWNUM = 1) AS OPERATION_DESCRIPTION2 -- 다음 공정명
FROM TARGET WMT
LEFT OUTER JOIN OP_DESC B ON WMT.FROM_OPERATION_ID = B.OPERATION_ID
WHERE WMT.JOB_NO = 'JOB-A0001'
ORDER BY WMT.MOVE_TRX_ID
)
--WHERE ACT_RECEIPT_DATE2 <> ACT_RECEIPT_DATE3 확인용
PK 정보
| TARGET_N10 | EXTEND_DATE |
| TARGET_N11 | JOB_NO |
| TARGET_N13 | MOVE_TRX_TYPE, EXTEND_DATE, FROM_RESOURCE_ID |
| TARGET_N14 | JOB_ID, FROM_OP_SEQ_NO, FROM_OPERATION_ID, MOVE_TRX_TYPE, MOVE_TRX_DATE |
| TARGET_N15 | SOB_ID, ORG_ID, MOVE_TRX_DATE, MOVE_TRX_TYPE |
| TARGET_N16 | FROM_RESOURCE_ID |
| TARGET_N17 | TO_RESOURCE_ID |
| TARGET_N2 | JOB_ID, MOVE_TRX_TYPE, EXTEND_DATE |
| TARGET_N20 | JOB_ID, JOB_NO, INVENTORY_ITEM_ID, MOVE_TRX_TYPE, FROM_STEP_ID, MOVE_TRX_DATE |
| TARGET_N3 | MOVE_TRX_TYPE_ID, MOVE_TRX_TYPE |
| TARGET_N4 | FROM_STEP_ID, FROM_OP_SEQ_NO, FROM_OPERATION_ID, FROM_RESOURCE_ID |
| TARGET_N5 | TO_STEP_ID, TO_OP_SEQ_NO, TO_OPERATION_ID, TO_RESOURCE_ID |
| TARGET_N8 | INVENTORY_ITEM_ID, BOM_ITEM_ID |
| TARGET_N91 | SOB_ID, ORG_ID, MOVE_TRX_ID |
| TARGET_U1 | MOVE_TRX_ID |
| OP_DESC = VIEW(OP_DESC1 + OP_DESC_TL2) | |
| OP_DESC1_N1 | SOB_ID, ORG_ID |
| OP_DESC1_N2 | OPERATION_CLASS_ID, OPERATION_TYPE_ID |
| OP_DESC1_N3 | COST_CENTER_ID |
| OP_DESC1_N4 | SOB_ID, ORG_ID, NVL("WORKING_TYPE_LCODE",'GENERAL') |
| OP_DESC1_N5 | OPERATION_ID, NOT_DISPLAY_ONHAND |
| OP_DESC1_N6 | NVL("QC_REPORT_OP_GROUP_LCODE",'GENERAL'), NVL("QC_REJECT_EXCEPT_FLAG",'N') |
| OP_DESC1_U1 | OPERATION_ID, SOB_ID, ORG_ID |
| OP_DESC1_U2 | SOB_ID, ORG_ID, OPERATION_CODE |
| OP_DESC1_U3 | SORT_NO |
| OP_DESC_TL2_N1 | SOB_ID, ORG_ID |
| OP_DESC_TL2_N2 | LANG_CODE |
| OP_DESC_TL2_U1 | OPERATION_ID, LANG_CODE |
by 마농
[2020.08.20 18:00:53]
테이블이 하나이므로 조인 없이 분석함수 등으로도 가능할 듯 합니다.
다만 서브쿼리 두개의 조건이 다른게 좀 의아한데? (from_op_seq_no 조건 유무)
메인 쿼리엔 아무런 조건이 없는 건가요? (전체 조회?)
테이블에 대한 정보가 필요합니다.
- wipmov(from_op_seq_no, job_no, move_trx_id, move_trx_date, move_trx_type)
- 각 항목의 의미와 역할, 특히 from_op_seq_no 와 move_trx_id
- PK 정보 및 인덱스 정보
- 간략한 샘플 정보(원본 대비 결과표 예시)
by 냥냥펀치원투
[2020.08.21 10:00:27]
샘플 데이터를 준비 한다고 답변이 늦었습니다.
메인 쿼리엔 아무런 조건이 없는 건가요? (전체 조회?)
-> 기간으로 조회 합니다.
- wipmov(from_op_seq_no, job_no, move_trx_id, move_trx_date, move_trx_type)
-> TARGET으로 변경 하였습니다. 그리고 샘플 데이터를 추가로 기입 하였습니다.
- 각 항목의 의미와 역할, 특히 from_op_seq_no 와 move_trx_id
-> 1. from_op_seq_no는 현재 공정순서를 의미합니다.
2. move_trx_id는 데이터 중복 등록을 방지하기 위한 SEQ_ID(유니크 키) 입니다.
- PK 정보 및 인덱스 정보
-> 추가로 기입 하였습니다.
-> OP_DESC는 VIEW이며 OP_DESC1 TABLE과 OP_DESC_TL2 TABLE의 조인된 뷰입니다.
- 간략한 샘플 정보(원본 대비 결과표 예시)
-> 추가로 기입 하였습니다. 원본은 TARGET이며 결과는 SELECT 쿼리 입니다.
by 마농
[2020.08.21 13:08:04]
모든 샘플의 결과가 act_receipt_date2 와 act_receipt_date3 가 동일한데?
act_receipt_date3 가 있을 이유가 있는지 의문이네요?
동일한 from_op_seq_no 에 대해 RECEIPT 이 두번 나오는 경우가 존재하는지?
by 냥냥펀치원투
[2020.08.21 13:37:39]
테스트 할 샘플 데이터가 잘 못 되었습니다. 다시 만들어 올리겠습니다.
네. 맞습니다.
새로운 MOVE_TRX_ID로 동일한 from_op_seq_no 에 대해 RECEIPT 이 중복으로 생성 가능 합니다.
그리고 TOMOVE 가 중복으로 존재 합니다.
by 마농
[2020.08.21 15:53:44]
SELECT move_trx_id
, job_no
, move_trx_type
, move_trx_date
, from_op_seq_no
, from_operation_id
, operation_code AS from_operation_code
, operation_desc AS from_operation_description
, LEAD(DECODE(move_trx_type, 'RECEIPT', move_trx_date )) IGNORE NULLS OVER(PARTITION BY job_no ORDER BY move_trx_id) act_receipt_date2
, LEAD(DECODE(move_trx_type, 'RECEIPT', move_trx_date3)) IGNORE NULLS OVER(PARTITION BY job_no ORDER BY move_trx_id) act_receipt_date3
, LEAD(DECODE(move_trx_type, 'RECEIPT', from_op_seq_no)) IGNORE NULLS OVER(PARTITION BY job_no ORDER BY move_trx_id) from_op_seq_no2
, LEAD(DECODE(move_trx_type, 'RECEIPT', operation_code)) IGNORE NULLS OVER(PARTITION BY job_no ORDER BY move_trx_id) operation_code2
, LEAD(DECODE(move_trx_type, 'RECEIPT', operation_desc)) IGNORE NULLS OVER(PARTITION BY job_no ORDER BY move_trx_id) operation_description2
FROM (SELECT a.move_trx_id
, a.job_no
, a.move_trx_type
, a.move_trx_date
, a.from_op_seq_no
, a.from_operation_id
, MAX(DECODE(move_trx_type, 'RECEIPT', move_trx_date)) OVER(PARTITION BY job_no, from_op_seq_no) move_trx_date3
, b.operation_code
, b.operation_description operation_desc
FROM target a
LEFT OUTER JOIN op_desc b
ON a.from_operation_id = b.operation_id
WHERE job_no = 'JOB-A0001'
)
;
by 냥냥펀치원투
[2020.08.21 18:03:33]
앗! 감사합니다.
본문 내용 다시 수정 하였습니다.
말씀 하신 부분은 TRX_ID = 182 ~ 185 입니다.
by 냥냥펀치원투
[2020.08.21 19:37:55]
정말 감사합니다. 마농님
매우 빠르게 동작이 됩니다.
정말 감사합니다. 마농님
) 버튼을 클릭하여 작성 하시면 됩니다.