1 PACKAGE xla_events_pub_pkg AUTHID CURRENT_USER AS
2 -- $Header: xlaevevp.pkh 120.35 2010/10/13 12:53:14 nksurana 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 | 30-SEP-10 VDAMERLA Bug:9077926 Created overloaded procedures |
68 | for create_bulk_events and |
69 | update_bulk_event_statuses to pass |
70 | bulk limit value |
71 | 13-Oct-10 nksurana Bug 10152910 Created new function/API |
72 | allow_third_party_update to check if the party|
73 | site can be updated, based on Control Account.|
74 +===========================================================================*/
75
76 -------------------------------------------------------------------------------
77 -- declaring global constants
78 -------------------------------------------------------------------------------
79 C_EVENT_PROCESSED CONSTANT VARCHAR2(1) := 'P'; -- The status will never be be used by product team.
80 C_EVENT_UNPROCESSED CONSTANT VARCHAR2(1) := 'U'; -- event status:unprocessed
81 C_EVENT_INCOMPLETE CONSTANT VARCHAR2(1) := 'I'; -- event status:incomplete
82 C_EVENT_NOACTION CONSTANT VARCHAR2(1) := 'N'; -- event status:noaction
83
84 -------------------------------------------------------------------------------
85 -- declaring global types
86 -------------------------------------------------------------------------------
87 --
88 -- Type that store security context for an entity/document
89 --
90 TYPE t_security IS RECORD
91 (security_id_int_1 INTEGER
92 ,security_id_int_2 INTEGER
93 ,security_id_int_3 INTEGER
94 ,security_id_char_1 VARCHAR2(30)
95 ,security_id_char_2 VARCHAR2(30)
96 ,security_id_char_3 VARCHAR2(30));
97
98 --
99 -- Source IDs information for an entity/document
100 --
101 TYPE t_event_source_info IS RECORD
102 (source_application_id PLS_INTEGER DEFAULT NULL
103 ,application_id PLS_INTEGER
104 ,legal_entity_id PLS_INTEGER
105 ,ledger_id PLS_INTEGER
106 ,entity_type_code VARCHAR2(30)
107 ,transaction_number VARCHAR2(240)
108 ,source_id_int_1 NUMBER
109 ,source_id_int_2 NUMBER
110 ,source_id_int_3 NUMBER
111 ,source_id_int_4 NUMBER
112 ,source_id_char_1 VARCHAR2(30)
113 ,source_id_char_2 VARCHAR2(30)
114 ,source_id_char_3 VARCHAR2(30)
115 ,source_id_char_4 VARCHAR2(30));
116
117 -- ,source_id_date_1 DATE
118 -- ,source_id_date_2 DATE
119 -- ,source_id_date_3 DATE
120 -- ,source_id_date_4 DATE);
121
122 --
123 -- Reference-only infos for an event
124 --
125 TYPE t_event_reference_info IS RECORD
126 (reference_num_1 NUMBER
127 ,reference_num_2 NUMBER
128 ,reference_num_3 NUMBER
129 ,reference_num_4 NUMBER
130 ,reference_char_1 VARCHAR2(240)
131 ,reference_char_2 VARCHAR2(240)
132 ,reference_char_3 VARCHAR2(240)
133 ,reference_char_4 VARCHAR2(240)
134 ,reference_date_1 DATE
135 ,reference_date_2 DATE
136 ,reference_date_3 DATE
137 ,reference_date_4 DATE);
138
139 --
140 -- Information for an event, by event_id
141 --
142 TYPE t_event_info IS RECORD
143 (event_id NUMBER -- 8761772
144 ,event_number PLS_INTEGER
145 ,event_type_code VARCHAR2(30)
146 ,event_date DATE
147 ,event_status_code VARCHAR2(1)
148 ,process_status_code VARCHAR2(1)
149 ,on_hold_flag VARCHAR2(1)
150 ,reference_num_1 NUMBER
151 ,reference_num_2 NUMBER
152 ,reference_num_3 NUMBER
153 ,reference_num_4 NUMBER
154 ,reference_char_1 VARCHAR2(240)
155 ,reference_char_2 VARCHAR2(240)
156 ,reference_char_3 VARCHAR2(240)
157 ,reference_char_4 VARCHAR2(240)
158 ,reference_date_1 DATE
159 ,reference_date_2 DATE
160 ,reference_date_3 DATE
161 ,reference_date_4 DATE);
162
163 --
164 -- Information for an event, by event_id, <L>imited to major members
165 --
166 TYPE t_event_info_l IS RECORD
167 (event_id NUMBER -- 8761772
168 ,event_type_code VARCHAR2(30)
169 ,event_date DATE
170 ,event_status_code VARCHAR2(1)
171 ,process_status_code VARCHAR2(1));
172
173 --
174 -- Full information for an event, by source both source IDs and event_id
175 --
176 TYPE t_entity_event_info IS RECORD
177 (event_type_code VARCHAR2(30)
178 ,event_date DATE
179 ,event_id NUMBER -- 8761772
180 ,event_number PLS_INTEGER
181 ,event_status_code VARCHAR2(1)
182 ,transaction_number VARCHAR2(240)
183 ,transaction_date DATE
184 ,source_id_int_1 NUMBER
185 ,source_id_int_2 NUMBER
186 ,source_id_int_3 NUMBER
187 ,source_id_int_4 NUMBER
188 ,source_id_char_1 VARCHAR2(30)
189 ,source_id_char_2 VARCHAR2(30)
190 ,source_id_char_3 VARCHAR2(30)
191 ,source_id_char_4 VARCHAR2(30)
192 ,reference_num_1 NUMBER
193 ,reference_num_2 NUMBER
194 ,reference_num_3 NUMBER
195 ,reference_num_4 NUMBER
196 ,reference_char_1 VARCHAR2(240)
197 ,reference_char_2 VARCHAR2(240)
198 ,reference_char_3 VARCHAR2(240)
199 ,reference_char_4 VARCHAR2(240)
200 ,reference_date_1 DATE
201 ,reference_date_2 DATE
202 ,reference_date_3 DATE
203 ,reference_date_4 DATE);
204
205 -- ,source_id_date_1 DATE
206 -- ,source_id_date_2 DATE
207 -- ,source_id_date_3 DATE
208 -- ,source_id_date_4 DATE
209
210 TYPE t_entity_event_info_s IS RECORD
211 (event_type_code VARCHAR2(30)
212 ,event_date DATE
213 ,event_id NUMBER -- 8761772
214 ,event_number PLS_INTEGER
215 ,event_status_code VARCHAR2(1)
216 ,transaction_number VARCHAR2(240)
217 ,transaction_date DATE
218 ,source_id_int_1 NUMBER
219 ,source_id_int_2 NUMBER
220 ,source_id_int_3 NUMBER
221 ,source_id_int_4 NUMBER
222 ,source_id_char_1 VARCHAR2(30)
223 ,source_id_char_2 VARCHAR2(30)
224 ,source_id_char_3 VARCHAR2(30)
225 ,source_id_char_4 VARCHAR2(30)
226 ,reference_num_1 NUMBER
227 ,reference_num_2 NUMBER
228 ,reference_num_3 NUMBER
229 ,reference_num_4 NUMBER
230 ,reference_char_1 VARCHAR2(240)
231 ,reference_char_2 VARCHAR2(240)
232 ,reference_char_3 VARCHAR2(240)
233 ,reference_char_4 VARCHAR2(240)
234 ,reference_date_1 DATE
235 ,reference_date_2 DATE
236 ,reference_date_3 DATE
237 ,reference_date_4 DATE
238 ,valuation_method VARCHAR2(30)
239 ,budgetary_control_flag VARCHAR2(1)
240 ,security_id_int_1 INTEGER
241 ,security_id_int_2 INTEGER
242 ,security_id_int_3 INTEGER
243 ,security_id_char_1 VARCHAR2(30)
244 ,security_id_char_2 VARCHAR2(30)
245 ,security_id_char_3 VARCHAR2(30));
246
247
248 TYPE t_array_event_reference_info IS TABLE OF t_event_reference_info INDEX BY BINARY_INTEGER;
249 TYPE t_array_event_info IS TABLE OF t_event_info INDEX BY BINARY_INTEGER;
250 TYPE t_array_event_info_l IS TABLE OF t_event_info_l INDEX BY BINARY_INTEGER;
251 TYPE t_array_event_source_info IS TABLE OF t_event_source_info INDEX BY BINARY_INTEGER;
252 TYPE t_array_event_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
253 TYPE t_array_event_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
254 TYPE t_array_event_status_code IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
255 TYPE t_array_entity_id IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; -- 8761772
256 TYPE t_array_event_id IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; -- 8761772
257 TYPE t_array_entity_event_info IS TABLE OF t_entity_event_info INDEX BY BINARY_INTEGER;
258 TYPE t_array_entity_event_info_s IS TABLE OF t_entity_event_info_s INDEX BY BINARY_INTEGER;
259 TYPE t_array_event_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
260
261
262 -------------------------------------------------------------------------------
263 -- declaring global variables
264 -------------------------------------------------------------------------------
265
266 g_security t_security;
267 g_valuation_method VARCHAR2(30);
268 -------------------------------------------------------------------------------
269 -- Event creation routines
270 -------------------------------------------------------------------------------
271
272 FUNCTION create_event
273 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
274 ,p_event_type_code IN VARCHAR2
275 ,p_event_date IN DATE
276 ,p_event_status_code IN VARCHAR2
277 ,p_event_number IN INTEGER DEFAULT NULL
278 ,p_transaction_date IN DATE DEFAULT NULL
279 ,p_reference_info IN xla_events_pub_pkg.t_event_reference_info DEFAULT NULL
280 ,p_valuation_method IN VARCHAR2
281 ,p_security_context IN xla_events_pub_pkg.t_security)
282 RETURN INTEGER;
283
284 FUNCTION create_event
285 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
286 ,p_event_type_code IN VARCHAR2
287 ,p_event_date IN DATE
288 ,p_event_status_code IN VARCHAR2
289 ,p_event_number IN INTEGER DEFAULT NULL
290 ,p_transaction_date IN DATE DEFAULT NULL
291 ,p_reference_info IN xla_events_pub_pkg.t_event_reference_info DEFAULT NULL
292 ,p_valuation_method IN VARCHAR2
293 ,p_security_context IN xla_events_pub_pkg.t_security
294 ,p_budgetary_control_flag IN VARCHAR2)
295 RETURN INTEGER;
296
297
298 -------------------------------------------------------------------------------
299 -- Event updation routines
300 -------------------------------------------------------------------------------
301
302 PROCEDURE update_event_status
303 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
307 ,p_event_status_code IN VARCHAR2
304 ,p_event_class_code IN VARCHAR2 DEFAULT NULL
305 ,p_event_type_code IN VARCHAR2 DEFAULT NULL
306 ,p_event_date IN DATE DEFAULT NULL
308 ,p_valuation_method IN VARCHAR2
309 ,p_security_context IN xla_events_pub_pkg.t_security);
310
311 PROCEDURE update_event
312 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
313 ,p_event_id IN INTEGER
314 ,p_event_type_code IN VARCHAR2 DEFAULT NULL
315 ,p_event_date IN DATE DEFAULT NULL
316 ,p_event_status_code IN VARCHAR2 DEFAULT NULL
317 ,p_valuation_method IN VARCHAR2
318 ,p_security_context IN xla_events_pub_pkg.t_security
319 ,p_transaction_date IN DATE DEFAULT NULL);
320
321 PROCEDURE update_event
322 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
323 ,p_event_id IN INTEGER
324 ,p_event_type_code IN VARCHAR2 DEFAULT NULL
325 ,p_event_date IN DATE DEFAULT NULL
326 ,p_event_status_code IN VARCHAR2 DEFAULT NULL
327 ,p_event_number IN INTEGER
328 ,p_valuation_method IN VARCHAR2
329 ,p_security_context IN xla_events_pub_pkg.t_security
330 ,p_transaction_date IN DATE DEFAULT NULL);
331
332 PROCEDURE update_event
333 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
334 ,p_event_id IN INTEGER
335 ,p_event_type_code IN VARCHAR2 DEFAULT NULL
336 ,p_event_date IN DATE DEFAULT NULL
337 ,p_event_status_code IN VARCHAR2 DEFAULT NULL
338 ,p_reference_info IN xla_events_pub_pkg.t_event_reference_info
339 ,p_valuation_method IN VARCHAR2
340 ,p_security_context IN xla_events_pub_pkg.t_security
341 ,p_transaction_date IN DATE DEFAULT NULL);
342
343 PROCEDURE update_event
344 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
345 ,p_event_id IN INTEGER
346 ,p_event_type_code IN VARCHAR2 DEFAULT NULL
347 ,p_event_date IN DATE DEFAULT NULL
348 ,p_event_status_code IN VARCHAR2 DEFAULT NULL
349 ,p_event_number IN INTEGER
350 ,p_reference_info IN xla_events_pub_pkg.t_event_reference_info
351 ,p_valuation_method IN VARCHAR2
352 ,p_security_context IN xla_events_pub_pkg.t_security
353 ,p_transaction_date IN DATE DEFAULT NULL);
354
355 -------------------------------------------------------------------------------
356 -- Event deletion routines
357 -------------------------------------------------------------------------------
358
359 PROCEDURE delete_event
360 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
361 ,p_event_id IN INTEGER
362 ,p_valuation_method IN VARCHAR2
363 ,p_security_context IN xla_events_pub_pkg.t_security);
364
365 FUNCTION delete_events
366 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
367 ,p_event_class_code IN VARCHAR2 DEFAULT NULL
368 ,p_event_type_code IN VARCHAR2 DEFAULT NULL
369 ,p_event_date IN DATE DEFAULT NULL
370 ,p_valuation_method IN VARCHAR2
371 ,p_security_context IN xla_events_pub_pkg.t_security)
372 RETURN INTEGER;
373
374 -------------------------------------------------------------------------------
375 -- Event information routines
376 -------------------------------------------------------------------------------
377
378 FUNCTION get_event_info
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 xla_events_pub_pkg.t_event_info;
384
385 FUNCTION get_event_status
386 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
387 ,p_event_id IN INTEGER
388 ,p_valuation_method IN VARCHAR2
389 ,p_security_context IN xla_events_pub_pkg.t_security)
390 RETURN VARCHAR2;
391
392 FUNCTION event_exists
393 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
394 ,p_event_class_code IN VARCHAR2 DEFAULT NULL
395 ,p_event_type_code IN VARCHAR2 DEFAULT NULL
396 ,p_event_date IN DATE DEFAULT NULL
397 ,p_event_status_code IN VARCHAR2 DEFAULT NULL
398 ,p_event_number IN INTEGER DEFAULT NULL
399 ,p_valuation_method IN VARCHAR2
400 ,p_security_context IN xla_events_pub_pkg.t_security)
401 RETURN BOOLEAN;
402
403 FUNCTION get_array_event_info
404 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
405 ,p_event_class_code IN VARCHAR2 DEFAULT NULL
406 ,p_event_type_code IN VARCHAR2 DEFAULT NULL
407 ,p_event_date IN DATE DEFAULT NULL
408 ,p_event_status_code IN VARCHAR2 DEFAULT NULL
409 ,p_valuation_method IN VARCHAR2
410 ,p_security_context IN xla_events_pub_pkg.t_security)
411 RETURN xla_events_pub_pkg.t_array_event_info;
412 -------------------------------------------------------------------------------
413 -- Entity deletion routines
414 -------------------------------------------------------------------------------
415
416 FUNCTION delete_entity
417 (p_source_info IN xla_events_pub_pkg.t_event_source_info
418 ,p_valuation_method IN VARCHAR2
419 ,p_security_context IN xla_events_pub_pkg.t_security)
420 RETURN INTEGER;
421
422 FUNCTION period_close
423 (p_subscription_guid IN raw
424 ,p_event IN OUT NOCOPY WF_EVENT_T)
425 RETURN VARCHAR2;
426
427 PROCEDURE period_close
428 (p_api_version IN NUMBER
429 ,x_return_status IN OUT NOCOPY VARCHAR2
430 ,p_application_id IN NUMBER
431 ,p_ledger_id IN NUMBER
432 ,p_period_name IN VARCHAR2);
433
434 PROCEDURE create_bulk_events
435 (p_source_application_id IN INTEGER DEFAULT NULL
436 ,p_application_id IN INTEGER
437 ,p_legal_entity_id IN INTEGER DEFAULT NULL
438 ,p_ledger_id IN INTEGER
439 ,p_entity_type_code IN VARCHAR2);
440
441
442 PROCEDURE create_bulk_events
443 (p_source_application_id IN INTEGER DEFAULT NULL
444 ,p_application_id IN INTEGER
445 ,p_legal_entity_id IN INTEGER DEFAULT NULL
446 ,p_ledger_id IN INTEGER
447 ,p_entity_type_code IN VARCHAR2
448 ,p_limit_size IN INTEGER);
449
450
451 PROCEDURE update_bulk_event_statuses(p_application_id INTEGER);
452 PROCEDURE update_bulk_event_statuses(p_application_id IN INTEGER
453 ,p_limit_size IN INTEGER);
454
455 PROCEDURE delete_bulk_events(p_application_id INTEGER);
456
457
458 --added bug6737299 update transaction number procedure
459
460 -------------------------------------------------------------------------------
461 -- Entity update routines
462 -------------------------------------------------------------------------------
463
464 PROCEDURE update_transaction_number
465 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
466 ,p_transaction_number IN VARCHAR2
467 ,p_valuation_method IN VARCHAR2
468 ,p_security_context IN xla_events_pub_pkg.t_security
469 ,p_event_id IN NUMBER DEFAULT NULL) ; -- 8761772
470
471 -------------------------------------------------------------------------------
472 -- Control Account Information routines
473 -------------------------------------------------------------------------------
474
475 FUNCTION allow_third_party_update
476 (p_event_source_info IN xla_events_pub_pkg.t_event_source_info
477 ,p_valuation_method IN VARCHAR2
478 ,p_security_context IN xla_events_pub_pkg.t_security)
479 RETURN BOOLEAN;
480
481 END xla_events_pub_pkg;