public override DataSet GetData(List <reportParm> parms)
{
var resultsTable = new DataSet();
using (var conn = new SqlConnection(defaultConnection))
{
string sql = string.Empty;
sql += $@"
WITH TEMPT AS --期末
(
Select * from
IFRS9_Bond_Report
WHERE Report_Date = @Final
AND IAS39_CATEGORY = @IAS39_CATEGORY
),
TEMPT1 AS --期初
(
Select * from
IFRS9_Bond_Report
WHERE Report_Date = @Initial
AND IAS39_CATEGORY = @IAS39_CATEGORY
),
TEMPI AS --表3
(
Select
T.IAS39_CATEGORY AS IAS39_CATEGORY_T,--期末公報分類(期末E)
T1.IAS39_CATEGORY AS IAS39_CATEGORY_T1, --期初公報分類(期初E)
T.Impairment_Stage AS Impairment_Stage_T, --期末減損階段(U)
T1.Impairment_Stage AS Impairment_Stage_T1, --期初減損階段(T)
T1.Principal_EL_Ex AS Principal_EL_Ex_T1_3, --期初累計減損_本金(期初報表日匯率台幣)(表3-AG)
--期初剩餘部位之期末ECL(報表日匯率台幣)(表3-AR) = (表3-AG)-(表3-AN)-(表3-AP)
CASE WHEN ISNULL(T.Ori_Amount,0) = 0
THEN T1.Principal_EL_Ex
ELSE 0
END AS Excluding_Monetary_Assets_ECL_Ex_3, --除列之金融資產ECL(期初報表日匯率台幣)(表3-AN)
CASE WHEN (T.Ori_Amount > 0 AND (T.Ori_Amount - T1.Ori_Amount) < 0)
THEN T1.Principal_EL_Ex * ABS( (T.Ori_Amount - T1.Ori_Amount) / T1.Ori_Amount )
ELSE 0
END AS Section_Excluding_Monetary_Assets_ECL_Ex_3 --部分除列之金融資產ECL(報表日匯率台幣)(表3-AP)
from
TEMPT1 AS T1
left join
TEMPT AS T
ON T1.Bond_Number =
CASE WHEN T.ISIN_Changed_Ind = 'Y'
THEN T.Bond_Number_Old
ELSE T.Bond_Number
END
AND T1.Lots =
CASE WHEN T.ISIN_Changed_Ind = 'Y'
THEN T.Lots_Old
ELSE T.Lots
END
AND T1.Portfolio_Name =
CASE WHEN T.ISIN_Changed_Ind = 'Y'
THEN T.Portfolio_Name_Old
ELSE T.Portfolio_Name
END
),
TEMPF AS --表4
(
Select
T.IAS39_CATEGORY AS IAS39_CATEGORY_T,--期末公報分類(期末E)
T1.IAS39_CATEGORY AS IAS39_CATEGORY_T1, --期初公報分類(期初E)
T.Impairment_Stage AS Impairment_Stage_T, --期末減損階段(U)
T1.Impairment_Stage AS Impairment_Stage_T1, --期初減損階段(T)
T.Principal_EL_Ex AS Principal_EL_Ex_T_4, --本期累計減損_本金(報表日匯率台幣)(表4-AK)
CASE WHEN ISNULL(T1.Ori_Amount,0) = 0
THEN T.Principal_EL_Ex
ELSE 0
END AS Purchase_Monetary_Assets_ECL_Ex_4, --新購入之金融資產ECL(報表日匯率台幣)(表4-AN)
CASE WHEN (T1.Ori_Amount > 0 AND (T.Ori_Amount - T1.Ori_Amount) > 0)
THEN T.Principal_EL_Ex * (T.Ori_Amount - T1.Ori_Amount) / T1.Ori_Amount
ELSE 0
END AS Section_Purchase_Monetary_Assets_ECL_Ex_4, --部分新增之金融資產ECL(報表日匯率台幣)(表4-AP)
--期初剩餘部位之期末ECL(報表日匯率台幣)(表4-AR) = (表4-AK)-(表4-AN)-(表4-AP)
T1.Ex_rate AS Ex_rate_T1_4, --期初報表日匯率(表4-AC)
T.Principal_EL_Ori AS Principal_EL_Ori_T_4, --本期累計減損_本金(原幣)(表4-AI)
CASE WHEN ISNULL(T1.Ori_Amount,0) = 0
THEN T.Principal_EL_Ori
ELSE 0
END AS Purchase_Monetary_Assets_ECL_Ori_4, --新購入之金融資產ECL(原幣)(表4-AM)
CASE WHEN (T1.Ori_Amount > 0 AND (T.Ori_Amount - T1.Ori_Amount) > 0)
THEN T.Principal_EL_Ori * (T.Ori_Amount - T1.Ori_Amount) / T1.Ori_Amount
ELSE 0
END AS Section_Purchase_Monetary_Assets_ECL_Ori_4 --部分新增之金融資產ECL(原幣)(表4-AO)
--期初剩餘部位之期末ECL(原幣)(表4-AQ) = (表4-AI)-(表4-AM)-(表4-AO)
--期初剩餘部位之期末ECL(期初報表日匯率台幣) (表4-AS) = (表4-AQ)*(表4-AC)
--匯兌變動影響(表4-AT) = (表4-AR)-(表4-AS)
from
TEMPT AS T
left join
TEMPT1 AS T1
ON T1.Bond_Number =
CASE WHEN T.ISIN_Changed_Ind = 'Y'
THEN T.Bond_Number_Old
ELSE T.Bond_Number
END
AND T1.Lots =
CASE WHEN T.ISIN_Changed_Ind = 'Y'
THEN T.Lots_Old
ELSE T.Lots
END
AND T1.Portfolio_Name =
CASE WHEN T.ISIN_Changed_Ind = 'Y'
THEN T.Portfolio_Name_Old
ELSE T.Portfolio_Name
END
)
,
TEMP1 AS
(
Select
IAS39_CATEGORY_T,--期末公報分類(期末E)
IAS39_CATEGORY_T1, --期初公報分類(期初E)
Impairment_Stage_T, --期末減損階段(U)
Impairment_Stage_T1, --期初減損階段(T)
Principal_EL_Ex_T1_3, --期初累計減損_本金(期初報表日匯率台幣)(表3-AG)
Principal_EL_Ex_T1_3 - Excluding_Monetary_Assets_ECL_Ex_3 - Section_Excluding_Monetary_Assets_ECL_Ex_3
AS Final_Remaining_Part_ECL_Ex_3, --期初剩餘部位之期末ECL(報表日匯率台幣)(表3-AR) = (表3-AG)-(表3-AN)-(表3-AP)
Excluding_Monetary_Assets_ECL_Ex_3, --除列之金融資產ECL(期初報表日匯率台幣)(表3-AN)
Section_Excluding_Monetary_Assets_ECL_Ex_3 --部分除列之金融資產ECL(報表日匯率台幣)(表3-AP)
from TEMPI
)
,
TEMP2 AS
(
Select
IAS39_CATEGORY_T,--期末公報分類(期末E)
IAS39_CATEGORY_T1, --期初公報分類(期初E)
Impairment_Stage_T, --期末減損階段(U)
Impairment_Stage_T1, --期初減損階段(T)
Purchase_Monetary_Assets_ECL_Ex_4, --新購入之金融資產ECL(報表日匯率台幣)(表4-AN)
Section_Purchase_Monetary_Assets_ECL_Ex_4, --部分新增之金融資產ECL(報表日匯率台幣)(表4-AP)
(Principal_EL_Ori_T_4 - Purchase_Monetary_Assets_ECL_Ori_4 - Section_Purchase_Monetary_Assets_ECL_Ori_4) *
--期初剩餘部位之期末ECL(原幣)(表4-AQ) = (表4-AI)-(表4-AM)-(表4-AO)
Ex_rate_T1_4 AS Initial_Remaining_Part_ECL_Ex_Rate_4,
--期初剩餘部位之期末ECL(期初報表日匯率台幣) (表4-AS) = (表4-AQ)*(表4-AC)
(Principal_EL_Ex_T_4 - Purchase_Monetary_Assets_ECL_Ex_4 - Section_Purchase_Monetary_Assets_ECL_Ex_4) -
--期初剩餘部位之期末ECL(報表日匯率台幣)(表4-AR) = (表4-AK)-(表4-AN)-(表4-AP)
((Principal_EL_Ori_T_4 - Purchase_Monetary_Assets_ECL_Ori_4 - Section_Purchase_Monetary_Assets_ECL_Ori_4) * Ex_rate_T1_4)
AS Exchange_Changes_4
--匯兌變動影響(表4-AT) = (表4-AR)-(表4-AS)
from TEMPF
)
Select
--期初餘額(5)
(Select SUM(TEMP1.Principal_EL_Ex_T1_3) from TEMP1 where TEMP1.Impairment_Stage_T1 = '1') AS E5,
--'(T-1期)Impairment_Stage = 1 期初累計減損_本金(期初報表日匯率台幣)(T - 1期)Principal_EL_Ex 報表3中，E欄 = AC且T欄 = 1之AG欄加總'
(Select SUM(TEMP1.Principal_EL_Ex_T1_3) from TEMP1 where TEMP1.Impairment_Stage_T1 = '2') AS G5,
--'(T-1期)Impairment_Stage=2 期初累計減損_本金(期初報表日匯率台幣) (T-1期)Principal_EL_Ex 報表3中，E欄=AC且T欄=2之AG欄加總'
(Select SUM(TEMP1.Principal_EL_Ex_T1_3) from TEMP1 where TEMP1.Impairment_Stage_T1 = '3') AS H5,
--'(T-1期)Impairment_Stage=3 期初累計減損_本金(期初報表日匯率台幣) (T-1期)Principal_EL_Ex 報表3中，E欄=AC且T欄=3之AG欄加總'
--因期初已認列之金融工具所產生之變動：(6)
-- 轉為存續期間預期信用損失(7)
(Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) * -1 from TEMP1 where TEMP1.Impairment_Stage_T1 = '1' and TEMP1.Impairment_Stage_T = '2') AS E7,
--'(減項) 加總報表3中符合T-1期Impairment_Stage=1且T期Impairment_Stage=2條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中，E欄=AC、T欄=1、U欄=2之AR欄加總'
(Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) from TEMP1 where TEMP1.Impairment_Stage_T1 IN('1', '3') and TEMP1.Impairment_Stage_T = '2') AS G7,
--'(加項) 加總報表3中符合T-1期Impairment_Stage=1 or 3且T期Impairment_Stage=2條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中，E欄=AC、T欄=1 or 3、U欄=2之AR欄加總'
(Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) * -1 from TEMP1 where TEMP1.Impairment_Stage_T1 = '3' and TEMP1.Impairment_Stage_T = '2') AS H7,
--'(減項) 加總報表3中符合T-1期Impairment_Stage=3且T期Impairment_Stage=2條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中，E欄=AC、T欄=3、U欄=2之AR欄加總'
-- 轉為信用減損金融資產(8)
(Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) * -1 from TEMP1 where TEMP1.Impairment_Stage_T1 = '1' and TEMP1.Impairment_Stage_T = '3') AS E8,
--'(減項) 加總報表3中符合T-1期Impairment_Stage=1且T期Impairment_Stage=3條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中，E欄=AC、T欄=1、U欄=3之AR欄加總'
(Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) * -1 from TEMP1 where TEMP1.Impairment_Stage_T1 = '2' and TEMP1.Impairment_Stage_T = '3') AS G8,
--'(減項) 加總報表3中符合T-1期Impairment_Stage=2且T期Impairment_Stage=3條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中，E欄=AC、T欄=2、U欄=3之AR欄加總'
(Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) from TEMP1 where TEMP1.Impairment_Stage_T1 IN('1', '2') and TEMP1.Impairment_Stage_T = '3') AS H8,
--'(加項) 加總報表3中符合T-1期Impairment_Stage=1 or 2且T期Impairment_Stage=3條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中，E欄=AC、T欄=1 or 2、U欄=3之AR欄加總'
-- 轉為12個月預期信用損失(9)
(Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) from TEMP1 where TEMP1.Impairment_Stage_T1 IN('2', '3') and TEMP1.Impairment_Stage_T = '1') AS E9,
--'(加項) 加總報表3中符合T-1期Impairment_Stage=2 or 3且T期Impairment_Stage=1條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中，E欄=AC、T欄=2 or 3、U欄=1之AR欄加總'
(Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) * -1 from TEMP1 where TEMP1.Impairment_Stage_T1 = '2' and TEMP1.Impairment_Stage_T = '1') AS G9,
--'(減項) 加總報表3中符合T-1期Impairment_Stage=2且T期Impairment_Stage=1條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中，E欄=AC、T欄=2、U欄=1之AR欄加總'
(Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) * -1 from TEMP1 where TEMP1.Impairment_Stage_T1 = '3' and TEMP1.Impairment_Stage_T = '1') AS H9,
--'(減項) 加總報表3中符合T-1期Impairment_Stage=3且T期Impairment_Stage=1條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中，E欄=AC、T欄=3、U欄=1之AR欄加總'
-- 於當期除列之金融資產(10)
(Select(SUM(TEMP1.Excluding_Monetary_Assets_ECL_Ex_3) + SUM(TEMP1.Section_Excluding_Monetary_Assets_ECL_Ex_3)) * -1 from TEMP1 where TEMP1.Impairment_Stage_T1 = '1') AS E10,
--'(減項) 加總報表3中符合T-1期Impairment_Stage=1條件者之除列之金融資產ECL(期初報表日匯率台幣)及部分除列之金融資產ECL(期初報表日匯率台幣) 報表3中，E欄=AC及T欄=1之AN欄及AP欄加總'
(Select(SUM(TEMP1.Excluding_Monetary_Assets_ECL_Ex_3) + SUM(TEMP1.Section_Excluding_Monetary_Assets_ECL_Ex_3)) * -1 from TEMP1 where TEMP1.Impairment_Stage_T1 = '2') AS G10,
--'(減項) 加總報表3中符合T-1期Impairment_Stage=2條件者之除列之金融資產ECL(期初報表日匯率台幣)及部分除列之金融資產ECL(期初報表日匯率台幣) 報表3中，E欄=AC及T欄=2之AN欄及AP欄加總'
(Select(SUM(TEMP1.Excluding_Monetary_Assets_ECL_Ex_3) + SUM(TEMP1.Section_Excluding_Monetary_Assets_ECL_Ex_3)) * -1 from TEMP1 where TEMP1.Impairment_Stage_T1 = '3') AS H10,
--'(減項) 加總報表3中符合T-1期Impairment_Stage=3條件者之除列之金融資產ECL(期初報表日匯率台幣)及部分除列之金融資產ECL(期初報表日匯率台幣) 報表3中，E欄=AC及T欄=3之AN欄及AP欄加總'
--創始或購入之新金融資產(11)
(Select(SUM(TEMP2.Purchase_Monetary_Assets_ECL_Ex_4) + SUM(TEMP2.Section_Purchase_Monetary_Assets_ECL_Ex_4)) from TEMP2 where TEMP2.Impairment_Stage_T = '1') AS E11,
--'(加項) 加總報表4中符合T期Impairment_Stage=1條件者之新購入之金融資產ECL(報表日匯率台幣)及部分新增之金融資產ECL(報表日匯率台幣) 報表4中，E欄=AC、U欄=1之AN欄及AP欄加總'
(Select(SUM(TEMP2.Purchase_Monetary_Assets_ECL_Ex_4) + SUM(TEMP2.Section_Purchase_Monetary_Assets_ECL_Ex_4)) from TEMP2 where TEMP2.Impairment_Stage_T = '2') AS G11,
--'(加項) 加總報表4中符合T期Impairment_Stage=2條件者之新購入之金融資產ECL(報表日匯率台幣)及部分新增之金融資產ECL(報表日匯率台幣) 報表4中，E欄=AC、U欄=2之AN欄及AP欄加總'
(Select(SUM(TEMP2.Purchase_Monetary_Assets_ECL_Ex_4) + SUM(TEMP2.Section_Purchase_Monetary_Assets_ECL_Ex_4)) from TEMP2 where TEMP2.Impairment_Stage_T = '3') AS H11,
--'(加項) 加總報表4中符合T期Impairment_Stage=3條件者之新購入之金融資產ECL(報表日匯率台幣)及部分新增之金融資產ECL(報表日匯率台幣) 報表4中，E欄=AC、U欄=3之AN欄及AP欄加總'
--本期沖銷(12)
--本期收回(13)
--模型 / 風險參數之改變(14)
(Select SUM(TEMP2.Initial_Remaining_Part_ECL_Ex_Rate_4) from TEMP2 where TEMP2.Impairment_Stage_T = '1') -
(Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) from TEMP1 where TEMP1.Impairment_Stage_T = '1') AS E14,
--'(加項) 加總報表4中符合T期Impairment_Stage=1條件者之期初剩餘部位之期末ECL(期初報表日匯率台幣) 報表4中，E欄=AC、U欄=1之AS欄加總
--(減項) 加總報表3中符合T期Impairment_Stage = 1條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中，E欄 = AC、U欄 = 1之AR欄加總'
(Select SUM(TEMP2.Initial_Remaining_Part_ECL_Ex_Rate_4) from TEMP2 where TEMP2.Impairment_Stage_T = '2') -
(Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) from TEMP1 where TEMP1.Impairment_Stage_T = '2') AS G14,
--'(加項) 加總報表4中符合T期Impairment_Stage=2條件者之期初剩餘部位之期末ECL(期初報表日匯率台幣) 報表4中，E欄=AC、U欄=2之AS欄加總
--(減項) 加總報表3中符合T期Impairment_Stage = 2條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中，E欄 = AC、U欄 = 2之AR欄加總'
(Select SUM(TEMP2.Initial_Remaining_Part_ECL_Ex_Rate_4) from TEMP2 where TEMP2.Impairment_Stage_T = '3') -
(Select SUM(TEMP1.Final_Remaining_Part_ECL_Ex_3) from TEMP1 where TEMP1.Impairment_Stage_T = '3') AS H14,
--'(加項) 加總報表4中符合T期Impairment_Stage=3條件者之期初剩餘部位之期末ECL(期初報表日匯率台幣) 報表4中，E欄=AC、U欄=3之AS欄加總
--(減項) 加總報表3中符合T期Impairment_Stage = 3條件者之期末剩餘部位之ECL(期初報表日匯率台幣) 報表3中，E欄 = AC、U欄 = 3之AR欄加總'
--匯兌及其他變動(15)
(Select SUM(TEMP2.Exchange_Changes_4) from TEMP2 where TEMP2.Impairment_Stage_T = '1') AS E15,
--'(加項) 加總報表4中符合T期Impairment_Stage=1條件者之匯兌變動影響 報表4中，E欄=AC、U欄=1之AT欄加總'
(Select SUM(TEMP2.Exchange_Changes_4) from TEMP2 where TEMP2.Impairment_Stage_T = '2') AS G15,
--'(加項) 加總報表4中符合T期Impairment_Stage=2條件者之匯兌變動影響 報表4中，E欄=AC、U欄=2之AT欄加總'
(Select SUM(TEMP2.Exchange_Changes_4) from TEMP2 where TEMP2.Impairment_Stage_T = '3') AS H15
--'(加項) 加總報表4中符合T期Impairment_Stage=3條件者之匯兌變動影響 報表4中，E欄=AC、U欄=3之AT欄加總'
--期末餘額(16)
";
using (var cmd = new SqlCommand(sql, conn))
{
string _IAS39_CATEGORY = "AC";
string Initial = parms.Where(x => x.key == "Initial").FirstOrDefault()?.value ?? string.Empty;
string Final = parms.Where(x => x.key == "Final").FirstOrDefault()?.value ?? string.Empty;
DateTime Initial_Date = DateTime.Parse(Initial);
DateTime Final_Date = DateTime.Parse(Final);
var _Initial = new SqlParameter();
_Initial.SqlDbType = SqlDbType.VarChar;
_Initial.ParameterName = "@Initial";
_Initial.Value = Initial;
_Initial.Direction = ParameterDirection.Input;
var _Final = new SqlParameter();
_Final.SqlDbType = SqlDbType.VarChar;
_Final.ParameterName = "@Final";
_Final.Value = Final;
_Final.Direction = ParameterDirection.Input;
var IAS39_CATEGORY = new SqlParameter();
IAS39_CATEGORY.SqlDbType = SqlDbType.VarChar;
IAS39_CATEGORY.ParameterName = "@IAS39_CATEGORY";
IAS39_CATEGORY.Value = _IAS39_CATEGORY;
IAS39_CATEGORY.Direction = ParameterDirection.Input;
cmd.Parameters.Add(_Initial); //期初
cmd.Parameters.Add(_Final); //期末
cmd.Parameters.Add(IAS39_CATEGORY);
cmd.CommandTimeout = 0; //190624 USER產報表跳出逾時訊息
//cmd.Parameters.Add(new SqlParameter("Initial", Initial)); //期初
//cmd.Parameters.Add(new SqlParameter("Final", Final)); //期末
//cmd.Parameters.Add(new SqlParameter("IAS39_CATEGORY", _IAS39_CATEGORY));
conn.Open();
var adapter = new SqlDataAdapter(cmd);
adapter.Fill(resultsTable);
var DateRange = new reportParm()
{
key = "DateRange",
value = $@"{Initial_Date.ToString("yyyy/MM/dd")}~{Final_Date.ToString("yyyy/MM/dd")}"
};
extensionParms.Add(DateRange);
}
}
return(resultsTable);
}
public override DataSet GetData(List <reportParm> parms)
{
var resultsTable = new DataSet();
using (var conn = new SqlConnection(defaultConnection))
{
string sql = string.Empty;
sql += $@"
WITH TempD62 AS
(
select TOP 1 Version from Bond_Basic_Assessment where Report_Date = @ReportDate ORDER BY Version DESC
),
TempD62All AS
(
select
A41.Assessment_Sub_Kind AS Assessment_Sub_Kind, --產業別 (A41#48)
D62.Bond_Type AS Bond_Type, --債券種類 (D62#5)
D62.Bond_Number AS Bond_Number, --債券編號 (D62#8)
D62.Lots AS Lots, --Lots (D62#9)
D62.Portfolio_Name AS Portfolio_Name, -- Portfolio_英文 (D62#11)
D62.Portfolio AS Portfolio, --Portfolio_中文 (D62#10)
A41.Segment_Name, --債券(資產)名稱 (A41#4)
convert(varchar, A41.Origination_Date, 111) AS Origination_Date, --債券購入(認列)日期 (62#12)
D62.Cost_Value, --攤銷後之成本數(原幣) (D62#20)
D62.Market_Value_Ori, --市價(原幣) (D62#21)
D62.Value_Change_Ratio, --未實現累計損失率_本月 (D62#22)
D62.Chg_In_Spread, --信用利差_本月 (D62#30)
CASE WHEN D62.Original_External_Rating is null
THEN 'N'
ELSE 'Y'
END AS Original_External_Rating_Flag, --是否有購買日信評 (判斷 D62#13)
D62.Original_External_Rating, --購買日信評(主標尺轉換值) (D62#13)
D62.Current_External_Rating, --報導日信評(主標尺轉換值) (D62#14)
D62.Rating_Ori_Good_IND, --原始購買信評是否為低信用風險等級 (D62#15)
D62.Rating_Curr_Good_Ind, --報導日信評是否為低信用風險等級 (D62#16)
D62.Curr_Ori_Rating_Diff, --評等下降數 (D62#17)
D62.Accumulation_Loss_This_Month, --未實現累計損失率逾越30%(含)連續月數 (D62#24)
D62.Chg_In_Spread_This_Month,--信用利差拓寬300基點(含)以上連續月數 (D62#33)
D62.Map_Rule_Id_D70 AS MapRule, --對應規則參數編號 (D62#27)
D62.Version
from
(SELECT _D62.*
FROM Bond_Basic_Assessment _D62 , TempD62
WHERE _D62.Report_Date = @ReportDate
AND _D62.Version = TempD62.Version
AND _D62.Watch_IND = 'Y' --觀察名單
) AS D62
JOIN
(
SELECT A41.*
FROM Bond_Account_Info A41,TempD62
WHERE A41.Report_Date = @ReportDate
AND A41.Version = TempD62.Version
)
AS A41
ON D62.Reference_Nbr = A41.Reference_Nbr)
Select * from TempD62All
union
select
null AS Assessment_Sub_Kind,
null AS Bond_Type,
null AS Bond_Number,
null AS Lots,
null AS Portfolio_Name,
null AS Portfolio,
null AS Segment_Name,
null AS Origination_Date,
null AS Cost_Value,
null AS Market_Value_Ori,
null AS Value_Change_Ratio,
null AS Chg_In_Spread,
null AS Original_External_Rating_Flag,
null AS Original_External_Rating,
null AS Current_External_Rating,
null AS Rating_Ori_Good_IND,
null AS Rating_Curr_Good_Ind,
null AS Curr_Ori_Rating_Diff,
null AS Accumulation_Loss_This_Month,
null AS Chg_In_Spread_This_Month,
null AS MapRule,
TempD62.Version AS Version
from TempD62
";
using (var cmd = new SqlCommand(sql, conn))
{
string reportDate = parms.Where(x => x.key == "ReportDate").FirstOrDefault()?.value ?? string.Empty;
cmd.Parameters.Add(new SqlParameter("ReportDate", reportDate));
Extension.NlogSet(cmd.CommandText);
conn.Open();
var adapter = new SqlDataAdapter(cmd);
adapter.Fill(resultsTable);
var Version = new reportParm()
{
key = "Version",
value = ""
};
var dt = resultsTable.Tables[0].AsEnumerable();
if (dt.Any())
{
var first = dt.First();
Version.value = first.Field <int>("Version").ToString();
}
extensionParms.Add(Version);
}
}
return(resultsTable);
}
public override DataSet GetData(List <reportParm> parms)
{
var resultsTable = new DataSet();
string Initial = parms.Where(x => x.key == "Initial").FirstOrDefault()?.value ?? string.Empty;
string Final = parms.Where(x => x.key == "Final").FirstOrDefault()?.value ?? string.Empty;
string Group_Product_Code = parms.Where(x => x.key == "Group_Product_Code").FirstOrDefault()?.value ?? string.Empty;
using (var conn = new SqlConnection(defaultConnection))
{
string sql = string.Empty;
sql += $@"
WITH T AS --期末
(
SELECT D52.*
FROM
(select * from IFRS9_Bond_Report
WHERE Report_Date = @Final) D52
JOIN (select PRJID,FLOWID from Flow_Info
where Group_Product_Code = @Group_Product_Code) FI
on D52.PRJID = FI.PRJID
and D52.FLOWID = FI.FLOWID
),
T1 AS --期初
(
SELECT
D52.*
FROM
(select * from IFRS9_Bond_Report
WHERE Report_Date = @Initial) D52
JOIN (select PRJID,FLOWID from Flow_Info
where Group_Product_Code = @Group_Product_Code) FI
on D52.PRJID = FI.PRJID
and D52.FLOWID = FI.FLOWID
),
TEMP AS
(
SELECT
T1.Reference_Nbr, --Reference_Nbr
T1.Security_Name, --Security Name(A)
T1.Bond_Number, --債券編號(B)
T1.Lots, --Lots(C)
T1.Segment_Name, --債券(資產)名稱(D)
T1.IAS39_CATEGORY, --公報分類(E)
T1.Bond_Aera, --國內\國外(F)
T1.Asset_Type, --金融商品分類(G)
T1.Currency_Code, --幣別(H)
T1.ASSET_SEG, --資產區隔(I)
T1.IH_OS, --自操\委外(J)
T1.Portfolio, --Portfolio(K)
T1.PRODUCT, --SMF(L)
T1.Ori_Amount AS Ori_Amount_T1, --期初帳列面額(原幣)(M)
T.Ori_Amount AS Ori_Amount_T, --期末帳列面額(原幣)(N)
T.Ori_Amount - T1.Ori_Amount AS Ori_Amount, --面額增(減)變動(原幣)(O)
T1.Principal AS Principal_T1, --期初攤銷後成本(原幣)(P)
T.Principal AS Principal_T, --期末攤銷後成本(原幣)(Q)
T1.Interest_Receivable AS Interest_Receivable_T1, --期初應收利息(原幣)(R)
T.Interest_Receivable AS Interest_Receivable_T, --期末應收利息(原幣)(S)
T1.Impairment_Stage AS Impairment_Stage_T1, --期初減損階段(T)
T.Impairment_Stage AS Impairment_Stage_T, --期末減損階段(U)
T1.PD AS PD_T1, --期初第一年違約率(V)
T.PD AS PD_T, --期末第一年違約率(W)
CASE WHEN ISNULL((T1.Principal * T1.Current_LGD) , 0) = 0
THEN NULL
ELSE
T1.Principal_EL_Ori/(T1.Principal * T1.Current_LGD)
END AS Default_Rate_T1, --期初違約率(X)
CASE WHEN ISNULL((T.Principal * T.Current_LGD) , 0) = 0
THEN NULL
ELSE
T.Principal_EL_Ori/(T.Principal * T.Current_LGD)
END AS Default_Rate_T, --期末違約率(Y)
T1.Current_LGD AS Current_LGD_T1, --期初違約損失率(Z)
T.Current_LGD AS Current_LGD_T, --期末違約損失率(AA)
T1.Ori_Ex_rate, --成本匯率(AB)
T1.Ex_rate AS Ex_rate_T1, --期初報表日匯率(AC)
T.Ex_rate AS Ex_rate_T, --報表日匯率(AD)
T1.Principal_EL_Ori AS Principal_EL_Ori_T1, --期初累計減損_本金(原幣)(AE)
T1.Principal_EL_Ori_Ex AS Principal_EL_Ori_Ex_T1, --期初累計減損_本金(成本匯率台幣)(AF)
T1.Principal_EL_Ex AS Principal_EL_Ex_T1, --期初累計減損_本金(期初報表日匯率台幣)(AG)
T1.Principal_Diff_Tw AS Principal_Diff_Tw_T1, --期初累計減損_本金匯兌損益(台幣)(AH)
T.Principal_EL_Ori AS Principal_EL_Ori_T, --本期累計減損_本金(原幣)(AI)
T.Principal_EL_Ori_Ex AS Principal_EL_Ori_Ex_T, --本期累計減損_本金(成本匯率台幣)(AJ)
T.Principal_EL_Ex AS Principal_EL_Ex_T, --本期累計減損_本金(報表日匯率台幣)(AK)
T.Principal_Diff_Tw AS Principal_Diff_Tw_T, --本期累計減損_本金匯兌損益(台幣)(AL)
CASE WHEN ISNULL(T.Ori_Amount,0) = 0
THEN T1.Principal_EL_Ori
ELSE 0
END AS Excluding_Monetary_Assets_ECL_Ori, --除列之金融資產ECL(原幣)(AM)
CASE WHEN ISNULL(T.Ori_Amount,0) = 0
THEN T1.Principal_EL_Ex
ELSE 0
END AS Excluding_Monetary_Assets_ECL_Ex, --除列之金融資產ECL(期初報表日匯率台幣)(AN)
CASE WHEN (T.Ori_Amount > 0 AND (T.Ori_Amount - T1.Ori_Amount) < 0)
THEN T1.Principal_EL_Ori * ABS( (T.Ori_Amount - T1.Ori_Amount) / T1.Ori_Amount )
ELSE 0
END AS Section_Excluding_Monetary_Assets_ECL_Ori, --部分除列之金融資產ECL(原幣)(AO)
CASE WHEN (T.Ori_Amount > 0 AND (T.Ori_Amount - T1.Ori_Amount) < 0)
THEN T1.Principal_EL_Ex * ABS( (T.Ori_Amount - T1.Ori_Amount) / T1.Ori_Amount )
ELSE 0
END AS Section_Excluding_Monetary_Assets_ECL_Ex --部分除列之金融資產ECL(報表日匯率台幣)(AP)
FROM
T1 left join T
ON T1.Bond_Number =
CASE WHEN T.ISIN_Changed_Ind = 'Y'
THEN T.Bond_Number_Old
ELSE T.Bond_Number
END
AND T1.Lots =
CASE WHEN T.ISIN_Changed_Ind = 'Y'
THEN T.Lots_Old
ELSE T.Lots
END
AND T1.Portfolio_Name =
CASE WHEN T.ISIN_Changed_Ind = 'Y'
THEN T.Portfolio_Name_Old
ELSE T.Portfolio_Name
END
)
select
TEMP.*,
TEMP.Principal_EL_Ori_T1 - TEMP.Excluding_Monetary_Assets_ECL_Ori - TEMP.Section_Excluding_Monetary_Assets_ECL_Ori
AS Final_Remaining_Part_ECL_Ori, --期初剩餘部位之期末ECL(原幣)(AQ) = AE欄-AM欄-AO欄
TEMP.Principal_EL_Ex_T1 - TEMP.Excluding_Monetary_Assets_ECL_Ex - TEMP.Section_Excluding_Monetary_Assets_ECL_Ex
AS Final_Remaining_Part_ECL_Ex --期初剩餘部位之期末ECL(報表日匯率台幣)(AR) = AG欄-AN欄-AP欄
from
TEMP ;
";
DateTime Initial_Date = DateTime.Parse(Initial);
DateTime Final_Date = DateTime.Parse(Final);
//using (IFRS9DBEntities db = new IFRS9DBEntities())
//{
// var _data = db.Database.DynamicSqlQuery(sql
// , null,
// new object[] {
// new SqlParameter("Initial", Initial),
// new SqlParameter("Final", Final),
// new SqlParameter("Group_Product_Code", Group_Product_Code)
// }
// );
// db.Database.Connection.Close();
// resultsTable.Tables.Add(_data.ToDataTable());
//}
using (var cmd = new SqlCommand(sql, conn))
{
var _Initial = new SqlParameter();
_Initial.SqlDbType = SqlDbType.VarChar;
_Initial.ParameterName = "@Initial";
_Initial.Value = Initial;
var _Final = new SqlParameter();
_Final.SqlDbType = SqlDbType.VarChar;
_Final.ParameterName = "@Final";
_Final.Value = Final;
var _Group_Product_Code = new SqlParameter();
_Group_Product_Code.SqlDbType = SqlDbType.VarChar;
_Group_Product_Code.ParameterName = "@Group_Product_Code";
_Group_Product_Code.Value = Group_Product_Code;
cmd.Parameters.Add(_Initial); //期初
cmd.Parameters.Add(_Final); //期末
cmd.Parameters.Add(_Group_Product_Code);
conn.Open();
var adapter = new SqlDataAdapter(cmd);
adapter.Fill(resultsTable);
}
var DateRange = new reportParm()
{
key = "DateRange",
value = $@"{Initial_Date.ToString("yyyy/MM/dd")}~{Final_Date.ToString("yyyy/MM/dd")}"
};
extensionParms.Add(DateRange);
}
return(resultsTable);
}
public override DataSet GetData(List <reportParm> parms)
{
var resultsTable = new DataSet();
string Initial = parms.Where(x => x.key == "Initial").FirstOrDefault()?.value ?? string.Empty;
string Final = parms.Where(x => x.key == "Final").FirstOrDefault()?.value ?? string.Empty;
string Group_Product_Code = parms.Where(x => x.key == "Group_Product_Code").FirstOrDefault()?.value ?? string.Empty;
using (var conn = new SqlConnection(defaultConnection))
{
string sql = string.Empty;
sql += $@"
WITH T AS --期末
(
SELECT D52.*
FROM
(select * from IFRS9_Bond_Report
WHERE Report_Date = @Final ) D52
JOIN (select PRJID,FLOWID from Flow_Info
where Group_Product_Code = @Group_Product_Code ) FI
on D52.PRJID = FI.PRJID
and D52.FLOWID = FI.FLOWID
),
T1 AS --期初
(
SELECT
D52.*
FROM
(select * from IFRS9_Bond_Report
WHERE Report_Date = @Initial ) D52
JOIN (select PRJID,FLOWID from Flow_Info
where Group_Product_Code = @Group_Product_Code ) FI
on D52.PRJID = FI.PRJID
and D52.FLOWID = FI.FLOWID
),
TEMP AS
(
SELECT
T.Reference_Nbr, -- Reference_Nbr
T.Security_Name, --Security Name(A)
T.Bond_Number, --債券編號(B)
T.Lots, --Lots(C)
T.Segment_Name, --債券(資產)名稱(D)
T.IAS39_CATEGORY, --公報分類(E)
T.Bond_Aera, --國內\國外(F)
T.Asset_Type, --金融商品分類(G)
T.Currency_Code, --幣別(H)
T.ASSET_SEG, --資產區隔(I)
T.IH_OS, --自操\委外(J)
T.Portfolio, --Portfolio(K)
T.PRODUCT, --SMF(L)
T1.Ori_Amount AS Ori_Amount_T1, --期初帳列面額(原幣)(M)
T.Ori_Amount AS Ori_Amount_T, --期末帳列面額(原幣)(N)
T.Ori_Amount - T1.Ori_Amount AS Ori_Amount, --面額增(減)變動(原幣)(O)
T1.Principal AS Principal_T1, --期初攤銷後成本(原幣)(P)
T.Principal AS Principal_T, --期末攤銷後成本(原幣)(Q)
T1.Interest_Receivable AS Interest_Receivable_T1, --期初應收利息(原幣)(R)
T.Interest_Receivable AS Interest_Receivable_T, --期末應收利息(原幣)(S)
T1.Impairment_Stage AS Impairment_Stage_T1, --期初減損階段(T)
T.Impairment_Stage AS Impairment_Stage_T, --期末減損階段(U)
T1.PD AS PD_T1, --期初第一年違約率(V)
T.PD AS PD_T, --期末第一年違約率(W)
CASE WHEN ISNULL((T1.Principal * T1.Current_LGD), 0) = 0
THEN NULL
ELSE
T1.Principal_EL_Ori/(T1.Principal * T1.Current_LGD)
END AS Default_Rate_T1, --期初違約率(X)
CASE WHEN ISNULL((T.Principal * T.Current_LGD) , 0) = 0
THEN NULL
ELSE
T.Principal_EL_Ori/(T.Principal * T.Current_LGD)
END AS Default_Rate_T, --期末違約率(Y)
T1.Current_LGD AS Current_LGD_T1, --期初違約損失率(Z)
T.Current_LGD AS Current_LGD_T, --期末違約損失率(AA)
T.Ori_Ex_rate, --成本匯率(AB)
T1.Ex_rate AS Ex_rate_T1, --期初報表日匯率(AC)
T.Ex_rate AS Ex_rate_T, --報表日匯率(AD)
T1.Principal_EL_Ori AS Principal_EL_Ori_T1, --期初累計減損_本金(原幣)(AE)
T1.Principal_EL_Ori_Ex AS Principal_EL_Ori_Ex_T1, --期初累計減損_本金(成本匯率台幣)(AF)
T1.Principal_EL_Ex AS Principal_EL_Ex_T1, --期初累計減損_本金(期初報表日匯率台幣)(AG)
T1.Principal_Diff_Tw AS Principal_Diff_Tw_T1, --期初累計減損_本金匯兌損益(台幣)(AH)
T.Principal_EL_Ori AS Principal_EL_Ori_T, --本期累計減損_本金(原幣)(AI)
T.Principal_EL_Ori_Ex AS Principal_EL_Ori_Ex_T, --本期累計減損_本金(成本匯率台幣)(AJ)
T.Principal_EL_Ex AS Principal_EL_Ex_T, --本期累計減損_本金(報表日匯率台幣)(AK)
T.Principal_Diff_Tw AS Principal_Diff_Tw_T, --本期累計減損_本金匯兌損益(台幣)(AL)
CASE WHEN ISNULL(T1.Ori_Amount,0) = 0
THEN T.Principal_EL_Ori
ELSE 0
END AS Purchase_Monetary_Assets_ECL_Ori, --新購入之金融資產ECL(原幣)(AM)
CASE WHEN ISNULL(T1.Ori_Amount,0) = 0
THEN T.Principal_EL_Ex
ELSE 0
END AS Purchase_Monetary_Assets_ECL_Ex, --新購入之金融資產ECL(報表日匯率台幣)(AN)
CASE WHEN (T1.Ori_Amount > 0 AND (T.Ori_Amount - T1.Ori_Amount) > 0)
THEN T.Principal_EL_Ori * (T.Ori_Amount - T1.Ori_Amount) / T1.Ori_Amount
ELSE 0
END AS Section_Purchase_Monetary_Assets_ECL_Ori, --部分新增之金融資產ECL(原幣)(AO)
CASE WHEN (T1.Ori_Amount > 0 AND (T.Ori_Amount - T1.Ori_Amount) > 0)
THEN T.Principal_EL_Ex * (T.Ori_Amount - T1.Ori_Amount) / T1.Ori_Amount
ELSE 0
END AS Section_Purchase_Monetary_Assets_ECL_Ex, --部分新增之金融資產ECL(報表日匯率台幣)(AP)
T.ISIN_Changed_Ind AS ISIN_Changed_Ind, --是否為換券
T.Bond_Number_Old AS Bond_Number_Old, --債券編號_換券前
T.Lots_Old AS Lots_Old, --Lots_舊
T.Portfolio_Name_Old AS Portfolio_Name_Old, --Portfolio英文_舊
convert(varchar, T.Origination_Date_Old , 111) AS Origination_Date_Old --舊券原始購入日
FROM
T Left join T1
ON T1.Bond_Number =
CASE WHEN T.ISIN_Changed_Ind = 'Y'
THEN T.Bond_Number_Old
ELSE T.Bond_Number
END
AND T1.Lots =
CASE WHEN T.ISIN_Changed_Ind = 'Y'
THEN T.Lots_Old
ELSE T.Lots
END
AND T1.Portfolio_Name =
CASE WHEN T.ISIN_Changed_Ind = 'Y'
THEN T.Portfolio_Name_Old
ELSE T.Portfolio_Name
END
)
select
TEMP.*,
TEMP.Principal_EL_Ori_T - TEMP.Purchase_Monetary_Assets_ECL_Ori - TEMP.Section_Purchase_Monetary_Assets_ECL_Ori
AS Initial_Remaining_Part_ECL_Ori, --期初剩餘部位之期末ECL(原幣)(AQ) = AI欄-AM欄-AO欄
TEMP.Principal_EL_Ex_T - TEMP.Purchase_Monetary_Assets_ECL_Ex - TEMP.Section_Purchase_Monetary_Assets_ECL_Ex
AS Initial_Remaining_Part_ECL_Ex, --期初剩餘部位之期末ECL(報表日匯率台幣)(AR) = AK欄-AN欄-AP欄
(TEMP.Principal_EL_Ori_T - TEMP.Purchase_Monetary_Assets_ECL_Ori - TEMP.Section_Purchase_Monetary_Assets_ECL_Ori) * Ex_rate_T1
AS Initial_Remaining_Part_ECL_Ex_Rate, --期初剩餘部位之期末ECL(期初報表日匯率台幣) (AS) = AQ欄*AC欄
(TEMP.Principal_EL_Ex_T - TEMP.Purchase_Monetary_Assets_ECL_Ex - TEMP.Section_Purchase_Monetary_Assets_ECL_Ex) -
(TEMP.Principal_EL_Ori_T - TEMP.Purchase_Monetary_Assets_ECL_Ori - TEMP.Section_Purchase_Monetary_Assets_ECL_Ori) * Ex_rate_T1
AS Exchange_Changes --匯兌變動影響(AT) = AR欄-AS欄
from TEMP ;
";
using (var cmd = new SqlCommand(sql, conn))
{
DateTime Initial_Date = DateTime.Parse(Initial);
DateTime Final_Date = DateTime.Parse(Final);
var _Initial = new SqlParameter();
_Initial.SqlDbType = SqlDbType.VarChar;
_Initial.ParameterName = "@Initial";
_Initial.Value = Initial;
_Initial.Direction = ParameterDirection.Input;
var _Final = new SqlParameter();
_Final.SqlDbType = SqlDbType.VarChar;
_Final.ParameterName = "@Final";
_Final.Value = Final;
_Final.Direction = ParameterDirection.Input;
var _Group_Product_Code = new SqlParameter();
_Group_Product_Code.SqlDbType = SqlDbType.VarChar;
_Group_Product_Code.ParameterName = "@Group_Product_Code";
_Group_Product_Code.Value = Group_Product_Code;
_Group_Product_Code.Direction = ParameterDirection.Input;
cmd.Parameters.Add(_Initial); //期初
cmd.Parameters.Add(_Final); //期末
cmd.Parameters.Add(_Group_Product_Code);
conn.Open();
var adapter = new SqlDataAdapter(cmd);
adapter.Fill(resultsTable);
var DateRange = new reportParm()
{
key = "DateRange",
value = $@"{Initial_Date.ToString("yyyy/MM/dd")}~{Final_Date.ToString("yyyy/MM/dd")}"
};
extensionParms.Add(DateRange);
}
}
return(resultsTable);
}
