[Home] [Help]
469:
470:
471: BEGIN
472: -- Insert all this info into the summary table with one row per process, org.
473: l_global_org_id := fnd_profile.value('AMW_GLOBAL_ORG_ID');
474: g_user_id := fnd_global.user_id;
475: g_login_id := fnd_global.conc_login_id;
476:
477: for proc_rec in proc loop
754: )
755:
756: AND acct_assoc.object_type = 'PROCESS_ORG'
757: AND apo.process_organization_id = acct_assoc.pk1
758: AND apo.organization_id = fnd_profile.value('AMW_GLOBAL_ORG_ID')
759: AND apo.process_id = hier.process_id
760: AND apo.organization_id = hier.organization_id
761: AND hier.up_down_ind = 'D'
762: AND not exists (SELECT 'Y'
818: )
819:
820: AND acct_assoc.object_type = 'PROCESS_ORG'
821: AND apo.process_organization_id = acct_assoc.pk1
822: AND apo.organization_id = fnd_profile.value('AMW_GLOBAL_ORG_ID')
823: AND hier.process_id = apo.process_id
824: AND hier.organization_id = apo.organization_id
825: AND hier.up_down_ind = 'D'
826: AND aov.object_name = 'AMW_ORG_PROCESS'
878: )
879:
880: AND acct_assoc.object_type = 'PROCESS_ORG'
881: AND apo.process_organization_id = acct_assoc.pk1
882: AND apo.organization_id <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999)
883: AND hier.process_id = apo.process_id
884: AND hier.organization_id = apo.organization_id
885: AND hier.up_down_ind = 'D'
886: AND not exists (SELECT 'Y'
939: )
940:
941: AND acct_assoc.object_type = 'PROCESS_ORG'
942: AND apo.process_organization_id = acct_assoc.pk1
943: AND apo.organization_id <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'), -999)
944: AND hier.process_id = apo.process_id
945: AND hier.organization_id = apo.organization_id
946: AND hier.up_down_ind = 'D'
947: AND aov.object_name = 'AMW_ORG_PROCESS'
998: )
999:
1000: AND acct_assoc.object_type = 'PROCESS_ORG'
1001: AND apo.process_organization_id = acct_assoc.pk1
1002: AND apo.organization_id = fnd_profile.value('AMW_GLOBAL_ORG_ID')
1003: AND hier.process_id = apo.process_id
1004: AND hier.organization_id = apo.organization_id
1005: AND hier.up_down_ind = 'D'
1006: AND aov.object_name = 'AMW_ORG_PROCESS'
1063: )
1064:
1065: AND acct_assoc.object_type = 'PROCESS_ORG'
1066: AND apo.process_organization_id = acct_assoc.pk1
1067: AND apo.organization_id <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'), -999)
1068: AND hier.process_id = apo.process_id
1069: AND hier.organization_id = apo.organization_id
1070: AND hier.up_down_ind = 'D'
1071: AND aov.object_name = 'AMW_ORG_PROCESS'
1574: SELECT count(distinct aov.pk3_value)
1575: FROM AMW_OPINIONS_V aov
1576: WHERE aov.object_name = 'AMW_ORG_PROCESS'
1577: AND aov.opinion_type_code = 'CERTIFICATION'
1578: AND aov.pk3_value <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999)
1579: AND aov.pk1_value = p_process_id
1580: AND aov.pk2_value in (select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
1581: where fin_stmt_cert_id = p_certification_id);
1582: --AND aov.authored_date >= p_start_date
1613: AND fika.statement_group_id = cert.statement_group_id
1614: AND fika.financial_statement_id = cert.financial_statement_id
1615: AND cert.certification_id = p_certification_id
1616: AND po.process_id = p_process_id
1617: AND po.organization_id <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999);
1618: */
1619:
1620: CURSOR certification_result IS
1621: SELECT aov.opinion_id
1735: OPEN total_sub_processes;
1736: FETCH total_sub_processes INTO l_total_sub_processes;
1737: CLOSE total_sub_processes;
1738:
1739: IF (p_organization_id = NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999)) THEN
1740: /*
1741: OPEN org_processes_certified;
1742: FETCH org_processes_certified INTO l_org_processes_certified;
1743: CLOSE org_processes_certified;
2030: SELECT count(distinct aov.pk3_value)
2031: FROM AMW_OPINIONS_V aov
2032: WHERE aov.object_name = 'AMW_ORG_PROCESS'
2033: AND aov.opinion_type_code = 'CERTIFICATION'
2034: AND aov.pk3_value <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999)
2035: AND aov.pk1_value = p_process_id
2036: AND aov.pk3_value in (select distinct evalsum.organization_id
2037: from amw_fin_process_eval_sum evalsum
2038: where evalsum.fin_certification_id = p_cert_id
2045: from amw_fin_process_eval_sum evalsum
2046: where evalsum.fin_certification_id = p_cert_id
2047: and evalsum.process_id=p_process_id
2048: and evalsum.organization_id <>
2049: NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999);
2050:
2051: --Begin variant process
2052:
2053: CURSOR var_procs(p_cert_id NUMBER) IS
2053: CURSOR var_procs(p_cert_id NUMBER) IS
2054: SELECT fin_certification_id,organization_id,process_id
2055: FROM AMW_FIN_PROCESS_EVAL_SUM
2056: WHERE fin_certification_id = p_cert_id
2057: AND organization_id = NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999);
2058:
2059: CURSOR org_var_processes_certified(p_cert_id NUMBER,p_start_date DATE,p_end_date DATE,p_process_id NUMBER) IS
2060: select count(1) from
2061: (SELECT distinct aov.pk3_value,aov.pk1_value
2061: (SELECT distinct aov.pk3_value,aov.pk1_value
2062: FROM AMW_OPINIONS_V aov
2063: WHERE aov.object_name = 'AMW_ORG_PROCESS'
2064: AND aov.opinion_type_code = 'CERTIFICATION'
2065: --AND aov.pk3_value <> NVL(fnd_profile.value('AMW_GLOBAL_ORG_ID'),-999)
2066: --AND aov.authored_date >= p_start_date
2067: --AND aov.authored_date <= p_end_date
2068: AND aov.pk2_value in (
2069: select proc_cert_Id from AMW_FIN_PROC_CERT_RELAN
4717: GL_PERIODS.PERIOD_NAME = CERTIFICATION.CERTIFICATION_PERIOD_NAME
4718: AND GL_PERIODS.PERIOD_SET_NAME = CERTIFICATION.CERTIFICATION_PERIOD_SET_NAME
4719: and CERTIFICATION.OBJECT_TYPE='FIN_STMT'
4720: AND CERTIFICATION.CERTIFICATION_ID = P_Certification_ID;
4721: -- AND GL_PERIODS.PERIOD_SET_NAME = fnd_profile.value('AMW_CALENDAR') ;
4722:
4723: EXCEPTION
4724: WHEN NO_DATA_FOUND
4725: THEN