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