FND Design Data [Home] [Help]

View: AMW_EXPORT_PROCESS_V

Product: AMW - Internal Controls Manager
Description:
Implementation/DBA Data: ViewAPPS.AMW_EXPORT_PROCESS_V
View Text

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'

Columns

Name
SEQ_NUM
PRC_ID
PRC_CODE
PRC_NAME
REVISE
PRNT_ID
IMM_PRNT_ID
PRNT_CODE
PRNT_NAME
PRC_STS
PRC_TYPE
PRC_CAT
PRC_SIG
PRC_STD
PRC_CTRL_ACT
ATT_URL
PROC_OWN
APPL_OWN
FIN_OWN
PARTY_ID