Exhibit 14 DSDS-BATCH_FIN_COLLECTIONS.pdf

23DPP00818

Exhibit 14

BATCH_FIN_COLLECTIONS

INTRODUCTION

The procedure BATCH_FIN_COLLECTIONS reads the data received from various deposit sources such as SDU and IRS and loads into temporary tables LOAD_SDU_RECEIPTS and LOAD_IRS_RECEIPTS for further processing. If the counts and amounts in the header/trailer record types do not match with the counts and amount totals of the detail record types, an error message will be written into Batch Status Log (BSTL screen/BSTL table) and the file processing will be terminated.

The second step is to read the records in the temporary tables and create batch records and the receipt records. The system validates the payor and case ID received in the input file and automatically classifies the receipts as unidentified if the payor/case id is not valid;

The receipts are placed on hold if there are any hold instructions as defined on global Hold Instructions Management screen (HIMS screen/HIMS table) or at case or payor level as defined on Manual Hold Instructions screen (HLDI screen/DISH table), or due to special processing based on the Receipt Source (for example IRS, CSLN, FIDM) or NSF status in the past. Additional information on unidentified receipts is recorded on Unidentified Receipts screen (URCT screen/URCT table).

1.1.1 BATCH_FIN_COLLECTIONS.SP_LOAD_SDU

1.1.1.1. Purpose

The stored Procedure SP_LOAD_SDU takes collection data from SDU in a file format, and then loads it into a temporary table in NJKiDS.

1.1.1.2. Database Tables Affected:

1.1.1.2.1. Driver Tables

- N/A

1.1.1.2.2. Output Tables

LOAD_SDU_RECEIPTS

1.1.1.3. Process Description

This procedure takes collection data from SDU, and then loads it into a temporary table LOAD_SDU_RECEIPTS using the UTL_FILE package given by oracle. The steps in this batch process are:

Get the run date and last run date from PARM (Parameters table) and validate that the batch program was not executed for the run date, by ensuring that the run date is different from the last run date in the PARM table. Otherwise, an error message to that effect will be written into Batch Status Log (BSTL screen/BSTL table) and terminate the file load process.

Read the collection file from SDU using the UTL_FILE package given by Oracle

Compare the total # of record count and the sum of receipt amount with the same in the LOAD_SDU_RECEIPTS table. If both are same then terminate the process.

Compare the first 25 records (only the key fields: Date Batch, No Batch, Batch Seq No, Batch Item No, Source Receipt, ID Case, NCP DCN, NCP SSN, DT_RECEIPT, AMT RECEIPT, No Reference, SDU Payment ID and SDU Receipt ID) with the same in the LOAD_SDU_RECEIPTS table. If all are same then terminate the process.

If a ‘Y’ indicator exists in the ind_soil_satisfied column then:

A ‘D-Delete transaction’ will be inserted into VSLST for the case A ‘D-Delete transaction’ will be inserted into VSLST for the member

A record will be inserted into VREMP

Delete the records from the LOAD_SDU_RECEIPTS table

Read the SDU file (flat file on a designated FTP location) and load the data to the LOAD_SDU_RECEIPTS table with IND_PROCESS = ‘N’.

If the counts and amounts in the header/trailer record types do not match with the counts and amount totals of the detail record types, an error message will be written into Batch Status Log (BSTL screen/BSTL table) and the file processing will be terminated.

Update the last run date in the PARM table upon successful completion.

Log any errors or successful completion in the BSTL (Batch Status table) for future reference. Abort the job if any loading error occurs.

1.1.1.4. Flow Chart

1.1.1.5. Frequency

Run Daily

1.1.1.6. Dependencies

1.1.1.6.1. Predecessors:

The program to extract PLOG logged receipts and send to SDU (BATCH_FIN_EXT_PLOG.SP_EXT_PLOG) should be executed successfully before running this program. Please note that a successful run of PLOG extraction may result in zero or more PLOG receipts for that run. Even if there are zero records to be extracted the batch program will run successfully.

1.1.1.6.2. Successors:

SP_LOAD_IRS

1.1.1.7. Restart Procedures

If any error, restart from the beginning

1.1.1.8. Instructions if Job is skipped

Job cannot be skipped.

1.1.1.9. Commit Frequency

One time at the end of job

1.1.1.10. Exception Threshold

N/A

1.1.1.11. Input File(s)

S.No Name of the File File Format Transfer Mechanism File location Estimated size (in MB’s)

1 SDU_INPUT_FILE - FTP - -

1.1.1.11.1. SDU_INPUT_FILE File Layout

Field Name Position Length Type

C-Char

D-Date

N-Number

Format/

Default value

Description

HEADER:

TYPE 1-1 1 C ‘H’

CREATION-DATE 2-9 8 D CCYYMMDD File generated Date

FILLER 10-458 449 C Spaces

DETAIL:

TYPE 1-1 1 C ‘D’

DT_BATCH 2-9 8 D CCYYMMDD The Date on which the Receipt batch is created

NO_BATCH 10-13 4 N 9999 Batch Number of the receipt that was applied in this transaction. This is 3rd part of the Receipt number. Batch numbers for the receipt batches created by the SDU, range from 0001-6999

BATCH_SEQNO 14-16 3 N 999 The first three digits of the Receipt Sequence is the Transaction

Sequence and the next three digits of the Receipt Sequence is the

Posting Sequence.

Each payment transaction (for example, a wage payment from an employer containing payment for three payers is a Transaction) is identified by a Transaction Sequence within the batch.

For each Transaction Sequence, the Posting Sequence will start from

001 and is incremented by one for each additional posting.

The Transaction Sequence enables the user to search and reverse all the receipts in one transaction, in a specific batch, as a single unit.

BATCH_ITEMNO 17-19 3 N 999 This is the posting sequence

CD_SOURCE_RECEIPT 20-21 2 C

Two character code that identifies the source of the receipt

CD_TYPE_REMITTANCE 22-24 3 C

Three character code that identifies the type of remittance for the collection

COUNTY_NUM 25-26 2 C

The county that collected the payment. Applicable only for County collections

ID_CASE 27-37 11 C

IV-D case id associated with the collection

(Blank for Unidentified Receipts)

NCP_DCN 38-45 8 C

NCP_DCN associated with the collection.

(Blank for Unidentified Receipts)

NCP_SSN 46-54 9 C

NCP SSN

DT_RECEIPT 55-62 8 D CCYYMMDD Actual Collection Date. This date is used by Distribution program to distribute the money to the appropriate month.

AMT_RECEIPT 63-73 11 N

Amount of receipt collected

ID_FIPS 74-80 7 N

This is the FIPS (Federal Information Processing Standard) code of the state from which the money is received

NO_REFERENCE 81-95 15 N

This field will record the check number or any other financial instrument (like Money order number or Demand Draft number) identifying number.

SDU_PAYMENT_ID 96-110 15 C

ID used by SDU internally for a posting

SDU_RECEIPT_ID 111-125 15 C

ID used by SDU internally for a transaction

SDU passes spaces for the Highlighted fields

NCP-NAME 126-165 40 C Spaces This will store the NCP name as it is entered by the worker.

NCP – ADD-LINE1 166-190 25 C Spaces This will store the NCP 's address

NCP – ADD-LINE2 191-215 25 C Spaces

NCP – CITY 216-235 20 C Spaces

NCP – STATE 236-237 2 C Spaces

NCP – ZIP 238-246 9 C Spaces

NCP – COUNTRY 247-248 2 C Spaces

BANK-NAME 249-278 30 C Spaces This will capture the name of the bank, if the collection was received through check

BANK-ADD-LINE1 279-303 25 C Spaces The Bank's address

BANK-ADD-LINE2 304-328 25 C Spaces

BANK-CITY 329-348 20 C Spaces

BANK-STATE 349-350 2 C Spaces

BANK-ZIP 351-359 9 C Spaces

BANK-ID 360-369 10 C

The Routing number or any other ID of the bank

BANK-ACCOUNT-NO 370-386 17 C

The account number of the NCP, if provided on the check

MISC-NOTE-1 387-416 30 C

Miscellaneous notes 1 from the SDU

Only for Unidentified Receipts

MISC-NOTE-2

417-446

C

Miscellaneous notes 2 from the SDU

Only for Unidentified Receipts

MEDICAL-SUPPORT-INDICATOR 447-447 1 C

The Medical Support indicator indicates whether the employer offers family medical insurance coverage.

Supported Code Values:

Y = Medical Coverage is available.

N = Medical Coverage is not available.

W = Medical Coverage is not applicable. This code is only used by a state when sending an interstate payment to another state.

If not provided by the remitter, the field will be blank.

EMPLOYMENT-TERMINATION-INDICATOR 448-448 1 C

The employment termination indicator is used to notify the child support enforcement agency that an individual’s employment has terminated.

A “Y” is placed in this field if the employee has been terminated;

otherwise the field is not used and will be blank.

(optional)

EMP-EIN 449-457 9 C

Employer Federal Identification Number.

SOIL-SATISFY-INDICATOR 458-458 1 C

‘Y’ – If debt is satisfied (based on the satisfy indicator in the SOIL intercept file)

Else spaces

TRAILER:

TYPE 1-1 1 C ‘T’

TOTAL-PAY-AMT 2-12 11 N

RECORD-COUNT 13-18 6 N

FILLER 19-458 440 C Spaces

Move ‘CNC’ – County Cash to the Deposit Source (CD_SOURCE_BATCH) field in the LOAD_SDU_RECEIPTS table if the collection is a county collection (If the Remittance Type is County Cash

– ‘CSL’)

Move SDU to the Deposit Source (CD_SOURCE_BATCH) field in the LOAD_SDU_RECEIPTS table if the Receipt Source (CD_RECEIPT_SOURCE) is ‘NR’ -- NSF RECOUPMENT and

CD_TYPE_REMITTANCE will not be equal to ’CSL’ for NR receipts.

Else move ‘SDU’ to the Deposit Source (CD_SOURCE_BATCH) field in the LOAD_SDU_RECEIPTS table.

Valid SDU Receipt Source

RECEIPT SOURCE SOURCE CODE

BOND BN

CP RECOUPMENT CR

DISABILITY INSURANCE DB

EMPLOYER WAGE EW

INTERSTATE IVD-REGULAR F4

INTERSTATE IVD-FEE (COST RECOVERY) FF

JUDGMENT JM

LEVY / FIDM LE

LOTTERY LO

NSF RECOUPMENT NR

RELEASE AMOUNTS (JAIL PAYMENTS) PM

QDRO/EDRO QR

REGULAR PAYMENT FROM NCP RE

SOIL (HOMESTEAD REBATE) SH

SOIL (SAVER REBATE) SS

SOIL (STATE TAX REFUND) ST

UNEMPLOYMENT COMPENSATION (UIB) UC

VOLUNTARY VL

WORKER’S COMPENSATION/ INSURANCE (CSLN) WC

Valid Remittance Types

Remittance Type Code

CASH CSH

CASHIER’S CHECK CTF

CHECK CHK

COUNTY CASH CSL

CREDIT CARD CRD

DIRECT PAY CREDIT DPC

ELECTRONIC FUNDS TRANSFER EFT

FOREIGN CURRENCY FNC

MONEY ORDER MNO

1.1.1.12. Output File(s)

- - None - - - -

1.1.1.12.1. File Layout

1.1.1.13. Contact information

Name of the Agency Technical Contact Functional Contact

- None - -

1.1.1.14. Stored Procedure / Functions matrix

No Stored Procedure Name/ Arguments Nature of the procedure

/ Function

Description Output Tables / Screens Applicability

I A U D

1 SP_LOAD_SDU Loading Loading the SDU file to the

LOAD_SDU_RECEIPTS table

- LOAD_SDU_RECEIP

TS

- Y - Y

2 BATCH_COMMON.SP_GET_RUN_DATE Common Routine Getting Run date - - - - - -

3 BATCH_COMMON.SP_GET_LAST_RUN_DATE Common Routine Getting Last Run date - - - - - -

4 BATCH_COMMON.SP_UPDATE_PARM_DATE Common Routine Updating Last run date - - - - - -

5 SP_BATCH_ERROR_LOG Common Routine Inserting the batch status - - - - - -

1.1.2 BATCH_FIN_COLLECTIONS.SP_LOAD_IRS

1.1.2.1. Purpose

The stored procedure SP_LOAD_IRS takes collection data received from IRS in a file format, and then loads it into the temporary table in NJKiDS.

1.1.2.2. Database Tables Affected:

1.1.2.2.1. Driver Tables

1.1.2.2.2. Output Tables

LOAD_IRS_RECEIPTS

LOAD_NEGATIVE_ADJUSTMENT

IRS_FRAUDULENT_FILING

1.1.2.3. Process Description

A stored procedure (SP_LOAD_IRS) reads the collection file from the IRS using the UTL_FILE package given by oracle and inserts records into the

LOAD_IRS_RECEIPTS table. The steps in this batch process are:

Get the run date and last run date from PARM (Parameters table) and validate that the batch program was not executed for the run date by checking the last run date in the PARM table.

Read the collection file from IRS using the UTL_FILE package given by Oracle.

Compare the total # of record count and the sum of receipt amount with the same in the LOAD_IRS_RECEIPTS and

LOAD_NEGATIVE_ADJUSTMENT tables. If both are same then terminate the process.

Compare the first 25 records (only the key fields: SSN, Source Receipt, Amt Receipt, No Reference, Date Receipt, IND TANF and IND TAX JOINT) with the same in the LOAD_IRS_RECEIPTS and LOAD_NEGATIVE_ADJUSTMENT tables. If all are same then terminate the process.

Delete the records from the LOAD_IRS_RECEIPTS and LOAD_NEGATIVE_ADJUSTMENT tables

If the counts and amounts in the trailer record types do not match with the counts and amount totals of the detail record types, an error message will be written into Batch Status Log (BSTL screen/BSTL table) and the file processing will be terminated.

Insert the collection records into LOAD_IRS_RECEIPTS table with IND_PROCESS = ‘N’ if the Collection Amount is greater than zero. The system will automatically assign a batch number in the range of 7000-7499. (start with 7000 and increment by 1 if the # of records reach 999)

Insert the collection records into LOAD_NEGATIVE_ADJUSTMENT table with IND_PROCESS = ‘N’ if the Adjustment Amount is greater than zero.

Insert the collection records into VIRSF table from LOAD_IRS_RECEIPTS and set IND_NOT_NJ, IND_MULTI_CLAIMS, IND_AMT and IND_MATCH_AHIS to ‘Y’ or ‘N’ based on the REFM code type ‘IRSF’. Also, insert the reference no from LOAD_IRS_RECEIPTS into NO_CHECK column of VIRSF table.

IND_NOT_NJ will be set to ‘Y’ if the NCP’s address is in NJ and address in the payment file is out of NJ.

IND_MULTI_CLAIMS will be set to ‘Y’ if there are Multiple claims for the same address

IND_AMT will be set to ‘Y’ if amount in the payment file matches 9515.00, 9530.00 and 1000.00 exactly.

IND_MATCH_AHIS will be set to ‘Y’ if IRS return address doesn’t match with AHIS addresses.

If the ‘SDU Go Live Date’ in REFM code type ‘BANK’ and code sub type ‘DATE’ is high date then insert the summary records in VIRPS table with CD_STATUS as ‘WP’ .

If the ‘SDU Go Live Date’ in REFM code type ‘BANK’ and code sub type ‘DATE’ is not a high date then insert the summary records in VIRPS table with CD_STATUS as ‘WD’ .

Update the last run date in the PARM table with the run date, upon successful completion.

Log any errors or successful completion in the BSTL (Batch Status Table) for future reference. Abort the job if any error occurs. The correctness of the header and trailer records is checked, along with basic file layout.

1.1.2.4. Flow Chart

1.1.2.5. Frequency

Run Bi-Weekly

1.1.2.6. Dependencies

1.1.2.6.1. Predecessors:

SP_LOAD_SDU

1.1.2.6.2. Successors:

SP_COLLECTION

1.1.2.7. Restart Procedures

If any error, restart from the beginning

1.1.2.8. Instructions if Job is skipped

If skipped, all the backlog files need to be processed first

1.1.2.9. Commit Frequency

One time at end of job

1.1.2.10. Exception Threshold

1.1.2.11. Input File(s)

1 TXOFFIN - FTP - -

1.1.2.11.1. IRS Collection File Layout

Collection and Adjustment Detailed Record Layout

Field Name Position Length Type

C-Char

D-Date

N-Number

Format/

Default value

Description

Submitting State Code 1-2 2 C

This field contains the state abbreviation that was sent to OCSE by the state and stored on the OCSE Case Master File.

Local Code 3-5 3 C

This field contains the local code that was sent to OCSE by the state and stored on the OCSE Case Master File.

SSN 6-14 9 N

This field contains the Social Security Number that was sent to

OCSE by the state and stored on the OCSE Case Master File.

Case ID 15-29 15 C

This field contains the case identification that was sent to OCSE by

Obligor Last Name 30-49 20 C

This field contains the obligor last name that was sent to OCSE by

Obligor First Name 50-64 15 C

This field contains the obligor first name s that was sent to OCSE by

Certified Arrearage Amount 65-75 11 N

This field contains the arrearage amount that is stored on the OCSE

Case Master File at the time of certification. The certified arrearage amount is a signed positive numeric amount with two decimal places assumed.

Collection Amount 76-86 11 N

This field contains the amount of the offset that was sent to OCSE on the FMS Weekly Collection Record. The collection amount is a signed positive numeric amount with two decimal places assumed.

If the Collection Amount Field contains a value greater than zero, the adjustment amount is zeroes.

Adjustment Amount 87-97 11 N

This field contains the amount of the adjustment that was sent to

OCSE on the FMS Weekly Collection Record. The adjustment amount is a signed positive numeric amount with two decimal places

If the adjustment amount contains a value greater than zero, the collection amount is zeroes.

Adjustment Year 98-101 4 N

This field contains the year the offset originated that was sent to

OCSE on the FMS Weekly Collection Record. The adjustment year is in the CCYY format.

If the adjustment amount is greater than zero, this field contains a valid year.

If the collection amount is greater than zero this field is zeroes.

Offset Year 102-105 4 N

This field contains the current processing year when the offset occurred, in the CCYY format.

Return Indicator 106 1 C

This field contains the return indicator that was sent to OCSE on the

FMS Weekly Collection Record. The return indicator identifies whether or not this is a joint return.

Y – joint return

N – not a joint return

Case Type Indicator 107 1 C

This field contains the case type indicator that was sent to OCSE by

Transfer State Code 108-109 2 C

This field contains the transfer state code that was sent to OCSE by the state, and stored on the OCSE Case Master File.

Transfer Local Code 110-112 3 C

This field contains the transfer local code that was sent to OCSE by the state, and stored on the OCSE Case Master File.

Payment Name 113-147 35 C

If the collection amount is greater than zero, this field contains the name on the FMS Payment Record.

If the return indicator is equal to ‘Y’, this field may contain both obligor and/or non-obligor name(s).

If the adjustment amount is greater than zero, this field contains spaces.

Payment Street Address 148-182 35 C/N payment street address that was sent to OCSE on the FMS Weekly

Collection Record.

Payment City and State 183-207 25 C/N payment city and state that was sent to OCSE on the FMS Payment

Record.

Payment Zip Code 208-216 9 N payment Zip Code that was sent to OCSE on the FMS Payment

Record.

Offset Type 217-219 3 C

This field identifies the type of offset or adjustment that applied.

Valid values are:

RET – Federal Retirement

TAX – Tax Refund Offset

VEN – Vendor Payment/Miscellaneous

SAL – Salary

MPY - Manual Payment

Fee Amount 220-224 5 N

This field contains the service fee amount for an adjustment that was sent to OCSE on the FMS Weekly Collection Record. The fee amount is a signed positive numeric amount with two decimal places

If the collection amount contains a value greater than zero, the fee amount is zeroes.

Injured Spouse Indicator 225-225 1 C

This field contains the injured spouse indicator that was sent to

OCSE on the FMS Weekly Collection Record. The injured spouse indicator identifies if an injured spouse claim has been processed.

Y – processed injured spouse claim

N – No information is available, or no injured spouse claim has been filed.

This field is only populated for Non-TANF joint return offset records, i.e., position 106 is a ‘Y’ and position 107 is an ‘N’.

If the record is for a TANF offset, Non-TANF single return offset or any adjustment record, the field contains a space.

Zero Balance Delete Indicator 226-226 1 C

This field contains the zero balance delete indicator that was set by

OCSE to show that an offset reduced the modified arrearage amount for a case to zero. The case is deleted at OCSE but not FMS.

Y – case deleted by the offset

N – all other records, including adjustments and offsets that did not reduce the modified arrearage amount to zeros

TOP Trace Number 227-236 10 C/N

The trace number assigned to a collection by FMS and returned as an identifier with a collection or associated adjustment.

IRS reversal reason code 237-240 4 C/N

This field represents the IRS reversal reason code on TAX reversals or the MMDD of the BFS effective date for offsets.

TRAILER

Submitting State Code 1-2 2 C

This field contains the state abbreviation that was sent to OCSE and stored on the OCSE Case Master File.

Filler 3-5 3 C/N

Space Filled.

Total Control 6-14 9 C/N

This field is a constant field and contains ‘TOTAL’ to identify this record as the Collection and Adjustment Control Record.

Filler 15-34 20 C/N

Space Filled.

Total Adjustments 35-49 15 N

This field contains the total number of adjustments that were processed.

Total Collections 50-64 15 N

This field contains the total number of collections that were processed.

Total Certified Arrearage Amount 65-75 11 N

This field is an accumulation of the certified arrearage amounts that were processed. The total certified arrearage amount is a signed positive numeric amount with two decimal places assumed.

Total Collection Amount 76-86 11 N This field is an accumulation of the collection amounts that were processed. The total collection amount is a signed positive numeric amount with two decimal places assumed.

Total Adjustment Amount 87-97 11 N This field is an accumulation of the adjustment amounts that were processed. The total adjustment amount is a signed positive numeric amount with two decimal places assumed.

Total Net Amount 98-108 11 N

This field contains the total collection amount minus the total adjusted amount. The total net amount is a signed positive numeric amount with two decimal places assumed.

Filler 109-240 132 C/N

Space filled.

Mapping from IRS Input File to LOAD_IRS_RECEIPTS Table

IRS Input File LOAD_IRS_RECEIPTS Table

Batch Process Date DT_BATCH

The system will automatically assign a batch number in the range of 7000-7499. (start with 7000 and increment by 1 if the # of records reach 999)

NO_BATCH

The system will automatically assign a Transaction sequence (batch seq) in the range of 001-999 (System resets the number to 001 if new batch starts)

BATCH_SEQNO

‘001’ BATCH_ITEMNO

‘IRS’ CD_SOURCE_BATCH

’IR’ if the Offset Type in the input file is TAX;

‘AS’ if the Offset Type in the input file is ‘SAL’

‘AV’ if the Offset Type in the input file is ‘VEN’ OR 'MPY'

‘AR’ if the Offset Type in the input file is ‘RET’

CD_SOURCE_RECEIPT

‘EFT’ CD_TYPE_REMITTANCE

SSN from the file SSN

Collection Amount from file AMT_RECEIPT

TOP Trace Number from file NO_REFERENCE

Batch Process Date DT_RECEIPT

Case Type Indicator IND_TANF

Return Indicator IND_TAX_JOINT

Payment Name NAME_TAX_JOINT (mapped only if the Return

Indicator is ‘Y’)

Injured spouse indicator IND_INJ_SPOUSE

Mapping from IRS Input File to LOAD_NEGATIVE_ADJUSTMENT Table

IRS Input File LOAD_NEGATIVE_ADJUSTMENT Table

The system will retrieve the Member ID from VDEMO based on NCP SSN ID_PAYOR

The amount of the adjustment that was sent to OCSE on the FMS Weekly Collection Record. The adjustment amount is a signed positive numeric amount with two decimal places assumed.If the adjustment amount contains a value greater than zero, the collection amount is zeroes

AMT_ADJUST

The Social Security Number that was sent to OCSE by the state and stored on the OCSE Case Master File MEM_SSN

TOP Trace Number from file NO_TOP_TRACE

Indicates the adjust date prefix value as '0101' DT_ADJUST

The case type indicator that was sent to OCSE by the state and stored on the OCSE Case Master File. IND_TANF

The return indicator that was sent to OCSE on the FMS Weekly Collection Record. Y – joint return

N – not a joint return

IND_TAX_JOINT

The name on the FMS Payment Record if the collection amount is greater than zero; both obligor and/or non-obligor name if the return indicator is equal to ‘Y’ or spaces if the adjustment amount is greater than zero

NAME_TAX_JOINT

The date the file was received DT_RECEIVED

The case identification that was sent to OCSE by the state and stored on the OCSE Case Master File ID_CASE

Obligor last name that was sent to OCSE by the state and stored on the OCSE Case Master File NAME_LAST_OBLIGOR

Obligor first name that was sent to OCSE by the state and stored on the OCSE Case Master File NAME_FIRST_OBLIGOR

Type of offset or adjustment that applied. Valid values are RET for Federal Retirement, TAX for Tax Refund Offset, VEN for Vendor Payment/Miscellaneous, MPY for Manual Payment and SAL for Salary

CD_TYPE_OFFSET

Injured spouse indicator:

Y – processed injured spouse claim

N – No information is available, or no injured spouse claim has been filed

IND_INJ_SPOUSE

1.1.2.12. Output File(s)

1.1.2.12.1. File Layout

1.1.2.13. Contact information

Name of the Agency Technical Contact Functional Contact

1.1.2.14. Stored Procedure / Functions matrix

/ Function

Description Output Tables / Screens Applicability

I A U D

1 SP_LOAD_IRS Procedure Loading the IRS collections - LOAD_IRS_RECEIPT

S

- Y - Y

LOAD_NEGATIVE_A

- Y - Y

DJUSTMENT - Y - Y

Y - - -

RFOPM Y - - -

VCMEM Y - - -

VCASE Y - - -

VFEDH Y - - -

HFEDH Y - - -

VMSSN - Y - -

VREFM Y - - -

VIRSF

VAHIS

2 BATCH_COMMON.SP_GET_RUN_DATE Common Routine Getting Run date - - - - - -

3 BATCH_COMMON.SP_GET_LAST_RUN_DATE Common Routine Getting Last Run date - - - - - -

4 BATCH_COMMON.SP_UPDATE_PARM_DATE Common Routine Updating Last run date - - - - - -

5 SP_BATCH_ERROR_LOG Common Routine Inserting the batch status - - - - - -

6 SF_GET_ID_PAYOR Function Getting the id payor - VMSSN

VCMEM

Y

Y

VCASE Y - - -

7 SF_GET_MATCH_AHIS Function To check whether the IRS address and AHIS address matches or not

- VAHIS Y - - -

BATCH_COMMON.

SP_GET_BATCH_DETAILS

Common Routine This procedure is used to get the run date, last run date , commit freq and exception threshold for a given batch process

SF_GET_REFM_DESC_VALUE

Common Function Function to get refm description value

SPKG_CBAT.

SP_INSERT_IRS_COLL_SUMMARY

Common Routine To insert record in IRS summary table

BATCH_COMMON.SP_BSTL_LOG

Common Routine This procedure is used to insert the batch status into VBSTL table.

BATCH_COMMON.

SP_RAISE_APPLICATION_ERROR

Common Routine This procedure is used to raise the application error.

1.1.3 BATCH_FIN_COLLECTIONS.SP_COLLECTIONS

1.1.3.1. Purpose

This procedure reads the data from the LOAD_SDU_RECEIPTS, and LOAD_IRS_RECEIPTS tables (extracted from SDU file or IRS) and loads data to the Collection Batch Posting (CBAT) screen / RECEIPT_BATCH (RBAT) and Batch Receipt Posting (BATR) screen / RECEIPT (RCTH) tables.

1.1.3.2. Database Tables Affected:

1.1.3.2.1. Driver Tables

LOAD_SDU_RECEIPTS

LOAD_IRS_RECEIPTS

VJRTL

SUMMARY_IRS_COLLECTIONS (VIRPS)

1.1.3.2.2. Output Tables

RECEIPT_BATCH (RBAT)

RECEIPT (RCTH)

UNIDENTIFIED_RECEIPTS (URCT)

EVENT_SEQ_MATRIX (ESEM)

GLOBAL_EVENT_LOG (GLEV)

RECEIPT_EMPLOYMENT_INFO (REMP)

1.1.3.3. Process Description

This procedure reads the data from the LOAD_SDU_RECEIPTS, and LOAD_IRS_RECEIPTS tables with IND_UPDATE = ‘N’, loads data to the Collection

Batch Posting (CBAT) screen / RECEIPT_BATCH (RBAT) and Batch Receipt Posting (BATR) screen / RECEIPT (RCTH) tables. The following table provides a field-by-field mapping from the temporary table to the NJKiDS receipts table.

The system uses ID_PAYOR from the input file for the SDU receipts. If the receipt source is payor level, spaces are moved to ID_CASE in RCTH. The

ID_CASE sent by SDU is still maintained in the field ID_CASE_SDU for reference purposes.

This procedure will process the IRS receipts received from the file after DFD/SDU approves the file for processing. It will process once the status for the file process is updated to ‘Waiting for Process’ in the CBAT screen i.e. ‘CD_STATUS = ‘WP’ in the VIRPS table. After processing it updates the CD_STATUS in the VIRPS table as ‘WN’.

For IRS payments, the system uses SSN received in the input to match against all verified SSNs of NCPs in the system to identify the NCP DCN. This DCN is used to populate ID_PAYOR in the RCTH table. The posting level for IRS and SOIL receipts is payor level.

INPUT TABLES

LOAD_SDU_RECEIPTS and

LOAD_IRS_RECEIPTA

OUTPUT TABLES DESCRIPTION

DT_BATCH RCTH.DT_BATCH Batch date

CD_SOURCE_BATCH RCTH.CD_SOURCE_BATCH Deposit Source

NO_BATCH RCTH.NO_BATCH Batch number

BATCH_SEQNO RCTH.SEQ_RECEIPT Receipt sequence

CD_SOURCE_RECEIPT RCTH.CD_SOURCE_RECEIPT Receipt Source

CD_TYPE_REMITTANCE RCTH.CD_TYPE_REMITTANCE Remittance Type

ID_CASE RCTH.ID_CASE (If it is a identified receipt)

URCT.UNIDENTIFIED_ID_CASE (If it is a unidentified receipt)

RCTH.ID_CASE_SDU

Case ID

AMT_RECEIPT RCTH.AMT_RECEIPT Collection amount

AMT_FEE RCTH.AMT_FEE Cost recovery held by the other state for interstate collections

ID_FIPS RCTH.ID_FIPS FIPS ID for interstate collections

NO_REFERENCE RCTH.NO_REFERENCE Payment reference

DT_RECEIPT RCTH.DT_RECEIPT Receipt date

IND_TANF RCTH.IND_TANF Collection type for IRS collections (TANF or Non-TANF)

IND_TAX_JOINT RCTH.IND_TAX_JOINT Joint Tax Indicator (J- Joint or I-Individual)

NAME_TAX_JOINT RCTH.NAME_TAX_JOINT Name on the tax return

ID_PAYMENT_SDU RCTH.ID_PAYMENT_SDU ID used by SDU internally for a posting

ID_RECEIPT_SDU RCTH.ID_RECEIPT_SDU ID used by SDU internally for a transaction

PAYOR_NAME URCT.PATOR_NAME Name of the payor

PAYOR_ADDR1 URCT.PAYOR_ADDR1

PAYOR_ADDR2 URCT.PAYOR_ADDR2

PAYOR_ADDR3 URCT.PAYOR_ADDR3

PAYOR_CITY URCT.PAYOR_CIRY

PAYOR_STATE URCT_PAYOR_STATE

PAYOR_ZIP URCT.PAYOR_ZIP

PAYOR_COUNTRY URCT.PAYOR_COUNTRY

NAME_BANK URCT.NAME_BANK Name of the bank on the check

BANK_ADDR1 URCT.BANK_ADDR1 Address of the bank

BANK_ADDR2 URCT.BANK_ADDR2

BANK_ADDR3 URCT.BANK_ADDR3

BANK_CITY URCT.BANK_CITY

BANK_STATE URCT.BANK_STATE

BANK_ZIP URCT.BANK_ZIP

BANK_ID URCT.BANK_ID Bank routing ID

BANK_ACCOUNT_NO URCT.BANK_ACCT_NUM Bank account number

MISC_NOTE_1 and MISC_NOTE_2 URCT.REMARKS Remarks from SDU on unidentified receipts

.ID_EMPLOYER URCT.ID_EMPLOYER

RCTH.ID_EMPLOYER

EIN of the employer

IND_MEDICAL REMP.IND_MEDICAL MEDICAL SUPPORT INDICATOR

IND_EMPLOYMENT_TERMINATION REMP.IND_EMP_TERMINATION EMPLOYMENT TERMINATION INDICATOR

The program also validates the input data and updates the initial receipt status for each collection:

I - Identified U - Unidentified

If Case ID is not received from the SDU, the receipt will be marked as unidentified with a UDC code of ‘UNID’ – Unidentified’ by SDU. If CASE ID is received in the input file, the Case ID is validated against the CASE table. If it is invalid, the receipt will be marked as unidentified with a UDC code of ‘UNEX’ – Unidentified Exception.

If the receipt source is NCP recoupment, the member relation to the case is validated and if it is not a NCP for the case ID entered, the receipt will be treated as unidentified.

If the receipt source is SOIL (ST, SH and SS) then the batch will mark CD_SOURCE_BATCH in VRCTH as ‘SOL’

Case Level Posting - For a receipt source with Case Level Posting, the Case ID from the collection file will be associated with the receipt.

Payor Level Posting – The system uses ID_PAYOR from the input file for the SDU receipts. For IRS payments, the system uses the SSN received in the input to match against all verified SSNs of NCPs in the system to identify the NCP DCN. Then the payor DCN will be associated with the receipt

Receipts posted by these sources will be classified as Case Level or Payor level receipts based on the receipt source. The following table lists

23DPP00818 20 | P a g e receipt sources and the corresponding posting level.

RECEIPT SOURCE SOURCE CODE POSTING LEVEL

REGULAR PAYMENT FROM NCP RE CASE

EMPLOYER WAGE EW PAYOR

SPECIAL COLLECTIONS IR PAYOR

SOIL (STATE TAX REFUND) ST PAYOR

SOIL (HOMESTEAD REBATE) SH PAYOR

SOIL (SAVER REBATE) SS PAYOR

CENTRAL PAYROLL PY PAYOR

JUDGMENT JM CASE

RELEASE AMOUNT (JAIL PAYMENTS) PM CASE

LOTTERY LO PAYOR

UNEMPLOYMENT COMPENSATION (UIB) UC PAYOR

WORKER’S COMPENSATION/ INSURANCE (CSLN) WC PAYOR

LEVY / FIDM LE PAYOR

DISABILITY INSURANCE DB PAYOR

INTERSTATE IV-D AGENCY-REGULAR F4 CASE

INTERSTATE IV-D AGENCY – FEE (COST RECOVERY) FF CASE

VOLUNTARY VL CASE

DIRECT PAYMENT CREDIT CD CASE

QDRO/EDRO QR PAYOR

BOND BN CASE

ADMINISTRATIVE OFFSET (RETIREMENT) AR PAYOR

ADMINISTRATIVE OFFSET (SALARY) AS PAYOR

ADMINISTRATIVE OFFSET (VENDOR PAYMENT) AV PAYOR

CP RECOUPMENT CR CASE

NSF RECOUPMENT NR CASE

The Batch date, Deposit Source, Batch number will form the batch key and batch level counts and amount totals will be inserted into the Collection Batch Posting (CBAT) screen / RECEIPT_BATCH (RBAT) table. Each batch can contain up to 999 receipts.

Once the batch details are inserted into the Collection Batch Posting (CBAT) screen / RECEIPT_BATCH (RBAT) table for a batch, the process will begin inserting the receipt details into the Batch Receipt Posting (BATR) screen / RECEIPT (RCTH) table for that batch.

When there is a change in the batch number, a record is inserted into the Collection Batch Posting (CBAT) screen / RECEIPT_BATCH (RBAT) table for the new batch and the process of loading receipts into Batch Receipt Posting (BATR) screen / RECEIPT (RCTH) table continues.

SP_RECEIPT_ON_HOLD procedure checks the receipt to determine if all or any part of the amount should be placed on hold. The hold status is identified by:

Checking the Hold Instruction Maintenance (HIMS) Screen / HOLD_INSTRUCTION_MAINTENANCE (HIMS) table for the receipt source. If there is a hold instruction for the receipt source, the receipt will be placed on hold for the number of calendar days identified in the Hold Instruction Maintenance (HIMS) Screen / HOLD_INSTRUCTION_MAINTENANCE (HIMS) table, In case of IRS joint collections, the receipts will be placed on hold based on HIMS parameters. IRS individual collections will not be held based on HIMS instructions.

23DPP00818 21 | P a g e

If this is the first time a payment is received, from the payor/remitter, by check for $1000 or more, it will be placed on a SHTH – 1st

Personal Check Threshold Amount hold automatically for 14 calendar days. These are table-driven parameters from the UCAT table. The program will check the receipts file RCTH and ind_amt_threshold column in VACAS to determine if this is the first time a payment was received for a check of $1000 or more, from this NCP.

23DPP00818 22 | P a g e

If there is an NSF history for the payor, the check will be put on a SHRU –History of NSF Return Checks hold for 14 calendar days.

If the receipt is a wage or unemployment disability payment and

If income withholding status is not active on any case, system posts the income withholding payment at the payor level. If NCP has multiple cases, then posts receipts only to cases where the income withholding status is active.

If income withholding is exempt on all cases, system posts the income withholding payment at the payor level. If NCP has multiple cases posts receipts only to cases where the income withholding is not exempt.

If a QDRO/EDRO indicator exists on all cases, or if no QDRO/EDRO indicator exists, system posts the income withholding payment at the payor level.

If the payor of the case has a QDRO/EDRO Indicator on any one of his open cases, the wage receipt will be placed on a SNQR

– System Hold Potential QDRO/EDRO hold. The QDRO/EDRO administrator will research these payments to verify if these are really wage payments or QDRO/EDRO payments and change the receipt source if required and then release the receipt.

If the receipt source is QR – QDRO/EDRO, place the receipt on a SNQR – System Hold Potential QDRO/EDRO hold; the QDRO /EDRO administrator will research these payments if one of the following conditions is true

NCP has only one open case and a QDRO/EDRO indicator does not exist.

If NCP has more than one open case and a QDRO/EDRO indicator does not exist on any case.

If Collections identified as Non-TANF Joint Federal Tax (IRS) collections are held for 180 days prior to distribution to the arrears for the Non-TANF case.

If the receipt is placed on ‘SNJT – SYSTEM HOLD JOINT FEDERAL TAX or MNFR (FINANCIAL REVIEW BY DFD (ARREARS, OVER-PAYMENT)) hold, check if the NCP has a valid address using batch_gen_notice_util.sp_get_recipient_address.

If valid address exists for the NCP, then insert a CS188 notice record for the case linked to the last FOP referral for the NCP.

Create a note to file for all the other open IVD cases associated to the NCP.

If no valid address exists, create a note to file for all open IVD cases associated to the NCP.

If Collections identified as Non-TANF Joint Federal Tax (IRS) and has a ‘Y’ in the Injured Spouse Indicator, the collection will not be placed on the 180 day Non TANF hold. An entry will be written to CPRO for all of the NCP’s open cases – ‘TXISI – ‘Injured Spouse indicator is Y. Collection Released’.

If payment is special collection and if at least one of the indicators in IND_NOT_NJ, IND_MULTI_CLAIMS, IND_AMT and IND_MATCH_AHIS are set to Y then the payment associated to the NO_CHECK is placed on MNFR hold in the VRCTH table with the number of days as per VUCAT table (UDC_CODE = MNFR) .

The system will then check the Manual Hold Instructions (HLDI) screen/ DISTRIBUTION_HOLD (DISH) table to determine if a distribution hold exists for the Payor or Case ID.

If the Manual Hold Instructions (HLDI) screen/ DISTRIBUTION_HOLD (DISH) table has a hold date greater than the number of calendar days to hold from the Hold Instruction Maintenance (HIMS) Screen / HOLD_INSTRUCTION_MAINTENANCE (HIMS) table, the DISH (Distribution Hold) hold date will be inserted into Batch Receipt Posting (BATR) screen / RECEIPT (RCTH) table for the collection as the release date.

If there is no distribution hold data for the Payor or Case ID in the Manual Hold Instructions (HLDI) screen/

DISTRIBUTION_HOLD (DISH) table or the hold date from the Manual Hold Instructions (HLDI) screen/ DISTRIBUTION_HOLD

(DISH) table is less than the number of calendar days to hold from the Hold Instruction Maintenance (HIMS) Screen / HOLD_INSTR UCTION_MAINTENANCE (HIMS) table, the HIMS (Hold Instruction Maintenance) hold date will be inserted into Batch Receipt

Posting (BATR) screen / RECEIPT (RCTH) table for the collection as the release date. The Receipt Status will be updated to H- Hold status.

If no hold information exists for the Receipt Source or Payor/Case ID

If the receipt is identified, the collection record is inserted into RECEIPT table with the status as ‘I’ (Identified).

23DPP00818 23 | P a g e

If the receipt is unidentified, the collection record is inserted into both RECEIPT and UNIDENTIFIED_RECEIPTS table with the status as ‘U’ (Unidentified).

The batch program will trigger an informational alert for RP035 (Probation Enforcement Worker) role when the SDU receipt file receives ‘Y’ for

EMPLOYMENT-TERMINATION-INDICATOR field for any identified receipt

According to the source of the receipts, the IND_UPDATE field will be updated with ‘Y’ in the LOAD_SDU_RECEIPTS and LOAD_IRS_RECEIPTS tables

1.1.3.4 Thread Process:

Currently, the COLLECTIONS batch program is running in serial mode (executed as a single thread) as any other regular jobs. As it stands, the execution time taken is 20 minutes approximately in Production. Once we went to statewide, the volume of the records to be processed have substantially increased and subsequently, the completion of this job was taking much longer.

In order to improve the performance of this job execution, the COLLECTIONS batch program will be modified to be executed in multiple threads in parallel thereby reducing the total execution time considerably.

A new Column (record_rownum) is introduced in the LOAD_SDU_RECEIPTS and LOAD_IRS_RECEIPTS tables that will hold unique number by sorting the table with respect to dt_batch,no_batch,batch_seqno,batch_itemno.

The population of this new column will be done in PRE_COLLECTIONS JOB. In other words, a new job ‘BATCH_FIN_COLLECTIOS.

SP_PRE_COLLECTIONS’ will be introduced that will populate the data into the new coulum of the tables. This job will be executed before the current COLLECTIONS job is executed.

The number of threads for a given job is set in the NO_THREAD column of the VPARM table. For COLLECTIONS job (NJB0540), the number of threads will be set to 8. Since this value is stored in the table, it can be modified accordingly.

Another table JOB_MULTI_THREADS_RESTART_LOG (VJRTL) is used to store the thread details for executing a given job in parallel. The data will be populated by the PRE-COLLECTIONS job. This table will have one record per thread. It means that if the number of threads is set to 8 for a given job, then this table will have 8 records for that given job and given date.

The Number of records to be processed by each thread is derived from the following condition:

No. of records for a thread= TRUNC (Total no of records in (SDU and IRS) tables / Thread value in VPARM).

Note: the last thread will process the remaining records, if any.

The thread details table will have the starting number of a record and ending number of the record that has to be processed for each thread.

Here is a sample illustration of what will be stored in the JOB_MULTI_THREADS_RESTART_LOG table: E.g.: If 8000 records have to be processed by COLLECTIONS and thread count = 8, then eight records will be inserted with the following values.

SERIAL_

NO

NO_REC_

START

NO_REC_END

IND_THREAD

_PROCESS

RESTART_

KEY

NO_REC_RESTART

ID_

JOB

DT_

RUN

1 1 1000 N 1 6/10/2011

2 1001 2000 N 1001 6/10/2011

3 2001 3000 N 2001 6/10/2011

4 3001 4000 N 3001 6/10/2011

5 4001 5000 N 4001 6/10/2011

6 5001 6000 N 5001 6/10/2011

7 6001 7000 N 6001 6/10/2011

8 7001 8000 N 7001 6/10/2011

23DPP00818 24 | P a g e

BATCH_FIN_COLLECTIONS.SP_COLLECTIONS changes (Part I):

1. This main procedure will start taking the thread number as an input argument to the procedure. The cursor query will be modified to have record_rownum between no_rec_restart and no_rec_end from VJRTL.

2. A process indicator column is available in the (SDU and IRS) tables that will be updated to ‘Y’ after each record is processed in the cursor by its corresponding thread.

3. Successful completion of a thread will update the column IND_THREAD_PROCESS=‘Y’ in the VJRTL table for that processed thread of the job.

4. Abend on a thread will update IND_THREAD_PROCESS=’A’. If the abend occurred after a commit frequency already reached then the

NO_REC_RESTART will have the last commit frequency number.

5. On successful completion of each thread, the Process indicator in the intermediate table is checked with any unprocessed records. If all the records are processed then the VPARM date of COLLECTIONS batches are updated as job successfully completed.

6. The process to execute the COLLECTIONS Job in parallel is to execute the same job with different thread number in separate oracle sessions simultaneously. (Below is an example for understanding purposes). The integer number that is passed as an argument is the thread number that needs to be executed

a. Open Oracle session # 1; issue the command: EXEC BATCH_FIN_COLLECTIONS.SP_COLLECTIONS(1);

b. Open Oracle session # 2; issue the command: EXEC BATCH_FIN_COLLECTIONS.SP_COLLECTIONS (2);

c. Open Oracle session # 3; issue the command: EXEC BATCH_FIN_COLLECTIONS.SP_COLLECTIONS (3);

7. Autosys JIL script (Job definitions) will need to be modified to handle the parallel batch run.

8. So, this modification will introduce multiple jobs and execute them in parallel to gain execution time. The total jobs for COLLECTIONS processing will be as below.

Execute PRE-CASE CLOSURE jobs:

BATCH_FIN_COLLECTIONS.SP_PRE_COLLECTIONS

Execute CASE CLOSURE Job:

BATCH_FIN_COLLECTIONS.SP_COLLECTIONS (run in multiple threads parallel)

Execute POST-CASE CLOSURE jobs:

BATCH_FIN_COLLECTIONS.SP_POST_COLLECTIONS

9. The update and select statement of JOB_MULTI_THREADS_RESTART_LOG tables will be implemented in BATCH_COMMON. These procedures are implemented in BATCH_COMMON since the thread table can be accessed by any jobs.

10. After successful execution of the case, the closure job needs to run post collections job (SP_POST_COLLECTIONS). This job will verify the successful execution of all the threads of COLLECTIONS job. If any of the thread is not executed and unprocessed records remain then this post collections job will abend.

1.1.3.5. Flow Chart

23DPP00818 25 | P a g e

23DPP00818 26 | P a g e

1.1.3.6. Frequency

1.1.3.7. Dependencies

1.1.3.7.1. Predecessors:

SP_LOAD_IRS

1.1.3.7.2. Successors:

SP_BATCH_ACCRUAL_DAILY

1.1.3.8. Restart Procedures

If any error, restart from the beginning.

1.1.3.9. Instructions if Job is skipped

Job cannot be skipped. It needs to be run before executing other processes.

1.1.3.10. Commit Frequency

At the end of Job

1.1.3.11. Exception Threshold

No exceptions

1.1.3.12. Input File(s)

1.1.3.13. Output File(s)

1.1.3.14. Contact information

23DPP00818 27 | P a g e

1.1.3.15. Stored Procedure / Functions matrix

23DPP00818 28 | P a g e

/ Function

Description Output Tables / Screens Applicability

I A U D

1 SP_COLLECTIONS Process Process the extract of collection file and post LOAD_SDU_RECEIP Y - Y -receipts in NJKIDS TS

LOAD_IRS_RECEIP

TS Y - Y -

VRBAT

Y

VRCTH

VURCT -

Y

VBATE - - - -

VHIMS

VJRTL

Y

- - Y

VCASE

VCMEM

Y

- Y -

VMSSN

Y

VIFMS

Y

VFEDH

VDEMO

2 SP_RECEIPT_ON_HOLD Common Routine This procedure checks Hold Instruction

Maintenance (HIMS) Screen / HOLD_INST

RUCTION_MAINTENANCE (HIMS) and

Manual Hold Instructions (HLDI) screen / D

ISTRIBUTION_HOLD (DISH) tables for existing holds and calculates amounts to be held and identified

3 BATCH_COMMON.SP_GET_BATCH_DETAILS Common Routine This procedure is used to get the run date, last run date, commit freq and exception threshold for a given batch process

4 BATCH_COMMON.SP_PREDECESSOR_JOB_CHECK Common Routine This procedure is used to check whether the predecessor job has been executed. This check is for post jobs which has a dependency on predecessor job.

5 BATCH_COMMON.SP_GET_THREAD_DETAILS Common Routine This procedure is used to get the starting and ending records numbers which needs to be processed by the given thread number.

6 SP_GENERATE_SEQ Procedure This procedure is used to generate a sequence value to be inserted into VGLEV table.

7 SPKG_PROCESS_RECEIPT.

SP_INSERT_MED_EMP_IND

Procedure This procedure is called to insert new receipt into vremp after the insert into vrcth. If it is new sdu receipt with medical indicator in

('Y','N','W') or employment indicator is 'Y'or if the receipt is reposted (original receipt should available in vremp).

8 SPKG_MATRIX.SP_ENTITY_MATRIX Procedure This procedure is used to process the

Parameters and create a VESEM Entry for the Case with the Key Elements in Sequence that is displayed on the ELOG Screen.

9 SP_CR_RECEIPT_PROCESS Procedure This procedure is used to process 'CR' receipt type records

AC_MSG_CODE

AS_ERROR_DESC

VPOFL Y - - -

AD_DT_BATCH VDHLD Y Y - -

AN_NO_BATCH

AS_SEQ_RECEIPT VRCTH Y Y Y -

AS_CD_SOURCE_BATCH

AS_ID_CASE VUCAT Y - - -

AS_ID_PAYOR

AN_AMT_IDENTIFIED

AN_SEQ_EVENT_GLOBAL_BEG

AD_DT_RECEIPT

AD_DT_RUN

10 BATCH_COMMON.SP_BATE_LOG Common Routine This procedure is used to insert the unprocessed record error codes into VBATE table.

11 BATCH_GEN_NOTICE_UTIL.

SP_GET_RECIPIENT_ADDRESS

Procedure This procedure is used to get the recipients' addresses

12 BATCH_COMMON.SP_INSERT_ACTIVITY Common Routine This procedure is used to insert the activity in VDMJR and VDMNR tables for the case and member combination.

13 SPKG_NOTE.SP_CREATE_NOTE Procedure This procedure is used to create a Customer

Service, Financial, Note to File and

Confidential notes.

23DPP00818 29 | P a g e

14 SP_INSERT_VSLST_VISTX_VREMP

AS_MEM_SSN

AS_CD_SOURCE_RECEIPT

AS_ID_CASE_SDU

AD_DT_BATCH

AS_NO_BATCH

AS_BATCH_SEQNO

AS_BATCH_ITEMNO

Procedure This procedure is used to insert into VSLST, VISTX and VREMP if

IND_SOIL_SATISFIED = 'Y'

AC_MSG_CODE

AS_ERROR_DESC

VREFM

VSLST

VISTX

VREMP

Y

Y

Y

Y

Y

15 BATCH_COMMON.SP_BSTL_LOG Common Routine This procedure is used to insert the batch status into VBSTL table

16 BATCH_COMMON.SP_UPDATE_PARM_DATE Common Routine Updating Last run date - - - - - -

17 BATCH_COMMON.

SP_RAISE_APPLICATION_ERROR

Common Routine This procedure is used to raise the application error

1.1.4 BATCH_FIN_COLLECTIONS.SP_POST_COLLECTIONS

1.1.4.1. Purpose

The stored Procedure SP_POST_COLLECTIONS generates alerts for the receipts processed by SP_COLLECTIONS.

1.1.4.2. Database Tables Affected:

1.1.4.2.1. Driver Tables

VREMP

VGLEV

VCMEM

VCASE

1.1.4.2.2. Output Tables

1.1.4.3. Process Description

This procedure generates ETERM informational alert for the receipt that is identified and ind_emp_termination flag is 'Y' and the case is Open. For closed cases write error message to vbate. The procedure generates Informational alert JUGRH for judgement receipts placed on SNJM hold.

Get the run date and last run date from PARM (Parameters table) and validate that the batch program was not executed for the run date, by ensuring that the run date is different from the last run date in the PARM table.

Verify the predecessor job SP_COLLECTIONS is executed for the batch run date.

For the Identified or Held receipts processed by the Collections job, Identify the receipts with IND_EMP_TERMINATION = 'Y' (Yes) in Receipt_Emp_Detail Table (VREMP).

FROM VREMP, VRCTH, VGLEV

WHERE VREMP.IND_EMP_TERMINATION = 'Y' (Yes)

AND VREMP.DT_BATCH = VRCTH.DT_BATCH

AND VREMP.NO_BATCH = VRCTH.NO_BATCH

AND VREMP.CD_SOURCE_BATCH = VRCTH.CD_SOURCE_BATCH

AND VREMP.SEQ_RECEIPT = VRCTH.SEQ_RECEIPT

AND VRCTH.DT_END_VALIDITY = '31-DEC-9999' (HIGH DATE)

AND VREMP.SEQ_EVENT_GLOBAL = VGLEV.SEQ_EVENT_GLOBAL

AND VRCTH.CD_STATUS_RECEIPT IN (I 'Identified', H 'Held')

AND VGLEV.ID_PROCESS = 'NJB0540' (SP_COLLECTIONS JOB)

AND VGLEV.DT_EFF_EVENT = Batch Run Date

For the receipts based on id_payor field identify the cases in which the member is an NCP or Putative Father.

23DPP00818 30 | P a g e

FROM VCMEM, VCASE, VREMP

23DPP00818 31 | P a g e

WHERE VCMEM.ID_MEMBER = VREMP.ID_PAYOR

AND VCMEM.CD_RELATION_CASE IN (A 'NCP', P 'Putative Father')

AND VCMEM.CD_STATUS_CASE_MBR = 'A' (Active)

AND VCMEM.ID_CASE = VCASE.ID_CASE

For the Open Cases identified by the conditions mentioned above Generate New Sequence Transaction Event and Insert an Informational Alert ETERM with the Special Note Description 'Employer (EIN) reported the payor's employment terminated. Validate and update EHIS if employment termination is confirmed.'

If the Case is a Closed Case then write a Bate error in VBATE.

Identify the Case and payor id to send Informational Alert JUGRH for the receipts processed for the batch run date by the Collection Batch which is Judgement receipts 'JM' being in SNJM Hold.

FROM VRCTH, VGLEV G

WHERE VRCTH.DT_END_VALIDITY = '31-DEC-9999' High Date

AND VRCTH.CD_STATUS_RECEIPT = 'H' (Hold)

AND VRCTH.DT_BEG_VALIDITY = Batch Run Date

AND VRCTH.CD_SOURCE_RECEIPT = 'JM'

AND VRCTH.CD_REASON_STATUS = 'SNJM'

AND VGLEV.SEQ_EVENT_GLOBAL = VRCTH.SEQ_EVENT_GLOBAL_BEG

AND VGLEV.ID_PROCESS = 'NJB0540' (SP_COLLECTIONS JOB)

AND VGLEV.DT_EFF_EVENT = Batch Run Date

Update the last run date in the PARM table upon successful completion.

Log any Errors or Successful completion in the BSTL (Batch Status table) for future reference.