RS_ED_RATES(SQL View) |
Index Back |
|---|---|
ETL Actual RatesActual Billed & Cost Rates view for Endeca ESA Project Discovery ETL process. |
| SELECT R.EMPLID , R.BUSINESS_UNIT , R.PROJECT_ID , R.PROJ_DESCR , R.PC_ACTUAL_HRS , R.ACTUAL_AMOUNT , R.BILLED_TO_DATE , CASE WHEN R.PC_ACTUAL_HRS > 0 THEN %Round(%DecDiv(R.BILLED_TO_DATE, R.PC_ACTUAL_HRS), 2) ELSE 0 END AS BILL_RATE_ACT , CASE WHEN R.PC_ACTUAL_HRS > 0 THEN %Round(%DecDiv(R.ACTUAL_AMOUNT, R.PC_ACTUAL_HRS), 2) ELSE 0 END AS COST_RATE , CASE WHEN (R.PC_ACTUAL_HRS > 0 AND R.ACTUAL_AMOUNT > 0) THEN %Round(%DecDiv((%DecDiv(R.BILLED_TO_DATE, R.PC_ACTUAL_HRS)) - (%DecDiv(R.ACTUAL_AMOUNT, R.PC_ACTUAL_HRS)), (%DecDiv(R.ACTUAL_AMOUNT, R.PC_ACTUAL_HRS))), 3) ELSE 0 END AS MARGIN FROM (%Sql(RS_ED_RATES_SEL)) R |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 3 | PROJECT_ID | Character(15) | VARCHAR2(15) NOT NULL | Project Id ChartField |
| 4 | PROJ_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Project ID Description |
| 5 | PC_ACTUAL_HRS | Signed Number(16,2) | DECIMAL(14,2) NOT NULL | Actual Hours |
| 6 | ACTUAL_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Actual Invoice Amount |
| 7 | BILLED_TO_DATE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Billed To Date |
| 8 | BILL_RATE_ACT | Number(27,3) | DECIMAL(26,3) NOT NULL | Bill Rate in Activity team (ARL). |
| 9 | COST_RATE | Number(27,3) | DECIMAL(26,3) NOT NULL | Cost Rate |
| 10 | MARGIN | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Margin |