[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_can_update VARCHAR2(10);
SELECT usage_id
FROM po_uda_ag_template_usages
WHERE template_id = p_template_id
AND last_updated_by =0 ;
SELECT COUNT(*)
INTO v_count
FROM PO_UDA_AG_TEMPLATES
WHERE NVL(FUNCTIONAL_AREA, '-1') = NVL(p_functional_area, '-1')
AND NVL(DOCUMENT_LEVEL, '-1') = NVL(p_document_level, '-1')
AND NVL(DOCUMENT_TYPE, '-1') = NVL(p_document_type, '-1')
AND NVL(DOCUMENT_STYLE_ID, -1) = DECODE(p_document_style, null, -1, 1);
SELECT PO_UDA_AG_TEMPLATES_S.nextval
INTO l_template_id
FROM DUAL
WHERE ROWNUM = 1;
INSERT INTO PO_UDA_AG_TEMPLATES
(
TEMPLATE_ID,
ENTITY_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
FUNCTIONAL_AREA,
DOCUMENT_LEVEL,
DOCUMENT_TYPE,
DOCUMENT_STYLE_ID,
REVISION
)
VALUES
(
l_template_id,
p_entity_code,
SYSDATE,
DECODE(p_owner, 'SEED', 1, 0),
0,
SYSDATE,
DECODE(p_owner, 'SEED', 1, 0),
p_functional_area,
p_document_level,
p_document_type,
decode(p_functional_area, 'PURCHASING', 1, null),
0
);
INSERT INTO PO_UDA_AG_TEMPLATES_TL
(
TEMPLATE_ID,
DISPLAY_NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
COMMENTS
)
SELECT
l_template_id,
p_display_name,
L.LANGUAGE_CODE,
USERENV('LANG'),
SYSDATE,
DECODE(p_owner, 'SEED', 1, 0),
0,
SYSDATE,
DECODE(p_owner, 'SEED', 1, 0),
p_comments
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B')
AND NOT EXISTS
( SELECT NULL
FROM PO_UDA_AG_TEMPLATES_TL T
WHERE T.TEMPLATE_ID = l_template_id
AND T.LANGUAGE = L.LANGUAGE_CODE);
load_msg('load_templates: after inserting tel table');
load_msg('load_templates: in update mode');
SELECT TB.TEMPLATE_ID, TB.REVISION, TB.ENTITY_CODE
INTO l_template_id, l_revision, l_entity_code
FROM PO_UDA_AG_TEMPLATES TB
WHERE NVL(TB.FUNCTIONAL_AREA, '-1') = NVL(p_functional_area, '-1')
AND NVL(TB.DOCUMENT_LEVEL, '-1') = NVL(p_document_level, '-1')
AND NVL(TB.DOCUMENT_TYPE, '-1') = NVL(p_document_type, '-1')
AND NVL(TB.DOCUMENT_STYLE_ID, -1) = decode (p_document_style, null, -1, 1)
AND TB.REVISION = (
SELECT MAX(T.REVISION)
FROM PO_UDA_AG_TEMPLATES T
WHERE NVL(T.FUNCTIONAL_AREA, '-1') = NVL(p_functional_area, '-1')
AND NVL(T.DOCUMENT_LEVEL, '-1') = NVL(p_document_level, '-1')
AND NVL(T.DOCUMENT_TYPE, '-1') = NVL(p_document_type, '-1')
AND NVL(T.DOCUMENT_STYLE_ID, -1) = decode (p_document_style, null, -1, 1)
);
INSERT INTO PO_UDA_AG_TEMPLATES_TL
(
TEMPLATE_ID,
DISPLAY_NAME,
LANGUAGE,
SOURCE_LANG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
COMMENTS
)
SELECT
l_template_id,
p_display_name,
L.LANGUAGE_CODE,
USERENV('LANG'),
SYSDATE,
DECODE(p_owner, 'SEED', 1, 0),
0,
SYSDATE,
DECODE(p_owner, 'SEED', 1, 0),
p_comments
FROM fnd_languages L
where L.INSTALLED_FLAG in ('I', 'B')
AND NOT EXISTS
( select NULL
from PO_UDA_AG_TEMPLATES_TL T
where T.TEMPLATE_ID = l_template_id
and T.LANGUAGE = L.LANGUAGE_CODE);
load_msg('load_templates: update mode : l_template_id ' ||l_template_id ||' l_revision '||l_revision ||' l_entity_code '|| l_entity_code || ' l_display_name ' || l_display_name );
l_can_update := PO_UDA_TEMPLATES_UTIL.can_update_delete(l_template_id);
load_msg('load_templates: l_can_update'|| l_can_update);
IF l_can_update = 'true' THEN
UPDATE PO_UDA_AG_TEMPLATES_TL
SET COMMENTS = p_comments,
source_lang = USERENV('LANG')
WHERE TEMPLATE_ID = l_template_id
AND LANGUAGE = USERENV('LANG');
load_msg('load_templates: updated comments ');
UPDATE PO_UDA_AG_TEMPLATES
SET COMPILED_FLAG ='N'
WHERE TEMPLATE_ID = l_template_id;
PO_UDA_TEMPLATES_UTIL.DELETE_USAGE(
p_api_version => 1.0,
p_usage_id => c_get_seeded_usage_id_rec.usage_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
DELETE FROM po_uda_ag_template_usages WHERE usage_id = c_get_seeded_usage_id_rec.usage_id;
l_can_update VARCHAR2(10);
load_msg('load_templates_nls: in update mode');
SELECT TB.TEMPLATE_ID, TB.REVISION, TB.ENTITY_CODE
INTO l_template_id, l_revision, l_entity_code
FROM PO_UDA_AG_TEMPLATES TB
WHERE NVL(TB.FUNCTIONAL_AREA, '-1') = NVL(p_functional_area, '-1')
AND NVL(TB.DOCUMENT_LEVEL, '-1') = NVL(p_document_level, '-1')
AND NVL(TB.DOCUMENT_TYPE, '-1') = NVL(p_document_type, '-1')
AND NVL(TB.DOCUMENT_STYLE_ID, -1) = decode (p_document_style, null, -1, 1)
AND TB.REVISION = (
SELECT MAX(T.REVISION)
FROM PO_UDA_AG_TEMPLATES T
WHERE NVL(T.FUNCTIONAL_AREA, '-1') = NVL(p_functional_area, '-1')
AND NVL(T.DOCUMENT_LEVEL, '-1') = NVL(p_document_level, '-1')
AND NVL(T.DOCUMENT_TYPE, '-1') = NVL(p_document_type, '-1')
AND NVL(T.DOCUMENT_STYLE_ID, -1) = decode (p_document_style, null, -1, 1)
);
load_msg('load_templates_nls: update mode : l_template_id ' ||l_template_id ||' l_revision '||l_revision ||' l_entity_code '|| l_entity_code );
l_can_update := PO_UDA_TEMPLATES_UTIL.can_update_delete(l_template_id);
load_msg('load_templates_nls: l_can_update'|| l_can_update);
IF l_can_update = 'true' THEN
UPDATE PO_UDA_AG_TEMPLATES_TL
SET DISPLAY_NAME = p_display_name,
COMMENTS = p_comments,
SOURCE_LANG = USERENV('LANG'),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = DECODE(p_owner, 'SEED', 1, 0),
LAST_UPDATE_LOGIN = 0
WHERE TEMPLATE_ID = l_template_id
AND LANGUAGE = USERENV('LANG');
SELECT ENTITY_CODE
INTO l_entity_code
FROM PO_UDA_AG_TEMPLATES
WHERE TEMPLATE_ID = g_pkg_template_id;
SELECT ATTR_GROUP_ID
INTO l_attr_grp_id
FROM EGO_FND_DSC_FLX_CTX_EXT ATTR_GRP
WHERE ATTR_GRP.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_desc_flex_context_code
AND ATTR_GRP.DESCRIPTIVE_FLEXFIELD_NAME = l_entity_code;
SELECT COUNT(*)
INTO v_count
FROM PO_UDA_AG_TEMPLATE_USAGES
WHERE TEMPLATE_ID = g_pkg_template_id
AND ATTRIBUTE_GROUP_ID = l_attr_grp_id
AND (p_attribute_category IS NULL OR ATTRIBUTE_CATEGORY = p_attribute_category)
AND (p_attribute1 IS NULL OR NVL(ATTRIBUTE1, '') = p_attribute1)
AND (p_attribute2 IS NULL OR NVL(ATTRIBUTE2, '') = p_attribute2)
AND (p_attribute3 IS NULL OR NVL(ATTRIBUTE3, '') = p_attribute3)
AND (p_attribute4 IS NULL OR NVL(ATTRIBUTE4, '') = p_attribute4)
AND (p_attribute5 IS NULL OR NVL(ATTRIBUTE5, '') = p_attribute5)
AND (p_attribute6 IS NULL OR NVL(ATTRIBUTE6, '') = p_attribute6)
AND (p_attribute7 IS NULL OR NVL(ATTRIBUTE7, '') = p_attribute7)
AND (p_attribute8 IS NULL OR NVL(ATTRIBUTE8, '') = p_attribute8)
AND (p_attribute9 IS NULL OR NVL(ATTRIBUTE9, '') = p_attribute9)
AND (p_attribute10 IS NULL OR NVL(ATTRIBUTE10, '') = p_attribute10)
AND (p_attribute11 IS NULL OR NVL(ATTRIBUTE11, '') = p_attribute11)
AND (p_attribute12 IS NULL OR NVL(ATTRIBUTE12, '') = p_attribute12)
AND (p_attribute13 IS NULL OR NVL(ATTRIBUTE13, '') = p_attribute13)
AND (p_attribute14 IS NULL OR NVL(ATTRIBUTE14, '') = p_attribute14)
AND (p_attribute15 IS NULL OR NVL(ATTRIBUTE15, '') = p_attribute15)
AND (p_attribute16 IS NULL OR NVL(ATTRIBUTE16, '') = p_attribute16)
AND (p_attribute17 IS NULL OR NVL(ATTRIBUTE17, '') = p_attribute17)
AND (p_attribute18 IS NULL OR NVL(ATTRIBUTE18, '') = p_attribute18)
AND (p_attribute19 IS NULL OR NVL(ATTRIBUTE19, '') = p_attribute19)
AND (p_attribute20 IS NULL OR NVL(ATTRIBUTE20, '') = p_attribute20);
INSERT INTO PO_UDA_AG_TEMPLATE_USAGES
(
TEMPLATE_ID,
ATTRIBUTE_GROUP_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ASSOCIATION_ID,
USAGE_ID,
ATTRIBUTE_GROUP_SEQUENCE
)
VALUES
(
g_pkg_template_id,
l_attr_grp_id,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_attribute16,
p_attribute17,
p_attribute18,
p_attribute19,
p_attribute20,
DECODE(p_owner, 'SEED', 1, 0),
SYSDATE,
SYSDATE,
DECODE(p_owner, 'SEED', 1, 0),
0,
l_association_id,
PO_UDA_AG_TEMPLATE_USAGES_S.nextval,
p_attribute_group_sequence
);
UPDATE PO_UDA_AG_TEMPLATE_USAGES
SET ATTRIBUTE_GROUP_SEQUENCE = p_attribute_group_sequence
WHERE TEMPLATE_ID = g_pkg_template_id
AND ATTRIBUTE_GROUP_ID = l_attr_grp_id
AND (p_attribute_category IS NULL OR ATTRIBUTE_CATEGORY = p_attribute_category)
AND (p_attribute1 IS NULL OR NVL(ATTRIBUTE1, '') = p_attribute1)
AND (p_attribute2 IS NULL OR NVL(ATTRIBUTE2, '') = p_attribute2)
AND (p_attribute3 IS NULL OR NVL(ATTRIBUTE3, '') = p_attribute3)
AND (p_attribute4 IS NULL OR NVL(ATTRIBUTE4, '') = p_attribute4)
AND (p_attribute5 IS NULL OR NVL(ATTRIBUTE5, '') = p_attribute5)
AND (p_attribute6 IS NULL OR NVL(ATTRIBUTE6, '') = p_attribute6)
AND (p_attribute7 IS NULL OR NVL(ATTRIBUTE7, '') = p_attribute7)
AND (p_attribute8 IS NULL OR NVL(ATTRIBUTE8, '') = p_attribute8)
AND (p_attribute9 IS NULL OR NVL(ATTRIBUTE9, '') = p_attribute9)
AND (p_attribute10 IS NULL OR NVL(ATTRIBUTE10, '') = p_attribute10)
AND (p_attribute11 IS NULL OR NVL(ATTRIBUTE11, '') = p_attribute11)
AND (p_attribute12 IS NULL OR NVL(ATTRIBUTE12, '') = p_attribute12)
AND (p_attribute13 IS NULL OR NVL(ATTRIBUTE13, '') = p_attribute13)
AND (p_attribute14 IS NULL OR NVL(ATTRIBUTE14, '') = p_attribute14)
AND (p_attribute15 IS NULL OR NVL(ATTRIBUTE15, '') = p_attribute15)
AND (p_attribute16 IS NULL OR NVL(ATTRIBUTE16, '') = p_attribute16)
AND (p_attribute17 IS NULL OR NVL(ATTRIBUTE17, '') = p_attribute17)
AND (p_attribute18 IS NULL OR NVL(ATTRIBUTE18, '') = p_attribute18)
AND (p_attribute19 IS NULL OR NVL(ATTRIBUTE19, '') = p_attribute19)
AND (p_attribute20 IS NULL OR NVL(ATTRIBUTE20, '') = p_attribute20);
SELECT ENTITY_CODE
INTO l_entity_code
FROM PO_UDA_AG_TEMPLATES
WHERE TEMPLATE_ID = g_pkg_template_id;
SELECT OBJECT_ID
INTO l_object_id
FROM FND_OBJECTS
WHERE OBJ_NAME = p_object_name;
SELECT FUNCTION_ID
INTO l_function_id
FROM EGO_FUNCTIONS_B
WHERE INTERNAL_NAME = p_function_name;
SELECT ATTR_GROUP_ID
INTO l_attr_grp_id
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_internal_name
AND DESCRIPTIVE_FLEXFIELD_NAME = l_entity_code;
SELECT ACTION_ID
INTO l_action_id
FROM EGO_ACTIONS_B
WHERE ACTION_NAME = p_action_name
AND ATTR_GROUP_ID = l_attr_grp_id
AND CLASSIFICATION_CODE = g_pkg_template_id || '';
UPDATE EGO_ACTIONS_B
SET SEQUENCE = p_sequence
WHERE ACTION_ID = l_action_id;
SELECT ACTION_ID
INTO l_action_id
FROM EGO_ACTIONS_TL
WHERE ACTION_ID = l_action_id
AND LANGUAGE = USERENV('LANG');
UPDATE EGO_ACTIONS_TL
SET DESCRIPTION = p_description
WHERE ACTION_ID = l_action_id
AND LANGUAGE = USERENV('LANG');
INSERT INTO EGO_ACTIONS_TL
(
ACTION_ID,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
SELECT
l_action_id,
p_description,
L.LANGUAGE_CODE,
USERENV('LANG'),
DECODE(p_owner, 'SEED', 1, 0),
SYSDATE,
DECODE(p_owner, 'SEED', 1, 0),
SYSDATE,
0
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B')
AND NOT EXISTS
( SELECT NULL
FROM EGO_ACTIONS_TL T
WHERE T.ACTION_ID =l_action_id
AND T.LANGUAGE = L.LANGUAGE_CODE
);
SELECT EGO_ACTIONS_S.nextval
INTO l_action_id
FROM DUAL
WHERE ROWNUM = 1;
INSERT INTO EGO_ACTIONS_B
(
ACTION_ID,
OBJECT_ID,
CLASSIFICATION_CODE,
ATTR_GROUP_ID,
SEQUENCE,
ACTION_NAME,
FUNCTION_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_action_id,
l_object_id,
g_pkg_template_id || '',
l_attr_grp_id,
p_sequence,
p_action_name,
l_function_id,
DECODE(p_owner, 'SEED', 1, 0),
SYSDATE,
DECODE(p_owner, 'SEED', 1, 0),
SYSDATE,
0
);
INSERT INTO EGO_ACTIONS_TL
(
ACTION_ID,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
SELECT
l_action_id,
p_description,
L.LANGUAGE_CODE,
USERENV('LANG'),
DECODE(p_owner, 'SEED', 1, 0),
SYSDATE,
DECODE(p_owner, 'SEED', 1, 0),
SYSDATE,
0
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B')
AND NOT EXISTS
( SELECT NULL
FROM EGO_ACTIONS_TL T
WHERE T.ACTION_ID =l_action_id
AND T.LANGUAGE = L.LANGUAGE_CODE
);
SELECT ENTITY_CODE
INTO l_entity_code
FROM PO_UDA_AG_TEMPLATES
WHERE TEMPLATE_ID = g_pkg_template_id;
SELECT OBJECT_ID
INTO l_object_id
FROM FND_OBJECTS
WHERE OBJ_NAME = p_object_name;
SELECT FUNCTION_ID
INTO l_function_id
FROM EGO_FUNCTIONS_B
WHERE INTERNAL_NAME = p_function_name;
SELECT ATTR_GROUP_ID
INTO l_attr_grp_id
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_internal_name
AND DESCRIPTIVE_FLEXFIELD_NAME = l_entity_code;
SELECT ACTION_ID
INTO l_action_id
FROM EGO_ACTIONS_B
WHERE ACTION_NAME = p_action_name
AND ATTR_GROUP_ID = l_attr_grp_id
AND CLASSIFICATION_CODE = g_pkg_template_id || '';
UPDATE EGO_ACTIONS_TL
SET DESCRIPTION = p_description,
SOURCE_LANG = USERENV('LANG'),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = DECODE(p_owner, 'SEED', 1, 0),
LAST_UPDATE_LOGIN = 0
WHERE ACTION_ID = l_action_id
AND LANGUAGE = USERENV('LANG');
SELECT ENTITY_CODE
INTO l_entity_code
FROM PO_UDA_AG_TEMPLATES
WHERE TEMPLATE_ID = g_pkg_template_id;
SELECT DATA_LEVEL_ID
INTO l_data_level_id
FROM EGO_DATA_LEVEL_B
WHERE DATA_LEVEL_NAME = p_data_level;
SELECT ATTR_GROUP_ID
INTO l_attr_grp_id
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_internal_name
AND DESCRIPTIVE_FLEXFIELD_NAME = l_entity_code;
SELECT ACTION_ID
INTO l_action_id
FROM EGO_ACTIONS_B
WHERE ACTION_NAME = p_action_name
AND ATTR_GROUP_ID = l_attr_grp_id
AND CLASSIFICATION_CODE = g_pkg_template_id || ''
AND ROWNUM = 1;
SELECT VISIBILITY_FLAG
INTO l_visibility_flag
FROM EGO_ACTIONS_DL
WHERE ACTION_ID = l_action_id
AND DATA_LEVEL_ID = l_data_level_id;
UPDATE EGO_ACTIONS_DL
SET VISIBILITY_FLAG = p_visibility_flag
WHERE ACTION_ID = l_action_id
AND DATA_LEVEL_ID = l_data_level_id;
INSERT INTO EGO_ACTIONS_DL
(
ACTION_ID,
DATA_LEVEL_ID,
VISIBILITY_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_action_id,
l_data_level_id,
p_visibility_flag,
DECODE(p_owner, 'SEED', 1, 0),
SYSDATE,
DECODE(p_owner, 'SEED', 1, 0),
SYSDATE,
0
);
l_update_login NUMBER;
SELECT ENTITY_CODE
INTO l_entity_code
FROM PO_UDA_AG_TEMPLATES
WHERE TEMPLATE_ID = g_pkg_template_id;
SELECT APPLICATION_ID
INTO l_app_id
FROM FND_APPLICATION
WHERE APPLICATION_SHORT_NAME = p_app_short_name;
SELECT ATTR_GROUP_ID
INTO l_attr_grp_id
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_internal_name
AND DESCRIPTIVE_FLEXFIELD_NAME = l_entity_code;
SELECT ACTION_ID
INTO l_action_id
FROM EGO_ACTIONS_B
WHERE ACTION_NAME = p_action_name
AND ATTR_GROUP_ID = l_attr_grp_id
AND CLASSIFICATION_CODE = g_pkg_template_id || ''
AND ROWNUM = 1;
SELECT LAST_UPDATE_LOGIN
INTO l_update_login
FROM EGO_ACTION_DISPLAYS_B
WHERE ACTION_ID = l_action_id;
UPDATE EGO_ACTION_DISPLAYS_B
SET EXECUTION_METHOD = p_exec_method,
EXECUTION_TRIGGER = p_exec_trigger,
DISPLAY_STYLE = p_display_style,
PROMPT_APPLICATION_ID = l_app_id,
PROMPT_MESSAGE_NAME = p_prompt_message_name,
VISIBILITY_FLAG = p_visibility_flag
WHERE ACTION_ID = l_action_id;
INSERT INTO EGO_ACTION_DISPLAYS_B
(
ACTION_ID,
EXECUTION_METHOD,
EXECUTION_TRIGGER,
DISPLAY_STYLE,
PROMPT_APPLICATION_ID,
PROMPT_MESSAGE_NAME,
VISIBILITY_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_action_id,
p_exec_method,
p_exec_trigger,
p_display_style,
l_app_id,
p_prompt_message_name,
p_visibility_flag,
DECODE(p_owner, 'SEED', 1, 0),
SYSDATE,
DECODE(p_owner, 'SEED', 1, 0),
SYSDATE,
0
);
SELECT ENTITY_CODE
INTO l_entity_code
FROM PO_UDA_AG_TEMPLATES
WHERE TEMPLATE_ID = g_pkg_template_id;
SELECT FUNCTION_ID
INTO l_function_id
FROM EGO_FUNCTIONS_B
WHERE INTERNAL_NAME = p_function_name;
SELECT ATTR_GROUP_ID
INTO l_attr_grp_id
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_grp_internal_name
AND DESCRIPTIVE_FLEXFIELD_NAME = l_entity_code;
SELECT ACTION_ID
INTO l_action_id
FROM EGO_ACTIONS_B
WHERE ACTION_NAME = p_action_name
AND ATTR_GROUP_ID = l_attr_grp_id
AND CLASSIFICATION_CODE = g_pkg_template_id || ''
AND ROWNUM = 1;
SELECT FUNC_PARAM_ID
INTO l_param_id
FROM EGO_FUNC_PARAMS_B
WHERE INTERNAL_NAME = p_map_par_int_name
AND FUNCTION_ID = l_function_id;
SELECT APPLICATION_ID
INTO l_app_id
FROM FND_APPLICATION
WHERE APPLICATION_SHORT_NAME = p_map_app_short_name;
SELECT MAPPED_TO_GROUP_PK2, MAPPED_TO_GROUP_PK3, MAPPED_ATTRIBUTE
INTO l_pk2, l_pk3, l_map_attr
FROM EGO_MAPPINGS_B
WHERE FUNCTION_ID = l_function_id
AND MAPPED_OBJ_PK1_VAL = l_action_id
AND FUNC_PARAM_ID = l_param_id
AND MAPPED_OBJ_TYPE = p_map_obj_type
AND MAPPED_TO_GROUP_TYPE = p_map_to_grp_type
AND NVL(MAPPED_TO_GROUP_PK2, '-1') = NVL(p_map_ext_attrs, '-1')
AND NVL(MAPPED_TO_GROUP_PK3, '-1') = NVL(p_map_context, '-1')
AND MAPPED_ATTRIBUTE = p_map_attr;
INSERT INTO EGO_MAPPINGS_B
(
FUNCTION_ID,
MAPPED_OBJ_TYPE,
MAPPED_OBJ_PK1_VAL,
FUNC_PARAM_ID,
MAPPED_TO_GROUP_TYPE,
MAPPED_TO_GROUP_PK1,
MAPPED_TO_GROUP_PK2,
MAPPED_TO_GROUP_PK3,
MAPPED_ATTRIBUTE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_function_id,
p_map_obj_type,
l_action_id,
l_param_id,
p_map_to_grp_type,
l_app_id,
p_map_ext_attrs,
p_map_context,
p_map_attr,
DECODE(p_owner, 'SEED', 1, 0),
SYSDATE,
DECODE(p_owner, 'SEED', 1, 0),
SYSDATE,
0
);