GPAU_SS_COM_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 , GPAU_PYMT_MTHD , DEBIT_DT , CASE WHEN GPAU_PYMT_MTHD = 'BPAY' THEN B.GPAU_CRN ELSE A.GPAU_LODGEMENT_REF END , CASE WHEN 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 , ' ' , LAST_NAME , FIRST_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_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 , ' ' , ' ' , ' ' , ' ' , ' ' , A.HIRE_DT , ' ' , GPAU_SUPER_BEN_AMT , GPAU_SUPEC_SAL , GPAU_RPT_START_DT , %DateNull , ' ' , ' ' , ' ' , ' ' , GPAU_FUND_REG_DT , ' ' , JOB_CLASS , %DateNull , %DateNull , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , GPAU_COMSUP_EMP_CN , GPAU_COMSUP_OTE , GPAU_COMSUP_CSPY , ' ' , ' ' , ' ' , %DateNull , ' ' , ' ' , ' ' , GPAU_PAYCNT_ID , ' ' , %DateNull , %DateNull , ' ' , %DateNull , %DateNull , GPAU_MBR_RATE , GPAU_COM_PCT_EFFDT , %DateNull , ' ' , %DateNull , %DateNull , ' ' , %DateNull , %DateNull , ' ' , %DateNull , %DateNull , ' ' , %DateNull , %DateNull , ' ' , %DateNull , %DateNull , GPAU_LWOP_CD , CASE WHEN GPAU_LWOP_CD <> ' ' THEN GPAU_LWOP_ST_DT ELSE %DateNull END , CASE WHEN GPAU_LWOP_CD <> ' ' THEN GPAU_LWOP_END_DT ELSE %DateNull END , %DateNull , %DateNull , GPAU_EMPL_EFFDT , %DateNull , ' ' , %DateNull , %DateNull , CASE WHEN GPAU_COMSUP_ADIC >= 0 THEN GPAU_COMSUP_ADIC ELSE 0.00 END , GPAU_COMSUP_CNDD , CASE WHEN GPAU_COMSUP_ADIC < 0 THEN GPAU_COMSUP_ADIC 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 JOB_CLASS = 'Full time' THEN GPAU_ACTUAL_HRS ELSE 0 END , CASE WHEN JOB_CLASS = 'Part time' THEN GPAU_ACTUAL_HRS ELSE 0 END , CASE WHEN JOB_CLASS = 'Part time' THEN GPAU_FUL_PRT_EFFDT ELSE %DateNull END , PERIOD_BEGIN_DT , PERIOD_END_DATE , A.GPAU_COMSUPER_FUND 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 AND A.GP_PAYGROUP = B.GP_PAYGROUP 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)

# 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
88 GPAU_DBM_PRETAX Character(1) VARCHAR2(1) NOT NULL Defined Benefit Member Pre Tax Contribution
89 GPAU_DBM_POSTAX Character(1) VARCHAR2(1) NOT NULL Defined Benefit Member Post Tax Contribution
90 GPAU_DBER_CONT Character(1) VARCHAR2(1) NOT NULL Defined Benefit Employer Contribution
91 GPAU_DBNM_PRETA Character(1) VARCHAR2(1) NOT NULL Defined Benefit Notional Member Pre Tax Contribution
92 GPAU_DBNM_POSTA Character(1) VARCHAR2(1) NOT NULL Defined Benefit Notional Member Post Tax Contribution
93 GPAU_COMSUP_EMP_CN Signed Number(12,2) DECIMAL(10,2) NOT NULL Employer Contribution
94 GPAU_COMSUP_OTE Number(11,2) DECIMAL(10,2) NOT NULL Stores Ordinary Time Earnings (OTE) or Fortnightly for ComSuper
95 GPAU_COMSUP_CSPY Signed Number(12,2) DECIMAL(10,2) NOT NULL Stores Casual Salary Payment for ComSuper
96 GPAU_SA_ALW_PAID Character(1) VARCHAR2(1) NOT NULL This switch indicates whether a Calendar can be paid in advance or not. It is used on the GP Aus Calendar Group details page to populate the User Field on the Cal Run Details table.
97 GPAU_NSA_AL_PAID Character(1) VARCHAR2(1) NOT NULL Notional Superannuable Allowances Paid
98 GPAU_SERV_FRACT Character(1) VARCHAR2(1) NOT NULL Service Fraction
99 GPAU_SEFR_EFFDT Date(10) DATE Service Fraction Effective Date
100 GPAU_FULTIME_HRS Number(7,2) DECIMAL(6,2) NOT NULL GP AUS - Stores Actual Hours worked for part-time employees contributing to ComSuper.
101 GPAU_CONTR_HRS Number(7,2) DECIMAL(6,2) NOT NULL GP AUS - Stores Actual Hours worked for part-time employees contributing to ComSuper.
102 GPAU_ACTHRS_PAID Number(7,2) DECIMAL(6,2) NOT NULL GP AUS - Stores Actual Hours worked for part-time employees contributing to ComSuper.
103 GPAU_PAYCNT_ID Character(20) VARCHAR2(20) NOT NULL Location identifier
104 GPAU_SERV_FRAC1 Character(1) VARCHAR2(1) NOT NULL Service Fraction
105 GPAU_SEFR_STDT Date(10) DATE Service Fraction Start Date
106 GPAU_SEFR_ENDDT Date(10) DATE Service Fraction end Date
107 GPAU_DB_ER_RATE Character(1) VARCHAR2(1) NOT NULL Defined Benefit Employer Rate
108 GPAU_DER_RT_STDT Date(10) DATE Defined Benefit Employer Rate Start Date
109 GPAU_DER_RT_ENDDT Date(10) DATE Defined Benefit Employer Rate End Date
110 GPAU_MBR_RATE Number(4,2) DECIMAL(3,2) NOT NULL GP AUS - Stores the ComSuper contributory percentage rates for member contributions to Superannuation.
111 GPAU_COM_PCT_EFFDT Date(10) DATE GP AUS - Stores the ComSuper contributory percentage rates for member contributions to Superannuation.
112 GPAU_DBM_RT_ENDDT Date(10) DATE Defined Benefit Member Rate End Date
113 GPAU_DB_ANN_SAL Character(1) VARCHAR2(1) NOT NULL Defined Benefit Annual Salary 1
114 GPAU_DBASAL_STDT Date(10) DATE Defined Benefit Annual Salary 1 Start Date
115 GPAU_DBASAL_ENDDT Date(10) DATE Defined Benefit Annual Salary 1 End Date
116 GPAU_DB_AN_SAL2 Character(1) VARCHAR2(1) NOT NULL Defined Benefit Annual Salary 2
117 GPAU_DBASAL2_STDT Date(10) DATE Defined Benefit Annual Salary 2 Start Date
118 GPAU_DBASAL2_ENDDT Date(10) DATE Defined Benefit Annual Salary 2 End Date
119 GPAU_DB_AN_SAL3 Character(1) VARCHAR2(1) NOT NULL Defined Benefit Annual Salary 3
120 GPAU_DBASAL3_STDT Date(10) DATE Defined Benefit Annual Salary 3 Start Date
121 GPAU_DBASAL3_ENDDT Date(10) DATE Defined Benefit Annual Salary 3 End Date
122 GPAU_DB_AN_SAL4 Character(1) VARCHAR2(1) NOT NULL Defined Benefit Annual Salary 4
123 GPAU_DBASAL4_STDT Date(10) DATE Defined Benefit Annual Salary 4Start Date
124 GPAU_DBASAL4_ENDDT Date(10) DATE Defined Benefit Annual Salary 4 End Date
125 GPAU_DB_AN_SAL5 Character(1) VARCHAR2(1) NOT NULL Defined Benefit Annual Salary 5
126 GPAU_DBASAL5_STDT Date(10) DATE Defined Benefit Annual Salary 5 Start Date
127 GPAU_DBASAL5_ENDDT Date(10) DATE Defined Benefit Annual Salary 5 End Date
128 GPAU_LWOP_CD Character(1) VARCHAR2(1) NOT NULL Leave Without Pay Code
1=1
3=3
6=6
129 GPAU_LWOP_ST_DT Date(10) DATE Stores the Employement Effective Date for comSuper
130 GPAU_LWOP_END_DT Date(10) DATE Stores the Employement Effective Date for comSuper
131 GPAU_ASINS_EFFDT Date(10) DATE Annual Insurance Salary Effective Date
132 GPAU_ASBEN_EFFDT Date(10) DATE Defined Benefit Annual Salary
133 GPAU_EMPL_EFFDT Date(10) DATE Stores the Employement Effective Date for comSuper
134 GPAU_BENCAT_EFFDT Date(10) DATE Benefit Category
135 GPAU_EMP_LOC_ID Character(1) VARCHAR2(1) NOT NULL Employee Location Identifier
136 GPAU_LOCID_STDT Date(10) DATE Employee Location Identifier start date
137 GPAU_LOCID_ENDDT Date(10) DATE Employee Location Identifier End Date
138 GPAU_COMSUP_ADIC Signed Number(10,2) DECIMAL(8,2) NOT NULL GP AUS - Stores ComSuper specific Additional Death and Invalidity Cover (ADIC)
139 GPAU_COMSUP_CNDD Number(2,0) SMALLINT NOT NULL Stores Contribution Due Days for ComSuper
140 GPAU_COMSUP_ADIC_N Signed Number(10,2) DECIMAL(8,2) NOT NULL GP AUS - Stores ComSuper specific Additional Death and Invalidity Cover (ADIC)
141 GPAU_NEG_EPSC_AMT Signed Number(10,2) DECIMAL(8,2) NOT NULL Negative EPSC
142 GPAU_NEG_MBR_CONT Signed Number(10,2) DECIMAL(8,2) NOT NULL Negative Member Contributions
143 STD_HOURS_NEW Number(7,2) DECIMAL(6,2) NOT NULL *New Standard Hours
144 GPAU_ACTUAL_HRS Number(7,2) DECIMAL(6,2) NOT NULL GP AUS - Stores Actual Hours worked for part-time employees contributing to ComSuper.
145 GPAU_FUL_PRT_EFFDT Date(10) DATE Stores the Employement Effective Date for comSuper
146 GPAU_MRR_PAY_STDT Date(10) DATE MRR Pay Period Start Date
147 GPAU_MRR_PAY_ENDDT Date(10) DATE MRR Pay Period End date
148 GPAU_COMSUPER_FUND Character(1) VARCHAR2(1) NOT NULL GP AUS - Stores the ComSuper Fund name for Super Contribution reporting for Aus Public Service customers who need to report contributions in ComSuper file format.
A=Public Sector Super Fund Accum
C=Commonwealth Super Scheme
P=Public Sector Super Fund