[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_SUBTEAMS_PKG
Source
1 PACKAGE BODY PA_PROJECT_SUBTEAMS_pkg AS
2 --$Header: PARTSTHB.pls 120.1 2005/08/19 17:01:53 mwasowic noship $
3
4 PROCEDURE Insert_Row
5 (
6
7 p_subteam_name IN pa_project_subteams.name%TYPE := FND_API.g_miss_char ,
8 p_object_type IN pa_project_subteams.object_type%TYPE := FND_API.g_miss_char,
9 p_object_id IN pa_project_subteams.object_id%TYPE := FND_API.g_miss_num,
10 p_description IN pa_project_subteams.description%TYPE := FND_API.g_miss_char ,
11 p_attribute_category IN pa_project_subteams.attribute_category%TYPE := FND_API.g_miss_char ,
12 p_attribute1 IN pa_project_subteams.attribute1%TYPE := FND_API.g_miss_char ,
13 p_attribute2 IN pa_project_subteams.attribute2%TYPE := FND_API.g_miss_char ,
14 p_attribute3 IN pa_project_subteams.attribute3%TYPE := FND_API.g_miss_char ,
15 p_attribute4 IN pa_project_subteams.attribute4%TYPE := FND_API.g_miss_char ,
16 p_attribute5 IN pa_project_subteams.attribute5%TYPE := FND_API.g_miss_char ,
17 p_attribute6 IN pa_project_subteams.attribute6%TYPE := FND_API.g_miss_char ,
18 p_attribute7 IN pa_project_subteams.attribute7%TYPE := FND_API.g_miss_char ,
19 p_attribute8 IN pa_project_subteams.attribute8%TYPE := FND_API.g_miss_char ,
20 p_attribute9 IN pa_project_subteams.attribute9%TYPE := FND_API.g_miss_char ,
21 p_attribute10 IN pa_project_subteams.attribute10%TYPE := FND_API.g_miss_char ,
22 p_attribute11 IN pa_project_subteams.attribute11%TYPE := FND_API.g_miss_char ,
23 p_attribute12 IN pa_project_subteams.attribute12%TYPE := FND_API.g_miss_char ,
24 p_attribute13 IN pa_project_subteams.attribute13%TYPE := FND_API.g_miss_char ,
25 p_attribute14 IN pa_project_subteams.attribute14%TYPE := FND_API.g_miss_char ,
26 p_attribute15 IN pa_project_subteams.attribute15%TYPE := FND_API.g_miss_char ,
27 x_subteam_row_id OUT NOCOPY ROWID, --File.Sql.39 bug 4440895
28 x_new_subteam_id OUT NOCOPY pa_project_subteams.project_subteam_id%TYPE, --File.Sql.39 bug 4440895
29 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
30 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
31 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
32 ) IS
33
34 l_subteam_id NUMBER;
35 l_record_version_number NUMBER := 1;
36
37 CURSOR c1 IS
38 SELECT rowid
39 FROM pa_project_subteams
40 WHERE project_subteam_id = l_subteam_id;
41
42 BEGIN
43
44 x_return_status := FND_API.G_RET_STS_SUCCESS;
45
46 --Fetch the next sequence number for subteam
47 --SELECT pa_project_subteams_s.NEXTVAL
48 --INTO l_subteam_id
49 --FROM dual;
50
51
52 INSERT INTO pa_project_subteams
53 (project_subteam_id,
54 name,
55 object_type,
56 object_id,
57 description,
58 record_version_number,
59 attribute_category,
60 attribute1,
61 attribute2,
62 attribute3,
63 attribute4,
64 attribute5,
65 attribute6,
66 attribute7,
67 attribute8,
68 attribute9,
69 attribute10,
70 attribute11,
71 attribute12,
72 attribute13,
73 attribute14,
74 attribute15,
75 creation_date,
76 created_by,
77 last_update_date,
78 last_updated_by,
79 last_update_login )
80 VALUES
81 (
82 pa_project_subteams_s.NEXTVAL,
83 DECODE(p_subteam_name, FND_API.G_MISS_CHAR, NULL, p_subteam_name),
84 DECODE(p_object_type, FND_API.G_MISS_CHAR, NULL, p_object_type),
85 DECODE(p_object_id, FND_API.G_MISS_NUM, NULL, p_object_id),
86 DECODE(p_description, FND_API.G_MISS_CHAR, NULL, p_description),
87 l_record_version_number,
88 DECODE(p_attribute_category, FND_API.G_MISS_CHAR, NULL, p_attribute_category),
89 DECODE(p_attribute1, FND_API.G_MISS_CHAR, NULL, p_attribute1),
90 DECODE(p_attribute2, FND_API.G_MISS_CHAR, NULL, p_attribute2),
91 DECODE(p_attribute3, FND_API.G_MISS_CHAR, NULL, p_attribute3),
92 DECODE(p_attribute4, FND_API.G_MISS_CHAR, NULL, p_attribute4),
93 DECODE(p_attribute5, FND_API.G_MISS_CHAR, NULL, p_attribute5),
94 DECODE(p_attribute6, FND_API.G_MISS_CHAR, NULL, p_attribute6),
95 DECODE(p_attribute7, FND_API.G_MISS_CHAR, NULL, p_attribute7),
96 DECODE(p_attribute8, FND_API.G_MISS_CHAR, NULL, p_attribute8),
97 DECODE(p_attribute9, FND_API.G_MISS_CHAR, NULL, p_attribute9),
98 DECODE(p_attribute10, FND_API.G_MISS_CHAR, NULL, p_attribute10),
99 DECODE(p_attribute11, FND_API.G_MISS_CHAR, NULL, p_attribute11),
100 DECODE(p_attribute12, FND_API.G_MISS_CHAR, NULL, p_attribute12),
101 DECODE(p_attribute13, FND_API.G_MISS_CHAR, NULL, p_attribute13),
102 DECODE(p_attribute14, FND_API.G_MISS_CHAR, NULL, p_attribute14),
103 DECODE(p_attribute15, FND_API.G_MISS_CHAR, NULL, p_attribute15),
104 sysdate,
105 fnd_global.user_id,
106 sysdate,
107 fnd_global.user_id,
108 fnd_global.login_id
109 ) RETURNING project_subteam_id INTO l_subteam_id;
110
111 x_new_subteam_id := l_subteam_id;
112
113 OPEN c1;
114 FETCH c1 INTO x_subteam_row_id;
115 IF (c1%NOTFOUND) THEN
116 CLOSE c1;
117 RAISE NO_DATA_FOUND;
118 END IF;
119 CLOSE c1;
120 --
121 --
122 EXCEPTION
123 WHEN OTHERS THEN -- catch the exceptions here
124 -- Set the current program unit name in the error stack
125 -- PA_Error_Utils.Set_Error_Stack('PA_PROJECT_SUBTEAMS_PKG.Insert_Row');
126 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
127 RAISE;
128 END Insert_Row;
129
130 PROCEDURE Update_Row
131 (
132
133 p_subteam_row_id IN ROWID :=NULL,
134
135 p_subteam_id IN pa_project_subteams.project_subteam_id%TYPE,
136
137 p_record_version_number IN NUMBER := NULL,
138
139 p_subteam_name IN pa_project_subteams.name%TYPE:= FND_API.g_miss_char,
140
141 p_object_type IN pa_project_subteams.object_type%TYPE := FND_API.g_miss_char,
142 p_object_id IN pa_project_subteams.object_id%TYPE := FND_API.g_miss_num,
143
144 p_description IN pa_project_subteams.description%TYPE := FND_API.g_miss_char,
145
146 p_attribute_category IN pa_project_subteams.attribute_category%TYPE := FND_API.g_miss_char ,
147
148 p_attribute1 IN pa_project_subteams.attribute1%TYPE := FND_API.g_miss_char ,
149
150 p_attribute2 IN pa_project_subteams.attribute2%TYPE := FND_API.g_miss_char ,
151
152 p_attribute3 IN pa_project_subteams.attribute3%TYPE := FND_API.g_miss_char ,
153
154 p_attribute4 IN pa_project_subteams.attribute4%TYPE := FND_API.g_miss_char ,
155
156 p_attribute5 IN pa_project_subteams.attribute5%TYPE := FND_API.g_miss_char ,
157
158 p_attribute6 IN pa_project_subteams.attribute6%TYPE := FND_API.g_miss_char ,
159
160 p_attribute7 IN pa_project_subteams.attribute7%TYPE := FND_API.g_miss_char ,
161
162 p_attribute8 IN pa_project_subteams.attribute8%TYPE := FND_API.g_miss_char ,
163
164 p_attribute9 IN pa_project_subteams.attribute9%TYPE := FND_API.g_miss_char ,
165
166 p_attribute10 IN pa_project_subteams.attribute10%TYPE := FND_API.g_miss_char ,
167
168 p_attribute11 IN pa_project_subteams.attribute11%TYPE := FND_API.g_miss_char ,
169
170 p_attribute12 IN pa_project_subteams.attribute12%TYPE := FND_API.g_miss_char ,
171
172 p_attribute13 IN pa_project_subteams.attribute13%TYPE := FND_API.g_miss_char ,
173
174 p_attribute14 IN pa_project_subteams.attribute14%TYPE := FND_API.g_miss_char ,
175
176 p_attribute15 IN pa_project_subteams.attribute15%TYPE := FND_API.g_miss_char ,
177
178 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
179 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
180 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
181 ) IS
182
183 l_row_id ROWID := p_subteam_row_id;
184 l_record_version_number NUMBER;
185
186 CURSOR get_row_id IS
187 SELECT rowid
188 FROM pa_project_subteams
189 WHERE project_subteam_id = p_subteam_id;
190
191
192 BEGIN
193
194 x_return_status := FND_API.G_RET_STS_SUCCESS;
195
196 /* ??????
197 -- Lock the row first
198 SELECT rowid INTO l_row_id
199 FROM pa_project_subteams
200 WHERE project_subteam_id = p_subteam_id
201 OR rowid = p_subteam_row_id
202 FOR UPDATE NOWAIT;
203 */
204
205
206 --get the ROWID for the row to be updated if
207 --p_subteam_row_id is not passed to the API.
208
209 IF l_row_id IS NULL THEN
210
211 OPEN get_row_id;
212
213 FETCH get_row_id INTO l_row_id;
214
215 CLOSE get_row_id;
216
217 END IF;
218
219 -- Increment the record version number by 1
220 l_record_version_number := p_record_version_number +1;
221
222 UPDATE pa_project_subteams
223 SET name = DECODE(p_subteam_name, FND_API.G_MISS_CHAR, name, p_subteam_name),
224
225 record_version_number = DECODE(p_record_version_number, NULL, record_version_number, l_record_version_number),
226
227 object_type = DECODE(p_object_type, FND_API.G_MISS_CHAR, object_type, p_object_type),
228 object_id = DECODE(p_object_id, FND_API.G_MISS_NUM, object_id, p_object_id),
229
230 description = DECODE(p_description, FND_API.G_MISS_CHAR, description, p_description),
231
232 attribute_category = DECODE(p_attribute_category, FND_API.G_MISS_CHAR, attribute_category, p_attribute_category),
233 attribute1 = DECODE(p_attribute1, FND_API.G_MISS_CHAR, attribute1, p_attribute1),
234 attribute2 = DECODE(p_attribute2, FND_API.G_MISS_CHAR, attribute2, p_attribute2),
235 attribute3 = DECODE(p_attribute3, FND_API.G_MISS_CHAR, attribute3, p_attribute3),
236 attribute4 = DECODE(p_attribute4, FND_API.G_MISS_CHAR, attribute4, p_attribute4),
237 attribute5 = DECODE(p_attribute5, FND_API.G_MISS_CHAR, attribute5, p_attribute5),
238 attribute6 = DECODE(p_attribute6, FND_API.G_MISS_CHAR, attribute6, p_attribute6),
239 attribute7 = DECODE(p_attribute7, FND_API.G_MISS_CHAR, attribute7, p_attribute7),
240 attribute8 = DECODE(p_attribute8, FND_API.G_MISS_CHAR, attribute8, p_attribute8),
241 attribute9 = DECODE(p_attribute9, FND_API.G_MISS_CHAR, attribute9, p_attribute9),
242 attribute10 = DECODE(p_attribute10, FND_API.G_MISS_CHAR, attribute10, p_attribute10),
243 attribute11 = DECODE(p_attribute11, FND_API.G_MISS_CHAR, attribute11, p_attribute11),
244 attribute12 = DECODE(p_attribute12, FND_API.G_MISS_CHAR, attribute12, p_attribute12),
245 attribute13 = DECODE(p_attribute13, FND_API.G_MISS_CHAR, attribute13, p_attribute13),
246 attribute14 = DECODE(p_attribute14, FND_API.G_MISS_CHAR, attribute14, p_attribute14),
247 attribute15 = DECODE(p_attribute15, FND_API.G_MISS_CHAR, attribute15, p_attribute15),
248 last_update_date = sysdate,
249 last_updated_by = fnd_global.user_id,
250 last_update_login = fnd_global.login_id
251 WHERE rowid = l_row_id
252 AND nvl(p_record_version_number, record_version_number) = record_version_number;
253 --
254
255 IF (SQL%NOTFOUND) THEN
256 PA_UTILS.Add_Message ( p_app_short_name => 'PA',p_msg_name => 'PA_XC_RECORD_CHANGED');
257 PA_PROJECT_SUBTEAMS_PUB.g_error_exists := FND_API.G_TRUE;
258 END IF;
259
260 --
261 EXCEPTION
262 WHEN OTHERS THEN -- catch the exceptins here
263 -- Set the current program unit name in the error stack
264 -- PA_Error_Utils.Set_Error_Stack('PA_PROJECT_SUBTEAMS_PKG.Update_Row');
265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
266 RAISE;
267 --
268 END Update_Row;
269
270 PROCEDURE Delete_Row
271 ( p_subteam_row_id IN ROWID
272 ,p_subteam_id IN pa_project_subteams.project_subteam_id%TYPE
273 ,p_record_version_number IN NUMBER := NULL
274 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
275 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
276 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
277 ) IS
278
279 l_row_id ROWID;
280
281 BEGIN
282
283 x_return_status := FND_API.G_RET_STS_SUCCESS;
284
285
286 /*
287 -- Lock the Subteam;
288 SELECT rowid INTO l_row_id
289 FROM pa_project_subteams
290 WHERE project_subteam_id = p_subteam_id
291 FOR UPDATE NOWAIT;
292 */
293
294 DELETE FROM pa_project_subteams
295 WHERE project_subteam_id = p_subteam_id
296 OR rowid = p_subteam_row_id;
297 --AND nvl(p_record_version_number, record_version_number) = record_version_number;
298
299 --
300 IF (SQL%NOTFOUND) THEN
301 PA_UTILS.Add_Message ( p_app_short_name => 'PA', p_msg_name => 'PA_XC_RECORD_CHANGED');
302 PA_PROJECT_SUBTEAMS_PUB.g_error_exists := FND_API.G_TRUE;
303 END IF;
304 --
305 --
306
307 EXCEPTION
308 WHEN OTHERS THEN
309 -- Set the current program unit name in the error stack
310 -- PA_Error_Utils.Set_Error_Stack('PA_PROJECT_SUBTEAMS_PKG.Delete_Row');
311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
312 RAISE;
313 END Delete_Row;
314
315 --
316 --
317 END PA_PROJECT_SUBTEAMS_pkg;