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.

Update incorrect CSIDs on the schedule before formatting:

• Open schedule and save a copy to desktop
• Perform an Autosum on Payment Amount $NZ (Column D). Confirm the amount matches the figure in cell B4. Delete the Autosum cell
• Print the top section of the schedule – this creates a record of the NZ$ amount, A$ amount and exchange rate
• Cut rows 1-8 and paste into a blank spreadsheet, leaving just the customer data with column headers
• Delete any $ symbols or commas that appear in Column D with NZ$ amounts
• Sort Payer CSID A-Z
• Remove the letter R if it appears in any CSIDs:
• Highlight Payer CSID column
• Perform a search for the letter R (using Ctrl H)
• Replace with: blank
• Replace All
• Add the letter R back into the column title, Save
• Check the Schedule to ensure all customers have a CSID and IRD. If they are missing locate them and add to the Schedule (This is unlikely to be required but use Cuba to search by name or check previous schedules received from NZ)
• Some CSIDs will have different fonts etc. Format them to all look the same
• Insert a new column after column A (Payer Last Name) and column C (Payer First Name) so the new columns are “B” and “D”
• Sort CSID A-Z
• Open NZ schedule – SYSOUT Prevention from G:\Int\DHS_International-_child_Support\Central_Authority_Team\Disbursements\NZ Schedules and Errors\NZ Error Report (Sysout)
• In cell B2 create a VLOOKUP where the lookup value is the CSID. Table Array is all rows excluding headings in B, C and D of the NZ Schedule – SYSOUT Prevention spreadsheet. Column index is 3 and 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 excluding headings in B, C and D of the NZ Schedule – SYSOUT Prevention spreadsheet. Column index is 2 and Range lookup is false. Drag this formula to the end of the schedule
• Select Global>Copy> Save values
• It 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 (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 cell next to the first #N/A in column B
• Repeat for Column D – then select Global> Copy>Paste values. When both columns, B and D are all upper case and show the names required to be used in the PRN. Delete Columns A and C
• Make all names upper case (create a new column and use the formula =Upper)
• Remove any spaces from before names and remove 'restricted'
• 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
• Copy rows 1 – 8 back into the top of the schedule (click in cell A1 - insert - copied fields - yes)
• Save this into the appropriate file (Z drive/International/Old files/Payments In and Out/$$ received from NZ schedule in/SCHEDULES/2019-2020 Fin Year with sequential month/year etc)

Note: if NZIR send a short form CSID which cannot be matched, escalate to SAS National team. They will liaise with NZIR to confirm the customer record.

• 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

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 (to 5 decimal places) with the cell that contains the NZ$ amount (row 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 on the bottom right corner of Cell F10 to copy the formula, this will produce the AU$ value for all customers
• Remove the formula by highlighting Column F:
• Copy>Paste Special>Values>Ok
• Save

Format spreadsheet:

• Highlight column F, and format to Number with 2 decimal places
• Right justify Colum F
• Left justify Columns A, B, C, D and E
• Delete any Autosum amounts at the bottom of the Schedule
• Save

Save file as .prn

• Save the file to the desktop as a .PRN file with the name NZ_payer_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 NZ in Schedule that checks the formatting is correct.

• Open the .prn data file from the Desktop as a notepad (hold mouse over file icon, right click, select open, choose option to open as a notepad)
• Open the Ruler for NZ in Schedule located at G:\Int\DHS_International_Child_Support\Central_Authority_Team\Disbursements\NZ schedules/ruler for NZ in schedule
• 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. The first customer should be the first line

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 and
• check the column widths to ensure they are correct

Save the .prn file to: X/Network/nitdscacroc/D/Inetpub/ftproot/prod/NZ Deductions.

This file will pick up from this location automatically when the NZ Load Deduction Batch job is set to run on the 15th of the month. If the file is not there, the file name is incorrect or in a different location, no data will load.

Also save a copy to: G:\Int\DHS_International_Child_Support\Central_Authority_Team\Disbursements\NZ Schedules and Errors.

Save the original unchanged spreadsheet received via email from NZIR in Z/International Disbursement/Old files/Payments In and Out/$$ Received from NZ, schedule in/SCHEDULES/2019-2020 Fin Year/ (name sequentially).

Once formatted, IT will pick up the file. IT will action the file on the 15th of the month or the first working day if this falls on a weekend, so payments can be automatically applied to Cuba and reports generated for payments requiring manual processing.

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.

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

Format the report into a spreadsheet.

• Highlight from the word Error down to the last figure, copy into a new Excel spreadsheet
• Highlight column A
• Convert text to columns as follows:
• Data>Text to Columns>Delimited>Next
• Un-tick Tab & Tick Space>Next/General/Finish
• Sort the spreadsheet by column B to group the errors together:
• Global>Data>Sort>Column B
• Tick box 'My data has headers'
• Delete rows from the top row down to where column B content changes from ERROR to NZ
• Delete columns A, B, C, D & E

Reformat the spreadsheet to ensure the reference numbers, names and amounts are in the correct columns.

• Search for the '<' character and replace it with nothing:
• Highlight Columns C and D
• Control H>Replace’>’with nothing>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 amount for NZIR. This appears on the main screen for the organisation NZIR
• The Autosum amount derived at step 3 above 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 $1 out)
• Open the last NZ schedule, 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)
• Copy columns C, D & E (CSID, Amount, IRD) from number down and paste into then errors spreadsheet
• Delete column I (Payer Amount)
• Ensure Column H has IRDs and Column I has CSIDs
• Delete IRD and CSID header cells
• Search for the correct CSID to match the IRD using the VLOOKUP function:
• Type the formula in cell E1 =VLOOKUP(A1,H1:I1 & drag to the bottom,2,FALSE)
• Format the cell E1 as a number:
• Format>Format Cells>Number>Decimal places 0
• Copy E1 down to the bottom so CSIDs populate
• Randomly check five entries with data on Cuba to ensure that Name and CSIDs match
• Remove the formula by highlighting Column E
• Copy>Paste Special>Values>Ok
• Remove the letter 'R' if it appears in any CSIDs:
• Highlight column E
• Control H>Replace R with nothing>Replace All
• Delete Columns H and I
• Some CSIDs will be short. Find these in Cuba and copy correct full CSID into spreadsheet
• Some CSIDs are missing and have N/A instead. Find the CSID on the last NZ Schedule previously opened, by copying the IRD and using Ctrl F. Copy the CSID across to the spreadsheet
• 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 organisation NZIR in Cuba using CSID 109036845959
• Find the Reconciliation Reports List by selecting the following from the Reconciliation menu:
• Reconciliation Reports List>tick Saved>Find
• There will be 6 or 7 Employer Reconciliation files with a status of SAVE. Add up the Actual Amount listed for each file and ensure they total the Reconciliation List amount on the NZIR Cuba front screen
• Double click the first file to open
• Bring up a list of customers whose records require amendments:
• Search using Ctrl F, entering A in the Family Name box
• Select Amendment Req in the Reason dropdown menu
• Find
• Click to highlight the first line that has an amount in it:
• Click the Reason box and arrow down, Cuba will automatically select the correct option, either DEDN WAS MORE or DEDN WAS LESS and then save
• Work through the file from A - Z for all customers that have an amount
• Delete the lines that have zero in both columns. Repeat on the next line with zero until the file has been worked through
• Repeat the process for amending the reason code and deleting the zero amount lines on all Reconciliation files
• Ensure the Employer Reconciliation files add up to the original amount before this process was started

• 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 search using Ctrl F in Cuba. Check all Reconciliation Reports in Cuba when trying to find the customer
• 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 6 or 7 Reconciliation files. 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.

Find the reason for the error that stopped the transaction being run automatically through Cuba and correct this:

• 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, then check the IRD on Cuba matches the IRD in the spreadsheet. Ensure 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 place. 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 so they can update their records
• If Child Support have a different IRD to that supplied by NZIR, do not make any changes to Cuba, but request from NZIR via daily exchange or emailing NZIR Disbursements which is the correct IRD Child Support should have recorded for that customer

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 6 or 7 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 6 or 7 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 their 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