DBA Data[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;