International Disbursements - New Zealand bulk payments in 277-08060000

New Zealand collects money for Australian Child Support cases. New Zealand send the money to Australian Child Support for distribution to payees in Australia.

Schedule of payments is received into the International Disbursement mailbox, after the 6th working day of each month. If it does not arrive, contact NZIR and request them to email it ASAP.

Format the spreadsheet and convert it to a .prn file before the 14th of each month:

• Open the schedule and save a copy to desktop
• Check the Autosum provided by NZ in cell D8. Select this cell and check the function box it should read =Sum($D$10:$D$99996). Ensure the amount in cell D8 matches the amount in B4
• In the footer, select the + to create a new worksheet:
• Select rows 1 to 8 on sheet 1 and cut and paste them into sheet 2. These details will be used later
• Delete the blank rows 1 to 8 on sheet 1
• Highlight Column D using the right mouse. Select Format Cells
• Format the cells to be a number with 2 decimal places, ensure the use 1000 separator (,) is not ticked. The figures in Column D will no longer have $ or commas (,)
• Highlight Row 1 and select the Data tab from the Tools bar, then select Filter
• Sort the spreadsheet by Payer Child Support Identity (CSID). Using the drop down at the top of Column C select Sort smallest to largest
• Check the schedule to ensure all customers have a CSID and IRD:
• Use the drop down filter at the top of Column C and Column E
• Check to see if there are any blanks by scrolling to the bottom
• If there are, deselect the Select all box at the top and select Blanks
• Attempt to locate missing CSIDs or IRDs. A name search can be used in Cuba or previous schedules
• If unable to locate the missing details advise SAS National who will follow up with New Zealand Inland Revenue Child Support (NZIRCS)
• Insert a new column after Column A (Payer Last Name) and Column C (Payer First Name) so the new columns are Column B and Column D
• Select File > Open > Browse:
• Go to G:\Int\DHS_International- child Support\Central Authority Team\Disbursements\NZ Schedules and Errors\NZ Error Report (SYSOUT
• Open file NZ schedule – SYSOUT Prevention

• In cell B2 on the schedule create a VLOOKUP where the lookup value is the Child support identity (CSID):
• Table Array is all rows in Columns B, C and D of the NZ Schedule – SYSOUT Prevention spreadsheet
• Column index is 3
• Range lookup is false
• Drag this formula to the end of the schedule
• In cell D2 create a VLOOKUP where the lookup value is the CSID:
• Table Array is all rows in Columns B, C and D of the NZ Schedule – SYSOUT Prevention spreadsheet
• Column index is 2
• Range lookup is false
• Drag this formula to the end of the schedule
• Select Global > Copy > Save values

• Columns B and D will now have first and last names of the customers who need to be changed from the NZ schedule to match the records in Cuba:
• Sort the spreadsheet by Column B (A to Z) all the customers requiring updates are at the top
• Scroll down to the first #N/A. In the first cell with #N/A type the formula =Upper(A???) where ??? is the row number of the first #N/A in Column B
• Drag the formula to the bottom of the spreadsheet
• Repeat for Column D – then select Global > Copy > Paste values and Save
• When both Columns B and D are all upper case and show the names required to be used in the PRN:
• Copy and paste the heading from Column A to Column B and from Column C to Column D
• Delete Column A and Column C
• Check for spaces at the start of any names by using the drop down at the top of the columns and selecting A to Z and then Z to A, checking if any of the names have spaces at the top after each
• Check a sample of CSIDs in Cuba to ensure the customer name, IRD and CSID match
• Perform an Autosum to confirm the total amount is the same as at the start
• Delete Autosum cell
• Select Sheet 2 from the footer:
• Copy rows 1 – 8 back into the top of sheet 1. (click in cell A1 - insert - copied fields - yes)
• Right mouse click on sheet 2 and Delete
• Save this into the appropriate file (Z drive/International/Old files/Payments In and Out/$$ received from NZ schedule in/SCHEDULES/

Insert formula to convert NZ$ to AU$ in cell F10:

• Create a formula in cell F10 to multiply the exchange rate that was provided by NZIR with the cell that contains the NZ$ amount (column D)
  For example, if the exchange rate that was provided in cell B6 was 0.78411, the formula entered in cell F10 would be =(0.78411*D10)
• Double click (assisted technology use Select > Select) on the bottom right corner of Cell F10 to copy the formula, this will produce the AU$ value for all customers
• Select Column D, then the Home tab on tools ribbon:
• Select Format Painter. The cursor will change to a paint brush
• Click to select Column F, and the format now from Column D will be reflected in Column F
• Add a heading into Column F – Payer Amount $AU
• Add an Autosum at the bottom of Column F. Ensure the AUD amount matches the amount in B5
• Open Cuba and search for an organisation. Use NZIR to bring up the NZ Employer profile. Check the amount received from NZ in Cuba matches the amount in the spreadsheet
• Copy>Paste Special>Values>Ok
• Save
• IMPORTANT - Resave the spreadsheet again but this time back to the desktop. Save the spreadsheet for our records, now turn it into a .prn file.
• Delete rows 1 to 8
• Save

After payment information is received from NZIR and the .prn file has been created and saved to the correct location, check that NZIR have sent the funds and been received by Child support.

• The correct account for NZIR to deposit funds into is:
• BSB: 092-009
• Account number: 116755
• Account name: CSA OFFICIAL RECEIPTS
• Open employer CSID 109036845959 on Cuba
• In the Outstanding box on the bottom RHS there should be an amount showing under Payment List. This amount should be the same as Payment Schedule (PRN) sent to Child Support from NZIR
• Click on the Payments List link or access via Accounting/Payment Lists/Find
• The Payments box should have a Received and Posted date showing when the payment has cleared into the Services Australia bank account and the amount. This confirms the payment has been received and is in the correct bank account

Note: the funds must by in the Child Support bank account before IT process the .prn file on the 15th of the month

Highlight each column and right mouse click. Select column width and set the widths as below:

• Column A to 25.29
• Column B to 27.29
• Column C to 18.29
• Column D to 16.71
• Column E to 16.29
• Column F to 15.14

Format spreadsheet:

• Right justify Colum F
• Left justify columns A, B, C, D and E
• Delete any Autosum amounts at the bottom of the Schedule
• Save (keep this on your desktop, if there are errors later you will be able to come back to the sheet saved at this stage)

Save file as .prn

• Save the file again to your desktop as a .PRN file with the name OSA input data
• In the Save as type dropdown menu, select Formatted Text (Space delimited) (*prn)

Format the file in a specific way or it will not load properly. There is a template called Ruler for Batchloading schedules that checks the formatting is correct.

• Open the .prn data file from your Desktop as a notepad (hold mouse over file icon, right click, select open, choose option to open as a notepad)
• Open the Ruler for Batchloading schedules located at G:\Int\DHS_International_Child_Support\Central_Authority_Team\Disbursements\NZ schedules/ruler for Batchloading schedules
• Copy and paste the content from the ruler notepad into the prn notepad (see below)

• Check each column and ensure they are in the following positions:
• Column 1: Left justified and starts at position 1
• Column 2: Left justified and starts at position 26
• Column 3: Left justified and starts at position 53
• Column 4: Left justified and starts at position 71
• Column 5: Left justified starts at position 87
• Column 6: Right justified and ends at position 117
• If the file is in the correct format, remove the ruler from the prn file. Delete the headings as well
• Enter the Child Support Reference Number (CSRN) for NZ as the first line of the .prn file. The CSRN is 109036845959. The second line should be the first customer

Data will not load unless the above conditions are set. If the .prn file with the ruler does not match the positions listed above:

• return to the schedule you saved as an excel document on your desktop
• check the column widths to ensure they are correct

The ICT system checks the OSA deductions folder daily for .prn files. With NZ run this file on the 14th of the month.

Therefore, if the date you have created the .prn is prior to the 14^th of the month, you need to save a copy of the .prn in the pending folder. Move a copy of the .prn file from your Desktop to: \\ntidcsacroc\d\Inetpub\ftproot\Prod\OSA Deductions\Pending. Then set a calendar appointment in outlook for 9am on the 14th of the month. If the 14th is a weekend then set this for the last working day prior to the 14th. Add your disbursement team colleagues to the appointment.

On the 14th of the month the file needs to be moved to: \\ntidcsacroc\d\Inetpub\ftproot\Prod\OSA Deductions

If the file is not moved or is saved in the incorrect file format the ICT Batchloading system will not pick it up and the payments will be delayed.

Also move a copy of the .prn to: G:\Int\DHS_International_Child_Support\Central_Authority_Team\Disbursements\NZ Schedules and Errors.

Once formatted and saved, IT will pick up the file. ICT will action the file on the 15th of the month or the first working day after if this falls on a weekend, so payments can be automatically applied to Cuba and a “SYSOUT report” generated for payments requiring manual processing. The SYSOUT report is emailed to the International Disbursement Mailbox.

On the 15th of each month, or the first working day after the 15th, the SYSOUT Error report is received via email. This report is for money that NZIR have sent to Australia and cannot be applied to Cuba automatically.

You will need to format the report into a spreadsheet.

• Open the report, highlight from the first time the word Error is seen down to the last dollar figure, copy and paste into a new Excel spreadsheet
• Highlight column A, select the Data tab from the tools ribbon and then text to columns
• Select Delimited then press next
• Untick Tab and select space then press next
• Leave general selected and press finish
• Sort the spreadsheet by column B to group the errors together. Select the spreadsheet by global. Select Data from the tools ribbon, then select Sort. Sort the spreadsheet by column B. Do not tick my data has headers
• Delete rows from the top row down to where column B content changes from ERROR to OSA
• Delete columns A, B, C, D & E

Reformat the spreadsheet to ensure the reference numbers, names and amounts are in the correct columns. You will need to do this manually:

• Next remove all the ‘<’ characters next to the values. Highlight column C and press Control H. In find what: enter the character ‘<’. In replace with leave this blank. Press Replace All
• Save to Desktop
• Complete Autosum, make a note of this amount and delete Autosum cell
• Open the schedule sent from NZIR that was used to create the PRN file, for example (Z/International Disbursements/Old files/Payments In and Out/$$ Received from NZ Schedule in/SCHEDULES/2019-2020 Fin Year/3.To Hobart September 2019-9). Obtain the AUD$ amount sent by NZIR and make a note of this
• Open NZIR employer CSID 109036845959 in Cuba. Obtain the Reconciliation list amounts for NZIR. This appears on the main screen for the organisation NZIR
• The Autosum amount derived at step 3 above (SYSOUT Total) added to the Reconciliation List amount for NZIR on Cuba should add up to the amount on the NZ schedule (with rounding errors this can be up to $5 out)
• Create a new column A. Insert a new Row 1 to use for headings. Label the new column A – CSRN, column B – IRD, column C – Name and column D – Amount
• Select File>Open>browse and navigate to this months NZ schedule, for example: \\ntidcsacroc\d\Inetpub\ftproot\Prod\PAYFILE\Client Accounting Unit\International Disbursement\Old files\Payments In and Out\$$ Received from NZ, schedule in\SCHEDULES
• Highlight column C (PAYER CSID) and copy. Highlight column F and insert copied cells, this will create a second CSID column to the right of the IRD column
• Search for the correct CSID to match the IRD on the SYSOUT using the VLOOKUP function:
• Select cell A2 and insert a VLOOKUP where the lookup value is the IRD on the SYSOUT report, the table array is columns E and F on this months schedule, the column index is 2 and the range lookup is false. Copy the formula down to the bottom to generate a CSID for each entry on the SYSOUT report
• Format column A as a number. Highlight column A, right mouse click, format cells, Number, No decimals
• Randomly check five entries with data on Cuba to ensure that Name and CSIDs match
• Remove the formula by highlighting Global
• Copy>Paste Special>Values>Ok
• Save
• Complete Autosum and ensure it is the same as the earlier Autosum conducted, then delete Autosum cell
• File the email in New Zealand In/ICT Output Files
• See Step 1 in the Action NZ Error report in Cuba instructions

• Open the Reconciliation Reports in Cuba with the lowest dollar amount
• Work through the Error Report spreadsheet, copying the CSID and pasting into the Client ID field in Cuba. From the Pay Date field use the dropdown arrow and select the date that populates. Copy the amount from the spreadsheet into the Actual field. In the Reason dropdown menu select ADD OS PAYER and then save
• If an error message appears saying 'The selected Payer currently has Employer Withholding established', click OK
• If an error message appears saying 'Employer Deduction record already exists for the same Payer and Pay date' click OK and mark the spreadsheet to come back to it
• If any of the CSIDs on the Error Report spreadsheet cannot be found highlight these and come back to them

Try some of the methods below to find CSIDs that were not located:

• Copy the CSID into the main Cuba screen to see if the customer has an alternate surname that may not be on the Error Report spreadsheet. Confirm it is the same customer by comparing the Inland Revenue Department IRD number on the spreadsheet to that on Cuba
• Copy the surname on the spreadsheet and open the first employer reconciliation report. Press Ctrl F and search for the customer. Try all of the reconciliation reports
• When the customer is found, confirm it is the correct customer on the reconciliation report by comparing IRD numbers. Copy the amount from the spreadsheet into the Actual field in Cuba. Click the Reason box and arrow down. Cuba will automatically select the correct option, either DEDN WAS MORE or DEDN WAS LESS
• Save
• Add up the totals from the Reconciliation files for the month. These must total the amount sent over on the schedule from NZIR (Z/International Disbursements/Old files/Payments In and Out/$$ Received from NZ Schedule in/SCHEDULES/2016-2017 Fin Year/3.To Hobart September 2016-3)

Note: there will usually be a discrepancy in this schedule because of the exchange rates used. A discrepancy of up to $5 is acceptable. Investigate discrepancies of more than $5.

After completing the previous steps, submit the payment files in Cuba to allow the payments to disburse to the payees.

• Open organisation NZIR in Cuba using CSID 109036845959
• Find the Reconciliation Reports List by selecting the following from the Reconciliation menu:
• Reconciliation Reports List>tick All>Find
• There will be Employer Reconciliation files that will have a status of SAVE or INT
• Highlight & open the first line (status on the RHS should say SAVE)
• Select Edit and then Submit, the Status will change from SAVE to INT
• Select Go and then Reconciliation match
• Highlight both the first line in the Unallocated Payments box and all lines (could be one or more) in the Unreconciled Employer Reconciliation Report box. Copy the Net Amount from the Reconciliation Details box and press tab, then paste the amount in the Excess Cash box. This could be the Excess Cash option in either the Pay or Use box. Cuba will select the correct box (usually the Pay box). Press tab again and the Net Amount should now be zero. Press SAVE. The status should have changed to SUB (submitted)
• On the remaining reports, highlight the first line in the Unreconciled Employer Reconciliation Report box. Copy the Net Amount from the Reconciliation Details box and press tab twice, then paste the amount in the Excess Cash box. This is in either the Pay or Use box (usually Use box), Cuba will select the correct box. Press tab again and the Net Amount should now be zero. Press SAVE The status should have changed to SUB (submitted)
• The files in the Reconciliation Report List will now have a status of SUB

Find the reason for the error that stopped the transaction being run automatically through Cuba. Correct the error.

It could be an error in the employer relationship window, a CSID, IRD or name, a completely wrong CSID, IRD or name, or the customer may have changed their name:

• Copy the CSID from the spreadsheet into Cuba and locate the customer. On the details page check:
• there is something in the Suburb/Town line
• the IRD on Cuba matches the IRD in the spreadsheet, and
• the IRD format is 2-3-3 or 3-3-3
• Go to the Employer Relationship window:
• Ensure the payer is linked to NZIR CSID 109036845959
• Ensure the Full Time Employee/Contractor radio button from the Employment Type is selected
• Ensure Employed is the status from the Employment Status box
• If no status date put today's date
• Select Payer from the Funds Paid To box
• Select Salary or Wages from the Payment By box
• Enter the IRD in the Payroll ID text box, ensure there are no spaces or dashes
• Pay Cycle must be MTLY
• Select the first available pay date from the First Pay Date dropdown menu
• Remove a Stop Withholding if there is one. In the Withholding Arrangements window update/add the Date to End field to any date before today's date
• A possible reason for the error is a name mismatch. If the name in Cuba is correct in comparison to Department of Home Affairs or POP etc., do not make any changes. Notify NZIR Disbursements of the correct name via email. They will update the records
• If Child Support have a different IRD to that supplied by NZIR, do not make any changes to Cuba. Request from NZIR via daily exchange or emailing NZIR Disbursements which is the correct IRD Child Support should have recorded for that customer

On the next business day, check on Cuba that payments have disbursed to payees:

• Select 5 CSRNs from the Schedule. Check in Cuba that payment has allocated to the payee
• Email to Specialised Assessment Services national team confirming same

SAS will issue an email confirming payments have commenced disbursing.