1 PACKAGE BODY PO_HEADERS_SV1 as
2 /* $Header: POXPOH1B.pls 120.9.12020000.2 2013/02/11 01:13:13 vegajula ship $*/
3
4 /*Added Log Messages as part of bug 12405805 */
5 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
6 g_log_head CONSTANT VARCHAR2(50) :='po.plsql.PO_HEADERS_SV1';
7
8
9 /*===========================================================================
10
11 PROCEDURE NAME: lock_row_for_status_update
12
13 ===========================================================================*/
14
15 PROCEDURE lock_row_for_status_update (x_po_header_id IN NUMBER)
16 IS
17 CURSOR C IS
18 SELECT *
19 FROM po_headers
20 WHERE po_header_id = x_po_header_id
21 FOR UPDATE of po_header_id NOWAIT;
22 Recinfo C%ROWTYPE;
23
24 x_progress VARCHAR2(3) := '';
25
26 BEGIN
27 x_progress := '010';
28 OPEN C;
29 FETCH C INTO Recinfo;
30 IF (C%NOTFOUND) then
31 CLOSE C;
32 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
33 APP_EXCEPTION.Raise_Exception;
34 END IF;
35 CLOSE C;
36
37 EXCEPTION
38 WHEN app_exception.record_lock_exception THEN
39 po_message_s.app_error ('PO_ALL_CANNOT_RESERVE_RECORD');
40
41 WHEN OTHERS THEN
42 --dbms_output.put_line('In Exception');
43 PO_MESSAGE_S.SQL_ERROR('LOCK_ROW_FOR_STATUS_UPDATE', x_progress, sqlcode);
44 RAISE;
45 END;
46
47 /*===========================================================================
48
49 FUNCTION NAME: val_po_encumbered()
50
51 ===========================================================================*/
52
53 /* PROCEDURE val_po_encumbered() IS
54
55 x_progress VARCHAR2(3) := NULL;
56
57 BEGIN
58
59
60 EXCEPTION
61 WHEN OTHERS THEN
62 po_message.set_name('val_po_encumbered', x_progress, sqlcode);
63 RAISE;
64
65 END val_po_encumbered; */
66
67 /*===========================================================================
68
69 FUNCTION NAME: get_doc_num()
70
71 ===========================================================================*/
72
73 /*PROCEDURE get_doc_num() IS
74
75 x_progress VARCHAR2(3) := NULL;
76
77 BEGIN
78
79
80 EXCEPTION
81 WHEN OTHERS THEN
82 po_message_s.sql_error('get_doc_num', x_progress, sqlcode);
83 RAISE;
84
85 END get_doc_num; */
86
87 /*===========================================================================
88
89 PROCEDURE NAME: val_delete()
90
91 ===========================================================================*/
92
93 FUNCTION val_delete(X_po_header_id IN NUMBER , X_type_lookup_code IN VARCHAR2)
94 return boolean is
95 X_allow_delete boolean;
96
97 X_progress VARCHAR2(3) := NULL;
98 X_encumbered boolean;
99
100 BEGIN
101
102 /* If it is a PO or an Agreement check if it is encumbered */
103
104 if ((X_type_lookup_code = 'STANDARD') or
105 (X_type_lookup_code = 'PLANNED') or
106 (X_type_lookup_code = 'BLANKET') or
107 (X_type_lookup_code = 'CONTRACT') ) then
108
109 X_progress := '005';
110
111 X_encumbered := po_headers_sv1.get_po_encumbered(X_po_header_id);
112
113 /* If the PO is encumbered, it has to be cancelled */
114
115 if X_encumbered then
116 X_allow_delete := FALSE;
117 po_message_s.app_error('PO_PO_USE_CANCEL_ON_ENCUMB_PO');
118 else
119 X_allow_delete := TRUE;
120 end if;
121
122 elsif (X_type_lookup_code = 'RFQ') then
123
124 X_progress := '010';
125 po_rfqs_sv.val_header_delete (X_po_header_id,
126 X_allow_delete);
127
128 elsif (X_type_lookup_code = 'QUOTATION') then
129
130 X_progress := '015';
131 po_quotes_sv.val_header_delete (X_po_header_id,
132 X_allow_delete);
133 end if;
134
135 return(X_allow_delete);
136
137
138 EXCEPTION
139 when others then
140 X_allow_delete := FALSE;
141 po_message_s.sql_error('val_delete', x_progress, sqlcode);
142 raise;
143
144 END val_delete;
145
146 /*===========================================================================
147
148 PROCEDURE NAME: get_po_encumbered()
149
150 ===========================================================================*/
151
152 FUNCTION get_po_encumbered(X_po_header_id IN number)
153 return boolean is
154
155 X_encumbered boolean := FALSE;
156
157 X_progress VARCHAR2(3) := '';
158
159 cursor c1 is SELECT 'Y'
160 FROM po_distributions
161 WHERE po_header_id = X_po_header_id
162 AND nvl(encumbered_flag,'N') <> 'N';
163
164 --BUG 3230237
165 --PO_HEADERS_SV1.delete_po calls this procedure to determine
166 --if a PO can be deleted. We need to prevent encumbered
167 --BPAs from getting deleted.
168 --AND distribution_type <> 'AGREEMENT'; --<Encumbrance FPJ>
169
170 Recinfo c1%rowtype;
171
172 BEGIN
173 X_progress := '010';
174 open c1;
175 X_progress := '020';
176
177 /* Check if any distributions for a given po_header_id is encumbered
178 ** If there are encumbered distributions, return TRUE else
179 ** return FALSE */
180
181 fetch c1 into recinfo;
182
183 X_progress := '030';
184
185 if (c1%notfound) then
186 close c1;
187 X_encumbered := FALSE;
188 return(X_encumbered);
189 end if;
190
191 X_encumbered := TRUE;
192 return(X_encumbered);
193
194
195 exception
196 when others then
197 po_message_s.sql_error('get_po_encumbered', X_progress, sqlcode);
198 raise;
199
200 END get_po_encumbered;
201
202 /*===========================================================================
203
204 PROCEDURE NAME: delete_children()
205
206 ===========================================================================*/
207
208 PROCEDURE delete_children(X_po_header_id IN NUMBER,
209 X_type_lookup_code IN VARCHAR2) IS
210
211 X_progress VARCHAR2(3) := NULL;
212 X_deleted boolean;
213
214 l_deleted_line_list PO_TBL_NUMBER;
215 l_deleted_dist_list PO_TBL_NUMBER;
216 d_pos NUMBER := 10;
217 l_api_name CONSTANT VARCHAR2(30) := 'delete_children';
218 d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_HEADERS_SV1.delete_children';
219 BEGIN
220 IF (PO_LOG.d_proc) THEN
221 PO_LOG.proc_begin(d_module); PO_LOG.proc_begin(d_module,'X_type_lookup_code', X_type_lookup_code);
222 PO_LOG.proc_begin(d_module,'X_po_header_id', X_po_header_id);
223 END IF;
224
225 -- Bug: 13948625 - Start
226 -- Re-written the logic to send the requisitions back to pool when CLM document or its lines/distributions deleted.
227 -- apply_changes proc of PO_XX_DRAFT_PVT above has a call to update_req_for_linked_po_count, where
228 -- the linked_po_count is updated for unlinked requisitions and those reqs CLINs(Autocreated/SoftLinked)
229 -- and PSlins(Softlinked) inserted into GT table for further procession(updating the reqs_in_pool_flag for entire
230 -- Clin-Slin structure if eligible) in the below proc update_reqs_in_pool_flag.
231 -- This should be called only for CLM Standard document.
232 IF (PO_CLM_INTG_GRP.IS_CLM_DOCUMENT(p_doc_type => 'PO',p_document_id => X_po_header_id) = 'Y'
233 AND X_type_lookup_code = 'STANDARD') THEN
234
235 l_deleted_line_list := PO_TBL_NUMBER();
236 l_deleted_dist_list := PO_TBL_NUMBER();
237
238 -- Collect all deleted lines
239 SELECT po_line_id
240 BULK COLLECT INTO l_deleted_line_list
241 FROM po_lines_all
242 WHERE po_header_id = X_po_header_id;
243
244 -- Collect all deleted distributions
245 SELECT po_distribution_id
246 BULK COLLECT INTO l_deleted_dist_list
247 FROM po_distributions_all
248 WHERE po_header_id = X_po_header_id;
249
250
251 IF (PO_LOG.d_stmt) THEN
252 PO_LOG.stmt(d_module,d_pos,'l_deleted_line_list',l_deleted_line_list);
253 PO_LOG.stmt(d_module,d_pos,'l_deleted_dist_list',l_deleted_dist_list);
254 END IF;
255
256 -- Call update_req_for_linked_po_count, where the linked_po_count is updated for unlinked requisitions
257 -- and those reqs CLINs(Autocreated/SoftLinked) and PSlins(Softlinked) inserted into GT table for
258 -- further processing
259 PO_REQ_LINES_SV.update_req_for_linked_po_count (l_deleted_line_list, 'LINE');
260 PO_REQ_LINES_SV.update_req_for_linked_po_count (l_deleted_dist_list, 'DISTRIBUTION');
261
262 -- Send the requisitions back to pool, if eleigible,
263 PO_REQ_LINES_SV.update_reqs_in_pool_flag;
264
265 END IF;
266 -- Bug: 13948625 - End
267
268 --BUG 3230237
269 --Added 'BLANKET' to the if-condition since Encumbrance BPAs can have distributions.
270 if (X_type_lookup_code IN ('STANDARD','PLANNED','BLANKET')) then --BUG 3230237
271 /* Delete Distributions for a PO */
272 X_progress := '020';
273 --dbms_output.put_line('Before Delete All Distributions');
274 po_distributions_sv.delete_distributions(X_po_header_id, 'HEADER');
275 end if;
276
277 if (X_type_lookup_code <> 'CONTRACT') then
278
279 /* Delete Shipments for a PO */
280 X_progress := '015';
281 --dbms_output.put_line('Before Delete All Shipments ');
282 po_shipments_sv4.delete_all_shipments (X_po_header_id, 'HEADER',
283 X_type_lookup_code);
284
285 /* Delete Lines for a PO */
286 X_progress := '010';
287 --dbms_output.put_line('Before Delete All lines ');
288 po_lines_sv.delete_all_lines (X_po_header_id,
289 X_type_lookup_code); --<HTML Agreements R12>
290
291 /* Delete Vendors for a PO */
292 if (X_type_lookup_code in ('RFQ','QUOTATION')) then
293 X_progress := '035';
294 --dbms_output.put_line('Before Delete All vendors ');
295 po_rfq_vendors_pkg_s2.delete_all_vendors (X_po_header_id);
296 end if;
297
298 end if;
299
300 /* Delete Notification Controls if it is PLANNED/BLANKET/CONTRACT PO */
301
302 if ((X_type_lookup_code = 'PLANNED') or
303 (X_type_lookup_code = 'BLANKET') or
304 (X_type_lookup_code = 'CONTRACT')) then
305
306 /* Call routine to delete po notification controls */
307 X_progress := '025';
308 --dbms_output.put_line('Before Delete Notification Controls');
309 X_deleted := po_notif_controls_sv.delete_notifs (X_po_header_id);
310
311 end if;
312
313 -- DEBUG it seems that this part of code is not needed since we don't allow to
314 -- delete approved blanket/planned PO. If it is unapproved, there should
315 -- have no releases against it.
316 -- Remove it after reviewing this with KIM.
317 --
318 -- /* Delete All Releases for this BLANKET/PLANNED that is being deleted.*/
319 --
320 -- if ((X_type_lookup_code = 'PLANNED') or
321 -- (X_type_lookup_code = 'BLANKET')) then
322 --
323 -- X_progress := '030';
324 -- --dbms_output.put_line('Before Delete all releases');
325 -- po_headers_sv1.delete_this_release (X_po_header_id);
326 --
327 -- end if;
328 --
329
330
331 EXCEPTION
332
333 when others then
334 po_message_s.sql_error('delete_children', x_progress, sqlcode);
335 raise;
336
337 END delete_children;
338
339 /*===========================================================================
340
341 PROCEDURE NAME: delete_po()
342
343 ===========================================================================*/
344
345 FUNCTION delete_po(X_po_header_id IN NUMBER,
346 X_type_lookup_code IN VARCHAR2,
347 p_skip_validation IN VARCHAR2) --<HTML Agreements R12>
348 return boolean is
349
350 X_deleted boolean;
351 X_progress VARCHAR2(3) := NULL;
352 X_allow_delete boolean;
353 X_rowid varchar2(30);
354 x_item_type varchar2(8);
355 x_item_key varchar2(240);
356 po_header_id_tbl PO_TBL_NUMBER;
357 l_return_status VARCHAR2(1);
358
359 BEGIN
360 X_progress := '010';
361
362 /* Retrieve PO row_id */
363 SELECT poh.rowid
364 INTO X_rowid
365 FROM PO_HEADERS_ALL poh /*Bug6632095: using base table instead of view */
366 WHERE poh.po_header_id = X_po_header_id;
367
368 --<HTML Agreements R12 Start>
369 -- If the calling source is HTML then we need not do the validations as we
370 -- would have already performed these validations in
371 -- PO_HEADERS_SV1.validate_delete_document
372 IF p_skip_validation = 'Y' THEN
373 x_progress := '012';
374 x_allow_delete := TRUE;
375 ELSE
376 X_progress := '015';
377 /* Validate if the Document can be deleted */
378 X_allow_delete := val_delete (X_po_header_id, X_type_lookup_code);
379 END IF;
380 --<HTML Agreements R12 End>
381
382 /* If the doc can be deleted, */
383
384 if (X_allow_delete) then
385
386 /* Call routine to delete PO notifications */
387 /*hvadlamu : commenting out the delete part. Adding the Workflow call to stop the process.
388 This call would also cancel any existing notifications waiting for a response*/
389 /*po_notifications_sv1.delete_po_notif (x_type_lookup_code,
390 x_po_header_id); */
391 SELECT wf_item_type,wf_item_key
392 INTO x_item_type,x_item_key
393 FROM PO_HEADERS_ALL /*Bug6632095: using base table instead of view */
394 WHERE po_header_id = x_po_header_id;
395
396 if ((x_item_type is null) and (x_item_key is null)) then
397 po_approval_reminder_sv.cancel_notif (x_type_lookup_code,
398 x_po_header_id);
399 else
400 /* when trying to delete a po it could be that it was submitted to
401 approval workflow and was never approved in which case we
402 need to stop the approval workflow as well as the reminder workflow */
403 po_approval_reminder_sv.cancel_notif (x_type_lookup_code,x_po_header_id);
404
405 po_approval_reminder_sv.stop_process(x_item_type,x_item_key);
406
407
408 end if;
409
410 /* Bug 2904413 Need to delete the action history also */
411 Delete po_action_history
412 Where OBJECT_TYPE_CODE = decode(x_type_lookup_code,
413 'STANDARD', 'PO',
414 'PLANNED','PO','PA') and
415 OBJECT_SUB_TYPE_CODE = x_type_lookup_code and
416 OBJECT_ID = x_po_header_id;
417
418 /* Delete header attachments */
419
420 fnd_attached_documents2_pkg.delete_attachments('PO_HEADERS',
421 x_po_header_id,
422 '', '', '', '', 'Y');
423
424 po_headers_sv1.delete_children(X_po_header_id, X_type_lookup_code);
425
426 -- delete uda data start
427 PO_MOD_CONTROL_PVT.delete_uda_data(
428 p_draft_id => -1,
429 p_entity_id => x_po_header_id,
430 p_entity_level => 'PO_HEADER',
431 x_return_status => l_return_status
432 );
433 -- delete uda data end
434
435 -- CLM Phase 4 - Elins project
436 -- Delete the exhibit details
437 DELETE po_exhibit_details
438 WHERE po_header_id = x_po_header_id;
439
440 po_headers_pkg_s2.delete_row(X_rowid);
441
442 if ((X_type_lookup_code = 'STANDARD') or
443 (X_type_lookup_code = 'PLANNED')) then
444
445 /* UPDATE REQ LINK */
446 po_headers_sv2.update_req_link(X_po_header_id);
447
448 end if;
449
450 X_deleted := TRUE;
451 return(X_deleted);
452
453 else
454
455 X_deleted := FALSE;
456 return(X_deleted);
457
458 end if;
459
460 EXCEPTION
461 WHEN NO_DATA_FOUND THEN
462 po_message_s.app_error('PO_ALL_RECORDS_NOT_FOUND');
463 RETURN (FALSE);
464 RAISE;
465 when others then
466 po_message_s.sql_error('delete_po', x_progress, sqlcode);
467 raise;
468
469 END delete_po;
470
471 /*===========================================================================
472
473 PROCEDURE NAME: delete_this_release()
474 ===========================================================================*/
475
476 PROCEDURE delete_this_release(X_po_header_id IN NUMBER) is
477
478 X_progress varchar2(3) := '';
479 X_release_id NUMBER;
480 X_rowid varchar2(30);
481
482 BEGIN
483
484 /* Delete the Releases against the PA if they exist */
485
486 X_progress := '010';
487 /* Retrieve the related release id and rowid if exists */
488
489 SELECT prl.po_release_id,
490 prl.rowid
491 INTO X_release_id,
492 X_rowid
493 FROM PO_RELEASES prl
494 WHERE prl.po_header_id = x_po_header_id;
495
496
497 X_progress := '015';
498
499 IF X_release_id is not NUll THEN
500
501 -- Attempt to lock the release for delete
502 po_releases_sv.lock_row_for_status_update (X_release_id);
503
504 -- Call the release server to delete the release document
505 po_releases_sv.delete_release (X_release_id, X_rowid);
506
507 END IF;
508
509
510 EXCEPTION
511
512 when no_data_found then
513
514 /* It is not an error if there have been no releases against this PA */
515 null;
516
517 when others then
518
519 po_message_s.sql_error('delete_po', x_progress, sqlcode);
520 raise;
521
522 END delete_this_release;
523
524 /*===========================================================================
525
526 PROCEDURE NAME: insert_po()- Moved to po_headers_sv11
527 ===========================================================================*/
528
529 /*===========================================================================
530
531 PROCEDURE NAME: insert_children()
532
533 ===========================================================================*/
534 /*
535 PROCEDURE insert_children() IS
536
537 x_progress VARCHAR2(3) := NULL;
538
539 BEGIN
540
541
542 EXCEPTION
543 WHEN OTHERS THEN
544 po_message_s.sql_error('insert_children', x_progress, sqlcode);
545 RAISE;
546
547 END insert_children; */
548
549 --<HTML Agreements R12 START>
550 -----------------------------------------------------------------------------
551 --Start of Comments
552 --Name: validate_delete_document
553 --Pre-reqs:
554 -- None
555 --Modifies:
556 -- None
557 --Locks:
558 -- None
559 --Function:
560 -- Checks whether the delete action is valid for this header
561 -- and in case it is not it returns the error message.
562 --Parameters:
563 --IN:
564 --p_doc_type
565 -- Document Type PO/PA
566 --p_doc_header_id
567 -- Header ID of the document whose header is being validated
568 --p_doc_approved_date
569 -- Latest Approval Date for the document
570 --p_auth_status
571 -- Authorization Status of the Document. See 'AUTHORIZATION STATUS' lookup type
572 --p_style_disp_name
573 -- Translated Style Display Name
574 --x_message_text
575 -- Will hold the error message in case the header cannot be deleted
576 --Notes:
577 -- Some of the validations which have already been done in Java layer are done
578 -- here again so that this procedure can be used by other modules which may
579 -- not have done any validation before calling this procedure.
580 --End of Comments
581 -----------------------------------------------------------------------------
582 PROCEDURE validate_delete_document( p_doc_type IN VARCHAR2
583 ,p_doc_header_id IN NUMBER
584 ,p_doc_approved_date IN DATE
585 ,p_auth_status IN VARCHAR2
586 ,p_style_disp_name IN VARCHAR2
587 ,x_message_text OUT NOCOPY VARCHAR2)
588 IS
589 l_some_dists_reserved_flag VARCHAR2(1) := 'N';
590 d_pos NUMBER := 10;
591 l_api_name CONSTANT VARCHAR2(30) := 'validate_delete_document';
592 d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_HEADERS_SV1.validate_delete_document';
593 BEGIN
594 IF (PO_LOG.d_proc) THEN
595 PO_LOG.proc_begin(d_module); PO_LOG.proc_begin(d_module,'p_doc_type', p_doc_type); PO_LOG.proc_begin(d_module,'p_doc_header_id', p_doc_header_id); PO_LOG.proc_begin(d_module,'p_doc_approved_date', p_doc_approved_date);
596 PO_LOG.proc_begin(d_module,'p_auth_status', p_auth_status); PO_LOG.proc_begin(d_module,'p_style_disp_name', p_style_disp_name);
597 END IF;
598
599 IF p_doc_approved_date IS NOT NULL THEN
600 x_message_text := PO_CORE_S.get_translated_text('PO_PO_USE_CANCEL_ON_APRVD_PO3'
601 ,'DOCUMENT_TYPE'
602 , p_style_disp_name);
603 RAISE PO_CORE_S.G_EARLY_RETURN_EXC;
604 END IF;
605
606 d_pos := 20;
607 -- Disallow a delete if any distributions are reserved.
608 PO_CORE_S.are_any_dists_reserved(
609 p_doc_type => p_doc_type,
610 p_doc_level => PO_CORE_S.g_doc_level_HEADER,
611 p_doc_level_id => p_doc_header_id,
612 x_some_dists_reserved_flag => l_some_dists_reserved_flag);
613 d_pos:= 30;
614
615 IF (PO_LOG.d_stmt) THEN
616 PO_LOG.stmt(d_module,d_pos,'l_some_dists_reserved_flag',l_some_dists_reserved_flag);
617 END IF;
618
619 IF l_some_dists_reserved_flag = 'Y'
620 THEN
621 x_message_text := PO_CORE_S.get_translated_text('PO_PO_USE_CANCEL_ON_ENCUMB_PO');
622 RAISE PO_CORE_S.G_EARLY_RETURN_EXC;
623 END IF;
624
625 IF (PO_LOG.d_proc) THEN
626 PO_LOG.proc_end(d_module);
627 END IF;
628
629 EXCEPTION
630 WHEN PO_CORE_S.G_EARLY_RETURN_EXC THEN
631 IF (PO_LOG.d_stmt) THEN
632 PO_LOG.stmt(d_module,d_pos,'x_message_text',x_message_text);
633 END IF;
634 WHEN OTHERS THEN
635 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name||':'||d_pos);
636 IF PO_LOG.d_exc THEN
637 PO_LOG.exc(d_module,d_pos,'Unhandled Exception in' || d_module);
638 END IF;
639 RAISE;
640 END validate_delete_document;
641
642 -----------------------------------------------------------------------------
643 --Start of Comments
644 --Name: delete_document
645 --Pre-reqs:
646 -- PO_HEADERS_SV1.validate_delete_document should be called to check if the
647 -- the delete action is a valid action on the document.
648 --Modifies:
649 -- None
650 --Locks:
651 -- None
652 --Function:
653 -- If the delete action is valid on the header, this procedure is responsible for
654 -- deleting the header as well its children and all the associated entities.
655 --Parameters:
656 --IN:
657 --p_doc_type
658 -- Document type of the PO [PO/PA]
659 --p_doc_subtype
660 -- Document Subtype of the [STANDARD/BLANKET/CONTRACT]
661 --p_doc_header_id
662 -- Header ID of the PO to which the entity being deleted belongs
663 --p_ga_flag
664 -- Whether the Document is global or not Global Agreement flag
665 --p_conterms_exist_flag
666 -- Whether the Document has Contract Terms or not
667 --OUT:
668 --x_return_status
669 -- Standard API specification parameter
670 -- Can hold one of the following values:
671 -- FND_API.G_RET_STS_SUCCESS (='S')
672 -- FND_API.G_RET_STS_ERROR (='E')
673 --End of Comments
674 -----------------------------------------------------------------------------
675 PROCEDURE delete_document( p_doc_type IN VARCHAR2
676 ,p_doc_subtype IN VARCHAR2
677 ,p_doc_header_id IN NUMBER
678 ,p_ga_flag IN VARCHAR2
679 ,p_conterms_exist_flag IN VARCHAR2
680 ,x_return_status OUT NOCOPY VARCHAR2)
681 IS
682 l_msg_count NUMBER;
683 l_msg_data VARCHAR2(2000);
684 d_pos NUMBER := 0;
685 l_api_name CONSTANT VARCHAR2(30) := 'delete_document';
686 d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_HEADERS_SV1.delete_document';
687
688 BEGIN
689 IF (PO_LOG.d_proc) THEN
690 PO_LOG.proc_begin(d_module); PO_LOG.proc_begin(d_module,'p_doc_type', p_doc_type); PO_LOG.proc_begin(d_module,'p_doc_header_id', p_doc_header_id); PO_LOG.proc_begin(d_module,'p_ga_flag', p_ga_flag);
691 PO_LOG.proc_begin(d_module,'p_conterms_exist_flag', p_conterms_exist_flag);
692 END IF;
693 d_pos := 10;
694 -- By default return status is SUCCESS if no exception occurs
695 x_return_status := FND_API.G_RET_STS_SUCCESS;
696
697 --Call the delete procedure
698 IF delete_po( x_po_header_id => p_doc_header_id
699 ,x_type_lookup_code => p_doc_subtype
700 ,p_skip_validation => 'Y') THEN
701
702 d_pos := 20;
703 --delete the GA Org Assignment Records if an Global Agreement
704 IF (p_ga_flag = 'Y' )
705 THEN
706 d_pos := 30;
707 PO_GA_ORG_ASSIGN_PVT.delete_row(p_doc_header_id);
708 END IF;
709
710 d_pos := 40;
711 IF p_conterms_exist_flag = 'Y' then
712 d_pos := 50;
713 -- call contracts to delete contract terms
714 OKC_TERMS_UTIL_GRP.DELETE_DOC(
715 p_api_version => 1.0,
716 p_init_msg_list => 'F',
717 p_commit => 'F',
718 p_doc_id => p_doc_header_id,
719 p_doc_type => p_doc_type || '_' || p_doc_subtype,
720 p_validate_commit =>'F',
721 x_return_status => x_return_status,
722 x_msg_data => l_msg_data,
723 x_msg_count => l_msg_count);
724
725 d_pos := 60;
726 IF (PO_LOG.d_stmt) THEN
727 PO_LOG.stmt(d_module,d_pos,'x_return_status',x_return_status); PO_LOG.stmt(d_module,d_pos,'l_msg_count',l_msg_count); PO_LOG.stmt(d_module,d_pos,'l_msg_data',l_msg_data);
728 END IF;
729
730 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
731 d_pos := 70;
732 IF(FND_MSG_PUB.Count_Msg = 0) THEN
733 FND_MESSAGE.set_name('PO','PO_API_ERROR');
734 FND_MESSAGE.set_token('PROC_CALLER',l_api_name);
735 FND_MESSAGE.set_token('PROC_CALLED','OKC_TERMS_UTIL_GRP.delete_doc');
736 FND_MSG_PUB.add;
737 RAISE FND_API.g_exc_error;
738 --else the message stack will be populated by the called procedure
739 END IF;
740 END IF; --x_return_status <> FND_API.G_RET_STS_SUCCESS
741
742 END IF; -- conterms flag
743
744 ELSE
745 d_pos := 80;
746 RAISE FND_API.g_exc_error;
747 END IF;--PO_HEADERS_SV1.delete_po
748
749 IF (PO_LOG.d_proc) THEN
750 PO_LOG.proc_end(d_module);
751 END IF;
752
753 EXCEPTION
754 WHEN FND_API.g_exc_error THEN
755 x_return_status := FND_API.g_ret_sts_error;
756 WHEN OTHERS THEN
757 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name||':'||d_pos);
758 IF PO_LOG.d_exc THEN
759 PO_LOG.exc(d_module,d_pos,'Unhandled Exception in' || d_module);
760 END IF;
761 RAISE;
762 END;
763
764
765 -----------------------------------------------------------------------------
766 --Bug 12405805
767 --Start of Comments
768 --Name: delete_events_entities
769 --Pre-reqs:
770 -- None.
771 --Modifies:
772 -- None
773 --Locks:
774 -- None
775 --Function:
776 -- If there are any unprocessed events associated with a document, those will be deleted.
777 -- If, after deleting unprocessed events, no processed events exist for the document,
778 -- then the entity associated with the document is deleted too.
779 --Parameters:
780 --IN:
781 --p_doc_entity
782 -- 'PURCHASE_ORDER', 'RELEASE', 'REQUISITION'
783 --p_doc_id
784 -- Header id of the p_doc_entity
785 --OUT:
786
787 --End of Comments
788 -----------------------------------------------------------------------------
789
790
791 PROCEDURE Delete_events_entities(p_doc_entity IN VARCHAR2,
792 p_doc_id IN NUMBER)
793 IS
794 l_event_source_info xla_events_pub_pkg.t_event_source_info;
795 l_security_context xla_events_pub_pkg.t_security;
796 l_ledger_id NUMBER;
797 l_legal_entity_id NUMBER;
798 l_delete_event NUMBER;
799 l_org_id NUMBER;
800
801 l_api_name CONSTANT varchar2(30) := 'Delete_events_entities';
802 l_log_head CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
803 l_progress VARCHAR2(3) := '000';
804 l_doc_type varchar2(20);
805 l_is_encumb_on BOOLEAN;
806
807 g_doc_type_RELEASE CONSTANT
808 PO_DOCUMENT_TYPES.document_type_code%TYPE := PO_CORE_S.g_doc_type_RELEASE;
809
810 g_doc_type_REQUISITION CONSTANT
811 PO_DOCUMENT_TYPES.document_type_code%TYPE := PO_CORE_S.g_doc_type_REQUISITION;
812
813
814 g_doc_type_PO CONSTANT
815 PO_DOCUMENT_TYPES.document_type_code%TYPE := PO_CORE_S.g_doc_type_PO;
816
817
818 BEGIN
819
820
821 IF g_debug_stmt THEN
822 PO_DEBUG.debug_begin(l_log_head);
823 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_entity',p_doc_entity);
824 PO_DEBUG.debug_var(l_log_head,l_progress,'p_doc_id',p_doc_id);
825 END IF;
826
827
828
829 IF ( p_doc_entity = 'RELEASE' ) THEN
830 SELECT org_id INTO l_org_id FROM po_releases_all WHERE po_release_id = p_doc_id;
831 l_doc_type := g_doc_type_RELEASE;
832 ELSIF(p_doc_entity ='PURCHASE_ORDER' ) THEN
833 SELECT org_id INTO l_org_id FROM po_headers_all WHERE po_header_id = p_doc_id;
834 l_doc_type := g_doc_type_PO;
835 ELSE /*p_doc_entity = 'REQUISITION' */
836 SELECT org_id INTO l_org_id FROM po_requisition_headers_all WHERE requisition_header_id = p_doc_id;
837 l_doc_type := g_doc_type_REQUISITION;
838 END IF;
839
840
841 l_progress:= '010';
842
843 IF g_debug_stmt THEN
844 PO_DEBUG.debug_var(l_log_head,l_progress,'l_org_id',l_org_id);
845 END IF;
846
847 l_progress:= '015';
848
849 /*Event and entity deletions are done for encumbered documents only.
850 Checking whether the document is created in encumbrance environment
851 or not.
852 */
853
854 l_is_encumb_on := PO_CORE_S.is_encumbrance_on(
855 p_doc_type => l_doc_type
856 , p_org_id => l_org_id
857 ) ;
858 IF ( NOT l_is_encumb_on ) THEN
859 RETURN;
860 ELSE
861
862 IF g_debug_stmt THEN
863 PO_DEBUG.debug_var(l_log_head,l_progress,'l_is_encumb_on', 'TRUE');
864 END IF;
865
866
867 SELECT set_of_books_id INTO l_ledger_id
868 FROM hr_operating_units hou
869 WHERE hou.organization_id = l_org_id;
870
871 l_progress:= '020';
872
873 IF g_debug_stmt THEN
874 PO_DEBUG.debug_var(l_log_head,l_progress,'l_ledger_id',l_ledger_id);
875 END IF;
876
877 l_security_context.security_id_int_1 := l_org_id;
878 l_legal_entity_id := xle_utilities_grp.Get_defaultlegalcontext_ou(l_org_id);
879
880 l_progress:= '030';
881
882 IF g_debug_stmt THEN
883 PO_DEBUG.debug_var(l_log_head,l_progress,'l_ledger_id',l_ledger_id);
884 END IF;
885
886 --Building the event source information
887 l_event_source_info.source_application_id := NULL;
888 l_event_source_info.application_id := 201;
889 l_event_source_info.legal_entity_id := l_legal_entity_id;
890 l_event_source_info.ledger_id := l_ledger_id;
891 l_event_source_info.entity_type_code := p_doc_entity;
892 l_event_source_info.source_id_int_1 := p_doc_id; -- header_id
893
894 l_progress:= '040';
895
896 IF g_debug_stmt THEN
897 PO_DEBUG.debug_var(l_log_head,l_progress,'l_event_source_info.source_application_id',l_event_source_info.source_application_id);
898 PO_DEBUG.debug_var(l_log_head,l_progress,'l_event_source_info.application_id', l_event_source_info.application_id);
899 PO_DEBUG.debug_var(l_log_head,l_progress,'l_event_source_info.legal_entity_id', l_event_source_info.legal_entity_id);
900 PO_DEBUG.debug_var(l_log_head,l_progress,'l_event_source_info.ledger_id', l_event_source_info.ledger_id);
901 PO_DEBUG.debug_var(l_log_head,l_progress,'l_event_source_info.entity_type_code', l_event_source_info.entity_type_code);
902 PO_DEBUG.debug_var(l_log_head,l_progress,'l_event_source_info.source_id_int_1', l_event_source_info.source_id_int_1);
903 END IF;
904
905 IF( p_doc_entity <> 'RELEASE' ) then
906 --Before cleaning up of unwanted events, cleaning the PO_BC_DISTRIBUTIONS too
907 DELETE FROM po_bc_distributions WHERE header_id = p_doc_id
908 AND APPLIED_TO_ENTITY_CODE = p_doc_entity
909 AND ae_event_id IN (SELECT event_id FROM xla_events WHERE event_status_code = 'U' AND process_status_code IN ('I', 'D'));
910 else
911 --Before cleaning up of unwanted events, cleaning the PO_BC_DISTRIBUTIONS too
912 DELETE FROM po_bc_distributions WHERE po_release_id = p_doc_id
913 AND APPLIED_TO_ENTITY_CODE = p_doc_entity
914 AND ae_event_id IN (SELECT event_id FROM xla_events WHERE event_status_code = 'U' AND process_status_code IN ('I', 'D'));
915 END IF;
916
917 begin
918 IF (xla_events_pub_pkg.event_exists(p_event_source_info => l_event_source_info,
919 p_valuation_method => NULL,
920 p_security_context => l_security_context,
921 p_event_status_code => 'U'
922 ) ) THEN
923
924 l_delete_event := xla_events_pub_pkg.delete_events(p_event_source_info => l_event_source_info,
925 p_valuation_method => NULL,
926 p_security_context => l_security_context) ;
927 END if;
928 EXCEPTION
929 WHEN OTHERS THEN
930 IF g_debug_stmt THEN
931 PO_DEBUG.debug_var(l_log_head,l_progress,'Exception of event_exists, delete_event',sqlerrm);
932 END IF;
933 NULL;
934 END;
935
936
937 begin
938 l_delete_event := xla_events_pub_pkg.Delete_entity(
939 p_source_info => l_event_source_info,
940 p_valuation_method => NULL,
941 p_security_context => l_security_context);
942
943 EXCEPTION
944 WHEN OTHERS THEN
945 IF g_debug_stmt THEN
946 PO_DEBUG.debug_var(l_log_head,l_progress,'Exception of delete_entity',sqlerrm);
947 END IF;
948 NULL;
949 END;
950 END IF;
951
952 EXCEPTION
953 WHEN OTHERS THEN
954 IF g_debug_stmt THEN
955 PO_DEBUG.debug_var(l_log_head,l_progress,'Exception Block of Delete_Events_entities',sqlerrm);
956 END IF;
957
958 RAISE;
959 END delete_events_entities;
960
961 --<HTML Agreements R12 END>
962 END PO_HEADERS_SV1;
[Home] [Help]