1. Tables and Views for Project Management
  2. PJC_LABOR_COSTS_ERROR_PERIOD_V

PJC_LABOR_COSTS_ERROR_PERIOD_V

Details

  • Schema: FUSION

  • Object owner: PJC

  • Object type: VIEW

Columns

Name

ROW_KEY

ERROR_COUNT

ERROR_AMOUNT

PAY_PERIOD_START

BU_ID

BUSINESS_UNIT_NAME

Query

SQL_Statement

SELECT ROWNUM AS ROW_KEY,

ERROR_COUNT,

ERROR_AMOUNT,

PAY_PERIOD_START,

BU_ID,

BUSINESS_UNIT_NAME

FROM(

SELECT ROW_NUMBER() OVER (PARTITION BY ProjectAllocTxnPEO.ORG_ID ORDER BY ProjectAllocTxnPEO.LD_PERIOD_START_DATE DESC) AS ROW_NUM,

SUM(ProjectAllocTxnPEO.TOTAL_POOL_AMOUNT) AS ERROR_AMOUNT,

COUNT(DISTINCT ProjectAllocTxnPEO.LD_INTERFACE_ID) AS ERROR_COUNT,

ProjectAllocTxnPEO.LD_PERIOD_START_DATE AS PAY_PERIOD_START,

ProjectAllocTxnPEO.ORG_ID AS BU_ID,

Organizations.NAME AS BUSINESS_UNIT_NAME

FROM PJC_ALLOC_RUNS_ALL ProjectAllocTxnPEO,

HR_ORGANIZATION_UNITS_F_TL Organizations

WHERE ProjectAllocTxnPEO.ORG_ID = Organizations.ORGANIZATION_ID

AND Organizations.LANGUAGE = USERENV('LANG')

AND TRUNC(SYSDATE) BETWEEN Organizations.EFFECTIVE_START_DATE AND Organizations.EFFECTIVE_END_DATE

AND ProjectAllocTxnPEO.RUN_STATUS = 'RF'

AND ProjectAllocTxnPEO.LD_ORIG_TRANSACTION_REFERENCE IS NOT NULL

AND LD_PERIOD_START_DATE IS NOT NULL

GROUP BY ProjectAllocTxnPEO.LD_PERIOD_START_DATE, ProjectAllocTxnPEO.ORG_ID, Organizations.NAME

ORDER BY Organizations.NAME ASC, ProjectAllocTxnPEO.LD_PERIOD_START_DATE DESC

) WHERE ROW_NUM <=3