1 PACKAGE BODY PO_HEADERS_SV1 as
2 /* $Header: POXPOH1B.pls 120.4 2007/12/04 09:01:22 bisdas ship $*/
3
4 /*===========================================================================
5
6 PROCEDURE NAME: lock_row_for_status_update
7
8 ===========================================================================*/
9
10 PROCEDURE lock_row_for_status_update (x_po_header_id IN NUMBER)
11 IS
12 CURSOR C IS
13 SELECT *
14 FROM po_headers
15 WHERE po_header_id = x_po_header_id
16 FOR UPDATE of po_header_id NOWAIT;
17 Recinfo C%ROWTYPE;
18
19 x_progress VARCHAR2(3) := '';
20
21 BEGIN
22 x_progress := '010';
23 OPEN C;
24 FETCH C INTO Recinfo;
25 IF (C%NOTFOUND) then
26 CLOSE C;
27 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
28 APP_EXCEPTION.Raise_Exception;
29 END IF;
30 CLOSE C;
31
32 EXCEPTION
33 WHEN app_exception.record_lock_exception THEN
34 po_message_s.app_error ('PO_ALL_CANNOT_RESERVE_RECORD');
35
36 WHEN OTHERS THEN
37 --dbms_output.put_line('In Exception');
38 PO_MESSAGE_S.SQL_ERROR('LOCK_ROW_FOR_STATUS_UPDATE', x_progress, sqlcode);
39 RAISE;
40 END;
41
42 /*===========================================================================
43
44 FUNCTION NAME: val_po_encumbered()
45
46 ===========================================================================*/
47
48 /* PROCEDURE val_po_encumbered() IS
49
50 x_progress VARCHAR2(3) := NULL;
51
52 BEGIN
53
54
55 EXCEPTION
56 WHEN OTHERS THEN
57 po_message.set_name('val_po_encumbered', x_progress, sqlcode);
58 RAISE;
59
60 END val_po_encumbered; */
61
62 /*===========================================================================
63
64 FUNCTION NAME: get_doc_num()
65
66 ===========================================================================*/
67
68 /*PROCEDURE get_doc_num() IS
69
70 x_progress VARCHAR2(3) := NULL;
71
72 BEGIN
73
74
75 EXCEPTION
76 WHEN OTHERS THEN
77 po_message_s.sql_error('get_doc_num', x_progress, sqlcode);
78 RAISE;
79
80 END get_doc_num; */
81
82 /*===========================================================================
83
84 PROCEDURE NAME: val_delete()
85
86 ===========================================================================*/
87
88 FUNCTION val_delete(X_po_header_id IN NUMBER , X_type_lookup_code IN VARCHAR2)
89 return boolean is
90 X_allow_delete boolean;
91
92 X_progress VARCHAR2(3) := NULL;
93 X_encumbered boolean;
94
95 BEGIN
96
97 /* If it is a PO or an Agreement check if it is encumbered */
98
99 if ((X_type_lookup_code = 'STANDARD') or
100 (X_type_lookup_code = 'PLANNED') or
101 (X_type_lookup_code = 'BLANKET') or
102 (X_type_lookup_code = 'CONTRACT') ) then
103
104 X_progress := '005';
105
106 X_encumbered := po_headers_sv1.get_po_encumbered(X_po_header_id);
107
108 /* If the PO is encumbered, it has to be cancelled */
109
110 if X_encumbered then
111 X_allow_delete := FALSE;
112 po_message_s.app_error('PO_PO_USE_CANCEL_ON_ENCUMB_PO');
113 else
114 X_allow_delete := TRUE;
115 end if;
116
117 elsif (X_type_lookup_code = 'RFQ') then
118
119 X_progress := '010';
120 po_rfqs_sv.val_header_delete (X_po_header_id,
121 X_allow_delete);
122
123 elsif (X_type_lookup_code = 'QUOTATION') then
124
125 X_progress := '015';
126 po_quotes_sv.val_header_delete (X_po_header_id,
127 X_allow_delete);
128 end if;
129
130 return(X_allow_delete);
131
132
133 EXCEPTION
134 when others then
135 X_allow_delete := FALSE;
136 po_message_s.sql_error('val_delete', x_progress, sqlcode);
137 raise;
138
139 END val_delete;
140
141 /*===========================================================================
142
143 PROCEDURE NAME: get_po_encumbered()
144
145 ===========================================================================*/
146
147 FUNCTION get_po_encumbered(X_po_header_id IN number)
148 return boolean is
149
150 X_encumbered boolean := FALSE;
151
152 X_progress VARCHAR2(3) := '';
153
154 cursor c1 is SELECT 'Y'
155 FROM po_distributions
156 WHERE po_header_id = X_po_header_id
157 AND nvl(encumbered_flag,'N') <> 'N';
158
159 --BUG 3230237
160 --PO_HEADERS_SV1.delete_po calls this procedure to determine
161 --if a PO can be deleted. We need to prevent encumbered
162 --BPAs from getting deleted.
163 --AND distribution_type <> 'AGREEMENT'; --<Encumbrance FPJ>
164
165 Recinfo c1%rowtype;
166
167 BEGIN
168 X_progress := '010';
169 open c1;
170 X_progress := '020';
171
172 /* Check if any distributions for a given po_header_id is encumbered
173 ** If there are encumbered distributions, return TRUE else
174 ** return FALSE */
175
176 fetch c1 into recinfo;
177
178 X_progress := '030';
179
180 if (c1%notfound) then
181 close c1;
182 X_encumbered := FALSE;
183 return(X_encumbered);
184 end if;
185
186 X_encumbered := TRUE;
187 return(X_encumbered);
188
189
190 exception
191 when others then
192 po_message_s.sql_error('get_po_encumbered', X_progress, sqlcode);
193 raise;
194
195 END get_po_encumbered;
196
197 /*===========================================================================
198
199 PROCEDURE NAME: delete_children()
200
201 ===========================================================================*/
202
203 PROCEDURE delete_children(X_po_header_id IN NUMBER,
204 X_type_lookup_code IN VARCHAR2) IS
205
206 X_progress VARCHAR2(3) := NULL;
207 X_deleted boolean;
208 BEGIN
209
210
211 --BUG 3230237
212 --Added 'BLANKET' to the if-condition since Encumbrance BPAs can have distributions.
213 if (X_type_lookup_code IN ('STANDARD','PLANNED','BLANKET')) then --BUG 3230237
214 /* Delete Distributions for a PO */
215 X_progress := '020';
216 --dbms_output.put_line('Before Delete All Distributions');
217 po_distributions_sv.delete_distributions(X_po_header_id, 'HEADER');
218 end if;
219
220 if (X_type_lookup_code <> 'CONTRACT') then
221
222 /* Delete Shipments for a PO */
223 X_progress := '015';
224 --dbms_output.put_line('Before Delete All Shipments ');
225 po_shipments_sv4.delete_all_shipments (X_po_header_id, 'HEADER',
226 X_type_lookup_code);
227
228 /* Delete Lines for a PO */
229 X_progress := '010';
230 --dbms_output.put_line('Before Delete All lines ');
231 po_lines_sv.delete_all_lines (X_po_header_id,
232 X_type_lookup_code); --<HTML Agreements R12>
233
234 /* Delete Vendors for a PO */
235 if (X_type_lookup_code in ('RFQ','QUOTATION')) then
236 X_progress := '035';
237 --dbms_output.put_line('Before Delete All vendors ');
238 po_rfq_vendors_pkg_s2.delete_all_vendors (X_po_header_id);
239 end if;
240
241 end if;
242
243 /* Delete Notification Controls if it is PLANNED/BLANKET/CONTRACT PO */
244
245 if ((X_type_lookup_code = 'PLANNED') or
246 (X_type_lookup_code = 'BLANKET') or
247 (X_type_lookup_code = 'CONTRACT')) then
248
249 /* Call routine to delete po notification controls */
250 X_progress := '025';
251 --dbms_output.put_line('Before Delete Notification Controls');
252 X_deleted := po_notif_controls_sv.delete_notifs (X_po_header_id);
253
254 end if;
255
256 -- DEBUG it seems that this part of code is not needed since we don't allow to
257 -- delete approved blanket/planned PO. If it is unapproved, there should
258 -- have no releases against it.
259 -- Remove it after reviewing this with KIM.
260 --
261 -- /* Delete All Releases for this BLANKET/PLANNED that is being deleted.*/
262 --
263 -- if ((X_type_lookup_code = 'PLANNED') or
264 -- (X_type_lookup_code = 'BLANKET')) then
265 --
266 -- X_progress := '030';
267 -- --dbms_output.put_line('Before Delete all releases');
268 -- po_headers_sv1.delete_this_release (X_po_header_id);
269 --
270 -- end if;
271 --
272
273
274 EXCEPTION
275
276 when others then
277 po_message_s.sql_error('delete_children', x_progress, sqlcode);
278 raise;
279
280 END delete_children;
281
282 /*===========================================================================
283
284 PROCEDURE NAME: delete_po()
285
286 ===========================================================================*/
287
288 FUNCTION delete_po(X_po_header_id IN NUMBER,
289 X_type_lookup_code IN VARCHAR2,
290 p_skip_validation IN VARCHAR2) --<HTML Agreements R12>
291 return boolean is
292
293 X_deleted boolean;
294 X_progress VARCHAR2(3) := NULL;
295 X_allow_delete boolean;
296 X_rowid varchar2(30);
297 x_item_type varchar2(8);
298 x_item_key varchar2(240);
299
300 BEGIN
301 X_progress := '010';
302
303 /* Retrieve PO row_id */
304 SELECT poh.rowid
305 INTO X_rowid
306 FROM PO_HEADERS_ALL poh /*Bug6632095: using base table instead of view */
307 WHERE poh.po_header_id = X_po_header_id;
308
309 --<HTML Agreements R12 Start>
310 -- If the calling source is HTML then we need not do the validations as we
311 -- would have already performed these validations in
312 -- PO_HEADERS_SV1.validate_delete_document
313 IF p_skip_validation = 'Y' THEN
314 x_progress := '012';
315 x_allow_delete := TRUE;
316 ELSE
317 X_progress := '015';
318 /* Validate if the Document can be deleted */
319 X_allow_delete := val_delete (X_po_header_id, X_type_lookup_code);
320 END IF;
321 --<HTML Agreements R12 End>
322
323 /* If the doc can be deleted, */
324
325 if (X_allow_delete) then
326
327 /* Call routine to delete PO notifications */
328 /*hvadlamu : commenting out the delete part. Adding the Workflow call to stop the process.
329 This call would also cancel any existing notifications waiting for a response*/
330 /*po_notifications_sv1.delete_po_notif (x_type_lookup_code,
331 x_po_header_id); */
332 SELECT wf_item_type,wf_item_key
333 INTO x_item_type,x_item_key
334 FROM PO_HEADERS_ALL /*Bug6632095: using base table instead of view */
335 WHERE po_header_id = x_po_header_id;
336
337 if ((x_item_type is null) and (x_item_key is null)) then
338 po_approval_reminder_sv.cancel_notif (x_type_lookup_code,
339 x_po_header_id);
340 else
341 /* when trying to delete a po it could be that it was submitted to
342 approval workflow and was never approved in which case we
343 need to stop the approval workflow as well as the reminder workflow */
344 po_approval_reminder_sv.cancel_notif (x_type_lookup_code,x_po_header_id);
345
346 po_approval_reminder_sv.stop_process(x_item_type,x_item_key);
347
348
349 end if;
350
351 /* Bug 2904413 Need to delete the action history also */
352 Delete po_action_history
353 Where OBJECT_TYPE_CODE = decode(x_type_lookup_code,
354 'STANDARD', 'PO',
355 'PLANNED','PO','PA') and
356 OBJECT_SUB_TYPE_CODE = x_type_lookup_code and
357 OBJECT_ID = x_po_header_id;
358
359 /* Delete header attachments */
360
361 fnd_attached_documents2_pkg.delete_attachments('PO_HEADERS',
362 x_po_header_id,
363 '', '', '', '', 'Y');
364
365 po_headers_sv1.delete_children(X_po_header_id, X_type_lookup_code);
366
367 po_headers_pkg_s2.delete_row(X_rowid);
368
369 if ((X_type_lookup_code = 'STANDARD') or
370 (X_type_lookup_code = 'PLANNED')) then
371
372 /* UPDATE REQ LINK */
373 po_headers_sv2.update_req_link(X_po_header_id);
374
375 end if;
376
377 X_deleted := TRUE;
378 return(X_deleted);
379
380 else
381
382 X_deleted := FALSE;
383 return(X_deleted);
384
385 end if;
386
387 EXCEPTION
388 WHEN NO_DATA_FOUND THEN
389 po_message_s.app_error('PO_ALL_RECORDS_NOT_FOUND');
390 RETURN (FALSE);
391 RAISE;
392 when others then
393 po_message_s.sql_error('delete_po', x_progress, sqlcode);
394 raise;
395
396 END delete_po;
397
398 /*===========================================================================
399
400 PROCEDURE NAME: delete_this_release()
401 ===========================================================================*/
402
403 PROCEDURE delete_this_release(X_po_header_id IN NUMBER) is
404
405 X_progress varchar2(3) := '';
406 X_release_id NUMBER;
407 X_rowid varchar2(30);
408
409 BEGIN
410
411 /* Delete the Releases against the PA if they exist */
412
413 X_progress := '010';
414 /* Retrieve the related release id and rowid if exists */
415
416 SELECT prl.po_release_id,
417 prl.rowid
418 INTO X_release_id,
419 X_rowid
420 FROM PO_RELEASES prl
421 WHERE prl.po_header_id = x_po_header_id;
422
423
424 X_progress := '015';
425
426 IF X_release_id is not NUll THEN
427
428 -- Attempt to lock the release for delete
429 po_releases_sv.lock_row_for_status_update (X_release_id);
430
431 -- Call the release server to delete the release document
432 po_releases_sv.delete_release (X_release_id, X_rowid);
433
434 END IF;
435
436
437 EXCEPTION
438
439 when no_data_found then
440
441 /* It is not an error if there have been no releases against this PA */
442 null;
443
444 when others then
445
446 po_message_s.sql_error('delete_po', x_progress, sqlcode);
447 raise;
448
449 END delete_this_release;
450
451 /*===========================================================================
452
453 PROCEDURE NAME: insert_po()- Moved to po_headers_sv11
454 ===========================================================================*/
455
456 /*===========================================================================
457
458 PROCEDURE NAME: insert_children()
459
460 ===========================================================================*/
461 /*
462 PROCEDURE insert_children() IS
463
464 x_progress VARCHAR2(3) := NULL;
465
466 BEGIN
467
468
469 EXCEPTION
470 WHEN OTHERS THEN
471 po_message_s.sql_error('insert_children', x_progress, sqlcode);
472 RAISE;
473
474 END insert_children; */
475
476 --<HTML Agreements R12 START>
477 -----------------------------------------------------------------------------
478 --Start of Comments
479 --Name: validate_delete_document
480 --Pre-reqs:
481 -- None
482 --Modifies:
483 -- None
484 --Locks:
485 -- None
486 --Function:
487 -- Checks whether the delete action is valid for this header
488 -- and in case it is not it returns the error message.
489 --Parameters:
490 --IN:
491 --p_doc_type
492 -- Document Type PO/PA
493 --p_doc_header_id
494 -- Header ID of the document whose header is being validated
495 --p_doc_approved_date
496 -- Latest Approval Date for the document
497 --p_auth_status
498 -- Authorization Status of the Document. See 'AUTHORIZATION STATUS' lookup type
499 --p_style_disp_name
500 -- Translated Style Display Name
501 --x_message_text
502 -- Will hold the error message in case the header cannot be deleted
503 --Notes:
504 -- Some of the validations which have already been done in Java layer are done
505 -- here again so that this procedure can be used by other modules which may
506 -- not have done any validation before calling this procedure.
507 --End of Comments
508 -----------------------------------------------------------------------------
509 PROCEDURE validate_delete_document( p_doc_type IN VARCHAR2
510 ,p_doc_header_id IN NUMBER
511 ,p_doc_approved_date IN DATE
512 ,p_auth_status IN VARCHAR2
513 ,p_style_disp_name IN VARCHAR2
514 ,x_message_text OUT NOCOPY VARCHAR2)
515 IS
516 l_some_dists_reserved_flag VARCHAR2(1) := 'N';
517 d_pos NUMBER := 10;
518 l_api_name CONSTANT VARCHAR2(30) := 'validate_delete_document';
519 d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_HEADERS_SV1.validate_delete_document';
520 BEGIN
521 IF (PO_LOG.d_proc) THEN
522 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);
523 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);
524 END IF;
525
526 IF p_doc_approved_date IS NOT NULL THEN
527 x_message_text := PO_CORE_S.get_translated_text('PO_PO_USE_CANCEL_ON_APRVD_PO3'
528 ,'DOCUMENT_TYPE'
529 , p_style_disp_name);
530 RAISE PO_CORE_S.G_EARLY_RETURN_EXC;
531 END IF;
532
533 d_pos := 20;
534 -- Disallow a delete if any distributions are reserved.
535 PO_CORE_S.are_any_dists_reserved(
536 p_doc_type => p_doc_type,
537 p_doc_level => PO_CORE_S.g_doc_level_HEADER,
538 p_doc_level_id => p_doc_header_id,
539 x_some_dists_reserved_flag => l_some_dists_reserved_flag);
540 d_pos:= 30;
541
542 IF (PO_LOG.d_stmt) THEN
543 PO_LOG.stmt(d_module,d_pos,'l_some_dists_reserved_flag',l_some_dists_reserved_flag);
544 END IF;
545
546 IF l_some_dists_reserved_flag = 'Y'
547 THEN
548 x_message_text := PO_CORE_S.get_translated_text('PO_PO_USE_CANCEL_ON_ENCUMB_PO');
549 RAISE PO_CORE_S.G_EARLY_RETURN_EXC;
550 END IF;
551
552 IF (PO_LOG.d_proc) THEN
553 PO_LOG.proc_end(d_module);
554 END IF;
555
556 EXCEPTION
557 WHEN PO_CORE_S.G_EARLY_RETURN_EXC THEN
558 IF (PO_LOG.d_stmt) THEN
559 PO_LOG.stmt(d_module,d_pos,'x_message_text',x_message_text);
560 END IF;
561 WHEN OTHERS THEN
562 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name||':'||d_pos);
563 IF PO_LOG.d_exc THEN
564 PO_LOG.exc(d_module,d_pos,'Unhandled Exception in' || d_module);
565 END IF;
566 RAISE;
567 END validate_delete_document;
568
569 -----------------------------------------------------------------------------
570 --Start of Comments
571 --Name: delete_document
572 --Pre-reqs:
573 -- PO_HEADERS_SV1.validate_delete_document should be called to check if the
574 -- the delete action is a valid action on the document.
575 --Modifies:
576 -- None
577 --Locks:
578 -- None
579 --Function:
580 -- If the delete action is valid on the header, this procedure is responsible for
581 -- deleting the header as well its children and all the associated entities.
582 --Parameters:
583 --IN:
584 --p_doc_type
585 -- Document type of the PO [PO/PA]
586 --p_doc_subtype
587 -- Document Subtype of the [STANDARD/BLANKET/CONTRACT]
588 --p_doc_header_id
589 -- Header ID of the PO to which the entity being deleted belongs
590 --p_ga_flag
591 -- Whether the Document is global or not Global Agreement flag
592 --p_conterms_exist_flag
593 -- Whether the Document has Contract Terms or not
594 --OUT:
595 --x_return_status
596 -- Standard API specification parameter
597 -- Can hold one of the following values:
598 -- FND_API.G_RET_STS_SUCCESS (='S')
599 -- FND_API.G_RET_STS_ERROR (='E')
600 --End of Comments
601 -----------------------------------------------------------------------------
602 PROCEDURE delete_document( p_doc_type IN VARCHAR2
603 ,p_doc_subtype IN VARCHAR2
604 ,p_doc_header_id IN NUMBER
605 ,p_ga_flag IN VARCHAR2
606 ,p_conterms_exist_flag IN VARCHAR2
607 ,x_return_status OUT NOCOPY VARCHAR2)
608 IS
609 l_msg_count NUMBER;
610 l_msg_data VARCHAR2(2000);
611 d_pos NUMBER := 0;
612 l_api_name CONSTANT VARCHAR2(30) := 'delete_document';
613 d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_HEADERS_SV1.delete_document';
614
615 BEGIN
616 IF (PO_LOG.d_proc) THEN
617 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);
618 PO_LOG.proc_begin(d_module,'p_conterms_exist_flag', p_conterms_exist_flag);
619 END IF;
620 d_pos := 10;
621 -- By default return status is SUCCESS if no exception occurs
622 x_return_status := FND_API.G_RET_STS_SUCCESS;
623
624 --Call the delete procedure
625 IF delete_po( x_po_header_id => p_doc_header_id
626 ,x_type_lookup_code => p_doc_subtype
627 ,p_skip_validation => 'Y') THEN
628
629 d_pos := 20;
630 --delete the GA Org Assignment Records if an Global Agreement
631 IF (p_ga_flag = 'Y' )
632 THEN
633 d_pos := 30;
634 PO_GA_ORG_ASSIGN_PVT.delete_row(p_doc_header_id);
635 END IF;
636
637 d_pos := 40;
638 IF p_conterms_exist_flag = 'Y' then
639 d_pos := 50;
640 -- call contracts to delete contract terms
641 OKC_TERMS_UTIL_GRP.DELETE_DOC(
642 p_api_version => 1.0,
643 p_init_msg_list => 'F',
644 p_commit => 'F',
645 p_doc_id => p_doc_header_id,
646 p_doc_type => p_doc_type || '_' || p_doc_subtype,
647 p_validate_commit =>'F',
648 x_return_status => x_return_status,
649 x_msg_data => l_msg_data,
650 x_msg_count => l_msg_count);
651
652 d_pos := 60;
653 IF (PO_LOG.d_stmt) THEN
654 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);
655 END IF;
656
657 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
658 d_pos := 70;
659 IF(FND_MSG_PUB.Count_Msg = 0) THEN
660 FND_MESSAGE.set_name('PO','PO_API_ERROR');
661 FND_MESSAGE.set_token('PROC_CALLER',l_api_name);
662 FND_MESSAGE.set_token('PROC_CALLED','OKC_TERMS_UTIL_GRP.delete_doc');
663 FND_MSG_PUB.add;
664 RAISE FND_API.g_exc_error;
665 --else the message stack will be populated by the called procedure
666 END IF;
667 END IF; --x_return_status <> FND_API.G_RET_STS_SUCCESS
668
669 END IF; -- conterms flag
670
671 ELSE
672 d_pos := 80;
673 RAISE FND_API.g_exc_error;
674 END IF;--PO_HEADERS_SV1.delete_po
675
676 IF (PO_LOG.d_proc) THEN
677 PO_LOG.proc_end(d_module);
678 END IF;
679
680 EXCEPTION
681 WHEN FND_API.g_exc_error THEN
682 x_return_status := FND_API.g_ret_sts_error;
683 WHEN OTHERS THEN
684 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name||':'||d_pos);
685 IF PO_LOG.d_exc THEN
686 PO_LOG.exc(d_module,d_pos,'Unhandled Exception in' || d_module);
687 END IF;
688 RAISE;
689 END;
690
691 --<HTML Agreements R12 END>
692 END PO_HEADERS_SV1;
[Home] [Help]