[Home] [Help]
PACKAGE BODY: APPS.PO_PDOI_UTL
Source
1 PACKAGE BODY PO_PDOI_UTL AS
2 /* $Header: PO_PDOI_UTL.plb 120.9 2006/09/15 22:26:54 jinwang noship $ */
3
4 d_pkg_name CONSTANT VARCHAR2(50) :=
5 PO_LOG.get_package_base('PO_PDOI_UTL');
6
7 -------------------------------------------------------
8 ----------- PRIVATE PROCEDURES PROTOTYPE --------------
9 -------------------------------------------------------
10
11 PROCEDURE set_doc_has_errors
12 ( p_intf_header_id_tbl IN PO_TBL_NUMBER
13 );
14
15 -------------------------------------------------------
16 -------------- PUBLIC PROCEDURES ----------------------
17 -------------------------------------------------------
18
19 -----------------------------------------------------------------------
20 --Start of Comments
21 --Name: remove_session_gt_records
22 --Function:
23 -- Remove all records from PO_SESSION_GT based on key.
24 --Parameters:
25 --IN:
26 --p_key
27 -- Value of the key
28 --IN OUT:
29 --OUT:
30 --End of Comments
31 ------------------------------------------------------------------------
32 PROCEDURE remove_session_gt_records
33 ( p_key IN NUMBER
34 ) IS
35
36 d_api_name CONSTANT VARCHAR2(30) := 'remove_session_gt_records';
37 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
38 d_position NUMBER;
39
40 BEGIN
41 d_position := 0;
42
43 IF (PO_LOG.d_proc) THEN
44 PO_LOG.proc_begin (d_module);
45 END IF;
46
47 DELETE FROM PO_SESSION_GT
48 WHERE key = p_key;
49
50 IF (PO_LOG.d_proc) THEN
51 PO_LOG.proc_end (d_module);
52 END IF;
53
54 EXCEPTION
55 WHEN OTHERS THEN
56 PO_MESSAGE_S.add_exc_msg
57 ( p_pkg_name => d_pkg_name,
58 p_procedure_name => d_api_name || '.' || d_position
59 );
60 RAISE;
61 END remove_session_gt_records;
62
63 -----------------------------------------------------------------------
64 --Start of Comments
65 --Name: commit_work
66 --Function:
67 -- Issues a commit if PO_PDOI_PARAMS.g_request.commit_work is
68 -- FND_API.G_TRUE
69 --Parameters:
70 --IN:
71 --IN OUT:
72 --OUT:
73 --End of Comments
74 ------------------------------------------------------------------------
75 PROCEDURE commit_work IS
76
77 d_api_name CONSTANT VARCHAR2(30) := 'commit_work';
78 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
79 d_position NUMBER;
80
81 BEGIN
82 d_position := 0;
83
84 IF (PO_LOG.d_proc) THEN
85 PO_LOG.proc_begin (d_module);
86 END IF;
87
88 IF (PO_PDOI_PARAMS.g_request.commit_work = FND_API.G_TRUE) THEN
89 COMMIT;
90 END IF;
91
92 IF (PO_LOG.d_proc) THEN
93 PO_LOG.proc_end(d_module);
94 END IF;
95
96 EXCEPTION
97 WHEN OTHERS THEN
98 PO_MESSAGE_S.add_exc_msg
99 ( p_pkg_name => d_pkg_name,
100 p_procedure_name => d_api_name || '.' || d_position
101 );
102 RAISE;
103 END commit_work;
104
105
106 -----------------------------------------------------------------------
107 --Start of Comments
108 --Name: get_next_batch_id
109 --Function:
110 -- Get the next batch id to be inserted into po_headers_interface.
111 -- It's done by getting max (batch_id) + 1 from po_headers_interface
112 --Parameters:
113 --IN:
114 --IN OUT:
115 --OUT:
116 --Returns:
117 --End of Comments
118 ------------------------------------------------------------------------
119 FUNCTION get_next_batch_id RETURN NUMBER
120 IS
121
122 d_api_name CONSTANT VARCHAR2(30) := 'get_next_batch_id';
123 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
124 d_position NUMBER;
125
126 l_batch_id PO_HEADERS_INTERFACE.batch_id%TYPE;
127 BEGIN
128 d_position := 0;
129
130 IF (PO_LOG.d_proc) THEN
131 PO_LOG.proc_begin (d_module);
132 END IF;
133
134 SELECT NVL(MAX(batch_id), 0) + 1
135 INTO l_batch_id
136 FROM po_headers_interface;
137
138 IF (PO_LOG.d_stmt) THEN
139 PO_LOG.stmt(d_module, d_position, 'batch_id', l_batch_id);
140 END IF;
141
142 RETURN l_batch_id;
143
144 EXCEPTION
145 WHEN OTHERS THEN
146 PO_MESSAGE_S.add_exc_msg
147 ( p_pkg_name => d_pkg_name,
148 p_procedure_name => d_api_name || '.' || d_position
149 );
150 RAISE;
151 END get_next_batch_id;
152
153 -----------------------------------------------------------------------
154 --Start of Comments
155 --Name: reject_headers_intf
156 --Function:
157 -- For all ids passed in, reject the corresponding records in headers
158 -- interface
159 --Parameters:
160 --IN:
161 --p_id_param_type
162 -- Type of the id. Possible values:
163 -- PO_PDOI_CONSTANTS.g_INTERFACE_HEADER_ID
164 --p_id_tbl
165 -- Table of ids
166 --p_cascade
167 -- FND_API.G_TRUE if rejection should be propagated to the lower level
168 -- FND_API.G_FALSE otherwise
169 --IN OUT:
170 --OUT:
171 --End of Comments
172 ------------------------------------------------------------------------
173 PROCEDURE reject_headers_intf
174 ( p_id_param_type IN VARCHAR2,
175 p_id_tbl IN PO_TBL_NUMBER,
176 p_cascade IN VARCHAR2
177 ) IS
178
179 d_api_name CONSTANT VARCHAR2(30) := 'reject_headers_intf';
180 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
181 d_position NUMBER;
182
183 BEGIN
184 d_position := 0;
185
186 IF (PO_LOG.d_proc) THEN
187 PO_LOG.proc_begin (d_module);
188 END IF;
189
190 IF (p_id_tbl IS NULL OR p_id_tbl.COUNT = 0) THEN
191 d_position := 10;
192 RETURN;
193 END IF;
194
195 IF (p_id_param_type = PO_PDOI_CONSTANTS.g_INTERFACE_HEADER_ID) THEN
196 d_position := 20;
197 FORALL i IN 1..p_id_tbl.COUNT
198 UPDATE po_headers_interface
199 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED,
200 processing_id = -PO_PDOI_PARAMS.g_processing_id
201 WHERE interface_header_id = p_id_tbl(i)
202 AND processing_id = PO_PDOI_PARAMS.g_processing_id;
203 ELSE
204 d_position := 30;
205 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
206 END IF;
207
208 IF (p_cascade = FND_API.G_TRUE) THEN
209 d_position := 40;
210 reject_lines_intf
211 ( p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_HEADER_ID
212 , p_id_tbl => p_id_tbl
213 , p_cascade => FND_API.G_TRUE
214 );
215 END IF;
216
217 set_doc_has_errors
218 ( p_intf_header_id_tbl => p_id_tbl
219 );
220
221 IF (PO_LOG.d_proc) THEN
222 PO_LOG.proc_end(d_module);
223 END IF;
224
225 EXCEPTION
226 WHEN OTHERS THEN
227 PO_MESSAGE_S.add_exc_msg
228 ( p_pkg_name => d_pkg_name,
229 p_procedure_name => d_api_name || '.' || d_position
230 );
231 RAISE;
232 END reject_headers_intf;
233
234
235 -----------------------------------------------------------------------
236 --Start of Comments
237 --Name: reject_lines_intf
238 --Function:
239 -- For all ids passed in, reject the corresponding records in lines
240 -- interface
241 --Parameters:
242 --IN:
243 --p_id_param_type
244 -- Type of the id. Possible values:
245 -- PO_PDOI_CONSTANTS.g_INTERFACE_HEADER_ID
246 -- PO_PDOI_CONSTANTS.g_INTERFACE_LINE_ID
247 --p_id_tbl
248 -- Table of ids
249 --p_cascade
250 -- FND_API.G_TRUE if rejection should be propagated to the lower level
251 -- FND_API.G_FALSE otherwise
252 --IN OUT:
253 --OUT:
254 --End of Comments
255 ------------------------------------------------------------------------
256 PROCEDURE reject_lines_intf
257 ( p_id_param_type IN VARCHAR2,
258 p_id_tbl IN PO_TBL_NUMBER,
259 p_cascade IN VARCHAR2
260 ) IS
261
262 d_api_name CONSTANT VARCHAR2(30) := 'reject_lines_intf';
263 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
264 d_position NUMBER;
265
266 l_intf_line_id_tbl PO_TBL_NUMBER;
267 l_intf_header_id_tbl PO_TBL_NUMBER;
268 BEGIN
269 d_position := 0;
270
271 IF (PO_LOG.d_proc) THEN
272 PO_LOG.proc_begin (d_module);
273 END IF;
274
275 IF (p_id_tbl IS NULL OR p_id_tbl.COUNT = 0) THEN
276 d_position := 10;
277 RETURN;
278 END IF;
279
280 IF (p_id_param_type = PO_PDOI_CONSTANTS.g_INTERFACE_HEADER_ID) THEN
281 d_position := 20;
282
283 FORALL i IN 1..p_id_tbl.COUNT
284 UPDATE po_lines_interface
285 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED,
286 processing_id = -PO_PDOI_PARAMS.g_processing_id
287 WHERE interface_header_id = p_id_tbl(i)
288 AND processing_id = PO_PDOI_PARAMS.g_processing_id
289 RETURNING interface_line_id, interface_header_id
290 BULK COLLECT INTO l_intf_line_id_tbl, l_intf_header_id_tbl;
291
292 ELSIF (p_id_param_type = PO_PDOI_CONSTANTS.g_INTERFACE_LINE_ID) THEN
293 d_position := 30;
294
295 FORALL i IN 1..p_id_tbl.COUNT
296 UPDATE po_lines_interface
297 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED,
298 processing_id = -PO_PDOI_PARAMS.g_processing_id
299 WHERE interface_line_id = p_id_tbl(i)
300 AND processing_id = PO_PDOI_PARAMS.g_processing_id
301 RETURNING interface_line_id, interface_header_id
302 BULK COLLECT INTO l_intf_line_id_tbl, l_intf_header_id_tbl;
303 ELSE
304
305 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
306 END IF;
307
308 set_doc_has_errors
309 ( p_intf_header_id_tbl => l_intf_header_id_tbl
310 );
311
312 IF (p_cascade = FND_API.G_TRUE) THEN
313 d_position := 40;
314 reject_line_locations_intf
315 ( p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_LINE_ID
316 , p_id_tbl => l_intf_line_id_tbl
317 , p_cascade => FND_API.G_TRUE);
318
319 d_position := 50;
320 reject_price_diff_intf
321 ( p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_LINE_ID
322 , p_id_tbl => l_intf_line_id_tbl);
323
324 IF (PO_PDOI_PARAMS.g_request.document_type <>
325 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
326
327 d_position := 60;
328 reject_attr_values_intf
329 ( p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_LINE_ID
330 , p_id_tbl => l_intf_line_id_tbl);
331
332 d_position := 70;
333 reject_attr_values_tlp_intf
334 ( p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_LINE_ID
335 , p_id_tbl => l_intf_line_id_tbl);
336 END IF;
337 END IF;
338
339 IF (PO_LOG.d_proc) THEN
340 PO_LOG.proc_end(d_module);
341 END IF;
342
343 EXCEPTION
344 WHEN OTHERS THEN
345 PO_MESSAGE_S.add_exc_msg
346 ( p_pkg_name => d_pkg_name,
347 p_procedure_name => d_api_name || '.' || d_position
348 );
349 RAISE;
350 END reject_lines_intf;
351
352
353 -----------------------------------------------------------------------
354 --Start of Comments
355 --Name: reject_line_locations_intf
356 --Function:
357 -- For all ids passed in, reject the corresponding records in line loc
358 -- interface
359 --Parameters:
360 --IN:
361 --p_id_param_type
362 -- Type of the id. Possible values:
363 -- PO_PDOI_CONSTANTS.g_INTERFACE_LINE_ID
364 -- PO_PDOI_CONSTANTS.g_INTERFACE_LINE_LOCATION_ID
365 --p_id_tbl
366 -- Table of ids
367 --p_cascade
368 -- FND_API.G_TRUE if rejection should be propagated to the lower level
369 -- FND_API.G_FALSE otherwise
370 --IN OUT:
371 --OUT:
372 --End of Comments
373 ------------------------------------------------------------------------
374 PROCEDURE reject_line_locations_intf
375 ( p_id_param_type IN VARCHAR2,
376 p_id_tbl IN PO_TBL_NUMBER,
377 p_cascade IN VARCHAR2
378 ) IS
379
380 d_api_name CONSTANT VARCHAR2(30) := 'reject_line_locations_intf';
381 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
382 d_position NUMBER;
383
384 l_intf_line_loc_id_tbl PO_TBL_NUMBER;
385 l_intf_header_id_tbl PO_TBL_NUMBER;
386
387 BEGIN
388 d_position := 0;
389
390 IF (PO_LOG.d_proc) THEN
391 PO_LOG.proc_begin (d_module);
392 END IF;
393
394 IF (p_id_tbl IS NULL OR p_id_tbl.COUNT = 0) THEN
395 d_position := 10;
396 RETURN;
397 END IF;
398
399 IF (p_id_param_type = PO_PDOI_CONSTANTS.g_INTERFACE_LINE_ID) THEN
400 d_position := 20;
401 FORALL i IN 1..p_id_tbl.COUNT
402 UPDATE po_line_locations_interface
403 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED,
404 processing_id = -PO_PDOI_PARAMS.g_processing_id
405 WHERE interface_line_id = p_id_tbl(i)
406 AND processing_id = PO_PDOI_PARAMS.g_processing_id
407 AND NVL(process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
408 <> PO_PDOI_CONSTANTS.g_PROCESS_CODE_OBSOLETE
409 RETURNING interface_line_location_id, interface_header_id
410 BULK COLLECT INTO l_intf_line_loc_id_tbl, l_intf_header_id_tbl;
411
412 ELSIF (p_id_param_type = PO_PDOI_CONSTANTS.g_INTERFACE_LINE_LOCATION_ID) THEN
413 d_position := 30;
414 FORALL i IN 1..p_id_tbl.COUNT
415 UPDATE po_line_locations_interface
416 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED,
417 processing_id = -PO_PDOI_PARAMS.g_processing_id
418 WHERE interface_line_location_id = p_id_tbl(i)
419 AND processing_id = PO_PDOI_PARAMS.g_processing_id
420 AND NVL(process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
421 <> PO_PDOI_CONSTANTS.g_PROCESS_CODE_OBSOLETE
422 RETURNING interface_line_location_id, interface_header_id
423 BULK COLLECT INTO l_intf_line_loc_id_tbl, l_intf_header_id_tbl;
424 ELSE
425 d_position := 40;
426 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
427 END IF;
428
429 d_position := 50;
430
431 set_doc_has_errors
432 ( p_intf_header_id_tbl => l_intf_header_id_tbl
433 );
434
435 IF (p_cascade = FND_API.G_TRUE) THEN
436 IF (PO_PDOI_PARAMS.g_request.document_type =
437 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
438
439 d_position := 60;
440 reject_distributions_intf
441 ( p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_LINE_LOCATION_ID
442 , p_id_tbl => l_intf_line_loc_id_tbl);
443 END IF;
444 END IF;
445
446 IF (PO_LOG.d_proc) THEN
447 PO_LOG.proc_end(d_module);
448 END IF;
449
450 EXCEPTION
451 WHEN OTHERS THEN
452 PO_MESSAGE_S.add_exc_msg
453 ( p_pkg_name => d_pkg_name,
454 p_procedure_name => d_api_name || '.' || d_position
455 );
456 RAISE;
457 END reject_line_locations_intf;
458
459
460 -----------------------------------------------------------------------
461 --Start of Comments
462 --Name: reject_distributions_intf
463 --Function:
464 -- For all ids passed in, reject the corresponding records in distributions
465 -- interface
466 --Parameters:
467 --IN:
468 --p_id_param_type
469 -- Type of the id. Possible values:
470 -- PO_PDOI_CONSTANTS.g_INTERFACE_LINE_LOCATION_ID
471 -- PO_PDOI_CONSTANTS.g_INTERFACE_DISTRIBUTION_ID
472 --p_id_tbl
473 -- Table of ids
474 --IN OUT:
475 --OUT:
476 --End of Comments
477 ------------------------------------------------------------------------
478 PROCEDURE reject_distributions_intf
479 ( p_id_param_type IN VARCHAR2,
480 p_id_tbl IN PO_TBL_NUMBER
481 ) IS
482
483 d_api_name CONSTANT VARCHAR2(30) := 'reject_distributions_intf';
484 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
485 d_position NUMBER;
486
487 l_intf_header_id_tbl PO_TBL_NUMBER;
488 BEGIN
489 d_position := 0;
490
491 IF (PO_LOG.d_proc) THEN
492 PO_LOG.proc_begin (d_module);
493 END IF;
494
495 IF (p_id_tbl IS NULL OR p_id_tbl.COUNT = 0) THEN
496 d_position := 10;
497 RETURN;
498 END IF;
499
500 IF (p_id_param_type = PO_PDOI_CONSTANTS.g_INTERFACE_LINE_LOCATION_ID) THEN
501 d_position := 20;
502 FORALL i IN 1..p_id_tbl.COUNT
503 UPDATE po_distributions_interface
504 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED,
505 processing_id = -PO_PDOI_PARAMS.g_processing_id
506 WHERE interface_line_location_id = p_id_tbl(i)
507 AND processing_id = PO_PDOI_PARAMS.g_processing_id
508 RETURNING interface_header_id
509 BULK COLLECT INTO l_intf_header_id_tbl;
510
511 ELSIF (p_id_param_type = PO_PDOI_CONSTANTS.g_INTERFACE_DISTRIBUTION_ID) THEN
512 d_position := 30;
513 FORALL i IN 1..p_id_tbl.COUNT
514 UPDATE po_distributions_interface
515 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED,
516 processing_id = -PO_PDOI_PARAMS.g_processing_id
517 WHERE interface_distribution_id = p_id_tbl(i)
518 AND processing_id = PO_PDOI_PARAMS.g_processing_id
519 RETURNING interface_header_id
520 BULK COLLECT INTO l_intf_header_id_tbl;
521
522 ELSE
523 d_position := 40;
524 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
525 END IF;
526
527 set_doc_has_errors
528 ( p_intf_header_id_tbl => l_intf_header_id_tbl
529 );
530
531 IF (PO_LOG.d_proc) THEN
532 PO_LOG.proc_end(d_module);
533 END IF;
534
535 EXCEPTION
536 WHEN OTHERS THEN
537 PO_MESSAGE_S.add_exc_msg
538 ( p_pkg_name => d_pkg_name,
539 p_procedure_name => d_api_name || '.' || d_position
540 );
541 RAISE;
542 END reject_distributions_intf;
543
544
545
546 -----------------------------------------------------------------------
547 --Start of Comments
548 --Name: reject_price_diff_intf
549 --Function:
550 -- For all ids passed in, reject the corresponding records in price diff
551 -- interface
552 --Parameters:
553 --IN:
554 --p_id_param_type
555 -- Type of the id. Possible values:
556 -- PO_PDOI_CONSTANTS.g_INTERFACE_LINE_ID
557 -- PO_PDOI_CONSTANTS.g_PRICE_DIFF_INTERFACE_ID
558 --p_id_tbl
559 -- Table of ids
560 --IN OUT:
561 --OUT:
562 --End of Comments
563 ------------------------------------------------------------------------
564 PROCEDURE reject_price_diff_intf
565 ( p_id_param_type IN VARCHAR2,
566 p_id_tbl IN PO_TBL_NUMBER
567 ) IS
568
569 d_api_name CONSTANT VARCHAR2(30) := 'reject_price_diff_intf';
570 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
571 d_position NUMBER;
572
573 l_intf_header_id_tbl PO_TBL_NUMBER;
574
575 BEGIN
576 d_position := 0;
577 IF (PO_LOG.d_proc) THEN
578 PO_LOG.proc_begin (d_module);
579 END IF;
580
581 IF (p_id_tbl IS NULL OR p_id_tbl.COUNT = 0) THEN
582 d_position := 10;
583 RETURN;
584 END IF;
585
586 IF (p_id_param_type = PO_PDOI_CONSTANTS.g_INTERFACE_LINE_ID) THEN
587 d_position := 20;
588 FORALL i IN 1..p_id_tbl.COUNT
589 UPDATE po_price_diff_interface
590 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED,
591 processing_id = -PO_PDOI_PARAMS.g_processing_id
592 WHERE interface_line_id = p_id_tbl(i)
593 AND processing_id = PO_PDOI_PARAMS.g_processing_id
594 RETURNING interface_header_id
595 BULK COLLECT INTO l_intf_header_id_tbl;
596
597 ELSIF (p_id_param_type = PO_PDOI_CONSTANTS.g_PRICE_DIFF_INTERFACE_ID) THEN
598 d_position := 30;
599 FORALL i IN 1..p_id_tbl.COUNT
600 UPDATE po_price_diff_interface
601 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED,
602 processing_id = -PO_PDOI_PARAMS.g_processing_id
603 WHERE price_diff_interface_id = p_id_tbl(i)
604 AND processing_id = PO_PDOI_PARAMS.g_processing_id
605 RETURNING interface_header_id
606 BULK COLLECT INTO l_intf_header_id_tbl;
607
608 ELSE
609 d_position := 40;
610 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
611 END IF;
612
613 /*
614 -- bug 4700377
615 -- do not set the error flag if price diff failed;
616 -- so po lines won't be rejected due to errors
617 -- on price diffs. This is to follow behavior
618 -- in 11.5.10
619 set_doc_has_errors
620 ( p_intf_header_id_tbl => l_intf_header_id_tbl
621 );
622 */
623 IF (PO_LOG.d_proc) THEN
624 PO_LOG.proc_end(d_module);
625 END IF;
626
627 EXCEPTION
628 WHEN OTHERS THEN
629 PO_MESSAGE_S.add_exc_msg
630 ( p_pkg_name => d_pkg_name,
631 p_procedure_name => d_api_name || '.' || d_position
632 );
633 RAISE;
634 END reject_price_diff_intf;
635
636
637 -----------------------------------------------------------------------
638 --Start of Comments
639 --Name: reject_attr_values_intf
640 --Function:
641 -- For all ids passed in, reject the corresponding records in attr values
642 -- interface
643 --Parameters:
644 --IN:
645 --p_id_param_type
646 -- Type of the id. Possible values:
647 -- PO_PDOI_CONSTANTS.g_INTERFACE_LINE_ID
648 -- PO_PDOI_CONSTANTS.g_INTERFACE_ATTR_VALUES_ID
649 --p_id_tbl
650 -- Table of ids
651 --IN OUT:
652 --OUT:
653 --End of Comments
654 ------------------------------------------------------------------------
655 PROCEDURE reject_attr_values_intf
656 ( p_id_param_type IN VARCHAR2,
657 p_id_tbl IN PO_TBL_NUMBER
658 ) IS
659
660 d_api_name CONSTANT VARCHAR2(30) := 'reject_attr_values_intf';
661 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
662 d_position NUMBER;
663
664 l_intf_header_id_tbl PO_TBL_NUMBER;
665
666 BEGIN
667 d_position := 0;
668
669 IF (PO_LOG.d_proc) THEN
670 PO_LOG.proc_begin (d_module);
671 END IF;
672
673 IF (p_id_tbl IS NULL OR p_id_tbl.COUNT = 0) THEN
674 d_position := 10;
675 RETURN;
676 END IF;
677
678 IF (p_id_param_type = PO_PDOI_CONSTANTS.g_INTERFACE_LINE_ID) THEN
679 d_position := 20;
680 FORALL i IN 1..p_id_tbl.COUNT
681 UPDATE po_attr_values_interface
682 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED,
683 processing_id = -PO_PDOI_PARAMS.g_processing_id
684 WHERE interface_line_id = p_id_tbl(i)
685 AND processing_id = PO_PDOI_PARAMS.g_processing_id
686 RETURNING interface_header_id
687 BULK COLLECT INTO l_intf_header_id_tbl;
688
689 ELSIF (p_id_param_type = PO_PDOI_CONSTANTS.g_INTERFACE_ATTR_VALUES_ID) THEN
690 d_position := 30;
691 FORALL i IN 1..p_id_tbl.COUNT
692 UPDATE po_attr_values_interface
693 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED,
694 processing_id = -PO_PDOI_PARAMS.g_processing_id
695 WHERE interface_attr_values_id = p_id_tbl(i)
696 AND processing_id = PO_PDOI_PARAMS.g_processing_id
697 RETURNING interface_header_id
698 BULK COLLECT INTO l_intf_header_id_tbl;
699
700 ELSE
701 d_position := 40;
702 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
703 END IF;
704
705 set_doc_has_errors
706 ( p_intf_header_id_tbl => l_intf_header_id_tbl
707 );
708
709 IF (PO_LOG.d_proc) THEN
710 PO_LOG.proc_end(d_module);
711 END IF;
712
713 EXCEPTION
714 WHEN OTHERS THEN
715 PO_MESSAGE_S.add_exc_msg
716 ( p_pkg_name => d_pkg_name,
717 p_procedure_name => d_api_name || '.' || d_position
718 );
719 RAISE;
720 END reject_attr_values_intf;
721
722
723 -----------------------------------------------------------------------
724 --Start of Comments
725 --Name: reject_attr_values_tlp_intf
726 --Function:
727 -- For all ids passed in, reject the corresponding records in attr values
728 -- tlp interface
729 --Parameters:
730 --IN:
731 --p_id_param_type
732 -- Type of the id. Possible values:
733 -- PO_PDOI_CONSTANTS.g_INTERFACE_LINE_ID
734 -- PO_PDOI_CONSTANTS.g_INTERFACE_ATTR_VALUES_TLP_ID
735 --p_id_tbl
736 -- Table of ids
737 --IN OUT:
738 --OUT:
739 --End of Comments
740 ------------------------------------------------------------------------
741 PROCEDURE reject_attr_values_tlp_intf
742 ( p_id_param_type IN VARCHAR2,
743 p_id_tbl IN PO_TBL_NUMBER
744 ) IS
745
746 d_api_name CONSTANT VARCHAR2(30) := 'reject_attr_values_tlp_intf';
747 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
748 d_position NUMBER;
749
750 l_intf_header_id_tbl PO_TBL_NUMBER;
751
752 BEGIN
753 d_position := 0;
754
755 IF (PO_LOG.d_proc) THEN
756 PO_LOG.proc_begin (d_module);
757 END IF;
758
759 IF (p_id_tbl IS NULL OR p_id_tbl.COUNT = 0) THEN
760 d_position := 10;
761 RETURN;
762 END IF;
763
764 IF (p_id_param_type = PO_PDOI_CONSTANTS.g_INTERFACE_LINE_ID) THEN
765 d_position := 20;
766 FORALL i IN 1..p_id_tbl.COUNT
767 UPDATE po_attr_values_tlp_interface
768 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED,
769 processing_id = -PO_PDOI_PARAMS.g_processing_id
770 WHERE interface_line_id = p_id_tbl(i)
771 AND processing_id = PO_PDOI_PARAMS.g_processing_id
772 RETURNING interface_header_id
773 BULK COLLECT INTO l_intf_header_id_tbl;
774
775 ELSIF (p_id_param_type = PO_PDOI_CONSTANTS.g_INTERFACE_ATTR_VALUES_TLP_ID) THEN
776 d_position := 30;
777
778 FORALL i IN 1..p_id_tbl.COUNT
779 UPDATE po_attr_values_tlp_interface
780 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED,
781 processing_id = -PO_PDOI_PARAMS.g_processing_id
782 WHERE interface_attr_values_tlp_id = p_id_tbl(i)
783 AND processing_id = PO_PDOI_PARAMS.g_processing_id
784 RETURNING interface_header_id
785 BULK COLLECT INTO l_intf_header_id_tbl;
786
787 ELSE
788 d_position := 40;
789 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
790 END IF;
791
792 set_doc_has_errors
793 ( p_intf_header_id_tbl => l_intf_header_id_tbl
794 );
795
796 IF (PO_LOG.d_proc) THEN
797 PO_LOG.proc_end(d_module);
798 END IF;
799
800 EXCEPTION
801 WHEN OTHERS THEN
802 PO_MESSAGE_S.add_exc_msg
803 ( p_pkg_name => d_pkg_name,
804 p_procedure_name => d_api_name || '.' || d_position
805 );
806 RAISE;
807 END reject_attr_values_tlp_intf;
808
809
810 -----------------------------------------------------------------------
811 --Start of Comments
812 --Name: post_reject_document
813 --Function:
814 -- Document rejection during post processing. This procedure is
815 -- different in that it removes draft changes as well.
816 --Parameters:
817 --IN:
818 --p_interface_header_id
819 -- interface header id
820 --p_po_header_id
821 -- po header id
822 --p_draft_id
823 -- draft id
824 --p_remove_draft
825 -- Flag to indicate whether draft should be removed or not
826 --IN OUT:
827 --OUT:
828 --End of Comments
829 ------------------------------------------------------------------------
830 PROCEDURE post_reject_document
831 ( p_interface_header_id IN NUMBER,
832 p_po_header_id IN NUMBER,
833 p_draft_id IN NUMBER,
834 p_remove_draft IN VARCHAR2 -- bug5129752
835 ) IS
836
837 d_api_name CONSTANT VARCHAR2(30) := 'post_reject_document';
838 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
839 d_position NUMBER;
840
841 l_return_status VARCHAR2(1);
842
843 BEGIN
844 d_position := 0;
845
846 IF (PO_LOG.d_proc) THEN
847 PO_LOG.proc_begin (d_module);
848 END IF;
849
850 -- reject interface records
851 reject_headers_intf
852 ( p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_HEADER_ID,
853 p_id_tbl => PO_TBL_NUMBER(p_interface_header_id),
854 p_cascade => FND_API.G_TRUE
855 );
856
857 d_position := 10;
858
859 -- bug5129752
860 -- Use p_remove_draft to determine whether draft should be removed or not
861
862 IF ( p_remove_draft = FND_API.G_TRUE ) THEN
863 -- remove draft changes
864 PO_DRAFTS_PVT.remove_draft_changes
865 ( p_draft_id => p_draft_id,
866 p_exclude_ctrl_tbl => FND_API.G_FALSE,
867 x_return_status => l_return_status
868 );
869 END IF;
870
871 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
872 d_position := 20;
873 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
874 END IF;
875
876 IF (PO_LOG.d_proc) THEN
877 PO_LOG.proc_end(d_module);
878 END IF;
879
880 EXCEPTION
881 WHEN OTHERS THEN
882 PO_MESSAGE_S.add_exc_msg
883 ( p_pkg_name => d_pkg_name,
884 p_procedure_name => d_api_name || '.' || d_position
885 );
886 RAISE;
887 END post_reject_document;
888
889 -----------------------------------------------------------------------
890 --Start of Comments
891 --Name: generate_ordered_num_list
892 --Function:
893 -- generate an array with the following characteristics
894 -- arr[i] = i
895 -- where i from 1..p_size
896 -- This procedure is mainly used for bulk insert, where we want to
897 -- keep the records in certain order after insertion
898 --Parameters:
899 --IN:
900 --p_size
901 -- size of the array
902 --IN OUT:
903 --OUT:
904 --x_num_list
905 --
906 --End of Comments
907 ------------------------------------------------------------------------
908 PROCEDURE generate_ordered_num_list
909 ( p_size IN NUMBER,
910 x_num_list OUT NOCOPY DBMS_SQL.NUMBER_TABLE
911 ) IS
912
913 d_api_name CONSTANT VARCHAR2(30) := 'generate_ordered_num_list';
914 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
915 d_position NUMBER;
916
917 BEGIN
918 d_position := 0;
919 FOR i IN 1..p_size LOOP
920 x_num_list(i) := i;
921 END LOOP;
922 END generate_ordered_num_list;
923
924 -----------------------------------------------------------------------
925 --Start of Comments
926 --Name: get_processing_doctype_info
927 --Function:
928 -- Derive document type and subtype current PDOI is parocessing
929 --Parameters:
930 --IN:
931 --IN OUT:
932 --OUT:
933 --x_doc_type
934 -- Document Type
935 --x_doc_subtype
936 -- Document Subtype
937 --End of Comments
938 ------------------------------------------------------------------------
939 PROCEDURE get_processing_doctype_info
940 ( x_doc_type OUT NOCOPY VARCHAR2,
941 x_doc_subtype OUT NOCOPY VARCHAR2
942 ) IS
943
944 d_api_name CONSTANT VARCHAR2(30) := 'get_processing_doctype_info';
945 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
946 d_position NUMBER;
947
948 BEGIN
949
950 d_position := 0;
951 IF (PO_LOG.d_proc) THEN
952 PO_LOG.proc_begin(d_module);
953 END IF;
954
955 IF (PO_PDOI_PARAMS.g_request.document_type =
956 PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET) THEN
957 x_doc_type := 'PA';
958 x_doc_subtype := PO_PDOI_PARAMS.g_request.document_type;
959
960 ELSIF (PO_PDOI_PARAMS.g_request.document_type =
961 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
962 x_doc_type := 'PO';
963 x_doc_subtype := PO_PDOI_PARAMS.g_request.document_type;
964
965 ELSIF (PO_PDOI_PARAMS.g_request.document_type =
966 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) THEN
967 x_doc_type := 'QUOTATION';
968 x_doc_subtype := PO_PDOI_PARAMS.g_request.document_subtype;
969 END IF;
970
971 IF (PO_LOG.d_proc) THEN
972 PO_LOG.proc_end(d_module);
973 END IF;
974
975 EXCEPTION
976 WHEN OTHERS THEN
977 PO_MESSAGE_S.add_exc_msg
978 ( p_pkg_name => d_pkg_name,
979 p_procedure_name => d_api_name || '.' || d_position
980 );
981 RAISE;
982 END get_processing_doctype_info;
983
984
985 -----------------------------------------------------------------------
986 --Start of Comments
987 --Name: is_old_request_complete
988 --Function:
989 -- Check whether the previous request (p_old_request_id) is complete and
990 -- won't process records anymore
991 --Parameters:
992 --IN:
993 --p_old_request_id
994 -- request id in question
995 --RETURN:
996 -- FND_API.G_TRUE if the request is still processing
997 -- FND_API.G_FALSE otherwise
998 --End of Comments
999 ------------------------------------------------------------------------
1000 FUNCTION is_old_request_complete
1001 ( p_old_request_id IN NUMBER
1002 ) RETURN VARCHAR2 IS
1003
1004 d_api_name CONSTANT VARCHAR2(30) := 'is_old_request_complete';
1005 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1006 d_position NUMBER;
1007
1008 l_old_request_id NUMBER;
1009
1010 l_complete VARCHAR2(1);
1011
1012 l_call_status BOOLEAN;
1013
1014 l_phase VARCHAR2(240);
1015 l_status VARCHAR2(240);
1016 l_dev_phase VARCHAR2(30);
1017 l_dev_status VARCHAR2(30);
1018 l_message VARCHAR2(4000);
1019
1020 BEGIN
1021 d_position := 0;
1022
1023 IF (PO_LOG.d_proc) THEN
1024 PO_LOG.proc_begin (d_module, 'p_old_request_id', p_old_request_id);
1025 END IF;
1026
1027 l_old_request_id := p_old_request_id;
1028 l_complete := FND_API.G_FALSE;
1029
1030 IF ( NVL(l_old_request_id, -1) = -1) THEN
1031 -- If record does not come from concurrent request, we cannot figure
1032 -- out whether the other process has finished processing this record or
1033 -- not, so we just assume that this record cannot be processed anymore
1034
1035 d_position := 10;
1036
1037 l_complete := FND_API.G_FALSE;
1038 ELSE
1039 IF (l_old_request_id = FND_GLOBAL.conc_request_id) THEN
1040 d_position := 20;
1041
1042 IF (PO_LOG.d_stmt) THEN
1043 PO_LOG.stmt (d_module, d_position, 'Restart case.');
1044 END IF;
1045
1046 -- restart case. The old one must have been completed.
1047 l_complete := FND_API.G_TRUE;
1048
1049 ELSE
1050 d_position := 30;
1051
1052 -- check request status. If it is complete, then we can safely assume
1053 -- that that request will no longer process the records
1054
1055 l_call_status :=
1056 FND_CONCURRENT.get_request_status
1057 ( request_id => l_old_request_id,
1058 phase => l_phase,
1059 status => l_status,
1060 dev_phase => l_dev_phase,
1061 dev_status => l_dev_status,
1062 message => l_message
1063 );
1064
1065 IF (l_call_status = FALSE OR l_dev_phase = 'COMPLETE') THEN
1066 d_position := 40;
1067 l_complete := FND_API.G_TRUE;
1068 ELSE
1069 d_position := 50;
1070 l_complete := FND_API.G_FALSE;
1071 END IF;
1072
1073 END IF; -- if l_dft_request_id = FND_GLOBAL.conc_request_id
1074
1075 END IF; -- if l_dft_request_id = -1;
1076
1077 IF (PO_LOG.d_proc) THEN
1078 PO_LOG.proc_begin (d_module, 'l_complete', l_complete);
1079 END IF;
1080
1081 RETURN l_complete;
1082
1083 EXCEPTION
1084 WHEN OTHERS THEN
1085 PO_MESSAGE_S.add_exc_msg
1086 ( p_pkg_name => d_pkg_name,
1087 p_procedure_name => d_api_name || '.' || d_position
1088 );
1089 RAISE;
1090 END is_old_request_complete;
1091
1092 -------------------------------------------------------
1093 -------------- PRIVATE PROCEDURES ----------------------
1094 -------------------------------------------------------
1095
1096 -----------------------------------------------------------------------
1097 --Start of Comments
1098 --Name: set_doc_has_errors
1099 --Function:
1100 -- For all header intf ids passed in, record that the document has errors.
1101 -- This will be looked at during post processing
1102 --Parameters:
1103 --IN:
1104 --p_intf_header_id_tbl
1105 -- Table of interface header ids
1106 --IN OUT:
1107 --OUT:
1108 --End of Comments
1109 ------------------------------------------------------------------------
1110 PROCEDURE set_doc_has_errors
1111 ( p_intf_header_id_tbl IN PO_TBL_NUMBER
1112 ) IS
1113
1114 d_api_name CONSTANT VARCHAR2(30) := 'set_doc_has_errors';
1115 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1116 d_position NUMBER;
1117
1118 BEGIN
1119 d_position := 0;
1120
1121 IF (PO_LOG.d_proc) THEN
1122 PO_LOG.proc_begin (d_module, 'p_intf_header_id_tbl', p_intf_header_id_tbl);
1123 END IF;
1124
1125 IF (p_intf_header_id_tbl IS NULL OR p_intf_header_id_tbl.COUNT = 0) THEN
1126 RETURN;
1127 END IF;
1128
1129 -- Mark doc info to indicate that an error has occurred.
1130 FOR i IN 1..p_intf_header_id_tbl.COUNT LOOP
1131 IF (p_intf_header_id_tbl(i) IS NOT NULL) THEN
1132 PO_PDOI_PARAMS.g_docs_info(p_intf_header_id_tbl(i)).has_errors := FND_API.G_TRUE;
1133 END IF;
1134 END LOOP;
1135
1136 IF (PO_LOG.d_proc) THEN
1137 PO_LOG.proc_end(d_module);
1138 END IF;
1139
1140 EXCEPTION
1141 WHEN OTHERS THEN
1142 PO_MESSAGE_S.add_exc_msg
1143 ( p_pkg_name => d_pkg_name,
1144 p_procedure_name => d_api_name || '.' || d_position
1145 );
1146 RAISE;
1147 END set_doc_has_errors;
1148
1149 -----------------------------------------------------------------------
1150 --Start of Comments
1151 --Name: reject_unprocessed_intf
1152 --Function:
1153 -- For intf header id passed in, reject the unprocessed records on all
1154 -- lower levels, including line, line location, attribute and price
1155 -- differential
1156 -- This is used in CATALOG UPLOAD error tolerance processing
1157 --Parameters:
1158 --IN:
1159 --p_intf_header_id
1160 -- document id for which all unprocessed lower level records should be
1161 -- rejected
1162 --IN OUT:
1163 --OUT:
1164 --End of Comments
1165 ------------------------------------------------------------------------
1166 PROCEDURE reject_unprocessed_intf
1167 (
1168 p_intf_header_id IN NUMBER
1169 ) IS
1170
1171 d_api_name CONSTANT VARCHAR2(30) := 'reject_unprocessed_intf';
1172 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1173 d_position NUMBER;
1174
1175 BEGIN
1176 d_position := 0;
1177
1178 IF (PO_LOG.d_proc) THEN
1179 PO_LOG.proc_begin (d_module, 'p_intf_header_id', p_intf_header_id);
1180 END IF;
1181
1182 IF (p_intf_header_id IS NULL) THEN
1183 d_position := 10;
1184 RETURN;
1185 END IF;
1186
1187 d_position := 20;
1188
1189 -- reject line level records
1190 UPDATE po_lines_interface
1191 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED,
1192 processing_id = -PO_PDOI_PARAMS.g_processing_id
1193 WHERE interface_header_id = p_intf_header_id
1194 AND processing_id = PO_PDOI_PARAMS.g_processing_id
1195 AND NVL(process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
1196 IN (PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING,
1197 PO_PDOI_CONSTANTS.g_PROCESS_CODE_VAL_AND_REJECT);
1198
1199 d_position := 30;
1200
1201 -- reject line location level records
1202 UPDATE po_line_locations_interface
1203 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED,
1204 processing_id = -PO_PDOI_PARAMS.g_processing_id
1205 WHERE interface_header_id = p_intf_header_id
1206 AND processing_id = PO_PDOI_PARAMS.g_processing_id
1207 AND NVL(process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
1208 = PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING;
1209
1210 d_position := 40;
1211
1212 -- reject price diff level records
1213 UPDATE po_price_diff_interface
1214 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED,
1215 processing_id = -PO_PDOI_PARAMS.g_processing_id
1216 WHERE interface_header_id = p_intf_header_id
1217 AND processing_id = PO_PDOI_PARAMS.g_processing_id
1218 AND NVL(process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
1219 = PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING;
1220
1221 d_position := 50;
1222
1223 -- reject attr level records
1224 UPDATE po_attr_values_interface
1225 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED,
1226 processing_id = -PO_PDOI_PARAMS.g_processing_id
1227 WHERE interface_header_id = p_intf_header_id
1228 AND processing_id = PO_PDOI_PARAMS.g_processing_id
1229 AND NVL(process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
1230 = PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING;
1231
1232 d_position := 60;
1233 UPDATE po_attr_values_tlp_interface
1234 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED,
1235 processing_id = -PO_PDOI_PARAMS.g_processing_id
1236 WHERE interface_header_id = p_intf_header_id
1237 AND processing_id = PO_PDOI_PARAMS.g_processing_id
1238 AND NVL(process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
1239 = PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING;
1240
1241 IF (PO_LOG.d_proc) THEN
1242 PO_LOG.proc_end(d_module);
1243 END IF;
1244
1245 EXCEPTION
1246 WHEN OTHERS THEN
1247 PO_MESSAGE_S.add_exc_msg
1248 ( p_pkg_name => d_pkg_name,
1249 p_procedure_name => d_api_name || '.' || d_position
1250 );
1251 RAISE;
1252 END reject_unprocessed_intf;
1253
1254 END PO_PDOI_UTL;