The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cz_config_hdrs_s.NEXTVAL
INTO last_hdr_allocated
FROM dual;
SELECT MAX(CONFIG_REV_NBR) + 1 INTO l_config_rev_nbr
FROM CZ_CONFIG_HDRS
WHERE CONFIG_HDR_ID = p_config_hdr_id;
SELECT cz_config_items_s.NEXTVAL
INTO last_item_allocated
FROM dual;
SELECT cz_config_messages_s.NEXTVAL INTO last_msg_seq_allocated FROM dual;
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;
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');
INSERT INTO cz_config_hdrs VALUES l_instance_hdr_tbl(i);
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';
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';
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;
l_config_item_tbl.DELETE;
INSERT INTO cz_config_items VALUES l_config_item_tbl(i);
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';
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';
l_config_input_tbl.DELETE;
INSERT INTO cz_config_inputs VALUES l_config_input_tbl(i);
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';
l_config_input_tbl.DELETE;
INSERT INTO cz_config_inputs VALUES l_config_input_tbl(i);
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';
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';
l_config_attr_tbl.DELETE;
INSERT INTO CZ_CONFIG_ATTRIBUTES VALUES l_config_attr_tbl(i);
v_InsertString VARCHAR2(5000) := NULL;
SELECT column_name FROM all_tab_columns
WHERE table_name=UPPER(LTRIM(RTRIM(CustomerTable)))
AND owner=v_AppsSchema
AND column_name NOT IN ('CONFIG_HDR_ID','CONFIG_REV_NBR');
v_InsertString := ' INSERT INTO '||p_table_name||' ( ';
v_InsertString := v_InsertString ||'CONFIG_HDR_ID, CONFIG_REV_NBR, '||v_ListColumns||' )';
v_InsertString := v_InsertString ||' SELECT :1, :2, '|| v_ListColumns;
v_InsertString := v_InsertString ||' FROM '||p_table_name ;
v_InsertString := v_InsertString ||' WHERE CONFIG_HDR_ID = :3' ;
v_InsertString := v_InsertString ||' AND CONFIG_REV_NBR= :4' ;
EXECUTE IMMEDIATE v_InsertString USING
p_new_config_hdr_id,
p_new_config_rev_nbr,
p_old_config_hdr_id,
p_old_config_rev_nbr;
SELECT UPPER(LTRIM(RTRIM(value))) INTO v_table_name FROM cz_db_settings
WHERE UPPER(setting_id) = 'CUSTOM_CONFIG_TABLE'
AND UPPER(section_name) = 'CONFIGURATION';
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');
l_ext_attr_tbl.DELETE;
INSERT INTO CZ_CONFIG_EXT_ATTRIBUTES VALUES l_ext_attr_tbl(i);
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;
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
);
,p_handle_deleted_flag IN VARCHAR2 := NULL
,p_new_name IN VARCHAR2 := NULL
--Sungard ER Bug7673721
,p_force_new_inst_rev IN VARCHAR2 := NULL
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_deleted_flag cz_config_hdrs.DELETED_FLAG%TYPE;
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;
IF( l_deleted_flag = '1') THEN
IF (p_handle_deleted_flag IS NULL) THEN
fnd_message.set_name('CZ', 'CZ_CFG_COPY_DELETED_CONFIG');
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;
,p_handle_deleted_flag IN VARCHAR2 := NULL
,p_new_name IN VARCHAR2 := NULL
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
,p_handle_deleted_flag
,p_new_name
);
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';
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'
--vsingava 09th Dec '10. Bug10379808
-- Only messages related to validation failures dictate config isValid status
AND constraint_type
IN (G_MIN_VAL_FAILURE,
G_MAX_VAL_FAILURE,
G_MINMAX_VAL_FAILURE,
G_MIN0_VAL_FAILURE,
G_COMPANION_VAL_FAILURE,
G_RESOURCE_VAL_FAILURE,
G_RESTORE_VAL_FAILURE,
G_REQBOM_DEL_VAL_FAILURE,
G_TEXT_MAXLENGTH_VAL_FAILURE,
G_INVALID_LOCATION_VAL_FAILURE,
G_LOCKED_INSTANCES_VAL_FAILURE,
G_CUSTOM_VAL_FAILURE
)
AND ROWNUM < 2;
SELECT NVL(cz_utils.conv_num(value),DEFAULT_INCR) INTO id_increment
FROM CZ_DB_SETTINGS
WHERE section_name='SCHEMA' AND setting_id='OracleSequenceIncr';