-
Notifications
You must be signed in to change notification settings - Fork 0
/
dynamic_action_plugin_ca_insum_getigdata.sql
364 lines (362 loc) · 14.4 KB
/
dynamic_action_plugin_ca_insum_getigdata.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
prompt --application/set_environment
set define off verify off feedback off
whenever sqlerror exit sql.sqlcode rollback
--------------------------------------------------------------------------------
--
-- ORACLE Application Express (APEX) export file
--
-- You should run the script connected to SQL*Plus as the Oracle user
-- APEX_200100 or as the owner (parsing schema) of the application.
--
-- NOTE: Calls to apex_application_install override the defaults below.
--
--------------------------------------------------------------------------------
begin
wwv_flow_api.import_begin (
p_version_yyyy_mm_dd=>'2020.03.31'
,p_release=>'20.1.0.00.13'
,p_default_workspace_id=>172414234705169759
,p_default_application_id=>135
,p_default_id_offset=>0
,p_default_owner=>'INSIDE'
);
end;
/
prompt APPLICATION 135 - plugin
--
-- Application Export:
-- Application: 135
-- Name: plugin
-- Date and Time: 12:45 Tuesday April 5, 2022
-- Exported By: [email protected]
-- Flashback: 0
-- Export Type: Component Export
-- Manifest
-- PLUGIN: 244321356707522462
-- Manifest End
-- Version: 20.1.0.00.13
-- Instance ID: 199018087689490
--
begin
-- replace components
wwv_flow_api.g_mode := 'REPLACE';
end;
/
prompt --application/shared_components/plugins/dynamic_action/ca_insum_getigdata
begin
wwv_flow_api.create_plugin(
p_id=>wwv_flow_api.id(244321356707522462)
,p_plugin_type=>'DYNAMIC ACTION'
,p_name=>'CA.INSUM.GETIGDATA'
,p_display_name=>'Get IG Data'
,p_category=>'COMPONENT'
,p_supported_ui_types=>'DESKTOP:JQM_SMARTPHONE'
,p_plsql_code=>wwv_flow_string.join(wwv_flow_t_varchar2(
'function render ',
' ( p_dynamic_action in apex_plugin.t_dynamic_action',
' , p_plugin in apex_plugin.t_plugin ',
' )',
'return apex_plugin.t_dynamic_action_render_result',
'as',
' k_crlf varchar2(20) := chr(13) ||chr(10);',
' l_result apex_plugin.t_dynamic_action_render_result;',
' l_js_val1 varchar2(32767);',
' l_js_val2 varchar2(32767);',
' l_columns varchar2(32767);',
' l_paths varchar2(32767); ',
' l_console_query varchar2(32767) := ',
'q''{',
'select',
'#COLUMNS#',
' a.INSUM$ROW',
' from json_table (:#ITEM# , ''$[*]''',
' columns ',
'#PATHS#',
' INSUM$ROW number path ''$.INSUM$ROW''',
' ) a }''; ',
' ',
' --attributes',
' l_static_id p_dynamic_action.attribute_01%type := p_dynamic_action.attribute_01;',
' l_item_name p_dynamic_action.attribute_02%type := p_dynamic_action.attribute_02;',
' l_rows_to_return p_dynamic_action.attribute_03%type := p_dynamic_action.attribute_03;',
' l_include_columns p_dynamic_action.attribute_04%type := p_dynamic_action.attribute_04;',
' l_exclude_columns p_dynamic_action.attribute_05%type := p_dynamic_action.attribute_05;',
' ',
' l_include boolean;',
' l_count number;',
'',
'begin',
' ',
' --debug',
' if apex_application.g_debug ',
' then',
' apex_plugin_util.debug_dynamic_action',
' ( p_plugin => p_plugin',
' , p_dynamic_action => p_dynamic_action',
' );',
' end if;',
'',
' for i in (',
' select igc.name, igc.data_type',
' from APEX_APPL_PAGE_IG_COLUMNS igc',
' inner join apex_application_page_regions pr on pr.region_id = igc.region_id',
' where igc.application_id = :APP_ID',
' and (igc.page_id = :APP_PAGE_ID or igc.page_id = 0)',
' and pr.static_id = l_static_id',
' and igc.name not like ''APEX$%''',
' order by igc.display_sequence',
' ) loop',
' ',
' ',
' if l_include_columns is null then',
' if l_exclude_columns is null then',
' l_include := true;',
' else',
' select count(*)',
' into l_count',
' from table(apex_string.split(l_exclude_columns,'',''))',
' where trim(column_value) = i.name;',
'',
' l_include := (l_count = 0);',
' end if;',
' else',
' select count(*)',
' into l_count',
' from table(apex_string.split(l_include_columns,'',''))',
' where trim(column_value) = i.name;',
' ',
' l_include := (l_count > 0);',
' end if;',
'',
' if l_include then',
' l_js_val1 := l_js_val1 || '' var col'' || i.name || '' = igModel.getFieldKey("''|| i.name ||''");'' || k_crlf;',
' l_js_val2 := l_js_val2 || '' igRecord.'' || i.name || '' = igRow[col''|| i.name ||''];'' || k_crlf;',
'',
' l_columns := l_columns || '' a.'' || i.name || '','' || k_crlf;',
' l_paths := l_paths || '' '' || rpad(i.name,30) || '' varchar2(4000) path ''''$.'' || i.name || '''''','' || k_crlf;',
' end if;',
' end loop;',
' ',
' l_console_query := replace(l_console_query, ''#COLUMNS#'', rtrim(l_columns,k_crlf));',
' l_console_query := replace(l_console_query, ''#ITEM#'', l_item_name);',
' l_console_query := replace(l_console_query, ''#PATHS#'', rtrim(l_paths,k_crlf));',
' ',
' l_console_query := ''apex.debug(`'' || l_console_query || ''`);'';',
' ',
' l_result.javascript_function :=''function(){''',
' || l_console_query',
' || '' apex.debug("getIGData"); '' || k_crlf',
' || '' var myIGArray = []; '' || k_crlf',
' || '' var igGrid = apex.region("'' || l_static_id || ''").widget().interactiveGrid("getViews").grid; '' || k_crlf ',
' || '' var igModel = apex.region("'' || l_static_id || ''").widget().interactiveGrid("getViews", "grid").model; '' || k_crlf',
' || l_js_val1 ',
' || case when l_rows_to_return = ''ALL'' then',
' '' var igModel2 = igModel;'' || k_crlf',
' else ',
' '' var igModel2 = igGrid.getSelectedRecords();'' || k_crlf ',
' end',
' || '' let i = 0;''',
' || l_js_val1 ',
' || ''igModel2.forEach(function(igRow) {'' || k_crlf',
' || '' var igRecord = new Object(); '' || k_crlf',
' || '' i++; '' || k_crlf',
' || '' '' || l_js_val2 || k_crlf',
' || '' igRecord.INSUM$ROW = i;'' || k_crlf',
' || '' myIGArray.push(igRecord);'' || k_crlf',
' || ''});'' || k_crlf || k_crlf',
' || ''$s("'' || l_item_name || ''", JSON.stringify(myIGArray));'' || k_crlf',
' || ''}'';',
' ',
' return l_result;',
'end render;'))
,p_api_version=>2
,p_render_function=>'render'
,p_standard_attributes=>'ONLOAD'
,p_substitute_attributes=>true
,p_subscribe_plugin_settings=>true
,p_help_text=>wwv_flow_string.join(wwv_flow_t_varchar2(
'Places the data from an Interactive Grid into a page item as a JSON string. That page item can then be submitted for processing as server side code within a Dynamic Action step or as an item to submit for a report region. <strong>The select statement'
||' used to process the data will be shown in the browser console.</strong> The INSUM$ROW column is always added as an indicator of the way the data was sorted at the time it was taken from the IG. An example is shown below.',
'<pre>',
'select',
' a.ID,',
' a.FIRST_NAME,',
' a.LAST_NAME,',
' a.INSUM$ROW',
' from json_table (:P1_GRID_DATA , ''$[*]''',
' columns ',
' ID varchar2(4000) path ''$.ID'',',
' FIRST_NAME varchar2(4000) path ''$.FIRST_NAME'',',
' LAST_NAME varchar2(4000) path ''$.LAST_NAME'',',
' INSUM$ROW number path ''$.INSUM$ROW''',
' ) a ',
'</pre>',
'',
'This may be used within an "Execute PL/SQL Code" Dynamic Action as shown below:',
'',
'<pre>',
'begin',
' for i in (',
' select',
' a.ID,',
' a.FIRST_NAME,',
' a.LAST_NAME,',
' a.INSUM$ROW',
' from json_table (:P1_GRID_DATA , ''$[*]''',
' columns ',
' ID varchar2(4000) path ''$.ID'',',
' FIRST_NAME varchar2(4000) path ''$.FIRST_NAME'',',
' LAST_NAME varchar2(4000) path ''$.LAST_NAME'',',
' INSUM$ROW number path ''$.INSUM$ROW''',
' ) a ',
' )',
' ) loop',
'',
' my_procedure(i.id, i.first_name, i.last_name);',
'',
' end loop;',
'end;',
'</pre>'))
,p_version_identifier=>'0.1'
);
wwv_flow_api.create_plugin_attribute(
p_id=>wwv_flow_api.id(244334965207921716)
,p_plugin_id=>wwv_flow_api.id(244321356707522462)
,p_attribute_scope=>'COMPONENT'
,p_attribute_sequence=>1
,p_display_sequence=>10
,p_prompt=>'IG Region Static ID'
,p_attribute_type=>'TEXT'
,p_is_required=>false
,p_is_translatable=>false
,p_help_text=>wwv_flow_string.join(wwv_flow_t_varchar2(
'<p>Enter the static ID of the Interactive Grid region.</p>',
'<p>Data from the associated Interactive Grid will be placed into the identified page item as a JSON string. That page item can then be submitted for processing as server side code within a Dynamic Action step or as an item to submit for a report regi'
||'on. <strong>The select statement used to process the data will be shown in the browser console.</strong> The INSUM$ROW column is always added as an indicator of the way the data was sorted at the time it was taken from the IG. An example is shown bel'
||'ow.',
'</p>',
'<pre>',
'select',
' a.ID,',
' a.FIRST_NAME,',
' a.LAST_NAME,',
' a.INSUM$ROW',
' from json_table (:P1_GRID_DATA , ''$[*]''',
' columns ',
' ID varchar2(4000) path ''$.ID'',',
' FIRST_NAME varchar2(4000) path ''$.FIRST_NAME'',',
' LAST_NAME varchar2(4000) path ''$.LAST_NAME'',',
' INSUM$ROW number path ''$.INSUM$ROW''',
' ) a ',
'</pre>',
'',
'This may be used within an "Execute PL/SQL Code" Dynamic Action as shown below:',
'',
'<pre>',
'begin',
' for i in (',
' select',
' a.ID,',
' a.FIRST_NAME,',
' a.LAST_NAME,',
' a.INSUM$ROW',
' from json_table (:P1_GRID_DATA , ''$[*]''',
' columns ',
' ID varchar2(4000) path ''$.ID'',',
' FIRST_NAME varchar2(4000) path ''$.FIRST_NAME'',',
' LAST_NAME varchar2(4000) path ''$.LAST_NAME'',',
' INSUM$ROW number path ''$.INSUM$ROW''',
' ) a ',
' )',
' ) loop',
'',
' my_procedure(i.id, i.first_name, i.last_name);',
'',
' end loop;',
'end;',
'</pre>'))
);
wwv_flow_api.create_plugin_attribute(
p_id=>wwv_flow_api.id(244329271009819028)
,p_plugin_id=>wwv_flow_api.id(244321356707522462)
,p_attribute_scope=>'COMPONENT'
,p_attribute_sequence=>2
,p_display_sequence=>20
,p_prompt=>'Item to Hold Data'
,p_attribute_type=>'PAGE ITEM'
,p_is_required=>true
,p_is_translatable=>false
,p_examples=>'P1_GRID_DATA'
,p_help_text=>wwv_flow_string.join(wwv_flow_t_varchar2(
'<p>This DA will put JSON data from the grid into the value of this item on the page. The item can then be sent back to the database with another DA or as part of a region refresh.</p>',
'<p>This item will usually be a hidden item with protection set to "off."</p>'))
);
wwv_flow_api.create_plugin_attribute(
p_id=>wwv_flow_api.id(244323917418764695)
,p_plugin_id=>wwv_flow_api.id(244321356707522462)
,p_attribute_scope=>'COMPONENT'
,p_attribute_sequence=>3
,p_display_sequence=>30
,p_prompt=>'Rows to Return'
,p_attribute_type=>'SELECT LIST'
,p_is_required=>true
,p_default_value=>'ALL'
,p_is_translatable=>false
,p_lov_type=>'STATIC'
,p_help_text=>'Indicate if all rows or only selected rows should be returned. Note: In both cases, only rows displayed on the screen will be returned.'
);
wwv_flow_api.create_plugin_attr_value(
p_id=>wwv_flow_api.id(244324203727765902)
,p_plugin_attribute_id=>wwv_flow_api.id(244323917418764695)
,p_display_sequence=>10
,p_display_value=>'All Rows'
,p_return_value=>'ALL'
,p_help_text=>'Return all rows regardless of the row selector state.'
);
wwv_flow_api.create_plugin_attr_value(
p_id=>wwv_flow_api.id(244324611572769377)
,p_plugin_attribute_id=>wwv_flow_api.id(244323917418764695)
,p_display_sequence=>20
,p_display_value=>'Selected Rows'
,p_return_value=>'SELECTED'
,p_help_text=>'Only return rows where the row selector is checked.'
);
wwv_flow_api.create_plugin_attribute(
p_id=>wwv_flow_api.id(244668959492835500)
,p_plugin_id=>wwv_flow_api.id(244321356707522462)
,p_attribute_scope=>'COMPONENT'
,p_attribute_sequence=>4
,p_display_sequence=>40
,p_prompt=>'Include Columns'
,p_attribute_type=>'TEXT'
,p_is_required=>false
,p_is_translatable=>false
,p_examples=>'ID,FIRST_NAME,LAST_NAME'
,p_help_text=>'List of IG columns that you wish to include. If empty all columns except those listed in "Exclude Columns" will be included. Note: these are the column names/aliases that you have defined in your query or table. This list is case sensitive and will t'
||'ypically be upper case.'
);
wwv_flow_api.create_plugin_attribute(
p_id=>wwv_flow_api.id(244328079616804775)
,p_plugin_id=>wwv_flow_api.id(244321356707522462)
,p_attribute_scope=>'COMPONENT'
,p_attribute_sequence=>5
,p_display_sequence=>50
,p_prompt=>'Exclude Columns'
,p_attribute_type=>'TEXT'
,p_is_required=>false
,p_is_translatable=>false
,p_examples=>'CREATED_BY,CREATE_DATE,UPDATED_BY,UPDATE_DATE'
,p_help_text=>wwv_flow_string.join(wwv_flow_t_varchar2(
'<p>List of IG columns that you do not wish to exclude. Note: these are the column names/aliases that you have defined in your query or table. This list is case sensitive and will typically be upper case.</p>',
'<p>This value will be ignored if Include Columns is populated</p>'))
);
end;
/
prompt --application/end_environment
begin
wwv_flow_api.import_end(p_auto_install_sup_obj => nvl(wwv_flow_application_install.get_auto_install_sup_obj, false));
commit;
end;
/
set verify on feedback on define on
prompt ...done