1 PACKAGE BODY HZ_EXTRACT_ORG_CUST_BO_PVT AS
2 /*$Header: ARHEOAVB.pls 120.9 2008/02/06 10:33:07 vsegu ship $ */
3 /*
4 * This package contains the private APIs for logical org customer.
5 * @rep:scope private
6 * @rep:product HZ
7 * @rep:displayname Organization Customer
8 * @rep:category BUSINESS_ENTITY HZ_PARTIES
9 * @rep:lifecycle active
10 * @rep:doccd 115hztig.pdf Organization Customer Get APIs
11 */
12
13 --------------------------------------
14 --
15 -- PROCEDURE get_org_cust_bo
16 --
17 -- DESCRIPTION
18 -- Get a logical organization customer.
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_organization_id Organization ID.
26 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
27 -- OUT:
28 -- x_org_cust_obj Logical organization customer record.
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 -- 10-JUN-2005 AWU Created.
42 --
43
44 /*
45 The Get Organization Customer API Procedure is a retrieval service that returns a full Organization Customer business object.
46 The user identifies a particular Organization Customer business object using the TCA identifier and/or
47 the object Source System information. Upon proper validation of the object,
48 the full Organization Customer business object is returned. The object consists of all data included within
49 the Organization Customer business object, at all embedded levels. This includes the set of all data stored
50 in the TCA tables for each embedded entity.
51
52 To retrieve the appropriate embedded business objects within the Organization Customer business object,
53 the Get procedure calls the equivalent procedure for the following embedded objects:
54
55 Embedded BO Mandatory Multiple Logical API Procedure Comments
56
57 Organization Y N get_organization_bo
58 Customer Account Y Y get_cust_acct_bo Called for each Customer Account object for the Organization Customer
59
60 */
61
62
63
64 PROCEDURE get_org_cust_bo(
65 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
66 p_organization_id IN NUMBER,
67 p_action_type IN VARCHAR2 := NULL,
68 x_org_cust_obj OUT NOCOPY HZ_ORG_CUST_BO,
69 x_return_status OUT NOCOPY VARCHAR2,
70 x_msg_count OUT NOCOPY NUMBER,
71 x_msg_data OUT NOCOPY VARCHAR2
72 ) is
73 l_debug_prefix VARCHAR2(30) := '';
74
75 begin
76 -- initialize API return status to success.
77 x_return_status := FND_API.G_RET_STS_SUCCESS;
78
79 -- Initialize message list if p_init_msg_list is set to TRUE
80 IF FND_API.to_Boolean(p_init_msg_list) THEN
81 FND_MSG_PUB.initialize;
82 END IF;
83
84
85 -- Debug info.
86 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
87 hz_utility_v2pub.debug(p_message=>'get_org_cust_bo(+)',
88 p_prefix=>l_debug_prefix,
89 p_msg_level=>fnd_log.level_procedure);
90 END IF;
91
92 x_org_cust_obj := HZ_ORG_CUST_BO(p_action_type, NULL, NULL);
93
94 HZ_EXTRACT_ORGANIZATION_BO_PVT.get_organization_bo(
95 p_init_msg_list => fnd_api.g_false,
96 p_organization_id => p_organization_id,
97 p_action_type => p_action_type,
98 x_organization_obj => x_org_cust_obj.organization_obj,
99 x_return_status => x_return_status,
100 x_msg_count => x_msg_count,
101 x_msg_data => x_msg_data);
102
103 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
104 RAISE FND_API.G_EXC_ERROR;
105 END IF;
106
107
108 HZ_EXTRACT_CUST_ACCT_BO_PVT.get_cust_acct_bos(
109 p_init_msg_list => fnd_api.g_false,
110 p_parent_id => p_organization_id,
111 p_cust_acct_id => NULL,
112 p_action_type => p_action_type,
113 x_cust_acct_objs => x_org_cust_obj.account_objs,
114 x_return_status => x_return_status,
115 x_msg_count => x_msg_count,
116 x_msg_data => x_msg_data);
117
118 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
119 RAISE FND_API.G_EXC_ERROR;
120 END IF;
121
122
123 -- Debug info.
124 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
125 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
126 p_msg_data=>x_msg_data,
127 p_msg_type=>'WARNING',
128 p_msg_level=>fnd_log.level_exception);
129 END IF;
130
131 -- Debug info.
132 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
133 hz_utility_v2pub.debug(p_message=>'get_org_cust_bo (-)',
134 p_prefix=>l_debug_prefix,
135 p_msg_level=>fnd_log.level_procedure);
136 END IF;
137
138
139 EXCEPTION
140
141 WHEN fnd_api.g_exc_error THEN
142 x_return_status := fnd_api.g_ret_sts_error;
143
144 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
145 p_count => x_msg_count,
146 p_data => x_msg_data);
147
148 -- Debug info.
149 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
150 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
151 p_msg_data=>x_msg_data,
152 p_msg_type=>'ERROR',
153 p_msg_level=>fnd_log.level_error);
154 END IF;
155 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
156 hz_utility_v2pub.debug(p_message=>'get_org_cust_bo (-)',
157 p_prefix=>l_debug_prefix,
158 p_msg_level=>fnd_log.level_procedure);
159 END IF;
160 WHEN fnd_api.g_exc_unexpected_error THEN
161 x_return_status := fnd_api.g_ret_sts_unexp_error;
162
163 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
164 p_count => x_msg_count,
165 p_data => x_msg_data);
166
167 -- Debug info.
168 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
169 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
170 p_msg_data=>x_msg_data,
171 p_msg_type=>'UNEXPECTED ERROR',
172 p_msg_level=>fnd_log.level_error);
173 END IF;
174 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
175 hz_utility_v2pub.debug(p_message=>'get_org_cust_bo (-)',
176 p_prefix=>l_debug_prefix,
177 p_msg_level=>fnd_log.level_procedure);
178 END IF;
179 WHEN OTHERS THEN
180 x_return_status := fnd_api.g_ret_sts_unexp_error;
181
182 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
183 fnd_message.set_token('ERROR' ,SQLERRM);
184 fnd_msg_pub.add;
185
186 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
187 p_count => x_msg_count,
188 p_data => x_msg_data);
189
190 -- Debug info.
191 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
192 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
193 p_msg_data=>x_msg_data,
194 p_msg_type=>'SQL ERROR',
195 p_msg_level=>fnd_log.level_error);
196 END IF;
197 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
198 hz_utility_v2pub.debug(p_message=>'get_org_cust_bo (-)',
199 p_prefix=>l_debug_prefix,
200 p_msg_level=>fnd_log.level_procedure);
201 END IF;
202
203 end;
204
205 function get_org_operation_type(p_event_id in number) return varchar2 is
206
207
208 cursor check_org_action_type_csr is
209 select child_operation_flag
210 from hz_bus_obj_tracking
211 where event_id = p_event_id
212 and child_bo_code = 'ORG'
213 and parent_bo_code = 'ORG_CUST';
214
215 l_child_operation_flag varchar2(1);
216 begin
217
218 open check_org_action_type_csr;
219 fetch check_org_action_type_csr into l_child_operation_flag;
220 close check_org_action_type_csr;
221
222 return l_child_operation_flag;
223 end;
224
225 --------------------------------------
226 --
227 -- PROCEDURE get_org_custs_created
228 --
229 -- DESCRIPTION
230 --The caller provides an identifier for the Organization Customers created business event and
231 --the procedure returns database objects of the type HZ_ORG CUSTOMER_BO for all of
232 --the Organization Customer business objects from the business event.
233
234 --
235 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
236 --
237 -- ARGUMENTS
238 -- IN:
239 -- p_init_msg_list Initialize message stack if it is set to
240 -- p_event_id BES Event identifier.
241 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
242 -- OUT:
243 -- x_org_cust_objs One or more created logical organization customer.
244 -- x_return_status Return status after the call. The status can
245 -- be fnd_api.g_ret_sts_success (success),
246 -- fnd_api.g_ret_sts_error (error),
247 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
248 -- x_msg_count Number of messages in message stack.
249 -- x_msg_data Message text if x_msg_count is 1.
250 --
251 -- NOTES
252 --
253 -- MODIFICATION HISTORY
254 --
255 -- 10-JUN-2005 AWU Created.
256 --
257
258
259
260 /*
261 The Get organization customers Created procedure is a service to retrieve all of the Organization Customer business objects
262 whose creations have been captured by a logical business event. Each Organization Customers Created
263 business event signifies that one or more Organization Customer business objects have been created.
264 The caller provides an identifier for the Organization Customers Created business event and the procedure
265 returns all of the Organization Customer business objects from the business event. For each business object
266 creation captured in the business event, the procedure calls the generic Get operation:
267 HZ_ORG_BO_PVT.get_org_bo
268
269 Gathering all of the returned business objects from those API calls, the procedure packages
270 them in a table structure and returns them to the caller.
271 */
272
273
274 PROCEDURE get_org_custs_created(
275 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
276 p_event_id IN NUMBER,
277 x_org_cust_objs OUT NOCOPY HZ_ORG_CUST_BO_TBL,
278 x_return_status OUT NOCOPY VARCHAR2,
279 x_msg_count OUT NOCOPY NUMBER,
280 x_msg_data OUT NOCOPY VARCHAR2
281 ) is
282
283 l_obj_root_ids HZ_EXTRACT_BO_UTIL_PVT.BO_ID_TBL;
284 l_debug_prefix VARCHAR2(30) := '';
285 L_CHILD_OPERATION_FLAG varchar2(1);
286 l_action_type varchar2(30);
287
288 begin
289
290 -- initialize API return status to success.
291 x_return_status := FND_API.G_RET_STS_SUCCESS;
292
293 -- Initialize message list if p_init_msg_list is set to TRUE
294 IF FND_API.to_Boolean(p_init_msg_list) THEN
295 FND_MSG_PUB.initialize;
296 END IF;
297
298
299 -- Debug info.
300 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
301 hz_utility_v2pub.debug(p_message=>'get_org_cust_created(+)',
302 p_prefix=>l_debug_prefix,
303 p_msg_level=>fnd_log.level_procedure);
304 END IF;
305
306
307 HZ_EXTRACT_BO_UTIL_PVT.get_bo_root_ids(
308 p_init_msg_list => fnd_api.g_false,
309 p_event_id => p_event_id,
310 x_obj_root_ids => l_obj_root_ids,
311 x_return_status => x_return_status,
312 x_msg_count => x_msg_count,
313 x_msg_data => x_msg_data);
314
315 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
316 RAISE FND_API.G_EXC_ERROR;
317 END IF;
318
319 l_child_operation_flag:= get_org_operation_type(p_event_id);
320
321 if L_CHILD_OPERATION_FLAG = 'I'
322 then
323 l_action_type := 'CREATED';
324 else
325 l_action_type := 'UNCHANGED'; -- default to unchanged.
326 end if;
327
328
329 -- call event API get_org_cust_updated for each id.
330
331 x_org_cust_objs := HZ_ORG_CUST_BO_TBL();
332
333
334 for i in 1..l_obj_root_ids.count loop
335
336 x_org_cust_objs.extend;
337 /* get_org_cust_bo(
338 p_init_msg_list => fnd_api.g_false,
339 p_organization_id => l_obj_root_ids(i),
340 p_action_type => 'CREATED',
341 x_org_cust_obj => x_org_cust_objs(i),
342 x_return_status => x_return_status,
343 x_msg_count => x_msg_count,
344 x_msg_data => x_msg_data);
345
346 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
347 RAISE FND_API.G_EXC_ERROR;
348 END IF;
349 */
350
351 x_org_cust_objs(i) := HZ_ORG_CUST_BO('UNCHANGED', NULL, NULL);
352
353 HZ_EXTRACT_ORGANIZATION_BO_PVT.get_organization_bo(
354 p_init_msg_list => fnd_api.g_false,
355 p_organization_id => l_obj_root_ids(i),
356 p_action_type => l_action_type,
357 x_organization_obj => x_org_cust_objs(i).organization_obj,
358 x_return_status => x_return_status,
359 x_msg_count => x_msg_count,
360 x_msg_data => x_msg_data);
361
362 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
363 RAISE FND_API.G_EXC_ERROR;
364 END IF;
365
366 if L_CHILD_OPERATION_FLAG = 'U'
367 then
368 HZ_EXTRACT_ORGANIZATION_BO_PVT.set_org_bo_action_type(p_event_id =>p_event_id,
369 p_root_id => l_obj_root_ids(i),
370 px_org_obj => x_org_cust_objs(i).organization_obj,
371 x_return_status => x_return_status);
372
373 end if;
374
375 HZ_EXTRACT_CUST_ACCT_BO_PVT.get_cust_acct_bos(
376 p_init_msg_list => fnd_api.g_false,
377 p_parent_id => l_obj_root_ids(i),
378 p_cust_acct_id => NULL,
379 p_action_type => 'CREATED',
380 x_cust_acct_objs => x_org_cust_objs(i).account_objs,
381 x_return_status => x_return_status,
382 x_msg_count => x_msg_count,
383 x_msg_data => x_msg_data);
384
385 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
386 RAISE FND_API.G_EXC_ERROR;
387 END IF;
388 if L_CHILD_OPERATION_FLAG = 'I'
389 then
390 x_org_cust_objs(i).action_type := 'CREATED';
391 else
395 end loop;
392 x_org_cust_objs(i).action_type := 'CHILD_UPDATED';
393 end if;
394
396
397 -- Debug info.
398 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
399 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
400 p_msg_data=>x_msg_data,
401 p_msg_type=>'WARNING',
402 p_msg_level=>fnd_log.level_exception);
403 END IF;
404
405 -- Debug info.
406 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
407 hz_utility_v2pub.debug(p_message=>'get_org_cust_created (-)',
408 p_prefix=>l_debug_prefix,
409 p_msg_level=>fnd_log.level_procedure);
410 END IF;
411
412
413 EXCEPTION
414
415 WHEN fnd_api.g_exc_error THEN
416 x_return_status := fnd_api.g_ret_sts_error;
417
418 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
419 p_count => x_msg_count,
420 p_data => x_msg_data);
421
422 -- Debug info.
423 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
424 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
425 p_msg_data=>x_msg_data,
426 p_msg_type=>'ERROR',
427 p_msg_level=>fnd_log.level_error);
428 END IF;
429 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
430 hz_utility_v2pub.debug(p_message=>'get_org_cust_created(-)',
431 p_prefix=>l_debug_prefix,
432 p_msg_level=>fnd_log.level_procedure);
433 END IF;
434 WHEN fnd_api.g_exc_unexpected_error THEN
435 x_return_status := fnd_api.g_ret_sts_unexp_error;
436
437 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
438 p_count => x_msg_count,
439 p_data => x_msg_data);
440
441 -- Debug info.
442 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
443 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
444 p_msg_data=>x_msg_data,
445 p_msg_type=>'UNEXPECTED ERROR',
446 p_msg_level=>fnd_log.level_error);
447 END IF;
448 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
449 hz_utility_v2pub.debug(p_message=>'get_org_cust_created(-)',
450 p_prefix=>l_debug_prefix,
451 p_msg_level=>fnd_log.level_procedure);
452 END IF;
453 WHEN OTHERS THEN
454 x_return_status := fnd_api.g_ret_sts_unexp_error;
455
456 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
457 fnd_message.set_token('ERROR' ,SQLERRM);
458 fnd_msg_pub.add;
459
460 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
461 p_count => x_msg_count,
462 p_data => x_msg_data);
463
464 -- Debug info.
465 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
466 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
467 p_msg_data=>x_msg_data,
468 p_msg_type=>'SQL ERROR',
469 p_msg_level=>fnd_log.level_error);
470 END IF;
471 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
472 hz_utility_v2pub.debug(p_message=>'get_org_cust_created(-)',
473 p_prefix=>l_debug_prefix,
474 p_msg_level=>fnd_log.level_procedure);
475 END IF;
476
477 end;
478
479
480
481 --------------------------------------
482 --
483 -- PROCEDURE get_org_custs_updated
484 --
485 -- DESCRIPTION
486 --The caller provides an identifier for the Organization Customers update business event and
487 --the procedure returns database objects of the type HZ_ORG_CUST_BO for all of
488 --the Organization Customer business objects from the business event.
489
490 --
491 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
492 --
493 -- ARGUMENTS
494 -- IN:
495 -- p_init_msg_list Initialize message stack if it is set to
496 -- p_event_id BES Event identifier.
497 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
498 -- OUT:
499 -- x_org_cust_objs One or more updated logical org customer.
500 -- x_return_status Return status after the call. The status can
501 -- be fnd_api.g_ret_sts_success (success),
502 -- fnd_api.g_ret_sts_error (error),
503 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
504 -- x_msg_count Number of messages in message stack.
505 -- x_msg_data Message text if x_msg_count is 1.
506 --
507 -- NOTES
508 --
509 -- MODIFICATION HISTORY
510 --
511 -- 10-JUN-2005 AWU Created.
512 --
513
514
515
516 /*
520 The caller provides an identifier for the Organization Customers Update business event and the procedure returns
517 The Get Organization Customers Updated procedure is a service to retrieve all of the Organization Customer business
518 objects whose updates have been captured by the logical business event. Each Organization Customers Updated business
519 event signifies that one or more Organization Customer business objects have been updated.
521 database objects of the type HZ_ORG_CUST_BO for all of the Organization Customer business objects from the business event.
522 Gathering all of the returned database objects from those API calls, the procedure packages them in a table structure
523 and returns them to the caller.
524 */
525
526 PROCEDURE get_org_custs_updated(
527 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
528 p_event_id IN NUMBER,
529 x_org_cust_objs OUT NOCOPY HZ_ORG_CUST_BO_TBL,
530 x_return_status OUT NOCOPY VARCHAR2,
531 x_msg_count OUT NOCOPY NUMBER,
532 x_msg_data OUT NOCOPY VARCHAR2
533 ) is
534
535 l_obj_root_ids HZ_EXTRACT_BO_UTIL_PVT.BO_ID_TBL;
536 l_debug_prefix VARCHAR2(30) := '';
537
538 begin
539
540 -- initialize API return status to success.
541 x_return_status := FND_API.G_RET_STS_SUCCESS;
542
543 -- Initialize message list if p_init_msg_list is set to TRUE
544 IF FND_API.to_Boolean(p_init_msg_list) THEN
545 FND_MSG_PUB.initialize;
546 END IF;
547
548
549 -- Debug info.
550 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
551 hz_utility_v2pub.debug(p_message=>'get_org_custs_updated(+)',
552 p_prefix=>l_debug_prefix,
553 p_msg_level=>fnd_log.level_procedure);
554 END IF;
555
556
557 HZ_EXTRACT_BO_UTIL_PVT.get_bo_root_ids(
558 p_init_msg_list => fnd_api.g_false,
559 p_event_id => p_event_id,
560 x_obj_root_ids => l_obj_root_ids,
561 x_return_status => x_return_status,
562 x_msg_count => x_msg_count,
563 x_msg_data => x_msg_data);
564
565 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
566 RAISE FND_API.G_EXC_ERROR;
567 END IF;
568
569
570
571 x_org_cust_objs := HZ_ORG_CUST_BO_TBL();
572
573 for i in 1..l_obj_root_ids.count loop
574 x_org_cust_objs.extend;
575 get_org_cust_updated(
576 p_init_msg_list => fnd_api.g_false,
577 p_event_id => p_event_id,
578 p_org_cust_id => l_obj_root_ids(i),
579 x_org_cust_obj => x_org_cust_objs(i),
580 x_return_status => x_return_status,
581 x_msg_count => x_msg_count,
582 x_msg_data => x_msg_data);
583
584 end loop;
585
586
587 -- Debug info.
588 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
589 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
590 p_msg_data=>x_msg_data,
591 p_msg_type=>'WARNING',
592 p_msg_level=>fnd_log.level_exception);
593 END IF;
594
595 -- Debug info.
596 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
597 hz_utility_v2pub.debug(p_message=>'get_org_custs_updated (-)',
598 p_prefix=>l_debug_prefix,
599 p_msg_level=>fnd_log.level_procedure);
600 END IF;
601
602
603 EXCEPTION
604
605 WHEN fnd_api.g_exc_error THEN
606 x_return_status := fnd_api.g_ret_sts_error;
607
608 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
609 p_count => x_msg_count,
610 p_data => x_msg_data);
611
612 -- Debug info.
613 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
614 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
615 p_msg_data=>x_msg_data,
616 p_msg_type=>'ERROR',
617 p_msg_level=>fnd_log.level_error);
618 END IF;
619 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
620 hz_utility_v2pub.debug(p_message=>'get_org_custs_updated(-)',
621 p_prefix=>l_debug_prefix,
622 p_msg_level=>fnd_log.level_procedure);
623 END IF;
624 WHEN fnd_api.g_exc_unexpected_error THEN
625 x_return_status := fnd_api.g_ret_sts_unexp_error;
626
627 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
628 p_count => x_msg_count,
629 p_data => x_msg_data);
630
631 -- Debug info.
632 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
633 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
634 p_msg_data=>x_msg_data,
635 p_msg_type=>'UNEXPECTED ERROR',
636 p_msg_level=>fnd_log.level_error);
637 END IF;
638 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
639 hz_utility_v2pub.debug(p_message=>'get_org_custs_updated(-)',
643 WHEN OTHERS THEN
640 p_prefix=>l_debug_prefix,
641 p_msg_level=>fnd_log.level_procedure);
642 END IF;
644 x_return_status := fnd_api.g_ret_sts_unexp_error;
645
646 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
647 fnd_message.set_token('ERROR' ,SQLERRM);
648 fnd_msg_pub.add;
649
650 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
651 p_count => x_msg_count,
652 p_data => x_msg_data);
653
654 -- Debug info.
655 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
656 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
657 p_msg_data=>x_msg_data,
658 p_msg_type=>'SQL ERROR',
659 p_msg_level=>fnd_log.level_error);
660 END IF;
661 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
662 hz_utility_v2pub.debug(p_message=>'get_org_custs_updated(-)',
663 p_prefix=>l_debug_prefix,
664 p_msg_level=>fnd_log.level_procedure);
665 END IF;
666
667 end;
668
669 procedure set_org_acct_bo_action_type(p_node_path IN VARCHAR2,
670 p_child_id IN NUMBER,
671 p_action_type IN VARCHAR2,
672 p_child_entity_name IN VARCHAR2,
673 px_cust_acct_obj IN OUT NOCOPY HZ_CUST_ACCT_BO) is
674 l_child_upd_flag varchar2(1):='N';
675
676 begin
677
678 -- check root level entities
679 if p_child_entity_name = 'HZ_CUST_ACCOUNTS'
680 then
681 if px_cust_acct_obj.cust_acct_id = p_child_id
682 then
683 px_cust_acct_obj.action_type := p_action_type;
684 l_child_upd_flag := 'N';
685 end if;
686 end if;
687
688 -- check first level objs
689
690
691 if p_child_entity_name = 'IBY_FNDCPT_PAYER_ASSGN_INSTR_V' then
692 for i in 1..PX_CUST_ACCT_OBJ.BANK_ACCT_USE_OBJS.COUNT
693 loop
694 if PX_CUST_ACCT_OBJ.BANK_ACCT_USE_OBJS(i).BANK_ACCT_USE_ID = p_child_id
695 then PX_CUST_ACCT_OBJ.BANK_ACCT_USE_OBJS(i).action_type := p_action_type;
696 l_child_upd_flag := 'Y';
697 end if;
698 end loop;
699 elsif p_child_entity_name = 'HZ_CUST_ACCT_RELATE_ALL' then
700 for i in 1..PX_CUST_ACCT_OBJ.ACCT_RELATE_OBJS.COUNT
701 loop
702 if PX_CUST_ACCT_OBJ.ACCT_RELATE_OBJS(i).RELATED_CUST_ACCT_ID = p_child_id
703 then PX_CUST_ACCT_OBJ.ACCT_RELATE_OBJS(i).action_type := p_action_type;
704 l_child_upd_flag := 'Y';
705 end if;
706 end loop;
707 elsif p_child_entity_name = 'RA_CUST_RECEIPT_METHODS' then
708
709 if PX_CUST_ACCT_OBJ.PAYMENT_METHOD_OBJ.PAYMENT_METHOD_ID = p_child_id
710 then PX_CUST_ACCT_OBJ.PAYMENT_METHOD_OBJ.action_type := p_action_type;
711 l_child_upd_flag := 'Y';
712 end if;
713 end if;
714 if px_cust_acct_obj.action_type = 'UNCHANGED' and l_child_upd_flag = 'Y'
715 then
716 px_cust_acct_obj.action_type := 'CHILD_UPDATED';
717 end if;
718
719
720 -- check customer porfile obj
721 if instr(p_node_path, 'ORG_CUST/CUST_ACCT/CUST_PROFILE') > 0
722 then
723 if p_child_entity_name = 'HZ_CUSTOMER_PROFILES'
724 then
725 if PX_CUST_ACCT_OBJ.CUST_PROFILE_OBJ.CUST_ACCT_PROFILE_ID = p_child_id
726 then
727 PX_CUST_ACCT_OBJ.CUST_PROFILE_OBJ.action_type := p_action_type;
728 l_child_upd_flag := 'N';
729 end if;
730 end if;
731
732 if p_child_entity_name = 'HZ_CUST_PROFILE_AMTS'
733 then
734 for i in 1..PX_CUST_ACCT_OBJ.CUST_PROFILE_OBJ.CUST_PROFILE_AMT_OBJS.COUNT
735 loop
736 if PX_CUST_ACCT_OBJ.CUST_PROFILE_OBJ.CUST_PROFILE_AMT_OBJS(i).CUST_ACCT_PROFILE_AMT_ID = p_child_id
737 then PX_CUST_ACCT_OBJ.CUST_PROFILE_OBJ.CUST_PROFILE_AMT_OBJS(i).action_type := p_action_type;
738 l_child_upd_flag := 'Y';
739 end if;
740 end loop;
741 end if;
742
743 if px_cust_acct_obj.CUST_PROFILE_OBJ.action_type = 'UNCHANGED' and l_child_upd_flag = 'Y'
744 then
745 px_cust_acct_obj.CUST_PROFILE_OBJ.action_type := 'CHILD_UPDATED';
746 end if;
747
748 end if;
749
750 -- check account contact obj
751
752 if instr(p_node_path, 'ORG_CUST/CUST_ACCT/CUST_ACCT_CONTACT') > 0
753 then
754 for i in 1..PX_CUST_ACCT_OBJ.CUST_ACCT_CONTACT_OBJS.COUNT
755 loop
756 if p_child_entity_name = 'HZ_CUST_ACCOUNT_ROLES'
757 then
758 if PX_CUST_ACCT_OBJ.CUST_ACCT_CONTACT_OBJS(i).CUST_ACCT_CONTACT_ID = p_child_id
759 then
760 PX_CUST_ACCT_OBJ.CUST_ACCT_CONTACT_OBJS(i).action_type := p_action_type;
761 l_child_upd_flag := 'N';
762 end if;
763 end if;
764
765
766 if p_child_entity_name = 'HZ_ROLE_RESPONSIBILITY'
767 then
768 for j in 1..PX_CUST_ACCT_OBJ.CUST_ACCT_CONTACT_OBJS(i).CONTACT_ROLE_OBJS.COUNT
769 loop
770 if PX_CUST_ACCT_OBJ.CUST_ACCT_CONTACT_OBJS(i).CONTACT_ROLE_OBJS(j).RESPONSIBILITY_ID = p_child_id
771 then PX_CUST_ACCT_OBJ.CUST_ACCT_CONTACT_OBJS(i).CONTACT_ROLE_OBJS(j).action_type := p_action_type;
772 l_child_upd_flag := 'Y';
773 end if;
774 end loop;
775 end if;
779 end if;
776 if PX_CUST_ACCT_OBJ.CUST_ACCT_CONTACT_OBJS(i).action_type = 'UNCHANGED'
777 and l_child_upd_flag = 'Y'
778 then PX_CUST_ACCT_OBJ.CUST_ACCT_CONTACT_OBJS(i).action_type := 'CHILD_UPDATED';
780 end loop;
781
782 end if;
783
784 -- check account site obj
785
786 if instr(p_node_path, 'ORG_CUST/CUST_ACCT/CUST_ACCT_SITE') > 0
787 then
788 for i in 1..PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS.COUNT
789 loop
790 if p_child_entity_name = 'HZ_CUST_ACCT_SITES_ALL'
791 then
792 if PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).cust_acct_site_id = p_child_id
793 then
794 PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).action_type := p_action_type;
795 l_child_upd_flag := 'N';
796 end if;
797 end if;
798
799 for j in 1..PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS.COUNT
800 loop
801 if p_child_entity_name = 'HZ_CUST_SITE_USES_ALL'
802 then
803
804 if PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).SITE_USE_ID = p_child_id
805 then PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).action_type := p_action_type;
806 l_child_upd_flag := 'Y';
807 end if;
808 end if;
809
810 if p_child_entity_name = 'IBY_FNDCPT_PAYER_ASSGN_INSTR_V' then
811 for k in 1..PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).BANK_ACCT_USE_OBJS.COUNT
812 loop
813 if PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).BANK_ACCT_USE_OBJS(k).BANK_ACCT_USE_ID = p_child_id
814 then PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).BANK_ACCT_USE_OBJS(k).action_type := p_action_type;
815 l_child_upd_flag := 'Y';
816 end if;
817 end loop;
818 elsif p_child_entity_name = 'RA_CUST_RECEIPT_METHODS' then
819
820 if PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).PAYMENT_METHOD_OBJ.PAYMENT_METHOD_ID = p_child_id
821 then PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).PAYMENT_METHOD_OBJ.action_type := p_action_type;
822 l_child_upd_flag := 'Y';
823 end if;
824
825 elsif p_child_entity_name = 'HZ_CUSTOMER_PROFILES' then
826
827 if PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).SITE_USE_PROFILE_OBJ.CUST_ACCT_PROFILE_ID = p_child_id
828 then PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).SITE_USE_PROFILE_OBJ.action_type := p_action_type;
829 l_child_upd_flag := 'Y';
830 end if;
831
832 end if;
833
834 end loop; -- CUST_ACCT_SITE_USE_OBJS.COUNT
835
836 for j in 1..PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS.COUNT
837 loop
838
839 if p_child_entity_name = 'HZ_CUST_ACCOUNT_ROLES'
840 then
841 if PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j).CUST_ACCT_CONTACT_ID = p_child_id
842 then
843 PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j).action_type := p_action_type;
844 l_child_upd_flag := 'N';
845 end if;
846 end if;
847
848
849 if p_child_entity_name = 'HZ_ROLE_RESPONSIBILITY'
850 then
851 for k in 1..PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j). CONTACT_ROLE_OBJS.COUNT
852 loop
853 if PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j).CONTACT_ROLE_OBJS(k).RESPONSIBILITY_ID = p_child_id
854 then PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j).CONTACT_ROLE_OBJS(k).action_type := p_action_type;
855 l_child_upd_flag := 'Y';
856 end if;
857 end loop;
858 end if;
859 if PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j).action_type = 'UNCHANGED'
860 and l_child_upd_flag = 'Y'
861 then PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j).action_type := 'CHILD_UPDATED';
862 end if;
863 end loop; -- CUST_ACCT_CONTACT_OBJS.COUNT
864 if PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).action_type = 'UNCHANGED'
865 and l_child_upd_flag = 'Y'
866 then PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).action_type := 'CHILD_UPDATED';
867 end if;
868
869 if PX_CUST_ACCT_OBJ.action_type = 'UNCHANGED' and l_child_upd_flag = 'Y'
870 then PX_CUST_ACCT_OBJ.action_type := 'CHILD_UPDATED';
871 end if;
872
873 end loop; -- acct site obj
874 end if;
875
876 end set_org_acct_bo_action_type;
877
878 procedure set_org_cust_bo_action_type(p_event_id IN NUMBER,
879 p_root_id IN NUMBER,
880 px_org_cust_obj IN OUT NOCOPY HZ_ORG_CUST_BO,
881 x_return_status OUT NOCOPY VARCHAR2) is
882
883 cursor c1 is
884
885 SELECT
886 sys_connect_by_path(CHILD_BO_CODE, '/') node_path,
887 CHILD_OPERATION_FLAG,
888 CHILD_BO_CODE,
889 CHILD_ENTITY_NAME,
890 CHILD_ID,
891 populated_flag
892 FROM HZ_BUS_OBJ_TRACKING
893 where event_id = p_event_id
894 START WITH child_id = p_root_id
895 AND child_entity_name = 'HZ_PARTIES'
896 AND PARENT_BO_CODE IS NULL
897 AND event_id = p_event_id
898 AND CHILD_BO_CODE = 'ORG_CUST' --(or ORG, PERSON_CUST, ORG_CUST).
899 CONNECT BY PARENT_ENTITY_NAME = PRIOR CHILD_ENTITY_NAME
900 AND PARENT_ID = PRIOR CHILD_ID
904 cursor c2 is
901 AND parent_bo_code = PRIOR child_bo_code
902 and event_id = PRIOR event_id;
903
905 select child_event_id,creation_date
906 FROM HZ_BUS_OBJ_TRACKING
907 where event_id = p_event_id
908 and parent_bo_code is null
909 and rownum = 1;
910
911 CURSOR c_get_child_event_id(cp_creation_date DATE) IS
912
913 SELECT event_id
914 FROM HZ_BUS_OBJ_TRACKING
915 WHERE creation_date = cp_creation_date
916 AND child_id = p_root_id
917 AND child_event_id IS NULL
918 and event_id <> p_event_id
919 and rownum = 1;
920
921 L_CHILD_OPERATION_FLAG VARCHAR2(1);
922 L_CHILD_BO_CODE VARCHAR2(30);
923 L_CHILD_ENTITY_NAME VARCHAR2(30);
924 L_CHILD_ID NUMBER;
925 l_action_type varchar2(30);
926 l_node_path varchar2(2000);
927 l_populated_flag varchar2(1);
928 l_child_upd_flag varchar2(1);
929 l_child_event_id number;
930 l_creation_date date;
931
932 begin
933 -- initialize API return status to success.
934 x_return_status := FND_API.G_RET_STS_SUCCESS;
935
936 open c2;
937 fetch c2 into l_child_event_id, l_creation_date;
938 close c2;
939
940 if l_child_event_id is null
941 then
942 OPEN c_get_child_event_id(l_creation_date);
943 FETCH c_get_child_event_id INTO l_child_event_id;
944 close c_get_child_event_id;
945 end if;
946 if l_child_event_id is not null
947 then
948 HZ_EXTRACT_ORGANIZATION_BO_PVT.set_org_bo_action_type(p_event_id => l_child_event_id,
949 p_root_id => p_root_id,
950 px_org_obj =>PX_ORG_CUST_OBJ.ORGANIZATION_OBJ,
951 x_return_status => x_return_status);
952 l_child_upd_flag := 'Y';
953
954 end if;
955
956
957 open c1;
958 loop
959 fetch c1 into L_NODE_PATH, L_CHILD_OPERATION_FLAG,L_CHILD_BO_CODE,
960 L_CHILD_ENTITY_NAME, L_CHILD_ID, l_populated_flag;
961 exit when c1%NOTFOUND;
962
963
964 if l_populated_flag = 'N'
965 then
966 if L_CHILD_OPERATION_FLAG = 'I'
967 then l_action_type := 'CREATED';
968 elsif L_CHILD_OPERATION_FLAG = 'U'
969 then l_action_type := 'UPDATED';
970 end if;
971
972 -- check account objects
973 if instr(l_node_path, 'ORG_CUST/CUST_ACCT') > 0
974 then
975 for i in 1..PX_ORG_CUST_OBJ.ACCOUNT_OBJS.COUNT
976 loop
977 set_org_acct_bo_action_type(p_node_path => l_node_path,
978 p_child_id => l_child_id,
979 p_action_type => l_action_type,
980 p_child_entity_name => l_child_entity_name,
981 px_cust_acct_obj => PX_ORG_CUST_OBJ.ACCOUNT_OBJS(i) );
982 l_child_upd_flag := 'Y';
983 end loop;
984 end if;
985 end if; -- populated_flag = 'N'
986 end loop;
987 close c1;
988 if PX_ORG_CUST_OBJ.action_type = 'UNCHANGED'
989 and l_child_upd_flag = 'Y'
990 then PX_ORG_CUST_OBJ.action_type := 'CHILD_UPDATED';
991 end if;
992
993
994 EXCEPTION
995
996
997 WHEN fnd_api.g_exc_unexpected_error THEN
998 x_return_status := fnd_api.g_ret_sts_unexp_error;
999
1000
1001 WHEN OTHERS THEN
1002 x_return_status := fnd_api.g_ret_sts_unexp_error;
1003
1004 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1005 fnd_message.set_token('ERROR' ,SQLERRM);
1006 fnd_msg_pub.add;
1007
1008 end set_org_cust_bo_action_type;
1009
1010 --------------------------------------
1011 --
1012 -- PROCEDURE get_org_cust_updated
1013 --
1014 -- DESCRIPTION
1015 --The caller provides an identifier for the Organization customer update business event and organization id
1016 --the procedure returns one database object of the type HZ_ORG_CUST_BO
1017 --
1018 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1019 --
1020 -- ARGUMENTS
1021 -- IN:
1022 -- p_init_msg_list Initialize message stack if it is set to
1023 -- p_event_id BES Event identifier.
1024 -- p_org_cust_id Organization customer identifier.
1025 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
1026 -- OUT:
1027 -- x_org_cust_obj One updated logical organization.
1028 -- x_return_status Return status after the call. The status can
1029 -- be fnd_api.g_ret_sts_success (success),
1030 -- fnd_api.g_ret_sts_error (error),
1031 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1032 -- x_msg_count Number of messages in message stack.
1033 -- x_msg_data Message text if x_msg_count is 1.
1034 --
1035 -- NOTES
1036 --
1037 -- MODIFICATION HISTORY
1038 --
1039 -- 06-JUN-2005 AWU Created.
1040 --
1041
1042
1043 PROCEDURE get_org_cust_updated(
1044 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1045 p_event_id IN NUMBER,
1046 p_org_cust_id IN NUMBER,
1047 x_org_cust_obj OUT NOCOPY HZ_ORG_CUST_BO,
1048 x_return_status OUT NOCOPY VARCHAR2,
1049 x_msg_count OUT NOCOPY NUMBER,
1050 x_msg_data OUT NOCOPY VARCHAR2
1054 l_org_cust_obj HZ_ORG_CUST_BO;
1051 ) is
1052 l_obj_root_ids HZ_EXTRACT_BO_UTIL_PVT.BO_ID_TBL;
1053 l_debug_prefix VARCHAR2(30) := '';
1055 begin
1056
1057 -- initialize API return status to success.
1058 x_return_status := FND_API.G_RET_STS_SUCCESS;
1059
1060 -- Initialize message list if p_init_msg_list is set to TRUE
1061 IF FND_API.to_Boolean(p_init_msg_list) THEN
1062 FND_MSG_PUB.initialize;
1063 END IF;
1064
1065
1066 -- Debug info.
1067 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1068 hz_utility_v2pub.debug(p_message=>'get_org_cust_updated(+)',
1069 p_prefix=>l_debug_prefix,
1070 p_msg_level=>fnd_log.level_procedure);
1071 END IF;
1072
1073 /* moved to public api
1074 HZ_EXTRACT_BO_UTIL_PVT.validate_event_id(p_event_id => p_event_id,
1075 p_party_id => p_org_cust_id,
1076 x_return_status => x_return_status);
1077
1078 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1079 RAISE FND_API.G_EXC_ERROR;
1080 END IF;
1081 */
1082 -- Set action type to 'UNCHANGED' by default
1083
1084 get_org_cust_bo(
1085 p_init_msg_list => fnd_api.g_false,
1086 p_organization_id => p_org_cust_id,
1087 p_action_type => 'UNCHANGED',
1088 x_org_cust_obj => x_org_cust_obj,
1089 x_return_status => x_return_status,
1090 x_msg_count => x_msg_count,
1091 x_msg_data => x_msg_data);
1092
1093
1094 -- Based on BOT, for updated branch, set action_type = 'UPDATED'/'CREATED'
1095
1096
1097 l_org_cust_obj := x_org_cust_obj;
1098 set_org_cust_bo_action_type(p_event_id => p_event_id,
1099 p_root_id => p_org_cust_id,
1100 px_org_cust_obj => l_org_cust_obj,
1101 x_return_status => x_return_status
1102 );
1103 x_org_cust_obj := l_org_cust_obj;
1104
1105
1106 -- Debug info.
1107 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1108 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1109 p_msg_data=>x_msg_data,
1110 p_msg_type=>'WARNING',
1111 p_msg_level=>fnd_log.level_exception);
1112 END IF;
1113
1114 -- Debug info.
1115 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1116 hz_utility_v2pub.debug(p_message=>'get_org_cust_updated (-)',
1117 p_prefix=>l_debug_prefix,
1118 p_msg_level=>fnd_log.level_procedure);
1119 END IF;
1120
1121
1122 EXCEPTION
1123
1124 WHEN fnd_api.g_exc_error THEN
1125 x_return_status := fnd_api.g_ret_sts_error;
1126
1127 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1128 p_count => x_msg_count,
1129 p_data => x_msg_data);
1130
1131 -- Debug info.
1132 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1133 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1134 p_msg_data=>x_msg_data,
1135 p_msg_type=>'ERROR',
1136 p_msg_level=>fnd_log.level_error);
1137 END IF;
1138 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1139 hz_utility_v2pub.debug(p_message=>'get_org_cust_updated(-)',
1140 p_prefix=>l_debug_prefix,
1141 p_msg_level=>fnd_log.level_procedure);
1142 END IF;
1143 WHEN fnd_api.g_exc_unexpected_error THEN
1144 x_return_status := fnd_api.g_ret_sts_unexp_error;
1145
1146 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1147 p_count => x_msg_count,
1148 p_data => x_msg_data);
1149
1150 -- Debug info.
1151 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1152 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1153 p_msg_data=>x_msg_data,
1154 p_msg_type=>'UNEXPECTED ERROR',
1155 p_msg_level=>fnd_log.level_error);
1156 END IF;
1157 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1158 hz_utility_v2pub.debug(p_message=>'get_org_cust_updated(-)',
1159 p_prefix=>l_debug_prefix,
1160 p_msg_level=>fnd_log.level_procedure);
1161 END IF;
1162 WHEN OTHERS THEN
1163 x_return_status := fnd_api.g_ret_sts_unexp_error;
1164
1165 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1166 fnd_message.set_token('ERROR' ,SQLERRM);
1167 fnd_msg_pub.add;
1168
1169 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1170 p_count => x_msg_count,
1171 p_data => x_msg_data);
1172
1173 -- Debug info.
1174 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1175 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1176 p_msg_data=>x_msg_data,
1177 p_msg_type=>'SQL ERROR',
1181 hz_utility_v2pub.debug(p_message=>'get_org_cust_updated(-)',
1178 p_msg_level=>fnd_log.level_error);
1179 END IF;
1180 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1182 p_prefix=>l_debug_prefix,
1183 p_msg_level=>fnd_log.level_procedure);
1184 END IF;
1185
1186 end;
1187
1188 --------------------------------------
1189 --
1190 -- PROCEDURE get_org_cust_v2_bo
1191 --
1192 -- DESCRIPTION
1193 -- Get a logical organization customer.
1194 --
1195 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1196 --
1197 -- ARGUMENTS
1198 -- IN:
1199 -- p_init_msg_list Initialize message stack if it is set to
1200 -- p_organization_id Organization ID.
1201 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
1202 -- OUT:
1203 -- x_org_cust_v2_obj Logical organization customer record.
1204 -- x_return_status Return status after the call. The status can
1205 -- be fnd_api.g_ret_sts_success (success),
1206 -- fnd_api.g_ret_sts_error (error),
1207 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1208 -- x_msg_count Number of messages in message stack.
1209 -- x_msg_data Message text if x_msg_count is 1.
1210 --
1211 -- NOTES
1212 --
1213 -- MODIFICATION HISTORY
1214 --
1215 --
1216 -- 04-FEB-2008 vsegu Created.
1217 --
1218
1219 /*
1220 The Get Organization Customer API Procedure is a retrieval service that returns a full Organization Customer business object.
1221 The user identifies a particular Organization Customer business object using the TCA identifier and/or
1222 the object Source System information. Upon proper validation of the object,
1223 the full Organization Customer business object is returned. The object consists of all data included within
1224 the Organization Customer business object, at all embedded levels. This includes the set of all data stored
1225 in the TCA tables for each embedded entity.
1226
1227 To retrieve the appropriate embedded business objects within the Organization Customer business object,
1228 the Get procedure calls the equivalent procedure for the following embedded objects:
1229
1230 Embedded BO Mandatory Multiple Logical API Procedure Comments
1231
1232 Organization Y N get_organization_bo
1233 Customer Account Y Y get_cust_acct_bo Called for each Customer Account object for the Organization Customer
1234
1235 */
1236
1237
1238
1239 PROCEDURE get_org_cust_v2_bo(
1240 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1241 p_organization_id IN NUMBER,
1242 p_action_type IN VARCHAR2 := NULL,
1243 x_org_cust_v2_obj OUT NOCOPY HZ_ORG_CUST_V2_BO,
1244 x_return_status OUT NOCOPY VARCHAR2,
1245 x_msg_count OUT NOCOPY NUMBER,
1246 x_msg_data OUT NOCOPY VARCHAR2
1247 ) is
1248 l_debug_prefix VARCHAR2(30) := '';
1249
1250 begin
1251 -- initialize API return status to success.
1252 x_return_status := FND_API.G_RET_STS_SUCCESS;
1253
1254 -- Initialize message list if p_init_msg_list is set to TRUE
1255 IF FND_API.to_Boolean(p_init_msg_list) THEN
1256 FND_MSG_PUB.initialize;
1257 END IF;
1258
1259
1260 -- Debug info.
1261 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1262 hz_utility_v2pub.debug(p_message=>'get_org_cust_v2_bo(+)',
1263 p_prefix=>l_debug_prefix,
1264 p_msg_level=>fnd_log.level_procedure);
1265 END IF;
1266
1267 x_org_cust_v2_obj := HZ_ORG_CUST_V2_BO(p_action_type, NULL, NULL);
1268
1269 HZ_EXTRACT_ORGANIZATION_BO_PVT.get_organization_bo(
1270 p_init_msg_list => fnd_api.g_false,
1271 p_organization_id => p_organization_id,
1272 p_action_type => p_action_type,
1273 x_organization_obj => x_org_cust_v2_obj.organization_obj,
1274 x_return_status => x_return_status,
1275 x_msg_count => x_msg_count,
1276 x_msg_data => x_msg_data);
1277
1278 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1279 RAISE FND_API.G_EXC_ERROR;
1280 END IF;
1281
1282
1283 HZ_EXTRACT_CUST_ACCT_BO_PVT.get_cust_acct_v2_bos(
1284 p_init_msg_list => fnd_api.g_false,
1285 p_parent_id => p_organization_id,
1286 p_cust_acct_id => NULL,
1287 p_action_type => p_action_type,
1288 x_cust_acct_v2_objs => x_org_cust_v2_obj.account_objs,
1289 x_return_status => x_return_status,
1290 x_msg_count => x_msg_count,
1291 x_msg_data => x_msg_data);
1292
1293 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1294 RAISE FND_API.G_EXC_ERROR;
1295 END IF;
1296
1297
1298 -- Debug info.
1299 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1300 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1301 p_msg_data=>x_msg_data,
1302 p_msg_type=>'WARNING',
1303 p_msg_level=>fnd_log.level_exception);
1304 END IF;
1305
1309 p_prefix=>l_debug_prefix,
1306 -- Debug info.
1307 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1308 hz_utility_v2pub.debug(p_message=>'get_org_cust_v2_bo (-)',
1310 p_msg_level=>fnd_log.level_procedure);
1311 END IF;
1312
1313
1314 EXCEPTION
1315
1316 WHEN fnd_api.g_exc_error THEN
1317 x_return_status := fnd_api.g_ret_sts_error;
1318
1319 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1320 p_count => x_msg_count,
1321 p_data => x_msg_data);
1322
1323 -- Debug info.
1324 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1325 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1326 p_msg_data=>x_msg_data,
1327 p_msg_type=>'ERROR',
1328 p_msg_level=>fnd_log.level_error);
1329 END IF;
1330 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1331 hz_utility_v2pub.debug(p_message=>'get_org_cust_v2_bo (-)',
1332 p_prefix=>l_debug_prefix,
1333 p_msg_level=>fnd_log.level_procedure);
1334 END IF;
1335 WHEN fnd_api.g_exc_unexpected_error THEN
1336 x_return_status := fnd_api.g_ret_sts_unexp_error;
1337
1338 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1339 p_count => x_msg_count,
1340 p_data => x_msg_data);
1341
1342 -- Debug info.
1343 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1344 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1345 p_msg_data=>x_msg_data,
1346 p_msg_type=>'UNEXPECTED ERROR',
1347 p_msg_level=>fnd_log.level_error);
1348 END IF;
1349 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1350 hz_utility_v2pub.debug(p_message=>'get_org_cust_v2_bo (-)',
1351 p_prefix=>l_debug_prefix,
1352 p_msg_level=>fnd_log.level_procedure);
1353 END IF;
1354 WHEN OTHERS THEN
1355 x_return_status := fnd_api.g_ret_sts_unexp_error;
1356
1357 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1358 fnd_message.set_token('ERROR' ,SQLERRM);
1359 fnd_msg_pub.add;
1360
1361 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1362 p_count => x_msg_count,
1363 p_data => x_msg_data);
1364
1365 -- Debug info.
1366 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1367 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1368 p_msg_data=>x_msg_data,
1369 p_msg_type=>'SQL ERROR',
1370 p_msg_level=>fnd_log.level_error);
1371 END IF;
1372 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1373 hz_utility_v2pub.debug(p_message=>'get_org_cust_v2_bo (-)',
1374 p_prefix=>l_debug_prefix,
1375 p_msg_level=>fnd_log.level_procedure);
1376 END IF;
1377
1378 end;
1379
1380 --------------------------------------
1381 --
1382 -- PROCEDURE get_v2_org_custs_created
1383 --
1384 -- DESCRIPTION
1385 --The caller provides an identifier for the Organization Customers created business event and
1386 --the procedure returns database objects of the type HZ_ORG CUSTOMER_BO for all of
1387 --the Organization Customer business objects from the business event.
1388
1389 --
1390 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1391 --
1392 -- ARGUMENTS
1393 -- IN:
1394 -- p_init_msg_list Initialize message stack if it is set to
1395 -- p_event_id BES Event identifier.
1396 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
1397 -- OUT:
1398 -- x_org_cust_v2_objs One or more created logical organization customer.
1399 -- x_return_status Return status after the call. The status can
1400 -- be fnd_api.g_ret_sts_success (success),
1401 -- fnd_api.g_ret_sts_error (error),
1402 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1403 -- x_msg_count Number of messages in message stack.
1404 -- x_msg_data Message text if x_msg_count is 1.
1405 --
1406 -- NOTES
1407 --
1408 -- MODIFICATION HISTORY
1409 --
1410 -- 04-FEB-2008 vsegu Created.
1411 --
1412
1413
1414
1415 /*
1416 The Get organization customers Created procedure is a service to retrieve all of the Organization Customer business objects
1417 whose creations have been captured by a logical business event. Each Organization Customers Created
1418 business event signifies that one or more Organization Customer business objects have been created.
1419 The caller provides an identifier for the Organization Customers Created business event and the procedure
1420 returns all of the Organization Customer business objects from the business event. For each business object
1421 creation captured in the business event, the procedure calls the generic Get operation:
1425 them in a table structure and returns them to the caller.
1422 HZ_ORG_BO_PVT.get_org_bo
1423
1424 Gathering all of the returned business objects from those API calls, the procedure packages
1426 */
1427
1428
1429 PROCEDURE get_v2_org_custs_created(
1430 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1431 p_event_id IN NUMBER,
1432 x_org_cust_v2_objs OUT NOCOPY HZ_ORG_CUST_V2_BO_TBL,
1433 x_return_status OUT NOCOPY VARCHAR2,
1434 x_msg_count OUT NOCOPY NUMBER,
1435 x_msg_data OUT NOCOPY VARCHAR2
1436 ) is
1437
1438 l_obj_root_ids HZ_EXTRACT_BO_UTIL_PVT.BO_ID_TBL;
1439 l_debug_prefix VARCHAR2(30) := '';
1440 L_CHILD_OPERATION_FLAG varchar2(1);
1441 l_action_type varchar2(30);
1442
1443 begin
1444
1445 -- initialize API return status to success.
1446 x_return_status := FND_API.G_RET_STS_SUCCESS;
1447
1448 -- Initialize message list if p_init_msg_list is set to TRUE
1449 IF FND_API.to_Boolean(p_init_msg_list) THEN
1450 FND_MSG_PUB.initialize;
1451 END IF;
1452
1453
1454 -- Debug info.
1455 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1456 hz_utility_v2pub.debug(p_message=>'get_v2_org_cust_created(+)',
1457 p_prefix=>l_debug_prefix,
1458 p_msg_level=>fnd_log.level_procedure);
1459 END IF;
1460
1461
1462 HZ_EXTRACT_BO_UTIL_PVT.get_bo_root_ids(
1463 p_init_msg_list => fnd_api.g_false,
1464 p_event_id => p_event_id,
1465 x_obj_root_ids => l_obj_root_ids,
1466 x_return_status => x_return_status,
1467 x_msg_count => x_msg_count,
1468 x_msg_data => x_msg_data);
1469
1470 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1471 RAISE FND_API.G_EXC_ERROR;
1472 END IF;
1473
1474 l_child_operation_flag:= get_org_operation_type(p_event_id);
1475
1476 if L_CHILD_OPERATION_FLAG = 'I'
1477 then
1478 l_action_type := 'CREATED';
1479 else
1480 l_action_type := 'UNCHANGED'; -- default to unchanged.
1481 end if;
1482
1483
1484 -- call event API get_org_cust_updated for each id.
1485
1486 x_org_cust_v2_objs := HZ_ORG_CUST_V2_BO_TBL();
1487
1488
1489 for i in 1..l_obj_root_ids.count loop
1490
1491 x_org_cust_v2_objs.extend;
1492
1493 x_org_cust_v2_objs(i) := HZ_ORG_CUST_V2_BO('UNCHANGED', NULL, NULL);
1494
1495 HZ_EXTRACT_ORGANIZATION_BO_PVT.get_organization_bo(
1496 p_init_msg_list => fnd_api.g_false,
1497 p_organization_id => l_obj_root_ids(i),
1498 p_action_type => l_action_type,
1499 x_organization_obj => x_org_cust_v2_objs(i).organization_obj,
1500 x_return_status => x_return_status,
1501 x_msg_count => x_msg_count,
1502 x_msg_data => x_msg_data);
1503
1504 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1505 RAISE FND_API.G_EXC_ERROR;
1506 END IF;
1507
1508 if L_CHILD_OPERATION_FLAG = 'U'
1509 then
1510 HZ_EXTRACT_ORGANIZATION_BO_PVT.set_org_bo_action_type(p_event_id =>p_event_id,
1511 p_root_id => l_obj_root_ids(i),
1512 px_org_obj => x_org_cust_v2_objs(i).organization_obj,
1513 x_return_status => x_return_status);
1514
1515 end if;
1516
1517 HZ_EXTRACT_CUST_ACCT_BO_PVT.get_cust_acct_v2_bos(
1518 p_init_msg_list => fnd_api.g_false,
1519 p_parent_id => l_obj_root_ids(i),
1520 p_cust_acct_id => NULL,
1521 p_action_type => 'CREATED',
1522 x_cust_acct_v2_objs => x_org_cust_v2_objs(i).account_objs,
1523 x_return_status => x_return_status,
1524 x_msg_count => x_msg_count,
1525 x_msg_data => x_msg_data);
1526
1527 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1528 RAISE FND_API.G_EXC_ERROR;
1529 END IF;
1530 if L_CHILD_OPERATION_FLAG = 'I'
1531 then
1532 x_org_cust_v2_objs(i).action_type := 'CREATED';
1533 else
1534 x_org_cust_v2_objs(i).action_type := 'CHILD_UPDATED';
1535 end if;
1536
1537 end loop;
1538
1539 -- Debug info.
1540 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1541 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1542 p_msg_data=>x_msg_data,
1543 p_msg_type=>'WARNING',
1544 p_msg_level=>fnd_log.level_exception);
1545 END IF;
1546
1547 -- Debug info.
1548 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1549 hz_utility_v2pub.debug(p_message=>'get_v2_org_cust_created (-)',
1550 p_prefix=>l_debug_prefix,
1551 p_msg_level=>fnd_log.level_procedure);
1552 END IF;
1553
1554
1555 EXCEPTION
1556
1557 WHEN fnd_api.g_exc_error THEN
1558 x_return_status := fnd_api.g_ret_sts_error;
1559
1560 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1561 p_count => x_msg_count,
1562 p_data => x_msg_data);
1563
1564 -- Debug info.
1565 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1569 p_msg_level=>fnd_log.level_error);
1566 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1567 p_msg_data=>x_msg_data,
1568 p_msg_type=>'ERROR',
1570 END IF;
1571 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1572 hz_utility_v2pub.debug(p_message=>'get_v2_org_cust_created(-)',
1573 p_prefix=>l_debug_prefix,
1574 p_msg_level=>fnd_log.level_procedure);
1575 END IF;
1576 WHEN fnd_api.g_exc_unexpected_error THEN
1577 x_return_status := fnd_api.g_ret_sts_unexp_error;
1578
1579 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1580 p_count => x_msg_count,
1581 p_data => x_msg_data);
1582
1583 -- Debug info.
1584 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1585 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1586 p_msg_data=>x_msg_data,
1587 p_msg_type=>'UNEXPECTED ERROR',
1588 p_msg_level=>fnd_log.level_error);
1589 END IF;
1590 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1591 hz_utility_v2pub.debug(p_message=>'get_v2_org_cust_created(-)',
1592 p_prefix=>l_debug_prefix,
1593 p_msg_level=>fnd_log.level_procedure);
1594 END IF;
1595 WHEN OTHERS THEN
1596 x_return_status := fnd_api.g_ret_sts_unexp_error;
1597
1598 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1599 fnd_message.set_token('ERROR' ,SQLERRM);
1600 fnd_msg_pub.add;
1601
1602 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1603 p_count => x_msg_count,
1604 p_data => x_msg_data);
1605
1606 -- Debug info.
1607 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1608 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1609 p_msg_data=>x_msg_data,
1610 p_msg_type=>'SQL ERROR',
1611 p_msg_level=>fnd_log.level_error);
1612 END IF;
1613 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1614 hz_utility_v2pub.debug(p_message=>'get_v2_org_cust_created(-)',
1615 p_prefix=>l_debug_prefix,
1616 p_msg_level=>fnd_log.level_procedure);
1617 END IF;
1618
1619 end;
1620
1621
1622
1623 --------------------------------------
1624 --
1625 -- PROCEDURE get_v2_org_custs_updated
1626 --
1627 -- DESCRIPTION
1628 --The caller provides an identifier for the Organization Customers update business event and
1629 --the procedure returns database objects of the type HZ_ORG_CUST_V2_BO for all of
1630 --the Organization Customer business objects from the business event.
1631
1632 --
1633 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1634 --
1635 -- ARGUMENTS
1636 -- IN:
1637 -- p_init_msg_list Initialize message stack if it is set to
1638 -- p_event_id BES Event identifier.
1639 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
1640 -- OUT:
1641 -- x_org_cust_v2_objs One or more updated logical org customer.
1642 -- x_return_status Return status after the call. The status can
1643 -- be fnd_api.g_ret_sts_success (success),
1644 -- fnd_api.g_ret_sts_error (error),
1645 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1646 -- x_msg_count Number of messages in message stack.
1647 -- x_msg_data Message text if x_msg_count is 1.
1648 --
1649 -- NOTES
1650 --
1651 -- MODIFICATION HISTORY
1652 --
1653 -- 04-FEB-2008 vsegu Created.
1654 --
1655
1656
1657
1658 /*
1659 The Get Organization Customers Updated procedure is a service to retrieve all of the Organization Customer business
1660 objects whose updates have been captured by the logical business event. Each Organization Customers Updated business
1661 event signifies that one or more Organization Customer business objects have been updated.
1662 The caller provides an identifier for the Organization Customers Update business event and the procedure returns
1663 database objects of the type HZ_ORG_CUST_V2_BO for all of the Organization Customer business objects from the business event.
1664 Gathering all of the returned database objects from those API calls, the procedure packages them in a table structure
1665 and returns them to the caller.
1666 */
1667
1668 PROCEDURE get_v2_org_custs_updated(
1669 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1670 p_event_id IN NUMBER,
1671 x_org_cust_v2_objs OUT NOCOPY HZ_ORG_CUST_V2_BO_TBL,
1672 x_return_status OUT NOCOPY VARCHAR2,
1673 x_msg_count OUT NOCOPY NUMBER,
1674 x_msg_data OUT NOCOPY VARCHAR2
1675 ) is
1676
1677 l_obj_root_ids HZ_EXTRACT_BO_UTIL_PVT.BO_ID_TBL;
1678 l_debug_prefix VARCHAR2(30) := '';
1679
1680 begin
1681
1682 -- initialize API return status to success.
1686 IF FND_API.to_Boolean(p_init_msg_list) THEN
1683 x_return_status := FND_API.G_RET_STS_SUCCESS;
1684
1685 -- Initialize message list if p_init_msg_list is set to TRUE
1687 FND_MSG_PUB.initialize;
1688 END IF;
1689
1690
1691 -- Debug info.
1692 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1693 hz_utility_v2pub.debug(p_message=>'get_v2_org_custs_updated(+)',
1694 p_prefix=>l_debug_prefix,
1695 p_msg_level=>fnd_log.level_procedure);
1696 END IF;
1697
1698
1699 HZ_EXTRACT_BO_UTIL_PVT.get_bo_root_ids(
1700 p_init_msg_list => fnd_api.g_false,
1701 p_event_id => p_event_id,
1702 x_obj_root_ids => l_obj_root_ids,
1703 x_return_status => x_return_status,
1704 x_msg_count => x_msg_count,
1705 x_msg_data => x_msg_data);
1706
1707 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1708 RAISE FND_API.G_EXC_ERROR;
1709 END IF;
1710
1711
1712
1713 x_org_cust_v2_objs := HZ_ORG_CUST_V2_BO_TBL();
1714
1715 for i in 1..l_obj_root_ids.count loop
1716 x_org_cust_v2_objs.extend;
1717 get_v2_org_cust_updated(
1718 p_init_msg_list => fnd_api.g_false,
1719 p_event_id => p_event_id,
1720 p_org_cust_id => l_obj_root_ids(i),
1721 x_org_cust_v2_obj => x_org_cust_v2_objs(i),
1722 x_return_status => x_return_status,
1723 x_msg_count => x_msg_count,
1724 x_msg_data => x_msg_data);
1725
1726 end loop;
1727
1728
1729 -- Debug info.
1730 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1731 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1732 p_msg_data=>x_msg_data,
1733 p_msg_type=>'WARNING',
1734 p_msg_level=>fnd_log.level_exception);
1735 END IF;
1736
1737 -- Debug info.
1738 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1739 hz_utility_v2pub.debug(p_message=>'get_v2_org_custs_updated (-)',
1740 p_prefix=>l_debug_prefix,
1741 p_msg_level=>fnd_log.level_procedure);
1742 END IF;
1743
1744
1745 EXCEPTION
1746
1747 WHEN fnd_api.g_exc_error THEN
1748 x_return_status := fnd_api.g_ret_sts_error;
1749
1750 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1751 p_count => x_msg_count,
1752 p_data => x_msg_data);
1753
1754 -- Debug info.
1755 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1756 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1757 p_msg_data=>x_msg_data,
1758 p_msg_type=>'ERROR',
1759 p_msg_level=>fnd_log.level_error);
1760 END IF;
1761 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1762 hz_utility_v2pub.debug(p_message=>'get_v2_org_custs_updated(-)',
1763 p_prefix=>l_debug_prefix,
1764 p_msg_level=>fnd_log.level_procedure);
1765 END IF;
1766 WHEN fnd_api.g_exc_unexpected_error THEN
1767 x_return_status := fnd_api.g_ret_sts_unexp_error;
1768
1769 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1770 p_count => x_msg_count,
1771 p_data => x_msg_data);
1772
1773 -- Debug info.
1774 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1775 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1776 p_msg_data=>x_msg_data,
1777 p_msg_type=>'UNEXPECTED ERROR',
1778 p_msg_level=>fnd_log.level_error);
1779 END IF;
1780 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1781 hz_utility_v2pub.debug(p_message=>'get_v2_org_custs_updated(-)',
1782 p_prefix=>l_debug_prefix,
1783 p_msg_level=>fnd_log.level_procedure);
1784 END IF;
1785 WHEN OTHERS THEN
1786 x_return_status := fnd_api.g_ret_sts_unexp_error;
1787
1788 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1789 fnd_message.set_token('ERROR' ,SQLERRM);
1790 fnd_msg_pub.add;
1791
1792 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1793 p_count => x_msg_count,
1794 p_data => x_msg_data);
1795
1796 -- Debug info.
1797 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1798 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1799 p_msg_data=>x_msg_data,
1800 p_msg_type=>'SQL ERROR',
1801 p_msg_level=>fnd_log.level_error);
1802 END IF;
1803 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1804 hz_utility_v2pub.debug(p_message=>'get_v2_org_custs_updated(-)',
1805 p_prefix=>l_debug_prefix,
1806 p_msg_level=>fnd_log.level_procedure);
1807 END IF;
1808
1809 end;
1810
1814 p_child_entity_name IN VARCHAR2,
1811 procedure set_v2_org_acct_bo_action_type(p_node_path IN VARCHAR2,
1812 p_child_id IN NUMBER,
1813 p_action_type IN VARCHAR2,
1815 px_cust_acct_v2_obj IN OUT NOCOPY HZ_CUST_ACCT_V2_BO) is
1816 l_child_upd_flag varchar2(1):='N';
1817
1818 begin
1819
1820 -- check root level entities
1821 if p_child_entity_name = 'HZ_CUST_ACCOUNTS'
1822 then
1823 if px_cust_acct_v2_obj.cust_acct_id = p_child_id
1824 then
1825 px_cust_acct_v2_obj.action_type := p_action_type;
1826 l_child_upd_flag := 'N';
1827 end if;
1828 end if;
1829
1830 -- check first level objs
1831
1832
1833 if p_child_entity_name = 'IBY_FNDCPT_PAYER_ASSGN_INSTR_V' then
1834 for i in 1..px_cust_acct_v2_obj.BANK_ACCT_USE_OBJS.COUNT
1835 loop
1836 if px_cust_acct_v2_obj.BANK_ACCT_USE_OBJS(i).BANK_ACCT_USE_ID = p_child_id
1837 then px_cust_acct_v2_obj.BANK_ACCT_USE_OBJS(i).action_type := p_action_type;
1838 l_child_upd_flag := 'Y';
1839 end if;
1840 end loop;
1841 elsif p_child_entity_name = 'HZ_CUST_ACCT_RELATE_ALL' then
1842 for i in 1..px_cust_acct_v2_obj.ACCT_RELATE_OBJS.COUNT
1843 loop
1844 if px_cust_acct_v2_obj.ACCT_RELATE_OBJS(i).RELATED_CUST_ACCT_ID = p_child_id
1845 then px_cust_acct_v2_obj.ACCT_RELATE_OBJS(i).action_type := p_action_type;
1846 l_child_upd_flag := 'Y';
1847 end if;
1848 end loop;
1849 elsif p_child_entity_name = 'RA_CUST_RECEIPT_METHODS' then
1850 for i in 1..px_cust_acct_v2_obj.PAYMENT_METHOD_OBJS.COUNT
1851 loop
1852 if px_cust_acct_v2_obj.PAYMENT_METHOD_OBJS(i).PAYMENT_METHOD_ID = p_child_id
1853 then px_cust_acct_v2_obj.PAYMENT_METHOD_OBJS(i).action_type := p_action_type;
1854 l_child_upd_flag := 'Y';
1855 end if;
1856 end loop;
1857 end if;
1858 if px_cust_acct_v2_obj.action_type = 'UNCHANGED' and l_child_upd_flag = 'Y'
1859 then
1860 px_cust_acct_v2_obj.action_type := 'CHILD_UPDATED';
1861 end if;
1862
1863
1864 -- check customer porfile obj
1865 if instr(p_node_path, 'ORG_CUST/CUST_ACCT/CUST_PROFILE') > 0
1866 then
1867 if p_child_entity_name = 'HZ_CUSTOMER_PROFILES'
1868 then
1869 if px_cust_acct_v2_obj.CUST_PROFILE_OBJ.CUST_ACCT_PROFILE_ID = p_child_id
1870 then
1871 px_cust_acct_v2_obj.CUST_PROFILE_OBJ.action_type := p_action_type;
1872 l_child_upd_flag := 'N';
1873 end if;
1874 end if;
1875
1876 if p_child_entity_name = 'HZ_CUST_PROFILE_AMTS'
1877 then
1878 for i in 1..px_cust_acct_v2_obj.CUST_PROFILE_OBJ.CUST_PROFILE_AMT_OBJS.COUNT
1879 loop
1880 if px_cust_acct_v2_obj.CUST_PROFILE_OBJ.CUST_PROFILE_AMT_OBJS(i).CUST_ACCT_PROFILE_AMT_ID = p_child_id
1881 then px_cust_acct_v2_obj.CUST_PROFILE_OBJ.CUST_PROFILE_AMT_OBJS(i).action_type := p_action_type;
1882 l_child_upd_flag := 'Y';
1883 end if;
1884 end loop;
1885 end if;
1886
1887 if px_cust_acct_v2_obj.CUST_PROFILE_OBJ.action_type = 'UNCHANGED' and l_child_upd_flag = 'Y'
1888 then
1889 px_cust_acct_v2_obj.CUST_PROFILE_OBJ.action_type := 'CHILD_UPDATED';
1890 end if;
1891
1892 end if;
1893
1894 -- check account contact obj
1895
1896 if instr(p_node_path, 'ORG_CUST/CUST_ACCT/CUST_ACCT_CONTACT') > 0
1897 then
1898 for i in 1..px_cust_acct_v2_obj.CUST_ACCT_CONTACT_OBJS.COUNT
1899 loop
1900 if p_child_entity_name = 'HZ_CUST_ACCOUNT_ROLES'
1901 then
1902 if px_cust_acct_v2_obj.CUST_ACCT_CONTACT_OBJS(i).CUST_ACCT_CONTACT_ID = p_child_id
1903 then
1904 px_cust_acct_v2_obj.CUST_ACCT_CONTACT_OBJS(i).action_type := p_action_type;
1905 l_child_upd_flag := 'N';
1906 end if;
1907 end if;
1908
1909
1910 if p_child_entity_name = 'HZ_ROLE_RESPONSIBILITY'
1911 then
1912 for j in 1..px_cust_acct_v2_obj.CUST_ACCT_CONTACT_OBJS(i).CONTACT_ROLE_OBJS.COUNT
1913 loop
1914 if px_cust_acct_v2_obj.CUST_ACCT_CONTACT_OBJS(i).CONTACT_ROLE_OBJS(j).RESPONSIBILITY_ID = p_child_id
1915 then px_cust_acct_v2_obj.CUST_ACCT_CONTACT_OBJS(i).CONTACT_ROLE_OBJS(j).action_type := p_action_type;
1916 l_child_upd_flag := 'Y';
1917 end if;
1918 end loop;
1919 end if;
1920 if px_cust_acct_v2_obj.CUST_ACCT_CONTACT_OBJS(i).action_type = 'UNCHANGED'
1921 and l_child_upd_flag = 'Y'
1922 then px_cust_acct_v2_obj.CUST_ACCT_CONTACT_OBJS(i).action_type := 'CHILD_UPDATED';
1923 end if;
1924 end loop;
1925
1926 end if;
1927
1928 -- check account site obj
1929
1930 if instr(p_node_path, 'ORG_CUST/CUST_ACCT/CUST_ACCT_SITE') > 0
1931 then
1932 for i in 1..px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS.COUNT
1933 loop
1934 if p_child_entity_name = 'HZ_CUST_ACCT_SITES_ALL'
1935 then
1936 if px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).cust_acct_site_id = p_child_id
1937 then
1938 px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).action_type := p_action_type;
1939 l_child_upd_flag := 'N';
1940 end if;
1941 end if;
1942
1943 for j in 1..px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS.COUNT
1944 loop
1945 if p_child_entity_name = 'HZ_CUST_SITE_USES_ALL'
1946 then
1947
1948 if px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).SITE_USE_ID = p_child_id
1952 end if;
1949 then px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).action_type := p_action_type;
1950 l_child_upd_flag := 'Y';
1951 end if;
1953
1954 if p_child_entity_name = 'IBY_FNDCPT_PAYER_ASSGN_INSTR_V' then
1955 for k in 1..px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).BANK_ACCT_USE_OBJS.COUNT
1956 loop
1957 if px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).BANK_ACCT_USE_OBJS(k).BANK_ACCT_USE_ID = p_child_id
1958 then px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).BANK_ACCT_USE_OBJS(k).action_type := p_action_type;
1959 l_child_upd_flag := 'Y';
1960 end if;
1961 end loop;
1962 elsif p_child_entity_name = 'RA_CUST_RECEIPT_METHODS' then
1963 for k in 1..px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).PAYMENT_METHOD_OBJS.COUNT
1964 loop
1965 if px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).PAYMENT_METHOD_OBJS(k).PAYMENT_METHOD_ID = p_child_id
1966 then px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).PAYMENT_METHOD_OBJS(k).action_type := p_action_type;
1967 l_child_upd_flag := 'Y';
1968 end if;
1969 end loop;
1970
1971 elsif p_child_entity_name = 'HZ_CUSTOMER_PROFILES' then
1972
1973 if px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).SITE_USE_PROFILE_OBJ.CUST_ACCT_PROFILE_ID = p_child_id
1974 then px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).SITE_USE_PROFILE_OBJ.action_type := p_action_type;
1975 l_child_upd_flag := 'Y';
1976 end if;
1977
1978 end if;
1979
1980 end loop; -- CUST_ACCT_SITE_USE_OBJS.COUNT
1981
1982 for j in 1..px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS.COUNT
1983 loop
1984
1985 if p_child_entity_name = 'HZ_CUST_ACCOUNT_ROLES'
1986 then
1987 if px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j).CUST_ACCT_CONTACT_ID = p_child_id
1988 then
1989 px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j).action_type := p_action_type;
1990 l_child_upd_flag := 'N';
1991 end if;
1992 end if;
1993
1994
1995 if p_child_entity_name = 'HZ_ROLE_RESPONSIBILITY'
1996 then
1997 for k in 1..px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j). CONTACT_ROLE_OBJS.COUNT
1998 loop
1999 if px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j).CONTACT_ROLE_OBJS(k).RESPONSIBILITY_ID = p_child_id
2000 then px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j).CONTACT_ROLE_OBJS(k).action_type := p_action_type;
2001 l_child_upd_flag := 'Y';
2002 end if;
2003 end loop;
2004 end if;
2005 if px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j).action_type = 'UNCHANGED'
2006 and l_child_upd_flag = 'Y'
2007 then px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j).action_type := 'CHILD_UPDATED';
2008 end if;
2009 end loop; -- CUST_ACCT_CONTACT_OBJS.COUNT
2010 if px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).action_type = 'UNCHANGED'
2011 and l_child_upd_flag = 'Y'
2012 then px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).action_type := 'CHILD_UPDATED';
2013 end if;
2014
2015 if px_cust_acct_v2_obj.action_type = 'UNCHANGED' and l_child_upd_flag = 'Y'
2016 then px_cust_acct_v2_obj.action_type := 'CHILD_UPDATED';
2017 end if;
2018
2019 end loop; -- acct site obj
2020 end if;
2021
2022 end set_v2_org_acct_bo_action_type;
2023
2024 procedure set_v2_org_cust_bo_action_type(p_event_id IN NUMBER,
2025 p_root_id IN NUMBER,
2026 px_org_cust_v2_obj IN OUT NOCOPY HZ_ORG_CUST_V2_BO,
2027 x_return_status OUT NOCOPY VARCHAR2) is
2028
2029 cursor c1 is
2030
2031 SELECT
2032 sys_connect_by_path(CHILD_BO_CODE, '/') node_path,
2033 CHILD_OPERATION_FLAG,
2034 CHILD_BO_CODE,
2035 CHILD_ENTITY_NAME,
2036 CHILD_ID,
2037 populated_flag
2038 FROM HZ_BUS_OBJ_TRACKING
2039 where event_id = p_event_id
2040 START WITH child_id = p_root_id
2041 AND child_entity_name = 'HZ_PARTIES'
2042 AND PARENT_BO_CODE IS NULL
2043 AND event_id = p_event_id
2044 AND CHILD_BO_CODE = 'ORG_CUST' --(or ORG, PERSON_CUST, ORG_CUST).
2045 CONNECT BY PARENT_ENTITY_NAME = PRIOR CHILD_ENTITY_NAME
2046 AND PARENT_ID = PRIOR CHILD_ID
2047 AND parent_bo_code = PRIOR child_bo_code
2048 and event_id = PRIOR event_id;
2049
2050 cursor c2 is
2051 select child_event_id,creation_date
2052 FROM HZ_BUS_OBJ_TRACKING
2053 where event_id = p_event_id
2054 and parent_bo_code is null
2055 and rownum = 1;
2056
2057 CURSOR c_get_child_event_id(cp_creation_date DATE) IS
2058
2059 SELECT event_id
2060 FROM HZ_BUS_OBJ_TRACKING
2061 WHERE creation_date = cp_creation_date
2062 AND child_id = p_root_id
2063 AND child_event_id IS NULL
2064 and event_id <> p_event_id
2065 and rownum = 1;
2066
2067 L_CHILD_OPERATION_FLAG VARCHAR2(1);
2068 L_CHILD_BO_CODE VARCHAR2(30);
2069 L_CHILD_ENTITY_NAME VARCHAR2(30);
2070 L_CHILD_ID NUMBER;
2071 l_action_type varchar2(30);
2072 l_node_path varchar2(2000);
2073 l_populated_flag varchar2(1);
2074 l_child_upd_flag varchar2(1);
2078 begin
2075 l_child_event_id number;
2076 l_creation_date date;
2077
2079 -- initialize API return status to success.
2080 x_return_status := FND_API.G_RET_STS_SUCCESS;
2081
2082 open c2;
2083 fetch c2 into l_child_event_id, l_creation_date;
2084 close c2;
2085
2086 if l_child_event_id is null
2087 then
2088 OPEN c_get_child_event_id(l_creation_date);
2089 FETCH c_get_child_event_id INTO l_child_event_id;
2090 close c_get_child_event_id;
2091 end if;
2092 if l_child_event_id is not null
2093 then
2094 HZ_EXTRACT_ORGANIZATION_BO_PVT.set_org_bo_action_type(p_event_id => l_child_event_id,
2095 p_root_id => p_root_id,
2096 px_org_obj =>PX_ORG_CUST_V2_OBJ.ORGANIZATION_OBJ,
2097 x_return_status => x_return_status);
2098 l_child_upd_flag := 'Y';
2099
2100 end if;
2101
2102
2103 open c1;
2104 loop
2105 fetch c1 into L_NODE_PATH, L_CHILD_OPERATION_FLAG,L_CHILD_BO_CODE,
2106 L_CHILD_ENTITY_NAME, L_CHILD_ID, l_populated_flag;
2107 exit when c1%NOTFOUND;
2108
2109
2110 if l_populated_flag = 'N'
2111 then
2112 if L_CHILD_OPERATION_FLAG = 'I'
2113 then l_action_type := 'CREATED';
2114 elsif L_CHILD_OPERATION_FLAG = 'U'
2115 then l_action_type := 'UPDATED';
2116 end if;
2117
2118 -- check account objects
2119 if instr(l_node_path, 'ORG_CUST/CUST_ACCT') > 0
2120 then
2121 for i in 1..PX_ORG_CUST_V2_OBJ.ACCOUNT_OBJS.COUNT
2122 loop
2123 set_v2_org_acct_bo_action_type(p_node_path => l_node_path,
2124 p_child_id => l_child_id,
2125 p_action_type => l_action_type,
2126 p_child_entity_name => l_child_entity_name,
2127 px_cust_acct_v2_obj => PX_ORG_CUST_V2_OBJ.ACCOUNT_OBJS(i) );
2128 l_child_upd_flag := 'Y';
2129 end loop;
2130 end if;
2131 end if; -- populated_flag = 'N'
2132 end loop;
2133 close c1;
2134 if PX_ORG_CUST_V2_OBJ.action_type = 'UNCHANGED'
2135 and l_child_upd_flag = 'Y'
2136 then PX_ORG_CUST_V2_OBJ.action_type := 'CHILD_UPDATED';
2137 end if;
2138
2139
2140 EXCEPTION
2141
2142
2143 WHEN fnd_api.g_exc_unexpected_error THEN
2144 x_return_status := fnd_api.g_ret_sts_unexp_error;
2145
2146
2147 WHEN OTHERS THEN
2148 x_return_status := fnd_api.g_ret_sts_unexp_error;
2149
2150 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
2151 fnd_message.set_token('ERROR' ,SQLERRM);
2152 fnd_msg_pub.add;
2153
2154 end set_v2_org_cust_bo_action_type;
2155
2156 --------------------------------------
2157 --
2158 -- PROCEDURE get_v2_org_cust_updated
2159 --
2160 -- DESCRIPTION
2161 --The caller provides an identifier for the Organization customer update business event and organization id
2162 --the procedure returns one database object of the type HZ_ORG_CUST_V2_BO
2163 --
2164 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2165 --
2166 -- ARGUMENTS
2167 -- IN:
2168 -- p_init_msg_list Initialize message stack if it is set to
2169 -- p_event_id BES Event identifier.
2170 -- p_org_cust_id Organization customer identifier.
2171 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
2172 -- OUT:
2173 -- x_org_cust_v2_obj One updated logical organization.
2174 -- x_return_status Return status after the call. The status can
2175 -- be fnd_api.g_ret_sts_success (success),
2176 -- fnd_api.g_ret_sts_error (error),
2177 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2178 -- x_msg_count Number of messages in message stack.
2179 -- x_msg_data Message text if x_msg_count is 1.
2180 --
2181 -- NOTES
2182 --
2183 -- MODIFICATION HISTORY
2184 --
2185 -- 06-JUN-2005 vsegu Created.
2186 --
2187
2188
2189 PROCEDURE get_v2_org_cust_updated(
2190 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2191 p_event_id IN NUMBER,
2192 p_org_cust_id IN NUMBER,
2193 x_org_cust_v2_obj OUT NOCOPY HZ_ORG_CUST_V2_BO,
2194 x_return_status OUT NOCOPY VARCHAR2,
2195 x_msg_count OUT NOCOPY NUMBER,
2196 x_msg_data OUT NOCOPY VARCHAR2
2197 ) is
2198 l_obj_root_ids HZ_EXTRACT_BO_UTIL_PVT.BO_ID_TBL;
2199 l_debug_prefix VARCHAR2(30) := '';
2200 l_org_cust_obj HZ_ORG_CUST_V2_BO;
2201 begin
2202
2203 -- initialize API return status to success.
2204 x_return_status := FND_API.G_RET_STS_SUCCESS;
2205
2206 -- Initialize message list if p_init_msg_list is set to TRUE
2207 IF FND_API.to_Boolean(p_init_msg_list) THEN
2208 FND_MSG_PUB.initialize;
2209 END IF;
2210
2211
2212 -- Debug info.
2213 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2214 hz_utility_v2pub.debug(p_message=>'get_v2_org_cust_updated(+)',
2215 p_prefix=>l_debug_prefix,
2216 p_msg_level=>fnd_log.level_procedure);
2217 END IF;
2218
2219 -- Set action type to 'UNCHANGED' by default
2220
2221 get_org_cust_v2_bo(
2222 p_init_msg_list => fnd_api.g_false,
2223 p_organization_id => p_org_cust_id,
2224 p_action_type => 'UNCHANGED',
2225 x_org_cust_v2_obj => x_org_cust_v2_obj,
2226 x_return_status => x_return_status,
2227 x_msg_count => x_msg_count,
2228 x_msg_data => x_msg_data);
2229
2230
2231 -- Based on BOT, for updated branch, set action_type = 'UPDATED'/'CREATED'
2232
2233
2234 l_org_cust_obj := x_org_cust_v2_obj;
2235 set_v2_org_cust_bo_action_type(p_event_id => p_event_id,
2236 p_root_id => p_org_cust_id,
2237 px_org_cust_v2_obj => l_org_cust_obj,
2238 x_return_status => x_return_status
2239 );
2240 x_org_cust_v2_obj := l_org_cust_obj;
2241
2242
2243 -- Debug info.
2244 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2245 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2246 p_msg_data=>x_msg_data,
2247 p_msg_type=>'WARNING',
2248 p_msg_level=>fnd_log.level_exception);
2249 END IF;
2250
2251 -- Debug info.
2252 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2253 hz_utility_v2pub.debug(p_message=>'get_v2_org_cust_updated (-)',
2254 p_prefix=>l_debug_prefix,
2255 p_msg_level=>fnd_log.level_procedure);
2256 END IF;
2257
2258
2259 EXCEPTION
2260
2261 WHEN fnd_api.g_exc_error THEN
2262 x_return_status := fnd_api.g_ret_sts_error;
2263
2264 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2265 p_count => x_msg_count,
2266 p_data => x_msg_data);
2267
2268 -- Debug info.
2269 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2270 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2271 p_msg_data=>x_msg_data,
2272 p_msg_type=>'ERROR',
2273 p_msg_level=>fnd_log.level_error);
2274 END IF;
2275 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2276 hz_utility_v2pub.debug(p_message=>'get_v2_org_cust_updated(-)',
2277 p_prefix=>l_debug_prefix,
2278 p_msg_level=>fnd_log.level_procedure);
2279 END IF;
2280 WHEN fnd_api.g_exc_unexpected_error THEN
2281 x_return_status := fnd_api.g_ret_sts_unexp_error;
2282
2283 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2284 p_count => x_msg_count,
2285 p_data => x_msg_data);
2286
2287 -- Debug info.
2288 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2289 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2290 p_msg_data=>x_msg_data,
2291 p_msg_type=>'UNEXPECTED ERROR',
2292 p_msg_level=>fnd_log.level_error);
2293 END IF;
2294 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2295 hz_utility_v2pub.debug(p_message=>'get_v2_org_cust_updated(-)',
2296 p_prefix=>l_debug_prefix,
2297 p_msg_level=>fnd_log.level_procedure);
2298 END IF;
2299 WHEN OTHERS THEN
2300 x_return_status := fnd_api.g_ret_sts_unexp_error;
2301
2302 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
2303 fnd_message.set_token('ERROR' ,SQLERRM);
2304 fnd_msg_pub.add;
2305
2306 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2307 p_count => x_msg_count,
2308 p_data => x_msg_data);
2309
2310 -- Debug info.
2311 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2312 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2313 p_msg_data=>x_msg_data,
2314 p_msg_type=>'SQL ERROR',
2315 p_msg_level=>fnd_log.level_error);
2316 END IF;
2317 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2318 hz_utility_v2pub.debug(p_message=>'get_v2_org_cust_updated(-)',
2319 p_prefix=>l_debug_prefix,
2320 p_msg_level=>fnd_log.level_procedure);
2321 END IF;
2322
2323 end;
2324
2325 END HZ_EXTRACT_ORG_CUST_BO_PVT;
[Home] [Help]