[Home] [Help]
PACKAGE BODY: APPS.FUN_SEQ
Source
1 PACKAGE BODY fun_seq AS
2 /* $Header: funsqgnb.pls 120.36 2004/11/04 01:07:53 masada noship $ */
3 --
4 --
5 --
6 g_use_cache_flag BOOLEAN DEFAULT FALSE;
7 --
8 -- For Sequencing Context Cache
9 --
10 g_sc_cache_size BINARY_INTEGER DEFAULT 0;
11 g_context_info_tbl context_info_tbl_type;
12 g_context_ctrl_tbl context_ctrl_tbl_type;
13
14 --
15 -- For Assignment Cache
16 --
17 g_as_cache_size BINARY_INTEGER DEFAULT 0;
18 g_assign_info_tbl assignment_info_tbl_type;
19 g_assign_seq_head_tbl assign_seq_head_tbl_type;
20
21 --
22 -- For Exception Cache
23 --
24 g_exp_cache_size BINARY_INTEGER DEFAULT 0;
25 g_exp_info_tbl exp_info_tbl_type;
26 g_exp_seq_head_tbl assign_seq_head_tbl_type;
27
28 -- PROCEDURE NAME:
29 -- get_sequence_number
30 -- DESCRIPTION:
31 -- Retrieve sequence information of assignments and generate
32 -- sequence numbers.
33 -- INPUT:
34 -- - p_context_type
35 -- Sequence Context Type. Only 'LEDGER_AND_CURRENCY' is supported
36 -- for Accounting Sequencing.
37 -- - p_context_value
38 -- Sequence Context Value. Only Ledger ID is supported for
39 -- Accounting Sequencing.
40 -- - p_application_id
41 -- Application Id of your Sequence Entity.
42 -- - p_table_name
43 -- Table Name of your Sequence Entity.
44 -- - p_event_code
45 -- Sequence Event Code
46 -- - p_control_attribute_rec
47 -- Sequence Control Attribute PL/SQL Record consists of 10 control
48 -- attribute columns.
49 -- [Implementation Example]
50 -- p_control_attribute_rec.control_attribute1 := 'ACTUAL';
51 -- p_control_attribute_rec.control_attribute2 :=
52 -- - p_control_date_tbl
53 -- Sequence Control Date PL/SQL Table which consistes of
54 -- Date Type and Date Value.
55 -- [Implementation Example]
56 -- p_control_date_tbl.extend(2);
57 -- p_control_date_tbl(1).date_type := 'GL_DATE';
58 -- p_control_date_tbl(1).date_value := sysdate;
59 -- p_control_date_tbl(2).date_type := 'COMPLETION_DATE';
60 -- p_control_date_tbl(2).date_value := sysdate;
61 -- - p_suppress_error
62 -- Suppress Error Flag. If Suppress Error is turned on, the caller is
63 -- responsible for raising the exceptions. If not, the exception is
64 -- raised as soon as an error is found.
65 -- OUTPUT:
66 -- - x_seq_version_id
67 -- Sequence Version Id. To be stored in the base table(e.g.GL_JE_HEADERS).
68 -- - x_sequence_number
69 -- Sequence Number. To be stored in the base table.
70 -- - x_assignment_id
71 -- Assignment Id. To be stored in the base table.
72 -- - x_error_code
73 -- - SUCCESS
74 -- 1. No active Assignment Context is found, or
75 -- 2. No Assignment is found and Require Assignment Flag is turned off
76 --
77 PROCEDURE Get_Sequence_Number(
78 p_context_type IN VARCHAR2,
79 p_context_value IN VARCHAR2,
80 p_application_id IN NUMBER,
81 p_table_name IN VARCHAR2,
82 p_event_code IN VARCHAR2,
83 p_control_attribute_rec IN control_attribute_rec_type,
84 p_control_date_tbl IN control_date_tbl_type,
85 p_suppress_error IN VARCHAR2,
86 x_seq_version_id OUT NOCOPY NUMBER,
87 x_sequence_number OUT NOCOPY NUMBER,
88 x_assignment_id OUT NOCOPY NUMBER,
89 x_error_code OUT NOCOPY VARCHAR2)
90 IS
91
92 BEGIN
93 --
94 -- Debug Information
95 --
96 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
97 FND_LOG.STRING(
98 FND_LOG.LEVEL_PROCEDURE,
99 'fun.plsql.fun_seq.get_sequence_number.begin',
100 'p_context_type: ' || p_context_type ||', '||
101 'p_context_value: ' || p_context_value ||', '||
102 'p_application_id: ' || p_application_id ||', '||
103 'p_table_name: ' || p_table_name ||', '||
104 'p_event_code: ' || p_event_code ||', '||
105 'p_suppress_error: ' || p_suppress_error);
106 END IF;
107
108 --
109 -- Check if Sequencing Context is Intercompany Batch or Not
110 --
111 IF p_context_type = 'INTERCOMPANY_BATCH_SOURCE' AND
112 p_context_value = 'LOCAL' AND
113 p_application_id = 435 AND
114 p_table_name = 'FUN_TRX_BATCHES' AND
115 p_event_code = 'CREATION'
116 THEN
117 --
118 -- Issue autonmous commit after getting a number
119 --
120 get_sequence_number_commit (
121 p_context_type => p_context_type,
122 p_context_value => p_context_value,
123 p_application_id => p_application_id,
124 p_table_name => p_table_name,
125 p_event_code => p_event_code,
126 p_control_attribute_rec => p_control_attribute_rec,
127 p_control_date_tbl => p_control_date_tbl,
128 p_suppress_error => p_suppress_error,
129 x_seq_version_id => x_seq_version_id,
130 x_sequence_number => x_sequence_number,
131 x_assignment_id => x_assignment_id,
132 x_error_code => x_error_code);
133
134 ELSE
135 --
136 -- Accounting Sequencing
137 --
138 get_sequence_number_no_commit (
139 p_context_type => p_context_type,
140 p_context_value => p_context_value,
141 p_application_id => p_application_id,
142 p_table_name => p_table_name,
143 p_event_code => p_event_code,
144 p_control_attribute_rec => p_control_attribute_rec,
145 p_control_date_tbl => p_control_date_tbl,
146 p_suppress_error => p_suppress_error,
147 x_seq_version_id => x_seq_version_id,
148 x_sequence_number => x_sequence_number,
149 x_assignment_id => x_assignment_id,
150 x_error_code => x_error_code);
151
152 END IF;
153
154 --
155 -- Debug Information
156 --
157 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
158 FND_LOG.STRING(
159 FND_LOG.LEVEL_PROCEDURE,
160 'fun.plsql.fun_seq.get_sequence_number.end',
161 'p_context_type: ' || p_context_type ||', '||
162 'p_context_value: ' || p_context_value ||', '||
163 'p_application_id: ' || p_application_id ||', '||
164 'p_table_name: ' || p_table_name ||', '||
165 'p_event_code: ' || p_event_code ||', '||
166 'p_suppress_error: ' || p_suppress_error);
167 END IF;
168
169 EXCEPTION
170 WHEN OTHERS THEN
171 --
172 -- Debug Information
173 --
174 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
175 fnd_log.string(
176 fnd_log.level_exception,
177 'fun.plsql.fun_seq.get_sequence_Number.exception',
178 'p_context_type: ' || p_context_type ||', '||
179 'p_context_value: ' || p_context_value ||', '||
180 'p_application_id: ' || p_application_id ||', '||
181 'p_table_name: ' || p_table_name ||', '||
182 'p_event_code: ' || p_event_code ||', '||
183 'p_suppress_error: ' || p_suppress_error ||', '||
184 'SQLERRM: ' || SQLERRM);
185 END IF;
186
187 --
188 -- Raise Exception
189 --
190 app_exception.raise_exception;
191 END Get_Sequence_Number;
192
193 --
194 -- Procedure Name: get_assigned_sequence_info
195 -- Description:
196 -- *** Consult with SSMOA team before calling this API ***
197 -- Steps
198 -- 1. Get_Assigned_Context_Info
199 -- 2. Get_Assigned_Sequence_Header
200 -- 3. Get_Seq_Version
201 -- Returns the following assigned sequence information:
202 -- - Sequence Type
203 -- - Sequence Version Id
204 -- - Sequence Assignment ID
205 -- - Sequence Control Date Value
206 -- - Require Assignment Flag
207 -- - Error Code
208 -- The meanings of Error Codes are as follows:
209 -- - NO_ASSIGN_CONTEXT
210 -- Validation succeeds. No Assignment Context is found.
211 -- No sequence number is generated.
212 -- - NO_ASSIGNMENT
213 -- Validation succeeds. No Assignment is found.
214 -- No sequence number is generated. Require Assignment Flag is turned off.
215 -- Therefore, no error is raised.
216 -- - ENFORCED_NO_ASSIGNMENT
217 -- Validation fails. No Assignment is found while
218 -- Require Assignment flag is turned on.
219 --
220 PROCEDURE Get_Assigned_Sequence_Info(
221 p_context_type IN VARCHAR2,
222 p_context_value IN VARCHAR2,
223 p_application_id IN NUMBER,
224 p_table_name IN VARCHAR2,
225 p_event_code IN VARCHAR2,
226 p_control_attribute_rec IN control_attribute_rec_type,
227 p_control_date_tbl IN control_date_tbl_type,
228 p_request_id IN NUMBER,
229 p_suppress_error IN VARCHAR2,
230 x_sequence_type OUT NOCOPY VARCHAR2,
231 x_seq_version_id OUT NOCOPY NUMBER,
232 x_assignment_id OUT NOCOPY NUMBER,
233 x_control_date_value OUT NOCOPY DATE,
234 x_req_assign_flag OUT NOCOPY VARCHAR2,
235 x_sort_option_code OUT NOCOPY VARCHAR2,
236 x_error_code OUT NOCOPY VARCHAR2)
237 IS
238
239 l_seq_context_id fun_seq_contexts.seq_context_id%TYPE;
240 l_control_date_type fun_seq_contexts.date_type%TYPE;
241 l_req_assign_flag fun_seq_contexts.require_assign_flag%TYPE;
242 l_sort_option_code fun_seq_contexts.sort_option%TYPE;
243 l_sequence_type fun_seq_headers.gapless_flag%TYPE;
244 l_error_code VARCHAR2(30);
245
246 l_control_date_value DATE;
247 l_seq_header_id fun_seq_versions.seq_version_id%TYPE;
248 l_seq_version_id fun_seq_versions.seq_version_id%TYPE;
249 l_assignment_id fun_seq_assignments.assignment_id%TYPE;
250
251 no_assignment_found EXCEPTION;
252 no_seq_version_found EXCEPTION;
253
254 BEGIN
255 --
256 -- Debug Information
257 --
258 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
259 FND_LOG.STRING(
260 FND_LOG.LEVEL_PROCEDURE,
261 'fun.plsql.fun_seq.get_assigned_sequence_info.begin',
262 'p_context_type: ' || p_context_type ||', '||
263 'p_context_value: ' || p_context_value ||', '||
264 'p_application_id: ' || p_application_id ||', '||
265 'p_table_name: ' || p_table_name ||', '||
266 'p_event_code: ' || p_event_code ||', '||
267 'p_request_id: ' || p_request_id ||', '||
268 'p_suppress_error: ' || p_suppress_error);
269 END IF;
270 --
271 -- Get Sequencing Context Information.
272 -- Return the following information:
273 -- - Sequencing Context ID
274 -- - Control Date Type
275 -- - Require Assignment Flag
276 get_assign_context_info(
277 p_context_type => p_context_type,
278 p_context_value => p_context_value,
279 p_application_id => p_application_id,
280 p_table_name => p_table_name,
281 p_event_code => p_event_code,
282 p_request_id => p_request_id,
283 x_seq_context_id => l_seq_context_id, -- OUT
284 x_control_date_type => l_control_date_type, -- OUT
285 x_req_assign_flag => l_req_assign_flag, -- OUT
286 x_sort_option_code => l_sort_option_code); -- OUT
287
288 --
289 -- Success:
290 -- There is no Active Assignment Context.
291 --
292 IF l_seq_context_id IS NULL THEN
293 x_error_code := 'NO_ASSIGN_CONTEXT';
294 --
295 -- If an Active Assignment Context Exists
296 -- 1. Get a Control Date Value
297 -- 2. Get an Assigned Sequence
298 --
299 ELSE
300 --
301 -- Get a Control Date Value
302 --
303 l_control_date_value := get_control_date_value(
304 l_control_date_type,
305 p_control_date_tbl);
306 --
307 -- Get an Assigned Sequence Header
308 --
309 --fun_seq_utils.log_procedure(
310 -- p_module => l_module || ' in progress',
311 -- p_message_text => 'Beginning of get_assigned_sequence_header');
312 --
313 IF p_application_id = 435 AND p_table_name = 'FUN_TRX_BATCHES' THEN
314 get_ic_assigned_seq_header(
315 p_seq_context_id => l_seq_context_id,
316 p_control_date_value => l_control_date_value,
317 p_request_id => p_request_id,
318 x_assignment_id => l_assignment_id, -- OUT
319 x_sequence_type => l_sequence_type, -- OUT
320 x_seq_header_id => l_seq_header_id); -- OUT
321 ELSE
322 get_assigned_sequence_header(
323 p_seq_context_id => l_seq_context_id,
324 p_control_attribute_rec => p_control_attribute_rec,
325 p_control_date_value => l_control_date_value,
326 p_request_id => p_request_id,
327 x_assignment_id => l_assignment_id, -- OUT
328 x_sequence_type => l_sequence_type, -- OUT
329 x_seq_header_id => l_seq_header_id); -- OUT
330 END IF;
331 --
332 -- Get Sequence Version Info if Sequence Assignment is found.
333 --
334 IF l_assignment_id IS NOT NULL THEN
335 IF l_seq_header_id IS NULL THEN
336 x_error_code := 'DO_NOT_SEQUENCE';
337 ELSE
338 --
339 -- Get a Sequence Version
340 --
341 get_seq_version (
342 p_sequence_type => l_sequence_type,
343 p_seq_header_id => l_seq_header_id,
344 p_control_date_value => l_control_date_value,
345 p_request_id => p_request_id,
346 x_seq_version_id => l_seq_version_id); -- OUT
347 --
348 -- Success:
349 -- Sequence Version is found.
350 x_error_code := 'SEQ_VER_FOUND';
351 END IF;
352 --
353 -- Error if Require Assignment Flag is YES:
354 -- Sequence Assignment Id is not found while an Active Assignment Context
355 -- exists
356 -- Success if Require Assignment Flag is NO:
357 --
358 ELSE
359 IF l_req_assign_flag = 'Y' THEN
360 RAISE no_assignment_found;
361 ELSE
362 x_error_code := 'NO_ASSIGNMENT';
363 END IF;
364 END IF;
365 END IF;
366
367 x_sequence_type := l_sequence_type;
368 x_assignment_id := l_assignment_id;
369 x_control_date_value := l_control_date_value;
370 x_req_assign_flag := l_req_assign_flag;
371 x_sort_option_code := l_sort_option_code;
372 --
373 -- Return Null Sequence Version ID for "Do Not Sequence".
374 --
375 x_seq_version_id := l_seq_version_id;
376 --
377 -- Debug Information
378 --
379 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
380 FND_LOG.STRING(
381 FND_LOG.LEVEL_PROCEDURE,
382 'fun.plsql.fun_seq.get_assigned_sequence_info.end',
383 'x_sequence_type: ' || x_sequence_type || ', ' ||
384 'x_assignment_id: ' || x_assignment_id || ', ' ||
385 'x_control_date_value: '|| x_control_date_value ||', '||
386 'x_req_assign_flag: ' || x_req_assign_flag);
387 END IF;
388 EXCEPTION
389 WHEN no_assignment_found THEN
390 --
391 -- Debug Information
392 --
393 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
394 FND_LOG.STRING(
395 FND_LOG.LEVEL_EXCEPTION,
396 'fun.plsql.fun_seq.get_assigned_sequence_info.no_assign',
397 'No Assignment Found (Require Assignment = Y)' ||', '||
398 'l_seq_context_id :' || l_seq_context_id);
399 END IF;
400
401 x_error_code := 'ENFORCED_NO_ASSIGNMENT';
402 fnd_message.set_name ('FUN','FUN_SEQ_NO_ACTIVE_ASSGN_FOUND');
403 fnd_message.set_token ('SEQ_CONTEXT_NAME',
404 get_seq_context_name(l_seq_context_id));
405 IF p_suppress_error = 'N' THEN
406 app_exception.raise_exception;
407 END IF;
408 WHEN OTHERS THEN
409 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
410 fnd_log.string(
411 fnd_log.level_exception,
412 'fun.plsql.fun_seq.get_assigned_sequence_info.exception',
413 'l_seq_context_id :' || l_seq_context_id ||', ' ||
414 'SQLERRM: ' || SQLERRM);
415 END IF;
416 app_exception.raise_exception;
417 END Get_Assigned_Sequence_Info;
418
419 --
420 -- PROCEDURE NAME:
421 -- Generate_Sequence_Number
422 -- DESCRIPTION
423 -- *** Consult with SSMOA team before calling this API ***
424 -- 1. Increment the current value by 1.
425 -- 2. Return the new current value.
426 -- The Status of the Version is updated
427 -- in Get_Assigned_Sequence_Info.
428 --
429 PROCEDURE Generate_Sequence_Number(
430 p_assignment_id IN NUMBER,
431 p_seq_version_id IN NUMBER,
432 p_sequence_type IN VARCHAR2,
433 p_request_id IN NUMBER,
434 x_sequence_number OUT NOCOPY NUMBER,
435 x_sequenced_date OUT NOCOPY DATE,
436 x_error_code OUT NOCOPY VARCHAR2)
437 IS
438 l_assignment_id fun_seq_assignments.assignment_id%TYPE;
439 l_seq_version_id fun_seq_versions.seq_version_id%TYPE;
440 invalid_seq_type EXCEPTION;
441 l_sql_stmt VARCHAR2(2000);
442 l_debug_loc CONSTANT VARCHAR2(100) DEFAULT 'generate_sequence_number';
443 BEGIN
444 l_assignment_id := p_assignment_id;
445 l_seq_version_id := p_seq_version_id;
446 --
447 -- Check if Sequence Type is Gapless
448 --
449 IF (p_sequence_type = 'G') THEN
450 --
451 -- Generate Sequence Number if "Do Not Sequence" policy is NOT on
452 --
453 IF l_seq_version_id IS NOT NULL THEN
454 --
455 -- If the Sequence Version is NOT used, the current_value is null.
456 -- Use initial_value in this case.
457 -- Sequence version is locked.
458 --
459 UPDATE fun_seq_versions
460 SET current_value = NVL(current_value + 1,initial_value)
461 WHERE seq_version_id= l_seq_version_Id
462 RETURNING current_value, sysdate
463 INTO x_sequence_number, x_sequenced_date;
464 --
465 -- Update Status from "New" to "Used" if p_batch_flag = 'N'.
466 --
467 update_gapless_status(
468 p_assignment_id => l_assignment_id,
469 p_seq_version_id => l_seq_version_id);
470
471 END IF;
472 ELSIF (p_sequence_type = 'D') THEN
473 l_sql_stmt := 'SELECT '
474 || 'FUN_SEQ_S' || l_seq_version_id || '.nextval '
475 || 'FROM dual';
476 EXECUTE IMMEDIATE l_sql_stmt INTO x_sequence_number;
477 --
478 -- Update Status from "New" to "Used".
479 --
480 update_db_status(
481 p_assignment_id => l_assignment_id,
482 p_seq_version_id => l_seq_version_id);
483 ELSE
484 RAISE invalid_seq_type;
485 END IF;
486 x_error_code := 'SUCCESS';
487 EXCEPTION
488 --
489 -- Invalid_Seq_Type is a critical programming error.
490 -- So, you cannot suppress this.
491 --
492 WHEN invalid_seq_type THEN
493 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
494 fnd_log.string(
495 log_level => fnd_log.level_exception,
496 module => 'fun.plsql.fun_seq.generate_sequence_number',
497 message => 'Invalid Sequence Type: ' || ', ' ||
498 'SQLERRM: ' || SQLERRM);
499 END IF;
500 app_exception.raise_exception;
501 WHEN OTHERS THEN
502 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
503 fnd_log.string(
504 log_level => fnd_log.level_exception,
505 module => 'fun.plsql.fun_seq.generate_sequence_number',
506 message =>
507 'SQLERRM: ' || SQLERRM);
508 END IF;
509 app_exception.raise_exception;
510 END Generate_Sequence_Number;
511
512 --
513 -- PROCEDURE NAME: reset
514 -- Reset sequence version information
515 -- !!Warning!!
516 -- Never call this procedure without consulting with SSAMOA team.
517 --
518 PROCEDURE Reset(
519 p_seq_version_id IN NUMBER,
520 p_sequence_number IN NUMBER)
521 IS
522 BEGIN
523 --
524 -- Rest Sequence Version
525 --
526 UPDATE fun_seq_versions sv
527 SET sv.current_value = p_sequence_number
528 WHERE sv.seq_version_id = p_seq_version_id;
529 EXCEPTION
530 WHEN OTHERS THEN
531 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
532 fnd_log.string(
533 log_level => fnd_log.level_exception,
534 module => 'fun.plsql.fun_seq.reset',
535 message =>
536 'SQLERRM: ' || SQLERRM);
537 END IF;
538 app_exception.raise_exception;
539 END Reset;
540
541 --
542 -- Retrieve an Active Sequence Assignment Context
543 -- Note:
544 -- Called from Get_Assigned_Sequence_Info
545 -- Product team should not call this procedure directly.
546 -- Lock the Sequencing Context so that no other process
547 -- can change the setup until sequence number is generated
548 --
549 PROCEDURE get_assign_context_info (
550 p_context_type IN VARCHAR2,
551 p_context_value IN VARCHAR2,
552 p_application_id IN NUMBER,
553 p_table_name IN VARCHAR2,
554 p_event_code IN VARCHAR2,
555 p_request_id IN NUMBER,
556 x_seq_context_id OUT NOCOPY NUMBER,
557 x_control_date_type OUT NOCOPY VARCHAR2,
558 x_req_assign_flag OUT NOCOPY VARCHAR2,
559 x_sort_option_code OUT NOCOPY VARCHAR2) IS
560
561 l_context_info_rec context_info_rec_type;
562 l_context_ctrl_rec context_ctrl_rec_type;
563 BEGIN
564 --
565 -- Debug Information
566 --
567 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
568 FND_LOG.STRING(
569 FND_LOG.LEVEL_PROCEDURE,
570 'fun.plsql.fun_seq.get_assign_context_info.begin',
571 'Beginning of get_assign_context_info');
572 END IF;
573 --
574 -- Retrieve Sequenceing Context
575 --
576 IF g_use_cache_flag = FALSE THEN
577 --
578 -- Check if we can use cache for next procedure calls.
579 --
580 g_use_cache_flag := use_cache(
581 p_request_id => p_request_id,
582 p_application_id => p_application_id,
583 p_table_name => p_table_name,
584 p_event_code => p_event_code);
585 --
586 -- For online transactions, a lock is issued.
587 --
588 SELECT sac.seq_context_id,
589 sac.date_type,
590 sac.require_assign_flag,
591 sac.sort_option
592 INTO x_seq_context_id,
593 x_control_date_type,
594 x_req_assign_flag,
595 x_sort_option_code
596 FROM fun_seq_contexts sac
597 WHERE sac.application_id = p_application_id
598 AND sac.table_name = p_table_name
599 AND sac.context_type = p_context_type
600 AND sac.context_value = p_context_value
601 AND sac.event_code = p_event_code
602 AND sac.obsolete_flag = 'N'
603 FOR UPDATE;
604 ELSE
605 l_context_info_rec.application_id := p_application_id;
606 l_context_info_rec.table_name := p_table_name;
607 l_context_info_rec.context_type := p_context_type;
608 l_context_info_rec.context_value := p_context_value;
609 l_context_info_rec.event_code := p_event_code;
610 --
611 -- Batch Mode or UI Display Only. Use Cache.
612 --
613 get_cached_context_info (
614 p_context_info_rec => l_context_info_rec,
615 x_context_ctrl_rec => l_context_ctrl_rec);
616
617 x_seq_context_id := l_context_ctrl_rec.seq_context_id;
618 x_control_date_type := l_context_ctrl_rec.date_type;
619 x_req_assign_flag := l_context_ctrl_rec.req_assign_flag;
620 x_sort_option_code := l_context_ctrl_rec.sort_option_code;
621 END IF;
622 --
623 -- Debug Information
624 --
625 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
626 FND_LOG.STRING(
627 FND_LOG.LEVEL_PROCEDURE,
628 'fun.plsql.fun_seq.get_assign_context_info.end',
629 'Get_assign_context_info completes successfully.' ||', ' ||
630 'x_seq_context_id: ' || x_seq_context_id || ', ' ||
631 'x_control_date_type: ' || x_control_date_type || ', ' ||
632 'x_req_assign_flag: ' || x_req_assign_flag || ', ' ||
633 'x_sort_option_code: ' || x_sort_option_code);
634 END IF;
635 EXCEPTION
636 WHEN NO_DATA_FOUND THEN
637 x_seq_context_id := NULL;
638 WHEN OTHERS THEN
639 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
640 fnd_log.string(
641 log_level => fnd_log.level_exception,
642 module => 'fun.plsql.get_assign_context_info',
643 message =>
644 'SQLERRM: ' || SQLERRM);
645 END IF;
646 FND_MESSAGE.SET_NAME('FND', 'SQL_PLSQL_ERROR');
647 FND_MESSAGE.SET_TOKEN('ROUTINE', 'p_context_value: ' || p_context_value);
648 FND_MESSAGE.SET_TOKEN('ERRNO', '100');
649 FND_MESSAGE.SET_TOKEN('REASON', SQLERRM);
650 FND_MESSAGE.RAISE_ERROR;
651 app_exception.raise_exception;
652 END get_assign_context_info;
653
654
655 --
656 -- Get Sequence Number (without Autonmous Commit)
657 --
658 PROCEDURE Get_Sequence_Number_No_Commit(
659 p_context_type IN VARCHAR2,
660 p_context_value IN VARCHAR2,
661 p_application_id IN NUMBER,
662 p_table_name IN VARCHAR2,
663 p_event_code IN VARCHAR2,
664 p_control_attribute_rec IN control_attribute_rec_type,
665 p_control_date_tbl IN control_date_tbl_type,
666 p_suppress_error IN VARCHAR2,
667 x_seq_version_id OUT NOCOPY NUMBER,
668 x_sequence_number OUT NOCOPY NUMBER,
669 x_assignment_id OUT NOCOPY NUMBER,
670 x_error_code OUT NOCOPY VARCHAR2) IS
671
672 l_context_type fun_seq_contexts.context_type%TYPE;
673 l_context_value fun_seq_contexts.context_value%TYPE;
674 l_application_id fun_seq_contexts.application_id%TYPE;
675 l_table_name fun_seq_contexts.table_name%TYPE;
676 l_event_code fun_seq_contexts.event_code%TYPE;
677 l_control_attribute_rec control_attribute_rec_type;
678 l_control_date_tbl control_date_tbl_type;
679 l_batch_flag VARCHAR2(1);
680 l_suppress_error VARCHAR2(1);
681 l_sequence_type fun_seq_headers.gapless_flag%TYPE;
682 l_seq_version_id fun_seq_versions.seq_version_id%TYPE;
683 l_assignment_id fun_seq_assignments.assignment_id%TYPE;
684 l_control_date_value DATE;
685 l_req_assign_flag fun_seq_contexts.require_assign_flag%TYPE;
686 l_error_code_assign VARCHAR2(30);
687 l_error_code_seq VARCHAR2(30);
688 l_sequence_number fun_seq_versions.initial_value%TYPE;
689 l_sequenced_date DATE;
690 l_dummy VARCHAR2(30); -- For Sort Option
691
692 l_debug_loc VARCHAR2(100);
693
694 no_assigned_seq_info EXCEPTION;
695 no_sequence_number EXCEPTION;
696 invalid_error_code EXCEPTION;
697
698 BEGIN
699 --
700 -- Pass IN parameters to local variables
701 --
702 l_context_type := p_context_type;
703 l_context_value := p_context_value;
704 l_application_id := p_application_id;
705 l_table_name := p_table_name;
706 l_event_code := p_event_code;
707 l_control_attribute_rec := p_control_attribute_rec;
708 l_control_date_tbl := p_control_date_tbl;
709 l_suppress_error := NVL(p_suppress_error,'N');
710 --
711 -- Retrieve Assigned Sequence Information.
712 -- If null request id is passed, pessimistic locks are imposed
713 -- in each SELECT statement so that the user cannot change the setup
714 -- between sub program steps. "NOWAIT" is not used here because
715 -- the user will receive ORA-54 frequently when calling this API
716 -- to generate sequence numbers.
717 -- Note:
718 -- From Online, Request Id is always null.
719 --
720 l_debug_loc := 'get_assigned_sequence_info';
721 --
722 get_assigned_sequence_info(
723 p_context_type => l_context_type,
724 p_context_value => l_context_value,
725 p_application_Id => l_application_id,
726 p_table_name => l_table_name,
727 p_event_code => l_event_code,
728 p_control_attribute_rec => l_control_attribute_rec,
729 p_control_date_tbl => l_control_date_tbl,
730 p_request_id => NULL,
731 p_suppress_error => l_suppress_error,
732 x_sequence_type => l_sequence_type, -- OUT
733 x_seq_version_id => l_seq_version_id, -- OUT
734 x_assignment_id => l_assignment_id, -- OUT
735 x_control_date_value => l_control_date_value, -- OUT
736 x_req_assign_flag => l_req_assign_flag, -- OUT
737 x_sort_option_code => l_dummy, -- OUT
738 x_error_code => l_error_code_assign); -- OUT
739
740 --
741 -- Return SUCCESS if no active Sequencing Context exists or
742 -- no Assignment is found and Require Assignment flag is turned off.
743 --
744 IF l_error_code_assign IN ('NO_ASSIGN_CONTEXT','NO_ASSIGNMENT') THEN
745 x_error_code := 'SUCCESS';
746 --
747 -- If there is an explicit order of "Do Not Sequence", that is,
748 -- Sequence Name is null of a valid Assignment,
749 -- Update Assignment Status
750 --
751 ELSIF l_error_code_assign = 'DO_NOT_SEQUENCE' THEN
752 --
753 l_debug_loc := 'update_assign_status';
754 --
755 -- Update Status of the Assignment
756 --
757 update_assign_status(
758 p_assignment_id => l_assignment_id);
759 --
760 -- Populate OUT variables
761 --
762 x_assignment_id := l_assignment_id;
763 x_error_code := 'SUCCESS';
764 ELSIF l_error_code_assign = 'ENFORCED_NO_ASSIGNMENT' THEN
765 --
766 l_debug_loc := 'EXCEPTION: no_assign_seq_info';
767 --
768 RAISE no_assigned_seq_info;
769 --
770 -- Generate sequence numbers if Sequence Version is found.
771 --
772 ELSIF l_error_code_assign = 'SEQ_VER_FOUND' THEN
773 --
774 l_debug_loc := 'generate_sequence_number';
775 --
776 generate_sequence_number(
777 p_assignment_id => l_assignment_id,
778 p_seq_version_id => l_seq_version_id,
779 p_sequence_type => l_sequence_type,
780 p_request_id => NULL, -- Online mode
781 x_sequence_number => l_sequence_number, -- OUT
782 x_sequenced_date => l_sequenced_date, -- Not Used Here
783 x_error_code => l_error_code_seq); -- OUT
784
785 --
786 -- Populate return values
787 --
788 x_seq_version_id := l_seq_version_id;
789 x_sequence_number := l_sequence_number;
790 x_assignment_id := l_assignment_id;
791 x_error_code := l_error_code_seq;
792
793 ELSE
794 RAISE invalid_error_code;
795 END IF;
796
797 EXCEPTION
798 WHEN no_assigned_seq_info THEN
799 --
800 -- Logging
801 --
802 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
803 fnd_log.string(
804 log_level => fnd_log.level_exception,
805 module => 'fun.plsql.fun_seq.get_sequence_number_no_commit',
806 message =>
810 'p_application_id: '|| p_application_id ||', '||
807 'l_debug_loc: ' || l_debug_loc ||', '||
808 'p_context_type: ' || p_context_type ||', '||
809 'p_context_value: ' || p_context_value ||', '||
811 'p_table_name: ' || p_table_name ||', '||
812 'p_event_code: ' || p_event_code ||', '||
813 'balance type: '
814 || p_control_attribute_rec.balance_type ||', '||
815 'journal source: '
816 || p_control_attribute_rec.journal_source ||', '||
817 'journal category: '
818 || p_control_attribute_rec.journal_category ||', '||
819 'acct entry type: '
820 || p_control_attribute_rec.accounting_entry_type ||', '||
821 'acct event type: '
822 || p_control_attribute_rec.accounting_event_type ||', '||
823 'doc category: '
824 || p_control_attribute_rec.document_category ||', '||
825 'p_suppress_error: '|| p_suppress_error ||', '||
826 'SQLERRM: ' || SQLERRM);
827 END IF;
828 --
829 -- The message is put on the stack in Get_Assigned_Sequence_Info
830 --
831 x_error_code := 'ENFORCED_NO_ASSIGNMENT';
832 IF p_suppress_error = 'N' THEN
833 app_exception.raise_exception;
834 END IF;
835 WHEN OTHERS THEN
836 --
837 -- Logging
838 --
839 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
840 fnd_log.string(
841 log_level => fnd_log.level_exception,
842 module => 'fun.plsql.fun_seq.get_sequence_number_no_commit',
843 message =>
844 'l_debug_loc: ' || l_debug_loc ||', '||
845 'p_context_type: ' || p_context_type ||', '||
846 'p_context_value: ' || p_context_value ||', '||
847 'p_application_id: '|| p_application_id ||', '||
848 'p_table_name: ' || p_table_name ||', '||
849 'p_event_code: ' || p_event_code ||', '||
850 'balance type: '
851 || p_control_attribute_rec.balance_type ||', '||
852 'journal source: '
853 || p_control_attribute_rec.journal_source ||', '||
854 'journal category: '
855 || p_control_attribute_rec.journal_category ||', '||
856 'acct entry type: '
857 || p_control_attribute_rec.accounting_entry_type ||', '||
858 'acct event type: '
859 || p_control_attribute_rec.accounting_event_type ||', '||
860 'doc category: '
861 || p_control_attribute_rec.document_category ||', '||
862 'p_suppress_error: '|| p_suppress_error ||', '||
863 'SQLERRM: ' || SQLERRM);
864 END IF;
865 --
866 -- Raise Exception
867 --
868 app_exception.raise_exception;
869 END Get_Sequence_Number_No_Commit;
870
871 --
872 -- Get Sequence Number (without Autonmous Commit)
873 --
874 PROCEDURE Get_Sequence_Number_Commit(
875 p_context_type IN VARCHAR2,
876 p_context_value IN VARCHAR2,
877 p_application_id IN NUMBER,
878 p_table_name IN VARCHAR2,
879 p_event_code IN VARCHAR2,
880 p_control_attribute_rec IN control_attribute_rec_type,
881 p_control_date_tbl IN control_date_tbl_type,
882 p_suppress_error IN VARCHAR2,
883 x_seq_version_id OUT NOCOPY NUMBER,
884 x_sequence_number OUT NOCOPY NUMBER,
885 x_assignment_id OUT NOCOPY NUMBER,
886 x_error_code OUT NOCOPY VARCHAR2) IS
887 PRAGMA AUTONOMOUS_TRANSACTION;
888 BEGIN
889
890 get_sequence_number_no_commit (
891 p_context_type => p_context_type,
892 p_context_value => p_context_value,
893 p_application_id => p_application_id,
894 p_table_name => p_table_name,
895 p_event_code => p_event_code,
896 p_control_attribute_rec => p_control_attribute_rec,
897 p_control_date_tbl => p_control_date_tbl,
898 p_suppress_error => p_suppress_error,
899 x_seq_version_id => x_seq_version_id,
900 x_sequence_number => x_sequence_number,
901 x_assignment_id => x_assignment_id,
902 x_error_code => x_error_code);
903
904 COMMIT;
905
906 END Get_Sequence_Number_Commit;
907
908 --
909 --
910 --
911
912 --
913 -- Get Sequencing Context Information with Cache
914 -- (For Batch Mode only)
915 --
916 PROCEDURE get_cached_context_info (
917 p_context_info_rec IN context_info_rec_type,
918 x_context_ctrl_rec OUT NOCOPY context_ctrl_rec_type) IS
919 l_context_ctrl_rec context_ctrl_rec_type;
920 l_sc_cache_index BINARY_INTEGER;
921 BEGIN
922
923 --
924 -- Find Index of Cached Sequencing Context Information
925 --
926 l_sc_cache_index := find_seq_context_in_cache (
927 p_context_info_rec => p_context_info_rec);
928 --
929 -- If the Sequencing Context is in the cache, ..
930 --
931 IF l_sc_cache_index < g_sc_cache_size THEN
932 --
933 -- Get Sequencing Context Control Information from Cache
934 --
935 x_context_ctrl_rec := g_context_ctrl_tbl(l_sc_cache_index);
936 ELSE
937 --
938 -- Get the Control Information from the Database
939 --
940 l_context_ctrl_rec := find_seq_context_in_db (
941 p_context_info_rec => p_context_info_rec);
942 --
946 --
943 -- If the Sequencing Context exists in DB,...
944 --
945 IF l_context_ctrl_rec.seq_context_id IS NOT NULL THEN
947 -- Put the record of Sequencing Context and Control information
948 -- in Cache.
949 --
950 g_context_info_tbl(g_sc_cache_size) := p_context_info_rec;
951 g_context_ctrl_tbl(g_sc_cache_size) := l_context_ctrl_rec;
952 --
953 -- Increase the cache size by 1
954 --
955 g_sc_cache_size := g_sc_cache_size + 1;
956 --
957 -- Return Seq_Context_Id, Req_Assign_Flag, and Date_Type
958 --
959 x_context_ctrl_rec := l_context_ctrl_rec;
960 END IF;
961 END IF;
962
963 EXCEPTION
964 WHEN OTHERS THEN
965 app_exception.raise_exception;
966 END get_cached_context_info;
967
968 --
969 -- Retrieve Assigned Sequences of Assignment / Exception
970 --
971 PROCEDURE get_assigned_sequence_header (
972 p_seq_context_id IN NUMBER,
973 p_control_attribute_rec IN control_attribute_rec_type,
974 p_control_date_value IN DATE,
975 p_request_id IN NUMBER,
976 x_assignment_id OUT NOCOPY NUMBER,
977 x_sequence_type OUT NOCOPY VARCHAR2,
978 x_seq_header_id OUT NOCOPY NUMBER)
979 IS
980
981 l_assignment_id fun_seq_assignments.assignment_id%TYPE;
982 l_sequence_type fun_seq_headers.gapless_flag%TYPE;
983 l_seq_header_id fun_seq_headers.seq_header_id%TYPE;
984
985 l_exp_assignment_id fun_seq_assignments.assignment_id%TYPE;
986 l_exp_sequence_type fun_seq_headers.gapless_flag%TYPE;
987 l_exp_seq_header_id fun_seq_headers.seq_header_id%TYPE;
988
989 BEGIN
990 --
991 -- Get Sequence Info of Assignment
992 --
993 get_seq_header_assignment(
994 p_seq_context_id => p_seq_context_id,
995 p_control_attribute_rec => p_control_attribute_rec,
996 p_control_date_value => p_control_date_value,
997 p_request_id => p_request_id,
998 x_assignment_id => l_assignment_id, -- OUT
999 x_sequence_type => l_sequence_type, -- OUT
1000 x_seq_header_id => l_seq_header_id); -- OUT
1001 --
1002 -- If no sequence assignment is found,
1003 -- Exit the routine.
1004 --
1005 IF l_assignment_id IS NULL THEN
1006 RETURN;
1007 ELSE
1008 --
1009 -- If sequence assignment id is found,
1010 -- check if exceptions exist for the assignment.
1011 --
1012 -- fun_seq_utils.log_procedure(
1013 -- p_module => l_module || '.' || 'in progress',
1014 -- p_message_text => 'Beginning of get_seq_header_exception');
1015 --
1016 get_seq_header_exception(
1017 p_assignment_id => l_assignment_id,
1018 p_control_attribute_rec => p_control_attribute_rec,
1019 p_control_date_value => p_control_date_value,
1020 p_request_id => p_request_id,
1021 x_exp_assignment_id => l_exp_assignment_id,
1022 x_exp_sequence_type => l_exp_sequence_type,
1023 x_exp_seq_header_id => l_exp_seq_header_id);
1024 --
1025 --fun_seq_utils.log_procedure(
1026 -- p_module => l_module || '.' || 'in progress',
1027 -- p_message_text => 'End of get_seq_header_exception');
1028 --
1029 END IF;
1030 --
1031 -- Return Assignment Id and Sequence Header Information
1032 --
1033 IF l_exp_assignment_id IS NULL THEN
1034 x_assignment_id := l_assignment_id;
1035 x_sequence_type := l_sequence_type;
1036 x_seq_header_id := l_seq_header_id;
1037 ELSE
1038 x_assignment_id := l_exp_assignment_id;
1039 x_sequence_type := l_exp_sequence_type;
1040 x_seq_header_id := l_exp_seq_header_id;
1041 END IF;
1042 EXCEPTION
1043 WHEN OTHERS THEN
1044 app_exception.raise_exception;
1045 END get_assigned_sequence_header;
1046
1047 --
1048 -- Retrieve Assignment Information of Intercompany Transactions
1049 --
1050 PROCEDURE get_ic_assigned_seq_header (
1051 p_seq_context_id IN NUMBER,
1052 p_control_date_value IN DATE,
1053 p_request_id IN NUMBER,
1054 x_assignment_id OUT NOCOPY NUMBER,
1055 x_sequence_type OUT NOCOPY VARCHAR2,
1056 x_seq_header_id OUT NOCOPY NUMBER) IS
1057 BEGIN
1058 --
1059 -- IF p_request_id IS NULL THEN
1060 --
1061 SELECT sa.assignment_id,
1062 sa.seq_header_id,
1063 sh.gapless_flag
1064 INTO x_assignment_id,
1065 x_seq_header_id,
1066 x_sequence_type
1067 FROM fun_seq_assignments sa, fun_seq_headers sh
1068 WHERE sa.seq_context_id = p_seq_context_id
1069 AND sa.seq_header_id = sh.seq_header_id
1070 AND sh.obsolete_flag = 'N'
1071 AND sa.link_to_assignment_id IS NULL
1072 AND sa.start_date <= p_control_date_value
1073 AND sa.use_status_code IN ('NEW','USED')
1074 AND p_control_date_value <= NVL(sa.end_date, p_control_date_value + 1);
1075 -- END IF;
1076 EXCEPTION
1077 WHEN OTHERS THEN
1078 app_exception.raise_exception;
1079 END get_ic_assigned_seq_header;
1080
1081 --
1082 -- Retrieve Assignment Id and its Sequence Header Id of Assignments.
1083 -- Note:
1084 -- Called from Get_Assigned_Sequence
1085 -- Product team should not call this procedure directly.
1086 --
1087 PROCEDURE get_seq_header_assignment(
1088 p_seq_context_id IN NUMBER,
1089 p_control_attribute_rec IN control_attribute_rec_type,
1090 p_control_date_value IN DATE,
1094 x_seq_header_id OUT NOCOPY NUMBER)
1091 p_request_id IN NUMBER,
1092 x_assignment_id OUT NOCOPY NUMBER,
1093 x_sequence_type OUT NOCOPY VARCHAR2,
1095 IS
1096 l_assignment_id fun_seq_assignments.assignment_id%TYPE;
1097 l_seq_header_id fun_seq_headers.seq_header_id%TYPE;
1098 l_sequence_type fun_seq_headers.gapless_flag%TYPE;
1099
1100 l_assign_info_rec assign_info_rec_type;
1101 l_assign_seq_head_rec assign_seq_head_rec_type;
1102 BEGIN
1103 IF g_use_cache_flag = FALSE THEN
1104 -- For Online Transactions, issue a Pessimistic Lock
1105 SELECT sa.assignment_id,
1106 sa.seq_header_id,
1107 sh.gapless_flag
1108 INTO x_assignment_id,
1109 x_seq_header_id,
1110 x_sequence_type
1111 FROM fun_seq_assignments sa, fun_seq_headers sh
1112 WHERE sa.seq_context_id = p_seq_context_id
1113 AND sa.seq_header_id = sh.seq_header_id (+) -- (+) Do Not Sequence
1114 AND sh.obsolete_flag (+) = 'N'
1115 AND sa.link_to_assignment_id IS NULL
1116 AND sa.start_date <= p_control_date_value
1117 AND sa.use_status_code IN ('NEW','USED')
1118 AND p_control_date_value <= NVL(sa.end_date, p_control_date_value + 1)
1119 AND NVL(sa.balance_type, '@NULL@') =
1120 NVL2(sa.balance_type,
1121 p_control_attribute_rec.balance_type, '@NULL@')
1122 AND NVL(sa.journal_source, '@NULL@') =
1123 NVL2(sa.journal_source,
1124 p_control_attribute_rec.journal_source, '@NULL@')
1125 AND NVL(sa.journal_category, '@NULL@') =
1126 NVL2(sa.journal_category,
1127 p_control_attribute_rec.journal_category, '@NULL@')
1128 AND NVL(sa.document_category, '@NULL@') =
1129 NVL2(sa.document_category,
1130 p_control_attribute_rec.document_category, '@NULL@')
1131 AND NVL(sa.accounting_event_type, '@NULL@') =
1132 NVL2(sa.accounting_event_type,
1133 p_control_attribute_rec.accounting_event_type, '@NULL@')
1134 AND NVL(sa.accounting_entry_type, '@NULL@') =
1135 NVL2(sa.accounting_entry_type,
1136 p_control_attribute_rec.accounting_entry_type, '@NULL@')
1137 FOR UPDATE;
1138 ELSE
1139 l_assign_info_rec.seq_context_id := p_seq_context_id;
1140 l_assign_info_rec.ctrl_attr_rec := p_control_attribute_rec;
1141 l_assign_info_rec.control_date := p_control_date_value;
1142 --
1143 -- Get Assigned Sequence information from Cache
1144 --
1145 get_cached_seq_header_assign (
1146 p_assign_info_rec => l_assign_info_rec,
1147 x_assign_seq_head_rec => l_assign_seq_head_rec);
1148
1149 x_assignment_id := l_assign_seq_head_rec.assignment_id;
1150 x_seq_header_id := l_assign_seq_head_rec.seq_header_id;
1151 x_sequence_type := l_assign_seq_head_rec.seq_type;
1152 END IF;
1153 EXCEPTION
1154 WHEN NO_DATA_FOUND THEN
1155 x_assignment_id := NULL;
1156 WHEN OTHERS THEN
1157 app_exception.raise_exception;
1158 END get_seq_header_assignment;
1159
1160 --
1161 -- Retrieve Assignment Id and Sequence Header Id from the Cache
1162 -- Note:
1163 -- Called from Get_Seq_Header_Assignment
1164 --
1165 PROCEDURE get_cached_seq_header_assign (
1166 p_assign_info_rec IN assign_info_rec_type,
1167 x_assign_seq_head_rec OUT NOCOPY assign_seq_head_rec_type) IS
1168
1169 l_assign_seq_head_rec assign_seq_head_rec_type;
1170 l_as_cache_index BINARY_INTEGER;
1171 BEGIN
1172 --
1173 -- Find Index of Cached Assigned Sequence Information
1174 --
1175 l_as_cache_index := find_seq_head_assign_in_cache (
1176 p_assign_info_rec => p_assign_info_rec);
1177 --
1178 -- If the Assigned Sequence Information is in the cache, ..
1179 --
1180 IF l_as_cache_index < g_as_cache_size THEN
1181 --
1182 -- Get Assigned Sequence Information from Cache
1183 --
1184 x_assign_seq_head_rec := g_assign_seq_head_tbl(l_as_cache_index);
1185 ELSE
1186 --
1187 -- Get the Assigned Sequence Information from the Database
1188 --
1189 l_assign_seq_head_rec := find_seq_head_assign_in_db (
1190 p_assign_info_rec => p_assign_info_rec);
1191 --
1192 -- If the Assigned Sequence Information exists in DB,...
1193 --
1194 IF l_assign_seq_head_rec.assignment_id IS NOT NULL THEN
1195 --
1196 -- Put the record of Assignment and Sequence Header information
1197 -- in Cache.
1198 --
1199 g_assign_info_tbl(g_as_cache_size).seq_context_id
1200 := p_assign_info_rec.seq_context_id;
1201 g_assign_info_tbl(g_as_cache_size).control_date
1202 := p_assign_info_rec.control_date;
1203 g_assign_info_tbl(g_as_cache_size).ctrl_attr_rec.balance_type
1204 := p_assign_info_rec.ctrl_attr_rec.balance_type;
1205 g_assign_info_tbl(g_as_cache_size).ctrl_attr_rec.journal_source
1206 := p_assign_info_rec.ctrl_attr_rec.journal_source;
1207 g_assign_info_tbl(g_as_cache_size).ctrl_attr_rec.journal_category
1208 := p_assign_info_rec.ctrl_attr_rec.journal_category;
1209 g_assign_info_tbl(g_as_cache_size).ctrl_attr_rec.document_category
1210 := NVL(p_assign_info_rec.ctrl_attr_rec.document_category,-1);
1211 g_assign_info_tbl(g_as_cache_size).ctrl_attr_rec.accounting_event_type
1212 := NVL(p_assign_info_rec.ctrl_attr_rec.accounting_event_type, -1);
1213 g_assign_info_tbl(g_as_cache_size).ctrl_attr_rec.accounting_entry_type
1214 := NVL(p_assign_info_rec.ctrl_attr_rec.accounting_entry_type, -1);
1215
1216 g_assign_seq_head_tbl(g_as_cache_size) := l_assign_seq_head_rec;
1217 --
1221 --
1218 -- Increase the cache size by 1
1219 --
1220 g_as_cache_size := g_as_cache_size + 1;
1222 -- Return Assignment Id, Sequence Header Id, and
1223 -- Sequence Type
1224 --
1225 x_assign_seq_head_rec := l_assign_seq_head_rec;
1226 END IF;
1227 END IF;
1228
1229 EXCEPTION
1230 WHEN OTHERS THEN
1231 app_exception.raise_exception;
1232 END get_cached_seq_header_assign;
1233 --
1234 -- Retrieve Assignment Id and its Sequence Header Id of Exceptions.
1235 -- Note:
1236 -- Called from Get_Assigned_Sequence
1237 -- Product team should not call this procedure directly.
1238 --
1239 PROCEDURE get_seq_header_exception(
1240 p_assignment_id IN NUMBER,
1241 p_control_attribute_rec IN control_attribute_rec_type,
1242 p_control_date_value IN DATE,
1243 p_request_id IN NUMBER,
1244 x_exp_assignment_id OUT NOCOPY NUMBER,
1245 x_exp_sequence_type OUT NOCOPY VARCHAR2,
1246 x_exp_seq_header_id OUT NOCOPY NUMBER) IS
1247
1248 -- TODO: Check the cursor SQL. What if all the passed control attributes
1249 -- are null?
1250
1251 TYPE Seq_Header_Type IS REF CURSOR;
1252 l_CursorVar Seq_Header_Type;
1253
1254 l_exp_info_rec exp_info_rec_type;
1255 l_exp_seq_head_rec assign_seq_head_rec_type;
1256
1257 BEGIN
1258 --
1259 -- Online
1260 --
1261 IF g_use_cache_flag = FALSE THEN
1262 OPEN l_CursorVar FOR
1263 SELECT xsa.assignment_id,
1264 xsh.gapless_flag,
1265 xsa.seq_header_id
1266 FROM fun_seq_assignments xsa, fun_seq_headers xsh
1267 WHERE xsa.link_to_assignment_id = p_assignment_id
1268 AND xsa.start_date <= p_control_date_value
1269 AND p_control_date_value <= NVL(xsa.end_date, p_control_date_value + 1)
1270 AND xsa.use_status_code IN ('NEW','USED')
1271 AND xsa.seq_header_id = xsh.seq_header_id (+) -- Do Not Sequence
1272 AND xsh.obsolete_flag (+) = 'N'
1273 AND (xsa.balance_type IS NULL OR
1274 xsa.balance_type = p_control_attribute_rec.balance_type)
1275 AND (xsa.journal_source IS NULL OR
1276 xsa.journal_source = p_control_attribute_rec.journal_source)
1277 AND (xsa.journal_category IS NULL OR
1278 xsa.journal_category = p_control_attribute_rec.journal_category)
1279 AND (xsa.document_category IS NULL OR
1280 xsa.document_category
1281 = p_control_attribute_rec.document_category)
1282 AND (xsa.accounting_event_type IS NULL OR
1283 xsa.accounting_event_type
1284 = p_control_attribute_rec.accounting_event_type)
1285 AND (xsa.accounting_entry_type IS NULL OR
1286 xsa.accounting_entry_type
1287 = p_control_attribute_rec.accounting_entry_type)
1288 ORDER BY xsa.priority
1289 FOR UPDATE;
1290 --
1291 -- Loop - Beginning
1292 --
1293 LOOP
1294 FETCH l_CursorVar
1295 INTO x_exp_assignment_id,
1296 x_exp_sequence_type,
1297 x_exp_seq_header_id;
1298 --
1299 -- Assignments are already ordered by Priority.
1300 -- The first fetched row should be the one returned to a caller.
1301 --
1302 IF (l_CursorVar%NOTFOUND) OR (l_CursorVar%ROWCOUNT = 1) THEN
1303 EXIT;
1304 END IF;
1305 END LOOP;
1306 CLOSE l_CursorVar;
1307 --
1308 -- Batch (Use Cache)
1309 --
1310 ELSE
1311 -- This ID is for the Parent Assignment not for Exception
1312 l_exp_info_rec.assignment_id := p_assignment_id;
1313 l_exp_info_rec.ctrl_attr_rec := p_control_attribute_rec;
1314 l_exp_info_rec.control_date := p_control_date_value;
1315 --
1316 -- Get Assigned Sequence information from Cache
1317 --
1318 get_cached_seq_header_exp (
1319 p_exp_info_rec => l_exp_info_rec,
1320 x_exp_seq_head_rec => l_exp_seq_head_rec);
1321 --
1322 -- Set OUT variables
1323 --
1324 x_exp_assignment_id := l_exp_seq_head_rec.assignment_id;
1325 x_exp_sequence_type := l_exp_seq_head_rec.seq_type;
1326 x_exp_seq_header_id := l_exp_seq_head_rec.seq_header_id;
1327 END IF;
1328
1329 EXCEPTION
1330 WHEN NO_DATA_FOUND THEN
1331 x_exp_assignment_id := NULL;
1332 x_exp_sequence_type := NULL;
1333 x_exp_seq_header_id := NULL;
1334 WHEN OTHERS THEN
1335 app_exception.raise_exception;
1336 END get_seq_header_exception;
1337
1338 --
1339 -- Get_Seq_Context_Name
1340 -- (for debug)
1341 FUNCTION get_seq_context_name (
1342 p_seq_context_id IN NUMBER) RETURN VARCHAR2 IS
1343 l_seq_context_name fun_seq_contexts.name%TYPE;
1344 BEGIN
1345 SELECT name
1346 INTO l_seq_context_name
1347 FROM fun_seq_contexts
1348 WHERE seq_context_id = p_seq_context_id;
1349
1350 RETURN l_seq_context_name;
1351 END get_seq_context_name;
1352
1353 --
1354 -- Get_Seq_Header_Name
1355 -- (for debug)
1356 FUNCTION get_seq_header_name (
1357 p_seq_header_id IN NUMBER) RETURN VARCHAR2 IS
1358 l_seq_header_name fun_seq_headers.header_name%TYPE;
1359 BEGIN
1360 SELECT header_name
1361 INTO l_seq_header_name
1362 FROM fun_seq_headers
1363 WHERE seq_header_id = p_seq_header_id;
1364
1365 RETURN l_seq_header_name;
1366 END get_seq_header_name;
1367
1368 --
1369 -- Retrieve Assignment Id and Sequence Header Id from the Cache
1370 -- Note:
1371 -- Called from Get_Seq_Header_Exception
1372 --
1373 PROCEDURE get_cached_seq_header_exp (
1377 l_exp_seq_head_rec assign_seq_head_rec_type;
1374 p_exp_info_rec IN exp_info_rec_type,
1375 x_exp_seq_head_rec OUT NOCOPY assign_seq_head_rec_type) IS
1376
1378 l_exp_cache_index BINARY_INTEGER;
1379 BEGIN
1380 --
1381 -- Find Index of Cached Assigned Sequence Information
1382 --
1383 l_exp_cache_index := find_seq_head_exp_in_cache (
1384 p_exp_info_rec => p_exp_info_rec);
1385 --
1386 -- If the Assigned Sequence Information is in the cache, ..
1387 --
1388 IF l_exp_cache_index < g_exp_cache_size THEN
1389 --
1390 -- Get Assigned Sequence Information from Cache
1391 --
1392 x_exp_seq_head_rec := g_exp_seq_head_tbl(l_exp_cache_index);
1393 ELSE
1394 --
1395 -- Get the Assigned Sequence Information from the Database
1396 --
1397 -- ** This Assignment ID is for Exception Line
1398 l_exp_seq_head_rec := find_seq_head_exp_in_db (
1399 p_exp_info_rec => p_exp_info_rec);
1400 --
1401 -- If the Assigned Sequence Information exists in DB,...
1402 --
1403 IF l_exp_seq_head_rec.assignment_id IS NOT NULL THEN
1404 --
1405 -- Put the record of Assignment and Sequence Header information
1406 -- in Cache.
1407 --
1408 -- This ID is for the parent Assignment not Exception
1409 g_exp_info_tbl(g_exp_cache_size).assignment_id
1410 := p_exp_info_rec.assignment_id;
1411 --
1412 g_exp_info_tbl(g_exp_cache_size).control_date
1413 := p_exp_info_rec.control_date;
1414 g_exp_info_tbl(g_exp_cache_size).ctrl_attr_rec.balance_type
1415 := p_exp_info_rec.ctrl_attr_rec.balance_type;
1416 g_exp_info_tbl(g_exp_cache_size).ctrl_attr_rec.journal_source
1417 := p_exp_info_rec.ctrl_attr_rec.journal_source;
1418 g_exp_info_tbl(g_exp_cache_size).ctrl_attr_rec.journal_category
1419 := p_exp_info_rec.ctrl_attr_rec.journal_category;
1420 g_exp_info_tbl(g_exp_cache_size).ctrl_attr_rec.document_category
1421 := NVL(p_exp_info_rec.ctrl_attr_rec.document_category, -1);
1422 g_exp_info_tbl(g_exp_cache_size).ctrl_attr_rec.accounting_event_type
1423 := NVL(p_exp_info_rec.ctrl_attr_rec.accounting_event_type, -1);
1424 g_exp_info_tbl(g_exp_cache_size).ctrl_attr_rec.accounting_entry_type
1425 := NVL(p_exp_info_rec.ctrl_attr_rec.accounting_entry_type, -1);
1426
1427 g_exp_seq_head_tbl(g_exp_cache_size) := l_exp_seq_head_rec;
1428 --
1429 -- Increase the cache size by 1
1430 --
1431 g_exp_cache_size := g_exp_cache_size + 1;
1432 --
1433 -- Return Assignment Id, Sequence Header Id, and
1434 -- Sequence Type
1435 --
1436 x_exp_seq_head_rec := l_exp_seq_head_rec;
1437 END IF;
1438 END IF;
1439
1440 EXCEPTION
1441 WHEN OTHERS THEN
1442 app_exception.raise_exception;
1443 END get_cached_seq_header_exp;
1444
1445 -- Program Name: Get_Seq_Version
1446 -- Description:
1447 -- Retrieve an Active Sequence Version
1448 --
1449 PROCEDURE get_seq_version (
1450 p_sequence_type IN VARCHAR2,
1451 p_seq_header_id IN NUMBER,
1452 p_control_date_value IN DATE,
1453 p_request_id IN NUMBER,
1454 x_seq_version_id OUT NOCOPY NUMBER) IS
1455 BEGIN
1456 IF g_use_cache_flag = FALSE THEN
1457 -- For Online Transactions, issue Pessimistic Lock
1458 SELECT sv.seq_version_id
1459 INTO x_seq_version_id
1460 FROM fun_seq_versions sv
1461 WHERE sv.seq_header_id = p_seq_header_id
1462 AND sv.start_date <= p_control_date_value
1463 AND p_control_date_value <= NVL(sv.end_date, p_control_date_value + 1)
1464 AND sv.use_status_code IN ('NEW','USED')
1465 FOR UPDATE;
1466 ELSE -- No Lock is necessary, assuming Setup Pages become read-only.
1467 SELECT sv.seq_version_id
1468 INTO x_seq_version_id
1469 FROM fun_seq_versions sv
1470 WHERE sv.seq_header_id = p_seq_header_id
1471 AND sv.start_date <= p_control_date_value
1472 AND p_control_date_value <= NVL(sv.end_date, p_control_date_value + 1)
1473 AND sv.use_status_code IN ('NEW','USED');
1474 END IF;
1475 EXCEPTION
1476 WHEN NO_DATA_FOUND THEN
1477 fnd_message.set_name ('FUN','FUN_SEQ_NO_ACTIVE_SEQ_FOUND');
1478 fnd_message.set_token ('SEQ_NAME',get_seq_header_name(p_seq_header_id));
1479 --
1480 -- Cannot suppress this exception
1481 --
1482 app_exception.raise_exception;
1483 END get_seq_version;
1484
1485 --
1486 --
1487 --
1488 FUNCTION get_control_date_value (
1489 p_control_date_type IN VARCHAR2,
1490 p_control_dates IN control_date_tbl_type) RETURN VARCHAR2
1491 IS
1492 l_control_date_value DATE;
1493 BEGIN
1494 FOR i IN p_control_dates.FIRST .. p_control_dates.LAST LOOP
1495 l_control_date_value:= p_control_dates(i).date_value;
1496 EXIT WHEN p_control_dates(i).date_type = p_control_date_type;
1497 END LOOP;
1498
1499 RETURN l_control_date_value;
1500 EXCEPTION
1501 WHEN OTHERS THEN
1502 app_exception.raise_exception;
1503 END get_control_date_value;
1504
1505 --
1506 -- Updat_Gapless_Status
1507 --
1508 PROCEDURE update_gapless_status (
1509 p_assignment_id IN NUMBER,
1510 p_seq_version_id IN NUMBER) IS
1511 BEGIN
1512 --
1513 -- Update the Status of Versions
1514 --
1515 update_seq_ver_status(p_seq_version_id => p_seq_version_id);
1516 --
1517 -- Update the Status of Assignment and Exception
1518 -- For Exceptions, Update the status of its Parent Assignment
1519 --
1523 app_exception.raise_exception;
1520 update_assign_status (p_assignment_id => p_assignment_id);
1521 EXCEPTION
1522 WHEN OTHERS THEN
1524 END update_gapless_status;
1525
1526 --
1527 -- Update the Status of Versions with Database Sequence
1528 --
1529 PROCEDURE update_db_status (
1530 p_assignment_id IN NUMBER,
1531 p_seq_version_id IN NUMBER) IS
1532 --
1533 -- This will cause deadlock if Version is Locked
1534 --
1535 PRAGMA AUTONOMOUS_TRANSACTION;
1536 BEGIN
1537 --
1538 -- Update the Status of Assignment and Exception
1539 -- For Exceptions, Update the status of its Parent Assignment
1540 --
1541 update_assign_status (p_assignment_id => p_assignment_id);
1542 --
1543 -- Update the Status of Versions
1544 --
1545 update_seq_ver_status(p_seq_version_id => p_seq_version_id);
1546 --
1547 COMMIT;
1548 EXCEPTION
1549 WHEN OTHERS THEN
1550 app_exception.raise_exception;
1551 END update_db_status;
1552
1553 --
1554 -- Update_Assign_Status
1555 --
1556 -- Update the status of the Assignment
1557 --
1558 PROCEDURE update_assign_status (
1559 p_assignment_id IN NUMBER) IS
1560
1561 TYPE assign_id_tbl_type IS TABLE OF fun_seq_assignments.assignment_id%TYPE
1562 INDEX BY BINARY_INTEGER;
1563
1564 l_assign_id_tbl assign_id_tbl_type;
1565 BEGIN
1566 SELECT assignment_id
1567 BULK COLLECT
1568 INTO l_assign_id_tbl
1569 FROM fun_seq_assignments
1570 WHERE use_status_code = 'NEW'
1571 AND ((assignment_id = p_assignment_id) OR
1572 (assignment_id = (SELECT ex.link_to_assignment_id
1573 FROM fun_seq_assignments ex
1574 WHERE ex.assignment_id = p_assignment_id)));
1575 --
1576 -- When no data is found, then l_assign_id_tbl.count = 0
1577 --
1578 IF l_assign_id_tbl.COUNT > 0 THEN
1579 FORALL i in l_assign_id_tbl.FIRST .. l_assign_id_tbl.LAST
1580 UPDATE fun_seq_assignments
1581 SET use_status_code = 'USED'
1582 WHERE assignment_id = l_assign_id_tbl(i);
1583 END IF;
1584 EXCEPTION
1585 WHEN OTHERS THEN
1586 app_exception.raise_exception;
1587 END update_assign_status;
1588 --
1589 -- Update_Seq_Ver_Status
1590 --
1591 -- Update the status of the Sequence Version
1592 --
1593 PROCEDURE update_seq_ver_status (
1594 p_seq_version_id IN NUMBER) IS
1595
1596 l_seq_version_id fun_seq_versions.seq_version_id%TYPE;
1597 BEGIN
1598 --
1599 -- Check if we need to update the status of the Version
1600 --
1601 SELECT seq_version_id
1602 INTO l_seq_version_id
1603 FROM fun_seq_versions
1604 WHERE seq_version_id = p_seq_version_id
1605 AND use_status_code = 'NEW';
1606 --
1607 -- Update status of the Version
1608 --
1609 UPDATE fun_seq_versions
1610 SET use_status_code = 'USED'
1611 WHERE seq_version_id = l_seq_version_id;
1612 EXCEPTION
1613 WHEN NO_DATA_FOUND THEN
1614 NULL;
1615 WHEN OTHERS THEN
1616 app_exception.raise_exception;
1617 END update_seq_ver_status;
1618 --
1619 -- Find a Sequencing Context in the Cache
1620 --
1621 FUNCTION find_seq_context_in_cache(
1622 p_context_info_rec IN context_info_rec_type) RETURN BINARY_INTEGER IS
1623
1624 l_sc_index BINARY_INTEGER;
1625 l_found BOOLEAN;
1626 BEGIN
1627 l_sc_index := 0;
1628 l_found := FALSE;
1629
1630 WHILE (l_sc_index < g_sc_cache_size) AND (NOT l_found) LOOP
1631 --
1632 -- g_context_info_tbl(l_sc_index) = p_context_info_rec gives an error.
1633 --
1634 IF g_context_info_tbl(l_sc_index).application_id =
1635 p_context_info_rec.application_id AND
1636 g_context_info_tbl(l_sc_index).table_name =
1637 p_context_info_rec.table_name AND
1638 g_context_info_tbl(l_sc_index).context_type =
1639 p_context_info_rec.context_type AND
1640 g_context_info_tbl(l_sc_index).context_value =
1641 p_context_info_rec.context_value AND
1642 g_context_info_tbl(l_sc_index).event_code =
1643 p_context_info_rec.event_code
1644 THEN
1645 l_found := TRUE;
1646 ELSE
1647 l_sc_index := l_sc_index + 1;
1648 END IF;
1649 END LOOP;
1650
1651 RETURN l_sc_index;
1652 END find_seq_context_in_cache;
1653
1654 --
1655 -- Find a Sequencing Context in the database
1656 --
1657 FUNCTION find_seq_context_in_db(
1658 p_context_info_rec IN context_info_rec_type) RETURN context_ctrl_rec_type IS
1659
1660 l_context_ctrl_rec context_ctrl_rec_type;
1661 BEGIN
1662 SELECT sac.seq_context_id,
1663 sac.date_type,
1664 sac.require_assign_flag,
1665 sac.sort_option
1666 INTO l_context_ctrl_rec.seq_context_id,
1667 l_context_ctrl_rec.date_type,
1668 l_context_ctrl_rec.req_assign_flag,
1669 l_context_ctrl_rec.sort_option_code
1670 FROM fun_seq_contexts sac
1671 WHERE sac.application_id = p_context_info_rec.application_id
1672 AND sac.table_name = p_context_info_rec.table_name
1673 AND sac.context_type = p_context_info_rec.context_type
1674 AND sac.context_value = p_context_info_rec.context_value
1675 AND sac.event_code = p_context_info_rec.event_code
1676 AND sac.obsolete_flag = 'N';
1677
1678 RETURN l_context_ctrl_rec;
1679 EXCEPTION
1680 WHEN NO_DATA_FOUND THEN
1681 --
1682 -- No Sequencing is required
1683 --
1684 l_context_ctrl_rec := NULL;
1685 RETURN l_context_ctrl_rec;
1686 WHEN OTHERS THEN
1687 app_exception.raise_exception;
1691 -- Find an Assigned Sequence Header in the Cache
1688 END find_seq_context_in_db;
1689
1690 --
1692 --
1693 FUNCTION find_seq_head_assign_in_cache(
1694 p_assign_info_rec IN assign_info_rec_type)
1695 RETURN BINARY_INTEGER IS
1696
1697 l_as_index BINARY_INTEGER;
1698 l_found BOOLEAN;
1699 BEGIN
1700 l_as_index := 0;
1701 l_found := FALSE;
1702
1703 --
1704 -- *** Revisit DATE evaliation for better performance
1705 -- *** May have better use range validation
1706 --
1707 WHILE (l_as_index < g_as_cache_size) AND (NOT l_found) LOOP
1708 IF g_assign_info_tbl(l_as_index).seq_context_id
1709 = p_assign_info_rec.seq_context_id AND
1710 g_assign_info_tbl(l_as_index).control_date
1711 = p_assign_info_rec.control_date AND
1712 g_assign_info_tbl(l_as_index).ctrl_attr_rec.balance_type
1713 = p_assign_info_rec.ctrl_attr_rec.balance_type AND
1714 g_assign_info_tbl(l_as_index).ctrl_attr_rec.journal_source
1715 = p_assign_info_rec.ctrl_attr_rec.journal_source AND
1716 g_assign_info_tbl(l_as_index).ctrl_attr_rec.journal_category
1717 = p_assign_info_rec.ctrl_attr_rec.journal_category AND
1718 g_assign_info_tbl(l_as_index).ctrl_attr_rec.document_category
1719 = NVL(p_assign_info_rec.ctrl_attr_rec.document_category,-1) AND
1720 g_assign_info_tbl(l_as_index).ctrl_attr_rec.accounting_event_type
1721 = NVL(p_assign_info_rec.ctrl_attr_rec.accounting_event_type,-1) AND
1722 g_assign_info_tbl(l_as_index).ctrl_attr_rec.accounting_entry_type
1723 = NVL(p_assign_info_rec.ctrl_attr_rec.accounting_entry_type,-1)
1724 THEN
1725 l_found := TRUE;
1726 ELSE
1727 l_as_index := l_as_index + 1;
1728 END IF;
1729 END LOOP;
1730
1731 RETURN l_as_index;
1732 EXCEPTION
1733 WHEN OTHERS THEN
1734 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1735 fnd_log.string(
1736 log_level => fnd_log.level_exception,
1737 module => 'fun.plsql.fun_seq.find_seq_head_assign_in_cache',
1738 message => 'SQLERRM: ' || SQLERRM);
1739 END IF;
1740 END find_seq_head_assign_in_cache;
1741
1742 --
1743 -- Find an Assigned Sequence Header Information
1744 --
1745 FUNCTION find_seq_head_assign_in_db (
1746 p_assign_info_rec IN assign_info_rec_type)
1747 RETURN assign_seq_head_rec_type IS
1748
1749 l_assign_seq_head_rec assign_seq_head_rec_type;
1750 l_rec assign_info_rec_type;
1751 BEGIN
1752 l_rec := p_assign_info_rec;
1753 SELECT sa.assignment_id,
1754 sa.seq_header_id,
1755 sh.gapless_flag
1756 INTO l_assign_seq_head_rec.assignment_id,
1757 l_assign_seq_head_rec.seq_header_id,
1758 l_assign_seq_head_rec.seq_type
1759 FROM fun_seq_assignments sa, fun_seq_headers sh
1760 WHERE sa.seq_context_id = l_rec.seq_context_id
1761 AND sa.seq_header_id = sh.seq_header_id (+) -- Do Not Sequence
1762 AND sh.obsolete_flag (+) = 'N'
1763 AND sa.link_to_assignment_id IS NULL
1764 AND sa.start_date <= l_rec.control_date
1765 AND sa.use_status_code IN ('NEW','USED')
1766 AND l_rec.control_date <= NVL(sa.end_date, l_rec.control_date + 1)
1767 AND NVL(sa.balance_type, '@NULL@') =
1768 NVL2(sa.balance_type,
1769 l_rec.ctrl_attr_rec.balance_type, '@NULL@')
1770 AND NVL(sa.journal_source, '@NULL@') =
1771 NVL2(sa.journal_source,
1772 l_rec.ctrl_attr_rec.journal_source, '@NULL@')
1773 AND NVL(sa.journal_category, '@NULL@') =
1774 NVL2(sa.journal_category,
1775 l_rec.ctrl_attr_rec.journal_category, '@NULL@')
1776 AND NVL(sa.document_category, '@NULL@') =
1777 NVL2(sa.document_category,
1778 l_rec.ctrl_attr_rec.document_category, '@NULL@')
1779 AND NVL(sa.accounting_event_type, '@NULL@') =
1780 NVL2(sa.accounting_event_type,
1781 l_rec.ctrl_attr_rec.accounting_event_type, '@NULL@')
1782 AND NVL(sa.accounting_entry_type, '@NULL@') =
1783 NVL2(sa.accounting_entry_type,
1784 l_rec.ctrl_attr_rec.accounting_entry_type, '@NULL@');
1785
1786 RETURN l_assign_seq_head_rec;
1787 EXCEPTION
1788 WHEN NO_DATA_FOUND THEN
1789 --
1790 -- No Assignment is found
1791 --
1792 l_assign_seq_head_rec := NULL;
1793 RETURN l_assign_seq_head_rec;
1794 WHEN OTHERS THEN
1795 app_exception.raise_exception;
1796 END find_seq_head_assign_in_db;
1797
1798 --
1799 -- Find an Exception in the database
1800 --
1801 FUNCTION find_seq_head_exp_in_cache(
1802 p_exp_info_rec IN exp_info_rec_type)
1803 RETURN BINARY_INTEGER IS
1804
1805 l_rec exp_info_rec_type;
1806 l_exp_index BINARY_INTEGER;
1807 l_found BOOLEAN;
1808 BEGIN
1809 l_rec := p_exp_info_rec;
1810 l_exp_index := 0;
1811 l_found := FALSE;
1812
1813 WHILE (l_exp_index < g_exp_cache_size) AND (NOT l_found) LOOP
1814 IF g_exp_info_tbl(l_exp_index).assignment_id = l_rec.assignment_id AND
1815 g_exp_info_tbl(l_exp_index).control_date = l_rec.control_date AND
1816 g_exp_info_tbl(l_exp_index).ctrl_attr_rec.balance_type =
1817 l_rec.ctrl_attr_rec.balance_type AND
1818 g_exp_info_tbl(l_exp_index).ctrl_attr_rec.journal_source =
1819 l_rec.ctrl_attr_rec.journal_source AND
1820 g_exp_info_tbl(l_exp_index).ctrl_attr_rec.journal_category =
1821 l_rec.ctrl_attr_rec.journal_category AND
1822 g_exp_info_tbl(l_exp_index).ctrl_attr_rec.document_category =
1823 NVL(l_rec.ctrl_attr_rec.document_category, -1) AND
1824 g_exp_info_tbl(l_exp_index).ctrl_attr_rec.accounting_event_type =
1825 NVL(l_rec.ctrl_attr_rec.accounting_event_type,-1) AND
1829 l_found := TRUE;
1826 g_exp_info_tbl(l_exp_index).ctrl_attr_rec.accounting_entry_type =
1827 NVL(l_rec.ctrl_attr_rec.accounting_entry_type,-1)
1828 THEN
1830 ELSE
1831 l_exp_index := l_exp_index + 1;
1832 END IF;
1833 END LOOP;
1834
1835 RETURN l_exp_index;
1836 END find_seq_head_exp_in_cache;
1837 --
1838 -- Find an Exception in the database
1839 --
1840 FUNCTION find_seq_head_exp_in_db(
1841 p_exp_info_rec IN exp_info_rec_type)
1842 RETURN assign_seq_head_rec_type IS
1843
1844 TYPE Seq_Header_Type IS REF CURSOR;
1845 l_CursorVar Seq_Header_Type;
1846
1847 l_rec_in exp_info_rec_type;
1848 l_exp_rec_out assign_seq_head_rec_type;
1849 BEGIN
1850 --
1851 -- Initialize records
1852 --
1853 l_rec_in := p_exp_info_rec;
1854
1855 OPEN l_CursorVar FOR
1856 SELECT xsa.assignment_id,
1857 xsh.gapless_flag,
1858 xsa.seq_header_id
1859 FROM fun_seq_assignments xsa, fun_seq_headers xsh
1860 WHERE xsa.link_to_assignment_id = l_rec_in.assignment_id
1861 AND xsa.start_date <= l_rec_in.control_date
1862 AND l_rec_in.control_date <= NVL(xsa.end_date, l_rec_in.control_date + 1)
1863 AND xsa.use_status_code IN ('NEW','USED')
1864 AND xsa.seq_header_id = xsh.seq_header_id (+) -- Do not Sequence
1865 AND xsh.obsolete_flag (+) = 'N'
1866 AND (xsa.balance_type IS NULL OR
1867 xsa.balance_type = l_rec_in.ctrl_attr_rec.balance_type)
1868 AND (xsa.journal_source IS NULL OR
1869 xsa.journal_source = l_rec_in.ctrl_attr_rec.journal_source)
1870 AND (xsa.journal_category IS NULL OR
1871 xsa.journal_category = l_rec_in.ctrl_attr_rec.journal_category)
1872 AND (xsa.document_category IS NULL OR
1873 xsa.document_category
1874 = l_rec_in.ctrl_attr_rec.document_category)
1875 AND (xsa.accounting_event_type IS NULL OR
1876 xsa.accounting_event_type
1877 = l_rec_in.ctrl_attr_rec.accounting_event_type)
1878 AND (xsa.accounting_entry_type IS NULL OR
1879 xsa.accounting_entry_type
1880 = l_rec_in.ctrl_attr_rec.accounting_entry_type)
1881 ORDER BY xsa.priority;
1882 --
1883 -- Loop - Begin
1884 --
1885 LOOP
1886 FETCH l_CursorVar
1887 INTO l_exp_rec_out.assignment_id,
1888 l_exp_rec_out.seq_type,
1889 l_exp_rec_out.seq_header_id;
1890 --
1891 -- Assignments are already ordered by Priority.
1892 -- The first fetched row should be the one returned to a caller.
1893 --
1894 IF (l_CursorVar%NOTFOUND) OR (l_CursorVar%ROWCOUNT = 1) THEN
1895 EXIT;
1896 END IF;
1897 END LOOP;
1898 CLOSE l_CursorVar;
1899 RETURN l_exp_rec_out;
1900 END find_seq_head_exp_in_db;
1901
1902 --
1903 -- Checkk if we should use Cache
1904 -- **** In case UI pages are display only. ****
1905 --
1906 FUNCTION use_cache (
1907 p_request_id IN NUMBER,
1908 p_application_id IN NUMBER,
1909 p_table_name IN VARCHAR2,
1910 p_event_code IN VARCHAR2) RETURN BOOLEAN IS
1911 BEGIN
1912 IF p_request_id IS NULL THEN
1913 --
1914 -- Online Transactions (UI pages are NOT updatealbe.)
1915 --
1916 IF (p_application_id = 435 AND p_table_name = 'FUN_TRX_BATCHES') THEN
1917 RETURN (TRUE);
1918 --
1919 -- Online Transactions (UI pages are updateable.)
1920 --
1921 ELSE
1922 RETURN (FALSE);
1923 END IF;
1924 ELSE
1925 --
1926 -- Batch Mode
1927 --
1928 RETURN (TRUE);
1929 END IF;
1930 END use_cache;
1931 END fun_seq;