create or replace function "dbLegal".trcasetranEvent_DefaultSysEvent()
returns trigger as $$
declare find record;
 -- caltask record;
 rec record; ok boolean;  input_step int=new.lgstepproitemid;
begin


  -- raise   '677:trcasetranEvent_DefaultSysEvent()';
 --  raise '0873:new.lgstepproitemid %, new.lgstepproitemeventid %',  new.lgstepproitemid, new.lgstepproitemeventid;

  --* Only system event
  if new.lgstepproitemeventid is not null  then
     --* key in directly.
    if new.sysevent is null then

      /*select sysevent from "dbLegal".tblgstepproitemevent where id = new.lgstepproitemeventid
			into new.sysevent; */

			execute format('select sysevent from "dbLegal".tblgstepproitemevent where id = $1.lgstepproitemeventid') using new
			into new.sysevent;

    end if;
  else
    if new.sysevent is not null then
      --if new.lgstepproitemeventid is null then
      if true then
        execute format('
	      select b.id
        from "dbLegal".trcasetran a
	        left join "dbLegal".tblgstepproitemevent b on b.lgstepproid  = a.lgstepproid  and b.sysevent  = $1.sysevent
	      where a.id = $1.pid '


        ) using new
	      into new.lgstepproitemeventid ;
        -- raise notice '701: new.title %, new.lgstepproitemeventid %', new.title, new.lgstepproitemeventid ;
      end if;
    elseif new.title is not null then

     /*
      execute format('
      select b.id
      from "dbLegal".trcasetran a
        left join "dbLegal".tblgstepproitemevent b on b.lgstepproid  = a.lgstepproid  and b.title   = $1.title  and b.sysevent is null
      where a.id = $1.pid '
      ) using new
      into new.lgstepproitemeventid;
     */
      execute $x$
        select b.id
          , b.pid
          ,b.sysevent
        from "dbLegal".trcasetran a
          left join "dbLegal".tblgstepproitemevent b on b.lgstepproid = a.lgstepproid
        where a.id = $1.pid and b.title = $1.title
      $x$ using new
      into new.lgstepproitemeventid
       , new.lgstepproitemid  -- override active step , if any
       , new.sysevent
     ;


     -- raise '1002:  new.lgstepproitemeventid %,  new.lgstepproitemid; % ', new.lgstepproitemeventid,  new.lgstepproitemid;

      --* not keep title, if found
      if new.lgstepproitemeventid is not null then
        new.title := null;
      else
	      --* Use input step if not found
	      --if new.lgstepproitemeventid is null then
	       new.lgstepproitemid = input_step;
	      -- end if;
      end if;

    end if;
  end if;

  --* Get from step if still null
  if new.lgstepproitemid is null then
    if new.lgstepproitemeventid is not null then
      execute $x$
      select pid, eventmulti from "dbLegal".tblgstepproitemevent where id = $1 .lgstepproitemeventid
      $x$ using new
      into new.lgstepproitemid, new.eventmulti ;
      --raise notice  '1286 : new.lgstepproitemid "%"', new.lgstepproitemid;
    end if;
  end if;

 -- raise notice   '1210:new.lgstepproitemeventid"%", new.sysevent"%", new.eventmulti"%",new.lgstepproitemid"%"' ,new.lgstepproitemeventid, new.sysevent, new.eventmulti, new.lgstepproitemid;

  --* Refresh profile attributs
  /*
  ok:=case
    when new.eventmulti is null then true
    when new.EventAmtFlag is null then true
    --else false
    when new.eventmulti or  new.EventAmtFlag then true
    when new.debtadj is null then true
    else false
  end ;
  */
  ok=true ;

  -- raise '705:ok %, new.lgstepproitemeventid  %', ok, new.lgstepproitemeventid ;
  if ok  then
    if  new.lgstepproitemeventid is not null then
    /*
		  execute format( '
		  select
		     case
            when a.eventmulti is null then false
            else a.eventmulti
         end
        ,a.eventamt  EventAmtFlag
        ,a.debtadj
        ,a.mustremark
        ,a.eachdefd
		  from "dbLegal".tblgstepproitemevent  a	  where a.id = $1.lgstepproitemeventid '
		  ) using new
		  into rec  ;
      -- raise '715:new.eventmulti %', new.eventmulti;
      new.eventmulti:= rec.eventmulti;
      new.EventAmtFlag:= rec.EventAmtFlag;
      new.debtadj := rec.debtadj;
      new.mustremark := rec.mustremark;
      new.eachdefd := rec.eachdefd;
*/

 		  execute $x$
		  select
		     a.eventmulti
        ,a.eventamt  EventAmtFlag
        ,a.debtadj
        ,a.mustremark
        ,a.eachdefd
		  from "dbLegal".tblgstepproitemevent  a	  where a.id = $1.lgstepproitemeventid
		  $x$  using new
		  into
       new.eventmulti
      ,new.EventAmtFlag
      ,new.debtadj
      ,new.mustremark
      ,new.eachdefd
      ;

    end if;
  end if;
 -- raise     '766:new.sysevent %, lgstepproitemid %', new.sysevent, new.lgstepproitemid;
  return new;
end ; $$ language plpgsql;


create  or replace FUNCTION "dbLegal".trcasetranVW_UserData ( in UserID int , in AccID int
  ,in _id int = null
  ,in _assignment boolean = false   -- assignment
  ,in _casekind smallint = null
  ,in _wkfrec_name text = null

)
returns table (
    id int

	,ReceiveNo text -- varchar -- b.Tvalue  เลขที่รับเรื่อง -- ReceiveNo
	,Title text -- ,a.Title  เรื่อง

	,ReceiveDate timestamp_wtz --    วันที่รับเรื่อง -- ReceiveDate
    ,CustID int
		  ,CustName text -- concat_ws(' ',i1.Prefix, i1.FirstName ,i1.LastName, i1.Suffix, '-', i1.ID )ลูกค้า --  Customer
		,Defendant boolean --   ฝ่าย  --  Defendant vs Complaintor
    ,CourtRole text

    ,ReceiverID int
		  ,ReceiverName text -- concat_ws(' ', da.Firstname,da.Lastname,'-', d.TValue ) ผู้รับเรื่อง -- ReceiveBy
    ,OrgID int   -- 10
		  ,OrgName text --   concat_ws(' ',f1.Prefix, f1.FirstName  ) หน่วยงาน -- Org
    ,LawOfficerID int
		  ,LawOfficerName text --  concat_ws(' ',h1.Prefix, h1.FirstName ,h1.LastName, h1.Suffix, '-', h1.ID ) นิติกร -- LawOfficer
      ,lawofficer_contactid int
    ,LawOfficeID int
		  ,LawOfficeName text -- concat_ws(' ',o1.Prefix, o1.FirstName ,o1.LastName, o1.Suffix, '-', o1.ID ) สำนักงานทนาย-- LawOffice
    ,LawyerID int
		  ,LawyerName text  -- concat_ws(' ',p1.Prefix, p1.FirstName ,p1.LastName, p1.Suffix, '-', p1.ID ) ทนาย
    ,LGTypeID int
 		  ,LGTypeName text --   e.Title  ประเภทคดี  --LGType
    ,ProfileID int  -- 20
		  ,ProfileName  text -- concat_ws(' ',e1.Title,'-',e1.ID) โปรไฟล์คดี  ---  LegalProfile
    ,ActiveStepID int
		  ,ActiveStepName text -- concat_ws(' ',l1.Title,'-',l1.id) ขั้นตอนคดีที่แอคทีพ   -- ที่กำลังดำเนินการ   Active step
		,LGNo text  -- varchar -- b1.TValue LGNo
    ,TranDate timestamp_wtz    --  วันที่บันทึกข้อมูล
    ,TranDate_Dis timestamp_wtz  -- วันที่ตั้งคดี  =  วันที่จ่ายคดีให้ สนง. ทนาย
		,BlackNo text -- varchar -- j.TValue  คดีดำ   --BlackNo
		,RedNo text -- varchar -- k.TValue  คดีแดง  --  RedNo
    -- ,CalenNameID int
		,UID int
		,ACC int   -- 30
    ,ACNo text

    ,Status text  -- wk status
       ,wkfstatid int
    ,CaseComplete "dbSys".Percent
    ,CaseComplete_date timestamp_wtz
    ,CalTaskID int
    ,amt_total num   -- จำนวนเงิน ทุุนทรัพย์
    ,CourtID int     -- updatable
      ,CourtName text
      ,CourtAreaName text
    ,sue_date  timestamp_wtz
    ,BlackNoBD text
    ,RedNoBD text  -- 40
    ,Groups text
    ,remark text
    ,c_defd smallint   -- Count of defendant
    ,docid int

    ,CaseStatusID int
       ,CaseStatusName text
    ,LawCase  text
    ,defendant1_id int
      ,Defendant1_Name text
    ,docno_client text   -- เลชที่แฟ้มคดี  ของลูกค้า

    ,keyword  text
   -- ,casetranid int
    ,auto_copy boolean
    ,casekind smallint
    ,assign_lawoff boolean
    ,taskid int
    --,casetranid int
    ,upddt timestamp_wtz
    ,updateamount boolean
    ,eleadevent_enable boolean
    ,assettypeid  int
       ,assettype text
    ,refid int
      ,reflgno text
      ,reftrandate timestamp_wtz
    ,bankid int   -- bank contact id
       ,BankName text
    ,BankBranchID int  --branch contact id
       ,BankBranchName text
    ,tgCalculate  boolean
    ,tgPrintForm boolean
     ,caseasset_gen boolean
) AS $$
declare
   parm "dbSys".tbviewregis_userdata_type ;
   v_wkfrec_name text  = format('"dbSysWKF".trcasetrnvw%s_wkfrec',accid) ;

begin

  select userid, accid, _id, null::Int _pid, null::int  _idgrp into parm;

  return query execute $x$

		select
      a.id
			,b.Tvalue    --     เลขที่รับเรื่อง -- ReceiveNo
			 --,a.Title    --     เรื่อง
      ,case when a.title is null then a3.title else a.title   end   Title
			,n2.StartDate::timestamp_wtz  --   วันที่รับเรื่อง -- ReceiveDate
      ,a.CustID
			  --,concat_ws(' ',i1.Prefix, i1.FirstName ,i1.LastName, i1.Suffix  ) customer --  ลูกค้า --  Customer
			  ,i1._name  customer --  ลูกค้า --  Customer

			, a.Defendant  ฝ่าย  --  Defendant vs Complaintor
      ,a.CourtRole
      ,d.tvalue ReceiverID
			  --,concat_ws(' ', da.Firstname,da.Lastname )  ReceiveBy  ---   ผู้รับเรื่อง --
			  ,da._name  ReceiveBy  ---   ผู้รับเรื่อง --

      ,a.OrgID
			  --,concat_ws(' ',f1.Prefix, f1.FirstName  ) หน่วยงาน -- Org
			  ,f1._name  org --  หน่วยงาน -- Org

      ,h2.id  LawOfficerID -- h.tvalue  LawOfficerID   -- contact id
			  --,concat_ws(' ',h1.Prefix, h1.FirstName ,h1.LastName, h1.Suffix  )  lawofficer --  นิติกร -- LawOfficer
			  ,h1._name  lawofficer --  นิติกร -- LawOfficer

        ,h1.id lawofficer_contactid

      ,a.lawofficeid --- o2.id LawOfficeID
	    	--,concat_ws(' ',o1.Prefix, o1.FirstName ,o1.LastName, o1.Suffix  ) สำนักงานทนาย-- LawOffice
	    	,o1._name  lawoffice -- สำนักงานทนาย-- LawOffice


      --,p2.id  LawyerID --p.tvalue LawyerID
      ,a.lawyerid -- ,p2.id  LawyerID --p.tvalue LawyerID
	  		-- ,concat_ws(' ',p1.Prefix, p1.FirstName ,p1.LastName, p1.Suffix  ) LawyerName -- ทนาย
	  		,p1._name LawyerName -- ทนาย

      ,a.LGTypeID
			  ,e.Title  ประเภทคดี  --LGType
      --,e.LGStepProID  -- Profile id
      ,a.LGStepProID  -- Profile id
	  		,concat_ws(' ',e1.Title  )
      ,l.LGStepProItemID    -- LGStepProItem id
      ,"dbLegal".trcasetranVW_grpTitle_active(
         l1.title -- d.title
        ,l.idgrp  )  LGStepProItemTitle
      ,a.LGNo
      ,a.trandate  -- วันที่บันทึกข้อมูล
      ,a.TranDate_Dis
			,j.TValue  คดีดำ   --BlackNo
			,k.TValue  คดีแดง  --  RedNo
      -- ,a.CalenNameID
      ,a.UID
      ,a.ACC
      ,a.tranno  -- ACNo , a/c no

		  --,"dbSysWKF".wkfrecord_status( q11.orderno, q11.title, q11.statusname , app:=q1.app  , StatusAlias :=true ) Status  -- wk status
		 --  ,"dbSysWKF".wkfrecord_status( q1.orderno, q1.title, q1.statusname , app:=q1.app  , StatusAlias :=true ) Status  -- wk status
		 -- ,"dbSysWKF".fnworkflow_act_status_wkfrec( a.id,  case  when _wkfrec_name is not null then _wkfrec_name else  '"dbSysWKF".trcasetran_wkfrec' end ) status
	    ,q1.status   -- wkf status
	      ,q1.wkfstatid   -- wkf status id
      ,a.CaseComplete
			,a.CaseComplete_date
      ,a.caltaskid
      ,a.total amt_total   -- ทุนทรัพย์
      ,a.courtid
      --,concat_ws( ' ',r11.prefix, r11.FirstName, r11.LastName,r11.LastName ) CourtName
      ,r11._name CourtName
        ,r1.SiteName  ::text CourtAreaName
      ,(b111.startdate  ) ::timestamp_wtz -- sue_date
      ,to_date ( r2.tvalue,yt:=1, fm:='yyyy' )  BlackNoBD -- BD Year - Black no
      ,to_date ( r3.tvalue,yt:=1, fm:='yyyy' ) RedNoBD  -- BD Year - Red No
      --,"dbLegal".trcasetranGrp_getgroups( a.id ) CaseGrps
      ,a.CaseGrps
      ,a.remark
      --,s3.fqty c_defd
      ,s1.fqty c_defd
      ,a.docid
      ,a.casestatusid
         ,a2.title casestatusname
      ,a.lawcase
      ,s1.contactid defendant1_id
      ,s1.contactname  Defendant1_Name
      ,a.docno_client
      ,null::text  -- "dbLegal".tbcasestran_fulltext_search(a.id)  -- keyword
      ,a.auto_copy
      ,a.casekind
      ,a.assign_lawoff
      ,a.taskid
      ,a.upddt
      ,a.updateamount
      ,a.eleadevent_enable
       ,a.assettypeid
         ,a11.title assettype
		  ,a.refcasetranid  refid
		     ,a12.lgno    reflgno
         ,a12.trandate  reftrandate
      ,a13.id -- bank id
         ,a13.fullname -- bank name
      ,a14.id -- bankbranch id
        ,a14.fullname -- bankbranch name
     ,false   -- tgCalculate  boolean
     ,false   -- tgPrintForm boolean
     ,a.caseasset_gen
		from
			"dbLegal".trcasetran a
         left join "dbLegal".tbassettype a11 on a11.id = a.assettypeid
         left join "dbLegal".trcasetran a12 on a12.id = a.refcasetranid
         left join "dbLegal".trcasetrancont_contact(a.id, 'Bank' ) a13   on a13.pid = a.id
         left join "dbLegal".trcasetrancont_contact(a.id, 'BankBr' ) a14 on a13.pid = a.id
      left join "dbLegal".tbcasestatus a2 on a2.id = a.casestatusid
      left join "dbCalen".tbcaltask a3 on a3.id = a.caltaskid
			left outer join "dbLegal".trcasetranNo b on (b.pid = a.id and b.Title = 'Receive' and b.tcustom is null  )
			left outer join "dbLegal".trcasetranDate c on (c.pid = a.id and c.Title = 'Receive'and c.tcustom is null )
		  left outer join "dbLegal".trcasetranCont d on (d.pid = a.id and d.Title = 'Receive'and c.tcustom is null)
				-- left outer join "dbContact".tbContact da on (da.id = d.TValue  )
				left outer join "dbContact".tbContact_fullname( d.tvalue)  da on (da._id = d.TValue  )

			left outer join "dbLegal".tbLGType e on (e.id = a.LGTypeID )
				left outer join "dbLegal".tbLGStepPro e1 on (e1.ID = e.LGStepProID )
			left outer join "dbHR".tbOrg f on (f.id = a.OrgID )
				left outer join "dbContact".tbContact_fullname(f.contactid)   f1 on (f1._ID = f.ContactID )

			left outer join "dbLegal".trcasetranCont h on ( h.PID = a.id  and h.Title = 'Law Officer' )
			 -- left outer join "dbContact".tbContact h1 on (h1.ID = h.TValue )
				left outer join "dbContact".tbContact_fullname( h.tvalue)  h1 on (h1._ID = h.TValue )

        left join "dbHR".tbemp h2 on (h2.contactid = h.tvalue )

			left outer join "dbContExt".tbCust i on (i.ID = a.CustID )
				-- left outer join "dbContact".tbContact i1 on (i1.ID = i.ContactID )
				left outer join "dbContact".tbContact_fullname(i.contactid )  i1 on (i1._ID = i.ContactID )

			left outer join "dbLegal".trcasetranNo j on (j.PID = a.ID and j.Title = 'Black' and j.tcustom is null )
			left outer join "dbLegal".trcasetranNo k on (k.PID = a.ID and k.Title = 'Red' and k.tcustom is null  )

			left join  "dbLegal".trcasetranStep l on (l.PID = a.ID and l.Active  )
				left join "dbLegal".tbLGStepProItem l1 on (l1.ID = l.LGStepProItemID )
      left join "dbLegal".trcasetranevent_getdata1(a.id ,'Receive', strict:=false ) n2 on  n2.casetranid = a.id
      --left join "dbLegal".trcasetranevent n2 on n2.pid = a.id and n2.sysevent = 'Receive'


      left join "dbLegal".tblawoffice o2 on (o2.id = a.lawofficeid )
          --left outer join "dbContact".tbContact o1 on (o1.id = o2.contactid  )
          left outer join "dbContact".tbContact_fullname(o2.contactid )  o1 on (o1._id = o2.contactid  )

       left join "dbLegal".tblawyer p on (p.id = a.lawyerid )
          --left   join "dbContact".tbContact p1 on (p1.id = p.contactid  )
          left   join "dbContact".tbContact_fullname(p.contactid)  p1 on (p1._id = p.contactid  )

			 --* wkf status
		 --	left join "dbSysWKF".trcasetran_wkfrec q1 on ( q1.pid = a.id and q1.active )  -- ** Active status
		 --		left join "dbSysWKF".tbwkfstat q11 on ( q11.id = q1.wkfstatid )

		  --left join "dbSysWKF".fnworkflow_status_wkfrec ( a.id, $3, _active:=true  ) q1 on q1.pid = a.id
			left join "dbSysWKF".fnworkflow_status_wkfrec ( $1.accid, $3 , _active:=true  ) q1 on q1.pid = a.id

      left join "dbLegal".tbcourt r1 on (r1.id = a.courtid )
          --  left join "dbContact".tbcontact r11 on r11.id = r1.contactid
         left join "dbContact".tbcontact_fullname(r1.contactid) r11 on r11._id = r1.contactid

       left join "dbLegal".tbcasetrandc_contact1
       (  a.id
         ,title := "dbLegal".trcasetrandc_contra_title_by_courtrole( a.courtrole )
       ) s1 on s1.id = a.id
 				 left outer join "dbContact".tbContAddr s2 on ( s2.pid = s1.id and s2.TCustom is null and s2.Title = 'Home')  --    ทะเบียนบ้าน
						left outer join "dbContact".tbAddr s21 on ( s21.id = s2.TValue )
         left join "dbLegal".trcasetranevent_getdata(a.id,s1.contactid,'Notice' ) s22 on s22.casetranid = a.id
         left join "dbLegal".trcasetranevent_getdata(a.id,s1.contactid,'NoticeRec' ) s23 on s23.casetranid = a.id
      left join "dbLegal".trcasetranevent_getdata1(a.id ,'Sue' ) b111 on b111.casetranid = a.id
      -- BD Year
      left join "dbLegal".trcasetrandate r2 on ( r2.pid = a.id and r2.title = 'BlackNo' )
      left join "dbLegal".trcasetrandate r3 on ( r3.pid = a.id and r3.title = 'RedNo' )


	  where
      case
        when $1._id is null then a.acc= $1.accid
        else a.id = $1._id
      end
      and case
        when $2 is null then a.casekind is null
        else a.casekind = $2
      end

    order by
      n2.StartDate   -- วันที่รับเรื่อง
      ,a.lgno       -- LG No
   $x$ using parm, _casekind
     ,case
        when _wkfrec_name is not null then _wkfrec_name
        else case
          when "dbSys".havetable(  v_wkfrec_name ) then v_wkfrec_name
          else  '"dbSysWKF".trcasetran_wkfrec'
        end
	    end
	 ;

end; $$ LANGUAGE plpgsql stable PARALLEL SAFE;


create or replace function "dbLegal".trcasetranevent_getdata1 (
  inout casetranid int  -- case id
  ,in _sysevent text
  ,in strict boolean = true
  ,out StartDate  date
  ,out caltaskid int
  ,out debtcalcid int
 -- ,out casetraneventid int
 -- ,out eventno smallint
) returns record as $$
begin
  /*
  select
    s23.startdate
   ,s23.caltaskid
   ,s23.debtcalcid

  from  "dbLegal".trcasetranevent s23

    left join "dbLegal".tblgstepproitemevent b on       b.id = s23.lgstepproitemeventid
  where  s23.pid = casetranid
    and case
      when strict then  b.sysevent = _sysevent
      else s23.sysevent = _sysevent
    end
  order by EventNo desc
	limit 1
  into
    StartDate
   ,caltaskid
   ,debtcalcid
  ;

  if startdate is null then
    startdate := (
          select  b.startdate::date
          from "dbLegal".trcasetranevent a
            left join "dbCalen".tbcaltask b on b.id = a.caltaskid
          where a.pid = casetranid and a.title = vartext('legal_วันที่รับเรื่อง')
    ) ;
  end if;
  */
	execute $x$
  select
    s23.startdate
   ,s23.caltaskid
   ,s23.debtcalcid
  -- ,s23.id
 --  ,s23.eventno
  from  "dbLegal".trcasetranevent s23

   -- left join "dbLegal".tblgstepproitemevent b on       b.id = s23.lgstepproitemeventid
  where  s23.pid = $2
    and s23.sysevent = $1
  --  and case
    --  when strict then  s23.sysevent = $1 else s23.sysevent = $1
  --  end
  order by EventNo, orderno
	limit 1
	$x$ using _sysevent, casetranid
  into
    StartDate
   ,caltaskid
   ,debtcalcid
  -- ,casetraneventid
  -- ,eventno
 ;

end $$ language plpgsql stable ;


create or replace function "dbLegal".trcasetran_UpdateAmount()
returns trigger as $$
declare
 ok boolean;  amt_reset boolean; rec record; diff  record; _total num;
  err text;
begin
  --	raise notice '1555: new.updating_amount %', new.updating_amount;
  if diff( old.updateamount,new.updateamount ) then
	  if new.updateamount  then
	    --* Not allow change if have its own case amt setting
	    if numval( new.count_calc)=0 then
		   --    raise '3999';
		    if new.count_asset >0 then
		      execute $x$
			    select
			       sum(a.caseamt ) caseassetamt
			      ,sum(b.tvalue) casetranamt
			      ,diff( sum(a.caseamt )::numeric , sum(b.tvalue)::numeric  ) ok
			    from "dbLegal".trcasetranasset a
			      left join "dbLegal".trcasetranamt b on b.pid = a.pid
			    where a.pid  = $1.id
			    group by a.pid
			    $x$ using new
			    into diff ;

			  else
			    select true ::boolean ok  into diff;
			  end if;

    --    raise '4007: ok % ', diff.ok ;
        if diff.ok then

		      --* select * from "dbLegal".trcasetranasset
		      amt_reset:=false;
	        if not  amt_reset then
	          execute $x$
	          delete from   "dbLegal".trcasetranamt  where pid = $1.id
	          $x$ using new;
	          amt_reset =true;
	        end if;

		      for rec in  execute $x$
		        select  a.id,  a.caseamtid , a.caseamt, a.caseamtdate

		          ,numval( b1.tvalue)  amt_debtcalcsum
		        from "dbLegal".trcasetranasset a
		          left  join "dbLegal".tbdebtcalc b on b.id = a.caseamtid
		            left  join only "dbLegal".tbdebtcalcsum b1 on b1.pid = a.caseamtid and b1.is_total

		        where a.pid  = $1.id
		        $x$ using new
		      loop
--	  raise      '4019:rec.caseamtdate %, rec.caseamt %, amt_reset %, rec.amt_debtcalcsum %', rec.caseamtdate, rec.caseamt, amt_reset, rec.amt_debtcalcsum ;

		        --* Refresh caseamt if out of sync to its caseamtid
		        if diff( rec.caseamt, rec.amt_debtcalcsum) then

		           -- update "dbLegal".trcasetranasset  set caseamt =  rec.amt_debtcalcsum  where id = rec.id ;
		           update "dbLegal".tbdebtcalcitem set tvalue = tvalue - (rec.amt_debtcalcsum - rec.caseamt ) where pid = rec.caseamtid and
		             id in (
		               select id from "dbLegal".tbdebtcalcitem where pid = rec.caseamtid limit 1
		             );
		        end if;

		        --* add new
						--* Sum amt by caseasset amt
		        select total from  "dbLegal".tbdebtcalc_sum (
		           new.id    -- tar - casetran
		          , rec.caseamtid  -- source
		          ,target_item_ds := '"dbLegal".trcasetranamt'

		        ) into _total;
           --  raise notice  '4045:total %', _total;
		        --* clear old value once it 's used to update casetranamt
		        --perform "dbLegal".tbdebtcalc_clear_oldvalue(  rec.caseamtid  );
		        execute $x$
		        update "dbLegal".tbdebtcalc set old_total=null, old_c_item = null where id = $1
		        $x$ using rec.caseamtid;


			    end loop;
       end if; -- diff.ok
		  else
	      /*
	      -- raise exception '0021:%', format('Can not change asset, it have asset calculate already');
	       err := 'ไม่สามารถแก้ไขทุนทรัพย์ได้ เนื่องจากมีการบันทึกยอดทุนทรัพย์โดยตรงแล้ว';
	 			select msg from "dbSys".sysCallErrMsg(
	 				113
	 				,format('%I.%s',tg_table_schema, tg_table_name) -- talbename  = description
	 				,new.id
	 				,NEW.uID
	         		,errmsg := err
	 			 )
	 			into err;
	       raise exception '%', format(err ) ;
	       */
		  end if;
		  --*
		  select a.tvalue from only  "dbLegal".trcasetranamt a where a.pid = new.id and a.is_total  into _total;
	    --* toggle to false
	    execute $x$ update "dbLegal".trcasetran set updateamount=false,total=$2  where id = $1.id $x$ using old, numval( _total);
	  end if;
	end if;
  return new;
end ; $$ language plpgsql;