policyproinfo.txt
9.15 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
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
SELECT nvl(po.policy_no, 'NAN') AS po_policy_no,
nvl(po.applicant_no, 'NAN') AS po_applicant_no,
nvl(po.branch_code, 'NAN') AS po_branch_code,
nvl(po.department_no, 'NAN') AS po_department_no,
nvl(po.duty_status, 'NAN') AS po_duty_status,
nvl(po.apl_option, 'NAN') AS po_apl_option,
nvl(po.channel_type, 'NAN') AS po_channel_type,
nvl(po.agent_no, 'NAN') AS po_agent_no,
nvl(po.apply_no, 'NAN') AS po_apply_no,
po.apply_date AS po_apply_date,
po.issue_date AS po_issue_date,
po.effect_date AS po_effect_date,
nvl(po.service_status, 'NAN') AS po_service_status,
nvl(po.combination_code, 'NAN') AS po_combination_code,
nvl(po.business_source, 'NAN') AS po_business_source,
nvl(pci.email_seq, 'NAN') AS po_email_seq,
nvl(cp.phone_no, 'NAN') AS po_phone_seq,
nvl((SELECT cph.phone_no
FROM client_phone cph
WHERE cph.phone_seq = pci.mobile_phone_seq),
'NAN') AS pci_mobile_phone_seq,
nvl((SELECT cph.phone_no
FROM client_phone cph
WHERE cph.phone_seq = pci.home_phone_seq),
'NAN') AS pci_home_phone_seq,
nvl((SELECT cph.phone_no
FROM client_phone cph
WHERE cph.phone_seq = pci.office_phone_seq),
'NAN') AS pci_office_phone_seq,
nvl(ca.address_type, 'NAN') AS ca_address_type,
ca.client_no AS ca_client_no,
ca.country_code AS ca_country_code,
ca.province_code AS ca_province_code,
ca.city_code AS ca_city_code,
ca.detail_address AS ca_detail_address,
ca.postalcode AS ca_postalcode,
ca.full_address AS ca_full_address,
rnpsi.agent_no AS rnspi_agent_no,
nvl(rnpsi.service_no, 'NAN') AS rnspi_service_no,
rnpsi.surpervise_no AS rnspi_surpervise_no,
rnpsi.orphan_mark AS rnpsi_orphan_mark,
rnpsi.pay_times AS rnpsi_pay_times,
rnpsi.actural_pay_times AS rnpsi_actural_pay_times,
rnpsi.assign_mode AS rnpsi_assign_mode,
rnpsi.last_assign_user AS rnpsi_last_assign_user,
rnpsi.last_assign_date AS rnpsi_last_assign_date,
nvl(rnpsi.assign_rule, 'NAN') AS rnpsi_assign_rule,
nvl(rnpsi.assign_branch_code, 'NAN') AS rnpsi_assign_branch_code,
nvl(rnpsi.assign_channel_type, 'NAN') AS rnpsi_assign_channel_type,
bi.branch_full_name AS bi_branch_full_name,
bi.branch_name AS bi_branch_name,
bi.branch_address AS bi_branch_address,
bi.phone AS bi_phone,
bi.fax AS bi_fax,
bi.postcode AS bi_postcode,
bi.email AS bi_email,
bi.branch_level AS bi_branch_level,
bi.parent_branch AS bi_parent_branch,
bi.is_valid AS bi_is_valid,
bi.service_phone AS bi_service_phone,
bi.service_address AS bi_service_address,
bi.country_code AS bi_country_code,
bi.province_code AS bi_province_code,
bi.city_code AS bi_city_code,
bi.area_code AS bi_area_code,
bi.branch_class AS bi_branch_class,
bi.circ_branch_code AS bi_circ_branch_code,
pp.prod_seq AS pp_prod_seq,
pp.product_code AS pp_product_code,
pp.insured_no AS pp_insured_no,
pp.units AS pp_units,
pp.duty_status AS pp_duty_status,
pp.product_level AS pp_product_level,
pp.base_sum_ins AS pp_base_sum_ins,
pp.dividend_sum_ins AS pp_dividend_sum_ins,
pp.coverage_period AS pp_coverage_period,
pp.maturity_date AS pp_maturity_date,
pp.special_term AS pp_special_term,
pp.renewal_permit AS pp_renewal_permit,
pp.insured_seq AS pp_insured_seq,
ppi.prem_status AS pp_prem_status,
ppi.charging_method AS pp_charging_method,
ppi.prem_source AS pp_prem_source,
ppi.frequency AS pp_frequency,
ppi.prem_due_date AS pp_prem_due_date,
ppi.modal_total_prem AS pp_modal_total_prem,
ppi.policy_balance AS pp_policy_balance,
ci.client_name AS ci_client_name,
ci.birthday AS ci_birthday,
ci.sex_code AS ci_sex_code,
ci.idno AS ci_idno,
ci.education_code AS ci_education_code,
ci.marriage_code AS ci_marriage_code,
ci.occupation_code AS ci_occupation_code,
ci.work_unit AS ci_work_unit,
ci.position AS ci_position,
ci.industry AS ci_industry,
ci.data_status AS ci_data_status,
ci.merge_to_clientno AS ci_merge_to_clientno,
pvc.vip_effect_date AS pvc_vip_effect_date,
nvl(pvc.vip_type, 'NAN') AS pvc_vip_type,
pvc.vip_grade AS pvc_vip_grade,
pvc.vip_end_date AS pvc_vip_end_date,
pvc.vip_prem_sum AS pvc_vip_prem_sum,
pvc.branch_code AS pvc_branch_code,
ch.channel_type AS ct_channel_type,
ch.description AS ct_description,
pro.abbr_name AS pro_abbr_name,
pro.full_name AS pro_full_name,
pro.ins_type AS pro_ins_type,
pro.renewal_permit AS pro_renewal_permit,
nvl(ssi.emp_name, 'NAN') AS agent_emp_name,
nvl(ssi.emp_no, 'NAN') AS agent_emp_code,
nvl(si.emp_name, 'NAN') AS service_emp_name,
nvl(si.emp_no, 'NAN') AS service_emp_no,
cci.client_no AS insured_client_no,
cci.client_name AS insured_client_name,
di.dept_no AS agent_dept_no,
di.dept_name AS agent_dept_name,
di.parent_dept AS agent_parent_dept,
di.dept_level AS agent_dept_level,
di.branch_code AS agent_branch_code,
ddi.dept_no AS service_dept_no,
ddi.dept_name AS service_dept_name,
ddi.parent_dept AS service_parent_dept,
ddi.dept_level AS service_dept_level,
ddi.branch_code AS service_branch_code,
ppp.prem_term AS ppp_prem_term,
ppp.prem_source AS ppp_prem_source,
ppp.period_prem_sum AS ppp_period_prem_sum,
ppp.ann_standard_prem AS ppp_ann_standard_prem,
ppp.ann_weak_add_prem AS ppp_ann_weak_add_prem,
ppp.ann_occu_add_prem AS ppp_ann_occu_add_prem,
ppp.period_standard_prem AS ppp_period_standard_prem,
ppp.period_occu_add_prem AS ppp_period_occu_add_prem,
ppp.period_weak_add_prem AS ppp_period_weak_add_prem,
ppp.pay_to_date AS ppp_pay_to_date,
ppp.add_prem_term AS ppp_add_prem_term,
ppp.add_prem_eff_date AS ppp_add_prem_eff_date,
ppp.prem_period_type AS ppp_prem_period_type,
ppp.sex_code AS ppp_sex_code,
ppp.ins_age AS ppp_ins_age,
ppp.occupation_code AS ppp_occupation_code,
ppp.prem_discount AS ppp_prem_discount,
ppp.payout_fee AS ppp_payout_fee,
ppp.consult_fee AS ppp_consult_fee,
nvl(gpg.lat, 0) || ',' || nvl(gpg.lon, 0) AS location,
(SELECT branch_code
FROM branch_info
WHERE is_valid = 'Y'
AND branch_level = '02'
START WITH branch_code = po.branch_code
CONNECT BY PRIOR parent_branch = branch_code) second_branch_code,
(SELECT branch_name
FROM branch_info
WHERE is_valid = 'Y'
AND branch_level = '02'
START WITH branch_code = po.branch_code
CONNECT BY PRIOR parent_branch = branch_code) second_branch_name,
(SELECT branch_code
FROM branch_info
WHERE is_valid = 'Y'
AND branch_level = '03'
START WITH branch_code = po.branch_code
CONNECT BY PRIOR parent_branch = branch_code) third_branch_code,
(SELECT branch_name
FROM branch_info
WHERE is_valid = 'Y'
AND branch_level = '03'
START WITH branch_code = po.branch_code
CONNECT BY PRIOR parent_branch = branch_code) third_branch_name
FROM policy po
LEFT JOIN policy_contact_info pci
ON pci.policy_no = po.policy_no
LEFT JOIN gras_policy_geo gpg
ON po.policy_no = gpg.policy_no
LEFT JOIN policy_product_prem ppp
ON ppp.policy_no = po.policy_no
LEFT JOIN client_address ca
ON ca.address_seq = pci.address_seq
LEFT JOIN rn_policy_service_info rnpsi
ON rnpsi.policy_no = po.policy_no
LEFT JOIN branch_info bi
ON bi.branch_code = po.branch_code
AND bi.is_valid = 'Y'
LEFT JOIN policy_product pp
ON pp.policy_no = po.policy_no
LEFT JOIN policy_prem_info ppi
ON ppi.policy_no = po.policy_no
LEFT JOIN client_information ci
ON ci.client_no = ca.client_no
LEFT JOIN pos_vip_client pvc
ON pvc.client_no = ci.client_no
AND pvc.vip_end_date IS NULL
LEFT JOIN channel_type_tbl ch
ON ch.channel_type = po.channel_type
LEFT JOIN product pro
ON pro.product_code = pp.product_code
LEFT JOIN staff_info ssi
ON ssi.emp_no = po.agent_no
LEFT JOIN staff_info si
ON si.emp_no = rnpsi.service_no
LEFT JOIN department_info di
ON di.dept_no = ssi.dept_no
LEFT JOIN department_info ddi
ON ddi.dept_no = si.dept_no
LEFT JOIN client_information cci
ON cci.client_no = pp.insured_no
LEFT JOIN client_phone cp
ON cp.phone_seq = pci.phone_seq
WHERE pp.prod_seq = ppp.prod_seq
AND po.policy_no IN (SELECT ppi2.policy_no
FROM policy_prem_info ppi2
WHERE ppi2.prem_due_date > DATE '2019-1-1')
AND po.branch_code IN (SELECT relative_branch_code
FROM branch_code_relation
WHERE branch_code IN ('8648')
AND relative_grade <= 0)