[Home] [Help]
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;
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;
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
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;
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
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);
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);
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');
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: --
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
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