1 PACKAGE BODY PO_PDOI_HEADER_GROUPING_PVT AS
2 /* $Header: PO_PDOI_HEADER_GROUPING_PVT.plb 120.10 2006/07/24 18:35:38 bao noship $ */
3
4 d_pkg_name CONSTANT VARCHAR2(50) :=
5 PO_LOG.get_package_base('PO_PDOI_HEADER_GROUPING_PVT');
6
7 -------------------------------------------------------
8 ----------- PRIVATE PROCEDURES PROTOTYPE --------------
9 -------------------------------------------------------
10
11 PROCEDURE assign_round_num
12 ( x_all_headers_processed OUT NOCOPY VARCHAR2
13 );
14
15 PROCEDURE assign_draft_id;
16
17 PROCEDURE expire_lines_by_catalog_name;
18
19 PROCEDURE init_doc_info_tbl
20 ( p_intf_header_id_tbl PO_TBL_NUMBER
21 );
22
23 PROCEDURE check_new_draft_needed
24 ( p_draft_id IN NUMBER,
25 p_status IN VARCHAR2,
26 x_new_draft_needed OUT NOCOPY VARCHAR2
27 );
28
29 -------------------------------------------------------
30 -------------- PUBLIC PROCEDURES ----------------------
31 -------------------------------------------------------
32
33 -----------------------------------------------------------------------
34 --Start of Comments
35 --Name: process
36 --Function:
37 -- Main process for header grouping
38 --Parameters:
39 --IN:
40 --IN OUT:
41 --OUT:
42 --x_all_headers_processed
43 -- FND_API.G_TRUE if all the headers are processed
44 -- FND_API.G_FALSE otherwise
45 --End of Comments
46 ------------------------------------------------------------------------
47 PROCEDURE process
48 ( x_all_headers_processed OUT NOCOPY VARCHAR2
49 ) IS
50
51 d_api_name CONSTANT VARCHAR2(30) := 'process';
52 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
53 d_position NUMBER;
54
55 BEGIN
56 d_position := 0;
57
58 IF (PO_LOG.d_proc) THEN
59 PO_LOG.proc_begin (d_module);
60 END IF;
61
62 PO_TIMING_UTL.start_time(PO_PDOI_CONSTANTS.g_T_HEADER_GROUPING);
63
64 d_position := 10;
65
66 -- set round number for interface records that will be processed in
67 -- current round
68 assign_round_num
69 ( x_all_headers_processed => x_all_headers_processed );
70
71 d_position := 20;
72
73 -- create draft for records that will be processed in current round
74 IF (x_all_headers_processed = FND_API.G_FALSE) THEN
75 assign_draft_id;
76
77 d_position := 30;
78
79 -- If lines for a particular catalog name will get expired, move those
80 -- records to draft table and set expiration date for those lines
81 IF (PO_PDOI_PARAMS.g_request.calling_module =
82 PO_PDOI_CONSTANTS.g_CALL_MOD_CATALOG_UPLOAD
83 AND
84 PO_PDOI_PARAMS.g_request.catalog_to_expire IS NOT NULL) THEN
85
86 expire_lines_by_catalog_name;
87 END IF;
88 END IF;
89
90 d_position := 40;
91
92 PO_INTERFACE_ERRORS_UTL.flush_errors_tbl;
93
94 d_position := 50;
95
96 PO_PDOI_UTL.commit_work;
97
98 PO_TIMING_UTL.stop_time(PO_PDOI_CONSTANTS.g_T_HEADER_GROUPING);
99
100 IF (PO_LOG.d_proc) THEN
101 PO_LOG.proc_end (d_module);
102 END IF;
103
104 EXCEPTION
105 WHEN OTHERS THEN
106 PO_MESSAGE_S.add_exc_msg
107 ( p_pkg_name => d_pkg_name,
108 p_procedure_name => d_api_name || '.' || d_position
109 );
110 RAISE;
111 END process;
112
113
114 -------------------------------------------------------
115 -------------- PRIVATE PROCEDURES ---------------------
116 -------------------------------------------------------
117
118 -----------------------------------------------------------------------
119 --Start of Comments
120 --Name: assign_round_num
121 --Function:
122 -- For documents that can be processed in the same round, stamp those
123 -- record with processing_round_num = current_round_num so that they
124 -- will be processed later on
125 --Parameters:
126 --IN:
127 --IN OUT:
128 --OUT:
129 --x_all_headers_processed
130 -- FND_API.G_TRUE if all the headers are processed
131 -- FND_API.G_FALSE otherwise
132 --End of Comments
133 ------------------------------------------------------------------------
134 PROCEDURE assign_round_num
135 ( x_all_headers_processed OUT NOCOPY VARCHAR2
136 ) IS
137
138 d_api_name CONSTANT VARCHAR2(30) := 'assign_round_num';
139 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
140 d_position NUMBER;
141
142 l_process_update_replace BOOLEAN := FALSE;
143
144 l_intf_header_id_tbl PO_TBL_NUMBER;
145 l_po_header_id_tbl PO_TBL_NUMBER;
146
147 l_process_list DBMS_SQL.NUMBER_TABLE;
148
149 BEGIN
150 d_position := 0;
151
152 IF (PO_LOG.d_proc) THEN
153 PO_LOG.proc_begin (d_module);
154 END IF;
155
156 x_all_headers_processed := FND_API.G_FALSE;
157
158 -- Increment round number
159 PO_PDOI_PARAMS.g_current_round_num := PO_PDOI_PARAMS.g_current_round_num + 1;
160
161 IF (PO_LOG.d_stmt) THEN
162 PO_LOG.stmt(d_module, d_position, 'Current Round Number: ' ||
163 PO_PDOI_PARAMS.g_current_round_num);
164 END IF;
165
166 IF (PO_PDOI_PARAMS.g_original_doc_processed = FND_API.G_FALSE) THEN
167 d_position := 10;
168
169 -- First, we need to process NEW documents
170
171 SELECT interface_header_id
172 BULK COLLECT
173 INTO l_intf_header_id_tbl
174 FROM (SELECT interface_header_id
175 FROM po_headers_interface
176 WHERE processing_round_num IS NULL
177 AND processing_id = PO_PDOI_PARAMS.g_processing_id
178 AND action IN (PO_PDOI_CONSTANTS.g_action_ORIGINAL,
179 PO_PDOI_CONSTANTS.g_action_ADD)
180 ORDER BY interface_header_id)
181 WHERE rownum <= PO_PDOI_PARAMS.g_request.batch_size;
182
183 IF (SQL%ROWCOUNT = 0) THEN
184 d_position := 20;
185
186 -- mark g_original_doc_processed to TRUE so that we won't process
187 -- NEW documents the next time we come here
188 PO_PDOI_PARAMS.g_original_doc_processed := FND_API.G_TRUE;
189
190 IF (PO_LOG.d_stmt) THEN
191 PO_LOG.stmt(d_module, d_position, 'Finished progressing doc with ' ||
192 'action = original ');
193 END IF;
194
195 -- IF there is no new doucment to create, we can go ahead and look for
196 -- documents to update/replace
197 l_process_update_replace := TRUE;
198 ELSE
199
200 d_position := 30;
201
202 FORALL i IN 1..l_intf_header_id_tbl.COUNT
203 UPDATE po_headers_interface
204 SET processing_round_num = PO_PDOI_PARAMS.g_current_round_num
205 WHERE interface_header_id = l_intf_header_id_tbl(i);
206
207 IF (PO_LOG.d_stmt) THEN
208 PO_LOG.stmt(d_module, d_position, 'updated ' || SQL%ROWCOUNT ||
209 'rows with current round number ');
210 END IF;
211 END IF;
212
213 ELSE
214 d_position := 40;
215
216 l_process_update_replace := TRUE;
217 END IF;
218
219
220 IF (l_process_update_replace) THEN
221 d_position := 50;
222
223 -- if there are multiple records in headers interface, they can be processed
224 -- together only if they are not acting on the same document.
225
226 SELECT interface_header_id,
227 po_header_id
228 BULK COLLECT
229 INTO l_intf_header_id_tbl,
230 l_po_header_id_tbl
231 FROM po_headers_interface
232 WHERE processing_round_num IS NULL
233 AND processing_id = PO_PDOI_PARAMS.g_processing_id
234 AND po_header_id IS NOT NULL
235 AND action IN (PO_PDOI_CONSTANTS.g_action_UPDATE,
236 PO_PDOI_CONSTANTS.g_action_REPLACE)
237 ORDER BY interface_header_id;
238
239 d_position := 60;
240
241 FOR i IN 1..l_intf_header_id_tbl.COUNT LOOP
242 -- Use an associative array to figure out whether there is already
243 -- another header interface record in the same round that tries to
244 -- update the same document. If so, defer the current update to the
245 -- next round.
246
247 IF (l_po_header_id_tbl IS NOT NULL AND
248 NOT l_process_list.EXISTS(l_po_header_id_tbl(i))) THEN
249 l_process_list(l_po_header_id_tbl(i)) := l_intf_header_id_tbl(i);
250 END IF;
251
252 IF (l_process_list.COUNT >= PO_PDOI_PARAMS.g_request.batch_size) THEN
253 IF (PO_LOG.d_stmt) THEN
254 PO_LOG.stmt(d_module, d_position, '# of documents to process has ' ||
255 'reached the limit. Wait for the next round');
256 END IF;
257
258 -- exit the loop if the number of document to process exceeds
259 -- the specified limit
260 EXIT;
261 END IF;
262 END LOOP;
263
264 d_position := 70;
265
266 IF (l_process_list.COUNT > 0) THEN
267 -- assign round number for those in process list
268 FORALL i IN INDICES OF l_process_list
269 UPDATE po_headers_interface
270 SET processing_round_num = PO_PDOI_PARAMS.g_current_round_num
271 WHERE interface_header_id = l_process_list(i);
272
273 IF (PO_LOG.d_stmt) THEN
274 PO_LOG.stmt(d_module, d_position, 'updated ' || SQL%ROWCOUNT ||
275 'rows with current round number ');
276 END IF;
277
278 ELSE
279
280 IF (PO_LOG.d_stmt) THEN
281 PO_LOG.stmt(d_module, d_position, '** No more doc to process ***');
282 END IF;
283
284 x_all_headers_processed := FND_API.G_TRUE;
285 END IF;
286
287 END IF;
288
289 IF (PO_LOG.d_proc) THEN
290 PO_LOG.proc_end (d_module);
291 END IF;
292
293 EXCEPTION
294 WHEN OTHERS THEN
295 PO_MESSAGE_S.add_exc_msg
296 ( p_pkg_name => d_pkg_name,
297 p_procedure_name => d_api_name || '.' || d_position
298 );
299 RAISE;
300 END assign_round_num;
301
302
303
304 -----------------------------------------------------------------------
305 --Start of Comments
306 --Name: assign_draft_id
307 --Function:
308 -- Stamp header interface records with draft ids. If the document the
309 -- interface record tries to process already has a draft version, reuse
310 -- the draft; otherwise, create a new draft
311 --Parameters:
312 --IN:
313 --IN OUT:
314 --OUT:
315 --End of Comments
316 ------------------------------------------------------------------------
317 PROCEDURE assign_draft_id IS
318
319 d_api_name CONSTANT VARCHAR2(30) := 'assign_draft_id';
320 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
321 d_position NUMBER;
322
323 l_draft_id_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
324 l_reject_list PO_TBL_NUMBER := PO_TBL_NUMBER();
325
326 l_new_dft_idx_tbl PO_PDOI_UTL.pls_integer_tbl_type :=
327 PO_PDOI_UTL.pls_integer_tbl_type();
328 l_existing_dft_idx_tbl PO_PDOI_UTL.pls_integer_tbl_type :=
329 PO_PDOI_UTL.pls_integer_tbl_type();
330
331 l_intf_header_id_tbl PO_TBL_NUMBER;
332 l_po_header_id_tbl PO_TBL_NUMBER;
333 l_action_tbl PO_TBL_VARCHAR30;
334 l_revision_num_tbl PO_TBL_NUMBER;
335
336 l_tmp_draft_id PO_DRAFTS.draft_id%TYPE;
337 l_tmp_draft_status PO_DRAFTS.status%TYPE;
338 l_tmp_draft_owner_role PO_DRAFTS.owner_role%TYPE;
339 l_new_draft_needed VARCHAR2(1);
340 l_return_status VARCHAR2(1);
341
342 l_status PO_DRAFTS.status%TYPE := PO_DRAFTS_PVT.g_status_PDOI_PROCESSING;
343
344 l_locking_allowed VARCHAR2(1);
345 l_message VARCHAR2(30);
346
347 l_intf_hdr_id NUMBER; -- bug5129752
348 BEGIN
349 d_position := 0;
350
351 IF (PO_LOG.d_proc) THEN
352 PO_LOG.proc_begin (d_module);
353 END IF;
354
355 --SQL What: get all records that will be processed in current round. It also
356 -- locks the po_Headers of documents that are being updated
357 --SQL Why: For each of this record, we need to find out whether we can
358 -- reuse an existing draft or have to create a new draft
359 SELECT PHI.interface_header_id,
360 PHI.po_header_id,
361 PHI.action,
362 NVL(PH.revision_num, 0)
363 BULK COLLECT
364 INTO l_intf_header_id_tbl,
365 l_po_header_id_tbl,
366 l_action_tbl,
367 l_revision_num_tbl
368 FROM po_headers_interface PHI,
369 po_headers_all PH
370 WHERE PHI.processing_id = PO_PDOI_PARAMS.g_processing_id
371 AND PHI.processing_round_num = PO_PDOI_PARAMS.g_current_round_num
372 AND PHI.po_header_id = PH.po_header_id(+)
373 FOR UPDATE OF PH.po_header_id;
374
375 d_position := 10;
376
377 -- initialize structure that stores additional information for each
378 -- interface header record
379
380 init_doc_info_tbl
381 ( p_intf_header_id_tbl => l_intf_header_id_tbl
382 );
383
384 -- allocate space to store draft ids
385 l_draft_id_tbl.extend(l_intf_header_id_tbl.COUNT);
386
387 FOR i IN 1..l_intf_header_id_tbl.COUNT LOOP
388 IF (l_action_tbl(i) = PO_PDOI_CONSTANTS.g_action_UPDATE) THEN
389 d_position := 20;
390
391 -- If we are updating a document, a draft for the document may or may
392 -- not exist. if the draft exists, reuse it; if not, create a new
393 -- draft
394
395 PO_DRAFTS_PVT.find_draft
396 ( p_po_header_id => l_po_header_id_tbl(i),
397 x_draft_id => l_tmp_draft_id,
398 x_draft_status => l_tmp_draft_status,
399 x_draft_owner_role => l_tmp_draft_owner_role
400 );
401
402 d_position := 30;
403
404 IF (l_tmp_draft_id IS NULL) THEN
405 IF (PO_LOG.d_stmt) THEN
406 PO_LOG.stmt(d_module, d_position, 'draft id not found');
407 END IF;
408
409 d_position := 40;
410
411 -- need to create a new draft
412 l_new_dft_idx_tbl.extend;
413 l_new_dft_idx_tbl(l_new_dft_idx_tbl.COUNT) := i;
414 l_draft_id_tbl(i) := PO_DRAFTS_PVT.draft_id_nextval;
415
416 ELSE
417 IF (l_tmp_draft_status IN (PO_DRAFTS_PVT.g_status_PDOI_PROCESSING,
418 PO_DRAFTS_PVT.g_status_PDOI_ERROR)) THEN
419
420 d_position := 50;
421
422 IF (PO_LOG.d_stmt) THEN
423 PO_LOG.stmt(d_module, d_position, 'existing draft is processed ' ||
424 'by PDOI. check whether the previous PDOI is still ' ||
425 'working on it');
426 END IF;
427
428 -- check whether the existing draft is still valid, or it is just
429 -- data corruption caused by incomplete execution of the previous
430 -- PDOI run.
431 check_new_draft_needed
432 ( p_draft_id => l_tmp_draft_id,
433 p_status => l_tmp_draft_status,
434 x_new_draft_needed => l_new_draft_needed
435 );
436
437 d_position := 60;
438
439
440 IF (l_new_draft_needed = FND_API.G_TRUE) THEN
441 d_position := 70;
442
443 IF (PO_LOG.d_stmt) THEN
444 PO_LOG.stmt(d_module, d_position, 'old draft can be removed. ' ||
445 'Creating a new one');
446 END IF;
447
448 PO_DRAFTS_PVT.remove_draft_changes
449 ( p_draft_id => l_tmp_draft_id,
450 p_exclude_ctrl_tbl => FND_API.G_FALSE,
451 x_return_status => l_return_status
452 );
453
454 -- new draft to be created
455 l_new_dft_idx_tbl.EXTEND;
456 l_new_dft_idx_tbl(l_new_dft_idx_tbl.COUNT) := i;
457 l_draft_id_tbl(i) := PO_DRAFTS_PVT.draft_id_nextval;
458 ELSE
459 d_position := 80;
460
461 IF (PO_LOG.d_stmt) THEN
462 PO_LOG.stmt(d_module, d_position, 'old draft is still being ' ||
463 'worked on. Fail the current intf record');
464 END IF;
465
466 PO_PDOI_ERR_UTL.add_fatal_error
467 ( p_interface_header_id => l_intf_header_id_tbl(i),
468 p_error_message_name => 'PO_LOCKED_OR_INVALID_STS',
469 p_table_name => 'PO_HEADERS_INTERFACE',
470 p_column_name => 'DRAFT_ID',
471 p_column_value => l_tmp_draft_id
472 );
473
474 -- the existing draft is still running. Need to reject the interface
475 -- record for the current run as it cannot be processed
476 l_reject_list.EXTEND;
477 l_reject_list(l_reject_list.COUNT) := l_intf_header_id_tbl(i);
478 END IF;
479
480 ELSE
481
482 d_position := 90;
483
484 IF (PO_LOG.d_stmt) THEN
485 PO_LOG.stmt(d_module, d_position, 'draft with id ' ||
486 l_tmp_draft_id || ' can be reused.');
487 END IF;
488
489 -- other draft status -- reuse the draft
490 l_draft_id_tbl(i) := l_tmp_draft_id;
491 l_existing_dft_idx_tbl.extend;
492 l_existing_dft_idx_tbl(l_existing_dft_idx_tbl.COUNT) := i;
493 END IF;
494 END IF;
495
496 ELSE
497 d_position := 100;
498
499 -- actions that cause new documents to be created
500 l_new_dft_idx_tbl.extend;
501 l_new_dft_idx_tbl(l_new_dft_idx_tbl.COUNT) := i;
502 l_draft_id_tbl(i) := PO_DRAFTS_PVT.draft_id_nextval;
503 END IF;
504 END LOOP;
505
506 d_position := 110;
507
508 PO_PDOI_UTL.reject_headers_intf
509 ( p_id_param_type => PO_PDOI_CONSTANTS.g_INTERFACE_HEADER_ID,
510 p_id_tbl => l_reject_list,
511 p_cascade => FND_API.G_TRUE
512 );
513
514 d_position := 120;
515
516 -- stamp draft_id value to interface table
517 FORALL i IN 1..l_intf_header_id_tbl.COUNT
518 UPDATE po_headers_interface
519 SET draft_id = l_draft_id_tbl(i)
520 WHERE interface_header_id = l_intf_header_id_tbl(i)
521 AND processing_id = PO_PDOI_PARAMS.g_processing_id;
522
523 d_position := 130;
524
525 -- Since DB 10g (bug4340345) has probems using VALUES OF against EMPTY
526 -- COLLECTION, Check the collection and call FORALL only if it's not empty
527
528 IF (l_new_dft_idx_tbl.COUNT > 0) THEN
529 d_position := 140;
530 -- Create new drafts
531 FORALL i IN VALUES OF l_new_dft_idx_tbl
532 INSERT INTO po_drafts
533 ( draft_id,
534 document_id,
535 revision_num,
536 owner_user_id,
537 owner_role,
538 status,
539 last_update_date,
540 last_updated_by,
541 last_update_login,
542 creation_date,
543 created_by,
544 request_id
545 )
546 VALUES
547 ( l_draft_id_tbl(i),
548 l_po_header_id_tbl(i),
549 l_revision_num_tbl(i),
550 FND_GLOBAL.user_id,
551 PO_PDOI_PARAMS.g_request.role,
552 l_status,
553 SYSDATE,
554 FND_GLOBAL.user_id,
555 FND_GLOBAL.login_id,
556 SYSDATE,
557 FND_GLOBAL.user_id,
558 FND_GLOBAL.conc_request_id
559 );
560
561 END IF;
562
563 -- bug5129752
564 -- Update new draft flag
565 FOR i IN 1..l_new_dft_idx_tbl.COUNT LOOP
566 -- get the interface header id that creates the new draft, and set
567 -- the new draft flag
568 l_intf_hdr_id := l_intf_header_id_tbl ( l_new_dft_idx_tbl(i) );
569 PO_PDOI_PARAMS.g_docs_info(l_intf_hdr_id).new_draft := FND_API.G_TRUE;
570
571 END LOOP;
572
573 d_position := 150;
574
575 IF (PO_LOG.d_stmt) THEN
576 PO_LOG.stmt(d_module, d_position,
577 'Number of existing drafts: ' || l_existing_dft_idx_tbl.COUNT);
578 END IF;
579
580 -- Since DB 10g has probems using VALUES OF against EMPTYCOLLECTION,
581 -- Check the collection and call FORALL only if it's not empty
582
583 IF (l_existing_dft_idx_tbl.COUNT > 0) THEN
584 d_position := 160;
585 -- If we are updating an existing draft, we need to temporarily set the
586 -- status to PDOI_PROCESSING
587 FORALL i IN VALUES OF l_existing_dft_idx_tbl
588 UPDATE po_drafts
589 SET status = l_status,
590 request_id = FND_GLOBAL.conc_request_id,
591 last_update_date = SYSDATE,
592 last_updated_by = FND_GLOBAL.user_id
593 WHERE draft_id = l_draft_id_tbl(i);
594 END IF;
595
596 IF (PO_LOG.d_proc) THEN
597 PO_LOG.proc_end (d_module);
598 END IF;
599
600 EXCEPTION
601 WHEN OTHERS THEN
602 PO_MESSAGE_S.add_exc_msg
603 ( p_pkg_name => d_pkg_name,
604 p_procedure_name => d_api_name || '.' || d_position
605 );
606 RAISE;
607 END assign_draft_id;
608
609 -----------------------------------------------------------------------
610 --Start of Comments
611 --Name: expire_lines_by_catalog_name
612 --Function:
613 -- Expire all the lines that match the catalog name specified in the parameter.
614 -- This involves bringing the lines to draft table.
615 --Parameters:
616 --IN:
617 --IN OUT:
618 --OUT:
619 --End of Comments
620 ------------------------------------------------------------------------
621 PROCEDURE expire_lines_by_catalog_name IS
622
623 d_api_name CONSTANT VARCHAR2(30) := 'expire_lines_by_catalog_name';
624 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
625 d_position NUMBER;
626
627 l_draft_id_tbl PO_TBL_NUMBER;
628 l_line_id_tbl PO_TBL_NUMBER;
629 l_delete_flag_tbl PO_TBL_VARCHAR1 := PO_TBL_VARCHAR1();
630
631 l_record_exist_tbl PO_TBL_VARCHAR1;
632 BEGIN
633 d_position := 0;
634
635 IF (PO_LOG.d_proc) THEN
636 PO_LOG.proc_begin (d_module);
637 END IF;
638
639 --SQL What: Get all the existing lines that match the category name given
640 -- Make sure that the lines are not cancelled, closed or expired
641 --SQL Why: All those lines need to be expired.
642 SELECT PHI.draft_id,
643 POL.po_line_id
644 BULK COLLECT
645 INTO l_draft_id_tbl,
646 l_line_id_tbl
647 FROM po_headers_interface PHI,
648 po_lines_all POL
649 WHERE PHI.processing_id = PO_PDOI_PARAMS.g_processing_id
650 AND PHI.processing_round_num = PO_PDOI_PARAMS.g_current_round_num
651 AND PHI.po_header_id = POL.po_header_id
652 AND POL.catalog_name = PO_PDOI_PARAMS.g_request.catalog_to_expire
653 AND NVL(POL.cancel_flag, 'N') = 'N'
654 AND NVL(POL.closed_code, 'OPEN') <> 'FINALLY CLOSED'
655 AND NVL(POL.expiration_date, SYSDATE+1) > SYSDATE;
656
657 IF (PO_LOG.d_stmt) THEN
658 PO_LOG.stmt(d_module, d_position, 'Number of lines to expire',
659 l_line_id_tbl.COUNT);
660 END IF;
661
662 IF (l_line_id_tbl.COUNT > 0) THEN
663 d_position := 10;
664
665 l_delete_flag_tbl.EXTEND(l_line_id_tbl.COUNT);
666
667 -- Bring all lines with matching catalog name to draft table
668 PO_LINES_DRAFT_PKG.sync_draft_from_txn
669 ( p_po_line_id_tbl => l_line_id_tbl,
670 p_draft_id_tbl => l_draft_id_tbl,
671 p_delete_flag_tbl => l_delete_flag_tbl,
672 x_record_already_exist_tbl => l_record_exist_tbl
673 );
674
675 d_position := 20;
676
677 -- set expiration date of the lines
678 FORALL i IN 1..l_line_id_tbl.COUNT
679 UPDATE po_lines_draft_all
680 SET expiration_date = TRUNC(SYSDATE-1)
681 WHERE po_line_id = l_line_id_tbl(i)
682 AND draft_id = l_draft_id_tbl(i);
683
684 END IF;
685
686 IF (PO_LOG.d_proc) THEN
687 PO_LOG.proc_end (d_module);
688 END IF;
689
690 EXCEPTION
691 WHEN OTHERS THEN
692 PO_MESSAGE_S.add_exc_msg
693 ( p_pkg_name => d_pkg_name,
694 p_procedure_name => d_api_name || '.' || d_position
695 );
696 RAISE;
697 END expire_lines_by_catalog_name;
698
699 -----------------------------------------------------------------------
700 --Start of Comments
701 --Name: init_doc_info_tbl
702 --Function:
703 -- initialize a structure that holds extra header interface record information
704 -- such has number of processed lines, whether there are lines to notify, etc.
705 --Parameters:
706 --IN:
707 --p_intf_header_id_tbl
708 -- list of interface header ids
709 --IN OUT:
710 --OUT:
711 --End of Comments
712 ------------------------------------------------------------------------
713 PROCEDURE init_doc_info_tbl
714 ( p_intf_header_id_tbl PO_TBL_NUMBER
715 ) IS
716
717 d_api_name CONSTANT VARCHAR2(30) := 'init_doc_info_tbl';
718 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
719 d_position NUMBER;
720
721 l_id PO_HEADERS_INTERFACE.interface_header_id%TYPE;
722
723
724 -- bug5215871 START
725 l_reject_count NUMBER;
726 -- bug5215871 END
727
728 BEGIN
729 d_position := 0;
730
731 IF (PO_LOG.d_proc) THEN
732 PO_LOG.proc_begin (d_module);
733 END IF;
734
735 PO_PDOI_PARAMS.g_docs_info.DELETE;
736
737 d_position := 10;
738
739 FOR i IN 1..p_intf_header_id_tbl.COUNT LOOP
740 l_id := p_intf_header_id_tbl(i);
741
742 -- bug5215871
743 -- Count the number of the lines that are already rejected before
744 -- PDOI processes them. We need to include them processed and errored
745 -- out.
746 SELECT count(*)
747 INTO l_reject_count
748 FROM po_lines_interface PLI
749 WHERE PLI.interface_header_id = p_intf_header_id_tbl(i)
750 AND PLI.process_code = PO_PDOI_CONSTANTS.g_PROCESS_CODE_REJECTED;
751
752 PO_PDOI_PARAMS.g_docs_info(l_id).number_of_processed_lines :=
753 l_reject_count;
754 PO_PDOI_PARAMS.g_docs_info(l_id).number_of_errored_lines :=
755 l_reject_count;
756 PO_PDOI_PARAMS.g_docs_info(l_id).number_of_valid_lines := 0;
757 PO_PDOI_PARAMS.g_docs_info(l_id).err_tolerance_exceeded := FND_API.G_FALSE;
758 PO_PDOI_PARAMS.g_docs_info(l_id).has_errors := FND_API.G_FALSE;
759 PO_PDOI_PARAMS.g_docs_info(l_id).has_lines_to_notify := FND_API.G_FALSE;
760 PO_PDOI_PARAMS.g_docs_info(l_id).new_draft := FND_API.G_FALSE; -- bug5129752
761 END LOOP;
762
763 IF (PO_LOG.d_proc) THEN
764 PO_LOG.proc_end (d_module);
765 END IF;
766
767 EXCEPTION
768 WHEN OTHERS THEN
769 PO_MESSAGE_S.add_exc_msg
770 ( p_pkg_name => d_pkg_name,
771 p_procedure_name => d_api_name || '.' || d_position
772 );
773 RAISE;
774 END init_doc_info_tbl;
775
776
777 -----------------------------------------------------------------------
778 --Start of Comments
779 --Name: check_new_draft_needed
780 --Function:
781 -- Check whether the previous draft is still being processed by PDOI.
782 -- If not, then return a flag telling the caller to create a new draft,
783 -- as the existing one is no longer being processed.
784 --Parameters:
785 --IN:
786 --p_draft_id
787 -- draft unique identifier
788 --p_status
789 -- status of the draft. (status will be PDOI related. Possible values:
790 -- PDOI_PROCESSING, PDOI_ERROR
791 --IN OUT:
792 --OUT:
793 --x_new_draft_needed
794 -- FND_API.G_TRUE if a new draft needs to be created
795 -- FND_API.G_FALSE if the old draft is still effective
796 --End of Comments
797 ------------------------------------------------------------------------
798 PROCEDURE check_new_draft_needed
799 ( p_draft_id IN NUMBER,
800 p_status IN VARCHAR2,
801 x_new_draft_needed OUT NOCOPY VARCHAR2
802 ) IS
803
804 d_api_name CONSTANT VARCHAR2(30) := 'check_new_draft_needed';
805 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
806 d_position NUMBER;
807
808 l_dft_request_id PO_DRAFTS.request_id%TYPE;
809 l_cur_request_id NUMBER;
810
811
812 BEGIN
813 d_position := 0;
814
815 IF (PO_LOG.d_proc) THEN
816 PO_LOG.proc_begin (d_module, 'p_draft_id', p_draft_id);
817 PO_LOG.proc_begin (d_module, 'p_status', p_status);
818 END IF;
819
820 x_new_draft_needed := FND_API.G_FALSE;
821
822 IF (p_status = PO_DRAFTS_PVT.g_status_PDOI_ERROR) THEN
823 d_position := 10;
824
825 x_new_draft_needed := FND_API.G_TRUE;
826
827 ELSIF (p_status = PO_DRAFTS_PVT.g_status_PDOI_PROCESSING) THEN
828 d_position := 20;
829
830 l_cur_request_id := FND_GLOBAL.conc_request_id;
831
832 PO_DRAFTS_PVT.get_request_id
833 ( p_draft_id => p_draft_id,
834 x_request_id => l_dft_request_id
835 );
836
837 x_new_draft_needed := PO_PDOI_UTL.is_old_request_complete
838 ( p_old_request_id => l_dft_request_id
839 );
840
841 END IF; -- if status = p_status = PO_DRAFTS_PVT.g_status_PDOI_ERROR
842
843 IF (PO_LOG.d_proc) THEN
844 PO_LOG.proc_end (d_module, 'x_new_draft_needed', x_new_draft_needed);
845 END IF;
846
847 EXCEPTION
848 WHEN OTHERS THEN
849 PO_MESSAGE_S.add_exc_msg
850 ( p_pkg_name => d_pkg_name,
851 p_procedure_name => d_api_name || '.' || d_position
852 );
853 RAISE;
854 END check_new_draft_needed;
855
856
857
858
859 END PO_PDOI_HEADER_GROUPING_PVT;
[Home] [Help]