1 PACKAGE BODY PO_PDOI_MAINPROC_PVT AS
2 /* $Header: PO_PDOI_MAINPROC_PVT.plb 120.21.12010000.2 2008/08/04 08:39:03 rramasam ship $ */
3
4 d_pkg_name CONSTANT VARCHAR2(50) :=
5 PO_LOG.get_package_base('PO_PDOI_MAINPROC_PVT');
6
7 g_snap_shot_too_old EXCEPTION;
8 PRAGMA EXCEPTION_INIT(g_snap_shot_too_old, -1555);
9
10 --------------------------------------------------------------------------
11 ---------------------- PRIVATE PROCEDURES PROTOTYPE ----------------------
12 --------------------------------------------------------------------------
13 PROCEDURE process_headers;
14
15 PROCEDURE process_lines;
16
17 PROCEDURE process_lines_add
18 (
19 p_data_set_type IN NUMBER -- choice on data set to be processed
20 );
21
22 PROCEDURE process_lines_sync
23 (
24 p_data_set_type IN NUMBER -- choice on data set to be processed
25 );
26
27 PROCEDURE process_create_lines_in_group
28 (
29 p_group_num IN NUMBER,
30 p_expire_line_id_tbl IN DBMS_SQL.NUMBER_TABLE,
31 x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
32 );
33
34 PROCEDURE process_update_lines_in_group
35 (
36 p_group_num IN NUMBER,
37 x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
38 );
39
40 PROCEDURE process_line_locations;
41
42 PROCEDURE process_distributions;
43
44 PROCEDURE process_price_diffs;
45
46 PROCEDURE process_attributes;
47
48 PROCEDURE process_attr_values;
49
50 PROCEDURE process_attr_values_tlp;
51 --------------------------------------------------------------------------
52 ---------------------- PUBLIC PROCEDURES ---------------------------------
53 --------------------------------------------------------------------------
54
55 --------------------------------------------------------------------------
56 --Start of Comments
57 --Name: process
58 --Pre-reqs: None
59 --Modifies:
60 --Locks:
61 -- None
62 --Function:
63 -- perform derive, default, validate and insert/update logic on all records
64 -- in interface tables
65 --Parameters:
66 --IN: None
67 --IN OUT: None
68 --OUT: None
69 --Returns: None
70 --Notes:
71 --Testing:
72 --End of Comments
73 --------------------------------------------------------------------------
74 PROCEDURE process IS
75
76 d_api_name CONSTANT VARCHAR2(30) := 'process';
77 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
78 d_position NUMBER;
79
80 BEGIN
81 d_position := 0;
82
83 IF (PO_LOG.d_proc) THEN
84 PO_LOG.proc_begin(d_module, 'start main process for header group',
85 PO_PDOI_PARAMS.g_current_round_num);
86 END IF;
87
88 PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_MAIN_PROCESSING);
89
90 -- clean up cache before each group processing
91 PO_PDOI_MAINPROC_UTL_PVT.cleanup;
92
93 d_position := 10;
94
95 -- process each entity from upper to lower level
96 process_headers;
97
98 d_position := 10;
99
100 process_lines;
101
102 d_position := 20;
103
104 IF (PO_PDOI_PARAMS.g_request.document_type IN
105 (PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET, PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION)) THEN
106 process_attributes;
107 END IF;
108
109 d_position := 30;
110
111 process_line_locations;
112
113 d_position := 40;
114
115 IF (PO_PDOI_PARAMS.g_request.document_type =
116 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
117 process_distributions;
118 END IF;
119
120 d_position := 50;
121
122 process_price_diffs;
123
124 d_position := 60;
125
126 -- Bug 5215781:
127 -- Remove all unprocessed records if error threshold is hit for CATALOG UPLOAD
128 IF (PO_PDOI_PARAMS.g_request.calling_module =
129 PO_PDOI_CONSTANTS.g_CALL_MOD_CATALOG_UPLOAD AND
130 PO_PDOI_PARAMS.g_docs_info(PO_PDOI_PARAMS.g_request.interface_header_id)
131 .err_tolerance_exceeded = FND_API.g_TRUE) THEN
132 IF (PO_LOG.d_stmt) THEN
133 PO_LOG.stmt(d_module, d_position, 'Unprocessed records will be removed for header ',
134 PO_PDOI_PARAMS.g_request.interface_header_id);
135 END IF;
136
137 PO_PDOI_UTL.reject_unprocessed_intf
138 (
139 p_intf_header_id => PO_PDOI_PARAMS.g_request.interface_header_id
140 );
141 END IF;
142
143 PO_TIMING_UTL.stop_time(PO_PDOI_CONSTANTS.g_T_MAIN_PROCESSING);
144
145 IF (PO_LOG.d_proc) THEN
146 PO_LOG.proc_end(d_module);
147 END IF;
148
149 EXCEPTION
150 WHEN OTHERS THEN
151 PO_MESSAGE_S.add_exc_msg
152 (
153 p_pkg_name => d_pkg_name,
154 p_procedure_name => d_api_name || '.' || d_position
155 );
156 RAISE;
157 END process;
158
159 -------------------------------------------------------------------------
160 --------------------- PRIVATE PROCEDURES --------------------------------
161 -------------------------------------------------------------------------
162
163 -------------------------------------------------------------------------
164 --Start of Comments
165 --Name: process_headers
166 --Pre-reqs: None
167 --Modifies:
168 --Locks:
169 -- None
170 --Function:
171 -- handle the logic to derive, default, validate and insert records
172 -- from po_headers_interface table
173 --Parameters:
174 --IN: None
175 --IN OUT: None
176 --OUT: None
177 --Returns:
178 --Notes:
179 --Testing:
180 --End of Comments
181 ------------------------------------------------------------------------
182 PROCEDURE process_headers IS
183
184 d_api_name CONSTANT VARCHAR2(30) := 'process_headers';
185 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
186 d_position NUMBER;
187
188 -- cursor variable to point to currently processing row
189 l_headers_csr PO_PDOI_TYPES.intf_cursor_type;
190
191 -- all header attribute values within a batch
192 l_headers PO_PDOI_TYPES.headers_rec_type;
193
194 -- variable to track the largest intf_header_id processed so far
195 l_max_intf_header_id NUMBER := -1;
196
197 -- variables to track the records that need to be rejected due
198 -- to errors in the processing
199 l_count NUMBER := 0;
200 l_rej_intf_header_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
201
202 BEGIN
203 d_position := 0;
204
205 IF (PO_LOG.d_proc) THEN
206 PO_LOG.proc_begin(d_module);
207 END IF;
208
209 PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_HEADER_PROCESS);
210
211 -- open cursor for the query which retrieve the header info
212 PO_PDOI_HEADER_PROCESS_PVT.open_headers
213 (
214 p_max_intf_header_id => l_max_intf_header_id,
215 x_headers_csr => l_headers_csr
216 );
217
218 d_position := 10;
219
220 -- fetch records from header interface table and process the records
221 LOOP
222 BEGIN
223 -- fetch one batch of header records from query result
224 PO_PDOI_HEADER_PROCESS_PVT.fetch_headers
225 (
226 x_headers_csr => l_headers_csr,
227 x_headers => l_headers
228 );
229
230 d_position := 20;
231
232 -- number of records read in current batch
233 l_headers.rec_count := l_headers.intf_header_id_tbl.COUNT;
234
235 IF (PO_LOG.d_stmt) THEN
236 PO_LOG.stmt(d_module, d_position, 'header count within batch',
237 l_headers.rec_count);
238 END IF;
239
240 EXIT WHEN l_headers.rec_count = 0;
241
242 -- set up hashtable between interface_header_id and index
243 l_headers.intf_id_index_tbl.DELETE;
244
245 FOR i IN 1..l_headers.rec_count
246 LOOP
247 l_headers.intf_id_index_tbl(l_headers.intf_header_id_tbl(i)) := i;
248 END LOOP;
249
250 -- derive logic
251 PO_PDOI_HEADER_PROCESS_PVT.derive_headers
252 (
253 x_headers => l_headers
254 );
255
256 d_position := 30;
257
258 -- default logic
259 PO_PDOI_HEADER_PROCESS_PVT.default_headers
260 (
261 x_headers => l_headers
262 );
263
264 d_position := 40;
265
266 -- validate logic
267 PO_PDOI_HEADER_PROCESS_PVT.validate_headers
268 (
269 x_headers => l_headers
270 );
271
272 d_position := 50;
273
274 -- insert valid header records into draft tables
275 PO_PDOI_MOVE_TO_DRAFT_TABS_PVT.insert_headers
276 (
277 p_headers => l_headers
278 );
279
280 d_position := 60;
281
282 -- Rejected header records with errors
283 l_rej_intf_header_id_tbl.EXTEND(l_headers.rec_count);
284 FOR i IN 1..l_headers.rec_count
285 LOOP
286 IF (l_headers.error_flag_tbl(i) = FND_API.g_TRUE) THEN
287 IF (PO_LOG.d_stmt) THEN
288 PO_LOG.stmt(d_module, d_position, 'rejected intf header id',
289 l_headers.intf_header_id_tbl(i));
290 END IF;
291
292 l_count := l_count + 1;
293 l_rej_intf_header_id_tbl(l_count) := l_headers.intf_header_id_tbl(i);
294 END IF;
295 END LOOP;
296 PO_PDOI_UTL.reject_headers_intf
297 (
298 p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_HEADER_ID,
299 p_id_tbl => l_rej_intf_header_id_tbl,
300 p_cascade => FND_API.g_TRUE
301 );
302 l_rej_intf_header_id_tbl.DELETE;
303
304 d_position := 70;
305
306 -- conditional commit
307 PO_PDOI_UTL.commit_work;
308
309 -- set maximal intf_header_id read so far(used in next batch read)
310 l_max_intf_header_id := l_headers.intf_header_id_tbl(l_headers.rec_count);
311
312 -- exit if this is the last batch
313 IF (l_headers.rec_count < PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE) THEN
314 EXIT;
315 END IF;
316 EXCEPTION
317 WHEN g_snap_shot_too_old THEN
318 d_position := 80;
319
320 PO_MESSAGE_S.add_exc_msg
321 (
322 p_pkg_name => d_pkg_name,
323 p_procedure_name => d_api_name || '.' || d_position
324 );
325
326 -- commit changes
327 PO_PDOI_UTL.commit_work;
328
329 IF (l_headers_csr%ISOPEN) THEN
330 CLOSE l_headers_csr;
331 PO_PDOI_HEADER_PROCESS_PVT.open_headers
332 (
333 p_max_intf_header_id => l_max_intf_header_id,
334 x_headers_csr => l_headers_csr
335 );
336 END IF;
337 END;
338 END LOOP;
339
340 d_position := 90;
341
342 -- close cursor
343 IF (l_headers_csr%ISOPEN) THEN
344 CLOSE l_headers_csr;
345 END IF;
346
347 PO_TIMING_UTL.stop_time(PO_PDOI_CONSTANTS.g_T_HEADER_PROCESS);
348
349 IF (PO_LOG.d_proc) THEN
350 PO_LOG.proc_end(d_module);
351 END IF;
352
353 EXCEPTION
354 WHEN OTHERS THEN
355 PO_MESSAGE_S.add_exc_msg
356 (
357 p_pkg_name => d_pkg_name,
358 p_procedure_name => d_api_name || '.' || d_position
359 );
360 RAISE;
361 END process_headers;
362
363 -------------------------------------------------------------------------
364 --Start of Comments
365 --Name: process_lines
366 --Pre-reqs: None
367 --Modifies:
368 --Locks:
369 -- None
370 --Function:
371 -- process the records in po_lines_interface table;
372 -- the records in the interface table will be divided into four sets
373 -- according to their header and line level actions:
374 -- First set: all the lines with header action equal to 'ORIGINAL'or
375 -- 'REPLACE'; If header action is 'UPDATE', the document
376 -- must be Standard PO
377 -- Second Set: Header level action is 'UPDATE' and document type is
378 -- 'BLANKET' or 'QUOTATION'; And line level action is 'ADD'
379 -- Third Set: Header level action is 'UPDATE' and document type is
380 -- 'BLANKET' or 'QUOTATION'; And line level action is 'SYNC'
381 -- or Null; And either of the following criteria is true:
382 -- 1. one of attributes {item, vendor_product_num, job} is not null
383 -- 2. if {item, vendor_product_num, job) are all null, then
384 -- description must be null
385 -- Fourth Set: Header level action is 'UPDATE' and document type is
386 -- 'BLANKET' or 'QUOTATION'; And line level action is 'SYNC'
387 -- or Null; {item, vendor_product_num, job) are all null and
388 -- description is not null
389 --Parameters:
390 --IN: None
391 --IN OUT: None
392 --OUT: None
393 --Returns:
394 --Notes:
395 --Testing:
396 --End of Comments
397 ------------------------------------------------------------------------
398 PROCEDURE process_lines IS
399
400 d_api_name CONSTANT VARCHAR2(30) := 'process_lines';
401 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
402 d_position NUMBER;
403
404 BEGIN
405 d_position := 0;
406
407 IF (PO_LOG.d_proc) THEN
408 PO_LOG.proc_begin(d_module);
409 END IF;
410
411 PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_LINE_PROCESS);
412
413 -- reject lines with existing line_num when updating standard PO
414 IF (PO_PDOI_PARAMS.g_request.document_type =
415 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
416 PO_PDOI_LINE_PROCESS_PVT.reject_dup_lines_for_spo;
417 END IF;
418
419 -- reject lines with invalid line level action.
420 -- line level action can only be NULL or 'ADD';
421 -- the only exception for this is when we re-process
422 -- the notified lines, the system has set the line
423 -- level action to either 'ADD' or 'UPADTE', we don't
424 -- need to check line level action for NOTOFIED lines
425 IF (PO_PDOI_PARAMS.g_request.process_code <>
426 PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED) THEN
427 PO_PDOI_LINE_PROCESS_PVT.reject_invalid_action_lines;
428 END IF;
429
430 d_position := 10;
431
432 -- first data set
433 process_lines_add
434 (
435 p_data_set_type => PO_PDOI_CONSTANTS.g_LINE_CSR_ADD
436 );
437
438 d_position := 20;
439
440 -- second data set
441 process_lines_add
442 (
443 p_data_set_type => PO_PDOI_CONSTANTS.g_LINE_CSR_FORCE_ADD
444 );
445
446 d_position := 30;
447
448 -- third data set
449 process_lines_sync
450 (
451 p_data_set_type => PO_PDOI_CONSTANTS.g_LINE_CSR_SYNC
452 );
453
454 d_position := 40;
455
456 -- Fourth data set
457 process_lines_sync
458 (
459 p_data_set_type => PO_PDOI_CONSTANTS.g_LINE_CSR_SYNC_ON_DESC
460 );
461
462 d_position := 50;
463
464 -- delete all locations from po_line_locations_interface that are obsoleted
465 DELETE FROM po_line_locations_interface
466 WHERE process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_OBSOLETE
467 AND processing_id = PO_PDOI_PARAMS.g_processing_id;
468
469 PO_TIMING_UTL.stop_time(PO_PDOI_CONSTANTS.g_T_LINE_PROCESS);
470
471 IF (PO_LOG.d_proc) THEN
472 PO_LOG.proc_end(d_module);
473 END IF;
474
475 EXCEPTION
476 WHEN OTHERS THEN
477 PO_MESSAGE_S.add_exc_msg
478 (
479 p_pkg_name => d_pkg_name,
480 p_procedure_name => d_api_name || '.' || d_position
481 );
482 RAISE;
483 END process_lines;
484
485 -----------------------------------------------------------------------
486 --Start of Comments
487 --Name: process_lines_add
488 --Function: This procedure will deal with two kinds of data set:
489 -- First set: all the lines with header action equal to 'ORIGINAL'or
490 -- 'REPLACE'; If header action is 'UPDATE', the document
491 -- must be Standard PO
492 -- Second Set: Header level action is 'UPDATE' and document type is
493 -- 'BLANKET' or 'QUOTATION'; And line level action is 'ADD'
494 -- The two data set share similiar processing logic except for the data
495 -- to be processed. There is also an extra matching logic for First Set.
496 --Parameters:
497 --IN:
498 --p_data_set_type
499 -- the value determines which data set is going to be processed:
500 -- PO_PDOI_CONSTANTS.g_LINE_CSR_ADD: first data set
501 -- PO_PDOI_CONSTANTS.g_LINE_CSR_FORCE_ADD: second data set
502 --IN OUT:
503 --OUT:
504 --End of Comments
505 ------------------------------------------------------------------------
506 PROCEDURE process_lines_add
507 (
508 p_data_set_type IN NUMBER -- choice on data set to be processed
509 ) IS
510
511 d_api_name CONSTANT VARCHAR2(30) := 'process_lines_add';
512 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
513 d_position NUMBER;
514
515 -- cursor variable defined for the query
516 l_lines_csr PO_PDOI_TYPES.intf_cursor_type;
517
518 -- all line attribute values within a batch
519 l_lines PO_PDOI_TYPES.lines_rec_type;
520
521 -- variable to track the largest intf_line_id processed so far
522 l_max_intf_line_id NUMBER := -1;
523
524 -- variables to track the records that need to be rejected due
525 -- to errors in the processing
526 l_rej_intf_line_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
527
528 BEGIN
529 d_position := 0;
530
531 IF (PO_LOG.d_proc) THEN
532 PO_LOG.proc_begin(d_module, 'p_data_set_type', p_data_set_type);
533 END IF;
534
535 /*
536 -- code added for debugging purpose
537 select interface_header_id
538 into PO_PDOI_PARAMS.g_request.interface_header_id
539 from po_headers_interface
540 where processing_id = PO_PDOI_PARAMS.g_processing_id
541 and processing_round_num = 1;
542 */
543
544 -- Bug 5215781:
545 -- exit immediately if error threshold is hit for CATALOG UPLOAD
546 IF (PO_PDOI_PARAMS.g_request.calling_module =
547 PO_PDOI_CONSTANTS.g_CALL_MOD_CATALOG_UPLOAD AND
548 PO_PDOI_PARAMS.g_docs_info(PO_PDOI_PARAMS.g_request.interface_header_id)
549 .err_tolerance_exceeded = FND_API.g_TRUE) THEN
550 IF (PO_LOG.d_stmt) THEN
551 PO_LOG.stmt(d_module, d_position, 'Exit from process_lines_add since' ||
552 ' error threshold is hit for header ',
553 PO_PDOI_PARAMS.g_request.interface_header_id);
554 END IF;
555
556 RETURN;
557 END IF;
558
559 d_position := 5;
560
561 -- open cursor for the correct query
562 PO_PDOI_LINE_PROCESS_PVT.open_lines
563 (
564 p_data_set_type => p_data_set_type,
565 p_max_intf_line_id => l_max_intf_line_id,
566 x_lines_csr => l_lines_csr
567 );
568
569 d_position := 10;
570
571 -- fetch records from line interface table and process the records
572 LOOP
573 -- Bug 5215781:
574 -- check whether num of error lines exceeds the error
575 -- threshold for each batch
576 IF (PO_PDOI_PARAMS.g_request.calling_module =
577 PO_PDOI_CONSTANTS.g_CALL_MOD_CATALOG_UPLOAD AND
578 PO_PDOI_PARAMS.g_docs_info(PO_PDOI_PARAMS.g_request.interface_header_id)
579 .err_tolerance_exceeded = FND_API.g_TRUE) THEN
580 IF (PO_LOG.d_stmt) THEN
581 PO_LOG.stmt(d_module, d_position, 'error tolerance exceeded for',
582 PO_PDOI_PARAMS.g_request.interface_header_id);
583 END IF;
584
585 -- no need to process remaining lines if error threshold is reached
586 EXIT;
587 END IF;
588
589 BEGIN
590 -- fetch one batch of line records from query result
591 PO_PDOI_LINE_PROCESS_PVT.fetch_lines
592 (
593 x_lines_csr => l_lines_csr,
594 x_lines => l_lines
595 );
596
597 d_position := 20;
598
599 -- number of records read in current batch
600 l_lines.rec_count := l_lines.intf_line_id_tbl.COUNT;
601
602 IF (PO_LOG.d_stmt) THEN
603 PO_LOG.stmt(d_module, d_position, 'line count in batch',
604 l_lines.rec_count);
605 END IF;
606
607 EXIT WHEN l_lines.rec_count = 0;
608
609 -- set up hashtable between interface_line_id and index
610 l_lines.intf_id_index_tbl.DELETE;
611
612 FOR i IN 1..l_lines.rec_count
613 LOOP
614 l_lines.intf_id_index_tbl(l_lines.intf_line_id_tbl(i)) := i;
615 END LOOP;
616
617 -- calculate max_line_num for all document in this batch
618 -- the result is saved and used in line processing
619 PO_PDOI_MAINPROC_UTL_PVT.calculate_max_line_num
620 (
621 p_po_header_id_tbl => l_lines.hd_po_header_id_tbl,
622 p_draft_id_tbl => l_lines.draft_id_tbl
623 );
624
625 d_position := 30;
626
627 -- derive logic
628 PO_PDOI_LINE_PROCESS_PVT.derive_lines
629 (
630 x_lines => l_lines
631 );
632
633 d_position := 40;
634
635 -- default logic
636 PO_PDOI_LINE_PROCESS_PVT.default_lines
637 (
638 x_lines => l_lines
639 );
640
641 d_position := 50;
642
643 -- match related lines based on line_num/item info
644 PO_PDOI_LINE_PROCESS_PVT.match_lines
645 (
646 p_data_set_type => p_data_set_type, -- bug5129752
647 x_lines => l_lines
648 );
649
650
651 d_position := 60;
652
653 -- bug5129752
654 -- After doing match line action, l_lines may become empty (this is the
655 -- case if all the lines can be matched to lines in the draft table
656
657
658 -- validate lines
659 PO_PDOI_LINE_PROCESS_PVT.validate_lines
660 (
661 x_lines => l_lines
662 );
663
664 d_position := 70;
665
666 -- create items if necessary
667 IF (PO_PDOI_PARAMS.g_request.create_items = 'Y') THEN
668 IF (PO_LOG.d_stmt) THEN
669 PO_LOG.stmt(d_module, d_position, 'create items for lines');
670 END IF;
671
672 PO_PDOI_ITEM_PROCESS_PVT.create_items
673 (
674 x_lines => l_lines
675 );
676 END IF;
677
678 d_position := 80;
679
680 -- insert lines into line draft table
681 PO_PDOI_MOVE_TO_DRAFT_TABS_PVT.insert_lines
682 (
683 x_lines => l_lines
684 );
685
686 d_position := 90;
687
688 -- update po_lines_interface with po_line_id and line level action
689 PO_PDOI_LINE_PROCESS_PVT.update_line_intf_tbl
690 (
691 x_lines => l_lines
692 );
693
694 d_position := 100;
695
696 -- reject lines with errors
697 FOR i IN 1..l_lines.rec_count
698 LOOP
699 IF (l_lines.error_flag_tbl(i) = FND_API.g_TRUE OR
700 l_lines.need_to_reject_flag_tbl(i) = FND_API.g_TRUE) THEN
701 IF (PO_LOG.d_stmt) THEN
702 PO_LOG.stmt(d_module, d_position, 'error flag',
703 l_lines.error_flag_tbl(i));
704 PO_LOG.stmt(d_module, d_position, 'reject flag',
705 l_lines.need_to_reject_flag_tbl(i));
706 PO_LOG.stmt(d_module, d_position, 'rejected intf line id',
707 l_lines.intf_line_id_tbl(i));
708 END IF;
709
710 l_rej_intf_line_id_tbl.EXTEND;
711 l_rej_intf_line_id_tbl(l_rej_intf_line_id_tbl.COUNT) := l_lines.intf_line_id_tbl(i);
712 END IF;
713 END LOOP;
714 PO_PDOI_UTL.reject_lines_intf
715 (
716 p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_LINE_ID,
717 p_id_tbl => l_rej_intf_line_id_tbl,
718 p_cascade => FND_API.g_TRUE
719 );
720 l_rej_intf_line_id_tbl.DELETE;
721
722 d_position := 110;
723
724 -- commit changes
725 PO_PDOI_UTL.commit_work;
726
727 -- set the maximal interface_line_id processed in this batch
728 l_max_intf_line_id := l_lines.intf_line_id_tbl(l_lines.rec_count);
729
730 IF (l_lines.rec_count < PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE) THEN
731 EXIT;
732 END IF;
733 EXCEPTION
734 WHEN g_snap_shot_too_old THEN
735 d_position := 120;
736
737 -- log errors
738 PO_MESSAGE_S.add_exc_msg
739 (
740 p_pkg_name => d_pkg_name,
741 p_procedure_name => d_api_name || '.' || d_position
742 );
743
744 -- commit changes
745 PO_PDOI_UTL.commit_work;
746
747 IF (l_lines_csr%ISOPEN) THEN
748 CLOSE l_lines_csr;
749 PO_PDOI_LINE_PROCESS_PVT.open_lines
750 (
751 p_data_set_type => p_data_set_type,
752 p_max_intf_line_id => l_max_intf_line_id,
753 x_lines_csr => l_lines_csr
754 );
755 END IF;
756 END;
757 END LOOP;
758
759 d_position := 130;
760
761 -- close the cursor
762 IF (l_lines_csr%ISOPEN) THEN
763 CLOSE l_lines_csr;
764 END IF;
765
766 d_position := 140;
767
768 -- reject lines that are price breaks if main po line failed
769 SELECT interface_line_id
770 BULK COLLECT INTO l_rej_intf_line_id_tbl
771 FROM po_lines_interface intf_line1
772 WHERE price_break_flag = 'Y'
773 AND processing_id = PO_PDOI_PARAMS.g_processing_id
774 AND EXISTS(
775 SELECT 'Y'
776 FROM po_lines_interface intf_line2
777 WHERE intf_line1.interface_header_id = intf_line2.interface_header_id
778 AND intf_line1.po_line_id = intf_line2.po_line_id
779 AND NVL(intf_line2.process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
780 = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED
781 AND NVL(intf_line2.price_break_flag, 'N') = 'N');
782 d_position := 150;
783 PO_PDOI_UTL.reject_lines_intf
784 (
785 p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_LINE_ID,
786 p_id_tbl => l_rej_intf_line_id_tbl,
787 p_cascade => FND_API.g_TRUE
788 );
789
790 d_position := 160;
791
792 -- set status to NOTIFIED for price break lines if main po line is NOTIFIED
793 UPDATE po_lines_interface intf_line1
794 SET intf_line1.process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED
795 WHERE intf_line1.processing_id = PO_PDOI_PARAMS.g_processing_id
796 AND intf_line1.price_break_flag = 'Y'
797 AND EXISTS(
798 SELECT 'Y'
799 FROM po_lines_interface intf_line2
800 WHERE intf_line2.interface_header_id = intf_line1.interface_header_id
801 AND intf_line2.po_line_id = intf_line1.po_line_id
802 AND NVL(intf_line2.process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
803 = PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED
804 AND NVL(intf_line2.price_break_flag, 'N') = 'N');
805
806 IF (PO_LOG.d_proc) THEN
807 PO_LOG.proc_end(d_module);
808 END IF;
809
810 EXCEPTION
811 WHEN OTHERS THEN
812 PO_MESSAGE_S.add_exc_msg
813 (
814 p_pkg_name => d_pkg_name,
815 p_procedure_name => d_api_name || '.' || d_position
816 );
817 RAISE;
818 END process_lines_add;
819
820 -----------------------------------------------------------------------
821 --Start of Comments
822 --Name: process_lines_sync
823 --Function: This procedure will deal with two kinds of data set from
824 -- calling procedure:
825 -- Third Set: Header level action is 'UPDATE' and document type is
826 -- 'BLANKET' or 'QUOTATION'; And line level action is 'SYNC'
827 -- or Null; And either of the following criteria is true:
828 -- 1. one of attributes {item, vendor_product_num, job} is not null
829 -- 2. if {item, vendor_product_num, job) are all null, then
830 -- description must be null
831 -- Fourth Set: Header level action is 'UPDATE' and document type is
832 -- 'BLANKET' or 'QUOTATION'; And line level action is 'SYNC'
833 -- or Null; {item, vendor_product_num, job) are all null and
834 -- description is not null
835 --
836 --Parameters:
837 --IN:
838 -- p_data_set_type
839 -- the value determines which data set is going to be processed:
840 -- PO_PDOI_CONSTANTS.g_LINE_CSR_SYNC: third data set
841 -- PO_PDOI_CONSTANTS.g_LINE_CSR_SYNC_ON_DESC: fourth data set
842 --IN OUT:
843 --OUT:
844 --End of Comments
845 ------------------------------------------------------------------------
846 PROCEDURE process_lines_sync
847 (
848 p_data_set_type IN NUMBER
849 ) IS
850
851 d_api_name CONSTANT VARCHAR2(30) := 'process_lines_sync';
852 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
853 d_position NUMBER;
854
855 -- cursor variable defined for the query
856 l_lines_csr PO_PDOI_TYPES.intf_cursor_type;
857
858 -- all line attribute values within a batch
859 l_lines PO_PDOI_TYPES.lines_rec_type;
860
861 -- variable to track the largest intf_line_id processed so far
862 l_max_intf_line_id NUMBER := -1;
863
864 -- lines that are going to be created/updated
865 l_create_lines PO_PDOI_TYPES.lines_rec_type;
866 l_update_lines PO_PDOI_TYPES.lines_rec_type;
867
868 /* bug 6926550 Added the following variable to null the
869 l_create_lines and l_update_lines inside the procedure*/
870 l_null_lines PO_PDOI_TYPES.lines_rec_type;
871
872 -- line grouping num
873 l_group_num NUMBER := 0;
874
875 -- variable to track records that have not been processed
876 l_processing_row_tbl DBMS_SQL.NUMBER_TABLE;
877
878 -- variable to track lines that need to be expired
879 l_expire_line_id_tbl DBMS_SQL.NUMBER_TABLE;
880
881 l_rej_intf_line_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
882 l_notified_intf_line_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
883
884 BEGIN
885 d_position := 0;
886
887 IF (PO_LOG.d_proc) THEN
888 PO_LOG.proc_begin(d_module, 'p_data_set_type', p_data_set_type);
889 END IF;
890
891 -- Bug 5215781:
892 -- exit immediately if error threshold is hit for CATALOG UPLOAD
893 IF (PO_PDOI_PARAMS.g_request.calling_module =
894 PO_PDOI_CONSTANTS.g_CALL_MOD_CATALOG_UPLOAD AND
895 PO_PDOI_PARAMS.g_docs_info(PO_PDOI_PARAMS.g_request.interface_header_id)
896 .err_tolerance_exceeded = FND_API.g_TRUE) THEN
897 IF (PO_LOG.d_stmt) THEN
898 PO_LOG.stmt(d_module, d_position, 'Exit from process_lines_sync since' ||
899 ' error threshold is hit for header ',
900 PO_PDOI_PARAMS.g_request.interface_header_id);
901 END IF;
902
903 RETURN;
904 END IF;
905
906 d_position := 5;
907
908 -- open cursor for the correct query
909 PO_PDOI_LINE_PROCESS_PVT.open_lines
910 (
911 p_data_set_type => p_data_set_type,
912 p_max_intf_line_id => l_max_intf_line_id,
913 x_lines_csr => l_lines_csr
914 );
915
916 d_position := 10;
917
918 -- fetch records from line interface table and process the records
919 LOOP
920 -- Bug 5215781:
921 -- check whether num of error lines exceeds the error
922 -- threshold for the previous batch
923 IF (PO_PDOI_PARAMS.g_request.calling_module =
924 PO_PDOI_CONSTANTS.g_CALL_MOD_CATALOG_UPLOAD AND
925 PO_PDOI_PARAMS.g_docs_info(PO_PDOI_PARAMS.g_request.interface_header_id)
926 .err_tolerance_exceeded = FND_API.g_TRUE) THEN
927 d_position := 20;
928
929 IF (PO_LOG.d_stmt) THEN
930 PO_LOG.stmt(d_module, d_position, 'error tolerance exceeded for',
931 PO_PDOI_PARAMS.g_request.interface_header_id);
932 END IF;
933
934 EXIT;
935 END IF;
936
937 BEGIN
938 -- fetch one batch of line records from query result
939 PO_PDOI_LINE_PROCESS_PVT.fetch_lines
940 (
941 x_lines_csr => l_lines_csr,
942 x_lines => l_lines
943 );
944
945 d_position := 30;
946
947 -- number of records read in current batch
948 l_lines.rec_count := l_lines.intf_line_id_tbl.COUNT;
949
950 IF (PO_LOG.d_stmt) THEN
951 PO_LOG.stmt(d_module, d_position, 'line count in batch',
952 l_lines.rec_count);
953 END IF;
954
955 EXIT WHEN l_lines.rec_count = 0;
956
957 /* <bug 6926550> null the l_create_lines and l_update_lines
958 before calling split lines procedure in the inner loop*/
959 l_create_lines := l_null_lines;
960 l_update_lines := l_null_lines;
961
962 -- set up hashtable between interface_line_id and index
963 l_lines.intf_id_index_tbl.DELETE;
964
965 FOR i IN 1..l_lines.rec_count
966 LOOP
967 l_lines.intf_id_index_tbl(l_lines.intf_line_id_tbl(i)) := i;
968 END LOOP;
969
970 -- calculate max_line_num for all document in this batch
971 -- the result is saved and used in line processing
972 PO_PDOI_MAINPROC_UTL_PVT.calculate_max_line_num
973 (
974 p_po_header_id_tbl => l_lines.hd_po_header_id_tbl,
975 p_draft_id_tbl => l_lines.draft_id_tbl
976 );
977
978 d_position := 40;
979
980 -- check whether provided line_num is unique across interface,
981 -- draft and txn tables
982 PO_PDOI_MAINPROC_UTL_PVT.check_line_num_unique
983 (
984 p_po_header_id_tbl => l_lines.hd_po_header_id_tbl,
985 p_draft_id_tbl => l_lines.draft_id_tbl,
986 p_intf_line_id_tbl => l_lines.intf_line_id_tbl,
987 p_line_num_tbl => l_lines.line_num_tbl,
988 x_line_num_unique_tbl => l_lines.line_num_unique_tbl
989 );
990
991 d_position := 50;
992
993 -- setup table to track rows that have not been processed
994 PO_PDOI_UTL.generate_ordered_num_list
995 (
996 p_size => l_lines.rec_count,
997 x_num_list => l_processing_row_tbl
998 );
999
1000 -- inner loop to process line records in groups
1001 -- If same line is created and updated within the same batch,
1002 -- they must be processed in separate groups
1003 LOOP
1004 -- exit when all rows are processed
1005 IF (l_processing_row_tbl.COUNT = 0) THEN
1006 d_position := 60;
1007
1008 IF (PO_LOG.d_stmt) THEN
1009 PO_LOG.stmt(d_module, d_position, 'exit after all lines are processed');
1010 END IF;
1011
1012 EXIT;
1013 END IF;
1014
1015 -- increment group number
1016 l_group_num := l_group_num + 1;
1017
1018 IF (PO_LOG.d_stmt) THEN
1019 PO_LOG.stmt(d_module, d_position, 'group_num', l_group_num);
1020 END IF;
1021
1022 -- perform uniqueness check on all the records and
1023 -- mark the group number on the records that can be processed
1024 -- within a group
1025 PO_PDOI_LINE_PROCESS_PVT.uniqueness_check
1026 (
1027 p_type => p_data_set_type,
1028 p_group_num => l_group_num,
1029 x_processing_row_tbl => l_processing_row_tbl,
1030 x_lines => l_lines,
1031 x_expire_line_id_tbl => l_expire_line_id_tbl
1032 );
1033
1034 d_position := 70;
1035
1036 -- separate data records into two set, one for create and one for update
1037 PO_PDOI_LINE_PROCESS_PVT.split_lines
1038 (
1039 p_group_num => l_group_num,
1040 p_lines => l_lines,
1041 x_create_lines => l_create_lines,
1042 x_update_lines => l_update_lines
1043 );
1044
1045 d_position := 80;
1046
1047 -- process all rows that needs to be created
1048 process_create_lines_in_group
1049 (
1050 p_group_num => l_group_num,
1051 p_expire_line_id_tbl => l_expire_line_id_tbl,
1052 x_lines => l_create_lines
1053 );
1054
1055 d_position := 90;
1056
1057 -- process all the lines that need to be updated
1058 process_update_lines_in_group
1059 (
1060 p_group_num => l_group_num,
1061 x_lines => l_update_lines
1062 );
1063 END LOOP;
1064
1065 PO_PDOI_UTL.commit_work;
1066
1067 -- set the maximal interface_line_id processed in this batch
1068 l_max_intf_line_id := l_lines.intf_line_id_tbl(l_lines.rec_count);
1069
1070 IF (l_lines.rec_count < PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE) THEN
1071 EXIT;
1072 END IF;
1073 EXCEPTION
1074 WHEN g_snap_shot_too_old THEN
1075 d_position := 100;
1076
1077 -- log error
1078 PO_MESSAGE_S.add_exc_msg
1079 (
1080 p_pkg_name => d_pkg_name,
1081 p_procedure_name => d_api_name || '.' || d_position
1082 );
1083
1084 -- commit changes
1085 PO_PDOI_UTL.commit_work;
1086
1087 IF (l_lines_csr%ISOPEN) THEN
1088 CLOSE l_lines_csr;
1089 PO_PDOI_LINE_PROCESS_PVT.open_lines
1090 (
1091 p_data_set_type => p_data_set_type,
1092 p_max_intf_line_id => l_max_intf_line_id,
1093 x_lines_csr => l_lines_csr
1094 );
1095 END IF;
1096 END;
1097 END LOOP;
1098
1099 d_position := 110;
1100
1101 -- close the cursor
1102 IF (l_lines_csr%ISOPEN) THEN
1103 CLOSE l_lines_csr;
1104 END IF;
1105
1106 d_position := 120;
1107
1108 -- reject lines that are price breaks if main po line failed
1109 SELECT v.interface_line_id
1110 BULK COLLECT INTO l_rej_intf_line_id_tbl
1111 FROM po_lines_interface intf_line1,
1112 (SELECT intf_line2.interface_line_id, max(intf_line3.interface_line_id) AS match_intf_line_id
1113 FROM po_lines_interface intf_line2, po_lines_interface intf_line3,
1114 po_headers_interface intf_headers
1115 WHERE intf_line2.interface_header_id = intf_headers.interface_header_id
1116 AND intf_headers.processing_id = PO_PDOI_PARAMS.g_processing_id
1117 AND intf_headers.processing_round_num = PO_PDOI_PARAMS.g_current_round_num
1118 AND intf_line2.price_break_flag = 'Y'
1119 AND intf_line2.processing_id = PO_PDOI_PARAMS.g_processing_id
1120 AND intf_line2.interface_header_id = intf_line3.interface_header_id
1121 AND intf_line2.po_line_id = intf_line3.po_line_id
1122 AND NVL(intf_line3.price_break_flag, 'N') = 'N'
1123 AND intf_line3.interface_line_id < intf_line2.interface_line_id
1124 GROUP BY intf_line2.interface_line_id) v
1125 WHERE intf_line1.interface_line_id = v.match_intf_line_id
1126 AND NVL(intf_line1.process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
1127 = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED;
1128
1129 d_position := 130;
1130
1131 PO_PDOI_UTL.reject_lines_intf
1132 (
1133 p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_LINE_ID,
1134 p_id_tbl => l_rej_intf_line_id_tbl,
1135 p_cascade => FND_API.g_TRUE
1136 );
1137
1138 d_position := 140;
1139
1140 -- set status to NOTIFIED for price break lines if main po line is NOTIFIED
1141 SELECT v.interface_line_id
1142 BULK COLLECT INTO l_notified_intf_line_id_tbl
1143 FROM po_lines_interface intf_line1,
1144 (SELECT intf_line2.interface_line_id, max(intf_line3.interface_line_id) AS match_intf_line_id
1145 FROM po_lines_interface intf_line2, po_lines_interface intf_line3,
1146 po_headers_interface intf_headers
1147 WHERE intf_line2.interface_header_id = intf_headers.interface_header_id
1148 AND intf_headers.processing_id = PO_PDOI_PARAMS.g_processing_id
1149 AND intf_headers.processing_round_num = PO_PDOI_PARAMS.g_current_round_num
1150 AND intf_line2.price_break_flag = 'Y'
1151 AND intf_line2.processing_id = PO_PDOI_PARAMS.g_processing_id
1152 AND intf_line2.interface_header_id = intf_line3.interface_header_id
1153 AND intf_line2.po_line_id = intf_line3.po_line_id
1154 AND NVL(intf_line3.price_break_flag, 'N') = 'N'
1155 AND intf_line3.interface_line_id < intf_line2.interface_line_id
1156 GROUP BY intf_line2.interface_line_id) v
1157 WHERE intf_line1.interface_line_id = v.match_intf_line_id
1158 AND NVL(intf_line1.process_code, PO_PDOI_CONSTANTS.g_PROCESS_CODE_PENDING)
1159 = PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED;
1160
1161 d_position := 150;
1162
1163 FORALL i IN 1..l_notified_intf_line_id_tbl.COUNT
1164 UPDATE po_lines_interface
1165 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED
1166 WHERE interface_line_id = l_notified_intf_line_id_tbl(i);
1167
1168 d_position := 160;
1169
1170 IF (PO_LOG.d_proc) THEN
1171 PO_LOG.proc_end(d_module);
1172 END IF;
1173
1174 EXCEPTION
1175 WHEN OTHERS THEN
1176 PO_MESSAGE_S.add_exc_msg
1177 (
1178 p_pkg_name => d_pkg_name,
1179 p_procedure_name => d_api_name || '.' || d_position
1180 );
1181 RAISE;
1182 END process_lines_sync;
1183
1184 -----------------------------------------------------------------------
1185 --Start of Comments
1186 --Name: process_create_lines_in_group
1187 --Function: Data set 3 and 4 contains lines of mixed actions. After the
1188 -- action is determined by uniqueness logic, lines with same
1189 -- actions will be grouped together and processed separately
1190 -- from lines with different actions.
1191 -- This procedure is to deal with lines whose action is 'ADD'.
1192 --Parameters:
1193 --IN:
1194 -- p_group_num
1195 -- the current processing group number. Only lines belonging to
1196 -- current group will be processed.
1197 -- p_expire_line_id_tbl
1198 -- The lines that need to be expired
1199 --IN OUT:
1200 -- x_lines
1201 -- contains all lines within the batch
1202 --OUT:
1203 --End of Comments
1204 ------------------------------------------------------------------------
1205 PROCEDURE process_create_lines_in_group
1206 (
1207 p_group_num IN NUMBER,
1208 p_expire_line_id_tbl IN DBMS_SQL.NUMBER_TABLE,
1209 x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
1210 ) IS
1211
1212 d_api_name CONSTANT VARCHAR2(30) := 'process_create_lines_in_group';
1213 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1214 d_position NUMBER;
1215
1216 -- variables to track the records that need to be rejected due
1217 -- to errors in the processing
1218 l_count NUMBER := 0;
1219 l_rej_intf_line_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
1220
1221 BEGIN
1222 d_position := 0;
1223
1224 IF (PO_LOG.d_proc) THEN
1225 PO_LOG.proc_begin(d_module, 'p_group_num', p_group_num);
1226 PO_LOG.proc_begin(d_module, 'count of lines', x_lines.rec_count);
1227 END IF;
1228
1229 -- return if there is no line to be processed
1230 IF (x_lines.rec_count = 0) THEN
1231 IF (PO_LOG.d_proc) THEN
1232 PO_LOG.proc_end(d_module);
1233 END IF;
1234
1235 RETURN;
1236 END IF;
1237
1238 -- derive logic
1239 PO_PDOI_LINE_PROCESS_PVT.derive_lines
1240 (
1241 x_lines => x_lines
1242 );
1243
1244 d_position := 10;
1245
1246 -- default logic
1247 PO_PDOI_LINE_PROCESS_PVT.default_lines
1248 (
1249 x_lines => x_lines
1250 );
1251
1252 d_position := 20;
1253
1254 -- validate lines
1255 PO_PDOI_LINE_PROCESS_PVT.validate_lines
1256 (
1257 x_lines => x_lines
1258 );
1259
1260 d_position := 30;
1261
1262 -- check whether line location needs to be created for the line
1263 PO_PDOI_LINE_PROCESS_PVT.check_line_locations
1264 (
1265 x_lines => x_lines
1266 );
1267
1268 d_position := 40;
1269
1270 -- create items if necessary
1271 IF (PO_PDOI_PARAMS.g_request.create_items = 'Y') THEN
1272 IF (PO_LOG.d_stmt) THEN
1273 PO_LOG.stmt(d_module, d_position, 'create items from line info');
1274 END IF;
1275
1276 PO_PDOI_ITEM_PROCESS_PVT.create_items
1277 (
1278 x_lines => x_lines
1279 );
1280 END IF;
1281
1282 d_position := 50;
1283
1284 -- insert lines into draft table
1285 PO_PDOI_MOVE_TO_DRAFT_TABS_PVT.insert_lines
1286 (
1287 x_lines => x_lines
1288 );
1289
1290 d_position := 60;
1291
1292 -- update po_lines_interface with po_line_id and line level action
1293 PO_PDOI_LINE_PROCESS_PVT.update_line_intf_tbl
1294 (
1295 x_lines => x_lines
1296 );
1297
1298 d_position := 70;
1299
1300 -- reject lines with errors
1301 l_rej_intf_line_id_tbl.EXTEND(x_lines.rec_count);
1302 FOR i IN 1..x_lines.rec_count
1303 LOOP
1304 IF (x_lines.error_flag_tbl(i) = FND_API.g_TRUE OR
1305 x_lines.need_to_reject_flag_tbl(i) = FND_API.g_TRUE) THEN
1306 IF (PO_LOG.d_stmt) THEN
1307 PO_LOG.stmt(d_module, d_position, 'error flag',
1308 x_lines.error_flag_tbl(i));
1309 PO_LOG.stmt(d_module, d_position, 'reject flag',
1310 x_lines.need_to_reject_flag_tbl(i));
1311 PO_LOG.stmt(d_module, d_position, 'rejected intf line id',
1312 x_lines.intf_line_id_tbl(i));
1313 END IF;
1314
1315 l_count := l_count + 1;
1316 l_rej_intf_line_id_tbl(l_count) := x_lines.intf_line_id_tbl(i);
1317 END IF;
1318 END LOOP;
1319 PO_PDOI_UTL.reject_lines_intf
1320 (
1321 p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_LINE_ID,
1322 p_id_tbl => l_rej_intf_line_id_tbl,
1323 p_cascade => FND_API.g_TRUE
1324 );
1325 l_rej_intf_line_id_tbl.DELETE;
1326
1327 d_position := 80;
1328
1329 -- expire blanket lines being replaced. They are replaced since it has
1330 -- release shipment and uom is changed in the new request
1331 FORALL i IN 1..p_expire_line_id_tbl.COUNT
1332 UPDATE po_lines_all
1333 SET expiration_date = TRUNC(sysdate - 1)
1334 WHERE po_line_id = p_expire_line_id_tbl(i);
1335
1336 IF (PO_LOG.d_proc) THEN
1337 PO_LOG.proc_end(d_module);
1338 END IF;
1339
1340 EXCEPTION
1341 WHEN OTHERS THEN
1342 PO_MESSAGE_S.add_exc_msg
1343 (
1344 p_pkg_name => d_pkg_name,
1345 p_procedure_name => d_api_name || '.' || d_position
1346 );
1347 RAISE;
1348 END process_create_lines_in_group;
1349
1350 -----------------------------------------------------------------------
1351 --Start of Comments
1352 --Name: process_update_lines_in_group
1353 --Function: Data set 3 and 4 contains lines of mixed actions. After the
1354 -- action is determined by uniqueness logic, lines with same
1355 -- actions will be grouped together and processed separately
1356 -- from lines with different actions.
1357 -- This procedure is to deal with lines whose action is 'UPDATE'
1358 --Parameters:
1359 -- p_group_num
1360 -- the current processing group number. Only lines belonging to
1361 -- current group will be processed.
1362 --IN OUT:
1363 -- x_lines
1364 -- contains all lines within the batch
1365 --OUT:
1366 --End of Comments
1367 ------------------------------------------------------------------------
1368 PROCEDURE process_update_lines_in_group
1369 (
1370 p_group_num IN NUMBER,
1371 x_lines IN OUT NOCOPY PO_PDOI_TYPES.lines_rec_type
1372 ) IS
1373
1374 d_api_name CONSTANT VARCHAR2(30) := 'process_update_lines_in_group';
1375 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1376 d_position NUMBER;
1377
1378 -- variables to track the records that need to be rejected due
1379 -- to errors in the processing
1380 l_count NUMBER := 0;
1381 l_rej_intf_line_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
1382
1383 BEGIN
1384 d_position := 0;
1385
1386 IF (PO_LOG.d_proc) THEN
1387 PO_LOG.proc_begin(d_module, 'p_group_num', p_group_num);
1388 PO_LOG.proc_begin(d_module, 'count of lines', x_lines.rec_count);
1389 END IF;
1390
1391 -- return if there is no line to be processed
1392 IF (x_lines.rec_count = 0) THEN
1393 IF (PO_LOG.d_proc) THEN
1394 PO_LOG.proc_end(d_module);
1395 END IF;
1396
1397 RETURN;
1398 END IF;
1399
1400 -- derive internal identifier for updatable attributes
1401 PO_PDOI_LINE_PROCESS_PVT.derive_lines_for_update
1402 (
1403 x_lines => x_lines
1404 );
1405
1406 -- default certain attributes for processing purpose
1407 PO_PDOI_LINE_PROCESS_PVT.default_lines_for_update
1408 (
1409 x_lines => x_lines
1410 );
1411
1412 -- validate lines
1413 PO_PDOI_LINE_PROCESS_PVT.validate_lines
1414 (
1415 p_action => 'UPDATE',
1416 x_lines => x_lines
1417 );
1418
1419 d_position := 10;
1420
1421 -- check whether line location needs to be created for the line
1422 PO_PDOI_LINE_PROCESS_PVT.check_line_locations
1423 (
1424 x_lines => x_lines
1425 );
1426
1427 d_position := 20;
1428
1429 -- update lines on draft table
1430 PO_PDOI_MOVE_TO_DRAFT_TABS_PVT.update_lines
1431 (
1432 x_lines => x_lines
1433 );
1434
1435 d_position := 30;
1436
1437 -- update po_lines_interface with po_line_id and line level action
1438 PO_PDOI_LINE_PROCESS_PVT.update_line_intf_tbl
1439 (
1440 x_lines => x_lines
1441 );
1442
1443 d_position := 40;
1444
1445 -- set rejected status to lines and lower levels
1446 l_rej_intf_line_id_tbl.EXTEND(x_lines.rec_count);
1447 FOR i IN 1..x_lines.rec_count
1448 LOOP
1449 IF (x_lines.error_flag_tbl(i) = FND_API.g_TRUE OR
1450 x_lines.need_to_reject_flag_tbl(i) = FND_API.g_TRUE) THEN
1451 IF (PO_LOG.d_stmt) THEN
1452 PO_LOG.stmt(d_module, d_position, 'error flag',
1453 x_lines.error_flag_tbl(i));
1454 PO_LOG.stmt(d_module, d_position, 'reject flag',
1455 x_lines.need_to_reject_flag_tbl(i));
1456 PO_LOG.stmt(d_module, d_position, 'rejected intf line id',
1457 x_lines.intf_line_id_tbl(i));
1458 END IF;
1459
1460 l_count := l_count + 1;
1461 l_rej_intf_line_id_tbl(l_count) := x_lines.intf_line_id_tbl(i);
1462 END IF;
1463 END LOOP;
1464 PO_PDOI_UTL.reject_lines_intf
1465 (
1466 p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_LINE_ID,
1467 p_id_tbl => l_rej_intf_line_id_tbl,
1468 p_cascade => FND_API.g_TRUE
1469 );
1470 l_rej_intf_line_id_tbl.DELETE;
1471
1472 IF (PO_LOG.d_proc) THEN
1473 PO_LOG.proc_end(d_module);
1474 END IF;
1475
1476 EXCEPTION
1477 WHEN OTHERS THEN
1478 PO_MESSAGE_S.add_exc_msg
1479 (
1480 p_pkg_name => d_pkg_name,
1481 p_procedure_name => d_api_name || '.' || d_position
1482 );
1483 RAISE;
1484 END process_update_lines_in_group;
1485
1486 -------------------------------------------------------------------------
1487 --Start of Comments
1488 --Name: process_line_locations
1489 --Pre-reqs: None
1490 --Modifies:
1491 --Locks:
1492 -- None
1493 --Function:
1494 -- handle the logic to derive, default, validate and insert records
1495 -- from po_line_locations_interface table
1496 --Parameters:
1497 --IN: None
1498 --IN OUT: None
1499 --OUT: None
1500 --Returns:
1501 --Notes:
1502 --Testing:
1503 --End of Comments
1504 ------------------------------------------------------------------------
1505 PROCEDURE process_line_locations IS
1506
1507 d_api_name CONSTANT VARCHAR2(30) := 'process_line_locations';
1508 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1509 d_position NUMBER;
1510
1511 -- cursor variable defined for the query
1512 l_line_locs_csr PO_PDOI_TYPES.intf_cursor_type;
1513
1514 -- all line location attribute values within a batch
1515 l_line_locs PO_PDOI_TYPES.line_locs_rec_type;
1516
1517 -- variables to track the records that need to be rejected due
1518 -- to errors in the processing
1519 l_rej_intf_line_loc_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
1520
1521 -- keep record of lines processed in this request
1522 -- the record will be used in post line location processing
1523 l_processed_line_id_tbl DBMS_SQL.number_table;
1524 l_processed_draft_id_tbl DBMS_SQL.number_table;
1525 l_line_ref_tbl DBMS_SQL.number_table;
1526
1527 -- maximal interface_line_location_id_processed
1528 l_max_intf_line_loc_id NUMBER := -1;
1529
1530 l_count NUMBER;
1531 BEGIN
1532 d_position := 0;
1533
1534 IF (PO_LOG.d_proc) THEN
1535 PO_LOG.proc_begin(d_module);
1536 END IF;
1537
1538 -- Bug 5215781:
1539 -- exit immediately if error threshold is hit for CATALOG UPLOAD
1540 IF (PO_PDOI_PARAMS.g_request.calling_module =
1541 PO_PDOI_CONSTANTS.g_CALL_MOD_CATALOG_UPLOAD AND
1542 PO_PDOI_PARAMS.g_docs_info(PO_PDOI_PARAMS.g_request.interface_header_id)
1543 .err_tolerance_exceeded = FND_API.g_TRUE) THEN
1544 IF (PO_LOG.d_stmt) THEN
1545 PO_LOG.stmt(d_module, d_position, 'Exit from process_line_locations since' ||
1546 ' error threshold is hit for header ',
1547 PO_PDOI_PARAMS.g_request.interface_header_id);
1548 END IF;
1549
1550 RETURN;
1551 END IF;
1552
1553 d_position := 5;
1554
1555 PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_LINE_LOC_PROCESS);
1556
1557 -- open cursor for the correct query
1558 PO_PDOI_LINE_LOC_PROCESS_PVT.open_line_locs
1559 (
1560 p_max_intf_line_loc_id => l_max_intf_line_loc_id,
1561 x_line_locs_csr => l_line_locs_csr
1562 );
1563
1564 d_position := 10;
1565
1566 -- fetch records from line location interface table and process the records
1567 LOOP
1568 -- Bug 5215781:
1569 -- check whether num of error lines exceeds the error
1570 -- threshold for the previous batch
1571 IF (PO_PDOI_PARAMS.g_request.calling_module =
1572 PO_PDOI_CONSTANTS.g_CALL_MOD_CATALOG_UPLOAD AND
1573 PO_PDOI_PARAMS.g_docs_info(PO_PDOI_PARAMS.g_request.interface_header_id)
1574 .err_tolerance_exceeded = FND_API.g_TRUE) THEN
1575 d_position := 20;
1576
1577 IF (PO_LOG.d_stmt) THEN
1578 PO_LOG.stmt(d_module, d_position, 'error tolerance exceeded for',
1579 PO_PDOI_PARAMS.g_request.interface_header_id);
1580 END IF;
1581
1582 EXIT;
1583 END IF;
1584
1585 BEGIN
1586 -- fetch one batch of line location records from query result
1587 PO_PDOI_LINE_LOC_PROCESS_PVT.fetch_line_locs
1588 (
1589 x_line_locs_csr => l_line_locs_csr,
1590 x_line_locs => l_line_locs
1591 );
1592
1593 d_position := 20;
1594
1595 -- number of records read in current batch
1596 l_line_locs.rec_count := l_line_locs.intf_line_loc_id_tbl.COUNT;
1597
1598 IF (PO_LOG.d_stmt) THEN
1599 PO_LOG.stmt(d_module, d_position, 'loc count in batch',
1600 l_line_locs.rec_count);
1601 END IF;
1602
1603 EXIT WHEN l_line_locs.rec_count = 0;
1604
1605 -- set up hashtable between interface_line_location_id and index
1606 l_line_locs.intf_id_index_tbl.DELETE;
1607
1608 FOR i IN 1..l_line_locs.rec_count
1609 LOOP
1610 l_line_locs.intf_id_index_tbl(l_line_locs.intf_line_loc_id_tbl(i)) := i;
1611 END LOOP;
1612
1613 -- calculate max_shipment_num for each line in this batch
1614 -- the result is saved and used in line location processing
1615 PO_PDOI_MAINPROC_UTL_PVT.calculate_max_shipment_num
1616 (
1617 p_po_line_id_tbl => l_line_locs.ln_po_line_id_tbl,
1618 p_draft_id_tbl => l_line_locs.draft_id_tbl
1619 );
1620
1621 d_position := 30;
1622
1623 -- check whether provided shipment_num is unqiue across interface,
1624 -- draft and txn tables.
1625 -- logic is performed for Standard PO only
1626 IF (PO_PDOI_PARAMS.g_request.document_type =
1627 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
1628 PO_PDOI_MAINPROC_UTL_PVT.check_shipment_num_unique
1629 (
1630 p_po_line_id_tbl => l_line_locs.ln_po_line_id_tbl,
1631 p_draft_id_tbl => l_line_locs.draft_id_tbl,
1632 p_intf_line_loc_id_tbl => l_line_locs.intf_line_loc_id_tbl,
1633 p_shipment_num_tbl => l_line_locs.shipment_num_tbl,
1634 x_shipment_num_unique_tbl => l_line_locs.shipment_num_unique_tbl
1635 );
1636 END IF;
1637
1638 d_position := 40;
1639
1640 -- derive logic
1641 PO_PDOI_LINE_LOC_PROCESS_PVT.derive_line_locs
1642 (
1643 x_line_locs => l_line_locs
1644 );
1645
1646 d_position := 50;
1647
1648 -- default logic
1649 PO_PDOI_LINE_LOC_PROCESS_PVT.default_line_locs
1650 (
1651 x_line_locs => l_line_locs
1652 );
1653
1654 d_position := 60;
1655
1656 -- validate logic
1657 PO_PDOI_LINE_LOC_PROCESS_PVT.validate_line_locs
1658 (
1659 x_line_locs => l_line_locs
1660 );
1661
1662 d_position := 70;
1663
1664 -- insert line locations into line location draft table
1665 PO_PDOI_MOVE_TO_DRAFT_TABS_PVT.insert_line_locs
1666 (
1667 p_line_locs => l_line_locs
1668 );
1669
1670 d_position := 80;
1671
1672 -- update po_line_locations_interface with line_location_id
1673 PO_PDOI_LINE_LOC_PROCESS_PVT.update_line_loc_interface
1674 (
1675 p_intf_line_loc_id_tbl => l_line_locs.intf_line_loc_id_tbl,
1676 p_line_loc_id_tbl => l_line_locs.line_loc_id_tbl,
1677 p_error_flag_tbl => l_line_locs.error_flag_tbl
1678 );
1679
1680 d_position := 90;
1681
1682 -- set rejected status to line locations and lower levels
1683 FOR i IN 1..l_line_locs.rec_count
1684 LOOP
1685 IF (l_line_locs.error_flag_tbl(i) = FND_API.G_TRUE) THEN
1686 l_rej_intf_line_loc_id_tbl.EXTEND;
1687 l_rej_intf_line_loc_id_tbl(l_rej_intf_line_loc_id_tbl.COUNT) := l_line_locs.intf_line_loc_id_tbl(i);
1688
1689 -- bug 5215781:
1690 -- remove the logic here, same logic is moved to PO_PDOI_LINE_LOC_PROCESS_PVT
1691 /*
1692 -- bug4662687 START
1693 -- Report the lines that have shipment/price break errors
1694 IF (NOT PO_PDOI_PARAMS.g_errored_lines.EXISTS(l_line_locs.intf_line_id_tbl(i))) THEN
1695 PO_PDOI_PARAMS.g_errored_lines(l_line_locs.intf_line_id_tbl(i)) := 'Y';
1696 PO_PDOI_PARAMS.g_docs_info(l_line_locs.intf_header_id_tbl(i)).number_of_errored_lines
1697 := PO_PDOI_PARAMS.g_docs_info(l_line_locs.intf_header_id_tbl(i)).number_of_errored_lines +1;
1698
1699 END IF;
1700 -- bug4662687 END
1701 */
1702 END IF;
1703 END LOOP;
1704
1705 IF (PO_LOG.d_stmt) THEN
1706 PO_LOG.stmt(d_module, d_position, 'to-be-rejected locs',
1707 l_rej_intf_line_loc_id_tbl);
1708 END IF;
1709
1710 PO_PDOI_UTL.reject_line_locations_intf
1711 (
1712 p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_LINE_LOCATION_ID,
1713 p_id_tbl => l_rej_intf_line_loc_id_tbl,
1714 p_cascade => FND_API.g_TRUE
1715 );
1716 l_rej_intf_line_loc_id_tbl.DELETE;
1717
1718 d_position := 100;
1719
1720 -- keep track of lines that have location records processed
1721 l_count := 0;
1722 FOR i IN 1..l_line_locs.rec_count
1723 LOOP
1724 IF (NOT l_line_ref_tbl.EXISTS(l_line_locs.ln_po_line_id_tbl(i)) AND
1725 l_line_locs.error_flag_tbl(i) = FND_API.g_FALSE) THEN
1726 l_count := l_count + 1;
1727 l_processed_line_id_tbl(l_count) := l_line_locs.ln_po_line_id_tbl(i);
1728 l_processed_draft_id_tbl(l_count) := l_line_locs.draft_id_tbl(i);
1729 l_line_ref_tbl(l_line_locs.ln_po_line_id_tbl(i)) := i;
1730 END IF;
1731 END LOOP;
1732
1733 -- commit
1734 PO_PDOI_UTL.commit_work;
1735
1736 IF (l_line_locs.rec_count < PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE) THEN
1737 EXIT;
1738 END IF;
1739
1740 l_max_intf_line_loc_id := l_line_locs.intf_line_loc_id_tbl(l_line_locs.rec_count);
1741 EXCEPTION
1742 WHEN g_snap_shot_too_old THEN
1743 d_position := 110;
1744
1745 -- log error
1746 PO_MESSAGE_S.add_exc_msg
1747 (
1748 p_pkg_name => d_pkg_name,
1749 p_procedure_name => d_api_name || '.' || d_position
1750 );
1751
1752 -- commit changes
1753 PO_PDOI_UTL.commit_work;
1754
1755 IF (l_line_locs_csr%ISOPEN) THEN
1756 CLOSE l_line_locs_csr;
1757 PO_PDOI_LINE_LOC_PROCESS_PVT.open_line_locs
1758 (
1759 p_max_intf_line_loc_id => l_max_intf_line_loc_id,
1760 x_line_locs_csr => l_line_locs_csr
1761 );
1762 END IF;
1763 END;
1764 END LOOP;
1765
1766 d_position := 120;
1767
1768 -- close the cursor
1769 IF (l_line_locs_csr%ISOPEN) THEN
1770 CLOSE l_line_locs_csr;
1771 END IF;
1772
1773 -- line location post processing
1774 IF (PO_PDOI_PARAMS.g_request.document_type =
1775 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
1776 d_position := 130;
1777
1778 -- update amount or quantity value on po_lines_draft_all based
1779 -- on the values on shipment for Standard PO.
1780 PO_PDOI_LINE_LOC_PROCESS_PVT.update_amount_quantity_on_line
1781 (
1782 p_po_line_id_tbl => l_processed_line_id_tbl,
1783 p_draft_id_tbl => l_processed_draft_id_tbl
1784 );
1785 ELSIF (PO_PDOI_PARAMS.g_request.document_type =
1786 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) THEN
1787 d_position := 140;
1788
1789 -- delete all existing price breaks for quotation update
1790 PO_PDOI_LINE_LOC_PROCESS_PVT.delete_exist_price_breaks
1791 (
1792 p_po_line_id_tbl => l_processed_line_id_tbl,
1793 p_draft_id_tbl => l_processed_draft_id_tbl
1794 );
1795 ELSE -- document_type = blanket
1796 d_position := 150;
1797
1798 -- In previous implementation, price discount is updated based
1799 -- on price and price break. The logic is removed in R12.
1800 NULL;
1801 END IF;
1802
1803 PO_TIMING_UTL.stop_time(PO_PDOI_CONSTANTS.g_T_LINE_LOC_PROCESS);
1804
1805 IF (PO_LOG.d_proc) THEN
1806 PO_LOG.proc_end(d_module);
1807 END IF;
1808
1809 EXCEPTION
1810 WHEN OTHERS THEN
1811 PO_MESSAGE_S.add_exc_msg
1812 (
1813 p_pkg_name => d_pkg_name,
1814 p_procedure_name => d_api_name || '.' || d_position
1815 );
1816 RAISE;
1817 END process_line_locations;
1818
1819 -------------------------------------------------------------------------
1820 --Start of Comments
1821 --Name: process_distributions
1822 --Pre-reqs: None
1823 --Modifies:
1824 --Locks:
1825 -- None
1826 --Function:
1827 -- handle the logic to derive, default, validate and insert records
1828 -- from po_distributions_interface table
1829 --Parameters:
1830 --IN: None
1831 --IN OUT: None
1832 --OUT: None
1833 --Returns:
1834 --Notes:
1835 --Testing:
1836 --End of Comments
1837 ------------------------------------------------------------------------
1838 PROCEDURE process_distributions IS
1839
1840 d_api_name CONSTANT VARCHAR2(30) := 'process_distributions';
1841 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1842 d_position NUMBER;
1843
1844 -- record to hold distribution values read within a batch
1845 l_dists PO_PDOI_TYPES.distributions_rec_type;
1846
1847 -- cursor variable to point to currently processing row
1848 l_dists_csr PO_PDOI_TYPES.intf_cursor_type;
1849
1850 -- variables to track the records that need to be rejected due
1851 -- to errors in the processing
1852 l_rej_intf_dist_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
1853
1854 -- maximal interface_line_location_id_processed
1855 l_max_intf_dist_id NUMBER := -1;
1856
1857 BEGIN
1858 d_position := 0;
1859
1860 IF (PO_LOG.d_proc) THEN
1861 PO_LOG.proc_begin(d_module);
1862 END IF;
1863
1864 PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_DIST_PROCESS);
1865
1866 -- open cursor for the correct query
1867 PO_PDOI_DIST_PROCESS_PVT.open_dists
1868 (
1869 p_max_intf_dist_id => l_max_intf_dist_id,
1870 x_dists_csr => l_dists_csr
1871 );
1872
1873 d_position := 10;
1874
1875 -- fetch records from distribution interface table and process the records
1876 LOOP
1877 BEGIN
1878 -- fetch one batch of distribution records from query result
1879 PO_PDOI_DIST_PROCESS_PVT.fetch_dists
1880 (
1881 x_dists_csr => l_dists_csr,
1882 x_dists => l_dists
1883 );
1884
1885 d_position := 20;
1886
1887 -- number of records read in current batch
1888 l_dists.rec_count := l_dists.intf_dist_id_tbl.COUNT;
1889
1890 IF (PO_LOG.d_stmt) THEN
1891 PO_LOG.stmt(d_module, d_position, 'dist count in batch',
1892 l_dists.rec_count);
1893 END IF;
1894
1895 EXIT WHEN l_dists.rec_count = 0;
1896
1897 -- calculate max distribution num for each line location
1898 -- in this batch
1899 -- the result is saved and used in distribution processing
1900 PO_PDOI_MAINPROC_UTL_PVT.calculate_max_dist_num
1901 (
1902 p_line_loc_id_tbl => l_dists.loc_line_loc_id_tbl,
1903 p_draft_id_tbl => l_dists.draft_id_tbl
1904 );
1905
1906 d_position := 30;
1907
1908 -- check whether the distribution num is unique per line location
1909 PO_PDOI_MAINPROC_UTL_PVT.check_dist_num_unique
1910 (
1911 p_line_loc_id_tbl => l_dists.loc_line_loc_id_tbl,
1912 p_draft_id_tbl => l_dists.draft_id_tbl,
1913 p_intf_dist_id_tbl => l_dists.intf_dist_id_tbl,
1914 p_dist_num_tbl => l_dists.dist_num_tbl,
1915 x_dist_num_unique_tbl => l_dists.dist_num_unique_tbl
1916 );
1917
1918 d_position := 40;
1919
1920 -- derive logic
1921 PO_PDOI_DIST_PROCESS_PVT.derive_dists
1922 (
1923 x_dists => l_dists
1924 );
1925
1926 d_position := 50;
1927
1928 -- default logic
1929 PO_PDOI_DIST_PROCESS_PVT.default_dists
1930 (
1931 x_dists => l_dists
1932 );
1933
1934 d_position := 60;
1935
1936 -- validate logic
1937 PO_PDOI_DIST_PROCESS_PVT.validate_dists
1938 (
1939 x_dists => l_dists
1940 );
1941
1942 d_position := 70;
1943
1944 -- insert distributions into draft table
1945 PO_PDOI_MOVE_TO_DRAFT_TABS_PVT.insert_dists
1946 (
1947 p_dists => l_dists
1948 );
1949
1950 d_position := 80;
1951
1952 -- set rejected status to distributions
1953 FOR i IN 1..l_dists.rec_count
1954 LOOP
1955 IF (l_dists.error_flag_tbl(i) = FND_API.G_TRUE) THEN
1956 l_rej_intf_dist_id_tbl.EXTEND;
1957 l_rej_intf_dist_id_tbl(l_rej_intf_dist_id_tbl.COUNT) := l_dists.intf_dist_id_tbl(i);
1958 END IF;
1959 END LOOP;
1960
1961 IF (PO_LOG.d_stmt) THEN
1962 PO_LOG.stmt(d_module, d_position, 'to-be-rejected dists',
1963 l_rej_intf_dist_id_tbl);
1964 END IF;
1965
1966 d_position := 90;
1967
1968 PO_PDOI_UTL.reject_distributions_intf
1969 (
1970 p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_DISTRIBUTION_ID,
1971 p_id_tbl => l_rej_intf_dist_id_tbl
1972 );
1973 l_rej_intf_dist_id_tbl.DELETE;
1974
1975 d_position := 100;
1976
1977 -- set status to ACCEPTED for records without errors on intf table
1978 FORALL i IN 1..l_dists.intf_dist_id_tbl.COUNT
1979 UPDATE po_distributions_interface
1980 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_ACCEPTED
1981 WHERE interface_distribution_id = l_dists.intf_dist_id_tbl(i)
1982 AND l_dists.error_flag_tbl(i) = FND_API.g_FALSE;
1983
1984 d_position := 110;
1985
1986 PO_PDOI_UTL.commit_work;
1987
1988 IF (l_dists.rec_count < PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE) THEN
1989 EXIT;
1990 END IF;
1991
1992 l_max_intf_dist_id := l_dists.intf_dist_id_tbl(l_dists.rec_count);
1993 EXCEPTION
1994 WHEN g_snap_shot_too_old THEN
1995 d_position := 120;
1996
1997 -- log error
1998 PO_MESSAGE_S.add_exc_msg
1999 (
2000 p_pkg_name => d_pkg_name,
2001 p_procedure_name => d_api_name || '.' || d_position
2002 );
2003
2004 -- commit changes
2005 PO_PDOI_UTL.commit_work;
2006
2007 IF (l_dists_csr%ISOPEN) THEN
2008 CLOSE l_dists_csr;
2009 PO_PDOI_DIST_PROCESS_PVT.open_dists
2010 (
2011 p_max_intf_dist_id => l_max_intf_dist_id,
2012 x_dists_csr => l_dists_csr
2013 );
2014 END IF;
2015 END;
2016 END LOOP;
2017
2018 d_position := 130;
2019
2020 -- close the cursor
2021 IF (l_dists_csr%ISOPEN) THEN
2022 CLOSE l_dists_csr;
2023 END IF;
2024
2025 PO_TIMING_UTL.stop_time(PO_PDOI_CONSTANTS.g_T_DIST_PROCESS);
2026
2027 IF (PO_LOG.d_proc) THEN
2028 PO_LOG.proc_end(d_module);
2029 END IF;
2030
2031 EXCEPTION
2032 WHEN OTHERS THEN
2033 PO_MESSAGE_S.add_exc_msg
2034 (
2035 p_pkg_name => d_pkg_name,
2036 p_procedure_name => d_api_name || '.' || d_position
2037 );
2038 RAISE;
2039 END process_distributions;
2040
2041 -------------------------------------------------------------------------
2042 --Start of Comments
2043 --Name: process_price_diffs
2044 --Pre-reqs: None
2045 --Modifies:
2046 --Locks:
2047 -- None
2048 --Function:
2049 -- handle the logic to validate and insert records
2050 -- from po_price_diffs_interface table
2051 --Parameters:
2052 --IN: None
2053 --IN OUT: None
2054 --OUT: None
2055 --Returns:
2056 --Notes:
2057 --Testing:
2058 --End of Comments
2059 ------------------------------------------------------------------------
2060 PROCEDURE process_price_diffs IS
2061
2062 d_api_name CONSTANT VARCHAR2(30) := 'process_price_diffs';
2063 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2064 d_position NUMBER;
2065
2066 -- record to hold price differential values read within a batch
2067 l_price_diffs PO_PDOI_TYPES.price_diffs_rec_type;
2068
2069 -- cursor variable to point to currently processing row
2070 l_price_diffs_csr PO_PDOI_TYPES.intf_cursor_type;
2071
2072 -- variables to track the records that need to be rejected due
2073 -- to errors in the processing
2074 l_rej_intf_price_diff_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
2075
2076 -- maximal interface_price_diff_id_processed
2077 l_max_intf_price_diff_id NUMBER := -1;
2078
2079 BEGIN
2080 d_position := 0;
2081
2082 IF (PO_LOG.d_proc) THEN
2083 PO_LOG.proc_begin(d_module);
2084 END IF;
2085
2086 -- Bug 5215781:
2087 -- exit immediately if error threshold is hit for CATALOG UPLOAD
2088 IF (PO_PDOI_PARAMS.g_request.calling_module =
2089 PO_PDOI_CONSTANTS.g_CALL_MOD_CATALOG_UPLOAD AND
2090 PO_PDOI_PARAMS.g_docs_info(PO_PDOI_PARAMS.g_request.interface_header_id)
2091 .err_tolerance_exceeded = FND_API.g_TRUE) THEN
2092 IF (PO_LOG.d_stmt) THEN
2093 PO_LOG.stmt(d_module, d_position, 'Exit from process_price_diffs since' ||
2094 ' error threshold is hit for header ',
2095 PO_PDOI_PARAMS.g_request.interface_header_id);
2096 END IF;
2097
2098 RETURN;
2099 END IF;
2100
2101 d_position := 5;
2102
2103 PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_PRICE_DIFF_PROCESS);
2104
2105 -- open cursor for the correct query
2106 PO_PDOI_PRICE_DIFF_PROCESS_PVT.open_price_diffs
2107 (
2108 p_max_intf_price_diff_id => l_max_intf_price_diff_id,
2109 x_price_diffs_csr => l_price_diffs_csr
2110 );
2111
2112 d_position := 10;
2113
2114 -- fetch records from price differential interface table and process the records
2115 LOOP
2116 -- Bug 5215781:
2117 -- check whether num of error lines exceeds the error
2118 -- threshold for the previous batch
2119 IF (PO_PDOI_PARAMS.g_request.calling_module =
2120 PO_PDOI_CONSTANTS.g_CALL_MOD_CATALOG_UPLOAD AND
2121 PO_PDOI_PARAMS.g_docs_info(PO_PDOI_PARAMS.g_request.interface_header_id)
2122 .err_tolerance_exceeded = FND_API.g_TRUE) THEN
2123 d_position := 20;
2124
2125 IF (PO_LOG.d_stmt) THEN
2126 PO_LOG.stmt(d_module, d_position, 'error tolerance exceeded for',
2127 PO_PDOI_PARAMS.g_request.interface_header_id);
2128 END IF;
2129
2130 EXIT;
2131 END IF;
2132
2133 BEGIN
2134 -- fetch one batch of records from query result
2135 PO_PDOI_PRICE_DIFF_PROCESS_PVT.fetch_price_diffs
2136 (
2137 x_price_diffs_csr => l_price_diffs_csr,
2138 x_price_diffs => l_price_diffs
2139 );
2140
2141 d_position := 30;
2142
2143 -- number of records read in current batch
2144 l_price_diffs.rec_count := l_price_diffs.intf_price_diff_id_tbl.COUNT;
2145
2146 IF (PO_LOG.d_stmt) THEN
2147 PO_LOG.stmt(d_module, d_position, 'price diffs in batch',
2148 l_price_diffs.rec_count);
2149 END IF;
2150
2151 EXIT WHEN l_price_diffs.rec_count = 0;
2152
2153 PO_PDOI_PRICE_DIFF_PROCESS_PVT.default_price_diffs
2154 (
2155 x_price_diffs => l_price_diffs
2156 );
2157
2158 d_position := 40;
2159
2160 -- validate logic
2161 PO_PDOI_PRICE_DIFF_PROCESS_PVT.validate_price_diffs
2162 (
2163 x_price_diffs => l_price_diffs
2164 );
2165
2166 d_position := 50;
2167
2168 -- insert into draft table
2169 PO_PDOI_MOVE_TO_DRAFT_TABS_PVT.insert_price_diffs
2170 (
2171 p_price_diffs => l_price_diffs
2172 );
2173
2174 d_position := 60;
2175
2176 -- set rejected status
2177 FOR i IN 1..l_price_diffs.rec_count
2178 LOOP
2179 IF (l_price_diffs.error_flag_tbl(i) = FND_API.G_TRUE) THEN
2180 l_rej_intf_price_diff_id_tbl.EXTEND;
2181 l_rej_intf_price_diff_id_tbl(l_rej_intf_price_diff_id_tbl.COUNT) := l_price_diffs.intf_price_diff_id_tbl(i);
2182 END IF;
2183 END LOOP;
2184
2185 IF (PO_LOG.d_stmt) THEN
2186 PO_LOG.stmt(d_module, d_position, 'to-be-rejected price diffs',
2187 l_rej_intf_price_diff_id_tbl);
2188 END IF;
2189
2190 PO_PDOI_UTL.reject_price_diff_intf
2191 (
2192 p_id_param_type => PO_PDOI_CONSTANTS.g_PRICE_DIFF_INTERFACE_ID,
2193 p_id_tbl => l_rej_intf_price_diff_id_tbl
2194 );
2195 l_rej_intf_price_diff_id_tbl.DELETE;
2196
2197 d_position := 70;
2198
2199 -- set status to ACCEPTED for records without errors on intf table
2200 FORALL i IN 1..l_price_diffs.intf_price_diff_id_tbl.COUNT
2201 UPDATE po_price_diff_interface
2202 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_ACCEPTED
2203 WHERE price_diff_interface_id = l_price_diffs.intf_price_diff_id_tbl(i)
2204 AND l_price_diffs.error_flag_tbl(i) = FND_API.g_FALSE;
2205
2206 PO_PDOI_UTL.commit_work;
2207
2208 IF (l_price_diffs.rec_count < PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE) THEN
2209 EXIT;
2210 END IF;
2211
2212 l_max_intf_price_diff_id := l_price_diffs.intf_price_diff_id_tbl(l_price_diffs.rec_count);
2213 EXCEPTION
2214 WHEN g_snap_shot_too_old THEN
2215 d_position := 80;
2216
2217 -- log error
2218 PO_MESSAGE_S.add_exc_msg
2219 (
2220 p_pkg_name => d_pkg_name,
2221 p_procedure_name => d_api_name || '.' || d_position
2222 );
2223
2224 -- commit changes
2225 PO_PDOI_UTL.commit_work;
2226
2227 IF (l_price_diffs_csr%ISOPEN) THEN
2228 CLOSE l_price_diffs_csr;
2229 PO_PDOI_PRICE_DIFF_PROCESS_PVT.open_price_diffs
2230 (
2231 p_max_intf_price_diff_id => l_max_intf_price_diff_id,
2232 x_price_diffs_csr => l_price_diffs_csr
2233 );
2234 END IF;
2235 END;
2236 END LOOP;
2237
2238 d_position := 90;
2239
2240 -- close the cursor
2241 IF (l_price_diffs_csr%ISOPEN) THEN
2242 CLOSE l_price_diffs_csr;
2243 END IF;
2244
2245 PO_TIMING_UTL.stop_time(PO_PDOI_CONSTANTS.g_T_PRICE_DIFF_PROCESS);
2246
2247 IF (PO_LOG.d_proc) THEN
2248 PO_LOG.proc_end(d_module);
2249 END IF;
2250
2251 EXCEPTION
2252 WHEN OTHERS THEN
2253 PO_MESSAGE_S.add_exc_msg
2254 (
2255 p_pkg_name => d_pkg_name,
2256 p_procedure_name => d_api_name || '.' || d_position
2257 );
2258 RAISE;
2259 END process_price_diffs;
2260
2261 -------------------------------------------------------------------------
2262 --Start of Comments
2263 --Name: process_attributes
2264 --Pre-reqs: None
2265 --Modifies:
2266 --Locks:
2267 -- None
2268 --Function:
2269 -- handle the logic to validate and insert records
2270 -- from po_attr_values_interface table and
2271 -- po_attr_values_tlp_interface table
2272 --Parameters:
2273 --IN: None
2274 --IN OUT: None
2275 --OUT: None
2276 --Returns:
2277 --Notes:
2278 --Testing:
2279 --End of Comments
2280 ------------------------------------------------------------------------
2281 PROCEDURE process_attributes IS
2282
2283 d_api_name CONSTANT VARCHAR2(30) := 'process_attributes';
2284 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2285 d_position NUMBER;
2286
2287 BEGIN
2288 d_position := 0;
2289
2290 IF (PO_LOG.d_proc) THEN
2291 PO_LOG.proc_begin(d_module);
2292 END IF;
2293
2294 -- Bug 5215781:
2295 -- exit immediately if error threshold is hit for CATALOG UPLOAD
2296 IF (PO_PDOI_PARAMS.g_request.calling_module =
2297 PO_PDOI_CONSTANTS.g_CALL_MOD_CATALOG_UPLOAD AND
2298 PO_PDOI_PARAMS.g_docs_info(PO_PDOI_PARAMS.g_request.interface_header_id)
2299 .err_tolerance_exceeded = FND_API.g_TRUE) THEN
2300 IF (PO_LOG.d_stmt) THEN
2301 PO_LOG.stmt(d_module, d_position, 'Exit from process_attributes since' ||
2302 ' error threshold is hit for header ',
2303 PO_PDOI_PARAMS.g_request.interface_header_id);
2304 END IF;
2305
2306 RETURN;
2307 END IF;
2308
2309 d_position := 10;
2310
2311 PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_ATTR_PROCESS);
2312
2313 -- process all lines in po_attr_values_interface table
2314 process_attr_values;
2315
2316 d_position := 20;
2317
2318 -- process all lines in po_attr_values_tlp_interface table
2319 process_attr_values_tlp;
2320
2321 d_position := 30;
2322
2323 -- add default attr_values and attr_values_tlp if not provided
2324 PO_PDOI_ATTR_PROCESS_PVT.add_default_attrs;
2325
2326 d_position := 40;
2327
2328 PO_TIMING_UTL.stop_time(PO_PDOI_CONSTANTS.g_T_ATTR_PROCESS);
2329
2330 IF (PO_LOG.d_proc) THEN
2331 PO_LOG.proc_end(d_module);
2332 END IF;
2333
2334 EXCEPTION
2335 WHEN OTHERS THEN
2336 PO_MESSAGE_S.add_exc_msg
2337 (
2338 p_pkg_name => d_pkg_name,
2339 p_procedure_name => d_api_name || '.' || d_position
2340 );
2341 RAISE;
2342 END process_attributes;
2343
2344 -------------------------------------------------------------------------
2345 --Start of Comments
2346 --Name: process_attr_values
2347 --Pre-reqs: None
2348 --Modifies:
2349 --Locks:
2350 -- None
2351 --Function:
2352 -- process records in po_attr_values_interface table;
2353 -- currently, there is only validation logic, no
2354 -- derivation and defaulting logic
2355 --Parameters:
2356 --IN: None
2357 --IN OUT: None
2358 --OUT: None
2359 --Returns:
2360 --Notes:
2361 --Testing:
2362 --End of Comments
2363 ------------------------------------------------------------------------
2364 PROCEDURE process_attr_values IS
2365
2366 d_api_name CONSTANT VARCHAR2(30) := 'process_attr_values';
2367 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2368 d_position NUMBER;
2369
2370 -- record to hold attribute values read within a batch
2371 l_attr_values PO_PDOI_TYPES.attr_values_rec_type;
2372
2373 -- cursor variable to point to currently processing row
2374 l_attr_values_csr PO_PDOI_TYPES.intf_cursor_type;
2375
2376 -- pl/sql table to track rows that needs to be created or updated
2377 l_merge_row_tbl DBMS_SQL.NUMBER_TABLE;
2378
2379 -- pl/sql table to track rows that need to be synced from txn
2380 -- table to draft table
2381 l_sync_attr_id_tbl PO_TBL_NUMBER;
2382 l_sync_draft_id_tbl PO_TBL_NUMBER;
2383
2384 -- maximal interface_attr_values_id_processed
2385 l_max_intf_attr_values_id NUMBER := -1;
2386
2387 -- index for the loop
2388 l_index NUMBER;
2389
2390 l_processing_row_tbl DBMS_SQL.NUMBER_TABLE;
2391
2392 BEGIN
2393 d_position := 0;
2394
2395 IF (PO_LOG.d_proc) THEN
2396 PO_LOG.proc_begin(d_module);
2397 END IF;
2398
2399 -- open cursor for query to retrieve attr value records
2400 PO_PDOI_ATTR_PROCESS_PVT.open_attr_values
2401 (
2402 p_max_intf_attr_values_id => l_max_intf_attr_values_id,
2403 x_attr_values_csr => l_attr_values_csr
2404 );
2405
2406 d_position := 10;
2407
2408 -- fetch records from attr values interface table and process the records
2409 LOOP
2410 BEGIN
2411 -- fetch one batch of records from query result
2412 PO_PDOI_ATTR_PROCESS_PVT.fetch_attr_values
2413 (
2414 x_attr_values_csr => l_attr_values_csr,
2415 x_attr_values => l_attr_values
2416 );
2417
2418 d_position := 20;
2419
2420 -- number of records read in current batch
2421 l_attr_values.rec_count := l_attr_values.intf_attr_values_id_tbl.COUNT;
2422
2423 IF (PO_LOG.d_stmt) THEN
2424 PO_LOG.stmt(d_module, d_position, 'attr values in batch',
2425 l_attr_values.rec_count);
2426 END IF;
2427
2428 EXIT WHEN l_attr_values.rec_count = 0;
2429
2430 -- process each record to determine the action;
2431 -- if multiple records are pointing to same
2432 -- po_line_id, the record will be processed
2433 -- in different groups
2434
2435 -- first setup table to track rows that have not been processed
2436 PO_PDOI_UTL.generate_ordered_num_list
2437 (
2438 p_size => l_attr_values.rec_count,
2439 x_num_list => l_processing_row_tbl
2440 );
2441
2442 -- second, process records in groups
2443 LOOP
2444 d_position := 30;
2445
2446 -- exit when all rows are processed
2447 IF (l_processing_row_tbl.COUNT = 0) THEN
2448 IF (PO_LOG.d_stmt) THEN
2449 PO_LOG.stmt(d_module, d_position, 'exit after all rows are processed');
2450 END IF;
2451
2452 EXIT;
2453 END IF;
2454
2455 -- determine the action for each attr value record
2456 PO_PDOI_ATTR_PROCESS_PVT.check_attr_actions
2457 (
2458 x_processing_row_tbl => l_processing_row_tbl,
2459 x_attr_values => l_attr_values,
2460 x_merge_row_tbl => l_merge_row_tbl,
2461 x_sync_attr_id_tbl => l_sync_attr_id_tbl,
2462 x_sync_draft_id_tbl => l_sync_draft_id_tbl
2463 );
2464
2465 d_position := 40;
2466
2467 -- insert records into draft table
2468 PO_PDOI_MOVE_TO_DRAFT_TABS_PVT.merge_attr_values
2469 (
2470 p_processing_row_tbl => l_merge_row_tbl,
2471 p_sync_attr_id_tbl => l_sync_attr_id_tbl,
2472 p_sync_draft_id_tbl => l_sync_draft_id_tbl,
2473 p_attr_values => l_attr_values
2474 );
2475 END LOOP;
2476
2477 d_position := 50;
2478
2479 -- set status to ACCEPTED for all records
2480 FORALL i IN 1..l_attr_values.intf_attr_values_id_tbl.COUNT
2481 UPDATE po_attr_values_interface
2482 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_ACCEPTED
2483 WHERE interface_attr_values_id = l_attr_values.intf_attr_values_id_tbl(i)
2484 AND l_attr_values.error_flag_tbl(i) = FND_API.g_FALSE;
2485
2486 PO_PDOI_UTL.commit_work;
2487
2488 IF (l_attr_values.rec_count < PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE) THEN
2489 EXIT;
2490 END IF;
2491
2492 l_max_intf_attr_values_id := l_attr_values.intf_attr_values_id_tbl(l_attr_values.rec_count);
2493 EXCEPTION
2494 WHEN g_snap_shot_too_old THEN
2495 d_position := 60;
2496
2497 -- log error
2498 PO_MESSAGE_S.add_exc_msg
2499 (
2500 p_pkg_name => d_pkg_name,
2501 p_procedure_name => d_api_name || '.' || d_position
2502 );
2503
2504 -- commit changes
2505 PO_PDOI_UTL.commit_work;
2506
2507 IF (l_attr_values_csr%ISOPEN) THEN
2508 CLOSE l_attr_values_csr;
2509 PO_PDOI_ATTR_PROCESS_PVT.open_attr_values
2510 (
2511 p_max_intf_attr_values_id => l_max_intf_attr_values_id,
2512 x_attr_values_csr => l_attr_values_csr
2513 );
2514 END IF;
2515 END;
2516 END LOOP;
2517
2518 d_position := 70;
2519
2520 -- close the cursor
2521 IF (l_attr_values_csr%ISOPEN) THEN
2522 CLOSE l_attr_values_csr;
2523 END IF;
2524
2525 IF (PO_LOG.d_proc) THEN
2526 PO_LOG.proc_end(d_module);
2527 END IF;
2528
2529 EXCEPTION
2530 WHEN OTHERS THEN
2531 PO_MESSAGE_S.add_exc_msg
2532 (
2533 p_pkg_name => d_pkg_name,
2534 p_procedure_name => d_api_name || '.' || d_position
2535 );
2536 RAISE;
2537 END process_attr_values;
2538
2539 -------------------------------------------------------------------------
2540 --Start of Comments
2541 --Name: process_attr_values_tlp
2542 --Pre-reqs: None
2543 --Modifies:
2544 --Locks:
2545 -- None
2546 --Function:
2547 -- handle the logic on records from po_attr_values_tlp_interface table
2548 --Parameters:
2549 --IN: None
2550 --IN OUT: None
2551 --OUT: None
2552 --Returns:
2553 --Notes:
2554 --Testing:
2555 --End of Comments
2556 ------------------------------------------------------------------------
2557 PROCEDURE process_attr_values_tlp IS
2558
2559 d_api_name CONSTANT VARCHAR2(30) := 'process_attr_values_tlp';
2560 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2561 d_position NUMBER;
2562
2563 -- record to hold attribute tlp values read within a batch
2564 l_attr_values_tlp PO_PDOI_TYPES.attr_values_tlp_rec_type;
2565
2566 -- cursor variable to point to currently processing row
2567 l_attr_values_tlp_csr PO_PDOI_TYPES.intf_cursor_type;
2568
2569 -- pl/sql table to track rows that needs to be created or updated
2570 l_merge_row_tbl DBMS_SQL.NUMBER_TABLE;
2571
2572 -- pl/sql table to track rows that need to be synced from txn
2573 -- table to draft table
2574 l_sync_attr_tlp_id_tbl PO_TBL_NUMBER;
2575 l_sync_draft_id_tbl PO_TBL_NUMBER;
2576
2577 -- maximal interface_attr_values_tlp_id_processed
2578 l_max_intf_attr_values_tlp_id NUMBER := -1;
2579
2580 l_processing_row_tbl DBMS_SQL.NUMBER_TABLE;
2581 l_index NUMBER;
2582
2583 BEGIN
2584 d_position := 0;
2585
2586 IF (PO_LOG.d_proc) THEN
2587 PO_LOG.proc_begin(d_module);
2588 END IF;
2589
2590 -- open cursor for query to retrieve attr value records
2591 PO_PDOI_ATTR_PROCESS_PVT.open_attr_values_tlp
2592 (
2593 p_max_intf_attr_values_tlp_id => l_max_intf_attr_values_tlp_id,
2594 x_attr_values_tlp_csr => l_attr_values_tlp_csr
2595 );
2596
2597 d_position := 10;
2598
2599 -- fetch records from attr values tlp interface table and process the records
2600 LOOP
2601 BEGIN
2602 -- fetch one batch of records from query result
2603 PO_PDOI_ATTR_PROCESS_PVT.fetch_attr_values_tlp
2604 (
2605 x_attr_values_tlp_csr => l_attr_values_tlp_csr,
2606 x_attr_values_tlp => l_attr_values_tlp
2607 );
2608
2609 d_position := 20;
2610
2611 -- number of records read in current batch
2612 l_attr_values_tlp.rec_count := l_attr_values_tlp.intf_attr_values_tlp_id_tbl.COUNT;
2613
2614 IF (PO_LOG.d_stmt) THEN
2615 PO_LOG.stmt(d_module, d_position, 'attr values tlp in batch',
2616 l_attr_values_tlp.rec_count);
2617 END IF;
2618
2619 EXIT WHEN l_attr_values_tlp.rec_count = 0;
2620
2621 -- process each record to determine the action;
2622 -- if multiple records are pointing to same
2623 -- po_line_id and language, the record will be processed
2624 -- in different groups
2625
2626 -- first setup table to track rows that have not been processed
2627 PO_PDOI_UTL.generate_ordered_num_list
2628 (
2629 p_size => l_attr_values_tlp.rec_count,
2630 x_num_list => l_processing_row_tbl
2631 );
2632
2633 -- second, process records in groups
2634 LOOP
2635 d_position := 20;
2636
2637 -- exit when all rows are processed
2638 IF (l_processing_row_tbl.COUNT = 0) THEN
2639 IF (PO_LOG.d_stmt) THEN
2640 PO_LOG.stmt(d_module, d_position, 'exit after all rows are processed');
2641 END IF;
2642
2643 EXIT;
2644 END IF;
2645
2646 -- determine the action for each attr value tlp record
2647 PO_PDOI_ATTR_PROCESS_PVT.check_attr_tlp_actions
2648 (
2649 x_processing_row_tbl => l_processing_row_tbl,
2650 x_attr_values_tlp => l_attr_values_tlp,
2651 x_merge_row_tbl => l_merge_row_tbl,
2652 x_sync_attr_tlp_id_tbl => l_sync_attr_tlp_id_tbl,
2653 x_sync_draft_id_tbl => l_sync_draft_id_tbl
2654 );
2655
2656 d_position := 30;
2657
2658 -- merge records into draft table
2659 PO_PDOI_MOVE_TO_DRAFT_TABS_PVT.merge_attr_values_tlp
2660 (
2661 p_processing_row_tbl => l_merge_row_tbl,
2662 p_sync_attr_tlp_id_tbl => l_sync_attr_tlp_id_tbl,
2663 p_sync_draft_id_tbl => l_sync_draft_id_tbl,
2664 p_attr_values_tlp => l_attr_values_tlp
2665 );
2666 END LOOP;
2667
2668 d_position := 40;
2669
2670 -- set status to ACCEPTED for all records
2671 FORALL i IN 1..l_attr_values_tlp.intf_attr_values_tlp_id_tbl.COUNT
2672 UPDATE po_attr_values_tlp_interface
2673 SET process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_ACCEPTED
2674 WHERE interface_attr_values_tlp_id = l_attr_values_tlp.intf_attr_values_tlp_id_tbl(i)
2675 AND l_attr_values_tlp.error_flag_tbl(i) = FND_API.g_FALSE;
2676
2677 PO_PDOI_UTL.commit_work;
2678
2679 IF (l_attr_values_tlp.rec_count < PO_PDOI_CONSTANTS.g_DEF_BATCH_SIZE) THEN
2680 EXIT;
2681 END IF;
2682
2683 l_max_intf_attr_values_tlp_id := l_attr_values_tlp.intf_attr_values_tlp_id_tbl(l_attr_values_tlp.rec_count);
2684 EXCEPTION
2685 WHEN g_snap_shot_too_old THEN
2686 d_position := 50;
2687
2688 -- log error
2689 PO_MESSAGE_S.add_exc_msg
2690 (
2691 p_pkg_name => d_pkg_name,
2692 p_procedure_name => d_api_name || '.' || d_position
2693 );
2694
2695 -- commit changes
2696 PO_PDOI_UTL.commit_work;
2697
2698 IF (l_attr_values_tlp_csr%ISOPEN) THEN
2699 CLOSE l_attr_values_tlp_csr;
2700 PO_PDOI_ATTR_PROCESS_PVT.open_attr_values_tlp
2701 (
2702 p_max_intf_attr_values_tlp_id => l_max_intf_attr_values_tlp_id,
2703 x_attr_values_tlp_csr => l_attr_values_tlp_csr
2704 );
2705 END IF;
2706 END;
2707 END LOOP;
2708
2709 d_position := 60;
2710
2711 -- close the cursor
2712 IF (l_attr_values_tlp_csr%ISOPEN) THEN
2713 CLOSE l_attr_values_tlp_csr;
2714 END IF;
2715
2716 IF (PO_LOG.d_proc) THEN
2717 PO_LOG.proc_end(d_module);
2718 END IF;
2719
2720 EXCEPTION
2721 WHEN OTHERS THEN
2722 PO_MESSAGE_S.add_exc_msg
2723 (
2724 p_pkg_name => d_pkg_name,
2725 p_procedure_name => d_api_name || '.' || d_position
2726 );
2727 RAISE;
2728 END process_attr_values_tlp;
2729
2730 END PO_PDOI_MAINPROC_PVT;
[Home] [Help]