[Home] [Help]
PACKAGE BODY: APPS.ICX_POR_TRACK_VALIDATE_JOB_S
Source
1 package body icx_por_track_validate_job_s as
2 /* $Header: ICXVALJB.pls 115.33 2004/03/31 21:43:36 vkartik ship $ */
3
4 g_error_message varchar2(1000) := '';
5
6 procedure Debug(p_message in varchar2) is
7 begin
8 g_error_message := substr(g_error_message || p_message, 1000);
9 end;
10
11 -- changed by sudsubra
12 -- added p_loaded_header and p_failed_header
13 procedure update_job_status(p_jobno in number,
14 p_new_status in varchar2,
15 p_loaded_items in number,
16 p_failed_items in number,
17 p_loaded_price in number,
18 p_failed_price in number,
19 p_loaded_header in number,
20 p_failed_header in number,
21 p_user_id IN NUMBER) is
22 l_progress varchar2(10) := '000';
23 begin
24 l_progress := '001';
25 update icx_por_batch_jobs
26 set job_status = p_new_status,
27 start_datetime = decode(p_new_status, 'RUNNING', sysdate, start_datetime),
28 items_loaded = p_loaded_items,
29 items_failed = p_failed_items,
30 prices_loaded= p_loaded_price,
31 prices_failed= p_failed_price,
32 headers_loaded = p_loaded_header,
33 headers_failed = p_failed_header,
34 last_updated_by = p_user_id,
35 last_update_date = sysdate
36 where job_number = p_jobno;
37
38 l_progress := '002';
39 exception
40 when others then
41 Debug('[update_job_status-'||l_progress||'] '||SQLERRM);
42 RAISE_APPLICATION_ERROR
43 (-20000, 'Exception at icx_por_track_validate_job_s.update_job_status(ErrLoc = ' || l_progress ||') ' ||
44 'SQL Error : ' || SQLERRM);
45 end;
46
47 FUNCTION create_job(p_supplier_id IN NUMBER,
48 p_supplier_file IN VARCHAR2,
49 p_exchange_file IN VARCHAR2,
50 p_host_ip_address IN VARCHAR2,
51 p_exchange_operator_id IN NUMBER,
52 p_job_type IN VARCHAR2,
53 p_max_failed_lines IN NUMBER,
54 p_user_id IN NUMBER,
55 p_timezone IN VARCHAR2) RETURN NUMBER IS
56 l_progress varchar2(10) := '000';
57 l_jobno number;
58 begin
59
60 l_progress := '001';
61 select icx_por_batch_jobs_s.nextval
62 into l_jobno
63 from sys.dual;
64
65 l_progress := '002';
66 insert into icx_por_batch_jobs (
67 job_number,
68 request_id,
69 supplier_id,
70 supplier_file_name,
71 exchange_file_name,
72 items_loaded,
73 items_failed,
74 job_status,
75 submission_datetime,
76 start_datetime,
77 completion_datetime,
78 failure_message,
79 host_ip_address,
80 exchange_operator_id,
81 job_type,
82 max_failed_lines,
83 timezone,
84 created_by,
85 creation_date,
86 last_updated_by,
87 last_update_date)
88 values (
89 l_jobno,
90 l_jobno,
91 p_supplier_id,
92 p_supplier_file,
93 p_exchange_file,
94 0,
95 0,
96 'PENDING',
97 sysdate,
98 null,
99 null,
100 null,
101 p_host_ip_address,
102 p_exchange_operator_id,
103 p_job_type,
104 p_max_failed_lines,
105 p_timezone,
106 p_user_id,
107 sysdate,
108 p_user_id,
109 sysdate
110 );
111
112 l_progress := '003';
113 return l_jobno;
114
115 exception
116 when others then
117 Debug('[create_job-'||l_progress||'] '||SQLERRM);
118 RAISE_APPLICATION_ERROR
119 (-20000, 'Exception at icx_por_track_validate_job_s.create_job(ErrLoc = ' || l_progress ||') ' ||
120 'SQL Error : ' || SQLERRM);
121 END create_job;
122
123 FUNCTION create_job(p_supplier_id IN NUMBER,
124 p_supplier_file IN VARCHAR2,
125 p_exchange_file IN VARCHAR2,
126 p_host_ip_address IN VARCHAR2,
127 p_exchange_operator_id IN NUMBER,
128 p_job_type IN VARCHAR2,
129 p_max_failed_lines IN NUMBER) RETURN NUMBER IS
130 l_progress varchar2(10) := '000';
131 l_jobno number;
132 begin
133
134 l_progress := '001';
135 select icx_por_batch_jobs_s.nextval
136 into l_jobno
137 from sys.dual;
138
139 l_progress := '002';
140 insert into icx_por_batch_jobs (
141 job_number,
142 request_id,
143 supplier_id,
144 supplier_file_name,
145 exchange_file_name,
146 items_loaded,
147 items_failed,
148 job_status,
149 submission_datetime,
150 start_datetime,
151 completion_datetime,
152 failure_message,
153 host_ip_address,
154 job_type
155 )
156 values (
157 l_jobno,
158 l_jobno,
159 p_supplier_id,
160 p_supplier_file,
161 p_exchange_file,
162 0,
163 0,
164 'PENDING',
165 sysdate,
166 null,
167 null,
168 null,
169 p_host_ip_address,
170 p_job_type
171 );
172
173 l_progress := '003';
174 return l_jobno;
175
176 exception
177 when others then
178 Debug('[create_job-'||l_progress||'] '||SQLERRM);
179 RAISE_APPLICATION_ERROR
180 (-20000, 'Exception at icx_por_track_validate_job_s.create_job(ErrLoc = ' || l_progress ||') ' ||
181 'SQL Error : ' || SQLERRM);
182 END create_job;
183
184 FUNCTION delete_job(p_jobno in number) return varchar2 is
185 BEGIN
186 DELETE FROM ICX_POR_BATCH_JOBS WHERE JOB_NUMBER = p_jobno;
187 DELETE FROM ICX_POR_FAILED_LINES WHERE JOB_NUMBER = p_jobno;
188 DELETE FROM ICX_POR_FAILED_LINE_MESSAGES WHERE JOB_NUMBER = p_jobno;
189 COMMIT;
190 return 'Y';
191
192 EXCEPTION
193 when others then
194 return 'N';
195
196
197 END delete_job;
198
199 /*
200 * Procedure to insert the debug message into
201 * FND_LOG_MESSAGES table using the AOL API.
202 * @param p_debug_message debug message
203 * @param p_log_type log types
204 LOADER: Logs into the loader log file using the fnd apis
205 CONCURRENT: Logs into the concurrent mgr log using the
206 ICX_POR_EXT_UTIL package
207 */
208 PROCEDURE log(p_debug_message VARCHAR2,
209 p_log_type VARCHAR2 DEFAULT 'LOADER' ) is
210
211 l_size NUMBER := 2000;
212 l_debug_msg_length NUMBER := LENGTH(p_debug_message);
213 l_debug_msg VARCHAR2(20000) := p_debug_message;
214 l_start NUMBER := 0;
215 xErrLoc PLS_INTEGER := 100;
216
217 BEGIN
218
219 /*Insert the Debug string */
220 IF p_log_type = 'LOADER' THEN
221 WHILE l_start < l_debug_msg_length LOOP
222 l_start := l_start + l_size;
223 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
224 g_module_name,
225 substrb(l_debug_msg,1,l_size));
226 l_debug_msg := substrb(l_debug_msg,l_size+1);
227 END LOOP;
228 ELSE
229 ICX_POR_EXT_UTL.debug(l_debug_msg);
230 END IF;
231
232 EXCEPTION
233 WHEN OTHERS THEN
234 ROLLBACK;
235 if p_log_type <> 'LOADER' THEN
236 ICX_POR_EXT_UTL.closeLog;
237 end if;
238
239 RAISE_APPLICATION_ERROR(-20000,
240 'Exception at icx_por_track_validate_job_s.log(' || xErrLoc || '): ' || SQLERRM);
241 END log;
242
243
244 /* Procedure to set the debug channel*/
245 PROCEDURE set_debug_channel(p_debug_channel number default 0) is
246 BEGIN
247 IF p_debug_channel=1 THEN
248 g_debug_channel := true;
249 ELSE
250 g_debug_channel :=false;
251 END IF;
252 END set_debug_channel;
253
254 PROCEDURE init_fnd_debug(p_request_id number) is
255 xErrLoc PLS_INTEGER := 100;
256 BEGIN
257 IF g_debug_channel THEN
258 g_request_id := p_request_id;
259 g_module_name:=
260 'ICX.PLSQL.LOADER.'|| g_request_id;
261 fnd_global.apps_initialize(1318, 10001, 178);
262 fnd_profile.put('AFLOG_ENABLED', 'Y');
263 fnd_profile.put('AFLOG_MODULE', g_module_name);
264 fnd_profile.put('AFLOG_LEVEL', '1');
265 fnd_profile.put('AFLOG_FILENAME', '');
266 fnd_log_repository.init;
267 END IF;
268
269 EXCEPTION
270 WHEN OTHERS THEN
271 ROLLBACK;
272
273 RAISE_APPLICATION_ERROR(-20000,
274 'Exception at icx_por_track_validate_job_s.init_fnd_debug('
275 || xErrLoc || '): ' || SQLERRM);
276 END init_fnd_debug;
277
278 end icx_por_track_validate_job_s;