1 Package EDW_GEN_VIEW AUTHID DEFINER AS
2 /*$Header: EDWVGENS.pls 120.1 2005/06/13 12:56:16 aguwalan noship $*/
3
4
5 cursor c_getAttributeMappings(p_obj_name in varchar2, p_instance in varchar2, p_level IN VARCHAR2) IS
6 SELECT attribute_name, source_attribute, datatype FROM edw_attribute_mappings
7 WHERE object_short_name = p_obj_name and upper(flex_flag) = 'N'
8 AND instance_code = p_instance
9 AND decode(level_name, null, '000', 'null','000',level_name) = nvl(p_level, '000')
10 AND attribute_name NOT IN /* for multiple columns mapping to single target col*/
11 (SELECT attribute_name FROM edw_attribute_mappings
12 WHERE object_short_name = p_obj_name and upper(flex_flag) = 'N'
13 AND instance_code = p_instance
14 AND decode(level_name, null, '000', 'null', '000', level_name) = nvl(p_level, '000')
15 GROUP BY attribute_name having count(attribute_name) > 1)
16 ORDER BY attribute_name;
17
18 cursor c_getMultiAttributeList(p_obj_name in varchar2, p_instance in varchar2, p_level IN VARCHAR2) IS
19 SELECT distinct attribute_name FROM edw_attribute_mappings
20 WHERE object_short_name = p_obj_name and upper(flex_flag) = 'N'
21 AND instance_code = p_instance
22 AND decode(level_name, null, '000', 'null', '000', level_name) = nvl(p_level, '000')
23 GROUP BY attribute_name having count(attribute_name) > 1
24 ORDER BY attribute_name;
25
26
27 cursor c_getMultiAttributeMappings(p_obj_name in varchar2, p_instance in varchar2, p_level IN VARCHAR2,
28 p_column IN VARCHAR2) IS
29 SELECT source_attribute FROM edw_attribute_mappings
30 WHERE object_short_name = p_obj_name and upper(flex_flag) = 'N'
31 AND instance_code = p_instance
32 AND decode(level_name, null, '000', 'null', '000', level_name)= nvl(p_level, '000')
33 AND attribute_name = p_column;
34
35
36 cursor c_getFlexAttributeMappings(p_obj_name in varchar2, p_instance in varchar2, p_level IN VARCHAR2) IS
37 SELECT distinct(attribute_name) attribute_name, source_view, id_flex_code, datatype, flex_field_type
38 FROM edw_attribute_mappings a, edw_flex_attribute_mappings b
39 WHERE a.object_short_name = p_obj_name
40 AND decode(level_name, null, '000', 'null', '000', level_name) = nvl(p_level, '000')
41 AND a.attr_mapping_pk = b.attr_mapping_fk
42 AND a.instance_code = p_instance
43 ORDER BY attribute_name;
44
45 cursor c_getFactFlexFKMaps(p_obj_name in varchar2, p_instance in varchar2) IS
46 SELECT fk_physical_name
47 from edw_fact_flex_fk_maps a, edw_flex_seg_mappings b
48 where a.fact_short_name = p_obj_name
49 and a.dimension_short_name = b.dimension_short_name
50 and b.instance_code = p_instance
51 and a.enabled_flag = 'Y';
52
53 cursor c_getMultipleMaps(p_obj_name in varchar2, p_instance in varchar2) IS
54 SELECT fk_physical_name
55 from edw_fact_flex_fk_maps a, edw_flex_seg_mappings b
56 where a.fact_short_name = p_obj_name
57 and a.dimension_short_name = b.dimension_short_name
58 and b.instance_code = p_instance
59 and a.enabled_flag = 'Y';
60
61 TYPE tab_att_maps IS TABLE of
62 c_getAttributeMappings%ROWTYPE
63 INDEX BY BINARY_INTEGER;
64
65 TYPE tab_multi_att_maps IS TABLE OF
66 c_getMultiAttributeMappings%ROWTYPE
67 INDEX BY BINARY_INTEGER;
68
69 TYPE tab_multi_att_list IS TABLE OF
70 c_getMultiAttributeList%ROWTYPE
71 INDEX BY BINARY_INTEGER;
72
73 TYPE tab_flex_att_maps IS TABLE of
74 c_getFlexAttributeMappings%ROWTYPE
75 INDEX BY BINARY_INTEGER;
76
77 TYPE tab_fact_flex_fk_maps IS TABLE of
78 c_getFactFlexFKMaps%ROWTYPE
79 INDEX BY BINARY_INTEGER;
80
81 g_source_db_link varchar2(40);
82 g_instance varchar2(120) := null;
83 g_indenting varchar2(30) := '';
84 g_spacing varchar2(10) := ' ';
85 g_apps_schema varchar2(100):= EDW_OWB_COLLECTION_UTIL.get_apps_schema_name;
86 g_version VARCHAR2(10):='11i';
87 g_status_failed_all varchar2(30):= 'FAILED_ALL';
88 g_status_failed_pruned varchar2(30):='FAILED_PRUNED';
89 g_status_generated_all varchar2(30):='GENERATED_ALL';
90 g_status_generated_pruned varchar2(30):='GENERATED_PRUNED';
91 l_file utl_file.file_type;
92 -- spool out the view_text into l_out_file
93 l_out_file utl_file.file_type;
94 g_success boolean := true;
95 g_error varchar2(2000):= null;
96 g_obj_name VARCHAR2(30);
97
98 viewgen_exception EXCEPTION;
99
100
101
102 g_where_clause dbms_sql.varchar2_table;
103
104 Procedure indentBegin;
105 Procedure indentEnd;
106
107 Function getColumnCountForView(view_name in varchar2) RETURN INTEGER;
108 Function getAppsSchema RETURN VARCHAR2;
109 Function getAppsVersion RETURN VARCHAR2;
110 Function formSegmentName(p_prefix IN VARCHAR2,
111 p_segment_name IN VARCHAR2,
112 p_struct_num IN NUMBER,
113 p_Id_Flex_Code VARCHAR2,
114 p_flex_type VARCHAR2) RETURN VARCHAR2;
115
116 Function getContextColForFlex(p_flex in varchar2, p_flex_type IN VARCHAR2) RETURN VARCHAR2;
117
118 PROCEDURE getColumnMaps(object_name IN VARCHAR2, attMaps OUT NOCOPY tab_att_maps, multiAttList OUT NOCOPY tab_multi_att_list, flexMaps OUT NOCOPY tab_flex_att_maps, fkMaps OUT NOCOPY tab_fact_flex_fk_maps, p_level IN VARCHAR2 DEFAULT null);
119
120 Function getFlexPrefix( pViewName IN VARCHAR2, pIdFlexCode IN VARCHAR2) RETURN VARCHAR2;
121
122
123 FUNCTION getDecodeClauseForFlexCol( pSourceView IN VARCHAR2,
124 pAttributeName IN VARCHAR2, pIdFlexCode IN VARCHAR2,
125 pFlexType IN VARCHAR2) RETURN VARCHAR2;
126
127 FUNCTION getNvlClause(p_object IN VARCHAR2, p_level IN VARCHAR2, p_instance IN VARCHAR2,
128 p_column IN VARCHAR2) return VARCHAR2;
129
130
131 Procedure Generate(p_obj_name in varchar2,
132 p_obj_type in varchar2,
133 p_instance in varchar2,
134 p_db_link in varchar2,
135 p_log_dir in varchar2 default null);
136
137 Function getUtlFileDir return VARCHAR2 ;
138
139 Procedure writeLog(p_message IN VARCHAR2);
140 Procedure writeOut(p_message IN VARCHAR2);
141 Procedure writeOutLine(p_message IN VARCHAR2);
142 --Procedure GenerateDepVSView(p_dim_name in varchar2);
143 function getApplsysSchema return varchar2;
144 Procedure BuildViewStmt(p_view_text in varchar2, p_line_num in number);
145 PROCEDURE createView(src_view IN VARCHAR2, view_name IN VARCHAR2);
146 PROCEDURE createLongView(view_name IN VARCHAR2, p_first_line_num IN NUMBER, p_last_line_num IN NUMBER);
147 FUNCTION convertString(p_string IN VARCHAR2) RETURN VARCHAR2;
148
149 Function checkWhereClause(p_value_set_id in NUMBER, p_link in varchar2) return boolean ;
150 End EDW_GEN_VIEW;
[Home] [Help]