[Home] [Help]
PACKAGE BODY: APPS.GMF_VALIDATIONS_PVT
Source
1 PACKAGE BODY GMF_validations_PVT AS
2 /* $Header: GMFVVALB.pls 120.5.12020000.2 2012/07/24 15:05:56 spabolu ship $ */
3
4 -- Start of comments
5 --+==========================================================================+
6 --| Copyright (c) 1998 Oracle Corporation |
7 --| Redwood Shores, CA, USA |
8 --| All rights reserved. |
9 --+==========================================================================+
10 --| File Name : GMFPVALS.pls |
11 --| Package Name : GMF_Validations_PVT |
12 --| API name : GMF_Validations_PVT |
13 --| Type : Public |
14 --| Pre-reqs : N/A |
15 --| Function : This package contains generic validations functions |
16 --| and procedures for masters. |
17 --| Parameters : N/A |
18 --| |
19 --| Current Vers : 1.0 |
20 --| Previous Vers : None |
21 --| Initial Vers : None |
22 --| |
23 --| Contents |
24 --| FUNCTION Validate_Calendar_Code |
25 --| PROCEDURE Validate_Calendar_Code |
26 --| FUNCTION Validate_Period_Code |
27 --| FUNCTION Validate_Cost_Mthd_Code |
28 --| PROCEDURE Validate_Cost_Mthd_Code |
29 --| FUNCTION Validate_Cost_Analysis_Code |
30 --| FUNCTION Validate_Company_Code |
31 --| FUNCTION Validate_Orgn_Code |
32 --| FUNCTION Validate_Whse_Code |
33 --| FUNCTION Validate_Item_Id |
34 --| PROCEDURE Validate_Item_Id |
35 --| FUNCTION Validate_Item_No |
36 --| PROCEDURE Validate_Item_No |
37 --| FUNCTION Fecth_Item_Id |
38 --| FUNCTION Validate_Itemcost_Class |
39 --| FUNCTION Validate_Cost_Cmpntcls_Id |
40 --| PROCEDURE Validate_Cost_Cmpntcls_Id |
41 --| FUNCTION Validate_Cost_Cmpntcls_Code |
42 --| PROCEDURE Validate_Cost_Cmpntcls_Code |
43 --| FUNCTION Fetch_Cmpntcls_Id |
44 --| FUNCTION Validate_Gl_Class |
45 --| FUNCTION Validate_Fmeff_Id |
46 --| FUNCTION Validate_Resources |
47 --| PROCEDURE Validate_Resources |
48 --| FUNCTION Validate_Alloc_Id |
49 --| FUNCTION Validate_Alloc_Code |
50 --| FUNCTION Validate_Text_Code |
51 --| FUNCTION Validate_Basis_Account_Key |
52 --| FUNCTION Validate_Usage_um |
53 --| |
54 --| Notes |
55 --| All the foreign key validations to masters will be added |
56 --| |
57 --| HISTORY |
58 --| 27/Feb/2001 Uday Moogala Created Bug# 1418689 |
59 --| 30-OCT-2002 RajaSekhar Bug#2641405 Added NOCOPY hint. |
60 --| 27/10/2003 Uday Moogla - Log error if lot cost method is passed. |
61 --| 31-MAY-2012 Saptagirish Pabolu Bug#14101225 |
62 --| Added a new function Validate_Uom_Conv|
63 --| |
64 --+==========================================================================+
65 -- End of comments
66
67 -- Func start of comments
68 --+==========================================================================+
69 --| FUNCTION NAME |
70 --| Validate_Calendar_Code |
71 --| |
72 --| USAGE |
73 --| Validates Calendar Code |
74 --| |
75 --| DESCRIPTION |
76 --| This function validates that the Calendar Code exists in |
77 --| on cm_cldr_hdr |
78 --| |
79 --| PARAMETERS |
80 --| p_calendar_code IN VARCHAR2(4) - Calendar Method Code |
81 --| |
82 --| RETURNS |
83 --| TRUE - If Calendar Code exists |
84 --| FALSE - If Calendar Code does not exists |
85 --| |
86 --| HISTORY |
87 --| 27/02/2001 Uday Moogla - Created |
88 --| |
89 --+==========================================================================+
90 -- Func end of comments
91
92 FUNCTION Validate_Calendar_Code
93 (
94 p_calendar_code IN cm_cldr_hdr.Calendar_Code%TYPE
95 )
96 RETURN BOOLEAN
97 IS
98 CURSOR cur_cm_cldr_hdr
99 IS
100 SELECT
101 calendar_code
102 FROM
103 cm_cldr_hdr
104 WHERE
105 calendar_code = p_calendar_code
106 AND delete_mark = 0;
107
108 l_calendar_code cm_cldr_hdr.Calendar_Code%TYPE ;
109
110 BEGIN
111
112 OPEN cur_cm_cldr_hdr;
113 FETCH cur_cm_cldr_hdr INTO l_calendar_code;
114 IF (cur_cm_cldr_hdr%NOTFOUND)
115 THEN
116 CLOSE cur_cm_cldr_hdr;
117 RETURN FALSE;
118 ELSE
119 CLOSE cur_cm_cldr_hdr;
120 RETURN TRUE;
121 END IF;
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 RAISE;
126
127 END Validate_Calendar_Code;
128
129 -- Func start of comments
130 --+==========================================================================+
131 --| PROCEDURE NAME |
132 --| Validate_Calendar_Code |
133 --| |
134 --| USAGE |
135 --| Validates Calendar Code |
136 --| |
137 --| DESCRIPTION |
138 --| This function validates that the Calendar Code exists in |
139 --| on cm_cldr_hdr and returns co_code and cost method |
140 --| |
141 --| PARAMETERS |
142 --| p_calendar_code IN VARCHAR2(4) - Calendar Method Code |
143 --| |
144 --| RETURNS |
145 --| |
146 --| HISTORY |
147 --| 27/02/2001 Uday Moogla - Created |
148 --| |
149 --+==========================================================================+
150 -- Func end of comments
151
152 PROCEDURE Validate_Calendar_Code
153 (
154 p_calendar_code IN cm_cldr_hdr.Calendar_Code%TYPE
155 , x_co_code OUT NOCOPY cm_cldr_hdr.co_code%TYPE
156 , x_cost_mthd_code OUT NOCOPY cm_cldr_hdr.cost_mthd_code%TYPE
157 )
158 IS
159 CURSOR cur_cm_cldr_hdr
160 IS
161 SELECT
162 co_code, cost_mthd_code
163 FROM
164 cm_cldr_hdr
165 WHERE
166 calendar_code = p_calendar_code
167 AND delete_mark = 0;
168
169 BEGIN
170
171 OPEN cur_cm_cldr_hdr;
172 FETCH cur_cm_cldr_hdr INTO x_co_code, x_cost_mthd_code;
173 CLOSE cur_cm_cldr_hdr;
174 /*
175 IF (cur_cm_cldr_hdr%NOTFOUND)
176 THEN
177 CLOSE cur_cm_cldr_hdr;
178 RETURN FALSE;
179 ELSE
180 CLOSE cur_cm_cldr_hdr;
181 RETURN TRUE;
182 END IF;
183 */
184 EXCEPTION
185 WHEN OTHERS THEN
186 RAISE;
187
188 END Validate_Calendar_Code;
189
190 -- Func start of comments
191 --+==========================================================================+
192 --| PROCEDURE NAME |
193 --| Validate_Period_Code |
194 --| |
195 --| USAGE |
196 --| Validates Period Code |
197 --| |
198 --| DESCRIPTION |
199 --| This function validates that the Period Code exists in |
200 --| on cm_cldr_dtl |
201 --| |
202 --| PARAMETERS |
203 --| p_calendar_code IN VARCHAR2(4) - Calendar Method Code |
204 --| p_period_code IN VARCHAR2(4) - Period Code |
205 --| |
206 --| RETURNS |
207 --| period_status - |
208 --| |
209 --| HISTORY |
210 --| 27/02/2001 Uday Moogla - Created |
211 --| |
212 --+==========================================================================+
213 -- Func end of comments
214
215 PROCEDURE Validate_Period_Code
216 (
217 p_Calendar_Code IN cm_cldr_hdr.Calendar_Code%TYPE,
218 p_Period_Code IN cm_cldr_dtl.Period_Code%TYPE,
219 x_Period_Status OUT NOCOPY cm_cldr_dtl.period_status%TYPE
220 )
221 IS
222 CURSOR cur_cm_cldr_dtl
223 IS
224 SELECT
225 period_status
226 FROM
227 cm_cldr_dtl
228 WHERE
229 calendar_code = p_calendar_code
230 AND period_code = p_period_code
231 AND delete_mark = 0;
232
233 BEGIN
234
235 OPEN cur_cm_cldr_dtl;
236 FETCH cur_cm_cldr_dtl INTO x_period_status;
237 CLOSE cur_cm_cldr_dtl;
238 /*
239 IF (cur_cm_cldr_dtl%NOTFOUND)
240 THEN
241 CLOSE cur_cm_cldr_dtl;
242 RETURN FALSE;
243 ELSE
244 CLOSE cur_cm_cldr_dtl;
245 RETURN TRUE;
246 END IF;
247 */
248 EXCEPTION
249 WHEN OTHERS THEN
250 RAISE;
251
252 End Validate_Period_Code;
253 --
254
255 -- Func start of comments
256 --+==========================================================================+
257 --| FUNCTION NAME |
258 --| Validate_cost_mthd_code |
259 --| |
260 --| USAGE |
261 --| Validates cost_mthd_code |
262 --| |
263 --| DESCRIPTION |
264 --| This function validates that the cost Method Code exists |
265 --| on cm_mthd_mst |
266 --| |
267 --| PARAMETERS |
268 --| p_cost_mthd_code IN VARCHAR2(4) - Cost Method Code |
269 --| |
270 --| RETURNS |
271 --| TRUE - If Cost Method contains a valid value |
272 --| FALSE - If Cost Method contains an invalid value |
273 --| |
274 --| HISTORY |
275 --| 27/02/2001 Uday Moogla - Created |
276 --| 27/10/2003 Uday Moogla - Log error if lot cost method is passed. |
277 --| |
278 --+==========================================================================+
279 -- Func end of comments
280
281 FUNCTION Validate_cost_mthd_code (
282 p_cost_mthd_code IN ic_item_mst.cost_mthd_code%TYPE
283 )
284 RETURN BOOLEAN
285 IS
286 CURSOR cur_cm_mthd_mst
287 IS
288 SELECT
289 cost_mthd_code, lot_actual_cost
290 FROM
291 cm_mthd_mst
292 WHERE
293 cm_mthd_mst.cost_mthd_code = p_cost_mthd_code
294 AND cm_mthd_mst.delete_mark = 0;
295
296 l_cost_mthd_code ic_item_mst.cost_mthd_code%TYPE;
297 l_lot_actual_cost cm_mthd_mst.lot_actual_cost%TYPE;
298
299 BEGIN
300
301 OPEN cur_cm_mthd_mst;
302 FETCH cur_cm_mthd_mst INTO l_cost_mthd_code, l_lot_actual_cost;
303 IF (cur_cm_mthd_mst%NOTFOUND)
304 THEN
305 CLOSE cur_cm_mthd_mst;
306 RETURN FALSE;
307 ELSE
308 CLOSE cur_cm_mthd_mst;
309 IF l_lot_actual_cost = 1 THEN
310 FND_MESSAGE.SET_NAME('GMF','GMF_API_LOTCOST_MTHD_UNSUPP');
311 FND_MESSAGE.SET_TOKEN('COST_MTHD_CODE',p_cost_mthd_code);
312 FND_MSG_PUB.Add;
313 RETURN FALSE;
314 ELSE
315 RETURN TRUE;
316 END IF;
317 END IF;
318
319 EXCEPTION
320 WHEN OTHERS THEN
321 RAISE;
322
323 END Validate_cost_mthd_code;
324
325 -- Func start of comments
326 --+==========================================================================+
327 --| PROCEDURE NAME |
328 --| Validate_cost_mthd_code |
329 --| |
330 --| USAGE |
331 --| Validates cost_mthd_code |
332 --| |
333 --| DESCRIPTION |
334 --| This function validates that the cost Method Code exists |
335 --| on cm_mthd_mst and returns cost_type, rmcalc_type and prodcalc_type|
336 --| |
337 --| PARAMETERS |
338 --| p_cost_mthd_code IN VARCHAR2(4) - Cost Method Code |
339 --| x_cost_type OUT NUMBER - Cost Type |
340 --| x_rmcalc_type OUT NUMBER |
341 --| x_prodcalc_type OUT NUMBER |
342 --| |
343 --| RETURNS |
344 --| x_cost_type OUT NUMBER - Cost Type |
345 --| x_rmcalc_type OUT NUMBER |
346 --| x_prodcalc_type OUT NUMBER |
347 --| |
348 --| HISTORY |
349 --| 27/02/2001 Uday Moogla - Created |
350 --| 27/10/2003 Uday Moogla - Log error if lot cost method is passed. |
351 --| |
352 --+==========================================================================+
353 -- Func end of comments
354
355 PROCEDURE Validate_cost_mthd_code
356 (
357 p_cost_mthd_code IN cm_mthd_mst.cost_mthd_code%TYPE,
358 x_cost_type OUT NOCOPY cm_mthd_mst.cost_type%TYPE,
359 x_rmcalc_type OUT NOCOPY cm_mthd_mst.rmcalc_type%TYPE,
360 x_prodcalc_type OUT NOCOPY cm_mthd_mst.prodcalc_type%TYPE
361 )
362 IS
363 CURSOR cur_cm_mthd_mst
364 IS
365 SELECT
366 cost_type, rmcalc_type, prodcalc_type, lot_actual_cost
367 FROM
368 cm_mthd_mst
369 WHERE
370 cm_mthd_mst.cost_mthd_code = p_cost_mthd_code
371 AND cm_mthd_mst.delete_mark = 0;
372
373 l_cost_mthd_code ic_item_mst.cost_mthd_code%TYPE;
374 l_lot_actual_cost cm_mthd_mst.lot_actual_cost%TYPE;
375
376 BEGIN
377
378 OPEN cur_cm_mthd_mst;
379 FETCH cur_cm_mthd_mst INTO x_cost_type, x_rmcalc_type, x_prodcalc_type, l_lot_actual_cost ;
380 CLOSE cur_cm_mthd_mst;
381
382 IF l_lot_actual_cost = 1 THEN
383 FND_MESSAGE.SET_NAME('GMF','GMF_API_LOTCOST_MTHD_UNSUPP');
384 FND_MESSAGE.SET_TOKEN('COST_MTHD_CODE',p_cost_mthd_code);
385 FND_MSG_PUB.Add;
386 x_cost_type := NULL;
387 x_rmcalc_type := NULL;
388 x_prodcalc_type := NULL;
389 END IF;
390 /*
391 IF (cur_cm_mthd_mst%NOTFOUND)
392 THEN
393 CLOSE cur_cm_mthd_mst;
394 RETURN FALSE;
395 ELSE
396 CLOSE cur_cm_mthd_mst;
397 RETURN TRUE;
398 END IF;
399 */
400 EXCEPTION
401 WHEN OTHERS THEN
402 RAISE;
403
404 END Validate_cost_mthd_code;
405
406 -- Func start of comments
407 --+==========================================================================+
408 --| FUNCTION NAME |
409 --| Validate_Analysis_Code |
410 --| |
411 --| USAGE |
412 --| Validates Cost Analysis Code |
413 --| |
414 --| DESCRIPTION |
415 --| This function validates that the Cost Analysis Code exists |
416 --| on cm_alys_mst |
417 --| |
418 --| PARAMETERS |
419 --| p_Cost_Analysis_Code IN VARCHAR2(4) - Cost Analysis Code |
420 --| |
421 --| RETURNS |
422 --| TRUE - If Cost Analysis Code contains a valid value |
423 --| FALSE - If Cost Analysis Code contains an invalid value |
424 --| |
425 --| HISTORY |
426 --| 27/02/2001 Uday Moogla - Created |
427 --| |
428 --+==========================================================================+
429 -- Func end of comments
430
431 FUNCTION Validate_Analysis_Code
432 (
433 p_Cost_Analysis_Code IN cm_alys_mst.Cost_Analysis_Code%TYPE
434 )
435 RETURN BOOLEAN
436 IS
437 CURSOR Cur_analysis_code
438 IS
439 SELECT
440 cost_analysis_code
441 FROM
442 cm_alys_mst
443 WHERE
444 cost_analysis_code = p_Cost_Analysis_Code
445 AND delete_mark = 0;
446
447 l_Cost_Analysis_Code cm_alys_mst.Cost_Analysis_Code%TYPE ;
448
449 BEGIN
450
451 OPEN Cur_analysis_code;
452 FETCH Cur_analysis_code INTO l_Cost_Analysis_Code;
453 IF (Cur_analysis_code%NOTFOUND)
454 THEN
455 CLOSE Cur_analysis_code;
456 RETURN FALSE;
457 ELSE
458 CLOSE Cur_analysis_code;
459 RETURN TRUE;
460 END IF;
461
462 EXCEPTION
463 WHEN OTHERS THEN
464 RAISE;
465
466 End Validate_Analysis_Code;
467 --
468
469 -- Func start of comments
470 --+==========================================================================+
471 --| FUNCTION NAME |
472 --| Validate_Company_Code |
473 --| |
474 --| USAGE |
475 --| Validates Company Code |
476 --| |
477 --| DESCRIPTION |
478 --| This function validates that the Company Code exists |
479 --| on sy_orgn_mst |
480 --| |
481 --| PARAMETERS |
482 --| p_Company_Code IN VARCHAR2(4) - Company Code |
483 --| |
484 --| RETURNS |
485 --| TRUE - If Company Code contains a valid value |
486 --| FALSE - If Company Code contains an invalid value |
487 --| |
488 --| HISTORY |
489 --| 27/02/2001 Uday Moogla - Created |
490 --| |
491 --+==========================================================================+
492 -- Func end of comments
493
494 FUNCTION Validate_Company_Code
495 (
496 p_Company_Code IN sy_orgn_mst.Co_Code%TYPE
497 )
498 RETURN BOOLEAN
499 IS
500 CURSOR Cur_company_code
501 IS
502 SELECT
503 co_code
504 FROM
505 sy_orgn_mst
506 WHERE
507 co_code = p_Company_Code
508 AND orgn_code = co_code
509 AND delete_mark = 0;
510
511 l_Company_Code sy_orgn_mst.Co_Code%TYPE ;
512
513 BEGIN
514
515 OPEN Cur_company_code;
516 FETCH Cur_company_code INTO l_Company_Code;
517 IF (Cur_company_code%NOTFOUND)
518 THEN
519 CLOSE Cur_company_code;
520 RETURN FALSE;
521 ELSE
522 CLOSE Cur_company_code;
523 RETURN TRUE;
524 END IF;
525
526 EXCEPTION
527 WHEN OTHERS THEN
528 RAISE;
529
530 End Validate_Company_Code;
531 --
532
533 -- Func start of comments
534 --+==========================================================================+
535 --| FUNCTION NAME |
536 --| Validate_Orgn_Code |
537 --| |
538 --| USAGE |
539 --| Validates Organization Code |
540 --| |
541 --| DESCRIPTION |
542 --| This function validates that the Orgn Code exists on sy_orgn_mst |
543 --| |
544 --| PARAMETERS |
545 --| p_orgn_code IN VARCHAR2(4) - Orgn Code |
546 --| |
547 --| RETURNS |
548 --| TRUE - If Orgn Code contains a valid value |
549 --| FALSE - If Orgn Code contains an invalid value |
550 --| |
551 --| HISTORY |
552 --| 27/02/2001 Uday Moogla - Created |
553 --| |
554 --+==========================================================================+
555 -- Func end of comments
556
557 FUNCTION Validate_Orgn_Code
558 ( p_Orgn_Code IN sy_orgn_mst.Orgn_Code%TYPE
559 )
560 RETURN BOOLEAN
561 IS
562 CURSOR Cur_orgn_code
563 IS
564 SELECT
565 orgn_code
566 FROM
567 sy_orgn_mst
568 WHERE
569 orgn_code = p_orgn_code
570 AND delete_mark = 0;
571
572 l_orgn_code sy_orgn_mst.orgn_code%TYPE ;
573
574 BEGIN
575
576 OPEN Cur_orgn_code;
577 FETCH Cur_orgn_code INTO l_orgn_code;
578 IF (Cur_orgn_code%NOTFOUND)
579 THEN
580 CLOSE Cur_orgn_code;
581 RETURN FALSE;
582 ELSE
583 CLOSE Cur_orgn_code;
584 RETURN TRUE;
585 END IF;
586
587 EXCEPTION
588 WHEN OTHERS THEN
589 RAISE;
590
591 End Validate_Orgn_Code ;
592 --
593
594 -- Func start of comments
595 --+==========================================================================+
596 --| FUNCTION NAME |
597 --| Validate_Whse_Code |
598 --| |
599 --| USAGE |
600 --| Validates Warehouse Code |
601 --| |
602 --| DESCRIPTION |
603 --| This function validates that the Whse Code exists on ic_whse_mst |
604 --| |
605 --| PARAMETERS |
606 --| p_whse_code IN VARCHAR2(4) - Whse Code |
607 --| |
608 --| RETURNS |
609 --| TRUE - If Whse Code contains a valid value |
610 --| FALSE - If Whse Code contains an invalid value |
611 --| |
612 --| HISTORY |
613 --| 27/02/2001 Uday Moogla - Created |
614 --| |
615 --+==========================================================================+
616 -- Func end of comments
617
618 FUNCTION Validate_Whse_Code
619 (
620 p_whse_code IN ic_whse_mst.whse_code%TYPE
621 )
622 RETURN BOOLEAN
623 IS
624 CURSOR Cur_whse_code
625 IS
626 SELECT
627 whse_code
628 FROM
629 ic_whse_mst
630 WHERE
631 whse_code = p_whse_code
632 AND delete_mark = 0;
633
634 l_whse_code ic_whse_mst.whse_code%TYPE ;
635
636 BEGIN
637
638 OPEN Cur_whse_code;
639 FETCH Cur_whse_code INTO l_whse_code;
640 IF (Cur_whse_code%NOTFOUND)
641 THEN
642 CLOSE Cur_whse_code;
643 RETURN FALSE;
644 ELSE
645 CLOSE Cur_whse_code;
646 RETURN TRUE;
647 END IF;
648
649 EXCEPTION
650 WHEN OTHERS THEN
651 RAISE;
652
653 End Validate_Whse_Code;
654 --
655
656 -- Func start of comments
657 --+==========================================================================+
658 --| FUNCTION NAME |
659 --| Validate_Item_Id |
660 --| |
661 --| USAGE |
662 --| Validates Item ID |
663 --| |
664 --| DESCRIPTION |
665 --| This function validates that the Item ID exists on ic_whse_mst |
666 --| |
667 --| PARAMETERS |
668 --| p_item_id IN VARCHAR2(4) - Item ID |
669 --| |
670 --| RETURNS |
671 --| TRUE - If Item ID contains a valid value |
672 --| FALSE - If Item ID contains an invalid value |
673 --| |
674 --| HISTORY |
675 --| 27/02/2001 Uday Moogla - Created |
676 --| |
677 --+==========================================================================+
678 -- Func end of comments
679
680 FUNCTION Validate_Item_Id
681 (
682 p_Item_Id IN ic_item_mst.Item_Id%TYPE
683 )
684 RETURN BOOLEAN
685 IS
686 CURSOR Cur_Item_Id
687 IS
688 SELECT
689 Item_Id
690 FROM
691 ic_item_mst
692 WHERE
693 Item_Id = p_Item_Id
694 AND delete_mark = 0
695 AND inactive_ind = 0 ;
696
697 l_Item_Id ic_item_mst.Item_Id%TYPE ;
698
699 BEGIN
700
701 OPEN Cur_Item_Id;
702 FETCH Cur_Item_Id INTO l_Item_Id;
703 IF (Cur_Item_Id%NOTFOUND)
704 THEN
705 CLOSE Cur_Item_Id;
706 RETURN FALSE;
707 ELSE
708 CLOSE Cur_Item_Id;
709 RETURN TRUE;
710 END IF;
711
712 EXCEPTION
713 WHEN OTHERS THEN
714 RAISE;
715
716 End Validate_Item_Id;
717 --
718
719 -- Func start of comments
720 --+==========================================================================+
721 --| PROCEDURE NAME |
722 --| Validate_Item_Id |
723 --| |
724 --| USAGE |
725 --| Validates Item ID |
726 --| |
727 --| DESCRIPTION |
728 --| This function validates that the Item ID exists in ic_item_mst |
729 --| and returns item_um |
730 --| |
731 --| PARAMETERS |
732 --| p_item_id IN VARCHAR2(4) - Item ID |
733 --| |
734 --| RETURNS |
735 --| Item_UM - If Item ID is valid |
736 --| |
737 --| HISTORY |
738 --| 27/02/2001 Uday Moogla - Created |
739 --| |
740 --+==========================================================================+
741 -- Func end of comments
742
743 PROCEDURE Validate_Item_Id
744 (
745 p_Item_Id IN ic_item_mst.Item_Id%TYPE
746 , x_Item_UM OUT NOCOPY ic_item_mst.Item_UM%TYPE
747 )
748 IS
749 CURSOR Cur_Item_UM
750 IS
751 SELECT
752 Item_UM
753 FROM
754 ic_item_mst
755 WHERE
756 Item_Id = p_Item_Id
757 AND delete_mark = 0
758 AND inactive_ind = 0 ;
759
760 BEGIN
761
762 OPEN Cur_Item_UM;
763 FETCH Cur_Item_UM INTO x_Item_UM;
764 CLOSE Cur_Item_UM;
765
766 EXCEPTION
767 WHEN OTHERS THEN
768 RAISE;
769
770 End Validate_Item_Id;
771 --
772
773 -- Func start of comments
774 --+==========================================================================+
775 --| FUNCTION NAME |
776 --| Validate_Item_No |
777 --| |
778 --| USAGE |
779 --| Validates Item No |
780 --| |
781 --| DESCRIPTION |
782 --| This function validates that the Item No exists on ic_whse_mst |
783 --| |
784 --| PARAMETERS |
785 --| p_item_No IN VARCHAR2(4) - Item No |
786 --| |
787 --| RETURNS |
788 --| TRUE - If Item No contains a valid value |
789 --| FALSE - If Item No contains an invalid value |
790 --| |
791 --| HISTORY |
792 --| 27/02/2001 Uday Moogla - Created |
793 --| |
794 --+==========================================================================+
795 -- Func end of comments
796
797 FUNCTION Validate_Item_No
798 ( p_Item_No IN ic_item_mst.Item_No%TYPE
799 )
800 RETURN NUMBER
801 IS
802 CURSOR Cur_Item_Id
803 IS
804 SELECT
805 Item_Id
806 FROM
807 ic_item_mst
808 WHERE
809 Item_No = p_Item_No
810 AND delete_mark = 0
811 AND inactive_ind = 0 ;
812
813 l_Item_Id ic_item_mst.Item_Id%TYPE ;
814
815 BEGIN
816
817 OPEN Cur_Item_Id;
818 FETCH Cur_Item_Id INTO l_Item_Id;
819 IF (Cur_Item_Id%NOTFOUND)
820 THEN
821 CLOSE Cur_Item_Id;
822 ELSE
823 CLOSE Cur_Item_Id;
824 END IF;
825
826 RETURN l_Item_Id;
827
828 EXCEPTION
829 WHEN OTHERS THEN
830 RAISE;
831
832 End Validate_Item_No;
833 --
834
835 -- Func start of comments
836 --+==========================================================================+
837 --| PROCEDURE NAME |
838 --| Validate_Item_No |
839 --| |
840 --| USAGE |
841 --| Validates Item No |
842 --| |
843 --| DESCRIPTION |
844 --| This function validates that the Item No exists on ic_item_mst |
845 --| |
846 --| PARAMETERS |
847 --| p_item_No IN VARCHAR2(4) - Item No |
848 --| |
849 --| RETURNS |
850 --| Item_Id - If Item No is valid |
851 --| Item_UM - If Item No is valid |
852 --| |
853 --| HISTORY |
854 --| 27/02/2001 Uday Moogla - Created |
855 --| |
856 --+==========================================================================+
857 -- Func end of comments
858
859 PROCEDURE Validate_Item_No
860 (
861 p_Item_No IN ic_item_mst.Item_No%TYPE
862 , x_Item_Id OUT NOCOPY ic_item_mst.Item_Id%TYPE
863 , x_Item_UM OUT NOCOPY ic_item_mst.Item_UM%TYPE
864 )
865 IS
866 CURSOR Cur_Item
867 IS
868 SELECT
869 Item_Id, Item_UM
870 FROM
871 ic_item_mst
872 WHERE
873 Item_No = p_Item_No
874 AND delete_mark = 0
875 AND inactive_ind = 0 ;
876
877 BEGIN
878
879 OPEN Cur_Item;
880 FETCH Cur_Item INTO x_Item_Id, x_Item_UM ;
881 CLOSE Cur_Item;
882
883 EXCEPTION
884 WHEN OTHERS THEN
885 RAISE;
886
887 End Validate_Item_No;
888 --
889
890 --+==========================================================================+
891 --| FUNCTION NAME |
892 --| Validate_itemcost_class |
893 --| |
894 --| USAGE |
895 --| Validates itemcost_class |
896 --| |
897 --| DESCRIPTION |
898 --| This function validates that the Item Cost Class exists |
899 --| on ic_cost_cls |
900 --| |
901 --| PARAMETERS |
902 --| p_itemcost_class class IN VARCHAR2(8) - Item Cost Class |
903 --| |
904 --| RETURNS |
905 --| TRUE - If Item Cost Class contains a valid value |
906 --| FALSE - If Item Cost Class contains an invalid value |
907 --| |
908 --| HISTORY |
909 --| 11/13/1998 Mike Godfrey - Created |
910 --| |
911 --+==========================================================================+
912 -- Func end of comments
913 FUNCTION Validate_itemcost_class
914 (
915 p_itemcost_class IN ic_item_mst.itemcost_class%TYPE
916 )
917 RETURN BOOLEAN
918 IS
919 /* ANTHIYAG Bug#4906488
920 CURSOR Cur_itemcost_class
921 IS
922 SELECT
923 itemcost_class
924 FROM
925 ic_cost_cls
926 WHERE
927 itemcost_class = p_itemcost_class
928 AND delete_mark = 0;
929
930 l_itemcost_class ic_cost_cls.itemcost_class%TYPE;
931 */
932 BEGIN
933 /* ANTHIYAG Bug#4906488
934 OPEN Cur_itemcost_class;
935 FETCH Cur_itemcost_class INTO l_itemcost_class;
936 IF (Cur_itemcost_class%NOTFOUND) THEN
937 CLOSE Cur_itemcost_class;
938 RETURN FALSE;
939 ELSE
940 CLOSE Cur_itemcost_class;
941 RETURN TRUE;
942 END IF;
943 */
944 RETURN TRUE;
945 EXCEPTION
946 WHEN OTHERS THEN
947 RAISE;
948
949 END Validate_itemcost_class;
950
951 -- Func start of comments
952 --+==========================================================================+
953 --| FUNCTION NAME |
954 --| Validate_Cost_Cmpntcls_Id |
955 --| |
956 --| USAGE |
957 --| Validates Cost Component Class ID |
958 --| |
959 --| DESCRIPTION |
960 --| This function validates that the Cost Component Class Id exists |
961 --| on cm_cmpt_mst |
962 --| |
963 --| PARAMETERS |
964 --| p_Cost_Cmpntcls_Id IN NUMBER(10) -- Cost Component Class Id |
965 --| |
966 --| RETURNS |
967 --| TRUE - if the Cost_Cmpntcls_Id exists |
968 --| FALSE - if the Cost_Cmpntcls_Id does not exist |
969 --| |
970 --| HISTORY |
971 --| 02/28/2001 Uday Moogala - Created |
972 --| |
973 --+==========================================================================+
974 -- Func end of comments
975
976 FUNCTION Validate_Cost_Cmpntcls_Id
977 ( p_Cost_Cmpntcls_Id IN cm_cmpt_mst.Cost_Cmpntcls_Id%TYPE
978 )
979 RETURN BOOLEAN
980 IS
981 CURSOR Cur_Cost_Cmpntcls_Id
982 IS
983 SELECT
984 Cost_Cmpntcls_Id
985 FROM
986 cm_cmpt_mst
987 WHERE
988 Cost_Cmpntcls_Id = p_Cost_Cmpntcls_Id
989 AND delete_mark = 0 ;
990
991 l_Cost_Cmpntcls_Id cm_cmpt_mst.Cost_Cmpntcls_Id%TYPE ;
992
993 BEGIN
994
995 OPEN Cur_Cost_Cmpntcls_Id;
996 FETCH Cur_Cost_Cmpntcls_Id INTO l_Cost_Cmpntcls_Id;
997 IF (Cur_Cost_Cmpntcls_Id%NOTFOUND)
998 THEN
999 CLOSE Cur_Cost_Cmpntcls_Id;
1000 RETURN FALSE;
1001 ELSE
1002 CLOSE Cur_Cost_Cmpntcls_Id;
1003 RETURN TRUE;
1004 END IF;
1005
1006 EXCEPTION
1007 WHEN OTHERS THEN
1008 RAISE;
1009
1010 End Validate_Cost_Cmpntcls_Id;
1011
1012
1013 -- Func start of comments
1014 --+==========================================================================+
1015 --| PROCEDURE NAME |
1016 --| Validate_Cost_Cmpntcls_Id |
1017 --| |
1018 --| USAGE |
1019 --| Validates Cost Component Class ID |
1020 --| |
1021 --| DESCRIPTION |
1022 --| This function validates that the Cost Component Class Id exists |
1023 --| on cm_cmpt_mst and returns usage_ind |
1024 --| |
1025 --| PARAMETERS |
1026 --| p_Cost_Cmpntcls_Id IN NUMBER(10) -- Cost Component Class Id |
1027 --| |
1028 --| RETURNS |
1029 --| Usage_Ind - Components Usage Indicator |
1030 --| |
1031 --| HISTORY |
1032 --| 02/28/2001 Uday Moogala - Created |
1033 --| |
1034 --+==========================================================================+
1035 -- Func end of comments
1036
1037 PROCEDURE Validate_Cost_Cmpntcls_Id
1038 ( p_Cost_Cmpntcls_Id IN cm_cmpt_mst.Cost_Cmpntcls_Id%TYPE,
1039 x_Cost_Cmpntcls_Id OUT NOCOPY cm_cmpt_mst.Cost_Cmpntcls_Code%TYPE,
1040 x_usage_ind OUT NOCOPY cm_cmpt_mst.usage_ind%TYPE
1041 )
1042 IS
1043 CURSOR Cur_Cost_Cmpntcls_Id
1044 IS
1045 SELECT
1046 cost_cmpntcls_code, usage_ind
1047 FROM
1048 cm_cmpt_mst
1049 WHERE
1050 Cost_Cmpntcls_Id = p_Cost_Cmpntcls_Id
1051 AND delete_mark = 0 ;
1052
1053 BEGIN
1054
1055 OPEN Cur_Cost_Cmpntcls_Id;
1056 FETCH Cur_Cost_Cmpntcls_Id INTO x_Cost_Cmpntcls_Id, x_usage_ind;
1057 CLOSE Cur_Cost_Cmpntcls_Id;
1058 /*
1059 IF (Cur_Cost_Cmpntcls_Id%NOTFOUND)
1060 THEN
1061 CLOSE Cur_Cost_Cmpntcls_Id;
1062 RETURN FALSE;
1063 ELSE
1064 CLOSE Cur_Cost_Cmpntcls_Id;
1065 RETURN TRUE;
1066 END IF;
1067 */
1068 EXCEPTION
1069 WHEN OTHERS THEN
1070 RAISE;
1071
1072 End Validate_Cost_Cmpntcls_Id;
1073
1074 -- Func start of comments
1075 --+==========================================================================+
1076 --| FUNCTION NAME |
1077 --| Validate_Cost_Cmpntcls_Code |
1078 --| |
1079 --| USAGE |
1080 --| Validates Cost Component Class Code |
1081 --| |
1082 --| DESCRIPTION |
1083 --| This function validates that the Cost Component Class Code exists |
1084 --| on cm_cmpt_mst |
1085 --| |
1086 --| PARAMETERS |
1087 --| p_Cost_Cmpntcls_Code IN NUMBER(10) -- Cost Component Class Code |
1088 --| |
1089 --| RETURNS |
1090 --| TRUE - if the Cost_Cmpntcls_Code exists |
1091 --| FALSE - if the Cost_Cmpntcls_Code does not exist |
1092 --| |
1093 --| HISTORY |
1094 --| 02/28/2001 Uday Moogala - Created |
1095 --| |
1096 --+==========================================================================+
1097 -- Func end of comments
1098
1099 FUNCTION Validate_Cost_Cmpntcls_Code
1100 ( p_Cost_Cmpntcls_Code IN cm_cmpt_mst.Cost_Cmpntcls_Code%TYPE
1101 )
1102 RETURN NUMBER
1103 IS
1104 CURSOR Cur_Cost_Cmpntcls_Id
1105 IS
1106 SELECT
1107 Cost_Cmpntcls_Id
1108 FROM
1109 cm_cmpt_mst
1110 WHERE
1111 Cost_Cmpntcls_Code = p_Cost_Cmpntcls_Code
1112 AND delete_mark = 0 ;
1113
1114 l_Cost_Cmpntcls_Id cm_cmpt_mst.Cost_Cmpntcls_Id%TYPE ;
1115
1116 BEGIN
1117
1118 OPEN Cur_Cost_Cmpntcls_Id;
1119 FETCH Cur_Cost_Cmpntcls_Id INTO l_Cost_Cmpntcls_Id;
1120 IF (Cur_Cost_Cmpntcls_Id%NOTFOUND)
1121 THEN
1122 CLOSE Cur_Cost_Cmpntcls_Id;
1123 ELSE
1124 CLOSE Cur_Cost_Cmpntcls_Id;
1125 END IF;
1126
1127 RETURN l_Cost_Cmpntcls_Id ;
1128
1129 EXCEPTION
1130 WHEN OTHERS THEN
1131 RAISE;
1132
1133 End Validate_Cost_Cmpntcls_Code;
1134 --
1135
1136 -- Func start of comments
1137 --+==========================================================================+
1138 --| PROCEDURE NAME |
1139 --| Validate_Cost_Cmpntcls_Code |
1140 --| |
1141 --| USAGE |
1142 --| Validates Cost Component Class Code |
1143 --| |
1144 --| DESCRIPTION |
1145 --| This function validates that the Cost Component Class Code exists |
1146 --| on cm_cmpt_mst and returns Component Class Id and Usage Indicator. |
1147 --| |
1148 --| PARAMETERS |
1149 --| p_Cost_Cmpntcls_Code IN NUMBER(10) -- Cost Component Class Code |
1150 --| |
1151 --| RETURNS |
1152 --| Cost_Cmpntcls_Id |
1153 --| Usage_Ind |
1154 --| |
1155 --| HISTORY |
1156 --| 02/28/2001 Uday Moogala - Created |
1157 --| |
1158 --+==========================================================================+
1159 -- Func end of comments
1160
1161 PROCEDURE Validate_Cost_Cmpntcls_Code
1162 ( p_Cost_Cmpntcls_Code IN cm_cmpt_mst.Cost_Cmpntcls_Code%TYPE,
1163 x_Cost_Cmpntcls_Id OUT NOCOPY cm_cmpt_mst.Cost_Cmpntcls_Id%TYPE,
1164 x_Usage_Ind OUT NOCOPY cm_cmpt_mst.Usage_Ind%TYPE
1165 )
1166 IS
1167 CURSOR Cur_Cost_Cmpntcls_Id
1168 IS
1169 SELECT
1170 Cost_Cmpntcls_Id, Usage_Ind
1171 FROM
1172 cm_cmpt_mst
1173 WHERE
1174 Cost_Cmpntcls_Code = p_Cost_Cmpntcls_Code
1175 AND delete_mark = 0 ;
1176
1177 BEGIN
1178
1179 OPEN Cur_Cost_Cmpntcls_Id;
1180 FETCH Cur_Cost_Cmpntcls_Id INTO x_Cost_Cmpntcls_Id, x_Usage_Ind;
1181 CLOSE Cur_Cost_Cmpntcls_Id;
1182 /*
1183 IF (Cur_Cost_Cmpntcls_Id%NOTFOUND)
1184 THEN
1185 CLOSE Cur_Cost_Cmpntcls_Id;
1186 ELSE
1187 CLOSE Cur_Cost_Cmpntcls_Id;
1188 END IF;
1189
1190 RETURN l_Cost_Cmpntcls_Id ;
1191 */
1192 EXCEPTION
1193 WHEN OTHERS THEN
1194 RAISE;
1195
1196 End Validate_Cost_Cmpntcls_Code;
1197 --
1198
1199 -- Func start of comments
1200 --+==========================================================================+
1201 --| FUNCTION NAME |
1202 --| Validate_gl_class |
1203 --| |
1204 --| USAGE |
1205 --| Validates gl_class |
1206 --| |
1207 --| DESCRIPTION |
1208 --| This function validates that the GL Class code exists |
1209 --| on ic_gled_cls |
1210 --| |
1211 --| PARAMETERS |
1212 --| p_gl_class IN VARCHAR2(8) - GL Class |
1213 --| |
1214 --| RETURNS |
1215 --| TRUE - If GL Class Code contains a valid value |
1216 --| FALSE - If GL Class Code contains an invalid value |
1217 --| |
1218 --| HISTORY |
1219 --| 02/28/2001 Uday Moogala - Created |
1220 --| |
1221 --+==========================================================================+
1222 -- Func end of comments
1223 FUNCTION Validate_gl_class
1224 (
1225 p_gl_class IN ic_gled_cls.icgl_class%TYPE
1226 )
1227 RETURN BOOLEAN
1228 IS
1229 /* ANTHIYAG Bug#4906488
1230 CURSOR Cur_gl_class
1231 IS
1232 SELECT
1233 icgl_class
1234 FROM
1235 ic_gled_cls
1236 WHERE
1237 ic_gled_cls.icgl_class = p_gl_class
1238 AND ic_gled_cls.delete_mark = 0;
1239
1240 l_gl_class ic_gled_cls.icgl_class%TYPE;
1241 */
1242
1243 BEGIN
1244 /* ANTHIYAG Bug#4906488
1245 OPEN Cur_gl_class;
1246 FETCH Cur_gl_class INTO l_gl_class;
1247 IF (Cur_gl_class%NOTFOUND)
1248 THEN
1249 CLOSE Cur_gl_class;
1250 RETURN FALSE;
1251 ELSE
1252 CLOSE Cur_gl_class;
1253 RETURN TRUE;
1254 END IF;
1255 */
1256 RETURN TRUE;
1257 EXCEPTION
1258 WHEN OTHERS THEN
1259 RAISE;
1260
1261 END Validate_gl_class;
1262
1263 -- Func start of comments
1264 --+==========================================================================+
1265 --| FUNCTION NAME |
1266 --| Validate_Fmeff_Id |
1267 --| |
1268 --| USAGE |
1269 --| Validates Fmeff_Id |
1270 --| |
1271 --| DESCRIPTION |
1272 --| This function validates that the Effectivity Id exists |
1273 --| on fm_form_eff |
1274 --| |
1275 --| PARAMETERS |
1276 --| p_fneff_id IN VARCHAR2(8) - Effectivity Id |
1277 --| |
1278 --| RETURNS |
1279 --| TRUE - If Effectivity Id contains a valid value |
1280 --| FALSE - If Effectivity Id contains an invalid value |
1281 --| |
1282 --| HISTORY |
1283 --| 02/28/2001 Uday Moogala - Created |
1284 --| |
1285 --+==========================================================================+
1286 -- Func end of comments
1287
1288 FUNCTION Validate_Fmeff_Id
1289 (
1290 p_Fmeff_Id IN fm_form_eff.Fmeff_Id%TYPE
1291 )
1292 RETURN BOOLEAN
1293 IS
1294 CURSOR Cur_fmeff_id
1295 IS
1296 SELECT
1297 fmeff_id
1298 FROM
1299 fm_form_eff
1300 WHERE
1301 fmeff_id = p_fmeff_id
1302 AND delete_mark = 0;
1303
1304 l_fmeff_id fm_form_eff.fmeff_id%TYPE;
1305
1306 BEGIN
1307
1308 OPEN Cur_fmeff_id;
1309 FETCH Cur_fmeff_id INTO l_fmeff_id;
1310 IF (Cur_fmeff_id%NOTFOUND)
1311 THEN
1312 CLOSE Cur_fmeff_id;
1313 RETURN FALSE;
1314 ELSE
1315 CLOSE Cur_fmeff_id;
1316 RETURN TRUE;
1317 END IF;
1318
1319 EXCEPTION
1320 WHEN OTHERS THEN
1321 RAISE;
1322
1323 End Validate_Fmeff_Id;
1324
1325 -- Func start of comments
1326 --+==========================================================================+
1327 --| FUNCTION NAME |
1328 --| Validate_Resources |
1329 --| |
1330 --| USAGE |
1331 --| Validates Resources |
1332 --| |
1333 --| DESCRIPTION |
1334 --| This function validates that the Resources exists |
1335 --| on cr_rsrc_mst |
1336 --| |
1337 --| PARAMETERS |
1338 --| p_Resources IN VARCHAR2(8) - Effectivity Id |
1339 --| |
1340 --| RETURNS |
1341 --| TRUE - If Resources contains a valid value |
1342 --| FALSE - If Resources contains an invalid value |
1343 --| |
1344 --| HISTORY |
1345 --| 02/28/2001 Uday Moogala - Created |
1346 --| |
1347 --+==========================================================================+
1348 -- Func end of comments
1349
1350 FUNCTION Validate_Resources
1351 (
1352 p_Resources IN cr_rsrc_mst.Resources%TYPE
1353 )
1354 RETURN BOOLEAN
1355 IS
1356 CURSOR Cur_resources
1357 IS
1358 SELECT
1359 resources
1360 FROM
1361 cr_rsrc_mst
1362 WHERE
1363 resources = p_Resources
1364 AND delete_mark = 0;
1365
1366 l_Resources cr_rsrc_mst.Resources%TYPE ;
1367
1368 BEGIN
1369
1370 OPEN Cur_resources;
1371 FETCH Cur_resources INTO l_Resources;
1372 IF (Cur_resources%NOTFOUND)
1373 THEN
1374 CLOSE Cur_resources;
1375 RETURN FALSE;
1376 ELSE
1377 CLOSE Cur_resources;
1378 RETURN TRUE;
1379 END IF;
1380
1381 EXCEPTION
1382 WHEN OTHERS THEN
1383 RAISE;
1384
1385 End Validate_Resources;
1386
1387 -- Func start of comments
1388 --+==========================================================================+
1389 --| FUNCTION NAME |
1390 --| Validate_Resources |
1391 --| |
1392 --| USAGE |
1393 --| Validates Resources |
1394 --| |
1395 --| DESCRIPTION |
1396 --| This function validates that the Resources exists |
1397 --| on cr_rsrc_mst and returns usage UOM |
1398 --| |
1399 --| PARAMETERS |
1400 --| p_Resources IN VARCHAR2(8) - Resources |
1401 --| x_resource_um OUT VARCHAR2(8) - Usage UOM |
1402 --| |
1403 --| RETURNS |
1404 --| Resource UOM - If Resources is valid |
1405 --| |
1406 --| HISTORY |
1407 --| 04/12/2001 Uday Moogala - Created |
1408 --| |
1409 --+==========================================================================+
1410 -- Func end of comments
1411
1412 PROCEDURE Validate_Resources
1413 (
1414 p_Resources IN cr_rsrc_mst.Resources%TYPE
1415 , x_resource_um OUT NOCOPY cr_rsrc_mst.std_usage_um%TYPE
1416 , x_resource_um_type OUT NOCOPY sy_uoms_mst.um_type%TYPE
1417 )
1418 IS
1419 CURSOR Cur_resources
1420 IS
1421 SELECT
1422 rm.std_usage_um, um.um_type
1423 FROM
1424 sy_uoms_mst um, cr_rsrc_mst rm
1425 WHERE
1426 rm.resources = p_Resources
1427 AND um.um_code = rm.std_usage_um
1428 AND rm.delete_mark = 0;
1429
1430 BEGIN
1431
1432 OPEN Cur_resources;
1433 FETCH Cur_resources INTO x_resource_um, x_resource_um_type;
1434 CLOSE Cur_resources;
1435
1436 EXCEPTION
1437 WHEN OTHERS THEN
1438 RAISE;
1439
1440 End Validate_Resources;
1441
1442 -- Func start of comments
1443 --+==========================================================================+
1444 --| FUNCTION NAME |
1445 --| Validate_Alloc_Id |
1446 --| |
1447 --| USAGE |
1448 --| Validates Allocation Id |
1449 --| |
1450 --| DESCRIPTION |
1451 --| This function validates that the Allocation Id exists |
1452 --| on cr_rsrc_mst |
1453 --| |
1454 --| PARAMETERS |
1455 --| p_alloc_id IN NUMBER(10) - Allocation Id |
1456 --| |
1457 --| RETURNS |
1458 --| TRUE - If Allocation Id contains a valid value |
1459 --| FALSE - If Allocation Id contains an invalid value |
1460 --| |
1461 --| HISTORY |
1462 --| 02/28/2001 Uday Moogala - Created |
1463 --| |
1464 --+==========================================================================+
1465 -- Func end of comments
1466
1467 FUNCTION Validate_Alloc_Id
1468 ( p_Alloc_Id IN gl_aloc_mst.Alloc_Id%TYPE
1469 )
1470 RETURN BOOLEAN
1471 IS
1472 CURSOR Cur_Alloc_Id
1473 IS
1474 SELECT
1475 Alloc_Id
1476 FROM
1477 gl_aloc_mst
1478 WHERE
1479 Alloc_Id = p_Alloc_Id
1480 AND delete_mark = 0;
1481
1482 l_Alloc_Id gl_aloc_mst.Alloc_Id%TYPE ;
1483
1484 BEGIN
1485 OPEN Cur_Alloc_Id;
1486 FETCH Cur_Alloc_Id INTO l_Alloc_Id;
1487 IF (Cur_Alloc_Id%NOTFOUND)
1488 THEN
1489 CLOSE Cur_Alloc_Id;
1490 RETURN FALSE;
1491 ELSE
1492 CLOSE Cur_Alloc_Id;
1493 RETURN TRUE;
1494 END IF;
1495
1496 EXCEPTION
1497 WHEN OTHERS THEN
1498 RAISE;
1499
1500 End Validate_Alloc_Id;
1501
1502 -- Func start of comments
1503 --+==========================================================================+
1504 --| FUNCTION NAME |
1505 --| Fetch_Alloc_Id |
1506 --| |
1507 --| USAGE |
1508 --| Used to get allocation id for a given alloc_code and co_code |
1509 --| |
1510 --| DESCRIPTION |
1511 --| Fetches allocation id for a given alloc_code and co_code |
1512 --| |
1513 --| PARAMETERS |
1514 --| p_alloc_Code IN NUMBER(10) - Allocation Code |
1515 --| Co_Code IN NUMBER(10) - Company Code |
1516 --| |
1517 --| RETURNS |
1518 --| Allocation Id |
1519 --| |
1520 --| HISTORY |
1521 --| 02/28/2001 Uday Moogala - Created |
1522 --| |
1523 --+==========================================================================+
1524 -- Func end of comments
1525
1526 FUNCTION Fetch_Alloc_Id
1527 ( p_Alloc_Code IN gl_aloc_mst.Alloc_Code%TYPE,
1528 p_co_code IN sy_orgn_mst.co_code%TYPE
1529 )
1530 RETURN NUMBER
1531 IS
1532 CURSOR Cur_Alloc_Id
1533 IS
1534 SELECT
1535 Alloc_id
1536 FROM
1537 gl_aloc_mst
1538 WHERE
1539 Alloc_Code = p_Alloc_Code
1540 AND Co_Code = p_Co_Code
1541 AND delete_mark = 0;
1542
1543 l_Alloc_id gl_aloc_mst.Alloc_id%TYPE := '' ;
1544
1545 BEGIN
1546
1547 OPEN Cur_Alloc_Id;
1548 FETCH Cur_Alloc_Id INTO l_Alloc_Id;
1549 IF (Cur_Alloc_Id%NOTFOUND)
1550 THEN
1551 CLOSE Cur_Alloc_Id;
1552 RETURN l_Alloc_Id;
1553 ELSE
1554 CLOSE Cur_Alloc_Id;
1555 RETURN l_Alloc_Id;
1556 END IF;
1557
1558 EXCEPTION
1559 WHEN OTHERS THEN
1560 RAISE;
1561
1562 End Fetch_Alloc_Id ;
1563
1564 -- Func start of comments
1565 --+==========================================================================+
1566 --| FUNCTION NAME |
1567 --| Validate_Basis_account_key |
1568 --| |
1569 --| USAGE |
1570 --| Validates Basis Account Key |
1571 --| |
1572 --| DESCRIPTION |
1573 --| This function validates that the Basis Account exists |
1574 --| |
1575 --| PARAMETERS |
1576 --| p_Basis_account_key IN VARCHAR2(10) |
1577 --| |
1578 --| RETURNS |
1579 --| 0 - Valid Basis Account Key |
1580 --| -1 - Invalid Accounting Unit No |
1581 --| -2 - Invalid Account No |
1582 --| |
1583 --| HISTORY |
1584 --| 02/28/2001 Uday Moogala - Created |
1585 --| |
1586 --+==========================================================================+
1587 -- Func end of comments
1588
1589 PROCEDURE Validate_Basis_account_key
1590 ( p_Basis_account_key IN gl_aloc_bas.Basis_account_key%TYPE
1591 , p_co_code IN sy_orgn_mst.co_code%TYPE
1592 , p_basis_description OUT NOCOPY VARCHAR2
1593 , p_return_status OUT NOCOPY NUMBER
1594 )
1595 IS
1596
1597 CURSOR Cur_get_seg_deli(p_co_code VARCHAR2) IS
1598 SELECT segment_delimiter
1599 FROM gl_plcy_mst
1600 WHERE co_code = p_co_code
1601 AND delete_mark = 0;
1602
1603 CURSOR Cur_get_seg_cnttyp(p_co_code VARCHAR2,
1604 ptype NUMBER) IS
1605 SELECT COUNT(*)
1606 FROM gl_plcy_seg
1607 WHERE co_code = p_co_code
1608 AND type = decode(ptype, '', type, ptype)
1609 AND delete_mark = 0;
1610
1611 l_segment_delimiter gl_plcy_mst.segment_delimiter%TYPE;
1612 l_acct_no gl_acct_mst.acct_no%TYPE;
1613 l_acct_id gl_acct_mst.acct_id%TYPE;
1614 l_acctg_unit gl_accu_mst.acctg_unit_no%TYPE ;
1615 l_acctg_unit_id gl_accu_mst.acctg_unit_id%TYPE ;
1616 l_cnt_acctg_unit NUMBER(10);
1617 l_cnt_seg NUMBER(10);
1618
1619 l_segments_tab gmf_get_mappings.my_opm_seg_values; -- to store segments
1620 l_accu_desc gl_aloc_bas.basis_account_desc%TYPE ;
1621 l_acct_desc gl_aloc_bas.basis_account_desc%TYPE ;
1622
1623 BEGIN
1624
1625 l_segments_tab := gmf_get_mappings.get_opm_segment_values(p_Basis_account_key, p_co_code, 2);
1626
1627 -- Fetch Segment delimiter
1628 OPEN Cur_get_seg_deli(p_co_code);
1629 FETCH Cur_get_seg_deli INTO l_segment_delimiter;
1630 CLOSE Cur_get_seg_deli;
1631
1632 -- Get Count of Accounting Units segments
1633 OPEN Cur_get_seg_cnttyp(p_co_code,0);
1634 FETCH Cur_get_seg_cnttyp INTO l_cnt_acctg_unit;
1635 CLOSE Cur_get_seg_cnttyp;
1636
1637 -- Get # of segments
1638 OPEN Cur_get_seg_cnttyp(p_co_code,'');
1639 FETCH Cur_get_seg_cnttyp INTO l_cnt_seg;
1640 CLOSE Cur_get_seg_cnttyp;
1641
1642 --
1643 -- getting account unit by concatinating individual segments based on
1644 -- count on segment types
1645 -- Accouting units always occur first
1646 --
1647 FOR i in 1..l_cnt_acctg_unit
1648 LOOP
1649 l_acctg_unit := l_acctg_unit || l_segments_tab(i) ;
1650 IF i < l_cnt_acctg_unit THEN -- to avoid end delimiter.
1651 l_acctg_unit := l_acctg_unit || l_segment_delimiter ;
1652 END IF;
1653 END LOOP ;
1654
1655 -- getting account by concatinating individual segments based on
1656 -- count on segment types
1657 FOR i in (l_cnt_acctg_unit+1)..l_cnt_seg
1658 LOOP
1659 l_acct_no := l_acct_no || l_segments_tab(i) ;
1660 IF i < l_cnt_seg THEN
1661 l_acct_no := l_acct_no || l_segment_delimiter ;
1662 END IF;
1663 END LOOP ;
1664
1665 SELECT acctg_unit_desc
1666 INTO l_accu_desc
1667 FROM gl_accu_mst
1668 WHERE acctg_unit_no = l_acctg_unit
1669 AND co_code = p_co_code
1670 AND delete_mark = 0 ;
1671
1672 SELECT acct_desc
1673 INTO l_acct_desc
1674 FROM gl_acct_mst
1675 WHERE acct_no = l_acct_no
1676 AND co_code = p_co_code
1677 AND delete_mark = 0 ;
1678
1679 p_basis_description := substrb(l_accu_desc || ' ' || l_acct_desc,1,70) ;
1680 p_return_status := 0 ;
1681
1682 EXCEPTION
1683 WHEN OTHERS THEN
1684 IF ( l_accu_desc IS NULL )
1685 THEN
1686 p_return_status := -1 ; /* error in acctg_unit_no */
1687 ELSE
1688 p_return_status := -2 ; /* error in acct_no */
1689 END IF;
1690 END Validate_Basis_account_key ;
1691
1692 -- Func start of comments
1693 --+==========================================================================+
1694 --| FUNCTION NAME |
1695 --| Validate_Usage_Um |
1696 --| |
1697 --| USAGE |
1698 --| Validates Usage UOM |
1699 --| |
1700 --| DESCRIPTION |
1701 --| This function validates that the Usage UOM exists |
1702 --| in sy_uoms_mst |
1703 --| |
1704 --| PARAMETERS |
1705 --| p_usage_um IN VARCHAR2(10) - Usage UOM |
1706 --| |
1707 --| RETURNS |
1708 --| TRUE - If Usage UOM contains a valid value |
1709 --| FALSE - If Usage UOM contains an invalid value |
1710 --| |
1711 --| HISTORY |
1712 --| 02/28/2001 Uday Moogala - Created |
1713 --| |
1714 --+==========================================================================+
1715 -- Func end of comments
1716
1717 FUNCTION Validate_Usage_Um
1718 ( p_Usage_Um IN sy_uoms_mst.Um_Code%TYPE
1719 )
1720 RETURN BOOLEAN
1721 IS
1722 CURSOR Cur_Usage_Um
1723 IS
1724 SELECT
1725 Um_Code
1726 FROM
1727 sy_uoms_mst
1728 WHERE
1729 Um_Code = p_Usage_Um ;
1730
1731 l_Um_Code sy_uoms_mst.Um_Code%TYPE ;
1732
1733 BEGIN
1734
1735 OPEN Cur_Usage_Um;
1736 FETCH Cur_Usage_Um INTO l_Um_Code;
1737 IF (Cur_Usage_Um%NOTFOUND)
1738 THEN
1739 CLOSE Cur_Usage_Um;
1740 RETURN FALSE;
1741 ELSE
1742 CLOSE Cur_Usage_Um;
1743 RETURN TRUE;
1744 END IF;
1745
1746 EXCEPTION
1747 WHEN OTHERS THEN
1748 RAISE;
1749
1750 END Validate_Usage_Um ;
1751
1752 -- Func start of comments
1753 --+==========================================================================+
1754 --| PROCEDURE NAME |
1755 --| Validate_Usage_Um |
1756 --| |
1757 --| USAGE |
1758 --| Validates Usage UOM |
1759 --| |
1760 --| DESCRIPTION |
1761 --| This function validates that the Usage UOM exists |
1762 --| in sy_uoms_mst and return UOM Type |
1763 --| |
1764 --| PARAMETERS |
1765 --| p_usage_um IN VARCHAR2(10) - Usage UOM |
1766 --| x_um_type IN VARCHAR2(10) - UOM Type |
1767 --| |
1768 --| RETURNS |
1769 --| Um_Type - If Usage UOM contains a valid value |
1770 --| |
1771 --| HISTORY |
1772 --| 04/12/2001 Uday Moogala - Created |
1773 --| |
1774 --+==========================================================================+
1775 -- Func end of comments
1776
1777 PROCEDURE Validate_Usage_Um
1778 (
1779 p_Usage_Um IN sy_uoms_mst.Um_Code%TYPE
1780 , x_Um_Type OUT NOCOPY sy_uoms_mst.Um_Type%TYPE
1781 )
1782 IS
1783 CURSOR Cur_Usage_Um
1784 IS
1785 SELECT
1786 Um_Type
1787 FROM
1788 sy_uoms_mst
1789 WHERE
1790 Um_Code = p_Usage_Um ;
1791
1792 BEGIN
1793
1794 OPEN Cur_Usage_Um;
1795 FETCH Cur_Usage_Um INTO x_Um_Type;
1796 CLOSE Cur_Usage_Um;
1797
1798 EXCEPTION
1799 WHEN OTHERS THEN
1800 RAISE;
1801
1802 END Validate_Usage_Um ;
1803
1804 -- Func start of comments
1805 --+==========================================================================+
1806 --| FUNCTION NAME |
1807 --| Validate_lot_cost_mthd_code |
1808 --| |
1809 --| USAGE |
1810 --| Validates lot cost_mthd_code |
1811 --| |
1812 --| DESCRIPTION |
1813 --| This function validates that the lot cost Method Code exists |
1814 --| on cm_mthd_mst |
1815 --| |
1816 --| PARAMETERS |
1817 --| p_cost_mthd_code IN VARCHAR2(4) - Lot Cost Method Code |
1818 --| |
1819 --| RETURNS |
1820 --| TRUE - If Cost Method contains a valid value |
1821 --| FALSE - If Cost Method contains an invalid value |
1822 --| |
1823 --| HISTORY |
1824 --| 07-Apr-2004 Dinesh Vadivel - Created |
1825 --| |
1826 --| |
1827 --+==========================================================================+
1828 -- Func end of comments
1829
1830 FUNCTION Validate_lot_cost_mthd_code
1831 (
1832 p_cost_mthd_code IN ic_item_mst.cost_mthd_code%TYPE
1833 )
1834 RETURN BOOLEAN
1835 IS
1836 CURSOR cur_cm_mthd_mst
1837 IS
1838 SELECT cost_mthd_code
1839 FROM cm_mthd_mst
1840 WHERE cm_mthd_mst.cost_mthd_code = p_cost_mthd_code
1841 AND cm_mthd_mst.delete_mark = 0
1842 AND lot_actual_cost = 1;
1843
1844 l_cost_mthd_code ic_item_mst.cost_mthd_code%TYPE;
1845
1846 BEGIN
1847
1848 OPEN cur_cm_mthd_mst;
1849 FETCH cur_cm_mthd_mst INTO l_cost_mthd_code;
1850 IF (cur_cm_mthd_mst%NOTFOUND)
1851 THEN
1852 CLOSE cur_cm_mthd_mst;
1853 RETURN FALSE;
1854 ELSE
1855 CLOSE cur_cm_mthd_mst;
1856 RETURN TRUE;
1857 END IF;
1858 EXCEPTION
1859 WHEN OTHERS THEN
1860 RAISE;
1861 END Validate_lot_cost_mthd_code;
1862
1863 -- Func start of comments
1864 --+==========================================================================+
1865 --| FUNCTION NAME |
1866 --| Validate_lot_id |
1867 --| |
1868 --| USAGE |
1869 --| Validates lot Id |
1870 --| |
1871 --| DESCRIPTION |
1872 --| This function validates the lot and returns Lot ID from ic_lots_mst|
1873 --| |
1874 --| PARAMETERS |
1875 --| p_item_id IN ic_item_mst.item_id%TYPE - Item Id |
1876 --| p_lot_no IN ic_lots_mst.lot_no%TYPE - Lot No |
1877 --| p_sublot_no IN ic_lots_mst.sublot_no%TYPE -Sublot No |
1878 --| |
1879 --| RETURNS |
1880 --| Lot Id NUMBER |
1881 --| |
1882 --| HISTORY |
1883 --| 15-Apr-2004 Anand Thiyagarajan - Created |
1884 --| |
1885 --+==========================================================================+
1886 -- Func end of comments
1887
1888 FUNCTION VALIDATE_LOT_ID
1889 (
1890 p_item_id IN ic_item_mst.item_id%TYPE
1891 , p_lot_no IN ic_lots_mst.lot_no%TYPE
1892 , p_sublot_no IN ic_lots_mst.sublot_no%TYPE
1893 )
1894 RETURN NUMBER
1895 IS
1896 l_lot_id ic_lots_mst.lot_id%TYPE;
1897 BEGIN
1898
1899 BEGIN
1900 SELECT lot_id
1901 INTO l_lot_id
1902 FROM ic_lots_mst
1903 WHERE item_id = p_item_id
1904 AND lot_no = p_lot_no
1905 AND sublot_no = p_sublot_no
1906 AND ROWNUM = 1;
1907 EXCEPTION
1908 WHEN NO_DATA_FOUND THEN
1909 l_lot_id := NULL;
1910 END;
1911
1912 RETURN ( l_lot_id );
1913
1914 END VALIDATE_LOT_ID;
1915
1916 -- Func start of comments
1917 --+==========================================================================+
1918 --| FUNCTION NAME |
1919 --| Validate_lot_id |
1920 --| |
1921 --| USAGE |
1922 --| Validates lot Id |
1923 --| |
1924 --| DESCRIPTION |
1925 --| This function validates that the lot ID exists in ic_lots_mst |
1926 --| |
1927 --| PARAMETERS |
1928 --| p_item_id IN ic_item_mst.item_id%TYPE - Item Id |
1929 --| p_lot_id IN ic_lots_mst.lot_ID%TYPE - Lot Id |
1930 --| |
1931 --| RETURNS |
1932 --| TRUE - If Lot Exists in ic_lots_mst |
1933 --| FALSE - If Lot Doesnt Exist in ic_lots_mst |
1934 --| |
1935 --| HISTORY |
1936 --| 15-Apr-2004 Anand Thiyagarajan - Created |
1937 --| |
1938 --+==========================================================================+
1939 -- Func end of comments
1940
1941 FUNCTION VALIDATE_LOT_ID
1942 (
1943 p_item_id IN ic_item_mst.item_id%TYPE
1944 , p_lot_id IN ic_lots_mst.lot_id%TYPE
1945 )
1946 RETURN BOOLEAN
1947 IS
1948 l_cnt NUMBER;
1949 BEGIN
1950
1951 BEGIN
1952 SELECT 1
1953 INTO l_cnt
1954 FROM ic_lots_mst
1955 WHERE item_id = p_item_id
1956 AND lot_id = p_lot_id
1957 AND ROWNUM = 1;
1958 EXCEPTION
1959 WHEN NO_DATA_FOUND THEN
1960 l_cnt := 0;
1961 END;
1962
1963 IF l_cnt > 0 THEN
1964 RETURN (TRUE);
1965 ELSE
1966 RETURN (FALSE);
1967 END IF;
1968
1969 END VALIDATE_LOT_ID;
1970
1971 -- Func start of comments
1972 --+==========================================================================+
1973 --| FUNCTION NAME |
1974 --| Validate_lot_No |
1975 --| |
1976 --| USAGE |
1977 --| Validates lot No |
1978 --| |
1979 --| DESCRIPTION |
1980 --| This function validates that the lot No exists in ic_lots_mst |
1981 --| |
1982 --| PARAMETERS |
1983 --| p_item_id IN ic_item_mst.item_id%TYPE - Item Id |
1984 --| p_lot_no IN ic_lots_mst.lot_no%TYPE - Lot No |
1985 --| p_sublot_no IN ic_lots_mst.lot_no%TYPE - sublot No |
1986 --| |
1987 --| RETURNS |
1988 --| TRUE - If Lot Exists in ic_lots_mst |
1989 --| FALSE - If Lot Doesnt Exist in ic_lots_mst |
1990 --| |
1991 --| HISTORY |
1992 --| 15-Apr-2004 Anand Thiyagarajan - Created |
1993 --| |
1994 --+==========================================================================+
1995 -- Func end of comments
1996
1997 FUNCTION VALIDATE_LOT_NO
1998 (
1999 p_item_id IN ic_item_mst.item_id%TYPE
2000 , p_lot_no IN ic_lots_mst.lot_no%TYPE
2001 , p_sublot_no IN ic_lots_mst.sublot_no%TYPE
2002 )
2003 RETURN BOOLEAN
2004 IS
2005 l_cnt NUMBER;
2006 BEGIN
2007
2008 BEGIN
2009 SELECT 1
2010 INTO l_cnt
2011 FROM ic_lots_mst
2012 WHERE item_id = p_item_id
2013 AND lot_no = p_lot_no
2014 AND sublot_no = p_sublot_no
2015 AND ROWNUM = 1;
2016 EXCEPTION
2017 WHEN NO_DATA_FOUND THEN
2018 l_cnt := 0;
2019 END;
2020 IF l_cnt > 0 THEN
2021 RETURN (TRUE);
2022 ELSE
2023 RETURN (FALSE);
2024 END IF;
2025
2026 END VALIDATE_LOT_NO;
2027
2028 /* ANTHIYAG Added for Release 12.0 Start */
2029
2030 /***************************************************************************************
2031 * FUNCTION NAME *
2032 * validate_legal_entity_id *
2033 * *
2034 * USAGE *
2035 * Validates Legal Entity *
2036 * *
2037 * DESCRIPTION *
2038 * This function validates that the Legal Entity Id exists *
2039 * *
2040 * PARAMETERS *
2041 * p_legal_entity_id IN xle_entity_profiles.legal_entity_id%TYPE *
2042 * *
2043 * RETURNS *
2044 * TRUE - If Legal Entity Exists *
2045 * FALSE - If Legal Entity Doesnt Exist *
2046 * *
2047 * HISTORY *
2048 * 20-Oct-2005 Anand Thiyagarajan - Created *
2049 ***************************************************************************************/
2050 FUNCTION validate_legal_entity_id
2051 (
2052 p_legal_entity_id IN xle_entity_profiles.legal_entity_id%TYPE
2053 )
2054 RETURN BOOLEAN
2055 IS
2056
2057 /**********
2058 * Cursors *
2059 **********/
2060
2061 CURSOR Cur_legal_entity
2062 IS
2063 SELECT legal_entity_id
2064 FROM xle_entity_profiles
2065 WHERE legal_entity_id = p_legal_entity_id;
2066
2067 /******************
2068 * Local Variables *
2069 ******************/
2070
2071 l_legal_entity_id xle_entity_profiles.legal_entity_id%TYPE ;
2072
2073 BEGIN
2074 OPEN Cur_legal_entity;
2075 FETCH Cur_legal_entity INTO l_legal_entity_id;
2076 IF Cur_legal_entity%NOTFOUND
2077 THEN
2078 CLOSE Cur_legal_entity;
2079 RETURN FALSE;
2080 ELSE
2081 CLOSE Cur_legal_entity;
2082 RETURN TRUE;
2083 END IF;
2084 EXCEPTION
2085 WHEN OTHERS THEN
2086 RAISE;
2087 END validate_legal_entity_id;
2088
2089 /***************************************************************************************
2090 * FUNCTION NAME *
2091 * Validate_Cost_type_id *
2092 * *
2093 * USAGE *
2094 * Validates Cost Type Id *
2095 * *
2096 * DESCRIPTION *
2097 * This function validates that the Cost Type Id exists *
2098 * *
2099 * PARAMETERS *
2100 * p_cost_type_id IN cm_mthd_mst.cost_type_id%TYPE *
2101 * *
2102 * RETURNS *
2103 * TRUE - If cost Type Exists *
2104 * FALSE - If Cost Type Doesnt Exist *
2105 * *
2106 * HISTORY *
2107 * 20-Oct-2005 Anand Thiyagarajan - Created *
2108 ***************************************************************************************/
2109 FUNCTION Validate_Cost_type_id
2110 (
2111 p_cost_type_id IN cm_mthd_mst.cost_type_id%TYPE
2112 )
2113 RETURN BOOLEAN
2114 IS
2115
2116 /**********
2117 * Cursors *
2118 **********/
2119
2120 CURSOR Cur_cost_type
2121 IS
2122 SELECT cost_type_id
2123 FROM cm_mthd_mst
2124 WHERE cost_type_id = p_cost_type_id;
2125
2126 /******************
2127 * Local Variables *
2128 ******************/
2129
2130 l_cost_type_id cm_mthd_mst.cost_Type_id%TYPE;
2131
2132 BEGIN
2133 OPEN Cur_cost_type;
2134 FETCH Cur_cost_type INTO l_cost_type_id;
2135 IF Cur_cost_type%NOTFOUND
2136 THEN
2137 CLOSE Cur_cost_type;
2138 RETURN FALSE;
2139 ELSE
2140 CLOSE Cur_cost_type;
2141 RETURN TRUE;
2142 END IF;
2143 EXCEPTION
2144 WHEN OTHERS THEN
2145 RAISE;
2146 END Validate_Cost_type_id;
2147
2148 /***************************************************************************************
2149 * FUNCTION NAME *
2150 * Validate_Cost_type_id *
2151 * *
2152 * USAGE *
2153 * Validates Cost Type Id *
2154 * *
2155 * DESCRIPTION *
2156 * This function validates that the Cost Type Id exists *
2157 * *
2158 * PARAMETERS *
2159 * p_cost_type_id IN cm_mthd_mst.cost_type_id%TYPE *
2160 * p_Type IN VARCHAR2 *
2161 * *
2162 * RETURNS *
2163 * TRUE - If cost Type Exists *
2164 * FALSE - If Cost Type Doesnt Exist *
2165 * *
2166 * HISTORY *
2167 * 20-Oct-2005 Anand Thiyagarajan - Created *
2168 ***************************************************************************************/
2169 FUNCTION Validate_Cost_type_id
2170 (
2171 p_cost_type_id IN cm_mthd_mst.cost_type_id%TYPE,
2172 p_Type IN VARCHAR2
2173 )
2174 RETURN BOOLEAN
2175 IS
2176
2177 /**********
2178 * Cursors *
2179 **********/
2180
2181 CURSOR Cur_cost_type
2182 IS
2183 SELECT cost_type_id
2184 FROM cm_mthd_mst
2185 WHERE cost_type_id = p_cost_type_id
2186 AND (cost_type = decode(p_type, 'A', 1, 'S', 0, 'L', 6,'AS', 0) or cost_type = decode(p_type, 'A', 1, 'S', 0, 'L', 6, 'AS', 1));
2187
2188 /******************
2189 * Local Variables *
2190 ******************/
2191
2192 l_cost_type_id cm_mthd_mst.cost_Type_id%TYPE;
2193
2194 BEGIN
2195 OPEN Cur_cost_type;
2196 FETCH Cur_cost_type INTO l_cost_type_id;
2197 IF Cur_cost_type%NOTFOUND
2198 THEN
2199 CLOSE Cur_cost_type;
2200 RETURN FALSE;
2201 ELSE
2202 CLOSE Cur_cost_type;
2203 RETURN TRUE;
2204 END IF;
2205 EXCEPTION
2206 WHEN OTHERS THEN
2207 RAISE;
2208 END Validate_Cost_type_id;
2209
2210 /***************************************************************************************
2211 * FUNCTION NAME *
2212 * Validate_Cost_type_code *
2213 * *
2214 * USAGE *
2215 * Validates Cost Method Code *
2216 * *
2217 * DESCRIPTION *
2218 * This function validates that the Cost Method Code exists *
2219 * *
2220 * PARAMETERS *
2221 * p_cost_mthd_code IN cm_mthd_mst.cost_mthd_code%TYPE *
2222 * *
2223 * RETURNS *
2224 * Cost Type Id *
2225 * *
2226 * HISTORY *
2227 * 20-Oct-2005 Anand Thiyagarajan - Created *
2228 ***************************************************************************************/
2229
2230 FUNCTION Validate_cost_type_code
2231 (
2232 p_cost_mthd_code IN cm_mthd_mst.cost_mthd_code%TYPE
2233 )
2234 RETURN NUMBER
2235 IS
2236 /**********
2237 * Cursors *
2238 **********/
2239
2240 CURSOR Cur_cost_type
2241 IS
2242 SELECT cost_type_id
2243 FROM cm_mthd_mst
2244 WHERE cost_mthd_code = p_cost_mthd_code;
2245
2246 /******************
2247 * Local Variables *
2248 ******************/
2249
2250 l_cost_type_id cm_mthd_mst.cost_Type_id%TYPE;
2251
2252 BEGIN
2253 OPEN Cur_cost_type;
2254 FETCH Cur_cost_type INTO l_cost_type_id;
2255 IF Cur_cost_type%NOTFOUND
2256 THEN
2257 CLOSE Cur_cost_type;
2258 RETURN NULL;
2259 ELSE
2260 CLOSE Cur_cost_type;
2261 RETURN l_cost_type_id;
2262 END IF;
2263 EXCEPTION
2264 WHEN OTHERS THEN
2265 RAISE;
2266 END Validate_Cost_type_Code;
2267
2268 /***************************************************************************************
2269 * FUNCTION NAME *
2270 * Validate_Cost_type_code *
2271 * *
2272 * USAGE *
2273 * Validates Cost Method Code *
2274 * *
2275 * DESCRIPTION *
2276 * This function validates that the Cost Method Code exists *
2277 * *
2278 * PARAMETERS *
2279 * p_cost_mthd_code IN cm_mthd_mst.cost_mthd_code%TYPE *
2280 * p_Type IN VARCHAR2 *
2281 * *
2282 * RETURNS *
2283 * Cost Type Id *
2284 * *
2285 * HISTORY *
2286 * 20-Oct-2005 Anand Thiyagarajan - Created *
2287 ***************************************************************************************/
2288 FUNCTION Validate_cost_type_code
2289 (
2290 p_cost_mthd_code IN cm_mthd_mst.cost_mthd_code%TYPE,
2291 p_Type IN VARCHAR2
2292 )
2293 RETURN NUMBER
2294 IS
2295 /**********
2296 * Cursors *
2297 **********/
2298
2299 CURSOR Cur_cost_type
2300 IS
2301 SELECT cost_type_id
2302 FROM cm_mthd_mst
2303 WHERE cost_mthd_code = p_cost_mthd_code
2304 AND (cost_type = decode(p_type, 'A', 1, 'S', 0, 'L', 6,'AS', 0) or cost_type = decode(p_type, 'A', 1, 'S', 0, 'L', 6, 'AS', 1));
2305
2306 /******************
2307 * Local Variables *
2308 ******************/
2309
2310 l_cost_type_id cm_mthd_mst.cost_Type_id%TYPE;
2311
2312 BEGIN
2313 OPEN Cur_cost_type;
2314 FETCH Cur_cost_type INTO l_cost_type_id;
2315 IF Cur_cost_type%NOTFOUND
2316 THEN
2317 CLOSE Cur_cost_type;
2318 RETURN NULL;
2319 ELSE
2320 CLOSE Cur_cost_type;
2321 RETURN l_cost_type_id;
2322 END IF;
2323 EXCEPTION
2324 WHEN OTHERS THEN
2325 RAISE;
2326 END Validate_Cost_type_Code;
2327
2328 /***************************************************************************************
2329 * FUNCTION NAME *
2330 * validate_period_id *
2331 * *
2332 * USAGE *
2333 * Validates Period *
2334 * *
2335 * DESCRIPTION *
2336 * This function validates that the Period Id exists *
2337 * *
2338 * PARAMETERS *
2339 * p_period_id IN gmf_period_statuses.period_id%TYPE *
2340 * *
2341 * RETURNS *
2342 * TRUE - If Period Exists *
2343 * FALSE - If Period Doesnt Exist *
2344 * *
2345 * HISTORY *
2346 * 20-Oct-2005 Anand Thiyagarajan - Created *
2347 ***************************************************************************************/
2348 FUNCTION Validate_period_id
2349 (
2350 p_period_id IN gmf_period_statuses.period_id%TYPE
2351 )
2352 RETURN BOOLEAN
2353 IS
2354
2355 /**********
2356 * Cursors *
2357 **********/
2358
2359 CURSOR Cur_period
2360 IS
2361 SELECT period_id
2362 FROM gmf_period_statuses
2363 WHERE period_id = p_period_id;
2364
2365 /******************
2366 * Local Variables *
2367 ******************/
2368
2369 l_period_id gmf_period_statuses.period_id%TYPE;
2370
2371 BEGIN
2372 OPEN Cur_period;
2373 FETCH Cur_period INTO l_period_id;
2374 IF Cur_period%NOTFOUND
2375 THEN
2376 CLOSE Cur_period;
2377 RETURN FALSE;
2378 ELSE
2379 CLOSE Cur_period;
2380 RETURN TRUE;
2381 END IF;
2382 EXCEPTION
2383 WHEN OTHERS THEN
2384 RAISE;
2385 END Validate_period_id;
2386
2387 /***************************************************************************************
2388 * FUNCTION NAME *
2389 * validate_period_id *
2390 * *
2391 * USAGE *
2392 * Validates Period *
2393 * *
2394 * DESCRIPTION *
2395 * This function validates that the Period Id exists *
2396 * *
2397 * PARAMETERS *
2398 * p_period_id IN gmf_period_statuses.period_id%TYPE *
2399 * *
2400 * RETURNS *
2401 * TRUE - If Period Exists *
2402 * FALSE - If Period Doesnt Exist *
2403 * *
2404 * HISTORY *
2405 * 20-Oct-2005 Anand Thiyagarajan - Created *
2406 ***************************************************************************************/
2407 FUNCTION Validate_period_id
2408 (
2409 p_period_id IN gmf_period_statuses.period_id%TYPE,
2410 p_cost_type_id OUT NOCOPY gmf_period_statuses.cost_type_id%TYPE
2411 )
2412 RETURN BOOLEAN
2413 IS
2414
2415 /**********
2416 * Cursors *
2417 **********/
2418
2419 CURSOR Cur_period
2420 IS
2421 SELECT period_id, cost_type_id
2422 FROM gmf_period_statuses
2423 WHERE period_id = p_period_id;
2424
2425 /******************
2426 * Local Variables *
2427 ******************/
2428
2429 l_period_id gmf_period_statuses.period_id%TYPE;
2430
2431 BEGIN
2432 OPEN Cur_period;
2433 FETCH Cur_period INTO l_period_id, p_cost_type_id;
2434 IF Cur_period%NOTFOUND
2435 THEN
2436 CLOSE Cur_period;
2437 RETURN FALSE;
2438 ELSE
2439 CLOSE Cur_period;
2440 RETURN TRUE;
2441 END IF;
2442 EXCEPTION
2443 WHEN OTHERS THEN
2444 RAISE;
2445 END Validate_period_id;
2446
2447 /***************************************************************************************
2448 * FUNCTION NAME *
2449 * Validate_period_code *
2450 * *
2451 * USAGE *
2452 * Validates Period Code *
2453 * *
2454 * DESCRIPTION *
2455 * This function validates that the Period Code exists *
2456 * *
2457 * PARAMETERS *
2458 * p_organization_id IN mtl_organizations.organization_id%TYPE *
2459 * p_calendar_code IN cm_cldr_hdr_b.calendar_code%TYPE *
2460 * p_period_code IN cm_cldr_dtl.period_code%TYPE *
2461 * p_cost_type_id IN cm_mthd_mst.cost_type_id%TYPE *
2462 * *
2463 * RETURNS *
2464 * Period Id *
2465 * *
2466 * HISTORY *
2467 * 20-Oct-2005 Anand Thiyagarajan - Created *
2468 ***************************************************************************************/
2469 FUNCTION Validate_period_code
2470 (
2471 p_organization_id IN mtl_organizations.organization_id%TYPE,
2472 p_calendar_code IN cm_cldr_hdr_b.calendar_code%TYPE,
2473 p_period_code IN cm_cldr_dtl.period_code%TYPE,
2474 p_cost_type_id IN cm_mthd_mst.cost_type_id%TYPE
2475 )
2476 RETURN NUMBER
2477 IS
2478
2479 /**********
2480 * Cursors *
2481 **********/
2482
2483 CURSOR Cur_period
2484 IS
2485 SELECT period_id
2486 FROM gmf_period_statuses a,
2487 org_organization_definitions b
2488 WHERE b.organization_id = p_organization_id
2489 AND b.legal_entity = a.legal_entity_id
2490 AND a.calendar_code = p_calendar_code
2491 AND a.period_code = p_period_code
2492 AND a.cost_type_id = p_cost_type_id;
2493
2494 /******************
2495 * Local Variables *
2496 ******************/
2497
2498 l_period_id gmf_period_statuses.period_id%TYPE;
2499
2500 BEGIN
2501 OPEN Cur_period;
2502 FETCH Cur_period INTO l_period_id;
2503 IF Cur_period%NOTFOUND
2504 THEN
2505 CLOSE Cur_period;
2506 RETURN NULL;
2507 ELSE
2508 CLOSE Cur_period;
2509 RETURN l_period_id;
2510 END IF;
2511 EXCEPTION
2512 WHEN OTHERS THEN
2513 RAISE;
2514 END Validate_period_code;
2515
2516 /***************************************************************************************
2517 * FUNCTION NAME *
2518 * validate_organization_id *
2519 * *
2520 * USAGE *
2521 * Validates Organization *
2522 * *
2523 * DESCRIPTION *
2524 * This function validates that the Organization Id exists *
2525 * *
2526 * PARAMETERS *
2527 * p_organization_id IN mtl_organizations.organization_id%TYPE *
2528 * *
2529 * RETURNS *
2530 * TRUE - If Organization Exists *
2531 * FALSE - If Organization Doesnt Exist *
2532 * *
2533 * HISTORY *
2534 * 20-Oct-2005 Anand Thiyagarajan - Created *
2535 ***************************************************************************************/
2536 FUNCTION Validate_organization_id
2537 (
2538 p_organization_id IN mtl_organizations.organization_id%TYPE
2539 )
2540 RETURN BOOLEAN
2541 IS
2542
2543 /**********
2544 * Cursors *
2545 **********/
2546
2547 CURSOR Cur_organization
2548 IS
2549 SELECT organization_id
2550 FROM mtl_parameters
2551 WHERE organization_id = p_organization_id
2552 AND process_enabled_flag = 'Y';
2553
2554 /******************
2555 * Local Variables *
2556 ******************/
2557
2558 l_organization_id mtl_organizations.organization_id%TYPE;
2559
2560 BEGIN
2561 OPEN Cur_organization;
2562 FETCH Cur_organization INTO l_organization_id;
2563 IF Cur_organization%NOTFOUND
2564 THEN
2565 CLOSE Cur_organization;
2566 RETURN FALSE;
2567 ELSE
2568 CLOSE Cur_organization;
2569 RETURN TRUE;
2570 END IF;
2571 EXCEPTION
2572 WHEN OTHERS THEN
2573 RAISE;
2574 END Validate_organization_id;
2575
2576 /***************************************************************************************
2577 * FUNCTION NAME *
2578 * Validate_Organization_code *
2579 * *
2580 * USAGE *
2581 * Validates Organization Code *
2582 * *
2583 * DESCRIPTION *
2584 * This function validates that the Organization Code exists *
2585 * *
2586 * PARAMETERS *
2587 * p_organization_code IN mtl_organizations.organization_code%TYPE *
2588 * *
2589 * RETURNS *
2590 * Organization Id *
2591 * *
2592 * HISTORY *
2593 * 20-Oct-2005 Anand Thiyagarajan - Created *
2594 ***************************************************************************************/
2595 FUNCTION Validate_Organization_code
2596 (
2597 p_organization_code IN mtl_parameters.organization_code%TYPE
2598 )
2599 RETURN NUMBER
2600 IS
2601
2602 /**********
2603 * Cursors *
2604 **********/
2605
2606 CURSOR Cur_organization
2607 IS
2608 SELECT organization_id
2609 FROM mtl_parameters
2610 WHERE organization_code = p_organization_code;
2611
2612 /******************
2613 * Local Variables *
2614 ******************/
2615
2616 l_organization_id mtl_organizations.organization_id%TYPE;
2617
2618 BEGIN
2619 OPEN Cur_organization;
2620 FETCH Cur_organization INTO l_organization_id;
2621 IF Cur_organization%NOTFOUND
2622 THEN
2623 CLOSE Cur_organization;
2624 RETURN NULL;
2625 ELSE
2626 CLOSE Cur_organization;
2627 RETURN l_organization_id;
2628 END IF;
2629 EXCEPTION
2630 WHEN OTHERS THEN
2631 RAISE;
2632 END Validate_Organization_code;
2633
2634 /***************************************************************************************
2635 * FUNCTION NAME *
2636 * Validate_inventory_item_id *
2637 * *
2638 * USAGE *
2639 * Validates Inventory Item Id *
2640 * *
2641 * DESCRIPTION *
2642 * This function validates that the Inventory Item Id exists *
2643 * *
2644 * PARAMETERS *
2645 * p_inventory_item_id IN mtl_system_items_b.inventory_item_id%TYPE*
2646 * p_organization_id IN mtl_organizations.organization_id%TYPE *
2647 * *
2648 * RETURNS *
2649 * TRUE - If Item Exists *
2650 * FALSE - If Item Doesnt Exist *
2651 * *
2652 * HISTORY *
2653 * 20-Oct-2005 Anand Thiyagarajan - Created *
2654 ***************************************************************************************/
2655 FUNCTION Validate_inventory_item_id
2656 (
2657 p_inventory_item_id IN mtl_system_items_b.inventory_item_id%TYPE,
2658 p_organization_id IN mtl_organizations.organization_id%TYPE
2659 )
2660 RETURN BOOLEAN
2661 IS
2662
2663 /**********
2664 * Cursors *
2665 **********/
2666
2667 CURSOR Cur_item
2668 IS
2669 SELECT inventory_item_id
2670 FROM mtl_system_items_b
2671 WHERE inventory_item_id = p_inventory_item_id
2672 AND organization_id = p_organization_id;
2673
2674 /******************
2675 * Local Variables *
2676 ******************/
2677
2678 l_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
2679
2680 BEGIN
2681 OPEN Cur_item;
2682 FETCH Cur_item INTO l_inventory_item_id;
2683 IF Cur_item%NOTFOUND
2684 THEN
2685 CLOSE Cur_item;
2686 RETURN FALSE;
2687 ELSE
2688 CLOSE Cur_item;
2689 RETURN TRUE;
2690 END IF;
2691 EXCEPTION
2692 WHEN OTHERS THEN
2693 RAISE;
2694 END Validate_inventory_item_id;
2695
2696 /***************************************************************************************
2697 * FUNCTION NAME *
2698 * Validate_item_number *
2699 * *
2700 * USAGE *
2701 * Validates Item Number *
2702 * *
2703 * DESCRIPTION *
2704 * This function validates that the Item Number exists *
2705 * *
2706 * PARAMETERS *
2707 * p_organization_id IN mtl_organizations.organization_id%TYPE *
2708 * p_item_number IN mtl_item_flexfields.item_number%TYPE *
2709 * *
2710 * RETURNS *
2711 * Inventory Item Id *
2712 * *
2713 * HISTORY *
2714 * 20-Oct-2005 Anand Thiyagarajan - Created *
2715 ***************************************************************************************/
2716 FUNCTION Validate_item_number
2717 (
2718 p_item_number IN mtl_item_flexfields.item_number%TYPE,
2719 p_organization_id IN mtl_organizations.organization_id%TYPE
2720 )
2721 RETURN NUMBER
2722 IS
2723
2724 /**********
2725 * Cursors *
2726 **********/
2727
2728 CURSOR Cur_item
2729 IS
2730 SELECT inventory_item_id
2731 FROM mtl_item_flexfields
2732 WHERE item_number = p_item_number
2733 AND organization_id = p_organization_id;
2734
2735 /******************
2736 * Local Variables *
2737 ******************/
2738
2739 l_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
2740
2741 BEGIN
2742 OPEN Cur_item;
2743 FETCH Cur_item INTO l_inventory_item_id;
2744 IF Cur_item%NOTFOUND
2745 THEN
2746 CLOSE Cur_item;
2747 RETURN NULL;
2748 ELSE
2749 CLOSE Cur_item;
2750 RETURN l_inventory_item_id;
2751 END IF;
2752 EXCEPTION
2753 WHEN OTHERS THEN
2754 RAISE;
2755 END Validate_item_number;
2756
2757 /***************************************************************************************
2758 * FUNCTION NAME *
2759 * Validate_lot_number *
2760 * *
2761 * USAGE *
2762 * Validates Lot Number *
2763 * *
2764 * DESCRIPTION *
2765 * This function validates that the Lot Number exists *
2766 * *
2767 * PARAMETERS *
2768 * p_inventory_item_id IN mtl_system_items_b.inventory_item_id%TYPE*
2769 * p_organization_id IN mtl_organizations.organization_id%TYPE *
2770 * p_lot_number in mtl_lot_numbers.lot_number%TYPE *
2771 * *
2772 * RETURNS *
2773 * TRUE - If Lot Exists *
2774 * FALSE - If Lot Doesnt Exist *
2775 * *
2776 * HISTORY *
2777 * 20-Oct-2005 Anand Thiyagarajan - Created *
2778 ***************************************************************************************/
2779 FUNCTION Validate_Lot_Number
2780 (
2781 p_lot_number IN mtl_lot_numbers.lot_number%TYPE,
2782 p_inventory_item_id IN mtl_system_items_b.inventory_item_id%TYPE,
2783 p_organization_id IN mtl_organizations.organization_id%TYPE
2784 )
2785 RETURN BOOLEAN
2786 IS
2787
2788 /**********
2789 * Cursors *
2790 **********/
2791
2792 CURSOR Cur_lot_number
2793 IS
2794 SELECT lot_number
2795 FROM mtl_lot_numbers
2796 WHERE inventory_item_id = p_inventory_item_id
2797 AND organization_id = p_organization_id
2798 AND lot_number = p_lot_number;
2799
2800 /******************
2801 * Local Variables *
2802 ******************/
2803
2804 l_lot_number mtl_lot_numbers.lot_number%TYPE;
2805
2806 BEGIN
2807 OPEN Cur_lot_number;
2808 FETCH Cur_lot_number INTO l_lot_number;
2809 IF Cur_lot_number%NOTFOUND
2810 THEN
2811 CLOSE Cur_lot_number;
2812 RETURN FALSE;
2813 ELSE
2814 CLOSE Cur_lot_number;
2815 RETURN TRUE;
2816 END IF;
2817 EXCEPTION
2818 WHEN OTHERS THEN
2819 RAISE;
2820 END Validate_Lot_Number;
2821
2822 /***************************************************************************************
2823 * FUNCTION NAME *
2824 * Validate_lot_Cost_type_id *
2825 * *
2826 * USAGE *
2827 * Validates Lot Cost Type Id *
2828 * *
2829 * DESCRIPTION *
2830 * This function validates that the Lot Cost Type Id exists *
2831 * *
2832 * PARAMETERS *
2833 * p_cost_type_id IN cm_mthd_mst.cost_type_id%TYPE *
2834 * *
2835 * RETURNS *
2836 * TRUE - If Lot Cost Type Exists *
2837 * FALSE - If Lot Cost Type Doesnt Exist *
2838 * *
2839 * HISTORY *
2840 * 20-Oct-2005 Anand Thiyagarajan - Created *
2841 ***************************************************************************************/
2842 FUNCTION Validate_Lot_Cost_type_id
2843 (
2844 p_cost_type_id IN cm_mthd_mst.cost_type_id%TYPE
2845 )
2846 RETURN BOOLEAN
2847 IS
2848
2849 /**********
2850 * Cursors *
2851 **********/
2852 CURSOR cur_cm_mthd_mst
2853 IS
2854 SELECT cost_type_id
2855 FROM cm_mthd_mst
2856 WHERE cm_mthd_mst.cost_type_id = p_cost_type_id
2857 AND cm_mthd_mst.delete_mark = 0
2858 AND cost_type = 6;
2859
2860 l_cost_type_id cm_mthd_mst.cost_type_id%TYPE;
2861
2862 BEGIN
2863 OPEN cur_cm_mthd_mst;
2864 FETCH cur_cm_mthd_mst INTO l_cost_type_id;
2865 IF cur_cm_mthd_mst%NOTFOUND
2866 THEN
2867 CLOSE cur_cm_mthd_mst;
2868 RETURN FALSE;
2869 ELSE
2870 CLOSE cur_cm_mthd_mst;
2871 RETURN TRUE;
2872 END IF;
2873 EXCEPTION
2874 WHEN OTHERS THEN
2875 RAISE;
2876 END Validate_Lot_Cost_type_id;
2877
2878 /***************************************************************************************
2879 * FUNCTION NAME *
2880 * Validate_Lot_Cost_Type *
2881 * *
2882 * USAGE *
2883 * Validates Lot Cost Method Code *
2884 * *
2885 * DESCRIPTION *
2886 * This function validates that the Lot Cost Method Code exists *
2887 * *
2888 * PARAMETERS *
2889 * p_cost_mthd_code IN cm_mthd_mst.cost_mthd_code%TYPE *
2890 * *
2891 * RETURNS *
2892 * Lot Cost Type Id *
2893 * *
2894 * HISTORY *
2895 * 20-Oct-2005 Anand Thiyagarajan - Created *
2896 ***************************************************************************************/
2897 FUNCTION Validate_Lot_Cost_Type
2898 (
2899 p_cost_mthd_code IN cm_mthd_mst.cost_mthd_code%TYPE
2900 )
2901 RETURN NUMBER
2902 IS
2903
2904 /**********
2905 * Cursors *
2906 **********/
2907 CURSOR cur_cm_mthd_mst
2908 IS
2909 SELECT cost_type_id
2910 FROM cm_mthd_mst
2911 WHERE cm_mthd_mst.cost_mthd_code = p_cost_mthd_code
2912 AND cm_mthd_mst.delete_mark = 0
2913 AND cost_type = 6;
2914
2915 l_cost_type_id cm_mthd_mst.cost_type_id%TYPE;
2916
2917 BEGIN
2918 OPEN cur_cm_mthd_mst;
2919 FETCH cur_cm_mthd_mst INTO l_cost_type_id;
2920 IF cur_cm_mthd_mst%NOTFOUND
2921 THEN
2922 CLOSE cur_cm_mthd_mst;
2923 RETURN NULL;
2924 ELSE
2925 CLOSE cur_cm_mthd_mst;
2926 RETURN l_cost_type_id;
2927 END IF;
2928 EXCEPTION
2929 WHEN OTHERS THEN
2930 RAISE;
2931 END Validate_Lot_Cost_Type;
2932
2933
2934 /***************************************************************************************
2935 * FUNCTION NAME *
2936 * Fetch_alloc_id *
2937 * *
2938 * USAGE *
2939 * gets teh alloc id *
2940 * *
2941 * DESCRIPTION *
2942 * This function gets the alloc id using alloc code and le id *
2943 * *
2944 * PARAMETERS *
2945 * p_Alloc_Code IN gl_aloc_mst.Alloc_Code%TYPE *
2946 * p_le_id IN xle_entity_profiles.legal_entity_id%TYPE *
2947 * *
2948 * RETURNS *
2949 * Alloc Id *
2950 * *
2951 * HISTORY *
2952 * 20-Oct-2005 Jahnavi Boppana - Created *
2953 ***************************************************************************************/
2954 FUNCTION Fetch_Alloc_Id
2955 (
2956 p_Alloc_Code IN gl_aloc_mst.Alloc_Code%TYPE
2957 , p_le_id IN xle_entity_profiles.legal_entity_id%TYPE
2958 )
2959 RETURN NUMBER
2960 IS
2961 CURSOR Cur_Alloc_Id
2962 IS
2963 SELECT
2964 Alloc_id
2965 FROM
2966 gl_aloc_mst
2967 WHERE
2968 Alloc_Code = p_Alloc_Code
2969 AND legal_entity_id = p_le_id
2970 AND delete_mark = 0;
2971
2972 l_Alloc_id gl_aloc_mst.Alloc_id%TYPE := '' ;
2973
2974 BEGIN
2975
2976 OPEN Cur_Alloc_Id;
2977 FETCH Cur_Alloc_Id INTO l_Alloc_Id;
2978 IF (Cur_Alloc_Id%NOTFOUND)
2979 THEN
2980 CLOSE Cur_Alloc_Id;
2981 RETURN NULL;
2982 ELSE
2983 CLOSE Cur_Alloc_Id;
2984 RETURN l_Alloc_Id;
2985 END IF;
2986
2987 EXCEPTION
2988 WHEN OTHERS THEN
2989 RAISE;
2990
2991 End Fetch_Alloc_Id ;
2992
2993
2994 /***************************************************************************************
2995 * FUNCTION NAME *
2996 * Validate_Basis_account_key *
2997 * *
2998 * USAGE *
2999 * Validates the Basis_account_key *
3000 * *
3001 * DESCRIPTION *
3002 * This function returns the account id for teh account key *
3003 * *
3004 * PARAMETERS *
3005 * p_Basis_account_key IN gl_aloc_bas.Basis_account_key%TYPE *
3006 * p_le_id IN xle_entity_profiles.legal_entity_id%TYPE *
3007 * *
3008 * RETURNS *
3009 * Account Id *
3010 * *
3011 * HISTORY *
3012 * 20-Oct-2005 Jahnavi Boppana - Created *
3013 ***************************************************************************************/
3014
3015
3016 FUNCTION Validate_Basis_account_key
3017 (
3018 p_Basis_account_key IN gl_aloc_bas.Basis_account_key%TYPE
3019 , p_le_id IN xle_entity_profiles.legal_entity_id%TYPE
3020 )
3021 RETURN NUMBER
3022 IS
3023
3024 CURSOR Cur_chart_of_accounts_id
3025 IS
3026 SELECT chart_of_accounts_id
3027 FROM gmf_legal_entities
3028 WHERE legal_entity_id = p_le_id ;
3029
3030 l_account_id gl_aloc_bas.Basis_account_id%TYPE;
3031 l_chart_of_accounts_id gmf_legal_entities.chart_of_accounts_id%TYPE;
3032
3033 BEGIN
3034
3035 OPEN Cur_chart_of_accounts_id;
3036 FETCH Cur_chart_of_accounts_id INTO l_chart_of_accounts_id;
3037 IF (Cur_chart_of_accounts_id%NOTFOUND)
3038 THEN
3039 CLOSE Cur_chart_of_accounts_id;
3040 RETURN NULL;
3041 ELSE
3042 CLOSE Cur_chart_of_accounts_id;
3043 l_account_id := fnd_flex_ext.get_ccid(application_short_name => 'SQLGL',
3044 key_flex_code => 'GL#',
3045 structure_number => l_chart_of_accounts_id,
3046 validation_date => SYSDATE,
3047 concatenated_segments => p_Basis_account_key);
3048 IF l_account_id = 0 THEN
3049 RETURN NULL;
3050 ELSE
3051 RETURN l_account_id;
3052 END IF;
3053
3054 END IF;
3055
3056 EXCEPTION
3057 WHEN OTHERS THEN
3058 RAISE;
3059
3060 End Validate_Basis_account_key ;
3061
3062 /***************************************************************************************
3063 * FUNCTION NAME *
3064 * Validate_account_id *
3065 * *
3066 * USAGE *
3067 * Validates the account id *
3068 * *
3069 * DESCRIPTION *
3070 * Validates the account id *
3071 * *
3072 * PARAMETERS *
3073 * p_Basis_account_key IN gl_aloc_bas.Basis_account_key%TYPE *
3074 * p_le_id IN xle_entity_profiles.legal_entity_id%TYPE *
3075 * *
3076 * RETURNS *
3077 * TRUE/FALSE *
3078 * *
3079 * HISTORY *
3080 * 20-Oct-2005 Jahnavi Boppana - Created *
3081 ***************************************************************************************/
3082
3083 FUNCTION Validate_ACCOUNT_ID
3084 (
3085 p_Basis_account_id IN gl_aloc_bas.Basis_account_id%TYPE
3086 , p_le_id IN xle_entity_profiles.legal_entity_id%TYPE
3087 )
3088 RETURN BOOLEAN
3089 IS
3090
3091 CURSOR Cur_accounts_id
3092 IS
3093 SELECT code_combination_id
3094 FROM gl_code_combinations_kfv glc, gmf_legal_entities gle
3095 WHERE gle.legal_entity_id = p_le_id
3096 AND glc.code_combination_id = p_Basis_account_id
3097 AND gle.chart_of_accounts_id = glc.chart_of_accounts_id ;
3098
3099 l_account_id gl_aloc_bas.Basis_account_id%TYPE;
3100
3101 BEGIN
3102
3103 OPEN Cur_accounts_id;
3104 FETCH Cur_accounts_id INTO l_account_id;
3105 IF (Cur_accounts_id%NOTFOUND)
3106 THEN
3107 CLOSE Cur_accounts_id;
3108 RETURN FALSE;
3109 ELSE
3110 CLOSE Cur_accounts_id;
3111 RETURN TRUE;
3112 END IF;
3113
3114 EXCEPTION
3115 WHEN OTHERS THEN
3116 RAISE;
3117
3118 End Validate_ACCOUNT_ID ;
3119
3120 -- Func start of comments
3121 --+==========================================================================+
3122 --| FUNCTION NAME |
3123 --| Validate_Usage_Uom |
3124 --| |
3125 --| USAGE |
3126 --| Validates UOM |
3127 --| |
3128 --| DESCRIPTION |
3129 --| This function validates that the UOM codes exists |
3130 --| on mtl_units_of_measure |
3131 --| |
3132 --| PARAMETERS |
3133 --| P_usgae_uom IN mtl_units_of_measure.uom_code%TYPE - UOM code |
3134 --| |
3135 --| RETURNS |
3136 --| TRUE - If UOM code is valid value |
3137 --| FALSE - If UOM codeis an invalid value |
3138 --| |
3139 --| HISTORY |
3140 --| 20-Oct-2005 Prasad Marada - Created for UOM Code validation |
3141 --| |
3142 --+==========================================================================+
3143 -- Func end of comments
3144
3145 FUNCTION Validate_Usage_Uom
3146 (
3147 P_usgae_uom IN mtl_units_of_measure.uom_code%TYPE
3148 ) RETURN BOOLEAN
3149 IS
3150
3151 CURSOR Cur_uom IS
3152 SELECT uom_code
3153 FROM mtl_units_of_measure
3154 WHERE uom_code = P_usgae_uom;
3155
3156 l_usgae_uom mtl_units_of_measure.uom_code%TYPE ;
3157
3158 BEGIN
3159
3160 OPEN Cur_uom;
3161 FETCH Cur_uom INTO l_usgae_uom;
3162 IF (Cur_uom%NOTFOUND)
3163 THEN
3164 CLOSE Cur_uom;
3165 RETURN FALSE;
3166 ELSE
3167 CLOSE Cur_uom;
3168 RETURN TRUE;
3169 END IF;
3170
3171 EXCEPTION
3172 WHEN OTHERS THEN
3173 RAISE;
3174 END Validate_Usage_Uom;
3175
3176 -- Func start of comments
3177 --+==========================================================================+
3178 --| FUNCTION NAME |
3179 --| Validate_Validate_same_class_Uom |
3180 --| |
3181 --| USAGE |
3182 --| Validates UOM |
3183 --| |
3184 --| DESCRIPTION |
3185 --| This function validates that the UOM codes exists |
3186 --| on mtl_units_of_measure for the item's class |
3187 --| |
3188 --| PARAMETERS |
3189 --| P_usgae_uom IN mtl_units_of_measure.uom_code%TYPE - UOM code |
3190 --| |
3191 --| RETURNS |
3192 --| TRUE - If UOM code is valid value |
3193 --| FALSE - If UOM codeis an invalid value |
3194 --| |
3195 --| HISTORY |
3196 --| 10-Oct-2006 Anand Thiyagarajan - Created for UOM Code validation |
3197 --| |
3198 --+==========================================================================+
3199 -- Func end of comments
3200
3201 FUNCTION Validate_same_class_Uom
3202 (
3203 P_uom_code IN mtl_units_of_measure.uom_code%TYPE,
3204 p_inventory_item_id IN mtl_system_items_b.inventory_item_id%TYPE,
3205 p_organization_id IN mtl_system_items_b.organization_id%TYPE
3206 ) RETURN BOOLEAN
3207 IS
3208
3209 CURSOR Cur_uom
3210 IS
3211 SELECT c.uom_code
3212 FROM mtl_system_items_b a,
3213 mtl_units_of_measure b,
3214 mtl_units_of_measure c
3215 WHERE a.inventory_item_id = p_inventory_item_id
3216 AND a.organization_id = p_organization_id
3217 AND a.primary_uom_code = b.uom_code
3218 AND b.uom_class = c.uom_class
3219 AND c.uom_code= P_uom_code;
3220
3221 l_uom_code mtl_units_of_measure.uom_code%TYPE;
3222 BEGIN
3223
3224 OPEN Cur_uom;
3225 FETCH Cur_uom INTO l_uom_code;
3226 IF (Cur_uom%NOTFOUND)
3227 THEN
3228 CLOSE Cur_uom;
3229 RETURN FALSE;
3230 ELSE
3231 CLOSE Cur_uom;
3232 RETURN TRUE;
3233 END IF;
3234 EXCEPTION
3235 WHEN OTHERS THEN
3236 RAISE;
3237 END Validate_same_class_Uom;
3238
3239 /* Introduced against Bug#14101225 */
3240 -- Func start of comments
3241 --+==========================================================================+
3242 --| FUNCTION NAME |
3243 --| Validate_Uom_Conv |
3244 --| |
3245 --| USAGE |
3246 --| Validates UOM |
3247 --| |
3248 --| DESCRIPTION |
3249 --| This function validates that the UOM conversion exists |
3250 --| |
3251 --| |
3252 --| PARAMETERS |
3253 --| p_adjust_uom IN cm_adjs_dtl.adjust_qty_um%TYPE, |
3254 --| p_inventory_item_id IN mtl_system_items_b.inventory_item_id%TYPE, |
3255 --| p_organization_id IN mtl_system_items_b.organization_id%TYPE, |
3256 --| p_adjust_qty IN cm_adjs_dtl.adjust_qty%TYPE |
3257 --| |
3258 --| RETURNS |
3259 --| TRUE - If UOM code is valid one |
3260 --| FALSE - If UOM code is an invalid one |
3261 --| |
3262 --| HISTORY |
3263 --| 31-MAY-2012 Saptagirish Pabolu - Created for UOM Conversion Validation |
3264 --| |
3265 --+==========================================================================+
3266 -- Func end of comments
3267 FUNCTION Validate_Uom_Conv
3268 (
3269 p_adjust_uom IN cm_adjs_dtl.adjust_qty_um%TYPE,
3270 p_inventory_item_id IN mtl_system_items_b.inventory_item_id%TYPE,
3271 p_organization_id IN mtl_system_items_b.organization_id%TYPE,
3272 p_adjust_qty IN cm_adjs_dtl.adjust_qty%TYPE
3273 ) RETURN BOOLEAN
3274 IS
3275
3276 l_primary_uom_code mtl_system_items_b.primary_uom_code%TYPE;
3277 l_conv_qty cm_adjs_dtl.adjust_qty%TYPE;
3278
3279 BEGIN
3280
3281 SELECT a.primary_uom_code
3282 INTO l_primary_uom_code
3283 FROM mtl_system_items_b a
3284 WHERE a.inventory_item_id = p_inventory_item_id
3285 AND a.organization_id = p_organization_id;
3286
3287 l_conv_qty := inv_convert.inv_um_convert(p_inventory_item_id,
3288 NULL,
3289 p_organization_id,
3290 5,
3291 p_adjust_qty,
3292 p_adjust_uom,
3293 l_primary_uom_code,
3294 NULL,
3295 NULL
3296 );
3297
3298 IF NVL(l_conv_qty, -99999) <> -99999
3299 THEN
3300 RETURN TRUE;
3301 ELSE
3302 RETURN FALSE;
3303 END IF;
3304 EXCEPTION
3305 WHEN OTHERS THEN
3306 RAISE;
3307 END Validate_Uom_Conv;
3308
3309 -- Procedure start of comments
3310 --+==========================================================================+
3311 --| FUNCTION NAME |
3312 --| validate_usage_uom |
3313 --| |
3314 --| USAGE |
3315 --| Returns UOM Class |
3316 --| |
3317 --| DESCRIPTION |
3318 --| This procedure returns the UOM class for the UOM code |
3319 --| |
3320 --| PARAMETERS |
3321 --| p_usage_uom IN mtl_units_of_measure.uom_code%TYPE UOM Code |
3322 --| p_usage_uom_class OUT mtl_units_of_measure.uom_class%TYPE |
3323 --| |
3324 --| RETURNS |
3325 --| Return UOM class |
3326 --| |
3327 --| HISTORY |
3328 --| 20-Oct-2005 Prasad Marada - Created to return the UOM class |
3329 --| |
3330 --+==========================================================================+
3331 -- Procedure end of comments
3332
3333 PROCEDURE validate_usage_uom (
3334 p_usage_uom IN mtl_units_of_measure.uom_code%TYPE,
3335 p_usage_uom_class OUT NOCOPY mtl_units_of_measure.uom_class%TYPE
3336 ) IS
3337
3338 CURSOR cur_uom IS
3339 SELECT uom_class
3340 FROM mtl_units_of_measure
3341 WHERE uom_code = p_usage_uom;
3342
3343 BEGIN
3344 OPEN cur_uom;
3345 FETCH cur_uom INTO p_usage_uom_class;
3346 CLOSE cur_uom ;
3347
3348 EXCEPTION
3349 WHEN OTHERS THEN
3350 RAISE;
3351
3352 END validate_usage_uom ;
3353
3354
3355 PROCEDURE Validate_Resource
3356 (
3357 p_Resources IN cr_rsrc_mst.Resources%TYPE
3358 , x_resource_uom OUT NOCOPY cr_rsrc_mst.std_usage_uom%TYPE
3359 , x_resource_uom_class OUT NOCOPY mtl_units_of_measure.uom_class%TYPE
3360 )
3361 IS
3362 CURSOR Cur_resources
3363 IS
3364 SELECT
3365 rm.std_usage_uom, uom.uom_class
3366 FROM
3367 mtl_units_of_measure uom, cr_rsrc_mst rm
3368 WHERE
3369 rm.resources = p_Resources
3370 AND uom.uom_code = rm.std_usage_uom
3371 AND rm.delete_mark = 0;
3372
3373 BEGIN
3374
3375 OPEN Cur_resources;
3376 FETCH Cur_resources INTO x_resource_uom, x_resource_uom_class;
3377 CLOSE Cur_resources;
3378
3379 EXCEPTION
3380 WHEN OTHERS THEN
3381 RAISE;
3382
3383 End Validate_Resource;
3384
3385 /* ANTHIYAG Added for Release 12.0 End */
3386
3387 END GMF_validations_PVT;