[Home] [Help]
189: where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
190: and query_alias_id is not null) alias1,
191: (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
192: 'A'||to_char(rownum) line_num
193: from AMS_QUERY_ALIAS alias,ams_list_src_types types
194: where alias.template_id = p_template_id
195: and alias.object_name = types.source_type_code
196: and types.list_source_type = 'TARGET'
197: order by alias.query_alias_id) alias2
206: where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
207: and query_alias_id is not null) alias1,
208: (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
209: 'A'||to_char(rownum) line_num
210: from AMS_QUERY_ALIAS alias,ams_list_src_types types
211: where alias.template_id = p_template_id
212: and alias.object_name = types.source_type_code
213: and types.list_source_type = 'TARGET'
214: order by alias.query_alias_id) alias2
225: where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
226: and query_alias_id is not null) alias1,
227: (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
228: 'A'||to_char(rownum) line_num
229: from AMS_QUERY_ALIAS alias,ams_list_src_types types
230: where alias.template_id = p_template_id
231: and alias.object_name = types.source_type_code
232: and types.list_source_type = 'TARGET'
233: order by alias.query_alias_id) alias2
236: cursor c_Where_clause is
237: select types.master_source_type_flag,
238: decode(master_source_type_flag,'Y','A'||to_char(rownum)||'.'||source_object_pk_field||' = ',
239: 'A'||to_char(rownum)||'.'||source_object_pk_field ||'(+)')
240: from AMS_QUERY_ALIAS alias,ams_list_src_types types
241: where alias.template_id = p_template_id
242: and alias.object_name = types.source_type_code
243: and types.list_source_type = 'TARGET'
244: order by alias.query_alias_id;
254: where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
255: and query_alias_id is not null) alias1,
256: (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
257: 'A'||to_char(rownum) line_num
258: from AMS_QUERY_ALIAS alias,ams_list_src_types types
259: where alias.template_id = p_template_id
260: and alias.object_name = types.source_type_code
261: and types.list_source_type = 'TARGET'
262: order by alias.query_alias_id) alias2
267: cursor c_index_columns is
268: select column_name from sys.all_tab_columns where table_name = l_view_name
269: and column_name not in (
270: select flds.source_column_name
271: from ams_list_src_fields flds ,AMS_QUERY_ALIAS alias,ams_list_src_types types,
272: ams_query_template_all qtemp
273: where alias.template_id = p_template_id
274: and alias.object_name = types.source_type_code
275: and types.LIST_SOURCE_TYPE_ID = flds.LIST_SOURCE_TYPE_ID
288:
289:
290: cursor c_master_view is
291: select types.source_object_name
292: from AMS_QUERY_ALIAS alias,ams_list_src_types types
293: where alias.template_id = p_template_id
294: and types.master_source_type_flag = 'Y'
295: and alias.object_name = types.source_type_code;
296:
312: where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
313: and query_alias_id is not null) alias1,
314: (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
315: 'A'||to_char(rownum) line_num
316: from AMS_QUERY_ALIAS alias,ams_list_src_types types
317: where alias.template_id = p_template_id
318: and alias.object_name = types.source_type_code
319: and types.list_source_type = 'TARGET'
320: order by alias.query_alias_id) alias2
322:
323: /*
324: cursor c_master_columns is
325: select flds.source_column_name
326: from ams_list_src_fields flds ,AMS_QUERY_ALIAS alias,ams_list_src_types types,
327: ams_query_template_all qtemp
328: where alias.template_id = p_template_id
329: and alias.object_name = types.source_type_code
330: and types.LIST_SOURCE_TYPE_ID = flds.LIST_SOURCE_TYPE_ID
336: -- SOLIN, select primary key only to save table space
337: -- In preview entries, it will join master data source.
338: cursor c_master_columns is
339: --select flds.source_column_name
340: --from ams_list_src_fields flds ,AMS_QUERY_ALIAS alias,ams_list_src_types types,
341: -- ams_query_template_all qtemp
342: --where alias.template_id = p_template_id
343: -- and alias.object_name = types.source_type_code
344: -- and types.LIST_SOURCE_TYPE_ID = flds.LIST_SOURCE_TYPE_ID
348: -- and qtemp.template_id = alias.template_id
349: -- and qtemp.list_src_type = types.source_type_code
350: --Union
351: select typ.SOURCE_OBJECT_PK_FIELD
352: from ams_query_template_all tmp,ams_list_src_types typ
353: where tmp.template_id = p_template_id
354: and tmp.list_src_type = typ.SOURCE_TYPE_CODE;
355:
356:
367: where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
368: and query_alias_id is not null) alias1,
369: (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
370: 'A'||to_char(rownum) line_num,types.list_source_type_id
371: from AMS_QUERY_ALIAS alias,ams_list_src_types types
372: where alias.template_id = p_template_id
373: and alias.object_name = types.source_type_code
374: and types.list_source_type = 'TARGET'
375: order by alias.query_alias_id) alias2
383:
384:
385: cursor c_const_where_cols is
386: select nvl(assoc.master_source_type_pk_column,types.SOURCE_OBJECT_PK_FIELD ),assoc.sub_source_type_pk_column from
387: ams_list_src_type_assocs assoc , ams_list_src_types types
388: where assoc.master_source_type_id = l_master_lstype_id
389: and assoc.sub_source_type_id = l_child_lstype_id
390: and assoc.master_source_type_id = types.list_source_type_id;
391:
395: x_msg_count number;
396: x_msg_data varchar2(2000);
397:
398: cursor c_master_pk is
399: select source_object_pk_field from ams_list_src_types where list_source_type_id = p_template_id;
400:
401: l_master_pk varchar2(60);
402:
403: cursor c_policy_exists is
416: l_mastcol_alias varchar2(100);
417: l_mastobj_name varchar2(100);
418: cursor c_find_mast_alias is
419: select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,'A'||to_char(rownum) line_num
420: from AMS_QUERY_ALIAS alias, ams_list_src_types types
421: where alias.template_id = p_template_id
422: and alias.object_name = types.source_type_code
423: and types.list_source_type = 'TARGET'
424: order by alias.query_alias_id;
424: order by alias.query_alias_id;
425:
426: cursor c_mast_alias is
427: select types.LIST_SOURCE_TYPE_ID, types.source_object_name, types.master_source_type_flag,'A'||to_char(rownum)||'.' line_num
428: from AMS_QUERY_ALIAS alias, ams_list_src_types types
429: where alias.template_id = p_template_id
430: and alias.object_name = types.source_type_code
431: and types.list_source_type = 'TARGET'
432: order by alias.query_alias_id;
447:
448: -- SOLIN, Bug 3696553
449: CURSOR c_get_dblink is
450: select NVL(types.remote_flag, 'N'), database_link
451: from AMS_QUERY_ALIAS alias, ams_list_src_types types
452: where alias.template_id = p_template_id
453: and alias.object_name = types.source_type_code;
454:
455: l_remote_flag VARCHAR2(1);
1387:
1388: -- SOLIN, Bug 3696553
1389: CURSOR c_get_dblink is
1390: select NVL(types.remote_flag, 'N'), database_link
1391: from AMS_QUERY_ALIAS alias, ams_list_src_types types
1392: where alias.template_id = p_template_id
1393: and alias.object_name = types.source_type_code;
1394:
1395: l_remote_flag VARCHAR2(1);
1574:
1575: cursor c_left_oprand (p_cond_id NUMBER) is
1576: select struct.non_variant_value attr_name, source_object_name table_name,src_types.list_source_type_id
1577: -- || alias.ALIAS_SEQ table_name
1578: from AMS_QUERY_COND_DISP_STRUCT_all struct, AMS_QUERY_ALIAS alias, ams_list_src_types src_types
1579: where struct.query_condition_id = p_cond_id
1580: and struct.token_type = 'ATTRIBUTE'
1581: and alias.query_alias_id = struct.query_alias_id
1582: and alias.OBJECT_NAME = src_types.SOURCE_TYPE_CODE;
1638: where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
1639: and query_alias_id is not null) alias1,
1640: (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
1641: 'A'||to_char(rownum) line_num
1642: from AMS_QUERY_ALIAS alias,ams_list_src_types types
1643: where alias.template_id = p_template_id
1644: and alias.object_name = types.source_type_code
1645: order by alias.query_alias_id) alias2
1646: where alias1.query_alias_id = alias2.query_alias_id;
1892:
1893: --Operand
1894: CURSOR C_left_oprand (p_cond_id NUMBER) IS
1895: select struct.non_variant_value attr_name, source_object_name table_name, src_types.list_source_type_id
1896: from AMS_QUERY_COND_DISP_STRUCT_all struct, AMS_QUERY_ALIAS alias, ams_list_src_types src_types
1897: where struct.query_condition_id = p_cond_id
1898: and struct.token_type = 'ATTRIBUTE'
1899: and alias.query_alias_id = struct.query_alias_id
1900: and alias.OBJECT_NAME = src_types.SOURCE_TYPE_CODE;
1931: where query_template_id = p_template_id and token_type in ('ATTRIBUTE','VALUE1','VALUE2')
1932: and query_alias_id is not null) alias1,
1933: (select alias.query_alias_id, types.source_object_name, types.master_source_type_flag,types.source_object_pk_field,
1934: 'A'||to_char(rownum) line_num
1935: from AMS_QUERY_ALIAS alias,ams_list_src_types types
1936: where alias.template_id = p_template_id
1937: and alias.object_name = types.source_type_code
1938: order by alias.query_alias_id) alias2
1939: where alias1.query_alias_id = alias2.query_alias_id;