GPNZ_PDR_CSV_VW(SQL View) |
Index Back |
|---|---|
PDR - View for File GenerationNew Zealand - New and Departing Employee - View for File Processing |
| SELECT DISTINCT TMP.PROCESS_INSTANCE , TMP.EMPLID , TMP.GPNZ_IRD_NUMBER , TMP.GPNZ_NAME , TMP.NAME_TITLE , TMP.FIRST_NAME , TMP.MIDDLE_NAME , TMP.LAST_NAME , TMP.DATE_OF_BIRTH , TED.EFFDT , TED.GPNZ_TAX_SCALE , TMP.GPNZ_START_DT , TMP.GPNZ_FINISH_DT , TMP.GPNZ_PDR_KIWI_ST , TMP.EMAILID , TMP.GPNZ_PDR_MOB_CNTR , TMP.GPNZ_PDR_MOB_AC , TMP.GPNZ_PDR_MOB_NBR , TMP.GPNZ_PDR_MOB_EXT , TMP.GPNZ_PDR_DTPH_CNTR , TMP.GPNZ_PDR_DTPH_AC , TMP.GPNZ_PDR_DTPH_NBR , TMP.GPNZ_PDR_DTPH_EXT , TMP.GPNZ_PDR_UNIT_TYP , TMP.ADDRESS1 , TMP.ADDRESS2 , TMP.ADDRESS3 , TMP.ADDRESS4 , TMP.CITY , TMP.POSTAL , TMP.STATE , TMP.COUNTRY , TMP.ACCOUNT_TYPE_PYE , TMP.BANK_CD , TMP.BRANCH_EC_CD , TMP.ACCOUNT_EC_ID , TMP.ACCOUNT_NAME , TMP.GPNZ_PDR_KIWI_EG , TMP.GPNZ_EXEMPT_CD , TMP.GPNZ_KIW_OPTYN , TMP.GPNZ_OPTOUT_DT , TMP.GPNZ_OPTOUT_REASON , TMP.GPNZ_OPTOUT_DESCR FROM %Table(GPNZ_PDR_EEDTMP) TMP LEFT OUTER JOIN %Table(GPNZ_PDR_EETTMP) TED ON TMP.PROCESS_INSTANCE = TED.PROCESS_INSTANCE AND TMP.EMPLID = TED.EMPLID |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  PROCESS_INSTANCE |
Number(10,0) | DECIMAL(10) NOT NULL | Process Instance |
| 2 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 3 | GPNZ_IRD_NUMBER | Character(9) | VARCHAR2(9) NOT NULL | New Zealand IRD Number |
| 4 | GPNZ_NAME | Character(20) | VARCHAR2(20) NOT NULL | Name char field for IR348 download. Contains 'firstname surname', without commas. |
| 5 | NAME_TITLE | Character(30) | VARCHAR2(30) NOT NULL | Name Title |
| 6 | FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | First Name |
| 7 | MIDDLE_NAME | Character(30) | VARCHAR2(30) NOT NULL | MIDDLE_NAME |
| 8 | LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 9 | DATE_OF_BIRTH | Date(10) | DATE | Date of Birth |
| 10 | EFFDT | Date(10) | DATE |
Effective Date
Default Value: %date |
| 11 | GPNZ_TAX_SCALE | Character(4) | VARCHAR2(4) NOT NULL | New Zealand Tax Scale |
| 12 | GPNZ_START_DT | Date(10) | DATE | Start Date for employees that have commenced employment during the month |
| 13 | GPNZ_FINISH_DT | Date(10) | DATE | Finish Date for employees terminated during the month |
| 14 | GPNZ_PDR_KIWI_ST | Character(22) | VARCHAR2(22) NOT NULL | PDR KIWI Status Description |
| 15 | EMAILID | Character(70) | VARCHAR2(70) NOT NULL | A user's E-mail address |
| 16 | GPNZ_PDR_MOB_CNTR | Character(30) | VARCHAR2(30) NOT NULL | PDR Mobile Phone |
| 17 | GPNZ_PDR_MOB_AC | Character(10) | VARCHAR2(10) NOT NULL | PDR Mobile Phone |
| 18 | GPNZ_PDR_MOB_NBR | Character(30) | VARCHAR2(30) NOT NULL | PDR Mobile PhoneNumber |
| 19 | GPNZ_PDR_MOB_EXT | Character(20) | VARCHAR2(20) NOT NULL | PDR Mobile PhoneExtn |
| 20 | GPNZ_PDR_DTPH_CNTR | Character(30) | VARCHAR2(30) NOT NULL | PDR DayTime Phone |
| 21 | GPNZ_PDR_DTPH_AC | Character(10) | VARCHAR2(10) NOT NULL | PDR DayTime Phone |
| 22 | GPNZ_PDR_DTPH_NBR | Character(30) | VARCHAR2(30) NOT NULL | PDR DayTime PhoneNumber |
| 23 | GPNZ_PDR_DTPH_EXT | Character(20) | VARCHAR2(20) NOT NULL | PDR DayTime PhoneExtn |
| 24 | GPNZ_PDR_UNIT_TYP | Character(50) | VARCHAR2(50) NOT NULL | PDR Employee File Unit Type |
| 25 | ADDRESS1 | Character(55) | VARCHAR2(55) NOT NULL | Address 1 |
| 26 | ADDRESS2 | Character(55) | VARCHAR2(55) NOT NULL | Address 2 |
| 27 | ADDRESS3 | Character(55) | VARCHAR2(55) NOT NULL | Address 3 |
| 28 | ADDRESS4 | Character(55) | VARCHAR2(55) NOT NULL | Address 4 |
| 29 | CITY | Character(30) | VARCHAR2(30) NOT NULL | City |
| 30 | POSTAL | Character(12) | VARCHAR2(12) NOT NULL | Postal |
| 31 | STATE | Character(6) | VARCHAR2(6) NOT NULL | State |
| 32 | COUNTRY | Character(3) | VARCHAR2(3) NOT NULL | Country |
| 33 | ACCOUNT_TYPE_PYE | Character(1) | VARCHAR2(1) NOT NULL |
Payee Bank Account Type
A=Current Account B=Building Society Roll Number C=Checking G=Giro Account R=Regular S=Savings T=Salary Account |
| 34 | BANK_CD | Character(11) | VARCHAR2(11) NOT NULL | Bank ID |
| 35 | BRANCH_EC_CD | Character(17) | VARCHAR2(17) NOT NULL | Bank Branch ID |
| 36 | ACCOUNT_EC_ID | Character(35) | VARCHAR2(35) NOT NULL | Account Number used in banking. |
| 37 | ACCOUNT_NAME | Character(50) | VARCHAR2(50) NOT NULL | Account Name |
| 38 | GPNZ_PDR_KIWI_EG | Character(22) | VARCHAR2(22) NOT NULL | PDR KIWI Eligibility code |
| 39 | GPNZ_EXEMPT_CD | Character(25) | VARCHAR2(25) NOT NULL | This Field records Income exemption code |
| 40 | GPNZ_KIW_OPTYN | Character(1) | VARCHAR2(1) NOT NULL | This Field records the option chosen by the user regarding the Kiwisaver. |
| 41 | GPNZ_OPTOUT_DT | Date(10) | DATE | Kiwisaver optout date |
| 42 | GPNZ_OPTOUT_REASON | Character(4) | VARCHAR2(4) NOT NULL |
Employee Kiwisaver optout reason
CRIT=Criteria not met ERIS=No invst stmt from Employer EVNT=Out of control events INER=<18 Yrs-Incorrectly controlled INFO=No KS Info from Employer IRIS=No invst stmt from IR OTHR=Other Information |
| 43 | GPNZ_OPTOUT_DESCR | Long Character(500) | VARCHAR2(500) | Employee Kiwisaver optout reason description when optout reason is other than the specified given reason. |