2023.01.19(쿼리분석)

<!-- 공단검진자동판정기준코드 등록 -->
 <insert id="registerExamPanRefCd" parameterType="kr.amc.amis.hm.gz.cd.entity.HeGHSExamPanJRefCdDTO">
	 <![CDATA[
	     MERGE /* SQLID=kr.amc.amis.hm.gz.cd.store.mapper.HeGHSExamPanJRefCdManageMapper.registerExamPanRefCd */
	     INTO GXEXDRFVL  --일반검진검사결과참고치상세
	     USING DUAL
	        ON (
	                 RSLT_JUDG_CD        = #{rsltJudgCd,        jdbcType=VARCHAR}
	             AND ORDR_CD             = #{ordrCd,            jdbcType=VARCHAR}
	             AND APPLY_ST_DT         = REPLACE(SUBSTR(#{applyStDt,         jdbcType=VARCHAR}, 1, 10),'-','')
	            )
	      WHEN MATCHED THEN 
	           UPDATE 
	           SET
	                   USE_SEX_CD           = #{useSexCd,            jdbcType=VARCHAR}
	                 , RFVL_SN              = #{rfvlSn,              jdbcType=VARCHAR}   -- 참고치일련번호 2023.01.05 / 박민준 
	                 , RSLT_TYP_VAL         = #{rsltTypVal,          jdbcType=VARCHAR}
	                 , EXM_RFVL_LOW_VAL     = #{exmRfvlLowVal,       jdbcType=VARCHAR}
	                 , EXM_RFVL_UPLMT_VAL   = #{exmRfvlUplmtVal,     jdbcType=VARCHAR}
	                 , APPLY_END_DT         = REPLACE(SUBSTR(#{applyStDt,         jdbcType=VARCHAR}, 1, 10),'-','')
	                 , RMRK                 = #{rmrk,                jdbcType=VARCHAR}
	                 , CRTE_LOW_AGE         = #{crteLowAge,          jdbcType=VARCHAR}
	                 , CRTE_UPLMT_AGE       = #{crteUplmtAge,         jdbcType=VARCHAR}
	                 , FINL_UPID_EMPNO      = #{frstEntrEmpno,       jdbcType=VARCHAR}
	                 , FINL_UPDT_DTM        = SYSDATE
           WHEN NOT MATCHED THEN
                INSERT
                (
                      RSLT_JUDG_CD	   
                    , ORDR_CD        
                    , APPLY_ST_DT
                    , USE_SEX_CD
                    , RFVL_SN
                    , RSLT_TYP_VAL
                    , EXM_RFVL_LOW_VAL
                    , EXM_RFVL_UPLMT_VAL
                    , APPLY_END_DT
                    , RMRK    
                    , CRTE_LOW_AGE
                    , CRTE_UPLMT_AGE
                    , FRST_ENTR_EMPNO
                    , FRST_ENTR_DTM
                    , FINL_UPID_EMPNO
                    , FINL_UPDT_DTM
                )
               VALUES
               (                     
                      #{rsltJudgCd,        jdbcType=VARCHAR}
                    , #{ordrCd,            jdbcType=VARCHAR}   
                    , REPLACE(SUBSTR(#{applyStDt,         jdbcType=VARCHAR}, 1, 10),'-','')
                    , #{useSexCd,          jdbcType=VARCHAR}
                    , #{rfvlSn,            jdbcType=VARCHAR}
                    , #{rsltTypVal,        jdbcType=VARCHAR}
                    , #{exmRfvlLowVal,     jdbcType=VARCHAR}
                    , #{exmRfvlUplmtVal,   jdbcType=VARCHAR}
                    , REPLACE(SUBSTR(#{applyEndDt,        jdbcType=VARCHAR},1,10), '-','')   
                    , #{rmrk,              jdbcType=VARCHAR}
                    , #{crteLowAge,        jdbcType=VARCHAR}
                    , #{crteUplmtAge,       jdbcType=VARCHAR}
                    , #{frstEntrEmpno}
                    , SYSDATE
                    , #{finlUpidEmpno}
                    , SYSDATE
                )   
                            
	 ]]>
 </insert>
 	 
 <!-- 공단검진자동판정기준코드 조회 -->
   <select id ="retrieveExamPanRefCdList" parameterType="kr.amc.amis.hm.gz.cd.entity.HeGHSExamPanJRefCdDTO"  resultType="kr.amc.amis.hm.gz.cd.entity.HeGHSExamPanJRefCdDTO">
    <![CDATA[
			SELECT 
			        RSLT_JUDG_CD        -- 결과판정코드
                   , VL.ORDR_CD         -- 처방코드
                   , ORCD.DTL_CD        AS DTL_CD -- 건강검진종합출력서식항목코드 세부코드
                   , ORCD.KOR_NM        AS DTL_NM -- 건강검진종합출력서식항목코드 한글명
                   , APPLY_ST_DT        -- 시작일자                   
                   , VL.USE_SEX_CD      -- 사용성별코드
                   , RSLT_TYP_VAL       -- 결과유형값
                   , EXM_RFVL_LOW_VAL   -- 검사참고치하한값
                   , EXM_RFVL_UPLMT_VAL -- 검사참고치상한값
                   , APPLY_END_DT       -- 종료일자                
                   , VL.RMRK            -- 특기사항
                   , CRTE_LOW_AGE       -- 기준하한나이
                   , CRTE_UPLMT_AGE     -- 기준상한나이 
                   , VL.RFVL_SN         AS RFVL_SN -- 참고치일련번호  
			FROM     GXEXDRFVL  VL  -- 일반검진검사결과참고치상세
                   , GZCDCODCD  CD  -- 건강검진처방코드
                   , GZCDCORCD ORCD -- 건강검진종합출력서식항목코드
			WHERE  VL.ORDR_CD = CD.ORDR_CD           
            AND    ORCD.ENG_NM = VL.ORDR_CD
            AND    ORCD.SYNTH_CD = 'RESULT-ITEM'
    ]]>
   </select>