[Home] [Help]
PACKAGE BODY: APPS.HZ_EXTRACT_ORGANIZATION_BO_PVT
Source
1 PACKAGE BODY HZ_EXTRACT_ORGANIZATION_BO_PVT AS
2 /*$Header: ARHEPOVB.pls 120.11.12000000.2 2007/02/23 20:55:37 awu ship $ */
3 /*
4 * This package contains the private APIs for logical organization.
5 * @rep:scope private
6 * @rep:product HZ
7 * @rep:displayname Organization
8 * @rep:category BUSINESS_ENTITY HZ_PARTIES
9 * @rep:lifecycle active
10 * @rep:doccd 115hztig.pdf Organization Get APIs
11 */
12
13 --------------------------------------
14 --
15 -- PROCEDURE get_organization_bo
16 --
17 -- DESCRIPTION
18 -- Get a logical organization.
19 --
20 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
21 --
22 -- ARGUMENTS
23 -- IN:
24 -- p_init_msg_list Initialize message stack if it is set to
25 -- p_organization_id Organization ID.
26 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
27 -- OUT:
28 -- x_organization_obj Logical organization record.
29 -- x_return_status Return status after the call. The status can
30 -- be fnd_api.g_ret_sts_success (success),
31 -- fnd_api.g_ret_sts_error (error),
32 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
33 -- x_msg_count Number of messages in message stack.
34 -- x_msg_data Message text if x_msg_count is 1.
35 --
36 -- NOTES
37 --
38 -- MODIFICATION HISTORY
39 --
40 --
41 -- 06-JUN-2005 AWU Created.
42 --
43
44 -- private procedure get_financial_report_bos
45
46 procedure get_financial_report_bos(
47 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
48 p_organization_id IN NUMBER,
49 p_action_type IN VARCHAR2 := NULL,
50 x_financial_report_objs OUT NOCOPY HZ_FINANCIAL_BO_TBL,
51 x_return_status OUT NOCOPY VARCHAR2,
52 x_msg_count OUT NOCOPY NUMBER,
53 x_msg_data OUT NOCOPY VARCHAR2
54 ) is
55 CURSOR C1 IS
56 SELECT HZ_FINANCIAL_BO(
57 P_ACTION_TYPE,
58 NULL, -- COMMON_OBJ_ID
59 FINANCIAL_REPORT_ID,
60 PARTY_ID,
61 TYPE_OF_FINANCIAL_REPORT,
62 DOCUMENT_REFERENCE,
63 DATE_REPORT_ISSUED,
64 ISSUED_PERIOD,
65 REPORT_START_DATE,
66 REPORT_END_DATE,
67 ACTUAL_CONTENT_SOURCE,
68 REQUIRING_AUTHORITY,
69 AUDIT_IND,
70 CONSOLIDATED_IND,
71 ESTIMATED_IND,
72 FISCAL_IND,
73 FINAL_IND,
74 FORECAST_IND,
75 OPENING_IND,
76 PROFORMA_IND,
77 QUALIFIED_IND,
78 RESTATED_IND,
79 SIGNED_BY_PRINCIPALS_IND,
80 TRIAL_BALANCE_IND,
81 UNBALANCED_IND,
82 STATUS,
83 PROGRAM_UPDATE_DATE,
84 CREATED_BY_MODULE,
85 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
86 CREATION_DATE,
87 LAST_UPDATE_DATE,
88 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
89 CAST(MULTISET (
90 SELECT HZ_FINANCIAL_NUMBER_OBJ(
91 P_ACTION_TYPE,
92 NULL, -- COMMON_OBJ_ID
93 FINANCIAL_NUMBER_ID,
94 FINANCIAL_REPORT_ID,
95 FINANCIAL_NUMBER,
96 FINANCIAL_NUMBER_NAME,
97 FINANCIAL_UNITS_APPLIED,
98 FINANCIAL_NUMBER_CURRENCY,
99 PROJECTED_ACTUAL_FLAG,
100 STATUS,
101 PROGRAM_UPDATE_DATE,
102 CREATED_BY_MODULE,
103 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
104 CREATION_DATE,
105 LAST_UPDATE_DATE,
106 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
107 ACTUAL_CONTENT_SOURCE)
108 FROM HZ_FINANCIAL_NUMBERS
109 WHERE FINANCIAL_REPORT_ID = FR.FINANCIAL_REPORT_ID) AS HZ_FINANCIAL_NUMBER_OBJ_TBL))
110 FROM HZ_FINANCIAL_REPORTS FR
111 WHERE PARTY_ID = P_ORGANIZATION_ID;
112
113 l_debug_prefix VARCHAR2(30) := '';
114
115 BEGIN
116
117
118 -- initialize API return status to success.
119 x_return_status := FND_API.G_RET_STS_SUCCESS;
120
121 -- Initialize message list if p_init_msg_list is set to TRUE
122 IF FND_API.to_Boolean(p_init_msg_list) THEN
123 FND_MSG_PUB.initialize;
124 END IF;
125
126
127 -- Debug info.
128 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
129 hz_utility_v2pub.debug(p_message=>'get_financial_report_bos(+)',
130 p_prefix=>l_debug_prefix,
131 p_msg_level=>fnd_log.level_procedure);
132 END IF;
133
134 x_financial_report_objs := HZ_FINANCIAL_BO_TBL();
135 open c1;
136 fetch c1 BULK COLLECT into x_financial_report_objs;
137 close c1;
138
139 -- Debug info.
140 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
141 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
142 p_msg_data=>x_msg_data,
143 p_msg_type=>'WARNING',
144 p_msg_level=>fnd_log.level_exception);
145 END IF;
146
147 -- Debug info.
148 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
149 hz_utility_v2pub.debug(p_message=>'get_financial_report_bos (-)',
150 p_prefix=>l_debug_prefix,
151 p_msg_level=>fnd_log.level_procedure);
152 END IF;
153
154
155 EXCEPTION
156
157 WHEN fnd_api.g_exc_error THEN
158 x_return_status := fnd_api.g_ret_sts_error;
159
160 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
161 p_count => x_msg_count,
162 p_data => x_msg_data);
163
164 -- Debug info.
165 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
166 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
167 p_msg_data=>x_msg_data,
168 p_msg_type=>'ERROR',
169 p_msg_level=>fnd_log.level_error);
170 END IF;
171 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
172 hz_utility_v2pub.debug(p_message=>'get_financial_report_bos (-)',
173 p_prefix=>l_debug_prefix,
174 p_msg_level=>fnd_log.level_procedure);
175 END IF;
176 WHEN fnd_api.g_exc_unexpected_error THEN
177 x_return_status := fnd_api.g_ret_sts_unexp_error;
178
179 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
180 p_count => x_msg_count,
181 p_data => x_msg_data);
182
183 -- Debug info.
184 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
185 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
186 p_msg_data=>x_msg_data,
187 p_msg_type=>'UNEXPECTED ERROR',
188 p_msg_level=>fnd_log.level_error);
189 END IF;
190 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
191 hz_utility_v2pub.debug(p_message=>'get_financial_report_bos (-)',
192 p_prefix=>l_debug_prefix,
193 p_msg_level=>fnd_log.level_procedure);
194 END IF;
195 WHEN OTHERS THEN
196 x_return_status := fnd_api.g_ret_sts_unexp_error;
197
198 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
199 fnd_message.set_token('ERROR' ,SQLERRM);
200 fnd_msg_pub.add;
201
202 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
203 p_count => x_msg_count,
204 p_data => x_msg_data);
205
206 -- Debug info.
207 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
208 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
209 p_msg_data=>x_msg_data,
210 p_msg_type=>'SQL ERROR',
211 p_msg_level=>fnd_log.level_error);
212 END IF;
213 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
214 hz_utility_v2pub.debug(p_message=>'get_financial_report_bos (-)',
215 p_prefix=>l_debug_prefix,
216 p_msg_level=>fnd_log.level_procedure);
217 END IF;
218
219 end;
220
221
222
223 /*
224 The Get Organization API Procedure is a retrieval service that returns a full Organization business object.
225 The user identifies a particular Organization business object using the TCA identifier and/or
226 the object Source System information. Upon proper validation of the object,
227 the full Organization business object is returned. The object consists of all data included within
228 the Organization business object, at all embedded levels. This includes the set of all data stored
229 in the TCA tables for each embedded entity.
230
231 To retrieve the appropriate embedded business objects within the Organization business object,
232 the Get procedure calls the equivalent procedure for the following embedded objects:
233
234 Embedded BO Mandatory Multiple Logical API Procedure Comments
235 Org Contact N Y get_contact_bo
236 Party Site N Y get_party_site_bo
237 Phone N Y get_phone_bo
238 Telex N Y get_telex_bo
239 Email N Y get_email_bo
240 Web N Y get_web_bo
241 EDI N Y get_edi_bo
242 EFT N Y get_eft_bo
243 Financial Report N Y Business Structure. Included entities: HZ_FINANCIAL_REPORTS, HZ_FINANCIAL_NUMBERS
244
245
246 To retrieve the appropriate embedded entities within the Organization business object,
247 the Get procedure returns all records for the particular organization from these TCA entity tables:
248
249 Embedded TCA Entity Mandatory Multiple TCA Table Entities
250
251 Party, Org Profile Y N HZ_PARTIES, HZ_ORGANIZATION_PROFILES
252 Org Preference N Y HZ_PARTY_PREFERENCES
253 Relationship N Y HZ_RELATIONSHIPS
254 Classification N Y HZ_CODE_ASSIGNMENTS
255 Credit Rating N Y HZ_CREDIT_RATINGS
256 Certification N Y HZ_CERTIFICATIONS
257 Financial Profile N Y HZ_FINANCIAL_PROFILE
258
259 */
260
261
262 PROCEDURE get_organization_bo(
263 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
264 p_organization_id IN NUMBER,
265 p_action_type IN VARCHAR2 := NULL,
266 x_organization_obj OUT NOCOPY HZ_ORGANIZATION_BO,
267 x_return_status OUT NOCOPY VARCHAR2,
268 x_msg_count OUT NOCOPY NUMBER,
269 x_msg_data OUT NOCOPY VARCHAR2
270 ) is
271
272 CURSOR C1 IS
273 SELECT HZ_ORGANIZATION_BO(
274 P_ACTION_TYPE,
275 NULL, -- COMMON_OBJ_ID
276 P.PARTY_ID,
277 NULL, --ORIG_SYSTEM,
278 NULL, --ORIG_SYSTEM_REFERENCE,
279 P.PARTY_NUMBER,
280 P.VALIDATED_FLAG,
281 P.STATUS,
282 P.CATEGORY_CODE,
283 P.SALUTATION,
284 P.ATTRIBUTE_CATEGORY,
285 P.ATTRIBUTE1,
286 P.ATTRIBUTE2,
287 P.ATTRIBUTE3,
288 P.ATTRIBUTE4,
289 P.ATTRIBUTE5,
290 P.ATTRIBUTE6,
291 P.ATTRIBUTE7,
292 P.ATTRIBUTE8,
293 P.ATTRIBUTE9,
294 P.ATTRIBUTE10,
295 P.ATTRIBUTE11,
296 P.ATTRIBUTE12,
297 P.ATTRIBUTE13,
298 P.ATTRIBUTE14,
299 P.ATTRIBUTE15,
300 P.ATTRIBUTE16,
301 P.ATTRIBUTE17,
302 P.ATTRIBUTE18,
303 P.ATTRIBUTE19,
304 P.ATTRIBUTE20,
305 P.ATTRIBUTE21,
306 P.ATTRIBUTE22,
307 P.ATTRIBUTE23,
308 P.ATTRIBUTE24,
309 PRO.ORGANIZATION_NAME,
310 PRO.DUNS_NUMBER_C,
311 PRO.ENQUIRY_DUNS,
312 PRO.CEO_NAME,
313 PRO.CEO_TITLE,
314 PRO.PRINCIPAL_NAME,
315 PRO.PRINCIPAL_TITLE,
316 PRO.LEGAL_STATUS,
317 PRO.CONTROL_YR,
318 PRO.EMPLOYEES_TOTAL,
319 PRO.HQ_BRANCH_IND,
320 PRO.BRANCH_FLAG,
321 PRO.OOB_IND,
322 PRO.LINE_OF_BUSINESS,
323 PRO.CONG_DIST_CODE,
324 PRO.SIC_CODE,
325 PRO.IMPORT_IND,
326 PRO.EXPORT_IND,
327 PRO.LABOR_SURPLUS_IND,
328 PRO.DEBARMENT_IND,
329 PRO.MINORITY_OWNED_IND,
330 PRO.MINORITY_OWNED_TYPE,
331 PRO.WOMAN_OWNED_IND,
332 PRO.DISADV_8A_IND,
333 PRO.SMALL_BUS_IND,
334 PRO.RENT_OWN_IND,
335 PRO.DEBARMENTS_COUNT,
336 PRO.DEBARMENTS_DATE,
337 PRO.FAILURE_SCORE,
338 PRO.FAILURE_SCORE_NATNL_PERCENTILE,
339 PRO.FAILURE_SCORE_OVERRIDE_CODE,
340 PRO.FAILURE_SCORE_COMMENTARY,
341 PRO.GLOBAL_FAILURE_SCORE,
342 PRO.DB_RATING,
343 PRO.CREDIT_SCORE,
344 PRO.CREDIT_SCORE_COMMENTARY,
345 PRO.PAYDEX_SCORE,
346 PRO.PAYDEX_THREE_MONTHS_AGO,
347 PRO.PAYDEX_NORM,
348 PRO.BEST_TIME_CONTACT_BEGIN,
349 PRO.BEST_TIME_CONTACT_END,
350 PRO.ORGANIZATION_NAME_PHONETIC,
351 PRO.TAX_REFERENCE,
352 PRO.GSA_INDICATOR_FLAG,
353 PRO.JGZZ_FISCAL_CODE,
354 PRO.ANALYSIS_FY,
355 PRO.FISCAL_YEAREND_MONTH,
356 PRO.CURR_FY_POTENTIAL_REVENUE,
357 PRO.NEXT_FY_POTENTIAL_REVENUE,
358 PRO.YEAR_ESTABLISHED,
359 PRO.MISSION_STATEMENT,
360 PRO.ORGANIZATION_TYPE,
361 PRO.BUSINESS_SCOPE,
362 PRO.CORPORATION_CLASS,
363 PRO.KNOWN_AS,
364 PRO.KNOWN_AS2,
365 PRO.KNOWN_AS3,
366 PRO.KNOWN_AS4,
367 PRO.KNOWN_AS5,
368 PRO.LOCAL_BUS_IDEN_TYPE,
369 PRO.LOCAL_BUS_IDENTIFIER,
370 PRO.PREF_FUNCTIONAL_CURRENCY,
371 PRO.REGISTRATION_TYPE,
372 PRO.TOTAL_EMPLOYEES_TEXT,
373 PRO.TOTAL_EMPLOYEES_IND,
374 PRO.TOTAL_EMP_EST_IND,
375 PRO.TOTAL_EMP_MIN_IND,
376 PRO.PARENT_SUB_IND,
377 PRO.INCORP_YEAR,
378 PRO.SIC_CODE_TYPE,
379 PRO.PUBLIC_PRIVATE_OWNERSHIP_FLAG,
380 PRO.INTERNAL_FLAG,
381 PRO.LOCAL_ACTIVITY_CODE_TYPE,
382 PRO.LOCAL_ACTIVITY_CODE,
383 PRO.EMP_AT_PRIMARY_ADR,
384 PRO.EMP_AT_PRIMARY_ADR_TEXT,
385 PRO.EMP_AT_PRIMARY_ADR_EST_IND,
386 PRO.EMP_AT_PRIMARY_ADR_MIN_IND,
387 PRO.HIGH_CREDIT,
388 PRO.AVG_HIGH_CREDIT,
389 PRO.TOTAL_PAYMENTS,
390 PRO.CREDIT_SCORE_CLASS,
391 PRO.CREDIT_SCORE_NATL_PERCENTILE,
392 PRO.CREDIT_SCORE_INCD_DEFAULT,
393 PRO.CREDIT_SCORE_AGE,
394 PRO.CREDIT_SCORE_DATE,
395 PRO.CREDIT_SCORE_COMMENTARY2,
396 PRO.CREDIT_SCORE_COMMENTARY3,
397 PRO.CREDIT_SCORE_COMMENTARY4,
398 PRO.CREDIT_SCORE_COMMENTARY5,
399 PRO.CREDIT_SCORE_COMMENTARY6,
400 PRO.CREDIT_SCORE_COMMENTARY7,
401 PRO.CREDIT_SCORE_COMMENTARY8,
402 PRO.CREDIT_SCORE_COMMENTARY9,
403 PRO.CREDIT_SCORE_COMMENTARY10,
404 PRO.FAILURE_SCORE_CLASS,
405 PRO.FAILURE_SCORE_INCD_DEFAULT,
406 PRO.FAILURE_SCORE_AGE,
407 PRO.FAILURE_SCORE_DATE,
408 PRO.FAILURE_SCORE_COMMENTARY2,
409 PRO.FAILURE_SCORE_COMMENTARY3,
410 PRO.FAILURE_SCORE_COMMENTARY4,
411 PRO.FAILURE_SCORE_COMMENTARY5,
412 PRO.FAILURE_SCORE_COMMENTARY6,
413 PRO.FAILURE_SCORE_COMMENTARY7,
414 PRO.FAILURE_SCORE_COMMENTARY8,
415 PRO.FAILURE_SCORE_COMMENTARY9,
416 PRO.FAILURE_SCORE_COMMENTARY10,
417 PRO.MAXIMUM_CREDIT_RECOMMENDATION,
418 PRO.MAXIMUM_CREDIT_CURRENCY_CODE,
419 PRO.DISPLAYED_DUNS_PARTY_ID,
420 PRO.PROGRAM_UPDATE_DATE,
421 PRO.CREATED_BY_MODULE,
422 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(PRO.CREATED_BY),
423 PRO.CREATION_DATE,
424 PRO.LAST_UPDATE_DATE,
425 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(PRO.LAST_UPDATED_BY),
426 PRO.DO_NOT_CONFUSE_WITH,
427 PRO.ACTUAL_CONTENT_SOURCE,
428 HZ_ORIG_SYS_REF_OBJ_TBL(),
429 HZ_EXT_ATTRIBUTE_OBJ_TBL(),
430 HZ_ORG_CONTACT_BO_TBL(),
431 HZ_PARTY_SITE_BO_TBL(),
432 CAST(MULTISET (
433 SELECT HZ_PARTY_PREF_OBJ(
434 P_ACTION_TYPE,
435 NULL, -- COMMON_OBJ_ID
436 PARTY_PREFERENCE_ID,
437 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type('HZ_PARTIES',PARTY_ID),
438 PARTY_ID,
439 CATEGORY,
440 PREFERENCE_CODE,
441 VALUE_VARCHAR2,
442 VALUE_NUMBER,
443 VALUE_DATE,
444 VALUE_NAME,
445 MODULE,
446 ADDITIONAL_VALUE1,
447 ADDITIONAL_VALUE2,
448 ADDITIONAL_VALUE3,
449 ADDITIONAL_VALUE4,
450 ADDITIONAL_VALUE5,
451 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
452 CREATION_DATE,
453 LAST_UPDATE_DATE,
454 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
455 FROM HZ_PARTY_PREFERENCES
456 WHERE PARTY_ID = P_ORGANIZATION_ID) AS HZ_PARTY_PREF_OBJ_TBL),
457 HZ_PHONE_CP_BO_TBL(),
458 HZ_TELEX_CP_BO_TBL(),
459 HZ_EMAIL_CP_BO_TBL(),
460 HZ_WEB_CP_BO_TBL(),
461 HZ_EDI_CP_BO_TBL(),
462 HZ_EFT_CP_BO_TBL(),
463 HZ_RELATIONSHIP_OBJ_TBL(),
464 CAST(MULTISET (
465 SELECT HZ_CODE_ASSIGNMENT_OBJ(
466 P_ACTION_TYPE,
467 NULL, -- COMMON_OBJ_ID
468 CODE_ASSIGNMENT_ID,
469 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type('HZ_PARTIES',OWNER_TABLE_ID),
470 OWNER_TABLE_ID,
471 CLASS_CATEGORY,
472 CLASS_CODE,
473 PRIMARY_FLAG,
474 ACTUAL_CONTENT_SOURCE,
475 START_DATE_ACTIVE,
476 END_DATE_ACTIVE,
477 STATUS,
478 PROGRAM_UPDATE_DATE,
479 CREATED_BY_MODULE,
480 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
481 CREATION_DATE,
482 LAST_UPDATE_DATE,
483 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
484 RANK)
485 FROM HZ_CODE_ASSIGNMENTS
486 WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
487 AND OWNER_TABLE_ID = P_ORGANIZATION_ID) AS HZ_CODE_ASSIGNMENT_OBJ_TBL),
488 HZ_FINANCIAL_BO_TBL(),
489 CAST(MULTISET (
490 SELECT HZ_CREDIT_RATING_OBJ(
491 P_ACTION_TYPE,
492 NULL, -- COMMON_OBJ_ID
493 CREDIT_RATING_ID,
494 DESCRIPTION,
495 PARTY_ID,
496 RATING,
497 RATED_AS_OF_DATE,
498 RATING_ORGANIZATION,
499 COMMENTS,
500 DET_HISTORY_IND,
501 FINCL_EMBT_IND,
502 CRIMINAL_PROCEEDING_IND,
503 CLAIMS_IND,
504 SECURED_FLNG_IND,
505 FINCL_LGL_EVENT_IND,
506 DISASTER_IND,
507 OPRG_SPEC_EVNT_IND,
508 OTHER_SPEC_EVNT_IND,
509 STATUS,
510 AVG_HIGH_CREDIT,
511 CREDIT_SCORE,
512 CREDIT_SCORE_AGE,
513 CREDIT_SCORE_CLASS,
514 CREDIT_SCORE_COMMENTARY,
515 CREDIT_SCORE_COMMENTARY2,
516 CREDIT_SCORE_COMMENTARY3,
517 CREDIT_SCORE_COMMENTARY4,
518 CREDIT_SCORE_COMMENTARY5,
519 CREDIT_SCORE_COMMENTARY6,
520 CREDIT_SCORE_COMMENTARY7,
521 CREDIT_SCORE_COMMENTARY8,
522 CREDIT_SCORE_COMMENTARY9,
523 CREDIT_SCORE_COMMENTARY10,
524 CREDIT_SCORE_DATE,
525 CREDIT_SCORE_INCD_DEFAULT,
526 CREDIT_SCORE_NATL_PERCENTILE,
527 FAILURE_SCORE,
528 FAILURE_SCORE_AGE,
529 FAILURE_SCORE_CLASS,
530 FAILURE_SCORE_COMMENTARY,
531 FAILURE_SCORE_COMMENTARY2,
532 FAILURE_SCORE_COMMENTARY3,
533 FAILURE_SCORE_COMMENTARY4,
534 FAILURE_SCORE_COMMENTARY5,
535 FAILURE_SCORE_COMMENTARY6,
536 FAILURE_SCORE_COMMENTARY7,
537 FAILURE_SCORE_COMMENTARY8,
538 FAILURE_SCORE_COMMENTARY9,
539 FAILURE_SCORE_COMMENTARY10,
540 FAILURE_SCORE_DATE,
541 FAILURE_SCORE_INCD_DEFAULT,
542 FAILURE_SCORE_NATNL_PERCENTILE,
543 FAILURE_SCORE_OVERRIDE_CODE,
544 GLOBAL_FAILURE_SCORE,
545 DEBARMENT_IND,
546 DEBARMENTS_COUNT,
547 DEBARMENTS_DATE,
548 HIGH_CREDIT,
549 MAXIMUM_CREDIT_CURRENCY_CODE,
550 MAXIMUM_CREDIT_RECOMMENDATION,
551 PAYDEX_NORM,
552 PAYDEX_SCORE,
553 PAYDEX_THREE_MONTHS_AGO,
554 CREDIT_SCORE_OVERRIDE_CODE,
555 CR_SCR_CLAS_EXPL,
556 LOW_RNG_DELQ_SCR,
557 HIGH_RNG_DELQ_SCR,
558 DELQ_PMT_RNG_PRCNT,
559 DELQ_PMT_PCTG_FOR_ALL_FIRMS,
560 NUM_TRADE_EXPERIENCES,
561 PAYDEX_FIRM_DAYS,
562 PAYDEX_FIRM_COMMENT,
563 PAYDEX_INDUSTRY_DAYS,
564 PAYDEX_INDUSTRY_COMMENT,
565 PAYDEX_COMMENT,
566 SUIT_IND,
567 LIEN_IND,
568 JUDGEMENT_IND,
569 BANKRUPTCY_IND,
570 NO_TRADE_IND,
571 PRNT_HQ_BKCY_IND,
572 NUM_PRNT_BKCY_FILING,
573 PRNT_BKCY_FILG_TYPE,
574 PRNT_BKCY_FILG_CHAPTER,
575 PRNT_BKCY_FILG_DATE,
576 NUM_PRNT_BKCY_CONVS,
577 PRNT_BKCY_CONV_DATE,
578 PRNT_BKCY_CHAPTER_CONV,
579 SLOW_TRADE_EXPL,
580 NEGV_PMT_EXPL,
581 PUB_REC_EXPL,
582 BUSINESS_DISCONTINUED,
583 SPCL_EVENT_COMMENT,
584 NUM_SPCL_EVENT,
585 SPCL_EVENT_UPDATE_DATE,
586 SPCL_EVNT_TXT,
587 ACTUAL_CONTENT_SOURCE,
588 PROGRAM_UPDATE_DATE,
589 CREATED_BY_MODULE,
590 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
591 CREATION_DATE,
592 LAST_UPDATE_DATE,
593 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
594 FROM HZ_CREDIT_RATINGS
595 WHERE PARTY_ID = P_ORGANIZATION_ID) AS HZ_CREDIT_RATING_OBJ_TBL),
596 CAST(MULTISET (
597 SELECT HZ_CERTIFICATION_OBJ(
598 P_ACTION_TYPE,
599 NULL, -- COMMON_OBJ_ID
600 CERTIFICATION_ID,
601 CERTIFICATION_NAME,
602 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type('HZ_PARTIES',PARTY_ID),
603 PARTY_ID,
604 CURRENT_STATUS,
605 EXPIRES_ON_DATE,
606 GRADE,
607 ISSUED_BY_AUTHORITY,
608 ISSUED_ON_DATE,
609 --WH_UPDATE_DATE,
610 STATUS,
611 PROGRAM_UPDATE_DATE,
612 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
613 CREATION_DATE,
614 LAST_UPDATE_DATE,
615 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
616 FROM HZ_CERTIFICATIONS
617 WHERE PARTY_ID = P_ORGANIZATION_ID) AS HZ_CERTIFICATION_OBJ_TBL),
618 CAST(MULTISET (
619 SELECT HZ_FINANCIAL_PROF_OBJ(
620 P_ACTION_TYPE,
621 NULL, -- COMMON_OBJ_ID
622 FINANCIAL_PROFILE_ID,
623 ACCESS_AUTHORITY_DATE,
624 ACCESS_AUTHORITY_GRANTED,
625 BALANCE_AMOUNT,
626 BALANCE_VERIFIED_ON_DATE,
627 FINANCIAL_ACCOUNT_NUMBER,
628 FINANCIAL_ACCOUNT_TYPE,
629 FINANCIAL_ORG_TYPE,
630 FINANCIAL_ORGANIZATION_NAME,
631 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type('HZ_PARTIES',PARTY_ID),
632 PARTY_ID,
633 --WH_UPDATE_DATE,
634 STATUS,
635 PROGRAM_UPDATE_DATE,
636 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
637 CREATION_DATE,
638 LAST_UPDATE_DATE,
639 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
640 FROM HZ_FINANCIAL_PROFILE
641 WHERE PARTY_ID = P_ORGANIZATION_ID) AS HZ_FINANCIAL_PROF_OBJ_TBL),
642 HZ_CONTACT_PREF_OBJ_TBL(),
643 HZ_PARTY_USAGE_OBJ_TBL())
644 FROM HZ_ORGANIZATION_PROFILES PRO, HZ_PARTIES P
645 WHERE PRO.PARTY_ID = P.PARTY_ID
646 AND PRO.PARTY_ID = P_ORGANIZATION_ID
647 AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND NVL(EFFECTIVE_END_DATE,SYSDATE);
648
649 cursor get_profile_id_csr is
650 select organization_profile_id
651 from HZ_ORGANIZATION_PROFILES
652 where party_id = p_organization_id
653 AND sysdate between effective_start_date and nvl(effective_end_date,sysdate);
654
655 l_debug_prefix VARCHAR2(30) := '';
656 l_prof_id number;
657 BEGIN
658
659 -- initialize API return status to success.
660 x_return_status := FND_API.G_RET_STS_SUCCESS;
661
662 -- Initialize message list if p_init_msg_list is set to TRUE
663 IF FND_API.to_Boolean(p_init_msg_list) THEN
664 FND_MSG_PUB.initialize;
665 END IF;
666
667
668 -- Debug info.
669 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
670 hz_utility_v2pub.debug(p_message=>'get_organization_bo(+)',
671 p_prefix=>l_debug_prefix,
672 p_msg_level=>fnd_log.level_procedure);
673 END IF;
674
675 open c1;
676 fetch c1 into x_organization_obj;
677 close c1;
678
679 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
680 (p_init_msg_list => fnd_api.g_false,
681 p_owner_table_id => p_organization_id,
682 p_owner_table_name => 'HZ_PARTIES',
683 p_action_type => NULL, --p_action_type,
684 x_orig_sys_ref_objs => x_organization_obj.orig_sys_objs,
685 x_return_status => x_return_status,
686 x_msg_count => x_msg_count,
687 x_msg_data => x_msg_data);
688
689 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
690 RAISE FND_API.G_EXC_ERROR;
691 END IF;
692
693 open get_profile_id_csr;
694 fetch get_profile_id_csr into l_prof_id;
695 close get_profile_id_csr;
696
697 hz_extract_ext_attri_bo_pvt.get_ext_attribute_bos
698 (p_init_msg_list => fnd_api.g_false,
699 p_ext_object_id => l_prof_id,
700 p_ext_object_name => 'HZ_ORGANIZATION_PROFILES',
701 p_action_type => p_action_type,
702 x_ext_attribute_objs => x_organization_obj.ext_attributes_objs,
703 x_return_status => x_return_status,
704 x_msg_count => x_msg_count,
705 x_msg_data => x_msg_data);
706
707 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
708 RAISE FND_API.G_EXC_ERROR;
709 END IF;
710
711 HZ_EXTRACT_PARTY_SITE_BO_PVT.get_party_site_bos
712 (p_init_msg_list => fnd_api.g_false,
713 p_party_id => p_organization_id,
714 p_party_site_id => NULL,
715 p_action_type => p_action_type,
716 x_party_site_objs => x_organization_obj.party_site_objs,
717 x_return_status => x_return_status,
718 x_msg_count => x_msg_count,
719 x_msg_data => x_msg_data);
720
721 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
722 RAISE FND_API.G_EXC_ERROR;
723 END IF;
724
725 HZ_EXTRACT_PARTY_USAGE_BO_PVT.get_party_usage_bos
726 (p_init_msg_list => fnd_api.g_false,
727 p_owner_table_id => p_organization_id,
728 p_owner_table_name => 'HZ_PARTIES',
729 p_action_type => p_action_type,
730 x_party_usage_objs => x_organization_obj.party_usage_objs,
731 x_return_status => x_return_status,
732 x_msg_count => x_msg_count,
733 x_msg_data => x_msg_data);
734
735 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
736 RAISE FND_API.G_EXC_ERROR;
737 END IF;
738
739 HZ_EXTRACT_ORG_CONT_BO_PVT.get_org_contact_bos
740 (p_init_msg_list => fnd_api.g_false,
741 p_organization_id => p_organization_id,
742 p_action_type => p_action_type,
743 x_org_contact_objs => x_organization_obj.contact_objs,
744 x_return_status => x_return_status,
745 x_msg_count => x_msg_count,
746 x_msg_data => x_msg_data);
747
748 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
749 RAISE FND_API.G_EXC_ERROR;
750 END IF;
751
752
753 get_financial_report_bos(p_init_msg_list => fnd_api.g_false,
754 p_organization_id => p_organization_id,
755 p_action_type => p_action_type,
756 x_financial_report_objs => x_organization_obj.financial_report_objs,
757 x_return_status => x_return_status,
758 x_msg_count => x_msg_count,
759 x_msg_data => x_msg_data);
760
761
762 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
763 RAISE FND_API.G_EXC_ERROR;
764 END IF;
765
766 hz_extract_cont_point_bo_pvt.get_phone_bos
767 (p_init_msg_list => fnd_api.g_false,
768 p_phone_id => null,
769 p_parent_id => p_organization_id,
770 p_parent_table_name => 'HZ_PARTIES',
771 p_action_type => p_action_type,
772 x_phone_objs => x_organization_obj.phone_objs,
773 x_return_status => x_return_status,
774 x_msg_count => x_msg_count,
775 x_msg_data => x_msg_data);
776
777 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
778 RAISE FND_API.G_EXC_ERROR;
779 END IF;
780
781 hz_extract_cont_point_bo_pvt.get_telex_bos
782 (p_init_msg_list => fnd_api.g_false,
783 p_telex_id => null,
784 p_parent_id => p_organization_id,
785 p_parent_table_name => 'HZ_PARTIES',
786 p_action_type => p_action_type,
787 x_telex_objs => x_organization_obj.telex_objs,
788 x_return_status => x_return_status,
789 x_msg_count => x_msg_count,
790 x_msg_data => x_msg_data);
791
792 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
793 RAISE FND_API.G_EXC_ERROR;
794 END IF;
795
796
797 hz_extract_cont_point_bo_pvt.get_email_bos
798 (p_init_msg_list => fnd_api.g_false,
799 p_email_id => null,
800 p_parent_id => p_organization_id,
801 p_parent_table_name => 'HZ_PARTIES',
802 p_action_type => p_action_type,
803 x_email_objs => x_organization_obj.email_objs,
804 x_return_status => x_return_status,
805 x_msg_count => x_msg_count,
806 x_msg_data => x_msg_data);
807
808 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
809 RAISE FND_API.G_EXC_ERROR;
810 END IF;
811
812
813 hz_extract_cont_point_bo_pvt.get_web_bos
814 (p_init_msg_list => fnd_api.g_false,
815 p_web_id => null,
816 p_parent_id => p_organization_id,
817 p_parent_table_name => 'HZ_PARTIES',
818 p_action_type => p_action_type,
819 x_web_objs => x_organization_obj.web_objs,
820 x_return_status => x_return_status,
821 x_msg_count => x_msg_count,
822 x_msg_data => x_msg_data);
823
824 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
825 RAISE FND_API.G_EXC_ERROR;
826 END IF;
827
828
829
830 hz_extract_cont_point_bo_pvt.get_edi_bos
831 (p_init_msg_list => fnd_api.g_false,
832 p_edi_id => null,
833 p_parent_id => p_organization_id,
834 p_parent_table_name => 'HZ_PARTIES',
835 p_action_type => p_action_type,
836 x_edi_objs => x_organization_obj.edi_objs,
837 x_return_status => x_return_status,
838 x_msg_count => x_msg_count,
839 x_msg_data => x_msg_data);
840
841 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
842 RAISE FND_API.G_EXC_ERROR;
843 END IF;
844
845
846 hz_extract_cont_point_bo_pvt.get_eft_bos
847 (p_init_msg_list => fnd_api.g_false,
848 p_eft_id => null,
849 p_parent_id => p_organization_id,
850 p_parent_table_name => 'HZ_PARTIES',
851 p_action_type => p_action_type,
852 x_eft_objs => x_organization_obj.eft_objs,
853 x_return_status => x_return_status,
854 x_msg_count => x_msg_count,
855 x_msg_data => x_msg_data);
856
857 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
858 RAISE FND_API.G_EXC_ERROR;
859 END IF;
860
861
862 HZ_EXTRACT_RELATIONSHIP_BO_PVT.get_relationship_bos
863 (p_init_msg_list => fnd_api.g_false,
864 p_subject_id => p_organization_id,
865 p_action_type => p_action_type,
866 x_relationship_objs => x_organization_obj.relationship_objs,
867 x_return_status => x_return_status,
868 x_msg_count => x_msg_count,
869 x_msg_data => x_msg_data);
870
871 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
872 RAISE FND_API.G_EXC_ERROR;
873 END IF;
874
875
876 hz_extract_cont_point_bo_pvt.get_cont_pref_objs
877 (p_init_msg_list => fnd_api.g_false,
878 p_cont_level_table_id => p_organization_id,
879 p_cont_level_table => 'HZ_PARTIES',
880 p_contact_type => NULL,
881 p_action_type => p_action_type,
882 x_cont_pref_objs => x_organization_obj.contact_pref_objs,
883 x_return_status => x_return_status,
884 x_msg_count => x_msg_count,
885 x_msg_data => x_msg_data);
886
887 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
888 RAISE FND_API.G_EXC_ERROR;
889 END IF;
890
891
892 -- Debug info.
893 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
894 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
895 p_msg_data=>x_msg_data,
896 p_msg_type=>'WARNING',
897 p_msg_level=>fnd_log.level_exception);
898 END IF;
899
900 -- Debug info.
901 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
902 hz_utility_v2pub.debug(p_message=>'get_organization_bo (-)',
903 p_prefix=>l_debug_prefix,
904 p_msg_level=>fnd_log.level_procedure);
905 END IF;
906
907
908 EXCEPTION
909
910 WHEN fnd_api.g_exc_error THEN
911 x_return_status := fnd_api.g_ret_sts_error;
912
913 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
914 p_count => x_msg_count,
915 p_data => x_msg_data);
916
917 -- Debug info.
918 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
919 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
920 p_msg_data=>x_msg_data,
921 p_msg_type=>'ERROR',
922 p_msg_level=>fnd_log.level_error);
923 END IF;
924 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
925 hz_utility_v2pub.debug(p_message=>'get_organization_bo (-)',
926 p_prefix=>l_debug_prefix,
927 p_msg_level=>fnd_log.level_procedure);
928 END IF;
929 WHEN fnd_api.g_exc_unexpected_error THEN
930 x_return_status := fnd_api.g_ret_sts_unexp_error;
931
932 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
933 p_count => x_msg_count,
934 p_data => x_msg_data);
935
936 -- Debug info.
937 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
938 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
939 p_msg_data=>x_msg_data,
940 p_msg_type=>'UNEXPECTED ERROR',
941 p_msg_level=>fnd_log.level_error);
942 END IF;
943 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
944 hz_utility_v2pub.debug(p_message=>'get_organization_bo (-)',
945 p_prefix=>l_debug_prefix,
946 p_msg_level=>fnd_log.level_procedure);
947 END IF;
948 WHEN OTHERS THEN
949 x_return_status := fnd_api.g_ret_sts_unexp_error;
950
951 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
952 fnd_message.set_token('ERROR' ,SQLERRM);
953 fnd_msg_pub.add;
954
955 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
956 p_count => x_msg_count,
957 p_data => x_msg_data);
958
959 -- Debug info.
960 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
961 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
962 p_msg_data=>x_msg_data,
963 p_msg_type=>'SQL ERROR',
964 p_msg_level=>fnd_log.level_error);
965 END IF;
966 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
967 hz_utility_v2pub.debug(p_message=>'get_organization_bo (-)',
968 p_prefix=>l_debug_prefix,
969 p_msg_level=>fnd_log.level_procedure);
970 END IF;
971
972 end;
973
974
975
976
977
978 --------------------------------------
979 --
980 -- PROCEDURE get_organizations_created
981 --
982 -- DESCRIPTION
983 --The caller provides an identifier for the Organizations created business event and
984 --the procedure returns database objects of the type HZ_ORGANIZATION_BO for all of
985 --the Organization business objects from the business event.
986
987 --
988 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
989 --
990 -- ARGUMENTS
991 -- IN:
992 -- p_init_msg_list Initialize message stack if it is set to
993 -- p_event_id BES Event identifier.
994 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
995 -- OUT:
996 -- x_organization_objs One or more created logical organization.
997 -- x_return_status Return status after the call. The status can
998 -- be fnd_api.g_ret_sts_success (success),
999 -- fnd_api.g_ret_sts_error (error),
1000 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1001 -- x_msg_count Number of messages in message stack.
1002 -- x_msg_data Message text if x_msg_count is 1.
1003 --
1004 -- NOTES
1005 --
1006 -- MODIFICATION HISTORY
1007 --
1008 -- 06-JUN-2005 AWU Created.
1009 --
1010
1011
1012
1013 /*
1014 The Get Organizations Created procedure is a service to retrieve all of the Organization business objects
1015 whose creations have been captured by a logical business event. Each Organizations Created
1016 business event signifies that one or more Organization business objects have been created.
1017 The caller provides an identifier for the Organizations Created business event and the procedure
1018 returns all of the Organization business objects from the business event. For each business object
1019 creation captured in the business event, the procedure calls the generic Get operation:
1020 HZ_ORGANIZATION_BO_PVT.get_organization_bo
1021
1022 Gathering all of the returned business objects from those API calls, the procedure packages
1023 them in a table structure and returns them to the caller.
1024 */
1025
1026
1027 PROCEDURE get_organizations_created(
1028 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1029 p_event_id IN NUMBER,
1030 x_organization_objs OUT NOCOPY HZ_ORGANIZATION_BO_TBL,
1031 x_return_status OUT NOCOPY VARCHAR2,
1032 x_msg_count OUT NOCOPY NUMBER,
1033 x_msg_data OUT NOCOPY VARCHAR2
1034 ) is
1035 l_obj_root_ids HZ_EXTRACT_BO_UTIL_PVT.BO_ID_TBL;
1036 l_debug_prefix VARCHAR2(30) := '';
1037 begin
1038
1039 -- initialize API return status to success.
1040 x_return_status := FND_API.G_RET_STS_SUCCESS;
1041
1042 -- Initialize message list if p_init_msg_list is set to TRUE
1043 IF FND_API.to_Boolean(p_init_msg_list) THEN
1044 FND_MSG_PUB.initialize;
1045 END IF;
1046
1047
1048 -- Debug info.
1049 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1050 hz_utility_v2pub.debug(p_message=>'get_organization_created(+)',
1051 p_prefix=>l_debug_prefix,
1052 p_msg_level=>fnd_log.level_procedure);
1053 END IF;
1054
1055
1056 HZ_EXTRACT_BO_UTIL_PVT.get_bo_root_ids(
1057 p_init_msg_list => fnd_api.g_false,
1058 p_event_id => p_event_id,
1059 x_obj_root_ids => l_obj_root_ids,
1060 x_return_status => x_return_status,
1061 x_msg_count => x_msg_count,
1062 x_msg_data => x_msg_data);
1063
1064 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1065 RAISE FND_API.G_EXC_ERROR;
1066 END IF;
1067
1068
1069 x_organization_objs := HZ_ORGANIZATION_BO_TBL();
1070
1071 for i in 1..l_obj_root_ids.count loop
1072 x_organization_objs.extend;
1073 get_organization_bo(
1074 p_init_msg_list => fnd_api.g_false,
1075 p_organization_id => l_obj_root_ids(i),
1076 p_action_type => 'CREATED',
1077 x_organization_obj => x_organization_objs(i),
1078 x_return_status => x_return_status,
1079 x_msg_count => x_msg_count,
1080 x_msg_data => x_msg_data);
1081 end loop;
1082
1083 -- Debug info.
1084 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1085 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1086 p_msg_data=>x_msg_data,
1087 p_msg_type=>'WARNING',
1088 p_msg_level=>fnd_log.level_exception);
1089 END IF;
1090
1091 -- Debug info.
1092 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1093 hz_utility_v2pub.debug(p_message=>'get_organization_created (-)',
1094 p_prefix=>l_debug_prefix,
1095 p_msg_level=>fnd_log.level_procedure);
1096 END IF;
1097
1098
1099 EXCEPTION
1100
1101 WHEN fnd_api.g_exc_error THEN
1102 x_return_status := fnd_api.g_ret_sts_error;
1103
1104 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1105 p_count => x_msg_count,
1106 p_data => x_msg_data);
1107
1108 -- Debug info.
1109 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1110 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1111 p_msg_data=>x_msg_data,
1112 p_msg_type=>'ERROR',
1113 p_msg_level=>fnd_log.level_error);
1114 END IF;
1115 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1116 hz_utility_v2pub.debug(p_message=>'get_organization_created(-)',
1117 p_prefix=>l_debug_prefix,
1118 p_msg_level=>fnd_log.level_procedure);
1119 END IF;
1120 WHEN fnd_api.g_exc_unexpected_error THEN
1121 x_return_status := fnd_api.g_ret_sts_unexp_error;
1122
1123 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1124 p_count => x_msg_count,
1125 p_data => x_msg_data);
1126
1127 -- Debug info.
1128 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1129 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1130 p_msg_data=>x_msg_data,
1131 p_msg_type=>'UNEXPECTED ERROR',
1132 p_msg_level=>fnd_log.level_error);
1133 END IF;
1134 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1135 hz_utility_v2pub.debug(p_message=>'get_organization_created(-)',
1136 p_prefix=>l_debug_prefix,
1137 p_msg_level=>fnd_log.level_procedure);
1138 END IF;
1139 WHEN OTHERS THEN
1140 x_return_status := fnd_api.g_ret_sts_unexp_error;
1141
1142 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1143 fnd_message.set_token('ERROR' ,SQLERRM);
1144 fnd_msg_pub.add;
1145
1146 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1147 p_count => x_msg_count,
1148 p_data => x_msg_data);
1149
1150 -- Debug info.
1151 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1152 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1153 p_msg_data=>x_msg_data,
1154 p_msg_type=>'SQL ERROR',
1155 p_msg_level=>fnd_log.level_error);
1156 END IF;
1157 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1158 hz_utility_v2pub.debug(p_message=>'get_organization_created(-)',
1159 p_prefix=>l_debug_prefix,
1160 p_msg_level=>fnd_log.level_procedure);
1161 END IF;
1162
1163 end;
1164
1165
1166
1167
1168
1169 --------------------------------------
1170 --
1171 -- PROCEDURE get_organizations_updated
1172 --
1173 -- DESCRIPTION
1174 --The caller provides an identifier for the Organizations update business event and
1175 --the procedure returns database objects of the type HZ_ORGANIZATION_BO for all of
1176 --the Organization business objects from the business event.
1177
1178 --
1179 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1180 --
1181 -- ARGUMENTS
1182 -- IN:
1183 -- p_init_msg_list Initialize message stack if it is set to
1184 -- p_event_id BES Event identifier.
1185 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
1186 -- OUT:
1187 -- x_organization_objs One or more created logical organization.
1188 -- x_return_status Return status after the call. The status can
1189 -- be fnd_api.g_ret_sts_success (success),
1190 -- fnd_api.g_ret_sts_error (error),
1191 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1192 -- x_msg_count Number of messages in message stack.
1193 -- x_msg_data Message text if x_msg_count is 1.
1194 --
1195 -- NOTES
1196 --
1197 -- MODIFICATION HISTORY
1198 --
1199 -- 06-JUN-2005 AWU Created.
1200 --
1201
1202
1203
1204 /*
1205 The Get Organizations Updated procedure is a service to retrieve all of the Organization business objects whose updates
1206 have been captured by the logical business event. Each Organizations Updated business event signifies that one or more
1207 Organization business objects have been updated.
1208 The caller provides an identifier for the Organizations Update business event and the procedure returns database objects
1209 of the type HZ_ORGANIZATION_BO for all of the Organization business objects from the business event.
1210 Gathering all of the returned database objects from those API calls, the procedure packages them in a table structure and
1211 returns them to the caller.
1212 */
1213
1214 PROCEDURE get_organizations_updated(
1215 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1216 p_event_id IN NUMBER,
1217 x_organization_objs OUT NOCOPY HZ_ORGANIZATION_BO_TBL,
1218 x_return_status OUT NOCOPY VARCHAR2,
1219 x_msg_count OUT NOCOPY NUMBER,
1220 x_msg_data OUT NOCOPY VARCHAR2
1221 ) is
1222
1223 l_obj_root_ids HZ_EXTRACT_BO_UTIL_PVT.BO_ID_TBL;
1224 l_debug_prefix VARCHAR2(30) := '';
1225
1226 begin
1227
1228 -- initialize API return status to success.
1229 x_return_status := FND_API.G_RET_STS_SUCCESS;
1230
1231 -- Initialize message list if p_init_msg_list is set to TRUE
1232 IF FND_API.to_Boolean(p_init_msg_list) THEN
1233 FND_MSG_PUB.initialize;
1234 END IF;
1235
1236
1237 -- Debug info.
1238 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1239 hz_utility_v2pub.debug(p_message=>'get_organizations_updated(+)',
1240 p_prefix=>l_debug_prefix,
1241 p_msg_level=>fnd_log.level_procedure);
1242 END IF;
1243
1244
1245 HZ_EXTRACT_BO_UTIL_PVT.get_bo_root_ids(
1246 p_init_msg_list => fnd_api.g_false,
1247 p_event_id => p_event_id,
1248 x_obj_root_ids => l_obj_root_ids,
1249 x_return_status => x_return_status,
1250 x_msg_count => x_msg_count,
1251 x_msg_data => x_msg_data);
1252
1253 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1254 RAISE FND_API.G_EXC_ERROR;
1255 END IF;
1256
1257 -- call event API get_organization_updated for each id.
1258
1259 x_organization_objs := HZ_ORGANIZATION_BO_TBL();
1260
1261 for i in 1..l_obj_root_ids.count loop
1262 x_organization_objs.extend;
1263 get_organization_updated(
1264 p_init_msg_list => fnd_api.g_false,
1265 p_event_id => p_event_id,
1266 p_organization_id => l_obj_root_ids(i),
1267 x_organization_obj => x_organization_objs(i),
1268 x_return_status => x_return_status,
1269 x_msg_count => x_msg_count,
1270 x_msg_data => x_msg_data);
1271
1272 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1273 RAISE FND_API.G_EXC_ERROR;
1274 END IF;
1275 end loop;
1276
1277
1278
1279 -- Debug info.
1280 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1281 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1282 p_msg_data=>x_msg_data,
1283 p_msg_type=>'WARNING',
1284 p_msg_level=>fnd_log.level_exception);
1285 END IF;
1286
1287 -- Debug info.
1288 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1289 hz_utility_v2pub.debug(p_message=>'get_organizations_updated (-)',
1290 p_prefix=>l_debug_prefix,
1291 p_msg_level=>fnd_log.level_procedure);
1292 END IF;
1293
1294
1295 EXCEPTION
1296
1297 WHEN fnd_api.g_exc_error THEN
1298 x_return_status := fnd_api.g_ret_sts_error;
1299
1300 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1301 p_count => x_msg_count,
1302 p_data => x_msg_data);
1303
1304 -- Debug info.
1305 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1306 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1307 p_msg_data=>x_msg_data,
1308 p_msg_type=>'ERROR',
1309 p_msg_level=>fnd_log.level_error);
1310 END IF;
1311 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1312 hz_utility_v2pub.debug(p_message=>'get_organizations_updated(-)',
1313 p_prefix=>l_debug_prefix,
1314 p_msg_level=>fnd_log.level_procedure);
1315 END IF;
1316 WHEN fnd_api.g_exc_unexpected_error THEN
1317 x_return_status := fnd_api.g_ret_sts_unexp_error;
1318
1319 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1320 p_count => x_msg_count,
1321 p_data => x_msg_data);
1322
1323 -- Debug info.
1324 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1325 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1326 p_msg_data=>x_msg_data,
1327 p_msg_type=>'UNEXPECTED ERROR',
1328 p_msg_level=>fnd_log.level_error);
1329 END IF;
1330 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1331 hz_utility_v2pub.debug(p_message=>'get_organizations_updated(-)',
1332 p_prefix=>l_debug_prefix,
1333 p_msg_level=>fnd_log.level_procedure);
1334 END IF;
1335 WHEN OTHERS THEN
1336 x_return_status := fnd_api.g_ret_sts_unexp_error;
1337
1338 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1339 fnd_message.set_token('ERROR' ,SQLERRM);
1340 fnd_msg_pub.add;
1341
1342 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1343 p_count => x_msg_count,
1344 p_data => x_msg_data);
1345
1346 -- Debug info.
1347 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1348 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1349 p_msg_data=>x_msg_data,
1350 p_msg_type=>'SQL ERROR',
1351 p_msg_level=>fnd_log.level_error);
1352 END IF;
1353 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1354 hz_utility_v2pub.debug(p_message=>'get_organizations_updated(-)',
1355 p_prefix=>l_debug_prefix,
1356 p_msg_level=>fnd_log.level_procedure);
1357 END IF;
1358
1359 end;
1360
1361 procedure set_orgcnt_site_bo_action_type(p_node_path IN VARCHAR2,
1362 p_child_id IN NUMBER,
1363 p_action_type IN VARCHAR2,
1364 p_child_entity_name IN VARCHAR2,
1365 p_child_bo_code IN VARCHAR2,
1366 px_party_site_obj IN OUT NOCOPY HZ_PARTY_SITE_BO) is
1367
1368 l_child_upd_flag varchar2(1);
1369
1370 begin
1371
1372 if p_child_entity_name = 'HZ_PARTY_SITES'
1373 then
1374 if px_party_site_obj.party_site_id = p_child_id
1375 then
1376 px_party_site_obj.action_type := p_action_type;
1377 l_child_upd_flag := 'N';
1378 if p_action_type = 'CREATED'
1379 then
1380 px_party_site_obj.location_obj.action_type :='CREATED';
1381 l_child_upd_flag := 'Y';
1382 end if;
1383
1384
1385 end if;
1386 end if;
1387
1388
1389 if p_child_entity_name = 'HZ_LOCATIONS'
1390 then
1391 if px_party_site_obj.location_obj.location_id = p_child_id and p_action_type = 'UPDATED'
1392 then px_party_site_obj.location_obj.action_type := p_action_type;
1393 l_child_upd_flag := 'Y';
1394 end if;
1395 end if;
1396
1397 if p_child_entity_name = 'HZ_CONTACT_POINTS'
1398 then
1399 if p_child_bo_code = 'EMAIL'
1400 then
1401 for j in 1..px_party_site_obj.EMAIL_OBJS.COUNT
1402 loop
1403 if px_party_site_obj.EMAIL_OBJS(j).email_id = p_child_id
1404 then px_party_site_obj.EMAIL_OBJS(j).action_type := p_action_type;
1405 l_child_upd_flag := 'Y';
1406 end if;
1407 end loop;
1408 elsif p_child_bo_code = 'PHONE'
1409 then
1410 for j in 1..px_party_site_obj.PHONE_OBJS.COUNT
1411 loop
1412 if px_party_site_obj.PHONE_OBJS(j).phone_id = p_child_id
1413 then px_party_site_obj.PHONE_OBJS(j).action_type := p_action_type;
1414 l_child_upd_flag := 'Y';
1415 end if;
1416 end loop;
1417 elsif p_child_bo_code = 'WEB'
1418 then
1419 for j in 1..px_party_site_obj.WEB_OBJS.COUNT
1420 loop
1421 if px_party_site_obj.WEB_OBJS(j).web_id = p_child_id
1422 then px_party_site_obj.WEB_OBJS(j).action_type := p_action_type;
1423 l_child_upd_flag := 'Y';
1424 end if;
1425 end loop;
1426 elsif p_child_bo_code = 'TLX'
1427 then
1428 for j in 1..px_party_site_obj.TELEX_OBJS.COUNT
1429 loop
1430 if px_party_site_obj.TELEX_OBJS(j).telex_id = p_child_id
1431 then px_party_site_obj.TELEX_OBJS(j).action_type := p_action_type;
1432 l_child_upd_flag := 'Y';
1433 end if;
1434 end loop;
1435 end if;
1436 end if; --'HZ_CONTACT_POINTS'
1437 if p_child_entity_name = 'HZ_CONTACT_PREFERENCES' then
1438 for j in 1..px_party_site_obj.CONTACT_PREF_OBJS.count
1439 loop
1440 if px_party_site_obj.CONTACT_PREF_OBJS(j).contact_preference_id = p_child_id
1441 then px_party_site_obj.CONTACT_PREF_OBJS(j).action_type := p_action_type;
1442 l_child_upd_flag := 'Y';
1443 end if;
1444 end loop;
1445 end if;
1446
1447 if px_party_site_obj.action_type = 'UNCHANGED' and l_child_upd_flag = 'Y'
1448 then
1449 px_party_site_obj.action_type := 'CHILD_UPDATED';
1450 end if;
1451 end set_orgcnt_site_bo_action_type;
1452
1453
1454 -- Based on BOT, for updated branch, set action_type = 'UPDATED'/'CREATED'
1455
1456 procedure set_org_bo_action_type(p_event_id IN NUMBER,
1457 p_root_id IN NUMBER,
1458 px_org_obj IN OUT NOCOPY HZ_ORGANIZATION_BO,
1459 x_return_status OUT NOCOPY VARCHAR2) is
1460 cursor c1 is
1461
1462 SELECT
1463 sys_connect_by_path(CHILD_BO_CODE, '/') node_path,
1464 CHILD_OPERATION_FLAG,
1465 CHILD_BO_CODE,
1466 CHILD_ENTITY_NAME,
1467 CHILD_ID,
1468 populated_flag
1469 FROM HZ_BUS_OBJ_TRACKING
1470 where event_id = p_event_id
1471 START WITH child_id = p_root_id
1472 and child_entity_name = 'HZ_PARTIES'
1473 AND PARENT_BO_CODE IS NULL
1474 AND CHILD_BO_CODE = 'ORG' --(or ORG, PERSON_CUST, ORG_CUST).
1475 and event_id = p_event_id
1476 CONNECT BY PARENT_ENTITY_NAME = PRIOR CHILD_ENTITY_NAME
1477 AND PARENT_ID = PRIOR CHILD_ID
1478 AND parent_bo_code = PRIOR child_bo_code
1479 and event_id = PRIOR event_id;
1480
1481
1482 cursor c2 is
1483 select CHILD_ENTITY_NAME
1484 FROM HZ_BUS_OBJ_TRACKING
1485 where event_id = p_event_id
1486 and populated_flag = 'N'
1487 and CHILD_ENTITY_NAME = 'HZ_ORGANIZATION_PROFILES';
1488
1489 L_CHILD_OPERATION_FLAG VARCHAR2(1);
1490 L_CHILD_BO_CODE VARCHAR2(30);
1491 L_CHILD_ENTITY_NAME VARCHAR2(30);
1492 L_CHILD_ID NUMBER;
1493 l_action_type varchar2(30);
1494 l_node_path varchar2(2000);
1495 l_populated_flag varchar2(1);
1496 l_child_upd_flag varchar2(1);
1497
1498 begin
1499 -- initialize API return status to success.
1500 x_return_status := FND_API.G_RET_STS_SUCCESS;
1501
1502 -- Solve populate_flag is not in sync in hz_parties and hz_org_profiles.
1503 -- c1 can't return profile entity.
1504
1505 open c2;
1506 fetch c2 into L_CHILD_ENTITY_NAME;
1507 close c2;
1508
1509 if l_child_entity_name = 'HZ_ORGANIZATION_PROFILES'
1510 then
1511 px_org_obj.action_type := 'UPDATED';
1512 else px_org_obj.action_type := 'CHILD_UPDATED';
1513 end if;
1514
1515 open c1;
1516 loop
1517 fetch c1 into L_NODE_PATH, L_CHILD_OPERATION_FLAG,L_CHILD_BO_CODE,
1518 L_CHILD_ENTITY_NAME, L_CHILD_ID, l_populated_flag;
1519 exit when c1%NOTFOUND;
1520 if l_child_entity_name = 'HZ_ORGANIZATION_PROFILES'
1521 then
1522 px_org_obj.action_type := 'UPDATED';
1523 end if;
1524 if l_populated_flag = 'N'
1525 then
1526 if L_CHILD_OPERATION_FLAG = 'I'
1527 then l_action_type := 'CREATED';
1528 elsif L_CHILD_OPERATION_FLAG = 'U'
1529 then l_action_type := 'UPDATED';
1530 end if;
1531
1532
1533 -- check first level entity objects
1534
1535 if l_child_entity_name = 'HZ_FINANCIAL_REPORTS' then
1536 for i in 1..PX_ORG_OBJ.FINANCIAL_REPORT_OBJS.COUNT
1537 loop
1538 if PX_ORG_OBJ.FINANCIAL_REPORT_OBJS(i).FINANCIAL_REPORT_ID = l_child_id
1539 then PX_ORG_OBJ.FINANCIAL_REPORT_OBJS(i).action_type := l_action_type;
1540 l_child_upd_flag := 'Y';
1541 end if;
1542 end loop;
1543 elsif l_child_entity_name = 'HZ_PARTY_USG_ASSIGNMENTS' then
1544 for i in 1..PX_ORG_OBJ.PARTY_USAGE_OBJS .COUNT
1545 loop
1546 if PX_ORG_OBJ.PARTY_USAGE_OBJS(i).party_usg_assignment_id = l_child_id
1547 then PX_ORG_OBJ.PARTY_USAGE_OBJS(i).action_type := l_action_type;
1548 l_child_upd_flag := 'Y';
1549 end if;
1550 end loop;
1551
1552 elsif l_child_entity_name = 'HZ_RELATIONSHIPS' then
1553 for i in 1..PX_ORG_OBJ.RELATIONSHIP_OBJS .COUNT
1554 loop
1555 if PX_ORG_OBJ.RELATIONSHIP_OBJS(i).relationship_id = l_child_id
1556 then PX_ORG_OBJ.RELATIONSHIP_OBJS(i).action_type := l_action_type;
1557 l_child_upd_flag := 'Y';
1558 end if;
1559 end loop;
1560 elsif l_child_entity_name = 'HZ_CERTIFICATIONS' then
1561 for i in 1..PX_ORG_OBJ.CERTIFICATION_OBJS .COUNT
1562 loop
1563 if PX_ORG_OBJ.CERTIFICATION_OBJS(i).certification_id = l_child_id
1564 then PX_ORG_OBJ.CERTIFICATION_OBJS(i).action_type := l_action_type;
1565 l_child_upd_flag := 'Y';
1566 end if;
1567 end loop;
1568 elsif l_child_entity_name = 'HZ_CREDIT_RATINGS' then
1569 for i in 1..PX_ORG_OBJ.CREDIT_RATING_OBJS.COUNT
1570 loop
1571 if PX_ORG_OBJ.CREDIT_RATING_OBJS(i).CREDIT_RATING_ID = l_child_id
1572 then PX_ORG_OBJ.CREDIT_RATING_OBJS(i).action_type := l_action_type;
1573 l_child_upd_flag := 'Y';
1574 end if;
1575 end loop;
1576
1577 elsif l_child_entity_name = 'HZ_CODE_ASSIGNMENTS' then
1578 for i in 1..PX_ORG_OBJ.CLASS_OBJS .COUNT
1579 loop
1580 if PX_ORG_OBJ.CLASS_OBJS(i).code_assignment_id = l_child_id
1581 then PX_ORG_OBJ.CLASS_OBJS(i).action_type := l_action_type;
1582 l_child_upd_flag := 'Y';
1583 end if;
1584 end loop;
1585 elsif l_child_entity_name = 'HZ_FINANCIAL_PROFILE' then
1586 for i in 1..PX_ORG_OBJ.FINANCIAL_PROF_OBJS .COUNT
1587 loop
1588 if PX_ORG_OBJ.FINANCIAL_PROF_OBJS(i).financial_profile_id = l_child_id
1589 then PX_ORG_OBJ.FINANCIAL_PROF_OBJS(i).action_type := l_action_type;
1590 l_child_upd_flag := 'Y';
1591 end if;
1592 end loop;
1593 elsif l_child_entity_name = 'HZ_PARTY_PREFERENCES' then
1594 for i in 1..PX_ORG_OBJ.PREFERENCE_OBJS .COUNT
1595 loop
1596 if PX_ORG_OBJ.PREFERENCE_OBJS(i).party_preference_id = l_child_id
1597 then PX_ORG_OBJ.PREFERENCE_OBJS(i).action_type := l_action_type;
1598 l_child_upd_flag := 'Y';
1599 end if;
1600 end loop;
1601
1602 elsif l_child_entity_name = 'HZ_ORG_PROFILES_EXT_VL' then
1603 for i in 1..PX_ORG_OBJ.EXT_ATTRIBUTES_OBJS .COUNT
1604 loop
1605 if PX_ORG_OBJ.EXT_ATTRIBUTES_OBJS(i).extension_id = l_child_id
1606 then PX_ORG_OBJ.EXT_ATTRIBUTES_OBJS(i).action_type := l_action_type;
1607 l_child_upd_flag := 'Y';
1608 end if;
1609 end loop;
1610
1611 -- contact preference might have multiple parents, but one id will not belong to
1612 -- more than one parents
1613 elsif l_child_entity_name = 'HZ_CONTACT_PREFERENCES' then
1614 l_child_upd_flag := 'Y';
1615
1616 for i in 1..PX_ORG_OBJ.CONTACT_PREF_OBJS .COUNT
1617 loop
1618 if PX_ORG_OBJ.CONTACT_PREF_OBJS(i).contact_preference_id = l_child_id
1619 then PX_ORG_OBJ.CONTACT_PREF_OBJS(i).action_type := l_action_type;
1620 end if;
1621 end loop;
1622 if instr(l_node_path, 'ORG/PHONE') > 0 then
1623 for i in 1..PX_ORG_OBJ.PHONE_OBJS.COUNT
1624 loop
1625 for j in 1..PX_ORG_OBJ.PHONE_OBJS(i).CONTACT_PREF_OBJS.count
1626 loop
1627 if px_org_obj.phone_objs(i).CONTACT_PREF_OBJS(j).contact_preference_id = l_child_id
1628 then px_org_obj.phone_objs(i).CONTACT_PREF_OBJS(j).action_type := l_action_type;
1629 end if;
1630 end loop;
1631 end loop;
1632 end if;
1633 if instr(l_node_path, 'ORG/EMAIL') > 0 then
1634 for i in 1..PX_ORG_OBJ.EMAIL_OBJS.COUNT
1635 loop
1636 for j in 1..PX_ORG_OBJ.EMAIL_OBJS(i).CONTACT_PREF_OBJS.count
1637 loop
1638 if px_org_obj.email_objs(i).CONTACT_PREF_OBJS(j).contact_preference_id = l_child_id
1639 then px_org_obj.email_objs(i).CONTACT_PREF_OBJS(j).action_type := l_action_type;
1640
1641 end if;
1642 end loop;
1643 end loop;
1644 end if;
1645 if instr(l_node_path, 'ORG/WEB') > 0 then
1646 for i in 1..PX_ORG_OBJ.WEB_OBJS.COUNT
1647 loop
1648 for j in 1..PX_ORG_OBJ.WEB_OBJS(i).CONTACT_PREF_OBJS.count
1649 loop
1650 if px_org_obj.web_objs(i).CONTACT_PREF_OBJS(j).contact_preference_id = l_child_id
1651 then px_org_obj.web_objs(i).CONTACT_PREF_OBJS(j).action_type := l_action_type;
1652
1653 end if;
1654 end loop;
1655 end loop;
1656 end if;
1657
1658 if instr(l_node_path, 'ORG/EDI') > 0 then
1659
1660 for i in 1..PX_ORG_OBJ.EDI_OBJS.COUNT
1661 loop
1662 for j in 1..PX_ORG_OBJ.EDI_OBJS(i).CONTACT_PREF_OBJS.count
1663 loop
1664 if px_org_obj.edi_objs(i).CONTACT_PREF_OBJS(j).contact_preference_id = l_child_id
1665 then px_org_obj.edi_objs(i).CONTACT_PREF_OBJS(j).action_type := l_action_type;
1666
1667 end if;
1668 end loop;
1669 end loop;
1670 end if;
1671
1672 if instr(l_node_path, 'ORG/TLX') > 0 then
1673 for i in 1..PX_ORG_OBJ.TELEX_OBJS.COUNT
1674 loop
1675 for j in 1..PX_ORG_OBJ.TELEX_OBJS(i).CONTACT_PREF_OBJS.count
1676 loop
1677 if px_org_obj.telex_objs(i).CONTACT_PREF_OBJS(j).contact_preference_id = l_child_id
1678 then px_org_obj.telex_objs(i).CONTACT_PREF_OBJS(j).action_type := l_action_type;
1679
1680 end if;
1681 end loop;
1682 end loop;
1683 end if;
1684 if instr(l_node_path, 'ORG/EFT') > 0 then
1685 for i in 1..PX_ORG_OBJ.EFT_OBJS.COUNT
1686 loop
1687 for j in 1..PX_ORG_OBJ.EFT_OBJS(i).CONTACT_PREF_OBJS.count
1688 loop
1689 if px_org_obj.eft_objs(i).CONTACT_PREF_OBJS(j).contact_preference_id = l_child_id
1690 then px_org_obj.eft_objs(i).CONTACT_PREF_OBJS(j).action_type := l_action_type;
1691
1692 end if;
1693 end loop;
1694 end loop;
1695 end if;
1696
1697
1698 elsif l_child_entity_name = 'HZ_CONTACT_POINTS' then
1699 if l_child_bo_code = 'EMAIL'
1700 then
1701 for i in 1..PX_ORG_OBJ.EMAIL_OBJS.COUNT
1702 loop
1703 if PX_ORG_OBJ.EMAIL_OBJS(i).email_id = l_child_id
1704 then PX_ORG_OBJ.EMAIL_OBJS(i).action_type := l_action_type;
1705 end if;
1706 end loop;
1707 l_child_upd_flag := 'Y';
1708 elsif l_child_bo_code = 'PHONE'
1709 then
1710 for i in 1..PX_ORG_OBJ.PHONE_OBJS.COUNT
1711 loop
1712 if PX_ORG_OBJ.PHONE_OBJS(i).phone_id = l_child_id
1713 then PX_ORG_OBJ.PHONE_OBJS(i).action_type := l_action_type;
1714 end if;
1715 end loop;
1716 l_child_upd_flag := 'Y';
1717 elsif l_child_bo_code = 'WEB'
1718 then
1719 for i in 1..PX_ORG_OBJ.WEB_OBJS.COUNT
1720 loop
1721 if PX_ORG_OBJ.WEB_OBJS(i).web_id = l_child_id
1722 then PX_ORG_OBJ.WEB_OBJS(i).action_type := l_action_type;
1723
1724 end if;
1725 end loop;
1726 l_child_upd_flag := 'Y';
1727 elsif l_child_bo_code = 'EDI'
1728 then
1729 for i in 1..PX_ORG_OBJ.EDI_OBJS.COUNT
1730 loop
1731 if PX_ORG_OBJ.EDI_OBJS(i).edi_id = l_child_id
1732 then PX_ORG_OBJ.EDI_OBJS(i).action_type := l_action_type;
1733 l_child_upd_flag := 'Y';
1734
1735 end if;
1736 end loop;
1737
1738 elsif l_child_bo_code = 'TLX'
1739 then
1740 for i in 1..PX_ORG_OBJ.TELEX_OBJS.COUNT
1741 loop
1742 if PX_ORG_OBJ.TELEX_OBJS(i).telex_id = l_child_id
1743 then PX_ORG_OBJ.TELEX_OBJS(i).action_type := l_action_type;
1744 l_child_upd_flag := 'Y';
1745
1746 end if;
1747 end loop;
1748
1749 elsif l_child_bo_code = 'EFT'
1750 then
1751 for i in 1..PX_ORG_OBJ.EFT_OBJS.COUNT
1752 loop
1753 if PX_ORG_OBJ.EFT_OBJS(i).eft_id = l_child_id
1754 then PX_ORG_OBJ.EFT_OBJS(i).action_type := l_action_type;
1755 l_child_upd_flag := 'Y';
1756 end if;
1757 end loop;
1758
1759 end if;
1760 end if; -- if l_child_entity_name = 'HZ_CONTACT_PREFERENCES'
1761
1762 -- check party site object
1763
1764 if instr(l_node_path, 'ORG/PARTY_SITE') > 0
1765 then
1766
1767
1768 for i in 1..PX_ORG_OBJ.PARTY_SITE_OBJS.COUNT
1769 loop
1770 -- check root level
1771 if l_child_entity_name = 'HZ_PARTY_SITES'
1772 then
1773 if px_org_obj.party_site_objs(i).party_site_id = l_child_id
1774 then
1775 px_org_obj.party_site_objs(i).action_type := l_action_type;
1776 l_child_upd_flag := 'N';
1777 if l_action_type = 'CREATED'
1778 then
1779 px_org_obj.party_site_objs(i).location_obj.action_type :='CREATED';
1780 l_child_upd_flag := 'Y';
1781 end if;
1782
1783 end if;
1784
1785 end if;
1786
1787 -- check second level
1788
1789 if l_child_entity_name = 'HZ_LOCATIONS'
1790 then
1791 if px_org_obj.party_site_objs(i).location_obj.location_id = l_child_id and l_action_type = 'UPDATED'
1792 then px_org_obj.party_site_objs(i).location_obj.action_type := l_action_type; l_child_upd_flag := 'Y';
1793 end if;
1794 end if;
1795 if l_child_entity_name = 'HZ_CONTACT_POINTS'
1796 then
1797 if l_child_bo_code = 'EMAIL'
1798 then
1799 for j in 1..px_org_obj.party_site_objs(i).EMAIL_OBJS.COUNT
1800 loop
1801 if px_org_obj.party_site_objs(i).EMAIL_OBJS(j).email_id = l_child_id
1802 then px_org_obj.party_site_objs(i).EMAIL_OBJS(j).action_type := l_action_type;
1803 l_child_upd_flag := 'Y';
1804 end if;
1805 end loop;
1806 elsif l_child_bo_code = 'PHONE'
1807 then
1808 for j in 1..px_org_obj.party_site_objs(i).PHONE_OBJS.COUNT
1809 loop
1810 if px_org_obj.party_site_objs(i).PHONE_OBJS(j).phone_id = l_child_id
1811 then px_org_obj.party_site_objs(i).PHONE_OBJS(j).action_type := l_action_type;
1812 l_child_upd_flag := 'Y';
1813 end if;
1814 end loop;
1815 elsif l_child_bo_code = 'WEB'
1816 then
1817 for j in 1..px_org_obj.party_site_objs(i).WEB_OBJS.COUNT
1818 loop
1819 if px_org_obj.party_site_objs(i).WEB_OBJS(j).web_id = l_child_id
1820 then px_org_obj.party_site_objs(i).WEB_OBJS(j).action_type := l_action_type;
1821 l_child_upd_flag := 'Y';
1822 end if;
1823 end loop;
1824 elsif l_child_bo_code = 'TLX'
1825 then
1826 for j in 1..px_org_obj.party_site_objs(i).TELEX_OBJS.COUNT
1827 loop
1828 if px_org_obj.party_site_objs(i).TELEX_OBJS(j).telex_id = l_child_id
1829 then px_org_obj.party_site_objs(i).TELEX_OBJS(j).action_type := l_action_type;
1830 l_child_upd_flag := 'Y';
1831 end if;
1832 end loop;
1833 end if;
1834 end if; --'HZ_CONTACT_POINTS'
1835 if l_child_entity_name = 'HZ_CONTACT_PREFERENCES' then
1836 for j in 1..PX_ORG_OBJ.PARTY_SITE_OBJS(i).CONTACT_PREF_OBJS.count
1837 loop
1838 if px_org_obj.party_site_objs(i).CONTACT_PREF_OBJS(j).contact_preference_id = l_child_id
1839 then px_org_obj.party_site_objs(i).CONTACT_PREF_OBJS(j).action_type := l_action_type;
1840 l_child_upd_flag := 'Y';
1841 end if;
1842 end loop;
1843 end if;
1844 if l_child_entity_name = 'HZ_PARTY_SITE_USES' then
1845 for j in 1..PX_ORG_OBJ.PARTY_SITE_OBJS(i).PARTY_SITE_USE_OBJS.count
1846 loop
1847 if px_org_obj.party_site_objs(i).PARTY_SITE_USE_OBJS(j).party_site_use_id = l_child_id
1848 then px_org_obj.party_site_objs(i).PARTY_SITE_USE_OBJS(j).action_type := l_action_type;
1849
1850 l_child_upd_flag := 'Y';
1851 end if;
1852 end loop;
1853 end if;
1854 if px_org_obj.party_site_objs(i).action_type = 'UNCHANGED'
1855 and l_child_upd_flag = 'Y'
1856 then
1857 px_org_obj.party_site_objs(i).action_type := 'CHILD_UPDATED';
1858 end if;
1859
1860 end loop; -- party_site_obj
1861 end if; -- party_site_obj
1862
1863 -- check fin report object
1864
1865 if instr(l_node_path, 'ORG/FIN_REPORT') > 0
1866 then
1867 for i in 1..PX_ORG_OBJ.FINANCIAL_REPORT_OBJS.COUNT
1868 loop
1869 -- check root level
1870 if l_child_entity_name = 'HZ_FINANCIAL_REPORTS'
1871 then
1872 if px_org_obj.FINANCIAL_REPORT_OBJS(i).FINANCIAL_REPORT_ID = l_child_id
1873 then
1874 px_org_obj.FINANCIAL_REPORT_OBJS(i).action_type := l_action_type;
1875 l_child_upd_flag := 'N';
1876 end if;
1877 end if;
1878
1879 -- check second level
1880
1881 if l_child_entity_name = 'HZ_FINANCIAL_NUMBERS'
1882 then
1883 for j in 1..PX_ORG_OBJ.FINANCIAL_REPORT_OBJS(i).FINANCIAL_NUMBER_OBJS.COUNT
1884 loop
1885 if PX_ORG_OBJ.FINANCIAL_REPORT_OBJS(i).FINANCIAL_NUMBER_OBJS(j).FINANCIAL_NUMBER_ID = l_child_id
1886 then PX_ORG_OBJ.FINANCIAL_REPORT_OBJS(i).FINANCIAL_NUMBER_OBJS(j).action_type := l_action_type;
1887 l_child_upd_flag := 'Y';
1888 end if;
1889 end loop;
1890 end if;
1891 if PX_ORG_OBJ.FINANCIAL_REPORT_OBJS(i).action_type = 'UNCHANGED' and l_child_upd_flag = 'Y'
1892 then
1893 PX_ORG_OBJ.FINANCIAL_REPORT_OBJS(i).action_type := 'CHILD_UPDATED';
1894 end if;
1895 end loop;
1896 end if; -- fin report
1897
1898 -- check org contact object
1899
1900 if instr(l_node_path, 'ORG/ORG_CONTACT') > 0
1901 then
1902 for i in 1..PX_ORG_OBJ.CONTACT_OBJS.COUNT
1903 loop
1904 -- check root level
1905 if l_child_entity_name = 'HZ_ORG_CONTACTS'
1906 then
1907 if px_org_obj.contact_objs(i).org_contact_id = l_child_id
1908 then
1909 px_org_obj.contact_objs(i).action_type := l_action_type;
1910 l_child_upd_flag := 'N';
1911 end if;
1912 end if;
1913
1914 -- check second level
1915
1916 if l_child_entity_name = 'HZ_PERSON_PROFILES'
1917 then
1918 if px_org_obj.contact_objs(i).PERSON_PROFILE_OBJ.person_id = l_child_id
1919 then px_org_obj.contact_objs(i).PERSON_PROFILE_OBJ.action_type := l_action_type;
1920 l_child_upd_flag := 'Y';
1921 end if;
1922 end if;
1923 if l_child_entity_name = 'HZ_CONTACT_POINTS'
1924 then
1925 if l_child_bo_code = 'EMAIL'
1926 then
1927 for j in 1..PX_ORG_OBJ.CONTACT_OBJS(i).EMAIL_OBJS.COUNT
1928 loop
1929 if PX_ORG_OBJ.CONTACT_OBJS(i).EMAIL_OBJS(j).email_id = l_child_id
1930 then PX_ORG_OBJ.CONTACT_OBJS(i).EMAIL_OBJS(j).action_type := l_action_type;
1931 l_child_upd_flag := 'Y';
1932 end if;
1933 end loop;
1934 elsif l_child_bo_code = 'PHONE'
1935 then
1936 for j in 1..PX_ORG_OBJ.CONTACT_OBJS(i).PHONE_OBJS.COUNT
1937 loop
1938 if PX_ORG_OBJ.CONTACT_OBJS(i).PHONE_OBJS(j).phone_id = l_child_id
1939 then PX_ORG_OBJ.CONTACT_OBJS(i).PHONE_OBJS(j).action_type := l_action_type;
1940 l_child_upd_flag := 'Y';
1941 end if;
1942 end loop;
1943 elsif l_child_bo_code = 'WEB'
1944 then
1945 for j in 1..PX_ORG_OBJ.CONTACT_OBJS(i).WEB_OBJS.COUNT
1946 loop
1947 if PX_ORG_OBJ.CONTACT_OBJS(i).WEB_OBJS(j).web_id = l_child_id
1948 then PX_ORG_OBJ.CONTACT_OBJS(i).WEB_OBJS(j).action_type := l_action_type;
1949 l_child_upd_flag := 'Y';
1950 end if;
1951 end loop;
1952 elsif l_child_bo_code = 'TLX'
1953 then
1954 for j in 1..PX_ORG_OBJ.CONTACT_OBJS(i).TELEX_OBJS.COUNT
1955 loop
1956 if PX_ORG_OBJ.CONTACT_OBJS(i).TELEX_OBJS(j).telex_id = l_child_id
1957 then PX_ORG_OBJ.CONTACT_OBJS(i).TELEX_OBJS(j).action_type := l_action_type;
1958 l_child_upd_flag := 'Y';
1959 end if;
1960 end loop;
1961 end if;
1962 end if; --'HZ_CONTACT_POINTS'
1963 if l_child_entity_name = 'HZ_CONTACT_PREFERENCES' then
1964 for j in 1..PX_ORG_OBJ.CONTACT_OBJS(i).CONTACT_PREF_OBJS.count
1965 loop
1966 if PX_ORG_OBJ.CONTACT_OBJS(i).CONTACT_PREF_OBJS(j).contact_preference_id = l_child_id
1967 then PX_ORG_OBJ.CONTACT_OBJS(i).CONTACT_PREF_OBJS(j).action_type := l_action_type;
1968 l_child_upd_flag := 'Y';
1969 end if;
1970 end loop;
1971 end if;
1972 if l_child_entity_name = 'HZ_ORG_CONTACT_ROLES' then
1973 for j in 1..PX_ORG_OBJ.CONTACT_OBJS(i).ORG_CONTACT_ROLE_OBJS.count
1974 loop
1975 if PX_ORG_OBJ.CONTACT_OBJS(i).ORG_CONTACT_ROLE_OBJS(j).ORG_CONTACT_ROLE_ID = l_child_id
1976 then PX_ORG_OBJ.CONTACT_OBJS(i).ORG_CONTACT_ROLE_OBJS(j).action_type := l_action_type;
1977 l_child_upd_flag := 'Y';
1978 end if;
1979 end loop;
1980 end if;
1981 if l_child_entity_name in ('HZ_PARTY_SITES','HZ_LOCATIONS','HZ_CONTACT_POINTS','HZ_CONTACT_PREFERENCES','HZ_PARTY_SITE_USES') then
1982 for j in 1..PX_ORG_OBJ.CONTACT_OBJS(i).PARTY_SITE_OBJS.count
1983 loop
1984 set_orgcnt_site_bo_action_type(p_node_path => l_node_path,
1985 p_child_id => l_child_id,
1986 p_action_type => l_action_type,
1987 p_child_entity_name => l_child_entity_name,
1988 p_child_bo_code => l_child_bo_code,
1989 px_party_site_obj => PX_ORG_OBJ.CONTACT_OBJS(i).party_site_objs(j) );
1990 l_child_upd_flag := 'Y';
1991 end loop;
1992 end if; -- org contact site obj
1993 if PX_ORG_OBJ.CONTACT_OBJS(i).action_type = 'UNCHANGED'
1994 and l_child_upd_flag = 'Y'
1995 then PX_ORG_OBJ.CONTACT_OBJS(i).action_type := 'CHILD_UPDATED';
1996 end if;
1997 end loop;
1998 end if; -- org contact_obj
1999 end if; -- populated_flag = 'N'
2000 end loop;
2001 close c1;
2002
2003 EXCEPTION
2004
2005
2006 WHEN fnd_api.g_exc_unexpected_error THEN
2007 x_return_status := fnd_api.g_ret_sts_unexp_error;
2008
2009
2010 WHEN OTHERS THEN
2011 x_return_status := fnd_api.g_ret_sts_unexp_error;
2012
2013 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
2014 fnd_message.set_token('ERROR' ,SQLERRM);
2015 fnd_msg_pub.add;
2016
2017 end set_org_bo_action_type;
2018
2019
2020
2021 --------------------------------------
2022 --
2023 -- PROCEDURE get_organization_updated
2024 --
2025 -- DESCRIPTION
2026 --The caller provides an identifier for the Organizations update business event and organization id
2027 --the procedure returns one database object of the type HZ_ORGANIZATION_BO
2028 --
2029 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2030 --
2031 -- ARGUMENTS
2032 -- IN:
2033 -- p_init_msg_list Initialize message stack if it is set to
2034 -- p_event_id BES Event identifier.
2035 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
2036 -- OUT:
2037 -- x_organization_objs One or more created logical organization.
2038 -- x_return_status Return status after the call. The status can
2039 -- be fnd_api.g_ret_sts_success (success),
2040 -- fnd_api.g_ret_sts_error (error),
2041 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2042 -- x_msg_count Number of messages in message stack.
2043 -- x_msg_data Message text if x_msg_count is 1.
2044 --
2045 -- NOTES
2046 --
2047 -- MODIFICATION HISTORY
2048 --
2049 -- 06-JUN-2005 AWU Created.
2050 --
2051
2052 PROCEDURE get_organization_updated(
2053 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2054 p_event_id IN NUMBER,
2055 p_organization_id IN NUMBER,
2056 x_organization_obj OUT NOCOPY HZ_ORGANIZATION_BO,
2057 x_return_status OUT NOCOPY VARCHAR2,
2058 x_msg_count OUT NOCOPY NUMBER,
2059 x_msg_data OUT NOCOPY VARCHAR2
2060 ) is
2061
2062 l_obj_root_ids HZ_EXTRACT_BO_UTIL_PVT.BO_ID_TBL;
2063 l_debug_prefix VARCHAR2(30) := '';
2064 l_organization_obj HZ_ORGANIZATION_BO;
2065
2066 begin
2067
2068 -- initialize API return status to success.
2069 x_return_status := FND_API.G_RET_STS_SUCCESS;
2070
2071 -- Initialize message list if p_init_msg_list is set to TRUE
2072 IF FND_API.to_Boolean(p_init_msg_list) THEN
2073 FND_MSG_PUB.initialize;
2074 END IF;
2075
2076
2077 -- Debug info.
2078 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2079 hz_utility_v2pub.debug(p_message=>'get_organizations_updated(+)',
2080 p_prefix=>l_debug_prefix,
2081 p_msg_level=>fnd_log.level_procedure);
2082 END IF;
2083 /* moved to public api
2084 HZ_EXTRACT_BO_UTIL_PVT.validate_event_id(p_event_id => p_event_id,
2085 p_party_id => p_organization_id,
2086 x_return_status => x_return_status);
2087
2088 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2089 RAISE FND_API.G_EXC_ERROR;
2090 END IF;
2091 */
2092 -- Set action type to 'UNCHANGED' by default
2093
2094 get_organization_bo(
2095 p_init_msg_list => fnd_api.g_false,
2096 p_organization_id => p_organization_id,
2097 p_action_type => 'UNCHANGED',
2098 x_organization_obj => x_organization_obj,
2099 x_return_status => x_return_status,
2100 x_msg_count => x_msg_count,
2101 x_msg_data => x_msg_data);
2102
2103 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2104 RAISE FND_API.G_EXC_ERROR;
2105 END IF;
2106
2107
2108 -- Based on BOT, for updated branch, set action_type = 'UPDATED'/'CREATED'
2109
2110
2111 l_organization_obj := x_organization_obj;
2112 set_org_bo_action_type(p_event_id => p_event_id,
2113 p_root_id => p_organization_id,
2114 px_org_obj => l_organization_obj,
2115 x_return_status => x_return_status
2116 );
2117 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2118 RAISE FND_API.G_EXC_ERROR;
2119 END IF;
2120
2121 x_organization_obj := l_organization_obj;
2122
2123
2124
2125 -- Debug info.
2126 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2127 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2128 p_msg_data=>x_msg_data,
2129 p_msg_type=>'WARNING',
2130 p_msg_level=>fnd_log.level_exception);
2131 END IF;
2132
2133 -- Debug info.
2134 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2135 hz_utility_v2pub.debug(p_message=>'get_organizations_updated (-)',
2136 p_prefix=>l_debug_prefix,
2137 p_msg_level=>fnd_log.level_procedure);
2138 END IF;
2139
2140
2141 EXCEPTION
2142
2143 WHEN fnd_api.g_exc_error THEN
2144 x_return_status := fnd_api.g_ret_sts_error;
2145
2146 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2147 p_count => x_msg_count,
2148 p_data => x_msg_data);
2149
2150 -- Debug info.
2151 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2152 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2153 p_msg_data=>x_msg_data,
2154 p_msg_type=>'ERROR',
2155 p_msg_level=>fnd_log.level_error);
2156 END IF;
2157 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2158 hz_utility_v2pub.debug(p_message=>'get_organizations_updated(-)',
2159 p_prefix=>l_debug_prefix,
2160 p_msg_level=>fnd_log.level_procedure);
2161 END IF;
2162 WHEN fnd_api.g_exc_unexpected_error THEN
2163 x_return_status := fnd_api.g_ret_sts_unexp_error;
2164
2165 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2166 p_count => x_msg_count,
2167 p_data => x_msg_data);
2168
2169 -- Debug info.
2170 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2171 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2172 p_msg_data=>x_msg_data,
2173 p_msg_type=>'UNEXPECTED ERROR',
2174 p_msg_level=>fnd_log.level_error);
2175 END IF;
2176 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2177 hz_utility_v2pub.debug(p_message=>'get_organizations_updated(-)',
2178 p_prefix=>l_debug_prefix,
2179 p_msg_level=>fnd_log.level_procedure);
2180 END IF;
2181 WHEN OTHERS THEN
2182 x_return_status := fnd_api.g_ret_sts_unexp_error;
2183
2184 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
2185 fnd_message.set_token('ERROR' ,SQLERRM);
2186 fnd_msg_pub.add;
2187
2188 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2189 p_count => x_msg_count,
2190 p_data => x_msg_data);
2191
2192 -- Debug info.
2193 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2194 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2195 p_msg_data=>x_msg_data,
2196 p_msg_type=>'SQL ERROR',
2197 p_msg_level=>fnd_log.level_error);
2198 END IF;
2199 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2200 hz_utility_v2pub.debug(p_message=>'get_organizations_updated(-)',
2201 p_prefix=>l_debug_prefix,
2202 p_msg_level=>fnd_log.level_procedure);
2203 END IF;
2204
2205 end;
2206
2207
2208
2209
2210
2211 END HZ_EXTRACT_ORGANIZATION_BO_PVT;