APPS.CZ_CONFIG_API_PUB SQL Statements

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

Line: 52

    SELECT cz_config_hdrs_s.NEXTVAL
      INTO last_hdr_allocated
    FROM dual;

Line: 70

  SELECT MAX(CONFIG_REV_NBR) + 1  INTO l_config_rev_nbr
  FROM  CZ_CONFIG_HDRS
  WHERE  CONFIG_HDR_ID = p_config_hdr_id;

Line: 89

    SELECT cz_config_items_s.NEXTVAL
    INTO last_item_allocated
    FROM dual;

Line: 107

    SELECT cz_config_messages_s.NEXTVAL INTO last_msg_seq_allocated FROM dual;

Line: 126

  INSERT INTO CZ_CONFIG_HDRS
        (CONFIG_HDR_ID
        ,NAME
        ,CONFIG_REV_NBR
        ,COMPONENT_ID
        ,PERSISTENT_COMPONENT_ID
        ,DESC_TEXT
        ,UI_DEF_ID
        ,OPPORTUNITY_HDR_ID
        ,CONFIG_STATUS
        ,CONFIG_DATE_CREATED
        ,CONFIG_NOTE
        ,USER_ID_CREATED
        ,USER_ID_FOR_WHOM_CREATED
        ,NUMBER_QUOTES_USED_IN
        ,USER_NUM01
        ,USER_NUM02
        ,USER_NUM03
        ,USER_NUM04
        ,USER_STR01
        ,USER_STR02
        ,USER_STR03
        ,USER_STR04
        ,DELETED_FLAG
        ,SECURITY_MASK
        ,CHECKOUT_USER
        ,LAST_UPDATE_LOGIN
        ,MODEL_IDENTIFIER
        ,EFFECTIVE_DATE
        ,EFFECTIVE_USAGE_ID
        ,CONFIG_MODEL_LOOKUP_DATE
        ,CONFIG_DELTA_SPEC
        ,MODEL_INSTANTIATION_TYPE
        ,COMPONENT_INSTANCE_TYPE
        ,BASELINE_REV_NBR
        ,HAS_FAILURES
        ,MODEL_POST_MIGR_CHG_FLAG
        ,TO_BE_DELETED_FLAG
        ,AUTO_COMPLETION_FLAG
        ,CONFIG_ENGINE_TYPE
        )
    SELECT
         p_new_config_hdr_id  -- new or old value
        ,SUBSTR(DECODE(p_new_name, NULL, DECODE(NAME,NULL,NAME,DECODE(p_copy_mode,CZ_API_PUB.G_NEW_HEADER_COPY_MODE,
             NEW_HDR_NAME_PREFIX || NAME, NEW_REV_NAME_PREFIX || NAME)), p_new_name), 1, 240)
        ,p_new_config_rev_nbr -- new value
        ,COMPONENT_ID
        ,PERSISTENT_COMPONENT_ID
        ,DESC_TEXT
        ,UI_DEF_ID
        ,OPPORTUNITY_HDR_ID
        ,CONFIG_STATUS
        ,CONFIG_DATE_CREATED
        ,CONFIG_NOTE
        ,USER_ID_CREATED
        ,USER_ID_FOR_WHOM_CREATED
        ,NUMBER_QUOTES_USED_IN
        ,USER_NUM01
        ,USER_NUM02
        ,USER_NUM03
        ,USER_NUM04
        ,USER_STR01
        ,USER_STR02
        ,USER_STR03
        ,USER_STR04
        ,DELETED_FLAG
        ,SECURITY_MASK
        ,CHECKOUT_USER
        ,LAST_UPDATE_LOGIN
        ,MODEL_IDENTIFIER
        ,EFFECTIVE_DATE
        ,EFFECTIVE_USAGE_ID
        ,CONFIG_MODEL_LOOKUP_DATE
        ,CONFIG_DELTA_SPEC
        ,MODEL_INSTANTIATION_TYPE
        ,COMPONENT_INSTANCE_TYPE
        ,BASELINE_REV_NBR
        ,HAS_FAILURES
        ,MODEL_POST_MIGR_CHG_FLAG
        ,TO_BE_DELETED_FLAG
        ,AUTO_COMPLETION_FLAG
        ,CONFIG_ENGINE_TYPE
    FROM CZ_CONFIG_HDRS
    WHERE CONFIG_HDR_ID = p_old_config_hdr_id
      AND CONFIG_REV_NBR = p_old_config_rev_nbr;

Line: 233

  SELECT * BULK COLLECT INTO l_instance_hdr_tbl
  FROM cz_config_hdrs
  WHERE deleted_flag = '0' AND (config_hdr_id, config_rev_nbr) IN
       (SELECT instance_hdr_id, instance_rev_nbr
        FROM cz_config_items
        WHERE config_hdr_id = p_session_hdr_id AND config_rev_nbr = p_session_rev_nbr
        AND component_instance_type IN (GENERIC_INSTANCE_ROOT, NETWORK_INSTANCE_ROOT)
        AND deleted_flag = '0');

Line: 280

    INSERT INTO cz_config_hdrs VALUES l_instance_hdr_tbl(i);

Line: 297

  INSERT INTO CZ_CONFIG_ITEMS
                (LAST_UPDATE_LOGIN,
                 CONFIG_HDR_ID,
                 CONFIG_REV_NBR,
                 CONFIG_ITEM_ID,
                 PARENT_CONFIG_ITEM_ID,
                 PS_NODE_ID,
                 ITEM_VAL,
                 ITEM_NUM_VAL,
                 INSTANCE_NBR,
                 ROOT_BOM_CONFIG_ITEM_ID,
                 SEQUENCE_NBR,
                 VALUE_TYPE_CODE,
                 DELETED_FLAG,
                 SECURITY_MASK,
                 CHECKOUT_USER,
                 NODE_IDENTIFIER,
                 INVENTORY_ITEM_ID,
                 ORGANIZATION_ID,
                 COMPONENT_SEQUENCE_ID,
                 UOM_CODE,
                 BOM_SORT_ORDER,
                 QUOTEABLE_FLAG,
                 BOM_ITEM_TYPE,
                 TARGET_CONFIG_ITEM_ID,
                 INSTANCE_CONFIG_ITEM_ID,
                 NAME,
                 ATO_CONFIG_ITEM_ID
                ,INSTANCE_HDR_ID
                ,INSTANCE_REV_NBR
                ,LINE_TYPE
                ,COMPONENT_INSTANCE_TYPE
                ,TARGET_HDR_ID
                ,TARGET_REV_NBR
                ,CONFIG_DELTA
                ,LOCATION_ID
                ,LOCATION_TYPE_CODE
                ,IB_TRACKABLE
                ,EXT_ACTIVATED_FLAG
                ,DISCONTINUED_FLAG
                ,ORIG_SYS_REF
                ,ITEM_SRC_APPL_ID
                ,PS_NODE_NAME
                ,TANGIBLE_ITEM_FLAG
                ,RETURNED_FLAG
                ,VALUE_SOURCE
                ,ORDERABLE_FLAG
                )
  SELECT
                LAST_UPDATE_LOGIN,
                p_new_config_hdr_id,
                p_new_config_rev_nbr,
                CONFIG_ITEM_ID,
                PARENT_CONFIG_ITEM_ID,
                PS_NODE_ID,
                ITEM_VAL,
                ITEM_NUM_VAL,
                INSTANCE_NBR,
                ROOT_BOM_CONFIG_ITEM_ID,
                SEQUENCE_NBR,
                VALUE_TYPE_CODE,
                DELETED_FLAG,
                SECURITY_MASK,
                CHECKOUT_USER,
                NODE_IDENTIFIER,
                INVENTORY_ITEM_ID,
                ORGANIZATION_ID,
                COMPONENT_SEQUENCE_ID,
                UOM_CODE,
                BOM_SORT_ORDER,
                QUOTEABLE_FLAG,
                BOM_ITEM_TYPE,
                TARGET_CONFIG_ITEM_ID,
                INSTANCE_CONFIG_ITEM_ID,
                NAME,
                ATO_CONFIG_ITEM_ID
               ,p_new_config_hdr_id    -- instance_hdr_id
               ,p_new_config_rev_nbr   -- instance_rev_nbr
               ,LINE_TYPE
               ,COMPONENT_INSTANCE_TYPE
               ,DECODE(TARGET_HDR_ID, NULL, NULL, p_new_config_hdr_id)
               ,DECODE(TARGET_REV_NBR, NULL, NULL, p_new_config_rev_nbr)
               ,CONFIG_DELTA
               ,LOCATION_ID
               ,LOCATION_TYPE_CODE
               ,IB_TRACKABLE
               ,EXT_ACTIVATED_FLAG
               ,DISCONTINUED_FLAG
               ,ORIG_SYS_REF
               ,ITEM_SRC_APPL_ID
               ,PS_NODE_NAME
               ,TANGIBLE_ITEM_FLAG
               ,RETURNED_FLAG
               ,VALUE_SOURCE
               ,ORDERABLE_FLAG
    FROM CZ_CONFIG_ITEMS
    WHERE CONFIG_HDR_ID = p_old_config_hdr_id
      AND CONFIG_REV_NBR = p_old_config_rev_nbr AND deleted_flag = '0';

Line: 424

  CURSOR item_cursor IS  SELECT * FROM cz_config_items
                         WHERE config_hdr_id = p_old_config_hdr_id
                         AND config_rev_nbr = p_old_config_rev_nbr
                         AND deleted_flag = '0';

Line: 446

          FOR id_rec IN (SELECT config_item_id FROM cz_config_items
                         WHERE config_hdr_id = p_old_config_hdr_id
                         AND config_rev_nbr = p_old_config_rev_nbr
                         AND deleted_flag = '0' AND instance_hdr_id = l_index)
          LOOP
            x_item_id_map(id_rec.config_item_id) := get_next_item_id;

Line: 460

      l_config_item_tbl.DELETE;

Line: 535

        INSERT INTO cz_config_items VALUES l_config_item_tbl(i);

Line: 559

  CURSOR input_cursor IS SELECT *
                         FROM cz_config_inputs
                         WHERE config_hdr_id = p_old_config_hdr_id
                         AND config_rev_nbr = p_old_config_rev_nbr
                         AND deleted_flag = '0';

Line: 569

     INSERT INTO CZ_CONFIG_INPUTS
                    (LAST_UPDATE_LOGIN,
                     CONFIG_HDR_ID,
                     CONFIG_REV_NBR,
                     CONFIG_INPUT_ID,
                     INPUT_SEQ,
                     PARENT_INPUT_ID,
                     PS_NODE_ID,
                     INPUT_VAL,
                     INPUT_NUM_VAL,
                     INSTANCE_NBR,
                     INPUT_TYPE_CODE,
                     DELETED_FLAG,
                     SECURITY_MASK,
                     CHECKOUT_USER,
                     NODE_IDENTIFIER,
                     INSTANCE_ACTION_TYPE,
                     CONFIG_ITEM_ID,
                     TARGET_CONFIG_ITEM_ID,
                     TARGET_CONFIG_INPUT_ID,
                     FLOAT_TYPE,
                     INPUT_SOURCE)
     SELECT
                     LAST_UPDATE_LOGIN,
                     p_new_config_hdr_id,
                     p_new_config_rev_nbr,
                     CONFIG_INPUT_ID,
                     INPUT_SEQ,
                     PARENT_INPUT_ID,
                     PS_NODE_ID,
                     INPUT_VAL,
                     INPUT_NUM_VAL,
                     INSTANCE_NBR,
                     INPUT_TYPE_CODE,
                     DELETED_FLAG,
                     SECURITY_MASK,
                     CHECKOUT_USER,
                     NODE_IDENTIFIER,
                     INSTANCE_ACTION_TYPE,
                     CONFIG_ITEM_ID,
                     TARGET_CONFIG_ITEM_ID,
                     TARGET_CONFIG_INPUT_ID,
                     FLOAT_TYPE,
                     INPUT_SOURCE
         FROM CZ_CONFIG_INPUTS
         WHERE CONFIG_HDR_ID = p_old_config_hdr_id
          AND CONFIG_REV_NBR = p_old_config_rev_nbr
          AND deleted_flag = '0';

Line: 627

      l_config_input_tbl.DELETE;

Line: 643

        INSERT INTO cz_config_inputs VALUES l_config_input_tbl(i);

Line: 668

  CURSOR input_cursor IS SELECT *
                         FROM cz_config_inputs
                         WHERE config_hdr_id = p_old_config_hdr_id
                         AND config_rev_nbr = p_old_config_rev_nbr
                         AND deleted_flag = '0';

Line: 685

      l_config_input_tbl.DELETE;

Line: 717

        INSERT INTO cz_config_inputs VALUES l_config_input_tbl(i);

Line: 739

  CURSOR attr_cursor IS SELECT *
                        FROM cz_config_attributes
                        WHERE config_hdr_id = p_old_config_hdr_id
                        AND config_rev_nbr = p_old_config_rev_nbr
                        AND deleted_flag = '0';

Line: 747

    INSERT INTO CZ_CONFIG_ATTRIBUTES
                (CONFIG_HDR_ID,
                 CONFIG_REV_NBR,
                 CONFIG_ITEM_ID,
                 ATTRIBUTE_CATEGORY,
                 ATTRIBUTE1,
                 ATTRIBUTE2,
                 ATTRIBUTE3,
                 ATTRIBUTE4,
                 ATTRIBUTE5,
                 ATTRIBUTE6,
                 ATTRIBUTE7,
                 ATTRIBUTE8,
                 ATTRIBUTE9,
                 ATTRIBUTE10,
                 ATTRIBUTE11,
                 ATTRIBUTE12,
                 ATTRIBUTE13,
                 ATTRIBUTE14,
                 ATTRIBUTE15,
                 ATTRIBUTE16,
                 ATTRIBUTE17,
                 ATTRIBUTE18,
                 ATTRIBUTE19,
                 ATTRIBUTE20,
                 ATTRIBUTE21,
                 ATTRIBUTE22,
                 ATTRIBUTE23,
                 ATTRIBUTE24,
                 ATTRIBUTE25,
                 ATTRIBUTE26,
                 ATTRIBUTE27,
                 ATTRIBUTE28,
                 ATTRIBUTE29,
                 ATTRIBUTE30,
                 LAST_UPDATE_LOGIN )
    SELECT
                 p_new_config_hdr_id,
                 p_new_config_rev_nbr,
                 CONFIG_ITEM_ID,
                 ATTRIBUTE_CATEGORY,
                 ATTRIBUTE1,
                 ATTRIBUTE2,
                 ATTRIBUTE3,
                 ATTRIBUTE4,
                 ATTRIBUTE5,
                 ATTRIBUTE6,
                 ATTRIBUTE7,
                 ATTRIBUTE8,
                 ATTRIBUTE9,
                 ATTRIBUTE10,
                 ATTRIBUTE11,
                 ATTRIBUTE12,
                 ATTRIBUTE13,
                 ATTRIBUTE14,
                 ATTRIBUTE15,
                 ATTRIBUTE16,
                 ATTRIBUTE17,
                 ATTRIBUTE18,
                 ATTRIBUTE19,
                 ATTRIBUTE20,
                 ATTRIBUTE21,
                 ATTRIBUTE22,
                 ATTRIBUTE23,
                 ATTRIBUTE24,
                 ATTRIBUTE25,
                 ATTRIBUTE26,
                 ATTRIBUTE27,
                 ATTRIBUTE28,
                 ATTRIBUTE29,
                 ATTRIBUTE30,
                 LAST_UPDATE_LOGIN
    FROM CZ_CONFIG_ATTRIBUTES
    WHERE CONFIG_HDR_ID=p_old_config_hdr_id AND CONFIG_REV_NBR=p_old_config_rev_nbr
    AND deleted_flag = '0';

Line: 826

      l_config_attr_tbl.DELETE;

Line: 840

        INSERT INTO CZ_CONFIG_ATTRIBUTES VALUES l_config_attr_tbl(i);

Line: 861

      SELECT *
      FROM cz_config_ext_attributes
      WHERE deleted_flag = '0' AND (config_hdr_id, config_rev_nbr) IN
        (SELECT instance_hdr_id, instance_rev_nbr
         FROM cz_config_items
         WHERE config_hdr_id = p_old_sess_config_hdr_id
         AND config_rev_nbr = p_old_sess_config_rev_nbr
         AND component_instance_type = NETWORK_INSTANCE_ROOT
         AND deleted_flag = '0');

Line: 874

    l_ext_attr_tbl.DELETE;

Line: 888

      INSERT INTO CZ_CONFIG_EXT_ATTRIBUTES VALUES l_ext_attr_tbl(i);

Line: 914

  FOR msg_rec IN (SELECT * FROM cz_config_messages
                  WHERE config_hdr_id = p_old_config_hdr_id
                    AND config_rev_nbr = p_old_config_rev_nbr
                    AND deleted_flag = '0'
                  ORDER BY MESSAGE_SEQ)
  LOOP
    l_instance_hdr_id  := msg_rec.instance_hdr_id;

Line: 940

    INSERT INTO CZ_CONFIG_MESSAGES
            (MESSAGE_SEQ,
             CONFIG_HDR_ID,
             CONFIG_REV_NBR,
             CONSTRAINT_TYPE,
             MESSAGE,
             OVERRIDE,
             RULE_ID,
             PS_NODE_ID,
             LAST_UPDATE_LOGIN,
             SECURITY_MASK,
             CHECKOUT_USER,
             DELETED_FLAG,
             EFF_FROM,
             EFF_TO,
             EFF_MASK
            ,config_item_id
            ,instance_hdr_id
            ,instance_rev_nbr
           )
        VALUES
            (l_message_seq,
             p_new_config_hdr_id,
             p_new_config_rev_nbr,
             msg_rec.CONSTRAINT_TYPE,
             msg_rec.MESSAGE,
             msg_rec.OVERRIDE,
             msg_rec.RULE_ID,
             msg_rec.PS_NODE_ID,
             msg_rec.LAST_UPDATE_LOGIN,
             msg_rec.SECURITY_MASK,
             msg_rec.CHECKOUT_USER,
             msg_rec.DELETED_FLAG,
             msg_rec.EFF_FROM,
             msg_rec.EFF_TO,
             msg_rec.EFF_MASK
            ,l_config_item_id
            ,l_instance_hdr_id
            ,l_instance_rev_nbr
            );

Line: 1000

                            ,p_handle_deleted_flag  IN  VARCHAR2 := NULL
                            ,p_new_name             IN  VARCHAR2 := NULL
                            )
IS
  l_api_version  CONSTANT NUMBER := 1.0;

Line: 1009

  l_deleted_flag              cz_config_hdrs.DELETED_FLAG%TYPE;

Line: 1049

    SELECT model_instantiation_type, component_instance_type, deleted_flag
      INTO l_model_instantiation_type, l_component_instance_type, l_deleted_flag
    FROM   cz_config_hdrs
    WHERE  config_hdr_id  = p_config_hdr_id
      AND  config_rev_nbr = p_config_rev_nbr;

Line: 1062

  IF( l_deleted_flag = '1') THEN
    IF (p_handle_deleted_flag IS NULL) THEN
      fnd_message.set_name('CZ', 'CZ_CFG_COPY_DELETED_CONFIG');

Line: 1067

    ELSIF (p_handle_deleted_flag = '0') THEN
      UPDATE CZ_CONFIG_HDRS SET DELETED_FLAG = '0'
      WHERE CONFIG_HDR_ID = p_config_hdr_id
        AND CONFIG_REV_NBR = p_config_rev_nbr;

Line: 1224

             ,p_handle_deleted_flag  IN  VARCHAR2 := NULL
             ,p_new_name             IN  VARCHAR2 := NULL
      	)
IS
  PRAGMA AUTONOMOUS_TRANSACTION;

Line: 1244

                    ,p_handle_deleted_flag
                    ,p_new_name
                    );

Line: 1289

    SELECT component_instance_type, config_status
     INTO  l_component_instance_type, l_config_status
    FROM cz_config_hdrs
    WHERE config_hdr_id = p_config_hdr_id
     AND  config_rev_nbr = p_config_rev_nbr
     AND  deleted_flag = '0';

Line: 1313

      SELECT 1 INTO l_dummy
      FROM cz_config_messages
      WHERE config_hdr_id = p_config_hdr_id
       AND  config_rev_nbr = p_config_rev_nbr
       AND  deleted_flag = '0'
       AND ROWNUM < 2;

Line: 1359

    SELECT NVL(cz_utils.conv_num(value),DEFAULT_INCR) INTO id_increment
    FROM CZ_DB_SETTINGS
    WHERE section_name='SCHEMA' AND setting_id='OracleSequenceIncr';