---

-- DROP FUNCTION mgr.ft_dwh_get__pension_account_operation(jsonb);

CREATE OR REPLACE FUNCTION mgr.ft_dwh_get__pension_account_operation(p_smethodparm jsonb)
RETURNS TABLE(service_type text, period_date date, pension_account_id text, amount numeric, operation_type_id text, operation_type_code text, operation_type_name text, op_comment text)
LANGUAGE plpgsql
ROWS 100
AS $function$
declare
    jCallParm jsonb = '{"RequestParams_Method":"[ws].[sp_WSRequest_npf360_get_PensionAccountOperation]"}';
begin
    jCallParm := jCallParm || p_sMethodParm;
    
    --
    return query
        with c_resp
        as
        (
        select (mgr.f_DWH_getData(jCallParm::text, TRUE)::xml) xresp         
        )
        select t.service_type,
                 mgr.f_to_date(t.period_date) period_date,
                 t.pension_account_id,
                 t.amount,
                 t.operation_type_id,
                 t.operation_type_code,
                 t.operation_type_name,
                 t.op_comment
         from c_resp r,
             xmltable('//root/pension_account_operation' passing xresp
                                         columns     service_type text path 'service_type',
                                                     period_date text path 'period_date',
                                                     pension_account_id text path 'pension_account_id',
                                                     amount numeric path 'amount',
                                                     operation_type_id text path 'operation_type_id',
                                                     operation_type_code text path 'operation_type_code',
                                                     operation_type_name text path 'operation_type_name',
                                                     op_comment text path 'op_comment'                                                 
                                                     ) t;
    
end;
$function$
;