社保理赔

批处理

社保账单信息导入批处理-LLSocialSecurityInfoImportTask
社保理赔审核批处理-LLSocialCaseReviewTask

社保理赔屏蔽的方案信息

select code from ldcode where codetype = 'SSClmExcludePlan';

社保限额刷新配置

SELECT * FROM llcontlimitrefreshinfo l WHERE l.NeedRefreshFlag ='Y' AND l.RefreshType='NB' AND l.GrpContNo ='' ;

社保理赔保单核心理赔不走社保理赔算法的配置

SELECT * FROM ldcode WHERE codetype LIKE '%notCalSSClm%';

社保理赔 导入失败清单

select * from LLSocialSecurityFeeInfoList l ,LLSocialSecurityAttachment d where 1=1 and State = 'a' and d.attachname = l.ImportFileName ;

社保理赔 审核状态

select a.GrpRgtNo,a.AppntName,a.AppPeoples,a.unAcceptPeoples,a.AppDate,a.RgtDate,DATEDIFF(CURDATE(),a.AppDate) + 1 AS acceptworkdays,a.MngCom,a.CurrentHandler from LLGrpSocialSecurityClaim a where 1=1 and a.CaseState = '1' and exists (select 1 from lwmission where missionprop1 = a.grprgtno and activityid = '1800608001');

[[社保理赔算法配置脚本]]

TPA理赔

核心导入

通过团险合并-->批量理赔-->第三方批量理赔-->TPA案件导入

保单要有tpa标记

-- 加上TPA理赔标记的保单才进行责任匹配
SELECT TPAFlag FROM lcgrpcont b WHERE grpcontno ='BJG11202211591';

特病提额

-- 特病导入记录
SELECT b.* FROM LBPOSpecInjuryInsuredList b where b.idno = '11010519690523411X';

限额查询

-- 获取限额   责任编码G1012  额度 25000
select b.p20,b.* from lccontplandetailsub b where policyno = 'BJG11202002505' and b.riskcode='1121101' and b.dutycode='1121101002' and plancode ='G1012';

关联到的责任

-- 关联责任
select distinct contno,customerno from llcaseduty where mappingflag = '1' and rgtno = '?RgtNo?';

第三方处理

批处理

智能理赔推送批处理-LCIntelligentClaimDataSendTask
智能理赔推解析处理-LLIntelligentClaimTask
TPA理赔自动化处理批处理 -LLTPAClaimAutoProcessingTask

前置机

-- 推送路径 
select * from ldftpmanage where systemtype IN('ZNLP','DYKJ','ZKRJ','YBSF' )  ; 
-- 案件推送三方的状态 
SELECT* FROM ldcode WHERE codetype ='clmrgtsources';

待推送数据

待推送数：
SELECT * FROM llintelligentsend a WHERE sendflag='N';

推送方案配置信息：
SELECT * FROM llclaimthirdconfig a WHERE a.GrpContNo ='BJG11202003263';  
SELECT * FROM llclaimthirdconfigsub a WHERE a.SerilaNo ='00000000000000000908'; 

三方映射码
SELECT * FROM ldcode a WHERE a.codetype ='ThirdClmCom';  

回传状态查询

-- 案件状态
SELECT l.RgtNo 案件号, l.RgtantName 申请人, l.GrpName 单位名, l.RgtDate 申请日期, l.BackDate 三方回传时间 , (SELECT codename FROM ldcode WHERE codetype ='rgtconclusion' AND code = rgtconclusion ) 回退原因 FROM llregisterlog l WHERE l.rgtno ='230526002688'; 
-- 理算问题件 
select *from llThirdQuestionInfo where rgtno ='2405095000299'; 
-- 案件回传记录表 
select * from llIntelligentClaimDetailInfo where tpargtno ='BJ-DTAF240307ZB038';

回传报错记录

SELECT * FROM llintelligentclaimerrorinfo l

理赔自动审核

-- 赔付保单配置的自动审核金额 
select concat(a.ThirdClmCom,'_',a.GrpContNo),a.AutoMaxMoney from llclaimthirdconfig a where a.IsAutoFlag = '1'
-- 自动审核错误
SELECT* FROM ltpaclaimautoprocessinginfo a WHERE a.GrpRgtNo ='BJWZN2026011400001'; 
SELECT* FROM lltpaclaimautoprocessingerror a WHERE a.GrpRgtNo='BJWZN2026011400001';

医渡云、宸夕理赔

接口

YDYClaimInsertJSON -- 医渡云案件审核结论推送接口，案件解析 ImageSupplementJSON -- 医渡云影像补传接口 YDYPayeeUpdateJSON -- 医渡云领款人更新接口 YDYCoinsuranceInfoJSON -- 理赔费用+公估费用分摊接口

接口里的一些规则

1.ydyClaimMainSchema.getapplyCom 01-医渡云 02-宸夕
-- 证件类型映射 宸汐不走医渡云的身份证码值 跟核心码值一致
select code,codealias from ldcode where codetype = 'YDYIDTYPE';
-- 影像类型映射
select code,codealias from ldcode where codetype = 'YDYSCANTYPE';
-- 关系映射
select code,codealias from ldcode where codetype = 'YDYRELATIONTYPE';
-- 职业映射
select code,codealias from ldcode where codetype = 'YDYOCCUPATION';
-- 责任映射
select concat(code,'_',code1),codealias,comcode,othersign from ldcode1 where codetype = 'YDYGETDUTYCODE'

/*
判断是否为公域保单，公域保单算一下具体的分摊比例
"00".equals(ydyClaimMainSchema.getpolicyArea())
rate = Arith.div(ydyClaimMainSchema.getindemnityShareAmount(),  
        ydyClaimMainSchema.getindemnityAmount(), 4);
*/

批量结算明细

SELECT * FROM LLThirdBalanceSub WHERE rgtno IN('2509180382PH','2406110189PH'); SELECT * FROM LLThirdBalanceSub WHERE serialno iN('CLMBLA00000000004929','CLMBLA00000000005897'); 
SELECT * FROM llthirdbalance WHERE serialno ='CLMBLA00000000005897'；

员福理赔

接口

YFLLGrpReportInsertJSON 员福团险理赔报案接口 LLGrpRegisterInsertJSON 员福团险理赔受理接口 LLCustomerRevokeJSON 员福客户撤销接口 GrpClaimDetailQueryJSON 员福团险理赔查询接口 LLGrpScheduleQueryJSON 员福团单理赔进度查询 WXClmDetailQueryJSON 员福案件详情查询接口 YFLLClmBankInfoPushJSON 员福理赔信息银行推送 LLScanIssueInfoQueryJSON 员福影像问题件查询接口 LLScanIssueReturnJSON 员福影像问题件补传接口

批处理

员福影像件消息推送补推批处理 LLScanIssueResendToYFTask 数据信息同步(员福已结案数据推送) ClosedCasePushYFTask

员福案件时效

-- 员福案件 
-- 1.没有问题件的案件: 案件申请日期   其他节点时间 参考轨迹：lbmission
SELECT l.ApplyDate,l.BackDate,l.* FROM llregisterlog l WHERE l.rgtno ='220505000488'; 
-- 2.有问题件的案件: 案件申请日期，没有客户的申请日期，只有记录三方首次回传的日期：其他节点时间 参考轨迹：lbmission
SELECT l.rgtdate FROM llgrpregister l WHERE rgtno ='BJW20220506009'; 

体检理赔

批处理

LLPEClaimTask -- 体检批量导入批处理

-- 导入清单
SELECT * FROM ldattachment a WHERE a.otherno ='86110000147109'; 
SELECT* FROM LBPOPEClmInsuredList a WHERE a.BatchNo ='86110000147109-1' AND idno IN ('110102197212090016','110224197509120814') ;

额度使用情况

-- 方案赔付额度 
SELECT * FROM lccontplandetailsub WHERE policyno ='BJG11202107671' AND sysplancode ='0000004802'; 
-- 已用额度 
select ifnull(sum(e.realpay),0) from llclaimdetail e,llregister f where e.polno='210110008917403' and e.riskcode='1121103' and e.dutycode='1121103008' and e.givetype='0' and e.clmno=f.rgtno AND '0000444594'=f.customerno and f.clmstate='60'; 
-- 账户余额 
select sum(ifnull(money,0)) from lcinsureacctrace where grpcontno = 'BJG11202107671';

体检批量结算

SELECT * FROM LLPERegisterFeeInfo WHERE GrpRgtNo='86110000026183'; 
SELECT * FROM LLThirdBalanceSub WHERE GrpRgtNo='86110000026183';-- 0-待结算 1-已结算 2-全部撤销 
SELECT * FROM llthirdbalance WHERE serialno ='CLMBLA00000000000386'; 
SELECT * FROM lwmission l WHERE l.missionprop1 ='CLMBLA00000000000386'; 
SELECT * FROM ljsget WHERE getnoticeno ='00000000000042277017'; 
SELECT * FROM ljsendtobank l WHERE l.transno = '00000000000042277017'; 
SELECT * FROM ljreturnfrombank l WHERE l.data1 ='00000000000042277017';

待结算查询

SELECT c.ThirdPartyCode,(SELECT ThirdPartyName FROM llthirdparty WHERE ThirdPartyCode = c.ThirdPartyCode),a.rgtno,b.rgtno,b.rgtantname,(SELECT SUM(d.standpay) FROM llclaimdetail d WHERE d.rgtno = b.rgtno),(SELECT SUM(d.realpay) FROM llclaimdetail d WHERE d.rgtno = b.rgtno),b.endcasedate,c.bankcode,(select headbankname from ldheadbank where headbankcode = c.bankcode),c.bankaccname,c.bankaccno FROM llgrpregister a,llregister b,LLPERegisterFeeInfo c WHERE a.rgtno = b.grprgtno AND a.rgtno = c.GrpRgtNo AND a.TPAType = 'PE' AND b.clmstate='60' 
-- -- AND NOT EXISTS (SELECT 1 FROM LLThirdBalanceSub c WHERE c.rgtno = b.rgtno AND c.state IN ('0','1')) 
and (SELECT SUM(d.realpay) FROM llclaimdetail d WHERE d.rgtno = b.rgtno)>0 AND a.rgtno ='86110000026183' group by b.rgtno;

结算查询

SELECT a.ThirdPlat,(SELECT ThirdPartyName FROM llthirdparty WHERE ThirdPartyCode = a.ThirdPlat),b.GrpRgtNo,b.rgtno,b.InsuredName,b.StandPay,b.RealPay,b.EndCaseDate FROM LLThirdBalance a, LLThirdBalanceSub b WHERE a.SerialNo = b.SerialNo AND a.state = '0' AND b.state = '0' and PEClaimFlag = '1' AND b.grprgtno ='86110000026183';

批量结算付款失败申请

-- 000008 批处理生产 1800607002 工作流 
select a.serialno,(select codename from ldcode where codetype = 'partytype' and code = othernotype),a.thirdplat,(case othernotype when '20' then (select ThirdPartyName from llthirdparty where ThirdPartyCode = a.thirdplat) when '13' then (select codename from ldcode where codetype = 'tpafrom' and code = a.thirdplat) when '21' then (select comname from ldcoinsurancecom where coinsurancecomcode = a.thirdplat) else '''' end),a.bankcode,(select bankname from ldbank where bankcode=a.bankcode),a.accname,a.bankaccno,j.sumgetmoney,j.makedate,w.missionid from llthirdbalance a ,ljsget j, lwmission w where a.getnoticeno = j.getnoticeno and a.serialno = j.otherno and w.missionprop1 = a.serialno and w.activityid = '1800607002' and (w.defaultoperator = '' or w.defaultoperator is null) and a.accname ='首都医科大学宣武医院'

健康委托管理业务

健康委托配置信息

select * from ldcode1 where codename ='healthyClaims'; -- 健康委托配置

责任起期配置

批量理赔 -> 健康委托管理配置 ->责任起期配置申请
SELECT * FROM LLGrpContHealthMandateBatch;-- 团 
SELECT * FROM LLPersonContHealthMandate ;-- 个 
SELECT * FROM llGrpContHealthMandate;-- 导入表 
SELECT * FROM LLGrpContHealthMandateTrace;

建委限额：lljwcontlimit

各种限额控制

关联公共保额：lccontplanpubamntrela

年度限额：lccontplandetailsub p20

建委限额：lljwcontlimit

社保个人限额：llpersonalclaimlimit；关联刷新表：llcontlimitrefreshinfo

体检理赔配置：llpeclaimconfig

门诊住院公用限额：lccontplan OutPaMedAllowMoney

审核信息

案件审核结论

-- 案件审核结论
SELECT * FROM llclaimuwmain l WHERE rgtno ='220905000555' l.auditconclusion ; 
-- 撤案原因
SELECT a.RevokeReason,ExamConclusion 8-客户撤案 7-公司撤销,a.rgtno FROM LLClaimUWMain a WHERE a.rgtno ='260320001988';

案件付款失败

理赔案件-->理赔付款失败处理-->账户变更申请

-- 批量导入记录
select * from LBPOClmChAccList where batchno ='20241126-7'

查询sql

select distinct a.rgtno,l.grprgtno,case when l.grprgtno is null then (select d.codename from ldcode d where d.codetype='llclaimstate' and d.code = l.clmstate) else (select d.codename from ldcode d where d.codetype='clmstate' and d.code = l.clmstate) end as state,case when l.grprgtno is null then (select '1' FROM dual) else (select '2' from dual) end as flag,case when l.grprgtno is null then a.CustomerNo else l.CustomerNo end as CustomerNo,case when l.grprgtno is NULL THEN a.CustomerName else l.rgtantname end as CustomerName,case when l.grprgtno is null then (select d.codename from ldcode d where d.codetype = 'sex' and d.code = a.CustomerSex ) else (select d.codename from ldcode d where d.codetype = 'sex' and d.code = l.rgtantsex) end as CustomerSex,'', l.endcasedate,w.LastOperator,(SELECT Name from ldcom m where m.comcode = w.OperateCom) as OperateCom,case when l.grprgtno is null then(SELECT r.standbyflag3 FROM ldbankreturnresult r,lyreturnfrombankb k,ljaget g where r.returncode = k.BankSuccFlag and k.paycode = g.actugetno and g.otherno = a.rgtno order by k.modifydate desc, k.modifytime desc limit 1) else if(EXISTS(SELECT 1 FROM ljsget WHERE state='B' AND otherno=l.rgtno), (SELECT l1.PayInfo FROM ljfundstuipiao l1,ljsget l2 WHERE l1.ActuGetNo=l2.getnoticeno AND l2.otherno=l.rgtno ORDER BY l1.MakeDate DESC, l1.MakeTime DESC LIMIT 1),(SELECT r.standbyflag3 FROM ldbankreturnresult r,ljreturnfrombank k,ljsget g WHERE r.returncode = k.returndesc AND k.data1 = g.getnoticeno AND g.otherno = a.rgtno ORDER BY k.modifydate DESC, k.modifytime DESC LIMIT 1)) END AS BankSuccFlag from llcase a,llregister l,lwmission w where a.rgtno = l.rgtno and a.rgtno = w.missionprop1 and w.activityid='1800605002' and (w.defaultoperator is null or w.defaultoperator ='') {0} {1} {2} {3} {4} {5} order by a.rgtdate,a.rgtno

团险合并一>批里结算一>其他批里结算

SELECT a.TPAFrom, (SELECT codename FROM ldcode WHERE codetype = 'TPAFrom' AND CODE = a.TPAFrom), a.rgtno,b.rgtno,b.rgtantname, (SELECT SUM(d.standpay) FROM llclaimdetail d WHERE d.rgtno = b.rgtno), (SELECT SUM(d.realpay) FROM llclaimdetail d WHERE d.rgtno = b.rgtno),b.endcasedate FROM llgrpregister a,llregister b,ydyclaimmain c WHERE a.rgtno = b.grprgtno AND b.clmstate='60' and b.rgtno = c.rgtno AND NOT EXISTS (SELECT 1 FROM LLThirdBalanceSub c WHERE c.rgtno = b.rgtno AND c.state IN ('0','1')) and (SELECT SUM(d.realpay) FROM llclaimdetail d WHERE d.rgtno = b.rgtno)>0 AND c.BalanceObj = '05';

团险合并-->批量理赔-->共保结算-->共保结算审核

-- state = '3' 审核通过 
select * from LLThirdBalance where serialno ='CLMBLA00000000029876'; 
-- 应收信息
select * from LJSPay where GetNoticeNo ='00000000000060521103';

共保案件分摊信息

- - 共保分摊待处理  
select * from llcoinsuranceshare where rgtno = '240110006788' ;
- - 共保分摊保单  
SELECT CoinsuranceParty,OwnCompanyFlag,ClaimPayMode,l.* FROM LCGrpCoinsurance l WHERE l.GrpContNo ='BJG11202312778' AND CoinsuranceParty='02' AND OwnCompanyFlag='Y' AND ClaimPayMode='01';

其他

错误或问题

团险理赔审批挂起预警

SELECT* FROM ldbussdeallock LIMIT 100 ; DELETE FROM ldbussdeallock WHERE bussno IN ('DY202502167139533','DY202502167311633');

存在超保额赔付风险

select a.clmno,a.customerno,c.customername,sum(a.realpay),ROUND((b.claimlimit-b.SumPayMoney),2),d.grprgtno,b.StartDate,b.EndDate from llclaimdetail a,llpersonalclaimlimit b,llcase c,LLregister d where a.clmno = c.rgtno and a.contno = b.contno and c.CUSTOMERNO = b.INSUREDNO and a.clmno = d.rgtno and c.accdate between b.StartDate and b.enddate and d.rgtno = '220715000399'group by a.clmno,b.contno,b.insuredno;

理赔合同自动处理

-- 团险理赔合同处理配置 g_claim\LLClaimContDeal.js * 
-- AfterGet＝003 无条件销户 * AfterGet = 004 最后一次给付销户 * AfterGet = 999 特殊处理 
select * from LMDutyGetClm where GetDutyCode='1071102401' and GetDutyKind='204'; 
SELECT* FROM lcgrpcont a WHERE a.grpcontno ='BJG32202519508';

映射

案件来源

-- llGrpRegisterSchema.setTPAFrom；
SELECT* FROM ldcode a WHERE a.codetype ='TPAFrom';

疾病药品代码

select * FROM lldisease ;-- 病 
select * FROM lldrugsfee ; -- 药品

出险类型

-- 出险类型 
SELECT SUBSTR(a.reasoncode,2),a.* from llappclaimreason a WHERE a.rgtno =''; 
"02" name=ClaimType>身故 
"04" name=ClaimType>重大疾病 
"03" name=ClaimType>高残 
"01" name=ClaimType>伤残 
"00" name=ClaimType>医疗 
"0A" name=ClaimType>津贴 
"06" name=ClaimType>失能 
"05" name=ClaimType>特定疾病 
"07" name=ClaimType>护理

团险出单渠道

select (SELECT codename from ldcode where codetype='salechannelbelong' and code=a.standbyflag1) 渠道归属, (select c.codename from ldcode c where c.codetype='salechannel' and a.ChnlType=c.code ) 渠道类型, (select c.codename from ldcodeexp c where c.codetype='agenttype' and c.codeexp=a.ChnlType and a.SaleChnl=c.code)销售渠道 FROM lcgrpcont a WHERE grpcontno = 'BJG11202001017';

理赔页面职业查询

select OccupationCode, OccupationName, OccupationType, (select codename from ldcode where codetype='occupationtype' and code=OccupationType) from LDOccupation where 1 = 1 and occupationver = '002' order by OccupationCode

理赔用户查询，核赔权限，核赔金额

-- 理赔用户查询，核赔权限，核赔金额

SELECT UserCode 用户编码,UserName 用户名称,ComCode,PersonalRank 核赔级别,
groupmedicalmoney  医疗险金额,
nogroupmedicalmoney  非医疗险金额,
GroupRemitMoney 豁免金额,
GroupAccommodationMoney 医疗险通融金额,
NoGroupAccommodationMoney 非医疗险通融金额,
GroupRefuseMoney 医疗险拒付金额,
NoGroupRefuseMoney 非医疗险拒付金额,
BatchMedicalMoney 批量医疗险金额,
NoBatchMedicalMoney 批量非医疗险金额,
BatchRemitMoney 批量豁免金额,
BatchAccommodationMoney 批量医疗险通融金额,
NoBatchAccommodationMoney 批量非医疗险通融金额,
BatchRefuseMoney 批量医疗险拒付金额,
NoBatchRefuseMoney 批量非医疗险拒付金额,
PersonalSubmitReport 是否分公司个险上报人,GroupSubmitReport 是否分公司团险上报人 
from LLCompetence a, llclaimuser b where a.competence ='1' and a.`rank` =b.GroupRank 
;


SELECT UserCode 用户编码,UserName 用户名称,ComCode,PersonalRank 核赔级别,
OneMedicalMoney  医疗险金额,
NoOneMedicalMoney  非医疗险金额,
OneRemitMoney 豁免金额,
OneAccommodationMoney 通融金额,
OneRefuseMoney 拒付金额,
PersonalSubmitReport 是否分公司个险上报人,GroupSubmitReport 是否分公司团险上报人 
from LLCompetence a, llclaimuser b where a.competence ='0' and a.`rank` =b.PersonalRank 
;