自定义总账接口表功能开发源代码

paddy.zhang

取自Oracle EBS内部提供的源代码

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

复制代码

难得糊涂

@rep:scope public
* @rep:lifecycle active
* @rep:displayname Get Last Create/Drop SQL Statement
* @rep:compatibility S
* @rep:category BUSINESS_ENTITY GL_JOURNAL
*/
  FUNCTION get_last_sql RETURN VARCHAR2;
  --
  -- Procedure
  --   get_error_msg
  -- Purpose
  --   Returns the last error message, if any, for the create_table
  --   and drop_table routines
  -- History
  --   09-OCT-2000  D. J. Ogg    Created
  -- Arguments
  --   * NONE *
  -- Example
  --   errmsg := gl_journal_import_pkg.get_error_msg;
  -- Notes
  --
/*#
* Returns the last error message, if any, produced by the CREATE_TABLE and DROP_TABLE
* routines.
* @return Last error message produced by the CREATE_TABLE and DROP_TABLE routines.
* @rep:scope public
* @rep:lifecycle active
* @rep:displayname Get Last Create/Drop Error Message
* @rep:compatibility S
* @rep:category BUSINESS_ENTITY GL_JOURNAL
*/
  FUNCTION get_error_msg RETURN VARCHAR2;

END GL_JOURNAL_IMPORT_PKG;