[Home] [Help]
PACKAGE BODY: APPS.PO_ASL_API_PUB
Source
1 PACKAGE BODY po_asl_api_pub AS
2 /* $Header: PO_ASL_API_PUB.plb 120.3.12020000.2 2013/02/11 13:18:27 vegajula noship $ */
3
4 g_session_key NUMBER;
5
6 PROCEDURE dump_to_asl_gtt(
7 p_asl_rec IN po_approved_supplier_list_rec
8 , x_return_status OUT NOCOPY VARCHAR2
9 , x_return_msg OUT NOCOPY VARCHAR2
10 );
11
12 PROCEDURE dump_to_asl_attr_gtt(
13 p_asl_attr_rec IN po_asl_attributes_rec
14 , x_return_status OUT NOCOPY VARCHAR2
15 , x_return_msg OUT NOCOPY VARCHAR2
16 );
17
18 PROCEDURE dump_to_asl_doc_gtt(
19 p_asl_doc_rec IN po_asl_documents_rec
20 , x_return_status OUT NOCOPY VARCHAR2
21 , x_return_msg OUT NOCOPY VARCHAR2
22 );
23
24 PROCEDURE dump_to_chv_auth_gtt(
25 p_chv_auth_rec IN chv_authorizations_rec
26 , x_return_status OUT NOCOPY VARCHAR2
27 , x_return_msg OUT NOCOPY VARCHAR2
28 );
29
30 PROCEDURE dump_to_capacity_gtt(
31 p_capacity_rec IN po_supplier_item_capacity_rec
32 , x_return_status OUT NOCOPY VARCHAR2
33 , x_return_msg OUT NOCOPY VARCHAR2
34 );
35
36 PROCEDURE dump_to_tolerance_gtt(
37 p_tolerance_rec IN po_supplier_item_tolerance_rec
38 , x_return_status OUT NOCOPY VARCHAR2
39 , x_return_msg OUT NOCOPY VARCHAR2
40 );
41
42 PROCEDURE derive_ids_past(
43 x_return_status OUT NOCOPY VARCHAR2
44 , x_return_msg OUT NOCOPY VARCHAR2
45 );
46
47 PROCEDURE derive_ids_podoc(
48 x_return_status OUT NOCOPY VARCHAR2
49 , x_return_msg OUT NOCOPY VARCHAR2
50 );
51
52 PROCEDURE derive_ids_ch_auth(
53 x_return_status OUT NOCOPY VARCHAR2
54 , x_return_msg OUT NOCOPY VARCHAR2
55 );
56
57 PROCEDURE derive_ids_asl_attr(
58 x_return_status OUT NOCOPY VARCHAR2
59 , x_return_msg OUT NOCOPY VARCHAR2
60 );
61
62 PROCEDURE derive_ids_sup_cap(
63 x_return_status OUT NOCOPY VARCHAR2
64 , x_return_msg OUT NOCOPY VARCHAR2
65 );
66
67 PROCEDURE derive_ids_sup_tol(
68 x_return_status OUT NOCOPY VARCHAR2
69 , x_return_msg OUT NOCOPY VARCHAR2
70 );
71
72 PROCEDURE collect_invalids_in_asl_gt(
73 x_return_status OUT NOCOPY VARCHAR2
74 , x_return_msg OUT NOCOPY VARCHAR2
75 );
76
77 PROCEDURE collect_invalids_in_aslattr_gt(
78 x_return_status OUT NOCOPY VARCHAR2
79 , x_return_msg OUT NOCOPY VARCHAR2
80 );
81
82 PROCEDURE collect_invalids_in_other_gt(
83 x_return_status OUT NOCOPY VARCHAR2
84 , x_return_msg OUT NOCOPY VARCHAR2
85 );
86
87 --------------------------------------------------------------------------------
88 --START of comments
89
90 --NAME: create_update_asl
91
92 --FUNCTION:
93 -- this will dump the data from the plsql tables into global temporary tables
94 -- with a session key generated by this procedure
95
96 --PARAMETERS:
97
98 --IN:
99 -- p_asl_rec po_approved_supplier_list_rec
100 -- p_asl_attr_rec po_asl_attributes_rec
101 -- p_asl_doc_rec po_asl_documents_rec
102 -- p_chv_auth_rec chv_authorizations_rec
103 -- p_capacity_rec po_supplier_item_capacity_rec
104 -- p_tolerance_rec po_supplier_item_tolerance_rec
105 -- p_commit VARCHAR2
106
107 --OUT:
108 -- x_session_key NUMBER
109 -- x_return_status VARCHAR2
110 -- x_return_msg VARCHAR2
111
112 --END of comments
113 --------------------------------------------------------------------------------
114 PROCEDURE create_update_asl(
115 p_asl_rec IN po_approved_supplier_list_rec
116 , p_asl_attr_rec IN po_asl_attributes_rec
117 , p_asl_doc_rec IN po_asl_documents_rec
118 , p_chv_auth_rec IN chv_authorizations_rec
119 , p_capacity_rec IN po_supplier_item_capacity_rec
120 , p_tolerance_rec IN po_supplier_item_tolerance_rec
121 , p_commit IN VARCHAR2
122 , x_session_key OUT NOCOPY NUMBER
123 , x_return_status OUT NOCOPY VARCHAR2
124 , x_return_msg OUT NOCOPY VARCHAR2
125 )
126 IS
127 l_progress NUMBER := 0;
128 invalids NUMBER;
129
130 duplicate_keys EXCEPTION;
131 parent_key_not_found EXCEPTION;
132 unexp_err_pasl EXCEPTION;
133 unexp_err_paa EXCEPTION;
134 unexp_err_pad EXCEPTION;
135 unexp_err_chv EXCEPTION;
136 unexp_err_psic EXCEPTION;
137 unexp_err_psit EXCEPTION;
138
139 BEGIN
140 x_return_msg := NULL;
141 po_asl_api_pvt.Log('START ::: PROCEDURE create_update_asl ');
142 --po_asl_api_pvt.Log('p_asl_rec count:' || p_asl_rec.user_key.Count);
143 --po_asl_api_pvt.Log('p_asl_attr_rec count:' || p_asl_attr_rec.user_key.Count);
144 --po_asl_api_pvt.Log('p_asl_doc_rec count:' || p_asl_doc_rec.user_key.Count);
145 --po_asl_api_pvt.Log('p_chv_auth_rec count:' || p_chv_auth_rec.user_key.Count);
146 --po_asl_api_pvt.Log('p_capacity_rec count:' || p_capacity_rec.user_key.Count);
147 --po_asl_api_pvt.Log('p_tolerance_rec count:'|| p_tolerance_rec.user_key.Count);
148 po_asl_api_pvt.Log(p_asl_rec);
149 po_asl_api_pvt.Log(p_asl_attr_rec);
150 po_asl_api_pvt.Log(p_asl_doc_rec);
151 po_asl_api_pvt.Log(p_chv_auth_rec);
152 po_asl_api_pvt.Log(p_capacity_rec);
153 po_asl_api_pvt.Log(p_tolerance_rec);
154 po_asl_api_pvt.Log('p_commit:' || p_commit);
155 --Empty GT tables
156 DELETE FROM po_approved_supplier_list_gt;
157 DELETE FROM po_asl_documents_gt ;
158 DELETE FROM chv_authorizations_gt;
159 DELETE FROM po_asl_attributes_gt;
160 DELETE FROM po_supplier_item_capacity_gt;
161 DELETE FROM po_supplier_item_tolerance_gt;
162
163 --generate the session key.
164 SELECT po_session_gt_s.NEXTVAL INTO x_session_key FROM dual;
165 po_asl_api_pvt.Log('session key:' || x_session_key);
166
167 g_session_key := x_session_key;
168
169 l_progress := 10;
170 --dump the data to gtt tables.
171 dump_to_asl_gtt(
172 p_asl_rec => p_asl_rec,
173 x_return_status => x_return_status,
174 x_return_msg => x_return_msg
175 );
176
177 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
178 THEN
179 RAISE unexp_err_pasl;
180 END IF;
181 -- if return status is expected error raise exception fnd_api.G_EXC_ERROR
182 IF x_return_status = FND_API.G_RET_STS_ERROR
183 THEN
184 RAISE duplicate_keys;
185 END IF;
186
187 l_progress := 20;
188 IF p_asl_attr_rec.user_key IS NOT NULL
189 THEN
190 dump_to_asl_attr_gtt(
191 p_asl_attr_rec => p_asl_attr_rec,
192 x_return_status => x_return_status,
193 x_return_msg => x_return_msg
194 );
195 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
196 THEN
197 RAISE unexp_err_paa;
198 END IF;
199 END IF;
200
201 l_progress := 30;
202 --Raise exception in case user_key not found in parent table
203 SELECT Count(*)
204 INTO invalids
205 FROM po_asl_attributes_gt
206 WHERE user_key NOT IN
207 (SELECT user_key
208 FROM po_approved_supplier_list_gt);
209
210 IF invalids <> 0
211 THEN
212 RAISE parent_key_not_found;
213 END IF;
214
215 l_progress := 40;
216 IF p_asl_doc_rec.user_key IS NOT NULL
217 THEN
218 dump_to_asl_doc_gtt(
219 p_asl_doc_rec => p_asl_doc_rec,
220 x_return_status => x_return_status,
221 x_return_msg => x_return_msg
222 );
223 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
224 THEN
225 RAISE unexp_err_pad;
226 END IF;
227 END IF;
228
229 l_progress := 45;
230 --Raise exception in case user_key not found in parent table
231 SELECT Count(*)
232 INTO invalids
233 FROM po_asl_documents_gt
234 WHERE NOT EXISTS
235 (SELECT user_key
236 FROM po_approved_supplier_list_gt);
237
238 IF invalids <> 0
239 THEN
240 RAISE parent_key_not_found;
241 END IF;
242
243 l_progress := 50;
244 IF p_chv_auth_rec.user_key IS NOT NULL
245 THEN
246 dump_to_chv_auth_gtt(
247 p_chv_auth_rec => p_chv_auth_rec,
248 x_return_status => x_return_status,
249 x_return_msg => x_return_msg
250 );
251 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
252 THEN
253 RAISE unexp_err_chv;
254 END IF;
255 END IF;
256
257 l_progress := 55;
258 --Raise exception in case user_key not found in parent table
259 SELECT Count(*)
260 INTO invalids
261 FROM chv_authorizations_gt
262 WHERE NOT EXISTS
263 (SELECT user_key
264 FROM po_approved_supplier_list_gt);
265
266 IF invalids <> 0
267 THEN
268 RAISE parent_key_not_found;
269 END IF;
270
271 l_progress := 60;
272 IF p_capacity_rec.user_key IS NOT NULL
273 THEN
274 dump_to_capacity_gtt(
275 p_capacity_rec => p_capacity_rec,
276 x_return_status => x_return_status,
277 x_return_msg => x_return_msg
278 );
279 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
280 THEN
281 RAISE unexp_err_psic;
282 END IF;
283 END IF;
284
285 l_progress := 65;
286 --Raise exception in case user_key not found in parent table
287 SELECT Count(*)
288 INTO invalids
289 FROM po_supplier_item_capacity_gt
290 WHERE NOT EXISTS
291 (SELECT user_key
292 FROM po_approved_supplier_list_gt);
293
294 IF invalids <> 0
295 THEN
296 RAISE parent_key_not_found;
297 END IF;
298
299 l_progress := 70;
300 IF p_tolerance_rec.user_key IS NOT NULL
301 THEN
302 dump_to_tolerance_gtt(
303 p_tolerance_rec => p_tolerance_rec,
304 x_return_status => x_return_status,
305 x_return_msg => x_return_msg
306 );
307 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR
308 THEN
309 RAISE unexp_err_psit;
310 END IF;
311 END IF;
312
313 l_progress := 75;
314 --Raise exception in case user_key not found in parent table
315 SELECT Count(*)
316 INTO invalids
317 FROM po_supplier_item_tolerance_gt
318 WHERE NOT EXISTS
319 (SELECT user_key
320 FROM po_approved_supplier_list_gt);
321
322 IF invalids <> 0
323 THEN
324 RAISE parent_key_not_found;
325 END IF;
326
327 l_progress := 80;
328 --call the po_asl_api_pub.process for the next steps.
329 po_asl_api_pub.process(
330 x_return_status => x_return_status,
331 x_return_msg => x_return_msg
332 );
333
334 l_progress := 90;
335 --commit the transaction if p_commit value is 'Y' and there is no error in
336 --subsequent processes.
337 IF x_return_status IS NULL
338 THEN
339 po_asl_api_pvt.log('x_return_status is null');
340 END IF;
341 IF (p_commit = 'Y' AND
342 Nvl(x_return_status,' ') NOT IN (FND_API.G_RET_STS_ERROR,
343 FND_API.G_RET_STS_UNEXP_ERROR)
344 )
345 THEN
346 COMMIT;
347 po_asl_api_pvt.log('Transaction committed');
348 END IF;
349
350 l_progress := 100;
351
352 IF x_return_status IS NULL
353 THEN
354 x_return_status := FND_API.G_RET_STS_SUCCESS;
355 END IF;
356 po_asl_api_pvt.log('END ::: PROCEDURE create_update_asl ');
357
358 EXCEPTION
359 WHEN duplicate_keys THEN
360 po_asl_api_pvt.log('create_update_asl : Existing the process
361 due to duplicate user_keys');
362 x_return_status := FND_API.G_RET_STS_ERROR;
363 x_return_msg := 'Existing the process due to duplicate user_keys';
364
365 WHEN parent_key_not_found THEN
366 po_asl_api_pvt.log('create_update_asl : Parent user_key not found');
367 x_return_status := FND_API.G_RET_STS_ERROR;
368 x_return_msg := 'Parent user_key not found';
369
370 WHEN unexp_err_pasl THEN
371 po_asl_api_pvt.log('Unexpected while dumping to po_approved_supplier_list_gt');
372 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
373 x_return_msg := 'Unexpected while dumping to po_approved_supplier_list_gt';
374
375 WHEN unexp_err_paa THEN
376 po_asl_api_pvt.log('Unexpected while dumping to po_asl_attributes_gt');
377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
378 x_return_msg := 'Unexpected while dumping to po_asl_attributes_gt';
379
380 WHEN unexp_err_pad THEN
381 po_asl_api_pvt.log('Unexpected while dumping to po_asl_documents_gt');
382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
383 x_return_msg := 'Unexpected while dumping to po_asl_documents_gt';
384
385 WHEN unexp_err_chv THEN
386 po_asl_api_pvt.log('Unexpected while dumping to chv_authorizations_gt');
387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
388 x_return_msg := 'Unexpected while dumping to chv_authorizations_gt';
389
390 WHEN unexp_err_psic THEN
391 po_asl_api_pvt.log('Unexpected while dumping to po_supplier_item_capacity_gt');
392 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
393 x_return_msg := 'Unexpected while dumping to po_supplier_item_capacity_gt';
394
395 WHEN unexp_err_psit THEN
396 po_asl_api_pvt.log('Unexpected while dumping to po_supplier_item_tolerance_gt');
397 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
398 x_return_msg := 'Unexpected while dumping to po_supplier_item_tolerance_gt';
399
400 WHEN OTHERS THEN
401 po_asl_api_pvt.log('create_update_asl : when others exception at '
402 || l_progress || ';' || SQLERRM );
403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
404 x_return_msg := SQLERRM;
405
406 END create_update_asl;
407
408
409 --------------------------------------------------------------------------------
410 --START of comments
411
412 --NAME: process
413
414 --FUNCTION:
415 -- This will first derive the id fields based on the display values provided.
416 -- next it will try to default any NULL fields which are defaultable.
417
418 --PARAMETERS:
419
420 --OUT:
421 -- x_return_status VARCHAR2
422 -- x_return_msg VARCHAR2
423
424 --END of comments
425 --------------------------------------------------------------------------------
426
427 PROCEDURE process(
428 x_return_status OUT NOCOPY VARCHAR2
429 , x_return_msg OUT NOCOPY VARCHAR2
430 )
431 IS
432 l_progress NUMBER := 0;
433
434 BEGIN
435 po_asl_api_pvt.log('START ::: po_asl_api_pub.process');
436 x_return_msg := NULL;
437
438 --derive the id fields based on the display values provided
439 --updating po_approved_supplier_list_gt to derive id fields
440 derive_ids_past(
441 x_return_status => x_return_status,
442 x_return_msg => x_return_msg
443 );
444 l_progress := 10;
445
446 --updating po_asl_documents_gt to derive id fields
447 derive_ids_podoc(
448 x_return_status => x_return_status,
449 x_return_msg => x_return_msg
450 );
451 l_progress := 20;
452
453 --updating chv_authorizations_gt to derive id fields
454 derive_ids_ch_auth(
455 x_return_status => x_return_status,
456 x_return_msg => x_return_msg
457 );
458 l_progress := 25;
459
460 --updating po_asl_attributes_gt to derive id fields
461 derive_ids_asl_attr(
462 x_return_status => x_return_status,
463 x_return_msg => x_return_msg
464 );
465 l_progress := 35;
466
467 --updating po_supplier_item_capacity_gt to derive id fields
468 derive_ids_sup_cap(
469 x_return_status => x_return_status,
470 x_return_msg => x_return_msg
471 );
472 l_progress := 40;
473
474 --updating po_supplier_item_tolerance_gt to derive id fields
475 derive_ids_sup_tol(
476 x_return_status => x_return_status,
477 x_return_msg => x_return_msg
478 );
479 l_progress := 45;
480
481 --default any NULL fields which are defaultable
482 --***********************TBD****************************
483
484 collect_invalids_in_asl_gt(
485 x_return_status => x_return_status,
486 x_return_msg => x_return_msg
487 );
488
489 l_progress := 55;
490
491 collect_invalids_in_aslattr_gt(
492 x_return_status => x_return_status,
493 x_return_msg => x_return_msg
494 );
495
496 l_progress := 65;
497
498 collect_invalids_in_other_gt(
499 x_return_status => x_return_status,
500 x_return_msg => x_return_msg
501 );
502
503 l_progress := 70;
504
505 --call the po_asl_api_grp.process for the next steps.
506 po_asl_api_grp.process(
507 p_session_key => g_session_key,
508 x_return_status => x_return_status,
509 x_return_msg => x_return_msg
510 );
511
512 l_progress := 100;
513 po_asl_api_pvt.log('END ::: po_asl_api_pub.process ');
514
515 EXCEPTION
516
517 WHEN OTHERS THEN
518
519 po_asl_api_pvt.log('po_asl_api_pub.process : when others exception at '
520 || l_progress || ';' || SQLERRM );
521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
522 x_return_msg := SQLERRM;
523 END process;
524
525
526 --------------------------------------------------------------------------------
527 --START of comments
528
529 --NAME: dump_to_asl_gtt
530
531 --FUNCTION:
532 -- this will dump all the data from p_asl_rec to po_approved_supplier_list_gt
533
534 --PARAMETERS:
535
536 --IN:
537 -- p_asl_rec po_approved_supplier_list_rec
538
539 --OUT:
540 -- x_return_status VARCHAR2
541 -- x_return_msg VARCHAR2
542
543 --END of comments
544 --------------------------------------------------------------------------------
545
546 PROCEDURE dump_to_asl_gtt(
547 p_asl_rec IN po_approved_supplier_list_rec
548 , x_return_status OUT NOCOPY VARCHAR2
549 , x_return_msg OUT NOCOPY VARCHAR2
550 )
551 IS
552 counter NUMBER;
553 BEGIN
554 po_asl_api_pvt.log('START ::: dump_to_asl_gtt');
555 po_asl_api_pvt.log(p_asl_rec);
556 x_return_msg := NULL;
557
558 FORALL l_index IN 1 .. p_asl_rec.user_key.Count
559 INSERT INTO po_approved_supplier_list_gt (
560 session_key ,
561 user_key ,
562 process_action ,
563 process_status ,
564 asl_id ,
565 using_organization_id ,
566 using_organization_dsp ,
567 owning_organization_id ,
568 owning_organization_dsp ,
569 vendor_business_type ,
570 asl_status_id ,
571 asl_status_dsp ,
572 manufacturer_id ,
573 manufacturer_dsp ,
574 vendor_id ,
575 vendor_dsp ,
576 item_id ,
577 item_dsp ,
578 category_id ,
579 category_dsp ,
580 vendor_site_id ,
581 vendor_site_dsp ,
582 primary_vendor_item ,
583 manufacturer_asl_id ,
584 manufacturer_asl_dsp ,
585 review_by_date ,
586 comments ,
587 attribute_category ,
588 attribute1 ,
589 attribute2 ,
590 attribute3 ,
591 attribute4 ,
592 attribute5 ,
593 attribute6 ,
594 attribute7 ,
595 attribute8 ,
596 attribute9 ,
597 attribute10 ,
598 attribute11 ,
599 attribute12 ,
600 attribute13 ,
601 attribute14 ,
602 attribute15 ,
603 request_id ,
604 program_application_id ,
605 program_id ,
606 program_update_date ,
607 disable_flag
608 )
609 VALUES (
610 g_session_key ,
611 p_asl_rec.user_key(l_index) ,
612 p_asl_rec.process_action(l_index) ,
613 PO_ASL_API_PVT.g_STATUS_PENDING ,
614 NULL ,
615 Decode(p_asl_rec.global_flag(l_index), 'Y', -1) ,
616 NULL ,
617 p_asl_rec.owning_organization_id(l_index) ,
618 p_asl_rec.owning_organization_dsp(l_index) ,
619 p_asl_rec.vendor_business_type(l_index) ,
620 p_asl_rec.asl_status_id(l_index) ,
621 p_asl_rec.asl_status_dsp(l_index) ,
622 p_asl_rec.manufacturer_id(l_index) ,
623 p_asl_rec.manufacturer_dsp(l_index) ,
624 p_asl_rec.vendor_id(l_index) ,
625 p_asl_rec.vendor_dsp(l_index) ,
626 p_asl_rec.item_id(l_index) ,
627 p_asl_rec.item_dsp(l_index) ,
628 p_asl_rec.category_id(l_index) ,
629 p_asl_rec.category_dsp(l_index) ,
630 p_asl_rec.vendor_site_id(l_index) ,
631 p_asl_rec.vendor_site_dsp(l_index) ,
632 p_asl_rec.primary_vendor_item(l_index) ,
633 p_asl_rec.manufacturer_asl_id(l_index) ,
634 p_asl_rec.manufacturer_asl_dsp(l_index) ,
635 p_asl_rec.review_by_date(l_index) ,
636 p_asl_rec.comments(l_index) ,
637 p_asl_rec.attribute_category(l_index) ,
638 p_asl_rec.attribute1(l_index) ,
639 p_asl_rec.attribute2(l_index) ,
640 p_asl_rec.attribute3(l_index) ,
641 p_asl_rec.attribute4(l_index) ,
642 p_asl_rec.attribute5(l_index) ,
643 p_asl_rec.attribute6(l_index) ,
644 p_asl_rec.attribute7(l_index) ,
645 p_asl_rec.attribute8(l_index) ,
646 p_asl_rec.attribute9(l_index) ,
647 p_asl_rec.attribute10(l_index) ,
648 p_asl_rec.attribute11(l_index) ,
649 p_asl_rec.attribute12(l_index) ,
650 p_asl_rec.attribute13(l_index) ,
651 p_asl_rec.attribute14(l_index) ,
652 p_asl_rec.attribute15(l_index) ,
653 p_asl_rec.request_id(l_index) ,
654 p_asl_rec.program_application_id(l_index) ,
655 p_asl_rec.program_id(l_index) ,
656 p_asl_rec.program_update_date(l_index) ,
657 p_asl_rec.disable_flag(l_index)
658 );
659
660 po_asl_api_pvt.log('dump_to_asl_gtt : rowcount :' || SQL%ROWCOUNT);
661 --To check if there are any duplicate user_keys in gt table
662 --If there are duplicates the following query returns the couner.
663 --Ideal case is, the query should throw No Data Found exception.
664 --Otherwise, it is expected error.
665 SELECT Count(*)
666 INTO counter
667 FROM (SELECT Count(*)
668 FROM po_approved_supplier_list_gt
669 GROUP BY user_key
670 HAVING Count(*) > 1);
671
672 IF counter > 0
673 THEN
674 x_return_status := FND_API.G_RET_STS_ERROR;
675 ELSE
676 x_return_status := FND_API.G_RET_STS_SUCCESS;
677 END IF;
678
679 po_asl_api_pvt.log('END ::: dump_to_asl_gtt');
680 EXCEPTION
681 WHEN OTHERS THEN
682
683 po_asl_api_pvt.log('dump_to_asl_gtt : when others exception ' || SQLERRM );
684 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
685 x_return_msg := SQLERRM;
686
687 END dump_to_asl_gtt;
688
689 --------------------------------------------------------------------------------
690 --START of comments
691
692 --NAME: dump_to_asl_attr_gtt
693
694 --FUNCTION:
695 -- this will dump all the data from p_asl_attr_rec to po_asl_attributes_gt
696
697 --parameters:
698
699 --IN:
700 -- p_asl_attr_rec po_asl_attributes_rec
701
702 --OUT:
703 -- x_return_status VARCHAR2
704 -- x_return_msg VARCHAR2
705
706 --END of comments
707 --------------------------------------------------------------------------------
708
709 PROCEDURE dump_to_asl_attr_gtt(
710 p_asl_attr_rec IN po_asl_attributes_rec
711 , x_return_status OUT NOCOPY VARCHAR2
712 , x_return_msg OUT NOCOPY VARCHAR2
713 )
714 IS
715
716 BEGIN
717 po_asl_api_pvt.log('START ::: dump_to_asl_attr_gtt');
718 po_asl_api_pvt.log(p_asl_attr_rec);
719 x_return_msg := NULL;
720
721 FORALL l_index IN 1 .. p_asl_attr_rec.user_key.Count
722 INSERT INTO po_asl_attributes_gt (
723 session_key ,
724 user_key ,
725 process_action ,
726 asl_id ,
727 using_organization_id ,
728 using_organization_dsp ,
729 document_sourcing_method ,
730 release_generation_method ,
731 release_generation_method_dsp ,
732 purchasing_unit_of_measure_dsp ,
733 enable_plan_schedule_flag_dsp ,
734 enable_ship_schedule_flag_dsp ,
735 plan_schedule_type ,
736 plan_schedule_type_dsp ,
737 ship_schedule_type ,
738 ship_schedule_type_dsp ,
739 plan_bucket_pattern_id ,
740 plan_bucket_pattern_dsp ,
741 ship_bucket_pattern_id ,
742 ship_bucket_pattern_dsp ,
743 enable_autoschedule_flag_dsp ,
744 scheduler_id ,
745 scheduler_dsp ,
746 enable_authorizations_flag_dsp ,
747 vendor_id ,
748 vendor_dsp ,
749 vendor_site_id ,
750 vendor_site_dsp ,
751 item_id ,
752 item_dsp ,
753 category_id ,
754 category_dsp ,
755 attribute_category ,
756 attribute1 ,
757 attribute2 ,
758 attribute3 ,
759 attribute4 ,
760 attribute5 ,
761 attribute6 ,
762 attribute7 ,
763 attribute8 ,
764 attribute9 ,
765 attribute10 ,
766 attribute11 ,
767 attribute12 ,
768 attribute13 ,
769 attribute14 ,
770 attribute15 ,
771 request_id ,
772 program_application_id ,
773 program_id ,
774 program_update_date ,
775 price_update_tolerance_dsp ,
776 processing_lead_time_dsp ,
777 min_order_qty_dsp ,
778 fixed_lot_multiple_dsp ,
779 delivery_calendar_dsp ,
780 country_of_origin_code_dsp ,
781 enable_vmi_flag_dsp ,
782 vmi_min_qty_dsp ,
783 vmi_max_qty_dsp ,
784 enable_vmi_auto_replenish_flag ,
785 vmi_replenishment_approval ,
786 vmi_replenishment_approval_dsp ,
787 consigned_from_supp_flag_dsp ,
788 last_billing_date ,
789 consigned_billing_cycle_dsp ,
790 consume_on_aging_flag_dsp ,
791 aging_period_dsp ,
792 replenishment_method ,
793 replenishment_method_dsp ,
794 vmi_min_days_dsp ,
795 vmi_max_days_dsp ,
796 fixed_order_quantity_dsp ,
797 forecast_horizon_dsp
798 )
799 VALUES (
800 g_session_key ,
801 p_asl_attr_rec.user_key(l_index) ,
802 p_asl_attr_rec.process_action(l_index) ,
803 NULL ,
804 p_asl_attr_rec.using_organization_id(l_index) ,
805 p_asl_attr_rec.using_organization_dsp(l_index) ,
806 'ASL' ,
807 p_asl_attr_rec.release_generation_method(l_index) ,
808 p_asl_attr_rec.release_generation_method_dsp(l_index) ,
809 p_asl_attr_rec.purchasing_unit_of_measure_dsp(l_index) ,
810 p_asl_attr_rec.enable_plan_schedule_flag_dsp(l_index) ,
811 p_asl_attr_rec.enable_ship_schedule_flag_dsp(l_index) ,
812 p_asl_attr_rec.plan_schedule_type(l_index) ,
813 p_asl_attr_rec.plan_schedule_type_dsp(l_index) ,
814 p_asl_attr_rec.ship_schedule_type(l_index) ,
815 p_asl_attr_rec.ship_schedule_type_dsp(l_index) ,
816 p_asl_attr_rec.plan_bucket_pattern_id(l_index) ,
817 p_asl_attr_rec.plan_bucket_pattern_dsp(l_index) ,
818 p_asl_attr_rec.ship_bucket_pattern_id(l_index) ,
819 p_asl_attr_rec.ship_bucket_pattern_dsp(l_index) ,
820 p_asl_attr_rec.enable_autoschedule_flag_dsp(l_index) ,
821 p_asl_attr_rec.scheduler_id(l_index) ,
822 p_asl_attr_rec.scheduler_dsp(l_index) ,
823 p_asl_attr_rec.enable_authorizations_flag_dsp(l_index) ,
824 p_asl_attr_rec.vendor_id(l_index) ,
825 p_asl_attr_rec.vendor_dsp(l_index) ,
826 p_asl_attr_rec.vendor_site_id(l_index) ,
827 p_asl_attr_rec.vendor_site_dsp(l_index) ,
828 p_asl_attr_rec.item_id(l_index) ,
829 p_asl_attr_rec.item_dsp(l_index) ,
830 p_asl_attr_rec.category_id(l_index) ,
831 p_asl_attr_rec.category_dsp(l_index) ,
832 p_asl_attr_rec.attribute_category(l_index) ,
833 p_asl_attr_rec.attribute1(l_index) ,
834 p_asl_attr_rec.attribute2(l_index) ,
835 p_asl_attr_rec.attribute3(l_index) ,
836 p_asl_attr_rec.attribute4(l_index) ,
837 p_asl_attr_rec.attribute5(l_index) ,
838 p_asl_attr_rec.attribute6(l_index) ,
839 p_asl_attr_rec.attribute7(l_index) ,
840 p_asl_attr_rec.attribute8(l_index) ,
841 p_asl_attr_rec.attribute9(l_index) ,
842 p_asl_attr_rec.attribute10(l_index) ,
843 p_asl_attr_rec.attribute11(l_index) ,
844 p_asl_attr_rec.attribute12(l_index) ,
845 p_asl_attr_rec.attribute13(l_index) ,
846 p_asl_attr_rec.attribute14(l_index) ,
847 p_asl_attr_rec.attribute15(l_index) ,
848 p_asl_attr_rec.request_id(l_index) ,
849 p_asl_attr_rec.program_application_id(l_index) ,
850 p_asl_attr_rec.program_id(l_index) ,
851 p_asl_attr_rec.program_update_date(l_index) ,
852 p_asl_attr_rec.price_update_tolerance_dsp(l_index) ,
853 p_asl_attr_rec.processing_lead_time_dsp(l_index) ,
854 p_asl_attr_rec.min_order_qty_dsp(l_index) ,
855 p_asl_attr_rec.fixed_lot_multiple_dsp(l_index) ,
856 p_asl_attr_rec.delivery_calendar_dsp(l_index) ,
857 p_asl_attr_rec.country_of_origin_code_dsp(l_index) ,
858 p_asl_attr_rec.enable_vmi_flag_dsp(l_index) ,
859 p_asl_attr_rec.vmi_min_qty_dsp(l_index) ,
860 p_asl_attr_rec.vmi_max_qty_dsp(l_index) ,
861 p_asl_attr_rec.enable_vmi_auto_replenish_flag(l_index) ,
862 p_asl_attr_rec.vmi_replenishment_approval(l_index) ,
863 p_asl_attr_rec.vmi_replenishment_approval_dsp(l_index) ,
864 p_asl_attr_rec.consigned_from_supp_flag_dsp(l_index) ,
865 p_asl_attr_rec.last_billing_date(l_index) ,
866 p_asl_attr_rec.consigned_billing_cycle_dsp(l_index) ,
867 p_asl_attr_rec.consume_on_aging_flag_dsp(l_index) ,
868 p_asl_attr_rec.aging_period_dsp(l_index) ,
869 p_asl_attr_rec.replenishment_method(l_index) ,
870 p_asl_attr_rec.replenishment_method_dsp(l_index) ,
871 p_asl_attr_rec.vmi_min_days_dsp(l_index) ,
872 p_asl_attr_rec.vmi_max_days_dsp(l_index) ,
873 p_asl_attr_rec.fixed_order_quantity_dsp(l_index) ,
874 p_asl_attr_rec.forecast_horizon_dsp(l_index)
875 );
876
877 po_asl_api_pvt.log('dump_to_asl_attr_gtt : rowcount :' || SQL%ROWCOUNT);
878 x_return_status := FND_API.G_RET_STS_SUCCESS;
879
880 po_asl_api_pvt.log('END ::: dump_to_asl_attr_gtt');
881 EXCEPTION
882
883 WHEN OTHERS THEN
884
885 po_asl_api_pvt.log('dump_to_asl_attr_gtt-when others exception ' || SQLERRM);
886 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
887 x_return_msg := SQLERRM;
888
889 END dump_to_asl_attr_gtt;
890
891 --------------------------------------------------------------------------------
892 --START of comments
893
894 --NAME: dump_to_asl_doc_gtt
895
896 --FUNCTION:
897 -- this will dump all the data from p_asl_doc_rec to po_asl_documents_gt
898
899 --parameters:
900
901 --IN:
902 -- p_asl_doc_rec po_asl_documents_rec
903
904 --OUT:
905 -- x_return_status VARCHAR2
906 -- x_return_msg VARCHAR2
907
908 --END of comments
909 --------------------------------------------------------------------------------
910
911 PROCEDURE dump_to_asl_doc_gtt(
912 p_asl_doc_rec IN po_asl_documents_rec
913 , x_return_status OUT NOCOPY VARCHAR2
914 , x_return_msg OUT NOCOPY VARCHAR2
915 )
916 IS
917
918 BEGIN
919 po_asl_api_pvt.log('START ::: dump_to_asl_doc_gtt');
920 po_asl_api_pvt.log(p_asl_doc_rec);
921 x_return_msg := NULL;
922
923 FORALL l_index IN 1 .. p_asl_doc_rec.user_key.Count
924 INSERT INTO po_asl_documents_gt (
925 session_key ,
926 user_key ,
927 process_action ,
928 asl_id ,
929 using_organization_id ,
930 using_organization_dsp ,
931 sequence_num ,
932 document_type_code ,
933 document_type_dsp ,
934 document_header_id ,
935 document_header_dsp ,
936 document_line_id ,
937 document_line_num_dsp ,
938 attribute_category ,
939 attribute1 ,
940 attribute2 ,
941 attribute3 ,
942 attribute4 ,
943 attribute5 ,
944 attribute6 ,
945 attribute7 ,
946 attribute8 ,
947 attribute9 ,
948 attribute10 ,
949 attribute11 ,
950 attribute12 ,
951 attribute13 ,
952 attribute14 ,
953 attribute15 ,
954 request_id ,
955 program_application_id ,
956 program_id ,
957 program_update_date ,
958 org_id
959 )
960 VALUES (
961 g_session_key ,
962 p_asl_doc_rec.user_key(l_index) ,
963 p_asl_doc_rec.process_action(l_index) ,
964 NULL ,
965 p_asl_doc_rec.using_organization_id(l_index) ,
966 p_asl_doc_rec.using_organization_dsp(l_index) ,
967 p_asl_doc_rec.sequence_num(l_index) ,
968 p_asl_doc_rec.document_type_code(l_index) ,
969 p_asl_doc_rec.document_type_dsp(l_index) ,
970 p_asl_doc_rec.document_header_id(l_index) ,
971 p_asl_doc_rec.document_header_dsp(l_index) ,
972 p_asl_doc_rec.document_line_id(l_index) ,
973 p_asl_doc_rec.document_line_num_dsp(l_index) ,
974 p_asl_doc_rec.attribute_category(l_index) ,
975 p_asl_doc_rec.attribute1(l_index) ,
976 p_asl_doc_rec.attribute2(l_index) ,
977 p_asl_doc_rec.attribute3(l_index) ,
978 p_asl_doc_rec.attribute4(l_index) ,
979 p_asl_doc_rec.attribute5(l_index) ,
980 p_asl_doc_rec.attribute6(l_index) ,
981 p_asl_doc_rec.attribute7(l_index) ,
982 p_asl_doc_rec.attribute8(l_index) ,
983 p_asl_doc_rec.attribute9(l_index) ,
984 p_asl_doc_rec.attribute10(l_index) ,
985 p_asl_doc_rec.attribute11(l_index) ,
986 p_asl_doc_rec.attribute12(l_index) ,
987 p_asl_doc_rec.attribute13(l_index) ,
988 p_asl_doc_rec.attribute14(l_index) ,
989 p_asl_doc_rec.attribute15(l_index) ,
990 p_asl_doc_rec.request_id(l_index) ,
991 p_asl_doc_rec.program_application_id(l_index) ,
992 p_asl_doc_rec.program_id(l_index) ,
993 p_asl_doc_rec.program_update_date(l_index) ,
994 p_asl_doc_rec.org_id(l_index)
995 );
996
997 po_asl_api_pvt.log('dump_to_asl_doc_gtt : rowcount :' || SQL%ROWCOUNT);
998 x_return_status := FND_API.G_RET_STS_SUCCESS;
999
1000 po_asl_api_pvt.log('END ::: dump_to_asl_doc_gtt');
1001 EXCEPTION
1002
1003 WHEN OTHERS THEN
1004
1005 po_asl_api_pvt.log('dump_to_asl_doc_gtt: when others exception ' || SQLERRM);
1006 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1007 x_return_msg := SQLERRM;
1008
1009 END dump_to_asl_doc_gtt;
1010
1011 --------------------------------------------------------------------------------
1012 --START of comments
1013
1014 --NAME: dump_to_chv_auth_gtt
1015
1016 --FUNCTION:
1017 -- this will dump all the data from p_chv_auth_rec to chv_authorizations_gt
1018
1019 --parameters:
1020
1021 --IN:
1022 -- p_chv_auth_rec chv_authorizations_rec
1023
1024 --OUT:
1025 -- x_return_status VARCHAR2
1026 -- x_return_msg VARCHAR2
1027
1028 --END of comments
1029 --------------------------------------------------------------------------------
1030
1031 PROCEDURE dump_to_chv_auth_gtt(
1032 p_chv_auth_rec IN chv_authorizations_rec
1033 , x_return_status OUT NOCOPY VARCHAR2
1034 , x_return_msg OUT NOCOPY VARCHAR2
1035 )
1036 IS
1037
1038 BEGIN
1039 po_asl_api_pvt.log('START ::: dump_to_chv_auth_gtt');
1040 po_asl_api_pvt.log(p_chv_auth_rec);
1041 x_return_msg := NULL;
1042
1043 FORALL l_index IN 1 .. p_chv_auth_rec.user_key.Count
1044 INSERT INTO chv_authorizations_gt (
1045 session_key ,
1046 user_key ,
1047 process_action ,
1048 reference_id ,
1049 reference_type ,
1050 authorization_code ,
1051 authorization_code_dsp ,
1052 authorization_sequence_dsp ,
1053 timefence_days_dsp ,
1054 attribute_category ,
1055 attribute1 ,
1056 attribute2 ,
1057 attribute3 ,
1058 attribute4 ,
1059 attribute5 ,
1060 attribute6 ,
1061 attribute7 ,
1062 attribute8 ,
1063 attribute9 ,
1064 attribute10 ,
1065 attribute11 ,
1066 attribute12 ,
1067 attribute13 ,
1068 attribute14 ,
1069 attribute15 ,
1070 request_id ,
1071 program_application_id ,
1072 program_id ,
1073 program_update_date ,
1074 using_organization_id ,
1075 using_organization_dsp
1076 )
1077 VALUES (
1078 g_session_key ,
1079 p_chv_auth_rec.user_key(l_index) ,
1080 p_chv_auth_rec.process_action(l_index) ,
1081 NULL ,
1082 'ASL' ,
1083 p_chv_auth_rec.authorization_code(l_index) ,
1084 p_chv_auth_rec.authorization_code_dsp(l_index) ,
1085 p_chv_auth_rec.authorization_sequence_dsp(l_index) ,
1086 p_chv_auth_rec.timefence_days_dsp(l_index) ,
1087 p_chv_auth_rec.attribute_category(l_index) ,
1088 p_chv_auth_rec.attribute1(l_index) ,
1089 p_chv_auth_rec.attribute2(l_index) ,
1090 p_chv_auth_rec.attribute3(l_index) ,
1091 p_chv_auth_rec.attribute4(l_index) ,
1092 p_chv_auth_rec.attribute5(l_index) ,
1093 p_chv_auth_rec.attribute6(l_index) ,
1094 p_chv_auth_rec.attribute7(l_index) ,
1095 p_chv_auth_rec.attribute8(l_index) ,
1096 p_chv_auth_rec.attribute9(l_index) ,
1097 p_chv_auth_rec.attribute10(l_index) ,
1098 p_chv_auth_rec.attribute11(l_index) ,
1099 p_chv_auth_rec.attribute12(l_index) ,
1100 p_chv_auth_rec.attribute13(l_index) ,
1101 p_chv_auth_rec.attribute14(l_index) ,
1102 p_chv_auth_rec.attribute15(l_index) ,
1103 p_chv_auth_rec.request_id(l_index) ,
1104 p_chv_auth_rec.program_application_id(l_index) ,
1105 p_chv_auth_rec.program_id(l_index) ,
1106 p_chv_auth_rec.program_update_date(l_index) ,
1107 p_chv_auth_rec.using_organization_id(l_index) ,
1108 p_chv_auth_rec.using_organization_dsp(l_index)
1109 );
1110
1111 po_asl_api_pvt.log('dump_to_chv_auth_gtt : rowcount :' || SQL%ROWCOUNT);
1112 x_return_status := FND_API.G_RET_STS_SUCCESS;
1113
1114 po_asl_api_pvt.log('END ::: dump_to_chv_auth_gtt');
1115 EXCEPTION
1116
1117 WHEN OTHERS THEN
1118
1119 po_asl_api_pvt.log('dump_to_chv_auth_gtt-when others exception ' || SQLERRM);
1120 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1121 x_return_msg := SQLERRM;
1122
1123 END dump_to_chv_auth_gtt;
1124
1125 --------------------------------------------------------------------------------
1126 --START of comments
1127
1128 --NAME: dump_to_capacity_gtt
1129
1130 --FUNCTION:
1131 -- this will dump all the data from p_capacity_rec to
1132 -- po_supplier_item_capacity_gt
1133
1134 --parameters:
1135
1136 --IN:
1137 -- p_capacity_rec po_supplier_item_capacity_rec
1138
1139 --OUT:
1140 -- x_return_status VARCHAR2
1141 -- x_return_msg VARCHAR2
1142
1143 --END of comments
1144 --------------------------------------------------------------------------------
1145
1146 PROCEDURE dump_to_capacity_gtt(
1147 p_capacity_rec IN po_supplier_item_capacity_rec
1148 , x_return_status OUT NOCOPY VARCHAR2
1149 , x_return_msg OUT NOCOPY VARCHAR2
1150 )
1151 IS
1152
1153 BEGIN
1154 po_asl_api_pvt.log('START ::: dump_to_capacity_gtt');
1155 po_asl_api_pvt.log(p_capacity_rec);
1156 x_return_msg := NULL;
1157
1158 FORALL l_index IN 1 .. p_capacity_rec.user_key.Count
1159 INSERT INTO po_supplier_item_capacity_gt (
1160 session_key ,
1161 user_key ,
1162 process_action ,
1163 capacity_id ,
1164 asl_id ,
1165 using_organization_id ,
1166 using_organization_dsp ,
1167 from_date_dsp ,
1168 to_date_dsp ,
1169 capacity_per_day_dsp ,
1170 attribute_category ,
1171 attribute1 ,
1172 attribute2 ,
1173 attribute3 ,
1174 attribute4 ,
1175 attribute5 ,
1176 attribute6 ,
1177 attribute7 ,
1178 attribute8 ,
1179 attribute9 ,
1180 attribute10 ,
1181 attribute11 ,
1182 attribute12 ,
1183 attribute13 ,
1184 attribute14 ,
1185 attribute15 ,
1186 request_id ,
1187 program_application_id ,
1188 program_id ,
1189 program_update_date
1190 )
1191 VALUES (
1192 g_session_key ,
1193 p_capacity_rec.user_key(l_index) ,
1194 p_capacity_rec.process_action(l_index) ,
1195 Decode(p_capacity_rec.process_action(l_index),
1196 po_asl_api_pub.g_action_add,
1197 po_supplier_item_capacity_s.NEXTVAL, NULL),
1198 NULL ,
1199 p_capacity_rec.using_organization_id(l_index) ,
1200 p_capacity_rec.using_organization_dsp(l_index) ,
1201 p_capacity_rec.from_date_dsp(l_index) ,
1202 p_capacity_rec.to_date_dsp(l_index) ,
1203 p_capacity_rec.capacity_per_day_dsp(l_index) ,
1204 p_capacity_rec.attribute_category(l_index) ,
1205 p_capacity_rec.attribute1(l_index) ,
1206 p_capacity_rec.attribute2(l_index) ,
1207 p_capacity_rec.attribute3(l_index) ,
1208 p_capacity_rec.attribute4(l_index) ,
1209 p_capacity_rec.attribute5(l_index) ,
1210 p_capacity_rec.attribute6(l_index) ,
1211 p_capacity_rec.attribute7(l_index) ,
1212 p_capacity_rec.attribute8(l_index) ,
1213 p_capacity_rec.attribute9(l_index) ,
1214 p_capacity_rec.attribute10(l_index) ,
1215 p_capacity_rec.attribute11(l_index) ,
1216 p_capacity_rec.attribute12(l_index) ,
1217 p_capacity_rec.attribute13(l_index) ,
1218 p_capacity_rec.attribute14(l_index) ,
1219 p_capacity_rec.attribute15(l_index) ,
1220 p_capacity_rec.request_id(l_index) ,
1221 p_capacity_rec.program_application_id(l_index) ,
1222 p_capacity_rec.program_id(l_index) ,
1223 p_capacity_rec.program_update_date(l_index)
1224 );
1225
1226 po_asl_api_pvt.log('dump_to_capacity_gtt : rowcount :' || SQL%ROWCOUNT);
1227 x_return_status := FND_API.G_RET_STS_SUCCESS;
1228
1229 po_asl_api_pvt.log('END ::: dump_to_capacity_gtt');
1230 EXCEPTION
1231
1232 WHEN OTHERS THEN
1233
1234 po_asl_api_pvt.log('dump_to_capacity_gtt-when others exception ' || SQLERRM);
1235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1236 x_return_msg := SQLERRM;
1237
1238 END dump_to_capacity_gtt;
1239
1240 --------------------------------------------------------------------------------
1241 --START of comments
1242
1243 --NAME: dump_to_tolerance_gtt
1244
1245 --FUNCTION:
1246 -- this will dump all the data FROM p_capacity_rec to
1247 -- po_supplier_item_tolerance_gt
1248
1249 --parameters:
1250
1251 --IN:
1252 -- p_tolerance_rec po_supplier_item_tolerance_rec
1253
1254 --OUT:
1255 -- x_return_status VARCHAR2
1256 -- x_return_msg VARCHAR2
1257
1258 --END of comments
1259 --------------------------------------------------------------------------------
1260
1261 PROCEDURE dump_to_tolerance_gtt(
1262 p_tolerance_rec IN po_supplier_item_tolerance_rec
1263 , x_return_status OUT NOCOPY VARCHAR2
1264 , x_return_msg OUT NOCOPY VARCHAR2
1265 )
1266 IS
1267
1268 BEGIN
1269 po_asl_api_pvt.log('START ::: dump_to_tolerance_gtt');
1270 po_asl_api_pvt.log(p_tolerance_rec);
1271 x_return_msg := NULL;
1272
1273 FORALL l_index IN 1 .. p_tolerance_rec.user_key.Count
1274 INSERT INTO po_supplier_item_tolerance_gt (
1275 session_key ,
1276 user_key ,
1277 process_action ,
1278 asl_id ,
1279 using_organization_id ,
1280 using_organization_dsp ,
1281 number_of_days_dsp ,
1282 tolerance_dsp ,
1283 attribute_category ,
1284 attribute1 ,
1285 attribute2 ,
1286 attribute3 ,
1287 attribute4 ,
1288 attribute5 ,
1289 attribute6 ,
1290 attribute7 ,
1291 attribute8 ,
1292 attribute9 ,
1293 attribute10 ,
1294 attribute11 ,
1295 attribute12 ,
1296 attribute13 ,
1297 attribute14 ,
1298 attribute15 ,
1299 request_id ,
1300 program_application_id ,
1301 program_id ,
1302 program_update_date
1303 )
1304 VALUES (
1305 g_session_key ,
1306 p_tolerance_rec.user_key(l_index) ,
1307 p_tolerance_rec.process_action(l_index) ,
1308 NULL ,
1309 p_tolerance_rec.using_organization_id(l_index) ,
1310 p_tolerance_rec.using_organization_dsp(l_index) ,
1311 p_tolerance_rec.number_of_days_dsp(l_index) ,
1312 p_tolerance_rec.tolerance_dsp(l_index) ,
1313 p_tolerance_rec.attribute_category(l_index) ,
1314 p_tolerance_rec.attribute1(l_index) ,
1315 p_tolerance_rec.attribute2(l_index) ,
1316 p_tolerance_rec.attribute3(l_index) ,
1317 p_tolerance_rec.attribute4(l_index) ,
1318 p_tolerance_rec.attribute5(l_index) ,
1319 p_tolerance_rec.attribute6(l_index) ,
1320 p_tolerance_rec.attribute7(l_index) ,
1321 p_tolerance_rec.attribute8(l_index) ,
1322 p_tolerance_rec.attribute9(l_index) ,
1323 p_tolerance_rec.attribute10(l_index) ,
1324 p_tolerance_rec.attribute11(l_index) ,
1325 p_tolerance_rec.attribute12(l_index) ,
1326 p_tolerance_rec.attribute13(l_index) ,
1327 p_tolerance_rec.attribute14(l_index) ,
1328 p_tolerance_rec.attribute15(l_index) ,
1329 p_tolerance_rec.request_id(l_index) ,
1330 p_tolerance_rec.program_application_id(l_index) ,
1331 p_tolerance_rec.program_id(l_index) ,
1332 p_tolerance_rec.program_update_date(l_index)
1333 );
1334
1335 po_asl_api_pvt.log('dump_to_tolerance_gtt : rowcount :' || SQL%ROWCOUNT);
1336 x_return_status := FND_API.G_RET_STS_SUCCESS;
1337
1338 po_asl_api_pvt.log('END ::: dump_to_tolerance_gtt');
1339 EXCEPTION
1340
1341 WHEN OTHERS THEN
1342
1343 po_asl_api_pvt.log('dump_to_tolerance_gtt-when others exception ' || SQLERRM);
1344 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1345 x_return_msg := SQLERRM;
1346
1347 END dump_to_tolerance_gtt;
1348
1349 --------------------------------------------------------------------------------
1350 --START of comments
1351
1352 --NAME: derive_ids_past
1353
1354 --FUNCTION:
1355 -- this will derive ids for the display fields in
1356 -- po_approved_supplier_list_gt, which ever applicabale
1357
1358 --parameters:
1359
1360 --OUT:
1361 -- x_return_status VARCHAR2
1362 -- x_return_msg VARCHAR2
1363
1364 --END of comments
1365 --------------------------------------------------------------------------------
1366
1367 PROCEDURE derive_ids_past(
1368 x_return_status OUT NOCOPY VARCHAR2
1369 , x_return_msg OUT NOCOPY VARCHAR2
1370 )
1371 IS
1372 l_progress NUMBER := 0;
1373
1374 BEGIN
1375 po_asl_api_pvt.log('START ::: derive_ids_past');
1376
1377 UPDATE po_approved_supplier_list_gt past
1378 SET past.owning_organization_id =
1379 (SELECT hout.organization_id
1380 FROM hr_all_organization_units_tl hout
1381 WHERE hout.name = past.owning_organization_dsp
1382 AND hout.language = UserEnv('lang')
1383 AND ROWNUM < 2)
1384 WHERE past.owning_organization_dsp IS NOT NULL;
1385 l_progress := 10;
1386
1387 --If Using Org id not global, then owning org will be the using org.
1388 UPDATE po_approved_supplier_list_gt past
1389 SET past.using_organization_id = owning_organization_id
1390 WHERE past.using_organization_id <> -1;
1391 l_progress := 20;
1392
1393 UPDATE po_approved_supplier_list_gt past
1394 SET past.asl_status_id =
1395 (SELECT pas.status_id
1396 FROM po_asl_statuses pas
1397 WHERE past.asl_status_dsp = pas.status
1398 AND ROWNUM < 2)
1399 WHERE past.asl_status_dsp IS NOT NULL;
1400 l_progress := 30;
1401
1402 UPDATE po_approved_supplier_list_gt past
1403 SET past.manufacturer_id =
1404 (SELECT mm.manufacturer_id
1405 FROM mtl_manufacturers mm
1406 WHERE mm.manufacturer_name = past.manufacturer_dsp
1407 AND ROWNUM < 2)
1408 WHERE past.manufacturer_dsp IS NOT NULL;
1409 l_progress := 40;
1410
1411 UPDATE po_approved_supplier_list_gt past
1412 SET past.vendor_id =
1413 (SELECT pv.vendor_id
1414 FROM po_vendors pv
1415 WHERE pv.vendor_name = past.vendor_dsp
1416 AND ROWNUM < 2)
1417 WHERE past.vendor_dsp IS NOT NULL;
1418 l_progress := 50;
1419
1420 UPDATE po_approved_supplier_list_gt past
1421 SET past.item_id =
1422 (SELECT msi.inventory_item_id
1423 FROM mtl_system_items_kfv msi
1424 WHERE msi.concatenated_segments = past.item_dsp
1425 AND msi.organization_id = past.owning_organization_id
1426 AND ROWNUM < 2)
1427 WHERE past.item_dsp IS NOT NULL;
1428 l_progress := 60;
1429
1430 UPDATE po_approved_supplier_list_gt past
1431 SET past.category_id =
1432 (SELECT mc.category_id
1433 FROM mtl_categories_kfv mc
1434 WHERE mc.concatenated_segments = past.category_dsp
1435 AND ROWNUM < 2)
1436 WHERE past.category_dsp IS NOT NULL;
1437 l_progress := 70;
1438
1439 UPDATE po_approved_supplier_list_gt past
1440 SET past.vendor_site_id =
1441 (SELECT pvs.vendor_site_id
1442 FROM po_vendor_sites_all pvs
1443 WHERE pvs.vendor_site_code = past.vendor_site_dsp
1444 AND pvs.vendor_id = past.vendor_id
1445 AND pvs.org_id = past.owning_organization_id
1446 AND ROWNUM < 2)
1447 WHERE past.vendor_site_dsp IS NOT NULL;
1448 l_progress := 80;
1449
1450 UPDATE po_approved_supplier_list_gt asl1
1451 SET asl1.manufacturer_asl_id =
1452 (SELECT pasl.asl_id
1453 FROM po_approved_supplier_list pasl,
1454 mtl_manufacturers mm,
1455 po_asl_statuses past,
1456 po_asl_status_rules psr
1457 WHERE pasl.manufacturer_id = mm.manufacturer_id
1458 AND pasl.asl_status_id = past.status_id
1459 AND psr.status_id = past.status_id
1460 AND psr.business_rule = '4_DISTRIBUTOR_MFR_LINK'
1461 AND psr.allow_action_flag = 'Y'
1462 AND (pasl.using_organization_id = -1
1463 OR pasl.using_organization_id = asl1.using_organization_id)
1464 AND ((pasl.item_id = asl1.item_id
1465 AND (pasl.category_id IS NULL
1466 AND asl1.category_id IS NULL
1467 )
1468 )
1469 OR (pasl.category_id = asl1.category_id
1470 AND (pasl.item_id IS NULL
1471 AND asl1.item_id IS NULL
1472 )
1473 )
1474 )
1475 )
1476 WHERE asl1.manufacturer_asl_dsp IS NOT NULL;
1477 l_progress := 100;
1478
1479 po_asl_api_pvt.log('derive_ids_past: po_approved_supplier_list_gt rowcount:'
1480 || SQL%ROWCOUNT);
1481 po_asl_api_pvt.log('END ::: derive_ids_past');
1482 EXCEPTION
1483
1484 WHEN OTHERS THEN
1485
1486 po_asl_api_pvt.log('derive_ids_past when others exception '
1487 || l_progress || ';' || SQLERRM);
1488 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1489 x_return_msg := SQLERRM;
1490
1491 END derive_ids_past;
1492
1493 --------------------------------------------------------------------------------
1494 --START of comments
1495
1496 --NAME: derive_ids_podoc
1497
1498 --FUNCTION:
1499 -- this will derive ids for the display fields in po_asl_documents_gt
1500 -- which ever applicabale
1501
1502 --parameters:
1503
1504 --OUT:
1505 -- x_return_status VARCHAR2
1506 -- x_return_msg VARCHAR2
1507
1508 --END of comments
1509 --------------------------------------------------------------------------------
1510
1511 PROCEDURE derive_ids_podoc(
1512 x_return_status OUT NOCOPY VARCHAR2
1513 , x_return_msg OUT NOCOPY VARCHAR2
1514 )
1515 IS
1516 l_progress NUMBER := 0;
1517 BEGIN
1518 po_asl_api_pvt.log('START ::: derive_ids_podoc');
1519
1520 UPDATE po_asl_documents_gt padt
1521 SET padt.using_organization_id =
1522 (SELECT hout.organization_id
1523 FROM hr_all_organization_units_tl hout
1524 WHERE hout.name = padt.using_organization_dsp
1525 AND hout.language = UserEnv('lang')
1526 AND ROWNUM < 2)
1527 WHERE padt.using_organization_dsp IS NOT NULL;
1528 l_progress := 25;
1529
1530 UPDATE po_asl_documents_gt padt
1531 SET padt.document_type_code =
1532 (SELECT polc.lookup_code
1533 FROM po_lookup_codes polc
1534 WHERE polc.lookup_type = 'SOURCE DOCUMENT TYPE'
1535 AND Upper(polc.displayed_field) = Upper(padt.document_type_dsp)
1536 AND ROWNUM < 2)
1537 WHERE padt.document_type_dsp IS NOT NULL;
1538 l_progress := 50;
1539
1540 UPDATE po_asl_documents_gt padt
1541 SET padt.document_header_id =
1542 get_doc_header (p_user_key => padt.user_key ,
1543 p_doc_type => padt.document_type_code ,
1544 p_using_org_id => padt.using_organization_id ,
1545 p_segment => padt.document_header_dsp)
1546 WHERE padt.document_header_dsp IS NOT NULL;
1547 l_progress := 75;
1548
1549 UPDATE po_asl_documents_gt padt
1550 SET padt.document_line_id =
1551 get_doc_line_id(p_user_key => padt.user_key ,
1552 p_header_id => padt.document_header_id ,
1553 p_using_org_id => padt.using_organization_id ,
1554 p_line_num => padt.document_line_num_dsp)
1555 WHERE document_line_num_dsp IS NOT NULL;
1556 l_progress := 100;
1557 po_asl_api_pvt.log('derive_ids_podoc UPDATE po_asl_documents_gt rowcount:'
1558 || SQL%ROWCOUNT);
1559 po_asl_api_pvt.log('END ::: derive_ids_podoc');
1560 EXCEPTION
1561
1562 WHEN OTHERS THEN
1563
1564 po_asl_api_pvt.log('derive_ids_podoc when others exception '
1565 || l_progress || ';' || SQLERRM);
1566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1567 x_return_msg := SQLERRM;
1568
1569 END derive_ids_podoc;
1570
1571 --------------------------------------------------------------------------------
1572 --START of comments
1573
1574 --NAME: derive_ids_ch_auth
1575
1576 --FUNCTION:
1577 -- this will derive ids for the display fields in chv_authorizations_gt
1578 -- which ever applicabale
1579
1580 --parameters:
1581
1582 --OUT:
1583 -- x_return_status VARCHAR2
1584 -- x_return_msg VARCHAR2
1585
1586 --END of comments
1587 --------------------------------------------------------------------------------
1588
1589 PROCEDURE derive_ids_ch_auth(
1590 x_return_status OUT NOCOPY VARCHAR2
1591 , x_return_msg OUT NOCOPY VARCHAR2
1592 )
1593 IS
1594 l_progress NUMBER := 0;
1595
1596 BEGIN
1597 po_asl_api_pvt.log('START ::: derive_ids_ch_auth');
1598
1599 UPDATE chv_authorizations_gt chv
1600 SET chv.authorization_code =
1601 (SELECT polc.lookup_code
1602 FROM po_lookup_codes polc
1603 WHERE polc.lookup_type = 'AUTHORIZATION_TYPE'
1604 AND polc.displayed_field = chv.authorization_code_dsp
1605 AND ROWNUM < 2)
1606 WHERE chv.authorization_code_dsp IS NOT NULL;
1607 l_progress := 50;
1608
1609 UPDATE chv_authorizations_gt chv
1610 SET chv.using_organization_id =
1611 (SELECT hout.organization_id
1612 FROM hr_all_organization_units_tl hout
1613 WHERE hout.name = chv.using_organization_dsp
1614 AND hout.language = UserEnv('lang')
1615 AND ROWNUM < 2)
1616 WHERE chv.using_organization_dsp IS NOT NULL;
1617 l_progress := 100;
1618
1619 po_asl_api_pvt.log('derive_ids_ch_auth update chv_authorizations_gt rowcount:'
1620 || SQL%ROWCOUNT);
1621 po_asl_api_pvt.log('END ::: derive_ids_ch_auth');
1622 EXCEPTION
1623
1624 WHEN OTHERS THEN
1625
1626 po_asl_api_pvt.log('derive_ids_ch_auth when others exception '
1627 || l_progress || ';' || SQLERRM);
1628 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1629 x_return_msg := SQLERRM;
1630
1631 END derive_ids_ch_auth;
1632
1633 --------------------------------------------------------------------------------
1634 --START of comments
1635
1636 --NAME: derive_ids_asl_attr
1637
1638 --FUNCTION:
1639 -- this will derive ids for the display fields in po_asl_attributes_gt
1640 -- which ever applicabale
1641
1642 --parameters:
1643
1644 --OUT:
1645 -- x_return_status VARCHAR2
1646 -- x_return_msg VARCHAR2
1647
1648 --END of comments
1649 --------------------------------------------------------------------------------
1650
1651 PROCEDURE derive_ids_asl_attr(
1652 x_return_status OUT NOCOPY VARCHAR2
1653 , x_return_msg OUT NOCOPY VARCHAR2
1654 )
1655 IS
1656 l_progress NUMBER := 0;
1657
1658 BEGIN
1659 po_asl_api_pvt.log('START ::: derive_ids_asl_attr');
1660
1661 UPDATE po_asl_attributes_gt paa
1662 SET paa.using_organization_id =
1663 (SELECT hout.organization_id
1664 FROM hr_all_organization_units_tl hout
1665 WHERE hout.name = paa.using_organization_dsp
1666 AND hout.language = UserEnv('lang')
1667 AND ROWNUM < 2)
1668 WHERE paa.using_organization_dsp IS NOT NULL;
1669 l_progress := 10;
1670
1671 UPDATE po_asl_attributes_gt paa
1672 SET paa.release_generation_method =
1673 (SELECT polc.lookup_code
1674 FROM po_lookup_codes polc
1675 WHERE polc.lookup_type = 'DOC GENERATION METHOD'
1676 AND polc.displayed_field = paa.release_generation_method_dsp
1677 AND ROWNUM < 2)
1678 WHERE paa.release_generation_method_dsp IS NOT NULL;
1679 l_progress := 15;
1680
1681 UPDATE po_asl_attributes_gt paa
1682 SET paa.plan_schedule_type =
1683 (SELECT polc.lookup_code
1684 FROM po_lookup_codes polc
1685 WHERE polc.lookup_type = 'PLAN_SCHEDULE_SUBTYPE'
1686 AND polc.displayed_field = paa.plan_schedule_type_dsp
1687 AND ROWNUM < 2)
1688 WHERE paa.plan_schedule_type_dsp IS NOT NULL;
1689 l_progress := 20;
1690
1691 UPDATE po_asl_attributes_gt paa
1692 SET paa.ship_schedule_type =
1693 (SELECT polc.lookup_code
1694 FROM po_lookup_codes polc
1695 WHERE polc.lookup_type = 'SHIP_SCHEDULE_SUBTYPE'
1696 AND polc.displayed_field = paa.ship_schedule_type_dsp
1697 AND ROWNUM < 2)
1698 WHERE paa.ship_schedule_type_dsp IS NOT NULL;
1699 l_progress := 25;
1700
1701 UPDATE po_asl_attributes_gt paa
1702 SET paa.plan_bucket_pattern_id =
1703 (SELECT cbp.bucket_pattern_id
1704 FROM chv_bucket_patterns cbp
1705 WHERE cbp.bucket_pattern_name = paa.plan_bucket_pattern_dsp
1706 AND ROWNUM < 2)
1707 WHERE paa.plan_bucket_pattern_dsp IS NOT NULL;
1708 l_progress := 30;
1709
1710 UPDATE po_asl_attributes_gt paa
1711 SET paa.ship_bucket_pattern_id =
1712 (SELECT cbp.bucket_pattern_id
1713 FROM chv_bucket_patterns cbp
1714 WHERE cbp.bucket_pattern_name = paa.ship_bucket_pattern_dsp
1715 AND ROWNUM < 2)
1716 WHERE paa.ship_bucket_pattern_dsp IS NOT NULL;
1717 l_progress := 35;
1718
1719 UPDATE po_asl_attributes_gt paa
1720 SET paa.scheduler_id =
1721 (SELECT ppf.person_id
1722 FROM per_people_f ppf
1723 WHERE ppf.full_name = paa.scheduler_dsp
1724 AND ROWNUM < 2)
1725 WHERE paa.scheduler_dsp IS NOT NULL;
1726 l_progress := 40;
1727
1728 UPDATE po_asl_attributes_gt paa
1729 SET paa.vendor_id =
1730 (SELECT pv.vendor_id
1731 FROM po_vendors pv
1732 WHERE pv.vendor_name = paa.vendor_dsp
1733 AND ROWNUM < 2)
1734 WHERE paa.vendor_dsp IS NOT NULL;
1735 l_progress := 50;
1736
1737 UPDATE po_asl_attributes_gt paa
1738 SET paa.vendor_site_id =
1739 (SELECT pvs.vendor_site_id
1740 FROM po_vendor_sites_all pvs
1741 WHERE pvs.vendor_site_code = paa.vendor_site_dsp
1742 AND pvs.vendor_id = paa.vendor_id
1743 AND pvs.org_id IN
1744 (SELECT owning_organization_id
1745 FROM po_approved_supplier_list_gt past
1746 WHERE paa.user_key = past.user_key)
1747 AND ROWNUM < 2)
1748 WHERE paa.vendor_site_dsp IS NOT NULL;
1749 l_progress := 60;
1750
1751 UPDATE po_asl_attributes_gt paa
1752 SET paa.item_id =
1753 (SELECT msi.inventory_item_id
1754 FROM mtl_system_items_kfv msi
1755 WHERE msi.concatenated_segments= paa.item_dsp
1756 AND ROWNUM < 2)
1757 WHERE paa.item_dsp IS NOT NULL;
1758 l_progress := 70;
1759
1760 UPDATE po_asl_attributes_gt paa
1761 SET paa.category_id =
1762 (SELECT mc.category_id
1763 FROM mtl_categories_kfv mc
1764 WHERE mc.concatenated_segments = paa.category_dsp
1765 AND ROWNUM < 2)
1766 WHERE paa.category_dsp IS NOT NULL;
1767 l_progress := 80;
1768
1769 UPDATE po_asl_attributes_gt paa
1770 SET paa.vmi_replenishment_approval =
1771 Decode(paa.vmi_replenishment_approval_dsp,
1772 'Supplier or Buyer', 'SUPPLIER_OR_BUYER',
1773 'None' , 'NONE',
1774 'Buyer' , 'BUYER')
1775 WHERE paa.vmi_replenishment_approval_dsp IS NOT NULL;
1776 l_progress := 90;
1777
1778 UPDATE po_asl_attributes_gt paa
1779 SET paa.replenishment_method =
1780 Decode(paa.replenishment_method_dsp,
1781 'Min - Max Quantities' , 1,
1782 'Min - Max Days' , 2,
1783 'Min Qty and Fixed Order Qty' , 3,
1784 'Min Days and Fixed Order Qty' , 4)
1785 WHERE paa.replenishment_method_dsp IS NOT NULL;
1786 l_progress := 100;
1787
1788 po_asl_api_pvt.log('derive_ids_asl_attr UPDATE po_asl_attributes_gt rowcount:'
1789 || l_progress || ';' || SQL%ROWCOUNT);
1790 po_asl_api_pvt.log('END ::: derive_ids_asl_attr');
1791 EXCEPTION
1792
1793 WHEN OTHERS THEN
1794
1795 po_asl_api_pvt.log('derive_ids_asl_attr when others exception ' || SQLERRM);
1796 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1797 x_return_msg := SQLERRM;
1798
1799 END derive_ids_asl_attr;
1800
1801 --------------------------------------------------------------------------------
1802 --START of comments
1803
1804 --NAME: derive_ids_sup_cap
1805
1806 --FUNCTION:
1807 -- this will derive ids for the display fields in
1808 -- po_supplier_item_capacity_gt, which ever applicabale
1809
1810 --parameters:
1811
1812 --OUT:
1813 -- x_return_status VARCHAR2
1814 -- x_return_msg VARCHAR2
1815
1816 --END of comments
1817 --------------------------------------------------------------------------------
1818
1819 PROCEDURE derive_ids_sup_cap(
1820 x_return_status OUT NOCOPY VARCHAR2
1821 , x_return_msg OUT NOCOPY VARCHAR2
1822 )
1823 IS
1824
1825 BEGIN
1826 po_asl_api_pvt.log('START ::: derive_ids_sup_cap');
1827
1828 UPDATE po_supplier_item_capacity_gt poic
1829 SET poic.using_organization_id =
1830 (SELECT hout.organization_id
1831 FROM hr_all_organization_units_tl hout
1832 WHERE hout.name = poic.using_organization_dsp
1833 AND hout.language = UserEnv('lang')
1834 AND ROWNUM < 2)
1835 WHERE poic.using_organization_dsp IS NOT NULL;
1836
1837 po_asl_api_pvt.log('derive_ids_sup_cap UPDATE po_supplier_item_capacity_gt rowcount:'
1838 || SQL%ROWCOUNT);
1839 po_asl_api_pvt.log('END ::: derive_ids_sup_cap');
1840 EXCEPTION
1841
1842 WHEN OTHERS THEN
1843
1844 po_asl_api_pvt.log('derive_ids_sup_cap when others exception ' || SQLERRM);
1845 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1846 x_return_msg := SQLERRM;
1847
1848 END derive_ids_sup_cap;
1849
1850 --------------------------------------------------------------------------------
1851 --START of comments
1852
1853 --NAME: derive_ids_sup_tol
1854
1855 --FUNCTION:
1856 -- this will derive ids for the display fields in
1857 -- po_supplier_item_tolerance_gt, which ever applicabale
1858
1859 --parameters:
1860
1861 --OUT:
1862 -- x_return_status VARCHAR2
1863 -- x_return_msg VARCHAR2
1864
1865 --END of comments
1866 --------------------------------------------------------------------------------
1867
1868 PROCEDURE derive_ids_sup_tol(
1869 x_return_status OUT NOCOPY VARCHAR2
1870 , x_return_msg OUT NOCOPY VARCHAR2
1871 )
1872 IS
1873
1874 BEGIN
1875 po_asl_api_pvt.log('START ::: derive_ids_sup_tol');
1876
1877 UPDATE po_supplier_item_tolerance_gt poit
1878 SET poit.using_organization_id =
1879 (SELECT hout.organization_id
1880 FROM hr_all_organization_units_tl hout
1881 WHERE hout.name = poit.using_organization_dsp
1882 AND hout.language = UserEnv('lang')
1883 AND ROWNUM < 2)
1884 WHERE poit.using_organization_dsp IS NOT NULL;
1885
1886 po_asl_api_pvt.log('derive_ids_sup_tol UPDATE po_supplier_item_tolerance_gt rowcount:'
1887 || SQL%ROWCOUNT);
1888 po_asl_api_pvt.log('END ::: derive_ids_sup_tol');
1889 EXCEPTION
1890
1891 WHEN OTHERS THEN
1892
1893 po_asl_api_pvt.log('derive_ids_sup_tol when others exception ' || SQLERRM);
1894 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1895 x_return_msg := SQLERRM;
1896
1897 END derive_ids_sup_tol;
1898
1899 --------------------------------------------------------------------------------
1900 --START of comments
1901
1902 --NAME: collect_invalids_in_asl_gt
1903
1904 --FUNCTION:
1905 -- This will collect userkeys for which ids not found for the descriptions
1906 -- in po_approved_supplier_list_gt table.
1907
1908 --PARAMETERS:
1909
1910 --OUT:
1911 -- x_return_status VARCHAR2
1912 -- x_return_msg VARCHAR2
1913
1914 --END of comments
1915 --------------------------------------------------------------------------------
1916
1917 PROCEDURE collect_invalids_in_asl_gt(
1918 x_return_status OUT NOCOPY VARCHAR2
1919 , x_return_msg OUT NOCOPY VARCHAR2
1920 )
1921 IS
1922 l_progress NUMBER := 0;
1923 l_user_key_tbl po_tbl_number;
1924 l_entity_name po_tbl_varchar30;
1925 l_reject_reason po_tbl_varchar2000;
1926 l_user_key_tbl1 po_tbl_number;
1927 l_entity_name1 po_tbl_varchar30;
1928 l_reject_reason1 po_tbl_varchar2000;
1929
1930 BEGIN
1931 po_asl_api_pvt.log('START ::: collect_invalids_in_asl_gt');
1932
1933 --SQL queries are broken since cost is greater than 10
1934 SELECT user_key ,
1935 entity ,
1936 msg
1937 BULK COLLECT INTO
1938 l_user_key_tbl ,
1939 l_entity_name ,
1940 l_reject_reason
1941 FROM
1942 (SELECT user_key ,
1943 'po_approved_supplier_list_gt' AS entity ,
1944 fnd_message.get_string('PO','OWNING_ORG_NOT_FOUND') AS msg
1945 FROM po_approved_supplier_list_gt
1946 WHERE owning_organization_dsp IS NOT NULL
1947 AND owning_organization_id IS NULL
1948
1949 UNION ALL
1950
1951 SELECT user_key ,
1952 'po_approved_supplier_list_gt' AS entity ,
1953 fnd_message.get_string('PO','STATUS_NOT_FOUND') AS msg
1954 FROM po_approved_supplier_list_gt
1955 WHERE asl_status_dsp IS NOT NULL
1956 AND asl_status_id IS NULL
1957
1958 UNION ALL
1959
1960 SELECT user_key ,
1961 'po_approved_supplier_list_gt' AS entity ,
1962 fnd_message.get_string('PO','MANUFACTURER_NOT_FOUND') AS msg
1963 FROM po_approved_supplier_list_gt
1964 WHERE (manufacturer_dsp IS NOT NULL
1965 AND manufacturer_id IS NULL)
1966 OR
1967 (manufacturer_asl_dsp IS NOT NULL
1968 AND manufacturer_asl_id IS NULL)
1969
1970 UNION ALL
1971
1972 SELECT user_key ,
1973 'po_approved_supplier_list_gt' AS entity ,
1974 fnd_message.get_string('PO','VENDOR_NOT_FOUND') AS msg
1975 FROM po_approved_supplier_list_gt
1976 WHERE vendor_dsp IS NOT NULL
1977 AND vendor_id IS NULL
1978
1979 UNION ALL
1980
1981 SELECT user_key ,
1982 'po_approved_supplier_list_gt' AS entity ,
1983 fnd_message.get_string('PO','ITEM_NOT_FOUND') AS msg
1984 FROM po_approved_supplier_list_gt
1985 WHERE item_dsp IS NOT NULL
1986 AND item_id IS NULL);
1987
1988 l_progress := 25;
1989
1990 SELECT user_key ,
1991 entity ,
1992 msg
1993 BULK COLLECT INTO
1994 l_user_key_tbl1 ,
1995 l_entity_name1 ,
1996 l_reject_reason1
1997 FROM
1998 (SELECT user_key ,
1999 'po_approved_supplier_list_gt' AS entity ,
2000 fnd_message.get_string('PO','CATEGORY_NOT_FOUND') AS msg
2001 FROM po_approved_supplier_list_gt
2002 WHERE category_dsp IS NOT NULL
2003 AND category_id IS NULL
2004
2005 UNION ALL
2006
2007 SELECT user_key ,
2008 'po_approved_supplier_list_gt' AS entity ,
2009 fnd_message.get_string('PO','VENDOR_SITE_NOT_FOUND') AS msg
2010 FROM po_approved_supplier_list_gt
2011 WHERE vendor_site_dsp IS NOT NULL
2012 AND vendor_site_id IS NULL
2013
2014 UNION ALL
2015
2016 SELECT user_key ,
2017 'po_approved_supplier_list_gt' AS entity ,
2018 fnd_message.get_string('PO','INVALID_PROCESS_ACTION') AS msg
2019 FROM po_approved_supplier_list_gt
2020 WHERE process_action NOT IN
2021 (PO_ASL_API_PUB.g_ACTION_CREATE,
2022 PO_ASL_API_PUB.g_ACTION_UPDATE,
2023 PO_ASL_API_PUB.g_ACTION_SYNC)
2024 OR process_action IS NULL
2025 );
2026
2027 l_progress := 50;
2028 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
2029 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
2030 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
2031 --call po_asl_api_pvt.reject_asl_record for which the id VALUES remain NULL
2032 po_asl_api_pvt.log('collect_invalids_in_asl_gt: reject count:' ||
2033 l_user_key_tbl1.Count);
2034 IF l_user_key_tbl1.Count > 0
2035 THEN
2036 po_asl_api_pvt.reject_asl_record(
2037 p_user_key_tbl => l_user_key_tbl1,
2038 p_rejection_reason=> l_reject_reason1,
2039 p_entity_name => l_entity_name1,
2040 p_session_key => g_session_key,
2041 x_return_status => x_return_status,
2042 x_return_msg => x_return_msg
2043 );
2044 END IF;
2045 l_progress := 100;
2046 x_return_status := FND_API.G_RET_STS_SUCCESS;
2047 po_asl_api_pvt.log('END ::: collect_invalids_in_asl_gt');
2048 EXCEPTION
2049
2050 WHEN OTHERS THEN
2051
2052 po_asl_api_pvt.log('collect_invalids_in_asl_gt when others exception '
2053 || l_progress || ';' || SQLERRM);
2054 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2055 x_return_msg := SQLERRM;
2056
2057 END collect_invalids_in_asl_gt;
2058
2059 --------------------------------------------------------------------------------
2060 --START of comments
2061
2062 --NAME: collect_invalids_in_aslattr_gt
2063
2064 --FUNCTION:
2065 -- This will collect userkeys for which ids not found for the descriptions
2066 -- in po_asl_attributes_gt table.
2067
2068 --PARAMETERS:
2069 --OUT:
2070 -- x_return_status VARCHAR2
2071 -- x_return_msg VARCHAR2
2072
2073 --END of comments
2074 --------------------------------------------------------------------------------
2075
2076 PROCEDURE collect_invalids_in_aslattr_gt(
2077 x_return_status OUT NOCOPY VARCHAR2
2078 , x_return_msg OUT NOCOPY VARCHAR2
2079 )
2080 IS
2081 l_progress NUMBER := 0;
2082 l_user_key_tbl po_tbl_number;
2083 l_entity_name po_tbl_varchar30;
2084 l_reject_reason po_tbl_varchar2000;
2085 l_user_key_tbl1 po_tbl_number;
2086 l_entity_name1 po_tbl_varchar30;
2087 l_reject_reason1 po_tbl_varchar2000;
2088
2089 BEGIN
2090 po_asl_api_pvt.log('START ::: collect_invalids_in_aslattr_gt');
2091
2092 --SQL queries are broken since cost is greater than 10
2093 SELECT user_key ,
2094 entity ,
2095 msg
2096 BULK COLLECT INTO
2097 l_user_key_tbl ,
2098 l_entity_name ,
2099 l_reject_reason
2100 FROM (
2101 SELECT user_key ,
2102 'po_asl_attributes_gt' AS entity ,
2103 fnd_message.get_string('PO','REL_GEN_METHOD_NOT_FOUND') AS msg
2104 FROM po_asl_attributes_gt
2105 WHERE release_generation_method_dsp IS NOT NULL
2106 AND release_generation_method IS NULL
2107
2108 UNION ALL
2109
2110 SELECT user_key ,
2111 'po_asl_attributes_gt' AS entity ,
2112 fnd_message.get_string('PO','PLAN_SCHEDULE_NOT_FOUND') AS msg
2113 FROM po_asl_attributes_gt
2114 WHERE plan_schedule_type_dsp IS NOT NULL
2115 AND plan_schedule_type IS NULL
2116
2117 UNION ALL
2118
2119 SELECT user_key ,
2120 'po_asl_attributes_gt' AS entity ,
2121 fnd_message.get_string('PO','SHIP_SCHEDULE_NOT_FOUND') AS msg
2122 FROM po_asl_attributes_gt
2123 WHERE ship_schedule_type_dsp IS NOT NULL
2124 AND ship_schedule_type IS NULL
2125
2126 UNION ALL
2127
2128 SELECT user_key ,
2129 'po_asl_attributes_gt' AS entity ,
2130 fnd_message.get_string('PO','PLAN_BUCKET_NOT_FOUND') AS msg
2131 FROM po_asl_attributes_gt
2132 WHERE plan_bucket_pattern_dsp IS NOT NULL
2133 AND plan_bucket_pattern_id IS NULL
2134
2135 UNION ALL
2136
2137 SELECT user_key ,
2138 'po_asl_attributes_gt' AS entity ,
2139 fnd_message.get_string('PO','SHIP_BUCKET_NOT_FOUND') AS msg
2140 FROM po_asl_attributes_gt
2141 WHERE ship_bucket_pattern_dsp IS NOT NULL
2142 AND ship_bucket_pattern_id IS NULL);
2143 l_progress := 10;
2144
2145 SELECT user_key ,
2146 entity ,
2147 msg
2148 BULK COLLECT INTO
2149 l_user_key_tbl1 ,
2150 l_entity_name1 ,
2151 l_reject_reason1
2152 FROM (
2153 SELECT user_key ,
2154 'po_asl_attributes_gt' AS entity ,
2155 fnd_message.get_string('PO','SHCEDULER_NOT_FOUND') AS msg
2156 FROM po_asl_attributes_gt
2157 WHERE scheduler_dsp IS NOT NULL
2158 AND scheduler_id IS NULL
2159
2160 UNION ALL
2161
2162 SELECT user_key ,
2163 'po_asl_attributes_gt' AS entity ,
2164 fnd_message.get_string('PO','VENDOR_NOT_FOUND') AS msg
2165 FROM po_asl_attributes_gt
2166 WHERE vendor_dsp IS NOT NULL
2167 AND vendor_id IS NULL
2168
2169 UNION ALL
2170
2171 SELECT user_key ,
2172 'po_asl_attributes_gt' AS entity ,
2173 fnd_message.get_string('PO','ITEM_NOT_FOUND') AS msg
2174 FROM po_asl_attributes_gt
2175 WHERE item_dsp IS NOT NULL
2176 AND item_id IS NULL
2177
2178 UNION ALL
2179
2180 SELECT user_key ,
2181 'po_asl_attributes_gt' AS entity ,
2182 fnd_message.get_string('PO','CATEGORY_NOT_FOUND') AS msg
2183 FROM po_asl_attributes_gt
2184 WHERE category_dsp IS NOT NULL
2185 AND category_id IS NULL
2186
2187 UNION ALL
2188
2189 SELECT user_key ,
2190 'po_asl_attributes_gt' AS entity ,
2191 fnd_message.get_string('PO','VENDOR_SITE_NOT_FOUND') AS msg
2192 FROM po_asl_attributes_gt
2193 WHERE vendor_site_dsp IS NOT NULL
2194 AND vendor_site_id IS NULL
2195
2196 UNION ALL
2197
2198 SELECT user_key ,
2199 'po_asl_attributes_gt' AS entity ,
2200 fnd_message.get_string('PO','USING_ORG_NOT_FOUND') AS msg
2201 FROM po_asl_attributes_gt
2202 WHERE using_organization_dsp IS NOT NULL
2203 AND using_organization_id IS NULL);
2204
2205 l_progress := 25;
2206 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
2207 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
2208 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
2209
2210 SELECT user_key ,
2211 entity ,
2212 msg
2213 BULK COLLECT INTO
2214 l_user_key_tbl ,
2215 l_entity_name ,
2216 l_reject_reason
2217 FROM (
2218 SELECT user_key ,
2219 'po_asl_attributes_gt' AS entity ,
2220 fnd_message.get_string('PO','REPL_APPROVAL_NOT_FOUND') AS msg
2221 FROM po_asl_attributes_gt
2222 WHERE vmi_replenishment_approval_dsp IS NOT NULL
2223 AND vmi_replenishment_approval IS NULL
2224
2225 UNION ALL
2226
2227 SELECT user_key ,
2228 'po_asl_attributes_gt' AS entity ,
2229 fnd_message.get_string('PO','REPL_METHOD_NOT_FOUND') AS msg
2230 FROM po_asl_attributes_gt
2231 WHERE replenishment_method_dsp IS NOT NULL
2232 AND replenishment_method IS NULL
2233
2234 UNION ALL
2235 --Reject records if process action is other than ADD,UPDATE,DELETE
2236 SELECT user_key ,
2237 'po_asl_attributes_gt' AS entity ,
2238 fnd_message.get_string('PO','INVALID_PROCESS_ACTION') AS msg
2239 FROM po_asl_attributes_gt
2240 WHERE process_action NOT IN
2241 (PO_ASL_API_PUB.g_ACTION_ADD,
2242 PO_ASL_API_PUB.g_ACTION_UPDATE,
2243 PO_ASL_API_PUB.g_ACTION_DELETE)
2244 OR process_action IS NULL
2245
2246 UNION ALL
2247 --Reject records if ASL Process action is create and attribute's process
2248 --action is delete/update
2249 SELECT PAA.user_key ,
2250 'po_asl_attributes_gt' AS entity ,
2251 fnd_message.get_string('PO','INVALID_PAA_ACTION') AS msg
2252 FROM po_asl_attributes_gt PAA,
2253 po_approved_supplier_list_gt ASL
2254 WHERE ASL.user_key = PAA.user_key
2255 AND ASL.process_action = PO_ASL_API_PUB.g_ACTION_CREATE
2256 AND PAA.process_action <> PO_ASL_API_PUB.g_ACTION_ADD
2257 );
2258
2259 l_progress := 50;
2260 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
2261 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
2262 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_reject_reason;
2263 --call po_asl_api_pvt.reject_asl_record for which the id VALUES remain NULL
2264 po_asl_api_pvt.log('collect_invalids_in_aslattr_gt: reject count:' ||
2265 l_user_key_tbl1.Count);
2266 IF l_user_key_tbl1.Count > 0
2267 THEN
2268 po_asl_api_pvt.reject_asl_record(
2269 p_user_key_tbl => l_user_key_tbl1,
2270 p_rejection_reason=> l_reject_reason1,
2271 p_entity_name => l_entity_name1,
2272 p_session_key => g_session_key,
2273 x_return_status => x_return_status,
2274 x_return_msg => x_return_msg
2275 );
2276 END IF;
2277 l_progress := 100;
2278 x_return_status := FND_API.G_RET_STS_SUCCESS;
2279 po_asl_api_pvt.log('END ::: collect_invalids_in_aslattr_gt');
2280 EXCEPTION
2281
2282 WHEN OTHERS THEN
2283
2284 po_asl_api_pvt.log('collect_invalids_in_aslattr_gt when others exception '
2285 || l_progress || ';' || SQLERRM);
2286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2287 x_return_msg := SQLERRM;
2288
2289 END collect_invalids_in_aslattr_gt;
2290
2291 --------------------------------------------------------------------------------
2292 --START of comments
2293
2294 --NAME: collect_invalids_in_other_gt
2295
2296 --FUNCTION:
2297 -- This will collect userkeys for which ids not found for the descriptions
2298 -- in chv_authorizations_gt, po_supplier_item_capacity_gt,
2299 -- po_supplier_item_tolerance_gt, po_asl_documents_gt tables.
2300
2301 --PARAMETERS:
2302 --IN:
2303 -- p_user_key_tbl po_tbl_number
2304 -- p_entity_name po_tbl_varchar30
2305 -- p_rejection_reason po_tbl_varchar2000
2306
2307 --OUT:
2308 -- x_return_status VARCHAR2
2309 -- x_return_msg VARCHAR2
2310
2311 --END of comments
2312 --------------------------------------------------------------------------------
2313
2314 PROCEDURE collect_invalids_in_other_gt(
2315 x_return_status OUT NOCOPY VARCHAR2
2316 , x_return_msg OUT NOCOPY VARCHAR2
2317 )
2318 IS
2319 l_progress NUMBER := 0;
2320 l_user_key_tbl po_tbl_number;
2321 l_entity_name po_tbl_varchar30;
2322 l_rejection_reason po_tbl_varchar2000;
2323 l_user_key_tbl1 po_tbl_number;
2324 l_entity_name1 po_tbl_varchar30;
2325 l_reject_reason1 po_tbl_varchar2000;
2326
2327 BEGIN
2328 po_asl_api_pvt.log('START ::: collect_invalids_in_other_gt');
2329
2330 --SQL queries are broken since cost is greater than 10
2331 SELECT user_key ,
2332 entity ,
2333 msg
2334 BULK COLLECT INTO
2335 l_user_key_tbl ,
2336 l_entity_name ,
2337 l_rejection_reason
2338 FROM
2339 (SELECT user_key ,
2340 'chv_authorizations_gt' AS entity ,
2341 fnd_message.get_string('PO','AUTH_CODE_NOT_FOUND') AS msg
2342 FROM chv_authorizations_gt
2343 WHERE authorization_code_dsp IS NOT NULL
2344 AND authorization_code IS NULL
2345
2346 UNION ALL
2347
2348 SELECT user_key ,
2349 'po_asl_documents_gt' AS entity ,
2350 fnd_message.get_string('PO','DOC_TYPE_NOT_FOUND') AS msg
2351 FROM po_asl_documents_gt
2352 WHERE document_type_dsp IS NOT NULL
2353 AND document_type_code IS NULL
2354
2355 UNION ALL
2356
2357 SELECT user_key ,
2358 'po_asl_documents_gt' AS entity ,
2359 fnd_message.get_string('PO','DOC_HEADER_NOT_FOUND') AS msg
2360 FROM po_asl_documents_gt
2361 WHERE document_header_dsp IS NOT NULL
2362 AND document_header_id IS NULL
2363
2364 UNION ALL
2365
2366 SELECT user_key ,
2367 'po_asl_documents_gt' AS entity ,
2368 fnd_message.get_string('PO','DOC_LINE_NOT_FOUND') AS msg
2369 FROM po_asl_documents_gt
2370 WHERE document_line_num_dsp IS NOT NULL
2371 AND document_line_id IS NULL
2372
2373 );
2374
2375 l_progress := 15;
2376 l_user_key_tbl1 := l_user_key_tbl;
2377 l_entity_name1 := l_entity_name;
2378 l_reject_reason1 := l_rejection_reason;
2379
2380 SELECT user_key ,
2381 entity ,
2382 msg
2383 BULK COLLECT INTO
2384 l_user_key_tbl ,
2385 l_entity_name ,
2386 l_rejection_reason
2387 FROM
2388 (
2389 --Reject records if ASL Process action is create and document's process
2390 --action is delete/update
2391 SELECT PAD.user_key ,
2392 'po_asl_documents_gt' AS entity ,
2393 fnd_message.get_string('PO','INVALID_PAD_ACTION') AS msg
2394 FROM po_asl_documents_gt PAD,
2395 po_approved_supplier_list_gt ASL
2396 WHERE ASL.user_key = PAD.user_key
2397 AND ASL.process_action = PO_ASL_API_PUB.g_ACTION_CREATE
2398 AND PAD.process_action <> PO_ASL_API_PUB.g_ACTION_ADD
2399
2400 UNION ALL
2401 --Reject records if ASL Process action is create and authorization's process
2402 --action is delete/update
2403 SELECT CHV.user_key ,
2404 'chv_authorizations_gt' AS entity ,
2405 fnd_message.get_string('PO','INVALID_CHV_ACTION') AS msg
2406 FROM chv_authorizations_gt CHV,
2407 po_approved_supplier_list_gt ASL
2408 WHERE ASL.user_key = CHV.user_key
2409 AND ASL.process_action = PO_ASL_API_PUB.g_ACTION_CREATE
2410 AND CHV.process_action <> PO_ASL_API_PUB.g_ACTION_ADD
2411
2412 UNION ALL
2413 --Reject records if ASL Process action is create and capacity's process
2414 --action is delete/update
2415 SELECT CAP.user_key ,
2416 'po_supplier_item_capacity_gt' AS entity ,
2417 fnd_message.get_string('PO','INVALID_CAP_ACTION') AS msg
2418 FROM po_supplier_item_capacity_gt CAP,
2419 po_approved_supplier_list_gt ASL
2420 WHERE ASL.user_key = CAP.user_key
2421 AND ASL.process_action = PO_ASL_API_PUB.g_ACTION_CREATE
2422 AND CAP.process_action <> PO_ASL_API_PUB.g_ACTION_ADD
2423
2424 UNION ALL
2425 --Reject records if ASL Process action is create and tolerance's process
2426 --action is delete/update
2427 SELECT TOL.user_key ,
2428 'po_supplier_item_tolerance_gt' AS entity ,
2429 fnd_message.get_string('PO','INVALID_TOL_ACTION') AS msg
2430 FROM po_supplier_item_tolerance_gt TOL,
2431 po_approved_supplier_list_gt ASL
2432 WHERE ASL.user_key = TOL.user_key
2433 AND ASL.process_action = PO_ASL_API_PUB.g_ACTION_CREATE
2434 AND TOL.process_action <> PO_ASL_API_PUB.g_ACTION_ADD
2435 );
2436
2437 l_progress := 20;
2438 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
2439 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
2440 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_rejection_reason;
2441
2442
2443 SELECT user_key ,
2444 entity ,
2445 msg
2446 BULK COLLECT INTO
2447 l_user_key_tbl ,
2448 l_entity_name ,
2449 l_rejection_reason
2450 FROM
2451 (
2452 SELECT user_key ,
2453 'po_asl_documents_gt' AS entity ,
2454 fnd_message.get_string('PO','USING_ORG_NOT_FOUND') AS msg
2455 FROM po_asl_documents_gt
2456 WHERE using_organization_dsp IS NOT NULL
2457 AND using_organization_id IS NULL
2458
2459 UNION ALL
2460
2461 SELECT user_key ,
2462 'chv_authorizations_gt' AS entity ,
2463 fnd_message.get_string('PO','USING_ORG_NOT_FOUND') AS msg
2464 FROM chv_authorizations_gt
2465 WHERE using_organization_dsp IS NOT NULL
2466 AND using_organization_id IS NULL
2467
2468 UNION ALL
2469
2470 SELECT user_key ,
2471 'po_supplier_item_capacity_gt' AS entity ,
2472 fnd_message.get_string('PO','USING_ORG_NOT_FOUND') AS msg
2473 FROM po_supplier_item_capacity_gt
2474 WHERE using_organization_dsp IS NOT NULL
2475 AND using_organization_id IS NULL
2476
2477 UNION ALL
2478
2479 SELECT user_key ,
2480 'po_supplier_item_tolerance_gt' AS entity,
2481 fnd_message.get_string('PO','USING_ORG_NOT_FOUND') AS msg
2482 FROM po_supplier_item_tolerance_gt
2483 WHERE using_organization_dsp IS NOT NULL
2484 AND using_organization_id IS NULL
2485 );
2486 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
2487 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
2488 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_rejection_reason;
2489
2490 l_progress := 30;
2491
2492 --Reject records if process action is other than ADD,UPDATE,DELETE
2493 SELECT user_key ,
2494 entity ,
2495 msg
2496 BULK COLLECT INTO
2497 l_user_key_tbl ,
2498 l_entity_name ,
2499 l_rejection_reason
2500 FROM
2501 (
2502 SELECT user_key ,
2503 'po_asl_documents_gt' AS entity ,
2504 fnd_message.get_string('PO','INVALID_PROCESS_ACTION') AS msg
2505 FROM po_asl_documents_gt
2506 WHERE process_action NOT IN
2507 (PO_ASL_API_PUB.g_ACTION_ADD ,
2508 PO_ASL_API_PUB.g_ACTION_UPDATE,
2509 PO_ASL_API_PUB.g_ACTION_DELETE)
2510 OR process_action IS NULL
2511
2512 UNION
2513
2514 SELECT user_key ,
2515 'chv_authorizations_gt' AS entity ,
2516 fnd_message.get_string('PO','INVALID_PROCESS_ACTION') AS msg
2517 FROM chv_authorizations_gt
2518 WHERE process_action NOT IN
2519 (PO_ASL_API_PUB.g_ACTION_ADD ,
2520 PO_ASL_API_PUB.g_ACTION_UPDATE,
2521 PO_ASL_API_PUB.g_ACTION_DELETE)
2522 OR process_action IS NULL
2523
2524 UNION
2525
2526 SELECT user_key ,
2527 'po_supplier_item_capacity_gt' AS entity ,
2528 fnd_message.get_string('PO','INVALID_PROCESS_ACTION') AS msg
2529 FROM po_supplier_item_capacity_gt
2530 WHERE process_action NOT IN
2531 (PO_ASL_API_PUB.g_ACTION_ADD ,
2532 PO_ASL_API_PUB.g_ACTION_UPDATE,
2533 PO_ASL_API_PUB.g_ACTION_DELETE)
2534 OR process_action IS NULL
2535
2536 UNION
2537
2538 SELECT user_key ,
2539 'po_supplier_item_tolerance_gt' AS entity ,
2540 fnd_message.get_string('PO','INVALID_PROCESS_ACTION') AS msg
2541 FROM po_supplier_item_tolerance_gt
2542 WHERE process_action NOT IN
2543 (PO_ASL_API_PUB.g_ACTION_ADD ,
2544 PO_ASL_API_PUB.g_ACTION_UPDATE,
2545 PO_ASL_API_PUB.g_ACTION_DELETE)
2546 OR process_action IS NULL
2547 );
2548 l_user_key_tbl1 := l_user_key_tbl1 MULTISET UNION ALL l_user_key_tbl ;
2549 l_entity_name1 := l_entity_name1 MULTISET UNION ALL l_entity_name ;
2550 l_reject_reason1 := l_reject_reason1 MULTISET UNION ALL l_rejection_reason;
2551
2552 l_progress := 50;
2553 --call po_asl_api_pvt.reject_asl_record for which the id VALUES remain NULL
2554 po_asl_api_pvt.log('collect_invalids_in_other_gt: reject count:' ||
2555 l_user_key_tbl1.Count);
2556 IF l_user_key_tbl1.Count > 0
2557 THEN
2558 po_asl_api_pvt.reject_asl_record(
2559 p_user_key_tbl => l_user_key_tbl1,
2560 p_rejection_reason => l_reject_reason1,
2561 p_entity_name => l_entity_name1,
2562 p_session_key => g_session_key,
2563 x_return_status => x_return_status,
2564 x_return_msg => x_return_msg
2565 );
2566 END IF;
2567 l_progress := 100;
2568 x_return_status := FND_API.G_RET_STS_SUCCESS;
2569 po_asl_api_pvt.log('END ::: collect_invalids_in_other_gt');
2570 EXCEPTION
2571
2572 WHEN OTHERS THEN
2573
2574 po_asl_api_pvt.log('collect_invalids_in_other_gt when others exception '
2575 || l_progress || ';' || SQLERRM);
2576 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2577 x_return_msg := SQLERRM;
2578
2579 END collect_invalids_in_other_gt;
2580
2581 --------------------------------------------------------------------------------
2582 --START of comments
2583
2584 --NAME: get_doc_header
2585
2586 --FUNCTION:
2587 -- Retrieves the document header id based on item and category
2588
2589 --PARAMETERS:
2590 --IN:
2591 -- p_user_key_tbl po_tbl_number
2592 -- p_entity_name po_tbl_varchar30
2593 -- p_rejection_reason po_tbl_varchar2000
2594
2595 --OUT:
2596 -- x_return_status VARCHAR2
2597 -- x_return_msg VARCHAR2
2598
2599 --END of comments
2600 --------------------------------------------------------------------------------
2601
2602 FUNCTION get_doc_header(
2603 p_user_key IN NUMBER
2604 , p_doc_type IN VARCHAR2
2605 , p_using_org_id IN NUMBER
2606 , p_segment IN VARCHAR2
2607 )
2608 RETURN NUMBER
2609 IS
2610 l_progress NUMBER := 0;
2611 l_header_id NUMBER;
2612 l_item_id NUMBER;
2613 l_category_id NUMBER;
2614 l_vendor_id NUMBER;
2615 l_vendor_site_id NUMBER;
2616 l_owning_org_id NUMBER;
2617
2618
2619 BEGIN
2620 po_asl_api_pvt.log('START ::: get_doc_header');
2621 po_asl_api_pvt.log('p_user_key:' || p_user_key);
2622 po_asl_api_pvt.log('p_doc_type:' || p_doc_type);
2623 po_asl_api_pvt.log('p_using_org_id:' || p_using_org_id);
2624 po_asl_api_pvt.log('p_segment:' || p_segment);
2625 SELECT ASL.item_id ,
2626 ASL.category_id ,
2627 ASL.vendor_id ,
2628 ASL.vendor_site_id ,
2629 ASL.owning_organization_id
2630 INTO l_item_id ,
2631 l_category_id ,
2632 l_vendor_id ,
2633 l_vendor_site_id ,
2634 l_owning_org_id
2635 FROM po_approved_supplier_list_gt ASL
2636 WHERE ASL.user_key = p_user_key
2637 AND ROWNUM < 2;
2638
2639 l_progress := 20;
2640 po_asl_api_pvt.log('l_item_id:' || l_item_id);
2641 po_asl_api_pvt.log('l_category_id:' || l_category_id);
2642 po_asl_api_pvt.log('l_vendor_id:' || l_vendor_id);
2643 po_asl_api_pvt.log('l_vendor_site_id:' || l_vendor_site_id);
2644 po_asl_api_pvt.log('l_owning_org_id:' || l_owning_org_id);
2645 IF l_item_id IS NOT NULL
2646 THEN
2647 po_asl_api_pvt.log('inside if');
2648 SELECT poh.po_header_id
2649 INTO l_header_id
2650 FROM po_lookup_codes plc,
2651 po_headers_all poh,
2652 hr_operating_units hrou
2653 WHERE poh.org_id = hrou.organization_id(+)
2654 AND poh.type_lookup_code = p_doc_type
2655 AND ( Nvl(poh.global_agreement_flag, 'N') = 'Y'
2656 OR poh.org_id = l_owning_org_id)
2657 AND ( ( poh.type_lookup_code = 'BLANKET'
2658 AND poh.approved_flag = 'Y'
2659 AND Nvl(poh.cancel_flag, 'N') = 'N'
2660 AND Nvl(poh.frozen_flag, 'N') = 'N'
2661 AND Nvl(poh.closed_code, 'OPEN') <> 'FINALLY CLOSED' )
2662 OR ( poh.type_lookup_code = 'CONTRACT'
2663 AND ( ( Nvl(fnd_profile.Value(
2664 'ALLOW_REFERENCING_CPA_UNDER_AMENDMENT'),
2665 'N') =
2666 'Y'
2667 AND poh.approved_date IS NOT NULL )
2668 OR Nvl (poh.approved_flag, 'N') = 'Y' )
2669 AND Nvl(poh.cancel_flag, 'N') = 'N'
2670 AND Nvl(poh.frozen_flag, 'N') = 'N'
2671 AND Nvl(poh.closed_code, 'OPEN') = 'OPEN' )
2672 OR ( poh.type_lookup_code = 'QUOTATION'
2673 AND ( poh.status_lookup_code = 'A' )
2674 AND ( poh.approval_required_flag = 'Y' )
2675 AND ( EXISTS (SELECT 'x'
2676 FROM po_quotation_approvals poqa,
2677 po_line_locations_all poll,
2678 po_lines_all pol
2679 WHERE poqa.approval_type IS NOT NULL
2680 AND poqa.line_location_id =
2681 poll.line_location_id
2682 AND poll.po_line_id = pol.po_line_id
2683 AND pol.item_id = l_item_id
2684 AND pol.po_header_id = poh.po_header_id
2685 AND Trunc(SYSDATE) <=
2686 Nvl(poh.end_date, SYSDATE + 1))
2687 OR NOT EXISTS (SELECT 'no shipments exists'
2688 FROM po_line_locations_all poll,
2689 po_lines_all pol
2690 WHERE poll.po_line_id = pol.po_line_id
2691 AND pol.item_id = l_item_id
2692 AND pol.po_header_id =
2693 poh.po_header_id
2694 ) )
2695 )
2696 OR ( poh.type_lookup_code = 'QUOTATION'
2697 AND ( poh.status_lookup_code = 'A' )
2698 AND ( poh.approval_required_flag = 'N' )
2699 AND ( EXISTS (SELECT 'x'
2700 FROM po_line_locations_all poll,
2701 po_lines_all pol
2702 WHERE poll.po_line_id = pol.po_line_id
2703 AND pol.item_id = l_item_id
2704 AND pol.po_header_id = poh.po_header_id
2705 AND Trunc(SYSDATE) <=
2706 Nvl(poh.end_date, SYSDATE + 1))
2707 OR NOT EXISTS (SELECT 'no shipments exists'
2708 FROM po_line_locations_all poll,
2709 po_lines_all pol
2710 WHERE poll.po_line_id = pol.po_line_id
2711 AND pol.item_id = l_item_id
2712 AND pol.po_header_id =
2713 poh.po_header_id
2714 ) )
2715 ) )
2716 AND poh.vendor_id = l_vendor_id
2717 AND ( Nvl (poh.vendor_site_id, -1) = Nvl (l_vendor_site_id,
2718 Decode (poh.vendor_site_id, NULL, -1,
2719 poh.vendor_site_id)
2720 )
2721 OR ( Nvl (poh.global_agreement_flag, 'N') = 'Y'
2722 AND l_vendor_site_id IS NOT NULL
2723 AND EXISTS (SELECT 'vendor site id matches'
2724 FROM po_ga_org_assignments poga
2725 WHERE poh.po_header_id = poga.po_header_id
2726 AND poga.vendor_site_id =
2727 Decode(Nvl(poh.enable_all_sites, 'N'), 'N',
2728 l_vendor_site_id,
2729 poga.vendor_site_id)
2730 AND poga.enabled_flag = 'Y') ) )
2731 AND ( poh.type_lookup_code = 'CONTRACT'
2732 OR EXISTS (SELECT 'x'
2733 FROM po_lines_all pol
2734 WHERE pol.po_header_id = poh.po_header_id
2735 AND pol.item_id = l_item_id
2736 AND Nvl(pol.cancel_flag, 'N') = 'N') )
2737 AND Trunc(SYSDATE) <= Nvl(poh.end_date, SYSDATE + 1)
2738 AND Decode(poh.type_lookup_code, 'QUOTATION', poh.status_lookup_code,
2739 'BLANKET', poh.authorization_status,
2740 'CONTRACT', poh.authorization_status) =
2741 plc.lookup_code(+)
2742 AND Decode(poh.type_lookup_code, 'QUOTATION', 'RFQ/QUOTE STATUS',
2743 'BLANKET', 'AUTHORIZATION STATUS',
2744 'CONTRACT', 'AUTHORIZATION STATUS') =
2745 plc.lookup_type(+)
2746 AND poh.segment1 = p_segment;
2747 ELSIF l_category_id IS NOT NULL
2748 THEN
2749 po_asl_api_pvt.log('inside else if');
2750 SELECT poh.po_header_id
2751 INTO l_header_id
2752 FROM po_lookup_codes plc,
2753 po_headers_all poh,
2754 hr_operating_units hrou
2755 WHERE poh.org_id = hrou.organization_id(+)
2756 AND poh.type_lookup_code = p_doc_type
2757 AND ( Nvl(poh.global_agreement_flag, 'N') = 'Y'
2758 OR poh.org_id = l_owning_org_id)
2759 AND ( ( poh.type_lookup_code = 'BLANKET'
2760 AND poh.approved_flag = 'Y'
2761 AND Nvl(poh.cancel_flag, 'N') = 'N'
2762 AND Nvl(poh.frozen_flag, 'N') = 'N'
2763 AND Nvl(poh.closed_code, 'OPEN') <> 'FINALLY CLOSED' )
2764 OR ( poh.type_lookup_code = 'CONTRACT'
2765 AND ( ( Nvl(fnd_profile.Value(
2766 'ALLOW_REFERENCING_CPA_UNDER_AMENDMENT'),
2767 'N') =
2768 'Y'
2769 AND poh.approved_date IS NOT NULL )
2770 OR Nvl(poh.approved_flag, 'N') = 'Y' )
2771 AND Nvl(poh.cancel_flag, 'N') = 'N'
2772 AND Nvl(poh.frozen_flag, 'N') = 'N'
2773 AND Nvl(poh.closed_code, 'OPEN') = 'OPEN' )
2774 OR ( poh.type_lookup_code = 'QUOTATION'
2775 AND ( poh.status_lookup_code = 'A' )
2776 AND ( poh.approval_required_flag = 'Y' )
2777 AND ( EXISTS (SELECT 'x'
2778 FROM po_quotation_approvals poqa,
2779 po_line_locations_all poll,
2780 po_lines_all pol
2781 WHERE poqa.approval_type IS NOT NULL
2782 AND poqa.line_location_id =
2783 poll.line_location_id
2784 AND poll.po_line_id = pol.po_line_id
2785 AND pol.category_id = l_category_id
2786 AND pol.po_header_id = poh.po_header_id
2787 AND Trunc(SYSDATE) <=
2788 Nvl(poh.end_date, SYSDATE + 1))
2789 OR NOT EXISTS (SELECT 'no shipments exists'
2790 FROM po_line_locations_all poll,
2791 po_lines_all pol
2792 WHERE poll.po_line_id = pol.po_line_id
2793 AND pol.category_id =
2794 l_category_id
2795 AND pol.po_header_id =
2796 poh.po_header_id
2797 ) )
2798 )
2799 OR ( poh.type_lookup_code = 'QUOTATION'
2800 AND ( poh.status_lookup_code = 'A' )
2801 AND ( poh.approval_required_flag = 'N' )
2802 AND ( EXISTS (SELECT 'x'
2803 FROM po_line_locations_all poll,
2804 po_lines_all pol
2805 WHERE poll.po_line_id = pol.po_line_id
2806 AND pol.category_id = l_category_id
2807 AND pol.po_header_id = poh.po_header_id
2808 AND Trunc(SYSDATE) <=
2809 Nvl(poh.end_date, SYSDATE + 1))
2810 OR NOT EXISTS (SELECT 'no shipments exists'
2811 FROM po_line_locations_all poll,
2812 po_lines_all pol
2813 WHERE poll.po_line_id = pol.po_line_id
2814 AND pol.category_id =
2815 l_category_id
2816 AND pol.po_header_id =
2817 poh.po_header_id
2818 ) )
2819 ) )
2820 AND poh.vendor_id = l_vendor_id
2821 AND ( Nvl (poh.vendor_site_id, -1) = Nvl (l_vendor_site_id,
2822 Decode (poh.vendor_site_id, NULL, -1,
2823 poh.vendor_site_id))
2824 OR ( Nvl (poh.global_agreement_flag, 'N') = 'Y'
2825 AND l_vendor_site_id IS NOT NULL
2826 AND EXISTS (SELECT 'vendor site id matches'
2827 FROM po_ga_org_assignments poga
2828 WHERE poh.po_header_id = poga.po_header_id
2829 AND poga.vendor_site_id =
2830 Decode(Nvl(poh.enable_all_sites, 'N'), 'Y',
2831 poga.vendor_site_id,
2832 l_vendor_site_id)
2833 AND poga.enabled_flag = 'Y') ) )
2834 AND ( poh.type_lookup_code = 'CONTRACT'
2835 OR EXISTS (SELECT 'x'
2836 FROM po_lines_all pol
2837 WHERE pol.po_header_id = poh.po_header_id
2838 AND pol.category_id = l_category_id
2839 AND Nvl(pol.cancel_flag, 'N') = 'N') )
2840 AND Trunc(SYSDATE) <= Nvl(poh.end_date, SYSDATE + 1)
2841 AND Decode(poh.type_lookup_code, 'QUOTATION', poh.status_lookup_code,
2842 'BLANKET', poh.authorization_status,
2843 'CONTRACT', poh.authorization_status) =
2844 plc.lookup_code(+)
2845 AND Decode(poh.type_lookup_code, 'QUOTATION', 'RFQ/QUOTE STATUS',
2846 'BLANKET', 'AUTHORIZATION STATUS',
2847 'CONTRACT', 'AUTHORIZATION STATUS') =
2848 plc.lookup_type(+)
2849 AND poh.segment1 = p_segment;
2850 END IF;
2851 po_asl_api_pvt.log('l_header_id ::: ' || l_header_id);
2852 po_asl_api_pvt.log('END ::: get_doc_header');
2853 RETURN l_header_id;
2854 EXCEPTION
2855 WHEN OTHERS THEN
2856
2857 po_asl_api_pvt.log('get_doc_header when others exception '
2858 || l_progress || ';' || SQLERRM);
2859 RETURN NULL;
2860
2861 END get_doc_header;
2862
2863 --------------------------------------------------------------------------------
2864 --START of comments
2865
2866 --NAME: get_doc_line_id
2867
2868 --FUNCTION:
2869 -- Retrieves the document line id based on headerId, item and category
2870
2871 --PARAMETERS:
2872 --IN:
2873 -- p_user_key_tbl po_tbl_number
2874 -- p_entity_name po_tbl_varchar30
2875 -- p_rejection_reason po_tbl_varchar2000
2876
2877 --OUT:
2878 -- x_return_status VARCHAR2
2879 -- x_return_msg VARCHAR2
2880
2881 --END of comments
2882 --------------------------------------------------------------------------------
2883
2884 FUNCTION get_doc_line_id(
2885 p_user_key IN NUMBER
2886 , p_header_id IN VARCHAR2
2887 , p_using_org_id IN NUMBER
2888 , p_line_num IN NUMBER
2889 )
2890 RETURN NUMBER
2891 IS
2892 l_progress NUMBER := 0;
2893 l_line_id NUMBER;
2894 l_item_id NUMBER;
2895 l_category_id NUMBER;
2896
2897 BEGIN
2898 po_asl_api_pvt.log('START ::: get_doc_line_id');
2899 po_asl_api_pvt.log('p_user_key:' || p_user_key);
2900 po_asl_api_pvt.log('p_header_id:' || p_header_id);
2901 po_asl_api_pvt.log('p_using_org_id:' || p_using_org_id);
2902
2903 SELECT ASL.item_id ,
2904 ASL.category_id
2905 INTO l_item_id ,
2906 l_category_id
2907 FROM po_approved_supplier_list_gt ASL
2908 WHERE ASL.user_key = p_user_key
2909 AND ROWNUM < 2;
2910
2911 po_asl_api_pvt.log('l_item_id :' || l_item_id);
2912 po_asl_api_pvt.log('l_category_id :' || l_category_id);
2913
2914 l_progress := 20;
2915
2916 IF l_item_id IS NOT NULL
2917 THEN
2918 po_asl_api_pvt.log('inside if :');
2919 SELECT pol.po_line_id
2920 INTO l_line_id
2921 FROM po_lines_all pol,
2922 fnd_currencies_vl fnc,
2923 po_headers_all poh
2924 WHERE pol.po_header_id = p_header_id
2925 AND pol.item_id = l_item_id
2926 AND pol.po_header_id = poh.po_header_id
2927 AND Nvl(pol.cancel_flag, 'N') = 'N'
2928 AND ( ( poh.type_lookup_code = 'BLANKET' )
2929 OR ( poh.type_lookup_code = 'QUOTATION'
2930 AND ( poh.approval_required_flag = 'Y' )
2931 AND ( EXISTS (SELECT 1
2932 FROM po_quotation_approvals poqa,
2933 po_line_locations_all poll
2934 WHERE poqa.approval_type IS NOT NULL
2935 AND poqa.line_location_id =
2936 poll.line_location_id
2937 AND poll.po_line_id = pol.po_line_id
2938 AND pol.item_id = l_item_id
2939 AND pol.po_header_id = poh.po_header_id
2940 AND SYSDATE <
2941 Nvl(poh.end_date, SYSDATE + 1))
2942 OR NOT EXISTS (SELECT 'no shipments exists'
2943 FROM po_line_locations_all poll
2944 WHERE poll.po_line_id = pol.po_line_id
2945 AND pol.item_id = l_item_id
2946 AND pol.po_header_id =
2947 poh.po_header_id
2948 ) )
2949 )
2950 OR ( poh.type_lookup_code = 'QUOTATION'
2951 AND ( poh.approval_required_flag = 'N' )
2952 AND ( EXISTS (SELECT 1
2953 FROM po_line_locations_all poll
2954 WHERE poll.po_line_id = pol.po_line_id
2955 AND pol.item_id = l_item_id
2956 AND pol.po_header_id = poh.po_header_id
2957 AND SYSDATE <
2958 Nvl(poh.end_date, SYSDATE + 1))
2959 OR NOT EXISTS (SELECT 'no shipments exists'
2960 FROM po_line_locations_all poll
2961 WHERE poll.po_line_id = pol.po_line_id
2962 AND pol.item_id = l_item_id
2963 AND pol.po_header_id =
2964 poh.po_header_id
2965 ) )
2966 ) )
2967 AND poh.currency_code = fnc.currency_code
2968 AND pol.line_num = p_line_num;
2969 ELSIF l_category_id IS NOT NULL
2970 THEN
2971 po_asl_api_pvt.log('inside else :');
2972 SELECT pol.po_line_id
2973 INTO l_line_id
2974 FROM po_lines_all pol,
2975 fnd_currencies_vl fnc,
2976 po_headers_all poh
2977 WHERE pol.po_header_id = p_header_id
2978 AND pol.category_id = l_category_id
2979 AND pol.po_header_id = poh.po_header_id
2980 AND Nvl(pol.cancel_flag, 'N') = 'N'
2981 AND ( ( poh.type_lookup_code = 'BLANKET' )
2982 OR ( poh.type_lookup_code = 'QUOTATION'
2983 AND ( poh.approval_required_flag = 'Y' )
2984 AND ( EXISTS (SELECT *
2985 FROM po_quotation_approvals poqa,
2986 po_line_locations_all poll
2987 WHERE poqa.approval_type IS NOT NULL
2988 AND poqa.line_location_id =
2989 poll.line_location_id
2990 AND poll.po_line_id = pol.po_line_id
2991 AND pol.category_id = l_category_id
2992 AND pol.po_header_id = poh.po_header_id
2993 AND SYSDATE < Nvl(poh.end_date, SYSDATE
2994 + 1))
2995 OR NOT EXISTS (SELECT 'no shipments exists'
2996 FROM po_line_locations_all poll
2997 WHERE poll.po_line_id = pol.po_line_id
2998 AND pol.category_id =
2999 l_category_id
3000 AND pol.po_header_id =
3001 poh.po_header_id
3002 ) )
3003 )
3004 OR ( poh.type_lookup_code = 'QUOTATION'
3005 AND ( poh.approval_required_flag = 'N' )
3006 AND ( EXISTS (SELECT *
3007 FROM po_line_locations_all poll
3008 WHERE poll.po_line_id = pol.po_line_id
3009 AND pol.category_id = l_category_id
3010 AND pol.po_header_id = poh.po_header_id
3011 AND SYSDATE < Nvl(poh.end_date, SYSDATE
3012 + 1))
3013 OR NOT EXISTS (SELECT 'no shipments exists'
3014 FROM po_line_locations_all poll
3015 WHERE poll.po_line_id = pol.po_line_id
3016 AND pol.category_id =
3017 l_category_id
3018 AND pol.po_header_id =
3019 poh.po_header_id
3020 ) )
3021 ) )
3022 AND poh.currency_code = fnc.currency_code
3023 AND pol.line_num = p_line_num;
3024
3025 END IF;
3026
3027 l_progress := 100;
3028 po_asl_api_pvt.log('END ::: get_doc_line_id');
3029 RETURN l_line_id;
3030 EXCEPTION
3031 WHEN OTHERS THEN
3032
3033 po_asl_api_pvt.log('get_doc_line_id when others exception '
3034 || l_progress || ';' || SQLERRM);
3035 RETURN NULL;
3036
3037 END get_doc_line_id;
3038
3039 END po_asl_api_pub;