[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.6 2010/08/31 05:36:30 vsegu 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 AND SUBJECT_TABLE_NAME = 'HZ_PARTIES';
180
181
182 l_debug_prefix VARCHAR2(30) := '';
183
184 BEGIN
185
186
187 -- initialize API return status to success.
188 x_return_status := FND_API.G_RET_STS_SUCCESS;
189
190 -- Initialize message list if p_init_msg_list is set to TRUE
191 IF FND_API.to_Boolean(p_init_msg_list) THEN
192 FND_MSG_PUB.initialize;
193 END IF;
194
195
196 -- Debug info.
197 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
198 hz_utility_v2pub.debug(p_message=>'get_relationship_bos(+)',
199 p_prefix=>l_debug_prefix,
200 p_msg_level=>fnd_log.level_procedure);
201 END IF;
202
203 x_relationship_objs := HZ_RELATIONSHIP_OBJ_TBL();
204 open c1;
205 fetch c1 BULK COLLECT into x_relationship_objs;
206 close c1;
207
208 -- Debug info.
209 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
210 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
211 p_msg_data=>x_msg_data,
212 p_msg_type=>'WARNING',
213 p_msg_level=>fnd_log.level_exception);
214 END IF;
215
216 -- Debug info.
217 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
218 hz_utility_v2pub.debug(p_message=>'get_relationship_bos (-)',
219 p_prefix=>l_debug_prefix,
220 p_msg_level=>fnd_log.level_procedure);
221 END IF;
222
223
224 EXCEPTION
225
226 WHEN fnd_api.g_exc_error THEN
227 x_return_status := fnd_api.g_ret_sts_error;
228
229 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
230 p_count => x_msg_count,
231 p_data => x_msg_data);
232
233 -- Debug info.
234 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
235 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
236 p_msg_data=>x_msg_data,
237 p_msg_type=>'ERROR',
238 p_msg_level=>fnd_log.level_error);
239 END IF;
240 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
241 hz_utility_v2pub.debug(p_message=>'get_relationship_bos (-)',
242 p_prefix=>l_debug_prefix,
243 p_msg_level=>fnd_log.level_procedure);
244 END IF;
245 WHEN fnd_api.g_exc_unexpected_error THEN
246 x_return_status := fnd_api.g_ret_sts_unexp_error;
247
248 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
249 p_count => x_msg_count,
250 p_data => x_msg_data);
251
252 -- Debug info.
253 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
254 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
255 p_msg_data=>x_msg_data,
256 p_msg_type=>'UNEXPECTED ERROR',
257 p_msg_level=>fnd_log.level_error);
258 END IF;
259 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
260 hz_utility_v2pub.debug(p_message=>'get_relationship_bos (-)',
261 p_prefix=>l_debug_prefix,
262 p_msg_level=>fnd_log.level_procedure);
263 END IF;
264 WHEN OTHERS THEN
265 x_return_status := fnd_api.g_ret_sts_unexp_error;
266
267 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
268 fnd_message.set_token('ERROR' ,SQLERRM);
269 fnd_msg_pub.add;
270
271 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
272 p_count => x_msg_count,
273 p_data => x_msg_data);
274
275 -- Debug info.
276 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
277 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
278 p_msg_data=>x_msg_data,
279 p_msg_type=>'SQL ERROR',
280 p_msg_level=>fnd_log.level_error);
281 END IF;
282 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
283 hz_utility_v2pub.debug(p_message=>'get_relationship_bos (-)',
284 p_prefix=>l_debug_prefix,
285 p_msg_level=>fnd_log.level_procedure);
286 END IF;
287
288 end;
289
290
291
292
293 END HZ_EXTRACT_RELATIONSHIP_BO_PVT;