GPTH_RPT_COM_VW(SQL View) |
Index Back |
|---|---|
Company Report ViewCompany Report View |
| /*Added DISTINCT for Bug fix 29438276*/ SELECT DISTINCT D.EMPLID , D.CAL_RUN_ID , D.EMPL_RCD , D.GP_PAYGROUP , D.CAL_ID , D.ORIG_CAL_RUN_ID , D.RSLT_SEG_NUM , D.GP_RPT_KEY , B.PHONE_TYPE BUSN_TYPE , E.PIN_NUM , E.INSTANCE , A.DESCR , A.ADDRESS1 , A.ADDRESS2 , A.ADDRESS3 , A.ADDRESS4 , A.CITY , A.COUNTY , A.COUNTRY , A.STATE , A.POSTAL , B.COUNTRY_CODE , B.PHONE BUSN_PHONE , C.PHONE_TYPE FAX_TYPE , C.PHONE FAX , D.PROCESS_INSTANCE , D.OFF_CYCLE , D.PRD_BGN_DT , D.PRD_END_DT , D.SEG_BGN_DT , D.SEG_END_DT , D.PYMT_DT , D.CAL_PRD_ID , D.COUNTRY , D.PIN_GROSS_VAL , D.PIN_NET_VAL , D.CURRENCY_CD , D.CUR_RT_TYPE , D.RUN_TYPE , D.PAY_ENTITY , D.BUSINESS_UNIT , D.COMPANY , D.ESTABID , D.SETID_DEPT , D.DEPTID , D.SETID_LOCATION , D.LOCATION , D.GPTH_GOV , D.GPTH_DETAIL_PAGE , D.GPTH_DOWN_TO_TXT , G.GPTH_TAX_ID , G.GPTH_TAX_BRANCH , G.GPTH_TAX_ID_L , F.GPTH_SI_ER_PER , F.GPTH_SI_ER_ACCT , H.GPTH_SI_BRANCH_NO , E.ACM_FROM_DT , E.ACM_THRU_DT , E.SLICE_BGN_DT , E.SLICE_END_DT , %Round(E.CALC_RSLT_VAL ,2) , E.BASE_RSLT_VAL , E.RATE_RSLT_VAL , E.UNIT_RSLT_VAL , E.PCT_RSLT_VAL , E.RSLT_ADD_ARR , E.RSLT_NOT_TKN , E.RSLT_PAYBK , E.RSLT_XFER_VAL , E.BASE_ADJ_VAL , E.CALC_ADJ_VAL , E.UNIT_ADJ_VAL , E.USER_ADJ_VAL , E.USER_KEY1 , E.USER_KEY2 , E.USER_KEY3 , E.USER_KEY4 , E.USER_KEY5 , E.USER_KEY6 , E.PIN_TYPE , E.PIN_NM , E.PIN_CODE , E.PIN_CUSTOM1 FROM PS_GPTH_RPT_G_VW D LEFT OUTER JOIN PS_GPTH_COM_PHN_VW B ON (B.COMPANY = D.COMPANY AND B.PROCESS_INSTANCE=D.PROCESS_INSTANCE AND B.EMPLID=D.EMPLID) LEFT OUTER JOIN PS_GPTH_COM_FX_VW C ON (C.COMPANY = D.COMPANY AND C.PROCESS_INSTANCE=D.PROCESS_INSTANCE AND C.EMPLID=D.EMPLID) LEFT OUTER JOIN PS_GPTH_SI_COMP_VW F ON (D.COMPANY=F.COMPANY AND F.PROCESS_INSTANCE=D.PROCESS_INSTANCE AND F.EMPLID=D.EMPLID) LEFT OUTER JOIN PS_GPTH_TX_BRN_VW1 G ON (D.COMPANY=G.COMPANY AND G.PROCESS_INSTANCE=D.PROCESS_INSTANCE AND G.EMPLID=D.EMPLID) LEFT OUTER JOIN PS_GPTH_COMP_VW1 A ON ( D.COMPANY = A.COMPANY AND A.PROCESS_INSTANCE=D.PROCESS_INSTANCE AND A.EMPLID=D.EMPLID) LEFT OUTER JOIN PS_GPTH_SI_PYE_VW H ON (D.EMPLID=H.EMPLID AND H.PROCESS_INSTANCE=D.PROCESS_INSTANCE) ,PS_GPTH_RPT_D_VW E WHERE D.PROCESS_INSTANCE = E.PROCESS_INSTANCE AND D.EMPLID = E.EMPLID AND D.CAL_RUN_ID = E.CAL_RUN_ID AND D.EMPL_RCD = E.EMPL_RCD AND D.GP_PAYGROUP = E.GP_PAYGROUP AND D.CAL_ID = E.CAL_ID AND D.ORIG_CAL_RUN_ID = E.ORIG_CAL_RUN_ID AND D.RSLT_SEG_NUM = E.RSLT_SEG_NUM AND D.GP_RPT_KEY = E.GP_RPT_KEY |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | CAL_RUN_ID |
Character(18) | VARCHAR2(18) NOT NULL | Calendar Run Id |
| 3 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 4 | GP_PAYGROUP |
Character(10) | VARCHAR2(10) NOT NULL | Global Payroll pay group |
| 5 | CAL_ID |
Character(18) | VARCHAR2(18) NOT NULL | calendar id |
| 6 | ORIG_CAL_RUN_ID |
Character(18) | VARCHAR2(18) NOT NULL | Original Calendar Group ID |
| 7 | RSLT_SEG_NUM |
Number(4,0) | SMALLINT NOT NULL | Result Segment Number |
| 8 | GP_RPT_KEY |
Character(22) | VARCHAR2(22) NOT NULL | Output Result Key |
| 9 | PHONE_TYPE |
Character(4) | VARCHAR2(4) NOT NULL |
Phone Type
BUSN=Business CAMP=Campus CELL=Mobile DORM=Dormitory FAX=FAX HOME=Home MAIN=Main OTR=Other PGR1=Pager 1 PGR2=Pager 2 TELX=Telex WORK=Work |
| 10 | PIN_NUM |
Number(8,0) | INTEGER NOT NULL | PIN Number |
| 11 | INSTANCE | Number(3,0) | SMALLINT NOT NULL | Instance Number |
| 12 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 13 | ADDRESS1 | Character(55) | VARCHAR2(55) NOT NULL | Address 1 |
| 14 | ADDRESS2 | Character(55) | VARCHAR2(55) NOT NULL | Address 2 |
| 15 | ADDRESS3 | Character(55) | VARCHAR2(55) NOT NULL | Address 3 |
| 16 | ADDRESS4 | Character(55) | VARCHAR2(55) NOT NULL | Address 4 |
| 17 | CITY | Character(30) | VARCHAR2(30) NOT NULL | City |
| 18 | COUNTY | Character(30) | VARCHAR2(30) NOT NULL | County |
| 19 | COUNTRY2 | Character(3) | VARCHAR2(3) NOT NULL | Country |
| 20 | STATE | Character(6) | VARCHAR2(6) NOT NULL |
State
Prompt Table: STATE_TBL |
| 21 | POSTAL | Character(12) | VARCHAR2(12) NOT NULL | Postal |
| 22 | COUNTRY_CODE | Character(3) | VARCHAR2(3) NOT NULL | Country Code |
| 23 | PHONE | Character(24) | VARCHAR2(24) NOT NULL | Telephone |
| 24 | GPTH_FAX_TYPE | Character(4) | VARCHAR2(4) NOT NULL | Phone Type |
| 25 | FAX | Character(24) | VARCHAR2(24) NOT NULL | Fax Number |
| 26 | PROCESS_INSTANCE | Number(10,0) | DECIMAL(10) NOT NULL | Process Instance |
| 27 | OFF_CYCLE | Character(1) | VARCHAR2(1) NOT NULL | Off Cycle ? |
| 28 | PRD_BGN_DT | Date(10) | DATE | Period Begin Date |
| 29 | PRD_END_DT | Date(10) | DATE | Pay Period End Date |
| 30 | SEG_BGN_DT | Date(10) | DATE | Payee Process Segment's Begin Date |
| 31 | SEG_END_DT | Date(10) | DATE | Payee Process Segment's End Date |
| 32 | PYMT_DT | Date(10) | DATE | Payment Date |
| 33 | CAL_PRD_ID | Character(10) | VARCHAR2(10) NOT NULL | Calendar Period ID |
| 34 | COUNTRY | Character(3) | VARCHAR2(3) NOT NULL | Country |
| 35 | PIN_GROSS_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Gross Pay Value |
| 36 | PIN_NET_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Net Pay Value |
| 37 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 38 | CUR_RT_TYPE | Character(5) | VARCHAR2(5) NOT NULL | Currency Rate Type |
| 39 | RUN_TYPE | Character(10) | VARCHAR2(10) NOT NULL | Run Type Name |
| 40 | PAY_ENTITY | Character(10) | VARCHAR2(10) NOT NULL | Pay Entity |
| 41 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 42 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 43 | ESTABID | Character(12) | VARCHAR2(12) NOT NULL | Establishment ID |
| 44 | SETID_DEPT | Character(5) | VARCHAR2(5) NOT NULL | Department Set ID |
| 45 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 46 | SETID_LOCATION | Character(5) | VARCHAR2(5) NOT NULL | Location Set ID |
| 47 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 48 | GPTH_GOV | Character(1) | VARCHAR2(1) NOT NULL | This field represents that if the report is an Government report |
| 49 | GPTH_DETAIL_PAGE | Character(1) | VARCHAR2(1) NOT NULL | This field represents that if the report need a detail page |
| 50 | GPTH_DOWN_TO_TXT | Character(1) | VARCHAR2(1) NOT NULL | This field represents that if the report need a Data Media File |
| 51 | GPTH_TAX_ID | Character(10) | VARCHAR2(10) NOT NULL | Tax identification number |
| 52 | GPTH_TAX_BRANCH | Character(6) | VARCHAR2(6) NOT NULL | Tax branch number |
| 53 | GPTH_TAX_ID_L | Character(13) | VARCHAR2(13) NOT NULL | Tax identification number 13 |
| 54 | GPTH_SI_ER_PER | Number(5,2) | DECIMAL(4,2) NOT NULL | Employer Contribution Rate |
| 55 | GPTH_SI_ACCT | Character(10) | VARCHAR2(10) NOT NULL | This field will be used to store company social security account information. |
| 56 | GPTH_SI_BRANCH_NO | Character(6) | VARCHAR2(6) NOT NULL | Social Security Branch Number |
| 57 | ACM_FROM_DT | Date(10) | DATE | From Date |
| 58 | ACM_THRU_DT | Date(10) | DATE | Through Date |
| 59 | SLICE_BGN_DT | Date(10) | DATE | Element Slice Begin Date |
| 60 | SLICE_END_DT | Date(10) | DATE | Element Slice End Date |
| 61 | CALC_RSLT_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Calculation Numeric Result |
| 62 | BASE_RSLT_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Calculated Base Value |
| 63 | RATE_RSLT_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Rate Result Value |
| 64 | UNIT_RSLT_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Number of units used in result calculation |
| 65 | PCT_RSLT_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Percent Result Value |
| 66 | RSLT_ADD_ARR | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Amount Added To Arrears |
| 67 | RSLT_NOT_TKN | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Amount Not Taken |
| 68 | RSLT_PAYBK | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Payback Amount Result |
| 69 | RSLT_XFER_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Bank Transfer Value of Deduct |
| 70 | BASE_ADJ_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Base Adjustment |
| 71 | CALC_ADJ_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Calculation Adjustment Value |
| 72 | UNIT_ADJ_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Unit Adjustment |
| 73 | USER_ADJ_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | User Adjustment Value |
| 74 | USER_KEY1 | Character(25) | VARCHAR2(25) NOT NULL | User Key 1 |
| 75 | USER_KEY2 | Character(25) | VARCHAR2(25) NOT NULL | User Key 2 |
| 76 | USER_KEY3 | Character(25) | VARCHAR2(25) NOT NULL | User Key 3 |
| 77 | USER_KEY4 | Character(25) | VARCHAR2(25) NOT NULL | User Key 4 |
| 78 | USER_KEY5 | Character(25) | VARCHAR2(25) NOT NULL | User Key 5 |
| 79 | USER_KEY6 | Character(25) | VARCHAR2(25) NOT NULL | User Key 6 |
| 80 | PIN_TYPE | Character(2) | VARCHAR2(2) NOT NULL | Element Type |
| 81 | PIN_NM | Character(18) | VARCHAR2(18) NOT NULL | Element Name field |
| 82 | PIN_CODE | Character(22) | VARCHAR2(22) NOT NULL | Unique identifier of a PIN (alternate to PIN number). critical for use in merging packages of elements from one database onto another when PINs may or may not be renumbered as they are moved across. Constructed from PIN_NM + COUNTRY of the element definition (GP_PIN) |
| 83 | PIN_CUSTOM1 | Character(20) | VARCHAR2(20) NOT NULL | Customer FIelds |