[Home] [Help]
893:
894: l_stmt1 := ' AND FIN.STATEMENT_GROUP_ID = :2 AND FIN.FINANCIAL_STATEMENT_ID = :3 AND FIN.FINANCIAL_ITEM_ID = :4)';
895: l_stmt2 := ' AND FIN.NATURAL_ACCOUNT_ID = :2)';
896:
897: /****replace with the following query that uses opinon_log_id in amw_fin_item_acc_ctrl table directly
898: l_stmt := 'select count(1) from(
899: select distinct fin.control_id, fin.organization_id
900: FROM
901: AMW_OPINION_MV aov,
898: l_stmt := 'select count(1) from(
899: select distinct fin.control_id, fin.organization_id
900: FROM
901: AMW_OPINION_MV aov,
902: amw_fin_item_acc_ctrl fin
903: WHERE
904: aov.AUTHORED_DATE in (select max(aov2.authored_date)
905: from AMW_OPINIONS aov2
906: where aov2.object_opinion_type_id = aov.object_opinion_type_id
918: and fin.fin_certification_id = :1 ';
919: ****************/
920:
921:
922: /*** use opinion_log_id in amw_control_associations instead of opinion_log_id in amw_fin_item_acc_ctrl
923: **** so that it consists with VO query and also reduces the maintainence ***********************/
924: l_stmt := 'select count(1) from(
925: select distinct fin.control_id, fin.organization_id
926: FROM
924: l_stmt := 'select count(1) from(
925: select distinct fin.control_id, fin.organization_id
926: FROM
927: amw_opinion_log_mv aov,
928: amw_fin_item_acc_ctrl fin,
929: amw_control_associations ctrls
930: WHERE
931: aov.opinion_log_id = ctrls.pk5
932: and ctrls.pk1 = fin.fin_certification_id
996:
997: l_stmt1 := ' AND FIN.STATEMENT_GROUP_ID = :2 AND FIN.FINANCIAL_STATEMENT_ID = :3 AND FIN.FINANCIAL_ITEM_ID = :4)';
998: l_stmt2 := ' AND FIN.NATURAL_ACCOUNT_ID = :2)';
999:
1000: /*****replace with the following query ,which uses opinion_log_id directly in amw_fin_item_acc_ctrl table
1001: l_stmt := 'select count(1) from(
1002: select distinct fin.control_id, fin.organization_id
1003: FROM
1004: AMW_OPINION_MV aov,
1001: l_stmt := 'select count(1) from(
1002: select distinct fin.control_id, fin.organization_id
1003: FROM
1004: AMW_OPINION_MV aov,
1005: amw_fin_item_acc_ctrl fin
1006: WHERE aov.OPINION_TYPE_CODE = ''EVALUATION''
1007: AND aov.object_name = ''AMW_ORG_CONTROL''
1008: and aov.opinion_component_code = ''OVERALL''
1009: AND aov.pk1_value = fin.control_id
1016: l_stmt := 'select count(1) from(
1017: select distinct fin.control_id, fin.organization_id
1018: FROM
1019: AMW_OPINION_LOG_MV aov,
1020: amw_fin_item_acc_ctrl fin,
1021: amw_control_associations ctrls
1022: WHERE
1023: aov.opinion_log_id = ctrls.pk5
1024: and ctrls.pk1 = fin.fin_certification_id
1091: l_stmt := 'select count(1)
1092: from (
1093: select distinct organization_id, control_id
1094: FROM
1095: amw_fin_item_acc_ctrl fin
1096: WHERE fin.object_type = ''' || p_object_type || '''' || '
1097: and fin.FIN_CERTIFICATION_ID= :1 ';
1098:
1099: IF P_OBJECT_TYPE = 'FINANCIAL ITEM' THEN
1488: x_ineffective_controls OUT NOCOPY Number
1489: )IS
1490: BEGIN
1491: ---------ineffective_controls
1492: /***************replace to use opinion_log_id in amw_fin_item_acc_ctrl table
1493: SELECT count(1) INTO x_ineffective_controls
1494: from(select distinct fin.control_id, fin.organization_id
1495: FROM AMW_OPINION_MV aov,
1496: AMW_FIN_ITEM_ACC_CTRL fin
1492: /***************replace to use opinion_log_id in amw_fin_item_acc_ctrl table
1493: SELECT count(1) INTO x_ineffective_controls
1494: from(select distinct fin.control_id, fin.organization_id
1495: FROM AMW_OPINION_MV aov,
1496: AMW_FIN_ITEM_ACC_CTRL fin
1497: WHERE fin.fin_certification_id = p_certification_id
1498: AND fin.object_type = 'FINANCIAL STATEMENT'
1499: AND aov.pk1_value = fin.control_id
1500: AND aov.pk3_value = fin.organization_id
1512: **********************/
1513: SELECT count(1) INTO x_ineffective_controls
1514: from(select distinct fin.control_id, fin.organization_id
1515: FROM AMW_OPINION_LOG_MV aov,
1516: AMW_FIN_ITEM_ACC_CTRL fin
1517: WHERE fin.fin_certification_id = p_certification_id
1518: AND fin.object_type = 'FINANCIAL STATEMENT'
1519: AND aov.opinion_log_id = fin.OPINION_LOG_ID
1520: AND aov.OPINION_TYPE_CODE = 'EVALUATION'
2255:
2256: -- Initialize API return status to SUCCESS
2257: x_return_status := FND_API.G_RET_STS_SUCCESS;
2258:
2259: SELECT COUNT(1) INTO L_COUNT FROM AMW_FIN_ITEM_ACC_CTRL
2260: WHERE FIN_CERTIFICATION_ID = P_CERTIFICATION_ID;
2261:
2262: IF (L_COUNT = 0 OR L_COUNT IS NULL) THEN
2263:
2260: WHERE FIN_CERTIFICATION_ID = P_CERTIFICATION_ID;
2261:
2262: IF (L_COUNT = 0 OR L_COUNT IS NULL) THEN
2263:
2264: insert into amw_fin_item_acc_ctrl
2265: (
2266: OBJECT_TYPE,
2267: FIN_CERTIFICATION_ID,
2268: STATEMENT_GROUP_ID ,
3424: (p_certification_id => p_certification_id,
3425: x_return_status => l_return_status,
3426: x_msg_count => l_msg_count,
3427: x_msg_data => l_msg_data);
3428: fnd_file.put_line(fnd_file.LOG, 'finish populating amw_fin_item_acc_ctrls table:p_certification_id = ' || p_certification_id || to_char(sysdate, 'hh:mi:ss dd-mon-rrrr'));
3429:
3430: l_start_time := sysdate;
3431:
3432: Populate_Cert_General_Sum
7998:
7999: ---CURSOR TO GET ALL OF FINANCIAL ITEMS WHICH RELATE TO THIS CONTROL
8000: CURSOR Get_all_items(l_control_id number, l_org_id number)IS
8001: SELECT DISTINCT item.FIN_CERTIFICATION_ID, item.STATEMENT_GROUP_ID, item.FINANCIAL_STATEMENT_ID, item.FINANCIAL_ITEM_ID
8002: FROM AMW_FIN_ITEM_ACC_CTRL ITEM,
8003: AMW_CERTIFICATION_B cert
8004: WHERE ITEM.CONTROL_ID = l_control_id
8005: AND ITEM.ORGANIZATION_ID = l_org_id
8006: AND ITEM.FINANCIAL_ITEM_ID IS NOT NULL
8009:
8010: --CURSOR TO GET ALL OF ACCOUNTS WHICH RELATE TO THIS RISK
8011: CURSOR Get_all_accts (l_control_id number, l_org_id number)IS
8012: SELECT DISTINCT finitemAcc.fin_certification_id, finitemAcc.account_group_id, finitemAcc.natural_account_id
8013: FROM AMW_FIN_ITEM_ACC_CTRL finitemAcc,
8014: AMW_CERTIFICATION_B cert
8015: WHERE finitemAcc.CONTROL_ID = l_control_id
8016: AND finitemAcc.ORGANIZATION_ID = l_org_id
8017: AND finitemAcc.natural_account_id is not null
8251: P_ORG_ID => p_org_id,
8252: P_CHANGE_FLAG => M_change_flag,
8253: P_NEW_FLAG => M_new_flag) ;
8254:
8255: UPDATE AMW_FIN_ITEM_ACC_CTRL
8256: SET
8257: LAST_UPDATE_DATE = sysdate,
8258: last_updated_by = fnd_global.user_id,
8259: last_update_login = fnd_global.conc_login_id,
8383: P_ORG_ID => p_org_id ,
8384: P_CHANGE_FLAG => M_change_flag,
8385: P_NEW_FLAG => M_new_flag);
8386:
8387: UPDATE AMW_FIN_ITEM_ACC_CTRL
8388: SET
8389: LAST_UPDATE_DATE = sysdate,
8390: last_updated_by = fnd_global.user_id,
8391: last_update_login = fnd_global.conc_login_id,
10853:
10854: l_stmt1 := ' AND FIN.STATEMENT_GROUP_ID = :2 AND FIN.FINANCIAL_STATEMENT_ID = :3 AND FIN.FINANCIAL_ITEM_ID = :4)';
10855: l_stmt2 := ' AND FIN.NATURAL_ACCOUNT_ID = :2)';
10856:
10857: /********replace to use opinion_log_id in amw_fin_item_acc_ctrl
10858: l_stmt := 'select count(1) from(
10859: select distinct fin.control_id, fin.organization_id
10860: FROM
10861: AMW_OPINION_M_V aov,
10858: l_stmt := 'select count(1) from(
10859: select distinct fin.control_id, fin.organization_id
10860: FROM
10861: AMW_OPINION_M_V aov,
10862: amw_fin_item_acc_ctrl fin
10863: WHERE aov.OPINION_TYPE_CODE = ''EVALUATION''
10864: AND aov.object_name = ''AMW_ORG_CONTROL''
10865: and aov.opinion_component_code = ''OVERALL''
10866: AND aov.pk1_value = fin.control_id
10875: select distinct fin.control_id, fin.organization_id
10876: FROM
10877: amw_opinion_m_v aov,
10878: amw_opinions_log aol,
10879: amw_fin_item_acc_ctrl fin
10880: WHERE aov.OPINION_TYPE_CODE = ''EVALUATION''
10881: and aov.object_name = ''AMW_ORG_CONTROL''
10882: and aov.opinion_component_code = ''OVERALL''
10883: and aol.opinion_log_id = fin.OPINION_LOG_ID