[Home] [Help]
168: where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
169: and query_alias_id is not null) alias1,
170: (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
171: 'A'||to_char(rownum) line_num
172: from AMS_QUERY_ALIAS alias,ams_list_src_types types
173: where alias.template_id = p_template_id
174: and alias.object_name = types.source_type_code
175: and types.list_source_type = 'TARGET'
176: order by alias.query_alias_id) alias2
185: where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
186: and query_alias_id is not null) alias1,
187: (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
188: 'A'||to_char(rownum) line_num
189: from AMS_QUERY_ALIAS alias,ams_list_src_types types
190: where alias.template_id = p_template_id
191: and alias.object_name = types.source_type_code
192: and types.list_source_type = 'TARGET'
193: order by alias.query_alias_id) alias2
204: where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
205: and query_alias_id is not null) alias1,
206: (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
207: 'A'||to_char(rownum) line_num
208: from AMS_QUERY_ALIAS alias,ams_list_src_types types
209: where alias.template_id = p_template_id
210: and alias.object_name = types.source_type_code
211: and types.list_source_type = 'TARGET'
212: order by alias.query_alias_id) alias2
215: cursor c_Where_clause is
216: select types.master_source_type_flag,
217: decode(master_source_type_flag,'Y','A'||to_char(rownum)||'.'||source_object_pk_field||' = ',
218: 'A'||to_char(rownum)||'.'||source_object_pk_field ||'(+)')
219: from AMS_QUERY_ALIAS alias,ams_list_src_types types
220: where alias.template_id = p_template_id
221: and alias.object_name = types.source_type_code
222: and types.list_source_type = 'TARGET'
223: order by alias.query_alias_id;
233: where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
234: and query_alias_id is not null) alias1,
235: (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
236: 'A'||to_char(rownum) line_num
237: from AMS_QUERY_ALIAS alias,ams_list_src_types types
238: where alias.template_id = p_template_id
239: and alias.object_name = types.source_type_code
240: and types.list_source_type = 'TARGET'
241: order by alias.query_alias_id) alias2
246: cursor c_index_columns is
247: select column_name from sys.all_tab_columns where table_name = l_view_name
248: and column_name not in (
249: select flds.source_column_name
250: from ams_list_src_fields flds ,AMS_QUERY_ALIAS alias,ams_list_src_types types,
251: ams_query_template_all qtemp
252: where alias.template_id = p_template_id
253: and alias.object_name = types.source_type_code
254: and types.LIST_SOURCE_TYPE_ID = flds.LIST_SOURCE_TYPE_ID
267:
268:
269: cursor c_master_view is
270: select types.source_object_name
271: from AMS_QUERY_ALIAS alias,ams_list_src_types types
272: where alias.template_id = p_template_id
273: and types.master_source_type_flag = 'Y'
274: and alias.object_name = types.source_type_code;
275:
291: where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
292: and query_alias_id is not null) alias1,
293: (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
294: 'A'||to_char(rownum) line_num
295: from AMS_QUERY_ALIAS alias,ams_list_src_types types
296: where alias.template_id = p_template_id
297: and alias.object_name = types.source_type_code
298: and types.list_source_type = 'TARGET'
299: order by alias.query_alias_id) alias2
301:
302: /*
303: cursor c_master_columns is
304: select flds.source_column_name
305: from ams_list_src_fields flds ,AMS_QUERY_ALIAS alias,ams_list_src_types types,
306: ams_query_template_all qtemp
307: where alias.template_id = p_template_id
308: and alias.object_name = types.source_type_code
309: and types.LIST_SOURCE_TYPE_ID = flds.LIST_SOURCE_TYPE_ID
315: -- SOLIN, select primary key only to save table space
316: -- In preview entries, it will join master data source.
317: cursor c_master_columns is
318: --select flds.source_column_name
319: --from ams_list_src_fields flds ,AMS_QUERY_ALIAS alias,ams_list_src_types types,
320: -- ams_query_template_all qtemp
321: --where alias.template_id = p_template_id
322: -- and alias.object_name = types.source_type_code
323: -- and types.LIST_SOURCE_TYPE_ID = flds.LIST_SOURCE_TYPE_ID
327: -- and qtemp.template_id = alias.template_id
328: -- and qtemp.list_src_type = types.source_type_code
329: --Union
330: select typ.SOURCE_OBJECT_PK_FIELD
331: from ams_query_template_all tmp,ams_list_src_types typ
332: where tmp.template_id = p_template_id
333: and tmp.list_src_type = typ.SOURCE_TYPE_CODE;
334:
335:
346: where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
347: and query_alias_id is not null) alias1,
348: (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
349: 'A'||to_char(rownum) line_num,types.list_source_type_id
350: from AMS_QUERY_ALIAS alias,ams_list_src_types types
351: where alias.template_id = p_template_id
352: and alias.object_name = types.source_type_code
353: and types.list_source_type = 'TARGET'
354: order by alias.query_alias_id) alias2
362:
363:
364: cursor c_const_where_cols is
365: select nvl(assoc.master_source_type_pk_column,types.SOURCE_OBJECT_PK_FIELD ),assoc.sub_source_type_pk_column from
366: ams_list_src_type_assocs assoc , ams_list_src_types types
367: where assoc.master_source_type_id = l_master_lstype_id
368: and assoc.sub_source_type_id = l_child_lstype_id
369: and assoc.master_source_type_id = types.list_source_type_id;
370:
374: x_msg_count number;
375: x_msg_data varchar2(2000);
376:
377: cursor c_master_pk is
378: select source_object_pk_field from ams_list_src_types where list_source_type_id = p_template_id;
379:
380: l_master_pk varchar2(60);
381:
382: cursor c_policy_exists is
395: l_mastcol_alias varchar2(100);
396: l_mastobj_name varchar2(100);
397: cursor c_find_mast_alias is
398: select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,'A'||to_char(rownum) line_num
399: from AMS_QUERY_ALIAS alias, ams_list_src_types types
400: where alias.template_id = p_template_id
401: and alias.object_name = types.source_type_code
402: and types.list_source_type = 'TARGET'
403: order by alias.query_alias_id;
403: order by alias.query_alias_id;
404:
405: cursor c_mast_alias is
406: select types.LIST_SOURCE_TYPE_ID, types.source_object_name, types.master_source_type_flag,'A'||to_char(rownum)||'.' line_num
407: from AMS_QUERY_ALIAS alias, ams_list_src_types types
408: where alias.template_id = p_template_id
409: and alias.object_name = types.source_type_code
410: and types.list_source_type = 'TARGET'
411: order by alias.query_alias_id;
426:
427: -- SOLIN, Bug 3696553
428: CURSOR c_get_dblink is
429: select NVL(types.remote_flag, 'N'), database_link
430: from AMS_QUERY_ALIAS alias, ams_list_src_types types
431: where alias.template_id = p_template_id
432: and alias.object_name = types.source_type_code;
433:
434: l_remote_flag VARCHAR2(1);
1321:
1322: -- SOLIN, Bug 3696553
1323: CURSOR c_get_dblink is
1324: select NVL(types.remote_flag, 'N'), database_link
1325: from AMS_QUERY_ALIAS alias, ams_list_src_types types
1326: where alias.template_id = p_template_id
1327: and alias.object_name = types.source_type_code;
1328:
1329: l_remote_flag VARCHAR2(1);
1508:
1509: cursor c_left_oprand (p_cond_id NUMBER) is
1510: select struct.non_variant_value attr_name, source_object_name table_name,src_types.list_source_type_id
1511: -- || alias.ALIAS_SEQ table_name
1512: from AMS_QUERY_COND_DISP_STRUCT_all struct, AMS_QUERY_ALIAS alias, ams_list_src_types src_types
1513: where struct.query_condition_id = p_cond_id
1514: and struct.token_type = 'ATTRIBUTE'
1515: and alias.query_alias_id = struct.query_alias_id
1516: and alias.OBJECT_NAME = src_types.SOURCE_TYPE_CODE;
1572: where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
1573: and query_alias_id is not null) alias1,
1574: (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
1575: 'A'||to_char(rownum) line_num
1576: from AMS_QUERY_ALIAS alias,ams_list_src_types types
1577: where alias.template_id = p_template_id
1578: and alias.object_name = types.source_type_code
1579: order by alias.query_alias_id) alias2
1580: where alias1.query_alias_id = alias2.query_alias_id;
1826:
1827: --Operand
1828: CURSOR C_left_oprand (p_cond_id NUMBER) IS
1829: select struct.non_variant_value attr_name, source_object_name table_name, src_types.list_source_type_id
1830: from AMS_QUERY_COND_DISP_STRUCT_all struct, AMS_QUERY_ALIAS alias, ams_list_src_types src_types
1831: where struct.query_condition_id = p_cond_id
1832: and struct.token_type = 'ATTRIBUTE'
1833: and alias.query_alias_id = struct.query_alias_id
1834: and alias.OBJECT_NAME = src_types.SOURCE_TYPE_CODE;
1865: where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
1866: and query_alias_id is not null) alias1,
1867: (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
1868: 'A'||to_char(rownum) line_num
1869: from AMS_QUERY_ALIAS alias,ams_list_src_types types
1870: where alias.template_id = p_template_id
1871: and alias.object_name = types.source_type_code
1872: order by alias.query_alias_id) alias2
1873: where alias1.query_alias_id = alias2.query_alias_id;