[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO cz_db_logs (logtime, urgency, caller, statuscode, message, run_id)
VALUES (SYSDATE, 1, p_caller, p_statuscode, p_message, p_run_id);
SELECT ROWID, devl_project_id, message, orig_sys_ref, rule_folder_id, rule_type,
name, seeded_flag, deleted_flag, mutable_flag, disabled_flag, invalid_flag,
presentation_flag, effective_usage_mask, seq_nbr, disposition,
component_id, model_ref_expl_id, fsk_component_id, fsk_model_ref_expl_id,
instantiation_scope
FROM cz_imp_rules
WHERE run_id = p_run_id
AND rec_status IS NULL
AND disposition IS NULL;
t_deleted_flag table_of_varchar;
'SELECT model_ref_expl_id ' ||
' FROM cz_rp_entries r, cz_model_ref_expls e ' ||
' WHERE r.deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
' AND r.object_type = ''' || CZRI_REPOSITORY_PROJECT || ''' ' ||
' AND r.object_id = :1 ' ||
' AND e.deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
' AND e.parent_expl_node_id IS NULL ' ||
' AND e.model_id = r.object_id';
'SELECT rule_folder_id ' ||
' FROM cz_rule_folders ' ||
' WHERE deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
' AND object_type = ''' || CZRI_TYPE_RULE_FOLDER || ''' ' ||
' AND devl_project_id = :1 ' ||
' AND parent_rule_folder_id IS NULL';
'SELECT NULL ' ||
' FROM cz_rule_folders ' ||
' WHERE deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
' AND object_type = ''' || CZRI_TYPE_RULE_FOLDER || ''' ' ||
' AND devl_project_id = :1 ' ||
' AND rule_folder_id = :2';
PROCEDURE update_table_data(p_upper_limit IN PLS_INTEGER) IS
BEGIN
FORALL i IN 1..p_upper_limit
UPDATE cz_imp_rules SET
message = t_message(i),
seeded_flag = t_seeded_flag(i),
deleted_flag = t_deleted_flag(i),
rec_status = CZRI_RECSTATUS_CND,
disposition = t_disposition(i),
mutable_flag = t_mutable_flag(i),
disabled_flag = t_disabled_flag(i),
invalid_flag = t_invalid_flag(i),
presentation_flag = t_presentation_flag(i),
effective_usage_mask = t_effective_usage_mask(i),
seq_nbr = t_seq_nbr(i),
model_ref_expl_id = t_model_ref_expl_id(i),
rule_folder_id = t_rule_folder_id(i),
component_id = t_component_id(i),
instantiation_scope = t_instantiation_scope(i)
WHERE ROWID = t_rowid(i);
t_rowid.DELETE;
t_devl_project_id.DELETE;
t_message.DELETE;
t_orig_sys_ref.DELETE;
t_rule_folder_id.DELETE;
t_rule_type.DELETE;
t_name.DELETE;
t_seeded_flag.DELETE;
t_deleted_flag.DELETE;
t_mutable_flag.DELETE;
t_disabled_flag.DELETE;
t_invalid_flag.DELETE;
t_presentation_flag.DELETE;
t_effective_usage_mask.DELETE;
t_seq_nbr.DELETE;
t_disposition.DELETE;
t_component_id.DELETE;
t_model_ref_expl_id.DELETE;
t_fsk_component_id.DELETE;
t_fsk_model_ref_expl_id.DELETE;
t_instantiation_scope.DELETE;
t_seeded_flag, t_deleted_flag, t_mutable_flag, t_disabled_flag, t_invalid_flag, t_presentation_flag,
t_effective_usage_mask, t_seq_nbr, t_disposition, t_component_id, t_model_ref_expl_id, t_fsk_component_id,
t_fsk_model_ref_expl_id, t_instantiation_scope
LIMIT p_commit_size;
update_table_data(i);
IF(t_deleted_flag(i) IS NULL)THEN t_deleted_flag(i) := CZRI_FLAG_NOT_DELETED; END IF;
update_table_data(t_rowid.COUNT);
SELECT ROWID, rule_id, devl_project_id, message, orig_sys_ref, rec_status, disposition,
fsk_localized_text_1, fsk_component_id, fsk_model_ref_expl_id, rule_type, reason_id
FROM cz_imp_rules
WHERE run_id = p_run_id
AND rec_status = CZRI_RECSTATUS_CND
AND disposition = CZRI_DISPOSITION_PASSED
ORDER BY devl_project_id, orig_sys_ref;
'SELECT rule_id ' ||
' FROM cz_rules ' ||
' WHERE deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
' AND devl_project_id = :1 ' ||
' AND orig_sys_ref = :2';
PROCEDURE update_table_data(p_upper_limit IN PLS_INTEGER) IS
BEGIN
FORALL i IN 1..p_upper_limit
UPDATE cz_imp_rules SET
rule_id = t_rule_id(i),
reason_id = t_reason_id(i),
message = t_message(i),
rec_status = CZRI_RECSTATUS_KRS,
disposition = t_disposition(i)
WHERE ROWID = t_rowid(i);
SELECT cz_rules_s.NEXTVAL INTO last_id_allocated FROM dual;
t_rowid.DELETE;
t_rule_id.DELETE;
t_devl_project_id.DELETE;
t_message.DELETE;
t_orig_sys_ref.DELETE;
t_rec_status.DELETE;
t_disposition.DELETE;
t_fsk_localized_text_1.DELETE;
t_fsk_component_id.DELETE;
t_fsk_model_ref_expl_id.DELETE;
t_rule_type.DELETE;
t_reason_id.DELETE;
t_disposition(i) := CZRI_DISPOSITION_INSERT;
SELECT intl_text_id BULK COLLECT INTO t_intl_text_id
FROM cz_localized_texts
WHERE deleted_flag = CZRI_FLAG_NOT_DELETED
AND model_id = t_devl_project_id(i)
AND orig_sys_ref = t_fsk_localized_text_1(i);
update_table_data(i);
update_table_data(t_rowid.COUNT);
SELECT ROWID, rule_id, reason_id, rule_folder_id, devl_project_id, invalid_flag, desc_text,
name, rule_type, expr_rule_type, component_id, model_ref_expl_id, reason_type,
disabled_flag, orig_sys_ref, deleted_flag, security_mask, checkout_user, last_update_login,
effective_usage_mask, seq_nbr, effective_from, effective_until, effectivity_set_id,
unsatisfied_msg_id, unsatisfied_msg_source, signature_id, template_primitive_flag,
presentation_flag, template_token, rule_text, notes, class_name, instantiation_scope,
mutable_flag, seeded_flag, ui_def_id, ui_page_id, ui_page_element_id, rule_folder_type,
disposition, message
FROM cz_imp_rules
WHERE run_id = p_run_id
AND rec_status = CZRI_RECSTATUS_KRS
AND disposition IN (CZRI_DISPOSITION_INSERT, CZRI_DISPOSITION_MODIFY);
t_deleted_flag table_of_varchar;
t_last_update_login table_of_number;
PROCEDURE update_table_data(p_upper_limit IN PLS_INTEGER) IS
BEGIN
--We updating rec_status to XFR, not OK, because the rules still have to be parsed.
--When parsed successfully, the status will be changed to OK.
FORALL i IN 1..p_upper_limit
UPDATE cz_imp_rules SET
message = t_message(i),
rec_status = CZRI_RECSTATUS_XFR,
disposition = t_disposition(i)
WHERE ROWID = t_rowid(i);
FUNCTION insert_online_data(p_upper_limit IN PLS_INTEGER) RETURN BOOLEAN IS
t_rule_id table_of_number;
t_deleted_flag table_of_varchar;
t_last_update_login table_of_number;
IF(xfr_rules.t_disposition(i) = CZRI_DISPOSITION_INSERT)THEN
t_rule_id(v_index) := xfr_rules.t_rule_id(i);
t_deleted_flag(v_index) := xfr_rules.t_deleted_flag(i);
t_last_update_login(v_index) := xfr_rules.t_last_update_login(i);
INSERT INTO cz_rules
(rule_id, reason_id, rule_folder_id, devl_project_id, invalid_flag, desc_text,
name, rule_type, expr_rule_type, component_id, model_ref_expl_id, reason_type,
disabled_flag, orig_sys_ref, deleted_flag, security_mask, checkout_user, last_update_login,
effective_usage_mask, seq_nbr, effective_from, effective_until, effectivity_set_id,
unsatisfied_msg_id, unsatisfied_msg_source, signature_id, template_primitive_flag,
presentation_flag, template_token, rule_text, notes, class_name, instantiation_scope,
mutable_flag, seeded_flag, ui_def_id, ui_page_id, ui_page_element_id, rule_folder_type)
VALUES
(t_rule_id(i), t_reason_id(i), t_rule_folder_id(i), t_devl_project_id(i), t_invalid_flag(i),
t_desc_text(i), t_name(i), t_rule_type(i), t_expr_rule_type(i), t_component_id(i),
t_model_ref_expl_id(i), t_reason_type(i), t_disabled_flag(i), t_orig_sys_ref(i), t_deleted_flag(i),
t_security_mask(i), t_checkout_user(i), t_last_update_login(i), t_effective_usage_mask(i),
t_seq_nbr(i), t_effective_from(i), t_effective_until(i), t_effectivity_set_id(i),
t_unsatisfied_msg_id(i), t_unsatisfied_msg_source(i), t_signature_id(i), t_template_primitive_flag(i),
t_presentation_flag(i), t_template_token(i), t_rule_text(i), t_notes(i), t_class_name(i),
t_instantiation_scope(i), t_mutable_flag(i), t_seeded_flag(i), t_ui_def_id(i), t_ui_page_id(i),
t_ui_page_element_id(i), t_rule_folder_type(i));
INSERT INTO cz_rule_folders
(rule_folder_id, parent_rule_folder_id, devl_project_id, desc_text, name, object_type, folder_type,
disabled_flag, orig_sys_ref, deleted_flag, security_mask, checkout_user, last_update_login,
effective_usage_mask, tree_seq, effective_from, effective_until, effectivity_set_id)
VALUES
(t_rule_id(i), t_rule_folder_id(i), t_devl_project_id(i), t_desc_text(i), t_name(i),
DECODE(t_rule_type(i), CZRI_TYPE_COMPANION_RULE, CZRI_FOLDER_TYPE_CX, CZRI_FOLDER_TYPE_RULE),
t_rule_folder_type(i), t_disabled_flag(i), t_orig_sys_ref(i), t_deleted_flag(i),
t_security_mask(i), t_checkout_user(i), t_last_update_login(i), t_effective_usage_mask(i),
t_seq_nbr(i), t_effective_from(i), t_effective_until(i), t_effectivity_set_id(i));
END insert_online_data;
FUNCTION update_online_data(p_upper_limit IN PLS_INTEGER) RETURN BOOLEAN IS
t_rule_id table_of_number;
t_deleted_flag table_of_varchar;
t_last_update_login table_of_number;
t_deleted_flag(v_index) := xfr_rules.t_deleted_flag(i);
t_last_update_login(v_index) := xfr_rules.t_last_update_login(i);
UPDATE cz_rules SET
reason_id = t_reason_id(i),
rule_folder_id = t_rule_folder_id(i),
devl_project_id = t_devl_project_id(i),
invalid_flag = t_invalid_flag(i),
desc_text = t_desc_text(i),
name = t_name(i),
rule_type = t_rule_type(i),
expr_rule_type = t_expr_rule_type(i),
component_id = t_component_id(i),
model_ref_expl_id = t_model_ref_expl_id(i),
reason_type = t_reason_type(i),
disabled_flag = t_disabled_flag(i),
orig_sys_ref = t_orig_sys_ref(i),
deleted_flag = t_deleted_flag(i),
security_mask = t_security_mask(i),
checkout_user = t_checkout_user(i),
last_update_login = t_last_update_login(i),
effective_usage_mask = t_effective_usage_mask(i),
seq_nbr = t_seq_nbr(i),
effective_from = t_effective_from(i),
effective_until = t_effective_until(i),
effectivity_set_id = t_effectivity_set_id(i),
unsatisfied_msg_id = t_unsatisfied_msg_id(i),
unsatisfied_msg_source = t_unsatisfied_msg_source(i),
signature_id = t_signature_id(i),
template_primitive_flag = t_template_primitive_flag(i),
presentation_flag = t_presentation_flag(i),
template_token = t_template_token(i),
rule_text = t_rule_text(i),
notes = t_notes(i),
class_name = t_class_name(i),
instantiation_scope = t_instantiation_scope(i),
mutable_flag = t_mutable_flag(i),
seeded_flag = t_seeded_flag(i),
ui_def_id = t_ui_def_id(i),
ui_page_id = t_ui_page_id(i),
ui_page_element_id = t_ui_page_element_id(i),
rule_folder_type = t_rule_folder_type(i)
WHERE rule_id = t_rule_id(i);
UPDATE cz_rule_folders SET
parent_rule_folder_id = t_rule_folder_id(i),
desc_text = t_desc_text(i),
name = t_name(i),
folder_type = t_rule_folder_type(i),
tree_seq = t_seq_nbr(i),
disabled_flag = t_disabled_flag(i),
deleted_flag = t_deleted_flag(i),
security_mask = t_security_mask(i),
checkout_user = t_checkout_user(i),
last_update_login = t_last_update_login(i),
effective_usage_mask = t_effective_usage_mask(i),
effective_from = t_effective_from(i),
effective_until = t_effective_until(i),
effectivity_set_id = t_effectivity_set_id(i)
WHERE rule_folder_id = t_rule_id(i)
AND object_type = CZRI_FOLDER_TYPE_RULE;
END update_online_data;
t_rowid.DELETE;
t_rule_id.DELETE;
t_reason_id.DELETE;
t_rule_folder_id.DELETE;
t_devl_project_id.DELETE;
t_invalid_flag.DELETE;
t_desc_text.DELETE;
t_name.DELETE;
t_rule_type.DELETE;
t_expr_rule_type.DELETE;
t_component_id.DELETE;
t_model_ref_expl_id.DELETE;
t_reason_type.DELETE;
t_disabled_flag.DELETE;
t_orig_sys_ref.DELETE;
t_deleted_flag.DELETE;
t_security_mask.DELETE;
t_checkout_user.DELETE;
t_last_update_login.DELETE;
t_effective_usage_mask.DELETE;
t_seq_nbr.DELETE;
t_effective_from.DELETE;
t_effective_until.DELETE;
t_effectivity_set_id.DELETE;
t_unsatisfied_msg_id.DELETE;
t_unsatisfied_msg_source.DELETE;
t_signature_id.DELETE;
t_template_primitive_flag.DELETE;
t_presentation_flag.DELETE;
t_template_token.DELETE;
t_rule_text.DELETE;
t_notes.DELETE;
t_class_name.DELETE;
t_instantiation_scope.DELETE;
t_mutable_flag.DELETE;
t_seeded_flag.DELETE;
t_ui_def_id.DELETE;
t_ui_page_id.DELETE;
t_ui_page_element_id.DELETE;
t_rule_folder_type.DELETE;
t_disposition.DELETE;
t_message.DELETE;
t_orig_sys_ref, t_deleted_flag, t_security_mask, t_checkout_user, t_last_update_login, t_effective_usage_mask,
t_seq_nbr, t_effective_from, t_effective_until, t_effectivity_set_id, t_unsatisfied_msg_id,
t_unsatisfied_msg_source, t_signature_id, t_template_primitive_flag, t_presentation_flag, t_template_token,
t_rule_text, t_notes, t_class_name, t_instantiation_scope, t_mutable_flag, t_seeded_flag, t_ui_def_id,
t_ui_page_id, t_ui_page_element_id, t_rule_folder_type, t_disposition, t_message
LIMIT p_commit_size;
IF(NOT insert_online_data(t_rowid.COUNT))THEN
FOR i IN 1..t_rowid.COUNT LOOP
IF(t_disposition(i) = CZRI_DISPOSITION_INSERT)THEN
BEGIN
SAVEPOINT insert_rule_record;
INSERT INTO cz_rules
(rule_id, reason_id, rule_folder_id, devl_project_id, invalid_flag, desc_text,
name, rule_type, expr_rule_type, component_id, model_ref_expl_id, reason_type,
disabled_flag, orig_sys_ref, deleted_flag, security_mask, checkout_user, last_update_login,
effective_usage_mask, seq_nbr, effective_from, effective_until, effectivity_set_id,
unsatisfied_msg_id, unsatisfied_msg_source, signature_id, template_primitive_flag,
presentation_flag, template_token, rule_text, notes, class_name, instantiation_scope,
mutable_flag, seeded_flag, ui_def_id, ui_page_id, ui_page_element_id, rule_folder_type)
VALUES
(t_rule_id(i), t_reason_id(i), t_rule_folder_id(i), t_devl_project_id(i),
t_invalid_flag(i), t_desc_text(i), t_name(i), t_rule_type(i), t_expr_rule_type(i),
t_component_id(i), t_model_ref_expl_id(i), t_reason_type(i), t_disabled_flag(i),
t_orig_sys_ref(i), t_deleted_flag(i), t_security_mask(i), t_checkout_user(i),
t_last_update_login(i), t_effective_usage_mask(i), t_seq_nbr(i), t_effective_from(i),
t_effective_until(i), t_effectivity_set_id(i), t_unsatisfied_msg_id(i),
t_unsatisfied_msg_source(i), t_signature_id(i), t_template_primitive_flag(i),
t_presentation_flag(i), t_template_token(i), t_rule_text(i), t_notes(i),
t_class_name(i), t_instantiation_scope(i), t_mutable_flag(i), t_seeded_flag(i),
t_ui_def_id(i), t_ui_page_id(i), t_ui_page_element_id(i), t_rule_folder_type(i));
INSERT INTO cz_rule_folders
(rule_folder_id, parent_rule_folder_id, devl_project_id, desc_text, name, object_type, folder_type,
disabled_flag, orig_sys_ref, deleted_flag, security_mask, checkout_user, last_update_login,
effective_usage_mask, tree_seq, effective_from, effective_until, effectivity_set_id)
VALUES
(t_rule_id(i), t_rule_folder_id(i), t_devl_project_id(i), t_desc_text(i), t_name(i),
DECODE(t_rule_type(i), CZRI_TYPE_COMPANION_RULE, CZRI_FOLDER_TYPE_CX, CZRI_FOLDER_TYPE_RULE),
t_rule_folder_type(i), t_disabled_flag(i), t_orig_sys_ref(i),
t_deleted_flag(i), t_security_mask(i), t_checkout_user(i), t_last_update_login(i),
t_effective_usage_mask(i), t_seq_nbr(i), t_effective_from(i), t_effective_until(i),
t_effectivity_set_id(i));
ROLLBACK TO insert_rule_record;
update_table_data(i);
IF(NOT update_online_data(t_rowid.COUNT))THEN
FOR i IN 1..t_rowid.COUNT LOOP
IF(t_disposition(i) = CZRI_DISPOSITION_MODIFY)THEN
BEGIN
SAVEPOINT update_rule_record;
UPDATE cz_rules SET
reason_id = t_reason_id(i),
rule_folder_id = t_rule_folder_id(i),
devl_project_id = t_devl_project_id(i),
invalid_flag = t_invalid_flag(i),
desc_text = t_desc_text(i),
name = t_name(i),
rule_type = t_rule_type(i),
expr_rule_type = t_expr_rule_type(i),
component_id = t_component_id(i),
model_ref_expl_id = t_model_ref_expl_id(i),
reason_type = t_reason_type(i),
disabled_flag = t_disabled_flag(i),
orig_sys_ref = t_orig_sys_ref(i),
deleted_flag = t_deleted_flag(i),
security_mask = t_security_mask(i),
checkout_user = t_checkout_user(i),
last_update_login = t_last_update_login(i),
effective_usage_mask = t_effective_usage_mask(i),
seq_nbr = t_seq_nbr(i),
effective_from = t_effective_from(i),
effective_until = t_effective_until(i),
effectivity_set_id = t_effectivity_set_id(i),
unsatisfied_msg_id = t_unsatisfied_msg_id(i),
unsatisfied_msg_source = t_unsatisfied_msg_source(i),
signature_id = t_signature_id(i),
template_primitive_flag = t_template_primitive_flag(i),
presentation_flag = t_presentation_flag(i),
template_token = t_template_token(i),
rule_text = t_rule_text(i),
notes = t_notes(i),
class_name = t_class_name(i),
instantiation_scope = t_instantiation_scope(i),
mutable_flag = t_mutable_flag(i),
seeded_flag = t_seeded_flag(i),
ui_def_id = t_ui_def_id(i),
ui_page_id = t_ui_page_id(i),
ui_page_element_id = t_ui_page_element_id(i),
rule_folder_type = t_rule_folder_type(i)
WHERE rule_id = t_rule_id(i);
UPDATE cz_rule_folders SET
parent_rule_folder_id = t_rule_folder_id(i),
desc_text = t_desc_text(i),
name = t_name(i),
folder_type = t_rule_folder_type(i),
tree_seq = t_seq_nbr(i),
disabled_flag = t_disabled_flag(i),
deleted_flag = t_deleted_flag(i),
security_mask = t_security_mask(i),
checkout_user = t_checkout_user(i),
last_update_login = t_last_update_login(i),
effective_usage_mask = t_effective_usage_mask(i),
effective_from = t_effective_from(i),
effective_until = t_effective_until(i),
effectivity_set_id = t_effectivity_set_id(i)
WHERE rule_folder_id = t_rule_id(i)
AND object_type = CZRI_FOLDER_TYPE_RULE;
ROLLBACK TO update_rule_record;
update_table_data(i);
update_table_data(t_rowid.COUNT);
FOR c_stat IN (SELECT disposition, rec_status, COUNT(*) as records
FROM cz_imp_rules
WHERE run_id = p_run_id
AND rec_status IS NOT NULL
AND disposition IS NOT NULL
GROUP BY disposition, rec_status) LOOP
INSERT INTO cz_xfr_run_results (run_id, imp_table, disposition, rec_status, records)
VALUES (p_run_id, 'CZ_IMP_RULES', c_stat.disposition, c_stat.rec_status, c_stat.records);
DELETE FROM cz_xfr_run_results WHERE run_id = p_run_id;
SELECT ROWID, model_id, message, orig_sys_ref, language, source_lang,
seeded_flag, deleted_flag, disposition
FROM cz_imp_localized_texts
WHERE run_id = p_run_id
AND rec_status IS NULL
AND disposition IS NULL;
t_deleted_flag table_of_varchar;
'SELECT NULL ' ||
' FROM cz_rp_entries ' ||
' WHERE deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
' AND object_type = ''' || CZRI_REPOSITORY_PROJECT || ''' ' ||
' AND object_id = :1';
PROCEDURE update_table_data(p_upper_limit IN PLS_INTEGER) IS
BEGIN
FORALL i IN 1..p_upper_limit
UPDATE cz_imp_localized_texts SET
message = t_message(i),
seeded_flag = t_seeded_flag(i),
deleted_flag = t_deleted_flag(i),
rec_status = CZRI_RECSTATUS_CND,
disposition = t_disposition(i)
WHERE ROWID = t_rowid(i);
t_rowid.DELETE;
t_model_id.DELETE;
t_message.DELETE;
t_orig_sys_ref.DELETE;
t_language.DELETE;
t_source_lang.DELETE;
t_seeded_flag.DELETE;
t_deleted_flag.DELETE;
t_disposition.DELETE;
t_rowid, t_model_id, t_message, t_orig_sys_ref, t_language, t_source_lang, t_seeded_flag, t_deleted_flag,
t_disposition
LIMIT p_commit_size;
update_table_data(i);
IF(t_deleted_flag(i) IS NULL)THEN t_deleted_flag(i) := CZRI_FLAG_NOT_DELETED; END IF;
update_table_data(t_rowid.COUNT);
SELECT ROWID, intl_text_id, model_id, message, orig_sys_ref, language, rec_status, disposition
FROM cz_imp_localized_texts
WHERE run_id = p_run_id
AND rec_status = CZRI_RECSTATUS_CND
AND disposition = CZRI_DISPOSITION_PASSED
ORDER BY model_id, orig_sys_ref, language;
'SELECT intl_text_id ' ||
' FROM cz_localized_texts ' ||
' WHERE deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
' AND model_id = :1 ' ||
' AND language = :2 ' ||
' AND orig_sys_ref = :3';
'SELECT intl_text_id ' ||
' FROM cz_localized_texts ' ||
' WHERE deleted_flag = ''' || CZRI_FLAG_NOT_DELETED || ''' ' ||
' AND model_id = :1 ' ||
' AND orig_sys_ref = :2 ' ||
' AND ROWNUM = 1';
PROCEDURE update_table_data(p_upper_limit IN PLS_INTEGER) IS
BEGIN
FORALL i IN 1..p_upper_limit
UPDATE cz_imp_localized_texts SET
intl_text_id = t_intl_text_id(i),
message = t_message(i),
rec_status = CZRI_RECSTATUS_KRS,
disposition = t_disposition(i)
WHERE ROWID = t_rowid(i);
SELECT cz_intl_texts_s.NEXTVAL INTO last_id_allocated FROM dual;
t_rowid.DELETE;
t_intl_text_id.DELETE;
t_model_id.DELETE;
t_message.DELETE;
t_orig_sys_ref.DELETE;
t_language.DELETE;
t_rec_status.DELETE;
t_disposition.DELETE;
t_disposition(i) := CZRI_DISPOSITION_INSERT;
update_table_data(i);
update_table_data(t_rowid.COUNT);
SELECT ROWID,last_update_login, locale_id, localized_str, intl_text_id, deleted_flag, security_mask,
checkout_user, orig_sys_ref, language, source_lang, model_id, seeded_flag,
disposition, message
FROM cz_imp_localized_texts
WHERE run_id = p_run_id
AND rec_status = CZRI_RECSTATUS_KRS
AND disposition IN (CZRI_DISPOSITION_INSERT, CZRI_DISPOSITION_MODIFY);
t_last_update_login table_of_number;
t_deleted_flag table_of_varchar;
PROCEDURE update_table_data(p_upper_limit IN PLS_INTEGER) IS
BEGIN
--We updating rec_status to OK, not XFR, because, unlike the rules which still have
--to be parsed, import of localized texts is done.
FORALL i IN 1..p_upper_limit
UPDATE cz_imp_localized_texts SET
message = t_message(i),
rec_status = CZRI_RECSTATUS_OK,
disposition = t_disposition(i)
WHERE ROWID = t_rowid(i);
FUNCTION insert_online_data(p_upper_limit IN PLS_INTEGER) RETURN BOOLEAN IS
t_last_update_login table_of_number;
t_deleted_flag table_of_varchar;
IF(t_disposition(i) = CZRI_DISPOSITION_INSERT)THEN
t_last_update_login(v_index) := xfr_localized_texts.t_last_update_login(i);
t_deleted_flag(v_index) := xfr_localized_texts.t_deleted_flag(i);
INSERT INTO cz_localized_texts
(last_update_login, locale_id, localized_str, intl_text_id, deleted_flag, security_mask,
checkout_user, orig_sys_ref, language, source_lang, model_id, seeded_flag)
VALUES
(t_last_update_login(i), t_locale_id(i), t_localized_str(i), t_intl_text_id(i),
t_deleted_flag(i), t_security_mask(i), t_checkout_user(i), t_orig_sys_ref(i),
t_language(i), t_source_lang(i), t_model_id(i), t_seeded_flag(i));
FUNCTION update_online_data(p_upper_limit IN PLS_INTEGER) RETURN BOOLEAN IS
t_last_update_login table_of_number;
t_deleted_flag table_of_varchar;
t_last_update_login(v_index) := xfr_localized_texts.t_last_update_login(i);
t_deleted_flag(v_index) := xfr_localized_texts.t_deleted_flag(i);
UPDATE cz_localized_texts SET
last_update_login = t_last_update_login(i),
locale_id = t_locale_id(i),
localized_str = t_localized_str(i),
deleted_flag = t_deleted_flag(i),
security_mask = t_security_mask(i),
checkout_user = t_checkout_user(i),
orig_sys_ref = t_orig_sys_ref(i),
source_lang = t_source_lang(i),
model_id = t_model_id(i),
seeded_flag = t_seeded_flag(i)
WHERE intl_text_id = t_intl_text_id(i)
AND language = t_language(i);
t_rowid.DELETE;
t_last_update_login.DELETE;
t_locale_id.DELETE;
t_localized_str.DELETE;
t_intl_text_id.DELETE;
t_deleted_flag.DELETE;
t_security_mask.DELETE;
t_checkout_user.DELETE;
t_orig_sys_ref.DELETE;
t_language.DELETE;
t_source_lang.DELETE;
t_model_id.DELETE;
t_seeded_flag.DELETE;
t_disposition.DELETE;
t_message.DELETE;
t_rowid, t_last_update_login, t_locale_id, t_localized_str, t_intl_text_id, t_deleted_flag,
t_security_mask, t_checkout_user, t_orig_sys_ref, t_language, t_source_lang, t_model_id,
t_seeded_flag, t_disposition, t_message
LIMIT p_commit_size;
IF(NOT insert_online_data(t_rowid.COUNT))THEN
FOR i IN 1..t_rowid.COUNT LOOP
IF(t_disposition(i) = CZRI_DISPOSITION_INSERT)THEN
BEGIN
INSERT INTO cz_localized_texts
(last_update_login, locale_id, localized_str, intl_text_id, deleted_flag, security_mask,
checkout_user, orig_sys_ref, language, source_lang, model_id, seeded_flag)
VALUES
(t_last_update_login(i), t_locale_id(i), t_localized_str(i), t_intl_text_id(i),
t_deleted_flag(i), t_security_mask(i), t_checkout_user(i), t_orig_sys_ref(i),
t_language(i), t_source_lang(i), t_model_id(i), t_seeded_flag(i));
update_table_data(i);
IF(NOT update_online_data(t_rowid.COUNT))THEN
FOR i IN 1..t_rowid.COUNT LOOP
IF(t_disposition(i) = CZRI_DISPOSITION_MODIFY)THEN
BEGIN
UPDATE cz_localized_texts SET
last_update_login = t_last_update_login(i),
locale_id = t_locale_id(i),
localized_str = t_localized_str(i),
deleted_flag = t_deleted_flag(i),
security_mask = t_security_mask(i),
checkout_user = t_checkout_user(i),
orig_sys_ref = t_orig_sys_ref(i),
source_lang = t_source_lang(i),
model_id = t_model_id(i),
seeded_flag = t_seeded_flag(i)
WHERE intl_text_id = t_intl_text_id(i)
AND language = t_language(i);
update_table_data(i);
update_table_data(t_rowid.COUNT);
FOR c_stat IN (SELECT disposition, rec_status, COUNT(*) as records
FROM cz_imp_localized_texts
WHERE run_id = p_run_id
AND rec_status IS NOT NULL
AND disposition IS NOT NULL
GROUP BY disposition, rec_status) LOOP
INSERT INTO cz_xfr_run_results (run_id, imp_table, disposition, rec_status, records)
VALUES (p_run_id, 'CZ_IMP_LOCALIZED_TEXTS', c_stat.disposition, c_stat.rec_status, c_stat.records);
DELETE FROM cz_xfr_run_results WHERE run_id = p_run_id;
SELECT NULL INTO v_null FROM v$session WHERE module = CZRI_MODULE_NAME;
SELECT cz_xfr_run_infos_s.NEXTVAL INTO p_run_id FROM DUAL;
UPDATE cz_imp_rules SET run_id = p_run_id
WHERE disposition IS NULL
AND rec_status IS NULL
AND run_id IS NULL;
UPDATE cz_imp_localized_texts SET run_id = p_run_id
WHERE disposition IS NULL
AND rec_status IS NULL
AND run_id IS NULL;
SELECT 1 INTO v_error_flag FROM DUAL WHERE EXISTS
(SELECT NULL FROM cz_imp_rules WHERE run_id = p_run_id);
SELECT NULL INTO v_null FROM cz_xfr_run_infos WHERE run_id = p_run_id;
INSERT INTO cz_xfr_run_infos (run_id, started, last_activity, completed)
VALUES (p_run_id, SYSDATE, SYSDATE, '0');
UPDATE cz_xfr_run_infos SET
last_activity = SYSDATE,
completed = '1'
WHERE run_id = p_run_id;
SELECT 1 INTO v_error_flag FROM DUAL WHERE EXISTS
(SELECT NULL FROM cz_imp_rules
WHERE run_id = p_run_id
AND rec_status = CZRI_RECSTATUS_XFR
AND disposition IN (CZRI_DISPOSITION_INSERT, CZRI_DISPOSITION_MODIFY));
SELECT distinct devl_project_id
BULK
COLLECT
INTO l_model_id_tbl
FROM cz_imp_rules
WHERE rec_status IS NULL
AND disposition IS NULL
AND run_id IS NULL;
SELECT distinct devl_project_id
BULK
COLLECT
INTO l_model_id_tbl
FROM cz_imp_rules
WHERE rec_status IS NULL
AND disposition IS NULL
AND run_id = p_run_id;