Melville Douglas Valuations Import

README.md

Non financial data

1. Create Tau_contract_fee_updated TABLE

   -- DROP TABLE [HiSight_LZ].[LZ].[Tau_contract_fee_updated] -- GO SELECT * INTO [HiSight_LZ].[LZ].[Tau_contract_fee_updated] FROM [HiSight_LZ].[LZ].[Tau_contract_fee] GO

2. Update stored procs and views:

   use file stored-procs/combined_fix.sql to update:

   • 3.2_report.usp_valuation_statement_static_details_popuplate.sql
   • 3.4_report.usp_valuation_statements_life_assured_popuplate.sql
   • report.usp_frd_tranche_update.sql
   • report.usp_valuation_statement_details_surrender_calculator.sql
   • report.vw_valuation_statement_details_CalculatedData.sql
   • report.vw_valuation_statements_calculated_details_HLGIP.sql
   • report.vw_valuation_statements_calculated_details_LE_HSLE.sql
   • vw_dbo.vw_contract_fee_updated.sql
   • vw_dbo.vw_LimitedEditionFees_GIP_updated.sql
   • vw_dbo.vw_LimitedEditionFees_updated.sql
   • vw_dbo.vw_LimitedEditionNetInvestmentAmount_updated.sql
   • make_total_fees.sql

3. Populate total_fees:

   cd hispy python3 scripts/txn_summary_parallel.py

4. Check differences between total_fees and Tau_contract_fee

   SELECT tcf.policy_no, tcf.fee_structure_name, tcf.fee_name, convert(decimal(15,4), tcf.[percentage]) as orig_pct, convert(decimal(15,4), tf.[percentage]) as new_pct, ABS(convert(decimal(15,4), tcf.[percentage]) - convert(decimal(15,4), tf.[percentage])) as difference, case when (ABS(convert(decimal(15,4), tcf.[percentage]) - convert(decimal(15,4), tf.[percentage])) > 0.001) THEN 'DIFF' ELSE 'OK' end as [status] FROM [HiSight_LZ].[LZ].[Tau_contract_fee] tcf LEFT JOIN [HiSight_LZ].[r].[total_fees] tf ON tcf.policy_no = tf.policy_no AND tcf.fee_name = tf.fee_name WHERE tcf.fee_structure_name like '%Limited%' -- and tcf.policy_no in (select policy_no FROM [HISight].[vs].[contract]) -- and tcf.policy_no = '201812070022' order by difference, orig_pct, policy_no

5. Update Tau_contract_fee_updated from total_fees

   UPDATE tcf SET tcf.[percentage] = tf.[percentage] FROM [HiSight_LZ].[LZ].[Tau_contract_fee_updated] tcf LEFT JOIN [HiSight_LZ].[r].[total_fees] tf ON tcf.policy_no = tf.policy_no AND tcf.fee_name = tf.fee_name WHERE tf.[percentage] IS NOT NULL

-- you can check differences post update like this: SELECT tcf.policy_no, tcf.fee_structure_name, tcf.fee_name, convert(decimal(15,4), tcf.[percentage]) as orig_pct, convert(decimal(15,4), tf.[percentage]) as new_pct, ABS(convert(decimal(15,4), tcf.[percentage]) - convert(decimal(15,4), tf.[percentage])) as difference, case when (ABS(convert(decimal(15,4), tcf.[percentage]) - convert(decimal(15,4), tf.[percentage])) > 0.001) THEN 'DIFF' ELSE 'OK' end as [status] FROM [HiSight_LZ].[LZ].[Tau_contract_fee_updated] tcf LEFT JOIN [HiSight_LZ].[r].[total_fees] tf ON tcf.policy_no = tf.policy_no AND tcf.fee_name = tf.fee_name WHERE tcf.fee_structure_name like '%Limited%' order by difference, orig_pct, policy_no

6. Copy spreadsheets Tobeimported folder

   Copy to ...HollardInvestments/HI - Administration/Limited Edition Valuation Statements/ToBeImported/

7. Run the import process (this is jobs 1-4 in the old SSIS)

   cd hispy IMPORT="/home/control/nashi/HI - Administration/Limited Edition Valuation Statements/ToBeImported/" python3 scripts/ltd_edition_valuation.py --loadinitial --directory "$IMPORT"

8. Check loads from SSIS job 1-4

   SELECT * FROM [HiSight_LZ].[LZ].[valuation_statements] SELECT * FROM Hisight_LZ.LZ.MD_tranche_valuations_LE order by created_at desc SELECT * FROM Hisight_LZ.LZ.MD_tranche_valuations_SE order by created_at desc SELECT * FROM Hisight_LZ.LZ.MD_tranche_valuations_RSA order by created_at desc

9. Run stored procs post jobs 1-4...

   python scripts/ltd_edition_valuation.py --generatestatic

   -- This "ltd_edition_valuation.py --generatestatic" runs -- the following SPs

   -- USE HISight -- EXECUTE [report].[usp_frd_tranche_valuations_insert] -- GO

   ---

   -- EXECUTE [report].[usp_frd_tranche_update] --- NB: MUST BE ADDED TO ltd_edition_valuation.py -- GO

   ---

   -- -- USE HISight -- EXECUTE [report].[usp_valuation_statements_load_runner] -- GO

   -- USE HISight -- EXECUTE [report].[usp_valuation_statements_popuplate_static_Master] -- GO

   -- -- Check for errors -- SELECT * FROM [HISight].[report].[error_log] order by id desc -- GO

10. Get the run_id

    SELECT max(valuation_statement_run_id) FROM [report].[valuation_statements] -- 27

11. Run non financial extract

    python scripts/ltd_edition_valuation.py --nonfinacialextract --directory /control/var/output

    creates spreadsheets in /control/var/output/

    Copy these to ...HollardInvestments/HI - Administration/Limited Edition Valuation Statements/Archive/yyyy_mm/ and send these to Leyya et al

Financial Data

Using new spreadsheets (completed), repeat steps 6,7,8,9 above

12. Run the financial valuation

    python scripts/ltd_edition_valuation.py --runvaluation

    -- This "ltd_edition_valuation.py --runvaluation" runs -- the following SPs

    -- EXECUTE [report].[usp_frd_tranche_update] -- GO

    -- EXECUTE [report].[usp_valuation_statement_calculated_details_LE_HSLE_popuplate] 27 -- 27 is the run_id -- GO

    -- EXECUTE [report].[usp_valuation_statement_calculated_detail_HLGIP_popuplate] 27 -- GO

13. Extract the financial spreadsheets

    python scripts/ltd_edition_valuation.py --finacialextract --directory outputs

    Copy these to ...HollardInvestments/HI - Administration/Limited Edition Valuation Statements/Archive/yyyy_mm/

14. Check for missing initial investment amounts

    use HISight go SELECT sd.[statement_date] ,vs.reference_id ,vs.valuation_statement_run_id ,sd.[investor_name] ,vs.[total_initial_investment_value] ,sd.[total_initial_investment_value] ,sd.[policy_number] ,sd.[revised_gmv] ,vs.is_valid ,vs.product_id FROM [HISight].[report].[valuation_statements] vs left join [HISight].[report].[valuation_statement_static_details] sd on sd.[valuation_statement_id] = vs.id where vs.valuation_statement_run_id = 27 and vs.[total_initial_investment_value] is NULL order by reference_id

15. Populate missing initial investment amounts

    use HISight go

    UPDATE vs SET vs.[total_initial_investment_value] = sd.[total_initial_investment_value] FROM [HISight].[report].[valuation_statements] vs left join [HISight].[report].[valuation_statement_static_details] sd on sd.[valuation_statement_id] = vs.id where vs.valuation_statement_run_id = 27 and vs.[total_initial_investment_value] is NULL and sd.[total_initial_investment_value] is NOT NULL

16. Authorise the valuation statements

    Once Checked, authorise the statements

    SELECT id, run_name, run_date, statements_creation_schedule_date, statements_creation_approved, approved_by FROM report.valuation_statement_runs order by id desc

    -- Get the ID, should be 27 as per above valuation_statement_run_id -- Update is with the date, and the person responsible

    UPDATE report.valuation_statement_runs SET statements_creation_approved=1, statements_creation_schedule_date=GetDate(), -- Today approved_by='Jessica Swart' WHERE id = 27

17. Generate batch job to generate PDF documents

    -- This job is run automatically at 5PM every Mon-Fri by SQL Task -- "HSSDB12385_DAILY_OPS_Valuation_Statements" so does NOT have -- to be run manually on production. -- It does have to be run on SIT/UAT

HSSDB12385_DAILY_OPS_Valuation_Statements: EXECUTE [report].[usp_valuation_statements_create_batch_job]

TO BE DONE: Need to add new fields: hollard_upfront_tax_fee hollard_upfront_tax_fee_percentage sars_upfront_tax_fee_charge sars_upfront_tax_fee_percentage