[Home] [Help]
PACKAGE BODY: APPS.HZ_EXTRACT_ACCT_CONT_BO_PVT
Source
1 PACKAGE BODY HZ_EXTRACT_ACCT_CONT_BO_PVT AS
2 /*$Header: ARHECCVB.pls 120.7 2006/11/08 22:44:03 awu noship $ */
3 /*
4 * This package contains the private APIs for logical customer account contact.
5 * @rep:scope private
6 * @rep:product HZ
7 * @rep:displayname customer account contact
8 * @rep:category BUSINESS_ENTITY
9 * @rep:lifecycle active
10 * @rep:doccd 115hztig.pdf customer account contact Get APIs
11 */
12
13 --------------------------------------
14 --
15 -- PROCEDURE get_cust_acct_contact_bos
16 --
17 -- DESCRIPTION
18 -- Get logical customer account contacts.
19 --
20 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
21 --
22 -- ARGUMENTS
23 -- IN:
24 -- p_init_msg_list Initialize message stack if it is set to
25 -- p_parent_id parent id.
26 -- p_cust_acct_contact_id customer account contact ID.
27 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
28 -- OUT:
29 -- x_cust_acct_contact_objs Logical customer account contact records.
30 -- x_return_status Return status after the call. The status can
31 -- be fnd_api.g_ret_sts_success (success),
32 -- fnd_api.g_ret_sts_error (error),
33 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
34 -- x_msg_count Number of messages in message stack.
35 -- x_msg_data Message text if x_msg_count is 1.
36 --
37 -- NOTES
38 --
39 -- MODIFICATION HISTORY
40 --
41 --
42 -- 8-JUN-2005 AWU Created.
43 --
44
45 /*
46
47 The Get customer account contact API Procedure is a retrieval service that returns a full customer account contact business object.
48 The user identifies a particular Organization Contact business object using the TCA identifier and/or the object's Source System
49 information. Upon proper validation of the object, the full Organization Contact business object is returned.
50 The object consists of all data included within the Organization Contact business object, at all embedded levels. This includes
51 the set of all data stored in the TCA tables for each embedded entity.
52
53
54 Embedded BO Mandatory Multiple Logical API Procedure Comments
55
56 Org Contact Y N get_org_contact_bo
57
58
59 To retrieve the appropriate embedded entities within the 'Customer Account Contact' business object, the Get procedure returns
60 all records for the particular contact from these TCA entity tables.
61
62 Embedded TCA Entity Mandatory Multiple TCA Table Entities
63
64 Customer Account Role N N HZ_CUST_ACCOUNT_ROLES
65 Role Responsibility N Y HZ_ROLE_RESPONSIBILITY
66
67 */
68
69
70
71 PROCEDURE get_cust_acct_contact_bos(
72 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
73 p_parent_id IN NUMBER,
74 p_cust_acct_contact_id IN NUMBER,
75 p_action_type IN VARCHAR2 := NULL,
76 x_cust_acct_contact_objs OUT NOCOPY HZ_CUST_ACCT_CONTACT_BO_TBL,
77 x_return_status OUT NOCOPY VARCHAR2,
78 x_msg_count OUT NOCOPY NUMBER,
79 x_msg_data OUT NOCOPY VARCHAR2
80 ) is
81 l_debug_prefix VARCHAR2(30) := '';
82
83 CURSOR C1 IS
84 SELECT HZ_CUST_ACCT_CONTACT_BO(
85 P_ACTION_TYPE,
86 NULL, --COMMON_OBJ_ID
87 CAR.CUST_ACCOUNT_ROLE_ID, --CUST_ACCT_CONTACT_ID,
88 NULL, --ORIG_SYSTEM,
89 NULL, --ORIG_SYSTEM_REFERENCE,
90 CAR.CUST_ACCOUNT_ID,
91 CAR.CUST_ACCT_SITE_ID,
92 CAR.PRIMARY_FLAG,
93 CAR.ROLE_TYPE,
94 CAR.SOURCE_CODE,
95 CAR.ATTRIBUTE_CATEGORY,
96 CAR.ATTRIBUTE1,
97 CAR.ATTRIBUTE2,
98 CAR.ATTRIBUTE3,
99 CAR.ATTRIBUTE4,
100 CAR.ATTRIBUTE5,
101 CAR.ATTRIBUTE6,
102 CAR.ATTRIBUTE7,
103 CAR.ATTRIBUTE8,
104 CAR.ATTRIBUTE9,
105 CAR.ATTRIBUTE10,
106 CAR.ATTRIBUTE11,
107 CAR.ATTRIBUTE12,
108 CAR.ATTRIBUTE13,
109 CAR.ATTRIBUTE14,
110 CAR.ATTRIBUTE15,
111 CAR.ATTRIBUTE16,
112 CAR.ATTRIBUTE17,
113 CAR.ATTRIBUTE18,
114 CAR.ATTRIBUTE19,
115 CAR.ATTRIBUTE20,
116 CAR.ATTRIBUTE21,
117 CAR.ATTRIBUTE22,
118 CAR.ATTRIBUTE23,
119 CAR.ATTRIBUTE24,
120 CAR.ATTRIBUTE25,
121 CAR.STATUS,
122 CAR.PROGRAM_UPDATE_DATE,
123 CAR.CREATED_BY_MODULE,
124 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CAR.CREATED_BY),
125 CAR.CREATION_DATE,
126 CAR.LAST_UPDATE_DATE,
127 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CAR.LAST_UPDATED_BY),
128 R.SUBJECT_ID, --CONTACT_PERSON_ID,
129 NULL, --CONTACT_PERSON_OS,
130 NULL, --CONTACT_PERSON_OSR,
131 R.RELATIONSHIP_ID,
132 R.RELATIONSHIP_CODE,
133 R.RELATIONSHIP_TYPE,
134 R.START_DATE,
135 CAST(MULTISET (
136 SELECT HZ_ORIG_SYS_REF_OBJ(
137 NULL, --P_ACTION_TYPE,
138 ORIG_SYSTEM_REF_ID,
139 ORIG_SYSTEM,
140 ORIG_SYSTEM_REFERENCE,
141 OWNER_TABLE_NAME,
142 OWNER_TABLE_ID,
143 STATUS,
144 REASON_CODE,
145 OLD_ORIG_SYSTEM_REFERENCE,
146 START_DATE_ACTIVE,
147 END_DATE_ACTIVE,
148 PROGRAM_UPDATE_DATE,
149 CREATED_BY_MODULE,
150 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
151 CREATION_DATE,
152 LAST_UPDATE_DATE,
153 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
154 ATTRIBUTE_CATEGORY,
155 ATTRIBUTE1,
156 ATTRIBUTE2,
157 ATTRIBUTE3,
158 ATTRIBUTE4,
159 ATTRIBUTE5,
160 ATTRIBUTE6,
161 ATTRIBUTE7,
162 ATTRIBUTE8,
163 ATTRIBUTE9,
164 ATTRIBUTE10,
165 ATTRIBUTE11,
166 ATTRIBUTE12,
167 ATTRIBUTE13,
168 ATTRIBUTE14,
169 ATTRIBUTE15,
170 ATTRIBUTE16,
171 ATTRIBUTE17,
172 ATTRIBUTE18,
173 ATTRIBUTE19,
174 ATTRIBUTE20)
175 FROM HZ_ORIG_SYS_REFERENCES OSR
176 WHERE
177 OSR.OWNER_TABLE_ID = CAR.CUST_ACCOUNT_ROLE_ID
178 AND OWNER_TABLE_NAME = 'HZ_CUST_ACCOUNT_ROLES'
179 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL), -- acct contact ssm
180 CAST(MULTISET (
181 SELECT HZ_ORIG_SYS_REF_OBJ(
182 NULL, --P_ACTION_TYPE,
183 OSR.ORIG_SYSTEM_REF_ID,
184 OSR.ORIG_SYSTEM,
185 OSR.ORIG_SYSTEM_REFERENCE,
186 OSR.OWNER_TABLE_NAME,
187 OSR.OWNER_TABLE_ID,
188 OSR.STATUS,
189 OSR.REASON_CODE,
190 OSR.OLD_ORIG_SYSTEM_REFERENCE,
191 OSR.START_DATE_ACTIVE,
192 OSR.END_DATE_ACTIVE,
193 OSR.PROGRAM_UPDATE_DATE,
194 OSR.CREATED_BY_MODULE,
195 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(OSR.CREATED_BY),
196 OSR.CREATION_DATE,
197 OSR.LAST_UPDATE_DATE,
198 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(OSR.LAST_UPDATED_BY),
199 OSR.ATTRIBUTE_CATEGORY,
200 OSR.ATTRIBUTE1,
201 OSR.ATTRIBUTE2,
202 OSR.ATTRIBUTE3,
203 OSR.ATTRIBUTE4,
204 OSR.ATTRIBUTE5,
205 OSR.ATTRIBUTE6,
206 OSR.ATTRIBUTE7,
207 OSR.ATTRIBUTE8,
208 OSR.ATTRIBUTE9,
209 OSR.ATTRIBUTE10,
210 OSR.ATTRIBUTE11,
211 OSR.ATTRIBUTE12,
212 OSR.ATTRIBUTE13,
213 OSR.ATTRIBUTE14,
214 OSR.ATTRIBUTE15,
215 OSR.ATTRIBUTE16,
216 OSR.ATTRIBUTE17,
217 OSR.ATTRIBUTE18,
218 OSR.ATTRIBUTE19,
219 OSR.ATTRIBUTE20)
220 FROM HZ_ORIG_SYS_REFERENCES OSR, HZ_RELATIONSHIPS R
221 WHERE OSR.OWNER_TABLE_ID = R.SUBJECT_ID
222 AND CAR.PARTY_ID = R.PARTY_ID
223 AND R.SUBJECT_TYPE = 'PERSON'
224 AND OSR.OWNER_TABLE_NAME = 'HZ_PARTIES'
225 AND OSR.STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL), -- contact person ssm
226 CAST(MULTISET (
227 SELECT HZ_ROLE_RESPONSIBILITY_OBJ(
228 P_ACTION_TYPE,
229 NULL, --COMMON_OBJ_ID
230 RESPONSIBILITY_ID,
231 CUST_ACCOUNT_ROLE_ID,
232 RESPONSIBILITY_TYPE,
233 PRIMARY_FLAG,
234 ATTRIBUTE_CATEGORY,
235 ATTRIBUTE1,
236 ATTRIBUTE2,
237 ATTRIBUTE3,
238 ATTRIBUTE4,
239 ATTRIBUTE5,
240 ATTRIBUTE6,
241 ATTRIBUTE7,
242 ATTRIBUTE8,
243 ATTRIBUTE9,
244 ATTRIBUTE10,
245 ATTRIBUTE11,
246 ATTRIBUTE12,
247 ATTRIBUTE13,
248 ATTRIBUTE14,
249 ATTRIBUTE15,
250 PROGRAM_UPDATE_DATE,
251 CREATED_BY_MODULE,
252 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
253 CREATION_DATE,
254 LAST_UPDATE_DATE,
255 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
256 FROM HZ_ROLE_RESPONSIBILITY
257 WHERE CUST_ACCOUNT_ROLE_ID = CAR.CUST_ACCOUNT_ROLE_ID) AS HZ_ROLE_RESPONSIBILITY_OBJ_TBL))
258 FROM HZ_CUST_ACCOUNT_ROLES CAR, HZ_CUST_ACCOUNTS CA, HZ_RELATIONSHIPS R
259 WHERE
260 R.PARTY_ID = CAR.PARTY_ID
261 AND CA.PARTY_ID = R.OBJECT_ID
262 AND CAR.CUST_ACCOUNT_ID = CA.CUST_ACCOUNT_ID
263 AND ((P_CUST_ACCT_CONTACT_ID IS NULL AND CAR.CUST_ACCOUNT_ID = P_PARENT_ID)
264 OR (P_CUST_ACCT_CONTACT_ID IS NOT NULL
265 AND CAR.CUST_ACCOUNT_ROLE_ID = P_CUST_ACCT_CONTACT_ID));
266
267
268 BEGIN
269
270
271 -- initialize API return status to success.
272 x_return_status := FND_API.G_RET_STS_SUCCESS;
273
274 -- Initialize message list if p_init_msg_list is set to TRUE
275 IF FND_API.to_Boolean(p_init_msg_list) THEN
276 FND_MSG_PUB.initialize;
277 END IF;
278
279
280 -- Debug info.
281 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
282 hz_utility_v2pub.debug(p_message=>'get_cust_acct_contact_bos(+)',
283 p_prefix=>l_debug_prefix,
284 p_msg_level=>fnd_log.level_procedure);
285 END IF;
286
287 x_cust_acct_contact_objs := HZ_CUST_ACCT_CONTACT_BO_TBL();
288 open c1;
289 fetch c1 BULK COLLECT into x_cust_acct_contact_objs;
290 close c1;
291
292
293
294 -- Debug info.
295 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
296 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
297 p_msg_data=>x_msg_data,
298 p_msg_type=>'WARNING',
299 p_msg_level=>fnd_log.level_exception);
300 END IF;
301
302 -- Debug info.
303 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
304 hz_utility_v2pub.debug(p_message=>'get_cust_acct_contact_bos (-)',
305 p_prefix=>l_debug_prefix,
306 p_msg_level=>fnd_log.level_procedure);
307 END IF;
308
309
310 EXCEPTION
311
312 WHEN fnd_api.g_exc_error THEN
313 x_return_status := fnd_api.g_ret_sts_error;
314
315 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
316 p_count => x_msg_count,
317 p_data => x_msg_data);
318
319 -- Debug info.
320 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
321 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
322 p_msg_data=>x_msg_data,
323 p_msg_type=>'ERROR',
324 p_msg_level=>fnd_log.level_error);
325 END IF;
326 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
327 hz_utility_v2pub.debug(p_message=>'get_cust_acct_contact_bos (-)',
328 p_prefix=>l_debug_prefix,
329 p_msg_level=>fnd_log.level_procedure);
330 END IF;
331 WHEN fnd_api.g_exc_unexpected_error THEN
332 x_return_status := fnd_api.g_ret_sts_unexp_error;
333
334 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
335 p_count => x_msg_count,
336 p_data => x_msg_data);
337
338 -- Debug info.
339 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
340 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
341 p_msg_data=>x_msg_data,
342 p_msg_type=>'UNEXPECTED ERROR',
343 p_msg_level=>fnd_log.level_error);
344 END IF;
345 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
346 hz_utility_v2pub.debug(p_message=>'get_cust_acct_contact_bos (-)',
347 p_prefix=>l_debug_prefix,
348 p_msg_level=>fnd_log.level_procedure);
349 END IF;
350 WHEN OTHERS THEN
351 x_return_status := fnd_api.g_ret_sts_unexp_error;
352
353 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
354 fnd_message.set_token('ERROR' ,SQLERRM);
355 fnd_msg_pub.add;
356
357 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
358 p_count => x_msg_count,
359 p_data => x_msg_data);
360
361 -- Debug info.
362 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
363 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
364 p_msg_data=>x_msg_data,
365 p_msg_type=>'SQL ERROR',
366 p_msg_level=>fnd_log.level_error);
367 END IF;
368 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
369 hz_utility_v2pub.debug(p_message=>'get_cust_acct_contact_bos (-)',
370 p_prefix=>l_debug_prefix,
371 p_msg_level=>fnd_log.level_procedure);
372 END IF;
373
374 end;
375
376
377
378 END HZ_EXTRACT_ACCT_CONT_BO_PVT;