1 PACKAGE BODY po_clm_clo_util AS
2 /* $Header: PO_CLM_CLO_UTIL.plb 120.16.12020000.2 2013/02/10 19:37:54 vegajula ship $ */
3
4
5 /* CLM Switch Code Changes - Start */
6
7 FUNCTION getCLMStatus RETURN VARCHAR2
8 IS
9 BEGIN
10 if checkCLMResp (fnd_global.user_id, fnd_global.resp_id,fnd_global.resp_appl_id) ='Y' THEN
11 RETURN 'Y';
12 ELSE
13 RETURN 'N';
14 END IF;
15 EXCEPTION WHEN OTHERS THEN
16 RETURN 'N';
17 END getCLMStatus;
18
19 FUNCTION checkCLMResp ( user_id NUMBER, resp_id NUMBER, appl_id NUMBER ) RETURN VARCHAR2
20 IS
24 Profile_Value_Site_Level := NVL(FND_PROFILE.VALUE('PO_CLM_INSTALLED'),'N');
21 Profile_Value_Site_Level VARCHAR2(1);
22 Profile_Value_Resp_Level VARCHAR2(1);
23 BEGIN
25 Profile_Value_Resp_Level := NVL(FND_PROFILE.VALUE_SPECIFIC( NAME => 'PO_CLM_ENABLED',
26 RESPONSIBILITY_ID => resp_id),'N');
27
28 IF Profile_Value_Site_Level = 'N' THEN
29 RETURN Profile_Value_Site_Level;
30 ELSE
31 RETURN Profile_Value_Resp_Level;
32 END IF;
33
34 EXCEPTION WHEN OTHERS THEN
35 RETURN 'N';
36 END checkCLMResp;
37
38 /* CLM Switch Code Changes - Start */
39
40 FUNCTION IsFundedInfoSLINAllowed RETURN VARCHAR2 IS
41 InfoFundedStatus VARCHAR2(1);
42 BEGIN
43
44 --Change THIS FLAG to Disable Info Funded
45 InfoFundedStatus := 'N';
46
47 IF getCLMStatus <> 'Y'
48 THEN
49 RETURN 'N';
50 ELSE
51 RETURN InfoFundedStatus;
52 END IF;
53 END;
54
55 --Enforce Single Distribution Flag
56 FUNCTION IsEnforceSingleDistEnabled
57 RETURN VARCHAR2
58 IS
59 EnforceSingleDistStatus VARCHAR2(1);
60 BEGIN
61
62 --Change THIS FLAG to Disable Enforce Single Distribution
63 EnforceSingleDistStatus := 'N';
64
65 IF getCLMStatus <> 'Y'
66 THEN
67 RETURN 'N';
68 ELSE
69 RETURN EnforceSingleDistStatus;
70 END IF;
71 END IsEnforceSingleDistEnabled;
72
73 --Complex Pricing Flag
74 FUNCTION IsComplexPricingAllowed return varchar2 is
75 ComplexPricingStatus VARCHAR2(1);
76 BEGIN
77
78 --Change THIS FLAG to Disable Complex Pricing
79 ComplexPricingStatus := 'N';
80 IF getCLMStatus <> 'Y'
81 THEN
82 RETURN 'N';
83 ELSE
84 RETURN ComplexPricingStatus;
85 END IF;
86 END;
87
88 FUNCTION Isclminstalled
89 RETURN BOOLEAN
90 IS
91 BEGIN
92 if getCLMStatus ='Y'
93 THEN
94 RETURN TRUE;
95 ELSE
96 RETURN FALSE;
97 END IF;
98
99 END isclminstalled;
100
101 FUNCTION includeOptionAmount RETURN VARCHAR2 is begin return 'Y'; end;
102
103 FUNCTION validateInfoflags( p_from_clm_info_flag IN VARCHAR2
104 ,p_to_clm_info_flag IN VARCHAR2
105 )
106 RETURN BOOLEAN
107 IS
108 BEGIN
109 IF Nvl(p_from_clm_info_flag,'N') = 'N' AND Nvl(p_to_clm_info_flag,'N') ='N' THEN
110 RETURN FALSE;
111 ELSE
112 RETURN TRUE;
113 END IF;
114 END validateInfoflags;
115
116 FUNCTION valdiateFundFalgs ( p_from_clm_funded_flag IN VARCHAR2
117 ,p_to_clm_funded_flag IN VARCHAR2
118 )
119 RETURN BOOLEAN
120 IS
121 BEGIN
122 IF p_from_clm_funded_flag ='Y' AND p_to_clm_funded_flag='Y' THEN
123 RETURN FALSE ;
124 ELSE
125 RETURN TRUE;
126 END IF;
127 END valdiateFundFalgs;
128
129 Procedure getclmFlags
130 (p_product IN VARCHAR2
131 ,p_document_id IN NUMBER
132 ,p_line_id IN NUMBER
133 ,x_clm_info_flag IN out NOCOPY VARCHAR2
134 ,x_clm_funded_flag IN OUT NOCOPY VARCHAR2)
135 IS
136 l_clm_info_flag NUMBER := NULL;
137 BEGIN
138 IF p_product = 'PO' THEN
139 SELECT Nvl(clm_info_flag,'N'),clm_funded_flag
140 INTO x_clm_info_flag, x_clm_funded_flag
141 FROM po_lines_merge_v
142 WHERE po_line_id = p_line_id AND draft_id = -1;
143 ELSIF p_product = 'ICX' THEN
144 SELECT Nvl(clm_info_flag,'N'),clm_funded_flag
145 INTO x_clm_info_flag, x_clm_funded_flag
146 FROM po_requisition_lines_all
147 WHERE requisition_line_id = p_line_id;
148 ELSIF p_product ='PON' THEN
149 SELECT Nvl(clm_info_flag,'N'),clm_funded_flag
150 INTO x_clm_info_flag, x_clm_funded_flag
151 FROM pon_auction_item_prices_all
152 WHERE line_number = p_line_id
153 AND auction_header_id = p_document_id;
154
155
156 ELSIF p_product='PON BID' THEN
157 SELECT Nvl(clm_info_flag,'N'),clm_funded_flag
158 INTO x_clm_info_flag, x_clm_funded_flag
159 FROM pon_bid_item_prices
160 WHERE BID_NUMBER = p_line_id
161 AND auction_header_id = p_document_id;
162
163 END IF;
164 EXCEPTION
165 WHEN OTHERS THEN
166 NULL;
167 END getclmflags;
168
169 FUNCTION Getlinenum
170 (p_product IN VARCHAR2
171 ,p_document_id IN NUMBER
172 ,p_line_id IN NUMBER)
173 RETURN NUMBER
174 IS
175 l_line_num NUMBER := NULL;
176 BEGIN
177 IF p_product = 'PO' THEN
178 SELECT LINE_NUM --line_num_display
179 INTO l_line_num
180 FROM po_lines_merge_v
181 WHERE po_line_id = p_line_id AND draft_id = -1;
182 ELSIF p_product = 'ICX' THEN
183 SELECT LINE_NUM
184 INTO l_line_num
185 FROM po_requisition_lines_all
186 WHERE requisition_line_id = p_line_id;
187 ELSIF p_product ='PON' THEN
191 WHERE line_number = p_line_id
188 /*SELECT LINE_NUM_DISPLAY
189 INTO l_line_num
190 FROM pon.pon_auction_item_prices_all
192 AND auction_header_id = p_document_id;*/
193 l_line_num := p_line_id;
194
195 ELSIF p_product='PON BID' THEN
196 /* SELECT LINE_NUM_DISPLAY
197 INTO l_line_num
198 FROM pon_bid_item_prices
199 WHERE BID_NUMBER = p_line_id
200 AND auction_header_id = p_document_id;*/
201 l_line_num := p_line_id;
202 END IF;
203
204 RETURN l_line_num;
205 EXCEPTION
206 WHEN OTHERS THEN
207 RETURN NULL;
208 END Getlinenum;
209
210 FUNCTION isOptionExist
211 (p_product IN VARCHAR2,
212 p_document_id IN NUMBER DEFAULT NULL,
213 p_line_id IN VARCHAR2)
214 RETURN VARCHAR2
215 IS
216 l_flag VARCHAR2(1) := 'N';
217 l_line_num NUMBER;
218 BEGIN
219 -- l_line_num:= getlinenum(p_product,p_document_id,p_line_id);
220 IF p_product = 'PO' THEN
221 SELECT 'Y'
222 INTO l_flag
223 FROM dual
224 WHERE EXISTS (SELECT 'x'
225 FROM po_lines_merge_v
226 WHERE CLM_BASE_LINE_NUM = p_line_id AND draft_id = -1
227 AND PO_HEADER_ID=Nvl(p_document_id,PO_HEADER_ID));
228
229 ELSIF p_product = 'ICX' THEN
230
231 SELECT 'Y'
232 INTO l_flag
233 FROM dual
234 WHERE EXISTS (SELECT 'x'
235 FROM po_requisition_lines_all
236 WHERE CLM_BASE_LINE_NUM = p_line_id
237 AND REQUISITION_HEADER_ID=Nvl(p_document_id,REQUISITION_HEADER_ID));
238
239 ELSIF p_product = 'PON' THEN
240 SELECT 'Y'
241 INTO l_flag
242 FROM dual
243 WHERE EXISTS (SELECT 'x'
244 FROM pon_auction_item_prices_all
245 WHERE 1=1
246 AND CLM_BASE_LINE_NUM = p_line_id
247 AND auction_header_id = p_document_id);
248
249 ELSIF p_product = 'OFFER' THEN
250 l_flag := 'N';
251 /*SELECT 'Y'
252 INTO l_flag
253 FROM dual
254 WHERE EXISTS (SELECT 'x'
255 FROM pon_bid_item_prices
256 WHERE CLM_BASE_LINE_NUM = p_line_id
257 AND auction_header_id = p_document_id);*/
258
259 END IF;
260
261 RETURN l_flag;
262
263 EXCEPTION
264 WHEN No_Data_Found THEN
265 RETURN 'N';
266 WHEN OTHERS THEN
267 RETURN 'N';
268
269 END isOptionExist;
270
271 FUNCTION Isclinvalidformove
272 (p_product IN VARCHAR2,
273 p_document_id IN NUMBER DEFAULT NULL,
274 p_line_id IN VARCHAR2)
275 RETURN VARCHAR2
276 IS
277 l_flag VARCHAR2(1) := 'N';
278 BEGIN
279 IF p_product = 'PO' THEN
280 SELECT 'Y'
281 INTO l_flag
282 FROM dual
283 WHERE NOT EXISTS (SELECT 'x'
284 FROM po_lines_merge_v
285 WHERE group_line_id = p_line_id AND draft_id = -1);
286
287
288 ELSIF p_product = 'ICX' THEN
289 SELECT 'Y'
290 INTO l_flag
291 FROM dual
292 WHERE NOT EXISTS (SELECT 'x'
293 FROM po_requisition_lines_all
294 WHERE group_line_id = p_line_id);
295
296
297
298
299 ELSIF p_product = 'PON' THEN
300 SELECT 'Y'
301 INTO l_flag
302 FROM dual
303 WHERE NOT EXISTS (SELECT 'x'
304 FROM pon_auction_item_prices_all
305 WHERE group_line_id = p_line_id
306 AND auction_header_id = p_document_id);
307
308
309 --Unsolicited Lines Project
310 ELSIF p_product = 'OFFER' THEN
311 SELECT 'Y'
312 INTO l_flag
313 FROM dual
314 WHERE NOT EXISTS (SELECT 'x'
315 FROM pon_bid_item_prices
316 WHERE bid_number = p_document_id
317 AND group_line_id = p_line_id);
318
319
320 END IF;
321
322 RETURN l_flag;
323 EXCEPTION
324 WHEN No_Data_Found THEN
325 RETURN 'N';
326 WHEN OTHERS THEN
327 RETURN 'N';
328 END isclinvalidformove;
329
330 FUNCTION Get_group_line_id
331 (p_product IN VARCHAR2,
332 p_document_id IN NUMBER DEFAULT NULL,
333 p_group_line_id IN VARCHAR2)
334 RETURN NUMBER
335 IS
336 l_group_line_id NUMBER := NULL;
337 BEGIN
338 IF p_product = 'PO' THEN
339 SELECT group_line_id
340 INTO l_group_line_id
341 FROM po_lines_merge_v
342 WHERE po_line_id = p_group_line_id AND draft_id = -1;
343 ELSIF p_product = 'ICX' THEN
344 SELECT group_line_id
345 INTO l_group_line_id
346 FROM po_requisition_lines_all
347 WHERE requisition_line_id = p_group_line_id;
348 ELSIF p_product = 'PON' THEN
349 SELECT group_line_id
350 INTO l_group_line_id
351 FROM pon_auction_item_prices_all
352 WHERE auction_header_id = p_document_id
356 INTO l_group_line_id
353 AND line_number = p_group_line_id;
354 ELSIF p_product = 'PON BID' THEN
355 SELECT group_line_id
357 FROM pon_bid_item_prices
358 WHERE auction_header_id = p_document_id
359 AND bid_number = p_group_line_id;
360 END IF;
361
362 RETURN l_group_line_id;
363 EXCEPTION
364 WHEN OTHERS THEN
365 NULL;
366 END get_group_line_id;
367
368
369 PROCEDURE Check_Uda_Enabled
370 (
371 p_functional_area_code IN VARCHAR2
372 ,p_document_type_code IN VARCHAR2
373 ,p_document_style_id IN NUMBER
374 ,x_enabled_flag OUT NOCOPY VARCHAR2
375 ,x_return_status OUT NOCOPY VARCHAR2
376 ,x_errorcode OUT NOCOPY NUMBER
377 ,x_msg_count OUT NOCOPY NUMBER
378 ,x_msg_data OUT NOCOPY VARCHAR2
379 )
380 IS
381 d_progress NUMBER := 0;
382
383 BEGIN
384
385 --kkolukul: Fix for bug 9298800
386 IF( p_functional_area_code = 'REQUISITIONS') THEN
387
388 x_enabled_flag := por_util_pkg.get_federal_enabled_flag;
389 ELSIF p_functional_area_code = 'PURCHASING' THEN
390 SELECT NVL(CLM_FLAG, 'N')
391 INTO x_enabled_flag
392 FROM PO_DOC_STYLE_HEADERS
393 WHERE STYLE_ID = p_document_style_id;
394
395 ELSE
396 x_enabled_flag := 'Y';
397
398 END IF;
399
400 x_return_status := FND_API.G_RET_STS_SUCCESS;
401
402 EXCEPTION
403 WHEN OTHERS THEN
404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
405
406 END Check_Uda_Enabled;
407
408
409 /*To check whether uda is enabled*/
410 PROCEDURE check_po_uda_enabled
411 (
412 p_document_type_code IN VARCHAR2,
413 p_document_style_id IN VARCHAR2,
414 x_enabled_flag OUT NOCOPY VARCHAR2,
415 x_return_status OUT NOCOPY VARCHAR2
416 )
417 IS
418 d_progress NUMBER := 0;
419 l_errorcode NUMBER;
420 l_msg_count NUMBER;
421 l_msg_data VARCHAR2(2000);
422
423 BEGIN
424
425 check_uda_enabled(p_functional_area_code => 'PURCHASING',
426 p_document_type_code => p_document_type_code, --<Bug 14837341>
427 p_document_style_id => p_document_style_id,
428 x_enabled_flag => x_enabled_flag,
429 x_return_status => x_return_status,
430 x_errorcode => l_errorcode,
431 x_msg_count => l_msg_count,
432 x_msg_data => l_msg_data
433 );
434
435 EXCEPTION
436 WHEN OTHERS THEN
437 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
438
439 END check_po_uda_enabled;
440
441
442 FUNCTION Isvalidtomove
443 (p_product IN VARCHAR2,
444 p_action IN VARCHAR2,
445 p_document_id IN NUMBER DEFAULT NULL,
446 p_from_line_id IN NUMBER,
447 p_from_group_line_id IN NUMBER,
448 p_from_clm_info_flag IN VARCHAR2,
449 p_from_clm_option_indicator IN VARCHAR2,
450 p_from_clm_base_line_num IN NUMBER,
451 p_from_clm_option_num IN NUMBER,
452 p_from_clm_option_from_date IN DATE,
453 p_from_clm_option_to_date IN DATE,
454 p_from_clm_funded_flag IN VARCHAR2,
455 p_to_line_id IN NUMBER,
456 p_to_group_line_id IN NUMBER,
457 p_to_clm_info_flag IN VARCHAR2,
458 p_to_clm_option_indicator IN VARCHAR2,
459 p_to_clm_base_line_num IN NUMBER,
460 p_to_clm_option_num IN NUMBER,
461 p_to_clm_option_from_date IN DATE,
462 p_to_clm_option_to_date IN DATE,
463 p_to_clm_funded_flag IN VARCHAR2)
464 RETURN VARCHAR2
465 IS
466
467 l_group_line_id NUMBER;
468 l_clm_info_flag VARCHAR2(1);
469 l_clm_funded_flag VARCHAR2(1);
470
471 BEGIN
472 -- Do not show option lines
473
474 IF p_from_line_id = p_to_line_id THEN
475 RETURN 'N';
476 END IF;
477
478 IF(Nvl(p_from_clm_option_indicator,'N') = 'O' AND
479 p_from_clm_base_line_num is not null) THEN
480 RETURN 'N';
481 END IF;
482
483 --IS_FROM_TO_VALID
484 IF (p_product = 'PON'
485 AND p_action IN ('BEFORE','AFTER')
486 AND IS_FROM_TO_VALID(p_from_line_id,
487 p_to_line_id,
488 p_action,
489 p_document_id) = 'NO') THEN
490
491 RETURN 'N';
492 END IF;
493
494 -- Unsolicited Lines Project
495 IF (p_product = 'OFFER'
496 AND p_action IN ('BEFORE','AFTER')
497 AND IS_UNSOL_FROM_TO_VALID(p_from_line_id,
498 p_to_line_id,
499 p_action,
500 p_document_id) = 'NO') THEN
501
502 RETURN 'N';
503 END IF;
504
505 /* moving a normal clin/slin before or after then show all normal clins start*/
506 IF p_action IN ('BEFORE','AFTER')
507 AND p_from_clm_base_line_num IS NULL THEN
508 IF p_to_clm_base_line_num IS NOT NULL THEN
509 RETURN 'N';
510 END IF;
511
512
516
513 IF ( p_from_group_line_id = p_to_group_line_id AND p_from_clm_info_flag <> p_to_clm_info_flag ) THEN
514 RETURN 'N'; -- dont move priced slins before/after info slins
515 END IF;
517 IF (p_from_group_line_id IS NOT NULL AND Nvl(isOptionExist(p_product,p_document_id,p_from_line_id),'N') = 'Y'
518 AND p_to_group_line_id IS NULL) THEN
519 RETURN 'N'; -- A base slin can't be moved out of its clin hierarchy
520 END IF;
521
522 IF p_from_group_line_id IS NULL
523 AND p_to_group_line_id IS NULL
524 AND p_to_clm_base_line_num IS NULL THEN
525 RETURN 'Y';
526 END IF;
527
528 IF p_from_group_line_id IS NOT NULL
529 AND p_to_group_line_id IS NULL
530 AND p_to_clm_base_line_num IS NULL THEN
531 RETURN 'Y';
532 END IF;
533
534 IF p_from_group_line_id IS NOT NULL
535 AND p_to_group_line_id = p_from_group_line_id
536 AND p_to_clm_base_line_num IS NULL THEN
537 RETURN 'Y';
538 END IF;
539
540 RETURN 'N';
541 END IF;
542
543 /* moving a normal clin/slin before or after then show all normal clins end*/
544
545 /* Moving a Normal clin/slin as slin start*/
546 IF p_action = 'AS_SLIN'
547 AND p_from_clm_base_line_num IS NULL THEN
548
549 IF p_to_clm_base_line_num IS NOT NULL THEN
550 RETURN 'N';
551 END IF;
552
553 IF NOT validateInfoflags(p_from_clm_info_flag,p_to_clm_info_flag)
554 OR NOT valdiateFundFalgs(p_from_clm_funded_flag,p_to_clm_funded_flag) THEN
555 RETURN 'N';
556 END IF;
557
558 IF p_from_group_line_id IS NULL THEN
559
560 IF Isclinvalidformove(p_product,p_document_id,p_from_line_id) = 'N'
561 OR isOptionExist(p_product,p_document_id,p_from_line_id) = 'Y'
562 THEN
563 RETURN 'N';
564 END IF;
565
566 IF p_to_group_line_id IS NULL THEN
567
568 /* Unsolicited Lines Project : If OFFER, check for unsol line references.
569 * A Unsolcited CLIN with sol line references cannot be moved into a structure
570 * where clin already has references.
571 */
572 IF PON_UNSOL_UTIL_PKG.HAS_SOL_LINE_REFERENCES(p_document_id,p_from_line_id) = 'Y'
573 AND PON_UNSOL_UTIL_PKG.HAS_SOL_LINE_REFERENCES(p_document_id,p_to_line_id) = 'Y' THEN
574
575 RETURN 'N';
576 END IF;
577
578 RETURN 'Y';
579 END IF;
580 END IF;
581
582
583 IF p_from_group_line_id IS NOT NULL
584 AND p_to_group_line_id IS NULL THEN
585 IF isOptionExist(p_product,p_document_id,p_from_line_id) = 'Y' then
586 return 'N';
587 end if;
588 IF p_from_group_line_id = p_to_line_id THEN
589 RETURN 'N';
590 END IF;
591
592 /* Unsolicited Lines Project : If OFFER, check for unsol line references.
593 * A Unsolcited SLIN with sol line references cannot be moved into a structure
594 * where clin already has references.
595 */
596 IF PON_UNSOL_UTIL_PKG.HAS_SOL_LINE_REFERENCES(p_document_id,p_from_line_id) = 'Y'
597 AND PON_UNSOL_UTIL_PKG.HAS_SOL_LINE_REFERENCES(p_document_id,p_to_line_id) = 'Y' THEN
598
599 RETURN 'N';
600 END IF;
601
602 RETURN 'Y';
603 END IF;
604
605 RETURN 'N';
606 END IF;
607
608 /* Moving a Normal clin/slin as slin end*/
609 /* Moving a Normal clin/slin as option start */
610 IF p_action = 'AS_OPTION'
611 AND p_from_clm_base_line_num IS NULL THEN
612 IF p_to_clm_base_line_num IS NOT NULL THEN
613 RETURN 'N';
614 END IF;
615
616 IF p_from_group_line_id IS NULL THEN
617 IF Isclinvalidformove(p_product,p_document_id,p_from_line_id) = 'N'
618 OR isOptionExist(p_product,p_document_id,p_from_line_id) = 'Y'
619 THEN
620 RETURN 'N';
621 END IF;
622
623 IF p_from_line_id = p_to_group_line_id THEN
624 RETURN 'N';
625 END IF;
626
627 IF p_to_group_line_id IS NOT NULL THEN
628 -- i.e Making normal clin as option to slin if the to line is 'slin'
629 -- Then validate info and funding flags with the parent of the 'to slin'.
630 getclmFlags
631 (p_product => p_product
632 ,p_document_id => p_document_id
633 ,p_line_id => p_to_group_line_id
634 ,x_clm_info_flag => l_clm_info_flag
635 ,x_clm_funded_flag => l_clm_funded_flag);
636
637 IF NOT validateInfoflags(p_from_clm_info_flag,l_clm_info_flag)
638 OR NOT valdiateFundFalgs(p_from_clm_funded_flag,l_clm_funded_flag) THEN
639 RETURN 'N';
640 END IF;
641 END IF;
642 RETURN 'Y';
643 END IF;
644
645 /* Show only base slins from the current clin - option clins are eliminated
646 at the first palce itself */
647 IF p_from_group_line_id IS NOT NULL THEN
648 IF p_to_group_line_id = p_from_group_line_id THEN
649 RETURN 'Y';
650 END IF;
651
652 RETURN 'N';
653 END IF;
654 END IF;
655
656 /* Moving a Normal clin/slin as option end */
657 /* Moving a o-clin/o-slin before,after is an invalid operation start*/
661 IF p_from_group_line_id IS NULL THEN
658 IF p_action IN ('BEFORE','AFTER')
659 AND p_from_clm_base_line_num IS NOT NULL THEN
660
662 IF p_to_group_line_id IS NULL AND p_from_clm_base_line_num=p_to_clm_base_line_num THEN
663 RETURN 'Y';
664 END IF;
665 END IF;
666
667 IF p_from_group_line_id IS NOT NULL THEN
668 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
669 RETURN 'Y';
670 END IF;
671 END IF;
672
673 RETURN 'N';
674 END IF;
675
676 /* Moving a o-clin/o-slin before,after is an invalid operation end*/
677 /* moving a o-clin/slin as slin start*/
678 IF p_action = 'AS_SLIN'
679 AND p_from_clm_base_line_num IS NOT NULL THEN
680 IF p_to_clm_base_line_num IS NOT NULL
681 AND p_to_group_line_id IS NOT NULL THEN
682 RETURN 'N';
683 END IF;
684
685 IF p_from_group_line_id IS NULL THEN
686 RETURN 'N';
687 END IF;
688
689 IF p_from_group_line_id IS NOT NULL THEN
690
691 IF NOT validateInfoflags(p_from_clm_info_flag,p_to_clm_info_flag)
692 OR NOT valdiateFundFalgs(p_from_clm_funded_flag,p_to_clm_funded_flag) THEN
693 RETURN 'N';
694 END IF;
695
696 IF p_to_group_line_id IS NULL
697 AND p_to_clm_base_line_num IS NOT NULL THEN
698 RETURN 'Y';
699 END IF;
700 END IF;
701
702 RETURN 'N';
703 END IF;
704
705 /* moving a o-clin/slin as slin end*/
706 /* moving a o-clin/slin as option start*/
707 IF p_action = 'AS_OPTION'
708 AND p_from_clm_base_line_num IS NOT NULL THEN
709 IF p_to_clm_base_line_num IS NOT NULL THEN
710 RETURN 'N';
711 END IF;
712
713 IF p_from_clm_base_line_num = p_to_line_id THEN
714 RETURN 'N';
715 END IF;
716
717 IF p_from_group_line_id IS NULL THEN
718 RETURN 'Y';
719 END IF;
720
721 IF p_from_group_line_id IS NOT NULL
722 AND p_to_group_line_id = p_from_group_line_id THEN
723 RETURN 'Y';
724 END IF;
725
726 RETURN 'N';
727 END IF;
728
729 /* moving a o-clin/slin as option end*/
730 RETURN 'N';
731 EXCEPTION
732 WHEN OTHERS THEN
733 RETURN 'N';
734 END isvalidtomove;
735
736 FUNCTION PO_HEADERS_ALL_DATA_SEC (object_schema in varchar2, object_name varchar2) return varchar2 AS
737 ProfileValue VARCHAR2(1);
738 p_user_id NUMBER;
739 p_resp_id NUMBER;
740 p_appl_id NUMBER;
741 p_emp_id NUMBER;
742 p_proc_name VARCHAR2(100) := 'PO.PLSQL.PO_HEADERS_ALL_DATA_SEC';
743 BEGIN
744 p_user_id := fnd_global.user_id;
745 p_resp_id := fnd_global.resp_id;
746 p_appl_id := fnd_global.resp_appl_id;
747 SELECT Nvl(employee_id,-999) INTO p_emp_id FROM fnd_user WHERE user_id = p_user_id;
748
749 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
750 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'Enter PO_HEADERS_ALL_DATA_SEC Procedure');
751 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'p_user_id : ' || p_user_id);
752 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'p_resp_id : ' || p_resp_id);
753 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'p_appl_id : ' || p_appl_id);
754 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'p_emp_id : ' || p_emp_id);
755 END IF;
756
757 IF p_emp_id <> -999 THEN
758 ProfileValue := PO_CLM_CLO_UTIL.checkCLMResp(p_user_id, p_resp_id, p_appl_id);
759
760 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
761 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'ProfileValue : '||ProfileValue);
762 END IF;
763
764 IF ProfileValue <> 'N' THEN
765 return ' EXISTS (SELECT REF.STYLE_ID FROM PO_DOC_STYLE_HEADERS REF WHERE
766 REF.STYLE_ID = PO_HEADERS_ALL_SEC.STYLE_ID AND REF.CLM_FLAG=''Y'') ';
767 ELSE
768 return ' EXISTS (SELECT REF.STYLE_ID FROM PO_DOC_STYLE_HEADERS REF WHERE
769 REF.STYLE_ID = PO_HEADERS_ALL_SEC.STYLE_ID AND NVL(REF.CLM_FLAG,''N'') = ''N'') ';
770 END IF;
771 ELSE
772 return ' 1=1 ';
773 END IF;
774
775 EXCEPTION WHEN OTHERS THEN
776 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
777 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'Error : '||SQLERRM);
778 END IF;
779 END PO_HEADERS_ALL_DATA_SEC;
780
781 FUNCTION OKC_BUS_DOC_TYPES_DATA_SEC (object_schema in varchar2, object_name varchar2) return varchar2 AS
782 ProfileValue VARCHAR2(1);
783 p_user_id NUMBER;
784 p_resp_id NUMBER;
785 p_appl_id NUMBER;
786 p_emp_id NUMBER;
787 p_proc_name VARCHAR2(100) := 'OKC.PLSQL.OKC_BUS_DOC_TYPES_DATA_SEC';
788 BEGIN
789 p_user_id := fnd_global.user_id;
790 p_resp_id := fnd_global.resp_id;
791 p_appl_id := fnd_global.resp_appl_id;
792 SELECT Nvl(employee_id,-999) INTO p_emp_id FROM fnd_user WHERE user_id = p_user_id;
793
794 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
795 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'Enter OKC_BUS_DOC_TYPES_DATA_SEC Procedure');
799 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'p_emp_id : ' || p_emp_id);
796 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'p_user_id : ' || p_user_id);
797 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'p_resp_id : ' || p_resp_id);
798 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'p_appl_id : ' || p_appl_id);
800 END IF;
801
802 IF p_emp_id <> -999 THEN
803 IF (p_appl_id = 396 OR p_appl_id = 201) THEN
804 ProfileValue := PO_CLM_CLO_UTIL.checkCLMResp(p_user_id, p_resp_id, p_appl_id);
805
806 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
807 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'ProfileValue : '||ProfileValue);
808 END IF;
809
810 IF ProfileValue <> 'N' THEN
811 return ' OKC_BUS_DOC_TYPES_SEC.DOCUMENT_TYPE_CLASS IN (''SOURCING'',''PO'') AND
812 OKC_BUS_DOC_TYPES_SEC.DOCUMENT_TYPE IN (''PA_BLANKET'',''PA_CONTRACT'',''PO_STANDARD'',
813 ''PA_BLANKET_MOD'',''PA_CONTRACT_MOD'',''PO_STANDARD_MOD'',''SOLICITATION_RESPONSE'')';
814 ELSE
815 return ' OKC_BUS_DOC_TYPES_SEC.DOCUMENT_TYPE_CLASS IN (''SOURCING'',''PO'') AND
816 OKC_BUS_DOC_TYPES_SEC.DOCUMENT_TYPE IN (''PA_BLANKET'',''PA_CONTRACT'',''PO_STANDARD'',
817 ''AUCTION_RESPONSE'',''RFI_RESPONSE'',''RFQ_RESPONSE'')';
818 END IF;
819 ELSE
820 return ' 1=1 ';
821 END IF;
822 ELSE
823 return ' 1=1 ';
824 END IF;
825
826 EXCEPTION WHEN OTHERS THEN
827 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
828 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'Error : '||SQLERRM);
829 END IF;
830 END OKC_BUS_DOC_TYPES_DATA_SEC;
831
832
833
834 FUNCTION OKC_DELIVERABLES_DATA_SEC (object_schema in varchar2, object_name varchar2) return varchar2 AS
835 ProfileValue VARCHAR2(1);
836 p_user_id NUMBER;
837 p_resp_id NUMBER;
838 p_appl_id NUMBER;
839 p_emp_id NUMBER;
840 p_proc_name VARCHAR2(100) := 'OKC.PLSQL.OKC_DELIVERABLES_DATA_SEC';
841 BEGIN
842 p_user_id := fnd_global.user_id;
843 p_resp_id := fnd_global.resp_id;
844 p_appl_id := fnd_global.resp_appl_id;
845 SELECT Nvl(employee_id,-999) INTO p_emp_id FROM fnd_user WHERE user_id = p_user_id;
846
847 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
848 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'Enter OKC_DELIVERABLES_DATA_SEC Procedure');
849 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'p_user_id : ' || p_user_id);
850 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'p_resp_id : ' || p_resp_id);
851 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'p_appl_id : ' || p_appl_id);
852 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'p_emp_id : ' || p_emp_id);
853 END IF;
854
855 IF p_emp_id <> -999 THEN
856 ProfileValue := PO_CLM_CLO_UTIL.checkCLMResp(p_user_id, p_resp_id, p_appl_id);
857
858 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
859 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'ProfileValue : '||ProfileValue);
860 END IF;
861
862 IF p_appl_id = 201 THEN
863 return ' EXISTS (SELECT POH.PO_HEADER_ID FROM PO_HEADERS_ALL_SEC POH
864 WHERE POH.PO_HEADER_ID = OKC_DELIVERABLES_SEC.BUSINESS_DOCUMENT_ID) ';
865 ELSIF p_appl_id = 396 THEN
866 return ' exists ( SELECT PAH.AUCTION_HEADER_ID FROM PON_AUCTION_HEADERS_ALL PAH
867 WHERE PAH.AUCTION_HEADER_ID = OKC_DELIVERABLES_SEC.BUSINESS_DOCUMENT_ID) ';
868 ELSE
869 return ' 1=1 ';
870 END IF;
871 ELSE
872 return ' 1=1 ';
873 END IF;
874
875 EXCEPTION WHEN OTHERS THEN
876 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
877 fnd_log.string(FND_LOG.LEVEL_PROCEDURE, p_proc_name || '.invoked', 'Error : '||SQLERRM);
878 END IF;
879 END OKC_DELIVERABLES_DATA_SEC;
880
881 FUNCTION IS_FROM_TO_VALID (p_from_line_id IN NUMBER,
882 p_to_line_id IN NUMBER,
883 p_action IN VARCHAR,
884 p_document_id IN NUMBER) RETURN VARCHAR2 AS
885
886 l_from_disp_line_num VARCHAR2(6);
887 l_to_disp_line_num VARCHAR2(6);
888 l_pred_line_id VARCHAR2(6);
889 l_succ_line_id VARCHAR2(6);
890 l_from_group_line_id NUMBER;
891 l_to_group_line_id NUMBER;
892 BEGIN
893
894 SELECT line_num_display,group_line_id
895 INTO l_from_disp_line_num,l_from_group_line_id
896 FROM pon_auction_item_prices_all
897 WHERE line_number = p_from_line_id
898 AND auction_header_id = p_document_id;
899
900
901 SELECT line_num_display,group_line_id
902 INTO l_to_disp_line_num ,l_to_group_line_id
903 FROM pon_auction_item_prices_all
904 WHERE line_number = p_to_line_id
905 AND auction_header_id = p_document_id;
906
907 IF p_action = 'BEFORE' THEN
908 SELECT line_num_display
909 INTO l_succ_line_id
910 FROM
911
912 (SELECT line_num_display
913 FROM pon_auction_item_prices_all
914 WHERE auction_header_id = p_document_id
915 AND Nvl(l_from_GROUP_LINE_ID,-1) = Nvl(GROUP_LINE_ID,-1)
916 AND line_num_display > l_from_disp_line_num
920 WHERE ROWNUM = 1;
917
918 ORDER BY line_num_display)
919
921
922 IF ( l_to_disp_line_num = l_succ_line_id)
923 THEN
924 RETURN 'NO';
925
926 ELSE
927 RETURN 'NONE';
928 END IF;
929 END IF;
930
931 IF p_action = 'AFTER' THEN
932 SELECT line_num_display
933 INTO l_pred_line_id
934 FROM
935
936 (SELECT line_num_display
937 FROM pon_auction_item_prices_all
938 WHERE auction_header_id = p_document_id
939 AND Nvl(l_from_GROUP_LINE_ID,-1) = Nvl(group_line_id,-1)
940 AND line_num_display < l_from_disp_line_num
941
942 ORDER BY line_num_display DESC)
943
944 WHERE ROWNUM = 1;
945
946 IF ( l_pred_line_id = l_to_disp_line_num)
947 THEN
948 RETURN 'NO';
949
950 ELSE
951 RETURN 'NONE';
952 END IF;
953 END IF;
954
955 EXCEPTION WHEN No_Data_Found THEN
956
957 RETURN 'NONE';
958
959 END IS_FROM_TO_VALID;
960
961 FUNCTION IS_UNSOL_FROM_TO_VALID (p_from_line_id IN NUMBER,
962 p_to_line_id IN NUMBER,
963 p_action IN VARCHAR,
964 p_document_id IN NUMBER) RETURN VARCHAR2 AS
965
966 l_from_disp_line_num VARCHAR2(6);
967 l_to_disp_line_num VARCHAR2(6);
968 l_pred_line_id VARCHAR2(6);
969 l_succ_line_id VARCHAR2(6);
970 l_from_group_line_id NUMBER;
971 l_to_group_line_id NUMBER;
972 BEGIN
973
974 SELECT line_num_display,group_line_id
975 INTO l_from_disp_line_num,l_from_group_line_id
976 FROM pon_bid_item_prices
977 WHERE bid_number = p_document_id
978 AND line_number = p_from_line_id
979 AND auction_line_number = -1;
980
981
982 SELECT line_num_display,group_line_id
983 INTO l_to_disp_line_num ,l_to_group_line_id
984 FROM pon_bid_item_prices
985 WHERE bid_number = p_document_id
986 AND line_number = p_to_line_id
987 AND auction_line_number = -1;
988
989 IF p_action = 'BEFORE' THEN
990 SELECT line_num_display
991 INTO l_succ_line_id
992 FROM
993
994 (SELECT line_num_display
995 FROM pon_bid_item_prices
996 WHERE bid_number = p_document_id
997 AND auction_line_number = -1
998 AND Nvl(l_from_GROUP_LINE_ID,-1) = Nvl(GROUP_LINE_ID,-1)
999 AND line_num_display > l_from_disp_line_num
1000
1001 ORDER BY line_num_display)
1002
1003 WHERE ROWNUM = 1;
1004
1005 IF ( l_to_disp_line_num = l_succ_line_id)
1006 THEN
1007 RETURN 'NO';
1008
1009 ELSE
1010 RETURN 'NONE';
1011 END IF;
1012 END IF;
1013
1014 IF p_action = 'AFTER' THEN
1015 SELECT line_num_display
1016 INTO l_pred_line_id
1017 FROM
1018
1019 (SELECT line_num_display
1020 FROM pon_bid_item_prices
1021 WHERE bid_number = p_document_id
1022 AND auction_line_number = -1
1023 AND Nvl(l_from_GROUP_LINE_ID,-1) = Nvl(group_line_id,-1)
1024 AND line_num_display < l_from_disp_line_num
1025
1026 ORDER BY line_num_display DESC)
1027
1028 WHERE ROWNUM = 1;
1029
1030 IF ( l_pred_line_id = l_to_disp_line_num)
1031 THEN
1032 RETURN 'NO';
1033
1034 ELSE
1035 RETURN 'NONE';
1036 END IF;
1037 END IF;
1038
1039 EXCEPTION WHEN No_Data_Found THEN
1040
1041 RETURN 'NONE';
1042
1043 END IS_UNSOL_FROM_TO_VALID;
1044
1045
1046 END;
[Home] [Help]