[Home] [Help]
PACKAGE BODY: APPS.HZ_EXTRACT_CUST_ACCT_BO_PVT
Source
1 PACKAGE BODY HZ_EXTRACT_CUST_ACCT_BO_PVT AS
2 /*$Header: ARHECAVB.pls 120.9 2008/02/06 10:04:40 vsegu ship $ */
3 /*
4 * This package contains the private APIs for logical customer account.
5 * @rep:scope private
6 * @rep:product HZ
7 * @rep:displayname customer account
8 * @rep:category BUSINESS_ENTITY HZ_PARTIES
9 * @rep:lifecycle active
10 * @rep:doccd 115hztig.pdf customer account Get APIs
11 */
12
13 -- Private local procedure
14 PROCEDURE get_cust_acct_relate_objs(
15 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
16 p_cust_acct_id IN NUMBER,
17 p_action_type IN VARCHAR2 := NULL,
18 x_cust_acct_relate_objs OUT NOCOPY HZ_CUST_ACCT_RELATE_OBJ_TBL,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2
22 ) is
23 l_debug_prefix VARCHAR2(30) := '';
24
25 CURSOR C1 IS
26 SELECT HZ_CUST_ACCT_RELATE_OBJ (
27 P_ACTION_TYPE,
28 NULL, -- COMMON_OBJ_ID
29 CUST_ACCOUNT_ID,
30 RELATED_CUST_ACCOUNT_ID,
31 NULL, --RELATED_CUST_ACCOUNT_OS,
32 NULL, --RELATED_CUST_ACCOUNT_OSR,
33 RELATIONSHIP_TYPE,
34 COMMENTS,
35 ATTRIBUTE_CATEGORY,
36 ATTRIBUTE1,
37 ATTRIBUTE2,
38 ATTRIBUTE3,
39 ATTRIBUTE4,
40 ATTRIBUTE5,
41 ATTRIBUTE6,
42 ATTRIBUTE7,
43 ATTRIBUTE8,
44 ATTRIBUTE9,
45 ATTRIBUTE10,
46 ATTRIBUTE11,
47 ATTRIBUTE12,
48 ATTRIBUTE13,
49 ATTRIBUTE14,
50 ATTRIBUTE15,
51 CUSTOMER_RECIPROCAL_FLAG,
52 STATUS,
53 BILL_TO_FLAG,
54 SHIP_TO_FLAG,
55 ORG_ID,
56 PROGRAM_UPDATE_DATE,
57 CREATED_BY_MODULE,
58 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
59 CREATION_DATE,
60 LAST_UPDATE_DATE,
61 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
62 CAST(MULTISET (
63 SELECT HZ_ORIG_SYS_REF_OBJ(
64 NULL, --P_ACTION_TYPE,
65 ORIG_SYSTEM_REF_ID,
66 ORIG_SYSTEM,
67 ORIG_SYSTEM_REFERENCE,
68 OWNER_TABLE_NAME,
69 OWNER_TABLE_ID,
70 STATUS,
71 REASON_CODE,
72 OLD_ORIG_SYSTEM_REFERENCE,
73 START_DATE_ACTIVE,
74 END_DATE_ACTIVE,
75 PROGRAM_UPDATE_DATE,
76 CREATED_BY_MODULE,
77 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
78 CREATION_DATE,
79 LAST_UPDATE_DATE,
80 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
81 ATTRIBUTE_CATEGORY,
82 ATTRIBUTE1,
83 ATTRIBUTE2,
84 ATTRIBUTE3,
85 ATTRIBUTE4,
86 ATTRIBUTE5,
87 ATTRIBUTE6,
88 ATTRIBUTE7,
89 ATTRIBUTE8,
90 ATTRIBUTE9,
91 ATTRIBUTE10,
92 ATTRIBUTE11,
93 ATTRIBUTE12,
94 ATTRIBUTE13,
95 ATTRIBUTE14,
96 ATTRIBUTE15,
97 ATTRIBUTE16,
98 ATTRIBUTE17,
99 ATTRIBUTE18,
100 ATTRIBUTE19,
101 ATTRIBUTE20)
102 FROM HZ_ORIG_SYS_REFERENCES
103 WHERE
104 OWNER_TABLE_ID = RELATED_CUST_ACCOUNT_ID
105 AND OWNER_TABLE_NAME = 'HZ_CUST_ACCOUNTS'
106 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL))
107 FROM HZ_CUST_ACCT_RELATE
108 WHERE CUST_ACCOUNT_ID = P_CUST_ACCT_ID;
109
110 BEGIN
111
112
113 -- initialize API return status to success.
114 x_return_status := FND_API.G_RET_STS_SUCCESS;
115
116 -- Initialize message list if p_init_msg_list is set to TRUE
117 IF FND_API.to_Boolean(p_init_msg_list) THEN
118 FND_MSG_PUB.initialize;
119 END IF;
120
121
122 -- Debug info.
123 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
124 hz_utility_v2pub.debug(p_message=>'get_cust_acct_relate_bo(+)',
125 p_prefix=>l_debug_prefix,
126 p_msg_level=>fnd_log.level_procedure);
127 END IF;
128
129 open c1;
130 fetch c1 bulk collect into x_cust_acct_relate_objs;
131 close c1;
132
133 -- Debug info.
134 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
135 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
136 p_msg_data=>x_msg_data,
137 p_msg_type=>'WARNING',
138 p_msg_level=>fnd_log.level_exception);
139 END IF;
140
141 -- Debug info.
142 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
143 hz_utility_v2pub.debug(p_message=>'get_cust_acct_relate_bo (-)',
144 p_prefix=>l_debug_prefix,
145 p_msg_level=>fnd_log.level_procedure);
146 END IF;
147
148
149 EXCEPTION
150
151 WHEN fnd_api.g_exc_error THEN
152 x_return_status := fnd_api.g_ret_sts_error;
153
154 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
155 p_count => x_msg_count,
156 p_data => x_msg_data);
157
158 -- Debug info.
159 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
160 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
161 p_msg_data=>x_msg_data,
162 p_msg_type=>'ERROR',
163 p_msg_level=>fnd_log.level_error);
164 END IF;
165 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
166 hz_utility_v2pub.debug(p_message=>'get_cust_acct_relate_bo (-)',
167 p_prefix=>l_debug_prefix,
168 p_msg_level=>fnd_log.level_procedure);
169 END IF;
170 WHEN fnd_api.g_exc_unexpected_error THEN
171 x_return_status := fnd_api.g_ret_sts_unexp_error;
172
173 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
174 p_count => x_msg_count,
175 p_data => x_msg_data);
176
177 -- Debug info.
178 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
179 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
180 p_msg_data=>x_msg_data,
181 p_msg_type=>'UNEXPECTED ERROR',
182 p_msg_level=>fnd_log.level_error);
183 END IF;
184 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
185 hz_utility_v2pub.debug(p_message=>'get_cust_acct_relate_bo (-)',
186 p_prefix=>l_debug_prefix,
187 p_msg_level=>fnd_log.level_procedure);
188 END IF;
189 WHEN OTHERS THEN
190 x_return_status := fnd_api.g_ret_sts_unexp_error;
191
192 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
193 fnd_message.set_token('ERROR' ,SQLERRM);
194 fnd_msg_pub.add;
195
196 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
197 p_count => x_msg_count,
198 p_data => x_msg_data);
199
200 -- Debug info.
201 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
202 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
203 p_msg_data=>x_msg_data,
204 p_msg_type=>'SQL ERROR',
205 p_msg_level=>fnd_log.level_error);
206 END IF;
207 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
208 hz_utility_v2pub.debug(p_message=>'get_cust_acct_relate_bo (-)',
209 p_prefix=>l_debug_prefix,
210 p_msg_level=>fnd_log.level_procedure);
211 END IF;
212
213 end;
214
215
216 -- Private local procedure
217 PROCEDURE get_cust_profile_bo(
218 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
219 p_cust_acct_id IN NUMBER,
220 p_action_type IN VARCHAR2 := NULL,
221 x_cust_profile_obj OUT NOCOPY HZ_CUSTOMER_PROFILE_BO,
222 x_return_status OUT NOCOPY VARCHAR2,
223 x_msg_count OUT NOCOPY NUMBER,
224 x_msg_data OUT NOCOPY VARCHAR2
225 ) is
226 l_debug_prefix VARCHAR2(30) := '';
227
228 CURSOR C1 IS
229 SELECT HZ_CUSTOMER_PROFILE_BO(
230 P_ACTION_TYPE,
231 NULL, -- COMMON_OBJ_ID
232 CUST_ACCOUNT_PROFILE_ID,
233 CUST_ACCOUNT_ID,
234 STATUS,
235 COLLECTOR_ID,
236 CREDIT_ANALYST_ID,
237 CREDIT_CHECKING,
238 NEXT_CREDIT_REVIEW_DATE,
239 TOLERANCE,
240 DISCOUNT_TERMS,
241 DUNNING_LETTERS,
242 INTEREST_CHARGES,
243 SEND_STATEMENTS,
244 CREDIT_BALANCE_STATEMENTS,
245 CREDIT_HOLD,
246 PROFILE_CLASS_ID,
247 SITE_USE_ID,
248 CREDIT_RATING,
249 RISK_CODE,
250 STANDARD_TERMS,
251 OVERRIDE_TERMS,
252 DUNNING_LETTER_SET_ID,
253 INTEREST_PERIOD_DAYS,
254 PAYMENT_GRACE_DAYS,
255 DISCOUNT_GRACE_DAYS,
256 STATEMENT_CYCLE_ID,
257 ACCOUNT_STATUS,
258 PERCENT_COLLECTABLE,
259 AUTOCASH_HIERARCHY_ID,
260 ATTRIBUTE_CATEGORY,
261 ATTRIBUTE1,
262 ATTRIBUTE2,
263 ATTRIBUTE3,
264 ATTRIBUTE4,
265 ATTRIBUTE5,
266 ATTRIBUTE6,
267 ATTRIBUTE7,
268 ATTRIBUTE8,
269 ATTRIBUTE9,
270 ATTRIBUTE10,
271 ATTRIBUTE11,
272 ATTRIBUTE12,
273 ATTRIBUTE13,
274 ATTRIBUTE14,
275 ATTRIBUTE15,
276 AUTO_REC_INCL_DISPUTED_FLAG,
277 TAX_PRINTING_OPTION,
278 CHARGE_ON_FINANCE_CHARGE_FLAG,
279 GROUPING_RULE_ID,
280 CLEARING_DAYS,
281 JGZZ_ATTRIBUTE_CATEGORY,
282 JGZZ_ATTRIBUTE1,
283 JGZZ_ATTRIBUTE2,
284 JGZZ_ATTRIBUTE3,
285 JGZZ_ATTRIBUTE4,
286 JGZZ_ATTRIBUTE5,
287 JGZZ_ATTRIBUTE6,
288 JGZZ_ATTRIBUTE7,
289 JGZZ_ATTRIBUTE8,
290 JGZZ_ATTRIBUTE9,
291 JGZZ_ATTRIBUTE10,
292 JGZZ_ATTRIBUTE11,
293 JGZZ_ATTRIBUTE12,
294 JGZZ_ATTRIBUTE13,
295 JGZZ_ATTRIBUTE14,
296 JGZZ_ATTRIBUTE15,
297 GLOBAL_ATTRIBUTE1,
298 GLOBAL_ATTRIBUTE2,
299 GLOBAL_ATTRIBUTE3,
300 GLOBAL_ATTRIBUTE4,
301 GLOBAL_ATTRIBUTE5,
302 GLOBAL_ATTRIBUTE6,
303 GLOBAL_ATTRIBUTE7,
304 GLOBAL_ATTRIBUTE8,
305 GLOBAL_ATTRIBUTE9,
306 GLOBAL_ATTRIBUTE10,
307 GLOBAL_ATTRIBUTE11,
308 GLOBAL_ATTRIBUTE12,
309 GLOBAL_ATTRIBUTE13,
310 GLOBAL_ATTRIBUTE14,
311 GLOBAL_ATTRIBUTE15,
312 GLOBAL_ATTRIBUTE16,
313 GLOBAL_ATTRIBUTE17,
314 GLOBAL_ATTRIBUTE18,
315 GLOBAL_ATTRIBUTE19,
316 GLOBAL_ATTRIBUTE20,
317 GLOBAL_ATTRIBUTE_CATEGORY,
318 CONS_INV_FLAG,
319 CONS_INV_TYPE,
320 AUTOCASH_HIERARCHY_ID_FOR_ADR,
321 LOCKBOX_MATCHING_OPTION,
322 PROGRAM_UPDATE_DATE,
323 CREATED_BY_MODULE,
324 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
325 CREATION_DATE,
326 LAST_UPDATE_DATE,
327 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
328 REVIEW_CYCLE,
329 LAST_CREDIT_REVIEW_DATE,
330 CREDIT_CLASSIFICATION,
331 CONS_BILL_LEVEL,
332 LATE_CHARGE_CALCULATION_TRX,
333 CREDIT_ITEMS_FLAG,
334 DISPUTED_TRANSACTIONS_FLAG,
335 LATE_CHARGE_TYPE,
336 LATE_CHARGE_TERM_ID,
337 INTEREST_CALCULATION_PERIOD,
338 HOLD_CHARGED_INVOICES_FLAG,
339 MESSAGE_TEXT_ID,
340 MULTIPLE_INTEREST_RATES_FLAG,
341 CHARGE_BEGIN_DATE,
342 CAST(MULTISET (
343 SELECT HZ_CUST_PROFILE_AMT_OBJ(
344 P_ACTION_TYPE,
345 NULL, -- COMMON_OBJ_ID
346 CUST_ACCT_PROFILE_AMT_ID,
347 CUST_ACCOUNT_PROFILE_ID,
348 CURRENCY_CODE,
349 TRX_CREDIT_LIMIT,
350 OVERALL_CREDIT_LIMIT,
351 MIN_DUNNING_AMOUNT,
352 MIN_DUNNING_INVOICE_AMOUNT,
353 MAX_INTEREST_CHARGE,
354 MIN_STATEMENT_AMOUNT,
355 AUTO_REC_MIN_RECEIPT_AMOUNT,
356 INTEREST_RATE,
357 ATTRIBUTE_CATEGORY,
358 ATTRIBUTE1,
359 ATTRIBUTE2,
360 ATTRIBUTE3,
361 ATTRIBUTE4,
362 ATTRIBUTE5,
363 ATTRIBUTE6,
364 ATTRIBUTE7,
365 ATTRIBUTE8,
366 ATTRIBUTE9,
367 ATTRIBUTE10,
368 ATTRIBUTE11,
369 ATTRIBUTE12,
370 ATTRIBUTE13,
371 ATTRIBUTE14,
372 ATTRIBUTE15,
373 MIN_FC_BALANCE_AMOUNT,
374 MIN_FC_INVOICE_AMOUNT,
375 CUST_ACCOUNT_ID,
376 SITE_USE_ID,
377 EXPIRATION_DATE,
378 JGZZ_ATTRIBUTE_CATEGORY,
379 JGZZ_ATTRIBUTE1,
380 JGZZ_ATTRIBUTE2,
381 JGZZ_ATTRIBUTE3,
382 JGZZ_ATTRIBUTE4,
383 JGZZ_ATTRIBUTE5,
384 JGZZ_ATTRIBUTE6,
385 JGZZ_ATTRIBUTE7,
386 JGZZ_ATTRIBUTE8,
387 JGZZ_ATTRIBUTE9,
388 JGZZ_ATTRIBUTE10,
389 JGZZ_ATTRIBUTE11,
390 JGZZ_ATTRIBUTE12,
391 JGZZ_ATTRIBUTE13,
392 JGZZ_ATTRIBUTE14,
393 JGZZ_ATTRIBUTE15,
394 GLOBAL_ATTRIBUTE1,
395 GLOBAL_ATTRIBUTE2,
396 GLOBAL_ATTRIBUTE3,
397 GLOBAL_ATTRIBUTE4,
398 GLOBAL_ATTRIBUTE5,
399 GLOBAL_ATTRIBUTE6,
400 GLOBAL_ATTRIBUTE7,
401 GLOBAL_ATTRIBUTE8,
402 GLOBAL_ATTRIBUTE9,
403 GLOBAL_ATTRIBUTE10,
404 GLOBAL_ATTRIBUTE11,
405 GLOBAL_ATTRIBUTE12,
406 GLOBAL_ATTRIBUTE13,
407 GLOBAL_ATTRIBUTE14,
408 GLOBAL_ATTRIBUTE15,
409 GLOBAL_ATTRIBUTE16,
410 GLOBAL_ATTRIBUTE17,
411 GLOBAL_ATTRIBUTE18,
412 GLOBAL_ATTRIBUTE19,
413 GLOBAL_ATTRIBUTE20,
414 GLOBAL_ATTRIBUTE_CATEGORY,
415 PROGRAM_UPDATE_DATE,
416 CREATED_BY_MODULE,
417 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
418 CREATION_DATE,
419 LAST_UPDATE_DATE,
420 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
421 EXCHANGE_RATE_TYPE,
422 MIN_FC_INVOICE_OVERDUE_TYPE,
423 MIN_FC_INVOICE_PERCENT,
424 MIN_FC_BALANCE_OVERDUE_TYPE,
425 MIN_FC_BALANCE_PERCENT,
426 INTEREST_TYPE,
427 INTEREST_FIXED_AMOUNT,
428 INTEREST_SCHEDULE_ID,
429 PENALTY_TYPE,
430 PENALTY_RATE,
431 MIN_INTEREST_CHARGE,
432 PENALTY_FIXED_AMOUNT,
433 PENALTY_SCHEDULE_ID )
434 FROM HZ_CUST_PROFILE_AMTS
435 WHERE CUST_ACCOUNT_ID = P_CUST_ACCT_ID) AS HZ_CUST_PROFILE_AMT_OBJ_TBL))
436 FROM HZ_CUSTOMER_PROFILES
437 WHERE CUST_ACCOUNT_ID = P_CUST_ACCT_ID;
438
439
440 BEGIN
441
442
443 -- initialize API return status to success.
444 x_return_status := FND_API.G_RET_STS_SUCCESS;
445
446 -- Initialize message list if p_init_msg_list is set to TRUE
447 IF FND_API.to_Boolean(p_init_msg_list) THEN
448 FND_MSG_PUB.initialize;
449 END IF;
450
451
452 -- Debug info.
453 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
454 hz_utility_v2pub.debug(p_message=>'get_cust_profile_bo(+)',
455 p_prefix=>l_debug_prefix,
456 p_msg_level=>fnd_log.level_procedure);
457 END IF;
458
459 open c1;
460 fetch c1 into x_cust_profile_obj;
461 close c1;
462
463 -- Debug info.
464 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
465 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
466 p_msg_data=>x_msg_data,
467 p_msg_type=>'WARNING',
468 p_msg_level=>fnd_log.level_exception);
469 END IF;
470
471 -- Debug info.
472 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
473 hz_utility_v2pub.debug(p_message=>'get_cust_profile_bo (-)',
474 p_prefix=>l_debug_prefix,
475 p_msg_level=>fnd_log.level_procedure);
476 END IF;
477
478
479 EXCEPTION
480
481 WHEN fnd_api.g_exc_error THEN
482 x_return_status := fnd_api.g_ret_sts_error;
483
484 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
485 p_count => x_msg_count,
486 p_data => x_msg_data);
487
488 -- Debug info.
489 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
490 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
491 p_msg_data=>x_msg_data,
492 p_msg_type=>'ERROR',
493 p_msg_level=>fnd_log.level_error);
494 END IF;
495 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
496 hz_utility_v2pub.debug(p_message=>'get_cust_profile_bo (-)',
497 p_prefix=>l_debug_prefix,
498 p_msg_level=>fnd_log.level_procedure);
499 END IF;
500 WHEN fnd_api.g_exc_unexpected_error THEN
501 x_return_status := fnd_api.g_ret_sts_unexp_error;
502
503 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
504 p_count => x_msg_count,
505 p_data => x_msg_data);
506
507 -- Debug info.
508 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
509 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
510 p_msg_data=>x_msg_data,
511 p_msg_type=>'UNEXPECTED ERROR',
512 p_msg_level=>fnd_log.level_error);
513 END IF;
514 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
515 hz_utility_v2pub.debug(p_message=>'get_cust_profile_bo (-)',
516 p_prefix=>l_debug_prefix,
517 p_msg_level=>fnd_log.level_procedure);
518 END IF;
519 WHEN OTHERS THEN
520 x_return_status := fnd_api.g_ret_sts_unexp_error;
521
522 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
523 fnd_message.set_token('ERROR' ,SQLERRM);
524 fnd_msg_pub.add;
525
526 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
527 p_count => x_msg_count,
528 p_data => x_msg_data);
529
530 -- Debug info.
531 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
532 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
533 p_msg_data=>x_msg_data,
534 p_msg_type=>'SQL ERROR',
535 p_msg_level=>fnd_log.level_error);
536 END IF;
537 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
538 hz_utility_v2pub.debug(p_message=>'get_cust_profile_bo (-)',
539 p_prefix=>l_debug_prefix,
540 p_msg_level=>fnd_log.level_procedure);
541 END IF;
542
543 end;
544
545
546
547
548
549 --------------------------------------
550 --
551 -- PROCEDURE get_cust_acct_bo
552 --
553 -- DESCRIPTION
554 -- Get a logical customer account.
555 --
556 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
557 --
558 -- ARGUMENTS
559 -- IN:
560 -- p_init_msg_list Initialize message stack if it is set to
561 -- p_cust_acct_id customer account ID.
562 -- p_parent_id Parent Id.
563 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
564 -- OUT:
565 -- x_cust_acct_obj Logical customer account record.
566 -- x_return_status Return status after the call. The status can
567 -- be fnd_api.g_ret_sts_success (success),
568 -- fnd_api.g_ret_sts_error (error),
569 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
570 -- x_msg_count Number of messages in message stack.
571 -- x_msg_data Message text if x_msg_count is 1.
572 --
573 -- NOTES
574 --
575 -- MODIFICATION HISTORY
576 --
577 --
578 -- 8-JUN-2005 AWU Created.
579 --
580
581 /*
582
583 The Get customer account API Procedure is a retrieval service that returns full customer account business objects.
584 The user identifies a particular Organization Contact business object using the TCA identifier and/or the object's
585 Source System information. Upon proper validation of the object, the full Organization Contact business object is returned.
586 The object consists of all data included within the Organization Contact business object, at all embedded levels.
587 This includes the set of all data stored in the TCA tables for each embedded entity.
588
589
590 Embedded BO Mandatory Multiple Logical API Procedure Comments
591 Customer Account Site N Y get_cust_acct_site_bo
592 Customer Account Contact N Y get_cust_acct_contact_bo
593 Customer Profile Y N Business Structure. Included entities:
594 HZ_CUSTOMER_PROFILES, HZ_CUST_PROFILE_AMTS
595
596 To retrieve the appropriate embedded entities within the 'Organization Contact' business object, the Get procedure
597 returns all records for the particular contact from these TCA entity tables.
598
599 Embedded TCA Entity Mandatory Multiple TCA Table Entities
600
601 Customer Account Y N HZ_CUST_ACCOUNTS
602 Account Relationship N Y HZ_CUST_ACCT_RELATE
603 Bank Account Use N Y Owned by Payments team
604 Payment Method N N Owned by AR team
605
606 */
607
608
609
610 PROCEDURE get_cust_acct_bos(
611 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
612 p_parent_id IN NUMBER,
613 p_cust_acct_id IN NUMBER,
614 p_action_type IN VARCHAR2 := NULL,
615 x_cust_acct_objs OUT NOCOPY HZ_CUST_ACCT_BO_TBL,
616 x_return_status OUT NOCOPY VARCHAR2,
617 x_msg_count OUT NOCOPY NUMBER,
618 x_msg_data OUT NOCOPY VARCHAR2
619 ) is
620 l_debug_prefix VARCHAR2(30) := '';
621
622 CURSOR C1 IS
623 SELECT HZ_CUST_ACCT_BO(
624 P_ACTION_TYPE,
625 NULL, -- COMMON_OBJ_ID
626 CA.CUST_ACCOUNT_ID,
627 NULL, --ORIG_SYSTEM,
628 NULL, --ORIG_SYSTEM_REFERENCE,
629 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type('HZ_CUST_ACCOUNTS',CA.PARTY_ID),
630 CA.PARTY_ID,
631 CA.ACCOUNT_NUMBER,
632 CA.ATTRIBUTE_CATEGORY,
633 CA.ATTRIBUTE1,
634 CA.ATTRIBUTE2,
635 CA.ATTRIBUTE3,
636 CA.ATTRIBUTE4,
637 CA.ATTRIBUTE5,
638 CA.ATTRIBUTE6,
639 CA.ATTRIBUTE7,
640 CA.ATTRIBUTE8,
641 CA.ATTRIBUTE9,
642 CA.ATTRIBUTE10,
643 CA.ATTRIBUTE11,
644 CA.ATTRIBUTE12,
645 CA.ATTRIBUTE13,
646 CA.ATTRIBUTE14,
647 CA.ATTRIBUTE15,
648 CA.ATTRIBUTE16,
649 CA.ATTRIBUTE17,
650 CA.ATTRIBUTE18,
651 CA.ATTRIBUTE19,
652 CA.ATTRIBUTE20,
653 CA.GLOBAL_ATTRIBUTE_CATEGORY,
654 CA.GLOBAL_ATTRIBUTE1,
655 CA.GLOBAL_ATTRIBUTE2,
656 CA.GLOBAL_ATTRIBUTE3,
657 CA.GLOBAL_ATTRIBUTE4,
658 CA.GLOBAL_ATTRIBUTE5,
659 CA.GLOBAL_ATTRIBUTE6,
660 CA.GLOBAL_ATTRIBUTE7,
661 CA.GLOBAL_ATTRIBUTE8,
662 CA.GLOBAL_ATTRIBUTE9,
663 CA.GLOBAL_ATTRIBUTE10,
664 CA.GLOBAL_ATTRIBUTE11,
665 CA.GLOBAL_ATTRIBUTE12,
666 CA.GLOBAL_ATTRIBUTE13,
667 CA.GLOBAL_ATTRIBUTE14,
668 CA.GLOBAL_ATTRIBUTE15,
669 CA.GLOBAL_ATTRIBUTE16,
670 CA.GLOBAL_ATTRIBUTE17,
671 CA.GLOBAL_ATTRIBUTE18,
672 CA.GLOBAL_ATTRIBUTE19,
673 CA.GLOBAL_ATTRIBUTE20,
674 CA.STATUS,
675 CA.CUSTOMER_TYPE,
676 CA.CUSTOMER_CLASS_CODE,
677 CA.PRIMARY_SALESREP_ID,
678 CA.SALES_CHANNEL_CODE,
679 CA.ORDER_TYPE_ID,
680 CA.PRICE_LIST_ID,
681 CA.TAX_CODE,
682 CA.FOB_POINT,
683 CA.FREIGHT_TERM,
684 CA.SHIP_PARTIAL,
685 CA.SHIP_VIA,
686 CA.WAREHOUSE_ID,
687 CA.TAX_HEADER_LEVEL_FLAG,
688 CA.TAX_ROUNDING_RULE,
689 CA.COTERMINATE_DAY_MONTH,
690 CA.PRIMARY_SPECIALIST_ID,
691 CA.SECONDARY_SPECIALIST_ID,
692 CA.ACCOUNT_LIABLE_FLAG,
693 CA.CURRENT_BALANCE,
694 CA.ACCOUNT_ESTABLISHED_DATE,
695 CA.ACCOUNT_TERMINATION_DATE,
696 CA.ACCOUNT_ACTIVATION_DATE,
697 CA.DEPARTMENT,
698 CA.HELD_BILL_EXPIRATION_DATE,
699 CA.HOLD_BILL_FLAG,
700 CA.REALTIME_RATE_FLAG,
701 CA.ACCT_LIFE_CYCLE_STATUS,
702 CA.ACCOUNT_NAME,
703 CA.DEPOSIT_REFUND_METHOD,
704 CA.DORMANT_ACCOUNT_FLAG,
705 CA.NPA_NUMBER,
706 CA.SUSPENSION_DATE,
707 CA.SOURCE_CODE,
708 CA.COMMENTS,
709 CA.DATES_NEGATIVE_TOLERANCE,
710 CA.DATES_POSITIVE_TOLERANCE,
711 CA.DATE_TYPE_PREFERENCE,
712 CA.OVER_SHIPMENT_TOLERANCE,
713 CA.UNDER_SHIPMENT_TOLERANCE,
714 CA.OVER_RETURN_TOLERANCE,
715 CA.UNDER_RETURN_TOLERANCE,
716 CA.ITEM_CROSS_REF_PREF,
717 CA.SHIP_SETS_INCLUDE_LINES_FLAG,
718 CA.ARRIVALSETS_INCLUDE_LINES_FLAG,
719 CA.SCHED_DATE_PUSH_FLAG,
720 CA.INVOICE_QUANTITY_RULE,
721 CA.PRICING_EVENT,
722 CA.STATUS_UPDATE_DATE,
723 CA.AUTOPAY_FLAG,
724 CA.NOTIFY_FLAG,
725 CA.LAST_BATCH_ID,
726 CA.SELLING_PARTY_ID,
727 CA.PROGRAM_UPDATE_DATE,
728 CA.CREATED_BY_MODULE,
729 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CA.CREATED_BY),
730 CA.CREATION_DATE,
731 CA.LAST_UPDATE_DATE,
732 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CA.LAST_UPDATED_BY),
733 CAST(MULTISET (
734 SELECT HZ_ORIG_SYS_REF_OBJ(
735 NULL, --P_ACTION_TYPE,
736 ORIG_SYSTEM_REF_ID,
737 ORIG_SYSTEM,
738 ORIG_SYSTEM_REFERENCE,
739 OWNER_TABLE_NAME,
740 OWNER_TABLE_ID,
741 STATUS,
742 REASON_CODE,
743 OLD_ORIG_SYSTEM_REFERENCE,
744 START_DATE_ACTIVE,
745 END_DATE_ACTIVE,
746 PROGRAM_UPDATE_DATE,
747 CREATED_BY_MODULE,
748 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
749 CREATION_DATE,
750 LAST_UPDATE_DATE,
751 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
752 ATTRIBUTE_CATEGORY,
753 ATTRIBUTE1,
754 ATTRIBUTE2,
755 ATTRIBUTE3,
756 ATTRIBUTE4,
757 ATTRIBUTE5,
758 ATTRIBUTE6,
759 ATTRIBUTE7,
760 ATTRIBUTE8,
761 ATTRIBUTE9,
762 ATTRIBUTE10,
763 ATTRIBUTE11,
764 ATTRIBUTE12,
765 ATTRIBUTE13,
766 ATTRIBUTE14,
767 ATTRIBUTE15,
768 ATTRIBUTE16,
769 ATTRIBUTE17,
770 ATTRIBUTE18,
771 ATTRIBUTE19,
772 ATTRIBUTE20)
773 FROM HZ_ORIG_SYS_REFERENCES OSR
774 WHERE
775 OSR.OWNER_TABLE_ID = CA.CUST_ACCOUNT_ID
776 AND OWNER_TABLE_NAME = 'HZ_CUST_ACCOUNTS'
777 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL),
778 HZ_CUST_ACCT_RELATE_OBJ_TBL(),
779 HZ_CUST_ACCT_SITE_BO_TBL(),
780 HZ_CUST_ACCT_CONTACT_BO_TBL(),
781 NULL, --CUST_PROFILE_OBJ,
782 CAST(MULTISET (
783 SELECT HZ_BANK_ACCT_USE_OBJ(
784 P_ACTION_TYPE,
785 NULL, -- COMMON_OBJ_ID
786 INSTR_ASSIGNMENT_ID, --BANK_ACCT_USE_ID,
787 PAYMENT_FUNCTION,
788 PARTY_ID,
789 ORG_TYPE,
790 ORG_ID,
791 CUST_ACCOUNT_ID,
792 ACCT_SITE_USE_ID,
793 INSTRUMENT_ID,
794 INSTRUMENT_TYPE,
795 ORDER_OF_PREFERENCE,
796 ASSIGNMENT_START_DATE,
797 ASSIGNMENT_END_DATE,
798 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
799 CREATION_DATE,
800 ASSIGNMENT_LAST_UPDATE,
801 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
802 FROM IBY_FNDCPT_PAYER_ASSGN_INSTR_V
803 WHERE CUST_ACCOUNT_ID = CA.CUST_ACCOUNT_ID) AS HZ_BANK_ACCT_USE_OBJ_TBL),
804 (SELECT HZ_PAYMENT_METHOD_OBJ(
805 P_ACTION_TYPE,
806 NULL, -- COMMON_OBJ_ID
807 CRM.CUST_RECEIPT_METHOD_ID, --PAYMENT_METHOD_ID,
808 CRM.CUSTOMER_ID,
809 CRM.RECEIPT_METHOD_ID,
810 CRM.PRIMARY_FLAG,
811 CRM.SITE_USE_ID,
812 CRM.START_DATE,
813 CRM.END_DATE,
814 CRM.ATTRIBUTE_CATEGORY,
815 CRM.ATTRIBUTE1,
816 CRM.ATTRIBUTE2,
817 CRM.ATTRIBUTE3,
818 CRM.ATTRIBUTE4,
819 CRM.ATTRIBUTE5,
820 CRM.ATTRIBUTE6,
821 CRM.ATTRIBUTE7,
822 CRM.ATTRIBUTE8,
823 CRM.ATTRIBUTE9,
824 CRM.ATTRIBUTE10,
825 CRM.ATTRIBUTE11,
826 CRM.ATTRIBUTE12,
827 CRM.ATTRIBUTE13,
828 CRM.ATTRIBUTE14,
829 CRM.ATTRIBUTE15,
830 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CRM.CREATED_BY),
831 CRM.CREATION_DATE,
832 CRM.LAST_UPDATE_DATE,
833 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CRM.LAST_UPDATED_BY))
834 FROM RA_CUST_RECEIPT_METHODS CRM
835 WHERE CA.CUST_ACCOUNT_ID = CRM.CUSTOMER_ID
836 AND ROWNUM = 1)
837 )
838 FROM HZ_PARTIES P, HZ_CUST_ACCOUNTS CA
839 WHERE P.PARTY_ID = CA.PARTY_ID
840 AND ((P_CUST_ACCT_ID IS NULL AND P.PARTY_ID = P_PARENT_ID)
841 OR (P_CUST_ACCT_ID IS NOT NULL AND CA.CUST_ACCOUNT_ID = P_CUST_ACCT_ID));
842
843
844 BEGIN
845
846 -- initialize API return status to success.
847 x_return_status := FND_API.G_RET_STS_SUCCESS;
848
849 -- Initialize message list if p_init_msg_list is set to TRUE
850 IF FND_API.to_Boolean(p_init_msg_list) THEN
851 FND_MSG_PUB.initialize;
852 END IF;
853
854
855 -- Debug info.
856 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
857 hz_utility_v2pub.debug(p_message=>'get_cust_acct_bos(+)',
858 p_prefix=>l_debug_prefix,
859 p_msg_level=>fnd_log.level_procedure);
860 END IF;
861
862 x_cust_acct_objs := HZ_CUST_ACCT_BO_TBL();
863 open c1;
864 fetch c1 BULK COLLECT into x_cust_acct_objs;
865 close c1;
866
867 for i in 1..x_cust_acct_objs.count loop
868 HZ_EXTRACT_ACCT_SITE_BO_PVT.get_cust_acct_site_bos(
869 p_init_msg_list => fnd_api.g_false,
870 p_parent_id => x_cust_acct_objs(i).cust_acct_id,
871 p_cust_acct_site_id => NULL,
872 p_action_type => p_action_type,
873 x_cust_acct_site_objs => x_cust_acct_objs(i).cust_acct_site_objs,
874 x_return_status => x_return_status,
875 x_msg_count => x_msg_count,
876 x_msg_data => x_msg_data);
877
878 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
879 RAISE FND_API.G_EXC_ERROR;
880 END IF;
881
882 HZ_EXTRACT_ACCT_CONT_BO_PVT.get_cust_acct_contact_bos(
883 p_init_msg_list => fnd_api.g_false,
884 p_parent_id => x_cust_acct_objs(i).cust_acct_id,
885 p_cust_acct_contact_id => NULL,
886 p_action_type => p_action_type,
887 x_cust_acct_contact_objs => x_cust_acct_objs(i).cust_acct_contact_objs,
888 x_return_status => x_return_status,
889 x_msg_count => x_msg_count,
890 x_msg_data => x_msg_data);
891
892 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
893 RAISE FND_API.G_EXC_ERROR;
894 END IF;
895
896
897 get_cust_profile_bo(
898 p_init_msg_list => fnd_api.g_false,
899 p_cust_acct_id => x_cust_acct_objs(i).cust_acct_id,
900 p_action_type => p_action_type,
901 x_cust_profile_obj => x_cust_acct_objs(i).cust_profile_obj,
902 x_return_status => x_return_status,
903 x_msg_count => x_msg_count,
904 x_msg_data => x_msg_data);
905
906 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
907 RAISE FND_API.G_EXC_ERROR;
908 END IF;
909
910 get_cust_acct_relate_objs(
911 p_init_msg_list => fnd_api.g_false,
912 p_cust_acct_id => x_cust_acct_objs(i).cust_acct_id,
913 p_action_type => p_action_type,
914 x_cust_acct_relate_objs => x_cust_acct_objs(i).acct_relate_objs,
915 x_return_status => x_return_status,
916 x_msg_count => x_msg_count,
917 x_msg_data => x_msg_data);
918
919 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
920 RAISE FND_API.G_EXC_ERROR;
921 END IF;
922
923
924 end loop;
925
926 -- Debug info.
927 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
928 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
929 p_msg_data=>x_msg_data,
930 p_msg_type=>'WARNING',
931 p_msg_level=>fnd_log.level_exception);
932 END IF;
933
934 -- Debug info.
935 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
936 hz_utility_v2pub.debug(p_message=>'get_cust_acct_bos (-)',
937 p_prefix=>l_debug_prefix,
938 p_msg_level=>fnd_log.level_procedure);
939 END IF;
940
941
942 EXCEPTION
943
944 WHEN fnd_api.g_exc_error THEN
945 x_return_status := fnd_api.g_ret_sts_error;
946
947 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
948 p_count => x_msg_count,
949 p_data => x_msg_data);
950
951 -- Debug info.
952 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
953 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
954 p_msg_data=>x_msg_data,
955 p_msg_type=>'ERROR',
956 p_msg_level=>fnd_log.level_error);
957 END IF;
958 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
959 hz_utility_v2pub.debug(p_message=>'get_cust_acct_bos (-)',
960 p_prefix=>l_debug_prefix,
961 p_msg_level=>fnd_log.level_procedure);
962 END IF;
963 WHEN fnd_api.g_exc_unexpected_error THEN
964 x_return_status := fnd_api.g_ret_sts_unexp_error;
965
966 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
967 p_count => x_msg_count,
968 p_data => x_msg_data);
969
970 -- Debug info.
971 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
972 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
973 p_msg_data=>x_msg_data,
974 p_msg_type=>'UNEXPECTED ERROR',
975 p_msg_level=>fnd_log.level_error);
976 END IF;
977 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
978 hz_utility_v2pub.debug(p_message=>'get_cust_acct_bos (-)',
979 p_prefix=>l_debug_prefix,
980 p_msg_level=>fnd_log.level_procedure);
981 END IF;
982 WHEN OTHERS THEN
983 x_return_status := fnd_api.g_ret_sts_unexp_error;
984
985 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
986 fnd_message.set_token('ERROR' ,SQLERRM);
987 fnd_msg_pub.add;
988
989 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
990 p_count => x_msg_count,
991 p_data => x_msg_data);
992
993 -- Debug info.
994 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
995 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
996 p_msg_data=>x_msg_data,
997 p_msg_type=>'SQL ERROR',
998 p_msg_level=>fnd_log.level_error);
999 END IF;
1000 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1001 hz_utility_v2pub.debug(p_message=>'get_cust_acct_bos (-)',
1002 p_prefix=>l_debug_prefix,
1003 p_msg_level=>fnd_log.level_procedure);
1004 END IF;
1005
1006 end;
1007
1008
1009 --------------------------------------
1010 --
1011 -- PROCEDURE get_cust_acct_v2_bo
1012 --
1013 -- DESCRIPTION
1014 -- Get a logical customer account.
1015 --
1016 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1017 --
1018 -- ARGUMENTS
1019 -- IN:
1020 -- p_init_msg_list Initialize message stack if it is set to
1021 -- p_cust_acct_id customer account ID.
1022 -- p_parent_id Parent Id.
1023 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
1024 -- OUT:
1025 -- x_cust_acct_v2_obj Logical customer account record.
1026 -- x_return_status Return status after the call. The status can
1027 -- be fnd_api.g_ret_sts_success (success),
1028 -- fnd_api.g_ret_sts_error (error),
1029 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1030 -- x_msg_count Number of messages in message stack.
1031 -- x_msg_data Message text if x_msg_count is 1.
1032 --
1033 -- NOTES
1034 --
1035 -- MODIFICATION HISTORY
1036 --
1037 --
1038 -- 1-FEB-2008 VSEGU Created.
1039 --
1040
1041 /*
1042
1043 The Get customer account API Procedure is a retrieval service that returns full customer account business objects.
1044 The user identifies a particular Organization Contact business object using the TCA identifier and/or the object's
1045 Source System information. Upon proper validation of the object, the full Organization Contact business object is returned.
1046 The object consists of all data included within the Organization Contact business object, at all embedded levels.
1047 This includes the set of all data stored in the TCA tables for each embedded entity.
1048
1049
1050 Embedded BO Mandatory Multiple Logical API Procedure Comments
1051 Customer Account Site N Y get_cust_acct_site_v2_bo
1052 Customer Account Contact N Y get_cust_acct_contact_bo
1053 Customer Profile Y N Business Structure. Included entities:
1054 HZ_CUSTOMER_PROFILES, HZ_CUST_PROFILE_AMTS
1055
1056 To retrieve the appropriate embedded entities within the 'Organization Contact' business object, the Get procedure
1057 returns all records for the particular contact from these TCA entity tables.
1058
1059 Embedded TCA Entity Mandatory Multiple TCA Table Entities
1060
1061 Customer Account Y N HZ_CUST_ACCOUNTS
1062 Account Relationship N Y HZ_CUST_ACCT_RELATE
1063 Bank Account Use N Y Owned by Payments team
1064 Payment Method N N Owned by AR team
1065
1066 */
1067
1068
1069
1070 PROCEDURE get_cust_acct_v2_bos(
1071 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1072 p_parent_id IN NUMBER,
1073 p_cust_acct_id IN NUMBER,
1074 p_action_type IN VARCHAR2 := NULL,
1075 x_cust_acct_v2_objs OUT NOCOPY HZ_CUST_ACCT_V2_BO_TBL,
1076 x_return_status OUT NOCOPY VARCHAR2,
1077 x_msg_count OUT NOCOPY NUMBER,
1078 x_msg_data OUT NOCOPY VARCHAR2
1079 ) is
1080 l_debug_prefix VARCHAR2(30) := '';
1081
1082 CURSOR C1 IS
1083 SELECT HZ_CUST_ACCT_V2_BO(
1084 P_ACTION_TYPE,
1085 NULL, -- COMMON_OBJ_ID
1086 CA.CUST_ACCOUNT_ID,
1087 NULL, --ORIG_SYSTEM,
1088 NULL, --ORIG_SYSTEM_REFERENCE,
1089 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type('HZ_CUST_ACCOUNTS',CA.PARTY_ID),
1090 CA.PARTY_ID,
1091 CA.ACCOUNT_NUMBER,
1092 CA.ATTRIBUTE_CATEGORY,
1093 CA.ATTRIBUTE1,
1094 CA.ATTRIBUTE2,
1095 CA.ATTRIBUTE3,
1096 CA.ATTRIBUTE4,
1097 CA.ATTRIBUTE5,
1098 CA.ATTRIBUTE6,
1099 CA.ATTRIBUTE7,
1100 CA.ATTRIBUTE8,
1101 CA.ATTRIBUTE9,
1102 CA.ATTRIBUTE10,
1103 CA.ATTRIBUTE11,
1104 CA.ATTRIBUTE12,
1105 CA.ATTRIBUTE13,
1106 CA.ATTRIBUTE14,
1107 CA.ATTRIBUTE15,
1108 CA.ATTRIBUTE16,
1109 CA.ATTRIBUTE17,
1110 CA.ATTRIBUTE18,
1111 CA.ATTRIBUTE19,
1112 CA.ATTRIBUTE20,
1113 CA.GLOBAL_ATTRIBUTE_CATEGORY,
1114 CA.GLOBAL_ATTRIBUTE1,
1115 CA.GLOBAL_ATTRIBUTE2,
1116 CA.GLOBAL_ATTRIBUTE3,
1117 CA.GLOBAL_ATTRIBUTE4,
1118 CA.GLOBAL_ATTRIBUTE5,
1119 CA.GLOBAL_ATTRIBUTE6,
1120 CA.GLOBAL_ATTRIBUTE7,
1121 CA.GLOBAL_ATTRIBUTE8,
1122 CA.GLOBAL_ATTRIBUTE9,
1123 CA.GLOBAL_ATTRIBUTE10,
1124 CA.GLOBAL_ATTRIBUTE11,
1125 CA.GLOBAL_ATTRIBUTE12,
1126 CA.GLOBAL_ATTRIBUTE13,
1127 CA.GLOBAL_ATTRIBUTE14,
1128 CA.GLOBAL_ATTRIBUTE15,
1129 CA.GLOBAL_ATTRIBUTE16,
1130 CA.GLOBAL_ATTRIBUTE17,
1131 CA.GLOBAL_ATTRIBUTE18,
1132 CA.GLOBAL_ATTRIBUTE19,
1133 CA.GLOBAL_ATTRIBUTE20,
1134 CA.STATUS,
1135 CA.CUSTOMER_TYPE,
1136 CA.CUSTOMER_CLASS_CODE,
1137 CA.PRIMARY_SALESREP_ID,
1138 CA.SALES_CHANNEL_CODE,
1139 CA.ORDER_TYPE_ID,
1140 CA.PRICE_LIST_ID,
1141 CA.TAX_CODE,
1142 CA.FOB_POINT,
1143 CA.FREIGHT_TERM,
1144 CA.SHIP_PARTIAL,
1145 CA.SHIP_VIA,
1146 CA.WAREHOUSE_ID,
1147 CA.TAX_HEADER_LEVEL_FLAG,
1148 CA.TAX_ROUNDING_RULE,
1149 CA.COTERMINATE_DAY_MONTH,
1150 CA.PRIMARY_SPECIALIST_ID,
1151 CA.SECONDARY_SPECIALIST_ID,
1152 CA.ACCOUNT_LIABLE_FLAG,
1153 CA.CURRENT_BALANCE,
1154 CA.ACCOUNT_ESTABLISHED_DATE,
1155 CA.ACCOUNT_TERMINATION_DATE,
1156 CA.ACCOUNT_ACTIVATION_DATE,
1157 CA.DEPARTMENT,
1158 CA.HELD_BILL_EXPIRATION_DATE,
1159 CA.HOLD_BILL_FLAG,
1160 CA.REALTIME_RATE_FLAG,
1161 CA.ACCT_LIFE_CYCLE_STATUS,
1162 CA.ACCOUNT_NAME,
1163 CA.DEPOSIT_REFUND_METHOD,
1164 CA.DORMANT_ACCOUNT_FLAG,
1165 CA.NPA_NUMBER,
1166 CA.SUSPENSION_DATE,
1167 CA.SOURCE_CODE,
1168 CA.COMMENTS,
1169 CA.DATES_NEGATIVE_TOLERANCE,
1170 CA.DATES_POSITIVE_TOLERANCE,
1171 CA.DATE_TYPE_PREFERENCE,
1172 CA.OVER_SHIPMENT_TOLERANCE,
1173 CA.UNDER_SHIPMENT_TOLERANCE,
1174 CA.OVER_RETURN_TOLERANCE,
1175 CA.UNDER_RETURN_TOLERANCE,
1176 CA.ITEM_CROSS_REF_PREF,
1177 CA.SHIP_SETS_INCLUDE_LINES_FLAG,
1178 CA.ARRIVALSETS_INCLUDE_LINES_FLAG,
1179 CA.SCHED_DATE_PUSH_FLAG,
1180 CA.INVOICE_QUANTITY_RULE,
1181 CA.PRICING_EVENT,
1182 CA.STATUS_UPDATE_DATE,
1183 CA.AUTOPAY_FLAG,
1184 CA.NOTIFY_FLAG,
1185 CA.LAST_BATCH_ID,
1186 CA.SELLING_PARTY_ID,
1187 CA.PROGRAM_UPDATE_DATE,
1188 CA.CREATED_BY_MODULE,
1189 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CA.CREATED_BY),
1190 CA.CREATION_DATE,
1191 CA.LAST_UPDATE_DATE,
1192 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CA.LAST_UPDATED_BY),
1193 CAST(MULTISET (
1194 SELECT HZ_ORIG_SYS_REF_OBJ(
1195 NULL, --P_ACTION_TYPE,
1196 ORIG_SYSTEM_REF_ID,
1197 ORIG_SYSTEM,
1198 ORIG_SYSTEM_REFERENCE,
1199 OWNER_TABLE_NAME,
1200 OWNER_TABLE_ID,
1201 STATUS,
1202 REASON_CODE,
1203 OLD_ORIG_SYSTEM_REFERENCE,
1204 START_DATE_ACTIVE,
1205 END_DATE_ACTIVE,
1206 PROGRAM_UPDATE_DATE,
1207 CREATED_BY_MODULE,
1208 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
1209 CREATION_DATE,
1210 LAST_UPDATE_DATE,
1211 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
1212 ATTRIBUTE_CATEGORY,
1213 ATTRIBUTE1,
1214 ATTRIBUTE2,
1215 ATTRIBUTE3,
1216 ATTRIBUTE4,
1217 ATTRIBUTE5,
1218 ATTRIBUTE6,
1219 ATTRIBUTE7,
1220 ATTRIBUTE8,
1221 ATTRIBUTE9,
1222 ATTRIBUTE10,
1223 ATTRIBUTE11,
1224 ATTRIBUTE12,
1225 ATTRIBUTE13,
1226 ATTRIBUTE14,
1227 ATTRIBUTE15,
1228 ATTRIBUTE16,
1229 ATTRIBUTE17,
1230 ATTRIBUTE18,
1231 ATTRIBUTE19,
1232 ATTRIBUTE20)
1233 FROM HZ_ORIG_SYS_REFERENCES OSR
1234 WHERE
1235 OSR.OWNER_TABLE_ID = CA.CUST_ACCOUNT_ID
1236 AND OWNER_TABLE_NAME = 'HZ_CUST_ACCOUNTS'
1237 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL),
1238 HZ_CUST_ACCT_RELATE_OBJ_TBL(),
1239 HZ_CUST_ACCT_SITE_V2_BO_TBL(),
1240 HZ_CUST_ACCT_CONTACT_BO_TBL(),
1241 NULL, --CUST_PROFILE_OBJ,
1242 CAST(MULTISET (
1243 SELECT HZ_BANK_ACCT_USE_OBJ(
1244 P_ACTION_TYPE,
1245 NULL, -- COMMON_OBJ_ID
1246 INSTR_ASSIGNMENT_ID, --BANK_ACCT_USE_ID,
1247 PAYMENT_FUNCTION,
1248 PARTY_ID,
1249 ORG_TYPE,
1250 ORG_ID,
1251 CUST_ACCOUNT_ID,
1252 ACCT_SITE_USE_ID,
1253 INSTRUMENT_ID,
1254 INSTRUMENT_TYPE,
1255 ORDER_OF_PREFERENCE,
1256 ASSIGNMENT_START_DATE,
1257 ASSIGNMENT_END_DATE,
1258 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
1259 CREATION_DATE,
1260 ASSIGNMENT_LAST_UPDATE,
1261 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
1262 FROM IBY_FNDCPT_PAYER_ASSGN_INSTR_V
1263 WHERE CUST_ACCOUNT_ID = CA.CUST_ACCOUNT_ID) AS HZ_BANK_ACCT_USE_OBJ_TBL),
1264 CAST( MULTISET ( SELECT HZ_PAYMENT_METHOD_OBJ(
1265 P_ACTION_TYPE,
1266 NULL, -- COMMON_OBJ_ID
1267 CRM.CUST_RECEIPT_METHOD_ID, --PAYMENT_METHOD_ID,
1268 CRM.CUSTOMER_ID,
1269 CRM.RECEIPT_METHOD_ID,
1270 CRM.PRIMARY_FLAG,
1271 CRM.SITE_USE_ID,
1272 CRM.START_DATE,
1273 CRM.END_DATE,
1274 CRM.ATTRIBUTE_CATEGORY,
1275 CRM.ATTRIBUTE1,
1276 CRM.ATTRIBUTE2,
1277 CRM.ATTRIBUTE3,
1278 CRM.ATTRIBUTE4,
1279 CRM.ATTRIBUTE5,
1280 CRM.ATTRIBUTE6,
1281 CRM.ATTRIBUTE7,
1282 CRM.ATTRIBUTE8,
1283 CRM.ATTRIBUTE9,
1284 CRM.ATTRIBUTE10,
1285 CRM.ATTRIBUTE11,
1286 CRM.ATTRIBUTE12,
1287 CRM.ATTRIBUTE13,
1288 CRM.ATTRIBUTE14,
1289 CRM.ATTRIBUTE15,
1290 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CRM.CREATED_BY),
1291 CRM.CREATION_DATE,
1292 CRM.LAST_UPDATE_DATE,
1293 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CRM.LAST_UPDATED_BY))
1294 FROM RA_CUST_RECEIPT_METHODS CRM
1295 WHERE CA.CUST_ACCOUNT_ID = CRM.CUSTOMER_ID ) AS HZ_PAYMENT_METHOD_OBJ_TBL )
1296 )
1297 FROM HZ_PARTIES P, HZ_CUST_ACCOUNTS CA
1298 WHERE P.PARTY_ID = CA.PARTY_ID
1299 AND ((P_CUST_ACCT_ID IS NULL AND P.PARTY_ID = P_PARENT_ID)
1300 OR (P_CUST_ACCT_ID IS NOT NULL AND CA.CUST_ACCOUNT_ID = P_CUST_ACCT_ID));
1301
1302
1303 BEGIN
1304
1305 -- initialize API return status to success.
1306 x_return_status := FND_API.G_RET_STS_SUCCESS;
1307
1308 -- Initialize message list if p_init_msg_list is set to TRUE
1309 IF FND_API.to_Boolean(p_init_msg_list) THEN
1310 FND_MSG_PUB.initialize;
1311 END IF;
1312
1313
1314 -- Debug info.
1315 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1316 hz_utility_v2pub.debug(p_message=>'get_cust_acct_v2_bos(+)',
1317 p_prefix=>l_debug_prefix,
1318 p_msg_level=>fnd_log.level_procedure);
1319 END IF;
1320
1321 x_cust_acct_v2_objs := HZ_CUST_ACCT_V2_BO_TBL();
1322 open c1;
1323 fetch c1 BULK COLLECT into x_cust_acct_v2_objs;
1324 close c1;
1325
1326 for i in 1..x_cust_acct_v2_objs.count loop
1327 HZ_EXTRACT_ACCT_SITE_BO_PVT.get_cust_acct_site_v2_bos(
1328 p_init_msg_list => fnd_api.g_false,
1329 p_parent_id => x_cust_acct_v2_objs(i).cust_acct_id,
1330 p_cust_acct_site_id => NULL,
1331 p_action_type => p_action_type,
1332 x_cust_acct_site_v2_objs => x_cust_acct_v2_objs(i).cust_acct_site_objs,
1333 x_return_status => x_return_status,
1334 x_msg_count => x_msg_count,
1335 x_msg_data => x_msg_data);
1336
1337 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1338 RAISE FND_API.G_EXC_ERROR;
1339 END IF;
1340
1341 HZ_EXTRACT_ACCT_CONT_BO_PVT.get_cust_acct_contact_bos(
1342 p_init_msg_list => fnd_api.g_false,
1343 p_parent_id => x_cust_acct_v2_objs(i).cust_acct_id,
1344 p_cust_acct_contact_id => NULL,
1345 p_action_type => p_action_type,
1346 x_cust_acct_contact_objs => x_cust_acct_v2_objs(i).cust_acct_contact_objs,
1347 x_return_status => x_return_status,
1348 x_msg_count => x_msg_count,
1349 x_msg_data => x_msg_data);
1350
1351 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1352 RAISE FND_API.G_EXC_ERROR;
1353 END IF;
1354
1355
1356 get_cust_profile_bo(
1357 p_init_msg_list => fnd_api.g_false,
1358 p_cust_acct_id => x_cust_acct_v2_objs(i).cust_acct_id,
1359 p_action_type => p_action_type,
1360 x_cust_profile_obj => x_cust_acct_v2_objs(i).cust_profile_obj,
1361 x_return_status => x_return_status,
1362 x_msg_count => x_msg_count,
1363 x_msg_data => x_msg_data);
1364
1365 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1366 RAISE FND_API.G_EXC_ERROR;
1367 END IF;
1368
1369 get_cust_acct_relate_objs(
1370 p_init_msg_list => fnd_api.g_false,
1371 p_cust_acct_id => x_cust_acct_v2_objs(i).cust_acct_id,
1372 p_action_type => p_action_type,
1373 x_cust_acct_relate_objs => x_cust_acct_v2_objs(i).acct_relate_objs,
1374 x_return_status => x_return_status,
1375 x_msg_count => x_msg_count,
1376 x_msg_data => x_msg_data);
1377
1378 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1379 RAISE FND_API.G_EXC_ERROR;
1380 END IF;
1381
1382
1383 end loop;
1384
1385 -- Debug info.
1386 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1387 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1388 p_msg_data=>x_msg_data,
1389 p_msg_type=>'WARNING',
1390 p_msg_level=>fnd_log.level_exception);
1391 END IF;
1392
1393 -- Debug info.
1394 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1395 hz_utility_v2pub.debug(p_message=>'get_cust_acct_v2_bos (-)',
1396 p_prefix=>l_debug_prefix,
1397 p_msg_level=>fnd_log.level_procedure);
1398 END IF;
1399
1400
1401 EXCEPTION
1402
1403 WHEN fnd_api.g_exc_error THEN
1404 x_return_status := fnd_api.g_ret_sts_error;
1405
1406 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1407 p_count => x_msg_count,
1408 p_data => x_msg_data);
1409
1410 -- Debug info.
1411 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1412 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1413 p_msg_data=>x_msg_data,
1414 p_msg_type=>'ERROR',
1415 p_msg_level=>fnd_log.level_error);
1416 END IF;
1417 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1418 hz_utility_v2pub.debug(p_message=>'get_cust_acct_v2_bos (-)',
1419 p_prefix=>l_debug_prefix,
1420 p_msg_level=>fnd_log.level_procedure);
1421 END IF;
1422 WHEN fnd_api.g_exc_unexpected_error THEN
1423 x_return_status := fnd_api.g_ret_sts_unexp_error;
1424
1425 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1426 p_count => x_msg_count,
1427 p_data => x_msg_data);
1428
1429 -- Debug info.
1430 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1431 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1432 p_msg_data=>x_msg_data,
1433 p_msg_type=>'UNEXPECTED ERROR',
1434 p_msg_level=>fnd_log.level_error);
1435 END IF;
1436 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1437 hz_utility_v2pub.debug(p_message=>'get_cust_acct_v2_bos (-)',
1438 p_prefix=>l_debug_prefix,
1439 p_msg_level=>fnd_log.level_procedure);
1440 END IF;
1441 WHEN OTHERS THEN
1442 x_return_status := fnd_api.g_ret_sts_unexp_error;
1443
1444 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1445 fnd_message.set_token('ERROR' ,SQLERRM);
1446 fnd_msg_pub.add;
1447
1448 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1449 p_count => x_msg_count,
1450 p_data => x_msg_data);
1451
1452 -- Debug info.
1453 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1454 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1455 p_msg_data=>x_msg_data,
1456 p_msg_type=>'SQL ERROR',
1457 p_msg_level=>fnd_log.level_error);
1458 END IF;
1459 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1460 hz_utility_v2pub.debug(p_message=>'get_cust_acct_v2_bos (-)',
1461 p_prefix=>l_debug_prefix,
1462 p_msg_level=>fnd_log.level_procedure);
1463 END IF;
1464
1465 end;
1466
1467
1468 END HZ_EXTRACT_CUST_ACCT_BO_PVT;