[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO CZ_DB_LOGS
(RUN_ID,
LOGTIME,
LOGUSER,
URGENCY,
CALLER,
STATUSCODE,
MESSAGE)
VALUES (mRUN_ID,
SYSDATE,
USER,
1,
p_caller,
var_status,
p_error_message);
INSERT INTO CZ_DB_LOGS
(RUN_ID,
LOGTIME,
LOGUSER,
URGENCY,
CALLER,
STATUSCODE,
MESSAGE,
MESSAGE_ID)
VALUES
(p_run_id,
SYSDATE,
USER,
1,
'CZ_POPULATORS_PKG',
11276,
fnd_msg_pub.GET(i,fnd_api.g_false),
mCOUNTER);
SELECT TO_NUMBER(value) INTO mINCREMENT FROM cz_db_settings
WHERE UPPER(setting_id)=UPPER('OracleSequenceIncr') AND section_name='SCHEMA';
SELECT CZ_PS_NODES_S.NEXTVAL INTO mNext_PS_Node_Id FROM dual;
SELECT CZ_INTL_TEXTS_S.NEXTVAL INTO mNext_Text_Id FROM dual;
SELECT CZ_PS_NODES_S.nextval INTO mBase_PS_Node_Id FROM dual;
SELECT CZ_INTL_TEXTS_S.nextval INTO mBase_Text_Id FROM dual;
FOR i IN(SELECT expr_node_id,field_name,expr_subtype,seq_nbr FROM CZ_EXPRESSION_NODES
WHERE rule_id=p_rule_id AND (field_name IS NULL AND data_value IS NULL)
AND deleted_flag='0' ORDER BY seq_nbr)
LOOP
BEGIN
SELECT DECODE(field_name,'ref_part_nbr','item_master_name',field_name)
INTO var_field_name FROM CZ_EXPRESSION_NODES
WHERE expr_parent_id=i.expr_node_id AND field_name IS NOT NULL;
SELECT REPLACE(data_value,'''','''''') BULK COLLECT INTO var_data_value_tbl FROM CZ_EXPRESSION_NODES
WHERE expr_parent_id=i.expr_node_id AND data_value IS NOT NULL
AND deleted_flag='0';
DELETED_EXPRESSION EXCEPTION;
SELECT filter_set_id,view_name,result_type,var_ps_node_id,query_syntax,name
INTO var_filter_set_id,var_view_name,var_ps_node_type,var_ps_node_id,var_sql,var_pop_name
FROM CZ_POPULATORS
WHERE populator_id=p_populator_id;
SELECT a.source_type,a.rule_id,b.view_name
INTO var_source_type,var_rule_id,var_based_view
FROM CZ_FILTER_SETS a, CZ_POPULATORS b
WHERE a.filter_set_id=var_filter_set_id AND a.source_type=b.populator_id;
SELECT source_type,rule_id
INTO var_source_type,var_rule_id
FROM CZ_FILTER_SETS
WHERE filter_set_id=var_filter_set_id;
var_sql:='select * from '||var_based_view||' where '||get_Where(var_rule_id);
UPDATE CZ_POPULATORS SET query_syntax=var_sql, last_generation_date=SYSDATE
WHERE populator_id=p_populator_id;
UPDATE CZ_POPULATORS SET view_name=p_view_name WHERE populator_id=p_populator_id;
WHEN DELETED_EXPRESSION THEN
p_err:=mRUN_ID;
LOG_REPORT('CZ_POPULATORS_PKG.Regenerate','Regenerate populator "'||var_pop_name||'" : definition was deleted.');
SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO mRUN_ID FROM dual;
SELECT devl_project_id INTO l_model_id FROM CZ_PS_NODES
WHERE ps_node_id IN(SELECT owned_by_node_id FROM CZ_POPULATORS
WHERE populator_id=p_populator_id) AND deleted_flag='0' AND rownum<2;
var_last_update CZ_POPULATORS.last_update_date%TYPE;
SELECT ps_node_id
FROM cz_ps_nodes
WHERE parent_id=p_parent_ps_node_id
AND from_populator_id=p_populator_id
AND NVL(property_backptr,NULL_VALUE)=NVL(p_property_ptr,NULL_VALUE)
AND NVL(item_type_backptr,NULL_VALUE)=NVL(p_item_type_ptr,NULL_VALUE)
AND NVL(item_id,NULL_VALUE)=NVL(p_item_id,NULL_VALUE)
AND NVL(ps_node_type,NULL_VALUE)=NVL(p_ps_node_type,NULL_VALUE)
AND deleted_flag='0';
SELECT filter_set_id,last_generation_date,view_name,owned_by_node_id,
result_type,query_syntax,has_level,feature_type,name
INTO var_filter_set_id,var_last_update,var_view_name,var_ps_node_id,
var_result_type,var_sql,var_has_level,var_feature_type,var_pop_name
FROM CZ_POPULATORS
WHERE populator_id=p_populator_id;
SELECT rule_id INTO var_rule_id FROM CZ_FILTER_SETS
WHERE filter_set_id=var_filter_set_id;
SELECT LAST_UPDATE_DATE INTO var_curr_date FROM CZ_EXPRESSION_NODES
WHERE rule_id=var_rule_id AND expr_parent_id IS NULL AND deleted_flag='0';
IF var_last_update<=var_curr_date OR var_last_update IS NULL OR mALWAYS_REGENERATE IN('1','Y') THEN
Regenerate_unsec(p_populator_id => p_populator_id,
p_view_name => var_view_name,
p_sql_query => var_sql,
p_err => p_err);
SELECT devl_project_id INTO var_project_id FROM CZ_PS_NODES
WHERE ps_node_id=var_ps_node_id;
SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO p_run_id FROM dual;
DBMS_SQL.PARSE(var_cursor, 'SELECT * FROM '||var_sql||' ORDER BY name', dbms_sql.native);
SELECT intl_text_id INTO var_new_text_id
FROM CZ_PS_NODES
WHERE parent_id=var_ps_node_id AND
FROM_POPULATOR_ID=p_populator_id AND
NVL(PROPERTY_BACKPTR,NULL_VALUE)=NVL(var_property_ptr,NULL_VALUE) AND
NVL(ITEM_TYPE_BACKPTR,NULL_VALUE)=NVL(var_item_type_ptr,NULL_VALUE) AND
NVL(ITEM_ID,NULL_VALUE)=NVL(var_item_id,NULL_VALUE) AND
NVL(PS_NODE_TYPE,NULL_VALUE)=NVL(var_ps_node_type,NULL_VALUE) AND deleted_flag='0';
SELECT text_str INTO var_text_str FROM CZ_INTL_TEXTS
WHERE intl_text_id=var_new_text_id;
UPDATE CZ_LOCALIZED_TEXTS
SET localized_str=var_desc_text,
source_lang=var_current_lang
WHERE intl_text_id=var_new_text_id;
UPDATE CZ_IMP_PS_NODES
SET PROPERTY_BACKPTR = var_property_ptr,ITEM_TYPE_BACKPTR = var_item_type_ptr,INTL_TEXT_ID = var_new_text_id,
ITEM_ID = var_item_id,USER_STR03 = var_key,FSK_PSNODE_3_EXT = var_fk_key,
PRIMARY_UOM_CODE = var_primary_uom_code,
QUOTEABLE_FLAG = var_quoteable_flag,
INSTANTIABLE_FLAG = var_instantiable_flag,
COUNTED_OPTIONS_FLAG = var_counted_options_flag
WHERE RUN_ID = p_run_id AND DEVL_PROJECT_ID = var_project_id AND
PARENT_ID = var_ps_node_id AND NAME = var_name;
INSERT INTO CZ_IMP_PS_NODES
(RUN_ID,
PS_NODE_ID,
PARENT_ID,
DEVL_PROJECT_ID,
NAME,
FROM_POPULATOR_ID,
PROPERTY_BACKPTR,
ITEM_TYPE_BACKPTR,
INTL_TEXT_ID,
SUB_CONS_ID,
ITEM_ID,
MINIMUM,
MAXIMUM,
PS_NODE_TYPE,
FEATURE_TYPE,
PRODUCT_FLAG,
ORDER_SEQ_FLAG,
SYSTEM_NODE_FLAG,
TREE_SEQ,
UI_OMIT,
SO_ITEM_TYPE_CODE,
EFFECTIVE_USAGE_MASK,
EFFECTIVE_FROM,
EFFECTIVE_UNTIL,
UI_SECTION,
DELETED_FLAG,
USER_STR03,
DECIMAL_QTY_FLAG,
FSK_PSNODE_3_EXT,
PRIMARY_UOM_CODE,
QUOTEABLE_FLAG,
MULTI_CONFIG_FLAG,
INSTANTIABLE_FLAG,
COUNTED_OPTIONS_FLAG)
VALUES
(p_run_id,
0,
var_ps_node_id,
var_project_id,
var_name,
p_populator_id,
var_property_ptr,
var_item_type_ptr,
var_new_text_id,
NULL,
var_item_id,
var_minimum,
var_maximum,
var_ps_node_type,
var_feature_type,
'0',
'0',
'0',
var_tree_seq,
'0',
NULL,
'0000000000000000',
EPOCH_BEGIN,
EPOCH_END,
'0',
'0',
var_key,
'0',
var_fk_key,
var_primary_uom_code,
var_quoteable_flag,
'1',
var_instantiable_flag,
var_counted_options_flag);
SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO mRUN_ID FROM dual;
SELECT devl_project_id INTO l_model_id FROM CZ_PS_NODES
WHERE ps_node_id IN(SELECT owned_by_node_id FROM CZ_POPULATORS
WHERE populator_id=p_populator_id) AND deleted_flag='0' AND rownum<2;
var_inserts INTEGER;
var_updates INTEGER;
SELECT owned_by_node_id,name INTO var_parent_id,var_pop_name
FROM CZ_POPULATORS WHERE populator_id=p_populator_id;
SELECT component_id INTO var_component_id FROM CZ_PS_NODES
WHERE ps_node_id=var_parent_id;
UPDATE CZ_PS_NODES SET deleted_flag='1'
WHERE parent_id=var_parent_id
AND deleted_flag='0' AND
USER_STR03 NOT IN
(SELECT USER_STR03
FROM CZ_IMP_PS_NODES WHERE run_id=p_run_id) AND FROM_POPULATOR_ID=p_populator_id
RETURNING devl_project_id,ps_node_id,intl_text_id
BULK COLLECT INTO t_devl_project,t_ps_node_id,t_intl_text_id;
UPDATE CZ_MODEL_REF_EXPLS SET deleted_flag='1'
WHERE model_id=t_devl_project(i) AND
model_ref_expl_id
IN(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
WHERE deleted_flag='0'
START WITH component_id=t_ps_node_id(i)
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id);
UPDATE CZ_LOCALIZED_TEXTS
SET deleted_flag='1'
WHERE intl_text_id=t_intl_text_id(i);
var_inserts,
var_updates,
var_failed,
var_dups,
var_nochange,
mXFR_PROJECT_GROUP);
var_inserts,
var_updates,
var_failed,
var_nochange,
mXFR_PROJECT_GROUP);
UPDATE CZ_PS_NODES SET deleted_flag='1'
WHERE parent_id=var_parent_id
AND deleted_flag='0' AND
(NVL(PROPERTY_BACKPTR,NULL_VALUE),
NVL(ITEM_TYPE_BACKPTR,NULL_VALUE),NVL(ITEM_ID,NULL_VALUE),
NVL(PS_NODE_TYPE,NULL_VALUE))
NOT IN
(SELECT NVL(PROPERTY_BACKPTR,NULL_VALUE),
NVL(ITEM_TYPE_BACKPTR,NULL_VALUE),NVL(ITEM_ID,NULL_VALUE),
NVL(PS_NODE_TYPE,NULL_VALUE)
FROM CZ_IMP_PS_NODES WHERE run_id=p_run_id) AND FROM_POPULATOR_ID=p_populator_id
RETURNING devl_project_id,ps_node_id,intl_text_id
BULK COLLECT INTO t_devl_project,t_ps_node_id,t_intl_text_id;
UPDATE CZ_MODEL_REF_EXPLS SET deleted_flag='1'
WHERE model_id=t_devl_project(i) AND
model_ref_expl_id
IN(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
WHERE deleted_flag='0'
START WITH component_id=t_ps_node_id(i)
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id);
UPDATE CZ_LOCALIZED_TEXTS
SET deleted_flag='1'
WHERE intl_text_id=t_intl_text_id(i);
SELECT NVL(MAX(tree_seq),0) INTO var_tree_seq FROM CZ_PS_NODES
WHERE parent_id=var_parent_id AND deleted_flag='0';
FOR i IN(SELECT INTL_TEXT_ID,DEVL_PROJECT_ID,FROM_POPULATOR_ID,PROPERTY_BACKPTR,
ITEM_TYPE_BACKPTR,ITEM_ID,PS_NODE_TYPE,FEATURE_TYPE,QUOTEABLE_FLAG,NAME
FROM CZ_IMP_PS_NODES WHERE run_id=p_run_id)
LOOP
var_name := i.name;
SELECT COUNT(*) INTO var_name_counter FROM CZ_PS_NODES
WHERE parent_id=var_parent_id AND deleted_flag='0' AND
(name=i.name OR name like 'Copy (%) of '||i.name);
UPDATE CZ_PS_NODES
SET name=var_name,intl_text_id=i.intl_text_id,
feature_type=i.feature_type,quoteable_flag=i.quoteable_flag
WHERE parent_id=var_parent_id AND
FROM_POPULATOR_ID=p_populator_id AND
NVL(PROPERTY_BACKPTR,NULL_VALUE)=NVL(i.PROPERTY_BACKPTR,NULL_VALUE) AND
NVL(ITEM_TYPE_BACKPTR,NULL_VALUE)=NVL(i.ITEM_TYPE_BACKPTR,NULL_VALUE) AND
NVL(ITEM_ID,NULL_VALUE)=NVL(i.ITEM_ID,NULL_VALUE) AND
NVL(PS_NODE_TYPE,NULL_VALUE)=NVL(i.PS_NODE_TYPE,NULL_VALUE) AND deleted_flag='0';
INSERT INTO CZ_PS_NODES
(PS_NODE_ID,
PARENT_ID,
DEVL_PROJECT_ID,
NAME,
FROM_POPULATOR_ID,
PROPERTY_BACKPTR,
ITEM_TYPE_BACKPTR,
INTL_TEXT_ID,
SUB_CONS_ID,
ITEM_ID,
MINIMUM,
MAXIMUM,
PS_NODE_TYPE,
FEATURE_TYPE,
PRODUCT_FLAG,
ORDER_SEQ_FLAG,
SYSTEM_NODE_FLAG,
TREE_SEQ,
UI_OMIT,
SO_ITEM_TYPE_CODE,
EFFECTIVE_USAGE_MASK,
EFFECTIVE_FROM,
EFFECTIVE_UNTIL,
UI_SECTION,
DELETED_FLAG,
USER_STR03,
DECIMAL_QTY_FLAG,
PRIMARY_UOM_CODE,
QUOTEABLE_FLAG,
MULTI_CONFIG_FLAG,
VIRTUAL_FLAG,
PERSISTENT_NODE_ID,
INSTANTIABLE_FLAG,
COMPONENT_ID,
COUNTED_OPTIONS_FLAG)
SELECT
var_new_ps_id,
PARENT_ID,
DEVL_PROJECT_ID,
var_name,
p_populator_id,
PROPERTY_BACKPTR,
ITEM_TYPE_BACKPTR,
INTL_TEXT_ID,
SUB_CONS_ID,
ITEM_ID,
MINIMUM,
MAXIMUM,
PS_NODE_TYPE,
FEATURE_TYPE,
PRODUCT_FLAG,
ORDER_SEQ_FLAG,
SYSTEM_NODE_FLAG,
var_tree_seq,
UI_OMIT,
SO_ITEM_TYPE_CODE,
EFFECTIVE_USAGE_MASK,
EFFECTIVE_FROM,
EFFECTIVE_UNTIL,
UI_SECTION,
DELETED_FLAG,
USER_STR03,
DECIMAL_QTY_FLAG,
PRIMARY_UOM_CODE,
QUOTEABLE_FLAG,
MULTI_CONFIG_FLAG,
'1',
var_new_ps_id,
INSTANTIABLE_FLAG,
var_component_id,
COUNTED_OPTIONS_FLAG
FROM CZ_IMP_PS_NODES
WHERE run_id=p_run_id AND
NVL(FROM_POPULATOR_ID,NULL_VALUE)=NVL(i.FROM_POPULATOR_ID,NULL_VALUE) AND
NVL(PROPERTY_BACKPTR,NULL_VALUE)=NVL(i.PROPERTY_BACKPTR,NULL_VALUE) AND
NVL(ITEM_TYPE_BACKPTR,NULL_VALUE)=NVL(i.ITEM_TYPE_BACKPTR,NULL_VALUE) AND
NVL(ITEM_ID,NULL_VALUE)=NVL(i.ITEM_ID,NULL_VALUE) AND
NVL(PS_NODE_TYPE,NULL_VALUE)=NVL(i.PS_NODE_TYPE,NULL_VALUE);
INSERT INTO CZ_INTL_TEXTS(
INTL_TEXT_ID,
TEXT_STR,
MODEL_ID,
UI_DEF_ID,
DELETED_FLAG)
VALUES(
i.INTL_TEXT_ID,
INTL_ID_DESC_TEXT_ARRAY(i.INTL_TEXT_ID),
i.DEVL_PROJECT_ID,
NULL,
'0');
SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO mRUN_ID FROM dual;
SELECT devl_project_id INTO l_model_id FROM CZ_PS_NODES
WHERE ps_node_id IN(SELECT owned_by_node_id FROM CZ_POPULATORS
WHERE populator_id=p_populator_id) AND deleted_flag='0' AND rownum<2;
SELECT devl_project_id INTO var_model_id
FROM CZ_DEVL_PROJECTS WHERE devl_project_id=p_model_id AND deleted_flag='0';
FOR i IN(SELECT populator_id FROM CZ_POPULATORS a,CZ_PS_NODES b
WHERE a.owned_by_node_id=b.ps_node_id AND b.devl_project_id=p_model_id AND
a.deleted_flag='0' AND b.deleted_flag='0')
LOOP
var_counter:=var_counter+1;
UPDATE CZ_DB_LOGS SET run_id=mRUN_ID
WHERE run_id=t_errors(i);
SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO mRUN_ID FROM dual;
SELECT UPPER(value) INTO mCREATE_DEBUG_VIEWS FROM CZ_DB_SETTINGS
WHERE UPPER(setting_id)='CREATEPOPVIEWS';
SELECT UPPER(value) INTO mUSE_IMPORT FROM CZ_DB_SETTINGS
WHERE UPPER(setting_id)='USEIMPORT';
SELECT value INTO mUSE_LOCKING FROM CZ_DB_SETTINGS
WHERE setting_id = 'USE_LOCKING' AND rownum<2;
SELECT UPPER(value) INTO mALWAYS_REGENERATE FROM CZ_DB_SETTINGS
WHERE UPPER(setting_id)='ALWAYS_REGENERATE_POPULATORS';