View: AMW_EXPORT_AP_V

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

View Text

SELECT DISTINCT AAPB.AUDIT_PROCEDURE_ID APID
, AAPB.AUDIT_PROCEDURE_REV_ID APRID
, AAPB.AUDIT_PROCEDURE_REV_NUM APRNM
, AAPB.END_DATE APEND
, AAPB.APPROVAL_DATE APAPDT
, AAPB.CURR_APPROVED_FLAG APCAF
, AAPB.LATEST_REVISION_FLAG APLRF
, (SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_PRCDR_APPRV_STATUS'
AND LOOKUP_CODE=AAPB.APPROVAL_STATUS
AND ENABLED_FLAG='Y'
AND NVL(END_DATE_ACTIVE
, SYSDATE) >= SYSDATE) APAPP
, AAPT.NAME APNM
, '' RAP
, AAPT.DESCRIPTION APDS
, ACT.NAME CNM
, NVL(AAA.DESIGN_EFFECTIVENESS
, 'N') ADE
, NVL(AAA.OP_EFFECTIVENESS
, 'N') AOE
, AAST.NAME STPNM
, AASB.CSEQNUM STPNUM
, AAST.DESCRIPTION STDSC
, AASB.SAMPLESIZE STSS
, (SELECT WORK_TYPE_NAME
FROM AMW_OBJ_CLASSIFICATION_V
WHERE CATEGORY_CODE='AUDIT_PROCEDURE'
AND WORK_TYPE_ID=AAPB.CLASSIFICATION) APCLASS
, -100 PARTY_ID
FROM AMW_AUDIT_PROCEDURES_B AAPB
, AMW_AUDIT_PROCEDURES_TL AAPT
, AMW_AP_STEPS_B AASB
, AMW_AP_STEPS_TL AAST
, AMW_AP_ASSOCIATIONS AAA
, AMW_CONTROLS_B ACB
, AMW_CONTROLS_TL ACT
WHERE AAPB.AUDIT_PROCEDURE_REV_ID=AAPT.AUDIT_PROCEDURE_REV_ID
AND AAPT.LANGUAGE=USERENV('LANG')
AND AAPB.APPROVAL_STATUS NOT IN ('P'
, 'R')
AND AAPB.AUDIT_PROCEDURE_ID=AASB.AUDIT_PROCEDURE_ID
AND AASB.AP_STEP_ID=AAST.AP_STEP_ID
AND AAST.LANGUAGE=USERENV('LANG')
AND AAPB.AUDIT_PROCEDURE_REV_NUM >= AASB.FROM_REV_NUM
AND AAPB.AUDIT_PROCEDURE_REV_NUM < NVL(AASB.TO_REV_NUM
, AAPB.AUDIT_PROCEDURE_REV_NUM + 1)
AND AAPB.AUDIT_PROCEDURE_ID=AAA.AUDIT_PROCEDURE_ID(+)
AND AAA.OBJECT_TYPE(+)='CTRL'
AND AAA.PK1=ACB.CONTROL_ID
AND AAA.DELETION_DATE IS NULL
AND ACB.CONTROL_REV_ID=ACT.CONTROL_REV_ID
AND ACB.CURR_APPROVED_FLAG='Y'
AND ACT.LANGUAGE=USERENV('LANG')
AND AAPB.LATEST_REVISION_FLAG='Y' UNION ALL SELECT DISTINCT AAPB.AUDIT_PROCEDURE_ID APID
, AAPB.AUDIT_PROCEDURE_REV_ID APRID
, AAPB.AUDIT_PROCEDURE_REV_NUM APRNM
, AAPB.END_DATE APEND
, AAPB.APPROVAL_DATE APAPDT
, AAPB.CURR_APPROVED_FLAG APCAF
, AAPB.LATEST_REVISION_FLAG APLRF
, (SELECT MEANING
FROM AMW_LOOKUPS
WHERE LOOKUP_TYPE='AMW_PRCDR_APPRV_STATUS'
AND LOOKUP_CODE=AAPB.APPROVAL_STATUS
AND ENABLED_FLAG='Y'
AND NVL(END_DATE_ACTIVE
, SYSDATE) >= SYSDATE) APAPP
, AAPT.NAME APNM
, '' RAP
, AAPT.DESCRIPTION APDS
, ACT.NAME CNM
, NVL(AAA.DESIGN_EFFECTIVENESS
, 'N') ADE
, NVL(AAA.OP_EFFECTIVENESS
, 'N') AOE
, AAST.NAME STPNM
, AASB.CSEQNUM STPNUM
, AAST.DESCRIPTION STDSC
, AASB.SAMPLESIZE STSS
, (SELECT WORK_TYPE_NAME
FROM AMW_OBJ_CLASSIFICATION_V
WHERE CATEGORY_CODE='AUDIT_PROCEDURE'
AND WORK_TYPE_ID=AAPB.CLASSIFICATION) APCLASS
, AORV.PARTY_ID AS PARTY_ID
FROM AMW_AUDIT_PROCEDURES_B AAPB
, AMW_AUDIT_PROCEDURES_TL AAPT
, AMW_AP_STEPS_B AASB
, AMW_AP_STEPS_TL AAST
, AMW_AP_ASSOCIATIONS AAA
, AMW_CONTROLS_B ACB
, AMW_CONTROLS_TL ACT
, AMW_OWNER_ROLES_V AORV
WHERE AAPB.AUDIT_PROCEDURE_REV_ID=AAPT.AUDIT_PROCEDURE_REV_ID
AND AAPT.LANGUAGE=USERENV('LANG')
AND AAPB.APPROVAL_STATUS NOT IN ('P'
, 'R')
AND AAPB.AUDIT_PROCEDURE_ID=AASB.AUDIT_PROCEDURE_ID
AND AASB.AP_STEP_ID=AAST.AP_STEP_ID
AND AAST.LANGUAGE=USERENV('LANG')
AND AAPB.AUDIT_PROCEDURE_REV_NUM >= AASB.FROM_REV_NUM
AND AAPB.AUDIT_PROCEDURE_REV_NUM < NVL(AASB.TO_REV_NUM
, AAPB.AUDIT_PROCEDURE_REV_NUM + 1)
AND AAPB.AUDIT_PROCEDURE_ID=AAA.AUDIT_PROCEDURE_ID(+)
AND AAA.OBJECT_TYPE(+)='CTRL'
AND AAA.PK1=ACB.CONTROL_ID
AND AAA.DELETION_DATE IS NULL
AND ACB.CONTROL_REV_ID=ACT.CONTROL_REV_ID
AND ACB.CURR_APPROVED_FLAG='Y'
AND ACT.LANGUAGE=USERENV('LANG')
AND AAPB.LATEST_REVISION_FLAG='Y'
AND AORV.ROLE_NAME='AMW_AP_OWNER_ROLE'
AND AAPB.AUDIT_PROCEDURE_ID=AORV.PK1_VALUE

Columns