1 PACKAGE BODY IGS_AS_VAL_UAPI AS
2 /* $Header: IGSAS36B.pls 115.5 2002/11/28 22:48:17 nsidana ship $ */
3 -- Val IGS_PS_UNIT offering option restrictions match at pattern and item level.
4 FUNCTION ASSP_VAL_UAPI_UOO(
5 p_unit_cd IN VARCHAR2 ,
6 p_version_number IN NUMBER ,
7 p_cal_type IN VARCHAR2 ,
8 p_ci_sequence_number IN NUMBER ,
9 p_ass_pattern_id IN NUMBER ,
10 p_ass_id IN NUMBER ,
11 p_uai_sequence_number IN NUMBER ,
12 p_message_name OUT NOCOPY VARCHAR2)
13 RETURN BOOLEAN IS
14 gv_other_detail VARCHAR2(255);
15 BEGIN -- assp_val_uapi_uoo
16 -- This module validate that the IGS_AS_ASSESSMNT_ITM being assigned to the
17 -- IGS_AS_UNT_PATRN_ITM is valid for the IGS_PS_UNIT offering options that the
18 -- IGS_AS_UNTAS_PATTERN applies too. That is, the IGS_AD_LOCATION, mode and
19 -- class must be identical.
20 DECLARE
21 cst_null CONSTANT VARCHAR2(4) := 'NULL';
22 v_uap_location_cd IGS_AS_UNTAS_PATTERN.location_cd%TYPE;
23 v_uap_unit_class IGS_AS_UNTAS_PATTERN.unit_class%TYPE;
24 v_uap_unit_mode IGS_AS_UNTAS_PATTERN.unit_mode%TYPE;
25 v_uai_location_cd IGS_AS_UNITASS_ITEM.location_cd%TYPE;
26 v_uai_unit_class IGS_AS_UNITASS_ITEM.unit_class%TYPE;
27 v_uai_unit_mode IGS_AS_UNITASS_ITEM.unit_mode%TYPE;
28 CURSOR c_uap IS
29 SELECT uap.location_cd,
30 uap.unit_class,
31 uap.unit_mode
32 FROM IGS_AS_UNTAS_PATTERN uap
33 WHERE uap.ass_pattern_id = p_ass_pattern_id;
34 CURSOR c_uai IS
35 SELECT uai.location_cd,
36 uai.unit_class,
37 uai.unit_mode
38 FROM IGS_AS_UNITASS_ITEM uai
39 WHERE uai.unit_cd = p_unit_cd AND
40 uai.version_number = p_version_number AND
41 uai.cal_type = p_cal_type AND
42 uai.ci_sequence_number = p_ci_sequence_number AND
43 uai.ass_id = p_ass_id AND
44 uai.sequence_number = p_uai_sequence_number;
45 BEGIN
46 OPEN c_uap;
47 FETCH c_uap INTO v_uap_location_cd,
48 v_uap_unit_class,
49 v_uap_unit_mode;
50 IF (c_uap%NOTFOUND) THEN
51 CLOSE c_uap;
52 RAISE NO_DATA_FOUND;
53 END IF;
54 CLOSE c_uap;
55 OPEN c_uai;
56 FETCH c_uai INTO v_uai_location_cd,
57 v_uai_unit_class,
58 v_uai_unit_mode;
59 IF (c_uai%NOTFOUND) THEN
60 CLOSE c_uai;
61 RAISE NO_DATA_FOUND;
62 END IF;
63 CLOSE c_uai;
64 IF (NVL(v_uai_location_cd, cst_null) <> NVL(v_uap_location_cd, cst_null)) OR
65 (NVL(v_uai_unit_class, cst_null) <> NVL(v_uap_unit_class, cst_null)) OR
66 (NVL(v_uai_unit_mode, cst_null) <> NVL(v_uap_unit_mode, cst_null)) THEN
67 p_message_name := 'IGS_AS_LOCCD_UNITCLASS_UNITMO';
68 RETURN FALSE;
69 END IF;
70 p_message_name := null;
71 RETURN TRUE;
72 EXCEPTION
73 WHEN OTHERS THEN
74 IF (c_uai%ISOPEN) THEN
75 CLOSE c_uai;
76 END IF;
77 IF (c_uap%ISOPEN) THEN
78 CLOSE c_uap;
79 END IF;
80 RAISE;
81 END;
82 EXCEPTION
83 WHEN OTHERS THEN
84 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
85 Fnd_Message.Set_Token('NAME','IGS_AS_VAL_UAPI.ASSP_VAL_UAPI_UOO');
86 Igs_Ge_Msg_Stack.Add;
87 App_Exception.Raise_Exception;
88 END assp_val_uapi_uoo;
89 --
90 -- Validate the to apportionment percentage does not exceed 100 for uap.
91 FUNCTION ASSP_VAL_UAPI_AP(
92 p_unit_cd IN VARCHAR2 ,
93 p_version_number IN NUMBER ,
94 p_cal_type IN VARCHAR2 ,
95 p_ci_sequence_number IN NUMBER ,
96 p_ass_pattern_id IN NUMBER ,
97 p_message_name OUT NOCOPY VARCHAR2)
98 RETURN BOOLEAN IS
99 gv_other_detail VARCHAR2(255);
100 BEGIN -- assp_val_uapi_ap
101 DECLARE
102 v_total_apportionment_percent NUMBER;
103 CURSOR c_uapi IS
104 SELECT NVL(SUM(uapi.apportionment_percentage), 0)
105 FROM IGS_AS_UNT_PATRN_ITM uapi
106 WHERE uapi.unit_cd = p_unit_cd AND
107 uapi.version_number = p_version_number AND
108 uapi.cal_type = p_cal_type AND
109 uapi.ci_sequence_number = p_ci_sequence_number AND
110 uapi.ass_pattern_id = p_ass_pattern_id;
111 BEGIN
112 -- Set the default message number
113 p_message_name := null;
114 -- Cursor handling
115 OPEN c_uapi;
116 FETCH c_uapi INTO v_total_apportionment_percent;
117 IF c_uapi %NOTFOUND THEN
118 CLOSE c_uapi;
119 RETURN TRUE;
120 END IF;
121 CLOSE c_uapi;
122 IF v_total_apportionment_percent > 100.00 THEN
123 --p_message_name := 'IGS_GR_SPECIFY_PRXY_AWD_PERS';
124 p_message_name := 'IGS_AS_PERC_APPORTION_EX_100';
125 RETURN FALSE;
126 END IF;
127 -- Return the default value
128 RETURN TRUE;
129 EXCEPTION
130 WHEN OTHERS THEN
131 IF c_uapi %ISOPEN THEN
132 CLOSE c_uapi;
133 END IF;
134 RAISE;
135 END;
136 EXCEPTION
137 WHEN OTHERS THEN
138 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
139 Fnd_Message.Set_Token('NAME','IGS_AS_VAL_UAPI.IGS_AS_VAL_UAPI');
140 Igs_Ge_Msg_Stack.Add;
141 App_Exception.Raise_Exception;
142 END assp_val_uapi_ap;
143 END IGS_AS_VAL_UAPI;
[Home] [Help]