[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
count(1) numIneffCtrls,
COMPONENT_CODE
from
(select
distinct
ctrl.ORGANIZATION_ID, ctrl.control_id, comp.COMPONENT_CODE
from
amw_fin_item_acc_ctrl ctrl,
amw_opinions_log_v opinion,
amw_assessment_components comp,
amw_control_associations ctrlAsso
where
ctrl.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID and
ctrl.OBJECT_TYPE = 'FINANCIAL ITEM' and
ctrl.FINANCIAL_ITEM_ID= P_FINANCIAL_ITEM_ID and
-- ctrl.ACCOUNT_GROUP_ID is null and
-- ctrl.NATURAL_ACCOUNT_ID is null and
--opinion.OPINION_LOG_ID = ctrl.OPINION_LOG_ID and
opinion.pk1_value = ctrl.control_id and
opinion.pk3_value = ctrl.ORGANIZATION_ID and
opinion.audit_result_CODE <> 'EFFECTIVE' and
opinion.OPINION_TYPE_CODE = 'EVALUATION' AND
opinion.OBJECT_NAME = 'AMW_ORG_CONTROL' and
ctrl.CONTROL_REV_ID =comp.OBJECT_ID and
comp.OBJECT_TYPE ='CONTROL' and
ctrlAsso.OBJECT_TYPE='RISK_FINCERT' and
opinion.OPINION_LOG_ID = ctrlAsso.PK5 and
ctrlAsso.PK1 = ctrl.FIN_CERTIFICATION_ID and
ctrlAsso.PK2 = ctrl.ORGANIZATION_ID and
ctrlAsso.CONTROL_ID = ctrl.control_id)
group by COMPONENT_CODE;
select
count(1) numOfCtrls,
comp.COMPONENT_CODE
from
amw.amw_fin_item_acc_ctrl ctrl,
amw_assessment_components comp
where
ctrl.FIN_CERTIFICATION_ID=P_CERTIFICATION_ID and
ctrl.OBJECT_TYPE = 'FINANCIAL ITEM' and
ctrl.FINANCIAL_ITEM_ID= P_FINANCIAL_ITEM_ID and
--ctrl.ACCOUNT_GROUP_ID is null and
--ctrl.NATURAL_ACCOUNT_ID is null and
ctrl.CONTROL_REV_ID =comp.OBJECT_ID and
comp.OBJECT_TYPE ='CONTROL'
group by COMPONENT_CODE;
select
count(1) numOfEvaluatedCtrls,
COMPONENT_CODE
from
(select
distinct
ctrl.ORGANIZATION_ID, ctrl.control_id, comp.COMPONENT_CODE
from
amw_fin_item_acc_ctrl ctrl,
amw_opinions_v opinion,
amw_assessment_components comp,
amw_control_associations ctrlAsso
where
ctrl.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID
and
ctrl.OBJECT_TYPE = 'FINANCIAL ITEM' and
ctrl.FINANCIAL_ITEM_ID= P_FINANCIAL_ITEM_ID
and
opinion.pk1_value = ctrl.control_id and
opinion.pk3_value = ctrl.ORGANIZATION_ID and
opinion.OPINION_TYPE_CODE = 'EVALUATION' AND
opinion.OBJECT_NAME = 'AMW_ORG_CONTROL' and
ctrl.CONTROL_REV_ID =comp.OBJECT_ID and
comp.OBJECT_TYPE ='CONTROL' and
ctrlAsso.OBJECT_TYPE='RISK_FINCERT' and
ctrlAsso.PK1 = ctrl.FIN_CERTIFICATION_ID and
ctrlAsso.PK2 = ctrl.ORGANIZATION_ID and
ctrlAsso.CONTROL_ID = ctrl.control_id)
group by COMPONENT_CODE;
select
count(1) numIneffCtrls,
COMPONENT_CODE from
( select
distinct
ctrl.ORGANIZATION_ID, ctrl.control_id, comp.COMPONENT_CODE
from
amw_fin_item_acc_ctrl ctrl,
amw_opinions_log_v opinion,
amw_assessment_components comp,
amw_control_associations ctrlAsso
where
ctrl.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID and
ctrl.OBJECT_TYPE = 'ACCOUNT' and
--ctrl.FINANCIAL_ITEM_ID= P_FINANCIAL_ITEM_ID and
ctrl.ACCOUNT_GROUP_ID = P_ACCOUNT_GROUP_ID
and NATURAL_ACCOUNT_ID = P_ACCOUNT_ID and
--opinion.OPINION_LOG_ID = ctrl.OPINION_LOG_ID and
opinion.pk1_value = ctrl.control_id and
opinion.pk3_value = ctrl.ORGANIZATION_ID and
opinion.audit_result_CODE <> 'EFFECTIVE' and
opinion.OPINION_TYPE_CODE = 'EVALUATION' AND
opinion.OBJECT_NAME = 'AMW_ORG_CONTROL' and
ctrl.CONTROL_REV_ID =comp.OBJECT_ID and
comp.OBJECT_TYPE ='CONTROL' and
ctrlAsso.OBJECT_TYPE='RISK_FINCERT' and
opinion.OPINION_LOG_ID = ctrlAsso.PK5 and
ctrlAsso.PK1 = ctrl.FIN_CERTIFICATION_ID and
ctrlAsso.PK2 = ctrl.ORGANIZATION_ID and
ctrlAsso.CONTROL_ID = ctrl.control_id)
group by COMPONENT_CODE;
SELECT COUNT(1) NUMINEFFCTRLS, COMPONENT_CODE
FROM
(SELECT DISTINCT CTRL.ORGANIZATION_ID, CTRL.CONTROL_ID, COMP.COMPONENT_CODE
FROM AMW_FIN_ITEM_ACC_CTRL CTRL,
AMW_ASSESSMENT_COMPONENTS COMP
WHERE
CTRL.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID
AND CTRL.OBJECT_TYPE = 'ACCOUNT'
AND CTRL.ACCOUNT_GROUP_ID = P_ACCOUNT_GROUP_ID
AND NATURAL_ACCOUNT_ID = P_ACCOUNT_ID
AND CTRL.CONTROL_REV_ID =COMP.OBJECT_ID
AND COMP.OBJECT_TYPE ='CONTROL'
AND EXISTS
(SELECT 1 FROM AMW_OPINIONS_LOG_V OPINION
WHERE OPINION.PK1_VALUE = CTRL.CONTROL_ID
AND OPINION.PK3_VALUE = CTRL.ORGANIZATION_ID
AND OPINION.AUDIT_RESULT_CODE <> 'EFFECTIVE'
AND OPINION.OPINION_TYPE_CODE = 'EVALUATION'
AND OPINION.OBJECT_NAME = 'AMW_ORG_CONTROL'
AND EXISTS
(SELECT 1 FROM AMW_CONTROL_ASSOCIATIONS CTRLASSO
WHERE
CTRLASSO.OBJECT_TYPE='RISK_FINCERT'
AND OPINION.OPINION_LOG_ID = CTRLASSO.PK5
AND CTRLASSO.PK1 = P_CERTIFICATION_ID
AND CTRLASSO.PK2 = OPINION.PK3_VALUE
AND CTRLASSO.CONTROL_ID = OPINION.PK1_VALUE
))) GROUP BY COMPONENT_CODE;
select
count(1) numOfCtrls,
comp.COMPONENT_CODE
from
amw.amw_fin_item_acc_ctrl ctrl,
amw_assessment_components comp
where
ctrl.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID and
ctrl.OBJECT_TYPE = 'ACCOUNT' and
-- ctrl.FINANCIAL_ITEM_ID= P_FINANCIAL_ITEM_ID and
ACCOUNT_GROUP_ID = P_ACCOUNT_GROUP_ID and
NATURAL_ACCOUNT_ID =P_ACCOUNT_ID and
ctrl.CONTROL_REV_ID =comp.OBJECT_ID and
comp.OBJECT_TYPE ='CONTROL'
group by COMPONENT_CODE;
select
count(1) numOfEvaluatedCtrls,
COMPONENT_CODE from
( select
distinct
ctrl.ORGANIZATION_ID, ctrl.control_id, comp.COMPONENT_CODE
from
amw_fin_item_acc_ctrl ctrl,
amw_opinions_log_v opinion,
amw_assessment_components comp,
amw_control_associations ctrlAsso
where
ctrl.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID
and
ctrl.OBJECT_TYPE = 'ACCOUNT' and
ctrl.ACCOUNT_GROUP_ID = P_ACCOUNT_GROUP_ID and
NATURAL_ACCOUNT_ID = P_ACCOUNT_ID and
opinion.pk1_value = ctrl.control_id and
opinion.pk3_value = ctrl.ORGANIZATION_ID and
opinion.OPINION_TYPE_CODE = 'EVALUATION' AND
opinion.OBJECT_NAME = 'AMW_ORG_CONTROL' and
ctrl.CONTROL_REV_ID =comp.OBJECT_ID and
comp.OBJECT_TYPE ='CONTROL' and
ctrlAsso.OBJECT_TYPE='RISK_FINCERT' and
ctrlAsso.PK1 = ctrl.FIN_CERTIFICATION_ID and
ctrlAsso.PK2 = ctrl.ORGANIZATION_ID and
ctrlAsso.CONTROL_ID = ctrl.control_id)
group by COMPONENT_CODE;
select
LOOKUP_CODE
from
amw_lookups
where lookup_type = 'AMW_ASSESSMENT_COMPONENTS';
amw_fin_coso_views_pvt.INSERT_ROW(
x_fin_certification_id => P_CERTIFICATION_ID ,
x_financial_statement_id => P_FINANCIAL_STATEMENT_ID ,
x_financial_item_id => P_FINANCIAL_ITEM_ID ,
x_account_group_id => P_ACCOUNT_GROUP_ID ,
x_natural_account_id => P_ACCOUNT_ID ,
x_object_type => P_OBJECT_TYPE ,
x_ctrl_attribute_type => m_ctrl_attribute_type ,
x_ctrl_attr_code_1 => m_component_code(1) ,
x_ineff_ctrl_attr_1 => m_ineff_control(1) ,
x_total_ctrl_attr_1 => m_total_control(1) ,
x_ctrl_attr_code_2 => m_component_code(2) ,
x_ineff_ctrl_attr_2 => m_ineff_control(2) ,
x_total_ctrl_attr_2 => m_total_control(2) ,
x_ctrl_attr_code_3 => m_component_code(3) ,
x_ineff_ctrl_attr_3 => m_ineff_control(3) ,
x_total_ctrl_attr_3 => m_total_control(3) ,
x_ctrl_attr_code_4 => m_component_code(4) ,
x_ineff_ctrl_attr_4 => m_ineff_control(4) ,
x_total_ctrl_attr_4 => m_total_control(4) ,
x_ctrl_attr_code_5 => m_component_code(5) ,
x_ineff_ctrl_attr_5 => m_ineff_control(5) ,
x_total_ctrl_attr_5 => m_total_control(5) ,
x_ctrl_attr_code_6 => m_component_code(6) ,
x_ineff_ctrl_attr_6 => m_ineff_control(6) ,
x_total_ctrl_attr_6 => m_total_control(6) ,
x_ctrl_attr_code_7 => m_component_code(7) ,
x_ineff_ctrl_attr_7 => m_ineff_control(7) ,
x_total_ctrl_attr_7 => m_total_control(7) ,
x_ctrl_attr_code_8 => m_component_code(8) ,
x_ineff_ctrl_attr_8 => m_ineff_control(8) ,
x_total_ctrl_attr_8 => m_total_control(8) ,
x_ctrl_attr_code_9 => m_component_code(9) ,
x_ineff_ctrl_attr_9 => m_ineff_control(9) ,
x_total_ctrl_attr_9 => m_total_control(9) ,
x_ctrl_attr_code_10 => m_component_code(10) ,
x_ineff_ctrl_attr_10 => m_ineff_control(10) ,
x_total_ctrl_attr_10 => m_total_control(10) ,
x_ctrl_attr_code_11 => m_component_code(11) ,
x_ineff_ctrl_attr_11 => m_ineff_control(11) ,
x_total_ctrl_attr_11 => m_total_control(11) ,
x_ctrl_attr_code_12 => m_component_code(12) ,
x_ineff_ctrl_attr_12 => m_ineff_control(12) ,
x_total_ctrl_attr_12 => m_total_control(12) ,
x_ctrl_attr_code_13 => m_component_code(13) ,
x_ineff_ctrl_attr_13 => m_ineff_control(13) ,
x_total_ctrl_attr_13 => m_total_control(13) ,
x_ctrl_attr_code_14 => m_component_code(14) ,
x_ineff_ctrl_attr_14 => m_ineff_control(14) ,
x_total_ctrl_attr_14 => m_total_control(14) ,
x_ctrl_attr_code_15 => m_component_code(15) ,
x_ineff_ctrl_attr_15 => m_ineff_control(15) ,
x_total_ctrl_attr_15 => m_total_control(15) ,
x_ctrl_attr_code_16 => m_component_code(16) ,
x_ineff_ctrl_attr_16 => m_ineff_control(16) ,
x_total_ctrl_attr_16 => m_total_control(16) ,
x_ctrl_attr_code_17 => m_component_code(17) ,
x_ineff_ctrl_attr_17 => m_ineff_control(17) ,
x_total_ctrl_attr_17 => m_total_control(17) ,
x_ctrl_attr_code_18 => m_component_code(18) ,
x_ineff_ctrl_attr_18 => m_ineff_control(18) ,
x_total_ctrl_attr_18 => m_total_control(18) ,
x_ctrl_attr_code_19 => m_component_code(19) ,
x_ineff_ctrl_attr_19 => m_ineff_control(19) ,
x_total_ctrl_attr_19 => m_total_control(19) ,
x_ctrl_attr_code_20 => m_component_code(20) ,
x_ineff_ctrl_attr_20 => m_ineff_control(20) ,
x_total_ctrl_attr_20 => m_total_control(20) ,
x_ctrl_attr_code_21 => m_component_code(21) ,
x_ineff_ctrl_attr_21 => m_ineff_control(21) ,
x_total_ctrl_attr_21 => m_total_control(21) ,
x_ctrl_attr_code_22 => m_component_code(22) ,
x_ineff_ctrl_attr_22 => m_ineff_control(22) ,
x_total_ctrl_attr_22 => m_total_control(22) ,
x_ctrl_attr_code_23 => m_component_code(23) ,
x_ineff_ctrl_attr_23 => m_ineff_control(23) ,
x_total_ctrl_attr_23 => m_total_control(23) ,
x_ctrl_attr_code_24 => m_component_code(24) ,
x_ineff_ctrl_attr_24 => m_ineff_control(24) ,
x_total_ctrl_attr_24 => m_total_control(24) ,
x_ctrl_attr_code_25 => m_component_code(25) ,
x_ineff_ctrl_attr_25 => m_ineff_control(25) ,
x_total_ctrl_attr_25 => m_total_control(25) ,
x_ctrl_attr_code_26 => m_component_code(26) ,
x_ineff_ctrl_attr_26 => m_ineff_control(26) ,
x_total_ctrl_attr_26 => m_total_control(26) ,
x_ctrl_attr_code_27 => m_component_code(27) ,
x_ineff_ctrl_attr_27 => m_ineff_control(27) ,
x_total_ctrl_attr_27 => m_total_control(27) ,
x_ctrl_attr_code_28 => m_component_code(28) ,
x_ineff_ctrl_attr_28 => m_ineff_control(28) ,
x_total_ctrl_attr_28 => m_total_control(28) ,
x_ctrl_attr_code_29 => m_component_code(29) ,
x_ineff_ctrl_attr_29 => m_ineff_control(29) ,
x_total_ctrl_attr_29 => m_total_control(29) ,
x_ctrl_attr_code_30 => m_component_code(30) ,
x_ineff_ctrl_attr_30 => m_ineff_control(30) ,
x_total_ctrl_attr_30 => m_total_control(30) ,
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 => m_object_version_number ,
x_acc_assert_flag1 => m_acc_assert_flag(1),
x_acc_assert_flag2 => m_acc_assert_flag(2),
x_acc_assert_flag3 => m_acc_assert_flag(3),
x_acc_assert_flag4 => m_acc_assert_flag(4),
x_acc_assert_flag5 => m_acc_assert_flag(5),
x_acc_assert_flag6 => m_acc_assert_flag(6),
x_acc_assert_flag7 => m_acc_assert_flag(7),
x_acc_assert_flag8 => m_acc_assert_flag(8),
x_acc_assert_flag9 => m_acc_assert_flag(9),
x_acc_assert_flag10 => m_acc_assert_flag(10),
x_acc_assert_flag11 => m_acc_assert_flag(11),
x_acc_assert_flag12 => m_acc_assert_flag(12),
x_acc_assert_flag13 => m_acc_assert_flag(13),
x_acc_assert_flag14 => m_acc_assert_flag(14),
x_acc_assert_flag15 => m_acc_assert_flag(15),
x_acc_assert_flag16 => m_acc_assert_flag(16),
x_acc_assert_flag17 => m_acc_assert_flag(17),
x_acc_assert_flag18 => m_acc_assert_flag(18),
x_acc_assert_flag19 => m_acc_assert_flag(19),
x_acc_assert_flag20 => m_acc_assert_flag(20),
x_acc_assert_flag21 => m_acc_assert_flag(21),
x_acc_assert_flag22 => m_acc_assert_flag(22),
x_acc_assert_flag23 => m_acc_assert_flag(23),
x_acc_assert_flag24 => m_acc_assert_flag(24),
x_acc_assert_flag25 => m_acc_assert_flag(25),
x_acc_assert_flag26 => m_acc_assert_flag(26),
x_acc_assert_flag27 => m_acc_assert_flag(27),
x_acc_assert_flag28 => m_acc_assert_flag(28),
x_acc_assert_flag29 => m_acc_assert_flag(29),
x_acc_assert_flag30 => m_acc_assert_flag(30),
x_eval_ctrl_attr_1 => m_evaluated_ctrls(1),
x_eval_ctrl_attr_2 => m_evaluated_ctrls(2),
x_eval_ctrl_attr_3 => m_evaluated_ctrls(3),
x_eval_ctrl_attr_4 => m_evaluated_ctrls(4),
x_eval_ctrl_attr_5 => m_evaluated_ctrls(5),
x_eval_ctrl_attr_6 => m_evaluated_ctrls(6),
x_eval_ctrl_attr_7 => m_evaluated_ctrls(7),
x_eval_ctrl_attr_8 => m_evaluated_ctrls(8),
x_eval_ctrl_attr_9 => m_evaluated_ctrls(9),
x_eval_ctrl_attr_10 => m_evaluated_ctrls(10),
x_eval_ctrl_attr_11 => m_evaluated_ctrls(11),
x_eval_ctrl_attr_12 => m_evaluated_ctrls(12),
x_eval_ctrl_attr_13 => m_evaluated_ctrls(13),
x_eval_ctrl_attr_14 => m_evaluated_ctrls(14),
x_eval_ctrl_attr_15 => m_evaluated_ctrls(15),
x_eval_ctrl_attr_16 => m_evaluated_ctrls(16),
x_eval_ctrl_attr_17 => m_evaluated_ctrls(17),
x_eval_ctrl_attr_18 => m_evaluated_ctrls(18),
x_eval_ctrl_attr_19 => m_evaluated_ctrls(19),
x_eval_ctrl_attr_20 => m_evaluated_ctrls(20),
x_eval_ctrl_attr_21 => m_evaluated_ctrls(21),
x_eval_ctrl_attr_22 => m_evaluated_ctrls(22),
x_eval_ctrl_attr_23 => m_evaluated_ctrls(23),
x_eval_ctrl_attr_24 => m_evaluated_ctrls(24),
x_eval_ctrl_attr_25 => m_evaluated_ctrls(25),
x_eval_ctrl_attr_26 => m_evaluated_ctrls(26),
x_eval_ctrl_attr_27 => m_evaluated_ctrls(27),
x_eval_ctrl_attr_28 => m_evaluated_ctrls(28),
x_eval_ctrl_attr_29 => m_evaluated_ctrls(29),
x_eval_ctrl_attr_30 => m_evaluated_ctrls(30),
x_display_flag => m_display_flag );
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));
select
count(1) numIneffCtrls,
OBJECTIVE_CODE
from
(select
distinct
ctrl.ORGANIZATION_ID, ctrl.control_id, comp.OBJECTIVE_CODE
from
amw.amw_fin_item_acc_ctrl ctrl,
amw_opinions_log_v opinion,
amw_control_objectives comp,
amw_control_associations ctrlAsso
where
ctrl.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID and
ctrl.OBJECT_TYPE = 'FINANCIAL ITEM' and
ctrl.FINANCIAL_ITEM_ID= P_FINANCIAL_ITEM_ID and
-- ctrl.ACCOUNT_GROUP_ID is null and
-- ctrl.NATURAL_ACCOUNT_ID is null and
-- opinion.OPINION_LOG_ID = ctrl.OPINION_LOG_ID and
opinion.pk1_value = ctrl.control_id and
opinion.pk3_value = ctrl.ORGANIZATION_ID and
opinion.audit_result_CODE <> 'EFFECTIVE' and
opinion.OPINION_TYPE_CODE = 'EVALUATION' AND
opinion.OBJECT_NAME = 'AMW_ORG_CONTROL' and
ctrl.CONTROL_REV_ID =comp.CONTROL_REV_ID and
ctrlAsso.OBJECT_TYPE='RISK_FINCERT' and
opinion.OPINION_LOG_ID = ctrlAsso.PK5 and
ctrlAsso.PK1 = ctrl.FIN_CERTIFICATION_ID and
ctrlAsso.PK2 = ctrl.ORGANIZATION_ID and
ctrlAsso.CONTROL_ID = ctrl.control_id)
group by OBJECTIVE_CODE;
select
count(1) numOfCtrls,
comp.OBJECTIVE_CODE
from
amw.amw_fin_item_acc_ctrl ctrl,
amw_control_objectives comp
where
ctrl.FIN_CERTIFICATION_ID=P_CERTIFICATION_ID and
ctrl.OBJECT_TYPE = 'FINANCIAL ITEM' and
ctrl.FINANCIAL_ITEM_ID= P_FINANCIAL_ITEM_ID and
--ctrl.ACCOUNT_GROUP_ID is null and
--ctrl.NATURAL_ACCOUNT_ID is null and
ctrl.CONTROL_REV_ID =comp.CONTROL_REV_ID
group by OBJECTIVE_CODE;
select
count(1) numOfEvaluatedCtrls,
OBJECTIVE_CODE
from
(select
distinct
ctrl.ORGANIZATION_ID, ctrl.control_id, comp.OBJECTIVE_CODE
from
amw.amw_fin_item_acc_ctrl ctrl,
amw_opinions_log_v opinion,
amw_control_objectives comp,
amw_control_associations ctrlAsso
where
ctrl.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID
and
ctrl.OBJECT_TYPE = 'FINANCIAL ITEM' and
ctrl.FINANCIAL_ITEM_ID= P_FINANCIAL_ITEM_ID
and
opinion.pk1_value = ctrl.control_id and
opinion.pk3_value = ctrl.ORGANIZATION_ID and
opinion.OPINION_TYPE_CODE = 'EVALUATION' AND
opinion.OBJECT_NAME = 'AMW_ORG_CONTROL' and
ctrl.CONTROL_REV_ID =comp.CONTROL_REV_ID and
ctrlAsso.OBJECT_TYPE='RISK_FINCERT' and
ctrlAsso.PK1 = ctrl.FIN_CERTIFICATION_ID and
ctrlAsso.PK2 = ctrl.ORGANIZATION_ID and
ctrlAsso.CONTROL_ID = ctrl.control_id)
group by OBJECTIVE_CODE;
select
count(1) numIneffCtrls,
OBJECTIVE_CODE from
(select
distinct
ctrl.ORGANIZATION_ID, ctrl.control_id, comp.OBJECTIVE_CODE
from
amw.amw_fin_item_acc_ctrl ctrl,
amw_opinions_log_v opinion,
amw_control_objectives comp,
amw_control_associations ctrlAsso
where
ctrl.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID and
ctrl.OBJECT_TYPE = 'ACCOUNT' and
--ctrl.FINANCIAL_ITEM_ID= P_FINANCIAL_ITEM_ID and
ctrl.ACCOUNT_GROUP_ID = P_ACCOUNT_GROUP_ID and
NATURAL_ACCOUNT_ID = P_ACCOUNT_ID and
--opinion.OPINION_LOG_ID = ctrl.OPINION_LOG_ID and
opinion.pk1_value = ctrl.control_id and
opinion.pk3_value = ctrl.ORGANIZATION_ID and
opinion.audit_result_CODE <> 'EFFECTIVE' and
opinion.OPINION_TYPE_CODE = 'EVALUATION' AND
opinion.OBJECT_NAME = 'AMW_ORG_CONTROL' and
ctrl.CONTROL_REV_ID =comp.CONTROL_REV_ID and
ctrlAsso.OBJECT_TYPE='RISK_FINCERT' and
opinion.OPINION_LOG_ID = ctrlAsso.PK5 and
ctrlAsso.PK1 = ctrl.FIN_CERTIFICATION_ID and
ctrlAsso.PK2 = ctrl.ORGANIZATION_ID and
ctrlAsso.CONTROL_ID = ctrl.control_id)
group by OBJECTIVE_CODE;
SELECT COUNT(1) NUMINEFFCTRLS, OBJECTIVE_CODE
FROM
(SELECT DISTINCT CTRL.ORGANIZATION_ID, CTRL.CONTROL_ID, COMP.OBJECTIVE_CODE
FROM AMW.AMW_FIN_ITEM_ACC_CTRL CTRL,
AMW_CONTROL_OBJECTIVES COMP
WHERE
CTRL.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID
AND CTRL.OBJECT_TYPE = 'ACCOUNT'
AND CTRL.ACCOUNT_GROUP_ID = P_ACCOUNT_GROUP_ID
AND NATURAL_ACCOUNT_ID = P_ACCOUNT_ID
AND CTRL.CONTROL_REV_ID =COMP.CONTROL_REV_ID
AND EXISTS
(SELECT 1 FROM
AMW_CONTROL_ASSOCIATIONS CTRLASSO
WHERE
CTRLASSO.OBJECT_TYPE='RISK_FINCERT'
AND CTRLASSO.PK1 = P_CERTIFICATION_ID
AND CTRLASSO.PK2 = CTRL.ORGANIZATION_ID
AND CTRLASSO.CONTROL_ID = CTRL.CONTROL_ID
AND EXISTS
(SELECT 1 FROM AMW_OPINIONS_LOG_V OPINION
WHERE
OPINION.OPINION_LOG_ID = CTRLASSO.PK5
AND OPINION.PK1_VALUE = CTRLASSO.CONTROL_ID
AND OPINION.PK3_VALUE = CTRLASSO.PK2
AND OPINION.AUDIT_RESULT_CODE <> 'EFFECTIVE'
AND OPINION.OPINION_TYPE_CODE = 'EVALUATION'
AND OPINION.OBJECT_NAME = 'AMW_ORG_CONTROL'
))) GROUP BY OBJECTIVE_CODE ;
select
count(1) numOfEvaluatedCtrls,
OBJECTIVE_CODE from
(select
distinct
ctrl.ORGANIZATION_ID, ctrl.control_id, comp.OBJECTIVE_CODE
from
amw.amw_fin_item_acc_ctrl ctrl,
amw_opinions_log_v opinion,
amw_control_objectives comp,
amw_control_associations ctrlAsso
where
ctrl.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID
and
ctrl.OBJECT_TYPE = 'ACCOUNT' and
ctrl.ACCOUNT_GROUP_ID = P_ACCOUNT_GROUP_ID
and
NATURAL_ACCOUNT_ID = P_ACCOUNT_ID
and
opinion.pk1_value = ctrl.control_id and
opinion.pk3_value = ctrl.ORGANIZATION_ID and
opinion.OPINION_TYPE_CODE = 'EVALUATION' AND
opinion.OBJECT_NAME = 'AMW_ORG_CONTROL' and
ctrl.CONTROL_REV_ID =comp.CONTROL_REV_ID and
ctrlAsso.OBJECT_TYPE='RISK_FINCERT' and
ctrlAsso.PK1 = ctrl.FIN_CERTIFICATION_ID and
ctrlAsso.PK2 = ctrl.ORGANIZATION_ID and
ctrlAsso.CONTROL_ID = ctrl.control_id)
group by OBJECTIVE_CODE;
select
count(1) numOfCtrls,
comp.OBJECTIVE_CODE
from
amw.amw_fin_item_acc_ctrl ctrl,
amw_control_objectives comp
where
ctrl.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID and
ctrl.OBJECT_TYPE = 'ACCOUNT' and
-- ctrl.FINANCIAL_ITEM_ID= P_FINANCIAL_ITEM_ID and
ACCOUNT_GROUP_ID = P_ACCOUNT_GROUP_ID and
NATURAL_ACCOUNT_ID =P_ACCOUNT_ID and
ctrl.CONTROL_REV_ID =comp.CONTROL_REV_ID
group by OBJECTIVE_CODE;
select
LOOKUP_CODE
from
amw_lookups
where lookup_type = 'AMW_CONTROL_OBJECTIVES';
amw_fin_coso_views_pvt.INSERT_ROW(
x_fin_certification_id => P_CERTIFICATION_ID ,
x_financial_statement_id => P_FINANCIAL_STATEMENT_ID ,
x_financial_item_id => P_FINANCIAL_ITEM_ID ,
x_account_group_id => P_ACCOUNT_GROUP_ID ,
x_natural_account_id => P_ACCOUNT_ID ,
x_object_type => P_OBJECT_TYPE ,
x_ctrl_attribute_type => m_ctrl_attribute_type ,
x_ctrl_attr_code_1 => m_objectives_code(1) ,
x_ineff_ctrl_attr_1 => m_ineff_control(1) ,
x_total_ctrl_attr_1 => m_total_control(1) ,
x_ctrl_attr_code_2 => m_objectives_code(2) ,
x_ineff_ctrl_attr_2 => m_ineff_control(2) ,
x_total_ctrl_attr_2 => m_total_control(2) ,
x_ctrl_attr_code_3 => m_objectives_code(3) ,
x_ineff_ctrl_attr_3 => m_ineff_control(3) ,
x_total_ctrl_attr_3 => m_total_control(3) ,
x_ctrl_attr_code_4 => m_objectives_code(4) ,
x_ineff_ctrl_attr_4 => m_ineff_control(4) ,
x_total_ctrl_attr_4 => m_total_control(4) ,
x_ctrl_attr_code_5 => m_objectives_code(5) ,
x_ineff_ctrl_attr_5 => m_ineff_control(5) ,
x_total_ctrl_attr_5 => m_total_control(5) ,
x_ctrl_attr_code_6 => m_objectives_code(6) ,
x_ineff_ctrl_attr_6 => m_ineff_control(6) ,
x_total_ctrl_attr_6 => m_total_control(6) ,
x_ctrl_attr_code_7 => m_objectives_code(7) ,
x_ineff_ctrl_attr_7 => m_ineff_control(7) ,
x_total_ctrl_attr_7 => m_total_control(7) ,
x_ctrl_attr_code_8 => m_objectives_code(8) ,
x_ineff_ctrl_attr_8 => m_ineff_control(8) ,
x_total_ctrl_attr_8 => m_total_control(8) ,
x_ctrl_attr_code_9 => m_objectives_code(9) ,
x_ineff_ctrl_attr_9 => m_ineff_control(9) ,
x_total_ctrl_attr_9 => m_total_control(9) ,
x_ctrl_attr_code_10 => m_objectives_code(10) ,
x_ineff_ctrl_attr_10 => m_ineff_control(10) ,
x_total_ctrl_attr_10 => m_total_control(10) ,
x_ctrl_attr_code_11 => m_objectives_code(11) ,
x_ineff_ctrl_attr_11 => m_ineff_control(11) ,
x_total_ctrl_attr_11 => m_total_control(11) ,
x_ctrl_attr_code_12 => m_objectives_code(12) ,
x_ineff_ctrl_attr_12 => m_ineff_control(12) ,
x_total_ctrl_attr_12 => m_total_control(12) ,
x_ctrl_attr_code_13 => m_objectives_code(13) ,
x_ineff_ctrl_attr_13 => m_ineff_control(13) ,
x_total_ctrl_attr_13 => m_total_control(13) ,
x_ctrl_attr_code_14 => m_objectives_code(14) ,
x_ineff_ctrl_attr_14 => m_ineff_control(14) ,
x_total_ctrl_attr_14 => m_total_control(14) ,
x_ctrl_attr_code_15 => m_objectives_code(15) ,
x_ineff_ctrl_attr_15 => m_ineff_control(15) ,
x_total_ctrl_attr_15 => m_total_control(15) ,
x_ctrl_attr_code_16 => m_objectives_code(16) ,
x_ineff_ctrl_attr_16 => m_ineff_control(16) ,
x_total_ctrl_attr_16 => m_total_control(16) ,
x_ctrl_attr_code_17 => m_objectives_code(17) ,
x_ineff_ctrl_attr_17 => m_ineff_control(17) ,
x_total_ctrl_attr_17 => m_total_control(17) ,
x_ctrl_attr_code_18 => m_objectives_code(18) ,
x_ineff_ctrl_attr_18 => m_ineff_control(18) ,
x_total_ctrl_attr_18 => m_total_control(18) ,
x_ctrl_attr_code_19 => m_objectives_code(19) ,
x_ineff_ctrl_attr_19 => m_ineff_control(19) ,
x_total_ctrl_attr_19 => m_total_control(19) ,
x_ctrl_attr_code_20 => m_objectives_code(20) ,
x_ineff_ctrl_attr_20 => m_ineff_control(20) ,
x_total_ctrl_attr_20 => m_total_control(20) ,
x_ctrl_attr_code_21 => m_objectives_code(21) ,
x_ineff_ctrl_attr_21 => m_ineff_control(21) ,
x_total_ctrl_attr_21 => m_total_control(21) ,
x_ctrl_attr_code_22 => m_objectives_code(22) ,
x_ineff_ctrl_attr_22 => m_ineff_control(22) ,
x_total_ctrl_attr_22 => m_total_control(22) ,
x_ctrl_attr_code_23 => m_objectives_code(23) ,
x_ineff_ctrl_attr_23 => m_ineff_control(23) ,
x_total_ctrl_attr_23 => m_total_control(23) ,
x_ctrl_attr_code_24 => m_objectives_code(24) ,
x_ineff_ctrl_attr_24 => m_ineff_control(24) ,
x_total_ctrl_attr_24 => m_total_control(24) ,
x_ctrl_attr_code_25 => m_objectives_code(25) ,
x_ineff_ctrl_attr_25 => m_ineff_control(25) ,
x_total_ctrl_attr_25 => m_total_control(25) ,
x_ctrl_attr_code_26 => m_objectives_code(26) ,
x_ineff_ctrl_attr_26 => m_ineff_control(26) ,
x_total_ctrl_attr_26 => m_total_control(26) ,
x_ctrl_attr_code_27 => m_objectives_code(27) ,
x_ineff_ctrl_attr_27 => m_ineff_control(27) ,
x_total_ctrl_attr_27 => m_total_control(27) ,
x_ctrl_attr_code_28 => m_objectives_code(28) ,
x_ineff_ctrl_attr_28 => m_ineff_control(28) ,
x_total_ctrl_attr_28 => m_total_control(28) ,
x_ctrl_attr_code_29 => m_objectives_code(29) ,
x_ineff_ctrl_attr_29 => m_ineff_control(29) ,
x_total_ctrl_attr_29 => m_total_control(29) ,
x_ctrl_attr_code_30 => m_objectives_code(30) ,
x_ineff_ctrl_attr_30 => m_ineff_control(30) ,
x_total_ctrl_attr_30 => m_total_control(30) ,
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 ,
x_acc_assert_flag1 => m_acc_assert_flag(1),
x_acc_assert_flag2 => m_acc_assert_flag(2),
x_acc_assert_flag3 => m_acc_assert_flag(3),
x_acc_assert_flag4 => m_acc_assert_flag(4),
x_acc_assert_flag5 => m_acc_assert_flag(5),
x_acc_assert_flag6 => m_acc_assert_flag(6),
x_acc_assert_flag7 => m_acc_assert_flag(7),
x_acc_assert_flag8 => m_acc_assert_flag(8),
x_acc_assert_flag9 => m_acc_assert_flag(9),
x_acc_assert_flag10 => m_acc_assert_flag(10),
x_acc_assert_flag11 => m_acc_assert_flag(11),
x_acc_assert_flag12 => m_acc_assert_flag(12),
x_acc_assert_flag13 => m_acc_assert_flag(13),
x_acc_assert_flag14 => m_acc_assert_flag(14),
x_acc_assert_flag15 => m_acc_assert_flag(15),
x_acc_assert_flag16 => m_acc_assert_flag(16),
x_acc_assert_flag17 => m_acc_assert_flag(17),
x_acc_assert_flag18 => m_acc_assert_flag(18),
x_acc_assert_flag19 => m_acc_assert_flag(19),
x_acc_assert_flag20 => m_acc_assert_flag(20),
x_acc_assert_flag21 => m_acc_assert_flag(21),
x_acc_assert_flag22 => m_acc_assert_flag(22),
x_acc_assert_flag23 => m_acc_assert_flag(23),
x_acc_assert_flag24 => m_acc_assert_flag(24),
x_acc_assert_flag25 => m_acc_assert_flag(25),
x_acc_assert_flag26 => m_acc_assert_flag(26),
x_acc_assert_flag27 => m_acc_assert_flag(27),
x_acc_assert_flag28 => m_acc_assert_flag(28),
x_acc_assert_flag29 => m_acc_assert_flag(29),
x_acc_assert_flag30 => m_acc_assert_flag(30),
x_eval_ctrl_attr_1 => m_evaluated_ctrls(1),
x_eval_ctrl_attr_2 => m_evaluated_ctrls(2),
x_eval_ctrl_attr_3 => m_evaluated_ctrls(3),
x_eval_ctrl_attr_4 => m_evaluated_ctrls(4),
x_eval_ctrl_attr_5 => m_evaluated_ctrls(5),
x_eval_ctrl_attr_6 => m_evaluated_ctrls(6),
x_eval_ctrl_attr_7 => m_evaluated_ctrls(7),
x_eval_ctrl_attr_8 => m_evaluated_ctrls(8),
x_eval_ctrl_attr_9 => m_evaluated_ctrls(9),
x_eval_ctrl_attr_10 => m_evaluated_ctrls(10),
x_eval_ctrl_attr_11 => m_evaluated_ctrls(11),
x_eval_ctrl_attr_12 => m_evaluated_ctrls(12),
x_eval_ctrl_attr_13 => m_evaluated_ctrls(13),
x_eval_ctrl_attr_14 => m_evaluated_ctrls(14),
x_eval_ctrl_attr_15 => m_evaluated_ctrls(15),
x_eval_ctrl_attr_16 => m_evaluated_ctrls(16),
x_eval_ctrl_attr_17 => m_evaluated_ctrls(17),
x_eval_ctrl_attr_18 => m_evaluated_ctrls(18),
x_eval_ctrl_attr_19 => m_evaluated_ctrls(19),
x_eval_ctrl_attr_20 => m_evaluated_ctrls(20),
x_eval_ctrl_attr_21 => m_evaluated_ctrls(21),
x_eval_ctrl_attr_22 => m_evaluated_ctrls(22),
x_eval_ctrl_attr_23 => m_evaluated_ctrls(23),
x_eval_ctrl_attr_24 => m_evaluated_ctrls(24),
x_eval_ctrl_attr_25 => m_evaluated_ctrls(25),
x_eval_ctrl_attr_26 => m_evaluated_ctrls(26),
x_eval_ctrl_attr_27 => m_evaluated_ctrls(27),
x_eval_ctrl_attr_28 => m_evaluated_ctrls(28),
x_eval_ctrl_attr_29 => m_evaluated_ctrls(29),
x_eval_ctrl_attr_30 => m_evaluated_ctrls(30),
x_display_flag => m_display_flag );
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));
select
count(1) numIneffCtrls,
ASSERTION_CODE
from
(select
distinct
ctrl.ORGANIZATION_ID, ctrl.control_id, comp.ASSERTION_CODE
from
amw.amw_fin_item_acc_ctrl ctrl,
amw_opinions_log_v opinion,
amw_control_assertions comp,
amw_control_associations ctrlAsso
where
ctrl.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID and
ctrl.OBJECT_TYPE = 'FINANCIAL ITEM' and
ctrl.FINANCIAL_ITEM_ID= P_FINANCIAL_ITEM_ID and
-- ctrl.ACCOUNT_GROUP_ID is null and
-- ctrl.NATURAL_ACCOUNT_ID is null and
--opinion.OPINION_LOG_ID = ctrl.OPINION_LOG_ID and
opinion.pk1_value = ctrl.control_id and
opinion.pk3_value = ctrl.ORGANIZATION_ID and
opinion.audit_result_CODE <> 'EFFECTIVE' and
opinion.OPINION_TYPE_CODE = 'EVALUATION' AND
opinion.OBJECT_NAME = 'AMW_ORG_CONTROL' and
ctrl.CONTROL_REV_ID =comp.CONTROL_REV_ID and
ctrlAsso.OBJECT_TYPE='RISK_FINCERT' and
opinion.OPINION_LOG_ID = ctrlAsso.PK5 and
ctrlAsso.PK1 = ctrl.FIN_CERTIFICATION_ID and
ctrlAsso.PK2 = ctrl.ORGANIZATION_ID and
ctrlAsso.CONTROL_ID = ctrl.control_id)
group by ASSERTION_CODE;
select
count(1) numOfEvaluatedCtrls,
ASSERTION_CODE
from
(select
distinct
ctrl.ORGANIZATION_ID, ctrl.control_id, comp.ASSERTION_CODE
from
amw.amw_fin_item_acc_ctrl ctrl,
amw_opinions_v opinion,
amw_control_assertions comp,
amw_control_associations ctrlAsso
where
ctrl.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID
and
ctrl.OBJECT_TYPE = 'FINANCIAL ITEM' and
ctrl.FINANCIAL_ITEM_ID= P_FINANCIAL_ITEM_ID
and
opinion.pk1_value = ctrl.control_id and
opinion.pk3_value = ctrl.ORGANIZATION_ID and
opinion.OPINION_TYPE_CODE = 'EVALUATION' AND
opinion.OBJECT_NAME = 'AMW_ORG_CONTROL' and
ctrl.CONTROL_REV_ID =comp.CONTROL_REV_ID and
ctrlAsso.OBJECT_TYPE='RISK_FINCERT' and
ctrlAsso.PK1 = ctrl.FIN_CERTIFICATION_ID and
ctrlAsso.PK2 = ctrl.ORGANIZATION_ID and
ctrlAsso.CONTROL_ID = ctrl.control_id)
group by ASSERTION_CODE;
select
count(1) numOfCtrls,
comp.ASSERTION_CODE
from
amw.amw_fin_item_acc_ctrl ctrl,
amw_control_assertions comp
where
ctrl.FIN_CERTIFICATION_ID=P_CERTIFICATION_ID and
ctrl.OBJECT_TYPE = 'FINANCIAL ITEM' and
ctrl.FINANCIAL_ITEM_ID= P_FINANCIAL_ITEM_ID and
--ctrl.ACCOUNT_GROUP_ID is null and
--ctrl.NATURAL_ACCOUNT_ID is null and
ctrl.CONTROL_REV_ID =comp.CONTROL_REV_ID
group by ASSERTION_CODE;
select
count(1) numIneffCtrls,
ASSERTION_CODE
from
(select
distinct
ctrl.ORGANIZATION_ID, ctrl.control_id, comp.ASSERTION_CODE
from
amw.amw_fin_item_acc_ctrl ctrl,
amw_opinions_log_v opinion,
amw_control_assertions comp,
amw_control_associations ctrlAsso
where
ctrl.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID and
ctrl.OBJECT_TYPE = 'ACCOUNT' and
--ctrl.FINANCIAL_ITEM_ID= P_FINANCIAL_ITEM_ID and
ctrl.ACCOUNT_GROUP_ID = P_ACCOUNT_GROUP_ID and
NATURAL_ACCOUNT_ID = P_ACCOUNT_ID and
--opinion.OPINION_LOG_ID = ctrl.OPINION_LOG_ID and
opinion.pk1_value = ctrl.control_id and
opinion.pk3_value = ctrl.ORGANIZATION_ID and
opinion.audit_result_CODE <> 'EFFECTIVE' and
opinion.OPINION_TYPE_CODE = 'EVALUATION' AND
opinion.OBJECT_NAME = 'AMW_ORG_CONTROL' and
ctrl.CONTROL_REV_ID =comp.CONTROL_REV_ID and
ctrlAsso.OBJECT_TYPE='RISK_FINCERT' and
opinion.OPINION_LOG_ID = ctrlAsso.PK5 and
ctrlAsso.PK1 = ctrl.FIN_CERTIFICATION_ID and
ctrlAsso.PK2 = ctrl.ORGANIZATION_ID and
ctrlAsso.CONTROL_ID = ctrl.control_id)
group by ASSERTION_CODE;
SELECT COUNT(1) NUMINEFFCTRLS, ASSERTION_CODE
FROM
(SELECT DISTINCT CTRL.ORGANIZATION_ID, CTRL.CONTROL_ID, COMP.ASSERTION_CODE
FROM AMW.AMW_FIN_ITEM_ACC_CTRL CTRL, AMW_CONTROL_ASSERTIONS COMP
WHERE
CTRL.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID
AND CTRL.OBJECT_TYPE = 'ACCOUNT'
AND CTRL.ACCOUNT_GROUP_ID = P_ACCOUNT_GROUP_ID
AND NATURAL_ACCOUNT_ID = P_ACCOUNT_ID
AND CTRL.CONTROL_REV_ID = COMP.CONTROL_REV_ID
AND EXISTS
(SELECT 1 FROM AMW_OPINIONS_LOG_V OPINION
WHERE OPINION.PK1_VALUE = CTRL.CONTROL_ID
AND OPINION.PK3_VALUE = CTRL.ORGANIZATION_ID
AND OPINION.AUDIT_RESULT_CODE <> 'EFFECTIVE'
AND OPINION.OPINION_TYPE_CODE = 'EVALUATION'
AND OPINION.OBJECT_NAME = 'AMW_ORG_CONTROL'
AND EXISTS
(SELECT 1 FROM AMW_CONTROL_ASSOCIATIONS CTRLASSO
WHERE CTRLASSO.OBJECT_TYPE='RISK_FINCERT'
AND OPINION.OPINION_LOG_ID = CTRLASSO.PK5
AND CTRLASSO.PK1 = P_CERTIFICATION_ID
AND CTRLASSO.PK2 = OPINION.PK3_VALUE
AND CTRLASSO.CONTROL_ID = OPINION.PK1_VALUE
))) GROUP BY ASSERTION_CODE;
select
count(1) numOfCtrls,
comp.ASSERTION_CODE
from
amw.amw_fin_item_acc_ctrl ctrl,
amw_control_assertions comp
where
ctrl.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID and
ctrl.OBJECT_TYPE = 'ACCOUNT' and
-- ctrl.FINANCIAL_ITEM_ID= P_FINANCIAL_ITEM_ID and
ctrl.ACCOUNT_GROUP_ID = P_ACCOUNT_GROUP_ID and
NATURAL_ACCOUNT_ID =P_ACCOUNT_ID and
ctrl.CONTROL_REV_ID =comp.CONTROL_REV_ID
group by ASSERTION_CODE;
select
count(1) numOfEvaluatedCtrls,
ASSERTION_CODE
from
(select
distinct
ctrl.ORGANIZATION_ID, ctrl.control_id, comp.ASSERTION_CODE
from
amw.amw_fin_item_acc_ctrl ctrl,
amw_opinions_log_v opinion,
amw_control_assertions comp,
amw_control_associations ctrlAsso
where
ctrl.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID and
ctrl.OBJECT_TYPE = 'ACCOUNT' and
ctrl.ACCOUNT_GROUP_ID = P_ACCOUNT_GROUP_ID and
NATURAL_ACCOUNT_ID = P_ACCOUNT_ID and
opinion.pk1_value = ctrl.control_id and
opinion.pk3_value = ctrl.ORGANIZATION_ID and
opinion.OPINION_TYPE_CODE = 'EVALUATION' AND
opinion.OBJECT_NAME = 'AMW_ORG_CONTROL' and
ctrl.CONTROL_REV_ID =comp.CONTROL_REV_ID and
ctrlAsso.OBJECT_TYPE='RISK_FINCERT' and
ctrlAsso.PK1 = ctrl.FIN_CERTIFICATION_ID and
ctrlAsso.PK2 = ctrl.ORGANIZATION_ID and
ctrlAsso.CONTROL_ID = ctrl.control_id)
group by ASSERTION_CODE;
select
LOOKUP_CODE
from
amw_lookups
where lookup_type = 'AMW_CONTROL_ASSERTIONS';
select
ASSERTION_CODE
from
amw_account_assertions
where
NATURAL_ACCOUNT_ID =P_ACCOUNT_ID ;
select
distinct
ASSERTION_CODE
from
amw_account_assertions
where
((NATURAL_ACCOUNT_ID =P_ACCOUNT_ID) or (NATURAL_ACCOUNT_ID in (select CHILD_NATURAL_ACCOUNT_ID from amw_fin_key_acct_flat
where PARENT_NATURAL_ACCOUNT_ID =P_ACCOUNT_ID and ACCOUNT_GROUP_ID=P_ACCOUNT_GROUP_ID)));
select DISTINCT
ASSERTION_CODE
from
amw_account_assertions
where
NATURAL_ACCOUNT_ID IN
(select DISTINCT NATURAL_ACCOUNT_ID from amw_fin_cert_scope where fin_certification_id = P_CERTIFICATION_ID and
financial_item_id = P_FINANCIAL_ITEM_ID );
amw_fin_coso_views_pvt.INSERT_ROW(
x_fin_certification_id => P_CERTIFICATION_ID ,
x_financial_statement_id => P_FINANCIAL_STATEMENT_ID ,
x_financial_item_id => P_FINANCIAL_ITEM_ID ,
x_account_group_id => P_ACCOUNT_GROUP_ID ,
x_natural_account_id => P_ACCOUNT_ID ,
x_object_type => P_OBJECT_TYPE ,
x_ctrl_attribute_type => m_ctrl_attribute_type ,
x_ctrl_attr_code_1 => m_assertions_code(1) ,
x_ineff_ctrl_attr_1 => m_ineff_control(1) ,
x_total_ctrl_attr_1 => m_total_control(1) ,
x_ctrl_attr_code_2 => m_assertions_code(2) ,
x_ineff_ctrl_attr_2 => m_ineff_control(2) ,
x_total_ctrl_attr_2 => m_total_control(2) ,
x_ctrl_attr_code_3 => m_assertions_code(3) ,
x_ineff_ctrl_attr_3 => m_ineff_control(3) ,
x_total_ctrl_attr_3 => m_total_control(3) ,
x_ctrl_attr_code_4 => m_assertions_code(4) ,
x_ineff_ctrl_attr_4 => m_ineff_control(4) ,
x_total_ctrl_attr_4 => m_total_control(4) ,
x_ctrl_attr_code_5 => m_assertions_code(5) ,
x_ineff_ctrl_attr_5 => m_ineff_control(5) ,
x_total_ctrl_attr_5 => m_total_control(5) ,
x_ctrl_attr_code_6 => m_assertions_code(6) ,
x_ineff_ctrl_attr_6 => m_ineff_control(6) ,
x_total_ctrl_attr_6 => m_total_control(6) ,
x_ctrl_attr_code_7 => m_assertions_code(7) ,
x_ineff_ctrl_attr_7 => m_ineff_control(7) ,
x_total_ctrl_attr_7 => m_total_control(7) ,
x_ctrl_attr_code_8 => m_assertions_code(8) ,
x_ineff_ctrl_attr_8 => m_ineff_control(8) ,
x_total_ctrl_attr_8 => m_total_control(8) ,
x_ctrl_attr_code_9 => m_assertions_code(9) ,
x_ineff_ctrl_attr_9 => m_ineff_control(9) ,
x_total_ctrl_attr_9 => m_total_control(9) ,
x_ctrl_attr_code_10 => m_assertions_code(10) ,
x_ineff_ctrl_attr_10 => m_ineff_control(10) ,
x_total_ctrl_attr_10 => m_total_control(10) ,
x_ctrl_attr_code_11 => m_assertions_code(11) ,
x_ineff_ctrl_attr_11 => m_ineff_control(11) ,
x_total_ctrl_attr_11 => m_total_control(11) ,
x_ctrl_attr_code_12 => m_assertions_code(12) ,
x_ineff_ctrl_attr_12 => m_ineff_control(12) ,
x_total_ctrl_attr_12 => m_total_control(12) ,
x_ctrl_attr_code_13 => m_assertions_code(13) ,
x_ineff_ctrl_attr_13 => m_ineff_control(13) ,
x_total_ctrl_attr_13 => m_total_control(13) ,
x_ctrl_attr_code_14 => m_assertions_code(14) ,
x_ineff_ctrl_attr_14 => m_ineff_control(14) ,
x_total_ctrl_attr_14 => m_total_control(14) ,
x_ctrl_attr_code_15 => m_assertions_code(15) ,
x_ineff_ctrl_attr_15 => m_ineff_control(15) ,
x_total_ctrl_attr_15 => m_total_control(15) ,
x_ctrl_attr_code_16 => m_assertions_code(16) ,
x_ineff_ctrl_attr_16 => m_ineff_control(16) ,
x_total_ctrl_attr_16 => m_total_control(16) ,
x_ctrl_attr_code_17 => m_assertions_code(17) ,
x_ineff_ctrl_attr_17 => m_ineff_control(17) ,
x_total_ctrl_attr_17 => m_total_control(17) ,
x_ctrl_attr_code_18 => m_assertions_code(18) ,
x_ineff_ctrl_attr_18 => m_ineff_control(18) ,
x_total_ctrl_attr_18 => m_total_control(18) ,
x_ctrl_attr_code_19 => m_assertions_code(19) ,
x_ineff_ctrl_attr_19 => m_ineff_control(19) ,
x_total_ctrl_attr_19 => m_total_control(19) ,
x_ctrl_attr_code_20 => m_assertions_code(20) ,
x_ineff_ctrl_attr_20 => m_ineff_control(20) ,
x_total_ctrl_attr_20 => m_total_control(20) ,
x_ctrl_attr_code_21 => m_assertions_code(21) ,
x_ineff_ctrl_attr_21 => m_ineff_control(21) ,
x_total_ctrl_attr_21 => m_total_control(21) ,
x_ctrl_attr_code_22 => m_assertions_code(22) ,
x_ineff_ctrl_attr_22 => m_ineff_control(22) ,
x_total_ctrl_attr_22 => m_total_control(22) ,
x_ctrl_attr_code_23 => m_assertions_code(23) ,
x_ineff_ctrl_attr_23 => m_ineff_control(23) ,
x_total_ctrl_attr_23 => m_total_control(23) ,
x_ctrl_attr_code_24 => m_assertions_code(24) ,
x_ineff_ctrl_attr_24 => m_ineff_control(24) ,
x_total_ctrl_attr_24 => m_total_control(24) ,
x_ctrl_attr_code_25 => m_assertions_code(25) ,
x_ineff_ctrl_attr_25 => m_ineff_control(25) ,
x_total_ctrl_attr_25 => m_total_control(25) ,
x_ctrl_attr_code_26 => m_assertions_code(26) ,
x_ineff_ctrl_attr_26 => m_ineff_control(26) ,
x_total_ctrl_attr_26 => m_total_control(26) ,
x_ctrl_attr_code_27 => m_assertions_code(27) ,
x_ineff_ctrl_attr_27 => m_ineff_control(27) ,
x_total_ctrl_attr_27 => m_total_control(27) ,
x_ctrl_attr_code_28 => m_assertions_code(28) ,
x_ineff_ctrl_attr_28 => m_ineff_control(28) ,
x_total_ctrl_attr_28 => m_total_control(28) ,
x_ctrl_attr_code_29 => m_assertions_code(29) ,
x_ineff_ctrl_attr_29 => m_ineff_control(29) ,
x_total_ctrl_attr_29 => m_total_control(29) ,
x_ctrl_attr_code_30 => m_assertions_code(30) ,
x_ineff_ctrl_attr_30 => m_ineff_control(30) ,
x_total_ctrl_attr_30 => m_total_control(30) ,
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 ,
x_acc_assert_flag1 => m_acc_assert_flag(1),
x_acc_assert_flag2 => m_acc_assert_flag(2),
x_acc_assert_flag3 => m_acc_assert_flag(3),
x_acc_assert_flag4 => m_acc_assert_flag(4),
x_acc_assert_flag5 => m_acc_assert_flag(5),
x_acc_assert_flag6 => m_acc_assert_flag(6),
x_acc_assert_flag7 => m_acc_assert_flag(7),
x_acc_assert_flag8 => m_acc_assert_flag(8),
x_acc_assert_flag9 => m_acc_assert_flag(9),
x_acc_assert_flag10 => m_acc_assert_flag(10),
x_acc_assert_flag11 => m_acc_assert_flag(11),
x_acc_assert_flag12 => m_acc_assert_flag(12),
x_acc_assert_flag13 => m_acc_assert_flag(13),
x_acc_assert_flag14 => m_acc_assert_flag(14),
x_acc_assert_flag15 => m_acc_assert_flag(15),
x_acc_assert_flag16 => m_acc_assert_flag(16),
x_acc_assert_flag17 => m_acc_assert_flag(17),
x_acc_assert_flag18 => m_acc_assert_flag(18),
x_acc_assert_flag19 => m_acc_assert_flag(19),
x_acc_assert_flag20 => m_acc_assert_flag(20),
x_acc_assert_flag21 => m_acc_assert_flag(21),
x_acc_assert_flag22 => m_acc_assert_flag(22),
x_acc_assert_flag23 => m_acc_assert_flag(23),
x_acc_assert_flag24 => m_acc_assert_flag(24),
x_acc_assert_flag25 => m_acc_assert_flag(25),
x_acc_assert_flag26 => m_acc_assert_flag(26),
x_acc_assert_flag27 => m_acc_assert_flag(27),
x_acc_assert_flag28 => m_acc_assert_flag(28),
x_acc_assert_flag29 => m_acc_assert_flag(29),
x_acc_assert_flag30 => m_acc_assert_flag(30),
x_eval_ctrl_attr_1 => m_evaluated_ctrls(1),
x_eval_ctrl_attr_2 => m_evaluated_ctrls(2),
x_eval_ctrl_attr_3 => m_evaluated_ctrls(3),
x_eval_ctrl_attr_4 => m_evaluated_ctrls(4),
x_eval_ctrl_attr_5 => m_evaluated_ctrls(5),
x_eval_ctrl_attr_6 => m_evaluated_ctrls(6),
x_eval_ctrl_attr_7 => m_evaluated_ctrls(7),
x_eval_ctrl_attr_8 => m_evaluated_ctrls(8),
x_eval_ctrl_attr_9 => m_evaluated_ctrls(9),
x_eval_ctrl_attr_10 => m_evaluated_ctrls(10),
x_eval_ctrl_attr_11 => m_evaluated_ctrls(11),
x_eval_ctrl_attr_12 => m_evaluated_ctrls(12),
x_eval_ctrl_attr_13 => m_evaluated_ctrls(13),
x_eval_ctrl_attr_14 => m_evaluated_ctrls(14),
x_eval_ctrl_attr_15 => m_evaluated_ctrls(15),
x_eval_ctrl_attr_16 => m_evaluated_ctrls(16),
x_eval_ctrl_attr_17 => m_evaluated_ctrls(17),
x_eval_ctrl_attr_18 => m_evaluated_ctrls(18),
x_eval_ctrl_attr_19 => m_evaluated_ctrls(19),
x_eval_ctrl_attr_20 => m_evaluated_ctrls(20),
x_eval_ctrl_attr_21 => m_evaluated_ctrls(21),
x_eval_ctrl_attr_22 => m_evaluated_ctrls(22),
x_eval_ctrl_attr_23 => m_evaluated_ctrls(23),
x_eval_ctrl_attr_24 => m_evaluated_ctrls(24),
x_eval_ctrl_attr_25 => m_evaluated_ctrls(25),
x_eval_ctrl_attr_26 => m_evaluated_ctrls(26),
x_eval_ctrl_attr_27 => m_evaluated_ctrls(27),
x_eval_ctrl_attr_28 => m_evaluated_ctrls(28),
x_eval_ctrl_attr_29 => m_evaluated_ctrls(29),
x_eval_ctrl_attr_30 => m_evaluated_ctrls(30),
x_display_flag => m_display_flag );
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));
/* **************************** DELETE_ROWS in case of refresh data for a particular certification ******************* */
procedure DELETE_ROWS ( x_fin_certification_id NUMBER ) IS
begin
DELETE
from
amw_fin_cert_ctrl_sum
where
fin_certification_id = x_fin_certification_id ;
end DELETE_ROWS ;
/* ******************************************* INSERT_ROW ************************************************************* */
procedure INSERT_ROW (
x_fin_certification_id NUMBER ,
x_financial_statement_id NUMBER ,
x_financial_item_id NUMBER ,
x_account_group_id NUMBER,
x_natural_account_id NUMBER,
x_object_type VARCHAR2,
x_ctrl_attribute_type VARCHAR2,
x_ctrl_attr_code_1 VARCHAR2,
x_ineff_ctrl_attr_1 NUMBER,
x_total_ctrl_attr_1 NUMBER,
x_ctrl_attr_code_2 VARCHAR2,
x_ineff_ctrl_attr_2 NUMBER,
x_total_ctrl_attr_2 NUMBER,
x_ctrl_attr_code_3 VARCHAR2,
x_ineff_ctrl_attr_3 NUMBER,
x_total_ctrl_attr_3 NUMBER,
x_ctrl_attr_code_4 VARCHAR2,
x_ineff_ctrl_attr_4 NUMBER,
x_total_ctrl_attr_4 NUMBER,
x_ctrl_attr_code_5 VARCHAR2,
x_ineff_ctrl_attr_5 NUMBER,
x_total_ctrl_attr_5 NUMBER,
x_ctrl_attr_code_6 VARCHAR2,
x_ineff_ctrl_attr_6 NUMBER,
x_total_ctrl_attr_6 NUMBER,
x_ctrl_attr_code_7 VARCHAR2,
x_ineff_ctrl_attr_7 NUMBER,
x_total_ctrl_attr_7 NUMBER,
x_ctrl_attr_code_8 VARCHAR2,
x_ineff_ctrl_attr_8 NUMBER,
x_total_ctrl_attr_8 NUMBER,
x_ctrl_attr_code_9 VARCHAR2,
x_ineff_ctrl_attr_9 NUMBER,
x_total_ctrl_attr_9 NUMBER,
x_ctrl_attr_code_10 VARCHAR2,
x_ineff_ctrl_attr_10 NUMBER,
x_total_ctrl_attr_10 NUMBER,
x_ctrl_attr_code_11 VARCHAR2,
x_ineff_ctrl_attr_11 NUMBER,
x_total_ctrl_attr_11 NUMBER,
x_ctrl_attr_code_12 VARCHAR2,
x_ineff_ctrl_attr_12 NUMBER,
x_total_ctrl_attr_12 NUMBER,
x_ctrl_attr_code_13 VARCHAR2,
x_ineff_ctrl_attr_13 NUMBER,
x_total_ctrl_attr_13 NUMBER,
x_ctrl_attr_code_14 VARCHAR2,
x_ineff_ctrl_attr_14 NUMBER,
x_total_ctrl_attr_14 NUMBER,
x_ctrl_attr_code_15 VARCHAR2,
x_ineff_ctrl_attr_15 NUMBER,
x_total_ctrl_attr_15 NUMBER,
x_ctrl_attr_code_16 VARCHAR2,
x_ineff_ctrl_attr_16 NUMBER,
x_total_ctrl_attr_16 NUMBER,
x_ctrl_attr_code_17 VARCHAR2,
x_ineff_ctrl_attr_17 NUMBER,
x_total_ctrl_attr_17 NUMBER,
x_ctrl_attr_code_18 VARCHAR2,
x_ineff_ctrl_attr_18 NUMBER,
x_total_ctrl_attr_18 NUMBER,
x_ctrl_attr_code_19 VARCHAR2,
x_ineff_ctrl_attr_19 NUMBER,
x_total_ctrl_attr_19 NUMBER,
x_ctrl_attr_code_20 VARCHAR2,
x_ineff_ctrl_attr_20 NUMBER,
x_total_ctrl_attr_20 NUMBER,
x_ctrl_attr_code_21 VARCHAR2,
x_ineff_ctrl_attr_21 NUMBER,
x_total_ctrl_attr_21 NUMBER,
x_ctrl_attr_code_22 VARCHAR2,
x_ineff_ctrl_attr_22 NUMBER,
x_total_ctrl_attr_22 NUMBER,
x_ctrl_attr_code_23 VARCHAR2,
x_ineff_ctrl_attr_23 NUMBER,
x_total_ctrl_attr_23 NUMBER,
x_ctrl_attr_code_24 VARCHAR2,
x_ineff_ctrl_attr_24 NUMBER,
x_total_ctrl_attr_24 NUMBER,
x_ctrl_attr_code_25 VARCHAR2,
x_ineff_ctrl_attr_25 NUMBER,
x_total_ctrl_attr_25 NUMBER,
x_ctrl_attr_code_26 VARCHAR2,
x_ineff_ctrl_attr_26 NUMBER,
x_total_ctrl_attr_26 NUMBER,
x_ctrl_attr_code_27 VARCHAR2,
x_ineff_ctrl_attr_27 NUMBER,
x_total_ctrl_attr_27 NUMBER,
x_ctrl_attr_code_28 VARCHAR2,
x_ineff_ctrl_attr_28 NUMBER,
x_total_ctrl_attr_28 NUMBER,
x_ctrl_attr_code_29 VARCHAR2,
x_ineff_ctrl_attr_29 NUMBER,
x_total_ctrl_attr_29 NUMBER,
x_ctrl_attr_code_30 VARCHAR2,
x_ineff_ctrl_attr_30 NUMBER,
x_total_ctrl_attr_30 NUMBER,
x_created_by NUMBER ,
x_creation_date DATE ,
x_last_updated_by NUMBER,
x_last_update_date DATE ,
x_last_update_login NUMBER,
-- x_security_group_id NUMBER,
x_object_version_number NUMBER,
x_acc_assert_flag1 VARCHAR2,
x_acc_assert_flag2 VARCHAR2,
x_acc_assert_flag3 VARCHAR2,
x_acc_assert_flag4 VARCHAR2,
x_acc_assert_flag5 VARCHAR2,
x_acc_assert_flag6 VARCHAR2,
x_acc_assert_flag7 VARCHAR2,
x_acc_assert_flag8 VARCHAR2,
x_acc_assert_flag9 VARCHAR2,
x_acc_assert_flag10 VARCHAR2,
x_acc_assert_flag11 VARCHAR2,
x_acc_assert_flag12 VARCHAR2,
x_acc_assert_flag13 VARCHAR2,
x_acc_assert_flag14 VARCHAR2,
x_acc_assert_flag15 VARCHAR2,
x_acc_assert_flag16 VARCHAR2,
x_acc_assert_flag17 VARCHAR2,
x_acc_assert_flag18 VARCHAR2,
x_acc_assert_flag19 VARCHAR2,
x_acc_assert_flag20 VARCHAR2,
x_acc_assert_flag21 VARCHAR2,
x_acc_assert_flag22 VARCHAR2,
x_acc_assert_flag23 VARCHAR2,
x_acc_assert_flag24 VARCHAR2,
x_acc_assert_flag25 VARCHAR2,
x_acc_assert_flag26 VARCHAR2,
x_acc_assert_flag27 VARCHAR2,
x_acc_assert_flag28 VARCHAR2,
x_acc_assert_flag29 VARCHAR2,
x_acc_assert_flag30 VARCHAR2,
x_eval_ctrl_attr_1 NUMBER,
x_eval_ctrl_attr_2 NUMBER,
x_eval_ctrl_attr_3 NUMBER,
x_eval_ctrl_attr_4 NUMBER,
x_eval_ctrl_attr_5 NUMBER,
x_eval_ctrl_attr_6 NUMBER,
x_eval_ctrl_attr_7 NUMBER,
x_eval_ctrl_attr_8 NUMBER,
x_eval_ctrl_attr_9 NUMBER,
x_eval_ctrl_attr_10 NUMBER,
x_eval_ctrl_attr_11 NUMBER,
x_eval_ctrl_attr_12 NUMBER,
x_eval_ctrl_attr_13 NUMBER,
x_eval_ctrl_attr_14 NUMBER,
x_eval_ctrl_attr_15 NUMBER,
x_eval_ctrl_attr_16 NUMBER,
x_eval_ctrl_attr_17 NUMBER,
x_eval_ctrl_attr_18 NUMBER,
x_eval_ctrl_attr_19 NUMBER,
x_eval_ctrl_attr_20 NUMBER,
x_eval_ctrl_attr_21 NUMBER,
x_eval_ctrl_attr_22 NUMBER,
x_eval_ctrl_attr_23 NUMBER,
x_eval_ctrl_attr_24 NUMBER,
x_eval_ctrl_attr_25 NUMBER,
x_eval_ctrl_attr_26 NUMBER,
x_eval_ctrl_attr_27 NUMBER,
x_eval_ctrl_attr_28 NUMBER,
x_eval_ctrl_attr_29 NUMBER,
x_eval_ctrl_attr_30 NUMBER,
x_display_flag VARCHAR2
) is
begin
declare
var_fin_certification_id number;
/* select
fin_certification_id into var_fin_certification_id
from
amw_fin_cert_ctrl_sum
where
fin_certification_id = x_fin_certification_id and
financial_statement_id = x_financial_statement_id and
NVL(financial_item_id, -1) = NVL(x_financial_item_id, -1) and
NVL(account_group_id, -1) = NVL(x_account_group_id, -1) and
nvl(natural_account_id, -1) = nvl(x_natural_account_id, -1) and
CTRL_ATTRIBUTE_TYPE = x_ctrl_attribute_type and
object_type = x_object_type ;
insert into amw_fin_cert_ctrl_sum (
fin_certification_id ,
financial_statement_id ,
financial_item_id ,
account_group_id ,
natural_account_id ,
object_type ,
ctrl_attribute_type ,
ctrl_attr_code_1 ,
ineff_ctrl_attr_1 ,
total_ctrl_attr_1 ,
ctrl_attr_code_2 ,
ineff_ctrl_attr_2 ,
total_ctrl_attr_2 ,
ctrl_attr_code_3 ,
ineff_ctrl_attr_3 ,
total_ctrl_attr_3 ,
ctrl_attr_code_4 ,
ineff_ctrl_attr_4 ,
total_ctrl_attr_4 ,
ctrl_attr_code_5 ,
ineff_ctrl_attr_5 ,
total_ctrl_attr_5 ,
ctrl_attr_code_6 ,
ineff_ctrl_attr_6 ,
total_ctrl_attr_6 ,
ctrl_attr_code_7 ,
ineff_ctrl_attr_7 ,
total_ctrl_attr_7 ,
ctrl_attr_code_8 ,
ineff_ctrl_attr_8 ,
total_ctrl_attr_8 ,
ctrl_attr_code_9 ,
ineff_ctrl_attr_9 ,
total_ctrl_attr_9 ,
ctrl_attr_code_10 ,
ineff_ctrl_attr_10 ,
total_ctrl_attr_10 ,
ctrl_attr_code_11 ,
ineff_ctrl_attr_11 ,
total_ctrl_attr_11 ,
ctrl_attr_code_12 ,
ineff_ctrl_attr_12 ,
total_ctrl_attr_12 ,
ctrl_attr_code_13 ,
ineff_ctrl_attr_13 ,
total_ctrl_attr_13 ,
ctrl_attr_code_14 ,
ineff_ctrl_attr_14 ,
total_ctrl_attr_14 ,
ctrl_attr_code_15 ,
ineff_ctrl_attr_15 ,
total_ctrl_attr_15 ,
ctrl_attr_code_16 ,
ineff_ctrl_attr_16 ,
total_ctrl_attr_16 ,
ctrl_attr_code_17 ,
ineff_ctrl_attr_17 ,
total_ctrl_attr_17 ,
ctrl_attr_code_18 ,
ineff_ctrl_attr_18 ,
total_ctrl_attr_18 ,
ctrl_attr_code_19 ,
ineff_ctrl_attr_19 ,
total_ctrl_attr_19 ,
ctrl_attr_code_20 ,
ineff_ctrl_attr_20 ,
total_ctrl_attr_20 ,
ctrl_attr_code_21 ,
ineff_ctrl_attr_21 ,
total_ctrl_attr_21 ,
ctrl_attr_code_22 ,
ineff_ctrl_attr_22 ,
total_ctrl_attr_22 ,
ctrl_attr_code_23 ,
ineff_ctrl_attr_23 ,
total_ctrl_attr_23 ,
ctrl_attr_code_24 ,
ineff_ctrl_attr_24 ,
total_ctrl_attr_24 ,
ctrl_attr_code_25 ,
ineff_ctrl_attr_25 ,
total_ctrl_attr_25 ,
ctrl_attr_code_26 ,
ineff_ctrl_attr_26 ,
total_ctrl_attr_26 ,
ctrl_attr_code_27 ,
ineff_ctrl_attr_27 ,
total_ctrl_attr_27 ,
ctrl_attr_code_28 ,
ineff_ctrl_attr_28 ,
total_ctrl_attr_28 ,
ctrl_attr_code_29 ,
ineff_ctrl_attr_29 ,
total_ctrl_attr_29 ,
ctrl_attr_code_30 ,
ineff_ctrl_attr_30 ,
total_ctrl_attr_30 ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date ,
last_update_login ,
-- Removed security_group_id ,
object_version_number,
acc_assert_flag_1 ,
acc_assert_flag_2 ,
acc_assert_flag_3 ,
acc_assert_flag_4 ,
acc_assert_flag_5 ,
acc_assert_flag_6 ,
acc_assert_flag_7 ,
acc_assert_flag_8 ,
acc_assert_flag_9 ,
acc_assert_flag_10,
acc_assert_flag_11 ,
acc_assert_flag_12 ,
acc_assert_flag_13 ,
acc_assert_flag_14 ,
acc_assert_flag_15 ,
acc_assert_flag_16 ,
acc_assert_flag_17 ,
acc_assert_flag_18 ,
acc_assert_flag_19 ,
acc_assert_flag_20,
acc_assert_flag_21 ,
acc_assert_flag_22 ,
acc_assert_flag_23 ,
acc_assert_flag_24 ,
acc_assert_flag_25 ,
acc_assert_flag_26 ,
acc_assert_flag_27 ,
acc_assert_flag_28 ,
acc_assert_flag_29 ,
acc_assert_flag_30 ,
eval_ctrl_attr_1 ,
eval_ctrl_attr_2 ,
eval_ctrl_attr_3 ,
eval_ctrl_attr_4 ,
eval_ctrl_attr_5 ,
eval_ctrl_attr_6 ,
eval_ctrl_attr_7 ,
eval_ctrl_attr_8 ,
eval_ctrl_attr_9 ,
eval_ctrl_attr_10 ,
eval_ctrl_attr_11 ,
eval_ctrl_attr_12 ,
eval_ctrl_attr_13 ,
eval_ctrl_attr_14 ,
eval_ctrl_attr_15 ,
eval_ctrl_attr_16 ,
eval_ctrl_attr_17 ,
eval_ctrl_attr_18 ,
eval_ctrl_attr_19 ,
eval_ctrl_attr_20 ,
eval_ctrl_attr_21 ,
eval_ctrl_attr_22 ,
eval_ctrl_attr_23 ,
eval_ctrl_attr_24 ,
eval_ctrl_attr_25 ,
eval_ctrl_attr_26 ,
eval_ctrl_attr_27 ,
eval_ctrl_attr_28 ,
eval_ctrl_attr_29 ,
eval_ctrl_attr_30 ,
CONTROLS_EXIST_FLAG,
ineff_ctrl_prcnt_1 ,
ineff_ctrl_prcnt_2 ,
ineff_ctrl_prcnt_3 ,
ineff_ctrl_prcnt_4 ,
ineff_ctrl_prcnt_5 ,
ineff_ctrl_prcnt_6 ,
ineff_ctrl_prcnt_7 ,
ineff_ctrl_prcnt_8 ,
ineff_ctrl_prcnt_9 ,
ineff_ctrl_prcnt_10 ,
ineff_ctrl_prcnt_11 ,
ineff_ctrl_prcnt_12 ,
ineff_ctrl_prcnt_13 ,
ineff_ctrl_prcnt_14 ,
ineff_ctrl_prcnt_15 ,
ineff_ctrl_prcnt_16 ,
ineff_ctrl_prcnt_17 ,
ineff_ctrl_prcnt_18 ,
ineff_ctrl_prcnt_19 ,
ineff_ctrl_prcnt_20 ,
ineff_ctrl_prcnt_21 ,
ineff_ctrl_prcnt_22 ,
ineff_ctrl_prcnt_23 ,
ineff_ctrl_prcnt_24 ,
ineff_ctrl_prcnt_25 ,
ineff_ctrl_prcnt_26 ,
ineff_ctrl_prcnt_27 ,
ineff_ctrl_prcnt_28 ,
ineff_ctrl_prcnt_29 ,
ineff_ctrl_prcnt_30
)
values (
x_fin_certification_id ,
x_financial_statement_id ,
x_financial_item_id ,
x_account_group_id ,
x_natural_account_id ,
x_object_type ,
x_ctrl_attribute_type ,
x_ctrl_attr_code_1 ,
x_ineff_ctrl_attr_1 ,
x_total_ctrl_attr_1 ,
x_ctrl_attr_code_2 ,
x_ineff_ctrl_attr_2 ,
x_total_ctrl_attr_2 ,
x_ctrl_attr_code_3 ,
x_ineff_ctrl_attr_3 ,
x_total_ctrl_attr_3 ,
x_ctrl_attr_code_4 ,
x_ineff_ctrl_attr_4 ,
x_total_ctrl_attr_4 ,
x_ctrl_attr_code_5 ,
x_ineff_ctrl_attr_5 ,
x_total_ctrl_attr_5 ,
x_ctrl_attr_code_6 ,
x_ineff_ctrl_attr_6 ,
x_total_ctrl_attr_6 ,
x_ctrl_attr_code_7 ,
x_ineff_ctrl_attr_7 ,
x_total_ctrl_attr_7 ,
x_ctrl_attr_code_8 ,
x_ineff_ctrl_attr_8 ,
x_total_ctrl_attr_8 ,
x_ctrl_attr_code_9 ,
x_ineff_ctrl_attr_9 ,
x_total_ctrl_attr_9 ,
x_ctrl_attr_code_10 ,
x_ineff_ctrl_attr_10 ,
x_total_ctrl_attr_10 ,
x_ctrl_attr_code_11 ,
x_ineff_ctrl_attr_11 ,
x_total_ctrl_attr_11 ,
x_ctrl_attr_code_12 ,
x_ineff_ctrl_attr_12 ,
x_total_ctrl_attr_12 ,
x_ctrl_attr_code_13 ,
x_ineff_ctrl_attr_13 ,
x_total_ctrl_attr_13 ,
x_ctrl_attr_code_14 ,
x_ineff_ctrl_attr_14 ,
x_total_ctrl_attr_14 ,
x_ctrl_attr_code_15 ,
x_ineff_ctrl_attr_15 ,
x_total_ctrl_attr_15 ,
x_ctrl_attr_code_16 ,
x_ineff_ctrl_attr_16 ,
x_total_ctrl_attr_16 ,
x_ctrl_attr_code_17 ,
x_ineff_ctrl_attr_17 ,
x_total_ctrl_attr_17 ,
x_ctrl_attr_code_18 ,
x_ineff_ctrl_attr_18 ,
x_total_ctrl_attr_18 ,
x_ctrl_attr_code_19 ,
x_ineff_ctrl_attr_19 ,
x_total_ctrl_attr_19 ,
x_ctrl_attr_code_20 ,
x_ineff_ctrl_attr_20 ,
x_total_ctrl_attr_20 ,
x_ctrl_attr_code_21 ,
x_ineff_ctrl_attr_21 ,
x_total_ctrl_attr_21 ,
x_ctrl_attr_code_22 ,
x_ineff_ctrl_attr_22 ,
x_total_ctrl_attr_22 ,
x_ctrl_attr_code_23 ,
x_ineff_ctrl_attr_23 ,
x_total_ctrl_attr_23 ,
x_ctrl_attr_code_24 ,
x_ineff_ctrl_attr_24 ,
x_total_ctrl_attr_24 ,
x_ctrl_attr_code_25 ,
x_ineff_ctrl_attr_25 ,
x_total_ctrl_attr_25 ,
x_ctrl_attr_code_26 ,
x_ineff_ctrl_attr_26 ,
x_total_ctrl_attr_26 ,
x_ctrl_attr_code_27 ,
x_ineff_ctrl_attr_27 ,
x_total_ctrl_attr_27 ,
x_ctrl_attr_code_28 ,
x_ineff_ctrl_attr_28 ,
x_total_ctrl_attr_28 ,
x_ctrl_attr_code_29 ,
x_ineff_ctrl_attr_29 ,
x_total_ctrl_attr_29 ,
x_ctrl_attr_code_30 ,
x_ineff_ctrl_attr_30 ,
x_total_ctrl_attr_30 ,
x_created_by ,
x_creation_date ,
x_last_updated_by ,
x_last_update_date ,
x_last_update_login ,
-- Removed x_security_group_id ,
-- x_object_version_number,
m_object_version_number ,
x_acc_assert_flag1 ,
x_acc_assert_flag2 ,
x_acc_assert_flag3 ,
x_acc_assert_flag4 ,
x_acc_assert_flag5 ,
x_acc_assert_flag6 ,
x_acc_assert_flag7 ,
x_acc_assert_flag8 ,
x_acc_assert_flag9 ,
x_acc_assert_flag10,
x_acc_assert_flag11 ,
x_acc_assert_flag12 ,
x_acc_assert_flag13 ,
x_acc_assert_flag14 ,
x_acc_assert_flag15 ,
x_acc_assert_flag16 ,
x_acc_assert_flag17 ,
x_acc_assert_flag18 ,
x_acc_assert_flag19 ,
x_acc_assert_flag20,
x_acc_assert_flag21 ,
x_acc_assert_flag22 ,
x_acc_assert_flag23 ,
x_acc_assert_flag24 ,
x_acc_assert_flag25 ,
x_acc_assert_flag26 ,
x_acc_assert_flag27 ,
x_acc_assert_flag28 ,
x_acc_assert_flag29 ,
x_acc_assert_flag30,
x_eval_ctrl_attr_1 ,
x_eval_ctrl_attr_2 ,
x_eval_ctrl_attr_3 ,
x_eval_ctrl_attr_4 ,
x_eval_ctrl_attr_5 ,
x_eval_ctrl_attr_6 ,
x_eval_ctrl_attr_7 ,
x_eval_ctrl_attr_8 ,
x_eval_ctrl_attr_9 ,
x_eval_ctrl_attr_10 ,
x_eval_ctrl_attr_11 ,
x_eval_ctrl_attr_12 ,
x_eval_ctrl_attr_13 ,
x_eval_ctrl_attr_14 ,
x_eval_ctrl_attr_15 ,
x_eval_ctrl_attr_16 ,
x_eval_ctrl_attr_17 ,
x_eval_ctrl_attr_18 ,
x_eval_ctrl_attr_19 ,
x_eval_ctrl_attr_20 ,
x_eval_ctrl_attr_21 ,
x_eval_ctrl_attr_22 ,
x_eval_ctrl_attr_23 ,
x_eval_ctrl_attr_24 ,
x_eval_ctrl_attr_25 ,
x_eval_ctrl_attr_26 ,
x_eval_ctrl_attr_27 ,
x_eval_ctrl_attr_28 ,
x_eval_ctrl_attr_29 ,
x_eval_ctrl_attr_30 ,
x_display_flag ,
round((x_ineff_ctrl_attr_1 / decode(x_total_ctrl_attr_1,null,1,0,1,x_total_ctrl_attr_1) ) * 100,0),
round((x_ineff_ctrl_attr_2 / decode(x_total_ctrl_attr_2,null,1,0,1,x_total_ctrl_attr_2) ) * 100,0),
round((x_ineff_ctrl_attr_3 / decode(x_total_ctrl_attr_3,null,1,0,1,x_total_ctrl_attr_3) ) * 100,0),
round((x_ineff_ctrl_attr_4 / decode(x_total_ctrl_attr_4,null,1,0,1,x_total_ctrl_attr_4) ) * 100,0),
round((x_ineff_ctrl_attr_5 / decode(x_total_ctrl_attr_5,null,1,0,1,x_total_ctrl_attr_5) ) * 100,0),
round((x_ineff_ctrl_attr_6 / decode(x_total_ctrl_attr_6,null,1,0,1,x_total_ctrl_attr_6) ) * 100,0),
round((x_ineff_ctrl_attr_7 / decode(x_total_ctrl_attr_7,null,1,0,1,x_total_ctrl_attr_7) ) * 100,0),
round((x_ineff_ctrl_attr_8 / decode(x_total_ctrl_attr_8,null,1,0,1,x_total_ctrl_attr_8) ) * 100,0),
round((x_ineff_ctrl_attr_9 / decode(x_total_ctrl_attr_9,null,1,0,1,x_total_ctrl_attr_9) ) * 100,0),
round((x_ineff_ctrl_attr_10 / decode(x_total_ctrl_attr_10,null,1,0,1,x_total_ctrl_attr_10) ) * 100,0),
round((x_ineff_ctrl_attr_11 / decode(x_total_ctrl_attr_11,null,1,0,1,x_total_ctrl_attr_11) ) * 100,0),
round((x_ineff_ctrl_attr_12 / decode(x_total_ctrl_attr_12,null,1,0,1,x_total_ctrl_attr_12) ) * 100,0),
round((x_ineff_ctrl_attr_13 / decode(x_total_ctrl_attr_13,null,1,0,1,x_total_ctrl_attr_13) ) * 100,0),
round((x_ineff_ctrl_attr_14 / decode(x_total_ctrl_attr_14,null,1,0,1,x_total_ctrl_attr_14) ) * 100,0),
round((x_ineff_ctrl_attr_15 / decode(x_total_ctrl_attr_15,null,1,0,1,x_total_ctrl_attr_15) ) * 100,0),
round((x_ineff_ctrl_attr_16 / decode(x_total_ctrl_attr_16,null,1,0,1,x_total_ctrl_attr_16) ) * 100,0),
round((x_ineff_ctrl_attr_17 / decode(x_total_ctrl_attr_17,null,1,0,1,x_total_ctrl_attr_17) ) * 100,0),
round((x_ineff_ctrl_attr_18 / decode(x_total_ctrl_attr_18,null,1,0,1,x_total_ctrl_attr_18) ) * 100,0),
round((x_ineff_ctrl_attr_19 / decode(x_total_ctrl_attr_19,null,1,0,1,x_total_ctrl_attr_19) ) * 100,0),
round((x_ineff_ctrl_attr_20 / decode(x_total_ctrl_attr_20,null,1,0,1,x_total_ctrl_attr_20) ) * 100,0),
round((x_ineff_ctrl_attr_21 / decode(x_total_ctrl_attr_21,null,1,0,1,x_total_ctrl_attr_21) ) * 100,0),
round((x_ineff_ctrl_attr_22 / decode(x_total_ctrl_attr_22,null,1,0,1,x_total_ctrl_attr_22) ) * 100,0),
round((x_ineff_ctrl_attr_23 / decode(x_total_ctrl_attr_23,null,1,0,1,x_total_ctrl_attr_23) ) * 100,0),
round((x_ineff_ctrl_attr_24 / decode(x_total_ctrl_attr_24,null,1,0,1,x_total_ctrl_attr_24) ) * 100,0),
round((x_ineff_ctrl_attr_25 / decode(x_total_ctrl_attr_25,null,1,0,1,x_total_ctrl_attr_25) ) * 100,0),
round((x_ineff_ctrl_attr_26 / decode(x_total_ctrl_attr_26,null,1,0,1,x_total_ctrl_attr_26) ) * 100,0),
round((x_ineff_ctrl_attr_27 / decode(x_total_ctrl_attr_27,null,1,0,1,x_total_ctrl_attr_27) ) * 100,0),
round((x_ineff_ctrl_attr_28 / decode(x_total_ctrl_attr_28,null,1,0,1,x_total_ctrl_attr_28) ) * 100,0),
round((x_ineff_ctrl_attr_29 / decode(x_total_ctrl_attr_29,null,1,0,1,x_total_ctrl_attr_29) ) * 100,0),
round((x_ineff_ctrl_attr_30 / decode(x_total_ctrl_attr_30,null,1,0,1,x_total_ctrl_attr_30) ) * 100,0)
);
fnd_file.put_line(fnd_file.LOG, 'Duplicate row insert');
end INSERT_ROW;
PROCEDURE Update_item_ctrl_components
(P_CERTIFICATION_ID number ,
P_FINANCIAL_STATEMENT_ID number,
P_STATEMENT_GROUP_ID number,
P_FINANCIAL_ITEM_ID number,
P_CONTROL_ID number ,
P_ORG_ID number ,
P_CHANGE_FLAG VARCHAR2,
P_NEW_FLAG VARCHAR2) is
begin
declare
cursor existing_code(par_type varchar2)
is
select
ctrl_attr_code_1,
ctrl_attr_code_2,
ctrl_attr_code_3,
ctrl_attr_code_4,
ctrl_attr_code_5,
ctrl_attr_code_6,
ctrl_attr_code_7,
ctrl_attr_code_8,
ctrl_attr_code_9,
ctrl_attr_code_10,
ctrl_attr_code_11,
ctrl_attr_code_12,
ctrl_attr_code_13,
ctrl_attr_code_14,
ctrl_attr_code_15,
ctrl_attr_code_16,
ctrl_attr_code_17,
ctrl_attr_code_18,
ctrl_attr_code_19,
ctrl_attr_code_20,
ctrl_attr_code_21,
ctrl_attr_code_22,
ctrl_attr_code_23,
ctrl_attr_code_24,
ctrl_attr_code_25,
ctrl_attr_code_26,
ctrl_attr_code_27,
ctrl_attr_code_28,
ctrl_attr_code_29,
ctrl_attr_code_30
from
amw_fin_cert_ctrl_sum
where
fin_certification_id = P_CERTIFICATION_ID and
ctrl_attribute_type = par_type and
ROWNUM <2;
select
distinct
comp.COMPONENT_CODE
from
amw.amw_fin_item_acc_ctrl ctrl,
amw_assessment_components comp
where
ctrl.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID and
ctrl.OBJECT_TYPE = 'FINANCIAL ITEM' and
ctrl.FINANCIAL_ITEM_ID= P_FINANCIAL_ITEM_ID and
ctrl.CONTROL_REV_ID =comp.OBJECT_ID and
comp.OBJECT_TYPE ='CONTROL' and
ctrl.ORGANIZATION_ID = P_ORG_ID and
ctrl.CONTROL_ID = P_CONTROL_ID ;
amw_fin_coso_views_pvt.UPDATE_FIN_ITEM_ROW(
x_fin_certification_id => P_CERTIFICATION_ID ,
x_financial_statement_id => P_FINANCIAL_STATEMENT_ID ,
x_financial_item_id => P_FINANCIAL_ITEM_ID ,
x_account_group_id => null ,
x_natural_account_id => null,
x_object_type => m_OBJECT_TYPE ,
x_ctrl_attribute_type => m_ctrl_attribute_type ,
x_ineff_ctrl_attr_1 => m_ineff_control(1) ,
x_ineff_ctrl_attr_2 => m_ineff_control(2) ,
x_ineff_ctrl_attr_3 => m_ineff_control(3) ,
x_ineff_ctrl_attr_4 => m_ineff_control(4) ,
x_ineff_ctrl_attr_5 => m_ineff_control(5) ,
x_ineff_ctrl_attr_6 => m_ineff_control(6) ,
x_ineff_ctrl_attr_7 => m_ineff_control(7) ,
x_ineff_ctrl_attr_8 => m_ineff_control(8) ,
x_ineff_ctrl_attr_9 => m_ineff_control(9) ,
x_ineff_ctrl_attr_10 => m_ineff_control(10) ,
x_ineff_ctrl_attr_11 => m_ineff_control(11) ,
x_ineff_ctrl_attr_12 => m_ineff_control(12) ,
x_ineff_ctrl_attr_13 => m_ineff_control(13) ,
x_ineff_ctrl_attr_14 => m_ineff_control(14) ,
x_ineff_ctrl_attr_15 => m_ineff_control(15) ,
x_ineff_ctrl_attr_16 => m_ineff_control(16) ,
x_ineff_ctrl_attr_17 => m_ineff_control(17) ,
x_ineff_ctrl_attr_18 => m_ineff_control(18) ,
x_ineff_ctrl_attr_19 => m_ineff_control(19) ,
x_ineff_ctrl_attr_20 => m_ineff_control(20) ,
x_ineff_ctrl_attr_21 => m_ineff_control(21) ,
x_ineff_ctrl_attr_22 => m_ineff_control(22) ,
x_ineff_ctrl_attr_23 => m_ineff_control(23) ,
x_ineff_ctrl_attr_24 => m_ineff_control(24) ,
x_ineff_ctrl_attr_25 => m_ineff_control(25) ,
x_ineff_ctrl_attr_26 => m_ineff_control(26) ,
x_ineff_ctrl_attr_27 => m_ineff_control(27) ,
x_ineff_ctrl_attr_28 => m_ineff_control(28) ,
x_ineff_ctrl_attr_29 => m_ineff_control(29) ,
x_ineff_ctrl_attr_30 => m_ineff_control(30) ,
x_last_updated_by => g_user_id ,
x_last_update_date => SYSDATE ,
x_last_update_login => g_login_id,
x_eval_ctrl_attr_1 => m_add_to_eval_ctrls(1),
x_eval_ctrl_attr_2 => m_add_to_eval_ctrls(2),
x_eval_ctrl_attr_3 => m_add_to_eval_ctrls(3),
x_eval_ctrl_attr_4 => m_add_to_eval_ctrls(4),
x_eval_ctrl_attr_5 => m_add_to_eval_ctrls(5),
x_eval_ctrl_attr_6 => m_add_to_eval_ctrls(6),
x_eval_ctrl_attr_7 => m_add_to_eval_ctrls(7),
x_eval_ctrl_attr_8 => m_add_to_eval_ctrls(8),
x_eval_ctrl_attr_9 => m_add_to_eval_ctrls(9),
x_eval_ctrl_attr_10 => m_add_to_eval_ctrls(10),
x_eval_ctrl_attr_11 => m_add_to_eval_ctrls(11),
x_eval_ctrl_attr_12 => m_add_to_eval_ctrls(12),
x_eval_ctrl_attr_13 => m_add_to_eval_ctrls(13),
x_eval_ctrl_attr_14 => m_add_to_eval_ctrls(14),
x_eval_ctrl_attr_15 => m_add_to_eval_ctrls(15),
x_eval_ctrl_attr_16 => m_add_to_eval_ctrls(16),
x_eval_ctrl_attr_17 => m_add_to_eval_ctrls(17),
x_eval_ctrl_attr_18 => m_add_to_eval_ctrls(18),
x_eval_ctrl_attr_19 => m_add_to_eval_ctrls(19),
x_eval_ctrl_attr_20 => m_add_to_eval_ctrls(20),
x_eval_ctrl_attr_21 => m_add_to_eval_ctrls(21),
x_eval_ctrl_attr_22 => m_add_to_eval_ctrls(22),
x_eval_ctrl_attr_23 => m_add_to_eval_ctrls(23),
x_eval_ctrl_attr_24 => m_add_to_eval_ctrls(24),
x_eval_ctrl_attr_25 => m_add_to_eval_ctrls(25),
x_eval_ctrl_attr_26 => m_add_to_eval_ctrls(26),
x_eval_ctrl_attr_27 => m_add_to_eval_ctrls(27),
x_eval_ctrl_attr_28 => m_add_to_eval_ctrls(28),
x_eval_ctrl_attr_29 => m_add_to_eval_ctrls(29),
x_eval_ctrl_attr_30 => m_add_to_eval_ctrls(30));
fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in Update_item_ctrl_components'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in Update_item_ctrl_components'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
end ; --Update_item_ctrl_components
PROCEDURE Update_acc_ctrl_components
(P_CERTIFICATION_ID number ,
P_ACCOUNT_GROUP_ID number ,
P_ACCOUNT_ID number ,
P_CONTROL_ID number ,
P_ORG_ID number ,
P_CHANGE_FLAG VARCHAR2,
P_NEW_FLAG VARCHAR2) is
begin
declare
cursor existing_code(par_type varchar2)
is
select
ctrl_attr_code_1,
ctrl_attr_code_2,
ctrl_attr_code_3,
ctrl_attr_code_4,
ctrl_attr_code_5,
ctrl_attr_code_6,
ctrl_attr_code_7,
ctrl_attr_code_8,
ctrl_attr_code_9,
ctrl_attr_code_10,
ctrl_attr_code_11,
ctrl_attr_code_12,
ctrl_attr_code_13,
ctrl_attr_code_14,
ctrl_attr_code_15,
ctrl_attr_code_16,
ctrl_attr_code_17,
ctrl_attr_code_18,
ctrl_attr_code_19,
ctrl_attr_code_20,
ctrl_attr_code_21,
ctrl_attr_code_22,
ctrl_attr_code_23,
ctrl_attr_code_24,
ctrl_attr_code_25,
ctrl_attr_code_26,
ctrl_attr_code_27,
ctrl_attr_code_28,
ctrl_attr_code_29,
ctrl_attr_code_30,
ineff_ctrl_attr_1,
ineff_ctrl_attr_2,
ineff_ctrl_attr_3,
ineff_ctrl_attr_4,
ineff_ctrl_attr_5,
ineff_ctrl_attr_6,
ineff_ctrl_attr_7,
ineff_ctrl_attr_8,
ineff_ctrl_attr_9,
ineff_ctrl_attr_10,
ineff_ctrl_attr_11,
ineff_ctrl_attr_12,
ineff_ctrl_attr_13,
ineff_ctrl_attr_14,
ineff_ctrl_attr_15,
ineff_ctrl_attr_16,
ineff_ctrl_attr_17,
ineff_ctrl_attr_18,
ineff_ctrl_attr_19,
ineff_ctrl_attr_20,
ineff_ctrl_attr_21,
ineff_ctrl_attr_22,
ineff_ctrl_attr_23,
ineff_ctrl_attr_24,
ineff_ctrl_attr_25,
ineff_ctrl_attr_26,
ineff_ctrl_attr_27,
ineff_ctrl_attr_28,
ineff_ctrl_attr_29,
ineff_ctrl_attr_30
from
amw_fin_cert_ctrl_sum
where
fin_certification_id = P_CERTIFICATION_ID and
ctrl_attribute_type = par_type and
ROWNUM <2
AND account_group_id = P_ACCOUNT_GROUP_ID and
natural_account_id = P_ACCOUNT_ID and
object_type = 'ACCOUNT' ;
is select
distinct
comp.COMPONENT_CODE
from
amw.amw_fin_item_acc_ctrl ctrl,
amw_assessment_components comp
where
ctrl.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID
and
ctrl.OBJECT_TYPE = 'ACCOUNT' and
ctrl.ACCOUNT_GROUP_ID= P_ACCOUNT_GROUP_ID AND
ctrl.NATURAL_ACCOUNT_ID= P_ACCOUNT_ID and
ctrl.CONTROL_REV_ID =comp.OBJECT_ID and
comp.OBJECT_TYPE ='CONTROL' and
ctrl.ORGANIZATION_ID = P_ORG_ID and
ctrl.CONTROL_ID = P_CONTROL_ID ;
amw_fin_coso_views_pvt.UPDATE_FIN_ACC_ROW(
x_fin_certification_id => P_CERTIFICATION_ID ,
x_financial_statement_id => NULL ,
x_financial_item_id => NULL ,
x_account_group_id => P_ACCOUNT_GROUP_ID,
x_natural_account_id => P_ACCOUNT_ID,
x_object_type => m_OBJECT_TYPE ,
x_ctrl_attribute_type => m_ctrl_attribute_type ,
x_ineff_ctrl_attr_1 => m_ineff_control(1) ,
x_ineff_ctrl_attr_2 => m_ineff_control(2) ,
x_ineff_ctrl_attr_3 => m_ineff_control(3) ,
x_ineff_ctrl_attr_4 => m_ineff_control(4) ,
x_ineff_ctrl_attr_5 => m_ineff_control(5) ,
x_ineff_ctrl_attr_6 => m_ineff_control(6) ,
x_ineff_ctrl_attr_7 => m_ineff_control(7) ,
x_ineff_ctrl_attr_8 => m_ineff_control(8) ,
x_ineff_ctrl_attr_9 => m_ineff_control(9) ,
x_ineff_ctrl_attr_10 => m_ineff_control(10) ,
x_ineff_ctrl_attr_11 => m_ineff_control(11) ,
x_ineff_ctrl_attr_12 => m_ineff_control(12) ,
x_ineff_ctrl_attr_13 => m_ineff_control(13) ,
x_ineff_ctrl_attr_14 => m_ineff_control(14) ,
x_ineff_ctrl_attr_15 => m_ineff_control(15) ,
x_ineff_ctrl_attr_16 => m_ineff_control(16) ,
x_ineff_ctrl_attr_17 => m_ineff_control(17) ,
x_ineff_ctrl_attr_18 => m_ineff_control(18) ,
x_ineff_ctrl_attr_19 => m_ineff_control(19) ,
x_ineff_ctrl_attr_20 => m_ineff_control(20) ,
x_ineff_ctrl_attr_21 => m_ineff_control(21) ,
x_ineff_ctrl_attr_22 => m_ineff_control(22) ,
x_ineff_ctrl_attr_23 => m_ineff_control(23) ,
x_ineff_ctrl_attr_24 => m_ineff_control(24) ,
x_ineff_ctrl_attr_25 => m_ineff_control(25) ,
x_ineff_ctrl_attr_26 => m_ineff_control(26) ,
x_ineff_ctrl_attr_27 => m_ineff_control(27) ,
x_ineff_ctrl_attr_28 => m_ineff_control(28) ,
x_ineff_ctrl_attr_29 => m_ineff_control(29) ,
x_ineff_ctrl_attr_30 => m_ineff_control(30) ,
x_last_updated_by => g_user_id ,
x_last_update_date => SYSDATE ,
x_last_update_login => g_login_id,
x_eval_ctrl_attr_1 => m_add_to_eval_ctrls(1),
x_eval_ctrl_attr_2 => m_add_to_eval_ctrls(2),
x_eval_ctrl_attr_3 => m_add_to_eval_ctrls(3),
x_eval_ctrl_attr_4 => m_add_to_eval_ctrls(4),
x_eval_ctrl_attr_5 => m_add_to_eval_ctrls(5),
x_eval_ctrl_attr_6 => m_add_to_eval_ctrls(6),
x_eval_ctrl_attr_7 => m_add_to_eval_ctrls(7),
x_eval_ctrl_attr_8 => m_add_to_eval_ctrls(8),
x_eval_ctrl_attr_9 => m_add_to_eval_ctrls(9),
x_eval_ctrl_attr_10 => m_add_to_eval_ctrls(10),
x_eval_ctrl_attr_11 => m_add_to_eval_ctrls(11),
x_eval_ctrl_attr_12 => m_add_to_eval_ctrls(12),
x_eval_ctrl_attr_13 => m_add_to_eval_ctrls(13),
x_eval_ctrl_attr_14 => m_add_to_eval_ctrls(14),
x_eval_ctrl_attr_15 => m_add_to_eval_ctrls(15),
x_eval_ctrl_attr_16 => m_add_to_eval_ctrls(16),
x_eval_ctrl_attr_17 => m_add_to_eval_ctrls(17),
x_eval_ctrl_attr_18 => m_add_to_eval_ctrls(18),
x_eval_ctrl_attr_19 => m_add_to_eval_ctrls(19),
x_eval_ctrl_attr_20 => m_add_to_eval_ctrls(20),
x_eval_ctrl_attr_21 => m_add_to_eval_ctrls(21),
x_eval_ctrl_attr_22 => m_add_to_eval_ctrls(22),
x_eval_ctrl_attr_23 => m_add_to_eval_ctrls(23),
x_eval_ctrl_attr_24 => m_add_to_eval_ctrls(24),
x_eval_ctrl_attr_25 => m_add_to_eval_ctrls(25),
x_eval_ctrl_attr_26 => m_add_to_eval_ctrls(26),
x_eval_ctrl_attr_27 => m_add_to_eval_ctrls(27),
x_eval_ctrl_attr_28 => m_add_to_eval_ctrls(28),
x_eval_ctrl_attr_29 => m_add_to_eval_ctrls(29),
x_eval_ctrl_attr_30 => m_add_to_eval_ctrls(30)
);
fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in Update_acc_ctrl_components'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in Update_acc_ctrl_components'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
end ; --Update_acc_ctrl_components
PROCEDURE Update_item_ctrl_objectives
(P_CERTIFICATION_ID number ,
P_FINANCIAL_STATEMENT_ID number,
P_STATEMENT_GROUP_ID number,
P_FINANCIAL_ITEM_ID number,
P_CONTROL_ID number ,
P_ORG_ID number ,
P_CHANGE_FLAG VARCHAR2,
P_NEW_FLAG VARCHAR2) is
begin
declare
cursor existing_code(par_type varchar2)
is
select
ctrl_attr_code_1,
ctrl_attr_code_2,
ctrl_attr_code_3,
ctrl_attr_code_4,
ctrl_attr_code_5,
ctrl_attr_code_6,
ctrl_attr_code_7,
ctrl_attr_code_8,
ctrl_attr_code_9,
ctrl_attr_code_10,
ctrl_attr_code_11,
ctrl_attr_code_12,
ctrl_attr_code_13,
ctrl_attr_code_14,
ctrl_attr_code_15,
ctrl_attr_code_16,
ctrl_attr_code_17,
ctrl_attr_code_18,
ctrl_attr_code_19,
ctrl_attr_code_20,
ctrl_attr_code_21,
ctrl_attr_code_22,
ctrl_attr_code_23,
ctrl_attr_code_24,
ctrl_attr_code_25,
ctrl_attr_code_26,
ctrl_attr_code_27,
ctrl_attr_code_28,
ctrl_attr_code_29,
ctrl_attr_code_30,
ineff_ctrl_attr_1,
ineff_ctrl_attr_2,
ineff_ctrl_attr_3,
ineff_ctrl_attr_4,
ineff_ctrl_attr_5,
ineff_ctrl_attr_6,
ineff_ctrl_attr_7,
ineff_ctrl_attr_8,
ineff_ctrl_attr_9,
ineff_ctrl_attr_10,
ineff_ctrl_attr_11,
ineff_ctrl_attr_12,
ineff_ctrl_attr_13,
ineff_ctrl_attr_14,
ineff_ctrl_attr_15,
ineff_ctrl_attr_16,
ineff_ctrl_attr_17,
ineff_ctrl_attr_18,
ineff_ctrl_attr_19,
ineff_ctrl_attr_20,
ineff_ctrl_attr_21,
ineff_ctrl_attr_22,
ineff_ctrl_attr_23,
ineff_ctrl_attr_24,
ineff_ctrl_attr_25,
ineff_ctrl_attr_26,
ineff_ctrl_attr_27,
ineff_ctrl_attr_28,
ineff_ctrl_attr_29,
ineff_ctrl_attr_30
from
amw_fin_cert_ctrl_sum
where
fin_certification_id = P_CERTIFICATION_ID and
ctrl_attribute_type = par_type and
ROWNUM <2;
select
distinct
comp.OBJECTIVE_CODE
from
amw.amw_fin_item_acc_ctrl ctrl,
amw_control_objectives comp
where
ctrl.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID
and
ctrl.OBJECT_TYPE = 'FINANCIAL ITEM' and
ctrl.FINANCIAL_ITEM_ID= P_FINANCIAL_ITEM_ID
and
ctrl.CONTROL_REV_ID =comp.CONTROL_REV_ID and
ctrl.ORGANIZATION_ID = P_ORG_ID
and
ctrl.CONTROL_ID = P_CONTROL_ID ;
amw_fin_coso_views_pvt.UPDATE_FIN_ITEM_ROW(
x_fin_certification_id => P_CERTIFICATION_ID ,
x_financial_statement_id => P_FINANCIAL_STATEMENT_ID ,
x_financial_item_id => P_FINANCIAL_ITEM_ID ,
x_account_group_id => null ,
x_natural_account_id => null,
x_object_type => m_OBJECT_TYPE ,
x_ctrl_attribute_type => m_ctrl_attribute_type ,
x_ineff_ctrl_attr_1 => m_ineff_control(1) ,
x_ineff_ctrl_attr_2 => m_ineff_control(2) ,
x_ineff_ctrl_attr_3 => m_ineff_control(3) ,
x_ineff_ctrl_attr_4 => m_ineff_control(4) ,
x_ineff_ctrl_attr_5 => m_ineff_control(5) ,
x_ineff_ctrl_attr_6 => m_ineff_control(6) ,
x_ineff_ctrl_attr_7 => m_ineff_control(7) ,
x_ineff_ctrl_attr_8 => m_ineff_control(8) ,
x_ineff_ctrl_attr_9 => m_ineff_control(9) ,
x_ineff_ctrl_attr_10 => m_ineff_control(10) ,
x_ineff_ctrl_attr_11 => m_ineff_control(11) ,
x_ineff_ctrl_attr_12 => m_ineff_control(12) ,
x_ineff_ctrl_attr_13 => m_ineff_control(13) ,
x_ineff_ctrl_attr_14 => m_ineff_control(14) ,
x_ineff_ctrl_attr_15 => m_ineff_control(15) ,
x_ineff_ctrl_attr_16 => m_ineff_control(16) ,
x_ineff_ctrl_attr_17 => m_ineff_control(17) ,
x_ineff_ctrl_attr_18 => m_ineff_control(18) ,
x_ineff_ctrl_attr_19 => m_ineff_control(19) ,
x_ineff_ctrl_attr_20 => m_ineff_control(20) ,
x_ineff_ctrl_attr_21 => m_ineff_control(21) ,
x_ineff_ctrl_attr_22 => m_ineff_control(22) ,
x_ineff_ctrl_attr_23 => m_ineff_control(23) ,
x_ineff_ctrl_attr_24 => m_ineff_control(24) ,
x_ineff_ctrl_attr_25 => m_ineff_control(25) ,
x_ineff_ctrl_attr_26 => m_ineff_control(26) ,
x_ineff_ctrl_attr_27 => m_ineff_control(27) ,
x_ineff_ctrl_attr_28 => m_ineff_control(28) ,
x_ineff_ctrl_attr_29 => m_ineff_control(29) ,
x_ineff_ctrl_attr_30 => m_ineff_control(30) ,
x_last_updated_by => g_user_id ,
x_last_update_date => SYSDATE ,
x_last_update_login => g_login_id,
x_eval_ctrl_attr_1 => m_add_to_eval_ctrls(1),
x_eval_ctrl_attr_2 => m_add_to_eval_ctrls(2),
x_eval_ctrl_attr_3 => m_add_to_eval_ctrls(3),
x_eval_ctrl_attr_4 => m_add_to_eval_ctrls(4),
x_eval_ctrl_attr_5 => m_add_to_eval_ctrls(5),
x_eval_ctrl_attr_6 => m_add_to_eval_ctrls(6),
x_eval_ctrl_attr_7 => m_add_to_eval_ctrls(7),
x_eval_ctrl_attr_8 => m_add_to_eval_ctrls(8),
x_eval_ctrl_attr_9 => m_add_to_eval_ctrls(9),
x_eval_ctrl_attr_10 => m_add_to_eval_ctrls(10),
x_eval_ctrl_attr_11 => m_add_to_eval_ctrls(11),
x_eval_ctrl_attr_12 => m_add_to_eval_ctrls(12),
x_eval_ctrl_attr_13 => m_add_to_eval_ctrls(13),
x_eval_ctrl_attr_14 => m_add_to_eval_ctrls(14),
x_eval_ctrl_attr_15 => m_add_to_eval_ctrls(15),
x_eval_ctrl_attr_16 => m_add_to_eval_ctrls(16),
x_eval_ctrl_attr_17 => m_add_to_eval_ctrls(17),
x_eval_ctrl_attr_18 => m_add_to_eval_ctrls(18),
x_eval_ctrl_attr_19 => m_add_to_eval_ctrls(19),
x_eval_ctrl_attr_20 => m_add_to_eval_ctrls(20),
x_eval_ctrl_attr_21 => m_add_to_eval_ctrls(21),
x_eval_ctrl_attr_22 => m_add_to_eval_ctrls(22),
x_eval_ctrl_attr_23 => m_add_to_eval_ctrls(23),
x_eval_ctrl_attr_24 => m_add_to_eval_ctrls(24),
x_eval_ctrl_attr_25 => m_add_to_eval_ctrls(25),
x_eval_ctrl_attr_26 => m_add_to_eval_ctrls(26),
x_eval_ctrl_attr_27 => m_add_to_eval_ctrls(27),
x_eval_ctrl_attr_28 => m_add_to_eval_ctrls(28),
x_eval_ctrl_attr_29 => m_add_to_eval_ctrls(29),
x_eval_ctrl_attr_30 => m_add_to_eval_ctrls(30));
fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in Update_item_ctrl_components'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in Update_item_ctrl_components'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
end ; --Update_item_ctrl_objectives
PROCEDURE Update_acc_ctrl_objectives
(P_CERTIFICATION_ID number ,
P_ACCOUNT_GROUP_ID number ,
P_ACCOUNT_ID number ,
P_CONTROL_ID number ,
P_ORG_ID number ,
P_CHANGE_FLAG VARCHAR2,
P_NEW_FLAG VARCHAR2) is
begin
declare
cursor existing_code(par_type varchar2)
is
select
ctrl_attr_code_1,
ctrl_attr_code_2,
ctrl_attr_code_3,
ctrl_attr_code_4,
ctrl_attr_code_5,
ctrl_attr_code_6,
ctrl_attr_code_7,
ctrl_attr_code_8,
ctrl_attr_code_9,
ctrl_attr_code_10,
ctrl_attr_code_11,
ctrl_attr_code_12,
ctrl_attr_code_13,
ctrl_attr_code_14,
ctrl_attr_code_15,
ctrl_attr_code_16,
ctrl_attr_code_17,
ctrl_attr_code_18,
ctrl_attr_code_19,
ctrl_attr_code_20,
ctrl_attr_code_21,
ctrl_attr_code_22,
ctrl_attr_code_23,
ctrl_attr_code_24,
ctrl_attr_code_25,
ctrl_attr_code_26,
ctrl_attr_code_27,
ctrl_attr_code_28,
ctrl_attr_code_29,
ctrl_attr_code_30,
ineff_ctrl_attr_1,
ineff_ctrl_attr_2,
ineff_ctrl_attr_3,
ineff_ctrl_attr_4,
ineff_ctrl_attr_5,
ineff_ctrl_attr_6,
ineff_ctrl_attr_7,
ineff_ctrl_attr_8,
ineff_ctrl_attr_9,
ineff_ctrl_attr_10,
ineff_ctrl_attr_11,
ineff_ctrl_attr_12,
ineff_ctrl_attr_13,
ineff_ctrl_attr_14,
ineff_ctrl_attr_15,
ineff_ctrl_attr_16,
ineff_ctrl_attr_17,
ineff_ctrl_attr_18,
ineff_ctrl_attr_19,
ineff_ctrl_attr_20,
ineff_ctrl_attr_21,
ineff_ctrl_attr_22,
ineff_ctrl_attr_23,
ineff_ctrl_attr_24,
ineff_ctrl_attr_25,
ineff_ctrl_attr_26,
ineff_ctrl_attr_27,
ineff_ctrl_attr_28,
ineff_ctrl_attr_29,
ineff_ctrl_attr_30
from
amw_fin_cert_ctrl_sum
where
fin_certification_id = P_CERTIFICATION_ID and
ctrl_attribute_type = par_type and
ROWNUM <2
AND account_group_id = P_ACCOUNT_GROUP_ID and
natural_account_id = P_ACCOUNT_ID and
object_type = 'ACCOUNT' ;
select
distinct
comp.OBJECTIVE_CODE
from
amw.amw_fin_item_acc_ctrl ctrl,
amw_control_objectives comp
where
ctrl.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID
and
ctrl.OBJECT_TYPE = 'ACCOUNT' and
ctrl.ACCOUNT_GROUP_ID= P_ACCOUNT_GROUP_ID
AND
ctrl.NATURAL_ACCOUNT_ID = P_ACCOUNT_ID
and
ctrl.CONTROL_REV_ID =comp.CONTROL_REV_ID and
ctrl.ORGANIZATION_ID = P_ORG_ID
and
ctrl.CONTROL_ID = P_CONTROL_ID ;
amw_fin_coso_views_pvt.UPDATE_FIN_ACC_ROW(
x_fin_certification_id => P_CERTIFICATION_ID ,
x_financial_statement_id => NULL ,
x_financial_item_id => NULL ,
x_account_group_id => P_ACCOUNT_GROUP_ID,
x_natural_account_id => P_ACCOUNT_ID,
x_object_type => m_OBJECT_TYPE ,
x_ctrl_attribute_type => m_ctrl_attribute_type ,
x_ineff_ctrl_attr_1 => m_ineff_control(1) ,
x_ineff_ctrl_attr_2 => m_ineff_control(2) ,
x_ineff_ctrl_attr_3 => m_ineff_control(3) ,
x_ineff_ctrl_attr_4 => m_ineff_control(4) ,
x_ineff_ctrl_attr_5 => m_ineff_control(5) ,
x_ineff_ctrl_attr_6 => m_ineff_control(6) ,
x_ineff_ctrl_attr_7 => m_ineff_control(7) ,
x_ineff_ctrl_attr_8 => m_ineff_control(8) ,
x_ineff_ctrl_attr_9 => m_ineff_control(9) ,
x_ineff_ctrl_attr_10 => m_ineff_control(10) ,
x_ineff_ctrl_attr_11 => m_ineff_control(11) ,
x_ineff_ctrl_attr_12 => m_ineff_control(12) ,
x_ineff_ctrl_attr_13 => m_ineff_control(13) ,
x_ineff_ctrl_attr_14 => m_ineff_control(14) ,
x_ineff_ctrl_attr_15 => m_ineff_control(15) ,
x_ineff_ctrl_attr_16 => m_ineff_control(16) ,
x_ineff_ctrl_attr_17 => m_ineff_control(17) ,
x_ineff_ctrl_attr_18 => m_ineff_control(18) ,
x_ineff_ctrl_attr_19 => m_ineff_control(19) ,
x_ineff_ctrl_attr_20 => m_ineff_control(20) ,
x_ineff_ctrl_attr_21 => m_ineff_control(21) ,
x_ineff_ctrl_attr_22 => m_ineff_control(22) ,
x_ineff_ctrl_attr_23 => m_ineff_control(23) ,
x_ineff_ctrl_attr_24 => m_ineff_control(24) ,
x_ineff_ctrl_attr_25 => m_ineff_control(25) ,
x_ineff_ctrl_attr_26 => m_ineff_control(26) ,
x_ineff_ctrl_attr_27 => m_ineff_control(27) ,
x_ineff_ctrl_attr_28 => m_ineff_control(28) ,
x_ineff_ctrl_attr_29 => m_ineff_control(29) ,
x_ineff_ctrl_attr_30 => m_ineff_control(30) ,
x_last_updated_by => g_user_id ,
x_last_update_date => SYSDATE ,
x_last_update_login => g_login_id,
x_eval_ctrl_attr_1 => m_add_to_eval_ctrls(1),
x_eval_ctrl_attr_2 => m_add_to_eval_ctrls(2),
x_eval_ctrl_attr_3 => m_add_to_eval_ctrls(3),
x_eval_ctrl_attr_4 => m_add_to_eval_ctrls(4),
x_eval_ctrl_attr_5 => m_add_to_eval_ctrls(5),
x_eval_ctrl_attr_6 => m_add_to_eval_ctrls(6),
x_eval_ctrl_attr_7 => m_add_to_eval_ctrls(7),
x_eval_ctrl_attr_8 => m_add_to_eval_ctrls(8),
x_eval_ctrl_attr_9 => m_add_to_eval_ctrls(9),
x_eval_ctrl_attr_10 => m_add_to_eval_ctrls(10),
x_eval_ctrl_attr_11 => m_add_to_eval_ctrls(11),
x_eval_ctrl_attr_12 => m_add_to_eval_ctrls(12),
x_eval_ctrl_attr_13 => m_add_to_eval_ctrls(13),
x_eval_ctrl_attr_14 => m_add_to_eval_ctrls(14),
x_eval_ctrl_attr_15 => m_add_to_eval_ctrls(15),
x_eval_ctrl_attr_16 => m_add_to_eval_ctrls(16),
x_eval_ctrl_attr_17 => m_add_to_eval_ctrls(17),
x_eval_ctrl_attr_18 => m_add_to_eval_ctrls(18),
x_eval_ctrl_attr_19 => m_add_to_eval_ctrls(19),
x_eval_ctrl_attr_20 => m_add_to_eval_ctrls(20),
x_eval_ctrl_attr_21 => m_add_to_eval_ctrls(21),
x_eval_ctrl_attr_22 => m_add_to_eval_ctrls(22),
x_eval_ctrl_attr_23 => m_add_to_eval_ctrls(23),
x_eval_ctrl_attr_24 => m_add_to_eval_ctrls(24),
x_eval_ctrl_attr_25 => m_add_to_eval_ctrls(25),
x_eval_ctrl_attr_26 => m_add_to_eval_ctrls(26),
x_eval_ctrl_attr_27 => m_add_to_eval_ctrls(27),
x_eval_ctrl_attr_28 => m_add_to_eval_ctrls(28),
x_eval_ctrl_attr_29 => m_add_to_eval_ctrls(29),
x_eval_ctrl_attr_30 => m_add_to_eval_ctrls(30)
);
fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in Update_acc_ctrl_components'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in Update_acc_ctrl_components'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
end ; --Update_acc_ctrl_objectives
PROCEDURE Update_item_ctrl_Assertions
(P_CERTIFICATION_ID number ,
P_FINANCIAL_STATEMENT_ID number,
P_STATEMENT_GROUP_ID number,
P_FINANCIAL_ITEM_ID number,
P_CONTROL_ID number ,
P_ORG_ID number ,
P_CHANGE_FLAG VARCHAR2,
P_NEW_FLAG VARCHAR2) is
begin
declare
cursor existing_code(par_type varchar2)
is
select
ctrl_attr_code_1,
ctrl_attr_code_2,
ctrl_attr_code_3,
ctrl_attr_code_4,
ctrl_attr_code_5,
ctrl_attr_code_6,
ctrl_attr_code_7,
ctrl_attr_code_8,
ctrl_attr_code_9,
ctrl_attr_code_10,
ctrl_attr_code_11,
ctrl_attr_code_12,
ctrl_attr_code_13,
ctrl_attr_code_14,
ctrl_attr_code_15,
ctrl_attr_code_16,
ctrl_attr_code_17,
ctrl_attr_code_18,
ctrl_attr_code_19,
ctrl_attr_code_20,
ctrl_attr_code_21,
ctrl_attr_code_22,
ctrl_attr_code_23,
ctrl_attr_code_24,
ctrl_attr_code_25,
ctrl_attr_code_26,
ctrl_attr_code_27,
ctrl_attr_code_28,
ctrl_attr_code_29,
ctrl_attr_code_30,
ineff_ctrl_attr_1,
ineff_ctrl_attr_2,
ineff_ctrl_attr_3,
ineff_ctrl_attr_4,
ineff_ctrl_attr_5,
ineff_ctrl_attr_6,
ineff_ctrl_attr_7,
ineff_ctrl_attr_8,
ineff_ctrl_attr_9,
ineff_ctrl_attr_10,
ineff_ctrl_attr_11,
ineff_ctrl_attr_12,
ineff_ctrl_attr_13,
ineff_ctrl_attr_14,
ineff_ctrl_attr_15,
ineff_ctrl_attr_16,
ineff_ctrl_attr_17,
ineff_ctrl_attr_18,
ineff_ctrl_attr_19,
ineff_ctrl_attr_20,
ineff_ctrl_attr_21,
ineff_ctrl_attr_22,
ineff_ctrl_attr_23,
ineff_ctrl_attr_24,
ineff_ctrl_attr_25,
ineff_ctrl_attr_26,
ineff_ctrl_attr_27,
ineff_ctrl_attr_28,
ineff_ctrl_attr_29,
ineff_ctrl_attr_30,
acc_assert_flag_1 ,
acc_assert_flag_2 ,
acc_assert_flag_3 ,
acc_assert_flag_4 ,
acc_assert_flag_5 ,
acc_assert_flag_6 ,
acc_assert_flag_7 ,
acc_assert_flag_8 ,
acc_assert_flag_9 ,
acc_assert_flag_10,
acc_assert_flag_11 ,
acc_assert_flag_12 ,
acc_assert_flag_13 ,
acc_assert_flag_14 ,
acc_assert_flag_15 ,
acc_assert_flag_16 ,
acc_assert_flag_17 ,
acc_assert_flag_18 ,
acc_assert_flag_19 ,
acc_assert_flag_20,
acc_assert_flag_21 ,
acc_assert_flag_22 ,
acc_assert_flag_23 ,
acc_assert_flag_24 ,
acc_assert_flag_25 ,
acc_assert_flag_26 ,
acc_assert_flag_27 ,
acc_assert_flag_28 ,
acc_assert_flag_29 ,
acc_assert_flag_30
from
amw_fin_cert_ctrl_sum
where
fin_certification_id = P_CERTIFICATION_ID and
ctrl_attribute_type = par_type and
ROWNUM <2;
select
distinct
comp.ASSERTION_CODE
from
amw.amw_fin_item_acc_ctrl ctrl,
amw_control_assertions comp
where
ctrl.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID
and
ctrl.OBJECT_TYPE = 'FINANCIAL ITEM' and
ctrl.FINANCIAL_ITEM_ID= P_FINANCIAL_ITEM_ID
and
ctrl.CONTROL_REV_ID =comp.CONTROL_REV_ID and
ctrl.ORGANIZATION_ID = P_ORG_ID
and
ctrl.CONTROL_ID = P_CONTROL_ID ;
select DISTINCT
ASSERTION_CODE
from
amw_account_assertions
where
NATURAL_ACCOUNT_ID IN
(select DISTINCT NATURAL_ACCOUNT_ID from amw_fin_cert_scope where fin_certification_id = P_CERTIFICATION_ID and
financial_item_id = P_FINANCIAL_ITEM_ID );
amw_fin_coso_views_pvt.UPDATE_FINITEM_ASSERT_ROW(
x_fin_certification_id => P_CERTIFICATION_ID ,
x_financial_statement_id => P_FINANCIAL_STATEMENT_ID ,
x_financial_item_id => P_FINANCIAL_ITEM_ID ,
x_account_group_id => null ,
x_natural_account_id => null,
x_object_type => m_OBJECT_TYPE ,
x_ctrl_attribute_type => m_ctrl_attribute_type ,
x_ineff_ctrl_attr_1 => m_ineff_control(1) ,
x_ineff_ctrl_attr_2 => m_ineff_control(2) ,
x_ineff_ctrl_attr_3 => m_ineff_control(3) ,
x_ineff_ctrl_attr_4 => m_ineff_control(4) ,
x_ineff_ctrl_attr_5 => m_ineff_control(5) ,
x_ineff_ctrl_attr_6 => m_ineff_control(6) ,
x_ineff_ctrl_attr_7 => m_ineff_control(7) ,
x_ineff_ctrl_attr_8 => m_ineff_control(8) ,
x_ineff_ctrl_attr_9 => m_ineff_control(9) ,
x_ineff_ctrl_attr_10 => m_ineff_control(10) ,
x_ineff_ctrl_attr_11 => m_ineff_control(11) ,
x_ineff_ctrl_attr_12 => m_ineff_control(12) ,
x_ineff_ctrl_attr_13 => m_ineff_control(13) ,
x_ineff_ctrl_attr_14 => m_ineff_control(14) ,
x_ineff_ctrl_attr_15 => m_ineff_control(15) ,
x_ineff_ctrl_attr_16 => m_ineff_control(16) ,
x_ineff_ctrl_attr_17 => m_ineff_control(17) ,
x_ineff_ctrl_attr_18 => m_ineff_control(18) ,
x_ineff_ctrl_attr_19 => m_ineff_control(19) ,
x_ineff_ctrl_attr_20 => m_ineff_control(20) ,
x_ineff_ctrl_attr_21 => m_ineff_control(21) ,
x_ineff_ctrl_attr_22 => m_ineff_control(22) ,
x_ineff_ctrl_attr_23 => m_ineff_control(23) ,
x_ineff_ctrl_attr_24 => m_ineff_control(24) ,
x_ineff_ctrl_attr_25 => m_ineff_control(25) ,
x_ineff_ctrl_attr_26 => m_ineff_control(26) ,
x_ineff_ctrl_attr_27 => m_ineff_control(27) ,
x_ineff_ctrl_attr_28 => m_ineff_control(28) ,
x_ineff_ctrl_attr_29 => m_ineff_control(29) ,
x_ineff_ctrl_attr_30 => m_ineff_control(30) ,
x_last_updated_by => g_user_id ,
x_last_update_date => SYSDATE ,
x_last_update_login => g_login_id,
x_eval_ctrl_attr_1 => m_add_to_eval_ctrls(1),
x_eval_ctrl_attr_2 => m_add_to_eval_ctrls(2),
x_eval_ctrl_attr_3 => m_add_to_eval_ctrls(3),
x_eval_ctrl_attr_4 => m_add_to_eval_ctrls(4),
x_eval_ctrl_attr_5 => m_add_to_eval_ctrls(5),
x_eval_ctrl_attr_6 => m_add_to_eval_ctrls(6),
x_eval_ctrl_attr_7 => m_add_to_eval_ctrls(7),
x_eval_ctrl_attr_8 => m_add_to_eval_ctrls(8),
x_eval_ctrl_attr_9 => m_add_to_eval_ctrls(9),
x_eval_ctrl_attr_10 => m_add_to_eval_ctrls(10),
x_eval_ctrl_attr_11 => m_add_to_eval_ctrls(11),
x_eval_ctrl_attr_12 => m_add_to_eval_ctrls(12),
x_eval_ctrl_attr_13 => m_add_to_eval_ctrls(13),
x_eval_ctrl_attr_14 => m_add_to_eval_ctrls(14),
x_eval_ctrl_attr_15 => m_add_to_eval_ctrls(15),
x_eval_ctrl_attr_16 => m_add_to_eval_ctrls(16),
x_eval_ctrl_attr_17 => m_add_to_eval_ctrls(17),
x_eval_ctrl_attr_18 => m_add_to_eval_ctrls(18),
x_eval_ctrl_attr_19 => m_add_to_eval_ctrls(19),
x_eval_ctrl_attr_20 => m_add_to_eval_ctrls(20),
x_eval_ctrl_attr_21 => m_add_to_eval_ctrls(21),
x_eval_ctrl_attr_22 => m_add_to_eval_ctrls(22),
x_eval_ctrl_attr_23 => m_add_to_eval_ctrls(23),
x_eval_ctrl_attr_24 => m_add_to_eval_ctrls(24),
x_eval_ctrl_attr_25 => m_add_to_eval_ctrls(25),
x_eval_ctrl_attr_26 => m_add_to_eval_ctrls(26),
x_eval_ctrl_attr_27 => m_add_to_eval_ctrls(27),
x_eval_ctrl_attr_28 => m_add_to_eval_ctrls(28),
x_eval_ctrl_attr_29 => m_add_to_eval_ctrls(29),
x_eval_ctrl_attr_30 => m_add_to_eval_ctrls(30),
x_acc_assert_flag1 => m_acc_assert_flag(1),
x_acc_assert_flag2 => m_acc_assert_flag(2),
x_acc_assert_flag3 => m_acc_assert_flag(3),
x_acc_assert_flag4 => m_acc_assert_flag(4),
x_acc_assert_flag5 => m_acc_assert_flag(5),
x_acc_assert_flag6 => m_acc_assert_flag(6),
x_acc_assert_flag7 => m_acc_assert_flag(7),
x_acc_assert_flag8 => m_acc_assert_flag(8),
x_acc_assert_flag9 => m_acc_assert_flag(9),
x_acc_assert_flag10 => m_acc_assert_flag(10),
x_acc_assert_flag11 => m_acc_assert_flag(11),
x_acc_assert_flag12 => m_acc_assert_flag(12),
x_acc_assert_flag13 => m_acc_assert_flag(13),
x_acc_assert_flag14 => m_acc_assert_flag(14),
x_acc_assert_flag15 => m_acc_assert_flag(15),
x_acc_assert_flag16 => m_acc_assert_flag(16),
x_acc_assert_flag17 => m_acc_assert_flag(17),
x_acc_assert_flag18 => m_acc_assert_flag(18),
x_acc_assert_flag19 => m_acc_assert_flag(19),
x_acc_assert_flag20 => m_acc_assert_flag(20),
x_acc_assert_flag21 => m_acc_assert_flag(21),
x_acc_assert_flag22 => m_acc_assert_flag(22),
x_acc_assert_flag23 => m_acc_assert_flag(23),
x_acc_assert_flag24 => m_acc_assert_flag(24),
x_acc_assert_flag25 => m_acc_assert_flag(25),
x_acc_assert_flag26 => m_acc_assert_flag(26),
x_acc_assert_flag27 => m_acc_assert_flag(27),
x_acc_assert_flag28 => m_acc_assert_flag(28),
x_acc_assert_flag29 => m_acc_assert_flag(29),
x_acc_assert_flag30 => m_acc_assert_flag(30)
);
fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in Update_item_ctrl_components'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in Update_item_ctrl_components'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
end ; --Update_item_ctrl_Assertions
PROCEDURE Update_acc_ctrl_Assertions
(P_CERTIFICATION_ID number ,
P_ACCOUNT_GROUP_ID number ,
P_ACCOUNT_ID number ,
P_CONTROL_ID number ,
P_ORG_ID number ,
P_CHANGE_FLAG VARCHAR2,
P_NEW_FLAG VARCHAR2) is
begin
declare
cursor existing_code(par_type varchar2)
is
select
ctrl_attr_code_1,
ctrl_attr_code_2,
ctrl_attr_code_3,
ctrl_attr_code_4,
ctrl_attr_code_5,
ctrl_attr_code_6,
ctrl_attr_code_7,
ctrl_attr_code_8,
ctrl_attr_code_9,
ctrl_attr_code_10,
ctrl_attr_code_11,
ctrl_attr_code_12,
ctrl_attr_code_13,
ctrl_attr_code_14,
ctrl_attr_code_15,
ctrl_attr_code_16,
ctrl_attr_code_17,
ctrl_attr_code_18,
ctrl_attr_code_19,
ctrl_attr_code_20,
ctrl_attr_code_21,
ctrl_attr_code_22,
ctrl_attr_code_23,
ctrl_attr_code_24,
ctrl_attr_code_25,
ctrl_attr_code_26,
ctrl_attr_code_27,
ctrl_attr_code_28,
ctrl_attr_code_29,
ctrl_attr_code_30,
ineff_ctrl_attr_1,
ineff_ctrl_attr_2,
ineff_ctrl_attr_3,
ineff_ctrl_attr_4,
ineff_ctrl_attr_5,
ineff_ctrl_attr_6,
ineff_ctrl_attr_7,
ineff_ctrl_attr_8,
ineff_ctrl_attr_9,
ineff_ctrl_attr_10,
ineff_ctrl_attr_11,
ineff_ctrl_attr_12,
ineff_ctrl_attr_13,
ineff_ctrl_attr_14,
ineff_ctrl_attr_15,
ineff_ctrl_attr_16,
ineff_ctrl_attr_17,
ineff_ctrl_attr_18,
ineff_ctrl_attr_19,
ineff_ctrl_attr_20,
ineff_ctrl_attr_21,
ineff_ctrl_attr_22,
ineff_ctrl_attr_23,
ineff_ctrl_attr_24,
ineff_ctrl_attr_25,
ineff_ctrl_attr_26,
ineff_ctrl_attr_27,
ineff_ctrl_attr_28,
ineff_ctrl_attr_29,
ineff_ctrl_attr_30,
acc_assert_flag_1 ,
acc_assert_flag_2 ,
acc_assert_flag_3 ,
acc_assert_flag_4 ,
acc_assert_flag_5 ,
acc_assert_flag_6 ,
acc_assert_flag_7 ,
acc_assert_flag_8 ,
acc_assert_flag_9 ,
acc_assert_flag_10,
acc_assert_flag_11 ,
acc_assert_flag_12 ,
acc_assert_flag_13 ,
acc_assert_flag_14 ,
acc_assert_flag_15 ,
acc_assert_flag_16 ,
acc_assert_flag_17 ,
acc_assert_flag_18 ,
acc_assert_flag_19 ,
acc_assert_flag_20,
acc_assert_flag_21 ,
acc_assert_flag_22 ,
acc_assert_flag_23 ,
acc_assert_flag_24 ,
acc_assert_flag_25 ,
acc_assert_flag_26 ,
acc_assert_flag_27 ,
acc_assert_flag_28 ,
acc_assert_flag_29 ,
acc_assert_flag_30
from
amw_fin_cert_ctrl_sum
where
fin_certification_id = P_CERTIFICATION_ID and
ctrl_attribute_type = par_type and
ROWNUM <2
AND account_group_id = P_ACCOUNT_GROUP_ID and
natural_account_id = P_ACCOUNT_ID and
object_type = 'ACCOUNT' ;
select
distinct
comp.ASSERTION_CODE
from
amw.amw_fin_item_acc_ctrl ctrl,
amw_control_assertions comp
where
ctrl.FIN_CERTIFICATION_ID= P_CERTIFICATION_ID
and
ctrl.OBJECT_TYPE = 'ACCOUNT' and
ctrl.ACCOUNT_GROUP_ID= P_ACCOUNT_GROUP_ID
AND
ctrl.NATURAL_ACCOUNT_ID = P_ACCOUNT_ID
and
ctrl.CONTROL_REV_ID =comp.CONTROL_REV_ID and
ctrl.ORGANIZATION_ID = P_ORG_ID
and
ctrl.CONTROL_ID = P_CONTROL_ID ;
select
ASSERTION_CODE
from
amw_account_assertions
where
NATURAL_ACCOUNT_ID =P_ACCOUNT_ID ;
select
distinct
ASSERTION_CODE
from
amw_account_assertions
where
((NATURAL_ACCOUNT_ID =P_ACCOUNT_ID) or (NATURAL_ACCOUNT_ID in (select CHILD_NATURAL_ACCOUNT_ID from amw_fin_key_acct_flat
where PARENT_NATURAL_ACCOUNT_ID =P_ACCOUNT_ID and ACCOUNT_GROUP_ID=P_ACCOUNT_GROUP_ID)));
amw_fin_coso_views_pvt.UPDATE_FIN_ACC_ASSERT_ROW(
x_fin_certification_id => P_CERTIFICATION_ID ,
x_financial_statement_id => NULL ,
x_financial_item_id => NULL ,
x_account_group_id => P_ACCOUNT_GROUP_ID,
x_natural_account_id => P_ACCOUNT_ID,
x_object_type => m_OBJECT_TYPE ,
x_ctrl_attribute_type => m_ctrl_attribute_type ,
x_ineff_ctrl_attr_1 => m_ineff_control(1) ,
x_ineff_ctrl_attr_2 => m_ineff_control(2) ,
x_ineff_ctrl_attr_3 => m_ineff_control(3) ,
x_ineff_ctrl_attr_4 => m_ineff_control(4) ,
x_ineff_ctrl_attr_5 => m_ineff_control(5) ,
x_ineff_ctrl_attr_6 => m_ineff_control(6) ,
x_ineff_ctrl_attr_7 => m_ineff_control(7) ,
x_ineff_ctrl_attr_8 => m_ineff_control(8) ,
x_ineff_ctrl_attr_9 => m_ineff_control(9) ,
x_ineff_ctrl_attr_10 => m_ineff_control(10) ,
x_ineff_ctrl_attr_11 => m_ineff_control(11) ,
x_ineff_ctrl_attr_12 => m_ineff_control(12) ,
x_ineff_ctrl_attr_13 => m_ineff_control(13) ,
x_ineff_ctrl_attr_14 => m_ineff_control(14) ,
x_ineff_ctrl_attr_15 => m_ineff_control(15) ,
x_ineff_ctrl_attr_16 => m_ineff_control(16) ,
x_ineff_ctrl_attr_17 => m_ineff_control(17) ,
x_ineff_ctrl_attr_18 => m_ineff_control(18) ,
x_ineff_ctrl_attr_19 => m_ineff_control(19) ,
x_ineff_ctrl_attr_20 => m_ineff_control(20) ,
x_ineff_ctrl_attr_21 => m_ineff_control(21) ,
x_ineff_ctrl_attr_22 => m_ineff_control(22) ,
x_ineff_ctrl_attr_23 => m_ineff_control(23) ,
x_ineff_ctrl_attr_24 => m_ineff_control(24) ,
x_ineff_ctrl_attr_25 => m_ineff_control(25) ,
x_ineff_ctrl_attr_26 => m_ineff_control(26) ,
x_ineff_ctrl_attr_27 => m_ineff_control(27) ,
x_ineff_ctrl_attr_28 => m_ineff_control(28) ,
x_ineff_ctrl_attr_29 => m_ineff_control(29) ,
x_ineff_ctrl_attr_30 => m_ineff_control(30) ,
x_last_updated_by => g_user_id ,
x_last_update_date => SYSDATE ,
x_last_update_login => g_login_id,
x_eval_ctrl_attr_1 => m_add_to_eval_ctrls(1),
x_eval_ctrl_attr_2 => m_add_to_eval_ctrls(2),
x_eval_ctrl_attr_3 => m_add_to_eval_ctrls(3),
x_eval_ctrl_attr_4 => m_add_to_eval_ctrls(4),
x_eval_ctrl_attr_5 => m_add_to_eval_ctrls(5),
x_eval_ctrl_attr_6 => m_add_to_eval_ctrls(6),
x_eval_ctrl_attr_7 => m_add_to_eval_ctrls(7),
x_eval_ctrl_attr_8 => m_add_to_eval_ctrls(8),
x_eval_ctrl_attr_9 => m_add_to_eval_ctrls(9),
x_eval_ctrl_attr_10 => m_add_to_eval_ctrls(10),
x_eval_ctrl_attr_11 => m_add_to_eval_ctrls(11),
x_eval_ctrl_attr_12 => m_add_to_eval_ctrls(12),
x_eval_ctrl_attr_13 => m_add_to_eval_ctrls(13),
x_eval_ctrl_attr_14 => m_add_to_eval_ctrls(14),
x_eval_ctrl_attr_15 => m_add_to_eval_ctrls(15),
x_eval_ctrl_attr_16 => m_add_to_eval_ctrls(16),
x_eval_ctrl_attr_17 => m_add_to_eval_ctrls(17),
x_eval_ctrl_attr_18 => m_add_to_eval_ctrls(18),
x_eval_ctrl_attr_19 => m_add_to_eval_ctrls(19),
x_eval_ctrl_attr_20 => m_add_to_eval_ctrls(20),
x_eval_ctrl_attr_21 => m_add_to_eval_ctrls(21),
x_eval_ctrl_attr_22 => m_add_to_eval_ctrls(22),
x_eval_ctrl_attr_23 => m_add_to_eval_ctrls(23),
x_eval_ctrl_attr_24 => m_add_to_eval_ctrls(24),
x_eval_ctrl_attr_25 => m_add_to_eval_ctrls(25),
x_eval_ctrl_attr_26 => m_add_to_eval_ctrls(26),
x_eval_ctrl_attr_27 => m_add_to_eval_ctrls(27),
x_eval_ctrl_attr_28 => m_add_to_eval_ctrls(28),
x_eval_ctrl_attr_29 => m_add_to_eval_ctrls(29),
x_eval_ctrl_attr_30 => m_add_to_eval_ctrls(30),
x_acc_assert_flag1 => m_acc_assert_flag(1),
x_acc_assert_flag2 => m_acc_assert_flag(2),
x_acc_assert_flag3 => m_acc_assert_flag(3),
x_acc_assert_flag4 => m_acc_assert_flag(4),
x_acc_assert_flag5 => m_acc_assert_flag(5),
x_acc_assert_flag6 => m_acc_assert_flag(6),
x_acc_assert_flag7 => m_acc_assert_flag(7),
x_acc_assert_flag8 => m_acc_assert_flag(8),
x_acc_assert_flag9 => m_acc_assert_flag(9),
x_acc_assert_flag10 => m_acc_assert_flag(10),
x_acc_assert_flag11 => m_acc_assert_flag(11),
x_acc_assert_flag12 => m_acc_assert_flag(12),
x_acc_assert_flag13 => m_acc_assert_flag(13),
x_acc_assert_flag14 => m_acc_assert_flag(14),
x_acc_assert_flag15 => m_acc_assert_flag(15),
x_acc_assert_flag16 => m_acc_assert_flag(16),
x_acc_assert_flag17 => m_acc_assert_flag(17),
x_acc_assert_flag18 => m_acc_assert_flag(18),
x_acc_assert_flag19 => m_acc_assert_flag(19),
x_acc_assert_flag20 => m_acc_assert_flag(20),
x_acc_assert_flag21 => m_acc_assert_flag(21),
x_acc_assert_flag22 => m_acc_assert_flag(22),
x_acc_assert_flag23 => m_acc_assert_flag(23),
x_acc_assert_flag24 => m_acc_assert_flag(24),
x_acc_assert_flag25 => m_acc_assert_flag(25),
x_acc_assert_flag26 => m_acc_assert_flag(26),
x_acc_assert_flag27 => m_acc_assert_flag(27),
x_acc_assert_flag28 => m_acc_assert_flag(28),
x_acc_assert_flag29 => m_acc_assert_flag(29),
x_acc_assert_flag30 => m_acc_assert_flag(30)
);
fnd_file.put_line (fnd_file.LOG, SUBSTR ('No data found in Update_acc_ctrl_components'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
fnd_file.put_line (fnd_file.LOG, SUBSTR ('Unexpected Error in Update_acc_ctrl_components'
|| SUBSTR (SQLERRM, 1, 100), 1, 200));
end ; --Update_acc_ctrl_Assertions
procedure UPDATE_FINITEM_ASSERT_ROW (
x_fin_certification_id NUMBER ,
x_financial_statement_id NUMBER ,
x_financial_item_id NUMBER ,
x_account_group_id NUMBER,
x_natural_account_id NUMBER,
x_object_type VARCHAR2,
x_ctrl_attribute_type VARCHAR2,
x_ineff_ctrl_attr_1 NUMBER,
x_ineff_ctrl_attr_2 NUMBER,
x_ineff_ctrl_attr_3 NUMBER,
x_ineff_ctrl_attr_4 NUMBER,
x_ineff_ctrl_attr_5 NUMBER,
x_ineff_ctrl_attr_6 NUMBER,
x_ineff_ctrl_attr_7 NUMBER,
x_ineff_ctrl_attr_8 NUMBER,
x_ineff_ctrl_attr_9 NUMBER,
x_ineff_ctrl_attr_10 NUMBER,
x_ineff_ctrl_attr_11 NUMBER,
x_ineff_ctrl_attr_12 NUMBER,
x_ineff_ctrl_attr_13 NUMBER,
x_ineff_ctrl_attr_14 NUMBER,
x_ineff_ctrl_attr_15 NUMBER,
x_ineff_ctrl_attr_16 NUMBER,
x_ineff_ctrl_attr_17 NUMBER,
x_ineff_ctrl_attr_18 NUMBER,
x_ineff_ctrl_attr_19 NUMBER,
x_ineff_ctrl_attr_20 NUMBER,
x_ineff_ctrl_attr_21 NUMBER,
x_ineff_ctrl_attr_22 NUMBER,
x_ineff_ctrl_attr_23 NUMBER,
x_ineff_ctrl_attr_24 NUMBER,
x_ineff_ctrl_attr_25 NUMBER,
x_ineff_ctrl_attr_26 NUMBER,
x_ineff_ctrl_attr_27 NUMBER,
x_ineff_ctrl_attr_28 NUMBER,
x_ineff_ctrl_attr_29 NUMBER,
x_ineff_ctrl_attr_30 NUMBER,
x_last_updated_by NUMBER,
x_last_update_date DATE ,
x_last_update_login NUMBER,
x_eval_ctrl_attr_1 NUMBER,
x_eval_ctrl_attr_2 NUMBER,
x_eval_ctrl_attr_3 NUMBER,
x_eval_ctrl_attr_4 NUMBER,
x_eval_ctrl_attr_5 NUMBER,
x_eval_ctrl_attr_6 NUMBER,
x_eval_ctrl_attr_7 NUMBER,
x_eval_ctrl_attr_8 NUMBER,
x_eval_ctrl_attr_9 NUMBER,
x_eval_ctrl_attr_10 NUMBER,
x_eval_ctrl_attr_11 NUMBER,
x_eval_ctrl_attr_12 NUMBER,
x_eval_ctrl_attr_13 NUMBER,
x_eval_ctrl_attr_14 NUMBER,
x_eval_ctrl_attr_15 NUMBER,
x_eval_ctrl_attr_16 NUMBER,
x_eval_ctrl_attr_17 NUMBER,
x_eval_ctrl_attr_18 NUMBER,
x_eval_ctrl_attr_19 NUMBER,
x_eval_ctrl_attr_20 NUMBER,
x_eval_ctrl_attr_21 NUMBER,
x_eval_ctrl_attr_22 NUMBER,
x_eval_ctrl_attr_23 NUMBER,
x_eval_ctrl_attr_24 NUMBER,
x_eval_ctrl_attr_25 NUMBER,
x_eval_ctrl_attr_26 NUMBER,
x_eval_ctrl_attr_27 NUMBER,
x_eval_ctrl_attr_28 NUMBER,
x_eval_ctrl_attr_29 NUMBER,
x_eval_ctrl_attr_30 NUMBER,
x_acc_assert_flag1 VARCHAR2,
x_acc_assert_flag2 VARCHAR2,
x_acc_assert_flag3 VARCHAR2,
x_acc_assert_flag4 VARCHAR2,
x_acc_assert_flag5 VARCHAR2,
x_acc_assert_flag6 VARCHAR2,
x_acc_assert_flag7 VARCHAR2,
x_acc_assert_flag8 VARCHAR2,
x_acc_assert_flag9 VARCHAR2,
x_acc_assert_flag10 VARCHAR2,
x_acc_assert_flag11 VARCHAR2,
x_acc_assert_flag12 VARCHAR2,
x_acc_assert_flag13 VARCHAR2,
x_acc_assert_flag14 VARCHAR2,
x_acc_assert_flag15 VARCHAR2,
x_acc_assert_flag16 VARCHAR2,
x_acc_assert_flag17 VARCHAR2,
x_acc_assert_flag18 VARCHAR2,
x_acc_assert_flag19 VARCHAR2,
x_acc_assert_flag20 VARCHAR2,
x_acc_assert_flag21 VARCHAR2,
x_acc_assert_flag22 VARCHAR2,
x_acc_assert_flag23 VARCHAR2,
x_acc_assert_flag24 VARCHAR2,
x_acc_assert_flag25 VARCHAR2,
x_acc_assert_flag26 VARCHAR2,
x_acc_assert_flag27 VARCHAR2,
x_acc_assert_flag28 VARCHAR2,
x_acc_assert_flag29 VARCHAR2,
x_acc_assert_flag30 VARCHAR2
) is
begin
declare
var_fin_certification_id number;
UPDATE
amw_fin_cert_ctrl_sum
SET
ineff_ctrl_attr_1= x_ineff_ctrl_attr_1
,ineff_ctrl_attr_2= x_ineff_ctrl_attr_2
,ineff_ctrl_attr_3= x_ineff_ctrl_attr_3
,ineff_ctrl_attr_4= x_ineff_ctrl_attr_4
,ineff_ctrl_attr_5= x_ineff_ctrl_attr_5
,ineff_ctrl_attr_6= x_ineff_ctrl_attr_6
,ineff_ctrl_attr_7= x_ineff_ctrl_attr_7
,ineff_ctrl_attr_8= x_ineff_ctrl_attr_8
,ineff_ctrl_attr_9= x_ineff_ctrl_attr_9
,ineff_ctrl_attr_10= x_ineff_ctrl_attr_10
,ineff_ctrl_attr_11= x_ineff_ctrl_attr_11
,ineff_ctrl_attr_12= x_ineff_ctrl_attr_12
,ineff_ctrl_attr_13= x_ineff_ctrl_attr_13
,ineff_ctrl_attr_14= x_ineff_ctrl_attr_14
,ineff_ctrl_attr_15= x_ineff_ctrl_attr_15
,ineff_ctrl_attr_16= x_ineff_ctrl_attr_16
,ineff_ctrl_attr_17= x_ineff_ctrl_attr_17
,ineff_ctrl_attr_18= x_ineff_ctrl_attr_18
,ineff_ctrl_attr_19= x_ineff_ctrl_attr_19
,ineff_ctrl_attr_20= x_ineff_ctrl_attr_20
,ineff_ctrl_attr_21= x_ineff_ctrl_attr_21
,ineff_ctrl_attr_22= x_ineff_ctrl_attr_22
,ineff_ctrl_attr_23= x_ineff_ctrl_attr_23
,ineff_ctrl_attr_24= x_ineff_ctrl_attr_24
,ineff_ctrl_attr_25= x_ineff_ctrl_attr_25
,ineff_ctrl_attr_26= x_ineff_ctrl_attr_26
,ineff_ctrl_attr_27= x_ineff_ctrl_attr_27
,ineff_ctrl_attr_28= x_ineff_ctrl_attr_28
,ineff_ctrl_attr_29= x_ineff_ctrl_attr_29
,ineff_ctrl_attr_30= x_ineff_ctrl_attr_30,
eval_ctrl_attr_1 = nvl(eval_ctrl_attr_1,0) + x_eval_ctrl_attr_1 ,
eval_ctrl_attr_2 = nvl(eval_ctrl_attr_2,0) + x_eval_ctrl_attr_2 ,
eval_ctrl_attr_3 = nvl(eval_ctrl_attr_3,0) + x_eval_ctrl_attr_3 ,
eval_ctrl_attr_4 = nvl(eval_ctrl_attr_4,0) + x_eval_ctrl_attr_4 ,
eval_ctrl_attr_5 = nvl(eval_ctrl_attr_5,0) + x_eval_ctrl_attr_5 ,
eval_ctrl_attr_6 = nvl(eval_ctrl_attr_6,0) + x_eval_ctrl_attr_6 ,
eval_ctrl_attr_7 = nvl(eval_ctrl_attr_7,0) + x_eval_ctrl_attr_7 ,
eval_ctrl_attr_8 = nvl(eval_ctrl_attr_8,0) + x_eval_ctrl_attr_8 ,
eval_ctrl_attr_9 = nvl(eval_ctrl_attr_9,0) + x_eval_ctrl_attr_9 ,
eval_ctrl_attr_10 = nvl(eval_ctrl_attr_10,0) + x_eval_ctrl_attr_10 ,
eval_ctrl_attr_11 = nvl(eval_ctrl_attr_11,0) + x_eval_ctrl_attr_11 ,
eval_ctrl_attr_12 = nvl(eval_ctrl_attr_12,0) + x_eval_ctrl_attr_12 ,
eval_ctrl_attr_13 = nvl(eval_ctrl_attr_13,0) + x_eval_ctrl_attr_13 ,
eval_ctrl_attr_14 = nvl(eval_ctrl_attr_14,0) + x_eval_ctrl_attr_14 ,
eval_ctrl_attr_15 = nvl(eval_ctrl_attr_15,0) + x_eval_ctrl_attr_15 ,
eval_ctrl_attr_16 = nvl(eval_ctrl_attr_16,0) + x_eval_ctrl_attr_16 ,
eval_ctrl_attr_17 = nvl(eval_ctrl_attr_17,0) + x_eval_ctrl_attr_17 ,
eval_ctrl_attr_18 = nvl(eval_ctrl_attr_18,0) + x_eval_ctrl_attr_18 ,
eval_ctrl_attr_19 = nvl(eval_ctrl_attr_19,0) + x_eval_ctrl_attr_19 ,
eval_ctrl_attr_20 = nvl(eval_ctrl_attr_20,0) + x_eval_ctrl_attr_20 ,
eval_ctrl_attr_21 = nvl(eval_ctrl_attr_21,0) + x_eval_ctrl_attr_21 ,
eval_ctrl_attr_22 = nvl(eval_ctrl_attr_22,0) + x_eval_ctrl_attr_22 ,
eval_ctrl_attr_23 = nvl(eval_ctrl_attr_23,0) + x_eval_ctrl_attr_23 ,
eval_ctrl_attr_24 = nvl(eval_ctrl_attr_24,0) + x_eval_ctrl_attr_24 ,
eval_ctrl_attr_25 = nvl(eval_ctrl_attr_25,0) + x_eval_ctrl_attr_25 ,
eval_ctrl_attr_26 = nvl(eval_ctrl_attr_26,0) + x_eval_ctrl_attr_26 ,
eval_ctrl_attr_27 = nvl(eval_ctrl_attr_27,0) + x_eval_ctrl_attr_27 ,
eval_ctrl_attr_28 = nvl(eval_ctrl_attr_28,0) + x_eval_ctrl_attr_28 ,
eval_ctrl_attr_29 = nvl(eval_ctrl_attr_29,0) + x_eval_ctrl_attr_29 ,
eval_ctrl_attr_30 = nvl(eval_ctrl_attr_30,0) + x_eval_ctrl_attr_30
,ineff_ctrl_prcnt_1= round((x_ineff_ctrl_attr_1 / decode(total_ctrl_attr_1,null,1,0,1,total_ctrl_attr_1) ) * 100,0)
,ineff_ctrl_prcnt_2 = round((x_ineff_ctrl_attr_2 / decode(total_ctrl_attr_2,null,1,0,1,total_ctrl_attr_2) ) * 100,0)
,ineff_ctrl_prcnt_3= round((x_ineff_ctrl_attr_3 / decode(total_ctrl_attr_3,null,1,0,1,total_ctrl_attr_3) ) * 100,0)
,ineff_ctrl_prcnt_4= round((x_ineff_ctrl_attr_4 / decode(total_ctrl_attr_4,null,1,0,1,total_ctrl_attr_4) ) * 100,0)
,ineff_ctrl_prcnt_5= round((x_ineff_ctrl_attr_5 / decode(total_ctrl_attr_5,null,1,0,1,total_ctrl_attr_5) ) * 100,0)
,ineff_ctrl_prcnt_6= round((x_ineff_ctrl_attr_6 / decode(total_ctrl_attr_6,null,1,0,1,total_ctrl_attr_6) ) * 100,0)
,ineff_ctrl_prcnt_7= round((x_ineff_ctrl_attr_7 / decode(total_ctrl_attr_7,null,1,0,1,total_ctrl_attr_7) ) * 100,0)
,ineff_ctrl_prcnt_8= round((x_ineff_ctrl_attr_8 / decode(total_ctrl_attr_8,null,1,0,1,total_ctrl_attr_8) ) * 100,0)
,ineff_ctrl_prcnt_9= round((x_ineff_ctrl_attr_9 / decode(total_ctrl_attr_9,null,1,0,1,total_ctrl_attr_9) ) * 100,0)
,ineff_ctrl_prcnt_10= round((x_ineff_ctrl_attr_10 / decode(total_ctrl_attr_10,null,1,0,1,total_ctrl_attr_10) ) * 100,0)
,ineff_ctrl_prcnt_11= round((x_ineff_ctrl_attr_11 / decode(total_ctrl_attr_11,null,1,0,1,total_ctrl_attr_11) ) * 100,0)
,ineff_ctrl_prcnt_12= round((x_ineff_ctrl_attr_12 / decode(total_ctrl_attr_12,null,1,0,1,total_ctrl_attr_12) ) * 100,0)
,ineff_ctrl_prcnt_13= round((x_ineff_ctrl_attr_13 / decode(total_ctrl_attr_13,null,1,0,1,total_ctrl_attr_13) ) * 100,0)
,ineff_ctrl_prcnt_14= round((x_ineff_ctrl_attr_14 / decode(total_ctrl_attr_14,null,1,0,1,total_ctrl_attr_14) ) * 100,0)
,ineff_ctrl_prcnt_15= round((x_ineff_ctrl_attr_15 / decode(total_ctrl_attr_15,null,1,0,1,total_ctrl_attr_15) ) * 100,0)
,ineff_ctrl_prcnt_16= round((x_ineff_ctrl_attr_16 / decode(total_ctrl_attr_16,null,1,0,1,total_ctrl_attr_16) ) * 100,0)
,ineff_ctrl_prcnt_17= round((x_ineff_ctrl_attr_17 / decode(total_ctrl_attr_17,null,1,0,1,total_ctrl_attr_17) ) * 100,0)
,ineff_ctrl_prcnt_18= round((x_ineff_ctrl_attr_18 / decode(total_ctrl_attr_18,null,1,0,1,total_ctrl_attr_18) ) * 100,0)
,ineff_ctrl_prcnt_19= round((x_ineff_ctrl_attr_19 / decode(total_ctrl_attr_19,null,1,0,1,total_ctrl_attr_19) ) * 100,0)
,ineff_ctrl_prcnt_20= round((x_ineff_ctrl_attr_20 / decode(total_ctrl_attr_20,null,1,0,1,total_ctrl_attr_20) ) * 100,0)
,ineff_ctrl_prcnt_21= round((x_ineff_ctrl_attr_21 / decode(total_ctrl_attr_21,null,1,0,1,total_ctrl_attr_21) ) * 100,0)
,ineff_ctrl_prcnt_22= round((x_ineff_ctrl_attr_22 / decode(total_ctrl_attr_22,null,1,0,1,total_ctrl_attr_22) ) * 100,0)
,ineff_ctrl_prcnt_23= round((x_ineff_ctrl_attr_23 / decode(total_ctrl_attr_23,null,1,0,1,total_ctrl_attr_23) ) * 100,0)
,ineff_ctrl_prcnt_24= round((x_ineff_ctrl_attr_24 / decode(total_ctrl_attr_24,null,1,0,1,total_ctrl_attr_24) ) * 100,0)
,ineff_ctrl_prcnt_25= round((x_ineff_ctrl_attr_25 / decode(total_ctrl_attr_25,null,1,0,1,total_ctrl_attr_25) ) * 100,0)
,ineff_ctrl_prcnt_26= round((x_ineff_ctrl_attr_26 / decode(total_ctrl_attr_26,null,1,0,1,total_ctrl_attr_26) ) * 100,0)
,ineff_ctrl_prcnt_27= round((x_ineff_ctrl_attr_27 / decode(total_ctrl_attr_27,null,1,0,1,total_ctrl_attr_27) ) * 100,0)
,ineff_ctrl_prcnt_28= round((x_ineff_ctrl_attr_28 / decode(total_ctrl_attr_28,null,1,0,1,total_ctrl_attr_28) ) * 100,0)
,ineff_ctrl_prcnt_29= round((x_ineff_ctrl_attr_29 / decode(total_ctrl_attr_29,null,1,0,1,total_ctrl_attr_29) ) * 100,0)
,ineff_ctrl_prcnt_30= round((x_ineff_ctrl_attr_30 / decode(total_ctrl_attr_30,null,1,0,1,total_ctrl_attr_30) ) * 100,0)
,acc_assert_flag_1 = x_acc_assert_flag1
,acc_assert_flag_2 = x_acc_assert_flag2
,acc_assert_flag_3 = x_acc_assert_flag3
,acc_assert_flag_4 = x_acc_assert_flag4
,acc_assert_flag_5 = x_acc_assert_flag5
,acc_assert_flag_6 = x_acc_assert_flag6
,acc_assert_flag_7 = x_acc_assert_flag7
,acc_assert_flag_8 = x_acc_assert_flag8
,acc_assert_flag_9 = x_acc_assert_flag9
,acc_assert_flag_10 = x_acc_assert_flag10
,acc_assert_flag_11 = x_acc_assert_flag11
,acc_assert_flag_12 = x_acc_assert_flag12
,acc_assert_flag_13 = x_acc_assert_flag13
,acc_assert_flag_14 = x_acc_assert_flag14
,acc_assert_flag_15 = x_acc_assert_flag15
,acc_assert_flag_16 = x_acc_assert_flag16
,acc_assert_flag_17 = x_acc_assert_flag17
,acc_assert_flag_18 = x_acc_assert_flag18
,acc_assert_flag_19 = x_acc_assert_flag19
,acc_assert_flag_20 = x_acc_assert_flag20
,acc_assert_flag_21 = x_acc_assert_flag21
,acc_assert_flag_22 = x_acc_assert_flag22
,acc_assert_flag_23 = x_acc_assert_flag23
,acc_assert_flag_24 = x_acc_assert_flag24
,acc_assert_flag_25 = x_acc_assert_flag25
,acc_assert_flag_26 = x_acc_assert_flag26
,acc_assert_flag_27 = x_acc_assert_flag27
,acc_assert_flag_28 = x_acc_assert_flag28
,acc_assert_flag_29 = x_acc_assert_flag29
,acc_assert_flag_30 = x_acc_assert_flag30
,last_updated_by = x_last_updated_by
,last_update_date = x_last_update_date
,last_update_login = x_last_update_login
,object_version_number = object_version_number +1
where
fin_certification_id = x_fin_certification_id and
financial_statement_id = x_financial_statement_id and
NVL(financial_item_id, -1) = NVL(x_financial_item_id, -1) and
NVL(account_group_id, -1) = NVL(x_account_group_id, -1) and
nvl(natural_account_id, -1) = nvl(x_natural_account_id, -1) and
CTRL_ATTRIBUTE_TYPE = x_ctrl_attribute_type and
object_type = x_object_type ;
end UPDATE_FINITEM_ASSERT_ROW;
procedure UPDATE_FIN_ITEM_ROW(
x_fin_certification_id NUMBER ,
x_financial_statement_id NUMBER ,
x_financial_item_id NUMBER ,
x_account_group_id NUMBER,
x_natural_account_id NUMBER,
x_object_type VARCHAR2,
x_ctrl_attribute_type VARCHAR2,
x_ineff_ctrl_attr_1 NUMBER,
x_ineff_ctrl_attr_2 NUMBER,
x_ineff_ctrl_attr_3 NUMBER,
x_ineff_ctrl_attr_4 NUMBER,
x_ineff_ctrl_attr_5 NUMBER,
x_ineff_ctrl_attr_6 NUMBER,
x_ineff_ctrl_attr_7 NUMBER,
x_ineff_ctrl_attr_8 NUMBER,
x_ineff_ctrl_attr_9 NUMBER,
x_ineff_ctrl_attr_10 NUMBER,
x_ineff_ctrl_attr_11 NUMBER,
x_ineff_ctrl_attr_12 NUMBER,
x_ineff_ctrl_attr_13 NUMBER,
x_ineff_ctrl_attr_14 NUMBER,
x_ineff_ctrl_attr_15 NUMBER,
x_ineff_ctrl_attr_16 NUMBER,
x_ineff_ctrl_attr_17 NUMBER,
x_ineff_ctrl_attr_18 NUMBER,
x_ineff_ctrl_attr_19 NUMBER,
x_ineff_ctrl_attr_20 NUMBER,
x_ineff_ctrl_attr_21 NUMBER,
x_ineff_ctrl_attr_22 NUMBER,
x_ineff_ctrl_attr_23 NUMBER,
x_ineff_ctrl_attr_24 NUMBER,
x_ineff_ctrl_attr_25 NUMBER,
x_ineff_ctrl_attr_26 NUMBER,
x_ineff_ctrl_attr_27 NUMBER,
x_ineff_ctrl_attr_28 NUMBER,
x_ineff_ctrl_attr_29 NUMBER,
x_ineff_ctrl_attr_30 NUMBER,
x_last_updated_by NUMBER,
x_last_update_date DATE ,
x_last_update_login NUMBER,
x_eval_ctrl_attr_1 NUMBER,
x_eval_ctrl_attr_2 NUMBER,
x_eval_ctrl_attr_3 NUMBER,
x_eval_ctrl_attr_4 NUMBER,
x_eval_ctrl_attr_5 NUMBER,
x_eval_ctrl_attr_6 NUMBER,
x_eval_ctrl_attr_7 NUMBER,
x_eval_ctrl_attr_8 NUMBER,
x_eval_ctrl_attr_9 NUMBER,
x_eval_ctrl_attr_10 NUMBER,
x_eval_ctrl_attr_11 NUMBER,
x_eval_ctrl_attr_12 NUMBER,
x_eval_ctrl_attr_13 NUMBER,
x_eval_ctrl_attr_14 NUMBER,
x_eval_ctrl_attr_15 NUMBER,
x_eval_ctrl_attr_16 NUMBER,
x_eval_ctrl_attr_17 NUMBER,
x_eval_ctrl_attr_18 NUMBER,
x_eval_ctrl_attr_19 NUMBER,
x_eval_ctrl_attr_20 NUMBER,
x_eval_ctrl_attr_21 NUMBER,
x_eval_ctrl_attr_22 NUMBER,
x_eval_ctrl_attr_23 NUMBER,
x_eval_ctrl_attr_24 NUMBER,
x_eval_ctrl_attr_25 NUMBER,
x_eval_ctrl_attr_26 NUMBER,
x_eval_ctrl_attr_27 NUMBER,
x_eval_ctrl_attr_28 NUMBER,
x_eval_ctrl_attr_29 NUMBER,
x_eval_ctrl_attr_30 NUMBER
) is
begin
declare
var_fin_certification_id number;
UPDATE
amw_fin_cert_ctrl_sum
SET
ineff_ctrl_attr_1= x_ineff_ctrl_attr_1
,ineff_ctrl_attr_2 = x_ineff_ctrl_attr_2
,ineff_ctrl_attr_3= x_ineff_ctrl_attr_3
,ineff_ctrl_attr_4= x_ineff_ctrl_attr_4
,ineff_ctrl_attr_5= x_ineff_ctrl_attr_5
,ineff_ctrl_attr_6= x_ineff_ctrl_attr_6
,ineff_ctrl_attr_7= x_ineff_ctrl_attr_7
,ineff_ctrl_attr_8= x_ineff_ctrl_attr_8
,ineff_ctrl_attr_9= x_ineff_ctrl_attr_9
,ineff_ctrl_attr_10= x_ineff_ctrl_attr_10
,ineff_ctrl_attr_11= x_ineff_ctrl_attr_11
,ineff_ctrl_attr_12= x_ineff_ctrl_attr_12
,ineff_ctrl_attr_13= x_ineff_ctrl_attr_13
,ineff_ctrl_attr_14= x_ineff_ctrl_attr_14
,ineff_ctrl_attr_15= x_ineff_ctrl_attr_15
,ineff_ctrl_attr_16= x_ineff_ctrl_attr_16
,ineff_ctrl_attr_17= x_ineff_ctrl_attr_17
,ineff_ctrl_attr_18= x_ineff_ctrl_attr_18
,ineff_ctrl_attr_19= x_ineff_ctrl_attr_19
,ineff_ctrl_attr_20= x_ineff_ctrl_attr_20
,ineff_ctrl_attr_21= x_ineff_ctrl_attr_21
,ineff_ctrl_attr_22= x_ineff_ctrl_attr_22
,ineff_ctrl_attr_23= x_ineff_ctrl_attr_23
,ineff_ctrl_attr_24= x_ineff_ctrl_attr_24
,ineff_ctrl_attr_25= x_ineff_ctrl_attr_25
,ineff_ctrl_attr_26= x_ineff_ctrl_attr_26
,ineff_ctrl_attr_27= x_ineff_ctrl_attr_27
,ineff_ctrl_attr_28= x_ineff_ctrl_attr_28
,ineff_ctrl_attr_29= x_ineff_ctrl_attr_29
,ineff_ctrl_attr_30= x_ineff_ctrl_attr_30,
eval_ctrl_attr_1 = nvl(eval_ctrl_attr_1,0) + x_eval_ctrl_attr_1 ,
eval_ctrl_attr_2 = nvl(eval_ctrl_attr_2,0) + x_eval_ctrl_attr_2 ,
eval_ctrl_attr_3 = nvl(eval_ctrl_attr_3,0) + x_eval_ctrl_attr_3 ,
eval_ctrl_attr_4 = nvl(eval_ctrl_attr_4,0) + x_eval_ctrl_attr_4 ,
eval_ctrl_attr_5 = nvl(eval_ctrl_attr_5,0) + x_eval_ctrl_attr_5 ,
eval_ctrl_attr_6 = nvl(eval_ctrl_attr_6,0) + x_eval_ctrl_attr_6 ,
eval_ctrl_attr_7 = nvl(eval_ctrl_attr_7,0) + x_eval_ctrl_attr_7 ,
eval_ctrl_attr_8 = nvl(eval_ctrl_attr_8,0) + x_eval_ctrl_attr_8 ,
eval_ctrl_attr_9 = nvl(eval_ctrl_attr_9,0) + x_eval_ctrl_attr_9 ,
eval_ctrl_attr_10 = nvl(eval_ctrl_attr_10,0) + x_eval_ctrl_attr_10 ,
eval_ctrl_attr_11 = nvl(eval_ctrl_attr_11,0) + x_eval_ctrl_attr_11 ,
eval_ctrl_attr_12 = nvl(eval_ctrl_attr_12,0) + x_eval_ctrl_attr_12 ,
eval_ctrl_attr_13 = nvl(eval_ctrl_attr_13,0) + x_eval_ctrl_attr_13 ,
eval_ctrl_attr_14 = nvl(eval_ctrl_attr_14,0) + x_eval_ctrl_attr_14 ,
eval_ctrl_attr_15 = nvl(eval_ctrl_attr_15,0) + x_eval_ctrl_attr_15 ,
eval_ctrl_attr_16 = nvl(eval_ctrl_attr_16,0) + x_eval_ctrl_attr_16 ,
eval_ctrl_attr_17 = nvl(eval_ctrl_attr_17,0) + x_eval_ctrl_attr_17 ,
eval_ctrl_attr_18 = nvl(eval_ctrl_attr_18,0) + x_eval_ctrl_attr_18 ,
eval_ctrl_attr_19 = nvl(eval_ctrl_attr_19,0) + x_eval_ctrl_attr_19 ,
eval_ctrl_attr_20 = nvl(eval_ctrl_attr_20,0) + x_eval_ctrl_attr_20 ,
eval_ctrl_attr_21 = nvl(eval_ctrl_attr_21,0) + x_eval_ctrl_attr_21 ,
eval_ctrl_attr_22 = nvl(eval_ctrl_attr_22,0) + x_eval_ctrl_attr_22 ,
eval_ctrl_attr_23 = nvl(eval_ctrl_attr_23,0) + x_eval_ctrl_attr_23 ,
eval_ctrl_attr_24 = nvl(eval_ctrl_attr_24,0) + x_eval_ctrl_attr_24 ,
eval_ctrl_attr_25 = nvl(eval_ctrl_attr_25,0) + x_eval_ctrl_attr_25 ,
eval_ctrl_attr_26 = nvl(eval_ctrl_attr_26,0) + x_eval_ctrl_attr_26 ,
eval_ctrl_attr_27 = nvl(eval_ctrl_attr_27,0) + x_eval_ctrl_attr_27 ,
eval_ctrl_attr_28 = nvl(eval_ctrl_attr_28,0) + x_eval_ctrl_attr_28 ,
eval_ctrl_attr_29 = nvl(eval_ctrl_attr_29,0) + x_eval_ctrl_attr_29,
eval_ctrl_attr_30 = nvl(eval_ctrl_attr_30,0) + x_eval_ctrl_attr_30
,ineff_ctrl_prcnt_1= round((x_ineff_ctrl_attr_1 / decode(total_ctrl_attr_1,null,1,0,1,total_ctrl_attr_1) ) * 100,0)
,ineff_ctrl_prcnt_2 = round((x_ineff_ctrl_attr_2 / decode(total_ctrl_attr_2,null,1,0,1,total_ctrl_attr_2) ) * 100,0)
,ineff_ctrl_prcnt_3= round((x_ineff_ctrl_attr_3 / decode(total_ctrl_attr_3,null,1,0,1,total_ctrl_attr_3) ) * 100,0)
,ineff_ctrl_prcnt_4= round((x_ineff_ctrl_attr_4 / decode(total_ctrl_attr_4,null,1,0,1,total_ctrl_attr_4) ) * 100,0)
,ineff_ctrl_prcnt_5= round((x_ineff_ctrl_attr_5 / decode(total_ctrl_attr_5,null,1,0,1,total_ctrl_attr_5) ) * 100,0)
,ineff_ctrl_prcnt_6= round((x_ineff_ctrl_attr_6 / decode(total_ctrl_attr_6,null,1,0,1,total_ctrl_attr_6) ) * 100,0)
,ineff_ctrl_prcnt_7= round((x_ineff_ctrl_attr_7 / decode(total_ctrl_attr_7,null,1,0,1,total_ctrl_attr_7) ) * 100,0)
,ineff_ctrl_prcnt_8= round((x_ineff_ctrl_attr_8 / decode(total_ctrl_attr_8,null,1,0,1,total_ctrl_attr_8) ) * 100,0)
,ineff_ctrl_prcnt_9= round((x_ineff_ctrl_attr_9 / decode(total_ctrl_attr_9,null,1,0,1,total_ctrl_attr_9) ) * 100,0)
,ineff_ctrl_prcnt_10= round((x_ineff_ctrl_attr_10 / decode(total_ctrl_attr_10,null,1,0,1,total_ctrl_attr_10) ) * 100,0)
,ineff_ctrl_prcnt_11= round((x_ineff_ctrl_attr_11 / decode(total_ctrl_attr_11,null,1,0,1,total_ctrl_attr_11) ) * 100,0)
,ineff_ctrl_prcnt_12= round((x_ineff_ctrl_attr_12 / decode(total_ctrl_attr_12,null,1,0,1,total_ctrl_attr_12) ) * 100,0)
,ineff_ctrl_prcnt_13= round((x_ineff_ctrl_attr_13 / decode(total_ctrl_attr_13,null,1,0,1,total_ctrl_attr_13) ) * 100,0)
,ineff_ctrl_prcnt_14= round((x_ineff_ctrl_attr_14 / decode(total_ctrl_attr_14,null,1,0,1,total_ctrl_attr_14) ) * 100,0)
,ineff_ctrl_prcnt_15= round((x_ineff_ctrl_attr_15 / decode(total_ctrl_attr_15,null,1,0,1,total_ctrl_attr_15) ) * 100,0)
,ineff_ctrl_prcnt_16= round((x_ineff_ctrl_attr_16 / decode(total_ctrl_attr_16,null,1,0,1,total_ctrl_attr_16) ) * 100,0)
,ineff_ctrl_prcnt_17= round((x_ineff_ctrl_attr_17 / decode(total_ctrl_attr_17,null,1,0,1,total_ctrl_attr_17) ) * 100,0)
,ineff_ctrl_prcnt_18= round((x_ineff_ctrl_attr_18 / decode(total_ctrl_attr_18,null,1,0,1,total_ctrl_attr_18) ) * 100,0)
,ineff_ctrl_prcnt_19= round((x_ineff_ctrl_attr_19 / decode(total_ctrl_attr_19,null,1,0,1,total_ctrl_attr_19) ) * 100,0)
,ineff_ctrl_prcnt_20= round((x_ineff_ctrl_attr_20 / decode(total_ctrl_attr_20,null,1,0,1,total_ctrl_attr_20) ) * 100,0)
,ineff_ctrl_prcnt_21= round((x_ineff_ctrl_attr_21 / decode(total_ctrl_attr_21,null,1,0,1,total_ctrl_attr_21) ) * 100,0)
,ineff_ctrl_prcnt_22= round((x_ineff_ctrl_attr_22 / decode(total_ctrl_attr_22,null,1,0,1,total_ctrl_attr_22) ) * 100,0)
,ineff_ctrl_prcnt_23= round((x_ineff_ctrl_attr_23 / decode(total_ctrl_attr_23,null,1,0,1,total_ctrl_attr_23) ) * 100,0)
,ineff_ctrl_prcnt_24= round((x_ineff_ctrl_attr_24 / decode(total_ctrl_attr_24,null,1,0,1,total_ctrl_attr_24) ) * 100,0)
,ineff_ctrl_prcnt_25= round((x_ineff_ctrl_attr_25 / decode(total_ctrl_attr_25,null,1,0,1,total_ctrl_attr_25) ) * 100,0)
,ineff_ctrl_prcnt_26= round((x_ineff_ctrl_attr_26 / decode(total_ctrl_attr_26,null,1,0,1,total_ctrl_attr_26) ) * 100,0)
,ineff_ctrl_prcnt_27= round((x_ineff_ctrl_attr_27 / decode(total_ctrl_attr_27,null,1,0,1,total_ctrl_attr_27) ) * 100,0)
,ineff_ctrl_prcnt_28= round((x_ineff_ctrl_attr_28 / decode(total_ctrl_attr_28,null,1,0,1,total_ctrl_attr_28) ) * 100,0)
,ineff_ctrl_prcnt_29= round((x_ineff_ctrl_attr_29 / decode(total_ctrl_attr_29,null,1,0,1,total_ctrl_attr_29) ) * 100,0)
,ineff_ctrl_prcnt_30= round((x_ineff_ctrl_attr_30 / decode(total_ctrl_attr_30,null,1,0,1,total_ctrl_attr_30) ) * 100,0)
,last_updated_by = x_last_updated_by
,last_update_date = x_last_update_date
,last_update_login = x_last_update_login
,object_version_number = object_version_number +1
where
fin_certification_id = x_fin_certification_id and
financial_statement_id = x_financial_statement_id and
NVL(financial_item_id, -1) = NVL(x_financial_item_id, -1) and
NVL(account_group_id, -1) = NVL(x_account_group_id, -1) and
nvl(natural_account_id, -1) = nvl(x_natural_account_id, -1) and
CTRL_ATTRIBUTE_TYPE = x_ctrl_attribute_type and
object_type = x_object_type ;
end UPDATE_FIN_ITEM_ROW;
procedure UPDATE_FIN_ACC_ASSERT_ROW (
x_fin_certification_id NUMBER ,
x_financial_statement_id NUMBER ,
x_financial_item_id NUMBER ,
x_account_group_id NUMBER,
x_natural_account_id NUMBER,
x_object_type VARCHAR2,
x_ctrl_attribute_type VARCHAR2,
x_ineff_ctrl_attr_1 NUMBER,
x_ineff_ctrl_attr_2 NUMBER,
x_ineff_ctrl_attr_3 NUMBER,
x_ineff_ctrl_attr_4 NUMBER,
x_ineff_ctrl_attr_5 NUMBER,
x_ineff_ctrl_attr_6 NUMBER,
x_ineff_ctrl_attr_7 NUMBER,
x_ineff_ctrl_attr_8 NUMBER,
x_ineff_ctrl_attr_9 NUMBER,
x_ineff_ctrl_attr_10 NUMBER,
x_ineff_ctrl_attr_11 NUMBER,
x_ineff_ctrl_attr_12 NUMBER,
x_ineff_ctrl_attr_13 NUMBER,
x_ineff_ctrl_attr_14 NUMBER,
x_ineff_ctrl_attr_15 NUMBER,
x_ineff_ctrl_attr_16 NUMBER,
x_ineff_ctrl_attr_17 NUMBER,
x_ineff_ctrl_attr_18 NUMBER,
x_ineff_ctrl_attr_19 NUMBER,
x_ineff_ctrl_attr_20 NUMBER,
x_ineff_ctrl_attr_21 NUMBER,
x_ineff_ctrl_attr_22 NUMBER,
x_ineff_ctrl_attr_23 NUMBER,
x_ineff_ctrl_attr_24 NUMBER,
x_ineff_ctrl_attr_25 NUMBER,
x_ineff_ctrl_attr_26 NUMBER,
x_ineff_ctrl_attr_27 NUMBER,
x_ineff_ctrl_attr_28 NUMBER,
x_ineff_ctrl_attr_29 NUMBER,
x_ineff_ctrl_attr_30 NUMBER,
x_last_updated_by NUMBER,
x_last_update_date DATE ,
x_last_update_login NUMBER,
x_eval_ctrl_attr_1 NUMBER,
x_eval_ctrl_attr_2 NUMBER,
x_eval_ctrl_attr_3 NUMBER,
x_eval_ctrl_attr_4 NUMBER,
x_eval_ctrl_attr_5 NUMBER,
x_eval_ctrl_attr_6 NUMBER,
x_eval_ctrl_attr_7 NUMBER,
x_eval_ctrl_attr_8 NUMBER,
x_eval_ctrl_attr_9 NUMBER,
x_eval_ctrl_attr_10 NUMBER,
x_eval_ctrl_attr_11 NUMBER,
x_eval_ctrl_attr_12 NUMBER,
x_eval_ctrl_attr_13 NUMBER,
x_eval_ctrl_attr_14 NUMBER,
x_eval_ctrl_attr_15 NUMBER,
x_eval_ctrl_attr_16 NUMBER,
x_eval_ctrl_attr_17 NUMBER,
x_eval_ctrl_attr_18 NUMBER,
x_eval_ctrl_attr_19 NUMBER,
x_eval_ctrl_attr_20 NUMBER,
x_eval_ctrl_attr_21 NUMBER,
x_eval_ctrl_attr_22 NUMBER,
x_eval_ctrl_attr_23 NUMBER,
x_eval_ctrl_attr_24 NUMBER,
x_eval_ctrl_attr_25 NUMBER,
x_eval_ctrl_attr_26 NUMBER,
x_eval_ctrl_attr_27 NUMBER,
x_eval_ctrl_attr_28 NUMBER,
x_eval_ctrl_attr_29 NUMBER,
x_eval_ctrl_attr_30 NUMBER,
x_acc_assert_flag1 VARCHAR2,
x_acc_assert_flag2 VARCHAR2,
x_acc_assert_flag3 VARCHAR2,
x_acc_assert_flag4 VARCHAR2,
x_acc_assert_flag5 VARCHAR2,
x_acc_assert_flag6 VARCHAR2,
x_acc_assert_flag7 VARCHAR2,
x_acc_assert_flag8 VARCHAR2,
x_acc_assert_flag9 VARCHAR2,
x_acc_assert_flag10 VARCHAR2,
x_acc_assert_flag11 VARCHAR2,
x_acc_assert_flag12 VARCHAR2,
x_acc_assert_flag13 VARCHAR2,
x_acc_assert_flag14 VARCHAR2,
x_acc_assert_flag15 VARCHAR2,
x_acc_assert_flag16 VARCHAR2,
x_acc_assert_flag17 VARCHAR2,
x_acc_assert_flag18 VARCHAR2,
x_acc_assert_flag19 VARCHAR2,
x_acc_assert_flag20 VARCHAR2,
x_acc_assert_flag21 VARCHAR2,
x_acc_assert_flag22 VARCHAR2,
x_acc_assert_flag23 VARCHAR2,
x_acc_assert_flag24 VARCHAR2,
x_acc_assert_flag25 VARCHAR2,
x_acc_assert_flag26 VARCHAR2,
x_acc_assert_flag27 VARCHAR2,
x_acc_assert_flag28 VARCHAR2,
x_acc_assert_flag29 VARCHAR2,
x_acc_assert_flag30 VARCHAR2
) is
begin
declare
var_fin_certification_id number;
UPDATE
amw_fin_cert_ctrl_sum
SET
ineff_ctrl_attr_1= x_ineff_ctrl_attr_1
,ineff_ctrl_attr_2 = x_ineff_ctrl_attr_2
,ineff_ctrl_attr_3= x_ineff_ctrl_attr_3
,ineff_ctrl_attr_4= x_ineff_ctrl_attr_4
,ineff_ctrl_attr_5= x_ineff_ctrl_attr_5
,ineff_ctrl_attr_6= x_ineff_ctrl_attr_6
,ineff_ctrl_attr_7= x_ineff_ctrl_attr_7
,ineff_ctrl_attr_8= x_ineff_ctrl_attr_8
,ineff_ctrl_attr_9= x_ineff_ctrl_attr_9
,ineff_ctrl_attr_10= x_ineff_ctrl_attr_10
,ineff_ctrl_attr_11= x_ineff_ctrl_attr_11
,ineff_ctrl_attr_12= x_ineff_ctrl_attr_12
,ineff_ctrl_attr_13= x_ineff_ctrl_attr_13
,ineff_ctrl_attr_14= x_ineff_ctrl_attr_14
,ineff_ctrl_attr_15= x_ineff_ctrl_attr_15
,ineff_ctrl_attr_16= x_ineff_ctrl_attr_16
,ineff_ctrl_attr_17= x_ineff_ctrl_attr_17
,ineff_ctrl_attr_18= x_ineff_ctrl_attr_18
,ineff_ctrl_attr_19= x_ineff_ctrl_attr_19
,ineff_ctrl_attr_20= x_ineff_ctrl_attr_20
,ineff_ctrl_attr_21= x_ineff_ctrl_attr_21
,ineff_ctrl_attr_22= x_ineff_ctrl_attr_22
,ineff_ctrl_attr_23= x_ineff_ctrl_attr_23
,ineff_ctrl_attr_24= x_ineff_ctrl_attr_24
,ineff_ctrl_attr_25= x_ineff_ctrl_attr_25
,ineff_ctrl_attr_26= x_ineff_ctrl_attr_26
,ineff_ctrl_attr_27= x_ineff_ctrl_attr_27
,ineff_ctrl_attr_28= x_ineff_ctrl_attr_28
,ineff_ctrl_attr_29= x_ineff_ctrl_attr_29
,ineff_ctrl_attr_30= x_ineff_ctrl_attr_30
,acc_assert_flag_1 = x_acc_assert_flag1
,acc_assert_flag_2 = x_acc_assert_flag2
,acc_assert_flag_3 = x_acc_assert_flag3
,acc_assert_flag_4 = x_acc_assert_flag4
,acc_assert_flag_5 = x_acc_assert_flag5
,acc_assert_flag_6 = x_acc_assert_flag6
,acc_assert_flag_7 = x_acc_assert_flag7
,acc_assert_flag_8 = x_acc_assert_flag8
,acc_assert_flag_9 = x_acc_assert_flag9
,acc_assert_flag_10 = x_acc_assert_flag10
,acc_assert_flag_11 = x_acc_assert_flag11
,acc_assert_flag_12 = x_acc_assert_flag12
,acc_assert_flag_13 = x_acc_assert_flag13
,acc_assert_flag_14 = x_acc_assert_flag14
,acc_assert_flag_15 = x_acc_assert_flag15
,acc_assert_flag_16 = x_acc_assert_flag16
,acc_assert_flag_17 = x_acc_assert_flag17
,acc_assert_flag_18 = x_acc_assert_flag18
,acc_assert_flag_19 = x_acc_assert_flag19
,acc_assert_flag_20 = x_acc_assert_flag20
,acc_assert_flag_21 = x_acc_assert_flag21
,acc_assert_flag_22 = x_acc_assert_flag22
,acc_assert_flag_23 = x_acc_assert_flag23
,acc_assert_flag_24 = x_acc_assert_flag24
,acc_assert_flag_25 = x_acc_assert_flag25
,acc_assert_flag_26 = x_acc_assert_flag26
,acc_assert_flag_27 = x_acc_assert_flag27
,acc_assert_flag_28 = x_acc_assert_flag28
,acc_assert_flag_29 = x_acc_assert_flag29
,acc_assert_flag_30 = x_acc_assert_flag30,
eval_ctrl_attr_1 = nvl(eval_ctrl_attr_1,0) + x_eval_ctrl_attr_1 ,
eval_ctrl_attr_2 = nvl(eval_ctrl_attr_2,0) + x_eval_ctrl_attr_2 ,
eval_ctrl_attr_3 = nvl(eval_ctrl_attr_3,0) + x_eval_ctrl_attr_3 ,
eval_ctrl_attr_4 = nvl(eval_ctrl_attr_4,0) + x_eval_ctrl_attr_4 ,
eval_ctrl_attr_5 = nvl(eval_ctrl_attr_5,0) + x_eval_ctrl_attr_5 ,
eval_ctrl_attr_6 = nvl(eval_ctrl_attr_6,0) + x_eval_ctrl_attr_6 ,
eval_ctrl_attr_7 = nvl(eval_ctrl_attr_7,0) + x_eval_ctrl_attr_7 ,
eval_ctrl_attr_8 = nvl(eval_ctrl_attr_8,0) + x_eval_ctrl_attr_8 ,
eval_ctrl_attr_9 = nvl(eval_ctrl_attr_9,0) + x_eval_ctrl_attr_9 ,
eval_ctrl_attr_10 = nvl(eval_ctrl_attr_10,0) + x_eval_ctrl_attr_10 ,
eval_ctrl_attr_11 = nvl(eval_ctrl_attr_11,0) + x_eval_ctrl_attr_11 ,
eval_ctrl_attr_12 = nvl(eval_ctrl_attr_12,0) + x_eval_ctrl_attr_12 ,
eval_ctrl_attr_13 = nvl(eval_ctrl_attr_13,0) + x_eval_ctrl_attr_13 ,
eval_ctrl_attr_14 = nvl(eval_ctrl_attr_14,0) + x_eval_ctrl_attr_14 ,
eval_ctrl_attr_15 = nvl(eval_ctrl_attr_15,0) + x_eval_ctrl_attr_15 ,
eval_ctrl_attr_16 = nvl(eval_ctrl_attr_16,0) + x_eval_ctrl_attr_16 ,
eval_ctrl_attr_17 = nvl(eval_ctrl_attr_17,0) + x_eval_ctrl_attr_17 ,
eval_ctrl_attr_18 = nvl(eval_ctrl_attr_18,0) + x_eval_ctrl_attr_18 ,
eval_ctrl_attr_19 = nvl(eval_ctrl_attr_19,0) + x_eval_ctrl_attr_19 ,
eval_ctrl_attr_20 = nvl(eval_ctrl_attr_20,0) + x_eval_ctrl_attr_20 ,
eval_ctrl_attr_21 = nvl(eval_ctrl_attr_21,0) + x_eval_ctrl_attr_21 ,
eval_ctrl_attr_22 = nvl(eval_ctrl_attr_22,0) + x_eval_ctrl_attr_22 ,
eval_ctrl_attr_23 = nvl(eval_ctrl_attr_23,0) + x_eval_ctrl_attr_23 ,
eval_ctrl_attr_24 = nvl(eval_ctrl_attr_24,0) + x_eval_ctrl_attr_24 ,
eval_ctrl_attr_25 = nvl(eval_ctrl_attr_25,0) + x_eval_ctrl_attr_25 ,
eval_ctrl_attr_26 = nvl(eval_ctrl_attr_26,0) + x_eval_ctrl_attr_26 ,
eval_ctrl_attr_27 = nvl(eval_ctrl_attr_27,0) + x_eval_ctrl_attr_27 ,
eval_ctrl_attr_28 = nvl(eval_ctrl_attr_28,0) + x_eval_ctrl_attr_28 ,
eval_ctrl_attr_29 = nvl(eval_ctrl_attr_29,0) + x_eval_ctrl_attr_29 ,
eval_ctrl_attr_30 = nvl(eval_ctrl_attr_30,0) + x_eval_ctrl_attr_30
,ineff_ctrl_prcnt_1= round((x_ineff_ctrl_attr_1 / decode(total_ctrl_attr_1,null,1,0,1,total_ctrl_attr_1) ) * 100,0)
,ineff_ctrl_prcnt_2 = round((x_ineff_ctrl_attr_2 / decode(total_ctrl_attr_2,null,1,0,1,total_ctrl_attr_2) ) * 100,0)
,ineff_ctrl_prcnt_3= round((x_ineff_ctrl_attr_3 / decode(total_ctrl_attr_3,null,1,0,1,total_ctrl_attr_3) ) * 100,0)
,ineff_ctrl_prcnt_4= round((x_ineff_ctrl_attr_4 / decode(total_ctrl_attr_4,null,1,0,1,total_ctrl_attr_4) ) * 100,0)
,ineff_ctrl_prcnt_5= round((x_ineff_ctrl_attr_5 / decode(total_ctrl_attr_5,null,1,0,1,total_ctrl_attr_5) ) * 100,0)
,ineff_ctrl_prcnt_6= round((x_ineff_ctrl_attr_6 / decode(total_ctrl_attr_6,null,1,0,1,total_ctrl_attr_6) ) * 100,0)
,ineff_ctrl_prcnt_7= round((x_ineff_ctrl_attr_7 / decode(total_ctrl_attr_7,null,1,0,1,total_ctrl_attr_7) ) * 100,0)
,ineff_ctrl_prcnt_8= round((x_ineff_ctrl_attr_8 / decode(total_ctrl_attr_8,null,1,0,1,total_ctrl_attr_8) ) * 100,0)
,ineff_ctrl_prcnt_9= round((x_ineff_ctrl_attr_9 / decode(total_ctrl_attr_9,null,1,0,1,total_ctrl_attr_9) ) * 100,0)
,ineff_ctrl_prcnt_10= round((x_ineff_ctrl_attr_10 / decode(total_ctrl_attr_10,null,1,0,1,total_ctrl_attr_10) ) * 100,0)
,ineff_ctrl_prcnt_11= round((x_ineff_ctrl_attr_11 / decode(total_ctrl_attr_11,null,1,0,1,total_ctrl_attr_11) ) * 100,0)
,ineff_ctrl_prcnt_12= round((x_ineff_ctrl_attr_12 / decode(total_ctrl_attr_12,null,1,0,1,total_ctrl_attr_12) ) * 100,0)
,ineff_ctrl_prcnt_13= round((x_ineff_ctrl_attr_13 / decode(total_ctrl_attr_13,null,1,0,1,total_ctrl_attr_13) ) * 100,0)
,ineff_ctrl_prcnt_14= round((x_ineff_ctrl_attr_14 / decode(total_ctrl_attr_14,null,1,0,1,total_ctrl_attr_14) ) * 100,0)
,ineff_ctrl_prcnt_15= round((x_ineff_ctrl_attr_15 / decode(total_ctrl_attr_15,null,1,0,1,total_ctrl_attr_15) ) * 100,0)
,ineff_ctrl_prcnt_16= round((x_ineff_ctrl_attr_16 / decode(total_ctrl_attr_16,null,1,0,1,total_ctrl_attr_16) ) * 100,0)
,ineff_ctrl_prcnt_17= round((x_ineff_ctrl_attr_17 / decode(total_ctrl_attr_17,null,1,0,1,total_ctrl_attr_17) ) * 100,0)
,ineff_ctrl_prcnt_18= round((x_ineff_ctrl_attr_18 / decode(total_ctrl_attr_18,null,1,0,1,total_ctrl_attr_18) ) * 100,0)
,ineff_ctrl_prcnt_19= round((x_ineff_ctrl_attr_19 / decode(total_ctrl_attr_19,null,1,0,1,total_ctrl_attr_19) ) * 100,0)
,ineff_ctrl_prcnt_20= round((x_ineff_ctrl_attr_20 / decode(total_ctrl_attr_20,null,1,0,1,total_ctrl_attr_20) ) * 100,0)
,ineff_ctrl_prcnt_21= round((x_ineff_ctrl_attr_21 / decode(total_ctrl_attr_21,null,1,0,1,total_ctrl_attr_21) ) * 100,0)
,ineff_ctrl_prcnt_22= round((x_ineff_ctrl_attr_22 / decode(total_ctrl_attr_22,null,1,0,1,total_ctrl_attr_22) ) * 100,0)
,ineff_ctrl_prcnt_23= round((x_ineff_ctrl_attr_23 / decode(total_ctrl_attr_23,null,1,0,1,total_ctrl_attr_23) ) * 100,0)
,ineff_ctrl_prcnt_24= round((x_ineff_ctrl_attr_24 / decode(total_ctrl_attr_24,null,1,0,1,total_ctrl_attr_24) ) * 100,0)
,ineff_ctrl_prcnt_25= round((x_ineff_ctrl_attr_25 / decode(total_ctrl_attr_25,null,1,0,1,total_ctrl_attr_25) ) * 100,0)
,ineff_ctrl_prcnt_26= round((x_ineff_ctrl_attr_26 / decode(total_ctrl_attr_26,null,1,0,1,total_ctrl_attr_26) ) * 100,0)
,ineff_ctrl_prcnt_27= round((x_ineff_ctrl_attr_27 / decode(total_ctrl_attr_27,null,1,0,1,total_ctrl_attr_27) ) * 100,0)
,ineff_ctrl_prcnt_28= round((x_ineff_ctrl_attr_28 / decode(total_ctrl_attr_28,null,1,0,1,total_ctrl_attr_28) ) * 100,0)
,ineff_ctrl_prcnt_29= round((x_ineff_ctrl_attr_29 / decode(total_ctrl_attr_29,null,1,0,1,total_ctrl_attr_29) ) * 100,0)
,ineff_ctrl_prcnt_30= round((x_ineff_ctrl_attr_30 / decode(total_ctrl_attr_30,null,1,0,1,total_ctrl_attr_30) ) * 100,0)
,last_updated_by = x_last_updated_by ,
last_update_date = x_last_update_date ,
last_update_login = x_last_update_login
,object_version_number = object_version_number +1
where
fin_certification_id = x_fin_certification_id and
account_group_id = x_account_group_id and
natural_account_id = x_natural_account_id and
CTRL_ATTRIBUTE_TYPE = x_ctrl_attribute_type and
object_type = x_object_type ;
end UPDATE_FIN_ACC_ASSERT_ROW ;
procedure UPDATE_FIN_ACC_ROW (
x_fin_certification_id NUMBER ,
x_financial_statement_id NUMBER ,
x_financial_item_id NUMBER ,
x_account_group_id NUMBER,
x_natural_account_id NUMBER,
x_object_type VARCHAR2,
x_ctrl_attribute_type VARCHAR2,
x_ineff_ctrl_attr_1 NUMBER,
x_ineff_ctrl_attr_2 NUMBER,
x_ineff_ctrl_attr_3 NUMBER,
x_ineff_ctrl_attr_4 NUMBER,
x_ineff_ctrl_attr_5 NUMBER,
x_ineff_ctrl_attr_6 NUMBER,
x_ineff_ctrl_attr_7 NUMBER,
x_ineff_ctrl_attr_8 NUMBER,
x_ineff_ctrl_attr_9 NUMBER,
x_ineff_ctrl_attr_10 NUMBER,
x_ineff_ctrl_attr_11 NUMBER,
x_ineff_ctrl_attr_12 NUMBER,
x_ineff_ctrl_attr_13 NUMBER,
x_ineff_ctrl_attr_14 NUMBER,
x_ineff_ctrl_attr_15 NUMBER,
x_ineff_ctrl_attr_16 NUMBER,
x_ineff_ctrl_attr_17 NUMBER,
x_ineff_ctrl_attr_18 NUMBER,
x_ineff_ctrl_attr_19 NUMBER,
x_ineff_ctrl_attr_20 NUMBER,
x_ineff_ctrl_attr_21 NUMBER,
x_ineff_ctrl_attr_22 NUMBER,
x_ineff_ctrl_attr_23 NUMBER,
x_ineff_ctrl_attr_24 NUMBER,
x_ineff_ctrl_attr_25 NUMBER,
x_ineff_ctrl_attr_26 NUMBER,
x_ineff_ctrl_attr_27 NUMBER,
x_ineff_ctrl_attr_28 NUMBER,
x_ineff_ctrl_attr_29 NUMBER,
x_ineff_ctrl_attr_30 NUMBER,
x_last_updated_by NUMBER,
x_last_update_date DATE ,
x_last_update_login NUMBER,
x_eval_ctrl_attr_1 NUMBER,
x_eval_ctrl_attr_2 NUMBER,
x_eval_ctrl_attr_3 NUMBER,
x_eval_ctrl_attr_4 NUMBER,
x_eval_ctrl_attr_5 NUMBER,
x_eval_ctrl_attr_6 NUMBER,
x_eval_ctrl_attr_7 NUMBER,
x_eval_ctrl_attr_8 NUMBER,
x_eval_ctrl_attr_9 NUMBER,
x_eval_ctrl_attr_10 NUMBER,
x_eval_ctrl_attr_11 NUMBER,
x_eval_ctrl_attr_12 NUMBER,
x_eval_ctrl_attr_13 NUMBER,
x_eval_ctrl_attr_14 NUMBER,
x_eval_ctrl_attr_15 NUMBER,
x_eval_ctrl_attr_16 NUMBER,
x_eval_ctrl_attr_17 NUMBER,
x_eval_ctrl_attr_18 NUMBER,
x_eval_ctrl_attr_19 NUMBER,
x_eval_ctrl_attr_20 NUMBER,
x_eval_ctrl_attr_21 NUMBER,
x_eval_ctrl_attr_22 NUMBER,
x_eval_ctrl_attr_23 NUMBER,
x_eval_ctrl_attr_24 NUMBER,
x_eval_ctrl_attr_25 NUMBER,
x_eval_ctrl_attr_26 NUMBER,
x_eval_ctrl_attr_27 NUMBER,
x_eval_ctrl_attr_28 NUMBER,
x_eval_ctrl_attr_29 NUMBER,
x_eval_ctrl_attr_30 NUMBER
) is
begin
declare
var_fin_certification_id number;
UPDATE
amw_fin_cert_ctrl_sum
SET
ineff_ctrl_attr_1= x_ineff_ctrl_attr_1
,ineff_ctrl_attr_2 = x_ineff_ctrl_attr_2
,ineff_ctrl_attr_3= x_ineff_ctrl_attr_3
,ineff_ctrl_attr_4= x_ineff_ctrl_attr_4
,ineff_ctrl_attr_5= x_ineff_ctrl_attr_5
,ineff_ctrl_attr_6= x_ineff_ctrl_attr_6
,ineff_ctrl_attr_7= x_ineff_ctrl_attr_7
,ineff_ctrl_attr_8= x_ineff_ctrl_attr_8
,ineff_ctrl_attr_9= x_ineff_ctrl_attr_9
,ineff_ctrl_attr_10= x_ineff_ctrl_attr_10
,ineff_ctrl_attr_11= x_ineff_ctrl_attr_11
,ineff_ctrl_attr_12= x_ineff_ctrl_attr_12
,ineff_ctrl_attr_13= x_ineff_ctrl_attr_13
,ineff_ctrl_attr_14= x_ineff_ctrl_attr_14
,ineff_ctrl_attr_15= x_ineff_ctrl_attr_15
,ineff_ctrl_attr_16= x_ineff_ctrl_attr_16
,ineff_ctrl_attr_17= x_ineff_ctrl_attr_17
,ineff_ctrl_attr_18= x_ineff_ctrl_attr_18
,ineff_ctrl_attr_19= x_ineff_ctrl_attr_19
,ineff_ctrl_attr_20= x_ineff_ctrl_attr_20
,ineff_ctrl_attr_21= x_ineff_ctrl_attr_21
,ineff_ctrl_attr_22= x_ineff_ctrl_attr_22
,ineff_ctrl_attr_23= x_ineff_ctrl_attr_23
,ineff_ctrl_attr_24= x_ineff_ctrl_attr_24
,ineff_ctrl_attr_25= x_ineff_ctrl_attr_25
,ineff_ctrl_attr_26= x_ineff_ctrl_attr_26
,ineff_ctrl_attr_27= x_ineff_ctrl_attr_27
,ineff_ctrl_attr_28= x_ineff_ctrl_attr_28
,ineff_ctrl_attr_29= x_ineff_ctrl_attr_29
,ineff_ctrl_attr_30= x_ineff_ctrl_attr_30,
eval_ctrl_attr_1 = nvl(eval_ctrl_attr_1,0) + x_eval_ctrl_attr_1 ,
eval_ctrl_attr_2 = nvl(eval_ctrl_attr_2,0) + x_eval_ctrl_attr_2 ,
eval_ctrl_attr_3 = nvl(eval_ctrl_attr_3,0) + x_eval_ctrl_attr_3 ,
eval_ctrl_attr_4 = nvl(eval_ctrl_attr_4,0) + x_eval_ctrl_attr_4 ,
eval_ctrl_attr_5 = nvl(eval_ctrl_attr_5,0) + x_eval_ctrl_attr_5 ,
eval_ctrl_attr_6 = nvl(eval_ctrl_attr_6,0) + x_eval_ctrl_attr_6 ,
eval_ctrl_attr_7 = nvl(eval_ctrl_attr_7,0) + x_eval_ctrl_attr_7 ,
eval_ctrl_attr_8 = nvl(eval_ctrl_attr_8,0) + x_eval_ctrl_attr_8 ,
eval_ctrl_attr_9 = nvl(eval_ctrl_attr_9,0) + x_eval_ctrl_attr_9 ,
eval_ctrl_attr_10 = nvl(eval_ctrl_attr_10,0) + x_eval_ctrl_attr_10 ,
eval_ctrl_attr_11 = nvl(eval_ctrl_attr_11,0) + x_eval_ctrl_attr_11 ,
eval_ctrl_attr_12 = nvl(eval_ctrl_attr_12,0) + x_eval_ctrl_attr_12 ,
eval_ctrl_attr_13 = nvl(eval_ctrl_attr_13,0) + x_eval_ctrl_attr_13 ,
eval_ctrl_attr_14 = nvl(eval_ctrl_attr_14,0) + x_eval_ctrl_attr_14 ,
eval_ctrl_attr_15 = nvl(eval_ctrl_attr_15,0) + x_eval_ctrl_attr_15 ,
eval_ctrl_attr_16 = nvl(eval_ctrl_attr_16,0) + x_eval_ctrl_attr_16 ,
eval_ctrl_attr_17 = nvl(eval_ctrl_attr_17,0) + x_eval_ctrl_attr_17 ,
eval_ctrl_attr_18 = nvl(eval_ctrl_attr_18,0) + x_eval_ctrl_attr_18 ,
eval_ctrl_attr_19 = nvl(eval_ctrl_attr_19,0) + x_eval_ctrl_attr_19 ,
eval_ctrl_attr_20 = nvl(eval_ctrl_attr_20,0) + x_eval_ctrl_attr_20 ,
eval_ctrl_attr_21 = nvl(eval_ctrl_attr_21,0) + x_eval_ctrl_attr_21 ,
eval_ctrl_attr_22 = nvl(eval_ctrl_attr_22,0) + x_eval_ctrl_attr_22 ,
eval_ctrl_attr_23 = nvl(eval_ctrl_attr_23,0) + x_eval_ctrl_attr_23 ,
eval_ctrl_attr_24 = nvl(eval_ctrl_attr_24,0) + x_eval_ctrl_attr_24 ,
eval_ctrl_attr_25 = nvl(eval_ctrl_attr_25,0) + x_eval_ctrl_attr_25 ,
eval_ctrl_attr_26 = nvl(eval_ctrl_attr_26,0) + x_eval_ctrl_attr_26 ,
eval_ctrl_attr_27 = nvl(eval_ctrl_attr_27,0) + x_eval_ctrl_attr_27 ,
eval_ctrl_attr_28 = nvl(eval_ctrl_attr_28,0) + x_eval_ctrl_attr_28 ,
eval_ctrl_attr_29 = nvl(eval_ctrl_attr_29,0) + x_eval_ctrl_attr_29 ,
eval_ctrl_attr_30 = nvl(eval_ctrl_attr_30,0) + x_eval_ctrl_attr_30
,ineff_ctrl_prcnt_1= round((x_ineff_ctrl_attr_1 / decode(total_ctrl_attr_1,null,1,0,1,total_ctrl_attr_1) ) * 100,0)
,ineff_ctrl_prcnt_2 = round((x_ineff_ctrl_attr_2 / decode(total_ctrl_attr_2,null,1,0,1,total_ctrl_attr_2) ) * 100,0)
,ineff_ctrl_prcnt_3= round((x_ineff_ctrl_attr_3 / decode(total_ctrl_attr_3,null,1,0,1,total_ctrl_attr_3) ) * 100,0)
,ineff_ctrl_prcnt_4= round((x_ineff_ctrl_attr_4 / decode(total_ctrl_attr_4,null,1,0,1,total_ctrl_attr_4) ) * 100,0)
,ineff_ctrl_prcnt_5= round((x_ineff_ctrl_attr_5 / decode(total_ctrl_attr_5,null,1,0,1,total_ctrl_attr_5) ) * 100,0)
,ineff_ctrl_prcnt_6= round((x_ineff_ctrl_attr_6 / decode(total_ctrl_attr_6,null,1,0,1,total_ctrl_attr_6) ) * 100,0)
,ineff_ctrl_prcnt_7= round((x_ineff_ctrl_attr_7 / decode(total_ctrl_attr_7,null,1,0,1,total_ctrl_attr_7) ) * 100,0)
,ineff_ctrl_prcnt_8= round((x_ineff_ctrl_attr_8 / decode(total_ctrl_attr_8,null,1,0,1,total_ctrl_attr_8) ) * 100,0)
,ineff_ctrl_prcnt_9= round((x_ineff_ctrl_attr_9 / decode(total_ctrl_attr_9,null,1,0,1,total_ctrl_attr_9) ) * 100,0)
,ineff_ctrl_prcnt_10= round((x_ineff_ctrl_attr_10 / decode(total_ctrl_attr_10,null,1,0,1,total_ctrl_attr_10) ) * 100,0)
,ineff_ctrl_prcnt_11= round((x_ineff_ctrl_attr_11 / decode(total_ctrl_attr_11,null,1,0,1,total_ctrl_attr_11) ) * 100,0)
,ineff_ctrl_prcnt_12= round((x_ineff_ctrl_attr_12 / decode(total_ctrl_attr_12,null,1,0,1,total_ctrl_attr_12) ) * 100,0)
,ineff_ctrl_prcnt_13= round((x_ineff_ctrl_attr_13 / decode(total_ctrl_attr_13,null,1,0,1,total_ctrl_attr_13) ) * 100,0)
,ineff_ctrl_prcnt_14= round((x_ineff_ctrl_attr_14 / decode(total_ctrl_attr_14,null,1,0,1,total_ctrl_attr_14) ) * 100,0)
,ineff_ctrl_prcnt_15= round((x_ineff_ctrl_attr_15 / decode(total_ctrl_attr_15,null,1,0,1,total_ctrl_attr_15) ) * 100,0)
,ineff_ctrl_prcnt_16= round((x_ineff_ctrl_attr_16 / decode(total_ctrl_attr_16,null,1,0,1,total_ctrl_attr_16) ) * 100,0)
,ineff_ctrl_prcnt_17= round((x_ineff_ctrl_attr_17 / decode(total_ctrl_attr_17,null,1,0,1,total_ctrl_attr_17) ) * 100,0)
,ineff_ctrl_prcnt_18= round((x_ineff_ctrl_attr_18 / decode(total_ctrl_attr_18,null,1,0,1,total_ctrl_attr_18) ) * 100,0)
,ineff_ctrl_prcnt_19= round((x_ineff_ctrl_attr_19 / decode(total_ctrl_attr_19,null,1,0,1,total_ctrl_attr_19) ) * 100,0)
,ineff_ctrl_prcnt_20= round((x_ineff_ctrl_attr_20 / decode(total_ctrl_attr_20,null,1,0,1,total_ctrl_attr_20) ) * 100,0)
,ineff_ctrl_prcnt_21= round((x_ineff_ctrl_attr_21 / decode(total_ctrl_attr_21,null,1,0,1,total_ctrl_attr_21) ) * 100,0)
,ineff_ctrl_prcnt_22= round((x_ineff_ctrl_attr_22 / decode(total_ctrl_attr_22,null,1,0,1,total_ctrl_attr_22) ) * 100,0)
,ineff_ctrl_prcnt_23= round((x_ineff_ctrl_attr_23 / decode(total_ctrl_attr_23,null,1,0,1,total_ctrl_attr_23) ) * 100,0)
,ineff_ctrl_prcnt_24= round((x_ineff_ctrl_attr_24 / decode(total_ctrl_attr_24,null,1,0,1,total_ctrl_attr_24) ) * 100,0)
,ineff_ctrl_prcnt_25= round((x_ineff_ctrl_attr_25 / decode(total_ctrl_attr_25,null,1,0,1,total_ctrl_attr_25) ) * 100,0)
,ineff_ctrl_prcnt_26= round((x_ineff_ctrl_attr_26 / decode(total_ctrl_attr_26,null,1,0,1,total_ctrl_attr_26) ) * 100,0)
,ineff_ctrl_prcnt_27= round((x_ineff_ctrl_attr_27 / decode(total_ctrl_attr_27,null,1,0,1,total_ctrl_attr_27) ) * 100,0)
,ineff_ctrl_prcnt_28= round((x_ineff_ctrl_attr_28 / decode(total_ctrl_attr_28,null,1,0,1,total_ctrl_attr_28) ) * 100,0)
,ineff_ctrl_prcnt_29= round((x_ineff_ctrl_attr_29 / decode(total_ctrl_attr_29,null,1,0,1,total_ctrl_attr_29) ) * 100,0)
,ineff_ctrl_prcnt_30= round((x_ineff_ctrl_attr_30 / decode(total_ctrl_attr_30,null,1,0,1,total_ctrl_attr_30) ) * 100,0)
,last_updated_by = x_last_updated_by ,
last_update_date = x_last_update_date ,
last_update_login = x_last_update_login
,object_version_number = object_version_number +1
where
fin_certification_id = x_fin_certification_id and
account_group_id = x_account_group_id and
natural_account_id = x_natural_account_id and
CTRL_ATTRIBUTE_TYPE = x_ctrl_attribute_type and
object_type = x_object_type ;
end UPDATE_FIN_ACC_ROW;
PROCEDURE update_acc_assert_flags
( P_ACCOUNT_ID number ) is
begin
declare
ctr integer :=0;
select
ASSERTION_CODE
from
amw_account_assertions
where
NATURAL_ACCOUNT_ID =P_ACCOUNT_ID ;
select
distinct
ASSERTION_CODE
from
amw_account_assertions
where
((NATURAL_ACCOUNT_ID =P_ACCOUNT_ID) or (NATURAL_ACCOUNT_ID in (select CHILD_NATURAL_ACCOUNT_ID from amw_fin_key_acct_flat
where PARENT_NATURAL_ACCOUNT_ID =P_ACCOUNT_ID and ACCOUNT_GROUP_ID=M_ACCOUNT_GROUP_ID)));
select distinct ACCOUNT_GROUP_ID from AMW_FIN_ITEMS_KEY_ACC where STATEMENT_GROUP_ID = M_STATEMENT_GROUP_ID
and FINANCIAL_STATEMENT_ID = M_STATEMENT_ID;
select
distinct
cert.certification_id ,
cert.STATEMENT_GROUP_ID,
cert.FINANCIAL_STATEMENT_ID,
ctrl_attr_code_1,
ctrl_attr_code_2,
ctrl_attr_code_3,
ctrl_attr_code_4,
ctrl_attr_code_5,
ctrl_attr_code_6,
ctrl_attr_code_7,
ctrl_attr_code_8,
ctrl_attr_code_9,
ctrl_attr_code_10,
ctrl_attr_code_11,
ctrl_attr_code_12,
ctrl_attr_code_13,
ctrl_attr_code_14,
ctrl_attr_code_15,
ctrl_attr_code_16,
ctrl_attr_code_17,
ctrl_attr_code_18,
ctrl_attr_code_19,
ctrl_attr_code_20,
ctrl_attr_code_21,
ctrl_attr_code_22,
ctrl_attr_code_23,
ctrl_attr_code_24,
ctrl_attr_code_25,
ctrl_attr_code_26,
ctrl_attr_code_27,
ctrl_attr_code_28,
ctrl_attr_code_29,
ctrl_attr_code_30,
ineff_ctrl_attr_1,
ineff_ctrl_attr_2,
ineff_ctrl_attr_3,
ineff_ctrl_attr_4,
ineff_ctrl_attr_5,
ineff_ctrl_attr_6,
ineff_ctrl_attr_7,
ineff_ctrl_attr_8,
ineff_ctrl_attr_9,
ineff_ctrl_attr_10,
ineff_ctrl_attr_11,
ineff_ctrl_attr_12,
ineff_ctrl_attr_13,
ineff_ctrl_attr_14,
ineff_ctrl_attr_15,
ineff_ctrl_attr_16,
ineff_ctrl_attr_17,
ineff_ctrl_attr_18,
ineff_ctrl_attr_19,
ineff_ctrl_attr_20,
ineff_ctrl_attr_21,
ineff_ctrl_attr_22,
ineff_ctrl_attr_23,
ineff_ctrl_attr_24,
ineff_ctrl_attr_25,
ineff_ctrl_attr_26,
ineff_ctrl_attr_27,
ineff_ctrl_attr_28,
ineff_ctrl_attr_29,
ineff_ctrl_attr_30,
total_ctrl_attr_1,
total_ctrl_attr_2,
total_ctrl_attr_3,
total_ctrl_attr_4,
total_ctrl_attr_5,
total_ctrl_attr_6,
total_ctrl_attr_7,
total_ctrl_attr_8,
total_ctrl_attr_9,
total_ctrl_attr_10,
total_ctrl_attr_11,
total_ctrl_attr_12,
total_ctrl_attr_13,
total_ctrl_attr_14,
total_ctrl_attr_15,
total_ctrl_attr_16,
total_ctrl_attr_17,
total_ctrl_attr_18,
total_ctrl_attr_19,
total_ctrl_attr_20,
total_ctrl_attr_21,
total_ctrl_attr_22,
total_ctrl_attr_23,
total_ctrl_attr_24,
total_ctrl_attr_25,
total_ctrl_attr_26,
total_ctrl_attr_27,
total_ctrl_attr_28,
total_ctrl_attr_29,
total_ctrl_attr_30
from
amw_certification_vl cert,
amw_fin_cert_ctrl_sum ctrlsum
where
cert.certification_id = ctrlsum.fin_certification_id and
ctrl_attribute_type = 'CTRL_ASSERTIONS'
and
(CERTIFICATION_STATUS= 'ACTIVE' or CERTIFICATION_STATUS= 'DRAFT')
and cert.OBJECT_TYPE ='FIN_STMT' AND
ctrlsum.OBJECT_TYPE='ACCOUNT' AND
ctrlsum.NATURAL_ACCOUNT_ID=P_ACCOUNT_ID ;
amw_fin_coso_views_pvt.UPDATE_CTRLSUM_FLAG(
x_fin_certification_id => m_cert_id ,
x_natural_account_id => P_ACCOUNT_ID ,
x_ctrl_attribute_type => m_ctrl_attribute_type ,
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_object_version_number => null,
x_acc_assert_flag1 => m_acc_assert_flag(1),
x_acc_assert_flag2 => m_acc_assert_flag(2),
x_acc_assert_flag3 => m_acc_assert_flag(3),
x_acc_assert_flag4 => m_acc_assert_flag(4),
x_acc_assert_flag5 => m_acc_assert_flag(5),
x_acc_assert_flag6 => m_acc_assert_flag(6),
x_acc_assert_flag7 => m_acc_assert_flag(7),
x_acc_assert_flag8 => m_acc_assert_flag(8),
x_acc_assert_flag9 => m_acc_assert_flag(9),
x_acc_assert_flag10 => m_acc_assert_flag(10),
x_acc_assert_flag11 => m_acc_assert_flag(11),
x_acc_assert_flag12 => m_acc_assert_flag(12),
x_acc_assert_flag13 => m_acc_assert_flag(13),
x_acc_assert_flag14 => m_acc_assert_flag(14),
x_acc_assert_flag15 => m_acc_assert_flag(15),
x_acc_assert_flag16 => m_acc_assert_flag(16),
x_acc_assert_flag17 => m_acc_assert_flag(17),
x_acc_assert_flag18 => m_acc_assert_flag(18),
x_acc_assert_flag19 => m_acc_assert_flag(19),
x_acc_assert_flag20 => m_acc_assert_flag(20),
x_acc_assert_flag21 => m_acc_assert_flag(21),
x_acc_assert_flag22 => m_acc_assert_flag(22),
x_acc_assert_flag23 => m_acc_assert_flag(23),
x_acc_assert_flag24 => m_acc_assert_flag(24),
x_acc_assert_flag25 => m_acc_assert_flag(25),
x_acc_assert_flag26 => m_acc_assert_flag(26),
x_acc_assert_flag27 => m_acc_assert_flag(27),
x_acc_assert_flag28 => m_acc_assert_flag(28),
x_acc_assert_flag29 => m_acc_assert_flag(29),
x_acc_assert_flag30 => m_acc_assert_flag(30),
x_display_flag => m_display_flag );
amw_fin_coso_views_pvt.update_parentacc_assert_flags
(P_ACCOUNT_ID =>P_ACCOUNT_ID ,
P_CERTFICATION_ID => m_cert_id ,
P_ACCOUNT_GROUP_ID => M_ACCOUNT_GROUP_ID);
amw_fin_coso_views_pvt.update_item_assert_flags
( P_NATRL_ACCOUNT_ID => P_ACCOUNT_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_acc_assert_flags
PROCEDURE update_item_assert_flags
( P_NATRL_ACCOUNT_ID number ) is
begin
declare
ctr integer :=0;
select
distinct
cert.certification_id ,
ctrlsum.financial_item_id,
ctrl_attr_code_1,
ctrl_attr_code_2,
ctrl_attr_code_3,
ctrl_attr_code_4,
ctrl_attr_code_5,
ctrl_attr_code_6,
ctrl_attr_code_7,
ctrl_attr_code_8,
ctrl_attr_code_9,
ctrl_attr_code_10,
ctrl_attr_code_11,
ctrl_attr_code_12,
ctrl_attr_code_13,
ctrl_attr_code_14,
ctrl_attr_code_15,
ctrl_attr_code_16,
ctrl_attr_code_17,
ctrl_attr_code_18,
ctrl_attr_code_19,
ctrl_attr_code_20,
ctrl_attr_code_21,
ctrl_attr_code_22,
ctrl_attr_code_23,
ctrl_attr_code_24,
ctrl_attr_code_25,
ctrl_attr_code_26,
ctrl_attr_code_27,
ctrl_attr_code_28,
ctrl_attr_code_29,
ctrl_attr_code_30,
ineff_ctrl_attr_1,
ineff_ctrl_attr_2,
ineff_ctrl_attr_3,
ineff_ctrl_attr_4,
ineff_ctrl_attr_5,
ineff_ctrl_attr_6,
ineff_ctrl_attr_7,
ineff_ctrl_attr_8,
ineff_ctrl_attr_9,
ineff_ctrl_attr_10,
ineff_ctrl_attr_11,
ineff_ctrl_attr_12,
ineff_ctrl_attr_13,
ineff_ctrl_attr_14,
ineff_ctrl_attr_15,
ineff_ctrl_attr_16,
ineff_ctrl_attr_17,
ineff_ctrl_attr_18,
ineff_ctrl_attr_19,
ineff_ctrl_attr_20,
ineff_ctrl_attr_21,
ineff_ctrl_attr_22,
ineff_ctrl_attr_23,
ineff_ctrl_attr_24,
ineff_ctrl_attr_25,
ineff_ctrl_attr_26,
ineff_ctrl_attr_27,
ineff_ctrl_attr_28,
ineff_ctrl_attr_29,
ineff_ctrl_attr_30,
total_ctrl_attr_1,
total_ctrl_attr_2,
total_ctrl_attr_3,
total_ctrl_attr_4,
total_ctrl_attr_5,
total_ctrl_attr_6,
total_ctrl_attr_7,
total_ctrl_attr_8,
total_ctrl_attr_9,
total_ctrl_attr_10,
total_ctrl_attr_11,
total_ctrl_attr_12,
total_ctrl_attr_13,
total_ctrl_attr_14,
total_ctrl_attr_15,
total_ctrl_attr_16,
total_ctrl_attr_17,
total_ctrl_attr_18,
total_ctrl_attr_19,
total_ctrl_attr_20,
total_ctrl_attr_21,
total_ctrl_attr_22,
total_ctrl_attr_23,
total_ctrl_attr_24,
total_ctrl_attr_25,
total_ctrl_attr_26,
total_ctrl_attr_27,
total_ctrl_attr_28,
total_ctrl_attr_29,
total_ctrl_attr_30
from
amw_certification_vl cert,
amw_fin_cert_ctrl_sum ctrlsum,
amw_fin_cert_scope scope
where
cert.certification_id = ctrlsum.fin_certification_id and
ctrl_attribute_type = 'CTRL_ASSERTIONS'
and
(CERTIFICATION_STATUS= 'ACTIVE' or CERTIFICATION_STATUS= 'DRAFT')
and cert.OBJECT_TYPE ='FIN_STMT'
and ctrlsum.object_type='FINANCIAL ITEM'
and scope.fin_certification_id = ctrlsum.fin_certification_id
and scope.NATURAL_ACCOUNT_ID = P_NATRL_ACCOUNT_ID
AND SCOPE.financial_item_id = ctrlsum.financial_item_id ;
select DISTINCT
ASSERTION_CODE
from
amw_account_assertions
where
NATURAL_ACCOUNT_ID IN
(select DISTINCT NATURAL_ACCOUNT_ID from amw_fin_cert_scope where fin_certification_id = P_CERTIFICATION_ID and
financial_item_id = P_FINANCIAL_ITEM_ID );
amw_fin_coso_views_pvt.UPDATE_CTRLSUM_ITEM_FLAG(
x_fin_certification_id => m_cert_id ,
x_financial_item_id => m_fin_item_id ,
x_ctrl_attribute_type => m_ctrl_attribute_type ,
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_object_version_number => null,
x_acc_assert_flag1 => m_acc_assert_flag(1),
x_acc_assert_flag2 => m_acc_assert_flag(2),
x_acc_assert_flag3 => m_acc_assert_flag(3),
x_acc_assert_flag4 => m_acc_assert_flag(4),
x_acc_assert_flag5 => m_acc_assert_flag(5),
x_acc_assert_flag6 => m_acc_assert_flag(6),
x_acc_assert_flag7 => m_acc_assert_flag(7),
x_acc_assert_flag8 => m_acc_assert_flag(8),
x_acc_assert_flag9 => m_acc_assert_flag(9),
x_acc_assert_flag10 => m_acc_assert_flag(10),
x_acc_assert_flag11 => m_acc_assert_flag(11),
x_acc_assert_flag12 => m_acc_assert_flag(12),
x_acc_assert_flag13 => m_acc_assert_flag(13),
x_acc_assert_flag14 => m_acc_assert_flag(14),
x_acc_assert_flag15 => m_acc_assert_flag(15),
x_acc_assert_flag16 => m_acc_assert_flag(16),
x_acc_assert_flag17 => m_acc_assert_flag(17),
x_acc_assert_flag18 => m_acc_assert_flag(18),
x_acc_assert_flag19 => m_acc_assert_flag(19),
x_acc_assert_flag20 => m_acc_assert_flag(20),
x_acc_assert_flag21 => m_acc_assert_flag(21),
x_acc_assert_flag22 => m_acc_assert_flag(22),
x_acc_assert_flag23 => m_acc_assert_flag(23),
x_acc_assert_flag24 => m_acc_assert_flag(24),
x_acc_assert_flag25 => m_acc_assert_flag(25),
x_acc_assert_flag26 => m_acc_assert_flag(26),
x_acc_assert_flag27 => m_acc_assert_flag(27),
x_acc_assert_flag28 => m_acc_assert_flag(28),
x_acc_assert_flag29 => m_acc_assert_flag(29),
x_acc_assert_flag30 => m_acc_assert_flag(30),
x_display_flag => m_display_flag );
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_item_assert_flags
/* ******************************************* UPDATE_ACC_ASSERT_FLAG ************************************************************* */
procedure UPDATE_CTRLSUM_ITEM_FLAG(
x_fin_certification_id NUMBER ,
x_financial_item_id NUMBER ,
x_ctrl_attribute_type varchar2,
x_created_by number ,
x_creation_date date ,
x_last_updated_by number ,
x_last_update_date date,
x_last_update_login number ,
x_acc_assert_flag1 VARCHAR2,
x_acc_assert_flag2 VARCHAR2,
x_acc_assert_flag3 VARCHAR2,
x_acc_assert_flag4 VARCHAR2,
x_acc_assert_flag5 VARCHAR2,
x_acc_assert_flag6 VARCHAR2,
x_acc_assert_flag7 VARCHAR2,
x_acc_assert_flag8 VARCHAR2,
x_acc_assert_flag9 VARCHAR2,
x_acc_assert_flag10 VARCHAR2,
x_acc_assert_flag11 VARCHAR2,
x_acc_assert_flag12 VARCHAR2,
x_acc_assert_flag13 VARCHAR2,
x_acc_assert_flag14 VARCHAR2,
x_acc_assert_flag15 VARCHAR2,
x_acc_assert_flag16 VARCHAR2,
x_acc_assert_flag17 VARCHAR2,
x_acc_assert_flag18 VARCHAR2,
x_acc_assert_flag19 VARCHAR2,
x_acc_assert_flag20 VARCHAR2,
x_acc_assert_flag21 VARCHAR2,
x_acc_assert_flag22 VARCHAR2,
x_acc_assert_flag23 VARCHAR2,
x_acc_assert_flag24 VARCHAR2,
x_acc_assert_flag25 VARCHAR2,
x_acc_assert_flag26 VARCHAR2,
x_acc_assert_flag27 VARCHAR2,
x_acc_assert_flag28 VARCHAR2,
x_acc_assert_flag29 VARCHAR2,
x_acc_assert_flag30 VARCHAR2,
x_display_flag VARCHAR2
) is
begin
UPDATE
amw_fin_cert_ctrl_sum
SET
acc_assert_flag_1= x_acc_assert_flag1
,acc_assert_flag_2 = x_acc_assert_flag2
,acc_assert_flag_3= x_acc_assert_flag3
,acc_assert_flag_4= x_acc_assert_flag4
,acc_assert_flag_5= x_acc_assert_flag5
,acc_assert_flag_6= x_acc_assert_flag6
,acc_assert_flag_7= x_acc_assert_flag7
,acc_assert_flag_8= x_acc_assert_flag8
,acc_assert_flag_9= x_acc_assert_flag9
,acc_assert_flag_10= x_acc_assert_flag10
,acc_assert_flag_11= x_acc_assert_flag11
,acc_assert_flag_12= x_acc_assert_flag12
,acc_assert_flag_13= x_acc_assert_flag13
,acc_assert_flag_14= x_acc_assert_flag14
,acc_assert_flag_15= x_acc_assert_flag15
,acc_assert_flag_16= x_acc_assert_flag16
,acc_assert_flag_17= x_acc_assert_flag17
,acc_assert_flag_18= x_acc_assert_flag18
,acc_assert_flag_19= x_acc_assert_flag19
,acc_assert_flag_20= x_acc_assert_flag20
,acc_assert_flag_21= x_acc_assert_flag21
,acc_assert_flag_22= x_acc_assert_flag22
,acc_assert_flag_23= x_acc_assert_flag23
,acc_assert_flag_24= x_acc_assert_flag24
,acc_assert_flag_25= x_acc_assert_flag25
,acc_assert_flag_26= x_acc_assert_flag26
,acc_assert_flag_27= x_acc_assert_flag27
,acc_assert_flag_28= x_acc_assert_flag28
,acc_assert_flag_29= x_acc_assert_flag29
,acc_assert_flag_30= x_acc_assert_flag30
,CONTROLS_EXIST_FLAG =x_display_flag
,last_updated_by = x_last_updated_by
,last_update_date = x_last_update_date
,last_update_login = x_last_update_login
,object_version_number = object_version_number +1
where
fin_certification_id = x_fin_certification_id and
financial_item_id = x_financial_item_id and
NVL(account_group_id, -1) = '-1' and
nvl(natural_account_id, -1) = '-1' and
CTRL_ATTRIBUTE_TYPE = x_ctrl_attribute_type and
object_type = 'FINANCIAL ITEM' ;
end UPDATE_CTRLSUM_ITEM_FLAG;
/* ******************************************* UPDATE_ACC_ASSERT_FLAG ***************************************** */
procedure UPDATE_CTRLSUM_FLAG(
x_fin_certification_id NUMBER ,
x_natural_account_id NUMBER,
x_ctrl_attribute_type varchar2,
x_created_by number ,
x_creation_date date ,
x_last_updated_by number ,
x_last_update_date date,
x_last_update_login number ,
x_acc_assert_flag1 VARCHAR2,
x_acc_assert_flag2 VARCHAR2,
x_acc_assert_flag3 VARCHAR2,
x_acc_assert_flag4 VARCHAR2,
x_acc_assert_flag5 VARCHAR2,
x_acc_assert_flag6 VARCHAR2,
x_acc_assert_flag7 VARCHAR2,
x_acc_assert_flag8 VARCHAR2,
x_acc_assert_flag9 VARCHAR2,
x_acc_assert_flag10 VARCHAR2,
x_acc_assert_flag11 VARCHAR2,
x_acc_assert_flag12 VARCHAR2,
x_acc_assert_flag13 VARCHAR2,
x_acc_assert_flag14 VARCHAR2,
x_acc_assert_flag15 VARCHAR2,
x_acc_assert_flag16 VARCHAR2,
x_acc_assert_flag17 VARCHAR2,
x_acc_assert_flag18 VARCHAR2,
x_acc_assert_flag19 VARCHAR2,
x_acc_assert_flag20 VARCHAR2,
x_acc_assert_flag21 VARCHAR2,
x_acc_assert_flag22 VARCHAR2,
x_acc_assert_flag23 VARCHAR2,
x_acc_assert_flag24 VARCHAR2,
x_acc_assert_flag25 VARCHAR2,
x_acc_assert_flag26 VARCHAR2,
x_acc_assert_flag27 VARCHAR2,
x_acc_assert_flag28 VARCHAR2,
x_acc_assert_flag29 VARCHAR2,
x_acc_assert_flag30 VARCHAR2,
x_display_flag VARCHAR2
) is
begin
UPDATE
amw_fin_cert_ctrl_sum
SET
acc_assert_flag_1= x_acc_assert_flag1
,acc_assert_flag_2 = x_acc_assert_flag2
,acc_assert_flag_3= x_acc_assert_flag3
,acc_assert_flag_4= x_acc_assert_flag4
,acc_assert_flag_5= x_acc_assert_flag5
,acc_assert_flag_6= x_acc_assert_flag6
,acc_assert_flag_7= x_acc_assert_flag7
,acc_assert_flag_8= x_acc_assert_flag8
,acc_assert_flag_9= x_acc_assert_flag9
,acc_assert_flag_10= x_acc_assert_flag10
,acc_assert_flag_11= x_acc_assert_flag11
,acc_assert_flag_12= x_acc_assert_flag12
,acc_assert_flag_13= x_acc_assert_flag13
,acc_assert_flag_14= x_acc_assert_flag14
,acc_assert_flag_15= x_acc_assert_flag15
,acc_assert_flag_16= x_acc_assert_flag16
,acc_assert_flag_17= x_acc_assert_flag17
,acc_assert_flag_18= x_acc_assert_flag18
,acc_assert_flag_19= x_acc_assert_flag19
,acc_assert_flag_20= x_acc_assert_flag20
,acc_assert_flag_21= x_acc_assert_flag21
,acc_assert_flag_22= x_acc_assert_flag22
,acc_assert_flag_23= x_acc_assert_flag23
,acc_assert_flag_24= x_acc_assert_flag24
,acc_assert_flag_25= x_acc_assert_flag25
,acc_assert_flag_26= x_acc_assert_flag26
,acc_assert_flag_27= x_acc_assert_flag27
,acc_assert_flag_28= x_acc_assert_flag28
,acc_assert_flag_29= x_acc_assert_flag29
,acc_assert_flag_30= x_acc_assert_flag30
,CONTROLS_EXIST_FLAG =x_display_flag
,last_updated_by = x_last_updated_by
,last_update_date = x_last_update_date
,last_update_login = x_last_update_login
,object_version_number = object_version_number +1
where
fin_certification_id = x_fin_certification_id and
natural_account_id = x_natural_account_id and
object_type = 'ACCOUNT' and
CTRL_ATTRIBUTE_TYPE = 'CTRL_ASSERTIONS';
end UPDATE_CTRLSUM_FLAG;
amw_fin_coso_views_pvt.update_acc_assert_flags(l_acccount_id);
WF_CORE.CONTEXT('amw_fin_coso_views_pvt', 'update_acc_assert_flags', p_event.getEventName(), p_subscription_guid);
PROCEDURE update_parentacc_assert_flags
( P_ACCOUNT_ID number,
P_CERTFICATION_ID number,
P_ACCOUNT_GROUP_ID number) is
begin
declare
ctr integer :=0;
select
distinct
ASSERTION_CODE
from
amw_account_assertions
where
((NATURAL_ACCOUNT_ID =M_PARENT_ACCOUNT_ID) or (NATURAL_ACCOUNT_ID in (select CHILD_NATURAL_ACCOUNT_ID from amw_fin_key_acct_flat
where PARENT_NATURAL_ACCOUNT_ID =M_PARENT_ACCOUNT_ID and ACCOUNT_GROUP_ID=P_ACCOUNT_GROUP_ID)));
select PARENT_NATURAL_ACCOUNT_ID from amw_fin_key_acct_flat
where CHILD_NATURAL_ACCOUNT_ID =P_ACCOUNT_ID and ACCOUNT_GROUP_ID=p_ACCOUNT_GROUP_ID;
select
distinct
FIN_CERTIFICATION_ID,
FINANCIAL_STATEMENT_ID,
ctrl_attr_code_1,
ctrl_attr_code_2,
ctrl_attr_code_3,
ctrl_attr_code_4,
ctrl_attr_code_5,
ctrl_attr_code_6,
ctrl_attr_code_7,
ctrl_attr_code_8,
ctrl_attr_code_9,
ctrl_attr_code_10,
ctrl_attr_code_11,
ctrl_attr_code_12,
ctrl_attr_code_13,
ctrl_attr_code_14,
ctrl_attr_code_15,
ctrl_attr_code_16,
ctrl_attr_code_17,
ctrl_attr_code_18,
ctrl_attr_code_19,
ctrl_attr_code_20,
ctrl_attr_code_21,
ctrl_attr_code_22,
ctrl_attr_code_23,
ctrl_attr_code_24,
ctrl_attr_code_25,
ctrl_attr_code_26,
ctrl_attr_code_27,
ctrl_attr_code_28,
ctrl_attr_code_29,
ctrl_attr_code_30,
ineff_ctrl_attr_1,
ineff_ctrl_attr_2,
ineff_ctrl_attr_3,
ineff_ctrl_attr_4,
ineff_ctrl_attr_5,
ineff_ctrl_attr_6,
ineff_ctrl_attr_7,
ineff_ctrl_attr_8,
ineff_ctrl_attr_9,
ineff_ctrl_attr_10,
ineff_ctrl_attr_11,
ineff_ctrl_attr_12,
ineff_ctrl_attr_13,
ineff_ctrl_attr_14,
ineff_ctrl_attr_15,
ineff_ctrl_attr_16,
ineff_ctrl_attr_17,
ineff_ctrl_attr_18,
ineff_ctrl_attr_19,
ineff_ctrl_attr_20,
ineff_ctrl_attr_21,
ineff_ctrl_attr_22,
ineff_ctrl_attr_23,
ineff_ctrl_attr_24,
ineff_ctrl_attr_25,
ineff_ctrl_attr_26,
ineff_ctrl_attr_27,
ineff_ctrl_attr_28,
ineff_ctrl_attr_29,
ineff_ctrl_attr_30,
total_ctrl_attr_1,
total_ctrl_attr_2,
total_ctrl_attr_3,
total_ctrl_attr_4,
total_ctrl_attr_5,
total_ctrl_attr_6,
total_ctrl_attr_7,
total_ctrl_attr_8,
total_ctrl_attr_9,
total_ctrl_attr_10,
total_ctrl_attr_11,
total_ctrl_attr_12,
total_ctrl_attr_13,
total_ctrl_attr_14,
total_ctrl_attr_15,
total_ctrl_attr_16,
total_ctrl_attr_17,
total_ctrl_attr_18,
total_ctrl_attr_19,
total_ctrl_attr_20,
total_ctrl_attr_21,
total_ctrl_attr_22,
total_ctrl_attr_23,
total_ctrl_attr_24,
total_ctrl_attr_25,
total_ctrl_attr_26,
total_ctrl_attr_27,
total_ctrl_attr_28,
total_ctrl_attr_29,
total_ctrl_attr_30
from
amw_fin_cert_ctrl_sum ctrlsum
where
FIN_CERTIFICATION_ID= P_CERTFICATION_ID
and ctrl_attribute_type = 'CTRL_ASSERTIONS'
AND OBJECT_TYPE='ACCOUNT' AND
NATURAL_ACCOUNT_ID=M_PARENT_ACCOUNT_ID AND
ACCOUNT_GROUP_ID =P_ACCOUNT_GROUP_ID ;
amw_fin_coso_views_pvt.UPDATE_CTRLSUM_FLAG(
x_fin_certification_id => m_cert_id ,
x_natural_account_id => M_PARENT_ACCOUNT_ID ,
x_ctrl_attribute_type => m_ctrl_attribute_type ,
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_object_version_number => null,
x_acc_assert_flag1 => m_acc_assert_flag(1),
x_acc_assert_flag2 => m_acc_assert_flag(2),
x_acc_assert_flag3 => m_acc_assert_flag(3),
x_acc_assert_flag4 => m_acc_assert_flag(4),
x_acc_assert_flag5 => m_acc_assert_flag(5),
x_acc_assert_flag6 => m_acc_assert_flag(6),
x_acc_assert_flag7 => m_acc_assert_flag(7),
x_acc_assert_flag8 => m_acc_assert_flag(8),
x_acc_assert_flag9 => m_acc_assert_flag(9),
x_acc_assert_flag10 => m_acc_assert_flag(10),
x_acc_assert_flag11 => m_acc_assert_flag(11),
x_acc_assert_flag12 => m_acc_assert_flag(12),
x_acc_assert_flag13 => m_acc_assert_flag(13),
x_acc_assert_flag14 => m_acc_assert_flag(14),
x_acc_assert_flag15 => m_acc_assert_flag(15),
x_acc_assert_flag16 => m_acc_assert_flag(16),
x_acc_assert_flag17 => m_acc_assert_flag(17),
x_acc_assert_flag18 => m_acc_assert_flag(18),
x_acc_assert_flag19 => m_acc_assert_flag(19),
x_acc_assert_flag20 => m_acc_assert_flag(20),
x_acc_assert_flag21 => m_acc_assert_flag(21),
x_acc_assert_flag22 => m_acc_assert_flag(22),
x_acc_assert_flag23 => m_acc_assert_flag(23),
x_acc_assert_flag24 => m_acc_assert_flag(24),
x_acc_assert_flag25 => m_acc_assert_flag(25),
x_acc_assert_flag26 => m_acc_assert_flag(26),
x_acc_assert_flag27 => m_acc_assert_flag(27),
x_acc_assert_flag28 => m_acc_assert_flag(28),
x_acc_assert_flag29 => m_acc_assert_flag(29),
x_acc_assert_flag30 => m_acc_assert_flag(30),
x_display_flag => m_display_flag );
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_parentacc_assert_flags