1 PACKAGE BODY POS_CLM_UTIL_PKG as
2 /* $Header: POSCLMUTILB.pls 120.8 2011/09/23 06:41:52 ramkandu ship $ */
3
4 FUNCTION getCLMStatus RETURN VARCHAR2
5 IS
6 ProfileValue VARCHAR2(1);
7 BEGIN
8 --CLM FLAG
9 ProfileValue := NVL(FND_PROFILE.VALUE('PO_CLM_INSTALLED'),'N');
10 RETURN ProfileValue;
11 EXCEPTION WHEN OTHERS THEN
12 RETURN 'N';
13 END getCLMStatus;
14
15
16 FUNCTION IsFundedInfoSLINAllowed RETURN VARCHAR2 IS
17 InfoFundedStatus VARCHAR2(1);
18 BEGIN
19
20 --Change THIS FLAG to Disable Info Funded
21 InfoFundedStatus := 'N';
22
23 IF getCLMStatus <> 'Y'
24 THEN
25 RETURN 'N';
26 ELSE
27 RETURN InfoFundedStatus;
28 END IF;
29 END;
30
31 --Enforce Single Distribution Flag
32 FUNCTION IsEnforceSingleDistEnabled
33 RETURN VARCHAR2
34 IS
35 EnforceSingleDistStatus VARCHAR2(1);
36 BEGIN
37
38 --Change THIS FLAG to Disable Enforce Single Distribution
39 EnforceSingleDistStatus := 'N';
40
41 IF getCLMStatus <> 'Y'
42 THEN
43 RETURN 'N';
44 ELSE
45 RETURN EnforceSingleDistStatus;
46 END IF;
47 END IsEnforceSingleDistEnabled;
48
49 --Complex Pricing Flag
50 FUNCTION IsComplexPricingAllowed return varchar2 is
51 ComplexPricingStatus VARCHAR2(1);
52 BEGIN
53
54 --Change THIS FLAG to Disable Complex Pricing
55 ComplexPricingStatus := 'N';
56 IF getCLMStatus <> 'Y'
57 THEN
58 RETURN 'N';
59 ELSE
60 RETURN ComplexPricingStatus;
61 END IF;
62 END;
63
64 FUNCTION Isclminstalled
65 RETURN BOOLEAN
66 IS
67 BEGIN
68 if getCLMStatus ='Y'
69 THEN
70 RETURN TRUE;
71 ELSE
72 RETURN FALSE;
73 END IF;
74
75 END isclminstalled;
76
77 FUNCTION includeOptionAmount RETURN VARCHAR2 is begin return 'Y'; end;
78
79 FUNCTION validateInfoflags( p_from_clm_info_flag IN VARCHAR2
80 ,p_to_clm_info_flag IN VARCHAR2
81 )
82 RETURN BOOLEAN
83 IS
84 BEGIN
85 IF Nvl(p_from_clm_info_flag,'N') = 'N' AND Nvl(p_to_clm_info_flag,'N') ='N' THEN
86 RETURN FALSE;
87 ELSE
88 RETURN TRUE;
89 END IF;
90 END validateInfoflags;
91
92 FUNCTION valdiateFundFalgs ( p_from_clm_funded_flag IN VARCHAR2
93 ,p_to_clm_funded_flag IN VARCHAR2
94 )
95 RETURN BOOLEAN
96 IS
97 BEGIN
98 IF p_from_clm_funded_flag ='Y' AND p_to_clm_funded_flag='Y' THEN
99 RETURN FALSE ;
100 ELSE
101 RETURN TRUE;
102 END IF;
103 END valdiateFundFalgs;
104
105 Procedure getclmFlags
106 (p_product IN VARCHAR2
107 ,p_document_id IN NUMBER
108 ,p_line_id IN NUMBER
109 ,x_clm_info_flag IN out NOCOPY VARCHAR2
110 ,x_clm_funded_flag IN OUT NOCOPY VARCHAR2)
111 IS
112 l_clm_info_flag NUMBER := NULL;
113 BEGIN
114 IF p_product = 'PO' THEN
115 SELECT Nvl(clm_info_flag,'N'),clm_funded_flag
116 INTO x_clm_info_flag, x_clm_funded_flag
117 FROM po_lines_draft_all
118 WHERE po_line_id = p_line_id;
119 ELSIF p_product = 'ICX' THEN
120 SELECT Nvl(clm_info_flag,'N'),clm_funded_flag
121 INTO x_clm_info_flag, x_clm_funded_flag
122 FROM po_requisition_lines_all
123 WHERE requisition_line_id = p_line_id;
124 ELSIF p_product ='PON' THEN
125 SELECT Nvl(clm_info_flag,'N'),clm_funded_flag
126 INTO x_clm_info_flag, x_clm_funded_flag
127 FROM pon_auction_item_prices_all
128 WHERE line_number = p_line_id
129 AND auction_header_id = p_document_id;
130
131
132 ELSIF p_product='PON BID' THEN
133 SELECT Nvl(clm_info_flag,'N'),clm_funded_flag
134 INTO x_clm_info_flag, x_clm_funded_flag
135 FROM pon_bid_item_prices
136 WHERE BID_NUMBER = p_line_id
137 AND auction_header_id = p_document_id;
138
139 END IF;
140 EXCEPTION
141 WHEN OTHERS THEN
142 NULL;
143 END getclmflags;
144
145 FUNCTION Getlinenum
146 (p_product IN VARCHAR2
147 ,p_document_id IN NUMBER
148 ,p_line_id IN NUMBER)
149 RETURN NUMBER
150 IS
151 l_line_num NUMBER := NULL;
152 BEGIN
153 IF p_product = 'PO' THEN
154 SELECT LINE_NUM --line_num_display
155 INTO l_line_num
156 FROM po_lines_draft_all
157 WHERE po_line_id = p_line_id;
158 ELSIF p_product = 'ICX' THEN
159 SELECT LINE_NUM
160 INTO l_line_num
161 FROM po_requisition_lines_all
162 WHERE requisition_line_id = p_line_id;
163 ELSIF p_product ='PON' THEN
164 /*SELECT LINE_NUM_DISPLAY
165 INTO l_line_num
166 FROM pon_auction_item_prices_all
167 WHERE line_number = p_line_id
168 AND auction_header_id = p_document_id;*/
169 l_line_num := p_line_id;
170
171 ELSIF p_product='PON BID' THEN
172 /* SELECT LINE_NUM_DISPLAY
173 INTO l_line_num
174 FROM pon_bid_item_prices
175 WHERE BID_NUMBER = p_line_id
176 AND auction_header_id = p_document_id;*/
177 l_line_num := p_line_id;
178 END IF;
179
180 RETURN l_line_num;
181 EXCEPTION
182 WHEN OTHERS THEN
183 RETURN NULL;
184 END Getlinenum;
185
186 FUNCTION isOptionExist
187 (p_product IN VARCHAR2,
188 p_document_id IN NUMBER DEFAULT NULL,
189 p_line_id IN VARCHAR2)
190 RETURN VARCHAR2
191 IS
192 l_flag VARCHAR2(1) := 'N';
193 l_line_num NUMBER;
194 BEGIN
195 -- l_line_num:= getlinenum(p_product,p_document_id,p_line_id);
196 IF p_product = 'PO' THEN
197 SELECT 'Y'
198 INTO l_flag
199 FROM dual
200 WHERE EXISTS (SELECT 'x'
201 FROM po_lines_draft_all
202 WHERE CLM_BASE_LINE_NUM = p_line_id
203 AND PO_HEADER_ID=Nvl(p_document_id,PO_HEADER_ID));
204
205 ELSIF p_product = 'ICX' THEN
206
207 SELECT 'Y'
208 INTO l_flag
209 FROM dual
210 WHERE EXISTS (SELECT 'x'
211 FROM po_requisition_lines_all
212 WHERE CLM_BASE_LINE_NUM = p_line_id
213 AND REQUISITION_HEADER_ID=Nvl(p_document_id,REQUISITION_HEADER_ID));
214
215 ELSIF p_product = 'PON' THEN
216 SELECT 'Y'
217 INTO l_flag
218 FROM dual
219 WHERE EXISTS (SELECT 'x'
220 FROM pon_auction_item_prices_all
221 WHERE 1=1
222 AND CLM_BASE_LINE_NUM = p_line_id
223 AND auction_header_id = p_document_id);
224
225 ELSIF p_product = 'PON BID' THEN
226 SELECT 'Y'
227 INTO l_flag
228 FROM dual
229 WHERE EXISTS (SELECT 'x'
230 FROM pon_bid_item_prices
231 WHERE CLM_BASE_LINE_NUM = p_line_id
232 AND auction_header_id = p_document_id);
233
234 END IF;
235
236 RETURN l_flag;
237
238 EXCEPTION
239 WHEN No_Data_Found THEN
240 RETURN 'N';
241 WHEN OTHERS THEN
242 RETURN 'N';
243
244 END isOptionExist;
245
246 FUNCTION Isclinvalidformove
247 (p_product IN VARCHAR2,
248 p_document_id IN NUMBER DEFAULT NULL,
249 p_line_id IN VARCHAR2)
250 RETURN VARCHAR2
251 IS
252 l_flag VARCHAR2(1) := 'N';
253 BEGIN
254 IF p_product = 'PO' THEN
255 SELECT 'Y'
256 INTO l_flag
257 FROM dual
258 WHERE NOT EXISTS (SELECT 'x'
259 FROM po_lines_draft_all
260 WHERE group_line_id = p_line_id);
261
262
263 ELSIF p_product = 'ICX' THEN
264 SELECT 'Y'
265 INTO l_flag
266 FROM dual
267 WHERE NOT EXISTS (SELECT 'x'
268 FROM po_requisition_lines_all
269 WHERE group_line_id = p_line_id);
270
271
272
273
274 ELSIF p_product = 'PON' THEN
275 SELECT 'Y'
276 INTO l_flag
277 FROM dual
278 WHERE NOT EXISTS (SELECT 'x'
279 FROM pon_auction_item_prices_all
280 WHERE group_line_id = p_line_id
281 AND auction_header_id = p_document_id);
282
283
284
285 ELSIF p_product = 'PON BID' THEN
286 SELECT 'Y'
287 INTO l_flag
288 FROM dual
289 WHERE NOT EXISTS (SELECT 'x'
290 FROM pon_bid_item_prices
291 WHERE group_line_id = p_line_id
292 AND auction_header_id = p_document_id);
293
294
295 END IF;
296
297 RETURN l_flag;
298 EXCEPTION
299 WHEN No_Data_Found THEN
300 RETURN 'N';
301 WHEN OTHERS THEN
302 RETURN 'N';
303 END isclinvalidformove;
304
305 FUNCTION Get_group_line_id
306 (p_product IN VARCHAR2,
307 p_document_id IN NUMBER DEFAULT NULL,
308 p_group_line_id IN VARCHAR2)
309 RETURN NUMBER
310 IS
311 l_group_line_id NUMBER := NULL;
312 BEGIN
313 IF p_product = 'PO' THEN
314 SELECT group_line_id
315 INTO l_group_line_id
316 FROM po_lines_draft_all
317 WHERE po_line_id = p_group_line_id;
318 ELSIF p_product = 'ICX' THEN
319 SELECT group_line_id
320 INTO l_group_line_id
321 FROM po_requisition_lines_all
322 WHERE requisition_line_id = p_group_line_id;
323 ELSIF p_product = 'PON' THEN
324 SELECT group_line_id
325 INTO l_group_line_id
326 FROM pon_auction_item_prices_all
327 WHERE auction_header_id = p_document_id
328 AND line_number = p_group_line_id;
329 ELSIF p_product = 'PON BID' THEN
330 SELECT group_line_id
331 INTO l_group_line_id
332 FROM pon_bid_item_prices
333 WHERE auction_header_id = p_document_id
334 AND bid_number = p_group_line_id;
335 END IF;
336
337 RETURN l_group_line_id;
338 EXCEPTION
339 WHEN OTHERS THEN
340 NULL;
341 END get_group_line_id;
342
343
344 PROCEDURE Check_Uda_Enabled
345 (
346 p_functional_area_code IN VARCHAR2
347 ,p_document_type_code IN VARCHAR2
348 ,p_document_style_id IN NUMBER
349 ,x_enabled_flag OUT NOCOPY VARCHAR2
350 ,x_return_status OUT NOCOPY VARCHAR2
351 ,x_errorcode OUT NOCOPY NUMBER
352 ,x_msg_count OUT NOCOPY NUMBER
353 ,x_msg_data OUT NOCOPY VARCHAR2
354 )
355 IS
356 d_progress NUMBER := 0;
357
358 BEGIN
359
360 x_enabled_flag := 'Y';
361
362 x_return_status := FND_API.G_RET_STS_SUCCESS;
363 EXCEPTION
364 WHEN OTHERS THEN
365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
366
367 END Check_Uda_Enabled;
368
369
370 PROCEDURE GET_MODIF_LINE_INFO
371 (
372 p_draftId IN NUMBER,
373 p_header_id IN NUMBER,
374 p_line_type_id IN NUMBER,
375 p_item_id IN NUMBER,
376 p_job_id IN NUMBER,
377 p_contract_type IN VARCHAR2,
378 p_matching_basis IN VARCHAR2,
379 x_line_type OUT NOCOPY VARCHAR2,
380 x_item_number OUT NOCOPY VARCHAR2,
381 x_job_title OUT NOCOPY VARCHAR2,
382 x_contract_type OUT NOCOPY VARCHAR2,
383 x_funded_amount OUT NOCOPY NUMBER,
384 x_return_status OUT NOCOPY VARCHAR2,
385 x_errorcode OUT NOCOPY NUMBER,
386 x_msg_count OUT NOCOPY NUMBER,
387 x_msg_data OUT NOCOPY VARCHAR2
388 )
389 IS
390 d_progress NUMBER := 0;
391 BEGIN
392
393 d_progress := 1;
394
395 BEGIN
396
397 SELECT LINE_TYPE
398 INTO x_line_type
399 FROM PO_LINE_TYPES
400 WHERE LINE_TYPE_ID = p_line_type_id AND
401 ROWNUM=1;
402
403 EXCEPTION WHEN OTHERS THEN
404 x_line_type := NULL;
405 END;
406
407 d_progress := 2;
408
409 BEGIN
410
411 SELECT CONCATENATED_SEGMENTS
412 INTO x_item_number
413 FROM MTL_SYSTEM_ITEMS_KFV
414 WHERE INVENTORY_ITEM_ID = p_item_id AND
415 ROWNUM=1;
416
417 EXCEPTION WHEN OTHERS THEN
418 x_item_number := NULL;
419 END;
420
421
422 d_progress := 3;
423
424 BEGIN
425
426 SELECT PJ.NAME
427 INTO x_job_title
428 FROM PER_JOBS PJ
429 WHERE PJ.JOB_ID = p_job_id AND
430 ROWNUM=1;
431
432 EXCEPTION WHEN OTHERS THEN
433 x_job_title := NULL;
434 END;
435
436
437 d_progress := 4;
438
439 BEGIN
440
441 IF p_matching_basis = 'QUANTITY' THEN
442 SELECT DISPLAYED_FIELD
443 INTO x_contract_type
444 FROM PO_LOOKUP_CODES
445 WHERE LOOKUP_CODE = p_contract_type AND
446 LOOKUP_TYPE = 'PO_FEDERAL_CONTRACT_TYPES_QTY';
447 ELSIF p_matching_basis = 'AMOUNT' THEN
448 SELECT DISPLAYED_FIELD
449 INTO x_contract_type
450 FROM PO_LOOKUP_CODES
451 WHERE LOOKUP_CODE = p_contract_type AND
452 LOOKUP_TYPE = 'PO_FEDERAL_CONTRACT_TYPES_AMT';
453 END IF;
454
455 EXCEPTION WHEN OTHERS THEN
456 x_contract_type := NULL;
457 END;
458
459
460 x_funded_amount := NULL;
461 x_return_status := FND_API.G_RET_STS_SUCCESS;
462
463 EXCEPTION WHEN OTHERS THEN
464
465 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
466
467 END GET_MODIF_LINE_INFO;
468
469 PROCEDURE GET_MODIF_LINE_UDA_INFO
470 (
471 p_draftId IN NUMBER,
472 p_header_id IN NUMBER,
473 p_line_type_id IN NUMBER,
474 p_org_id IN NUMBER,
475 p_idc_type IN VARCHAR2,
476 p_contract_type IN VARCHAR2,
477 p_item_id IN NUMBER,
478 p_matching_basis IN VARCHAR2,
479 x_line_type OUT NOCOPY VARCHAR2,
480 x_org_name OUT NOCOPY VARCHAR2,
481 x_contract_type OUT NOCOPY VARCHAR2,
482 x_item_name OUT NOCOPY VARCHAR2,
483 x_currency_code OUT NOCOPY VARCHAR2,
484 x_idc_type OUT NOCOPY VARCHAR2,
485 x_ext_price_label OUT NOCOPY VARCHAR2,
486 x_document_type OUT NOCOPY VARCHAR2,
487 x_return_status OUT NOCOPY VARCHAR2,
488 x_errorcode OUT NOCOPY NUMBER,
489 x_msg_count OUT NOCOPY NUMBER,
490 x_msg_data OUT NOCOPY VARCHAR2
491 )
492 IS
493 d_progress NUMBER := 0;
494 BEGIN
495 d_progress := 1;
496
497 BEGIN
498
499 SELECT LINE_TYPE
500 INTO x_line_type
501 FROM PO_LINE_TYPES
502 WHERE LINE_TYPE_ID = p_line_type_id AND
503 ROWNUM=1;
504
505 EXCEPTION WHEN OTHERS THEN
506 x_line_type := NULL;
507 END;
508
509 d_progress := 2;
510
511 BEGIN
512
513 SELECT name
514 INTO x_org_name
515 FROM hr_all_organization_units_tl
516 WHERE organization_id = p_org_id
517 AND language = USERENV('LANG');
518
519 EXCEPTION WHEN OTHERS THEN
520 x_org_name := NULL;
521 END;
522
523 d_progress := 3;
524
525 BEGIN
526
527 IF p_matching_basis = 'QUANTITY' THEN
528 SELECT DISPLAYED_FIELD
529 INTO x_contract_type
530 FROM PO_LOOKUP_CODES
531 WHERE LOOKUP_CODE = p_contract_type AND
532 LOOKUP_TYPE = 'PO_FEDERAL_CONTRACT_TYPES_QTY';
533 ELSIF p_matching_basis = 'AMOUNT' THEN
534 SELECT DISPLAYED_FIELD
535 INTO x_contract_type
536 FROM PO_LOOKUP_CODES
537 WHERE LOOKUP_CODE = p_contract_type AND
538 LOOKUP_TYPE = 'PO_FEDERAL_CONTRACT_TYPES_AMT';
539 END IF;
540
541 EXCEPTION WHEN OTHERS THEN
542 x_contract_type := NULL;
543 END;
544
545 d_progress := 4;
546
547 BEGIN
548
549 SELECT CONCATENATED_SEGMENTS
550 INTO x_item_name
551 FROM MTL_SYSTEM_ITEMS_KFV
552 WHERE INVENTORY_ITEM_ID = p_item_id AND
553 ROWNUM=1;
554
555 EXCEPTION WHEN OTHERS THEN
556 x_item_name := NULL;
557 END;
558
559 d_progress := 5;
560
561 BEGIN
562 IF p_draftId = '-1' OR p_draftId IS NULL THEN
563 SELECT CURRENCY_CODE
564 INTO x_currency_code
565 FROM po_headers_all
566 WHERE PO_HEADER_ID = p_header_id;
567 ELSE
568 SELECT CURRENCY_CODE
569 INTO x_currency_code
570 FROM po_headers_merge_v
571 WHERE PO_HEADER_ID = p_header_id AND
572 DRAFT_ID = p_draftId;
573 END IF;
574
575 EXCEPTION WHEN OTHERS THEN
576 x_currency_code := NULL;
577 END;
578
579 d_progress := 6;
580
581 BEGIN
582
583 IF p_matching_basis = 'QUANTITY' THEN
584 SELECT DISPLAYED_FIELD
585 INTO x_idc_type
586 FROM PO_LOOKUP_CODES
587 WHERE LOOKUP_CODE = p_idc_type AND
588 LOOKUP_TYPE = 'PO_FEDERAL_IDC_TYPES';
589 ELSIF p_matching_basis = 'AMOUNT' THEN
590 SELECT DISPLAYED_FIELD
591 INTO x_idc_type
592 FROM PO_LOOKUP_CODES
593 WHERE LOOKUP_CODE = p_idc_type AND
594 LOOKUP_TYPE = 'PO_FEDERAL_IDC_TYPES';
595 END IF;
596
597 EXCEPTION WHEN OTHERS THEN
598 x_idc_type := NULL;
599 END;
600
601 d_progress := 7;
602
603 BEGIN
604
605 SELECT PO_CLM_CP_LABEL_EXT.getHintLabel(p_matching_basis,
606 p_contract_type,
607 p_idc_type)
608 INTO x_ext_price_label
609 FROM dual;
610 EXCEPTION WHEN OTHERS THEN
611 x_ext_price_label := NULL;
612 END;
613
614 d_progress := 8;
615
616 BEGIN
617 IF p_draftId = '-1' OR p_draftId IS NULL THEN
618 SELECT TYPE_LOOKUP_CODE
619 INTO x_document_type
620 FROM po_headers_all
621 WHERE PO_HEADER_ID = p_header_id;
622 ELSE
623 SELECT TYPE_LOOKUP_CODE
624 INTO x_document_type
625 FROM po_headers_merge_v
626 WHERE PO_HEADER_ID = p_header_id AND
627 DRAFT_ID = p_draftId;
628 END IF;
629 EXCEPTION WHEN OTHERS THEN
630 x_document_type := NULL;
631 END;
632
633
634 x_return_status := FND_API.G_RET_STS_SUCCESS;
635
636 EXCEPTION WHEN OTHERS THEN
637
638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
639
640 END GET_MODIF_LINE_UDA_INFO;
641
642 PROCEDURE GET_MODIF_HEADER_INFO
643 (
644 p_draftId IN NUMBER,
645 p_header_id IN NUMBER,
646 p_vendor_id IN NUMBER,
647 p_vendor_site_id IN NUMBER,
648 p_user_id IN NUMBER,
649 p_orgId IN NUMBER,
650 p_term_id IN NUMBER,
651 p_ship_to_loc_id IN NUMBER,
652 p_bill_to_loc_id IN NUMBER,
653 p_document_level IN VARCHAR2,
654 p_freight_lookup_code IN VARCHAR2,
655 p_ship_cntrl_lookup_code IN VARCHAR2,
656 p_fob_lookup_code IN VARCHAR2,
657 x_chg_amt OUT NOCOPY NUMBER,
658 x_chg_amt_wo_opt OUT NOCOPY NUMBER,
659 x_vendor_name OUT NOCOPY VARCHAR2,
660 x_vendor_site_code OUT NOCOPY VARCHAR2,
661 x_addr_line1 OUT NOCOPY VARCHAR2,
662 x_addr_line2 OUT NOCOPY VARCHAR2,
663 x_addr_line3 OUT NOCOPY VARCHAR2,
664 x_city_state OUT NOCOPY VARCHAR2,
665 x_country OUT NOCOPY VARCHAR2,
666 x_employee_id OUT NOCOPY NUMBER,
667 x_employee_full_name OUT NOCOPY VARCHAR2,
668 x_emp_location_id OUT NOCOPY NUMBER,
669 x_agent_id OUT NOCOPY NUMBER,
670 x_org_name OUT NOCOPY VARCHAR2,
671 x_pay_term_name OUT NOCOPY VARCHAR2,
672 x_freight_meaning OUT NOCOPY VARCHAR2,
673 x_ship_cntrl_meaning OUT NOCOPY VARCHAR2,
674 x_fob_meaning OUT NOCOPY VARCHAR2,
675 x_ship_addr1 OUT NOCOPY VARCHAR2,
676 x_ship_addr2 OUT NOCOPY VARCHAR2,
677 x_ship_addr3 OUT NOCOPY VARCHAR2,
678 x_ship_city_state OUT NOCOPY VARCHAR2,
679 x_bill_addr1 OUT NOCOPY VARCHAR2,
680 x_bill_addr2 OUT NOCOPY VARCHAR2,
681 x_bill_addr3 OUT NOCOPY VARCHAR2,
682 x_bill_city_state OUT NOCOPY VARCHAR2,
683 x_return_status OUT NOCOPY VARCHAR2,
684 x_errorcode OUT NOCOPY NUMBER,
685 x_msg_count OUT NOCOPY NUMBER,
686 x_msg_data OUT NOCOPY VARCHAR2
687 )
688 IS
689 d_progress NUMBER := 0;
690 l_old_chg_amt NUMBER := 0;
691 l_new_chg_amt NUMBER := 0;
692 l_new_chg_exopt_amt NUMBER := 0;
693 l_old_chg_exopt_amt NUMBER := 0;
694 BEGIN
695
696 d_progress := 1;
697
698 BEGIN
699
700 SELECT CHANGED_AMT, CHANGED_AMT_EXCL_OPT
701 INTO x_chg_amt, x_chg_amt_wo_opt
702 FROM PO_DRAFTS
703 WHERE DRAFT_ID = p_draftId AND
704 DOCUMENT_ID = p_header_id;
705
706 IF x_chg_amt IS NULL THEN
707 l_new_chg_amt := PO_DOCUMENT_TOTALS_PVT.getAmountOrdered(p_doc_level => p_document_level,
708 p_doc_level_id => p_header_id,
709 p_data_source => 'TRANSACTION',
710 p_draft_id => p_draftId);
711
712 l_old_chg_amt := PO_DOCUMENT_TOTALS_PVT.getAmountOrdered(p_doc_level => p_document_level,
713 p_doc_level_id => p_header_id,
714 p_data_source => 'TRANSACTION',
715 p_draft_id => -1);
716
717 x_chg_amt := l_new_chg_amt - l_old_chg_amt;
718 END IF;
719
720 IF x_chg_amt_wo_opt IS NULL THEN
721 l_new_chg_exopt_amt := PO_DOCUMENT_TOTALS_PVT.getAmountOrderedExclOptions(p_doc_level => p_document_level,
722 p_doc_level_id => p_header_id,
723 p_data_source => 'TRANSACTION',
724 p_draft_id => p_draftId);
725
726 l_old_chg_exopt_amt := PO_DOCUMENT_TOTALS_PVT.getAmountOrderedExclOptions(p_doc_level => p_document_level,
727 p_doc_level_id => p_header_id,
728 p_data_source => 'TRANSACTION',
729 p_draft_id => -1);
730
731 x_chg_amt_wo_opt := l_new_chg_exopt_amt - l_old_chg_exopt_amt;
732 END IF;
733
734 IF x_chg_amt IS NULL THEN
735 x_chg_amt := 0;
736 END IF;
737
738 IF x_chg_amt_wo_opt IS NULL THEN
739 x_chg_amt_wo_opt := 0;
740 END IF;
741
742 EXCEPTION WHEN OTHERS THEN
743 x_chg_amt := 0;
744 x_chg_amt_wo_opt := 0;
745 END;
746
747 d_progress := 2;
748
749 BEGIN
750
751 SELECT VENDOR_NAME INTO x_vendor_name FROM PO_VENDORS WHERE VENDOR_ID = p_vendor_id;
752
753 EXCEPTION WHEN OTHERS THEN
754 x_vendor_name := NULL;
755 END;
756
757 d_progress := 3;
758
759 BEGIN
760
761 SELECT VENDOR_SITE_CODE,
762 ADDRESS_LINE1,
763 ADDRESS_LINE2,
764 ADDRESS_LINE3,
765 CITY||', '||STATE||' '||ZIP CITYSTATE,
766 COUNTRY
767 INTO x_vendor_site_code,
768 x_addr_line1,
769 x_addr_line2,
770 x_addr_line3,
771 x_city_state,
772 x_country
773 FROM PO_VENDOR_SITES_ALL
774 WHERE VENDOR_SITE_ID = p_vendor_site_id;
775
776 EXCEPTION WHEN OTHERS THEN
777 x_vendor_site_code := NULL;
778 x_addr_line1 := NULL;
779 x_addr_line1 := NULL;
780 x_addr_line3 := NULL;
781 x_city_state := NULL;
782 x_country := NULL;
783 END;
784
785 d_progress := 4;
786
787 BEGIN
788
789 SELECT EMP.person_id,
790 EMP.full_name,
791 nvl(EMP.location_id, 0),
792 POA.agent_id
793 INTO x_employee_id,
794 x_employee_full_name,
795 x_emp_location_id,
796 x_agent_id
797 FROM fnd_user FU,
798 per_workforce_current_x EMP,
799 po_agents POA
800 WHERE FU.employee_id = p_user_id
801 AND FU.employee_id = EMP.person_id (+)
802 AND EMP.person_id = POA.agent_id (+)
803 AND sysdate BETWEEN NVL(POA.start_date_active (+), sysdate - 1)
804 AND NVL(POA.end_date_active (+), sysdate + 1)
805 AND ROWNUM=1;
806
807 EXCEPTION WHEN OTHERS THEN
808 x_employee_full_name := NULL;
809 END;
810
811 d_progress := 5;
812
813 BEGIN
814
815 x_org_name := PO_GA_PVT.get_org_name( p_org_id => p_orgId);
816
817 EXCEPTION WHEN OTHERS THEN
818 x_org_name := NULL;
819 END;
820
821 d_progress := 6;
822
823 BEGIN
824
825 SELECT NAME INTO x_pay_term_name FROM ap_terms WHERE term_id = p_term_id;
826
827 EXCEPTION WHEN OTHERS THEN
828 x_pay_term_name := NULL;
829 END;
830
831 d_progress := 7;
832
833 BEGIN
834
835 SELECT displayed_field
836 INTO x_freight_meaning
837 FROM po_lookup_codes
838 WHERE lookup_type = 'FREIGHT TERMS'
839 AND lookup_code = p_freight_lookup_code;
840
841 EXCEPTION WHEN OTHERS THEN
842 x_freight_meaning := NULL;
843 END;
844
845 d_progress := 8;
846
847 BEGIN
848
849 SELECT displayed_field
850 INTO x_ship_cntrl_meaning
851 FROM po_lookup_codes
852 WHERE lookup_type = 'SHIPPING CONTROL'
853 AND lookup_code = p_ship_cntrl_lookup_code;
854
855 EXCEPTION WHEN OTHERS THEN
856 x_ship_cntrl_meaning := NULL;
857 END;
858
859 d_progress := 9;
860
861 BEGIN
862
863 SELECT displayed_field
864 INTO x_fob_meaning
865 FROM po_lookup_codes
866 WHERE lookup_type = 'FOB'
867 AND lookup_code = p_fob_lookup_code;
868
869 EXCEPTION WHEN OTHERS THEN
870 x_fob_meaning := NULL;
871 END;
872
873 d_progress := 10;
874
875 BEGIN
876
877 SELECT ADDRESS_LINE_1,
878 ADDRESS_LINE_2,
879 ADDRESS_LINE_3,
880 TOWN_OR_CITY||', '||REGION_2||' '||POSTAL_CODE SHIP_CITY_STATE
881 INTO x_ship_addr1,
882 x_ship_addr2,
883 x_ship_addr3,
884 x_ship_city_state
885 FROM HR_LOCATIONS_ALL
886 WHERE LOCATION_ID = p_ship_to_loc_id;
887
888 EXCEPTION WHEN OTHERS THEN
889 x_ship_addr1 := NULL;
890 x_ship_addr2 := NULL;
891 x_ship_addr3 := NULL;
892 x_ship_city_state := NULL;
893 END;
894
895 d_progress := 11;
896
897 BEGIN
898
899 SELECT ADDRESS_LINE_1,
900 ADDRESS_LINE_2,
901 ADDRESS_LINE_3,
902 TOWN_OR_CITY||', '||REGION_2||' '||POSTAL_CODE SHIP_CITY_STATE
903 INTO x_bill_addr1,
904 x_bill_addr2,
905 x_bill_addr3,
906 x_bill_city_state
907 FROM HR_LOCATIONS_ALL
908 WHERE LOCATION_ID = p_bill_to_loc_id;
909
910
911 EXCEPTION WHEN OTHERS THEN
912 x_bill_addr1 := NULL;
913 x_bill_addr2 := NULL;
914 x_bill_addr3 := NULL;
915 x_bill_city_state := NULL;
916 END;
917
918
919 x_return_status := FND_API.G_RET_STS_SUCCESS;
920
921 EXCEPTION WHEN OTHERS THEN
922
923 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
924
925 END GET_MODIF_HEADER_INFO;
926
927 FUNCTION Isvalidtomove
928 (p_product IN VARCHAR2,
929 p_action IN VARCHAR2,
930 p_document_id IN NUMBER DEFAULT NULL,
931 p_from_line_id IN NUMBER,
932 p_from_group_line_id IN NUMBER,
933 p_from_clm_info_flag IN VARCHAR2,
934 p_from_clm_option_indicator IN VARCHAR2,
935 p_from_clm_base_line_num IN NUMBER,
936 p_from_clm_option_num IN NUMBER,
937 p_from_clm_option_from_date IN DATE,
938 p_from_clm_option_to_date IN DATE,
939 p_from_clm_funded_flag IN VARCHAR2,
940 p_to_line_id IN NUMBER,
941 p_to_group_line_id IN NUMBER,
942 p_to_clm_info_flag IN VARCHAR2,
943 p_to_clm_option_indicator IN VARCHAR2,
944 p_to_clm_base_line_num IN NUMBER,
945 p_to_clm_option_num IN NUMBER,
946 p_to_clm_option_from_date IN DATE,
947 p_to_clm_option_to_date IN DATE,
948 p_to_clm_funded_flag IN VARCHAR2)
949 RETURN VARCHAR2
950 IS
951
952 l_group_line_id NUMBER;
953 l_clm_info_flag VARCHAR2(1);
954 l_clm_funded_flag VARCHAR2(1);
955
956 BEGIN
957 -- Do not show option lines
958 IF p_from_line_id = p_to_line_id THEN
959 RETURN 'N';
960 END IF;
961
962 /* moving a normal clin/slin before or after then show all normal clins start*/
963 IF p_action IN ('BEFORE','AFTER')
964 AND p_from_clm_base_line_num IS NULL THEN
965 IF p_to_clm_base_line_num IS NOT NULL THEN
966 RETURN 'N';
967 END IF;
968
969 IF p_from_group_line_id IS NULL
970 AND p_to_group_line_id IS NULL
971 AND p_to_clm_base_line_num IS NULL THEN
972 RETURN 'Y';
973 END IF;
974
975 IF p_from_group_line_id IS NOT NULL
976 AND p_to_group_line_id IS NULL
977 AND p_to_clm_base_line_num IS NULL THEN
978 RETURN 'Y';
979 END IF;
980
981 IF p_from_group_line_id IS NOT NULL
982 AND p_to_group_line_id = p_from_group_line_id
983 AND p_to_clm_base_line_num IS NULL THEN
984 RETURN 'Y';
985 END IF;
986
987 RETURN 'N';
988 END IF;
989
990 /* moving a normal clin/slin before or after then show all normal clins end*/
991
992 /* Moving a Normal clin/slin as slin start*/
993 IF p_action = 'AS_SLIN'
994 AND p_from_clm_base_line_num IS NULL THEN
995
996
997 IF p_to_clm_base_line_num IS NOT NULL THEN
998 RETURN 'N';
999 END IF;
1000
1001 IF NOT validateInfoflags(p_from_clm_info_flag,p_to_clm_info_flag)
1002 OR NOT valdiateFundFalgs(p_from_clm_funded_flag,p_to_clm_funded_flag) THEN
1003 RETURN 'N';
1004 END IF;
1005
1006
1007
1008 IF p_from_group_line_id IS NULL THEN
1009
1010 IF Isclinvalidformove(p_product,p_document_id,p_from_line_id) = 'N'
1011 OR isOptionExist(p_product,p_document_id,p_from_line_id) = 'Y'
1012 THEN
1013 RETURN 'N';
1014 END IF;
1015
1016 IF p_to_group_line_id IS NULL THEN
1017 RETURN 'Y';
1018 END IF;
1019 END IF;
1020
1021
1022 IF p_from_group_line_id IS NOT NULL
1023 AND p_to_group_line_id IS NULL THEN
1024 IF isOptionExist(p_product,p_document_id,p_from_line_id) = 'Y' then
1025 return 'N';
1026 end if;
1027 IF p_from_group_line_id = p_to_line_id THEN
1028 RETURN 'N';
1029 END IF;
1030
1031 RETURN 'Y';
1032 END IF;
1033
1034 RETURN 'N';
1035 END IF;
1036
1037 /* Moving a Normal clin/slin as slin end*/
1038 /* Moving a Normal clin/slin as option start */
1039 IF p_action = 'AS_OPTION'
1040 AND p_from_clm_base_line_num IS NULL THEN
1041 IF p_to_clm_base_line_num IS NOT NULL THEN
1042 RETURN 'N';
1043 END IF;
1044
1045 IF p_from_group_line_id IS NULL THEN
1046 IF Isclinvalidformove(p_product,p_document_id,p_from_line_id) = 'N'
1047 OR isOptionExist(p_product,p_document_id,p_from_line_id) = 'Y'
1048 THEN
1049 RETURN 'N';
1050 END IF;
1051
1052 IF p_from_line_id = p_to_group_line_id THEN
1053 RETURN 'N';
1054 END IF;
1055
1056 IF p_to_group_line_id IS NOT NULL THEN
1057 -- i.e Making normal clin as option to slin if the to line is 'slin'
1058 -- Then validate info and funding flags with the parent of the 'to slin'.
1059 getclmFlags
1060 (p_product => p_product
1061 ,p_document_id => p_document_id
1062 ,p_line_id => p_to_group_line_id
1063 ,x_clm_info_flag => l_clm_info_flag
1064 ,x_clm_funded_flag => l_clm_funded_flag);
1065
1066 IF NOT validateInfoflags(p_from_clm_info_flag,l_clm_info_flag)
1067 OR NOT valdiateFundFalgs(p_from_clm_funded_flag,l_clm_funded_flag) THEN
1068 RETURN 'N';
1069 END IF;
1070 END IF;
1071 RETURN 'Y';
1072 END IF;
1073
1074 /* Show only base slins from the current clin - option clins are eliminated
1075 at the first palce itself */
1076 IF p_from_group_line_id IS NOT NULL THEN
1077 IF p_to_group_line_id = p_from_group_line_id THEN
1078 RETURN 'Y';
1079 END IF;
1080
1081 RETURN 'N';
1082 END IF;
1083 END IF;
1084
1085 /* Moving a Normal clin/slin as option end */
1086 /* Moving a o-clin/o-slin before,after is an invalid operation start*/
1087 IF p_action IN ('BEFORE','AFTER')
1088 AND p_from_clm_base_line_num IS NOT NULL THEN
1089
1090 IF p_from_group_line_id IS NULL THEN
1091 IF p_to_group_line_id IS NULL AND p_from_clm_base_line_num=p_to_clm_base_line_num THEN
1092 RETURN 'Y';
1093 END IF;
1094 END IF;
1095
1096 IF p_from_group_line_id IS NOT NULL THEN
1097 IF p_to_group_line_id = p_from_group_line_id AND p_from_clm_base_line_num=p_to_clm_base_line_num THEN
1098 RETURN 'Y';
1099 END IF;
1100 END IF;
1101
1102 RETURN 'N';
1103 END IF;
1104
1105 /* Moving a o-clin/o-slin before,after is an invalid operation end*/
1106 /* moving a o-clin/slin as slin start*/
1107 IF p_action = 'AS_SLIN'
1108 AND p_from_clm_base_line_num IS NOT NULL THEN
1109 IF p_to_clm_base_line_num IS NOT NULL
1110 AND p_to_group_line_id IS NOT NULL THEN
1111 RETURN 'N';
1112 END IF;
1113
1114 IF p_from_group_line_id IS NULL THEN
1115 RETURN 'N';
1116 END IF;
1117
1118 IF p_from_group_line_id IS NOT NULL THEN
1119
1120 IF NOT validateInfoflags(p_from_clm_info_flag,p_to_clm_info_flag)
1121 OR NOT valdiateFundFalgs(p_from_clm_funded_flag,p_to_clm_funded_flag) THEN
1122 RETURN 'N';
1123 END IF;
1124
1125 IF p_to_group_line_id IS NULL
1126 AND p_to_clm_base_line_num IS NOT NULL THEN
1127 RETURN 'Y';
1128 END IF;
1129 END IF;
1130
1131 RETURN 'N';
1132 END IF;
1133
1134 /* moving a o-clin/slin as slin end*/
1135 /* moving a o-clin/slin as option start*/
1136 IF p_action = 'AS_OPTION'
1137 AND p_from_clm_base_line_num IS NOT NULL THEN
1138 IF p_to_clm_base_line_num IS NOT NULL THEN
1139 RETURN 'N';
1140 END IF;
1141
1142 IF p_from_clm_base_line_num = p_to_line_id THEN
1143 RETURN 'N';
1144 END IF;
1145
1146 IF p_from_group_line_id IS NULL THEN
1147 RETURN 'Y';
1148 END IF;
1149
1150 IF p_from_group_line_id IS NOT NULL
1151 AND p_to_group_line_id = p_from_group_line_id THEN
1152 RETURN 'Y';
1153 END IF;
1154
1155 RETURN 'N';
1156 END IF;
1157
1158 /* moving a o-clin/slin as option end*/
1159 RETURN 'N';
1160 EXCEPTION
1161 WHEN OTHERS THEN
1162 RETURN 'N';
1163 END isvalidtomove;
1164
1165 END POS_CLM_UTIL_PKG;
[Home] [Help]