-- 데여금 프로시저
JOB_BRCASH_MOVE_0800
INSERT INTO CDMS_BRCASH_MOVE_SCHED (CMS_NAME, FROM_BR_NO, )
SELECT BR_NAME FROM CDMS_BRANCH B WHERE B.BR_NO = 'Q129';
SELECT * FROM CDMS_SYSCODE WHERE DT_NM LIKE '%미납%';
SELECT * FROM CDMS_SYSCODE WHERE DT_CD = 'M1';
SELECT * FROM CDMS_SYSCODE WHERE SY_CD = 'BT';-- WK_SNUM => 복호화 캐스팅
CAST (pls_decrypt_b64_id (WK_SNUM, 35438101) AS VARCHAR2 (14))
SELECT * FROM CDMS_WORKER$$; -- 이거와
SELECT * FROM CDMS_WORKER_ARCHIVED c ; -- 이두 테이블은 컬럼들이 동일함.
SELECT * FROM CDMS_WORKER;
-- 복호화를 해서 기존값과 비요하여 컬럼값들을 가져옴.
SELECT *
FROM CDMS_WORKER$$
WHERE 1=1
AND CAST(pls_decrypt_b64_id(WK_SNUM, 35438101) AS VARCHAR2(14))
= NVL('8006111110021', CAST(pls_decrypt_b64_id(WK_SNUM, 35438101) AS VARCHAR2(14));
AND WK_SNUM = CAST(pls_decrypt_b64_id(WK_SNUM, '${WK_SNUM}') AS VARCHAR2(14))
이게 정확함.
NVL('${WK_SNUM}', WK_SNUM)
-- 공부 기록용 일부러 유니크키 만들기 위한 쿼리도 포함
7:32
------------- 타회사 에서 차단한 자기사-------------
SELECT * FROM
(
SELECT ROWNUM AS RNUM, T.* FROM
(
SELECT
BR_NAME 소속지사명
, W_BR_NO 소속지사$W_BR_NO#S
, WK_REAL_NAME 기사명$WK_REAL_NAME#R
, W_WK_SABUN 기사사번$W_WK_SABUN#S
, WK_HP 단말기번호$WK_HP#R#S
, HD_NAME 차단본사명
, W_HD_NO 차단본사코드$W_HD_NO#S
, C1 차단지사명
, BL_BR_NO 차단지사코드$BL_BR_NO#S
, CALLCENTER 차단콜센터코드$CALLCENTER
, BR_TEL 상황실번호$BR_TEL
, IN_ID 차단ID
, C2 차단구분
, MEMO 차단사유
, C3 차단일
, C4 차단해제
-- , BL_BR_NO
-- , BLOCK_TYPE
-- , BL_WK_SABUN
FROM (
SELECT
(CASE
WHEN BLOCK_TYPE IN ('1','2','3','6','8','9') THEN (SELECT BR_NAME FROM CDMS_BRANCH WHERE BR_NO=bl.BR_NO)
WHEN BLOCK_TYPE IN ('4','5','7') THEN (SELECT HD_NAME FROM CDMS_HEAD WHERE HD_NO=bl.BR_NO) ELSE '' END) C1,
bl.BR_NO BL_BR_NO,
DECODE(BLOCK_TYPE,'1','기사차단','2','지사차단','3','지사간차단','4','본사간차단','5','본사기사차단','6','기사주민차단','7','기사주민본사차단','8','기사콜센터차단','9','콜센터주민차단') C2,
BLOCK_TYPE,
w.WK_SABUN W_WK_SABUN,
WK_REAL_NAME,
wk_hp,
wb.br_name,
bl.in_id,
bl.memo,
TO_CHAR(bl.in_date,'YYYY-MM-DD') C3,
TO_CHAR(bl.to_date,'YYYY-MM-DD') C4,
bl.WK_SABUN BL_WK_SABUN,
wb.hd_no W_HD_NO,
W.BR_NO W_BR_NO,
(CASE
WHEN BLOCK_TYPE IN ('4','5','7') THEN (SELECT HD_NAME FROM CDMS_HEAD WHERE HD_NO=bl.BR_NO) ELSE ch.HD_NAME END) HD_NAME,
(CASE
WHEN BLOCK_TYPE IN ('4','5','7') THEN bl.BR_NO ELSE ch.HD_NO END) HD_NO,
wb.BR_TEL BR_TEL,
wb.CALLCENTER,
bl.BR_NO||bl.WK_sabun||To_char(bl.in_date,'YYYYMMDDHH24MISS') SEQ$SEQ#K
FROM cdms_block_list bl, CDMS_WORKER w, cdms_branch wb, CDMS_HEAD ch
WHERE bl.WK_SABUN=w.WK_SABUN
AND w.br_no=wb.br_no
AND w.HD_NO = ch.HD_NO
AND WK_STATUS != '50'
UNION ALL
SELECT
(CASE
WHEN BLOCK_TYPE IN ('1','2','3','6','8','9') THEN (SELECT BR_NAME FROM CDMS_BRANCH WHERE BR_NO=bl.BR_NO)
WHEN BLOCK_TYPE IN ('4','5','7') THEN (SELECT HD_NAME FROM CDMS_HEAD WHERE HD_NO=bl.BR_NO) ELSE '' END) C1,
bl.BR_NO BL_BR_NO,
DECODE(BLOCK_TYPE,'1','기사차단','2','지사차단','3','지사간차단','4','본사간차단','5','본사기사차단','6','기사주민차단','7','기사주민본사차단','8','기사콜센터차단','9','콜센터주민차단') C2,
BLOCK_TYPE,
w.WK_SABUN W_WK_SABUN,
WK_REAL_NAME,
wk_hp,
wb.br_name,
bl.in_id,
bl.memo,
TO_CHAR(bl.in_date,'YYYY-MM-DD') C3,
TO_CHAR(bl.to_date,'YYYY-MM-DD') C4,
bl.WK_SABUN BL_WK_SABUN,
wb.hd_no W_HD_NO,
W.BR_NO W_BR_NO,
(CASE
WHEN BLOCK_TYPE IN ('4','5','7') THEN (SELECT HD_NAME FROM CDMS_HEAD WHERE HD_NO=bl.BR_NO) ELSE ch.HD_NAME END) HD_NAME,
(CASE
WHEN BLOCK_TYPE IN ('4','5','7') THEN bl.BR_NO ELSE ch.HD_NO END) HD_NO,
wb.BR_TEL BR_TEL,
wb.CALLCENTER,
bl.BR_NO||bl.WK_sabun||To_char(bl.in_date,'YYYYMMDDHH24MISS') SEQ$SEQ#K
FROM cdms_block_list bl, CDMS_WORKER w, cdms_branch wb, CDMS_HEAD ch
WHERE bl.WK_SABUN=w.WK_SNUM_HASH
AND w.br_no=wb.br_no
AND w.HD_NO = ch.HD_NO
AND WK_STATUS != '50'
ORDER BY C3 DESC
)
) T
WHERE 1 = 1
)
WHERE RNUM BETWEEN 1 AND 300;
SELECT CALLCENTER, BR_NO FROM cdms_branch;
SELECT * FROM cdms_block_list WHERE BLOCK_TYPE ='8';
SELECT * FROM
(
SELECT ROWNUM AS RNUM, T.* FROM
(
SELECT
W_HD_NO 기사소속본사$W_HD_NO#S
, W_BR_NO 기사소속지사$W_BR_NO#S
, C1 회사명
, W_WK_SABUN 기사사번$W_WK_SABUN#S
, BR_WK_NUM 자체사번
, WK_NAME 기사명$WK_NAME#R
, WK_HP 단말기번호$WK_HP#R#S
, BR_NAME 차단한지사명
, C2 차단구분
, MEMO 차단사유
, IN_ID 차단ID
, C3 차단일
, C4 차단해제
-- , BL_BR_NO
-- , BLOCK_TYPE
-- , BL_WK_SABUN
FROM (
SELECT
(CASE
WHEN BLOCK_TYPE IN ('1','2','3','6','8','9') THEN (SELECT BR_NAME FROM CDMS_BRANCH WHERE BR_NO=bl.BR_NO)
WHEN BLOCK_TYPE IN ('4','5','7') THEN (SELECT HD_NAME FROM CDMS_HEAD WHERE HD_NO=bl.BR_NO) ELSE '' END) C1,
bl.BR_NO BL_BR_NO,
DECODE(BLOCK_TYPE,'1','기사차단','2','지사차단','3','지사간차단','4','본사간차단','5','본사기사차단','6','기사주민차단','7','기사주민본사차단','8','기사콜센터차단','9','콜센터주민차단') C2,
BLOCK_TYPE,
w.WK_SABUN W_WK_SABUN,
BR_WK_NUM,
WK_NAME,
wk_hp,
wb.br_name,
bl.in_id,
bl.memo,
TO_CHAR(bl.in_date,'YYYY-MM-DD') C3,
TO_CHAR(bl.to_date,'YYYY-MM-DD') C4,
bl.WK_SABUN BL_WK_SABUN,
wb.hd_no W_HD_NO,
W.BR_NO W_BR_NO
FROM cdms_block_list bl, CDMS_WORKER w, cdms_branch wb
WHERE bl.WK_SABUN=w.WK_SABUN
AND w.br_no=wb.br_no
AND WK_STATUS != '50'
UNION ALL
SELECT
(CASE
WHEN BLOCK_TYPE IN ('1','2','3','6','8','9') THEN (SELECT BR_NAME FROM CDMS_BRANCH WHERE BR_NO=bl.BR_NO)
WHEN BLOCK_TYPE IN ('4','5','7') THEN (SELECT HD_NAME FROM CDMS_HEAD WHERE HD_NO=bl.BR_NO) ELSE '' END) C1,
bl.BR_NO BL_BR_NO,
DECODE(BLOCK_TYPE,'1','기사차단','2','지사차단','3','지사간차단','4','본사간차단','5','본사기사차단','6','기사주민차단','7','기사주민본사차단','8','기사콜센터차단','9','콜센터주민차단') C2,
BLOCK_TYPE,
w.WK_SABUN W_WK_SABUN,
BR_WK_NUM,
WK_NAME,
wk_hp,
wb.br_name,
bl.in_id,
bl.memo,
TO_CHAR(bl.in_date,'YYYY-MM-DD') C3,
TO_CHAR(bl.to_date,'YYYY-MM-DD') C4,
bl.WK_SABUN BL_WK_SABUN,
wb.hd_no W_HD_NO,
W.BR_NO W_BR_NO,
BR_NO||WK_sabun||To_char(in_date,'YYYYMMDDHH24MISS') SEQ$SEQ#K
FROM cdms_block_list bl, CDMS_WORKER w, cdms_branch wb
WHERE bl.WK_SABUN=w.WK_SNUM_HASH
AND w.br_no=wb.br_no
AND WK_STATUS != '50'
ORDER BY C3 DESC
)
) T
WHERE 1 = 1
)
WHERE RNUM BETWEEN 1 AND 300;
SELECT BR_NO||WK_sabun||To_char(in_date,'YYYYMMDDHH24MISS'), COUNT()
FROM cdms_block_list bl
GROUP BY BR_NO||WK_sabun||in_date ORDER BY 2 DESC
SELECT DISTINCT *
FROM cdms_block_list bl
WHERE 1=1
-- AND BR_NO ='B883'
-- AND WK_SABUN ='Q54332'
AND IN_ID = 'cb7970'
SELECT
FROM cdms_block_list bl, CDMS_WORKER w, cdms_branch wb
WHERE BR_NO||WK_sabun||To_char(in_date,'YYYYMMDDHH24MISS') = #{id}
--- 상세보기 쿼리---
SELECT
WK_REAL_NAME 기사명
, WK_HP 단말기번호
, SEQ$SEQ#K
FROM (
SELECT
WK_REAL_NAME,
wk_hp,
bl.BR_NO||bl.WK_sabun||To_char(bl.in_date,'YYYYMMDDHH24MISS') SEQ$SEQ#K
FROM cdms_block_list bl, CDMS_WORKER w, cdms_branch wb, CDMS_HEAD ch
WHERE bl.WK_SABUN=w.WK_SABUN
AND w.br_no=wb.br_no
AND w.HD_NO = ch.HD_NO
AND WK_STATUS != '50'
AND bl.BR_NO||bl.WK_sabun||To_char(bl.in_date,'YYYYMMDDHH24MISS') = 'A5106Q3958720250730103652'
UNION ALL
SELECT
WK_REAL_NAME,
wk_hp,
bl.BR_NO||bl.WK_sabun||To_char(bl.in_date,'YYYYMMDDHH24MISS') SEQ$SEQ#K
FROM cdms_block_list bl, CDMS_WORKER w, cdms_branch wb, CDMS_HEAD ch
WHERE bl.WK_SABUN=w.WK_SNUM_HASH
AND w.br_no=wb.br_no
AND w.HD_NO = ch.HD_NO
AND WK_STATUS != '50'
AND bl.BR_NO||bl.WK_sabun||To_char(bl.in_date,'YYYYMMDDHH24MISS') = 'A5106Q3958720250730103652'
);
SELECT * FROM USER_DB_LINKS; -- 현재 스키마(로그인 계정)의 시퀀스 목록 확인
SELECT SEQUENCE_NAME
FROM USER_SEQUENCES;
SELECT SEQUENCE_NAME
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME LIKE '%BRCASH%';
-- 특정 시퀀스의 현재 값, 증가값 등 상세정보 보기
SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, LAST_NUMBER
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = 'BRCASH_MOVE_ONCE_SEQ';-- 본인 인증 받은 날짜시간까지 포함.
SELECT
DUP_INFO,
MEM_NAME,
BIRTH_DATE,
MAX(AUTH_TYPE) KEEP (DENSE_RANK FIRST ORDER BY IN_DATE DESC) AS AUTH_TYPE,
MAX(IN_DATE) KEEP (DENSE_RANK FIRST ORDER BY IN_DATE DESC) AS LATEST_AUTH_DATE -- :작은_파란색_다이아몬드: 추가 컬럼
FROM CDMS_NAMECHECK_NICE_LOG
WHERE DUP_INFO IS NOT NULL AND mem_name ='이용훈'
GROUP BY DUP_INFO, MEM_NAME, BIRTH_DATE
UNION ALL
SELECT
DUP_INFO,
MEM_NAME,
BIRTH_DATE,
MAX(AUTH_TYPE) KEEP (DENSE_RANK FIRST ORDER BY IN_DATE DESC) AS AUTH_TYPE,
MAX(IN_DATE) KEEP (DENSE_RANK FIRST ORDER BY IN_DATE DESC) AS LATEST_AUTH_DATE
FROM CDMS_NAMECHECK_NICE_LOG@CDMS_OTHER
WHERE DUP_INFO IS NOT NULL AND mem_name ='이용훈'
GROUP BY DUP_INFO, MEM_NAME, BIRTH_DATE;
---cid 기사정보 조회시 본인인증 날짜 까지 추가한 컬럼 이때 주민번호에서 생일의 년월일 까지 일치해서 조회 하는게 중요.빼면 절대 안됨.
select
DECODE(n.AUTH_TYPE, 'd', '기사앱인증','m','상담원인증') NAMECHECK_SOURCE -- 본인인증구분
, TO_CHAR(n.LATEST_AUTH_DATE, 'yyyy-mm-dd hh24:mi:ss') AS NAMECHECK_TIME -- 본인인증시간
,WK.wk_hp
FROM CDMS_WORKER WK
LEFT JOIN CDMS_WORKER_TODAY WT ON WK.WK_SABUN = WT.WK_SABUN
LEFT JOIN CDMS_CAREPLUS CP ON WK.WK_SABUN = CP.WK_SABUN
LEFT JOIN (
SELECT
DUP_INFO,
MEM_NAME,
BIRTH_DATE,
MAX(AUTH_TYPE) KEEP (DENSE_RANK FIRST ORDER BY IN_DATE DESC) AS AUTH_TYPE,
MAX(IN_DATE) KEEP (DENSE_RANK FIRST ORDER BY IN_DATE DESC) AS LATEST_AUTH_DATE -- 추가 컬럼
FROM CDMS_NAMECHECK_NICE_LOG
WHERE DUP_INFO IS NOT NULL
GROUP BY DUP_INFO, MEM_NAME, BIRTH_DATE
UNION ALL
SELECT
DUP_INFO,
MEM_NAME,
BIRTH_DATE,
MAX(AUTH_TYPE) KEEP (DENSE_RANK FIRST ORDER BY IN_DATE DESC) AS AUTH_TYPE,
MAX(IN_DATE) KEEP (DENSE_RANK FIRST ORDER BY IN_DATE DESC) AS LATEST_AUTH_DATE -- 추가 컬럼
FROM CDMS_NAMECHECK_NICE_LOG@CDMS_OTHER
WHERE DUP_INFO IS NOT NULL
GROUP BY DUP_INFO, MEM_NAME, BIRTH_DATE
) N ON WK.WK_HID=n.DUP_INFO AND WK.WK_REAL_NAME = N.MEM_NAME AND SUBSTR(WK.WK_SNUM,1,6) = SUBSTR(N.BIRTH_DATE,3,8)
WHERE n.MEM_NAME='이용훈' AND WK.wk_hp ='01048598298';
-- 밖에서 한번에 max keep 해서 조회 그럼 둘이합친 테이블에서 가장 최신 정보가 나옴.
---cid 기사정보 조회시 본인인증 날짜 까지 추가한 컬럼 이때 주민번호에서 생일의 년월일 까지 일치해서 조회 하는게 중요.빼면 절대 안됨.
select
DECODE(n.AUTH_TYPE, 'd', '기사앱인증','m','상담원인증') NAMECHECK_SOURCE -- 본인인증구분
,TO_CHAR(n.LATEST_AUTH_DATE, 'yyyy-mm-dd hh24:mi:ss') AS NAMECHECK_TIME -- 본인인증시간
,WK.wk_hp
,WK.WK_SABUN
,n.DUP_INFO
FROM CDMS_WORKER WK
LEFT JOIN CDMS_WORKER_TODAY WT ON WK.WK_SABUN = WT.WK_SABUN
LEFT JOIN CDMS_CAREPLUS CP ON WK.WK_SABUN = CP.WK_SABUN
LEFT JOIN (
SELECT DUP_INFO, MEM_NAME, BIRTH_DATE
,MAX(AUTH_TYPE) KEEP (DENSE_RANK FIRST ORDER BY IN_DATE DESC) AS AUTH_TYPE
,MAX(IN_DATE) KEEP (DENSE_RANK FIRST ORDER BY IN_DATE DESC) AS LATEST_AUTH_DATE
FROM (
SELECT DUP_INFO, MEM_NAME, BIRTH_DATE, AUTH_TYPE, IN_DATE
FROM CDMS_NAMECHECK_NICE_LOG
WHERE DUP_INFO IS NOT NULL
UNION ALL
SELECT DUP_INFO, MEM_NAME, BIRTH_DATE, AUTH_TYPE, IN_DATE
FROM CDMS_NAMECHECK_NICE_LOG@CDMS_OTHER
WHERE DUP_INFO IS NOT NULL)
GROUP BY DUP_INFO, MEM_NAME, BIRTH_DATE
) N ON WK.WK_HID=n.DUP_INFO AND WK.WK_REAL_NAME = N.MEM_NAME AND SUBSTR(WK.WK_SNUM,1,6) = SUBSTR(N.BIRTH_DATE,3,8)
WHERE WK.wk_hp ='01066114990#2';
-- 인증시간추가 쿼리 결과 둘이 같음.
-- MC0GCCqGSIb3DQIJAyEAErZc28NGun6xEoHjYHh5KHKcveNeieDH7O+ceJ3Oj7I=
-- 예전 원본쿼리
-- MC0GCCqGSIb3DQIJAyEAErZc28NGun6xEoHjYHh5KHKcveNeieDH7O+ceJ3Oj7I=
-- 3번재 수정 쿼리 결과
-- MC0GCCqGSIb3DQIJAyEAErZc28NGun6xEoHjYHh5KHKcveNeieDH7O+ceJ3Oj7I=
테이블 권한 관련 쿼리 기록
- PostgreSQL에서 함수의 실행권한과 함수내부에 사용되는 테이블 권한이 갑자기 없어 api-map호출이 정상 작동하지 않아 테이블 권한 부여하여 오류 수정함. 관련 쿼리 기록함.
SELECT * FROM gis.find_place(35.1595,126.8526);
-- 현재 DB접속 유저 확인
SELECT current_user, session_user;
SELECT current_user;
gis.find_address();
-- 테이블 권한 확인
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'gis.map_popular_places';
-- 테이블 이 속한 스키마 확인쿼리
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name = 'map_popular_places';
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name ILIKE '%regular_places%';
select * from map_regular_places;
-- 그 스키마에 USAGE 권한 부여
GRANT USAGE ON SCHEMA gis TO etl_user;
GRANT USAGE ON SCHEMA gis TO etl_user;
commit;
--- 모든 권한이 제대로 적용되었는지 확인
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'map_popular_places';
SELECT grantee, privilege_type, table_schema
FROM information_schema.role_table_grants
WHERE table_name = 'map_regular_places'
-- 권한 부여 쿼리
GRANT SELECT ON TABLE gis.map_popular_places TO etl_user;
GRANT SELECT ON TABLE gis.map_regular_places TO etl_user;
-- 스키마 접근 권한도 추가
GRANT USAGE ON SCHEMA gis TO etl_user;
commit;취소된 오더 접수번호 조회 상세보기에서 카카오기사 재배차 후 완료처리 수수료는 차감되었으나 수주본사/지사/사번 내역 파악 기록.
- 오더수정 => 상세보기 조회시 운행 상태일 경우는 재배차 버튼을 표기 되지 않도록 수정 배포
SELECT WK_NAME FROM "CDMS_WORKER$$" c WHERE c.WK_SABUN ='M00001'
--- 오더 수정 조회 쿼리 147960015: 접수 번호 <2025 12 04>
SELECT ROWNUM AS RNUM, T.* FROM
(
SELECT
CONF_SLIP 접수번호$CONF_SLIP#K#S
, TO_CHAR(IN_DATE, 'yyyy-mm-dd hh24:mi:ss') 입력시간$IN_DATE#D#S
, NVL(CONF_HEAD, ' ') 발주본사$CONF_HEAD#U
, NVL(CONF_BRCH, ' ') 발주지사$CONF_BRCH#U
, NVL(WK_HEAD, ' ') 수주본사$WK_HEAD#U
, NVL(WK_BRCH, ' ') 수주지사$WK_BRCH#U
, NVL(CONF_WK_SABUN, ' ') 기사사번$CONF_WK_SABUN#S#C
, CONF_WORKER 기사이름$CONF_WORKER#R
, CONF_CUST_TEL 고객번호$CONF_CUST_TEL#S#R
, NVL(CONF_USER, ' ') 고객명$CONF_USER#U
, NVL(CONF_STATUS, ' ') 접수상태$CONF_STATUS#C
, NVL(CONF_INFO, ' ') 적요$CONF_INFO#U
, NVL(CONF_INFO2, ' ') 적요2$CONF_INFO2#U
, NVL(CONF_CREASON, ' ') 취소사유$CONF_CREASON#U
, NVL(KD_SHARE_CALL_YN, ' ') 카카오공유$KD_SHARE_CALL_YN#U
, NVL(PAY_METHOD, ' ') 결제방법$PAY_METHOD#U
, NVL(TO_CHAR(CONF_CHARGE), ' ') 요금$CONF_CHARGE#U
, NVL(TO_CHAR(POST_CHARGE), ' ') 후불요금$POST_CHARGE#U
, NVL(TO_CHAR(SUPPORT_CHARGE), ' ') 지원금$SUPPORT_CHARGE#U
, NVL(TO_CHAR(OTHER_CHARGE), ' ') 기타요금$OTHER_CHARGE#U
, NVL(CONF_AREA, ' ') 출발시도$CONF_AREA#U
, NVL(CONF_AREA2, ' ') 출발시군구$CONF_AREA2#U
, NVL(CONF_AREA3, ' ') 출발읍면동$CONF_AREA3#U
, NVL(CONF_AREA4, ' ') 출발세부지명$CONF_AREA4#U
, NVL(CONF_AREA5, ' ') 출발세부설명$CONF_AREA5#U
, NVL(CONF_EDAREA, ' ') 도착시도$CONF_EDAREA#U
, NVL(CONF_EDAREA2, ' ') 도착시군구$CONF_EDAREA2#U
, NVL(CONF_EDAREA3, ' ') 도착읍면동$CONF_EDAREA3#U
, NVL(CONF_EDAREA4, ' ') 도착세부지명$CONF_EDAREA4#U
, NVL(CONF_EDAREA5, ' ') 도착세부설명$CONF_EDAREA5#U
, NVL(TO_CHAR(UP_DATE, 'yyyy-mm-dd hh24:mi:ss'), ' ') 수정시간$UP_DATE
, NVL(IN_ID, ' ') 입력자$IN_ID
, NVL(UP_ID, ' ') 수정자$UP_ID
, NVL(KEY_NUMBER, ' ') 대표번호$KEY_NUMBER
, CASE WHEN CONF_BAR = '0' THEN '일반'
WHEN CONF_BAR = '1' THEN '업소'
WHEN CONF_BAR = '2' THEN '업소후불'
WHEN CONF_BAR = '3' THEN '법인'
WHEN CONF_BAR = '4' THEN '주말골퍼'
ELSE ' '
END 고객구분$CONF_BAR
, NVL(CB_CODE, ' ') 법인코드$CB_CODE#U
, NVL(CB_CORP_NAME, ' ') 법인회사명$CB_CORP_NAME#U
, NVL(CB_DEPT_NAME, ' ') 법인부서명$CB_DEPT_NAME#U
FROM CDMS_A01
WHERE 1=1
AND CONF_SLIP = NVL('147960015', CONF_SLIP)
AND NVL(CONF_CUST_TEL, '-') = NVL('', NVL(CONF_CUST_TEL, '-'))
AND NVL(CONF_WK_SABUN, '기사NO') = NVL('', NVL(CONF_WK_SABUN, '기사NO'))
) T
WHERE 1 = 1;
SELECT * FROM CDMS_A01_HIS c;
SELECT in_date, UP_DATE, count(*) FROM CDMS_A01 WHERE CONF_SLIP = '147960015' AND CONF_STATUS = '2';
SELECT * FROM CDMS_PROC_DEBUG_LOG_OLD_1 d WHERE module = 'MNG_ORDER.RE_BAECHA';
--- 오더조회 쿼리 <2025 12 05>
SELECT ROWNUM AS RNUM, T.* FROM
(
SELECT
CONF_SLIP 접수번호$CONF_SLIP#K#S
, CONF_HEAD 발주본사$CONF_HEAD#S
, CONF_BRCH 발주지사$CONF_BRCH#S
, WK_HEAD 수주본사$WK_HEAD
, WK_BRCH 수주지사$WK_BRCH
, CONF_WK_SABUN 사번$CONF_WK_SABUN#S
, CONF_WORKER 이름$CONF_WORKER#R
, (SELECT DT_NM FROM CDMS_SYSCODE WHERE SY_CD = 'AS' AND DT_CD = CONF_STATUS) as 상태$CONF_STATUS
, KEY_NUMBER 대표번호
, (SELECT BR_TEL FROM CDMS_BRANCH WHERE BR_NO=CONF_BRCH) 발주지사_상황실번호
, DECODE(BAECHA_TYPE, '0', '기사배차', '1', '강제배차', '2', '문자배차', '3','차감문자배차') as 배차타입$BAECHA_TYPE
, CONF_CREASON 취소사유$CONF_CREASON
, CONF_AREA5 출발지$CONF_AREA5#S#R
, CONF_EDAREA5 도착지$CONF_EDAREA5#S#R
, DECODE(SUBSTR(A.DRIVER_OPTION,3,1), 'y', 'y','-') "탁송여부"
, CONF_CUST_TEL 고객번호$CONF_CUST_TEL#S#R
, (SELECT DT_NM FROM CDMS_SYSCODE WHERE SY_CD = 'PY' AND DT_CD = PAY_METHOD) as 결제방법$PAY_METHOD
, CONF_INFO2 적요2$CONF_INFO2#S
, A.IN_DATE 입력일자$IN_DATE#D#S
, FIRST_IN_DATE 최초접수시간$FIRST_IN_DATE
, BAECHA_TIME 배차시간$BAECHA_TIME
, ODB_WK_HP 카카오단말번호$ODB_WK_HP#S
, PARTNER_CALL_ID KM_위탁콜_ID$KM_ID#S
, NVL(( SELECT 'P' FROM CDMS_BRANCH_ENV E
WHERE E.BR_NO = A.CONF_BRCH AND E.MAIN_BRANCH_YN = 'y'
AND EVENT_WEEK LIKE '%'||TO_CHAR(A.FIRST_IN_DATE-0.375, 'd')||'%'
AND ( (E.EVENT_STT < E.EVENT_END AND TO_CHAR(A.FIRST_IN_DATE , 'HH24MISS') BETWEEN E.EVENT_STT||'00' AND E.EVENT_END||'00' )
OR (E.EVENT_STT >= E.EVENT_END AND ( NOT TO_CHAR(A.FIRST_IN_DATE , 'HH24MISS') BETWEEN E.EVENT_END||'00' AND E.EVENT_STT||'00' OR TO_CHAR(A.FIRST_IN_DATE, 'HH24MISS')='210000' ) ) )
AND (
A.FIRST_IN_DATE BETWEEN TO_DATE('2024-12-11 09:00:00', 'yyyy-mm-dd hh24:mi:ss') AND TO_DATE('2024-12-15 01:00:00', 'yyyy-mm-dd hh24:mi:ss')
OR
A.FIRST_IN_DATE BETWEEN TO_DATE('2024-12-18 09:00:00', 'yyyy-mm-dd hh24:mi:ss') AND TO_DATE('2024-12-22 01:00:00', 'yyyy-mm-dd hh24:mi:ss')
OR
A.FIRST_IN_DATE BETWEEN TO_DATE('2024-12-26 09:00:00', 'yyyy-mm-dd hh24:mi:ss') AND TO_DATE('2024-12-29 01:00:00', 'yyyy-mm-dd hh24:mi:ss')
)
AND A.BAECHA_TYPE NOT IN ('1','2','3')
), ' ') 포인트콜조건충족$IS_EVENT_YN
FROM CDMS_A01 A
WHERE 1=1
AND CONF_HEAD = NVL('', CONF_HEAD)
AND CONF_BRCH = NVL('', CONF_BRCH)
AND NVL(ODB_WK_HP, '-') = NVL('', NVL(ODB_WK_HP, '-'))
AND NVL(PARTNER_CALL_ID, '-') = NVL('', NVL(PARTNER_CALL_ID, '-'))
AND CONF_SLIP like '147960015'||'%'
ORDER BY a.IN_DATE DESC
) T
WHERE 1 = 1;
-- 좀더 간략하게 조회
SELECT
CONF_SLIP 접수번호$CONF_SLIP#K#S
, CONF_HEAD 발주본사$CONF_HEAD#S
, CONF_BRCH 발주지사$CONF_BRCH#S
, WK_HEAD 수주본사$WK_HEAD
, WK_BRCH 수주지사$WK_BRCH
, CONF_WK_SABUN 사번$CONF_WK_SABUN#S
, CONF_WORKER 이름$CONF_WORKER#R
, (SELECT DT_NM FROM CDMS_SYSCODE WHERE SY_CD = 'AS' AND DT_CD = CONF_STATUS) as 상태$CONF_STATUS
, KEY_NUMBER 대표번호
, (SELECT BR_TEL FROM CDMS_BRANCH WHERE BR_NO=CONF_BRCH) 발주지사_상황실번호
, DECODE(BAECHA_TYPE, '0', '기사배차', '1', '강제배차', '2', '문자배차', '3','차감문자배차') as 배차타입$BAECHA_TYPE
, CONF_CREASON 취소사유$CONF_CREASON
FROM CDMS_A01 A
WHERE 1=1
AND CONF_HEAD = NVL('', CONF_HEAD)
AND CONF_BRCH = NVL('', CONF_BRCH)
AND NVL(ODB_WK_HP, '-') = NVL('', NVL(ODB_WK_HP, '-'))
AND NVL(PARTNER_CALL_ID, '-') = NVL('', NVL(PARTNER_CALL_ID, '-'))
AND CONF_SLIP like '147960015'||'%'
ORDER BY a.IN_DATE DESC;
-- 수주본사값 넣기위해 , 수주지사값 : 'X012' => 이값으로 조회시 HD_NO: X100 나옴.
SELECT HD_NO FROM CDMS_BRANCH WHERE BR_NO = 'X012';
--- 수정문
SELECT WK_HEAD, WK_BRCH, CONF_WK_SABUN FROM CDMS_A01 WHERE CONF_SLIP ='147960015';
-- 해당 값이 비어 있으므로 이값을 강제로 넣어줌.
UPDATE CDMS_A01
SET WK_HEAD ='X100', WK_BRCH='X012', CONF_WK_SABUN='M00001'
WHERE CONF_SLIP ='147960015';
COMMIT;
MNG_ORDER.RE_BAECHA
--- 오더 조회 쿼리 2025 12 04
SELECT ROWNUM AS RNUM, T.* FROM
(
SELECT
CONF_SLIP 접수번호$CONF_SLIP#K#S
, CONF_HEAD 발주본사$CONF_HEAD#S
, CONF_BRCH 발주지사$CONF_BRCH#S
, WK_HEAD 수주본사$WK_HEAD
, WK_BRCH 수주지사$WK_BRCH
, CONF_WK_SABUN 사번$CONF_WK_SABUN#S
, CONF_WORKER 이름$CONF_WORKER#R
, (SELECT DT_NM FROM CDMS_SYSCODE WHERE SY_CD = 'AS' AND DT_CD = CONF_STATUS) as 상태$CONF_STATUS
, KEY_NUMBER 대표번호
, (SELECT BR_TEL FROM CDMS_BRANCH WHERE BR_NO=CONF_BRCH) 발주지사_상황실번호
, DECODE(BAECHA_TYPE, '0', '기사배차', '1', '강제배차', '2', '문자배차', '3','차감문자배차') as 배차타입$BAECHA_TYPE
, CONF_CREASON 취소사유$CONF_CREASON
, CONF_AREA5 출발지$CONF_AREA5#S#R
, CONF_EDAREA5 도착지$CONF_EDAREA5#S#R
, DECODE(SUBSTR(A.DRIVER_OPTION,3,1), 'y', 'y','-') "탁송여부"
, CONF_CUST_TEL 고객번호$CONF_CUST_TEL#S#R
, (SELECT DT_NM FROM CDMS_SYSCODE WHERE SY_CD = 'PY' AND DT_CD = PAY_METHOD) as 결제방법$PAY_METHOD
, CONF_INFO2 적요2$CONF_INFO2#S
, A.IN_DATE 입력일자$IN_DATE#D#S
, FIRST_IN_DATE 최초접수시간$FIRST_IN_DATE
, BAECHA_TIME 배차시간$BAECHA_TIME
, ODB_WK_HP 카카오단말번호$ODB_WK_HP#S
, PARTNER_CALL_ID KM_위탁콜_ID$KM_ID#S
, NVL((
SELECT 'P' FROM CDMS_BRANCH_ENV E
WHERE E.BR_NO = A.CONF_BRCH AND E.MAIN_BRANCH_YN = 'y'
AND EVENT_WEEK LIKE '%'||TO_CHAR(A.FIRST_IN_DATE-0.375, 'd')||'%'
AND ( (E.EVENT_STT < E.EVENT_END AND TO_CHAR(A.FIRST_IN_DATE , 'HH24MISS') BETWEEN E.EVENT_STT||'00' AND E.EVENT_END||'00' )
OR (E.EVENT_STT >= E.EVENT_END AND ( NOT TO_CHAR(A.FIRST_IN_DATE , 'HH24MISS') BETWEEN E.EVENT_END||'00' AND E.EVENT_STT||'00' OR TO_CHAR(A.FIRST_IN_DATE, 'HH24MISS')='210000' ) ) )
AND (
A.FIRST_IN_DATE BETWEEN TO_DATE('2024-12-11 09:00:00', 'yyyy-mm-dd hh24:mi:ss') AND TO_DATE('2024-12-15 01:00:00', 'yyyy-mm-dd hh24:mi:ss')
OR
A.FIRST_IN_DATE BETWEEN TO_DATE('2024-12-18 09:00:00', 'yyyy-mm-dd hh24:mi:ss') AND TO_DATE('2024-12-22 01:00:00', 'yyyy-mm-dd hh24:mi:ss')
OR
A.FIRST_IN_DATE BETWEEN TO_DATE('2024-12-26 09:00:00', 'yyyy-mm-dd hh24:mi:ss') AND TO_DATE('2024-12-29 01:00:00', 'yyyy-mm-dd hh24:mi:ss')
)
AND A.BAECHA_TYPE NOT IN ('1','2','3')
), ' ') 포인트콜조건충족$IS_EVENT_YN
FROM CDMS_A01 A
WHERE 1=1
AND CONF_HEAD = NVL('', CONF_HEAD)
AND CONF_BRCH = NVL('', CONF_BRCH)
AND NVL(ODB_WK_HP, '-') = NVL('', NVL(ODB_WK_HP, '-'))
AND NVL(PARTNER_CALL_ID, '-') = NVL('', NVL(PARTNER_CALL_ID, '-'))
AND CONF_SLIP like '147960015'||'%'
ORDER BY a.IN_DATE DESC
) T
WHERE 1 = 1
AND ROWNUM BETWEEN 1 AND 100;
-- 기사캐쉬로그 147960015 <2025 12 04>
SELECT T.* FROM
(
SELECT ROWNUM AS RNUM, T.* FROM
(
SELECT
WK_SEQ 일련번호#K
, IN_DATE 날짜$IN_DATE
, nvl(CONF_SLIP,' ') 접수번호$CONF_SLIP
, (SELECT DT_NM FROM CDMS_SYSCODE WHERE SY_CD = 'WT' AND DT_CD = wc.WK_CHARGE_TYPE) 구분$DT_NM
, WK_CHARGE 공용캐쉬$WK_CHARGE
, WK_CY_CHARGE 자체캐쉬$WK_CY_CHARGE
, WK_CASH 남은금액$WK_CASH
, IN_ID 입출금자$IN_ID#D#S
, NVL(WK_CYCASH_MEMO, ' ') 메모$WK_CYCASH_MEMO
, (SELECT HD_NAME FROM CDMS_HEAD WHERE HD_NO = wc.HD_NO) || '(' || HD_NO || ')' 본사$hd_n
, (SELECT BR_NAME FROM CDMS_BRANCH WHERE BR_NO = wc.BR_NO) || '(' || BR_NO || ')' 지사$br_n
, WK_SABUN 기사사번$WK_SABUN#S
, (SELECT WK_NAME FROM CDMS_WORKER WHERE WK_SABUN = wc.WK_SABUN) 이름$WK_NAME#R
FROM CDMS_WK_CASHLOG wc
WHERE 1=1
AND WK_SABUN = 'M00001'
AND IN_DATE >= TRUNC(SYSDATE-14)
AND IN_DATE <= TRUNC(SYSDATE+1)
ORDER BY IN_DATE desc, WK_SEQ DESC
) T
WHERE 1=1
AND
(
접수번호$CONF_SLIP LIKE '%147960015%'
)
) T
WHERE RNUM BETWEEN 1 AND 100;
-- (1) 재배차 후 CDMS_A01 실제 데이터 확인
SELECT
CONF_SLIP,
CONF_WK_SABUN,
CONF_WORKER,
WK_HEAD,
WK_BRCH,
KD_CALL_ID,
ODB_WK_SABUN,
ODB_WK_NAME,
ODB_WK_HP,
UP_ID,
TO_CHAR(UP_DATE, 'YYYY-MM-DD HH24:MI:SS') AS UP_DATE
FROM CDMS_A01
WHERE CONF_SLIP = '147960015';
--- 오더 상태 확인
SELECT CONF_STATUS, CONF_SLIP, CONF_WK_SABUN, WK_BRCH, WK_HEAD
FROM CDMS_A01
WHERE CONF_SLIP = '147960015';
--- 디버그 로그 테이블 확인
SELECT *
FROM CDMS_PROC_DEBUG_LOG
WHERE MODULE LIKE '%BAECHA%' AND USER_MESSAGE LIKE '%M00001%'
ORDER BY IN_TIMESTAMP DESC;
-- 에러 로그 테이블 확인
SELECT *
FROM CDMS_PROC_ERROR_LOG
WHERE USER_MESSAGE LIKE '%M00001%'
ORDER BY IN_DATE DESC;
CANCEL_CALL_MANUALLY_OLD
WK_BRCH
CDMS_A01
CDMS_WORKER
MNG_ORDER
SELECT * FROM CDMS_PROC_DEBUG_LOG_OLD_1 d WHERE module = 'MNG_ORDER.RE_BAECHA';
--- 오더수정 페이지 상세보기 쿼리
SELECT
A.CONF_SLIP 접수번호
, A.CONF_AREA || '/' || A.CONF_AREA2 || '/' || A.CONF_AREA3 || '/' || A.CONF_AREA4 || '/' || A.CONF_AREA5|| '
<br>->' || A.CONF_EDAREA || '/' || A.CONF_EDAREA2 || '/' || A.CONF_EDAREA3 || '/' || A.CONF_EDAREA4 || '/' || A.CONF_EDAREA5 AS "출발지 -> 도착지"
, A.CONF_CHARGE 요금
, DECODE_SYSCODE('AS',A.CONF_STATUS) 상태
, (SELECT B.MEM_NAME FROM CDMS_MEMBER B WHERE B.MEM_ID = A.IN_ID)||'
s<br>('||A.IN_ID||')' 등록자
, NVL((SELECT AA.BR_NAME || '<br> (' || A.WK_CENTER || ')' FROM CDMS_BRANCH AA WHERE AA.BR_NO = A.WK_CENTER), ' ') 기사콜센터
, NVL((SELECT BB.BR_NAME || '<br> (' || A.WK_BRCH || ')' FROM CDMS_BRANCH BB WHERE BB.BR_NO = A.WK_BRCH), ' ') 기사소속사
, (SELECT W.WK_NAME FROM CDMS_WORKER W WHERE W.WK_SABUN = A.CONF_WK_SABUN)|| '<br> (' || NVL(A.CONF_WK_SABUN, ' ') || ')' 기사
, NVL(REPLACE(A.MODIFIED,'송금 완료','<b>송금 완료</b>'), ' ') 수정메모
, TO_CHAR(A.IN_DATE,'DD HH24:MI:SS') 입력시간
, NVL(A.ODB_WK_INFO, ' ') 카카오정보
, CASE WHEN A.CONF_WK_SABUN IS NOT NULL AND A.CONF_WK_SABUN != '기사NO'
AND NVL(A.CONF_STATUS, '-') != 'D' -- 운행 상태 제외
THEN
'<input type="button" name="reBaecha" onClick="wk_re_baecha('''
|| A.CONF_SLIP||'│'||A.CONF_WK_SABUN||'│'||A.WK_BRCH||'│'||A.ODB_WK_INFO
||''')" value="재배차">'
ELSE ' ' END 재배차$RE_BAECHA
FROM CDMS_A01_HIS A
WHERE A.CONF_SLIP = '147960015'
ORDER BY A.in_date desc, decode(conf_status,'7','a','3','b','1','c','8','d',conf_status) DESC;
-- 최종 수정 쿼리 하지만 두번 조회 해야 하므로 효율이 떨어짐.
SELECT
A.CONF_SLIP 접수번호,
A.CONF_AREA || '/' || A.CONF_AREA2 || '/' || A.CONF_AREA3 || '/' || A.CONF_AREA4 || '/' || A.CONF_AREA5
|| '<br>->' ||
A.CONF_EDAREA || '/' || A.CONF_EDAREA2 || '/' || A.CONF_EDAREA3 || '/' || A.CONF_EDAREA4 || '/' || A.CONF_EDAREA5
AS "출발지 -> 도착지",
A.CONF_CHARGE 요금,
DECODE_SYSCODE('AS',A.CONF_STATUS) 상태,
(SELECT B.MEM_NAME FROM CDMS_MEMBER B WHERE B.MEM_ID = A.IN_ID) || '<br>(' || A.IN_ID || ')' 등록자,
NVL((
SELECT BB.BR_NAME || '<br>(' ||
CASE
WHEN A.CONF_STATUS = 'D' THEN
NVL(A.WK_CENTER, (
SELECT H.WK_CENTER
FROM CDMS_A01_HIS H
WHERE H.CONF_SLIP = A.CONF_SLIP AND H.CONF_STATUS = '1' AND ROWNUM = 1
))
ELSE A.WK_CENTER
END || ')'
FROM CDMS_BRANCH BB
WHERE BB.BR_NO = CASE
WHEN A.CONF_STATUS = 'D' THEN
NVL(A.WK_CENTER, (
SELECT H.WK_CENTER
FROM CDMS_A01_HIS H
WHERE H.CONF_SLIP = A.CONF_SLIP AND H.CONF_STATUS = '1' AND ROWNUM = 1
))
ELSE A.WK_CENTER
END
AND ROWNUM = 1
), ' ') AS 기사콜센터,
NVL((
SELECT CC.BR_NAME || '<br>(' ||
CASE
WHEN A.CONF_STATUS = 'D' THEN
NVL(A.WK_BRCH, (
SELECT H.WK_BRCH
FROM CDMS_A01_HIS H
WHERE H.CONF_SLIP = A.CONF_SLIP AND H.CONF_STATUS = '1' AND ROWNUM = 1
))
ELSE A.WK_BRCH
END || ')'
FROM CDMS_BRANCH CC
WHERE CC.BR_NO = CASE
WHEN A.CONF_STATUS = 'D' THEN
NVL(A.WK_BRCH, (
SELECT H.WK_BRCH
FROM CDMS_A01_HIS H
WHERE H.CONF_SLIP = A.CONF_SLIP AND H.CONF_STATUS = '1' AND ROWNUM = 1
))
ELSE A.WK_BRCH
END
AND ROWNUM = 1
), ' ') AS 기사소속사,
(SELECT W.WK_NAME FROM CDMS_WORKER W WHERE W.WK_SABUN = A.CONF_WK_SABUN)
|| '<br>(' || NVL(A.CONF_WK_SABUN, ' ') || ')' 기사,
NVL(REPLACE(A.MODIFIED,'송금 완료','<b>송금 완료</b>'), ' ') 수정메모,
TO_CHAR(A.IN_DATE,'DD HH24:MI:SS') 입력시간,
NVL(A.ODB_WK_INFO, ' ') 카카오정보,
CASE
WHEN A.CONF_WK_SABUN IS NOT NULL AND A.CONF_WK_SABUN != '기사NO' THEN
'<input type="button" name="reBaecha" onClick="wk_re_baecha('''
|| A.CONF_SLIP||'│'||A.CONF_WK_SABUN||'│'||NVL(A.WK_BRCH,'')||'│'||NVL(A.ODB_WK_INFO,'')
||''')" value="재배차">'
ELSE ' '
END 재배차$RE_BAECHA
FROM CDMS_A01_HIS A
WHERE A.CONF_SLIP = '147960015'
ORDER BY A.IN_DATE DESC, DECODE(CONF_STATUS,'7','a','3','b','1','c','8','d',CONF_STATUS) DESC;
케어플러스 정기결제관련 프로시저 <케어플러스고도화 작업>
CREATE OR REPLACE PROCEDURE CDMS_T.JOB_CAREPLUS_PAYMENT_AUTO_V3( i_param IN VARCHAR2)
IS
v_module VARCHAR (50) := 'JOB_CAREPLUS_PAYMENT_AUTO_V3';
v_result VARCHAR2(500); -- 업데이트 결과값
-- G__PAYMENT NUMBER := 25000; -- 결제금액 [2025.12.01 케플고도화] 고정 금액 제거
v_balance NUMBER;
v_real_charge NUMBER;
v_money NUMBER;
v_cy_charge NUMBER;
v_real_cash NUMBER;
v_wk_cycash CDMS_WORKER.WK_CYCASH%TYPE;
v_temp_param VARCHAR2 (100);
v_careplus_start_date VARCHAR2(10);
v_careplus_end_date VARCHAR2(10);
v_next_payment_date VARCHAR2(10);
v_fourteen_oclock VARCHAR2(2) := '14';
v_eight_oclock VARCHAR2(2) := '08';
v_request_time VARCHAR2(14);
v_event_memo VARCHAR2 (100);
o_code NUMBER;
o_mesg VARCHAR2(3000);
o_data VARCHAR2(500);
v_sub_cnt NUMBER := 0; -- [2025.12.01 케플고도화] 구독 서비스 개수 카운트
v_next_opt_str CHAR(3); -- [2025.12.01 케플고도화] 다음달 옵션 문자열 (예: 'yny')
ERR_PARAM EXCEPTION;
-- 일반정기결제 및 모든 이벤트 대상을 가져온다.
CURSOR PAYMENT (II_TIME VARCHAR) IS
SELECT WK.WK_SABUN WK_SABUN
, DECODE_WK_HP(WK.WK_HP) WK_HP
, NVL(REGEXP_REPLACE(WK.WK_NAME, '[^가-힝]', ''), ' ') WK_NAME -- 기사이름
, WK.HD_NO HD_NO
, WK.BR_NO BR_NO
, WK.WK_CASH WK_CASH
, WK.WK_CYCASH WK_CYCASH
, BR.BR_CASH
, NVL(BR.CYBANK_YN, 'n') CYBANK_YN
, (SELECT BANK_CODE ||'('|| DECODE_SYSCODE('BK',BANK_CD)||')' FROM CDMS_BANK WHERE USER_ID = WK.WK_SABUN AND USER_TYPE='2' AND BANK_CODE=WK.WK_CYBANK2 AND BANK_STATUS ='Y' AND ROWNUM <2) WK_BANK_ACCOUNT
, CP.CAREPLUS_REG_DATE CAREPLUS_REG_DATE
, CP.CAREPLUS_START_DATE CAREPLUS_START_DATE
, CP.CAREPLUS_END_DATE CAREPLUS_END_DATE
, CP.PAYMENT_DATE PAYMENT_DATE
, CP.NEXT_PAYMENT_YN
, CP.NEXT_PAYMENT_DATE
, CP.IN_DATE
, CP.IN_ID
, CP.CAREPLUS_UPDATE_DATE
, CP.UPDATE_ID
, CP.DEL_YN
, CP.PAYMENT_MONEY
, CP.TRIED_REGULAR_PAYMENT
, CP.ONEPLUSONE_EVENT_YN
, CP.IN_MEMO -- 구독취소 일경우 유지하기위함
, NVL(CP.NEXT_PKG_OPTION, 'yyy') AS NEXT_PKG_OPTION -- [2025.12.01 케플고도화] 다음 결제 옵션 (기본값 처리 포함)
FROM CDMS_WORKER WK JOIN CDMS_CAREPLUS CP ON WK.WK_SABUN = CP.WK_SABUN
JOIN CDMS_BRANCH BR ON WK.BR_NO = BR.BR_NO
WHERE WK_STATUS = '00'
AND CP.DEL_YN = 'n'
AND CP.NEXT_PAYMENT_YN = 'y'
AND CP.NEXT_PAYMENT_DATE = (CASE WHEN II_TIME = '08' THEN TO_CHAR(SYSDATE-1,'yyyymmdd') ELSE TO_CHAR(SYSDATE,'yyyymmdd') END) -- 다음날 08시는 SYSDATE-1 2,5,8시는 SYSDATE
AND CP.PRIMARY_YN = 'y'
--AND CP.WK_SABUN = 'L68092'
;
cursor1 PAYMENT%ROWTYPE;
BEGIN
/******************************************************************************
매일 오후 2시, 5시, 8시, 다음날 오전 08시, 케어플러스 정기결제일이 된 기사를 대상으로
자동 결제를 처리한다.
date : 24-11-22
author : james
******************************************************************************/
OPEN PAYMENT(i_param);
LOOP
FETCH PAYMENT INTO cursor1;
EXIT WHEN PAYMENT%NOTFOUND;
BEGIN
v_event_memo := '';
v_sub_cnt := 0; -- [2025.12.01 케플고도화] 구독 서비스 개수 초기화
v_next_opt_str := cursor1.NEXT_PKG_OPTION; -- [2025.12.01 케플고도화] 다음달 옵션 문자열
-- 이벤트 무료 대상 체크
IF cursor1.ONEPLUSONE_EVENT_YN = 'y' THEN
v_real_cash := 0;
v_wk_cycash := 0;
v_money := 0;
v_event_memo := '1+1이벤트 무료';
ELSE
-- 일반결제
-- [2025.12.01 케플고도화] 구독 패키지 개수 카운트
IF SUBSTR(v_next_opt_str, 1, 1) = 'y' THEN v_sub_cnt := v_sub_cnt + 1; END IF;
IF SUBSTR(v_next_opt_str, 2, 1) = 'y' THEN v_sub_cnt := v_sub_cnt + 1; END IF;
IF SUBSTR(v_next_opt_str, 3, 1) = 'y' THEN v_sub_cnt := v_sub_cnt + 1; END IF;
-- [2025.12.01 케플고도화] 개수에 따른 금액 설정 (1 15000 / 2 20000 / 3 25000)
CASE v_sub_cnt
WHEN 1 THEN v_money := 15000;
WHEN 2 THEN v_money := 20000;
WHEN 3 THEN v_money := 25000;
ELSE
-- 예외: 구독 정보가 모두 n인데 결제 대상인 경우 ? 방어 로직
-- 정책에 따라 처리 필요. 여기서는 기존 금액 유지 혹은 최소 금액 설정
v_money := 25000;
END CASE;
-- v_money := G__PAYMENT;
v_balance := cursor1.WK_CASH + cursor1.WK_CYCASH;
v_real_charge := CASE WHEN cursor1.WK_CASH > v_money THEN v_money -- 전액 공용캐시
WHEN cursor1.WK_CYCASH > v_money THEN 0 -- 전액 자체캐시
WHEN cursor1.WK_CASH > 0 THEN cursor1.WK_CASH -- 부분차감 : 공용캐시 잔액 + 자체캐시
ELSE 0 END; -- 전액 자체캐시 (마이너스 발생)
v_cy_charge := v_money - v_real_charge;
IF v_balance < v_money THEN
o_mesg := '잔액이 부족합니다. (가상계좌 : '||cursor1.WK_BANK_ACCOUNT||')';
v_result := 'NO MONEY';
UPDATE CDMS_CAREPLUS
SET TRIED_REGULAR_PAYMENT = TRIED_REGULAR_PAYMENT + 1
WHERE WK_SABUN = cursor1.WK_SABUN;
IF i_param = v_fourteen_oclock THEN
PROG_CAREPLUS.send_type_pay_fail(cursor1.WK_HP, cursor1.WK_NAME, cursor1.WK_BANK_ACCOUNT);
END IF;
CONTINUE; -- 실패건 제외 ....
ELSE
-- 캐쉬계산
v_real_cash := get_real_charge(cursor1.CYBANK_YN, v_money, cursor1.WK_CASH, cursor1.WK_CYCASH);
v_wk_cycash := v_money - v_real_cash;
END IF;
END IF;
-- 결제시작 --
INSERT INTO CDMS_WK_CASHLOG
(WK_SABUN, HD_NO, BR_NO, WK_CHARGE_TYPE, WK_CHARGE, WK_CY_CHARGE, IN_ID, WK_CYCASH_MEMO)
VALUES (cursor1.WK_SABUN, cursor1.HD_NO, cursor1.BR_NO, '36', (-1)*v_real_cash, (-1)*v_wk_cycash, cursor1.WK_SABUN, '케어플러스 '||to_char(sysdate,'MM')||'월 정기결제'||v_event_memo);
-- 자체캐쉬 지사에서 출금
IF v_wk_cycash > 0 THEN
INSERT INTO CDMS_BR_CASHLOG
(HD_NO, BR_NO, BR_CHARGE_TYPE, BR_CHARGE, IN_ID, CONF_SLIP, BR_MEMO)
VALUES (cursor1.HD_NO, cursor1.BR_NO, '36', (-1)*v_wk_cycash, cursor1.WK_SABUN, null, '케어플러스 정기결제('|| cursor1.WK_SABUN || ')'||v_event_memo);
END IF;
-- 케어플러스 사용료 차감 후 예수금 지사 입금처리
INSERT INTO CDMS_BR_CASHLOG (HD_NO, BR_NO, BR_CHARGE_TYPE, BR_CHARGE, IN_ID, BR_MEMO)
VALUES ('A100', 'B100', '38', v_money, 'system', '케어플러스 정기결제(' || cursor1.WK_SABUN || ', ' || v_money || '원)'||v_event_memo ); --기사 사번 메모에 남겨야함
-- 결제 끝--
/*
INSERT INTO CDMS_CAREPLUS_LOG
(WK_SABUN, CAREPLUS_REG_DATE, CAREPLUS_START_DATE, CAREPLUS_END_DATE, CAREPLUS_STATUS, PAYMENT_DATE, PAYMENT_MONEY, NEXT_PAYMENT_DATE, CAREPLUS_FREE, IN_DATE, IN_ID, IN_MEMO, STATUS_TYPE)
VALUES (cursor1.WK_SABUN, cursor1.CAREPLUS_REG_DATE, sysdate+1, add_months(sysdate+1,1), 'y', sysdate, v_money, add_months(sysdate,1), 'n', sysdate, 'system', 'REGULAR PAYMENT'||v_event_memo, '02');
*/
UPDATE CDMS_CAREPLUS
SET
CAREPLUS_START_DATE = sysdate+1
, CAREPLUS_END_DATE = add_months(sysdate+1, 1)
, PAYMENT_DATE = sysdate
, NEXT_PAYMENT_DATE = to_char(add_months(sysdate, 1) , 'yyyymmdd')
, CAREPLUS_UPDATE_DATE = sysdate
, UPDATE_ID = 'system'
, PAYMENT_MONEY = v_money
, PAYMENT_CNT = PAYMENT_CNT + 1
, TRIED_REGULAR_PAYMENT = 0
, IN_MEMO = (CASE WHEN cursor1.IN_MEMO != 'QUIT REQ' THEN 'REGULAR PAYMENT' ELSE IN_MEMO END) -- 구독취소는 유지한다.
, CAREPLUS_FREE = (CASE WHEN cursor1.ONEPLUSONE_EVENT_YN = 'y' THEN 'y' ELSE 'n' END) -- 케어플러스 무료를 y로 한다.
, ONEPLUSONE_EVENT_YN = 'n' -- 정기결제는 무조건 원플러스를 해제한다.
, DEL_YN = 'n'
, NEXT_PAYMENT_YN = 'y'
, PKG_OPTION = v_next_opt_str -- [2025.12.01 케플고도화] 구독옵션 갱신
WHERE PRIMARY_SABUN = cursor1.WK_SABUN
and wk_sabun in(SELECT wk_sabun FROM cdms_worker WHERE wk_status != '50')
;
IF SQL%ROWCOUNT = 0 THEN
v_result := 'NG';
ELSE
v_result := 'GOOD';
END IF;
-- [2025.12.01 케플고도화] 구독옵션 갱신
UPDATE CDMS_WORKER SET CAREPLUS_PKG_OPTION = v_next_opt_str
WHERE WK_SABUN IN ( SELECT WK_SABUN FROM CDMS_CAREPLUS WHERE PRIMARY_SABUN = cursor1.WK_SABUN AND WK_SABUN IN(SELECT WK_SABUN FROM CDMS_WORKER WHERE WK_STATUS != '50') );
v_request_time := to_char(trunc(sysdate+1),'yyyymmdd') || '140000';
v_temp_param := cursor1.WK_SABUN||'│'||cursor1.WK_HP||'│'||v_request_time;
log_info.i('system', v_module, v_temp_param);
-- [2025.12.01 케플고도화] 쿠폰패키지 구독자에게만 쿠폰지급
IF SUBSTR(v_next_opt_str, 2, 1) = 'y' THEN
COUPON_DISTRIBUTE_REGULAR_PAY(o_code, o_mesg,'system', v_temp_param, o_data);
END IF;
v_careplus_start_date := to_char(sysdate+1, 'yyyy.mm.dd');
v_careplus_end_date := to_char(add_months(sysdate+1,1), 'yyyy.mm.dd');
v_next_payment_date := to_char(add_months(sysdate,1), 'yyyy.mm.dd');
PROG_CAREPLUS_V2.send_type_pay_succ(cursor1.WK_HP, cursor1.WK_NAME, v_careplus_start_date, v_careplus_end_date, v_next_payment_date, v_money);
log_debug.i ('system', v_module, v_result || ': ['||cursor1.WK_SABUN||']');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
/** logging */ util_error.auto('job', v_module);
END;
END LOOP;
CLOSE PAYMENT;
EXCEPTION
WHEN OTHERS
THEN
/** logging */ util_error.auto('job', v_module);
END;
백오피스 전체 이용자 접속로그 10월~11월꺼 조회 쿼리MYSQL
-- MYSQL백오피스 전체 이용자 접속로그 10월~11월꺼 좀 전달해주세요.
SELECT
l.user_id,
u.username AS user_email,
u.name AS user,
l.client_ip,
l.type,
l.created_at as login_date
FROM user_log AS l
JOIN user AS u
ON l.user_id = u.id
WHERE l.type = 'AUTHENTICATION'
AND l.created_at BETWEEN '2025-10-01 00:00:00' AND '2025-11-30 23:59:59'
ORDER BY l.created_at DESC;케어플러스 이관 실패 확인 요청 쿼리 기록
알려주신 사번으로는 잘조회 되어 뭐가 문제였는지 역추적 하다 딘이 클락이 알려준 사번이 틀릴수도있다하여 딘의 아이디어로 문제해결 문제 추적 하는 쿼리 기록함.
-- 딘 케어플러스 역조회 방법 2025 12 11
-- 없음.
SELECT * FROM CDMS_PROC_ERROR_LOG WHERE ERROR_MESSAGE LIKE '%MOVE_CAREPLUS_EVENT%';
-- 케어플러스 퇴직이동시 이동할 사번으로 조회시 일단 해당 조건으로 데이터 존재 하는지 봄. 없으면
-- '케어플러스 미가입 상태입니다(CDMS_CAREPLUS)' 이메시지 뱉고 함수 종료함.
SELECT * FROM CDMS_WORKER WK JOIN CDMS_CAREPLUS CP ON WK.WK_SABUN = CP.WK_SABUN WHERE CP.WK_SABUN ='N84684' AND CP.PRIMARY_YN = 'y';
-- 어떤 패키지나 프로시저에 해당 내용이 기록되어 있는지 line 까지 알려줌, 여기서 어디서 해당값이 update 됬는지 찾을수 있음.
SELECT *
FROM user_source A
WHERE upper(A.text) LIKE '%PRIMARY_YN%';
SELECT * FROM CDMS_WORKER WHERE WK_SABUN ='N84684';
SELECT * FROM CDMS_WORKER WHERE WK_SABUN ='N84684';
result
SELECT WK_SABUN FROM CDMS_WORKER WHERE WK_SNUM_HASH ='5abad7d81996a0021d935073de8de03979157f8b161d2f11867ce02a350d6782';
result
-- 케어 플러스는 주민번호가 메인으로 조회함 프로시저에서도 CDMS_WORKER에서 주민번호 조회후 뱉은 사번으로 케어플러스 해당 기사 조회함.
SELECT * FROM CDMS_CAREPLUS WHERE WK_SABUN IN(SELECT WK_SABUN FROM CDMS_WORKER WHERE WK_SNUM_HASH ='5abad7d81996a0021d935073de8de03979157f8b161d2f11867ce02a350d6782');
result
CDMS_CAREPLUS테이블 에서는 PRIMARY_YN 의 'y'값이 하나이여야 하는데 처음 조회시 y값이 두개가 나오므로 이건 잘못된 기록이라고 판단함.
- 그래서 딘이 N84684가 아니라 Q58943가 아닌지 물어보니 Q58943가 맞는 사번이여서 PRIMARY_SABUN이 주력 사번인 Q58943이로 변경하고 PRIMARY_YN도 사번이 Q58943것만 'y'로 남기고 나머지는 'n'이 되어야한다.
따라서 수정 쿼리는 아래와 같이 UPDATE 해준다.
UPDATE CDMS_CAREPLUS
SET PRIMARY_SABUN = 'Q58943', PRIMARY_YN = 'n'
WHERE WK_SABUN ='N81993';
재 조회
SELECT * FROM CDMS_CAREPLUS WHERE WK_SABUN IN(SELECT WK_SABUN FROM CDMS_WORKER WHERE WK_SNUM_HASH ='5abad7d81996a0021d935073de8de03979157f8b161d2f11867ce02a350d6782');
원래는 PRIMARY_SABUN = 'Q58943' 인데 케어플러스 퇴직이동을 실행하여 최종 이동사번이 N81993로 변경 되었다.
추가 기록 쿼리
SELECT * FROM CDMS_WORKER WHERE WK_SNUM_HASH ='5abad7d81996a0021d935073de8de03979157f8b161d2f11867ce02a350d6782';
result
hi