[Home] [Help]
22: -- Cursor to see if Originator ID already exists in FCN table
23: --
24: CURSOR originator_exists(p_originator_id varchar2) IS
25: select 1
26: from pay_ca_file_creation_numbers
27: where originator_id = p_originator_id;
28: --
29: -- Cursor to find the max number of rows allowed in FCN table for a particular
30: -- Financial Institution.
39: -- ID
40: --
41: CURSOR count_fcn_rows(p_originator_id varchar2) IS
42: select count(*)
43: from pay_ca_file_creation_numbers
44: where originator_id = p_originator_id;
45: --
46: -- Cursor to find the maximum sequence_number for a particular Originator ID
47: --
46: -- Cursor to find the maximum sequence_number for a particular Originator ID
47: --
48: CURSOR get_max_sequence(l_orig_id varchar2) is
49: select max(sequence_number)
50: from pay_ca_file_creation_numbers
51: where originator_id = l_orig_id;
52: --
53: -- Cursor to find the maximum sequence_number for a particular Originator ID
54: -- where the file_creation_number is composed of digits only
54: -- where the file_creation_number is composed of digits only
55: --
56: CURSOR digits_only_max_sequence(l_orig_id varchar2) is
57: select max(sequence_number)
58: from pay_ca_file_creation_numbers
59: where originator_id = l_orig_id
60: and file_creation_number =
61: translate(file_creation_number,
62: 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz?><~!@#$%^*()_-+=/\|}{][":;.,`''',
73: -- Cursor to find the next FCN for a given Originator ID and seqence number
74: --
75: CURSOR get_new_fcn(l_var number, l_orig_id varchar2, l_seq_num number) is
76: select fnd_number.canonical_to_number(file_creation_number) + l_var
77: from pay_ca_file_creation_numbers
78: where originator_id = l_orig_id
79: and sequence_number = l_seq_num;
80:
81: -- Cursor to find the existing FCNs for a given Originator ID
81: -- Cursor to find the existing FCNs for a given Originator ID
82: --
83: CURSOR current_fcns(l_orig_id varchar2, l_fcn varchar2) is
84: select 1
85: from pay_ca_file_creation_numbers
86: where originator_id = l_orig_id
87: and file_creation_number = l_fcn;
88: --
89: -- Cursor to get the next sequence number
88: --
89: -- Cursor to get the next sequence number
90: --
91: CURSOR get_nextval is
92: select pay_ca_file_creation_numbers_s.nextval
93: from dual;
94: --
95: -- Cursor to get the min sequence_number for a given Originator ID
96: --
95: -- Cursor to get the min sequence_number for a given Originator ID
96: --
97: CURSOR get_min_sequence(p_originator_id varchar2) IS
98: select min(sequence_number)
99: from pay_ca_file_creation_numbers
100: where originator_id = p_originator_id;
101: --
102: -- Cursor to find if Override FCN already exists in FCN table
103: --
103: --
104: CURSOR override_fcn_exists(p_originator_id varchar2
105: ,p_override_fcn varchar2) is
106: select 1
107: from pay_ca_file_creation_numbers
108: where originator_id = p_originator_id
109: and file_creation_number = p_override_fcn;
110: --
111: -- Procedure to insert and commit a new row in pay_ca_file_creation_numbers
107: from pay_ca_file_creation_numbers
108: where originator_id = p_originator_id
109: and file_creation_number = p_override_fcn;
110: --
111: -- Procedure to insert and commit a new row in pay_ca_file_creation_numbers
112: --
113: PROCEDURE insert_new_fcn(p_originator_id varchar2
114: ,p_sequence_number number
115: ,p_new_fcn varchar2) is
115: ,p_new_fcn varchar2) is
116: BEGIN
117: --
118: insert into
119: pay_ca_file_creation_numbers
120: (originator_id ,
121: sequence_number,
122: file_creation_number)
123: values (p_originator_id,
133: PROCEDURE delete_oldest_fcn(p_originator_id varchar2
134: ,p_sequence_number number) IS
135: BEGIN
136: --
137: delete from pay_ca_file_creation_numbers
138: where sequence_number = p_sequence_number
139: and originator_id = p_originator_id;
140: --
141: --commit;
203: close digits_only_max_sequence;
204:
205: -- dbms_output.put_line('l_new_fcn is '||l_new_fcn);
206: --
207: -- Insert new row into pay_ca_file_creation_numbers, get the
208: -- sequence.nextval first.
209: --
210:
211: open get_nextval;
221: close get_max_sequence;
222: --
223: select file_creation_number
224: into l_new_fcn
225: from pay_ca_file_creation_numbers
226: where sequence_number = l_max_seq
227: and originator_id = p_originator_id;
228: --
229: RETURN l_new_fcn;
323: fetch override_fcn_exists into l_override_fcn;
324: close override_fcn_exists;
325: -- select 1
326: -- into l_override_fcn
327: -- from pay_ca_file_creation_numbers
328: -- where originator_id = p_originator_id
329: -- and file_creation_number = p_override_fcn;
330: --
331: -- dbms_output.put_line('l_override_fcn is '||l_override_fcn);
369: close get_max_sequence;
370: --
371: select file_creation_number
372: into l_new_fcn
373: from pay_ca_file_creation_numbers
374: where sequence_number = l_max_seq
375: and originator_id = p_originator_id;
376: --
377: --
401: close get_max_sequence;
402: --
403: select file_creation_number
404: into l_new_fcn
405: from pay_ca_file_creation_numbers
406: where sequence_number = l_max_seq
407: and originator_id = p_originator_id;
408: --
409: RETURN l_new_fcn;
435: close get_max_sequence;
436: --
437: select file_creation_number
438: into l_new_fcn
439: from pay_ca_file_creation_numbers
440: where sequence_number = l_max_seq
441: and originator_id = p_originator_id;
442: --
443: RETURN l_new_fcn;
490: close get_max_sequence;
491: --
492: select file_creation_number
493: into l_new_fcn
494: from pay_ca_file_creation_numbers
495: where sequence_number = l_max_seq
496: and originator_id = p_originator_id;
497: --
498: RETURN l_new_fcn;