2ac4c3c920
갭분석 2·3순위 보강. 전부 실측 검증. ① interchange qualification 심화 (MCC 업종별 등급) - mm_merch_mcc: 40개 가맹점 → 실제 4자리 ISO MCC (커피 5814, 마트 5411, 면세점 5309, 병원 8062, 주유소 5541, 여행사 4722, 숙박 7011 ...) - mm_mcc: 26개 MCC + risk tier(1 저율/2 표준/3 고율) - st_interchange_rate 에 MCC×카드종류 요율 override → fee_component 재계산 - 실측: tier1(병원0.69%/약국0.72%/주유0.75%) < tier2(요식~1.15%) < tier3(여행~1.62%) — 실제 매입사 interchange 차등 그대로. 3층 항등 7560/7560, 펀딩 2925/2925 유지. ② ISO 8583 클리어링 메시지 클래스 (승인전문과 분리된 클리어링 단계) - mg_clearing: PRESENTMENT(1240)/CHARGEBACK(1442)/REPRESENT/RECONCILE(1500)/ FEE_COLLECT(1740). 실측 8,658건. - MG_CLEARING_INQ 서비스(mg, 120줄): 클래스/영업일/pid 필터 집계 조회. 실호출 검증(DUAL 6,386 / SINGLE 2,272, 대표 MTI 1240). ③ 이중/단일 메시지 구분 - msg_type: 신용=DUAL(5,288) / 체크=SINGLE(2,272). 실제 dual/single-message 프로토콜. 포털 "정산 경제"(#econ)에 MCC 등급표 + ISO8583 클리어링 메시지표 추가. 통합 빌드 error 0, 도메인 5,883 서비스 AVAIL, 6단 체인 유지. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
157 lines
9.6 KiB
SQL
157 lines
9.6 KiB
SQL
-- ===========================================================================
|
||
-- 97-econ2-seed.sql : 2차 경제모델 시드 (71-econ2 DDL + 96-econ-seed 이후 실행).
|
||
-- MCC 부여 → MCC별 interchange tier 로 수수료 재계산 → 순액정산·펀딩 재집계 →
|
||
-- ISO 8583 클리어링 메시지 생성. 전부 idempotent(재실행 안전).
|
||
-- ===========================================================================
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 1. MCC 마스터 (실제 4자리 ISO MCC + interchange tier).
|
||
-- tier(risk_level): 1=저율(생필/주유) 2=표준(요식/소매) 3=고율(여행/유흥).
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO mm_mcc (mcc, mcc_name, risk_level) VALUES
|
||
('5411','슈퍼마켓/식료품',1),('5541','주유소',1),('5912','약국',1),('8062','병원',1),
|
||
('5812','일반음식점',2),('5814','패스트푸드/카페',2),('5732','전자제품',2),
|
||
('5942','서점',2),('5943','문구',2),('5992','화훼',2),('5712','가구/인테리어',2),
|
||
('7216','세탁',2),('8043','안경',2),('0742','동물병원',2),('5571','오토바이',2),
|
||
('5309','면세점',2),('5941','스포츠/캠핑용품',2),('7230','미용',2),
|
||
('4722','여행사',3),('7011','숙박',3),('7994','게임/PC',3),('7929','노래연습장',3),
|
||
('7997','휘트니스/골프',3),('7992','골프연습',3),('7299','예식/서비스',3),('8299','교육',2)
|
||
ON CONFLICT (mcc) DO UPDATE SET mcc_name=EXCLUDED.mcc_name, risk_level=EXCLUDED.risk_level;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 2. 가맹점 → MCC 매핑 (상호 기반).
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO mm_merch_mcc (merchant_id, mcc) VALUES
|
||
('M0001','5814'),('M0002','5411'),('M0003','5309'),('M0004','5812'),('M0005','8062'),
|
||
('M0006','7230'),('M0007','5812'),('M0008','5912'),('M0009','5541'),('M0010','5942'),
|
||
('M0011','5812'),('M0012','5712'),('M0013','8043'),('M0014','5812'),('M0015','7994'),
|
||
('M0016','7997'),('M0017','5814'),('M0018','5732'),('M0019','5992'),('M0020','5411'),
|
||
('M0021','7216'),('M0022','5912'),('M0023','7929'),('M0024','0742'),('M0025','7992'),
|
||
('M0026','5812'),('M0027','5943'),('M0028','5712'),('M0029','5411'),('M0030','5941'),
|
||
('M0031','5571'),('M0032','7299'),('M0033','4722'),('M0034','5812'),('M0035','7011'),
|
||
('M0036','8299'),('M0037','7997'),('M0038','5732'),('M0039','5814'),('M0040','5812')
|
||
ON CONFLICT (merchant_id) DO UPDATE SET mcc=EXCLUDED.mcc;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 3. MCC×카드종류 interchange 요율(tier 반영). DEFAULT 채널 기준 override 행.
|
||
-- tier1 저율, tier2 표준, tier3 고율. 체크카드는 신용 대비 저율.
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO st_interchange_rate (card_type, mcc, channel, txn_type, rate_bps, fixed_fee)
|
||
SELECT ct, m.mcc, 'DEFAULT', 'SALE',
|
||
CASE m.risk_level WHEN 1 THEN 90 WHEN 3 THEN 175 ELSE 130 END
|
||
- CASE WHEN ct='CHECK' THEN 55 WHEN ct='PREPAID' THEN 65 ELSE 0 END,
|
||
0
|
||
FROM mm_mcc m CROSS JOIN (VALUES ('CREDIT'),('CHECK'),('PREPAID')) v(ct)
|
||
ON CONFLICT (card_type, mcc, channel, txn_type) DO UPDATE SET rate_bps=EXCLUDED.rate_bps;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 4. 수수료 재계산 — MCC tier interchange 우선(있으면), 없으면 채널요율 폴백.
|
||
-- interchange+scheme_fee+markup=mdr_total 항등 유지, markup 하한 8bp.
|
||
-- ---------------------------------------------------------------------------
|
||
UPDATE st_fee_component fc SET
|
||
interchange = ic.amt,
|
||
markup = GREATEST(fc.mdr_total - ic.amt - fc.scheme_fee, fc.amount*8/10000),
|
||
mdr_total = ic.amt + fc.scheme_fee
|
||
+ GREATEST(fc.mdr_total - ic.amt - fc.scheme_fee, fc.amount*8/10000)
|
||
FROM (
|
||
SELECT fc2.purchase_id,
|
||
fc2.amount * COALESCE(r.rate_bps, fc2.amount*0 + 130) / 10000 AS amt
|
||
FROM st_fee_component fc2
|
||
JOIN purchase p ON p.purchase_id = fc2.purchase_id
|
||
LEFT JOIN mm_merch_mcc mm ON mm.merchant_id = p.merchant_id
|
||
LEFT JOIN st_interchange_rate r
|
||
ON r.card_type = fc2.card_type AND r.mcc = mm.mcc
|
||
AND r.channel = 'DEFAULT' AND r.txn_type = 'SALE'
|
||
) ic
|
||
WHERE ic.purchase_id = fc.purchase_id;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 5. 클리어링액(프레젠트먼트) 재동기화 = amount − interchange.
|
||
-- ---------------------------------------------------------------------------
|
||
UPDATE cl_presentment c SET clearing_amt = fc.amount - fc.interchange
|
||
FROM st_fee_component fc WHERE fc.purchase_id = c.purchase_id;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 6. 순액정산 재집계 (fee_component 변경 반영).
|
||
-- ---------------------------------------------------------------------------
|
||
TRUNCATE st_net_settlement;
|
||
INSERT INTO st_net_settlement (net_id, scheme, member_bin, biz_date, txn_count,
|
||
gross_amt, interchange_amt, scheme_fee_amt, net_amt, direction, status)
|
||
SELECT nextval('st_netset_seq'), 'BC', mb.bin, p.biz_date, count(*),
|
||
sum(fc.amount), sum(fc.interchange), sum(fc.scheme_fee),
|
||
sum(fc.amount - fc.interchange - fc.scheme_fee), 'RECEIVE', 'SETTLED'
|
||
FROM st_fee_component fc
|
||
JOIN purchase p ON p.purchase_id = fc.purchase_id
|
||
JOIN st_member_bank mb ON mb.bank_code = p.issuer
|
||
GROUP BY mb.bin, p.biz_date;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 7. 펀딩 재집계 (수수료 변경 반영).
|
||
-- ---------------------------------------------------------------------------
|
||
TRUNCATE st_merchant_funding;
|
||
INSERT INTO st_merchant_funding (funding_id, merchant_id, biz_date, gross_sales, refunds,
|
||
chargebacks, fees, reserve, deposit, funding_model, status)
|
||
SELECT nextval('st_funding_seq'), s.merchant_id, s.biz_date,
|
||
s.gross, s.refunds, 0, s.fees, coalesce(rv.reserve_amt,0),
|
||
s.gross - s.refunds - s.fees - coalesce(rv.reserve_amt,0),
|
||
'NET_DAILY', 'FUNDED'
|
||
FROM (
|
||
SELECT p.merchant_id, p.biz_date,
|
||
sum(CASE WHEN p.status='S' THEN p.amount ELSE 0 END) gross,
|
||
sum(CASE WHEN p.status='C' THEN p.amount ELSE 0 END) refunds,
|
||
coalesce(sum(fc.mdr_total),0) fees
|
||
FROM purchase p LEFT JOIN st_fee_component fc ON fc.purchase_id=p.purchase_id
|
||
GROUP BY p.merchant_id, p.biz_date
|
||
) s
|
||
LEFT JOIN st_reserve rv ON rv.merchant_id=s.merchant_id AND rv.biz_date=s.biz_date
|
||
WHERE s.gross > 0;
|
||
UPDATE st_merchant_funding f SET chargebacks = d.cb,
|
||
deposit = f.gross_sales - f.refunds - d.cb - f.fees - f.reserve
|
||
FROM (SELECT p.merchant_id, p.biz_date, sum(dp.amount) cb
|
||
FROM ac_dispute dp JOIN purchase p ON p.purchase_id = dp.purchase_id
|
||
WHERE dp.status = 'LOST' GROUP BY p.merchant_id, p.biz_date) d
|
||
WHERE f.merchant_id = d.merchant_id AND f.biz_date = d.biz_date;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 8. ISO 8583 클리어링 메시지 — 클리어링 단계의 실제 전문 클래스.
|
||
-- PRESENTMENT(1240): 정산완료건. msg_type: 신용=DUAL, 체크=SINGLE.
|
||
-- ---------------------------------------------------------------------------
|
||
INSERT INTO mg_clearing (clr_id, purchase_id, scheme, msg_class, mti, msg_type, amount, biz_date, status)
|
||
SELECT nextval('mg_clearing_seq'), fc.purchase_id, fc.scheme, 'PRESENTMENT', '1240',
|
||
CASE WHEN fc.card_type='CHECK' THEN 'SINGLE' ELSE 'DUAL' END,
|
||
fc.amount - fc.interchange, p.biz_date, 'ACCEPTED'
|
||
FROM st_fee_component fc JOIN purchase p ON p.purchase_id = fc.purchase_id
|
||
WHERE NOT EXISTS (SELECT 1 FROM mg_clearing c
|
||
WHERE c.purchase_id = fc.purchase_id AND c.msg_class='PRESENTMENT');
|
||
|
||
-- CHARGEBACK(1442): 분쟁 개시건.
|
||
INSERT INTO mg_clearing (clr_id, purchase_id, scheme, msg_class, mti, msg_type, amount, biz_date, status)
|
||
SELECT nextval('mg_clearing_seq'), d.purchase_id, 'BC', 'CHARGEBACK', '1442', 'DUAL',
|
||
d.amount, d.opened_at::date, 'ACCEPTED'
|
||
FROM ac_dispute d
|
||
WHERE NOT EXISTS (SELECT 1 FROM mg_clearing c
|
||
WHERE c.purchase_id = d.purchase_id AND c.msg_class='CHARGEBACK');
|
||
|
||
-- REPRESENT(1240 재제출): representment 단계 이상 분쟁.
|
||
INSERT INTO mg_clearing (clr_id, purchase_id, scheme, msg_class, mti, msg_type, amount, biz_date, status)
|
||
SELECT nextval('mg_clearing_seq'), d.purchase_id, 'BC', 'REPRESENT', '1240', 'DUAL',
|
||
d.amount, d.opened_at::date + 5, 'ACCEPTED'
|
||
FROM ac_dispute d WHERE d.stage IN ('REPRESENT','PRE_ARB','ARBITRATION')
|
||
AND NOT EXISTS (SELECT 1 FROM mg_clearing c
|
||
WHERE c.purchase_id = d.purchase_id AND c.msg_class='REPRESENT');
|
||
|
||
-- RECONCILE(1500): 발급사×영업일 정산대사 메시지 (순액정산 배치당 1건).
|
||
INSERT INTO mg_clearing (clr_id, purchase_id, scheme, msg_class, mti, msg_type, amount, biz_date, status)
|
||
SELECT nextval('mg_clearing_seq'), NULL, 'BC', 'RECONCILE', '1500', 'DUAL',
|
||
ns.net_amt, ns.biz_date, 'ACCEPTED'
|
||
FROM st_net_settlement ns
|
||
WHERE NOT EXISTS (SELECT 1 FROM mg_clearing c
|
||
WHERE c.msg_class='RECONCILE' AND c.biz_date=ns.biz_date AND c.amount=ns.net_amt);
|
||
|
||
-- FEE_COLLECT(1740): scheme fee 징수 메시지 (일별 집계 1건).
|
||
INSERT INTO mg_clearing (clr_id, purchase_id, scheme, msg_class, mti, msg_type, amount, biz_date, status)
|
||
SELECT nextval('mg_clearing_seq'), NULL, 'BC', 'FEE_COLLECT', '1740', 'DUAL',
|
||
sum(fc.scheme_fee), p.biz_date, 'ACCEPTED'
|
||
FROM st_fee_component fc JOIN purchase p ON p.purchase_id=fc.purchase_id
|
||
GROUP BY p.biz_date
|
||
HAVING NOT EXISTS (SELECT 1 FROM mg_clearing c WHERE c.msg_class='FEE_COLLECT' AND c.biz_date=p.biz_date);
|