CREATE OR REPLACE PACKAGE BODY APEX_ENHANCED_LOV_ITEM
AS
G_APP_ID NUMBER DEFAULT V ('APP_ID');
G_PAGE_ID NUMBER DEFAULT V ('APP_PAGE_ID');
G_DEBUG BOOLEAN
DEFAULT CASE
WHEN V ('DEBUG') IN ('YES', 'LEVEL6', 'LEVEL9') THEN TRUE
ELSE FALSE
END;
G_LOGPREFIX CONSTANT VARCHAR2 (100) := '# Pretius Enhanced LOV Item';
G_AJAX_MODE VARCHAR2 (4000); --x01
G_AJAX_ROWS_PER_PAGE NUMBER; --x02
G_AJAX_SEARCH_STRING VARCHAR2 (4000); --x03
G_AJAX_FETCH_PAGE NUMBER; --x04
G_AJAX_REPOT_SORT_COL_IDX NUMBER; --x05
G_AJAX_REPOT_SORT_COL_DIR VARCHAR2 (4); --x06
G_AJAX_SEARCH_COLUMN_IDX NUMBER; --x07
G_ITEM APEX_PLUGIN.T_PAGE_ITEM;
G_PLUGIN APEX_PLUGIN.T_PLUGIN;
--
-- function prepareSqlQuery
--
FUNCTION PREPARESQLQUERY
RETURN VARCHAR2
IS
V_QUERY VARCHAR2 (32767) := G_ITEM.LOV_DEFINITION;
BEGIN
/*
queries generated by APEX for static inline lov and static shared lov
*/
--select /*+ cardinality(t 5) no_result_cache */ disp, val from table(wwv_flow_utilities.get_temp_lov_data(1)) t order by disp
--select /*+ cardinality(t 10) no_result_cache */ disp, val from table(wwv_flow_utilities.get_temp_lov_data(1)) t order by insert_order, disp
IF INSTR (V_QUERY, '*/ disp, val from') > 0
THEN
V_QUERY := '
/*1*/
select disp d, val r from (
' || V_QUERY || '
) /*2*/
';
END IF;
RETURN 'select * from (' || V_QUERY || ') where r is not null';
END PREPARESQLQUERY;
PROCEDURE META_DATA (
P_ITEM IN APEX_PLUGIN.T_ITEM,
P_PLUGIN IN APEX_PLUGIN.T_PLUGIN,
P_PARAM IN APEX_PLUGIN.T_ITEM_META_DATA_PARAM,
P_RESULT IN OUT NOCOPY APEX_PLUGIN.T_ITEM_META_DATA_RESULT)
IS
L_QUERY VARCHAR2 (32767);
BEGIN
G_ITEM := P_ITEM;
P_RESULT.DISPLAY_LOV_DEFINITION :=
'select d, r from (' || PREPARESQLQUERY () || ')';
P_RESULT.IS_MULTI_VALUE :=
INSTR (':' || P_ITEM.ATTRIBUTE_05 || ':', ':MS:') > 0;
END META_DATA;
--
-- getBindedRefCursor
--
FUNCTION GETBINDEDREFCURSOR (PI_SQL IN VARCHAR2)
RETURN SYS_REFCURSOR
AS
V_APEX_ITEMS_NAMES DBMS_SQL.VARCHAR2_TABLE;
V_CURSOR PLS_INTEGER;
V_STATUS NUMBER;
V_VALUE VARCHAR2 (4000);
BEGIN
V_APEX_ITEMS_NAMES := WWV_FLOW_UTILITIES.GET_BINDS (PI_SQL);
-- open v_cursor;
V_CURSOR := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE (V_CURSOR, PI_SQL, DBMS_SQL.NATIVE);
APEX_DEBUG.INFO (G_LOGPREFIX || ' -> Bind Variables Start ... ');
-- bind items
FOR I IN 1 .. V_APEX_ITEMS_NAMES.COUNT
LOOP
IF V_APEX_ITEMS_NAMES (I) = ':SEARCH_STRING'
THEN
APEX_DEBUG.INFO (
G_LOGPREFIX
|| ' -> Bind Variable "'
|| V_APEX_ITEMS_NAMES (I)
|| '" with Value "'
|| G_AJAX_SEARCH_STRING
|| '" ');
DBMS_SQL.BIND_VARIABLE (V_CURSOR,
V_APEX_ITEMS_NAMES (I),
G_AJAX_SEARCH_STRING);
ELSE
V_VALUE := V (TRIM (BOTH ':' FROM V_APEX_ITEMS_NAMES (I)));
/*
if v_value is null then
apex_debug.info(g_logprefix||' -> NULL');
else
apex_debug.info(g_logprefix||' -> length = '||LENGTH(v_value));
apex_debug.info(g_logprefix||' -> ascii = '||ASCII(v_value));
end if;
*/
APEX_DEBUG.INFO (
G_LOGPREFIX
|| ' -> Bind Variable "'
|| V_APEX_ITEMS_NAMES (I)
|| '" with Value "'
|| V_VALUE
|| '" ');
DBMS_SQL.BIND_VARIABLE (V_CURSOR,
V_APEX_ITEMS_NAMES (I),
V_VALUE);
END IF;
END LOOP;
V_STATUS := DBMS_SQL.EXECUTE (V_CURSOR);
APEX_DEBUG.INFO (G_LOGPREFIX || ' -> Bind Variables End. ');
RETURN DBMS_SQL.TO_REFCURSOR (V_CURSOR);
END GETBINDEDREFCURSOR;
--
-- t_item_render_param_to_json
--
FUNCTION T_ITEM_RENDER_PARAM_TO_JSON (
P_PARAM IN APEX_PLUGIN.T_ITEM_RENDER_PARAM)
RETURN CLOB
IS
V_CLOB CLOB;
BEGIN
APEX_JSON.INITIALIZE_CLOB_OUTPUT;
APEX_JSON.OPEN_OBJECT;
APEX_JSON.WRITE ('value_set_by_controller',
P_PARAM.VALUE_SET_BY_CONTROLLER);
APEX_JSON.WRITE ('value', P_PARAM.VALUE);
APEX_JSON.WRITE ('is_readonly', P_PARAM.IS_READONLY);
APEX_JSON.WRITE ('is_printer_friendly', P_PARAM.IS_PRINTER_FRIENDLY);
APEX_JSON.CLOSE_OBJECT;
V_CLOB := APEX_JSON.GET_CLOB_OUTPUT;
APEX_JSON.FREE_OUTPUT;
RETURN V_CLOB;
END T_ITEM_RENDER_PARAM_TO_JSON;
--
-- t_plugin_to_json
--
FUNCTION T_PLUGIN_TO_JSON (P_PLUGIN IN APEX_PLUGIN.T_PLUGIN)
RETURN CLOB
IS
V_CLOB CLOB;
BEGIN
APEX_JSON.INITIALIZE_CLOB_OUTPUT;
APEX_JSON.OPEN_OBJECT;
APEX_JSON.WRITE ('name', P_PLUGIN.NAME);
APEX_JSON.WRITE ('file_prefix', P_PLUGIN.FILE_PREFIX);
APEX_JSON.WRITE ('ajaxIdentifier', APEX_PLUGIN.GET_AJAX_IDENTIFIER);
APEX_JSON.WRITE ('attribute_01', P_PLUGIN.ATTRIBUTE_01);
APEX_JSON.WRITE ('attribute_02', P_PLUGIN.ATTRIBUTE_02);
APEX_JSON.WRITE ('attribute_03', P_PLUGIN.ATTRIBUTE_03);
APEX_JSON.WRITE ('attribute_04', P_PLUGIN.ATTRIBUTE_04);
APEX_JSON.WRITE ('attribute_05', P_PLUGIN.ATTRIBUTE_05);
APEX_JSON.WRITE ('attribute_06', P_PLUGIN.ATTRIBUTE_06);
APEX_JSON.WRITE ('attribute_07', P_PLUGIN.ATTRIBUTE_07);
APEX_JSON.WRITE ('attribute_08', P_PLUGIN.ATTRIBUTE_08);
APEX_JSON.WRITE ('attribute_09', P_PLUGIN.ATTRIBUTE_09);
APEX_JSON.WRITE ('attribute_10', P_PLUGIN.ATTRIBUTE_10);
APEX_JSON.WRITE ('attribute_11', P_PLUGIN.ATTRIBUTE_11);
APEX_JSON.WRITE ('attribute_12', P_PLUGIN.ATTRIBUTE_12);
APEX_JSON.WRITE ('attribute_13', P_PLUGIN.ATTRIBUTE_13);
APEX_JSON.WRITE ('attribute_14', P_PLUGIN.ATTRIBUTE_14);
APEX_JSON.WRITE ('attribute_15', P_PLUGIN.ATTRIBUTE_15);
APEX_JSON.CLOSE_OBJECT;
V_CLOB := APEX_JSON.GET_CLOB_OUTPUT;
APEX_JSON.FREE_OUTPUT;
RETURN V_CLOB;
END T_PLUGIN_TO_JSON;
--
-- t_page_item_to_json
--
FUNCTION T_PAGE_ITEM_TO_JSON (P_ITEM IN APEX_PLUGIN.T_ITEM)
RETURN CLOB
IS
V_LOV_CASCADE_PARENT_ITEMS P_ITEM.LOV_CASCADE_PARENT_ITEMS%TYPE;
V_AJAX_ITEMS_TO_SUBMIT P_ITEM.AJAX_ITEMS_TO_SUBMIT%TYPE;
V_CLOB CLOB;
BEGIN
V_LOV_CASCADE_PARENT_ITEMS :=
APEX_PLUGIN_UTIL.ITEM_NAMES_TO_JQUERY (
P_ITEM_NAMES => P_ITEM.LOV_CASCADE_PARENT_ITEMS,
P_ITEM => P_ITEM);
V_AJAX_ITEMS_TO_SUBMIT :=
APEX_PLUGIN_UTIL.ITEM_NAMES_TO_JQUERY (
P_ITEM_NAMES => P_ITEM.AJAX_ITEMS_TO_SUBMIT,
P_ITEM => P_ITEM);
APEX_JSON.INITIALIZE_CLOB_OUTPUT;
APEX_JSON.OPEN_OBJECT;
APEX_JSON.WRITE ('id', P_ITEM.ID, TRUE);
APEX_JSON.WRITE ('name', P_ITEM.NAME, TRUE);
APEX_JSON.WRITE ('label', P_ITEM.LABEL, TRUE);
APEX_JSON.WRITE ('plain_label', P_ITEM.PLAIN_LABEL, TRUE);
APEX_JSON.WRITE ('label_id', P_ITEM.LABEL_ID, TRUE);
APEX_JSON.WRITE ('placeholder', P_ITEM.PLACEHOLDER, TRUE);
APEX_JSON.WRITE ('format_mask', P_ITEM.FORMAT_MASK, TRUE);
APEX_JSON.WRITE ('is_required', P_ITEM.IS_REQUIRED, TRUE);
APEX_JSON.WRITE ('lov_definition', P_ITEM.LOV_DEFINITION, TRUE);
APEX_JSON.WRITE ('lov_display_extra', P_ITEM.LOV_DISPLAY_EXTRA, TRUE);
APEX_JSON.WRITE ('lov_display_null', P_ITEM.LOV_DISPLAY_NULL, TRUE);
APEX_JSON.WRITE ('lov_null_text', P_ITEM.LOV_NULL_TEXT, TRUE);
APEX_JSON.WRITE ('lov_null_value', P_ITEM.LOV_NULL_VALUE, TRUE);
APEX_JSON.WRITE ('lov_cascade_parent_items',
V_LOV_CASCADE_PARENT_ITEMS,
TRUE);
APEX_JSON.WRITE ('ajax_items_to_submit',
V_AJAX_ITEMS_TO_SUBMIT,
TRUE);
APEX_JSON.WRITE ('ajax_optimize_refresh',
P_ITEM.AJAX_OPTIMIZE_REFRESH,
TRUE);
APEX_JSON.WRITE ('element_width', P_ITEM.ELEMENT_WIDTH, TRUE);
APEX_JSON.WRITE ('element_max_length',
P_ITEM.ELEMENT_MAX_LENGTH,
TRUE);
APEX_JSON.WRITE ('element_height', P_ITEM.ELEMENT_HEIGHT, TRUE);
APEX_JSON.WRITE ('element_css_classes',
P_ITEM.ELEMENT_CSS_CLASSES,
TRUE);
APEX_JSON.WRITE ('element_attributes',
P_ITEM.ELEMENT_ATTRIBUTES,
TRUE);
APEX_JSON.WRITE ('element_option_attributes',
P_ITEM.ELEMENT_OPTION_ATTRIBUTES,
TRUE);
APEX_JSON.WRITE ('escape_output', P_ITEM.ESCAPE_OUTPUT, TRUE);
APEX_JSON.WRITE ('attribute_01', P_ITEM.ATTRIBUTE_01, TRUE);
APEX_JSON.WRITE ('attribute_02', P_ITEM.ATTRIBUTE_02, TRUE);
APEX_JSON.WRITE ('attribute_03', P_ITEM.ATTRIBUTE_03, TRUE);
APEX_JSON.WRITE ('attribute_04', P_ITEM.ATTRIBUTE_04, TRUE);
APEX_JSON.WRITE ('attribute_05', P_ITEM.ATTRIBUTE_05, TRUE);
APEX_JSON.WRITE ('attribute_06', P_ITEM.ATTRIBUTE_06, TRUE);
APEX_JSON.WRITE ('attribute_07', P_ITEM.ATTRIBUTE_07, TRUE);
APEX_JSON.WRITE ('attribute_08', P_ITEM.ATTRIBUTE_08, TRUE);
APEX_JSON.WRITE ('attribute_09', P_ITEM.ATTRIBUTE_09, TRUE);
APEX_JSON.WRITE ('attribute_10', P_ITEM.ATTRIBUTE_10, TRUE);
APEX_JSON.WRITE ('attribute_11', P_ITEM.ATTRIBUTE_11, TRUE);
APEX_JSON.WRITE ('attribute_12', P_ITEM.ATTRIBUTE_12, TRUE);
APEX_JSON.WRITE ('attribute_13', P_ITEM.ATTRIBUTE_13, TRUE);
APEX_JSON.WRITE ('attribute_14', P_ITEM.ATTRIBUTE_14, TRUE);
APEX_JSON.WRITE ('attribute_15', P_ITEM.ATTRIBUTE_15, TRUE);
APEX_JSON.CLOSE_OBJECT;
V_CLOB := APEX_JSON.GET_CLOB_OUTPUT;
APEX_JSON.FREE_OUTPUT;
RETURN V_CLOB;
END T_PAGE_ITEM_TO_JSON;
--
-- function f_queryGetColumnType
--
FUNCTION F_QUERYGETCOLUMNTYPE (P_COL_TYPE IN NUMBER)
RETURN VARCHAR2
IS
L_COL_TYPE VARCHAR2 (50);
BEGIN
IF P_COL_TYPE = 1
THEN
L_COL_TYPE := 'VARCHAR2';
ELSIF P_COL_TYPE = 2
THEN
L_COL_TYPE := 'NUMBER';
ELSIF P_COL_TYPE = 12
THEN
L_COL_TYPE := 'DATE';
ELSIF P_COL_TYPE IN (180, 181, 231)
THEN
L_COL_TYPE := 'TIMESTAMP';
IF P_COL_TYPE = 231
THEN
L_COL_TYPE := 'TIMESTAMP_LTZ';
END IF;
ELSIF P_COL_TYPE = 112
THEN
L_COL_TYPE := 'CLOB';
ELSIF P_COL_TYPE = 113
THEN
L_COL_TYPE := 'BLOB';
ELSIF P_COL_TYPE = 96
THEN
L_COL_TYPE := 'CHAR';
ELSE
L_COL_TYPE := 'OTHER';
END IF;
RETURN L_COL_TYPE;
END F_QUERYGETCOLUMNTYPE;
--
-- procedure p_queryDescribeColumns
--
PROCEDURE P_QUERYDESCRIBECOLUMNS (
PI_SQL IN VARCHAR2,
PO_COLUMNS_NO OUT NUMBER,
PO_COLUMNS_INFO_ARR OUT SYS.DBMS_SQL.DESC_TAB2)
IS
V_APEX_ITEMS_NAMES DBMS_SQL.VARCHAR2_TABLE
:= WWV_FLOW_UTILITIES.GET_BINDS (PI_SQL);
V_CURSOR PLS_INTEGER;
V_DESC_COL_NO NUMBER := 0;
V_DESC_COL_INFO SYS.DBMS_SQL.DESC_TAB2;
V_STATUS NUMBER;
BEGIN
V_CURSOR := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE (V_CURSOR, PI_SQL, DBMS_SQL.NATIVE);
--bind items
FOR I IN 1 .. V_APEX_ITEMS_NAMES.COUNT
LOOP
DBMS_SQL.BIND_VARIABLE (
V_CURSOR,
V_APEX_ITEMS_NAMES (I),
V (TRIM (BOTH ':' FROM V_APEX_ITEMS_NAMES (I))));
END LOOP;
SYS.DBMS_SQL.DESCRIBE_COLUMNS2 (V_CURSOR,
V_DESC_COL_NO,
V_DESC_COL_INFO);
V_STATUS := DBMS_SQL.EXECUTE (V_CURSOR);
PO_COLUMNS_NO := V_DESC_COL_NO;
PO_COLUMNS_INFO_ARR := V_DESC_COL_INFO;
END P_QUERYDESCRIBECOLUMNS;
--
-- function f_queryPrepareConditions
--
FUNCTION F_QUERYPREPARECONDITIONS (PI_SQL IN VARCHAR2)
RETURN VARCHAR2
IS
V_DESC_COL_NO NUMBER := 0;
V_DESC_COL_INFO SYS.DBMS_SQL.DESC_TAB2;
V_RETURN VARCHAR2 (32767);
BEGIN
P_QUERYDESCRIBECOLUMNS (PI_SQL => PI_SQL,
PO_COLUMNS_NO => V_DESC_COL_NO,
PO_COLUMNS_INFO_ARR => V_DESC_COL_INFO);
IF G_AJAX_SEARCH_COLUMN_IDX IS NOT NULL
THEN
--search by specific column
V_RETURN :=
'where upper('
|| V_DESC_COL_INFO (G_AJAX_SEARCH_COLUMN_IDX).COL_NAME
|| ') like upper(''%''||:SEARCH_STRING||''%'')';
ELSE
--search by every column from query
V_RETURN := 'where ';
FOR I IN 1 .. V_DESC_COL_NO
LOOP
V_RETURN :=
V_RETURN
|| ' upper('
|| V_DESC_COL_INFO (I).COL_NAME
|| ') like upper(''%''||:SEARCH_STRING||''%'') or';
END LOOP;
V_RETURN := RTRIM (V_RETURN, ' or');
END IF;
RETURN V_RETURN;
END F_QUERYPREPARECONDITIONS;
--
-- f_queryGetColumnsJson
--
FUNCTION F_QUERYGETCOLUMNSJSON
RETURN CLOB
IS
V_RETURN CLOB;
V_DESC_COL_NO NUMBER := 0;
V_DESC_COL_INFO SYS.DBMS_SQL.DESC_TAB2;
BEGIN
P_QUERYDESCRIBECOLUMNS (PI_SQL => PREPARESQLQUERY,
PO_COLUMNS_NO => V_DESC_COL_NO,
PO_COLUMNS_INFO_ARR => V_DESC_COL_INFO);
APEX_JSON.INITIALIZE_CLOB_OUTPUT;
APEX_JSON.OPEN_ARRAY;
FOR I IN 1 .. V_DESC_COL_NO
LOOP
APEX_JSON.OPEN_OBJECT;
APEX_JSON.WRITE ('COLUMN_NAME', V_DESC_COL_INFO (I).COL_NAME);
APEX_JSON.WRITE (
'COLUMN_TYPE',
F_QUERYGETCOLUMNTYPE (V_DESC_COL_INFO (I).COL_TYPE));
APEX_JSON.WRITE ('SHEMA_NAME',
V_DESC_COL_INFO (I).COL_SCHEMA_NAME,
TRUE);
APEX_JSON.WRITE ('IDX', I);
APEX_JSON.CLOSE_OBJECT;
END LOOP;
APEX_JSON.CLOSE_ARRAY;
V_RETURN := APEX_JSON.GET_CLOB_OUTPUT;
APEX_JSON.FREE_OUTPUT;
RETURN V_RETURN;
END F_QUERYGETCOLUMNSJSON;
--
-- f_getDisplayValues
--
FUNCTION F_GETDISPLAYVALUES (PI_VALUE IN VARCHAR2)
RETURN VARCHAR2
IS
V_CURSOR SYS_REFCURSOR;
V_RESULT VARCHAR2 (32767);
V_QUERY VARCHAR2 (32767);
BEGIN
V_QUERY :=
'
select
listagg(d, '', '') within group( order by d asc )
from (
'
|| PREPARESQLQUERY
|| '
)
where
r in ('
|| ''''
|| REPLACE (PI_VALUE, ':', ''',''')
|| ''''
|| ')
';
V_CURSOR := GETBINDEDREFCURSOR (V_QUERY);
FETCH V_CURSOR INTO V_RESULT;
CLOSE V_CURSOR;
RETURN V_RESULT;
END F_GETDISPLAYVALUES;
--
-- f_autocompleteGetDefaulsSearch
--
FUNCTION F_AUTOCOMPLETEGETDEFAULSSEARCH
RETURN VARCHAR2
IS
V_ATTR_AUTOCOMPLETE_D_SEARCH APEX_APPLICATION_PAGE_ITEMS.ATTRIBUTE_04%TYPE
:= G_ITEM.ATTRIBUTE_04;
V_CONDITIONS VARCHAR2 (32767);
BEGIN
IF V_ATTR_AUTOCOMPLETE_D_SEARCH = 'D%'
THEN
V_CONDITIONS := V_CONDITIONS || '
and upper(d) like upper(:SEARCH_STRING||''%'')
';
END IF;
IF V_ATTR_AUTOCOMPLETE_D_SEARCH = '%D'
THEN
V_CONDITIONS := V_CONDITIONS || '
and upper(d) like upper(''%''||:SEARCH_STRING)
';
END IF;
IF V_ATTR_AUTOCOMPLETE_D_SEARCH = '%D%'
THEN
V_CONDITIONS := V_CONDITIONS || '
and upper(d) like upper(''%''||:SEARCH_STRING||''%'')
';
END IF;
IF V_ATTR_AUTOCOMPLETE_D_SEARCH = 'D'
THEN
V_CONDITIONS := V_CONDITIONS || '
and upper(d) = upper(:SEARCH_STRING)
';
END IF;
IF V_ATTR_AUTOCOMPLETE_D_SEARCH = 'R%'
THEN
V_CONDITIONS := V_CONDITIONS || '
and upper(r) like upper(:SEARCH_STRING||''%'')
';
END IF;
IF V_ATTR_AUTOCOMPLETE_D_SEARCH = '%R'
THEN
V_CONDITIONS := V_CONDITIONS || '
and upper(r) like upper(''%''||:SEARCH_STRING)
';
END IF;
IF V_ATTR_AUTOCOMPLETE_D_SEARCH = '%R%'
THEN
V_CONDITIONS := V_CONDITIONS || '
and upper(r) like upper(''%''||:SEARCH_STRING||''%'')
';
END IF;
IF V_ATTR_AUTOCOMPLETE_D_SEARCH = 'R'
THEN
V_CONDITIONS := V_CONDITIONS || '
and upper(R) = upper(:SEARCH_STRING)
';
END IF;
IF V_ATTR_AUTOCOMPLETE_D_SEARCH = 'DR%'
THEN
V_CONDITIONS := V_CONDITIONS || '
and (
upper(r) like upper(:SEARCH_STRING||''%'')
or upper(d) like upper(:SEARCH_STRING||''%'')
)
';
END IF;
IF V_ATTR_AUTOCOMPLETE_D_SEARCH = '%DR'
THEN
V_CONDITIONS := V_CONDITIONS || '
and (
upper(r) like upper(''%''||:SEARCH_STRING)
or upper(d) like upper(''%''||:SEARCH_STRING)
)
';
END IF;
IF V_ATTR_AUTOCOMPLETE_D_SEARCH = '%DR%'
THEN
V_CONDITIONS := V_CONDITIONS || '
and (
upper(r) like upper(''%''||:SEARCH_STRING||''%'')
or upper(d) like upper(''%''||:SEARCH_STRING||''%'')
)
';
END IF;
IF V_ATTR_AUTOCOMPLETE_D_SEARCH = 'DR'
THEN
V_CONDITIONS := V_CONDITIONS || '
and (
upper(r) = upper(:SEARCH_STRING)
or upper(d) = upper(:SEARCH_STRING)
)
';
END IF;
RETURN V_CONDITIONS;
END F_AUTOCOMPLETEGETDEFAULSSEARCH;
--
-- function f_getRownumLimiterStart
--
FUNCTION F_GETROWNUMLIMITERSTART (P_PAGE IN NUMBER,
P_ROWS_PER_PAGE IN NUMBER)
RETURN NUMBER
IS
V_START_ROWNUM_WITH NUMBER;
V_START_ROWNUM NUMBER;
BEGIN
V_START_ROWNUM_WITH := (P_PAGE - 1) * P_ROWS_PER_PAGE + 1;
IF V_START_ROWNUM_WITH = 0
THEN
V_START_ROWNUM := 1;
ELSE
V_START_ROWNUM := V_START_ROWNUM_WITH;
END IF;
RETURN V_START_ROWNUM;
END F_GETROWNUMLIMITERSTART;
--
-- f_popupGetCurrentPageDataCount
--
FUNCTION F_POPUPGETCURRENTPAGEDATACOUNT (PI_WHERE IN VARCHAR2)
RETURN NUMBER
IS
V_CURSOR SYS_REFCURSOR;
V_RESULT NUMBER;
V_QUERY VARCHAR2 (4000);
BEGIN
APEX_DEBUG.INFO (
G_LOGPREFIX || ' -> Popup Report "Current Page Data Count" ... ');
V_QUERY :=
'select count(1) from ( ' || PREPARESQLQUERY || ' )' || PI_WHERE;
APEX_DEBUG.LOG_LONG_MESSAGE (
P_MESSAGE => V_QUERY,
P_ENABLED => FALSE,
P_LEVEL => APEX_DEBUG.C_LOG_LEVEL_INFO);
V_CURSOR := GETBINDEDREFCURSOR (V_QUERY);
FETCH V_CURSOR INTO V_RESULT;
CLOSE V_CURSOR;
RETURN V_RESULT;
END F_POPUPGETCURRENTPAGEDATACOUNT;
--
-- p_ajax_getReturnValues
--
PROCEDURE P_AJAX_GETRETURNVALUES
IS
V_QUERY VARCHAR2 (32767) := G_ITEM.LOV_DEFINITION;
V_WHERE VARCHAR2 (32767);
V_REF_CURSOR SYS_REFCURSOR;
BEGIN
IF G_AJAX_SEARCH_STRING IS NOT NULL
THEN
V_WHERE := F_QUERYPREPARECONDITIONS (V_QUERY);
END IF;
V_QUERY := '
select
r
from (
' || V_QUERY || '
)
' || V_WHERE || '
';
APEX_DEBUG.INFO (G_LOGPREFIX || ' -> SQL Query "Return Values" ... ');
APEX_DEBUG.LOG_LONG_MESSAGE (
P_MESSAGE => V_QUERY,
P_ENABLED => FALSE,
P_LEVEL => APEX_DEBUG.C_LOG_LEVEL_INFO);
V_REF_CURSOR := GETBINDEDREFCURSOR (V_QUERY);
APEX_JSON.OPEN_OBJECT;
IF G_DEBUG
THEN
APEX_JSON.WRITE ('query', V_QUERY, TRUE);
END IF;
APEX_JSON.WRITE ('searchString', G_AJAX_SEARCH_STRING, TRUE);
APEX_JSON.WRITE ('searchColumnIdx', G_AJAX_SEARCH_COLUMN_IDX, TRUE);
APEX_JSON.WRITE ('data', V_REF_CURSOR);
APEX_JSON.CLOSE_OBJECT;
END P_AJAX_GETRETURNVALUES;
--
-- f_queryAutocomplete
--
FUNCTION F_QUERYAUTOCOMPLETE (PI_ROWNUM_START IN VARCHAR2 DEFAULT 0,
PI_ROWNUM_END IN VARCHAR2 DEFAULT 0)
RETURN VARCHAR2
IS
V_LOV_QUERY VARCHAR2 (32767);
V_QUERY VARCHAR2 (32767);
V_ATTR_AUTOCOMPLETE_SETTINGS APEX_APPLICATION_PAGE_ITEMS.ATTRIBUTE_01%TYPE
:= G_ITEM.ATTRIBUTE_01;
V_ATTR_AUTOCOMPLETE_SEARCH APEX_APPLICATION_PAGE_ITEMS.ATTRIBUTE_02%TYPE
:= G_ITEM.ATTRIBUTE_02;
BEGIN
V_LOV_QUERY := PREPARESQLQUERY;
V_QUERY := '
select
*
from (
' || V_LOV_QUERY || '
)
where
1=1
';
IF INSTR (V_ATTR_AUTOCOMPLETE_SETTINGS, 'UCSL') > 0
THEN
--custom search
V_QUERY := V_QUERY || ' ' || V_ATTR_AUTOCOMPLETE_SEARCH || '';
ELSE
--default search
V_QUERY := V_QUERY || F_AUTOCOMPLETEGETDEFAULSSEARCH ();
END IF;
IF PI_ROWNUM_START = 0 AND PI_ROWNUM_END = 0
THEN
RETURN V_QUERY;
END IF;
RETURN '
select
*
from (
select
a.*,
rownum pretius_rnum
from (
/**/
' || V_QUERY || '
/**/
) a
where rownum < ' || PI_ROWNUM_END || '
)
where pretius_rnum >= ' || PI_ROWNUM_START || '
';
END F_QUERYAUTOCOMPLETE;
--
-- f_query_popupSelected
--
FUNCTION F_QUERY_POPUPSELECTED (PI_COLLECTION_NAME IN VARCHAR2,
PI_QUERY IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN '
select
query.*
from
apex_collections
join (
'
|| PI_QUERY
|| '
) query
on
c001 = r
--replace(c001, '''
|| G_ITEM.LOV_NULL_VALUE
|| ''', r) = to_char(r)
where
collection_name = '''
|| PI_COLLECTION_NAME
|| '''
';
END F_QUERY_POPUPSELECTED;
--
-- f_query_popupSelected
--
FUNCTION F_QUERY_POPUPDATANOTMATCH (PI_COLLECTION_NAME IN VARCHAR2,
PI_QUERY IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN '
select
c001 d,
c001 r
from
apex_collections
where
collection_name = ''' || PI_COLLECTION_NAME || '''
and c001 not in (
select r from ( ' || PI_QUERY || ' )
)
';
END F_QUERY_POPUPDATANOTMATCH;
--
-- p_ajax_getSessionState
--
PROCEDURE P_AJAX_GETSESSIONSTATE
IS
V_SELECTED_ARR APEX_APPLICATION_GLOBAL.VC_ARR2;
V_COLLECTION_NAME APEX_COLLECTIONS.COLLECTION_NAME%TYPE
:= G_ITEM.NAME || '_SHOWSELECTED';
V_SESSION_VALUE VARCHAR2 (4000) := V (G_ITEM.NAME);
V_QUERY VARCHAR2 (32767);
V_REF_CURSOR SYS_REFCURSOR;
BEGIN
V_SELECTED_ARR :=
APEX_UTIL.STRING_TO_TABLE (P_STRING => V_SESSION_VALUE,
P_SEPARATOR => ':');
BEGIN
APEX_COLLECTION.DELETE_COLLECTION (V_COLLECTION_NAME);
APEX_COLLECTION.DELETE_COLLECTION (V_COLLECTION_NAME);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION (V_COLLECTION_NAME);
APEX_COLLECTION.ADD_MEMBERS (P_COLLECTION_NAME => V_COLLECTION_NAME,
P_C001 => V_SELECTED_ARR);
V_QUERY := PREPARESQLQUERY;
V_QUERY :=
F_QUERY_POPUPSELECTED (PI_COLLECTION_NAME => V_COLLECTION_NAME,
PI_QUERY => V_QUERY);
APEX_DEBUG.INFO (
G_LOGPREFIX || ' -> Item SQL Query "Get Session State" ... ');
APEX_DEBUG.LOG_LONG_MESSAGE (
P_MESSAGE => V_QUERY,
P_ENABLED => FALSE,
P_LEVEL => APEX_DEBUG.C_LOG_LEVEL_INFO);
V_REF_CURSOR := GETBINDEDREFCURSOR (V_QUERY);
APEX_JSON.OPEN_OBJECT;
IF G_DEBUG
THEN
APEX_JSON.WRITE ('query', V_QUERY, TRUE);
END IF;
APEX_JSON.WRITE ('request', G_AJAX_MODE, TRUE);
APEX_JSON.WRITE ('data', V_REF_CURSOR);
APEX_JSON.WRITE ('session', V_SESSION_VALUE);
APEX_JSON.CLOSE_OBJECT;
END P_AJAX_GETSESSIONSTATE;
--
-- p_ajax_autocomplete
--
PROCEDURE P_AJAX_AUTOCOMPLETE
IS
V_REF_CURSOR SYS_REFCURSOR;
V_REF_CURSOR_CNT SYS_REFCURSOR;
V_START_ROWNUM NUMBER;
V_END_ROWNUM NUMBER;
V_QUERY VARCHAR2 (32767);
BEGIN
V_START_ROWNUM :=
F_GETROWNUMLIMITERSTART (
P_PAGE => G_AJAX_FETCH_PAGE,
P_ROWS_PER_PAGE => G_AJAX_ROWS_PER_PAGE);
V_END_ROWNUM := V_START_ROWNUM + G_AJAX_ROWS_PER_PAGE;
V_QUERY :=
F_QUERYAUTOCOMPLETE (PI_ROWNUM_START => V_START_ROWNUM,
PI_ROWNUM_END => V_END_ROWNUM);
APEX_DEBUG.INFO (G_LOGPREFIX || ' -> Autocomplete SQL Query ... ');
APEX_DEBUG.LOG_LONG_MESSAGE (
P_MESSAGE => V_QUERY,
P_ENABLED => FALSE,
P_LEVEL => APEX_DEBUG.C_LOG_LEVEL_INFO);
V_REF_CURSOR := GETBINDEDREFCURSOR (V_QUERY);
V_REF_CURSOR_CNT :=
GETBINDEDREFCURSOR (
'select count(1) cnt from (' || F_QUERYAUTOCOMPLETE || ')');
APEX_JSON.OPEN_OBJECT;
IF G_DEBUG
THEN
APEX_JSON.WRITE ('query', V_QUERY, TRUE);
END IF;
APEX_JSON.WRITE ('request', G_AJAX_MODE, TRUE);
APEX_JSON.WRITE ('searchString', G_AJAX_SEARCH_STRING, TRUE);
APEX_JSON.WRITE ('rownumStart', V_START_ROWNUM, TRUE);
APEX_JSON.WRITE ('rownumEnd', V_END_ROWNUM, TRUE);
APEX_JSON.WRITE ('requestedPage', G_AJAX_FETCH_PAGE, TRUE);
APEX_JSON.WRITE ('rowsPerPage', G_AJAX_ROWS_PER_PAGE, TRUE);
APEX_JSON.WRITE ('data', V_REF_CURSOR);
APEX_JSON.WRITE ('dataVolume', V_REF_CURSOR_CNT);
APEX_JSON.CLOSE_OBJECT;
END P_AJAX_AUTOCOMPLETE;
--
-- p_ajax_popup_report
--
PROCEDURE P_AJAX_POPUP_REPORT
IS
V_START_ROWNUM NUMBER;
V_END_ROWNUM NUMBER;
V_QUERY VARCHAR2 (32767);
V_ORDER_BY VARCHAR2 (4000);
V_WHERE VARCHAR2 (32767);
E_MSG VARCHAR2 (4000);
V_REF_CURSOR SYS_REFCURSOR;
E_OPEN_REF_CURSOR EXCEPTION;
BEGIN
V_START_ROWNUM :=
F_GETROWNUMLIMITERSTART (
P_PAGE => G_AJAX_FETCH_PAGE,
P_ROWS_PER_PAGE => G_AJAX_ROWS_PER_PAGE);
V_END_ROWNUM := V_START_ROWNUM + G_AJAX_ROWS_PER_PAGE - 1;
V_QUERY := PREPARESQLQUERY;
V_QUERY := REPLACE (V_QUERY, CHR (9), ' ');
V_QUERY := REPLACE (V_QUERY, CHR (10) || CHR (13), ' ');
V_QUERY := REPLACE (V_QUERY, CHR (10), ' ');
V_QUERY := REPLACE (V_QUERY, CHR (13), ' ');
IF G_AJAX_REPOT_SORT_COL_IDX IS NOT NULL
THEN
V_ORDER_BY :=
'order by '
|| G_AJAX_REPOT_SORT_COL_IDX
|| ' '
|| G_AJAX_REPOT_SORT_COL_DIR
|| '';
END IF;
IF G_AJAX_SEARCH_STRING IS NOT NULL
THEN
V_WHERE := F_QUERYPREPARECONDITIONS (V_QUERY);
END IF;
V_QUERY :=
'
select
*
from (
select
a.*,
rownum pretius_rnum
from (
select
*
from (
'
|| V_QUERY
|| '
)
'
|| V_WHERE
|| '
'
|| V_ORDER_BY
|| '
) a
where rownum <= '
|| V_END_ROWNUM
|| '
)
where
pretius_rnum >= '
|| V_START_ROWNUM
|| '
';
APEX_DEBUG.INFO (
G_LOGPREFIX || ' -> Popup Report "Report" SQL Query ... ');
APEX_DEBUG.LOG_LONG_MESSAGE (
P_MESSAGE => V_QUERY,
P_ENABLED => FALSE,
P_LEVEL => APEX_DEBUG.C_LOG_LEVEL_INFO);
V_REF_CURSOR := GETBINDEDREFCURSOR (V_QUERY);
APEX_JSON.OPEN_OBJECT;
IF G_DEBUG
THEN
APEX_JSON.WRITE ('query', V_QUERY, TRUE);
END IF;
APEX_JSON.WRITE ('request', G_AJAX_MODE, TRUE);
APEX_JSON.WRITE ('searchString', G_AJAX_SEARCH_STRING, TRUE);
APEX_JSON.WRITE ('rownumStart', V_START_ROWNUM, TRUE);
APEX_JSON.WRITE ('rownumEnd', V_END_ROWNUM, TRUE);
APEX_JSON.WRITE ('requestedPage', G_AJAX_FETCH_PAGE, TRUE);
APEX_JSON.WRITE ('rowsPerPage', G_AJAX_ROWS_PER_PAGE, TRUE);
APEX_JSON.WRITE ('sortByColumnIdx', G_AJAX_REPOT_SORT_COL_IDX, TRUE);
APEX_JSON.WRITE ('sortByColumnDirection',
G_AJAX_REPOT_SORT_COL_DIR,
TRUE);
APEX_JSON.WRITE ('searchColumnIdx', G_AJAX_SEARCH_COLUMN_IDX, TRUE);
APEX_JSON.WRITE ('totalCount',
F_POPUPGETCURRENTPAGEDATACOUNT (V_WHERE));
APEX_JSON.WRITE ('data', V_REF_CURSOR);
APEX_JSON.CLOSE_OBJECT;
END P_AJAX_POPUP_REPORT;
--
-- p_ajax_popup_selected
--
PROCEDURE P_AJAX_POPUP_SELECTED
IS
V_SELECTED_ARR APEX_APPLICATION_GLOBAL.VC_ARR2
DEFAULT APEX_APPLICATION.G_F01;
V_COLLECTION_NAME APEX_COLLECTIONS.COLLECTION_NAME%TYPE
:= G_ITEM.NAME || '_SHOWSELECTED';
V_REF_CURSOR SYS_REFCURSOR;
V_REF_CURSOR_NOT_MATCHED SYS_REFCURSOR;
V_QUERY VARCHAR2 (32000);
V_QUERY_NOT_MATCHED VARCHAR2 (32000);
V_NULL_VALUE_SELECTED BOOLEAN := FALSE;
BEGIN
BEGIN
APEX_COLLECTION.DELETE_COLLECTION (V_COLLECTION_NAME);
APEX_COLLECTION.DELETE_COLLECTION (V_COLLECTION_NAME);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION (V_COLLECTION_NAME);
FOR I IN 1 .. V_SELECTED_ARR.COUNT
LOOP
IF V_SELECTED_ARR (I) = G_ITEM.LOV_NULL_VALUE
THEN
V_NULL_VALUE_SELECTED := TRUE;
CONTINUE;
END IF;
APEX_COLLECTION.ADD_MEMBER (
P_COLLECTION_NAME => V_COLLECTION_NAME,
P_C001 => V_SELECTED_ARR (I));
END LOOP;
V_QUERY := PREPARESQLQUERY;
V_QUERY_NOT_MATCHED :=
F_QUERY_POPUPDATANOTMATCH (
PI_COLLECTION_NAME => V_COLLECTION_NAME,
PI_QUERY => V_QUERY);
V_QUERY :=
F_QUERY_POPUPSELECTED (PI_COLLECTION_NAME => V_COLLECTION_NAME,
PI_QUERY => V_QUERY);
APEX_DEBUG.INFO (
G_LOGPREFIX || ' -> Popup Report "Show only selected" SQL Query:',
'fnord');
APEX_DEBUG.LOG_LONG_MESSAGE (
P_MESSAGE => V_QUERY,
P_ENABLED => FALSE,
P_LEVEL => APEX_DEBUG.C_LOG_LEVEL_INFO);
V_REF_CURSOR := GETBINDEDREFCURSOR (V_QUERY);
V_REF_CURSOR_NOT_MATCHED := GETBINDEDREFCURSOR (V_QUERY_NOT_MATCHED);
APEX_JSON.OPEN_OBJECT;
IF G_DEBUG
THEN
APEX_JSON.WRITE ('query', V_QUERY, TRUE);
END IF;
APEX_JSON.WRITE ('request', G_AJAX_MODE, TRUE);
APEX_JSON.WRITE ('data', V_REF_CURSOR);
APEX_JSON.WRITE ('dataNotMatched', V_REF_CURSOR_NOT_MATCHED);
IF V_NULL_VALUE_SELECTED
THEN
APEX_JSON.WRITE ('nullValueSelected', TRUE);
ELSE
APEX_JSON.WRITE ('nullValueSelected', FALSE);
END IF;
APEX_JSON.CLOSE_OBJECT;
END P_AJAX_POPUP_SELECTED;
--
-- procedure render
--
PROCEDURE RENDER (
P_ITEM IN APEX_PLUGIN.T_ITEM,
P_PLUGIN IN APEX_PLUGIN.T_PLUGIN,
P_PARAM IN APEX_PLUGIN.T_ITEM_RENDER_PARAM,
P_RESULT IN OUT NOCOPY APEX_PLUGIN.T_ITEM_RENDER_RESULT)
IS
V_DEBUG_PREFIX VARCHAR2 (100) := '# ' || P_PLUGIN.NAME;
V_ITEM_NAME VARCHAR2 (4000) := P_ITEM.NAME;
V_IS_REQUIRED VARCHAR2 (8)
:= CASE WHEN P_ITEM.IS_REQUIRED THEN 'required' ELSE NULL END;
V_ITEM_VALUE VARCHAR2 (32767) := P_PARAM.VALUE;
V_POPUP_DEFAULT_ICON VARCHAR2 (50) := 'fa-list-ul';
V_ITEM_NAME_ATTR VARCHAR2 (32767);
V_ITEM_ATTRIBUTES CLOB;
V_TRANSLATIONS_REF SYS_REFCURSOR;
V_ATTR_AUTOCOMPLETE_SETTINGS APEX_APPLICATION_PAGE_ITEMS.ATTRIBUTE_01%TYPE
:= P_ITEM.ATTRIBUTE_01;
V_ATTR_AUTOCOMPLETE_SEARCH APEX_APPLICATION_PAGE_ITEMS.ATTRIBUTE_02%TYPE
:= P_ITEM.ATTRIBUTE_02;
V_ATTR_AUTOCOMPLETE_TEMPLATE APEX_APPLICATION_PAGE_ITEMS.ATTRIBUTE_03%TYPE
:= P_ITEM.ATTRIBUTE_03;
V_ATTR_AUTOCOMPLETE_D_SEARCH APEX_APPLICATION_PAGE_ITEMS.ATTRIBUTE_04%TYPE
:= P_ITEM.ATTRIBUTE_04;
V_ATTR_SETTINGS APEX_APPLICATION_PAGE_ITEMS.ATTRIBUTE_05%TYPE
:= P_ITEM.ATTRIBUTE_05;
V_ATTR_POPUP_SETTINGS APEX_APPLICATION_PAGE_ITEMS.ATTRIBUTE_06%TYPE
:= P_ITEM.ATTRIBUTE_06;
V_ATTR_POPUP_COLUMNS_SETTINGS APEX_APPLICATION_PAGE_ITEMS.ATTRIBUTE_07%TYPE
:= P_ITEM.ATTRIBUTE_07;
V_ATTR_AUTOCOMPLETE_TAGS_NO APEX_APPLICATION_PAGE_ITEMS.ATTRIBUTE_08%TYPE
:= P_ITEM.ATTRIBUTE_08;
V_ATTR_AUTOCOMPLETE_MIN_LENGTH APEX_APPLICATION_PAGE_ITEMS.ATTRIBUTE_09%TYPE
:= P_ITEM.ATTRIBUTE_09;
V_ATTR_POPUP_REPORT_BASIC_CONF APEX_APPLICATION_PAGE_ITEMS.ATTRIBUTE_10%TYPE
:= P_ITEM.ATTRIBUTE_10;
V_ATTR_POPUP_TITLE_TEXT APEX_APPLICATION_PAGE_ITEMS.ATTRIBUTE_11%TYPE
:= P_ITEM.ATTRIBUTE_11;
V_ATTR_POPUP_WIDTH APEX_APPLICATION_PAGE_ITEMS.ATTRIBUTE_12%TYPE
:= P_ITEM.ATTRIBUTE_12;
V_ATTR_POPUP_HEIGHT APEX_APPLICATION_PAGE_ITEMS.ATTRIBUTE_13%TYPE
:= P_ITEM.ATTRIBUTE_13;
V_ATTR_AUTOCOMPLETE_MAXHEIGHT APEX_APPLICATION_PAGE_ITEMS.ATTRIBUTE_14%TYPE
:= P_ITEM.ATTRIBUTE_14;
V_ATTR_AUTOCOMPLETE_ROWS2SHOW APEX_APPLICATION_PAGE_ITEMS.ATTRIBUTE_15%TYPE
:= P_ITEM.ATTRIBUTE_15;
V_ITEM_ICON_CLASS APEX_APPLICATION_PAGE_ITEMS.ITEM_ICON_CSS_CLASSES%TYPE;
V_APEX_VERSION APEX_RELEASE.VERSION_NO%TYPE;
BEGIN
IF P_PARAM.VALUE_SET_BY_CONTROLLER AND P_PARAM.IS_READONLY
THEN
APEX_DEBUG.INFO (
G_LOGPREFIX
|| ' -> Item rendering aborted due to readonly and controller.');
RETURN;
END IF;
G_ITEM := P_ITEM;
G_PLUGIN := P_PLUGIN;
SELECT VERSION_NO INTO V_APEX_VERSION FROM APEX_RELEASE;
-- APEXPLUGIN-141
-- add css file based on apex version
IF V_APEX_VERSION LIKE '21.2%'
THEN
APEX_CSS.ADD_FILE (P_NAME => 'enhancedLovItem_21_2',
P_DIRECTORY => P_PLUGIN.FILE_PREFIX);
ELSE
APEX_CSS.ADD_FILE (P_NAME => 'enhancedLovItem_18_1',
P_DIRECTORY => P_PLUGIN.FILE_PREFIX);
END IF;
V_ITEM_NAME_ATTR :=
APEX_PLUGIN.GET_INPUT_NAME_FOR_PAGE_ITEM (
P_IS_MULTI_VALUE =>
INSTR (':' || P_ITEM.ATTRIBUTE_05 || ':', ':MS:') > 0);
APEX_PLUGIN_UTIL.PRINT_HIDDEN_IF_READONLY (
P_ITEM_NAME => P_ITEM.NAME,
P_VALUE => V_ITEM_VALUE,
P_IS_READONLY => P_PARAM.IS_READONLY,
P_IS_PRINTER_FRIENDLY => P_PARAM.IS_PRINTER_FRIENDLY);
IF P_PARAM.IS_PRINTER_FRIENDLY OR P_PARAM.IS_READONLY
THEN
APEX_DEBUG.INFO (G_LOGPREFIX || ' -> Render item as readonly');
APEX_PLUGIN_UTIL.PRINT_DISPLAY_ONLY (
P_ITEM_NAME => P_ITEM.NAME,
P_DISPLAY_VALUE => F_GETDISPLAYVALUES (V_ITEM_VALUE),
P_SHOW_LINE_BREAKS => FALSE,
P_ESCAPE => P_ITEM.ESCAPE_OUTPUT,
P_ATTRIBUTES => P_ITEM.ELEMENT_ATTRIBUTES);
HTP.P ('123');
ELSE
APEX_DEBUG.INFO (
G_LOGPREFIX || ' -> Render item as not readonly');
HTP.PRN ( ''
|| ' P_ITEM,
P_NAME => V_ITEM_NAME_ATTR,
P_DEFAULT_CLASS => 'text_field apex-item-text',
P_ADD_ID => TRUE,
P_ADD_LABELLEDBY => TRUE)
|| ' value="'
|| '');
APEX_PLUGIN_UTIL.PRINT_ESCAPED_VALUE (
APEX_ESCAPE.HTML (V_ITEM_VALUE));
HTP.PRN (
''
|| '"'
|| --closing of value attr
' data-return-value=""'
|| ' '
|| V_IS_REQUIRED
|| '>'
|| '');
--fetch icon
BEGIN
SELECT NVL (ITEM_ICON_CSS_CLASSES, V_POPUP_DEFAULT_ICON)
INTO V_ITEM_ICON_CLASS
FROM APEX_APPLICATION_PAGE_ITEMS
WHERE APPLICATION_ID = G_APP_ID
AND PAGE_ID = G_PAGE_ID
AND ITEM_NAME = P_ITEM.NAME;
EXCEPTION
WHEN OTHERS
THEN
V_ITEM_ICON_CLASS := V_POPUP_DEFAULT_ICON;
END;
APEX_JSON.INITIALIZE_CLOB_OUTPUT;
APEX_JSON.OPEN_OBJECT;
APEX_JSON.WRITE ('autoCompleteSettings',
V_ATTR_AUTOCOMPLETE_SETTINGS,
TRUE);
APEX_JSON.WRITE ('autoCompleteSettingsSearchLogic',
V_ATTR_AUTOCOMPLETE_SEARCH,
TRUE);
APEX_JSON.WRITE ('autoCompleteSettingsTemplate',
V_ATTR_AUTOCOMPLETE_TEMPLATE,
TRUE);
APEX_JSON.WRITE ('autoCompleteTagsNo',
V_ATTR_AUTOCOMPLETE_TAGS_NO,
TRUE);
APEX_JSON.WRITE ('autoCompleteMinInputLength',
V_ATTR_AUTOCOMPLETE_MIN_LENGTH,
TRUE);
APEX_JSON.WRITE ('autoCompleteMaxHeight',
V_ATTR_AUTOCOMPLETE_MAXHEIGHT,
TRUE);
APEX_JSON.WRITE ('autoCompleteRows2Show',
V_ATTR_AUTOCOMPLETE_ROWS2SHOW,
TRUE);
APEX_JSON.WRITE ('settings', NVL (V_ATTR_SETTINGS, ''), TRUE);
APEX_JSON.WRITE ('popupSettings', V_ATTR_POPUP_SETTINGS, TRUE);
APEX_JSON.WRITE ('popupColumnSettings',
V_ATTR_POPUP_COLUMNS_SETTINGS,
TRUE);
APEX_JSON.WRITE ('popupReportBasicConf',
V_ATTR_POPUP_REPORT_BASIC_CONF,
TRUE);
APEX_JSON.WRITE ('popupTitleText', V_ATTR_POPUP_TITLE_TEXT, TRUE);
APEX_JSON.WRITE ('popupWidth', V_ATTR_POPUP_WIDTH, TRUE);
APEX_JSON.WRITE ('popupHeight', V_ATTR_POPUP_HEIGHT, TRUE);
OPEN V_TRANSLATIONS_REF FOR
SELECT TRANSLATABLE_MESSAGE, MESSAGE_TEXT
FROM APEX_APPLICATION_TRANSLATIONS AAT
WHERE APPLICATION_ID = G_APP_ID
AND LANGUAGE_CODE =
(SELECT APPLICATION_PRIMARY_LANGUAGE
FROM APEX_APPLICATIONS
WHERE APPLICATION_ID = AAT.APPLICATION_ID)
AND TRANSLATABLE_MESSAGE LIKE 'PAELI%';
APEX_JSON.WRITE ('translations', V_TRANSLATIONS_REF);
APEX_JSON.CLOSE_OBJECT;
V_ITEM_ATTRIBUTES := APEX_JSON.GET_CLOB_OUTPUT;
APEX_JSON.FREE_OUTPUT;
APEX_JAVASCRIPT.ADD_ONLOAD_CODE (
''
|| '$("#'
|| V_ITEM_NAME
|| '").enhancedLovItem({'
|| ' "item": $.extend('
|| T_PAGE_ITEM_TO_JSON (P_ITEM)
|| ', {"icon": "'
|| V_ITEM_ICON_CLASS
|| '"}),'
|| ' "param": '
|| T_ITEM_RENDER_PARAM_TO_JSON (P_PARAM)
|| ','
|| ' "plugin": '
|| T_PLUGIN_TO_JSON (P_PLUGIN)
|| ','
|| ' "columns": '
|| F_QUERYGETCOLUMNSJSON
|| ','
|| ' "attributes": '
|| V_ITEM_ATTRIBUTES
|| ','
|| ' "apexVersion": "'
|| V_APEX_VERSION
|| '",'
|| ' "debug": '
|| CASE G_DEBUG WHEN TRUE THEN 'true' ELSE 'false' END
|| '});'
|| '');
END IF;
END RENDER;
FUNCTION ESCAPE_AJAX (P_VALUE IN VARCHAR2)
RETURN VARCHAR2
IS
V_RETURN VARCHAR2 (4000);
BEGIN
V_RETURN := APEX_ESCAPE.HTML (P_VALUE);
V_RETURN := REPLACE (V_RETURN, '''', '''''');
RETURN V_RETURN;
END;
--
-- procedure ajax
--
PROCEDURE AJAX (P_ITEM IN APEX_PLUGIN.T_ITEM,
P_PLUGIN IN APEX_PLUGIN.T_PLUGIN,
P_PARAM IN APEX_PLUGIN.T_ITEM_AJAX_PARAM,
P_RESULT IN OUT NOCOPY APEX_PLUGIN.T_ITEM_AJAX_RESULT)
IS
BEGIN
G_ITEM := P_ITEM;
G_PLUGIN := P_PLUGIN;
G_DEBUG :=
CASE
WHEN V ('DEBUG') IN ('YES', 'LEVEL6', 'LEVEL9') THEN TRUE
ELSE FALSE
END;
G_AJAX_MODE := ESCAPE_AJAX (V ('APP_AJAX_X01'));
G_AJAX_ROWS_PER_PAGE := TO_NUMBER (ESCAPE_AJAX (V ('APP_AJAX_X02')));
G_AJAX_SEARCH_STRING := ESCAPE_AJAX (V ('APP_AJAX_X03'));
G_AJAX_FETCH_PAGE := TO_NUMBER (ESCAPE_AJAX (V ('APP_AJAX_X04')));
G_AJAX_REPOT_SORT_COL_IDX :=
TO_NUMBER (ESCAPE_AJAX (V ('APP_AJAX_X05')));
G_AJAX_REPOT_SORT_COL_DIR := ESCAPE_AJAX (V ('APP_AJAX_X06'));
G_AJAX_SEARCH_COLUMN_IDX :=
TO_NUMBER (ESCAPE_AJAX (V ('APP_AJAX_X07')));
APEX_DEBUG.INFO (G_LOGPREFIX || ' -> AJAX call start');
APEX_DEBUG.INFO (
G_LOGPREFIX
|| ': AJAX_X01 (Mode) = '
|| '"'
|| G_AJAX_MODE
|| '"');
APEX_DEBUG.INFO (
G_LOGPREFIX
|| ': AJAX_X02 (Rows Per Page) = '
|| '"'
|| G_AJAX_ROWS_PER_PAGE
|| '"');
APEX_DEBUG.INFO (
G_LOGPREFIX
|| ': AJAX_X03 (Search String) = '
|| '"'
|| G_AJAX_SEARCH_STRING
|| '"');
APEX_DEBUG.INFO (
G_LOGPREFIX
|| ': AJAX_X04 (Fetch Page No.) = '
|| '"'
|| G_AJAX_FETCH_PAGE
|| '"');
APEX_DEBUG.INFO (
G_LOGPREFIX
|| ': AJAX_X05 (Report Sort Column No.) = '
|| '"'
|| G_AJAX_REPOT_SORT_COL_IDX
|| '"');
APEX_DEBUG.INFO (
G_LOGPREFIX
|| ': AJAX_X06 (Report Sort Direction) = '
|| '"'
|| G_AJAX_REPOT_SORT_COL_DIR
|| '"');
APEX_DEBUG.INFO (
G_LOGPREFIX
|| ': AJAX_X07 (Report Search Column No.) = '
|| '"'
|| G_AJAX_SEARCH_COLUMN_IDX
|| '"');
APEX_DEBUG.INFO (G_LOGPREFIX || ': Item SQL Query ... ');
APEX_DEBUG.LOG_LONG_MESSAGE (
P_MESSAGE => G_ITEM.LOV_DEFINITION,
P_ENABLED => FALSE,
P_LEVEL => APEX_DEBUG.C_LOG_LEVEL_INFO);
IF G_AJAX_MODE = 'AUTOCOMPLETE'
THEN
P_AJAX_AUTOCOMPLETE;
ELSIF G_AJAX_MODE = 'POPUP'
THEN
P_AJAX_POPUP_REPORT;
ELSIF G_AJAX_MODE = 'GETONLYSELECTED'
THEN
P_AJAX_POPUP_SELECTED;
ELSIF G_AJAX_MODE = 'GETSESSIONSTATE'
THEN
P_AJAX_GETSESSIONSTATE;
ELSIF G_AJAX_MODE = 'DEBUG'
THEN
NULL;
ELSIF G_AJAX_MODE = 'CASCADINGLOV'
THEN
APEX_JSON.OPEN_OBJECT;
APEX_JSON.WRITE ('request', G_AJAX_MODE, TRUE);
APEX_JSON.CLOSE_OBJECT;
ELSE
P_AJAX_GETRETURNVALUES;
END IF;
APEX_DEBUG.INFO ('Pretius Enhanced LOV Item -> AJAX call end.');
END AJAX;
END APEX_ENHANCED_LOV_ITEM;