[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PAGE_ID
FROM EGO_PAGES_B
WHERE CLASSIFICATION_CODE = p_classification_code;
SELECT USAGE_ID, ASSOCIATION_ID, ATTRIBUTE_GROUP_SEQUENCE
FROM PO_UDA_AG_TEMPLATE_USAGES
WHERE TEMPLATE_ID = p_template_id
AND NVL(ATTRIBUTE_CATEGORY, '@@@') = l_cur_usage
AND NVL(ATTRIBUTE1, '@@@') = l_cur_context1
AND NVL(ATTRIBUTE2, '@@@') = l_cur_context2
AND NVL(ATTRIBUTE3, '@@@') = l_cur_context3
AND NVL(ATTRIBUTE4, '@@@') = l_cur_context4
AND NVL(ATTRIBUTE5, '@@@') = l_cur_context5
AND NVL(ATTRIBUTE6, '@@@') = l_cur_context6
AND NVL(ATTRIBUTE7, '@@@') = l_cur_context7
AND NVL(ATTRIBUTE8, '@@@') = l_cur_context8
AND NVL(ATTRIBUTE9, '@@@') = l_cur_context9
AND NVL(ATTRIBUTE10, '@@@') = l_cur_context10
AND NVL(ATTRIBUTE11, '@@@') = l_cur_context11
AND NVL(ATTRIBUTE12, '@@@') = l_cur_context12
AND NVL(ATTRIBUTE13, '@@@') = l_cur_context13
AND NVL(ATTRIBUTE14, '@@@') = l_cur_context14
AND NVL(ATTRIBUTE15, '@@@') = l_cur_context15
AND NVL(ATTRIBUTE16, '@@@') = l_cur_context16
AND NVL(ATTRIBUTE17, '@@@') = l_cur_context17
AND NVL(ATTRIBUTE18, '@@@') = l_cur_context18
AND NVL(ATTRIBUTE19, '@@@') = l_cur_context19
AND NVL(ATTRIBUTE20, '@@@') = l_cur_context20
ORDER BY ATTRIBUTE_CATEGORY, ATTRIBUTE1,
ATTRIBUTE2, ATTRIBUTE3,
ATTRIBUTE4, ATTRIBUTE5,
ATTRIBUTE6, ATTRIBUTE7,
ATTRIBUTE8, ATTRIBUTE9,
ATTRIBUTE10, ATTRIBUTE11,
ATTRIBUTE12, ATTRIBUTE13,
ATTRIBUTE14, ATTRIBUTE15,
ATTRIBUTE16, ATTRIBUTE17,
ATTRIBUTE18, ATTRIBUTE19,
ATTRIBUTE20;
SELECT NVL(ATTRIBUTE_CATEGORY, '@@@'), NVL(ATTRIBUTE1, '@@@'),
NVL(ATTRIBUTE2, '@@@'), NVL(ATTRIBUTE3, '@@@'),
NVL(ATTRIBUTE4, '@@@'), NVL(ATTRIBUTE5, '@@@'),
NVL(ATTRIBUTE6, '@@@'), NVL(ATTRIBUTE7, '@@@'),
NVL(ATTRIBUTE8, '@@@'), NVL(ATTRIBUTE9, '@@@'),
NVL(ATTRIBUTE10, '@@@'), NVL(ATTRIBUTE11, '@@@'),
NVL(ATTRIBUTE12, '@@@'), NVL(ATTRIBUTE13, '@@@'),
NVL(ATTRIBUTE14, '@@@'), NVL(ATTRIBUTE15, '@@@'),
NVL(ATTRIBUTE16, '@@@'), NVL(ATTRIBUTE17, '@@@'),
NVL(ATTRIBUTE18, '@@@'), NVL(ATTRIBUTE19, '@@@'),
NVL(ATTRIBUTE20, '@@@')
FROM PO_UDA_AG_TEMPLATE_USAGES
WHERE TEMPLATE_ID = p_template_id
GROUP BY ATTRIBUTE_CATEGORY, ATTRIBUTE1,
ATTRIBUTE2, ATTRIBUTE3,
ATTRIBUTE4, ATTRIBUTE5,
ATTRIBUTE6, ATTRIBUTE7,
ATTRIBUTE8, ATTRIBUTE9,
ATTRIBUTE10, ATTRIBUTE11,
ATTRIBUTE12, ATTRIBUTE13,
ATTRIBUTE14, ATTRIBUTE15,
ATTRIBUTE16, ATTRIBUTE17,
ATTRIBUTE18, ATTRIBUTE19,
ATTRIBUTE20
HAVING COUNT(*) > 1;
EGO_EXT_FWK_PUB.DELETE_PAGE
(
p_api_version => 1.0
,p_page_id => l_page_id
,p_init_msg_list => fnd_api.g_FALSE
,p_commit => fnd_api.g_FALSE
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
SELECT entity_code
INTO l_entity_type
FROM PO_UDA_AG_TEMPLATES
WHERE TEMPLATE_ID = p_template_id;
SELECT OBJECT_ID
INTO l_object_id
FROM FND_OBJECTS
WHERE OBJ_NAME = PO_UDA_DATA_UTIL.g_object_dff_tl(l_entity_type).l_object_name;
SELECT DATA_LEVEL_NAME
INTO l_data_level_name
FROM EGO_DATA_LEVEL_B
WHERE ATTR_GROUP_TYPE IN
(
SELECT ENTITY_CODE
FROM PO_UDA_AG_TEMPLATES
WHERE TEMPLATE_ID = p_template_id
)
AND DATA_LEVEL_NAME NOT LIKE '%ARCHIVE';
SELECT temp.TEMPLATE_ID
FROM PO_UDA_AG_TEMPLATES temp
WHERE temp.FUNCTIONAL_AREA = NVL(p_functional_area_code, temp.FUNCTIONAL_AREA)
AND temp.DOCUMENT_TYPE = NVL(p_document_type_code, temp.DOCUMENT_TYPE)
AND temp.DOCUMENT_STYLE_ID = NVL(p_document_style_id, temp.DOCUMENT_STYLE_ID)
AND temp.DOCUMENT_LEVEL = NVL(p_level_code, temp.DOCUMENT_LEVEL)
AND temp.REVISION = NVL(p_revision, temp.REVISION)
AND COMPILED_FLAG <> 'Y';
SELECT DISTINCT ATTRIBUTE_GROUP_ID
FROM PO_UDA_AG_TEMPLATE_USAGES
WHERE TEMPLATE_ID = p_template_id;
UPDATE PO_UDA_AG_TEMPLATES
SET COMPILED_FLAG = 'Y'
WHERE TEMPLATE_ID = p_template_id;
SELECT ATTR_GROUP_ID
INTO l_attr_grp_id
FROM ego_fnd_dsc_flx_ctx_ext
WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_attribute_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attribute_group_name
AND ROWNUM < 2;
SELECT PO_HEADER_GROUP_ID,
PO_LINE_GROUP_ID,
PO_DISTRIBUTIONS_GROUP_ID,
PO_SHIPMENTS_GROUP_ID,
REQ_HEADER_GROUP_ID,
REQ_LINE_GROUP_ID,
REQ_DISTRIBUTION_GROUP_ID,
PON_AUC_ITEM_PRICE_GROUP_ID,
PON_AUC_HEADER_GROUP_ID,
BID_HEADER_GROUP_ID,
BID_ITEM_PRICE_GROUP_ID
INTO l_po_header_group_id,
l_po_line_group_id,
l_po_distributions_group_id,
l_shipments_group_id,
l_req_header_group_id,
l_req_line_group_id,
l_req_distribution_group_id,
l_pon_auc_item_price_group_id,
l_pon_auc_header_group_id,
l_bid_header_group_id,
l_bid_item_proce_group_id
FROM PO_UDA_MAPPINGS
WHERE DUMMY_ATTRIBUTE_GROUP_ID = l_attr_grp_id
AND ROWNUM < 2;
SELECT CREATED_BY
INTO l_created_by
FROM ego_fnd_dsc_flx_ctx_ext
WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_attribute_group_type
AND ATTR_GROUP_ID = p_attribute_group_id
AND ROWNUM < 2;
SELECT count(*)
INTO l_count
FROM PO_UDA_AG_TEMPLATE_USAGES
WHERE ATTRIBUTE_GROUP_ID = p_attribute_group_id;
x_is_seeded := can_update_delete(l_template_id);
SELECT CREATED_BY, CLASSIFICATION_CODE
INTO l_created_by, l_template_id
FROM EGO_ACTIONS_B
WHERE FUNCTION_ID = p_function_id
AND ROWNUM < 2;
SELECT ENTITY_CODE
INTO l_entity_code
FROM PO_UDA_AG_TEMPLATES
WHERE TEMPLATE_ID = l_template_id;
SELECT count(*)
INTO l_count
FROM PO_HEADERS_ALL
WHERE UDA_TEMPLATE_ID = l_template_id;
SELECT count(*)
INTO l_count
FROM PO_LINES_ALL
WHERE UDA_TEMPLATE_ID = l_template_id;
SELECT count(*)
INTO l_count
FROM PO_LINE_LOCATIONS_ALL
WHERE UDA_TEMPLATE_ID = l_template_id;
SELECT count(*)
INTO l_count
FROM PO_DISTRIBUTIONS_ALL
WHERE UDA_TEMPLATE_ID = l_template_id;
SELECT count(*)
INTO l_count
FROM PO_REQUISITION_HEADERS_ALL
WHERE UDA_TEMPLATE_ID = l_template_id;
SELECT count(*)
INTO l_count
FROM PO_REQUISITION_LINES_ALL
WHERE UDA_TEMPLATE_ID = l_template_id;
SELECT count(*)
INTO l_count
FROM PO_REQ_DISTRIBUTIONS_ALL
WHERE UDA_TEMPLATE_ID = l_template_id;
SELECT count(*)
INTO l_count
FROM PON_AUCTION_HEADERS_ALL
WHERE UDA_TEMPLATE_ID = l_template_id;
SELECT count(*)
INTO l_count
FROM PON_AUCTION_ITEM_PRICES_ALL
WHERE UDA_TEMPLATE_ID = l_template_id;
SELECT count(*)
INTO l_count
FROM PON_BID_HEADERS
WHERE UDA_TEMPLATE_ID = l_template_id;
SELECT count(*)
INTO l_count
FROM PON_BID_ITEM_PRICES
WHERE UDA_TEMPLATE_ID = l_template_id;
FUNCTION can_update_delete(p_template_id NUMBER)
RETURN VARCHAR2 IS
d_api_name CONSTANT VARCHAR2(30) := 'can_delete';
SELECT fo.DATABASE_OBJECT_NAME
FROM EGO_OBJECT_EXT_TABLES_B eb, fnd_objects fo, EGO_FND_DESC_FLEXS_EXT et, po_uda_ag_templates pt
WHERE eb.OBJECT_ID = fo.object_id
AND eb.EXT_TABLE_NAME = et.APPLICATION_TL_TABLE_NAME
AND et.DESCRIPTIVE_FLEXFIELD_NAME = pt.entity_code
AND pt.template_id = p_template_id;
l_stmt := 'SELECT ''Y'' from '|| l_prod_table ||
' WHERE '|| l_template_column||' = '|| p_template_id || ' AND ROWNUM < 2';
END can_update_delete;
CURSOR c_to_delete_AG
(
p_internal_name IN VARCHAR2
)
IS
SELECT ATTR_GROUP_ID
FROM ego_fnd_dsc_flx_ctx_ext
WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = p_internal_name;
SELECT ATTR_GROUP_ID
INTO l_attr_grp_id
FROM ego_fnd_dsc_flx_ctx_ext
WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_attribute_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attribute_group_name
AND ROWNUM < 2;
OPEN c_to_delete_AG
(
p_attribute_group_name
);
FETCH c_to_delete_AG INTO l_attr_grp_id;
EXIT WHEN c_to_delete_AG%NOTFOUND;
PO_UDA_TEMPLATES_UTIL.delete_real_attr_group(
p_glb_api_version,
l_attr_grp_id
);
CLOSE c_to_delete_AG;
l_total_delete NUMBER;
SELECT 1
INTO l_total_delete
FROM ego_fnd_dsc_flx_ctx_ext
WHERE ATTR_GROUP_ID = p_attribute_group_id
AND ROWNUM < 2;
PO_LOG.stmt(d_module, d_progress, 'l_total_delete', l_total_delete);
IF l_total_delete = 1 THEN
d_progress := 30;
WHEN OTHERS THEN -- Total delete case for dummy
d_progress := 60;
delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, 'PO_HEADER_EXT_ATTRS'));
delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, 'PO_LINE_EXT_ATTRS'));
delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, 'PO_DISTRIBUTIONS_EXT_ATTRS'));
delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, 'PO_SHIPMENTS_EXT_ATTRS'));
delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, 'PO_REQ_HEADER_EXT_ATTRS'));
delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, 'PO_REQ_LINE_EXT_ATTRS'));
delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, 'PO_REQ_DIST_EXT_ATTRS'));
delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, 'PON_AUC_PRICES_EXT_ATTRS'));
delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, 'PON_AUC_HDRS_EXT_ATTRS'));
delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, 'PON_BID_HEDRS_EXT_ATTRS'));
delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, 'PON_BID_PRICES_EXT_ATTRS'));
DELETE FROM PO_UDA_MAPPINGS
WHERE dummy_attribute_group_id = p_attribute_group_id;
SELECT DATA_LEVEL_ID
INTO l_data_level_id
FROM EGO_DATA_LEVEL_B
WHERE ATTR_GROUP_TYPE = p_attr_group_type
AND DATA_LEVEL_NAME = p_data_level_name;
SELECT DATA_LEVEL_ID
INTO l_data_level_id
FROM EGO_DATA_LEVEL_B
WHERE ATTR_GROUP_TYPE = p_attr_group_type
AND DATA_LEVEL_NAME = p_data_level_name || '_ARCHIVE';
INSERT INTO PO_UDA_MAPPINGS
(
dummy_attribute_group_id
,PO_HEADER_GROUP_ID
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
VALUES
(
p_dummy_attr_group_id
,p_real_attr_group_id
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.Login_Id
);
INSERT INTO PO_UDA_MAPPINGS
(
dummy_attribute_group_id
,PO_LINE_GROUP_ID
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
VALUES
(
p_dummy_attr_group_id
,p_real_attr_group_id
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.Login_Id
);
INSERT INTO PO_UDA_MAPPINGS
(
dummy_attribute_group_id
,PO_DISTRIBUTIONS_GROUP_ID
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
VALUES
(
p_dummy_attr_group_id
,p_real_attr_group_id
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.Login_Id
);
INSERT INTO PO_UDA_MAPPINGS
(
dummy_attribute_group_id
,PO_SHIPMENTS_GROUP_ID
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
VALUES
(
p_dummy_attr_group_id
,p_real_attr_group_id
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.Login_Id
);
INSERT INTO PO_UDA_MAPPINGS
(
dummy_attribute_group_id
,REQ_HEADER_GROUP_ID
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
VALUES
(
p_dummy_attr_group_id
,p_real_attr_group_id
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.Login_Id
);
INSERT INTO PO_UDA_MAPPINGS
(
dummy_attribute_group_id
,REQ_LINE_GROUP_ID
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
VALUES
(
p_dummy_attr_group_id
,p_real_attr_group_id
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.Login_Id
);
INSERT INTO PO_UDA_MAPPINGS
(
dummy_attribute_group_id
,REQ_DISTRIBUTION_GROUP_ID
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
VALUES
(
p_dummy_attr_group_id
,p_real_attr_group_id
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.Login_Id
);
INSERT INTO PO_UDA_MAPPINGS
(
dummy_attribute_group_id
,PON_AUC_ITEM_PRICE_GROUP_ID
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
VALUES
(
p_dummy_attr_group_id
,p_real_attr_group_id
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.Login_Id
);
INSERT INTO PO_UDA_MAPPINGS
(
dummy_attribute_group_id
,PON_AUC_HEADER_GROUP_ID
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
VALUES
(
p_dummy_attr_group_id
,p_real_attr_group_id
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.Login_Id
);
INSERT INTO PO_UDA_MAPPINGS
(
dummy_attribute_group_id
,BID_HEADER_GROUP_ID
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
VALUES
(
p_dummy_attr_group_id
,p_real_attr_group_id
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.Login_Id
);
INSERT INTO PO_UDA_MAPPINGS
(
dummy_attribute_group_id
,BID_ITEM_PRICE_GROUP_ID
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
VALUES
(
p_dummy_attr_group_id
,p_real_attr_group_id
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.Login_Id
);
UPDATE PO_UDA_MAPPINGS
SET PO_HEADER_GROUP_ID = p_real_attr_group_id
WHERE dummy_attribute_group_id = p_dummy_attr_group_id;
UPDATE PO_UDA_MAPPINGS
SET PO_LINE_GROUP_ID = p_real_attr_group_id
WHERE dummy_attribute_group_id = p_dummy_attr_group_id;
UPDATE PO_UDA_MAPPINGS
SET PO_DISTRIBUTIONS_GROUP_ID = p_real_attr_group_id
WHERE dummy_attribute_group_id = p_dummy_attr_group_id;
UPDATE PO_UDA_MAPPINGS
SET PO_SHIPMENTS_GROUP_ID = p_real_attr_group_id
WHERE dummy_attribute_group_id = p_dummy_attr_group_id;
UPDATE PO_UDA_MAPPINGS
SET REQ_HEADER_GROUP_ID = p_real_attr_group_id
WHERE dummy_attribute_group_id = p_dummy_attr_group_id;
UPDATE PO_UDA_MAPPINGS
SET REQ_LINE_GROUP_ID = p_real_attr_group_id
WHERE dummy_attribute_group_id = p_dummy_attr_group_id;
UPDATE PO_UDA_MAPPINGS
SET REQ_DISTRIBUTION_GROUP_ID = p_real_attr_group_id
WHERE dummy_attribute_group_id = p_dummy_attr_group_id;
UPDATE PO_UDA_MAPPINGS
SET PON_AUC_ITEM_PRICE_GROUP_ID = p_real_attr_group_id
WHERE dummy_attribute_group_id = p_dummy_attr_group_id;
UPDATE PO_UDA_MAPPINGS
SET PON_AUC_HEADER_GROUP_ID = p_real_attr_group_id
WHERE dummy_attribute_group_id = p_dummy_attr_group_id;
UPDATE PO_UDA_MAPPINGS
SET BID_HEADER_GROUP_ID = p_real_attr_group_id
WHERE dummy_attribute_group_id = p_dummy_attr_group_id;
UPDATE PO_UDA_MAPPINGS
SET BID_ITEM_PRICE_GROUP_ID = p_real_attr_group_id
WHERE dummy_attribute_group_id = p_dummy_attr_group_id;
INSERT INTO EGO_ATTR_GROUP_DL
(
attr_group_id
,data_level_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
VALUES
(
p_attr_group_id
,p_data_level_id
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.User_Id
,SYSDATE
,FND_GLOBAL.Login_Id
);
SELECT DESCRIPTIVE_FLEX_CONTEXT_NAME,
DESCRIPTION,
SECURITY_TYPE,
MULTI_ROW,
VARIANT,
NUM_OF_COLS,
NUM_OF_ROWS,
OWNING_PARTY_ID,
REGION_CODE,
VIEW_PRIVILEGE_ID,
EDIT_PRIVILEGE_ID,
BUSINESS_EVENT_FLAG,
PRE_BUSINESS_EVENT_FLAG,
FL_CTX_EXT.CREATED_BY
INTO l_display_name,
l_attr_group_desc,
l_security_type,
l_multi_row_attrib_group,
l_variant_attrib_group,
l_num_of_cols,
l_num_of_rows,
l_owning_company_id,
l_region_code,
l_view_privilege_id,
l_edit_privilege_id,
l_business_event_flag,
l_pre_business_event_flag,
l_owner
FROM FND_DESCR_FLEX_CONTEXTS_TL FL_CTX_TL,
EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT
WHERE FL_CTX_EXT.APPLICATION_ID = FL_CTX_TL.APPLICATION_ID
AND FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = FL_CTX_TL.DESCRIPTIVE_FLEXFIELD_NAME
AND FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = FL_CTX_TL.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND FL_CTX_EXT.ATTR_GROUP_ID = p_attribute_group_id
AND FL_CTX_TL.LANGUAGE = userenv('LANG');
update_real_meta_attr_group(p_glb_api_version, p_glb_application_id, p_attribute_group_id, get_map_table_details(p_attribute_group_id, 'PO_HEADER_EXT_ATTRS'), 'PO_HEADER_EXT_ATTRS');
update_real_meta_attr_group(p_glb_api_version, p_glb_application_id, p_attribute_group_id, get_map_table_details(p_attribute_group_id, 'PO_LINE_EXT_ATTRS'),'PO_LINE_EXT_ATTRS');
update_real_meta_attr_group(p_glb_api_version, p_glb_application_id, p_attribute_group_id, get_map_table_details(p_attribute_group_id, 'PO_DISTRIBUTIONS_EXT_ATTRS'),'PO_DISTRIBUTIONS_EXT_ATTRS');
update_real_meta_attr_group(p_glb_api_version, p_glb_application_id, p_attribute_group_id, get_map_table_details(p_attribute_group_id, 'PO_SHIPMENTS_EXT_ATTRS'),'PO_SHIPMENTS_EXT_ATTRS');
update_real_meta_attr_group(p_glb_api_version, p_glb_application_id, p_attribute_group_id, get_map_table_details(p_attribute_group_id, 'PO_REQ_HEADER_EXT_ATTRS'),'PO_REQ_HEADER_EXT_ATTRS');
update_real_meta_attr_group(p_glb_api_version, p_glb_application_id, p_attribute_group_id, get_map_table_details(p_attribute_group_id, 'PO_REQ_LINE_EXT_ATTRS'),'PO_REQ_LINE_EXT_ATTRS');
update_real_meta_attr_group(p_glb_api_version, p_glb_application_id, p_attribute_group_id, get_map_table_details(p_attribute_group_id, 'PO_REQ_DIST_EXT_ATTRS'),'PO_REQ_DIST_EXT_ATTRS');
update_real_meta_attr_group(p_glb_api_version, p_glb_application_id, p_attribute_group_id, get_map_table_details(p_attribute_group_id, 'PON_AUC_PRICES_EXT_ATTRS'),'PON_AUC_PRICES_EXT_ATTRS');
update_real_meta_attr_group(p_glb_api_version, p_glb_application_id, p_attribute_group_id, get_map_table_details(p_attribute_group_id, 'PON_AUC_HDRS_EXT_ATTRS'),'PON_AUC_HDRS_EXT_ATTRS');
update_real_meta_attr_group(p_glb_api_version, p_glb_application_id, p_attribute_group_id, get_map_table_details(p_attribute_group_id, 'PON_BID_HDRS_EXT_ATTRS'),'PON_BID_HDRS_EXT_ATTRS');
update_real_meta_attr_group(p_glb_api_version, p_glb_application_id, p_attribute_group_id, get_map_table_details(p_attribute_group_id, 'PON_BID_PRICES_EXT_ATTRS'),'PON_BID_PRICES_EXT_ATTRS');
delete_real_attr_group(p_glb_api_version, get_map_table_details(p_attribute_group_id, p_attribute_group_type));
DELETE FROM ego_attr_group_dl
WHERE ATTR_GROUP_ID = get_map_table_details(p_attribute_group_id, p_attribute_group_type);
l_true_false := update_map_table(p_attribute_group_id, p_attribute_group_type);
DELETE FROM PO_UDA_MAPPINGS
WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
PROCEDURE delete_real_attr_group
(
p_glb_api_version IN NUMBER
,p_real_attribute_group_id IN NUMBER
)
IS
l_return_status VARCHAR2(100);
d_api_name CONSTANT VARCHAR2(30) := 'delete_real_attr_group';
EGO_EXT_FWK_PUB.Delete_Attribute_Group(
p_api_version => p_glb_api_version
,p_attr_group_id => p_real_attribute_group_id
,p_init_msg_list => fnd_api.g_FALSE
,p_commit => fnd_api.g_FALSE
,x_return_status => l_return_status
,x_errorcode => l_errorcode
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
END delete_real_attr_group;
PROCEDURE update_real_meta_attr_group
(
p_glb_api_version IN NUMBER
,p_glb_application_id IN NUMBER
,p_dummy_attribute_group_id IN NUMBER
,p_real_attribute_group_id IN NUMBER
,p_real_attribute_group_type IN VARCHAR2
)
IS
l_return_status VARCHAR2(100);
d_api_name CONSTANT VARCHAR2(30) := 'update_real_meta_attr_group';
CURSOR c_get_attributes_to_delete
(
p_real_attr_grp_type IN VARCHAR2
,p_dummy_attr_grp_type IN VARCHAR2
,p_dummy_internal_name IN VARCHAR2
)
IS
SELECT END_USER_COLUMN_NAME
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_real_attr_grp_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_dummy_internal_name
AND END_USER_COLUMN_NAME NOT IN
(
SELECT END_USER_COLUMN_NAME
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_dummy_attr_grp_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_dummy_internal_name
);
SELECT END_USER_COLUMN_NAME
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_dummy_attr_grp_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_dummy_internal_name;
SELECT SECURITY_TYPE, MULTI_ROW, BUSINESS_EVENT_FLAG,
PRE_BUSINESS_EVENT_FLAG, NUM_OF_COLS, NUM_OF_ROWS, VARIANT,
OWNING_PARTY_ID, REGION_CODE, VIEW_PRIVILEGE_ID, EDIT_PRIVILEGE_ID
INTO l_security_type, l_multi_row, l_business_event_flag,
l_pre_business_event_flag, l_num_of_cols, l_num_of_rows, l_variant,
l_owning_party_id, l_region_code, l_view_privilege_id, l_edit_privilege_id
FROM ego_fnd_dsc_flx_ctx_ext
WHERE ATTR_GROUP_ID = p_dummy_attribute_group_id;
SELECT DESCRIPTIVE_FLEX_CONTEXT_CODE
INTO l_internal_name
FROM ego_fnd_dsc_flx_ctx_ext
WHERE ATTR_GROUP_ID = p_dummy_attribute_group_id;
SELECT DESCRIPTIVE_FLEX_CONTEXT_NAME, DESCRIPTION
INTO l_display_name, l_attr_grp_desc
FROM FND_DESCR_FLEX_CONTEXTS_TL
WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = l_internal_name
AND APPLICATION_ID = p_glb_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME IN
(
SELECT DESCRIPTIVE_FLEXFIELD_NAME
FROM ego_fnd_dsc_flx_ctx_ext
WHERE ATTR_GROUP_ID = p_dummy_attribute_group_id
)
AND LANGUAGE = USERENV('LANG')
AND USERENV('LANG') in (LANGUAGE , SOURCE_LANG);
ego_ext_fwk_pub.update_attribute_group(
p_api_version => p_glb_api_version
,p_attr_group_id => p_real_attribute_group_id
,p_display_name => l_display_name
,p_attr_group_desc => l_attr_grp_desc
,p_security_type => l_security_type
,p_multi_row_attrib_group => l_multi_row
,p_variant_attrib_group => l_variant
,p_num_of_cols => l_num_of_cols
,p_num_of_rows => l_num_of_rows
,p_owning_company_id => l_owning_party_id
,p_region_code => l_region_code
,p_view_privilege_id => l_view_privilege_id
,p_edit_privilege_id => l_edit_privilege_id
,p_business_event_flag => l_business_event_flag
,p_pre_business_event_flag => l_pre_business_event_flag
,p_init_msg_list => fnd_api.g_FALSE
,p_commit => fnd_api.g_FALSE
,x_return_status => l_return_status
,x_errorcode => l_errorcode
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
OPEN c_get_attributes_to_delete
(
l_real_attr_grp_type
,'PO_UDA_DUMMY_EXT_ATTRS'
,l_internal_name
);
FETCH c_get_attributes_to_delete INTO l_attribute_name;
EXIT WHEN c_get_attributes_to_delete%NOTFOUND;
ego_ext_fwk_pub.delete_attribute(
p_api_version => p_glb_api_version
,p_application_id => p_glb_application_id
,p_attr_group_type => l_real_attr_grp_type
,p_attr_group_name => l_internal_name
,p_attr_name => l_attribute_name
,p_init_msg_list => fnd_api.g_FALSE
,p_commit => fnd_api.g_FALSE
,x_return_status => l_return_status
,x_errorcode => l_errorcode
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
CLOSE c_get_attributes_to_delete;
SELECT TL.FORM_LEFT_PROMPT,
TL.DESCRIPTION,
CL.COLUMN_SEQ_NUM,
EG.DATA_TYPE,
CL.REQUIRED_FLAG,
EG.SEARCH_FLAG,
EG.READ_ONLY_FLAG,
EG.APPLICATION_COLUMN_NAME,
CL.FLEX_VALUE_SET_ID,
EG.INFO_1,
CL.DEFAULT_VALUE,
EG.UNIQUE_KEY_FLAG,
CL.ENABLED_FLAG,
CL.DISPLAY_FLAG,
EG.UOM_CLASS,
EG.CONTROL_LEVEL,
EG.ATTRIBUTE_CODE,
EG.VIEW_IN_HIERARCHY_CODE,
EG.EDIT_IN_HIERARCHY_CODE,
EG.CUSTOMIZATION_LEVEL,
CL.CREATED_BY,
CL.CREATION_DATE
INTO l_attr_display_name,
l_description,
l_sequence,
l_data_type,
l_required,
l_searchable,
l_read_only_flag,
l_column,
l_value_set_id,
l_info_1,
l_default_value,
l_unique_key_flag,
l_enabled,
l_display,
l_uom_class,
l_control_level,
l_attribute_code,
l_view_in_hierarchy_code,
l_edit_in_hierarchy_code,
l_customization_level,
l_owner,
l_lud
FROM FND_DESCR_FLEX_COL_USAGE_TL TL,
FND_DESCR_FLEX_COLUMN_USAGES CL,
EGO_FND_DF_COL_USGS_EXT EG
WHERE CL.APPLICATION_ID = p_glb_application_id
AND EG.APPLICATION_ID = CL.APPLICATION_ID
AND EG.DESCRIPTIVE_FLEXFIELD_NAME = CL.DESCRIPTIVE_FLEXFIELD_NAME
AND EG.DESCRIPTIVE_FLEX_CONTEXT_CODE = CL.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND EG.APPLICATION_COLUMN_NAME = CL.APPLICATION_COLUMN_NAME
AND CL.APPLICATION_ID = TL.APPLICATION_ID
AND CL.DESCRIPTIVE_FLEXFIELD_NAME = TL.DESCRIPTIVE_FLEXFIELD_NAME
AND CL.DESCRIPTIVE_FLEX_CONTEXT_CODE = TL.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND CL.APPLICATION_COLUMN_NAME = TL.APPLICATION_COLUMN_NAME
AND TL.LANGUAGE = USERENV('LANG')
AND CL.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_UDA_DUMMY_EXT_ATTRS'
AND CL.DESCRIPTIVE_FLEX_CONTEXT_CODE = l_internal_name
AND CL.END_USER_COLUMN_NAME = l_attribute_name;
--bug15893437, commented the sql%found condition as it is not necessary and is inhibiting the call to ego_ext_fwk_pub.update_attribute or ego_ext_fwk_pub.create_attribute
--IF SQL%FOUND THEN
d_progress := 80;
ego_ext_fwk_pub.update_attribute
(
p_api_version => p_glb_api_version
,p_application_id => p_glb_application_id
,p_attr_group_type => l_real_attr_grp_type
,p_attr_group_name => l_internal_name
,p_internal_name => l_attribute_name
,p_display_name => l_attr_display_name
,p_description => l_description
,p_sequence => l_sequence
,p_required => l_required
,p_searchable => l_searchable
,p_read_only_flag => l_read_only_flag
,p_column => l_column
,p_value_set_id => l_value_set_id
,p_info_1 => l_info_1
,p_default_value => l_default_value
,p_unique_key_flag => l_unique_key_flag
,p_enabled => l_enabled
,p_display => l_display
,p_control_level => l_control_level
,p_attribute_code => l_attribute_code
,p_view_in_hierarchy_code => l_view_in_hierarchy_code
,p_edit_in_hierarchy_code => l_edit_in_hierarchy_code
,p_customization_level => l_customization_level
,p_owner => l_owner
,p_lud => l_lud
,p_init_msg_list => fnd_api.g_FALSE
,p_commit => fnd_api.g_FALSE
,p_is_nls_mode => fnd_api.g_FALSE
,p_uom_class => l_uom_class
,x_return_status => l_return_status
,x_errorcode => l_errorcode
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
END update_real_meta_attr_group;
SELECT 1
INTO l_present
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_real_attr_grp_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_real_internal_name
AND END_USER_COLUMN_NAME = p_attribute_name
AND ROWNUM < 2;
FUNCTION update_map_table( p_attribute_group_id IN NUMBER, p_data_level_type IN VARCHAR2 )
RETURN VARCHAR2
IS
d_api_name CONSTANT VARCHAR2(30) := 'update_map_table';
UPDATE PO_UDA_MAPPINGS
SET PO_HEADER_GROUP_ID = NULL
WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
UPDATE PO_UDA_MAPPINGS
SET PO_LINE_GROUP_ID = NULL
WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
UPDATE PO_UDA_MAPPINGS
SET PO_DISTRIBUTIONS_GROUP_ID = NULL
WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
UPDATE PO_UDA_MAPPINGS
SET PO_SHIPMENTS_GROUP_ID = NULL
WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
UPDATE PO_UDA_MAPPINGS
SET REQ_HEADER_GROUP_ID = NULL
WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
UPDATE PO_UDA_MAPPINGS
SET REQ_LINE_GROUP_ID = NULL
WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
UPDATE PO_UDA_MAPPINGS
SET REQ_DISTRIBUTION_GROUP_ID = NULL
WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
UPDATE PO_UDA_MAPPINGS
SET PON_AUC_ITEM_PRICE_GROUP_ID = NULL
WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
UPDATE PO_UDA_MAPPINGS
SET PON_AUC_HEADER_GROUP_ID = NULL
WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
UPDATE PO_UDA_MAPPINGS
SET BID_HEADER_GROUP_ID = NULL
WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
UPDATE PO_UDA_MAPPINGS
SET BID_ITEM_PRICE_GROUP_ID = NULL
WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
END update_map_table;
SELECT PO_HEADER_GROUP_ID,
PO_LINE_GROUP_ID,
PO_DISTRIBUTIONS_GROUP_ID,
PO_SHIPMENTS_GROUP_ID,
REQ_HEADER_GROUP_ID,
REQ_LINE_GROUP_ID,
REQ_DISTRIBUTION_GROUP_ID,
PON_AUC_ITEM_PRICE_GROUP_ID,
PON_AUC_HEADER_GROUP_ID,
BID_HEADER_GROUP_ID,
BID_ITEM_PRICE_GROUP_ID
INTO l_po_header_group_id,
l_po_line_group_id,
l_po_distributions_group_id,
l_po_shipments_group_id,
l_req_header_group_id,
l_req_line_group_id,
l_req_distribution_group_id,
l_pon_auc_item_price_group_id,
l_pon_auc_header_group_id,
l_bid_header_group_id,
l_bid_item_price_group_id
FROM PO_UDA_MAPPINGS
WHERE DUMMY_ATTRIBUTE_GROUP_ID = p_attribute_group_id;
SELECT 1, dummy_def.DESCRIPTIVE_FLEX_CONTEXT_CODE
INTO l_data_level_present, x_internal_name
FROM ego_fnd_dsc_flx_ctx_ext dummy_def,
ego_data_level_b levels,
ego_attr_group_dl dummy_level
WHERE levels.DATA_LEVEL_ID = dummy_level.DATA_LEVEL_ID
AND dummy_def.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_UDA_DUMMY_EXT_ATTRS'
AND dummy_def.ATTR_GROUP_ID = dummy_level.ATTR_GROUP_ID
AND dummy_def.ATTR_GROUP_ID = p_attribute_group_id
AND levels.DATA_LEVEL_NAME = p_data_level;
PROCEDURE DELETE_TEMP_USAGES
( p_api_version IN NUMBER,
p_template_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
d_api_name CONSTANT VARCHAR2(30) := 'DELETE_TEMP_USAGES';
SELECT UNIQUE(association_id) FROM po_uda_ag_template_usages
WHERE template_id = p_template_id;
ego_ext_fwk_pub.Delete_Association(
p_api_version => 1.0, -- IN NUMBER
p_association_id => association_ids_rec.association_id, -- IN NUMBER
p_init_msg_list => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
p_commit => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
p_force => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
x_return_status => l_return_status, -- OUT NOCOPY VARCHAR2
x_errorcode => l_errorcode, -- OUT NOCOPY NUMBER
x_msg_count => l_msg_count, -- OUT NOCOPY NUMBER
x_msg_data => l_msg_data); -- OUT NOCOPY VARCHAR2
DELETE FROM PO_UDA_AG_TEMPLATE_USAGES
WHERE association_id = association_ids_rec.association_id;
PO_LOG.stmt(d_module, d_progress, 'Deleted association and usages for association_id:', association_ids_rec.association_id);
END DELETE_TEMP_USAGES;
PROCEDURE DELETE_TEMP_SINGLE_USAGE(p_api_version IN NUMBER,
p_template_id IN NUMBER,
p_usage_name IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
d_api_name CONSTANT VARCHAR2(30) := 'DELETE_TEMP_USAGE';
SELECT USAGE_ID,ATTRIBUTE_CATEGORY
FROM po_uda_ag_template_usages
WHERE template_id = p_template_id
AND ATTRIBUTE_CATEGORY = p_usage_name ;
DELETE_USAGE
( p_api_version => 1.0,
p_usage_id => c_get_usage_id_rec.usage_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
DELETE FROM po_uda_ag_template_usages WHERE usage_id = c_get_usage_id_rec.usage_id;
END DELETE_TEMP_SINGLE_USAGE;
l_select_columns VARCHAR2(15000);
l_select_query VARCHAR2(15000);
SELECT entity_code
FROM po_uda_ag_templates
WHERE template_id = p_template_id;
x_select_query => l_select_query,
x_user_col_name => l_select_columns);
IF (l_select_columns IS NOT NULL AND l_select_query IS NOT NULL) THEN
l_create_view := 'CREATE OR REPLACE VIEW ' || l_view_name || '('|| l_select_columns ||' ) AS ( '|| l_select_query ||' )' ;
/*update database_view_name in po_ag_templates table*/
UPDATE po_uda_ag_templates SET database_view_name = l_view_name
WHERE template_id = p_template_id;
x_select_query => l_select_query,
x_user_col_name => l_select_columns);
IF (l_select_columns IS NOT NULL AND l_select_query IS NOT NULL) THEN
l_create_view := 'CREATE OR REPLACE VIEW ' || l_hist_view_name || '('|| l_select_columns ||' ) AS ( '|| l_select_query ||' )' ;
l_select_columns VARCHAR2(10000);
l_select_query VARCHAR2(22500);
x_select_query => l_select_query,
x_user_col_name => l_select_columns);
IF (l_select_columns IS NOT NULL AND l_select_query IS NOT NULL) THEN
l_create_view := 'CREATE OR REPLACE VIEW ' || l_view_name || '('|| l_select_columns ||' ) AS ( '|| l_select_query ||' )' ;
SELECT document_level, document_type, document_style_id, revision, functional_area
FROM po_uda_ag_templates
WHERE template_id = p_template_id;
( SELECT tbl.PO_HEADER_ID , pkt0.C_EXT_ATTR2 , pkt0.C_EXT_ATTR3 , pkt0.C_EXT_ATTR5 , pkt0.C_EXT_ATTR1 , pkt0.C_EXT_ATTR4 ,
pkt1.C_EXT_ATTR2 , pkt1.C_EXT_ATTR3 , pkt1.C_EXT_ATTR5 , pkt1.C_EXT_ATTR1 , pkt1.C_EXT_ATTR4
FROM PO_HEADERS_ALL_EXT_VL pkt0 , PO_HEADERS_ALL tbl , PO_HEADERS_ALL_EXT_VL pkt1
WHERE pkt0.attr_group_id (+) = 1734 AND tbl.PO_HEADER_ID = pkt0.PO_HEADER_ID (+) AND pkt0.pk1_value is NULL
AND pkt1.attr_group_id (+) = 1740 AND tbl.PO_HEADER_ID = pkt1.PO_HEADER_ID (+) AND pkt1.pk1_value is NULL )
*/
PROCEDURE get_query_for_temp_view
( p_api_version IN NUMBER,
p_template_id IN NUMBER,
p_hist_view IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_select_query OUT NOCOPY VARCHAR2,
x_user_col_name OUT NOCOPY VARCHAR2
) IS
d_api_name CONSTANT VARCHAR2(30) := 'get_query_for_temp_view';
l_query_select VARCHAR2(8000) := null;
l_select_query VARCHAR2(32500) := NULL;
SELECT pt.entity_code, et.APPLICATION_VL_NAME, et.APPLICATION_TL_TABLE_NAME
FROM po_uda_ag_templates pt, EGO_FND_DESC_FLEXS_EXT et
WHERE pt.template_id = p_template_id
AND et.DESCRIPTIVE_FLEXFIELD_NAME = pt.entity_code;
SELECT distinct(attribute_group_id)
FROM po_uda_ag_template_usages u,
ego_fnd_dsc_flx_ctx_ext a
WHERE u.template_id = p_template_id
and u.attribute_group_id = a.attr_group_id
and a.multi_row = 'N';
SELECT efdcue.APPLICATION_COLUMN_NAME AS APPLICATION_COLUMN_NAME
FROM ego_fnd_dsc_flx_ctx_ext efdfce , ego_fnd_df_col_usgs_ext efdcue
WHERE ATTR_GROUP_ID = p_attr_grp_id
AND efdfce.DESCRIPTIVE_FLEX_CONTEXT_CODE = efdcue.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND efdcue.DESCRIPTIVE_FLEXFIELD_NAME <> 'PO_UDA_DUMMY_EXT_ATTRS'
AND efdcue.DESCRIPTIVE_FLEXFIELD_NAME = p_entity_code;
SELECT fo.DATABASE_OBJECT_NAME, fo.PK1_COLUMN_NAME
FROM EGO_OBJECT_EXT_TABLES_B eb, fnd_objects fo
WHERE eb.OBJECT_ID = fo.object_id
AND eb.EXT_TABLE_NAME = l_prod_ext_tl_table;
and get all its attributes group by group and then construct the query. Finally the select query and
the columns will be returned back and the view will be created by calling program.
*/
for c_attr_group_ids_rec IN c_attr_group_ids(p_template_id)
LOOP
IF i=0 THEN
j:=1;
l_query_select := 'tbl.'|| l_pk_col_name || ' , ' || l_table||'.'||l_attr_appl_name;
l_query_select := l_query_select||' , '||l_table||'.'||l_attr_appl_name;
l_query_select := l_query_select||' , '||l_table||'.'||l_attr_appl_name;
IF (l_query_select IS NOT NULL AND l_query_from IS NOT NULL AND l_query_where IS NOT NULL) THEN
x_select_query := 'SELECT '|| l_query_select || ' FROM '|| l_query_from ||' WHERE ' || l_query_where;
PO_LOG.stmt(d_module, d_progress, 'x_select_query', x_select_query);
(SELECT tbl.bid_number, pkt0.N_EXT_ATTR1 , pkt0.C_EXT_ATTR1 , pkt0.C_EXT_ATTR2 , pkt0.C_EXT_ATTR3 , pkt0.C_EXT_ATTR4 , pkt0.TL_EXT_ATTR2
FROM PON_BID_HEADERS_EXT_VL pkt0 , PON_BID_HEADERS tbl
WHERE pkt0.attr_group_id (+) = 2 AND tbl.bid_number = pkt0.bid_number )
*/
PROCEDURE get_query_for_entity_view(p_api_version IN NUMBER,
p_entity_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_select_query OUT NOCOPY VARCHAR2,
x_user_col_name OUT NOCOPY VARCHAR2)
IS
d_api_name CONSTANT VARCHAR2(30) := 'get_query_for_entity_view';
l_query_select VARCHAR2(10000) := null;
l_select_query VARCHAR2(22500) := NULL;
SELECT et.APPLICATION_VL_NAME, et.APPLICATION_TL_TABLE_NAME
FROM EGO_FND_DESC_FLEXS_EXT et
WHERE et.DESCRIPTIVE_FLEXFIELD_NAME = p_entity_code;
SELECT DISTINCT(u.attribute_group_id)
FROM po_uda_ag_template_usages u, po_uda_ag_templates b
WHERE b.entity_code = p_entity_code
AND b.template_id = u.template_id;
SELECT efdcue.APPLICATION_COLUMN_NAME AS APPLICATION_COLUMN_NAME
FROM ego_fnd_dsc_flx_ctx_ext efdfce , ego_fnd_df_col_usgs_ext efdcue
WHERE ATTR_GROUP_ID = p_attr_grp_id
AND efdfce.DESCRIPTIVE_FLEX_CONTEXT_CODE = efdcue.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND efdcue.DESCRIPTIVE_FLEXFIELD_NAME = p_entity_code
AND efdcue.DESCRIPTIVE_FLEXFIELD_NAME <> 'PO_UDA_DUMMY_EXT_ATTRS';
SELECT fo.DATABASE_OBJECT_NAME, fo.PK1_COLUMN_NAME
FROM EGO_OBJECT_EXT_TABLES_B eb, fnd_objects fo
WHERE eb.OBJECT_ID = fo.object_id
AND eb.EXT_TABLE_NAME = l_prod_ext_tl_table;
and get all its attributes group by group and then construct the query. Finally the select query and
the columns will be returned back and the view will be created by calling program.
*/
for c_attr_group_ids_rec IN c_attr_group_ids(p_entity_code)
LOOP
IF i=0 THEN
j:=1;
l_query_select := 'tbl.'|| l_pk_col_name || ' , ' ||l_table||'.'||l_attr_appl_name;
l_query_select := l_query_select||' , '||l_table||'.'||l_attr_appl_name;
l_query_select := l_query_select||' , '||l_table||'.'||l_attr_appl_name;
IF (l_query_select IS NOT NULL AND l_query_from IS NOT NULL AND l_query_where IS NOT NULL) THEN
x_select_query := 'SELECT '|| l_query_select || ' FROM '|| l_query_from ||' WHERE ' || l_query_where;
PO_LOG.stmt(d_module, d_progress, 'x_select_query', x_select_query);
select fdfcu.APPLICATION_COLUMN_NAME AS APPLICATION_COLUMN_NAME, fdfcu.END_USER_COLUMN_NAME AS END_USER_COLUMN_NAME,
efdfce.DESCRIPTIVE_FLEX_CONTEXT_CODE AS AG_internal_name
FROM fnd_descr_flex_column_usages fdfcu,
ego_fnd_df_col_usgs_ext efdcue,
ego_fnd_dsc_flx_ctx_ext efdfce
where efdfce.ATTR_GROUP_ID = P_ATTR_GRP_ID
AND efdfce.DESCRIPTIVE_FLEX_CONTEXT_CODE = fdfcu.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND fdfcu.DESCRIPTIVE_FLEX_CONTEXT_CODE = efdcue.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND fdfcu.APPLICATION_COLUMN_NAME = efdcue.APPLICATION_COLUMN_NAME
AND Upper(fdfcu.APPLICATION_COLUMN_NAME) = Upper(P_APP_COL_NAME);
SELECT action_id FROM ego_actions_b
WHERE ATTR_GROUP_ID = p_attr_group_id
AND classification_code = p_src_template_code
AND OBJECT_ID = p_object_id;
SELECT b.OBJECT_ID, b.CLASSIFICATION_CODE, b.SEQUENCE, b.ACTION_NAME, b.FUNCTION_ID,b.SECURITY_PRIVILEGE_ID, b.ENABLE_KEY_ATTRIBUTES, tl.DESCRIPTION
FROM EGO_ACTIONS_tl tl, EGO_ACTIONS_B b
WHERE b.action_id = p_action_id AND b.action_id = tl.action_id AND tl.LANGUAGE = userenv('LANG');
SELECT ACTION_ID, EXECUTION_METHOD, DISPLAY_STYLE, PROMPT_APPLICATION_ID, PROMPT_MESSAGE_NAME, VISIBILITY_FLAG, PROMPT_FUNCTION_ID, VISIBILITY_FUNC_ID
FROM EGO_ACTION_DISPLAYS_B
WHERE ACTION_ID = p_action_id;
SELECT 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, MAPPED_UOM_PARAMETER, VALUE_UOM_SOURCE, FIXED_UOM
FROM EGO_MAPPINGS_B
WHERE function_id = p_function_id
AND MAPPED_OBJ_PK1_VAL = p_action_id;
SELECT document_level, document_type, document_style_id, revision, functional_area
FROM po_uda_ag_templates
WHERE template_id = p_template_id;
SELECT f.FLEX_VALUE_SET_ID, fv.VALIDATION_TYPE FROM FND_DESCR_FLEX_COL_USAGE_VL f, PO_UDA_AG_TEMPLATE_USAGES t , fnd_flex_value_sets fv
WHERE DESCRIPTIVE_FLEXFIELD_NAME LIKE 'PO_UDA_TEMPLATE_USAGES'
AND f.DESCRIPTIVE_FLEX_CONTEXT_CODE = T.ATTRIBUTE_CATEGORY
AND t. usage_id = p_usage_id AND f.APPLICATION_COLUMN_NAME = p_attr_col
AND f.FLEX_VALUE_SET_ID = fv.FLEX_VALUE_SET_ID;
SELECT value_column_name, value_column_type,
id_column_name, id_column_type,
application_table_name, additional_where_clause
from fnd_flex_validation_tables
where flex_value_set_id = p_value_set_id;
SELECT FLEX_VALUE_MEANING
FROM FND_FLEX_VALUES_VL
WHERE flex_value_set_id = p_value_set_id
AND FLEX_VALUE = p_attr_value;
l_query := 'SELECT '||l_value_column_name ||' from (SELECT ' ||l_value_column_name ||' , '||l_id_column_name ||
' from ' || l_application_table_name ||' ' || l_additional_where_clause || ' ) where '
|| l_id_column_name || ' = ' ||'''' || p_attr_value || '''';
l_query := 'SELECT value_column ' ||' from (SELECT ' ||l_value_column_name ||' as value_column , '||l_id_column_name ||
' as id_column from ' || l_application_table_name || l_additional_where_clause || ' ) where id_column '
|| ' = ' ||'''' || p_attr_value || '''';
SELECT f.FLEX_VALUE_SET_ID, fv.VALIDATION_TYPE FROM FND_DESCR_FLEX_COL_USAGE_VL f, PO_UDA_AG_TEMPLATE_USAGES t , fnd_flex_value_sets fv
WHERE DESCRIPTIVE_FLEXFIELD_NAME LIKE 'PO_UDA_TEMPLATE_USAGES'
AND f.DESCRIPTIVE_FLEX_CONTEXT_CODE = T.ATTRIBUTE_CATEGORY
AND t. usage_id = p_usage_id AND f.APPLICATION_COLUMN_NAME = p_attr_col
AND f.FLEX_VALUE_SET_ID = fv.FLEX_VALUE_SET_ID;
SELECT value_column_name, value_column_type,
id_column_name, id_column_type,
application_table_name, additional_where_clause
from fnd_flex_validation_tables
where flex_value_set_id = p_value_set_id;
SELECT FLEX_VALUE_MEANING
FROM FND_FLEX_VALUES_VL
WHERE flex_value_set_id = p_value_set_id
AND FLEX_VALUE = p_attr_value;
l_query := 'SELECT '||l_value_column_name ||' from (SELECT ' ||l_value_column_name ||' , '||l_id_column_name ||
' from ' || l_application_table_name ||' ' || l_additional_where_clause || ' ) where '
|| l_id_column_name || ' = ' ||'''' || p_attr_value || '''';
l_query := 'SELECT '||l_value_column_name ||' from (SELECT ' ||l_value_column_name ||' , '||l_id_column_name ||
' from ' || l_application_table_name ||' where ' || l_additional_where_clause || ' ) where '
|| l_id_column_name || ' = ' ||'''' || p_attr_value || '''';
SELECT template_id, effective_from, revision FROM po_uda_ag_templates
WHERE functional_area = p_functional_area
AND Nvl(document_type,'null') = Nvl(p_document_type, 'null')
AND Nvl(document_style_id, -1) = Nvl(p_document_style_id,-1)
AND document_level = p_document_level
AND revision = (
SELECT max(revision)
FROM po_uda_ag_templates
WHERE functional_area = p_functional_area
AND Nvl(document_type,'null') = Nvl(p_document_type, 'null')
AND Nvl(document_style_id, -1) = Nvl(p_document_style_id,-1)
AND document_level = p_document_level);
SELECT revision INTO l_current_revision FROM po_uda_ag_templates WHERE template_id = p_template_id;
l_action_type := 'UPDATE';
l_action_type := 'UPDATE';
ELSIF (l_effective_from <= SYSDATE AND can_update_delete(l_template_id) = 'true') THEN
l_action_type := 'UPDATE';
SELECT template_id
FROM po_uda_ag_templates
WHERE functional_area = p_functional_area
AND Nvl(document_type, 'null') = Nvl(p_document_type, 'null')
AND Nvl(document_style_id, -1) = Nvl(p_document_style_id, -1)
AND document_level = p_document_level
AND (SYSDATE BETWEEN effective_from AND nvl(effective_to,sysdate+1) );
/** Before deleting usage, we have to delete the association for that usage
* the actions, the action displays and the function mappings.
*/
PROCEDURE DELETE_USAGE
( p_api_version IN NUMBER,
p_usage_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
d_api_name CONSTANT VARCHAR2(30) := 'DELETE_USAGE';
SELECT ptu.association_id, ptu.attribute_group_id, ptu.template_id, umv.object_id
FROM po_uda_ag_template_usages ptu, uda_setup_metadata_v umv
WHERE ptu.usage_id = p_usage_id
AND ptu.template_id = umv.template_id;
SELECT action_id, function_id FROM EGO_ACTIONS_B
WHERE object_id = p_object_id
AND classification_code = p_template_id
AND attr_group_id = p_attribute_group_id;
SELECT Count(USAGE_ID)
INTO l_usage_ag_count
FROM po_uda_ag_template_usages
WHERE attribute_group_id = l_attribute_group_id
AND template_id = l_template_id ;
Delete_Association(
p_api_version => 1.0, -- IN NUMBER
p_association_id => l_association_id, -- IN NUMBER
p_init_msg_list => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
p_commit => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
p_force => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
x_return_status => l_return_status, -- OUT NOCOPY VARCHAR2
x_errorcode => l_errorcode, -- OUT NOCOPY NUMBER
x_msg_count => l_msg_count, -- OUT NOCOPY NUMBER
x_msg_data => l_msg_data); -- OUT NOCOPY VARCHAR2
PO_LOG.stmt(d_module, d_progress, 'Delete Association succesful. x_return_status: ', x_return_status);
ego_ext_fwk_pub.Delete_Action (
p_api_version => 1.0
,p_action_id => c_get_action_id_rec.action_id
,p_init_msg_list => 'F'
,p_commit => 'F'
,x_return_status => l_return_status
,x_errorcode => l_errorcode, -- OUT NOCOPY NUMBER
x_msg_count => l_msg_count, -- OUT NOCOPY NUMBER
x_msg_data => l_msg_data);
PO_LOG.stmt(d_module, d_progress, 'Delete Action successful. x_return_status: ', x_return_status);
ego_ext_fwk_pub.Delete_Action_Display (
p_api_version => 1.0
,p_action_id => c_get_action_id_rec.action_id
,p_init_msg_list => 'F'
,p_commit => 'F'
,x_return_status => l_return_status
,x_errorcode => l_errorcode, -- OUT NOCOPY NUMBER
x_msg_count => l_msg_count, -- OUT NOCOPY NUMBER
x_msg_data => l_msg_data);
PO_LOG.stmt(d_module, d_progress, 'Delete_Action_Display successful. x_return_status: ', x_return_status);
ego_ext_fwk_pub.Delete_Func_Mapping (
p_api_version => 1.0
,p_function_id => c_get_action_id_rec.function_id
,p_mapped_obj_type => 'A' -- hardcoded to 'A'
,p_mapped_obj_pk1_value => c_get_action_id_rec.action_id
,p_init_msg_list => 'F'
,p_commit => 'F'
,x_return_status => l_return_status
,x_errorcode => l_errorcode -- OUT NOCOPY NUMBER
,x_msg_count => l_msg_count -- OUT NOCOPY NUMBER
,x_msg_data => l_msg_data);
PO_LOG.stmt(d_module, d_progress, 'Delete failed. Rollback happened. x_return_status: ', x_return_status);
END DELETE_USAGE;
d_api_name CONSTANT VARCHAR2(30) := 'DELETE_USAGE';
SELECT USAGE_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,
ATTRIBUTE_GROUP_SEQUENCE
FROM po_uda_ag_template_usages
WHERE template_id = p_src_template_id;
SELECT object_id, DATA_LEVEL_NAME
FROM uda_setup_metadata_v
WHERE template_id = p_src_template_id;
SELECT EGO_ASSOCS_S.NEXTVAL
FROM dual;
SELECT PO_UDA_AG_TEMPLATE_USAGES_S.NEXTVAL
FROM dual;
SAVEPOINT start_insert_usages;
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,
ASSOCIATION_ID,
USAGE_ID,
ATTRIBUTE_GROUP_SEQUENCE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES (p_new_template_id,
c_get_usage_ids_rec.ATTRIBUTE_GROUP_ID,
c_get_usage_ids_rec.ATTRIBUTE_CATEGORY,
c_get_usage_ids_rec.ATTRIBUTE1,
c_get_usage_ids_rec.ATTRIBUTE2,
c_get_usage_ids_rec.ATTRIBUTE3,
c_get_usage_ids_rec.ATTRIBUTE4,
c_get_usage_ids_rec.ATTRIBUTE5,
c_get_usage_ids_rec.ATTRIBUTE6,
c_get_usage_ids_rec.ATTRIBUTE7,
c_get_usage_ids_rec.ATTRIBUTE8,
c_get_usage_ids_rec.ATTRIBUTE9,
c_get_usage_ids_rec.ATTRIBUTE10,
c_get_usage_ids_rec.ATTRIBUTE11,
c_get_usage_ids_rec.ATTRIBUTE12,
c_get_usage_ids_rec.ATTRIBUTE13,
c_get_usage_ids_rec.ATTRIBUTE14,
c_get_usage_ids_rec.ATTRIBUTE15,
c_get_usage_ids_rec.ATTRIBUTE16,
c_get_usage_ids_rec.ATTRIBUTE17,
c_get_usage_ids_rec.ATTRIBUTE18,
c_get_usage_ids_rec.ATTRIBUTE19,
c_get_usage_ids_rec.ATTRIBUTE20,
l_r_association_id,
l_usage_id,
c_get_usage_ids_rec.ATTRIBUTE_GROUP_SEQUENCE,
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id);
ROLLBACK TO SAVEPOINT start_insert_usages;
SELECT USAGE_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,
ATTRIBUTE_GROUP_SEQUENCE
FROM po_uda_ag_template_usages
WHERE template_id = p_src_template_id
AND ATTRIBUTE_CATEGORY = p_usage_name ;
SELECT object_id, DATA_LEVEL_NAME
FROM uda_setup_metadata_v
WHERE template_id = p_src_template_id;
SELECT EGO_ASSOCS_S.NEXTVAL
FROM dual;
SELECT PO_UDA_AG_TEMPLATE_USAGES_S.NEXTVAL
FROM dual;
SELECT Max(ATTRIBUTE_GROUP_SEQUENCE)
INTO l_max_attr_grp_seq_num
FROM po_uda_ag_template_usages
WHERE template_id = p_dest_template_id
AND ATTRIBUTE_CATEGORY = p_usage_name ;
SAVEPOINT start_insert_usages;
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,
ASSOCIATION_ID,
USAGE_ID,
ATTRIBUTE_GROUP_SEQUENCE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES (p_dest_template_id,
c_get_usage_id_rec.ATTRIBUTE_GROUP_ID,
c_get_usage_id_rec.ATTRIBUTE_CATEGORY,
c_get_usage_id_rec.ATTRIBUTE1,
c_get_usage_id_rec.ATTRIBUTE2,
c_get_usage_id_rec.ATTRIBUTE3,
c_get_usage_id_rec.ATTRIBUTE4,
c_get_usage_id_rec.ATTRIBUTE5,
c_get_usage_id_rec.ATTRIBUTE6,
c_get_usage_id_rec.ATTRIBUTE7,
c_get_usage_id_rec.ATTRIBUTE8,
c_get_usage_id_rec.ATTRIBUTE9,
c_get_usage_id_rec.ATTRIBUTE10,
c_get_usage_id_rec.ATTRIBUTE11,
c_get_usage_id_rec.ATTRIBUTE12,
c_get_usage_id_rec.ATTRIBUTE13,
c_get_usage_id_rec.ATTRIBUTE14,
c_get_usage_id_rec.ATTRIBUTE15,
c_get_usage_id_rec.ATTRIBUTE16,
c_get_usage_id_rec.ATTRIBUTE17,
c_get_usage_id_rec.ATTRIBUTE18,
c_get_usage_id_rec.ATTRIBUTE19,
c_get_usage_id_rec.ATTRIBUTE20,
l_r_association_id,
l_usage_id,
l_max_attr_grp_seq_num,
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id);
ROLLBACK TO SAVEPOINT start_insert_usages;
d_api_name CONSTANT VARCHAR2(30) := 'DELETE_USAGE';
SELECT b.template_id, b.ENTITY_CODE, b.FUNCTIONAL_AREA, b.DOCUMENT_LEVEL, b.DOCUMENT_TYPE, b.DOCUMENT_STYLE_ID, b.REVISION,
b.SRC_TEMPLATE_ID
FROM po_uda_ag_templates b
WHERE b.template_id = p_src_template_id;
SELECT tl.display_name, tl.comments, tl.LANGUAGE, tl.SOURCE_LANG
FROM po_uda_ag_templates_tl tl
WHERE tl.template_id = p_src_template_id;
SELECT PO_UDA_AG_TEMPLATES_S.NEXTVAL FROM dual;
INSERT INTO po_uda_ag_templates (TEMPLATE_ID,
ENTITY_CODE,
FUNCTIONAL_AREA,
DOCUMENT_LEVEL,
DOCUMENT_TYPE,
DOCUMENT_STYLE_ID,
REVISION,
SRC_TEMPLATE_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES (x_new_template_id,
c_src_template_details_rec.ENTITY_CODE,
c_src_template_details_rec.FUNCTIONAL_AREA,
c_src_template_details_rec.DOCUMENT_LEVEL,
c_src_template_details_rec.DOCUMENT_TYPE,
c_src_template_details_rec.DOCUMENT_STYLE_ID,
c_src_template_details_rec.REVISION+1,
c_src_template_details_rec.TEMPLATE_ID,
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id);
INSERT INTO po_uda_ag_templates_tl(template_id,
display_name,
comments,
LANGUAGE,
source_lang,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES (x_new_template_id,
c_src_template_tl_details_rec.display_name,
c_src_template_tl_details_rec.comments,
c_src_template_tl_details_rec.LANGUAGE,
c_src_template_tl_details_rec.source_lang,
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id);
SELECT 'N' FROM po_uda_ag_template_usages
WHERE template_id = p_template_id
AND attribute_category = p_attribute_category
AND attribute_group_id = p_attribute_group_id
AND attribute1||'.'|| attribute2||'.'||attribute3||'.'|| attribute4||'.'||attribute5||'.'|| attribute6||'.'||attribute7||'.'|| attribute8||
'.'||attribute9||'.'|| attribute10||'.'||attribute11||'.'|| attribute12||'.'||attribute13||'.'|| attribute14||'.'||attribute15||'.'|| attribute16||
'.'||attribute17||'.'|| attribute18||'.'||attribute19 ||'.'||attribute20 ||'.'= p_concatenated_segments;
SELECT 'N' FROM po_uda_ag_template_usages
WHERE template_id = p_template_id
AND attribute_category = p_attribute_category
AND attribute_group_sequence = p_sequence
AND attribute1||'.'|| attribute2||'.'||attribute3||'.'|| attribute4||'.'||attribute5||'.'|| attribute6||'.'||attribute7||'.'|| attribute8||
'.'||attribute9||'.'|| attribute10||'.'||attribute11||'.'|| attribute12||'.'||attribute13||'.'|| attribute14||'.'||attribute15||'.'|| attribute16||
'.'||attribute17||'.'|| attribute18||'.'||attribute19 ||'.'||attribute20 ||'.' = p_concatenated_segments;
SELECT attribute_category,context,Count(*) l_count
FROM (SELECT attribute_category,
RTrim(get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE1',attribute1) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE2',attribute2) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE3',attribute3) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE4',attribute4) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE5',attribute5) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE6',attribute6) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE7',attribute7) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE8',attribute8) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE9',attribute9) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE10',attribute10) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE11',attribute11) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE12',attribute12) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE13',attribute13) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE14',attribute14) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE15',attribute15) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE16',attribute16) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE17',attribute17) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE18',attribute18) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE19',attribute19) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE20',attribute20) ||'.','.') AS CONTEXT
FROM
PO_UDA_AG_TEMPLATE_USAGES
WHERE TEMPLATE_ID= p_template_id
AND attribute_category = p_attribute_category)
GROUP BY attribute_category,context;
SELECT attribute_category,attribute_group_sequence,context,Count(*) l_count
FROM (SELECT attribute_category, ATTRIBUTE_GROUP_SEQUENCE,
RTrim(get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE1',attribute1) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE2',attribute2) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE3',attribute3) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE4',attribute4) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE5',attribute5) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE6',attribute6) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE7',attribute7) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE8',attribute8) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE9',attribute9) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE10',attribute10) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE11',attribute11) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE12',attribute12) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE13',attribute13) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE14',attribute14) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE15',attribute15) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE16',attribute16) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE17',attribute17) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE18',attribute18) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE19',attribute19) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE20',attribute20) ||'.','.') AS CONTEXT
FROM
PO_UDA_AG_TEMPLATE_USAGES
WHERE TEMPLATE_ID= p_template_id )
GROUP BY attribute_category,attribute_group_sequence,context ;
SELECT attribute_category,attribute_group_id,context,Count(*) l_count
FROM (SELECT attribute_category, attribute_group_id,
RTrim(get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE1',attribute1) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE2',attribute2) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE3',attribute3) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE4',attribute4) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE5',attribute5) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE6',attribute6) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE7',attribute7) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE8',attribute8) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE9',attribute9) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE10',attribute10) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE11',attribute11) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE12',attribute12) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE13',attribute13) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE14',attribute14) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE15',attribute15) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE16',attribute16) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE17',attribute17) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE18',attribute18) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE19',attribute19) ||'.'||
get_flex_segment_meaning(1.0,usage_id,'ATTRIBUTE20',attribute20) ||'.','.') AS CONTEXT
FROM
PO_UDA_AG_TEMPLATE_USAGES
WHERE TEMPLATE_ID= p_template_id
AND attribute_category NOT IN (p_attribute_category1,p_attribute_category2,p_attribute_category3))
GROUP BY attribute_category,attribute_group_id,context ;
SELECT Count(*)
INTO l_count
FROM po_uda_ag_template_usages
WHERE ATTRIBUTE_CATEGORY = 'ADDRESS'
AND template_id = p_template_id;
SELECT descriptive_flex_context_name
INTO l_usage_display_name
FROM fnd_descr_flex_contexts_vl
WHERE descriptive_flexfield_name = 'PO_UDA_TEMPLATE_USAGES'
AND descriptive_flex_context_code = c_get_usage_ctx_seq_unique_rec.attribute_category ;
SELECT attr_group_disp_name
INTO l_attr_group_name
FROM ego_attr_groups_v
WHERE attr_group_id = c_get_usage_ctx_ag_unique_rec. attribute_group_id;
SELECT descriptive_flex_context_name
INTO l_usage_display_name
FROM fnd_descr_flex_contexts_vl
WHERE descriptive_flexfield_name = 'PO_UDA_TEMPLATE_USAGES'
AND descriptive_flex_context_code = c_get_usage_ctx_ag_unique_rec.attribute_category;
SELECT attr_group_id
INTO l_dummy_attr_grp_id
FROM ego_attr_groups_v
WHERE attr_group_type='PO_UDA_DUMMY_EXT_ATTRS'
AND attr_group_name = (SELECT attr_group_name FROM ego_attr_groups_v WHERE attr_group_id = p_attr_group_id);
SELECT EGO_ASSOCS_S.NEXTVAL
INTO l_dummy_associaton_id
FROM dual;
SELECT object_id
INTO l_dummy_object_id
FROM fnd_objects
WHERE obj_name = 'PO_UDA_DUMMY_ALL';
SELECT data_level_name
INTO l_dummy_data_level
FROM ego_data_level_b
WHERE attr_group_type='PO_UDA_DUMMY_EXT_ATTRS'
AND data_level_name= (SELECT attr_group_type FROM ego_data_level_b WHERE data_level_name= p_data_level);
SELECT ptl.display_name || ', Rev ' || pt.revision
INTO l_dummy_classfication_code
FROM po_uda_ag_templates_tl ptl,po_uda_ag_templates pt
WHERE ptl.template_id = pt.template_id
AND ptl.template_id = p_classification_code
AND language = USERENV('LANG');
PROCEDURE Delete_Association (
p_api_version IN NUMBER
,p_association_id IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,p_force IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
d_api_name CONSTANT VARCHAR2(30) := 'Delete_Association';
SELECT attr_group_id,classification_code
INTO l_real_attr_grp_id,l_template_id
FROM ego_obj_ag_assocs_b
WHERE association_id = p_association_id;
SELECT data_level
INTO l_real_data_level
FROM ego_obj_ag_assocs_b
WHERE classification_code = l_template_id
AND attr_group_id = l_real_attr_grp_id;
ego_ext_fwk_pub.Delete_Association(
p_api_version => 1.0, -- IN NUMBER
p_association_id => p_association_id, -- IN NUMBER
p_init_msg_list => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
p_commit => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
p_force => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
x_return_status => x_return_status, -- OUT NOCOPY VARCHAR2
x_errorcode => x_errorcode, -- OUT NOCOPY NUMBER
x_msg_count => x_msg_count, -- OUT NOCOPY NUMBER
x_msg_data => x_msg_data); -- OUT NOCOPY VARCHAR2
SELECT attr_group_id
INTO l_dummy_attr_grp_id
FROM ego_attr_groups_v
WHERE attr_group_type='PO_UDA_DUMMY_EXT_ATTRS'
AND attr_group_name = (SELECT attr_group_name FROM ego_attr_groups_v WHERE attr_group_id = l_real_attr_grp_id);
SELECT ptl.display_name || ', Rev ' || pt.revision
INTO l_template_name
FROM po_uda_ag_templates_tl ptl,po_uda_ag_templates pt
WHERE ptl.template_id = pt.template_id
AND ptl.template_id = l_template_id
AND language = USERENV('LANG');
SELECT data_level_name
INTO l_dummy_data_level
FROM ego_data_level_b
WHERE attr_group_type='PO_UDA_DUMMY_EXT_ATTRS'
AND data_level_name= (SELECT attr_group_type FROM ego_data_level_b WHERE data_level_name= l_real_data_level);
SELECT association_id
INTO l_dummy_associaton_id
FROM ego_obj_ag_assocs_b
WHERE attr_group_id = l_dummy_attr_grp_id AND classification_code = l_template_name AND data_level= l_dummy_data_level;
ego_ext_fwk_pub.Delete_Association(
p_api_version => 1.0, -- IN NUMBER
p_association_id => l_dummy_associaton_id, -- IN NUMBER
p_init_msg_list => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
p_commit => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
p_force => 'F', -- IN VARCHAR2 := fnd_api.g_FALSE
x_return_status => x_return_status, -- OUT NOCOPY VARCHAR2
x_errorcode => x_errorcode, -- OUT NOCOPY NUMBER
x_msg_count => x_msg_count, -- OUT NOCOPY NUMBER
x_msg_data => x_msg_data); -- OUT NOCOPY VARCHAR2
END Delete_Association;