VIEW: APPS.WIP_BIS_PRODUCTIVITIES_V

Source

View Text - Preformatted

SELECT WBPI.ORGANIZATION_ID , WBPI.ORGANIZATION_NAME , WBPI.LEGAL_ENTITY_ID LEGAL_ENTITY , WBPI.TRANSACTION_DATE , OAP.PERIOD_SET_NAME , OAP.PERIOD_NAME , OAP.PERIOD_START_DATE , OAP.SCHEDULE_CLOSE_DATE PERIOD_END_DATE , WBPI.DEPARTMENT_CODE , WBPI.RESOURCE_CODE , SUM(NVL(WBPI.APPLIED_HOURS_PRD, 0)) "APPLIED_HOURS" , SUM(NVL(WBPI.STANDARD_HOURS, 0)) "STANDARD_HOURS" , DECODE(SUM( NVL(WBPI.APPLIED_HOURS_PRD, 0)), 0, 0, DECODE(SUM(NVL(WBPI.STANDARD_HOURS, 0)), 0, 0, SUM(NVL(WBPI.STANDARD_HOURS, 0)) / SUM(NVL(WBPI.APPLIED_HOURS_PRD, 0)))) * 100 "PRODUCTIVITY_PERCENT" FROM ORG_ACCT_PERIODS OAP, WIP_BIS_PROD_INDICATORS WBPI WHERE WBPI.EXISTING_FLAG = 1 AND WBPI.STANDARD_HOURS IS NOT NULL AND OAP.ORGANIZATION_ID = WBPI.ORGANIZATION_ID AND WBPI.TRANSACTION_DATE BETWEEN OAP.PERIOD_START_DATE AND OAP.SCHEDULE_CLOSE_DATE GROUP BY WBPI.LEGAL_ENTITY_ID, WBPI.ORGANIZATION_ID, WBPI.ORGANIZATION_NAME, WBPI.TRANSACTION_DATE, OAP.PERIOD_SET_NAME, OAP.PERIOD_NAME, OAP.PERIOD_START_DATE, OAP.SCHEDULE_CLOSE_DATE, WBPI.DEPARTMENT_CODE , WBPI.RESOURCE_CODE

View Text - HTML Formatted

SELECT WBPI.ORGANIZATION_ID
, WBPI.ORGANIZATION_NAME
, WBPI.LEGAL_ENTITY_ID LEGAL_ENTITY
, WBPI.TRANSACTION_DATE
, OAP.PERIOD_SET_NAME
, OAP.PERIOD_NAME
, OAP.PERIOD_START_DATE
, OAP.SCHEDULE_CLOSE_DATE PERIOD_END_DATE
, WBPI.DEPARTMENT_CODE
, WBPI.RESOURCE_CODE
, SUM(NVL(WBPI.APPLIED_HOURS_PRD
, 0)) "APPLIED_HOURS"
, SUM(NVL(WBPI.STANDARD_HOURS
, 0)) "STANDARD_HOURS"
, DECODE(SUM( NVL(WBPI.APPLIED_HOURS_PRD
, 0))
, 0
, 0
, DECODE(SUM(NVL(WBPI.STANDARD_HOURS
, 0))
, 0
, 0
, SUM(NVL(WBPI.STANDARD_HOURS
, 0)) / SUM(NVL(WBPI.APPLIED_HOURS_PRD
, 0)))) * 100 "PRODUCTIVITY_PERCENT"
FROM ORG_ACCT_PERIODS OAP
, WIP_BIS_PROD_INDICATORS WBPI
WHERE WBPI.EXISTING_FLAG = 1
AND WBPI.STANDARD_HOURS IS NOT NULL
AND OAP.ORGANIZATION_ID = WBPI.ORGANIZATION_ID
AND WBPI.TRANSACTION_DATE BETWEEN OAP.PERIOD_START_DATE
AND OAP.SCHEDULE_CLOSE_DATE GROUP BY WBPI.LEGAL_ENTITY_ID
, WBPI.ORGANIZATION_ID
, WBPI.ORGANIZATION_NAME
, WBPI.TRANSACTION_DATE
, OAP.PERIOD_SET_NAME
, OAP.PERIOD_NAME
, OAP.PERIOD_START_DATE
, OAP.SCHEDULE_CLOSE_DATE
, WBPI.DEPARTMENT_CODE
, WBPI.RESOURCE_CODE