====== pears.NetTempShiftStatusString ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create function "pears"."NetTempShiftStatusString"( in "pShiftStatus" char(30),in "pTempConfirmed" integer default null,in "pClientConfirmed" integer default null,in "pCancelReason" char(255) default null ) 
returns char(200)
deterministic
begin
  if "pShiftStatus" = 'B' then
    return 'Confirmed'
  end if;
  if "pShiftStatus" = 'W' then
    return 'Worked'
  end if;
  if "isnull"("pShiftStatus",'') = '' then
    return 'Unfilled'
  end if;
  if "pShiftStatus" = 'P' then
    return "string"('Pending',
      case when "pClientConfirmed" = 0 and "pTempConfirmed" = 0 then
        ' Client and Temp Confirmation'
      when "pClientConfirmed" = 0 then
        ' Client Confirmation'
      when "pTempConfirmed" = 0 then
        ' Temp Confirmation'
      end)
  end if;
  if "pShiftStatus" = 'C' then
    return "string"('Cancelled',
      (if "pCancelReason" is not null then ': '+"pCancelReason" endif),
      case when "pClientConfirmed" = 0 and "pTempConfirmed" = 0 then
        ', Awaiting Client and Temp Notification'
      when "pClientConfirmed" = 0 then
        ', Awaiting Client Notification'
      when "pTempConfirmed" = 0 then
        ', Awaiting Temp Notification'
      end)
  end if
end /* DOC
30-05-2018 PC IW-558 move state detail to second routine
6/6/2018 MHS re-worked and sorted tests

*/
/* TEST
select NetTempshiftStatusString('W') as A;
expect A=Worked;
select replace(NetTempshiftStatusString('C',0,0,'Test'),',','#') as A;
expect A=Cancelled: Test# Awaiting Client and Temp Notification;
select NetTempshiftStatusString('P',0,1) as A;
expect A=Pending Temp Confirmation;
*/
</code>