1 PACKAGE GL_JOURNAL_IMPORT_PKG AUTHID CURRENT_USER as
2 /* $Header: glujimns.pls 120.7 2011/09/28 10:01:18 sommukhe ship $ */
3 /*#
4 * Provides functions to use with Journal Import.
5 * @rep:scope public
6 * @rep:product GL
7 * @rep:lifecycle active
8 * @rep:displayname Journal Import Functions
9 * @rep:compatibility S
10 * @rep:category BUSINESS_ENTITY GL_JOURNAL
11 */
12
13 --
14 -- Package
15 -- GL_JOURNAL_IMPORT_PKG
16 -- Purpose
17 -- Various utilities for working directly with Journal Import
18 -- History
19 -- 09-OCT-2000 D J Ogg Created.
20 --
21
22 -- This exception is raised when we fail to get the applsys schema
23 -- information in the create_table and drop_table routines
24 CANNOT_GET_APPLSYS_SCHEMA EXCEPTION;
25
26 -- This exception is raised when the populate_interface_control routine
27 -- is passed an invalid journal entry source name
28 INVALID_JE_SOURCE EXCEPTION;
29
30 -- This exception is raised when the populate_interface_control routine
31 -- is passed an invalid processed_data_action
32 INVALID_PROCESSED_ACTION EXCEPTION;
33
34 -- This exception is raised when the populate_interface_control routine
35 -- is passed parameters that would imply that gl_interface should be
36 -- dropped.
37 CANNOT_DROP_GL_INTERFACE EXCEPTION;
38
39
40 -- Constants for use with the processed_data_action parameter of
41 -- the populate_interface_control procedure.
42 SAVE_DATA CONSTANT VARCHAR2(1) := 'S';
43 DELETE_DATA CONSTANT VARCHAR2(1) := 'D';
44 DROP_INTERFACE_TABLE CONSTANT VARCHAR2(1) := 'R';
45
46 --
47 -- Procedure
48 -- create_table
49 -- Purpose
50 -- Creates a copy of the gl_interface table with the given
51 -- name and storage parameters. The table will be created
52 -- in the gl schema.
53 -- History
54 -- 09-OCT-2000 D. J. Ogg Created
55 -- Arguments
56 -- table_name The name of the new table
57 -- tablespace The tablespace the table should be created in
58 -- physical_attributes The physical attributes clause for the
59 -- creation of the table
60 -- create_n1_index Indicates whether or not the n1 index should
61 -- be created
62 -- n1_tablespace The tablespace the n1 index should be created
63 -- in
64 -- n1_physical_attributes The physical attributes clause for the
65 -- creation of the n1 index
66 -- create_n2_index Indicates whether or not the n2 index should
67 -- be created
68 -- n2_tablespace The tablespace the n2 index should be created
69 -- in
70 -- n2_physical_attributes The physical attributes clause for the
71 -- creation of the n2 index
72 -- Example
73 -- gl_journal_import_pkg.create_table(
74 -- 'GL_CUSTOM_INTERFACE',
75 -- 'TAB1',
76 -- 'PCTFREE 10 STORAGE (INITIAL 500K NEXT 1M)',
77 -- FALSE,
78 -- NULL,
79 -- NULL,
80 -- TRUE,
81 -- 'IND1',
82 -- 'STORAGE (INITIAL 10K NEXT 20K)');
83 -- Notes
84 --
85 /*#
86 * Creates a copy of the GL_INTERFACE table. The newly created copy is created in
87 * the GL schema and has the name and storage parameters provided by the user.
88 * Journal Import can pull data from the newly created interface table to
89 * create journals.
90 * @param table_name Name of new table.
91 * @param tablespace Tablespace that will contain the new table.
92 * @param physical_attributes Physical attributes clause to be used in the creation of the new table.
93 * @param create_n1_index Indicates whether a copy of the GL_INTERFACE_N1 index is created for this table.
94 * @param n1_tablespace Tablespace that will contain a copy of the GL_INTERFACE_N1 index.
95 * @param n1_physical_attributes Physical attributes clause to be used in the creation of a copy of the GL_INTERFACE_N1 index.
96 * @param create_n2_index Indicates whether a copy of the GL_INTERFACE_N2 index is created for this table.
97 * @param n2_tablespace Tablespace that will contain a copy of the GL_INTERFACE_N2 index.
98 * @param n2_physical_attributes Physical attributes clause to be used in the creation of a copy of the GL_INTERFACE_N2 index.
99 * @rep:scope public
100 * @rep:lifecycle active
101 * @rep:displayname Create Journal Interface Table
102 * @rep:compatibility S
103 * @rep:category BUSINESS_ENTITY GL_JOURNAL
104 */
105 PROCEDURE create_table(table_name VARCHAR2,
106 tablespace VARCHAR2 DEFAULT NULL,
107 physical_attributes VARCHAR2 DEFAULT NULL,
108 create_n1_index BOOLEAN DEFAULT TRUE,
109 n1_tablespace VARCHAR2 DEFAULT NULL,
110 n1_physical_attributes VARCHAR2 DEFAULT NULL,
111 create_n2_index BOOLEAN DEFAULT TRUE,
112 n2_tablespace VARCHAR2 DEFAULT NULL,
113 n2_physical_attributes VARCHAR2 DEFAULT NULL,
114 create_n3_index BOOLEAN DEFAULT FALSE,
115 n3_tablespace VARCHAR2 DEFAULT NULL,
116 n3_physical_attributes VARCHAR2 DEFAULT NULL
117 );
118
119 --
120 -- Procedure
121 -- drop_table
122 -- Purpose
123 -- Drops a copy of the gl_interface table from the gl schema
124 -- History
125 -- 09-OCT-2000 D. J. Ogg Created
126 -- Arguments
127 -- table_name The name of the new table
128 -- Example
129 -- gl_journal_import_pkg.drop_table(
130 -- 'GL_CUSTOM_INTERFACE');
131 -- Notes
132 --
133 /*#
134 * Drops a copy of the GL_INTERFACE table, which must be in the GL schema.
135 * @param table_name Name of the table to drop.
136 * @rep:scope public
137 * @rep:lifecycle active
138 * @rep:displayname Drop Journal Interface Table
139 * @rep:compatibility S
140 * @rep:category BUSINESS_ENTITY GL_JOURNAL
141 */
142 PROCEDURE drop_table(table_name VARCHAR2);
143
144 --
145 -- Procedure
146 -- populate_interface_control
147 -- Purpose
148 -- Populates the gl_interface_control table. This routine does not
149 -- do a commit. Returns the interface run id.
150 -- History
151 -- 09-OCT-2000 D. J. Ogg Created
152 -- Arguments
153 -- user_je_source_name User friendly version of the source name
154 -- group_id Group id to be processed. If none is
155 -- provided, one will be automatically generated
156 -- and passed back through this parameter.
157 -- set_of_books_id Ledger Id to be used
158 -- interface_run_id Interface run id to be used. If none is
159 -- provided, one will be automatically generated
160 -- and passed back through this parameter.
161 -- table_name The name of the new table
162 -- processed_data_action Indicates what to do with the data and table
163 -- if it is successfully imported.
164 -- Valid options are SAVE_DATA, DELETE_DATA,
165 -- and DROP_INTERFACE_TABLE
166 -- Example
167 -- gl_journal_import_pkg.populate_interface_control(
168 -- 'Custom',
169 -- 5001,
170 -- 1,
171 -- 'GL_CUSTOM_INTERFACE');
172 -- Notes
173 --
174 /*#
175 * Communicates to Journal Import the source and GROUP_ID of transaction data
176 * to be processed, its location, and the action to take when it is
177 * imported successfully. This information is stored in the GL_INTERFACE_CONTROL
178 * table under the specified value of INTERFACE_RUN_ID. When Journal Import
179 * is run for that INTERFACE_RUN_ID, the specified data is processed.
180 *
181 * The TABLE_NAME parameter specifies the table in which the data is stored.
182 * If no value is specified for this parameter, then the table is assumed to be
183 * GL_INTERFACE. If the data is stored in a table other than GL_INTERFACE, then
184 * information about the location of the data is saved in GL_INTERFACE_CONTROL
185 * until the data is imported successfully. Any Journal Import run that processes
186 * that source and GROUP_ID will retrieve data from the specified table,
187 * even if run for a different INTERFACE_RUN_ID. Once the data is imported
188 * successfully, the row is deleted from GL_INTERFACE_CONTROL. Any
189 * additional runs of Journal Import for that source and GROUP_ID will retrieve
190 * data from the GL_INTERFACE table.
191 *
192 * The PROCESSED_DATA_ACTION parameter specifies the method to use in handling
193 * the data once it is successfully processed. Valid values are
194 * gl_journal_import_pkg.SAVE_DATA, gl_journal_import_pkg.DELETE_DATA,
195 * and gl_journal_import_pkg.DROP_INTERFACE_TABLE. The value,
196 * gl_journal_import_pkg.SAVE_DATA, leaves the data in
197 * the interface table, but does not allow it to be reimported. The value,
198 * gl_journal_import_pkg.DELETE_DATA, indicates the data should be deleted from
199 * the interface table, and the value, gl_journal_import_pkg.DROP_INTERFACE_TABLE,
200 * indicates the interface table should be dropped if all of its data has been
201 * processed successfully by the Journal Import run. If the table cannot be
202 * dropped or if all of the data in the table has not been processed, the data is
203 * deleted. Note, however, that the GL_INTERFACE table is never
204 * dropped, regardless of the setting of the PROCESSED_DATA_ACTION parameter.
205 *
206 * If no value is specified for the PROCESSED_DATA_ACTION parameter, then the
207 * data is deleted once it has been successfully processed. If a value is
208 * specified other than gl_journal_import_pkg.DELETE_DATA, then the action to
209 * take for this data is saved in
210 * GL_INTERFACE_CONTROL until the data is imported successfully. Any Journal
211 * Import run that processes that source and GROUP_ID will execute this action
212 * upon importing the data successfully, even if run for a different
213 * INTERFACE_RUN_ID. Once the data is imported successfully, the row is
214 * deleted from GL_INTERFACE_CONTROL. Any additional runs of Journal Import
215 * for that source and GROUP_ID will execute the default action of deleting
216 * any successfully imported data.
217 *
218 * If a null value of INTERFACE_RUN_ID is passed to this routine, then the routine
219 * automatically generates and returns a unique INTERFACE_RUN_ID. If a null value
220 * of GROUP_ID is passed to this routine, then the routine automatically
221 * generates and returns a unique GROUP_ID.
222 * @param user_je_source_name User-friendly journal source name of the data to be processed.
223 * @param group_id Group id of the data to be processed.
224 * @param set_of_books_id Ledger id of the data to be processed.
225 * @param interface_run_id Identifier of a specific Journal Import run.
226 * @param table_name Table that contains the data to be processed.
227 * @param processed_data_action Action to take once the data has been successfully processed.
228 * @rep:scope public
229 * @rep:lifecycle active
230 * @rep:displayname Populate Interface Control
231 * @rep:compatibility S
232 * @rep:category BUSINESS_ENTITY GL_JOURNAL
233 */
234
235 PROCEDURE populate_interface_control(
236 user_je_source_name VARCHAR2,
237 group_id IN OUT NOCOPY NUMBER,
238 set_of_books_id NUMBER,
239 interface_run_id IN OUT NOCOPY NUMBER,
240 table_name VARCHAR2 DEFAULT NULL,
241 processed_data_action VARCHAR2 DEFAULT NULL);
242
243 --
244 -- Procedure
245 -- get_last_sql
246 -- Purpose
247 -- Returns the last attempted sql*statement executed by the
248 -- create_table and drop_table routines
249 -- History
250 -- 09-OCT-2000 D. J. Ogg Created
251 -- Arguments
252 -- * NONE *
253 -- Example
254 -- last_sql := gl_journal_import_pkg.get_last_sql;
255 -- Notes
256 --
257 /*#
258 * Returns the last sql* statement executed by the CREATE_TABLE and DROP_TABLE
259 * routines.
260 * @return Last sql*statement executed by the CREATE_TABLE and DROP_TABLE routines.
261 * @rep:scope public
262 * @rep:lifecycle active
263 * @rep:displayname Get Last Create/Drop SQL Statement
264 * @rep:compatibility S
265 * @rep:category BUSINESS_ENTITY GL_JOURNAL
266 */
267 FUNCTION get_last_sql RETURN VARCHAR2;
268
269 --
270 -- Procedure
271 -- get_error_msg
272 -- Purpose
273 -- Returns the last error message, if any, for the create_table
274 -- and drop_table routines
275 -- History
276 -- 09-OCT-2000 D. J. Ogg Created
277 -- Arguments
278 -- * NONE *
279 -- Example
280 -- errmsg := gl_journal_import_pkg.get_error_msg;
281 -- Notes
282 --
283 /*#
284 * Returns the last error message, if any, produced by the CREATE_TABLE and DROP_TABLE
285 * routines.
286 * @return Last error message produced by the CREATE_TABLE and DROP_TABLE routines.
287 * @rep:scope public
288 * @rep:lifecycle active
289 * @rep:displayname Get Last Create/Drop Error Message
290 * @rep:compatibility S
291 * @rep:category BUSINESS_ENTITY GL_JOURNAL
292 */
293 FUNCTION get_error_msg RETURN VARCHAR2;
294
295
296 END GL_JOURNAL_IMPORT_PKG;