EMPLIDEMPL_RCDEFFDTASOFDATECOMPONENTPrompt Table: PKG_BSE_CMP_TBL
ADD_COMPONENTPrompt Table: PKG_ADD_CMP_TBL
PKG_EXP_PTPV_VW(SQL View) |
Index Back |
|---|---|
Pkg Expense TPV ViewSalary Packaging - Expense TPV View |
| SELECT P.EMPLID , P.EMPL_RCD , P.EFFDT , P.ASOFDATE , E.COMPONENT , E.ADD_COMPONENT , P.PERIOD_START , SUM(E.BUDGET_AMT) , SUM(E.EXPENSE_AMT) , SUM(E.TAXABLE_AMT) , MIN(E.REMAINING_BUDGET) FROM PS_PKG_EXP_TBL P , PS_PKG_EXP_PER_TBL E WHERE E.EMPLID = P.EMPLID AND E.EMPL_RCD = P.EMPL_RCD AND E.EFFDT = P.EFFDT AND E.PERIOD_END BETWEEN P.PERIOD_START AND P.ASOFDATE AND ((E.ADD_COMPONENT=' ' AND E.COMPONENT= ( SELECT B.COMPONENT FROM PS_PKG_BSE_CMP_TBL B WHERE B.COMPONENT = E.COMPONENT AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_PKG_BSE_CMP_TBL B1 WHERE B1.COMPONENT = B.COMPONENT) AND B.PACKAGE_LEVEL = 'TPV')) OR (E.ADD_COMPONENT <> ' ' AND E.ADD_COMPONENT = ( SELECT A.ADD_COMPONENT FROM PS_PKG_ADD_CMP_TBL A WHERE A.ADD_COMPONENT = E.ADD_COMPONENT AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_PKG_ADD_CMP_TBL A1 WHERE A1.ADD_COMPONENT = A.ADD_COMPONENT) AND A.PACKAGE_LEVEL = 'TPV'))) GROUP BY P.EMPLID, P.EMPL_RCD, P.EFFDT, P.ASOFDATE, E.COMPONENT, E.ADD_COMPONENT, P.PERIOD_START |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Rcd Nbr |
| 3 | EFFDT |
Date(10) | DATE | Effective Date |
| 4 | ASOFDATE |
Date(10) | DATE NOT NULL | As of Date |
| 5 | COMPONENT |
Character(8) | VARCHAR2(8) NOT NULL |
Sal package - Package Component
Prompt Table: PKG_BSE_CMP_TBL |
| 6 | ADD_COMPONENT |
Character(8) | VARCHAR2(8) NOT NULL |
Sal Package - Additional Component
Prompt Table: PKG_ADD_CMP_TBL |
| 7 | PERIOD_START | Date(10) | DATE NOT NULL | Sal package - Period Start Date |
| 8 | BUDGET_AMT | Number(19,3) | DECIMAL(18,3) NOT NULL | Budget Amount |
| 9 | EXPENSE_AMT | Signed Number(20,3) | DECIMAL(18,3) NOT NULL | Expense Amount |
| 10 | TAXABLE_AMT | Signed Number(17,2) | DECIMAL(15,2) NOT NULL | Sal Package - Balance Total Employment Cost |
| 11 | REMAINING_BUDGET | Signed Number(17,2) | DECIMAL(15,2) NOT NULL | Sal Package - Remaining Budget |