[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
ITEM_DELETE_MESSAGE CONSTANT VARCHAR2(30) := 'CZ_BATCH_VAL_ITEM_DELETED';
SELECT value INTO l_ret_value FROM cz_db_settings WHERE Upper(section_name)
= Upper(p_section_name) AND Upper(setting_id) = Upper(p_setting);
SELECT model_usage_id
INTO v_usage_id
FROM CZ_MODEL_USAGES
WHERE LTRIM(RTRIM(UPPER(CZ_MODEL_USAGES.name))) = LTRIM(RTRIM(UPPER(p_usage_name)))
AND cz_model_usages.in_use = '1';
SELECT DECODE(ui_type, 'APPLET', UI_STYLE_APPLET, 'DHTML', UI_STYLE_DHTML,
'JRAD', UI_STYLE_JRAD, 'WEGA', UI_STYLE_WEGA, NULL)
INTO v_ui_style
FROM dual;
SELECT DECODE(ui_style, UI_STYLE_APPLET, 'APPLET', UI_STYLE_DHTML, 'DHTML',
UI_STYLE_JRAD, 'JRAD', UI_STYLE_WEGA, 'WEGA', NULL)
INTO v_ui_type
FROM dual;
SELECT cz_config_hdrs_s.NEXTVAL
INTO last_hdr_allocated
FROM dual;
SELECT cz_config_messages_s.NEXTVAL INTO last_msg_seq_allocated FROM dual;
SELECT message
FROM cz_db_logs
WHERE run_id = p_run_id
ORDER BY logtime;
handle_deleted_flag IN VARCHAR2 DEFAULT NULL,
new_name IN VARCHAR2 DEFAULT NULL)
IS
l_api_version CONSTANT NUMBER := 1.0;
,handle_deleted_flag
,new_name
);
handle_deleted_flag IN VARCHAR2 DEFAULT NULL,
new_name IN VARCHAR2 DEFAULT NULL)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
out_config_rev_nbr,Error_message,Return_value,handle_deleted_flag, new_name);
PROCEDURE delete_configuration(config_hdr_id IN NUMBER,
config_rev_nbr IN NUMBER,
usage_exists IN OUT NOCOPY NUMBER,
Error_message IN OUT NOCOPY VARCHAR2,
Return_value IN OUT NOCOPY NUMBER)
IS
in_config_hdr_id NUMBER := config_hdr_id;
SELECT model_instantiation_type, component_instance_type
INTO l_model_instantiation_type, l_component_instance_type
FROM cz_config_hdrs
WHERE config_hdr_id = in_config_hdr_id AND config_rev_nbr = in_config_rev_nbr;
SELECT instance_hdr_id, instance_rev_nbr, component_instance_type
BULK COLLECT INTO l_instance_hdr_id_tbl, l_instance_rev_nbr_tbl, l_instance_type_tbl
FROM cz_config_items
WHERE config_hdr_id = in_config_hdr_id AND config_rev_nbr = in_config_rev_nbr
AND deleted_flag = '0'
AND component_instance_type IN (GENERIC_INSTANCE_ROOT, NETWORK_INSTANCE_ROOT);
DELETE FROM CZ_CONFIG_ATTRIBUTES
WHERE CONFIG_HDR_ID=in_config_hdr_id
AND CONFIG_REV_NBR=in_config_rev_nbr;
DELETE FROM CZ_CONFIG_ITEMS
WHERE CONFIG_HDR_ID=in_config_hdr_id
AND CONFIG_REV_NBR=in_config_rev_nbr;
DELETE FROM CZ_CONFIG_INPUTS
WHERE CONFIG_HDR_ID=in_config_hdr_id
AND CONFIG_REV_NBR=in_config_rev_nbr;
DELETE FROM CZ_CONFIG_MESSAGES
WHERE CONFIG_HDR_ID=in_config_hdr_id
AND CONFIG_REV_NBR=in_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';
EXECUTE IMMEDIATE ' DELETE FROM '||v_table_name||' WHERE CONFIG_HDR_ID = :1 AND CONFIG_REV_NBR = :2 '
USING in_config_hdr_id, in_config_rev_nbr;
DELETE FROM CZ_CONFIG_EXT_ATTRIBUTES
WHERE CONFIG_HDR_ID = l_instance_hdr_id_tbl(i)
AND CONFIG_REV_NBR = l_instance_rev_nbr_tbl(i);
DELETE FROM CZ_CONFIG_HDRS
WHERE CONFIG_HDR_ID = l_instance_hdr_id_tbl(i)
AND CONFIG_REV_NBR = l_instance_rev_nbr_tbl(i);
UPDATE cz_config_hdrs
SET to_be_deleted_flag = '1'
WHERE config_hdr_id=in_config_hdr_id and
config_rev_nbr = in_config_rev_nbr;
cz_utils.log_report('CZ_CF_API', 'delete_configuration', l_ndebug, error_message,
fnd_log.LEVEL_ERROR);
cz_utils.log_report('CZ_CF_API', 'delete_configuration', l_ndebug, error_message,
fnd_log.LEVEL_ERROR);
cz_utils.log_report('CZ_CF_API', 'delete_configuration', l_ndebug, error_message, fnd_log.LEVEL_UNEXPECTED);
END delete_configuration;
PROCEDURE delete_configuration_usage(calling_application_id IN NUMBER,
calling_application_ref_key IN NUMBER,
Error_message IN OUT NOCOPY VARCHAR2,
Return_value IN OUT NOCOPY NUMBER)
IS
in_calling_application_id NUMBER:=calling_application_id;
DELETE FROM CZ_CONFIG_USAGES WHERE CALLING_APPLICATION_ID=in_calling_application_id AND
CALLING_APPLICATION_REF_KEY=in_calling_application_ref_key;
cz_utils.log_report('CZ_CF_API', 'delete_configuration_usage', 1, error_message,
fnd_log.LEVEL_UNEXPECTED);
END delete_configuration_usage;
PROCEDURE update_configuration_usage(calling_application_id IN NUMBER,
calling_application_ref_key IN NUMBER,
config_hdr_id IN NUMBER,
config_rev_nbr IN NUMBER,
config_item_id IN NUMBER,
uom_code IN VARCHAR2,
list_price IN NUMBER,
discounted_price IN NUMBER,
auto_discount_id IN NUMBER,
auto_discount_line_id IN NUMBER,
auto_discount_pct IN NUMBER,
manual_discount_id IN NUMBER,
manual_discount_line_id IN NUMBER,
manual_discount_pct IN NUMBER,
Error_message IN OUT NOCOPY VARCHAR2,
Return_value IN OUT NOCOPY NUMBER)
IS
in_calling_application_id NUMBER:=calling_application_id;
UPDATE CZ_CONFIG_USAGES SET
LIST_PRICE=in_list_price,
AUTO_DISCOUNT_ID=in_auto_discount_id,
AUTO_DISCOUNT_LINE_ID=in_auto_discount_line_id,
AUTO_DISCOUNT_PCT=in_auto_discount_pct,
MANUAL_DISCOUNT_ID=in_manual_discount_id,
MANUAL_DISCOUNT_LINE_ID=in_manual_discount_line_id,
MANUAL_DISCOUNT_PCT=in_manual_discount_pct,
DISCOUNTED_PRICE=in_discounted_price,
UOM_CODE=in_uom_code
WHERE
CALLING_APPLICATION_ID=in_calling_application_id AND
CALLING_APPLICATION_REF_KEY=in_calling_application_ref_key AND
CONFIG_HDR_ID=in_config_hdr_id AND
CONFIG_REV_NBR=in_config_rev_nbr AND
CONFIG_ITEM_ID=in_config_item_id;
cz_utils.log_report('CZ_CF_API', 'update_configuration_usage', 1, error_message,
fnd_log.LEVEL_UNEXPECTED);
END update_configuration_usage;
SELECT inventory_item_id, instance_nbr
FROM cz_config_items
WHERE deleted_flag = '0'
AND inventory_item_id IS NOT NULL
AND config_hdr_id = p_config_hdr_id
AND config_rev_nbr = p_config_rev_nbr
START WITH config_hdr_id = p_config_hdr_id and config_rev_nbr = p_config_rev_nbr and config_item_id = p_config_item_id
CONNECT BY PRIOR parent_config_item_id = config_item_id and config_hdr_id = p_config_hdr_id and config_rev_nbr = p_config_rev_nbr
ORDER BY ROWNUM DESC;
SELECT decode(l_instance_nbr, -1,1,0,1,l_instance_nbr) into l_instance_nbr from dual;
SELECT description,concatenated_segments
INTO x_description,x_item_name
FROM mtl_system_items_kfv
WHERE mtl_system_items_kfv.inventory_item_id = p_inventory_item_id
AND mtl_system_items_kfv.organization_id = p_organization_id;
IF ( (p_message_name = ITEM_DELETE_MESSAGE) OR (p_message_name = ITEM_ADD_MESSAGE ) ) THEN
l_delta_message := CZ_UTILS.GET_TEXT(p_message_name,
'ITEMNAME',l_new_item_name,
'QUANTITY',p_current_quantity,
'COMPONENTCODE',p_component_code,
'DESCRIPTION',l_description);
insert into cz_config_messages (config_hdr_id,config_rev_nbr,constraint_type,
message,message_seq,deleted_flag)
values (p_config_hdr,p_config_rev,'ITEM DELTA',l_delta_message,l_msg_seq, '0');
IS select config_rev_nbr,config_item_id,quantity,
component_code,inventory_item_id,organization_id
from cz_config_details_v
where (config_hdr_id,config_item_id,quantity)
IN (
(select config_hdr_id,config_item_id,quantity from cz_config_details_v
where config_hdr_id = p_old_hdr and config_rev_nbr = p_old_rev
minus
select config_hdr_id,config_item_id,quantity from cz_config_details_v
where config_hdr_id = p_new_hdr and config_rev_nbr = p_new_rev )
union
(select config_hdr_id,config_item_id,quantity from cz_config_details_v
where config_hdr_id = p_new_hdr and config_rev_nbr = p_new_rev
minus
select config_hdr_id,config_item_id,quantity from cz_config_details_v
where config_hdr_id = p_old_hdr and config_rev_nbr = p_old_rev )
)
and config_rev_nbr IN (p_old_rev, p_new_rev)
ORDER BY config_item_id,config_hdr_id,config_rev_nbr;
--item deleted
l_delta_exists := 'YES';
ITEM_DELETE_MESSAGE);
----this block of code process the last record if add or delete item
IF ( l_qty_changed = FALSE ) THEN
IF (l_new_rev = v_rev_nbr) THEN
--item deleted
l_delta_exists := 'YES';
ITEM_DELETE_MESSAGE);
x_config_messages.DELETE;
procedure delete_bv_records(p_pseudo_hdr_id IN NUMBER
,p_check_db_setting IN BOOLEAN
,p_delete_ext_attr IN BOOLEAN)
IS
l_no_config_del cz_db_settings.value%TYPE;
SELECT upper(value) INTO l_no_config_del
FROM cz_db_settings
WHERE setting_id = 'BatchValConfigInputDelete';
IF p_delete_ext_attr THEN
DELETE FROM CZ_CONFIG_EXT_ATTRIBUTES
WHERE config_hdr_id = p_pseudo_hdr_id AND config_rev_nbr = 1;
DELETE FROM CZ_CONFIG_ITEMS
WHERE config_hdr_id = p_pseudo_hdr_id AND config_rev_nbr = 1;
DELETE FROM CZ_CONFIG_HDRS
WHERE config_hdr_id = p_pseudo_hdr_id AND config_rev_nbr = 1;
END delete_bv_records;
SELECT model_identifier INTO l_model_identifier FROM cz_config_hdrs
WHERE config_hdr_id = p_config_header_id AND config_rev_nbr = p_config_rev_nbr AND deleted_flag = 0;
SELECT product_key INTO x_product_key FROM cz_model_publications WHERE publication_id = x_publication_id;
INSERT INTO CZ_CONFIG_HDRS (config_hdr_id
,config_rev_nbr
,name
,effective_usage_id
,component_instance_type
,model_instantiation_type
,CONFIG_DELTA_SPEC
,deleted_flag
,HAS_FAILURES
)
VALUES (l_pseudo_hdr_id
,1
,'new batch'
,ANY_USAGE_ID
,ROOT
,BV_MODEL_TYPE
,0
,'0'
,'0'
);
ELSIF p_config_item_tbl(i).operation NOT IN (BV_OPERATION_UPDATE,BV_OPERATION_DELETE) THEN
IF p_config_item_tbl(i).operation = BV_OPERATION_INSERT THEN
l_operation_code := 'INSERT';
ELSIF p_config_item_tbl(i).operation = BV_OPERATION_UPDATE AND
p_config_item_tbl(i).instance_name = FND_API.G_MISS_CHAR THEN
set_message('CZ_BV_NULL_VAL', 'COLUMN', 'INSTANCE_NAME', 'TYPE', 'ITEM',
'IDX', i, 'SEQ', p_config_item_tbl(i).sequence_nbr);
ELSIF p_config_item_tbl(i).operation = BV_OPERATION_UPDATE THEN
l_upd_item_map(p_config_item_tbl(i).config_item_id) := p_config_item_tbl(i).config_item_id;
INSERT INTO CZ_CONFIG_ITEMS
(config_hdr_id
,config_rev_nbr
,config_item_id
,sequence_nbr
,value_type_code
,node_identifier
,item_num_val
,INSTANCE_HDR_ID
,INSTANCE_REV_NBR
,COMPONENT_INSTANCE_TYPE
,CONFIG_DELTA
,name
,location_id
,location_type_code
)
VALUES (l_pseudo_hdr_id
,1
,l_config_item_tbl(i)
,l_seq_nbr_tbl(i)
,l_operation_tbl(i)
,l_ecc_tbl(i)
,l_quantity_tbl(i)
,l_pseudo_hdr_id
,1
,'T'
,l_rec_seq_tbl(i)
,l_instance_name_tbl(i)
,l_loc_id_tbl(i)
,l_loc_type_code_tbl(i)
);
SELECT config_item_id BULK COLLECT INTO l_config_item_tbl
FROM cz_config_items
WHERE config_hdr_id = l_pseudo_hdr_id AND config_rev_nbr = 1
ORDER BY sequence_nbr, config_delta;
UPDATE cz_config_items
SET sequence_nbr = l_seq_nbr_tbl(i)
WHERE config_hdr_id = l_pseudo_hdr_id AND config_rev_nbr = 1
AND config_item_id = l_config_item_tbl(i);
l_config_item_tbl.DELETE;
l_ecc_tbl.DELETE;
l_seq_nbr_tbl.DELETE;
INSERT INTO CZ_CONFIG_ITEMS
(config_hdr_id
,config_rev_nbr
,config_item_id
,sequence_nbr
,value_type_code
,node_identifier
,INSTANCE_HDR_ID
,INSTANCE_REV_NBR
,COMPONENT_INSTANCE_TYPE
,CONFIG_DELTA
)
VALUES (l_pseudo_hdr_id
,1
,p_config_ext_attr_tbl(i).config_item_id
,i -- do not matter for no-op item
,BV_OPERATION_UPDATE
,l_ext_comp_code
,l_pseudo_hdr_id
,1
,'T'
,l_item_depth
);
INSERT INTO cz_config_ext_attributes(config_hdr_id
,config_rev_nbr
,config_item_id
,attribute_name
,attribute_group
,attribute_value
,sequence_nbr
)
VALUES(l_pseudo_hdr_id
,1
,l_config_item_tbl(i)
,l_attr_nam_tbl(i)
,l_attr_grp_tbl(i)
,l_attr_val_tbl(i)
,l_seq_nbr_tbl(i)
);
l_config_item_tbl.DELETE;
l_seq_nbr_tbl.DELETE;
SELECT attr.config_item_id BULK COLLECT INTO l_config_item_tbl
FROM cz_config_ext_attributes attr, cz_config_items item
WHERE attr.config_hdr_id = l_pseudo_hdr_id AND attr.config_rev_nbr = 1
AND attr.config_hdr_id = item.config_hdr_id AND attr.config_rev_nbr = item.config_rev_nbr
AND attr.config_item_id = item.config_item_id
ORDER BY nvl(length(translate(item.node_identifier,'-0123456789','A')),0), attr.sequence_nbr;
UPDATE cz_config_ext_attributes
SET sequence_nbr = l_seq_nbr_tbl(i)
WHERE config_hdr_id = l_pseudo_hdr_id AND config_rev_nbr = 1
AND config_item_id = l_config_item_tbl(i);
FOR i IN (SELECT message FROM cz_config_messages
WHERE config_hdr_id = l_pseudo_hdr_id AND config_rev_nbr = 1
ORDER BY message_seq) LOOP
fnd_msg_pub.Add_Exc_Msg(p_error_text => i.message);
FOR i IN (SELECT message FROM cz_config_messages
WHERE config_hdr_id = l_pseudo_hdr_id AND config_rev_nbr = 1
ORDER BY message_seq) LOOP
fnd_msg_pub.Add_Exc_Msg(p_error_text => i.message);
delete_bv_records(l_pseudo_hdr_id, TRUE, TRUE);
delete_bv_records(l_pseudo_hdr_id, FALSE, TRUE);
delete_bv_records(l_pseudo_hdr_id, FALSE, TRUE);
INSERT INTO CZ_CONFIG_HDRS (config_hdr_id
,config_rev_nbr
,name
,effective_usage_id
,deleted_flag
,CONFIG_DELTA_SPEC
,COMPONENT_INSTANCE_TYPE
,MODEL_INSTANTIATION_TYPE
,HAS_FAILURES)
VALUES (l_pseudo_hdr_id
,1
,'old batch'
,ANY_USAGE_ID
,'0'
,0
,ROOT
,BV_MODEL_TYPE
,'0');
INSERT INTO CZ_CONFIG_ITEMS(config_hdr_id
,config_rev_nbr
,config_item_id
,sequence_nbr
,node_identifier
,item_num_val
,value_type_code
,INSTANCE_HDR_ID
,INSTANCE_REV_NBR
,COMPONENT_INSTANCE_TYPE
,CONFIG_DELTA
)
VALUES (l_pseudo_hdr_id
,1
,l_item_id_tbl(i)
,l_input_seq_tbl(i)
,l_ecc_tbl(i)
,l_quantity_tbl(i)
,BV_OPERATION_OLD
,l_pseudo_hdr_id
,1
,INCLUDED
,l_item_depth_tbl(i)
);
l_item_id_tbl.delete;
l_input_seq_tbl.delete;
SELECT config_item_id BULK COLLECT INTO l_item_id_tbl
FROM cz_config_items
WHERE config_hdr_id = l_pseudo_hdr_id
AND config_rev_nbr = 1
ORDER BY config_delta, sequence_nbr;
UPDATE cz_config_items
SET sequence_nbr = l_input_seq_tbl(i)
WHERE config_hdr_id = l_pseudo_hdr_id
AND config_rev_nbr = 1
AND config_item_id = l_item_id_tbl(i);
delete_bv_records(l_pseudo_hdr_id, l_check_db_setting, FALSE);
delete_bv_records(l_pseudo_hdr_id, FALSE, FALSE);
SELECT 1 INTO l_dummy
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND bom_item_type = BOM_ITEM_TYPE_MODEL;
SELECT ui_style INTO l_pub_ui_style
FROM CZ_MODEL_PUBLICATIONS
WHERE publication_id = p_publication_id;
SELECT look_and_feel INTO look_and_feel FROM cz_ui_defs WHERE ui_def_id = v_ui_def_id;
SELECT model_id
INTO v_model_id
FROM CZ_MODEL_PUBLICATIONS
WHERE CZ_MODEL_PUBLICATIONS.publication_id = v_publication_id
AND CZ_MODEL_PUBLICATIONS.export_status = 'OK'
AND CZ_MODEL_PUBLICATIONS.deleted_flag = '0';
SELECT ui_def_id
INTO v_ui_def_id
FROM CZ_MODEL_PUBLICATIONS
WHERE CZ_MODEL_PUBLICATIONS.publication_id = v_publication_id
AND CZ_MODEL_PUBLICATIONS.export_status = 'OK'
AND CZ_MODEL_PUBLICATIONS.deleted_flag = '0';
SELECT publication_id
FROM cz_model_applicabilities_v
WHERE inventory_item_id = v_inventory_item_id
AND bom_explosion_org_id = v_organization_id
AND UPPER(publication_mode) = LTRIM(RTRIM(UPPER(v_publication_mode)))
AND fnd_application_id = v_application_id
AND usage_id = v_usage_id
AND Source_Target_Flag = v_source_target_flag
AND deleted_flag = '0'
AND language = v_language
AND (start_date <= v_config_lookup_date)
AND (v_config_lookup_date < disable_date)
ORDER BY start_date DESC;
SELECT publication_id
FROM cz_model_applicabilities_v
WHERE inventory_item_id = v_inventory_item_id
AND bom_explosion_org_id = v_organization_id
AND UPPER(publication_mode) = LTRIM(RTRIM(UPPER(v_publication_mode)))
AND usage_id = v_usage_id
AND Source_Target_Flag = v_source_target_flag
AND deleted_flag = '0'
AND language = v_language
AND start_date <= v_config_lookup_date
AND v_config_lookup_date < disable_date
ORDER BY start_date DESC;
SELECT userenv('LANG') INTO v_language FROM dual;
SELECT prj.inventory_item_id, prj.organization_id
FROM cz_devl_projects prj, cz_model_ref_expls expl
WHERE expl.model_id = p_model_id
AND expl.ps_node_type = PS_NODE_TYPE_REFERENCE
AND expl.deleted_flag = '0'
AND prj.devl_project_id = expl.component_id
AND prj.deleted_flag = '0'
AND prj.inventory_item_id IS NOT NULL
AND prj.organization_id IS NOT NULL
ORDER BY expl.node_depth;
SELECT component_id, model_identifier
INTO v_component_id, v_model_identifier
FROM CZ_CONFIG_HDRS
WHERE config_hdr_id = v_config_hdr_id AND config_rev_nbr = v_config_rev_nbr;
SELECT inventory_item_id, organization_id, product_key
INTO v_inventory_item_id, v_organization_id, v_product_key
FROM CZ_DEVL_PROJECTS
WHERE deleted_flag = '0' AND devl_project_id = v_component_id;
SELECT inventory_item_id, organization_id
INTO v_inventory_item_id, v_organization_id
FROM cz_config_items
WHERE config_hdr_id = v_config_hdr_id AND config_rev_nbr = v_config_rev_nbr
AND deleted_flag = '0' AND inventory_item_id IS NOT NULL
AND to_char(inventory_item_id) = node_identifier;
SELECT publication_id
FROM cz_model_applicabilities_v
WHERE product_key = v_product_key
AND UPPER(publication_mode) = LTRIM(RTRIM(UPPER(v_publication_mode)))
AND fnd_application_id = v_application_id
AND usage_id = v_usage_id
AND Source_Target_Flag = v_source_target_flag
AND deleted_flag = '0'
AND language = v_language
AND (start_date <= v_config_lookup_date)
AND (v_config_lookup_date < disable_date)
ORDER BY start_date DESC;
SELECT publication_id
FROM cz_model_applicabilities_v
WHERE product_key = v_product_key
AND UPPER(publication_mode) = LTRIM(RTRIM(UPPER(v_publication_mode)))
AND usage_id = v_usage_id
AND Source_Target_Flag = v_source_target_flag
AND deleted_flag = '0'
AND language = v_language
AND (start_date <= v_config_lookup_date)
AND (v_config_lookup_date < disable_date)
ORDER BY start_date DESC;
SELECT userenv('LANG') INTO v_language FROM dual;
SELECT creation_date, effective_date INTO l_config_creation_date,
l_config_effective_date FROM CZ_CONFIG_HDRS WHERE config_hdr_id =
p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr;
SELECT NVL(p_creation_date, NVL(l_config_creation_date, SYSDATE)) INTO
p_creation_date FROM dual;
SELECT value
INTO l_rest_cfg_lookup_setting
FROM cz_db_settings WHERE setting_id = 'RestoredConfigDefaultModelLookupDate';
SELECT NVL(p_lookup_date, SYSDATE) INTO p_lookup_date FROM dual;
SELECT NVL(p_effective_date, NVL(l_config_effective_date, SYSDATE)) INTO
p_effective_date FROM dual;
select ORGANIZATION_ID, ASSEMBLY_ITEM_ID
into COMMON_ORGANIZATION_ID, COMMON_INVENTORY_ITEM_ID
from BOM_BILL_OF_MATERIALS
where BILL_SEQUENCE_ID in (select SOURCE_BILL_SEQUENCE_ID from BOM_BILL_OF_MATERIALS
where ORGANIZATION_ID = in_organization_id
and ASSEMBLY_ITEM_ID = in_inventory_item_id);
,x_last_update_dates OUT NOCOPY date_tbl_indexby_type
,x_model_type OUT NOCOPY VARCHAR2
)
IS
TYPE model_type_tbl_type IS TABLE OF cz_devl_projects.model_type%TYPE
INDEX BY BINARY_INTEGER;
SELECT pub.publication_id, pub.model_id, pub.ui_def_id, pub.start_date,
pub.last_update_date, prj.model_type
BULK COLLECT INTO x_publication_ids, x_model_ids, x_ui_def_ids,
x_start_dates, x_last_update_dates, l_model_type_tbl
FROM cz_model_applicabilities_v pub, cz_devl_projects prj
WHERE pub.model_id = prj.devl_project_id
AND prj.deleted_flag ='0' AND pub.deleted_flag = '0'
AND pub.inventory_item_id = p_inventory_item_id
AND bom_explosion_org_id = p_organization_id
AND fnd_application_id = p_application_id
AND usage_id = p_usage_id AND language = p_language
AND UPPER(publication_mode) IN ( cz_api_pub.G_PRODUCTION_PUB_MODE , 'T')
AND source_target_flag = TARGET_PUBLICATION
AND ui_style = UI_STYLE_DHTML
AND ( (p_pub_start_date >= start_date AND p_pub_start_date < disable_date) OR
(P_pub_end_date > start_date AND p_pub_end_date <= disable_date) OR
(start_date >= p_pub_start_date AND start_date < p_pub_end_date) OR
(disable_date > p_pub_start_date AND disable_date <= p_pub_end_date) );
,x_last_update_date_tbl OUT NOCOPY date_tbl_indexby_type
,x_model_type OUT NOCOPY VARCHAR2
)
IS
l_usage_name VARCHAR2(255) := p_usage_name;
,x_last_update_date_tbl
,x_model_type
);
,x_last_update_date_tbl
,x_model_type
);
,x_last_update_date_tbl
,x_model_type
);
SELECT product_key
FROM CZ_DEVL_PROJECTS
WHERE persistent_project_id = v_persist_comp_id
AND product_key IS NOT NULL
AND deleted_flag = '0';
SELECT persistent_component_id, model_identifier
INTO v_persist_comp_id, v_model_identifier
FROM CZ_CONFIG_HDRS
WHERE config_hdr_id = v_config_hdr_id AND config_rev_nbr = v_config_rev_nbr;
SELECT pool_identifier INTO v_pool_token FROM cz_model_pool_mappings WHERE model_product_key = p_product_key;
SELECT value INTO v_pool_token FROM cz_db_settings WHERE section_name = 'ORAAPPS_INTEGRATE' AND setting_id = 'DefaultPoolIdentifier';
SELECT A.ASSEMBLY_ITEM_ID FROM CZ_EXV_BILL_OF_MATERIALS A,CZ_EXV_MTL_SYSTEM_ITEMS B
WHERE A.ASSEMBLY_ITEM_ID=B.INVENTORY_ITEM_ID
AND A.ORGANIZATION_ID=B.ORGANIZATION_ID
AND B.BOM_ITEM_TYPE=1
AND A.ORGANIZATION_ID= Org_ID
AND B.CONCATENATED_SEGMENTS = p_inventory_item_id;
SELECT ORGANIZATION_ID INTO nOrg_ID FROM CZ_EXV_ORGANIZATIONS
WHERE ORGANIZATION_CODE = p_org_id;
SELECT COUNT(*)
INTO v_count
FROM cz_model_pool_mappings
WHERE model_product_key = p_model_product_key ;
INSERT INTO cz_model_pool_mappings(pool_identifier, model_product_key)
VALUES(p_pool_identifier, p_model_product_key);
SELECT A.ASSEMBLY_ITEM_ID FROM CZ_EXV_BILL_OF_MATERIALS A,CZ_EXV_MTL_SYSTEM_ITEMS B
WHERE A.ASSEMBLY_ITEM_ID=B.INVENTORY_ITEM_ID
AND A.ORGANIZATION_ID=B.ORGANIZATION_ID
AND B.BOM_ITEM_TYPE=1
AND A.ORGANIZATION_ID= Org_ID
AND B.CONCATENATED_SEGMENTS = p_inventory_item_id;
SELECT ORGANIZATION_ID INTO nOrg_ID FROM CZ_EXV_ORGANIZATIONS
WHERE ORGANIZATION_CODE = p_org_id;
DELETE FROM cz_model_pool_mappings WHERE pool_identifier = p_pool_identifier
AND model_product_key = p_model_product_key;
DELETE FROM cz_model_pool_mappings WHERE pool_identifier = p_pool_identifier;
SELECT orig_sys_ref, NVL(config_engine_type,'L') INTO lOSR, lConfigEngineType
FROM cz_devl_projects d
WHERE d.deleted_flag = '0'
AND d.devl_project_id = inDevlProjectId
AND EXISTS (SELECT NULL FROM CZ_RP_ENTRIES WHERE deleted_flag = '0'
AND object_type = 'PRJ' AND object_id = d.devl_project_id)
AND orig_sys_ref IS NOT NULL;
SELECT d.devl_project_id INTO lModelId FROM cz_devl_projects d
WHERE d.orig_sys_ref = lOSR
AND d.deleted_flag = '0'
AND NVL(d.import_enabled,'0') = '1'
AND NVL(d.config_engine_type,'L') = lConfigEngineType
AND EXISTS (SELECT NULL FROM CZ_RP_ENTRIES WHERE deleted_flag = '0'
AND object_type = 'PRJ' AND object_id = d.devl_project_id) ;
UPDATE cz_devl_projects SET import_enabled = '1'
WHERE deleted_flag = '0'
AND devl_project_id = inDevlProjectId ;
SELECT orig_sys_ref INTO lOSR FROM cz_devl_projects d
WHERE d.deleted_flag = '0'
AND d.devl_project_id = inDevlProjectId
AND EXISTS (SELECT NULL FROM CZ_RP_ENTRIES WHERE deleted_flag = '0'
AND object_type = 'PRJ' AND object_id = d.devl_project_id)
AND orig_sys_ref IS NOT NULL ;
UPDATE cz_devl_projects SET import_enabled = '0'
WHERE deleted_flag = '0'
AND devl_project_id = inDevlProjectId ;