GPAU_PSSAP_VW

(SQL View)
 Index Back

ComSuper CSV file data view

This view is used to populate the CSV file to be used in ComSuper reporting

SELECT DISTINCT A.CAL_RUN_ID , EMPLID , A.PAY_ENTITY , B.RECIPIENT_ID , A.GPAU_SMART_KEY , GPAU_SENDER_ABN , 'urn:oasis:tc:ebcore:partyid-type:ABN:0151' , GPAU_SENDER_URL , CASE WHEN GPAU_ERROR_ENABLED = 'Y' THEN 'TRUE' ELSE 'FALSE' END , GPAU_SENDER_ABN , GPAU_SENDER_NAME , GPAU_CON_FAMILY_NM , GPAU_CONTACT_GV_NM , DISPLAY_NAME , GPAU_EMAIL_ADDR , PHONE , GPAU_PAYER_ABN , GPAU_PAYER_NM , GPAU_BRANCH_EC_CD , GPAU_ACCOUNT_EC_ID , GPAU_ACCOUNT_NAME , GPAU_PAYEE_ABN , GPAU_TARGTPRODID , GPAU_RECEIVER_NAME , GPAU_TARGET_URL , B.GPAU_PYMT_MTHD , DEBIT_DT , CASE WHEN B.GPAU_PYMT_MTHD = 'BPAY' THEN B.GPAU_CRN ELSE B.GPAU_LODGEMENT_REF END , CASE WHEN B.GPAU_PYMT_MTHD = 'BPAY' THEN B.GPAU_BILLER_CODE ELSE ' ' END , B.PAYMENT_AMT , BRANCH_EC_CD , ACCOUNT_EC_ID , ACCOUNT_NAME , A.GPAU_ER_ABN , GPAU_PAYCNT_ID , GPAU_ER_NAME , GPAU_SUPER_EMPLID , GPAU_TFN , NAME_TITLE , NAME_SUFFIX , LAST_NAME , FIRST_NAME , PERSON_NAME , SEX , BIRTHDATE , ADDRESS_TYPE , ADDRESS1 , ADDRESS2 , ADDRESS3 , ' ' , CITY , POSTAL , STATE , COUNTRY , EMAIL_ADDR2 , CASE WHEN GPAU_PH_SERVICE = '02' THEN PHONE1 ELSE ' ' END , CASE WHEN GPAU_PH_SERVICE = '01' THEN PHONE1 ELSE ' ' END , GPAU_MEMBER_ID , EMPLID_PAYEE ,A.GPAU_DT_END_CAT , GPAU_CESSATION_RSN , PERIOD_BEGIN_DT , PERIOD_END_DATE , CASE WHEN GPAU_SGC_AMT >= 0 THEN GPAU_SGC_AMT ELSE 0.00 END , CASE WHEN GPAU_AWARD_AMT >= 0 THEN GPAU_AWARD_AMT ELSE 0.00 END , CASE WHEN GPAU_PERS_AMT >= 0 THEN GPAU_PERS_AMT ELSE 0.00 END , CASE WHEN GPAU_SACRIFICE_AMT >= 0 THEN GPAU_SACRIFICE_AMT ELSE 0.00 END , CASE WHEN GPAU_VOLUNTARY_AMT >= 0 THEN GPAU_VOLUNTARY_AMT ELSE 0.00 END , CASE WHEN GPAU_SPOUSE_AMT >= 0 THEN GPAU_SPOUSE_AMT ELSE 0.00 END , ' ' , ' ' , A.HIRE_DT , ' ' , ' ' , GPAU_SUPEC_SAL , %DateNull , %DateNull , CASE WHEN GPAU_YRLY_INS_SAL >= 0 THEN GPAU_YRLY_INS_SAL ELSE 0.00 END , ' ' , ' ' , ' ' , %DateNull , ' ' , JOB_CLASS , %DateNull , %DateNull , ' ' FROM PS_GPAU_SS_COM_TBL A LEFT OUTER JOIN PS_GPAU_SS_CTX_DAT B ON A.GPAU_SMART_KEY = B.GPAU_SMART_KEY AND A.CAL_RUN_ID = B.CAL_RUN_ID AND A.PAY_ENTITY = B.PAY_ENTITY AND A.RECIPIENT_ID = B.RECIPIENT_ID AND A.GPAU_LODGEMENT_REF = B.GPAU_LODGEMENT_REF WHERE A.EMPL_RCD = ( SELECT MIN(D.EMPL_RCD) FROM PS_GPAU_SS_COM_TBL D WHERE D.CAL_RUN_ID = A.CAL_RUN_ID AND D.EMPLID = A.EMPLID AND D.PAY_ENTITY = D.PAY_ENTITY AND A.GPAU_LODGEMENT_REF = D.GPAU_LODGEMENT_REF) AND A.GPAU_COMSUPER_FUND = 'A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id
2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
3 PAY_ENTITY Character(10) VARCHAR2(10) NOT NULL Pay Entity
4 RECIPIENT_ID Character(8) VARCHAR2(8) NOT NULL Payment recipient ID
5 GPAU_SMART_KEY Character(90) VARCHAR2(90) NOT NULL Primary Key used in SuperStream reporting
6 GPAU_ABN Character(11) VARCHAR2(11) NOT NULL A unique number used by the ATO to identify a contractor.
7 DESCR100 Character(100) VARCHAR2(100) NOT NULL Length 100 Description
8 GPAU_SENDER_URL Character(254) VARCHAR2(254) NOT NULL Sender E-Service Address
9 GPAU_ERR_ENBLD Character(5) VARCHAR2(5) NOT NULL Field used for Error Messaging
10 GPAU_SENDER_ABN Character(11) VARCHAR2(11) NOT NULL Sender ABN
11 GPAU_SENDER_NAME Character(76) VARCHAR2(76) NOT NULL Sender Name
12 GPAU_CON_FAMILY_NM Character(30) VARCHAR2(30) NOT NULL Given family name
13 GPAU_CONTACT_GV_NM Character(45) VARCHAR2(45) NOT NULL Given contact name
14 DISPLAY_NAME Character(50) VARCHAR2(50) NOT NULL Used to display the person's name in the PERS_SRCH_SBP, EMPL_SRCH_SBP and the components that allow a person to be created
15 GPAU_EMAIL_ADDR Character(76) VARCHAR2(76) NOT NULL Supplier E-mail address
16 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
17 GPAU_PAYER_ABN Character(11) VARCHAR2(11) NOT NULL Payer ABN
18 GPAU_PAYER_NM Character(200) VARCHAR2(200) NOT NULL Payer Name
19 GPAU_BRANCH_EC_CD Character(17) VARCHAR2(17) NOT NULL Branch Code
20 GPAU_ACCOUNT_EC_ID Character(35) VARCHAR2(35) NOT NULL Account Number used in banking.
21 GPAU_ACCOUNT_NAME Character(50) VARCHAR2(50) NOT NULL Account Name
22 GPAU_PAYEE_ABN Character(11) VARCHAR2(11) NOT NULL Payee ABN
23 GPAU_TARGTPRODID Character(14) VARCHAR2(14) NOT NULL GPAUS - Stores the target Superannuation Fund's Product ID. Used in SuperEC processing.
24 GPAU_RECEIVER_NAME Character(76) VARCHAR2(76) NOT NULL Receiver Name
25 GPAU_TARGET_URL Character(254) VARCHAR2(254) NOT NULL E-service address used in SuperStream reporting
26 GPAU_PYMT_MTHD Character(15) VARCHAR2(15) NOT NULL Payment Method
27 DEBIT_DT Date(10) DATE Debit Date
28 GPAU_LODGEMENT_REF Character(50) VARCHAR2(50) NOT NULL This field is used in the AE program GPAU_EFT_CMX.
29 GPAU_BILLER_CODE Character(20) VARCHAR2(20) NOT NULL BPAY Biller Code
30 PAYMENT_AMT Number(19,3) DECIMAL(18,3) NOT NULL Payment Amount
31 BRANCH_EC_CD Character(17) VARCHAR2(17) NOT NULL Bank Branch ID
32 ACCOUNT_EC_ID Character(35) VARCHAR2(35) NOT NULL Account Number used in banking.
33 ACCOUNT_NAME Character(50) VARCHAR2(50) NOT NULL Account Name
34 GPAU_ER_ABN Character(11) VARCHAR2(11) NOT NULL Employer ABN
35 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
36 GPAU_ER_NAME Character(200) VARCHAR2(200) NOT NULL Name of the employer
37 GPAU_SUPER_EMPLID Character(20) VARCHAR2(20) NOT NULL The ID provide by Super Fund to the employer
38 GPAU_TFN Character(9) VARCHAR2(9) NOT NULL This is the payee's tax file number. This is the payee's unique identification number for tax purposes.
39 NAME_TITLE Character(30) VARCHAR2(30) NOT NULL Name Title
40 NAME_SUFFIX Character(15) VARCHAR2(15) NOT NULL Name Suffix
41 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
42 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
43 PERSON_NAME Character(100) VARCHAR2(100) NOT NULL Name
44 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
45 BIRTHDATE Date(10) DATE Date of Birth
46 GPAU_ADDR_USGE Character(3) VARCHAR2(3) NOT NULL Address Usage code
47 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
48 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
49 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
50 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
51 CITY Character(30) VARCHAR2(30) NOT NULL City
52 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
53 STATE Character(6) VARCHAR2(6) NOT NULL State
54 COUNTRY_2CHAR Character(2) VARCHAR2(2) NOT NULL 2-Char Country Code
55 EMAIL_ADDR2 Character(70) VARCHAR2(70) NOT NULL Email Address
56 PHONE1 Character(24) VARCHAR2(24) NOT NULL Phone number field for discounts and perks eligibility file.
57 PHONE2 Character(24) VARCHAR2(24) NOT NULL Phone number field for discounts and perks eligibility file.
58 GPAU_MEMBER_ID Character(18) VARCHAR2(18) NOT NULL The Membership Group ID is required for the file generated by GPAU_RCPFILE.
59 EMPLID_PAYEE Character(11) VARCHAR2(11) NOT NULL Payee Empl ID
60 TERMINATION_DT Date(10) DATE Termination Date
61 ACTION_DESCR Character(50) VARCHAR2(50) NOT NULL Action Description
62 PERIOD_BEGIN_DT Date(10) DATE Period Begin Date
63 PERIOD_END_DATE Date(10) DATE Period End Date
64 GPAU_SGC_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Super Gurantee amount
65 GPAU_AWARD_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Award/Productivity amount
66 GPAU_PERS_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Personal Contributions Amount
67 GPAU_SACRIFICE_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Salary Sacrificed contribution
68 GPAU_VOLUNTARY_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Voluntary contribution amount
69 GPAU_SPOUSE_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Spouse contribution amount
70 GPAU_CHILD_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Child contributions amount
71 GPAU_THRDPARTY_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Third party contribution amount
72 HIRE_DT Date(10) DATE First Start Date
73 STATUSSTRING Character(5) VARCHAR2(5) NOT NULL Status String
74 GPAU_SUPER_BEN_AMT Number(19,3) DECIMAL(18,3) NOT NULL Benefit amount
75 GPAU_SUPEC_SAL Number(19,3) DECIMAL(18,3) NOT NULL GPAUS - SuperEC Salary field. This field stores Annual or Superannuation Salary amoutn for employees enrolled in SuperEC.
76 GPAU_RPT_START_DT Date(10) DATE Period of report Start Date
77 GPAU_RPT_END_DT Date(10) DATE Period of report end date
78 GPAU_YRLY_INS_SAL Number(19,3) DECIMAL(18,3) NOT NULL Annual Salary insurance amount
79 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
80 OCCUPATION Character(40) VARCHAR2(40) NOT NULL Occup
81 GPAU_INSR_OPT_OUT Character(5) VARCHAR2(5) NOT NULL Indicates if the employee does not want the Super Fund to provide an Insurance product to them.
82 GPAU_FUND_REG_DT Date(10) DATE Fund Registration Date
83 GPAU_FUND_BEN_CATG Character(25) VARCHAR2(25) NOT NULL Benefit Category used in SuperStream reporting
84 JOB_CLASS Character(10) VARCHAR2(10) NOT NULL Job Class for discounts and perks eligibility file.
85 GPAU_SUPER_ST_DT Date(10) DATE Report period start date
86 GPAU_SUPER_END_DT Date(10) DATE Report period end date
87 GPAU_AMEND_RSN Character(25) VARCHAR2(25) NOT NULL SuperStream reporting data amendment reason