This contains information on how to add in missing info to the shared tables and how to set the transfer batch back if it needs to be re-run
MISSING INFO
In PearsReports, Solution Recruitment folder, Payroll, look for Pegasus Timesheet Transfer.xml which contains the field codes reproduced below.
Identify the fieldid for the item that is wrong or missing.
In our example, the bank sort code and account number were missing.
First get the payroll number of the candidate record.
Then identify the transactionid:
select * from iqxpayroll.temppayrolltransactions where payrollnumber = '12868'; this will get the transactionid.
select * from iqxpayroll.temppayrolltransactiondata where transactionid = 89895; this will give us the data and show the blank, or missing data.
use this to insert the missing field and data, like so
insert into iqxpayroll.temppayrolltransactiondata (transactionid,dataid,datavalue) values (89895,30,'11-11-11'); this adds the field (30) for sort code and the value.
insert into iqxpayroll.temppayrolltransactiondata (transactionid,dataid,datavalue) values (89895,32,'11111111'); this add the field (32) for account number and the value.
Another example where a lowercase character was in the value field of the record, Pegasus wanted an upper-case and would not import the data.
Again get the payroll number for the temp, this time we do not have to insert data, merely update it. The question was for gender, M or F.
The fieldid for gender = 7, again we get this from the code reproduced at the foot of this note.
select * from iqxpayroll.temppayrolltransactions where payrollnumber = '12868';
select * from iqxpayroll.temppayrolltransactiondata where transactionid = 89895;
use this to update the field data, like so
update IQXPayroll.TempPayrollTransactionData set datavalue = 'F' where dataid = 7 and transactionid = 89895;
Once this data has been updated they should be able to import into Pegasus.
TRANSFER BATCH
If Solutions want to re-import the data then the status in the transfer table needs to be set back to 1.
Select * from iqxpayroll.temppayrolltransactions where transferbatch = x;
Get them to check correct data by looking at names etc. (status should be 10, if it is 1 it has not been imported so nothing to do if correct batch.
If ok
update iqxpayroll.temppayrolltransactions set status = 1 where transferbatch = x;
Field names and ID's
<SetVariable name="HeaderDone" value="F"/>
<SetVariable name="transID" value="0"/>
<Call fieldname="surname" fieldvalue="{surname}" fieldid="2">CheckField</Call>
<Call fieldname="forenames" fieldvalue="{forenames}" fieldid="3">CheckField</Call>
<Call fieldname="title" fieldvalue="{title}" fieldid="4">CheckField</Call>
<Call fieldname="ni" fieldvalue="{ni}" fieldid="5">CheckField</Call>
<Call fieldname="dob" fieldvalue="{dob}" fieldid="6">CheckField</Call>
<Call fieldname="sex" fieldvalue="{sex}" fieldid="7">CheckField</Call>
<Call fieldname="addr1||addr2||addr3||town||county" fieldvalue="{addr}" fieldid="9">CheckField</Call>
<Call fieldname="postcode" fieldvalue="{postcode}" fieldid="14">CheckField</Call>
<Call fieldname="telephone" fieldvalue="{telephone}" fieldid="15">CheckField</Call>
<Call fieldname="mobile" fieldvalue="{mobile}" fieldid="16">CheckField</Call>
<Call fieldname="textpaymentadvice" fieldvalue="{textpaymentadvice}" fieldid="17">CheckField</Call>
<Call fieldname="email" fieldvalue="{email}" fieldid="18">CheckField</Call>
<Call fieldname="emailpayslips" fieldvalue="{emailpayslips}" fieldid="19">CheckField</Call>
<Call fieldname="employmentdate" fieldvalue="{employmentdate}" fieldid="20">CheckField</Call>
<xxxCall fieldname="leavingdate" fieldvalue="{leavingdate}" fieldid="21">CheckField</xxxCall>
<Call fieldname="payfrequency" fieldvalue="{payfrequency}" fieldid="22">CheckField</Call>
<Call fieldname="paymethod" fieldvalue="{paymethod}" fieldid="23">CheckField</Call>
<Call fieldname="bankname" fieldvalue="{bankname}" fieldid="24">CheckField</Call>
<Call fieldname="bankaddr1 || bankaddr2 " fieldvalue="{bankaddr}" fieldid="25">CheckField</Call>
<Call fieldname="banksortcode" fieldvalue="{banksortcode}" fieldid="30">CheckField</Call>
<Call fieldname="bankacname" fieldvalue="{bankacname}" fieldid="31">CheckField</Call>
<Call fieldname="bankacno" fieldvalue="{bankacno}" fieldid="32">CheckField</Call>
<Call fieldname="bankref" fieldvalue="{bankref}" fieldid="33">CheckField</Call>
<Call fieldname="vatregno" fieldvalue="{vatregno}" fieldid="67">CheckField</Call>
<Call fieldname="businesstype" fieldvalue="{businesstype}" fieldid="93">CheckField</Call>
<Call fieldname="p45requested" fieldvalue="{p45requested}" fieldid="121">CheckField</Call>
<xxxCall fieldname="appnumber" fieldvalue="{appnumber}" fieldid="161">CheckField</xxxCall>
<Call fieldname="divisionanalysis" fieldvalue="{divisionanalysis}" fieldid="162">CheckField</Call>
<Call fieldname="branchanalysis" fieldvalue="{branchanalysis}" fieldid="164">CheckField</Call>
<Call fieldname="TaxCode" fieldvalue="{TaxCode}" fieldid="175">CheckField</Call>
<Call fieldname="NILetter" fieldvalue="{NILetter}" fieldid="176">CheckField</Call>
<Call fieldname="Week1" fieldvalue="{Week1}" fieldid="177">CheckField</Call>
<If x1="{businesstype}" x2="0" comparison=">" type="String">
<Call fieldname="coname" fieldvalue="{coname}" fieldid="57">CheckField</Call>
<Call fieldname="regno" fieldvalue="{compregno}" fieldid="61">CheckField</Call>
<Call fieldname="vatregno" fieldvalue="{vatregno}" fieldid="67">CheckField</Call>
<SQLExec><![CDATA[insert into IQXPayroll.TempPayrollTransactionData
(transactionid, dataid, datavalue) values (:transid, 98, :VATRegd)]]></SQLExec>
<If x1="{businesstype}" x2="2" comparison="=" type="String">
<Call fieldname="cisverno" fieldvalue="{cisverno}" fieldid="62">CheckField</Call>
<Call fieldname="cistype" fieldvalue="{cistype}" fieldid="94">CheckField</Call>
<Call fieldname="cisutr" fieldvalue="{cisutr}" fieldid="95">CheckField</Call>
<Call fieldname="cisstart" fieldvalue="{cisstart}" fieldid="96">CheckField</Call>
<Call fieldname="cisend" fieldvalue="{cisend}" fieldid="97">CheckField</Call>