UFN_QUERY_MKTCOMMUNICATIONEFFORTWITHOUTTEMPLATEKPISLIST

create function dbo.[UFN_QUERY_MKTCOMMUNICATIONEFFORTWITHOUTTEMPLATEKPISLIST]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier
)
returns @KPI_DETAIL table
(
  [KPIINSTANCEID] uniqueidentifier,
  [NAME] nvarchar(255),
  [GOALTYPECODE] tinyint,
  [KPIVALUEMONEY] decimal(20,4),
  [KPIVALUENUMBER] decimal(20,4),
  [KPIVALUEPERCENT] decimal(20,4),    
  [GOALVALUEMONEY] decimal(20,4),
  [GOALVALUENUMBER] decimal(20,4),
  [GOALVALUEPERCENT] decimal(20,4),    
  [YELLOWTARGETMONEY] decimal(20,4),
  [YELLOWTARGETNUMBER] decimal(20,4),
  [YELLOWTARGETPERCENT] decimal(20,4),    
  [GREENTARGETMONEY] decimal(20,4),
  [GREENTARGETNUMBER] decimal(20,4),
  [GREENTARGETPERCENT] decimal(20,4),    
  [GOALSTATUSIMAGE] nvarchar(255),
  [VARIANCE] decimal(20,4),
  [CLOSESTASOFDATE] datetime,    
  [USERHASRIGHTSTOKPI] bit,    
  [CURRENCYID] uniqueidentifier
)
with execute as caller
as
begin

  insert into @KPI_DETAIL
  (
    [KPIINSTANCEID],
    [NAME],
    [GOALTYPECODE],
    [KPIVALUEMONEY],
    [KPIVALUENUMBER],
    [KPIVALUEPERCENT],
    [GOALVALUEMONEY],
    [GOALVALUENUMBER],
    [GOALVALUEPERCENT],
    [YELLOWTARGETMONEY],
    [YELLOWTARGETNUMBER],
    [YELLOWTARGETPERCENT],
    [GREENTARGETMONEY],
    [GREENTARGETNUMBER],
    [GREENTARGETPERCENT],
    [GOALSTATUSIMAGE],
    [VARIANCE],
    [CLOSESTASOFDATE],
    [USERHASRIGHTSTOKPI],
    [CURRENCYID]
  )
    select 
      [KPIINSTANCEID],
      [NAME],
      [GOALTYPECODE],
      [KPIVALUEMONEY],
      [KPIVALUENUMBER],
      [KPIVALUEPERCENT],    
      [GOALVALUEMONEY],
      [GOALVALUENUMBER],
      [GOALVALUEPERCENT],
      [YELLOWTARGETMONEY],
      [YELLOWTARGETNUMBER],    
      [YELLOWTARGETPERCENT],
      [GREENTARGETMONEY],
      [GREENTARGETNUMBER],
      [GREENTARGETPERCENT],
      [GOALSTATUSIMAGE],
      [VARIANCE],
      [CLOSESTASOFDATE],    
      [USERHASRIGHTSTOKPI],
      [CURRENCYID]
    from
      dbo.[UFN_QUERY_MKTCOMMUNICATIONEFFORTKPISLIST](@ID, @CURRENTAPPUSERID);

  return;

end;