1 PACKAGE fun_seq AS
2 /* $Header: funsqgns.pls 120.22 2004/06/23 21:05:18 masada noship $ */
3
4 TYPE control_date_rec_type IS RECORD (
5 date_type VARCHAR2(30), --- Update DLD
6 date_value DATE);
7
8 TYPE control_date_tbl_type IS TABLE OF control_date_rec_type;-- INDEX BY BINARY_INTEGER;
9
10 TYPE control_attribute_rec_type IS RECORD (
11 balance_type fun_seq_assignments.balance_type%TYPE,
12 journal_source fun_seq_assignments.journal_source%TYPE,
13 journal_category fun_seq_assignments.journal_category%TYPE,
14 document_category fun_seq_assignments.document_category%TYPE,
15 accounting_event_type fun_seq_assignments.accounting_event_type%TYPE,
16 accounting_entry_type fun_seq_assignments.accounting_entry_type%TYPE);
17
18 TYPE context_info_rec_type IS RECORD (
19 application_id fun_seq_contexts.application_id%TYPE,
20 table_name fun_seq_contexts.table_name%TYPE,
21 context_type fun_seq_contexts.context_type%TYPE,
22 context_value fun_seq_contexts.context_value%TYPE,
23 event_code fun_seq_contexts.event_code%TYPE);
24
25 TYPE context_info_tbl_type IS TABLE OF context_info_rec_type
26 INDEX BY BINARY_INTEGER;
27
28 TYPE context_ctrl_rec_type IS RECORD (
29 seq_context_id fun_seq_contexts.seq_context_id%TYPE,
30 date_type fun_seq_contexts.date_type%TYPE,
31 req_assign_flag fun_seq_contexts.require_assign_flag%TYPE,
32 sort_option_code fun_seq_contexts.sort_option%TYPE);
33
34 TYPE context_ctrl_tbl_type IS TABLE OF context_ctrl_rec_type
35 INDEX BY BINARY_INTEGER;
36
37
38 TYPE assign_info_rec_type IS RECORD (
39 seq_context_id fun_seq_contexts.seq_context_id%TYPE,
40 ctrl_attr_rec control_attribute_rec_type,
41 control_date DATE);
42
43 TYPE assignment_info_tbl_type IS TABLE of assign_info_rec_type
44 INDEX BY BINARY_INTEGER;
45
46 TYPE exp_info_rec_type IS RECORD (
47 assignment_id fun_seq_assignments.assignment_id%TYPE,
48 ctrl_attr_rec control_attribute_rec_type,
49 control_date DATE);
50
51 TYPE exp_info_tbl_type IS TABLE of exp_info_rec_type
52 INDEX BY BINARY_INTEGER;
53
54 TYPE assign_seq_head_rec_type IS RECORD (
55 assignment_id fun_seq_assignments.assignment_id%TYPE,
56 seq_header_id fun_seq_headers.seq_header_id%TYPE,
57 seq_type fun_seq_headers.gapless_flag%TYPE);
58
59 TYPE assign_seq_head_tbl_type IS TABLE of assign_seq_head_rec_type
60 INDEX BY BINARY_INTEGER;
61
62 -- Start of comments
63 -- API name : Get_Sequence_Number
64 -- Type : Group
65 -- Pre-reqs : None
66 -- Function : Provide a wrapper for get_assigned_sequence_info
67 -- and generate_sequence_number
68 -- Parameters :
69 --
70 -- Version: Current version
71 --
72 -- End of comments
73 PROCEDURE Get_Sequence_Number(
74 p_context_type IN VARCHAR2,
75 p_context_value IN VARCHAR2,
76 p_application_id IN NUMBER,
77 p_table_name IN VARCHAR2,
78 p_event_code IN VARCHAR2,
79 p_control_attribute_rec IN control_attribute_rec_type,
80 p_control_date_tbl IN control_date_tbl_type,
81 p_suppress_error IN VARCHAR2,
82 x_seq_version_id OUT NOCOPY NUMBER,
83 x_sequence_number OUT NOCOPY NUMBER,
84 x_assignment_id OUT NOCOPY NUMBER,
85 x_error_code OUT NOCOPY VARCHAR2);
86 -- Start of comments
87 -- API name : Get_Assigned_Sequence_Info
88 -- Type : Group
89 -- Pre-reqs : None
90 -- Function : Retrieve Assigned Sequence Information
91 -- Parameters :
92 --
93 -- Version: Current version
94 --
95 -- End of comments
96 PROCEDURE Get_Assigned_Sequence_Info(
97 p_context_type IN VARCHAR2,
98 p_context_value IN VARCHAR2,
99 p_application_id IN NUMBER,
100 p_table_name IN VARCHAR2,
101 p_event_code IN VARCHAR2,
102 p_control_attribute_rec IN control_attribute_rec_type,
103 p_control_date_tbl IN control_date_tbl_type,
104 p_request_id IN NUMBER,
105 p_suppress_error IN VARCHAR2,
106 x_sequence_type OUT NOCOPY VARCHAR2,
107 x_seq_version_id OUT NOCOPY NUMBER,
108 x_assignment_id OUT NOCOPY NUMBER,
109 x_control_date_value OUT NOCOPY DATE,
110 x_req_assign_flag OUT NOCOPY VARCHAR2,
111 x_sort_option_code OUT NOCOPY VARCHAR2,
112 x_error_code OUT NOCOPY VARCHAR2);
113
114 -- Start of comments
115 -- API name : Generate_Sequence_Number
116 -- Type : Group
117 -- Pre-reqs : None
118 -- Function : Generate Sequence Numbers
119 -- Parameters :
120 --
121 -- Version: Current version
122 --
123 -- End of comments
124 PROCEDURE Generate_Sequence_Number(
125 p_assignment_id IN NUMBER,
126 p_seq_version_id IN NUMBER,
127 p_sequence_type IN VARCHAR2,
128 p_request_id IN NUMBER,
129 x_sequence_number OUT NOCOPY NUMBER,
130 x_sequenced_date OUT NOCOPY DATE,
131 x_error_code OUT NOCOPY VARCHAR2);
132
133 -- Start of comments
134 -- API name : Reset
135 -- Type : Group
136 -- Pre-reqs : None
137 -- Function : Reset Sequence Version Information for renumbering
138 -- Current Value is updated.
139 -- Parameters :
140 -- p_seq_version_id
141 -- p_sequence_number
142 -- Version: Current version
143 --
144 -- End of comments
145 PROCEDURE Reset(
146 p_seq_version_id IN NUMBER,
147 p_sequence_number IN NUMBER); -- Last Used Number
148
149 --
150 -- Supporting Programs
151 --
152 PROCEDURE get_assign_context_info (
153 p_context_type IN VARCHAR2,
154 p_context_value IN VARCHAR2,
155 p_application_id IN NUMBER,
156 p_table_name IN VARCHAR2,
157 p_event_code IN VARCHAR2,
158 p_request_id IN NUMBER,
159 x_seq_context_id OUT NOCOPY NUMBER,
160 x_control_date_type OUT NOCOPY VARCHAR2,
161 x_req_assign_flag OUT NOCOPY VARCHAR2,
162 x_sort_option_code OUT NOCOPY VARCHAR2);
163
164 --
165 -- Supporting Programs
166 --
167
168 --
169 -- Sequence Numbering (without Autonomous Commit)
170 --
171 PROCEDURE Get_Sequence_Number_No_Commit(
172 p_context_type IN VARCHAR2,
173 p_context_value IN VARCHAR2,
174 p_application_id IN NUMBER,
175 p_table_name IN VARCHAR2,
176 p_event_code IN VARCHAR2,
177 p_control_attribute_rec IN control_attribute_rec_type,
178 p_control_date_tbl IN control_date_tbl_type,
179 p_suppress_error IN VARCHAR2,
180 x_seq_version_id OUT NOCOPY NUMBER,
181 x_sequence_number OUT NOCOPY NUMBER,
182 x_assignment_id OUT NOCOPY NUMBER,
183 x_error_code OUT NOCOPY VARCHAR2);
184
185 --
186 -- Sequence Numbering (w/ Autonomous Commit)
187 --
188 PROCEDURE Get_Sequence_Number_Commit(
189 p_context_type IN VARCHAR2,
190 p_context_value IN VARCHAR2,
191 p_application_id IN NUMBER,
192 p_table_name IN VARCHAR2,
193 p_event_code IN VARCHAR2,
194 p_control_attribute_rec IN control_attribute_rec_type,
195 p_control_date_tbl IN control_date_tbl_type,
196 p_suppress_error IN VARCHAR2,
197 x_seq_version_id OUT NOCOPY NUMBER,
198 x_sequence_number OUT NOCOPY NUMBER,
199 x_assignment_id OUT NOCOPY NUMBER,
200 x_error_code OUT NOCOPY VARCHAR2);
201
202 PROCEDURE get_cached_context_info (
203 p_context_info_rec IN context_info_rec_type,
204 x_context_ctrl_rec OUT NOCOPY context_ctrl_rec_type);
205
206 --
207 -- Wrapper for get_assigned_seq_header and get_assigned_seq_version
208 --
209 PROCEDURE get_assigned_sequence_header (
210 p_seq_context_id IN NUMBER,
211 p_control_attribute_rec IN control_attribute_rec_type,
212 p_control_date_value IN DATE,
213 p_request_id IN NUMBER,
214 x_assignment_id OUT NOCOPY NUMBER,
215 x_sequence_type OUT NOCOPY VARCHAR2,
216 x_seq_header_id OUT NOCOPY NUMBER);
217
218 --
219 -- Retrieve Assignment Information of Intercompany Transactions
220 --
221 PROCEDURE get_ic_assigned_seq_header (
222 p_seq_context_id IN NUMBER,
223 p_control_date_value IN DATE,
224 p_request_id IN NUMBER,
225 x_assignment_id OUT NOCOPY NUMBER,
226 x_sequence_type OUT NOCOPY VARCHAR2,
227 x_seq_header_id OUT NOCOPY NUMBER);
228
229 --
230 -- Get_Seq_Header_Assignment
231 --
232 PROCEDURE get_seq_header_assignment (
233 p_seq_context_id IN NUMBER,
234 p_control_attribute_rec IN control_attribute_rec_type,
235 p_control_date_value IN DATE,
236 p_request_id IN NUMBER,
237 x_assignment_id OUT NOCOPY NUMBER,
238 x_sequence_type OUT NOCOPY VARCHAR2,
239 x_seq_header_id OUT NOCOPY NUMBER);
240
241 --
242 -- Called from Get_Seq_Header_Assignment
243 -- Use Cache for Batch Programs
244 --
245 PROCEDURE get_cached_seq_header_assign (
246 p_assign_info_rec IN assign_info_rec_type,
247 x_assign_seq_head_rec OUT NOCOPY assign_seq_head_rec_type);
248
249 --
250 -- Get_Seq_Header_Exception
251 --
252 PROCEDURE get_seq_header_exception (
253 p_assignment_id IN NUMBER,
254 p_control_attribute_rec IN control_attribute_rec_type,
255 p_control_date_value IN DATE,
256 p_request_id IN NUMBER,
257 x_exp_assignment_id OUT NOCOPY NUMBER,
258 x_exp_sequence_type OUT NOCOPY VARCHAR2,
259 x_exp_seq_header_id OUT NOCOPY NUMBER);
260
261 --
262 -- Get_Seq_Context_Name
263 -- (for debug)
264 FUNCTION get_seq_context_name (
265 p_seq_context_id IN NUMBER) RETURN VARCHAR2;
266
267 --
268 -- Get_Seq_Header_Name
269 -- (for debug)
270 FUNCTION get_seq_header_name (
271 p_seq_header_id IN NUMBER) RETURN VARCHAR2;
272
273 --
274 -- Get_Cached_Seq_Header_Exp
275 --
276 PROCEDURE get_cached_seq_header_exp (
277 p_exp_info_rec IN exp_info_rec_type,
278 x_exp_seq_head_rec OUT NOCOPY assign_seq_head_rec_type);
279 --
280 --
281 --
282 PROCEDURE get_seq_version (
283 p_sequence_type IN VARCHAR2,
284 p_seq_header_id IN NUMBER,
285 p_control_date_value IN DATE,
286 p_request_id IN NUMBER,
287 x_seq_version_id OUT NOCOPY NUMBER);
288
289 --
290 --
291 --
292 FUNCTION get_control_date_value (
293 p_control_date_type IN VARCHAR2,
294 p_control_dates IN control_date_tbl_type) RETURN VARCHAR2;
295 --
296 -- Update the Status of Assignments/Exceptions and Versions
297 -- ** For Gapless Sequences **
298 --
299 PROCEDURE update_gapless_status (
300 p_assignment_id IN NUMBER,
301 p_seq_version_id IN NUMBER);
302
303 --
304 -- Update the Status of Assignments/Exceptions and Versions
305 -- ** For Database Sequences **
306 --
307 PROCEDURE update_db_status (
308 p_assignment_id IN NUMBER,
309 p_seq_version_id IN NUMBER);
310 --
311 -- Update the Status of Assignments and Exceptions
312 --
313 PROCEDURE update_assign_status (
314 p_assignment_id IN NUMBER);
315
316 --
317 -- Update the Status of Versions
318 --
319 PROCEDURE update_seq_ver_status (
320 p_seq_version_id IN NUMBER);
321 --
322 -- Find a Sequencing Context in the Cache
323 --
324 FUNCTION find_seq_context_in_cache(
325 p_context_info_rec IN context_info_rec_type)
326 RETURN BINARY_INTEGER;
327
328 --
329 -- Find a Sequencing Context in the database
330 --
331 FUNCTION find_seq_context_in_db(
332 p_context_info_rec IN context_info_rec_type)
333 RETURN context_ctrl_rec_type;
334
335 --
336 -- Find an Assignment in the Cache
337 --
338 FUNCTION find_seq_head_assign_in_cache(
339 p_assign_info_rec IN assign_info_rec_type)
340 RETURN BINARY_INTEGER;
341 --
342 -- Find an Assignment in the database
343 --
344 FUNCTION find_seq_head_assign_in_db (
345 p_assign_info_rec IN assign_info_rec_type)
346 RETURN assign_seq_head_rec_type;
347
348 --
349 -- Find an Exception in the database
350 --
351 FUNCTION find_seq_head_exp_in_cache(
352 p_exp_info_rec IN exp_info_rec_type)
353 RETURN BINARY_INTEGER;
354 --
355 -- Find an Exception in the database
356 --
357 FUNCTION find_seq_head_exp_in_db(
358 p_exp_info_rec IN exp_info_rec_type)
359 RETURN assign_seq_head_rec_type;
360 --
361 -- Return the flag indicating whether to use cache
362 --
363 FUNCTION use_cache (
364 p_request_id IN NUMBER,
365 p_application_id IN NUMBER,
366 p_table_name IN VARCHAR2,
367 p_event_code IN VARCHAR2)
368 RETURN BOOLEAN;
369 END fun_seq;
[Home] [Help]