1 PACKAGE BODY PO_DISTRIBUTIONS_SV AS
2 /* $Header: POXPOD1B.pls 120.4.12020000.2 2013/05/30 11:48:48 gjyothi ship $ */
3
4 /*===========================================================================
5
6 PROCEDURE NAME: check_unique()
7
8 ===========================================================================*/
9
10 FUNCTION check_unique(x_line_location_id NUMBER,
11 x_distribution_num NUMBER,
12 x_rowid VARCHAR2) RETURN BOOLEAN IS
13
14 l_is_unique BOOLEAN;
15
16 x_progress VARCHAR2(3) := NULL;
17 x_dummy VARCHAR2(12) := NULL;
18
19 BEGIN
20 x_progress := '001';
21
22 -- bug3322899
23 -- The sql statement to check for uniqueness has been moved
24 -- to distribution_num_unique
25
26 l_is_unique :=
27 distribution_num_unique
28 ( p_line_location_id => x_line_location_id,
29 p_distribution_num => x_distribution_num,
30 p_rowid => x_rowid
31 );
32
33 IF (l_is_unique) THEN
34 RETURN TRUE;
35 ELSE
36 -- po_message_s.app_error('PO_PO_ENTER_UNIQUE_DIST_NUM');
37 RETURN(FALSE);
38 END IF;
39
40 EXCEPTION
41 WHEN OTHERS THEN
42 --dbms_output.put_line('In Exception');
43 po_message_s.sql_error('check_unique', x_progress, sqlcode);
44 RAISE;
45
46 END check_unique;
47
48 -- bug3322899 START
49
50
51 -----------------------------------------------------------------------
52 --Start of Comments
53 --Name: disribution_num_unique
54 --Pre-reqs: None
55 --Modifies:
56 --Locks:
57 -- None
58 --Function:
59 -- Return TRUE if the distribution number does not exist in the
60 -- Shipment being passed in. Return FALSE otherwise
61 --Parameters:
62 --IN:
63 --p_line_location_id
64 -- Line location id of the shipment
65 --p_distribution_num
66 -- Distribution Number being checked
67 --p_rowid
68 -- Rowid of the dsitribution being cheked. If provided, the record
69 -- with this ROWID will be excluded from being checked
70 --IN OUT:
71 --OUT:
72 --Returns: BOOLEAN that says whether the distribution number is unique
73 --Notes:
74 --Testing:
75 --End of Comments
76 ------------------------------------------------------------------------
77 FUNCTION distribution_num_unique
78 ( p_line_location_id IN NUMBER,
79 p_distribution_num IN NUMBER,
80 p_rowid IN VARCHAR2
81 ) RETURN BOOLEAN IS
82
83 l_progress VARCHAR2(3);
84 l_dummy VARCHAR2(20);
85 BEGIN
86
87 l_progress := '000';
88
89 --SQL WHAT: Given line_location_id and distribution_num,
90 -- check whether there is already a duplicate
91 --SQL WHY: To ensure that we do not insert multiple distributions
92 -- with same distribution num for a shipment
93
94 SELECT 'unique'
95 INTO l_dummy
96 FROM dual
97 WHERE NOT EXISTS
98 ( SELECT 1
99 FROM po_distributions
100 WHERE line_location_id = p_line_location_id
101 AND distribution_num = p_distribution_num
102 AND (rowid <> p_rowid
103 OR p_rowid IS NULL));
104
105 RETURN TRUE;
106
107 EXCEPTION
108 WHEN NO_DATA_FOUND THEN
109 RETURN FALSE;
110 WHEN OTHERS THEN
111 PO_MESSAGE_S.sql_error('distribution_num_unique', l_progress, SQLCODE);
112 RAISE;
113 END distribution_num_unique;
114 -- bug3322899 END
115
116
117 /*===========================================================================
118
119 FUNCTION NAME: get_max_dist_num()
120
121 ===========================================================================*/
122
123 FUNCTION get_max_dist_num(x_line_location_id NUMBER) RETURN NUMBER IS
124
125 x_progress VARCHAR2(3) := NULL;
126 max_dist_num NUMBER;
127
128 cursor C1 is
129 select nvl(max(distribution_num),0)
130 from po_distributions
131 where line_location_id = x_line_location_id;
132
133 BEGIN
134 x_progress := '001';
135 open C1;
136 fetch C1 into max_dist_num;
137 RETURN(max_dist_num);
138 close C1;
139
140 EXCEPTION
141 WHEN OTHERS THEN
142 --dbms_output.put_line('In Exception');
143 po_message_s.sql_error('get_max_dist_num', x_progress, sqlcode);
144 RAISE;
145 END get_max_dist_num;
146
147
148 /*===========================================================================
149
150 PROCEDURE NAME: select_summary()
151
152 ===========================================================================*/
153
154 PROCEDURE select_summary(x_line_location_id IN OUT NOCOPY NUMBER,
155 x_total IN OUT NOCOPY NUMBER) IS
156
157 x_progress VARCHAR2(3) := NULL;
158
159 BEGIN
160
161 -- Services FPJ calculate the total from amounts for service lines
162
163 select nvl(sum(decode(pol.order_type_lookup_code,'RATE',pod.amount_ordered,
164 'FIXED PRICE',pod.amount_ordered,pod.quantity_ordered)),0)
165 into x_total
166 from po_distributions pod,
167 po_lines pol
168 where pod.po_line_id = pol.po_line_id
169 and pod.line_location_id = x_line_location_id;
170
171
172 EXCEPTION
173 WHEN OTHERS THEN
174 --dbms_output.put_line('In Exception');
175 po_message_s.sql_error('select_summary', x_progress, sqlcode);
176 RAISE;
177 END select_summary;
178
179
180 /*===========================================================================
181
182 FUNCTION NAME: post_query()
183
184 ===========================================================================*/
185
186 PROCEDURE post_query(
187 x_deliver_to_location_id NUMBER,
188 x_deliver_to_person_id NUMBER,
189 x_ship_to_org_id NUMBER,
190 x_project_id NUMBER,
191 x_task_id NUMBER,
192 x_org_id NUMBER,
193 x_destination_type_code VARCHAR2,
194 x_deliver_to_location IN OUT NOCOPY VARCHAR2,
195 x_deliver_to_person IN OUT NOCOPY VARCHAR2,
196 x_project_num IN OUT NOCOPY VARCHAR2,
197 x_task_num IN OUT NOCOPY VARCHAR2,
198 x_org_code IN OUT NOCOPY VARCHAR2,
199 --togeorge 10/03/2000
200 -- added to bring oke line info during post query.
201 x_oke_contract_header_id IN NUMBER default null,
202 x_oke_contract_line_id IN NUMBER default null,
203 x_oke_contract_line_num IN OUT NOCOPY VARCHAR2,
204 x_oke_contract_deliverable_id IN NUMBER default null,
205 x_oke_contract_deliverable_num IN OUT NOCOPY VARCHAR2
206 ) IS
207
208 x_progress VARCHAR2(3):=NULL;
209
210 -- As part of hr_location changes bug# 2393886
211
212 cursor c1 is
213 select location_code from hr_locations
214 where nvl(inventory_organization_id,x_ship_to_org_id) = x_ship_to_org_id
215 and location_id = x_deliver_to_location_id
216 UNION
217 select (substrb(rtrim(address1)||'-'||rtrim(city),1,20)) location_code from hz_locations
218 where location_id = x_deliver_to_location_id ;
219
220 /* Bug 1323765
221 and nvl(inactive_date, trunc(sysdate + 1)) > trunc(sysdate);
222 */
223
224 --< Bug 3370335 > Remove cursor c2 for employee name.
225
226 cursor c3 is
227 --Bug 610292 ecso 1/19/97
228 --Owing to PJM changes,
229 --use new view PJM_PROJECTS_V
230 --instead of MTL_PROJECTS_V
231 -- select project_number
232 -- from PJM_PROJECTS_V
233 -- where project_id = x_project_id;
234 --Bug# 1223698 - To select Closed Projects during Query
235 --Copied the same logic as in MRP_GET_PROJECT.PROJECT
236 select segment1
237 from pa_projects_all
238 where project_id = x_project_id
239 union
240 select project_number
241 from mrp_seiban_numbers
242 where project_id = x_project_id;
243
244
245 cursor c4 is
246 -- select task_number
247 -- from pa_tasks_expend_v
248 -- where project_id = x_project_id
249 -- and task_id = x_task_id;
250 -- Bug# 1223698 - To select Non-Chargeable Task during Query
251 -- Copied the same logic as in MRP_GET_PROJECT.TASK
252 -- Copying the same logic as in
253 select task_number
254 -- from pa_tasks Bug 16863607
255 from pa_tasks_expend_v
256 where task_id = x_task_id;
257
258 cursor c5 is
259 select name
260 from HR_ORGANIZATION_UNITS --bug 3342946 (used to be pa_organizations_expend_v)
261 where organization_id = x_org_id;
262 --togeorge 10/03/2000
263 --added cursors for oke info.
264 cursor c6 is
265 select line_number
266 from okc_k_lines_b
267 --Bug# 1633032, togeorge 02/21/2001
268 -- Just line id enough to pick the line num.
269 -- where dnz_chr_id =x_oke_contract_header_id
270 -- and id = x_oke_contract_line_id;
271 where id = x_oke_contract_line_id;
272
273 cursor c7 is
274 select deliverable_num
275 from oke_k_deliverables_b
276 where k_line_id = x_oke_contract_line_id
277 and deliverable_id = x_oke_contract_deliverable_id;
278
279 BEGIN
280
281 x_progress := '001';
282
283 IF x_deliver_to_location_id IS NOT NULL THEN
284 open c1;
285 fetch c1 into x_deliver_to_location;
286 close c1;
287 END IF;
288
289 IF x_deliver_to_person_id IS NOT NULL THEN
290 x_progress := '002';
291 --< Bug 3370335 Start >
292 -- Use utility procedure to get the emp name directly from base table
293 x_deliver_to_person :=
294 PO_EMPLOYEES_SV.get_emp_name( x_person_id => x_deliver_to_person_id );
295 --< Bug 3370335 End >
296 END IF;
297
298 /* If the PROJECT_ID is not null, then if the destination_type='INVENTORY'
299 then get the project_reference_enabled and the project_control_level
300 flags for Project Manugacturing
301 */
302 IF x_project_id IS NOT NULL THEN
303
304 IF x_destination_type_code='EXPENSE' THEN
305
306 x_progress := '003';
307 open c3;
308 fetch c3 into x_project_num;
309 close c3;
310
311 open c4;
312 fetch c4 into x_task_num;
313 close c4;
314
315 open c5;
316 fetch c5 into x_org_code;
317 close c5;
318
319 ELSIF x_destination_type_code IN ('INVENTORY','SHOP FLOOR') THEN
320
321 x_progress := '004';
322 open c3;
323 fetch c3 into x_project_num;
324 close c3;
325
326 IF x_task_id IS NOT NULL THEN
327 x_progress := '004';
328 open c4;
329 fetch c4 into x_task_num;
330 close c4;
331 END IF;
332
333 END IF;
334
335 END IF;
336
337
338 IF x_oke_contract_line_id is not null then
339
340 x_progress := '006';
341 open c6;
342 fetch c6 into x_oke_contract_line_num;
343 close c6;
344
345 IF x_oke_contract_deliverable_id is not null then
346 x_progress := '007';
347 open c7;
348 fetch c7 into x_oke_contract_deliverable_num;
349 close c7;
350 END IF;
351 END IF;
352
353
354
355 EXCEPTION
356 WHEN OTHERS THEN
357 --dbms_output.put_line('In Exception');
358 po_message_s.sql_error('post_query', x_progress, sqlcode);
359 RAISE;
360 END post_query;
361
362
363 /*===========================================================================
364
365 FUNCTION NAME: get_dest_type()
366
367 ===========================================================================*/
368
369 FUNCTION get_dest_type( x_destination_type_code VARCHAR2) return VARCHAR2 IS
370
371 x_progress VARCHAR2(3) := NULL;
372
373 x_dest_type VARCHAR2(40);
374
375 BEGIN
376
377 select displayed_field
378 into x_dest_type
379 from po_destination_types_all_v
380 where lookup_code = x_destination_type_code;
381
382 RETURN(x_dest_type);
383
384 EXCEPTION
385 WHEN OTHERS THEN
386 --dbms_output.put_line('In Exception');
387 po_message_s.sql_error('get_dest_type', x_progress, sqlcode);
388 RAISE;
389 END get_dest_type;
390
391
392 /*===========================================================================
393
394 PROCEDURE NAME: delete_distributions()
395
396 ===========================================================================*/
397
398 PROCEDURE delete_distributions(x_delete_id NUMBER,
399 x_delete_entity VARCHAR2) IS
400
401 x_progress VARCHAR2(3) := NULL;
402
403 BEGIN
404
405 x_progress := '010';
406 IF (X_delete_entity = 'RELEASE') THEN
407
408 delete po_distributions_all /*Bug6632095: using base table instead of view */
409 where line_location_id in
410 (select line_location_id
411 from po_line_locations_all
412 where po_release_id = x_delete_id);
413
414 ELSIF (X_delete_entity = 'SHIPMENT') THEN
415
416 delete po_distributions
417 where line_location_id = x_delete_id;
418
419 ELSIF (X_delete_entity = 'LINE') THEN
420
421 delete po_distributions
422 where line_location_id in
423 (select line_location_id
424 from po_line_locations
425 where po_line_id = x_delete_id
426 and shipment_type in ('STANDARD', 'PLANNED'));
427
428 ELSIF (X_delete_entity = 'HEADER') THEN
429
430 --<BUG 3230237 START>
431 --We can have encumbrance distributions tied to a BPA header.
432 --Allow distributions with distribution_type 'AGREEMENT' to be deleted.
433 DELETE PO_DISTRIBUTIONS_ALL /*Bug6632095: using base table instead of view */
434 WHERE po_header_id = x_delete_id
435 AND distribution_type in ('STANDARD', 'PLANNED', 'AGREEMENT');
436 --<BUG 3230237 END>
437
438 END IF;
439
440
441 EXCEPTION
442 WHEN OTHERS THEN
443 --dbms_output.put_line('In Exception');
444 po_message_s.sql_error('delete_distributions', x_progress, sqlcode);
445 RAISE;
446 END delete_distributions;
447
448 /*===========================================================================
449 PROCEDURE NAME: test_get_total_dist_qty
450 ===========================================================================*/
451 PROCEDURE test_get_total_dist_qty(X_po_line_location_id IN NUMBER) IS
452 X_total_quantitya NUMBER;
453 BEGIN
454 --dbms_output.put_line('before call');
455 po_dist_s.get_total_dist_qty(X_po_line_location_id, X_total_quantitya);
456
457
458 --dbms_output.put_line('after call');
459 --dbms_output.put_line(X_total_quantitya);
460 END test_get_total_dist_qty;
461 /*===========================================================================
462 PROCEDURE NAME: get_total_dist_qty
463 ===========================================================================*/
464 PROCEDURE get_total_dist_qty
465 (X_po_line_location_id IN NUMBER,
466 X_total_quantity IN OUT NOCOPY NUMBER) IS
467 X_progress varchar2(3) := '';
468 CURSOR C is
469 SELECT sum(POD.quantity_ordered)
470 FROM po_distributions POD
471 WHERE POD.po_distribution_id = X_po_line_location_id;
472 BEGIN
473 --dbms_output.put_line('Before open cursor');
474 if (X_po_line_location_id is not null) then
475 X_progress := '010';
476 OPEN C;
477 X_progress := '020';
478 FETCH C into X_total_quantity;
479 CLOSE C;
480 else
481 X_progress := '030';
482 po_message_s.sql_error('get_total_dist_qty', X_progress, sqlcode);
483 end if;
484 EXCEPTION
485 when others then
486 --dbms_output.put_line('In exception');
487 po_message_s.sql_error('get_total_dist_qty', X_progress, sqlcode);
488 END get_total_dist_qty;
489 /*===========================================================================
490 FUNCTION NAME: test_val_distribution_exists
491 ===========================================================================*/
492 PROCEDURE test_val_distribution_exists(X_po_line_location_id IN NUMBER) IS
493 X_val_dist BOOLEAN;
494 BEGIN
495 --dbms_output.put_line('before call');
496 X_val_dist := po_dist_s.val_distribution_exists(X_po_line_location_id);
497
498
499 --dbms_output.put_line('after call');
500 END test_val_distribution_exists;
501 /*===========================================================================
502 FUNCTION NAME: val_distribution_exists
503 ===========================================================================*/
504 FUNCTION val_distribution_exists
505 (X_po_line_location_id IN NUMBER) RETURN BOOLEAN IS
506 X_progress VARCHAR2(3) := '';
507 X_max_distribution_id NUMBER := '';
508 BEGIN
509 SELECT max(POD.po_distribution_id)
510 INTO X_max_distribution_id
511 FROM po_distributions POD
512 WHERE POD.line_location_id = X_po_line_location_id;
513 IF (X_max_distribution_id is null) THEN
514 --dbms_output.put_line('returned false');
515 return(FALSE);
516 ELSE
517 --dbms_output.put_line('returned true');
518 return(TRUE);
519 END IF;
520 EXCEPTION
521 when others then
522 --dbms_output.put_line('In exception');
523 po_message_s.sql_error('val_distribution_exists', X_progress, sqlcode);
524 END val_distribution_exists;
525
526 /*===================================================================
527
528 PROCEDURE NAME : performed_rcv_or_bill_activity (bug 4239813, 4239805)
529
530 =====================================================================*/
531 function performed_rcv_or_bill_activity(p_line_location_id IN NUMBER,
532 p_distribution_id IN NUMBER)
533 RETURN BOOLEAN IS
534 l_exists VARCHAR2(1);
535 Begin
536 l_exists :='N';
537 /* Find if any record for the shipment exists in rcv_transactions table */
538 begin
539 select 'Y'
540 into l_exists
541 from dual
542 where exists
543 (select 'rcv transaction records'
544 from rcv_transactions
545 where po_line_location_id = p_line_location_id);
546
547 exception
548 when no_data_found then
549 l_exists:= 'N';
550 when others then
551 raise;
552 end;
553
554 if (l_exists = 'Y') then
555 return (TRUE);
556 end if;
557
558 begin
559 select 'Y'
560 into l_exists
561 from dual
562 where exists
563 (select 'transaction interface records'
564 from rcv_transactions_interface
565 where po_line_location_id = p_line_location_id
566 and transaction_status_code = 'PENDING');
567
568 exception
569 when no_data_found then
570 l_exists:= 'N';
571 when others then
572 raise;
573 end;
574
575 if (l_exists = 'Y') then
576 return (TRUE);
577 end if;
578
579 /* trying to get all uncancelled, unreversed associated invoice distributions
580 * , if they exist disallow change of destination type.
581 */
582 begin
583 select 'Y'
584 into l_exists
585 from dual
586 where exists
587 (select 'Active invoice distributions'
588 from ap_invoice_distributions
589 where po_distribution_id = p_distribution_id
590 and nvl(cancellation_flag,'N') <> 'Y'
591 and nvl(reversal_flag,'N') <> 'Y');
592
593 exception
594 when no_data_found then
595 l_exists:= 'N';
596 when others then
597 raise;
598 end;
599
600 if (l_exists = 'Y') then
601 return (TRUE);
602 end if;
603
604 return (FALSE);
605 end;
606 -----------------------------------------------------------------------------
607 --Start of Comments
608 --Name: validate_delete_distribution
609 --Pre-reqs:
610 -- Before calling this procedure one must call validate_delete_line_loc
611 -- to ensure that deletion of the line location is a valid action
612 --Modifies:
613 -- PO_LINES_ALL
614 -- PO_LINE_LOCATIONS_ALL
615 --Locks:
616 -- None
617 --Function:
618 -- Deletes the selected Line Location from the Database and
619 -- calls the pricing APIs to calculate the new price if a Standard PO
620 -- shipment with a source reference is deleted
621 --Parameters:
622 --IN:
623 --p_po_distribution_id
624 -- Distribution ID for the Po Distribution to be deleted
625 --p_line_loc_id
626 -- Line Location ID for the Po Shipment to be deleted
627 --p_doc_subtype
628 -- Document Sub type of the PO [STANDARD/BLANKET]
629 --p_approved_date
630 -- Date on which the document was last approved. Get it from the PO header.
631 --p_style_disp_name
632 -- Display Name of the document style
633 --OUT:
634 --x_message_text
635 -- Will hold the error message in case the header cannot be deleted
636 --Notes:
637 -- Rules: Do not allow deletion of a distribution which satisfies any of the following
638 -- condition
639 -- > Approved atleast once
640 -- > Created from an on-line Requisition
641 -- > Has been Delivered
642 -- > Has been Billed
643 -- > is Encumbered
644 -- > if only distribution
645 --End of Comments
646 -----------------------------------------------------------------------------
647 PROCEDURE validate_delete_distribution(p_po_distribution_id IN NUMBER
648 ,p_line_loc_id IN NUMBER
649 ,p_approved_date IN VARCHAR2
650 ,p_style_disp_name IN VARCHAR2
651 ,x_message_text OUT NOCOPY VARCHAR2) IS
652 l_creation_date po_distributions_all.creation_date%TYPE;
653 l_req_distribution_id po_distributions_all.req_distribution_id%TYPE;
654 l_quantity_delivered po_distributions_all.quantity_delivered%TYPE;
655 l_amount_delivered po_distributions_all.amount_delivered%TYPE;
656 l_encumbered_flag po_distributions_all.encumbered_flag%TYPE;
657 l_quantity_billed po_distributions_all.quantity_billed%TYPE;
658 l_amount_billed po_distributions_all.amount_billed%TYPE;
659 --BUG 5553581
660 l_po_header_id po_distributions_all.po_header_id%TYPE;
661 l_is_complex_work_po BOOLEAN;
662 l_dummy NUMBER := 0;
663 d_pos NUMBER := 0;
664 l_api_name CONSTANT VARCHAR2(30) := 'validate_delete_distribution';
665 d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_DISTRIBUTIONS_SV.validate_delete_distribution';
666
667 BEGIN
668
669 IF (PO_LOG.d_proc) THEN
670 PO_LOG.proc_begin(d_module); PO_LOG.proc_begin(d_module,'p_line_loc_id', p_line_loc_id); PO_LOG.proc_begin(d_module,'p_style_disp_name', p_style_disp_name);
671 END IF;
672
673 SELECT creation_date,
674 req_distribution_id,
675 po_header_id,
676 nvl(quantity_delivered, 0),
677 nvl(amount_delivered, 0),
678 nvl(encumbered_flag, 'N'),
679 nvl(quantity_billed, 0),
680 nvl(amount_billed, 0)
681 INTO l_creation_date,
682 l_req_distribution_id,
683 l_po_header_id,
684 l_quantity_delivered,
685 l_amount_delivered,
686 l_encumbered_flag,
687 l_quantity_billed,
688 l_amount_billed
689 FROM po_distributions_all
690 WHERE po_distribution_id = p_po_distribution_id;
691
692 l_is_complex_work_po := PO_COMPLEX_WORK_PVT.is_complex_work_po(l_po_header_id);
693
694 d_pos := 10;
695 --Created from an on-line Requisition
696 IF l_req_distribution_id IS NOT NULL
697 THEN
698 IF(l_is_complex_work_po = FALSE) --BUG 5553581
699 THEN
700 x_message_text := PO_CORE_S.get_translated_text('PO_PO_DEL_DIST_ONLINE_REQ_NA');
701 RAISE PO_CORE_S.G_EARLY_RETURN_EXC;
702 ELSE
703 BEGIN --5553581
704 select po_distribution_id
705 into l_dummy
706 FROM po_distributions_all pod
707 WHERE pod.line_location_id = p_line_loc_id
708 AND pod.req_distribution_id = l_req_distribution_id;
709
710 x_message_text := PO_CORE_S.get_translated_text('PO_PO_DEL_DIST_ONLINE_REQ_NA');
711 RAISE PO_CORE_S.G_EARLY_RETURN_EXC;
712 EXCEPTION
713 WHEN TOO_MANY_ROWS THEN
714 --If there are multiple rows then we can go ahead and delete
715 NULL;
716 END;
717 END IF;
718 END IF;
719
720 d_pos := 20;
721 -- Has been Delivered
722 IF l_quantity_delivered > 0
723 OR l_amount_delivered > 0
724 THEN
725 x_message_text := PO_CORE_S.get_translated_text('PO_PO_DELETE_DEL_DIST_NA');
726 RAISE PO_CORE_S.G_EARLY_RETURN_EXC;
727 END IF;
728
729 d_pos := 30;
730 --Has been Billed
731 IF l_quantity_billed > 0
732 OR l_amount_billed > 0
733 THEN
734 x_message_text := PO_CORE_S.get_translated_text('PO_PO_DELETE_DIST_BILLED_NA');
735 RAISE PO_CORE_S.G_EARLY_RETURN_EXC;
736 END IF;
737
738 d_pos := 40;
739 -- is Encumbered
740 IF l_encumbered_flag = 'Y'
741 THEN
742 x_message_text := PO_CORE_S.get_translated_text('PO_PO_USE_CANCEL_ON_ENCUMB_PO');
743 RAISE PO_CORE_S.G_EARLY_RETURN_EXC;
744 END IF;
745
746 d_pos := 50;
747 -- Approved atleast once
748 IF (l_creation_date <= p_approved_date)
749 THEN
750 x_message_text := PO_CORE_S.get_translated_text('PO_PO_USE_CANCEL_ON_APRVD_PO3');
751 RAISE PO_CORE_S.G_EARLY_RETURN_EXC;
752 END IF;
753
754 d_pos := 60;
755 -- If there is only one single viable distribution then we should not allow to
756 -- to delete the distribution
757 BEGIN
758 select po_distribution_id
759 into l_dummy
760 FROM po_distributions_all pod
761 WHERE pod.line_location_id = p_line_loc_id;
762
763 x_message_text := PO_CORE_S.get_translated_text('PO_CANT_DELETE_ONLY_DIST');
764 RAISE PO_CORE_S.G_EARLY_RETURN_EXC;
765 EXCEPTION
766 WHEN TOO_MANY_ROWS THEN
767 --If there are multiple rows then we can go ahead and delete
768 NULL;
769 END;
770 EXCEPTION
771 WHEN PO_CORE_S.G_EARLY_RETURN_EXC THEN
772 NULL;
773 IF (PO_LOG.d_stmt) THEN
774 PO_LOG.stmt(d_module,d_pos,'x_message_text',x_message_text);
775 END IF;
776 WHEN OTHERS THEN
777 FND_MSG_PUB.add_exc_msg('PO_DISTRIBUTIONS_SV', l_api_name||':'||d_pos);
778 IF PO_LOG.d_exc THEN
779 PO_LOG.exc(d_module,d_pos,'Unhandled Exception in' || d_module);
780 END IF;
781 RAISE;
782 END validate_delete_distribution;
783
784 END po_distributions_sv;
[Home] [Help]