1: package body icx_on as
2: /* $Header: ICXONMB.pls 120.0 2005/10/07 12:16:13 gjimenez noship $ */
3:
4: procedure get_page(p_attributes in icx_on_utilities.v80_table,
5: p_conditions in icx_on_utilities.v80_table,
6: p_inputs in icx_on_utilities.v80_table,
7: p_match in varchar2,
8: p_and_or in varchar2) is
1: package body icx_on as
2: /* $Header: ICXONMB.pls 120.0 2005/10/07 12:16:13 gjimenez noship $ */
3:
4: procedure get_page(p_attributes in icx_on_utilities.v80_table,
5: p_conditions in icx_on_utilities.v80_table,
6: p_inputs in icx_on_utilities.v80_table,
7: p_match in varchar2,
8: p_and_or in varchar2) is
9: l_timer number;
2: /* $Header: ICXONMB.pls 120.0 2005/10/07 12:16:13 gjimenez noship $ */
3:
4: procedure get_page(p_attributes in icx_on_utilities.v80_table,
5: p_conditions in icx_on_utilities.v80_table,
6: p_inputs in icx_on_utilities.v80_table,
7: p_match in varchar2,
8: p_and_or in varchar2) is
9: l_timer number;
10:
29: begin
30:
31: -- select HSECS into l_timer from v$timer;htp.p('begin get_page @ '||l_timer);htp.nl;
32:
33: l_type := icx_on_utilities.g_on_parameters(1);
34:
35: if l_type = 'Q1' or l_type = 'Q5'
36: then
37: l_flow_appl_id := icx_on_utilities.g_on_parameters(2);
33: l_type := icx_on_utilities.g_on_parameters(1);
34:
35: if l_type = 'Q1' or l_type = 'Q5'
36: then
37: l_flow_appl_id := icx_on_utilities.g_on_parameters(2);
38: l_flow_code := icx_on_utilities.g_on_parameters(3);
39: l_page_appl_id := icx_on_utilities.g_on_parameters(4);
40: l_page_code := icx_on_utilities.g_on_parameters(5);
41:
34:
35: if l_type = 'Q1' or l_type = 'Q5'
36: then
37: l_flow_appl_id := icx_on_utilities.g_on_parameters(2);
38: l_flow_code := icx_on_utilities.g_on_parameters(3);
39: l_page_appl_id := icx_on_utilities.g_on_parameters(4);
40: l_page_code := icx_on_utilities.g_on_parameters(5);
41:
42: l_Y := icx_call.encrypt2('DQ'||'*'||l_flow_appl_id||'*'||l_flow_code||'*'||l_page_appl_id||'*'||l_page_code||'*****]');
35: if l_type = 'Q1' or l_type = 'Q5'
36: then
37: l_flow_appl_id := icx_on_utilities.g_on_parameters(2);
38: l_flow_code := icx_on_utilities.g_on_parameters(3);
39: l_page_appl_id := icx_on_utilities.g_on_parameters(4);
40: l_page_code := icx_on_utilities.g_on_parameters(5);
41:
42: l_Y := icx_call.encrypt2('DQ'||'*'||l_flow_appl_id||'*'||l_flow_code||'*'||l_page_appl_id||'*'||l_page_code||'*****]');
43:
36: then
37: l_flow_appl_id := icx_on_utilities.g_on_parameters(2);
38: l_flow_code := icx_on_utilities.g_on_parameters(3);
39: l_page_appl_id := icx_on_utilities.g_on_parameters(4);
40: l_page_code := icx_on_utilities.g_on_parameters(5);
41:
42: l_Y := icx_call.encrypt2('DQ'||'*'||l_flow_appl_id||'*'||l_flow_code||'*'||l_page_appl_id||'*'||l_page_code||'*****]');
43:
44: l_help_url := 'OracleON.IC?X='||icx_call.encrypt2(l_flow_appl_id||'*'||l_flow_code||'*'||l_page_appl_id||'*'||l_page_code||'*'||'ICX_HLP_QUERY'||'**]');
65: end if;
66: else
67: if l_type = 'Q1'
68: then
69: icx_on_utilities.findPage(l_flow_appl_id,l_flow_code,l_page_appl_id,l_page_code,'','','',1,'',5,'Y',l_Y,l_help_url);
70: else
71: icx_on_utilities.findPage(l_flow_appl_id,l_flow_code,l_page_appl_id,l_page_code,'','','',5,'',1,'Y',l_Y,l_help_url);
72: end if;
73: end if;
67: if l_type = 'Q1'
68: then
69: icx_on_utilities.findPage(l_flow_appl_id,l_flow_code,l_page_appl_id,l_page_code,'','','',1,'',5,'Y',l_Y,l_help_url);
70: else
71: icx_on_utilities.findPage(l_flow_appl_id,l_flow_code,l_page_appl_id,l_page_code,'','','',5,'',1,'Y',l_Y,l_help_url);
72: end if;
73: end if;
74: else
75: if l_type = 'DQ'
73: end if;
74: else
75: if l_type = 'DQ'
76: then
77: l_encrypted_where := icx_on_utilities.g_on_parameters(9);
78:
79: if l_encrypted_where is null
80: then
81: l_where := icx_on_utilities.whereSegment(p_attributes,p_conditions,p_inputs,p_match,p_and_or);
77: l_encrypted_where := icx_on_utilities.g_on_parameters(9);
78:
79: if l_encrypted_where is null
80: then
81: l_where := icx_on_utilities.whereSegment(p_attributes,p_conditions,p_inputs,p_match,p_and_or);
82: l_encrypted_where := icx_call.encrypt2(l_where);
83: else
84: l_where := icx_call.decrypt2(l_encrypted_where);
85: end if;
85: end if;
86:
87: -- htp.p('DEBUG where => '||l_where);htp.nl;
88:
89: icx_on_utilities.g_on_parameters(9) := l_encrypted_where;
90:
91: icx_on_utilities.getRegions(l_where);
92: elsif l_type = 'W'
93: then
87: -- htp.p('DEBUG where => '||l_where);htp.nl;
88:
89: icx_on_utilities.g_on_parameters(9) := l_encrypted_where;
90:
91: icx_on_utilities.getRegions(l_where);
92: elsif l_type = 'W'
93: then
94: l_start := 1;
95:
95:
96: select REGION_CODE
97: into l_start_region
98: from AK_FLOW_PAGE_REGIONS
99: where PAGE_CODE = icx_on_utilities.g_on_parameters(5)
100: and PAGE_APPLICATION_ID = icx_on_utilities.g_on_parameters(4)
101: and FLOW_CODE = icx_on_utilities.g_on_parameters(3)
102: and FLOW_APPLICATION_ID = icx_on_utilities.g_on_parameters(2)
103: and PARENT_REGION_CODE is null;
96: select REGION_CODE
97: into l_start_region
98: from AK_FLOW_PAGE_REGIONS
99: where PAGE_CODE = icx_on_utilities.g_on_parameters(5)
100: and PAGE_APPLICATION_ID = icx_on_utilities.g_on_parameters(4)
101: and FLOW_CODE = icx_on_utilities.g_on_parameters(3)
102: and FLOW_APPLICATION_ID = icx_on_utilities.g_on_parameters(2)
103: and PARENT_REGION_CODE is null;
104:
97: into l_start_region
98: from AK_FLOW_PAGE_REGIONS
99: where PAGE_CODE = icx_on_utilities.g_on_parameters(5)
100: and PAGE_APPLICATION_ID = icx_on_utilities.g_on_parameters(4)
101: and FLOW_CODE = icx_on_utilities.g_on_parameters(3)
102: and FLOW_APPLICATION_ID = icx_on_utilities.g_on_parameters(2)
103: and PARENT_REGION_CODE is null;
104:
105: -- The parameters(6) is a funny used to pass in a direct where
98: from AK_FLOW_PAGE_REGIONS
99: where PAGE_CODE = icx_on_utilities.g_on_parameters(5)
100: and PAGE_APPLICATION_ID = icx_on_utilities.g_on_parameters(4)
101: and FLOW_CODE = icx_on_utilities.g_on_parameters(3)
102: and FLOW_APPLICATION_ID = icx_on_utilities.g_on_parameters(2)
103: and PARENT_REGION_CODE is null;
104:
105: -- The parameters(6) is a funny used to pass in a direct where
106: l_where := replace(icx_on_utilities.g_on_parameters(6),'~','=')||'**]';
102: and FLOW_APPLICATION_ID = icx_on_utilities.g_on_parameters(2)
103: and PARENT_REGION_CODE is null;
104:
105: -- The parameters(6) is a funny used to pass in a direct where
106: l_where := replace(icx_on_utilities.g_on_parameters(6),'~','=')||'**]';
107: l_encrypted_where := icx_call.encrypt2(l_where);
108:
109: -- 2093780 nlbarlow, multiple region first page
110: select count(*)
109: -- 2093780 nlbarlow, multiple region first page
110: select count(*)
111: into l_count
112: from AK_FLOW_PAGE_REGIONS
113: where PAGE_CODE = icx_on_utilities.g_on_parameters(5)
114: and PAGE_APPLICATION_ID = icx_on_utilities.g_on_parameters(4)
115: and FLOW_CODE = icx_on_utilities.g_on_parameters(3)
116: and FLOW_APPLICATION_ID = icx_on_utilities.g_on_parameters(2);
117:
110: select count(*)
111: into l_count
112: from AK_FLOW_PAGE_REGIONS
113: where PAGE_CODE = icx_on_utilities.g_on_parameters(5)
114: and PAGE_APPLICATION_ID = icx_on_utilities.g_on_parameters(4)
115: and FLOW_CODE = icx_on_utilities.g_on_parameters(3)
116: and FLOW_APPLICATION_ID = icx_on_utilities.g_on_parameters(2);
117:
118: if l_count > 1
111: into l_count
112: from AK_FLOW_PAGE_REGIONS
113: where PAGE_CODE = icx_on_utilities.g_on_parameters(5)
114: and PAGE_APPLICATION_ID = icx_on_utilities.g_on_parameters(4)
115: and FLOW_CODE = icx_on_utilities.g_on_parameters(3)
116: and FLOW_APPLICATION_ID = icx_on_utilities.g_on_parameters(2);
117:
118: if l_count > 1
119: then
112: from AK_FLOW_PAGE_REGIONS
113: where PAGE_CODE = icx_on_utilities.g_on_parameters(5)
114: and PAGE_APPLICATION_ID = icx_on_utilities.g_on_parameters(4)
115: and FLOW_CODE = icx_on_utilities.g_on_parameters(3)
116: and FLOW_APPLICATION_ID = icx_on_utilities.g_on_parameters(2);
117:
118: if l_count > 1
119: then
120: icx_on_utilities.g_on_parameters(1) := 'W';
116: and FLOW_APPLICATION_ID = icx_on_utilities.g_on_parameters(2);
117:
118: if l_count > 1
119: then
120: icx_on_utilities.g_on_parameters(1) := 'W';
121: else
122: icx_on_utilities.g_on_parameters(1) := 'DQ';
123: end if;
124:
118: if l_count > 1
119: then
120: icx_on_utilities.g_on_parameters(1) := 'W';
121: else
122: icx_on_utilities.g_on_parameters(1) := 'DQ';
123: end if;
124:
125: icx_on_utilities.g_on_parameters(6) := l_start;
126: icx_on_utilities.g_on_parameters(7) := '';
121: else
122: icx_on_utilities.g_on_parameters(1) := 'DQ';
123: end if;
124:
125: icx_on_utilities.g_on_parameters(6) := l_start;
126: icx_on_utilities.g_on_parameters(7) := '';
127: icx_on_utilities.g_on_parameters(8) := l_start_region;
128: icx_on_utilities.g_on_parameters(9) := l_encrypted_where;
129:
122: icx_on_utilities.g_on_parameters(1) := 'DQ';
123: end if;
124:
125: icx_on_utilities.g_on_parameters(6) := l_start;
126: icx_on_utilities.g_on_parameters(7) := '';
127: icx_on_utilities.g_on_parameters(8) := l_start_region;
128: icx_on_utilities.g_on_parameters(9) := l_encrypted_where;
129:
130: icx_on_cabo.wherePage;
123: end if;
124:
125: icx_on_utilities.g_on_parameters(6) := l_start;
126: icx_on_utilities.g_on_parameters(7) := '';
127: icx_on_utilities.g_on_parameters(8) := l_start_region;
128: icx_on_utilities.g_on_parameters(9) := l_encrypted_where;
129:
130: icx_on_cabo.wherePage;
131: elsif l_type = 'D'
124:
125: icx_on_utilities.g_on_parameters(6) := l_start;
126: icx_on_utilities.g_on_parameters(7) := '';
127: icx_on_utilities.g_on_parameters(8) := l_start_region;
128: icx_on_utilities.g_on_parameters(9) := l_encrypted_where;
129:
130: icx_on_cabo.wherePage;
131: elsif l_type = 'D'
132: then
131: elsif l_type = 'D'
132: then
133: icx_on_cabo.WFPage;
134: else
135: icx_on_utilities.getRegions;
136: end if;
137:
138: if ak_query_pkg.g_regions_table(0).flow_application_id > 0
139: then
141: icx_sec.g_mode_code = 'SLAVE')
142: then
143: icx_on_cabo.displayPage;
144: else
145: icx_on_utilities.displayPage;
146: end if;
147: end if;
148:
149: end if;
187: l_start_region varchar2(30);
188: c_rowid rowid;
189: l_encrypted_where number;
190: c_unique_key_name varchar2(30);
191: c_parameters icx_on_utilities.v240_table;
192: c_keys icx_on_utilities.v80_table;
193:
194: l_region_appl_id number(15);
195: l_region_code varchar2(30);
188: c_rowid rowid;
189: l_encrypted_where number;
190: c_unique_key_name varchar2(30);
191: c_parameters icx_on_utilities.v240_table;
192: c_keys icx_on_utilities.v80_table;
193:
194: l_region_appl_id number(15);
195: l_region_code varchar2(30);
196: l_where varchar2(2000);
224:
225:
226: begin
227:
228: icx_on_utilities.unpack_parameters(icx_call.decrypt2(S),c_parameters);
229:
230: l_type := c_parameters(1);
231:
232: if l_type = 'DQ' or l_type = 'W'
253:
254: l_responsibility_id := icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID);
255: l_user_id := icx_sec.getID(icx_sec.PV_WEB_USER_ID);
256:
257: icx_on_utilities.unpack_whereSegment(l_where,l_where_clause,l_query_binds);
258:
259: ak_query_pkg.exec_query(
260: P_FLOW_APPL_ID => l_flow_appl_id,
261: P_FLOW_CODE => l_flow_code,
332: end if;
333:
334: owa_util.mime_header('application/x-excel', TRUE);
335:
336: -- icx_on_utilities2.printPLSQLtables;
337:
338: if ak_query_pkg.g_results_table.COUNT = 0
339: then
340: fnd_message.set_name('ICX','ICX_NO_RECORDS_FOUND');