<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.style24.front.biz.dao.TsfOrderChangeDao">

	<!-- 페이징을 위한 select절 상단 -->
	<sql id="selectForPagingHeader">
		SELECT *
		FROM   (
	</sql>

	<!-- 페이징을 위한 select절 하단 -->
	<sql id="selectForPagingFooter">
		       ) ORIGINAL
		WHERE  NUMB BETWEEN #{pageable.startRow} AND #{pageable.endRow}
	</sql>
	<!--// Paging -->

	<!-- 마이페이지 취소 목록 조회 -->
	<select id="getCancelListForMypage" parameterType="Order" resultType="Order">
		/* TsfOrderChange.getCancelListForMypage */
		SELECT GROUP_CONCAT(Z.ITEM_NM ORDER BY Z.ORD_DTL_ITEM_SQ SEPARATOR '!@!') AS ITEM_NM
		     , GROUP_CONCAT(Z.COLOR_NM ORDER BY Z.ORD_DTL_ITEM_SQ) AS COLOR_NM
		     , GROUP_CONCAT(Z.OPT_CD1 ORDER BY Z.ORD_DTL_ITEM_SQ) AS OPT_CD1
		     , GROUP_CONCAT(Z.OPT_CD2 ORDER BY Z.ORD_DTL_ITEM_SQ) AS OPT_CD2
		     , Z.ORD_NO
		     , Z.ORD_DT
		     , Z.ORD_NM
		     , Z.ORD_PHNNO
		     , Z.GIFT_ADDR_INP_YN
		     , Z.ORD_DTL_NO
		     , Z.ORD_EXCH_GB
		     , Z.GOODS_CD
		     , Z.GOODS_NM
		     , Z.LIST_PRICE
		     , Z.GOODS_TYPE
		     , Z.SYS_IMG_NM
		     , Z.ORD_DTL_STAT
		     , Z.ORD_QTY
		     , Z.CNCL_RTN_QTY
		     , Z.ORD_AMT
		     , Z.REAL_ORD_AMT
		     , Z.SAVE_PNT_AMT
		     , Z.CNCL_RTN_AMT
		     , Z.PNT_DC_AMT
		     , Z.GFCD_USE_AMT
		     , Z.SHIP_COMP_CD
		     , Z.GIFT_PACK_YN
		     , Z.SHIP_COMP_NM
		     , Z.INVOICE_NO
		     , Z.SUPPLY_COMP_CD
		     , Z.DELV_FEE_CD
		     , Z.SHOT_DELV_YN
		     , Z.CHANGEABLE_YN
		     , Z.RETURNABLE_YN
		     , Z.SELF_GOODS_YN
		     , Z.BRAND_NM
		     , Z.ORD_REQ_CHG_QTY
		     , Z.ORD_CAN_CHG_QTY
		     , Z.BANK_CD
		     , Z.ACCOUNT_NO
		     , Z.ACCOUNT_NM
		  FROM (SELECT O.ORD_NO
		             , DATE_FORMAT(O.ORD_DT, '%Y.%m.%d')                                AS ORD_DT
		             , O.ORD_NM
		             , O.ORD_PHNNO
		             , OD.ORD_DTL_NO
		             , OD.ORD_EXCH_GB
		             , OD.GOODS_CD
		             , OD.GIFT_ADDR_INP_YN
		             , G.GOODS_NM
		             , IFNULL((SELECT COLOR_KNM FROM TB_COLOR C WHERE COLOR_CD = IFNULL(NULLIF(ODI.OPT_CD1,'') , G.MAIN_COLOR_CD ) AND USE_YN  = 'Y'), ODI.OPT_CD1) AS COLOR_NM
		             , (SELECT GOODS_NM FROM TB_GOODS WHERE GOODS_CD = ODI.ITEM_CD)     AS ITEM_NM
		             , ODI.ORD_DTL_ITEM_SQ
		             , ODI.OPT_CD1
		             , ODI.OPT_CD2
		             , G.LIST_PRICE
		             , G.GOODS_TYPE
		             , CASE WHEN G.GOODS_TYPE != 'G056_S' AND G.SELF_GOODS_YN = 'Y' THEN (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G.GOODS_CD AND COLOR_CD = ODI.OPT_CD1 AND DEFAULT_IMG_YN = 'Y')
		                    ELSE (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G.GOODS_CD AND COLOR_CD = IFNULL(G.MAIN_COLOR_CD,'XX') AND DEFAULT_IMG_YN = 'Y') END AS SYS_IMG_NM
		             , OD.ORD_DTL_STAT
		             , OD.ORD_QTY
		             , OD.CNCL_RTN_QTY
		             , OD.ORD_AMT
		             , OD.REAL_ORD_AMT
		             , OD.SAVE_PNT_AMT
		             , OD.CNCL_RTN_AMT
		             , OD.PNT_DC_AMT
		             , OD.GFCD_USE_AMT
		             , OD.SHIP_COMP_CD
		             , OD.GIFT_PACK_YN
		             , (SELECT SHIP_COMP_NM
		                  FROM TB_SHIP_COMPANY Z
		                 WHERE Z.SHIP_COMP_CD = OD.SHIP_COMP_CD)                        AS SHIP_COMP_NM
		             , OD.INVOICE_NO
		             , OD.SUPPLY_COMP_CD
		             , OD.DELV_FEE_CD
		             , OD.SHOT_DELV_YN
		             , OD.CHANGEABLE_YN
		             , OD.RETURNABLE_YN
		             , G.SELF_GOODS_YN
		             , CASE BG.DISP_NM_LANG WHEN 'EN' THEN BG.BRAND_GROUP_ENM
		                                    ELSE BG.BRAND_GROUP_KNM
		                END                                                             AS BRAND_NM
		             , IFNULL(OCD.ORD_REQ_CHG_QTY, 0)                                   AS ORD_REQ_CHG_QTY
		             , OD.ORD_QTY - OD.CNCL_RTN_QTY - IFNULL(OCD.ORD_REQ_CHG_QTY, 0)    AS ORD_CAN_CHG_QTY
		             , CA.BANK_CD
		             , CA.ACCOUNT_NO
		             , IFNULL(CA.ACCOUNT_NM, O.ORD_NM)                                  AS ACCOUNT_NM
		          FROM TB_ORDER O
		         INNER JOIN TB_ORDER_DETAIL OD
		            ON O.ORD_NO = OD.ORD_NO
		           AND OD.ORD_DTL_STAT NOT IN ('G013_00', 'G013_11', 'G013_25', 'G013_97', 'G013_98', 'G013_99')
		         INNER JOIN TB_ORDER_DETAIL_ITEM ODI
		            ON OD.ORD_NO = ODI.ORD_NO
		           AND OD.ORD_DTL_NO = ODI.ORD_DTL_NO
		         INNER JOIN TB_GOODS G
		            ON OD.GOODS_CD = G.GOODS_CD
		         INNER JOIN TB_BRAND B
		            ON B.BRAND_CD = G.BRAND_CD
		         INNER JOIN TB_BRAND_GROUP BG
		            ON B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
		           AND BG.USE_YN = 'Y'
		          LEFT OUTER JOIN (SELECT OD.ORD_DTL_NO
		                                , SUM(OCD.CHG_QTY) AS ORD_REQ_CHG_QTY
		                             FROM TB_ORDER_CHANGE_DETAIL OCD
		                            INNER JOIN TB_ORDER_DETAIL OD
		                               ON OCD.ORD_DTL_NO = OD.ORD_DTL_NO
		                            INNER JOIN TB_ORDER_CHANGE OC
		                               ON OC.ORD_CHG_SQ = OCD.ORD_CHG_SQ
		                            WHERE OD.ORD_NO =  #{ordNo}
		                              AND OCD.DEL_YN = 'N'
		                              AND OCD.CHG_STAT IN ('G685_20', 'G685_21','G685_30', 'G685_31', 'G685_32', 'G685_33', 'G685_34', 'G685_50', 'G685_51')
		                              AND OC.WD_BF_SEND_YN = 'N'
		                            GROUP BY OCD.ORD_DTL_NO
		                          ) OCD
		            ON OD.ORD_DTL_NO = OCD.ORD_DTL_NO
		          LEFT OUTER JOIN TB_CUST_ACCOUNT CA
		            ON O.CUST_NO = CA.CUST_NO
		         WHERE O.ORD_NO = #{ordNo}
		           AND OD.ORD_DTL_STAT IN ('G013_10', 'G013_11', 'G013_20', 'G013_30', 'G013_35', 'G013_40')
		<choose>
			<when test='custNo > 0'>
		           AND O.CUST_NO = #{custNo}
			</when>
			<otherwise>
		           AND O.ORD_NO = #{ordNo}
		           AND O.ORD_NM = #{ordNm}
		           AND O.ORD_PHNNO = #{ordPhnno}
			</otherwise>
		</choose>
		<if test="delvFeeCd != null and delvFeeCd != ''">
			<choose>
				<when test="delvFeeCd == 'WMS'">
		           AND G.SELF_GOODS_YN = 'Y'
				</when>
				<otherwise>
		           AND OD.DELV_FEE_CD = #{delvFeeCd}
				</otherwise>
			</choose>
		</if>
		           AND O.DISP_YN = 'Y'
		           AND OD.ORD_QTY - OD.CNCL_RTN_QTY > 0
		       ) Z
		 GROUP BY Z.ORD_NO, Z.ORD_DT, Z.ORD_NM, Z.ORD_PHNNO, Z.GIFT_ADDR_INP_YN, Z.ORD_DTL_NO, Z.ORD_EXCH_GB
		        , Z.GOODS_CD, Z.GOODS_NM, Z.LIST_PRICE, Z.SYS_IMG_NM, Z.ORD_DTL_STAT, Z.ORD_QTY, Z.CNCL_RTN_QTY, Z.ORD_AMT, Z.REAL_ORD_AMT
		        , Z.SAVE_PNT_AMT, Z.CNCL_RTN_AMT, Z.PNT_DC_AMT, Z.GFCD_USE_AMT, Z.SHIP_COMP_CD, Z.GIFT_PACK_YN , Z.SHIP_COMP_NM
		        , Z.INVOICE_NO, Z.SUPPLY_COMP_CD, Z.DELV_FEE_CD, Z.SHOT_DELV_YN, Z.CHANGEABLE_YN, Z.SELF_GOODS_YN, Z.BRAND_NM
		        , Z.ORD_REQ_CHG_QTY, Z.ORD_CAN_CHG_QTY, Z.BANK_CD, Z.ACCOUNT_NO, Z.ACCOUNT_NM
		 ORDER BY Z.ORD_NO DESC, Z.DELV_FEE_CD DESC, Z.ORD_DTL_NO DESC
	</select>

	<!-- 마이페이지 반품 목록 조회 -->
	<select id="getReturnListForMypage" parameterType="Order" resultType="Order">
		/* TsfOrderChange.getReturnListForMypage */
		SELECT GROUP_CONCAT(Z.ITEM_NM ORDER BY Z.ORD_DTL_ITEM_SQ SEPARATOR '!@!') AS ITEM_NM
		     , GROUP_CONCAT(Z.COLOR_NM ORDER BY Z.ORD_DTL_ITEM_SQ) AS COLOR_NM
		     , GROUP_CONCAT(Z.OPT_CD1 ORDER BY Z.ORD_DTL_ITEM_SQ) AS OPT_CD1
		     , GROUP_CONCAT(Z.OPT_CD2 ORDER BY Z.ORD_DTL_ITEM_SQ) AS OPT_CD2
		     , Z.ORD_NO
		     , Z.ORD_DT
		     , Z.ORD_NM
		     , Z.ORD_PHNNO
		     , Z.GIFT_ADDR_INP_YN
		     , Z.ORD_DTL_NO
		     , Z.ORD_EXCH_GB
		     , Z.GOODS_CD
		     , Z.GOODS_NM
		     , Z.LIST_PRICE
		     , Z.GOODS_TYPE
		     , Z.SYS_IMG_NM
		     , Z.ORD_DTL_STAT
		     , Z.ORD_QTY
		     , Z.CNCL_RTN_QTY
		     , Z.ORD_AMT
		     , Z.REAL_ORD_AMT
		     , Z.SAVE_PNT_AMT
		     , Z.CNCL_RTN_AMT
		     , Z.PNT_DC_AMT
		     , Z.GFCD_USE_AMT
		     , Z.SHIP_COMP_CD
		     , Z.GIFT_PACK_YN
		     , Z.SHIP_COMP_NM
		     , Z.INVOICE_NO
		     , Z.SUPPLY_COMP_CD
		     , Z.DELV_FEE_CD
		     , Z.DELV_FEE_CD_GRP
		     , Z.SHOT_DELV_YN
		     , Z.CHANGEABLE_YN
		     , Z.RETURNABLE_YN
		     , Z.SELF_GOODS_YN
		     , Z.BRAND_NM
		     , Z.ORD_REQ_CHG_QTY
		     , Z.ORD_CAN_CHG_QTY
		     , Z.BANK_CD
		     , Z.ACCOUNT_NO
		     , Z.ACCOUNT_NM
		     , Z.DELV_FEE
		     , Z.RTN_DELV_FEE
		     , Z.EXC_DELV_FEE
		  FROM (SELECT O.ORD_NO
		             , DATE_FORMAT(O.ORD_DT, '%Y.%m.%d')                                      AS ORD_DT
		             , O.ORD_NM
		             , O.ORD_PHNNO
		             , OD.ORD_DTL_NO
		             , OD.ORD_EXCH_GB
		             , OD.GOODS_CD
		             , OD.GIFT_ADDR_INP_YN
		             , G.GOODS_NM
		             , IFNULL((SELECT COLOR_KNM FROM TB_COLOR C WHERE COLOR_CD = IFNULL(NULLIF(ODI.OPT_CD1,'') , G.MAIN_COLOR_CD ) AND USE_YN  = 'Y'), ODI.OPT_CD1) AS COLOR_NM
		             , (SELECT GOODS_NM FROM TB_GOODS WHERE GOODS_CD = ODI.ITEM_CD)           AS ITEM_NM
		             , ODI.ORD_DTL_ITEM_SQ
		             , ODI.OPT_CD1
		             , ODI.OPT_CD2
		             , G.LIST_PRICE
		             , G.GOODS_TYPE
		             , CASE WHEN G.GOODS_TYPE != 'G056_S' AND G.SELF_GOODS_YN = 'Y' THEN (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G.GOODS_CD AND COLOR_CD = ODI.OPT_CD1 AND DEFAULT_IMG_YN = 'Y')
		                    ELSE (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G.GOODS_CD AND COLOR_CD = IFNULL(G.MAIN_COLOR_CD,'XX') AND DEFAULT_IMG_YN = 'Y') END AS SYS_IMG_NM
		             , OD.ORD_DTL_STAT
		             , OD.ORD_QTY
		             , OD.CNCL_RTN_QTY
		             , OD.ORD_AMT
		             , OD.REAL_ORD_AMT
		             , OD.SAVE_PNT_AMT
		             , OD.CNCL_RTN_AMT
		             , OD.PNT_DC_AMT
		             , OD.GFCD_USE_AMT
		             , OD.SHIP_COMP_CD
		             , OD.GIFT_PACK_YN
		             , (SELECT SHIP_COMP_NM
		                  FROM TB_SHIP_COMPANY Z
		                 WHERE Z.SHIP_COMP_CD = OD.SHIP_COMP_CD)                              AS SHIP_COMP_NM
		             , OD.INVOICE_NO
		             , OD.SUPPLY_COMP_CD
		             , OD.DELV_FEE_CD
		             , CASE G.SELF_GOODS_YN WHEN 'Y' THEN 'WMS'
		                                    ELSE OD.DELV_FEE_CD
		                END                                                                   AS DELV_FEE_CD_GRP
		             , OD.SHOT_DELV_YN
		             , OD.CHANGEABLE_YN
		             , OD.RETURNABLE_YN
		             , G.SELF_GOODS_YN
		             , CASE BG.DISP_NM_LANG WHEN 'EN' THEN BG.BRAND_GROUP_ENM
		                                    ELSE BG.BRAND_GROUP_KNM
		                END                                                                   AS BRAND_NM
		             , IFNULL(OCD.ORD_REQ_CHG_QTY, 0)                                         AS ORD_REQ_CHG_QTY
		             , OD.ORD_QTY - OD.CNCL_RTN_QTY - IFNULL(OCD.ORD_REQ_CHG_QTY, 0)          AS ORD_CAN_CHG_QTY
		             , CA.BANK_CD
		             , CA.ACCOUNT_NO
		             , IFNULL(CA.ACCOUNT_NM, O.ORD_NM)                                        AS ACCOUNT_NM
		             , DFP.DELV_FEE
		             , DFP.RTN_DELV_FEE
		             , DFP.DELV_FEE + DFP.RTN_DELV_FEE                                        AS EXC_DELV_FEE
		          FROM TB_ORDER O
		         INNER JOIN TB_ORDER_DETAIL OD
		            ON O.ORD_NO = OD.ORD_NO
		           AND OD.ORD_DTL_STAT NOT IN ('G013_00', 'G013_11', 'G013_25', 'G013_97', 'G013_98', 'G013_99')
		         INNER JOIN TB_ORDER_DETAIL_ITEM ODI
		            ON OD.ORD_NO = ODI.ORD_NO
		           AND OD.ORD_DTL_NO = ODI.ORD_DTL_NO
		         INNER JOIN TB_GOODS G
		            ON OD.GOODS_CD = G.GOODS_CD
		         INNER JOIN TB_BRAND B
		            ON B.BRAND_CD = G.BRAND_CD
		         INNER JOIN TB_BRAND_GROUP BG
		            ON B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
		           AND BG.USE_YN = 'Y'
		          LEFT OUTER JOIN TB_DELV_FEE_POLICY DFP
		            ON OD.DELV_FEE_CD = DFP.DELV_FEE_CD
		           AND OD.SUPPLY_COMP_CD = DFP.SUPPLY_COMP_CD
		           AND DFP.USE_YN = 'Y'
		          LEFT OUTER JOIN (SELECT OD.ORD_DTL_NO
		                                , SUM(OCD.CHG_QTY) AS ORD_REQ_CHG_QTY
		                             FROM TB_ORDER_CHANGE_DETAIL OCD
		                            INNER JOIN TB_ORDER_DETAIL OD
		                               ON OCD.ORD_DTL_NO = OD.ORD_DTL_NO
		                            INNER JOIN TB_ORDER_CHANGE OC
		                               ON OC.ORD_CHG_SQ = OCD.ORD_CHG_SQ
		                            WHERE OD.ORD_NO = #{ordNo}
		                              AND OCD.DEL_YN = 'N'
		                              AND OCD.CHG_STAT IN ('G685_20', 'G685_21','G685_30', 'G685_31', 'G685_32', 'G685_33', 'G685_34', 'G685_50', 'G685_51')
		                              AND OC.WD_BF_SEND_YN = 'N'
		                            GROUP BY OCD.ORD_DTL_NO
		                          ) OCD
		            ON OD.ORD_DTL_NO = OCD.ORD_DTL_NO
		          LEFT OUTER JOIN TB_CUST_ACCOUNT CA
		            ON O.CUST_NO = CA.CUST_NO
		         WHERE O.DISP_YN = 'Y'
		           AND OD.ORD_DTL_STAT IN ('G013_50', 'G013_55', 'G013_60')
		<choose>
			<when test='custNo > 0'>
		           AND O.CUST_NO = #{custNo}
			</when>
			<otherwise>
		           AND O.ORD_NO = #{ordNo}
		           AND O.ORD_NM = #{ordNm}
		           AND O.ORD_PHNNO = #{ordPhnno}
			</otherwise>
		</choose>
		<if test="ordNo != null and ordNo != ''">
		           AND O.ORD_NO = #{ordNo}
		</if>
		<if test="delvFeeCd != null and delvFeeCd != ''">
			<choose>
				<when test="delvFeeCd == 'WMS'">
		           AND G.SELF_GOODS_YN = 'Y'
				</when>
				<otherwise>
		           AND OD.DELV_FEE_CD = #{delvFeeCd}
				</otherwise>
			</choose>
		</if>
		<choose>
			<when test="chgGb == 'G680_30'">
		           AND OD.RETURNABLE_YN = 'Y'
			</when>
			<when test="chgGb == 'G680_40'">
		           AND OD.CHANGEABLE_YN = 'Y'
			</when>
		</choose>
		       ) Z
		 WHERE Z.ORD_CAN_CHG_QTY > 0
		 GROUP BY Z.ORD_NO, Z.ORD_DT, Z.ORD_NM, Z.ORD_PHNNO, Z.GIFT_ADDR_INP_YN, Z.ORD_DTL_NO, Z.ORD_EXCH_GB, Z.GOODS_CD, Z.GOODS_NM, Z.LIST_PRICE, Z.SYS_IMG_NM
		        , Z.ORD_DTL_STAT, Z.ORD_QTY, Z.CNCL_RTN_QTY, Z.ORD_AMT, Z.REAL_ORD_AMT, Z.SAVE_PNT_AMT, Z.CNCL_RTN_AMT, Z.PNT_DC_AMT
		        , Z.GFCD_USE_AMT, Z.SHIP_COMP_CD, Z.GIFT_PACK_YN, Z.SHIP_COMP_NM, Z.INVOICE_NO, Z.SUPPLY_COMP_CD, Z.DELV_FEE_CD, Z.DELV_FEE_CD_GRP
		        , Z.SHOT_DELV_YN, Z.CHANGEABLE_YN, Z.SELF_GOODS_YN, Z.BRAND_NM, Z.ORD_REQ_CHG_QTY, Z.ORD_CAN_CHG_QTY, Z.BANK_CD
		        , Z.ACCOUNT_NO, Z.ACCOUNT_NM, Z.DELV_FEE, Z.RTN_DELV_FEE, Z.EXC_DELV_FEE
		ORDER BY Z.ORD_DTL_NO DESC
	</select>
	
	<!-- 사용 상품권 정보 조회 -->
	<select id="getUsedGiftcardInfo" parameterType="Order" resultType="GiftCard">
		/* TsfOrderChange.getUsedGiftcardInfo */
		SELECT CGH.ORD_NO
		     , CGH.ORD_DTL_NO
		     , SUM(CGH.GFCD_AMT) AS GFCD_AMT
		  FROM TB_CUST_GIFTCARD_HST CGH
		 INNER JOIN TB_CUST_GIFTCARD CG
		    ON CG.CUST_GFCD_SQ = CGH.CUST_GFCD_SQ
		 WHERE CGH.CUST_NO = #{custNo}
		   AND CGH.ORD_NO = #{ordNo}
		<if test="ordDtlNoArr != null">
		   AND CGH.ORD_DTL_NO IN
			<foreach collection="ordDtlNoArr" item="item" index="index"  open="(" close=")" separator=",">
				#{item}
			</foreach>
		</if>
		   AND OCCUR_GB = 'G074_12'
		   AND NOW() BETWEEN CG.USE_ST_DATE AND CG.USE_EXP_DATE
		 GROUP BY CGH.ORD_NO, CGH.ORD_DTL_NO
		 ORDER BY CGH.ORD_NO, CGH.ORD_DTL_NO
	</select>
	
	<!-- 사용 포인트 정보 조회 -->
	<select id="getUsedPointInfo" parameterType="Order" resultType="Point">
		/* TsfOrderChange.getUsedPointInfo */
		SELECT CPH.ORD_NO
		     , CPH.ORD_DTL_NO
		     , SUM(CPH.PNT_AMT) AS PNT_AMT
		  FROM TB_CUST_POINT_HST CPH
		 INNER JOIN TB_CUST_POINT CP
		    ON CP.CUST_PNT_SQ = CPH.CUST_PNT_SQ
		 WHERE CPH.CUST_NO = #{custNo}
		   AND CPH.ORD_NO = #{ordNo}
		<if test="ordDtlNoArr != null">
		   AND CPH.ORD_DTL_NO IN
			<foreach collection="ordDtlNoArr" item="item" index="index"  open="(" close=")" separator=",">
				#{item}
			</foreach>
		</if>
		   AND CPH.OCCUR_GB = 'G069_10'
		   AND CP.EXP_BE_DT <![CDATA[>]]> NOW()
		   AND CP.EXP_CMP_DT IS NULL
		 GROUP BY CPH.ORD_NO, CPH.ORD_DTL_NO
		 ORDER BY CPH.ORD_NO, CPH.ORD_DTL_NO
	</select>
	
	<!-- 교환 옵션 정보 조회 -->
	<select id="getExchangeOptionInfo" parameterType="OrderChange" resultType="OrderChange">
		/* TscOrderChange.getExchangeOptionInfo */
		SELECT Z.ORD_NO
		     , Z.ORD_DTL_NO
		     , Z.ORD_DTL_ITEM_SQ
		     , Z.ITEM_CD
		     , Z.OPT_CD
		     , Z.OPT_CD1
		     , Z.OPT_CD2
		     , Z.COLOR_NM
		     , Z.GOODS_TYPE
		     , Z.SELF_GOODS_YN
		     , Z.ITEM_NM
		     , Z.GOODS_STAT
		     , GROUP_CONCAT(Z.CHG_OPT_CD ORDER BY Z.DISP_ORD SEPARATOR ',')     AS CHG_OPT_CD
		     , GROUP_CONCAT(Z.CHG_OPT_CD2 ORDER BY Z.DISP_ORD SEPARATOR ',')    AS CHG_OPT_CD2
		     , GROUP_CONCAT(Z.CURR_STOCK_QTY ORDER BY Z.DISP_ORD SEPARATOR ',') AS CHG_CURR_STOCK_QTY
		     , GROUP_CONCAT(Z.SOLDOUT_YN ORDER BY Z.DISP_ORD SEPARATOR ',')     AS CHG_SOLDOUT_YN
		  FROM (SELECT O.ORD_NO
		             , OD.ORD_DTL_NO
		             , ODI.ORD_DTL_ITEM_SQ
		             , ODI.ITEM_CD
		             , ODI.OPT_CD
		             , ODI.OPT_CD1
		             , ODI.OPT_CD2
		             , IFNULL((SELECT COLOR_KNM FROM TB_COLOR WHERE COLOR_CD = IFNULL(NULLIF(ODI.OPT_CD1,'') , G1.MAIN_COLOR_CD ) AND USE_YN  = 'Y'), ODI.OPT_CD1) AS COLOR_NM
		             , OD.GOODS_TYPE
		             , G1.SELF_GOODS_YN
		             , G2.GOODS_NM                           AS ITEM_NM
		             , G2.GOODS_STAT
		             , OP.OPT_CD                             AS CHG_OPT_CD
		             , OP.OPT_CD2                            AS CHG_OPT_CD2
		             , OP.SOLDOUT_YN
		             , OP.DISP_ORD
		             , VS.CURR_STOCK_QTY - VS.BASE_STOCK_QTY AS CURR_STOCK_QTY
		          FROM TB_ORDER O
		         INNER JOIN TB_ORDER_DETAIL OD
		            ON O.ORD_NO = OD.ORD_NO
		         INNER JOIN TB_ORDER_DETAIL_ITEM ODI
		            ON OD.ORD_NO = ODI.ORD_NO
		           AND OD.ORD_DTL_NO = ODI.ORD_DTL_NO
		         INNER JOIN TB_GOODS G1
		            ON OD.GOODS_CD = G1.GOODS_CD
		         INNER JOIN TB_GOODS G2
		            ON ODI.ITEM_CD = G2.GOODS_CD
		         INNER JOIN TB_OPTION OP
		            ON ODI.ITEM_CD = OP.GOODS_CD
		           AND ODI.OPT_CD1 = OP.OPT_CD1
		         INNER JOIN VW_STOCK VS
		            ON OP.GOODS_CD = VS.GOODS_CD
		           AND OP.OPT_CD = VS.OPT_CD
		           AND VS.DISP_YN = 'Y'
		          LEFT OUTER JOIN TB_COLOR C
		            ON C.COLOR_CD = IFNULL(NULLIF(ODI.OPT_CD1, ''), G1.MAIN_COLOR_CD)
		           AND C.USE_YN = 'Y'
		         WHERE O.ORD_NO = #{ordNo}
		           AND OD.ORD_DTL_NO = #{ordDtlNo}
		  ) Z
		 GROUP BY Z.ORD_NO, Z.ORD_DTL_NO, Z.ORD_DTL_ITEM_SQ, Z.ITEM_CD, Z.OPT_CD, Z.OPT_CD1, Z.OPT_CD2
		        , Z.COLOR_NM, Z.GOODS_TYPE, Z.SELF_GOODS_YN, Z.ITEM_NM, Z.GOODS_STAT
	</select>

	<!-- 마이페이지 취반교 내역 조회 -->
	<select id="getCreList" parameterType="OrderChange" resultType="OrderChange">
		/* TsfOrderChange.getCreList */
		SELECT GROUP_CONCAT(Z.ITEM_NM ORDER BY Z.ORD_DTL_ITEM_SQ SEPARATOR '!@!')       AS ITEM_NM
		     , GROUP_CONCAT(Z.COLOR_NM ORDER BY Z.ORD_DTL_ITEM_SQ)                      AS COLOR_NM
		     , GROUP_CONCAT(Z.OPT_CD1 ORDER BY Z.ORD_DTL_ITEM_SQ)                       AS OPT_CD1
		     , GROUP_CONCAT(Z.OPT_CD2 ORDER BY Z.ORD_DTL_ITEM_SQ)                       AS OPT_CD2
		     , Z.ORD_CHG_SQ
		     , Z.CHG_GB
		     , Z.CHG_QTY
		     , Z.ORD_DT
		     , Z.CHG_DATE
		     , Z.CHG_TIME
		     , Z.CHG_ORD_DTL_NO
		     , Z.CHG_STAT
		     , Z.CHG_STAT_NM
		     , Z.CHG_REASON
		     , Z.ORD_NO
		     , Z.ORD_DTL_NO
		     , Z.GOODS_CD
		     , Z.BRAND_NM
		     , Z.SHOT_DELV_YN
		     , Z.SELF_GOODS_YN
		     , Z.GOODS_NM
		     , Z.GOODS_TYPE
		     , Z.LIST_PRICE
		     , Z.ADD_PAY_COST
		     , Z.SYS_IMG_NM
		     , Z.GIFT_PACK_YN
		     , Z.GIFT_ADDR_INP_YN
		     , Z.GIFT_CANCEL_DAY
		     , Z.REFUND_AMT
		  FROM (SELECT OC.ORD_CHG_SQ
		             , OC.CHG_GB
		             , OCD.CHG_QTY
		             , DATE_FORMAT(O.ORD_DT, '%Y.%m.%d')                                AS ORD_DT
		             , IFNULL(DATE_FORMAT(OCDH.REG_DT, '%Y.%m.%d'), '')                 AS CHG_DATE
		             , IFNULL(DATE_FORMAT(OCDH.REG_DT,'%H:%i:%S'), '')                  AS CHG_TIME
		             , OCD.CHG_ORD_DTL_NO
		             , OCD.CHG_STAT
		             , FN_GET_CODE_NM('G685', OCD.CHG_STAT)                             AS CHG_STAT_NM
		             , OC.CHG_REASON
		             , OD.ORD_NO
		             , OCD.ORD_DTL_NO
		             , G1.GOODS_CD
		             , CASE BG.DISP_NM_LANG WHEN 'EN' THEN BG.BRAND_GROUP_ENM
		                                    ELSE BG.BRAND_GROUP_KNM
		                END                                                             AS BRAND_NM
		             , OD.SHOT_DELV_YN
		             , G1.SELF_GOODS_YN
		             , G1.GOODS_NM
		             , IFNULL((SELECT COLOR_KNM FROM TB_COLOR C WHERE COLOR_CD = IFNULL(NULLIF(ODI.OPT_CD1,'') , G1.MAIN_COLOR_CD ) AND USE_YN  = 'Y'), ODI.OPT_CD1) AS COLOR_NM
		             , G2.GOODS_NM                                                      AS ITEM_NM
		             , G1.GOODS_TYPE
		             , ODI.ORD_DTL_ITEM_SQ
		             , ODI.OPT_CD1
		             , ODI.OPT_CD2
		             , G1.LIST_PRICE
		             , OC.ADD_PAY_COST
		             , CASE WHEN G1.GOODS_TYPE != 'G056_S' AND G1.SELF_GOODS_YN = 'Y' THEN (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G1.GOODS_CD AND COLOR_CD = ODI.OPT_CD1 AND DEFAULT_IMG_YN = 'Y')
		                    ELSE (SELECT MAX(SYS_IMG_NM) FROM TB_GOODS_IMG WHERE GOODS_CD = G1.GOODS_CD AND COLOR_CD = IFNULL(G1.MAIN_COLOR_CD,'XX') AND DEFAULT_IMG_YN = 'Y') END AS SYS_IMG_NM
		             , OD.GIFT_PACK_YN
		             , OD.GIFT_ADDR_INP_YN
		             , TIMESTAMPDIFF(DAY, O.ORD_DT, OCDH.REG_DT)                        AS GIFT_CANCEL_DAY
		             , R.REFUND_AMT
		          FROM TB_ORDER_CHANGE OC
		         INNER JOIN TB_ORDER_CHANGE_DETAIL OCD
		            ON OC.ORD_CHG_SQ = OCD.ORD_CHG_SQ
		           AND OCD.CHG_STAT NOT IN ('G685_10', 'G685_11')
		          LEFT OUTER JOIN (SELECT ORD_CHG_SQ
		                                , ORD_DTL_NO
		                                , CHG_STAT
		                                , IFNULL(MAX(REG_DT), '') AS REG_DT
		                             FROM TB_ORDER_CHANGE_DETAIL_HST
		<where>
			<if test="ordChgSqArr != null">
			                        AND ORD_CHG_SQ IN
				<foreach collection="ordChgSqArr" item="item" index="index"  open="(" close=")" separator=",">
				#{item}
				</foreach>
			</if>
		</where>
		                            GROUP BY ORD_CHG_SQ, ORD_DTL_NO, CHG_STAT
		                            ) OCDH
		            ON OCD.ORD_CHG_SQ = OCDH.ORD_CHG_SQ
		           AND OCD.ORD_DTL_NO = OCDH.ORD_DTL_NO
		           AND OCD.CHG_STAT = OCDH.CHG_STAT
		         INNER JOIN TB_ORDER_DETAIL OD
		            ON OCD.ORD_DTL_NO = OD.ORD_DTL_NO
		           AND OD.ORD_DTL_STAT NOT IN ('G013_00', 'G013_11', 'G013_25')
		         INNER JOIN TB_ORDER O
		            ON OD.ORD_NO = O.ORD_NO
		         INNER JOIN TB_PAYMENT P
		            ON O.ORD_NO = P.ORD_NO
		           AND P.PAY_STAT IN ('G016_10', 'G016_30')
		           AND P.PAY_GB = 'O'
		           AND P.PG_GB <![CDATA[<>]]> 'NAVER_ORDER'
		         INNER JOIN TB_ORDER_DETAIL_ITEM ODI
		            ON OD.ORD_NO = ODI.ORD_NO
		           AND OD.ORD_DTL_NO = ODI.ORD_DTL_NO
		           AND OD.ORD_DTL_STAT NOT IN ('G013_00', 'G013_11', 'G013_25')
		         INNER JOIN TB_GOODS G1
		            ON OD.GOODS_CD = G1.GOODS_CD
		         INNER JOIN TB_GOODS G2
		            ON ODI.ITEM_CD = G2.GOODS_CD
		         INNER JOIN TB_BRAND B
		            ON G1.BRAND_CD = B.BRAND_CD
		           AND B.USE_YN = 'Y'
		         INNER JOIN TB_BRAND_GROUP BG
		            ON B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
		           AND BG.USE_YN = 'Y'
		          LEFT OUTER JOIN TB_REFUND R
		            ON R.ORD_CHG_SQ = OC.ORD_CHG_SQ
		         WHERE O.DISP_YN = 'Y'
		<choose>
			<when test="custNo > 0">
		           AND O.CUST_NO = #{custNo}
			</when>
			<otherwise>
		           AND O.ORD_NO = #{ordNo}
		           AND O.ORD_NM = #{ordNm}
		           AND O.ORD_PHNNO = #{ordPhnno}
			</otherwise>
		</choose>
		<if test="ordChgSq != null and ordChgSq != ''">
		           AND OC.ORD_CHG_SQ = #{ordChgSq}
		</if>
		<if test="ordDtlNoArr != null">
		           AND OD.ORD_DTL_NO IN
			<foreach collection="ordDtlNoArr" item="item" index="index"  open="(" close=")" separator=",">
				#{item}
			</foreach>
		</if>
		<if test="ordChgSqArr != null">
		           AND OC.ORD_CHG_SQ IN
			<foreach collection="ordChgSqArr" item="item" index="index"  open="(" close=")" separator=",">
				#{item}
			</foreach>
		</if>
		<if test="chgStat != null and chgStat != ''">
			<choose>
				<when test="chgStat == 'G685_91'">
		           AND OCD.CHG_STAT IN ('G685_21', 'G685_32', 'G685_51')
				</when>
				<when test="chgStat == 'G685_34'">
		           AND OCD.CHG_STAT = 'G685_40'
		           AND EXISTS (SELECT 1
		                         FROM TB_ORDER_DETAIL OD2
		                        WHERE OD2.ORD_DTL_NO = OCD.CHG_ORD_DTL_NO
		                          AND OD2.ORD_DTL_STAT IN ('G013_20', 'G013_30', 'G013_35', 'G013_40', 'G013_50', 'G013_55'))
				</when>
				<when test="chgStat == 'G685_40'">
		           AND OCD.CHG_STAT = 'G685_40'
		           AND EXISTS (SELECT 1
		                         FROM TB_ORDER_DETAIL OD2
		                        WHERE OD2.ORD_DTL_NO = OCD.CHG_ORD_DTL_NO
		                          AND OD2.ORD_DTL_STAT IN ('G013_60', 'G013_70'))
				</when>
				<when test="chgStat == 'G685_92'">
		           AND OCD.CHG_STAT IN ('G685_49', 'G685_69')
				</when>
				<when test="chgStat == 'G685_93'">
		           AND OCD.CHG_STAT IN ('G685_17', 'G685_18')
		           AND OC.CHG_REASON <![CDATA[<>]]> 'G686_30'
				</when>
				<when test="chgStat == 'G685_94'">
		           AND OCD.CHG_STAT IN ('G685_17', 'G685_18')
		           AND OC.CHG_REASON = 'G686_30'
				</when>
				<otherwise>
		           AND OCD.CHG_STAT = #{chgStat}
				</otherwise>
			</choose>
		</if>
		           -- AND O.REG_DT > 20210901
		  ) Z
		 GROUP BY Z.ORD_CHG_SQ, Z.CHG_GB, Z.CHG_QTY, Z.ORD_DT, Z.CHG_DATE, Z.CHG_TIME, Z.CHG_ORD_DTL_NO, Z.CHG_STAT, Z.CHG_STAT_NM, Z.CHG_REASON
		        , Z.ORD_NO, Z.ORD_DTL_NO, Z.GOODS_CD, Z.BRAND_NM, Z.SHOT_DELV_YN, Z.SELF_GOODS_YN, Z.GOODS_NM, Z.GOODS_TYPE, Z.LIST_PRICE
		        , Z.ADD_PAY_COST, Z.SYS_IMG_NM, Z.GIFT_PACK_YN, Z.GIFT_ADDR_INP_YN, Z.GIFT_CANCEL_DAY, Z.REFUND_AMT
		 ORDER BY Z.ORD_CHG_SQ DESC, Z.ORD_DTL_NO DESC
	</select>

	<!-- 마이페이지 취반교 조회 페이징 처리 주문변경번호 수 조회 -->
	<select id="getPagingCreListCount" parameterType="OrderChange" resultType="int">
		/* TsfOrderChange.getPagingCreListCount */
		SELECT COUNT(*) AS CNT
		  FROM (SELECT OCD.ORD_CHG_SQ
		          FROM TB_ORDER_CHANGE OC
		         INNER JOIN TB_ORDER_CHANGE_DETAIL OCD
		            ON OC.ORD_CHG_SQ = OCD.ORD_CHG_SQ
		           AND OCD.CHG_STAT NOT IN ('G685_10', 'G685_11')
		          LEFT OUTER JOIN TB_ORDER_CHANGE_DETAIL_HST OCDH
		            ON OCD.ORD_CHG_SQ = OCDH.ORD_CHG_SQ
		           AND OCD.ORD_DTL_NO = OCDH.ORD_DTL_NO
		           AND OCD.CHG_STAT = OCDH.CHG_STAT
		         INNER JOIN TB_ORDER_DETAIL OD
		            ON OCD.ORD_DTL_NO = OD.ORD_DTL_NO
		           AND OD.ORD_DTL_STAT NOT IN ('G013_00', 'G013_11', 'G013_25')
		         INNER JOIN TB_ORDER O
		            ON OD.ORD_NO = O.ORD_NO
		         INNER JOIN TB_PAYMENT P
		            ON O.ORD_NO = P.ORD_NO
		           AND P.PAY_STAT IN ('G016_10', 'G016_30')
		           AND P.PAY_GB = 'O'
		           AND P.PG_GB <![CDATA[<>]]> 'NAVER_ORDER'
		         INNER JOIN TB_ORDER_DETAIL_ITEM ODI
		            ON OD.ORD_NO = ODI.ORD_NO
		           AND OD.ORD_DTL_NO = ODI.ORD_DTL_NO
		         INNER JOIN TB_GOODS G1
		            ON OD.GOODS_CD = G1.GOODS_CD
		         INNER JOIN TB_GOODS G2
		            ON ODI.ITEM_CD = G2.GOODS_CD
		         INNER JOIN TB_BRAND B
		            ON G1.BRAND_CD = B.BRAND_CD
		           AND B.USE_YN = 'Y'
		         WHERE O.DISP_YN = 'Y'
		<choose>
			<when test="custNo > 0">
		           AND O.CUST_NO = #{custNo}
			</when>
			<otherwise>
		           AND O.ORD_NO = #{ordNo}
		           AND O.ORD_NM = #{ordNm}
		           AND O.ORD_PHNNO = #{ordPhnno}
			</otherwise>
		</choose>
		<if test="stDate != null and stDate != '' and edDate != null and edDate != ''">
		           AND OCD.REG_DT BETWEEN CONCAT(#{stDate}, ' 00:00:00') AND CONCAT(#{edDate}, ' 23:59:59')
		</if>
		<if test="chgStat != null and chgStat != ''">
			<choose>
				<when test="chgStat == 'G685_91'">
		           AND OCD.CHG_STAT IN ('G685_21', 'G685_32', 'G685_51')
				</when>
				<when test="chgStat == 'G685_34'">
		           AND OCD.CHG_STAT = 'G685_40'
		           AND EXISTS (SELECT 1
		                         FROM TB_ORDER_DETAIL OD2
		                        WHERE OD2.ORD_DTL_NO = OCD.CHG_ORD_DTL_NO
		                          AND OD2.ORD_DTL_STAT IN ('G013_20', 'G013_30', 'G013_35', 'G013_40', 'G013_50', 'G013_55'))
				</when>
				<when test="chgStat == 'G685_40'">
		           AND OCD.CHG_STAT = 'G685_40'
		           AND EXISTS (SELECT 1
		                         FROM TB_ORDER_DETAIL OD2
		                        WHERE OD2.ORD_DTL_NO = OCD.CHG_ORD_DTL_NO
		                          AND OD2.ORD_DTL_STAT IN ('G013_60', 'G013_70'))
				</when>
				<when test="chgStat == 'G685_92'">
		           AND OCD.CHG_STAT IN ('G685_49', 'G685_69')
				</when>
				<when test="chgStat == 'G685_93'">
		           AND OCD.CHG_STAT IN ('G685_17', 'G685_18')
		           AND OC.CHG_REASON <![CDATA[<>]]> 'G686_30'
				</when>
				<when test="chgStat == 'G685_94'">
		           AND OCD.CHG_STAT IN ('G685_17', 'G685_18')
		           AND OC.CHG_REASON = 'G686_30'
				</when>
				<otherwise>
		           AND OCD.CHG_STAT = #{chgStat}
				</otherwise>
			</choose>
		</if>
		           -- AND O.REG_DT > 20210901
		         GROUP BY OCD.ORD_CHG_SQ
		       ) Z
	</select>

	<!-- 마이페이지 취반교 조회 페이징 처리 주문변경번호 조회 -->
	<select id="getPagingCreList" parameterType="OrderChange" resultType="OrderChange">
		/* TsfOrderChange.getPagingCreList */
		<include refid="selectForPagingHeader"/>
		SELECT OCD.ORD_CHG_SQ
		     , RANK() OVER(ORDER BY OCD.ORD_CHG_SQ DESC) AS NUMB
		  FROM TB_ORDER_CHANGE OC
		 INNER JOIN TB_ORDER_CHANGE_DETAIL OCD
		    ON OC.ORD_CHG_SQ = OCD.ORD_CHG_SQ
		   AND OCD.CHG_STAT NOT IN ('G685_10', 'G685_11')
		  LEFT OUTER JOIN TB_ORDER_CHANGE_DETAIL_HST OCDH
		    ON OCD.ORD_CHG_SQ = OCDH.ORD_CHG_SQ
		   AND OCD.ORD_DTL_NO = OCDH.ORD_DTL_NO
		   AND OCD.CHG_STAT = OCDH.CHG_STAT
		 INNER JOIN TB_ORDER_DETAIL OD
		    ON OCD.ORD_DTL_NO = OD.ORD_DTL_NO
		   AND OD.ORD_DTL_STAT NOT IN ('G013_00', 'G013_11', 'G013_25')
		 INNER JOIN TB_ORDER O
		    ON OD.ORD_NO = O.ORD_NO
		 INNER JOIN TB_PAYMENT P
		    ON O.ORD_NO = P.ORD_NO
		   AND P.PAY_STAT IN ('G016_10', 'G016_30')
		   AND P.PAY_GB = 'O'
		   AND P.PG_GB <![CDATA[<>]]> 'NAVER_ORDER'
		INNER JOIN TB_ORDER_DETAIL_ITEM ODI
		    ON OD.ORD_NO = ODI.ORD_NO
		   AND OD.ORD_DTL_NO = ODI.ORD_DTL_NO
		 INNER JOIN TB_GOODS G1
		    ON OD.GOODS_CD = G1.GOODS_CD
		 INNER JOIN TB_GOODS G2
		    ON ODI.ITEM_CD = G2.GOODS_CD
		 INNER JOIN TB_BRAND B
		    ON G1.BRAND_CD = B.BRAND_CD
		   AND B.USE_YN = 'Y'
		 WHERE O.DISP_YN = 'Y'
		<choose>
			<when test="custNo > 0">
		   AND O.CUST_NO = #{custNo}
			</when>
			<otherwise>
		   AND O.ORD_NO = #{ordNo}
		   AND O.ORD_NM = #{ordNm}
		   AND O.ORD_PHNNO = #{ordPhnno}
			</otherwise>
		</choose>
		<if test="stDate != null and stDate != '' and edDate != null and edDate != ''">
		   AND OCD.REG_DT BETWEEN CONCAT(#{stDate}, ' 00:00:00') AND CONCAT(#{edDate}, ' 23:59:59')
		</if>
		<if test="chgStat != null and chgStat != ''">
			<choose>
				<when test="chgStat == 'G685_91'">
		   AND OCD.CHG_STAT IN ('G685_21', 'G685_32', 'G685_51')
				</when>
				<when test="chgStat == 'G685_34'">
		   AND OCD.CHG_STAT = 'G685_40'
		   AND EXISTS (SELECT 1
		                 FROM TB_ORDER_DETAIL OD2
		                WHERE OD2.ORD_DTL_NO = OCD.CHG_ORD_DTL_NO
		                  AND OD2.ORD_DTL_STAT IN ('G013_20', 'G013_30', 'G013_35', 'G013_40', 'G013_50', 'G013_55'))
				</when>
				<when test="chgStat == 'G685_40'">
		   AND OCD.CHG_STAT = 'G685_40'
		   AND EXISTS (SELECT 1
		                 FROM TB_ORDER_DETAIL OD2
		                WHERE OD2.ORD_DTL_NO = OCD.CHG_ORD_DTL_NO
		                  AND OD2.ORD_DTL_STAT IN ('G013_60', 'G013_70'))
				</when>
				<when test="chgStat == 'G685_92'">
		   AND OCD.CHG_STAT IN ('G685_49', 'G685_69')
				</when>
				<when test="chgStat == 'G685_93'">
		   AND OCD.CHG_STAT IN ('G685_17', 'G685_18')
		   AND OC.CHG_REASON <![CDATA[<>]]> 'G686_30'
				</when>
				<when test="chgStat == 'G685_94'">
		   AND OCD.CHG_STAT IN ('G685_17', 'G685_18')
		   AND OC.CHG_REASON = 'G686_30'
				</when>
				<otherwise>
		   AND OCD.CHG_STAT = #{chgStat}
				</otherwise>
			</choose>
		</if>
		 -- AND O.REG_DT > 20210901
		 GROUP BY OCD.ORD_CHG_SQ
		<include refid="selectForPagingFooter"/>
	</select>
</mapper>
