SELECT DISTINCT ALHRV.CHILD_ORDER_NUMBER AS SEQ_NUM , ALHRV.CHILD_PROCESS_ID AS PRC_ID , ALHRV.CHILD_PROCESS_CODE AS PRC_CODE , ALHRV.CHILD_DISPLAY_NAME AS PRC_NAME , (SELECT MEANING FROM AMW_LOOKUPS AL WHERE AL.LOOKUP_TYPE='AMW_REVISE_PROCESS_FLAG' AND AL.LOOKUP_CODE='R' AND AL.ENABLED_FLAG='Y' AND NVL(AL.END_DATE_ACTIVE , SYSDATE) >= SYSDATE) AS REVISE , APHD.PROCESS_ID AS PRNT_ID , ALHRV.PARENT_PROCESS_ID AS IMM_PRNT_ID , ALHRV.PARENT_PROCESS_CODE AS PRNT_CODE , ALHRV.PARENT_DISPLAY_NAME AS PRNT_NAME , (SELECT MEANING FROM AMW_LOOKUPS AL WHERE AL.LOOKUP_TYPE='AMW_PROCESS_APPROVAL_STATUS' AND AL.LOOKUP_CODE=ALHRV.CHILD_APPROVAL_STATUS AND AL.ENABLED_FLAG='Y' AND NVL(AL.END_DATE_ACTIVE , SYSDATE) >= SYSDATE) AS PRC_STS , (SELECT MEANING FROM AMW_LOOKUPS AL WHERE AL.LOOKUP_TYPE='AMW_PROCESS_TYPE' AND AL.LOOKUP_CODE=(SELECT PROCESS_TYPE FROM AMW_PROCESS WHERE PROCESS_ID=ALHRV.CHILD_PROCESS_ID AND END_DATE IS NULL AND DELETION_DATE IS NULL) AND AL.ENABLED_FLAG='Y' AND NVL(AL.END_DATE_ACTIVE , SYSDATE) >= SYSDATE) AS PRC_TYPE , (SELECT MEANING FROM AMW_LOOKUPS AL WHERE AL.LOOKUP_TYPE='AMW_PROCESS_CATEGORY' AND AL.LOOKUP_CODE=ALHRV.CHILD_PROCESS_CATEGORY AND AL.ENABLED_FLAG='Y' AND NVL(AL.END_DATE_ACTIVE , SYSDATE) >= SYSDATE) AS PRC_CAT , (SELECT MEANING FROM AMW_LOOKUPS AL WHERE AL.LOOKUP_TYPE='AMW_SIGNIFICANT_PROCESS' AND AL.LOOKUP_CODE=ALHRV.CHILD_SIGNIFICANT_PROCESS_FLAG AND AL.ENABLED_FLAG='Y' AND NVL(AL.END_DATE_ACTIVE , SYSDATE) >= SYSDATE) AS PRC_SIG , (SELECT MEANING FROM AMW_LOOKUPS AL WHERE AL.LOOKUP_TYPE='AMW_STANDARD_PROCESS' AND AL.LOOKUP_CODE=ALHRV.CHILD_STANDARD_PROCESS_FLAG AND AL.ENABLED_FLAG='Y' AND NVL(AL.END_DATE_ACTIVE , SYSDATE) >= SYSDATE) AS PRC_STD , (SELECT MEANING FROM AMW_LOOKUPS AL WHERE AL.LOOKUP_TYPE='AMW_CONTROL_ACTIVITY_TYPE' AND AL.LOOKUP_CODE=(SELECT CONTROL_ACTIVITY_TYPE FROM AMW_PROCESS WHERE PROCESS_ID=ALHRV.CHILD_PROCESS_ID AND END_DATE IS NULL AND DELETION_DATE IS NULL) AND AL.ENABLED_FLAG='Y' AND NVL(AL.END_DATE_ACTIVE , SYSDATE) >= SYSDATE) AS PRC_CTRL_ACT , ATTCH.FILE_NAME AS ATT_URL , (SELECT PARTY_NAME FROM (SELECT PARTY_NAME , PK1_VALUE FROM AMW_OWNER_ROLES_V WHERE ROLE_NAME=NVL(FND_PROFILE.VALUE ('AMW_PROC_IMP_PROC_OWNER_COL') , 'AMW_RL_PROC_OWNER_ROLE') ORDER BY PK1_VALUE DESC , START_DATE ASC) WHERE PK1_VALUE=TO_CHAR(ALHRV.CHILD_PROCESS_ID) AND ROWNUM<=1) AS PROC_OWN , (SELECT PARTY_NAME FROM (SELECT PARTY_NAME , PK1_VALUE FROM AMW_OWNER_ROLES_V WHERE ROLE_NAME=NVL(FND_PROFILE.VALUE ('AMW_PROC_IMP_APPL_OWNER_COL') , 'AMW_RL_PROC_APPL_OWNER_ROLE') ORDER BY PK1_VALUE DESC , START_DATE ASC) WHERE PK1_VALUE=TO_CHAR(ALHRV.CHILD_PROCESS_ID) AND ROWNUM<=1) AS APPL_OWN , (SELECT PARTY_NAME FROM (SELECT PARTY_NAME , PK1_VALUE FROM AMW_OWNER_ROLES_V WHERE ROLE_NAME=NVL(FND_PROFILE.VALUE ('AMW_PROC_IMP_FINANCE_OWNER_COL') , 'AMW_RL_PROC_FINANCE_OWNER_ROLE') ORDER BY PK1_VALUE DESC , START_DATE ASC) WHERE PK1_VALUE=TO_CHAR(ALHRV.CHILD_PROCESS_ID) AND ROWNUM<=1) AS FIN_OWN , -100 AS PARTY_ID FROM AMW_LATEST_HIERARCHY_RL_V ALHRV , AMW_PROC_HIERARCHY_DENORM APHD , (SELECT FAD.SEQ_NUM , FAD.DOCUMENT_ID , FAD.COLUMN1 , FAD.AUTOMATICALLY_ADDED_FLAG , FAD.CATEGORY_ID ATT_CAT , FAD.PK1_VALUE , FAD.PK2_VALUE , FAD.PK3_VALUE , FAD.PK4_VALUE , FAD.PK5_VALUE , FD.DATATYPE_ID , FD.CATEGORY_ID , FD.SECURITY_TYPE , FD.SECURITY_ID , FD.PUBLISH_FLAG , FD.IMAGE_TYPE , FD.STORAGE_TYPE , FD.USAGE_TYPE , FD.START_DATE_ACTIVE , FD.END_DATE_ACTIVE , FDTL.LANGUAGE , FDTL.DESCRIPTION , FDTL.FILE_NAME , FDTL.MEDIA_ID FROM FND_ATTACHED_DOCUMENTS FAD , FND_DOCUMENTS FD , FND_DOCUMENTS_TL FDTL WHERE FAD.DOCUMENT_ID = FD.DOCUMENT_ID AND FD.DOCUMENT_ID = FDTL.DOCUMENT_ID AND FDTL.LANGUAGE = USERENV('LANG') AND FAD.ENTITY_NAME = 'AMW_PROCESS' AND FDTL.MEDIA_ID IS NULL) ATTCH WHERE APHD.UP_DOWN_IND='D' AND APHD.HIERARCHY_TYPE='L' AND APHD.PARENT_CHILD_ID=ALHRV.CHILD_PROCESS_ID AND ALHRV.PARENT_PROCESS_ID IN (SELECT PARENT_CHILD_ID FROM AMW_PROC_HIERARCHY_DENORM WHERE UP_DOWN_IND='D' AND HIERARCHY_TYPE='L' AND PROCESS_ID=APHD.PROCESS_ID UNION SELECT APHD.PROCESS_ID FROM DUAL) AND ALHRV.CHILD_APPROVAL_STATUS NOT IN ('P' , 'R') AND ALHRV.CHILD_PROCESS_REV_ID=ATTCH.PK1_VALUE(+) AND ALHRV.CHILD_PROCESS_ID <> -1 UNION ALL SELECT DISTINCT ALHRV.CHILD_ORDER_NUMBER AS SEQ_NUM , ALHRV.CHILD_PROCESS_ID AS PRC_ID , ALHRV.CHILD_PROCESS_CODE AS PRC_CODE , ALHRV.CHILD_DISPLAY_NAME AS PRC_NAME , (SELECT MEANING FROM AMW_LOOKUPS AL WHERE AL.LOOKUP_TYPE='AMW_REVISE_PROCESS_FLAG' AND AL.LOOKUP_CODE='R' AND AL.ENABLED_FLAG='Y' AND NVL(AL.END_DATE_ACTIVE , SYSDATE) >= SYSDATE) AS REVISE , APHD.PROCESS_ID AS PRNT_ID , ALHRV.PARENT_PROCESS_ID AS IMM_PRNT_ID , ALHRV.PARENT_PROCESS_CODE AS PRNT_CODE , ALHRV.PARENT_DISPLAY_NAME AS PRNT_NAME , (SELECT MEANING FROM AMW_LOOKUPS AL WHERE AL.LOOKUP_TYPE='AMW_PROCESS_APPROVAL_STATUS' AND AL.LOOKUP_CODE=ALHRV.CHILD_APPROVAL_STATUS AND AL.ENABLED_FLAG='Y' AND NVL(AL.END_DATE_ACTIVE , SYSDATE) >= SYSDATE) AS PRC_STS , (SELECT MEANING FROM AMW_LOOKUPS AL WHERE AL.LOOKUP_TYPE='AMW_PROCESS_TYPE' AND AL.LOOKUP_CODE=(SELECT PROCESS_TYPE FROM AMW_PROCESS WHERE PROCESS_ID=ALHRV.CHILD_PROCESS_ID AND END_DATE IS NULL AND DELETION_DATE IS NULL) AND AL.ENABLED_FLAG='Y' AND NVL(AL.END_DATE_ACTIVE , SYSDATE) >= SYSDATE) AS PRC_TYPE , (SELECT MEANING FROM AMW_LOOKUPS AL WHERE AL.LOOKUP_TYPE='AMW_PROCESS_CATEGORY' AND AL.LOOKUP_CODE=ALHRV.CHILD_PROCESS_CATEGORY AND AL.ENABLED_FLAG='Y' AND NVL(AL.END_DATE_ACTIVE , SYSDATE) >= SYSDATE) AS PRC_CAT , (SELECT MEANING FROM AMW_LOOKUPS AL WHERE AL.LOOKUP_TYPE='AMW_SIGNIFICANT_PROCESS' AND AL.LOOKUP_CODE=ALHRV.CHILD_SIGNIFICANT_PROCESS_FLAG AND AL.ENABLED_FLAG='Y' AND NVL(AL.END_DATE_ACTIVE , SYSDATE) >= SYSDATE) AS PRC_SIG , (SELECT MEANING FROM AMW_LOOKUPS AL WHERE AL.LOOKUP_TYPE='AMW_STANDARD_PROCESS' AND AL.LOOKUP_CODE=ALHRV.CHILD_STANDARD_PROCESS_FLAG AND AL.ENABLED_FLAG='Y' AND NVL(AL.END_DATE_ACTIVE , SYSDATE) >= SYSDATE) AS PRC_STD , (SELECT MEANING FROM AMW_LOOKUPS AL WHERE AL.LOOKUP_TYPE='AMW_CONTROL_ACTIVITY_TYPE' AND AL.LOOKUP_CODE=(SELECT CONTROL_ACTIVITY_TYPE FROM AMW_PROCESS WHERE PROCESS_ID=ALHRV.CHILD_PROCESS_ID AND END_DATE IS NULL AND DELETION_DATE IS NULL) AND AL.ENABLED_FLAG='Y' AND NVL(AL.END_DATE_ACTIVE , SYSDATE) >= SYSDATE) AS PRC_CTRL_ACT , ATTCH.FILE_NAME AS ATT_URL , (SELECT PARTY_NAME FROM (SELECT PARTY_NAME , PK1_VALUE FROM AMW_OWNER_ROLES_V WHERE ROLE_NAME=NVL(FND_PROFILE.VALUE ('AMW_PROC_IMP_PROC_OWNER_COL') , 'AMW_RL_PROC_OWNER_ROLE') ORDER BY PK1_VALUE DESC , START_DATE ASC) WHERE PK1_VALUE=TO_CHAR(ALHRV.CHILD_PROCESS_ID) AND ROWNUM<=1) AS PROC_OWN , (SELECT PARTY_NAME FROM (SELECT PARTY_NAME , PK1_VALUE FROM AMW_OWNER_ROLES_V WHERE ROLE_NAME=NVL(FND_PROFILE.VALUE ('AMW_PROC_IMP_APPL_OWNER_COL') , 'AMW_RL_PROC_APPL_OWNER_ROLE') ORDER BY PK1_VALUE DESC , START_DATE ASC) WHERE PK1_VALUE=TO_CHAR(ALHRV.CHILD_PROCESS_ID) AND ROWNUM<=1) AS APPL_OWN , (SELECT PARTY_NAME FROM (SELECT PARTY_NAME , PK1_VALUE FROM AMW_OWNER_ROLES_V WHERE ROLE_NAME=NVL(FND_PROFILE.VALUE ('AMW_PROC_IMP_FINANCE_OWNER_COL') , 'AMW_RL_PROC_FINANCE_OWNER_ROLE') ORDER BY PK1_VALUE DESC , START_DATE ASC) WHERE PK1_VALUE=TO_CHAR(ALHRV.CHILD_PROCESS_ID) AND ROWNUM<=1) AS FIN_OWN , AORV.PARTY_ID AS PARTY_ID FROM AMW_LATEST_HIERARCHY_RL_V ALHRV , AMW_PROC_HIERARCHY_DENORM APHD , (SELECT FAD.SEQ_NUM , FAD.DOCUMENT_ID , FAD.COLUMN1 , FAD.AUTOMATICALLY_ADDED_FLAG , FAD.CATEGORY_ID ATT_CAT , FAD.PK1_VALUE , FAD.PK2_VALUE , FAD.PK3_VALUE , FAD.PK4_VALUE , FAD.PK5_VALUE , FD.DATATYPE_ID , FD.CATEGORY_ID , FD.SECURITY_TYPE , FD.SECURITY_ID , FD.PUBLISH_FLAG , FD.IMAGE_TYPE , FD.STORAGE_TYPE , FD.USAGE_TYPE , FD.START_DATE_ACTIVE , FD.END_DATE_ACTIVE , FDTL.LANGUAGE , FDTL.DESCRIPTION , FDTL.FILE_NAME , FDTL.MEDIA_ID FROM FND_ATTACHED_DOCUMENTS FAD , FND_DOCUMENTS FD , FND_DOCUMENTS_TL FDTL WHERE FAD.DOCUMENT_ID = FD.DOCUMENT_ID AND FD.DOCUMENT_ID = FDTL.DOCUMENT_ID AND FDTL.LANGUAGE = USERENV('LANG') AND FAD.ENTITY_NAME = 'AMW_PROCESS' AND FDTL.MEDIA_ID IS NULL) ATTCH , AMW_OWNER_ROLES_V AORV WHERE APHD.UP_DOWN_IND='D' AND APHD.HIERARCHY_TYPE='L' AND APHD.PARENT_CHILD_ID=ALHRV.CHILD_PROCESS_ID AND ALHRV.PARENT_PROCESS_ID IN (SELECT PARENT_CHILD_ID FROM AMW_PROC_HIERARCHY_DENORM WHERE UP_DOWN_IND='D' AND HIERARCHY_TYPE='L' AND PROCESS_ID=APHD.PROCESS_ID UNION SELECT APHD.PROCESS_ID FROM DUAL) AND ALHRV.CHILD_APPROVAL_STATUS NOT IN ('P' , 'R') AND ALHRV.CHILD_PROCESS_REV_ID=ATTCH.PK1_VALUE(+) AND ALHRV.CHILD_PROCESS_ID <> -1 AND ALHRV.CHILD_PROCESS_ID=AORV.PK1_VALUE AND AORV.ROLE_NAME='AMW_RL_PROC_OWNER_ROLE'