1 PACKAGE BODY HRDU_DO_API_TRANSLATE AS
2 /* $Header: perduapi.pkb 120.0 2005/05/31 17:19:43 appldev noship $ */
3
4 -- ------------------------ hrdu_insert_mapping ---------------------------
5 -- Description:
6 -- This procedure inserts a record into the hr_du_column_mappings table.
7 -- Most entries are defaulted, and the procedure automatically uses the
8 -- associated sequence. This procedure will be used in a package created
9 -- by hrdu_copy_api.
10 --
11 -- Input Parameters:
12 -- p_api_module - API module mapping references to
13 -- p_column_name - Column name in spreadsheet
14 -- p_mapped_to_name - Column mapped to
15 -- p_mapping_type - Type of mapping - Default 'D'
16 -- p_parent_api_module - Parent API module - Default Null
17 -- p_parent_table - Parent table - Default Null
18 -- p_last_update_date - Last updated date - Default SysDate
19 -- p_last_updated_by - Last updated by - Default 1
20 -- p_last_update_login - Last update login - Default 1
21 -- p_created_by - Created by - Default 1
22 -- p_creation_date - Creation date - Default SysDate
23 --
24 -- -------------------------------------------------------------------------
25 PROCEDURE hrdu_insert_mapping( p_api_module IN VarChar2,
26 p_column_name IN VarChar2,
27 p_mapped_to_name IN VarChar2,
28 p_mapping_type IN VarChar2 DEFAULT 'D',
29 p_parent_api_module IN VarChar2 DEFAULT NULL,
30 p_parent_table IN VarChar2 DEFAULT NULL,
31 p_last_update_date IN Date DEFAULT SYSDATE,
32 p_last_updated_by IN Number DEFAULT 1,
33 p_last_update_login IN Number DEFAULT 1,
34 p_created_by IN Number DEFAULT 1,
35 p_creation_date IN Date DEFAULT SYSDATE)
36 IS
37 cursor api_id_csr is
38 select api_module_id
39 from hr_api_modules
40 where module_name = upper(p_api_module);
41
42 cursor parent_api_id_csr is
43 select api_module_id
44 from hr_api_modules
45 where module_name = upper(p_parent_api_module);
46
47 l_api_id Number(15);
48 l_parent_api_id Number(15) := null;
49
50 BEGIN
51 -- Get the API id for the module
52 open api_id_csr;
53 fetch api_id_csr into l_api_id;
54 close api_id_csr;
55
56 -- Get the API id for the Parent API
57 IF p_parent_api_module is not null THEN
58 open parent_api_id_csr;
59 fetch parent_api_id_csr into l_parent_api_id;
60 close parent_api_id_csr;
61 END IF;
62
63 INSERT INTO hr_du_column_mappings( column_mapping_id, api_module_id, column_name,
64 mapped_to_name, mapping_type, parent_api_module_id,
65 parent_table, last_update_date, last_updated_by,
66 last_update_login, created_by, creation_date )
67 SELECT hr_du_column_mappings_s.NEXTVAL,
68 l_api_id,
69 p_column_name,
70 p_mapped_to_name,
71 p_mapping_type,
72 l_parent_api_id,
73 p_parent_table,
74 p_last_update_date,
75 p_last_updated_by,
76 p_last_update_login,
77 p_created_by,
78 p_creation_date
79 FROM dual
80 WHERE NOT EXISTS
81 (SELECT NULL FROM hr_du_column_mappings
82 WHERE api_module_id = l_api_id
83 AND column_name = p_column_name
84 AND NVL(mapped_to_name,'<null>') = NVL(p_mapped_to_name,'<null>')
85 AND mapping_type = p_mapping_type
86 AND NVL(parent_api_module_id,-24926578) = NVL(l_parent_api_id,-24926578)
87 AND NVL(parent_table,'<null>') = NVL(p_parent_table,'<null>'));
88
89 EXCEPTION
90
91 WHEN OTHERS THEN
92 raise_application_error( -20000, 'Failed to insert item - ' || p_column_name ||
93 ' : ' || sqlerrm(sqlcode));
94
95 END;
96
97
98 -- ------------------------ hrdu_get_api_columns ---------------------------
99 -- Description:
100 -- This procedure goes through each column that needs to be added to the
101 -- table, and outputs it to file, then enter's the associated reference
102 -- into the hrdu_column_mappings table
103 --
104 -- Input Parameters
105 -- p_filehandle - File handle to the file script where the list of columns
106 -- should be outputted to
107 -- p_api_name - Database name of the API to find columns for
108 --
109 -- -------------------------------------------------------------------------
110 PROCEDURE hrdu_get_api_columns( p_filehandle IN UTL_FILE.FILE_TYPE, p_api_name IN Varchar2 )
111 IS
112
113 cursor columns_csr is
114 select distinct column_id, lower( substr(column_name, 3, length(column_name) - 2) ) column_headings
115 from user_tab_columns
116 where table_name = substr( upper( 'HRDPV_' || p_api_name ), 1, 30 )
117 and column_name like 'P_%'
118 order by column_headings;
119
120 columns_rec columns_csr%rowtype;
121 l_scripthandle UTL_FILE.FILE_TYPE;
122 l_location VARCHAR2(2000);
123
124 BEGIN
125 -- Fill in first column of the csv file
126 utl_file.put(p_filehandle, 'ID' );
127
128 -- Open the new script file for writing
129 fnd_profile.get('PER_DATA_EXCHANGE_DIR', l_location);
130 l_scripthandle := utl_file.fopen(l_location, 'hrdu_' || LOWER(p_api_name) || '.sql', 'w', 32767);
131
132 -- First create the header for the new script file
133 utl_file.put_line(l_scripthandle, 'REM /* $Header: perduapi.pkb 120.0 2005/05/31 17:19:43 appldev noship $ */' );
134 utl_file.put_line(l_scripthandle, 'REM +======================================================================+' );
135 utl_file.put_line(l_scripthandle, 'REM | Copyright (c) 2000 Oracle Corporation UK Ltd | ' );
136 utl_file.put_line(l_scripthandle, 'REM | Reading, Berkshire, England | ' );
137 utl_file.put_line(l_scripthandle, 'REM | All rights reserved. | ' );
138 utl_file.put_line(l_scripthandle, 'REM +======================================================================+' );
139 utl_file.put_line(l_scripthandle, 'REM API for translation : ' || upper(p_api_name) );
140 utl_file.put_line(l_scripthandle, 'REM Data pump view name : ' || substr( upper( 'HRDPV_' || p_api_name ), 1, 30 ) );
141 utl_file.put_line(l_scripthandle, 'REM ' );
142 utl_file.put_line(l_scripthandle, 'REM Description: Script created to load the API: ' || p_api_name);
143 utl_file.put_line(l_scripthandle, 'REM into the HRMS Data Uploader. Edit the script then run it to' );
144 utl_file.put_line(l_scripthandle, 'REM populate the data uploader tables.' );
145 utl_file.put_line(l_scripthandle, 'REM ' );
146 utl_file.put_line(l_scripthandle, 'REM Change List: ' );
147 utl_file.put_line(l_scripthandle, 'REM ============ ' );
148 utl_file.put_line(l_scripthandle, 'REM Name Date Version Bug Text ' );
149 utl_file.put_line(l_scripthandle, 'REM -------------- ----------- ------- ------- -----------------------------' );
150 utl_file.put_line(l_scripthandle, 'REM <auto> ' || to_char(sysdate, 'DD-Mon-RRRR') || ' 1 - Auto generated code' );
151 utl_file.put_line(l_scripthandle, 'REM' );
152 utl_file.put_line(l_scripthandle, 'REM ========================================================================' );
153 utl_file.new_line(l_scripthandle, 1 );
154
155 -- Create list of columns that are probably going to need to be changed
156 utl_file.put_line(l_scripthandle, 'REM ------------------------------------------------------------------------' );
157 utl_file.put_line(l_scripthandle, 'REM Columns which are most likely to need editing to include references to' );
158 utl_file.put_line(l_scripthandle, 'REM other API''s are shown below :-' );
159 utl_file.put_line(l_scripthandle, 'REM ' );
160
161 -- Get a list of most common columns that need changing
162 for columns_rec in columns_csr loop
163 IF ( ( substr( columns_rec.column_headings, length(columns_rec.column_headings) - 2, 3 ) = '_id' ) OR
164 ( columns_rec.column_headings like '%user%key%' ) ) THEN
165 utl_file.put_line(l_scripthandle, 'REM * ' || columns_rec.column_headings );
166 END IF;
167 end loop;
168
169 utl_file.put_line(l_scripthandle, 'REM ' );
170 utl_file.put_line(l_scripthandle, 'REM ------------------------------------------------------------------------' );
171 utl_file.put_line(l_scripthandle, 'REM Example of a modified column entry:' );
172 utl_file.put_line(l_scripthandle, 'REM ' );
173 utl_file.put_line(l_scripthandle, 'REM HRDU_DO_API_TRANSLATE.hrdu_insert_mapping(' );
174 utl_file.put_line(l_scripthandle, 'REM p_api_module => ''' || p_api_name || ''',' );
175 utl_file.put_line(l_scripthandle, 'REM p_column_name => ''person_id'',' );
176 utl_file.put_line(l_scripthandle, 'REM p_mapped_to_name => ''p_person_id'',');
177 utl_file.put_line(l_scripthandle, 'REM p_mapping_type => ''R'',' );
178 utl_file.put_line(l_scripthandle, 'REM p_parent_api_module => ''create_us_employee'',' );
179 utl_file.put_line(l_scripthandle, 'REM p_parent_table => null);' );
180 utl_file.put_line(l_scripthandle, 'REM ' );
181 utl_file.put_line(l_scripthandle, 'REM ------------------------------------------------------------------------' );
182 utl_file.new_line(l_scripthandle, 1 );
183 utl_file.put_line(l_scripthandle, 'WHENEVER OSERROR EXIT FAILURE ROLLBACK; ' );
184 utl_file.put_line(l_scripthandle, 'WHENEVER SQLERROR EXIT FAILURE ROLLBACK; ' );
185 utl_file.new_line(l_scripthandle, 1 );
186
187 utl_file.put_line(l_scripthandle, 'DECLARE' );
188 utl_file.new_line(l_scripthandle, 1 );
189 utl_file.put_line(l_scripthandle, 'BEGIN' );
190 utl_file.new_line(l_scripthandle, 1 );
191
192 for columns_rec in columns_csr loop
193 -- Fill in csv file
194 IF lower(columns_rec.column_headings) not like '%user%key%' THEN
195 utl_file.put(p_filehandle, ',' || lower(columns_rec.column_headings) );
196 END IF;
197
198 -- Fill in sql file
199 utl_file.put_line(l_scripthandle, ' HRDU_DO_API_TRANSLATE.hrdu_insert_mapping(' );
200 utl_file.put_line(l_scripthandle, ' p_api_module => ''' || p_api_name || ''',' );
201 IF lower(columns_rec.column_headings) like '%user%key%' THEN
202 utl_file.put_line(l_scripthandle, ' p_mapping_type => ''U'',' );
203 END IF;
204 utl_file.put_line(l_scripthandle, ' p_column_name => ''' || columns_rec.column_headings || ''',' );
205 utl_file.put_line(l_scripthandle, ' p_mapped_to_name => ''' || 'p_' || columns_rec.column_headings || ''');' );
206 utl_file.new_line(l_scripthandle, 1 );
207 end loop;
208
209 utl_file.new_line(l_scripthandle, 1 );
210 utl_file.put_line(l_scripthandle, 'END;' );
211 utl_file.put_line(l_scripthandle, '/' );
212 utl_file.new_line(l_scripthandle, 1 );
213 utl_file.put_line(l_scripthandle, 'COMMIT;' );
214 utl_file.put_line(l_scripthandle, 'EXIT;' );
215
216 -- Close Script file
217 utl_file.fclose(l_scripthandle);
218
219 utl_file.new_line(p_filehandle, 1 );
220 EXCEPTION
221
222 WHEN OTHERS THEN
223 raise_application_error( -20000, 'Exception occurred in hrdu_get_api_columns - ' || substr(SQLERRM,1,200));
224
225 END;
226
227 -- ---------------------------- hrdu_copy_api -------------------------------
228 -- Description:
229 -- This procedure creates a new comma seperated text file for use with the
230 -- HR data uploader. The procedure is passed an API name which it uses to
231 -- create a comma seperated file to be loaded into into the hrdu spreadsheet,
232 -- also the associated database table entries are entered into hr_du_column_mappings
233 --
234 -- Input Parameters
235 -- p_api_name - Database name of the API to find columns for
236 -- p_title_name - Entry to go in the spreadsheet by Title Name
237 -- p_user_key - Entry to go in the spreadsheet by user key
238 -- p_file_name - Name of the file to output to
239 --
240 -- --------------------------------------------------------------------------
241 PROCEDURE hrdu_copy_api( p_api_name IN Varchar2,
242 p_title_name IN VarChar2,
243 p_user_key IN VarChar2,
244 p_file_name IN VarChar2 )
245 IS
246
247 cursor api_id_csr is
248 select api_module_id
249 from hr_api_modules
250 where module_name = upper(p_api_name);
251
252 l_api_id Number(15);
253 l_filehandle UTL_FILE.FILE_TYPE;
254 l_location VARCHAR2(2000);
255
256 BEGIN
257 -- Get the API id for the module
258 open api_id_csr;
259 fetch api_id_csr into l_api_id;
260 close api_id_csr;
261
262 IF l_api_id is not null THEN
263
264 fnd_profile.get('PER_DATA_EXCHANGE_DIR', l_location);
265
266 l_filehandle := utl_file.fopen(l_location, p_file_name, 'w', 32767);
267
268 utl_file.put_line(l_filehandle, 'Descriptor,Start');
269 utl_file.put_line(l_filehandle, 'API,' || p_api_name);
270 utl_file.put_line(l_filehandle, 'Title,' || p_title_name);
271 utl_file.put_line(l_filehandle, 'Process Order,<User_enter>');
272 utl_file.put_line(l_filehandle, 'User Key,' || p_user_key);
273 utl_file.put_line(l_filehandle, 'Descriptor,End');
274
275 utl_file.new_line(l_filehandle);
276
277 utl_file.put_line(l_filehandle, 'Data,Start');
278 hrdu_get_api_columns( l_filehandle, p_api_name );
279 utl_file.put_line(l_filehandle, 'Data,End');
280
281 -- close file
282 utl_file.fclose(l_filehandle);
283
287
284 ELSE
285
286 raise_application_error( -20000, 'API: ' || p_api_name || ' does not exist!' );
288 END IF;
289
290 EXCEPTION
291 WHEN OTHERS THEN
292 raise_application_error( -20000, 'Exception occurred in hrdu_copy_api - ' || substr(SQLERRM,1,200));
293 END ;
294
295 END HRDU_DO_API_TRANSLATE;
296
[Home] [Help]