1 PACKAGE BODY HR_ADE_UPGRADE AS
2 /* $Header: peadeupg.pkb 120.0 2005/05/31 04:58:47 appldev noship $ */
3 --
4 ------------------------ get_to_seperator -------------------------------------
5 -- helper function for parse_ini_file procedure
6 -- returns left most characters of supplied string upto first
7 -- occurance of passed character
8 --
9 -- Input Parameters
10 -- p_instr - string from which substring to be selected
11 -- p_inchar - character at which selection is to stop
12 --
13 -- Output Parameters
14 -- <none>
15 --
16 -- Return Value
17 -- substring of input
18 -------------------------------------------------------------------------------
19 FUNCTION get_to_seperator(p_instr VARCHAR2, p_inchar CHAR) RETURN VARCHAR2 IS
20
21 l_substr VARCHAR2(200) DEFAULT NULL;
22 l_pos NUMBER(3) ;
23 BEGIN
24
25 l_pos := INSTR(p_instr,p_inchar);
26 -- get position of 1st 'inchar' in input line
27
28 l_substr := substr(p_instr,1,l_pos-1);
29 -- substring of inline from 1st char upto but not inc. first 'inchar'
30 RETURN l_substr;
31
32 END get_to_seperator;
33
34 --------------------- crop_to_seperator -----------------------------
35 -- helper function for parse_ini_file procedure
36 -- discards leading characters upto first occurance of supplied character
37 --
38 -- Input Parameters
39 -- p_instr - string from which substring to be selected
40 -- p_inchar - character at which selection is to start
41 -- Output Parameters
42 -- <none>
43 --
44 -- Return Value
45 -- substring of input
46 --------------------------------------------------------------
47
48 FUNCTION crop_to_seperator(p_instr VARCHAR2, p_inchar CHAR) RETURN VARCHAR2 IS
49
50 l_substr VARCHAR2(2000);
51 l_pos NUMBER(3);
52 BEGIN
53
54 l_pos := INSTR(p_instr,p_inchar);
55 -- get position of 1st 'inchar' in input line
56
57 l_substr := substr(p_instr,l_pos+1);
58 -- substring of inline from 1st first 'inchar' to end of string
59 RETURN l_substr;
60
61 END crop_to_seperator;
62 --
63 ---------------------------- parse_ini_file -----------------------------
64 -- This process reads style setting from the ADE.ini file
65 -- creates output file with metadata suitable for upload to Web ADI
66 -- Will be run as a concurrent process
67 --
68 -- Input Parameters
69 -- p_file - name of input file, normally ADE.INI
70 --
71 -- Output Parameters
72 -- errbuff - variable used by concurrent process manager
73 -- retcode - variable used by concurrent process manager
74 --------------------------------------------------------------------------
75 PROCEDURE parse_ini_file(errbuff OUT NOCOPY VARCHAR2
76 ,retcode OUT NOCOPY NUMBER
77 ,p_file IN VARCHAR2) IS
78
79 l_dir VARCHAR2(240);
80 l_infile UTL_FILE.FILE_TYPE ;
81 l_out_file UTL_FILE.FILE_TYPE ;
82 --
83 l_inline VARCHAR2(32767) ;
84 --
85 l_package VARCHAR2(500) DEFAULT NULL;
86 l_procedure VARCHAR2(500) DEFAULT NULL;
87 l_interface_param VARCHAR2(500) DEFAULT NULL;
88 --
89 l_groupname VARCHAR2(500) DEFAULT NULL;
90 l_setting1 VARCHAR2(500) DEFAULT NULL;
91 l_setting2 VARCHAR2(500) DEFAULT NULL;
92 l_setting3 VARCHAR2(500) DEFAULT NULL;
93 l_setting4 VARCHAR2(500) DEFAULT NULL;
94 l_setting5 VARCHAR2(500) DEFAULT NULL;
95 l_setting6 VARCHAR2(500) DEFAULT NULL;
96 l_setting7 VARCHAR2(500) DEFAULT NULL;
97 l_all_settings VARCHAR2(2000) ;
98
99 l_int_igr_name VARCHAR2(500) ;
100 l_date DATE ;
101 l_datestamp VARCHAR2(12) ;
102
103 l_out_file_name varchar2(50);
104 BEGIN
105 --set Concurrent process variables (used to indicate if warnings encounters)
106 retcode := 0; --can be 0 (success),1(warnings) or 2(failure)
107 errbuff := 'Output Files Created';
108 --
109 --get directory from system profile value
110 fnd_profile.get('PER_DATA_EXCHANGE_DIR', l_dir);
111 -- open input file for reading
112 l_infile := UTL_FILE.FOPEN(l_dir,p_file,'r');
113 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Processing '||l_dir||'/'||p_file);
114 select sysdate into l_date from dual;
115 l_datestamp := to_char(l_date,'DDMMYYHH24MISS');
116 --
117 --create output file names
118 l_out_file_name := 'UPG' ||l_datestamp||'.csv';
119 --
120 -- open/create file for writing
121 l_out_file := UTL_FILE.FOPEN(l_dir,l_out_file_name,'w');
122 --
123 BEGIN -- annonymous block to trap NO_DATA_FOUND at end of file
124 LOOP
125 EXIT WHEN 1=2;-- continue reading until NO_DATA_FOUND error
126 -- raised when end-of-file reached
127 --
128 --read 1 line from infile into inline
129 UTL_FILE.get_line(l_infile,l_inline);
130 --
131 IF substr(l_inline,1,1) = '[' THEN
132 l_groupname := l_inline; -- store group name
133 l_groupname := REPLACE(l_groupname,'[','');
134 l_groupname := REPLACE(l_groupname,']',''); --without square brackets
135 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Processing Group '||l_groupname);
136 END IF; --end if line is group name
137 --
138 IF UPPER(substr(l_inline,1,5)) = 'STYLE' THEN
139 --parse for values
140 --settingN is the Nth comma seperated value in .ini file list
141 FND_FILE.PUT(FND_FILE.LOG, ' Processing Style ');
142 --get upto first '=' and discard processed part of string
143 l_setting1 := HR_ADE_UPGRADE.get_to_seperator(l_inline,'=');
144 l_inline := HR_ADE_UPGRADE.crop_to_seperator(l_inline, '=');
145 -- get style name and discard processed part of string
146 l_setting2 := HR_ADE_UPGRADE.get_to_seperator(l_inline,',');
147 FND_FILE.PUT(FND_FILE.LOG, l_setting2);
148 FND_FILE.NEW_LINE(FND_FILE.LOG);
149 l_inline := HR_ADE_UPGRADE.crop_to_seperator(l_inline, ',');
150 -- get document name and discard processed part of string
151 l_setting3 := HR_ADE_UPGRADE.get_to_seperator(l_inline,',');
152 l_inline := HR_ADE_UPGRADE.crop_to_seperator(l_inline, ',');
153 -- get view name and discard processed part of string
154 l_setting4 := HR_ADE_UPGRADE.get_to_seperator(l_inline,',');
155 l_inline := HR_ADE_UPGRADE.crop_to_seperator(l_inline, ',');
156 -- get single/multiple and discard processed part of string
157 l_setting5 := HR_ADE_UPGRADE.get_to_seperator(l_inline,',');
158 l_inline := HR_ADE_UPGRADE.crop_to_seperator(l_inline, ',');
159 -- get api name and discard processed part of string
160 l_setting6 := HR_ADE_UPGRADE.get_to_seperator(l_inline,',');
161 l_inline := HR_ADE_UPGRADE.crop_to_seperator(l_inline, ',');
162 -- get letter name and discard processed part of string
163 l_setting7 := HR_ADE_UPGRADE.get_to_seperator(l_inline,',');
164 l_inline := HR_ADE_UPGRADE.crop_to_seperator(l_inline, ',');
165 --
166 -- validate view name, replace with group name if no specified
167 IF l_setting4 = '' OR l_setting4 IS NULL THEN
168 l_setting4 := '<<VIEWNAME>>';
169 FND_FILE.PUT_LINE(FND_FILE.LOG,
170 ' WARNING: Please add view name to this style.');
171 retcode := 1; -- mark Concurrent process with warning
172 END IF;
173 --
174 --get package and procedure name from API name
175 IF l_setting6 IS NOT NULL THEN
176 l_package := get_to_seperator(l_setting6,'.');
177 l_procedure := crop_to_seperator(l_setting6,'.');
178 ELSE
179 --no api specified, so blank previous package and procedure
180 l_package :=null;
181 l_procedure := null;
182 END IF;
183 --create internal integrator name from user integrator name
184 l_int_igr_name := SUBSTR((UPPER(REPLACE(l_setting2,' ','_'))),1,20);
185 --
186 --create interface parameter list name
187 l_interface_param := l_setting2||' Parameters';
188 --concatenate all processed strings to be transfered to CSV file
189 -- with placeholder values for data to be entered by user
190 l_all_settings := '<<APPLICATION_ID>>'||','||
191 l_setting2 ||','||
192 l_setting4 ||','||
193 l_groupname ||','||
194 l_package ||','||
195 l_procedure ||','||
196 l_setting2 ||','||
197 l_interface_param||','||
198 'PROCEDURE';
199 --write modified line to appropriate csv file, appending new line char/s
200 IF l_package IS NOT NULL THEN
201 --has an API specified so is update style
202 utl_file.put_line(l_out_file,'UPDATE,'||l_all_settings);
203 ELSE
204 --No API specified so is download type
205 utl_file.put_line(l_out_file,'DOWNLOAD,'||l_all_settings);
206 END IF;
207 -- There cannot be create style as these were not supported in ADE
208
209 END IF;--end of if line is style specifier
210 END LOOP;
211
212 EXCEPTION
213 WHEN NO_DATA_FOUND THEN -- reached end of file
214 --will drop out of anonymous block and contine main procedure execution.
215 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Processed '|| p_file);
216 END;--end of anonymous block for loop
217 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Closing files');
218 UTL_FILE.FCLOSE(l_infile); -- close file opened for reading
219
220 UTL_FILE.FFLUSH(l_out_file); --force physical write of data
221
222 UTL_FILE.FCLOSE(l_out_file); -- close file opened for writing
223
224 IF retcode <>0 --warnings encountered
225 THEN
226 errbuff := errbuff || ' - Some View Names must be added manually';
227 ELSE
228 errbuff := errbuff || ' - Procedure completed Successfully';
229 END IF;
230 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Complete');
231 --Concurrent Process log line
232 EXCEPTION
233 WHEN NO_DATA_FOUND THEN -- file opened for reading does not exist
234 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Input File Not Found');
235 --Concurrent Process log line
236 retcode := 2; -- mark Concurrent process as failed
237 UTL_FILE.FCLOSE(l_infile); -- close file opened for reading
238 UTL_FILE.FCLOSE(l_out_file);
239 FND_MESSAGE.SET_NAME('PER','PER_289859_FILE_NOT_FOUND');
240 FND_MESSAGE.RAISE_ERROR;
241
242 WHEN UTL_FILE.INVALID_OPERATION THEN
243 FND_FILE.PUT_LINE(FND_FILE.LOG,
244 'The supplied file cannot be opened.'||
245 'Please check the file name and make sure this file exists'||
246 ' in the correct directory, and that the file is readable');
247 --Concurrent Process log line
248 retcode := 2; -- mark Concurrent process as failed
249 UTL_FILE.FCLOSE(l_infile); -- close file opened for reading
250 UTL_FILE.FCLOSE(l_out_file);
251 FND_MESSAGE.SET_NAME('PER','PER_289863_INVALID_OP');
252 FND_MESSAGE.RAISE_ERROR;
253
254 WHEN UTL_FILE.READ_ERROR THEN
255 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Input File Error');
256 --Concurrent Process log line
257 retcode := 2; -- mark Concurrent process as failed
258 UTL_FILE.FCLOSE(l_infile); -- close file opened for reading
259 UTL_FILE.FCLOSE(l_out_file);
260 FND_MESSAGE.SET_NAME('PER','PER_289860_READ_ERROR');
261 FND_MESSAGE.RAISE_ERROR;
262
263 WHEN UTL_FILE.WRITE_ERROR THEN
264 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Output File Error');
265 --Concurrent Process log line
266 retcode := 2; -- mark Concurrent process as failed
267 UTL_FILE.FCLOSE(l_infile); -- close file opened for reading
268 UTL_FILE.FCLOSE(l_out_file);
269 FND_MESSAGE.SET_NAME('PER','PER_289861_WRITE_ERROR');
270 FND_MESSAGE.RAISE_ERROR;
271
272 WHEN UTL_FILE.INVALID_PATH THEN
273 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invalid Directory Supplied');
274 --Concurrent Process log line
275 retcode := 2; -- mark Concurrent process as failed
276 UTL_FILE.FCLOSE(l_infile); -- close file opened for reading
277 UTL_FILE.FCLOSE(l_out_file);
278 FND_MESSAGE.SET_NAME('PER','PER_289862_INVALID_PATH');
279 FND_MESSAGE.RAISE_ERROR;
280
281 END parse_ini_file;
282
283 END HR_ADE_UPGRADE;
284