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