集中计费中欠费和预存对账分析及探讨

摘要:集中计费全省集中后,内控中要求了对集中计费中欠费和预存的对账,济宁分公司根据在本地网计费中的对账经验,建立模型方案,实现对账的无差异。并以某月数据举例验证方案及模型的正确性。

Abstract:With the Centralized Billing System on-line,Internal Control has the requirements of Owe checking and Stored-on account checking.Base on the experience of Local Area Telecom Network Billing System,China Unicom Jining Branch built models and the realization schemes,achieved balance of results.Data of a certain month will be presented as an example to verify the correctness of the models and the solutions.

关键字:集中计费;内控;欠费对账;预存对帐;模型

Keyword:Centralized Billing;Internal control;Owe checking;Stored-on account checking;models

中图分类号:TN876.3+2 文献标识码:A文章编号:1007-9599 (2010) 01-0000-04

集中计费全省集中[1]后,内控中要求了对集中计费中欠费和预存的对账,中国联通山东省济宁分公司根据在本地网计费[2]中的对账经验,结合集中计费中对账的新变化,基本实现了对账的无差异,解决的方案与设计情况如下:

一、模型的建立:

1.欠费部分

应收费用:按计费区域统计的营业点统计收取的费用。即统计出计费区域每个营业员收取的费用,包括收取的本营业区电话或合同号的金额,也包括其他营业区在本营业点计费的金额。

被收费用:按费用发生地域的费用。例如某本地网有n个营业区,第m个营业区(m

应收费用=被收费用

原有的欠费核对公式(均指主业):

上月欠费+本月计费收入-上月前台收款-卡销账+上月调账=本月欠费

集中计费中又涉及两类费用,

一次性费用:有营业受理后托收到收费系统得费用,此类费用客户并不再前台缴纳,而是下次与计费出账费用同时缴纳的费用。

呆坏账费用:包括每月收取的呆坏账费用;每年一次的呆坏账处理。

原有的公式需要升级和完善:

上月欠费=上月账单欠费+上月一次性费用欠费

本月欠费=本月账单欠费+本月一次性费用欠费

上月前台收款=收取的非呆坏账前台结账+银行结账+充值卡结账

本月计费收入=本月计费收入+本月倒入的一次性费用

卡销账的处理模式发生变化,去掉-卡销账部分。

2.预存部分

原有预存的核对公式(均指主业):

上月预存+本月预存变化=本月预存

集中计费后:

所有预存变化=现金预存变化+专款预存变化

专款预存变化=卡预存变化+宽带预存变化+97转入的预存变化

3.以200803月的对账为例说明主要统计过程

/******1.1***获取前台结账合计*******************/

create table lee200803_jiezhang as

SELECTmax(b.area_id) area_id,b.site_id,max(b.name) yyd,

nvl(nvl(sum(decode(e.status,'11',nvl(e.cash_count,0),0)),0)-nvl(sum(decode(e.status,'12',nvl(e.cash_count,0),0)),0),0) sj ,

nvl(nvl(sum(case when e.status='11' then nvl(e.cash_amount,0) end),0)-nvl(sum(case when e.status='12'then nvl(e.cash_amount,0) end),0),0) sc

FROM jzsf.t_site b,jzsf.t_staff c, jzsf.t_staff_check d ,jzsf.t_staff_check_detail e

WHERE d.staff_id=c.staff_id AND c.site_id=b.site_id

AND d.latn_id=537 AND d.staff_id=e.staff_id AND d.seq=e.seq

AND e.action_code'O' AND d.begin_time>=to_date('2008/03/01','yyyy/mm/dd')

AND d.begin_time

AND b.site_id not in(select distinct site_id from jzsf.t_bank_staff_id where latn_id=537)

and c.staff_id53744444

GROUP BY b.site_id

ORDER BY max(b.area_id),b.site_id

/******1.2***统计银行结账数据**************************/

create table lee200803_jiebank as

SELECT a.staff_id s_id,

max(a.staff_name) yinhang,

count(distinct c.bank_code || c.trade_group_seq) sj_cz,

nvl(sum(nvl(c.trade_amount, 0)), 0) ss

FROM jzsf.t_staffa,

jzsf.t_trade_transactionc,

jzsf.t_payment_distribution e

WHERE c.status = '0'

and a.staff_id 53744444

AND a.staff_id = c.staff_id

AND c.staff_id in (select staff_id

from jzsf.t_bank_staff_id

where latn_id = 537

and bank_code > 0)

AND c.trade_Seq = e.trade_seq

AND c.latn_id = 537

AND e.latn_id = 537

AND c.trade_date between to_date('2008/03/01', 'yyyy/mm/dd') AND

to_date('2008/03/31/23/59/59', 'yyyy/mm/dd/hh24/mi:ss')

AND e.create_date between to_date('2008/03/01', 'yyyy/mm/dd') AND

to_date('2008/03/31/23/59/59', 'yyyy/mm/dd/hh24/mi:ss')

GROUP BY a.staff_id

order by a.staff_id

select'银行', sum(sj_cz) tiaoshu,0.01*sum(ss) bank_shou from lee200803_jiebank

/********1.3本地异地充值卡******************/

select a.area_id,

max(w.area_name),

sum(case

when c.card_number like '8537%' then

nvl(a.trade_amount, 0)

else

end) 本地,

sum(case

when c.card_number not like '8537%' then

nvl(a.trade_amount, 0)

else

end) 异地

from jzsf.t_trade_transactiona,

jzsf.t_payment_distribution b,

jzsf.t_card_prepay_detail c,

jzsf.t_area w

where a.latn_id = 537

and b.latn_id = 537

and c.latn_id = 537

and a.trade_date between to_date('2008/03/01', 'yyyy/mm/dd') AND

to_date('2008/03/31/23/59/59', 'yyyy/mm/dd/hh24/mi:ss')

and b.create_date between to_date('2008/03/01', 'yyyy/mm/dd') AND

to_date('2008/03/31/23/59/59', 'yyyy/mm/dd/hh24/mi:ss')

and a.trade_Seq = b.trade_seq

and a.trade_seq = c.trade_seq

and a.status = '0'

and a.area_id = w.area_id

and a.action_code = 'N'

and a.staff_id = 20009001

group by a.area_id

/*******1.4**统计呆坏账合计**********************/

create table lee200803_daihz as

SELECTmax(b.area_id) area_id,b.site_id,max(b.name)zj_name ,

nvl(nvl(sum(decode(e.status,'11',nvl(e.cash_count,0),0)),0) -nvl(sum(decode(e.status,'12',nvl(e.cash_count,0),0)),0),0) sj ,

nvl(nvl(sum(case when e.status='11' then nvl(e.cash_amount,0) end),0)-nvl(sum(case when e.status='12'then nvl(e.cash_amount,0) end),0),0) sc

FROM jzsf.t_site b,jzsf.t_staff c, jzsf.t_staff_check d ,jzsf.t_staff_check_detail e

WHERE d.staff_id=c.staff_id AND c.site_id=b.site_id

AND d.latn_id=537 AND d.staff_id=e.staff_id AND d.seq=e.seq

AND e.action_code='O' AND d.begin_time>=to_date('2008/03/01','yyyy/mm/dd')

AND d.begin_time

AND b.site_id not in(select distinct site_id from jzsf.t_bank_staff_id where latn_id=537)

and c.staff_id53744444

GROUP BY b.site_id

ORDER BY max(b.area_id),b.site_id

/*******2**被收详单******************/

create table lee200803_beishou as

Selectp.account_id,tr.staff_id,tr.site_id,tr.area_id, tr.latn_id,tr.action_code,tr.status,

Sum(p.paid_amount-NVL(p.latefee_amount, 0)-NVL(p.sec_amount, 0)) zhuye_amount,

sum(nvl(p.latefee_amount, 0)) latefee_amount ,

sum(NVL(tr.cur_prepay, 0)+NVL(tr.cur_change, 0)-NVL(tr.orig_prepay, 0)-NVL(tr.orig_change, 0)) yucun_bianhua,

Sum(nvl(p.sec_amount, 0)) sec_amount,

Sum(tr.trade_amount) trade_amount

From jzsf.t_payment_distribution p, jzsf.t_trade_transaction tr, jzsf.t_area a

Where p.latn_id=537 And tr.latn_id=537 And a.latn_id=537

And p.create_date>(Select start_date From jzsf.t_bill_cycle Where status='1')

And tr.trade_date>(Select start_date From jzsf.t_bill_cycle Where status='1')

And tr.trade_date

And p.create_date

And p.trade_seq=tr.trade_seq

And tr.status='0'

and tr.action_code!='O'

And a.area_id=tr.area_id

and tr.staff_id53744444

Group By p.account_id,tr.staff_id,tr.site_id,tr.area_id, tr.latn_id,tr.action_code,tr.status

Order By p.account_id,tr.staff_id,tr.site_id,tr.area_id, tr.latn_id,tr.action_code,tr.status

前台结账+银行结账+充值卡结账=被收

/************预存分析************/

/********3*预存变化=现金预存变化+专款预存变化***************/

select yingyequ,

0.01*sum(nvl(yucun_bianhua, 0))yucun_bianhua,

0.01*sum(case when action_code not in ('O','E','H','J','N') then nvl(yucun_bianhua, 0) end )xianjin_bianhua,

0.01*sum(case when action_code in ( 'E','H','J','N') then nvl(yucun_bianhua, 0) end )zhuankuan_bianhua

from lee200803_beishou

group by yingyequ

order by yingyequ

/******4.1形成专款数据******/

create table lee_zhuankuan200803 as

select tr.*,a.area_name ,k.PREPAY_SOURCE_TYPE

From jzsf.t_payment_distribution subpartition(month200803_latn537) p ,

jzsf.t_trade_transaction subpartition(month200803_latn537) tr, jzsf.t_area a,

jzsf.t_special_prepay partition(latn537) k

where p.trade_seq=tr.trade_seq

And tr.status='0'

and tr.action_code in ('E','H','J','N')

And a.area_id=tr.area_id

and tr.staff_id53744444

and tr.SPEC_PREPAY_SEQ=k.SPEC_PREPAY_SEQ

/******4.2补充局向******/

alter table lee_zhuankuan200803 add exchange_code numeric(8) null

create index idx_lee_zhuankuan200803a on lee_zhuankuan200803(account_id)

update lee_zhuankuan200803 a

set a.exchange_code =(select b.exchange_code

from jzsf.t_account b

where b.latn_id=537 and b.account_id=a.account_id )

/******4.3专款分类统计******/

Selecttr.area_id,tr.area_name,

0.01*sum(NVL(tr.cur_prepay, 0)+NVL(tr.cur_change, 0)-NVL(tr.orig_prepay, 0)-NVL(tr.orig_change, 0)) yucunb_zong,

0.01*sum(case when PREPAY_SOURCE_TYPE=53700001

then (NVL(tr.cur_prepay, 0)+NVL(tr.cur_change, 0)-NVL(tr.orig_prepay, 0)-NVL(tr.orig_change, 0)) end) yucunb_card ,

0.01*sum(case when PREPAY_SOURCE_TYPE=53700002

then (NVL(tr.cur_prepay, 0)+NVL(tr.cur_change, 0)-NVL(tr.orig_prepay, 0)-NVL(tr.orig_change, 0)) end) yucunb_kuan ,

0.01*sum(case when PREPAY_SOURCE_TYPE=53700116

then (NVL(tr.cur_prepay, 0)+NVL(tr.cur_change, 0)-NVL(tr.orig_prepay, 0)-NVL(tr.orig_change, 0)) end) yucunb_97zhuan

from lee_zhuankuan200803 tr

group bytr.area_id,tr.area_name

order by tr.area_id,tr.area_name

/****5*******本月倒入的一次性费用******/

/************上月最大的NRC_IMMEDIATE_BILL_SEQ****/

select max(NRC_IMMEDIATE_BILL_SEQ)BILL_SEQ_LEE from jzsf.t_nrc_537_200801 where latn_id=537

select count(*) gs, 0.01*sum(nvl(TOTAL_PAID,0)+ nvl(TOTAL_DUE_AMOUNT,0) ) heji

fromjzsf.t_nrc_537_200802 where latn_id=537

and NRC_IMMEDIATE_BILL_SEQ> BILL_SEQ_LEE

/****************/

二、对账过程中可能的原因分析

欠费对账不平原因分析

1.销账不正确,交费异常情况,此类很少。

2.呆坏账参与计算,应单设报表。

3.隔月反销账。

4每年一次的呆坏账应分析出实际处理成功的主业和副业呆坏账。

预存对账不平原因分析

1.计费收费开始自动专款处理的开始日期不一致。

2.专款抵扣参与的账目类型不一致,有电话参与的。

3.有虚受理的宽带业务。

对欠费及对账的一些想法:

1.全收入统一管理,包括使用费、租费、一次性费用,营业款统一管理。

2.引入类似电信计费模型2.0中余额账本的概念及管理。

参考文献:

[1]集中计费系统设计,信息产业部,2004 年

[2]本地网集中计费系统设计,信息产业部,1999年

[3]山东网通计费账务作业指导手册,山东网通,2008年

[4]山东网通计费账务业务管理办法,山东网通,2008年