The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 1
FROM qa_plan_transactions
WHERE plan_id = p_plan_id
AND transaction_number = qa_ss_const.ss_outside_processing_txn
AND enabled_flag = 1;
SELECT 1
FROM qa_plan_transactions
WHERE plan_id = p_plan_id
AND transaction_number = qa_ss_const.ss_shipments_txn
AND enabled_flag = 1;
SELECT 1
FROM qa_plan_transactions
WHERE plan_id = p_plan_id
AND transaction_number = 31
AND enabled_flag = 1;
SELECT 1
FROM qa_plan_transactions
WHERE plan_id = p_plan_id
AND transaction_number = 32
AND enabled_flag = 1;
SELECT 1
FROM qa_plan_transactions
WHERE plan_id = p_plan_id
AND transaction_number = 33
AND enabled_flag = 1;
SELECT 1
FROM qa_pc_plan_relationship
WHERE parent_plan_id = p_plan_id
OR child_plan_id = p_plan_id;
SELECT prompt
FROM qa_plan_chars
WHERE plan_id = p_plan_id
AND char_id = p_element_id;
SELECT prompt_sequence
FROM qa_plan_chars
WHERE plan_id = p_plan_id
AND char_id = p_element_id;
ELSIF (p_appendix = g_last_update_date_attribute) THEN
label := fnd_message.get_string('QA','QA_SS_ENTRY_DATE');
ELSIF (INSTR(p_code, g_last_update_date_attribute) <> 0) THEN
column_name := 'LAST_UPDATE_DATE';
SELECT 'TRUE'
FROM qa_txn_collection_triggers
WHERE transaction_number = p_txn_number
AND search_flag = 1
AND collection_trigger_id = p_element_id;
SELECT MAX(display_sequence)
FROM ak_region_items
WHERE region_code = p_region_code
AND region_application_id = p_region_application_id;
SELECT MAX(display_sequence)
FROM ak_region_items
WHERE region_code = p_region_code
AND region_application_id = p_region_application_id
AND item_style <> 'HIDDEN';
x_parents.delete();
SELECT 1
FROM DUAL
WHERE p_char_id
IN
(SELECT assigned_char_id
FROM qa_plan_char_actions
WHERE plan_char_action_trigger_id
IN
(SELECT plan_char_action_trigger_id
FROM qa_plan_char_action_triggers
WHERE plan_id = p_plan_id));
SELECT 'TRUE'
FROM ak_attributes
WHERE attribute_application_id = p_attribute_application_id
AND attribute_code = p_attribute_code;
PROCEDURE delete_attribute_for_plan (
p_attribute_code IN VARCHAR2,
p_attribute_application_id IN NUMBER) IS
BEGIN
-- Deletes an attribute (corresponds to a collection plan)
-- if the combination already exists.
IF attribute_exists(p_attribute_application_id, p_attribute_code) THEN
-- dbms_output.put_line('Deleting Element : ' || p_attribute_code);
ak_attributes_pkg.delete_row(p_attribute_application_id,
p_attribute_code);
END delete_attribute_for_plan;
PROCEDURE delete_element_mapping (
p_char_id IN NUMBER,
p_attribute_application_id IN NUMBER) IS
l_attribute_code VARCHAR2(30);
ak_attributes_pkg.delete_row(p_attribute_application_id,
l_attribute_code);
END delete_element_mapping;
PROCEDURE delete_region (
p_region_application_id IN NUMBER,
p_region_code IN VARCHAR2) IS
l_row_exists VARCHAR2(6);
SELECT 'TRUE'
FROM ak_regions
WHERE region_application_id = p_region_application_id
AND region_code = p_region_code;
ak_regions_pkg.delete_row(p_region_application_id,
p_region_code);
END delete_region;
PROCEDURE delete_a_lov_relation (
p_region_application_id IN NUMBER,
p_region_code IN VARCHAR2,
p_attribute_application_id IN NUMBER,
p_attribute_code IN VARCHAR2,
p_lov_region_appl_id IN NUMBER,
p_lov_region_code IN VARCHAR2,
p_lov_attribute_appl_id IN NUMBER,
p_lov_attribute_code IN VARCHAR2,
p_base_attribute_appl_id IN NUMBER,
p_base_attribute_code IN VARCHAR2,
p_direction_flag IN VARCHAR2) IS
l_row_exists VARCHAR2(4);
SELECT 'TRUE'
FROM ak_region_lov_relations
WHERE region_application_id = p_region_application_id
AND region_code = p_region_code
AND attribute_application_id = p_attribute_application_id
AND attribute_code = p_attribute_code
AND lov_region_appl_id = p_lov_region_appl_id
AND lov_region_code = p_lov_region_code
AND lov_attribute_appl_id = p_lov_attribute_appl_id
AND lov_attribute_code = p_lov_attribute_code
AND base_attribute_appl_id = p_base_attribute_appl_id
AND base_attribute_code = p_base_attribute_code
AND direction_flag = p_direction_flag ;
AK_LOV_RELATIONS_PKG.DELETE_ROW (
X_REGION_APPLICATION_ID => p_region_application_id,
X_REGION_CODE => p_region_code,
X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
X_ATTRIBUTE_CODE => p_attribute_code,
X_LOV_REGION_APPL_ID => p_lov_region_appl_id,
X_LOV_REGION_CODE => p_lov_region_code,
X_LOV_ATTRIBUTE_APPL_ID => p_lov_attribute_appl_id,
X_LOV_ATTRIBUTE_CODE => p_lov_attribute_code,
X_BASE_ATTRIBUTE_APPL_ID => p_base_attribute_appl_id,
X_BASE_ATTRIBUTE_CODE => p_base_attribute_code,
X_DIRECTION_FLAG => p_direction_flag);
END delete_a_lov_relation;
PROCEDURE delete_lov_relations (
p_char_id IN NUMBER,
p_region_application_id IN NUMBER,
p_region_code IN VARCHAR2,
p_attribute_application_id IN NUMBER,
p_attribute_code IN VARCHAR2,
p_lov_region_appl_id IN NUMBER,
p_lov_region_code IN VARCHAR2) IS
l_lov_attribute_code VARCHAR2(30);
delete_a_lov_relation(
p_region_application_id => p_region_application_id,
p_region_code => p_region_code,
p_attribute_application_id => p_attribute_application_id,
p_attribute_code => p_attribute_code,
p_lov_region_appl_id => p_lov_region_appl_id,
p_lov_region_code => p_lov_region_code,
p_lov_attribute_appl_id => g_application_id,
p_lov_attribute_code => g_lov_attribute_code,
p_base_attribute_appl_id => p_attribute_application_id,
p_base_attribute_code => p_attribute_code,
p_direction_flag => 'CRITERIA');
delete_a_lov_relation(
p_region_application_id => p_region_application_id,
p_region_code => p_region_code,
p_attribute_application_id => p_attribute_application_id,
p_attribute_code => p_attribute_code,
p_lov_region_appl_id => p_lov_region_appl_id,
p_lov_region_code => p_lov_region_code,
p_lov_attribute_appl_id => g_application_id,
p_lov_attribute_code => g_lov_attribute_code,
p_base_attribute_appl_id => p_attribute_application_id,
p_base_attribute_code => p_attribute_code,
p_direction_flag => 'RESULT');
delete_a_lov_relation(
p_region_application_id => p_region_application_id,
p_region_code => p_region_code,
p_attribute_application_id => p_attribute_application_id,
p_attribute_code => p_attribute_code,
p_lov_region_appl_id => p_lov_region_appl_id,
p_lov_region_code => p_lov_region_code,
p_lov_attribute_appl_id => g_application_id,
p_lov_attribute_code => g_lov_attribute_org_id,
p_base_attribute_appl_id => p_attribute_application_id,
p_base_attribute_code => g_org_id_attribute,
p_direction_flag => 'PASSIVE_CRITERIA');
delete_a_lov_relation(
p_region_application_id => p_region_application_id,
p_region_code => p_region_code,
p_attribute_application_id => p_attribute_application_id,
p_attribute_code => p_attribute_code,
p_lov_region_appl_id => p_lov_region_appl_id,
p_lov_region_code => p_lov_region_code,
p_lov_attribute_appl_id => g_application_id,
p_lov_attribute_code => g_lov_attribute_plan_id,
p_base_attribute_appl_id => p_attribute_application_id,
p_base_attribute_code => g_plan_id_attribute,
p_direction_flag => 'PASSIVE_CRITERIA');
delete_a_lov_relation(
p_region_application_id => p_region_application_id,
p_region_code => p_region_code,
p_attribute_application_id => p_attribute_application_id,
p_attribute_code => p_attribute_code,
p_lov_region_appl_id => p_lov_region_appl_id,
p_lov_region_code => p_lov_region_code,
p_lov_attribute_appl_id => g_application_id,
p_lov_attribute_code => l_lov_attribute_code,
p_base_attribute_appl_id => p_attribute_application_id,
p_base_attribute_code => l_base_attribute_code,
p_direction_flag => 'PASSIVE_CRITERIA');
END delete_lov_relations;
PROCEDURE delete_region_item (
p_region_application_id IN NUMBER,
p_region_code IN VARCHAR2,
p_attribute_application_id IN NUMBER,
p_attribute_code IN VARCHAR2) IS
l_row_exists VARCHAR2(4);
SELECT 'TRUE'
FROM ak_region_items
WHERE region_application_id = p_region_application_id
AND region_code = p_region_code
AND attribute_application_id = p_attribute_application_id
AND attribute_code = p_attribute_code;
ak_region_items_pkg.delete_row(
p_region_application_id,
p_region_code,
p_attribute_application_id,
p_attribute_code);
delete_lov_relations(
P_CHAR_ID => l_char_id,
P_REGION_APPLICATION_ID => g_application_id,
P_REGION_CODE => p_region_code,
P_ATTRIBUTE_APPLICATION_ID => g_application_id,
P_ATTRIBUTE_CODE => p_attribute_code,
P_LOV_REGION_APPL_ID => g_application_id,
P_LOV_REGION_CODE => g_lov_region);
END delete_region_item;
l_last_updated_by OUT NOCOPY NUMBER,
l_last_update_login OUT NOCOPY NUMBER) IS
l_qa_chars_row qa_chars%ROWTYPE;
SELECT * from qa_chars
WHERE char_id = p_char_id;
l_last_updated_by := l_qa_chars_row.last_updated_by;
l_last_update_login := nvl(l_qa_chars_row.last_update_login,
l_last_updated_by);
l_last_updated_by OUT NOCOPY NUMBER,
l_last_update_login OUT NOCOPY NUMBER) IS
l_qa_plans_row qa_plans%ROWTYPE;
SELECT * from qa_plans
WHERE plan_id = p_plan_id;
l_last_updated_by := l_qa_plans_row.last_updated_by;
l_last_update_login := l_qa_plans_row.last_update_login;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
delete_element_mapping (p_char_id, p_attribute_application_id);
l_last_updated_by, l_last_update_login);
ak_attributes_pkg.insert_row (
X_ROWID => l_row_id,
X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
X_ATTRIBUTE_CODE => l_attribute_code,
X_ATTRIBUTE_LABEL_LENGTH => l_label_length,
X_ATTRIBUTE_VALUE_LENGTH => 150,
X_BOLD => 'N',
X_ITALIC => 'N',
X_UPPER_CASE_FLAG => 'N',
X_VERTICAL_ALIGNMENT => 'TOP',
X_HORIZONTAL_ALIGNMENT => 'LEFT' ,
X_DEFAULT_VALUE_VARCHAR2 => l_varchar2_default,
X_DEFAULT_VALUE_NUMBER => l_number_default,
X_DEFAULT_VALUE_DATE => l_date_default,
X_LOV_REGION_CODE => null,
X_LOV_REGION_APPLICATION_ID => null,
X_DATA_TYPE => l_data_type,
X_DISPLAY_HEIGHT => 1,
X_ITEM_STYLE => l_item_style,
X_CSS_CLASS_NAME => null,
X_CSS_LABEL_CLASS_NAME => null,
X_POPLIST_VIEWOBJECT => null,
X_POPLIST_DISPLAY_ATTRIBUTE => null,
X_POPLIST_VALUE_ATTRIBUTE => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_NAME => l_name,
X_ATTRIBUTE_LABEL_LONG => l_label,
X_DESCRIPTION => 'Quality Attribute',
X_CREATION_DATE => g_sysdate,
X_CREATED_BY => l_created_by,
X_LAST_UPDATE_DATE => g_sysdate,
X_LAST_UPDATED_BY => l_last_updated_by,
X_LAST_UPDATE_LOGIN => l_last_update_login);
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
delete_attribute_for_plan (l_attribute_code, p_attribute_application_id);
l_item_style, l_name, l_label, l_created_by, l_last_updated_by,
l_last_update_login);
ak_attributes_pkg.insert_row (
X_ROWID => l_row_id,
X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
X_ATTRIBUTE_CODE => l_attribute_code,
X_ATTRIBUTE_LABEL_LENGTH => l_label_length,
X_ATTRIBUTE_VALUE_LENGTH => 150,
X_BOLD => 'N',
X_ITALIC => 'N',
X_UPPER_CASE_FLAG => 'N',
X_VERTICAL_ALIGNMENT => 'TOP',
X_HORIZONTAL_ALIGNMENT => 'LEFT' ,
X_DEFAULT_VALUE_VARCHAR2 => l_varchar2_default,
X_DEFAULT_VALUE_NUMBER => l_number_default,
X_DEFAULT_VALUE_DATE => l_date_default,
X_LOV_REGION_CODE => null,
X_LOV_REGION_APPLICATION_ID => null,
X_DATA_TYPE => l_data_type,
X_DISPLAY_HEIGHT => 1,
X_ITEM_STYLE => l_item_style,
X_CSS_CLASS_NAME => null,
X_CSS_LABEL_CLASS_NAME => null,
X_POPLIST_VIEWOBJECT => null,
X_POPLIST_DISPLAY_ATTRIBUTE => null,
X_POPLIST_VALUE_ATTRIBUTE => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_NAME => l_name,
X_ATTRIBUTE_LABEL_LONG => l_label,
X_DESCRIPTION => 'Quality Attribute',
X_CREATION_DATE => g_sysdate,
X_CREATED_BY => l_created_by,
X_LAST_UPDATE_DATE => g_sysdate,
X_LAST_UPDATED_BY => l_last_updated_by,
X_LAST_UPDATE_LOGIN => l_last_update_login);
SELECT * from qa_plans
WHERE plan_id = p_plan_id;
delete_region (p_region_application_id, l_region_code);
AK_REGIONS_PKG.insert_row (
X_ROWID => l_row_id,
X_REGION_APPLICATION_ID => p_region_application_id,
X_REGION_CODE => l_region_code,
X_DATABASE_OBJECT_NAME => 'ICX_PROMPTS',
X_REGION_STYLE => l_region_style,
X_NUM_COLUMNS => null,
X_ICX_CUSTOM_CALL => null ,
X_NAME => l_qa_plans_row.name,
X_DESCRIPTION => l_qa_plans_row.description,
X_REGION_DEFAULTING_API_PKG => null,
X_REGION_DEFAULTING_API_PROC => null,
X_REGION_VALIDATION_API_PKG => null,
X_REGION_VALIDATION_API_PROC => null,
X_APPL_MODULE_OBJECT_TYPE => null,
X_NUM_ROWS_DISPLAY => null,
X_REGION_OBJECT_TYPE => null,
X_IMAGE_FILE_NAME => null,
X_ISFORM_FLAG => 'N',
X_HELP_TARGET => null,
X_STYLE_SHEET_FILENAME => null,
X_VERSION => null,
X_APPLICATIONMODULE_USAGE_NAME => null,
X_ADD_INDEXED_CHILDREN => 'Y',
X_STATEFUL_FLAG => null, --5.5
X_FUNCTION_NAME => null, --5.5
X_CHILDREN_VIEW_USAGE_NAME => null, --5.5
X_SEARCH_PANEL => null, --5.5
X_ADVANCED_SEARCH_PANEL => null, --5.5
X_CUSTOMIZE_PANEL => null, --5.5
X_DEFAULT_SEARCH_PANEL => null, --5.5
X_RESULTS_BASED_SEARCH => null, --5.5
X_DISPLAY_GRAPH_TABLE => null, --5.5
X_CREATION_DATE => g_sysdate,
X_CREATED_BY => l_qa_plans_row.created_by,
X_LAST_UPDATE_DATE => g_sysdate,
X_LAST_UPDATED_BY => l_qa_plans_row.last_updated_by,
X_LAST_UPDATE_LOGIN => l_qa_plans_row.last_update_login,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null);
SELECT * from qa_plans
WHERE plan_id = p_plan_id;
AK_LOV_RELATIONS_PKG.INSERT_ROW (
X_ROWID => l_row_id,
X_REGION_APPLICATION_ID => p_region_application_id,
X_REGION_CODE => p_region_code,
X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
X_ATTRIBUTE_CODE => p_attribute_code,
X_LOV_REGION_APPL_ID => p_lov_region_appl_id,
X_LOV_REGION_CODE => g_lov_region,
X_LOV_ATTRIBUTE_APPL_ID => g_application_id,
X_LOV_ATTRIBUTE_CODE => g_lov_attribute_code,
X_BASE_ATTRIBUTE_APPL_ID => p_attribute_application_id,
X_BASE_ATTRIBUTE_CODE => p_attribute_code,
X_DIRECTION_FLAG => 'CRITERIA',
X_REQUIRED_FLAG => 'N',
X_LAST_UPDATE_DATE => l_qa_plans_row.last_update_date,
X_LAST_UPDATED_BY => l_qa_plans_row.last_updated_by,
X_CREATION_DATE => l_qa_plans_row.creation_date,
X_CREATED_BY => l_qa_plans_row.created_by,
X_LAST_UPDATE_LOGIN => l_qa_plans_row.last_update_login);
AK_LOV_RELATIONS_PKG.INSERT_ROW (
X_ROWID => l_row_id,
X_REGION_APPLICATION_ID => p_region_application_id,
X_REGION_CODE => p_region_code,
X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
X_ATTRIBUTE_CODE => p_attribute_code,
X_LOV_REGION_APPL_ID => p_lov_region_appl_id,
X_LOV_REGION_CODE => g_lov_region,
X_LOV_ATTRIBUTE_APPL_ID => g_application_id,
X_LOV_ATTRIBUTE_CODE => g_lov_attribute_code,
X_BASE_ATTRIBUTE_APPL_ID => p_attribute_application_id,
X_BASE_ATTRIBUTE_CODE => p_attribute_code,
X_DIRECTION_FLAG => 'RESULT',
X_REQUIRED_FLAG => 'N',
X_LAST_UPDATE_DATE => l_qa_plans_row.last_update_date,
X_LAST_UPDATED_BY => l_qa_plans_row.last_updated_by,
X_CREATION_DATE => l_qa_plans_row.creation_date,
X_CREATED_BY => l_qa_plans_row.created_by,
X_LAST_UPDATE_LOGIN => l_qa_plans_row.last_update_login);
AK_LOV_RELATIONS_PKG.INSERT_ROW (
X_ROWID => l_row_id,
X_REGION_APPLICATION_ID => p_region_application_id,
X_REGION_CODE => p_region_code,
X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
X_ATTRIBUTE_CODE => p_attribute_code,
X_LOV_REGION_APPL_ID => p_lov_region_appl_id,
X_LOV_REGION_CODE => p_lov_region_code,
X_LOV_ATTRIBUTE_APPL_ID => g_application_id,
X_LOV_ATTRIBUTE_CODE => g_lov_attribute_org_id,
X_BASE_ATTRIBUTE_APPL_ID => p_attribute_application_id,
X_BASE_ATTRIBUTE_CODE => g_org_id_attribute,
X_DIRECTION_FLAG => 'PASSIVE_CRITERIA',
X_REQUIRED_FLAG => 'N',
X_LAST_UPDATE_DATE => l_qa_plans_row.last_update_date,
X_LAST_UPDATED_BY => l_qa_plans_row.last_updated_by,
X_CREATION_DATE => l_qa_plans_row.creation_date,
X_CREATED_BY => l_qa_plans_row.created_by,
X_LAST_UPDATE_LOGIN => l_qa_plans_row.last_update_login);
AK_LOV_RELATIONS_PKG.INSERT_ROW (
X_ROWID => l_row_id,
X_REGION_APPLICATION_ID => p_region_application_id,
X_REGION_CODE => p_region_code,
X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
X_ATTRIBUTE_CODE => p_attribute_code,
X_LOV_REGION_APPL_ID => p_lov_region_appl_id,
X_LOV_REGION_CODE => p_lov_region_code,
X_LOV_ATTRIBUTE_APPL_ID => g_application_id,
X_LOV_ATTRIBUTE_CODE => g_lov_attribute_plan_id,
X_BASE_ATTRIBUTE_APPL_ID => p_attribute_application_id,
X_BASE_ATTRIBUTE_CODE => g_plan_id_attribute,
X_DIRECTION_FLAG => 'PASSIVE_CRITERIA',
X_REQUIRED_FLAG => 'N',
X_LAST_UPDATE_DATE => l_qa_plans_row.last_update_date,
X_LAST_UPDATED_BY => l_qa_plans_row.last_updated_by,
X_CREATION_DATE => l_qa_plans_row.creation_date,
X_CREATED_BY => l_qa_plans_row.created_by,
X_LAST_UPDATE_LOGIN => l_qa_plans_row.last_update_login);
AK_LOV_RELATIONS_PKG.INSERT_ROW (
X_ROWID => l_row_id,
X_REGION_APPLICATION_ID => p_region_application_id,
X_REGION_CODE => p_region_code,
X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
X_ATTRIBUTE_CODE => p_attribute_code,
X_LOV_REGION_APPL_ID => p_lov_region_appl_id,
X_LOV_REGION_CODE => p_lov_region_code,
X_LOV_ATTRIBUTE_APPL_ID => g_application_id,
X_LOV_ATTRIBUTE_CODE => l_lov_attribute_code,
X_BASE_ATTRIBUTE_APPL_ID => p_attribute_application_id,
X_BASE_ATTRIBUTE_CODE => l_base_attribute_code,
X_DIRECTION_FLAG => 'PASSIVE_CRITERIA',
X_REQUIRED_FLAG => 'N',
X_LAST_UPDATE_DATE => l_qa_plans_row.last_update_date,
X_LAST_UPDATED_BY => l_qa_plans_row.last_updated_by,
X_CREATION_DATE => l_qa_plans_row.creation_date,
X_CREATED_BY => l_qa_plans_row.created_by,
X_LAST_UPDATE_LOGIN => l_qa_plans_row.last_update_login);
SELECT *
FROM qa_plan_chars
WHERE plan_id = p_plan_id
AND char_id = p_char_id;
SELECT *
FROM qa_chars
WHERE char_id = p_char_id;
AK_REGION_ITEMS_PKG.insert_row (
X_ROWID => l_row_id,
X_REGION_APPLICATION_ID => p_region_application_id,
X_REGION_CODE => l_region_code,
X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
X_ATTRIBUTE_CODE => l_attribute_code,
X_DISPLAY_SEQUENCE => l_display_sequence,
X_NODE_DISPLAY_FLAG => l_display_flag,
X_NODE_QUERY_FLAG => 'N',
X_ATTRIBUTE_LABEL_LENGTH => length(l_qa_chars_row.prompt),
X_BOLD => 'N',
X_ITALIC => 'N',
X_VERTICAL_ALIGNMENT => 'TOP',
X_HORIZONTAL_ALIGNMENT => 'LEFT',
X_ITEM_STYLE => l_item_style,
X_OBJECT_ATTRIBUTE_FLAG => 'N',
X_ATTRIBUTE_LABEL_LONG => l_qa_chars_row.prompt,
X_DESCRIPTION => l_qa_chars_row.name,
X_SECURITY_CODE => null,
X_UPDATE_FLAG => 'Y',
X_REQUIRED_FLAG => l_required_flag,
X_DISPLAY_VALUE_LENGTH => l_qa_chars_row.display_length,
X_LOV_REGION_APPLICATION_ID => g_application_id,
X_LOV_REGION_CODE => l_lov_region_code,
X_LOV_FOREIGN_KEY_NAME => null,
X_LOV_ATTRIBUTE_APPLICATION_ID => null,
X_LOV_ATTRIBUTE_CODE => null,
X_LOV_DEFAULT_FLAG => null,
X_REGION_DEFAULTING_API_PKG => null,
X_REGION_DEFAULTING_API_PROC => null,
X_REGION_VALIDATION_API_PKG => null,
X_REGION_VALIDATION_API_PROC => null,
X_ORDER_SEQUENCE => null,
X_ORDER_DIRECTION => null,
X_DEFAULT_VALUE_VARCHAR2 => l_varchar2_default,
X_DEFAULT_VALUE_NUMBER => l_number_default,
X_DEFAULT_VALUE_DATE => l_date_default,
X_ITEM_NAME => l_attribute_code,
X_DISPLAY_HEIGHT => 1,
X_SUBMIT => 'N',
X_ENCRYPT => 'N',
X_VIEW_USAGE_NAME => g_eqr_view_usage_name,
X_VIEW_ATTRIBUTE_NAME => l_vo_attribute_name,
X_CSS_CLASS_NAME => null,
X_CSS_LABEL_CLASS_NAME => null,
X_URL => null,
X_POPLIST_VIEWOBJECT => null,
X_POPLIST_DISPLAY_ATTRIBUTE => null,
X_POPLIST_VALUE_ATTRIBUTE => null,
X_IMAGE_FILE_NAME => null,
X_NESTED_REGION_CODE => null,
X_NESTED_REGION_APPL_ID => null,
X_MENU_NAME => null,
X_FLEXFIELD_NAME => null,
X_FLEXFIELD_APPLICATION_ID => null,
X_TABULAR_FUNCTION_CODE => null,
X_TIP_TYPE => null,
X_TIP_MESSAGE_NAME => null,
X_TIP_MESSAGE_APPLICATION_ID => null,
X_ENTITY_ID => null,
X_FLEX_SEGMENT_LIST => null,
X_ANCHOR => null,
X_POPLIST_VIEW_USAGE_NAME => null,
X_USER_CUSTOMIZABLE => null, --5.5
X_SORTBY_VIEW_ATTRIBUTE_NAME => null, --5.5
X_CREATION_DATE => g_sysdate,
X_CREATED_BY => l_qa_plan_chars_row.created_by,
X_LAST_UPDATE_DATE => g_sysdate,
X_LAST_UPDATED_BY => l_qa_plan_chars_row.last_updated_by,
X_LAST_UPDATE_LOGIN => l_qa_plan_chars_row.last_updated_by,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null);
l_update_flag VARCHAR2(1) DEFAULT 'Y';
l_update_flag := 'N';
AK_REGION_ITEMS_PKG.insert_row (
X_ROWID => l_row_id,
X_REGION_APPLICATION_ID => p_region_application_id,
X_REGION_CODE => l_region_code,
X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
X_ATTRIBUTE_CODE => p_attribute_code,
X_DISPLAY_SEQUENCE => l_display_sequence,
X_NODE_DISPLAY_FLAG => l_display_flag,
X_NODE_QUERY_FLAG => l_query_flag,
X_ATTRIBUTE_LABEL_LENGTH => 13,
X_BOLD => 'N',
X_ITALIC => 'N',
X_VERTICAL_ALIGNMENT => 'TOP',
X_HORIZONTAL_ALIGNMENT => 'LEFT',
X_ITEM_STYLE => l_item_style,
X_OBJECT_ATTRIBUTE_FLAG => 'N',
X_ATTRIBUTE_LABEL_LONG => l_label_long,
X_DESCRIPTION => 'Quality Column',
X_SECURITY_CODE => null,
X_UPDATE_FLAG => l_update_flag,
X_REQUIRED_FLAG => 'N',
X_DISPLAY_VALUE_LENGTH => 0,
X_LOV_REGION_APPLICATION_ID => null,
X_LOV_REGION_CODE => null,
X_LOV_FOREIGN_KEY_NAME => null,
X_LOV_ATTRIBUTE_APPLICATION_ID => null,
X_LOV_ATTRIBUTE_CODE => null,
X_LOV_DEFAULT_FLAG => null,
X_REGION_DEFAULTING_API_PKG => null,
X_REGION_DEFAULTING_API_PROC => null,
X_REGION_VALIDATION_API_PKG => null,
X_REGION_VALIDATION_API_PROC => null,
X_ORDER_SEQUENCE => null,
X_ORDER_DIRECTION => null,
X_DEFAULT_VALUE_VARCHAR2 => null,
X_DEFAULT_VALUE_NUMBER => null,
X_DEFAULT_VALUE_DATE => null,
X_ITEM_NAME => p_attribute_code,
X_DISPLAY_HEIGHT => 1,
X_SUBMIT => 'N',
X_ENCRYPT => 'N',
X_VIEW_USAGE_NAME => l_view_usage_name,
X_VIEW_ATTRIBUTE_NAME => l_view_attribute_name,
X_CSS_CLASS_NAME => null,
X_CSS_LABEL_CLASS_NAME => null,
X_URL => null,
X_POPLIST_VIEWOBJECT => null,
X_POPLIST_DISPLAY_ATTRIBUTE => null,
X_POPLIST_VALUE_ATTRIBUTE => null,
X_IMAGE_FILE_NAME => null,
X_NESTED_REGION_CODE => l_nested_region_code,
X_NESTED_REGION_APPL_ID => p_region_application_id,
X_MENU_NAME => null,
X_FLEXFIELD_NAME => null,
X_FLEXFIELD_APPLICATION_ID => null,
X_TABULAR_FUNCTION_CODE => null,
X_TIP_TYPE => null,
X_TIP_MESSAGE_NAME => null,
X_TIP_MESSAGE_APPLICATION_ID => null,
X_ENTITY_ID => null,
X_FLEX_SEGMENT_LIST => null,
X_ANCHOR => null,
X_POPLIST_VIEW_USAGE_NAME => null,
X_USER_CUSTOMIZABLE => null, --5.5
X_SORTBY_VIEW_ATTRIBUTE_NAME => null, --5.5
X_CREATION_DATE => g_sysdate,
X_CREATED_BY => 1,
X_LAST_UPDATE_DATE => g_sysdate,
X_LAST_UPDATED_BY => 1,
X_LAST_UPDATE_LOGIN => 1,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null);
l_update_flag VARCHAR2(1) DEFAULT 'Y';
AK_REGION_ITEMS_PKG.insert_row (
X_ROWID => l_row_id,
X_REGION_APPLICATION_ID => p_region_application_id,
X_REGION_CODE => l_region_code,
X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
X_ATTRIBUTE_CODE => p_attribute_code,
X_DISPLAY_SEQUENCE => l_display_sequence,
X_NODE_DISPLAY_FLAG => l_display_flag,
X_NODE_QUERY_FLAG => 'N',
X_ATTRIBUTE_LABEL_LENGTH => 0,
X_BOLD => 'N',
X_ITALIC => 'N',
X_VERTICAL_ALIGNMENT => 'TOP',
X_HORIZONTAL_ALIGNMENT => 'LEFT',
X_ITEM_STYLE => l_item_style,
X_OBJECT_ATTRIBUTE_FLAG => 'N',
X_ATTRIBUTE_LABEL_LONG => l_label_long,
X_DESCRIPTION => 'Quality Column',
X_SECURITY_CODE => null,
X_UPDATE_FLAG => l_update_flag,
X_REQUIRED_FLAG => 'N',
X_DISPLAY_VALUE_LENGTH => 0,
X_LOV_REGION_APPLICATION_ID => null,
X_LOV_REGION_CODE => null,
X_LOV_FOREIGN_KEY_NAME => null,
X_LOV_ATTRIBUTE_APPLICATION_ID => null,
X_LOV_ATTRIBUTE_CODE => null,
X_LOV_DEFAULT_FLAG => null,
X_REGION_DEFAULTING_API_PKG => null,
X_REGION_DEFAULTING_API_PROC => null,
X_REGION_VALIDATION_API_PKG => null,
X_REGION_VALIDATION_API_PROC => null,
X_ORDER_SEQUENCE => null,
X_ORDER_DIRECTION => null,
X_DEFAULT_VALUE_VARCHAR2 => null,
X_DEFAULT_VALUE_NUMBER => null,
X_DEFAULT_VALUE_DATE => null,
X_ITEM_NAME => p_attribute_code,
X_DISPLAY_HEIGHT => 1,
X_SUBMIT => 'N',
X_ENCRYPT => 'N',
X_VIEW_USAGE_NAME => l_view_usage_name,
X_VIEW_ATTRIBUTE_NAME => l_view_attribute_name,
X_CSS_CLASS_NAME => null,
X_CSS_LABEL_CLASS_NAME => null,
X_URL => null,
X_POPLIST_VIEWOBJECT => null,
X_POPLIST_DISPLAY_ATTRIBUTE => null,
X_POPLIST_VALUE_ATTRIBUTE => null,
X_IMAGE_FILE_NAME => null,
X_NESTED_REGION_CODE => l_nested_region_code,
X_NESTED_REGION_APPL_ID => p_region_application_id,
X_MENU_NAME => null,
X_FLEXFIELD_NAME => null,
X_FLEXFIELD_APPLICATION_ID => null,
X_TABULAR_FUNCTION_CODE => null,
X_TIP_TYPE => null,
X_TIP_MESSAGE_NAME => null,
X_TIP_MESSAGE_APPLICATION_ID => null,
X_ENTITY_ID => null,
X_FLEX_SEGMENT_LIST => null,
X_ANCHOR => null,
X_POPLIST_VIEW_USAGE_NAME => null,
X_USER_CUSTOMIZABLE => null, --5.5
X_SORTBY_VIEW_ATTRIBUTE_NAME => null, --5.5
X_CREATION_DATE => g_sysdate,
X_CREATED_BY => 1,
X_LAST_UPDATE_DATE => g_sysdate,
X_LAST_UPDATED_BY => 1,
X_LAST_UPDATE_LOGIN => 1,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null);
l_update_flag VARCHAR2(1) DEFAULT 'Y';
l_update_flag := 'N';
IF (p_attribute_code = g_update_attribute) THEN
-- dbms_output.put_line('Display Sequence: ' || l_display_sequence);
l_label_long := 'Update';
l_image_file_name := 'updateicon_enabled.gif';
AK_REGION_ITEMS_PKG.insert_row (
X_ROWID => l_row_id,
X_REGION_APPLICATION_ID => p_region_application_id,
X_REGION_CODE => l_region_code,
X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
X_ATTRIBUTE_CODE => p_attribute_code,
X_DISPLAY_SEQUENCE => l_display_sequence,
X_NODE_DISPLAY_FLAG => l_display_flag,
X_NODE_QUERY_FLAG => l_query_flag,
X_ATTRIBUTE_LABEL_LENGTH => 13,
X_BOLD => 'N',
X_ITALIC => 'N',
X_VERTICAL_ALIGNMENT => 'TOP',
X_HORIZONTAL_ALIGNMENT => 'LEFT',
X_ITEM_STYLE => l_item_style,
X_OBJECT_ATTRIBUTE_FLAG => 'N',
X_ATTRIBUTE_LABEL_LONG => l_label_long,
X_DESCRIPTION => l_description,
X_SECURITY_CODE => NULL,
X_UPDATE_FLAG => l_update_flag,
X_REQUIRED_FLAG => 'N',
X_DISPLAY_VALUE_LENGTH => 0,
X_LOV_REGION_APPLICATION_ID => null,
X_LOV_REGION_CODE => null,
X_LOV_FOREIGN_KEY_NAME => null,
X_LOV_ATTRIBUTE_APPLICATION_ID => null,
X_LOV_ATTRIBUTE_CODE => null,
X_LOV_DEFAULT_FLAG => null,
X_REGION_DEFAULTING_API_PKG => null,
X_REGION_DEFAULTING_API_PROC => null,
X_REGION_VALIDATION_API_PKG => null,
X_REGION_VALIDATION_API_PROC => null,
X_ORDER_SEQUENCE => null,
X_ORDER_DIRECTION => null,
X_DEFAULT_VALUE_VARCHAR2 => null,
X_DEFAULT_VALUE_NUMBER => null,
X_DEFAULT_VALUE_DATE => null,
X_ITEM_NAME => p_attribute_code,
X_DISPLAY_HEIGHT => 1,
X_SUBMIT => 'N',
X_ENCRYPT => 'N',
X_VIEW_USAGE_NAME => l_view_usage_name,
X_VIEW_ATTRIBUTE_NAME => l_view_attribute_name,
X_CSS_CLASS_NAME => null,
X_CSS_LABEL_CLASS_NAME => null,
X_URL => l_url,
X_POPLIST_VIEWOBJECT => null,
X_POPLIST_DISPLAY_ATTRIBUTE => null,
X_POPLIST_VALUE_ATTRIBUTE => null,
X_IMAGE_FILE_NAME => l_image_file_name,
X_NESTED_REGION_CODE => l_nested_region_code,
X_NESTED_REGION_APPL_ID => p_region_application_id,
X_MENU_NAME => null,
X_FLEXFIELD_NAME => null,
X_FLEXFIELD_APPLICATION_ID => null,
X_TABULAR_FUNCTION_CODE => null,
X_TIP_TYPE => null,
X_TIP_MESSAGE_NAME => null,
X_TIP_MESSAGE_APPLICATION_ID => null,
X_ENTITY_ID => l_entity_id,
X_FLEX_SEGMENT_LIST => null,
X_ANCHOR => null,
X_POPLIST_VIEW_USAGE_NAME => null,
X_USER_CUSTOMIZABLE => null, --5.5
X_SORTBY_VIEW_ATTRIBUTE_NAME => null, --5.5
X_CREATION_DATE => g_sysdate,
X_CREATED_BY => 1,
X_LAST_UPDATE_DATE => g_sysdate,
X_LAST_UPDATED_BY => 1,
X_LAST_UPDATE_LOGIN => 1,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null);
SELECT char_id
FROM qa_plan_chars
WHERE plan_id = x_plan_id
AND enabled_flag = 1;
SELECT char_id
FROM qa_plan_chars
WHERE plan_id = x_plan_id
AND enabled_flag = 1
ORDER BY PROMPT_SEQUENCE;
p_attribute_code => g_last_update_date_attribute,
p_attribute_application_id => g_application_id,
p_plan_id => x_plan_id,
p_region_application_id => g_application_id,
p_appendix => x_appendix);
p_attribute_code => g_update_attribute,
p_attribute_application_id => g_application_id,
p_plan_id => x_plan_id,
p_region_application_id => g_application_id,
p_appendix => x_appendix);
delete_plan_mapping(p_plan_id, p_region_application_id,
p_attribute_application_id);
PROCEDURE delete_plan_mapping_for_txn (
p_plan_id IN VARCHAR2,
p_region_application_id IN NUMBER,
p_attribute_application_id IN NUMBER,
p_appendix IN VARCHAR2) IS
l_element_id NUMBER;
SELECT attribute_code
FROM ak_region_items
WHERE region_code = x_region_code
AND region_application_id = g_application_id;
delete_region_item (
p_region_application_id,
l_top_region,
p_attribute_application_id,
l_region_code);
delete_region_item (
p_region_application_id,
l_region_code,
601,
l_attribute_code);
delete_region_item (
p_region_application_id,
l_region_code,
p_attribute_application_id,
l_attribute_code);
delete_region (p_region_application_id, l_region_code);
delete_attribute_for_plan (l_region_code, p_attribute_application_id);
END delete_plan_mapping_for_txn;
PROCEDURE delete_plan_mapping (
p_plan_id IN NUMBER,
p_region_application_id IN NUMBER,
p_attribute_application_id IN NUMBER) IS
BEGIN
-- This procedure deletes the mapping of a collection
-- plan from ak tables. This does it by deleting all the
-- individual mapping per transaction at a time.
-- Even for the same txn the call needs to be made multiple times
-- for example for ship txn, call made once for EQR and once for VQR
-- with appendix g_txn_ship_appendix and g_ship_vqr_appendix
-- parent-child
-- note the use of g_pc_vqr_appendix means this is for PC vqr
delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
p_attribute_application_id, g_pc_vqr_appendix);
delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
p_attribute_application_id, g_pc_vqr_sin_appendix);
delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
p_attribute_application_id, g_txn_osp_appendix);
delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
p_attribute_application_id, g_txn_ship_appendix);
delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
p_attribute_application_id, g_ship_vqr_appendix);
delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
p_attribute_application_id, g_osp_vqr_appendix);
delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
p_attribute_application_id, g_om_vqr_appendix);
delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
p_attribute_application_id, g_txn_work_appendix);
delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
p_attribute_application_id, g_work_vqr_appendix);
delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
p_attribute_application_id, g_txn_asset_appendix);
delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
p_attribute_application_id, g_asset_vqr_appendix);
delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
p_attribute_application_id, g_txn_op_appendix);
delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
p_attribute_application_id, g_op_vqr_appendix);
END delete_plan_mapping;
SELECT 1
FROM qa_txn_collection_triggers qtct
WHERE qtct.transaction_number = txn_number
AND qtct.collection_trigger_id = element_id;