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.

VIEW: APPS.AMW_EXPORT_CTRL_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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