APPS.WIP_WS_EXCEPTIONS SQL Statements

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

Line: 19

    UPDATE
      WIP_EXCEPTIONS
    SET
      STATUS_TYPE = 2,
      LAST_UPDATE_DATE = SYSDATE,
      LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
      LAST_UPDATED_BY = FND_GLOBAL.USER_ID
    WHERE
      ORGANIZATION_ID = P_ORGANIZATION_ID AND
      WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
      STATUS_TYPE = 1;

Line: 57

    UPDATE
      WIP_EXCEPTIONS
    SET
      STATUS_TYPE = 2,
      LAST_UPDATE_DATE = SYSDATE,
      LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
      LAST_UPDATED_BY = FND_GLOBAL.USER_ID
    WHERE
      ORGANIZATION_ID = P_ORGANIZATION_ID AND
      WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
      OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
      STATUS_TYPE = 1;

Line: 99

    SELECT COUNT(*) INTO L_ROW_COUNT FROM
      WIP_OPERATIONS
    WHERE
      WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
      OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
      ORGANIZATION_ID = P_ORGANIZATION_ID AND
      NVL(DEPARTMENT_ID, '-9999') <> NVL(P_DEPARTMENT_ID, '-9999');

Line: 109

      UPDATE
        WIP_EXCEPTIONS
      SET
        STATUS_TYPE = 2,
        LAST_UPDATE_DATE = SYSDATE,
        LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
        LAST_UPDATED_BY = FND_GLOBAL.USER_ID
      WHERE
        ORGANIZATION_ID = P_ORGANIZATION_ID AND
        WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
        OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
        STATUS_TYPE = 1;

Line: 150

    UPDATE
      WIP_EXCEPTIONS
    SET
      STATUS_TYPE = 2,
      LAST_UPDATE_DATE = SYSDATE,
      LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
      LAST_UPDATED_BY = FND_GLOBAL.USER_ID
    WHERE
      ORGANIZATION_ID = P_ORGANIZATION_ID AND
      WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
      OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
      RESOURCE_SEQ_NUM = P_RESOURCE_SEQ_NUM AND
      STATUS_TYPE = 1;

Line: 194

    SELECT COUNT(*) INTO L_ROW_COUNT FROM
      WIP_OPERATION_RESOURCES
    WHERE
      WIP_ENTITY_ID     = P_WIP_ENTITY_ID AND
      OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
      RESOURCE_SEQ_NUM  = P_RESOURCE_SEQ_NUM AND
      ORGANIZATION_ID   = P_ORGANIZATION_ID AND
      RESOURCE_ID <> P_RESOURCE_ID;

Line: 205

      UPDATE
        WIP_EXCEPTIONS
      SET
        STATUS_TYPE = 2,
        LAST_UPDATE_DATE = SYSDATE,
        LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
        LAST_UPDATED_BY = FND_GLOBAL.USER_ID
      WHERE
        ORGANIZATION_ID = P_ORGANIZATION_ID AND
        WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
        OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
        RESOURCE_SEQ_NUM = P_RESOURCE_SEQ_NUM AND
        STATUS_TYPE = 1;

Line: 252

    SELECT COUNT(*) INTO L_ROW_COUNT FROM
      WIP_OPERATION_RESOURCES
    WHERE
      WIP_ENTITY_ID     = P_WIP_ENTITY_ID AND
      OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
      RESOURCE_SEQ_NUM  = P_RESOURCE_SEQ_NUM AND
      ORGANIZATION_ID   = P_ORGANIZATION_ID AND
        (RESOURCE_ID      <> P_RESOURCE_ID
        OR DEPARTMENT_ID  <>
              (SELECT DEPARTMENT_ID FROM BOM_DEPARTMENTS
                WHERE ORGANIZATION_ID = P_ORGANIZATION_ID AND
                DEPARTMENT_CODE = P_DEPARTMENT_CODE)
        );

Line: 268

      UPDATE
        WIP_EXCEPTIONS
      SET
        STATUS_TYPE = 2,
        LAST_UPDATE_DATE = SYSDATE,
        LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
        LAST_UPDATED_BY = FND_GLOBAL.USER_ID
      WHERE
        ORGANIZATION_ID = P_ORGANIZATION_ID AND
        WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
        OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
        RESOURCE_SEQ_NUM = P_RESOURCE_SEQ_NUM AND
        STATUS_TYPE = 1;

Line: 312

    UPDATE
      WIP_EXCEPTIONS
    SET
      STATUS_TYPE = 2,
      LAST_UPDATE_DATE = SYSDATE,
      LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
      LAST_UPDATED_BY = FND_GLOBAL.USER_ID
    WHERE
      ORGANIZATION_ID = P_ORGANIZATION_ID AND
      WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
      OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
      STATUS_TYPE = 1 AND
      RESOURCE_SEQ_NUM IN
      (
        SELECT
          RESOURCE_SEQ_NUM
        FROM
          WIP_OPERATION_RESOURCES WOR
        WHERE
          WOR.ORGANIZATION_ID = P_ORGANIZATION_ID AND
          WOR.WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
          WOR.OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
          WOR.SUBSTITUTE_GROUP_NUM = P_SUBSTITUTE_GROUP_NUM
        UNION
        SELECT
          RESOURCE_SEQ_NUM
        FROM
          WIP_SUB_OPERATION_RESOURCES WSOR
        WHERE
          WSOR.ORGANIZATION_ID = P_ORGANIZATION_ID AND
          WSOR.WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
          WSOR.OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
          WSOR.SUBSTITUTE_GROUP_NUM = P_SUBSTITUTE_GROUP_NUM
      );

Line: 360

  * when doing a res Instance delete.
  * Serial Number field is ALSO used.
  * for any Machine Instance.
  */
  FUNCTION close_exception_res_instance
  (
    p_wip_entity_id number,
    P_OPERATION_SEQ_NUM NUMBER,
    P_RESOURCE_SEQ_NUM NUMBER,
    P_INSTANCE_ID NUMBER,
    P_SERIAL_NUMBER VARCHAR2,
    P_ORGANIZATION_ID NUMBER
  ) RETURN BOOLEAN
  IS
    RETURN_STATUS BOOLEAN := TRUE;

Line: 380

    SELECT INVENTORY_ITEM_ID
    INTO  L_EQUIP_ITEM_ID
    FROM BOM_RESOURCE_EQUIPMENTS
    WHERE INSTANCE_ID = P_INSTANCE_ID;

Line: 388

    UPDATE
      WIP_EXCEPTIONS
    SET
      STATUS_TYPE = 2,
      LAST_UPDATE_DATE = SYSDATE,
      LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
      LAST_UPDATED_BY = FND_GLOBAL.USER_ID
    WHERE
      ORGANIZATION_ID = P_ORGANIZATION_ID AND
      WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
      OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
      RESOURCE_SEQ_NUM = P_RESOURCE_SEQ_NUM AND
      EQUIPMENT_ITEM_ID = L_EQUIP_ITEM_ID AND
      NVL(SERIAL_NUMBER, '-9999') = NVL(P_SERIAL_NUMBER, '-9999') AND
      STATUS_TYPE = 1;

Line: 416

  * Closes exception when a Res Instance is Updated.
  * Check if Serial_Number is changed, close exception.
  * Otherwise don't need to close.
  */
  function close_exp_res_instance_update
  (
    p_wip_entity_id number,
    p_operation_seq_num number,
    p_resource_seq_num number,
    p_instance_id number,
    p_serial_number varchar2,
    p_organization_id number
  ) return boolean
  IS
    RETURN_STATUS BOOLEAN := TRUE;

Line: 438

    SELECT COUNT(*) INTO L_ROW_COUNT
    FROM WIP_OP_RESOURCE_INSTANCES
    WHERE ORGANIZATION_ID = P_ORGANIZATION_ID AND
          WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
          OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
          RESOURCE_SEQ_NUM = P_RESOURCE_SEQ_NUM AND
          INSTANCE_ID = P_INSTANCE_ID AND
          SERIAL_NUMBER <> P_SERIAL_NUMBER;

Line: 453

      SELECT INVENTORY_ITEM_ID
      INTO  L_EQUIP_ITEM_ID
      FROM BOM_RESOURCE_EQUIPMENTS BRE
      WHERE INSTANCE_ID = P_INSTANCE_ID;

Line: 458

      SELECT SERIAL_NUMBER INTO L_SERIAL_NUMBER
      FROM WIP_OP_RESOURCE_INSTANCES
      WHERE ORGANIZATION_ID = P_ORGANIZATION_ID AND
            WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
            OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
            RESOURCE_SEQ_NUM = P_RESOURCE_SEQ_NUM AND
            INSTANCE_ID = P_INSTANCE_ID;

Line: 466

      UPDATE
        WIP_EXCEPTIONS
      SET
        STATUS_TYPE = 2,
        LAST_UPDATE_DATE = SYSDATE,
        LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
        LAST_UPDATED_BY = FND_GLOBAL.USER_ID
      WHERE
        ORGANIZATION_ID = P_ORGANIZATION_ID AND
        WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
        OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
        RESOURCE_SEQ_NUM = P_RESOURCE_SEQ_NUM AND
        EQUIPMENT_ITEM_ID = L_EQUIP_ITEM_ID AND
        NVL(SERIAL_NUMBER, '@@@@@') = NVL(L_SERIAL_NUMBER, '@@@@@') AND
        STATUS_TYPE = 1;

Line: 491

  END close_exp_res_instance_update;

Line: 511

    UPDATE
      WIP_EXCEPTIONS
    SET
      STATUS_TYPE = 2,
      LAST_UPDATE_DATE = SYSDATE,
      LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
      LAST_UPDATED_BY = FND_GLOBAL.USER_ID
    WHERE
      ORGANIZATION_ID = P_ORGANIZATION_ID AND
      WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
      OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM AND
      COMPONENT_ITEM_ID = P_COMPONENT_ITEM_ID AND
      STATUS_TYPE = 1;

Line: 549

    UPDATE
      WIP_EXCEPTIONS
    SET
      STATUS_TYPE = 2,
      LAST_UPDATE_DATE = SYSDATE,
      LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
      LAST_UPDATED_BY = FND_GLOBAL.USER_ID
    WHERE
      EXCEPTION_ID = P_EXCEPTION_ID AND
      STATUS_TYPE = 1;

Line: 572

  * Delete exception for a Job:Op combination.
  */
  function delete_exception_jobop
  (
    p_wip_entity_id number,
    p_operation_seq_num number,
    p_organization_id number
  ) return boolean
  IS
    RETURN_STATUS BOOLEAN := TRUE;

Line: 586

    DELETE FROM
      WIP_EXCEPTIONS
    WHERE
      ORGANIZATION_ID = P_ORGANIZATION_ID AND
      WIP_ENTITY_ID = P_WIP_ENTITY_ID AND
      OPERATION_SEQ_NUM = P_OPERATION_SEQ_NUM;

Line: 601

  END delete_exception_jobop;

Line: 605

  * Delete all exceptions for a Job.
  */
  function delete_exception_job
  (
    p_wip_entity_id number,
    p_organization_id number
  ) return boolean
  IS
    RETURN_STATUS BOOLEAN := TRUE;

Line: 618

    DELETE FROM
      WIP_EXCEPTIONS
    WHERE
      ORGANIZATION_ID = P_ORGANIZATION_ID AND
      WIP_ENTITY_ID = P_WIP_ENTITY_ID;

Line: 632

  END delete_exception_job;