eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
[Home] [Help]
PACKAGE: APPS.FEM_CONDITIONS_API

Source


1 package fem_conditions_api AS
2 --$Header: FEMCONDS.pls 120.2.12010000.2 2008/08/28 08:37:52 lkiran ship $
3 
4 PROCEDURE DISPLAY_CONDITION_PREDICATE(
5    x_err_code OUT NOCOPY NUMBER,
6    x_err_msg OUT NOCOPY VARCHAR2,
7    p_condition_obj_id IN NUMBER,
8    p_rule_effective_date IN VARCHAR2);
9 
10 /*PROCEDURE DISPLAY_CONDITION_PREDICATE(
11    p_api_version          IN NUMBER,
12    p_init_msg_list        IN VARCHAR2,
13    p_commit               IN VARCHAR2,
14    p_encoded              IN VARCHAR2,
15    p_condition_obj_id     IN NUMBER,
16    p_rule_effective_date  IN VARCHAR2,
17    x_return_status        OUT NOCOPY VARCHAR2,
18    x_msg_count            OUT NOCOPY NUMBER,
19    x_msg_data             OUT NOCOPY VARCHAR2);*/
20 
21 
22 PROCEDURE GENERATE_CONDITION_PREDICATE(
23    x_err_code OUT NOCOPY NUMBER,
24    x_err_msg OUT NOCOPY VARCHAR2,
25    p_condition_obj_id IN NUMBER,
26    p_rule_effective_date IN VARCHAR2,
27    p_input_fact_table_name IN VARCHAR2,
28    p_table_alias IN VARCHAR2,
29    p_display_predicate IN VARCHAR2,
30    p_return_predicate_type IN VARCHAR2,
31    p_logging_turned_on IN VARCHAR2,
32    x_predicate_string OUT NOCOPY LONG);
33 
34 
35 ------------------------
36 --  Package Constants --
37 ------------------------
38 
39 g_false            CONSTANT  VARCHAR2(1)  := FND_API.G_FALSE;
40 g_true             CONSTANT  VARCHAR2(1)  := FND_API.G_TRUE;
41 g_success          CONSTANT  VARCHAR2(1)  := FND_API.G_RET_STS_SUCCESS;
42 g_error            CONSTANT  VARCHAR2(1)  := FND_API.G_RET_STS_ERROR;
43 g_unexp            CONSTANT  VARCHAR2(1)  := FND_API.G_RET_STS_UNEXP_ERROR;
44 g_api_version      CONSTANT  NUMBER       := 1.0;
45 
46 PROCEDURE GENERATE_CONDITION_PREDICATE(
47    p_api_version     IN NUMBER     DEFAULT g_api_version,
48    p_init_msg_list   IN VARCHAR2   DEFAULT g_false,
49    p_commit          IN VARCHAR2   DEFAULT g_false,
50    p_encoded         IN VARCHAR2   DEFAULT g_true,
51    p_condition_obj_id IN NUMBER,
52    p_rule_effective_date IN VARCHAR2,
53    p_input_fact_table_name IN VARCHAR2,
54    p_table_alias IN VARCHAR2,
55    p_display_predicate IN VARCHAR2,
56    p_return_predicate_type IN VARCHAR2,
57    p_logging_turned_on IN VARCHAR2,
58    x_return_status  OUT NOCOPY VARCHAR2,
59    x_msg_count      OUT NOCOPY NUMBER,
60    x_msg_data       OUT NOCOPY VARCHAR2,
61    x_predicate_string OUT NOCOPY LONG);
62 
63 /******Mapping By Dimension Type Support (Bug 4059078)******/
64 PROCEDURE GENERATE_CONDITION_PREDICATE(
65    p_api_version     IN NUMBER     DEFAULT g_api_version,
66    p_init_msg_list   IN VARCHAR2   DEFAULT g_false,
67    p_commit          IN VARCHAR2   DEFAULT g_false,
68    p_encoded         IN VARCHAR2   DEFAULT g_true,
69    p_condition_obj_id IN NUMBER,
70    p_rule_effective_date IN VARCHAR2,
71    p_input_fact_table_name IN VARCHAR2,
72    p_table_alias IN VARCHAR2,
73    p_display_predicate IN VARCHAR2,
74    p_return_predicate_type IN VARCHAR2,
75    p_logging_turned_on IN VARCHAR2,
76    p_by_dimension_column IN VARCHAR2,
77    p_by_dimension_id  IN NUMBER,
78    p_by_dimension_value IN VARCHAR2,
79    x_return_status  OUT NOCOPY VARCHAR2,
80    x_msg_count      OUT NOCOPY NUMBER,
81    x_msg_data       OUT NOCOPY VARCHAR2,
82    x_predicate_string OUT NOCOPY LONG);
83 
84 /****** API's for use by Condition VO's (Bug 5451847) ******/
85 FUNCTION Get_Dim_Member_Display_Code (
86   p_dimension_id                  in number
87   ,p_member_id                    in varchar2
88 ) RETURN varchar2;
89 
90 FUNCTION Get_Dim_Member_Name (
91   p_dimension_id                  in number
92   ,p_member_id                    in varchar2
93 ) RETURN varchar2;
94 
95 /*============================================================================+
96  | PROCEDURE
97  |   Generate_Dim_Hier_Query
98  |
99  | DESCRIPTION
100  |   This procedure returns a query string for finding a list of nodes in a
101  |   hierarchy definition based on the passed hierarchy object id,
102  |   effective date, node id (and value set id for VSR dimensions), and
103  |   relation code.
104  |
105  |   All passed parameters are checked to ensure they are valid.
106  |
107  |  SCOPE - PUBLIC
108  +============================================================================*/
109 
110 PROCEDURE Generate_Dim_Hier_Query (
111   p_api_version                   in number   default g_api_version
112   ,p_init_msg_list                in varchar2 default g_false
113   ,p_commit                       in varchar2 default g_false
114   ,p_encoded                      in varchar2 default g_true
115   ,x_return_status                out nocopy varchar2
116   ,x_msg_count                    out nocopy number
117   ,x_msg_data                     out nocopy varchar2
118   ---------------------------------------------------
119   ,p_dimension_id                 in number
120   ,p_hierarchy_object_id          in number
121   ,p_effective_date               in varchar2
122   ,p_relation_code                in varchar2
123   ,p_node_id                      in varchar2
124   ,p_value_set_id                 in number default null
125   ,x_query_string                 out nocopy long
126 );
127 
128 
129 ------------------------
130 --  Standard Types --
131 ------------------------
132 DEF_OBJECT_ID   FEM_OBJECT_CATALOG_B.OBJECT_ID%TYPE;
133 DEF_OBJ_DEF_ID  FEM_OBJECT_DEFINITION_B.OBJECT_DEFINITION_ID%TYPE;
134 DEF_OBJECT_NAME FEM_OBJECT_CATALOG_VL.OBJECT_NAME%TYPE;
135 DEF_FOLDER_NAME FEM_FOLDERS_VL.FOLDER_NAME%TYPE;
136 
137 ------------------------
138 --  Exceptions --
139 ------------------------
140 USER_EXCEPTION EXCEPTION;
141 NO_CONDITION_EXCEPTION EXCEPTION;
142 INVALID_FACT_TABLE_EXCEPTION EXCEPTION;
143 NO_FACT_TABLE_EXCEPTION EXCEPTION;
144 HIER_FLATTENED_EXCEPTION EXCEPTION;
145 NO_VALID_COMPONENTS_EXCEPTION EXCEPTION;
146 NO_COMPONENTS_EXCEPTION EXCEPTION;
147 INVALID_DIM_COMP_EXCEPTION EXCEPTION;
148 
149 ------------------------
150 --  Messages --
151 ------------------------
152 G_INVALID_COLUMN CONSTANT VARCHAR2(30)          := 'FEM_COND_API_INVALID_COLUMN';
153 G_INVALID_OPERATOR CONSTANT VARCHAR2(30)        := 'FEM_COND_API_INVALID_OPERATOR';
154 G_INVALID_NODE CONSTANT VARCHAR2(30)            := 'FEM_COND_API_INVALID_NODE';
155 G_INVALID_RELATION CONSTANT VARCHAR2(30)        := 'FEM_COND_API_INVALID_RELATION';
156 G_INVALID_DIMENSION CONSTANT VARCHAR2(30)       := 'FEM_COND_API_INVALID_DIM_COL';
157 G_NO_VERSION CONSTANT VARCHAR2(30)              := 'FEM_COND_API_NO_DEFINITION';
158 G_INVALID_TABLE CONSTANT VARCHAR2(30)           := 'FEM_COND_API_INVALID_TABLE';
159 G_NO_TABLE CONSTANT VARCHAR2(30)                := 'FEM_COND_API_NO_TABLE';
160 G_HIER_FLATTENED CONSTANT VARCHAR2(30)          := 'FEM_COND_API_HIER_FLATTENED';
161 G_NO_VALID_COMPS CONSTANT VARCHAR2(30)          := 'FEM_COND_API_NO_VALID_COMPS';
162 G_NO_COMPONENTS CONSTANT VARCHAR2(30)           := 'FEM_COND_API_NO_COMPS';
163 G_UNHANDLED_EXCEPTION CONSTANT VARCHAR2(30)     := 'FEM_COND_API_UNHANDLED_ERROR';
164 G_INVALID_COMP_DIMENSION CONSTANT VARCHAR2(30)  := 'FEM_COND_API_INVALID_COMP_DIM';
165 G_NULL_PARAM_VALUE_ERR CONSTANT VARCHAR2(30)    := 'FEM_NULL_PARAM_VALUE_ERR';
166 G_BAD_DIM_ID_ERR CONSTANT VARCHAR2(30)          := 'FEM_BAD_DIM_ID_ERR';
167 G_DIM_BAD_DIM_LABEL CONSTANT VARCHAR2(30)       := 'FEM_DIM_BAD_DIM_LABEL';
168 G_INVALID_DIM_COMPONENT CONSTANT VARCHAR2(30)   := 'FEM_COND_API_INVALID_DIM_COMP';
169 
170 ------------------------
171 --  Tokens --
172 ------------------------
173 G_COLUMN_TOKEN CONSTANT VARCHAR2(30) := 'COLUMN_NAME';
174 G_TABLE_TOKEN CONSTANT VARCHAR2(30) := 'TABLE_NAME';
175 G_OPERATOR_TOKEN CONSTANT VARCHAR2(30) := 'OPERATOR';
176 G_CONDITION_TOKEN CONSTANT VARCHAR2(30) := 'CONDITION';
177 G_COMPONENT_TOKEN CONSTANT VARCHAR2(30) := 'COMPONENT';
178 G_NODE_TOKEN CONSTANT VARCHAR2(30) := 'NODE';
179 G_HIERARCHY_TOKEN CONSTANT VARCHAR2(30) := 'HIERARCHY';
180 G_RELATION_TOKEN CONSTANT VARCHAR2(30) := 'RELATION';
181 G_EFFECTIVE_DATE_TOKEN CONSTANT VARCHAR2(30) := 'EFFECTIVE_DATE';
182 G_COMP_DIM_TOKEN CONSTANT VARCHAR2(30) := 'COMP_DIM';
183 G_SQL_ERR_TOKEN CONSTANT VARCHAR2(30) := 'SQLERRM';
184 G_PARAM_TOKEN CONSTANT VARCHAR2(30) := 'PARAM';
185 G_DIM_ID_TOKEN CONSTANT VARCHAR2(30) := 'DIM_ID';
186 G_DIM_LABEL_TOKEN CONSTANT VARCHAR2(30) := 'DIM_LABEL';
187 
188 ------------------------
189 --  PL/SQL Tables --
190 ------------------------
191 TYPE DATA_STEP_VALUES_REC IS RECORD(
192    OPERATOR_CODE VARCHAR2(30),
193    OPERATOR_VALUE VARCHAR2(30),
194    VALUE VARCHAR2(255),
195    MAX_RANGE_VALUE VARCHAR2(255));
196 
197 TYPE DATA_STEP_VALUES_TAB IS TABLE OF DATA_STEP_VALUES_REC
198    INDEX BY BINARY_INTEGER;
199 
200 TYPE DATA_DIM_STEP_PREDICATE_REC IS RECORD(
201    DATA_DIM_COMPONENT_DEF_ID NUMBER,
202    STEP_SPECIFIC_PREDICATE LONG);
203 
204 TYPE DATA_DIM_STEP_PREDICATE_TAB IS TABLE OF DATA_DIM_STEP_PREDICATE_REC
205    INDEX BY BINARY_INTEGER;
206 
207 TYPE INVALID_DATA_COMPONENTS_REC IS RECORD(
208    COMPONENT_OBJECT_NAME DEF_OBJECT_NAME%TYPE,
209    COMPONENT_FOLDER_NAME DEF_FOLDER_NAME%TYPE,
210    INVALID_TABLE_NAME VARCHAR2(30),
211    INVALID_FIRST_COLUMN VARCHAR2(30),
212    INVALID_SECOND_COLUMN VARCHAR2(30));
213 
214 TYPE INVALID_DATA_COMPONENTS_TAB IS TABLE OF INVALID_DATA_COMPONENTS_REC
215    INDEX BY BINARY_INTEGER;
216 
217 TYPE COMP_CUR_TYPE IS REF CURSOR;
218 
219 TYPE INVALID_DIM_COMPONENTS_REC IS RECORD(
220    COMPONENT_OBJECT_NAME DEF_OBJECT_NAME%TYPE,
221    COMPONENT_FOLDER_NAME DEF_FOLDER_NAME%TYPE,
222    INVALID_TABLE_NAME VARCHAR2(30),
223    INVALID_DIMENSION_COLUMN VARCHAR2(30));
224 
225 TYPE INVALID_DIM_COMPONENTS_TAB IS TABLE OF INVALID_DIM_COMPONENTS_REC
226    INDEX BY BINARY_INTEGER;
227 
228 
229 ------------------------
230 --  Constants --
231 ------------------------
232 FEM_APP_ID CONSTANT NUMBER	 := 274;
233 FEM_APP CONSTANT VARCHAR2(5) := 'FEM';
234 
235 G_OPEN CONSTANT VARCHAR2(1)   := '(';
236 G_CLOSE CONSTANT VARCHAR2(1)  := ')';
237 G_PERIOD	CONSTANT VARCHAR2(1)      := '.';
238 G_SPACE CONSTANT VARCHAR2(1)        := ' ';
239 G_2_SPACE CONSTANT VARCHAR2(2)        := '  ';
240 
241 --Operands--
242 G_OR  	CONSTANT VARCHAR2(3)          := 'OR';
243 G_AND 	CONSTANT VARCHAR2(3)          := 'AND';
244 
245 --Operators--
246 G_GREATER  CONSTANT VARCHAR2(30)     := FEM_UTILS.getLookupMeaning
247 				(p_Application_ID => FEM_APP_ID
248    	                        ,p_Lookup_Type => 'FEM_CONDITION_OPERATOR'
249 			        ,p_Lookup_Code => 'GREATER');
250 G_LESSER   CONSTANT VARCHAR2(30)     := FEM_UTILS.getLookupMeaning
251 				(p_Application_ID => FEM_APP_ID
252                                 ,p_Lookup_Type => 'FEM_CONDITION_OPERATOR'
253                                 ,p_Lookup_Code => 'LESSER');
254 G_EQUAL	   CONSTANT VARCHAR2(30)	     := FEM_UTILS.getLookupMeaning
255                                 (p_Application_ID => FEM_APP_ID
256                                 ,p_Lookup_Type => 'FEM_CONDITION_OPERATOR'
257                                 ,p_Lookup_Code => 'EQUAL');
258 G_GREATER_EQUAL CONSTANT VARCHAR2(30) := FEM_UTILS.getLookupMeaning
259                                 (p_Application_ID => FEM_APP_ID
260                                 ,p_Lookup_Type => 'FEM_CONDITION_OPERATOR'
261                                 ,p_Lookup_Code => 'GREATER_EQUAL');
262 G_LESSER_EQUAL	CONSTANT VARCHAR2(30) := FEM_UTILS.getLookupMeaning
263                                 (p_Application_ID => FEM_APP_ID
264                                 ,p_Lookup_Type => 'FEM_CONDITION_OPERATOR'
265                                 ,p_Lookup_Code => 'LESSER_EQUAL');
266 G_NOT_EQUAL  CONSTANT VARCHAR2(30)    := FEM_UTILS.getLookupMeaning
267                                 (p_Application_ID => FEM_APP_ID
268                                 ,p_Lookup_Type => 'FEM_CONDITION_OPERATOR'
269                                 ,p_Lookup_Code => 'NOT_EQUAL');
270 G_BETWEEN    CONSTANT VARCHAR2(30)    := FEM_UTILS.getLookupMeaning
271                                 (p_Application_ID => FEM_APP_ID
272                                 ,p_Lookup_Type => 'FEM_CONDITION_RANGE_OPERATOR'
273                                 ,p_Lookup_Code => 'BETWEEN');
274 G_NOT_BETWEEN  CONSTANT VARCHAR2(30)  := FEM_UTILS.getLookupMeaning
275                                 (p_Application_ID => FEM_APP_ID
276                                 ,p_Lookup_Type => 'FEM_CONDITION_RANGE_OPERATOR'
277                                 ,p_Lookup_Code => 'NOT_BETWEEN');
278 G_NODE  CONSTANT VARCHAR2(30)       := FEM_UTILS.getLookupMeaning
279                                 (p_Application_ID => FEM_APP_ID
280                                 ,p_Lookup_Type => 'FEM_COND_DIM_HIER_RELATIONS'
281                                 ,p_Lookup_Code => 'NODE');
282 G_DESC  CONSTANT VARCHAR2(30)       := FEM_UTILS.getLookupMeaning
283                                 (p_Application_ID => FEM_APP_ID
284                                 ,p_Lookup_Type => 'FEM_COND_DIM_HIER_RELATIONS'
285                                 ,p_Lookup_Code => 'DESC_OF');
286 G_NODE_DESC  CONSTANT VARCHAR2(30)       := FEM_UTILS.getLookupMeaning
287                                 (p_Application_ID => FEM_APP_ID
288                                 ,p_Lookup_Type => 'FEM_COND_DIM_HIER_RELATIONS'
289                                 ,p_Lookup_Code => 'NODE_AND_DESC');
290 G_LAST_DESC  CONSTANT VARCHAR2(30)       := FEM_UTILS.getLookupMeaning
291                                 (p_Application_ID => FEM_APP_ID
292                                 ,p_Lookup_Type => 'FEM_COND_DIM_HIER_RELATIONS'
293                                 ,p_Lookup_Code => 'LAST_DESC_OF');
294 
295 --Data Component Step Types--
296 G_DATA_ANOTHER_COL CONSTANT VARCHAR2(30) := 'DATA_ANOTHER_COL';
297 G_SPECIFIC_VALUE   CONSTANT VARCHAR2(30) := 'DATA_SPECIFIC';
298 G_RANGE_OF_VALUES  CONSTANT VARCHAR2(30) := 'DATA_RANGE';
299 
300 --Data Component SQL--
301 G_QUERY_FOR_DATA_CMPS CONSTANT LONG :=
302 
303                     'select'
304                   ||' a.cond_component_obj_id  as COMPONENT_OBJECT_ID'
305                   ||',a.data_dim_flag as COMPONENT_FLAG'
306                   ||',c1.object_definition_id  as COMPONENT_OBJECT_DEF_ID'
307                   ||',b.object_type_code       as COMPONENT_TYPE_CODE'
308 	            ||',d.table_name             as FACT_TABLE_NAME'
309                   ||' from'
310                   ||' fem_cond_components a'
311                   ||',fem_object_catalog_b b'
312                   ||',fem_object_definition_b c1'
313                   ||',fem_cond_data_cmp_tables d'
314                   ||' where'
315                   ||'    a.condition_obj_def_id = :Cond_Obj_Def_ID '
316                   ||' and b.object_id = a.cond_component_obj_id'
317                   ||' and b.object_id = c1.object_id'
318                   ||' and a.data_dim_flag = ''T'''
319                   ||' and d.cond_data_cmp_obj_def_id = c1.object_definition_id'
320                   ||' and d.table_name = :Table_Name';
321 
322 --(Display) Data Component SQL--
323 G_QUERY_FOR_DISPLAY_DATA_CMPS CONSTANT LONG :=
324                     'select'
325                   ||' a.cond_component_obj_id  as COMPONENT_OBJECT_ID'
326                   ||',a.data_dim_flag as COMPONENT_FLAG'
327                   ||',c1.object_definition_id  as COMPONENT_OBJECT_DEF_ID'
328                   ||',b.object_type_code       as COMPONENT_TYPE_CODE'
329 	            ||',d.table_name             as FACT_TABLE_NAME'
330                   ||' from'
331                   ||' fem_cond_components a'
332                   ||',fem_object_catalog_b b'
333                   ||',fem_object_definition_b c1'
334                   ||',fem_cond_data_cmp_tables d'
335                   ||' where'
336                   ||'    a.condition_obj_def_id = :Cond_Obj_Def_ID '
337                   ||' and b.object_id = a.cond_component_obj_id'
338                   ||' and b.object_id = c1.object_id'
339                   ||' and a.data_dim_flag = ''T'''
340                   ||' and d.cond_data_cmp_obj_def_id = c1.object_definition_id';
341 
342 G_UNION CONSTANT VARCHAR2(10) := ' UNION ';
343 
344 --Dimension Component SQL--
345 G_QUERY_FOR_DIM_CMPS CONSTANT LONG :=
346                     'SELECT'
347                   ||' c1.object_id as COMPONENT_OBJECT_ID,'
348                   ||' a.data_dim_flag AS COMPONENT_FLAG,'
349                   ||' d1.object_definition_id AS COMPONENT_OBJ_DEF_ID,'
350                   ||' c1.object_type_code AS COMPONENT_TYPE_CODE,'
351                   ||' NULL AS FACT_TABLE_NAME'
352                   ||' FROM fem_cond_components a,'
353                   ||' fem_object_catalog_b c,'
354                   ||' fem_object_definition_b d,'
355                   ||' fem_object_catalog_b c1,'
356                   ||' fem_object_definition_b d1'
357                   ||' where a.condition_obj_def_id = :condition_obj_def_id'
358                   ||' and a.condition_obj_def_id = d.object_definition_id'
359                   ||' and c.object_id = d.object_id'
360                   ||' and a.cond_component_obj_id = c1.object_id'
361                   ||' and c1.object_id = d1.object_id'
362                   ||' and a.data_dim_flag IN (''D'', ''V'')';
363 
364 END fem_conditions_api;