[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_amend_code_deleted CONSTANT VARCHAR2 (30) := 'DELETED';
g_amend_code_updated CONSTANT VARCHAR2 (30) := 'UPDATED';
SELECT application_id
INTO p_application_id
FROM fnd_application
WHERE application_short_name = 'OKC';
PROCEDURE update_k_art_var (
p_cat_id IN NUMBER,
p_variable_code IN VARCHAR2,
p_blobdata IN BLOB,
p_type IN VARCHAR2
)
IS
l_clob CLOB;
UPDATE okc_k_art_variables
SET mr_variable_xml = l_clob
WHERE variable_code = p_variable_code AND cat_id = p_cat_id;
UPDATE okc_k_art_variables
SET mr_variable_html = l_clob
WHERE variable_code = p_variable_code AND cat_id = p_cat_id;
END update_k_art_var;
SELECT mr_variable_html
INTO l_clob
FROM okc_k_art_variables
WHERE variable_code = p_variable_code AND cat_id = p_cat_id;
SELECT mr_variable_xml
INTO l_clob
FROM okc_k_art_variables
WHERE variable_code = p_variable_code AND cat_id = p_cat_id;
SELECT AG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP,
AG.ATTR_GROUP_ID ATTR_GROUP_ID,
AG.DESCRIPTIVE_FLEXFIELD_NAME DESC_FLEXFLD_NAME,
AG.MULTI_ROW MULTI_ROW
FROM EGO_FND_DSC_FLX_CTX_EXT AG
WHERE 1=1
AND AG.DESCRIPTIVE_FLEXFIELD_NAME = 'OKC_K_ART_VAR_EXT_ATTRS'
AND AG.ATTR_GROUP_ID = p_Attr_group_id
;
SELECT EFDFCE.ATTR_ID,
EFDFCE.APPLICATION_COLUMN_NAME,
FCU.END_USER_COLUMN_NAME,
fcu.flex_value_set_id,
EFDFCE.data_type
FROM
EGO_FND_DF_COL_USGS_EXT EFDFCE,
FND_DESCR_FLEX_COLUMN_USAGES FCU
WHERE EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEXFLD_NAME
AND EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE = P_ATTR_GROUP
AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE = EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND FCU.DESCRIPTIVE_FLEXFIELD_NAME = EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME
AND FCU.APPLICATION_COLUMN_NAME = EFDFCE.APPLICATION_COLUMN_NAME
AND FCU.DISPLAY_FLAG <> 'H';
L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
|| '", XMLAgg(XMLForest(' ;
L_SQL := '(select XMLElement ( "VAR_VALUE" , XMLAgg( XMLElement("' || REC.ATTR_GROUP || '", XMLForest(';
L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
|| '", XMLForest(' ;
L_SQL := '(select XMLElement ( "VAR_VALUE" , XMLElement("' || REC.ATTR_GROUP || '", XMLForest(';
l_op := 'select XMLConcat(' || l_sql || ') from dual';
PROCEDURE update_uda_attr_xml (
p_init_msg_list IN VARCHAR2 ,
p_cat_id IN NUMBER,
p_variable_code IN VARCHAR2,
p_attr_group_id IN NUMBER,
p_mode IN VARCHAR2 DEFAULT 'NORMAL',
p_locking_enabled IN VARCHAR2 DEFAULT 'N',
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name varchar2(240) := 'update_uda_attr_xml';
SAVEPOINT g_update_uda_attr_xml_GRP;
OKC_K_ARTICLES_GRP.update_article(
p_api_version =>1,
p_init_msg_list => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_validate_commit => FND_API.G_FALSE,
p_validation_string => NULL,
p_commit => FND_API.G_FALSE,
p_mode => p_mode,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_id => p_cat_id,
p_amendment_description => NULL,
p_print_text_yn =>NULL,
p_object_version_number => NULL,
p_lock_terms_yn => p_locking_enabled
);
UPDATE okc_k_art_variables
SET mr_variable_xml =
get_uda_attr_xml (p_cat_id, p_variable_code, p_attr_group_id)
WHERE cat_id = p_cat_id AND variable_code = p_variable_code;
fnd_log.STRING (g_stmt_level,g_pkg_name,'300: Leaving update_uda_attr_xml: OKC_API.G_EXCEPTION_ERROR Exception');
ROLLBACK TO g_update_uda_attr_xml_GRP;
fnd_log.STRING (g_stmt_level,g_pkg_name,'400: Leaving update_uda_attr_xml: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
ROLLBACK TO g_update_uda_attr_xml_GRP;
fnd_log.STRING (g_stmt_level,g_pkg_name,'500: Leaving update_uda_attr_xml: '||sqlerrm);
ROLLBACK TO g_update_uda_attr_xml_GRP;
END update_uda_attr_xml;
SELECT 'Y'
INTO dochasmrv
FROM okc_k_articles_b kart
WHERE document_type = doctype
AND document_id = docid
AND EXISTS (
SELECT 'Y'
FROM okc_k_art_variables kvar, okc_bus_variables_b var
WHERE 1 = 1
AND kvar.cat_id = kart.ID
AND kvar.variable_code = var.variable_code
AND var.mrv_flag = 'Y')
AND ROWNUM = 1;
SELECT '' mrVar,
xmltype(''||Dbms_Lob.SubStr(MR_VARIABLE_HTML,dbms_lob.getLength(MR_VARIABLE_HTML),Dbms_Lob.InStr(MR_VARIABLE_HTML,''))).extract('//style/text()').getClobVal() mrStyle,
xmltype(''||Dbms_Lob.SubStr(MR_VARIABLE_HTML,dbms_lob.getLength(MR_VARIABLE_HTML),Dbms_Lob.InStr(MR_VARIABLE_HTML,''))).extract('//body/*|text()').getClobVal() mrBody
FROM okc_mrv_t t,
xmltable('//SectionsArticlesToPrintVORow' PASSING xmltype(''||regexp_replace(OKC_WORD_DOWNLOAD_UPLOAD.blob_to_clob(blob_data),' ',' ')||' ')
SELECT OKC_WORD_DOWNLOAD_UPLOAD.blob_to_clob(blob_data) INTO l_doc_xml
FROM okc_mrv_t;
SELECT 'class="'||Trim(cl."className")||'"' srcStr,
'style="'||Trim(cl."styleAttr")||'"' trgStr
FROM xmltable('//row1' PASSING xmltype(l_mrStyle)
COLUMNS "className" VARCHAR2(10) PATH '//row1/class1/text()',
"styleAttr" VARCHAR2(2000) PATH '//row1/styleAttr/text()') cl;
UPDATE okc_mrv_t
SET blob_output = OKC_WORD_DOWNLOAD_UPLOAD.clob_to_blob(l_doc_xml)
WHERE doc_id= DocID
AND doc_type =DocType;
SELECT attr.attr_group_disp_name
INTO l_agdispname
FROM ego_attr_groups_v attr, fnd_application fa
WHERE attr.attr_group_type = 'OKC_K_ART_VAR_EXT_ATTRS'
AND fa.application_short_name = 'OKC'
AND attr.application_id = fa.application_id
AND attr.attr_group_id = TO_NUMBER (attrgroupid);
SELECT template_name
INTO l_tmplname
FROM xdo_templates_vl xtv
WHERE xtv.template_code = mrv_tmpl_code AND xtv.ds_app_short_name(+) =
'OKC';
SELECT object_id
INTO l_object_id
FROM fnd_objects
WHERE obj_name = 'OKC_K_ART_VARIABLES';
SELECT data_level_id
INTO l_main_data_level_id
FROM ego_data_level_b
WHERE attr_group_type = 'OKC_K_ART_VAR_EXT_ATTRS'
-- AND DATA_LEVEL_NAME LIKE '%CLAUSE%'
;
PROCEDURE update_uda_attr_xml (
p_cat_id IN NUMBER,
p_variable_code IN VARCHAR2,
p_attr_group_id IN NUMBER
)
IS
BEGIN
UPDATE okc_k_art_variables
SET mr_variable_xml =
get_uda_attr_xml (p_cat_id, p_variable_code, p_attr_group_id)
WHERE cat_id = p_cat_id AND variable_code = p_variable_code;
END update_uda_attr_xml;