OI_SUBCA_EFF_VW

(SQL View)
 Index Back

Subcategory

Subcategory with Effective Start Date & End Date

SELECT SETID , RESOURCE_SUB_CAT , EFFDT AS EFF_ST_DT , CASE WHEN LEAD(CONCAT(SETID ,RESOURCE_SUB_CAT) ,1 ,NULL) OVER ( ORDER BY SETID ,RESOURCE_SUB_CAT ,EFFDT) <> CONCAT (SETID ,RESOURCE_SUB_CAT) THEN TO_DATE('9999-12-31' , 'YYYY-MM-DD') ELSE CASE WHEN LEAD(EFFDT ,1 ,NULL) OVER ( ORDER BY SETID ,RESOURCE_SUB_CAT ,EFFDT) = EFFDT THEN EFFDT ELSE LEAD(EFFDT-1 ,1 ,TO_DATE('9999-12-31' , 'YYYY-MM-DD')) OVER ( ORDER BY SETID ,RESOURCE_SUB_CAT ,EFFDT) END END AS EFF_END_DT FROM PS_PROJ_SUBCAT_TBL ORDER BY 1 , 2 , 3

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 RESOURCE_SUB_CAT Character(5) VARCHAR2(5) NOT NULL Subcategory
3 EFF_ST_DT Date(10) DATE Effective Start Date
4 EFF_END_DT Date(10) DATE Effective End Date