1 PACKAGE BODY HZ_EXTRACT_ORG_CUST_BO_PVT AS
2 /*$Header: ARHEOAVB.pls 120.10.12020000.2 2012/07/13 08:32:11 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
392 x_org_cust_objs(i).action_type := 'CHILD_UPDATED';
393 end if;
394
395 end loop;
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 /*
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.
520 The caller provides an identifier for the Organization Customers Update business event and the procedure returns
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(-)',
640 p_prefix=>l_debug_prefix,
641 p_msg_level=>fnd_log.level_procedure);
642 END IF;
643 WHEN OTHERS THEN
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 l_cust_acct_relate_id number;
676
677 begin
678
679 -- check root level entities
680 if p_child_entity_name = 'HZ_CUST_ACCOUNTS'
681 then
682 if px_cust_acct_obj.cust_acct_id = p_child_id
683 then
684 px_cust_acct_obj.action_type := p_action_type;
685 l_child_upd_flag := 'N';
686 end if;
687 end if;
688
689 -- check first level objs
690
691
692 if p_child_entity_name = 'IBY_FNDCPT_PAYER_ASSGN_INSTR_V' then
693 for i in 1..PX_CUST_ACCT_OBJ.BANK_ACCT_USE_OBJS.COUNT
694 loop
695 if PX_CUST_ACCT_OBJ.BANK_ACCT_USE_OBJS(i).BANK_ACCT_USE_ID = p_child_id
696 then PX_CUST_ACCT_OBJ.BANK_ACCT_USE_OBJS(i).action_type := p_action_type;
697 l_child_upd_flag := 'Y';
698 end if;
699 end loop;
700 elsif p_child_entity_name = 'HZ_CUST_ACCT_RELATE_ALL' then
701 for i in 1..PX_CUST_ACCT_OBJ.ACCT_RELATE_OBJS.COUNT
702 loop
703 -- bug 8654754: cust_acct_relate_id is not a attribute in acct relate obj.
704 select cust_acct_relate_id into l_cust_acct_relate_id
705 from HZ_CUST_ACCT_RELATE_ALL
706 where cust_account_id = PX_CUST_ACCT_OBJ.ACCT_RELATE_OBJS(i).cust_acct_id
707 and related_cust_account_id = PX_CUST_ACCT_OBJ.ACCT_RELATE_OBJS(i).related_cust_acct_id
708 --and relationship_type = PX_CUST_ACCT_OBJ.ACCT_RELATE_OBJS(i).relationship_type -- bug 13871410
709 and nvl(relationship_type,'null') = nvl(PX_CUST_ACCT_OBJ.ACCT_RELATE_OBJS(i).relationship_type, 'null')
710 --and org_id = PX_CUST_ACCT_OBJ.ACCT_RELATE_OBJS(i).org_id
711 and nvl(org_id,-99) = nvl(PX_CUST_ACCT_OBJ.ACCT_RELATE_OBJS(i).org_id,-99)
712 and rownum =1;
713
714 if l_cust_acct_relate_id = p_child_id
715 then PX_CUST_ACCT_OBJ.ACCT_RELATE_OBJS(i).action_type := p_action_type;
716 l_child_upd_flag := 'Y';
717 end if;
718
719 end loop;
720 elsif p_child_entity_name = 'RA_CUST_RECEIPT_METHODS' then
721
722 if PX_CUST_ACCT_OBJ.PAYMENT_METHOD_OBJ.PAYMENT_METHOD_ID = p_child_id
723 then PX_CUST_ACCT_OBJ.PAYMENT_METHOD_OBJ.action_type := p_action_type;
724 l_child_upd_flag := 'Y';
725 end if;
726 end if;
727 if px_cust_acct_obj.action_type = 'UNCHANGED' and l_child_upd_flag = 'Y'
728 then
729 px_cust_acct_obj.action_type := 'CHILD_UPDATED';
730 end if;
731
732
733 -- check customer porfile obj
734 if instr(p_node_path, 'ORG_CUST/CUST_ACCT/CUST_PROFILE') > 0
735 then
736 if p_child_entity_name = 'HZ_CUSTOMER_PROFILES'
737 then
738 if PX_CUST_ACCT_OBJ.CUST_PROFILE_OBJ.CUST_ACCT_PROFILE_ID = p_child_id
739 then
740 PX_CUST_ACCT_OBJ.CUST_PROFILE_OBJ.action_type := p_action_type;
741 l_child_upd_flag := 'N';
742 end if;
743 end if;
744
745 if p_child_entity_name = 'HZ_CUST_PROFILE_AMTS'
746 then
747 for i in 1..PX_CUST_ACCT_OBJ.CUST_PROFILE_OBJ.CUST_PROFILE_AMT_OBJS.COUNT
748 loop
749 if PX_CUST_ACCT_OBJ.CUST_PROFILE_OBJ.CUST_PROFILE_AMT_OBJS(i).CUST_ACCT_PROFILE_AMT_ID = p_child_id
750 then PX_CUST_ACCT_OBJ.CUST_PROFILE_OBJ.CUST_PROFILE_AMT_OBJS(i).action_type := p_action_type;
751 l_child_upd_flag := 'Y';
752 end if;
753 end loop;
754 end if;
755
756 if px_cust_acct_obj.CUST_PROFILE_OBJ.action_type = 'UNCHANGED' and l_child_upd_flag = 'Y'
757 then
758 px_cust_acct_obj.CUST_PROFILE_OBJ.action_type := 'CHILD_UPDATED';
759 end if;
760
761 end if;
762
763 -- check account contact obj
764
765 if instr(p_node_path, 'ORG_CUST/CUST_ACCT/CUST_ACCT_CONTACT') > 0
766 then
767 for i in 1..PX_CUST_ACCT_OBJ.CUST_ACCT_CONTACT_OBJS.COUNT
768 loop
769 if p_child_entity_name = 'HZ_CUST_ACCOUNT_ROLES'
770 then
771 if PX_CUST_ACCT_OBJ.CUST_ACCT_CONTACT_OBJS(i).CUST_ACCT_CONTACT_ID = p_child_id
772 then
773 PX_CUST_ACCT_OBJ.CUST_ACCT_CONTACT_OBJS(i).action_type := p_action_type;
774 l_child_upd_flag := 'N';
775 end if;
776 end if;
777
778
779 if p_child_entity_name = 'HZ_ROLE_RESPONSIBILITY'
780 then
781 for j in 1..PX_CUST_ACCT_OBJ.CUST_ACCT_CONTACT_OBJS(i).CONTACT_ROLE_OBJS.COUNT
782 loop
783 if PX_CUST_ACCT_OBJ.CUST_ACCT_CONTACT_OBJS(i).CONTACT_ROLE_OBJS(j).RESPONSIBILITY_ID = p_child_id
784 then PX_CUST_ACCT_OBJ.CUST_ACCT_CONTACT_OBJS(i).CONTACT_ROLE_OBJS(j).action_type := p_action_type;
785 l_child_upd_flag := 'Y';
786 end if;
787 end loop;
788 end if;
789 if PX_CUST_ACCT_OBJ.CUST_ACCT_CONTACT_OBJS(i).action_type = 'UNCHANGED'
790 and l_child_upd_flag = 'Y'
791 then PX_CUST_ACCT_OBJ.CUST_ACCT_CONTACT_OBJS(i).action_type := 'CHILD_UPDATED';
792 end if;
793 end loop;
794
795 end if;
796
797 -- check account site obj
798
799 if instr(p_node_path, 'ORG_CUST/CUST_ACCT/CUST_ACCT_SITE') > 0
800 then
801 for i in 1..PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS.COUNT
802 loop
803 if p_child_entity_name = 'HZ_CUST_ACCT_SITES_ALL'
804 then
805 if PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).cust_acct_site_id = p_child_id
806 then
807 PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).action_type := p_action_type;
808 l_child_upd_flag := 'N';
809 end if;
810 end if;
811
812 for j in 1..PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS.COUNT
813 loop
814 if p_child_entity_name = 'HZ_CUST_SITE_USES_ALL'
815 then
816
817 if PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).SITE_USE_ID = p_child_id
818 then PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).action_type := p_action_type;
819 l_child_upd_flag := 'Y';
820 end if;
821 end if;
822
823 if p_child_entity_name = 'IBY_FNDCPT_PAYER_ASSGN_INSTR_V' then
824 for k in 1..PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).BANK_ACCT_USE_OBJS.COUNT
825 loop
826 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
827 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;
828 l_child_upd_flag := 'Y';
829 end if;
830 end loop;
831 elsif p_child_entity_name = 'RA_CUST_RECEIPT_METHODS' then
832
833 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
834 then PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).PAYMENT_METHOD_OBJ.action_type := p_action_type;
835 l_child_upd_flag := 'Y';
836 end if;
837
838 elsif p_child_entity_name = 'HZ_CUSTOMER_PROFILES' then
839
840 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
841 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;
842 l_child_upd_flag := 'Y';
843 end if;
844
845
846 elsif p_child_entity_name = 'HZ_CUST_PROFILE_AMTS' then
847
848 if PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).SITE_USE_PROFILE_OBJ is not null then
849
850 for k in 1..PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).SITE_USE_PROFILE_OBJ.CUST_PROFILE_AMT_OBJS.COUNT
851
852 loop
853
854 if PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).SITE_USE_PROFILE_OBJ.CUST_PROFILE_AMT_OBJS(k).CUST_ACCT_PROFILE_AMT_ID = p_child_id
855
856 then PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).SITE_USE_PROFILE_OBJ.CUST_PROFILE_AMT_OBJS(k).action_type := p_action_type;
857
858 l_child_upd_flag := 'Y';
859
860 end if;
861
862 end loop;
863
864 end if;
865
866 if PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).SITE_USE_PROFILE_OBJ.action_type = 'UNCHANGED' and l_child_upd_flag = 'Y'
867
868 then PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).SITE_USE_PROFILE_OBJ.action_type := 'CHILD_UPDATED';
869
870 end if;
871 end if;
872 end loop; -- CUST_ACCT_SITE_USE_OBJS.COUNT
873
874 for j in 1..PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS.COUNT
875 loop
876
877 if p_child_entity_name = 'HZ_CUST_ACCOUNT_ROLES'
878 then
879 if PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j).CUST_ACCT_CONTACT_ID = p_child_id
880 then
881 PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j).action_type := p_action_type;
882 l_child_upd_flag := 'N';
883 end if;
884 end if;
885
886
887 if p_child_entity_name = 'HZ_ROLE_RESPONSIBILITY'
888 then
889 for k in 1..PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j). CONTACT_ROLE_OBJS.COUNT
890 loop
891 if PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j).CONTACT_ROLE_OBJS(k).RESPONSIBILITY_ID = p_child_id
892 then PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j).CONTACT_ROLE_OBJS(k).action_type := p_action_type;
893 l_child_upd_flag := 'Y';
894 end if;
895 end loop;
896 end if;
897 if PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j).action_type = 'UNCHANGED'
898 and l_child_upd_flag = 'Y'
899 then PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j).action_type := 'CHILD_UPDATED';
900 end if;
901 end loop; -- CUST_ACCT_CONTACT_OBJS.COUNT
902 if PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).action_type = 'UNCHANGED'
903 and l_child_upd_flag = 'Y'
904 then PX_CUST_ACCT_OBJ.CUST_ACCT_SITE_OBJS(i).action_type := 'CHILD_UPDATED';
905 end if;
906
907 if PX_CUST_ACCT_OBJ.action_type = 'UNCHANGED' and l_child_upd_flag = 'Y'
908 then PX_CUST_ACCT_OBJ.action_type := 'CHILD_UPDATED';
909 end if;
910
911 end loop; -- acct site obj
912 end if;
913
914 end set_org_acct_bo_action_type;
915
916 procedure set_org_cust_bo_action_type(p_event_id IN NUMBER,
917 p_root_id IN NUMBER,
918 px_org_cust_obj IN OUT NOCOPY HZ_ORG_CUST_BO,
919 x_return_status OUT NOCOPY VARCHAR2) is
920
921 cursor c1 is
922
923 SELECT
924 sys_connect_by_path(CHILD_BO_CODE, '/') node_path,
925 CHILD_OPERATION_FLAG,
926 CHILD_BO_CODE,
927 CHILD_ENTITY_NAME,
928 CHILD_ID,
929 populated_flag
930 FROM HZ_BUS_OBJ_TRACKING
931 where event_id = p_event_id
932 START WITH child_id = p_root_id
933 AND child_entity_name = 'HZ_PARTIES'
934 AND PARENT_BO_CODE IS NULL
935 AND event_id = p_event_id
936 AND CHILD_BO_CODE = 'ORG_CUST' --(or ORG, PERSON_CUST, ORG_CUST).
937 CONNECT BY PARENT_ENTITY_NAME = PRIOR CHILD_ENTITY_NAME
938 AND PARENT_ID = PRIOR CHILD_ID
939 AND parent_bo_code = PRIOR child_bo_code
940 and event_id = PRIOR event_id;
941
942 cursor c2 is
943 select child_event_id,creation_date
944 FROM HZ_BUS_OBJ_TRACKING
945 where event_id = p_event_id
946 and parent_bo_code is null
947 and rownum = 1;
948
949 CURSOR c_get_child_event_id(cp_creation_date DATE) IS
950
951 SELECT event_id
952 FROM HZ_BUS_OBJ_TRACKING
953 WHERE creation_date = cp_creation_date
954 AND child_id = p_root_id
955 AND child_event_id IS NULL
956 and event_id <> p_event_id
957 and rownum = 1;
958
959 L_CHILD_OPERATION_FLAG VARCHAR2(1);
960 L_CHILD_BO_CODE VARCHAR2(30);
961 L_CHILD_ENTITY_NAME VARCHAR2(30);
962 L_CHILD_ID NUMBER;
963 l_action_type varchar2(30);
964 l_node_path varchar2(2000);
965 l_populated_flag varchar2(1);
966 l_child_upd_flag varchar2(1);
967 l_child_event_id number;
968 l_creation_date date;
969
970 begin
971 -- initialize API return status to success.
972 x_return_status := FND_API.G_RET_STS_SUCCESS;
973
974 open c2;
975 fetch c2 into l_child_event_id, l_creation_date;
976 close c2;
977
978 if l_child_event_id is null
979 then
980 OPEN c_get_child_event_id(l_creation_date);
981 FETCH c_get_child_event_id INTO l_child_event_id;
982 close c_get_child_event_id;
983 end if;
984 if l_child_event_id is not null
985 then
986 HZ_EXTRACT_ORGANIZATION_BO_PVT.set_org_bo_action_type(p_event_id => l_child_event_id,
987 p_root_id => p_root_id,
988 px_org_obj =>PX_ORG_CUST_OBJ.ORGANIZATION_OBJ,
989 x_return_status => x_return_status);
990 l_child_upd_flag := 'Y';
991
992 end if;
993
994
995 open c1;
996 loop
997 fetch c1 into L_NODE_PATH, L_CHILD_OPERATION_FLAG,L_CHILD_BO_CODE,
998 L_CHILD_ENTITY_NAME, L_CHILD_ID, l_populated_flag;
999 exit when c1%NOTFOUND;
1000
1001
1002 if l_populated_flag = 'N'
1003 then
1004 if L_CHILD_OPERATION_FLAG = 'I'
1005 then l_action_type := 'CREATED';
1006 elsif L_CHILD_OPERATION_FLAG = 'U'
1007 then l_action_type := 'UPDATED';
1008 end if;
1009
1010 -- check account objects
1011 if instr(l_node_path, 'ORG_CUST/CUST_ACCT') > 0
1012 then
1013 for i in 1..PX_ORG_CUST_OBJ.ACCOUNT_OBJS.COUNT
1014 loop
1015 set_org_acct_bo_action_type(p_node_path => l_node_path,
1016 p_child_id => l_child_id,
1017 p_action_type => l_action_type,
1018 p_child_entity_name => l_child_entity_name,
1019 px_cust_acct_obj => PX_ORG_CUST_OBJ.ACCOUNT_OBJS(i) );
1020 l_child_upd_flag := 'Y';
1021 end loop;
1022 end if;
1023 end if; -- populated_flag = 'N'
1024 end loop;
1025 close c1;
1026 if PX_ORG_CUST_OBJ.action_type = 'UNCHANGED'
1027 and l_child_upd_flag = 'Y'
1028 then PX_ORG_CUST_OBJ.action_type := 'CHILD_UPDATED';
1029 end if;
1030
1031
1032 EXCEPTION
1033
1034
1035 WHEN fnd_api.g_exc_unexpected_error THEN
1036 x_return_status := fnd_api.g_ret_sts_unexp_error;
1037
1038
1039 WHEN OTHERS THEN
1040 x_return_status := fnd_api.g_ret_sts_unexp_error;
1041
1042 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1043 fnd_message.set_token('ERROR' ,SQLERRM);
1044 fnd_msg_pub.add;
1045
1046 end set_org_cust_bo_action_type;
1047
1048 --------------------------------------
1049 --
1050 -- PROCEDURE get_org_cust_updated
1051 --
1052 -- DESCRIPTION
1053 --The caller provides an identifier for the Organization customer update business event and organization id
1054 --the procedure returns one database object of the type HZ_ORG_CUST_BO
1055 --
1056 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1057 --
1058 -- ARGUMENTS
1059 -- IN:
1060 -- p_init_msg_list Initialize message stack if it is set to
1061 -- p_event_id BES Event identifier.
1062 -- p_org_cust_id Organization customer identifier.
1063 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
1064 -- OUT:
1065 -- x_org_cust_obj One updated logical organization.
1066 -- x_return_status Return status after the call. The status can
1067 -- be fnd_api.g_ret_sts_success (success),
1068 -- fnd_api.g_ret_sts_error (error),
1069 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1070 -- x_msg_count Number of messages in message stack.
1071 -- x_msg_data Message text if x_msg_count is 1.
1072 --
1073 -- NOTES
1074 --
1075 -- MODIFICATION HISTORY
1076 --
1077 -- 06-JUN-2005 AWU Created.
1078 --
1079
1080
1081 PROCEDURE get_org_cust_updated(
1082 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1083 p_event_id IN NUMBER,
1084 p_org_cust_id IN NUMBER,
1085 x_org_cust_obj OUT NOCOPY HZ_ORG_CUST_BO,
1086 x_return_status OUT NOCOPY VARCHAR2,
1087 x_msg_count OUT NOCOPY NUMBER,
1088 x_msg_data OUT NOCOPY VARCHAR2
1089 ) is
1090 l_obj_root_ids HZ_EXTRACT_BO_UTIL_PVT.BO_ID_TBL;
1091 l_debug_prefix VARCHAR2(30) := '';
1092 l_org_cust_obj HZ_ORG_CUST_BO;
1093 begin
1094
1095 -- initialize API return status to success.
1096 x_return_status := FND_API.G_RET_STS_SUCCESS;
1097
1098 -- Initialize message list if p_init_msg_list is set to TRUE
1099 IF FND_API.to_Boolean(p_init_msg_list) THEN
1100 FND_MSG_PUB.initialize;
1101 END IF;
1102
1103
1104 -- Debug info.
1105 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1106 hz_utility_v2pub.debug(p_message=>'get_org_cust_updated(+)',
1107 p_prefix=>l_debug_prefix,
1108 p_msg_level=>fnd_log.level_procedure);
1109 END IF;
1110
1111 /* moved to public api
1112 HZ_EXTRACT_BO_UTIL_PVT.validate_event_id(p_event_id => p_event_id,
1113 p_party_id => p_org_cust_id,
1114 x_return_status => x_return_status);
1115
1116 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1117 RAISE FND_API.G_EXC_ERROR;
1118 END IF;
1119 */
1120 -- Set action type to 'UNCHANGED' by default
1121
1122 get_org_cust_bo(
1123 p_init_msg_list => fnd_api.g_false,
1124 p_organization_id => p_org_cust_id,
1125 p_action_type => 'UNCHANGED',
1126 x_org_cust_obj => x_org_cust_obj,
1127 x_return_status => x_return_status,
1128 x_msg_count => x_msg_count,
1129 x_msg_data => x_msg_data);
1130
1131
1132 -- Based on BOT, for updated branch, set action_type = 'UPDATED'/'CREATED'
1133
1134
1135 l_org_cust_obj := x_org_cust_obj;
1136 set_org_cust_bo_action_type(p_event_id => p_event_id,
1137 p_root_id => p_org_cust_id,
1138 px_org_cust_obj => l_org_cust_obj,
1139 x_return_status => x_return_status
1140 );
1141 x_org_cust_obj := l_org_cust_obj;
1142
1143
1144 -- Debug info.
1145 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1146 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1147 p_msg_data=>x_msg_data,
1148 p_msg_type=>'WARNING',
1149 p_msg_level=>fnd_log.level_exception);
1150 END IF;
1151
1152 -- Debug info.
1153 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1154 hz_utility_v2pub.debug(p_message=>'get_org_cust_updated (-)',
1155 p_prefix=>l_debug_prefix,
1156 p_msg_level=>fnd_log.level_procedure);
1157 END IF;
1158
1159
1160 EXCEPTION
1161
1162 WHEN fnd_api.g_exc_error THEN
1163 x_return_status := fnd_api.g_ret_sts_error;
1164
1165 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1166 p_count => x_msg_count,
1167 p_data => x_msg_data);
1168
1169 -- Debug info.
1170 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1171 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1172 p_msg_data=>x_msg_data,
1173 p_msg_type=>'ERROR',
1174 p_msg_level=>fnd_log.level_error);
1175 END IF;
1176 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1177 hz_utility_v2pub.debug(p_message=>'get_org_cust_updated(-)',
1178 p_prefix=>l_debug_prefix,
1179 p_msg_level=>fnd_log.level_procedure);
1180 END IF;
1181 WHEN fnd_api.g_exc_unexpected_error THEN
1182 x_return_status := fnd_api.g_ret_sts_unexp_error;
1183
1184 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1185 p_count => x_msg_count,
1186 p_data => x_msg_data);
1187
1188 -- Debug info.
1189 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1190 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1191 p_msg_data=>x_msg_data,
1192 p_msg_type=>'UNEXPECTED ERROR',
1193 p_msg_level=>fnd_log.level_error);
1194 END IF;
1195 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1196 hz_utility_v2pub.debug(p_message=>'get_org_cust_updated(-)',
1197 p_prefix=>l_debug_prefix,
1198 p_msg_level=>fnd_log.level_procedure);
1199 END IF;
1200 WHEN OTHERS THEN
1201 x_return_status := fnd_api.g_ret_sts_unexp_error;
1202
1203 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1204 fnd_message.set_token('ERROR' ,SQLERRM);
1205 fnd_msg_pub.add;
1206
1207 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1208 p_count => x_msg_count,
1209 p_data => x_msg_data);
1210
1211 -- Debug info.
1212 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1213 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1214 p_msg_data=>x_msg_data,
1215 p_msg_type=>'SQL ERROR',
1216 p_msg_level=>fnd_log.level_error);
1217 END IF;
1218 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1219 hz_utility_v2pub.debug(p_message=>'get_org_cust_updated(-)',
1220 p_prefix=>l_debug_prefix,
1221 p_msg_level=>fnd_log.level_procedure);
1222 END IF;
1223
1224 end;
1225
1226 --------------------------------------
1227 --
1228 -- PROCEDURE get_org_cust_v2_bo
1229 --
1230 -- DESCRIPTION
1231 -- Get a logical organization customer.
1232 --
1233 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1234 --
1235 -- ARGUMENTS
1236 -- IN:
1237 -- p_init_msg_list Initialize message stack if it is set to
1238 -- p_organization_id Organization ID.
1239 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
1240 -- OUT:
1241 -- x_org_cust_v2_obj Logical organization customer record.
1242 -- x_return_status Return status after the call. The status can
1243 -- be fnd_api.g_ret_sts_success (success),
1244 -- fnd_api.g_ret_sts_error (error),
1245 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1246 -- x_msg_count Number of messages in message stack.
1247 -- x_msg_data Message text if x_msg_count is 1.
1248 --
1249 -- NOTES
1250 --
1251 -- MODIFICATION HISTORY
1252 --
1253 --
1254 -- 04-FEB-2008 vsegu Created.
1255 --
1256
1257 /*
1258 The Get Organization Customer API Procedure is a retrieval service that returns a full Organization Customer business object.
1259 The user identifies a particular Organization Customer business object using the TCA identifier and/or
1260 the object Source System information. Upon proper validation of the object,
1261 the full Organization Customer business object is returned. The object consists of all data included within
1262 the Organization Customer business object, at all embedded levels. This includes the set of all data stored
1263 in the TCA tables for each embedded entity.
1264
1265 To retrieve the appropriate embedded business objects within the Organization Customer business object,
1266 the Get procedure calls the equivalent procedure for the following embedded objects:
1267
1268 Embedded BO Mandatory Multiple Logical API Procedure Comments
1269
1270 Organization Y N get_organization_bo
1271 Customer Account Y Y get_cust_acct_bo Called for each Customer Account object for the Organization Customer
1272
1273 */
1274
1275
1276
1277 PROCEDURE get_org_cust_v2_bo(
1278 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1279 p_organization_id IN NUMBER,
1280 p_action_type IN VARCHAR2 := NULL,
1281 x_org_cust_v2_obj OUT NOCOPY HZ_ORG_CUST_V2_BO,
1282 x_return_status OUT NOCOPY VARCHAR2,
1283 x_msg_count OUT NOCOPY NUMBER,
1284 x_msg_data OUT NOCOPY VARCHAR2
1285 ) is
1286 l_debug_prefix VARCHAR2(30) := '';
1287
1288 begin
1289 -- initialize API return status to success.
1290 x_return_status := FND_API.G_RET_STS_SUCCESS;
1291
1292 -- Initialize message list if p_init_msg_list is set to TRUE
1293 IF FND_API.to_Boolean(p_init_msg_list) THEN
1294 FND_MSG_PUB.initialize;
1295 END IF;
1296
1297
1298 -- Debug info.
1299 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1300 hz_utility_v2pub.debug(p_message=>'get_org_cust_v2_bo(+)',
1301 p_prefix=>l_debug_prefix,
1302 p_msg_level=>fnd_log.level_procedure);
1303 END IF;
1304
1305 x_org_cust_v2_obj := HZ_ORG_CUST_V2_BO(p_action_type, NULL, NULL);
1306
1307 HZ_EXTRACT_ORGANIZATION_BO_PVT.get_organization_bo(
1308 p_init_msg_list => fnd_api.g_false,
1309 p_organization_id => p_organization_id,
1310 p_action_type => p_action_type,
1311 x_organization_obj => x_org_cust_v2_obj.organization_obj,
1312 x_return_status => x_return_status,
1313 x_msg_count => x_msg_count,
1314 x_msg_data => x_msg_data);
1315
1316 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1317 RAISE FND_API.G_EXC_ERROR;
1318 END IF;
1319
1320
1321 HZ_EXTRACT_CUST_ACCT_BO_PVT.get_cust_acct_v2_bos(
1322 p_init_msg_list => fnd_api.g_false,
1323 p_parent_id => p_organization_id,
1324 p_cust_acct_id => NULL,
1325 p_action_type => p_action_type,
1326 x_cust_acct_v2_objs => x_org_cust_v2_obj.account_objs,
1327 x_return_status => x_return_status,
1328 x_msg_count => x_msg_count,
1329 x_msg_data => x_msg_data);
1330
1331 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1332 RAISE FND_API.G_EXC_ERROR;
1333 END IF;
1334
1335
1336 -- Debug info.
1337 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1338 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1339 p_msg_data=>x_msg_data,
1340 p_msg_type=>'WARNING',
1341 p_msg_level=>fnd_log.level_exception);
1342 END IF;
1343
1344 -- Debug info.
1345 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1346 hz_utility_v2pub.debug(p_message=>'get_org_cust_v2_bo (-)',
1347 p_prefix=>l_debug_prefix,
1348 p_msg_level=>fnd_log.level_procedure);
1349 END IF;
1350
1351
1352 EXCEPTION
1353
1354 WHEN fnd_api.g_exc_error THEN
1355 x_return_status := fnd_api.g_ret_sts_error;
1356
1357 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1358 p_count => x_msg_count,
1359 p_data => x_msg_data);
1360
1361 -- Debug info.
1362 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1363 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1364 p_msg_data=>x_msg_data,
1365 p_msg_type=>'ERROR',
1366 p_msg_level=>fnd_log.level_error);
1367 END IF;
1368 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1369 hz_utility_v2pub.debug(p_message=>'get_org_cust_v2_bo (-)',
1370 p_prefix=>l_debug_prefix,
1371 p_msg_level=>fnd_log.level_procedure);
1372 END IF;
1373 WHEN fnd_api.g_exc_unexpected_error THEN
1374 x_return_status := fnd_api.g_ret_sts_unexp_error;
1375
1376 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1377 p_count => x_msg_count,
1378 p_data => x_msg_data);
1379
1380 -- Debug info.
1381 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1382 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1383 p_msg_data=>x_msg_data,
1384 p_msg_type=>'UNEXPECTED ERROR',
1385 p_msg_level=>fnd_log.level_error);
1386 END IF;
1387 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1388 hz_utility_v2pub.debug(p_message=>'get_org_cust_v2_bo (-)',
1389 p_prefix=>l_debug_prefix,
1390 p_msg_level=>fnd_log.level_procedure);
1391 END IF;
1392 WHEN OTHERS THEN
1393 x_return_status := fnd_api.g_ret_sts_unexp_error;
1394
1395 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1396 fnd_message.set_token('ERROR' ,SQLERRM);
1397 fnd_msg_pub.add;
1398
1399 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1400 p_count => x_msg_count,
1401 p_data => x_msg_data);
1402
1403 -- Debug info.
1404 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1405 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1406 p_msg_data=>x_msg_data,
1407 p_msg_type=>'SQL ERROR',
1408 p_msg_level=>fnd_log.level_error);
1409 END IF;
1410 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1411 hz_utility_v2pub.debug(p_message=>'get_org_cust_v2_bo (-)',
1412 p_prefix=>l_debug_prefix,
1413 p_msg_level=>fnd_log.level_procedure);
1414 END IF;
1415
1416 end;
1417
1418 --------------------------------------
1419 --
1420 -- PROCEDURE get_v2_org_custs_created
1421 --
1422 -- DESCRIPTION
1423 --The caller provides an identifier for the Organization Customers created business event and
1424 --the procedure returns database objects of the type HZ_ORG CUSTOMER_BO for all of
1425 --the Organization Customer business objects from the business event.
1426
1427 --
1428 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1429 --
1430 -- ARGUMENTS
1431 -- IN:
1432 -- p_init_msg_list Initialize message stack if it is set to
1433 -- p_event_id BES Event identifier.
1434 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
1435 -- OUT:
1436 -- x_org_cust_v2_objs One or more created logical organization customer.
1437 -- x_return_status Return status after the call. The status can
1438 -- be fnd_api.g_ret_sts_success (success),
1439 -- fnd_api.g_ret_sts_error (error),
1440 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1441 -- x_msg_count Number of messages in message stack.
1442 -- x_msg_data Message text if x_msg_count is 1.
1443 --
1444 -- NOTES
1445 --
1446 -- MODIFICATION HISTORY
1447 --
1448 -- 04-FEB-2008 vsegu Created.
1449 --
1450
1451
1452
1453 /*
1454 The Get organization customers Created procedure is a service to retrieve all of the Organization Customer business objects
1455 whose creations have been captured by a logical business event. Each Organization Customers Created
1456 business event signifies that one or more Organization Customer business objects have been created.
1457 The caller provides an identifier for the Organization Customers Created business event and the procedure
1458 returns all of the Organization Customer business objects from the business event. For each business object
1459 creation captured in the business event, the procedure calls the generic Get operation:
1460 HZ_ORG_BO_PVT.get_org_bo
1461
1462 Gathering all of the returned business objects from those API calls, the procedure packages
1463 them in a table structure and returns them to the caller.
1464 */
1465
1466
1467 PROCEDURE get_v2_org_custs_created(
1468 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1469 p_event_id IN NUMBER,
1470 x_org_cust_v2_objs OUT NOCOPY HZ_ORG_CUST_V2_BO_TBL,
1471 x_return_status OUT NOCOPY VARCHAR2,
1472 x_msg_count OUT NOCOPY NUMBER,
1473 x_msg_data OUT NOCOPY VARCHAR2
1474 ) is
1475
1476 l_obj_root_ids HZ_EXTRACT_BO_UTIL_PVT.BO_ID_TBL;
1477 l_debug_prefix VARCHAR2(30) := '';
1478 L_CHILD_OPERATION_FLAG varchar2(1);
1479 l_action_type varchar2(30);
1480
1481 begin
1482
1483 -- initialize API return status to success.
1484 x_return_status := FND_API.G_RET_STS_SUCCESS;
1485
1486 -- Initialize message list if p_init_msg_list is set to TRUE
1487 IF FND_API.to_Boolean(p_init_msg_list) THEN
1488 FND_MSG_PUB.initialize;
1489 END IF;
1490
1491
1492 -- Debug info.
1493 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1494 hz_utility_v2pub.debug(p_message=>'get_v2_org_cust_created(+)',
1495 p_prefix=>l_debug_prefix,
1496 p_msg_level=>fnd_log.level_procedure);
1497 END IF;
1498
1499
1500 HZ_EXTRACT_BO_UTIL_PVT.get_bo_root_ids(
1501 p_init_msg_list => fnd_api.g_false,
1502 p_event_id => p_event_id,
1503 x_obj_root_ids => l_obj_root_ids,
1504 x_return_status => x_return_status,
1505 x_msg_count => x_msg_count,
1506 x_msg_data => x_msg_data);
1507
1508 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1509 RAISE FND_API.G_EXC_ERROR;
1510 END IF;
1511
1512 l_child_operation_flag:= get_org_operation_type(p_event_id);
1513
1514 if L_CHILD_OPERATION_FLAG = 'I'
1515 then
1516 l_action_type := 'CREATED';
1517 else
1518 l_action_type := 'UNCHANGED'; -- default to unchanged.
1519 end if;
1520
1521
1522 -- call event API get_org_cust_updated for each id.
1523
1524 x_org_cust_v2_objs := HZ_ORG_CUST_V2_BO_TBL();
1525
1526
1527 for i in 1..l_obj_root_ids.count loop
1528
1529 x_org_cust_v2_objs.extend;
1530
1531 x_org_cust_v2_objs(i) := HZ_ORG_CUST_V2_BO('UNCHANGED', NULL, NULL);
1532
1533 HZ_EXTRACT_ORGANIZATION_BO_PVT.get_organization_bo(
1534 p_init_msg_list => fnd_api.g_false,
1535 p_organization_id => l_obj_root_ids(i),
1536 p_action_type => l_action_type,
1537 x_organization_obj => x_org_cust_v2_objs(i).organization_obj,
1538 x_return_status => x_return_status,
1539 x_msg_count => x_msg_count,
1540 x_msg_data => x_msg_data);
1541
1542 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1543 RAISE FND_API.G_EXC_ERROR;
1544 END IF;
1545
1546 if L_CHILD_OPERATION_FLAG = 'U'
1547 then
1548 HZ_EXTRACT_ORGANIZATION_BO_PVT.set_org_bo_action_type(p_event_id =>p_event_id,
1549 p_root_id => l_obj_root_ids(i),
1550 px_org_obj => x_org_cust_v2_objs(i).organization_obj,
1551 x_return_status => x_return_status);
1552
1553 end if;
1554
1555 HZ_EXTRACT_CUST_ACCT_BO_PVT.get_cust_acct_v2_bos(
1556 p_init_msg_list => fnd_api.g_false,
1557 p_parent_id => l_obj_root_ids(i),
1558 p_cust_acct_id => NULL,
1559 p_action_type => 'CREATED',
1560 x_cust_acct_v2_objs => x_org_cust_v2_objs(i).account_objs,
1561 x_return_status => x_return_status,
1562 x_msg_count => x_msg_count,
1563 x_msg_data => x_msg_data);
1564
1565 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1566 RAISE FND_API.G_EXC_ERROR;
1567 END IF;
1568 if L_CHILD_OPERATION_FLAG = 'I'
1569 then
1570 x_org_cust_v2_objs(i).action_type := 'CREATED';
1571 else
1572 x_org_cust_v2_objs(i).action_type := 'CHILD_UPDATED';
1573 end if;
1574
1575 end loop;
1576
1577 -- Debug info.
1578 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1579 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1580 p_msg_data=>x_msg_data,
1581 p_msg_type=>'WARNING',
1582 p_msg_level=>fnd_log.level_exception);
1583 END IF;
1584
1585 -- Debug info.
1586 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1587 hz_utility_v2pub.debug(p_message=>'get_v2_org_cust_created (-)',
1588 p_prefix=>l_debug_prefix,
1589 p_msg_level=>fnd_log.level_procedure);
1590 END IF;
1591
1592
1593 EXCEPTION
1594
1595 WHEN fnd_api.g_exc_error THEN
1596 x_return_status := fnd_api.g_ret_sts_error;
1597
1598 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1599 p_count => x_msg_count,
1600 p_data => x_msg_data);
1601
1602 -- Debug info.
1603 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1604 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1605 p_msg_data=>x_msg_data,
1606 p_msg_type=>'ERROR',
1607 p_msg_level=>fnd_log.level_error);
1608 END IF;
1609 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1610 hz_utility_v2pub.debug(p_message=>'get_v2_org_cust_created(-)',
1611 p_prefix=>l_debug_prefix,
1612 p_msg_level=>fnd_log.level_procedure);
1613 END IF;
1614 WHEN fnd_api.g_exc_unexpected_error THEN
1615 x_return_status := fnd_api.g_ret_sts_unexp_error;
1616
1617 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1618 p_count => x_msg_count,
1619 p_data => x_msg_data);
1620
1621 -- Debug info.
1622 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1623 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1624 p_msg_data=>x_msg_data,
1625 p_msg_type=>'UNEXPECTED ERROR',
1626 p_msg_level=>fnd_log.level_error);
1627 END IF;
1628 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1629 hz_utility_v2pub.debug(p_message=>'get_v2_org_cust_created(-)',
1630 p_prefix=>l_debug_prefix,
1631 p_msg_level=>fnd_log.level_procedure);
1632 END IF;
1633 WHEN OTHERS THEN
1634 x_return_status := fnd_api.g_ret_sts_unexp_error;
1635
1636 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1637 fnd_message.set_token('ERROR' ,SQLERRM);
1638 fnd_msg_pub.add;
1639
1640 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1641 p_count => x_msg_count,
1642 p_data => x_msg_data);
1643
1644 -- Debug info.
1645 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1646 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1647 p_msg_data=>x_msg_data,
1648 p_msg_type=>'SQL ERROR',
1649 p_msg_level=>fnd_log.level_error);
1650 END IF;
1651 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1652 hz_utility_v2pub.debug(p_message=>'get_v2_org_cust_created(-)',
1653 p_prefix=>l_debug_prefix,
1654 p_msg_level=>fnd_log.level_procedure);
1655 END IF;
1656
1657 end;
1658
1659
1660
1661 --------------------------------------
1662 --
1663 -- PROCEDURE get_v2_org_custs_updated
1664 --
1665 -- DESCRIPTION
1666 --The caller provides an identifier for the Organization Customers update business event and
1667 --the procedure returns database objects of the type HZ_ORG_CUST_V2_BO for all of
1668 --the Organization Customer business objects from the business event.
1669
1670 --
1671 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1672 --
1673 -- ARGUMENTS
1674 -- IN:
1675 -- p_init_msg_list Initialize message stack if it is set to
1676 -- p_event_id BES Event identifier.
1677 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
1678 -- OUT:
1679 -- x_org_cust_v2_objs One or more updated logical org customer.
1680 -- x_return_status Return status after the call. The status can
1681 -- be fnd_api.g_ret_sts_success (success),
1682 -- fnd_api.g_ret_sts_error (error),
1683 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1684 -- x_msg_count Number of messages in message stack.
1685 -- x_msg_data Message text if x_msg_count is 1.
1686 --
1687 -- NOTES
1688 --
1689 -- MODIFICATION HISTORY
1690 --
1691 -- 04-FEB-2008 vsegu Created.
1692 --
1693
1694
1695
1696 /*
1697 The Get Organization Customers Updated procedure is a service to retrieve all of the Organization Customer business
1698 objects whose updates have been captured by the logical business event. Each Organization Customers Updated business
1699 event signifies that one or more Organization Customer business objects have been updated.
1700 The caller provides an identifier for the Organization Customers Update business event and the procedure returns
1701 database objects of the type HZ_ORG_CUST_V2_BO for all of the Organization Customer business objects from the business event.
1702 Gathering all of the returned database objects from those API calls, the procedure packages them in a table structure
1703 and returns them to the caller.
1704 */
1705
1706 PROCEDURE get_v2_org_custs_updated(
1707 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1708 p_event_id IN NUMBER,
1709 x_org_cust_v2_objs OUT NOCOPY HZ_ORG_CUST_V2_BO_TBL,
1710 x_return_status OUT NOCOPY VARCHAR2,
1711 x_msg_count OUT NOCOPY NUMBER,
1712 x_msg_data OUT NOCOPY VARCHAR2
1713 ) is
1714
1715 l_obj_root_ids HZ_EXTRACT_BO_UTIL_PVT.BO_ID_TBL;
1716 l_debug_prefix VARCHAR2(30) := '';
1717
1718 begin
1719
1720 -- initialize API return status to success.
1721 x_return_status := FND_API.G_RET_STS_SUCCESS;
1722
1723 -- Initialize message list if p_init_msg_list is set to TRUE
1724 IF FND_API.to_Boolean(p_init_msg_list) THEN
1725 FND_MSG_PUB.initialize;
1726 END IF;
1727
1728
1729 -- Debug info.
1730 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1731 hz_utility_v2pub.debug(p_message=>'get_v2_org_custs_updated(+)',
1732 p_prefix=>l_debug_prefix,
1733 p_msg_level=>fnd_log.level_procedure);
1734 END IF;
1735
1736
1737 HZ_EXTRACT_BO_UTIL_PVT.get_bo_root_ids(
1738 p_init_msg_list => fnd_api.g_false,
1739 p_event_id => p_event_id,
1740 x_obj_root_ids => l_obj_root_ids,
1741 x_return_status => x_return_status,
1742 x_msg_count => x_msg_count,
1743 x_msg_data => x_msg_data);
1744
1745 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1746 RAISE FND_API.G_EXC_ERROR;
1747 END IF;
1748
1749
1750
1751 x_org_cust_v2_objs := HZ_ORG_CUST_V2_BO_TBL();
1752
1753 for i in 1..l_obj_root_ids.count loop
1754 x_org_cust_v2_objs.extend;
1755 get_v2_org_cust_updated(
1756 p_init_msg_list => fnd_api.g_false,
1757 p_event_id => p_event_id,
1758 p_org_cust_id => l_obj_root_ids(i),
1759 x_org_cust_v2_obj => x_org_cust_v2_objs(i),
1760 x_return_status => x_return_status,
1761 x_msg_count => x_msg_count,
1762 x_msg_data => x_msg_data);
1763
1764 end loop;
1765
1766
1767 -- Debug info.
1768 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1769 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1770 p_msg_data=>x_msg_data,
1771 p_msg_type=>'WARNING',
1772 p_msg_level=>fnd_log.level_exception);
1773 END IF;
1774
1775 -- Debug info.
1776 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1777 hz_utility_v2pub.debug(p_message=>'get_v2_org_custs_updated (-)',
1778 p_prefix=>l_debug_prefix,
1779 p_msg_level=>fnd_log.level_procedure);
1780 END IF;
1781
1782
1783 EXCEPTION
1784
1785 WHEN fnd_api.g_exc_error THEN
1786 x_return_status := fnd_api.g_ret_sts_error;
1787
1788 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1789 p_count => x_msg_count,
1790 p_data => x_msg_data);
1791
1792 -- Debug info.
1793 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1794 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1795 p_msg_data=>x_msg_data,
1796 p_msg_type=>'ERROR',
1797 p_msg_level=>fnd_log.level_error);
1798 END IF;
1799 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1800 hz_utility_v2pub.debug(p_message=>'get_v2_org_custs_updated(-)',
1801 p_prefix=>l_debug_prefix,
1802 p_msg_level=>fnd_log.level_procedure);
1803 END IF;
1804 WHEN fnd_api.g_exc_unexpected_error THEN
1805 x_return_status := fnd_api.g_ret_sts_unexp_error;
1806
1807 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1808 p_count => x_msg_count,
1809 p_data => x_msg_data);
1810
1811 -- Debug info.
1812 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1813 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1814 p_msg_data=>x_msg_data,
1815 p_msg_type=>'UNEXPECTED ERROR',
1816 p_msg_level=>fnd_log.level_error);
1817 END IF;
1818 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1819 hz_utility_v2pub.debug(p_message=>'get_v2_org_custs_updated(-)',
1820 p_prefix=>l_debug_prefix,
1821 p_msg_level=>fnd_log.level_procedure);
1822 END IF;
1823 WHEN OTHERS THEN
1824 x_return_status := fnd_api.g_ret_sts_unexp_error;
1825
1826 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1827 fnd_message.set_token('ERROR' ,SQLERRM);
1828 fnd_msg_pub.add;
1829
1830 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1831 p_count => x_msg_count,
1832 p_data => x_msg_data);
1833
1834 -- Debug info.
1835 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1836 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1837 p_msg_data=>x_msg_data,
1838 p_msg_type=>'SQL ERROR',
1839 p_msg_level=>fnd_log.level_error);
1840 END IF;
1841 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1842 hz_utility_v2pub.debug(p_message=>'get_v2_org_custs_updated(-)',
1843 p_prefix=>l_debug_prefix,
1844 p_msg_level=>fnd_log.level_procedure);
1845 END IF;
1846
1847 end;
1848
1849 procedure set_v2_org_acct_bo_action_type(p_node_path IN VARCHAR2,
1850 p_child_id IN NUMBER,
1851 p_action_type IN VARCHAR2,
1852 p_child_entity_name IN VARCHAR2,
1853 px_cust_acct_v2_obj IN OUT NOCOPY HZ_CUST_ACCT_V2_BO) is
1854 l_child_upd_flag varchar2(1):='N';
1855
1856 begin
1857
1858 -- check root level entities
1859 if p_child_entity_name = 'HZ_CUST_ACCOUNTS'
1860 then
1861 if px_cust_acct_v2_obj.cust_acct_id = p_child_id
1862 then
1863 px_cust_acct_v2_obj.action_type := p_action_type;
1864 l_child_upd_flag := 'N';
1865 end if;
1866 end if;
1867
1868 -- check first level objs
1869
1870
1871 if p_child_entity_name = 'IBY_FNDCPT_PAYER_ASSGN_INSTR_V' then
1872 for i in 1..px_cust_acct_v2_obj.BANK_ACCT_USE_OBJS.COUNT
1873 loop
1874 if px_cust_acct_v2_obj.BANK_ACCT_USE_OBJS(i).BANK_ACCT_USE_ID = p_child_id
1875 then px_cust_acct_v2_obj.BANK_ACCT_USE_OBJS(i).action_type := p_action_type;
1876 l_child_upd_flag := 'Y';
1877 end if;
1878 end loop;
1879 elsif p_child_entity_name = 'HZ_CUST_ACCT_RELATE_ALL' then
1880 for i in 1..px_cust_acct_v2_obj.ACCT_RELATE_OBJS.COUNT
1881 loop
1882 if px_cust_acct_v2_obj.ACCT_RELATE_OBJS(i).RELATED_CUST_ACCT_ID = p_child_id
1883 then px_cust_acct_v2_obj.ACCT_RELATE_OBJS(i).action_type := p_action_type;
1884 l_child_upd_flag := 'Y';
1885 end if;
1886 end loop;
1887 elsif p_child_entity_name = 'RA_CUST_RECEIPT_METHODS' then
1888 for i in 1..px_cust_acct_v2_obj.PAYMENT_METHOD_OBJS.COUNT
1889 loop
1890 if px_cust_acct_v2_obj.PAYMENT_METHOD_OBJS(i).PAYMENT_METHOD_ID = p_child_id
1891 then px_cust_acct_v2_obj.PAYMENT_METHOD_OBJS(i).action_type := p_action_type;
1892 l_child_upd_flag := 'Y';
1893 end if;
1894 end loop;
1895 end if;
1896 if px_cust_acct_v2_obj.action_type = 'UNCHANGED' and l_child_upd_flag = 'Y'
1897 then
1898 px_cust_acct_v2_obj.action_type := 'CHILD_UPDATED';
1899 end if;
1900
1901
1902 -- check customer porfile obj
1903 if instr(p_node_path, 'ORG_CUST/CUST_ACCT/CUST_PROFILE') > 0
1904 then
1905 if p_child_entity_name = 'HZ_CUSTOMER_PROFILES'
1906 then
1907 if px_cust_acct_v2_obj.CUST_PROFILE_OBJ.CUST_ACCT_PROFILE_ID = p_child_id
1908 then
1909 px_cust_acct_v2_obj.CUST_PROFILE_OBJ.action_type := p_action_type;
1910 l_child_upd_flag := 'N';
1911 end if;
1912 end if;
1913
1914 if p_child_entity_name = 'HZ_CUST_PROFILE_AMTS'
1915 then
1916 for i in 1..px_cust_acct_v2_obj.CUST_PROFILE_OBJ.CUST_PROFILE_AMT_OBJS.COUNT
1917 loop
1918 if px_cust_acct_v2_obj.CUST_PROFILE_OBJ.CUST_PROFILE_AMT_OBJS(i).CUST_ACCT_PROFILE_AMT_ID = p_child_id
1919 then px_cust_acct_v2_obj.CUST_PROFILE_OBJ.CUST_PROFILE_AMT_OBJS(i).action_type := p_action_type;
1920 l_child_upd_flag := 'Y';
1921 end if;
1922 end loop;
1923 end if;
1924
1925 if px_cust_acct_v2_obj.CUST_PROFILE_OBJ.action_type = 'UNCHANGED' and l_child_upd_flag = 'Y'
1926 then
1927 px_cust_acct_v2_obj.CUST_PROFILE_OBJ.action_type := 'CHILD_UPDATED';
1928 end if;
1929
1930 end if;
1931
1932 -- check account contact obj
1933
1934 if instr(p_node_path, 'ORG_CUST/CUST_ACCT/CUST_ACCT_CONTACT') > 0
1935 then
1936 for i in 1..px_cust_acct_v2_obj.CUST_ACCT_CONTACT_OBJS.COUNT
1937 loop
1938 if p_child_entity_name = 'HZ_CUST_ACCOUNT_ROLES'
1939 then
1940 if px_cust_acct_v2_obj.CUST_ACCT_CONTACT_OBJS(i).CUST_ACCT_CONTACT_ID = p_child_id
1941 then
1942 px_cust_acct_v2_obj.CUST_ACCT_CONTACT_OBJS(i).action_type := p_action_type;
1943 l_child_upd_flag := 'N';
1944 end if;
1945 end if;
1946
1947
1948 if p_child_entity_name = 'HZ_ROLE_RESPONSIBILITY'
1949 then
1950 for j in 1..px_cust_acct_v2_obj.CUST_ACCT_CONTACT_OBJS(i).CONTACT_ROLE_OBJS.COUNT
1951 loop
1952 if px_cust_acct_v2_obj.CUST_ACCT_CONTACT_OBJS(i).CONTACT_ROLE_OBJS(j).RESPONSIBILITY_ID = p_child_id
1953 then px_cust_acct_v2_obj.CUST_ACCT_CONTACT_OBJS(i).CONTACT_ROLE_OBJS(j).action_type := p_action_type;
1954 l_child_upd_flag := 'Y';
1955 end if;
1956 end loop;
1957 end if;
1958 if px_cust_acct_v2_obj.CUST_ACCT_CONTACT_OBJS(i).action_type = 'UNCHANGED'
1959 and l_child_upd_flag = 'Y'
1960 then px_cust_acct_v2_obj.CUST_ACCT_CONTACT_OBJS(i).action_type := 'CHILD_UPDATED';
1961 end if;
1962 end loop;
1963
1964 end if;
1965
1966 -- check account site obj
1967
1968 if instr(p_node_path, 'ORG_CUST/CUST_ACCT/CUST_ACCT_SITE') > 0
1969 then
1970 for i in 1..px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS.COUNT
1971 loop
1972 if p_child_entity_name = 'HZ_CUST_ACCT_SITES_ALL'
1973 then
1974 if px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).cust_acct_site_id = p_child_id
1975 then
1976 px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).action_type := p_action_type;
1977 l_child_upd_flag := 'N';
1978 end if;
1979 end if;
1980
1981 for j in 1..px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS.COUNT
1982 loop
1983 if p_child_entity_name = 'HZ_CUST_SITE_USES_ALL'
1984 then
1985
1986 if px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).SITE_USE_ID = p_child_id
1987 then px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).action_type := p_action_type;
1988 l_child_upd_flag := 'Y';
1989 end if;
1990 end if;
1991
1992 if p_child_entity_name = 'IBY_FNDCPT_PAYER_ASSGN_INSTR_V' then
1993 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
1994 loop
1995 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
1996 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;
1997 l_child_upd_flag := 'Y';
1998 end if;
1999 end loop;
2000 elsif p_child_entity_name = 'RA_CUST_RECEIPT_METHODS' then
2001 for k in 1..px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_SITE_USE_OBJS(j).PAYMENT_METHOD_OBJS.COUNT
2002 loop
2003 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
2004 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;
2005 l_child_upd_flag := 'Y';
2006 end if;
2007 end loop;
2008
2009 elsif p_child_entity_name = 'HZ_CUSTOMER_PROFILES' then
2010
2011 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
2012 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;
2013 l_child_upd_flag := 'Y';
2014 end if;
2015
2016 end if;
2017
2018 end loop; -- CUST_ACCT_SITE_USE_OBJS.COUNT
2019
2020 for j in 1..px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS.COUNT
2021 loop
2022
2023 if p_child_entity_name = 'HZ_CUST_ACCOUNT_ROLES'
2024 then
2025 if px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j).CUST_ACCT_CONTACT_ID = p_child_id
2026 then
2027 px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j).action_type := p_action_type;
2028 l_child_upd_flag := 'N';
2029 end if;
2030 end if;
2031
2032
2033 if p_child_entity_name = 'HZ_ROLE_RESPONSIBILITY'
2034 then
2035 for k in 1..px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j). CONTACT_ROLE_OBJS.COUNT
2036 loop
2037 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
2038 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;
2039 l_child_upd_flag := 'Y';
2040 end if;
2041 end loop;
2042 end if;
2043 if px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j).action_type = 'UNCHANGED'
2044 and l_child_upd_flag = 'Y'
2045 then px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).CUST_ACCT_CONTACT_OBJS(j).action_type := 'CHILD_UPDATED';
2046 end if;
2047 end loop; -- CUST_ACCT_CONTACT_OBJS.COUNT
2048 if px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).action_type = 'UNCHANGED'
2049 and l_child_upd_flag = 'Y'
2050 then px_cust_acct_v2_obj.CUST_ACCT_SITE_OBJS(i).action_type := 'CHILD_UPDATED';
2051 end if;
2052
2053 if px_cust_acct_v2_obj.action_type = 'UNCHANGED' and l_child_upd_flag = 'Y'
2054 then px_cust_acct_v2_obj.action_type := 'CHILD_UPDATED';
2055 end if;
2056
2057 end loop; -- acct site obj
2058 end if;
2059
2060 end set_v2_org_acct_bo_action_type;
2061
2062 procedure set_v2_org_cust_bo_action_type(p_event_id IN NUMBER,
2063 p_root_id IN NUMBER,
2064 px_org_cust_v2_obj IN OUT NOCOPY HZ_ORG_CUST_V2_BO,
2065 x_return_status OUT NOCOPY VARCHAR2) is
2066
2067 cursor c1 is
2068
2069 SELECT
2070 sys_connect_by_path(CHILD_BO_CODE, '/') node_path,
2071 CHILD_OPERATION_FLAG,
2072 CHILD_BO_CODE,
2073 CHILD_ENTITY_NAME,
2074 CHILD_ID,
2075 populated_flag
2076 FROM HZ_BUS_OBJ_TRACKING
2077 where event_id = p_event_id
2078 START WITH child_id = p_root_id
2079 AND child_entity_name = 'HZ_PARTIES'
2080 AND PARENT_BO_CODE IS NULL
2081 AND event_id = p_event_id
2082 AND CHILD_BO_CODE = 'ORG_CUST' --(or ORG, PERSON_CUST, ORG_CUST).
2083 CONNECT BY PARENT_ENTITY_NAME = PRIOR CHILD_ENTITY_NAME
2084 AND PARENT_ID = PRIOR CHILD_ID
2085 AND parent_bo_code = PRIOR child_bo_code
2086 and event_id = PRIOR event_id;
2087
2088 cursor c2 is
2089 select child_event_id,creation_date
2090 FROM HZ_BUS_OBJ_TRACKING
2091 where event_id = p_event_id
2092 and parent_bo_code is null
2093 and rownum = 1;
2094
2095 CURSOR c_get_child_event_id(cp_creation_date DATE) IS
2096
2097 SELECT event_id
2098 FROM HZ_BUS_OBJ_TRACKING
2099 WHERE creation_date = cp_creation_date
2100 AND child_id = p_root_id
2101 AND child_event_id IS NULL
2102 and event_id <> p_event_id
2103 and rownum = 1;
2104
2105 L_CHILD_OPERATION_FLAG VARCHAR2(1);
2106 L_CHILD_BO_CODE VARCHAR2(30);
2107 L_CHILD_ENTITY_NAME VARCHAR2(30);
2108 L_CHILD_ID NUMBER;
2109 l_action_type varchar2(30);
2110 l_node_path varchar2(2000);
2111 l_populated_flag varchar2(1);
2112 l_child_upd_flag varchar2(1);
2113 l_child_event_id number;
2114 l_creation_date date;
2115
2116 begin
2117 -- initialize API return status to success.
2118 x_return_status := FND_API.G_RET_STS_SUCCESS;
2119
2120 open c2;
2121 fetch c2 into l_child_event_id, l_creation_date;
2122 close c2;
2123
2124 if l_child_event_id is null
2125 then
2126 OPEN c_get_child_event_id(l_creation_date);
2127 FETCH c_get_child_event_id INTO l_child_event_id;
2128 close c_get_child_event_id;
2129 end if;
2130 if l_child_event_id is not null
2131 then
2132 HZ_EXTRACT_ORGANIZATION_BO_PVT.set_org_bo_action_type(p_event_id => l_child_event_id,
2133 p_root_id => p_root_id,
2134 px_org_obj =>PX_ORG_CUST_V2_OBJ.ORGANIZATION_OBJ,
2135 x_return_status => x_return_status);
2136 l_child_upd_flag := 'Y';
2137
2138 end if;
2139
2140
2141 open c1;
2142 loop
2143 fetch c1 into L_NODE_PATH, L_CHILD_OPERATION_FLAG,L_CHILD_BO_CODE,
2144 L_CHILD_ENTITY_NAME, L_CHILD_ID, l_populated_flag;
2145 exit when c1%NOTFOUND;
2146
2147
2148 if l_populated_flag = 'N'
2149 then
2150 if L_CHILD_OPERATION_FLAG = 'I'
2151 then l_action_type := 'CREATED';
2152 elsif L_CHILD_OPERATION_FLAG = 'U'
2153 then l_action_type := 'UPDATED';
2154 end if;
2155
2156 -- check account objects
2157 if instr(l_node_path, 'ORG_CUST/CUST_ACCT') > 0
2158 then
2159 for i in 1..PX_ORG_CUST_V2_OBJ.ACCOUNT_OBJS.COUNT
2160 loop
2161 set_v2_org_acct_bo_action_type(p_node_path => l_node_path,
2162 p_child_id => l_child_id,
2163 p_action_type => l_action_type,
2164 p_child_entity_name => l_child_entity_name,
2165 px_cust_acct_v2_obj => PX_ORG_CUST_V2_OBJ.ACCOUNT_OBJS(i) );
2166 l_child_upd_flag := 'Y';
2167 end loop;
2168 end if;
2169 end if; -- populated_flag = 'N'
2170 end loop;
2171 close c1;
2172 if PX_ORG_CUST_V2_OBJ.action_type = 'UNCHANGED'
2173 and l_child_upd_flag = 'Y'
2174 then PX_ORG_CUST_V2_OBJ.action_type := 'CHILD_UPDATED';
2175 end if;
2176
2177
2178 EXCEPTION
2179
2180
2181 WHEN fnd_api.g_exc_unexpected_error THEN
2182 x_return_status := fnd_api.g_ret_sts_unexp_error;
2183
2184
2185 WHEN OTHERS THEN
2186 x_return_status := fnd_api.g_ret_sts_unexp_error;
2187
2188 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
2189 fnd_message.set_token('ERROR' ,SQLERRM);
2190 fnd_msg_pub.add;
2191
2192 end set_v2_org_cust_bo_action_type;
2193
2194 --------------------------------------
2195 --
2196 -- PROCEDURE get_v2_org_cust_updated
2197 --
2198 -- DESCRIPTION
2199 --The caller provides an identifier for the Organization customer update business event and organization id
2200 --the procedure returns one database object of the type HZ_ORG_CUST_V2_BO
2201 --
2202 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2203 --
2204 -- ARGUMENTS
2205 -- IN:
2206 -- p_init_msg_list Initialize message stack if it is set to
2207 -- p_event_id BES Event identifier.
2208 -- p_org_cust_id Organization customer identifier.
2209 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
2210 -- OUT:
2211 -- x_org_cust_v2_obj One updated logical organization.
2212 -- x_return_status Return status after the call. The status can
2213 -- be fnd_api.g_ret_sts_success (success),
2214 -- fnd_api.g_ret_sts_error (error),
2215 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2216 -- x_msg_count Number of messages in message stack.
2217 -- x_msg_data Message text if x_msg_count is 1.
2218 --
2219 -- NOTES
2220 --
2221 -- MODIFICATION HISTORY
2222 --
2223 -- 06-JUN-2005 vsegu Created.
2224 --
2225
2226
2227 PROCEDURE get_v2_org_cust_updated(
2228 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2229 p_event_id IN NUMBER,
2230 p_org_cust_id IN NUMBER,
2231 x_org_cust_v2_obj OUT NOCOPY HZ_ORG_CUST_V2_BO,
2232 x_return_status OUT NOCOPY VARCHAR2,
2233 x_msg_count OUT NOCOPY NUMBER,
2234 x_msg_data OUT NOCOPY VARCHAR2
2235 ) is
2236 l_obj_root_ids HZ_EXTRACT_BO_UTIL_PVT.BO_ID_TBL;
2237 l_debug_prefix VARCHAR2(30) := '';
2238 l_org_cust_obj HZ_ORG_CUST_V2_BO;
2239 begin
2240
2241 -- initialize API return status to success.
2242 x_return_status := FND_API.G_RET_STS_SUCCESS;
2243
2244 -- Initialize message list if p_init_msg_list is set to TRUE
2245 IF FND_API.to_Boolean(p_init_msg_list) THEN
2246 FND_MSG_PUB.initialize;
2247 END IF;
2248
2249
2250 -- Debug info.
2251 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2252 hz_utility_v2pub.debug(p_message=>'get_v2_org_cust_updated(+)',
2253 p_prefix=>l_debug_prefix,
2254 p_msg_level=>fnd_log.level_procedure);
2255 END IF;
2256
2257 -- Set action type to 'UNCHANGED' by default
2258
2259 get_org_cust_v2_bo(
2260 p_init_msg_list => fnd_api.g_false,
2261 p_organization_id => p_org_cust_id,
2262 p_action_type => 'UNCHANGED',
2263 x_org_cust_v2_obj => x_org_cust_v2_obj,
2264 x_return_status => x_return_status,
2265 x_msg_count => x_msg_count,
2266 x_msg_data => x_msg_data);
2267
2268
2269 -- Based on BOT, for updated branch, set action_type = 'UPDATED'/'CREATED'
2270
2271
2272 l_org_cust_obj := x_org_cust_v2_obj;
2273 set_v2_org_cust_bo_action_type(p_event_id => p_event_id,
2274 p_root_id => p_org_cust_id,
2275 px_org_cust_v2_obj => l_org_cust_obj,
2276 x_return_status => x_return_status
2277 );
2278 x_org_cust_v2_obj := l_org_cust_obj;
2279
2280
2281 -- Debug info.
2282 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2283 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2284 p_msg_data=>x_msg_data,
2285 p_msg_type=>'WARNING',
2286 p_msg_level=>fnd_log.level_exception);
2287 END IF;
2288
2289 -- Debug info.
2290 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2291 hz_utility_v2pub.debug(p_message=>'get_v2_org_cust_updated (-)',
2292 p_prefix=>l_debug_prefix,
2293 p_msg_level=>fnd_log.level_procedure);
2294 END IF;
2295
2296
2297 EXCEPTION
2298
2299 WHEN fnd_api.g_exc_error THEN
2300 x_return_status := fnd_api.g_ret_sts_error;
2301
2302 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2303 p_count => x_msg_count,
2304 p_data => x_msg_data);
2305
2306 -- Debug info.
2307 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2308 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2309 p_msg_data=>x_msg_data,
2310 p_msg_type=>'ERROR',
2311 p_msg_level=>fnd_log.level_error);
2312 END IF;
2313 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2314 hz_utility_v2pub.debug(p_message=>'get_v2_org_cust_updated(-)',
2315 p_prefix=>l_debug_prefix,
2316 p_msg_level=>fnd_log.level_procedure);
2317 END IF;
2318 WHEN fnd_api.g_exc_unexpected_error THEN
2319 x_return_status := fnd_api.g_ret_sts_unexp_error;
2320
2321 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2322 p_count => x_msg_count,
2323 p_data => x_msg_data);
2324
2325 -- Debug info.
2326 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2327 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2328 p_msg_data=>x_msg_data,
2329 p_msg_type=>'UNEXPECTED ERROR',
2330 p_msg_level=>fnd_log.level_error);
2331 END IF;
2332 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2333 hz_utility_v2pub.debug(p_message=>'get_v2_org_cust_updated(-)',
2334 p_prefix=>l_debug_prefix,
2335 p_msg_level=>fnd_log.level_procedure);
2336 END IF;
2337 WHEN OTHERS THEN
2338 x_return_status := fnd_api.g_ret_sts_unexp_error;
2339
2340 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
2341 fnd_message.set_token('ERROR' ,SQLERRM);
2342 fnd_msg_pub.add;
2343
2344 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2345 p_count => x_msg_count,
2346 p_data => x_msg_data);
2347
2348 -- Debug info.
2349 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2350 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2351 p_msg_data=>x_msg_data,
2352 p_msg_type=>'SQL ERROR',
2353 p_msg_level=>fnd_log.level_error);
2354 END IF;
2355 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2356 hz_utility_v2pub.debug(p_message=>'get_v2_org_cust_updated(-)',
2357 p_prefix=>l_debug_prefix,
2358 p_msg_level=>fnd_log.level_procedure);
2359 END IF;
2360
2361 end;
2362
2363 END HZ_EXTRACT_ORG_CUST_BO_PVT;
[Home] [Help]