policyproinfo.txt
20.2 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
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
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,
date_add(po.apply_date,interval 8 hour) AS po_apply_date,
date_add(po.issue_date,interval 8 hour) AS po_issue_date,
date_add(po.effect_date,interval 8 hour) 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,
date_add(rnpsi.last_assign_date,interval 8 hour) 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,
date_add(pp.maturity_date,interval 8 hour) 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,
date_add(ppi.prem_due_date,interval 8 hour) 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,
date_add(ci.birthday,interval 8 hour) 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,
date_add(pvc.vip_end_date,interval 8 hour) 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,
date_add(ppp.pay_to_date,interval 8 hour) AS ppp_pay_to_date,
ppp.add_prem_term AS ppp_add_prem_term,
date_add(ppp.add_prem_eff_date,interval 8 hour) 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)
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,
date_add(po.apply_date,interval 8 hour) AS po_apply_date,
date_add(po.issue_date,interval 8 hour) AS po_issue_date,
date_add(po.effect_date,interval 8 hour) 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,
date_add(rnpsi.last_assign_date,interval 8 hour) 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,
date_add(pp.maturity_date,interval 8 hour) 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,
date_add(ppi.prem_due_date,interval 8 hour) 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,
date_add(ci.birthday,interval 8 hour) 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,
date_add(pvc.vip_end_date,interval 8 hour) 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,
date_add(ppp.pay_to_date,interval 8 hour) AS ppp_pay_to_date,
ppp.add_prem_term AS ppp_add_prem_term,
date_add(ppp.add_prem_eff_date,interval 8 hour) 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 DISTINCT policy_no
FROM policy p
WHERE p.updated_date > :sql_last_value
UNION
SELECT DISTINCT policy_no
FROM policy_contact_info pc
WHERE pc.updated_date > :sql_last_value
UNION
SELECT DISTINCT policy_no
FROM policy_product_prem ppp
WHERE ppp.updated_date > :sql_last_value
UNION
SELECT DISTINCT policy_no
FROM policy_prem_info ppi
WHERE ppi.updated_date > :sql_last_value
UNION
SELECT DISTINCT policy_no
FROM rn_policy_service_info rn
WHERE rn.updated_date > :sql_last_value
UNION
SELECT DISTINCT policy_no
FROM policy_product pp
WHERE pp.updated_date > :sql_last_value
UNION
SELECT DISTINCT policy_no
FROM gras_policy_geo gpg
WHERE gpg.updated_date > :sql_last_value)