개요
렌탈료 청구서, 렌탈료 납부서, 위약금 청구서, 렌탈료 납부서 총 4개의 메일 전송 기능이 필요합니다. 기존에 사용했던 기능에서 솔루션을 도입해 개선하는 건으로, 변경사항, 삭제사항, 추가사항을 정리하고 이메일 솔루션을 도입하였습니다.
미팅 내용
- 변경사항
- 렌탈료와 위약금 청구서 및 납입서 수기 업로드를 이메일로 전송으로 전환
- 여러 달을 합쳐서 보여주지 않고 1달을 기준으로 문서 작성
- 삭제사항
- 합산포함, 선납확인서 삭제
- 신규 추가사항
- 정기 전송 여부, 정기 전송 일자를 추가해 일 배치로 매일 전송
- 그룹 주문번호로 최대 15개까지 내용을 묶어서 보낼 수 있음
- 위약금 자동입력(프로시저 활용, 반환접수일 추가)
실행 프로세스 정리
청구서, 납입서 전송은 크게 [유효 주문번호 조회] -> [전송 데이터 조회] -> [전송] 과정을 거칩니다.
- 렌탈료 청구서
1. 그룹주문번호가 15개를 초과하는지 검사한다.
*DB에 그룹주문번호로 전송 할 대상을 조회하는데
'전송' 기능을 사용하는 화면에서 해당 주문번호를 주문그룹번호로 묶지 않더라도
해당 주문번호를 그룹주문번호에 합칠 수 있어야 한다.
2. 해당 월에 유효한 렌탈료 청구내역이 있는지 유효성 검사
*유효한 렌탈료 청구내역이 하나도 없다면 전송하지 않고 알림창을 띄운다.
3. 청구 테이블을 기준으로 각종 금액과 설치자 정보 조회, 채번한다.
4. 전송
- 렌탈료 납입서
1. 그룹주문번호가 15개를 초과하는지 검사한다.
2. 해당 월에 유효한 렌탈료 청구내역이 있는지 유효성 검사
*유효한 렌탈료 납입내역이 하나도 없다면 전송하지 않고 알림창을 띄운다.
3. 납입 테이블을 기준으로 각종 금액과 설치자 정보 조회, 채번한다.
4. 전송
- 위약금 청구서
1. 그룹주문번호가 15개를 초과하는지 검사한다.
2. 해당 월에 유효한 렌탈료 청구내역이 있는지 유효성 검사
*유효한 위약금 청구내역이 하나도 없다면 전송하지 않고 알림창을 띄운다.
3. 청구 테이블을 기준으로 각종 금액과 설치자 정보, 위약금, 연체료 조회, 채번한다.
4. 전송
- 위약금 납입서
1. 그룹주문번호가 15개를 초과하는지 검사한다.
2. 해당 월에 유효한 렌탈료 납입내역이 있는지 유효성 검사
*유효한 위약금 납입내역이 하나도 없다면 전송하지 않고 알림창을 띄운다.
3. 납입 테이블을 기준으로 각종 금액과 설치자 정보, 위약금, 연체료 조회, 채번한다.
4. 전송
문제 상황 해결(V1)
- 여러 주문번호의 청구, 납부내역서을 한번에 보내는 방법은?
법인의 경우 수십개의 주문번호를 가지고 있으며 청구서, 납입서를 동시에 신청합니다. 모든 제품마다 1장씩 가지고 있는 것은 비효율적인 방법입니다. 따라서, 최대 15개의 정보를 같이 담아서 보낼 수 있는 양식을 만들었습니다. 여러 주문번호가 같이 전송되어야 하는 기준을 잡아야 했습니다. 주문테이블(TB_ORD_B)과 결제테이블(TB_STLM_INF)은 주문번호를 기준으로 1:1 관계를 가지고 있습니다. 따라서 결제테이블에 '청구그룹주문번호(DMND_ORD_NO)'와 '납입그룹주문번호(PYM_ORD_NO)'를 위한 칼럼을 새로 생성했습니다.
각 주문번호에 대해 그룹주문번호(DMND_ORD_NO 혹은 PYM_ORD_NO)를 등록하면 해당 주문번호를 대표로 하는 그룹이 만들어집니다. 어떠한 주문번호라도 이메일 전송을 하면 대표 주문번호(DMND_ORD_NO 혹은 PYM_ORD_NO) 존재 여부를 확인해서 같은 그룹으로 묶인 최대 15개의 정보를 1장의 내용에 담아 이메일을 보냅니다
이메일 전송 화면에 있는 주문번호가 그룹주문번호에 포함이 되지 않을 수도 있으므로 UNION을 활용해 같은 그룹으로 묶어줍니다.
SELECT
ORD_NO,
FROM
TB_STLM_INF
WHERE
DMND_ORD_NO = #{DMND_ORD_NO}
UNION
SELECT
ORD_NO,
FROM
TB_STLM_INF
WHERE
ORD_NO = #{POPUP_ORD_NO}
ORDER BY
ORD_NO
- 한 달에 납입이 2번 있는 경우, 납입일자와 총 납부 금액을 어떻게 보여 줄 것인가?
청구와 달리, 납입은 1달에 고객이 1번 이상 할 수 있습니다. 따라서, 무조건 주문번호 1개당 1달의 납입정보는 1줄로 표시해야하는데 어떻게 해야 하나 고민했습니다. 미팅을 통해서 마지막 납입 날을 기준으로 납입방법, 청구일자, 납입일자를 계산하기로 했습니다. 따라서 주문번호로 GROUP BY를 해주고, 청구금액과 납입금액은 모든 내용을 SUM() 함수를 이용해서 더해주고, 납입방법, 청구일자, 납입일자는 MAX() KEEP(DENSE_RANK...)를 활용해서 대표값을 뽑아오도록 했습니다. 이로서 한달동안 납입한 횟수가 한번이든 여러번이든 관계없이 모든 데이터를 1개 로우로 반환합니다.
SELECT MAX(ID.IAMT_SEQ) AS IAMT_SEQ
, MAX(TP.PRDT_NM) AS PRDT_NM
, MAX(FN_CODE_NM('2070',ID.DMND_GBCD)) KEEP(DENSE_RANK LAST ORDER BY ID.IAMT_SEQ) AS DMND_GBNM
, MAX(ID.DMND_GBCD) KEEP(DENSE_RANK LAST ORDER BY ID.IAMT_SEQ) AS DMND_GBCD
, MAX(ID.IAMT_DT) KEEP(DENSE_RANK LAST ORDER BY ID.IAMT_SEQ) AS PMDATE_PYM_DT
, MAX(FN_CODE_NM('1150',ID.PYM_MTHD_CD)) KEEP(DENSE_RANK LAST ORDER BY ID.IAMT_SEQ) AS PYM_MTHD_NM
, SUM(ID.IAMT_AMT) AS IAMT_AMOUNT
, SUM(BD.REAL_DMND_AMT) AS AMOUNT
, MAX(BD.PMDATE_DT) KEEP(DENSE_RANK LAST ORDER BY ID.IAMT_SEQ) AS PMDATE_DMND_DT
, MAX(OB.ORD_NO) AS ORD_NO
FROM TB_ORD_B OB,
TB_PRDT TP,
TB_IAMT_D ID,
TB_DMND_IAMT DI,
TB_BILL_D BD,
TB_STLM_INF SI
WHERE ID.ORD_NO = #{ORD_NO}
AND OB.PRDT_CD = TP.PRDT_CD
AND OB.ORD_NO = ID.ORD_NO
AND OB.ORD_NO = SI.ORD_NO
AND ID.IAMT_SEQ = DI.IAMT_SEQ
AND DI.DMND_SEQ_NO = BD.DMND_SEQ_NO
AND SUBSTR(ID.IAMT_DT,0,6) BETWEEN #{S_DATE} AND #{E_DATE}
AND ID.DMND_GBCD = #{DMND_GBCD}
GROUP BY OB.ORD_NO
- 제품 반환 접수 기준으로 달라지는 위약금 계산식을 어떻게 할 것인가?
위약금을 조회하기 위해서 주문번호와 반환 접수일로 프로시저를 호출합니다. 한가지 주의 할 점은 반환 접수가 되면 일할 렌탈료가 변경되어 더이상 위약금 프로시저를 사용할 수 없습니다. 즉 반환 접수 이전에 위약금 조회 금액과 반환 접수 이후에 위약금 조회는 달라져 반환 접수 유무에 따라서 위약금 청구액 계산식이 달라져야 합니다. 현재 위약금 프로시저는 반환 접수 유무에 따라 계산식이 분기되어 있지 않습니다. 따라서 반환 접수를 완료한 고객이 자신의 위약금을 확인하고자 한다면 어떤 데이터를 보여주어야 하는지 분기를 만들어야 합니다.
이 문제를 해결하기 위해 반환 접수 시 메모에 생성되는 기존 금액 정보들을 활용했습니다. 반환 접수가 되는 시점에 단순히 메모에 저장했던 값들을 새로운 테이블에 관리하도록 개선했습니다.
정리하면 다음과 같습니다.
1. 제품이 반환이 되었다면, 가장 최신의 AS 반환 데이터를 위약금액으로 조회한다.
2. 제품이 반환이 되지 않았다면, 위약금 프로시저를 조회한다.
이 방식으로 기존에 상담원이 수기로 위약금 정보를 입력하던 작업이 자동화되었습니다.
실제 전송하는 위약금 납입서는 납입 여부까지 확인해야 합니다. 다이어그램은 아래와 같습니다.
- 반환 접수 유무 확인 및 데이터 조회 한방 쿼리
SELECT OB.ORD_NO
,ASB.AS_PRST_CD
,RH.*
FROM TB_ORD_B OB
,( SELECT ORD_NO
,MAX(ACPT_DT) KEEP(DENSE_RANK FIRST ORDER BY ACPT_DT DESC, ACPT_SEQ_NO DESC) AS ACPT_DT
,MAX(ACPT_SEQ_NO) KEEP(DENSE_RANK FIRST ORDER BY ACPT_DT DESC, ACPT_SEQ_NO DESC) AS ACPT_SEQ_NO
,MAX(RECEIPT_DT) KEEP(DENSE_RANK FIRST ORDER BY ACPT_DT DESC, ACPT_SEQ_NO DESC) AS RECEIPT_DT
,MAX(INST_AMT) KEEP(DENSE_RANK FIRST ORDER BY ACPT_DT DESC, ACPT_SEQ_NO DESC) AS INST_AMT
,MAX(USE_MM) KEEP(DENSE_RANK FIRST ORDER BY ACPT_DT DESC, ACPT_SEQ_NO DESC) AS USE_MM
,MAX(REMAIN_MM) KEEP(DENSE_RANK FIRST ORDER BY ACPT_DT DESC, ACPT_SEQ_NO DESC) AS REMAIN_MM
,MAX(REMAIN_AMT) KEEP(DENSE_RANK FIRST ORDER BY ACPT_DT DESC, ACPT_SEQ_NO DESC) AS REMAIN_AMT
,MAX(JOIN_AMT) KEEP(DENSE_RANK FIRST ORDER BY ACPT_DT DESC, ACPT_SEQ_NO DESC) AS JOIN_AMT
,MAX(EXPE_AMT) KEEP(DENSE_RANK FIRST ORDER BY ACPT_DT DESC, ACPT_SEQ_NO DESC) AS EXPE_AMT
,MAX(DC_AMT) KEEP(DENSE_RANK FIRST ORDER BY ACPT_DT DESC, ACPT_SEQ_NO DESC) AS DC_AMT
,MAX(GIFT_AMT) KEEP(DENSE_RANK FIRST ORDER BY ACPT_DT DESC, ACPT_SEQ_NO DESC) AS GIFT_AMT
,MAX(PENALTY_AMT) KEEP(DENSE_RANK FIRST ORDER BY ACPT_DT DESC, ACPT_SEQ_NO DESC) AS PENALTY_AMT
,MAX(LAST_DMND_AMT) KEEP(DENSE_RANK FIRST ORDER BY ACPT_DT DESC, ACPT_SEQ_NO DESC) AS LAST_DMND_AMT
,MAX(PENALTY_RATE) KEEP(DENSE_RANK FIRST ORDER BY ACPT_DT DESC, ACPT_SEQ_NO DESC) AS PENALTY_RATE
FROM TB_AS_RTN_H
GROUP BY ORD_NO
) RH
,( SELECT ORD_NO
,MAX(AS_PRST_CD) KEEP(DENSE_RANK FIRST ORDER BY ACPT_DT DESC, ACPT_SEQ_NO DESC) AS AS_PRST_CD
FROM TB_AS_B
WHERE AS_WRK_GBCD = 'R'
GROUP BY ORD_NO
) ASB
WHERE OB.ORD_NO = RH.ORD_NO(+)
AND OB.ORD_NO = ASB.ORD_NO(+)
AND ASB.AS_PRST_CD(+) IN ('01','02') --AS_PRST_CD 01:접수, 02:완료, 09:취소
AND OB.ORD_NO = #{ORD_NO}
제품 반환 시 해당 제품의 위약금 금액은 TB_AS_RTN_H에 저장합니다. 쿼리에서는 가장 최근 날짜와 최근 순번의 데이터를 조회합니다. 제품 반환인 경우는 AS테이블(TB_AS_B)에서 AS_WRK_GBCD가 'R' 인 경우입니다. 반환이 접수되거나 완료되는 경우를 기준으로 아우터 조인을 걸어 제품 반환 접수 여부를 확인함과 동시에 해당 데이터를 리턴합니다. 만약 제품 반환 접수가 되었다면 유효한 데이터를 리턴 할 것이고, 제품 반환 접수 이력이 없으면 주문번호를 제외한 칼럼이 null을 리턴합니다. 한 번의 쿼리로 반환 접수 여부와 데이터를 확인 할 수 있습니다. 테이블을 조인하는 경우 미리 데이터 로우를 줄이는 것이 성능 향상에 도움이 됩니다.
청구서 납입서 전송 일 배치 만들기
오라클 배치를 사용해 청구서와 납입서 전송 일 배치를 만들었습니다.
- 월말에 보내기 위한 쿼리문 개선
예를 들어 고객이 매달 31일에 청구서를 받기를 희망하면 30일이 마지막인 달과 2월을 처리할 수 없습니다. 만약 이번 달 마지막 날이 예정일보다 작으면 어떻게 처리해야 하는지 고민했습니다. 따라서 최초에 전송대상을 조회할 때, 오늘이 이번달의 마지막 날이면서 전송 예약일이 오늘보다 큰지 검사합니다.
SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'DD')) INTO V_TODAY_DAY FROM DUAL; --오늘 날짜
SELECT TO_NUMBER(TO_CHAR(LAST_DAY(TRUNC(SYSDATE)), 'DD')) INTO V_LAST_DAY FROM DUAL; --이번달 마지막 날짜
...
SELECT
ORD_NO
FROM
TB_STLM_INF
WHERE
DMND_YN = 'Y'
AND (
DMND_DT = TO_CHAR(SYSDATE, 'DD')
OR
(V_TODAY_DAY = V_LAST_DAY AND (TO_NUMBER(DMND_DT) > V_LAST_DAY))
)
AND ORD_NO = DMND_ORD_NO
오늘 날짜가 마지막 달의 날짜와 같으면서 예약 날짜는 마지막 날짜보다 크다면 전송이 가능합니다.
- 전송 로그 먼저 생성하기
최대 15개의 주문정보가 하나의 테이블에 들어오므로 최대 15번의 LOOP를 돌면서 if문에 해당 데이터를 할당합니다. 청구서 납입서를 보낸 이후에 로그 기록을 남기는데, 데이터를 할당하는 곳에 미리 로그를 기록하면 코드 양을 줄일 수 있습니다. 문제가 생겨 롤백하게 되면 당연히 로그로 INSERT 했던 데이터도 롤백됩니다.
LOOP
IF(V_SEQ = 1) THEN
...
ELSIF(V_SEQ = 15) THEN
END IF;
INSERT INTO TB_TMSG_INTF_H(...) VALUES (...);
END LOOP;
피드백과 개선사항(V1 -> V2)
- 그룹 전송 주문번호 선택 방식 개선으로 그룹 한번에 등록하기
기존에는 왼쪽 화면과 같이 팝업을 띄워서 1개씩 설정해야 했습니다. 불편한 점은 10개를 그룹 전송으로 묶는 경우 10번 같은 작업을 반복해야 합니다.
하지만 개선 후에는 오른쪽 화면과 같이 대표 그룹 주문번호 팝업에서 원하는 모든 주문번호를 추가하여 한번에 등록할 수 있습니다. 위에는 원하는 주문번호를 선택할 수 있는 화면이고 아래는 그룹 전송에 추가된 주문번호입니다.
- 발송이력을 추가하여 고객 응대 효율성 증대
청구서/납입서가 필요한 고객들에게 요청이 올때마다 전송하는 작업을 자동화하였으며 보다 효율적인 응대와 안내를 위해서 발송 이력을 추가했습니다. 상담원은 별도로 이력을 조회하지 않아도 제품번호만 클릭하면 이력을 볼 수 있습니다.
단순하게 언제 발송했는지 뿐만 아니라, 자동 발송 등록일, 해제일, 재발송 여부 등의 케이스를 나누었습니다. 따라서 전송, 재전송, 자동발송 취소, 팝업 업데이트, 배치전송 총 5가지의 경우에 로그 기록을 남겼습니다. 이를 통해 상담원은 고객들의 이력 조회나 요청에 빠르고 쉽게 응대할 수 있습니다.
- 재발송 개발, 자동발행 취소 개발로 업무 편의성 향상
혹시 모를 모듈 오류상황이나 고객 요청으로 같은 내용을 다시 전송해야 하는 경우가 있습니다. 이를 대비하고자 과거에 보냈던 청구서, 납입서 그대로 다시 전송할 수 있도록 재발송을 만들었습니다. 기존의 데이터에 새로운 순번을 부여해서 보내면 그대로 내용이 다시 전달됩니다.
또한 만약 15개의 그룹 주문번호가 자동발송으로 묶여 있는데 해제를 하려면 관련 설정 팝업창에서 해결하지 않고도 바로 목록에서 취소할 수 있는 기능을 만들었습니다. 상담원들이 자주 사용하는 기능의 편의성을 개선하여 고객 응대 속도를 높였습니다.
회고
청구와 납입 프로세스를 다루므로 초기에 도메인을 파악하느라 힘들었습니다. 또한 일을 요청한 현업이 제대로 내용을 정리하지 않은 상태에서 요청하여 정말 많이 미팅을 해야만 했습니다.
많은 미팅으로 중간중간에 검증과 코드 로직 변경이 많았습니다. 항상 이 기능이 왜 필요한지, 과연 목표를 달성하기 위해서 이 방법이 최선인지 고민하고 질문하여 올바른 방향을 잡기 위해 노력했습니다.
또한 기존 코드를 변경하는 작업이기에 선행 파악이 먼저 필요했습니다. 항상 먼저 만들어진 코드가 완벽한 것은 아닙니다. 이중에 내가 활용할 핵심 로직은 무엇인지, 혹시 잘못된 로직들은 있는지 비판적으로 확인해야 합니다. 기존 기능이 복잡한 편이였는데 이전에 만들었던 분이 퇴사하는 바람에 다른 팀원들과 현업들을 통해 정보를 수집해야 했습니다. 신규 기능을 만드는 것이 아니므로 코딩을 하기 전에 이전 코드를 충분히 숙지하는 연습이 필요하다고 느꼈습니다.
'문제 해결, 기술 비교 > 실무 업무 회고' 카테고리의 다른 글
| UNION을 CASE WHEN THEN으로 개선하기 (0) | 2023.01.09 |
|---|---|
| 할인 쿠폰 개선하기 (0) | 2022.12.28 |
| NICE API 일시불 취소 개발하기 (0) | 2022.12.09 |
| 스케줄링에 어떤 기술을 사용할까?(@Scheduled vs Spring Batch vs Quartz) (0) | 2022.11.10 |
| Jenkins로 CI/CD 구축하기 (0) | 2022.06.02 |
