[Home] [Help]
PACKAGE BODY: APPS.PO_PDOI_POSTPROC_PVT
Source
1 PACKAGE BODY PO_PDOI_POSTPROC_PVT AS
2 /* $Header: PO_PDOI_POSTPROC_PVT.plb 120.26.12010000.3 2009/01/07 15:47:25 ggandhi ship $ */
3
4 d_pkg_name CONSTANT VARCHAR2(30) :=
5 PO_LOG.get_package_base('PO_PDOI_POSTPROC_PVT');
6
7 DOC_POSTPROC_EXC EXCEPTION;
8
9 -------------------------------------------------------
10 ----------- PRIVATE PROCEDURES PROTOTYPE --------------
11 -------------------------------------------------------
12 PROCEDURE process_rejected_records;
13
14 PROCEDURE post_validate
15 ( p_doc_rec IN doc_row_type,
16 p_doc_info IN PO_PDOI_PARAMS.doc_info_rec_type,
17 x_header_rejected OUT NOCOPY VARCHAR2,
18 x_remove_draft OUT NOCOPY VARCHAR2 -- bug5129752
19 );
20
21
22 PROCEDURE transfer_document_check
23 ( p_doc_rec IN doc_row_type,
24 p_doc_info IN PO_PDOI_PARAMS.doc_info_rec_type,
25 x_transfer_flag OUT NOCOPY VARCHAR2,
26 x_submit_for_buyer_acc_flag OUT NOCOPY VARCHAR2
27 );
28
29 -- bug5149827
30 PROCEDURE remove_notified_draft_lines
31 ( p_draft_id IN NUMBER
32 );
33
34 PROCEDURE create_blanket
35 ( p_doc_rec IN doc_row_type,
36 x_process_code OUT NOCOPY VARCHAR2
37 );
38
39 PROCEDURE update_blanket
40 ( p_doc_rec IN doc_row_type,
41 x_process_code OUT NOCOPY VARCHAR2
42 );
43
44 PROCEDURE create_quotation
45 ( p_doc_rec IN doc_row_type
46 );
47
48 PROCEDURE update_quotation
49 ( p_doc_rec IN doc_row_type
50 );
51
52 PROCEDURE create_standard_po
53 ( p_doc_rec IN doc_row_type,
54 x_process_code OUT NOCOPY VARCHAR2
55 );
56
57 PROCEDURE update_standard_po
58 ( p_doc_rec IN doc_row_type,
59 x_process_code OUT NOCOPY VARCHAR2
60 );
61
62 PROCEDURE expire_document
63 ( p_doc_rec IN doc_row_type
64 );
65
66 PROCEDURE assign_document_number
67 ( p_doc_rec IN doc_row_type
68 );
69
70 PROCEDURE get_lines_for_src_rules
71 ( p_doc_rec IN doc_row_type,
72 x_lines OUT NOCOPY src_rule_lines_rec_type
73 );
74
75 PROCEDURE process_sourcing_rules
76 ( p_doc_rec IN doc_row_type,
77 p_approval_status IN VARCHAR2,
78 p_lines IN src_rule_lines_rec_type
79 );
80
81 PROCEDURE transfer_draft_to_txn
82 ( p_doc_rec IN doc_row_type
83 );
84
85 PROCEDURE get_approval_method
86 ( p_doc_rec IN doc_row_type,
87 x_approval_method OUT NOCOPY VARCHAR2
88 );
89
90 PROCEDURE archive_po
91 ( p_doc_rec IN doc_row_type
92 );
93
94 PROCEDURE update_document_status
95 ( p_doc_rec IN doc_row_type,
96 p_auth_status IN VARCHAR2,
97 p_status_lookup_code IN VARCHAR2
98 );
99
100 PROCEDURE reserve_fund
101 ( p_doc_rec IN doc_row_type,
102 x_result OUT NOCOPY VARCHAR2
103 );
104
105 FUNCTION need_to_encumber
106 ( p_doc_rec IN doc_row_type,
107 p_new_auth_status IN VARCHAR2
108 ) RETURN VARCHAR2;
109
110 FUNCTION need_to_create_sourcing_rules
111 ( p_doc_rec IN doc_row_type
112 ) RETURN VARCHAR2;
113
114 PROCEDURE start_po_approval_workflow
115 ( p_doc_rec IN doc_row_type
116 );
117
118 PROCEDURE create_po_supply
119 ( p_doc_rec IN doc_row_type,
120 p_action IN VARCHAR2
121 );
122
123 PROCEDURE create_delivery_record
124 ( p_doc_rec IN doc_row_type
125 );
126
127 PROCEDURE close_po
128 ( p_doc_rec IN doc_row_type
129 );
130
131 PROCEDURE rebuild_catalog_index
132 ( p_type IN VARCHAR2,
133 p_doc_rec IN doc_row_type
134 );
135
136 PROCEDURE calculate_tax
137 ( p_doc_rec IN doc_row_type,
138 x_return_status OUT NOCOPY VARCHAR2
139 );
140
141 PROCEDURE submission_check
142 ( p_doc_rec IN doc_row_type,
143 x_return_status OUT NOCOPY VARCHAR2
144 );
145
146 -------------------------------------------------------
147 -------------- PUBLIC PROCEDURES ----------------------
148 -------------------------------------------------------
149
150 -----------------------------------------------------------------------
151 --Start of Comments
152 --Name: process
153 --Function:
154 -- Main procedure for post processing
155 --Parameters:
156 --IN:
157 --IN OUT:
158 --OUT:
159 --End of Comments
160 ------------------------------------------------------------------------
161 PROCEDURE process IS
162
163 d_api_name CONSTANT VARCHAR2(30) := 'process';
164 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
165 d_position NUMBER;
166
167 TYPE doc_tbl_type IS TABLE OF doc_row_type INDEX BY BINARY_INTEGER;
168 l_docs_tbl doc_tbl_type;
169
170 l_doc_rec doc_row_type;
171
172 l_header_rejected VARCHAR2(1);
173
174 l_transfer_flag VARCHAR2(1);
175 l_submit_for_buyer_acc_flag VARCHAR2(1);
176
177 l_doc_info PO_PDOI_PARAMS.doc_info_rec_type;
178 l_process_code PO_HEADERS_INTERFACE.process_code%TYPE;
179
180 x_process_code VARCHAR2(1) := ''; -- bug 7277317
181 l_remove_draft VARCHAR2(1); -- bug5129752
182 BEGIN
183
184 d_position := 0;
185 IF (PO_LOG.d_proc) THEN
186 PO_LOG.proc_begin(d_module);
187 END IF;
188
189 PO_TIMING_UTL.start_time (PO_PDOI_CONSTANTS.g_T_POSTPROCESSING);
190
191 process_rejected_records;
192
193 OPEN c_doc;
194
195 LOOP
196
197 IF (PO_LOG.d_stmt) THEN
198 PO_LOG.stmt(d_module, d_position, 'inside loop');
199 END IF;
200
201 FETCH c_doc
202 BULK COLLECT
203 INTO l_docs_tbl
204 LIMIT PO_PDOI_PARAMS.g_request.batch_size;
205
206 IF (PO_LOG.d_stmt) THEN
207 PO_LOG.stmt(d_module, d_position, 'num of records fetched: '
208 || l_docs_tbl.COUNT);
209 END IF;
210 d_position := 10;
211
212 EXIT WHEN l_docs_tbl.COUNT = 0;
213 x_process_code := '';
214 -- process one header at a time
215 FOR i IN 1..l_docs_tbl.COUNT
216 LOOP
217 BEGIN
218 -- set savepoint for each draft document processing
219 SAVEPOINT po_pdoi_doc_postproc_sp;
220 d_position := 20;
221
222 l_doc_rec := l_docs_tbl(i);
223 l_doc_info := PO_PDOI_PARAMS.g_docs_info(l_doc_rec.interface_header_id);
224
225 IF (PO_PDOI_PARAMS.g_request.calling_module =
226 PO_PDOI_CONSTANTS.g_call_mod_CATALOG_UPLOAD) THEN
227 -- Copy processed lines info back to g_out structure. These values
228 -- will get returned to the caller of PDOI
229 PO_PDOI_PARAMS.g_out.processed_lines_count :=
230 l_doc_info.number_of_processed_lines;
231 PO_PDOI_PARAMS.g_out.rejected_lines_count :=
232 l_doc_info.number_of_errored_lines;
233 PO_PDOI_PARAMS.g_out.err_tolerance_exceeded :=
234 l_doc_info.err_tolerance_exceeded;
235 END IF;
236
237 IF (PO_LOG.d_stmt) THEN
238 PO_LOG.stmt(d_module, d_position, 'interface_header_id: ' ||
239 l_doc_rec.interface_header_id ||
240 ', processing header_id: ' || l_doc_rec.po_header_id ||
241 ', action: ' || l_doc_rec.action);
242 END IF;
243
244 -- post validation
245 post_validate
246 ( p_doc_rec => l_doc_rec,
247 p_doc_info => l_doc_info,
248 x_header_rejected => l_header_rejected,
249 x_remove_draft => l_remove_draft -- bug5129752
250 );
251
252 IF (PO_LOG.d_stmt) THEN
253 PO_LOG.stmt(d_module, d_position, 'l_header_rejected', l_header_rejected);
254 END IF;
255
256 IF (l_header_rejected = FND_API.G_FALSE) THEN
257 d_position := 30;
258
259 transfer_document_check
260 ( p_doc_rec => l_doc_rec,
261 p_doc_info => l_doc_info,
262 x_transfer_flag => l_transfer_flag,
263 x_submit_for_buyer_acc_flag => l_submit_for_buyer_acc_flag
264 );
265
266 IF (PO_LOG.d_stmt) THEN
267 PO_LOG.stmt(d_module, d_position, 'l_transfer_flag', l_transfer_flag);
268 PO_LOG.stmt(d_module, d_position, 'l_submit_for_buyer_acc_flag',
269 l_submit_for_buyer_acc_flag);
270 END IF;
271
272 d_position := 40;
273
274 IF (l_transfer_flag = FND_API.G_FALSE) THEN
275
276 d_position := 50;
277
278 -- Change the draft status back to 'DRAFT' (it was set to
279 -- 'PDOI PROCESSING' during header grouping)
280 PO_DRAFTS_PVT.update_draft_status
281 ( p_draft_id => l_doc_rec.draft_id,
282 p_new_status => PO_DRAFTS_PVT.g_STATUS_DRAFT
283 );
284
285 -- If user is creating a draft without transferring, we need
286 -- to get the functional lock of the document explicitly
287 PO_DRAFTS_PVT.lock_document
288 ( p_po_header_id => l_doc_rec.po_header_id,
289 p_role => PO_PDOI_PARAMS.g_request.role,
290 p_role_user_id => FND_GLOBAL.user_id,
291 p_unlock_current => FND_API.G_FALSE
292 );
293
294 IF (l_submit_for_buyer_acc_flag = FND_API.G_TRUE) THEN
295 d_position := 60;
296
297 IF (PO_LOG.d_stmt) THEN
298 PO_LOG.stmt(d_module, d_position, 'launch buyer acceptance process');
299 END IF;
300
301 -- submit document for buyer acceptance
302 PO_DIFF_SUMMARY_PKG.start_workflow
303 ( p_po_header_id => l_doc_rec.po_header_id
304 );
305
306 END IF;
307
308 l_process_code := PO_PDOI_CONSTANTS.g_PROCESS_CODE_ACCEPTED;
309 ELSE
310 d_position := 70;
311
312 IF ( l_doc_info.has_lines_to_notify = FND_API.G_TRUE) THEN
313 l_process_code := PO_PDOI_CONSTANTS.g_PROCESS_CODE_NOTIFIED;
314
315 -- 5149827
316 -- If the changes are to go over price tolerance acceptance
317 -- process, they should be removed from the draft since
318 -- we shouldn't transfer them to the trasnsaction table
319 remove_notified_draft_lines
320 ( p_draft_id => l_doc_rec.draft_id
321 );
322 ELSE
323 l_process_code := PO_PDOI_CONSTANTS.g_PROCESS_CODE_ACCEPTED;
324 END IF;
325
326 -- Call different module for post processing according to document
327 -- type and action
328
329 IF (PO_PDOI_PARAMS.g_request.document_type =
330 PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET) THEN
331
332 IF (l_doc_rec.action IN (PO_PDOI_CONSTANTS.g_ACTION_ADD,
333 PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL)) THEN
334 d_position := 80;
335 create_blanket ( p_doc_rec => l_doc_rec,
336 x_process_code => x_process_code ); --bug 7277317
337
338 ELSIF (l_doc_rec.action = PO_PDOI_CONSTANTS.g_ACTION_REPLACE) THEN
339 d_position := 90;
340 expire_document(p_doc_rec => l_doc_rec);
341 create_blanket(p_doc_rec => l_doc_rec,
342 x_process_code => x_process_code); -- bug 7277317
343
344 ELSIF (l_doc_rec.action = PO_PDOI_CONSTANTS.g_ACTION_UPDATE) THEN
345 d_position := 100;
346 update_blanket(p_doc_rec => l_doc_rec,
347 x_process_code => x_process_code); -- bug 7277317
348
349 END IF;
350
351 ELSIF (PO_PDOI_PARAMS.g_request.document_type =
352 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) THEN
353
354 IF (l_doc_rec.action IN (PO_PDOI_CONSTANTS.g_ACTION_ADD,
355 PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL)) THEN
356
357 d_position := 110;
358 create_quotation(p_doc_rec => l_doc_rec);
359
360 ELSIF (l_doc_rec.action = PO_PDOI_CONSTANTS.g_ACTION_REPLACE) THEN
361 d_position := 120;
362 expire_document(p_doc_rec => l_doc_rec);
363 create_quotation(p_doc_rec => l_doc_rec);
364
365 ELSIF (l_doc_rec.action = PO_PDOI_CONSTANTS.g_ACTION_UPDATE) THEN
366 d_position := 130;
367 update_quotation(p_doc_rec => l_doc_rec);
368
369 END IF;
370
371 ELSIF (PO_PDOI_PARAMS.g_request.document_type =
372 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD) THEN
373
374 IF (l_doc_rec.action IN (PO_PDOI_CONSTANTS.g_ACTION_ADD,
375 PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL)) THEN
376 d_position := 140;
377 create_standard_po(p_doc_rec => l_doc_rec,
378 x_process_code => x_process_code); -- bug 7277317
379
380 ELSIF (l_doc_rec.action = PO_PDOI_CONSTANTS.g_ACTION_UPDATE) THEN
381 d_position := 150;
382 update_standard_po(p_doc_rec => l_doc_rec,
383 x_process_code => x_process_code); -- bug 7277317
384
385 END IF;
386 END IF; -- if document_type = 'BLANKET'
387
388 d_position := 160;
389
390 END IF; -- if transfer_flag = FND_API.G_FALSE
391
392 d_position := 170;
393 -- bug 7277317
394 IF (x_process_code = 'I' ) THEN
395 l_process_code := 'INCOMPLETE';
396 END IF;
397
398 UPDATE po_headers_interface
399 SET process_code = l_process_code
400 WHERE interface_header_id = l_doc_rec.interface_header_id;
401
402 IF (PO_LOG.d_stmt) THEN
403 PO_LOG.stmt(d_module, d_position, 'process_code', l_process_Code);
404 END IF;
405
406 ELSE
407 IF (PO_LOG.d_stmt) THEN
408 PO_LOG.stmt(d_module, d_position, 'header gets rejected');
409 END IF;
410
411 -- bug5129752
412 IF (l_remove_draft = FND_API.G_FALSE) THEN
413
414 -- Change the draft status back to 'DRAFT' (it was set to
415 -- 'PDOI PROCESSING' during header grouping)
416 PO_DRAFTS_PVT.update_draft_status
417 ( p_draft_id => l_doc_rec.draft_id,
418 p_new_status => PO_DRAFTS_PVT.g_STATUS_DRAFT
419 );
420
421 END IF;
422 END IF;
423
424 PO_PDOI_UTL.commit_work;
425
426 EXCEPTION
427 WHEN OTHERS THEN
428 PO_LOG.stmt(d_module, d_position, 'rollback to savepoint po_pdoi_doc_postproc_sp');
429
430 ROLLBACK TO SAVEPOINT po_pdoi_doc_postproc_sp;
431 RAISE;
432 END;
433
434 END LOOP;
435 END LOOP;
436
437 close c_doc;
438
439 PO_TIMING_UTL.stop_time (PO_PDOI_CONSTANTS.g_T_POSTPROCESSING);
440
441 IF (PO_LOG.d_proc) THEN
442 PO_LOG.proc_end(d_module);
443 END IF;
444
445 EXCEPTION
446 WHEN OTHERS THEN
447 IF (c_doc%ISOPEN) THEN
448 CLOSE c_doc;
449 END IF;
450
451 PO_MESSAGE_S.add_exc_msg
452 ( p_pkg_name => d_pkg_name,
453 p_procedure_name => d_api_name || '.' || d_position
454 );
455 RAISE;
456 END process;
457
458 -------------------------------------------------------
459 -------------- PRIVATE PROCEDURES ----------------------
460 -------------------------------------------------------
461
462 -- bug5149827 START
463 -----------------------------------------------------------------------
464 --Start of Comments
465 --Name: remove_notified_draft_lines
466 --Function:
467 -- Remove all lines that have notified status in the interface table.
468 -- These lines need to be removed because the acceptance process will be
469 -- based on top of the interface table.
470 --Parameters:
471 --IN:
472 --p_draft_id
473 -- Draft identifier
474 --IN OUT:
475 --OUT:
476 --x_header_rejected
477 -- Whether the document passed validation or not
478 -- FND_API.G_TRUE if passed
479 -- FND_API.G_FALSE otherwise
480 --End of Comments
481 ------------------------------------------------------------------------
482 PROCEDURE remove_notified_draft_lines
483 ( p_draft_id IN NUMBER
484 ) IS
485
486 d_api_name CONSTANT VARCHAR2(30) := 'remove_notified_draft_lines';
487 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
488 d_position NUMBER;
489
490 l_po_line_id_tbl PO_TBL_NUMBER;
491 BEGIN
492
493 d_position := 0;
494 IF (PO_LOG.d_proc) THEN
495 PO_LOG.proc_begin(d_module);
496 END IF;
497
498 DELETE FROM po_lines_draft_all
499 WHERE draft_id = p_draft_id
500 AND change_accepted_flag = PO_DRAFTS_PVT.g_chg_accepted_flag_NOTIFY
501 RETURNING po_line_id
502 BULK COLLECT
503 INTO l_po_line_id_tbl;
504
505 d_position := 10;
506
507 FORALL i IN 1..l_po_line_id_tbl.COUNT
508 DELETE FROM po_line_locations_draft_all
509 WHERE draft_id = p_draft_id
510 AND po_line_id = l_po_line_id_tbl(i);
511
512 d_position := 20;
513
514 FORALL i IN 1..l_po_line_id_tbl.COUNT
515 DELETE FROM po_attribute_values_draft
516 WHERE draft_id = p_draft_id
517 AND po_line_id = l_po_line_id_tbl(i);
518
519 d_position := 30;
520
521 FORALL i IN 1..l_po_line_id_tbl.COUNT
522 DELETE FROM po_attribute_values_tlp_draft
523 WHERE draft_id = p_draft_id
524 AND po_line_id = l_po_line_id_tbl(i);
525
526 d_position := 40;
527
528 FORALL i IN 1..l_po_line_id_tbl.COUNT
529 DELETE FROM po_price_diff_draft
530 WHERE draft_id = p_draft_id
531 AND entity_id = l_po_line_id_tbl(i)
532 AND entity_type = 'BLANKET LINE';
533
534 d_position := 50;
535
536 FORALL i IN 1..l_po_line_id_tbl.COUNT
537 DELETE FROM po_price_diff_draft PPDD
538 WHERE draft_id = p_draft_id
539 AND entity_type = 'PRICE BREAK'
540 AND EXISTS (SELECT 1
541 FROM po_line_locations_draft_all PLLD
542 WHERE PLLD.draft_id = p_draft_id
543 AND PLLD.po_line_id = l_po_line_id_tbl(i)
544 AND PLLD.line_location_id = PPDD.entity_id
545 UNION ALL
546 SELECT 1
547 FROM po_line_locations_all PLLA
548 WHERE PLLA.po_line_id = l_po_line_id_tbl(i)
549 AND PLLA.line_location_id = PPDD.entity_id);
550
551 IF (PO_LOG.d_proc) THEN
552 PO_LOG.proc_end(d_module);
553 END IF;
554
555 EXCEPTION
556 WHEN OTHERS THEN
557 PO_MESSAGE_S.add_exc_msg
558 ( p_pkg_name => d_pkg_name,
559 p_procedure_name => d_api_name || '.' || d_position
560 );
561 RAISE;
562 END remove_notified_draft_lines;
563
564 -- bug5149827 END
565
566 -----------------------------------------------------------------------
567 --Start of Comments
568 --Name: process_rejected_records
569 --Function:
570 -- For interface records that are rejected, we need to find out if we
571 -- need to clean up the draft of the document. Draft documents should be
572 -- deleted when:
573 -- 1) Action is not update
574 -- 2) Action = Update, and document type does not allow draft
575 -- 3) Action = Update, and document type allows draft, but no changes have
576 -- been populated into PDOI for the draft (i.e. the draft was populated
577 -- by this PDOI run). Need to check this because there may already be
578 -- draft changes existing in the draft table before PDOI is called.
579 --Parameters:
580 --IN:
581 --p_doc_rec
582 -- Some attribute values of the document
583 --p_doc_info
584 -- Processing status values of the document
585 --IN OUT:
586 --OUT:
587 --x_header_rejected
588 -- Whether the document passed validation or not
589 -- FND_API.G_TRUE if passed
590 -- FND_API.G_FALSE otherwise
591 --End of Comments
592 ------------------------------------------------------------------------
593 PROCEDURE process_rejected_records IS
594
595 d_api_name CONSTANT VARCHAR2(30) := 'process_rejected_records';
596 d_module CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
597 d_position NUMBER;
598
599 l_intf_header_id_tbl PO_TBL_NUMBER;
600 l_action_tbl PO_TBL_VARCHAR30;
601 l_dft_id_tbl PO_TBL_NUMBER;
602 l_po_header_id_tbl PO_TBL_NUMBER;
603
604 l_dft_to_delete_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
605
606 l_dft_exist_chg_check_tbl PO_TBL_NUMBER := PO_TBL_NUMBER();
607 l_chg_exist_tbl PO_TBL_VARCHAR1;
608
609 BEGIN
610 d_position := 0;
611 IF (PO_LOG.d_proc) THEN
612 PO_LOG.proc_begin(d_module);
613 END IF;
614
615 SELECT PHI.interface_header_id,
616 PHI.action,
617 PHI.draft_id,
618 PHI.po_header_id
619 BULK COLLECT
620 INTO l_intf_header_id_tbl,
621 l_action_tbl,
622 l_dft_id_tbl,
623 l_po_header_id_tbl
624 FROM po_headers_interface PHI
625 WHERE processing_id = -PO_PDOI_PARAMS.g_processing_id
626 AND processing_round_num = PO_PDOI_PARAMS.g_current_round_num;
627
628 d_position := 10;
629
630 FOR i IN 1..l_intf_header_id_tbl.COUNT LOOP
631 -- If action is not 'UPDATE', remove draft
632 IF ( l_action_tbl(i) <> PO_PDOI_CONSTANTS.g_action_UPDATE ) THEN
633 d_position := 20;
634
635 l_dft_to_delete_tbl.EXTEND;
636 l_dft_to_delete_tbl(l_dft_to_delete_tbl.COUNT) := l_dft_id_tbl(i);
637
638 ELSE
639 d_position := 30;
640 -- Update action
641
642 IF (PO_DRAFTS_PVT.is_draft_applicable
643 ( p_po_header_id => l_po_header_id_tbl(i),
644 p_role => PO_PDOI_PARAMS.g_request.role
645 ) = FND_API.G_FALSE ) THEN
646
647 d_position := 40;
648
649 l_dft_to_delete_tbl.EXTEND;
650 l_dft_to_delete_tbl(l_dft_to_delete_tbl.COUNT) := l_dft_id_tbl(i);
651
652 ELSE
653 d_position := 50;
654
655 -- If draft changes before PDOI runs is possible to exist,
656 -- need to check if changes really exist. If so, do not remove
657 -- draft control record
658 l_dft_exist_chg_check_tbl.EXTEND;
659 l_dft_exist_chg_check_tbl(l_dft_exist_chg_check_tbl.COUNT) :=
660 l_dft_id_tbl(i);
661 END IF;
662 END IF;
663
664 END LOOP;
665
666 -- check whether the draft already contains changes
667 l_chg_exist_tbl :=
668 PO_DRAFTS_PVT.changes_exist_for_draft
669 ( p_draft_id_tbl => l_dft_exist_chg_check_tbl
670 );
671
672 FOR i IN 1..l_dft_exist_chg_check_tbl.COUNT LOOP
673 IF (l_chg_exist_tbl(i) = FND_API.G_FALSE) THEN
674 l_dft_to_delete_tbl.EXTEND;
675 l_dft_to_delete_tbl(l_dft_to_delete_tbl.COUNT) :=
676 l_dft_exist_chg_check_tbl(i);
677 END IF;
678 END LOOP;
679
680 FORALL i IN 1..l_dft_to_delete_tbl.COUNT
681 DELETE po_drafts
682 WHERE draft_id = l_dft_to_delete_tbl(i);
683
684 IF (PO_LOG.d_proc) THEN
685 PO_LOG.proc_end(d_module);
686 END IF;
687
688 EXCEPTION
689 WHEN OTHERS THEN
690 PO_MESSAGE_S.add_exc_msg
691 ( p_pkg_name => d_pkg_name,
692 p_procedure_name => d_api_name || '.' || d_position
693 );
694 RAISE;
695 END process_rejected_records;
696
697 -----------------------------------------------------------------------
698 --Start of Comments
699 --Name: post_validate
700 --Function:
701 -- Post validation for PDOI - Perform validations that have to be done
702 -- against the document as a whole
703 --Parameters:
704 --IN:
705 --p_doc_rec
706 -- Some attribute values of the document
707 --p_doc_info
708 -- Processing status values of the document
709 --IN OUT:
710 --OUT:
711 --x_header_rejected
712 -- Whether the document passed validation or not
713 -- FND_API.G_TRUE if passed
714 -- FND_API.G_FALSE otherwise
715 --x_remove_draft
716 -- Whether draft has been removed
717 --End of Comments
718 ------------------------------------------------------------------------
719 PROCEDURE post_validate
720 ( p_doc_rec IN doc_row_type,
721 p_doc_info IN PO_PDOI_PARAMS.doc_info_rec_type,
722 x_header_rejected OUT NOCOPY VARCHAR2,
723 x_remove_draft OUT NOCOPY VARCHAR2 -- bug5129752
724 ) IS
725
726 d_api_name CONSTANT VARCHAR2(30) := 'post_validate';
727 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
728 d_position NUMBER;
729
730 l_is_quotation BOOLEAN := FALSE;
731 l_is_local_bpa BOOLEAN := FALSE;
732 l_is_std_po BOOLEAN := FALSE;
733
734 BEGIN
735 d_position := 0;
736 IF (PO_LOG.d_proc) THEN
737 PO_LOG.proc_begin(d_module);
738 END IF;
739
740 x_header_rejected := FND_API.G_FALSE;
741
742 -- Check 1: If a document has errors, Reject the whole document in the
743 -- following situations
744 -- 1) Standard PO
745 -- 2) Local blankets uploaded through catalog upload
746 -- 3) Quotation upload through catalog upload (bug5461177)
747
748 IF ( p_doc_info.has_errors = FND_API.G_TRUE ) THEN
749
750 IF (PO_PDOI_PARAMS.g_request.document_type =
751 PO_PDOI_CONSTANTS.g_doc_type_STANDARD) THEN
752
753 l_is_std_po := TRUE;
754
755 ELSIF (PO_PDOI_PARAMS.g_request.document_type =
756 PO_PDOI_CONSTANTS.g_doc_type_BLANKET
757 AND p_doc_rec.ga_flag <> 'Y') THEN
758
759 l_is_local_bpa := TRUE;
760
761 ELSIF (PO_PDOI_PARAMS.g_request.document_type =
762 PO_PDOI_CONSTANTS.g_doc_type_QUOTATION) THEN
763
764 l_is_quotation := TRUE;
765
766 END IF;
767
768 IF (l_is_std_po
769 OR
770 ( (l_is_local_bpa OR l_is_quotation)
771 AND
772 PO_PDOI_PARAMS.g_request.calling_module =
773 PO_PDOI_CONSTANTS.g_CALL_MOD_CATALOG_UPLOAD )) THEN
774
775 d_position := 10;
776
777 IF (PO_LOG.d_stmt) THEN
778 PO_LOG.stmt(d_module, d_position, 'failed Check 1');
779 END IF;
780
781 x_header_rejected := FND_API.G_TRUE;
782
783 END IF;
784 END IF;
785
786 d_position := 20;
787
788 -- add more checks here
789 -- Check 2: There should at least be one line that is valid
790 IF (x_header_rejected = FND_API.G_FALSE) THEN
791 IF ( p_doc_info.number_of_valid_lines = 0 ) THEN
792 IF (PO_LOG.d_stmt) THEN
793 PO_LOG.stmt(d_module, d_position, 'failed Check 2');
794 END IF;
795
796 IF ( p_doc_rec.action <> PO_PDOI_CONSTANTS.g_ACTION_UPDATE ) THEN
797 -- If action <> update, we need to tell user that the document created
798 -- has 0 line, and thus the failure
799
800 PO_PDOI_ERR_UTL.add_fatal_error
801 ( p_interface_header_id => p_doc_rec.interface_header_id,
802 p_error_message_name => 'PO_PDOI_INVALID_NUM_OF_LINES',
803 p_table_name => 'PO_HEADERS_INTERFACE',
804 p_column_name => 'PO_HEADER_ID',
805 p_column_value => p_doc_rec.po_header_id,
806 p_token1_name => 'COLUMN_NAME',
807 p_token1_value => 'PO_HEADER_ID'
808 );
809
810 END IF;
811
812 x_header_rejected := FND_API.G_TRUE;
813 END IF;
814 END IF;
815
816 x_remove_draft := FND_API.G_FALSE;
817
818 IF (x_header_rejected = FND_API.G_TRUE) THEN
819
820 -- bug5129752
821 -- Calculate x_remove_draft flag
822
823 IF ( PO_PDOI_PARAMS.g_docs_info(p_doc_rec.interface_header_id).new_draft =
824 FND_API.G_TRUE) THEN
825 x_remove_draft := FND_API.G_TRUE;
826 END IF;
827
828 -- If post validation fails, since records are already in draft tables,
829 -- besides setting the interface record to 'REJECTED', we also need to
830 -- remove the draft.
831 PO_PDOI_UTL.post_reject_document
832 ( p_interface_header_id => p_doc_rec.interface_header_id,
833 p_po_header_id => p_doc_rec.po_header_id,
834 p_draft_id => p_doc_rec.draft_id,
835 p_remove_draft => x_remove_draft -- bug5129752
836 );
837 END IF;
838
839 IF (PO_LOG.d_proc) THEN
840 PO_LOG.proc_end(d_module);
841 END IF;
842 EXCEPTION
843 WHEN OTHERS THEN
844 PO_MESSAGE_S.add_exc_msg
845 ( p_pkg_name => d_pkg_name,
846 p_procedure_name => d_api_name || '.' || d_position
847 );
848 RAISE;
849 END post_validate;
850
851 -----------------------------------------------------------------------
852 --Start of Comments
853 --Name: transfer_document_check
854 --Function:
855 -- Determines whether document needs to be transferred to transaction table,
856 -- and if it does not get transferred, do we submit the document for buyer
857 -- acceptance
858 --Parameters:
859 --IN:
860 --p_doc_rec
861 -- Some attribute values of the document
862 --p_doc_info
863 -- Processing status values of the document
864 --IN OUT:
865 --OUT:
866 --x_transfer_flag
867 -- FND_API.G_TRUE if transfer needs to happen
868 -- FND_API.G_FALSE otherwise
869 --x_submit_for_buyer_acc_flag
870 -- FND_API.G_TRUE if the draft is submitted for buyer acceptance
871 -- FND_API.G_FALSE otherwise
872 --End of Comments
873 ------------------------------------------------------------------------
874 PROCEDURE transfer_document_check
875 ( p_doc_rec IN doc_row_type,
876 p_doc_info IN PO_PDOI_PARAMS.doc_info_rec_type,
877 x_transfer_flag OUT NOCOPY VARCHAR2,
878 x_submit_for_buyer_acc_flag OUT NOCOPY VARCHAR2
879 ) IS
880
881 d_api_name CONSTANT VARCHAR2(30) := 'transfer_document_check';
882 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
883 d_position NUMBER;
884
885 l_has_over_tolerance_lines VARCHAR2(1) := FND_API.G_FALSE;
886 l_role_auth_acceptance VARCHAR2(30);
887
888 BEGIN
889 d_position := 0;
890 IF (PO_LOG.d_proc) THEN
891 PO_LOG.proc_begin(d_module);
892 END IF;
893
894 -- transfer if
895 -- 1) document is not a global agreement, OR
896 -- 2) role of the user is BUYER
897
898 IF (NOT (PO_PDOI_PARAMS.g_request.document_type =
899 PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET
900 AND
901 p_doc_rec.ga_flag = 'Y')
902 OR
903 PO_PDOI_PARAMS.g_request.role = PO_GLOBAL.g_role_BUYER) THEN
904
905 d_position := 10;
906 x_transfer_flag := FND_API.G_TRUE;
907 ELSE
908 d_position := 20;
909
910 x_transfer_flag := FND_API.G_FALSE;
911
912 -- If there is any line erroring out, or user explicitly prevents the
913 -- document to be submitted, then document will not be submitted for
914 -- buyer acceptance
915 IF (p_doc_info.number_of_errored_lines > 0
916 OR NVL(PO_PDOI_PARAMS.g_request.submit_dft_flag, 'N') = 'N') THEN
917
918 x_submit_for_buyer_acc_flag := FND_API.G_FALSE;
919 ELSE
920 x_submit_for_buyer_acc_flag := FND_API.G_TRUE;
921 END IF;
922
923 END IF; -- if not ga or ...
924
925 IF (PO_LOG.d_stmt) THEN
926 PO_LOG.stmt(d_module, d_position, 'x_transfer_flag', x_transfer_flag);
927 PO_LOG.stmt(d_module, d_position, 'x_submit_for_buyer_acc_flag',
928 x_submit_for_buyer_acc_flag);
929 END IF;
930
931 IF (PO_LOG.d_proc) THEN
932 PO_LOG.proc_end(d_module);
933 END IF;
934 EXCEPTION
935 WHEN OTHERS THEN
936 PO_MESSAGE_S.add_exc_msg
937 ( p_pkg_name => d_pkg_name,
938 p_procedure_name => d_api_name || '.' || d_position
939 );
940 RAISE;
941 END transfer_document_check;
942
943
944 -----------------------------------------------------------------------
945 --Start of Comments
946 --Name: create_blanket
947 --Function:
948 -- Performs necessary actions for creating a blanket
949 --Parameters:
950 --IN:
951 --p_doc_rec
952 -- Some attribute values of the document
953 --IN OUT:
954 --OUT:
955 --End of Comments
956 ------------------------------------------------------------------------
957 PROCEDURE create_blanket
958 ( p_doc_rec IN doc_row_type,
959 x_process_code OUT NOCOPY VARCHAR2
960 ) IS
961
962 d_api_name CONSTANT VARCHAR2(30) := 'create_blanket';
963 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
964 d_position NUMBER;
965
966
967 l_approval_method VARCHAR2(30);
968 l_new_auth_status PO_HEADERS_ALL.authorization_status%TYPE;
969
970 l_fund_reserve_result VARCHAR2(1);
971 l_return_status VARCHAR2(1);
972 l_need_to_encumber VARCHAR2(1);
973
974 l_need_to_create_src_rules VARCHAR2(1);
975 l_lines src_rule_lines_rec_type;
976
977 BEGIN
978 d_position := 0;
979 IF (PO_LOG.d_proc) THEN
980 PO_LOG.proc_begin(d_module);
981 END IF;
982
983 assign_document_number ( p_doc_rec => p_doc_rec );
984
985 d_position := 10;
986 l_need_to_create_src_rules := need_to_create_sourcing_rules
987 ( p_doc_rec => p_doc_rec
988 );
989
990 IF (l_need_to_create_src_rules = FND_API.G_TRUE) THEN
991
992 get_lines_for_src_rules
993 ( p_doc_rec => p_doc_rec,
994 x_lines => l_lines
995 );
996
997 IF (PO_LOG.d_stmt) THEN
998 PO_LOG.stmt(d_module, d_position, '# of lines requiring sourcing rules',
999 l_lines.po_line_id_tbl.COUNT);
1000 END IF;
1001 END IF;
1002
1003 d_position := 20;
1004
1005 transfer_draft_to_txn (p_doc_rec => p_doc_rec);
1006
1007 d_position := 30;
1008
1009 get_approval_method
1010 ( p_doc_rec => p_doc_rec,
1011 x_approval_method => l_approval_method
1012 );
1013
1014 IF (PO_LOG.d_stmt) THEN
1015 PO_LOG.stmt(d_module, d_position, 'l_approval_method', l_approval_method);
1016 END IF;
1017 --bug 7277317 added a call for submission check
1018 submission_check
1019 ( p_doc_rec => p_doc_rec,
1020 x_return_status => l_return_status
1021 );
1022 -- bug 7277317 added an if condition to check the return status of submission check
1023 -- proceed the below logic only if return status is success
1024 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1025 x_process_code := NULL;
1026 IF ( l_approval_method = PO_PDOI_CONSTANTS.g_appr_method_INIT_APPROVAL) THEN
1027 d_position := 40;
1028
1029
1030 IF (PO_LOG.d_stmt) THEN
1031 PO_LOG.stmt(d_module, d_position, 'starting approval workflow');
1032 END IF;
1033
1034 start_po_approval_workflow
1035 ( p_doc_rec => p_doc_rec
1036 );
1037
1038 ELSIF ( l_approval_method = PO_PDOI_CONSTANTS.g_appr_method_AUTO_APPROVE ) THEN
1039 d_position := 50;
1040
1041 l_new_auth_status := 'APPROVED';
1042
1043 IF (l_need_to_create_src_rules = FND_API.G_TRUE) THEN
1044
1045 IF (PO_LOG.d_stmt) THEN
1046 PO_LOG.stmt(d_module, d_position, 'creating sourcing rules');
1047 END IF;
1048
1049 d_position := 60;
1050 process_sourcing_rules
1051 ( p_doc_rec => p_doc_rec,
1052 p_approval_status => l_new_auth_status,
1053 p_lines => l_lines
1054 );
1055 END IF;
1056
1057 d_position := 70;
1058 l_need_to_encumber := need_to_encumber
1059 ( p_doc_rec => p_doc_rec,
1060 p_new_auth_status => l_new_auth_status
1061 );
1062
1063 IF (l_need_to_encumber = FND_API.G_TRUE) THEN
1064 d_position := 80;
1065
1066 -- bug4907624
1067 -- Removed the code that sets the document to PRE-APPROVED status
1068
1069 d_position := 90;
1070
1071
1072 IF (PO_LOG.d_stmt) THEN
1073 PO_LOG.stmt(d_module, d_position, 'Reserving document');
1074 END IF;
1075
1076 -- reserve document
1077 reserve_fund
1078 ( p_doc_rec => p_doc_rec,
1079 x_result => l_fund_reserve_result
1080 );
1081
1082 IF (l_fund_reserve_result = FND_API.G_FALSE) THEN
1083
1084
1085 IF (PO_LOG.d_stmt) THEN
1086 PO_LOG.stmt(d_module, d_position, 'Fund reservation was not done correctly');
1087 END IF;
1088
1089 -- If fund reservation is not successful, we need to revert the
1090 -- authorization status back
1091
1092 l_new_auth_status := 'INCOMPLETE';
1093 END IF;
1094 END IF;
1095
1096 d_position := 100;
1097 update_document_status
1098 ( p_doc_rec => p_doc_rec,
1099 p_auth_status => l_new_auth_status,
1100 p_status_lookup_code => NULL
1101 );
1102
1103 IF (l_new_auth_status = 'APPROVED') THEN
1104 d_position := 110;
1105 -- approved document should not have locks applied
1106 PO_DRAFTS_PVT.unlock_document
1107 ( p_po_header_id => p_doc_rec.po_header_id
1108 );
1109
1110 IF (PO_PDOI_PARAMS.g_sys.when_to_archive_blanket = 'APPROVE') THEN
1111
1112 -- <Unified Catalog R12 START>
1113 -- When importing blankets as approved, we need to rebuild the index
1114 -- for the catalog
1115 d_position := 130;
1116
1117 rebuild_catalog_index
1118 ( p_type => PO_PDOI_CONSTANTS.g_doc_type_BLANKET,
1119 p_doc_rec => p_doc_rec
1120 );
1121 -- <Unified Catalog R12 END>
1122
1123 -- bug5139656
1124 d_position := 120;
1125 archive_po
1126 ( p_doc_rec => p_doc_rec
1127 );
1128
1129 END IF;
1130 END IF;
1131 END IF;
1132 ELSE -- bug 7277317
1133 x_process_code := 'I';
1134 END IF;
1135
1136 IF (PO_LOG.d_proc) THEN
1137 PO_LOG.proc_end(d_module);
1138 END IF;
1139 EXCEPTION
1140 WHEN OTHERS THEN
1141 PO_MESSAGE_S.add_exc_msg
1142 ( p_pkg_name => d_pkg_name,
1143 p_procedure_name => d_api_name || '.' || d_position
1144 );
1145 RAISE;
1146 END create_blanket;
1147
1148
1149 -----------------------------------------------------------------------
1150 --Start of Comments
1151 --Name: update_blanket
1152 --Function:
1153 -- Performs necessary post processing actions to update a blanket
1154 --Parameters:
1155 --IN:
1156 --p_doc_rec
1157 -- Some attribute values of the document
1158 --IN OUT:
1159 --OUT:
1160 --End of Comments
1161 ------------------------------------------------------------------------
1162 PROCEDURE update_blanket
1163 ( p_doc_rec IN doc_row_type,
1164 x_process_code OUT NOCOPY VARCHAR2
1165 ) IS
1166
1167 d_api_name CONSTANT VARCHAR2(30) := 'update_blanket';
1168 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1169 d_position NUMBER;
1170
1171
1172 l_approval_method VARCHAR2(30);
1173 l_return_status VARCHAR2(1);
1174 l_doc_info PO_PDOI_PARAMS.doc_info_rec_type;
1175
1176 l_new_auth_status PO_HEADERS_ALL.authorization_status%TYPE;
1177
1178 l_need_to_create_src_rules VARCHAR2(1);
1179 l_lines src_rule_lines_rec_type;
1180 BEGIN
1181 d_position := 0;
1182 IF (PO_LOG.d_proc) THEN
1183 PO_LOG.proc_begin(d_module);
1184 END IF;
1185
1186 l_doc_info := PO_PDOI_PARAMS.g_docs_info(p_doc_rec.interface_header_id);
1187
1188 d_position := 10;
1189 l_need_to_create_src_rules := need_to_create_sourcing_rules
1190 ( p_doc_rec => p_doc_rec
1191 );
1192
1193 IF (l_need_to_create_src_rules = FND_API.G_TRUE) THEN
1194 d_position := 20;
1195 get_lines_for_src_rules
1196 ( p_doc_rec => p_doc_rec,
1197 x_lines => l_lines
1198 );
1199
1200 IF (PO_LOG.d_stmt) THEN
1201 PO_LOG.stmt(d_module, d_position, '# of lines requiring src rules: ' ||
1202 l_lines.po_line_id_tbl.COUNT);
1203 END IF;
1204 END IF;
1205
1206 d_position := 30;
1207 transfer_draft_to_txn (p_doc_rec => p_doc_rec);
1208
1209 IF (PO_LOG.d_stmt) THEN
1210 PO_LOG.stmt(d_module, d_position, 'Called transfer draft to txn');
1211 END IF;
1212
1213 IF (l_doc_info.has_lines_to_notify = FND_API.G_TRUE) THEN
1214 -- set header level process code to NOTIFIED
1215 UPDATE po_headers_interface
1216 SET process_code = 'NOTIFIED'
1217 WHERE interface_header_id = p_doc_rec.interface_header_id;
1218
1219 IF (PO_LOG.d_stmt) THEN
1220 PO_LOG.stmt(d_module, d_position, 'start price tolerance workflow');
1221 END IF;
1222
1223 -- start workflow
1224 PO_PDOI_PRICE_TOLERANCE_PVT.start_price_tolerance_wf
1225 (
1226 p_intf_header_id => p_doc_rec.interface_header_id,
1227 p_po_header_id => p_doc_rec.po_header_id,
1228 p_document_num => p_doc_rec.document_num,
1229 p_batch_id => PO_PDOI_PARAMS.g_request.batch_id,
1230 p_document_type => p_doc_rec.document_type,
1231 p_document_subtype => p_doc_rec.document_subtype,
1232 p_commit_interval => 1, -- parameter removed in R12
1233 p_any_line_updated => l_doc_info.has_lines_updated,
1234 p_buyer_id => PO_PDOI_PARAMS.g_request.buyer_id,
1235 p_agent_id => p_doc_rec.agent_id,
1236 p_vendor_id => p_doc_rec.vendor_id,
1237 p_vendor_name => p_doc_rec.vendor_name
1238 );
1239 ELSE
1240
1241 d_position := 40;
1242 get_approval_method
1243 ( p_doc_rec => p_doc_rec,
1244 x_approval_method => l_approval_method
1245 );
1246
1247 IF (PO_LOG.d_stmt) THEN
1248 PO_LOG.stmt(d_module, d_position, 'l_approval_method', l_approval_method);
1249 END IF;
1250 --bug 7277317 added a call for submission check
1251 submission_check
1252 ( p_doc_rec => p_doc_rec,
1253 x_return_status => l_return_status
1254 );
1255
1256 -- bug 7277317 added an if condition to check the return status of submission check
1257 -- proceed the below logic only if return status is success
1258 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1259 x_process_code := NULL;
1260 IF ( l_approval_method = PO_PDOI_CONSTANTS.g_appr_method_INIT_APPROVAL) THEN
1261 d_position := 50;
1262
1263 IF (PO_LOG.d_stmt) THEN
1264 PO_LOG.stmt(d_module, d_position, 'launch approval workflow');
1265 END IF;
1266
1267 start_po_approval_workflow
1268 ( p_doc_rec => p_doc_rec
1269 );
1270
1271 ELSIF (l_approval_method = PO_PDOI_CONSTANTS.g_appr_method_AUTO_APPROVE) THEN
1272 l_new_auth_status := 'APPROVED';
1273
1274 IF (l_need_to_create_src_rules = FND_API.G_TRUE) THEN
1275 d_position := 60;
1276 process_sourcing_rules
1277 ( p_doc_rec => p_doc_rec,
1278 p_approval_status => l_new_auth_status,
1279 p_lines => l_lines
1280 );
1281 END IF;
1282
1283 d_position := 70;
1284 update_document_status
1285 ( p_doc_rec => p_doc_rec,
1286 p_auth_status => l_new_auth_status,
1287 p_status_lookup_code => NULL
1288 );
1289
1290 IF (PO_PDOI_PARAMS.g_sys.when_to_archive_blanket = 'APPROVE') THEN
1291 d_position := 80;
1292
1293 IF (PO_LOG.d_stmt) THEN
1294 PO_LOG.stmt(d_module, d_position, 'Need to archive');
1295 END IF;
1296
1297 archive_po
1298 ( p_doc_rec => p_doc_rec
1299 );
1300
1301 -- <Unified Catalog R12 START>
1302 -- When importing blankets as approved, we need to rebuild the index
1303 -- for the catalog
1304 d_position := 90;
1305
1306 rebuild_catalog_index
1307 ( p_type => PO_PDOI_CONSTANTS.g_doc_type_BLANKET,
1308 p_doc_rec => p_doc_rec
1309 );
1310 -- <Unified Catalog R12 END>
1311
1312 END IF; -- when to archive blanket = 'APPROVE'
1313
1314 END IF; -- approval method = init approval
1315 ELSE -- bug 7277317
1316 x_process_code := 'I';
1317 END IF;
1318
1319 END IF;
1320
1321 IF (PO_LOG.d_proc) THEN
1322 PO_LOG.proc_end(d_module);
1323 END IF;
1324 EXCEPTION
1325 WHEN OTHERS THEN
1326 PO_MESSAGE_S.add_exc_msg
1327 ( p_pkg_name => d_pkg_name,
1328 p_procedure_name => d_api_name || '.' || d_position
1329 );
1330 RAISE;
1331 END update_blanket;
1332
1333
1334
1335 -----------------------------------------------------------------------
1336 --Start of Comments
1337 --Name: create_quotation
1338 --Function:
1339 -- Performs necessary post processing action to create a quotation
1340 --Parameters:
1341 --IN:
1342 --p_doc_rec
1343 -- Some attribute values of the document
1344 --IN OUT:
1345 --OUT:
1346 --End of Comments
1347 ------------------------------------------------------------------------
1348 PROCEDURE create_quotation
1349 ( p_doc_rec IN doc_row_type
1350 ) IS
1351
1352 d_api_name CONSTANT VARCHAR2(30) := 'create_quotation';
1353 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1354 d_position NUMBER;
1355
1356
1357 l_approval_method VARCHAR2(30);
1358
1359 l_need_to_create_src_rules VARCHAR2(1);
1360 l_lines src_rule_lines_rec_type;
1361 BEGIN
1362
1363 d_position := 0;
1364 IF (PO_LOG.d_proc) THEN
1365 PO_LOG.proc_begin(d_module);
1366 END IF;
1367
1368 assign_document_number ( p_doc_rec => p_doc_rec );
1369
1370 d_position := 10;
1371 l_need_to_create_src_rules := need_to_create_sourcing_rules
1372 ( p_doc_rec => p_doc_rec
1373 );
1374
1375 IF (l_need_to_create_src_rules = FND_API.G_TRUE) THEN
1376 d_position := 20;
1377 get_lines_for_src_rules
1378 ( p_doc_rec => p_doc_rec,
1379 x_lines => l_lines
1380 );
1381
1382 IF (PO_LOG.d_stmt) THEN
1383 PO_LOG.stmt(d_module, d_position, '# lines requiring src rules: ' || l_lines.po_line_id_tbl.COUNT);
1384 END IF;
1385 END IF;
1386
1387 d_position := 30;
1388 transfer_draft_to_txn (p_doc_rec => p_doc_rec);
1389
1390 d_position := 40;
1391 get_approval_method
1392 ( p_doc_rec => p_doc_rec,
1393 x_approval_method => l_approval_method
1394 );
1395
1396 IF (PO_LOG.d_stmt) THEN
1397 PO_LOG.stmt(d_module, d_position, 'l_approval_method', l_approval_method);
1398 END IF;
1399
1400 IF (l_approval_method = PO_PDOI_CONSTANTS.g_appr_method_AUTO_APPROVE) THEN
1401 IF (l_need_to_create_src_rules = FND_API.G_TRUE) THEN
1402 d_position := 50;
1403
1404 IF (PO_LOG.d_stmt) THEN
1405 PO_LOG.stmt(d_module, d_position, 'creating sourcing rules');
1406 END IF;
1407
1408 process_sourcing_rules
1409 ( p_doc_rec => p_doc_rec,
1410 p_approval_status => 'APPROVED',
1411 p_lines => l_lines
1412 );
1413 END IF;
1414
1415 d_position := 60;
1416 update_document_status
1417 ( p_doc_rec => p_doc_rec,
1418 p_auth_status => NULL,
1419 p_status_lookup_code => 'A'
1420 );
1421 END IF;
1422
1423 -- <Unified Catalog R12 START>
1424 -- When importing quotation, we need to rebuild the index
1425 -- for the catalog
1426 d_position := 70;
1427
1428 rebuild_catalog_index
1429 ( p_type => PO_PDOI_CONSTANTS.g_doc_type_QUOTATION,
1430 p_doc_rec => p_doc_rec
1431 );
1432 -- <Unified Catalog R12 END>
1433
1434 IF (PO_LOG.d_proc) THEN
1435 PO_LOG.proc_end(d_module);
1436 END IF;
1437 EXCEPTION
1438 WHEN OTHERS THEN
1439 PO_MESSAGE_S.add_exc_msg
1440 ( p_pkg_name => d_pkg_name,
1441 p_procedure_name => d_api_name || '.' || d_position
1442 );
1443 RAISE;
1444 END create_quotation;
1445
1446 -----------------------------------------------------------------------
1447 --Start of Comments
1448 --Name: update_quotation
1449 --Function:
1450 -- Performs necessary post processing action to update a quotation
1451 --Parameters:
1452 --IN:
1453 --p_doc_rec
1454 -- Some attribute values of the document
1455 --IN OUT:
1456 --OUT:
1457 --End of Comments
1458 ------------------------------------------------------------------------
1459 PROCEDURE update_quotation
1460 ( p_doc_rec IN doc_row_type
1461 ) IS
1462
1463 d_api_name CONSTANT VARCHAR2(30) := 'update_quotation';
1464 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1465 d_position NUMBER;
1466
1467
1468 l_approval_method VARCHAR2(30);
1469
1470 l_doc_info PO_PDOI_PARAMS.doc_info_rec_type;
1471
1472 l_need_to_create_src_rules VARCHAR2(1);
1473 l_lines src_rule_lines_rec_type;
1474
1475 BEGIN
1476 d_position := 0;
1477 IF (PO_LOG.d_proc) THEN
1478 PO_LOG.proc_begin(d_module);
1479 END IF;
1480
1481 l_doc_info := PO_PDOI_PARAMS.g_docs_info(p_doc_rec.interface_header_id);
1482
1483 d_position := 20;
1484 l_need_to_create_src_rules := need_to_create_sourcing_rules
1485 ( p_doc_rec => p_doc_rec
1486 );
1487
1488 IF (l_need_to_create_src_rules = FND_API.G_TRUE) THEN
1489 d_position := 30;
1490 get_lines_for_src_rules
1491 ( p_doc_rec => p_doc_rec,
1492 x_lines => l_lines
1493 );
1494
1495 IF (PO_LOG.d_stmt) THEN
1496 PO_LOG.stmt(d_module, d_position, '# lines requiring src rules: ' ||
1497 l_lines.po_line_id_tbl.COUNT);
1498 END IF;
1499 END IF;
1500
1501 d_position := 40;
1502 transfer_draft_to_txn (p_doc_rec => p_doc_rec);
1503
1504 IF (l_doc_info.has_lines_to_notify = FND_API.G_TRUE) THEN
1505 d_position := 10;
1506
1507 -- set header level process code to NOTIFIED
1508 UPDATE po_headers_interface
1509 SET process_code = 'NOTIFIED'
1510 WHERE interface_header_id = p_doc_rec.interface_header_id;
1511
1512 IF (PO_LOG.d_stmt) THEN
1513 PO_LOG.stmt(d_module, d_position, 'start price tolerance workflow');
1514 END IF;
1515
1516 -- start workflow
1517 PO_PDOI_PRICE_TOLERANCE_PVT.start_price_tolerance_wf
1518 (
1519 p_intf_header_id => p_doc_rec.interface_header_id,
1520 p_po_header_id => p_doc_rec.po_header_id,
1521 p_document_num => p_doc_rec.document_num,
1522 p_batch_id => PO_PDOI_PARAMS.g_request.batch_id,
1523 p_document_type => p_doc_rec.document_type,
1524 p_document_subtype => p_doc_rec.document_subtype,
1525 p_commit_interval => 1, -- parameter removed in R12
1526 p_any_line_updated => l_doc_info.has_lines_updated,
1527 p_buyer_id => PO_PDOI_PARAMS.g_request.buyer_id,
1528 p_agent_id => p_doc_rec.agent_id,
1529 p_vendor_id => p_doc_rec.vendor_id,
1530 p_vendor_name => p_doc_rec.vendor_name
1531 );
1532
1533 ELSE
1534
1535 d_position := 50;
1536 get_approval_method
1537 ( p_doc_rec => p_doc_rec,
1538 x_approval_method => l_approval_method
1539 );
1540
1541 IF (PO_LOG.d_stmt) THEN
1542 PO_LOG.stmt(d_module, d_position, 'l_approval_method', l_approval_method);
1543 END IF;
1544
1545 IF (l_approval_method = PO_PDOI_CONSTANTS.g_appr_method_AUTO_APPROVE) THEN
1546 IF (l_need_to_create_src_rules = FND_API.G_TRUE) THEN
1547 d_position := 60;
1548
1549 IF (PO_LOG.d_stmt) THEN
1550 PO_LOG.stmt(d_module, d_position, 'Creating Sourcing rules');
1551 END IF;
1552
1553 process_sourcing_rules
1554 ( p_doc_rec => p_doc_rec,
1555 p_approval_status => 'APPROVED',
1556 p_lines => l_lines
1557 );
1558 END IF;
1559 END IF;
1560 END IF;
1561
1562 -- <Unified Catalog R12 START>
1563 -- When importing quotation, we need to rebuild the index
1564 -- for the catalog
1565 d_position := 70;
1566
1567 rebuild_catalog_index
1568 ( p_type => PO_PDOI_CONSTANTS.g_doc_type_QUOTATION,
1569 p_doc_rec => p_doc_rec
1570 );
1571 -- <Unified Catalog R12 END>
1572
1573 IF (PO_LOG.d_proc) THEN
1574 PO_LOG.proc_end(d_module);
1575 END IF;
1576 EXCEPTION
1577 WHEN OTHERS THEN
1578 PO_MESSAGE_S.add_exc_msg
1579 ( p_pkg_name => d_pkg_name,
1580 p_procedure_name => d_api_name || '.' || d_position
1581 );
1582 RAISE;
1583 END update_quotation;
1584
1585 -----------------------------------------------------------------------
1586 --Start of Comments
1587 --Name: create_standard_po
1588 --Function:
1589 -- Performs necessary post processing action to create a standard po
1590 --Parameters:
1591 --IN:
1592 --p_doc_rec
1593 -- Some attribute values of the document
1594 --IN OUT:
1595 --OUT:
1596 --End of Comments
1597 ------------------------------------------------------------------------
1598 PROCEDURE create_standard_po
1599 ( p_doc_rec IN doc_row_type,
1600 x_process_code OUT NOCOPY VARCHAR2
1601 ) IS
1602
1603 d_api_name CONSTANT VARCHAR2(30) := 'create_standard_po';
1604 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1605 d_position NUMBER;
1606
1607 l_new_auth_status PO_HEADERS_ALL.authorization_status%TYPE;
1608 l_approval_method VARCHAR2(30);
1609 l_need_to_encumber VARCHAR2(1);
1610 l_fund_reserve_result VARCHAR2(1);
1611 l_return_status VARCHAR2(1);
1612
1613 p_return_status VARCHAR2(1); -- bug 7277317
1614 BEGIN
1615
1616 d_position := 0;
1617 IF (PO_LOG.d_proc) THEN
1618 PO_LOG.proc_begin(d_module);
1619 END IF;
1620
1621 assign_document_number ( p_doc_rec => p_doc_rec );
1622
1623 d_position := 10;
1624 transfer_draft_to_txn (p_doc_rec => p_doc_rec);
1625
1626 d_position := 20;
1627
1628 -- calculate tax for the standard PO
1629 calculate_tax
1630 ( p_doc_rec => p_doc_rec,
1631 x_return_status => l_return_status
1632 );
1633
1634 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1635 d_position := 30;
1636
1637 -- change document status to 'INCOMPLETE'
1638 update_document_status
1639 ( p_doc_rec => p_doc_rec,
1640 p_auth_status => 'INCOMPLETE',
1641 p_status_lookup_code => NULL
1642 );
1643 ELSE
1644 d_position := 50;
1645 get_approval_method
1646 ( p_doc_rec => p_doc_rec,
1647 x_approval_method => l_approval_method
1648 );
1649
1650 IF (PO_LOG.d_stmt) THEN
1651 PO_LOG.stmt(d_module, d_position, 'l_approval_method', l_approval_method);
1652 END IF;
1653 --bug 7277317 added a call for submission check
1654 submission_check
1655 ( p_doc_rec => p_doc_rec,
1656 x_return_status => p_return_status
1657 );
1658 -- bug 7277317 added an if condition to check the return status of submission check
1659 -- proceed the below logic only if return status is success
1660 IF (p_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1661 x_process_code := NULL;
1662 IF ( l_approval_method = PO_PDOI_CONSTANTS.g_appr_method_INIT_APPROVAL) THEN
1663 d_position := 60;
1664
1665 IF (PO_LOG.d_stmt) THEN
1666 PO_LOG.stmt(d_module, d_position, 'launching approval workflow');
1667 END IF;
1668
1669 start_po_approval_workflow
1670 ( p_doc_rec => p_doc_rec
1671 );
1672
1673 ELSIF ( l_approval_method = PO_PDOI_CONSTANTS.g_appr_method_AUTO_APPROVE) THEN
1674
1675 l_new_auth_status := 'APPROVED';
1676 d_position := 70;
1677 l_need_to_encumber := need_to_encumber
1678 ( p_doc_rec => p_doc_rec,
1679 p_new_auth_status => l_new_auth_status
1680 );
1681
1682
1683 IF (PO_LOG.d_stmt) THEN
1684 PO_LOG.stmt(d_module, d_position, 'l_need_to_encumber', l_need_to_encumber);
1685 END IF;
1686
1687 IF ( l_need_to_encumber = FND_API.G_TRUE) THEN
1688 d_position := 80;
1689 -- Temporarily setting the status to PRE-APPROVED
1690 update_document_status
1691 ( p_doc_rec => p_doc_rec,
1692 p_auth_status => 'PRE-APPROVED',
1693 p_status_lookup_code => NULL
1694 );
1695
1696 d_position := 90;
1697 -- reserve document
1698 reserve_fund
1699 ( p_doc_rec => p_doc_rec,
1700 x_result => l_fund_reserve_result
1701 );
1702
1703 IF (l_fund_reserve_result = FND_API.G_FALSE) THEN
1704 d_position := 100;
1705 IF (PO_LOG.d_stmt) THEN
1706 PO_LOG.stmt(d_module, d_position, 'failed fund reservation');
1707 END IF;
1708
1709 /*6851752 Start
1710 Set Authorization Status to PRE-APPROVED, when it fails funds check */
1711
1712 l_new_auth_status := 'PRE-APPROVED';
1713
1714 /*6851752 End */
1715 END IF;
1716 END IF;
1717
1718 d_position := 110;
1719 update_document_status
1720 ( p_doc_rec => p_doc_rec,
1721 p_auth_status => l_new_auth_status,
1722 p_status_lookup_code => NULL
1723 );
1724
1725 /*6851752 Start
1726 Launch Approval, when it fails funds check and the Authrization status is PRE-APPROVED*/
1727
1728 IF (l_fund_reserve_result = FND_API.G_FALSE) AND (l_new_auth_status = 'PRE-APPROVED') THEN
1729
1730 IF (PO_LOG.d_stmt) THEN
1731 PO_LOG.stmt(d_module, d_position, 'launching approval workflow');
1732 END IF;
1733 start_po_approval_workflow
1734 ( p_doc_rec => p_doc_rec
1735 );
1736
1737 END IF;
1738
1739 /*6851752 End */
1740
1741 IF (l_new_auth_status = 'APPROVED') THEN
1742 IF (PO_PDOI_PARAMS.g_sys.when_to_archive_std_po = 'APPROVE') THEN
1743 d_position := 120;
1744 IF (PO_LOG.d_stmt) THEN
1745 PO_LOG.stmt(d_module, d_position, 'archiving...');
1746 END IF;
1747
1748 archive_po
1749 ( p_doc_rec => p_doc_rec
1750 );
1751 END IF;
1752
1753 d_position := 130;
1754 create_po_supply
1755 ( p_doc_rec => p_doc_rec,
1756 p_action => 'Approve_PO_Supply'
1757 );
1758
1759 d_position := 140;
1760 create_delivery_record (p_doc_rec => p_doc_rec);
1761
1762 d_position := 150;
1763 close_po (p_doc_rec => p_doc_rec);
1764
1765 END IF;
1766 END IF;
1767 ELSE -- bug 7277317
1768 x_process_code := 'I';
1769 END IF;
1770 END IF;
1771
1772 IF (PO_LOG.d_proc) THEN
1773 PO_LOG.proc_end(d_module);
1774 END IF;
1775
1776 EXCEPTION
1777 WHEN OTHERS THEN
1778 PO_MESSAGE_S.add_exc_msg
1779 ( p_pkg_name => d_pkg_name,
1780 p_procedure_name => d_api_name || '.' || d_position
1781 );
1782 RAISE;
1783 END create_standard_po;
1784
1785 -----------------------------------------------------------------------
1786 --Start of Comments
1787 --Name: update_standard_po
1788 --Function:
1789 -- Performs necessary post processing action to update a standard po
1790 --Parameters:
1791 --IN:
1792 --p_doc_rec
1793 -- Some attribute values of the document
1794 --IN OUT:
1795 --OUT:
1796 --End of Comments
1797 ------------------------------------------------------------------------
1798 PROCEDURE update_standard_po
1799 ( p_doc_rec IN doc_row_type,
1800 x_process_code OUT NOCOPY VARCHAR2
1801 ) IS
1802
1803 d_api_name CONSTANT VARCHAR2(30) := 'update_standard_po';
1804 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1805 d_position NUMBER;
1806
1807
1808 l_approval_method VARCHAR2(30);
1809 l_new_auth_status PO_HEADERS_ALL.authorization_status%TYPE;
1810 l_update_auth_status VARCHAR2(1);
1811
1812 l_need_to_encumber VARCHAR2(1);
1813 l_fund_reserve_result VARCHAR2(1);
1814 l_return_status VARCHAR2(1);
1815
1816 p_return_status VARCHAR2(1); -- bug 7277317
1817 BEGIN
1818 d_position := 0;
1819 IF (PO_LOG.d_proc) THEN
1820 PO_LOG.proc_begin(d_module);
1821 END IF;
1822
1823 transfer_draft_to_txn (p_doc_rec => p_doc_rec);
1824
1825 d_position := 10;
1826
1827 -- calculate tax for the standard PO
1828 calculate_tax
1829 ( p_doc_rec => p_doc_rec,
1830 x_return_status => l_return_status
1831 );
1832
1833 IF (l_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1834
1835 d_position := 30;
1836
1837 get_approval_method
1838 ( p_doc_rec => p_doc_rec,
1839 x_approval_method => l_approval_method
1840 );
1841
1842 IF (PO_LOG.d_stmt) THEN
1843 PO_LOG.stmt(d_module, d_position, 'l_approval_method', l_approval_method);
1844 END IF;
1845 --bug 7277317 added a call for submission check
1846 submission_check
1847 ( p_doc_rec => p_doc_rec,
1848 x_return_status => p_return_status
1849 );
1850 -- bug 7277317 added an if condition to check the return status of submission check
1851 -- proceed the below logic only if return status is success
1852 IF (p_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1853 x_process_code := NULL;
1854 IF ( l_approval_method = PO_PDOI_CONSTANTS.g_appr_method_INIT_APPROVAL) THEN
1855 d_position := 40;
1856 IF (PO_LOG.d_stmt) THEN
1857 PO_LOG.stmt(d_module, d_position, 'start approval workflow');
1858 END IF;
1859
1860 start_po_approval_workflow
1861 ( p_doc_rec => p_doc_rec
1862 );
1863
1864 ELSIF (l_approval_method = PO_PDOI_CONSTANTS.g_appr_method_AUTO_APPROVE) THEN
1865 d_position := 50;
1866 l_update_auth_status := FND_API.G_TRUE;
1867 l_new_auth_status := 'APPROVED';
1868
1869 l_need_to_encumber := need_to_encumber
1870 ( p_doc_rec => p_doc_rec,
1871 p_new_auth_status => l_new_auth_status
1872 );
1873
1874 IF (PO_LOG.d_stmt) THEN
1875 PO_LOG.stmt(d_module, d_position, 'l_need_to_encumber', l_need_to_encumber);
1876 END IF;
1877
1878 IF (l_need_to_encumber = FND_API.G_TRUE) THEN
1879 d_position := 60;
1880 -- reserve document
1881 reserve_fund
1882 ( p_doc_rec => p_doc_rec,
1883 x_result => l_fund_reserve_result
1884 );
1885
1886 IF (l_fund_reserve_result = FND_API.G_FALSE) THEN
1887 d_position := 70;
1888 IF (PO_LOG.d_stmt) THEN
1889 PO_LOG.stmt(d_module, d_position, 'l_approval_method', l_approval_method);
1890 END IF;
1891
1892 -- No need to set the authorziation_status because transfer program
1893 -- should have handled the approval status properly
1894 l_update_auth_status := FND_API.G_FALSE;
1895 END IF;
1896 END IF;
1897
1898 IF (l_update_auth_status = FND_API.G_TRUE) THEN
1899 d_position := 80;
1900 update_document_status
1901 ( p_doc_rec => p_doc_rec,
1902 p_auth_status => l_new_auth_status,
1903 p_status_lookup_code => NULL
1904 );
1905
1906 IF (l_new_auth_status = 'APPROVED') THEN
1907 IF (PO_PDOI_PARAMS.g_sys.when_to_archive_std_po = 'APPROVE') THEN
1908 d_position := 90;
1909 IF (PO_LOG.d_stmt) THEN
1910 PO_LOG.stmt(d_module, d_position, 'l_archiving...');
1911 END IF;
1912
1913 archive_po
1914 ( p_doc_rec => p_doc_rec
1915 );
1916 END IF;
1917
1918 d_position := 100;
1919 create_po_supply
1920 ( p_doc_rec => p_doc_rec,
1921 p_action => 'Approve_PO_Supply'
1922 );
1923
1924 d_position := 110;
1925 create_delivery_record (p_doc_rec => p_doc_rec);
1926 d_position := 120;
1927 close_po (p_doc_rec => p_doc_rec);
1928 END IF;
1929
1930 END IF;
1931 END IF;
1932 ELSE -- bug 7277317
1933 x_process_code := 'I';
1934 END IF;
1935 END IF;
1936
1937 IF (PO_LOG.d_proc) THEN
1938 PO_LOG.proc_end(d_module);
1939 END IF;
1940
1941 EXCEPTION
1942 WHEN OTHERS THEN
1943 PO_MESSAGE_S.add_exc_msg
1944 ( p_pkg_name => d_pkg_name,
1945 p_procedure_name => d_api_name || '.' || d_position
1946 );
1947 RAISE;
1948 END update_standard_po;
1949
1950
1951 -----------------------------------------------------------------------
1952 --Start of Comments
1953 --Name: expire_document
1954 --Function:
1955 -- Expires a document, identified by orig_po_header_id column
1956 --Parameters:
1957 --IN:
1958 --p_doc_rec
1959 -- Some attribute values of the document
1960 --IN OUT:
1961 --OUT:
1962 --End of Comments
1963 ------------------------------------------------------------------------
1964 PROCEDURE expire_document
1965 ( p_doc_rec IN doc_row_type
1966 ) IS
1967
1968 d_api_name CONSTANT VARCHAR2(30) := 'expire_document';
1969 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
1970 d_position NUMBER;
1971
1972 l_expiration_date DATE;
1973
1974 BEGIN
1975
1976 d_position := 0;
1977 IF (PO_LOG.d_proc) THEN
1978 PO_LOG.proc_begin(d_module);
1979 END IF;
1980
1981 l_expiration_date := p_doc_rec.intf_start_date - 1;
1982
1983 IF (PO_LOG.d_stmt) THEN
1984 PO_LOG.stmt(d_module, d_position, 'l_expiration_date', l_expiration_date);
1985 END IF;
1986
1987 UPDATE po_headers_all
1988 SET start_date = NVL(start_date, l_expiration_date),
1989 end_date = l_expiration_date,
1990 last_updated_by = FND_GLOBAL.user_id,
1991 last_update_date = SYSDATE
1992 WHERE po_header_id = p_doc_rec.orig_po_header_id;
1993
1994 IF (PO_LOG.d_proc) THEN
1995 PO_LOG.proc_end(d_module);
1996 END IF;
1997 EXCEPTION
1998 WHEN OTHERS THEN
1999 PO_MESSAGE_S.add_exc_msg
2000 ( p_pkg_name => d_pkg_name,
2001 p_procedure_name => d_api_name || '.' || d_position
2002 );
2003 RAISE;
2004 END expire_document;
2005
2006
2007
2008 -----------------------------------------------------------------------
2009 --Start of Comments
2010 --Name: assign_document_number
2011 --Function:
2012 -- Assign document number for document being imported
2013 --Parameters:
2014 --IN:
2015 --p_doc_rec
2016 -- Some attribute values of the document
2017 --IN OUT:
2018 --OUT:
2019 --End of Comments
2020 ------------------------------------------------------------------------
2021 PROCEDURE assign_document_number
2022 ( p_doc_rec IN doc_row_type
2023 ) IS
2024
2025 d_api_name CONSTANT VARCHAR2(30) := 'assign_document_number';
2026 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2027 d_position NUMBER;
2028
2029 l_document_num PO_HEADERS_ALL.segment1%TYPE;
2030
2031 BEGIN
2032
2033 d_position := 0;
2034 IF (PO_LOG.d_proc) THEN
2035 PO_LOG.proc_begin(d_module);
2036 END IF;
2037
2038 -- bug5024833
2039 -- Do not generate the po number from the system if number generation
2040 -- method is Manual
2041 IF ( (PO_PDOI_PARAMS.g_request.document_type =
2042 PO_PDOI_CONSTANTS.g_doc_type_QUOTATION AND
2043 PO_PDOI_PARAMS.g_sys.user_defined_quote_num_code = 'MANUAL')
2044 OR
2045 (PO_PDOI_PARAMS.g_request.document_type IN
2046 (PO_PDOI_CONSTANTS.g_doc_type_BLANKET,
2047 PO_PDOI_CONSTANTS.g_doc_type_STANDARD) AND
2048 PO_PDOI_PARAMS.g_sys.user_defined_po_num_code = 'MANUAL')) THEN
2049
2050 d_position := 10;
2051
2052 IF (PO_LOG.d_stmt) THEN
2053 PO_LOG.stmt(d_module, d_position, 'Manual numbering. No need to generate new number');
2054 END IF;
2055
2056 ELSIF (p_doc_rec.doc_num_provided = 'Y') THEN
2057 -- bug5028275
2058 -- If user provides their own document number, use the one they provide
2059 d_position := 20;
2060
2061 IF (PO_LOG.d_stmt) THEN
2062 PO_LOG.stmt(d_module, d_position, 'User provides document number. No need to generate new number');
2063 END IF;
2064
2065 ELSE
2066 d_position := 30;
2067
2068 IF (PO_PDOI_PARAMS.g_request.document_type =
2069 PO_PDOI_CONSTANTS.g_doc_type_QUOTATION) THEN
2070
2071 l_document_num := PO_CORE_SV1.default_po_unique_identifier
2072 ( x_table_name => 'PO_HEADERS_QUOTE'
2073 );
2074 ELSIF (PO_PDOI_PARAMS.g_request.document_type IN
2075 ( PO_PDOI_CONSTANTS.g_doc_type_BLANKET,
2076 PO_PDOI_CONSTANTS.g_doc_type_STANDARD)) THEN
2077
2078 l_document_num := PO_CORE_SV1.default_po_unique_identifier
2079 ( x_table_name => 'PO_HEADERS'
2080 );
2081 END IF;
2082
2083 IF (PO_LOG.d_stmt) THEN
2084 PO_LOG.stmt(d_module, d_position, 'Get new document number');
2085 PO_LOG.stmt(d_module, d_position, 'l_document_num', l_document_num);
2086 END IF;
2087
2088 d_position := 40;
2089
2090 UPDATE po_headers_draft_all
2091 SET segment1 = l_document_num
2092 WHERE po_header_id = p_doc_rec.po_header_id;
2093
2094 END IF;
2095
2096 IF (PO_LOG.d_proc) THEN
2097 PO_LOG.proc_end(d_module);
2098 END IF;
2099
2100 EXCEPTION
2101 WHEN OTHERS THEN
2102 PO_MESSAGE_S.add_exc_msg
2103 ( p_pkg_name => d_pkg_name,
2104 p_procedure_name => d_api_name || '.' || d_position
2105 );
2106 RAISE;
2107 END assign_document_number;
2108
2109 -----------------------------------------------------------------------
2110 --Start of Comments
2111 --Name: need_to_create_sourcing_rules
2112 --Function:
2113 -- Check whether sourcing rules need to be created
2114 --Parameters:
2115 --IN:
2116 --p_doc_rec
2117 -- Some attribute values of the document
2118 --IN OUT:
2119 --OUT:
2120 --Returns:
2121 --End of Comments
2122 ------------------------------------------------------------------------
2123 FUNCTION need_to_create_sourcing_rules
2124 ( p_doc_rec IN doc_row_type
2125 ) RETURN VARCHAR2 IS
2126
2127 d_api_name CONSTANT VARCHAR2(30) := 'need_to_create_sourcing_rules';
2128 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2129 d_position NUMBER;
2130
2131 BEGIN
2132 d_position := 0;
2133 IF (PO_LOG.d_proc) THEN
2134 PO_LOG.proc_begin(d_module);
2135 END IF;
2136
2137 IF ( NVL(p_doc_rec.load_sourcing_rules_flag,
2138 PO_PDOI_PARAMS.g_request.create_sourcing_rules_flag) = 'Y') THEN
2139 RETURN FND_API.G_TRUE;
2140 ELSE
2141 RETURN FND_API.G_FALSE;
2142 END IF;
2143
2144 EXCEPTION
2145 WHEN OTHERS THEN
2146 PO_MESSAGE_S.add_exc_msg
2147 ( p_pkg_name => d_pkg_name,
2148 p_procedure_name => d_api_name || '.' || d_position
2149 );
2150 RAISE;
2151 END need_to_create_sourcing_rules;
2152
2153 -----------------------------------------------------------------------
2154 --Start of Comments
2155 --Name: get_lines_for_src_rules
2156 --Function:
2157 -- Find out all the lines that may require sourcing rules to be created for
2158 --Parameters:
2159 --IN:
2160 --p_doc_rec
2161 -- Some attribute values of the document
2162 --IN OUT:
2163 --OUT:
2164 --x_lines
2165 -- Lines that require sourcing rules creation
2166 --End of Comments
2167 ------------------------------------------------------------------------
2168 PROCEDURE get_lines_for_src_rules
2169 ( p_doc_rec IN doc_row_type,
2170 x_lines OUT NOCOPY src_rule_lines_rec_type
2171 ) IS
2172
2173 d_api_name CONSTANT VARCHAR2(30) := 'get_lines_for_src_rules';
2174 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2175 d_position NUMBER;
2176
2177 BEGIN
2178 d_position := 0;
2179 IF (PO_LOG.d_proc) THEN
2180 PO_LOG.proc_begin(d_module);
2181 END IF;
2182
2183 --SQL What: Select columns needed for calling sourcing rule/asl creation
2184 -- API. Lines that are in draft but not transaction table will
2185 -- be candidates for sourcing rules creation.
2186 --SQL Why: Need to find out what lines are new lines being created so
2187 -- that sourcing rules and ASLs are updated accordingly
2188
2189 -- bug4181354: Do not send lines for sourcing rules creation if
2190 -- it's a price break line.
2191
2192 SELECT PLD.po_line_id,
2193 PLD.item_id,
2194 PLD.category_id,
2195 PLI.interface_line_id,
2196 PLI.sourcing_rule_name,
2197 PLI.effective_date,
2198 PLI.expiration_date
2199 BULK COLLECT
2200 INTO x_lines.po_line_id_tbl,
2201 x_lines.item_id_tbl,
2202 x_lines.category_id_tbl,
2203 x_lines.interface_line_id_tbl,
2204 x_lines.sourcing_rule_name_tbl,
2205 x_lines.effective_date_tbl,
2206 x_lines.expiration_date_tbl
2207 FROM po_headers_interface PHI,
2208 po_lines_interface PLI,
2209 po_lines_draft_all PLD
2210 WHERE PHI.interface_header_id = p_doc_rec.interface_header_id
2211 AND PHI.interface_header_id = PLI.interface_header_id
2212 AND PHI.draft_id = PLD.draft_id
2213 AND PLI.po_line_id = PLD.po_line_id
2214 AND NVL(PLI.price_break_flag, 'N') <> 'Y'
2215 AND PLD.item_id IS NOT NULL
2216 AND PLD.order_type_lookup_code = 'QUANTITY'
2217 AND NOT EXISTS
2218 (SELECT 1
2219 FROM po_lines_all PLA
2220 WHERE PLD.po_line_id = PLA.po_line_id);
2221
2222 IF (PO_LOG.d_proc) THEN
2223 PO_LOG.proc_end(d_module);
2224 END IF;
2225 EXCEPTION
2226 WHEN OTHERS THEN
2227 PO_MESSAGE_S.add_exc_msg
2228 ( p_pkg_name => d_pkg_name,
2229 p_procedure_name => d_api_name || '.' || d_position
2230 );
2231 RAISE;
2232 END get_lines_for_src_rules;
2233
2234
2235 -----------------------------------------------------------------------
2236 --Start of Comments
2237 --Name: process_sourcing_rules
2238 --Function:
2239 -- Call an API to create sourcing rules and ASL
2240 --Parameters:
2241 --IN:
2242 --p_doc_rec
2243 -- Some attribute values of the document
2244 --p_approval_status
2245 -- Approval status of the document
2246 --p_lines
2247 -- Lines that require sourcing rules creation
2248 --IN OUT:
2249 --OUT:
2250 --End of Comments
2251 ------------------------------------------------------------------------
2252 PROCEDURE process_sourcing_rules
2253 ( p_doc_rec IN doc_row_type,
2254 p_approval_status IN VARCHAR2,
2255 p_lines IN src_rule_lines_rec_type
2256 ) IS
2257
2258 d_api_name CONSTANT VARCHAR2(30) := 'process_sourcing_rules';
2259 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2260 d_position NUMBER;
2261
2262 l_vendor_id PO_VENDORS.vendor_id%TYPE;
2263 l_vendor_site_id PO_VENDOR_SITES_ALL.vendor_site_id%TYPE;
2264
2265 l_header_processable_flag VARCHAR2(1) := 'Y';
2266
2267 l_return_status VARCHAR2(1);
2268 l_msg_count NUMBER;
2269 l_msg_data VARCHAR2(2000);
2270 BEGIN
2271
2272 d_position := 0;
2273 IF (PO_LOG.d_proc) THEN
2274 PO_LOG.proc_begin(d_module);
2275 END IF;
2276
2277 IF (p_lines.po_line_id_tbl IS NULL) THEN
2278 d_position := 10;
2279 RETURN;
2280 ELSE
2281 d_position := 20;
2282 SELECT vendor_id,
2283 vendor_site_id
2284 INTO l_vendor_id,
2285 l_vendor_site_id
2286 FROM po_headers_all
2287 WHERE po_header_id = p_doc_rec.po_header_id;
2288
2289 FOR i IN 1..p_lines.po_line_id_tbl.COUNT LOOP
2290 d_position := 30;
2291
2292 IF (PO_LOG.d_stmt) THEN
2293 PO_LOG.stmt(d_module, d_position, 'processing po_line_id: ' ||
2294 p_lines.po_line_id_tbl(i));
2295 END IF;
2296
2297 PO_CREATE_SR_ASL.create_sourcing_rules_asl
2298 ( p_api_version => 1.0,
2299 p_init_msg_list => FND_API.G_FALSE,
2300 p_commit => FND_API.G_FALSE,
2301 x_return_status => l_return_status,
2302 x_msg_count => l_msg_count,
2303 x_msg_data => l_msg_data,
2304 p_interface_header_id => p_doc_rec.interface_header_id,
2305 p_interface_line_id => p_lines.interface_line_id_tbl(i),
2306 p_document_id => p_doc_rec.po_header_id,
2307 p_po_line_id => p_lines.po_line_id_tbl(i),
2308 p_document_type => PO_PDOI_PARAMS.g_request.document_type,
2309 p_approval_status => p_approval_status,
2310 p_vendor_id => l_vendor_id,
2311 p_vendor_site_id => l_vendor_site_id,
2312 p_inv_org_id => PO_PDOI_PARAMS.g_request.sourcing_inv_org_id,
2313 p_sourcing_level => PO_PDOI_PARAMS.g_request.sourcing_level,
2314 p_item_id => p_lines.item_id_tbl(i),
2315 p_category_id => p_lines.category_id_tbl(i),
2316 p_rel_gen_method => PO_PDOI_PARAMS.g_request.rel_gen_method,
2317 p_rule_name => p_lines.sourcing_rule_name_tbl(i),
2318 p_rule_name_prefix => 'PURCH_OPEN_INTERFACE',
2319 p_start_date => p_lines.effective_date_tbl(i),
2320 p_end_date => p_lines.expiration_date_tbl(i),
2321 p_assignment_set_id => NULL,
2322 p_create_update_code => 'CREATE_UPDATE',
2323 p_interface_error_code => 'PO_DOCS_OPEN_INTERFACE',
2324 x_header_processable_flag => l_header_processable_flag
2325 );
2326
2327 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2328 d_position := 40;
2329 IF (PO_LOG.d_stmt) THEN
2330 PO_LOG.stmt(d_module, d_position, 'Sourcing rule creation failed' ||
2331 ' with status: ' || l_return_status || '. Continue to ' ||
2332 'process.' );
2333 END IF;
2334
2335 END IF;
2336 END LOOP;
2337 END IF;
2338
2339 IF (PO_LOG.d_proc) THEN
2340 PO_LOG.proc_end(d_module);
2341 END IF;
2342 EXCEPTION
2343 WHEN OTHERS THEN
2344 PO_MESSAGE_S.add_exc_msg
2345 ( p_pkg_name => d_pkg_name,
2346 p_procedure_name => d_api_name || '.' || d_position
2347 );
2348 RAISE;
2349 END process_sourcing_rules;
2350
2351 -----------------------------------------------------------------------
2352 --Start of Comments
2353 --Name: transfer_draft_to_txn
2354 --Function:
2355 -- Call transfer API to move data from draft to transaction
2356 --Parameters:
2357 --IN:
2358 --p_doc_rec
2359 -- Some attribute values of the document
2360 --IN OUT:
2361 --OUT:
2362 --End of Comments
2363 ------------------------------------------------------------------------
2364 PROCEDURE transfer_draft_to_txn
2365 ( p_doc_rec IN doc_row_type
2366 ) IS
2367
2368 d_api_name CONSTANT VARCHAR2(30) := 'transfer_draft_to_txn';
2369 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2370 d_position NUMBER;
2371
2372 l_delete_processed_draft VARCHAR2(1);
2373 l_return_status VARCHAR2(1);
2374
2375 BEGIN
2376 d_position := 0;
2377 IF (PO_LOG.d_proc) THEN
2378 PO_LOG.proc_begin(d_module);
2379 END IF;
2380
2381 -- bug5149827
2382 -- Delete draft if buyer initiates PDOI or document is not GA
2383 IF (PO_PDOI_PARAMS.g_request.role = PO_GLOBAL.g_role_BUYER
2384 OR
2385 p_doc_rec.ga_flag <> 'Y') THEN
2386 l_delete_processed_draft := FND_API.G_TRUE;
2387 ELSE
2388 l_delete_processed_draft := FND_API.G_FALSE;
2389 END IF;
2390
2391 d_position := 10;
2392
2393 PO_DRAFTS_PVT.transfer_draft_to_txn
2394 ( p_api_version => 1.0,
2395 p_init_msg_list => FND_API.G_FALSE,
2396 p_draft_id => p_doc_rec.draft_id,
2397 p_po_header_id => p_doc_rec.po_header_id,
2398 p_delete_processed_draft => l_delete_processed_draft,
2399 p_acceptance_action => NULL,
2400 x_return_status => l_return_status
2401 );
2402
2403 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2404 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2405 END IF;
2406
2407 -- bug4907624
2408 -- Commit txn table changes once transfer is done
2409
2410 PO_PDOI_UTL.commit_work;
2411 SAVEPOINT po_pdoi_doc_postproc_sp;
2412
2413 IF (PO_LOG.d_proc) THEN
2414 PO_LOG.proc_end(d_module);
2415 END IF;
2416 EXCEPTION
2417 WHEN OTHERS THEN
2418 PO_MESSAGE_S.add_exc_msg
2419 ( p_pkg_name => d_pkg_name,
2420 p_procedure_name => d_api_name || '.' || d_position
2421 );
2422 RAISE;
2423 END transfer_draft_to_txn;
2424
2425
2426
2427 -----------------------------------------------------------------------
2428 --Start of Comments
2429 --Name: get_approval_method
2430 --Function:
2431 -- Get the method that will be used for approval
2432 --Parameters:
2433 --IN:
2434 --p_doc_rec
2435 -- Some attribute values of the document
2436 --IN OUT:
2437 --OUT:
2438 --x_approval_method
2439 -- PO_PDOI_CONSTANTS.g_appr_method_NONE: No approval
2440 -- PO_PDOI_CONSTANTS.g_appr_method_AUTO_APPROVE: Approve within PDOI
2441 -- PO_PDOI_CONSTANTS.g_appr_method_INIT_APPROVAL: Launch approval wf
2442 --End of Comments
2443 ------------------------------------------------------------------------
2444 PROCEDURE get_approval_method
2445 ( p_doc_rec IN doc_row_type,
2446 x_approval_method OUT NOCOPY VARCHAR2
2447 ) IS
2448
2449 d_api_name CONSTANT VARCHAR2(30) := 'get_approval_method';
2450 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2451 d_position NUMBER;
2452
2453 l_intended_approval_status PO_HEADERS_INTERFACE.approval_status%TYPE;
2454 BEGIN
2455 d_position := 0;
2456 IF (PO_LOG.d_proc) THEN
2457 PO_LOG.proc_begin(d_module);
2458 END IF;
2459
2460 x_approval_method := PO_PDOI_CONSTANTS.g_appr_method_NONE;
2461
2462 l_intended_approval_status :=
2463 NVL(p_doc_rec.intf_auth_status, PO_PDOI_PARAMS.g_request.approved_status);
2464
2465 --------- Blanket or Standard PO -----------
2466 IF (PO_PDOI_PARAMS.g_request.document_type IN
2467 (PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET,
2468 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD)) THEN
2469
2470 IF ( l_intended_approval_status = 'INCOMPLETE' OR
2471 p_doc_rec.orig_user_hold_flag = 'Y') THEN
2472
2473 -- Do not approve the document. The authorization status will be set
2474 -- properly by the transfer program.
2475 x_approval_method := PO_PDOI_CONSTANTS.g_appr_method_NONE;
2476
2477 ELSIF ( l_intended_approval_status = 'APPROVED') THEN
2478
2479 x_approval_method := PO_PDOI_CONSTANTS.g_appr_method_AUTO_APPROVE;
2480
2481 ELSIF ( l_intended_approval_status = 'INITIATE APPROVAL') THEN
2482
2483 x_approval_method := PO_PDOI_CONSTANTS.g_appr_method_INIT_APPROVAL;
2484 END IF;
2485
2486 --------- Quotation -----------
2487 ELSIF (PO_PDOI_PARAMS.g_request.document_type =
2488 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) THEN
2489
2490 IF ( p_doc_rec.orig_auth_status = 'A' OR
2491 l_intended_approval_status IN ('APPROVED', 'INITIATE APPROVAL')) THEN
2492
2493 x_approval_method := PO_PDOI_CONSTANTS.g_appr_method_AUTO_APPROVE;
2494 END IF;
2495
2496 END IF;
2497
2498 IF (PO_LOG.d_proc) THEN
2499 PO_LOG.proc_end(d_module);
2500 END IF;
2501 EXCEPTION
2502 WHEN OTHERS THEN
2503 PO_MESSAGE_S.add_exc_msg
2504 ( p_pkg_name => d_pkg_name,
2505 p_procedure_name => d_api_name || '.' || d_position
2506 );
2507 RAISE;
2508 END get_approval_method;
2509
2510
2511
2512 -----------------------------------------------------------------------
2513 --Start of Comments
2514 --Name: archive_po
2515 --Function:
2516 -- Archive document
2517 --Parameters:
2518 --IN:
2519 --p_doc_rec
2520 -- Some attribute values of the document
2521 --IN OUT:
2522 --OUT:
2523 --End of Comments
2524 ------------------------------------------------------------------------
2525 PROCEDURE archive_po
2526 ( p_doc_rec IN doc_row_type
2527 ) IS
2528
2529 d_api_name CONSTANT VARCHAR2(30) := 'archive_po';
2530 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2531 d_position NUMBER;
2532
2533 l_doc_type PO_DOCUMENT_TYPES.document_type_code%TYPE;
2534 l_doc_subtype PO_DOCUMENT_TYPES.document_subtype%TYPE;
2535
2536 l_return_status VARCHAR2(1);
2537 l_msg_count NUMBER;
2538 l_msg_data VARCHAR2(2000);
2539
2540 BEGIN
2541 d_position := 0;
2542 IF (PO_LOG.d_proc) THEN
2543 PO_LOG.proc_begin(d_module);
2544 END IF;
2545
2546 PO_PDOI_UTL.get_processing_doctype_info
2547 ( x_doc_type => l_doc_type,
2548 x_doc_subtype => l_doc_subtype
2549 );
2550
2551 d_position := 10;
2552
2553 PO_DOCUMENT_ARCHIVE_GRP.archive_po
2554 ( p_api_version => 1.0,
2555 p_document_id => p_doc_rec.po_header_id,
2556 p_document_type => l_doc_type,
2557 p_document_subtype => l_doc_subtype,
2558 x_return_status => l_return_status,
2559 x_msg_count => l_msg_count,
2560 x_msg_data => l_msg_data
2561 );
2562
2563 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2564 d_position := 20;
2565 IF (PO_LOG.d_stmt) THEN
2566 PO_LOG.stmt(d_module, d_position, 'Archiving failed');
2567 END IF;
2568 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2569 END IF;
2570
2571 IF (PO_LOG.d_proc) THEN
2572 PO_LOG.proc_end(d_module);
2573 END IF;
2574 EXCEPTION
2575 WHEN OTHERS THEN
2576 PO_MESSAGE_S.add_exc_msg
2577 ( p_pkg_name => d_pkg_name,
2578 p_procedure_name => d_api_name || '.' || d_position
2579 );
2580 RAISE;
2581 END archive_po;
2582
2583 -----------------------------------------------------------------------
2584 --Start of Comments
2585 --Name: update_document_status
2586 --Function:
2587 -- Update document authorization status
2588 --Parameters:
2589 --IN:
2590 --p_doc_rec
2591 -- Some attribute values of the document
2592 --p_auth_status
2593 -- New authorization status. Used by Standard PO and Blanket
2594 --p_status_lookup_code
2595 -- New status of quotation
2596 --IN OUT:
2597 --OUT:
2598 --End of Comments
2599 ------------------------------------------------------------------------
2600 PROCEDURE update_document_status
2601 ( p_doc_rec IN doc_row_type,
2602 p_auth_status IN VARCHAR2,
2603 p_status_lookup_code IN VARCHAR2
2604 ) IS
2605
2606 d_api_name CONSTANT VARCHAR2(30) := 'update_document_status';
2607 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2608 d_position NUMBER;
2609
2610 l_approved_flag PO_HEADERS_ALL.approved_flag%TYPE;
2611 l_approved_date PO_HEADERS_ALL.approved_date%TYPE;
2612
2613 BEGIN
2614
2615 d_position := 0;
2616 IF (PO_LOG.d_proc) THEN
2617 PO_LOG.proc_begin(d_module);
2618 END IF;
2619
2620 IF (PO_PDOI_PARAMS.g_request.document_type IN
2621 (PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET,
2622 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD)) THEN
2623
2624 IF (p_auth_status = 'INCOMPLETE') THEN
2625 l_approved_flag := NULL;
2626 l_approved_date := NULL;
2627 ELSIF (p_auth_status = 'REQUIRES_REAPPROVAL') THEN
2628 l_approved_flag := 'R';
2629 l_approved_date := FND_API.G_MISS_DATE;
2630 ELSIF (p_auth_status = 'PRE-APPROVED') THEN
2631 l_approved_flag := 'N';
2632 l_approved_date := FND_API.G_MISS_DATE;
2633 ELSIF (p_auth_status = 'APPROVED') THEN
2634 l_approved_flag := 'Y';
2635 l_approved_date := SYSDATE;
2636 ELSE
2637 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2638 END IF;
2639
2640 ELSIF (PO_PDOI_PARAMS.g_request.document_type =
2641 PO_PDOI_CONSTANTS.g_DOC_TYPE_QUOTATION) THEN
2642
2643 l_approved_flag := NULL;
2644 l_approved_date := NULL;
2645 END IF;
2646
2647 IF (PO_LOG.d_stmt) THEN
2648 PO_LOG.stmt(d_module, d_position, 'p_auth_status', p_auth_status);
2649 PO_LOG.stmt(d_module, d_position, 'l_approved_flag', l_approved_flag);
2650 PO_LOG.stmt(d_module, d_position, 'l_approved_date', l_approved_date);
2651 END IF;
2652
2653 d_position := 10;
2654 UPDATE po_headers_all
2655 SET authorization_status = p_auth_status,
2656 approved_flag = l_approved_flag,
2657 approved_date = DECODE(l_approved_date,
2658 FND_API.G_MISS_DATE, approved_date,
2659 l_approved_date),
2660 last_update_date = SYSDATE,
2661 last_updated_by = FND_GLOBAL.user_id,
2662 last_update_login = FND_GLOBAL.login_id
2663 WHERE po_header_id = p_doc_rec.po_header_id;
2664
2665 IF (p_auth_status = 'APPROVED') THEN
2666 d_position := 20;
2667 IF (PO_LOG.d_stmt) THEN
2668 PO_LOG.stmt(d_module, d_position, 'Update shipment approval status');
2669 END IF;
2670
2671 -- Shipments/Price Breaks should be marked as APPROVED if the document
2672 -- is imported as approved
2673 UPDATE po_line_locations_all
2674 SET approved_flag = 'Y',
2675 approved_date = l_approved_date,
2676 last_update_date = SYSDATE,
2677 last_updated_by = FND_GLOBAL.user_id,
2678 last_update_login = FND_GLOBAL.login_id
2679 WHERE po_header_id = p_doc_rec.po_header_id
2680 AND shipment_type IN ('STANDARD', 'PRICE BREAK')
2681 AND NVL(approved_flag, 'N') <> 'Y';
2682
2683 d_position := 30;
2684 IF (PO_LOG.d_stmt) THEN
2685 PO_LOG.stmt(d_module, d_position, 'Update action history');
2686 END IF;
2687 -- need to update action history when we approve
2688 PO_FORWARD_SV1.update_action_history
2689 ( x_object_id => p_doc_rec.po_header_id,
2690 x_object_type_code => PO_PDOI_PARAMS.g_request.document_type,
2691 x_old_employee_id => p_doc_rec.agent_id,
2692 x_action_code => 'APPROVE',
2693 x_note => NULL,
2694 x_user_id => FND_GLOBAL.user_id,
2695 x_login_id => FND_GLOBAL.login_id
2696 );
2697
2698 -- Approved document should not have functional lock
2699 PO_DRAFTS_PVT.unlock_document
2700 ( p_po_header_id => p_doc_rec.po_header_id
2701 );
2702 END IF;
2703
2704 IF (PO_LOG.d_proc) THEN
2705 PO_LOG.proc_end(d_module);
2706 END IF;
2707 EXCEPTION
2708 WHEN OTHERS THEN
2709 PO_MESSAGE_S.add_exc_msg
2710 ( p_pkg_name => d_pkg_name,
2711 p_procedure_name => d_api_name || '.' || d_position
2712 );
2713 RAISE;
2714 END update_document_status;
2715
2716
2717 -----------------------------------------------------------------------
2718 --Start of Comments
2719 --Name: reserve_fund
2720 --Function:
2721 -- Call Encumbrance API to reserve funds
2722 --Parameters:
2723 --IN:
2724 --p_doc_rec
2725 -- Some attribute values of the document
2726 --IN OUT:
2727 --OUT:
2728 --x_result
2729 -- FND_API.G_TRUE if successful. FND_API.G_FALSE otherwise
2730 --End of Comments
2731 ------------------------------------------------------------------------
2732 PROCEDURE reserve_fund
2733 ( p_doc_rec IN doc_row_type,
2734 x_result OUT NOCOPY VARCHAR2
2735 ) IS
2736
2737 d_api_name CONSTANT VARCHAR2(30) := 'reserve_fund';
2738 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2739 d_position NUMBER;
2740
2741 l_doc_type PO_DOCUMENT_TYPES.document_type_code%TYPE;
2742 l_doc_subtype PO_DOCUMENT_TYPES.document_subtype%TYPE;
2743
2744 l_validate_document VARCHAR2(20);
2745
2746 l_return_status VARCHAR2(1);
2747 l_po_return_code VARCHAR2(20);
2748 l_online_report_id NUMBER;
2749
2750 BEGIN
2751 d_position := 0;
2752 IF (PO_LOG.d_proc) THEN
2753 PO_LOG.proc_begin(d_module);
2754 END IF;
2755
2756 x_result := FND_API.G_TRUE; -- bug4907624
2757
2758 d_position := 10;
2759 PO_PDOI_UTL.get_processing_doctype_info
2760 ( x_doc_type => l_doc_type,
2761 x_doc_subtype => l_doc_subtype
2762 );
2763
2764 -- run submission check for update action, if it's a federal instance
2765 IF (p_doc_rec.action = PO_PDOI_CONSTANTS.g_ACTION_UPDATE AND
2766 PO_PDOI_PARAMS.g_sys.is_federal_instance = 'Y') THEN
2767 l_validate_document := PO_DOCUMENT_FUNDS_PVT.g_parameter_YES;
2768 ELSE
2769 l_validate_document := PO_DOCUMENT_FUNDS_PVT.g_parameter_NO;
2770 END IF;
2771
2772 d_position := 20;
2773 PO_DOCUMENT_FUNDS_PVT.do_reserve
2774 ( x_return_status => l_return_status,
2775 p_doc_type => l_doc_type,
2776 p_doc_subtype => l_doc_subtype,
2777 p_doc_level => PO_DOCUMENT_FUNDS_PVT.g_doc_level_HEADER,
2778 p_doc_level_id => p_doc_rec.po_header_id,
2779 p_use_enc_gt_flag => PO_DOCUMENT_FUNDS_PVT.g_parameter_NO,
2780 p_prevent_partial_flag => PO_DOCUMENT_FUNDS_PVT.g_parameter_YES,
2781 p_employee_id => p_doc_rec.agent_id,
2782 p_override_funds => PO_PDOI_PARAMS.g_profile.override_funds,
2783 p_validate_document => l_validate_document,
2784 x_po_return_code => l_po_return_code,
2785 x_online_report_id => l_online_report_id
2786 );
2787
2788 IF (l_return_status IN (FND_API.G_RET_STS_ERROR,
2789 FND_API.G_RET_STS_UNEXP_ERROR)) THEN
2790 d_position := 30;
2791 IF (PO_LOG.d_stmt) THEN
2792 PO_LOG.stmt(d_module, d_position, 'Encumbrance returned system error');
2793 END IF;
2794
2795 x_result := FND_API.G_FALSE; -- bug4907624
2796 ELSE
2797 IF (l_po_return_code IN (PO_DOCUMENT_FUNDS_PVT.g_return_SUCCESS,
2798 PO_DOCUMENT_FUNDS_PVT.g_return_WARNING)) THEN
2799
2800 d_position := 40;
2801 IF (PO_LOG.d_stmt) THEN
2802 PO_LOG.stmt(d_module, d_position, 'l_po_return_code', l_po_return_code);
2803 END IF;
2804
2805 ELSE
2806 d_position := 50;
2807 IF (PO_LOG.d_stmt) THEN
2808 PO_LOG.stmt(d_module, d_position, 'Fund Reservation failed');
2809 END IF;
2810 x_result := FND_API.G_FALSE; -- bug4907624
2811 END IF;
2812
2813 END IF;
2814
2815 IF (PO_LOG.d_proc) THEN
2816 PO_LOG.proc_end(d_module);
2817 END IF;
2818 EXCEPTION
2819 WHEN OTHERS THEN
2820 PO_MESSAGE_S.add_exc_msg
2821 ( p_pkg_name => d_pkg_name,
2822 p_procedure_name => d_api_name || '.' || d_position
2823 );
2824 RAISE;
2825 END reserve_fund;
2826
2827
2828 -----------------------------------------------------------------------
2829 --Start of Comments
2830 --Name: need_to_encumber
2831 --Function:
2832 -- Check whether encumbrance is required.
2833 --Parameters:
2834 --IN:
2835 --p_doc_rec
2836 -- Some attribute values of the document
2837 --p_new_auth_status
2838 -- New authorization status
2839 --IN OUT:
2840 --OUT:
2841 --Returns:
2842 --End of Comments
2843 ------------------------------------------------------------------------
2844 FUNCTION need_to_encumber
2845 ( p_doc_rec IN doc_row_type,
2846 p_new_auth_status IN VARCHAR2
2847 ) RETURN VARCHAR2 IS
2848
2849 d_api_name CONSTANT VARCHAR2(30) := 'need_to_encumber';
2850 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2851 d_position NUMBER;
2852
2853 l_check_encumbrance_setting VARCHAR2(1) := FND_API.G_FALSE;
2854 l_need_to_encumber VARCHAR2(1);
2855
2856 BEGIN
2857 d_position := 0;
2858 IF (PO_LOG.d_proc) THEN
2859 PO_LOG.proc_begin(d_module);
2860 END IF;
2861
2862 -- Encumbrance is required when:
2863 -- For standard PO: If document is approved and po encumbrance is turned on
2864 -- For blanket PO: If document is approved for creation case, document is
2865 -- marked as encumbrance required, and both po and req encumbrance is
2866 -- turned on
2867 IF (PO_PDOI_PARAMS.g_request.document_type =
2868 PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD ) THEN
2869
2870 IF (PO_PDOI_PARAMS.g_sys.po_encumbrance_flag = 'Y' AND
2871 p_new_auth_status = 'APPROVED') THEN
2872
2873 l_need_to_encumber := FND_API.G_TRUE;
2874 ELSE
2875 l_need_to_encumber := FND_API.G_FALSE;
2876 END IF;
2877
2878 ELSIF (PO_PDOI_PARAMS.g_request.document_type =
2879 PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET ) THEN
2880
2881 IF (PO_PDOI_PARAMS.g_sys.po_encumbrance_flag = 'Y' AND
2882 PO_PDOI_PARAMS.g_sys.req_encumbrance_flag = 'Y' AND
2883 p_doc_rec.action <> PO_PDOI_CONSTANTS.g_ACTION_UPDATE AND
2884 p_doc_rec.encumbrance_required_flag = 'Y' AND
2885 p_new_auth_status = 'APPROVED') THEN
2886
2887 l_need_to_encumber := FND_API.G_TRUE;
2888 ELSE
2889 l_need_to_encumber := FND_API.G_FALSE;
2890 END IF;
2891 ELSE
2892 -- for other documents, we do not need to encumber
2893 l_need_to_encumber := FND_API.G_FALSE;
2894 END IF;
2895
2896 IF (PO_LOG.d_stmt) THEN
2897 PO_LOG.stmt(d_module, d_position, 'l_need_to_encumber', l_need_to_encumber);
2898 END IF;
2899
2900 IF (PO_LOG.d_proc) THEN
2901 PO_LOG.proc_end(d_module);
2902 END IF;
2903
2904 RETURN l_need_to_encumber;
2905 EXCEPTION
2906 WHEN OTHERS THEN
2907 PO_MESSAGE_S.add_exc_msg
2908 ( p_pkg_name => d_pkg_name,
2909 p_procedure_name => d_api_name || '.' || d_position
2910 );
2911 RAISE;
2912 END need_to_encumber;
2913
2914 -----------------------------------------------------------------------
2915 --Start of Comments
2916 --Name: start_po_approval_workflow
2917 --Function:
2918 -- Launch approval workflow
2919 --Parameters:
2920 --IN:
2921 --p_doc_rec
2922 -- Some attribute values of the document
2923 --IN OUT:
2924 --OUT:
2925 --End of Comments
2926 ------------------------------------------------------------------------
2927 PROCEDURE start_po_approval_workflow
2928 ( p_doc_rec IN doc_row_type
2929 ) IS
2930
2931 d_api_name CONSTANT VARCHAR2(30) := 'start_po_approval_workflow';
2932 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
2933 d_position NUMBER;
2934
2935 l_doc_type VARCHAR2(30);
2936 l_doc_subtype VARCHAR2(30);
2937
2938 l_agent_id PO_HEADERS_ALL.agent_id%TYPE;
2939 l_default_method VARCHAR2(30);
2940 l_email_address PO_VENDOR_SITES_ALL.email_address%TYPE;
2941 l_fax_number VARCHAR2(100);
2942 l_document_num PO_HEADERS_ALL.segment1%TYPE;
2943
2944 l_email_flag VARCHAR2(1) := 'N';
2945 l_fax_flag VARCHAR2(1) := 'N';
2946 l_print_flag VARCHAR2(1) := 'N';
2947 BEGIN
2948 d_position := 0;
2949 IF (PO_LOG.d_proc) THEN
2950 PO_LOG.proc_begin(d_module);
2951 END IF;
2952
2953 PO_PDOI_UTL.get_processing_doctype_info
2954 ( x_doc_type => l_doc_type,
2955 x_doc_subtype => l_doc_subtype
2956 );
2957
2958 l_agent_id := p_doc_rec.agent_id;
2959
2960 d_position := 10;
2961 PO_VENDOR_SITES_SV.get_transmission_defaults
2962 ( p_document_id => p_doc_rec.po_header_id,
2963 p_document_type => l_doc_type,
2964 p_document_subtype => l_doc_subtype,
2965 p_preparer_id => l_agent_id,
2966 x_default_method => l_default_method,
2967 x_email_address => l_email_address,
2968 x_fax_number => l_fax_number,
2969 x_document_num => l_document_num
2970 );
2971
2972 IF (l_default_method = 'EMAIL' AND l_email_address IS NOT NULL) THEN
2973 l_email_flag := 'Y';
2974 l_fax_number := NULL;
2975 ELSIF (l_default_method = 'FAX' AND l_fax_number IS NOT NULL) THEN
2976 l_fax_flag := 'Y';
2977 l_email_address := NULL;
2978 ELSE
2979 l_email_flag := NULL;
2980 l_fax_number := NULL;
2981
2982 IF (l_default_method = 'PRINT') THEN
2983 l_print_flag := 'Y';
2984 END IF;
2985 END IF;
2986
2987 d_position := 20;
2988
2989 -- Call Approval Workflow
2990 -- ItemTYpe, ItemKey and WorkflowProcess will be filled in by
2991 -- the approval workflow
2992 PO_REQAPPROVAL_INIT1.start_wf_process
2993 ( ItemType => NULL,
2994 ItemKey => NULL,
2995 WorkflowProcess => NULL,
2996 ActionOriginatedFrom => 'PDOI',
2997 DocumentId => p_doc_rec.po_header_id,
2998 DocumentNumber => NULL, -- Obsolete parameter
2999 PreparerId => p_doc_rec.agent_id,
3000 DocumentTypeCode => l_doc_type,
3001 DocumentSubtype => l_doc_subtype,
3002 SubmitterAction => 'APPROVE',
3003 ForwardToId => NULL,
3004 ForwardFromId => NULL,
3005 DefaultApprovalPathId => NULL,
3006 Note => NULL,
3007 PrintFlag => l_print_flag,
3008 FaxFlag => l_fax_flag,
3009 FaxNumber => l_fax_number,
3010 EmailFlag => l_email_flag,
3011 EmailAddress => l_email_address,
3012 CreateSourcingRule => PO_PDOI_PARAMS.g_request.create_sourcing_rules_flag,
3013 ReleaseGenMethod => PO_PDOI_PARAMS.g_request.rel_gen_method,
3014 UpdateSourcingRule => PO_PDOI_PARAMS.g_request.create_sourcing_rules_flag
3015 );
3016
3017 IF (PO_LOG.d_proc) THEN
3018 PO_LOG.proc_end(d_module);
3019 END IF;
3020 EXCEPTION
3021 WHEN OTHERS THEN
3022 PO_MESSAGE_S.add_exc_msg
3023 ( p_pkg_name => d_pkg_name,
3024 p_procedure_name => d_api_name || '.' || d_position
3025 );
3026 RAISE;
3027 END start_po_approval_workflow;
3028
3029 -----------------------------------------------------------------------
3030 --Start of Comments
3031 --Name: create_po_supply
3032 --Function:
3033 -- Create po supply in MTL_SUPPLY
3034 --Parameters:
3035 --IN:
3036 --p_doc_rec
3037 -- Some attribute values of the document
3038 --p_action
3039 -- Action to perform
3040 --IN OUT:
3041 --OUT:
3042 --End of Comments
3043 ------------------------------------------------------------------------
3044 PROCEDURE create_po_supply
3045 ( p_doc_rec IN doc_row_type,
3046 p_action IN VARCHAR2
3047 ) IS
3048
3049 d_api_name CONSTANT VARCHAR2(30) := 'create_po_supply';
3050 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3051 d_position NUMBER;
3052
3053
3054 l_return_status BOOLEAN;
3055 BEGIN
3056 d_position := 0;
3057 IF (PO_LOG.d_proc) THEN
3058 PO_LOG.proc_begin(d_module);
3059 END IF;
3060
3061 l_return_status := PO_SUPPLY.po_req_supply
3062 ( p_docid => p_doc_rec.po_header_id,
3063 p_lineid => NULL,
3064 p_shipid => NULL,
3065 p_action => p_action,
3066 p_recreate_flag => NULL,
3067 p_qty => NULL,
3068 p_receipt_date => NULL
3069 );
3070
3071 IF (NOT l_return_status) THEN
3072 d_position := 10;
3073 IF (PO_LOG.d_stmt) THEN
3074 PO_LOG.stmt(d_module, d_position, 'Supply creation failed');
3075 END IF;
3076
3077 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3078 END IF;
3079
3080 IF (PO_LOG.d_proc) THEN
3081 PO_LOG.proc_end(d_module);
3082 END IF;
3083 EXCEPTION
3084 WHEN OTHERS THEN
3085 PO_MESSAGE_S.add_exc_msg
3086 ( p_pkg_name => d_pkg_name,
3087 p_procedure_name => d_api_name || '.' || d_position
3088 );
3089 RAISE;
3090 END create_po_supply;
3091
3092
3093 -----------------------------------------------------------------------
3094 --Start of Comments
3095 --Name: create_delivery_record
3096 --Function:
3097 -- Create delivery records in transportation module
3098 --Parameters:
3099 --IN:
3100 --p_doc_rec
3101 -- Some attribute values of the document
3102 --IN OUT:
3103 --OUT:
3104 --End of Comments
3105 ------------------------------------------------------------------------
3106 PROCEDURE create_delivery_record
3107 ( p_doc_rec IN doc_row_type
3108 ) IS
3109
3110 d_api_name CONSTANT VARCHAR2(30) := 'create_delivery_record';
3111 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3112 d_position NUMBER;
3113
3114 l_return_status VARCHAR2(1);
3115 l_msg_count NUMBER;
3116 l_msg_data VARCHAR2(2000);
3117
3118 BEGIN
3119 d_position := 0;
3120
3121 IF (PO_LOG.d_proc) THEN
3122 PO_LOG.proc_begin(d_module);
3123 END IF;
3124
3125 PO_DELREC_PVT.create_update_delrec
3126 ( p_api_version => 1.0,
3127 x_return_status => l_return_status,
3128 x_msg_count => l_msg_count,
3129 x_msg_data => l_msg_data,
3130 p_action => 'APPROVE',
3131 p_doc_type => 'PO',
3132 p_doc_subtype => 'STANDARD',
3133 p_doc_id => p_doc_rec.po_header_id,
3134 p_line_id => NULL,
3135 p_line_location_id => NULL
3136 );
3137
3138 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3139 d_position := 10;
3140 IF (PO_LOG.d_stmt) THEN
3141 PO_LOG.stmt(d_module, d_position, 'create delivery record failed');
3142 END IF;
3143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3144 END IF;
3145
3146 IF (PO_LOG.d_proc) THEN
3147 PO_LOG.proc_end(d_module);
3148 END IF;
3149 EXCEPTION
3150 WHEN OTHERS THEN
3151 PO_MESSAGE_S.add_exc_msg
3152 ( p_pkg_name => d_pkg_name,
3153 p_procedure_name => d_api_name || '.' || d_position
3154 );
3155 RAISE;
3156 END create_delivery_record;
3157
3158 -----------------------------------------------------------------------
3159 --Start of Comments
3160 --Name: close_po
3161 --Function:
3162 -- Attempt to close the document
3163 --Parameters:
3164 --IN:
3165 --p_doc_rec
3166 -- Some attribute values of the document
3167 --IN OUT:
3168 --OUT:
3169 --End of Comments
3170 ------------------------------------------------------------------------
3171 PROCEDURE close_po
3172 ( p_doc_rec IN doc_row_type
3173 ) IS
3174
3175 d_api_name CONSTANT VARCHAR2(30) := 'close_po';
3176 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3177 d_position NUMBER;
3178
3179 l_result BOOLEAN;
3180 l_return_code VARCHAR2(100);
3181
3182 BEGIN
3183 d_position := 0;
3184 IF (PO_LOG.d_proc) THEN
3185 PO_LOG.proc_begin(d_module);
3186 END IF;
3187
3188 l_result := PO_ACTIONS.close_po
3189 ( p_docid => p_doc_rec.po_header_id,
3190 p_doctyp => 'PO',
3191 p_docsubtyp => 'STANDARD',
3192 p_lineid => NULL,
3193 p_shipid => NULL,
3194 p_action => 'CLOSE',
3195 p_reason => '',
3196 p_calling_mode => 'PO',
3197 p_conc_flag => 'N',
3198 p_return_code => l_return_code,
3199 p_auto_close => 'Y'
3200 );
3201
3202 IF (NOT l_result) THEN
3203 d_position := 10;
3204 PO_PDOI_ERR_UTL.add_fatal_error
3205 ( p_interface_header_id => p_doc_rec.interface_header_id,
3206 p_error_message_name => 'PO_PDOI_UNABLE_CLOSE_PO',
3207 p_table_name => 'PO_HEADERS_INTERFACE',
3208 p_column_name => 'CLOSED_CODE',
3209 p_column_value => ''
3210 );
3211
3212 IF (PO_LOG.d_stmt) THEN
3213 PO_LOG.stmt(d_module, d_position, 'PO Closing failed');
3214 END IF;
3215
3216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3217 END IF;
3218
3219 IF (PO_LOG.d_proc) THEN
3220 PO_LOG.proc_end(d_module);
3221 END IF;
3222 EXCEPTION
3223 WHEN OTHERS THEN
3224 PO_MESSAGE_S.add_exc_msg
3225 ( p_pkg_name => d_pkg_name,
3226 p_procedure_name => d_api_name || '.' || d_position
3227 );
3228 RAISE;
3229 END close_po;
3230
3231
3232 -----------------------------------------------------------------------
3233 --Start of Comments
3234 --Name: rebuild_catalog_index
3235 --Function:
3236 -- Call a PO API that rebuilds the catalog index for document currently
3237 -- being processed
3238 --Parameters:
3239 --IN:
3240 --p_type
3241 -- Type of the document to rebuild index on
3242 --p_doc_rec
3243 -- Some attribute values of the document
3244 --IN OUT:
3245 --OUT:
3246 --End of Comments
3247 ------------------------------------------------------------------------
3248 PROCEDURE rebuild_catalog_index
3249 ( p_type IN VARCHAR2,
3250 p_doc_rec IN doc_row_type
3251 ) IS
3252
3253 d_api_name CONSTANT VARCHAR2(30) := 'rebuild_catalog_index';
3254 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3255 d_position NUMBER;
3256
3257 BEGIN
3258 d_position := 0;
3259 IF (PO_LOG.d_proc) THEN
3260 PO_LOG.proc_begin(d_module);
3261 END IF;
3262
3263 -- bug5027915
3264 -- populate parameter p_po_header_id rather than p_po_header_ids,
3265 -- which is used only when type is 'BLANKET_BULK'
3266 PO_CATALOG_INDEX_PVT.rebuild_index
3267 ( p_type => p_type,
3268 p_po_header_id => p_doc_rec.po_header_id
3269 );
3270
3271 IF (PO_LOG.d_proc) THEN
3272 PO_LOG.proc_end(d_module);
3273 END IF;
3274
3275 EXCEPTION
3276 WHEN OTHERS THEN
3277 PO_MESSAGE_S.add_exc_msg
3278 ( p_pkg_name => d_pkg_name,
3279 p_procedure_name => d_api_name || '.' || d_position
3280 );
3281 RAISE;
3282 END rebuild_catalog_index;
3283
3284
3285 -----------------------------------------------------------------------
3286 --Start of Comments
3287 --Name: calculate_tax
3288 --Function:
3289 -- Calculate tax by integrating with eTax module.
3290 --Parameters:
3291 --IN:
3292 --p_doc_rec
3293 -- Some attribute values of the document
3294 --IN OUT:
3295 --OUT:
3296 --x_return_status
3297 -- status of the eTax API call
3298 --End of Comments
3299 ------------------------------------------------------------------------
3300 PROCEDURE calculate_tax
3301 ( p_doc_rec IN doc_row_type,
3302 x_return_status OUT NOCOPY VARCHAR2
3303 ) IS
3304
3305 d_api_name CONSTANT VARCHAR2(30) := 'calculate_tax';
3306 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3307 d_position NUMBER;
3308
3309 BEGIN
3310 d_position := 0;
3311 IF (PO_LOG.d_proc) THEN
3312 PO_LOG.proc_begin(d_module);
3313 END IF;
3314
3315 PO_TAX_INTERFACE_PVT.calculate_tax
3316 ( p_po_header_id => p_doc_rec.po_header_id,
3317 p_po_release_id => NULL,
3318 p_calling_program => 'PDOI',
3319 x_return_status => x_return_status
3320 );
3321
3322 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3323 -- handle tax calculation error
3324 -- 1. insert warning to error interface table
3325 PO_PDOI_ERR_UTL.add_warning
3326 (
3327 p_interface_header_id => p_doc_rec.interface_header_id,
3328 p_error_message_name => 'PO_PDOI_TAX_CALCULATION_ERR',
3329 p_table_name => 'PO_HEADERS_INTERFACE',
3330 p_column_name => NULL
3331 );
3332 END IF;
3333
3334 IF (PO_LOG.d_proc) THEN
3335 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
3336 END IF;
3337
3338
3339 EXCEPTION
3340 WHEN OTHERS THEN
3341 PO_MESSAGE_S.add_exc_msg
3342 ( p_pkg_name => d_pkg_name,
3343 p_procedure_name => d_api_name || '.' || d_position
3344 );
3345 RAISE;
3346 END calculate_tax;
3347
3348
3349 -----------------------------------------------------------------------
3350 --Start of Comments
3351 --Name: submission_check
3352 --Function:
3353 -- Doing submission check for the document's created before approval.
3354 --Parameters:
3355 --IN:
3356 --p_doc_rec
3357 -- Some attribute values of the document
3358 --IN OUT:
3359 --OUT:
3360 --x_return_status
3361 -- status of the submission check call
3362 --End of Comments
3363 ------------------------------------------------------------------------
3364 PROCEDURE submission_check
3365 ( p_doc_rec IN doc_row_type,
3366 x_return_status OUT NOCOPY VARCHAR2
3367 ) IS
3368
3369 d_api_name CONSTANT VARCHAR2(30) := 'submission_check';
3370 d_module CONSTANT VARCHAR2(255) := d_pkg_name || d_api_name || '.';
3371 d_position NUMBER;
3372
3373 l_check_asl BOOLEAN := TRUE;
3374 x_sub_check_status VARCHAR2(1);
3375 x_msg_data VARCHAR2(2000);
3376 x_online_report_id NUMBER;
3377 x_count NUMBER;
3378 l_document_type VARCHAR2(30);
3379
3380 BEGIN
3381 d_position := 0;
3382 IF (PO_LOG.d_proc) THEN
3383 PO_LOG.proc_begin(d_module);
3384 END IF;
3385
3386 IF p_doc_rec.document_type = 'STANDARD' THEN
3387 l_document_type := 'PO';
3388 ELSIF p_doc_rec.document_type = 'BLANKET' THEN
3389 l_document_type := 'PA';
3390 END IF;
3391
3392 IF p_doc_rec.document_type in ('BLANKET', 'STANDARD') THEN
3393 l_check_asl := TRUE;
3394 else
3395 l_check_asl := FALSE;
3396 END IF;
3397 IF p_doc_rec.document_type in ('BLANKET', 'STANDARD') THEN
3398 PO_DOCUMENT_CHECKS_GRP.po_submission_check(
3399 p_api_version => 1.0
3400 , p_action_requested => 'DOC_SUBMISSION_CHECK'
3401 , p_document_type => l_document_type
3402 , p_document_subtype => p_doc_rec.document_type
3403 , p_document_id => p_doc_rec.po_header_id
3404 , p_check_asl => l_check_asl
3405 , x_return_status => x_return_status
3406 , x_sub_check_status => x_sub_check_status
3407 , x_msg_data => x_msg_data
3408 , x_online_report_id => x_online_report_id
3409 );
3410 else
3411 x_return_status := FND_API.G_RET_STS_SUCCESS;
3412 end if;
3413
3414 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS OR x_online_report_id IS NOT null) THEN
3415 -- write the error into log
3416 PO_LOG.stmt(d_module, d_position,'Submission Check Failed');
3417 x_count := 0;
3418
3419
3420 FOR i IN (SELECT MESSAGE_NAME, TEXT_LINE FROM po_online_report_text_gt WHERE online_report_id = x_online_report_id)
3421 LOOP
3422 x_count := x_count + 1;
3423 IF (PO_LOG.d_stmt) THEN
3424 PO_LOG.stmt(d_module, d_position, 'Error in Submission Check #'||x_count||' --'||i.TEXT_LINE);
3425 END IF;
3426 END LOOP;
3427
3428 IF ( p_doc_rec.action = PO_PDOI_CONSTANTS.g_ACTION_ORIGINAL) THEN
3429 update_document_status
3430 ( p_doc_rec => p_doc_rec,
3431 p_auth_status => 'INCOMPLETE',
3432 p_status_lookup_code => NULL
3433 );
3434 ELSE
3435 update_document_status
3436 ( p_doc_rec => p_doc_rec,
3437 p_auth_status => 'REQUIRES_REAPPROVAL',
3438 p_status_lookup_code => NULL
3439 );
3440 END IF;
3441 if x_count > 0 then
3442 x_return_status := FND_API.G_RET_STS_ERROR;
3443 end if;
3444 END IF;
3445
3446 IF (PO_LOG.d_proc) THEN
3447 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
3448 END IF;
3449
3450
3451 EXCEPTION
3452 WHEN OTHERS THEN
3453 PO_MESSAGE_S.add_exc_msg
3454 ( p_pkg_name => d_pkg_name,
3455 p_procedure_name => d_api_name || '.' || d_position
3456 );
3457 RAISE;
3458 END submission_check;
3459
3460
3461 END PO_PDOI_POSTPROC_PVT;