[Home] [Help]
PACKAGE BODY: APPS.IGW_PROP_RATES_TBH
Source
1 PACKAGE BODY IGW_PROP_RATES_TBH AS
2 -- $Header: igwtprtb.pls 115.5 2002/11/15 00:44:47 ashkumar ship $
3
4 procedure INSERT_ROW (
5 p_proposal_id NUMBER
6 ,p_version_id NUMBER
7 ,p_rate_class_id NUMBER
8 ,p_rate_type_id NUMBER
9 ,p_fiscal_year VARCHAR2
10 ,p_location_code VARCHAR2
11 ,p_activity_type_code VARCHAR2
12 ,p_start_date DATE
13 ,p_applicable_rate NUMBER
14 ,p_institute_rate NUMBER
15 ,x_rowid OUT NOCOPY VARCHAR2
16 ,x_return_status OUT NOCOPY VARCHAR2) IS
17
18 cursor c_rates is
19 select rowid
20 from igw_prop_rates
21 where proposal_id = p_proposal_id
22 and version_id = p_version_id
23 and rate_class_id = p_rate_class_id
24 and rate_type_id = p_rate_type_id
25 and location_code = p_location_code
26 and activity_type_code = p_activity_type_code
27 and fiscal_year = p_fiscal_year;
28
29 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
30 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
31 l_last_update_date DATE := SYSDATE;
32
33 begin
34 x_return_status := FND_API.G_RET_STS_SUCCESS;
35
36 insert into igw_prop_rates(
37 proposal_id
38 ,version_id
39 ,rate_class_id
40 ,rate_type_id
41 ,fiscal_year
42 ,location_code
43 ,activity_type_code
44 ,start_date
45 ,applicable_rate
46 ,institute_rate
47 ,last_update_date
48 ,last_updated_by
49 ,creation_date
50 ,created_by
51 ,last_update_login
52 ,record_version_number)
53 values
54 ( p_proposal_id
55 ,p_version_id
56 ,p_rate_class_id
57 ,p_rate_type_id
58 ,p_fiscal_year
59 ,p_location_code
60 ,p_activity_type_code
61 ,p_start_date
62 ,p_applicable_rate
63 ,p_institute_rate
64 ,l_last_update_date
65 ,l_last_updated_by
66 ,l_last_update_date
67 ,l_last_updated_by
68 ,l_last_update_login
69 ,1);
70
71 open c_rates;
72 fetch c_rates into x_ROWID;
73 if (c_rates%notfound) then
74 close c_rates;
75 raise no_data_found;
76 end if;
77 close c_rates;
78
79 exception
80 when others then
81 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_package_name
82 ,p_procedure_name => 'INSERT_ROW' );
83 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
84 RAISE;
85 end insert_row;
86
87
88 -----------------------------------------------------------------------------------
89 procedure UPDATE_ROW (
90 p_proposal_id NUMBER
91 ,p_version_id NUMBER
92 ,p_rate_class_id NUMBER
93 ,p_rate_type_id NUMBER
94 ,p_fiscal_year VARCHAR2
95 ,p_location_code VARCHAR2
96 ,p_activity_type_code VARCHAR2
97 ,p_start_date DATE
98 ,p_applicable_rate NUMBER
99 ,p_institute_rate NUMBER
100 ,p_rowid VARCHAR2
101 ,p_record_version_number NUMBER
102 ,x_return_status OUT NOCOPY VARCHAR2) IS
103
104 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
105 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
106 l_last_update_date DATE := SYSDATE;
107
108 l_row_id ROWID := p_rowid;
109
110 CURSOR get_row_id IS
111 select rowid
112 from igw_prop_rates
113 where proposal_id = p_proposal_id
114 and version_id = p_version_id
115 and rate_class_id = p_rate_class_id
116 and rate_type_id = p_rate_type_id
117 and location_code = p_location_code
118 and activity_type_code = p_activity_type_code
119 and fiscal_year = p_fiscal_year;
120
121 begin
122 x_return_status := FND_API.G_RET_STS_SUCCESS;
123
124 IF l_row_id IS NULL THEN
125 OPEN get_row_id;
126 FETCH get_row_id INTO l_row_id;
127 CLOSE get_row_id;
128 END IF;
129
130 update igw_prop_rates
131 set applicable_rate = p_applicable_rate
132 where rowid = l_row_id;
133 --and record_version_number = p_record_version_number;
134
135 if (sql%notfound) then
136 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
137 FND_MSG_PUB.Add;
138 X_RETURN_STATUS := 'E';
139 end if;
140
141 exception
142 when others then
143 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_package_name
144 ,p_procedure_name => 'UPDATE_ROW' );
145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
146 RAISE;
147 end update_row;
148 -------------------------------------------------------------------------------------------
149
150 procedure DELETE_ROW (
151 p_rowid IN ROWID
152 ,p_proposal_id NUMBER
153 ,p_version_id NUMBER
154 ,p_rate_class_id NUMBER
155 ,p_rate_type_id NUMBER
156 ,p_fiscal_year VARCHAR2
157 ,p_location_code VARCHAR2
158 ,p_activity_type_code VARCHAR2
159 ,p_start_date DATE
160 ,p_applicable_rate NUMBER
161 ,p_institute_rate NUMBER
162 ,p_record_version_number IN NUMBER
163 ,x_return_status OUT NOCOPY VARCHAR2) is
164
165 l_row_id ROWID := p_rowid;
166
167
168 CURSOR get_row_id IS
169 select rowid
170 from igw_prop_rates
171 where proposal_id = p_proposal_id
172 and version_id = p_version_id
173 and rate_class_id = p_rate_class_id
174 and rate_type_id = p_rate_type_id
175 and location_code = p_location_code
176 and activity_type_code = p_activity_type_code
177 and fiscal_year = p_fiscal_year;
178
179 begin
180
181 x_return_status := FND_API.G_RET_STS_SUCCESS;
182
183 IF l_row_id IS NULL THEN
184 OPEN get_row_id;
185 FETCH get_row_id INTO l_row_id;
186 CLOSE get_row_id;
187 END IF;
188
189 delete from igw_prop_rates
190 where rowid = l_row_id;
191 --and record_version_number = p_record_version_number;
192
193 if (sql%notfound) then
194 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
195 FND_MSG_PUB.Add;
196 X_RETURN_STATUS := 'E';
197 end if;
198
199 EXCEPTION
200 WHEN OTHERS THEN
201 FND_MSG_PUB.add_exc_msg( p_pkg_name => G_package_name
202 ,p_procedure_name => 'DELETE_ROW' );
203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
204 RAISE;
205 end DELETE_ROW;
206
207
208 END IGW_PROP_RATES_TBH;