[Home] [Help]
PACKAGE BODY: APPS.POS_HZ_RELATIONSHIPS_PKG
Source
1 PACKAGE BODY POS_HZ_RELATIONSHIPS_PKG AS
2 /*$Header: POSHZPRB.pls 120.1 2005/10/27 12:37:16 bitang noship $ */
3
4 procedure pos_create_relationship(
5 p_subject_id IN NUMBER,
6 p_object_id IN NUMBER,
7 p_relationship_type IN VARCHAR2,
8 p_relationship_code IN VARCHAR2,
9
10 x_party_relationship_id OUT NOCOPY NUMBER,
11 x_return_status OUT NOCOPY VARCHAR2,
12 x_exception_msg OUT NOCOPY VARCHAR2)
13 IS
14 l_object_type varchar2(200);
15 l_subject_type varchar2(200);
16 BEGIN
17 x_exception_msg := 'pos_create_relationship():select object_type';
18 select party_type
19 into l_object_type
20 from hz_parties
21 where party_id = p_object_id;
22
23 x_exception_msg := 'select subject_type';
24 select party_type
25 into l_subject_type
26 from hz_parties
27 where party_id = p_subject_id;
28
29 x_exception_msg := 'Calling the pos_hz_create_relationship';
30 pos_hz_create_relationship( p_subject_id => p_subject_id
31 , p_object_id => p_object_id
32 , p_relationship_type => p_relationship_type
33 , p_relationship_code => p_relationship_code
34 , p_party_object_type => l_object_type
35 , p_party_subject_type => l_subject_type
36 , p_subject_table_name => 'HZ_PARTIES'
37 , p_object_table_name => 'HZ_PARTIES'
38 , p_relationship_status => 'A'
39 , p_relationship_start_date => NULL
40 , p_relationship_end_date => NULL
41 , x_party_relationship_id => x_party_relationship_id
42 , x_return_status => x_return_status
43 , x_exception_msg => x_exception_msg
44 );
45 IF x_return_status <> 'S' THEN
46 raise_application_error(-20001, 'pos_create_relationship():' || x_exception_msg || p_relationship_type || p_relationship_code || l_object_type || l_subject_type,true);
47 END IF;
48
49 EXCEPTION
50 WHEN OTHERS THEN
51 /* Check for the exceptions */
52 raise_application_error(-20001, 'pos_create_relationship():' || x_exception_msg,true);
53 END pos_create_relationship;
54
55 /* You dont have to call this method directly. Helper procedures will be
56 * created.
57 */
58 procedure pos_hz_create_relationship(
59 p_subject_id IN NUMBER,
60 p_object_id IN NUMBER,
61 p_relationship_type IN VARCHAR2,
62 p_relationship_code IN VARCHAR2,
63 p_party_object_type IN VARCHAR2,
64 p_party_subject_type IN VARCHAR2,
65 p_subject_table_name IN VARCHAR2,
66 p_object_table_name IN VARCHAR2,
67 p_relationship_status IN VARCHAR2 := NULL, -- can be null
68 p_relationship_start_date IN DATE := NULL, -- can be null
69 p_relationship_end_date IN DATE := NULL, -- can be null
70
71 x_party_relationship_id OUT NOCOPY NUMBER,
72 x_return_status OUT NOCOPY VARCHAR2,
73 x_exception_msg OUT NOCOPY VARCHAR2
74 )
75 IS
76 l_rel_rec hz_relationship_v2pub.relationship_rec_type;
77
78 l_msg_count NUMBER;
79 l_msg_data VARCHAR2(240);
80 l_party_relationship_id NUMBER;
81 l_party_id NUMBER;
82 l_party_number VARCHAR2(30);
83 l_return_status VARCHAR2(1);
84
85 BEGIN
86
87 l_rel_rec.subject_id := p_subject_id;
88 l_rel_rec.object_id := p_object_id;
89
90 l_rel_rec.subject_table_name := p_subject_table_name;
91 l_rel_rec.object_table_name := p_object_table_name;
92
93 l_rel_rec.relationship_code := p_relationship_code;
94 l_rel_rec.relationship_type := p_relationship_type;
95
96 l_rel_rec.subject_type := p_party_subject_type;
97 l_rel_rec.object_type := p_party_object_type;
98
99 l_rel_rec.created_by_module := 'POS_SUPPLIER_MGMT';
100 l_rel_rec.application_id := 177;
101
102 if p_object_id IS NULL then
103 x_exception_msg := 'POS_HZ_RELATIONSHIPS_PKG.pos_create_relationship_all_args(): Failed to object_id cannot be null';
104 x_return_status := 'E'; /* JP changed it to 'E' from 'S' */
105 return;
106 end if;
107
108 IF p_subject_id IS NULL THEN
109 x_exception_msg := 'POS_HZ_RELATIONSHIPS_PKG.pos_create_relationship_all_args(): Failed to subject_id cannot be null';
110 x_return_status := 'E';
111 return;
112 END IF;
113
114 IF p_relationship_start_date IS NOT NULL THEN
115 l_rel_rec.start_date:= p_relationship_start_date;
116 ELSE
117 l_rel_rec.start_date:= SYSDATE;
118 END IF;
119
120 IF p_relationship_status IS NULL THEN
121 l_rel_rec.status := 'A';
122 ELSE
123 l_rel_rec.status := p_relationship_status;
124 END IF;
125
126 IF p_relationship_end_date IS NOT NULL THEN
127 l_rel_rec.end_date := p_relationship_end_date;
128 END IF;
129
130 hz_relationship_v2pub.create_relationship(
131 --p_api_version => 1.0
132 p_init_msg_list => fnd_api.g_true
133 ,p_relationship_rec => l_rel_rec
134 ,x_relationship_id => l_party_relationship_id
135 ,x_party_id => l_party_id
136 ,x_party_number => l_party_number
137 --,p_commit => fnd_api.g_false
138 ,x_return_status => l_return_status
139 ,x_msg_count => l_msg_count
140 ,x_msg_data => l_msg_data
141 --,p_validation_level => FND_API.G_VALID_LEVEL_FULL
142 );
143
144 x_party_relationship_id := l_party_relationship_id;
145 x_return_status := l_return_status;
146 x_exception_msg := l_msg_data;
147
148 IF x_return_status <> 'S' THEN
149 -- There has been an error
150 BEGIN
151 --raise;
152 raise_application_error(-20001, 'POS_HZ_RELATIONSHIPS_PKG:pos_create_relationship(): Create relationship failed :' || x_exception_msg || p_relationship_type || p_relationship_code || p_party_subject_type || p_party_object_type, true);
153 END;
154 END IF;
155
156 EXCEPTION
157 WHEN OTHERS THEN
158 raise_application_error(-20001, 'POS_HZ_RELATIONSHIPS_PKG:pos_create_relationship(): Create relationship failed :' || x_exception_msg || p_relationship_type || p_relationship_code || p_party_subject_type || p_party_object_type, true);
159 END pos_hz_create_relationship;
160
161 /*
162 procedure pos_outdate_relationship_args ( p_subject_id IN NUMBER,
163 p_object_id IN NUMBER,
164 p_relationship_type IN VARCHAR2,
165 p_relationship_code IN VARCHAR2,
166 p_party_object_type IN VARCHAR2,
167 p_party_subject_type IN VARCHAR2,
168 p_subject_table_name IN VARCHAR2,
169 p_object_table_name IN VARCHAR2,
170 x_status OUT NOCOPY number,
171 x_exceptions_msg OUT NOCOPY VARCHAR2
172 )
173 IS
174 l_count NUMBER;
175 BEGIN
176 x_status = 'S';
177 select count(*)
178 into l_count
179 from hz_relationships
180 where object_table_name = p_object_table_name
181 and subject_table_name = p_subject_table_name
182 and object_type = p_party_object_type
183 and subject_type = p_party_subject_type
184 and relationship_code = p_relationship_code
185 and relationship_type = p_relationship_type
186 and object_id = p_object_id
187 and subject_id = p_subject_id;
188
189 IF l_count = 0 THEN
190 x_status = 'E'; -- Failure, because the relationship does not exist
191 x_exception_msg = 'pos_hz_relationships_pkg.pos_outdate_relationships_args():The requested relationship does not exist';
192 return;
193 END IF;
194
195 x_exceptions_msg := 'Ending the relationship ';
196
197 update hz_relationships
198 set end_date := SYSDATE, status := 'A'
199 where object_table_name = p_object_table_name
200 and subject_table_name = p_subject_table_name
201 and object_type = p_party_object_type
202 and subject_type = p_party_subject_type
203 and relationship_code = p_relationship_code
204 and relationship_type = p_relationship_type
205 and object_id = p_object_id
206 and subject_id = p_subject_id;
207
208 EXCEPTION
209 WHEN OTHERS THEN
210 POS_UTIL_PKG.raise_error('POS_HZ_RELATIONSHIPS_PKG:pos_outdate_relationship_args():' || x_exception_msg);
211 END;
212
213 END pos_outdate_relationship_args;
214 */
215
216 /* Donot call this directly instead use pos_outdate_relationship() when
217 possible
218 */
219 procedure pos_hz_update_relationship(p_subject_id IN NUMBER,
220 p_object_id IN NUMBER,
221 p_relationship_type IN VARCHAR2,
222 p_relationship_code IN VARCHAR2,
223 p_party_object_type IN VARCHAR2,
224 p_party_subject_type IN VARCHAR2,
225 p_subject_table_name IN VARCHAR2,
226 p_object_table_name IN VARCHAR2,
227 -- p_relationship_status IN VARCHAR2, -- should not be updated
228 p_relationship_start_date IN DATE, -- can be null
229 p_relationship_end_date IN DATE, -- can be null
230
231 p_relationship_id IN NUMBER,
232 p_object_version_number in number,
233
234 p_rel_last_update_date IN OUT NOCOPY DATE,
235 p_party_last_update_date IN OUT NOCOPY DATE,
236 x_return_status OUT NOCOPY VARCHAR2,
237 x_exception_msg OUT NOCOPY VARCHAR2)
238 IS
239 l_rel_rec hz_relationship_v2pub.relationship_rec_type;
240 l_msg_count NUMBER;
241 l_msg_data VARCHAR2(240);
242 l_party_id NUMBER;
243 l_party_number VARCHAR2(30);
244 l_return_status VARCHAR2(1);
245 l_rel_last_update_date date;
246 l_party_last_update_date date;
247 l_proc_name varchar2(50);
248 l_obj_ver number;
249 l_party_obj_ver number;
250 BEGIN
251 l_proc_name := 'pos_hz_update_relationship';
252 l_obj_ver := p_object_version_number;
253 l_rel_rec.subject_id := p_subject_id;
254 l_rel_rec.object_id := p_object_id;
255
256 l_rel_rec.subject_table_name := p_subject_table_name;
257 l_rel_rec.object_table_name := p_object_table_name;
258
259 l_rel_rec.relationship_code := p_relationship_code;
260 l_rel_rec.relationship_type := p_relationship_type;
261 l_rel_rec.subject_type := p_party_subject_type;
262 l_rel_rec.object_type := p_party_object_type;
263
264 l_rel_rec.created_by_module := 'POS_SUPPLIER_MGMT';
265 l_rel_rec.application_id := 177;
266
267 if p_object_id IS NULL then
268 x_return_status := 'E';
269 x_exception_msg := l_proc_name || 'Object id cannot be null';
270 return;
271 end if;
272
273 if p_subject_id IS NULL then
274 x_return_status := 'E';
275 x_exception_msg := l_proc_name || 'subject id cannot be null';
276 return;
277 end if;
278
279 IF p_relationship_start_date IS NOT NULL THEN
280 l_rel_rec.start_date:= p_relationship_start_date;
281 END IF;
282
283 IF p_relationship_end_date IS NOT NULL THEN
284 l_rel_rec.end_date := p_relationship_end_date;
285 END IF;
286
287 IF p_relationship_id IS NOT NULL THEN
288 l_rel_rec.relationship_id := p_relationship_id;
289 ELSE
290 x_return_status := 'E';
291 x_exception_msg := l_proc_name || ' relationship_id cannot be null';
292 return;
293 END IF;
294
295 /* Do not update the l_rel_rec.status variable. Because this value
296 is slightly confusing is typically handled by TCA.
297 */
298
299 hz_relationship_v2pub.update_relationship(
300 --p_api_version => 1.0
301 p_init_msg_list => FND_API.G_TRUE
302 --,p_commit => FND_API.G_FALSE
303 ,p_relationship_rec => l_rel_rec
304 ,p_object_version_number => l_obj_ver
305 ,p_party_object_version_number => l_party_obj_ver
306 --,p_rel_last_update_date => l_rel_last_update_date
307 --,p_party_last_update_date => l_party_last_update_date
308 ,x_return_status => l_return_status
309 ,x_msg_count => l_msg_count
310 ,x_msg_data => l_msg_data
311
312 --,x_relationship_id => l_party_relationship_id
313 --,x_party_id => l_party_id
314 --,x_party_number => l_party_number
315 );
316
317 x_return_status := l_return_status;
318 x_exception_msg := l_msg_data;
319
320 IF x_return_status <> 'S' THEN
321 -- There has been an error
322 BEGIN
323 raise_application_error(-20001,'POS_HZ_RELATIONSHIPS_PKG:pos_outdate_relationship(): Update relationship failed :' || x_exception_msg, true);
324 END;
325 END IF;
326
327 EXCEPTION
328 WHEN OTHERS THEN
329 raise_application_error(-20001,'POS_HZ_RELATIONSHIPS_PKG:pos_outdate_relationship(): Update relationship failed :' || x_exception_msg,true);
330 --x_return_status := 'U';
331 --x_exception_msg := 'DEBUG: in pos_hz_relationships_pkg.update_or_insert';
332 END pos_hz_update_relationship;
333
334 procedure pos_outdate_relationship(
335 p_subject_id IN NUMBER,
336 p_object_id IN NUMBER,
337 p_relationship_type IN VARCHAR2,
338 p_relationship_code IN VARCHAR2,
339 x_return_status OUT NOCOPY VARCHAR2,
340 x_exception_msg OUT NOCOPY VARCHAR2)
341 IS
342 l_object_type varchar2(200);
343 l_subject_type varchar2(200);
344 l_party_date date;
345 l_rel_date date;
346 l_relationship_id number ;
347 l_obj_ver number;
348 BEGIN
349 x_exception_msg := 'pos_outdate_relationship():select object_type';
350 select party_type
351 into l_object_type
352 from hz_parties
353 where party_id = p_object_id;
354
355 x_exception_msg := 'select subject_type';
356 select party_type
357 into l_subject_type
358 from hz_parties
359 where party_id = p_subject_id;
360
361 x_exception_msg := 'selecting distinct relationship id';
362
363 select distinct relationship_id, object_version_number
364 into l_relationship_id, l_obj_ver
365 from hz_relationships
366 where start_date <= sysdate
367 and end_date >= sysdate
368 and status = 'A'
369 and object_table_name = 'HZ_PARTIES'
370 and subject_table_name = 'HZ_PARTIES'
371 and object_type = l_object_type
372 and subject_type = l_subject_type
373 and relationship_code = p_relationship_code
374 and relationship_type = p_relationship_type
375 and object_id = p_object_id
376 and subject_id = p_subject_id;
377
378 x_exception_msg := 'Calling the pos_hz_update_relationship';
379 pos_hz_update_relationship( p_subject_id => p_subject_id
380 , p_object_id => p_object_id
381 , p_relationship_type => p_relationship_type
382 , p_relationship_code => p_relationship_code
383 , p_party_object_type => l_object_type
384 , p_party_subject_type => l_subject_type
385 , p_subject_table_name => 'HZ_PARTIES'
386 , p_object_table_name => 'HZ_PARTIES'
387 , p_relationship_end_date => SYSDATE
388 , p_relationship_start_date => NULL
389 , p_relationship_id => l_relationship_id
390 , p_object_version_number => l_obj_ver
391 , p_rel_last_update_date => l_rel_date
392 , p_party_last_update_date => l_party_date
393 , x_return_status => x_return_status
394 , x_exception_msg => x_exception_msg
395 );
396 IF x_return_status <> 'S' THEN
397 raise_application_error(-20001, 'pos_outdate_relationship():' || x_exception_msg, true);
398 END IF;
399 EXCEPTION
400 WHEN OTHERS THEN
401 /* Check for the exceptions */
402 raise_application_error(-20001, 'pos_outdate_relationship():' || x_exception_msg,true);
403 END pos_outdate_relationship;
404
405 procedure GET_RELATING_PARTY_ID(p_subject_id IN NUMBER,
406 p_relationship_type IN VARCHAR2,
407 p_relationship_code IN VARCHAR2,
408 x_object_id OUT NOCOPY NUMBER,
409 x_return_status OUT NOCOPY VARCHAR2,
410 x_exception_msg OUT NOCOPY VARCHAR2)
411 IS
412 l_object_id NUMBER;
413 BEGIN
414
415 select object_id
416 into l_object_id
417 from hz_relationships
418 where subject_id = p_subject_id
419 and relationship_type = p_relationship_type
420 and relationship_code = p_relationship_code
421 and status = 'A'
422 and start_date <= sysdate
423 and end_date >= sysdate;
424
425 x_return_status := 'S';
426 x_object_id := l_object_id;
427
428 EXCEPTION
429 WHEN NO_DATA_FOUND THEN
430 x_return_status := 'S'; -- No problem, a person may not have a boss!
431 WHEN OTHERS THEN
432 x_return_status := 'U';
433 x_exception_msg := 'DEBUG: POS_HZ_RELATIONSHIPS_PKG.get_relating_party_id';
434 raise_application_error(-20001, x_exception_msg, true);
435 END GET_RELATING_PARTY_ID;
436
437
438 procedure pos_outdate_relationship(
439 p_relationship_id IN NUMBER,
440 p_object_version_num IN NUMBER,
441 x_return_status OUT NOCOPY VARCHAR2,
442 x_exception_msg OUT NOCOPY VARCHAR2)
443 IS
444
445 l_rel_rec hz_relationship_v2pub.relationship_rec_type;
446 l_msg_count NUMBER;
447 l_msg_data VARCHAR2(240);
448 l_exception_msg varchar2(500);
449 l_party_obj_ver number;
450
451 l_obj_ver number;
452 BEGIN
453
454 l_exception_msg := 'POSHZRPB:V2:Start of out date relationship:';
455 l_rel_rec.relationship_id := p_relationship_id;
456 l_rel_rec.end_date := sysdate;
457 l_rel_rec.status := 'I';
458 l_obj_ver := p_object_version_num;
459
460 l_exception_msg := 'POSHZRPB:V2:Calling hz update relationship:';
461 hz_relationship_v2pub.update_relationship(
462 p_init_msg_list => FND_API.G_TRUE
463 ,p_relationship_rec => l_rel_rec
464 ,p_object_version_number => l_obj_ver
465 ,p_party_object_version_number => l_party_obj_ver
466 ,x_return_status => x_return_status
467 ,x_msg_count => l_msg_count
468 ,x_msg_data => l_msg_data
469 );
470 x_exception_msg := l_msg_data;
471 EXCEPTION
472 WHEN OTHERS THEN
473 x_return_status := 'E';
474 x_exception_msg := 'Caught exception in pos_outdate_relationship:V2:';
475 raise_application_error(-20001, x_exception_msg||' at :'
476 || l_exception_msg, true);
477 END pos_outdate_relationship;
478
479 END POS_HZ_RELATIONSHIPS_PKG;