1 PACKAGE BODY IGF_AP_LOADER_LAUNCH_PKG AS
2 /* $Header: IGFAP49B.pls 120.4 2006/04/18 06:28:30 hkodali noship $ */
3
4
5 FUNCTION get_message_class (p_file_name VARCHAR2)
6 RETURN VARCHAR2
7 IS
8 /*
9 || Created By : rgangara
10 || Created On : 27-Jul-2004
11 || Purpose : Returns just the file name after removing the file path, if any.
12 || Known limitations, enhancements or remarks : The max parameter length would be < 400 chars.
13 || Change History :
14 || Who When What
15 || (reverse chronological order - newest change first)
16 */
17
18 l_firstslash NUMBER;
19 l_finalslash NUMBER;
20 l_nextslash NUMBER;
21 l_filename VARCHAR2(400);
22
23 BEGIN
24
25 l_firstslash := INSTR(p_file_name, '/');
26
27 IF l_firstslash = 0 THEN
28 -- i.e. no '/' in the string and hence entire length is p_file_name
29 l_filename := p_file_name;
30 RETURN (l_filename);
31
32 ELSE -- i.e. '/' exists
33 l_finalslash := l_firstslash;
34 l_nextslash := l_finalslash;
35
36 -- loop till the last '/' is reached.
37 WHILE l_nextslash > 0 LOOP
38 l_nextslash := INSTR(SUBSTR(p_file_name, l_finalslash + 1), '/') ;
39 l_finalslash := l_finalslash + l_nextslash;
40 END LOOP;
41
42 l_filename := SUBSTR(SUBSTR(p_file_name,1), l_finalslash+1);
43 END IF;
44
45 RETURN (l_filename);
46
47 EXCEPTION
48 WHEN OTHERS THEN
49 fnd_file.put_line(fnd_file.log, SQLERRM);
50 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
51 fnd_message.set_token('NAME','IGF_AP_LOADER_LAUNCH_PKG.get_message_class');
52 fnd_file.put_line(fnd_file.log, fnd_message.get);
53 RETURN(NULL);
54 END get_message_class;
55
56
57 FUNCTION get_parameter_filename
58 RETURN VARCHAR2
59 IS
60 /*
61 || Created By : rgangara
62 || Created On : 27-Jul-2004
63 || Purpose : Returns the 1st parameter value for the Conc Program from FND Tables.
64 || Known limitations, enhancements or remarks :
65 || 1. Filename parameter would be the 1st parameter for the conc. program IGFAPX02.
66 || 2. Max parameter length < 400 chars. (Conc. program parameter has only 240 limit.)
67 || 3. Conc program IGFAPX02 is incompatible with itself else the query would returns multiple rows.
68 ||
69 || Change History :
70 || Who When What
71 || (reverse chronological order - newest change first)
72 */
73
74 CURSOR get_parameter_cur IS
75 SELECT fcr.argument1
76 FROM fnd_concurrent_requests fcr, fnd_concurrent_programs ucpn
77 WHERE fcr.program_application_id = ucpn.application_id
78 AND fcr.concurrent_program_id = ucpn.concurrent_program_id
79 AND fcr.phase_code = 'R'
80 AND ucpn.concurrent_program_name = 'IGFAPX06'
81 AND ucpn.application_id = 8405;
82
83 -- IGFAPX02 is the executable name for ISIR Loader Process
84
85 l_parameter_value fnd_concurrent_requests.argument1%TYPE;
86 l_file_name VARCHAR2(100);
87 BEGIN
88
89 -- get the parameter value
90 OPEN get_parameter_cur;
91 FETCH get_parameter_cur INTO l_parameter_value;
92 CLOSE get_parameter_cur;
93
94 IF l_parameter_value IS NOT NULL THEN
95 -- call the function which returns the filename after removing the file path
96 l_file_name := get_message_class(l_parameter_value) ;
97
98 END IF;
99
100 RETURN (l_file_name);
101
102 EXCEPTION
103 WHEN OTHERS THEN
104 fnd_file.put_line(fnd_file.log, SQLERRM);
105 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
106 fnd_message.set_token('NAME','IGF_AP_LOADER_LAUNCH_PKG.get_parameter_filename');
107 fnd_file.put_line(fnd_file.log, fnd_message.get);
108 RETURN(NULL);
109 END get_parameter_filename;
110
111
112
113 PROCEDURE main_process ( errbuf OUT NOCOPY VARCHAR2,
114 retcode OUT NOCOPY NUMBER,
115 p_org_id IN NUMBER,
116 p_file_path IN VARCHAR2,
117 p_file_list IN VARCHAR2
118 )
119 IS
120
121 /*
122 || Created By : rgangara
123 || Created On : 27-JUL-2004
124 || Purpose : Main process which in turn calls the ISIR Loader Process
125 || Known limitations, enhancements or remarks :
126 || Change History :
127 || Who When What
128 || (reverse chronological order - newest change first)
129 ||---------------------------------------------------------------------
130 || azmohamm 04/04/06...... Bug No: 4946522,
131 || Added 'fileNullOrMsgInvalid' exception
132 */
133
134 CURSOR validate_msg_class_cur (cp_msg_class igf_lookups_view.lookup_code%TYPE) IS
135 SELECT 'X'
136 FROM igf_lookups_view
137 WHERE lookup_type = 'IGF_AP_ISIR_MESSAGE_CLASS'
138 AND lookup_code = UPPER(cp_msg_class)
139 AND enabled_flag = 'Y';
140
141 l_found_msg_class VARCHAR2(1);
142 l_req_id NUMBER;
143 l_msg_class VARCHAR2(400);
144 fileNullOrMsgInvalid Exception;
145 l_file_list VARCHAR2(500);
146 l_file_path VARCHAR2(400);
147 l_file_name VARCHAR2(400);
148
149 BEGIN
150
151
152 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
153 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_loader_launch_pkg.main_process.exception','Before Calling method igf_aw_gen.set_org_id' );
154 END IF;
155
156 igf_aw_gen.set_org_id(p_org_id);
157
158 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
159 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_loader_launch_pkg.main_process.exception','After Calling method igf_aw_gen.set_org_id' );
160 END IF;
161
162 errbuf := NULL;
163 retcode := 0;
164
165 l_file_path := TRIM(p_file_path);
166 l_file_list := p_file_list;
167
168 -- Append / to file list
169 IF SUBSTR(l_file_path, LENGTH(l_file_path)) <> '/' THEN
170 l_file_path := l_file_path || '/' ;
171 END IF;
172
173 ------------------------------------------------
174 -- Validate File Name Parameter.
175 ------------------------------------------------
176 IF l_file_list IS NOT NULL THEN
177
178 -- loop through the file list to extract each file name
179 -- pass the file name along with path to the Internal Loader process
180 LOOP
181
182 -- extract file name
183 IF INSTR(l_file_list, ',') = 0 THEN
184 l_file_name := trim(l_file_list);
185 ELSE
186 l_file_name := trim(substr(l_file_list, 1, INSTR(l_file_list, ',')-1));
187 END IF;
188
189 -- call function to get the message class from the file name
190 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
191 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_loader_launch_pkg.main_process.exception','Before Calling method igf_ap_matching_process_pkg.get_msg_class_from_filename' );
192 END IF;
193
194 l_msg_class := igf_ap_matching_process_pkg.get_msg_class_from_filename(l_file_name);
195
196 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
197 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_loader_launch_pkg.main_process.exception','After Calling method igf_ap_matching_process_pkg.get_msg_class_from_filename' );
198 END IF;
199
200 -- validate that the filename derived is a valid message class
201 -- It is assumed that the file name would be the same as the message class.
202 OPEN validate_msg_class_cur(l_msg_class);
203 FETCH validate_msg_class_cur INTO l_found_msg_class;
204
205 IF validate_msg_class_cur%NOTFOUND THEN
206 RAISE fileNullOrMsgInvalid;
207 END IF;
208
209 CLOSE validate_msg_class_cur;
210
211 ------------------------------------------------
212 -- End of parameter Validation
213 ------------------------------------------------
214
215 -- since the filename validation is successful, launch the ISIR Loader process
216 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
217 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_loader_launch_pkg.main_process.exception','Before Calling method fnd_request.submit_request' );
218 END IF;
219 l_req_id := FND_REQUEST.SUBMIT_REQUEST
220 ('IGF',
221 'IGFAPX06',
222 'ISIR Loader Process Internal',
223 NULL,
224 FALSE,
225 TRIM(l_file_path)||l_file_name, -- file path + file name,
226 CHR(0) ,
227 NULL,
228 NULL,
229 NULL,
230 NULL, NULL, NULL, NULL, NULL,
231 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
232 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
233 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
234 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
235 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
236 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
237 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
238 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
239 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
240
241 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
242 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_loader_launch_pkg.main_process.exception','After Calling method fnd_request.submit_request' );
243 END IF;
244
245 IF l_req_id > 0 THEN
246 fnd_message.set_name('IGF','IGF_AP_INT_LDR_PROC_SBMIT');
247 fnd_message.set_token('REQ_ID', l_req_id);
248 fnd_file.put_line(fnd_file.log, fnd_message.get);
249 COMMIT;
250
251 ELSE
252 fnd_message.set_name('IGF','IGF_AP_INT_LDR_PROC_NOT_SBMIT');
253 fnd_file.put_line(fnd_file.log, fnd_message.get);
254 END IF;
255
256 IF INSTR(l_file_list, ',') = 0 THEN
257 EXIT;
258 END IF;
259
260 -- extract rest of list
261 l_file_list := TRIM(SUBSTR(l_file_list, INSTR(l_file_list, ',') + 1, LENGTH(l_file_list)));
262
263 END LOOP;
264 ELSE
265 -- i.e. file name parameter is NULL
266 RAISE fileNullOrMsgInvalid;
267 END IF;
268
269 EXCEPTION
270 WHEN fileNullOrMsgInvalid THEN
271 fnd_message.set_name('IGF','IGF_AP_INVALID_MSG_CLASS');
272 errbuf := fnd_message.get;
273 fnd_file.put_line(fnd_file.log, errbuf);
274 retcode := 2;
275 WHEN others THEN
276 ROLLBACK;
277 retcode := 2;
278 fnd_file.put_line(fnd_file.log, SQLERRM);
279
280 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
281 fnd_message.set_token('NAME','IGF_AP_LOADER_LAUNCH_PKG.MAIN_PROCESS');
282 errbuf := fnd_message.get;
283 igs_ge_msg_stack.conc_exception_hndl;
284 END main_process;
285
286 END igf_ap_loader_launch_pkg;
[Home] [Help]