保单分配属性标记业务逻辑.txt
4.88 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
参数：
p_branch_code 机构代码
p_policy_no 保单号
p_assign_rule 分单标记 ----01正常分单，02跨机构分单，03跨渠道分单
p_assign_branch_code 分单机构代码
p_assign_channel_type 分单渠道代码
后台校验
一：只有86总公司用户才有权限执行保单分配属性标记操作
二：p_assign_rule 在01，02,03范围内
获取保单基本信息：
select p.branch_code,
(
select bi.branch_level
from branch_info bi
where bi.branch_code = p.branch_code
),
(
select bi.parent_branch
from branch_info bi
where bi.branch_code = p.branch_code
),
p.channel_type,
nvl(r.assign_rule,'01'),
nvl(r.assign_branch_code,p.branch_code),
nvl(r.assign_channel_type,p.channel_type)
into v_branch_code,v_branch_level,v_branch_parent,v_channel_type,
v_earlier_rule,v_earlier_branch_code,v_earlier_channel_type
from policy p,rn_policy_service_info r
where p.policy_no = p_policy_no
and p.policy_no = r.policy_no;
三：
1；01正常分单
if v_earlier_rule = p_assign_rule then
return
原分单规则已经为正常分单
2；02跨机构分单
2.1
select count(1) into v_cnt from branch_info bi where bi.branch_code = p_assign_branch_code;
如果机构不存在，则return
2.2
select bi.branch_level
into v_branch_level_1
from branch_info bi
where bi.branch_code = p_assign_branch_code;
if v_branch_level_1 in ('01','02') then
return 不能调整到一级或者二级机构去
2.3
if v_branch_level = '04' and v_branch_level_1 = '03' then
if v_branch_parent = p_branch_code then
保单所属机构为' || v_branch_code || '，不能调整至' || p_branch_code;
return
PS:v_branch_parent ---上级机构
2.4
--分单机构不能跟保单所属机构一样
if p_assign_branch_code = v_branch_code then
return
2.5
原规则也是02时，校验：
if v_earlier_rule = '02' then
if p_assign_branch_code = v_earlier_branch_code then
return '跨分单机构' '不能与原跨分单机构' 一致
03 跨渠道分单-个经
3.1--校验p_assign_channel_type是否存在
select count(1) into v_cnt from channel_type_tbl where channel_type = p_assign_channel_type;
不存在则return
3.2
--分单渠道不能跟保单所属渠道一样
if p_assign_channel_type = v_channel_type then
return
3.3原规则也是03时，校验：
if v_earlier_rule = '03' then
if p_assign_channel_type = v_earlier_channel_type then
跨分单渠道 不能与原跨分单机构一致
return
3.4目前仅支持个险保单跨经代分单
if v_earlier_channel_type <> '01' or p_assign_channel_type <> '03' then
return
满足校验条件则开始做跟新操作
update rn_policy_service_info a
set a.assign_rule = p_assign_rule,
a.assign_branch_code = v_next_branch_code,
a.assign_channel_type = v_next_channel_type
where a.policy_no = p_policy_no;
--保存记录
insert into rn_assign_rule_chg_record
(policy_no,
branch_code,
earlier_assign_rule,
next_assign_rule,
earlier_branch_code,
next_branch_code,
earlier_channel_type,
next_channel_type,
change_user,
change_date)
values
(p_policy_no,
v_branch_code,
v_earlier_rule,
v_next_rule,
v_earlier_branch_code,
v_next_branch_code,
v_earlier_channel_type,
v_next_channel_type,
pub_sys_package.get_user,
sysdate);
sysdate);
查询保单分配属性列表
select POLICY_NO,
BRANCH_CODE,
(select bi.branch_name from branch_info bi where bi.branch_code = a.branch_code) branchName,
EARLIER_ASSIGN_RULE,
(select DESCRIPTION from RN_ASSIGN_RULE bi where ASSIGN_RULE = EARLIER_ASSIGN_RULE) earlierAssignRuleDesc,
NEXT_ASSIGN_RULE,
(select DESCRIPTION from RN_ASSIGN_RULE bi where ASSIGN_RULE = NEXT_ASSIGN_RULE) nextAssignRuleDesc,
EARLIER_BRANCH_CODE,
(select bi.branch_name from branch_info bi where bi.branch_code = EARLIER_BRANCH_CODE) earlierBranchName,
NEXT_BRANCH_CODE,
(select bi.branch_name from branch_info bi where bi.branch_code = NEXT_BRANCH_CODE) nextBranchName,
EARLIER_CHANNEL_TYPE,
(select DESCRIPTION from channel_type_tbl bi where channel_type = EARLIER_CHANNEL_TYPE) earlierChannelDesc,
NEXT_CHANNEL_TYPE,
(select DESCRIPTION from channel_type_tbl bi where channel_type = NEXT_CHANNEL_TYPE) nextChannelDesc,
CHANGE_USER,
CHANGE_DATE
from RN_ASSIGN_RULE_CHG_RECORD a
where a.policy_no = '0170001110001162'
order by a.created_date desc