1 PACKAGE ego_vs_bulkload_pvt AUTHID CURRENT_USER AS
2 /* $Header: EGOVVSBS.pls 120.11 2011/07/08 10:25:50 yjain noship $ */
3 /*==========================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : EGOVVSBS.pls |
9 | DESCRIPTION : This file is a packaged procedure for importing value set |
10 | and corresponding values using interface or concurrent |
11 | program route. |
12 +==========================================================================*/
13
14
15
16 -- =================================================================================
17 -- Name : Convert_Name_To_Id
18 -- Description : This procedure will be used to get a Id for a name for a given sub entity.
19 --
20 --
21 -- Parameters:
22 -- IN :
23 -- Name IN VARCHAR2
24 -- Sub entity name, for which Id has to be resolved.
25 --
26 -- Entity_Id IN Number
27 --
28 --
29 -- Parent_Id IN Number
30 --
31 -- OUT :
32 -- Id OUT NOCOPY VARCHAR2
33 --
34 --
35 --
36 -- ===============================================================================
37 Procedure Convert_Name_To_Id (
38 Name IN VARCHAR2,
39 Entity_Id IN NUMBER,
40 Parent_Id IN NUMBER DEFAULT NULL, -- Here Parent Id will be Id of parent entity for a sub entity.
41 Id OUT NOCOPY NUMBER);
42
43
44
45
46 Procedure Convert_Id_To_Name (
47 Id IN OUT NOCOPY NUMBER,
48 Entity_Id IN NUMBER,
49 Parent_Id IN NUMBER DEFAULT NULL, -- Here Parent Id will be Id of parent entity for a sub entity.
50 Name OUT NOCOPY VARCHAR2 );
51
52
53
54
55 PROCEDURE Get_Effective_Version_Date ( p_value_set_id IN NUMBER,
56 p_version_seq_id IN NUMBER,
57 x_start_active_date OUT NOCOPY DATE,
58 x_end_active_date OUT NOCOPY DATE
59 );
60
61
62
63 -- =================================================================================
64 -- Name : Resolve_Transaction_Type
65 -- Description : This procedure will be used to resolve transaction type 'SYNC' to either 'CREATE'
66 -- or 'UPDATE'.
67 --
68 -- Parameters:
69 -- IN :
70 --
71 -- p_set_process_id IN Number
72 -- Batch Id to be processed
73 --
74 --
75 -- =================================================================================
76
77 PROCEDURE Resolve_Transaction_Type
78 ( p_set_process_id IN NUMBER,
79 x_return_status OUT NOCOPY VARCHAR2,
80 x_return_msg OUT NOCOPY VARCHAR2
81 );
82
83
84
85 -- ================================================================================
86 -- Name : Validate_Transaction_Type
87 -- Description : This procedure will be used to validate valid transaction type for a given record
88 --
89 -- Parameters:
90 -- IN :
91 --
92 -- p_set_process_id IN Number
93 -- Batch Id to be processed
94 --
95 --
96 -- =================================================================================
97
98 PROCEDURE Validate_Transaction_Type
99 ( p_set_process_id IN NUMBER,
100 x_return_status OUT NOCOPY VARCHAR2,
101 x_return_msg OUT NOCOPY VARCHAR2
102 );
103
104
105
106 PROCEDURE Release_Value_Set_Version(
107 p_value_set_id IN NUMBER,
108 p_description IN VARCHAR2,
109 p_start_date IN TIMESTAMP,
110 p_end_date IN TIMESTAMP,
111 p_version_seq_id IN NUMBER,
112 p_transaction_id IN NUMBER,
113 x_out_vers_seq_id OUT NOCOPY NUMBER,
114 x_return_status OUT NOCOPY VARCHAR2,
115 x_return_msg OUT NOCOPY VARCHAR2 );
116
117
118 PROCEDURE Get_Key_VS_Columns
119 ( p_value_set_id IN NUMBER,
120 p_transaction_id IN NUMBER,
121 x_maximum_size IN OUT NOCOPY VARCHAR2,
122 x_maximum_value IN OUT NOCOPY VARCHAR2,
123 x_minimum_value IN OUT NOCOPY VARCHAR2,
124 x_description IN OUT NOCOPY VARCHAR2,
125 x_longlist_flag IN OUT NOCOPY VARCHAR2,
126 x_format_code IN OUT NOCOPY VARCHAR2,
127 x_validation_code IN OUT NOCOPY VARCHAR2,
128 x_return_status OUT NOCOPY VARCHAR2,
129 x_return_msg OUT NOCOPY VARCHAR2
130 );
131
132
133
134 --Bug 9702828
135 PROCEDURE Get_Key_Value_Columns
136 ( p_value_set_id IN NUMBER,
137 p_value_id IN NUMBER,
138 x_display_name IN OUT NOCOPY VARCHAR2,
139 x_disp_sequence IN OUT NOCOPY NUMBER,
140 x_start_date_active IN OUT NOCOPY DATE, --Bug 12692653
141 x_end_date_active IN OUT NOCOPY DATE, --Bug 12692653
142 x_description IN OUT NOCOPY VARCHAR2,
143 x_enabled_flag IN OUT NOCOPY VARCHAR2,
144 x_return_status OUT NOCOPY VARCHAR2,
145 x_return_msg OUT NOCOPY VARCHAR2
146 );
147
148
149
150 -- =================================================================================
151 -- Name : Populate_VS_Interface
152 -- Description : This procedure will be used to update pl/sql record back to interface table for a given value set
153 --
154 --
155 -- Parameters:
156 -- IN :
157 -- p_value_set_tbl IN Value_Set_Tbl
158 -- Table instance having record of the type of ego_flex_value_set_intf
159 --
160 -- OUT :
161 -- x_return_status OUT NOCOPY VARCHAR2
162 -- Used to get status of a procedure, whether it executed
163 -- Successfully or not.
164 --
165 --
166 -- =================================================================================
167
168 PROCEDURE Populate_VS_Interface ( p_valueset_tbl IN Ego_Metadata_Pub.Value_Set_Tbl,
169 x_return_status OUT NOCOPY VARCHAR2,
170 x_return_msg OUT NOCOPY VARCHAR2);
171
172
173
174
175 -- =================================================================================
176 -- Name : Populate_VS_Val_Interface
177 -- Description : This procedure will be used to update pl/sql record back to interface table
178 -- for a value of a value set.
179 --
180 --
181 -- Parameters:
182 -- IN :
183 -- p_valueset_val_tbl IN Value_Set_Value_Tbl
184 -- Table instance having record of the type of ego_flex_value_intf
185 --
186 -- OUT :
187 -- x_return_status OUT NOCOPY VARCHAR2
188 -- Used to get status of a procedure, whether it executed
189 -- Successfully or not.
190 --
191 --
192 -- =================================================================================
193
194 PROCEDURE Populate_VS_Val_Interface ( p_valueset_val_tbl IN Ego_Metadata_Pub.Value_Set_Value_Tbl,
195 x_return_status OUT NOCOPY VARCHAR2,
196 x_return_msg OUT NOCOPY VARCHAR2);
197
198
199
200
201 -- =================================================================================
202 -- Name : Populate_VS_Val_Tl_Interface
203 -- Description : This procedure will be used to update pl/sql record back to interface table
204 -- for a translatable value of a value set.
205 --
206 --
207 -- Parameters:
208 -- IN :
209 -- p_valueset_val_tl_tbl IN Value_Set_Value_Tl_Tbl
210 -- Table instance having record of the type of ego_flex_value_Tl_intf
211 --
212 -- OUT :
213 -- x_return_status OUT NOCOPY VARCHAR2
214 -- Used to get status of a procedure, whether it executed
215 -- Successfully or not.
216 --
217 --
218 -- =================================================================================
219
220 PROCEDURE Populate_VS_Val_Tl_Interface (p_valueset_val_tl_tbl IN Ego_Metadata_Pub.Value_Set_Value_Tl_Tbl,
221 x_return_status OUT NOCOPY VARCHAR2,
222 x_return_msg OUT NOCOPY VARCHAR2);
223
224
225
226
227 -- =================================================================================
228 -- Name : Import_Value_Set_Intf
229 -- Description : This procedure will be used to import value set using a concurrent program.
230 --
231 --
232 -- Parameters:
233 -- IN :
234 -- p_api_version IN NUMBER
235 -- Active API version number
236 --
237 -- p_set_process_id IN Number
238 -- Batch Id to be processed
239 --
240 --
241 -- =================================================================================
242
243 PROCEDURE Import_Value_Set_Intf (p_set_process_id IN NUMBER,
244 x_return_status OUT NOCOPY VARCHAR2,
245 x_return_msg OUT NOCOPY VARCHAR2);
246
247
248
249 PROCEDURE Delete_Processed_Value_Sets( p_set_process_id IN NUMBER,
250 x_return_status OUT NOCOPY VARCHAR2,
251 x_return_msg OUT NOCOPY VARCHAR2);
252
253
254
255 -- Bug 9802900
256 -- =================================================================================
257 -- Name : Validate_Telco_profile
258 -- Description : This procedure will be used to validate if Telco profile option is
259 -- enabled to process version related records.
260 --
261 -- Parameters:
262 -- IN :
263 --
264 -- p_set_process_id IN Number
265 -- Batch Id to be processed
266 --
267 --
268 -- OUT :
269 -- x_return_status OUT NOCOPY VARCHAR2
270 -- Used to get status of a procedure, whether it executed
271 -- Successfully or not.
272 --
273 --
274 -- x_return_msg OUT NOCOPY VARCHAR2
275 -- =================================================================================
276 PROCEDURE Validate_Telco_profile(p_set_process_id IN NUMBER,
277 x_return_status OUT NOCOPY VARCHAR2,
278 x_return_msg OUT NOCOPY VARCHAR2);
279
280
281
282 -- Bug 9804379
283 -- =================================================================================
284 -- Name : Sync_VS_With_Draft
285 -- Description : This procedure will be used to sync up draft version of a value set
286 -- with passed in version number.
287 --
288 -- Parameters:
289 -- IN :
290 --
291 -- p_value_set_id IN Number
292 -- Value Set Id for which draft version need to be in sync with
293 -- passed in version number
294 --
295 -- p_version_number IN Number
296 -- Version sequence id with which draft version need to be in sync.
297 --
298 -- OUT :
299 -- x_return_status OUT NOCOPY VARCHAR2
300 -- Used to get status of a procedure, whether it executed
301 -- Successfully or not.
302 --
303 --
304 -- x_return_msg OUT NOCOPY VARCHAR2
305 -- =================================================================================
306 PROCEDURE Sync_VS_With_Draft ( p_value_set_id IN NUMBER
307 ,p_version_number IN NUMBER
308 ,x_return_status OUT NOCOPY VARCHAR2
309 ,x_return_msg OUT NOCOPY VARCHAR2);
310
311 -- =================================================================================
312 -- Name : Initialize_VS_Interface
313 -- Description : This procedure will be used to do bulk validation while called through
314 -- concurent program.
315 --
316 -- Parameters:
317 -- IN :
318 -- p_api_version IN NUMBER
319 -- Active API version number
320 --
321 -- p_set_process_id IN Number
322 -- Batch Id to be processed
323 --
324 --
325 --
326 -- OUT :
327 -- x_return_status OUT NOCOPY VARCHAR2
328 -- Used to get status of a procedure, whether it executed
329 -- Successfully or not.
330 --
331 -- x_msg_count OUT NOCOPY NUMBER
332 --
333 -- x_return_msg OUT NOCOPY VARCHAR2
334 --
335 -- =================================================================================
336
337 PROCEDURE Initialize_VS_Interface (
338 p_api_version IN NUMBER,
339 p_set_process_id IN NUMBER,
340 x_return_status OUT NOCOPY VARCHAR2,
341 x_msg_count OUT NOCOPY NUMBER,
342 x_return_msg OUT NOCOPY VARCHAR2);
343
344
345
346 -- Bug 9702845
347 PROCEDURE Validate_value_Set (
348 p_value_set_name IN VARCHAR2,
349 p_validation_code IN VARCHAR2,
350 p_longlist_flag IN VARCHAR2,
351 p_format_code IN VARCHAR2,
352 p_maximum_size IN NUMBER,
353 p_maximum_value IN VARCHAR2,
354 p_minimum_value IN VARCHAR2,
355 p_version_seq_id IN NUMBER,
356 p_transaction_id IN NUMBER,
357 p_transaction_type IN VARCHAR2,
358 x_return_status OUT NOCOPY VARCHAR2,
359 x_return_msg OUT NOCOPY VARCHAR2);
360
361
362
363 -- Pocedure to convert value to DB Date
364 -- Bug 9701510
365 PROCEDURE Convert_Value_To_DbDate ( p_value IN OUT NOCOPY VARCHAR2);
366
367
368 -- Procedure to validate if input date is in user preferred date format.
369 -- Bug 9701510
370 PROCEDURE Validate_User_Preferred_Date (p_value IN OUT NOCOPY VARCHAR2,
371 p_format_code IN VARCHAR2,
372 p_transaction_id IN VARCHAR2,
373 x_return_status OUT NOCOPY VARCHAR2,
374 x_return_msg OUT NOCOPY VARCHAR2);
375
376
377
378
379 PROCEDURE Validate_Child_Value_Set (
380 p_value_set_name IN VARCHAR2,
381 p_value_set_id IN NUMBER,
382 p_validation_code IN VARCHAR2,
383 p_longlist_flag IN VARCHAR2,
384 p_format_code IN VARCHAR2,
385 p_version_seq_id IN NUMBER,
386 p_transaction_id IN NUMBER,
387 x_return_status OUT NOCOPY VARCHAR2,
388 x_return_msg OUT NOCOPY VARCHAR2);
389
390
391
392 PROCEDURE Validate_Table_Value_Set (
393 p_value_set_name IN VARCHAR2,
394 p_value_set_id IN NUMBER,
395 p_format_code IN VARCHAR2,
396 p_application_table_name IN VARCHAR2,
397 p_additional_where_clause IN VARCHAR2 DEFAULT NULL,
398 p_value_column_name IN VARCHAR2,
399 p_value_column_type IN VARCHAR2,
400 p_value_column_size IN NUMBER,
401
402 p_id_column_name IN VARCHAR2 DEFAULT NULL ,
403 p_id_column_type IN VARCHAR2 DEFAULT NULL ,
404 p_id_column_size IN NUMBER DEFAULT NULL ,
405
406 p_meaning_column_name IN VARCHAR2 DEFAULT NULL ,
407 p_meaning_column_type IN VARCHAR2 DEFAULT NULL ,
408 p_meaning_column_size IN NUMBER DEFAULT NULL ,
409
410
411 p_transaction_id IN NUMBER,
412 x_return_status OUT NOCOPY VARCHAR2,
413 x_return_msg OUT NOCOPY VARCHAR2);
414
415
416 -- =================================================================================
417 -- Name : Process_Value_Sets
418 -- Description : This procedure will be used to create value set. It will process
419 -- record Row by Row.
420 --
421 -- Parameters:
422 -- IN :
423 -- p_api_version IN NUMBER
424 -- Active API version number
425 --
426 -- p_value_set_tbl IN Value_Set_Tbl
427 -- Table instance having record of the type of ego_flex_value_set_intf
428 --
429 -- p_set_process_id IN Number
430 -- Batch Id to be processed
431 --
432 --
433 --
434 -- OUT :
435 -- x_return_status OUT NOCOPY VARCHAR2
436 -- Used to get status of a procedure, whether it executed
437 -- Successfully or not.
438 --
439 -- x_msg_count OUT NOCOPY NUMBER
440 --
441 -- x_return_msg OUT NOCOPY VARCHAR2
442 --
443 -- =================================================================================
444
445 PROCEDURE Process_Value_Set (
446 p_api_version IN NUMBER,
447 p_value_set_tbl IN OUT NOCOPY Ego_Metadata_Pub.Value_Set_Tbl,
448 p_set_process_id IN NUMBER,
449 p_commit IN BOOLEAN DEFAULT FALSE,
450 x_return_status OUT NOCOPY VARCHAR2,
451 x_msg_count OUT NOCOPY NUMBER,
452 x_return_msg OUT NOCOPY VARCHAR2) ;
453
454
455 -- =================================================================================
456 -- Name : Process_Value_Set_Value
457 -- Description : This procedure will be used to create values associated to a value set.
458 -- It will process record Row by Row.
459 --
460 -- Parameters:
461 -- IN :
462 -- p_api_version IN NUMBER
463 -- Active API version number
464 --
465 -- p_value_set_val_tbl IN Value_Set_Value_Tbl
466 -- Table instance having record of the type of ego_flex_value_intf
467 --
468 -- p_value_set_val_tl_tbl IN Value_Set_Value_Tl_Tbl
469 -- Table instance having record of the type of ego_flex_value_tl_intf
470 --
471 -- p_set_process_id IN Number
472 -- Batch Id to be processed
473 --
474 --
475 --
476 -- OUT :
477 -- x_return_status OUT NOCOPY VARCHAR2
478 -- Used to get status of a procedure, whether it executed
479 -- Successfully or not.
480 --
481 -- x_msg_count OUT NOCOPY NUMBER
482 --
483 -- x_return_msg OUT NOCOPY VARCHAR2
484 --
485 -- =================================================================================
486 PROCEDURE Process_Value_Set_Value (
487 p_api_version IN NUMBER,
488 p_value_set_val_tbl IN OUT NOCOPY Ego_Metadata_Pub.Value_Set_Value_Tbl,
489 p_value_set_val_tl_tbl IN OUT NOCOPY Ego_Metadata_Pub.Value_Set_Value_Tl_Tbl,
490 p_set_process_id IN NUMBER,
491 p_commit IN BOOLEAN DEFAULT FALSE,
492 x_return_status OUT NOCOPY VARCHAR2,
493 x_msg_count OUT NOCOPY NUMBER,
494 x_return_msg OUT NOCOPY VARCHAR2) ;
495
496
497
498
499
500 PROCEDURE Process_Isolate_Value (
501 p_api_version IN NUMBER,
502 p_value_set_val_tbl IN OUT NOCOPY Ego_Metadata_Pub.Value_Set_Value_Tbl,
503 p_value_set_val_tl_tbl IN OUT NOCOPY Ego_Metadata_Pub.Value_Set_Value_Tl_Tbl,
504 p_set_process_id IN NUMBER,
505 p_commit IN BOOLEAN DEFAULT FALSE,
506 x_return_status OUT NOCOPY VARCHAR2,
507 x_msg_count OUT NOCOPY NUMBER,
508 x_return_msg OUT NOCOPY VARCHAR2);
509
510
511 -- =================================================================================
512 -- Name : Process_Child_Value_Set
513 -- Description : This procedure will be used to create child value set and corresponding
514 -- values. It will process record Row by Row.
515 --
516 -- Parameters:
517 -- IN :
518 -- p_api_version IN NUMBER
519 -- Active API version number
520 --
521 -- p_value_set_tbl IN Value_Set_Tbl
522 -- Table instance having record of the type of ego_flex_value_set_intf
523 --
524 -- p_valueset_val_tab IN Value_Set_Value_Tbl
525 -- Table instance having record of the type of ego_flex_value_intf
526 --
527 -- p_set_process_id IN Number
528 -- Batch Id to be processed
529 --
530 --
531 --
532 -- OUT :
533 -- x_return_status OUT NOCOPY VARCHAR2
534 -- Used to get status of a procedure, whether it executed
535 -- Successfully or not.
536 --
537 -- x_msg_count OUT NOCOPY NUMBER
538 --
539 -- x_return_msg OUT NOCOPY VARCHAR2
540 --
541 -- =================================================================================
542
543 PROCEDURE Process_Child_Value_Set (
544 p_api_version IN NUMBER,
545 p_value_set_tbl IN OUT NOCOPY Ego_Metadata_Pub.Value_Set_Tbl,
546 p_valueset_val_tab IN OUT NOCOPY Ego_Metadata_Pub.Value_Set_Value_Tbl,
547 p_set_process_id IN NUMBER,
548 p_commit IN BOOLEAN DEFAULT FALSE,
549 x_return_status OUT NOCOPY VARCHAR2,
550 x_msg_count OUT NOCOPY NUMBER,
551 x_return_msg OUT NOCOPY VARCHAR2) ;
552
553 END ego_vs_bulkload_pvt ;
[Home] [Help]