GPCH_EG_PSTA_VW(SQL View) |
Index Back |
|---|---|
ELM Provider Status ViewELM Provider Status View |
| SELECT DISTINCT FS.COMPANY ,FS.GPCH_RC_PAY_YEAR ,FS.GPCH_EG_DOMAINID ,PS.GPCH_EG_REQUEST_ID ,PS.GPCH_SI_PROV_TYPE ,PS.GPCH_SI_PROV_CD ,SI.GPCH_EG_TRANS_MODE ,FS.FILENAME ,FS.TIME_STAMP ,FS.GPCH_REPORT_STATUS ,PS.GPCH_REPORT_STATUS ,PS.GPCH_EG_RESEND ,FS.GPCH_EG_REQID_OLD ,FS.GPCH_EG_RESPID_OLD ,DS.GPCH_EG_VERSION ,FS.GPCH_SEND_FLAG ,FS.GPCH_GETSTATUS_FLG ,FS.GPCH_SENDSYN_FLAG ,FS.GPCH_STATUSSYN_FLG ,FS.GPCH_MAX_MIN_IND ,FS.GPCH_EG_SORT_1 ,FS.GPCH_EG_SORT_2 ,FS.GPCH_EG_SORT_3 FROM PS_GPCH_EG_FSTATUS FS ,PS_GPCH_EG_PSTATUS PS ,PS_GPCH_SI_PROVIDR SI ,PS_GPCH_EG_DSET DS WHERE FS.COMPANY =PS.COMPANY AND FS.GPCH_RC_PAY_YEAR =PS.GPCH_RC_PAY_YEAR AND FS.GPCH_EG_DOMAINID =PS.GPCH_EG_DOMAINID AND PS.GPCH_EG_REQUEST_ID = FS.GPCH_EG_REQUEST_ID AND PS.GPCH_SI_PROV_TYPE =SI.GPCH_SI_PROV_TYPE AND PS.GPCH_SI_PROV_CD =SI.GPCH_SI_PROV_CD AND SI.EFFDT = ( SELECT MAX(EFFDT) FROM PS_GPCH_SI_PROVIDR WHERE GPCH_SI_PROV_TYPE =PS.GPCH_SI_PROV_TYPE AND GPCH_SI_PROV_CD =PS.GPCH_SI_PROV_CD) AND DS.COMPANY = FS.COMPANY AND DS.GPCH_EG_DOMAINID = FS.GPCH_EG_DOMAINID AND DS.EFFDT = ( SELECT MAX(EFFDT) FROM PS_GPCH_EG_DSET WHERE COMPANY = FS.COMPANY AND GPCH_EG_DOMAINID = FS.GPCH_EG_DOMAINID) AND DS.EFFDT <= %CurrentDateIn |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 2 | GPCH_RC_PAY_YEAR | Number(4,0) | SMALLINT NOT NULL | Pay Year |
| 3 | GPCH_EG_DOMAINID | Number(3,0) | SMALLINT NOT NULL | Domain Set ID for the ELM Domain Sets |
| 4 | GPCH_EG_REQUEST_ID | Character(30) | VARCHAR2(30) NOT NULL | Holds Request ID in the ELM File |
| 5 | GPCH_SI_PROV_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Provider Type
0=Identify 1=Others 2=UVG-LAA 3=KTG-AMC 4=UVGZ-LAAC 5=FAK-CAF 6=BVG-LPP 7=AHV-AVS 8=LAW-CDS 9=BFS-OFS A=QST-IS B=Write File C=Base AL08 D=GRFI-FRFI E=EO-APG I=BNK_BNQ |
| 6 | GPCH_SI_PROV_CD | Character(6) | VARCHAR2(6) NOT NULL | Provider Code |
| 7 | GPCH_EG_TRANS_MODE | Character(3) | VARCHAR2(3) NOT NULL |
ELM File TRansmission Mode(EIV/PIV)
1=EIV 2=PIV |
| 8 | FILENAME | Character(80) | VARCHAR2(80) NOT NULL | File Name |
| 9 | TIME_STAMP | DateTime(26) | TIMESTAMP | Time Stamp |
| 10 | GPCH_REPORT_STATUS | Character(4) | VARCHAR2(4) NOT NULL |
Report Status for Year End Process
0001=Success 0002=Accepted 0003=Warning 0004=Error 0005=Soap Fault 0006=Fault 0007=Ignored 0008=Open 0009=N 0010=File Error 0011=Processing 0012=Completion Release Missing |
| 11 | GPCH_RPT_E_STATUS | Character(4) | VARCHAR2(4) NOT NULL |
GPCH ELM Report External Status
0001=Success 0002=Accepted 0003=Warning 0004=Error 0005=Soap Fault 0006=Fault 0007=Ignored 0008=Open 0009=N 0010=File Error 0011=Processing 0012=Completion Release Missing |
| 12 | GPCH_EG_RESEND | Character(1) | VARCHAR2(1) NOT NULL | GPCH ELM Flag for Resend |
| 13 | GPCH_EG_REQID_OLD | Character(30) | VARCHAR2(30) NOT NULL | Old Request ID for ELM |
| 14 | GPCH_EG_RESPID_OLD | Character(30) | VARCHAR2(30) NOT NULL | Old Response Id for ELM file |
| 15 | GPCH_EG_VERSION | Character(4) | VARCHAR2(4) NOT NULL |
Swissdec Version Number
2=Version 4.0 5=Version 5.0 |
| 16 | GPCH_SEND_FLAG | Character(14) | VARCHAR2(14) NOT NULL | Send Flag |
| 17 | GPCH_GETSTATUS_FLG | Character(14) | VARCHAR2(14) NOT NULL | GetStatus Flag |
| 18 | GPCH_SENDSYN_FLAG | Character(14) | VARCHAR2(14) NOT NULL | Send Sync. Flag |
| 19 | GPCH_STATUSSYN_FLG | Character(14) | VARCHAR2(14) NOT NULL | Get Status Sync. Flag |
| 20 | GPCH_MAX_MIN_IND | Character(1) | VARCHAR2(1) NOT NULL | Max Min Indicator |
| 21 | GPCH_EG_SORT_1 | Character(1) | VARCHAR2(1) NOT NULL |
Sort Criteria 1.
C=Residence Canton E=Emplid F=Firstname G=Language L=Lastname |
| 22 | GPCH_EG_SORT_2 | Character(1) | VARCHAR2(1) NOT NULL |
Sort Criteria 2.
C=Residence Canton E=Emplid F=Firstname G=Language L=Lastname |
| 23 | GPCH_EG_SORT_3 | Character(1) | VARCHAR2(1) NOT NULL |
Sort Criteria 3.
C=Residence Canton E=Emplid F=Firstname G=Language L=Lastname |