1 package EGO_ODI_PUB AUTHID CURRENT_USER as
2 /* $Header: EGOODIXS.pls 120.1.12010000.14 2009/11/07 01:33:41 emtapia noship $ */
3 /*==========================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : EGOODIXS.pls |
9 | DESCRIPTION : This file is a packaged procedure for the BOM exploders |
10 | |
11 | |
12 +==========================================================================*/
13
14
15 -- ============================================================================
16 -- Name : Generate_XML
17 -- Description : This procedure generates the ODI output XML from the results
18 -- stored in intermediate table EGO_PUB_WS_FLAT_RECS
19 --
20 -- Parameters:
21 -- IN :
22 -- p_session_id IN NUMBER
23 -- An Unique DB sequence generated at Java wrapper.
24 --
25 -- p_odi_session_id IN VARCHAR2
26 -- ODI generated Unique Session Id for ODI Scenario.
27 --
28 -- p_web_service_name IN VARCHAR2
29 -- The name of the web service for which the output will be
30 -- generated
31 --
32 -- p_xml_root_element IN VARCHAR2
33 -- The name to give to the root element containing the XML output
34 --
35 -- p_transform_xml IN BOOLEAN DEFAULT TRUE
36 -- Tells if the ODI output XML will be transformed into the
37 -- final output XML using the XSL data contained in table
38 -- EGO_ODI_WS_XSL for a given web service.
39 -- ============================================================================
40
41 PROCEDURE Generate_XML(p_session_id IN NUMBER,
42 p_odi_session_id IN NUMBER,
43 p_web_service_name IN VARCHAR2,
44 p_xml_root_element IN VARCHAR2,
45 p_transform_xml IN BOOLEAN DEFAULT TRUE);
46
47
48
49 -- ============================================================================
50 -- Name : Preprocess_Input_Structure
51 -- Description : This procedure will be used to pre-process input xml for Entity
52 -- type as BOM or Structure. This procedure performs the following
53 -- actions:
54 -- 1. Reads the input parameters contained in the XML payload
55 -- stored in table EGO_PUB_WS_PARAMS using session_id and
56 -- stores them in table EGO_PUB_WS_CONFIG.
57 -- 2. Populates ODI input table BOM_ODI_WS_ENTITIES for entity BOM
58 -- based on the invokation type (e.g. batch, list, H-MDM)
59 -- 3. Explodes the bom for all the end-items to process
60 --
61 -- Parameters:
62 -- IN :
63 -- p_session_id IN NUMBER
64 -- An Unique DB sequence generated at Java wrapper.
65 --
66 -- ============================================================================
67
68 PROCEDURE Preprocess_Input_Structure(p_session_id IN NUMBER,
69 p_odi_session_id IN NUMBER);
70
71
72
73 -- ============================================================================
74 -- Name : Preprocess_Input_valueSet
75 -- Description : This procedure will be used to pre-process input xml for Entity
76 -- type as ValueSets.
77 --
78 -- Parameters:
79 -- IN :
80 -- p_session_id IN NUMBER
81 -- An Unique DB sequence generated at Java wrapper.
82 --
83 -- p_odi_session_id IN VARCHAR2
84 -- ODI generated Unique Session Id for ODI Scenario.
85 --
86 --
87 -- ============================================================================
88
89
90 PROCEDURE Preprocess_Input_valueSet( p_session_id IN NUMBER,
91 p_odi_session_id IN NUMBER );
92
93
94
95 -- ============================================================================
96 -- Name : Invocation_Mode
97 -- Description : This procedure will be used to get invocation mode used for xml
98 -- input and will return Batch Id,if invocation mode is 'BATCH'
99 -- It will return Batch Id as -1,If mode is 'LIST'.
100 --
101 -- Parameters:
102 -- IN :
103 -- p_session_id IN NUMBER
104 -- An Unique DB sequence generated at Java wrapper.
105 --
106 -- p_search_str IN VARCHAR2
107 -- Input string passed to find invocation mode for that string
108 -- String can be for ICC,ValueSet etc.
109 --
110 -- OUT :
111 -- x_mode IN NUMBER
112 -- An Unique DB sequence generated at Java wrapper.
113 --
114 -- x_batch_id IN VARCHAR2
115 -- ODI generated Unique Session Id for ODI Scenario.
116
117 -- ============================================================================
118 PROCEDURE Invocation_Mode( p_session_id IN NUMBER,
119 p_search_str IN VARCHAR2,
120 x_mode OUT NOCOPY VARCHAR2,
121 x_batch_id OUT NOCOPY NUMBER ) ;
122
123
124 -- ============================================================================
125 -- Name : Preprocess_Input_ICC
126 -- Description : This procedure will be used to pre-process input xml .
127 --
128 -- Parameters:
129 -- IN :
130 -- p_session_id IN NUMBER
131 -- An Unique DB sequence generated at Java wrapper.
132 --
133 -- p_odi_session_id IN VARCHAR2
134 -- ODI generated Unique Session Id for ODI Scenario.
135 --
136 --
137 -- ============================================================================
138
139 PROCEDURE Preprocess_Input_ICC ( p_session_id IN NUMBER,
140 p_odi_session_id IN NUMBER);
141
142
143
144 -- ============================================================================
145 -- Name : Explode_ICC_Structure
146 -- Description : This procedure will be used to
147 --
148 -- Parameters:
149 -- IN :
150 -- p_session_id IN NUMBER
151 -- An Unique DB sequence generated at Java wrapper.
152 --
153 -- p_odi_session_id IN VARCHAR2
154 -- ODI generated Unique Session Id for ODI Scenario.
155 --
156 --
157 -- ============================================================================
158
159 PROCEDURE Explode_ICC_Structure(p_session_id IN NUMBER,
160 p_odi_session_id IN NUMBER);
161
162
163 -- ============================================================================
164 -- Name : Create_Config_Param
165 -- Description : This procedure will be used to insert record for configurable
166 -- parameters.
167 --
168 -- Parameters:
169 -- IN :
170 -- p_session_id IN NUMBER
171 -- An Unique DB sequence generated at Java wrapper.
172 --
173 -- p_odi_session_id IN VARCHAR2
174 -- ODI generated Unique Session Id for ODI Scenario.
175 --
176 -- p_webservice_name IN VARCHAR2
177 -- Name of webservice for which config parametera needs to create.
178 --
179 -- p_lang_search_str IN VARCHAR2
180 -- Input xml string for language node corresponding to a webservice.
181 --
182 -- p_parent_hier IN VARCHAR2
183 -- TRUE if parent hierarchy needs to publish
184 --
185 -- p_child_hier IN VARCHAR2
186 -- TRUE if child hierarchy needs to publish.
187 --
188 --
189 -- ============================================================================
190
191 PROCEDURE Create_Config_Param(p_session_id IN NUMBER,
192 p_odi_session_id IN NUMBER,
193 p_webservice_name IN VARCHAR2,
194 p_lang_search_str IN VARCHAR2,
195 p_parent_hier IN VARCHAR2,
196 p_child_hier IN VARCHAR2);
197
198
199 -- ============================================================================
200 -- Name : Decode_Component_Code
201 -- Description : This procedure converts the given component code (string
202 -- containing the concatenated inventory_item_ids from a given
203 -- component to the end-item in a BOM) into a human readable
204 -- XML describing containing the inventory item names, sequence
205 -- number, and other related information.
206 --
207 --
208 -- Parameters:
209 -- IN :
210 -- component_code IN VARCHAR2
211 -- Component code for a given component inside a BOM.
212
213 --
214 -- ============================================================================
215 FUNCTION Decode_Component_Code(component_code IN VARCHAR2) RETURN CLOB;
216
217 -- ============================================================================
218 -- Name : Populate_Input_Identifier
219 -- Description : This procedure will be used to insert record for Input Identifier
220 --
221 -- Parameters:
222 -- IN :
223 -- p_session_id IN NUMBER
224 -- An Unique DB sequence generated at Java wrapper.
225 --
226 -- p_odi_session_id IN VARCHAR2
227 -- ODI generated Unique Session Id for ODI Scenario.
228 --
229 -- p_input_id IN VARCHAR2
230 -- Id of the Input Identifier.
231 --
232 -- p_param_name IN VARCHAR2
233 -- Name of the Parameter in the Identifier
234 --
235 -- p_param_value IN VARCHAR2
236 -- Value of the Parameter in the Identifier
237 -- ============================================================================
238
239 PROCEDURE Populate_Input_Identifier(p_session_id IN NUMBER,
240 p_odi_session_id IN NUMBER,
241 p_input_id IN NUMBER,
242 p_param_name IN VARCHAR2,
243 p_param_value IN VARCHAR2);
244
245
246 -- ============================================================================
247 -- Name : Log_Error
248 -- Description : This procedure will be used to insert error record
249 --
250 -- Parameters:
251 -- IN :
252 -- p_session_id IN NUMBER
253 -- An Unique DB sequence generated at Java wrapper.
254 --
255 -- p_odi_session_id IN VARCHAR2
256 -- ODI generated Unique Session Id for ODI Scenario.
257 --
258 -- p_input_id IN VARCHAR2
259 -- Id of the Input Identifier for which the error occured.
260 --
261 -- p_err_code IN VARCHAR2
262 -- Error Code
263 --
267 PROCEDURE Log_Error(p_session_id IN NUMBER,
264 -- p_err_message IN VARCHAR2
265 -- Error Message
266 -- ============================================================================
268 p_odi_session_id IN NUMBER,
269 p_input_id IN NUMBER,
270 p_err_code IN VARCHAR2,
271 p_err_message IN VARCHAR2);
272
273
274
275 -- ============================================================================
276 -- Name : validate_entity
277 -- Description : This procedure will be used to insert record for configurable
278 -- parameters.
279 --
280 -- Parameters:
281 -- IN :
282 -- p_session_id IN NUMBER
283 -- An Unique DB sequence generated at Java wrapper.
284 --
285 -- p_odi_session_id IN VARCHAR2
286 -- ODI generated Unique Session Id for ODI Scenario.
287 --
288 -- p_webservice_name IN VARCHAR2
289 -- Name of webservice for which config parametera needs to create.
290 --
291 --
292 -- RETURN :
293 -- BOOLEAN value
294
295 -- ============================================================================
296
297 FUNCTION validate_entity( p_session_id IN NUMBER,
298 p_odi_session_id IN NUMBER,
299 p_batch_id IN NUMBER DEFAULT NULL,
300 p_webservice_name IN VARCHAR2 DEFAULT NULL,
301 p_pk1_value IN VARCHAR2 DEFAULT NULL,
302 p_pk2_value IN VARCHAR2 DEFAULT NULL,
303 p_pk3_value IN VARCHAR2 DEFAULT NULL,
304 p_pk4_value IN VARCHAR2 DEFAULT NULL ,
305 p_pk5_value IN VARCHAR2 DEFAULT NULL)
306 RETURN BOOLEAN;
307
308
309
310 -- ============================================================================
311 -- Name : Check_Access_Priv
312 -- Description : This function will check access priviledge to function 'EGO_ITEM_ADMINISTRATION'
313 -- and return boolean value.
314 --
315 --
316 -- Parameters:
317 -- IN :
318 -- p_session_id IN NUMBER
319 -- An Unique DB sequence generated at Java wrapper.
320 --
321 -- p_odi_session_id IN VARCHAR2
322 -- ODI generated Unique Session Id for ODI Scenario.
323 --
324 -- p_webservice_name IN VARCHAR2
325 -- Name of webservice for which config parametera needs to create.
326 --
327 --
328 -- RETURN :
329 -- BOOLEAN value
330 -- ============================================================================
331
332 FUNCTION Check_Access_Priv( p_session_id IN NUMBER,
333 p_odi_session_id IN NUMBER,
334 p_web_service_name IN VARCHAR2)
335 RETURN BOOLEAN;
336
337 -- ============================================================================
338 -- Name : Populate_Input_Identifier
339 -- Description : This procedure will be populate transaction attribute into flat
340 -- table.
341 --
342 -- Parameters:
343 -- IN :
344 -- p_session_id IN NUMBER
345 -- An Unique DB sequence generated at Java wrapper.
346 --
347 -- p_odi_session_id IN VARCHAR2
348 -- ODI generated Unique Session Id for ODI Scenario.
349 --
350 -- ============================================================================
351
352 PROCEDURE POPULATE_TRANS_ATTR_LIST( p_session_id IN NUMBER,
353 p_odi_session_id IN NUMBER) ;
354
355 -- ============================================================================
356
357 /*============================================================================
358 Name : POPULATE_VSTBLINFO_VSSVC
359 Description : This procedure populates the details of Table Type of ValueSet
360 This procedure will be called by ValueSet ODI Project
361
362 Parameters: p_Session_Id IN NUMBER
363 An Unique DB sequence generated at Java wrapper.
364
365 p_ODISession_Id IN VARCHAR2
366 p_ODI generated Unique Session Id for ODI Scenario*/
367
368 PROCEDURE POPULATE_VSTBLINFO_VSSVC(p_Session_Id IN NUMBER,
369 p_ODISession_Id IN NUMBER);
370 /*============================================================================*/
371
372 /*============================================================================
373 Name : POPULATE_VSTBLINFO_ICCSVC
374 Description : This procedure populates the details of Table Type of ValueSet
375 This procedure will be called by ICC ODI Project
376
377 Parameters: p_Session_Id IN NUMBER
378 An Unique DB sequence generated at Java wrapper.
379
380 p_ODISession_Id IN VARCHAR2
381 p_ODI generated Unique Session Id for ODI Scenario*/
382
383 PROCEDURE POPULATE_VSTBLINFO_ICCSVC(p_Session_Id IN NUMBER,
384 p_ODISession_Id IN NUMBER);
385 /*============================================================================*/
386
387 ---------------------------------------------------------------
388 -- Global Variables and Constants --
389 ---------------------------------------------------------------
390 G_CURRENT_USER_ID NUMBER := FND_GLOBAL.User_Id;
391 G_CURRENT_LOGIN_ID NUMBER := FND_GLOBAL.Login_Id;
392 G_ICC_WEBSERVICE VARCHAR2(20) :='GETICCDETAILS';
393 G_VS_WEBSERVICE VARCHAR2(20) :='GETVALUESETDETAILS';
394
395
396
397
398 END EGO_ODI_PUB;