[Home] [Help]
PACKAGE BODY: APPS.HZ_EXTRACT_ACCT_SITE_BO_PVT
Source
1 PACKAGE BODY HZ_EXTRACT_ACCT_SITE_BO_PVT AS
2 /*$Header: ARHECSVB.pls 120.7 2008/02/06 10:27:08 vsegu ship $ */
3 /*
4 * This package contains the private APIs for logical customer account sites.
5 * @rep:scope private
6 * @rep:product HZ
7 * @rep:displayname customer account site
8 * @rep:category BUSINESS_ENTITY HZ_PARTIES
9 * @rep:lifecycle active
10 * @rep:doccd 115hztig.pdf customer account site Get APIs
11 */
12
13 -- Private local procedure
14 PROCEDURE get_site_use_profile_bo(
15 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
16 p_site_use_id IN NUMBER,
17 p_action_type IN VARCHAR2 := NULL,
18 x_cust_profile_obj OUT NOCOPY HZ_CUSTOMER_PROFILE_BO,
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_CUSTOMER_PROFILE_BO(
27 P_ACTION_TYPE,
28 NULL, -- COMMON_OBJ_ID
29 CUST_ACCOUNT_PROFILE_ID,
30 CUST_ACCOUNT_ID,
31 STATUS,
32 COLLECTOR_ID,
33 CREDIT_ANALYST_ID,
34 CREDIT_CHECKING,
35 NEXT_CREDIT_REVIEW_DATE,
36 TOLERANCE,
37 DISCOUNT_TERMS,
38 DUNNING_LETTERS,
39 INTEREST_CHARGES,
40 SEND_STATEMENTS,
41 CREDIT_BALANCE_STATEMENTS,
42 CREDIT_HOLD,
43 PROFILE_CLASS_ID,
44 SITE_USE_ID,
45 CREDIT_RATING,
46 RISK_CODE,
47 STANDARD_TERMS,
48 OVERRIDE_TERMS,
49 DUNNING_LETTER_SET_ID,
50 INTEREST_PERIOD_DAYS,
51 PAYMENT_GRACE_DAYS,
52 DISCOUNT_GRACE_DAYS,
53 STATEMENT_CYCLE_ID,
54 ACCOUNT_STATUS,
55 PERCENT_COLLECTABLE,
56 AUTOCASH_HIERARCHY_ID,
57 ATTRIBUTE_CATEGORY,
58 ATTRIBUTE1,
59 ATTRIBUTE2,
60 ATTRIBUTE3,
61 ATTRIBUTE4,
62 ATTRIBUTE5,
63 ATTRIBUTE6,
64 ATTRIBUTE7,
65 ATTRIBUTE8,
66 ATTRIBUTE9,
67 ATTRIBUTE10,
68 ATTRIBUTE11,
69 ATTRIBUTE12,
70 ATTRIBUTE13,
71 ATTRIBUTE14,
72 ATTRIBUTE15,
73 AUTO_REC_INCL_DISPUTED_FLAG,
74 TAX_PRINTING_OPTION,
75 CHARGE_ON_FINANCE_CHARGE_FLAG,
76 GROUPING_RULE_ID,
77 CLEARING_DAYS,
78 JGZZ_ATTRIBUTE_CATEGORY,
79 JGZZ_ATTRIBUTE1,
80 JGZZ_ATTRIBUTE2,
81 JGZZ_ATTRIBUTE3,
82 JGZZ_ATTRIBUTE4,
83 JGZZ_ATTRIBUTE5,
84 JGZZ_ATTRIBUTE6,
85 JGZZ_ATTRIBUTE7,
86 JGZZ_ATTRIBUTE8,
87 JGZZ_ATTRIBUTE9,
88 JGZZ_ATTRIBUTE10,
89 JGZZ_ATTRIBUTE11,
90 JGZZ_ATTRIBUTE12,
91 JGZZ_ATTRIBUTE13,
92 JGZZ_ATTRIBUTE14,
93 JGZZ_ATTRIBUTE15,
94 GLOBAL_ATTRIBUTE1,
95 GLOBAL_ATTRIBUTE2,
96 GLOBAL_ATTRIBUTE3,
97 GLOBAL_ATTRIBUTE4,
98 GLOBAL_ATTRIBUTE5,
99 GLOBAL_ATTRIBUTE6,
100 GLOBAL_ATTRIBUTE7,
101 GLOBAL_ATTRIBUTE8,
102 GLOBAL_ATTRIBUTE9,
103 GLOBAL_ATTRIBUTE10,
104 GLOBAL_ATTRIBUTE11,
105 GLOBAL_ATTRIBUTE12,
106 GLOBAL_ATTRIBUTE13,
107 GLOBAL_ATTRIBUTE14,
108 GLOBAL_ATTRIBUTE15,
109 GLOBAL_ATTRIBUTE16,
110 GLOBAL_ATTRIBUTE17,
111 GLOBAL_ATTRIBUTE18,
112 GLOBAL_ATTRIBUTE19,
113 GLOBAL_ATTRIBUTE20,
114 GLOBAL_ATTRIBUTE_CATEGORY,
115 CONS_INV_FLAG,
116 CONS_INV_TYPE,
117 AUTOCASH_HIERARCHY_ID_FOR_ADR,
118 LOCKBOX_MATCHING_OPTION,
119 PROGRAM_UPDATE_DATE,
120 CREATED_BY_MODULE,
121 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
122 CREATION_DATE,
123 LAST_UPDATE_DATE,
124 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
125 REVIEW_CYCLE,
126 LAST_CREDIT_REVIEW_DATE,
127 CREDIT_CLASSIFICATION,
128 CONS_BILL_LEVEL,
129 LATE_CHARGE_CALCULATION_TRX,
130 CREDIT_ITEMS_FLAG,
131 DISPUTED_TRANSACTIONS_FLAG,
132 LATE_CHARGE_TYPE,
133 LATE_CHARGE_TERM_ID,
134 INTEREST_CALCULATION_PERIOD,
135 HOLD_CHARGED_INVOICES_FLAG,
136 MESSAGE_TEXT_ID,
137 MULTIPLE_INTEREST_RATES_FLAG,
138 CHARGE_BEGIN_DATE,
139 CAST(MULTISET (
140 SELECT HZ_CUST_PROFILE_AMT_OBJ(
141 P_ACTION_TYPE,
142 NULL, -- COMMON_OBJ_ID
143 CUST_ACCT_PROFILE_AMT_ID,
144 CUST_ACCOUNT_PROFILE_ID,
145 CURRENCY_CODE,
146 TRX_CREDIT_LIMIT,
147 OVERALL_CREDIT_LIMIT,
148 MIN_DUNNING_AMOUNT,
149 MIN_DUNNING_INVOICE_AMOUNT,
150 MAX_INTEREST_CHARGE,
151 MIN_STATEMENT_AMOUNT,
152 AUTO_REC_MIN_RECEIPT_AMOUNT,
153 INTEREST_RATE,
154 ATTRIBUTE_CATEGORY,
155 ATTRIBUTE1,
156 ATTRIBUTE2,
157 ATTRIBUTE3,
158 ATTRIBUTE4,
159 ATTRIBUTE5,
160 ATTRIBUTE6,
161 ATTRIBUTE7,
162 ATTRIBUTE8,
163 ATTRIBUTE9,
164 ATTRIBUTE10,
165 ATTRIBUTE11,
166 ATTRIBUTE12,
167 ATTRIBUTE13,
168 ATTRIBUTE14,
169 ATTRIBUTE15,
170 MIN_FC_BALANCE_AMOUNT,
171 MIN_FC_INVOICE_AMOUNT,
172 CUST_ACCOUNT_ID,
173 SITE_USE_ID,
174 EXPIRATION_DATE,
175 JGZZ_ATTRIBUTE_CATEGORY,
176 JGZZ_ATTRIBUTE1,
177 JGZZ_ATTRIBUTE2,
178 JGZZ_ATTRIBUTE3,
179 JGZZ_ATTRIBUTE4,
180 JGZZ_ATTRIBUTE5,
181 JGZZ_ATTRIBUTE6,
182 JGZZ_ATTRIBUTE7,
183 JGZZ_ATTRIBUTE8,
184 JGZZ_ATTRIBUTE9,
185 JGZZ_ATTRIBUTE10,
186 JGZZ_ATTRIBUTE11,
187 JGZZ_ATTRIBUTE12,
188 JGZZ_ATTRIBUTE13,
189 JGZZ_ATTRIBUTE14,
190 JGZZ_ATTRIBUTE15,
191 GLOBAL_ATTRIBUTE1,
192 GLOBAL_ATTRIBUTE2,
193 GLOBAL_ATTRIBUTE3,
194 GLOBAL_ATTRIBUTE4,
195 GLOBAL_ATTRIBUTE5,
196 GLOBAL_ATTRIBUTE6,
197 GLOBAL_ATTRIBUTE7,
198 GLOBAL_ATTRIBUTE8,
199 GLOBAL_ATTRIBUTE9,
200 GLOBAL_ATTRIBUTE10,
201 GLOBAL_ATTRIBUTE11,
202 GLOBAL_ATTRIBUTE12,
203 GLOBAL_ATTRIBUTE13,
204 GLOBAL_ATTRIBUTE14,
205 GLOBAL_ATTRIBUTE15,
206 GLOBAL_ATTRIBUTE16,
207 GLOBAL_ATTRIBUTE17,
208 GLOBAL_ATTRIBUTE18,
209 GLOBAL_ATTRIBUTE19,
210 GLOBAL_ATTRIBUTE20,
211 GLOBAL_ATTRIBUTE_CATEGORY,
212 PROGRAM_UPDATE_DATE,
213 CREATED_BY_MODULE,
214 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
215 CREATION_DATE,
216 LAST_UPDATE_DATE,
217 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
218 EXCHANGE_RATE_TYPE,
219 MIN_FC_INVOICE_OVERDUE_TYPE,
220 MIN_FC_INVOICE_PERCENT,
221 MIN_FC_BALANCE_OVERDUE_TYPE,
222 MIN_FC_BALANCE_PERCENT,
223 INTEREST_TYPE,
224 INTEREST_FIXED_AMOUNT,
225 INTEREST_SCHEDULE_ID,
226 PENALTY_TYPE,
227 PENALTY_RATE,
228 MIN_INTEREST_CHARGE,
229 PENALTY_FIXED_AMOUNT,
230 PENALTY_SCHEDULE_ID )
231 FROM HZ_CUST_PROFILE_AMTS
232 WHERE SITE_USE_ID = P_SITE_USE_ID) AS HZ_CUST_PROFILE_AMT_OBJ_TBL))
233 FROM HZ_CUSTOMER_PROFILES
234 WHERE SITE_USE_ID = P_SITE_USE_ID;
235
236
237 BEGIN
238
239
240 -- initialize API return status to success.
241 x_return_status := FND_API.G_RET_STS_SUCCESS;
242
243 -- Initialize message list if p_init_msg_list is set to TRUE
244 IF FND_API.to_Boolean(p_init_msg_list) THEN
245 FND_MSG_PUB.initialize;
246 END IF;
247
248
249 -- Debug info.
250 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
251 hz_utility_v2pub.debug(p_message=>'get_site_use_profile_bo(+)',
252 p_prefix=>l_debug_prefix,
253 p_msg_level=>fnd_log.level_procedure);
254 END IF;
255
256 open c1;
257 fetch c1 into x_cust_profile_obj;
258 close c1;
259
260 -- Debug info.
261 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
262 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
263 p_msg_data=>x_msg_data,
264 p_msg_type=>'WARNING',
265 p_msg_level=>fnd_log.level_exception);
266 END IF;
267
268 -- Debug info.
269 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
270 hz_utility_v2pub.debug(p_message=>'get_site_use_profile_bo (-)',
271 p_prefix=>l_debug_prefix,
272 p_msg_level=>fnd_log.level_procedure);
273 END IF;
274
275
276 EXCEPTION
277
278 WHEN fnd_api.g_exc_error THEN
279 x_return_status := fnd_api.g_ret_sts_error;
280
281 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
282 p_count => x_msg_count,
283 p_data => x_msg_data);
284
285 -- Debug info.
286 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
287 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
288 p_msg_data=>x_msg_data,
289 p_msg_type=>'ERROR',
290 p_msg_level=>fnd_log.level_error);
291 END IF;
292 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
293 hz_utility_v2pub.debug(p_message=>'get_site_use_profile_bo (-)',
294 p_prefix=>l_debug_prefix,
295 p_msg_level=>fnd_log.level_procedure);
296 END IF;
297 WHEN fnd_api.g_exc_unexpected_error THEN
298 x_return_status := fnd_api.g_ret_sts_unexp_error;
299
300 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
301 p_count => x_msg_count,
302 p_data => x_msg_data);
303
304 -- Debug info.
305 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
306 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
307 p_msg_data=>x_msg_data,
308 p_msg_type=>'UNEXPECTED ERROR',
309 p_msg_level=>fnd_log.level_error);
310 END IF;
311 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
312 hz_utility_v2pub.debug(p_message=>'get_site_use_profile_bo (-)',
313 p_prefix=>l_debug_prefix,
314 p_msg_level=>fnd_log.level_procedure);
315 END IF;
316 WHEN OTHERS THEN
317 x_return_status := fnd_api.g_ret_sts_unexp_error;
318
319 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
320 fnd_message.set_token('ERROR' ,SQLERRM);
321 fnd_msg_pub.add;
322
323 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
324 p_count => x_msg_count,
325 p_data => x_msg_data);
326
327 -- Debug info.
328 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
329 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
330 p_msg_data=>x_msg_data,
331 p_msg_type=>'SQL ERROR',
332 p_msg_level=>fnd_log.level_error);
333 END IF;
334 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
335 hz_utility_v2pub.debug(p_message=>'get_site_use_profile_bo (-)',
336 p_prefix=>l_debug_prefix,
337 p_msg_level=>fnd_log.level_procedure);
338 END IF;
339
340 end;
341
342
343
344
345 -- Private procedure
346 PROCEDURE get_cust_site_use_bos(
347 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
348 p_cust_acct_site_id IN NUMBER,
349 p_action_type IN VARCHAR2 := NULL,
350 x_cust_site_use_objs OUT NOCOPY HZ_CUST_SITE_USE_BO_TBL,
351 x_return_status OUT NOCOPY VARCHAR2,
352 x_msg_count OUT NOCOPY NUMBER,
353 x_msg_data OUT NOCOPY VARCHAR2
354 ) is
355 l_debug_prefix VARCHAR2(30) := '';
356
357 CURSOR C1 IS
358 SELECT HZ_CUST_SITE_USE_BO(
359 P_ACTION_TYPE,
360 NULL, -- COMMON_OBJ_ID
361 CSU.SITE_USE_ID,
362 NULL, --CSU.ORIG_SYSTEM,
363 NULL, --CSU.ORIG_SYSTEM_REFERENCE,
364 CSU.CUST_ACCT_SITE_ID,
365 CSU.SITE_USE_CODE,
366 CSU.PRIMARY_FLAG,
367 CSU.STATUS,
368 CSU.LOCATION,
369 CSU.BILL_TO_SITE_USE_ID,
370 CSU.SIC_CODE,
371 CSU.PAYMENT_TERM_ID,
372 CSU.GSA_INDICATOR,
373 CSU.SHIP_PARTIAL,
374 CSU.SHIP_VIA,
375 CSU.FOB_POINT,
376 CSU.ORDER_TYPE_ID,
377 CSU.PRICE_LIST_ID,
378 CSU.FREIGHT_TERM,
379 CSU.WAREHOUSE_ID,
380 CSU.TERRITORY_ID,
381 CSU.TAX_REFERENCE,
382 CSU.SORT_PRIORITY,
383 CSU.TAX_CODE,
384 CSU.ATTRIBUTE_CATEGORY,
385 CSU.ATTRIBUTE1,
386 CSU.ATTRIBUTE2,
387 CSU.ATTRIBUTE3,
388 CSU.ATTRIBUTE4,
389 CSU.ATTRIBUTE5,
390 CSU.ATTRIBUTE6,
391 CSU.ATTRIBUTE7,
392 CSU.ATTRIBUTE8,
393 CSU.ATTRIBUTE9,
394 CSU.ATTRIBUTE10,
395 CSU.ATTRIBUTE11,
396 CSU.ATTRIBUTE12,
397 CSU.ATTRIBUTE13,
398 CSU.ATTRIBUTE14,
399 CSU.ATTRIBUTE15,
400 CSU.ATTRIBUTE16,
401 CSU.ATTRIBUTE17,
402 CSU.ATTRIBUTE18,
403 CSU.ATTRIBUTE19,
404 CSU.ATTRIBUTE20,
405 CSU.ATTRIBUTE21,
406 CSU.ATTRIBUTE22,
407 CSU.ATTRIBUTE23,
408 CSU.ATTRIBUTE24,
409 CSU.ATTRIBUTE25,
410 CSU.DEMAND_CLASS_CODE,
411 CSU.TAX_HEADER_LEVEL_FLAG,
412 CSU.TAX_ROUNDING_RULE,
413 CSU.GLOBAL_ATTRIBUTE1,
414 CSU.GLOBAL_ATTRIBUTE2,
415 CSU.GLOBAL_ATTRIBUTE3,
416 CSU.GLOBAL_ATTRIBUTE4,
417 CSU.GLOBAL_ATTRIBUTE5,
418 CSU.GLOBAL_ATTRIBUTE6,
419 CSU.GLOBAL_ATTRIBUTE7,
420 CSU.GLOBAL_ATTRIBUTE8,
421 CSU.GLOBAL_ATTRIBUTE9,
422 CSU.GLOBAL_ATTRIBUTE10,
423 CSU.GLOBAL_ATTRIBUTE11,
424 CSU.GLOBAL_ATTRIBUTE12,
425 CSU.GLOBAL_ATTRIBUTE13,
426 CSU.GLOBAL_ATTRIBUTE14,
427 CSU.GLOBAL_ATTRIBUTE15,
428 CSU.GLOBAL_ATTRIBUTE16,
429 CSU.GLOBAL_ATTRIBUTE17,
430 CSU.GLOBAL_ATTRIBUTE18,
431 CSU.GLOBAL_ATTRIBUTE19,
432 CSU.GLOBAL_ATTRIBUTE20,
433 CSU.GLOBAL_ATTRIBUTE_CATEGORY,
434 CSU.PRIMARY_SALESREP_ID,
435 CSU.FINCHRG_RECEIVABLES_TRX_ID,
436 CSU.DATES_NEGATIVE_TOLERANCE,
437 CSU.DATES_POSITIVE_TOLERANCE,
438 CSU.DATE_TYPE_PREFERENCE,
439 CSU.OVER_SHIPMENT_TOLERANCE,
440 CSU.UNDER_SHIPMENT_TOLERANCE,
441 CSU.ITEM_CROSS_REF_PREF,
442 CSU.OVER_RETURN_TOLERANCE,
443 CSU.UNDER_RETURN_TOLERANCE,
444 CSU.SHIP_SETS_INCLUDE_LINES_FLAG,
445 CSU.ARRIVALSETS_INCLUDE_LINES_FLAG,
446 CSU.SCHED_DATE_PUSH_FLAG,
447 CSU.INVOICE_QUANTITY_RULE,
448 CSU.PRICING_EVENT,
449 CSU.GL_ID_REC,
450 CSU.GL_ID_REV,
451 CSU.GL_ID_TAX,
452 CSU.GL_ID_FREIGHT,
453 CSU.GL_ID_CLEARING,
454 CSU.GL_ID_UNBILLED,
455 CSU.GL_ID_UNEARNED,
456 CSU.GL_ID_UNPAID_REC,
457 CSU.GL_ID_REMITTANCE,
458 CSU.GL_ID_FACTOR,
459 CSU.TAX_CLASSIFICATION,
460 CSU.ORG_ID,
461 CSU.PROGRAM_UPDATE_DATE,
462 CSU.CREATED_BY_MODULE,
463 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CSU.CREATED_BY),
464 CSU.CREATION_DATE,
465 CSU.LAST_UPDATE_DATE,
466 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CSU.LAST_UPDATED_BY),
467 CAST(MULTISET (
468 SELECT HZ_ORIG_SYS_REF_OBJ(
469 NULL, --P_ACTION_TYPE,
470 ORIG_SYSTEM_REF_ID,
471 ORIG_SYSTEM,
472 ORIG_SYSTEM_REFERENCE,
473 OWNER_TABLE_NAME,
474 OWNER_TABLE_ID,
475 STATUS,
476 REASON_CODE,
477 OLD_ORIG_SYSTEM_REFERENCE,
478 START_DATE_ACTIVE,
479 END_DATE_ACTIVE,
480 PROGRAM_UPDATE_DATE,
481 CREATED_BY_MODULE,
482 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
483 CREATION_DATE,
484 LAST_UPDATE_DATE,
485 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
486 ATTRIBUTE_CATEGORY,
487 ATTRIBUTE1,
488 ATTRIBUTE2,
489 ATTRIBUTE3,
490 ATTRIBUTE4,
491 ATTRIBUTE5,
492 ATTRIBUTE6,
493 ATTRIBUTE7,
494 ATTRIBUTE8,
495 ATTRIBUTE9,
496 ATTRIBUTE10,
497 ATTRIBUTE11,
498 ATTRIBUTE12,
499 ATTRIBUTE13,
500 ATTRIBUTE14,
501 ATTRIBUTE15,
502 ATTRIBUTE16,
503 ATTRIBUTE17,
504 ATTRIBUTE18,
505 ATTRIBUTE19,
506 ATTRIBUTE20)
507 FROM HZ_ORIG_SYS_REFERENCES OSR
508 WHERE OSR.OWNER_TABLE_ID = CSU.SITE_USE_ID
509 AND OWNER_TABLE_NAME = 'HZ_CUST_SITE_USES_ALL'
510 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL),
511 NULL, --HZ_CUSTOMER_PROFILE_BO
512 CAST(MULTISET (
513 SELECT HZ_BANK_ACCT_USE_OBJ(
514 P_ACTION_TYPE,
515 NULL, -- COMMON_OBJ_ID
516 INSTR_ASSIGNMENT_ID, --BANK_ACCT_USE_ID,
517 PAYMENT_FUNCTION,
518 PARTY_ID,
519 ORG_TYPE,
520 ORG_ID,
521 CUST_ACCOUNT_ID,
522 ACCT_SITE_USE_ID,
523 INSTRUMENT_ID,
524 INSTRUMENT_TYPE,
525 ORDER_OF_PREFERENCE,
526 ASSIGNMENT_START_DATE,
527 ASSIGNMENT_END_DATE,
528 NULL, --HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
529 NULL, --CREATION_DATE,
530 ASSIGNMENT_LAST_UPDATE,
531 NULL) --HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
532 FROM IBY_FNDCPT_PAYER_ASSGN_INSTR_V
533 WHERE ACCT_SITE_USE_ID = CSU.SITE_USE_ID) AS HZ_BANK_ACCT_USE_OBJ_TBL),
534 ( SELECT HZ_PAYMENT_METHOD_OBJ(
535 P_ACTION_TYPE,
536 NULL, -- COMMON_OBJ_ID
537 CRM.CUST_RECEIPT_METHOD_ID, --PAYMENT_METHOD_ID,
538 CRM.CUSTOMER_ID,
539 CRM.RECEIPT_METHOD_ID,
540 CRM.PRIMARY_FLAG,
541 CRM.SITE_USE_ID,
542 CRM.START_DATE,
543 CRM.END_DATE,
544 CRM.ATTRIBUTE_CATEGORY,
545 CRM.ATTRIBUTE1,
546 CRM.ATTRIBUTE2,
547 CRM.ATTRIBUTE3,
548 CRM.ATTRIBUTE4,
549 CRM.ATTRIBUTE5,
550 CRM.ATTRIBUTE6,
551 CRM.ATTRIBUTE7,
552 CRM.ATTRIBUTE8,
553 CRM.ATTRIBUTE9,
554 CRM.ATTRIBUTE10,
555 CRM.ATTRIBUTE11,
556 CRM.ATTRIBUTE12,
557 CRM.ATTRIBUTE13,
558 CRM.ATTRIBUTE14,
559 CRM.ATTRIBUTE15,
560 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CRM.CREATED_BY),
561 CRM.CREATION_DATE,
562 CRM.LAST_UPDATE_DATE,
563 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CRM.LAST_UPDATED_BY))
564 FROM RA_CUST_RECEIPT_METHODS CRM
565 WHERE CSU.SITE_USE_ID = CRM.SITE_USE_ID
566 AND ROWNUM = 1)
567 )
568 FROM HZ_CUST_SITE_USES CSU
569 WHERE CUST_ACCT_SITE_ID = P_CUST_ACCT_SITE_ID;
570
571 BEGIN
572
573
574 -- initialize API return status to success.
575 x_return_status := FND_API.G_RET_STS_SUCCESS;
576
577 -- Initialize message list if p_init_msg_list is set to TRUE
578 IF FND_API.to_Boolean(p_init_msg_list) THEN
579 FND_MSG_PUB.initialize;
580 END IF;
581
582
583 -- Debug info.
584 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
585 hz_utility_v2pub.debug(p_message=>'get_cust_site_use_bos(+)',
586 p_prefix=>l_debug_prefix,
587 p_msg_level=>fnd_log.level_procedure);
588 END IF;
589
590 x_cust_site_use_objs := HZ_CUST_SITE_USE_BO_TBL();
591 open c1;
592 fetch c1 BULK COLLECT into x_cust_site_use_objs;
593 close c1;
594
595 for i in 1..x_cust_site_use_objs.count loop
596 get_site_use_profile_bo(
597 p_init_msg_list => fnd_api.g_false,
598 p_site_use_id => x_cust_site_use_objs(i).site_use_id,
599 p_action_type => p_action_type,
600 x_cust_profile_obj => x_cust_site_use_objs(i).site_use_profile_obj,
601 x_return_status => x_return_status,
602 x_msg_count => x_msg_count,
603 x_msg_data => x_msg_data);
604
605 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
606 RAISE FND_API.G_EXC_ERROR;
607 END IF;
608 end loop;
609
610 -- Debug info.
611 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
612 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
613 p_msg_data=>x_msg_data,
614 p_msg_type=>'WARNING',
615 p_msg_level=>fnd_log.level_exception);
616 END IF;
617
618 -- Debug info.
619 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
620 hz_utility_v2pub.debug(p_message=>'get_cust_site_use_bos (-)',
621 p_prefix=>l_debug_prefix,
622 p_msg_level=>fnd_log.level_procedure);
623 END IF;
624
625
626 EXCEPTION
627
628 WHEN fnd_api.g_exc_error THEN
629 x_return_status := fnd_api.g_ret_sts_error;
630
631 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
632 p_count => x_msg_count,
633 p_data => x_msg_data);
634
635 -- Debug info.
636 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
637 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
638 p_msg_data=>x_msg_data,
639 p_msg_type=>'ERROR',
640 p_msg_level=>fnd_log.level_error);
641 END IF;
642 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
643 hz_utility_v2pub.debug(p_message=>'get_cust_site_use_bos (-)',
644 p_prefix=>l_debug_prefix,
645 p_msg_level=>fnd_log.level_procedure);
646 END IF;
647 WHEN fnd_api.g_exc_unexpected_error THEN
648 x_return_status := fnd_api.g_ret_sts_unexp_error;
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=>'UNEXPECTED 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_cust_site_use_bos (-)',
663 p_prefix=>l_debug_prefix,
664 p_msg_level=>fnd_log.level_procedure);
665 END IF;
666 WHEN OTHERS THEN
667 x_return_status := fnd_api.g_ret_sts_unexp_error;
668
669 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
670 fnd_message.set_token('ERROR' ,SQLERRM);
671 fnd_msg_pub.add;
672
673 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
674 p_count => x_msg_count,
675 p_data => x_msg_data);
676
677 -- Debug info.
678 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
679 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
680 p_msg_data=>x_msg_data,
681 p_msg_type=>'SQL ERROR',
682 p_msg_level=>fnd_log.level_error);
683 END IF;
684 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
685 hz_utility_v2pub.debug(p_message=>'get_cust_site_use_bos (-)',
686 p_prefix=>l_debug_prefix,
687 p_msg_level=>fnd_log.level_procedure);
688 END IF;
689
690 end;
691
692
693
694
695 --------------------------------------
696 --
697 -- PROCEDURE get_cust_acct_site_bos
698 --
699 -- DESCRIPTION
700 -- Get logical customer account sites.
701 --
702 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
703 --
704 -- ARGUMENTS
705 -- IN:
706 -- p_init_msg_list Initialize message stack if it is set to
707 -- p_parent_id parent id.
708 -- p_cust_acct_site_id customer account site ID.
709 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
710 -- OUT:
711 -- x_cust_acct_site_objs Logical customer account site records.
712 -- x_return_status Return status after the call. The status can
713 -- be fnd_api.g_ret_sts_success (success),
714 -- fnd_api.g_ret_sts_error (error),
715 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
716 -- x_msg_count Number of messages in message stack.
717 -- x_msg_data Message text if x_msg_count is 1.
718 --
719 -- NOTES
720 --
721 -- MODIFICATION HISTORY
722 --
723 --
724 -- 8-JUN-2005 AWU Created.
725 --
726
727
728 /*
729
730 The Get customer account site API Procedure is a retrieval service that returns a full customer account site business object.
731 The user identifies a particular Organization Contact business object using the TCA identifier and/or the object's
732 Source System information. Upon proper validation of the object, the full Organization Contact business object is returned.
733 The object consists of all data included within the Organization Contact business object, at all embedded levels.
734 This includes the set of all data stored in the TCA tables for each embedded entity.
735
736
737 Embedded BO Mandatory Multiple Logical API Procedure Comments
738
739 Party Site Y N get_party_site_bo
740 Customer Account Site Contact N Y get_cust_acct_contact_bo
741 Customer Account Site Use N Y Business Structure. Included entities and
742 structures:HZ_CUST_SITE_USES_ALL,Customer
743 Profile (Business Structure)
744
745
746 To retrieve the appropriate embedded entities within the 'Organization Contact' business object, the Get procedure returns
747 all records for the particular contact from these TCA entity tables.
748
749 Embedded TCA Entity Mandatory Multiple TCA Table Entities
750
751 Customer account site Y N HZ_CUST_ACCOUNTS
752 Bank account site Use N Y Owned by Payments team
753 Payment Method N N Owned by AR team
754
755 */
756
757
758
759 PROCEDURE get_cust_acct_site_bos(
760 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
761 p_parent_id IN NUMBER,
762 p_cust_acct_site_id IN NUMBER,
763 p_action_type IN VARCHAR2 := NULL,
764 x_cust_acct_site_objs OUT NOCOPY HZ_CUST_ACCT_SITE_BO_TBL,
765 x_return_status OUT NOCOPY VARCHAR2,
766 x_msg_count OUT NOCOPY NUMBER,
767 x_msg_data OUT NOCOPY VARCHAR2
768 ) is
769 l_debug_prefix VARCHAR2(30) := '';
770 --l_party_site_objs HZ_PARTY_SITE_BO_TBL;
771
772 CURSOR C1 IS
773 SELECT HZ_CUST_ACCT_SITE_BO(
774 P_ACTION_TYPE,
775 NULL, -- COMMON_OBJ_ID
776 CUST_ACCT_SITE_ID,
777 NULL, --ORIG_SYSTEM,
778 NULL, --ORIG_SYSTEM_REFERENCE,
779 CUST_ACCOUNT_ID,
780 ATTRIBUTE_CATEGORY,
781 ATTRIBUTE1,
782 ATTRIBUTE2,
783 ATTRIBUTE3,
784 ATTRIBUTE4,
785 ATTRIBUTE5,
786 ATTRIBUTE6,
787 ATTRIBUTE7,
788 ATTRIBUTE8,
789 ATTRIBUTE9,
790 ATTRIBUTE10,
791 ATTRIBUTE11,
792 ATTRIBUTE12,
793 ATTRIBUTE13,
794 ATTRIBUTE14,
795 ATTRIBUTE15,
796 ATTRIBUTE16,
797 ATTRIBUTE17,
798 ATTRIBUTE18,
799 ATTRIBUTE19,
800 ATTRIBUTE20,
801 GLOBAL_ATTRIBUTE_CATEGORY,
802 GLOBAL_ATTRIBUTE1,
803 GLOBAL_ATTRIBUTE2,
804 GLOBAL_ATTRIBUTE3,
805 GLOBAL_ATTRIBUTE4,
806 GLOBAL_ATTRIBUTE5,
807 GLOBAL_ATTRIBUTE6,
808 GLOBAL_ATTRIBUTE7,
809 GLOBAL_ATTRIBUTE8,
810 GLOBAL_ATTRIBUTE9,
811 GLOBAL_ATTRIBUTE10,
812 GLOBAL_ATTRIBUTE11,
813 GLOBAL_ATTRIBUTE12,
814 GLOBAL_ATTRIBUTE13,
815 GLOBAL_ATTRIBUTE14,
816 GLOBAL_ATTRIBUTE15,
817 GLOBAL_ATTRIBUTE16,
818 GLOBAL_ATTRIBUTE17,
819 GLOBAL_ATTRIBUTE18,
820 GLOBAL_ATTRIBUTE19,
821 GLOBAL_ATTRIBUTE20,
822 STATUS,
823 CUSTOMER_CATEGORY_CODE,
824 LANGUAGE,
825 KEY_ACCOUNT_FLAG,
826 TP_HEADER_ID,
827 ECE_TP_LOCATION_CODE,
828 PRIMARY_SPECIALIST_ID,
829 SECONDARY_SPECIALIST_ID,
830 TERRITORY_ID,
831 TERRITORY,
832 TRANSLATED_CUSTOMER_NAME,
833 ORG_ID,
834 PROGRAM_UPDATE_DATE,
835 CREATED_BY_MODULE,
836 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
837 CREATION_DATE,
838 LAST_UPDATE_DATE,
839 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
840 PARTY_SITE_ID,
841 NULL, --PARTY_SITE_OS,
842 NULL, --PARTY_SITE_OSR,
843 CAST(MULTISET (
844 SELECT HZ_ORIG_SYS_REF_OBJ(
845 NULL, --P_ACTION_TYPE,
846 ORIG_SYSTEM_REF_ID,
847 ORIG_SYSTEM,
848 ORIG_SYSTEM_REFERENCE,
849 OWNER_TABLE_NAME,
850 OWNER_TABLE_ID,
851 STATUS,
852 REASON_CODE,
853 OLD_ORIG_SYSTEM_REFERENCE,
854 START_DATE_ACTIVE,
855 END_DATE_ACTIVE,
856 PROGRAM_UPDATE_DATE,
857 CREATED_BY_MODULE,
858 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
859 CREATION_DATE,
860 LAST_UPDATE_DATE,
861 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
862 ATTRIBUTE_CATEGORY,
863 ATTRIBUTE1,
864 ATTRIBUTE2,
865 ATTRIBUTE3,
866 ATTRIBUTE4,
867 ATTRIBUTE5,
868 ATTRIBUTE6,
869 ATTRIBUTE7,
870 ATTRIBUTE8,
871 ATTRIBUTE9,
872 ATTRIBUTE10,
873 ATTRIBUTE11,
874 ATTRIBUTE12,
875 ATTRIBUTE13,
876 ATTRIBUTE14,
877 ATTRIBUTE15,
878 ATTRIBUTE16,
879 ATTRIBUTE17,
880 ATTRIBUTE18,
881 ATTRIBUTE19,
882 ATTRIBUTE20)
883 FROM HZ_ORIG_SYS_REFERENCES OSR
884 WHERE
885 OSR.OWNER_TABLE_ID = CAS.CUST_ACCT_SITE_ID
886 AND OWNER_TABLE_NAME = 'HZ_CUST_ACCT_SITES_ALL'
887 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL), -- acct site ssm
888 CAST(MULTISET (
889 SELECT HZ_ORIG_SYS_REF_OBJ(
890 NULL, --P_ACTION_TYPE,
891 ORIG_SYSTEM_REF_ID,
892 ORIG_SYSTEM,
893 ORIG_SYSTEM_REFERENCE,
894 OWNER_TABLE_NAME,
895 OWNER_TABLE_ID,
896 STATUS,
897 REASON_CODE,
898 OLD_ORIG_SYSTEM_REFERENCE,
899 START_DATE_ACTIVE,
900 END_DATE_ACTIVE,
901 PROGRAM_UPDATE_DATE,
902 CREATED_BY_MODULE,
903 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
904 CREATION_DATE,
905 LAST_UPDATE_DATE,
906 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
907 ATTRIBUTE_CATEGORY,
908 ATTRIBUTE1,
909 ATTRIBUTE2,
910 ATTRIBUTE3,
911 ATTRIBUTE4,
912 ATTRIBUTE5,
913 ATTRIBUTE6,
914 ATTRIBUTE7,
915 ATTRIBUTE8,
916 ATTRIBUTE9,
917 ATTRIBUTE10,
918 ATTRIBUTE11,
919 ATTRIBUTE12,
920 ATTRIBUTE13,
921 ATTRIBUTE14,
922 ATTRIBUTE15,
923 ATTRIBUTE16,
924 ATTRIBUTE17,
925 ATTRIBUTE18,
926 ATTRIBUTE19,
927 ATTRIBUTE20)
928 FROM HZ_ORIG_SYS_REFERENCES OSR
929 WHERE
930 OSR.OWNER_TABLE_ID = CAS.PARTY_SITE_ID
931 AND OWNER_TABLE_NAME = 'HZ_PARTY_SITES'
932 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL), -- party site ssm
933 HZ_CUST_SITE_USE_BO_TBL(),
934 HZ_CUST_ACCT_CONTACT_BO_TBL())
935 FROM HZ_CUST_ACCT_SITES CAS
936 WHERE ((P_CUST_ACCT_SITE_ID IS NULL AND CUST_ACCOUNT_ID = P_PARENT_ID)
937 OR (P_CUST_ACCT_SITE_ID IS NOT NULL
938 AND CUST_ACCT_SITE_ID = P_CUST_ACCT_SITE_ID));
939
940
941
942 BEGIN
943
944
945 -- initialize API return status to success.
946 x_return_status := FND_API.G_RET_STS_SUCCESS;
947
948 -- Initialize message list if p_init_msg_list is set to TRUE
949 IF FND_API.to_Boolean(p_init_msg_list) THEN
950 FND_MSG_PUB.initialize;
951 END IF;
952
953
954 -- Debug info.
955 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
956 hz_utility_v2pub.debug(p_message=>'get_cust_acct_site_bos(+)',
957 p_prefix=>l_debug_prefix,
958 p_msg_level=>fnd_log.level_procedure);
959 END IF;
960
961 x_cust_acct_site_objs := HZ_CUST_ACCT_SITE_BO_TBL();
962 open c1;
963 fetch c1 BULK COLLECT into x_cust_acct_site_objs;
964 close c1;
965
966 for i in 1..x_cust_acct_site_objs.count loop
967 get_cust_site_use_bos(
968 p_init_msg_list => fnd_api.g_false,
969 p_cust_acct_site_id => x_cust_acct_site_objs(i).cust_acct_site_id,
970 p_action_type => p_action_type,
971 x_cust_site_use_objs =>x_cust_acct_site_objs(i).CUST_ACCT_SITE_USE_OBJS,
972 x_return_status => x_return_status,
973 x_msg_count => x_msg_count,
974 x_msg_data => x_msg_data);
975
976 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
977 RAISE FND_API.G_EXC_ERROR;
978 END IF;
979
980 HZ_EXTRACT_ACCT_CONT_BO_PVT.get_cust_acct_contact_bos(
981 p_init_msg_list => fnd_api.g_false,
982 p_parent_id => x_cust_acct_site_objs(i).cust_acct_site_id,
983 p_cust_acct_contact_id => null,
984 p_action_type => p_action_type,
985 x_cust_acct_contact_objs =>x_cust_acct_site_objs(i).CUST_ACCT_CONTACT_OBJS,
986 x_return_status => x_return_status,
987 x_msg_count => x_msg_count,
988 x_msg_data => x_msg_data);
989
990 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
991 RAISE FND_API.G_EXC_ERROR;
992 END IF;
993
994 /*
995 HZ_EXTRACT_PARTY_SITE_BO_PVT.get_party_site_bos
996 (p_init_msg_list => fnd_api.g_false,
997 p_party_id => NULL,
998 p_party_site_id => x_cust_acct_site_objs(i).party_site_id,
999 p_action_type => p_action_type,
1000 x_party_site_objs =>l_party_site_objs,
1001 x_return_status => x_return_status,
1002 x_msg_count => x_msg_count,
1003 x_msg_data => x_msg_data);
1004
1005 l_party_site_objs(1) := x_cust_acct_site_objs(i).party_site_obj;
1006 */
1007 end loop;
1008
1009 -- Debug info.
1010 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1011 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1012 p_msg_data=>x_msg_data,
1013 p_msg_type=>'WARNING',
1014 p_msg_level=>fnd_log.level_exception);
1015 END IF;
1016
1017 -- Debug info.
1018 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1019 hz_utility_v2pub.debug(p_message=>'get_cust_acct_site_bos (-)',
1020 p_prefix=>l_debug_prefix,
1021 p_msg_level=>fnd_log.level_procedure);
1022 END IF;
1023
1024
1025 EXCEPTION
1026
1027 WHEN fnd_api.g_exc_error THEN
1028 x_return_status := fnd_api.g_ret_sts_error;
1029
1030 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1031 p_count => x_msg_count,
1032 p_data => x_msg_data);
1033
1034 -- Debug info.
1035 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1036 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1037 p_msg_data=>x_msg_data,
1038 p_msg_type=>'ERROR',
1039 p_msg_level=>fnd_log.level_error);
1040 END IF;
1041 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1042 hz_utility_v2pub.debug(p_message=>'get_cust_acct_site_bos (-)',
1043 p_prefix=>l_debug_prefix,
1044 p_msg_level=>fnd_log.level_procedure);
1045 END IF;
1046 WHEN fnd_api.g_exc_unexpected_error THEN
1047 x_return_status := fnd_api.g_ret_sts_unexp_error;
1048
1049 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1050 p_count => x_msg_count,
1051 p_data => x_msg_data);
1052
1053 -- Debug info.
1054 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1055 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1056 p_msg_data=>x_msg_data,
1057 p_msg_type=>'UNEXPECTED ERROR',
1058 p_msg_level=>fnd_log.level_error);
1059 END IF;
1060 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1061 hz_utility_v2pub.debug(p_message=>'get_cust_acct_site_bos (-)',
1062 p_prefix=>l_debug_prefix,
1063 p_msg_level=>fnd_log.level_procedure);
1064 END IF;
1065 WHEN OTHERS THEN
1066 x_return_status := fnd_api.g_ret_sts_unexp_error;
1067
1068 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1069 fnd_message.set_token('ERROR' ,SQLERRM);
1070 fnd_msg_pub.add;
1071
1072 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1073 p_count => x_msg_count,
1074 p_data => x_msg_data);
1075
1076 -- Debug info.
1077 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1078 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1079 p_msg_data=>x_msg_data,
1080 p_msg_type=>'SQL ERROR',
1081 p_msg_level=>fnd_log.level_error);
1082 END IF;
1083 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1084 hz_utility_v2pub.debug(p_message=>'get_cust_acct_site_bos (-)',
1085 p_prefix=>l_debug_prefix,
1086 p_msg_level=>fnd_log.level_procedure);
1087 END IF;
1088
1089 end;
1090
1091 -- Private procedure
1092 PROCEDURE get_cust_site_use_v2_bos(
1093 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1094 p_cust_acct_site_id IN NUMBER,
1095 p_action_type IN VARCHAR2 := NULL,
1096 x_cust_site_use_v2_objs OUT NOCOPY HZ_CUST_SITE_USE_V2_BO_TBL,
1097 x_return_status OUT NOCOPY VARCHAR2,
1098 x_msg_count OUT NOCOPY NUMBER,
1099 x_msg_data OUT NOCOPY VARCHAR2
1100 ) is
1101 l_debug_prefix VARCHAR2(30) := '';
1102
1103 CURSOR C1 IS
1104 SELECT HZ_CUST_SITE_USE_V2_BO(
1105 P_ACTION_TYPE,
1106 NULL, -- COMMON_OBJ_ID
1107 CSU.SITE_USE_ID,
1108 NULL, --CSU.ORIG_SYSTEM,
1109 NULL, --CSU.ORIG_SYSTEM_REFERENCE,
1110 CSU.CUST_ACCT_SITE_ID,
1111 CSU.SITE_USE_CODE,
1112 CSU.PRIMARY_FLAG,
1113 CSU.STATUS,
1114 CSU.LOCATION,
1115 CSU.BILL_TO_SITE_USE_ID,
1116 CSU.SIC_CODE,
1117 CSU.PAYMENT_TERM_ID,
1118 CSU.GSA_INDICATOR,
1119 CSU.SHIP_PARTIAL,
1120 CSU.SHIP_VIA,
1121 CSU.FOB_POINT,
1122 CSU.ORDER_TYPE_ID,
1123 CSU.PRICE_LIST_ID,
1124 CSU.FREIGHT_TERM,
1125 CSU.WAREHOUSE_ID,
1126 CSU.TERRITORY_ID,
1127 CSU.TAX_REFERENCE,
1128 CSU.SORT_PRIORITY,
1129 CSU.TAX_CODE,
1130 CSU.ATTRIBUTE_CATEGORY,
1131 CSU.ATTRIBUTE1,
1132 CSU.ATTRIBUTE2,
1133 CSU.ATTRIBUTE3,
1134 CSU.ATTRIBUTE4,
1135 CSU.ATTRIBUTE5,
1136 CSU.ATTRIBUTE6,
1137 CSU.ATTRIBUTE7,
1138 CSU.ATTRIBUTE8,
1139 CSU.ATTRIBUTE9,
1140 CSU.ATTRIBUTE10,
1141 CSU.ATTRIBUTE11,
1142 CSU.ATTRIBUTE12,
1143 CSU.ATTRIBUTE13,
1144 CSU.ATTRIBUTE14,
1145 CSU.ATTRIBUTE15,
1146 CSU.ATTRIBUTE16,
1147 CSU.ATTRIBUTE17,
1148 CSU.ATTRIBUTE18,
1149 CSU.ATTRIBUTE19,
1150 CSU.ATTRIBUTE20,
1151 CSU.ATTRIBUTE21,
1152 CSU.ATTRIBUTE22,
1153 CSU.ATTRIBUTE23,
1154 CSU.ATTRIBUTE24,
1155 CSU.ATTRIBUTE25,
1156 CSU.DEMAND_CLASS_CODE,
1157 CSU.TAX_HEADER_LEVEL_FLAG,
1158 CSU.TAX_ROUNDING_RULE,
1159 CSU.GLOBAL_ATTRIBUTE1,
1160 CSU.GLOBAL_ATTRIBUTE2,
1161 CSU.GLOBAL_ATTRIBUTE3,
1162 CSU.GLOBAL_ATTRIBUTE4,
1163 CSU.GLOBAL_ATTRIBUTE5,
1164 CSU.GLOBAL_ATTRIBUTE6,
1165 CSU.GLOBAL_ATTRIBUTE7,
1166 CSU.GLOBAL_ATTRIBUTE8,
1167 CSU.GLOBAL_ATTRIBUTE9,
1168 CSU.GLOBAL_ATTRIBUTE10,
1169 CSU.GLOBAL_ATTRIBUTE11,
1170 CSU.GLOBAL_ATTRIBUTE12,
1171 CSU.GLOBAL_ATTRIBUTE13,
1172 CSU.GLOBAL_ATTRIBUTE14,
1173 CSU.GLOBAL_ATTRIBUTE15,
1174 CSU.GLOBAL_ATTRIBUTE16,
1175 CSU.GLOBAL_ATTRIBUTE17,
1176 CSU.GLOBAL_ATTRIBUTE18,
1177 CSU.GLOBAL_ATTRIBUTE19,
1178 CSU.GLOBAL_ATTRIBUTE20,
1179 CSU.GLOBAL_ATTRIBUTE_CATEGORY,
1180 CSU.PRIMARY_SALESREP_ID,
1181 CSU.FINCHRG_RECEIVABLES_TRX_ID,
1182 CSU.DATES_NEGATIVE_TOLERANCE,
1183 CSU.DATES_POSITIVE_TOLERANCE,
1184 CSU.DATE_TYPE_PREFERENCE,
1185 CSU.OVER_SHIPMENT_TOLERANCE,
1186 CSU.UNDER_SHIPMENT_TOLERANCE,
1187 CSU.ITEM_CROSS_REF_PREF,
1188 CSU.OVER_RETURN_TOLERANCE,
1189 CSU.UNDER_RETURN_TOLERANCE,
1190 CSU.SHIP_SETS_INCLUDE_LINES_FLAG,
1191 CSU.ARRIVALSETS_INCLUDE_LINES_FLAG,
1192 CSU.SCHED_DATE_PUSH_FLAG,
1193 CSU.INVOICE_QUANTITY_RULE,
1194 CSU.PRICING_EVENT,
1195 CSU.GL_ID_REC,
1196 CSU.GL_ID_REV,
1197 CSU.GL_ID_TAX,
1198 CSU.GL_ID_FREIGHT,
1199 CSU.GL_ID_CLEARING,
1200 CSU.GL_ID_UNBILLED,
1201 CSU.GL_ID_UNEARNED,
1202 CSU.GL_ID_UNPAID_REC,
1203 CSU.GL_ID_REMITTANCE,
1204 CSU.GL_ID_FACTOR,
1205 CSU.TAX_CLASSIFICATION,
1206 CSU.ORG_ID,
1207 CSU.PROGRAM_UPDATE_DATE,
1208 CSU.CREATED_BY_MODULE,
1209 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CSU.CREATED_BY),
1210 CSU.CREATION_DATE,
1211 CSU.LAST_UPDATE_DATE,
1212 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CSU.LAST_UPDATED_BY),
1213 CAST(MULTISET (
1214 SELECT HZ_ORIG_SYS_REF_OBJ(
1215 NULL, --P_ACTION_TYPE,
1216 ORIG_SYSTEM_REF_ID,
1217 ORIG_SYSTEM,
1218 ORIG_SYSTEM_REFERENCE,
1219 OWNER_TABLE_NAME,
1220 OWNER_TABLE_ID,
1221 STATUS,
1222 REASON_CODE,
1223 OLD_ORIG_SYSTEM_REFERENCE,
1224 START_DATE_ACTIVE,
1225 END_DATE_ACTIVE,
1226 PROGRAM_UPDATE_DATE,
1227 CREATED_BY_MODULE,
1228 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
1229 CREATION_DATE,
1230 LAST_UPDATE_DATE,
1231 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
1232 ATTRIBUTE_CATEGORY,
1233 ATTRIBUTE1,
1234 ATTRIBUTE2,
1235 ATTRIBUTE3,
1236 ATTRIBUTE4,
1237 ATTRIBUTE5,
1238 ATTRIBUTE6,
1239 ATTRIBUTE7,
1240 ATTRIBUTE8,
1241 ATTRIBUTE9,
1242 ATTRIBUTE10,
1243 ATTRIBUTE11,
1244 ATTRIBUTE12,
1245 ATTRIBUTE13,
1246 ATTRIBUTE14,
1247 ATTRIBUTE15,
1248 ATTRIBUTE16,
1249 ATTRIBUTE17,
1250 ATTRIBUTE18,
1251 ATTRIBUTE19,
1252 ATTRIBUTE20)
1253 FROM HZ_ORIG_SYS_REFERENCES OSR
1254 WHERE OSR.OWNER_TABLE_ID = CSU.SITE_USE_ID
1255 AND OWNER_TABLE_NAME = 'HZ_CUST_SITE_USES_ALL'
1256 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL),
1257 NULL, --HZ_CUSTOMER_PROFILE_BO
1258 CAST(MULTISET (
1259 SELECT HZ_BANK_ACCT_USE_OBJ(
1260 P_ACTION_TYPE,
1261 NULL, -- COMMON_OBJ_ID
1262 INSTR_ASSIGNMENT_ID, --BANK_ACCT_USE_ID,
1263 PAYMENT_FUNCTION,
1264 PARTY_ID,
1265 ORG_TYPE,
1266 ORG_ID,
1267 CUST_ACCOUNT_ID,
1268 ACCT_SITE_USE_ID,
1269 INSTRUMENT_ID,
1270 INSTRUMENT_TYPE,
1271 ORDER_OF_PREFERENCE,
1272 ASSIGNMENT_START_DATE,
1273 ASSIGNMENT_END_DATE,
1274 NULL, --HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
1275 NULL, --CREATION_DATE,
1276 ASSIGNMENT_LAST_UPDATE,
1277 NULL) --HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
1278 FROM IBY_FNDCPT_PAYER_ASSGN_INSTR_V
1279 WHERE ACCT_SITE_USE_ID = CSU.SITE_USE_ID) AS HZ_BANK_ACCT_USE_OBJ_TBL),
1280 CAST( MULTISET (
1281 SELECT HZ_PAYMENT_METHOD_OBJ(
1282 P_ACTION_TYPE,
1283 NULL, -- COMMON_OBJ_ID
1284 CRM.CUST_RECEIPT_METHOD_ID, --PAYMENT_METHOD_ID,
1285 CRM.CUSTOMER_ID,
1286 CRM.RECEIPT_METHOD_ID,
1287 CRM.PRIMARY_FLAG,
1288 CRM.SITE_USE_ID,
1289 CRM.START_DATE,
1290 CRM.END_DATE,
1291 CRM.ATTRIBUTE_CATEGORY,
1292 CRM.ATTRIBUTE1,
1293 CRM.ATTRIBUTE2,
1294 CRM.ATTRIBUTE3,
1295 CRM.ATTRIBUTE4,
1296 CRM.ATTRIBUTE5,
1297 CRM.ATTRIBUTE6,
1298 CRM.ATTRIBUTE7,
1299 CRM.ATTRIBUTE8,
1300 CRM.ATTRIBUTE9,
1301 CRM.ATTRIBUTE10,
1302 CRM.ATTRIBUTE11,
1303 CRM.ATTRIBUTE12,
1304 CRM.ATTRIBUTE13,
1305 CRM.ATTRIBUTE14,
1306 CRM.ATTRIBUTE15,
1307 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CRM.CREATED_BY),
1308 CRM.CREATION_DATE,
1309 CRM.LAST_UPDATE_DATE,
1310 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CRM.LAST_UPDATED_BY))
1311 FROM RA_CUST_RECEIPT_METHODS CRM
1312 WHERE CSU.SITE_USE_ID = CRM.SITE_USE_ID ) AS HZ_PAYMENT_METHOD_OBJ_TBL)
1313 )
1314 FROM HZ_CUST_SITE_USES CSU
1315 WHERE CUST_ACCT_SITE_ID = P_CUST_ACCT_SITE_ID;
1316
1317 BEGIN
1318
1319
1320 -- initialize API return status to success.
1321 x_return_status := FND_API.G_RET_STS_SUCCESS;
1322
1323 -- Initialize message list if p_init_msg_list is set to TRUE
1324 IF FND_API.to_Boolean(p_init_msg_list) THEN
1325 FND_MSG_PUB.initialize;
1326 END IF;
1327
1328
1329 -- Debug info.
1330 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1331 hz_utility_v2pub.debug(p_message=>'get_cust_site_use_v2_bos(+)',
1332 p_prefix=>l_debug_prefix,
1333 p_msg_level=>fnd_log.level_procedure);
1334 END IF;
1335
1336 x_cust_site_use_v2_objs := HZ_CUST_SITE_USE_V2_BO_TBL();
1337 open c1;
1338 fetch c1 BULK COLLECT into x_cust_site_use_v2_objs;
1339 close c1;
1340
1341 for i in 1..x_cust_site_use_v2_objs.count loop
1342 get_site_use_profile_bo(
1343 p_init_msg_list => fnd_api.g_false,
1344 p_site_use_id => x_cust_site_use_v2_objs(i).site_use_id,
1345 p_action_type => p_action_type,
1346 x_cust_profile_obj => x_cust_site_use_v2_objs(i).site_use_profile_obj,
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 end loop;
1355
1356 -- Debug info.
1357 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1358 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1359 p_msg_data=>x_msg_data,
1360 p_msg_type=>'WARNING',
1361 p_msg_level=>fnd_log.level_exception);
1362 END IF;
1363
1364 -- Debug info.
1365 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1366 hz_utility_v2pub.debug(p_message=>'get_cust_site_use_v2_bos (-)',
1367 p_prefix=>l_debug_prefix,
1368 p_msg_level=>fnd_log.level_procedure);
1369 END IF;
1370
1371
1372 EXCEPTION
1373
1374 WHEN fnd_api.g_exc_error THEN
1375 x_return_status := fnd_api.g_ret_sts_error;
1376
1377 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1378 p_count => x_msg_count,
1379 p_data => x_msg_data);
1380
1381 -- Debug info.
1382 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1383 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1384 p_msg_data=>x_msg_data,
1385 p_msg_type=>'ERROR',
1386 p_msg_level=>fnd_log.level_error);
1387 END IF;
1388 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1389 hz_utility_v2pub.debug(p_message=>'get_cust_site_use_v2_bos (-)',
1390 p_prefix=>l_debug_prefix,
1391 p_msg_level=>fnd_log.level_procedure);
1392 END IF;
1393 WHEN fnd_api.g_exc_unexpected_error THEN
1394 x_return_status := fnd_api.g_ret_sts_unexp_error;
1395
1396 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1397 p_count => x_msg_count,
1398 p_data => x_msg_data);
1399
1400 -- Debug info.
1401 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1402 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1403 p_msg_data=>x_msg_data,
1404 p_msg_type=>'UNEXPECTED ERROR',
1405 p_msg_level=>fnd_log.level_error);
1406 END IF;
1407 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1408 hz_utility_v2pub.debug(p_message=>'get_cust_site_use_v2_bos (-)',
1409 p_prefix=>l_debug_prefix,
1410 p_msg_level=>fnd_log.level_procedure);
1411 END IF;
1412 WHEN OTHERS THEN
1413 x_return_status := fnd_api.g_ret_sts_unexp_error;
1414
1415 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1416 fnd_message.set_token('ERROR' ,SQLERRM);
1417 fnd_msg_pub.add;
1418
1419 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1420 p_count => x_msg_count,
1421 p_data => x_msg_data);
1422
1423 -- Debug info.
1424 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1425 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1426 p_msg_data=>x_msg_data,
1427 p_msg_type=>'SQL ERROR',
1428 p_msg_level=>fnd_log.level_error);
1429 END IF;
1430 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1431 hz_utility_v2pub.debug(p_message=>'get_cust_site_use_bos (-)',
1432 p_prefix=>l_debug_prefix,
1433 p_msg_level=>fnd_log.level_procedure);
1434 END IF;
1435
1436 end;
1437
1438
1439
1440
1441 --------------------------------------
1442 --
1443 -- PROCEDURE get_cust_acct_site_v2_bos
1444 --
1445 -- DESCRIPTION
1446 -- Get logical customer account sites.
1447 --
1448 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1449 --
1450 -- ARGUMENTS
1451 -- IN:
1452 -- p_init_msg_list Initialize message stack if it is set to
1453 -- p_parent_id parent id.
1454 -- p_cust_acct_site_id customer account site ID.
1455 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
1456 -- OUT:
1457 -- x_cust_acct_site_v2_objs Logical customer account site records.
1458 -- x_return_status Return status after the call. The status can
1459 -- be fnd_api.g_ret_sts_success (success),
1460 -- fnd_api.g_ret_sts_error (error),
1461 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1462 -- x_msg_count Number of messages in message stack.
1463 -- x_msg_data Message text if x_msg_count is 1.
1464 --
1465 -- NOTES
1466 --
1467 -- MODIFICATION HISTORY
1468 --
1469 --
1470 -- 31-JAN-2008 vsegu Created.
1471 --
1472
1473
1474 /*
1475
1476 The Get customer account site API Procedure is a retrieval service that returns a full customer account site business object.
1477 The user identifies a particular Organization Contact business object using the TCA identifier and/or the object's
1478 Source System information. Upon proper validation of the object, the full Organization Contact business object is returned.
1479 The object consists of all data included within the Organization Contact business object, at all embedded levels.
1480 This includes the set of all data stored in the TCA tables for each embedded entity.
1481
1482
1483 Embedded BO Mandatory Multiple Logical API Procedure Comments
1484
1485 Party Site Y N get_party_site_bo
1486 Customer Account Site Contact N Y get_cust_acct_contact_bo
1487 Customer Account Site Use N Y Business Structure. Included entities and
1488 structures:HZ_CUST_SITE_USES_ALL,Customer
1489 Profile (Business Structure)
1490
1491
1492 To retrieve the appropriate embedded entities within the 'Organization Contact' business object, the Get procedure returns
1493 all records for the particular contact from these TCA entity tables.
1494
1495 Embedded TCA Entity Mandatory Multiple TCA Table Entities
1496
1497 Customer account site Y N HZ_CUST_ACCOUNTS
1498 Bank account site Use N Y Owned by Payments team
1499 Payment Method N N Owned by AR team
1500
1501 */
1502
1503
1504
1505 PROCEDURE get_cust_acct_site_v2_bos(
1506 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1507 p_parent_id IN NUMBER,
1508 p_cust_acct_site_id IN NUMBER,
1509 p_action_type IN VARCHAR2 := NULL,
1510 x_cust_acct_site_v2_objs OUT NOCOPY HZ_CUST_ACCT_SITE_V2_BO_TBL,
1511 x_return_status OUT NOCOPY VARCHAR2,
1512 x_msg_count OUT NOCOPY NUMBER,
1513 x_msg_data OUT NOCOPY VARCHAR2
1514 ) is
1515 l_debug_prefix VARCHAR2(30) := '';
1516 --l_party_site_objs HZ_PARTY_SITE_BO_TBL;
1517
1518 CURSOR C1 IS
1519 SELECT HZ_CUST_ACCT_SITE_V2_BO(
1520 P_ACTION_TYPE,
1521 NULL, -- COMMON_OBJ_ID
1522 CUST_ACCT_SITE_ID,
1523 NULL, --ORIG_SYSTEM,
1524 NULL, --ORIG_SYSTEM_REFERENCE,
1525 CUST_ACCOUNT_ID,
1526 ATTRIBUTE_CATEGORY,
1527 ATTRIBUTE1,
1528 ATTRIBUTE2,
1529 ATTRIBUTE3,
1530 ATTRIBUTE4,
1531 ATTRIBUTE5,
1532 ATTRIBUTE6,
1533 ATTRIBUTE7,
1534 ATTRIBUTE8,
1535 ATTRIBUTE9,
1536 ATTRIBUTE10,
1537 ATTRIBUTE11,
1538 ATTRIBUTE12,
1539 ATTRIBUTE13,
1540 ATTRIBUTE14,
1541 ATTRIBUTE15,
1542 ATTRIBUTE16,
1543 ATTRIBUTE17,
1544 ATTRIBUTE18,
1545 ATTRIBUTE19,
1546 ATTRIBUTE20,
1547 GLOBAL_ATTRIBUTE_CATEGORY,
1548 GLOBAL_ATTRIBUTE1,
1549 GLOBAL_ATTRIBUTE2,
1550 GLOBAL_ATTRIBUTE3,
1551 GLOBAL_ATTRIBUTE4,
1552 GLOBAL_ATTRIBUTE5,
1553 GLOBAL_ATTRIBUTE6,
1554 GLOBAL_ATTRIBUTE7,
1555 GLOBAL_ATTRIBUTE8,
1556 GLOBAL_ATTRIBUTE9,
1557 GLOBAL_ATTRIBUTE10,
1558 GLOBAL_ATTRIBUTE11,
1559 GLOBAL_ATTRIBUTE12,
1560 GLOBAL_ATTRIBUTE13,
1561 GLOBAL_ATTRIBUTE14,
1562 GLOBAL_ATTRIBUTE15,
1563 GLOBAL_ATTRIBUTE16,
1564 GLOBAL_ATTRIBUTE17,
1565 GLOBAL_ATTRIBUTE18,
1566 GLOBAL_ATTRIBUTE19,
1567 GLOBAL_ATTRIBUTE20,
1568 STATUS,
1569 CUSTOMER_CATEGORY_CODE,
1570 LANGUAGE,
1571 KEY_ACCOUNT_FLAG,
1572 TP_HEADER_ID,
1573 ECE_TP_LOCATION_CODE,
1574 PRIMARY_SPECIALIST_ID,
1575 SECONDARY_SPECIALIST_ID,
1576 TERRITORY_ID,
1577 TERRITORY,
1578 TRANSLATED_CUSTOMER_NAME,
1579 ORG_ID,
1580 PROGRAM_UPDATE_DATE,
1581 CREATED_BY_MODULE,
1582 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
1583 CREATION_DATE,
1584 LAST_UPDATE_DATE,
1585 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
1586 PARTY_SITE_ID,
1587 NULL, --PARTY_SITE_OS,
1588 NULL, --PARTY_SITE_OSR,
1589 CAST(MULTISET (
1590 SELECT HZ_ORIG_SYS_REF_OBJ(
1591 NULL, --P_ACTION_TYPE,
1592 ORIG_SYSTEM_REF_ID,
1593 ORIG_SYSTEM,
1594 ORIG_SYSTEM_REFERENCE,
1595 OWNER_TABLE_NAME,
1596 OWNER_TABLE_ID,
1597 STATUS,
1598 REASON_CODE,
1599 OLD_ORIG_SYSTEM_REFERENCE,
1600 START_DATE_ACTIVE,
1601 END_DATE_ACTIVE,
1602 PROGRAM_UPDATE_DATE,
1603 CREATED_BY_MODULE,
1604 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
1605 CREATION_DATE,
1606 LAST_UPDATE_DATE,
1607 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
1608 ATTRIBUTE_CATEGORY,
1609 ATTRIBUTE1,
1610 ATTRIBUTE2,
1611 ATTRIBUTE3,
1612 ATTRIBUTE4,
1613 ATTRIBUTE5,
1614 ATTRIBUTE6,
1615 ATTRIBUTE7,
1616 ATTRIBUTE8,
1617 ATTRIBUTE9,
1618 ATTRIBUTE10,
1619 ATTRIBUTE11,
1620 ATTRIBUTE12,
1621 ATTRIBUTE13,
1622 ATTRIBUTE14,
1623 ATTRIBUTE15,
1624 ATTRIBUTE16,
1625 ATTRIBUTE17,
1626 ATTRIBUTE18,
1627 ATTRIBUTE19,
1628 ATTRIBUTE20)
1629 FROM HZ_ORIG_SYS_REFERENCES OSR
1630 WHERE
1631 OSR.OWNER_TABLE_ID = CAS.CUST_ACCT_SITE_ID
1632 AND OWNER_TABLE_NAME = 'HZ_CUST_ACCT_SITES_ALL'
1633 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL), -- acct site ssm
1634 CAST(MULTISET (
1635 SELECT HZ_ORIG_SYS_REF_OBJ(
1636 NULL, --P_ACTION_TYPE,
1637 ORIG_SYSTEM_REF_ID,
1638 ORIG_SYSTEM,
1639 ORIG_SYSTEM_REFERENCE,
1640 OWNER_TABLE_NAME,
1641 OWNER_TABLE_ID,
1642 STATUS,
1643 REASON_CODE,
1644 OLD_ORIG_SYSTEM_REFERENCE,
1645 START_DATE_ACTIVE,
1646 END_DATE_ACTIVE,
1647 PROGRAM_UPDATE_DATE,
1648 CREATED_BY_MODULE,
1649 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
1650 CREATION_DATE,
1651 LAST_UPDATE_DATE,
1652 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
1653 ATTRIBUTE_CATEGORY,
1654 ATTRIBUTE1,
1655 ATTRIBUTE2,
1656 ATTRIBUTE3,
1657 ATTRIBUTE4,
1658 ATTRIBUTE5,
1659 ATTRIBUTE6,
1660 ATTRIBUTE7,
1661 ATTRIBUTE8,
1662 ATTRIBUTE9,
1663 ATTRIBUTE10,
1664 ATTRIBUTE11,
1665 ATTRIBUTE12,
1666 ATTRIBUTE13,
1667 ATTRIBUTE14,
1668 ATTRIBUTE15,
1669 ATTRIBUTE16,
1670 ATTRIBUTE17,
1671 ATTRIBUTE18,
1672 ATTRIBUTE19,
1673 ATTRIBUTE20)
1674 FROM HZ_ORIG_SYS_REFERENCES OSR
1675 WHERE
1676 OSR.OWNER_TABLE_ID = CAS.PARTY_SITE_ID
1677 AND OWNER_TABLE_NAME = 'HZ_PARTY_SITES'
1678 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL), -- party site ssm
1679 HZ_CUST_SITE_USE_V2_BO_TBL(),
1680 HZ_CUST_ACCT_CONTACT_BO_TBL())
1681 FROM HZ_CUST_ACCT_SITES CAS
1682 WHERE ((P_CUST_ACCT_SITE_ID IS NULL AND CUST_ACCOUNT_ID = P_PARENT_ID)
1683 OR (P_CUST_ACCT_SITE_ID IS NOT NULL
1684 AND CUST_ACCT_SITE_ID = P_CUST_ACCT_SITE_ID));
1685
1686
1687
1688 BEGIN
1689
1690
1691 -- initialize API return status to success.
1692 x_return_status := FND_API.G_RET_STS_SUCCESS;
1693
1694 -- Initialize message list if p_init_msg_list is set to TRUE
1695 IF FND_API.to_Boolean(p_init_msg_list) THEN
1696 FND_MSG_PUB.initialize;
1697 END IF;
1698
1699
1700 -- Debug info.
1701 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1702 hz_utility_v2pub.debug(p_message=>'get_cust_acct_site_v2_bos(+)',
1703 p_prefix=>l_debug_prefix,
1704 p_msg_level=>fnd_log.level_procedure);
1705 END IF;
1706
1707 x_cust_acct_site_v2_objs := HZ_CUST_ACCT_SITE_V2_BO_TBL();
1708 open c1;
1709 fetch c1 BULK COLLECT into x_cust_acct_site_v2_objs;
1710 close c1;
1711
1712 for i in 1..x_cust_acct_site_v2_objs.count loop
1713 get_cust_site_use_v2_bos(
1714 p_init_msg_list => fnd_api.g_false,
1715 p_cust_acct_site_id => x_cust_acct_site_v2_objs(i).cust_acct_site_id,
1716 p_action_type => p_action_type,
1717 x_cust_site_use_v2_objs =>x_cust_acct_site_v2_objs(i).CUST_ACCT_SITE_USE_OBJS,
1718 x_return_status => x_return_status,
1719 x_msg_count => x_msg_count,
1720 x_msg_data => x_msg_data);
1721
1722 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1723 RAISE FND_API.G_EXC_ERROR;
1724 END IF;
1725
1726 HZ_EXTRACT_ACCT_CONT_BO_PVT.get_cust_acct_contact_bos(
1727 p_init_msg_list => fnd_api.g_false,
1728 p_parent_id => x_cust_acct_site_v2_objs(i).cust_acct_site_id,
1729 p_cust_acct_contact_id => null,
1730 p_action_type => p_action_type,
1731 x_cust_acct_contact_objs =>x_cust_acct_site_v2_objs(i).CUST_ACCT_CONTACT_OBJS,
1732 x_return_status => x_return_status,
1733 x_msg_count => x_msg_count,
1734 x_msg_data => x_msg_data);
1735
1736 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1737 RAISE FND_API.G_EXC_ERROR;
1738 END IF;
1739
1740
1741 end loop;
1742
1743 -- Debug info.
1744 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1745 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1746 p_msg_data=>x_msg_data,
1747 p_msg_type=>'WARNING',
1748 p_msg_level=>fnd_log.level_exception);
1749 END IF;
1750
1751 -- Debug info.
1752 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1753 hz_utility_v2pub.debug(p_message=>'get_cust_acct_site_v2_bos (-)',
1754 p_prefix=>l_debug_prefix,
1755 p_msg_level=>fnd_log.level_procedure);
1756 END IF;
1757
1758
1759 EXCEPTION
1760
1761 WHEN fnd_api.g_exc_error THEN
1762 x_return_status := fnd_api.g_ret_sts_error;
1763
1764 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1765 p_count => x_msg_count,
1766 p_data => x_msg_data);
1767
1768 -- Debug info.
1769 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1770 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1771 p_msg_data=>x_msg_data,
1772 p_msg_type=>'ERROR',
1773 p_msg_level=>fnd_log.level_error);
1774 END IF;
1775 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1776 hz_utility_v2pub.debug(p_message=>'get_cust_acct_site_v2_bos (-)',
1777 p_prefix=>l_debug_prefix,
1778 p_msg_level=>fnd_log.level_procedure);
1779 END IF;
1780 WHEN fnd_api.g_exc_unexpected_error THEN
1781 x_return_status := fnd_api.g_ret_sts_unexp_error;
1782
1783 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1784 p_count => x_msg_count,
1785 p_data => x_msg_data);
1786
1787 -- Debug info.
1788 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1789 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1790 p_msg_data=>x_msg_data,
1791 p_msg_type=>'UNEXPECTED ERROR',
1792 p_msg_level=>fnd_log.level_error);
1793 END IF;
1794 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1795 hz_utility_v2pub.debug(p_message=>'get_cust_acct_site_v2_bos (-)',
1796 p_prefix=>l_debug_prefix,
1797 p_msg_level=>fnd_log.level_procedure);
1798 END IF;
1799 WHEN OTHERS THEN
1800 x_return_status := fnd_api.g_ret_sts_unexp_error;
1801
1802 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1803 fnd_message.set_token('ERROR' ,SQLERRM);
1804 fnd_msg_pub.add;
1805
1806 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1807 p_count => x_msg_count,
1808 p_data => x_msg_data);
1809
1810 -- Debug info.
1811 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1812 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1813 p_msg_data=>x_msg_data,
1814 p_msg_type=>'SQL ERROR',
1815 p_msg_level=>fnd_log.level_error);
1816 END IF;
1817 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1818 hz_utility_v2pub.debug(p_message=>'get_cust_acct_site_bos (-)',
1819 p_prefix=>l_debug_prefix,
1820 p_msg_level=>fnd_log.level_procedure);
1821 END IF;
1822
1823 end;
1824
1825
1826 END HZ_EXTRACT_ACCT_SITE_BO_PVT;