1 PACKAGE fun_seq_batch AUTHID CURRENT_USER AS
2 /* $Header: funsqbts.pls 120.20 2006/04/25 19:03:00 sryu noship $ */
3
4 TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5 TYPE num15_tbl_type IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
6 TYPE vc30_tbl_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
7
8 TYPE seq_info_rec_type IS RECORD (
9 seq_version_id fun_seq_versions.seq_version_id%TYPE,
10 assignment_id fun_seq_assignments.assignment_id%TYPE);
11
12 TYPE seq_info_tbl_type IS TABLE OF seq_info_rec_type
13 INDEX BY BINARY_INTEGER;
14
15 TYPE context_value_tbl_type IS TABLE OF
16 fun_seq_contexts.context_value%TYPE INDEX BY BINARY_INTEGER;
17
18 --
19 -- Indexed by Source_ID, that is, either AE_HEADER_ID or or
20 --
21 TYPE seq_head_id_tbl_type IS TABLE OF fun_seq_headers.seq_header_id%TYPE
22 INDEX BY BINARY_INTEGER;
23
24 TYPE seq_type_tbl_type IS TABLE OF fun_seq_headers.gapless_flag%TYPE
25 INDEX BY BINARY_INTEGER;
26
27 TYPE seq_ver_id_tbl_type IS TABLE OF fun_seq_versions.seq_version_id%TYPE
28 INDEX BY BINARY_INTEGER;
29
30 TYPE assign_id_tbl_type IS TABLE OF fun_seq_assignments.assignment_id%TYPE
31 INDEX BY BINARY_INTEGER;
32
33 TYPE seq_value_tbl_type IS TABLE OF fun_seq_versions.current_value%TYPE
34 INDEX BY BINARY_INTEGER;
35
36 --
37 -- Called from Populate_Seq_Requests
38 --
39 TYPE ledger_id_tbl_type IS TABLE OF gl_je_headers.ledger_id%TYPE
40 INDEX BY BINARY_INTEGER;
41
42 TYPE je_header_id_tbl_type IS TABLE OF gl_je_headers.je_header_id%TYPE
43 INDEX BY BINARY_INTEGER;
44
45 TYPE actual_flag_tbl_type IS TABLE OF gl_je_headers.actual_flag%TYPE
46 INDEX BY BINARY_INTEGER;
47
48 TYPE je_source_tbl_type IS TABLE OF gl_je_headers.je_source%TYPE
49 INDEX BY BINARY_INTEGER;
50
51 TYPE je_category_tbl_type IS TABLE OF gl_je_headers.je_category%TYPE
52 INDEX BY BINARY_INTEGER;
53
54 TYPE date_tbl_type IS TABLE OF DATE
55 INDEX BY BINARY_INTEGER;
56
57 TYPE req_assign_flag_tbl_type IS TABLE OF
58 fun_seq_contexts.require_assign_flag%TYPE INDEX BY BINARY_INTEGER;
59
60 TYPE error_code_tbl_type IS TABLE OF VARCHAR2(30)
61 INDEX BY BINARY_INTEGER;
62
63
64 --
65 -- Used in Populate_Acct_Seq_Info
66 --
67 TYPE ae_header_id_tbl_type IS TABLE OF xla_ae_headers.ae_header_id%TYPE
68 INDEX BY BINARY_INTEGER;
69
70 TYPE balance_type_code_tbl_type IS TABLE OF
71 xla_ae_headers.balance_type_code%TYPE INDEX BY BINARY_INTEGER;
72
73 TYPE je_source_name_tbl_type IS TABLE OF gl_je_headers.je_source%TYPE
74 INDEX BY BINARY_INTEGER;
75
76 TYPE je_category_name_tbl_type IS TABLE OF xla_ae_headers.je_category_name%TYPE
77 INDEX BY BINARY_INTEGER;
78
79 TYPE doc_category_code_tbl_type IS TABLE OF
80 xla_ae_headers.doc_category_code%TYPE INDEX BY BINARY_INTEGER;
81
82 -- **** replace event_type_code with proper columns
83 TYPE acct_event_type_code_tbl_type IS TABLE OF VARCHAR2(50)
84 INDEX BY BINARY_INTEGER;
85 -- xla_ae_headers.event_type_code%TYPE INDEX BY BINARY_INTEGER;
86
87 TYPE acct_entry_type_code_tbl_type IS TABLE OF
88 xla_ae_headers.accounting_entry_type_code%TYPE INDEX BY BINARY_INTEGER;
89
90 -- **** Use date_tbl_type for date ****
91 -- TYPE date_tbl_type IS TABLE OF DATE
92 -- INDEX BY BINARY_INTEGER;
93
94
95 -- Start of comments
96 -- API name : Batch_Init *** For Accounting Program ***
97 -- Type : Group
98 -- Pre-reqs : None
99 -- Function : Locks the setup data by inserting new records into
100 -- fun_seq_requests
101 -- Parameters :
102 --
103 -- Version: Current version
104 --
105 -- End of comments
106 PROCEDURE Batch_Init(
107 p_application_id IN NUMBER,
108 p_table_name IN VARCHAR2,
109 p_event_code IN VARCHAR2,
110 p_context_type IN VARCHAR2,
111 p_context_value_tbl IN context_value_tbl_type,
112 p_request_id IN NUMBER,
113 x_status OUT NOCOPY VARCHAR2,
114 x_seq_context_id OUT NOCOPY NUMBER);
115
116 -- Start of comments
117 -- API name : Batch_Init *** For GL Posting Program ***
118 -- Type : Group
119 -- Pre-reqs : None
120 -- Function : Locks the setup data by inserting new records into
121 -- fun_seq_requests
122 -- Parameters :
123 --
124 -- Version: Current version
125 --
126 -- End of comments
127 PROCEDURE Batch_Init(
128 p_request_id IN NUMBER,
129 p_ledgers_tbl IN num15_tbl_type,
130 x_ledgers_locked_tbl OUT NOCOPY num15_tbl_type,
131 x_ledgers_locked_cnt OUT NOCOPY NUMBER);
132
133 -- Start of comments
134 -- API name : Batch_Exit
135 -- *** For XLA Accounting Program and GL Posting Program
136 -- Type : Group
137 -- Pre-reqs : None
138 -- Function : Unlocks setup data by deleting records from fun_seq_requests
139 -- Parameters :
140 --
141 -- Version: Current version
142 --
143 -- End of comments
144 PROCEDURE Batch_Exit(
145 p_request_id IN NUMBER,
146 x_status OUT NOCOPY VARCHAR2);
147
148 -- Start of comments
149 -- API name : Generate_Bulk_Numbers
150 -- Type : Group
151 -- Pre-reqs : None
152 -- Function : Returns sequence numbers for records with same sequence version
153 -- ID and sequence assignment ID. Also, current value for the
154 -- sequence version is updated
155 --
156 -- Parameters :
157 --
158 -- Version: Current version
159 --
160 -- End of comments
161 PROCEDURE Generate_Bulk_Numbers(
162 p_request_id IN NUMBER,
163 p_seq_ver_id_tbl IN seq_ver_id_tbl_type,
164 p_assign_id_tbl IN assign_id_tbl_type,
165 x_seq_value_tbl OUT NOCOPY seq_value_tbl_type,
166 x_seq_date_tbl OUT NOCOPY date_tbl_type);
167
168 -- Start of comments
169 -- API name : Populate_Acct_Seq_Info *** For Accounting Program ***
170 -- Type : Group
171 -- Pre-reqs : None
172 -- Function : This procedure retrieves Sequence information for each
173 -- Subledger journal entry header and update Assignment Id,
174 -- Sequence Version Id, and Sequence Number columns of XLA
175 -- updateable view.
176 -- Parameters :
177 -- p_calling_program
178 -- p_request_id Number
179 -- Version: Current version
180 --
181 -- End of comments
182 PROCEDURE Populate_Acct_Seq_Info(
183 p_calling_program IN VARCHAR2,
184 p_request_id IN NUMBER);
185
186 -- Start of comments
187 -- API name : Populate_Sequence_Info *** For GL Posting Program ***
188 -- Type : Group
189 -- Pre-reqs : None
190 -- Function : This procedure processes each GL journal entry header found
191 -- in FUN_SEQ_BATCH_GT and stores the sequencing assignment
192 -- and version that will be used to sequence the header.
193 -- It also marks the headers for which the sequence numbering
194 -- setup is incorrect.
195 --
196 -- Parameters :
197 -- None
198 -- Version: Current version
199 --
200 -- End of comments
201 PROCEDURE Populate_Seq_Info;
202
203 -- Start of comments
204 -- API name : Populate_Numbers *** For GL Posting Program ***
205 -- Type : Group
206 -- Pre-reqs : None
207 -- Function : This procedure obtains sequence numbers for the journal entry
208 -- headers found in the table FUN_SEQ_BATCH_GT.
209 -- Parameters :
210 -- None
211 -- Version: Current version
212 --
213 -- End of comments
214 FUNCTION Populate_Numbers RETURN DATE;
215
216 -- Start of comments
217 -- API name : Release_Lock *** For GL and XLA ***
218 -- Type : Group
219 -- Pre-reqs : None
220 -- Function : This procedure is called from a Concurrent Program
221 -- to release locks imposed on the Sequencing setup.
222 -- Parameters :
223 -- none
224 -- Version: Current version
225 --
226 -- End of comments
227 PROCEDURE Release_Lock (
228 errbuf OUT NOCOPY VARCHAR2, -- Required by Conc. Manager
229 retcode OUT NOCOPY NUMBER,
230 p_request_id IN NUMBER); -- Required by Conc. Manager
231
232 -------------------------------------------------------------------------
233 -- Supportive Procedures --
234 -------------------------------------------------------------------------
235 -- !!Warning!!
236 -- Do not call these procedures without consulting the SSMOA team
237 --
238 PROCEDURE Populate_Seq_Requests (
239 p_request_id IN NUMBER,
240 p_seq_context_id IN NUMBER);
241
242 PROCEDURE Populate_Seq_Context (
243 p_request_id IN NUMBER,
244 p_seq_context_id IN NUMBER);
245
246 PROCEDURE Populate_Seq_Headers (
247 p_request_id IN NUMBER,
248 p_seq_context_id IN NUMBER);
249
250 PROCEDURE Delete_Seq_Requests (
251 p_request_id IN NUMBER);
252
253 PROCEDURE Populate_Acct_Seq_Prog_View(
254 p_request_id IN NUMBER) ;
255
256 PROCEDURE Populate_Rep_Seq_Prog_Gt(
257 p_request_id IN NUMBER);
258
259 PROCEDURE Sort_Acct_Entries (
260 p_calling_program IN VARCHAR2,
261 p_application_id_tbl IN num_tbl_type,
262 p_ae_header_id_tbl IN ae_header_id_tbl_type,
263 p_assign_id_tbl IN assign_id_tbl_type,
264 p_seq_ver_id_tbl IN seq_ver_id_tbl_type,
265 p_sorting_key_tbl IN date_tbl_type,
266 x_application_id_tbl OUT NOCOPY num_tbl_type,
267 x_ae_header_id_tbl OUT NOCOPY ae_header_id_tbl_type,
268 x_assign_id_tbl OUT NOCOPY assign_id_tbl_type,
269 x_seq_ver_id_tbl OUT NOCOPY seq_ver_id_tbl_type);
270
271 END fun_seq_batch;