1 PACKAGE xla_events_pub_pkg AS
2 -- $Header: xlaevevp.pkh 120.31 2008/01/14 07:25:16 pshukla ship $
3 /*===========================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +============================================================================+
8 | FILENAME |
9 | xlaevevp.pkh |
10 | |
11 | PACKAGE NAME |
12 | xla_events_pub_pkg |
13 | |
14 | DESCRIPTION |
15 | This is a public package for product teams, which contains all the |
16 | APIs required for processing accounting events. |
17 | |
18 | Note: the APIs do not excute COMMIT or ROLLBACK. |
19 | |
20 | These public APIs are wrapper over public routines of xla_event_pkg |
21 | |
22 | HISTORY |
23 | 08-Feb-01 G. Gu Created |
24 | 10-Mar-01 P. Labrevois Reviewed |
25 | 17-Jul-01 P. Labrevois Added array w/ id |
26 | 14-Sep-01 P. Labrevois Added 2nd bulk |
27 | 08-Feb-02 S. Singhania Reviewed and performed major changes |
28 | 10-Feb-02 S. Singhania Made changes in APIs to handle 'Transaction |
29 | Number' (new column added in xla_entities) |
30 | 13-Feb-02 S. Singhania Made changes in APIs to handle 'Event Number' |
31 | 17-Feb-02 S. Singhania Added 'Org_id' parameter to all the APIs as a |
32 | 'security context' |
33 | 04-Apr-02 S. Singhania Replaced 'Org_id' parameter with the generic |
34 | paramenter 'p_security_context'. Removed the|
35 | not required, redundant APIs |
36 | 17-Apr-02 S. Singhania Removed type "t_entity_source_info" and shadow|
37 | procedures. Added type "t_security". Added |
38 | seperate APIs to update transaction number, |
39 | Event Number and Reference Information |
40 | 06-May-02 S. Singhania Added "valuation_method" and "ledger_id" |
41 | Bug # 2351677 |
42 | 31-May-02 S. Singhania Changes based on Bug # 2392835. Changed type |
43 | definitions to remove 'source_id_date_n'. |
44 | Removed one of the 'create_entity_event' API |
45 | Renamed 'create_entity_event' API to |
46 | 'create_bulk_events' |
47 | 23-Jul-02 S. Singhania commented 'document status' contants and the |
48 | 'get_document_status' API. Bug # 2464825 |
49 | 09-Sep-02 S. Singhania Made changes to resolve bug # 2530796. |
50 | Modified signature of 'create_bulk_events', |
51 | added types 't_entity_event_info_s' and |
52 | 't_array_entity_event_info_s' |
53 | 04-Sep-03 S. Singhania Made changes to satisfy enhanced requirement |
54 | for 'Source Application'. |
55 | - Modified the type T_EVENT_SOURCE_INFO |
56 | - Added parameter to CREATE_BULK_EVENTS |
57 | 12-Dec-03 S. Singhania Removed the API UPDATE_TRANSACTION_NUMBER. |
58 | Bug # 3268790 |
59 | |
60 | 23-Mar-04 W. Shen add on_hold_flag to t_event_info |
61 | 25-Jun-04 W. Shen add a new function delete_entity(bug 3316535) |
62 | 23-OCT-04 W. Shen New API to delete/update/create event in bulk |
63 | 4 -Apr-05 W. Shen add transaction_date to the new update_event |
64 | API and create_event API |
65 | 02-May-05 V. Kumar Removed function create_bulk_events, |
66 | Bug # 4323140 |
67 +===========================================================================*/
68
69 -------------------------------------------------------------------------------
70 -- declaring global constants
71 -------------------------------------------------------------------------------
72 C_EVENT_PROCESSED CONSTANT VARCHAR2(1) := 'P'; -- The status will never be be used by product team.
73 C_EVENT_UNPROCESSED CONSTANT VARCHAR2(1) := 'U'; -- event status:unprocessed
74 C_EVENT_INCOMPLETE CONSTANT VARCHAR2(1) := 'I'; -- event status:incomplete
75 C_EVENT_NOACTION CONSTANT VARCHAR2(1) := 'N'; -- event status:noaction
76
77 -------------------------------------------------------------------------------
78 -- declaring global types
79 -------------------------------------------------------------------------------
80 --
81 -- Type that store security context for an entity/document
82 --
83 TYPE t_security IS RECORD
84 (security_id_int_1 INTEGER
85 ,security_id_int_2 INTEGER
86 ,security_id_int_3 INTEGER
87 ,security_id_char_1 VARCHAR2(30)
88 ,security_id_char_2 VARCHAR2(30)
89 ,security_id_char_3 VARCHAR2(30));
90
91 --
92 -- Source IDs information for an entity/document
93 --
94 TYPE t_event_source_info IS RECORD
95 (source_application_id PLS_INTEGER DEFAULT NULL
96 ,application_id PLS_INTEGER
97 ,legal_entity_id PLS_INTEGER
98 ,ledger_id PLS_INTEGER
99 ,entity_type_code VARCHAR2(30)
100 ,transaction_number VARCHAR2(240)
101 ,source_id_int_1 PLS_INTEGER
102 ,source_id_int_2 PLS_INTEGER
103 ,source_id_int_3 PLS_INTEGER
104 ,source_id_int_4 PLS_INTEGER
105 ,source_id_char_1 VARCHAR2(30)
106 ,source_id_char_2 VARCHAR2(30)
107 ,source_id_char_3 VARCHAR2(30)
108 ,source_id_char_4 VARCHAR2(30));
109
110 -- ,source_id_date_1 DATE
111 -- ,source_id_date_2 DATE
112 -- ,source_id_date_3 DATE
113 -- ,source_id_date_4 DATE);
114
115 --
116 -- Reference-only infos for an event
117 --
118 TYPE t_event_reference_info IS RECORD
119 (reference_num_1 NUMBER
120 ,reference_num_2 NUMBER
121 ,reference_num_3 NUMBER
122 ,reference_num_4 NUMBER
123 ,reference_char_1 VARCHAR2(240)
124 ,reference_char_2 VARCHAR2(240)
125 ,reference_char_3 VARCHAR2(240)
126 ,reference_char_4 VARCHAR2(240)
127 ,reference_date_1 DATE
128 ,reference_date_2 DATE
129 ,reference_date_3 DATE
130 ,reference_date_4 DATE);
131
132 --
133 -- Information for an event, by event_id
134 --
135 TYPE t_event_info IS RECORD
136 (event_id PLS_INTEGER
137 ,event_number PLS_INTEGER
138 ,event_type_code VARCHAR2(30)
139 ,event_date DATE
140 ,event_status_code VARCHAR2(1)
141 ,process_status_code VARCHAR2(1)
142 ,on_hold_flag VARCHAR2(1)
143 ,reference_num_1 NUMBER
144 ,reference_num_2 NUMBER
145 ,reference_num_3 NUMBER
146 ,reference_num_4 NUMBER
147 ,reference_char_1 VARCHAR2(240)
148 ,reference_char_2 VARCHAR2(240)
149 ,reference_char_3 VARCHAR2(240)
150 ,reference_char_4 VARCHAR2(240)
151 ,reference_date_1 DATE
152 ,reference_date_2 DATE
153 ,reference_date_3 DATE
154 ,reference_date_4 DATE);
155
156 --
157 -- Information for an event, by event_id, <L>imited to major members
158 --
159 TYPE t_event_info_l IS RECORD
160 (event_id PLS_INTEGER
161 ,event_type_code VARCHAR2(30)
162 ,event_date DATE
163 ,event_status_code VARCHAR2(1)
164 ,process_status_code VARCHAR2(1));
165
166 --
167 -- Full information for an event, by source both source IDs and event_id
168 --
169 TYPE t_entity_event_info IS RECORD
170 (event_type_code VARCHAR2(30)
171 ,event_date DATE
172 ,event_id PLS_INTEGER
173 ,event_number PLS_INTEGER
174 ,event_status_code VARCHAR2(1)
175 ,transaction_number VARCHAR2(240)
176 ,transaction_date DATE
177 ,source_id_int_1 PLS_INTEGER
178 ,source_id_int_2 PLS_INTEGER
179 ,source_id_int_3 PLS_INTEGER
180 ,source_id_int_4 PLS_INTEGER
181 ,source_id_char_1 VARCHAR2(30)
182 ,source_id_char_2 VARCHAR2(30)
183 ,source_id_char_3 VARCHAR2(30)
184 ,source_id_char_4 VARCHAR2(30)
185 ,reference_num_1 NUMBER
186 ,reference_num_2 NUMBER
187 ,reference_num_3 NUMBER
188 ,reference_num_4 NUMBER
189 ,reference_char_1 VARCHAR2(240)
190 ,reference_char_2 VARCHAR2(240)
191 ,reference_char_3 VARCHAR2(240)
192 ,reference_char_4 VARCHAR2(240)
193 ,reference_date_1 DATE
194 ,reference_date_2 DATE
195 ,reference_date_3 DATE
196 ,reference_date_4 DATE);
197
198 -- ,source_id_date_1 DATE
199 -- ,source_id_date_2 DATE
200 -- ,source_id_date_3 DATE
201 -- ,source_id_date_4 DATE
202
203 TYPE t_entity_event_info_s IS RECORD
204 (event_type_code VARCHAR2(30)
205 ,event_date DATE
206 ,event_id PLS_INTEGER
207 ,event_number PLS_INTEGER
208 ,event_status_code VARCHAR2(1)
209 ,transaction_number VARCHAR2(240)
210 ,transaction_date DATE
211 ,source_id_int_1 PLS_INTEGER
212 ,source_id_int_2 PLS_INTEGER
213 ,source_id_int_3 PLS_INTEGER
214 ,source_id_int_4 PLS_INTEGER
215 ,source_id_char_1 VARCHAR2(30)
216 ,source_id_char_2 VARCHAR2(30)
217 ,source_id_char_3 VARCHAR2(30)
218 ,source_id_char_4 VARCHAR2(30)
219 ,reference_num_1 NUMBER
220 ,reference_num_2 NUMBER
221 ,reference_num_3 NUMBER
222 ,reference_num_4 NUMBER
223 ,reference_char_1 VARCHAR2(240)
224 ,reference_char_2 VARCHAR2(240)
225 ,reference_char_3 VARCHAR2(240)
226 ,reference_char_4 VARCHAR2(240)
227 ,reference_date_1 DATE
228 ,reference_date_2 DATE
229 ,reference_date_3 DATE
230 ,reference_date_4 DATE
231 ,valuation_method VARCHAR2(30)
232 ,budgetary_control_flag VARCHAR2(1)
233 ,security_id_int_1 INTEGER
234 ,security_id_int_2 INTEGER
235 ,security_id_int_3 INTEGER
236 ,security_id_char_1 VARCHAR2(30)
237 ,security_id_char_2 VARCHAR2(30)
238 ,security_id_char_3 VARCHAR2(30));
239
240
241 TYPE t_array_event_reference_info IS TABLE OF t_event_reference_info INDEX BY BINARY_INTEGER;
242 TYPE t_array_event_info IS TABLE OF t_event_info INDEX BY BINARY_INTEGER;
243 TYPE t_array_event_info_l IS TABLE OF t_event_info_l INDEX BY BINARY_INTEGER;
244 TYPE t_array_event_source_info IS TABLE OF t_event_source_info INDEX BY BINARY_INTEGER;
245 TYPE t_array_event_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
246 TYPE t_array_event_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
247 TYPE t_array_event_status_code IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
248 TYPE t_array_entity_id IS TABLE OF PLS_INTEGER INDEX BY BINARY_INTEGER;
249 TYPE t_array_event_id IS TABLE OF PLS_INTEGER INDEX BY BINARY_INTEGER;
250 TYPE t_array_entity_event_info IS TABLE OF t_entity_event_info INDEX BY BINARY_INTEGER;
251 TYPE t_array_entity_event_info_s IS TABLE OF t_entity_event_info_s INDEX BY BINARY_INTEGER;
252 TYPE t_array_event_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
253
254
255 -------------------------------------------------------------------------------
256 -- declaring global variables
257 -------------------------------------------------------------------------------
258
259 g_security t_security;
260 g_valuation_method VARCHAR2(30);
261 -------------------------------------------------------------------------------
262 -- Event creation routines
263 -------------------------------------------------------------------------------
264
265 FUNCTION create_event
266 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
267 ,p_event_type_code IN VARCHAR2
268 ,p_event_date IN DATE
269 ,p_event_status_code IN VARCHAR2
270 ,p_event_number IN INTEGER DEFAULT NULL
271 ,p_transaction_date IN DATE DEFAULT NULL
272 ,p_reference_info IN xla_events_pub_pkg.t_event_reference_info DEFAULT NULL
273 ,p_valuation_method IN VARCHAR2
274 ,p_security_context IN xla_events_pub_pkg.t_security)
275 RETURN INTEGER;
276
277 FUNCTION create_event
278 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
279 ,p_event_type_code IN VARCHAR2
280 ,p_event_date IN DATE
281 ,p_event_status_code IN VARCHAR2
282 ,p_event_number IN INTEGER DEFAULT NULL
286 ,p_security_context IN xla_events_pub_pkg.t_security
283 ,p_transaction_date IN DATE DEFAULT NULL
284 ,p_reference_info IN xla_events_pub_pkg.t_event_reference_info DEFAULT NULL
285 ,p_valuation_method IN VARCHAR2
287 ,p_budgetary_control_flag IN VARCHAR2)
288 RETURN INTEGER;
289
290
291 -------------------------------------------------------------------------------
292 -- Event updation routines
293 -------------------------------------------------------------------------------
294
295 PROCEDURE update_event_status
296 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
297 ,p_event_class_code IN VARCHAR2 DEFAULT NULL
298 ,p_event_type_code IN VARCHAR2 DEFAULT NULL
299 ,p_event_date IN DATE DEFAULT NULL
300 ,p_event_status_code IN VARCHAR2
301 ,p_valuation_method IN VARCHAR2
302 ,p_security_context IN xla_events_pub_pkg.t_security);
303
304 PROCEDURE update_event
305 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
306 ,p_event_id IN INTEGER
307 ,p_event_type_code IN VARCHAR2 DEFAULT NULL
308 ,p_event_date IN DATE DEFAULT NULL
309 ,p_event_status_code IN VARCHAR2 DEFAULT NULL
310 ,p_valuation_method IN VARCHAR2
311 ,p_security_context IN xla_events_pub_pkg.t_security
312 ,p_transaction_date IN DATE DEFAULT NULL);
313
314 PROCEDURE update_event
315 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
316 ,p_event_id IN INTEGER
317 ,p_event_type_code IN VARCHAR2 DEFAULT NULL
318 ,p_event_date IN DATE DEFAULT NULL
319 ,p_event_status_code IN VARCHAR2 DEFAULT NULL
320 ,p_event_number IN INTEGER
321 ,p_valuation_method IN VARCHAR2
322 ,p_security_context IN xla_events_pub_pkg.t_security
323 ,p_transaction_date IN DATE DEFAULT NULL);
324
325 PROCEDURE update_event
326 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
327 ,p_event_id IN INTEGER
328 ,p_event_type_code IN VARCHAR2 DEFAULT NULL
329 ,p_event_date IN DATE DEFAULT NULL
330 ,p_event_status_code IN VARCHAR2 DEFAULT NULL
331 ,p_reference_info IN xla_events_pub_pkg.t_event_reference_info
332 ,p_valuation_method IN VARCHAR2
333 ,p_security_context IN xla_events_pub_pkg.t_security
334 ,p_transaction_date IN DATE DEFAULT NULL);
335
336 PROCEDURE update_event
337 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
338 ,p_event_id IN INTEGER
339 ,p_event_type_code IN VARCHAR2 DEFAULT NULL
340 ,p_event_date IN DATE DEFAULT NULL
341 ,p_event_status_code IN VARCHAR2 DEFAULT NULL
342 ,p_event_number IN INTEGER
343 ,p_reference_info IN xla_events_pub_pkg.t_event_reference_info
344 ,p_valuation_method IN VARCHAR2
345 ,p_security_context IN xla_events_pub_pkg.t_security
346 ,p_transaction_date IN DATE DEFAULT NULL);
347
348 -------------------------------------------------------------------------------
349 -- Event deletion routines
350 -------------------------------------------------------------------------------
351
352 PROCEDURE delete_event
353 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
354 ,p_event_id IN INTEGER
355 ,p_valuation_method IN VARCHAR2
356 ,p_security_context IN xla_events_pub_pkg.t_security);
357
358 FUNCTION delete_events
359 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
360 ,p_event_class_code IN VARCHAR2 DEFAULT NULL
361 ,p_event_type_code IN VARCHAR2 DEFAULT NULL
362 ,p_event_date IN DATE DEFAULT NULL
363 ,p_valuation_method IN VARCHAR2
364 ,p_security_context IN xla_events_pub_pkg.t_security)
365 RETURN INTEGER;
366
367 -------------------------------------------------------------------------------
368 -- Event information routines
369 -------------------------------------------------------------------------------
370
371 FUNCTION get_event_info
372 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
373 ,p_event_id IN INTEGER
374 ,p_valuation_method IN VARCHAR2
375 ,p_security_context IN xla_events_pub_pkg.t_security)
376 RETURN xla_events_pub_pkg.t_event_info;
377
378 FUNCTION get_event_status
379 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
380 ,p_event_id IN INTEGER
381 ,p_valuation_method IN VARCHAR2
382 ,p_security_context IN xla_events_pub_pkg.t_security)
383 RETURN VARCHAR2;
384
385 FUNCTION event_exists
386 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
387 ,p_event_class_code IN VARCHAR2 DEFAULT NULL
388 ,p_event_type_code IN VARCHAR2 DEFAULT NULL
389 ,p_event_date IN DATE DEFAULT NULL
390 ,p_event_status_code IN VARCHAR2 DEFAULT NULL
391 ,p_event_number IN INTEGER DEFAULT NULL
392 ,p_valuation_method IN VARCHAR2
393 ,p_security_context IN xla_events_pub_pkg.t_security)
394 RETURN BOOLEAN;
395
396 FUNCTION get_array_event_info
400 ,p_event_date IN DATE DEFAULT NULL
397 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
398 ,p_event_class_code IN VARCHAR2 DEFAULT NULL
399 ,p_event_type_code IN VARCHAR2 DEFAULT NULL
401 ,p_event_status_code IN VARCHAR2 DEFAULT NULL
402 ,p_valuation_method IN VARCHAR2
403 ,p_security_context IN xla_events_pub_pkg.t_security)
404 RETURN xla_events_pub_pkg.t_array_event_info;
405 -------------------------------------------------------------------------------
406 -- Entity deletion routines
407 -------------------------------------------------------------------------------
408
409 FUNCTION delete_entity
410 (p_source_info IN xla_events_pub_pkg.t_event_source_info
411 ,p_valuation_method IN VARCHAR2
412 ,p_security_context IN xla_events_pub_pkg.t_security)
413 RETURN INTEGER;
414
415 FUNCTION period_close
416 (p_subscription_guid IN raw
417 ,p_event IN OUT NOCOPY WF_EVENT_T)
418 RETURN VARCHAR2;
419
420 PROCEDURE period_close
421 (p_api_version IN NUMBER
422 ,x_return_status IN OUT NOCOPY VARCHAR2
423 ,p_application_id IN NUMBER
424 ,p_ledger_id IN NUMBER
425 ,p_period_name IN VARCHAR2);
426
427 PROCEDURE create_bulk_events
428 (p_source_application_id IN INTEGER DEFAULT NULL
429 ,p_application_id IN INTEGER
430 ,p_legal_entity_id IN INTEGER DEFAULT NULL
431 ,p_ledger_id IN INTEGER
432 ,p_entity_type_code IN VARCHAR2);
433
434 PROCEDURE update_bulk_event_statuses(p_application_id INTEGER);
435
436 PROCEDURE delete_bulk_events(p_application_id INTEGER);
437
438
439 --added bug6737299 update transaction number procedure
440
441 -------------------------------------------------------------------------------
442 -- Entity update routines
443 -------------------------------------------------------------------------------
444
445 PROCEDURE update_transaction_number
446 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
447 ,p_transaction_number IN VARCHAR2
448 ,p_valuation_method IN VARCHAR2
449 ,p_security_context IN xla_events_pub_pkg.t_security
450 ,p_event_id IN PLS_INTEGER DEFAULT NULL) ;
451
452 END xla_events_pub_pkg;