Untitled

Seq	Table	Field	Desc	Condition	Validation Type	Exception Code	Exception Message	Remark
1	"raw_fa_nav_share
raw_fa_holdings
raw_fa_valuation
raw_fa_transaction
raw_fa_trial_balance
raw_fa_os_settle
raw_fa_div_rec
raw_fa_cash_mvt
raw_fa_shareholder_acty
raw_fa_security
raw_fa_fx_rate
raw_fa_asset_alloc"	All	To check if the non-nullable field is null (i.e. does not have value in the field)	"1. The field in ""Nullable"" column (i.e. Column J) of ""Oak Data Dictionary.xlsx"" is marked as ""N"" ; AND
2. The field is null "	Hard	5000	The field cannot be null 	The exception message is recorded in the log
2	"raw_fa_nav_share
raw_fa_holdings
raw_fa_valuation
raw_fa_transaction
raw_fa_trial_balance
raw_fa_os_settle
raw_fa_div_rec
raw_fa_cash_mvt
raw_fa_shareholder_acty
raw_fa_security
raw_fa_fx_rate
raw_fa_asset_alloc"	All	To check if the length of value exceeds the pre-defined length of field or the data type of value not equal to the data type of field	"1. The length of value in the field > The length stated in ""Data Type (Type, length)"" (i.e. Column I) of ""Oak Data Dictionary.xlsx"" ; OR
2. The data type of value in the field <> The data type state in ""Data Type (Type, length)"" (i.e. Column I) of ""Oak Data Dictionary.xlsx"""	Hard	5100	The lengh/ data type of value does not match the parameter of fields.	The exception message is recorded in the log
3	"raw_fa_nav_share
raw_fa_holdings
raw_fa_valuation
raw_fa_transaction
raw_fa_trial_balance
raw_fa_os_settle
raw_fa_div_rec
raw_fa_security
raw_fa_fx_rate"	All	To check if there are records with same primary key(s) in the batch	More than one record with the same primary key(s) in the batch		5200	Duplicate records with same primary key(s) are found in the batch	The exception message is recorded in the log
4	"raw_fa_nav_share
raw_fa_holdings
raw_fa_valuation
raw_fa_transaction
raw_fa_trial_balance
raw_fa_os_settle
raw_fa_div_rec
raw_fa_cash_mvt
raw_fa_shareholder_acty"	nav_dt	To check if the nav_dt in tables are consistent and expected 	The value of nav_dt <> current business date (comm_bsuiness_dt.business_dt) - 1 HK business date	Hard	5300	The nav date is not expected	The exception message is recorded in the log
5	raw_fa_nav_share	"fund_cd
sharcls_cd"	To check if there is any irrelevant fund passing from Citi	"1. fund_cd = sharecls_cd
The value of fund_cd in raw_fa_nav_share matches the fa_fund_code in custom_fa_fund which the status of all its share class = 'Inactive or the value of fund_cd in raw_fa_nav cannot match any fa_fund_cd in custom_fa_fund
2. fund_cd <> sharecls_cd 
The value of fund_cd and sharecls_cd in raw_fa_nav_share matches with the fa_fund_code and fa_sharecls_cd with status = 'Inactive' in custom_fa_fund or the (fund_cd, share cls_cd) in raw_fa_nav cannot match any (fa_fund_cd, fa_share cls_cd)  in custom_fa_fund"	Soft	5400	The fund cannot be found in custom_fa_fund table	The exception message is recorded in the log
7	"raw_fa_nav_share
raw_fa_holdings
raw_fa_valuation
raw_fa_transaction
raw_fa_os_settle
raw_fa_div_rec"	sec_intl_cd	To check if there is any irrelvant security in the feed	The value of sec_intl_cd  cannot be found among all the records in the raw_fa_security table in the batch	Hard	5600	The security cannot be found in fa_security table	The exception message is recorded in the log
15	raw_fa_nav_share	"actg_navps
bid_price
offer_price"	To check if there is any price missing for the share class of fund	"For record with fund code <> share class code
actg_navps = null
or
bid_price = null
or 
offer_price = null"	Soft 	6400	 No price (actg_navps/ bid_price, offer_price) is found for share class level of fund	The exception message is recorded in the log
22	raw_fa_nav_share	fund_cd	To check if there is data of any fund to be missed by Citi	"The fund_cd in raw_fa_nav_share cannot find in below tables
1. raw_fa_holdings
2. raw_fa_valuation
3. raw_fa_trial_balance "	Soft	7100	The fund found in nav_share but it cannot be found in raw_fa_holdings or raw_fa_valuation or raw_fa_trial_balance	The exception message is recorded in the log