APPS.WIP_OPERATION_VALIDATE SQL Statements

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

Line: 176

    select interface_id
      from wip_job_dtls_interface
     where group_id = p_group_id
       and parent_header_id = p_parent_header_id /* Fix for Bug#3636378 */
       and process_phase = wip_constants.ml_validation  /* Bug 2751349 */
       and process_status in (wip_constants.running, wip_constants.warning)  /*  Bug 2751349 */
       and substitution_type = p_sub_type
       and load_type = wip_job_details.wip_operation
       and (   operation_seq_num is null
            or department_id is null
            or first_unit_start_date is null
            or first_unit_completion_date is null
            or last_unit_start_date is null
            or last_unit_completion_date is null
            or minimum_transfer_quantity is null
            or count_point_type is null
            or backflush_flag is null
           );

Line: 196

    select interface_id
      from wip_job_dtls_interface
     where group_id = p_group_id
       and parent_header_id = p_parent_header_id /* Fix for Bug#3636378 */
       and process_phase = wip_constants.ml_validation  /* Bug 2751349 */
       and process_status in (wip_constants.running, wip_constants.warning)  /*  Bug 2751349 */
       and substitution_type = p_sub_type
       and load_type = wip_job_details.wip_operation
       and operation_seq_num is null;

Line: 247

      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  /* Bug 2751349 */
         and process_status in (wip_constants.running, wip_constants.warning)  /*  Bug 2751349 */
         and substitution_type = p_sub_type
         and load_type = wip_job_details.wip_operation
         and (   operation_seq_num IS NULL
              OR department_id IS NULL
              OR first_unit_start_date IS NULL
              OR first_unit_completion_date IS NULL
              OR last_unit_start_date IS NULL
              OR last_unit_completion_date IS NULL
              OR minimum_transfer_quantity IS NULL
              OR count_point_type IS NULL
              OR backflush_flag IS NULL
             );

Line: 265

      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  /* Bug 2751349 */
         and process_status in (wip_constants.running, wip_constants.warning)  /*  Bug 2751349 */
         and substitution_type = p_sub_type
         and load_type = wip_job_details.wip_operation
         and operation_seq_num IS NULL;

Line: 288

    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 load_type = wip_job_details.wip_operation
       and substitution_type = p_sub_type
       and wip_entity_id = p_wip_entity_id
       and organization_id = p_organization_id
       and (   (    department_id is null --can not add w/null dept
                and p_sub_type = wip_job_details.wip_add
               )
            or (    department_id is not null
                and not exists (select 1
                                  from bom_departments
                                 where department_id = wjdi.department_id
                                   and organization_id = wjdi.organization_id)
               )
           );

Line: 327

    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 (   (    department_id is null
                and p_sub_type = wip_job_details.wip_add
               )
            or (    department_id is not null
                and not exists (select 1
                                  from bom_departments
                                 where department_id = wjdi.department_id
                                   and organization_id = wjdi.organization_id)
               )
           );

Line: 354

    select interface_id,
           operation_seq_num
      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 load_type = wip_job_details.wip_operation
       and substitution_type = wip_job_details.wip_change
       and wip_entity_id = p_wip_entity_id
       and organization_id = p_organization_id
       and department_id  is not null
       and exists (select 1
                   from  wip_operation_resources wor,
                         wip_operations wo
                   where wo.wip_entity_id = wjdi.wip_entity_id
                   and   wo.operation_seq_num = wjdi.operation_seq_num
                   and   wo.organization_id = wjdi.organization_id
                   and   wo.wip_entity_id = wor.wip_entity_id
                   and   wo.operation_seq_num = wor.operation_seq_num
                   and   wo.organization_id = wor.organization_id
                   and   nvl(wo.repetitive_schedule_id, 1) =
                         nvl(wor.repetitive_schedule_id, 1)
                   and   wo.department_id <> wjdi.department_id
                   ) ;

Line: 400

     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 load_type = wip_job_details.wip_operation
       and substitution_type = wip_job_details.wip_change
       and wip_entity_id = p_wip_entity_id
       and organization_id = p_organization_id
       and department_id  is not null
       and exists (select 1
                   from  wip_operation_resources wor,
                         wip_operations wo
                   where wo.wip_entity_id = wjdi.wip_entity_id
                   and   wo.operation_seq_num = wjdi.operation_seq_num
                   and   wo.organization_id = wjdi.organization_id
                   and   wo.wip_entity_id = wor.wip_entity_id
                   and   wo.operation_seq_num = wor.operation_seq_num
                   and   wo.organization_id = wor.organization_id
                   and   nvl(wo.repetitive_schedule_id, 1) =
                         nvl(wor.repetitive_schedule_id, 1)
                   and   wo.department_id <> wjdi.department_id
                   ) ;

Line: 434

    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 load_type = wip_job_details.wip_operation
       and substitution_type = p_sub_type
       and wip_entity_id = p_wip_entity_id
       and organization_id = p_organization_id
       and standard_operation_id is not null
       and standard_operation_id <> fnd_api.g_miss_num
       and not exists (select 1
                         from bom_standard_operations
                        where standard_operation_id = wjdi.standard_operation_id
                          and organization_id = wjdi.organization_id);

Line: 470

    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 load_type = wip_job_details.wip_operation
       and substitution_type = p_sub_type
       and wip_entity_id = p_wip_entity_id
       and organization_id = p_organization_id
       and standard_operation_id is not null
       and standard_operation_id <> fnd_api.g_miss_num
       and not exists (select 1
                         from bom_standard_operations
                        where standard_operation_id = wjdi.standard_operation_id
                          and organization_id = wjdi.organization_id);

Line: 497

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

Line: 530

    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 load_type = wip_job_details.wip_operation
       and substitution_type = p_sub_type
       and wip_entity_id = p_wip_entity_id
       and organization_id = p_organization_id
       and (   (    minimum_transfer_quantity is null
                and p_sub_type = wip_job_details.wip_add
               )
            or minimum_transfer_quantity < 0 );

Line: 555

    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 load_type = wip_job_details.wip_operation
       and substitution_type = p_sub_type
       and wip_entity_id = p_wip_entity_id
       and organization_id = p_organization_id
       and (   (    count_point_type is null
                and p_sub_type = wip_job_details.wip_add
               )
            or (    count_point_type is not null
                and not exists (select 1
                                  from mfg_lookups mfg_l
                                 where mfg_l.lookup_type = 'BOM_COUNT_POINT_TYPE'
                                   and mfg_l.lookup_code = wjdi.count_point_type )
               )
           );

Line: 595

    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 load_type = wip_job_details.wip_operation
       and substitution_type = p_sub_type
       and wip_entity_id = p_wip_entity_id
       and organization_id = p_organization_id
       and (   (    count_point_type is null
                and p_sub_type = wip_job_details.wip_add
               )
            or (    count_point_type is not null
                and not exists (select 1
                                  from mfg_lookups mfg_l
                                 where mfg_l.lookup_type = 'BOM_COUNT_POINT_TYPE'
                                   and mfg_l.lookup_code = wjdi.count_point_type )
               )
           );

Line: 627

    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 load_type = wip_job_details.wip_operation
       and substitution_type = p_sub_type
       and wip_entity_id = p_wip_entity_id
       and organization_id = p_organization_id
       and (   (    backflush_flag is null
                and p_sub_type = wip_job_details.wip_add
               )
            or (    backflush_flag is not null
                and not exists (select 1
                                  from mfg_lookups mfg_l
                                 where mfg_l.lookup_type = 'SYS_YES_NO'
                                   and mfg_l.lookup_code = wjdi.backflush_flag)
               )
           );

Line: 667

    update wip_job_dtls_interface wjdi
       set process_status = wip_constants.error
     where group_id = p_group_id
       and load_type = wip_job_details.wip_operation
       and substitution_type = p_sub_type
       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 (   (    backflush_flag is null
                and p_sub_type = wip_job_details.wip_add
               )
            or (    backflush_flag is not null
                and not exists (select 1
                                  from mfg_lookups mfg_l
                                 where mfg_l.lookup_type = 'SYS_YES_NO'
                                   and mfg_l.lookup_code = wjdi.backflush_flag)
               )
           );

Line: 700

    select interface_id, operation_seq_num
      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 load_type = wip_job_details.wip_operation
       and substitution_type = wip_job_details.wip_add
       and wip_entity_id = p_wip_entity_id
       and organization_id = p_organization_id
       and (exists (select 1
                     from wip_operations
                    where wip_entity_id = wjdi.wip_entity_id
                      and organization_id = wjdi.organization_id
                      and operation_seq_num = wjdi.operation_seq_num) or
             operation_seq_num <= 0);

Line: 742

    update wip_job_dtls_interface wjdi
       set process_status = wip_constants.error
     where group_id = p_group_id
       and load_type = wip_job_details.wip_operation
       and substitution_type = wip_job_details.wip_add
       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 (exists (select 1
                     from wip_operations
                    where wip_entity_id = wjdi.wip_entity_id
                      and organization_id = wjdi.organization_id
                      and operation_seq_num = wjdi.operation_seq_num)
	    or operation_seq_num <= 0);

Line: 769

    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 load_type = wip_job_details.wip_operation
       and substitution_type = wip_job_details.wip_add
       and wip_entity_id = p_wip_entity_id
       and organization_id = p_organization_id
       and parent_header_id = p_parent_header_id
       and (   wjdi.first_unit_start_date is null
            or wjdi.first_unit_completion_date is null
            or wjdi.last_unit_start_date is null
            or wjdi.last_unit_completion_date is null
/* bug3669728 begin */
            or wjdi.first_unit_start_date > wjdi.last_unit_start_date
            or wjdi.first_unit_completion_date > wjdi.last_unit_completion_date
            or wjdi.first_unit_start_date > wjdi.first_unit_completion_date
            or wjdi.last_unit_start_date > wjdi.last_unit_completion_date
/* bug3669728 end */
           );

Line: 794

    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 load_type = wip_job_details.wip_operation
       and substitution_type = wip_job_details.wip_add
       and wip_entity_id = p_wip_entity_id
       and organization_id = p_organization_id
       and (   first_unit_start_date is null
            or first_unit_completion_date is null
            or last_unit_start_date is null
            or last_unit_completion_date is null
/* bug3669728 begin */
            or wjdi.first_unit_start_date > wjdi.last_unit_start_date
            or wjdi.first_unit_completion_date > wjdi.last_unit_completion_date
            or wjdi.first_unit_start_date > wjdi.first_unit_completion_date
            or wjdi.last_unit_start_date > wjdi.last_unit_completion_date
/* bug3669728 end */
           );

Line: 858

      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 load_type = wip_job_details.wip_operation
         and substitution_type = wip_job_details.wip_add
         and wip_entity_id = p_wip_entity_id
         and organization_id = p_organization_id
         and (   wjdi.first_unit_start_date is null
              or wjdi.first_unit_completion_date is null
              or wjdi.last_unit_start_date is null
              or wjdi.last_unit_completion_date is null
/* bug3669728 begin */
              or wjdi.first_unit_start_date > wjdi.last_unit_start_date
              or wjdi.first_unit_completion_date > wjdi.last_unit_completion_date
              or wjdi.first_unit_start_date > wjdi.first_unit_completion_date
              or wjdi.last_unit_start_date > wjdi.last_unit_completion_date
/* bug3669728 end */
             );

Line: 880

      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 load_type = wip_job_details.wip_operation
         and substitution_type = wip_job_details.wip_add
         and wip_entity_id = p_wip_entity_id
         and organization_id = p_organization_id
         and parent_header_id = p_parent_header_id
         and (   wjdi.first_unit_start_date is null
              or wjdi.first_unit_completion_date is null
              or wjdi.last_unit_start_date is null
              or wjdi.last_unit_completion_date is null
/* bug3669728 begin */
              or wjdi.first_unit_start_date > wjdi.last_unit_start_date
              or wjdi.first_unit_completion_date > wjdi.last_unit_completion_date
              or wjdi.first_unit_start_date > wjdi.first_unit_completion_date
              or wjdi.last_unit_start_date > wjdi.last_unit_completion_date
/* bug3669728 end */
             );

Line: 917

    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 load_type = wip_job_details.wip_operation
       and substitution_type = wip_job_details.wip_change
       and wip_entity_id = p_wip_entity_id
       and not exists (select 1
                         from wip_operations
                        where wip_entity_id = wjdi.wip_entity_id
                          and organization_id = wjdi.organization_id
                          and operation_seq_num = wjdi.operation_seq_num);

Line: 951

    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 not exists (select 1
                         from wip_operations
                        where wip_entity_id = wjdi.wip_entity_id
                          and organization_id = wjdi.organization_id
                          and operation_seq_num = wjdi.operation_seq_num);

Line: 974

    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 load_type = wip_job_details.wip_operation
       and substitution_type = wip_job_details.wip_change
       and wip_entity_id = p_wip_entity_id
       and organization_id = p_organization_id
       and (   wjdi.last_unit_start_date is not null
            or wjdi.first_unit_start_date is not null
            or wjdi.first_unit_completion_date is not null
            or wjdi.last_unit_completion_date is not null
           )
       and exists (select 1
            from wip_operations wo
            where (
/* bug#3669728 begin */
                   (nvl(wjdi.first_unit_start_date, wo.first_unit_start_date)) >
                   (nvl(wjdi.last_unit_start_date, wo.last_unit_start_date))
                       or
                   (nvl(wjdi.first_unit_completion_date, wo.first_unit_completion_date)) >
                   (nvl(wjdi.last_unit_completion_date, wo.last_unit_completion_date))
                       or
                   (nvl(wjdi.first_unit_start_date, wo.first_unit_start_date)) >
                   (nvl(wjdi.first_unit_completion_date, wo.first_unit_completion_date))
                       or
                   (nvl(wjdi.last_unit_start_date, wo.last_unit_start_date)) >
                   (nvl(wjdi.last_unit_completion_date, wo.last_unit_completion_date))
/* bug#3669728 end */
                         )
                      and wip_entity_id = wjdi.wip_entity_id
                      and organization_id = wjdi.organization_id
                      and operation_seq_num = wjdi.operation_seq_num);

Line: 1029

    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 load_type = wip_job_details.wip_operation
       and substitution_type = wip_job_details.wip_change
       and wip_entity_id = p_wip_entity_id
       and organization_id = p_organization_id
       and (   wjdi.last_unit_start_date is not null
            or wjdi.first_unit_start_date is not null
            or wjdi.first_unit_completion_date is not null
            or wjdi.last_unit_completion_date is not null
           )
       and exists (select 1
              from wip_operations wo
              where (
/* bug#3669728 begin */
                   (nvl(wjdi.first_unit_start_date, wo.first_unit_start_date)) >
                   (nvl(wjdi.last_unit_start_date, wo.last_unit_start_date))
                       or
                   (nvl(wjdi.first_unit_completion_date, wo.first_unit_completion_date)) >
                   (nvl(wjdi.last_unit_completion_date, wo.last_unit_completion_date))
                       or
                   (nvl(wjdi.first_unit_start_date, wo.first_unit_start_date)) >
                   (nvl(wjdi.first_unit_completion_date, wo.first_unit_completion_date))
                       or
                   (nvl(wjdi.last_unit_start_date, wo.last_unit_start_date)) >
                   (nvl(wjdi.last_unit_completion_date, wo.last_unit_completion_date))
/* bug#3669728 end */
                         )
                      and wip_entity_id = wjdi.wip_entity_id
                      and organization_id = wjdi.organization_id
                      and operation_seq_num = wjdi.operation_seq_num);/*bug 3659006 */