1 PACKAGE BODY PO_LINES_SV as
2 /* $Header: POXPOL1B.pls 120.10.12020000.2 2013/02/10 14:39:08 vegajula ship $ */
3
4 /*============================= PO_LINES_SV ===============================*/
5
6
7 /*===========================================================================
8
9 PROCEDURE NAME: delete_line()
10
11 ===========================================================================*/
12
13 PROCEDURE delete_line(X_type_lookup_code IN VARCHAR2,
14 X_po_line_id IN NUMBER,
15 X_row_id IN VARCHAR2,
16 p_skip_validation IN VARCHAR2) --<HTML Agreements R12>)
17 IS
18 x_progress VARCHAR2(3) := '';
19 X_allow_delete VARCHAR2(1) := '';
20 X_po_line_num NUMBER := '';
21 X_po_header_id NUMBER := '';
22 l_type_name po_document_types.type_name%type; --Bug 3453216;
23 BEGIN
24 x_progress := '010';
25 --<HTML Agreements R12 Start>
26 -- If the calling source is HTML then we need not do the validations as we
27 -- would have already performed these validations in
28 -- PO_LINES_SV.check_deletion_allowed
29 IF p_skip_validation = 'Y' THEN
30 x_allow_delete := 'Y';
31 ELSE
32 /*
33 ** Get additional line information for delete verification
34 */
35 SELECT line_num,
36 po_header_id
37 INTO X_po_line_num,
38 X_po_header_id
39 FROM po_lines pol
40 WHERE po_line_id = X_po_line_id;
41
42 /*
43 ** Verify a line can be deleted
44 */
45 IF (X_type_lookup_code = 'RFQ') THEN
46 /*
47 ** verify rfq line can be deleted.
48 */
49 po_rfqs_sv.val_line_delete (X_po_line_id,
50 X_po_header_id,
51 X_allow_delete);
52
53 ELSIF (X_type_lookup_code = 'QUOTATION') THEN
54 /*
55 ** verify quotation line can be deleted.
56 */
57 po_quotes_sv.val_line_delete(X_po_line_id,
58 X_po_line_num,
59 X_po_header_id,
60 X_allow_delete);
61
62 -- bug 424099
63 -- Check for Blanket PO
64
65 ELSIF (X_type_lookup_code IN ('STANDARD', 'PLANNED','BLANKET')) THEN
66 /*
67 ** verify PO line can be deleted.
68 */
69 --Bug 3453216. Added token values for the message
70 --'PO_PO_USE_CANCEL_ON_APRVD_PO3'. Deriving the token values here from
71 --X_type_lookup_code
72 Begin
73 IF X_type_lookup_code IN ('STANDARD','PLANNED') THEN
74 select type_name
75 into l_type_name
76 from po_document_types
77 where document_type_code = 'PO'
78 and document_subtype=X_type_lookup_code;
79 ELSIF X_type_lookup_code='BLANKET' THEN
80 select type_name
81 into l_type_name
82 from po_document_types
83 where document_type_code = 'PA'
84 and document_subtype='BLANKET';
85 END IF;
86 Exception when others THEN
87 l_type_name := X_type_lookup_code;
88 End;
89 po_lines_sv.val_line_delete(X_po_line_id => X_po_line_id,
90 X_allow_delete => X_allow_delete,
91 p_token => 'DOCUMENT_TYPE',
92 p_token_value => l_type_name);
93
94 ELSE
95 x_progress:= '020';
96 po_message_s.sql_error('document type is invalid', x_progress, sqlcode);
97
98 END IF;
99 END IF; --p_skip_validation = 'Y'
100 /*
101 ** If deletion is permitted, call the Lines table handler to delete row.
102 */
103 IF (x_allow_delete = 'Y') THEN
104
105 /* call the ATTACHMENTS PKG to delete all attachments*/
106 fnd_attached_documents2_pkg.delete_attachments('PO_LINES',
107 x_po_line_id,
108 '', '', '', '', 'Y');
109
110
111 /*
112 ** Delete all children of the selected line.
113 */
114
115 po_lines_sv.delete_children(X_type_lookup_code, X_po_line_id);
116 --dbms_output.put_line('after delete children');
117
118 --<Enhanced Pricing Start:>
119 PO_PRICE_ADJUSTMENTS_PKG.delete_price_adjustments
120 ( p_po_header_id => X_po_header_id
121 , p_po_line_id => X_po_line_id
122 );
123 --<Enhanced Pricing End>
124
125 /*
126 ** Delete the Line.
127 */
128
129 po_lines_pkg_sud.delete_row(X_row_id);
130 --dbms_output.put_line('after call to delete row');
131
132
133 END IF;
134
135 EXCEPTION
136
137 WHEN OTHERS THEN
138 --dbms_output.put_line('In exception');
139 po_message_s.sql_error('delete_line', x_progress, sqlcode);
140 raise;
141
142
143 END delete_line;
144
145 /*===========================================================================
146
147 PROCEDURE NAME: delete_all_lines
148
149 ===========================================================================*/
150 PROCEDURE delete_all_lines( X_po_header_id IN NUMBER
151 ,p_type_lookup_code IN VARCHAR2) IS
152
153 X_progress VARCHAR2(3) := '';
154 X_po_line_id NUMBER := '';
155 l_isucaenabled VARCHAR2(1) :='N';
156
157 CURSOR C_LINE is
158 SELECT po_line_id
159 FROM po_lines_all /*Bug6632095: using base table instead of view */
160 WHERE po_header_id = X_po_header_id;
161
162
163 BEGIN
164
165 X_progress := '010';
166
167 -- delete attachements associated with shipment.
168 OPEN C_LINE;
169
170 LOOP
171
172 FETCH C_LINE INTO x_po_line_id;
173 EXIT WHEN C_LINE%notfound;
174
175 fnd_attached_documents2_pkg.delete_attachments('PO_LINES',
176 x_po_line_id,
177 '', '', '', '', 'Y');
178
179 --<HTML Agreements R12 Start>
180 --Delete the Price differentials entity type for the given Line
181 PO_PRICE_DIFFERENTIALS_PKG.del_level_specific_price_diff(
182 p_doc_level => PO_CORE_S.g_doc_level_LINE
183 ,p_doc_level_id => x_po_line_id);
184 --<HTML Agreements R12 End>
185 END LOOP;
186
187 CLOSE C_LINE;
188
189 X_progress := '015';
190
191 --<Unified Catalog R12: Start>
192 PO_ATTRIBUTE_VALUES_PVT.delete_attributes_for_header
193 (
194 p_doc_type => p_type_lookup_code
195 , p_po_header_id => x_po_header_id
196 );
197 --<Unified Catalog R12: End>
198
199 --Enhanced Pricing
200 PO_PRICE_ADJUSTMENTS_PKG.delete_price_adjustments(p_po_header_id => X_po_header_id );
201
202 --dbms_output.put_line('Before delete all lines');
203 DELETE FROM PO_LINES_ALL /*Bug6632095: using base table instead of view */
204 WHERE po_header_id = X_po_header_id;
205 --Delete Line UCAS IF UCA Enabled Begin.
206
207 SELECT Nvl(po_core_s.Retrieveoptionvalue(org_id,
208 po_core_s.g_undef_cont_act_col), 'N')
209 INTO l_isucaenabled
210 FROM po_headers_all
211 WHERE po_header_id = X_po_header_id;
212
213 IF l_isucaenabled = 'Y' THEN
214 DELETE FROM po_line_ucas
215 WHERE po_header_id = X_po_header_id;
216 END IF;
217 --Delete Line UCAS If UCA Enabled End.
218
219
220 EXCEPTION
221 WHEN OTHERS THEN
222 --dbms_output.put_line('In exception');
223 po_message_s.sql_error('delete_all_lines', X_progress, sqlcode);
224 raise;
225 END delete_all_lines;
226
227 /*===========================================================================
228
229 PROCEDURE NAME: delete_children
230
231 ===========================================================================*/
232 PROCEDURE delete_children(X_type_lookup_code IN VARCHAR2,
233 X_po_line_id IN NUMBER) IS
234
235 X_progress VARCHAR2(3) := '';
236 l_isucaenabled VARCHAR2(1) :='N';
237 BEGIN
238
239 IF (X_type_lookup_code IN ('PLANNED', 'STANDARD')) THEN
240
241 /* Delete Distributions for a PO */
242 X_progress := '010';
243 --dbms_output.put_line('Before Delete All Distributions');
244 po_distributions_sv.delete_distributions(X_po_line_id, 'LINE');
245
246 END IF;
247
248 X_progress := '020';
249 --dbms_output.put_line('In call to delete children');
250 po_shipments_sv4.delete_all_shipments(X_po_line_id, 'LINE',
251 X_type_lookup_code);
252
253 X_progress := '030';
254 --<Unified Catalog R12: Start>
255 -- Deleting the Attribute values associated with the BLANKET or QUOTATION
256 IF X_type_lookup_code IN ('BLANKET', 'QUOTATION') THEN
257 PO_ATTRIBUTE_VALUES_PVT.delete_attributes
258 (
259 p_doc_type => x_type_lookup_code
260 , p_po_line_id => x_po_line_id
261 );
262 END IF;
263 --<Unified Catalog R12: End>
264
265 --<HTML Agreements R12 Start>
266 --Delete the Price differentials entity type for the given Line
267 PO_PRICE_DIFFERENTIALS_PKG.del_level_specific_price_diff(
268 p_doc_level => PO_CORE_S.g_doc_level_LINE
269 ,p_doc_level_id => x_po_line_id);
270 --<HTML Agreements R12 End>
271
272 --Delete Line UCAS IF UCA Enabled Begin.
273
274 SELECT Nvl(po_core_s.Retrieveoptionvalue(org_id,
275 po_core_s.g_undef_cont_act_col), 'N')
276 INTO l_isucaenabled
277 FROM po_lines
278 WHERE po_line_id = x_po_line_id;
279
280 IF l_isucaenabled = 'Y' THEN
281 DELETE FROM po_line_ucas
282 WHERE po_line_id = x_po_line_id;
283 END IF;
284 --Delete Line UCAS If UCA Enabled End.
285
286 EXCEPTION
287 WHEN OTHERS THEN
288 --dbms_output.put_line('In exception');
289 po_message_s.sql_error('delete_children', X_progress, sqlcode);
290 raise;
291 END delete_children;
292 /*===========================================================================
293
294 PROCEDURE NAME: test_val_line_delete()
295
296 ===========================================================================*/
297
298 PROCEDURE test_val_line_delete(X_po_line_id IN NUMBER) IS
299
300 X_allow_delete VARCHAR2(1) := '';
301
302 BEGIN
303
304 --dbms_output.put_line('Before_call');
305
306 po_lines_sv.val_line_delete(X_po_line_id, X_allow_delete);
307
308 --dbms_output.put_line('After call');
309 --dbms_output.put_line('Allow Delete = '||X_allow_delete);
310
311 END test_val_line_delete;
312
313
314 /*===========================================================================
315
316 PROCEDURE NAME: check_line_deletion_allowed()
317
318 ===========================================================================*/
319 --
320 -- This is the same procedure as what is val_line_delete, but the exceptions are not
321 -- raised. The are returned in x_message_text.
322 --
323 PROCEDURE check_line_deletion_allowed
324 (X_po_line_id IN NUMBER,
325 X_allow_delete IN OUT NOCOPY VARCHAR2,
326 p_token IN VARCHAR2,
327 p_token_value IN VARCHAR2,
328 x_message_text OUT NOCOPY VARCHAR2)
329 IS
330 x_progress VARCHAR2(3) := '';
331
332 l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
333 l_some_dists_reserved_flag VARCHAR2(1);
334
335 l_approved_flag PO_HEADERS_ALL.approved_flag%type;
336 l_po_header_id PO_HEADERS_ALL.po_header_id%type;
337 l_approved_date PO_HEADERS_ALL.approved_date%type;
338
339 l_line_creation_date PO_LINES_ALL.creation_date%type;
340 BEGIN
341 x_progress := '010';
342
343 -- Get type lookup code and approval status. Variables are used in later checks
344 SELECT POH.type_lookup_code,
345 POH.approved_flag,
346 POH.po_header_id,
347 POH.approved_date,
348 POL.creation_date
349 INTO l_type_lookup_code,
350 l_approved_flag,
351 l_po_header_id,
352 l_approved_date,
353 l_line_creation_date
354 FROM po_headers_all POH,
355 po_lines_all POL
356 WHERE POH.po_header_id = POL.po_header_id
357 AND POL.po_line_id = X_po_line_id;
358
359 -- Following check is in key_delrec and not in val_line_delete
360 -- Therefore, it was not included in the original revision of this method
361 --<HTML Agreements R12 Start>
362 -- Removing checks mentioned below and straight away checking if the line was
363 -- atleast approved once. We wont allow deletion of the line if it has been part
364 -- of an Approved Document.
365 -- These checks below are NOT DONE ANY MORE
366 -- For blanket PO's, do the following checks:
367 -- 1) If the document status has been approved, do not allow user to delete line
368 -- 2) If the document is in requires reapproval state, do not allow deletion if:
369 -- - If the line under consideration has been archived already OR
370 -- - Any open releases exist on the document. We have to do this second check
371 -- because a document in requires re-approval status is not necessarily
372 -- archived. This happens if the set up is for archive on print.
373 --<HTML Agreements R12 End>
374
375 IF (l_type_lookup_code = 'BLANKET') THEN
376 IF (l_approved_flag = 'Y') THEN
377
378 X_allow_delete := 'N';
379
380 ELSIF (l_approved_flag = 'R') THEN
381
382 --<HTML Agreements R12 Start>
383 --Modified the above mentioned checks for Blanket Agreements in Version 120.2
384 IF (l_approved_date IS NOT NULL
385 AND l_line_creation_date <= l_approved_date) THEN
386 X_allow_delete := 'N';
387 END IF;
388 --<HTML Agreements R12 End>
389
390 END IF; -- if l_approved_flag = 'Y'
391 END IF; -- if l_type_lookup_code = 'BLANKET'
392
393 -- If any checks have failed so far, there's no need to do further checks
394 -- Fill out message text and skip to the end of this procedure
395 IF (X_allow_delete = 'N') THEN
396 x_message_text := PO_CORE_S.get_translated_text(
397 'PO_PO_USE_CANCEL_ON_APRVD_PO3',
398 p_token,
399 p_token_value);
400
401 RAISE PO_CORE_S.G_EARLY_RETURN_EXC;
402 END IF;
403
404 --
405 -- Check to see if the Purchase Order line has approved or
406 -- previously approved shipments.
407 -- If it does NOT, verify the shipments are not encumbered.
408 -- If it does, put a message on the stack
409 --
410 SELECT MAX('N')
411 INTO X_allow_delete
412 FROM po_line_locations pll
413 WHERE pll.po_line_id = X_po_line_id
414 AND pll.approved_flag IN ('Y','R');
415
416 IF (X_allow_delete is NULL) THEN
417
418 --
419 -- Check to see if the Purchase Order line has encumbered shipments.
420 -- If it does NOT, allow deletion.
421 -- If it does, put the appropriate message on the stack
422 --
423
424 SELECT POH.type_lookup_code
425 INTO l_type_lookup_code
426 FROM PO_HEADERS_ALL POH,
427 PO_LINES_ALL POL
428 WHERE POH.po_header_id = POL.po_header_id
429 AND POL.po_line_id = x_po_line_id;
430
431 X_allow_delete := 'Y';
432
433 IF (l_type_lookup_code IN ('STANDARD','PLANNED')) THEN
434
435 PO_CORE_S.are_any_dists_reserved(
436 p_doc_type => PO_CORE_S.g_doc_type_PO
437 , p_doc_level => PO_CORE_S.g_doc_level_LINE
438 , p_doc_level_id => x_po_line_id
439 , x_some_dists_reserved_flag => l_some_dists_reserved_flag
440 );
441
442 IF (l_some_dists_reserved_flag = 'N') THEN
443 x_allow_delete := 'Y';
444 ELSE
445 x_allow_delete := 'N';
446 x_message_text := PO_CORE_S.get_translated_text(
447 'PO_PO_USE_CANCEL_ON_ENCUMB_PO');
448 END IF;
449 END IF;
450
451
452 ELSE
453 x_message_text := PO_CORE_S.get_translated_text(
454 'PO_PO_USE_CANCEL_ON_APRVD_PO3',
455 p_token,
456 p_token_value);
457 END IF;
458
459
460 EXCEPTION
461 WHEN PO_CORE_S.G_EARLY_RETURN_EXC THEN
462 NULL;
463
464 WHEN NO_DATA_FOUND then
465 -- There are no shipments for the given line
466 X_allow_delete := 'Y';
467
468 WHEN OTHERS THEN
469 po_message_s.sql_error('val_delete', x_progress, sqlcode);
470 raise;
471 END check_line_deletion_allowed;
472
473
474
475 /*===========================================================================
476
477 PROCEDURE NAME: val_line_delete()
478
479 ===========================================================================*/
480 /*
481 ** this val line delete is specific to purchase orders.
482 */
483 PROCEDURE val_line_delete
484 (X_po_line_id IN NUMBER,
485 X_allow_delete IN OUT NOCOPY VARCHAR2,
486 p_token IN VARCHAR2, -- Bug 3453216
487 p_token_value IN VARCHAR2) -- Bug 3453216
488 IS
489 x_progress VARCHAR2(3) := '';
490
491 --<Encumbrance FPJ>
492 l_type_lookup_code PO_HEADERS_ALL.type_lookup_code%TYPE;
493 l_some_dists_reserved_flag VARCHAR2(1);
494
495 BEGIN
496 x_progress := '010';
497
498 /*
499 ** Check to see if the Purchase Order line has approved or
500 ** previously approved shipments.
501 ** If it does NOT, verify the shipments are not encumbered.
502 ** If it does, display message and prevent deletion.
503 */
504 SELECT MAX('N')
505 INTO X_allow_delete
506 FROM po_line_locations pll
507 WHERE pll.po_line_id = X_po_line_id
508 AND pll.approved_flag IN ('Y','R');
509
510 IF (X_allow_delete is NULL) THEN
511
512 /*
513 ** Check to see if the Purchase Order line has encumbered shipments.
514 ** If it does NOT, allow deletion.
515 ** If it does, display message and prevent deletion.
516 */
517
518 --<Encumbrance FPJ START>
519 -- Only check for reserved distributions for Standard and Planned PO lines.
520
521 SELECT POH.type_lookup_code
522 INTO l_type_lookup_code
523 FROM PO_HEADERS_ALL POH
524 , PO_LINES_ALL POL
525 WHERE POH.po_header_id = POL.po_header_id
526 AND POL.po_line_id = x_po_line_id
527 ;
528
529 --Bug 8611806: Check for GBPA. If this line has been referred in SPO, do not allow delete.
530
531 IF(l_type_lookup_code='BLANKET') THEN
532 SELECT Max('N')
533 INTO X_allow_delete
534 FROM po_lines_all pol
535 WHERE pol.from_line_id = X_po_line_id ;
536
537 IF ( X_allow_delete = 'N' ) THEN
538 po_message_s.app_error('PO_PO_USE_CANCEL_ON_APRVD_PO3',p_token,p_token_value);
539 RETURN ;
540 END IF ;
541 END IF;
542
543 -- Bug 3320400,
544 -- Should initialize X_allow_delete for document other than
545 -- Standard/Planned PO, otherwise, val_line_delete will return NULL.
546 X_allow_delete := 'Y';
547
548 IF (l_type_lookup_code IN ('STANDARD','PLANNED')) THEN
549
550 PO_CORE_S.are_any_dists_reserved(
551 p_doc_type => PO_CORE_S.g_doc_type_PO
552 , p_doc_level => PO_CORE_S.g_doc_level_LINE
553 , p_doc_level_id => x_po_line_id
554 , x_some_dists_reserved_flag => l_some_dists_reserved_flag
555 );
556
557 IF (l_some_dists_reserved_flag = 'N') THEN
558 x_allow_delete := 'Y';
559 ELSE
560 x_allow_delete := 'N';
561 po_message_s.app_error('PO_PO_USE_CANCEL_ON_ENCUMB_PO');
562 END IF;
563 END IF;
564 --<Encumbrance FPJ END>
565
566
567 ELSE
568 --Bug 3453216. Updated the message name and set the token values
569 po_message_s.app_error('PO_PO_USE_CANCEL_ON_APRVD_PO3',p_token,p_token_value);
570 END IF;
571
572 --dbms_output.put_line('Allow delete = '||X_allow_delete);
573
574 EXCEPTION
575 when no_data_found then
576
577 /* There are no shipments for the given line */
578 X_allow_delete := 'Y';
579
580 WHEN OTHERS THEN
581 --dbms_output.put_line('In VAL exception');
582 po_message_s.sql_error('val_delete', x_progress, sqlcode);
583 raise;
584 END val_line_delete;
585
586 /*===========================================================================
587
588 PROCEDURE NAME: val_update()
589
590 ===========================================================================*/
591
592 PROCEDURE val_update
593 (X_po_line_id IN NUMBER,
594 X_quantity_ordered IN NUMBER) IS
595
596 X_progress VARCHAR2(3) := '';
597 X_entity_level VARCHAR2(25) := 'LINE';
598 X_quantity_released NUMBER := '';
599 X_ordered_lt_released VARCHAR2(1) := '';
600
601 BEGIN
602
603 X_progress := '010';
604 /*
605 ** The client side will call this procedure only if the type lookup code
606 ** is either PLANNED or BLANKET.
607 */
608 --po_shipments_sv.val_sched_released_qty(X_entity_level,
609 -- X_po_line_id,
610 -- '',
611 -- '',
612 -- X_quantity_ordered,
613 -- X_quantity_released,
614 -- X_ordered_lt_released);
615 /*
616 ** DEBUG: Kim is changing the function called above to a procedure,
617 ** passing back X_quantity_released and <I assume> X_ordered_lt_released.
618 ** need to verify X_ordered_lt_released.
619 */
620
621 X_progress := '020';
622 --dbms_output.put_line ('after call to val_sched_release_qty');
623
624 /*
625 ** If the quantity ordered is less than the quantity released, a message
626 ** is displayed.
627 */
628 IF (X_ordered_lt_released = 'Y') THEN
629 po_message_s.app_error('PO_PO_REL_EXCEEDS_QTY',
630 'SCHEDULED_QTY', TO_CHAR(X_quantity_released));
631 END IF;
632
633 --dbms_output.put_line ('Ordered < Released = '||X_ordered_lt_released);
634 --dbms_output.put_line ('Quantity released = '||X_quantity_released);
635
636 EXCEPTION
637 WHEN OTHERS THEN
638 po_message_s.sql_error('val_update', x_progress, sqlcode);
639 raise;
640 END val_update;
641
642 /*===========================================================================
643
644 PROCEDURE NAME: test_val_approval_status
645
646 ===========================================================================*/
647 PROCEDURE test_val_approval_status
648 (X_po_line_id IN NUMBER,
649 X_type_lookup_code IN VARCHAR2,
650 X_unit_price IN NUMBER,
651 X_line_num IN NUMBER,
652 X_item_id IN NUMBER,
653 X_item_description IN VARCHAR2,
654 X_quantity IN NUMBER,
655 X_unit_meas_lookup_code IN VARCHAR2,
656 X_from_header_id IN NUMBER,
657 X_from_line_id IN NUMBER,
658 X_hazard_class_id IN NUMBER,
659 X_vendor_product_num IN VARCHAR2,
660 X_un_number_id IN NUMBER,
661 X_note_to_vendor IN VARCHAR2,
662 X_item_revision IN VARCHAR2,
663 X_category_id IN NUMBER,
664 X_price_type_lookup_code IN VARCHAR2,
665 X_not_to_exceed_price IN NUMBER,
666 X_quantity_committed IN NUMBER,
667 X_committed_amount IN NUMBER,
668 p_contract_id IN NUMBER -- <GC FPJ>
669 ) IS
670
671 X_expiration_date DATE;
672 BEGIN
673
674 --dbms_output.put_line('before call');
675
676 IF po_lines_sv.val_approval_status
677 (X_po_line_id ,
678 X_type_lookup_code ,
679 X_unit_price ,
680 X_line_num ,
681 X_item_id ,
682 X_item_description ,
683 X_quantity ,
684 X_unit_meas_lookup_code,
685 X_from_header_id ,
686 X_from_line_id ,
687 X_hazard_class_id ,
688 X_vendor_product_num ,
689 X_un_number_id ,
690 X_note_to_vendor ,
691 X_item_revision ,
692 X_category_id ,
693 X_price_type_lookup_code,
694 X_not_to_exceed_price ,
695 X_quantity_committed ,
696 X_committed_amount,
697 X_expiration_date,
698 p_contract_id -- <GC FPJ>
699 ) THEN
700 --dbms_output.put_line('TRUE');
701 null;
702 ELSE
703 null;
704 --dbms_output.put_line('FALSE');
705 END IF;
706
707 END test_val_approval_status;
708
709
710 /*===========================================================================
711
712 FUNCTION NAME: val_approval_status()
713
714 ===========================================================================*/
715
716 FUNCTION val_approval_status
717 (X_po_line_id IN NUMBER,
718 X_type_lookup_code IN VARCHAR2,
719 X_unit_price IN NUMBER,
720 X_line_num IN NUMBER,
721 X_item_id IN NUMBER,
722 X_item_description IN VARCHAR2,
723 X_quantity IN NUMBER,
724 X_unit_meas_lookup_code IN VARCHAR2,
725 X_from_header_id IN NUMBER,
726 X_from_line_id IN NUMBER,
727 X_hazard_class_id IN NUMBER,
728 X_vendor_product_num IN VARCHAR2,
729 X_un_number_id IN NUMBER,
730 X_note_to_vendor IN VARCHAR2,
731 X_item_revision IN VARCHAR2,
732 X_category_id IN NUMBER,
733 X_price_type_lookup_code IN VARCHAR2,
734 X_not_to_exceed_price IN NUMBER,
735 X_quantity_committed IN NUMBER,
736 X_committed_amount IN NUMBER,
737 X_expiration_date IN DATE,
738 p_contract_id IN NUMBER, -- <GC FPJ>
739 -- <SERVICES FPJ START>
740 X_contractor_first_name IN VARCHAR2 default null,
741 X_contractor_last_name IN VARCHAR2 default null,
742 X_assignment_start_date IN DATE default null,
743 X_amount_db IN NUMBER default null
744 -- <SERVICES FPJ END>
745 ) RETURN BOOLEAN IS
746
747 X_progress VARCHAR2(3) := '';
748 X_approval_status_changed VARCHAR2(1) := 'N';
749
750 BEGIN
751
752 X_progress := '010';
753
754 IF (X_type_lookup_code IN ('STANDARD', 'PLANNED')) THEN
755
756 --dbms_output.put_line('Unit Price = '||X_unit_price);
757 --dbms_output.put_line('Line Num = '||X_line_num);
758 --dbms_output.put_line('Item Id = '||X_item_id);
759 --dbms_output.put_line('Item Description = '||X_item_description);
760 --dbms_output.put_line('Quantity = '||X_quantity);
761 --dbms_output.put_line('Unit of Measure = '||X_unit_meas_lookup_code);
762 --dbms_output.put_line('From PO Id = '||X_from_header_id);
763 --dbms_output.put_line('From Line Id = '||X_from_line_id);
764 --dbms_output.put_line('Hazard Class Id = '||X_hazard_class_id);
765 --dbms_output.put_line('Contract Num = '||X_contract_num);
766 --dbms_output.put_line('Vendor Product Num = '||X_vendor_product_num);
767 --dbms_output.put_line('UN Num Id = '||X_un_number_id);
768 --dbms_output.put_line('Note To Vendor = '||X_note_to_vendor);
769 --dbms_output.put_line('Item Revision = '||X_item_revision);
770 --dbms_output.put_line('Category Id = '||X_category_id);
771 --dbms_output.put_line('Price Type = '||X_price_type_lookup_code);
772 --dbms_output.put_line('Not to Exceed Price = '||X_not_to_exceed_price);
773
774 SELECT 'Y'
775 INTO X_approval_status_changed
776 FROM po_lines pol
777 WHERE pol.po_line_id = X_po_line_id
778 AND ((pol.unit_price <> X_unit_price)
779 OR (pol.unit_price is NULL
780 AND
781 X_unit_price is NOT NULL)
782 OR (pol.unit_price is NOT NULL
783 AND
784 X_unit_price is NULL)
785 OR (pol.line_num <> X_line_num)
786 OR (pol.line_num is NULL
787 AND
788 X_line_num IS NOT NULL)
789 OR (pol.line_num IS NOT NULL
790 AND
791 X_line_num IS NULL)
792 OR (pol.item_id <> X_item_id)
793 OR (pol.item_id is NULL
794 AND
795 X_item_id IS NOT NULL)
796 OR (pol.item_id IS NOT NULL
797 AND
798 X_item_id IS NULL)
799 OR (pol.item_description <> X_item_description)
800 OR (pol.item_description is NULL
801 AND
802 X_item_description IS NOT NULL)
803 OR (pol.item_description IS NOT NULL
804 AND
805 X_item_description IS NULL)
806 OR (pol.quantity <> X_quantity)
807 OR (pol.quantity is NULL
808 AND
809 X_quantity IS NOT NULL)
810 OR (pol.quantity IS NOT NULL
811 AND
812 X_quantity IS NULL)
813 OR (pol.unit_meas_lookup_code <> X_unit_meas_lookup_code)
814 OR (pol.unit_meas_lookup_code is NULL
815 AND
816 X_unit_meas_lookup_code IS NOT NULL)
817 OR (pol.unit_meas_lookup_code IS NOT NULL
818 AND
819 X_unit_meas_lookup_code IS NULL)
820 OR (pol.from_header_id <> X_from_header_id)
821 OR (pol.from_header_id is NULL
822 AND
823 X_from_header_id IS NOT NULL)
824 OR (pol.from_header_id IS NOT NULL
825 AND
826 X_from_header_id IS NULL)
827 OR (pol.from_line_id <> X_from_line_id)
828 OR (pol.from_line_id is NULL
829 AND
830 X_from_line_id IS NOT NULL)
831 OR (pol.from_line_id IS NOT NULL
832 AND
833 X_from_line_id IS NULL)
834 OR (pol.hazard_class_id <> X_hazard_class_id)
835 OR (pol.hazard_class_id is NULL
836 AND
837 X_hazard_class_id IS NOT NULL)
838 OR (pol.hazard_class_id IS NOT NULL
839 AND
840 X_hazard_class_id IS NULL)
841 -- <GC FPJ>
842 -- Remove the check for contract_num
843 OR (pol.vendor_product_num <> X_vendor_product_num)
844 OR (pol.vendor_product_num is NULL
845 AND
846 X_vendor_product_num IS NOT NULL)
847 OR (pol.vendor_product_num IS NOT NULL
848 AND
849 X_vendor_product_num IS NULL)
850 OR (pol.un_number_id <> X_un_number_id)
851 OR (pol.un_number_id is NULL
852 AND
853 X_un_number_id IS NOT NULL)
854 OR (pol.un_number_id IS NOT NULL
855 AND
856 X_un_number_id IS NULL)
857 OR (pol.note_to_vendor <> X_note_to_vendor)
858 OR (pol.note_to_vendor is NULL
859 AND
860 X_note_to_vendor IS NOT NULL)
861 OR (pol.note_to_vendor IS NOT NULL
862 AND
863 X_note_to_vendor IS NULL)
864 OR (pol.item_revision <> X_item_revision)
865 OR (pol.item_revision is NULL
866 AND
867 X_item_revision IS NOT NULL)
868 OR (pol.item_revision IS NOT NULL
869 AND
870 X_item_revision IS NULL)
871 OR (pol.category_id <> X_category_id)
872 OR (pol.category_id is NULL
873 AND
874 X_category_id IS NOT NULL)
875 OR (pol.category_id IS NOT NULL
876 AND
877 X_category_id IS NULL)
878 OR (pol.price_type_lookup_code <> X_price_type_lookup_code)
879 OR (pol.price_type_lookup_code is NULL
880 AND
881 X_price_type_lookup_code IS NOT NULL)
882 OR (pol.price_type_lookup_code IS NOT NULL
883 AND
884 X_price_type_lookup_code IS NULL)
885 OR (pol.not_to_exceed_price <> X_not_to_exceed_price)
886 OR (pol.not_to_exceed_price is NULL
887 AND
888 X_not_to_exceed_price IS NOT NULL)
889 OR (pol.not_to_exceed_price IS NOT NULL
890 AND
891 X_not_to_exceed_price IS NULL)
892 -- <GC FPJ START>
893 OR (pol.contract_id <> p_contract_id)
894 OR (pol.contract_id IS NOT NULL
895 AND
896 p_contract_id IS NULL)
897 OR (pol.contract_id IS NULL
898 AND
899 p_contract_id IS NOT NULL)
900 -- <GC FPJ END>
901 -- <SERVICES FPJ START>
902 OR (pol.contractor_first_name <> X_contractor_first_name)
903 OR (pol.contractor_first_name IS NOT NULL
904 AND
905 X_contractor_first_name IS NULL)
906 OR (pol.contractor_first_name IS NULL
907 AND
908 X_contractor_first_name IS NOT NULL)
909
910 OR (pol.contractor_last_name <> X_contractor_last_name)
911 OR (pol.contractor_last_name IS NOT NULL
912 AND
913 X_contractor_last_name IS NULL)
914 OR (pol.contractor_last_name IS NULL
915 AND
916 X_contractor_first_name IS NOT NULL)
917
918 OR (pol.start_date <> X_assignment_start_date)
919 OR (pol.start_date IS NOT NULL
920 AND
921 X_assignment_start_date IS NULL)
922 OR (pol.start_date IS NULL
923 AND
924 X_assignment_start_date IS NOT NULL)
925
926 OR (pol.expiration_date <> X_expiration_date)
927 OR (pol.expiration_date IS NOT NULL
928 AND
929 X_expiration_date IS NULL)
930 OR (pol.expiration_date IS NULL
931 AND
932 X_expiration_date IS NOT NULL)
933
934 OR (pol.amount <> X_amount_db)
935 OR (pol.amount IS NOT NULL
936 AND
937 X_amount_db IS NULL)
938 OR (pol.amount IS NULL
939 AND
940 X_amount_db IS NOT NULL)
941 -- <SERVICES FPJ END>
942 );
943
944
945 X_progress := '020';
946
947 ELSIF (X_type_lookup_code = 'BLANKET') THEN
948
949 --dbms_output.put_line('Unit Price = '||X_unit_price);
950 --dbms_output.put_line('Line Num = '||X_line_num);
951 --dbms_output.put_line('Item Id = '||X_item_id);
952 --dbms_output.put_line('Item Description = '||X_item_description);
953 --dbms_output.put_line('Unit of Measure = '||X_unit_meas_lookup_code);
954 --dbms_output.put_line('From PO Id = '||X_from_header_id);
955 --dbms_output.put_line('From Line Id = '||X_from_line_id);
956 --dbms_output.put_line('Hazard Class Id = '||X_hazard_class_id);
957 --dbms_output.put_line('Vendor Product Num = '||X_vendor_product_num);
958 --dbms_output.put_line('UN Num Id = '||X_un_number_id);
959 --dbms_output.put_line('Note To Vendor = '||X_note_to_vendor);
960 --dbms_output.put_line('Item Revision = '||X_item_revision);
961 --dbms_output.put_line('Category Id = '||X_category_id);
962 --dbms_output.put_line('Price Type = '||X_price_type_lookup_code);
963 --dbms_output.put_line('Not to Exceed Price = '||X_not_to_exceed_price);
964 --dbms_output.put_line('Quantity Committed = '||X_quantity_committed);
965 --dbms_output.put_line('Committed Amount = '||X_committed_amount);
966
967 X_progress := '030';
968
969
970 /* Bug 944367
971 The sql statement below was split to fix the error(bug426305)
972 PLS-00103: Parser stack overflow error.
973 ,BUT if the first sql statement returned no rows , then
974 it goes to the exception block of the procedure (though
975 it returns a true) and thereby not performing the
976 following sql statement and thereby returning the incorrect approval
977 status change which results in incorrect authorization status
978 of the Blanket agreement.
979 To fix the error encapsulating the first sql statment in a separate
980 plsql block and handling no data found exception to make sure that
981 it does process the second sql statement if the first returns no rows.
982
983 */
984 /* ER- 1260356 - Added expiration_date so that any change to expiration_date
985 at the Line Level of the blanket can also be archived */
986
987 BEGIN
988
989 SELECT 'Y'
990 INTO X_approval_status_changed
991 FROM po_lines pol
992 WHERE pol.po_line_id = X_po_line_id
993 AND ((pol.unit_price <> X_unit_price)
994 OR (pol.unit_price is NULL
995 AND
996 X_unit_price is NOT NULL)
997 OR (pol.unit_price is NOT NULL
998 AND
999 X_unit_price is NULL)
1000 OR (pol.line_num <> X_line_num)
1001 OR (pol.line_num is NULL
1002 AND
1003 X_line_num IS NOT NULL)
1004 OR (pol.line_num IS NOT NULL
1005 AND
1006 X_line_num IS NULL)
1007 OR (pol.item_id <> X_item_id)
1008 OR (pol.item_id is NULL
1009 AND
1010 X_item_id IS NOT NULL)
1011 OR (pol.item_id IS NOT NULL
1012 AND
1013 X_item_id IS NULL)
1014 OR (pol.item_description <> X_item_description)
1015 OR (pol.item_description is NULL
1016 AND
1017 X_item_description IS NOT NULL)
1018 OR (pol.item_description IS NOT NULL
1019 AND
1020 X_item_description IS NULL)
1021 OR (pol.quantity <> X_quantity)
1022 OR (pol.quantity is NULL
1023 AND
1024 X_quantity IS NOT NULL)
1025 OR (pol.quantity IS NOT NULL
1026 AND
1027 X_quantity IS NULL)
1028 OR (pol.unit_meas_lookup_code <> X_unit_meas_lookup_code)
1029 OR (pol.unit_meas_lookup_code is NULL
1030 AND
1031 X_unit_meas_lookup_code IS NOT NULL)
1032 OR (pol.unit_meas_lookup_code IS NOT NULL
1033 AND
1034 X_unit_meas_lookup_code IS NULL)
1035 OR (pol.from_header_id <> X_from_header_id)
1036 OR (pol.from_header_id is NULL
1037 AND
1038 X_from_header_id IS NOT NULL)
1039 OR (pol.from_header_id IS NOT NULL
1040 AND
1041 X_from_header_id IS NULL)
1042 OR (pol.from_line_id <> X_from_line_id)
1043 OR (pol.from_line_id is NULL
1044 AND
1045 X_from_line_id IS NOT NULL)
1046 OR (pol.from_line_id IS NOT NULL
1047 AND
1048 X_from_line_id IS NULL)
1049 OR (pol.hazard_class_id <> X_hazard_class_id)
1050 OR (pol.hazard_class_id is NULL
1051 AND
1052 X_hazard_class_id IS NOT NULL)
1053 OR (pol.hazard_class_id IS NOT NULL
1054 AND
1055 X_hazard_class_id IS NULL)
1056 -- <GC FPJ>
1057 -- Remove the check for CONTRACT_NUM
1058 OR (pol.vendor_product_num <> X_vendor_product_num)
1059 OR (pol.vendor_product_num is NULL
1060 AND
1061 X_vendor_product_num IS NOT NULL)
1062 OR (pol.vendor_product_num IS NOT NULL
1063 AND
1064 X_vendor_product_num IS NULL)
1065 OR (trunc(pol.expiration_date) <> trunc(X_expiration_date))
1066 OR (pol.expiration_date IS NULL
1067 AND
1068 X_expiration_date IS NOT NULL)
1069 OR (pol.expiration_date IS NOT NULL
1070 AND
1071 X_expiration_date IS NULL)
1072 OR (pol.un_number_id <> X_un_number_id)
1073 OR (pol.un_number_id is NULL
1074 AND
1075 X_un_number_id IS NOT NULL)
1076 OR (pol.un_number_id IS NOT NULL
1077 AND
1078 X_un_number_id IS NULL)
1079 -- <GC FPJ START>
1080 OR (pol.contract_id <> p_contract_id)
1081 OR (pol.contract_id IS NOT NULL
1082 AND
1083 p_contract_id IS NULL)
1084 OR (pol.contract_id IS NULL
1085 AND
1086 p_contract_id IS NOT NULL));
1087 -- <GC FPJ END>
1088
1089 EXCEPTION
1090 WHEN NO_DATA_FOUND THEN
1091 --dbms_output.put_line('No data found');
1092 null;
1093 WHEN OTHERS THEN
1094 --dbms_output.put_line('In Val Approval Status First exception');
1095 po_message_s.sql_error('val_approval_status', x_progress, sqlcode);
1096 raise;
1097 END;
1098
1099
1100 -- Bug 426305
1101 -- Break the SQL check into two statements to prevent
1102 -- from getting the server package compilation error:
1103 -- PLS-00103: Parser stack overflow error.
1104 --
1105 -- Continue to check the rest if the flat is not marked.
1106 --
1107 IF X_approval_status_changed <> 'Y' THEN
1108
1109 SELECT 'Y'
1110 INTO X_approval_status_changed
1111 FROM po_lines pol
1112 WHERE pol.po_line_id = X_po_line_id
1113 AND (
1114 (pol.note_to_vendor <> X_note_to_vendor)
1115 OR (pol.note_to_vendor is NULL
1116 AND
1117 X_note_to_vendor IS NOT NULL)
1118 OR (pol.note_to_vendor IS NOT NULL
1119 AND
1120 X_note_to_vendor IS NULL)
1121 OR (pol.item_revision <> X_item_revision)
1122 OR (pol.item_revision is NULL
1123 AND
1124 X_item_revision IS NOT NULL)
1125 OR (pol.item_revision IS NOT NULL
1126 AND
1127 X_item_revision IS NULL)
1128 OR (pol.category_id <> X_category_id)
1129 OR (pol.category_id is NULL
1130 AND
1131 X_category_id IS NOT NULL)
1132 OR (pol.category_id IS NOT NULL
1133 AND
1134 X_category_id IS NULL)
1135 OR (pol.price_type_lookup_code <> X_price_type_lookup_code)
1136 OR (pol.price_type_lookup_code is NULL
1137 AND
1138 X_price_type_lookup_code IS NOT NULL)
1139 OR (pol.price_type_lookup_code IS NOT NULL
1140 AND
1141 X_price_type_lookup_code IS NULL)
1142 OR (pol.not_to_exceed_price <> X_not_to_exceed_price)
1143 OR (pol.not_to_exceed_price is NULL
1144 AND
1145 X_not_to_exceed_price IS NOT NULL)
1146 OR (pol.not_to_exceed_price IS NOT NULL
1147 AND
1148 X_not_to_exceed_price IS NULL)
1149 OR (pol.quantity_committed <> X_quantity_committed)
1150 OR (pol.quantity_committed is NULL
1151 AND
1152 X_quantity_committed IS NOT NULL)
1153 OR (pol.quantity_committed IS NOT NULL
1154 AND
1155 X_quantity_committed IS NULL)
1156 OR (pol.committed_amount <> X_committed_amount)
1157 OR (pol.committed_amount is NULL
1158 AND
1159 X_committed_amount IS NOT NULL)
1160 OR (pol.committed_amount IS NOT NULL
1161 AND
1162 X_committed_amount IS NULL)
1163 -- <SERVICES FPJ START>
1164 OR (pol.amount <> X_amount_db)
1165 OR (pol.amount IS NOT NULL
1166 AND
1167 X_amount_db IS NULL)
1168 OR (pol.amount IS NULL
1169 AND
1170 X_amount_db IS NOT NULL)
1171 -- <SERVICES FPJ END>
1172 );
1173 END IF;
1174 END IF;
1175
1176 X_progress := '040';
1177
1178 IF (X_approval_status_changed = 'Y') THEN
1179 --dbms_output.put_line('status changed = Y');
1180 return(FALSE);
1181 ELSE
1182 --dbms_output.put_line('status changed = N');
1183 return(TRUE);
1184 END IF;
1185
1186 EXCEPTION
1187 WHEN NO_DATA_FOUND THEN
1188 --dbms_output.put_line('No data found');
1189 return(TRUE);
1190 WHEN OTHERS THEN
1191 --dbms_output.put_line('In Val Approval Status exception');
1192 po_message_s.sql_error('val_approval_status', x_progress, sqlcode);
1193 raise;
1194 END val_approval_status;
1195
1196 /*===========================================================================
1197
1198 PROCEDURE NAME: update_released_quantity()
1199
1200 ===========================================================================*/
1201
1202 PROCEDURE update_released_quantity
1203 (X_event IN VARCHAR2,
1204 X_shipment_type IN VARCHAR2,
1205 X_po_line_id IN NUMBER,
1206 X_original_quantity IN NUMBER,
1207 X_quantity IN NUMBER) IS
1208
1209 x_progress VARCHAR2(3) := '';
1210
1211 BEGIN
1212
1213 x_progress := '010';
1214
1215 /* Bug# 3104460 - PO_LINES.QUANTITY should not be updated. */
1216 IF (X_shipment_type = 'BLANKET') THEN
1217
1218 IF (X_event = 'INSERT') THEN
1219
1220 UPDATE PO_LINES
1221 SET closed_code = 'OPEN'
1222 WHERE po_line_id = X_po_line_id
1223 -- Bug 3202973 Should not update quantity for Services lines:
1224 AND order_type_lookup_code NOT IN ('RATE', 'FIXED PRICE')
1225 and nvl(closed_code,'OPEN')<>'OPEN'; --Bug 8529004
1226
1227 END IF;
1228
1229 END IF;
1230
1231 EXCEPTION
1232 WHEN OTHERS THEN
1233 po_message_s.sql_error('update_released_quantity', x_progress, sqlcode);
1234 raise;
1235 END update_released_quantity;
1236
1237 END PO_LINES_SV;
[Home] [Help]