APPS.AMW_PROC_APPROVAL_PKG SQL Statements

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

Line: 30

    update amw_process
    set approval_status = 'PA'
    where process_id = p_process_id
    and end_date is null;

Line: 44

    insert into amw_process_locks
    (organization_id,
    locking_process_id,
    locked_process_id,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    CREATION_DATE,
    CREATED_BY,
    object_version_number)
    values
    (
    -1,
    p_process_id,
    p_process_id,
    sysdate,
    G_USER_ID,
    G_LOGIN_ID,
    sysdate,
    G_USER_ID,
    1
    );

Line: 69

        insert into amw_process_locks
        (organization_id,
        locking_process_id,
        locked_process_id,
	LAST_UPDATE_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_LOGIN,
	CREATION_DATE,
	CREATED_BY,
	object_version_number)
        (select -1, p_process_id, parent_child_id, sysdate, G_USER_ID, G_LOGIN_ID,
sysdate, G_USER_ID, 1
        from amw_proc_hierarchy_denorm
        where process_id = p_process_id
        and up_down_ind = 'D'
        and hierarchy_type = 'L');

Line: 88

        insert into amw_process_locks
        (organization_id,
        locking_process_id,
        locked_process_id,
	LAST_UPDATE_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_LOGIN,
	CREATION_DATE,
	CREATED_BY,
	object_version_number)
        (select -1, p_process_id, child_process_id, sysdate, G_USER_ID, G_LOGIN_ID,
sysdate, G_USER_ID, 1
        from amw_latest_hierarchy_rl_v
        where parent_process_id = p_process_id);

Line: 112

        insert into amw_process_locks
        (organization_id,
        locking_process_id,
        locked_process_id,
	LAST_UPDATE_DATE,
	LAST_UPDATED_BY,
	LAST_UPDATE_LOGIN,
	CREATION_DATE,
	CREATED_BY,
	object_version_number)
        (select -1, p_process_id, parent_child_id,  sysdate, G_USER_ID, G_LOGIN_ID,
sysdate, G_USER_ID, 1
        from amw_proc_hierarchy_denorm
        where process_id = p_process_id
        and up_down_ind = 'D'
        and hierarchy_type = 'L');

Line: 146

 * ko .. We need to update only draft children
 * select parent_child_id
        from amw_proc_hierarchy_denorm
        where process_id = p_process_id
        and up_down_ind = 'D'
        and hierarchy_type = 'L';

Line: 153

	select ah.parent_child_id
        from amw_proc_hierarchy_denorm ah,
            amw_process ap
        where ah.process_id = p_process_id
        and ah.up_down_ind = 'D'
        and ah.hierarchy_type = 'L'
        and ah.parent_child_id = ap.process_id
        and ap.end_date is null
        and ap.approval_date is null;

Line: 169

    select approval_status
    into curr_app_status
    from amw_process
    where process_id = p_process_id
    and end_date is null;

Line: 183

    delete from amw_process_locks
    where organization_id = -1
    and locking_process_id = p_process_id;

Line: 187

    update amw_process
    set approval_status = 'A',
    approval_date = APPROV_TXN_DATE
    where process_id = p_process_id
    and end_date is null
    returning revision_number into rev_num;

Line: 195

        update amw_process
        set approval_end_date = APPROV_TXN_DATE
        where process_id = p_process_id
        and revision_number = (rev_num-1);

Line: 203

    AMW_RL_HIERARCHY_PKG.update_org_count(p_process_id => p_process_id);

Line: 212

                update amw_process
                set approval_status = 'A',
                approval_date = APPROV_TXN_DATE
                where process_id = c1_rec.parent_child_id
                and end_date is null
                returning revision_number into rev_num;

Line: 220

                    update amw_process
                    set approval_end_date = APPROV_TXN_DATE
                    where process_id = c1_rec.parent_child_id
                    and revision_number = (rev_num-1);

Line: 225

		--kosriniv ..Need to update the org count...
		AMW_RL_HIERARCHY_PKG.update_org_count(p_process_id => c1_rec.parent_child_id );

Line: 234

    amw_rl_hierarchy_pkg.update_approved_denorm(-1);

Line: 235

    amw_rl_hierarchy_pkg.update_appr_control_counts;

Line: 236

    amw_rl_hierarchy_pkg.update_appr_risk_counts;

Line: 246

    update amw_process
    set approval_status = 'D'
    where process_id = p_process_id
    and end_date is null;

Line: 251

    delete from amw_process_locks
    where organization_id = -1
    and locking_process_id = p_process_id;

Line: 268

        select 1 --parent_child_id, a.approval_status
        into l_dummy
        from amw_proc_hierarchy_denorm d, amw_process a
        where d.process_id = p_process_id
        and up_down_ind = 'D'
        and hierarchy_type = 'L'
        and a.process_id = d.parent_child_id
        and a.end_date is null
        and a.approval_status <> 'A';

Line: 304

    update amw_risk_associations
    set approval_date = APPROV_TXN_DATE
    where pk1 = p_process_id
    and object_type = 'PROCESS'
    and approval_date is null;

Line: 310

    update amw_risk_associations
    set deletion_approval_date = APPROV_TXN_DATE
    where pk1 = p_process_id
    and object_type = 'PROCESS'
    and deletion_date is not null
    and deletion_approval_date is null;

Line: 317

    update amw_control_associations
    set approval_date = APPROV_TXN_DATE
    where pk1 = p_process_id
    and object_type = 'RISK'
    and approval_date is null;

Line: 323

    update amw_control_associations
    set deletion_approval_date = APPROV_TXN_DATE
    where pk1 = p_process_id
    and object_type = 'RISK'
    and deletion_date is not null
    and deletion_approval_date is null;

Line: 330

    update amw_acct_associations
    set approval_date = APPROV_TXN_DATE
    where pk1 = p_process_id
    and object_type = 'PROCESS'
    and approval_date is null;

Line: 336

    update amw_acct_associations
    set deletion_approval_date = APPROV_TXN_DATE
    where pk1 = p_process_id
    and object_type = 'PROCESS'
    and deletion_date is not null
    and deletion_approval_date is null;

Line: 343

    update amw_objective_associations
    set approval_date = APPROV_TXN_DATE
    where pk1 = p_process_id
    and object_type in ('PROCESS', 'CONTROL')
    and approval_date is null;

Line: 349

    update amw_objective_associations
    set deletion_approval_date = APPROV_TXN_DATE
    where pk1 = p_process_id
    and object_type in ('PROCESS', 'CONTROL')
    and deletion_date is not null
    and deletion_approval_date is null;

Line: 356

    update amw_significant_elements
    set approval_date = APPROV_TXN_DATE
    where pk1 = p_process_id
    and object_type = 'PROCESS'
    and approval_date is null;

Line: 362

    update amw_significant_elements
    set deletion_approval_date = APPROV_TXN_DATE
    where pk1 = p_process_id
    and object_type = 'PROCESS'
    and deletion_date is not null
    and deletion_approval_date is null;

Line: 385

               (select parent_process_id,
			           child_process_id,
					   child_order_number
                  from (select *
				          from amw_latest_hierarchy_rl_v
						 where parent_approval_status = 'A'
						   and child_approval_status = 'A')
                  start with parent_process_id = -1
                connect by prior child_process_id = parent_process_id)
                MINUS
                (select parent_process_id,
				        child_process_id,
						child_order_number
				   from AMW_CURR_APP_HIERARCHY_RL_V);

Line: 403

			 (select parent_process_id,
			         child_process_id
			    from AMW_CURR_APP_HIERARCHY_RL_V
			   where parent_process_id = p_process_id)
			 MINUS
			 (select parent_process_id,
			         child_process_id
				from amw_latest_hierarchy_rl_v
			   where parent_process_id = p_process_id);

Line: 418

			 (select parent_process_id,
			         child_process_id
			    from AMW_CURR_APP_HIERARCHY_RL_V)
			 MINUS
			 (select parent_process_id,
			         child_process_id
				from amw_latest_hierarchy_rl_v);

Line: 431

			  (select parent_process_id,
			          child_process_id
				 from AMW_CURR_APP_HIERARCHY_RL_V
                where parent_process_id is not null)
			  MINUS
			  (select parent_process_id,
			          child_process_id
				 from AMW_CURR_APP_HIERARCHY_RL_V
			    start with parent_process_id = -1
			  connect by prior child_process_id = parent_process_id);

Line: 444

         insert into amw_approved_hierarchies(
		    organization_id,
			parent_id,
			child_id,
			start_date,
			child_order_number,
			LAST_UPDATE_DATE,
			LAST_UPDATED_BY,
			LAST_UPDATE_LOGIN,
			CREATION_DATE,
			CREATED_BY,
			object_version_number
		 )values(
		    -1,
			a_link.parent_process_id,
			a_link.child_process_id,
			APPROV_TXN_DATE,
			a_link.child_order_number,
			sysdate,
			G_USER_ID,
			G_LOGIN_ID,
			sysdate,
			G_USER_ID,
			1
		 );

Line: 474

		 update amw_approved_hierarchies
		    set end_date = APPROV_TXN_DATE,
		        object_version_number = object_version_number + 1
		  where organization_id = -1
		    and parent_id = defunct_link.parent_process_id
		    and child_id = defunct_link.child_process_id
		    and end_date is null;

Line: 486

         update amw_approved_hierarchies
			set end_date = APPROV_TXN_DATE,
			    object_version_number = object_version_number + 1
		  where organization_id = -1
			and parent_id = defunct_link.parent_process_id
			and child_id = defunct_link.child_process_id
			and end_date is null;

Line: 500

         insert into amw_approved_hierarchies(
		    organization_id,
			parent_id,
			child_id,
			start_date,
			child_order_number,
			LAST_UPDATE_DATE,
			LAST_UPDATED_BY,
			LAST_UPDATE_LOGIN,
			CREATION_DATE,
			CREATED_BY,
			object_version_number
		 )values(
		    -1,
			a_link.parent_process_id,
			a_link.child_process_id,
			APPROV_TXN_DATE,
			a_link.child_order_number,
			sysdate,
			G_USER_ID,
			G_LOGIN_ID,
			sysdate,
			G_USER_ID,
			1
		 );

Line: 528

		 update amw_approved_hierarchies
		    set end_date              = APPROV_TXN_DATE
		       ,object_version_number = object_version_number + 1
			   ,last_update_date      = sysdate
			   ,last_updated_by       = G_USER_ID
			   ,last_update_login     = G_LOGIN_ID
		  where organization_id = -1
		    and parent_id = defunct_link.parent_process_id
		    and child_id = defunct_link.child_process_id
		    and end_date is null;

Line: 541

         update amw_approved_hierarchies
			set end_date              = APPROV_TXN_DATE,
			    object_version_number = object_version_number + 1
			   ,last_update_date      = sysdate
			   ,last_updated_by       = G_USER_ID
			   ,last_update_login     = G_LOGIN_ID
		  where organization_id = -1
			and parent_id = defunct_link.parent_process_id
			and child_id = defunct_link.child_process_id
			and end_date is null;

Line: 563

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

Line: 572

    select risk_id from amw_risk_associations where pk1 = pid and object_type = 'PROCESS';

Line: 575

    select control_id from amw_control_associations where pk1 = pid and object_type = 'RISK';

Line: 593

                        select 1
                        into l_dummy
                        from amw_risks_b
                        where risk_id = ass_risks_rec.risk_id
                        and approval_status = 'A';

Line: 612

                        select 1
                        into l_dummy
                        from amw_controls_b
                        where control_id = ass_controls_rec.control_id
                        and approval_status = 'A';

Line: 632

                        select 1
                        into l_dummy
                        from amw_risks_b
                        where risk_id = ass_risks_rec.risk_id
                        and approval_status = 'A';

Line: 651

                        select 1
                        into l_dummy
                        from amw_controls_b
                        where control_id = ass_controls_rec.control_id
                        and approval_status = 'A';

Line: 758

        select 1 --parent_child_id, a.approval_status
        into l_dummy
        from amw_proc_hierarchy_denorm d, amw_process a
        where d.process_id = p_process_id
        and up_down_ind = 'D'
        and hierarchy_type = 'L'
        and a.process_id = d.parent_child_id
        and a.end_date is null
        and a.approval_status <> 'A';

Line: 886

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

Line: 907

        select a.standard_process_flag, b.process_id
        into std_process, st_var_pid
        from amw_process a, amw_process b
        where a.process_id = process_list_rec.parent_child_id
        and a.end_date is null
        and b.process_rev_id = a.standard_variation;

Line: 921

                select 1
                into l_dummy
                from amw_process
                where process_id = st_var_pid
                and approval_status = 'A';

Line: 942

    select a.standard_process_flag, b.process_id
    into std_process, st_var_pid
    from amw_process a, amw_process b
    where a.process_id = p_process_id
    and a.end_date is null
    and b.process_rev_id = a.standard_variation;

Line: 956

        select 1
        into l_dummy
        from amw_process
        where process_id = st_var_pid
        and approval_status = 'A';

Line: 986

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

Line: 1005

    select standard_process_flag,standard_variation
    into std_process,l_std_variation
    from amw_process
    where process_id = process_list_rec.parent_child_id
    and end_date is null;

Line: 1014

        select parent_child_id
        into l_dummy
        from amw_proc_hierarchy_denorm
        where process_id = process_list_rec.parent_child_id
        and up_down_ind = 'D'
        and hierarchy_type = 'L'
        and parent_child_id not in
            (select NON_STD_CHILD_ID
            from AMW_NONSTANDARD_VARIATIONS_B
            where NON_STD_PROCESS_ID = process_list_rec.parent_child_id
            and NON_STD_PROCESS_REV_NUM = (select revision_number
                                           from amw_process
                                           where process_id = process_list_rec.parent_child_id
                                           and end_date is null)
            and END_DATE is null);

Line: 1047

        select NON_STD_CHILD_ID
        into l_dummy
        from AMW_NONSTANDARD_VARIATIONS_B
        where NON_STD_PROCESS_ID = process_list_rec.parent_child_id
        and NON_STD_PROCESS_REV_NUM = (select revision_number
                                       from amw_process
                                       where process_id = process_list_rec.parent_child_id
                                       and end_date is null)
        and END_DATE is null
        and NON_STD_CHILD_ID not in
                (select parent_child_id
                from amw_proc_hierarchy_denorm
                where process_id = process_list_rec.parent_child_id
                and up_down_ind = 'D'
                and hierarchy_type = 'L');

Line: 1083

    select standard_process_flag,standard_variation
    into std_process,l_std_variation
    from amw_process
    where process_id = p_process_id
    and end_date is null;

Line: 1092

        select parent_child_id
        into l_dummy
        from amw_proc_hierarchy_denorm
        where process_id = p_process_id
        and up_down_ind = 'D'
        and hierarchy_type = 'L'
        and parent_child_id not in
            (select NON_STD_CHILD_ID
            from AMW_NONSTANDARD_VARIATIONS_B
            where NON_STD_PROCESS_ID = p_process_id
            and NON_STD_PROCESS_REV_NUM = (select revision_number
                                           from amw_process
                                           where process_id = p_process_id
                                           and end_date is null)
            and END_DATE is null);

Line: 1125

        select NON_STD_CHILD_ID
        into l_dummy
        from AMW_NONSTANDARD_VARIATIONS_B
        where NON_STD_PROCESS_ID = p_process_id
        and NON_STD_PROCESS_REV_NUM = (select revision_number
                                       from amw_process
                                       where process_id = p_process_id
                                       and end_date is null)
        and END_DATE is null
        and NON_STD_CHILD_ID not in
                (select parent_child_id
                from amw_proc_hierarchy_denorm
                where process_id = p_process_id
                and up_down_ind = 'D'
                and hierarchy_type = 'L');

Line: 1177

      select parent_child_id
        from amw_proc_hierarchy_denorm
       where process_id = p_process_id
         and up_down_ind = 'D'
         and hierarchy_type = 'L';

Line: 1191

   select approval_status
     into curr_app_status
     from amw_process
    where process_id = p_process_id
      and end_date is null;

Line: 1202

   delete from amw_process_locks
    where organization_id = -1
      and locking_process_id = p_process_id;

Line: 1207

   update amw_process
      set approval_status = 'A',
          approval_date = APPROV_TXN_DATE
    where process_id = p_process_id
      and end_date is null
   returning revision_number into rev_num;

Line: 1215

      update amw_process
         set approval_end_date = APPROV_TXN_DATE
       where process_id = p_process_id
         and revision_number = (rev_num-1);

Line: 1222

    AMW_RL_HIERARCHY_PKG.update_org_count(p_process_id => p_process_id);

Line: 1233

         update amw_process
            set approval_status = 'A',
                approval_date = APPROV_TXN_DATE
          where process_id = c1_rec.parent_child_id
            and end_date is null
         returning revision_number into rev_num;

Line: 1241

            update amw_process
               set approval_end_date = APPROV_TXN_DATE
             where process_id = c1_rec.parent_child_id
               and revision_number = (rev_num-1);

Line: 1247

	    --kosriniv ..Need to update the org count...
	 AMW_RL_HIERARCHY_PKG.update_org_count(p_process_id => c1_rec.parent_child_id);

Line: 1262

   amw_rl_hierarchy_pkg.update_approved_denorm(-1);

Line: 1263

   amw_rl_hierarchy_pkg.update_appr_control_counts;

Line: 1264

   amw_rl_hierarchy_pkg.update_appr_risk_counts;