[Home] [Help]
118: if l_return is null then
119: begin
120: select 1
121: into l_dummy
122: from amw_process_organization
123: where process_id = p_process_id
124: and organization_id = p_org_id;
125:
126: l_return := 'NOHIER';
151:
152: -- check if the latest revision is approved.
153: select REVISION_NUMBER
154: into l_rev_num
155: from amw_process_organization
156: where process_id = p_process_id
157: and organization_id = p_org_id
158: and end_date is null
159: and approval_status = 'A';
160:
161:
162: -- if you've come here => the latest revision is approved. We need to revise.
163:
164: -- insert a new revision row in amw_process_organization
165: insert into amw_process_organization
166: (CONTROL_COUNT,
167: RISK_COUNT,
168: PROCESS_ORGANIZATION_ID,
161:
162: -- if you've come here => the latest revision is approved. We need to revise.
163:
164: -- insert a new revision row in amw_process_organization
165: insert into amw_process_organization
166: (CONTROL_COUNT,
167: RISK_COUNT,
168: PROCESS_ORGANIZATION_ID,
169: PROCESS_ID,
225:
226: (select
227: CONTROL_COUNT,
228: RISK_COUNT,
229: AMW_PROCESS_ORGANIZATION_S.nextval, --kosriniv this is primary key till AMW.C PROCESS_ORGANIZATION_ID,
230: PROCESS_ID,
231: STANDARD_PROCESS_FLAG,
232: RISK_CATEGORY,
233: CERTIFICATION_STATUS,
277: REVISION_NUMBER + 1,
278: 1,
279: 'D',
280: null,
281: AMW_PROCESS_ORG_REV_S.nextval,
282: sysdate,
283: null,
284: null,
285: DELETION_DATE
282: sysdate,
283: null,
284: null,
285: DELETION_DATE
286: from amw_process_organization
287: where process_id = p_process_id
288: and organization_id = p_org_id
289: and end_date is null
290: and approval_status = 'A');
289: and end_date is null
290: and approval_status = 'A');
291:
292: -- update the old row
293: update amw_process_organization
294: set end_date = sysdate,
295: object_version_number = object_version_number + 1
296: where process_id = p_process_id
297: and revision_number = l_rev_num
330: Begin
331: -- check if the latest revision is approved.
332: select REVISION_NUMBER
333: into l_rev_num
334: from amw_process_organization
335: where process_id = p_process_id
336: and organization_id = Org_Ids(indx)
337: and end_date is null
338: and approval_status = 'A';
350: END LOOP;
351:
352: -- if you've come here => the latest revision is approved. We need to revise.
353:
354: -- insert a new revision row in amw_process_organization
355: FORALL indx IN l_Org_Ids.FIRST .. l_Org_Ids.LAST
356: insert into amw_process_organization
357: (CONTROL_COUNT,
358: RISK_COUNT,
352: -- if you've come here => the latest revision is approved. We need to revise.
353:
354: -- insert a new revision row in amw_process_organization
355: FORALL indx IN l_Org_Ids.FIRST .. l_Org_Ids.LAST
356: insert into amw_process_organization
357: (CONTROL_COUNT,
358: RISK_COUNT,
359: PROCESS_ORGANIZATION_ID,
360: PROCESS_ID,
416:
417: (select
418: CONTROL_COUNT,
419: RISK_COUNT,
420: AMW_PROCESS_ORGANIZATION_S.nextval, --kosriniv this is primary key till AMW.C PROCESS_ORGANIZATION_ID,
421: PROCESS_ID,
422: STANDARD_PROCESS_FLAG,
423: RISK_CATEGORY,
424: CERTIFICATION_STATUS,
468: REVISION_NUMBER + 1,
469: 1,
470: 'D',
471: null,
472: AMW_PROCESS_ORG_REV_S.nextval,
473: sysdate,
474: null,
475: null,
476: DELETION_DATE
473: sysdate,
474: null,
475: null,
476: DELETION_DATE
477: from amw_process_organization
478: where process_id = p_process_id
479: and organization_id = l_Org_Ids(indx)
480: and end_date is null
481: and approval_status = 'A');
481: and approval_status = 'A');
482:
483: -- update the old row
484: FORALL indx IN l_Org_Ids.FIRST .. l_Org_Ids.LAST
485: update amw_process_organization
486: set end_date = sysdate,
487: object_version_number = object_version_number + 1
488: where process_id = p_process_id
489: and revision_number = l_rev_number(indx)
689: if appexst = 'Y' then
690:
691: revise_process_if_necessary(p_org_id, p_process_id);
692:
693: update amw_process_organization
694: set deletion_date = sysdate
695: where process_id = p_process_id
696: and organization_id = p_org_id
697: and end_date is null;
716: -- of a new org-process
717: procedure import_rlproc_as_child_of_ex (
718: p_org_id in number,
719: p_parent_process_id in number, -- exisitng org process
720: p_child_process_id in number, -- amw_process id
721: apply_rcm in varchar2) is
722:
723: l_curr_log_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
724: l_log_stmt_level number := FND_LOG.LEVEL_STATEMENT;
750: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
751: 'amw.plsql.AMW_ORG_HIERARCHY_PKG.IMPORT_RLPROC_AS_CHILD_OF_EX.insert Row','Insert Row' );
752: end if;
753:
754: --insert into amw_process_organization table
755: insert into amw_process_organization
756: ( PROCESS_ORG_REV_ID,
757: PROCESS_ORGANIZATION_ID,
758: REVISION_NUMBER,
751: 'amw.plsql.AMW_ORG_HIERARCHY_PKG.IMPORT_RLPROC_AS_CHILD_OF_EX.insert Row','Insert Row' );
752: end if;
753:
754: --insert into amw_process_organization table
755: insert into amw_process_organization
756: ( PROCESS_ORG_REV_ID,
757: PROCESS_ORGANIZATION_ID,
758: REVISION_NUMBER,
759: PROCESS_ID,
793: START_DATE,
794: risk_category)
795:
796: (select
797: AMW_PROCESS_ORG_REV_S.nextval,
798: AMW_PROCESS_ORGANIZATION_S.nextval,
799: 1,
800: PROCESS_ID,
801: p_org_id,
794: risk_category)
795:
796: (select
797: AMW_PROCESS_ORG_REV_S.nextval,
798: AMW_PROCESS_ORGANIZATION_S.nextval,
799: 1,
800: PROCESS_ID,
801: p_org_id,
802: PROCESS_CODE,
832: 1,
833: 'D',
834: sysdate,
835: 'High'
836: from amw_process
837: where process_id = p_child_process_id
838: and approval_date is not null
839: and approval_end_date is null);
840:
883: v_proc_rev_id FND_ATTACHED_DOCUMENTS.pk1_value%type;
884: v_proc_org_rev_id FND_ATTACHED_DOCUMENTS.pk1_value%type;
885: BEGIN
886: select process_org_rev_id into v_proc_org_rev_id
887: from amw_process_organization
888: where process_id = p_process_id
889: and organization_id = p_org_id
890: and end_date is null;
891:
890: and end_date is null;
891:
892: select distinct pk1_value into v_proc_rev_id
893: From FND_ATTACHED_DOCUMENTS
894: where entity_name ='AMW_PROCESS'
895: and pk1_value in ( select PROCESS_REV_ID
896: from amw_process_vl
897: where process_id = p_process_id
898: and end_date is null);
892: select distinct pk1_value into v_proc_rev_id
893: From FND_ATTACHED_DOCUMENTS
894: where entity_name ='AMW_PROCESS'
895: and pk1_value in ( select PROCESS_REV_ID
896: from amw_process_vl
897: where process_id = p_process_id
898: and end_date is null);
899:
900: IF p_add_upd_flag = 'U' THEN
897: where process_id = p_process_id
898: and end_date is null);
899:
900: IF p_add_upd_flag = 'U' THEN
901: FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(X_entity_name => 'AMW_PROCESS_ORGANIZATION'
902: ,X_pk1_value => v_proc_org_rev_id);
903: --,X_delete_document_flag => 'Y');
904:
905: END IF; -- if update then delete the current attachments before adding new
903: --,X_delete_document_flag => 'Y');
904:
905: END IF; -- if update then delete the current attachments before adding new
906:
907: FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(X_from_entity_name => 'AMW_PROCESS'
908: ,X_from_pk1_value => v_proc_rev_id
909: ,X_to_entity_name => 'AMW_PROCESS_ORGANIZATION'
910: ,X_to_pk1_value => v_proc_org_rev_id
911: ,X_created_by => G_USER_ID
905: END IF; -- if update then delete the current attachments before adding new
906:
907: FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(X_from_entity_name => 'AMW_PROCESS'
908: ,X_from_pk1_value => v_proc_rev_id
909: ,X_to_entity_name => 'AMW_PROCESS_ORGANIZATION'
910: ,X_to_pk1_value => v_proc_org_rev_id
911: ,X_created_by => G_USER_ID
912: ,X_last_update_login => G_LOGIN_ID
913: );
928: grants.end_date end_date
929: FROM fnd_grants grants,
930: fnd_menus granted_menu,
931: fnd_objects obj
932: WHERE obj.obj_name = 'AMW_PROCESS_APPR_ETTY'
933: AND grants.object_id = obj.object_id
934: AND grants.grantee_type ='USER'
935: AND grantee_key like 'HZ_PARTY%'
936: AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
957: AMW_SECURITY_PUB.grant_role_guid
958: (
959: p_api_version => 1,
960: p_role_name => 'AMW_ORG_PROC_OWNER_ROLE',
961: p_object_name => 'AMW_PROCESS_ORGANIZATION',
962: p_instance_type => 'INSTANCE',
963: p_instance_set_id => null,
964: p_instance_pk1_value => p_org_id,
965: p_instance_pk2_value => p_child_process_id,
979: AMW_SECURITY_PUB.grant_role_guid
980: (
981: p_api_version => 1,
982: p_role_name => 'AMW_ORG_PROC_FIN_OWNER_ROLE',
983: p_object_name => 'AMW_PROCESS_ORGANIZATION',
984: p_instance_type => 'INSTANCE',
985: p_instance_set_id => null,
986: p_instance_pk1_value => p_org_id,
987: p_instance_pk2_value => p_child_process_id,
1001: AMW_SECURITY_PUB.grant_role_guid
1002: (
1003: p_api_version => 1,
1004: p_role_name => 'AMW_ORG_PROC_APPL_OWNER_ROLE',
1005: p_object_name => 'AMW_PROCESS_ORGANIZATION',
1006: p_instance_type => 'INSTANCE',
1007: p_instance_set_id => null,
1008: p_instance_pk1_value => p_org_id,
1009: p_instance_pk2_value => p_child_process_id,
1321: p_org_id in number,
1322: p_process_id in number,
1323: apply_rcm in varchar2) is
1324:
1325: l_RL_PROCESS_REV_ID amw_process.PROCESS_REV_ID%type;
1326: l_SIGNIFICANT_PROCESS_FLAG amw_process.SIGNIFICANT_PROCESS_FLAG%type;
1327: l_STANDARD_PROCESS_FLAG amw_process.STANDARD_PROCESS_FLAG%type;
1328: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
1329: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
1322: p_process_id in number,
1323: apply_rcm in varchar2) is
1324:
1325: l_RL_PROCESS_REV_ID amw_process.PROCESS_REV_ID%type;
1326: l_SIGNIFICANT_PROCESS_FLAG amw_process.SIGNIFICANT_PROCESS_FLAG%type;
1327: l_STANDARD_PROCESS_FLAG amw_process.STANDARD_PROCESS_FLAG%type;
1328: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
1329: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
1330: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
1323: apply_rcm in varchar2) is
1324:
1325: l_RL_PROCESS_REV_ID amw_process.PROCESS_REV_ID%type;
1326: l_SIGNIFICANT_PROCESS_FLAG amw_process.SIGNIFICANT_PROCESS_FLAG%type;
1327: l_STANDARD_PROCESS_FLAG amw_process.STANDARD_PROCESS_FLAG%type;
1328: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
1329: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
1330: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
1331: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
1324:
1325: l_RL_PROCESS_REV_ID amw_process.PROCESS_REV_ID%type;
1326: l_SIGNIFICANT_PROCESS_FLAG amw_process.SIGNIFICANT_PROCESS_FLAG%type;
1327: l_STANDARD_PROCESS_FLAG amw_process.STANDARD_PROCESS_FLAG%type;
1328: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
1329: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
1330: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
1331: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
1332: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
1325: l_RL_PROCESS_REV_ID amw_process.PROCESS_REV_ID%type;
1326: l_SIGNIFICANT_PROCESS_FLAG amw_process.SIGNIFICANT_PROCESS_FLAG%type;
1327: l_STANDARD_PROCESS_FLAG amw_process.STANDARD_PROCESS_FLAG%type;
1328: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
1329: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
1330: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
1331: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
1332: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
1333: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
1326: l_SIGNIFICANT_PROCESS_FLAG amw_process.SIGNIFICANT_PROCESS_FLAG%type;
1327: l_STANDARD_PROCESS_FLAG amw_process.STANDARD_PROCESS_FLAG%type;
1328: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
1329: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
1330: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
1331: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
1332: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
1333: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
1334: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
1327: l_STANDARD_PROCESS_FLAG amw_process.STANDARD_PROCESS_FLAG%type;
1328: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
1329: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
1330: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
1331: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
1332: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
1333: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
1334: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
1335: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
1328: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
1329: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
1330: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
1331: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
1332: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
1333: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
1334: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
1335: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
1336: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
1329: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
1330: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
1331: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
1332: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
1333: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
1334: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
1335: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
1336: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
1337: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
1330: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
1331: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
1332: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
1333: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
1334: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
1335: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
1336: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
1337: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
1338: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
1331: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
1332: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
1333: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
1334: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
1335: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
1336: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
1337: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
1338: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
1339: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
1332: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
1333: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
1334: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
1335: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
1336: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
1337: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
1338: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
1339: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
1340: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
1333: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
1334: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
1335: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
1336: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
1337: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
1338: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
1339: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
1340: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
1341: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
1334: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
1335: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
1336: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
1337: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
1338: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
1339: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
1340: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
1341: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
1342: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
1335: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
1336: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
1337: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
1338: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
1339: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
1340: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
1341: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
1342: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
1343: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
1336: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
1337: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
1338: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
1339: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
1340: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
1341: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
1342: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
1343: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
1344: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
1337: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
1338: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
1339: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
1340: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
1341: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
1342: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
1343: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
1344: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
1345: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
1338: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
1339: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
1340: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
1341: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
1342: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
1343: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
1344: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
1345: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
1346: l_SECURITY_GROUP_ID amw_process.SECURITY_GROUP_ID%type;
1339: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
1340: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
1341: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
1342: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
1343: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
1344: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
1345: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
1346: l_SECURITY_GROUP_ID amw_process.SECURITY_GROUP_ID%type;
1347: l_PROCESS_TYPE amw_process.PROCESS_TYPE%type;
1340: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
1341: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
1342: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
1343: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
1344: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
1345: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
1346: l_SECURITY_GROUP_ID amw_process.SECURITY_GROUP_ID%type;
1347: l_PROCESS_TYPE amw_process.PROCESS_TYPE%type;
1348: l_CONTROL_ACTIVITY_TYPE amw_process.CONTROL_ACTIVITY_TYPE%type;
1341: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
1342: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
1343: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
1344: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
1345: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
1346: l_SECURITY_GROUP_ID amw_process.SECURITY_GROUP_ID%type;
1347: l_PROCESS_TYPE amw_process.PROCESS_TYPE%type;
1348: l_CONTROL_ACTIVITY_TYPE amw_process.CONTROL_ACTIVITY_TYPE%type;
1349:
1342: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
1343: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
1344: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
1345: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
1346: l_SECURITY_GROUP_ID amw_process.SECURITY_GROUP_ID%type;
1347: l_PROCESS_TYPE amw_process.PROCESS_TYPE%type;
1348: l_CONTROL_ACTIVITY_TYPE amw_process.CONTROL_ACTIVITY_TYPE%type;
1349:
1350:
1343: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
1344: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
1345: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
1346: l_SECURITY_GROUP_ID amw_process.SECURITY_GROUP_ID%type;
1347: l_PROCESS_TYPE amw_process.PROCESS_TYPE%type;
1348: l_CONTROL_ACTIVITY_TYPE amw_process.CONTROL_ACTIVITY_TYPE%type;
1349:
1350:
1351: begin
1344: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
1345: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
1346: l_SECURITY_GROUP_ID amw_process.SECURITY_GROUP_ID%type;
1347: l_PROCESS_TYPE amw_process.PROCESS_TYPE%type;
1348: l_CONTROL_ACTIVITY_TYPE amw_process.CONTROL_ACTIVITY_TYPE%type;
1349:
1350:
1351: begin
1352: -- find out if the latest revision for target is approved or not.
1402: l_ATTRIBUTE15,
1403: l_SECURITY_GROUP_ID,
1404: l_PROCESS_TYPE,
1405: l_CONTROL_ACTIVITY_TYPE
1406: from amw_process
1407: where process_id = p_process_id
1408: and end_date is null;
1409:
1410:
1407: where process_id = p_process_id
1408: and end_date is null;
1409:
1410:
1411: update amw_process_organization
1412: set RL_PROCESS_REV_ID = l_RL_PROCESS_REV_ID,
1413: SIGNIFICANT_PROCESS_FLAG = l_SIGNIFICANT_PROCESS_FLAG,
1414: STANDARD_PROCESS_FLAG = l_STANDARD_PROCESS_FLAG,
1415: PROCESS_CATEGORY_CODE = l_PROCESS_CATEGORY,
1673: cursor c1 (l_pid number) is
1674: select ah.child_id child_process_id
1675: from amw_approved_hierarchies ah
1676: where ah.parent_id = (select pp.process_id
1677: from amw_process pp
1678: where pp.process_id = ah.parent_id
1679: and pp.approval_date is not null
1680: and pp.approval_end_date is null
1681: and pp.deletion_date is null)
1679: and pp.approval_date is not null
1680: and pp.approval_end_date is null
1681: and pp.deletion_date is null)
1682: and ah.child_id = ( select Cp.process_id
1683: from amw_process Cp
1684: where Cp.process_id = ah.child_id
1685: and Cp.approval_date is not null
1686: and Cp.approval_end_date is null
1687: and Cp.deletion_date is null)
1789: elsif p_revise_existing = 'N' then
1790: -- Check if the process got deleted and approved..In this case we need to bring this back ...
1791: BEGIN
1792: select 'Y' into pex
1793: from amw_process_organization
1794: where organization_id = p_org_id
1795: and process_id = l_child_process_id
1796: and end_date is null
1797: and approval_date is not null
1847: cursor c1 (l_pid number) is
1848: select ah.child_id child_process_id
1849: from amw_approved_hierarchies ah
1850: where ah.parent_id = (select pp.process_id
1851: from amw_process pp
1852: where pp.process_id = ah.parent_id
1853: and pp.approval_date is not null
1854: and pp.approval_end_date is null
1855: and pp.deletion_date is null)
1853: and pp.approval_date is not null
1854: and pp.approval_end_date is null
1855: and pp.deletion_date is null)
1856: and ah.child_id = ( select Cp.process_id
1857: from amw_process Cp
1858: where Cp.process_id = ah.child_id
1859: and Cp.approval_date is not null
1860: and Cp.approval_end_date is null
1861: and Cp.deletion_date is null)
1944:
1945: -- Check if the process got deleted and approved..In this case we need to bring this back ...
1946: BEGIN
1947: select 'Y' into pex
1948: from amw_process_organization
1949: where organization_id = p_org_id
1950: and process_id = l_child_process_id
1951: and end_date is null
1952: and approval_date is not null
2039: cursor c1 (l_pid number) is
2040: select ah.child_id child_process_id
2041: from amw_approved_hierarchies ah
2042: where ah.parent_id = (select pp.process_id
2043: from amw_process pp
2044: where pp.process_id = ah.parent_id
2045: and pp.approval_date is not null
2046: and pp.approval_end_date is null
2047: and pp.deletion_date is null)
2045: and pp.approval_date is not null
2046: and pp.approval_end_date is null
2047: and pp.deletion_date is null)
2048: and ah.child_id = ( select Cp.process_id
2049: from amw_process Cp
2050: where Cp.process_id = ah.child_id
2051: and Cp.approval_date is not null
2052: and Cp.approval_end_date is null
2053: and Cp.deletion_date is null)
2102: elsif pex = 'D' then
2103: -- Check if the process got deleted and approved..In this case we need to bring this back ...
2104: BEGIN
2105: select 'Y' into pex
2106: from amw_process_organization
2107: where organization_id = Org_Ids(indx)
2108: and process_id = l_child_process_id
2109: and end_date is null
2110: and approval_date is not null
2152: l_dummy number;
2153: begin
2154: select 1
2155: into l_dummy
2156: from amw_process_locks
2157: where locked_process_id = p_process_id
2158: and organization_id = p_org_id;
2159:
2160: return true;
2174: l_dummy number;
2175: begin
2176: select 1
2177: into l_dummy
2178: from amw_process_organization
2179: where process_id = p_process_id
2180: and organization_id = p_org_id
2181: and end_date is null
2182: and approval_status = 'PA';
2203:
2204: -- KSR CHANGES END.....
2205: revise_process_if_necessary (p_org_id, p_process_id);
2206:
2207: update amw_process_organization
2208: set deletion_date = null,
2209: object_version_number = object_version_number + 1
2210: where process_id = p_process_id
2211: and organization_id = p_org_id
2220: begin
2221: -- check if the latest version is end-dated
2222: select deletion_date
2223: into l_del_date
2224: from amw_process_organization
2225: where process_id = p_process_id
2226: and organization_id = p_org_id
2227: and end_date is null;
2228:
2248: cursor c1 (l_pid number) is
2249: select ah.child_id child_process_id
2250: from amw_approved_hierarchies ah
2251: where ah.parent_id = (select pp.process_id
2252: from amw_process pp
2253: where pp.process_id = ah.parent_id
2254: and pp.approval_date is not null
2255: and pp.approval_end_date is null
2256: and pp.deletion_date is null)
2254: and pp.approval_date is not null
2255: and pp.approval_end_date is null
2256: and pp.deletion_date is null)
2257: and ah.child_id = ( select Cp.process_id
2258: from amw_process Cp
2259: where Cp.process_id = ah.child_id
2260: and Cp.approval_date is not null
2261: and Cp.approval_end_date is null
2262: and Cp.deletion_date is null)
2477: procedure upd_ltst_risk_count(p_org_id in number, p_process_id in number) is
2478:
2479: cursor c1 is
2480: (select process_id
2481: from amw_process_organization
2482: where organization_id = p_org_id
2483: and process_id in ( select parent_id
2484: from amw_latest_hierarchies
2485: start with child_id = p_process_id and organization_id = p_org_id
2492: );
2493: --ko replacing the below clause for removing amw_org_hierarchy_denorm usage...
2494: /*
2495: (select process_id
2496: from amw_process_organization
2497: where organization_id = p_org_id
2498: and process_id in ( select parent_child_id
2499: from amw_org_hierarchy_denorm
2500: where process_id = p_process_id
2507: select p_process_id from dual);
2508: */
2509: cursor c2 is
2510: select process_id
2511: from amw_process_organization
2512: where end_date is null
2513: and organization_id = p_org_id;
2514:
2515: type t_n is table of number;
2528: end if;
2529:
2530: if x.exists(1) then
2531: forall ctr in x.first .. x.last
2532: update amw_process_organization
2533: set risk_count_latest = (select count(*) from (
2534: select distinct risk_id from amw_risk_associations
2535: where pk1 = p_org_id
2536: and pk2 in (
2571:
2572:
2573: cursor c1 is
2574: select process_id
2575: from amw_process_organization
2576: where organization_id = p_org_id
2577: and process_id in ( select parent_id
2578: from amw_latest_hierarchies
2579: start with child_id = p_process_id and organization_id = p_org_id
2585: and end_date is null;
2586: --ko replacing the below clause for removing amw_org_hierarchy_denorm usage...
2587: /*
2588: (select process_id
2589: from amw_process_organization
2590: where organization_id = p_org_id
2591: and process_id in ( select parent_child_id
2592: from amw_org_hierarchy_denorm
2593: where process_id = p_process_id
2601: */
2602:
2603: cursor c2 is
2604: select process_id
2605: from amw_process_organization
2606: where end_date is null
2607: and organization_id = p_org_id;
2608:
2609: type t_n is table of number;
2622: end if;
2623:
2624: if x.exists(1) then
2625: forall ctr in x.first .. x.last
2626: update amw_process_organization
2627: set control_count_latest = (select count(*) from (
2628: select distinct control_id from amw_control_associations
2629: where pk1 = p_org_id
2630: and pk2 in ( select child_id
2659: procedure upd_appr_risk_count(p_org_id in number, p_process_id in number) is
2660:
2661: cursor c1 is
2662: select process_id
2663: from amw_process_organization
2664: where organization_id = p_org_id
2665: and process_id in ( select parent_id
2666: from amw_approved_hierarchies
2667: start with child_id = p_process_id and organization_id = p_org_id
2674: and end_date is null;
2675: --ko replacing the below clause for removing amw_org_hierarchy_denorm usage...
2676: /*
2677: (select process_id
2678: from amw_process_organization
2679: where organization_id = p_org_id
2680: and process_id in ( select parent_child_id
2681: from amw_org_hierarchy_denorm
2682: where process_id = p_process_id
2689: select p_process_id from dual);
2690: */
2691: cursor c2 is
2692: select process_id
2693: from amw_process_organization
2694: where approval_date is not null
2695: and approval_end_date is null
2696: and organization_id = p_org_id;
2697: type t_n is table of number;
2710: end if;
2711:
2712: if x.exists(1) then
2713: forall ctr in x.first .. x.last
2714: update amw_process_organization
2715: set risk_count = (select count(*) from (
2716: select distinct risk_id from amw_risk_associations
2717: where pk1 = p_org_id
2718: and pk2 in ( select child_id
2754: procedure upd_appr_control_count(p_org_id in number, p_process_id in number) is
2755:
2756: cursor c1 is
2757: select process_id
2758: from amw_process_organization
2759: where organization_id = p_org_id
2760: and process_id in ( select parent_id
2761: from amw_approved_hierarchies
2762: start with child_id = p_process_id and organization_id = p_org_id
2769: and end_date is null;
2770: --ko replacing the below clause for removing amw_org_hierarchy_denorm usage...
2771: /*
2772: (select process_id
2773: from amw_process_organization
2774: where organization_id = p_org_id
2775: and process_id in ( select parent_child_id
2776: from amw_org_hierarchy_denorm
2777: where process_id = p_process_id
2784: select p_process_id from dual);
2785: */
2786: cursor c2 is
2787: select process_id
2788: from amw_process_organization
2789: where approval_date is not null
2790: and approval_end_date is null
2791: and organization_id = p_org_id;
2792:
2806: end if;
2807:
2808: if x.exists(1) then
2809: forall ctr in x.first .. x.last
2810: update amw_process_organization
2811: set control_count= (select count(*) from (
2812: select distinct control_id from amw_control_associations
2813: where pk1 = p_org_id
2814: and pk2 in ( select child_id
2872: where NVL( AAUV.DATE_TO,SYSDATE ) >= SYSDATE
2873: and 'Y' = AMW_UTILITY_PVT.IS_ORG_REGISTERED(aauv.ORGANIZATION_ID)
2874: and aauv.organization_id not in(
2875: select distinct organization_id
2876: from amw_process_organization
2877: where process_id = pid
2878: and end_date is null
2879: and (
2880: deletion_date is null or
2889: where NVL( AAUV.DATE_TO,SYSDATE ) >= SYSDATE
2890: and 'Y' = AMW_UTILITY_PVT.IS_ORG_REGISTERED(aauv.ORGANIZATION_ID)
2891: and aauv.organization_id not in(
2892: select distinct organization_id
2893: from amw_process_organization
2894: where process_id = pid
2895: and end_date is null
2896: and (
2897: deletion_date is null or
3005: conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING','Process cannot be associated to some organizations');
3006: end if;
3007:
3008: -- update the org count..
3009: update amw_process AP
3010: set AP.org_count = (select count(o.organization_id)
3011: from hr_all_organization_units o,
3012: hr_organization_information o2
3013: WHERE o.organization_id = o2.organization_id
3014: and o2.org_information_context = 'CLASS'
3015: and o2.org_information1 = 'AMW_AUDIT_UNIT'
3016: and o2.org_information2 = 'Y'
3017: and exists (select 1
3018: from amw_process_organization APO
3019: WHERE APO.organization_id = o.ORGANIZATION_ID
3020: AND APO.process_id = AP.PROCESS_ID
3021: and APO.end_date is null
3022: and (APO.deletion_date is null or (APO.deletion_date is not null and APO.approval_date is null)))),
3044:
3045:
3046: if p_org_range_from is not null and p_org_range_to is not null then
3047:
3048: update amw_process_organization APO
3049: set APO.risk_count_latest = (
3050: select count(distinct ARA.risk_id) from amw_risk_associations ARA
3051: where ARA.pk1 = APO.ORGANIZATION_ID
3052: and ARA.pk2 in ( select alh.child_id
3097: where NVL( AAUV.DATE_TO,SYSDATE ) >= SYSDATE
3098: and NAME >= p_org_range_from and substr(NAME,0,length(p_org_range_to))<= p_org_range_to);
3099: else
3100:
3101: update amw_process_organization APO
3102: set APO.risk_count_latest = (
3103: select count(distinct ARA.risk_id) from amw_risk_associations ARA
3104: where ARA.pk1 = APO.ORGANIZATION_ID
3105: and ARA.pk2 in ( select alh.child_id
3164: if fnd_profile.value('AMW_PROC_ORG_ASS_APPRV') = 'Y' then
3165:
3166: if p_org_range_from is not null and p_org_range_to is not null then
3167:
3168: update amw_process_organization APO
3169: set APO.risk_count = (
3170: select count(distinct ARA.risk_id) from amw_risk_associations ARA
3171: where ARA.pk1 = APO.ORGANIZATION_ID
3172: and ARA.pk2 in ( select alh.child_id
3225: where NVL( AAUV.DATE_TO,SYSDATE ) >= SYSDATE
3226: and NAME >= p_org_range_from and substr(NAME,0,length(p_org_range_to))<= p_org_range_to);
3227: else
3228:
3229: update amw_process_organization APO
3230: set APO.risk_count = (
3231: select count(distinct ARA.risk_id) from amw_risk_associations ARA
3232: where ARA.pk1 = APO.ORGANIZATION_ID
3233: and ARA.pk2 in ( select alh.child_id
3464:
3465:
3466: l_org_id NUMBER;
3467:
3468: type t_org_proc_rec is record (organization_id amw_process_organization.organization_id%type,
3469: process_id amw_process_organization.process_id%type);
3470:
3471: type t_org_proc_tbl is table of t_org_proc_rec;
3472: l_org_proc_tbl t_org_proc_tbl;
3465:
3466: l_org_id NUMBER;
3467:
3468: type t_org_proc_rec is record (organization_id amw_process_organization.organization_id%type,
3469: process_id amw_process_organization.process_id%type);
3470:
3471: type t_org_proc_tbl is table of t_org_proc_rec;
3472: l_org_proc_tbl t_org_proc_tbl;
3473:
3573: conc_status:=FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING','Process cannot be associated to some organizations');
3574: end if;
3575:
3576: /* This block is to update the latest and approved risk control counts..*/
3577: l_sql_string := 'select organization_id, process_id from amw_process_organization where revision_number = 1' ||
3578: ' and process_id <> -2 and organization_id in ( ' ||
3579: replace(rtrim(l_org_string,'x'),'x',',') || ')';
3580: open l_proc_cur for l_sql_string;
3581: fetch l_proc_cur bulk collect into l_org_proc_tbl;
3592: l_procs_tbl(l_procs_tbl.last) := l_org_proc_tbl(ctr).process_id;
3593: end loop;
3594:
3595: forall ctr in l_orgs_tbl.first .. l_orgs_tbl.last
3596: update amw_process_organization
3597: set risk_count_latest = (select count(*) from (
3598: select distinct risk_id from amw_risk_associations
3599: where pk1 = l_orgs_tbl(ctr)
3600: and pk2 in (select alh.child_id
3648: if fnd_profile.value('AMW_PROC_ORG_ASS_APPRV') = 'Y' then
3649:
3650:
3651: forall ctr in l_orgs_tbl.first .. l_orgs_tbl.last
3652: update amw_process_organization
3653: set risk_count = (select count(*) from (
3654: select distinct risk_id from amw_risk_associations
3655: where pk1 = l_orgs_tbl(ctr)
3656: and pk2 in ( select alh.child_id
3714: fetch c_processes bulk collect into x_ptbl;
3715: close c_processes;
3716: if(x_ptbl.exists(1)) then
3717: forall i in x_ptbl.first .. x_ptbl.last
3718: update amw_process
3719: set org_count = (select count(*) from
3720: (select distinct organization_id
3721: from amw_process_organization
3722: where process_id = x_ptbl(i)
3717: forall i in x_ptbl.first .. x_ptbl.last
3718: update amw_process
3719: set org_count = (select count(*) from
3720: (select distinct organization_id
3721: from amw_process_organization
3722: where process_id = x_ptbl(i)
3723: and end_date is null
3724: and (deletion_date is null or (deletion_date is not null and approval_date is null)))),
3725: object_version_number = object_version_number + 1
4056:
4057: -- Make sure latest revision of parent is existing in the organization..
4058:
4059: SELECT 1 INTO l_dummy
4060: FROM amw_process_organization
4061: where process_id = p_parent_id
4062: and organization_id = p_organization_id
4063: and end_date is null
4064: and deletion_date is null;
4068: produce_err_if_pa_or_locked(p_org_id => p_organization_id,p_process_id => p_parent_id);
4069: -- Parent can be updated...Add the child and parent... Circular hierarchy check is taken care in the procedure.
4070: -- Make sure latest revision of child exists in the organization
4071: SELECT 1 INTO l_dummy
4072: FROM amw_process_organization
4073: where process_id = p_child_id
4074: and organization_id = p_organization_id
4075: and end_date is null
4076: and deletion_date is null;
4110:
4111: -- Make sure that the process not existing in the org.
4112: BEGIN
4113: SELECT 1 INTO l_dummy
4114: FROM amw_process_organization
4115: where process_id = p_child_id
4116: and organization_id = p_organization_id
4117: and end_date is null
4118: and deletion_date is null;
4205:
4206: -- Make sure latest revision of parent is existing in the organization..
4207:
4208: SELECT 1 INTO l_dummy
4209: FROM amw_process_organization
4210: where process_id = p_parent_id
4211: and organization_id = p_organization_id
4212: and end_date is null
4213: and deletion_date is null;
4220:
4221: -- Make sure latest revision of child exists in the organization
4222:
4223: SELECT 1 INTO l_dummy
4224: FROM amw_process_organization
4225: where process_id = p_child_id
4226: and organization_id = p_organization_id
4227: and end_date is null
4228: and deletion_date is null;
4683: l_dummy number;
4684: begin
4685: select 1
4686: into l_dummy
4687: from amw_process_organization
4688: where process_id = p_process_id
4689: and organization_id = p_organization_id
4690: and approval_status = 'A';
4691:
4732:
4733: -- check if the process is draft
4734:
4735: select approval_status into appstatus
4736: from amw_process_organization
4737: where process_id = p_process_id
4738: and organization_id = p_organization_id
4739: and end_date is null;
4740:
4819: RAISE FND_API.G_EXC_ERROR;
4820: END IF;
4821:
4822: select process_org_rev_id
4823: into previd from amw_process_organization
4824: where process_id = p_process_id
4825: and organization_id = p_organization_id
4826: and end_date is null;
4827:
4853:
4854:
4855:
4856:
4857: delete from amw_process_organization
4858: where process_id = p_process_id
4859: and organization_id = p_organization_id
4860: and end_date is null;
4861:
4859: and organization_id = p_organization_id
4860: and end_date is null;
4861:
4862:
4863: update amw_process_organization
4864: set end_date = null
4865: where process_id = p_process_id
4866: and organization_id = p_organization_id
4867: and approval_date is not null
4869:
4870: -- If the previous version is deleted; then we make it deleted...
4871: begin
4872: select 1 into l_dummy
4873: from amw_process_organization
4874: where process_id = p_process_id
4875: and organization_id = p_organization_id
4876: and deletion_date is not null
4877: and end_date is null;
4909:
4910: delete from amw_latest_hierarchies
4911: where parent_id = p_process_id
4912: and organization_id = p_organization_id;
4913: delete from amw_process_organization where process_id = p_process_id and organization_id = p_organization_id;
4914:
4915: end if;
4916:
4917: -- perform other common delete operations
4992: and association_creation_date is null
4993: and object_type = 'CTRL_ORG';
4994:
4995:
4996: FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(X_entity_name => 'AMW_PROCESS_ORGANIZATION',
4997: X_pk1_value => previd);
4998:
4999: -- cancel existing change requests
5000: -- update org count..
5292: p_process_id in number
5293: ) is
5294:
5295:
5296: l_RL_PROCESS_REV_ID amw_process.PROCESS_REV_ID%type;
5297: l_SIGNIFICANT_PROCESS_FLAG amw_process.SIGNIFICANT_PROCESS_FLAG%type;
5298: l_STANDARD_PROCESS_FLAG amw_process.STANDARD_PROCESS_FLAG%type;
5299: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
5300: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
5293: ) is
5294:
5295:
5296: l_RL_PROCESS_REV_ID amw_process.PROCESS_REV_ID%type;
5297: l_SIGNIFICANT_PROCESS_FLAG amw_process.SIGNIFICANT_PROCESS_FLAG%type;
5298: l_STANDARD_PROCESS_FLAG amw_process.STANDARD_PROCESS_FLAG%type;
5299: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
5300: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
5301: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
5294:
5295:
5296: l_RL_PROCESS_REV_ID amw_process.PROCESS_REV_ID%type;
5297: l_SIGNIFICANT_PROCESS_FLAG amw_process.SIGNIFICANT_PROCESS_FLAG%type;
5298: l_STANDARD_PROCESS_FLAG amw_process.STANDARD_PROCESS_FLAG%type;
5299: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
5300: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
5301: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
5302: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
5295:
5296: l_RL_PROCESS_REV_ID amw_process.PROCESS_REV_ID%type;
5297: l_SIGNIFICANT_PROCESS_FLAG amw_process.SIGNIFICANT_PROCESS_FLAG%type;
5298: l_STANDARD_PROCESS_FLAG amw_process.STANDARD_PROCESS_FLAG%type;
5299: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
5300: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
5301: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
5302: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
5303: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
5296: l_RL_PROCESS_REV_ID amw_process.PROCESS_REV_ID%type;
5297: l_SIGNIFICANT_PROCESS_FLAG amw_process.SIGNIFICANT_PROCESS_FLAG%type;
5298: l_STANDARD_PROCESS_FLAG amw_process.STANDARD_PROCESS_FLAG%type;
5299: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
5300: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
5301: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
5302: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
5303: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
5304: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
5297: l_SIGNIFICANT_PROCESS_FLAG amw_process.SIGNIFICANT_PROCESS_FLAG%type;
5298: l_STANDARD_PROCESS_FLAG amw_process.STANDARD_PROCESS_FLAG%type;
5299: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
5300: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
5301: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
5302: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
5303: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
5304: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
5305: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
5298: l_STANDARD_PROCESS_FLAG amw_process.STANDARD_PROCESS_FLAG%type;
5299: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
5300: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
5301: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
5302: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
5303: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
5304: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
5305: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
5306: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
5299: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
5300: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
5301: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
5302: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
5303: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
5304: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
5305: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
5306: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
5307: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
5300: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
5301: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
5302: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
5303: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
5304: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
5305: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
5306: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
5307: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
5308: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
5301: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
5302: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
5303: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
5304: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
5305: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
5306: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
5307: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
5308: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
5309: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
5302: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
5303: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
5304: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
5305: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
5306: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
5307: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
5308: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
5309: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
5310: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
5303: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
5304: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
5305: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
5306: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
5307: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
5308: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
5309: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
5310: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
5311: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
5304: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
5305: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
5306: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
5307: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
5308: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
5309: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
5310: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
5311: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
5312: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
5305: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
5306: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
5307: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
5308: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
5309: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
5310: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
5311: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
5312: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
5313: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
5306: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
5307: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
5308: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
5309: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
5310: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
5311: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
5312: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
5313: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
5314: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
5307: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
5308: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
5309: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
5310: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
5311: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
5312: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
5313: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
5314: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
5315: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
5308: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
5309: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
5310: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
5311: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
5312: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
5313: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
5314: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
5315: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
5316: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
5309: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
5310: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
5311: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
5312: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
5313: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
5314: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
5315: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
5316: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
5317: l_SECURITY_GROUP_ID amw_process.SECURITY_GROUP_ID%type;
5310: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
5311: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
5312: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
5313: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
5314: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
5315: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
5316: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
5317: l_SECURITY_GROUP_ID amw_process.SECURITY_GROUP_ID%type;
5318: l_PROCESS_TYPE amw_process.PROCESS_TYPE%type;
5311: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
5312: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
5313: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
5314: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
5315: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
5316: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
5317: l_SECURITY_GROUP_ID amw_process.SECURITY_GROUP_ID%type;
5318: l_PROCESS_TYPE amw_process.PROCESS_TYPE%type;
5319: l_CONTROL_ACTIVITY_TYPE amw_process.CONTROL_ACTIVITY_TYPE%type;
5312: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
5313: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
5314: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
5315: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
5316: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
5317: l_SECURITY_GROUP_ID amw_process.SECURITY_GROUP_ID%type;
5318: l_PROCESS_TYPE amw_process.PROCESS_TYPE%type;
5319: l_CONTROL_ACTIVITY_TYPE amw_process.CONTROL_ACTIVITY_TYPE%type;
5320:
5313: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
5314: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
5315: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
5316: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
5317: l_SECURITY_GROUP_ID amw_process.SECURITY_GROUP_ID%type;
5318: l_PROCESS_TYPE amw_process.PROCESS_TYPE%type;
5319: l_CONTROL_ACTIVITY_TYPE amw_process.CONTROL_ACTIVITY_TYPE%type;
5320:
5321:
5314: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
5315: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
5316: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
5317: l_SECURITY_GROUP_ID amw_process.SECURITY_GROUP_ID%type;
5318: l_PROCESS_TYPE amw_process.PROCESS_TYPE%type;
5319: l_CONTROL_ACTIVITY_TYPE amw_process.CONTROL_ACTIVITY_TYPE%type;
5320:
5321:
5322: begin
5315: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
5316: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
5317: l_SECURITY_GROUP_ID amw_process.SECURITY_GROUP_ID%type;
5318: l_PROCESS_TYPE amw_process.PROCESS_TYPE%type;
5319: l_CONTROL_ACTIVITY_TYPE amw_process.CONTROL_ACTIVITY_TYPE%type;
5320:
5321:
5322: begin
5323: -- find out if the latest revision for target is approved or not.
5373: l_ATTRIBUTE15,
5374: l_SECURITY_GROUP_ID,
5375: l_PROCESS_TYPE,
5376: l_CONTROL_ACTIVITY_TYPE
5377: from amw_process
5378: where process_id = p_process_id
5379: --ko need to sync with approved revision
5380: -- and end_date is null;
5381: and approval_date is not null
5382: and approval_end_date is null;
5383:
5384:
5385:
5386: update amw_process_organization
5387: set RL_PROCESS_REV_ID = l_RL_PROCESS_REV_ID,
5388: SIGNIFICANT_PROCESS_FLAG = l_SIGNIFICANT_PROCESS_FLAG,
5389: STANDARD_PROCESS_FLAG = l_STANDARD_PROCESS_FLAG,
5390: PROCESS_CATEGORY_CODE = l_PROCESS_CATEGORY,
5563: /*
5564: Synchronize the Process Attributes of the process in a all organizations.
5565: */
5566: procedure sync_process_attributes(p_process_id in number ) is
5567: l_RL_PROCESS_REV_ID amw_process.PROCESS_REV_ID%type;
5568: l_SIGNIFICANT_PROCESS_FLAG amw_process.SIGNIFICANT_PROCESS_FLAG%type;
5569: l_STANDARD_PROCESS_FLAG amw_process.STANDARD_PROCESS_FLAG%type;
5570: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
5571: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
5564: Synchronize the Process Attributes of the process in a all organizations.
5565: */
5566: procedure sync_process_attributes(p_process_id in number ) is
5567: l_RL_PROCESS_REV_ID amw_process.PROCESS_REV_ID%type;
5568: l_SIGNIFICANT_PROCESS_FLAG amw_process.SIGNIFICANT_PROCESS_FLAG%type;
5569: l_STANDARD_PROCESS_FLAG amw_process.STANDARD_PROCESS_FLAG%type;
5570: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
5571: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
5572: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
5565: */
5566: procedure sync_process_attributes(p_process_id in number ) is
5567: l_RL_PROCESS_REV_ID amw_process.PROCESS_REV_ID%type;
5568: l_SIGNIFICANT_PROCESS_FLAG amw_process.SIGNIFICANT_PROCESS_FLAG%type;
5569: l_STANDARD_PROCESS_FLAG amw_process.STANDARD_PROCESS_FLAG%type;
5570: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
5571: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
5572: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
5573: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
5566: procedure sync_process_attributes(p_process_id in number ) is
5567: l_RL_PROCESS_REV_ID amw_process.PROCESS_REV_ID%type;
5568: l_SIGNIFICANT_PROCESS_FLAG amw_process.SIGNIFICANT_PROCESS_FLAG%type;
5569: l_STANDARD_PROCESS_FLAG amw_process.STANDARD_PROCESS_FLAG%type;
5570: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
5571: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
5572: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
5573: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
5574: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
5567: l_RL_PROCESS_REV_ID amw_process.PROCESS_REV_ID%type;
5568: l_SIGNIFICANT_PROCESS_FLAG amw_process.SIGNIFICANT_PROCESS_FLAG%type;
5569: l_STANDARD_PROCESS_FLAG amw_process.STANDARD_PROCESS_FLAG%type;
5570: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
5571: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
5572: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
5573: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
5574: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
5575: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
5568: l_SIGNIFICANT_PROCESS_FLAG amw_process.SIGNIFICANT_PROCESS_FLAG%type;
5569: l_STANDARD_PROCESS_FLAG amw_process.STANDARD_PROCESS_FLAG%type;
5570: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
5571: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
5572: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
5573: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
5574: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
5575: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
5576: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
5569: l_STANDARD_PROCESS_FLAG amw_process.STANDARD_PROCESS_FLAG%type;
5570: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
5571: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
5572: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
5573: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
5574: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
5575: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
5576: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
5577: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
5570: l_PROCESS_CATEGORY amw_process.PROCESS_CATEGORY%type;
5571: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
5572: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
5573: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
5574: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
5575: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
5576: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
5577: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
5578: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
5571: l_STANDARD_VARIATION amw_process.STANDARD_VARIATION%type;
5572: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
5573: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
5574: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
5575: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
5576: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
5577: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
5578: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
5579: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
5572: l_ATTRIBUTE_CATEGORY amw_process.ATTRIBUTE_CATEGORY%type;
5573: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
5574: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
5575: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
5576: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
5577: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
5578: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
5579: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
5580: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
5573: l_ATTRIBUTE1 amw_process.ATTRIBUTE1%type;
5574: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
5575: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
5576: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
5577: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
5578: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
5579: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
5580: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
5581: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
5574: l_ATTRIBUTE2 amw_process.ATTRIBUTE2%type;
5575: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
5576: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
5577: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
5578: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
5579: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
5580: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
5581: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
5582: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
5575: l_ATTRIBUTE3 amw_process.ATTRIBUTE3%type;
5576: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
5577: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
5578: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
5579: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
5580: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
5581: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
5582: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
5583: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
5576: l_ATTRIBUTE4 amw_process.ATTRIBUTE4%type;
5577: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
5578: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
5579: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
5580: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
5581: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
5582: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
5583: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
5584: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
5577: l_ATTRIBUTE5 amw_process.ATTRIBUTE5%type;
5578: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
5579: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
5580: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
5581: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
5582: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
5583: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
5584: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
5585: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
5578: l_ATTRIBUTE6 amw_process.ATTRIBUTE6%type;
5579: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
5580: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
5581: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
5582: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
5583: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
5584: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
5585: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
5586: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
5579: l_ATTRIBUTE7 amw_process.ATTRIBUTE7%type;
5580: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
5581: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
5582: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
5583: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
5584: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
5585: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
5586: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
5587: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
5580: l_ATTRIBUTE8 amw_process.ATTRIBUTE8%type;
5581: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
5582: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
5583: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
5584: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
5585: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
5586: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
5587: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
5588: l_SECURITY_GROUP_ID amw_process.SECURITY_GROUP_ID%type;
5581: l_ATTRIBUTE9 amw_process.ATTRIBUTE9%type;
5582: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
5583: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
5584: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
5585: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
5586: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
5587: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
5588: l_SECURITY_GROUP_ID amw_process.SECURITY_GROUP_ID%type;
5589: l_PROCESS_TYPE amw_process.PROCESS_TYPE%type;
5582: l_ATTRIBUTE10 amw_process.ATTRIBUTE10%type;
5583: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
5584: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
5585: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
5586: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
5587: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
5588: l_SECURITY_GROUP_ID amw_process.SECURITY_GROUP_ID%type;
5589: l_PROCESS_TYPE amw_process.PROCESS_TYPE%type;
5590: l_CONTROL_ACTIVITY_TYPE amw_process.CONTROL_ACTIVITY_TYPE%type;
5583: l_ATTRIBUTE11 amw_process.ATTRIBUTE11%type;
5584: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
5585: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
5586: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
5587: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
5588: l_SECURITY_GROUP_ID amw_process.SECURITY_GROUP_ID%type;
5589: l_PROCESS_TYPE amw_process.PROCESS_TYPE%type;
5590: l_CONTROL_ACTIVITY_TYPE amw_process.CONTROL_ACTIVITY_TYPE%type;
5591: begin
5584: l_ATTRIBUTE12 amw_process.ATTRIBUTE12%type;
5585: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
5586: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
5587: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
5588: l_SECURITY_GROUP_ID amw_process.SECURITY_GROUP_ID%type;
5589: l_PROCESS_TYPE amw_process.PROCESS_TYPE%type;
5590: l_CONTROL_ACTIVITY_TYPE amw_process.CONTROL_ACTIVITY_TYPE%type;
5591: begin
5592: -- find out if the latest revision for target is approved or not.
5585: l_ATTRIBUTE13 amw_process.ATTRIBUTE13%type;
5586: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
5587: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
5588: l_SECURITY_GROUP_ID amw_process.SECURITY_GROUP_ID%type;
5589: l_PROCESS_TYPE amw_process.PROCESS_TYPE%type;
5590: l_CONTROL_ACTIVITY_TYPE amw_process.CONTROL_ACTIVITY_TYPE%type;
5591: begin
5592: -- find out if the latest revision for target is approved or not.
5593: -- if approved, revise it. if draft, don't do anything
5586: l_ATTRIBUTE14 amw_process.ATTRIBUTE14%type;
5587: l_ATTRIBUTE15 amw_process.ATTRIBUTE15%type;
5588: l_SECURITY_GROUP_ID amw_process.SECURITY_GROUP_ID%type;
5589: l_PROCESS_TYPE amw_process.PROCESS_TYPE%type;
5590: l_CONTROL_ACTIVITY_TYPE amw_process.CONTROL_ACTIVITY_TYPE%type;
5591: begin
5592: -- find out if the latest revision for target is approved or not.
5593: -- if approved, revise it. if draft, don't do anything
5594:
5645: l_ATTRIBUTE15,
5646: l_SECURITY_GROUP_ID,
5647: l_PROCESS_TYPE,
5648: l_CONTROL_ACTIVITY_TYPE
5649: from amw_process
5650: where process_id = p_process_id
5651: --ko need to sync with approved revision
5652: -- and end_date is null;
5653: and approval_date is not null
5653: and approval_date is not null
5654: and approval_end_date is null;
5655:
5656: FORALL indx IN Org_Ids.FIRST .. Org_Ids.LAST
5657: update amw_process_organization
5658: set RL_PROCESS_REV_ID = l_RL_PROCESS_REV_ID,
5659: SIGNIFICANT_PROCESS_FLAG = l_SIGNIFICANT_PROCESS_FLAG,
5660: STANDARD_PROCESS_FLAG = l_STANDARD_PROCESS_FLAG,
5661: PROCESS_CATEGORY_CODE = l_PROCESS_CATEGORY,
7047: grants.end_date end_date
7048: FROM fnd_grants grants,
7049: fnd_menus granted_menu,
7050: fnd_objects obj
7051: WHERE obj.obj_name = 'AMW_PROCESS_APPR_ETTY'
7052: AND grants.object_id = obj.object_id
7053: AND grants.grantee_type ='USER'
7054: AND grantee_key like 'HZ_PARTY%'
7055: AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
7068: SELECT grants.grant_guid grant_guid
7069: FROM fnd_grants grants,
7070: fnd_menus granted_menu,
7071: fnd_objects obj
7072: WHERE obj.obj_name = 'AMW_PROCESS_ORGANIZATION'
7073: AND grants.object_id = obj.object_id
7074: AND grants.grantee_type ='USER'
7075: AND grantee_key like 'HZ_PARTY%'
7076: AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
7089: and (grants.grantee_key, obj.object_id ,grants.menu_id ) not in (select grants.grantee_key,obj.object_id, grants.menu_id
7090: FROM fnd_grants grants,
7091: fnd_menus granted_menu,
7092: fnd_objects obj
7093: WHERE obj.obj_name = 'AMW_PROCESS_APPR_ETTY'
7094: AND grants.object_id = obj.object_id
7095: AND grants.grantee_type ='USER'
7096: AND grantee_key like 'HZ_PARTY%'
7097: AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
7159: AMW_SECURITY_PUB.grant_role_guid
7160: (
7161: p_api_version => 1,
7162: p_role_name => 'AMW_ORG_PROC_OWNER_ROLE',
7163: p_object_name => 'AMW_PROCESS_ORGANIZATION',
7164: p_instance_type => 'INSTANCE',
7165: p_instance_set_id => null,
7166: p_instance_pk1_value => p_org_id,
7167: p_instance_pk2_value => p_process_id,
7180: AMW_SECURITY_PUB.grant_role_guid
7181: (
7182: p_api_version => 1,
7183: p_role_name => 'AMW_ORG_PROC_FIN_OWNER_ROLE',
7184: p_object_name => 'AMW_PROCESS_ORGANIZATION',
7185: p_instance_type => 'INSTANCE',
7186: p_instance_set_id => null,
7187: p_instance_pk1_value => p_org_id,
7188: p_instance_pk2_value => p_process_id,
7201: AMW_SECURITY_PUB.grant_role_guid
7202: (
7203: p_api_version => 1,
7204: p_role_name => 'AMW_ORG_PROC_APPL_OWNER_ROLE',
7205: p_object_name => 'AMW_PROCESS_ORGANIZATION',
7206: p_instance_type => 'INSTANCE',
7207: p_instance_set_id => null,
7208: p_instance_pk1_value => p_org_id,
7209: p_instance_pk2_value => p_process_id,
7271: BULK COLLECT INTO l_grant_guid
7272: FROM fnd_grants grants,
7273: fnd_menus granted_menu,
7274: fnd_objects obj
7275: WHERE obj.obj_name = 'AMW_PROCESS_ORGANIZATION'
7276: AND grants.object_id = obj.object_id
7277: AND grants.grantee_type ='USER'
7278: AND grantee_key like 'HZ_PARTY%'
7279: AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
7288: and (grants.grantee_key) not in ( select grants.grantee_key
7289: FROM fnd_grants grants,
7290: fnd_menus granted_menu,
7291: fnd_objects obj
7292: WHERE obj.obj_name = 'AMW_PROCESS_APPR_ETTY'
7293: AND grants.object_id = obj.object_id
7294: AND grants.grantee_type ='USER'
7295: AND grantee_key like 'HZ_PARTY%'
7296: AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
7361: l_end_date
7362: FROM fnd_grants grants,
7363: fnd_menus granted_menu,
7364: fnd_objects obj
7365: WHERE obj.obj_name = 'AMW_PROCESS_APPR_ETTY'
7366: AND grants.object_id = obj.object_id
7367: AND grants.grantee_type ='USER'
7368: AND grantee_key like 'HZ_PARTY%'
7369: AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
7378: and (grants.grantee_key) not in ( select grants.grantee_key
7379: FROM fnd_grants grants,
7380: fnd_menus granted_menu,
7381: fnd_objects obj
7382: WHERE obj.obj_name = 'AMW_PROCESS_ORGANIZATION'
7383: AND grants.object_id = obj.object_id
7384: AND grants.grantee_type ='USER'
7385: AND grantee_key like 'HZ_PARTY%'
7386: AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
7398: AMW_SECURITY_PUB.grant_role_guid
7399: (
7400: p_api_version => 1,
7401: p_role_name => p_org_menu_name,
7402: p_object_name => 'AMW_PROCESS_ORGANIZATION',
7403: p_instance_type => 'INSTANCE',
7404: p_instance_set_id => null,
7405: p_instance_pk1_value => p_org_id,
7406: p_instance_pk2_value => p_process_id,
7498: cursor c1 (l_pid number) is
7499: select ah.child_id child_process_id
7500: from amw_approved_hierarchies ah
7501: where ah.parent_id = (select pp.process_id
7502: from amw_process pp
7503: where pp.process_id = ah.parent_id
7504: and pp.approval_date is not null
7505: and pp.approval_end_date is null
7506: and pp.deletion_date is null)
7504: and pp.approval_date is not null
7505: and pp.approval_end_date is null
7506: and pp.deletion_date is null)
7507: and ah.child_id = ( select Cp.process_id
7508: from amw_process Cp
7509: where Cp.process_id = ah.child_id
7510: and Cp.approval_date is not null
7511: and Cp.approval_end_date is null
7512: and Cp.deletion_date is null)
7545: return;
7546: END IF;
7547:
7548: SELECT 1 INTO l_dummy
7549: from amw_process_organization
7550: where organization_id = p_org_id
7551: and process_id = p_process_id
7552: and end_date is null
7553: and deletion_date is null;
7567: --attachment bug5968299 codefix start
7568:
7569: select approval_status
7570: into l_approval_status
7571: from amw_process_organization
7572: where process_id = p_process_id
7573: and organization_id = p_org_id
7574: and end_date is null
7575: and deletion_date is null;
7595: p_org_id || p_process_id );
7596: end if;
7597: if l_approval_status='A' then
7598: select process_org_rev_id into l_proc_latest
7599: from amw_process_organization
7600: where process_id = p_process_id
7601: and organization_id = p_org_id
7602: and end_date is null;
7603:
7601: and organization_id = p_org_id
7602: and end_date is null;
7603:
7604: select process_org_rev_id into l_proc_prev
7605: from amw_process_organization
7606: where process_id = p_process_id
7607: and organization_id = p_org_id
7608: and approval_date is not null
7609: and approval_end_date is null;
7607: and organization_id = p_org_id
7608: and approval_date is not null
7609: and approval_end_date is null;
7610:
7611: FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(X_from_entity_name => 'AMW_PROCESS_ORGANIZATION'
7612: ,X_from_pk1_value => l_proc_prev
7613: ,X_to_entity_name => 'AMW_PROCESS_ORGANIZATION'
7614: ,X_to_pk1_value => l_proc_latest
7615: ,X_created_by => G_USER_ID
7609: and approval_end_date is null;
7610:
7611: FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(X_from_entity_name => 'AMW_PROCESS_ORGANIZATION'
7612: ,X_from_pk1_value => l_proc_prev
7613: ,X_to_entity_name => 'AMW_PROCESS_ORGANIZATION'
7614: ,X_to_pk1_value => l_proc_latest
7615: ,X_created_by => G_USER_ID
7616: ,X_last_update_login => G_LOGIN_ID
7617: );
7724: cursor c1 (l_pid number) is
7725: select ah.child_id child_process_id
7726: from amw_approved_hierarchies ah
7727: where ah.parent_id = (select pp.process_id
7728: from amw_process pp
7729: where pp.process_id = ah.parent_id
7730: and pp.approval_date is not null
7731: and pp.approval_end_date is null
7732: and pp.deletion_date is null)
7730: and pp.approval_date is not null
7731: and pp.approval_end_date is null
7732: and pp.deletion_date is null)
7733: and ah.child_id = ( select Cp.process_id
7734: from amw_process Cp
7735: where Cp.process_id = ah.child_id
7736: and Cp.approval_date is not null
7737: and Cp.approval_end_date is null
7738: and Cp.deletion_date is null)
7920: conc_status boolean;
7921:
7922: cursor all_orgs is
7923: select distinct organization_id
7924: from amw_process_organization
7925: where process_id = -2;
7926:
7927: begin
7928:
7937: amw_rl_hierarchy_pkg.update_denorm (org_cursor.organization_id, sysdate);
7938: --updates approved hier denorm
7939: amw_rl_hierarchy_pkg.update_approved_denorm (org_cursor.organization_id, sysdate);
7940: */
7941: update amw_process_organization
7942: set risk_count = null,
7943: control_count = null,
7944: risk_count_latest = null,
7945: control_count_latest = null
7965: amw_rl_hierarchy_pkg.update_denorm (p_org_id, sysdate);
7966: --updates approved hier denorm
7967: amw_rl_hierarchy_pkg.update_approved_denorm (p_org_id, sysdate);
7968: */
7969: update amw_process_organization
7970: set risk_count = null,
7971: control_count = null,
7972: risk_count_latest = null,
7973: control_count_latest = null
8209: l_child_id := to_number(childstr);
8210:
8211: BEGIN
8212: SELECT 1 INTO l_dummy
8213: FROM amw_process_organization
8214: where process_id = l_child_id
8215: and organization_id = p_organization_id
8216: and end_date is null
8217: and deletion_date is null;
8391: init;
8392: SELECT DISTINCT aauv.organization_id
8393: BULK COLLECT INTO Org_Ids
8394: FROM amw_audit_units_v aauv,
8395: amw_process_organization apo
8396: WHERE aauv.organization_id=apo.organization_id
8397: AND NVL( AAUV.DATE_TO,SYSDATE ) >= SYSDATE
8398: AND apo.process_id=p_process_id
8399: AND apo.end_date IS NULL
8403: init;
8404: select distinct aauv.organization_id
8405: BULK COLLECT INTO Org_Ids
8406: from amw_audit_units_v aauv,
8407: amw_process_organization apo
8408: where aauv.organization_id=apo.organization_id
8409: and NVL( AAUV.DATE_TO,SYSDATE ) >= SYSDATE
8410: and apo.process_id=p_process_id
8411: and apo.end_date is null
8494:
8495: IF p_sync_mode = 'Y' or p_sync_hierarchy = 'Y' then
8496:
8497: -- update the org count..
8498: update amw_process AP
8499: set AP.org_count = (select COUNT(o.organization_id)
8500: from hr_all_organization_units o,
8501: hr_organization_information o2,
8502: amw_process_organization APO
8498: update amw_process AP
8499: set AP.org_count = (select COUNT(o.organization_id)
8500: from hr_all_organization_units o,
8501: hr_organization_information o2,
8502: amw_process_organization APO
8503: WHERE o.organization_id = o2.organization_id
8504: AND APO.organization_id = o.ORGANIZATION_ID
8505: and o2.org_information_context = 'CLASS'
8506: and o2.org_information1 = 'AMW_AUDIT_UNIT'
8611: where NVL( AAUV.DATE_TO,SYSDATE ) >= SYSDATE
8612: and 'Y' = AMW_UTILITY_PVT.IS_ORG_REGISTERED(aauv.ORGANIZATION_ID)
8613: and aauv.organization_id not in(
8614: select distinct organization_id
8615: from amw_process_organization
8616: where process_id = pid
8617: and end_date is null
8618: and (
8619: deletion_date is null or
8628: where NVL( AAUV.DATE_TO,SYSDATE ) >= SYSDATE
8629: and 'Y' = AMW_UTILITY_PVT.IS_ORG_REGISTERED(aauv.ORGANIZATION_ID)
8630: and aauv.organization_id not in(
8631: select distinct organization_id
8632: from amw_process_organization
8633: where process_id = pid
8634: and end_date is null
8635: and (
8636: deletion_date is null or