APPS.AMW_ORG_HIERARCHY_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 19

select parent_id
    into l_dummy
    from amw_latest_hierarchies
    where parent_id = p_child_process_id
    start with child_id = p_parent_process_id  and organization_id = p_org_id
    connect by prior parent_id = child_id
    and organization_id = p_org_id;

Line: 27

     select 1
     into l_dummy
     from amw_org_hierarchy_denorm
     where process_id = p_parent_process_id
     and   parent_child_id = p_child_process_id
     and   up_down_ind = 'U'
     and   hierarchy_type = 'L'
     and  organization_id = p_org_id;

Line: 64

    select 1
    into l_dummy
    from AMW_LATEST_HIERARCHY_ORG_V
    where child_process_id =  p_process_id
    and child_organization_id = p_org_id;

Line: 81

    select 1
    into l_dummy
    from AMW_CURR_APP_HIERARCHY_org_v
    where child_process_id =  p_process_id
    and child_organization_id = p_org_id;

Line: 104

        select 1
        into l_dummy
        from AMW_CURR_APPROVED_REV_ORG_v
        where process_id =  p_process_id
        and organization_id = p_org_id
        and deletion_date is not null;

Line: 120

                select 1
                into l_dummy
                from amw_process_organization
                where process_id =  p_process_id
                and organization_id = p_org_id;

Line: 153

    select REVISION_NUMBER
    into l_rev_num
    from amw_process_organization
    where process_id = p_process_id
    and organization_id = p_org_id
    and end_date is null
    and approval_status = 'A';

Line: 165

		insert into amw_process_organization
			(CONTROL_COUNT,
			 RISK_COUNT,
			 PROCESS_ORGANIZATION_ID,
			 PROCESS_ID,
			 STANDARD_PROCESS_FLAG,
			 RISK_CATEGORY,
			 CERTIFICATION_STATUS,
			 LAST_AUDIT_STATUS,
			 ORGANIZATION_ID,
			 LAST_CERTIFICATION_DATE,
			 LAST_AUDIT_DATE,
			 NEXT_AUDIT_DATE,
			 APPLICATION_OWNER_ID,
			 PROCESS_OWNER_ID,
			 PROCESS_CATEGORY_CODE,
			 SIGNIFICANT_PROCESS_FLAG,
			 CREATED_FROM,
			 REQUEST_ID,
			 PROGRAM_APPLICATION_ID,
			  PROGRAM_ID,
			  PROGRAM_UPDATE_DATE,
			  ATTRIBUTE_CATEGORY,
			  ATTRIBUTE1,
			  ATTRIBUTE2,
			  ATTRIBUTE3,
			  ATTRIBUTE4,
			  ATTRIBUTE5,
			  ATTRIBUTE6,
			  ATTRIBUTE7,
			  ATTRIBUTE8,
			  ATTRIBUTE9,
			  ATTRIBUTE10,
			  ATTRIBUTE11,
			  ATTRIBUTE12,
			  ATTRIBUTE13,
			  ATTRIBUTE14,
			  ATTRIBUTE15,
			  SECURITY_GROUP_ID,
			  FINANCE_OWNER_ID,
			  PROCESS_CODE,
			  PROCESS_TYPE,
			  CONTROL_ACTIVITY_TYPE,
			  RL_PROCESS_REV_ID,
			  STANDARD_VARIATION,
			  PROCESS_CATEGORY,
			  LAST_UPDATE_DATE,
			  LAST_UPDATED_BY,
			  CREATION_DATE,
			  CREATED_BY,
			  LAST_UPDATE_LOGIN,
			  REVISION_NUMBER,
			  OBJECT_VERSION_NUMBER,
			  APPROVAL_STATUS,
			  END_DATE,
			  PROCESS_ORG_REV_ID,
			  START_DATE,
			  APPROVAL_DATE,
			  APPROVAL_END_DATE,
			  DELETION_DATE )

			  (select
			  CONTROL_COUNT,
			  RISK_COUNT,
			  AMW_PROCESS_ORGANIZATION_S.nextval,  --kosriniv this is primary key till AMW.C PROCESS_ORGANIZATION_ID,
			  PROCESS_ID,
			  STANDARD_PROCESS_FLAG,
			  RISK_CATEGORY,
			  CERTIFICATION_STATUS,
			  LAST_AUDIT_STATUS,
			  ORGANIZATION_ID,
			  LAST_CERTIFICATION_DATE,
			  LAST_AUDIT_DATE,
			  NEXT_AUDIT_DATE,
			  APPLICATION_OWNER_ID,
			  PROCESS_OWNER_ID,
			  PROCESS_CATEGORY_CODE,
			  SIGNIFICANT_PROCESS_FLAG,
			  CREATED_FROM,
			  REQUEST_ID,
			  PROGRAM_APPLICATION_ID,
			  PROGRAM_ID,
			  PROGRAM_UPDATE_DATE,
			  ATTRIBUTE_CATEGORY,
			  ATTRIBUTE1,
			  ATTRIBUTE2,
			  ATTRIBUTE3,
			  ATTRIBUTE4,
			  ATTRIBUTE5,
			  ATTRIBUTE6,
			  ATTRIBUTE7,
			  ATTRIBUTE8,
			  ATTRIBUTE9,
			  ATTRIBUTE10,
			  ATTRIBUTE11,
			  ATTRIBUTE12,
			  ATTRIBUTE13,
			  ATTRIBUTE14,
			  ATTRIBUTE15,
			  SECURITY_GROUP_ID,
			  FINANCE_OWNER_ID,
			  PROCESS_CODE,
			  PROCESS_TYPE,
			  CONTROL_ACTIVITY_TYPE,
			  RL_PROCESS_REV_ID,
			  STANDARD_VARIATION,
			  PROCESS_CATEGORY,
			  sysdate,
			  G_USER_ID,
			  sysdate,
			  G_USER_ID,
			  G_LOGIN_ID,
			  REVISION_NUMBER + 1,
			  1,
			  'D',
			  null,
			  AMW_PROCESS_ORG_REV_S.nextval,
			  sysdate,
			  null,
			  null,
			  DELETION_DATE
			  from amw_process_organization
			  where process_id = p_process_id
			  and organization_id = p_org_id
			  and end_date is null
			  and approval_status = 'A');

Line: 293

         update amw_process_organization
         set    end_date = sysdate,
                object_version_number = object_version_number + 1
         where  process_id = p_process_id
         and    revision_number = l_rev_num
         and    organization_id = p_org_id;

Line: 332

            select REVISION_NUMBER
            into l_rev_num
            from amw_process_organization
            where process_id = p_process_id
            and organization_id = Org_Ids(indx)
            and end_date is null
            and approval_status = 'A';

Line: 356

        insert into amw_process_organization
			(CONTROL_COUNT,
			 RISK_COUNT,
			 PROCESS_ORGANIZATION_ID,
			 PROCESS_ID,
			 STANDARD_PROCESS_FLAG,
			 RISK_CATEGORY,
			 CERTIFICATION_STATUS,
			 LAST_AUDIT_STATUS,
			 ORGANIZATION_ID,
			 LAST_CERTIFICATION_DATE,
			 LAST_AUDIT_DATE,
			 NEXT_AUDIT_DATE,
			 APPLICATION_OWNER_ID,
			 PROCESS_OWNER_ID,
			 PROCESS_CATEGORY_CODE,
			 SIGNIFICANT_PROCESS_FLAG,
			 CREATED_FROM,
			 REQUEST_ID,
			 PROGRAM_APPLICATION_ID,
			  PROGRAM_ID,
			  PROGRAM_UPDATE_DATE,
			  ATTRIBUTE_CATEGORY,
			  ATTRIBUTE1,
			  ATTRIBUTE2,
			  ATTRIBUTE3,
			  ATTRIBUTE4,
			  ATTRIBUTE5,
			  ATTRIBUTE6,
			  ATTRIBUTE7,
			  ATTRIBUTE8,
			  ATTRIBUTE9,
			  ATTRIBUTE10,
			  ATTRIBUTE11,
			  ATTRIBUTE12,
			  ATTRIBUTE13,
			  ATTRIBUTE14,
			  ATTRIBUTE15,
			  SECURITY_GROUP_ID,
			  FINANCE_OWNER_ID,
			  PROCESS_CODE,
			  PROCESS_TYPE,
			  CONTROL_ACTIVITY_TYPE,
			  RL_PROCESS_REV_ID,
			  STANDARD_VARIATION,
			  PROCESS_CATEGORY,
			  LAST_UPDATE_DATE,
			  LAST_UPDATED_BY,
			  CREATION_DATE,
			  CREATED_BY,
			  LAST_UPDATE_LOGIN,
			  REVISION_NUMBER,
			  OBJECT_VERSION_NUMBER,
			  APPROVAL_STATUS,
			  END_DATE,
			  PROCESS_ORG_REV_ID,
			  START_DATE,
			  APPROVAL_DATE,
			  APPROVAL_END_DATE,
			  DELETION_DATE )

			  (select
			  CONTROL_COUNT,
			  RISK_COUNT,
			  AMW_PROCESS_ORGANIZATION_S.nextval,  --kosriniv this is primary key till AMW.C PROCESS_ORGANIZATION_ID,
			  PROCESS_ID,
			  STANDARD_PROCESS_FLAG,
			  RISK_CATEGORY,
			  CERTIFICATION_STATUS,
			  LAST_AUDIT_STATUS,
			  ORGANIZATION_ID,
			  LAST_CERTIFICATION_DATE,
			  LAST_AUDIT_DATE,
			  NEXT_AUDIT_DATE,
			  APPLICATION_OWNER_ID,
			  PROCESS_OWNER_ID,
			  PROCESS_CATEGORY_CODE,
			  SIGNIFICANT_PROCESS_FLAG,
			  CREATED_FROM,
			  REQUEST_ID,
			  PROGRAM_APPLICATION_ID,
			  PROGRAM_ID,
			  PROGRAM_UPDATE_DATE,
			  ATTRIBUTE_CATEGORY,
			  ATTRIBUTE1,
			  ATTRIBUTE2,
			  ATTRIBUTE3,
			  ATTRIBUTE4,
			  ATTRIBUTE5,
			  ATTRIBUTE6,
			  ATTRIBUTE7,
			  ATTRIBUTE8,
			  ATTRIBUTE9,
			  ATTRIBUTE10,
			  ATTRIBUTE11,
			  ATTRIBUTE12,
			  ATTRIBUTE13,
			  ATTRIBUTE14,
			  ATTRIBUTE15,
			  SECURITY_GROUP_ID,
			  FINANCE_OWNER_ID,
			  PROCESS_CODE,
			  PROCESS_TYPE,
			  CONTROL_ACTIVITY_TYPE,
			  RL_PROCESS_REV_ID,
			  STANDARD_VARIATION,
			  PROCESS_CATEGORY,
			  sysdate,
			  G_USER_ID,
			  sysdate,
			  G_USER_ID,
			  G_LOGIN_ID,
			  REVISION_NUMBER + 1,
			  1,
			  'D',
			  null,
			  AMW_PROCESS_ORG_REV_S.nextval,
			  sysdate,
			  null,
			  null,
			  DELETION_DATE
			  from amw_process_organization
			  where process_id    = p_process_id
			  and organization_id = l_Org_Ids(indx)
			  and end_date is null
			  and approval_status = 'A');

Line: 485

         update amw_process_organization
         set    end_date = sysdate,
                object_version_number = object_version_number + 1
         where  process_id = p_process_id
         and    revision_number = l_rev_number(indx)
         and    organization_id = l_Org_Ids(indx);

Line: 505

procedure add_delete_ex_child (
	p_org_id in number,
	p_parent_process_id in number,
	p_child_process_id in number,
    p_action in varchar2)
is
  l_dummy number;

Line: 518

    	FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.AMW_ORG_HIERARCHY_PKG.ADD_DELETE_EX_CHILD.Begin',
        'p_org_id:'||p_org_id ||';p_parent_process_id:'||p_parent_process_id ||';p_child_process_id:'||p_child_process_id ||';p_action:'||'ADD');

Line: 520

        FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.AMW_ORG_HIERARCHY_PKG.ADD_DELETE_EX_CHILD.produce_err_if_circular',
        p_org_id ||';'||p_parent_process_id ||';'||p_child_process_id);

Line: 534

    	FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.AMW_ORG_HIERARCHY_PKG.ADD_DELETE_EX_CHILD.revise_process_if_necessary',
        p_org_id ||';'||p_parent_process_id );

Line: 541

               insert into amw_latest_hierarchies
                (ORGANIZATION_ID, PARENT_ID, CHILD_ID, CHILD_ORDER_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY, object_version_number)
               VALUES
                (p_org_id,p_parent_process_id,p_child_process_id,AMW_ORG_CHILD_ORDER_S.nextval, sysdate, G_USER_ID, G_LOGIN_ID, sysdate, G_USER_ID, 1)
                returning                CHILD_ORDER_NUMBER
                 into                     l_child_order_num;

Line: 547

                AMW_RL_HIERARCHY_PKG.update_appr_ch_ord_num_if_reqd
                (p_org_id      =>  p_org_id,
                 p_parent_id   =>  p_parent_process_id,
                 p_child_id    =>  p_child_process_id,
                 p_instance_id =>  l_child_order_num);

Line: 553

               delete from amw_latest_hierarchies
               where parent_id = p_parent_process_id
               and   child_id  = p_child_process_id
               and organization_id = p_org_id;

Line: 565

    				FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.AMW_ORG_HIERARCHY_PKG.ADD_DELETE_EX_CHILD.write_approved_hierarchy',
        			'-2;0;'||p_org_id ||':'||sysdate);

Line: 571

    				FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.AMW_ORG_HIERARCHY_PKG.ADD_DELETE_EX_CHILD.write_approved_hierarchy',
        			'-2;2;'||p_org_id ||':'||sysdate);

Line: 577

    				FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.AMW_ORG_HIERARCHY_PKG.ADD_DELETE_EX_CHILD.write_approved_hierarchy',
        			'-2;3;'||p_org_id ||':'||sysdate);

Line: 583

    				FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.AMW_ORG_HIERARCHY_PKG.ADD_DELETE_EX_CHILD.write_approved_hierarchy',
        			'WRITE_APPROVED_HIERARCHY_END');

Line: 589

    	FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.AMW_ORG_HIERARCHY_PKG.ADD_DELETE_EX_CHILD.End',
        'End');

Line: 593

end add_delete_ex_child;

Line: 600

procedure add_delete_ex_child (
	p_org_id in number,
	p_parent_process_id in number,
	p_child_process_id in number,
    p_action in varchar2,
    x_return_status out nocopy varchar2,
    x_msg_count out nocopy number,
    x_msg_data out nocopy varchar2)
is
  l_api_name CONSTANT varchar2(30) := 'add_delete_ex_child';

Line: 632

               insert into amw_latest_hierarchies
                (ORGANIZATION_ID, PARENT_ID, CHILD_ID, CHILD_ORDER_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY, object_version_number)
               VALUES
                (p_org_id,p_parent_process_id,p_child_process_id,AMW_ORG_CHILD_ORDER_S.nextval, sysdate, G_USER_ID, G_LOGIN_ID, sysdate, G_USER_ID, 1);

Line: 637

               delete from amw_latest_hierarchies
               where parent_id = p_parent_process_id
               and   child_id  = p_child_process_id
               and organization_id = p_org_id;

Line: 672

end add_delete_ex_child;

Line: 678

procedure delete_process (
	p_org_id in number,
	p_process_id in number)
is
appexst varchar2(1);

Line: 693

	      update amw_process_organization
    	  set deletion_date = sysdate
	      where process_id = p_process_id
    	  and organization_id = p_org_id
      	  and end_date is null;

Line: 700

      	delete_draft(   p_organization_id  => p_org_id,
                        p_process_id       => p_process_id,
                        x_return_status    => l_return_status,
                        x_msg_count        => l_msg_count,
                        x_msg_data         => l_msg_data);

Line: 708

end delete_process;

Line: 751

        	'amw.plsql.AMW_ORG_HIERARCHY_PKG.IMPORT_RLPROC_AS_CHILD_OF_EX.insert Row','Insert Row' );

Line: 755

		insert into amw_process_organization
			( PROCESS_ORG_REV_ID,
   			  PROCESS_ORGANIZATION_ID,
			  REVISION_NUMBER,
			  PROCESS_ID,
       		  ORGANIZATION_ID,
			  PROCESS_CODE,
			  RL_PROCESS_REV_ID,
			  SIGNIFICANT_PROCESS_FLAG,
			  STANDARD_PROCESS_FLAG,
			  PROCESS_CATEGORY_CODE,
			  STANDARD_VARIATION,
			  ATTRIBUTE_CATEGORY,
			  ATTRIBUTE1,
			  ATTRIBUTE2,
			  ATTRIBUTE3,
			  ATTRIBUTE4,
			  ATTRIBUTE5,
			  ATTRIBUTE6,
			  ATTRIBUTE7,
			  ATTRIBUTE8,
			  ATTRIBUTE9,
			  ATTRIBUTE10,
			  ATTRIBUTE11,
			  ATTRIBUTE12,
			  ATTRIBUTE13,
			  ATTRIBUTE14,
			  ATTRIBUTE15,
			  SECURITY_GROUP_ID,
			  PROCESS_TYPE,
			  CONTROL_ACTIVITY_TYPE,
			  LAST_UPDATE_DATE,
			  LAST_UPDATED_BY,
			  CREATION_DATE,
			  CREATED_BY,
			  LAST_UPDATE_LOGIN,
			  OBJECT_VERSION_NUMBER,
			  APPROVAL_STATUS,
  			  START_DATE,
              risk_category)

			  (select
			  AMW_PROCESS_ORG_REV_S.nextval,
			  AMW_PROCESS_ORGANIZATION_S.nextval,
			  1,
			  PROCESS_ID,
			  p_org_id,
			  PROCESS_CODE,
			  PROCESS_REV_ID,
			  SIGNIFICANT_PROCESS_FLAG,
			  STANDARD_PROCESS_FLAG,
			  PROCESS_CATEGORY,
			  STANDARD_VARIATION,
			  ATTRIBUTE_CATEGORY,
			  ATTRIBUTE1,
			  ATTRIBUTE2,
			  ATTRIBUTE3,
			  ATTRIBUTE4,
			  ATTRIBUTE5,
			  ATTRIBUTE6,
			  ATTRIBUTE7,
			  ATTRIBUTE8,
			  ATTRIBUTE9,
			  ATTRIBUTE10,
			  ATTRIBUTE11,
			  ATTRIBUTE12,
			  ATTRIBUTE13,
			  ATTRIBUTE14,
			  ATTRIBUTE15,
			  SECURITY_GROUP_ID,
			  PROCESS_TYPE,
			  CONTROL_ACTIVITY_TYPE,
			  sysdate,
			  G_USER_ID,
			  sysdate,
			  G_USER_ID,
			  G_LOGIN_ID,
			  1,
			  'D',
			  sysdate,
              'High'
			  from amw_process
			  where process_id = p_child_process_id
			  and approval_date is not null
			  and approval_end_date is null);

Line: 843

        	'amw.plsql.AMW_ORG_HIERARCHY_PKG.IMPORT_RLPROC_AS_CHILD_OF_EX.insert_latest_hierarchy','insert_latest_hierarchy' );

Line: 846

      insert into amw_latest_hierarchies
        (ORGANIZATION_ID, PARENT_ID, CHILD_ID, CHILD_ORDER_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY, object_version_number)
      VALUES
        (p_org_id,p_parent_process_id,p_child_process_id,AMW_ORG_CHILD_ORDER_S.nextval, sysdate, G_USER_ID, G_LOGIN_ID, sysdate, G_USER_ID, 1);

Line: 886

    select process_org_rev_id into v_proc_org_rev_id
    from amw_process_organization
    where process_id = p_process_id
        and organization_id = p_org_id
        and end_date is null;

Line: 892

    select distinct pk1_value into v_proc_rev_id
    From FND_ATTACHED_DOCUMENTS
    where entity_name ='AMW_PROCESS'
        and pk1_value in ( select PROCESS_REV_ID
                            from amw_process_vl
                            where  process_id = p_process_id
                            and end_date is null);

Line: 901

        FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(X_entity_name => 'AMW_PROCESS_ORGANIZATION'
         ,X_pk1_value => v_proc_org_rev_id);

Line: 905

    END IF; -- if update then delete the current attachments before adding new

Line: 912

     ,X_last_update_login => G_LOGIN_ID
     );

Line: 924

   SELECT      TO_NUMBER(REPLACE(grants.grantee_key,'HZ_PARTY:','')) party_id,
        	   granted_menu.menu_name role_name,
        	   obj.obj_name object_name,
     		   granted_menu.menu_id menu_id,
		   grants.end_date end_date
         FROM fnd_grants grants,
             fnd_menus granted_menu,
             fnd_objects obj
         WHERE obj.obj_name = 'AMW_PROCESS_APPR_ETTY'
         AND   grants.object_id = obj.object_id
         AND   grants.grantee_type ='USER'
         AND   grantee_key like 'HZ_PARTY%'
         AND   NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
         AND   grants.menu_id = granted_menu.menu_id
         AND   grants.instance_type = 'INSTANCE'
         AND   grants.instance_pk1_value = to_char(pid)
         AND   grants.instance_pk2_value = '*NULL*'
         AND   grants.instance_pk3_value = '*NULL*'
         AND   grants.instance_pk4_value = '*NULL*'
         AND   grants.instance_pk5_value = '*NULL*'
         and   granted_menu.menu_name in ('AMW_RL_PROC_OWNER_ROLE', 'AMW_RL_PROC_FINANCE_OWNER_ROLE', 'AMW_RL_PROC_APPL_OWNER_ROLE');

Line: 1025

    insert into amw_acct_associations
    (ACCT_ASSOC_ID,
    NATURAL_ACCOUNT_ID,
    PK1,
    PK2,
    STATEMENT_ID,
    STATEMENT_LINE_ID,
    ORIG_SYSTEM_ACCT_VALUE,
    ASSOCIATION_CREATION_DATE,
    APPROVAL_DATE,
    DELETION_DATE,
    DELETION_APPROVAL_DATE,
    OBJECT_TYPE,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    OBJECT_VERSION_NUMBER)
    (select
    AMW_ACCT_ASSOCIATIONS_S.nextval,
    NATURAL_ACCOUNT_ID,
    p_org_id,
    PK1,
    STATEMENT_ID,
    STATEMENT_LINE_ID,
    ORIG_SYSTEM_ACCT_VALUE,
    sysdate,
    null,
    null,
    null,
    'PROCESS_ORG',
    sysdate,
    G_USER_ID,
    sysdate,
    G_USER_ID,
    G_LOGIN_ID,
    1
    from amw_acct_associations
    where PK1 = p_child_process_id
    and object_type = 'PROCESS'
    and approval_date is not null
    and deletion_approval_date is null);

Line: 1068

insert into amw_objective_associations
    (OBJECTIVE_ASSOCIATION_ID,
    PROCESS_OBJECTIVE_ID,
    PK1,
    PK2,
    ASSOCIATION_CREATION_DATE,
    APPROVAL_DATE,
    DELETION_DATE,
    DELETION_APPROVAL_DATE,
    OBJECT_TYPE,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    OBJECT_VERSION_NUMBER)
    (select
    AMW_OBJECTIVE_ASSOCIATIONS_S.nextval,
    PROCESS_OBJECTIVE_ID,
    p_org_id,
    PK1,
    sysdate,
    null,
    null,
    null,
    'PROCESS_ORG',
    sysdate,
    G_USER_ID,
    sysdate,
    G_USER_ID,
    G_LOGIN_ID,
    1
    from amw_objective_associations
    where PK1 = p_child_process_id
    and object_type = 'PROCESS'
    and approval_date is not null
    and deletion_approval_date is null);

Line: 1119

    insert into amw_risk_associations
    (RISK_ASSOCIATION_ID,
    RISK_ID,
    PK1,
    PK2,
    RISK_LIKELIHOOD_CODE,
    RISK_IMPACT_CODE,
    MATERIAL,
    MATERIAL_VALUE,
    ASSOCIATION_CREATION_DATE,
    APPROVAL_DATE,
    DELETION_DATE,
    DELETION_APPROVAL_DATE,
    OBJECT_TYPE,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    OBJECT_VERSION_NUMBER)
    (select
    AMW_RISK_ASSOCIATIONS_S.nextval,
    RISK_ID,
    p_org_id,
    PK1,
    RISK_LIKELIHOOD_CODE,
    RISK_IMPACT_CODE,
    MATERIAL,
    MATERIAL_VALUE,
    sysdate,
    null,
    null,
    null,
    'PROCESS_ORG',
    sysdate,
    G_USER_ID,
    sysdate,
    G_USER_ID,
    G_LOGIN_ID,
    1
    from amw_risk_associations
    where PK1 = p_child_process_id
    and object_type = 'PROCESS'
    and approval_date is not null
    and deletion_approval_date is null);

Line: 1168

    insert into amw_control_associations
    (CONTROL_ASSOCIATION_ID,
    CONTROL_ID,
    PK1,
    PK2,
    PK3,
    ASSOCIATION_CREATION_DATE,
    APPROVAL_DATE,
    DELETION_DATE,
    DELETION_APPROVAL_DATE,
    OBJECT_TYPE,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    OBJECT_VERSION_NUMBER)
    (select
    AMW_CONTROL_ASSOCIATIONS_S.nextval,
    CONTROL_ID,
    p_org_id,
    PK1,
    PK2,
    sysdate,
    null,
    null,
    null,
    'RISK_ORG',
    sysdate,
    G_USER_ID,
    sysdate,
    G_USER_ID,
    G_LOGIN_ID,
    1
    from amw_control_associations
    where PK1 = p_child_process_id
    and object_type = 'RISK'
    and approval_date is not null
    and deletion_approval_date is null);

Line: 1212

    insert into amw_objective_associations
    (OBJECTIVE_ASSOCIATION_ID,
    PROCESS_OBJECTIVE_ID,
    PK1,
    PK2,
    PK3,
    PK4,
    ASSOCIATION_CREATION_DATE,
    APPROVAL_DATE,
    DELETION_DATE,
    DELETION_APPROVAL_DATE,
    OBJECT_TYPE,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    OBJECT_VERSION_NUMBER)
    (select
    AMW_OBJECTIVE_ASSOCIATIONS_S.nextval,
    o.PROCESS_OBJECTIVE_ID,
    p_org_id,
    o.PK1,
    o.PK2,
    o.pk3,
    sysdate,
    null,
    null,
    null,
    'CONTROL_ORG',
    sysdate,
    G_USER_ID,
    sysdate,
    G_USER_ID,
    G_LOGIN_ID,
    1
    from amw_objective_associations o, amw_control_associations c
    where o.object_type = 'CONTROL'
    and o.approval_date is not null
    and o.deletion_approval_date is null
    and c.object_type = 'RISK_ORG'
    and c.approval_date is null
    and c.deletion_date is null
    and c.pk1 = p_org_id
    and c.pk2 = p_child_process_id
    and c.pk2 = o.pk1
    and c.pk3 = o.pk2
    and o.pk3 = c.control_id);

Line: 1261

    insert into amw_ap_associations
    (AP_ASSOCIATION_ID,
    AUDIT_PROCEDURE_ID,
    PK1,
    PK2,
    PK3,
    DESIGN_EFFECTIVENESS,
    OP_EFFECTIVENESS,
    ASSOCIATION_CREATION_DATE,
    APPROVAL_DATE,
    DELETION_DATE,
    DELETION_APPROVAL_DATE,
    OBJECT_TYPE,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    OBJECT_VERSION_NUMBER)
    (select
    AMW_AP_ASSOCIATIONS_S.nextval,
    AUDIT_PROCEDURE_ID,
    p_org_id,
    p_child_process_id,
    PK1,
--ko, the values are pk1 = org, pk2 = process, pk3 = control in the org context.    PK2,
    DESIGN_EFFECTIVENESS,
    OP_EFFECTIVENESS,
    null, --ko commenting.. we set association creation date upon approval of the process..sysdate,
    null,
    null,
    null,
    'CTRL_ORG',
    sysdate,
    G_USER_ID,
    sysdate,
    G_USER_ID,
    G_LOGIN_ID,
    1
    from amw_ap_associations
    where PK1 in --ko, replacing  = with in  controls can be more than one..
        (select distinct control_id
        from amw_control_associations
        where PK1 = p_child_process_id
        and object_type = 'RISK'
        and (APPROVAL_DATE is not null and APPROVAL_DATE <=  sysdate)
        and (DELETION_DATE is null or (DELETION_DATE is not null and DELETION_APPROVAL_DATE is null)))
    and object_type = 'CTRL'
    and approval_date is not null
    and deletion_approval_date is null);

Line: 1357

       select PROCESS_REV_ID,
			  SIGNIFICANT_PROCESS_FLAG,
			  STANDARD_PROCESS_FLAG,
			  PROCESS_CATEGORY,
			  STANDARD_VARIATION,
			  ATTRIBUTE_CATEGORY,
			  ATTRIBUTE1,
			  ATTRIBUTE2,
			  ATTRIBUTE3,
			  ATTRIBUTE4,
			  ATTRIBUTE5,
			  ATTRIBUTE6,
			  ATTRIBUTE7,
			  ATTRIBUTE8,
			  ATTRIBUTE9,
			  ATTRIBUTE10,
			  ATTRIBUTE11,
			  ATTRIBUTE12,
			  ATTRIBUTE13,
			  ATTRIBUTE14,
			  ATTRIBUTE15,
			  SECURITY_GROUP_ID,
			  PROCESS_TYPE,
			  CONTROL_ACTIVITY_TYPE
              into
              l_RL_PROCESS_REV_ID,
			  l_SIGNIFICANT_PROCESS_FLAG,
			  l_STANDARD_PROCESS_FLAG,
			  l_PROCESS_CATEGORY,
			  l_STANDARD_VARIATION,
			  l_ATTRIBUTE_CATEGORY,
			  l_ATTRIBUTE1,
			  l_ATTRIBUTE2,
			  l_ATTRIBUTE3,
			  l_ATTRIBUTE4,
			  l_ATTRIBUTE5,
			  l_ATTRIBUTE6,
			  l_ATTRIBUTE7,
			  l_ATTRIBUTE8,
			  l_ATTRIBUTE9,
			  l_ATTRIBUTE10,
			  l_ATTRIBUTE11,
			  l_ATTRIBUTE12,
			  l_ATTRIBUTE13,
			  l_ATTRIBUTE14,
			  l_ATTRIBUTE15,
			  l_SECURITY_GROUP_ID,
			  l_PROCESS_TYPE,
			  l_CONTROL_ACTIVITY_TYPE
              from amw_process
              where process_id = p_process_id
              and end_date is null;

Line: 1411

      update amw_process_organization
      set     RL_PROCESS_REV_ID             =   l_RL_PROCESS_REV_ID,
			  SIGNIFICANT_PROCESS_FLAG      =   l_SIGNIFICANT_PROCESS_FLAG,
			  STANDARD_PROCESS_FLAG	        =   l_STANDARD_PROCESS_FLAG,
			  PROCESS_CATEGORY_CODE	            =   l_PROCESS_CATEGORY,
			  STANDARD_VARIATION	        =   l_STANDARD_VARIATION,
			  ATTRIBUTE_CATEGORY            =   l_ATTRIBUTE_CATEGORY,
			  ATTRIBUTE1		    =   l_ATTRIBUTE1,
			  ATTRIBUTE2			=   l_ATTRIBUTE2,
			  ATTRIBUTE3			=   l_ATTRIBUTE3,
			  ATTRIBUTE4			=   l_ATTRIBUTE4,
			  ATTRIBUTE5			=   l_ATTRIBUTE5,
			  ATTRIBUTE6			=   l_ATTRIBUTE6,
			  ATTRIBUTE7			=   l_ATTRIBUTE7,
			  ATTRIBUTE8			=   l_ATTRIBUTE8,
			  ATTRIBUTE9			=   l_ATTRIBUTE9,
			  ATTRIBUTE10			=   l_ATTRIBUTE10,
			  ATTRIBUTE11			=   l_ATTRIBUTE11,
			  ATTRIBUTE12			=   l_ATTRIBUTE12,
			  ATTRIBUTE13			=   l_ATTRIBUTE13,
			  ATTRIBUTE14			=   l_ATTRIBUTE14,
			  ATTRIBUTE15			=   l_ATTRIBUTE15,
			  SECURITY_GROUP_ID		=   l_SECURITY_GROUP_ID,
			  PROCESS_TYPE			=   l_PROCESS_TYPE,
			  CONTROL_ACTIVITY_TYPE	=   l_CONTROL_ACTIVITY_TYPE
      where organization_id = p_org_id
      and process_id = p_process_id
      and end_date is null;

Line: 1443

        update amw_acct_associations
        set DELETION_DATE = sysdate
        where pk1 = p_org_id
        and   pk2 = p_process_id
        and   object_type = 'PROCESS_ORG'
        and   deletion_date is null;

Line: 1450

      delete amw_acct_associations
      where pk1 = p_org_id
        and   pk2 = p_process_id
        and   object_type = 'PROCESS_ORG'
        and   approval_date is null;

Line: 1468

        delete_existing_rcm(p_process_id, p_org_id);

Line: 1479

procedure delete_existing_rcm(
p_process_id in number,
p_org_id     in number) is

begin

-- ko, We Don't want the draft associations to linger in the table..So delete them..
        delete amw_risk_associations
        where pk1 = p_org_id
        and   pk2 = p_process_id
        and   object_type = 'PROCESS_ORG'
        and   approval_date is null;

Line: 1492

        update amw_risk_associations
        set DELETION_DATE = sysdate
        where pk1 = p_org_id
        and   pk2 = p_process_id
        and   object_type = 'PROCESS_ORG'
        and   deletion_date is null;

Line: 1501

        delete amw_control_associations
        where pk1 = p_org_id
        and   pk2 = p_process_id
        and   object_type = 'RISK_ORG'
        and   approval_date is null;

Line: 1507

        update amw_control_associations
        set DELETION_DATE = sysdate
        where pk1 = p_org_id
        and   pk2 = p_process_id
        and   object_type = 'RISK_ORG'
        and   deletion_date is null;

Line: 1515

        delete amw_objective_associations
        where pk1 = p_org_id
        and   pk2 = p_process_id
        and   object_type = 'CONTROL_ORG'
        and   approval_date is null;

Line: 1521

        update amw_objective_associations
        set DELETION_DATE = sysdate
        where pk1 = p_org_id
        and   pk2 = p_process_id
        and   object_type = 'CONTROL_ORG'
        and   deletion_date is null;

Line: 1531

      delete from amw_ap_associations
      where pk1 = p_org_id
      and   pk2 = p_process_id
      and association_creation_date is null
      and   object_type = 'CTRL_ORG';

Line: 1537

      update amw_ap_associations
      set DELETION_DATE = sysdate
      where pk1 = p_org_id
      and   pk2 = p_process_id
      and   object_type = 'CTRL_ORG'
      and   deletion_date is null;

Line: 1548

end delete_existing_rcm;

Line: 1626

	elsif pex = 'D' then --The process is existing in the system some time back and got deleted. so undelete it and sync up..
			if( l_log_stmt_level >= l_curr_log_level ) then
    			FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.AMW_ORG_HIERARCHY_PKG.ASSOCIATE_PROCESS_TO_ORG.undelete',
        			p_associated_proc_id ||';'||p_org_id );

Line: 1631

	        undelete(p_associated_proc_id, p_org_id);

Line: 1633

    			FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.AMW_ORG_HIERARCHY_PKG.ASSOCIATE_PROCESS_TO_ORG.add_delete_ex_child',
        			p_org_id ||';'||p_parent_process_id ||';'||p_associated_proc_id ||';'||'ADD');

Line: 1636

			add_delete_ex_child (p_org_id, p_parent_process_id, p_associated_proc_id, 'ADD');

Line: 1674

    select ah.child_id child_process_id
      from amw_approved_hierarchies ah
      where ah.parent_id = (select pp.process_id
                            from amw_process pp
                            where pp.process_id = ah.parent_id
                            and pp.approval_date is not null
                            and pp.approval_end_date is null
                            and pp.deletion_date is null)
       and ah.child_id  =  ( select Cp.process_id
                            from amw_process Cp
                            where Cp.process_id = ah.child_id
                            and Cp.approval_date is not null
                            and Cp.approval_end_date is null
                            and Cp.deletion_date is null)
       and ah.start_date is not null
       and ah.end_date is null
       and ah.organization_id = -1
       and ah.parent_id = l_pid;

Line: 1735

    			FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'amw.plsql.AMW_ORG_HIERARCHY_PKG.ASSOCIATE_HIERARCHY.add_delete_ex_child',
        			p_org_id ||';'||p_parent_process_id ||';'||l_child_process_id ||';'||'ADD');

Line: 1738

            add_delete_ex_child (p_org_id, p_parent_process_id, l_child_process_id, 'ADD');

Line: 1792

                	select 'Y' into pex
                	from amw_process_organization
                	where organization_id = p_org_id
                	and process_id = l_child_process_id
                	and end_date is null
                	and approval_date is not null
                	and deletion_date is not null;

Line: 1848

    select ah.child_id child_process_id
      from amw_approved_hierarchies ah
      where ah.parent_id = (select pp.process_id
                            from amw_process pp
                            where pp.process_id = ah.parent_id
                            and pp.approval_date is not null
                            and pp.approval_end_date is null
                            and pp.deletion_date is null)
       and ah.child_id  =  ( select Cp.process_id
                            from amw_process Cp
                            where Cp.process_id = ah.child_id
                            and Cp.approval_date is not null
                            and Cp.approval_end_date is null
                            and Cp.deletion_date is null)
       and ah.start_date is not null
       and ah.end_date is null
       and ah.organization_id = -1
       and ah.parent_id = l_pid;

Line: 1884

  delete from amw_latest_hierarchies
  where parent_id = p_parent_process_id
  and organization_id = p_org_id;

Line: 1923

        		'amw.plsql.AMW_ORG_HIERARCHY_PKG.SYNCHRONIZE_HIERARCHY.add_delete_ex_child',
        		p_org_id||';'||p_parent_process_id||';'||l_child_process_id||';'||'ADD');

Line: 1926

            add_delete_ex_child (p_org_id, p_parent_process_id, l_child_process_id, 'ADD');

Line: 1947

                	select 'Y' into pex
                	from amw_process_organization
                	where organization_id = p_org_id
                	and process_id = l_child_process_id
                	and end_date is null
                	and approval_date is not null
                	and deletion_date is not null;

Line: 1956

        				'amw.plsql.AMW_ORG_HIERARCHY_PKG.SYNCHRONIZE_HIERARCHY.undelete',
        				l_child_process_id||';'||p_org_id);

Line: 1959

                	undelete(l_child_process_id, p_org_id);

Line: 1962

        				'amw.plsql.AMW_ORG_HIERARCHY_PKG.SYNCHRONIZE_HIERARCHY.add_delete_ex_child',
        				p_org_id||';'||p_parent_process_id||';'||l_child_process_id||';'||'ADD');

Line: 1965

                  	add_delete_ex_child (p_org_id, p_parent_process_id, l_child_process_id, 'ADD');

Line: 1996

        					'amw.plsql.AMW_ORG_HIERARCHY_PKG.SYNCHRONIZE_HIERARCHY.undelete',
        					l_child_process_id||';'||p_org_id);

Line: 1999

                		undelete(l_child_process_id, p_org_id);

Line: 2002

        					'amw.plsql.AMW_ORG_HIERARCHY_PKG.SYNCHRONIZE_HIERARCHY.add_delete_ex_child',
        					p_org_id||';'||p_parent_process_id||';'||l_child_process_id||';'||'ADD');

Line: 2005

                  		add_delete_ex_child (p_org_id, p_parent_process_id, l_child_process_id, 'ADD');

Line: 2040

    select ah.child_id child_process_id
      from amw_approved_hierarchies ah
      where ah.parent_id = (select pp.process_id
                            from amw_process pp
                            where pp.process_id = ah.parent_id
                            and pp.approval_date is not null
                            and pp.approval_end_date is null
                            and pp.deletion_date is null)
       and ah.child_id  =  ( select Cp.process_id
                            from amw_process Cp
                            where Cp.process_id = ah.child_id
                            and Cp.approval_date is not null
                            and Cp.approval_end_date is null
                            and Cp.deletion_date is null)
       and ah.start_date is not null
       and ah.end_date is null
       and ah.organization_id = -1
       and ah.parent_id = l_pid;

Line: 2069

        delete from amw_latest_hierarchies
        where parent_id = p_parent_process_id
        and organization_id = Org_Ids(indx);

Line: 2091

                add_delete_ex_child (Org_Ids(indx), p_parent_process_id, l_child_process_id, 'ADD');

Line: 2105

                	select 'Y' into pex
                	from amw_process_organization
                	where organization_id = Org_Ids(indx)
                	and process_id = l_child_process_id
                	and end_date is null
                	and approval_date is not null
                	and deletion_date is not null;

Line: 2113

                	undelete(l_child_process_id, Org_Ids(indx));

Line: 2115

                  	add_delete_ex_child (Org_Ids(indx), p_parent_process_id, l_child_process_id, 'ADD');

Line: 2134

                		undelete(l_child_process_id, Org_Ids(indx));

Line: 2135

                  		add_delete_ex_child (Org_Ids(indx), p_parent_process_id, l_child_process_id, 'ADD');

Line: 2154

    select 1
    into l_dummy
    from amw_process_locks
    where locked_process_id = p_process_id
    and organization_id = p_org_id;

Line: 2176

    select 1
    into l_dummy
    from amw_process_organization
    where process_id = p_process_id
    and organization_id = p_org_id
    and end_date is null
    and approval_status = 'PA';

Line: 2193

procedure undelete (
	p_process_id in number,
  	p_org_id in number) is

begin

-- KSR CHANGES BEGIN...

-- Make changes such that the disassociation is also taken care of....


-- KSR CHANGES END.....
    revise_process_if_necessary (p_org_id, p_process_id);

Line: 2207

    update amw_process_organization
         set    deletion_date = null,
                object_version_number = object_version_number + 1
         where  process_id = p_process_id
         and    organization_id = p_org_id
         and    end_date is null;

Line: 2214

end undelete;

Line: 2222

    select deletion_date
    into l_del_date
    from amw_process_organization
    where process_id = p_process_id
    and organization_id = p_org_id
    and end_date is null;

Line: 2249

    select ah.child_id child_process_id
      from amw_approved_hierarchies ah
      where ah.parent_id = (select pp.process_id
                            from amw_process pp
                            where pp.process_id = ah.parent_id
                            and pp.approval_date is not null
                            and pp.approval_end_date is null
                            and pp.deletion_date is null)
       and ah.child_id  =  ( select Cp.process_id
                            from amw_process Cp
                            where Cp.process_id = ah.child_id
                            and Cp.approval_date is not null
                            and Cp.approval_end_date is null
                            and Cp.deletion_date is null)
       and ah.start_date is not null
       and ah.end_date is null
       and ah.organization_id = -1
       and ah.parent_id = l_pid;

Line: 2350

    select child_process_id
	from AMW_LATEST_HIERARCHY_ORG_V
    where parent_process_id=l_pid
    and child_organization_id = l_org;

Line: 2367

        /*delete from amw_latest_hierarchies
        where child_id  = l_child_process_id
        and parent_id = p_process_id
        and organization_id = p_org_id;*/

Line: 2371

        /*commenting the above delete and adding the following update
         so that when a process is dissasociated and approved,the
         children will also be approved using the below link*/
         update amw_latest_hierarchies
          set organization_id = organization_id * (-1),
              object_version_number = object_version_number + 1,
              LAST_UPDATE_DATE = sysdate,
              LAST_UPDATED_BY = g_user_id,
              LAST_UPDATE_LOGIN = g_login_id
          where child_id  = l_child_process_id
          and parent_id = p_process_id
          and organization_id = p_org_id;

Line: 2384

delete_existing_rcm(l_child_process_id,p_org_id);

Line: 2390

              select 1 into l_dummy
              from amw_latest_hierarchies
              where child_id  = l_child_process_id
              and organization_id = p_org_id
              and parent_id <> p_process_id;

Line: 2414

            delete_process(p_org_id, l_child_process_id);

Line: 2428

    select parent_process_id
	from AMW_LATEST_HIERARCHY_ORG_V
    where child_process_id = l_pid
    and child_organization_id = l_org;

Line: 2450

        delete from amw_latest_hierarchies
        where child_id  = p_process_id
        and organization_id = p_org_id;

Line: 2454

delete_existing_rcm(p_process_id,p_org_id);

Line: 2471

		delete_process(p_org_id, p_process_id);

Line: 2480

   (select process_id
    from amw_process_organization
    where organization_id = p_org_id
    and process_id in ( select parent_id
                    		from amw_latest_hierarchies
                    		start with child_id = p_process_id and organization_id = p_org_id
                    		connect by prior parent_id = child_id
                    		and organization_id = p_org_id
                        union all
                        select p_process_id from dual
                      )
    and end_date is null
    );

Line: 2495

   (select process_id
    from amw_process_organization
    where organization_id = p_org_id
    and process_id in ( select parent_child_id
                        from amw_org_hierarchy_denorm
                        where process_id = p_process_id
                        and organization_id = p_org_id
                        and up_down_ind = 'U'
                        and hierarchy_type = 'L'
                       )
    and end_date is null
    union
    select p_process_id from dual);

Line: 2510

    select process_id
    from amw_process_organization
    where end_date is null
   and organization_id = p_org_id;

Line: 2532

update amw_process_organization
        set risk_count_latest = (select count(*) from (
                            select distinct risk_id from amw_risk_associations
                            where pk1 = p_org_id
                            and pk2 in (
                            						select child_id
  																			from amw_latest_hierarchies
  																			start with child_id = x(ctr) and organization_id = p_org_id
  																			connect by prior child_id = parent_id and organization_id = p_org_id
                            						)
/* ko removing the usage of amw_org_hierarchy_denorm
												    ( ( select parent_child_id
                            from amw_org_hierarchy_denorm
                            where process_id = x(ctr)
                            and organization_id = p_org_id
                            and up_down_ind = 'D'
                            and hierarchy_type = 'L' ) union (select x(ctr) from dual) )
*/
                            and deletion_date is null
                            and object_type = 'PROCESS_ORG'
                            ) ),last_update_date = sysdate
              ,last_updated_by = G_USER_ID
              ,last_update_login = G_LOGIN_ID
        where end_date is null
        and process_id <> -2
        and organization_id = p_org_id
        and process_id = x(ctr);

Line: 2574

		select process_id
    from amw_process_organization
    where organization_id = p_org_id
    and process_id in ( select parent_id
                    		from amw_latest_hierarchies
                    		start with child_id = p_process_id and organization_id = p_org_id
                    		connect by prior parent_id = child_id
                    		and organization_id = p_org_id
  		                union all
  		                select p_process_id from dual
                       )
    and end_date is null;

Line: 2588

   (select process_id
    from amw_process_organization
    where organization_id = p_org_id
    and process_id in ( select parent_child_id
                        from amw_org_hierarchy_denorm
                        where process_id = p_process_id
                        and organization_id = p_org_id
                        and up_down_ind = 'U'
                        and hierarchy_type = 'L'
                       )
    and end_date is null
    union
    select p_process_id from dual);

Line: 2604

    select process_id
    from amw_process_organization
    where end_date is null
   and organization_id = p_org_id;

Line: 2626

update amw_process_organization
        set control_count_latest = (select count(*) from (
                            select distinct control_id from amw_control_associations
                            where pk1 = p_org_id
                            and pk2 in ( select child_id
  																			from amw_latest_hierarchies
  																			start with child_id = x(ctr) and organization_id = p_org_id
  																			connect by prior child_id = parent_id and organization_id = p_org_id
  																			)
/* ko remove org_denorm
                            ( ( select parent_child_id
                            from amw_org_hierarchy_denorm
                            where process_id = x(ctr)
                            and organization_id = p_org_id
                            and up_down_ind = 'D'
                            and hierarchy_type = 'L' ) union (select x(ctr) from dual)
                            )
*/
                            and deletion_date is null
                            and object_type = 'RISK_ORG'
                            ) ),last_update_date = sysdate
              ,last_updated_by = G_USER_ID
              ,last_update_login = G_LOGIN_ID
        where end_date is null
        and process_id <> -2
        and organization_id = p_org_id
        and process_id = x(ctr);

Line: 2662

		select process_id
    from amw_process_organization
    where organization_id = p_org_id
    and process_id in ( select parent_id
                        from amw_approved_hierarchies
                        start with child_id = p_process_id and organization_id = p_org_id
                        and start_date is not null and end_date is null
                        connect by prior parent_id = child_id and organization_id = p_org_id
                        and start_date is not null and end_date is null
                        union all
                        select p_process_id from dual
                        )
    and end_date is null;

Line: 2677

   (select process_id
    from amw_process_organization
    where organization_id = p_org_id
    and process_id in ( select parent_child_id
                        from amw_org_hierarchy_denorm
                        where process_id = p_process_id
                        and organization_id = p_org_id
                        and up_down_ind = 'U'
                        and hierarchy_type = 'A'
                       )
    and end_date is null
    union
    select p_process_id from dual);

Line: 2692

    select process_id
    from amw_process_organization
    where approval_date is not null
        and approval_end_date is null
        and organization_id = p_org_id;

Line: 2714

update amw_process_organization
        set risk_count = (select count(*) from (
                            select distinct risk_id from amw_risk_associations
                            where pk1 = p_org_id
                            and pk2 in ( select child_id
                                         from amw_approved_hierarchies
                                         start with child_id = x(ctr) and organization_id = p_org_id
                                                and start_date is not null and end_date is null
                                         connect by prior child_id = parent_id and organization_id = p_org_id
                                                and start_date is not null and end_date is null
																				)
--ko replacing the below clause for removing amw_org_hierarchy_denorm usage...
/*
                            ( ( select parent_child_id
                            from amw_org_hierarchy_denorm
                            where process_id = x(ctr)
                            and organization_id = p_org_id
                            and up_down_ind = 'D'
                            and hierarchy_type = 'A' ) union (select x(ctr) from dual)
                            )
*/
                            and approval_date is not null
                            and deletion_approval_date is null
                            and object_type = 'PROCESS_ORG'
                            ) ),last_update_date = sysdate
              ,last_updated_by = G_USER_ID
              ,last_update_login = G_LOGIN_ID
        where approval_date is not null
        and approval_end_date is null
        and process_id <> -2
        and organization_id = p_org_id
        and process_id = x(ctr);

Line: 2757

		select process_id
    from amw_process_organization
    where organization_id = p_org_id
    and process_id in ( select parent_id
                        from amw_approved_hierarchies
                        start with child_id = p_process_id and organization_id = p_org_id
                        and start_date is not null and end_date is null
                        connect by prior parent_id = child_id and organization_id = p_org_id
                        and start_date is not null and end_date is null
                        union all
                        select p_process_id from dual
                        )
    and end_date is null;

Line: 2772

   (select process_id
    from amw_process_organization
    where organization_id = p_org_id
    and process_id in ( select parent_child_id
                        from amw_org_hierarchy_denorm
                        where process_id = p_process_id
                        and organization_id = p_org_id
                        and up_down_ind = 'U'
                        and hierarchy_type = 'A'
                       )
    and end_date is null
    union
    select p_process_id from dual);

Line: 2787

    select process_id
    from amw_process_organization
    where approval_date is not null
        and approval_end_date is null
        and organization_id = p_org_id;

Line: 2810

update amw_process_organization
        set control_count= (select count(*) from (
                            select distinct control_id from amw_control_associations
                            where pk1 = p_org_id
                            and pk2 in ( select child_id
                                         from amw_approved_hierarchies
                                         start with child_id = x(ctr) and organization_id = p_org_id
                                                and start_date is not null and end_date is null
                                         connect by prior child_id = parent_id and organization_id = p_org_id
                                                and start_date is not null and end_date is null
																				)
--ko replacing the below clause for removing amw_org_hierarchy_denorm usage...
/*
                            ( ( select parent_child_id
                            from amw_org_hierarchy_denorm
                            where process_id = x(ctr)
                            and organization_id = p_org_id
                            and up_down_ind = 'D'
                            and hierarchy_type = 'A' ) union (select x(ctr) from dual)
                            )
*/
                            and approval_date is not null
                            and deletion_approval_date is null
                            and object_type = 'RISK_ORG'
                            ) ),last_update_date = sysdate
              ,last_updated_by = G_USER_ID
              ,last_update_login = G_LOGIN_ID
        where approval_date is not null
        and approval_end_date is null
        and process_id <> -2
        and organization_id = p_org_id
        and process_id = x(ctr);

Line: 2860

        select parent_child_id process_to_count
        from amw_proc_hierarchy_denorm
        where process_id = pid
        and up_down_ind = 'D'
        and hierarchy_type = 'A'
        union
        select pid process_to_count from dual;

Line: 2869

    select  aauv.organization_id,
    		name
    from    amw_audit_units_v aauv
    where   NVL( AAUV.DATE_TO,SYSDATE ) >= SYSDATE
    and     'Y' = AMW_UTILITY_PVT.IS_ORG_REGISTERED(aauv.ORGANIZATION_ID)
    and     aauv.organization_id not in(
                select distinct organization_id
                from amw_process_organization
                where process_id = pid
                and end_date is null
                and (
                    deletion_date is null or
                    (deletion_date is not null and approval_date is null)
                )
            )
    and (UPPER(NAME) LIKE UPPER(orgName));

Line: 2886

    select  aauv.organization_id,
    		name
    from    amw_audit_units_v aauv
    where   NVL( AAUV.DATE_TO,SYSDATE ) >= SYSDATE
    and     'Y' = AMW_UTILITY_PVT.IS_ORG_REGISTERED(aauv.ORGANIZATION_ID)
    and     aauv.organization_id not in(
                select distinct organization_id
                from amw_process_organization
                where process_id = pid
                and end_date is null
                and (
                    deletion_date is null or
                    (deletion_date is not null and approval_date is null)
                )
            )
    and NAME >= rangeFrom and substr(NAME,0,length(rangeTo))<= rangeTo;

Line: 2914

        select parent_child_id process_to_count
        from amw_proc_hierarchy_denorm
        where process_id = pid
        and up_down_ind = 'D'
        and hierarchy_type = 'A'
        union
        select pid process_to_count from dual;

Line: 2967

			p_update_count		=> FND_API.G_FALSE,
			p_commit            => FND_API.G_FALSE,
			x_return_status		=> l_return_status,
			x_msg_count			=> l_msg_count,
			x_msg_data			=> l_msg_data);

Line: 3009

	update amw_process AP
  	set AP.org_count = (select count(o.organization_id)
    					from hr_all_organization_units o,
              			hr_organization_information o2
        					WHERE o.organization_id = o2.organization_id
         					 and o2.org_information_context = 'CLASS'
         					 and o2.org_information1 = 'AMW_AUDIT_UNIT'
         					 and o2.org_information2 = 'Y'
                             and exists (select 1
                            			from amw_process_organization APO
                            			WHERE APO.organization_id = o.ORGANIZATION_ID
                            			AND  APO.process_id = AP.PROCESS_ID
                            			and APO.end_date is null
                            			and (APO.deletion_date is null or (APO.deletion_date is not null and APO.approval_date is null)))),
         AP.object_version_number = AP.object_version_number + 1
        ,AP.last_update_date = sysdate
        ,AP.last_updated_by = G_USER_ID
        ,AP.last_update_login = G_LOGIN_ID
   	where AP.approval_date is not null
    and AP.approval_end_date is null
    and AP.process_id <> -1
    and AP.process_id   IN (select APHD.parent_child_id process_to_count
                              from amw_proc_hierarchy_denorm APHD
                              where APHD.process_id = p_process_id
                              and APHD.up_down_ind = 'D'
                              and APHD.hierarchy_type = 'A'
                              union
                              select p_process_id process_to_count from dual);

Line: 3040

        'amw.plsql.AMW_ORG_HIERARCHY_PKG.PUSH_PROC_ORG_SRS.Update_ORG_Counts','UPDATED');

Line: 3048

 		update amw_process_organization APO
        set APO.risk_count_latest = (
                            select count(distinct ARA.risk_id) from amw_risk_associations ARA
                            where ARA.pk1 = APO.ORGANIZATION_ID
                            and ARA.pk2 in ( select alh.child_id
																				     from amw_latest_hierarchies alh
																				     start with alh.child_id = APO.PROCESS_ID and alh.organization_id = APO.ORGANIZATION_ID
																				     connect by prior alh.child_id = alh.parent_id and alh.organization_id = APO.ORGANIZATION_ID
																				    )
--ko replacing the below clause for removing amw_org_hierarchy_denorm usage...
/*
                            ( ( select AOH.parent_child_id
                            from amw_org_hierarchy_denorm AOH
                            where AOH.process_id = APO.PROCESS_ID
                            and AOH.organization_id = APO.ORGANIZATION_ID
                            and AOH.up_down_ind = 'D'
                            and AOH.hierarchy_type = 'L' ) union all (select APO.PROCESS_ID from dual)
                            )
*/
                            and ARA.deletion_date is null
                            and ARA.object_type = 'PROCESS_ORG'
                            ),
        APO.control_count_latest = (
                            select count(distinct ACA.CONTROL_ID) from amw_control_associations ACA
                            where ACA.pk1 = APO.ORGANIZATION_ID
                            and ACA.pk2 in ( select alh.child_id
																				     from amw_latest_hierarchies alh
																				     start with alh.child_id = APO.PROCESS_ID and alh.organization_id = APO.ORGANIZATION_ID
																				     connect by prior alh.child_id = alh.parent_id and alh.organization_id = APO.ORGANIZATION_ID
																				    )
--ko replacing the below clause for removing amw_org_hierarchy_denorm usage...
/*													( ( select AOH.parent_child_id
                            from amw_org_hierarchy_denorm AOH
                            where AOH.process_id = APO.PROCESS_ID
                            and AOH.organization_id = APO.ORGANIZATION_ID
                            and AOH.up_down_ind = 'D'
                            and AOH.hierarchy_type = 'L' ) union all (select APO.PROCESS_ID from dual)
                            )
*/
                            and ACA.deletion_date is null
                            and ACA.object_type = 'RISK_ORG'
                            )
              ,APO.last_update_date = sysdate
              ,APO.last_updated_by = G_USER_ID
              ,APO.last_update_login = G_LOGIN_ID
        where APO.end_date is null
        and APO.process_id <> -2
        and APO.organization_id in (  select  aauv.organization_id
    								  from    amw_audit_units_v aauv
    								  where NVL( AAUV.DATE_TO,SYSDATE ) >= SYSDATE
    								  and  NAME >= p_org_range_from and substr(NAME,0,length(p_org_range_to))<= p_org_range_to);

Line: 3101

    	update amw_process_organization APO
        set APO.risk_count_latest = (
                            select count(distinct ARA.risk_id) from amw_risk_associations ARA
                            where ARA.pk1 = APO.ORGANIZATION_ID
                            and ARA.pk2 in ( select alh.child_id
																				     from amw_latest_hierarchies alh
																				     start with alh.child_id = APO.PROCESS_ID and alh.organization_id = APO.ORGANIZATION_ID
																				     connect by prior alh.child_id = alh.parent_id and alh.organization_id = APO.ORGANIZATION_ID
																				    )
--ko replacing the below clause for removing amw_org_hierarchy_denorm usage...
/*
                            ( ( select AOH.parent_child_id
                            from amw_org_hierarchy_denorm AOH
                            where AOH.process_id = APO.PROCESS_ID
                            and AOH.organization_id = APO.ORGANIZATION_ID
                            and AOH.up_down_ind = 'D'
                            and AOH.hierarchy_type = 'L' ) union all (select APO.PROCESS_ID from dual)
                            )
*/
                            and ARA.deletion_date is null
                            and ARA.object_type = 'PROCESS_ORG'
                            ),
        APO.control_count_latest = (
                            select count(distinct ACA.CONTROL_ID) from amw_control_associations ACA
                            where ACA.pk1 = APO.ORGANIZATION_ID
                            and ACA.pk2 in ( select alh.child_id
																				     from amw_latest_hierarchies alh
																				     start with alh.child_id = APO.PROCESS_ID and alh.organization_id = APO.ORGANIZATION_ID
																				     connect by prior alh.child_id = alh.parent_id and alh.organization_id = APO.ORGANIZATION_ID
																				    )
--ko replacing the below clause for removing amw_org_hierarchy_denorm usage...
/*
                            ( ( select AOH.parent_child_id
                            from amw_org_hierarchy_denorm AOH
                            where AOH.process_id = APO.PROCESS_ID
                            and AOH.organization_id = APO.ORGANIZATION_ID
                            and AOH.up_down_ind = 'D'
                            and AOH.hierarchy_type = 'L' ) union all (select APO.PROCESS_ID from dual)
                            )
*/
                            and ACA.deletion_date is null
                            and ACA.object_type = 'RISK_ORG'
                            )
              ,APO.last_update_date = sysdate
              ,APO.last_updated_by = G_USER_ID
              ,APO.last_update_login = G_LOGIN_ID
        where APO.end_date is null
        and APO.process_id <> -2
        and APO.organization_id in (  select  aauv.organization_id
    								  from    amw_audit_units_v aauv
    								  where NVL( AAUV.DATE_TO,SYSDATE ) >= SYSDATE
    								  and (UPPER(NAME) LIKE UPPER(p_org_name || '%')));

Line: 3157

        'amw.plsql.AMW_ORG_HIERARCHY_PKG.PUSH_PROC_ORG_SRS.Update_Latest_Counts','UPDATED');

Line: 3162

	-- Update the latest risk and control counts

        if fnd_profile.value('AMW_PROC_ORG_ASS_APPRV') = 'Y' then

          if p_org_range_from is not null and p_org_range_to is not null then

	   		update amw_process_organization APO
        	set APO.risk_count = (
                            select count(distinct ARA.risk_id) from amw_risk_associations ARA
                            where ARA.pk1 = APO.ORGANIZATION_ID
                            and ARA.pk2 in (  select alh.child_id
											  											from amw_approved_hierarchies  alh
											  											start with alh.child_id = APO.PROCESS_ID and alh.organization_id = APO.ORGANIZATION_ID
											     															and alh.start_date is not null and alh.end_date is null
								              								connect by prior alh.child_id = alh.parent_id and alh.organization_id = APO.ORGANIZATION_ID
								                												and alh.start_date is not null and alh.end_date is null
								           									)
--ko replacing the below clause for removing amw_org_hierarchy_denorm usage...
/*
                            ( ( select AOH.parent_child_id
                            from amw_org_hierarchy_denorm AOH
                            where AOH.process_id = APO.PROCESS_ID
                            and AOH.organization_id = APO.ORGANIZATION_ID
                            and AOH.up_down_ind = 'D'
                            and AOH.hierarchy_type = 'A' ) union all (select APO.PROCESS_ID from dual)
                            )
*/
                            and ARA.approval_date is not null
                            and ARA.deletion_approval_date is null
                            and ARA.object_type = 'PROCESS_ORG'
                            ),
        	APO.control_count = (
                            select count(distinct ACA.CONTROL_ID) from amw_control_associations ACA
                            where ACA.pk1 = APO.ORGANIZATION_ID
                            and ACA.pk2 in (  select alh.child_id
											  											from amw_approved_hierarchies  alh
											  											start with alh.child_id = APO.PROCESS_ID and alh.organization_id = APO.ORGANIZATION_ID
											     															and alh.start_date is not null and alh.end_date is null
								              								connect by prior alh.child_id = alh.parent_id and alh.organization_id = APO.ORGANIZATION_ID
								                												and alh.start_date is not null and alh.end_date is null
								           									)
--ko replacing the below clause for removing amw_org_hierarchy_denorm usage...
/*
                            ( ( select AOH.parent_child_id
                            from amw_org_hierarchy_denorm AOH
                            where AOH.process_id = APO.PROCESS_ID
                            and AOH.organization_id = APO.ORGANIZATION_ID
                            and AOH.up_down_ind = 'D'
                            and AOH.hierarchy_type = 'A' ) union all (select APO.PROCESS_ID from dual)
                            )
*/
                            and ACA.approval_date is not null
                            and ACA.deletion_approval_date is null
                            and ACA.object_type = 'RISK_ORG'
                            )
              ,APO.last_update_date = sysdate
              ,APO.last_updated_by = G_USER_ID
              ,APO.last_update_login = G_LOGIN_ID
           where APO.approval_date is not null
           and APO.approval_end_date is null
           and APO.process_id <> -2
           and APO.organization_id in (  select  aauv.organization_id
    								  from    amw_audit_units_v aauv
    								  where NVL( AAUV.DATE_TO,SYSDATE ) >= SYSDATE
    								  and  NAME >= p_org_range_from and substr(NAME,0,length(p_org_range_to))<= p_org_range_to);

Line: 3229

    		update amw_process_organization APO
        	set APO.risk_count = (
                            select count(distinct ARA.risk_id) from amw_risk_associations ARA
                            where ARA.pk1 = APO.ORGANIZATION_ID
                            and ARA.pk2 in (  select alh.child_id
											  											from amw_approved_hierarchies  alh
											  											start with alh.child_id = APO.PROCESS_ID and alh.organization_id = APO.ORGANIZATION_ID
											     															and alh.start_date is not null and alh.end_date is null
								              								connect by prior alh.child_id = alh.parent_id and alh.organization_id = APO.ORGANIZATION_ID
								                												and alh.start_date is not null and alh.end_date is null
								           									)
--ko replacing the below clause for removing amw_org_hierarchy_denorm usage...
/*
                            ( ( select AOH.parent_child_id
                            from amw_org_hierarchy_denorm AOH
                            where AOH.process_id = APO.PROCESS_ID
                            and AOH.organization_id = APO.ORGANIZATION_ID
                            and AOH.up_down_ind = 'D'
                            and AOH.hierarchy_type = 'A' ) union all (select APO.PROCESS_ID from dual)
                            )
*/
                            and ARA.approval_date is not null
                            and ARA.deletion_approval_date is null
                            and ARA.object_type = 'PROCESS_ORG'
                            ),
        	APO.control_count = (
                            select count(distinct ACA.CONTROL_ID) from amw_control_associations ACA
                            where ACA.pk1 = APO.ORGANIZATION_ID
                            and ACA.pk2 in (  select alh.child_id
											  											from amw_approved_hierarchies  alh
											  											start with alh.child_id = APO.PROCESS_ID and alh.organization_id = APO.ORGANIZATION_ID
											     															and alh.start_date is not null and alh.end_date is null
								              								connect by prior alh.child_id = alh.parent_id and alh.organization_id = APO.ORGANIZATION_ID
								                												and alh.start_date is not null and alh.end_date is null
								           									)
--ko replacing the below clause for removing amw_org_hierarchy_denorm usage...
/*
                            ( ( select AOH.parent_child_id
                            from amw_org_hierarchy_denorm AOH
                            where AOH.process_id = APO.PROCESS_ID
                            and AOH.organization_id = APO.ORGANIZATION_ID
                            and AOH.up_down_ind = 'D'
                            and AOH.hierarchy_type = 'A' ) union all (select APO.PROCESS_ID from dual)
                            )
*/
                            and ACA.approval_date is not null
                            and ACA.deletion_approval_date is null
                            and ACA.object_type = 'RISK_ORG'
                            )
              ,APO.last_update_date = sysdate
              ,APO.last_updated_by = G_USER_ID
              ,APO.last_update_login = G_LOGIN_ID
           where APO.approval_date is not null
           and APO.approval_end_date is null
           and APO.process_id <> -2
           and APO.organization_id in (  select  aauv.organization_id
    								  from    amw_audit_units_v aauv
    								  where NVL( AAUV.DATE_TO,SYSDATE ) >= SYSDATE
    								  and (UPPER(NAME) LIKE UPPER(p_org_name || '%')));

Line: 3292

        'amw.plsql.AMW_ORG_HIERARCHY_PKG.PUSH_PROC_ORG_SRS.Update_Approved_Counts','UPDATED');

Line: 3439

        select parent_child_id process_to_count
        from amw_proc_hierarchy_denorm
        where process_id = pid
        and up_down_ind = 'D'
        and hierarchy_type = 'A'
        union
        select pid process_to_count from dual;

Line: 3520

	l_sql_string  := 'select organization_id, name  from amw_audit_units_v where  organization_id in ( ' ||
								   replace(rtrim(l_org_string,'x'),'x',',') || ')';

Line: 3535

			p_update_count		=> FND_API.G_FALSE,
			p_commit            => FND_API.G_FALSE,
			x_return_status		=> l_return_status,
			x_msg_count			=> l_msg_count,
			x_msg_data			=> l_msg_data);

Line: 3576

    /* This block is to update the latest and approved risk control counts..*/
    l_sql_string  := 'select organization_id, process_id  from amw_process_organization where revision_number = 1' ||
    							  ' and process_id <> -2  and organization_id in ( ' ||
								   replace(rtrim(l_org_string,'x'),'x',',') || ')';

Line: 3584

	-- Update the latest risk and control counts
	if l_org_proc_tbl.exists(1) then
		l_orgs_tbl :=  t_tn();

Line: 3596

		update amw_process_organization
        set risk_count_latest = (select count(*) from (
                            select distinct risk_id from amw_risk_associations
                            where pk1 = l_orgs_tbl(ctr)
                            and pk2 in (select alh.child_id
					from amw_latest_hierarchies alh
					start with alh.child_id = l_procs_tbl(ctr) and alh.organization_id = l_orgs_tbl(ctr)
					connect by prior alh.child_id = alh.parent_id and alh.organization_id = l_orgs_tbl(ctr)
					)
--ko replacing the below clause for removing amw_org_hierarchy_denorm usage...
/*
                            ( ( select parent_child_id
                            from amw_org_hierarchy_denorm
                            where process_id = l_procs_tbl(ctr)
                            and organization_id = l_orgs_tbl(ctr)
                            and up_down_ind = 'D'
                            and hierarchy_type = 'L' ) union (select l_procs_tbl(ctr) from dual)
                            )
*/
                            and deletion_date is null
                            and object_type = 'PROCESS_ORG'
                            ) ),
            control_count_latest = (select count(*) from (
                            select distinct control_id from amw_control_associations
                            where pk1 = l_orgs_tbl(ctr)
                            and pk2 in (select alh.child_id
					from amw_latest_hierarchies alh
					start with alh.child_id = l_procs_tbl(ctr) and alh.organization_id = l_orgs_tbl(ctr)
					connect by prior alh.child_id = alh.parent_id and alh.organization_id = l_orgs_tbl(ctr)
					)
--ko replacing the below clause for removing amw_org_hierarchy_denorm usage...
/*
														( ( select parent_child_id
                            from amw_org_hierarchy_denorm
                            where process_id = l_procs_tbl(ctr)
                            and organization_id = l_orgs_tbl(ctr)
                            and up_down_ind = 'D'
                            and hierarchy_type = 'L' ) union (select l_procs_tbl(ctr) from dual)
                            )
*/
                            and deletion_date is null
                            and object_type = 'RISK_ORG'
                            ) )
              ,last_update_date = sysdate
              ,last_updated_by = G_USER_ID
              ,last_update_login = G_LOGIN_ID
        where end_date is null
        and process_id <> -2
        and organization_id = l_orgs_tbl(ctr)
        and process_id = l_procs_tbl(ctr);

Line: 3652

        update amw_process_organization
        set risk_count = (select count(*) from (
                            select distinct risk_id from amw_risk_associations
                            where pk1 = l_orgs_tbl(ctr)
                            and pk2 in (  select alh.child_id
                                          from  amw_approved_hierarchies alh
																					start with alh.child_id = l_procs_tbl(ctr) and alh.organization_id = l_orgs_tbl(ctr)
                                              and alh.start_date is not null and alh.end_date is null
																					connect by prior alh.child_id = alh.parent_id and alh.organization_id = l_orgs_tbl(ctr)
										      										and alh.start_date is not null and alh.end_date is null
							             							)
--ko replacing the below clause for removing amw_org_hierarchy_denorm usage...
/*
                            ( ( select parent_child_id
                            from amw_org_hierarchy_denorm
                            where process_id = l_procs_tbl(ctr)
                            and organization_id = l_orgs_tbl(ctr)
                            and up_down_ind = 'D'
                            and hierarchy_type = 'A' ) union (select l_procs_tbl(ctr) from dual)
                            )
*/
                            and approval_date is not null
                            and deletion_approval_date is null
                            and object_type = 'PROCESS_ORG'
                            ) ),
             control_count= (select count(*) from (
                            select distinct control_id from amw_control_associations
                            where pk1 = l_orgs_tbl(ctr)
                            and pk2 in (  select alh.child_id
                                          from  amw_approved_hierarchies alh
																					start with alh.child_id = l_procs_tbl(ctr) and alh.organization_id = l_orgs_tbl(ctr)
                                              and alh.start_date is not null and alh.end_date is null
																					connect by prior alh.child_id = alh.parent_id and alh.organization_id = l_orgs_tbl(ctr)
										      										and alh.start_date is not null and alh.end_date is null
							             							)
--ko replacing the below clause for removing amw_org_hierarchy_denorm usage...
/*
                            ( ( select parent_child_id
                            from amw_org_hierarchy_denorm
                            where process_id = l_procs_tbl(ctr)
                            and organization_id = l_orgs_tbl(ctr)
                            and up_down_ind = 'D'
                            and hierarchy_type = 'A' ) union (select l_procs_tbl(ctr) from dual)
                            )
*/
                            and approval_date is not null
                            and deletion_approval_date is null
                            and object_type = 'RISK_ORG'
                            ) )
              ,last_update_date = sysdate
              ,last_updated_by = G_USER_ID
              ,last_update_login = G_LOGIN_ID
        where approval_date is not null
        and approval_end_date is null
        and process_id <> -2
        and organization_id = l_orgs_tbl(ctr)
        and process_id = l_procs_tbl(ctr);

Line: 3718

  		update amw_process
  		set org_count = (select count(*) from
                (select distinct organization_id
                from amw_process_organization
                where process_id = x_ptbl(i)
                and end_date is null
                and (deletion_date is null or (deletion_date is not null and approval_date is null)))),
    	object_version_number = object_version_number + 1
    	,last_update_date = sysdate
    	,last_updated_by = G_USER_ID
    	,last_update_login = G_LOGIN_ID
		where approval_date is not null
		and approval_end_date is null
		and process_id <> -1  --retained for safety
		and process_id = x_ptbl(i);

Line: 3757

p_update_count		    in varchar2 := FND_API.G_TRUE,
p_commit			    in varchar2 := FND_API.G_FALSE,
p_validation_level		IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_init_msg_list			IN VARCHAR2 := FND_API.G_FALSE,
x_return_status			out nocopy varchar2,
x_msg_count			    out nocopy number,
x_msg_data			    out nocopy varchar2 )

is


cursor c1 (pid number) is
        select parent_child_id process_to_count
        from amw_proc_hierarchy_denorm
        where process_id = pid
        and up_down_ind = 'D'
        and hierarchy_type = 'A'
        union
        select pid process_to_count from dual;

Line: 3802

select LTRIM(l_org_string, '1234567890') into str from dual;

Line: 3805

select SUBSTR(l_org_string, 1, diff) into orgstr from dual;

Line: 3815

			p_update_count     => p_update_count,
			p_commit            =>p_commit,
			x_return_status		=> l_return_status,
			x_msg_count			=> l_msg_count,
			x_msg_data			=> l_msg_data);

Line: 3837

		AMW_PROC_ORG_APPROVAL_PKG.approve(p_process_id, orgid,p_update_count);

Line: 3841

select LTRIM(str, 'x') into l_org_string from dual;

Line: 3844

IF (p_update_count = FND_API.G_TRUE) THEN
	-- update the org counts of the child process and its hierarchy....
	for descendents_rec in c1(p_process_id) loop
     		exit when c1%notfound;

Line: 3848

      		amw_rl_hierarchy_pkg.update_org_count(descendents_rec.process_to_count);

Line: 3888

p_update_count		    in varchar2 := FND_API.G_TRUE,
p_commit			in varchar2 := FND_API.G_FALSE,
p_validation_level		IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_init_msg_list			IN VARCHAR2 := FND_API.G_FALSE,
x_return_status			out nocopy varchar2,
x_msg_count			out nocopy number,
x_msg_data			out nocopy varchar2 )

is

L_API_NAME CONSTANT VARCHAR2(30) := 'push_proc_per_org';

Line: 3944

        'amw.plsql.AMW_ORG_HIERARCHY_PKG.PUSH_PROC_PER_ORG.update_denorm',
        'Begin update denrom; OrgId:' ||p_org_id );

Line: 3947

  AMW_RL_HIERARCHY_PKG.update_denorm(p_org_id => p_org_id);

Line: 3949

  IF p_update_count = FND_API.G_TRUE THEN
  -- ko update the risk1 counts of the child process..
  if( l_log_stmt_level >= l_curr_log_level ) then
    FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
        'amw.plsql.AMW_ORG_HIERARCHY_PKG.PUSH_PROC_PER_ORG.update_counts',
        'Begin update counts; OrgId:' ||p_org_id );

Line: 4032

        select parent_child_id process_to_count
        from amw_proc_hierarchy_denorm
        where process_id = pid
        and up_down_ind = 'D'
        and hierarchy_type = 'A'
        union
        select pid process_to_count from dual;

Line: 4059

  SELECT 1 INTO l_dummy
  FROM amw_process_organization
  where process_id = p_parent_id
  and organization_id = p_organization_id
  and end_date is null
  and deletion_date is null;

Line: 4067

	-- Check that parent can be updated..(Not in pA or in locked state..)
	produce_err_if_pa_or_locked(p_org_id => p_organization_id,p_process_id => p_parent_id);

Line: 4071

       	SELECT 1 INTO l_dummy
        FROM amw_process_organization
        where process_id = p_child_id
        and organization_id = p_organization_id
        and end_date is null
  	and deletion_date is null;

Line: 4080

	 SELECT 1 INTo l_dummy
	 from amw_latest_hierarchies
	 where organization_id = p_organization_id
	 and parent_id = p_parent_id
	 and child_id = p_child_id;

Line: 4089

 		 add_delete_ex_child (
			p_org_id => p_organization_id,
			p_parent_process_id => p_parent_id,
			p_child_process_id => p_child_id,
			p_action => 'ADD');

Line: 4097

		-- Update the Proc_org_hierarchy_denorm tables..


		AMW_RL_HIERARCHY_PKG.update_denorm(p_org_id => p_organization_id);

Line: 4102

		-- update the risk 1 counts of the parent process ..
		upd_ltst_risk_count(p_org_id => p_organization_id, p_process_id => p_parent_id);

Line: 4113

		SELECT 1 INTO l_dummy
  		FROM amw_process_organization
		where process_id = p_child_id
		and organization_id = p_organization_id
  		and end_date is null
	  	and deletion_date is null;

Line: 4130

			-- Update the Proc_org_hierarchy_denorm tables..
			AMW_RL_HIERARCHY_PKG.update_denorm(p_org_id => p_organization_id);

Line: 4133

			-- update the risk1 counts of the child process..
			upd_ltst_risk_count(p_org_id => p_organization_id, p_process_id => null);

Line: 4138

			-- update the org counts of the child process and its hierarchy....
			for descendents_rec in c1(p_child_id) loop
    	  		exit when c1%notfound;

Line: 4141

    	  		amw_rl_hierarchy_pkg.update_org_count(descendents_rec.process_to_count);

Line: 4170

/*kosriniv..... Procedure to delete a process in the organization from a parent process..
 * p_organization_id  ---> Organization Id
 * p_parent_id        ---> parent process Id to which the child is being added
 * p_child_id         ---> process being associated
*/

PROCEDURE delete_organization_child
( p_organization_id	    IN NUMBER,
  p_child_id                IN NUMBER,
  P_parent_id		    IN NUMBER,
  p_commit		           IN VARCHAR2 := FND_API.G_FALSE,
  p_validation_level		   IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
  p_init_msg_list		   IN VARCHAR2 := FND_API.G_FALSE,
  x_return_status		   OUT NOCOPY VARCHAR2,
  x_msg_count			   OUT NOCOPY VARCHAR2,
  x_msg_data			   OUT NOCOPY VARCHAR2)

IS

L_API_NAME CONSTANT VARCHAR2(30) := 'delete_organization_child';

Line: 4208

  SELECT 1 INTO l_dummy
  FROM amw_process_organization
  where process_id = p_parent_id
  and organization_id = p_organization_id
  and end_date is null
  and deletion_date is null;

Line: 4223

   SELECT 1 INTO l_dummy
   FROM amw_process_organization
   where process_id = p_child_id
   and organization_id = p_organization_id
   and end_date is null
   and deletion_date is null;

Line: 4231

   add_delete_ex_child (
	p_org_id => p_organization_id,
	p_parent_process_id => p_parent_id,
	p_child_process_id => p_child_id,
	p_action => 'DEL');

Line: 4241

   AMW_RL_HIERARCHY_PKG.update_denorm(p_org_id => p_organization_id);

Line: 4274

END delete_organization_child;

Line: 4312

   AMW_RL_HIERARCHY_PKG.update_denorm(p_org_id => p_organization_id);

Line: 4360

     select parent_child_id process_to_count
     from amw_proc_hierarchy_denorm
     where process_id = pid
     and up_down_ind = 'D'
     and hierarchy_type = 'A';

Line: 4401

   AMW_RL_HIERARCHY_PKG.update_denorm(p_org_id => p_org_id);

Line: 4410

    	amw_rl_hierarchy_pkg.update_org_count(descendents_rec.process_to_count);

Line: 4439

PROCEDURE update_latest_rc_counts
( p_organization_id	    IN NUMBER,
  P_process_id		    IN NUMBER,
  p_commit		           IN VARCHAR2 := FND_API.G_FALSE,
  p_validation_level		   IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
  p_init_msg_list		   IN VARCHAR2 := FND_API.G_FALSE,
  x_return_status		   OUT NOCOPY VARCHAR2,
  x_msg_count			   OUT NOCOPY VARCHAR2,
  x_msg_data			   OUT NOCOPY VARCHAR2)
IS

  L_API_NAME CONSTANT VARCHAR2(30) := 'update_latest_rc_counts';

Line: 4492

END update_latest_rc_counts;

Line: 4496

PROCEDURE update_approved_rc_counts
( p_organization_id	    IN NUMBER,
  P_process_id		    IN NUMBER,
  p_commit		           IN VARCHAR2 := FND_API.G_FALSE,
  p_validation_level		   IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
  p_init_msg_list		   IN VARCHAR2 := FND_API.G_FALSE,
  x_return_status		   OUT NOCOPY VARCHAR2,
  x_msg_count			   OUT NOCOPY VARCHAR2,
  x_msg_data			   OUT NOCOPY VARCHAR2)
IS

  L_API_NAME CONSTANT VARCHAR2(30) := 'update_approved_rc_counts';

Line: 4549

END update_approved_rc_counts;

Line: 4553

PROCEDURE insert_exception_justification (
p_exception_Id		IN Number,
p_justification	        IN Varchar2,
p_commit		in varchar2 := FND_API.G_FALSE,
p_validation_level	IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_init_msg_list		IN VARCHAR2 := FND_API.G_FALSE,
x_return_status		out nocopy varchar2,
x_msg_count		out nocopy number,
x_msg_data		out nocopy varchar2
)
IS

  L_API_NAME CONSTANT VARCHAR2(30) := 'insert_exception_justification';

Line: 4583

    insert into amw_exceptions_tl
		 (
                  EXCEPTION_ID,
                  LANGUAGE,
                  SOURCE_LANG,
                  JUSTIFICATION,
                  LAST_UPDATE_DATE,
                  LAST_UPDATED_BY,
                  CREATION_DATE,
                  CREATED_BY,
                  LAST_UPDATE_LOGIN
                  )
                  select
                 	p_Exception_Id,
                 	L.LANGUAGE_CODE,
                 	userenv('LANG'),
                 	p_Justification,
                 	sysdate,
                 	G_USER_ID,
                 	sysdate,
                 	G_USER_ID,
                 	G_LOGIN_ID
			from FND_LANGUAGES L
                 	where L.INSTALLED_FLAG in ('I', 'B')
                 	and not exists
                 		(select NULL
                 		 from AMW_EXCEPTIONS_TL T
                 		 where T.EXCEPTION_ID = p_Exception_Id
                 		 and T.LANGUAGE = L.LANGUAGE_CODE);

Line: 4633

END insert_exception_justification;

Line: 4644

           select child_id
           into l_dummy
           from amw_approved_hierarchies
           where parent_id = p_process_id
           and organization_id = p_organization_id
           and (end_date is null or end_date > sysdate)
           and child_id not in
              (select child_id
              from amw_latest_hierarchies
              where parent_id = p_process_id
              and organization_id = p_organization_id);

Line: 4658

                   select child_id
                   into l_dummy
                   from amw_latest_hierarchies
                   where parent_id = p_process_id
                   and organization_id = p_organization_id
                   and child_id not in
                       (select child_id
                       from amw_approved_hierarchies
                       where parent_id = p_process_id
                       and organization_id = p_organization_id
                       and (end_date is null or end_date > sysdate));

Line: 4685

    select 1
    into l_dummy
    from amw_process_organization
    where process_id = p_process_id
    and organization_id = p_organization_id
    and approval_status = 'A';

Line: 4735

select approval_status into appstatus
from amw_process_organization
where process_id = p_process_id
and organization_id = p_organization_id
and end_date is null;

Line: 4785

procedure delete_draft (p_organization_id in number,
                        p_process_id in number,
                        x_return_status out nocopy varchar2,
                        x_msg_count out nocopy number,
                        x_msg_data out nocopy varchar2) is

l_api_name constant varchar2(30) := 'delete_draft';

Line: 4798

             select parent_id
             from amw_latest_hierarchies
             where child_id = pid
             and organization_id = orgId ;

Line: 4822

select process_org_rev_id
    into previd from amw_process_organization
    where process_id = p_process_id
    and organization_id = p_organization_id
    and end_date is null;

Line: 4846

                       	            p_procedure_name => 'delete_draft',
                                    p_error_text => err_msg);

Line: 4857

    delete from amw_process_organization
    where process_id = p_process_id
    and organization_id = p_organization_id
    and end_date is null;

Line: 4863

    update amw_process_organization
    set end_date = null
    where process_id = p_process_id
    and organization_id = p_organization_id
    and approval_date is not null
    and approval_end_date is null;

Line: 4872

    	select 1 into l_dummy
    	from amw_process_organization
    	where process_id = p_process_id
    	and organization_id = p_organization_id
    	and deletion_date is not null
    	and end_date is null;

Line: 4879

    	-- So  process is deleted..so remove the process from hierarchy
    	for parent_rec in parents(p_organization_id,p_process_id) loop
    	  exit when parents%notfound;

Line: 4883

    	  delete from amw_latest_hierarchies
    	  where parent_id = parent_rec.parent_id
    	  and child_id = p_process_id
    	  and organization_id = p_organization_id;

Line: 4889

    	delete from amw_latest_hierarchies
    	where parent_id = p_process_id
    	and organization_id = p_organization_id;

Line: 4904

    	  delete from amw_latest_hierarchies
    	  where parent_id = parent_rec.parent_id
    	  and child_id = p_process_id
    	  and organization_id = p_organization_id;

Line: 4910

    delete from amw_latest_hierarchies
    where parent_id = p_process_id
    and organization_id = p_organization_id;

Line: 4913

    delete from amw_process_organization where process_id = p_process_id and organization_id = p_organization_id;

Line: 4919

delete from amw_risk_associations
where pk1 = p_organization_id
and pk2 = p_process_id
and approval_date is null
and object_type = 'PROCESS_ORG';

Line: 4927

update amw_risk_associations
set deletion_date = null
where pk1 = p_organization_id
and pk2 = p_process_id
and object_type = 'PROCESS_ORG'
and deletion_date is not null
and deletion_approval_date is null;

Line: 4939

delete from amw_control_associations
where pk1 = p_organization_id
and pk2 = p_process_id
and approval_date is null
and object_type = 'RISK_ORG';

Line: 4948

update amw_control_associations
set deletion_date = null
where pk1 = p_organization_id
and pk2 = p_process_id
and object_type = 'RISK_ORG'
and deletion_date is not null
and deletion_approval_date is null;

Line: 4960

delete from amw_acct_associations
where pk1 = p_organization_id
and pk2 = p_process_id
and approval_date is null
and object_type = 'PROCESS_ORG';

Line: 4966

update amw_acct_associations
set deletion_date = null
where pk1 = p_organization_id
and pk2 = p_process_id
and object_type = 'PROCESS_ORG'
and deletion_date is not null
and deletion_approval_date is null;

Line: 4975

delete from amw_objective_associations
where pk1 = p_organization_id
and pk2 = p_process_id
and approval_date is null
and object_type in ('PROCESS_ORG', 'CONTROL_ORG');

Line: 4981

update amw_objective_associations
set deletion_date = null
where pk1 = p_organization_id
and pk2 = p_process_id
and object_type in ('PROCESS_ORG', 'CONTROL_ORG')
and deletion_date is not null
and deletion_approval_date is null;

Line: 4989

delete from amw_ap_associations
where pk1 = p_organization_id
and   pk2 = p_process_id
and association_creation_date is null
and   object_type = 'CTRL_ORG';

Line: 4996

FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(X_entity_name => 'AMW_PROCESS_ORGANIZATION',
                                               X_pk1_value   => previd);

Line: 5001

amw_rl_hierarchy_pkg.update_org_count(p_process_id);

Line: 5005

amw_rl_hierarchy_pkg.update_denorm (p_organization_id, sysdate);

Line: 5051

procedure UPDATE_ORG_PROC_AP(p_organization_id	    IN NUMBER,
                        p_process_id in number,
                        p_date in DATE,
                        x_return_status out nocopy varchar2,
                        x_msg_count out nocopy number,
                        x_msg_data out nocopy varchar2) is

l_api_name constant varchar2(30) := 'UPDATE_ORG_PROC_AP';

Line: 5076

	insert into amw_ap_associations
    (AP_ASSOCIATION_ID,
    AUDIT_PROCEDURE_ID,
    PK1,
    PK2,
    PK3,
    DESIGN_EFFECTIVENESS,
    OP_EFFECTIVENESS,
    ASSOCIATION_CREATION_DATE,
    APPROVAL_DATE,
    DELETION_DATE,
    DELETION_APPROVAL_DATE,
    OBJECT_TYPE,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    OBJECT_VERSION_NUMBER)
    (select
    AMW_AP_ASSOCIATIONS_S.nextval,
    APA.AUDIT_PROCEDURE_ID,
    p_organization_id,
    p_process_id,
    CONTROLS.control_id,
    APA.DESIGN_EFFECTIVENESS,
    APA.OP_EFFECTIVENESS,
    null,
    null,
    null,
    null,
    'CTRL_ORG',
    sysdate,
    G_USER_ID,
    sysdate,
    G_USER_ID,
    G_LOGIN_ID,
    1
	from
	amw_ap_associations APA,
	(SELECT distinct control_id from
	amw_control_associations
	where object_type = 'RISK_ORG'
	AND PK1 = p_organization_id
	AND PK2 = p_process_id
	AND association_creation_date = p_date
	AND approval_date is null) CONTROLS
	where APA.object_type = 'CTRL'
	and APA.pk1 = CONTROLS.control_id
	and APA.approval_date is not null
	and APA.deletion_approval_date is null
	and APA.audit_procedure_id not in ( select audit_procedure_id from amw_ap_associations
                                where object_type = 'CTRL_ORG'
                                and pk1 = p_organization_id
                                and pk2 = p_process_id
                                and pk3 =  CONTROLS.control_id
                                and deletion_date is null));

Line: 5135

DELETE FROM AMW_AP_ASSOCIATIONS
WHERE object_type = 'CTRL_ORG'
and pk1 = p_organization_id
and pk2 = p_process_id
and association_creation_date is null
and pk3 not in ( SELECT control_id from
                 amw_control_associations
                 where object_type = 'RISK_ORG'
                 AND PK1 = p_organization_id
                 AND PK2 = p_process_id
                 AND approval_date is null);

Line: 5176

procedure UPDATE_ENTITY_AP(p_organization_id	    IN NUMBER,
                        p_date in DATE,
                        x_return_status out nocopy varchar2,
                        x_msg_count out nocopy number,
                        x_msg_data out nocopy varchar2) is

l_api_name constant varchar2(30) := 'UPDATE_ENTITY_AP';

Line: 5200

    insert into amw_ap_associations
    (AP_ASSOCIATION_ID,
    AUDIT_PROCEDURE_ID,
    PK1,
    PK2,
    DESIGN_EFFECTIVENESS,
    OP_EFFECTIVENESS,
    ASSOCIATION_CREATION_DATE,
    APPROVAL_DATE,
    DELETION_DATE,
    DELETION_APPROVAL_DATE,
    OBJECT_TYPE,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    OBJECT_VERSION_NUMBER)
    (select
    AMW_AP_ASSOCIATIONS_S.nextval,
    APA.AUDIT_PROCEDURE_ID,
    p_organization_id,
    CONTROLS.control_id,
    APA.DESIGN_EFFECTIVENESS,
    APA.OP_EFFECTIVENESS,
    sysdate,
    null,
    null,
    null,
    'ENTITY_AP',
    sysdate,
    G_USER_ID,
    sysdate,
    G_USER_ID,
    G_LOGIN_ID,
    1
	from
	amw_ap_associations APA,
	(SELECT distinct control_id from
	amw_control_associations
	where object_type = 'ENTITY_CONTROL'
	AND PK1 = p_organization_id
	AND association_creation_date = p_date	) CONTROLS
	where APA.object_type = 'CTRL'
	and APA.pk1 = CONTROLS.control_id
	and APA.approval_date is not null
	and APA.deletion_approval_date is null
	and APA.audit_procedure_id not in ( select audit_procedure_id from amw_ap_associations
                                where object_type = 'ENTITY_AP'
                                and pk1 = p_organization_id
                                and pk2 = CONTROLS.control_id)
    );

Line: 5254

DELETE FROM AMW_AP_ASSOCIATIONS
WHERE object_type = 'ENTITY_AP'
and pk1 = p_organization_id
and pk2 not in ( SELECT control_id from
                 amw_control_associations
                 where object_type = 'ENTITY_CONTROL'
                 AND PK1 = p_organization_id
                 );

Line: 5328

       select PROCESS_REV_ID,
			  SIGNIFICANT_PROCESS_FLAG,
			  STANDARD_PROCESS_FLAG,
			  PROCESS_CATEGORY,
			  STANDARD_VARIATION,
			  ATTRIBUTE_CATEGORY,
			  ATTRIBUTE1,
			  ATTRIBUTE2,
			  ATTRIBUTE3,
			  ATTRIBUTE4,
			  ATTRIBUTE5,
			  ATTRIBUTE6,
			  ATTRIBUTE7,
			  ATTRIBUTE8,
			  ATTRIBUTE9,
			  ATTRIBUTE10,
			  ATTRIBUTE11,
			  ATTRIBUTE12,
			  ATTRIBUTE13,
			  ATTRIBUTE14,
			  ATTRIBUTE15,
			  SECURITY_GROUP_ID,
			  PROCESS_TYPE,
			  CONTROL_ACTIVITY_TYPE
              into
              l_RL_PROCESS_REV_ID,
			  l_SIGNIFICANT_PROCESS_FLAG,
			  l_STANDARD_PROCESS_FLAG,
			  l_PROCESS_CATEGORY,
			  l_STANDARD_VARIATION,
			  l_ATTRIBUTE_CATEGORY,
			  l_ATTRIBUTE1,
			  l_ATTRIBUTE2,
			  l_ATTRIBUTE3,
			  l_ATTRIBUTE4,
			  l_ATTRIBUTE5,
			  l_ATTRIBUTE6,
			  l_ATTRIBUTE7,
			  l_ATTRIBUTE8,
			  l_ATTRIBUTE9,
			  l_ATTRIBUTE10,
			  l_ATTRIBUTE11,
			  l_ATTRIBUTE12,
			  l_ATTRIBUTE13,
			  l_ATTRIBUTE14,
			  l_ATTRIBUTE15,
			  l_SECURITY_GROUP_ID,
			  l_PROCESS_TYPE,
			  l_CONTROL_ACTIVITY_TYPE
              from amw_process
              where process_id = p_process_id
--ko need to sync with approved revision
--              and end_date is null;

Line: 5386

      update amw_process_organization
      set     RL_PROCESS_REV_ID             =   l_RL_PROCESS_REV_ID,
			  SIGNIFICANT_PROCESS_FLAG      =   l_SIGNIFICANT_PROCESS_FLAG,
			  STANDARD_PROCESS_FLAG	        =   l_STANDARD_PROCESS_FLAG,
			  PROCESS_CATEGORY_CODE	            =   l_PROCESS_CATEGORY,
			  STANDARD_VARIATION	        =   l_STANDARD_VARIATION,
			  ATTRIBUTE_CATEGORY            =   l_ATTRIBUTE_CATEGORY,
			  ATTRIBUTE1		    =   l_ATTRIBUTE1,
			  ATTRIBUTE2			=   l_ATTRIBUTE2,
			  ATTRIBUTE3			=   l_ATTRIBUTE3,
			  ATTRIBUTE4			=   l_ATTRIBUTE4,
			  ATTRIBUTE5			=   l_ATTRIBUTE5,
			  ATTRIBUTE6			=   l_ATTRIBUTE6,
			  ATTRIBUTE7			=   l_ATTRIBUTE7,
			  ATTRIBUTE8			=   l_ATTRIBUTE8,
			  ATTRIBUTE9			=   l_ATTRIBUTE9,
			  ATTRIBUTE10			=   l_ATTRIBUTE10,
			  ATTRIBUTE11			=   l_ATTRIBUTE11,
			  ATTRIBUTE12			=   l_ATTRIBUTE12,
			  ATTRIBUTE13			=   l_ATTRIBUTE13,
			  ATTRIBUTE14			=   l_ATTRIBUTE14,
			  ATTRIBUTE15			=   l_ATTRIBUTE15,
			  SECURITY_GROUP_ID		=   l_SECURITY_GROUP_ID,
			  PROCESS_TYPE			=   l_PROCESS_TYPE,
			  CONTROL_ACTIVITY_TYPE	=   l_CONTROL_ACTIVITY_TYPE
      where organization_id = p_org_id
      and process_id = p_process_id
      and end_date is null;

Line: 5417

 	--ko, delete all the unapproved rows
        delete amw_acct_associations
        where pk1 = p_org_id
        and   pk2 = p_process_id
        and   object_type = 'PROCESS_ORG'
        and   approval_date is null;

Line: 5425

        update amw_acct_associations
        set DELETION_DATE = sysdate
        where pk1 = p_org_id
        and   pk2 = p_process_id
        and   object_type = 'PROCESS_ORG'
        and   deletion_date is null
        and natural_account_id not in (select natural_account_id
        								from amw_acct_associations
        								where pk1 = p_process_id
        								and object_type = 'PROCESS'
        								and approval_date is not null
        								and deletion_approval_date is null);

Line: 5439

		insert into amw_acct_associations(
		ACCT_ASSOC_ID,
        NATURAL_ACCOUNT_ID,
        PK1,
        PK2,
        STATEMENT_ID,
        STATEMENT_LINE_ID,
        ORIG_SYSTEM_ACCT_VALUE,
        ASSOCIATION_CREATION_DATE,
        APPROVAL_DATE,
        DELETION_DATE,
        DELETION_APPROVAL_DATE,
        OBJECT_TYPE,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        OBJECT_VERSION_NUMBER)
        (select
        AMW_ACCT_ASSOCIATIONS_S.nextval,
        NATURAL_ACCOUNT_ID,
        p_org_id,
        PK1,
        STATEMENT_ID,
        STATEMENT_LINE_ID,
        ORIG_SYSTEM_ACCT_VALUE,
        sysdate,
        null,
        null,
        null,
        'PROCESS_ORG',
        sysdate,
        G_USER_ID,
        sysdate,
        G_USER_ID,
        G_LOGIN_ID,
        1
        from amw_acct_associations
        where PK1 = p_process_id
        and object_type = 'PROCESS'
        and approval_date is not null
        and deletion_approval_date is null
        and NATURAL_ACCOUNT_ID not in(select natural_account_id
                                      from amw_acct_associations
                                      where pk1 = p_org_id
        							  and   pk2 = p_process_id
        							  and   object_type = 'PROCESS_ORG'
        							  and   deletion_date is null));

Line: 5495

delete from amw_objective_associations
where pk1 = p_org_id
and   pk2 = p_process_id
and   object_type = 'PROCESS_ORG'
and   approval_date is null;

Line: 5502

update amw_objective_associations
set deletion_date = sysdate
where pk1 = p_org_id
and   pk2 = p_process_id
and   object_type = 'PROCESS_ORG'
and   deletion_date is null
and PROCESS_OBJECTIVE_ID not in (select PROCESS_OBJECTIVE_ID
								from amw_objective_associations
								where pk1 = p_process_id
								and object_type = 'PROCESS'
								and approval_date is not null
								and deletion_approval_date is null);

Line: 5515

insert into amw_objective_associations
    (OBJECTIVE_ASSOCIATION_ID,
    PROCESS_OBJECTIVE_ID,
    PK1,
    PK2,
    ASSOCIATION_CREATION_DATE,
    APPROVAL_DATE,
    DELETION_DATE,
    DELETION_APPROVAL_DATE,
    OBJECT_TYPE,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    OBJECT_VERSION_NUMBER)
    (select
    AMW_OBJECTIVE_ASSOCIATIONS_S.nextval,
    PROCESS_OBJECTIVE_ID,
    p_org_id,
    PK1,
    sysdate,
    null,
    null,
    null,
    'PROCESS_ORG',
    sysdate,
    G_USER_ID,
    sysdate,
    G_USER_ID,
    G_LOGIN_ID,
    1
    from amw_objective_associations
    where PK1 = p_process_id
    and object_type = 'PROCESS'
    and approval_date is not null
    and deletion_approval_date is null
    and PROCESS_OBJECTIVE_ID not in (select PROCESS_OBJECTIVE_ID
                                    from amw_objective_associations
                                    where
                                    PK1 = p_org_id
                                    and PK2 = p_process_id
                                    and object_type = 'PROCESS_ORG'
                                    and deletion_date is null)
    );

Line: 5600

    select  PROCESS_REV_ID,
            SIGNIFICANT_PROCESS_FLAG,
            STANDARD_PROCESS_FLAG,
		    PROCESS_CATEGORY,
			STANDARD_VARIATION,
			ATTRIBUTE_CATEGORY,
			ATTRIBUTE1,
			ATTRIBUTE2,
			ATTRIBUTE3,
			ATTRIBUTE4,
			ATTRIBUTE5,
			ATTRIBUTE6,
			ATTRIBUTE7,
			ATTRIBUTE8,
			ATTRIBUTE9,
			ATTRIBUTE10,
			ATTRIBUTE11,
			ATTRIBUTE12,
			ATTRIBUTE13,
			ATTRIBUTE14,
			ATTRIBUTE15,
			SECURITY_GROUP_ID,
			PROCESS_TYPE,
			CONTROL_ACTIVITY_TYPE
            into
            l_RL_PROCESS_REV_ID,
			l_SIGNIFICANT_PROCESS_FLAG,
			l_STANDARD_PROCESS_FLAG,
			l_PROCESS_CATEGORY,
			l_STANDARD_VARIATION,
			l_ATTRIBUTE_CATEGORY,
			l_ATTRIBUTE1,
			l_ATTRIBUTE2,
			l_ATTRIBUTE3,
			l_ATTRIBUTE4,
			l_ATTRIBUTE5,
			l_ATTRIBUTE6,
			l_ATTRIBUTE7,
			l_ATTRIBUTE8,
			l_ATTRIBUTE9,
			l_ATTRIBUTE10,
			l_ATTRIBUTE11,
			l_ATTRIBUTE12,
			l_ATTRIBUTE13,
			l_ATTRIBUTE14,
			l_ATTRIBUTE15,
			l_SECURITY_GROUP_ID,
			l_PROCESS_TYPE,
			l_CONTROL_ACTIVITY_TYPE
    from amw_process
    where process_id = p_process_id
--ko need to sync with approved revision
--              and end_date is null;

Line: 5657

        update amw_process_organization
        set    RL_PROCESS_REV_ID             =   l_RL_PROCESS_REV_ID,
			   SIGNIFICANT_PROCESS_FLAG      =   l_SIGNIFICANT_PROCESS_FLAG,
			   STANDARD_PROCESS_FLAG	     =   l_STANDARD_PROCESS_FLAG,
			   PROCESS_CATEGORY_CODE	     =   l_PROCESS_CATEGORY,
			   STANDARD_VARIATION	         =   l_STANDARD_VARIATION,
			   ATTRIBUTE_CATEGORY            =   l_ATTRIBUTE_CATEGORY,
			   ATTRIBUTE1		             =   l_ATTRIBUTE1,
			   ATTRIBUTE2			         =   l_ATTRIBUTE2,
			   ATTRIBUTE3			         =   l_ATTRIBUTE3,
			   ATTRIBUTE4			         =   l_ATTRIBUTE4,
			   ATTRIBUTE5			         =   l_ATTRIBUTE5,
			   ATTRIBUTE6			         =   l_ATTRIBUTE6,
			   ATTRIBUTE7			         =   l_ATTRIBUTE7,
			   ATTRIBUTE8			         =   l_ATTRIBUTE8,
			   ATTRIBUTE9			         =   l_ATTRIBUTE9,
			   ATTRIBUTE10			         =   l_ATTRIBUTE10,
			   ATTRIBUTE11			         =   l_ATTRIBUTE11,
			   ATTRIBUTE12			         =   l_ATTRIBUTE12,
			   ATTRIBUTE13			         =   l_ATTRIBUTE13,
			   ATTRIBUTE14			         =   l_ATTRIBUTE14,
			   ATTRIBUTE15			         =   l_ATTRIBUTE15,
			   SECURITY_GROUP_ID		     =   l_SECURITY_GROUP_ID,
			   PROCESS_TYPE			         =   l_PROCESS_TYPE,
			   CONTROL_ACTIVITY_TYPE	     =   l_CONTROL_ACTIVITY_TYPE
        where organization_id = Org_Ids(indx)
        and process_id = p_process_id
        and end_date is null;

Line: 5689

 	--ko, delete all the unapproved rows
    FORALL indx IN Org_Ids.FIRST .. Org_Ids.LAST
        delete amw_acct_associations
        where pk1 = Org_Ids(indx)
        and   pk2 = p_process_id
        and   object_type = 'PROCESS_ORG'
        and   approval_date is null;

Line: 5699

        update amw_acct_associations
        set DELETION_DATE = sysdate
        where pk1 = Org_Ids(indx)
        and   pk2 = p_process_id
        and   object_type = 'PROCESS_ORG'
        and   deletion_date is null
        and   natural_account_id not in (select natural_account_id
        								 from   amw_acct_associations
        								 where  pk1 = p_process_id
        								 and    object_type = 'PROCESS'
        								 and    approval_date is not null
        								 and    deletion_approval_date is null);

Line: 5713

		insert into amw_acct_associations(
		ACCT_ASSOC_ID,
        NATURAL_ACCOUNT_ID,
        PK1,
        PK2,
        STATEMENT_ID,
        STATEMENT_LINE_ID,
        ORIG_SYSTEM_ACCT_VALUE,
        ASSOCIATION_CREATION_DATE,
        APPROVAL_DATE,
        DELETION_DATE,
        DELETION_APPROVAL_DATE,
        OBJECT_TYPE,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        OBJECT_VERSION_NUMBER)
        (select
        AMW_ACCT_ASSOCIATIONS_S.nextval,
        NATURAL_ACCOUNT_ID,
        Org_Ids(indx),
        PK1,
        STATEMENT_ID,
        STATEMENT_LINE_ID,
        ORIG_SYSTEM_ACCT_VALUE,
        sysdate,
        null,
        null,
        null,
        'PROCESS_ORG',
        sysdate,
        G_USER_ID,
        sysdate,
        G_USER_ID,
        G_LOGIN_ID,
        1
        from amw_acct_associations
        where PK1 = p_process_id
        and object_type = 'PROCESS'
        and approval_date is not null
        and deletion_approval_date is null
        and NATURAL_ACCOUNT_ID not in (select natural_account_id
                                      from amw_acct_associations
                                      where pk1 = Org_Ids(indx)
        							  and   pk2 = p_process_id
        							  and   object_type = 'PROCESS_ORG'
        							  and   deletion_date is null));

Line: 5770

            delete from amw_objective_associations
            where pk1 = Org_Ids(indx)
            and   pk2 = p_process_id
            and   object_type = 'PROCESS_ORG'
            and   approval_date is null;

Line: 5777

            update amw_objective_associations
            set deletion_date = sysdate
            where pk1 = Org_Ids(indx)
            and   pk2 = p_process_id
            and   object_type = 'PROCESS_ORG'
            and   deletion_date is null
            and PROCESS_OBJECTIVE_ID not in (select PROCESS_OBJECTIVE_ID
            								from amw_objective_associations
            								where pk1 = p_process_id
            								and object_type = 'PROCESS'
            								and approval_date is not null
            								and deletion_approval_date is null);

Line: 5791

            insert into amw_objective_associations
                (OBJECTIVE_ASSOCIATION_ID,
                PROCESS_OBJECTIVE_ID,
                PK1,
                PK2,
                ASSOCIATION_CREATION_DATE,
                APPROVAL_DATE,
                DELETION_DATE,
                DELETION_APPROVAL_DATE,
                OBJECT_TYPE,
                LAST_UPDATE_DATE,
                LAST_UPDATED_BY,
                CREATION_DATE,
                CREATED_BY,
                LAST_UPDATE_LOGIN,
                OBJECT_VERSION_NUMBER)
                (select
                AMW_OBJECTIVE_ASSOCIATIONS_S.nextval,
                PROCESS_OBJECTIVE_ID,
                Org_Ids(indx),
                PK1,
                sysdate,
                null,
                null,
                null,
                'PROCESS_ORG',
                sysdate,
                G_USER_ID,
                sysdate,
                G_USER_ID,
                G_LOGIN_ID,
                1
                from amw_objective_associations
                where PK1 = p_process_id
                and object_type = 'PROCESS'
                and approval_date is not null
                and deletion_approval_date is null
                and PROCESS_OBJECTIVE_ID not in (select PROCESS_OBJECTIVE_ID
                                                from amw_objective_associations
                                                where
                                                PK1 = Org_Ids(indx)
                                                and PK2 = p_process_id
                                                and object_type = 'PROCESS_ORG'
                                                and deletion_date is null)
                );

Line: 5849

		-- We Don't want the draft associations to linger in the table..So delete them..
        delete amw_risk_associations
        where pk1 = p_org_id
        and   pk2 = p_process_id
        and   object_type = 'PROCESS_ORG'
        and   approval_date is null;

Line: 5856

        update amw_risk_associations
        set DELETION_DATE = sysdate
        where pk1 = p_org_id
        and   pk2 = p_process_id
        and   object_type = 'PROCESS_ORG'
        and   deletion_date is null
        and  risk_id not in (select risk_id
        					 from amw_risk_associations
        					 where pk1 = p_process_id
        					 and object_type = 'PROCESS'
        					 and approval_date is not null
        					 and deletion_approval_date is null);

Line: 5868

		-- Now deleted all the risks that exists in org only but not in rl..Now copy all the risks that exists in rl only and not in org..
  		insert into amw_risk_associations
        (RISK_ASSOCIATION_ID,
        RISK_ID,
        PK1,
        PK2,
        RISK_LIKELIHOOD_CODE,
        RISK_IMPACT_CODE,
        MATERIAL,
        MATERIAL_VALUE,
        ASSOCIATION_CREATION_DATE,
        APPROVAL_DATE,
        DELETION_DATE,
        DELETION_APPROVAL_DATE,
        OBJECT_TYPE,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        OBJECT_VERSION_NUMBER)
        (select
        AMW_RISK_ASSOCIATIONS_S.nextval,
        RISK_ID,
        p_org_id,
        PK1,
        RISK_LIKELIHOOD_CODE,
        RISK_IMPACT_CODE,
        MATERIAL,
        MATERIAL_VALUE,
        sysdate,
        null,
        null,
        null,
        'PROCESS_ORG',
        sysdate,
        G_USER_ID,
        sysdate,
        G_USER_ID,
        G_LOGIN_ID,
        1
        from amw_risk_associations
        where PK1 = p_process_id
        and object_type = 'PROCESS'
        and approval_date is not null
        and deletion_approval_date is null
        and risk_id not in(select risk_id
                            from amw_risk_associations
                            where pk1 = p_org_id
        					and   pk2 = p_process_id
        					and   object_type = 'PROCESS_ORG'
        					and   deletion_date is null));

Line: 5922

        delete amw_control_associations
        where pk1 = p_org_id
        and   pk2 = p_process_id
        and   object_type = 'RISK_ORG'
        and   approval_date is null;

Line: 5928

        update amw_control_associations
        set DELETION_DATE = sysdate
        where pk1 = p_org_id
        and   pk2 = p_process_id
        and   object_type = 'RISK_ORG'
        and   deletion_date is null
        and   (pk3, control_id) not in (select pk2, control_id
        					 			from amw_control_associations
        					 			where pk1 = p_process_id
        					 			and object_type = 'RISK'
        					 			and approval_date is not null
        					 			and deletion_approval_date is null);

Line: 5940

    	insert into amw_control_associations
        (CONTROL_ASSOCIATION_ID,
        CONTROL_ID,
        PK1,
        PK2,
        PK3,
        ASSOCIATION_CREATION_DATE,
        APPROVAL_DATE,
        DELETION_DATE,
        DELETION_APPROVAL_DATE,
        OBJECT_TYPE,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        OBJECT_VERSION_NUMBER)
        (select
        AMW_CONTROL_ASSOCIATIONS_S.nextval,
        CONTROL_ID,
        p_org_id,
        PK1,
        PK2,
        sysdate,
        null,
        null,
        null,
        'RISK_ORG',
        sysdate,
        G_USER_ID,
        sysdate,
        G_USER_ID,
        G_LOGIN_ID,
        1
        from amw_control_associations
        where PK1 = p_process_id
        and object_type = 'RISK'
        and approval_date is not null
        and deletion_approval_date is null
        and (pk2, control_id) not in(select pk3,control_id
                            from amw_control_associations
                            where pk1 = p_org_id
        					and   pk2 = p_process_id
        					and   object_type = 'RISK_ORG'
        					and   deletion_date is null));

Line: 5986

		delete from amw_ap_associations
      	where pk1 = p_org_id
      	and   pk2 = p_process_id
      	and association_creation_date is null
      	and   object_type = 'CTRL_ORG';

Line: 5992

     	update amw_ap_associations
      	set DELETION_DATE = sysdate
      	where pk1 = p_org_id
      	and   pk2 = p_process_id
      	and   object_type = 'CTRL_ORG'
      	and   deletion_date is null
        and   (pk3, audit_procedure_id) not in (select pk1, audit_procedure_id
        					 			from amw_ap_associations
        					 			where object_type = 'CTRL'
        					 			and approval_date is not null
        					 			and deletion_approval_date is null);

Line: 6003

		insert into amw_ap_associations
        (AP_ASSOCIATION_ID,
        AUDIT_PROCEDURE_ID,
        PK1,
        PK2,
        PK3,
        DESIGN_EFFECTIVENESS,
        OP_EFFECTIVENESS,
        ASSOCIATION_CREATION_DATE,
        APPROVAL_DATE,
        DELETION_DATE,
        DELETION_APPROVAL_DATE,
        OBJECT_TYPE,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        OBJECT_VERSION_NUMBER)
        (select
        AMW_AP_ASSOCIATIONS_S.nextval,
        AUDIT_PROCEDURE_ID,
        p_org_id,
        p_process_id,
        PK1,
    --ko, the values are pk1 = org, pk2 = process, pk3 = control in the org context.    PK2,
        DESIGN_EFFECTIVENESS,
        OP_EFFECTIVENESS,
        null, --ko commenting.. we set association creation date upon approval of the process..sysdate,
        null,
        null,
        null,
        'CTRL_ORG',
        sysdate,
        G_USER_ID,
        sysdate,
        G_USER_ID,
        G_LOGIN_ID,
        1
        from amw_ap_associations
        where PK1 in --ko, replacing  = with in  controls can be more than one..
            (select distinct control_id
            from amw_control_associations
            where PK1 = p_process_id
            and object_type = 'RISK'
            and (APPROVAL_DATE is not null and APPROVAL_DATE <=  sysdate)
            and (DELETION_DATE is null or (DELETION_DATE is not null and DELETION_APPROVAL_DATE is null)))
        and object_type = 'CTRL'
        and approval_date is not null
        and deletion_approval_date is null
        and (pk1, audit_procedure_id) not in(select pk3,audit_procedure_id
                            from amw_ap_associations
                            where pk1 = p_org_id
        					and   pk2 = p_process_id
        					and   object_type = 'CTRL_ORG'
        					and   deletion_date is null));

Line: 6061

      	delete amw_objective_associations
        where pk1 = p_org_id
        and   pk2 = p_process_id
--        and   object_type  IN ('PROCESS_ORG','CONTROL_ORG') ...by dpatel
        and   object_type = 'CONTROL_ORG'
        and   approval_date is null;

Line: 6067

		-- UPDATE CONTROL OBJECTIVES....
        update amw_objective_associations
        set DELETION_DATE = sysdate
        where pk1 = p_org_id
        and   pk2 = p_process_id
        and   object_type  = 'CONTROL_ORG'
        and   deletion_date is null
        and  (pk3,pk4,process_objective_id) not in (select pk2,pk3, process_objective_id
        					 						from amw_objective_associations
        					 						where pk1 = p_process_id
        					 						and object_type = 'CONTROL'
        					 						and approval_date is not null
        					 						and deletion_approval_date is null);

Line: 6081

       	-- UPDATE PROCESS OBJECTIVES..
       	update amw_objective_associations
        set DELETION_DATE = sysdate
        where pk1 = p_org_id
        and   pk2 = p_process_id
        and   object_type  = 'PROCESS_ORG'
        and   deletion_date is null
        and  process_objective_id not in (select process_objective_id
        					 			  from amw_objective_associations
        					 			  where pk1 = p_process_id
        					 			  and object_type = 'PROCESS'
        					 			  and approval_date is not null
        					 			  and deletion_approval_date is null);

Line: 6094

		 -- INSERT PROCESS OBJECTIVES..
        insert into amw_objective_associations
        (OBJECTIVE_ASSOCIATION_ID,
        PROCESS_OBJECTIVE_ID,
        PK1,
        PK2,
        ASSOCIATION_CREATION_DATE,
        APPROVAL_DATE,
        DELETION_DATE,
        DELETION_APPROVAL_DATE,
        OBJECT_TYPE,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        OBJECT_VERSION_NUMBER)
        (select
        AMW_OBJECTIVE_ASSOCIATIONS_S.nextval,
        PROCESS_OBJECTIVE_ID,
        p_org_id,
        PK1,
        sysdate,
        null,
        null,
        null,
        'PROCESS_ORG',
        sysdate,
        G_USER_ID,
        sysdate,
        G_USER_ID,
        G_LOGIN_ID,
        1
        from amw_objective_associations
        where PK1 = p_process_id
        and object_type = 'PROCESS'
        and approval_date is not null
        and deletion_approval_date is null
        and process_objective_id not in(select process_objective_id
                            			from amw_objective_associations
                            			where pk1 = p_org_id
        								and   pk2 = p_process_id
        								and   object_type = 'PROCESS_ORG'
        								and   deletion_date is null));

Line: 6140

   	   insert into amw_objective_associations
       (OBJECTIVE_ASSOCIATION_ID,
       PROCESS_OBJECTIVE_ID,
       PK1,
       PK2,
       PK3,
       PK4,
       ASSOCIATION_CREATION_DATE,
       APPROVAL_DATE,
       DELETION_DATE,
       DELETION_APPROVAL_DATE,
       OBJECT_TYPE,
       LAST_UPDATE_DATE,
       LAST_UPDATED_BY,
       CREATION_DATE,
       CREATED_BY,
       LAST_UPDATE_LOGIN,
       OBJECT_VERSION_NUMBER)
       (select
       AMW_OBJECTIVE_ASSOCIATIONS_S.nextval,
       PROCESS_OBJECTIVE_ID,
       p_org_id,
       PK1,
       PK2,
       pk3,
       sysdate,
       null,
       null,
       null,
       'CONTROL_ORG',
       sysdate,
       G_USER_ID,
       sysdate,
       G_USER_ID,
       G_LOGIN_ID,
       1
       from amw_objective_associations
       where object_type = 'CONTROL'
       and pk1 = p_process_id
       and approval_date is not null
       and deletion_approval_date is null
       and  (pk2,pk3,process_objective_id) not in (select pk3,pk4, process_objective_id
        					 						from amw_objective_associations
        					 						where pk1 = p_org_id
        											and   pk2 = p_process_id
        											and   object_type  = 'CONTROL_ORG'
        					 						and   deletion_date is null));

Line: 6188

        update amw_objective_associations
        set DELETION_DATE = sysdate
        where pk1 = p_org_id
        and   pk2 = p_process_id
        and   object_type  = 'CONTROL_ORG'
        and   deletion_date is null
        and  process_objective_id not in (select process_objective_id
                            			from amw_objective_associations
                            			where pk1 = p_org_id
        								and   pk2 = p_process_id
        								and   object_type = 'PROCESS_ORG'
        								and   deletion_date is null);

Line: 6205

	    insert into amw_risk_associations
        (RISK_ASSOCIATION_ID,
        RISK_ID,
        PK1,
        PK2,
        RISK_LIKELIHOOD_CODE,
        RISK_IMPACT_CODE,
        MATERIAL,
        MATERIAL_VALUE,
        ASSOCIATION_CREATION_DATE,
        APPROVAL_DATE,
        DELETION_DATE,
        DELETION_APPROVAL_DATE,
        OBJECT_TYPE,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        OBJECT_VERSION_NUMBER)
        (select
        AMW_RISK_ASSOCIATIONS_S.nextval,
        RISK_ID,
        p_org_id,
        PK1,
        RISK_LIKELIHOOD_CODE,
        RISK_IMPACT_CODE,
        MATERIAL,
        MATERIAL_VALUE,
        sysdate,
        null,
        null,
        null,
        'PROCESS_ORG',
        sysdate,
        G_USER_ID,
        sysdate,
        G_USER_ID,
        G_LOGIN_ID,
        1
        from amw_risk_associations
        where PK1 = p_process_id
        and object_type = 'PROCESS'
        and approval_date is not null
        and deletion_approval_date is null
        and risk_id not in(select risk_id
                            from  amw_risk_associations
                            where pk1 = p_org_id
        					and   pk2 = p_process_id
        					and   object_type = 'PROCESS_ORG'
        					and   deletion_date is null));

Line: 6257

		insert into amw_control_associations
        (CONTROL_ASSOCIATION_ID,
        CONTROL_ID,
        PK1,
        PK2,
        PK3,
        ASSOCIATION_CREATION_DATE,
        APPROVAL_DATE,
        DELETION_DATE,
        DELETION_APPROVAL_DATE,
        OBJECT_TYPE,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        OBJECT_VERSION_NUMBER)
        (select
        AMW_CONTROL_ASSOCIATIONS_S.nextval,
        CONTROL_ID,
        p_org_id,
        PK1,
        PK2,
        sysdate,
        null,
        null,
        null,
        'RISK_ORG',
        sysdate,
        G_USER_ID,
        sysdate,
        G_USER_ID,
        G_LOGIN_ID,
        1
        from amw_control_associations
        where PK1 = p_process_id
        and object_type = 'RISK'
        and approval_date is not null
        and deletion_approval_date is null
        and (pk2, control_id) not in(select pk3,control_id
                            from amw_control_associations
                            where pk1 = p_org_id
        					and   pk2 = p_process_id
        					and   object_type = 'RISK_ORG'
        					and   deletion_date is null));

Line: 6303

        insert into amw_ap_associations
        (AP_ASSOCIATION_ID,
        AUDIT_PROCEDURE_ID,
        PK1,
        PK2,
        PK3,
        DESIGN_EFFECTIVENESS,
        OP_EFFECTIVENESS,
        ASSOCIATION_CREATION_DATE,
        APPROVAL_DATE,
        DELETION_DATE,
        DELETION_APPROVAL_DATE,
        OBJECT_TYPE,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        OBJECT_VERSION_NUMBER)
        (select
        AMW_AP_ASSOCIATIONS_S.nextval,
        AUDIT_PROCEDURE_ID,
        p_org_id,
        p_process_id,
        PK1,
    --ko, the values are pk1 = org, pk2 = process, pk3 = control in the org context.    PK2,
        DESIGN_EFFECTIVENESS,
        OP_EFFECTIVENESS,
        null, --ko commenting.. we set association creation date upon approval of the process..sysdate,
        null,
        null,
        null,
        'CTRL_ORG',
        sysdate,
        G_USER_ID,
        sysdate,
        G_USER_ID,
        G_LOGIN_ID,
        1
        from amw_ap_associations
        where PK1 in --ko, replacing  = with in  controls can be more than one..
            (select distinct control_id
            from amw_control_associations
            where PK1 = p_process_id
            and object_type = 'RISK'
            and (APPROVAL_DATE is not null and APPROVAL_DATE <=  sysdate)
            and (DELETION_DATE is null or (DELETION_DATE is not null and DELETION_APPROVAL_DATE is null)))
        and object_type = 'CTRL'
        and approval_date is not null
        and deletion_approval_date is null
        and (pk1, audit_procedure_id) not in(select pk3,audit_procedure_id
                            from amw_ap_associations
                            where pk1 = p_org_id
        					and   pk2 = p_process_id
        					and   object_type = 'CTRL_ORG'
        					and   deletion_date is null));

Line: 6360

		 -- INSERT PROCESS OBJECTIVES..
        insert into amw_objective_associations
        (OBJECTIVE_ASSOCIATION_ID,
        PROCESS_OBJECTIVE_ID,
        PK1,
        PK2,
        ASSOCIATION_CREATION_DATE,
        APPROVAL_DATE,
        DELETION_DATE,
        DELETION_APPROVAL_DATE,
        OBJECT_TYPE,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        OBJECT_VERSION_NUMBER)
        (select
        AMW_OBJECTIVE_ASSOCIATIONS_S.nextval,
        PROCESS_OBJECTIVE_ID,
        p_org_id,
        PK1,
        sysdate,
        null,
        null,
        null,
        'PROCESS_ORG',
        sysdate,
        G_USER_ID,
        sysdate,
        G_USER_ID,
        G_LOGIN_ID,
        1
        from amw_objective_associations
        where PK1 = p_process_id
        and object_type = 'PROCESS'
        and approval_date is not null
        and deletion_approval_date is null
        and process_objective_id not in(select process_objective_id
                            			from amw_objective_associations
                            			where pk1 = p_org_id
        								and   pk2 = p_process_id
        								and   object_type = 'PROCESS_ORG'
        								and   deletion_date is null));

Line: 6406

   	   insert into amw_objective_associations
       (OBJECTIVE_ASSOCIATION_ID,
       PROCESS_OBJECTIVE_ID,
       PK1,
       PK2,
       PK3,
       PK4,
       ASSOCIATION_CREATION_DATE,
       APPROVAL_DATE,
       DELETION_DATE,
       DELETION_APPROVAL_DATE,
       OBJECT_TYPE,
       LAST_UPDATE_DATE,
       LAST_UPDATED_BY,
       CREATION_DATE,
       CREATED_BY,
       LAST_UPDATE_LOGIN,
       OBJECT_VERSION_NUMBER)
       (select
       AMW_OBJECTIVE_ASSOCIATIONS_S.nextval,
       PROCESS_OBJECTIVE_ID,
       p_org_id,
       PK1,
       PK2,
       pk3,
       sysdate,
       null,
       null,
       null,
       'CONTROL_ORG',
       sysdate,
       G_USER_ID,
       sysdate,
       G_USER_ID,
       G_LOGIN_ID,
       1
       from amw_objective_associations
       where object_type = 'CONTROL'
       and pk1 = p_process_id
       and approval_date is not null
       and deletion_approval_date is null
       and  (pk2,pk3,process_objective_id) not in (select pk3,pk4, process_objective_id
        					 						from amw_objective_associations
        					 						where pk1 = p_org_id
        											and   pk2 = p_process_id
        											and   object_type  = 'CONTROL_ORG'
        					 						and   deletion_date is null)
	   and (pk2,pk3) not in (select pk3,pk4 from amw_objective_associations
        					 						where pk1 = p_org_id
        											and   pk2 = p_process_id
        											and   object_type  = 'CONTROL_ORG'
        					 						and   deletion_date is null)
        );

Line: 6460

        update amw_objective_associations
        set DELETION_DATE = sysdate
        where pk1 = p_org_id
        and   pk2 = p_process_id
        and   object_type  = 'CONTROL_ORG'
        and   deletion_date is null
        and  process_objective_id not in (select process_objective_id
                            			from amw_objective_associations
                            			where pk1 = p_org_id
        								and   pk2 = p_process_id
        								and   object_type = 'PROCESS_ORG'
        								and   deletion_date is null);

Line: 6489

		-- We Don't want the draft associations to linger in the table..So delete them..

        FORALL indx IN Org_Ids.FIRST .. Org_Ids.LAST
            delete amw_risk_associations
            where pk1 = Org_Ids(indx)
            and   pk2 = p_process_id
            and   object_type = 'PROCESS_ORG'
            and   approval_date is null;

Line: 6499

            update amw_risk_associations
            set DELETION_DATE = sysdate
            where pk1 = Org_Ids(indx)
            and   pk2 = p_process_id
            and   object_type = 'PROCESS_ORG'
            and   deletion_date is null
            and   risk_id not in (select risk_id
	 				              from amw_risk_associations
            					  where pk1 = p_process_id
        	       				  and object_type = 'PROCESS'
        	   	      			  and approval_date is not null
        		      			  and deletion_approval_date is null);

Line: 6511

		-- Now deleted all the risks that exists in org only but not in rl..Now copy all the risks that exists in rl only and not in org..
        FORALL indx IN Org_Ids.FIRST .. Org_Ids.LAST
  		    insert into amw_risk_associations
            (RISK_ASSOCIATION_ID,
            RISK_ID,
            PK1,
            PK2,
            RISK_LIKELIHOOD_CODE,
            RISK_IMPACT_CODE,
            MATERIAL,
            MATERIAL_VALUE,
            ASSOCIATION_CREATION_DATE,
            APPROVAL_DATE,
            DELETION_DATE,
            DELETION_APPROVAL_DATE,
            OBJECT_TYPE,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATE_LOGIN,
            OBJECT_VERSION_NUMBER)
            (select
            AMW_RISK_ASSOCIATIONS_S.nextval,
            RISK_ID,
            Org_Ids(indx),
            PK1,
            RISK_LIKELIHOOD_CODE,
            RISK_IMPACT_CODE,
            MATERIAL,
            MATERIAL_VALUE,
            sysdate,
            null,
            null,
            null,
            'PROCESS_ORG',
            sysdate,
            G_USER_ID,
            sysdate,
            G_USER_ID,
            G_LOGIN_ID,
            1
            from amw_risk_associations
            where PK1 = p_process_id
            and object_type = 'PROCESS'
            and approval_date is not null
            and deletion_approval_date is null
            and risk_id not in (select risk_id
                                from amw_risk_associations
                                where pk1 = Org_Ids(indx)
        		      			and   pk2 = p_process_id
        		  	     		and   object_type = 'PROCESS_ORG'
        			     		and   deletion_date is null));

Line: 6567

            delete  amw_control_associations
            where   pk1 = Org_Ids(indx)
            and     pk2 = p_process_id
            and     object_type = 'RISK_ORG'
            and     approval_date is null;

Line: 6574

            update amw_control_associations
            set DELETION_DATE = sysdate
            where pk1 = Org_Ids(indx)
            and   pk2 = p_process_id
            and   object_type = 'RISK_ORG'
            and   deletion_date is null
            and   (pk3, control_id) not in (select pk2, control_id
        					 			from amw_control_associations
        					 			where pk1 = p_process_id
        					 			and object_type = 'RISK'
        					 			and approval_date is not null
        					 			and deletion_approval_date is null);

Line: 6588

    	   insert into amw_control_associations
            (CONTROL_ASSOCIATION_ID,
            CONTROL_ID,
            PK1,
            PK2,
            PK3,
            ASSOCIATION_CREATION_DATE,
            APPROVAL_DATE,
            DELETION_DATE,
            DELETION_APPROVAL_DATE,
            OBJECT_TYPE,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATE_LOGIN,
            OBJECT_VERSION_NUMBER)
            (select
            AMW_CONTROL_ASSOCIATIONS_S.nextval,
            CONTROL_ID,
            Org_Ids(indx),
            PK1,
            PK2,
            sysdate,
            null,
            null,
            null,
            'RISK_ORG',
            sysdate,
            G_USER_ID,
            sysdate,
            G_USER_ID,
            G_LOGIN_ID,
            1
            from amw_control_associations
            where PK1 = p_process_id
            and object_type = 'RISK'
            and approval_date is not null
            and deletion_approval_date is null
            and (pk2, control_id) not in(select pk3,control_id
                            from amw_control_associations
                            where pk1 = Org_Ids(indx)
        					and   pk2 = p_process_id
        					and   object_type = 'RISK_ORG'
        					and   deletion_date is null));

Line: 6637

            delete from amw_ap_associations
            where pk1 = Org_Ids(indx)
            and   pk2 = p_process_id
            and   association_creation_date is null
            and   object_type = 'CTRL_ORG';

Line: 6644

            update amw_ap_associations
            set DELETION_DATE = sysdate
            where pk1 = Org_Ids(indx)
            and   pk2 = p_process_id
            and   object_type = 'CTRL_ORG'
            and   deletion_date is null
            and   (pk3, audit_procedure_id) not in (select pk1, audit_procedure_id
        					 			from amw_ap_associations
        					 			where object_type = 'CTRL'
        					 			and approval_date is not null
        					 			and deletion_approval_date is null);

Line: 6657

            insert into amw_ap_associations
            (AP_ASSOCIATION_ID,
            AUDIT_PROCEDURE_ID,
            PK1,
            PK2,
            PK3,
            DESIGN_EFFECTIVENESS,
            OP_EFFECTIVENESS,
            ASSOCIATION_CREATION_DATE,
            APPROVAL_DATE,
            DELETION_DATE,
            DELETION_APPROVAL_DATE,
            OBJECT_TYPE,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATE_LOGIN,
            OBJECT_VERSION_NUMBER)
            (select
            AMW_AP_ASSOCIATIONS_S.nextval,
            AUDIT_PROCEDURE_ID,
            Org_Ids(indx),
            p_process_id,
            PK1,
            --ko, the values are pk1 = org, pk2 = process, pk3 = control in the org context.    PK2,
            DESIGN_EFFECTIVENESS,
            OP_EFFECTIVENESS,
            null, --ko commenting.. we set association creation date upon approval of the process..sysdate,
            null,
            null,
            null,
            'CTRL_ORG',
            sysdate,
            G_USER_ID,
            sysdate,
            G_USER_ID,
            G_LOGIN_ID,
            1
            from amw_ap_associations
            where PK1 in --ko, replacing  = with in  controls can be more than one..
                (select distinct control_id
                from amw_control_associations
                where PK1 = p_process_id
                and object_type = 'RISK'
                and (APPROVAL_DATE is not null and APPROVAL_DATE <=  sysdate)
                and (DELETION_DATE is null or (DELETION_DATE is not null and DELETION_APPROVAL_DATE is null)))
                and object_type = 'CTRL'
                and approval_date is not null
                and deletion_approval_date is null
                and (pk1, audit_procedure_id) not in(select pk3,audit_procedure_id
                            from amw_ap_associations
                            where pk1 = Org_Ids(indx)
        					and   pk2 = p_process_id
        					and   object_type = 'CTRL_ORG'
        					and   deletion_date is null));

Line: 6716

          	delete amw_objective_associations
            where pk1 = Org_Ids(indx)
            and   pk2 = p_process_id
            and   object_type = 'CONTROL_ORG'
            and   approval_date is null;

Line: 6722

		  -- UPDATE CONTROL OBJECTIVES....
        FORALL indx IN Org_Ids.FIRST .. Org_Ids.LAST
            update amw_objective_associations
            set DELETION_DATE = sysdate
            where pk1 = Org_Ids(indx)
            and   pk2 = p_process_id
            and   object_type  = 'CONTROL_ORG'
            and   deletion_date is null
            and  (pk3,pk4,process_objective_id) not in (select pk2,pk3, process_objective_id
        					 						from amw_objective_associations
        					 						where pk1 = p_process_id
        					 						and object_type = 'CONTROL'
        					 						and approval_date is not null
        					 						and deletion_approval_date is null);

Line: 6739

   	       insert into amw_objective_associations
           (OBJECTIVE_ASSOCIATION_ID,
            PROCESS_OBJECTIVE_ID,
            PK1,
            PK2,
            PK3,
            PK4,
            ASSOCIATION_CREATION_DATE,
            APPROVAL_DATE,
            DELETION_DATE,
            DELETION_APPROVAL_DATE,
            OBJECT_TYPE,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATE_LOGIN,
            OBJECT_VERSION_NUMBER)
            (select
            AMW_OBJECTIVE_ASSOCIATIONS_S.nextval,
            PROCESS_OBJECTIVE_ID,
            Org_Ids(indx),
            PK1,
            PK2,
            pk3,
            sysdate,
            null,
            null,
            null,
            'CONTROL_ORG',
            sysdate,
            G_USER_ID,
            sysdate,
            G_USER_ID,
            G_LOGIN_ID,
            1
            from amw_objective_associations
            where object_type = 'CONTROL'
            and pk1 = p_process_id
            and approval_date is not null
            and deletion_approval_date is null
            and  (pk2,pk3,process_objective_id) not in (select pk3,pk4, process_objective_id
        					 						from amw_objective_associations
        					 						where pk1 = Org_Ids(indx)
        											and   pk2 = p_process_id
        											and   object_type  = 'CONTROL_ORG'
        					 						and   deletion_date is null));

Line: 6788

            update amw_objective_associations
            set DELETION_DATE = sysdate
            where pk1 = Org_Ids(indx)
            and   pk2 = p_process_id
            and   object_type  = 'CONTROL_ORG'
            and   deletion_date is null
            and  process_objective_id not in (select process_objective_id
                                			from amw_objective_associations
                                			where pk1 = Org_Ids(indx)
            								and   pk2 = p_process_id
            								and   object_type = 'PROCESS_ORG'
            								and   deletion_date is null);

Line: 6806

        insert into amw_risk_associations
        (RISK_ASSOCIATION_ID,
        RISK_ID,
        PK1,
        PK2,
        RISK_LIKELIHOOD_CODE,
        RISK_IMPACT_CODE,
        MATERIAL,
        MATERIAL_VALUE,
        ASSOCIATION_CREATION_DATE,
        APPROVAL_DATE,
        DELETION_DATE,
        DELETION_APPROVAL_DATE,
        OBJECT_TYPE,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        OBJECT_VERSION_NUMBER)
        (select
        AMW_RISK_ASSOCIATIONS_S.nextval,
        RISK_ID,
        Org_Ids(indx),
        PK1,
        RISK_LIKELIHOOD_CODE,
        RISK_IMPACT_CODE,
        MATERIAL,
        MATERIAL_VALUE,
        sysdate,
        null,
        null,
        null,
        'PROCESS_ORG',
        sysdate,
        G_USER_ID,
        sysdate,
        G_USER_ID,
        G_LOGIN_ID,
        1
        from amw_risk_associations
        where PK1 = p_process_id
        and object_type = 'PROCESS'
        and approval_date is not null
        and deletion_approval_date is null
        and risk_id not in (select risk_id
                            from  amw_risk_associations
                            where pk1 = Org_Ids(indx)
        					and   pk2 = p_process_id
        					and   object_type = 'PROCESS_ORG'
        					and   deletion_date is null));

Line: 6859

		insert into amw_control_associations
        (CONTROL_ASSOCIATION_ID,
        CONTROL_ID,
        PK1,
        PK2,
        PK3,
        ASSOCIATION_CREATION_DATE,
        APPROVAL_DATE,
        DELETION_DATE,
        DELETION_APPROVAL_DATE,
        OBJECT_TYPE,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        OBJECT_VERSION_NUMBER)
        (select
        AMW_CONTROL_ASSOCIATIONS_S.nextval,
        CONTROL_ID,
        Org_Ids(indx),
        PK1,
        PK2,
        sysdate,
        null,
        null,
        null,
        'RISK_ORG',
        sysdate,
        G_USER_ID,
        sysdate,
        G_USER_ID,
        G_LOGIN_ID,
        1
        from amw_control_associations
        where PK1 = p_process_id
        and object_type = 'RISK'
        and approval_date is not null
        and deletion_approval_date is null
        and (pk2, control_id) not in (select pk3,control_id
                            from amw_control_associations
                            where pk1 = Org_Ids(indx)
        					and   pk2 = p_process_id
        					and   object_type = 'RISK_ORG'
        					and   deletion_date is null));

Line: 6906

        insert into amw_ap_associations
        (AP_ASSOCIATION_ID,
        AUDIT_PROCEDURE_ID,
        PK1,
        PK2,
        PK3,
        DESIGN_EFFECTIVENESS,
        OP_EFFECTIVENESS,
        ASSOCIATION_CREATION_DATE,
        APPROVAL_DATE,
        DELETION_DATE,
        DELETION_APPROVAL_DATE,
        OBJECT_TYPE,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        OBJECT_VERSION_NUMBER)
        (select
        AMW_AP_ASSOCIATIONS_S.nextval,
        AUDIT_PROCEDURE_ID,
        Org_Ids(indx),
        p_process_id,
        PK1,
    --ko, the values are pk1 = org, pk2 = process, pk3 = control in the org context.    PK2,
        DESIGN_EFFECTIVENESS,
        OP_EFFECTIVENESS,
        null, --ko commenting.. we set association creation date upon approval of the process..sysdate,
        null,
        null,
        null,
        'CTRL_ORG',
        sysdate,
        G_USER_ID,
        sysdate,
        G_USER_ID,
        G_LOGIN_ID,
        1
        from amw_ap_associations
        where PK1 in --ko, replacing  = with in  controls can be more than one..
            (select distinct control_id
            from amw_control_associations
            where PK1 = p_process_id
            and object_type = 'RISK'
            and (APPROVAL_DATE is not null and APPROVAL_DATE <=  sysdate)
            and (DELETION_DATE is null or (DELETION_DATE is not null and DELETION_APPROVAL_DATE is null)))
        and object_type = 'CTRL'
        and approval_date is not null
        and deletion_approval_date is null
        and (pk1, audit_procedure_id) not in(select pk3,audit_procedure_id
                            from amw_ap_associations
                            where pk1 = Org_Ids(indx)
        					and   pk2 = p_process_id
        					and   object_type = 'CTRL_ORG'
        					and   deletion_date is null));

Line: 6964

       	   insert into amw_objective_associations
           (OBJECTIVE_ASSOCIATION_ID,
           PROCESS_OBJECTIVE_ID,
           PK1,
           PK2,
           PK3,
           PK4,
           ASSOCIATION_CREATION_DATE,
           APPROVAL_DATE,
           DELETION_DATE,
           DELETION_APPROVAL_DATE,
           OBJECT_TYPE,
           LAST_UPDATE_DATE,
           LAST_UPDATED_BY,
           CREATION_DATE,
           CREATED_BY,
           LAST_UPDATE_LOGIN,
           OBJECT_VERSION_NUMBER)
           (select
           AMW_OBJECTIVE_ASSOCIATIONS_S.nextval,
           PROCESS_OBJECTIVE_ID,
           Org_Ids(indx),
           PK1,
           PK2,
           pk3,
           sysdate,
           null,
           null,
           null,
           'CONTROL_ORG',
           sysdate,
           G_USER_ID,
           sysdate,
           G_USER_ID,
           G_LOGIN_ID,
           1
           from amw_objective_associations
           where object_type = 'CONTROL'
           and pk1 = p_process_id
           and approval_date is not null
           and deletion_approval_date is null
           and  (pk2,pk3,process_objective_id) not in (select pk3,pk4, process_objective_id
            					 						from amw_objective_associations
            					 						where pk1 = Org_Ids(indx)
            											and   pk2 = p_process_id
            											and   object_type  = 'CONTROL_ORG'
            					 						and   deletion_date is null)
    	   and (pk2,pk3) not in (select pk3,pk4 from amw_objective_associations
            					 						where pk1 = Org_Ids(indx)
            											and   pk2 = p_process_id
            											and   object_type  = 'CONTROL_ORG'
            					 						and   deletion_date is null)
            );

Line: 7018

            update amw_objective_associations
            set DELETION_DATE = sysdate
            where pk1 = Org_Ids(indx)
            and   pk2 = p_process_id
            and   object_type  = 'CONTROL_ORG'
            and   deletion_date is null
            and  process_objective_id not in (select process_objective_id
                                			from amw_objective_associations
                                			where pk1 = Org_Ids(indx)
            								and   pk2 = p_process_id
            								and   object_type = 'PROCESS_ORG'
            								and   deletion_date is null);

Line: 7043

   SELECT      TO_NUMBER(REPLACE(grants.grantee_key,'HZ_PARTY:','')) party_id,
        	   granted_menu.menu_name role_name,
        	   obj.obj_name object_name,
     		   granted_menu.menu_id menu_id,
		   grants.end_date end_date
         FROM fnd_grants grants,
             fnd_menus granted_menu,
             fnd_objects obj
         WHERE obj.obj_name = 'AMW_PROCESS_APPR_ETTY'
         AND   grants.object_id = obj.object_id
         AND   grants.grantee_type ='USER'
         AND   grantee_key like 'HZ_PARTY%'
         AND   NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
         AND   grants.menu_id = granted_menu.menu_id
         AND   grants.instance_type = 'INSTANCE'
         ---06.27.2005 npanandi: bug fix for ADS bug 4458414, passing to_char instead of num
		 ---AND   grants.instance_pk1_value = pid
		 AND   grants.instance_pk1_value = to_char(pid)
         AND   grants.instance_pk2_value = '*NULL*'
         AND   grants.instance_pk3_value = '*NULL*'
         AND   grants.instance_pk4_value = '*NULL*'
         AND   grants.instance_pk5_value = '*NULL*'
         and   granted_menu.menu_name in ('AMW_RL_PROC_OWNER_ROLE', 'AMW_RL_PROC_FINANCE_OWNER_ROLE', 'AMW_RL_PROC_APPL_OWNER_ROLE');

Line: 7068

   SELECT   grants.grant_guid grant_guid
   FROM 	fnd_grants grants,
        	fnd_menus granted_menu,
            fnd_objects obj
   		WHERE obj.obj_name = 'AMW_PROCESS_ORGANIZATION'
         AND   grants.object_id = obj.object_id
         AND   grants.grantee_type ='USER'
         AND   grantee_key like 'HZ_PARTY%'
         AND   NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
         AND   grants.menu_id = granted_menu.menu_id
         AND   grants.instance_type = 'INSTANCE'
         ---06.27.2005 npanandi: bug fix for ADS bug 4458414, passing to_char instead of num
         ---AND   grants.instance_pk1_value = org_id
		 AND   grants.instance_pk1_value = to_char(org_id)
		 ---06.27.2005 npanandi: bug fix for ADS bug 4458414, passing to_char instead of num
         ----AND   grants.instance_pk2_value = process_id
		 AND   grants.instance_pk2_value = to_char(process_id)
         AND   grants.instance_pk3_value = '*NULL*'
         AND   grants.instance_pk4_value = '*NULL*'
         AND   grants.instance_pk5_value = '*NULL*'
         and   granted_menu.menu_name = org_menu_name
         and (grants.grantee_key, obj.object_id ,grants.menu_id ) not in (select grants.grantee_key,obj.object_id, grants.menu_id
         	                                                              FROM fnd_grants grants,
         																	    fnd_menus granted_menu,
         																	    fnd_objects obj
         																	WHERE obj.obj_name = 'AMW_PROCESS_APPR_ETTY'
         																	AND   grants.object_id = obj.object_id
         																	AND   grants.grantee_type ='USER'
         																	AND   grantee_key like 'HZ_PARTY%'
         																	AND   NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
         																	AND   grants.menu_id = granted_menu.menu_id
         																	AND   grants.instance_type = 'INSTANCE'
         																	---06.27.2005 npanandi: bug fix for ADS bug 4458414, passing to_char instead of num
         																	---AND   grants.instance_pk1_value = process_id
																			AND   grants.instance_pk1_value = to_char(process_id)
         																	AND   grants.instance_pk2_value = '*NULL*'
         																	AND   grants.instance_pk3_value = '*NULL*'
         																	AND   grants.instance_pk4_value = '*NULL*'
         																	AND   grants.instance_pk5_value = '*NULL*'
         																	and   granted_menu.menu_name = rl_menu_name);

Line: 7268

    l_grant_guid.delete;

Line: 7270

    SELECT  grants.grant_guid grant_guid
    BULK COLLECT INTO l_grant_guid
    FROM 	fnd_grants grants,
            fnd_menus granted_menu,
            fnd_objects obj
    WHERE   obj.obj_name = 'AMW_PROCESS_ORGANIZATION'
    AND     grants.object_id = obj.object_id
    AND     grants.grantee_type ='USER'
    AND     grantee_key like 'HZ_PARTY%'
    AND     NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
    AND     grants.menu_id = granted_menu.menu_id
    AND     grants.instance_type = 'INSTANCE'
    AND     grants.instance_pk1_value = to_char(p_org_id)
    AND     grants.instance_pk2_value = to_char(p_process_id)
    AND     grants.instance_pk3_value = '*NULL*'
    AND     grants.instance_pk4_value = '*NULL*'
    AND     grants.instance_pk5_value = '*NULL*'
    and     granted_menu.menu_name = p_org_menu_name
    and     (grants.grantee_key) not in (   select  grants.grantee_key
                                            FROM    fnd_grants grants,
				                                    fnd_menus granted_menu,
                                                    fnd_objects obj
                                            WHERE obj.obj_name = 'AMW_PROCESS_APPR_ETTY'
         										AND   grants.object_id = obj.object_id
         										AND   grants.grantee_type ='USER'
         										AND   grantee_key like 'HZ_PARTY%'
         										AND   NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
         										AND   grants.menu_id = granted_menu.menu_id
         										AND   grants.instance_type = 'INSTANCE'
												AND   grants.instance_pk1_value = to_char(p_process_id)
         										AND   grants.instance_pk2_value = '*NULL*'
         										AND   grants.instance_pk3_value = '*NULL*'
         										AND   grants.instance_pk4_value = '*NULL*'
         										AND   grants.instance_pk5_value = '*NULL*'
         										and   granted_menu.menu_name = p_rl_menu_name);

Line: 7354

    l_party_id.delete;

Line: 7355

    l_end_date.delete;

Line: 7358

    SELECT  TO_NUMBER(REPLACE(grants.grantee_key,'HZ_PARTY:','')) party_id,
            grants.end_date end_date
    BULK COLLECT INTO   l_party_id,
                        l_end_date
    FROM    fnd_grants grants,
            fnd_menus granted_menu,
            fnd_objects obj
    WHERE   obj.obj_name = 'AMW_PROCESS_APPR_ETTY'
    AND     grants.object_id = obj.object_id
    AND     grants.grantee_type ='USER'
    AND     grantee_key like 'HZ_PARTY%'
    AND     NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
    AND     grants.menu_id = granted_menu.menu_id
    AND     grants.instance_type = 'INSTANCE'
    AND     grants.instance_pk1_value = to_char(p_process_id)
    AND     grants.instance_pk2_value = '*NULL*'
    AND     grants.instance_pk3_value = '*NULL*'
    AND     grants.instance_pk4_value = '*NULL*'
    AND     grants.instance_pk5_value = '*NULL*'
    and     granted_menu.menu_name = p_rl_menu_name
    and     (grants.grantee_key) not in (   select  grants.grantee_key
                                            FROM    fnd_grants grants,
         							                fnd_menus granted_menu,
                                                    fnd_objects obj
         							        WHERE   obj.obj_name = 'AMW_PROCESS_ORGANIZATION'
         							        AND     grants.object_id = obj.object_id
         							        AND     grants.grantee_type ='USER'
         							        AND     grantee_key like 'HZ_PARTY%'
         							        AND     NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
         							        AND     grants.menu_id = granted_menu.menu_id
         							        AND     grants.instance_type = 'INSTANCE'
                                            AND     grants.instance_pk1_value = to_char(p_org_id)
                                            AND     grants.instance_pk2_value = to_char(p_process_id)
         							        AND     grants.instance_pk3_value = '*NULL*'
         							        AND     grants.instance_pk4_value = '*NULL*'
         							        AND     grants.instance_pk5_value = '*NULL*'
         							        and     granted_menu.menu_name = p_org_menu_name );

Line: 7499

    select ah.child_id child_process_id
      from amw_approved_hierarchies ah
      where ah.parent_id = (select pp.process_id
                            from amw_process pp
                            where pp.process_id = ah.parent_id
                            and pp.approval_date is not null
                            and pp.approval_end_date is null
                            and pp.deletion_date is null)
       and ah.child_id  =  ( select Cp.process_id
                            from amw_process Cp
                            where Cp.process_id = ah.child_id
                            and Cp.approval_date is not null
                            and Cp.approval_end_date is null
                            and Cp.deletion_date is null)
       and ah.start_date is not null
       and ah.end_date is null
       and ah.organization_id = -1
       and ah.parent_id = l_pid;

Line: 7548

	SELECT 1 INTO l_dummy
	from amw_process_organization
	where organization_id = p_org_id
	and process_id = p_process_id
	and end_date is null
	and deletion_date is null;

Line: 7569

	select approval_status
        into l_approval_status
        from amw_process_organization
        where process_id = p_process_id
        and organization_id = p_org_id
        and end_date is null
        and  deletion_date is null;

Line: 7598

		    select process_org_rev_id into l_proc_latest
		    from amw_process_organization
                    where process_id = p_process_id
                    and organization_id = p_org_id
                    and end_date is null;

Line: 7604

                    select process_org_rev_id into l_proc_prev
	   	    from amw_process_organization
                    where process_id = p_process_id
                    and organization_id = p_org_id
                    and approval_date is not null
                    and approval_end_date is null;

Line: 7616

                   ,X_last_update_login => G_LOGIN_ID
                    );

Line: 7725

    select ah.child_id child_process_id
      from amw_approved_hierarchies ah
      where ah.parent_id = (select pp.process_id
                            from amw_process pp
                            where pp.process_id = ah.parent_id
                            and pp.approval_date is not null
                            and pp.approval_end_date is null
                            and pp.deletion_date is null)
       and ah.child_id  =  ( select Cp.process_id
                            from amw_process Cp
                            where Cp.process_id = ah.child_id
                            and Cp.approval_date is not null
                            and Cp.approval_end_date is null
                            and Cp.deletion_date is null)
       and ah.start_date is not null
       and ah.end_date is null
       and ah.organization_id = -1
       and ah.parent_id = l_pid;

Line: 7827

        select parent_child_id process_to_count
        from amw_proc_hierarchy_denorm
        where process_id = pid
        and up_down_ind = 'D'
        and hierarchy_type = 'A';

Line: 7856

select LTRIM(l_org_string, '1234567890') into str from dual;

Line: 7859

select SUBSTR(l_org_string, 1, diff) into orgstr from dual;

Line: 7872

  AMW_RL_HIERARCHY_PKG.update_denorm(p_org_id => orgid);

Line: 7882

select LTRIM(str, 'x') into l_org_string from dual;

Line: 7888

      		amw_rl_hierarchy_pkg.update_org_count(descendents_rec.process_to_count);

Line: 7923

select distinct organization_id
from amw_process_organization
where process_id = -2;

Line: 7936

			--updates latest hier denorm
			amw_rl_hierarchy_pkg.update_denorm (org_cursor.organization_id, sysdate);

Line: 7938

			--updates approved hier denorm
			amw_rl_hierarchy_pkg.update_approved_denorm (org_cursor.organization_id, sysdate);

Line: 7941

			update amw_process_organization
			set risk_count = null,
			control_count = null,
			risk_count_latest = null,
			control_count_latest = null
			where organization_id = org_cursor.organization_id;

Line: 7948

			-- update latest risk and control counts

			upd_ltst_risk_count(p_org_id => org_cursor.organization_id, p_process_id => null);

Line: 7954

			-- update latest risk and control counts

			upd_appr_risk_count(p_org_id => org_cursor.organization_id, p_process_id => null);

Line: 7964

		--updates latest hier denorm
		amw_rl_hierarchy_pkg.update_denorm (p_org_id, sysdate);

Line: 7966

		--updates approved hier denorm
		amw_rl_hierarchy_pkg.update_approved_denorm (p_org_id, sysdate);

Line: 7969

		update amw_process_organization
		set risk_count = null,
		control_count = null,
		risk_count_latest = null,
		control_count_latest = null
		where organization_id = p_org_id;

Line: 7976

		-- update latest risk and control counts

		upd_ltst_risk_count(p_org_id => p_org_id, p_process_id => null);

Line: 7982

		-- update latest risk and control counts

		upd_appr_risk_count(p_org_id => p_org_id, p_process_id => null);

Line: 7999

procedure delete_activities(p_parent_process_id in number,
						    p_organization_id in number,
			   				p_child_id_string in varchar2,
  						 	p_init_msg_list	IN VARCHAR2 := FND_API.G_FALSE,
	                        x_return_status out nocopy varchar2,
                            x_msg_count out nocopy number,
                            x_msg_data out nocopy varchar2)
is
  l_api_name constant varchar2(30) := 'delete_activities';

Line: 8032

    select LTRIM(l_child_string, '1234567890') into str from dual;

Line: 8037

    select SUBSTR(l_child_string, 1, diff) into childstr from dual;

Line: 8040

    delete from amw_latest_hierarchies
    where parent_id = p_parent_process_id
    and   child_id  = l_child_id
    and organization_id = p_organization_id;

Line: 8045

    select LTRIM(str, 'x') into l_child_string from dual;

Line: 8066

end delete_activities;

Line: 8101

    select LTRIM(l_child_string, '1234567890') into str from dual;

Line: 8106

    select SUBSTR(l_child_string, 1, diff) into childstr from dual;

Line: 8114

    insert into amw_latest_hierarchies
    (ORGANIZATION_ID, PARENT_ID, CHILD_ID, CHILD_ORDER_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY, object_version_number)
    VALUES
    (p_organization_id,p_parent_process_id,l_child_id,AMW_ORG_CHILD_ORDER_S.nextval, sysdate, G_USER_ID, G_LOGIN_ID, sysdate, G_USER_ID, 1)
    returning                CHILD_ORDER_NUMBER
    into                     l_child_order_num;

Line: 8121

    AMW_RL_HIERARCHY_PKG.update_appr_ch_ord_num_if_reqd
                (p_org_id      =>  p_organization_id,
                 p_parent_id   =>  p_parent_process_id,
                 p_child_id    =>  l_child_id,
                 p_instance_id =>  l_child_order_num);

Line: 8128

    select LTRIM(str, 'x') into l_child_string from dual;

Line: 8169

        select parent_child_id process_to_count
        from amw_proc_hierarchy_denorm
        where process_id = pid
        and up_down_ind = 'D'
        and hierarchy_type = 'A'
        union
        select pid process_to_count from dual;

Line: 8212

		 SELECT 1 INTO l_dummy
  		 FROM amw_process_organization
		 where process_id = l_child_id
		 and organization_id = p_organization_id
  		 and end_date is null
	  	 and deletion_date is null;

Line: 8228

			-- update the org counts of the child process and its hierarchy....
			for descendents_rec in c1(l_child_id) loop
    	  		exit when c1%notfound;

Line: 8231

    	  		amw_rl_hierarchy_pkg.update_org_count(descendents_rec.process_to_count);

Line: 8257

PROCEDURE update_latest_denorm_counts
( p_organization_id	    IN NUMBER,
  P_process_id		    IN NUMBER,
  p_commit		           IN VARCHAR2 := FND_API.G_FALSE,
  p_validation_level		   IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
  p_init_msg_list		   IN VARCHAR2 := FND_API.G_FALSE,
  x_return_status		   OUT NOCOPY VARCHAR2,
  x_msg_count			   OUT NOCOPY VARCHAR2,
  x_msg_data			   OUT NOCOPY VARCHAR2)
IS

  L_API_NAME CONSTANT VARCHAR2(30) := 'update_latest_denorm_counts';

Line: 8286

	AMW_RL_HIERARCHY_PKG.update_denorm(p_org_id => p_organization_id);

Line: 8312

END update_latest_denorm_counts;

Line: 8322

    Org_Ids.delete;

Line: 8353

        select parent_child_id process_to_count
        from amw_proc_hierarchy_denorm
        where process_id = pid
        and up_down_ind = 'D'
        and hierarchy_type = 'A'
        union
        select pid process_to_count from dual;

Line: 8362

        select parent_child_id process_to_count
        from amw_proc_hierarchy_denorm
        where process_id = pid
        and up_down_ind = 'D'
        and hierarchy_type = 'A'
        union
        select pid process_to_count from dual;

Line: 8392

        SELECT  DISTINCT aauv.organization_id
        BULK COLLECT INTO Org_Ids
        FROM    amw_audit_units_v aauv,
                amw_process_organization apo
        WHERE   aauv.organization_id=apo.organization_id
        AND     NVL( AAUV.DATE_TO,SYSDATE ) >= SYSDATE
        AND     apo.process_id=p_process_id
        AND     apo.end_date IS NULL
        AND     apo.deletion_date IS NULL
        AND     aauv.NAME >= p_org_range_from AND substr(aauv.NAME,0,length(p_org_range_to))<= p_org_range_to;

Line: 8404

        select  distinct aauv.organization_id
        BULK COLLECT INTO Org_Ids
        from    amw_audit_units_v aauv,
                amw_process_organization apo
        where aauv.organization_id=apo.organization_id
        and   NVL( AAUV.DATE_TO,SYSDATE ) >= SYSDATE
        and apo.process_id=p_process_id
        and apo.end_date is null
        and apo.deletion_date is null
        and (UPPER(aauv.NAME) LIKE UPPER(p_org_name || '%'));

Line: 8455

    	        --Ko Update the Proc_org_hierarchy_denorm tables..
                AMW_RL_HIERARCHY_PKG.update_denorm(p_org_id =>Org_Ids(indx));

Line: 8498

            update amw_process AP
            set AP.org_count = (select  COUNT(o.organization_id)
                                from    hr_all_organization_units o,
                                        hr_organization_information o2,
                                        amw_process_organization APO
                                WHERE   o.organization_id = o2.organization_id
                                AND     APO.organization_id = o.ORGANIZATION_ID
                                and     o2.org_information_context = 'CLASS'
                                and     o2.org_information1 = 'AMW_AUDIT_UNIT'
                                and     o2.org_information2 = 'Y'
                                AND    APO.process_id = AP.PROCESS_ID
                                and    APO.end_date is null
                                and    ( APO.deletion_date is null
                                or
                                    ( APO.deletion_date is not null and APO.approval_date is null)
                                    )
                                ),
                AP.object_version_number = AP.object_version_number + 1,
                AP.last_update_date = sysdate,
                AP.last_updated_by = G_USER_ID,
                AP.last_update_login = G_LOGIN_ID
            where AP.approval_date is not null
            and   AP.approval_end_date is null
            and   AP.process_id <> -1
            and   AP.process_id   IN (  select APHD.parent_child_id process_to_count
                                from   amw_proc_hierarchy_denorm APHD
                                where  APHD.process_id = p_process_id
                                and    APHD.up_down_ind = 'D'
                                and    APHD.hierarchy_type = 'A'
                                union
                                select p_process_id process_to_count from dual
                            );

Line: 8561

    select
      request_id into p_request_id
    from
          fnd_concurrent_requests
    where
          CONCURRENT_PROGRAM_ID = concur_prog_id
          and last_update_date = (select max(last_update_date) from fnd_concurrent_requests where CONCURRENT_PROGRAM_ID = concur_prog_id)
          and phase_code<>'C';

Line: 8577

    select
        concurrent_program_id into v_concurrent_program_id
    from
        fnd_concurrent_programs
    where CONCURRENT_PROGRAM_NAME = concur_prog_name;

Line: 8599

        select parent_child_id process_to_count
        from amw_proc_hierarchy_denorm
        where process_id = pid
        and up_down_ind = 'D'
        and hierarchy_type = 'A'
        union
        select pid process_to_count from dual;

Line: 8608

    select  aauv.organization_id,
    		name
    from    amw_audit_units_v aauv
    where   NVL( AAUV.DATE_TO,SYSDATE ) >= SYSDATE
    and     'Y' = AMW_UTILITY_PVT.IS_ORG_REGISTERED(aauv.ORGANIZATION_ID)
    and     aauv.organization_id not in(
                select distinct organization_id
                from amw_process_organization
                where process_id = pid
                and end_date is null
                and (
                    deletion_date is null or
                    (deletion_date is not null and approval_date is null)
                )
            )
    and (UPPER(NAME) LIKE UPPER(orgName));

Line: 8625

    select  aauv.organization_id,
    		name
    from    amw_audit_units_v aauv
    where   NVL( AAUV.DATE_TO,SYSDATE ) >= SYSDATE
    and     'Y' = AMW_UTILITY_PVT.IS_ORG_REGISTERED(aauv.ORGANIZATION_ID)
    and     aauv.organization_id not in(
                select distinct organization_id
                from amw_process_organization
                where process_id = pid
                and end_date is null
                and (
                    deletion_date is null or
                    (deletion_date is not null and approval_date is null)
                )
            )
    and NAME >= rangeFrom and substr(NAME,0,length(rangeTo))<= rangeTo;

Line: 8653

        select parent_child_id process_to_count
        from amw_proc_hierarchy_denorm
        where process_id = pid
        and up_down_ind = 'D'
        and hierarchy_type = 'A'
        union
        select pid process_to_count from dual;

Line: 8672

 select
  request_id, argument1, argument2, argument3, argument4
from
      fnd_concurrent_requests
where
      CONCURRENT_PROGRAM_ID = prog_id
      and last_update_date < (select max(last_update_date) from fnd_concurrent_requests where CONCURRENT_PROGRAM_ID = prog_id)
      and phase_code<>'C';

Line: 8773

			p_update_count		=> FND_API.G_FALSE,
			p_commit            => FND_API.G_FALSE,
			x_return_status		=> l_return_status,
			x_msg_count			=> l_msg_count,
			x_msg_data			=> l_msg_data);