[Home] [Help]
PACKAGE BODY: APPS.HZ_EXTRACT_RELATIONSHIP_BO_PVT
Source
1 PACKAGE BODY HZ_EXTRACT_RELATIONSHIP_BO_PVT AS
2 /*$Header: ARHEREVB.pls 120.4.12000000.2 2007/02/22 20:14:59 awu ship $ */
3 /*
4 * This package contains the private APIs for ssm information.
5 * @rep:scope private
6 * @rep:product HZ
7 * @rep:displayname
8 * @rep:category BUSINESS_ENTITY HZ_PARTIES
9 * @rep:lifecycle active
10 * @rep:doccd 115hztig.pdf cGet APIs
11 */
12
13 --------------------------------------
14 --
15 -- PROCEDURE get_relationship_bos
16 --
17 -- DESCRIPTION
18 -- Get relationship information.
19 --
20 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
21 --
22 -- ARGUMENTS
23 -- IN:
24 -- p_init_msg_list Initialize message stack if it is set to FND_API.G_TRUE. Default is FND_API.G_FALSE.
25 -- p_subject_id relationship subject ID.
26 --
27 -- OUT:
28 -- x_relationship_objs Table of relationship objects.
29 -- x_return_status Return status after the call. The status can
30 -- be fnd_api.g_ret_sts_success (success),
31 -- fnd_api.g_ret_sts_error (error),
32 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
33 -- x_msg_count Number of messages in message stack.
34 -- x_msg_data Message text if x_msg_count is 1.
35 --
36 -- NOTES
37 --
38 -- MODIFICATION HISTORY
39 --
40 --
41 -- 15-May-2005 AWU Created.
42 --
43
44
45
46 PROCEDURE get_relationship_bos(
47 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
48 p_subject_id IN NUMBER,
49 p_action_type IN VARCHAR2 := NULL,
50 x_relationship_objs OUT NOCOPY HZ_RELATIONSHIP_OBJ_TBL,
51 x_return_status OUT NOCOPY VARCHAR2,
52 x_msg_count OUT NOCOPY NUMBER,
53 x_msg_data OUT NOCOPY VARCHAR2
54 ) is
55
56 CURSOR C1 IS
57 SELECT HZ_RELATIONSHIP_OBJ(
58 P_ACTION_TYPE,
59 NULL, -- COMMON_OBJ_ID
60 RELATIONSHIP_ID,
61 DECODE(SUBJECT_TYPE, 'ORGANIZATION', 'ORG', SUBJECT_TYPE),
62 P_SUBJECT_ID,
63 OBJECT_ID,
64 DECODE(OBJECT_TYPE, 'ORGANIZATION', 'ORG', OBJECT_TYPE),
65 NULL, --OBJECT_ORIG_SYSTEM_REFERENCE,
66 NULL, --OBJECT_ORIG_SYSTEM,
67 RELATIONSHIP_CODE,
68 RELATIONSHIP_TYPE,
69 COMMENTS,
70 START_DATE,
71 END_DATE,
72 STATUS,
73 ATTRIBUTE_CATEGORY,
74 ATTRIBUTE1,
75 ATTRIBUTE2,
76 ATTRIBUTE3,
77 ATTRIBUTE4,
78 ATTRIBUTE5,
79 ATTRIBUTE6,
80 ATTRIBUTE7,
81 ATTRIBUTE8,
82 ATTRIBUTE9,
83 ATTRIBUTE10,
84 ATTRIBUTE11,
85 ATTRIBUTE12,
86 ATTRIBUTE13,
87 ATTRIBUTE14,
88 ATTRIBUTE15,
89 ATTRIBUTE16,
90 ATTRIBUTE17,
91 ATTRIBUTE18,
92 ATTRIBUTE19,
93 ATTRIBUTE20,
94 PROGRAM_UPDATE_DATE,
95 CREATED_BY_MODULE,
96 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
97 CREATION_DATE,
98 LAST_UPDATE_DATE,
99 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
100 ADDITIONAL_INFORMATION1,
101 ADDITIONAL_INFORMATION2,
102 ADDITIONAL_INFORMATION3,
103 ADDITIONAL_INFORMATION4,
104 ADDITIONAL_INFORMATION5,
105 ADDITIONAL_INFORMATION6,
106 ADDITIONAL_INFORMATION7,
107 ADDITIONAL_INFORMATION8,
108 ADDITIONAL_INFORMATION9,
109 ADDITIONAL_INFORMATION10,
110 ADDITIONAL_INFORMATION11,
111 ADDITIONAL_INFORMATION12,
112 ADDITIONAL_INFORMATION13,
113 ADDITIONAL_INFORMATION14,
114 ADDITIONAL_INFORMATION15,
115 ADDITIONAL_INFORMATION16,
116 ADDITIONAL_INFORMATION17,
117 ADDITIONAL_INFORMATION18,
118 ADDITIONAL_INFORMATION19,
119 ADDITIONAL_INFORMATION20,
120 ADDITIONAL_INFORMATION21,
121 ADDITIONAL_INFORMATION22,
122 ADDITIONAL_INFORMATION23,
123 ADDITIONAL_INFORMATION24,
124 ADDITIONAL_INFORMATION25,
125 ADDITIONAL_INFORMATION26,
126 ADDITIONAL_INFORMATION27,
127 ADDITIONAL_INFORMATION28,
128 ADDITIONAL_INFORMATION29,
129 ADDITIONAL_INFORMATION30,
130 PERCENTAGE_OWNERSHIP,
131 ACTUAL_CONTENT_SOURCE,
132 CAST(MULTISET (
133 SELECT HZ_ORIG_SYS_REF_OBJ(
134 NULL, --P_ACTION_TYPE,
135 ORIG_SYSTEM_REF_ID,
136 ORIG_SYSTEM,
137 ORIG_SYSTEM_REFERENCE,
138 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
139 OWNER_TABLE_ID,
140 STATUS,
141 REASON_CODE,
142 OLD_ORIG_SYSTEM_REFERENCE,
143 START_DATE_ACTIVE,
144 END_DATE_ACTIVE,
145 PROGRAM_UPDATE_DATE,
146 CREATED_BY_MODULE,
147 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
148 CREATION_DATE,
149 LAST_UPDATE_DATE,
150 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
151 ATTRIBUTE_CATEGORY,
152 ATTRIBUTE1,
153 ATTRIBUTE2,
154 ATTRIBUTE3,
155 ATTRIBUTE4,
156 ATTRIBUTE5,
157 ATTRIBUTE6,
158 ATTRIBUTE7,
159 ATTRIBUTE8,
160 ATTRIBUTE9,
161 ATTRIBUTE10,
162 ATTRIBUTE11,
163 ATTRIBUTE12,
164 ATTRIBUTE13,
165 ATTRIBUTE14,
166 ATTRIBUTE15,
167 ATTRIBUTE16,
168 ATTRIBUTE17,
169 ATTRIBUTE18,
170 ATTRIBUTE19,
171 ATTRIBUTE20)
172 FROM HZ_ORIG_SYS_REFERENCES OSR
173 WHERE
174 OSR.OWNER_TABLE_ID = REL.OBJECT_ID
175 AND OWNER_TABLE_NAME = 'HZ_PARTIES'
176 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL))
177 FROM HZ_RELATIONSHIPS REL
178 WHERE SUBJECT_ID = P_SUBJECT_ID;
179
180
181 l_debug_prefix VARCHAR2(30) := '';
182
183 BEGIN
184
185
186 -- initialize API return status to success.
187 x_return_status := FND_API.G_RET_STS_SUCCESS;
188
189 -- Initialize message list if p_init_msg_list is set to TRUE
190 IF FND_API.to_Boolean(p_init_msg_list) THEN
191 FND_MSG_PUB.initialize;
192 END IF;
193
194
195 -- Debug info.
196 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
197 hz_utility_v2pub.debug(p_message=>'get_relationship_bos(+)',
198 p_prefix=>l_debug_prefix,
199 p_msg_level=>fnd_log.level_procedure);
200 END IF;
201
202 x_relationship_objs := HZ_RELATIONSHIP_OBJ_TBL();
203 open c1;
204 fetch c1 BULK COLLECT into x_relationship_objs;
205 close c1;
206
207 -- Debug info.
208 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
209 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
210 p_msg_data=>x_msg_data,
211 p_msg_type=>'WARNING',
212 p_msg_level=>fnd_log.level_exception);
213 END IF;
214
215 -- Debug info.
216 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
217 hz_utility_v2pub.debug(p_message=>'get_relationship_bos (-)',
218 p_prefix=>l_debug_prefix,
219 p_msg_level=>fnd_log.level_procedure);
220 END IF;
221
222
223 EXCEPTION
224
225 WHEN fnd_api.g_exc_error THEN
226 x_return_status := fnd_api.g_ret_sts_error;
227
228 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
229 p_count => x_msg_count,
230 p_data => x_msg_data);
231
232 -- Debug info.
233 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
234 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
235 p_msg_data=>x_msg_data,
236 p_msg_type=>'ERROR',
237 p_msg_level=>fnd_log.level_error);
238 END IF;
239 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
240 hz_utility_v2pub.debug(p_message=>'get_relationship_bos (-)',
241 p_prefix=>l_debug_prefix,
242 p_msg_level=>fnd_log.level_procedure);
243 END IF;
244 WHEN fnd_api.g_exc_unexpected_error THEN
245 x_return_status := fnd_api.g_ret_sts_unexp_error;
246
247 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
251 -- Debug info.
248 p_count => x_msg_count,
249 p_data => x_msg_data);
250
252 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
253 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
254 p_msg_data=>x_msg_data,
255 p_msg_type=>'UNEXPECTED ERROR',
256 p_msg_level=>fnd_log.level_error);
257 END IF;
258 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
259 hz_utility_v2pub.debug(p_message=>'get_relationship_bos (-)',
260 p_prefix=>l_debug_prefix,
261 p_msg_level=>fnd_log.level_procedure);
262 END IF;
263 WHEN OTHERS THEN
264 x_return_status := fnd_api.g_ret_sts_unexp_error;
265
266 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
267 fnd_message.set_token('ERROR' ,SQLERRM);
268 fnd_msg_pub.add;
269
270 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
271 p_count => x_msg_count,
272 p_data => x_msg_data);
273
274 -- Debug info.
275 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
276 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
277 p_msg_data=>x_msg_data,
278 p_msg_type=>'SQL ERROR',
279 p_msg_level=>fnd_log.level_error);
280 END IF;
281 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
282 hz_utility_v2pub.debug(p_message=>'get_relationship_bos (-)',
283 p_prefix=>l_debug_prefix,
284 p_msg_level=>fnd_log.level_procedure);
285 END IF;
286
287 end;
288
289
290
291
292 END HZ_EXTRACT_RELATIONSHIP_BO_PVT;