eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.WIP_RES_USAGE_VALIDATE SQL Statements

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

Line: 19


      select resource_seq_num, parent_seq_num, operation_seq_num, resource_id_new, rowid
        from wip_job_dtls_interface wjdi
       where group_id = p_group_id
         and process_phase = wip_constants.ml_validation
         and process_status in (wip_constants.running,
                                wip_constants.warning)
         and wip_entity_id = p_wip_entity_id
         and organization_id = p_organization_id
         and load_type in (wip_job_details.wip_res_usage,
                           wip_job_details.wip_res_instance_usage)
         and substitution_type = wip_job_details.wip_add;

Line: 38


            select resource_seq_num into l_res_seq
	      from wip_operation_resources
	     where organization_id = p_organization_id
	       and wip_entity_id = p_wip_entity_id
	       and operation_seq_num = cur_row.operation_seq_num
	       and resource_id = cur_row.resource_id_new
	       and parent_resource_seq = cur_row.parent_seq_num;

Line: 46


        update wip_job_dtls_interface wjdi
           set resource_seq_num = l_res_seq
         where rowid = cur_row.rowid;

Line: 116


      select interface_id
        from wip_job_dtls_interface wjdi
       where group_id = p_group_id
         and process_phase = wip_constants.ml_validation
         and process_status in (wip_constants.running,
                                wip_constants.warning)
         and wip_entity_id = p_wip_entity_id
         and organization_id = p_organization_id
         and load_type in (wip_job_details.wip_res_usage,
                           wip_job_details.wip_res_instance_usage)
         and substitution_type = wip_job_details.wip_add
         and (resource_seq_num is null
             or (load_type = wip_job_details.wip_res_instance_usage
                 and resource_instance_id is null));

Line: 148


      update wip_job_dtls_interface wjdi
         set process_status = wip_constants.error
       where group_id = p_group_id
         and process_phase = wip_constants.ml_validation
         and process_status in (wip_constants.running,
                                wip_constants.warning)
         and wip_entity_id = p_wip_entity_id
         and organization_id = p_organization_id
         and load_type in (wip_job_details.wip_res_usage,
                           wip_job_details.wip_res_instance_usage)
         and substitution_type = wip_job_details.wip_add
         and (resource_seq_num is null
             or (load_type = wip_job_details.wip_res_instance_usage
                 and resource_instance_id is null));

Line: 168


      select interface_id
        from wip_job_dtls_interface wjdi
       where group_id = p_group_id
         and process_phase = wip_constants.ml_validation
         and process_status in (wip_constants.running,
                                wip_constants.warning)
         and wip_entity_id = p_wip_entity_id
         and organization_id = p_organization_id
         and substitution_type = wip_job_details.wip_add
         and ((load_type = wip_job_details.wip_res_instance_usage
               and  not exists (select 1
                           from wip_op_resource_instances
                          where wip_entity_id = wjdi.wip_entity_id
                            and organization_id = wjdi.organization_id
                            and operation_seq_num = wjdi.operation_seq_num
                            and resource_seq_num = wjdi.resource_seq_num
                            and instance_id = wjdi.resource_instance_id))
           or (load_type = wip_job_details.wip_res_usage
               and not exists (select 1
                           from wip_operation_resources
                          where wip_entity_id = wjdi.wip_entity_id
                            and organization_id = wjdi.organization_id
                            and operation_seq_num = wjdi.operation_seq_num
                            and resource_seq_num = wjdi.resource_seq_num)));

Line: 210


      update wip_job_dtls_interface wjdi
         set process_status = wip_constants.error
       where group_id = p_group_id
         and process_phase = wip_constants.ml_validation
         and process_status in (wip_constants.running,
                                wip_constants.warning)
         and wip_entity_id = p_wip_entity_id
         and organization_id = p_organization_id
         and substitution_type = wip_job_details.wip_add
         and ((load_type = wip_job_details.wip_res_instance_usage
               and  not exists (select 1
                           from wip_op_resource_instances
                          where wip_entity_id = wjdi.wip_entity_id
                            and organization_id = wjdi.organization_id
                            and operation_seq_num = wjdi.operation_seq_num
                            and resource_seq_num = wjdi.resource_seq_num
                            and instance_id = wjdi.resource_instance_id))
           or (load_type = wip_job_details.wip_res_usage
               and not exists (select 1
                           from wip_operation_resources
                          where wip_entity_id = wjdi.wip_entity_id
                            and organization_id = wjdi.organization_id
                            and operation_seq_num = wjdi.operation_seq_num
                            and resource_seq_num = wjdi.resource_seq_num)));

Line: 240


      select interface_id
        from wip_job_dtls_interface wjdi
       where group_id = p_group_id
         and process_phase = wip_constants.ml_validation
         and process_status in (wip_constants.running,
                                wip_constants.warning)
         and wip_entity_id = p_wip_entity_id
         and organization_id = p_organization_id
         and load_type in (wip_job_details.wip_res_usage,
                             wip_job_details.wip_res_instance_usage)
         and substitution_type = wip_job_details.wip_add
         and (   start_date is null
              or completion_date is null
    --Bug 5139799:Following 2 validations are added:
    -- 1.Start date of usage is greater than or equal to start date of the corresponding resource or instance.
    -- 2.Start and end dates of usage is in between start and end dates of corresponding resource or instance.
              or start_date > completion_date
              or exists (select 1
                         from   wip_operation_resources wor,wip_op_resource_instances wori
                         where  wor.wip_entity_id = p_wip_entity_id
                         and    wor.operation_seq_num = wjdi.operation_seq_num
                         and    wor.resource_seq_num  = wjdi.resource_seq_num
                         and    wor.wip_entity_id = wori.wip_entity_id(+)
                         and    wor.operation_seq_num = wori.operation_seq_num(+)
                         and    wor.resource_seq_num  = wori.resource_seq_num(+)
                         and    wjdi.resource_instance_id = wori.instance_id(+)
                         and   (nvl(wori.start_date,wor.start_date) > wjdi.start_date
	                        or  nvl(wori.completion_date,wor.completion_date)


Line: 289

      update wip_job_dtls_interface wjdi
         set process_status = wip_constants.error
       where group_id = p_group_id
         and process_phase = wip_constants.ml_validation
         and process_status in (wip_constants.running,
                                wip_constants.warning)
         and wip_entity_id = p_wip_entity_id
         and organization_id = p_organization_id
         and load_type in (wip_job_details.wip_res_usage,
                             wip_job_details.wip_res_instance_usage)
         and substitution_type = wip_job_details.wip_add
         and (   start_date is null
              or completion_date is null
    --Bug 5139799:Following 2 validations are added:
    -- 1.Start date of usage is greater than or equal to start date of the corresponding resource or instance.
    -- 2.Start and end dates of usage is in between start and end dates of corresponding resource or instance.
              or start_date > completion_date
              or exists (select 1
                         from   wip_operation_resources wor,wip_op_resource_instances wori
                         where  wor.wip_entity_id = p_wip_entity_id
                         and    wor.operation_seq_num = wjdi.operation_seq_num
                         and    wor.resource_seq_num  = wjdi.resource_seq_num
                         and    wor.wip_entity_id = wori.wip_entity_id(+)
                         and    wor.operation_seq_num = wori.operation_seq_num(+)
                         and    wor.resource_seq_num  = wori.resource_seq_num(+)
                         and    wjdi.resource_instance_id = wori.instance_id(+)
                         and   (nvl(wori.start_date,wor.start_date) > wjdi.start_date
                                or  nvl(wori.completion_date,wor.completion_date) 

Line: 322

      select interface_id
        from wip_job_dtls_interface wjdi
       where group_id = p_group_id
         and process_phase = wip_constants.ml_validation
         and process_status in (wip_constants.running,
                                wip_constants.warning)
         and wip_entity_id = p_wip_entity_id
         and organization_id = p_organization_id
         and load_type in (wip_job_details.wip_res_usage,
                             wip_job_details.wip_res_instance_usage)
         and substitution_type = wip_job_details.wip_add
         and (   assigned_units is null
              or assigned_units <= 0);

Line: 355

      update wip_job_dtls_interface wjdi
         set process_status = wip_constants.error
       where group_id = p_group_id
         and process_phase = wip_constants.ml_validation
         and process_status in (wip_constants.running,
                                wip_constants.warning)
         and wip_entity_id = p_wip_entity_id
         and organization_id = p_organization_id
         and load_type in (wip_job_details.wip_res_usage,
                             wip_job_details.wip_res_instance_usage)
         and substitution_type = wip_job_details.wip_add
         and (   assigned_units is null
              or assigned_units < 0);

Line: 374

      select interface_id
        from wip_job_dtls_interface wjdi
       where group_id = p_group_id
         and process_phase = wip_constants.ml_validation
         and process_status in (wip_constants.running,
                                wip_constants.warning)
         and wip_entity_id = p_wip_entity_id
         and organization_id = p_organization_id
         and load_type = wip_job_details.wip_res_instance_usage
         and substitution_type = wip_job_details.wip_add
         and exists (select 1
                       from wip_job_dtls_interface wjdi2
                      where wjdi2.group_id = wjdi.group_id
                        and wjdi2.wip_entity_id = p_wip_entity_id
                        and wjdi2.organization_id = p_organization_id
                        and wjdi2.operation_seq_num = wjdi.operation_seq_num
                        and wjdi2.resource_seq_num = wjdi.resource_seq_num
                        and wjdi2.load_type = wip_job_details.wip_res_instance_usage
                        and wjdi2.start_date < wjdi.start_date
                        and wjdi2.completion_date > wjdi.start_date);

Line: 414

      update wip_job_dtls_interface wjdi
         set process_status = wip_constants.error
       where group_id = p_group_id
         and process_phase = wip_constants.ml_validation
         and process_status in (wip_constants.running,
                                wip_constants.warning)
         and wip_entity_id = p_wip_entity_id
         and organization_id = p_organization_id
         and load_type = wip_job_details.wip_res_instance_usage
         and substitution_type = wip_job_details.wip_add
         and exists (select 1
                       from wip_job_dtls_interface wjdi2
                      where wjdi2.group_id = wjdi.group_id
                        and wjdi2.wip_entity_id = p_wip_entity_id
                        and wjdi2.organization_id = p_organization_id
                        and wjdi2.operation_seq_num = wjdi.operation_seq_num
                        and wjdi2.resource_seq_num = wjdi.resource_seq_num
                        and load_type = wip_job_details.wip_res_instance_usage
                        and wjdi2.rowid <> wjdi.rowid
                        and (   wjdi2.start_date between wjdi.start_date and wjdi.completion_date
                             or wjdi2.completion_date between wjdi.start_date and wjdi.completion_date));

Line: 442

      select interface_id
        from wip_job_dtls_interface wjdi
       where group_id = p_group_id
         and process_phase = wip_constants.ml_validation
         and process_status in (wip_constants.running,
                                wip_constants.warning)
         and wip_entity_id = p_wip_entity_id
         and organization_id = p_organization_id
         and load_type = wip_job_details.wip_res_usage
         and substitution_type = wip_job_details.wip_add
         and exists (select 1
                       from wip_job_dtls_interface wjdi2
                      where wjdi2.group_id = wjdi.group_id
                        and wjdi2.wip_entity_id = p_wip_entity_id
                        and wjdi2.organization_id = p_organization_id
                        and wjdi2.operation_seq_num = wjdi.operation_seq_num
                        and wjdi2.resource_seq_num = wjdi.resource_seq_num
                        and wjdi2.load_type = wip_job_details.wip_res_usage
                        and wjdi2.start_date < wjdi.start_date
                        and wjdi2.completion_date > wjdi.start_date);

Line: 482

      update wip_job_dtls_interface wjdi
         set process_status = wip_constants.error
       where group_id = p_group_id
         and process_phase = wip_constants.ml_validation
         and process_status in (wip_constants.running,
                                wip_constants.warning)
         and wip_entity_id = p_wip_entity_id
         and organization_id = p_organization_id
         and load_type = wip_job_details.wip_res_usage
         and substitution_type = wip_job_details.wip_add
         and exists (select 1
                       from wip_job_dtls_interface wjdi2
                      where wjdi2.group_id = wjdi.group_id
                        and wjdi2.wip_entity_id = p_wip_entity_id
                        and wjdi2.organization_id = p_organization_id
                        and wjdi2.operation_seq_num = wjdi.operation_seq_num
                        and wjdi2.resource_seq_num = wjdi.resource_seq_num
                        and load_type = wip_job_details.wip_res_usage
                        and wjdi2.rowid <> wjdi.rowid
                        and (   wjdi2.start_date between wjdi.start_date and wjdi.completion_date
                             or wjdi2.completion_date between wjdi.start_date and wjdi.completion_date));

Line: 510

      select interface_id
        from wip_job_dtls_interface wjdi
       where group_id = p_group_id
         and process_phase = wip_constants.ml_validation
         and process_status in (wip_constants.running,
                                wip_constants.warning)
         and wip_entity_id = p_wip_entity_id
         and organization_id = p_organization_id
         and load_type = wip_job_details.wip_res_usage
         and substitution_type = wip_job_details.wip_add
         and (   not exists (select 1
                               from wip_job_dtls_interface wjdi2, wip_operation_resources wor
                              where wjdi2.group_id = wjdi.group_id
                                and wjdi2.wip_entity_id = p_wip_entity_id
                                and wjdi2.organization_id = p_organization_id
                                and wjdi2.operation_seq_num =  wjdi.operation_seq_num
                                and wjdi2.resource_seq_num = wjdi.resource_seq_num
                                and wjdi2.load_type = wip_job_details.wip_res_usage
                                and wjdi2.substitution_type = wip_job_details.wip_add
                                and wor.wip_entity_id = p_wip_entity_id
                                and wor.organization_id = p_organization_id
                                and wor.operation_seq_num = wjdi2.operation_seq_num
                                and wor.resource_seq_num = wjdi2.resource_seq_num
                                and wor.start_date = wjdi2.start_date)
              or not exists (select 1
                               from wip_job_dtls_interface wjdi2, wip_operation_resources wor
                              where wjdi2.group_id = wjdi.group_id
                                and wjdi2.wip_entity_id = p_wip_entity_id
                                and wjdi2.organization_id = p_organization_id
                                and wjdi2.operation_seq_num =  wjdi.operation_seq_num
                                and wjdi2.resource_seq_num = wjdi.resource_seq_num
                                and wjdi2.load_type = wip_job_details.wip_res_usage
                                and wjdi2.substitution_type = wip_job_details.wip_add
                                and wor.wip_entity_id = p_wip_entity_id
                                and wor.organization_id = p_organization_id
                                and wor.operation_seq_num = wjdi2.operation_seq_num
                                and wor.resource_seq_num = wjdi2.resource_seq_num
                                and wor.completion_date = wjdi2.completion_date));

Line: 566

      update wip_job_dtls_interface wjdi
         set process_status = wip_constants.error
       where group_id = p_group_id
         and process_phase = wip_constants.ml_validation
         and process_status in (wip_constants.running,
                                wip_constants.warning)
         and wip_entity_id = p_wip_entity_id
         and organization_id = p_organization_id
         and load_type = wip_job_details.wip_res_usage
         and substitution_type = wip_job_details.wip_add
         and (   not exists (select 1
                               from wip_job_dtls_interface wjdi2, wip_operation_resources wor
                              where wjdi2.group_id = wjdi.group_id
                                and wjdi2.wip_entity_id = p_wip_entity_id
                                and wjdi2.organization_id = p_organization_id
                                and wjdi2.operation_seq_num =  wjdi.operation_seq_num
                                and wjdi2.resource_seq_num = wjdi.resource_seq_num
                                and wjdi2.load_type = wip_job_details.wip_res_usage
                                and wjdi2.substitution_type = wip_job_details.wip_add
                                and wor.wip_entity_id = p_wip_entity_id
                                and wor.organization_id = p_organization_id
                                and wor.operation_seq_num = wjdi2.operation_seq_num
                                and wor.resource_seq_num = wjdi2.resource_seq_num
                                and wor.start_date = wjdi2.start_date)
              or not exists (select 1
                               from wip_job_dtls_interface wjdi2, wip_operation_resources wor
                              where wjdi2.group_id = wjdi.group_id
                                and wjdi2.wip_entity_id = p_wip_entity_id
                                and wjdi2.organization_id = p_organization_id
                                and wjdi2.operation_seq_num =  wjdi.operation_seq_num
                                and wjdi2.resource_seq_num = wjdi.resource_seq_num
                                and wjdi2.load_type = wip_job_details.wip_res_usage
                                and wjdi2.substitution_type = wip_job_details.wip_add
                                and wor.wip_entity_id = p_wip_entity_id
                                and wor.organization_id = p_organization_id
                                and wor.operation_seq_num = wjdi2.operation_seq_num
                                and wor.resource_seq_num = wjdi2.resource_seq_num
                                and wor.completion_date = wjdi2.completion_date));