VIEW: APPS.AMW_EXPORT_AP_V

Source

View Text - Preformatted

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.APPROVAL_DATE(+) IS NOT NULL AND AAA.DELETION_APPROVAL_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.APPROVAL_DATE(+) IS NOT NULL AND AAA.DELETION_APPROVAL_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

View Text - HTML Formatted

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.APPROVAL_DATE(+) IS NOT NULL
AND AAA.DELETION_APPROVAL_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.APPROVAL_DATE(+) IS NOT NULL
AND AAA.DELETION_APPROVAL_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