View: AMW_EXPORT_AUDIT_PROCEDURE_V

Product:	AMW - Internal Controls Manager
Description:
Implementation/DBA Data:	APPS.AMW_EXPORT_AUDIT_PROCEDURE_V

View Text

SELECT APR.APAID
, APR.APID
, APR.APRID
, APR.APJID
, APR.OID
, APR.CID
, APR.TID
, APR.APNM
, APR.TNM
, APR.TDES
, APR.ONM
, APR.APS
, APR.APWD
, APR.APEB
, APR.APEO
, APR.APEID
, APR.APENM
, APR.CNM
, APR.CDES
, APR.DEF
, APR.OEF
, APR.CAR
, APR.COID
, APR.PNM
, APR.PSD
, STEP.SID
, STEP.SS
, STEP.SWD
, STEP.SEB
, STEP.SEO
, STEP.SEID
, STEP.SENM
, STEP.SNM
, STEP.SDES
, STEP.SNUM
, APR.DSUM
, APR.DDES
, APR.OSUM
, APR.ODES
, APR.CSUM
, APR.CODES
, STEP.SAU
, APR.KEYC
, APR.PRNM
, APR.RNM
, APR.COBJ
, STEP.FAD
FROM ( SELECT APA.AP_ASSOCIATION_ID APAID
, APA.AUDIT_PROCEDURE_ID APID
, APA.AUDIT_PROCEDURE_REV_ID APRID
, APA.PK1 APJID
, APA.PK2 OID
, APA.PK3 CID
, APA.PK4 TID
, AAPV.NAME APNM
, DECODE(APA.PK4
, -1
, 'AUDIT PROCEDURES WITH NO TASK ASSIGNMENTS'
, AATV.TASK_NAME )TNM
, AATV.DESCRIPTION TDES
, AUV.NAME ONM
, NVL((SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_PROCEDURE_STATUS'
AND ENABLED_FLAG ='Y'
AND (END_DATE_ACTIVE > SYSDATE OR END_DATE_ACTIVE IS NULL)
AND LOOKUP_CODE = (SELECT STATUS
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'AP'))
, 'IN PROGRESS') APS
, (SELECT WORK_DESC
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'AP') APWD
, (SELECT EXECUTED_BY
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'AP') APEB
, (SELECT EXECUTED_ON
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'AP') APEO
, (SELECT EXECUTION_ID
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'AP') APEID
, (SELECT FULL_NAME
FROM AMW_EMPLOYEES_CURRENT_V
, AMW_AP_EXECUTIONS
WHERE EXECUTED_BY = PARTY_ID
AND AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'AP') APENM
, ACT.NAME CNM
, ACT.DESCRIPTION CDES
, ( SELECT OV.OPINION_VALUE_NAME
FROM AMW_OPINION_DETAILS OD
, AMW_OPINION_VALUES_VL OV
, AMW_OPINIONS AO
WHERE OD.OPINION_COMPONENT_ID = OV.OPINION_COMPONENT_ID AND OD.OPINION_COMPONENT_ID = 13
AND OD.OPINION_VALUE_ID = OV.OPINION_VALUE_ID
AND OD.OPINION_ID = AO.OPINION_ID AND AO.OBJECT_OPINION_TYPE_ID = 5
AND AO.PK1_VALUE = APA.PK3
AND AO.PK2_VALUE = APA.PK1
AND AO.PK3_VALUE = APA.PK2 AND AO.PK4_VALUE = APA.AUDIT_PROCEDURE_ID
AND AO.PK5_VALUE = APA.PK4 ) DEF
, ( SELECT OD.SUMMARY_TXT
FROM AMW_OPINION_DETAILS OD
, AMW_OPINIONS AO
WHERE OD.OPINION_COMPONENT_ID = 13
AND OD.OPINION_ID = AO.OPINION_ID
AND AO.OBJECT_OPINION_TYPE_ID = 5
AND AO.PK1_VALUE = APA.PK3
AND AO.PK2_VALUE = APA.PK1
AND AO.PK3_VALUE = APA.PK2 AND AO.PK4_VALUE = APA.AUDIT_PROCEDURE_ID
AND AO.PK5_VALUE = APA.PK4 ) DSUM
, ( SELECT OD.DESCRIPTION_TXT
FROM AMW_OPINION_DETAILS OD
, AMW_OPINIONS AO
WHERE OD.OPINION_COMPONENT_ID = 13
AND OD.OPINION_ID = AO.OPINION_ID
AND AO.OBJECT_OPINION_TYPE_ID = 5
AND AO.PK1_VALUE = APA.PK3
AND AO.PK2_VALUE = APA.PK1
AND AO.PK3_VALUE = APA.PK2 AND AO.PK4_VALUE = APA.AUDIT_PROCEDURE_ID
AND AO.PK5_VALUE = APA.PK4 ) DDES
, ( SELECT OV.OPINION_VALUE_NAME
FROM AMW_OPINION_DETAILS OD
, AMW_OPINION_VALUES_VL OV
, AMW_OPINIONS AO
WHERE OD.OPINION_COMPONENT_ID = OV.OPINION_COMPONENT_ID AND OD.OPINION_COMPONENT_ID = 14
AND OD.OPINION_VALUE_ID = OV.OPINION_VALUE_ID
AND OD.OPINION_ID = AO.OPINION_ID AND AO.OBJECT_OPINION_TYPE_ID = 5
AND AO.PK1_VALUE = APA.PK3
AND AO.PK2_VALUE = APA.PK1
AND AO.PK3_VALUE = APA.PK2 AND AO.PK4_VALUE = APA.AUDIT_PROCEDURE_ID
AND AO.PK5_VALUE = APA.PK4 ) OEF
, ( SELECT OD.SUMMARY_TXT
FROM AMW_OPINION_DETAILS OD
, AMW_OPINIONS AO
WHERE OD.OPINION_COMPONENT_ID = 14
AND OD.OPINION_ID = AO.OPINION_ID
AND AO.OBJECT_OPINION_TYPE_ID = 5
AND AO.PK1_VALUE = APA.PK3
AND AO.PK2_VALUE = APA.PK1
AND AO.PK3_VALUE = APA.PK2 AND AO.PK4_VALUE = APA.AUDIT_PROCEDURE_ID
AND AO.PK5_VALUE = APA.PK4 ) OSUM
, ( SELECT OD.DESCRIPTION_TXT
FROM AMW_OPINION_DETAILS OD
, AMW_OPINIONS AO
WHERE OD.OPINION_COMPONENT_ID = 14
AND OD.OPINION_ID = AO.OPINION_ID
AND AO.OBJECT_OPINION_TYPE_ID = 5
AND AO.PK1_VALUE = APA.PK3
AND AO.PK2_VALUE = APA.PK1
AND AO.PK3_VALUE = APA.PK2 AND AO.PK4_VALUE = APA.AUDIT_PROCEDURE_ID
AND AO.PK5_VALUE = APA.PK4 ) ODES
, ( SELECT OV.OPINION_VALUE_NAME
FROM AMW_OPINION_DETAILS OD
, AMW_OPINION_VALUES_VL OV
, AMW_OPINIONS AO
WHERE OD.OPINION_COMPONENT_ID = OV.OPINION_COMPONENT_ID AND OD.OPINION_COMPONENT_ID = 12
AND OD.OPINION_VALUE_ID = OV.OPINION_VALUE_ID
AND OD.OPINION_ID = AO.OPINION_ID AND AO.OBJECT_OPINION_TYPE_ID = 5
AND AO.PK1_VALUE = APA.PK3
AND AO.PK2_VALUE = APA.PK1
AND AO.PK3_VALUE = APA.PK2 AND AO.PK4_VALUE = APA.AUDIT_PROCEDURE_ID
AND AO.PK5_VALUE = APA.PK4 ) CAR
, ( SELECT OD.SUMMARY_TXT
FROM AMW_OPINION_DETAILS OD
, AMW_OPINIONS AO
WHERE OD.OPINION_COMPONENT_ID = 12
AND OD.OPINION_ID = AO.OPINION_ID
AND AO.OBJECT_OPINION_TYPE_ID = 5
AND AO.PK1_VALUE = APA.PK3
AND AO.PK2_VALUE = APA.PK1
AND AO.PK3_VALUE = APA.PK2 AND AO.PK4_VALUE = APA.AUDIT_PROCEDURE_ID
AND AO.PK5_VALUE = APA.PK4 ) CSUM
, ( SELECT OD.DESCRIPTION_TXT
FROM AMW_OPINION_DETAILS OD
, AMW_OPINIONS AO
WHERE OD.OPINION_COMPONENT_ID = 12
AND OD.OPINION_ID = AO.OPINION_ID
AND AO.OBJECT_OPINION_TYPE_ID = 5
AND AO.PK1_VALUE = APA.PK3
AND AO.PK2_VALUE = APA.PK1
AND AO.PK3_VALUE = APA.PK2 AND AO.PK4_VALUE = APA.AUDIT_PROCEDURE_ID
AND AO.PK5_VALUE = APA.PK4 ) CODES
, ( SELECT OV.OPINION_VALUE_ID
FROM AMW_OPINION_DETAILS OD
, AMW_OPINION_VALUES_VL OV
, AMW_OPINIONS AO
WHERE OD.OPINION_COMPONENT_ID = OV.OPINION_COMPONENT_ID AND OD.OPINION_COMPONENT_ID = 12
AND OD.OPINION_VALUE_ID = OV.OPINION_VALUE_ID
AND OD.OPINION_ID = AO.OPINION_ID AND AO.OBJECT_OPINION_TYPE_ID = 5
AND AO.PK1_VALUE = APA.PK3
AND AO.PK2_VALUE = APA.PK1
AND AO.PK3_VALUE = APA.PK2 AND AO.PK4_VALUE = APA.AUDIT_PROCEDURE_ID
AND AO.PK5_VALUE = APA.PK4 ) COID
, DECODE(ACB.KEY_MITIGATING
, 'Y'
, 'YES'
, 'NO') KEYC
, ACA.PK3 PID
, ACA.PK4 RID
, (SELECT DISPLAY_NAME
FROM AMW_CURRENT_APPRVD_REV_V
WHERE PROCESS_ID = ACA.PK3) PRNM
, (SELECT NAME
FROM AMW_RISKS_ALL_VL
WHERE RISK_ID = ACA.PK4
AND CURR_APPROVED_FLAG = 'Y') RNM
, (SELECT VL.NAME
FROM AMW_OBJECTIVE_ASSOCIATIONS AO
, AMW_PROCESS_OBJECTIVES_VL VL WHERE AO.OBJECT_TYPE = 'CONTROL'
AND AO.PK1 = ACA.PK3
AND AO.PK2 = ACA.PK4
AND AO.PK3 = ACA.CONTROL_ID AND VL.PROCESS_OBJECTIVE_ID = AO.PROCESS_OBJECTIVE_ID) COBJ
, APV.PROJECT_NAME PNM
, APV.START_DATE PSD
, ROW_NUMBER() OVER (PARTITION BY APA.PK1
, APA.PK4
, APA.AUDIT_PROCEDURE_ID
, APA.PK2 ORDER BY APA.PK3) AS AROWNUM
FROM AMW_AP_ASSOCIATIONS APA
, AMW_AUDIT_PROCEDURES_VL AAPV
, AMW_AUDIT_TASKS_V AATV
, AMW_CONTROLS_TL ACT
, AMW_CONTROLS_B ACB
, AMW_AUDIT_UNITS_V AUV
, AMW_AUDIT_PROJECTS_V APV
, AMW_CONTROL_ASSOCIATIONS ACA
WHERE AAPV.AUDIT_PROCEDURE_ID = APA.AUDIT_PROCEDURE_ID
AND AAPV.AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND AATV.TASK_ID(+) = APA.PK4
AND ACT.CONTROL_REV_ID = ACB.CONTROL_REV_ID
AND ACB.CONTROL_ID = APA.PK3
AND ACB.CURR_APPROVED_FLAG = 'Y'
AND ACT.LANGUAGE(+)= USERENV('LANG')
AND AUV.ORGANIZATION_ID(+) = APA.PK2
AND ACA.OBJECT_TYPE(+) = 'PROJECT'
AND ACA.PK1(+) = APA.PK1
AND ACA.PK2(+) = APA.PK2
AND ACA.CONTROL_ID(+) = APA.PK3
AND APA.OBJECT_TYPE = 'PROJECT'
AND APV.AUDIT_PROJECT_ID = APA.PK1) APR
, ( SELECT STP.APJID
, STP.OID
, STP.TID
, STP.APID
, STP.SID
, STP.SS
, STP.SWD
, STP.SEB
, STP.SEO
, STP.SEID
, STP.SENM
, STP.SNM
, STP.SDES
, STP.SNUM
, STP.SAU
, STP.FAD
, ROW_NUMBER() OVER (PARTITION BY STP.APJID
, STP.TID
, STP.APID
, STP.OID ORDER BY STP.SID) AS BROWNUM
FROM ( SELECT STP1.APJID
, STP1.APID
, STP1.SID
, STP1.SS
, STP1.SWD
, STP1.SEB
, STP1.SEO
, STP1.SEID
, STP1.SENM
, STP1.SNM
, STP1.SDES
, STP1.SNUM
, STP1.OID
, STP1.TID
, ATTCH.SAU
, ATTCH.FAD
FROM ( SELECT DISTINCT APA.AUDIT_PROCEDURE_ID APID
, APA.AUDIT_PROCEDURE_REV_ID APRID
, APA.PK1 APJID
, APA.PK2 OID
, APA.PK4 TID
, AASV.AP_STEP_ID SID
, NVL((SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_PROCEDURE_STATUS'
AND ENABLED_FLAG ='Y'
AND (END_DATE_ACTIVE > SYSDATE OR END_DATE_ACTIVE IS NULL)
AND LOOKUP_CODE = (SELECT STATUS
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'STEP'
AND AP_STEP_ID = AASV.AP_STEP_ID ))
, 'IN PROGRESS') SS
, (SELECT WORK_DESC
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'STEP'
AND AP_STEP_ID = AASV.AP_STEP_ID) SWD
, (SELECT EXECUTED_BY
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'STEP'
AND AP_STEP_ID = AASV.AP_STEP_ID) SEB
, (SELECT EXECUTED_ON
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'STEP'
AND AP_STEP_ID = AASV.AP_STEP_ID) SEO
, (SELECT EXECUTION_ID
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'STEP'
AND AP_STEP_ID = AASV.AP_STEP_ID) SEID
, (SELECT FULL_NAME
FROM AMW_EMPLOYEES_CURRENT_V
, AMW_AP_EXECUTIONS
WHERE EXECUTED_BY = PARTY_ID
AND AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'STEP'
AND AP_STEP_ID = AASV.AP_STEP_ID) SENM
, AASV.NAME SNM
, AASV.DESCRIPTION SDES
, AASV.CSEQNUM SNUM
FROM AMW_AP_ASSOCIATIONS APA
, AMW_AP_STEPS_VL AASV
WHERE APA.OBJECT_TYPE = 'PROJECT'
AND AASV.AUDIT_PROCEDURE_ID(+) = APA.AUDIT_PROCEDURE_ID) STP1
, ( SELECT FAD.SEQ_NUM
, FAD.DOCUMENT_ID FAD
, 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 SAU
, 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_PROJECT_STEP'
AND FDTL.MEDIA_ID IS NULL) ATTCH
WHERE STP1.APJID =ATTCH.PK1_VALUE(+)
AND STP1.OID =ATTCH.PK2_VALUE(+)
AND STP1.TID =ATTCH.PK3_VALUE(+)
AND STP1.APRID =ATTCH.PK4_VALUE(+)
AND STP1.SID =ATTCH.PK5_VALUE(+) ORDER BY APJID
, TID
, OID
, APID
, SID )STP ) STEP
WHERE APR.APID = STEP.APID(+)
AND APR.AROWNUM = STEP.BROWNUM(+)
AND APR.OID = STEP.OID(+)
AND APR.TID = STEP.TID(+)
AND APR.APJID = STEP.APJID(+) UNION SELECT APR.APAID
, STEP.APID
, STEP.APRID
, STEP.APJID
, STEP.OID
, APR.CID
, STEP.TID
, STEP.APNM
, STEP.TNM
, APR.TDES
, STEP.ONM
, STEP.APS
, STEP.APWD
, STEP.APEB
, STEP.APEO
, STEP.APEID
, STEP.APENM
, APR.CNM
, APR.CDES
, APR.DEF
, APR.OEF
, APR.CAR
, APR.COID
, STEP.PNM
, STEP.PSD
, STEP.SID
, STEP.SS
, STEP.SWD
, STEP.SEB
, STEP.SEO
, STEP.SEID
, STEP.SENM
, STEP.SNM
, STEP.SDES
, STEP.SNUM
, APR.DSUM
, APR.DDES
, APR.OSUM
, APR.ODES
, APR.CSUM
, APR.CODES
, STEP.SAU
, APR.KEYC
, APR.PRNM
, APR.RNM
, APR.COBJ
, STEP.FAD
FROM ( SELECT APA.AP_ASSOCIATION_ID APAID
, APA.AUDIT_PROCEDURE_ID APID
, APA.AUDIT_PROCEDURE_REV_ID APRID
, APA.PK1 APJID
, APA.PK2 OID
, APA.PK3 CID
, APA.PK4 TID
, AAPV.NAME APNM
, DECODE(APA.PK4
, -1
, 'AUDIT PROCEDURES WITH NO TASK ASSIGNMENTS'
, AATV.TASK_NAME )TNM
, AATV.DESCRIPTION TDES
, AUV.NAME ONM
, NVL((SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_PROCEDURE_STATUS'
AND ENABLED_FLAG ='Y'
AND (END_DATE_ACTIVE > SYSDATE OR END_DATE_ACTIVE IS NULL)
AND LOOKUP_CODE = (SELECT STATUS
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'AP'))
, 'IN PROGRESS') APS
, (SELECT WORK_DESC
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'AP') APWD
, (SELECT EXECUTED_BY
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'AP') APEB
, (SELECT EXECUTED_ON
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'AP') APEO
, (SELECT EXECUTION_ID
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'AP') APEID
, (SELECT FULL_NAME
FROM AMW_EMPLOYEES_CURRENT_V
, AMW_AP_EXECUTIONS
WHERE EXECUTED_BY = PARTY_ID
AND AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'AP') APENM
, ACT.NAME CNM
, ACT.DESCRIPTION CDES
, ( SELECT OV.OPINION_VALUE_NAME
FROM AMW_OPINION_DETAILS OD
, AMW_OPINION_VALUES_VL OV
, AMW_OPINIONS AO
WHERE OD.OPINION_COMPONENT_ID = OV.OPINION_COMPONENT_ID AND OD.OPINION_COMPONENT_ID = 13
AND OD.OPINION_VALUE_ID = OV.OPINION_VALUE_ID
AND OD.OPINION_ID = AO.OPINION_ID AND AO.OBJECT_OPINION_TYPE_ID = 5
AND AO.PK1_VALUE = APA.PK3
AND AO.PK2_VALUE = APA.PK1
AND AO.PK3_VALUE = APA.PK2 AND AO.PK4_VALUE = APA.AUDIT_PROCEDURE_ID
AND AO.PK5_VALUE = APA.PK4 ) DEF
, ( SELECT OD.SUMMARY_TXT
FROM AMW_OPINION_DETAILS OD
, AMW_OPINIONS AO
WHERE OD.OPINION_COMPONENT_ID = 13
AND OD.OPINION_ID = AO.OPINION_ID
AND AO.OBJECT_OPINION_TYPE_ID = 5
AND AO.PK1_VALUE = APA.PK3
AND AO.PK2_VALUE = APA.PK1
AND AO.PK3_VALUE = APA.PK2 AND AO.PK4_VALUE = APA.AUDIT_PROCEDURE_ID
AND AO.PK5_VALUE = APA.PK4 ) DSUM
, ( SELECT OD.DESCRIPTION_TXT
FROM AMW_OPINION_DETAILS OD
, AMW_OPINIONS AO
WHERE OD.OPINION_COMPONENT_ID = 13
AND OD.OPINION_ID = AO.OPINION_ID
AND AO.OBJECT_OPINION_TYPE_ID = 5
AND AO.PK1_VALUE = APA.PK3
AND AO.PK2_VALUE = APA.PK1
AND AO.PK3_VALUE = APA.PK2 AND AO.PK4_VALUE = APA.AUDIT_PROCEDURE_ID
AND AO.PK5_VALUE = APA.PK4 ) DDES
, ( SELECT OV.OPINION_VALUE_NAME
FROM AMW_OPINION_DETAILS OD
, AMW_OPINION_VALUES_VL OV
, AMW_OPINIONS AO
WHERE OD.OPINION_COMPONENT_ID = OV.OPINION_COMPONENT_ID AND OD.OPINION_COMPONENT_ID = 14
AND OD.OPINION_VALUE_ID = OV.OPINION_VALUE_ID
AND OD.OPINION_ID = AO.OPINION_ID AND AO.OBJECT_OPINION_TYPE_ID = 5
AND AO.PK1_VALUE = APA.PK3
AND AO.PK2_VALUE = APA.PK1
AND AO.PK3_VALUE = APA.PK2 AND AO.PK4_VALUE = APA.AUDIT_PROCEDURE_ID
AND AO.PK5_VALUE = APA.PK4 ) OEF
, ( SELECT OD.SUMMARY_TXT
FROM AMW_OPINION_DETAILS OD
, AMW_OPINIONS AO
WHERE OD.OPINION_COMPONENT_ID = 14
AND OD.OPINION_ID = AO.OPINION_ID
AND AO.OBJECT_OPINION_TYPE_ID = 5
AND AO.PK1_VALUE = APA.PK3
AND AO.PK2_VALUE = APA.PK1
AND AO.PK3_VALUE = APA.PK2 AND AO.PK4_VALUE = APA.AUDIT_PROCEDURE_ID
AND AO.PK5_VALUE = APA.PK4 ) OSUM
, ( SELECT OD.DESCRIPTION_TXT
FROM AMW_OPINION_DETAILS OD
, AMW_OPINIONS AO
WHERE OD.OPINION_COMPONENT_ID = 14
AND OD.OPINION_ID = AO.OPINION_ID
AND AO.OBJECT_OPINION_TYPE_ID = 5
AND AO.PK1_VALUE = APA.PK3
AND AO.PK2_VALUE = APA.PK1
AND AO.PK3_VALUE = APA.PK2 AND AO.PK4_VALUE = APA.AUDIT_PROCEDURE_ID
AND AO.PK5_VALUE = APA.PK4 ) ODES
, ( SELECT OV.OPINION_VALUE_NAME
FROM AMW_OPINION_DETAILS OD
, AMW_OPINION_VALUES_VL OV
, AMW_OPINIONS AO
WHERE OD.OPINION_COMPONENT_ID = OV.OPINION_COMPONENT_ID AND OD.OPINION_COMPONENT_ID = 12
AND OD.OPINION_VALUE_ID = OV.OPINION_VALUE_ID
AND OD.OPINION_ID = AO.OPINION_ID AND AO.OBJECT_OPINION_TYPE_ID = 5
AND AO.PK1_VALUE = APA.PK3
AND AO.PK2_VALUE = APA.PK1
AND AO.PK3_VALUE = APA.PK2 AND AO.PK4_VALUE = APA.AUDIT_PROCEDURE_ID
AND AO.PK5_VALUE = APA.PK4 ) CAR
, ( SELECT OD.SUMMARY_TXT
FROM AMW_OPINION_DETAILS OD
, AMW_OPINIONS AO
WHERE OD.OPINION_COMPONENT_ID = 12
AND OD.OPINION_ID = AO.OPINION_ID
AND AO.OBJECT_OPINION_TYPE_ID = 5
AND AO.PK1_VALUE = APA.PK3
AND AO.PK2_VALUE = APA.PK1
AND AO.PK3_VALUE = APA.PK2 AND AO.PK4_VALUE = APA.AUDIT_PROCEDURE_ID
AND AO.PK5_VALUE = APA.PK4 ) CSUM
, ( SELECT OD.DESCRIPTION_TXT
FROM AMW_OPINION_DETAILS OD
, AMW_OPINIONS AO
WHERE OD.OPINION_COMPONENT_ID = 12
AND OD.OPINION_ID = AO.OPINION_ID
AND AO.OBJECT_OPINION_TYPE_ID = 5
AND AO.PK1_VALUE = APA.PK3
AND AO.PK2_VALUE = APA.PK1
AND AO.PK3_VALUE = APA.PK2 AND AO.PK4_VALUE = APA.AUDIT_PROCEDURE_ID
AND AO.PK5_VALUE = APA.PK4 ) CODES
, ( SELECT OV.OPINION_VALUE_ID
FROM AMW_OPINION_DETAILS OD
, AMW_OPINION_VALUES_VL OV
, AMW_OPINIONS AO
WHERE OD.OPINION_COMPONENT_ID = OV.OPINION_COMPONENT_ID AND OD.OPINION_COMPONENT_ID = 12
AND OD.OPINION_VALUE_ID = OV.OPINION_VALUE_ID
AND OD.OPINION_ID = AO.OPINION_ID AND AO.OBJECT_OPINION_TYPE_ID = 5
AND AO.PK1_VALUE = APA.PK3
AND AO.PK2_VALUE = APA.PK1
AND AO.PK3_VALUE = APA.PK2 AND AO.PK4_VALUE = APA.AUDIT_PROCEDURE_ID
AND AO.PK5_VALUE = APA.PK4 ) COID
, APV.PROJECT_NAME PNM
, APV.START_DATE PSD
, DECODE(ACB.KEY_MITIGATING
, 'Y'
, 'YES'
, 'NO') KEYC
, ACA.PK3 PID
, ACA.PK4 RID
, (SELECT DISPLAY_NAME
FROM AMW_CURRENT_APPRVD_REV_V
WHERE PROCESS_ID = ACA.PK3) PRNM
, (SELECT NAME
FROM AMW_RISKS_ALL_VL
WHERE RISK_ID = ACA.PK4
AND CURR_APPROVED_FLAG = 'Y') RNM
, (SELECT VL.NAME
FROM AMW_OBJECTIVE_ASSOCIATIONS AO
, AMW_PROCESS_OBJECTIVES_VL VL WHERE AO.OBJECT_TYPE = 'CONTROL'
AND AO.PK1 = ACA.PK3
AND AO.PK2 = ACA.PK4
AND AO.PK3 = ACA.CONTROL_ID AND VL.PROCESS_OBJECTIVE_ID = AO.PROCESS_OBJECTIVE_ID) COBJ
, ROW_NUMBER() OVER (PARTITION BY APA.PK1
, APA.PK4
, APA.AUDIT_PROCEDURE_ID
, APA.PK2 ORDER BY APA.PK3) AS AROWNUM
FROM AMW_AP_ASSOCIATIONS APA
, AMW_AUDIT_PROCEDURES_VL AAPV
, AMW_AUDIT_TASKS_V AATV
, AMW_CONTROLS_TL ACT
, AMW_CONTROLS_B ACB
, AMW_AUDIT_UNITS_V AUV
, AMW_AUDIT_PROJECTS_V APV
, AMW_CONTROL_ASSOCIATIONS ACA
WHERE AAPV.AUDIT_PROCEDURE_ID = APA.AUDIT_PROCEDURE_ID
AND AAPV.AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND AATV.TASK_ID(+) = APA.PK4
AND ACT.CONTROL_REV_ID = ACB.CONTROL_REV_ID
AND ACB.CONTROL_ID = APA.PK3
AND ACB.CURR_APPROVED_FLAG = 'Y'
AND ACT.LANGUAGE(+)= USERENV('LANG')
AND AUV.ORGANIZATION_ID(+) = APA.PK2
AND ACA.OBJECT_TYPE(+) = 'PROJECT'
AND ACA.PK1(+) = APA.PK1
AND ACA.PK2(+) = APA.PK2
AND ACA.CONTROL_ID(+) = APA.PK3 AND APA.OBJECT_TYPE(+) = 'PROJECT'
AND APV.AUDIT_PROJECT_ID = APA.PK1) APR
, ( SELECT STP.APID
, STP.SID
, STP.SS
, STP.SWD
, STP.SEB
, STP.SEO
, STP.SEID
, STP.SENM
, STP.SNM
, STP.SDES
, STP.SNUM
, STP.TNM
, STP.APNM
, STP.TID
, STP.APRID
, STP.APS
, STP.APWD
, STP.APEB
, STP.APEO
, STP.APEID
, STP.APENM
, STP.APJID
, STP.OID
, STP.PNM
, STP.PSD
, STP.ONM
, STP.SAU
, STP.FAD
, ROW_NUMBER() OVER (PARTITION BY STP.APJID
, STP.TID
, STP.APID
, STP.OID ORDER BY STP.SID) AS BROWNUM
FROM ( SELECT STP1.APID
, STP1.SID
, STP1.SS
, STP1.SWD
, STP1.SEB
, STP1.SEO
, STP1.SEID
, STP1.SENM
, STP1.SNM
, STP1.SDES
, STP1.SNUM
, STP1.TNM
, STP1.APNM
, STP1.TID
, STP1.APRID
, STP1.APS
, STP1.APWD
, STP1.APEB
, STP1.APEO
, STP1.APEID
, STP1.APENM
, STP1.APJID
, STP1.OID
, STP1.PNM
, STP1.PSD
, STP1.ONM
, ATTCH.SAU
, ATTCH.FAD
FROM ( SELECT DISTINCT APA.AUDIT_PROCEDURE_ID APID
, APA.AUDIT_PROCEDURE_REV_ID APRID
, APA.PK4 TID
, AASV.AP_STEP_ID SID
, APA.PK1 APJID
, APA.PK2 OID
, AUV.NAME ONM
, NVL((SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_PROCEDURE_STATUS'
AND ENABLED_FLAG ='Y'
AND (END_DATE_ACTIVE > SYSDATE OR END_DATE_ACTIVE IS NULL)
AND LOOKUP_CODE = (SELECT STATUS
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'STEP'
AND AP_STEP_ID = AASV.AP_STEP_ID ))
, 'IN PROGRESS') SS
, (SELECT WORK_DESC
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'STEP'
AND AP_STEP_ID = AASV.AP_STEP_ID) SWD
, (SELECT EXECUTED_BY
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'STEP'
AND AP_STEP_ID = AASV.AP_STEP_ID) SEB
, (SELECT EXECUTED_ON
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'STEP'
AND AP_STEP_ID = AASV.AP_STEP_ID) SEO
, (SELECT EXECUTION_ID
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'STEP'
AND AP_STEP_ID = AASV.AP_STEP_ID) SEID
, (SELECT FULL_NAME
FROM AMW_EMPLOYEES_CURRENT_V
, AMW_AP_EXECUTIONS
WHERE EXECUTED_BY = PARTY_ID
AND AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'STEP'
AND AP_STEP_ID = AASV.AP_STEP_ID) SENM
, AASV.NAME SNM
, AASV.DESCRIPTION SDES
, AASV.CSEQNUM SNUM
, AAPV.NAME APNM
, DECODE(APA.PK4
, -1
, 'AUDIT PROCEDURES WITH NO TASK ASSIGNMENTS'
, AATV.TASK_NAME )TNM
, NVL((SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_PROCEDURE_STATUS'
AND ENABLED_FLAG ='Y'
AND (END_DATE_ACTIVE > SYSDATE OR END_DATE_ACTIVE IS NULL)
AND LOOKUP_CODE = (SELECT STATUS
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'AP'))
, 'IN PROGRESS') APS
, (SELECT WORK_DESC
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'AP') APWD
, (SELECT EXECUTED_BY
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'AP') APEB
, (SELECT EXECUTED_ON
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'AP') APEO
, (SELECT EXECUTION_ID
FROM AMW_AP_EXECUTIONS
WHERE AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'AP') APEID
, (SELECT FULL_NAME
FROM AMW_EMPLOYEES_CURRENT_V
, AMW_AP_EXECUTIONS
WHERE EXECUTED_BY = PARTY_ID
AND AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND PK2 = APA.PK2
AND PK1 = APA.PK1
AND PK3 = APA.PK4
AND EXECUTION_TYPE = 'AP') APENM
, APV.PROJECT_NAME PNM
, APV.START_DATE PSD
FROM AMW_AP_ASSOCIATIONS APA
, AMW_AP_STEPS_VL AASV
, AMW_AP_EXECUTIONS APE
, AMW_EMPLOYEES_CURRENT_V AEC
, AMW_AUDIT_PROCEDURES_VL AAPV
, AMW_AUDIT_TASKS_V AATV
, AMW_AUDIT_UNITS_V AUV
, AMW_AUDIT_PROJECTS_V APV
WHERE AAPV.AUDIT_PROCEDURE_ID = APA.AUDIT_PROCEDURE_ID
AND AAPV.AUDIT_PROCEDURE_REV_ID = APA.AUDIT_PROCEDURE_REV_ID
AND AATV.TASK_ID(+) = APA.PK4
AND APA.OBJECT_TYPE = 'PROJECT'
AND AASV.AUDIT_PROCEDURE_ID(+) = APA.AUDIT_PROCEDURE_ID
AND APE.AUDIT_PROCEDURE_REV_ID(+) = APA.AUDIT_PROCEDURE_REV_ID
AND APE.PK2(+) = APA.PK2
AND APE.PK1(+) = APA.PK1
AND APE.PK3(+) = APA.PK4
AND APE.EXECUTION_TYPE(+) = 'STEP'
AND APE.EXECUTED_BY = AEC.PARTY_ID(+)
AND AUV.ORGANIZATION_ID(+) = APA.PK2
AND APV.AUDIT_PROJECT_ID = APA.PK1) STP1
, ( SELECT FAD.SEQ_NUM
, FAD.DOCUMENT_ID FAD
, 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 SAU
, 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_PROJECT_STEP'
AND FDTL.MEDIA_ID IS NULL) ATTCH
WHERE STP1.APJID =ATTCH.PK1_VALUE(+)
AND STP1.OID =ATTCH.PK2_VALUE(+)
AND STP1.TID =ATTCH.PK3_VALUE(+)
AND STP1.APRID =ATTCH.PK4_VALUE(+)
AND STP1.SID =ATTCH.PK5_VALUE(+) ORDER BY APJID
, TID
, OID
, APID
, SID )STP) STEP
WHERE APR.APID(+) = STEP.APID
AND APR.AROWNUM(+) = STEP.BROWNUM
AND APR.OID(+) = STEP.OID
AND APR.TID(+) = STEP.TID
AND APR.APJID(+) = STEP.APJID ORDER BY APJID
, OID
, TID
, APID
, CID
, SID

Columns