[Home] [Help]
PACKAGE BODY: APPS.ICX_POR_JOB_TRACK_VALIDATION_S
Source
1 package body icx_por_job_track_validation_s as
2 /* $Header: ICXJVALB.pls 115.5 2001/06/29 18:03:56 pkm 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 procedure update_job_status(p_jobno in number,
12 p_new_status in varchar2,
13 p_loaded_items in number,
14 p_failed_items in number) is
15 l_progress varchar2(10) := '000';
16 begin
17 l_progress := '001';
18 update icx_por_batch_jobs
19 set job_status = p_new_status,
20 start_datetime = decode(p_new_status, 'RUNNING', sysdate, start_datetime),
21 items_loaded = p_loaded_items,
22 items_failed = p_failed_items
23 where job_number = p_jobno;
24
25 l_progress := '002';
26 exception
27 when others then
28 Debug('[update_job_status-'||l_progress||'] '||SQLERRM);
29 RAISE_APPLICATION_ERROR
30 (-20000, 'Exception at icx_por_job_track_validation_s.update_job_status(ErrLoc = ' || l_progress ||') ' ||
31 'SQL Error : ' || SQLERRM);
32 end;
33
34 procedure complete_job(p_jobno in number) is
35 l_progress varchar2(10) := '000';
36 begin
37 l_progress := '001';
38 update icx_por_batch_jobs
39 set job_status = decode(items_failed, 0, 'COMPLETED', 'COMPLETED W/ERRORS'),
40 completion_datetime = sysdate
41 where job_number = p_jobno;
42
43 l_progress := '002';
44 exception
45 when others then
46 Debug('[complete_job-'||l_progress||'] '||SQLERRM);
47 RAISE_APPLICATION_ERROR
48 (-20000, 'Exception at icx_por_job_track_validation_s.complete_job(ErrLoc = ' || l_progress ||') ' ||
49 'SQL Error : ' || SQLERRM);
50 end;
51
52 procedure fail_job(p_jobno in number,
53 p_error_message in varchar2) is
54 l_progress varchar2(10) := '000';
55 begin
56 l_progress := '001';
57 update icx_por_batch_jobs
58 set job_status = 'FAILED',
59 completion_datetime = sysdate,
60 failure_message = p_error_message
61 where job_number = p_jobno;
62
63 l_progress := '002';
64 -- update the intermedia index for the failed job to
65 -- index those items that were successfully inserted
66 icx_por_populate_desc.populateCtxDescAll(p_jobno, 'N');
67
68
69 exception
70 when others then
71 Debug('[fail_job-'||l_progress||'] '||SQLERRM);
72 RAISE_APPLICATION_ERROR
73 (-20000, 'Exception at icx_por_job_track_validation_s.fail_job(ErrLoc = ' || l_progress ||') ' ||
74 'SQL Error : ' || SQLERRM);
75 end;
76
77 procedure InsertError(p_jobno in out number,
78 p_descriptor_key in varchar2,
79 p_message_name in varchar2,
80 p_line_number in number
81 ) is
82 l_progress varchar2(10) := '000';
83 begin
84 l_progress := '001';
85 if (p_jobno is null) then
86 l_progress := '002';
87 select icx_por_batch_jobs_s.nextval
88 into p_jobno
89 from sys.dual;
90 end if;
91
92 l_progress := '004';
93 insert into icx_por_failed_line_messages (
94 job_number,
95 descriptor_key,
96 message_name,
97 line_number
98 ) values (
99 p_jobno,
100 p_descriptor_key,
101 p_message_name,
102 p_line_number
103 );
104
105 l_progress := '005';
109 RAISE_APPLICATION_ERROR
106 exception
107 when others then
108 Debug('[InsertError-'||l_progress||'] '||SQLERRM);
110 (-20000, 'Exception at icx_por_job_track_validation_s.InsertError(ErrLoc = ' || l_progress ||') ' ||
111 'SQL Error : ' || SQLERRM);
112 end;
113
114 function validate_item_price(p_jobno in out number,
115 p_action in varchar2,
116 p_row_type in varchar2,
117 p_supplier_id in number,
118 p_supplier in varchar2,
119 p_supplier_part_num in varchar2,
120 p_description in varchar2,
121 p_unspsc in varchar2,
122 p_lead_time in number,
123 p_availability in varchar2,
124 p_item_type in varchar2,
125 p_buyer in varchar2,
126 p_uom in varchar2,
127 p_price in number,
128 p_currency_code in varchar2,
129 p_line_number in number,
130 p_job_supplier_name in varchar2,
131 p_business_group_id in number,
132 p_supplier_site in varchar2) return varchar2 is
133 l_progress varchar2(10) := '000';
134 l_need_commit boolean := false;
135 l_foo varchar2(1) := null;
136 l_valid boolean := true;
137 l_buyer_id number := -1;
138 l_supplier_id number := -1;
139 begin
140 l_need_commit := (p_jobno is null);
141
142 if (p_row_type in ('ITEM_PRICE', 'ITEM')) then
143 l_progress := '010';
144 if (p_supplier is null) then
145 l_progress := '012';
146 l_valid := false;
147 InsertError(p_jobno, 'SUPPLIER', 'ICX_POR_SUPPLIER_REQD',
148 p_line_number);
149 /* We don't need to validate suppliers
150 else
151 if (p_supplier <> p_job_supplier_name) then
152 -- Supplier specified in item does not match supplier submitting
153 -- the job. Fail the item.
154 InsertError(p_jobno, 'SUPPLIER','ICX_POR_DIFFERENT_SUPPLIER',
155 p_line_number);
156 l_valid := false;
157 else
158 */
159 else
160 begin
161 l_progress := '014';
162 select vendor_id
163 into l_supplier_id
164 from po_vendors
165 where vendor_name = p_supplier
166 and rownum = 1;
167 exception
168 when no_data_found then
169 l_progress := '018';
170 l_valid := false;
171 InsertError(p_jobno, 'SUPPLIER', 'ICX_POR_INVALID_SUPPLIER',
172 p_line_number);
173 end;
174
175 /*end if;*/
176
177 end if;
178
179 l_progress := '020';
180 if (p_supplier_part_num is null) then
181 l_progress := '022';
182 l_valid := false;
183 InsertError(p_jobno, 'SUPPLIER_PART_NUM', 'ICX_POR_SUPPLIER_PART_REQD',
184 p_line_number);
185 elsif (p_action in ('ADD','UPDATE')) then
186 begin
187 l_progress := '024';
188 select 'Y'
189 into l_foo
190 from icx_por_items
191 where a3 = p_supplier_part_num
192 and a1 = p_supplier
193 and rownum = 1;
194
195 if (p_action = 'ADD') then
196 l_progress := '028';
197 l_valid := false;
198 InsertError(p_jobno, 'SUPPLIER_PART_NUM', 'ICX_POR_DUP_SUPPLIER_PART',
199 p_line_number);
200 end if;
201
202 -- bug 1791053
203 if ( length(p_supplier_part_num) > 25 ) then
204 l_valid := false;
205 InsertError(p_jobno, 'SUPPLIER_PART_NUM',
206 'ICX_POR_MAX_SUP_PART_LEN', p_line_number);
207 end if;
208 l_progress := '029';
209
210
211 exception
212 when no_data_found then
213 if (p_action = 'UPDATE') then
214 l_valid := false;
215 InsertError(p_jobno, 'SUPPLIER_PART_NUM',
216 'ICX_POR_PRC_INVALID_SUP_PART', p_line_number);
217 end if;
218 l_progress := '018';
219 end;
220 end if;
221
222 -- bug 1791053
223 if ( p_action in ('ADD', 'UPDATE') AND
224 length(p_supplier_part_num) > 25 ) then
225 l_valid := false;
226 InsertError(p_jobno, 'SUPPLIER_PART_NUM',
227 'ICX_POR_MAX_SUP_PART_LEN', p_line_number);
228 end if;
229
230 l_progress := '030';
231 if (p_description is null AND p_action = 'ADD') then
232 l_progress := '032';
233 l_valid := false;
234 InsertError(p_jobno, 'DESCRIPTION', 'ICX_POR_INVALID_DESCRIPTION',
235 p_line_number);
236 end if;
237
238
239 /*
240 * bug 1364308 - we don't need to validate unspsc codes anymore
241 *
242 l_progress := '040';
243 if p_unspsc is not null then
244 begin
245 l_progress := '042';
246 select 'Y'
247 into l_foo
248 from icx_unspsc_codes
249 where unspsc_code = p_unspsc
250 and rownum = 1;
251 exception
252 when no_data_found then
253 l_progress := '048';
254 l_valid := false;
255 InsertError(p_jobno, 'UNSPSC', 'ICX_POR_INVALID_UNSPSC',
256 p_line_number);
257 end;
261 l_progress := '050';
258 end if;
259 */
260
262 if (p_lead_time is not null) then
263 if (p_lead_time <= 0) then
264 l_progress := '052';
265 l_valid := false;
266 InsertError(p_jobno, 'LEAD_TIME', 'ICX_POR_INVALID_LEAD_TIME',
267 p_line_number);
268 end if;
269 end if;
270
271 l_progress := '060';
272 if (p_availability is not null) then
273 begin
274 l_progress := '062';
275 select 'Y'
276 into l_foo
277 from fnd_lookups
278 where lookup_type = 'ICX_CATALOG_AVAILABILITY'
279 and lookup_code = p_availability
280 and rownum = 1;
281 exception
282 when no_data_found then
283 l_progress := '068';
284 l_valid := false;
285 InsertError(p_jobno, 'AVAILABILITY', 'ICX_POR_INVALID_AVAILABILITY',
286 p_line_number);
287 end;
288 end if;
289
290 l_progress := '070';
291 if (p_item_type is not null) then
292 begin
293 l_progress := '072';
294 select 'Y'
295 into l_foo
296 from fnd_lookups
297 where lookup_type = 'ICX_CATALOG_ITEM_TYPE'
298 and lookup_code = p_item_type
299 and rownum = 1;
300 exception
301 when no_data_found then
302 l_progress := '078';
303 l_valid := false;
304 InsertError(p_jobno, 'ITEM_TYPE', 'ICX_POR_INVALID_ITEM_TYPE',
305 p_line_number);
306 end;
307 end if;
308
309 end if;
310
311 l_progress := '100';
312
313 if (p_row_type in ('PRICE')) then
314
315 l_progress := '110';
316 if (p_supplier is null) then
317 l_progress := '112';
318 l_valid := false;
319 InsertError(p_jobno, 'SUPPLIER', 'ICX_POR_PRC_SUPPLIER_REQD',
320 p_line_number);
321 /* We don't need to validate supplier
322 else
323 if (p_supplier <> p_job_supplier_name) then
324 InsertError(p_jobno, 'SUPPLIER','ICX_POR_PRC_DIFF_SUPPLIER',
325 p_line_number);
326 l_valid := false;
327 else
328 */
329 else
330 begin
331 l_progress := '114';
332 select vendor_id
333 into l_supplier_id
334 from po_vendors
335 where vendor_name = p_supplier
336 and rownum = 1;
337 exception
338 when no_data_found then
339 l_progress := '118';
340 l_valid := false;
341 InsertError(p_jobno, 'SUPPLIER', 'ICX_POR_PRC_INVALID_SUPPLIER',
342 p_line_number);
343 end;
344
345 /*end if;*/
346
347 end if;
348
349
350 l_progress := '120';
351 if (p_supplier_part_num is null) then
352 l_progress := '122';
353 l_valid := false;
354 InsertError(p_jobno, 'SUPPLIER_PART_NUM', 'ICX_POR_PRC_SUP_PART_REQD',
355 p_line_number);
356 elsif (p_action = 'ADD') then
357 begin
358 l_progress := '124';
359 select 'Y'
360 into l_foo
361 from icx_por_items
362 where a3 = p_supplier_part_num
363 and a1 = p_supplier
364 and rownum = 1;
365
366 l_progress := '128';
367
368 exception
369 when no_data_found then
370 l_progress := '118';
371 l_valid := false;
372 InsertError(p_jobno, 'SUPPLIER_PART_NUM',
373 'ICX_POR_PRC_INVALID_SUP_PART',
374 p_line_number);
375 end;
376 end if;
377
378 end if;
379
380 l_progress := '200';
381
382 if ((p_row_type in ('ITEM_PRICE', 'PRICE')) OR
383 (p_row_type in ('ITEM') and p_action in ('ADD'))) then
384
385 l_progress := '230';
386 if (p_buyer is null) then
387 /* This is allowed since in single-org setup
388 there won't be any operating unit
389 l_progress := '231';
390 l_valid := false;
391 InsertError(p_jobno, 'BUYER', 'ICX_POR_BUYER_REQD',
392 p_line_number);
393 */
394 null;
395 else
396 begin
397 l_progress := '232';
398 /*l_buyer_id := to_number(p_buyer);*/
399
400 -- Only need to check if not the default buyer
401 /*if l_buyer_id <> -1 then*/
402 select organization_id
403 into l_buyer_id
404 from hr_all_organization_units
405 /*where organization_id = l_buyer_id*/
406 where name = p_buyer
407 and business_group_id = p_business_group_id
408 and rownum = 1;
409 /*end if;*/
410
411 exception
412 when no_data_found then
413 l_progress := '248';
414 l_valid := false;
415 InsertError(p_jobno, 'BUYER', 'ICX_POR_INVALID_BUYER',
416 p_line_number);
417 end;
418 end if;
419
420 l_progress := '235';
421 if (l_supplier_id <> -1 and p_supplier_site is not null) then
422 -- Check if supplier site exists
423
424 if (l_buyer_id = -1) then
425 begin
426 select 'Y'
427 into l_foo
428 from po_vendor_sites_all
429 where vendor_id = l_supplier_id
433 exception
430 and vendor_site_code = p_supplier_site
431 and rownum = 1;
432
434 when no_data_found then
435 l_progress := '235_0';
436 l_valid := false;
437 InsertError(p_jobno, 'SUPPLIER_SITE','ICX_POR_INVALID_SUPP_SITE',
438 p_line_number);
439 end;
440
441 else
442 begin
443 l_progress := '235_1';
444 select 'Y'
445 into l_foo
446 from po_vendor_sites_all
447 where vendor_id = l_supplier_id
448 and vendor_site_code = p_supplier_site
449 and org_id = l_buyer_id
450 and rownum = 1;
451
452 exception
453 when no_data_found then
454 l_progress := '235_2';
455 l_valid := false;
456 InsertError(p_jobno, 'SUPPLIER_SITE','ICX_POR_INVALID_SUPP_SITE',
457 p_line_number);
458 end;
459 end if;
460 end if;
461
462 l_progress := '240';
463 if (p_uom is null) then
464 l_progress := '241';
465 l_valid := false;
466 InsertError(p_jobno, 'UOM', 'ICX_POR_UOM_REQD',
467 p_line_number);
468 end if;
469
470 /* UOM now validated in OracleCatalogCreator.java
471 else
472 begin
473 l_progress := '242';
474 select 'Y'
475 into l_foo
476 from mtl_units_of_measure
477 where uom_code = p_uom
478 and rownum = 1;
479 exception
480 when no_data_found then
481 l_progress := '248';
482 l_valid := false;
483 InsertError(p_jobno, 'UOM', 'ICX_POR_INVALID_UOM',
484 p_line_number);
485 end;
486 end if;
487 */
488
489 l_progress := '250';
490 if (p_price is null) then
491 l_progress := '251';
492 l_valid := false;
493 InsertError(p_jobno, 'PRICE', 'ICX_POR_PRICE_REQD',
494 p_line_number);
495 /* price == 0 is valid */
496 -- elsif (p_price <= 0) then
497 elsif (p_price < 0) then
498 l_progress := '252';
499 l_valid := false;
500 InsertError(p_jobno, 'PRICE', 'ICX_POR_INVALID_PRICE',
501 p_line_number);
502 end if;
503
504
505 l_progress := '260';
506 if (p_currency_code is null) then
507 l_progress := '261';
508 l_valid := false;
509 InsertError(p_jobno, 'CURRENCY_CODE', 'ICX_POR_CURRENCY_REQD',
510 p_line_number);
511 else
512 begin
513 l_progress := '262';
514 select 'Y'
515 into l_foo
516 from fnd_currencies
517 where currency_code = p_currency_code
518 and rownum = 1;
519 exception
520 when no_data_found then
521 l_progress := '268';
522 l_valid := false;
523 InsertError(p_jobno, 'CURRENCY_CODE', 'ICX_POR_INVALID_CURRENCY',
524 p_line_number);
525 end;
526 end if;
527
528 end if;
529
530 l_progress := '300';
531 if (l_need_commit) then
532 commit;
533 end if;
534
535 l_progress := '301';
536 if (l_valid) then
537 return 'Y';
538 else
539 return 'N';
540 end if;
541
542 exception
543 when others then
544 Debug('[validate_item_price-'||l_progress||'] '||SQLERRM);
545 RAISE_APPLICATION_ERROR
546 (-20000, 'Exception at icx_por_job_track_validation_s.validate_item_price(ErrLoc = ' || l_progress ||') ' ||
547 'SQL Error : ' || SQLERRM);
548 end;
549
550 procedure get_next_job(p_jobno out number,
551 p_exchange_file_name out varchar2,
552 p_supplier_id out number,
553 p_supplier_name out varchar2,
554 p_host_ip_address in varchar2,
555 p_exchange_operator out varchar2) is
556 l_progress varchar2(10) := '000';
557 begin
558 l_progress := '010';
559 begin
560 select min(job_number)
561 into p_jobno
562 from icx_por_batch_jobs
563 where job_status='PENDING'
564 and host_ip_address = p_host_ip_address;
565 exception
566 when no_data_found then
567 l_progress := '010';
568 end;
569 l_progress := '020';
570 if (p_jobno is not null) then
571
572 select jb.exchange_file_name,
573 jb.supplier_id,
574 /*pt.vendor_name,*/
575 'DEFAULT_SUPPLIER_NAME',
576 jb.exchange_operator_name
577 into p_exchange_file_name,
578 p_supplier_id,
579 p_supplier_name,
580 p_exchange_operator
581 /* from po_vendors pt, */
582 from icx_por_batch_jobs jb
583 where job_number = p_jobno;
584 -- and jb.supplier_id = pt.vendor_id;
585
586 update icx_por_batch_jobs
587 set job_status = 'RUNNING',
588 start_datetime = sysdate
589 where job_number = p_jobno;
590
591 end if;
592 l_progress := '030';
593 exception
594 when others then
595 Debug('[get_next_job-'||l_progress||'] '||SQLERRM);
596 RAISE_APPLICATION_ERROR
597 (-20000, 'Exception at icx_por_job_track_validation_s.get_next_job(E
601
598 rrLoc = ' || l_progress ||') ' ||
599 'SQL Error : ' || SQLERRM);
600 end;
602 function create_job(p_supplier_id in number,
603 p_supplier_file in varchar2,
604 p_exchange_file in varchar2,
605 p_host_ip_address in varchar2,
606 p_exchange_operator in varchar2) return number is
607 l_progress varchar2(10) := '000';
608 l_jobno number;
609 begin
610
611 l_progress := '001';
612 select icx_por_batch_jobs_s.nextval
613 into l_jobno
614 from sys.dual;
615
616 l_progress := '002';
617 insert into icx_por_batch_jobs (
618 job_number,
619 supplier_id,
620 supplier_file_name,
621 exchange_file_name,
622 items_loaded,
623 items_failed,
624 job_status,
625 submission_datetime,
626 start_datetime,
627 completion_datetime,
628 failure_message,
629 host_ip_address,
630 exchange_operator_name)
631 values (
632 l_jobno,
633 p_supplier_id,
634 p_supplier_file,
635 p_exchange_file,
636 0,
637 0,
638 'PENDING',
639 sysdate,
640 null,
641 null,
642 null,
643 p_host_ip_address,
644 p_exchange_operator
645 );
646
647 l_progress := '003';
648 return l_jobno;
649
650 exception
651 when others then
652 Debug('[create_job-'||l_progress||'] '||SQLERRM);
653 RAISE_APPLICATION_ERROR
654 (-20000, 'Exception at icx_por_job_track_validation_s.create_job(Err
655 Loc = ' || l_progress ||') ' ||
656 'SQL Error : ' || SQLERRM);
657 end;
658
659 end icx_por_job_track_validation_s;