胡澤亮

?

實(shí)際生產(chǎn)中處理過(guò)戶導(dǎo)致的業(yè)務(wù)與財(cái)務(wù)稽核差異的一種方法

胡澤亮

中國(guó)電信河北分公司，河北 石家莊 050000

通信企業(yè)進(jìn)行業(yè)務(wù)稽核，對(duì)收入的邏輯差異進(jìn)行分析時(shí)常受到過(guò)戶數(shù)據(jù)的干擾。提供了一種簡(jiǎn)單的方法通過(guò)數(shù)據(jù)庫(kù)過(guò)濾過(guò)戶數(shù)據(jù)，在稽核工作中使用數(shù)據(jù)庫(kù)工具，提高稽核工作的信息化水平。

業(yè)務(wù)稽核；數(shù)據(jù)庫(kù)；過(guò)戶

1 概述

在通信企業(yè)的業(yè)務(wù)與財(cái)務(wù)稽核工作中，驗(yàn)證預(yù)存平衡是其中很重要的一環(huán)，最常用的邏輯差異稽核公式為：起初余額+當(dāng)月繳費(fèi)－當(dāng)月消費(fèi)=期末余額。

日常處理過(guò)程一般是細(xì)化顆粒度到賬戶級(jí)，將每個(gè)用戶套用一遍以上的公式以便找出存在邏輯差異的賬戶。但是找出的差異賬戶大量是當(dāng)月發(fā)生過(guò)戶操作，實(shí)際分析后發(fā)現(xiàn)可以正負(fù)相抵并不是真正的差異賬戶。本文介紹了一種使用數(shù)據(jù)庫(kù)腳本消除這種過(guò)戶產(chǎn)生差異的簡(jiǎn)易方法。

2 分析方法

首先我們舉例分析過(guò)戶導(dǎo)致邏輯差異的原理。

實(shí)例1：用戶A在當(dāng)月操作過(guò)戶從賬戶1轉(zhuǎn)入賬戶2，同時(shí)有10元錢也從賬戶1轉(zhuǎn)入賬戶2，那么用戶與賬戶關(guān)系表中會(huì)有以下記錄（見(jiàn)表1）。

表1

賬戶用戶是否有效變更日期 1A否20180402 2A是20180402

假定賬戶1和賬戶2下沒(méi)有其他用戶，用戶A當(dāng)月的期初余額為10，當(dāng)月也沒(méi)有繳費(fèi)和消費(fèi)，當(dāng)月邏輯差異公式為（見(jiàn)表2）。

表2

賬戶用戶期初余額繳費(fèi)消費(fèi)期末余額邏輯差異 1A1000010 2A00010﹣10

雖然賬戶1和賬戶2都有差異，但是兩個(gè)賬戶的邏輯差異正好正負(fù)相抵，差異合計(jì)為0。由此我們可以認(rèn)為通過(guò)用戶A可以找到賬戶1和賬戶2之間的關(guān)聯(lián)，賬戶1和賬戶2的集合當(dāng)月邏輯差異為0。

在這里我們引入一個(gè)賬戶組的概念，當(dāng)月有過(guò)戶關(guān)系的賬戶的合集。如果將現(xiàn)有差異按照賬戶組的細(xì)粒度進(jìn)行全量比對(duì)，就可剔除其中因過(guò)戶導(dǎo)致的合理差異。在實(shí)際生產(chǎn)中過(guò)戶關(guān)系遠(yuǎn)比以上例子復(fù)雜，可能涉及多個(gè)用戶多個(gè)賬戶多次過(guò)戶，因此我們需要將以上情況考慮周全避免引入額外的差異。

以下是復(fù)雜場(chǎng)景下的兩個(gè)例子，我們可以試著分析如何找到關(guān)聯(lián)，組成我們需要的賬戶組。

實(shí)例2：用戶A當(dāng)月有兩次過(guò)戶操作，分別是攜帶10元從賬戶1轉(zhuǎn)入賬戶2，又?jǐn)y帶20元從賬戶2轉(zhuǎn)入賬戶3，那么用戶與賬戶關(guān)系表中會(huì)有以下的記錄（見(jiàn)表3）。

表3

賬戶用戶是否有效變更日期 1A否20180402 2A否20180403 3A是20180403

假定賬戶1、賬戶2、賬戶3下沒(méi)有其他用戶，當(dāng)月也沒(méi)有繳費(fèi)和消費(fèi)，當(dāng)月邏輯差異公式為（見(jiàn)表4）。

表4

用戶期初余額繳費(fèi)消費(fèi)期末余額邏輯差異 A1000010 A1000010 A00020﹣20

實(shí)例3：用戶A當(dāng)月攜帶10元從賬戶1轉(zhuǎn)入賬戶2，用戶B當(dāng)月攜帶20元從賬戶2轉(zhuǎn)入賬戶3，那么用戶與賬戶關(guān)系表中會(huì)有以下的記錄（見(jiàn)表5）。

表5

假定賬戶1、賬戶2、賬戶3下沒(méi)有其他用戶，當(dāng)月也沒(méi)有繳費(fèi)和消費(fèi)，當(dāng)月邏輯差異公式為（見(jiàn)表6）。

表6

賬戶用戶期初余額繳費(fèi)消費(fèi)期末余額邏輯差異 1A1000010 2A、B1000010 3B00020﹣20

在實(shí)例2中雖然用戶做了兩次過(guò)戶操作，但是仍然可以通過(guò)用戶A一次性關(guān)聯(lián)到賬戶1、賬戶2、賬戶3。在實(shí)例3中則要復(fù)雜一些，先通過(guò)用戶A關(guān)聯(lián)到賬戶1和賬戶2，再通過(guò)賬戶2找到用戶B，通過(guò)用戶B發(fā)現(xiàn)賬戶2和賬戶3是關(guān)聯(lián)賬戶，最終形成賬戶1、賬戶2、賬戶3的賬戶組。

在實(shí)際生產(chǎn)中我們無(wú)法預(yù)知實(shí)例3中的迭代關(guān)聯(lián)會(huì)出現(xiàn)多少次，因此需要使用腳本窮舉，直到找齊所有的關(guān)聯(lián)賬戶。

3 編寫腳本

在通信企業(yè)中，Oracle是常見(jiàn)的數(shù)據(jù)庫(kù)，因此本文的腳本遵循Oracle數(shù)據(jù)庫(kù)的編碼格式，其他數(shù)據(jù)庫(kù)腳本可以參考修改。

首先建立中間表臨時(shí)存儲(chǔ)找到的關(guān)聯(lián)賬戶，其中ACCT_ID為起始賬戶id，SERV_ID為根據(jù)起始賬戶id找到的用戶id，ACCT_ID1為根據(jù)用戶id關(guān)聯(lián)到的賬戶id，note為迭代查找的次數(shù)。

create table HZL_TEMP0702_3

( city_code VARCHAR2(4),

ACCT_ID NUMBER(12),

SERV_ID NUMBER(12),

ACCT_ID1 NUMBER(12),

NOTE NUMBER

)

根據(jù)用戶和賬戶關(guān)系提取相關(guān)賬戶并插入中間表

declare[1]

i NUMBER ;

v1 number ;

v2 number ;

cursor c1 is

select city_code,acct_id from HZL_TEMP0702_2 group by city_code,acct_id ;

begin

for rec in c1 loop[2]

i:=0 ;

v1:=0 ;

v2:=0 ;

insert into HZL_TEMP0702_3

select t1.city_code,rec.acct_id,t1.serv_id,t1.acct_id,0

from month_serv_acct t1,month_serv_acct t2

where t1.fee_date=to_char(add_months(sysdate,-1),'yyyymm')

and t2.fee_date=to_char(add_months(sysdate,-1),'yyyymm')

and t1.city_code=rec.city_code

and t2.city_code=rec.city_code

and t2.acct_id =rec.acct_id and (to_char(t2.state_date,'yyyymm')=to_char(add_months(sysdate,-1),'yyyymm') or (t2.state='10A' and to_char(t2.state_date,'yyyymm')<=to_char(add_months(sysdate,-1),'yyyymm')))

and t2.serv_id=t1.serv_id and (to_char(t1.state_date,'yyyymm')=to_char(add_months(sysdate,-1),'yyyymm') or (t1.state='10A' and to_char(t1.state_date,'yyyymm')<=to_char(add_months(sysdate,-1),'yyyymm')))

group by t1.city_code,rec.acct_id,t1.serv_id,t1.acct_id ;

commit ;

begin

loop

i:=i+1;

insert into HZL_TEMP0702_3

select t1.city_code,rec.acct_id,t1.serv_id,t1.acct_id,i

from month_serv_acct t1,month_serv_acct t2

where t1.fee_date=to_char(add_months(sysdate,-1),'yyyymm')

and t2.fee_date=to_char(add_months(sysdate,-1),'yyyymm')

and t1.city_code=rec.city_code

and t2.city_code=rec.city_code

and t2.acct_id in (select distinct acct_id1 from HZL_TEMP0702_3 where acct_id=rec.acct_id and note=i-1) and (to_char(t2.state_date,'yyyymm')=to_char(add_months(sysdate,-1),'yyyymm') or (t2.state='10A' and to_char(t2.state_date,'yyyymm')<=to_char(add_months(sysdate,-1),'yyyymm')))

and t2.serv_id=t1.serv_id and (to_char(t1.state_date,'yyyymm')=to_char(add_months(sysdate,-1),'yyyymm') or (t1.state='10A' and to_char(t1.state_date,'yyyymm')<=to_char(add_months(sysdate,-1),'yyyymm')))

group by t1.city_code,rec.acct_id,t1.serv_id,t1.acct_id ;

commit ;

select count(1) into v2

from HZL_TEMP0702_3

where acct_id=rec.acct_id

and note=i ;

select count(1) into v1

from HZL_TEMP0702_3

where acct_id=rec.acct_id

and note=i-1 ;

exit when v2=v1 ;

end loop ;

commit ;

end ;

end loop ;

commit;

end ;

在這個(gè)腳本中我們加入了循環(huán)，循環(huán)的結(jié)束條件是找到的關(guān)聯(lián)賬戶數(shù)量不再增加，實(shí)現(xiàn)相關(guān)賬戶的窮舉。

由于最后一次循環(huán)的結(jié)果與倒數(shù)第二次的結(jié)果一致，所以需要做一次排重匯總。

create table HZL_TEMP0702_31 as

select acct_id, serv_id, acct_id1

from HZL_TEMP0702_3

group by acct_id, serv_id, acct_id1

最終HZL_TEMP0702_31這張表中ACCT_ID是起始賬戶id，ACCT_ID1是根據(jù)起始賬戶id找到的所有關(guān)聯(lián)賬戶id，形成賬戶組。我們以根據(jù)賬戶組為細(xì)粒度分組匯總已有的差異賬戶，當(dāng)賬戶組下所有賬戶邏輯差異合計(jì)為0，可以認(rèn)為是過(guò)戶導(dǎo)致的合理差異，反之則需要進(jìn)一步核實(shí)是其他什么原因?qū)е碌姆呛侠聿町悺?/p>

4 效果評(píng)估

通過(guò)以上腳本分析4月份某個(gè)地市差異，執(zhí)行前該地市以賬戶為細(xì)粒度共有差異2?958條，執(zhí)行腳本后發(fā)現(xiàn)剔除過(guò)戶導(dǎo)致的合理差異91條，如圖1所示。

圖 1

從腳本沉淀的臨時(shí)結(jié)果表hzl_temp0416_31，隨機(jī)抽取其中起始id為13169200的賬戶組進(jìn)行分析，如圖2所示。

圖 2

起始賬戶id=13169200共關(guān)聯(lián)到兩個(gè)用戶id，分別是13944547和15214681，其中用戶id=13944547的過(guò)戶操作涉及賬戶13169200、152142279、152144117，可以根據(jù)用戶與賬戶關(guān)系表中的記錄證明，如圖3所示。

圖 3

用戶id=15214681的過(guò)戶操作涉及賬戶13169200、152142810，可以根據(jù)用戶與賬戶關(guān)系表中的記錄證明，如圖4所示。

圖 4

起始賬戶id=13169200的賬戶組當(dāng)月邏輯差異公式，如圖5所示。

圖 5

圖5展示的結(jié)果表明該賬戶組下所有關(guān)聯(lián)賬戶當(dāng)月邏輯差異合計(jì)為0，證明之前第二節(jié)中的實(shí)例3是正確的。

5 結(jié)論

經(jīng)過(guò)實(shí)際驗(yàn)證可見(jiàn)該方法最終執(zhí)行結(jié)果與預(yù)期一致，滿足生產(chǎn)要求并具備可操作性，不失為一種能夠排除過(guò)戶導(dǎo)致邏輯差異的簡(jiǎn)便易行的方法。

[1]Oracle Database Online Documentation 11g Release 2 (11.2) / SQL Language Reference[Z].

[2]Expert Indexing in Oracle Database 11g[M].

A Method of Dealing with the Difference between Business and Financial Audits in Actual Production

Hu Zeliang

China Telecom Hebei Branch, Hebei Shijiazhuang 050000

Communication companies conduct business audits, and the analysis of the logical differences in revenue is often interrupted by transfer data. The paper provides a simple way to filter data through the database, use database tools in the audit work, and improve the information level of audit work.

business audit; database; transfer

F275；TP311.13

A