新系统bug.docx
《新系统bug.docx》由会员分享，可在线阅读，更多相关《新系统bug.docx（18页珍藏版）》请在冰点文库上搜索。
新系统bug
1.、营业收费清单融合报表：
逻辑实现及控制：
//页面初始化加载老工号所在本地网和所属市县
SELECTlanid,lanname,ppdomid,ppdomname,departid4,departname4,departid5,departname5,departid6,departname6,base_code,base_name,org_level
From
（Selectnvl（d.org_id2,-1）lanid,nvl（d.org_name2,-1）lanname,nvl（d.org_id3,-1）ppdomid,nvl（d.org_name3,-1）ppdomname,
nvl（d.org_id4,-1）departid4,nvl（d.org_name4,'全部'）departname4,
nvl（d.org_id5,-1）departid5,nvl（d.org_name5,'全部'）departname5,
nvl（d.org_id6,-1）departid6,nvl（d.org_name6,'全部'）departname6,a.base_code,a.base_name,c.org_level
Fromdim_org_relata
LeftJoinstaff_mixbOnto_char（b.staff_id）=a.base_code
LeftJoinorganization_mixcOnc.party_id=b.org_id
LeftJoinvw_organization_mixdOnd.org_id4=c.party_idOrd.org_id5=c.party_idOrd.org_id6=c.party_id
Wherea.local_code_type=12Anda.local_code='155127'）WhereRownum=1;
如果上面SQL查询出来的baseCode为空就提示“请联系管理员，未映射该工号的信息”。
根据选择的不同的级别组织进行查询：
如只选择了本地网，而且复选，其他市县，营业部等都是全部：
Selecta.bc_org_name_5,a.bc_org_name_6,a.bc_oper_name,a.accept_id,a.accept_time,
a.source_code,a.pay_type_code,a.service_code,b.prod_type_name,a.feeitem_code,
c.feeitem_name,a.pay_feefromdw_busi_fee_report_daya,DIM_FIX_PROD_TYPE
b,DIM_FIX_FEEITEMcwherea.service_code=b.prod_type_codeand
a.feeitem_code=c.feeitem_codeanda.source_code=b.source_codeand
a.source_code=c.source_codeanda.bc_org_code_2in
（6,1,2,8,5,9,16,17,11,10,13,18,7,4,12,3,14,0,376284）anda.month_no=to_number（201008）;
如果要是选择了本地网，再选择某个市县，上面SQL依次拼接,若为日报，月报什么的改变报表的类型，费用来源、收费方式即可。
ANDa.bc_org_code_3in（……..）
ANDa.bc_org_code_4in（……..）……
2、营业员收费融合报表：
SELECTa.bc_org_name_6departname,a.bc_oper_code,a.bc_oper_nameopername,
a.service_code,
（SELECTb.prod_type_nameFROMdim_fix_prod_typeb
WHEREa.service_code=b.prod_type_codeanda.source_code=b.source_codeANDROWNUM=1）service_name,
a.feeitem_code,（SELECTb.feeitem_nameFROMdim_fix_feeitemb
WHEREa.feeitem_code=b.feeitem_codeanda.source_code=b.source_codeANDROWNUM=1）feeitem_name,
SUM（CASEa.oper_typeWHEN'0'THENa.pay_numELSE0END）chargenum,
SUM（CASEa.oper_typeWHEN'1'THENa.pay_numELSE0END）refundnum,
SUM（CASEa.oper_typeWHEN'0'THENa.pay_feeELSE0END）chargefee,
SUM（CASEa.oper_typeWHEN'1'THENa.pay_feeELSE0END）refundfee,
SUM（a.pay_fee）totalfeeFromst_busi_fee_mona
WHEREa.month_no=to_char（to_date（'2010-08-0100:
00:
00','yyyy-mm-ddhh24:
mi:
ss'）,'yyyymm'）
anda.bc_org_code_2in
（1）
GroupBya.bc_org_name_6,a.bc_oper_code,a.bc_oper_name,
a.service_code,a.feeitem_code,a.source_code
UNIONALL
SELECT''departname,''bc_oper_code,'总计'opername,''service_code,''service_name,''feeitem_code,''feeitem_name,
SUM（CASEa.oper_typeWHEN'0'THENa.pay_numELSE0END）chargenum,
SUM（CASEa.oper_typeWHEN'1'THENa.pay_numELSE0END）refundnum,
SUM（CASEa.oper_typeWHEN'0'THENa.pay_feeELSE0END）chargefee,
SUM（CASEa.oper_typeWHEN'1'THENa.pay_feeELSE0END）refundfee,
SUM（a.pay_fee）totalfeeFROMst_busi_fee_mona
WHEREa.month_no=to_char（to_date（'2010-08-0100:
00:
00','yyyy-mm-ddhh24:
mi:
ss'）,'yyyymm'）
anda.bc_org_code_2in
（1）;
上述SQL也是根据选择的组织依次拼接。
3、营业厅收费融合报表：
SELECTA.BC_ORG_NAME_6DEPARTNAME,
A.BC_OPER_CODE,
A.BC_OPER_NAMEOPERNAME,
A.SERVICE_CODE,
（SELECTB.PROD_TYPE_NAME
FROMDIM_FIX_PROD_TYPEB
WHEREA.SERVICE_CODE=B.PROD_TYPE_CODE
ANDA.SOURCE_CODE=B.SOURCE_CODE
ANDROWNUM=1）SERVICE_NAME,
A.FEEITEM_CODE,
（SELECTB.FEEITEM_NAME
FROMDIM_FIX_FEEITEMB
WHEREA.FEEITEM_CODE=B.FEEITEM_CODE
ANDA.SOURCE_CODE=B.SOURCE_CODE
ANDROWNUM=1）FEEITEM_NAME,
SUM（CASEA.OPER_TYPE
WHEN'0'THEN
A.PAY_NUM
ELSE
0
END）CHARGENUM,
SUM（CASEA.OPER_TYPE
WHEN'1'THEN
A.PAY_NUM
ELSE
0
END）REFUNDNUM,
SUM（CASEA.OPER_TYPE
WHEN'0'THEN
A.PAY_FEE
ELSE
0
END）CHARGEFEE,
SUM（CASEA.OPER_TYPE
WHEN'1'THEN
A.PAY_FEE
ELSE
0
END）REFUNDFEE,
SUM（A.PAY_FEE）TOTALFEE
FROMST_BUSI_FEE_MONA
WHEREA.MONTH_NO=
TO_CHAR（TO_DATE（'2010-08-0100:
00:
00','yyyy-mm-ddhh24:
mi:
ss'）,
'yyyymm'）
ANDA.BC_ORG_CODE_2IN
（1）
GROUPBYA.BC_ORG_NAME_6,
A.BC_OPER_CODE,
A.BC_OPER_NAME,
A.SERVICE_CODE,
A.FEEITEM_CODE,
A.SOURCE_CODE
UNIONALL
SELECT'总计'DEPARTNAME,
''BC_OPER_CODE,
''OPERNAME,
''SERVICE_CODE,
''SERVICE_NAME,
''FEEITEM_CODE,
''FEEITEM_NAME,
SUM（CASEA.OPER_TYPE
WHEN'0'THEN
A.PAY_NUM
ELSE
0
END）CHARGENUM,
SUM（CASEA.OPER_TYPE
WHEN'1'THEN
A.PAY_NUM
ELSE
0
END）REFUNDNUM,
SUM（CASEA.OPER_TYPE
WHEN'0'THEN
A.PAY_FEE
ELSE
0
END）CHARGEFEE,
SUM（CASEA.OPER_TYPE
WHEN'1'THEN
A.PAY_FEE
ELSE
0
END）REFUNDFEE,
SUM（A.PAY_FEE）TOTALFEE
FROMST_BUSI_FEE_MONA
WHEREA.MONTH_NO=
TO_CHAR（TO_DATE（'2010-08-0100:
00:
00','yyyy-mm-ddhh24:
mi:
ss'）,
'yyyymm'）
ANDA.BC_ORG_CODE_2IN
（1）;
4：
营业部收费汇总融合报表：
5:
融合组织管理，已经映射组织SQL：
SELECTC.LAN_NAMEASLANNME,
B.LAN_IDASLANID,
A.DEPART_IDASDEPARTID,
'固网营业：
'||A.DEPART_NAMEASDEPARTNAME,
1ASCODESOURCE
FROMMP_DEPARTMENTA,RR_PPDOMB,RR_LANC
WHEREA.REGION_ID=B.PPDOM_ID
ANDC.LAN_ID=B.LAN_ID
ANDB.LAN_ID=1
ANDA.VALID_FLAG='0'
ANDEXISTS（SELECT1
FROMDIM_ORG_RELAT
WHERELOCAL_CODE_TYPE=2
ANDCODE_SOURCE=1
ANDLOCAL_CODE=A.DEPART_ID||''
ANDBASE_CODE=50）
UNIONALL
SELECTC.LAN_NAMEASLANNME,
B.LAN_IDASLANID,
A.SITE_IDASDEPARTID,
'固网计费：
'||A.SITE_NAMEASDEPARTNAME,
2ASCODESOURCE
FROMDIM_ORG_BILLA,DIM_REGION_BILLB,RR_LANC
WHEREA.REGION_ID=B.REGION_ID
ANDC.LAN_ID=B.LAN_ID
ANDB.LAN_ID=1
ANDEXISTS（SELECT1
FROMDIM_ORG_RELAT
WHERELOCAL_CODE_TYPE=3
ANDCODE_SOURCE=2
ANDLOCAL_CODE=A.SITE_ID||''
ANDBASE_CODE=50）
UNIONALL
SELECTB.LAN_NAMEASLANNME,
（CASECITY_CODE
WHEN'760'THEN
1
WHEN'762'THEN
2
WHEN'761'THEN
3
WHEN'769'THEN
4
WHEN'767'THEN
5
WHEN'774'THEN
6
WHEN'764'THEN
7
WHEN'763'THEN
8
WHEN'773'THEN
9
WHEN'765'THEN
10
WHEN'766'THEN
11
WHEN'772'THEN
12
WHEN'768'THEN
13
WHEN'770'THEN
14
WHEN'771'THEN
15
WHEN'777'THEN
16
WHEN'776'THEN
17
WHEN'775'THEN
18
ELSE
0
END）ASLANID,
DEALER_IDASDEPARTID,
'移网营业：
'||DEALER_NAMEASDEPARTNAME,
3ASCODESOURCE
FROMDIM_ORG_MOB,RR_LANB
WHERE1=1
ANDB.LAN_ID=（CASECITY_CODE
WHEN'760'THEN
1
WHEN'762'THEN
2
WHEN'761'THEN
3
WHEN'769'THEN
4
WHEN'767'THEN
5
WHEN'774'THEN
6
WHEN'764'THEN
7
WHEN'763'THEN
8
WHEN'773'THEN
9
WHEN'765'THEN
10
WHEN'766'THEN
11
WHEN'772'THEN
12
WHEN'768'THEN
13
WHEN'770'THEN
14
WHEN'771'THEN
15
WHEN'777'THEN
16
WHEN'776'THEN
17
WHEN'775'THEN
18
ELSE
0
END）
AND（CASECITY_CODE
WHEN'760'THEN
1
WHEN'762'THEN
2
WHEN'761'THEN
3
WHEN'769'THEN
4
WHEN'767'THEN
5
WHEN'774'THEN
6
WHEN'764'THEN
7
WHEN'763'THEN
8
WHEN'773'THEN
9
WHEN'765'THEN
10
WHEN'766'THEN
11
WHEN'772'THEN
12
WHEN'768'THEN
13
WHEN'770'THEN
14
WHEN'771'THEN
15
WHEN'777'THEN
16
WHEN'776'THEN
17
WHEN'775'THEN
18
ELSE
0
END）=1
ANDEXISTS（SELECT1
FROMDIM_ORG_RELAT
WHERELOCAL_CODE_TYPE=4
ANDCODE_SOURCE=3
ANDLOCAL_CODE=DEALER_ID||''
ANDBASE_CODE=50）
未映射的组织SQL：
SELECT（CASECITY_CODE
WHEN'760'THEN
1
WHEN'762'THEN
2
WHEN'761'THEN
3
WHEN'769'THEN
4
WHEN'767'THEN
5
WHEN'774'THEN
6
WHEN'764'THEN
7
WHEN'763'THEN
8
WHEN'773'THEN
9
WHEN'765'THEN
10
WHEN'766'THEN
11
WHEN'772'THEN
12
WHEN'768'THEN
13
WHEN'770'THEN
14
WHEN'771'THEN
15
WHEN'777'THEN
16
WHEN'776'THEN
17
WHEN'775'THEN
18
ELSE
0
END）ASLANID,
DEALER_IDASDEPARTID,
'移网营业：
'||DEALER_NAMEASDEPARTNAME,
B.LAN_NAMEASLANNME,
3ASCODESOURCE
FROMDIM_ORG_MOB,RR_LANB
WHERE1=1
ANDB.LAN_ID=（CASECITY_CODE
WHEN'760'THEN
1
WHEN'762'THEN
2
WHEN'761'THEN
3
WHEN'769'THEN
4
WHEN'767'THEN
5
WHEN'774'THEN
6
WHEN'764'THEN
7
WHEN'763'THEN
8
WHEN'773'THEN
9
WHEN'765'THEN
10
WHEN'766'THEN
11
WHEN'772'THEN
12
WHEN'768'THEN
13
WHEN'770'THEN
14
WHEN'771'THEN
15
WHEN'777'THEN
16
WHEN'776'THEN
17
WHEN'775'THEN
18
ELSE
0
END）
AND（CASECITY_CODE
WHEN'760'THEN
1
WHEN'762'THEN
2
WHEN'761'THEN
3
WHEN'769'THEN
4
WHEN'767'THEN
5
WHEN'774'THEN
6
WHEN'764'THEN
7
WHEN'763'THEN
8
WHEN'773'THEN
9
WHEN'765'THEN
10
WHEN'766'THEN
11
WHEN'772'THEN
12
WHEN'768'THEN
13
WHEN'770'THEN
14
WHEN'771'THEN
15
WHEN'777'THEN
16
WHEN'776'THEN
17
WHEN'775'THEN
18
ELSE
0
END）=1
ANDDEALER_NAMELIKE'花园路夏自军%'
ANDDEALER_ID=3760004092
ANDNOTEXISTS（SELECT1
FROMDIM_ORG_RELAT
WHERELOCAL_CODE_TYPE=4
ANDCODE_SOURCE=3
ANDLOCAL_CODE=DEALER_ID||''）
升级会员 