eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: AMW_EXPORT_CTRL_V

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

View Text

SELECT ACB.CONTROL_ID CID
, ACB.CONTROL_REV_ID CRID
, ACB.REV_NUM CRNM
, ACB.END_DATE CEND
, ACB.CURR_APPROVED_FLAG CCAF
, ACB.LATEST_REVISION_FLAG CLRF
, ACT.NAME CNM
, '' RC
, ACT.DESCRIPTION CDS
, (SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_APPROVAL_STATUS'
AND LOOKUP_CODE=ACB.APPROVAL_STATUS
AND ENABLED_FLAG='Y'
AND NVL(END_DATE_ACTIVE
, SYSDATE) >= SYSDATE) CAPP
, ACB.APPROVAL_DATE CAPDT
, (SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_LOCATION'
AND LOOKUP_CODE=ACB.CONTROL_LOCATION
AND ENABLED_FLAG='Y'
AND NVL(END_DATE_ACTIVE
, SYSDATE) >= SYSDATE) CLOC
, (SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_TYPE'
AND LOOKUP_CODE=ACB.CONTROL_TYPE
AND ENABLED_FLAG='Y'
AND NVL(END_DATE_ACTIVE
, SYSDATE) >= SYSDATE) CTYP
, (SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_AUTOMATION_TYPE'
AND LOOKUP_CODE=ACB.AUTOMATION_TYPE
AND ENABLED_FLAG='Y'
AND NVL(END_DATE_ACTIVE
, SYSDATE) >= SYSDATE) CAUT
, (SELECT APPLICATION_SHORT_NAME
FROM FND_APPLICATION
WHERE APPLICATION_ID=ACB.APPLICATION_ID) CAPL
, ACB.SOURCE CSRC
, ACT.PHYSICAL_EVIDENCE CPEV
, (SELECT PBG.NAME
FROM PER_BUSINESS_GROUPS PBG
, PER_JOBS PJ
WHERE PJ.BUSINESS_GROUP_ID=PBG.BUSINESS_GROUP_ID
AND PJ.JOB_ID=ACB.JOB_ID) CBGR
, (SELECT PJ.NAME
FROM PER_JOBS PJ
WHERE PJ.JOB_ID=ACB.JOB_ID) CJOB
, ACB.PREVENTIVE_CONTROL CPRV
, ACB.DETECTIVE_CONTROL CDET
, ACB.DISCLOSURE_CONTROL CDIS
, ACB.KEY_MITIGATING CKEY
, (SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_VERIFICATION_SOURCE'
AND LOOKUP_CODE=ACB.VERIFICATION_SOURCE
AND ENABLED_FLAG='Y'
AND NVL(END_DATE_ACTIVE
, SYSDATE) >= SYSDATE) CVSO
, ACT.VERIFICATION_SOURCE_NAME CVNM
, ACT.VERIFICATION_INSTRUCTION CVIN
, (SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_FREQUENCY'
AND ENABLED_FLAG ='Y'
AND (END_DATE_ACTIVE > SYSDATE OR END_DATE_ACTIVE IS NULL)
AND LOOKUP_CODE = ACB.UOM_CODE) CUOMTL
, ACB.CONTROL_FREQUENCY CCF
, (SELECT WORK_TYPE_NAME
FROM AMW_OBJ_CLASSIFICATION_V
WHERE CATEGORY_CODE='CONTROL'AND WORK_TYPE_ID=ACB.CLASSIFICATION) CCLASS
, (SELECT 'Y'
FROM AMW_ASSESSMENT_COMPONENTS
WHERE OBJECT_TYPE='CONTROL'
AND OBJECT_ID=ACB.CONTROL_REV_ID
AND COMPONENT_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_ASSESSMENT_COMPONENTS'
AND TAG=1)) CCM1
, (SELECT 'Y'
FROM AMW_ASSESSMENT_COMPONENTS
WHERE OBJECT_TYPE='CONTROL'
AND OBJECT_ID=ACB.CONTROL_REV_ID
AND COMPONENT_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_ASSESSMENT_COMPONENTS'
AND TAG=2)) CCM2
, (SELECT 'Y'
FROM AMW_ASSESSMENT_COMPONENTS
WHERE OBJECT_TYPE='CONTROL'
AND OBJECT_ID=ACB.CONTROL_REV_ID
AND COMPONENT_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_ASSESSMENT_COMPONENTS'
AND TAG=3)) CCM3
, (SELECT 'Y'
FROM AMW_ASSESSMENT_COMPONENTS
WHERE OBJECT_TYPE='CONTROL'
AND OBJECT_ID=ACB.CONTROL_REV_ID
AND COMPONENT_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_ASSESSMENT_COMPONENTS'
AND TAG=4)) CCM4
, (SELECT 'Y'
FROM AMW_ASSESSMENT_COMPONENTS
WHERE OBJECT_TYPE='CONTROL'
AND OBJECT_ID=ACB.CONTROL_REV_ID
AND COMPONENT_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_ASSESSMENT_COMPONENTS'
AND TAG=5)) CCM5
, (SELECT 'Y'
FROM AMW_ASSESSMENT_COMPONENTS
WHERE OBJECT_TYPE='CONTROL'
AND OBJECT_ID=ACB.CONTROL_REV_ID
AND COMPONENT_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_ASSESSMENT_COMPONENTS'
AND TAG=6)) CCM6
, (SELECT 'Y'
FROM AMW_ASSESSMENT_COMPONENTS
WHERE OBJECT_TYPE='CONTROL'
AND OBJECT_ID=ACB.CONTROL_REV_ID
AND COMPONENT_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_ASSESSMENT_COMPONENTS'
AND TAG=7)) CCM7
, (SELECT 'Y'
FROM AMW_ASSESSMENT_COMPONENTS
WHERE OBJECT_TYPE='CONTROL'
AND OBJECT_ID=ACB.CONTROL_REV_ID
AND COMPONENT_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_ASSESSMENT_COMPONENTS'
AND TAG=8)) CCM8
, (SELECT 'Y'
FROM AMW_ASSESSMENT_COMPONENTS
WHERE OBJECT_TYPE='CONTROL'
AND OBJECT_ID=ACB.CONTROL_REV_ID
AND COMPONENT_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_ASSESSMENT_COMPONENTS'
AND TAG=9)) CCM9
, (SELECT 'Y'
FROM AMW_ASSESSMENT_COMPONENTS
WHERE OBJECT_TYPE='CONTROL'
AND OBJECT_ID=ACB.CONTROL_REV_ID
AND COMPONENT_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_ASSESSMENT_COMPONENTS'
AND TAG=10)) CCM10
, (SELECT 'Y'
FROM AMW_CONTROL_OBJECTIVES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND OBJECTIVE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_OBJECTIVES'
AND TAG=1)) CCT1
, (SELECT 'Y'
FROM AMW_CONTROL_OBJECTIVES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND OBJECTIVE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_OBJECTIVES'
AND TAG=2)) CCT2
, (SELECT 'Y'
FROM AMW_CONTROL_OBJECTIVES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND OBJECTIVE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_OBJECTIVES'
AND TAG=3)) CCT3
, (SELECT 'Y'
FROM AMW_CONTROL_OBJECTIVES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND OBJECTIVE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_OBJECTIVES'
AND TAG=4)) CCT4
, (SELECT 'Y'
FROM AMW_CONTROL_OBJECTIVES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND OBJECTIVE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_OBJECTIVES'
AND TAG=5)) CCT5
, (SELECT 'Y'
FROM AMW_CONTROL_OBJECTIVES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND OBJECTIVE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_OBJECTIVES'
AND TAG=6)) CCT6
, (SELECT 'Y'
FROM AMW_CONTROL_OBJECTIVES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND OBJECTIVE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_OBJECTIVES'
AND TAG=7)) CCT7
, (SELECT 'Y'
FROM AMW_CONTROL_OBJECTIVES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND OBJECTIVE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_OBJECTIVES'
AND TAG=8)) CCT8
, (SELECT 'Y'
FROM AMW_CONTROL_OBJECTIVES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND OBJECTIVE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_OBJECTIVES'
AND TAG=9)) CCT9
, (SELECT 'Y'
FROM AMW_CONTROL_OBJECTIVES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND OBJECTIVE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_OBJECTIVES'
AND TAG=10)) CCT10
, (SELECT 'Y'
FROM AMW_CONTROL_ASSERTIONS
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND ASSERTION_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_ASSERTIONS'
AND TAG=1)) CAS1
, (SELECT 'Y'
FROM AMW_CONTROL_ASSERTIONS
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND ASSERTION_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_ASSERTIONS'
AND TAG=2)) CAS2
, (SELECT 'Y'
FROM AMW_CONTROL_ASSERTIONS
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND ASSERTION_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_ASSERTIONS'
AND TAG=3)) CAS3
, (SELECT 'Y'
FROM AMW_CONTROL_ASSERTIONS
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND ASSERTION_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_ASSERTIONS'
AND TAG=4)) CAS4
, (SELECT 'Y'
FROM AMW_CONTROL_ASSERTIONS
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND ASSERTION_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_ASSERTIONS'
AND TAG=5)) CAS5
, (SELECT 'Y'
FROM AMW_CONTROL_ASSERTIONS
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND ASSERTION_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_ASSERTIONS'
AND TAG=6)) CAS6
, (SELECT 'Y'
FROM AMW_CONTROL_ASSERTIONS
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND ASSERTION_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_ASSERTIONS'
AND TAG=7)) CAS7
, (SELECT 'Y'
FROM AMW_CONTROL_ASSERTIONS
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND ASSERTION_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_ASSERTIONS'
AND TAG=8)) CAS8
, (SELECT 'Y'
FROM AMW_CONTROL_ASSERTIONS
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND ASSERTION_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_ASSERTIONS'
AND TAG=9)) CAS9
, (SELECT 'Y'
FROM AMW_CONTROL_ASSERTIONS
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND ASSERTION_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_ASSERTIONS'
AND TAG=10)) CAS10
, (SELECT 'Y'
FROM AMW_CONTROL_PURPOSES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND PURPOSE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_PURPOSES'
AND TAG=1)) CPP1
, (SELECT 'Y'
FROM AMW_CONTROL_PURPOSES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND PURPOSE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_PURPOSES'
AND TAG=2)) CPP2
, (SELECT 'Y'
FROM AMW_CONTROL_PURPOSES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND PURPOSE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_PURPOSES'
AND TAG=3)) CPP3
, (SELECT 'Y'
FROM AMW_CONTROL_PURPOSES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND PURPOSE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_PURPOSES'
AND TAG=4)) CPP4
, (SELECT 'Y'
FROM AMW_CONTROL_PURPOSES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND PURPOSE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_PURPOSES'
AND TAG=5)) CPP5
, (SELECT 'Y'
FROM AMW_CONTROL_PURPOSES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND PURPOSE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_PURPOSES'
AND TAG=6)) CPP6
, (SELECT 'Y'
FROM AMW_CONTROL_PURPOSES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND PURPOSE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_PURPOSES'
AND TAG=7)) CPP7
, (SELECT 'Y'
FROM AMW_CONTROL_PURPOSES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND PURPOSE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_PURPOSES'
AND TAG=8)) CPP8
, (SELECT 'Y'
FROM AMW_CONTROL_PURPOSES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND PURPOSE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_PURPOSES'
AND TAG=9)) CPP9
, (SELECT 'Y'
FROM AMW_CONTROL_PURPOSES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND PURPOSE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_PURPOSES'
AND TAG=10)) CPP10
, -100 PARTY_ID
FROM AMW_CONTROLS_B ACB
, AMW_CONTROLS_TL ACT
WHERE ACB.CONTROL_REV_ID=ACT.CONTROL_REV_ID
AND ACT.LANGUAGE=USERENV('LANG')
AND ACB.APPROVAL_STATUS NOT IN ('P'
, 'R') UNION ALL SELECT ACB.CONTROL_ID CID
, ACB.CONTROL_REV_ID CRID
, ACB.REV_NUM CRNM
, ACB.END_DATE CEND
, ACB.CURR_APPROVED_FLAG CCAF
, ACB.LATEST_REVISION_FLAG CLRF
, ACT.NAME CNM
, '' RC
, ACT.DESCRIPTION CDS
, (SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_APPROVAL_STATUS'
AND LOOKUP_CODE=ACB.APPROVAL_STATUS
AND ENABLED_FLAG='Y'
AND NVL(END_DATE_ACTIVE
, SYSDATE) >= SYSDATE) CAPP
, ACB.APPROVAL_DATE CAPDT
, (SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_LOCATION'
AND LOOKUP_CODE=ACB.CONTROL_LOCATION
AND ENABLED_FLAG='Y'
AND NVL(END_DATE_ACTIVE
, SYSDATE) >= SYSDATE) CLOC
, (SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_TYPE'
AND LOOKUP_CODE=ACB.CONTROL_TYPE
AND ENABLED_FLAG='Y'
AND NVL(END_DATE_ACTIVE
, SYSDATE) >= SYSDATE) CTYP
, (SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_AUTOMATION_TYPE'
AND LOOKUP_CODE=ACB.AUTOMATION_TYPE
AND ENABLED_FLAG='Y'
AND NVL(END_DATE_ACTIVE
, SYSDATE) >= SYSDATE) CAUT
, (SELECT APPLICATION_SHORT_NAME
FROM FND_APPLICATION
WHERE APPLICATION_ID=ACB.APPLICATION_ID) CAPL
, ACB.SOURCE CSRC
, ACT.PHYSICAL_EVIDENCE CPEV
, (SELECT PBG.NAME
FROM PER_BUSINESS_GROUPS PBG
, PER_JOBS PJ
WHERE PJ.BUSINESS_GROUP_ID=PBG.BUSINESS_GROUP_ID
AND PJ.JOB_ID=ACB.JOB_ID) CBGR
, (SELECT PJ.NAME
FROM PER_JOBS PJ
WHERE PJ.JOB_ID=ACB.JOB_ID) CJOB
, ACB.PREVENTIVE_CONTROL CPRV
, ACB.DETECTIVE_CONTROL CDET
, ACB.DISCLOSURE_CONTROL CDIS
, ACB.KEY_MITIGATING CKEY
, (SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_VERIFICATION_SOURCE'
AND LOOKUP_CODE=ACB.VERIFICATION_SOURCE
AND ENABLED_FLAG='Y'
AND NVL(END_DATE_ACTIVE
, SYSDATE) >= SYSDATE) CVSO
, ACT.VERIFICATION_SOURCE_NAME CVNM
, ACT.VERIFICATION_INSTRUCTION CVIN
, (SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_FREQUENCY'
AND ENABLED_FLAG ='Y'
AND (END_DATE_ACTIVE > SYSDATE OR END_DATE_ACTIVE IS NULL)
AND LOOKUP_CODE = ACB.UOM_CODE) CUOMTL
, ACB.CONTROL_FREQUENCY CCF
, (SELECT WORK_TYPE_NAME
FROM AMW_OBJ_CLASSIFICATION_V
WHERE CATEGORY_CODE='CONTROL'AND WORK_TYPE_ID=ACB.CLASSIFICATION) CCLASS
, (SELECT 'Y'
FROM AMW_ASSESSMENT_COMPONENTS
WHERE OBJECT_TYPE='CONTROL'
AND OBJECT_ID=ACB.CONTROL_REV_ID
AND COMPONENT_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_ASSESSMENT_COMPONENTS'
AND TAG=1)) CCM1
, (SELECT 'Y'
FROM AMW_ASSESSMENT_COMPONENTS
WHERE OBJECT_TYPE='CONTROL'
AND OBJECT_ID=ACB.CONTROL_REV_ID
AND COMPONENT_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_ASSESSMENT_COMPONENTS'
AND TAG=2)) CCM2
, (SELECT 'Y'
FROM AMW_ASSESSMENT_COMPONENTS
WHERE OBJECT_TYPE='CONTROL'
AND OBJECT_ID=ACB.CONTROL_REV_ID
AND COMPONENT_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_ASSESSMENT_COMPONENTS'
AND TAG=3)) CCM3
, (SELECT 'Y'
FROM AMW_ASSESSMENT_COMPONENTS
WHERE OBJECT_TYPE='CONTROL'
AND OBJECT_ID=ACB.CONTROL_REV_ID
AND COMPONENT_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_ASSESSMENT_COMPONENTS'
AND TAG=4)) CCM4
, (SELECT 'Y'
FROM AMW_ASSESSMENT_COMPONENTS
WHERE OBJECT_TYPE='CONTROL'
AND OBJECT_ID=ACB.CONTROL_REV_ID
AND COMPONENT_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_ASSESSMENT_COMPONENTS'
AND TAG=5)) CCM5
, (SELECT 'Y'
FROM AMW_ASSESSMENT_COMPONENTS
WHERE OBJECT_TYPE='CONTROL'
AND OBJECT_ID=ACB.CONTROL_REV_ID
AND COMPONENT_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_ASSESSMENT_COMPONENTS'
AND TAG=6)) CCM6
, (SELECT 'Y'
FROM AMW_ASSESSMENT_COMPONENTS
WHERE OBJECT_TYPE='CONTROL'
AND OBJECT_ID=ACB.CONTROL_REV_ID
AND COMPONENT_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_ASSESSMENT_COMPONENTS'
AND TAG=7)) CCM7
, (SELECT 'Y'
FROM AMW_ASSESSMENT_COMPONENTS
WHERE OBJECT_TYPE='CONTROL'
AND OBJECT_ID=ACB.CONTROL_REV_ID
AND COMPONENT_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_ASSESSMENT_COMPONENTS'
AND TAG=8)) CCM8
, (SELECT 'Y'
FROM AMW_ASSESSMENT_COMPONENTS
WHERE OBJECT_TYPE='CONTROL'
AND OBJECT_ID=ACB.CONTROL_REV_ID
AND COMPONENT_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_ASSESSMENT_COMPONENTS'
AND TAG=9)) CCM9
, (SELECT 'Y'
FROM AMW_ASSESSMENT_COMPONENTS
WHERE OBJECT_TYPE='CONTROL'
AND OBJECT_ID=ACB.CONTROL_REV_ID
AND COMPONENT_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_ASSESSMENT_COMPONENTS'
AND TAG=10)) CCM10
, (SELECT 'Y'
FROM AMW_CONTROL_OBJECTIVES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND OBJECTIVE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_OBJECTIVES'
AND TAG=1)) CCT1
, (SELECT 'Y'
FROM AMW_CONTROL_OBJECTIVES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND OBJECTIVE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_OBJECTIVES'
AND TAG=2)) CCT2
, (SELECT 'Y'
FROM AMW_CONTROL_OBJECTIVES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND OBJECTIVE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_OBJECTIVES'
AND TAG=3)) CCT3
, (SELECT 'Y'
FROM AMW_CONTROL_OBJECTIVES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND OBJECTIVE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_OBJECTIVES'
AND TAG=4)) CCT4
, (SELECT 'Y'
FROM AMW_CONTROL_OBJECTIVES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND OBJECTIVE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_OBJECTIVES'
AND TAG=5)) CCT5
, (SELECT 'Y'
FROM AMW_CONTROL_OBJECTIVES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND OBJECTIVE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_OBJECTIVES'
AND TAG=6)) CCT6
, (SELECT 'Y'
FROM AMW_CONTROL_OBJECTIVES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND OBJECTIVE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_OBJECTIVES'
AND TAG=7)) CCT7
, (SELECT 'Y'
FROM AMW_CONTROL_OBJECTIVES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND OBJECTIVE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_OBJECTIVES'
AND TAG=8)) CCT8
, (SELECT 'Y'
FROM AMW_CONTROL_OBJECTIVES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND OBJECTIVE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_OBJECTIVES'
AND TAG=9)) CCT9
, (SELECT 'Y'
FROM AMW_CONTROL_OBJECTIVES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND OBJECTIVE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_OBJECTIVES'
AND TAG=10)) CCT10
, (SELECT 'Y'
FROM AMW_CONTROL_ASSERTIONS
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND ASSERTION_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_ASSERTIONS'
AND TAG=1)) CAS1
, (SELECT 'Y'
FROM AMW_CONTROL_ASSERTIONS
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND ASSERTION_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_ASSERTIONS'
AND TAG=2)) CAS2
, (SELECT 'Y'
FROM AMW_CONTROL_ASSERTIONS
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND ASSERTION_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_ASSERTIONS'
AND TAG=3)) CAS3
, (SELECT 'Y'
FROM AMW_CONTROL_ASSERTIONS
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND ASSERTION_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_ASSERTIONS'
AND TAG=4)) CAS4
, (SELECT 'Y'
FROM AMW_CONTROL_ASSERTIONS
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND ASSERTION_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_ASSERTIONS'
AND TAG=5)) CAS5
, (SELECT 'Y'
FROM AMW_CONTROL_ASSERTIONS
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND ASSERTION_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_ASSERTIONS'
AND TAG=6)) CAS6
, (SELECT 'Y'
FROM AMW_CONTROL_ASSERTIONS
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND ASSERTION_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_ASSERTIONS'
AND TAG=7)) CAS7
, (SELECT 'Y'
FROM AMW_CONTROL_ASSERTIONS
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND ASSERTION_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_ASSERTIONS'
AND TAG=8)) CAS8
, (SELECT 'Y'
FROM AMW_CONTROL_ASSERTIONS
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND ASSERTION_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_ASSERTIONS'
AND TAG=9)) CAS9
, (SELECT 'Y'
FROM AMW_CONTROL_ASSERTIONS
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND ASSERTION_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_ASSERTIONS'
AND TAG=10)) CAS10
, (SELECT 'Y'
FROM AMW_CONTROL_PURPOSES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND PURPOSE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_PURPOSES'
AND TAG=1)) CPP1
, (SELECT 'Y'
FROM AMW_CONTROL_PURPOSES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND PURPOSE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_PURPOSES'
AND TAG=2)) CPP2
, (SELECT 'Y'
FROM AMW_CONTROL_PURPOSES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND PURPOSE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_PURPOSES'
AND TAG=3)) CPP3
, (SELECT 'Y'
FROM AMW_CONTROL_PURPOSES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND PURPOSE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_PURPOSES'
AND TAG=4)) CPP4
, (SELECT 'Y'
FROM AMW_CONTROL_PURPOSES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND PURPOSE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_PURPOSES'
AND TAG=5)) CPP5
, (SELECT 'Y'
FROM AMW_CONTROL_PURPOSES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND PURPOSE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_PURPOSES'
AND TAG=6)) CPP6
, (SELECT 'Y'
FROM AMW_CONTROL_PURPOSES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND PURPOSE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_PURPOSES'
AND TAG=7)) CPP7
, (SELECT 'Y'
FROM AMW_CONTROL_PURPOSES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND PURPOSE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_PURPOSES'
AND TAG=8)) CPP8
, (SELECT 'Y'
FROM AMW_CONTROL_PURPOSES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND PURPOSE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_PURPOSES'
AND TAG=9)) CPP9
, (SELECT 'Y'
FROM AMW_CONTROL_PURPOSES
WHERE CONTROL_REV_ID=ACB.CONTROL_REV_ID
AND PURPOSE_CODE IN (SELECT LOOKUP_CODE
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_CONTROL_PURPOSES'
AND TAG=10)) CPP10
, AORV.PARTY_ID AS PARTY_ID
FROM AMW_CONTROLS_B ACB
, AMW_CONTROLS_TL ACT
, AMW_OWNER_ROLES_V AORV
WHERE ACB.CONTROL_REV_ID=ACT.CONTROL_REV_ID
AND ACT.LANGUAGE=USERENV('LANG')
AND ACB.APPROVAL_STATUS NOT IN ('P'
, 'R')
AND AORV.ROLE_NAME='AMW_CTRL_OWNER_ROLE'
AND ACB.CONTROL_ID=AORV.PK1_VALUE

Columns

Name
CID
CRID
CRNM
CEND
CCAF
CLRF
CNM
RC
CDS
CAPP
CAPDT
CLOC
CTYP
CAUT
CAPL
CSRC
CPEV
CBGR
CJOB
CPRV
CDET
CDIS
CKEY
CVSO
CVNM
CVIN
CUOMTL
CCF
CCLASS
CCM1
CCM2
CCM3
CCM4
CCM5
CCM6
CCM7
CCM8
CCM9
CCM10
CCT1
CCT2
CCT3
CCT4
CCT5
CCT6
CCT7
CCT8
CCT9
CCT10
CAS1
CAS2
CAS3
CAS4
CAS5
CAS6
CAS7
CAS8
CAS9
CAS10
CPP1
CPP2
CPP3
CPP4
CPP5
CPP6
CPP7
CPP8
CPP9
CPP10
PARTY_ID