APPS.WIP_SERIAL_ASSOC_VALIDATIONS SQL Statements

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

Line: 159

  procedure delete_serial(p_group_id              in number,
                          p_wip_entity_id         in number,
                          p_organization_id       in number,
                          p_substitution_type     in number) is
    l_ret_status VARCHAR2(10) := fnd_api.g_ret_sts_success;

Line: 168

                           p_substitution_type => WIP_JOB_DETAILS.WIP_DELETE,
                          x_return_status => l_ret_status);

Line: 175

                       p_substitution_type => WIP_JOB_DETAILS.WIP_DELETE,
                       x_return_status => l_ret_status);

Line: 183

                      p_substitution_type => WIP_JOB_DETAILS.WIP_DELETE,
                      x_return_status => l_ret_status);

Line: 192

                         p_substitution_type => WIP_JOB_DETAILS.WIP_DELETE,
                         x_return_status => l_ret_status);

Line: 195

  end delete_serial;

Line: 204

    select interface_id
      from wip_job_dtls_interface wjdi
     where wjdi.group_id = p_group_id
       and wjdi.process_phase = wip_constants.ml_validation
       and wjdi.process_status in (wip_constants.running,
                                   wip_constants.pending,
                                   wip_constants.warning)
       and wjdi.wip_entity_id = p_wip_entity_id
       and wjdi.organization_id = p_organization_id
       and wjdi.load_type = wip_job_details.wip_serial
       and wjdi.substitution_type = p_substitution_type
       and wjdi.serial_number_old is null;

Line: 235

      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.pending,
                               wip_constants.warning)
        and wip_entity_id = p_wip_entity_id
        and organization_id = p_organization_id
        and wjdi.load_type = wip_job_details.wip_serial
        and wjdi.substitution_type = p_substitution_type
        and wjdi.serial_number_old is null;

Line: 256

    select interface_id,
           we.primary_item_id,
           wjdi.serial_number_new,
           wjdi.rowid
      from wip_job_dtls_interface wjdi,
           wip_entities we
     where wjdi.group_id = p_group_id
       and wjdi.process_phase = wip_constants.ml_validation
       and wjdi.process_status in (wip_constants.running,
                                   wip_constants.pending,
                                   wip_constants.warning)
       and wjdi.wip_entity_id = p_wip_entity_id
       and wjdi.organization_id = p_organization_id
       and wjdi.load_type = wip_job_details.wip_serial
       and wjdi.substitution_type = p_substitution_type
       and wjdi.wip_entity_id = we.wip_entity_id
       and not exists(select 1
                        from mtl_serial_numbers
                       where serial_number = wjdi.serial_number_new
                         and inventory_item_id = we.primary_item_id
                         and current_organization_id = wjdi.organization_id);

Line: 311

        update wip_job_dtls_interface wjdi
          set process_status = wip_constants.error
          where rowid = l_ser_rec.rowid;

Line: 324

    select interface_id
      from wip_job_dtls_interface wjdi
     where wjdi.group_id = p_group_id
       and wjdi.process_phase = wip_constants.ml_validation
       and wjdi.process_status in (wip_constants.running,
                                   wip_constants.pending,
                                   wip_constants.warning)
       and wjdi.wip_entity_id = p_wip_entity_id
       and wjdi.organization_id = p_organization_id
       and wjdi.load_type = wip_job_details.wip_serial
       and wjdi.substitution_type = p_substitution_type
       and wjdi.serial_number_new is null;

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.pending,
                               wip_constants.warning)
        and wip_entity_id = p_wip_entity_id
        and organization_id = p_organization_id
        and wjdi.load_type = wip_job_details.wip_serial
        and wjdi.substitution_type = p_substitution_type
        and wjdi.serial_number_new is null;

Line: 376

    select interface_id
      from wip_job_dtls_interface wjdi
     where wjdi.group_id = p_group_id
       and wjdi.process_phase = wip_constants.ml_validation
       and wjdi.process_status in (wip_constants.running,
                                   wip_constants.pending,
                                   wip_constants.warning)
       and wjdi.wip_entity_id = p_wip_entity_id
       and wjdi.organization_id = p_organization_id
       and wjdi.load_type = wip_job_details.wip_serial
       and wjdi.substitution_type = p_substitution_type
       and not exists(select 1
                        from mtl_serial_numbers msn, wip_entities we
                       where msn.serial_number = wjdi.serial_number_new
                         and msn.current_organization_id = wjdi.organization_id
                         and msn.wip_entity_id is null
                         and msn.group_mark_id is null
                         and msn.current_status in (1,6) --defined not used /* Modified for Bug 5466955 */
                         and msn.inventory_item_id = we.primary_item_id
                         and we.wip_entity_id = p_wip_entity_id);

Line: 415

      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.pending,
                               wip_constants.warning)
        and wip_entity_id = p_wip_entity_id
        and organization_id = p_organization_id
        and wjdi.load_type = wip_job_details.wip_serial
        and wjdi.substitution_type = p_substitution_type
        and not exists(select 1
                         from mtl_serial_numbers msn, wip_entities we
                        where msn.serial_number = wjdi.serial_number_new
                          and msn.current_organization_id = wjdi.organization_id
                          and msn.wip_entity_id is null
                          and msn.group_mark_id is null
                          and msn.current_status in (1,6) --defined not used /* Modified for Bug 5466955 */
                          and msn.inventory_item_id = we.primary_item_id
                          and we.wip_entity_id = p_wip_entity_id);

Line: 446

    select interface_id
      from wip_job_dtls_interface wjdi
     where wjdi.group_id = p_group_id
       and wjdi.process_phase = wip_constants.ml_validation
       and wjdi.process_status in (wip_constants.running,
                                   wip_constants.pending,
                                   wip_constants.warning)
       and wjdi.wip_entity_id = p_wip_entity_id
       and wjdi.organization_id = p_organization_id
       and wjdi.load_type = wip_job_details.wip_serial
       and wjdi.substitution_type = p_substitution_type
       and not exists(select 1
                        from mtl_serial_numbers msn, wip_entities we
                       where msn.serial_number = wjdi.serial_number_old
                         and msn.current_organization_id = wjdi.organization_id
                         and msn.wip_entity_id = p_wip_entity_id
                         and msn.group_mark_id = p_wip_entity_id
                         and msn.operation_seq_num is null
                         and msn.current_status = 1 --defined not used
                         and msn.inventory_item_id = we.primary_item_id
                         and we.wip_entity_id = p_wip_entity_id);

Line: 486

      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.pending,
                               wip_constants.warning)
        and wip_entity_id = p_wip_entity_id
        and organization_id = p_organization_id
        and wjdi.load_type = wip_job_details.wip_serial
        and wjdi.substitution_type = p_substitution_type
        and not exists(select 1
                         from mtl_serial_numbers msn, wip_entities we
                        where msn.serial_number = wjdi.serial_number_old
                          and msn.current_organization_id = wjdi.organization_id
                          and msn.wip_entity_id = p_wip_entity_id
                          and msn.group_mark_id = p_wip_entity_id
                          and msn.operation_seq_num is null
                          and msn.current_status = 1 --defined not used
                          and msn.inventory_item_id = we.primary_item_id
                          and we.wip_entity_id = p_wip_entity_id);

Line: 517

    select interface_id
      from wip_job_dtls_interface wjdi
     where wjdi.group_id = p_group_id
       and wjdi.process_phase = wip_constants.ml_validation
       and wjdi.process_status in (wip_constants.running,
                                   wip_constants.pending,
                                   wip_constants.warning)
       and wjdi.wip_entity_id = p_wip_entity_id
       and wjdi.organization_id = p_organization_id
       and wjdi.substitution_type = p_substitution_type
       and wjdi.load_type = wip_job_details.wip_serial
       and not exists (select 1
                         from wip_job_schedule_interface wjsi
                        where wjsi.header_id = wjdi.parent_header_id
                          and wjsi.group_id = wjdi.group_id
                          and wjsi.load_type in (wip_constants.create_job,
                                                 wip_constants.create_ns_job,
                                                 wip_constants.resched_job));

Line: 554

      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.pending,
                               wip_constants.warning)
        and wip_entity_id = p_wip_entity_id
        and organization_id = p_organization_id
        and wjdi.substitution_type = p_substitution_type
        and wjdi.load_type = wip_job_details.wip_serial
        and not exists (select 1
                          from wip_job_schedule_interface wjsi
                         where wjsi.header_id = wjdi.parent_header_id
                           and wjsi.group_id = wjdi.group_id
                           and wjsi.load_type in (wip_constants.create_job,
                                                  wip_constants.create_ns_job,
                                                  wip_constants.resched_job));

Line: 581

    select interface_id
      from wip_job_dtls_interface wjdi
     where wjdi.group_id = p_group_id
       and wjdi.process_phase = wip_constants.ml_validation
       and wjdi.process_status in (wip_constants.running,
                                   wip_constants.pending,
                                   wip_constants.warning)
       and wjdi.wip_entity_id = p_wip_entity_id
       and wjdi.organization_id = p_organization_id
       and wjdi.substitution_type = p_substitution_type
       and wjdi.load_type = wip_job_details.wip_serial
       and not exists (select 1
                         from wip_job_schedule_interface wjsi, wip_discrete_jobs wdj
                        where wjsi.header_id = wjdi.parent_header_id
                          and wjsi.group_id = wjdi.group_id
                          and wjsi.wip_entity_id = wdj.wip_entity_id
                          and wdj.serialization_start_op is not null
                          and wdj.status_type in (wip_constants.unreleased,
                                                  wip_constants.released,
                                                  wip_constants.hold,
                                                  wip_constants.comp_chrg));

Line: 621

      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.pending,
                               wip_constants.warning)
        and wip_entity_id = p_wip_entity_id
        and organization_id = p_organization_id
        and wjdi.substitution_type = p_substitution_type
        and wjdi.load_type = wip_job_details.wip_serial
       and not exists (select 1
                         from wip_discrete_jobs wdj
                        where wjdi.wip_entity_id = wdj.wip_entity_id
                          and wdj.serialization_start_op is not null
                          and wdj.status_type in (wip_constants.unreleased,
                                                  wip_constants.released,
                                                  wip_constants.hold,
                                                  wip_constants.comp_chrg));