Exhibit 20 DSDS-BATCH_CUSW.pdf

23DPP00838

Exhibit 20

BATCH_CUSW

1.1 Introduction

The process BATCH_CUSW is a daily job that is used to refresh the tables in CUSW schema in NJKiDS.

1.1.1 BATCH_CUSW

1.1.1.1 Purpose

This procedure is used to refresh all the tables in customer service schema with the data from the underlying base tables after the nightly batch is completed

1.1.2 Database Tables Affected:

1.1.2.1 Driver Tables

NJKiDS transaction tables

1. VCASE

2. VCMEM

3. VDEMO

4. VAMJR

5. VAMNR

6. VDMNR

7. VDSBL

8. VFIPS

9. VIVMG

10. VMHIS

11. VMSSN

12. VOBLE

13. VOTHP

14. VRCTH

15. VREFM

16. VSORD

17. VSWKS

18. VNSFL

19. VBWNT

20. VDINS

21. VMINS

22. VICAS

23. VLSUP

24. VMDET

25. VSTAT

26. VUSRT

27. VCTHB

28. VGTST

29. VDSBH

30. VDSBC

31. VAHIS

32. VEHIS

33. VLSTT

34. VCBOR

35. VPLIC

36. VFEDH

37. HFEDH

38. VIWEM

39. VNRRQ

40. VDMJR

41. VDHLD

42. VELRP

43. VPOFL

44. VEFTR

45. VJDCA

1.1.2.2 Output Tables

Tables in Customer service schema

1. CCASE

2. CCMEM

3. CDEMO

4. CAMJR

5. CAMNR

6. CDMNR

7. CDSBL

8. CFIPS

9. CIVMG

10. CMHIS

11. CMSSN

12. COBLE

13. COTHP

14. CRCTH

15. CREFM

16. CSORD

17. CSWKS

18. CNSFL

19. CBWNT

20. CDINS

21. CMINS

22. CICAS

23. CMDET

24. CSTAT

25. CUSRT

26. CCTHB

27. CGTST

28. CDSBH

29. CDSBC

30. CAHIS

31. CEHIS

32. CREFL

33. CSPAY

34. CSBAL

35. CENSD

36. CENFN

1.1.3 Process Description

This procedure is used to provide data for the customer service portal. It refreshes all the tables in the customer service schema with the data from the underlying base tables, after the nightly batch is completed.

Get the run date and last run date from VPARM 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 VPARM table. Otherwise, an error message to that effect will be written into VBSTL table and terminate the process.

Refresh the Customer service tables based on below

Tables refreshed in SP_REFRESH_STEP1 procedure

CAMJR:

The table is used to store all valid records from VAMJR. All the values in the table are truncated before filling the data for the table.

CFIPS:

The table is used to store all valid records from VFIPS. All the values in the table are truncated before filling the data for the table.

CREFM:

The table is used to store all valid records from VREFM. All the values in the table are truncated before filling the data for the table.

CAMNR:

The table is used to store all valid records in VAMNR. All the values in the table are truncated before filling the data for the table.

CBWNT:

The table is used to store all valid records in VBWNT. All the values in the table are truncated before filling the data for the table.

CDINS:

The table is used to store all valid records in VDINS. All the values in the table are truncated before filling the data for the table.

CSTAT:

The table is used to store all valid records in VSTAT. All the values in the table are truncated before filling the data for the table.

CUSRT:

The table is used to store all valid high profile case records in VUSRT. All the values in the table are truncated before filling the data for the table.

CSWKS:

The table is used to store all records with a valid id_workers in VSWKS. All the values in the table are truncated before filling the data for the table.

CCASE:

The table is used to store all records in VCASE. All the values in the table are truncated before filling the data for the table

CENSD:

The table is used to store record case wise with the following fields are derived from many tables.

Field Description

NCP_LOCATE_STATUS Locate status of the NCP

IND_FAMILY_VIOLENCE Indicates the case is family violence when one of the member is family violence

IND_HIGH_PROFILE Indicates the case is a high profile case

DT_REPORTED_CRPT Date when NCP was submitted for credit reporting

DT_LICENSE_STATUS Recent License status date with Suspended or Reinstated

CD_LICENSE_STATUS Current status with Suspended or Reinstated

DT_ACTIVE_BWNT Date of active bench warrant for the NCP

DT_IMIW_SENT Most recent date when the NCP submitted for income withholding

DT_FOP_SUBMITTED Date when NCP was submitted for IRS.

DT_PASSPORT_DENIAL Date when the NCP was sent for passport denial.

DT_CS150_GENERATE Most recent CS150 notice sent for the case

DT_CS154_GENERATE Most recent CS154 notice sent for the case

DT_SOIL_SUBMITTED Date when NCP was submitted for SOIL

AMT_DISB_HOLD Total amount of CP disbursement hold

AMT_DIST_HOLD Total amount of distribution hold

AMT_NSF_RECOVERED NSF amount recovered for the case

AMT_NSF_BAL_DUE NSF balance due for the case

AMT_RECOUP_RECOVERED CP recoupment amount recovered

AMT_RECOUP_BAL_DUE CP recoupment balance due

AMT_DEFRA Previous month defra amount for the case.

CD_STATUS_EFT Current status for EFTR for CP

NO_ACCOUNT_BANK_CP Last 4 digits of CP's bank account number.

All the values in the table are truncated before filling the data for the table.

Tables refreshed in SP_REFRESH_STEP2 procedure

CMINS:

The table is used to store all valid records in VMINS. All the values in the table are truncated before filling the data for the table.

CICAS:

The table is used to store all valid records in VICAS. All the values in the table are truncated before filling the data for the table.

CSBAL:

The table is used to store only the latest mth_support record from VLSUP. All the values in the table are truncated before filling the data for the

CMDET:

The table is used to store all records in VMDET. All the values in the table are truncated before filling the data for the table.

CMSSN:

The table is used to store all valid records (Primary – Confirmed Good, Pending, ITIN – Confirmed Good, Pending) in VMSSN. All the values in the table are truncated before filling the data for the table.

CCMEM:

The table is used to store all the records from VCMEM. All the values in the table are truncated before filling the data for the table.

CCTHB:

The table is used to store all transactions from VCTHB with transactions from last 6 months with the following reason code

ERALL, EREXO, ERRES, ERMEO, ERFSM, ERFSO, ERFSS, SRORD, SRPAT

With the case is still open in VICAS. All the values in the table are truncated before filling the data for the table.

CDSBL:

The table is used to store all the records from VDSBL. If previous batch run date is low date then all the values in the table are truncated before filling the data for the table or only the data from previous batch run to current run date are deleted and inserted.

Tables refreshed in SP_REFRESH_STEP3 procedure

CGTST:

The table used to store all valid records from VGTST. All the values in the table are truncated before filling the data for the table.

CMHIS:

The table is used to store all the records in VMHIS. All the values in the table are truncated before filling the data for the table.

CDMNR:

The table is used to store only the records for the major ‘PATI’, ‘ESTP’ and ‘TERM’ from VDMNR. All the values in the table are truncated before filling the data for the table.

Major Activity Minor Activity Reason code Comment Requirement

PATI SHJUD, SEHSU AH Yes/No – Failed to respond to Paternity or Child-support proceeding?

ESTP JUGSI YU Yes/No – Failed to respond to Paternity or Child-support proceeding?

TERM SCSTP VE Status complete Yes/No – Contest / documentation has been received?

COTHP:

The table is used to store all valid records in VOTHP. All the values in the table are truncated before filling the data for the table.

CNSFL:

The table is used to store records with cd_check_recipient ‘1’ in VNSFL. All the values in the table are truncated before filling the data for the

CDSBC:

The table is used to store all records in VDSBH. All the values in the table are truncated before filling the data for the table.

CDEMO:

The table is used to store all records in VDEMO. All the values in the table are truncated before filling the data for the table.

Tables refreshed in SP_REFRESH_STEP4 procedure

CSORD:

The table is used to store all valid records from VSORD and VJDCA. All the values in the table are truncated before filling the data for the table.

CAHIS:

The table is used to store all the records in VAHIS. All the values in the table are truncated before filling the data for the table.

CIVMG:

The table is used to store only the current and previous months welfare cords from VIVMG. All the values in the table are truncated before filling the data for the table.

CEHIS:

The table is used to store all the records in VEHIS. All the values in the table are truncated before filling the data for the table.

COBLE:

The table is used to store all valid records in VOBLE. All the values in the table are truncated before filling the data for the table.

CRCTH:

The table is used to store all the receipts not backed out from VRCTH.

When previous batch run date is lowdate then all non backedout receipts are moved from VRCTH or the Receipts are incremental refreshed from previous batch run date.

CENFN:

The table stores activity chain details of NMSN, FIDM, REVW, AREN.

Field Description dt_open_nmsn Start date of the recent NMSN sent date cd_nmsn_closure_reason

NMSN closure reason of the most recent closed chain with no open chain existing from last 6 months cd_status_open_fidm Status of the open FIDM chain cd_result_closed_fidm Result of the most recent FIDM chain from last 6 months cd_status_open_csln Status of the open CSLN chain cd_result_closed_csln Result of the most recent CSLN chain from last 6 months dt_contest_fop Most recent fop contest date cd_contest_result_fop Result from the most recent contest fop.

dt_contest_stx Most recent stx contest date cd_contest_result_stx Result from the most recent contest stx.

cd_status_triennial_review Current status of the triennial review.

When setting CD_STATUS_OPEN_FIDM to 'A – Under Appeal' only consider most recent open FIDM major activity where the minor activity is either REARF, RRONA, AARFP, AIRRN, or SARCD.

When setting CD_STATUS_OPEN_FIDM to 'S – In Progress' only consider most recent open FIDM major activity where the minor activity is not

REARF, RRONA, AARFP, AIRRN, SARCD, ECFLA, OCFUR, or NOTOB.

Open FIDM activity chain is not considered ‘In Progress’ at step NOTOB – 'Send Notice to Obligor of Intent to Levy' thereby causing the Bank

Levy information to be shown on the web only when the FIDM activity chain is advanced to step ‘ANCPA – Record Response from NCP/Joint

Account Holder/FI’.

All the values in the table are truncated before filling the data for the table.

CSPAY:

The values from VLSUP with seq_event_functional 1820 and type_record ‘O’ are moved. When previous batch run date is lowdate then all the records are moved excluding reversed receipts else if it is incremental refresh, all the existing receipts in CSPAY which are now reversed will be deleted first before inserting only the dt_distribute records before previous and current batch run date excluding reversed receipts.

CDSBH:

The table is used to store all valid records from VDSBH. If previous batch run date is low date then all the values in the table are truncated before filling the data for the table or only the data’s from previous batch run to current run date are deleted and inserted.

Table CREFL is appended by CUSW schema table insert procedures called within refresh steps 1, 2, 3, and 4.

CREFL:

The table logs the CUSW schema table that is refreshed in the batch process with its elapsed time.

Log any errors or successful completion in the VBSTL table for future reference.

Update Last Run date in the VPARM table with the run date, upon successful completion.

1.1.4 Flow Chart

1.1.5 Frequency

Daily

1.1.6 Dependencies

1.1.6.1 1.1.6.1 Predecessors:

BATCH_AFP_NOTICE_GENERATION

1.1.6.2 1.1.6.2 Successors:

23DPP00838 10 | P a g e

N/A

1.1.7 Restart Procedures

Restart the job from the last commit point

1.1.8 Instructions if Job is skipped

Job can be skipped.

1.1.9 Commit Frequency

One commit at the end of the batch job

1.1.10 Exception Threshold

Zero

1.1.11 Input File(s)

1.1.12 Output File(s)

1.1.13 Contact information

Name of the Agency Technical Contact Functional Contact

1.1.14 Stored Procedure / Functions matrix

S.

NO

Stored Procedure Name/ Arguments Nature of the procedure /

Function

Description Output Tables / Screens Applicability

I A U D

1 BATCH_COMMON.

SP_GET_BATCH_DETAILS

AS_ID_JOB

Common Routine Get Run date and Last run date

Internally called by

SP_MVIEW_REFRESH_ALL

SP_MVIEW_REFRESH

SP_REFRESH_STEP1

SP_REFRESH_STEP2

SP_REFRESH_STEP3

SP_REFRESH_STEP4

AD_DT_RUN

AD_DT_LAST_RUN

AN_NO_COMMIT_FREQ

AN_NO_EXCEPTION_THRE

SHOLD

AC_MSG_CODE

AS_ERROR_DESC

2 BATCH_COMMON.

SP_UPDATE_PARM_DATE

Common Routine Updating Last run date

Internally called by

SP_MVIEW_REFRESH_ALL

SP_MVIEW_REFRESH

SP_REFRESH_STEP1

SP_REFRESH_STEP2

SP_REFRESH_STEP3

SP_REFRESH_STEP4

AS_ID_JOB

AD_DT_RUN

3 BATCH_COMMON.SP_BSTL_LOG Common Routine Inserting the batch status - - - - - -

4 BATCH_COMMON.

SP_BATCH_RESTART_UPDATE

Common Routine Insert the restart key

SP_REFRESH_STEP1

SP_REFRESH_STEP2

SP_REFRESH_STEP3

SP_REFRESH_STEP4

23DPP00838 11 | P a g e

5 BATCH_CUSW.

SP_REFRESH_STEP1

Process This procedure is used to refresh CAMJR, CFIPS, CREFM, CAMNR, CBWNT, CDINS, CSTAT, CUSRT, CSWKS, CCASE, CENSD tables in the customer service schema

6 BATCH_CUSW.SP_INSERT_CAMJR procedure This procedure inserts records in CAMJR table

AC_MSG_CODE

AS_ERROR_DESC

CAMJR

VAMJR

Y

Y

This procedure is internally called by

SP_REFRESH_STEP1

7 BATCH_CUSW.SP_INSERT_CFIPS procedure This procedure inserts records in CFIPS table AC_MSG_CODE

AS_ERROR_DESC

CFIPS

VFIPS

Y Y

8 BATCH_CUSW.SP_INSERT_CREFM procedure This procedure inserts records in CREFM

AC_MSG_CODE

AS_ERROR_DESC

CREFM

VREFM

Y

Y

9 BATCH_CUSW.

SP_INSERT_CAMNR

procedure This procedure inserts records in CAMNR table

AC_MSG_CODE

AS_ERROR_DESC

CAMNR

VAMNR

Y

Y

10 BATCH_CUSW.

SP_INSERT_CBWNT

procedure This procedure inserts records in CBWNT table

AC_MSG_CODE

AS_ERROR_DESC

CBWNT

VBWNT

Y

Y

11 BATCH_CUSW.SP_INSERT_CDINS procedure This procedure inserts records in CDINS table AC_MSG_CODE

CDINS

VDINS

Y Y

12 BATCH_CUSW.SP_INSERT_CSTAT procedure This procedure inserts records in CSTAT table AC_MSG_CODE

CSTAT

VSTAT

Y Y

13 BATCH_CUSW.SP_INSERT_CUSRT procedure This procedure inserts records in CUSRT

AC_MSG_CODE

AS_ERROR_DESC

CUSRT

VUSRT

Y

Y

14 BATCH_CUSW.SP_INSERT_CSWKS procedure This procedure inserts records in CSWKS

AC_MSG_CODE

AS_ERROR_DESC

CSWKS

VSWKS

Y

Y

15 BATCH_CUSW.SP_INSERT_CCASE procedure This procedure inserts records in CCASE

AC_MSG_CODE

AS_ERROR_DESC

CCASE

VCASE

Y

Y

16 BATCH_CUSW.SP_INSERT_CENSD procedure This procedure inserts records in CENSD

AC_MSG_CODE

AS_ERROR_DESC

CENSD

VENSD

Y

Y

17 BATCH_CUSW.

SP_REFRESH_STEP2

Process This procedure is used to refresh CMINS, CICAS, CSBAL, CMDET, CMSSN, CCMEM, CCTHB, CDSBL tables in the customer service schema

- VRSTL Y - - -

18 BATCH_CUSW.SP_INSERT_CMINS procedure This procedure inserts records in CMINS table AC_MSG_CODE

AS_ERROR_DESC

CMINS

VMINS

Y Y

19 BATCH_CUSW.SP_INSERT_CICAS procedure This procedure inserts records in CICAS table AC_MSG_CODE

CCIAS

VCIAS

Y Y

20 BATCH_CUSW.SP_INSERT_CSPAY procedure This procedure inserts records in CSPAY AC_MSG_CODE CSPAY - Y - Y table AS_ERROR_DESC VLSUP Y - - -

VELRP Y - - -

This procedure is internally called by VREFM Y - - -

SP_REFRESH_STEP4 VRCTH Y - - -

21 BATCH_CUSW.SP_INSERT_CSBAL procedure This procedure inserts records in CSBAL table

AC_MSG_CODE

AS_ERROR_DESC

CSBAL

VLSUP

Y

Y

22 BATCH_CUSW.SP_INSERT_CMDET procedure This procedure inserts records in CMDET

AC_MSG_CODE

AS_ERROR_DESC

CMDET

VMDET

Y

Y

23DPP00838 12 | P a g e

23 BATCH_CUSW.SP_INSERT_CMSSN procedure This procedure inserts records in CMSSN

AC_MSG_CODE

AS_ERROR_DESC

CMSSN

VMSSN

Y

Y

24 BATCH_CUSW.

SP_INSERT_CCMEM

procedure This procedure inserts records in CCMEM

AC_MSG_CODE

AS_ERROR_DESC

CCMEM

VCMEM

Y

Y

25 BATCH_CUSW.SP_INSERT_CCTHB procedure This procedure inserts records in CCTHB

AC_MSG_CODE

AS_ERROR_DESC

CCTHB

VCTHB

Y

Y

26 BATCH_CUSW.SP_INSERT_CDSBL procedure This procedure inserts records in CDSBL

AC_MSG_CODE

AS_ERROR_DESC

CDSBL

VDSBL

Y

Y

27 BATCH_CUSW.

SP_REFRESH_STEP3

Process This procedure is used to refresh

CGTST,

CMHIS,

CDMNR,

COTHP,

CNSFL,

CDSBC,

CDEMO

tables in the customer service schema

28 BATCH_CUSW.SP_INSERT_CGTST procedure This procedure inserts records in CGTST table

SP_REFRESH_STEP3

AC_MSG_CODE

AS_ERROR_DESC

CGTST

VGTST

Y

Y

29 BATCH_CUSW.SP_INSERT_CMHIS procedure This procedure inserts records in CMHIS table

AC_MSG_CODE

AS_ERROR_DESC

CMHIS

VMHIS

Y

Y

30 BATCH_CUSW.

SP_INSERT_CDMNR

procedure This procedure inserts records in CDMNR

AC_MSG_CODE

AS_ERROR_DESC

CDMNR

VDMNR

VDMJR

Y

Y

Y

31 BATCH_CUSW.SP_INSERT_COTHP procedure This procedure inserts records in COTHP

AC_MSG_CODE

AS_ERROR_DESC

COTHP

VOTHP

Y

Y

32 BATCH_CUSW.SP_INSERT_CNSFL procedure This procedure inserts records in CNSFL table

AC_MSG_CODE

AS_ERROR_DESC

CNSFL

VNSFL

Y

Y

33 BATCH_CUSW.SP_INSERT_CDSBH procedure This procedure inserts records in CDSBH

AC_MSG_CODE

AS_ERROR_DESC

CDSBH

VDSBH

Y

Y

34 BATCH_CUSW.SP_INSERT_CDSBC procedure This procedure inserts records in CDSBC

AC_MSG_CODE

AS_ERROR_DESC

CDSBC

VDSBC

Y

Y

35 BATCH_CUSW.

SP_INSERT_CDEMO

procedure This procedure inserts records in CDEMO

AC_MSG_CODE

AS_ERROR_DESC

CDEMO

VDEMO

Y

Y

36 BATCH_CUSW.

SP_REFRESH_STEP4

Process This procedure is used to refresh CAHIS, CSORD, CIVMG, CEHIS, COBLE, CRCTH, CENFN, CSPAY, CDSBH tables in the customer service schema

- VRSTL Y - - -

37 BATCH_CUSW.SP_INSERT_CSORD procedure This procedure inserts records in CSORD

AC_MSG_CODE

AS_ERROR_DESC

CSORD

VSORD

VJDCA

Y Y

Y

38 BATCH_CUSW.SP_INSERT_CAHIS procedure This procedure inserts records in CAHIS table

AC_MSG_CODE

AS_ERROR_DESC

CAHIS

VAHIS

Y

Y

39 BATCH_CUSW.SP_INSERT_CIVMG procedure This procedure inserts records in CIVMG

AC_MSG_CODE

AS_ERROR_DESC

CIVMG

VIVMG

Y

Y

40 BATCH_CUSW.SP_INSERT_CEHIS procedure This procedure inserts records in CEHIS table

AC_MSG_CODE

AS_ERROR_DESC

CEHIS

VEHIS

Y

Y

41 BATCH_CUSW.SP_INSERT_COBLE procedure This procedure inserts records in COBLE

AC_MSG_CODE

AS_ERROR_DESC

COBLE

VOBLE

Y

Y

23DPP00838 13 | P a g e

42 BATCH_CUSW.SP_INSERT_CRCTH procedure This procedure inserts records in CRCTH

AC_MSG_CODE

AS_ERROR_DESC

CRCTH

VRCTH

Y

Y

43 BATCH_CUSW.SP_INSERT_CENFN procedure This procedure inserts records in CENFN

AC_MSG_CODE

AS_ERROR_DESC

CENFN

VENFN

VDMJR

VDMNR

Y

Y

Y

Y

44 BATCH_CUSW.SP_INSERT_CREFL

AD_DT_RUN

AS_INDV_STEP

AD_DT_START

AD_DT_END

procedure This procedure is called by all the insert procedures in step1,2,3 and 4 to log the batch status into CREFL table

- CREFL Y - -

45 BATCH_CUSW.SP_DBMS_STAT

AS_TABLE_NAME

procedure This procedure is used to gather table stats.

This procedure is called by all the insert procedures in step1,2,3 and 4 to gather table stats before inserting into CREFL table

AC_MSG_CODE

AS_ERROR_DESC

All the tables used in the insert procedures in step1, 2, 3 and 4.

46 BATCH_CUSW.

SP_TRUNCATE_TABLE

AS_TABLE_NAME

procedure This procedure is used to truncate the input table.

This procedure is called by all the insert procedures in step1,2,3 and 4.

AC_MSG_CODE

AS_ERROR_DESC

All the tables used in the insert procedures in step1, 2, 3 and 4.

47 BATCH_CUSW.

SP_MVIEW_REFRESH_ALL

AS_REFRESH_AFTER_ERRORS

procedure This procedure is used to refresh all the materialized view in

NJKIDSCUSW schema on demand

48 BATCH_CUSW.

SP_MVIEW_REFRESH

AS_MVIEW_NAME

AS_REFRESH_AFTER_ERRORS

procedure This procedure is used to refresh individual materialized view in

NJKIDSCUSW schema on demand