清算詳細(xì)設(shè)計(jì)說(shuō)明書(shū)清算框架

1、<OTC清算子系統(tǒng)>軟件設(shè)計(jì)說(shuō)明書(shū)（清算框架）公司名稱(chēng)深圳市金證科技股份有限公司文檔編號(hào)文檔名稱(chēng)OTC-軟件設(shè)計(jì)說(shuō)明書(shū)-清算框架文檔版本0.1起 草曹永亮起草日期2013-11-15審 批<審批人>審批日期YYYY-MM-DD修訂歷史版本號(hào)日期狀態(tài)修訂人摘要0.12013-11-15C曹永亮初稿狀態(tài)標(biāo)識(shí)：C Created A- Added M - Modified D - Deleted目錄1前言51.1目的和范圍51.2背景51.3術(shù)語(yǔ)和定義51.4參考資料52清算框架設(shè)計(jì)62.1清算數(shù)據(jù)準(zhǔn)備6查詢未完成清算的TA或產(chǎn)品6數(shù)據(jù)采集準(zhǔn)備6查詢需采集數(shù)據(jù)6數(shù)據(jù)采集過(guò)程6

2、數(shù)據(jù)采集結(jié)束處理6數(shù)據(jù)導(dǎo)入準(zhǔn)備過(guò)程6查詢需導(dǎo)入數(shù)據(jù)7數(shù)據(jù)導(dǎo)入過(guò)程7數(shù)據(jù)導(dǎo)入結(jié)束處理72.2清算預(yù)處理7查詢可清算批次7查詢清算數(shù)據(jù)狀態(tài)7預(yù)處理準(zhǔn)備72.3下賬處理8查詢可導(dǎo)出數(shù)據(jù)8數(shù)據(jù)導(dǎo)出過(guò)程8查詢數(shù)據(jù)導(dǎo)出狀態(tài)8查詢可下發(fā)數(shù)據(jù)8數(shù)據(jù)下發(fā)過(guò)程8查詢數(shù)據(jù)下發(fā)狀態(tài)83數(shù)據(jù)結(jié)構(gòu)設(shè)計(jì)94接口設(shè)計(jì)95后臺(tái)類(lèi)設(shè)計(jì)105.1清算數(shù)據(jù)準(zhǔn)備10設(shè)計(jì)說(shuō)明105.2清算預(yù)處理11設(shè)計(jì)說(shuō)明115.3簿記處理12設(shè)計(jì)說(shuō)明125.4下賬處理13設(shè)計(jì)說(shuō)明131 前言1.1 目的和范圍本文對(duì)OTC清算子系統(tǒng)的清算框架的概要設(shè)計(jì)和詳細(xì)設(shè)計(jì)進(jìn)行了描述說(shuō)明，將作為清算框架編碼的依據(jù)。1.2 背景1.3 術(shù)語(yǔ)和定義1.4 參考資料l

3、金證股份OTC-清算概要設(shè)計(jì)說(shuō)明書(shū).docxl 金證股份KBSS_OTC_SETT_ORACLE.pdml 金證股份OTC_清算框架詳細(xì)設(shè)計(jì)_OOM.oom2 清算框架設(shè)計(jì)2.1 清算數(shù)據(jù)準(zhǔn)備2.1.1 數(shù)據(jù)采集準(zhǔn)備 處理流程1. 檢查入?yún)?shù)據(jù)的合法性：檢查數(shù)據(jù)字典，登記公司及產(chǎn)品代碼是否存在；2. 調(diào)用COtcSettDataPrepare:FetchProvisionBegin方法；3. 輸出執(zhí)行結(jié)果給前臺(tái)。相關(guān)SQL：當(dāng)前清算日是否有清算記錄:SELECTCOUNT(*)FROM OTC_SETT_STAT WHERE SETT_DATE =SETT_DATE存在狀態(tài)為0、

4、1取該記錄批次號(hào)：SELECTDISTINCT(SETT_BAT_NO)FROM OTC_SETT_STATWHERE SETT_DATE =SETT_DATEAND SETT_STAT IN('0','1')存在狀態(tài)不為0、1取該最大批次號(hào)+1：SELECTMAX(SETT_BAT_NO)+1FROM OTC_SETT_STATWHERE SETT_DATE =SETT_DATEAND SETT_STAT NOTIN('0','1')2.1.2 查詢需采集數(shù)據(jù) 處理流程1. 調(diào)用COtcSettFetchStat:

5、ListFetchData方法；2. 輸出查詢結(jié)果給前臺(tái)。相關(guān)SQL:SELECT A.TABLE_CODE,A.TABLE_NAME,A.SRC_SUBSYS,A.FETCH_WAY,A.SQL_COND,A.SQL_CHECK, A.EARLIEST_FETCH_TIME,A.LATEST_FETCH_FLAG,A.DUP_FETCH_FLAG,COALESCE(B.SETT_DATE,0),COALESCE(B.SETT_BAT_NO,0),COALESCE(B.LAST_FETCH_TIMESTAMP,NULL),COALESCE(B.FETCH_TIMES,0),COALESCE(B

6、.FETCH_REC_CNT,0),COALESCE(B.SRC_REC_CNT,0),COALESCE(B.DEST_REC_CNT,0),COALESCE(B.FETCH_STAT,''),COALESCE(B.CHECK_STAT,''),COALESCE(B.REMARK,''),COALESCE(B.FETCH_OPER,0)FROM OTC_SETT_FETCH_CONFIG A,OTC_SETT_FETCH_STAT BWHERE A.TABLE_CODE = B.TABLE_CODE(+)AND(B.SETT_DATE =2013

7、1123OR B.SETT_DATE ISNULL)AND(B.SETT_BAT_NO =1OR B.SETT_BAT_NO ISNULL)2.1.3 數(shù)據(jù)采集過(guò)程 處理流程1. 檢查入?yún)?shù)據(jù)的合法性；2. 調(diào)用COtcSettDataPrepare:DoDataFetch方法；3. 輸出執(zhí)行結(jié)果給前臺(tái)。處理流程圖：相關(guān)SQL：加鎖:UPDATE OTC_SETT_FETCH_STAT A SET A.FETCH_REC_CNT = A.FETCH_REC_CNTWHERE A.SETT_DATE =20131121AND A.TABLE_CODE ='OTC_MARKE

8、T'AND A.SETT_BAT_NO =1;采集狀態(tài)更新：UPDATE OTC_SETT_FETCH_STAT A SET A.FETCH_STAT ='1'WHERE A.SETT_DATE =20131121AND A.TABLE_CODE ='OTC_MARKET'AND A.SETT_BAT_NO =1AND A.FETCH_STAT <>'1'2.1.4 數(shù)據(jù)采集結(jié)束處理 處理流程1. 檢查入?yún)?shù)據(jù)的合法性；2. 調(diào)用COtcSettDataPrepare:FetchProvisionEnd方法；3.

9、 輸出執(zhí)行結(jié)果給前臺(tái)。相關(guān)SQL：按采集的狀態(tài)更新清算狀態(tài):SELECT COUNT(*) CNT FROM OTC_SETT_STAT X WHERE X.SETT_DATE = %d AND X.SETT_BAT_NO = %d AND NOT EXISTS ( SELECT 0 FROM ( SELECT COALESCE(B.SETT_DATE,%d) SETT_DATE,COALESCE(B.SETT_BAT_NO,%d)SETT_BAT_NO, COALESCE(B.FETCH_STAT,' ')FETCH_STAT ,COALESCE(B.CHECK_STAT,&

10、#39; ')CHECK_STAT FROM OTC_SETT_FETCH_CONFIG A ,OTC_SETT_FETCH_STAT B WHERE A.TABLE_CODE = B.TABLE_CODE(+) )C WHERE C.SETT_DATE = X.SETT_DATE AND C.SETT_BAT_NO = X.SETT_BAT_NO AND (C.FETCH_STAT != '%c' OR C.CHECK_STAT != '%c') )CNT為0時(shí)，按SETT_DATE,SETT_BAT_NO更新?tīng)顟B(tài)為9，否則更新為2按采集狀態(tài)和導(dǎo)入狀態(tài)

11、更新清算狀態(tài):UPDATE OTC_SETT_STAT A SET SETT_STAT ='1'WHERE A.SETT_DATE =20131121AND A.SETT_BAT_NO =1AND A.FETCH_STAT ='2'AND A.IMP_STAT ='2'2.1.5 數(shù)據(jù)導(dǎo)入準(zhǔn)備過(guò)程 處理流程1. 檢查入?yún)?shù)據(jù)的合法性；2. 調(diào)用COtcSettDataPrepare:ImpProvisionBegin方法；3. 輸出執(zhí)行結(jié)果給前臺(tái)。2.1.6 查詢需導(dǎo)入數(shù)據(jù) 處理流程1. 檢查入?yún)?shù)據(jù)的合法性；2. 調(diào)

12、用COtcSettDataStat:ListImpData方法；3. 輸出查詢結(jié)果給前臺(tái)。相關(guān)SQL:SELECT A.SETT_CFG_ID,A.MKT_CODE,A.TA_CODE,A.INST_SNO,A.INST_CLS,A.ITF_ID,A.PROVIDE_TYPE,A.DATA_TYPE, A.IMP_XML,A.ITF_TABLE,A.FILE_FORMAT,A.FILE_NAME,A.FILE_PATH,COALESCE(B.IMP_STAT,''),COALESCE(B.DEAL_STAT,'')FROM OTC_SETT_DATA_PATH

13、A,OTC_SETT_DATA_STAT BWHERE A.SETT_CFG_ID = B.SETT_CFG_ID(+)AND A.ITF_ID = B.ITF_ID(+)AND(B.SETT_DATE =20131123OR B.SETT_DATE ISNULL)AND(B.SETT_BAT_NO =1OR B.SETT_BAT_NO ISNULL)AND(B.DEAL_STAT IN('0','1','2')OR B.DEAL_STAT ISNULL);2.1.7 數(shù)據(jù)導(dǎo)入過(guò)程 處理流程1. 檢查入?yún)?shù)據(jù)的合法性；2. 調(diào)用CO

14、tcSettDataPrepare:DoDataImp方法；3. 輸出執(zhí)行結(jié)果給前臺(tái)。相關(guān)SQL：加鎖：UPDATE OTC_SETT_DATA_STAT A SET A.IMP_CNT = A.IMP_CNT WHERE A.SETT_CFG_ID =1AND A.SETT_DATE =20131121AND A.SETT_BAT_NO =1AND A.ITF_ID ='0_01'更新導(dǎo)入狀態(tài):UPDATE OTC_SETT_DATA_STAT A SET A.IMP_STAT ='1',A.DEAL_STAT ='0'WHERE A.SETT

15、_CFG_ID =1AND A.SETT_DATE =20131121AND A.SETT_BAT_NO =1AND A.ITF_ID ='0_01'AND A.IMP_STAT <>'1'AND A.DEAL_STAT IN('0','1','2')處理流程：2.1.8 數(shù)據(jù)導(dǎo)入結(jié)束處理 處理流程1. 檢查入?yún)?shù)據(jù)的合法性；2. 調(diào)用COtcSettDataPrepare:ImpProvisionEnd方法；3. 輸出執(zhí)行結(jié)果給前臺(tái)。相關(guān)SQL：SELECT COUNT(*) CNT F

16、ROM OTC_SETT_STAT X,OTC_SETT_CONFIG FWHERE X.SETT_CFG_ID = F.SETT_CFG_ID(+)AND X.SETT_DATE = F.SETT_DATE AND NOT EXISTS ( SELECT 0 FROM ( SELECT COALESCE(B.SETT_DATE,0) SETT_DATE,COALESCE(B.SETT_BAT_NO,0)SETT_BAT_NO, COALESCE(B.SETT_CFG_ID,0)SETT_CFG_ID,COALESCE(B.IMP_STAT,' ')IMP_STAT FROM

17、OTC_SETT_DATA_PATH A, OTC_SETT_DATA_STAT B WHERE A.SETT_CFG_ID = B.SETT_CFG_ID(+) AND A.ITF_ID = B.ITF_ID(+) )C WHERE (C.SETT_DATE = X.SETT_DATE OR C.SETT_DATE = 0) AND (C.SETT_BAT_NO = X.SETT_BAT_NO OR C.SETT_BAT_NO =0) AND (C.SETT_CFG_ID = X.SETT_CFG_ID OR C.SETT_CFG_ID =0) AND (C.IMP_STAT != '

18、;%c') )CNT為0時(shí)，按SETT_DATE,SETT_BAT_NO,SETT_CFG_ID更新?tīng)顟B(tài)為9,否則更新為2按采集狀態(tài)和導(dǎo)入狀態(tài)更新清算狀態(tài):UPDATE OTC_SETT_STAT A SET SETT_STAT ='1'WHERE A.SETT_DATE =20131121AND A.SETT_BAT_NO =1AND A.SETT_CFG_ID =1AND A.FETCH_STAT ='2'AND A.IMP_STAT ='2'2.2 清算預(yù)處理2.2.1 查詢可清算批次 處理流程1. 檢查入?yún)?shù)據(jù)的合法

19、性；2. 調(diào)用COtcSettPreDealBase:ListSettBatch方法；3. 輸出查詢結(jié)果給前臺(tái)。相關(guān)SQL：查詢可預(yù)處理批次:SELECT*FROM OTC_SETT_STAT AWHERE A.SETT_STAT IN('1','2') AND EXISTS ( SELECT 0 FROM OTC_SETT_CONFIG B WHERE A.SETT_CFG_ID = B.SETT_CFG_ID AND A.SETT_DATE = B.SETT_DATE)2.2.2 查詢清算數(shù)據(jù)狀態(tài) 處理流程1. 檢查入?yún)?shù)據(jù)的合法性；2. 調(diào)用

20、COtcSettDataStat:List方法；3. 輸出查詢結(jié)果給前臺(tái)。SQL：SELECT C.SETT_CFG_ID, C.MKT_CODE,COALESCE(D.EXT_TA_CODE,'') AS EXT_TA_CODE,COALESCE(A.INST_SNO,0) AS INST_SNO,COALESCE(E.EXT_ISS_CODE,'') AS EXT_ISS_CODE,COALESCE(A.INST_CODE,'') AS INST_CODE,COALESCE(A.INST_ID,'') AS INST_ID,C

21、.INST_CLS,C.SETT_DATE,C.TRD_DATE,C.SETT_BAT_NO, C.ITF_ID,C.IMP_CNT,C.SUCCESS_CNT,C.FAIL_CNT,C.NO_DEAL_CNT, C.DEAL_STAT,C.IMP_STAT FROM OTC_SETT_DATA_STATC ,OTC_INST_BASE_INFO A ,OTC_INST_ORG D,OTC_INST_ORG E WHERE C.INST_SNO = A.INST_SNO(+) AND C.TA_CODE = D.INST_ORG_SNO(+) AND A.ISS_CODE = E.INST_O

22、RG_SNO(+) AND C.SETT_DATE = SETT_DATE AND C.SETT_BAT_NO = SETT_BAT_NO ORDER BY C.SETT_CFG_ID,C.ITF_ID;2.2.3 預(yù)處理準(zhǔn)備 處理流程1. 檢查入?yún)?shù)據(jù)的合法性；2. 調(diào)用COtcSettPreDealBase:PreprocessBegin方法；3. 輸出執(zhí)行結(jié)果給前臺(tái)。2.3 下賬處理2.3.1 查詢可導(dǎo)出數(shù)據(jù) 處理流程1. 檢查入?yún)?shù)據(jù)的合法性；2. 調(diào)用COtcSettExpStat:ListData4Exp方法；3. 輸出查詢結(jié)果給前臺(tái)。SQL：SELEC

23、T B.SETT_CFG_ID,B.MKT_CODE,COALESCE(D.EXT_TA_CODE,'') AS EXT_TA_CODE,COALESCE(A.INST_SNO,0) AS INST_SNO,COALESCE(E.EXT_ISS_CODE,'') AS EXT_ISS_CODE,COALESCE(A.INST_CODE,'') AS INST_CODE,COALESCE(A.INST_ID,'') AS INST_ID,C.INST_CLS,B.ITF_ID,B.SETT_REL_TYPE,B.EXP_TIME_T

24、YPE,B.EXP_XML,B.FILE_FORMAT,B.FILE_NAME,B.FILE_PATH,B.EXTRA_LBM_PARA,COALESCE(C.SETT_DATE,0)SETT_DATE,COALESCE(C.SETT_BAT_NO,0)SETT_BAT_NO,COALESCE(C.EXP_CNT,0)EXP_CNT ,COALESCE(C.EXP_STAT,'')EXP_STAT,COALESCE(C.EXP_TIME,0)EXP_TIME,COALESCE(C.REMARK,'')REMARK ,COALESCE(C.EXP_OPER,0)E

25、XP_OPER FROM OTC_SETT_EXP_STAT C,OTC_SETT_EXP_PATH B ,OTC_INST_BASE_INFO A ,OTC_INST_ORG D,OTC_INST_ORG EWHERE C.SETT_CFG_ID(+)= B.SETT_CFG_IDAND C.ITF_ID(+)= B.ITF_IDAND B.INST_SNO = A.INST_SNO(+)AND B.TA_CODE = D.INST_ORG_SNO(+)AND A.ISS_CODE = E.INST_ORG_SNO(+)ANDEXISTS(SELECT0FROM OTC_SETT_STAT

26、F ,OTC_SETT_CONFIG G WHERE F.SETT_CFG_ID = G.SETT_CFG_ID(+) AND F.SETT_DATE =G.SETT_DATEAND C.SETT_CFG_ID = F.SETT_CFG_IDAND C.SETT_BAT_NO = F.SETT_BAT_NOAND C.SETT_DATE = F.SETT_DATEAND(B.EXP_TIME_TYPE ='0'AND F.FETCH_STAT ='2')OR(B.EXP_TIME_TYPE ='1'AND F.SETT_STAT ='2&

27、#39;)OR(B.EXP_TIME_TYPE ='2'AND F.SETT_STAT ='5')OR(B.EXP_TIME_TYPE ='9');2.3.2 數(shù)據(jù)導(dǎo)出過(guò)程 處理流程1. 檢查入?yún)?shù)據(jù)的合法性；2. 調(diào)用COtcSettExpStat:DoDataExp方法；3. 輸出執(zhí)行結(jié)果給前臺(tái)。2.3.3 查詢數(shù)據(jù)導(dǎo)出狀態(tài) 處理流程1. 檢查入?yún)?shù)據(jù)的合法性；2. 調(diào)用COtcSettExpStat:ListDataExpStat方法；3. 輸出查詢結(jié)果給前臺(tái)。SQL:SELECT C.SETT_CFG_ID,

28、C.MKT_CODE,COALESCE(D.EXT_TA_CODE,'')AS EXT_TA_CODE,COALESCE(A.INST_SNO,0)AS INST_SNO,COALESCE(E.EXT_ISS_CODE,'')AS EXT_ISS_CODE,COALESCE(A.INST_CODE,'')AS INST_CODE,COALESCE(A.INST_ID,'')AS INST_ID,C.INST_CLS,C.SETT_DATE,C.SETT_BAT_NO, C.ITF_ID,C.EXP_CNT,C.DEAL_STAT,

29、C.EXP_STAT,C.REMARK,C.EXP_TIME,C.EXP_OPERFROM OTC_SETT_EXP_STAT C ,OTC_INST_BASE_INFO A ,OTC_INST_ORG D,OTC_INST_ORG EWHERE C.INST_SNO = A.INST_SNO(+)AND C.TA_CODE = D.INST_ORG_SNO(+)AND A.ISS_CODE = E.INST_ORG_SNO(+)AND(0=SETT_CFG_ID OR C.SETT_CFG_ID =SETT_CFG_ID)AND(0=SETT_DATE OR C.SETT_DATE =SET

30、T_DATE)AND(0=SETT_BAT_NO OR C.SETT_BAT_NO =SETT_BAT_NO)ORDERBY C.MKT_CODE,C.TA_CODE,C.INST_SNO,C.INST_CLS;2.3.4 查詢可下發(fā)數(shù)據(jù) 處理流程1. 檢查入?yún)?shù)據(jù)的合法性；2. 調(diào)用COtcSettDispatchStat:ListData4Dispatch方法；3. 輸出查詢結(jié)果給前臺(tái)。SQL：SELECT B.TABLE_CODE,B.TABLE_NAME,B.SETT_CFG_ID,B.TARGET_SUBSYS,B.BK_DATE_FIELD,B.DISPATCH_TY

31、PE,COALESCE(C.SETT_DATE,0) SETT_DATE,COALESCE(C.SETT_BAT_NO,0)SETT_BAT_NO,COALESCE(C.DISPATCH_STAT,'')DISPATCH_STAT,COALESCE(C.DEAL_STAT,'')DEAL_STATFROM OTC_SETT_DISPATCH_STAT C,OTC_SETT_DISPATCH B WHERE C.TABLE_CODE(+)= B.TABLE_CODEANDEXISTS(SELECT0FROM OTC_SETT_STAT F WHERE C.SETT

32、_BAT_NO = F.SETT_BAT_NOAND C.SETT_DATE = F.SETT_DATEAND F.SETT_DATE =20131122AND F.SETT_BAT_NO =1AND F.SETT_STAT ='5');2.3.5 數(shù)據(jù)下發(fā)過(guò)程 處理流程1. 檢查入?yún)?shù)據(jù)的合法性；2. 調(diào)用COtcSettDispatchStat:DoDataDispatch方法；3. 輸出執(zhí)行結(jié)果給前臺(tái)。2.3.6 查詢數(shù)據(jù)下發(fā)狀態(tài) 處理流程1. 檢查入?yún)?shù)據(jù)的合法性；2. 調(diào)用COtcSettDispatchStat:ListDataDispa

33、tchStat方法；3. 輸出查詢結(jié)果給前臺(tái)。SQL:SELECT A.TABLE_CODE,A.TABLE_NAME,A.SETT_DATE,A.SETT_BAT_NO, A.TARGET_SUBSYS,A.DEAL_STAT,A.DISPATCH_STAT FROM OTC_SETT_DISPATCH_STAT A2.3.7 下賬準(zhǔn)備 處理流程1. 檢查入?yún)?shù)據(jù)的合法性；2. 調(diào)用COtcSettExtBK:ExtBKBegin方法；3. 輸出執(zhí)行結(jié)果給前臺(tái)。SQL:檢查數(shù)據(jù)下發(fā)是否全部完成：SELECTCOUNT(*)FROM(SELECTCOALESCE(B.DISPAT

34、CH_STAT,'')DISPATCH_STAT FROM OTC_SETT_DISPATCH A ,OTC_SETT_DISPATCH_STAT BWHERE A.TABLE_CODE = B.TABLE_CODE(+)AND B.SETT_DATE =20131122AND B.SETT_BAT_NO =1)CWHERE C.DISPATCH_STAT <>'1' COUNT > 0,則沒(méi)全部完成2.3.8 下賬處理(業(yè)務(wù)子系統(tǒng)) 處理流程1. 檢查入?yún)?shù)據(jù)的合法性；2. 調(diào)用COtcSettExtBK:DoExtBK方法；3

35、. 輸出執(zhí)行結(jié)果給前臺(tái)。2.3.9 下賬結(jié)束處理 處理流程1. 檢查入?yún)?shù)據(jù)的合法性；2. 調(diào)用COtcSettExtBK:ExtBKEnd方法；3. 輸出執(zhí)行結(jié)果給前臺(tái)。SQL:所有下賬處理都成功時(shí)，修改對(duì)應(yīng)清算配置的清算狀態(tài)為已經(jīng)完成下賬UPDATE OTC_SETT_STAT X SET SETT_STAT ='6'WHERE X.SETT_DATE =20131122AND X.SETT_BAT_NO =1ANDNOTEXISTS(SELECT0FROM(SELECT B.SETT_DATE,B.SETT_BAT_NO,COALESCE(B.DEAL_ST

36、AT,'')DEAL_STAT FROM OTC_SETT_DISPATCH A ,OTC_SETT_DISPATCH_STAT BWHERE A.TABLE_CODE = B.TABLE_CODE(+)CWHERE C.SETT_DATE = X.SETT_DATE AND C.SETT_BAT_NO = X.SETT_BAT_NOAND C.DEAL_STAT <>'1');2.4 清算狀態(tài)變遷參見(jiàn)OTC_清算框架詳細(xì)設(shè)計(jì)_OOM.oom清算狀態(tài)部分。3 數(shù)據(jù)結(jié)構(gòu)設(shè)計(jì)數(shù)據(jù)字段定義和表結(jié)構(gòu)設(shè)計(jì)參見(jiàn)KBSS_OTC_SETT_ORACLE.pdm中清算框架部分。4 接口設(shè)計(jì)LBM接口定義請(qǐng)參考sys_lbm_清算框架.xls文檔。5