XXX

SELECT sr.*, nvl(sp.idx, 0) AS "idx" , nvl(sp.min_idx, 0) AS "minIdx" , nvl(sp.max_idx, 0) AS "maxIdx" , nvl(sp.risk_class_name_tail, '') AS "riskClassNameTail" FROM ( SELECT p.id_rims_treaty_prop_section_t AS "idRimsTreatyPropSection", p.section_name AS "sectionName", p.section_type AS "sectionType", l.risk_class_code AS "riskClassCode" , ( SELECT r.risk_class_name FROM rims_risk_plan r WHERE r.risk_class_code = l.risk_class_code AND rownum = 1 ) AS "riskClassName", reinsucde.pkg_rims_treaty_prop.get_operate_flag(p.treaty_no, p.id_rims_treaty_prop_section_t, l.risk_class_code) AS "operateFlag" , reinsucde.pkg_rims_treaty_prop.get_rule_is_same(p.treaty_no, p.id_rims_treaty_prop_section_t, l.risk_class_code) AS "isRuleSame" FROM rims_treaty_prop_section_t p, rims_risk_level l WHERE (EXISTS ( SELECT 1 FROM rims_treaty_prop_sect_plan_t t, rims_risk_plan r WHERE (t.id_rims_treaty_prop_section_t = p.id_rims_treaty_prop_section_t AND t.plan_code = r.plan_code AND r.risk_class_code = l.risk_class_code AND r.risk_plan_status = '02') ) AND p.treaty_no = 'QSCG1803' AND l.risk_level_status = '03' AND EXISTS ( SELECT 1 FROM rims_treaty_t t WHERE (t.treaty_no = p.treaty_no AND t.treaty_begin_date > l.effective_date AND (l.invalidate_date IS NULL OR l.invalidate_date > t.treaty_begin_date)) )) UNION SELECT p.id_rims_treaty_prop_section AS "idRimsTreatyPropSection", p.section_name AS "sectionName", 'sectionType', l.risk_class_code AS "riskClassCode" , ( SELECT r.risk_class_name FROM rims_risk_plan r WHERE r.risk_class_code = l.risk_class_code AND rownum = 1 ) AS "riskClassName", 'DELETEoperateFlag', '' FROM rims_treaty_prop_section p, rims_risk_level l WHERE (EXISTS ( SELECT 1 FROM rims_treaty_prop_section_plan t, rims_risk_plan r WHERE (t.id_rims_treaty_prop_section = p.id_rims_treaty_prop_section AND t.plan_code = r.plan_code AND r.risk_class_code = l.risk_class_code) ) AND p.treaty_no = 'QSCG1803'