[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_SUBTEAM_PARTIES_PKG
Source
1 PACKAGE BODY pa_project_subteam_parties_pkg AS
2 --$Header: PARTSPHB.pls 120.1 2005/08/19 17:01:37 mwasowic noship $
3
4 PROCEDURE Insert_Row
5 (
6 p_project_subteam_id IN pa_project_subteams.Project_subteam_id%TYPE := FND_API.g_miss_num,
7
8 p_object_type IN pa_project_subteam_parties.object_type%TYPE := FND_API.g_miss_char,
9
10 p_object_id IN pa_project_subteam_parties.object_id%TYPE := fnd_api.g_miss_num,
11
12 p_primary_subteam_flag IN pa_project_subteam_parties.primary_subteam_flag%TYPE := 'Y',
13
14 x_project_subteam_party_row_id OUT NOCOPY ROWID, --File.Sql.39 bug 4440895
15
16 x_project_subteam_party_id OUT NOCOPY pa_project_subteam_parties.project_subteam_party_id%TYPE, --File.Sql.39 bug 4440895
17
18 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
19
20 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
21
22 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
23 ) IS
24
25 l_subteam_party_id NUMBER;
26 l_record_version_number NUMBER := 1;
27 l_primary_subteam_flag pa_project_subteam_parties.primary_subteam_flag%TYPE;
28
29 CURSOR c1 IS
30 SELECT rowid
31 FROM pa_project_subteam_parties
32 WHERE project_subteam_party_id = l_subteam_party_id;
33
34 BEGIN
35
36 x_return_status := FND_API.G_RET_STS_SUCCESS;
37
38 --Fetch the next sequence number for subteam
39 --SELECT pa_project_subteams_s.NEXTVAL
40 --INTO l_subteam_party_id
41 --FROM dual;
42
43 if p_primary_subteam_flag is NULL or p_primary_subteam_flag = FND_API.g_miss_char then
44 l_primary_subteam_flag := 'Y';
45 else
46 l_primary_subteam_flag := p_primary_subteam_flag;
47 end if;
48
49 INSERT INTO pa_project_subteam_parties
50 (project_subteam_party_id,
51 project_subteam_id,
52 record_version_number,
53 object_type,
54 object_id,
55 primary_subteam_flag,
56 creation_date,
57 created_by,
58 last_update_date,
59 last_updated_by,
60 last_update_login )
61 VALUES
62 (
63 --l_subteam_party_id,
64 pa_project_subteams_s.NEXTVAL,
65 DECODE(p_project_subteam_id, FND_API.G_MISS_NUM, NULL, p_project_subteam_id),
66 1,
67 DECODE(p_object_type, FND_API.G_MISS_CHAR, NULL, p_object_type),
68 DECODE(p_object_id, FND_API.G_MISS_NUM, NULL, p_object_id),
69 DECODE(l_primary_subteam_flag, FND_API.G_MISS_CHAR, NULL, l_primary_subteam_flag),
70 sysdate,
71 fnd_global.user_id,
72 sysdate,
73 fnd_global.user_id,
74 fnd_global.login_id
75 ) RETURNING project_subteam_party_id INTO l_subteam_party_id;
76
77
78 x_project_subteam_party_id := l_subteam_party_id;
79
80 OPEN c1;
81 FETCH c1 INTO x_project_subteam_party_row_id;
82 IF (c1%NOTFOUND) THEN
83 CLOSE c1;
84 RAISE NO_DATA_FOUND;
85 END IF;
86 CLOSE c1;
87 --
88 --
89 EXCEPTION
90 WHEN OTHERS THEN -- catch the exceptions here
91 -- Set the current program unit name in the error stack
92 -- PA_Error_Utils.Set_Error_Stack('PA_PROJECT_SUBTEAMS_PKG.Insert_Row');
93 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
94 RAISE;
95 END Insert_Row;
96
97 PROCEDURE Update_Row
98 (
99 p_project_subteam_party_row_id IN ROWID :=NULL,
100
101 p_project_subteam_party_id IN pa_project_subteam_parties.project_subteam_party_id%TYPE,
102
103 p_project_subteam_id IN pa_project_subteams.project_subteam_id%TYPE,
104
105 -- p_object_type IN varchar2,
106
107 -- p_object_id IN NUMBER := fnd_api.g_miss_num,
108
109 p_primary_subteam_flag IN pa_project_subteam_parties.primary_subteam_flag%TYPE := 'Y',
110
111 p_record_version_number IN NUMBER := NULL,
112
113 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
114
115 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
116
117 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
118 ) IS
119
120 l_primary_subteam_flag pa_project_subteam_parties.primary_subteam_flag%TYPE;
121 l_row_id ROWID := p_project_subteam_party_row_id;
122 l_record_version_number NUMBER;
123
124 CURSOR get_row_id IS
125 SELECT rowid
126 FROM pa_project_subteam_parties
127 WHERE project_subteam_party_id = p_project_subteam_party_id;
128
129
130 BEGIN
131
132 x_return_status := FND_API.G_RET_STS_SUCCESS;
133
134 if p_primary_subteam_flag is NULL or p_primary_subteam_flag = FND_API.g_miss_char then
135 l_primary_subteam_flag := 'Y';
136 else
137 l_primary_subteam_flag := p_primary_subteam_flag;
138 end if;
139
140
141 --get the ROWID for the row to be updated if
142 --p_subteam_row_id is not passed to the API.
143
144 IF l_row_id IS NULL THEN
145
146 OPEN get_row_id;
147
148 FETCH get_row_id INTO l_row_id;
149
150 CLOSE get_row_id;
151
152 END IF;
153
154 -- Increment the record version number by 1
155 IF p_record_version_number IS NOT NULL then
156 l_record_version_number := p_record_version_number +1;
157 END IF;
158
159 UPDATE pa_project_subteam_parties
160 SET
161
162 project_subteam_id = Decode (p_project_subteam_id,fnd_api.g_miss_num,project_subteam_id, p_project_subteam_id),
163
164 record_version_number = DECODE(p_record_version_number, NULL, record_version_number + 1, l_record_version_number),
165
166 primary_subteam_flag = Decode(l_primary_subteam_flag, fnd_api.g_miss_char, primary_subteam_flag, l_primary_subteam_flag),
167
168 last_update_date = sysdate,
169
170 last_updated_by = fnd_global.user_id,
171
172 last_update_login = fnd_global.login_id
173
174 WHERE rowid = l_row_id
175 AND nvl(p_record_version_number, record_version_number) = record_version_number;
176
177 IF (SQL%NOTFOUND) THEN
178 PA_UTILS.Add_Message ( p_app_short_name => 'PA',p_msg_name => 'PA_XC_RECORD_CHANGED');
179 PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists := FND_API.G_TRUE;
180 END IF;
181
182 --
183 EXCEPTION
184 WHEN OTHERS THEN -- catch the exceptins here
185 -- Set the current program unit name in the error stack
186 -- PA_Error_Utils.Set_Error_Stack('PA_PROJECT_SUBTEAMS_PKG.Update_Row');
187 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
188 RAISE;
189 END Update_Row;
190
191 PROCEDURE Delete_Row
192 (
193 p_project_subteam_party_row_id IN ROWID
194 ,p_project_subteam_party_id IN pa_project_subteam_parties.project_subteam_party_id%TYPE
195 ,p_record_version_number IN NUMBER := NULL
196 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
197 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
198 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
199 ) IS
200
201 l_row_id ROWID;
202
203 BEGIN
204
205 x_return_status := FND_API.G_RET_STS_SUCCESS;
206
207 DELETE FROM pa_project_subteam_parties
208 WHERE project_subteam_party_id = p_project_subteam_party_id
209 OR rowid = p_project_subteam_party_row_id
210 AND nvl(p_record_version_number, record_version_number) = record_version_number;
211
212 --
213 IF (SQL%NOTFOUND) THEN
214 PA_UTILS.Add_Message ( p_app_short_name => 'PA', p_msg_name => 'PA_XC_RECORD_CHANGED');
215 PA_PROJECT_SUBTEAM_PARTIES_PVT.g_error_exists := FND_API.G_TRUE;
216 x_return_status := FND_API.G_RET_STS_ERROR;
217 END IF;
218 --
219 --
220
221 EXCEPTION
222 WHEN OTHERS THEN
223 -- Set the current program unit name in the error stack
224 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
225 RAISE;
226 END Delete_Row;
227
228 --
229 --
230 END PA_PROJECT_SUBTEAM_PARTIES_PKG;