1 PACKAGE MSD_DEM_DEMANTRA_UTILITIES AUTHID CURRENT_USER AS
2 /* $Header: msddemdus.pls 120.3.12020000.2 2012/11/15 14:09:53 nallkuma ship $ */
3
4
5
6 /*** CONSTANTS - BEGIN ***/
7 /* MSD DEM Debug Profile Value */
8 C_MSD_DEM_DEBUG VARCHAR2(1) := nvl( fnd_profile.value( 'MSD_DEM_DEBUG_MODE'), 'N');
9
10 /* Demantra Schema Name */
11 C_MSD_DEM_SCHEMA VARCHAR2(100) := nvl( fnd_profile.value( 'MSD_DEM_SCHEMA' ) , 'DMTRA_TEMPLATE');
12
13 /*** CONSTANTS - END ***/
14
15
16
17
18 /*** PUBLIC PROCEDURES - BEGIN ***
19 * LOG_MESSAGE
20 * LOG_DEBUG
21 */
22
23
24 /* DUMMY PROCEDURE
25 * This procedure logs a given message text in ???
26 * param: p_buff - message text to be logged.
27 */
28 PROCEDURE LOG_MESSAGE ( p_buff IN VARCHAR2);
29
30
31 /* DUMMY PROCEDURE
32 * This procedure logs a given debug message text in ???
33 * only if the profile MSD_DEM_DEBUG is set to 'Yes'.
34 * param: p_buff - debug message text to be logged.
35 */
36 PROCEDURE LOG_DEBUG ( p_buff IN VARCHAR2);
37
38
39
40 /*** PUBLIC PROCEDURES - END ***/
41
42
43
44
45 /*** PUBLIC FUNCTIONS - BEGIN ***
46 * GET_SEQUENCE_NEXTVAL
47 * CREATE_SERIES
48 * DELETE_SERIES
49 * ADD_SERIES_TO_COMPONENT
50 * CREATE_INTEGRATION_INTERFACE
51 * DELETE_INTEGRATION_INTERFACE
52 * CREATE_DATA_PROFILE
53 * ADD_SERIES_TO_PROFILE
54 * ADD_LEVEL_TO_PROFILE
55 * CREATE_WORKFLOW_SCHEMA
56 * DELETE_WORKFLOW_SCHEMA
57 * GET_DEMANTRA_SCHEMA
58 * CREATE_DEMANTRA_DB_OBJECT
59 * DROP_DEMANTRA_DB_OBJECT
60 * CREATE_SYNONYM_IN_EBS
61 */
62
63
64 /*
65 * This function calls the GET_SEQ_NEXTVAL procedure in the Demantra schema.
66 * The function returns -
67 * n : next value for the given sequence
68 * -1 : If table is not present
69 * -2 : If column is not present
70 * -3 : Any other error
71 */
72 FUNCTION GET_SEQUENCE_NEXTVAL (
73 p_table_name IN VARCHAR2,
74 p_column_name IN VARCHAR2,
75 p_seq_name IN VARCHAR2)
76 RETURN NUMBER;
77
78
79 /*
80 * This function creates the series given in Demantra schema.
81 * The function returns -
82 * n : The series id in case of success
83 * -1 : in case of error
84 * -3 : Unable to set demantra schema name
85 * -4 : Some of the mandatory parameters are NULL.
86 * -5 : Unable to get next sequence value for forecast type id
87 * -6 : Column already present in the table
88 */
89 FUNCTION CREATE_SERIES (
90 p_computed_name IN VARCHAR2,
91 p_exp_template IN VARCHAR2,
92 p_computed_title IN VARCHAR2,
93 p_sum_func IN VARCHAR2,
94 p_scaleble IN NUMBER,
95 p_editable IN NUMBER,
96 p_is_proportion IN NUMBER,
97 p_dbname IN VARCHAR2,
98 p_hint_message IN VARCHAR2,
99 p_hist_pred_type IN NUMBER,
100 p_data_table_name IN VARCHAR2,
101 p_prop_calc_series IN NUMBER,
102 p_base_level IN NUMBER,
103 p_expression_type IN NUMBER,
104 p_int_aggr_func IN VARCHAR2,
105 p_aggr_by IN NUMBER,
106 p_preservation_type IN NUMBER,
107 p_move_preservation_type IN NUMBER,
108 p_data_type IN NUMBER)
109 RETURN NUMBER;
110
111
112 /*
113 * This function deletes the series given in Demantra Schema.
114 * The function returns -
115 * n : The series id in case of success
116 * -1 : in case of error
117 * -2 : if series is not present
118 * -3 : Unable to set demantra schema name
119 */
120 FUNCTION DELETE_SERIES ( p_computed_name IN VARCHAR2 )
121 RETURN NUMBER;
122
123
124 /*
125 * This function adds the given series to the given component and also
126 * to the user who owns the component.
127 * The function returns -
128 * 0 : In case of success
129 * -1 : in case of error
130 * -3 : Unable to set demantra schema name
131 */
132 FUNCTION ADD_SERIES_TO_COMPONENT (
133 p_series_id IN NUMBER,
134 p_component_id IN NUMBER)
135 RETURN NUMBER;
136
137
138 /*
139 * This function creates an integration interface given name and description
140 * the the owning user.
141 * The function returns -
142 * n : integration interface id
143 * -1 : in case of error
144 * -3 : Unable to set demantra schema name
145 * -4 : If an integration interface with the same name already exists
146 * -5 : Unable to get next sequence value for integration interface id
147 */
148 FUNCTION CREATE_INTEGRATION_INTERFACE (
149 p_name IN VARCHAR2,
150 p_description IN VARCHAR2,
151 p_user_id IN NUMBER)
152 RETURN NUMBER;
153
154
155 /*
156 * This function creates an integration interface given name and description
157 * the the owning user.
158 * The function returns -
159 * 0 : in case of success (includes absence of the given integration interface name)
160 * -1 : in case of error
161 * -3 : Unable to set demantra schema name
162 */
163 FUNCTION DELETE_INTEGRATION_INTERFACE (p_name IN VARCHAR2)
164 RETURN NUMBER;
165
166
167 /*
168 * This function creates a data profile
169 * The function returns -
170 * n : the data profile id
171 * -1 : in case of error
172 * -3 : Unable to set demantra schema name
173 */
174 FUNCTION CREATE_DATA_PROFILE (
175 p_transfer_id IN NUMBER,
176 p_view_name IN VARCHAR2,
177 p_table_name IN VARCHAR2,
178 p_view_type IN NUMBER,
179 p_use_real_proportion IN NUMBER,
180 p_insertnewcombinations IN NUMBER,
181 p_insertforecasthorizon IN NUMBER,
182 p_query_name IN VARCHAR2,
183 p_description IN VARCHAR2,
184 p_time_res_id IN NUMBER,
185 p_from_date IN DATE,
186 p_until_date IN DATE,
187 p_relative_date IN NUMBER,
188 p_relative_from_date IN NUMBER,
189 p_relative_until_date IN NUMBER,
190 p_integration_type IN NUMBER,
191 p_export_type IN NUMBER
192 )
193 RETURN NUMBER;
194
195
196 /*
197 * This function adds the given series to the data profile.
198 * The function returns -
199 * 0 : in case of success
200 * -1 : in case of error
201 * -3 : Unable to set demantra schema name
202 */
203 FUNCTION ADD_SERIES_TO_PROFILE (
204 p_data_profile_id IN NUMBER,
205 p_series_id IN NUMBER )
206 RETURN NUMBER;
207
208
209 /*
210 * This function adds the given level to the data profile.
211 * The function returns -
212 * 0 : in case of success
213 * -1 : in case of error
214 * -3 : Unable to set demantra schema name
215 */
216 FUNCTION ADD_LEVEL_TO_PROFILE (
217 p_data_profile_id IN NUMBER,
218 p_level_id IN NUMBER,
219 p_lorder IN NUMBER )
220 RETURN NUMBER;
221
222
223 /*
224 * This function creates the given workflow schema.
225 * The function returns -
226 * n : the schema id
227 * -1 : in case of error
228 * -3 : Unable to set demantra schema name
229 */
230 FUNCTION CREATE_WORKFLOW_SCHEMA (
231 p_schema_name IN VARCHAR2,
232 p_schema_data IN VARCHAR2,
233 p_owner_id IN NUMBER,
234 p_creation_date IN DATE,
235 p_modified_date IN DATE,
236 p_schema_type IN NUMBER )
237 RETURN NUMBER;
238
239
240 /*
241 * This function deletes the workflow schema given.
242 * The function returns -
243 * 0 : in case of success
244 * -1 : in case of error
245 * -3 : Unable to set demantra schema name
246 */
247 FUNCTION DELETE_WORKFLOW_SCHEMA ( p_schema_name IN VARCHAR2 )
248 RETURN NUMBER;
249
250
251 /*
252 * This function gets the demantra schema name.
253 * The function returns -
254 * <demantra schema name> : if demantra is installed.
255 * null : if demantra is not installed.
256 */
257 FUNCTION GET_DEMANTRA_SCHEMA
258 RETURN VARCHAR2;
259
260
261 /*
262 * This function creates the given db object.
263 * The function returns -
264 * 0 : in case of success
265 * -1 : in case of error
266 * -3 : Unable to set demantra schema name
267 */
268 FUNCTION CREATE_DEMANTRA_DB_OBJECT (
269 p_object_type IN VARCHAR2,
270 p_object_name IN VARCHAR2,
271 p_create_sql IN VARCHAR2)
272 RETURN NUMBER;
273
274
275 /*
276 * This function drop the given demantra db object.
277 * The function returns -
278 * 0 : in case of success/object not present
279 * -1 : in case of error
280 * -3 : Unable to set demantra schema name
281 */
282 FUNCTION DROP_DEMANTRA_DB_OBJECT (
283 p_object_type IN VARCHAR2,
284 p_object_name IN VARCHAR2 )
285 RETURN NUMBER;
286
287
288 /*
289 * This function work only if Demantra and APS are on the same DB instance.
290 * This function creates a synonym in the APPS schema using the given sql
291 * script.
292 * The function returns -
293 * 0 : in case of success
294 * -1 : in case of error
295 * -3 : Unable to set demantra schema name
296 */
297 FUNCTION CREATE_SYNONYM_IN_EBS (
298 p_object_name IN VARCHAR2,
299 p_create_replace_sql IN VARCHAR2)
300 RETURN NUMBER;
301
302 /*
303 * This function will insert rows into the TRANSFER_QUERY_INTERSECTIONS table
304 * against the given data profiles.
305 * The function returns -
306 * 0 : in case of success
307 * -1 : in case of error
308 * -3 : Unable to set demantra schema name
309 */
310 FUNCTION ADD_INTERSECT_TO_PROFILE (
311 p_data_profile_id IN NUMBER,
312 p_base_level_id IN VARCHAR2)
313 RETURN NUMBER;
314
315
316
317 /*
318 * Update the synonyms MSD_DEM_TRANSFER_LIST and MSD_DEM_TRANSFER_QUERY
319 * to point to the Demantra's tables TRANSFER_LIST and TRANSFER_QUERY
320 * if Demantra is installed.
321 * Sets the profile MSD_DEM_SCHEMA to the Demantra Schema Name
322 * The checks if the table MDP_MATRIX exists in the Demantra Schema
323 */
324
325 PROCEDURE UPDATE_SYNONYMS (
326 errbuf OUT NOCOPY VARCHAR2,
327 retcode OUT NOCOPY VARCHAR2,
328 p_demantra_schema IN VARCHAR2 DEFAULT NULL);
329
330 /*** PUBLIC FUNCTIONS - END ***/
331
332
333 END MSD_DEM_DEMANTRA_UTILITIES;
[Home] [Help]