[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Next_Level_Proc_Info(p_process_id IN NUMBER, p_org_id IN NUMBER, p_certification_id IN NUMBER) is
cursor c2 (l_process_id number,l_org_id number) is
select CHILD_PROCESS_ID
from Amw_Process_Org_Relations
where PARENT_PROCESS_ID=l_process_id and ORGANIZATION_ID = l_org_id;
select opinion.audit_result INTO l_audit_result
FROM AMW_OPINIONS_V opinion, AMW_OPINION_TYPES_TL opiniontype, FND_OBJECTS fndobject, AMW_OBJECT_OPINION_TYPES objectopiniontype
WHERE
(opinion.AUTHORED_DATE in (Select MAX(opinion.AUTHORED_DATE)
from AMW_OPINIONS_V opinion
Group By PK1_VALUE)) AND
opinion.PK3_VALUE = p_org_id AND
opinion.PK2_VALUE = p_certification_id AND
opinion.PK1_VALUE = c2_rec.CHILD_PROCESS_ID AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_NAME = 'Certification' AND
opiniontype.LANGUAGE = 'US' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORG_PROCESS';
Update AMW_PROC_CERT_EVAL_SUM
SET SUB_PROCESS_CERT = l_sum_certified,TOTAL_SUB_PROCESS_CERT = l_subprocess_total, LAST_UPDATE_DATE = SYSDATE, LAST_UPDATED_BY = G_USER_ID
where PROCESS_ID = p_process_id and CERTIFICATION_ID = p_certification_id and ORGANIZATION_ID = p_org_id;
fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in Update_Next_Level_Proc_Info'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in Update_Next_Level_Proc_Info'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
end Update_Next_Level_Proc_Info;
Procedure Update_Certification_Detail(p_process_id IN NUMBER, p_org_id IN NUMBER, p_certification_id IN NUMBER) is
BEGIN
g_user_id := fnd_global.user_id;
Update AMW_PROC_CERT_EVAL_SUM
SET (CERTIFICATION_OPINION_ID) =
(select opinion.OPINION_ID
FROM AMW_OPINIONS_V opinion, AMW_OPINION_TYPES_TL opiniontype, FND_OBJECTS fndobject, AMW_OBJECT_OPINION_TYPES objectopiniontype
WHERE
(opinion.AUTHORED_DATE in (Select MAX(AMW_OPINIONS_V.AUTHORED_DATE)
from AMW_OPINIONS_V
Group By PK1_VALUE)) AND
opinion.PK3_VALUE = p_org_id AND
opinion.PK2_VALUE = p_certification_id AND
opinion.PK1_VALUE = p_process_id AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_NAME = 'Certification' AND
opiniontype.LANGUAGE = 'US' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORG_PROCESS'), LAST_UPDATE_DATE = SYSDATE, LAST_UPDATED_BY = G_USER_ID
WHERE PROCESS_ID = p_process_id and CERTIFICATION_ID = p_certification_id and ORGANIZATION_ID = p_org_id;
fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in Update_Certification_Detail'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in Update_Certification_Detail'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
END Update_Certification_Detail;
PROCEDURE UPDATE_GLOBAL_PROC_INFO(p_process_id IN NUMBER, p_certification_id IN NUMBER, p_global_org_id IN NUMBER) is
l_total_org_cert NUMBER;
Update AMW_PROC_CERT_EVAL_SUM
SET TOTAL_ORG_PROCESS_CERT =
(Select distinct count(*) from AMW_PROCESS_ORGANIZATION processorg where
(processorg.PROCESS_ID = p_process_id) and
(processorg.ORGANIZATION_ID = p_global_org_id))
, GLOBAL_PROCESS ='Y', LAST_UPDATE_DATE = SYSDATE, LAST_UPDATED_BY = G_USER_ID
where PROCESS_ID = p_process_id and CERTIFICATION_ID = p_certification_id
and ORGANIZATION_ID = p_global_org_id;
Update AMW_PROC_CERT_EVAL_SUM
SET ORG_PROCESS_CERT =
(
select distinct count(*)
FROM AMW_OPINIONS_V opinion, AMW_OPINION_TYPES_TL opiniontype, FND_OBJECTS fndobject, AMW_OBJECT_OPINION_TYPES objectopiniontype
WHERE
(opinion.AUTHORED_DATE in (Select MAX(AMW_OPINIONS_V.AUTHORED_DATE)
from AMW_OPINIONS_V
Group By PK1_VALUE)) AND
opinion.PK2_VALUE = p_certification_id AND
opinion.PK1_VALUE = p_process_id AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_NAME = 'Certification' AND
opiniontype.LANGUAGE = 'US' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORG_PROCESS' AND
((opinion.AUDIT_RESULT = 'Certified') OR (opinion.AUDIT_RESULT = 'Certified with Issues'))
), LAST_UPDATE_DATE = SYSDATE, LAST_UPDATED_BY = G_USER_ID
WHERE PROCESS_ID = p_process_id and CERTIFICATION_ID = p_certification_id and ORGANIZATION_ID = p_global_org_id;
fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in Update_Global_Proc_Info'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in Update_Global_Proc_Info'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
END UPDATE_GLOBAL_PROC_INFO;
PROCEDURE UPDATE_LAST_EVALUATION_INFO(p_process_id IN NUMBER, p_org_id IN NUMBER,p_certification_id IN NUMBER) is
BEGIN
g_user_id := fnd_global.user_id;
Update AMW_PROC_CERT_EVAL_SUM
SET (EVALUATION_OPINION_ID) =
(
select opinion.OPINION_ID
FROM AMW_OPINIONS_V opinion, AMW_OPINION_TYPES_TL opiniontype, FND_OBJECTS fndobject, AMW_OBJECT_OPINION_TYPES objectopiniontype
WHERE
(opinion.AUTHORED_DATE in (Select MAX(AMW_OPINIONS_V.AUTHORED_DATE)
from AMW_OPINIONS_V
Group By PK1_VALUE)) AND
opinion.PK1_VALUE = p_process_id AND
opinion.PK3_VALUE = p_org_id AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_NAME = 'Evaluation' AND
opiniontype.LANGUAGE = 'US' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORG_PROCESS'
) , LAST_UPDATE_DATE = SYSDATE, LAST_UPDATED_BY = G_USER_ID
WHERE PROCESS_ID = p_process_id and CERTIFICATION_ID = p_certification_id and ORGANIZATION_ID = p_org_id;
fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in Update_Last_Evaluation_Info'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in Update_Last_Evaluation_Info'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
END UPDATE_LAST_EVALUATION_INFO;
PROCEDURE UPDATE_UNMITIGATED_RISKS(p_process_id IN NUMBER, p_org_id IN NUMBER, p_certification_id IN NUMBER) is
BEGIN
g_user_id := fnd_global.user_id;
Update AMW_PROC_CERT_EVAL_SUM
SET UNMITIGATED_RISKS =
(
select distinct count(*)
from amw_risk_associations assoctable,
amw_risks_all_vl risktable,
amw_audit_units_v orgtable,
amw_wf_org_hierarchy_main_v processtable,
fnd_objects fo,
amw_opinion_types_tl optypes,
AMW_OBJECT_OPINION_TYPES objoptypes,
amw_opinions_v opinionstable,
pa_project_lists_v pap,
AMW_PROCESS_ORGANIZATION procorg
where assoctable.object_type = 'PROCESS_ORG'
and orgtable.organization_id = procorg.organization_id
and processtable.process_id = procorg.process_id
and assoctable.risk_id = risktable.risk_id
and assoctable.pk1 = procorg.PROCESS_ORGANIZATION_ID
and opinionstable.pk1_value = assoctable.risk_id
and opinionstable.pk3_value = procorg.organization_id
and opinionstable.pk3_value = p_org_id
and opinionstable.pk4_value = procorg.process_id
and opinionstable.pk4_value = p_process_id
and opinionstable.pk5_value is null
and opinionstable.object_opinion_type_id =
objoptypes.object_opinion_type_id
and opinionstable.last_update_date in
( select max ( a.last_update_date )
from amw_opinions_v a
where a.pk1_value = opinionstable.pk1_value
and a.pk3_value = opinionstable.pk3_value
and a.pk4_value = opinionstable.pk4_value
and a.object_opinion_type_id =
opinionstable.object_opinion_type_id
group by a.pk1_value )
and opinionstable.audit_result_code = 'INEFFECTIVE'
and pap.project_id = opinionstable.pk2_value
and pap.project_status_code = 'CLOSED'
and objoptypes.object_id = fo.object_id
and fo.obj_name = 'AMW_ORG_PROCESS_RISK'
and objoptypes.opinion_type_id = optypes.opinion_type_id
and optypes.opinion_type_name = 'Evaluation'
and optypes.LANGUAGE = 'US'
) , LAST_UPDATE_DATE = SYSDATE, LAST_UPDATED_BY = G_USER_ID
WHERE PROCESS_ID = p_process_id and CERTIFICATION_ID = p_certification_id and ORGANIZATION_ID = p_org_id;
fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in Update_Unmitigated_Risks'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in Update_Unmitigated_Risks'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
END UPDATE_UNMITIGATED_RISKS;
PROCEDURE UPDATE_INEFFECTIVE_CONTROLS(p_process_id IN NUMBER, p_org_id IN NUMBER, p_certification_id IN NUMBER) is
BEGIN
g_user_id := fnd_global.user_id;
Update AMW_PROC_CERT_EVAL_SUM
SET INEFFECTIVE_CONTROLS =
(select distinct Count(*)
from amw_control_associations ctrlassoc,
amw_risk_associations riskassoc,
amw_process_organization procorg,
amw_controls_all_vl controltable,
amw_audit_units_v orgtable,
amw_wf_org_hierarchy_main_v processtable,
fnd_objects fo,
amw_opinion_types_tl optypes,
AMW_OBJECT_OPINION_TYPES objoptypes,
pa_project_lists_v pap,
amw_opinions_v opinionstable
where procorg.process_id = processtable.process_id
and procorg.process_id = p_process_id
and procorg.organization_id = orgtable.organization_id
and procorg.organization_id = p_org_id
and procorg.process_organization_id = riskassoc.pk1
and riskassoc.object_type = 'PROCESS_ORG'
and riskassoc.risk_association_id = ctrlassoc.pk1
and ctrlassoc.object_type = 'RISK_ORG'
and ctrlassoc.control_id = controltable.control_id
and opinionstable.pk1_value = controltable.control_id
and opinionstable.pk3_value = orgtable.organization_id
and opinionstable.pk4_value is null
and opinionstable.pk5_value is null
and opinionstable.object_opinion_type_id =
objoptypes.object_opinion_type_id
and opinionstable.last_update_date in
( select max ( a.last_update_date )
from amw_opinions_v a
where a.pk1_value = opinionstable.pk1_value
and a.pk3_value = opinionstable.pk3_value
and a.object_opinion_type_id =
opinionstable.object_opinion_type_id
group by a.pk1_value )
and opinionstable.audit_result_code = 'INEFFECTIVE'
and pap.project_id = opinionstable.pk2_value
and pap.project_status_code = 'CLOSED'
and objoptypes.object_id = fo.object_id
and fo.obj_name = 'AMW_ORG_CONTROL'
and objoptypes.opinion_type_id = optypes.opinion_type_id
and optypes.opinion_type_name = 'Evaluation'
and optypes.LANGUAGE = 'US'
), LAST_UPDATE_DATE = SYSDATE, LAST_UPDATED_BY = G_USER_ID
WHERE PROCESS_ID = p_process_id and CERTIFICATION_ID = p_certification_id and ORGANIZATION_ID = p_org_id;
fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in Update_Ineffective_Controls'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in Update_Ineffective_Controls'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
END UPDATE_INEFFECTIVE_CONTROLS;
select process_id, organization_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc,
AMW_ACCT_ASSOCIATIONS acct_assoc, AMW_PROCESS_ORGANIZATION process_org
where cert.certification_id = p_certification_id and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id and
cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID and
acct_assoc.natural_account_id = key_acc.natural_account_id and
acct_assoc.object_type = 'PROCESS_ORG' and
process_org.process_organization_id = acct_assoc.pk1;
select count(*) into l_count from amw_proc_cert_eval_sum
where certification_id = p_certification_id and
process_id = proc_rec.process_id and organization_id = proc_rec.organization_id;
INSERT into AMW_PROC_CERT_EVAL_SUM(CERTIFICATION_ID, PROCESS_ID, ORGANIZATION_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, lAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
VALUES (p_certification_id, proc_rec.PROCESS_ID, proc_rec.ORGANIZATION_ID,G_USER_ID,SYSDATE,G_USER_ID,SYSDATE,G_LOGIN_ID);
Update_Next_Level_Proc_Info(proc_rec.PROCESS_ID, proc_rec.ORGANIZATION_ID, p_certification_id);
Update_Certification_Detail(proc_rec.PROCESS_ID, proc_rec.ORGANIZATION_ID, p_certification_id);
then UPDATE_GLOBAL_PROC_INFO(proc_rec.PROCESS_ID, p_certification_id, l_global_org_id);
UPDATE_LAST_EVALUATION_INFO(proc_rec.PROCESS_ID, proc_rec.ORGANIZATION_ID, p_certification_id);
UPDATE_UNMITIGATED_RISKS(proc_rec.PROCESS_ID, proc_rec.ORGANIZATION_ID, p_certification_id);
UPDATE_INEFFECTIVE_CONTROLS(proc_rec.PROCESS_ID, proc_rec.ORGANIZATION_ID, p_certification_id);
Select distinct CERTIFICATION_ID
from AMW_CERTIFICATION_VL
where object_type = 'FIN_STMT' and
CERTIFICATION_STATUS in ('ACTIVE','DRAFT');
SELECT count(1)
FROM AMW_RISK_ASSOCIATIONS
WHERE creation_date >= p_start_date
AND object_type = 'PROCESS_ORG'
AND pk1 in (
select distinct p_org.process_organization_id
from AMW_ACCT_ASSOCIATIONS acct_assoc,
AMW_PROCESS_ORGANIZATION process_org,
AMW_PROCESS_ORGANIZATION p_org,
(select process_id, organization_id, parent_child_id child_process_id, up_down_ind
from Amw_Org_Hierarchy_Denorm
union
select process_id, organization_id, process_id child_process_id, 'D' up_down_ind
from amw_process_organization) hier
where acct_assoc.natural_account_id in
-- (select natural_account_id
-- from amw_fin_key_accounts_b
-- start with (natural_account_id, account_group_id) in
-- (select natural_account_id, account_group_id
-- from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
-- where cert.certification_id = p_certification_id and
-- cert.object_type = 'FIN_STMT' and
-- cert.statement_group_id = key_acc.statement_group_id and
-- cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID)
-- connect by parent_natural_account_id = PRIOR natural_account_id and
-- account_group_id = PRIOR account_group_id)
-- Note by Sanket: The above connect by was replaced by the below query
-- which uses the newly created flat table. We have to use a union because
-- the first part of the query only returns the children (if any) of the (natural
-- account id = x, account group id = y) pair returned by the subquery, while the
-- second part of the query returns the actual pair (x, y). Such unions
-- are used in all the flattened queries in this file.
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
)
and acct_assoc.object_type = 'PROCESS_ORG' and
process_org.process_organization_id = acct_assoc.pk1 and
hier.process_id = process_org.process_id and
hier.organization_id = process_org.organization_id and
hier.up_down_ind = 'D' and
p_org.process_id = hier.child_process_id and
p_org.organization_id = hier.organization_id
);
SELECT count(1)
FROM (
SELECT distinct aca.control_id, p_org.organization_id
FROM AMW_CONTROL_ASSOCIATIONS aca,
AMW_RISK_ASSOCIATIONS ara,
AMW_PROCESS_ORGANIZATION apo,
AMW_ACCT_ASSOCIATIONS acct_assoc,
AMW_PROCESS_ORGANIZATION p_org,
(select process_id, organization_id, parent_child_id child_process_id, up_down_ind
from Amw_Org_Hierarchy_Denorm
union
select process_id, organization_id, process_id child_process_id, 'D' up_down_ind
from amw_process_organization) hier
WHERE acct_assoc.natural_account_id in
-- (select natural_account_id
-- from amw_fin_key_accounts_b
-- start with (natural_account_id, account_group_id) in
-- (select natural_account_id, account_group_id
-- from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
-- where cert.certification_id = p_certification_id and
-- cert.object_type = 'FIN_STMT' and
-- cert.statement_group_id = key_acc.statement_group_id and
-- cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID)
-- connect by parent_natural_account_id = PRIOR natural_account_id and
-- account_group_id = PRIOR account_group_id)
-- Modifed for flatting key_acct table
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
)
AND acct_assoc.object_type = 'PROCESS_ORG'
AND apo.process_organization_id = acct_assoc.pk1
AND hier.process_id = apo.process_id
AND hier.organization_id = apo.organization_id
AND hier.up_down_ind = 'D'
AND p_org.process_id = hier.child_process_id
AND p_org.organization_id = hier.organization_id
AND ara.object_type = 'PROCESS_ORG'
AND ara.pk1 = p_org.process_organization_id
AND aca.creation_date >= p_start_date
AND aca.object_type = 'RISK_ORG'
AND aca.pk1 = ara.risk_association_id
AND not exists (SELECT 'Y'
FROM AMW_CONTROL_ASSOCIATIONS aca2,
AMW_RISK_ASSOCIATIONS ara2
WHERE aca2.creation_date < p_start_date
AND aca2.object_type = 'RISK_ORG'
AND aca2.pk1 = ara2.risk_association_id
AND ara2.object_type = 'PROCESS_ORG'
AND ara2.pk1 = p_org.process_organization_id
AND aca2.control_id = aca.control_id
));
SELECT count(1)
FROM (
SELECT distinct hier.organization_id, hier.child_process_id
FROM AMW_PROCESS_ORGANIZATION apo,
AMW_ACCT_ASSOCIATIONS acct_assoc,
(select process_id, organization_id, parent_child_id child_process_id, up_down_ind
from Amw_Org_Hierarchy_Denorm
union
select process_id, organization_id, process_id child_process_id, 'D' up_down_ind
from amw_process_organization) hier
WHERE acct_assoc.natural_account_id in
-- (select natural_account_id
-- from amw_fin_key_accounts_b
-- start with (natural_account_id, account_group_id) in
-- (select natural_account_id, account_group_id
-- from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
-- where cert.certification_id = p_certification_id and
-- cert.object_type = 'FIN_STMT' and
-- cert.statement_group_id = key_acc.statement_group_id and
-- cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID)
-- connect by parent_natural_account_id = PRIOR natural_account_id and
-- account_group_id = PRIOR account_group_id)
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
)
AND acct_assoc.object_type = 'PROCESS_ORG'
AND apo.process_organization_id = acct_assoc.pk1
AND apo.organization_id = fnd_profile.value('AMW_GLOBAL_ORG_ID')
AND apo.process_id = hier.process_id
AND apo.organization_id = hier.organization_id
AND hier.up_down_ind = 'D'
AND not exists (SELECT 'Y'
FROM AMW_OPINIONS_V aov
WHERE aov.object_name = 'AMW_ORG_PROCESS'
AND aov.opinion_type_code = 'CERTIFICATION'
AND aov.pk3_value = hier.organization_id
AND aov.pk2_value in (select proc_cert_id from AMW_FIN_PROC_CERT_RELAN where fin_stmt_cert_id = p_certification_id)
AND aov.pk1_value = hier.child_process_id)
);
SELECT count(1)
FROM (
SELECT distinct hier.organization_id, hier.child_process_id
FROM AMW_PROCESS_ORGANIZATION apo,
AMW_ACCT_ASSOCIATIONS acct_assoc,
AMW_OPINIONS_V aov,
(select process_id, organization_id, parent_child_id child_process_id, up_down_ind
from Amw_Org_Hierarchy_Denorm
union
select process_id, organization_id, process_id child_process_id, 'D' up_down_ind
from amw_process_organization) hier
WHERE acct_assoc.natural_account_id in
-- (select natural_account_id
-- from amw_fin_key_accounts_b
-- start with (natural_account_id, account_group_id) in
-- (select natural_account_id, account_group_id
-- from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
-- where cert.certification_id = p_certification_id and
-- cert.object_type = 'FIN_STMT' and
-- cert.statement_group_id = key_acc.statement_group_id and
-- cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID)
-- connect by parent_natural_account_id = PRIOR natural_account_id and
-- account_group_id = PRIOR account_group_id)
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
)
AND acct_assoc.object_type = 'PROCESS_ORG'
AND apo.process_organization_id = acct_assoc.pk1
AND apo.organization_id = fnd_profile.value('AMW_GLOBAL_ORG_ID')
AND hier.process_id = apo.process_id
AND hier.organization_id = apo.organization_id
AND hier.up_down_ind = 'D'
AND aov.object_name = 'AMW_ORG_PROCESS'
AND aov.opinion_type_code = 'CERTIFICATION'
AND aov.pk3_value = hier.organization_id
AND aov.pk2_value in (select proc_cert_id from AMW_FIN_PROC_CERT_RELAN where fin_stmt_cert_id = p_certification_id)
AND aov.pk1_value = hier.child_process_id
AND aov.audit_result_code <> 'EFFECTIVE'
);
SELECT count(1)
FROM (SELECT distinct hier.organization_id, hier.child_process_id
FROM AMW_PROCESS_ORGANIZATION apo,
AMW_ACCT_ASSOCIATIONS acct_assoc,
(select process_id, organization_id, parent_child_id child_process_id, up_down_ind
from Amw_Org_Hierarchy_Denorm
union
select process_id, organization_id, process_id child_process_id, 'D' up_down_ind
from amw_process_organization) hier
WHERE acct_assoc.natural_account_id in
-- (select natural_account_id
-- from amw_fin_key_accounts_b
-- start with (natural_account_id, account_group_id) in
-- (select natural_account_id, account_group_id
-- from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
-- where cert.certification_id = p_certification_id and
-- cert.object_type = 'FIN_STMT' and
-- cert.statement_group_id = key_acc.statement_group_id and
-- cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID)
-- connect by parent_natural_account_id = PRIOR natural_account_id and
-- account_group_id = PRIOR account_group_id)
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
)
AND acct_assoc.object_type = 'PROCESS_ORG'
AND apo.process_organization_id = acct_assoc.pk1
AND apo.organization_id <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999)
AND hier.process_id = apo.process_id
AND hier.organization_id = apo.organization_id
AND hier.up_down_ind = 'D'
AND not exists (SELECT 'Y'
FROM AMW_OPINIONS_V aov
WHERE aov.object_name = 'AMW_ORG_PROCESS'
AND aov.opinion_type_code = 'CERTIFICATION'
AND aov.pk3_value = hier.organization_id
AND aov.pk2_value in (select proc_cert_id from AMW_FIN_PROC_CERT_RELAN where fin_stmt_cert_id = p_certification_id)
AND aov.pk1_value = hier.child_process_id));
SELECT count(1)
FROM (SELECT distinct hier.organization_id, hier.child_process_id
FROM AMW_PROCESS_ORGANIZATION apo,
AMW_ACCT_ASSOCIATIONS acct_assoc,
AMW_OPINIONS_V aov,
(select process_id, organization_id, parent_child_id child_process_id, up_down_ind
from Amw_Org_Hierarchy_Denorm
union
select process_id, organization_id, process_id child_process_id, 'D' up_down_ind
from amw_process_organization) hier
WHERE acct_assoc.natural_account_id in
-- (select natural_account_id
-- from amw_fin_key_accounts_b
-- start with (natural_account_id, account_group_id) in
-- (select natural_account_id, account_group_id
-- from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
-- where cert.certification_id = p_certification_id and
-- cert.object_type = 'FIN_STMT' and
-- cert.statement_group_id = key_acc.statement_group_id and
-- cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID)
-- connect by parent_natural_account_id = PRIOR natural_account_id and
-- account_group_id = PRIOR account_group_id)
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
)
AND acct_assoc.object_type = 'PROCESS_ORG'
AND apo.process_organization_id = acct_assoc.pk1
AND apo.organization_id <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'), -999)
AND hier.process_id = apo.process_id
AND hier.organization_id = apo.organization_id
AND hier.up_down_ind = 'D'
AND aov.object_name = 'AMW_ORG_PROCESS'
AND aov.opinion_type_code = 'CERTIFICATION'
AND aov.pk3_value = hier.organization_id
AND aov.pk2_value in (select proc_cert_id from AMW_FIN_PROC_CERT_RELAN where fin_stmt_cert_id = p_certification_id)
AND aov.pk1_value = hier.child_process_id
AND aov.audit_result_code <> 'EFFECTIVE');
SELECT count(distinct hier.child_process_id)
FROM AMW_PROCESS_ORGANIZATION apo,
AMW_ACCT_ASSOCIATIONS acct_assoc,
AMW_OPINIONS_V aov,
(select process_id, organization_id, parent_child_id child_process_id, up_down_ind
from Amw_Org_Hierarchy_Denorm
union
select process_id, organization_id, process_id child_process_id, 'D' up_down_ind
from amw_process_organization) hier
WHERE acct_assoc.natural_account_id in
-- (select natural_account_id
-- from amw_fin_key_accounts_b
-- start with (natural_account_id, account_group_id) in
-- (select natural_account_id, account_group_id
-- from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
-- where cert.certification_id = p_certification_id and
-- cert.object_type = 'FIN_STMT' and
-- cert.statement_group_id = key_acc.statement_group_id and
-- cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID)
-- connect by parent_natural_account_id = PRIOR natural_account_id and
-- account_group_id = PRIOR account_group_id)
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
)
AND acct_assoc.object_type = 'PROCESS_ORG'
AND apo.process_organization_id = acct_assoc.pk1
AND apo.organization_id = fnd_profile.value('AMW_GLOBAL_ORG_ID')
AND hier.process_id = apo.process_id
AND hier.organization_id = apo.organization_id
AND hier.up_down_ind = 'D'
AND aov.object_name = 'AMW_ORG_PROCESS'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = hier.organization_id
AND aov.pk1_value = hier.child_process_id
AND aov.authored_date = (select max(aov2.authored_date)
from AMW_OPINIONS_V aov2
where aov2.object_opinion_type_id = aov.object_opinion_type_id
and aov2.pk3_value = aov.pk3_value
and aov2.pk1_value = aov.pk1_value)
and aov.audit_result_code <> 'EFFECTIVE';
SELECT count(1)
FROM (SELECT distinct hier.organization_id, hier.child_process_id
FROM AMW_PROCESS_ORGANIZATION apo,
AMW_ACCT_ASSOCIATIONS acct_assoc,
AMW_OPINIONS_V aov,
(select process_id, organization_id, parent_child_id child_process_id, up_down_ind
from Amw_Org_Hierarchy_Denorm
union
select process_id, organization_id, process_id child_process_id, 'D' up_down_ind
from amw_process_organization) hier
WHERE acct_assoc.natural_account_id in
-- (select natural_account_id
-- from amw_fin_key_accounts_b
-- start with (natural_account_id, account_group_id) in
-- (select natural_account_id, account_group_id
-- from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
-- where cert.certification_id = p_certification_id and
-- cert.object_type = 'FIN_STMT' and
-- cert.statement_group_id = key_acc.statement_group_id and
-- cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID)
-- connect by parent_natural_account_id = PRIOR natural_account_id and
-- account_group_id = PRIOR account_group_id)
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
)
AND acct_assoc.object_type = 'PROCESS_ORG'
AND apo.process_organization_id = acct_assoc.pk1
AND apo.organization_id <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'), -999)
AND hier.process_id = apo.process_id
AND hier.organization_id = apo.organization_id
AND hier.up_down_ind = 'D'
AND aov.object_name = 'AMW_ORG_PROCESS'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = hier.organization_id
AND aov.pk1_value = hier.child_process_id
AND aov.authored_date = (select max(aov2.authored_date)
from AMW_OPINIONS_V aov2
where aov2.object_opinion_type_id = aov.object_opinion_type_id
and aov2.pk3_value = aov.pk3_value
AND aov.audit_result_code <> 'EFFECTIVE'
and aov2.pk1_value = aov.pk1_value));
SELECT count(1)
FROM (SELECT distinct p_org.organization_id, p_org.process_id, ara.risk_id
FROM AMW_PROCESS_ORGANIZATION apo,
AMW_ACCT_ASSOCIATIONS acct_assoc,
AMW_RISK_ASSOCIATIONS ara,
AMW_OPINIONS_V aov,
AMW_PROCESS_ORGANIZATION p_org,
(select process_id, organization_id, parent_child_id child_process_id, up_down_ind
from Amw_Org_Hierarchy_Denorm
union
select process_id, organization_id, process_id child_process_id, 'D' up_down_ind
from amw_process_organization) hier
WHERE acct_assoc.natural_account_id in
-- (select natural_account_id
-- from amw_fin_key_accounts_b
-- start with (natural_account_id, account_group_id) in
-- (select natural_account_id, account_group_id
-- from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
-- where cert.certification_id = p_certification_id and
-- cert.object_type = 'FIN_STMT' and
-- cert.statement_group_id = key_acc.statement_group_id and
-- cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID)
-- connect by parent_natural_account_id = PRIOR natural_account_id and
-- account_group_id = PRIOR account_group_id)
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
)
AND acct_assoc.object_type = 'PROCESS_ORG'
AND apo.process_organization_id = acct_assoc.pk1
AND hier.process_id = apo.process_id
AND hier.organization_id = apo.organization_id
AND hier.up_down_ind = 'D'
AND p_org.process_id = hier.child_process_id
AND p_org.organization_id = hier.organization_id
AND ara.object_type = 'PROCESS_ORG'
AND ara.pk1 = p_org.process_organization_id
AND aov.object_name = 'AMW_ORG_PROCESS_RISK'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = p_org.organization_id
AND aov.pk4_value = p_org.process_id
AND aov.pk1_value = ara.risk_id
AND aov.authored_date =
(select max(aov2.authored_date)
from AMW_OPINIONS_V aov2
where aov2.object_opinion_type_id = aov.object_opinion_type_id
and aov2.pk4_value = aov.pk4_value
and aov2.pk3_value = aov.pk3_value
and aov2.pk1_value = aov.pk1_value)
AND aov.audit_result_code <> 'EFFECTIVE');
SELECT count(1)
FROM (SELECT distinct p_org.organization_id, aca.control_id
FROM AMW_PROCESS_ORGANIZATION apo,
AMW_ACCT_ASSOCIATIONS acct_assoc,
AMW_RISK_ASSOCIATIONS ara, AMW_CONTROL_ASSOCIATIONS aca,
AMW_OPINIONS_V aov,
AMW_PROCESS_ORGANIZATION p_org,
(select process_id, organization_id, parent_child_id child_process_id, up_down_ind
from Amw_Org_Hierarchy_Denorm
union
select process_id, organization_id, process_id child_process_id, 'D' up_down_ind
from amw_process_organization) hier
WHERE acct_assoc.natural_account_id in
-- (select natural_account_id
-- from amw_fin_key_accounts_b
-- start with (natural_account_id, account_group_id) in
-- (select natural_account_id, account_group_id
-- from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
-- where cert.certification_id = p_certification_id and
-- cert.object_type = 'FIN_STMT' and
-- cert.statement_group_id = key_acc.statement_group_id and
-- cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID)
-- connect by parent_natural_account_id = PRIOR natural_account_id and
-- account_group_id = PRIOR account_group_id)
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
)
AND acct_assoc.object_type = 'PROCESS_ORG'
AND apo.process_organization_id = acct_assoc.pk1
AND hier.process_id = apo.process_id
AND hier.organization_id = apo.organization_id
AND hier.up_down_ind = 'D'
AND p_org.process_id = hier.child_process_id
AND p_org.organization_id = hier.organization_id
AND ara.object_type = 'PROCESS_ORG'
AND ara.pk1 = p_org.process_organization_id
AND aca.object_type = 'RISK_ORG'
AND aca.pk1 = ara.risk_association_id
AND aov.object_name = 'AMW_ORG_CONTROL'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = p_org.organization_id
AND aov.pk1_value = aca.control_id
AND aov.authored_date =
(select max(aov2.authored_date)
from AMW_OPINIONS_V aov2
where aov2.object_opinion_type_id = aov.object_opinion_type_id
and aov2.pk3_value = aov.pk3_value
and aov2.pk1_value = aov.pk1_value)
AND aov.audit_result_code <> 'EFFECTIVE');
SELECT count(distinct p_org.organization_id)
FROM AMW_PROCESS_ORGANIZATION apo,
AMW_ACCT_ASSOCIATIONS acct_assoc,
AMW_PROCESS_ORGANIZATION p_org,
(select process_id, organization_id, parent_child_id child_process_id, up_down_ind
from Amw_Org_Hierarchy_Denorm
union
select process_id, organization_id, process_id child_process_id, 'D' up_down_ind
from amw_process_organization) hier
WHERE acct_assoc.natural_account_id in
-- (select natural_account_id
-- from amw_fin_key_accounts_b
-- start with (natural_account_id, account_group_id) in
-- (select natural_account_id, account_group_id
-- from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
-- where cert.certification_id = p_certification_id and
-- cert.object_type = 'FIN_STMT' and
-- cert.statement_group_id = key_acc.statement_group_id and
-- cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID)
-- connect by parent_natural_account_id = PRIOR natural_account_id and
-- account_group_id = PRIOR account_group_id)
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
)
AND acct_assoc.object_type = 'PROCESS_ORG'
AND apo.process_organization_id = acct_assoc.pk1
AND hier.process_id = apo.process_id
AND hier.organization_id = apo.organization_id
AND hier.up_down_ind = 'D'
AND p_org.process_id = hier.child_process_id
AND p_org.organization_id = hier.organization_id
AND not exists (SELECT 'Y'
FROM AMW_OPINIONS_V aov
WHERE aov.object_name = 'AMW_ORG_PROCESS'
AND aov.opinion_type_code = 'CERTIFICATION'
AND aov.pk3_value = p_org.organization_id
AND aov.pk2_value = p_certification_id
AND aov.pk1_value = p_org.process_id);
SELECT count(distinct apo.organization_id)
FROM AMW_PROCESS_ORGANIZATION apo,
AMW_ACCT_ASSOCIATIONS acct_assoc
WHERE acct_assoc.natural_account_id in
-- (select natural_account_id
-- from amw_fin_key_accounts_b
-- start with (natural_account_id, account_group_id) in
-- (select natural_account_id, account_group_id
-- from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
-- where cert.certification_id = p_certification_id and
-- cert.object_type = 'FIN_STMT' and
-- cert.statement_group_id = key_acc.statement_group_id and
-- cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID)
-- connect by parent_natural_account_id = PRIOR natural_account_id and
-- account_group_id = PRIOR account_group_id)
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC key_acc
where cert.certification_id = p_certification_id
and cert.object_type = 'FIN_STMT'
and cert.statement_group_id = key_acc.statement_group_id
and cert.financial_statement_id = key_acc.FINANCIAL_STATEMENT_ID
)
)
AND acct_assoc.object_type = 'PROCESS_ORG'
AND apo.process_organization_id = acct_assoc.pk1;
UPDATE AMW_CERT_DASHBOARD_SUM
SET NEW_RISKS_ADDED = l_new_risks_added,
NEW_CONTROLS_ADDED = l_new_controls_added,
PROCESSES_NOT_CERT = l_global_proc_not_certified,
PROCESSES_CERT_ISSUES = l_global_proc_with_issue,
ORG_PROCESS_NOT_CERT = l_local_proc_not_certified,
ORG_PROCESS_CERT_ISSUES = l_local_proc_with_issue,
PROC_INEFF_CONTROL = l_global_proc_with_ineff_ctrl,
ORG_PROC_INEFF_CONTROL = l_local_proc_with_ineff_ctrl,
UNMITIGATED_RISKS = l_unmitigated_risks,
INEFFECTIVE_CONTROLS = l_ineffective_controls,
ORGS_IN_SCOPE = l_orgs_in_scope,
ORGS_PENDING_IN_SCOPE = l_orgs_pending_in_scope,
PERIOD_START_DATE = p_start_date,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE certification_id = p_certification_id;
INSERT INTO AMW_CERT_DASHBOARD_SUM (
CERTIFICATION_ID,
NEW_RISKS_ADDED,
NEW_CONTROLS_ADDED,
PROCESSES_NOT_CERT,
PROCESSES_CERT_ISSUES,
ORG_PROCESS_NOT_CERT,
ORG_PROCESS_CERT_ISSUES,
PROC_INEFF_CONTROL,
ORG_PROC_INEFF_CONTROL,
UNMITIGATED_RISKS,
INEFFECTIVE_CONTROLS,
ORGS_IN_SCOPE,
ORGS_PENDING_IN_SCOPE,
PERIOD_START_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
SELECT p_certification_id,
l_new_risks_added,
l_new_controls_added,
l_global_proc_not_certified,
l_global_proc_with_issue,
l_local_proc_not_certified,
l_local_proc_with_issue,
l_global_proc_with_ineff_ctrl,
l_local_proc_with_ineff_ctrl,
l_unmitigated_risks,
l_ineffective_controls,
l_orgs_in_scope,
l_orgs_pending_in_scope,
p_start_date,
FND_GLOBAL.USER_ID,
SYSDATE,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID
FROM DUAL;
SELECT cert.CERTIFICATION_ID, period.start_date
FROM AMW_CERTIFICATION_B cert, AMW_GL_PERIODS_V period
WHERE cert.object_type = 'FIN_STMT' and cert.certification_period_name = period.period_name
AND cert.certification_period_set_name = period.period_set_name
and cert.CERTIFICATION_STATUS in ('ACTIVE', 'DRAFT');
SELECT period.start_date
FROM AMW_CERTIFICATION_B cert, AMW_GL_PERIODS_V period
WHERE cert.object_type = 'FIN_STMT' and cert.certification_period_name = period.period_name
AND cert.certification_period_set_name = period.period_set_name
and cert.CERTIFICATION_STATUS in ('ACTIVE', 'DRAFT')
AND cert.certification_id = p_certification_id;
SELECT count(distinct aov.pk1_value)
FROM AMW_OPINIONS_V aov
WHERE aov.object_name = 'AMW_ORG_PROCESS'
AND aov.opinion_type_code = 'CERTIFICATION'
AND aov.pk3_value = p_organization_id
-- AND aov.authored_date >= p_start_date
-- AND aov.authored_date <= p_end_date
AND aov.pk2_value in (select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
where fin_stmt_cert_id = p_certification_id)
AND aov.pk1_value in (select distinct(orgrel.child_process_id)
from AMW_PROCESS_ORG_RELATIONS orgrel
START WITH orgrel.parent_process_id = p_process_id
and orgrel.organization_id = p_organization_id
CONNECT BY PRIOR orgrel.child_process_id = orgrel.parent_process_id
and PRIOR orgrel.organization_id = orgrel.organization_id);
SELECT count(distinct child_process_id)
FROM AMW_PROCESS_ORG_RELATIONS
START WITH parent_process_id = p_process_id
AND organization_id = p_organization_id
CONNECT BY PRIOR child_process_id = parent_process_id
AND PRIOR organization_id = organization_id;
SELECT count(distinct aov.pk3_value)
FROM AMW_OPINIONS_V aov
WHERE aov.object_name = 'AMW_ORG_PROCESS'
AND aov.opinion_type_code = 'CERTIFICATION'
AND aov.pk3_value <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999)
AND aov.pk1_value = p_process_id
AND aov.pk2_value in (select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
where fin_stmt_cert_id = p_certification_id);
SELECT count(distinct po.organization_id)
FROM AMW_ACCT_ASSOCIATIONS aa,
AMW_FIN_ITEMS_KEY_ACC fika,
AMW_PROCESS_ORGANIZATION po,
AMW_CERTIFICATION_B cert
WHERE aa.object_type = 'PROCESS_ORG'
AND aa.pk1 = po.process_organization_id
AND aa.natural_account_id in
-- (select acc.natural_account_id
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH acc.natural_account_id = fika.natural_account_id
-- and acc.account_group_id = fika.account_group_id
-- CONNECT BY PRIOR acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from AMW_FIN_KEY_ACCT_FLAT acc
where acc.parent_natural_account_id = fika.natural_account_id
and acc.account_group_id = fika.account_group_id
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where acc.natural_account_id = fika.natural_account_id
and acc.account_group_id = fika.account_group_id
)
AND fika.statement_group_id = cert.statement_group_id
AND fika.financial_statement_id = cert.financial_statement_id
AND cert.certification_id = p_certification_id
AND po.process_id = p_process_id
AND po.organization_id <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999);
SELECT aov.opinion_id
FROM AMW_OPINIONS_V aov
WHERE aov.object_name = 'AMW_ORG_PROCESS'
AND aov.opinion_type_code = 'CERTIFICATION'
AND aov.pk3_value = p_organization_id
AND aov.pk2_value in (select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
where fin_stmt_cert_id = p_certification_id)
AND aov.pk1_value = p_process_id
AND aov.authored_date = (select max(aov2.authored_date)
from AMW_OPINIONS_V aov2
where aov2.object_opinion_type_id = aov.object_opinion_type_id
and aov2.pk3_value = aov.pk3_value
and aov2.pk1_value = aov.pk1_value
and aov2.pk2_value in
(select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
where fin_stmt_cert_id = p_certification_id));
SELECT aov.opinion_id
FROM AMW_OPINIONS_V aov
WHERE aov.object_name = 'AMW_ORG_PROCESS'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = p_organization_id
AND aov.pk1_value = p_process_id
AND aov.authored_date = (select max(aov2.authored_date)
from AMW_OPINIONS_V aov2
where aov2.object_opinion_type_id = aov.object_opinion_type_id
and aov2.pk3_value = aov.pk3_value
and aov2.pk1_value = aov.pk1_value);
SELECT count(1)
FROM AMW_PROCESS_ORGANIZATION apo,
AMW_RISK_ASSOCIATIONS ara,
AMW_OPINIONS_V aov
WHERE apo.organization_id = p_organization_id
AND apo.process_id in ( select distinct(orgrel.child_process_id)
from AMW_PROCESS_ORG_RELATIONS orgrel
START WITH orgrel.child_process_id = p_process_id
and orgrel.organization_id = apo.organization_id
CONNECT BY PRIOR orgrel.child_process_id = orgrel.parent_process_id
and PRIOR orgrel.organization_id = orgrel.organization_id )
AND ara.object_type = 'PROCESS_ORG'
AND ara.pk1 = apo.process_organization_id
AND aov.object_name = 'AMW_ORG_PROCESS_RISK'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = apo.organization_id
AND aov.pk4_value = apo.process_id
AND aov.pk1_value = ara.risk_id
AND aov.authored_date = (select max(aov2.authored_date)
from AMW_OPINIONS_V aov2
where aov2.object_opinion_type_id = aov.object_opinion_type_id
and aov2.pk4_value = aov.pk4_value
and aov2.pk3_value = aov.pk3_value
and aov2.pk1_value = aov.pk1_value)
-- commented by qliu
-- and aov2.authored_date >= p_start_date
-- and aov2.authored_date <= p_end_date)
AND aov.audit_result_code <> 'EFFECTIVE';
SELECT count(distinct aca.control_id)
FROM AMW_PROCESS_ORGANIZATION apo,
AMW_RISK_ASSOCIATIONS ara,
AMW_CONTROL_ASSOCIATIONS aca,
AMW_OPINIONS_V aov
WHERE apo.organization_id = p_organization_id
AND apo.process_id in ( select distinct(orgrel.child_process_id)
from AMW_PROCESS_ORG_RELATIONS orgrel
START WITH orgrel.child_process_id = p_process_id
and orgrel.organization_id = apo.organization_id
CONNECT BY PRIOR orgrel.child_process_id = orgrel.parent_process_id
and PRIOR orgrel.organization_id = orgrel.organization_id )
AND ara.object_type = 'PROCESS_ORG'
AND ara.pk1 = apo.process_organization_id
AND aca.object_type = 'RISK_ORG'
AND aca.pk1 = ara.risk_association_id
AND aov.object_name = 'AMW_ORG_CONTROL'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = apo.organization_id
AND aov.pk1_value = aca.control_id
AND aov.authored_date = (select max(aov2.authored_date)
from AMW_OPINIONS_V aov2
where aov2.object_opinion_type_id = aov.object_opinion_type_id
and aov2.pk3_value = aov.pk3_value
and aov2.pk1_value = aov.pk1_value)
-- commented by qliu
-- and aov2.authored_date >= p_start_date
-- and aov2.authored_date <= p_end_date)
AND aov.audit_result_code <> 'EFFECTIVE';
UPDATE AMW_FIN_PROCESS_EVAL_SUM
SET CERT_OPINION_ID = l_cert_opinion_id,
EVAL_OPINION_ID = l_eval_opinion_id,
UNMITIGATED_RISKS = l_unmitigated_risks,
INEFFECTIVE_CONTROLS = l_ineffective_controls,
NUMBER_OF_SUB_PROCS_CERTIFIED = l_sub_processes_certified,
TOTAL_NUMBER_OF_SUB_PROCS = l_total_sub_processes,
SUB_PROCS_CERTIFIED_PRCNT = round((l_sub_processes_certified/decode(nvl(l_total_sub_processes,0),0,1,l_total_sub_processes) *100),0),
NUMBER_OF_ORG_PROCS_CERTIFIED = l_org_processes_certified,
TOTAL_NUMBER_OF_ORG_PROCS = l_total_org_processes,
ORG_PROCS_CERTIFIED_PRCNT = round((l_org_processes_certified/decode(nvl(l_total_org_processes,0),0,1,l_total_org_processes) *100),0),
OPEN_FINDINGS = l_open_findings,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE fin_certification_id = p_certification_id
AND organization_id = p_organization_id
AND process_id = p_process_id;
INSERT INTO AMW_FIN_PROCESS_EVAL_SUM(
FIN_CERTIFICATION_ID,
PROCESS_ID,
ORGANIZATION_ID,
CERT_OPINION_ID,
EVAL_OPINION_ID,
UNMITIGATED_RISKS,
INEFFECTIVE_CONTROLS,
UNMITIGATED_RISKS_PRCNT,
INEFFECTIVE_CONTROLS_PRCNT,
TOTAL_NUMBER_OF_SUB_PROCS,
NUMBER_OF_SUB_PROCS_CERTIFIED,
SUB_PROCS_CERTIFIED_PRCNT,
TOTAL_NUMBER_OF_ORG_PROCS,
NUMBER_OF_ORG_PROCS_CERTIFIED,
ORG_PROCS_CERTIFIED_PRCNT,
OPEN_FINDINGS,
ACCOUNT_PROCESS_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
SELECT p_certification_id,
p_process_id,
p_organization_id,
l_cert_opinion_id,
l_eval_opinion_id,
l_unmitigated_risks,
l_ineffective_controls,
null,
null,
l_total_sub_processes,
l_sub_processes_certified,
round((l_sub_processes_certified/decode(nvl(l_total_sub_processes,0),0,1,l_total_sub_processes) *100),0),
l_total_org_processes,
l_org_processes_certified,
round((l_org_processes_certified/decode(nvl(l_total_org_processes,0),0,1,l_total_org_processes) *100),0),
l_open_findings,
p_account_process_flag,
FND_GLOBAL.USER_ID,
SYSDATE,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID
FROM DUAL;
SELECT cert.CERTIFICATION_ID, period.start_date
FROM AMW_CERTIFICATION_B cert, AMW_GL_PERIODS_V period
WHERE cert.object_type = 'FIN_STMT' and cert.certification_period_name = period.period_name
AND cert.certification_period_set_name = period.period_set_name
and cert.CERTIFICATION_STATUS in ('ACTIVE', 'DRAFT');
amw_fin_coso_views_pvt.DELETE_ROWS(x_fin_certification_id => l_certification_id);
amw_fin_coso_views_pvt.DELETE_ROWS(x_fin_certification_id => cert_rec.certification_id);
SELECT cert.certification_id, period.start_date,period.end_date
FROM AMW_CERTIFICATION_B cert,
AMW_GL_PERIODS_V period
WHERE cert.certification_period_name = period.period_name
AND cert.certification_period_set_name = period.period_set_name
AND cert.object_type='FIN_STMT'
AND cert.certification_status in ('ACTIVE','DRAFT');
SELECT period.start_date,period.end_date
FROM AMW_CERTIFICATION_B cert,
AMW_GL_PERIODS_V period
WHERE cert.certification_period_name = period.period_name
AND cert.certification_period_set_name = period.period_set_name
AND cert.certification_id = p_certification_id;
SELECT distinct aa.pk1 process_organization_id,po.process_id,po.organization_id
FROM AMW_ACCT_ASSOCIATIONS aa,
AMW_FIN_ITEMS_KEY_ACC fika,
AMW_PROCESS_ORGANIZATION po,
AMW_CERTIFICATION_B cert
WHERE aa.object_type = 'PROCESS_ORG'
AND aa.pk1 = po.process_organization_id
AND aa.natural_account_id in
-- (select acc.natural_account_id
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH acc.natural_account_id = fika.natural_account_id
-- and acc.account_group_id = fika.account_group_id
-- CONNECT BY PRIOR acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from AMW_FIN_KEY_ACCT_FLAT acc
where acc.parent_natural_account_id = fika.natural_account_id
and acc.account_group_id = fika.account_group_id
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where acc.natural_account_id = fika.natural_account_id
and acc.account_group_id = fika.account_group_id
)
AND fika.statement_group_id = cert.statement_group_id
AND fika.financial_statement_id = cert.financial_statement_id
AND cert.certification_id = p_cert_id;
SELECT distinct child_process_id
FROM AMW_PROCESS_ORG_RELATIONS
START WITH parent_process_id = p_proc_id
AND organization_id = p_org_id
CONNECT BY PRIOR child_process_id = parent_process_id
AND PRIOR organization_id = organization_id;
SELECT count(distinct aov.pk3_value)
FROM AMW_OPINIONS_V aov
WHERE aov.object_name = 'AMW_ORG_PROCESS'
AND aov.opinion_type_code = 'CERTIFICATION'
AND aov.pk3_value <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999)
AND aov.pk1_value = p_process_id
AND aov.pk3_value in (select distinct evalsum.organization_id
from amw_fin_process_eval_sum evalsum
where evalsum.fin_certification_id = p_cert_id
and evalsum.process_id=p_process_id)
AND aov.pk2_value in (select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
where fin_stmt_cert_id = p_cert_id);
select count(distinct evalsum.organization_id)
from amw_fin_process_eval_sum evalsum
where evalsum.fin_certification_id = p_cert_id
and evalsum.process_id=p_process_id
and evalsum.organization_id <>
NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999);
SELECT fin_certification_id,organization_id,process_id
FROM AMW_FIN_PROCESS_EVAL_SUM
WHERE fin_certification_id = p_cert_id
AND organization_id = NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999);
select count(1) from
(SELECT distinct aov.pk3_value,aov.pk1_value
FROM AMW_OPINIONS_V aov
WHERE aov.object_name = 'AMW_ORG_PROCESS'
AND aov.opinion_type_code = 'CERTIFICATION'
--AND aov.pk3_value <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999)
--AND aov.authored_date >= p_start_date
--AND aov.authored_date <= p_end_date
AND aov.pk2_value in (
select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
where fin_stmt_cert_id = p_cert_id)
AND aov.pk1_value in (SELECT varproc.process_id
FROM AMW_PROCESS varproc
WHERE varproc.standard_variation = p_process_id
AND varproc.standard_process_flag = 'N'
AND varproc.process_id in (select distinct evalsum.process_id
from amw_fin_process_eval_sum evalsum
where evalsum.fin_certification_id = p_cert_id)));
select count(1) from
(select distinct evalsum.organization_id,evalsum.process_id
from amw_fin_process_eval_sum evalsum
where evalsum.fin_certification_id = p_cert_id
and evalsum.process_id in (select varproc.process_id
FROM AMW_PROCESS varproc
WHERE varproc.standard_variation = p_process_id
AND varproc.standard_process_flag = 'N'));
DELETE from AMW_FIN_PROCESS_EVAL_SUM
WHERE fin_certification_id = p_certification_id;
UPDATE AMW_FIN_PROCESS_EVAL_SUM
SET TOTAL_NUMBER_OF_ORG_PROCS = l_total_org_processes,
NUMBER_OF_ORG_PROCS_CERTIFIED = l_org_processes_certified,
ORG_PROCS_CERTIFIED_PRCNT =
round(((l_org_processes_certified/decode(l_total_org_processes,0,1,l_total_org_processes)) *100),0)
WHERE FIN_CERTIFICATION_ID = var_rec.fin_certification_id
AND ORGANIZATION_ID = var_rec.organization_id
AND PROCESS_ID = var_rec.process_id;
DELETE from AMW_FIN_PROCESS_EVAL_SUM
WHERE fin_certification_id = cert_rec.certification_id;
UPDATE AMW_FIN_PROCESS_EVAL_SUM
SET TOTAL_NUMBER_OF_ORG_PROCS = l_total_org_processes,
NUMBER_OF_ORG_PROCS_CERTIFIED = l_org_processes_certified,
ORG_PROCS_CERTIFIED_PRCNT =
round(((l_org_processes_certified/decode(l_total_org_processes,0,1,l_total_org_processes)) *100),0)
WHERE FIN_CERTIFICATION_ID = var_rec.fin_certification_id
AND ORGANIZATION_ID = var_rec.organization_id
AND PROCESS_ID = var_rec.process_id;
SELECT aov.opinion_id
FROM AMW_OPINIONS_V aov
WHERE aov.object_name = 'AMW_ORGANIZATION'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk1_value = p_organization_id
AND aov.authored_date = (select max(aov2.authored_date)
from AMW_OPINIONS_V aov2
where aov2.object_opinion_type_id = aov.object_opinion_type_id
and aov2.pk1_value = aov.pk1_value);
SELECT count(distinct apo.process_id)
FROM AMW_ACCT_ASSOCIATIONS aa,
AMW_FIN_ITEMS_KEY_ACC fika,
AMW_CERTIFICATION_B cert,
AMW_PROCESS_ORGANIZATION apo,
AMW_PROCESS_ORGANIZATION apo2
WHERE aa.object_type = 'PROCESS_ORG'
AND apo2.process_organization_id = aa.pk1
AND (apo.process_id, apo.organization_id) in
(select child_process_id, organization_id
from Amw_Process_Org_Relations
start with child_process_id = apo2.process_id
and organization_id = apo2.organization_Id
CONNECT BY PRIOR child_process_id = parent_process_id
and PRIOR organization_id = organization_id)
AND aa.natural_account_id in
-- (select acc.natural_account_id
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH acc.natural_account_id = fika.natural_account_id
-- and acc.account_group_id = fika.account_group_id
-- CONNECT BY PRIOR acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from AMW_FIN_KEY_ACCT_FLAT acc
where acc.parent_natural_account_id = fika.natural_account_id
and acc.account_group_id = fika.account_group_id
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where acc.natural_account_id = fika.natural_account_id
and acc.account_group_id = fika.account_group_id
)
AND fika.statement_group_id = cert.statement_group_id
AND fika.financial_statement_id = cert.financial_statement_id
AND cert.certification_id = p_certification_id
AND apo.organization_id = p_organization_id
AND not exists (SELECT 'Y'
FROM AMW_OPINIONS_V aov
WHERE aov.object_name = 'AMW_ORG_PROCESS'
AND aov.opinion_type_code = 'CERTIFICATION'
AND aov.pk3_value = p_organization_id
AND aov.pk1_value = apo.process_id
AND aov.authored_date >= p_start_date
AND aov.authored_date <= p_end_date);
SELECT count(distinct hier.parent_child_id)
FROM AMW_ACCT_ASSOCIATIONS aa,
AMW_PROCESS_ORGANIZATION po,
(select process_id, organization_id, parent_child_id, up_down_ind
from Amw_Org_Hierarchy_Denorm hier
union select process_id, organization_id, process_id, 'D'
from amw_process_organization) hier
WHERE aa.object_type = 'PROCESS_ORG'
AND po.process_organization_id = aa.pk1
AND hier.organization_id = po.organization_id
AND hier.process_id = po.process_id
AND hier.up_down_ind = 'D'
AND aa.natural_account_id in
-- (select acc.natural_account_id
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ((acc.natural_account_id, acc.account_group_id) in
-- (select fika.natural_account_id, fika.account_group_id
-- from AMW_FIN_ITEMS_KEY_ACC fika,
-- AMW_CERTIFICATION_B cert
-- where fika.statement_group_id = cert.statement_group_id
-- AND fika.financial_statement_id = cert.financial_statement_id
-- AND cert.certification_id = p_certification_id))
-- CONNECT BY PRIOR acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC fika
where cert.certification_id = p_certification_id
and cert.statement_group_id = fika.statement_group_id
and cert.financial_statement_id = fika.FINANCIAL_STATEMENT_ID
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC fika
where cert.certification_id = p_certification_id
and cert.statement_group_id = fika.statement_group_id
and cert.financial_statement_id = fika.FINANCIAL_STATEMENT_ID
)
)
AND po.organization_id = p_organization_id;
SELECT count(distinct hier.parent_child_id)
FROM AMW_ACCT_ASSOCIATIONS aa,
AMW_PROCESS_ORGANIZATION po,
(select process_id, organization_id, parent_child_id, up_down_ind
from Amw_Org_Hierarchy_Denorm hier
union select process_id, organization_id, process_id, 'D'
from amw_process_organization) hier,
AMW_OPINIONS_V aov
WHERE aa.object_type = 'PROCESS_ORG'
AND po.process_organization_id = aa.pk1
AND hier.organization_id = po.organization_id
AND hier.process_id = po.process_id
AND hier.up_down_ind = 'D'
AND aa.natural_account_id in
-- (select acc.natural_account_id
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ((acc.natural_account_id, acc.account_group_id) in
-- (select fika.natural_account_id, fika.account_group_id
-- from AMW_FIN_ITEMS_KEY_ACC fika,
-- AMW_CERTIFICATION_B cert
-- where fika.statement_group_id = cert.statement_group_id
-- AND fika.financial_statement_id = cert.financial_statement_id
-- AND cert.certification_id = p_certification_id))
-- CONNECT BY PRIOR acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC fika
where cert.certification_id = p_certification_id
and cert.statement_group_id = fika.statement_group_id
and cert.financial_statement_id = fika.FINANCIAL_STATEMENT_ID
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC fika
where cert.certification_id = p_certification_id
and cert.statement_group_id = fika.statement_group_id
and cert.financial_statement_id = fika.FINANCIAL_STATEMENT_ID
)
)
AND po.organization_id = p_organization_id
AND aov.object_name = 'AMW_ORG_PROCESS'
AND aov.opinion_type_code = 'CERTIFICATION'
AND aov.pk3_value = p_organization_id
AND aov.pk2_value in (select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
where fin_stmt_cert_id = p_certification_id)
AND aov.pk1_value = hier.parent_child_id
AND aov.authored_date = (select max(aov2.authored_date)
from AMW_OPINIONS_V aov2
where aov2.object_opinion_type_id
= aov.object_opinion_type_id
and aov2.pk3_value = aov.pk3_value
AND aov2.pk2_value in
(select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
w where fin_stmt_cert_id = p_certification_id)
and aov2.pk1_value = aov.pk1_value)
AND aov.audit_result_code <> 'EFFECTIVE';
SELECT count(distinct hier.parent_child_id)
FROM AMW_ACCT_ASSOCIATIONS aa,
AMW_PROCESS_ORGANIZATION po,
(select process_id, organization_id, parent_child_id, up_down_ind
from Amw_Org_Hierarchy_Denorm hier
union select process_id, organization_id, process_id, 'D'
from amw_process_organization) hier,
AMW_OPINIONS_V aov
WHERE aa.object_type = 'PROCESS_ORG'
AND po.process_organization_id = aa.pk1
AND hier.organization_id = po.organization_id
AND hier.process_id = po.process_id
AND hier.up_down_ind = 'D'
AND aa.natural_account_id in
-- (select acc.natural_account_id
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ((acc.natural_account_id, acc.account_group_id) in
-- (select fika.natural_account_id, fika.account_group_id
-- from AMW_FIN_ITEMS_KEY_ACC fika,
-- AMW_CERTIFICATION_B cert
-- where fika.statement_group_id = cert.statement_group_id
-- AND fika.financial_statement_id = cert.financial_statement_id
-- AND cert.certification_id = p_certification_id))
-- CONNECT BY PRIOR acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC fika
where cert.certification_id = p_certification_id
and cert.statement_group_id = fika.statement_group_id
and cert.financial_statement_id = fika.FINANCIAL_STATEMENT_ID
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC fika
where cert.certification_id = p_certification_id
and cert.statement_group_id = fika.statement_group_id
and cert.financial_statement_id = fika.FINANCIAL_STATEMENT_ID
)
)
AND po.organization_id = p_organization_id
AND aov.object_name = 'AMW_ORG_PROCESS'
AND aov.opinion_type_code = 'CERTIFICATION'
AND aov.pk3_value = p_organization_id
AND aov.pk2_value in (select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
where fin_stmt_cert_id = p_certification_id)
AND aov.pk1_value = hier.parent_child_id
AND aov.authored_date = (select max(aov2.authored_date)
from AMW_OPINIONS_V aov2
where aov2.object_opinion_type_id
= aov.object_opinion_type_id
and aov2.pk3_value = aov.pk3_value
AND aov2.pk2_value in
(select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
w where fin_stmt_cert_id = p_certification_id)
and aov2.pk1_value = aov.pk1_value)
AND aov.audit_result_code = 'EFFECTIVE';
SELECT count(distinct hier.parent_child_id)
FROM AMW_ACCT_ASSOCIATIONS aa,
AMW_PROCESS_ORGANIZATION po,
(select process_id, organization_id, parent_child_id, up_down_ind
from Amw_Org_Hierarchy_Denorm hier
union select process_id, organization_id, process_id, 'D'
from amw_process_organization) hier,
AMW_OPINIONS_V aov
WHERE aa.object_type = 'PROCESS_ORG'
AND po.process_organization_id = aa.pk1
AND hier.organization_id = po.organization_id
AND hier.process_id = po.process_id
AND hier.up_down_ind = 'D'
AND aa.natural_account_id in
-- (select acc.natural_account_id
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ((acc.natural_account_id, acc.account_group_id) in
-- (select fika.natural_account_id, fika.account_group_id
-- from AMW_FIN_ITEMS_KEY_ACC fika,
-- AMW_CERTIFICATION_B cert
-- where fika.statement_group_id = cert.statement_group_id
-- AND fika.financial_statement_id = cert.financial_statement_id
-- AND cert.certification_id = p_certification_id))
-- CONNECT BY PRIOR acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC fika
where cert.certification_id = p_certification_id
and cert.statement_group_id = fika.statement_group_id
and cert.financial_statement_id = fika.FINANCIAL_STATEMENT_ID
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC fika
where cert.certification_id = p_certification_id
and cert.statement_group_id = fika.statement_group_id
and cert.financial_statement_id = fika.FINANCIAL_STATEMENT_ID
)
)
AND po.organization_id = p_organization_id
AND aov.object_name = 'AMW_ORG_PROCESS'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = hier.organization_id
AND aov.pk1_value = hier.parent_child_id
AND aov.authored_date =
(select max(aov2.authored_date)
from AMW_OPINIONS_V aov2
where aov2.object_opinion_type_id = aov.object_opinion_type_id
and aov2.pk3_value = aov.pk3_value
and aov2.pk1_value = aov.pk1_value)
AND aov.audit_result_code <> 'EFFECTIVE';
SELECT count(distinct ara.risk_association_id)
FROM AMW_ACCT_ASSOCIATIONS aa,
AMW_PROCESS_ORGANIZATION po,
(select process_id, organization_id, parent_child_id, up_down_ind
from Amw_Org_Hierarchy_Denorm hier
union select process_id, organization_id, process_id, 'D'
from amw_process_organization) hier,
AMW_PROCESS_ORGANIZATION po2,
AMW_RISK_ASSOCIATIONS ara,
AMW_OPINIONS_V aov
WHERE aa.object_type = 'PROCESS_ORG'
AND po.process_organization_id = aa.pk1
AND hier.organization_id = po.organization_id
AND hier.process_id = po.process_id
AND hier.up_down_ind = 'D'
AND aa.natural_account_id in
-- (select acc.natural_account_id
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ((acc.natural_account_id, acc.account_group_id) in
-- (select fika.natural_account_id, fika.account_group_id
-- from AMW_FIN_ITEMS_KEY_ACC fika,
-- AMW_CERTIFICATION_B cert
-- where fika.statement_group_id = cert.statement_group_id
-- AND fika.financial_statement_id = cert.financial_statement_id
-- AND cert.certification_id = p_certification_id))
-- CONNECT BY PRIOR acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC fika
where cert.certification_id = p_certification_id
and cert.statement_group_id = fika.statement_group_id
and cert.financial_statement_id = fika.FINANCIAL_STATEMENT_ID
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC fika
where cert.certification_id = p_certification_id
and cert.statement_group_id = fika.statement_group_id
and cert.financial_statement_id = fika.FINANCIAL_STATEMENT_ID
)
)
AND po.organization_id = p_organization_id
AND po2.process_id = hier.parent_child_id
AND po2.organization_id = hier.organization_id
AND ara.object_type = 'PROCESS_ORG'
AND ara.pk1 = po2.process_organization_id
AND aov.object_name = 'AMW_ORG_PROCESS_RISK'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = po2.organization_id
AND aov.pk4_value = po2.process_id
AND aov.pk1_value = ara.risk_id
AND aov.authored_date = (select max(aov2.authored_date)
from AMW_OPINIONS_V aov2
where aov2.object_opinion_type_id = aov.object_opinion_type_id
and aov2.pk4_value = aov.pk4_value
and aov2.pk3_value = aov.pk3_value
and aov2.pk1_value = aov.pk1_value)
AND aov.audit_result_code <> 'EFFECTIVE';
SELECT count(distinct ara.risk_association_id)
FROM AMW_ACCT_ASSOCIATIONS aa,
AMW_PROCESS_ORGANIZATION po,
(select process_id, organization_id, parent_child_id, up_down_ind
from Amw_Org_Hierarchy_Denorm hier
union select process_id, organization_id, process_id, 'D'
from amw_process_organization) hier,
AMW_PROCESS_ORGANIZATION po2,
AMW_RISK_ASSOCIATIONS ara
WHERE aa.object_type = 'PROCESS_ORG'
AND po.process_organization_id = aa.pk1
AND hier.organization_id = po.organization_id
AND hier.process_id = po.process_id
AND hier.up_down_ind = 'D'
AND aa.natural_account_id in
-- (select acc.natural_account_id
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ((acc.natural_account_id, acc.account_group_id) in
-- (select fika.natural_account_id, fika.account_group_id
-- from AMW_FIN_ITEMS_KEY_ACC fika,
-- AMW_CERTIFICATION_B cert
-- where fika.statement_group_id = cert.statement_group_id
-- AND fika.financial_statement_id = cert.financial_statement_id
-- AND cert.certification_id = p_certification_id))
-- CONNECT BY PRIOR acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC fika
where cert.certification_id = p_certification_id
and cert.statement_group_id = fika.statement_group_id
and cert.financial_statement_id = fika.FINANCIAL_STATEMENT_ID
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC fika
where cert.certification_id = p_certification_id
and cert.statement_group_id = fika.statement_group_id
and cert.financial_statement_id = fika.FINANCIAL_STATEMENT_ID
)
)
AND po.organization_id = p_organization_id
AND po2.process_id = hier.parent_child_id
AND po2.organization_id = hier.organization_id
AND ara.object_type = 'PROCESS_ORG'
AND ara.pk1 = po2.process_organization_id;
SELECT count(distinct aca.control_id)
FROM AMW_ACCT_ASSOCIATIONS aa,
AMW_PROCESS_ORGANIZATION po,
(select process_id, organization_id, parent_child_id, up_down_ind
from Amw_Org_Hierarchy_Denorm hier
union select process_id, organization_id, process_id, 'D'
from amw_process_organization) hier,
AMW_PROCESS_ORGANIZATION po2,
AMW_RISK_ASSOCIATIONS ara,
AMW_CONTROL_ASSOCIATIONS aca ,
AMW_OPINIONS_V aov
WHERE aa.object_type = 'PROCESS_ORG'
AND po.process_organization_id = aa.pk1
AND hier.organization_id = po.organization_id
AND hier.process_id = po.process_id
AND hier.up_down_ind = 'D'
AND aa.natural_account_id in
-- (select acc.natural_account_id
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ((acc.natural_account_id, acc.account_group_id) in
-- (select fika.natural_account_id, fika.account_group_id
-- from AMW_FIN_ITEMS_KEY_ACC fika,
-- AMW_CERTIFICATION_B cert
-- where fika.statement_group_id = cert.statement_group_id
-- AND fika.financial_statement_id = cert.financial_statement_id
-- AND cert.certification_id = p_certification_id))
-- CONNECT BY PRIOR acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC fika
where cert.certification_id = p_certification_id
and cert.statement_group_id = fika.statement_group_id
and cert.financial_statement_id = fika.FINANCIAL_STATEMENT_ID
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC fika
where cert.certification_id = p_certification_id
and cert.statement_group_id = fika.statement_group_id
and cert.financial_statement_id = fika.FINANCIAL_STATEMENT_ID
)
)
AND po.organization_id = p_organization_id
AND po2.process_id = hier.parent_child_id
AND po2.organization_id = hier.organization_id
AND ara.object_type = 'PROCESS_ORG'
AND ara.pk1 = po2.process_organization_id
AND aca.object_type = 'RISK_ORG'
AND aca.pk1 = ara.risk_association_id
AND aov.object_name = 'AMW_ORG_CONTROL'
AND aov.opinion_type_code = 'EVALUATION'
AND aov.pk3_value = po2.organization_id
AND aov.pk1_value = aca.control_id
AND aov.authored_date = (select max(aov2.authored_date)
from AMW_OPINIONS_V aov2
where aov2.object_opinion_type_id = aov.object_opinion_type_id
and aov2.pk3_value = aov.pk3_value
and aov2.pk1_value = aov.pk1_value)
AND aov.audit_result_code <> 'EFFECTIVE';
SELECT count(distinct aca.control_id)
FROM AMW_ACCT_ASSOCIATIONS aa,
AMW_PROCESS_ORGANIZATION po,
(select process_id, organization_id, parent_child_id, up_down_ind
from Amw_Org_Hierarchy_Denorm hier
union select process_id, organization_id, process_id, 'D'
from amw_process_organization) hier,
AMW_PROCESS_ORGANIZATION po2,
AMW_RISK_ASSOCIATIONS ara,
AMW_CONTROL_ASSOCIATIONS aca
WHERE aa.object_type = 'PROCESS_ORG'
AND po.process_organization_id = aa.pk1
AND hier.organization_id = po.organization_id
AND hier.process_id = po.process_id
AND hier.up_down_ind = 'D'
AND aa.natural_account_id in
-- (select acc.natural_account_id
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ((acc.natural_account_id, acc.account_group_id) in
-- (select fika.natural_account_id, fika.account_group_id
-- from AMW_FIN_ITEMS_KEY_ACC fika,
-- AMW_CERTIFICATION_B cert
-- where fika.statement_group_id = cert.statement_group_id
-- AND fika.financial_statement_id = cert.financial_statement_id
-- AND cert.certification_id = p_certification_id))
-- CONNECT BY PRIOR acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC fika
where cert.certification_id = p_certification_id
and cert.statement_group_id = fika.statement_group_id
and cert.financial_statement_id = fika.FINANCIAL_STATEMENT_ID
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select natural_account_id, account_group_id
from amw_certification_b cert, AMW_FIN_ITEMS_KEY_ACC fika
where cert.certification_id = p_certification_id
and cert.statement_group_id = fika.statement_group_id
and cert.financial_statement_id = fika.FINANCIAL_STATEMENT_ID
)
)
AND po.organization_id = p_organization_id
AND po2.process_id = hier.parent_child_id
AND po2.organization_id = hier.organization_id
AND ara.object_type = 'PROCESS_ORG'
AND ara.pk1 = po2.process_organization_id
AND aca.object_type = 'RISK_ORG'
AND aca.pk1 = ara.risk_association_id;
SELECT subsidiary_valueset, company_code, lob_valueset, lob_code
FROM amw_audit_units_v
WHERE organization_id = p_organization_id;
UPDATE AMW_FIN_ORG_EVAL_SUM
SET EVAL_OPINION_ID = l_eval_opinion_id,
PROC_PENDING_CERTIFICATION = l_proc_pending_cert,
TOTAL_NUMBER_OF_PROCS = l_total_num_of_procs,
PROC_CERTIFIED_WITH_ISSUES = l_proc_with_issue,
PROC_VERIFIED = l_proc_certified,
PROC_WITH_INEFFECTIVE_CONTROLS = l_proc_with_ineff_ctrl,
UNMITIGATED_RISKS = l_unmitigated_risks,
RISKS_VERIFIED = l_risks,
INEFFECTIVE_CONTROLS = l_ineff_controls,
CONTROLS_VERIFIED = l_controls,
PROC_PENDING_CERT_PRCNT =
round(l_proc_pending_cert/decode(l_total_num_of_procs, 0,1,l_total_num_of_procs),2)*100,
PROCESSES_WITH_ISSUES_PRCNT =
round(l_proc_with_issue/decode(l_total_num_of_procs, 0,1,l_total_num_of_procs),2)*100,
PROC_WITH_INEFF_CONTROLS_PRCNT =
round(l_proc_with_ineff_ctrl/decode(l_total_num_of_procs, 0,1,l_total_num_of_procs),2)*100,
UNMITIGATED_RISKS_PRCNT = round(l_unmitigated_risks/decode(l_risks,0,1,l_risks), 2)*100,
INEFFECTIVE_CONTROLS_PRCNT = round(l_ineff_controls/decode(l_controls,0,1,l_controls), 2)*100,
OPEN_FINDINGS = l_open_findings,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
SUBSIDIARY_VS = l_sub_vs,
SUBSIDIARY_CODE = l_sub_code,
LOB_VS = l_lob_vs,
LOB_CODE = l_lob_code
WHERE fin_certification_id = p_certification_id
AND organization_id = p_organization_id;
INSERT INTO AMW_FIN_ORG_EVAL_SUM(
FIN_CERTIFICATION_ID,
ORGANIZATION_ID,
EVAL_OPINION_ID,
PROC_PENDING_CERTIFICATION,
TOTAL_NUMBER_OF_PROCS,
PROC_CERTIFIED_WITH_ISSUES,
PROC_VERIFIED,
PROC_WITH_INEFFECTIVE_CONTROLS,
UNMITIGATED_RISKS,
RISKS_VERIFIED,
INEFFECTIVE_CONTROLS,
CONTROLS_VERIFIED,
PROC_PENDING_CERT_PRCNT,
PROCESSES_WITH_ISSUES_PRCNT,
PROC_WITH_INEFF_CONTROLS_PRCNT,
UNMITIGATED_RISKS_PRCNT,
INEFFECTIVE_CONTROLS_PRCNT,
OPEN_FINDINGS,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
SUBSIDIARY_VS,
SUBSIDIARY_CODE,
LOB_VS,
LOB_CODE)
SELECT p_certification_id,
p_organization_id,
l_eval_opinion_id,
l_proc_pending_cert,
l_total_num_of_procs,
l_proc_with_issue,
l_proc_certified,
l_proc_with_ineff_ctrl,
l_unmitigated_risks,
l_risks,
l_ineff_controls,
l_controls,
round(l_proc_pending_cert/decode(l_total_num_of_procs, 0,1,l_total_num_of_procs),2)*100,
round(l_proc_with_issue/decode(l_total_num_of_procs, 0,1,l_total_num_of_procs),2)*100,
round(l_proc_with_ineff_ctrl/decode(l_total_num_of_procs, 0,1,l_total_num_of_procs),2)*100,
round(l_unmitigated_risks/decode(l_risks,0,1,l_risks), 2)*100,
round(l_ineff_controls/decode(l_controls,0,1,l_controls), 2)*100,
l_open_findings,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
1,
l_sub_vs,
l_sub_code,
l_lob_vs,
l_lob_code
FROM DUAL;
SELECT cert.certification_id, period.start_date, period.end_date
FROM AMW_CERTIFICATION_B cert,
AMW_GL_PERIODS_V period
WHERE cert.certification_period_name = period.period_name
AND cert.certification_period_set_name = period.period_set_name
AND cert.object_type='FIN_STMT'
AND cert.certification_status in ('ACTIVE','DRAFT');
SELECT period.start_date, period.end_date
FROM AMW_CERTIFICATION_B cert,
AMW_GL_PERIODS_V period
WHERE cert.certification_period_name = period.period_name
AND cert.certification_period_set_name = period.period_set_name
AND cert.certification_id = p_certification_id;
SELECT distinct po.organization_id
FROM AMW_ACCT_ASSOCIATIONS aa,
AMW_FIN_ITEMS_KEY_ACC fika,
AMW_PROCESS_ORGANIZATION po,
AMW_CERTIFICATION_B cert
WHERE aa.object_type = 'PROCESS_ORG'
AND aa.pk1 = po.process_organization_id
AND aa.natural_account_id in
-- (select acc.natural_account_id
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH acc.natural_account_id = fika.natural_account_id
-- and acc.account_group_id = fika.account_group_id
-- CONNECT BY PRIOR acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from AMW_FIN_KEY_ACCT_FLAT acc
where acc.parent_natural_account_id = fika.natural_account_id
and acc.account_group_id = fika.account_group_id
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where acc.natural_account_id = fika.natural_account_id
and acc.account_group_id = fika.account_group_id
)
AND fika.statement_group_id = cert.statement_group_id
AND fika.financial_statement_id = cert.financial_statement_id
AND cert.certification_id = p_cert_id;
DELETE from AMW_FIN_ORG_EVAL_SUM orgevalsum
where FIN_CERTIFICATION_ID = p_certification_id
and not exists
(SELECT 'Y'
FROM AMW_ACCT_ASSOCIATIONS aa,
AMW_FIN_ITEMS_KEY_ACC fika,
AMW_PROCESS_ORGANIZATION po,
AMW_CERTIFICATION_B cert
WHERE aa.object_type = 'PROCESS_ORG'
AND aa.pk1 = po.process_organization_id
AND aa.natural_account_id in
-- (select acc.natural_account_id
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH acc.natural_account_id = fika.natural_account_id
-- and acc.account_group_id = fika.account_group_id
-- CONNECT BY PRIOR acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from AMW_FIN_KEY_ACCT_FLAT acc
where acc.parent_natural_account_id = fika.natural_account_id
and acc.account_group_id = fika.account_group_id
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where acc.natural_account_id = fika.natural_account_id
and acc.account_group_id = fika.account_group_id
)
AND fika.statement_group_id = cert.statement_group_id
AND fika.financial_statement_id = cert.financial_statement_id
AND cert.certification_id = p_certification_id
AND po.organization_id = orgevalsum.organization_id);
DELETE from AMW_FIN_ORG_EVAL_SUM orgevalsum
where not exists
(SELECT 'Y'
FROM AMW_ACCT_ASSOCIATIONS aa,
AMW_FIN_ITEMS_KEY_ACC fika,
AMW_PROCESS_ORGANIZATION po,
AMW_CERTIFICATION_B cert
WHERE aa.object_type = 'PROCESS_ORG'
AND aa.pk1 = po.process_organization_id
AND aa.natural_account_id = fika.natural_account_id
AND fika.statement_group_id = cert.statement_group_id
AND fika.financial_statement_id = cert.financial_statement_id
AND cert.certification_status in ('ACTIVE','DRAFT')
AND cert.certification_id = orgevalsum.fin_certification_id
AND po.organization_id = orgevalsum.organization_id);
select
certifcationVL.CERTIFICATION_ID ,
certifcationVL.FINANCIAL_STATEMENT_ID,
certifcationVL.STATEMENT_GROUP_ID
FROM
AMW_CERTIFICATION_vl certifcationVL
where
certifcationVL.OBJECT_TYPE='FIN_STMT'
and certifcationVL.CERTIFICATION_STATUS in ('ACTIVE', 'DRAFT');
select
certifcationVL.CERTIFICATION_ID ,
certifcationVL.FINANCIAL_STATEMENT_ID,
certifcationVL.STATEMENT_GROUP_ID
FROM
AMW_CERTIFICATION_vl certifcationVL
where
certifcationVL.OBJECT_TYPE='FIN_STMT'
and certifcationVL.CERTIFICATION_ID = P_CERTIFICATION_ID;
update amw_fin_cert_eval_sum
set
PROC_PENDING_CERTIFICATION = 0,
TOTAL_NUMBER_OF_PROCESSES =0,
PROC_CERTIFIED_WITH_ISSUES =0,
--PROC_VERIFIED =0 ,
PROCS_FOR_CERT_DONE =0 ,
proc_evaluated =0 ,
org_with_ineffective_controls =0,
--org_certified =0,
orgs_FOR_CERT_DONE =0,
orgs_evaluated =0,
proc_with_ineffective_controls =0,
unmitigated_risks =0,
risks_verified =0,
ineffective_controls =0,
controls_verified =0,
open_issues =0,
PRO_PENDING_CERT_PRCNT =0,
PROCESSES_WITH_ISSUES_PRCNT =0,
ORG_WITH_INEFF_CONTROLS_PRCNT =0,
PROC_WITH_INEFF_CONTROLS_PRCNT =0,
UNMITIGATED_RISKS_PRCNT =0,
INEFFECTIVE_CONTROLS_PRCNT =0,
total_number_of_risks = 0,
total_number_of_ctrls = 0,
total_number_of_orgs = 0
WHERE fin_certification_id = p_certification_id;
update amw_fin_cert_eval_sum
set
PROC_PENDING_CERTIFICATION = 0,
TOTAL_NUMBER_OF_PROCESSES =0,
PROC_CERTIFIED_WITH_ISSUES =0,
--PROC_VERIFIED =0 ,
PROCS_FOR_CERT_DONE =0 ,
proc_evaluated =0 ,
org_with_ineffective_controls =0,
-- org_certified =0,
orgs_FOR_CERT_DONE =0,
orgs_evaluated =0,
proc_with_ineffective_controls =0,
unmitigated_risks =0,
risks_verified =0,
ineffective_controls =0,
controls_verified =0,
open_issues =0,
PRO_PENDING_CERT_PRCNT =0,
PROCESSES_WITH_ISSUES_PRCNT =0,
ORG_WITH_INEFF_CONTROLS_PRCNT =0,
PROC_WITH_INEFF_CONTROLS_PRCNT =0,
UNMITIGATED_RISKS_PRCNT =0,
INEFFECTIVE_CONTROLS_PRCNT =0,
total_number_of_risks = 0,
total_number_of_ctrls = 0,
total_number_of_orgs = 0
WHERE fin_certification_id IN
(select
certifcationVL.CERTIFICATION_ID
FROM AMW_CERTIFICATION_vl certifcationVL
where
certifcationVL.OBJECT_TYPE='FIN_STMT'
and certifcationVL.CERTIFICATION_STATUS in ('ACTIVE', 'DRAFT'));
select
STATEMENT_GROUP_ID,
FINANCIAL_STATEMENT_ID,
FINANCIAL_ITEM_ID
from
AMW_FIN_STMNT_ITEMS_B
where
STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
and FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
and PARENT_FINANCIAL_ITEM_ID is null;
select
STATEMENT_GROUP_ID,
FINANCIAL_STATEMENT_ID,
FINANCIAL_ITEM_ID,
PARENT_FINANCIAL_ITEM_ID
from
AMW_FIN_STMNT_ITEMS_B
where
STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
and FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
and PARENT_FINANCIAL_ITEM_ID = P_FINANCIAL_ITEM_ID ;
select
STATEMENT_GROUP_ID,
ACCOUNT_GROUP_ID,
FINANCIAL_STATEMENT_ID,
FINANCIAL_ITEM_ID,
NATURAL_ACCOUNT_ID
from
AMW_FIN_ITEMS_KEY_ACC finitemAcc
where
STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
and FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
and FINANCIAL_ITEM_ID = P_FINANCIAL_ITEM_ID
and not exists
-- make sure that children accounts whose parents are also attached to a financial item are not selected
-- again, as we will select them in next union
(
Select
KeyAccChild2.ACCOUNT_GROUP_ID ,
KeyAccChild2.NATURAL_ACCOUNT_ID,
KeyAccChild2.PARENT_NATURAL_ACCOUNT_ID,
KeyAccChild2.NATURAL_ACCOUNT_VALUE
From
AMW_FIN_KEY_ACCOUNTS_B KeyAccChild2,
AMW_FIN_ITEMS_KEY_ACC finitemAcc2
Where
(KeyAccChild2.PARENT_NATURAL_ACCOUNT_ID is not null)
and KeyAccChild2.ACCOUNT_GROUP_ID = finitemAcc.ACCOUNT_GROUP_ID
AND KeyAccChild2.NATURAL_ACCOUNT_ID = finitemAcc.NATURAL_ACCOUNT_ID
AND KeyAccChild2.PARENT_NATURAL_ACCOUNT_ID = finitemAcc2.NATURAL_ACCOUNT_ID
and KeyAccChild2.ACCOUNT_GROUP_ID = finitemAcc2.ACCOUNT_GROUP_ID
------------------- ADDED THE REST OF CRITERIA TO CHECK THE MASTER ACCOUT EXISTS FOR THE SAME STATEMENT AND ITEM
and finitemAcc2.STATEMENT_GROUP_ID = finitemAcc.STATEMENT_GROUP_ID
and finitemAcc2.FINANCIAL_STATEMENT_ID = finitemAcc.FINANCIAL_STATEMENT_ID
and finitemAcc2.FINANCIAL_ITEM_ID = finitemAcc.FINANCIAL_ITEM_ID
)
;
select
STATEMENT_GROUP_ID,
ACCOUNT_GROUP_ID,
FINANCIAL_STATEMENT_ID,
FINANCIAL_ITEM_ID,
NATURAL_ACCOUNT_ID
from
AMW_FIN_ITEMS_KEY_ACC
where
STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
and FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
and FINANCIAL_ITEM_ID = P_FINANCIAL_ITEM_ID ;
select
ACCOUNT_GROUP_ID,
NATURAL_ACCOUNT_ID
from
AMW_FIN_KEY_ACCOUNTS_B
where
PARENT_NATURAL_ACCOUNT_ID = P_ACCOUNT_ID
and ACCOUNT_GROUP_ID = P_ACCOUNT_GROUP_ID;
AMW_FINSTMT_CERT_PVT.insert_fin_cert_eval_sum(
X_FIN_CERTIFICATION_ID => M_CERTIFICATION_ID,
X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID,
X_FINANCIAL_ITEM_ID => M_FINANCIAL_ITEM_ID,
X_ACCOUNT_GROUP_ID => NULL ,
X_NATURAL_ACCOUNT_ID => NULL,
X_OBJECT_TYPE => 'FINANCIAL ITEM',
X_PROC_PENDING_CERTIFICATION => M_PROC_PENDING_CERTIFICATION,
X_TOTAL_NUMBER_OF_PROCESSES => M_TOTAL_NUMBER_OF_PROCESSES,
X_PROC_CERTIFIED_WITH_ISSUES => M_PROC_CERTIFIED_WITH_ISSUES,
--X_PROC_VERIFIED => M_PROC_VERIFIED,
X_PROCS_FOR_CERT_DONE => M_PROCS_FOR_CERT_DONE,
x_proc_evaluated => M_PROC_VERIFIED,
X_ORG_WITH_INEFFECTIVE_CTRLS => M_org_with_ineffective_ctrls,
-- X_ORG_CERTIFIED => M_org_certified,
x_orgs_FOR_CERT_DONE => M_org_certified,
x_orgs_evaluated => M_org_evaluated,
X_PROC_WITH_INEFFECTIVE_CTRLS => M_proc_with_ineffective_ctrls,
X_UNMITIGATED_RISKS => M_unmitigated_risks,
X_RISKS_VERIFIED => M_risks_verified,
X_INEFFECTIVE_CONTROLS => M_ineffective_controls,
X_CONTROLS_VERIFIED => M_controls_verified,
X_OPEN_ISSUES => M_open_issues,
X_PRO_PENDING_CERT_PRCNT => M_PRO_PENDING_CERT_PRCNT,
X_PROCESSES_WITH_ISSUES_PRCNT => M_PROCESSES_WITH_ISSUES_PRCNT,
X_ORG_WITH_INEFF_CTRLS_PRCNT => M_ORG_WITH_INEFF_CTRLS_PRCNT,
X_PROC_WITH_INEFF_CTRLS_PRCNT => M_PROC_WITH_INEFF_CTRLS_PRCNT,
X_UNMITIGATED_RISKS_PRCNT => M_UNMITIGATED_RISKS_PRCNT,
X_INEFFECTIVE_CTRLS_PRCNT => M_INEFFECTIVE_CONTROLS_PRCNT,
X_OBJ_CONTEXT => NULL,
X_CREATED_BY => g_user_id,
X_CREATION_DATE => SYSDATE,
X_LAST_UPDATED_BY => g_user_id,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATE_LOGIN => g_login_id,
X_SECURITY_GROUP_ID => NULL,
X_OBJECT_VERSION_NUMBER => NULL);
AMW_FINSTMT_CERT_PVT.insert_fin_cert_eval_sum(
X_FIN_CERTIFICATION_ID => M_CERTIFICATION_ID,
X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID,
X_FINANCIAL_ITEM_ID => M_FINANCIAL_ITEM_ID,
X_ACCOUNT_GROUP_ID => NULL,
X_NATURAL_ACCOUNT_ID => NULL,
X_OBJECT_TYPE => 'FINANCIAL ITEM',
X_PROC_PENDING_CERTIFICATION => M_PROC_PENDING_CERTIFICATION,
X_TOTAL_NUMBER_OF_PROCESSES => M_TOTAL_NUMBER_OF_PROCESSES,
X_PROC_CERTIFIED_WITH_ISSUES => M_PROC_CERTIFIED_WITH_ISSUES,
-- X_PROC_VERIFIED => M_PROC_VERIFIED,
X_PROCS_FOR_CERT_DONE => M_PROCS_FOR_CERT_DONE,
x_proc_evaluated => M_PROC_VERIFIED,
X_ORG_WITH_INEFFECTIVE_CTRLS => M_org_with_ineffective_ctrls,
-- X_ORG_CERTIFIED => M_org_certified,
x_orgs_FOR_CERT_DONE => M_org_certified,
x_orgs_evaluated => M_org_evaluated,
X_PROC_WITH_INEFFECTIVE_CTRLS => M_proc_with_ineffective_ctrls,
X_UNMITIGATED_RISKS => M_unmitigated_risks,
X_RISKS_VERIFIED => M_risks_verified,
X_INEFFECTIVE_CONTROLS => M_ineffective_controls,
X_CONTROLS_VERIFIED => M_controls_verified,
X_OPEN_ISSUES => M_open_issues,
X_PRO_PENDING_CERT_PRCNT => M_PRO_PENDING_CERT_PRCNT,
X_PROCESSES_WITH_ISSUES_PRCNT => M_PROCESSES_WITH_ISSUES_PRCNT,
X_ORG_WITH_INEFF_CTRLS_PRCNT => M_ORG_WITH_INEFF_CTRLS_PRCNT,
X_PROC_WITH_INEFF_CTRLS_PRCNT => M_PROC_WITH_INEFF_CTRLS_PRCNT,
X_UNMITIGATED_RISKS_PRCNT => M_UNMITIGATED_RISKS_PRCNT,
X_INEFFECTIVE_CTRLS_PRCNT => M_INEFFECTIVE_CONTROLS_PRCNT,
X_OBJ_CONTEXT => NULL,
X_CREATED_BY => g_user_id,
X_CREATION_DATE => SYSDATE,
X_LAST_UPDATED_BY => g_user_id,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATE_LOGIN => g_login_id,
X_SECURITY_GROUP_ID => NULL,
X_OBJECT_VERSION_NUMBER => NULL);
AMW_FINSTMT_CERT_PVT.insert_fin_cert_eval_sum(
X_FIN_CERTIFICATION_ID => M_CERTIFICATION_ID,
X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID,
X_FINANCIAL_ITEM_ID => M_FINANCIAL_ITEM_ID,
X_ACCOUNT_GROUP_ID => M_ACCOUNT_GROUP_ID ,
X_NATURAL_ACCOUNT_ID => M_NATURAL_ACCOUNT_ID,
X_OBJECT_TYPE => 'ACCOUNT',
X_PROC_PENDING_CERTIFICATION => M_PROC_PENDING_CERTIFICATION,
X_TOTAL_NUMBER_OF_PROCESSES => M_TOTAL_NUMBER_OF_PROCESSES,
X_PROC_CERTIFIED_WITH_ISSUES => M_PROC_CERTIFIED_WITH_ISSUES,
-- X_PROC_VERIFIED => M_PROC_VERIFIED,
X_PROCS_FOR_CERT_DONE => M_PROCS_FOR_CERT_DONE,
x_proc_evaluated => M_PROC_VERIFIED,
X_ORG_WITH_INEFFECTIVE_CTRLS => M_org_with_ineffective_ctrls,
-- X_ORG_CERTIFIED => M_org_certified,
x_orgs_FOR_CERT_DONE => M_org_certified,
x_orgs_evaluated => M_org_evaluated,
X_PROC_WITH_INEFFECTIVE_CTRLS => M_proc_with_ineffective_ctrls,
X_UNMITIGATED_RISKS => M_unmitigated_risks,
X_RISKS_VERIFIED => M_risks_verified,
X_INEFFECTIVE_CONTROLS => M_ineffective_controls,
X_CONTROLS_VERIFIED => M_controls_verified,
X_OPEN_ISSUES => M_open_issues,
X_PRO_PENDING_CERT_PRCNT => M_PRO_PENDING_CERT_PRCNT,
X_PROCESSES_WITH_ISSUES_PRCNT => M_PROCESSES_WITH_ISSUES_PRCNT,
X_ORG_WITH_INEFF_CTRLS_PRCNT => M_ORG_WITH_INEFF_CTRLS_PRCNT,
X_PROC_WITH_INEFF_CTRLS_PRCNT => M_PROC_WITH_INEFF_CTRLS_PRCNT,
X_UNMITIGATED_RISKS_PRCNT => M_UNMITIGATED_RISKS_PRCNT,
X_INEFFECTIVE_CTRLS_PRCNT => M_INEFFECTIVE_CONTROLS_PRCNT,
X_OBJ_CONTEXT => NULL,
X_CREATED_BY => g_user_id,
X_CREATION_DATE => SYSDATE,
X_LAST_UPDATED_BY => g_user_id,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATE_LOGIN => g_login_id,
X_SECURITY_GROUP_ID => NULL,
X_OBJECT_VERSION_NUMBER => NULL);
AMW_FINSTMT_CERT_PVT.insert_fin_cert_eval_sum(
X_FIN_CERTIFICATION_ID => M_CERTIFICATION_ID,
X_FINANCIAL_STATEMENT_ID => M_FINANCIAL_STATEMENT_ID,
X_FINANCIAL_ITEM_ID => M_FINANCIAL_ITEM_ID,
X_ACCOUNT_GROUP_ID => M_ACCOUNT_GROUP_ID ,
X_NATURAL_ACCOUNT_ID => M_NATURAL_ACCOUNT_ID_2,
X_OBJECT_TYPE => 'ACCOUNT',
X_PROC_PENDING_CERTIFICATION => M_PROC_PENDING_CERTIFICATION,
X_TOTAL_NUMBER_OF_PROCESSES => M_TOTAL_NUMBER_OF_PROCESSES,
X_PROC_CERTIFIED_WITH_ISSUES => M_PROC_CERTIFIED_WITH_ISSUES,
-- X_PROC_VERIFIED => M_PROC_VERIFIED,
X_PROCS_FOR_CERT_DONE => M_PROCS_FOR_CERT_DONE,
x_proc_evaluated => M_PROC_VERIFIED,
X_ORG_WITH_INEFFECTIVE_CTRLS => M_org_with_ineffective_ctrls,
--X_ORG_CERTIFIED => M_org_certified,
x_orgs_FOR_CERT_DONE => M_org_certified,
x_orgs_evaluated => M_org_evaluated,
X_PROC_WITH_INEFFECTIVE_CTRLS => M_proc_with_ineffective_ctrls,
X_UNMITIGATED_RISKS => M_unmitigated_risks,
X_RISKS_VERIFIED => M_risks_verified,
X_INEFFECTIVE_CONTROLS => M_ineffective_controls,
X_CONTROLS_VERIFIED => M_controls_verified,
X_OPEN_ISSUES => M_open_issues,
X_PRO_PENDING_CERT_PRCNT => M_PRO_PENDING_CERT_PRCNT,
X_PROCESSES_WITH_ISSUES_PRCNT => M_PROCESSES_WITH_ISSUES_PRCNT,
X_ORG_WITH_INEFF_CTRLS_PRCNT => M_ORG_WITH_INEFF_CTRLS_PRCNT,
X_PROC_WITH_INEFF_CTRLS_PRCNT => M_PROC_WITH_INEFF_CTRLS_PRCNT,
X_UNMITIGATED_RISKS_PRCNT => M_UNMITIGATED_RISKS_PRCNT,
X_INEFFECTIVE_CTRLS_PRCNT => M_INEFFECTIVE_CONTROLS_PRCNT,
X_OBJ_CONTEXT => NULL,
X_CREATED_BY => g_user_id,
X_CREATION_DATE => SYSDATE,
X_LAST_UPDATED_BY => g_user_id,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATE_LOGIN => g_login_id,
X_SECURITY_GROUP_ID => NULL,
X_OBJECT_VERSION_NUMBER => NULL);
procedure insert_fin_cert_eval_sum(
X_FIN_CERTIFICATION_ID IN NUMBER,
X_FINANCIAL_STATEMENT_ID IN NUMBER,
X_FINANCIAL_ITEM_ID IN NUMBER,
X_ACCOUNT_GROUP_ID IN NUMBER,
X_NATURAL_ACCOUNT_ID IN NUMBER,
X_OBJECT_TYPE IN VARCHAR,
X_PROC_PENDING_CERTIFICATION IN NUMBER,
X_TOTAL_NUMBER_OF_PROCESSES IN NUMBER,
X_PROC_CERTIFIED_WITH_ISSUES IN NUMBER,
X_PROCS_FOR_CERT_DONE IN NUMBER,
x_proc_evaluated IN NUMBER,
X_ORG_WITH_INEFFECTIVE_CTRLS IN NUMBER,
-- X_ORG_CERTIFIED IN NUMBER,
x_orgs_FOR_CERT_DONE IN NUMBER,
x_orgs_evaluated IN NUMBER,
X_PROC_WITH_INEFFECTIVE_CTRLS IN NUMBER,
X_UNMITIGATED_RISKS IN NUMBER,
X_RISKS_VERIFIED IN NUMBER,
X_INEFFECTIVE_CONTROLS IN NUMBER,
X_CONTROLS_VERIFIED IN NUMBER,
X_OPEN_ISSUES IN NUMBER,
X_PRO_PENDING_CERT_PRCNT IN NUMBER,
X_PROCESSES_WITH_ISSUES_PRCNT IN NUMBER,
X_ORG_WITH_INEFF_CTRLS_PRCNT IN NUMBER,
X_PROC_WITH_INEFF_CTRLS_PRCNT IN NUMBER,
X_UNMITIGATED_RISKS_PRCNT IN NUMBER,
X_INEFFECTIVE_CTRLS_PRCNT IN NUMBER,
X_OBJ_CONTEXT IN NUMBER,
X_CREATED_BY IN NUMBER,
X_CREATION_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATE_LOGIN IN NUMBER,
X_SECURITY_GROUP_ID IN NUMBER,
X_OBJECT_VERSION_NUMBER IN NUMBER
)
IS begin
DECLARE
M_COUNT NUMBER := 0;
SELECT COUNT(1) INTO M_COUNT FROM amw_fin_cert_eval_sum
WHERE FIN_CERTIFICATION_ID = X_FIN_CERTIFICATION_ID
AND FINANCIAL_STATEMENT_ID = X_FINANCIAL_STATEMENT_ID
AND NVL(FINANCIAL_ITEM_ID,0) = NVL(X_FINANCIAL_ITEM_ID,0)
AND NVL(NATURAL_ACCOUNT_ID,0) = NVL(X_NATURAL_ACCOUNT_ID,0)
AND NVL(ACCOUNT_GROUP_ID,0) = NVL(X_ACCOUNT_GROUP_ID,0)
AND OBJECT_TYPE = X_OBJECT_TYPE;
insert into amw_fin_cert_eval_sum(
FIN_CERTIFICATION_ID ,
FINANCIAL_STATEMENT_ID ,
FINANCIAL_ITEM_ID ,
NATURAL_ACCOUNT_ID ,
ACCOUNT_GROUP_ID ,
OBJECT_TYPE ,
PROC_PENDING_CERTIFICATION ,
TOTAL_NUMBER_OF_PROCESSES ,
PROC_CERTIFIED_WITH_ISSUES ,
PROCS_FOR_CERT_DONE ,
proc_evaluated ,
ORG_WITH_INEFFECTIVE_CONTROLS ,
orgs_FOR_CERT_DONE ,
--org_certified ,
orgs_evaluated ,
PROC_WITH_INEFFECTIVE_CONTROLS ,
UNMITIGATED_RISKS ,
RISKS_VERIFIED ,
INEFFECTIVE_CONTROLS ,
CONTROLS_VERIFIED ,
OPEN_ISSUES ,
PRO_PENDING_CERT_PRCNT ,
PROCESSES_WITH_ISSUES_PRCNT ,
ORG_WITH_INEFF_CONTROLS_PRCNT ,
PROC_WITH_INEFF_CONTROLS_PRCNT ,
UNMITIGATED_RISKS_PRCNT ,
INEFFECTIVE_CONTROLS_PRCNT ,
OBJ_CONTEXT ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
SECURITY_GROUP_ID ,
OBJECT_VERSION_NUMBER )
values
(
X_FIN_CERTIFICATION_ID,
X_FINANCIAL_STATEMENT_ID,
X_FINANCIAL_ITEM_ID ,
X_NATURAL_ACCOUNT_ID ,
X_ACCOUNT_GROUP_ID ,
X_OBJECT_TYPE ,
X_PROC_PENDING_CERTIFICATION,
X_TOTAL_NUMBER_OF_PROCESSES ,
X_PROC_CERTIFIED_WITH_ISSUES,
X_TOTAL_NUMBER_OF_PROCESSES ,
-- X_PROCS_FOR_CERT_DONE -- was replaced by total processes,
X_TOTAL_NUMBER_OF_PROCESSES , --x_proc_evaluated was commented as the denominator
--for Proc with Ineffective Ctrl became total processes
--x_proc_evaluated ,
-- X_PROC_VERIFIED not used ,
X_ORG_WITH_INEFFECTIVE_CTRLS,
--X_ORG_CERTIFIED ,
x_orgs_FOR_CERT_DONE ,
x_orgs_evaluated ,
X_PROC_WITH_INEFFECTIVE_CTRLS,
X_UNMITIGATED_RISKS ,
X_RISKS_VERIFIED ,
X_INEFFECTIVE_CONTROLS ,
X_CONTROLS_VERIFIED ,
X_OPEN_ISSUES ,
round(X_PRO_PENDING_CERT_PRCNT) ,
round(X_PROCESSES_WITH_ISSUES_PRCNT),
round(X_ORG_WITH_INEFF_CTRLS_PRCNT) ,
round(X_PROC_WITH_INEFF_CTRLS_PRCNT),
round(X_UNMITIGATED_RISKS_PRCNT) ,
round(X_INEFFECTIVE_CTRLS_PRCNT) ,
X_OBJ_CONTEXT ,
X_CREATED_BY ,
X_CREATION_DATE ,
X_LAST_UPDATED_BY ,
X_LAST_UPDATE_DATE ,
X_LAST_UPDATE_LOGIN ,
X_SECURITY_GROUP_ID ,
X_OBJECT_VERSION_NUMBER
);
else -- update
update amw_fin_cert_eval_sum set
FIN_CERTIFICATION_ID
= X_FIN_CERTIFICATION_ID,
FINANCIAL_STATEMENT_ID
= X_FINANCIAL_STATEMENT_ID,
FINANCIAL_ITEM_ID
= X_FINANCIAL_ITEM_ID ,
NATURAL_ACCOUNT_ID
= X_NATURAL_ACCOUNT_ID,
ACCOUNT_GROUP_ID
= X_ACCOUNT_GROUP_ID ,
OBJECT_TYPE
= X_OBJECT_TYPE ,
PROC_PENDING_CERTIFICATION
= X_PROC_PENDING_CERTIFICATION,
TOTAL_NUMBER_OF_PROCESSES
= X_TOTAL_NUMBER_OF_PROCESSES,
PROC_CERTIFIED_WITH_ISSUES
= X_PROC_CERTIFIED_WITH_ISSUES,
PROCS_FOR_CERT_DONE
= X_TOTAL_NUMBER_OF_PROCESSES ,
-- X_PROCS_FOR_CERT_DONE -- was replaced by total processes,
-- = X_PROCS_FOR_CERT_DONE ,
proc_evaluated
= X_TOTAL_NUMBER_OF_PROCESSES , --x_proc_evaluated was commented as the denominator
--for Proc with Ineffective Ctrl became total processes
----- = X_proc_evaluated ,
ORG_WITH_INEFFECTIVE_CONTROLS
= X_ORG_WITH_INEFFECTIVE_CTRLS,
orgs_FOR_CERT_DONE
= X_orgs_FOR_CERT_DONE ,
orgs_evaluated
= X_orgs_evaluated ,
PROC_WITH_INEFFECTIVE_CONTROLS
= X_PROC_WITH_INEFFECTIVE_CTRLS,
UNMITIGATED_RISKS
= X_UNMITIGATED_RISKS ,
RISKS_VERIFIED
= X_RISKS_VERIFIED ,
INEFFECTIVE_CONTROLS
= X_INEFFECTIVE_CONTROLS,
CONTROLS_VERIFIED
= X_CONTROLS_VERIFIED ,
OPEN_ISSUES
= X_OPEN_ISSUES ,
PRO_PENDING_CERT_PRCNT
= round(X_PRO_PENDING_CERT_PRCNT),
PROCESSES_WITH_ISSUES_PRCNT
= round(X_PROCESSES_WITH_ISSUES_PRCNT),
ORG_WITH_INEFF_CONTROLS_PRCNT
= round(X_ORG_WITH_INEFF_CTRLS_PRCNT) ,
PROC_WITH_INEFF_CONTROLS_PRCNT
= round(X_PROC_WITH_INEFF_CTRLS_PRCNT),
UNMITIGATED_RISKS_PRCNT
= round(X_UNMITIGATED_RISKS_PRCNT) ,
INEFFECTIVE_CONTROLS_PRCNT
= round(X_INEFFECTIVE_CTRLS_PRCNT) ,
OBJ_CONTEXT
= X_OBJ_CONTEXT ,
CREATED_BY
= X_CREATED_BY ,
CREATION_DATE
= X_CREATION_DATE ,
LAST_UPDATED_BY
= X_LAST_UPDATED_BY ,
LAST_UPDATE_DATE
= X_LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN
= X_LAST_UPDATE_LOGIN ,
SECURITY_GROUP_ID
= X_SECURITY_GROUP_ID ,
OBJECT_VERSION_NUMBER
= X_OBJECT_VERSION_NUMBER
WHERE FIN_CERTIFICATION_ID = X_FIN_CERTIFICATION_ID
AND FINANCIAL_STATEMENT_ID = X_FINANCIAL_STATEMENT_ID
AND NVL(FINANCIAL_ITEM_ID,0) = NVL(X_FINANCIAL_ITEM_ID,0)
AND NVL(NATURAL_ACCOUNT_ID,0) = NVL(X_NATURAL_ACCOUNT_ID,0)
AND NVL(ACCOUNT_GROUP_ID,0) = NVL(X_ACCOUNT_GROUP_ID,0)
AND OBJECT_TYPE = X_OBJECT_TYPE;
END insert_fin_cert_eval_sum;
Select
GL_PERIODS.START_DATE ,
GL_PERIODS.END_DATE
into P_start_date, P_end_Date
from
AMW_CERTIFICATION_VL CERTIFICATION,
amw_gl_periods_v GL_PERIODS
WHERE
GL_PERIODS.PERIOD_NAME = CERTIFICATION.CERTIFICATION_PERIOD_NAME
AND GL_PERIODS.PERIOD_SET_NAME = CERTIFICATION.CERTIFICATION_PERIOD_SET_NAME
and CERTIFICATION.OBJECT_TYPE='FIN_STMT'
AND CERTIFICATION.CERTIFICATION_ID = P_Certification_ID;
Select
Count(1) into P_TOTAL_NUMBER_OF_PROCESSES from
(select distinct procRln.ORGANIZATION_ID ,procRln.CHILD_PROCESS_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs .object_type = 'PROCESS_ORG' and
AccProcs .pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
--(select
-- distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH acc.natural_account_id = P_NATURAL_ACCOUNT_ID
-- and acc.account_group_id = P_account_group_id
--CONNECT BY PRIOR
-- acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from AMW_FIN_KEY_ACCT_FLAT acc
where acc.parent_natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where acc.natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
)
))
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID);
Select
count(1) into P_PROCS_IN_CERTIFICATION
from
(
Select distinct orgprocess.PROCESS_ID, orgprocess.ORGANIZATION_ID
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess
WHERE
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'CERTIFICATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORG_PROCESS'
and opinion.PK2_VALUE in (select PROC_CERT_ID from AMW_FIN_PROC_CERT_RELAN where FIN_STMT_CERT_ID = p_fin_cert_id)
and (opinion.PK3_VALUE = orgprocess.ORGANIZATION_ID
and opinion.PK1_VALUE = orgprocess.PROCESS_ID)
and ( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
in
(select procRln.ORGANIZATION_ID ,procRln.CHILD_PROCESS_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs .object_type = 'PROCESS_ORG' and
AccProcs .pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
-- (select
-- distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH acc.natural_account_id = P_NATURAL_ACCOUNT_ID
-- and acc.account_group_id = P_account_group_id
--CONNECT BY PRIOR
-- acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from AMW_FIN_KEY_ACCT_FLAT acc
where acc.parent_natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where acc.natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
)
))
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
Select
count(1) INTO P_PROC_CERTIFIED_WITH_ISSUES
from
(
Select distinct orgprocess.PROCESS_ID, orgprocess.ORGANIZATION_ID
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess
WHERE
(opinion.AUTHORED_DATE in
(Select
MAX(opinion2.AUTHORED_DATE)
from
AMW_OPINIONS_V opinion2
where
opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID
and opinion2.PK2_VALUE in
(select PROC_CERT_ID from AMW_FIN_PROC_CERT_RELAN where FIN_STMT_CERT_ID = p_fin_cert_id) and
opinion2.PK1_VALUE = opinion.PK1_VALUE and
opinion2.PK3_VALUE = opinion.PK3_VALUE
) AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'CERTIFICATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORG_PROCESS' and
opinion.audit_result_CODE <> 'EFFECTIVE')
and opinion.PK2_VALUE in
(select PROC_CERT_ID from AMW_FIN_PROC_CERT_RELAN where FIN_STMT_CERT_ID = p_fin_cert_id)
and (opinion.PK3_VALUE = orgprocess.ORGANIZATION_ID
and opinion.PK1_VALUE = orgprocess.PROCESS_ID)
and ( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
in
(select procRln.ORGANIZATION_ID ,procRln.CHILD_PROCESS_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs .object_type = 'PROCESS_ORG' and
AccProcs .pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
-- (select
-- distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH acc.natural_account_id = P_NATURAL_ACCOUNT_ID
-- and acc.account_group_id = P_account_group_id
--CONNECT BY PRIOR
-- acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from AMW_FIN_KEY_ACCT_FLAT acc
where acc.parent_natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where acc.natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
)
))
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
Select
count(1) into P_org_with_ineffective_ctrls
from (
select distinct orgprocess.ORGANIZATION_ID
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess
WHERE
(opinion.AUTHORED_DATE in
(Select
MAX(opinion2.AUTHORED_DATE)
from
AMW_OPINIONS_V opinion2
where
opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
opinion2.PK1_VALUE = opinion.PK1_VALUE
--- Commented chekcing whether the opinion date falls within the period as
--- per discussion with Say and Bastin on 2/9/04
---AND
---- opinion2.AUTHORED_DATE >= p_start_date and
-- opinion2.AUTHORED_DATE <= p_end_date
) AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'EVALUATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORGANIZATION'
and opinion.audit_result_CODE <> 'EFFECTIVE'
)
and opinion.PK1_VALUE = orgprocess.ORGANIZATION_ID
and orgprocess.ORGANIZATION_ID
in
(select procRln.ORGANIZATION_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs .object_type = 'PROCESS_ORG' and
AccProcs .pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
-- (select
-- distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH acc.natural_account_id = P_NATURAL_ACCOUNT_ID
-- and acc.account_group_id = P_account_group_id
--CONNECT BY PRIOR
-- acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from AMW_FIN_KEY_ACCT_FLAT acc
where acc.parent_natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where acc.natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
)
))
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
Select
count(1) into P_org_cert_with_issues
from
(
select distinct orgprocess.ORGANIZATION_ID
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess
WHERE
(opinion.AUTHORED_DATE in
(Select
MAX(opinion2.AUTHORED_DATE)
from
AMW_OPINIONS_V opinion2
where
opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
opinion2.PK1_VALUE = opinion.PK1_VALUE AND
opinion2.AUTHORED_DATE >= p_start_date and
opinion2.AUTHORED_DATE <= p_end_date
) AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'CERTIFICATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORGANIZATION'
and opinion.audit_result_CODE <> 'EFFECTIVE'
)
and opinion.PK1_VALUE = orgprocess.ORGANIZATION_ID
and orgprocess.ORGANIZATION_ID
in
(select procRln.ORGANIZATION_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs .object_type = 'PROCESS_ORG' and
AccProcs .pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
-- (select
-- distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH acc.natural_account_id = P_NATURAL_ACCOUNT_ID
-- and acc.account_group_id = P_account_group_id
--CONNECT BY PRIOR
-- acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from AMW_FIN_KEY_ACCT_FLAT acc
where acc.parent_natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where acc.natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
)
))
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
Select
count(1) into P_org_certified
from
(
select distinct orgprocess.ORGANIZATION_ID
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess
WHERE
(opinion.AUTHORED_DATE in
(Select
MAX(opinion2.AUTHORED_DATE)
from
AMW_OPINIONS_V opinion2
where
opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
opinion2.PK1_VALUE = opinion.PK1_VALUE AND
opinion2.AUTHORED_DATE >= p_start_date and
opinion2.AUTHORED_DATE <= p_end_date
) AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'CERTIFICATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORGANIZATION'
)
and opinion.PK1_VALUE = orgprocess.ORGANIZATION_ID
and orgprocess.ORGANIZATION_ID
in
(select procRln.ORGANIZATION_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs .object_type = 'PROCESS_ORG' and
AccProcs .pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
-- (select
-- distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH acc.natural_account_id = P_NATURAL_ACCOUNT_ID
-- and acc.account_group_id = P_account_group_id
--CONNECT BY PRIOR
-- acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from AMW_FIN_KEY_ACCT_FLAT acc
where acc.parent_natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where acc.natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
)
))
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
Select
Count(1)
into P_org_evaluated
from
(select distinct procRln.ORGANIZATION_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs .object_type = 'PROCESS_ORG' and
AccProcs .pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
-- (select
-- distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH acc.natural_account_id = P_NATURAL_ACCOUNT_ID
-- and acc.account_group_id = P_account_group_id
--CONNECT BY PRIOR
-- acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from AMW_FIN_KEY_ACCT_FLAT acc
where acc.parent_natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where acc.natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
)
))
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID);
Select
count(1) into P_org_evaluated
from
(
select distinct orgprocess.ORGANIZATION_ID
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess
WHERE
(opinion.AUTHORED_DATE in
(Select
MAX(opinion2.AUTHORED_DATE)
from
AMW_OPINIONS_V opinion2
where
opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
opinion2.PK1_VALUE = opinion.PK1_VALUE AND
----- **** opinion2.AUTHORED_DATE >= p_start_date and
opinion2.AUTHORED_DATE <= p_end_date
) AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'EVALUATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORGANIZATION'
)
and opinion.PK1_VALUE = orgprocess.ORGANIZATION_ID
and orgprocess.ORGANIZATION_ID
in
(select procRln.ORGANIZATION_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs .object_type = 'PROCESS_ORG' and
AccProcs .pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
-- (select
-- distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH acc.natural_account_id = P_NATURAL_ACCOUNT_ID
-- and acc.account_group_id = P_account_group_id
--CONNECT BY PRIOR
-- acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from AMW_FIN_KEY_ACCT_FLAT acc
where acc.parent_natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where acc.natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
)
))
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
Select
count(1) into P_proc_with_ineffective_ctrls
from (
Select distinct orgprocess.PROCESS_ID, orgprocess.ORGANIZATION_ID
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess
WHERE
(opinion.AUTHORED_DATE in
(Select
MAX(opinion2.AUTHORED_DATE)
from
AMW_OPINIONS_V opinion2
where
opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID and
opinion2.PK1_VALUE = opinion.PK1_VALUE and
opinion2.PK3_VALUE = opinion.PK3_VALUE
--- Commented chekcing whether the opinion date falls within the period as
--- per discussion with Say and Bastin on 2/9/04
-- AND
--- ********opinion2.AUTHORED_DATE >= p_start_date and
-- opinion2.AUTHORED_DATE <= p_end_date
) AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'EVALUATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORG_PROCESS' and
opinion.audit_result_CODE <> 'EFFECTIVE'
)
and (opinion.PK3_VALUE = orgprocess.ORGANIZATION_ID
and opinion.PK1_VALUE = orgprocess.PROCESS_ID)
and ( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
in
(select procRln.ORGANIZATION_ID ,procRln.CHILD_PROCESS_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs .object_type = 'PROCESS_ORG' and
AccProcs .pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
-- (select
-- distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH acc.natural_account_id = P_NATURAL_ACCOUNT_ID
-- and acc.account_group_id = P_account_group_id
--CONNECT BY PRIOR
-- acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from AMW_FIN_KEY_ACCT_FLAT acc
where acc.parent_natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where acc.natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
)
))
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
Select
count(1) into P_PROCS_EVALUATED from
(
Select distinct orgprocess.PROCESS_ID, orgprocess.ORGANIZATION_ID
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess
WHERE
(opinion.AUTHORED_DATE in
(Select
MAX(opinion2.AUTHORED_DATE)
from
AMW_OPINIONS_V opinion2
where
opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID and
opinion2.PK1_VALUE = opinion.PK1_VALUE and
opinion2.PK3_VALUE = opinion.PK3_VALUE AND
--- ****** opinion2.AUTHORED_DATE >= p_start_date and
opinion2.AUTHORED_DATE <= p_end_date
) AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'EVALUATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORG_PROCESS'
--- *** RESULT IS IRRILEVENT FOR THIS COMPUTATION and opinion.audit_result_CODE <> 'EFFECTIVE'
)
and (opinion.PK3_VALUE = orgprocess.ORGANIZATION_ID
and opinion.PK1_VALUE = orgprocess.PROCESS_ID)
and ( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
in
(select procRln.ORGANIZATION_ID ,procRln.CHILD_PROCESS_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs .object_type = 'PROCESS_ORG' and
AccProcs .pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
-- (select
-- distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH acc.natural_account_id = P_NATURAL_ACCOUNT_ID
-- and acc.account_group_id = P_account_group_id
--CONNECT BY PRIOR
-- acc.natural_account_id = acc.parent_natural_account_id and PRIOR
--acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from AMW_FIN_KEY_ACCT_FLAT acc
where acc.parent_natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where acc.natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
)
))
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
Select
count(1) into p_ineffective_controls
from (
select distinct ctrlassoc.control_id, orgprocess.organization_id
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess,
amw_risk_associations riskassoc,
amw_control_associations ctrlassoc
WHERE
(opinion.AUTHORED_DATE in
(Select
MAX(opinion2.AUTHORED_DATE)
from
AMW_OPINIONS_V opinion2
where
opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
opinion2.PK1_VALUE = opinion.PK1_VALUE and
opinion2.PK3_VALUE = opinion.PK3_VALUE
---AND
--- Commented chekcing whether the opinion date falls within the period as
--- per discussion with Say and Bastin on 2/9/04
--- opinion2.AUTHORED_DATE >= p_start_date and
---opinion2.AUTHORED_DATE <= p_end_date
)AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'EVALUATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORG_CONTROL' and
opinion.pk1_value = ctrlassoc.control_id
and opinion.pk3_value = orgprocess.organization_id --??
-- and opinion.pk4_value is null
-- and opinion.pk5_value is null
and orgprocess.process_organization_id = riskassoc.pk1
and riskassoc.object_type = 'PROCESS_ORG'
and riskassoc.risk_association_id = ctrlassoc.pk1
and ctrlassoc.object_type = 'RISK_ORG'
and opinion.audit_result_CODE <> 'EFFECTIVE')
and opinion.pk3_value = orgprocess.ORGANIZATION_ID
--and opinion.pk4_value = orgprocess.PROCESS_ID and
and ( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
in
-- and ( opinion.pk4_value ) in
(select procRln.ORGANIZATION_ID ,procRln.CHILD_PROCESS_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs .object_type = 'PROCESS_ORG' and
AccProcs .pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
-- (select
-- distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH acc.natural_account_id = P_NATURAL_ACCOUNT_ID
-- and acc.account_group_id = P_account_group_id
--CONNECT BY PRIOR
-- acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from AMW_FIN_KEY_ACCT_FLAT acc
where acc.parent_natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where acc.natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
)
))
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
Select
count(1) into p_unmitigated_risks
from (select distinct riskassoc.risk_id ,orgprocess.organization_id, orgprocess.Process_ID
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess,
amw_risk_associations riskassoc
WHERE
(opinion.AUTHORED_DATE in
(Select
MAX(opinion2.AUTHORED_DATE)
from
AMW_OPINIONS_V opinion2
where
opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
opinion2.PK1_VALUE = opinion.PK1_VALUE and
opinion2.PK3_VALUE = opinion.PK3_VALUE and
opinion2.PK4_VALUE = opinion.PK4_VALUE
--- Commented chekcing whether the opinion date falls within the period as
--- per discussion with Say and Bastin on 2/9/04
-- and
-- opinion2.AUTHORED_DATE >= p_start_date and
-- opinion2.AUTHORED_DATE <= p_end_date
) AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'EVALUATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORG_PROCESS_RISK' and
-- AccProcs .natural_account_id = P_NATURAL_ACCOUNT_ID and -- from procedure parameter
opinion.pk1_value = riskassoc.risk_id
--and opinion.pk3_value = orgprocess.organization_id
-- and opinion.pk4_value = orgprocess.Process_ID
and orgprocess.process_organization_id = riskassoc.pk1
and riskassoc.object_type = 'PROCESS_ORG'
and opinion.audit_result_CODE <> 'EFFECTIVE')
-- and (opinion.pk3_value , opinion.pk4_value )
and (opinion.pk3_value = orgprocess.ORGANIZATION_ID and opinion.pk4_value = orgprocess.PROCESS_ID ) and
( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
in
-- and ( opinion.pk4_value ) in
(select procRln.ORGANIZATION_ID ,procRln.CHILD_PROCESS_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs .object_type = 'PROCESS_ORG' and
AccProcs .pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
-- (select
-- distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH acc.natural_account_id = P_NATURAL_ACCOUNT_ID
-- and acc.account_group_id = P_account_group_id
--CONNECT BY PRIOR
-- acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from AMW_FIN_KEY_ACCT_FLAT acc
where acc.parent_natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where acc.natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
)
))
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
Select
count(1) into p_risks_verified
from (select distinct riskassoc.risk_id ,orgprocess.organization_id, orgprocess.Process_ID
FROM
amw_process_org_basicinfo_v orgprocess,
amw_risk_associations riskassoc
WHERE
orgprocess.process_organization_id = riskassoc.pk1
and riskassoc.object_type = 'PROCESS_ORG' AND
( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
in
(select procRln.ORGANIZATION_ID ,procRln.CHILD_PROCESS_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs.object_type = 'PROCESS_ORG' and
AccProcs .pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
-- (select
-- distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH acc.natural_account_id = P_NATURAL_ACCOUNT_ID
-- and acc.account_group_id = P_account_group_id
--CONNECT BY PRIOR
-- acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from AMW_FIN_KEY_ACCT_FLAT acc
where acc.parent_natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where acc.natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
)
))
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
Select
count(1) into p_risks_verified
from (select distinct riskassoc.risk_id ,orgprocess.organization_id, orgprocess.Process_ID
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess,
amw_risk_associations riskassoc
WHERE
(opinion.AUTHORED_DATE in
(Select
MAX(opinion2.AUTHORED_DATE)
from
AMW_OPINIONS_V opinion2
where
opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
opinion2.PK1_VALUE = opinion.PK1_VALUE and
opinion2.PK3_VALUE = opinion.PK3_VALUE and
opinion2.PK4_VALUE = opinion.PK4_VALUE
and
opinion2.AUTHORED_DATE >= p_start_date and
opinion2.AUTHORED_DATE <= p_end_date
) AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'EVALUATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORG_PROCESS_RISK' and
-------- AccProcs .natural_account_id = P_NATURAL_ACCOUNT_ID and -- from procedure parameter
opinion.pk1_value = riskassoc.risk_id
--------and opinion.pk3_value = orgprocess.organization_id
------ and opinion.pk4_value = orgprocess.Process_ID
and orgprocess.process_organization_id = riskassoc.pk1
and riskassoc.object_type = 'PROCESS_ORG'
)
--- and (opinion.pk3_value , opinion.pk4_value )
and (opinion.pk3_value = orgprocess.ORGANIZATION_ID and opinion.pk4_value = orgprocess.PROCESS_ID ) and
( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
in
-- and ( opinion.pk4_value ) in
(select procRln.ORGANIZATION_ID ,procRln.CHILD_PROCESS_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs .object_type = 'PROCESS_ORG' and
AccProcs .pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
-- (select
-- distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH acc.natural_account_id = P_NATURAL_ACCOUNT_ID
-- and acc.account_group_id = P_account_group_id
--CONNECT BY PRIOR
-- acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from AMW_FIN_KEY_ACCT_FLAT acc
where acc.parent_natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where acc.natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
)
))
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
SELECT
COUNT(1) into p_controls_verified
FROM
( select distinct ctrlassoc.control_id, orgprocess.organization_id
FROM
amw_process_org_basicinfo_v orgprocess,
amw_risk_associations riskassoc,
amw_control_associations ctrlassoc
WHERE
orgprocess.process_organization_id = riskassoc.pk1
and riskassoc.object_type = 'PROCESS_ORG'
and riskassoc.risk_association_id = ctrlassoc.pk1
and ctrlassoc.object_type = 'RISK_ORG'
and ( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
in
(select procRln.ORGANIZATION_ID ,procRln.CHILD_PROCESS_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs .object_type = 'PROCESS_ORG' and
AccProcs .pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
-- (select
-- distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH acc.natural_account_id = P_NATURAL_ACCOUNT_ID
-- and acc.account_group_id = P_account_group_id
--CONNECT BY PRIOR
-- acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from AMW_FIN_KEY_ACCT_FLAT acc
where acc.parent_natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where acc.natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
)
))
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
SELECT
COUNT(1) into p_controls_verified
FROM
(select distinct ctrlassoc.control_id, orgprocess.organization_id
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess,
amw_risk_associations riskassoc,
amw_control_associations ctrlassoc
WHERE
(opinion.AUTHORED_DATE in
(Select
MAX(opinion2.AUTHORED_DATE)
from
AMW_OPINIONS_V opinion2
where
opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
opinion2.PK1_VALUE = opinion.PK1_VALUE and
opinion2.PK3_VALUE = opinion.PK3_VALUE AND
opinion2.AUTHORED_DATE >= p_start_date and
opinion2.AUTHORED_DATE <= p_end_date
)AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'EVALUATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORG_CONTROL' and
opinion.pk1_value = ctrlassoc.control_id
and opinion.pk3_value = orgprocess.organization_id --??
-- and opinion.pk4_value is null
-- and opinion.pk5_value is null
and orgprocess.process_organization_id = riskassoc.pk1
and riskassoc.object_type = 'PROCESS_ORG'
and riskassoc.risk_association_id = ctrlassoc.pk1
and ctrlassoc.object_type = 'RISK_ORG')
and opinion.pk3_value = orgprocess.ORGANIZATION_ID
--and opinion.pk4_value = orgprocess.PROCESS_ID and
and ( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
in
-- and ( opinion.pk4_value ) in
(select procRln.ORGANIZATION_ID ,procRln.CHILD_PROCESS_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs .object_type = 'PROCESS_ORG' and
AccProcs .pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
-- (select
-- distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH acc.natural_account_id = P_NATURAL_ACCOUNT_ID
-- and acc.account_group_id = P_account_group_id
--CONNECT BY PRIOR
-- acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id)
( select distinct acc.child_natural_account_id
from AMW_FIN_KEY_ACCT_FLAT acc
where acc.parent_natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where acc.natural_account_id = p_natural_account_id
and acc.account_group_id = p_account_group_id
)
))
CONNECT BY
PRIOR procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
select COUNT(1) into P_TOTAL_NUMBER_OF_PROCESSES from (
(select distinct procRln.ORGANIZATION_ID ,procRln.CHILD_PROCESS_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs.object_type = 'PROCESS_ORG' and
AccProcs.pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
--(select distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ( (acc.natural_account_id, acc.account_group_id) in
-- (select finkeyacc.NATURAL_ACCOUNT_ID,finkeyacc.account_group_id from AMW_FIN_ITEMS_KEY_ACC finkeyacc
--where
-- finkeyacc.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and finkeyacc.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- and finkeyacc.FINANCIAL_ITEM_ID IN (select
-- distinct(stmtitem.FINANCIAL_ITEM_ID )
-- from AMW_FIN_STMNT_ITEMS_B stmtitem
-- START WITH (stmtitem.FINANCIAL_ITEM_ID = P_FINANCIAL_ITEM_ID
-- and stmtitem.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and stmtitem.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- )
-- CONNECT BY PRIOR
-- stmtitem.FINANCIAL_ITEM_ID = stmtitem.PARENT_FINANCIAL_ITEM_ID
--and PRIOR stmtitem.STATEMENT_GROUP_ID = stmtitem.STATEMENT_GROUP_ID
--and PRIOR stmtitem.FINANCIAL_STATEMENT_ID = stmtitem.FINANCIAL_STATEMENT_ID)
--)
--)
-- CONNECT BY PRIOR
--acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id) -- end of acc
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
)
)
)
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
select count(1) into P_PROCS_IN_CERTIFICATION from (
Select distinct orgprocess.PROCESS_ID, orgprocess.ORGANIZATION_ID
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess
WHERE
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'CERTIFICATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORG_PROCESS'
and opinion.PK2_VALUE in (select PROC_CERT_ID from AMW_FIN_PROC_CERT_RELAN where FIN_STMT_CERT_ID = p_fin_cert_id)
and (opinion.PK3_VALUE = orgprocess.ORGANIZATION_ID
and opinion.PK1_VALUE = orgprocess.PROCESS_ID)
and ( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
in
(select procRln.ORGANIZATION_ID ,procRln.CHILD_PROCESS_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs.object_type = 'PROCESS_ORG' and
AccProcs.pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
--(select distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ( (acc.natural_account_id, acc.account_group_id) in
-- (select finkeyacc.NATURAL_ACCOUNT_ID,finkeyacc.account_group_id from AMW_FIN_ITEMS_KEY_ACC finkeyacc
--where
-- finkeyacc.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and finkeyacc.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- and finkeyacc.FINANCIAL_ITEM_ID IN (select
-- distinct(stmtitem.FINANCIAL_ITEM_ID )
-- from AMW_FIN_STMNT_ITEMS_B stmtitem
-- START WITH (stmtitem.FINANCIAL_ITEM_ID = P_FINANCIAL_ITEM_ID
-- and stmtitem.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and stmtitem.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- )
-- CONNECT BY PRIOR
-- stmtitem.FINANCIAL_ITEM_ID = stmtitem.PARENT_FINANCIAL_ITEM_ID
--and PRIOR stmtitem.STATEMENT_GROUP_ID = stmtitem.STATEMENT_GROUP_ID
--and PRIOR stmtitem.FINANCIAL_STATEMENT_ID = stmtitem.FINANCIAL_STATEMENT_ID)
--)
--)
-- CONNECT BY PRIOR
--acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id) -- end of acc
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
)
)
)
--) --?
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
select count(1) into P_PROCS_EVALUATED from (
Select distinct orgprocess.PROCESS_ID, orgprocess.ORGANIZATION_ID
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess
WHERE
(opinion.AUTHORED_DATE in
(Select
MAX(opinion2.AUTHORED_DATE)
from
AMW_OPINIONS_V opinion2
where
opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID and
opinion2.PK1_VALUE = opinion.PK1_VALUE and
opinion2.PK3_VALUE = opinion.PK3_VALUE
--AND
--opinion2.AUTHORED_DATE >= p_start_date
--and
--opinion2.AUTHORED_DATE <= p_end_date
) AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'EVALUATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORG_PROCESS' and
opinion.audit_result_CODE <> 'EFFECTIVE' )
and (opinion.PK3_VALUE = orgprocess.ORGANIZATION_ID
and opinion.PK1_VALUE = orgprocess.PROCESS_ID)
and ( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
in
(select procRln.ORGANIZATION_ID ,procRln.CHILD_PROCESS_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs.object_type = 'PROCESS_ORG' and
AccProcs.pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
--(select distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ( (acc.natural_account_id, acc.account_group_id) in
-- (select finkeyacc.NATURAL_ACCOUNT_ID,finkeyacc.account_group_id from AMW_FIN_ITEMS_KEY_ACC finkeyacc
--where
-- finkeyacc.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and finkeyacc.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- and finkeyacc.FINANCIAL_ITEM_ID IN (select
-- distinct(stmtitem.FINANCIAL_ITEM_ID )
-- from AMW_FIN_STMNT_ITEMS_B stmtitem
-- START WITH (stmtitem.FINANCIAL_ITEM_ID = P_FINANCIAL_ITEM_ID
-- and stmtitem.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and stmtitem.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- )
-- CONNECT BY PRIOR
-- stmtitem.FINANCIAL_ITEM_ID = stmtitem.PARENT_FINANCIAL_ITEM_ID
--and PRIOR stmtitem.STATEMENT_GROUP_ID = stmtitem.STATEMENT_GROUP_ID
--and PRIOR stmtitem.FINANCIAL_STATEMENT_ID = stmtitem.FINANCIAL_STATEMENT_ID)
--)
--)
-- CONNECT BY PRIOR
--acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id) -- end of acc
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
)
)
)
--) --?
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
select count(1) into P_PROC_CERTIFIED_WITH_ISSUES from (
Select distinct orgprocess.PROCESS_ID, orgprocess.ORGANIZATION_ID
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess
WHERE
(opinion.AUTHORED_DATE in
(Select
MAX(opinion2.AUTHORED_DATE)
from
AMW_OPINIONS_V opinion2
where
opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID
and opinion2.PK2_VALUE in
(select PROC_CERT_ID from AMW_FIN_PROC_CERT_RELAN where FIN_STMT_CERT_ID = p_fin_cert_id) and
opinion2.PK1_VALUE = opinion.PK1_VALUE and
opinion2.PK3_VALUE = opinion.PK3_VALUE ) AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'CERTIFICATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORG_PROCESS' and
opinion.audit_result_CODE <> 'EFFECTIVE')
and opinion.PK2_VALUE in (select PROC_CERT_ID from AMW_FIN_PROC_CERT_RELAN where FIN_STMT_CERT_ID = p_fin_cert_id)
and (opinion.PK3_VALUE = orgprocess.ORGANIZATION_ID
and opinion.PK1_VALUE = orgprocess.PROCESS_ID)
and ( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
in
(select procRln.ORGANIZATION_ID ,procRln.CHILD_PROCESS_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs.object_type = 'PROCESS_ORG' and
AccProcs.pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
--(select distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ( (acc.natural_account_id, acc.account_group_id) in
-- (select finkeyacc.NATURAL_ACCOUNT_ID,finkeyacc.account_group_id from AMW_FIN_ITEMS_KEY_ACC finkeyacc
--where
-- finkeyacc.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and finkeyacc.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- and finkeyacc.FINANCIAL_ITEM_ID IN (select
-- distinct(stmtitem.FINANCIAL_ITEM_ID )
-- from AMW_FIN_STMNT_ITEMS_B stmtitem
-- START WITH (stmtitem.FINANCIAL_ITEM_ID = P_FINANCIAL_ITEM_ID
-- and stmtitem.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and stmtitem.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- )
-- CONNECT BY PRIOR
-- stmtitem.FINANCIAL_ITEM_ID = stmtitem.PARENT_FINANCIAL_ITEM_ID
--and PRIOR stmtitem.STATEMENT_GROUP_ID = stmtitem.STATEMENT_GROUP_ID
--and PRIOR stmtitem.FINANCIAL_STATEMENT_ID = stmtitem.FINANCIAL_STATEMENT_ID)
--)
--)
-- CONNECT BY PRIOR
--acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id) -- end of acc
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
)
)
)
--) --?
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
select count(1) into P_org_with_ineffective_ctrls from (
select distinct orgprocess.ORGANIZATION_ID
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess
WHERE
(opinion.AUTHORED_DATE in
(Select
MAX(opinion2.AUTHORED_DATE)
from
AMW_OPINIONS_V opinion2
where
opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
opinion2.PK1_VALUE = opinion.PK1_VALUE
--AND
--opinion2.AUTHORED_DATE >= p_start_date
--and
--opinion2.AUTHORED_DATE <= p_end_date
) AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'EVALUATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORGANIZATION' and
opinion.audit_result_CODE <> 'EFFECTIVE'
)
and opinion.PK1_VALUE = orgprocess.ORGANIZATION_ID
and orgprocess.ORGANIZATION_ID
in
-- ?
(select procRln.ORGANIZATION_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs.object_type = 'PROCESS_ORG' and
AccProcs.pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
--(select distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ( (acc.natural_account_id, acc.account_group_id) in
-- (select finkeyacc.NATURAL_ACCOUNT_ID,finkeyacc.account_group_id from AMW_FIN_ITEMS_KEY_ACC finkeyacc
--where
-- finkeyacc.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and finkeyacc.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- and finkeyacc.FINANCIAL_ITEM_ID IN (select
-- distinct(stmtitem.FINANCIAL_ITEM_ID )
-- from AMW_FIN_STMNT_ITEMS_B stmtitem
-- START WITH (stmtitem.FINANCIAL_ITEM_ID = P_FINANCIAL_ITEM_ID
-- and stmtitem.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and stmtitem.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- )
-- CONNECT BY PRIOR
-- stmtitem.FINANCIAL_ITEM_ID = stmtitem.PARENT_FINANCIAL_ITEM_ID
--and PRIOR stmtitem.STATEMENT_GROUP_ID = stmtitem.STATEMENT_GROUP_ID
--and PRIOR stmtitem.FINANCIAL_STATEMENT_ID = stmtitem.FINANCIAL_STATEMENT_ID)
--)
--)
-- CONNECT BY PRIOR
--acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id) -- end of acc
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
)
)
)
--) --?
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
select COUNT(1)
into P_org_evaluated
from (
(select distinct procRln.ORGANIZATION_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs.object_type = 'PROCESS_ORG' and
AccProcs.pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
--(select distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ( (acc.natural_account_id, acc.account_group_id) in
-- (select finkeyacc.NATURAL_ACCOUNT_ID,finkeyacc.account_group_id from AMW_FIN_ITEMS_KEY_ACC finkeyacc
--where
-- finkeyacc.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and finkeyacc.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- and finkeyacc.FINANCIAL_ITEM_ID IN (select
-- distinct(stmtitem.FINANCIAL_ITEM_ID )
-- from AMW_FIN_STMNT_ITEMS_B stmtitem
-- START WITH (stmtitem.FINANCIAL_ITEM_ID = P_FINANCIAL_ITEM_ID
-- and stmtitem.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and stmtitem.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- )
-- CONNECT BY PRIOR
-- stmtitem.FINANCIAL_ITEM_ID = stmtitem.PARENT_FINANCIAL_ITEM_ID
--and PRIOR stmtitem.STATEMENT_GROUP_ID = stmtitem.STATEMENT_GROUP_ID
--and PRIOR stmtitem.FINANCIAL_STATEMENT_ID = stmtitem.FINANCIAL_STATEMENT_ID)
--)
--)
-- CONNECT BY PRIOR
--acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id) -- end of acc
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
)
)
)
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
select count(1) into P_org_evaluated from (
select distinct orgprocess.ORGANIZATION_ID
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess
WHERE
(opinion.AUTHORED_DATE in
(Select
MAX(opinion2.AUTHORED_DATE)
from
AMW_OPINIONS_V opinion2
where
opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
opinion2.PK1_VALUE = opinion.PK1_VALUE AND
opinion2.AUTHORED_DATE >= p_start_date
and
opinion2.AUTHORED_DATE <= p_end_date
) AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'EVALUATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORGANIZATION'
)
and opinion.PK1_VALUE = orgprocess.ORGANIZATION_ID
and orgprocess.ORGANIZATION_ID
in
(select procRln.ORGANIZATION_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs.object_type = 'PROCESS_ORG' and
AccProcs.pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
--(select distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ( (acc.natural_account_id, acc.account_group_id) in
-- (select finkeyacc.NATURAL_ACCOUNT_ID,finkeyacc.account_group_id from AMW_FIN_ITEMS_KEY_ACC finkeyacc
--where
-- finkeyacc.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and finkeyacc.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- and finkeyacc.FINANCIAL_ITEM_ID IN (select
-- distinct(stmtitem.FINANCIAL_ITEM_ID )
-- from AMW_FIN_STMNT_ITEMS_B stmtitem
-- START WITH (stmtitem.FINANCIAL_ITEM_ID = P_FINANCIAL_ITEM_ID
-- and stmtitem.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and stmtitem.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- )
-- CONNECT BY PRIOR
-- stmtitem.FINANCIAL_ITEM_ID = stmtitem.PARENT_FINANCIAL_ITEM_ID
--and PRIOR stmtitem.STATEMENT_GROUP_ID = stmtitem.STATEMENT_GROUP_ID
--and PRIOR stmtitem.FINANCIAL_STATEMENT_ID = stmtitem.FINANCIAL_STATEMENT_ID)
--)
--)
-- CONNECT BY PRIOR
--acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id) -- end of acc
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
)
)
)
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
select count(1) into P_org_certified from (
select
distinct orgprocess.ORGANIZATION_ID
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess
WHERE
(opinion.AUTHORED_DATE in
(Select
MAX(opinion2.AUTHORED_DATE)
from
AMW_OPINIONS_V opinion2
where
opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
opinion2.PK1_VALUE = opinion.PK1_VALUE AND
opinion2.AUTHORED_DATE >= p_start_date
and
opinion2.AUTHORED_DATE <= p_end_date
) AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'CERTIFICATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORGANIZATION'
)
and opinion.PK1_VALUE = orgprocess.ORGANIZATION_ID
and orgprocess.ORGANIZATION_ID
in
(select procRln.ORGANIZATION_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs.object_type = 'PROCESS_ORG' and
AccProcs.pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
--(select distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ( (acc.natural_account_id, acc.account_group_id) in
-- (select finkeyacc.NATURAL_ACCOUNT_ID,finkeyacc.account_group_id from AMW_FIN_ITEMS_KEY_ACC finkeyacc
--where
-- finkeyacc.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and finkeyacc.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- and finkeyacc.FINANCIAL_ITEM_ID IN (select
-- distinct(stmtitem.FINANCIAL_ITEM_ID )
-- from AMW_FIN_STMNT_ITEMS_B stmtitem
-- START WITH (stmtitem.FINANCIAL_ITEM_ID = P_FINANCIAL_ITEM_ID
-- and stmtitem.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and stmtitem.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- )
-- CONNECT BY PRIOR
-- stmtitem.FINANCIAL_ITEM_ID = stmtitem.PARENT_FINANCIAL_ITEM_ID
--and PRIOR stmtitem.STATEMENT_GROUP_ID = stmtitem.STATEMENT_GROUP_ID
--and PRIOR stmtitem.FINANCIAL_STATEMENT_ID = stmtitem.FINANCIAL_STATEMENT_ID)
--)
--)
-- CONNECT BY PRIOR
--acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id) -- end of acc
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
)
)
)
--) --?
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
select count(1) into P_proc_with_ineffective_ctrls from (
Select distinct orgprocess.PROCESS_ID, orgprocess.ORGANIZATION_ID
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess
WHERE
(opinion.AUTHORED_DATE in
(Select
MAX(opinion2.AUTHORED_DATE)
from
AMW_OPINIONS_V opinion2
where
opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID and
opinion2.PK1_VALUE = opinion.PK1_VALUE and
opinion2.PK3_VALUE = opinion.PK3_VALUE
--AND
--opinion2.AUTHORED_DATE >= p_start_date
--and
-- opinion2.AUTHORED_DATE <= p_end_date
) AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'EVALUATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORG_PROCESS'
and opinion.audit_result_CODE <> 'EFFECTIVE'
)
and (opinion.PK3_VALUE = orgprocess.ORGANIZATION_ID
and opinion.PK1_VALUE = orgprocess.PROCESS_ID)
and ( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
IN
(select procRln.ORGANIZATION_ID ,procRln.CHILD_PROCESS_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs.object_type = 'PROCESS_ORG' and
AccProcs.pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
--(select distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ( (acc.natural_account_id, acc.account_group_id) in
-- (select finkeyacc.NATURAL_ACCOUNT_ID,finkeyacc.account_group_id from AMW_FIN_ITEMS_KEY_ACC finkeyacc
--where
-- finkeyacc.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and finkeyacc.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- and finkeyacc.FINANCIAL_ITEM_ID IN (select
-- distinct(stmtitem.FINANCIAL_ITEM_ID )
-- from AMW_FIN_STMNT_ITEMS_B stmtitem
-- START WITH (stmtitem.FINANCIAL_ITEM_ID = P_FINANCIAL_ITEM_ID
-- and stmtitem.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and stmtitem.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- )
-- CONNECT BY PRIOR
-- stmtitem.FINANCIAL_ITEM_ID = stmtitem.PARENT_FINANCIAL_ITEM_ID
--and PRIOR stmtitem.STATEMENT_GROUP_ID = stmtitem.STATEMENT_GROUP_ID
--and PRIOR stmtitem.FINANCIAL_STATEMENT_ID = stmtitem.FINANCIAL_STATEMENT_ID)
--)
--)
-- CONNECT BY PRIOR
--acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id) -- end of acc
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
)
)
)
--) --?
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
select count(1) into p_ineffective_controls from(
select distinct ctrlassoc.control_id, orgprocess.organization_id
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess,
amw_risk_associations riskassoc,
amw_control_associations ctrlassoc
WHERE
(opinion.AUTHORED_DATE in
(Select
MAX(opinion2.AUTHORED_DATE)
from
AMW_OPINIONS_V opinion2
where
opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
opinion2.PK1_VALUE = opinion.PK1_VALUE and
opinion2.PK3_VALUE = opinion.PK3_VALUE
--- Commented chekcing whether the opinion date falls within the period as
--- per discussion with Say and Bastin on 2/9/04
--AND
-- opinion2.AUTHORED_DATE >= p_start_date and
-- opinion2.AUTHORED_DATE <= p_end_date
) AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'EVALUATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORG_CONTROL' and
-- AccProcs .natural_account_id = P_NATURAL_ACCOUNT_ID and -- from procedure parameter
opinion.pk1_value = ctrlassoc.control_id
and opinion.pk3_value = orgprocess.organization_id --??
and orgprocess.process_organization_id = riskassoc.pk1
and riskassoc.object_type = 'PROCESS_ORG'
and riskassoc.risk_association_id = ctrlassoc.pk1
and ctrlassoc.object_type = 'RISK_ORG'
and opinion.audit_result_CODE <> 'EFFECTIVE')
and opinion.pk3_value = orgprocess.ORGANIZATION_ID
and (opinion.pk3_value = orgprocess.ORGANIZATION_ID)
--and opinion.pk4_value = orgprocess.PROCESS_ID )
and
( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
in
(select procRln.ORGANIZATION_ID ,procRln.CHILD_PROCESS_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs.object_type = 'PROCESS_ORG' and
AccProcs.pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
--(select distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ( (acc.natural_account_id, acc.account_group_id) in
--- (select finkeyacc.NATURAL_ACCOUNT_ID,finkeyacc.account_group_id from AMW_FIN_ITEMS_KEY_ACC finkeyacc
--where
-- finkeyacc.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and finkeyacc.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- and finkeyacc.FINANCIAL_ITEM_ID IN (select
-- distinct(stmtitem.FINANCIAL_ITEM_ID )
-- from AMW_FIN_STMNT_ITEMS_B stmtitem
-- START WITH (stmtitem.FINANCIAL_ITEM_ID = P_FINANCIAL_ITEM_ID
-- and stmtitem.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and stmtitem.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- )
-- CONNECT BY PRIOR
-- stmtitem.FINANCIAL_ITEM_ID = stmtitem.PARENT_FINANCIAL_ITEM_ID
--and PRIOR stmtitem.STATEMENT_GROUP_ID = stmtitem.STATEMENT_GROUP_ID
--and PRIOR stmtitem.FINANCIAL_STATEMENT_ID = stmtitem.FINANCIAL_STATEMENT_ID)
--)
--)
-- CONNECT BY PRIOR
--acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id) -- end of acc
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
)
)
)
--) --?
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID));
select count(1) into p_unmitigated_risks from (
select distinct riskassoc.risk_id ,orgprocess.organization_id, orgprocess.Process_ID
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess,
amw_risk_associations riskassoc
WHERE
(opinion.AUTHORED_DATE in
(Select
MAX(opinion2.AUTHORED_DATE)
from
AMW_OPINIONS_V opinion2
where
opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
opinion2.PK1_VALUE = opinion.PK1_VALUE and
opinion2.PK3_VALUE = opinion.PK3_VALUE and
opinion2.PK4_VALUE = opinion.PK4_VALUE
--- Commented chekcing whether the opinion date falls within the period as
--- per discussion with Say and Bastin on 2/9/04
-- and
-- opinion2.AUTHORED_DATE >= p_start_date and
-- opinion2.AUTHORED_DATE <= p_end_date
) AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'EVALUATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORG_PROCESS_RISK' and
-- AccProcs .natural_account_id = P_NATURAL_ACCOUNT_ID and -- from procedure parameter
opinion.pk1_value = riskassoc.risk_id
--and opinion.pk3_value = orgprocess.organization_id
-- and opinion.pk4_value = orgprocess.Process_ID
and orgprocess.process_organization_id = riskassoc.pk1
and riskassoc.object_type = 'PROCESS_ORG'
and opinion.audit_result_CODE <> 'EFFECTIVE'
)
-- and (opinion.pk3_value , opinion.pk4_value )
and (opinion.pk3_value = orgprocess.ORGANIZATION_ID and opinion.pk4_value = orgprocess.PROCESS_ID ) and
( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
in
(select procRln.ORGANIZATION_ID ,procRln.CHILD_PROCESS_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs.object_type = 'PROCESS_ORG' and
AccProcs.pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
--(select distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ( (acc.natural_account_id, acc.account_group_id) in
-- (select finkeyacc.NATURAL_ACCOUNT_ID,finkeyacc.account_group_id from AMW_FIN_ITEMS_KEY_ACC finkeyacc
--where
-- finkeyacc.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and finkeyacc.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- and finkeyacc.FINANCIAL_ITEM_ID IN (select
-- distinct(stmtitem.FINANCIAL_ITEM_ID )
-- from AMW_FIN_STMNT_ITEMS_B stmtitem
-- START WITH (stmtitem.FINANCIAL_ITEM_ID = P_FINANCIAL_ITEM_ID
-- and stmtitem.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and stmtitem.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- )
-- CONNECT BY PRIOR
-- stmtitem.FINANCIAL_ITEM_ID = stmtitem.PARENT_FINANCIAL_ITEM_ID
--and PRIOR stmtitem.STATEMENT_GROUP_ID = stmtitem.STATEMENT_GROUP_ID
--and PRIOR stmtitem.FINANCIAL_STATEMENT_ID = stmtitem.FINANCIAL_STATEMENT_ID)
--)
--)
-- CONNECT BY PRIOR
--acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id) -- end of acc
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
)
)
)
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
select count(1)
into p_risks_verified
from (
select distinct riskassoc.risk_id ,orgprocess.organization_id, orgprocess.Process_ID
FROM
amw_process_org_basicinfo_v orgprocess,
amw_risk_associations riskassoc
WHERE
orgprocess.process_organization_id = riskassoc.pk1
and riskassoc.object_type = 'PROCESS_ORG'
and (orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
in
(select procRln.ORGANIZATION_ID ,procRln.CHILD_PROCESS_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs.object_type = 'PROCESS_ORG' and
AccProcs.pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
--(select distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ( (acc.natural_account_id, acc.account_group_id) in
-- (select finkeyacc.NATURAL_ACCOUNT_ID,finkeyacc.account_group_id from AMW_FIN_ITEMS_KEY_ACC finkeyacc
--where
-- finkeyacc.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and finkeyacc.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- and finkeyacc.FINANCIAL_ITEM_ID IN (select
-- distinct(stmtitem.FINANCIAL_ITEM_ID )
-- from AMW_FIN_STMNT_ITEMS_B stmtitem
-- START WITH (stmtitem.FINANCIAL_ITEM_ID = P_FINANCIAL_ITEM_ID
-- and stmtitem.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and stmtitem.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- )
-- CONNECT BY PRIOR
-- stmtitem.FINANCIAL_ITEM_ID = stmtitem.PARENT_FINANCIAL_ITEM_ID
--and PRIOR stmtitem.STATEMENT_GROUP_ID = stmtitem.STATEMENT_GROUP_ID
--and PRIOR stmtitem.FINANCIAL_STATEMENT_ID = stmtitem.FINANCIAL_STATEMENT_ID)
--)
--)
-- CONNECT BY PRIOR
--acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id) -- end of acc
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
)
)
)
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
select count(1) into p_risks_verified from (
select distinct riskassoc.risk_id ,orgprocess.organization_id, orgprocess.Process_ID
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess,
amw_risk_associations riskassoc
WHERE
(opinion.AUTHORED_DATE in
(Select
MAX(opinion2.AUTHORED_DATE)
from
AMW_OPINIONS_V opinion2
where
opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
opinion2.PK1_VALUE = opinion.PK1_VALUE and
opinion2.PK3_VALUE = opinion.PK3_VALUE and
opinion2.PK4_VALUE = opinion.PK4_VALUE
and
opinion2.AUTHORED_DATE >= p_start_date and
opinion2.AUTHORED_DATE <= p_end_date
) AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'EVALUATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORG_PROCESS_RISK' and
-- AccProcs .natural_account_id = P_NATURAL_ACCOUNT_ID and -- from procedure parameter
opinion.pk1_value = riskassoc.risk_id
--and opinion.pk3_value = orgprocess.organization_id
-- and opinion.pk4_value = orgprocess.Process_ID
and orgprocess.process_organization_id = riskassoc.pk1
and riskassoc.object_type = 'PROCESS_ORG'
)
-- and (opinion.pk3_value , opinion.pk4_value )
and (opinion.pk3_value = orgprocess.ORGANIZATION_ID and opinion.pk4_value = orgprocess.PROCESS_ID ) and
( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
in
(select procRln.ORGANIZATION_ID ,procRln.CHILD_PROCESS_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs.object_type = 'PROCESS_ORG' and
AccProcs.pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
--(select distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ( (acc.natural_account_id, acc.account_group_id) in
-- (select finkeyacc.NATURAL_ACCOUNT_ID,finkeyacc.account_group_id from AMW_FIN_ITEMS_KEY_ACC finkeyacc
--where
-- finkeyacc.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and finkeyacc.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- and finkeyacc.FINANCIAL_ITEM_ID IN (select
-- distinct(stmtitem.FINANCIAL_ITEM_ID )
-- from AMW_FIN_STMNT_ITEMS_B stmtitem
-- START WITH (stmtitem.FINANCIAL_ITEM_ID = P_FINANCIAL_ITEM_ID
-- and stmtitem.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and stmtitem.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- )
-- CONNECT BY PRIOR
-- stmtitem.FINANCIAL_ITEM_ID = stmtitem.PARENT_FINANCIAL_ITEM_ID
--and PRIOR stmtitem.STATEMENT_GROUP_ID = stmtitem.STATEMENT_GROUP_ID
--and PRIOR stmtitem.FINANCIAL_STATEMENT_ID = stmtitem.FINANCIAL_STATEMENT_ID)
--)
--)
-- CONNECT BY PRIOR
--acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id) -- end of acc
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
)
)
)
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);
select count(1)
into p_controls_verified
from (
select distinct ctrlassoc.control_id, orgprocess.organization_id
FROM
amw_process_org_basicinfo_v orgprocess,
amw_risk_associations riskassoc,
amw_control_associations ctrlassoc
WHERE
orgprocess.process_organization_id = riskassoc.pk1
and riskassoc.object_type = 'PROCESS_ORG'
and riskassoc.risk_association_id = ctrlassoc.pk1
and ctrlassoc.object_type = 'RISK_ORG'
and
( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
in
(select procRln.ORGANIZATION_ID ,procRln.CHILD_PROCESS_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs.object_type = 'PROCESS_ORG' and
AccProcs.pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
--(select distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ( (acc.natural_account_id, acc.account_group_id) in
-- (select finkeyacc.NATURAL_ACCOUNT_ID,finkeyacc.account_group_id from AMW_FIN_ITEMS_KEY_ACC finkeyacc
--where
-- finkeyacc.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and finkeyacc.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- and finkeyacc.FINANCIAL_ITEM_ID IN (select
-- distinct(stmtitem.FINANCIAL_ITEM_ID )
-- from AMW_FIN_STMNT_ITEMS_B stmtitem
-- START WITH (stmtitem.FINANCIAL_ITEM_ID = P_FINANCIAL_ITEM_ID
-- and stmtitem.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and stmtitem.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- )
-- CONNECT BY PRIOR
-- stmtitem.FINANCIAL_ITEM_ID = stmtitem.PARENT_FINANCIAL_ITEM_ID
--and PRIOR stmtitem.STATEMENT_GROUP_ID = stmtitem.STATEMENT_GROUP_ID
--and PRIOR stmtitem.FINANCIAL_STATEMENT_ID = stmtitem.FINANCIAL_STATEMENT_ID)
--)
--)
-- CONNECT BY PRIOR
--acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id) -- end of acc
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
)
)
)
--) --?
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID));
select count(1) into p_controls_verified from (
select distinct ctrlassoc.control_id, orgprocess.organization_id
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess,
amw_risk_associations riskassoc,
amw_control_associations ctrlassoc
WHERE
(opinion.AUTHORED_DATE in
(Select
MAX(opinion2.AUTHORED_DATE)
from
AMW_OPINIONS_V opinion2
where
opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
opinion2.PK1_VALUE = opinion.PK1_VALUE and
opinion2.PK3_VALUE = opinion.PK3_VALUE AND
opinion2.AUTHORED_DATE >= p_start_date and
opinion2.AUTHORED_DATE <= p_end_date
) AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'EVALUATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORG_CONTROL' and
-- AccProcs .natural_account_id = P_NATURAL_ACCOUNT_ID and -- from procedure parameter
opinion.pk1_value = ctrlassoc.control_id
and opinion.pk3_value = orgprocess.organization_id --??
and orgprocess.process_organization_id = riskassoc.pk1
and riskassoc.object_type = 'PROCESS_ORG'
and riskassoc.risk_association_id = ctrlassoc.pk1
and ctrlassoc.object_type = 'RISK_ORG'
and opinion.audit_result_CODE <> 'EFFECTIVE')
and opinion.pk3_value = orgprocess.ORGANIZATION_ID
and (opinion.pk3_value = orgprocess.ORGANIZATION_ID)
--and opinion.pk4_value = orgprocess.PROCESS_ID )
and
( orgprocess.ORGANIZATION_ID , orgprocess.PROCESS_ID )
in
(select procRln.ORGANIZATION_ID ,procRln.CHILD_PROCESS_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs.object_type = 'PROCESS_ORG' and
AccProcs.pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
--(select distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ( (acc.natural_account_id, acc.account_group_id) in
-- (select finkeyacc.NATURAL_ACCOUNT_ID,finkeyacc.account_group_id from AMW_FIN_ITEMS_KEY_ACC finkeyacc
--where
-- finkeyacc.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and finkeyacc.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- and finkeyacc.FINANCIAL_ITEM_ID IN (select
-- distinct(stmtitem.FINANCIAL_ITEM_ID )
-- from AMW_FIN_STMNT_ITEMS_B stmtitem
-- START WITH (stmtitem.FINANCIAL_ITEM_ID = P_FINANCIAL_ITEM_ID
-- and stmtitem.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and stmtitem.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- )
-- CONNECT BY PRIOR
-- stmtitem.FINANCIAL_ITEM_ID = stmtitem.PARENT_FINANCIAL_ITEM_ID
--and PRIOR stmtitem.STATEMENT_GROUP_ID = stmtitem.STATEMENT_GROUP_ID
--and PRIOR stmtitem.FINANCIAL_STATEMENT_ID = stmtitem.FINANCIAL_STATEMENT_ID)
--)
--)
-- CONNECT BY PRIOR
--acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id) -- end of acc
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
)
)
)
--) --?
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID));
select count(1) into P_org_cert_with_issues from (
select distinct orgprocess.ORGANIZATION_ID
FROM
AMW_OPINIONS_V opinion,
AMW_OPINION_TYPES_B opiniontype,
FND_OBJECTS fndobject,
AMW_OBJECT_OPINION_TYPES objectopiniontype,
amw_process_org_basicinfo_v orgprocess
WHERE
(opinion.AUTHORED_DATE in
(Select
MAX(opinion2.AUTHORED_DATE)
from
AMW_OPINIONS_V opinion2
where
opinion.OBJECT_OPINION_TYPE_ID = opinion2.OBJECT_OPINION_TYPE_ID AND
opinion2.PK1_VALUE = opinion.PK1_VALUE AND
opinion2.AUTHORED_DATE >= p_start_date
and
opinion2.AUTHORED_DATE <= p_end_date
) AND
opinion.OBJECT_OPINION_TYPE_ID = objectopiniontype.OBJECT_OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_ID = objectopiniontype.OPINION_TYPE_ID AND
opiniontype.OPINION_TYPE_CODE = 'CERTIFICATION' AND
fndobject.OBJECT_ID = objectopiniontype.OBJECT_ID AND
fndobject.obj_name = 'AMW_ORGANIZATION'
and opinion.audit_result_CODE <> 'EFFECTIVE'
)
and opinion.PK1_VALUE = orgprocess.ORGANIZATION_ID
and orgprocess.ORGANIZATION_ID
in
(select procRln.ORGANIZATION_ID from Amw_Process_Org_Relations procRln
START WITH ((procRln.CHILD_PROCESS_ID , procRln.ORGANIZATION_ID) in
( select orgprocess2.PROCESS_ID , orgprocess2.ORGANIZATION_ID from amw_process_org_basicinfo_v orgprocess2,
Amw_acct_associations AccProcs where AccProcs.object_type = 'PROCESS_ORG' and
AccProcs.pk1= orgprocess2.PROCESS_ORGANIZATION_ID
and AccProcs.natural_account_id in
--(select distinct(acc.natural_account_id)
-- from AMW_FIN_KEY_ACCOUNTS_B acc
-- START WITH ( (acc.natural_account_id, acc.account_group_id) in
-- (select finkeyacc.NATURAL_ACCOUNT_ID,finkeyacc.account_group_id from AMW_FIN_ITEMS_KEY_ACC finkeyacc
--where
-- finkeyacc.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and finkeyacc.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- and finkeyacc.FINANCIAL_ITEM_ID IN (select
-- distinct(stmtitem.FINANCIAL_ITEM_ID )
-- from AMW_FIN_STMNT_ITEMS_B stmtitem
-- START WITH (stmtitem.FINANCIAL_ITEM_ID = P_FINANCIAL_ITEM_ID
-- and stmtitem.STATEMENT_GROUP_ID = P_STATEMENT_GROUP_ID
-- and stmtitem.FINANCIAL_STATEMENT_ID = P_FINANCIAL_STATEMENT_ID
-- )
-- CONNECT BY PRIOR
-- stmtitem.FINANCIAL_ITEM_ID = stmtitem.PARENT_FINANCIAL_ITEM_ID
--and PRIOR stmtitem.STATEMENT_GROUP_ID = stmtitem.STATEMENT_GROUP_ID
--and PRIOR stmtitem.FINANCIAL_STATEMENT_ID = stmtitem.FINANCIAL_STATEMENT_ID)
--)
--)
-- CONNECT BY PRIOR
--acc.natural_account_id = acc.parent_natural_account_id
-- and PRIOR
--acc.account_group_id = acc.account_group_id) -- end of acc
( select distinct acc.child_natural_account_id
from amw_fin_key_acct_flat acc
where ( acc.parent_natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
union
select distinct acc.natural_account_id
from amw_fin_key_accounts_b acc
where ( acc.natural_account_id, acc.account_group_id ) in
( select finkeyacc.natural_account_id, finkeyacc.account_group_id
from amw_fin_items_key_acc finkeyacc
where finkeyacc.statement_group_id = p_statement_group_id
and finkeyacc.financial_statement_id = p_financial_statement_id
and finkeyacc.financial_item_id in
( select distinct item.child_financial_item_id
from amw_fin_item_flat item
where item.parent_financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
union
select distinct item.financial_item_id
from amw_fin_stmnt_items_b item
where item.financial_item_id = p_financial_item_id
and item.statement_group_id = p_statement_group_id
and item.financial_statement_id = p_financial_statement_id
)
)
)
)
)
--) --?
CONNECT BY PRIOR
procRln.ORGANIZATION_ID = procRln.ORGANIZATION_ID
and PRIOR procRln.CHILD_PROCESS_ID = procRln.PARENT_PROCESS_ID)
);