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
| SELECT aa."ENTITY_MEMBER_NM",
ab."LOB_MEMBER_NM",
ad."ACTUAL_SOURCE_CD",
ae."TRANS_MEMBER_NM",
af."PROV_CD",
ag."ACCYR_MEMBER_NM",
ah."CAT_NBR",
ai."POLICY_SOURCE_MEMBER_NM",
aj."KOL_CD",
CASE WHEN "TREATY_NBR" is null THEN 'NOTREATY' ELSE "TREATY_NBR" END as "TREATY_NBR",
al."NR_MEMBER_NM",
'TS_CHILD' TS_CHILD,
am."TIME_MEMBER_NM",
SUM(ac."CHG_REG_TAX_DAC_FULL_TERM") CHG_REG_TAX_DAC_FULL_TERM,
SUM(ac."CHG_REG_TAX_DAC_CURR_TERM") CHG_REG_TAX_DAC_CURR_TERM,
SUM(ac."WRITTEN_PREMIUMS_CURR_TERM") WRITTEN_PREMIUMS_CURR_TERM,
SUM(ac."REG_TAX_PAID_CURR_TERM") REG_TAX_PAID_CURR_TERM,
SUM(ac."FRE_PREM_TAX_PAID_CURR_TERM") FRE_PREM_TAX_PAID_CURR_TERM,
SUM(ac."CHANGE_IN_IBNR_INDEMNITY") CHANGE_IN_IBNR_INDEMNITY,
SUM(ac."WRITTEN_PREMIUMS_FULL_TERM") WRITTEN_PREMIUMS_FULL_TERM,
SUM(ac."CHG_UPR_FULL_TERM") CHG_UPR_FULL_TERM,
SUM(ac."CHG_DAC_FRE_TAX_FULL_TERM") CHG_DAC_FRE_TAX_FULL_TERM,
SUM(ac."CHG_DAC_FRE_TAX_CURR_TERM") CHG_DAC_FRE_TAX_CURR_TERM,
SUM(ac."CHANGE_IN_IBNR_ALAE") CHANGE_IN_IBNR_ALAE,
SUM(ac."PAID_INDEMNITY") PAID_INDEMNITY,
SUM(ac."CHANGE_IN_IBNR_ULAE") CHANGE_IN_IBNR_ULAE,
SUM(ac."CHANGE_IN_CR_INDEMNITY") CHANGE_IN_CR_INDEMNITY,
SUM(ac."PAID_REG_CMMSSNS_FULL_TERM") PAID_REG_CMMSSNS_FULL_TERM,
SUM(ac."CPC_CMMSSNS") CPC_CMMSSNS,
SUM(ac."CHANGE_IN_CR_ALAE") CHANGE_IN_CR_ALAE,
SUM(ac."OVERRIDE_CMMSSNS") OVERRIDE_CMMSSNS,
SUM(ac."PAID_HEALTH_LEVY") PAID_HEALTH_LEVY,
SUM(ac."PAID_CLAIMS_ALAE_ADJUSTER") PAID_CLAIMS_ALAE_ADJUSTER,
SUM(ac."PAID_CLAIMS_ALAE_LEGAL") PAID_CLAIMS_ALAE_LEGAL,
SUM(ac."PAID_CLAIMS_ALAE_OTHER") PAID_CLAIMS_ALAE_OTHER,
SUM(ac."PAID_ULAE_DIRECT") PAID_ULAE_DIRECT,
SUM(ac."PAID_CLAIMS_ALAE_APPRAISER") PAID_CLAIMS_ALAE_APPRAISER,
SUM(ac."PAID_ULAE_INDIRECT") PAID_ULAE_INDIRECT,
SUM(ac."PAID_REG_CMMSSNS_CURR_TERM") PAID_REG_CMMSSNS_CURR_TERM,
SUM(ac."CHG_DAC_REG_CMMSSNS_CURR_TERM") CHG_DAC_REG_CMMSSNS_CURR_TERM,
SUM(ac."LICENSES") LICENSES,
SUM(ac."PAID_EXPENSES_DIRECT") PAID_EXPENSES_DIRECT,
SUM(ac."PAID_EXPENSES_INDIRECT") PAID_EXPENSES_INDIRECT,
SUM(ac."CHG_DAC_EXPENSES_DIRECT") CHG_DAC_EXPENSES_DIRECT,
SUM(ac."CHG_DAC_EXPENSES_INDIRECT") CHG_DAC_EXPENSES_INDIRECT,
SUM(ac."CHG_DAC_REG_CMMSSNS_FULL_TERM") CHG_DAC_REG_CMMSSNS_FULL_TERM,
SUM(ac."REG_TAX_PAID_FULL_TERM") REG_TAX_PAID_FULL_TERM,
SUM(ac."FRE_PREM_TAX_PAID_FULL_TERM") FRE_PREM_TAX_PAID_FULL_TERM,
SUM(ac."CHG_UPR_CURR_TERM") CHG_UPR_CURR_TERM,
SUM(ac."PAID_SALVAGE_SUBROGATION") PAID_SALVAGE_SUBROGATION
FROM entity aa,
lob ab,
hyfi_fact ac,
source_system ad,
trans ae,
province af,
accyr ag,
catastrophy_number_list ah,
policy_source ai,
kind_of_loss aj,
treaty ak,
new_renewal al,
time am
WHERE ac."ENTITY_KEY" = aa."ENTITY_KEY"
AND ac."LOB_KEY" = ab."LOB_KEY"
AND ac."SOURCE_KEY" = ad."SOURCE_KEY"
AND ac."TRANS_KEY" = ae."TRANS_KEY"
AND ac."PROV_KEY" = af."PROV_KEY"
AND ac."ACCYR_KEY" = ag."ACCYR_KEY"
AND ac."CAT_NBR_KEY" = ah."CAT_NBR_KEY"
AND ac."POLICY_SOURCE_KEY" = ai."POLICY_SOURCE_KEY"
AND ac."KOL_KEY" = aj."KOL_KEY"
AND ac."TREATY_KEY" = ak."TREATY_KEY"
AND ac."NR_KEY" = al."NR_KEY"
AND ac."TIME_KEY" = am."TIME_KEY"
AND (((am."TIME_MEMBER_NM" in ('2006-04'))))
GROUP BY aa."ENTITY_MEMBER_NM",
ab."LOB_MEMBER_NM",
ad."SOURCE_GEN1",
ad."SOURCE_GEN2",
ad."SOURCE_MEMBER_NM",
ad."ACTUAL_SOURCE_CD",
ae."TRANS_MEMBER_NM",
af."PROV_CD",
ag."ACCYR_MEMBER_NM",
ah."CAT_NBR",
ai."POLICY_SOURCE_MEMBER_NM",
aj."KOL_CD",
ak."TREATY_NBR",
al."NR_MEMBER_NM",
am."TIME_MEMBER_NM" |
Partager