create or replace
PROCEDURE proc_AddUpdateSumFooter(
    TableName in varchar2,
    OrgID in nvarchar2,
    TimeStamp in varchar2
)
AS

    Param_TargetAssessScore number;
    Param_ExceedLimitAssessScore number;
    Param_TotalScore number;


    SqlIsExist varchar2(500);
   mySql varchar2(500);
   mySql2 varchar2(500);
   tID number;
begin  --the add 
 
  execute immediate 'select sum(Param_TargetAssessScore) into Param_TargetAssessScore 
                     from   '||TableName||' where TimeStamp='||TimeStamp||' and  OrgID = '||OrgID||' and 
                     Param_ID is not null';
    if Param_TargetAssessScore is null then
    Param_TargetAssessScore := 0;
     elsif Param_TargetAssessScore > 100 then
    Param_TargetAssessScore := 100;
    elsif Param_TargetAssessScore <0 then
    Param_TargetAssessScore := 0;
    end if;


  execute immediate 'select sum(Param_ExceedLimitAssessScore) into Param_ExceedLimitAssessScore 
                     from   '||TableName||' where TimeStamp='||TimeStamp||' and  OrgID = '||OrgID||' and 
                     Param_ID is not null';
    if Param_ExceedLimitAssessScore is null then
    Param_ExceedLimitAssessScore := 0;
     elsif Param_ExceedLimitAssessScore > 100 then
    Param_ExceedLimitAssessScore := 100;
    elsif Param_ExceedLimitAssessScore <0 then
    Param_ExceedLimitAssessScore := 0;
    end if;
    
      execute immediate 'select sum(Param_TotalScore) into Param_TotalScore 
                     from   '||TableName||' where TimeStamp='||TimeStamp||' and  OrgID = '||OrgID||' and 
                     Param_ID is not null';
    if Param_TotalScore is null then
    Param_TotalScore := 0;
     elsif Param_TotalScore > 100 then
    Param_TotalScore := 100;
    elsif Param_TotalScore <0 then
    Param_TotalScore := 0;
    end if;


 mySql2 :='SELECT ID into tID FROM   '||TableName||'   where TimeStamp='|| TimeStamp ||' and  OrgID =' || OrgID ||'  and Param_ID is null)'; 

execute immediate mySql2;

if tID is not null then 

mySql :='    INSERT INTO '|| TableName ||' (
        ID,
        OrgID,
        TimeStamp,
        Param_TargetAssessScore,
        Param_ExceedLimitAssessScore,
        Param_TotalScore
    ) VALUES (
        ''f2acf9cf-a561-4e99-8c00-8e70b1293cd3'',
        '||OrgID||' ,
        '||TimeStamp||',
        convert(float, ' || Param_TargetAssessScore|| '),
        convert(float, ' ||  Param_ExceedLimitAssessScore ||'),
        convert(float,  ' || Param_TotalScore ||')
    )';
else 
  mySql := 'UPDATE ' || TableName ||'  SET
        Param_TargetAssessScore = convert(float, '|| Param_TargetAssessScore ||'),
        Param_ExceedLimitAssessScore =convert(float,  '|| Param_ExceedLimitAssessScore||'),
        Param_TotalScore = convert(float,  '|| Param_TotalScore||')
    WHERE
        OrgID='||OrgID||'  and    TimeStamp = '||TimeStamp||' and Param_ID is null';


end if;

execute immediate mySql;

end proc_AddUpdateSumFooter;







--endregion