VIEW: APPS.AMW_EXPORT_PROCESS_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'