1 PACKAGE BODY CSM_PARTY_ASSIGNMENT_PKG AS
2 /* $Header: csmptagb.pls 120.4 2008/02/29 08:58:24 anaraman noship $ */
3
4 PROCEDURE INSERT_PARTY_ASSG (p_user_id IN NUMBER,
5 p_party_id IN NUMBER,
6 p_owner_id IN NUMBER,
7 p_party_site_id IN NUMBER DEFAULT NULL,
8 x_return_status OUT NOCOPY VARCHAR2,
9 x_error_message OUT NOCOPY VARCHAR2
10 )
11
12 IS
13
14 --variable declarations
15
16 l_chk_party NUMBER := NULL;
17 l_chk_party_site NUMBER := NULL;
18 l_cnt_party NUMBER := 0;
19 l_cnt_party_site NUMBER := 0;
20 l_cnt_upd_site NUMBER := 0;
21 l_cnt_upd_party NUMBER := 0;
22 l_sqlerrno VARCHAR2(20);
23 l_sqlerrmsg VARCHAR2(2000);
24 l_party_site_id NUMBER;
25 l_deleted_flag VARCHAR2(1) := NULL;
26
27 BEGIN
28
29 CSM_UTIL_PKG.LOG('Entering CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG Package ', 'CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
30
31 l_party_site_id := NVL(p_party_site_id,-1);
32
33 IF l_party_site_id = -1
34
35 THEN
36
37 SELECT COUNT(1)
38 INTO l_chk_party
39 FROM hz_parties hp
40 WHERE hp.party_id = p_party_id;
41
42 ELSIF l_party_site_id <> -1
43
44 THEN
45
46 SELECT COUNT(1)
47 INTO l_chk_party_site
48 FROM hz_party_sites hps
49 WHERE hps.party_id = p_party_id
50 AND hps.party_site_id = p_party_site_id;
51
52 END IF;
53
54 IF l_chk_party = 0
55
56 THEN
57
58 x_error_message := 'The party records does not exists in the HZ PARTIES base table for the party - '||p_party_id;
59
60 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
61
62 ELSIF l_chk_party_site = 0
63
64 THEN
65
66 x_error_message := 'The party site records does not exists in the HZ PARTY SITES base table for the party - '||p_party_id|| ' and site -'||p_party_site_id;
67
68 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
69
70 END IF;
71
72 SELECT COUNT(1)
73 INTO l_cnt_party
74 FROM CSM_PARTY_ASSIGNMENT
75 WHERE USER_ID = p_user_id
76 AND PARTY_ID = p_party_id
77 AND PARTY_SITE_ID = l_party_site_id;
78
79 IF l_cnt_party = 0 AND l_party_site_id = -1 THEN
80
81 CSM_UTIL_PKG.LOG('Inserting the record with party id only ', 'CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
82
83 INSERT INTO CSM_PARTY_ASSIGNMENT( USER_ID, PARTY_ID,
84 OWNER_ID, PARTY_SITE_ID,
85 DELETED_FLAG, CREATED_BY,
86 CREATION_DATE, LAST_UPDATED_BY,
87 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
88 )
89 VALUES
90 ( p_user_id, p_party_id,
91 p_owner_id, l_party_site_id,
92 'N', 1,
93 SYSDATE, 1,
94 SYSDATE, 1
95 );
96
97 END IF;
98
99 IF l_cnt_party = 0 AND l_party_site_id <> -1 THEN
100
101 SELECT COUNT(1)
102 INTO l_cnt_party_site
103 FROM CSM_PARTY_ASSIGNMENT
104 WHERE USER_ID = p_user_id
105 AND PARTY_ID = p_party_id
106 AND PARTY_SITE_ID = -2;
107
108 IF l_cnt_party_site = 0 THEN
109
110 /* if the user first assign party alone to
111 a user and then assign a record with party and site then
112 we have to delete the record which hold the party alone */
113
114 SELECT COUNT(1)
115 INTO l_cnt_upd_party
116 FROM CSM_PARTY_ASSIGNMENT
117 WHERE USER_ID = p_user_id
121 IF l_cnt_upd_party <> 0 THEN
118 AND PARTY_ID = p_party_id
119 AND PARTY_SITE_ID = -1;
120
122
123 CSM_UTIL_PKG.LOG('Updating the deleted flag to Y for party record ', 'CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
124
125 UPDATE CSM_PARTY_ASSIGNMENT
126 SET DELETED_FLAG = 'Y'
127 WHERE USER_ID = p_user_id
128 AND PARTY_ID = p_party_id
129 AND PARTY_SITE_ID = -1;
130
131 END IF;
132
133 /* if the party is inserted along with the party_site
134 another record is inserted with party_site_id as -2
135 for deleting the access table purpose*/
136
137 CSM_UTIL_PKG.LOG('Inserting the record with party id and party site id along with another record with - 2 as party site value', 'CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
138
139 INSERT INTO CSM_PARTY_ASSIGNMENT( USER_ID, PARTY_ID,
140 OWNER_ID, PARTY_SITE_ID,
141 DELETED_FLAG, CREATED_BY,
142 CREATION_DATE, LAST_UPDATED_BY,
143 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
144 )
145 VALUES
146 ( p_user_id, p_party_id,
147 p_owner_id, -2,
148 'N', 1,
149 SYSDATE, 1,
150 SYSDATE, 1
151 );
152
153 INSERT INTO CSM_PARTY_ASSIGNMENT( USER_ID, PARTY_ID,
154 OWNER_ID, PARTY_SITE_ID,
155 DELETED_FLAG, CREATED_BY,
156 CREATION_DATE, LAST_UPDATED_BY,
157 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
158 )
159 VALUES
160 ( p_user_id, p_party_id,
161 p_owner_id, l_party_site_id,
162 'N', 1,
163 SYSDATE, 1,
164 SYSDATE, 1
165 );
166 END IF;
167
168 IF l_cnt_party_site <> 0 THEN
169
170 CSM_UTIL_PKG.LOG('Inserting the record with party id and party site id only', 'CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
171
172 INSERT INTO CSM_PARTY_ASSIGNMENT( USER_ID, PARTY_ID,
173 OWNER_ID, PARTY_SITE_ID,
174 DELETED_FLAG, CREATED_BY,
175 CREATION_DATE, LAST_UPDATED_BY,
176 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
177 )
178 VALUES
179 ( p_user_id, p_party_id,
180 p_owner_id, l_party_site_id,
181 'N', 1,
182 SYSDATE, 1,
183 SYSDATE, 1
184 );
185
186 END IF;
187
188 END IF;
189
190 /* if a party is removed by mistake and again added
191 we are updating the deleted flag to N*/
192
193 IF l_cnt_party <> 0 THEN
194
195 SELECT deleted_flag
196 INTO l_deleted_flag
197 FROM CSM_PARTY_ASSIGNMENT
198 WHERE USER_ID = p_user_id
199 AND PARTY_ID = p_party_id
200 AND PARTY_SITE_ID = l_party_site_id;
201
202 IF l_deleted_flag = 'Y' THEN
203
204 CSM_UTIL_PKG.LOG('Updating the deleted flag to N for record which was deleted by mistake', 'CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
205
206 UPDATE CSM_PARTY_ASSIGNMENT
207 SET DELETED_FLAG = 'N'
208 WHERE USER_ID = p_user_id
209 AND PARTY_ID = p_party_id
210 AND PARTY_SITE_ID = l_party_site_id;
211
212 END IF;
213
214 END IF;
215
216 /* if the deleted flag is set to Y for a party
217 and then a new record with party and party site id is inserted
218 then we are updating the deleted flag to N*/
219
220 SELECT COUNT(1)
221 INTO l_cnt_upd_site
222 FROM CSM_PARTY_ASSIGNMENT
223 WHERE USER_ID = p_user_id
224 AND PARTY_ID = p_party_id
225 AND PARTY_SITE_ID not in (-2,-1)
226 AND DELETED_FLAG = 'N';
227
228 IF l_cnt_upd_site <> 0 THEN
229
230 CSM_UTIL_PKG.LOG('Updating the deleted flag to N for record which hold the -2 value if any record with part site is inserted', 'CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
231
232 UPDATE CSM_PARTY_ASSIGNMENT
233 SET DELETED_FLAG = 'N'
234 WHERE USER_ID = p_user_id
235 AND PARTY_ID = p_party_id
236 AND OWNER_ID = p_owner_id
237 AND PARTY_SITE_ID = -2;
238
239 END IF;
240
241 x_return_status := FND_API.G_RET_STS_SUCCESS;
242 x_error_message := 'PARTY_ID successfully Inserted ';
243 CSM_UTIL_PKG.LOG('Leaving CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG Package ', 'CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
244
245 EXCEPTION
246
247 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
248
249 THEN
250
251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
252 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
253
254 WHEN OTHERS THEN
255 l_sqlerrno := TO_CHAR(SQLCODE);
256 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
258 x_error_message := 'Exception in CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG Procedure :'||'while inserting the party -'||p_party_id|| 'for the user -'||p_user_id || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
259 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_PARTY_ASSIGNMENT_PKG.INSERT_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
260
261 END INSERT_PARTY_ASSG;
262
263 PROCEDURE DELETE_PARTY_ASSG (p_user_id IN NUMBER,
264 p_party_id IN NUMBER,
265 p_owner_id IN NUMBER,
266 p_party_site_id IN NUMBER DEFAULT NULL,
267 x_return_status OUT NOCOPY VARCHAR2,
268 x_error_message OUT NOCOPY VARCHAR2
269 )
270
271 IS
272
273 --variable declarations
274
275 l_cnt_upd NUMBER := 0;
276 l_cnt_upd_site NUMBER := 0;
277 l_sqlerrno VARCHAR2(20);
278 l_sqlerrmsg VARCHAR2(2000);
279 l_error_message VARCHAR2(2000);
280 l_party_site_id NUMBER;
281
282 BEGIN
283
284 CSM_UTIL_PKG.LOG('Entering CSM_PARTY_ASSIGNMENT_PKG.DELETE_PARTY_ASSG Package ', 'CSM_PARTY_ASSIGNMENT_PKG.DELETE_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
285
286 l_party_site_id := NVL(p_party_site_id,-1);
287
288 SELECT COUNT(1)
289 INTO l_cnt_upd
290 FROM CSM_PARTY_ASSIGNMENT
291 WHERE USER_ID = p_user_id
292 AND PARTY_ID = p_party_id
293 AND PARTY_SITE_ID = l_party_site_id;
294
295 IF l_cnt_upd <> 0 THEN
296
297 CSM_UTIL_PKG.LOG('Updating the deleted flag to Y for Deleted records', 'CSM_PARTY_ASSIGNMENT_PKG.DELETE_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
298
299 UPDATE CSM_PARTY_ASSIGNMENT
300 SET DELETED_FLAG = 'Y'
301 WHERE USER_ID = p_user_id
302 AND PARTY_ID = p_party_id
303 AND OWNER_ID = p_owner_id
304 AND PARTY_SITE_ID = l_party_site_id;
305
306 l_error_message := 'PARTY_ID successfully Deleted ';
307
308 ELSE
309
310 CSM_UTIL_PKG.LOG('No records found for Deleting', 'CSM_PARTY_ASSIGNMENT_PKG.DELETE_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
311
312 l_error_message := 'No records found for Deleting for user - '||p_user_id ||'and party -'||p_party_id||' and party site -'||l_party_site_id;
313
314 END IF;
315
316 /* if all the party_sites are deleted
317 then record which holds the party_id (i.e -2)
318 will also be deleted*/
319
320 SELECT COUNT(1)
321 INTO l_cnt_upd_site
322 FROM CSM_PARTY_ASSIGNMENT
323 WHERE USER_ID = p_user_id
324 AND PARTY_ID = p_party_id
325 AND PARTY_SITE_ID not in (-2,-1)
326 AND DELETED_FLAG = 'N';
327
328 IF l_cnt_upd_site = 0 THEN
329
330 CSM_UTIL_PKG.LOG('Updating the deleted flag to Y for the record which holds the -2 value', 'CSM_PARTY_ASSIGNMENT_PKG.DELETE_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
331
332 UPDATE CSM_PARTY_ASSIGNMENT
333 SET DELETED_FLAG = 'Y'
334 WHERE USER_ID = p_user_id
335 AND PARTY_ID = p_party_id
336 AND OWNER_ID = p_owner_id
337 AND PARTY_SITE_ID = -2;
338
339 END IF;
340
341 x_return_status := FND_API.G_RET_STS_SUCCESS;
342 x_error_message := l_error_message;
343 CSM_UTIL_PKG.LOG('Leaving CSM_PARTY_ASSIGNMENT_PKG.DELETE_PARTY_ASSG Package ', 'CSM_PARTY_ASSIGNMENT_PKG.DELETE_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
344
345 EXCEPTION
346 WHEN others THEN
347 l_sqlerrno := TO_CHAR(SQLCODE);
348 l_sqlerrmsg := SUBSTR(SQLERRM, 1,2000);
349 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
350 x_error_message := 'Exception in CSM_PARTY_ASSIGNMENT_PKG.DELETE_PARTY_ASSG Procedure :'||'while deleting the party -'||p_party_id|| 'for the user -'||p_user_id || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
351 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_PARTY_ASSIGNMENT_PKG.DELETE_PARTY_ASSG',FND_LOG.LEVEL_EXCEPTION);
352
353 END DELETE_PARTY_ASSG;
354
355 END CSM_PARTY_ASSIGNMENT_PKG;
[Home] [Help]