REFERNCE TICKET: INC 1349492

DESCRIPTION: IRP agents noted that Marcy Walters had 2 files in our system DFO 202100088  and DFO 2021000889. 

File DFO 2021000889 is the active and functional file that the member was using for their relocation.

File DFO 202100088 was a file that the agents were trying to cancel.

When IRP agents are performing quick search for file DFO 202100088 specifically they are receiving the following error message.  

I've logged into the iZone web server (TORIRPIWEBPV001) using my biz\z_username account and repeated the process to search for member DFO 202100088 and received the true error. It appears to be a NULL value.

TROUBLESHOOTING:

I've launched SQL Profiler and used the Text value (%DFO 202100088%) to capture process flow details and noted the below store procedure calls while performing the search for DFO 202100088 in iZone

--Script1: 

SELECT top 10 t.projectid, t.ee_first, t.ee_last, e.eenumber, p.prompt_eng as filestatus from trmaster t with (nolock) JOIN CLIENT C with (nolock) ON C.PIN = t.PIN left join employ e with (nolock) on t.projectid=e.projectid left join pop_list p with (nolock) on p.keyvalue=t.projstatus and p.pop_key='PROJSTATUS' where C.CLIENTGROUP IN ('DND','GOC','RCMP') AND (t.projectid like 'DFO 202100088%' or t.ee_last like 'DFO 202100088%' or t.ee_first like 'DFO 202100088%' ) order by t.CreateDate desc

--Script2 
exec TpQuickSearchFN @content='DFO 202100088',@count=10,@LoginRole='(''DND'',''GOC'',''RCMP'')',@UserID='slumbania'

--Script3
exec sp_executesql N'select projectid,syscode,temp_num,ee_last,ee_initial,ee_first,ee_salu,street,city,provcode,pin,filetype,clientid,billcont,postal,opened_dt,closed_dt,trxfr_dt,projstatus,movetype,origcity,destcity,authname,confidentl,vip,billacctno,billdiv,coordinatr,subscoordr,officeid,prebill3,auth_recd,gicex,destoffid,team_id,mscouple,fiscalyear,fileassign,programCode,postingMsg_dtg,selfregistrations,ReportNewLocDate,CostCentre,Fund,ArchiveDate,DeactivateDate,DeactivateReason,ReactivateDate,CreateDate,CreatedBy,update_dt,lchange_no,opid,record_id,record_id2,gicexOLD,ee_middle,contractreference,BirthDate,Gender,TravelSalutation,DeclinedAirTravelInfo,TIN from trmaster where projectid=@projectid',N'@projectid varchar(13)',@projectid='DFO 202100088'
 

--Script4

exec RBACGetFilePermission @Roles='ADMIN;AUTHORIZATIONS;CAA;CCA;CLIENTMAINTANANCE;DMSUPERADMIN;DMUSERADMIN;DND;FILEASSIGN;GOC;GSUSER;KMUPDATE;QA;RCMP;REPORTSYSTEM;SUPPLIERINVOICE;',@FileNumber='DFO 202100088'
exec GetMenuItems @UserId='slumbania',@MenuType='FileLevel',@Roles='ADMIN;AUTHORIZATIONS;CAA;CCA;CLIENTMAINTANANCE;DMSUPERADMIN;DMUSERADMIN;DND;FILEASSIGN;GOC;GSUSER;KMUPDATE;QA;RCMP;REPORTSYSTEM;SUPPLIERINVOICE;',@FileNumber='DFO 202100088'

--Script5

exec sp_executesql N'select projectid,pin,empcode,eenumber,ee_salu,ee_first,ee_initial,ee_last,co_salu,co_first,co_initial,co_last,co_serviceno,phoneres,phoneee,ee_ext,language,o_bus_fax,title,emailadd,ph_cell,no_dep_ch,no_pets,pets_desc,lo_age,hi_age,smoker,sin,marstatus,no_depoth,no_people,no_vehicle,no_towing,annuitant,salary,salaryrate,bank,transit,bacctno,bankaddr,payLevel,yrservice,enrolmentPlace,verbal_prv_agreement,verbal_prv_agreement_dt,signed_prv_agreement,signed_prv_agreement_dt,revoke_prv_agreement,revoke_prv_agreement_dt,solicitation_acceptance,spousal_disclosure,pager,VehicleLN,update_dt,lchange_no,opid,record_id,record_id2,allowance,phone_destbus,phone_destbusext,phone_destres,ee_middle,co_middle,Declined_Access,Declined_Access_dt,Declined_Access_Comment,Access_Reinstated,Access_Reinstated_dt,Access_Reinstated_Comment,Total_Approved_Funding,SpouseBirthDate,SpouseGender,SpouseTravelSalutation,SpouseDeclinedAirTravelInfo,SpouseTIN from employ where projectid=@projectid',N'@projectid varchar(13)',@projectid='DFO 202100088'

All scripts above returned results except for Script5. It appears the employ table cannot contain 2 records with the same "employcode" as we received the error below when attempting to insert a record for the members file DFO 202100088

RESOLUTION:

The resolution provided was to add an underscore to the empcode to make this record unique. 

USE [irp]

Begin Tran

INSERT INTO [dbo].[employ]

([projectid]

,[pin]

,[empcode]

,[eenumber]

,[ee_last]

,[update_dt]

,[opid])

VALUES

('DFO 202100088', '1128', '_01369708', '055725937', 'Walters', getdate(), 'slumbania' )

if @@ERROR > 0 or @@ROWCOUNT <> 1

BEGIN

ROLLBACK TRAN

RETURN

END

commit tran

Once the DBCR was applied the agent was able to validate she can access the member file to cancel.