[Home] [Help]
PACKAGE BODY: APPS.HZ_EXTENSIBILITY_PVT
Source
1 PACKAGE BODY HZ_EXTENSIBILITY_PVT AS
2 /* $Header: ARHEXTCB.pls 120.4 2006/03/06 18:14:13 acng noship $ */
3
4
5 -- =============================================================================
6 -- Package variables and cursors
7 -- =============================================================================
8
9 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ARHEXTCB.pls';
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'HZ_EXTENSIBILITY_PVT';
11 G_APP_NAME CONSTANT VARCHAR2(3) := 'AR';
12 G_PKG_NAME_TOKEN CONSTANT VARCHAR2(8) := 'PKG_NAME';
13 G_API_NAME_TOKEN CONSTANT VARCHAR2(8) := 'API_NAME';
14 G_PROC_NAME_TOKEN CONSTANT VARCHAR2(9) := 'PROC_NAME';
15 G_SQL_ERR_MSG_TOKEN CONSTANT VARCHAR2(11) := 'SQL_ERR_MSG';
16 G_PLSQL_ERR CONSTANT VARCHAR2(17) := 'HZ_PLSQL_ERR';
17 G_INVALID_PARAMS_MSG CONSTANT VARCHAR2(30) := 'HZ_API_INVALID_PARAMS';
18
19 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
20 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
21
22 G_EQ_VAL CONSTANT VARCHAR2(2) := 'EQ';
23 G_GT_VAL CONSTANT VARCHAR2(2) := 'GT';
24 G_GE_VAL CONSTANT VARCHAR2(2) := 'GE';
25 G_LT_VAL CONSTANT VARCHAR2(2) := 'LT';
26 G_LE_VAL CONSTANT VARCHAR2(2) := 'LE';
27
28 G_TRUE CONSTANT VARCHAR2(1) := 'T'; -- FND_API.G_TRUE;
29 G_FALSE CONSTANT VARCHAR2(1) := 'F'; -- FND_API.G_FALSE;
30
31 -- entity name used by conc programs.
32 --
33 C_ORG CONSTANT VARCHAR2(30) := 'ORGANIZATION';
34 C_PER CONSTANT VARCHAR2(30) := 'PERSON';
35
36 -- =============================================================================
37 -- Private Procedures
38 -- =============================================================================
39
40 -- ----------------------
41 --
42 -- Developer debugging
43 -- ----------------------
44 PROCEDURE code_debug (p_msg IN VARCHAR2) IS
45 BEGIN
46 -- sri_debug ('ITEM_PVT '||p_msg);
47 RETURN;
48 EXCEPTION
49 WHEN OTHERS THEN
50 NULL;
51 END code_debug;
52
53
54 -- -----------------------------------------------------------------------------
55 -- API Name: Process_User_Attrs_For_Item
56 --
57 -- Description:
58 -- Process passed-in User-Defined Attrs data for
59 -- the Item whose Primary Keys are passed in
60 -- -----------------------------------------------------------------------------
61 PROCEDURE Process_User_Attrs_For_Item (
62 p_api_version IN NUMBER
63 ,p_owner_table_id IN NUMBER
64 ,p_owner_table_name IN VARCHAR2
65 ,p_attributes_row_table IN EGO_USER_ATTR_ROW_TABLE
66 ,p_attributes_data_table IN EGO_USER_ATTR_DATA_TABLE
67 ,p_entity_id IN NUMBER DEFAULT NULL
68 ,p_entity_index IN NUMBER DEFAULT NULL
69 ,p_entity_code IN VARCHAR2 DEFAULT NULL
70 ,p_debug_level IN NUMBER DEFAULT 0
71 ,p_init_error_handler IN VARCHAR2 DEFAULT FND_API.G_TRUE
72 ,p_write_to_concurrent_log IN VARCHAR2 DEFAULT FND_API.G_FALSE
73 ,p_init_fnd_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
74 ,p_log_errors IN VARCHAR2 DEFAULT FND_API.G_TRUE
75 ,p_add_errors_to_fnd_stack IN VARCHAR2 DEFAULT FND_API.G_FALSE
76 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
77 ,x_failed_row_id_list OUT NOCOPY VARCHAR2
78 ,x_return_status OUT NOCOPY VARCHAR2
79 ,x_errorcode OUT NOCOPY NUMBER
80 ,x_msg_count OUT NOCOPY NUMBER
81 ,x_msg_data OUT NOCOPY VARCHAR2
82 ) IS
83
84 l_api_name CONSTANT VARCHAR2(30) := 'Process_User_Attrs_For_Item';
85 l_pk_column_NAME VARCHAR2(30);
86 l_pk_column_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
87 l_class_code_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
88 l_item_catalog_group_id NUMBER;
89 l_related_class_codes_list VARCHAR2(150);
90 l_user_privileges_on_object EGO_VARCHAR_TBL_TYPE;
91 l_token_table ERROR_HANDLER.Token_Tbl_Type;
92 l_extension_id NUMBER;
93 l_mode VARCHAR2(10);
94 l_object_type VARCHAR2(10);
95 l_operation VARCHAR2(1);
96
97 BEGIN
98
99 -------------------------------------------------------------------------
100 -- First we build tables of Primary Key and Classification Code values --
101 -------------------------------------------------------------------------
102
103 IF p_owner_table_name = 'HZ_PERSON_PROFILES' THEN
104 l_pk_column_name := 'PERSON_PROFILE_ID';
105 l_object_type := 'PERSON';
106 ELSIF p_owner_table_name = 'HZ_ORGANIZATION_PROFILES' THEN
107 l_pk_column_name := 'ORGANIZATION_PROFILE_ID';
108 l_object_type := 'ORG';
109 ELSIF p_owner_table_name = 'HZ_LOCATIONS' THEN
110 l_pk_column_name := 'LOCATION_ID';
111 l_object_type := 'LOCATION';
112 ELSIF p_owner_table_name = 'HZ_PARTY_SITES' THEN
113 l_pk_column_name := 'PARTY_SITE_ID';
114 l_object_type := 'PARTY_SITE';
115 END IF;
116
117 -----------------------
118 -- Get PKs organized --
119 -----------------------
120 l_pk_column_values :=
121 EGO_COL_NAME_VALUE_PAIR_ARRAY(
122 EGO_COL_NAME_VALUE_PAIR_OBJ(l_pk_column_name, TO_CHAR(p_owner_table_id))
123 );
124
125 ---------------------------------------------------------------
126 -- If all went well with retrieving privileges, we call PUAD --
127 ---------------------------------------------------------------
128 EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data(
129 p_api_version => 1.0
130 ,p_object_name => p_owner_table_name
131 ,p_attributes_row_table => p_attributes_row_table
132 ,p_attributes_data_table => p_attributes_data_table
133 ,p_pk_column_name_value_pairs => l_pk_column_values
134 ,p_class_code_name_value_pairs => l_class_code_values
135 ,p_user_privileges_on_object => l_user_privileges_on_object
136 ,p_entity_id => p_entity_id
137 ,p_entity_index => p_entity_index
138 ,p_entity_code => p_entity_code
139 ,p_debug_level => p_debug_level
140 ,p_init_error_handler => p_init_error_handler
141 ,p_write_to_concurrent_log => p_write_to_concurrent_log
142 ,p_init_fnd_msg_list => p_init_fnd_msg_list
143 ,p_log_errors => p_log_errors
144 ,p_add_errors_to_fnd_stack => p_add_errors_to_fnd_stack
145 ,p_commit => p_commit
146 ,x_extension_id => l_extension_id
147 ,x_mode => l_mode
148 ,x_failed_row_id_list => x_failed_row_id_list
149 ,x_return_status => x_return_status
150 ,x_errorcode => x_errorcode
151 ,x_msg_count => x_msg_count
152 ,x_msg_data => x_msg_data
153 );
154
155 IF(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
156 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
157 IF(l_extension_id IS NOT NULL) THEN
158 IF(l_mode = 'CREATE') THEN
159 l_operation := 'I';
160 ELSE
161 l_operation := 'U';
162 END IF;
163 HZ_POPULATE_BOT_PKG.pop_hz_extensibility(
164 p_operation => l_operation,
165 p_object_type => l_object_type,
166 p_extension_id => l_extension_id);
167 END IF;
168 END IF;
169 END IF;
170
171 EXCEPTION
172 WHEN FND_API.G_EXC_ERROR THEN
173
174 x_return_status := FND_API.G_RET_STS_ERROR;
175
176 x_msg_count := ERROR_HANDLER.Get_Message_Count();
177
178 IF (x_msg_count > 0) THEN
179 IF (FND_API.To_Boolean(p_log_errors)) THEN
180 IF (FND_API.To_Boolean(p_write_to_concurrent_log)) THEN
181 ERROR_HANDLER.Log_Error(
182 p_write_err_to_inttable => 'Y'
183 ,p_write_err_to_conclog => 'Y'
184 ,p_write_err_to_debugfile => ERROR_HANDLER.Get_Debug()
185 );
186 ELSE
187 ERROR_HANDLER.Log_Error(
188 p_write_err_to_inttable => 'Y'
189 ,p_write_err_to_debugfile => ERROR_HANDLER.Get_Debug()
190 );
191 END IF;
192 END IF;
193
194 IF (x_msg_count = 1) THEN
195 DECLARE
196 message_list ERROR_HANDLER.Error_Tbl_Type;
197 BEGIN
198 ERROR_HANDLER.Get_Message_List(message_list);
199 x_msg_data := message_list(message_list.FIRST).message_text;
200 END;
201 ELSE
202 x_msg_data := NULL;
203 END IF;
204 END IF;
205
206 WHEN OTHERS THEN
207
208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
209
210 DECLARE
211 l_dummy_entity_index NUMBER;
212 l_dummy_entity_id VARCHAR2(60);
213 l_dummy_message_type VARCHAR2(1);
214 BEGIN
215 l_token_table(1).TOKEN_NAME := 'PKG_NAME';
216 l_token_table(1).TOKEN_VALUE := G_PKG_NAME;
217 l_token_table(2).TOKEN_NAME := 'API_NAME';
218 l_token_table(2).TOKEN_VALUE := l_api_name;
219 l_token_table(3).TOKEN_NAME := 'SQL_ERR_MSG';
220 l_token_table(3).TOKEN_VALUE := SQLERRM;
221
222 IF (FND_API.To_Boolean(p_add_errors_to_fnd_stack)) THEN
223 ERROR_HANDLER.Add_Error_Message(
224 p_message_name => 'EGO_PLSQL_ERR'
225 ,p_application_id => 'EGO'
226 ,p_token_tbl => l_token_table
227 ,p_message_type => FND_API.G_RET_STS_ERROR
228 ,p_addto_fnd_stack => 'Y'
229 );
230 ELSE
231 ERROR_HANDLER.Add_Error_Message(
232 p_message_name => 'EGO_PLSQL_ERR'
233 ,p_application_id => 'EGO'
234 ,p_token_tbl => l_token_table
235 ,p_message_type => FND_API.G_RET_STS_ERROR
236 );
237 END IF;
238
239 ERROR_HANDLER.Get_Message(x_message_text => x_msg_data
240 ,x_entity_index => l_dummy_entity_index
241 ,x_entity_id => l_dummy_entity_id
242 ,x_message_type => l_dummy_message_type);
243
244 END;
245
246 END Process_User_Attrs_For_Item;
247
248 -- -----------------------------------------------------------------------------
249 -- API Name: Get_User_Attrs_For_Item
250 --
251 -- Description:
252 -- Fetch passed-in User-Defined Attrs data for
253 -- the Item whose Primary Keys are passed in
254 -- -----------------------------------------------------------------------------
255 PROCEDURE Get_User_Attrs_For_Item (
256 p_api_version IN NUMBER
257 ,p_org_profile_id IN NUMBER
258 ,p_attr_group_request_table IN EGO_ATTR_GROUP_REQUEST_TABLE
259 ,p_entity_id IN NUMBER DEFAULT NULL
260 ,p_entity_index IN NUMBER DEFAULT NULL
261 ,p_entity_code IN VARCHAR2 DEFAULT NULL
262 ,p_debug_level IN NUMBER DEFAULT 0
263 ,p_init_error_handler IN VARCHAR2 DEFAULT FND_API.G_TRUE
264 ,p_init_fnd_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
265 ,p_add_errors_to_fnd_stack IN VARCHAR2 DEFAULT FND_API.G_FALSE
266 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
267 ,x_attributes_row_table OUT NOCOPY EGO_USER_ATTR_ROW_TABLE
268 ,x_attributes_data_table OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
269 ,x_return_status OUT NOCOPY VARCHAR2
270 ,x_errorcode OUT NOCOPY NUMBER
271 ,x_msg_count OUT NOCOPY NUMBER
272 ,x_msg_data OUT NOCOPY VARCHAR2
273 ) IS
274
275 l_api_name CONSTANT VARCHAR2(30) := 'Get_User_Attrs_For_Item';
276
277 l_pk_column_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
278 l_user_privileges_on_object EGO_VARCHAR_TBL_TYPE;
279
280 BEGIN
281
282 -----------------------
283 -- Get PKs organized --
284 -----------------------
285 l_pk_column_values :=
286 EGO_COL_NAME_VALUE_PAIR_ARRAY(
287 EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_PROFILE_ID', TO_CHAR(p_org_profile_id))
288 );
289
290 ---------------------------------------------------------------
291 -- If all went well with retrieving privileges, we call GUAD --
295 ,p_object_name => 'EGO_ITEM'
292 ---------------------------------------------------------------
293 EGO_USER_ATTRS_DATA_PUB.Get_User_Attrs_Data(
294 p_api_version => p_api_version
296 ,p_pk_column_name_value_pairs => l_pk_column_values
297 ,p_attr_group_request_table => p_attr_group_request_table
298 ,p_user_privileges_on_object => l_user_privileges_on_object
299 ,p_entity_id => p_entity_id
300 ,p_entity_index => p_entity_index
301 ,p_entity_code => p_entity_code
302 ,p_debug_level => p_debug_level
303 ,p_init_error_handler => p_init_error_handler
304 ,p_init_fnd_msg_list => p_init_fnd_msg_list
305 ,p_add_errors_to_fnd_stack => p_add_errors_to_fnd_stack
306 ,p_commit => p_commit
307 ,x_attributes_row_table => x_attributes_row_table
308 ,x_attributes_data_table => x_attributes_data_table
309 ,x_return_status => x_return_status
310 ,x_errorcode => x_errorcode
311 ,x_msg_count => x_msg_count
312 ,x_msg_data => x_msg_data
313 );
314
315
316 EXCEPTION
317 WHEN FND_API.G_EXC_ERROR THEN
318
319 x_return_status := FND_API.G_RET_STS_ERROR;
320
321 x_msg_count := ERROR_HANDLER.Get_Message_Count();
322
323 IF (x_msg_count > 0) THEN
324 ERROR_HANDLER.Log_Error(
325 p_write_err_to_inttable => 'Y'
326 ,p_write_err_to_debugfile => ERROR_HANDLER.Get_Debug()
327 );
328
329 IF (x_msg_count = 1) THEN
330 DECLARE
331 message_list ERROR_HANDLER.Error_Tbl_Type;
332 BEGIN
333 ERROR_HANDLER.Get_Message_List(message_list);
334 x_msg_data := message_list(message_list.FIRST).message_text;
335 END;
336 ELSE
337 x_msg_data := NULL;
338 END IF;
339 END IF;
340
341 WHEN OTHERS THEN
342
343 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
344
345 DECLARE
346 l_token_table ERROR_HANDLER.Token_Tbl_Type;
347 l_dummy_entity_index NUMBER;
348 l_dummy_entity_id VARCHAR2(60);
349 l_dummy_message_type VARCHAR2(1);
350 BEGIN
351 l_token_table(1).TOKEN_NAME := 'PKG_NAME';
352 l_token_table(1).TOKEN_VALUE := G_PKG_NAME;
353 l_token_table(2).TOKEN_NAME := 'API_NAME';
354 l_token_table(2).TOKEN_VALUE := l_api_name;
355 l_token_table(3).TOKEN_NAME := 'SQL_ERR_MSG';
356 l_token_table(3).TOKEN_VALUE := SQLERRM;
357
358 IF (FND_API.To_Boolean(p_add_errors_to_fnd_stack)) THEN
359 ERROR_HANDLER.Add_Error_Message(
360 p_message_name => 'EGO_PLSQL_ERR'
361 ,p_application_id => 'EGO'
362 ,p_token_tbl => l_token_table
363 ,p_message_type => FND_API.G_RET_STS_ERROR
364 ,p_addto_fnd_stack => 'Y'
365 );
366 ELSE
367 ERROR_HANDLER.Add_Error_Message(
368 p_message_name => 'EGO_PLSQL_ERR'
369 ,p_application_id => 'EGO'
370 ,p_token_tbl => l_token_table
371 ,p_message_type => FND_API.G_RET_STS_ERROR
372 );
373 END IF;
374
375 ERROR_HANDLER.Get_Message(x_message_text => x_msg_data
376 ,x_entity_index => l_dummy_entity_index
377 ,x_entity_id => l_dummy_entity_id
378 ,x_message_type => l_dummy_message_type);
379
380 END;
381
382 END Get_User_Attrs_For_Item;
383
384 /**
385 * PROCEDURE copy_person_extent_data
386 *
387 * DESCRIPTION
388 * Copy person extent data. This procedure will be called whenever
389 * a new person profile is created for maintain history reason.
390 *
391 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
392 *
393 * ARGUMENTS
394 * IN:
395 * p_old_profile_id Old profile Id.
396 * p_new_profile_id New profile Id.
397 * IN/OUT:
398 * OUT:
399 * x_return_status Return status after the call. The status can
400 * be FND_API.G_RET_STS_SUCCESS (success),
401 * FND_API.G_RET_STS_ERROR (error),
402 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
403 *
404 * NOTES
405 *
406 * MODIFICATION HISTORY
407 *
408 * 12-01-2004 Jianying Huang o Created.
409 *
410 */
411
412 PROCEDURE copy_person_extent_data (
413 p_old_profile_id IN NUMBER,
414 p_new_profile_id IN NUMBER,
415 x_return_status IN OUT NOCOPY VARCHAR2
416 ) IS
417
418 l_debug_prefix VARCHAR2(30) := '';
419 l_created_by NUMBER;
420 l_last_update_login NUMBER;
421 l_last_updated_by NUMBER;
422
423 BEGIN
424
425 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
426 hz_utility_v2pub.debug(
427 p_prefix => l_debug_prefix,
428 p_message => 'copy_person_extent_data (+)',
429 p_msg_level => fnd_log.level_procedure);
430 END IF;
431
432 l_created_by := hz_utility_v2pub.created_by;
436 -- This code will copy the existing profile extension records over
433 l_last_update_login := hz_utility_v2pub.last_update_login;
434 l_last_updated_by := hz_utility_v2pub.last_updated_by;
435
437 -- to the new profile _id everytime the SST record id is changed.
438 -- When the record is copied the original extension_id is stored
439 -- in the old_extension_id column of the record, so that it can be
440 -- used by the CPUI componenet.
441
442 INSERT INTO HZ_PER_PROFILES_EXT_B (
443 extension_id,
444 person_profile_id,
445 attr_group_id,
446 created_by,
447 creation_date,
448 last_updated_by,
449 last_update_date,
450 last_update_login,
451 c_ext_attr1,
452 c_ext_attr2,
453 c_ext_attr3,
454 c_ext_attr4,
455 c_ext_attr5,
456 c_ext_attr6,
457 c_ext_attr7,
458 c_ext_attr8,
459 c_ext_attr9,
460 c_ext_attr10,
461 c_ext_attr11,
462 c_ext_attr12,
463 c_ext_attr13,
464 c_ext_attr14,
465 c_ext_attr15,
466 c_ext_attr16,
467 c_ext_attr17,
468 c_ext_attr18,
469 c_ext_attr19,
470 c_ext_attr20,
471 n_ext_attr1,
472 n_ext_attr2,
473 n_ext_attr3,
474 n_ext_attr4,
475 n_ext_attr5,
476 n_ext_attr6,
477 n_ext_attr7,
478 n_ext_attr8,
479 n_ext_attr9,
480 n_ext_attr10,
481 n_ext_attr11,
482 n_ext_attr12,
483 n_ext_attr13,
484 n_ext_attr14,
485 n_ext_attr15,
486 n_ext_attr16,
487 n_ext_attr17,
488 n_ext_attr18,
489 n_ext_attr19,
490 n_ext_attr20,
491 d_ext_attr1,
492 d_ext_attr2,
493 d_ext_attr3,
494 d_ext_attr4,
495 d_ext_attr5,
496 d_ext_attr6,
497 d_ext_attr7,
498 d_ext_attr8,
499 d_ext_attr9,
500 d_ext_attr10,
501 old_extension_id )
502 SELECT
503 ego_extfwk_s.nextval,
504 p_new_profile_id,
505 attr_group_id,
506 l_created_by,
507 SYSDATE,
508 l_last_updated_by,
509 SYSDATE,
510 l_last_update_login,
511 c_ext_attr1,
512 c_ext_attr2,
513 c_ext_attr3,
514 c_ext_attr4,
515 c_ext_attr5,
516 c_ext_attr6,
517 c_ext_attr7,
518 c_ext_attr8,
519 c_ext_attr9,
520 c_ext_attr10,
521 c_ext_attr11,
522 c_ext_attr12,
523 c_ext_attr13,
524 c_ext_attr14,
525 c_ext_attr15,
526 c_ext_attr16,
527 c_ext_attr17,
528 c_ext_attr18,
529 c_ext_attr19,
530 c_ext_attr20,
531 n_ext_attr1,
532 n_ext_attr2,
533 n_ext_attr3,
534 n_ext_attr4,
535 n_ext_attr5,
536 n_ext_attr6,
537 n_ext_attr7,
538 n_ext_attr8,
539 n_ext_attr9,
540 n_ext_attr10,
541 n_ext_attr11,
542 n_ext_attr12,
543 n_ext_attr13,
544 n_ext_attr14,
545 n_ext_attr15,
546 n_ext_attr16,
547 n_ext_attr17,
548 n_ext_attr18,
549 n_ext_attr19,
550 n_ext_attr20,
551 d_ext_attr1,
552 d_ext_attr2,
553 d_ext_attr3,
554 d_ext_attr4,
555 d_ext_attr5,
556 d_ext_attr6,
557 d_ext_attr7,
558 d_ext_attr8,
559 d_ext_attr9,
560 d_ext_attr10,
561 extension_id
562 FROM HZ_PER_PROFILES_EXT_B
563 WHERE person_profile_id = p_old_profile_id;
564
565 IF (SQL%ROWCOUNT > 0) THEN
566 INSERT INTO HZ_PER_PROFILES_EXT_TL (
567 extension_id,
568 person_profile_id,
569 attr_group_id,
570 source_lang,
571 language,
572 created_by,
573 creation_date,
574 last_updated_by,
575 last_update_date,
576 last_update_login,
577 tl_ext_attr1,
578 tl_ext_attr2,
579 tl_ext_attr3,
580 tl_ext_attr4,
581 tl_ext_attr5,
582 tl_ext_attr6,
583 tl_ext_attr7,
584 tl_ext_attr8,
585 tl_ext_attr9,
586 tl_ext_attr10,
587 tl_ext_attr11,
588 tl_ext_attr12,
589 tl_ext_attr13,
590 tl_ext_attr14,
591 tl_ext_attr15,
592 tl_ext_attr16,
593 tl_ext_attr17,
594 tl_ext_attr18,
595 tl_ext_attr19,
596 tl_ext_attr20
597 )
598 SELECT
599 b.extension_id,
600 p_new_profile_id,
601 tl.attr_group_id,
602 tl.source_lang,
603 tl.language,
604 l_created_by,
605 SYSDATE,
606 l_last_updated_by,
607 SYSDATE,
608 l_last_update_login,
609 tl.tl_ext_attr1,
610 tl.tl_ext_attr2,
611 tl.tl_ext_attr3,
612 tl.tl_ext_attr4,
613 tl.tl_ext_attr5,
614 tl.tl_ext_attr6,
615 tl.tl_ext_attr7,
616 tl.tl_ext_attr8,
617 tl.tl_ext_attr9,
618 tl.tl_ext_attr10,
619 tl.tl_ext_attr11,
620 tl.tl_ext_attr12,
621 tl.tl_ext_attr13,
622 tl.tl_ext_attr14,
623 tl.tl_ext_attr15,
624 tl.tl_ext_attr16,
625 tl.tl_ext_attr17,
626 tl.tl_ext_attr18,
630 HZ_PER_PROFILES_EXT_TL tl
627 tl.tl_ext_attr19,
628 tl.tl_ext_attr20
629 FROM HZ_PER_PROFILES_EXT_B b,
631 WHERE b.person_profile_id = p_new_profile_id
632 AND tl.extension_id = b.old_extension_id;
633 END IF;
634
635
636 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
637 hz_utility_v2pub.debug(
638 p_prefix => l_debug_prefix,
639 p_message => 'copy_person_extent_data (-)',
640 p_msg_level => fnd_log.level_procedure);
641 END IF;
642
643 END copy_person_extent_data;
644
645
646 /**
647 * PROCEDURE copy_org_extent_data
648 *
649 * DESCRIPTION
650 * Copy organization extent data. This procedure will be called whenever
651 * a new organization profile is created for maintain history reason.
652 *
653 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
654 *
655 * ARGUMENTS
656 * IN:
657 * p_old_profile_id Old profile Id.
658 * p_new_profile_id New profile Id.
659 * IN/OUT:
660 * OUT:
661 * x_return_status Return status after the call. The status can
662 * be FND_API.G_RET_STS_SUCCESS (success),
663 * FND_API.G_RET_STS_ERROR (error),
664 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
665 *
666 * NOTES
667 *
668 * MODIFICATION HISTORY
669 *
670 * 12-01-2004 Jianying Huang o Created.
671 *
672 */
673
674 PROCEDURE copy_org_extent_data (
675 p_old_profile_id IN NUMBER,
676 p_new_profile_id IN NUMBER,
677 x_return_status IN OUT NOCOPY VARCHAR2
678 ) IS
679
680 l_debug_prefix VARCHAR2(30) := '';
681 l_created_by NUMBER;
682 l_last_update_login NUMBER;
683 l_last_updated_by NUMBER;
684
685 BEGIN
686
687 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
688 hz_utility_v2pub.debug(
689 p_prefix => l_debug_prefix,
690 p_message => 'copy_org_extent_data (+)',
691 p_msg_level => fnd_log.level_procedure);
692 END IF;
693
694 l_created_by := hz_utility_v2pub.created_by;
695 l_last_update_login := hz_utility_v2pub.last_update_login;
696 l_last_updated_by := hz_utility_v2pub.last_updated_by;
697
698 -- This code will copy the existing profile extension records over
699 -- to the new profile _id everytime the SST record id is changed.
700 -- When the record is copied the original extension_id is stored
701 -- in the old_extension_id column of the record, so that it can be
702 -- used by the CPUI componenet.
703
704 INSERT INTO hz_org_profiles_ext_b (
705 extension_id,
706 organization_profile_id,
707 attr_group_id,
708 created_by,
709 creation_date,
710 last_updated_by,
711 last_update_date,
712 last_update_login,
713 c_ext_attr1,
714 c_ext_attr2,
715 c_ext_attr3,
716 c_ext_attr4,
717 c_ext_attr5,
718 c_ext_attr6,
719 c_ext_attr7,
720 c_ext_attr8,
721 c_ext_attr9,
722 c_ext_attr10,
723 c_ext_attr11,
724 c_ext_attr12,
725 c_ext_attr13,
726 c_ext_attr14,
727 c_ext_attr15,
728 c_ext_attr16,
729 c_ext_attr17,
730 c_ext_attr18,
731 c_ext_attr19,
732 c_ext_attr20,
733 n_ext_attr1,
734 n_ext_attr2,
735 n_ext_attr3,
736 n_ext_attr4,
737 n_ext_attr5,
738 n_ext_attr6,
739 n_ext_attr7,
740 n_ext_attr8,
741 n_ext_attr9,
742 n_ext_attr10,
743 n_ext_attr11,
744 n_ext_attr12,
745 n_ext_attr13,
746 n_ext_attr14,
747 n_ext_attr15,
748 n_ext_attr16,
749 n_ext_attr17,
750 n_ext_attr18,
751 n_ext_attr19,
752 n_ext_attr20,
753 d_ext_attr1,
754 d_ext_attr2,
755 d_ext_attr3,
756 d_ext_attr4,
757 d_ext_attr5,
758 d_ext_attr6,
759 d_ext_attr7,
760 d_ext_attr8,
761 d_ext_attr9,
762 d_ext_attr10,
763 old_extension_id )
764 SELECT
765 ego_extfwk_s.nextval,
766 p_new_profile_id,
767 attr_group_id,
768 l_created_by,
769 SYSDATE,
770 l_last_updated_by,
771 SYSDATE,
772 l_last_update_login,
773 c_ext_attr1,
774 c_ext_attr2,
775 c_ext_attr3,
776 c_ext_attr4,
777 c_ext_attr5,
778 c_ext_attr6,
779 c_ext_attr7,
780 c_ext_attr8,
781 c_ext_attr9,
782 c_ext_attr10,
783 c_ext_attr11,
784 c_ext_attr12,
785 c_ext_attr13,
786 c_ext_attr14,
787 c_ext_attr15,
788 c_ext_attr16,
789 c_ext_attr17,
790 c_ext_attr18,
791 c_ext_attr19,
792 c_ext_attr20,
793 n_ext_attr1,
794 n_ext_attr2,
795 n_ext_attr3,
796 n_ext_attr4,
797 n_ext_attr5,
798 n_ext_attr6,
799 n_ext_attr7,
800 n_ext_attr8,
801 n_ext_attr9,
802 n_ext_attr10,
803 n_ext_attr11,
804 n_ext_attr12,
808 n_ext_attr16,
805 n_ext_attr13,
806 n_ext_attr14,
807 n_ext_attr15,
809 n_ext_attr17,
810 n_ext_attr18,
811 n_ext_attr19,
812 n_ext_attr20,
813 d_ext_attr1,
814 d_ext_attr2,
815 d_ext_attr3,
816 d_ext_attr4,
817 d_ext_attr5,
818 d_ext_attr6,
819 d_ext_attr7,
820 d_ext_attr8,
821 d_ext_attr9,
822 d_ext_attr10,
823 extension_id
824 FROM hz_org_profiles_ext_b
825 WHERE organization_profile_id = p_old_profile_id;
826
827 IF (SQL%ROWCOUNT > 0) THEN
828 INSERT INTO hz_org_profiles_ext_tl (
829 extension_id,
830 organization_profile_id,
831 attr_group_id,
832 source_lang,
833 language,
834 created_by,
835 creation_date,
836 last_updated_by,
837 last_update_date,
838 last_update_login,
839 tl_ext_attr1,
840 tl_ext_attr2,
841 tl_ext_attr3,
842 tl_ext_attr4,
843 tl_ext_attr5,
844 tl_ext_attr6,
845 tl_ext_attr7,
846 tl_ext_attr8,
847 tl_ext_attr9,
848 tl_ext_attr10,
849 tl_ext_attr11,
850 tl_ext_attr12,
851 tl_ext_attr13,
852 tl_ext_attr14,
853 tl_ext_attr15,
854 tl_ext_attr16,
855 tl_ext_attr17,
856 tl_ext_attr18,
857 tl_ext_attr19,
858 tl_ext_attr20
859 )
860 SELECT
861 b.extension_id,
862 p_new_profile_id,
863 tl.attr_group_id,
864 tl.source_lang,
865 tl.language,
866 l_created_by,
867 SYSDATE,
868 l_last_updated_by,
869 SYSDATE,
870 l_last_update_login,
871 tl.tl_ext_attr1,
872 tl.tl_ext_attr2,
873 tl.tl_ext_attr3,
874 tl.tl_ext_attr4,
875 tl.tl_ext_attr5,
876 tl.tl_ext_attr6,
877 tl.tl_ext_attr7,
878 tl.tl_ext_attr8,
879 tl.tl_ext_attr9,
880 tl.tl_ext_attr10,
881 tl.tl_ext_attr11,
882 tl.tl_ext_attr12,
883 tl.tl_ext_attr13,
884 tl.tl_ext_attr14,
885 tl.tl_ext_attr15,
886 tl.tl_ext_attr16,
887 tl.tl_ext_attr17,
888 tl.tl_ext_attr18,
889 tl.tl_ext_attr19,
890 tl.tl_ext_attr20
891 FROM hz_org_profiles_ext_b b,
892 hz_org_profiles_ext_tl tl
893 WHERE b.organization_profile_id = p_new_profile_id
894 AND tl.extension_id = b.old_extension_id;
895 END IF;
896
897 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level THEN
898 hz_utility_v2pub.debug(
899 p_prefix => l_debug_prefix,
900 p_message => 'copy_org_extent_data (-)',
901 p_msg_level => fnd_log.level_procedure);
902 END IF;
903
904 END copy_org_extent_data;
905
906
907 /**
908 * PRIVATE PROCEDURE Write_Log
909 *
910 * DESCRIPTION
911 * Write message into log file.
912 *
913 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
914 *
915 * IN:
916 * p_str Message.
917 *
918 * MODIFICATION HISTORY
919 *
920 * 03-15-2005 Jianying Huang o Created.
921 */
922
923 PROCEDURE Write_Log (
924 p_str IN VARCHAR2
925 ) IS
926 BEGIN
927 FND_FILE.PUT_LINE(FND_FILE.LOG,TO_CHAR(SYSDATE, 'YYYY/MM/DD HH:MI:SS')||' -- '||p_str);
928 END Write_Log;
929
930
931 /**
932 * PRIVATE PROCEDURE populate_staging_table
933 *
934 * DESCRIPTION
935 * Populate staging table.
936 *
937 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
938 *
939 * IN:
940 * p_entity_name Entity Name
941 * p_batch_size Batch Size
942 * OUT:
943 * x_total Number of records in staging table
944 *
945 * MODIFICATION HISTORY
946 *
947 * 03-15-2005 Jianying Huang o Created.
948 */
949
950 PROCEDURE populate_staging_table (
951 p_entity_name IN VARCHAR2,
952 p_batch_size IN NUMBER,
953 x_total OUT NOCOPY NUMBER
954 ) IS
955
956 BEGIN
957
958 IF p_entity_name = C_ORG THEN
959 -- insert into org staging table
960 --
961 INSERT INTO hz_org_profiles_ext_sg (
962 old_profile_id,
963 new_profile_id,
964 work_unit_number,
965 status
966 )
967 SELECT old_profile_id,
968 new_profile_id,
969 round(ROWNUM/p_batch_size + 0.5) work_unit_number,
970 'N' status
971 FROM (
972 SELECT /*+ parallel(p1) parallel(p2) */
973 max(p2.organization_profile_id) old_profile_id, p1.organization_profile_id new_profile_id
974 FROM hz_organization_profiles p1,
975 hz_organization_profiles p2
976 WHERE p1.effective_end_date is null
977 AND NOT EXISTS (
978 SELECT null
979 FROM hz_org_profiles_ext_b ext
980 WHERE p1.organization_profile_id = ext.organization_profile_id)
981 AND p2.party_id = p1.party_id
982 AND p2.organization_profile_id <> p1.organization_profile_id
983 AND EXISTS (
984 SELECT null
988
985 FROM hz_org_profiles_ext_b ext
986 WHERE p2.organization_profile_id = ext.organization_profile_id)
987 GROUP BY p1.organization_profile_id);
989 ELSIF p_entity_name = C_PER THEN
990 -- insert into person staging table
991 --
992 INSERT INTO hz_per_profiles_ext_sg (
993 old_profile_id,
994 new_profile_id,
995 work_unit_number,
996 status
997 )
998 SELECT old_profile_id,
999 new_profile_id,
1000 round(ROWNUM/p_batch_size + 0.5) work_unit_number,
1001 'N' status
1002 FROM (
1003 SELECT /*+ parallel(p1) parallel(p2) */
1004 max(p2.person_profile_id) old_profile_id, p1.person_profile_id new_profile_id
1005 FROM hz_person_profiles p1,
1006 hz_person_profiles p2
1007 WHERE p1.effective_end_date is null
1008 AND NOT EXISTS (
1009 SELECT null
1010 FROM hz_per_profiles_ext_b ext
1011 WHERE p1.person_profile_id = ext.person_profile_id)
1012 AND p2.party_id = p1.party_id
1013 AND p2.person_profile_id <> p1.person_profile_id
1014 AND EXISTS (
1015 SELECT null
1016 FROM hz_per_profiles_ext_b ext
1017 WHERE p2.person_profile_id = ext.person_profile_id)
1018 GROUP BY p1.person_profile_id);
1019
1020 END IF;
1021
1022 x_total := SQL%ROWCOUNT;
1023
1024 COMMIT;
1025
1026 END populate_staging_table;
1027
1028
1029 /**
1030 * PRIVATE PROCEDURE update_staging_status
1031 *
1032 * DESCRIPTION
1033 * Update staging table status column
1034 *
1035 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1036 *
1037 * IN:
1038 * p_entity_name Entity Name
1039 * p_work_unit_number Work Unit Number
1040 * p_status Status
1041 *
1042 * MODIFICATION HISTORY
1043 *
1044 * 03-15-2005 Jianying Huang o Created.
1045 */
1046
1047 PROCEDURE update_staging_status (
1048 p_entity_name IN VARCHAR2,
1049 p_work_unit_number IN NUMBER,
1050 p_status IN VARCHAR2
1051 ) IS
1052 BEGIN
1053
1054 IF p_entity_name = C_ORG THEN
1055 -- update org staging table
1056 --
1057 UPDATE hz_org_profiles_ext_sg
1058 SET status = p_status
1059 WHERE work_unit_number = p_work_unit_number;
1060
1061 ELSIF p_entity_name = C_PER THEN
1062 -- update per staging table
1063 --
1064 UPDATE hz_per_profiles_ext_sg
1065 SET status = p_status
1066 WHERE work_unit_number = p_work_unit_number;
1067
1068 END IF;
1069
1070 END update_staging_status;
1071
1072
1073 /**
1074 * PRIVATE PROCEDURE copy_org_extension
1075 *
1076 * DESCRIPTION
1077 * Copy organization extension data
1078 *
1079 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1080 *
1081 * IN:
1082 * p_parent_request_id Parent Request ID
1083 * p_work_unit_number Work Unit Number
1084 * p_created_by Created By
1085 * p_last_updated_by Last Updated By
1086 * p_last_update_login Last Update Login
1087 *
1088 * MODIFICATION HISTORY
1089 *
1090 * 03-15-2005 Jianying Huang o Created.
1091 */
1092
1093 PROCEDURE copy_org_extension (
1094 p_parent_request_id IN NUMBER,
1095 p_work_unit_number IN NUMBER,
1096 p_created_by IN NUMBER,
1097 p_last_updated_by IN NUMBER,
1098 p_last_update_login IN NUMBER
1099 ) IS
1100 BEGIN
1101
1102 -- insert into _b table
1103 --
1104 INSERT INTO hz_org_profiles_ext_b ext (
1105 extension_id,
1106 organization_profile_id,
1107 attr_group_id,
1108 created_by,
1109 creation_date,
1110 last_updated_by,
1111 last_update_date,
1112 last_update_login,
1113 c_ext_attr1,
1114 c_ext_attr2,
1115 c_ext_attr3,
1116 c_ext_attr4,
1117 c_ext_attr5,
1118 c_ext_attr6,
1119 c_ext_attr7,
1120 c_ext_attr8,
1121 c_ext_attr9,
1122 c_ext_attr10,
1123 c_ext_attr11,
1124 c_ext_attr12,
1125 c_ext_attr13,
1126 c_ext_attr14,
1127 c_ext_attr15,
1128 c_ext_attr16,
1129 c_ext_attr17,
1130 c_ext_attr18,
1131 c_ext_attr19,
1132 c_ext_attr20,
1133 n_ext_attr1,
1134 n_ext_attr2,
1135 n_ext_attr3,
1136 n_ext_attr4,
1137 n_ext_attr5,
1138 n_ext_attr6,
1139 n_ext_attr7,
1140 n_ext_attr8,
1141 n_ext_attr9,
1142 n_ext_attr10,
1143 n_ext_attr11,
1144 n_ext_attr12,
1145 n_ext_attr13,
1146 n_ext_attr14,
1147 n_ext_attr15,
1148 n_ext_attr16,
1149 n_ext_attr17,
1150 n_ext_attr18,
1151 n_ext_attr19,
1152 n_ext_attr20,
1153 d_ext_attr1,
1154 d_ext_attr2,
1155 d_ext_attr3,
1156 d_ext_attr4,
1157 d_ext_attr5,
1158 d_ext_attr6,
1159 d_ext_attr7,
1160 d_ext_attr8,
1161 d_ext_attr9,
1162 d_ext_attr10,
1163 old_extension_id )
1164 SELECT
1165 ego_extfwk_s.nextval,
1166 sg.new_profile_id,
1167 attr_group_id,
1171 SYSDATE,
1168 p_created_by,
1169 SYSDATE,
1170 p_last_updated_by,
1172 p_last_update_login,
1173 c_ext_attr1,
1174 c_ext_attr2,
1175 c_ext_attr3,
1176 c_ext_attr4,
1177 c_ext_attr5,
1178 c_ext_attr6,
1179 c_ext_attr7,
1180 c_ext_attr8,
1181 c_ext_attr9,
1182 c_ext_attr10,
1183 c_ext_attr11,
1184 c_ext_attr12,
1185 c_ext_attr13,
1186 c_ext_attr14,
1187 c_ext_attr15,
1188 c_ext_attr16,
1189 c_ext_attr17,
1190 c_ext_attr18,
1191 c_ext_attr19,
1192 c_ext_attr20,
1193 n_ext_attr1,
1194 n_ext_attr2,
1195 n_ext_attr3,
1196 n_ext_attr4,
1197 n_ext_attr5,
1198 n_ext_attr6,
1199 n_ext_attr7,
1200 n_ext_attr8,
1201 n_ext_attr9,
1202 n_ext_attr10,
1203 n_ext_attr11,
1204 n_ext_attr12,
1205 n_ext_attr13,
1206 n_ext_attr14,
1207 n_ext_attr15,
1208 n_ext_attr16,
1209 n_ext_attr17,
1210 n_ext_attr18,
1211 n_ext_attr19,
1212 n_ext_attr20,
1213 d_ext_attr1,
1214 d_ext_attr2,
1215 d_ext_attr3,
1216 d_ext_attr4,
1217 d_ext_attr5,
1218 d_ext_attr6,
1219 d_ext_attr7,
1220 d_ext_attr8,
1221 d_ext_attr9,
1222 d_ext_attr10,
1223 extension_id
1224 FROM hz_org_profiles_ext_b b,
1225 hz_org_profiles_ext_sg sg
1226 WHERE
1227 sg.work_unit_number = p_work_unit_number
1228 AND b.organization_profile_id = sg.old_profile_id;
1229
1230 Write_Log(SQL%ROWCOUNT||' records inserted into org base table.');
1231
1232 IF (SQL%ROWCOUNT IS NULL) OR (SQL%ROWCOUNT = 0) THEN
1233 RETURN;
1234 END IF;
1235
1236 -- gather table statistics
1237 --
1238 fnd_stats.gather_table_stats('AR', 'HZ_ORG_PROFILES_EXT_B');
1239
1240 -- insert into _tl table
1241 --
1242 INSERT INTO hz_org_profiles_ext_tl ext (
1243 extension_id,
1244 organization_profile_id,
1245 attr_group_id,
1246 source_lang,
1247 language,
1248 created_by,
1249 creation_date,
1250 last_updated_by,
1251 last_update_date,
1252 last_update_login,
1253 tl_ext_attr1,
1254 tl_ext_attr2,
1255 tl_ext_attr3,
1256 tl_ext_attr4,
1257 tl_ext_attr5,
1258 tl_ext_attr6,
1259 tl_ext_attr7,
1260 tl_ext_attr8,
1261 tl_ext_attr9,
1262 tl_ext_attr10,
1263 tl_ext_attr11,
1264 tl_ext_attr12,
1265 tl_ext_attr13,
1266 tl_ext_attr14,
1267 tl_ext_attr15,
1268 tl_ext_attr16,
1269 tl_ext_attr17,
1270 tl_ext_attr18,
1271 tl_ext_attr19,
1272 tl_ext_attr20
1273 )
1274 SELECT
1275 b.extension_id,
1276 sg.new_profile_id,
1277 tl.attr_group_id,
1278 tl.source_lang,
1279 tl.language,
1280 p_created_by,
1281 SYSDATE,
1282 p_last_updated_by,
1283 SYSDATE,
1284 p_last_update_login,
1285 tl.tl_ext_attr1,
1286 tl.tl_ext_attr2,
1287 tl.tl_ext_attr3,
1288 tl.tl_ext_attr4,
1289 tl.tl_ext_attr5,
1290 tl.tl_ext_attr6,
1291 tl.tl_ext_attr7,
1292 tl.tl_ext_attr8,
1293 tl.tl_ext_attr9,
1294 tl.tl_ext_attr10,
1295 tl.tl_ext_attr11,
1296 tl.tl_ext_attr12,
1297 tl.tl_ext_attr13,
1298 tl.tl_ext_attr14,
1299 tl.tl_ext_attr15,
1300 tl.tl_ext_attr16,
1301 tl.tl_ext_attr17,
1302 tl.tl_ext_attr18,
1303 tl.tl_ext_attr19,
1304 tl.tl_ext_attr20
1305 FROM hz_org_profiles_ext_b b,
1306 hz_org_profiles_ext_tl tl,
1307 hz_org_profiles_ext_sg sg
1308 WHERE
1309 sg.work_unit_number = p_work_unit_number
1310 AND b.organization_profile_id = sg.new_profile_id
1311 AND tl.extension_id = b.old_extension_id;
1312
1313 Write_Log(SQL%ROWCOUNT||' records inserted into org tl table.');
1314
1315 -- gather table statistics
1316 --
1317 fnd_stats.gather_table_stats('AR', 'HZ_ORG_PROFILES_EXT_TL');
1318
1319 END copy_org_extension;
1320
1321
1322 /**
1323 * PRIVATE PROCEDURE copy_per_extension
1324 *
1325 * DESCRIPTION
1326 * Copy person extension data
1327 *
1328 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1329 *
1330 * IN:
1331 * p_parent_request_id Parent Request ID
1332 * p_work_unit_number Work Unit Number
1333 * p_created_by Created By
1334 * p_last_updated_by Last Updated By
1335 * p_last_update_login Last Update Login
1336 *
1337 * MODIFICATION HISTORY
1338 *
1339 * 03-15-2005 Jianying Huang o Created.
1340 */
1341
1342 PROCEDURE copy_per_extension (
1343 p_parent_request_id IN NUMBER,
1344 p_work_unit_number IN NUMBER,
1345 p_created_by IN NUMBER,
1346 p_last_updated_by IN NUMBER,
1347 p_last_update_login IN NUMBER
1348 ) IS
1349 BEGIN
1350
1351 -- insert into _b table
1352 --
1353 INSERT INTO hz_per_profiles_ext_b ext (
1354 extension_id,
1355 person_profile_id,
1356 attr_group_id,
1357 created_by,
1358 creation_date,
1359 last_updated_by,
1363 c_ext_attr2,
1360 last_update_date,
1361 last_update_login,
1362 c_ext_attr1,
1364 c_ext_attr3,
1365 c_ext_attr4,
1366 c_ext_attr5,
1367 c_ext_attr6,
1368 c_ext_attr7,
1369 c_ext_attr8,
1370 c_ext_attr9,
1371 c_ext_attr10,
1372 c_ext_attr11,
1373 c_ext_attr12,
1374 c_ext_attr13,
1375 c_ext_attr14,
1376 c_ext_attr15,
1377 c_ext_attr16,
1378 c_ext_attr17,
1379 c_ext_attr18,
1380 c_ext_attr19,
1381 c_ext_attr20,
1382 n_ext_attr1,
1383 n_ext_attr2,
1384 n_ext_attr3,
1385 n_ext_attr4,
1386 n_ext_attr5,
1387 n_ext_attr6,
1388 n_ext_attr7,
1389 n_ext_attr8,
1390 n_ext_attr9,
1391 n_ext_attr10,
1392 n_ext_attr11,
1393 n_ext_attr12,
1394 n_ext_attr13,
1395 n_ext_attr14,
1396 n_ext_attr15,
1397 n_ext_attr16,
1398 n_ext_attr17,
1399 n_ext_attr18,
1400 n_ext_attr19,
1401 n_ext_attr20,
1402 d_ext_attr1,
1403 d_ext_attr2,
1404 d_ext_attr3,
1405 d_ext_attr4,
1406 d_ext_attr5,
1407 d_ext_attr6,
1408 d_ext_attr7,
1409 d_ext_attr8,
1410 d_ext_attr9,
1411 d_ext_attr10,
1412 old_extension_id )
1413 SELECT
1414 ego_extfwk_s.nextval,
1415 sg.new_profile_id,
1416 attr_group_id,
1417 p_created_by,
1418 SYSDATE,
1419 p_last_updated_by,
1420 SYSDATE,
1421 p_last_update_login,
1422 c_ext_attr1,
1423 c_ext_attr2,
1424 c_ext_attr3,
1425 c_ext_attr4,
1426 c_ext_attr5,
1427 c_ext_attr6,
1428 c_ext_attr7,
1429 c_ext_attr8,
1430 c_ext_attr9,
1431 c_ext_attr10,
1432 c_ext_attr11,
1433 c_ext_attr12,
1434 c_ext_attr13,
1435 c_ext_attr14,
1436 c_ext_attr15,
1437 c_ext_attr16,
1438 c_ext_attr17,
1439 c_ext_attr18,
1440 c_ext_attr19,
1441 c_ext_attr20,
1442 n_ext_attr1,
1443 n_ext_attr2,
1444 n_ext_attr3,
1445 n_ext_attr4,
1446 n_ext_attr5,
1447 n_ext_attr6,
1448 n_ext_attr7,
1449 n_ext_attr8,
1450 n_ext_attr9,
1451 n_ext_attr10,
1452 n_ext_attr11,
1453 n_ext_attr12,
1454 n_ext_attr13,
1455 n_ext_attr14,
1456 n_ext_attr15,
1457 n_ext_attr16,
1458 n_ext_attr17,
1459 n_ext_attr18,
1460 n_ext_attr19,
1461 n_ext_attr20,
1462 d_ext_attr1,
1463 d_ext_attr2,
1464 d_ext_attr3,
1465 d_ext_attr4,
1466 d_ext_attr5,
1467 d_ext_attr6,
1468 d_ext_attr7,
1469 d_ext_attr8,
1470 d_ext_attr9,
1471 d_ext_attr10,
1472 extension_id
1473 FROM hz_per_profiles_ext_b b,
1474 hz_per_profiles_ext_sg sg
1475 WHERE
1476 sg.work_unit_number = p_work_unit_number
1477 AND b.person_profile_id = sg.old_profile_id;
1478
1479 Write_Log(SQL%ROWCOUNT||' records inserted into per base table.');
1480
1481 IF (SQL%ROWCOUNT IS NULL) OR (SQL%ROWCOUNT = 0) THEN
1482 RETURN;
1483 END IF;
1484
1485 -- gather table statistics
1486 --
1487 fnd_stats.gather_table_stats('AR', 'HZ_PER_PROFILES_EXT_B');
1488
1489 -- insert into _tl table
1490 --
1491 INSERT INTO hz_per_profiles_ext_tl ext (
1492 extension_id,
1493 person_profile_id,
1494 attr_group_id,
1495 source_lang,
1496 language,
1497 created_by,
1498 creation_date,
1499 last_updated_by,
1500 last_update_date,
1501 last_update_login,
1502 tl_ext_attr1,
1503 tl_ext_attr2,
1504 tl_ext_attr3,
1505 tl_ext_attr4,
1506 tl_ext_attr5,
1507 tl_ext_attr6,
1508 tl_ext_attr7,
1509 tl_ext_attr8,
1510 tl_ext_attr9,
1511 tl_ext_attr10,
1512 tl_ext_attr11,
1513 tl_ext_attr12,
1514 tl_ext_attr13,
1515 tl_ext_attr14,
1516 tl_ext_attr15,
1517 tl_ext_attr16,
1518 tl_ext_attr17,
1519 tl_ext_attr18,
1520 tl_ext_attr19,
1521 tl_ext_attr20
1522 )
1523 SELECT
1524 b.extension_id,
1525 sg.new_profile_id,
1526 tl.attr_group_id,
1527 tl.source_lang,
1528 tl.language,
1529 p_created_by,
1530 SYSDATE,
1531 p_last_updated_by,
1532 SYSDATE,
1533 p_last_update_login,
1534 tl.tl_ext_attr1,
1535 tl.tl_ext_attr2,
1536 tl.tl_ext_attr3,
1537 tl.tl_ext_attr4,
1538 tl.tl_ext_attr5,
1539 tl.tl_ext_attr6,
1540 tl.tl_ext_attr7,
1541 tl.tl_ext_attr8,
1542 tl.tl_ext_attr9,
1543 tl.tl_ext_attr10,
1544 tl.tl_ext_attr11,
1545 tl.tl_ext_attr12,
1546 tl.tl_ext_attr13,
1547 tl.tl_ext_attr14,
1548 tl.tl_ext_attr15,
1549 tl.tl_ext_attr16,
1550 tl.tl_ext_attr17,
1551 tl.tl_ext_attr18,
1552 tl.tl_ext_attr19,
1553 tl.tl_ext_attr20
1554 FROM hz_per_profiles_ext_b b,
1555 hz_per_profiles_ext_tl tl,
1556 hz_per_profiles_ext_sg sg
1557 WHERE
1558 sg.work_unit_number = p_work_unit_number
1559 AND b.person_profile_id = sg.new_profile_id
1563
1560 AND tl.extension_id = b.old_extension_id;
1561
1562 Write_Log(SQL%ROWCOUNT||' records inserted into per tl table.');
1564 -- gather table statistics
1565 --
1566 fnd_stats.gather_table_stats('AR', 'HZ_PER_PROFILES_EXT_TL');
1567
1568 END copy_per_extension;
1569
1570
1571 /**
1572 * PUBLIC PROCEDURE copy_conc_sub
1573 *
1574 * DESCRIPTION
1575 * Sub concurrent program to copy org extension data
1576 *
1577 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1578 *
1579 * IN:
1580 * p_parent_request_id Parent Request ID
1581 *
1582 * MODIFICATION HISTORY
1583 *
1584 * 03-15-2005 Jianying Huang o Created.
1585 */
1586
1587 PROCEDURE copy_conc_sub (
1588 errbuf OUT NOCOPY VARCHAR2,
1589 retcode OUT NOCOPY VARCHAR2,
1590 p_entity_name IN VARCHAR2,
1591 p_parent_request_id IN NUMBER
1592 ) IS
1593
1594 CURSOR c_org_work_unit IS
1595 SELECT work_unit_number
1596 FROM hz_org_profiles_ext_sg
1597 WHERE status = 'N'
1598 AND ROWNUM =1;
1599
1600 CURSOR c_per_work_unit IS
1601 SELECT work_unit_number
1602 FROM hz_per_profiles_ext_sg
1603 WHERE status = 'N'
1604 AND ROWNUM =1;
1605
1606 CURSOR c_lock_org_records (
1607 p_work_unit_number NUMBER
1608 ) IS
1609 SELECT *
1610 FROM hz_org_profiles_ext_sg
1611 WHERE status = 'N'
1612 AND work_unit_number = p_work_unit_number
1613 FOR UPDATE NOWAIT;
1614
1615 CURSOR c_lock_per_records (
1616 p_work_unit_number NUMBER
1617 ) IS
1618 SELECT *
1619 FROM hz_per_profiles_ext_sg
1620 WHERE status = 'N'
1621 AND work_unit_number = p_work_unit_number
1622 FOR UPDATE NOWAIT;
1623
1624 resource_busy EXCEPTION;
1625 PRAGMA EXCEPTION_INIT(resource_busy, -54);
1626
1627 l_created_by NUMBER;
1628 l_last_updated_by NUMBER;
1629 l_last_update_login NUMBER;
1630 l_work_unit_number NUMBER;
1631
1632 BEGIN
1633
1634 retcode := 0;
1635
1636 -- retrieve who information
1637 --
1638 l_created_by := hz_utility_v2pub.created_by;
1639 l_last_update_login := hz_utility_v2pub.last_update_login;
1640 l_last_updated_by := hz_utility_v2pub.last_updated_by;
1641
1642 -- find out if there is any work units need to be processed
1643 --
1644 LOOP
1645
1646 <<next_fetch>>
1647
1648 -- get work unit number
1649 --
1650 IF p_entity_name = C_ORG THEN
1651 OPEN c_org_work_unit;
1652 FETCH c_org_work_unit INTO l_work_unit_number;
1653 IF c_org_work_unit%NOTFOUND THEN
1654 l_work_unit_number := 0;
1655 END IF;
1656 CLOSE c_org_work_unit;
1657
1658 ELSIF p_entity_name = C_PER THEN
1659 OPEN c_per_work_unit;
1660 FETCH c_per_work_unit INTO l_work_unit_number;
1661 IF c_per_work_unit%NOTFOUND THEN
1662 l_work_unit_number := 0;
1663 END IF;
1664 CLOSE c_per_work_unit;
1665
1666 END IF;
1667
1668 IF l_work_unit_number = 0 THEN
1669 Write_Log('No more records need to be processed. Quit.');
1670 RETURN;
1671 END IF;
1672
1673 Write_Log('l_work_unit_number = '||l_work_unit_number);
1674
1675 -- lock records
1676 --
1677 BEGIN
1678 IF p_entity_name = C_ORG THEN
1679 OPEN c_lock_org_records(l_work_unit_number);
1680 CLOSE c_lock_org_records;
1681
1682 ELSIF p_entity_name = C_PER THEN
1683 OPEN c_lock_per_records(l_work_unit_number);
1684 CLOSE c_lock_per_records;
1685
1686 END IF;
1687
1688 EXCEPTION
1689 WHEN resource_busy THEN
1690 GOTO next_fetch;
1691 END;
1692
1693 -- update status to 'P' for processing
1694 --
1695 update_staging_status(p_entity_name, l_work_unit_number, 'P');
1696 Write_Log(SQL%ROWCOUNT||' records have been locked.');
1697
1698 COMMIT;
1699
1700 -- insert into extension tables
1701 --
1702 BEGIN
1703 IF p_entity_name = C_ORG THEN
1704 copy_org_extension(
1705 p_parent_request_id, l_work_unit_number,
1706 l_created_by, l_last_updated_by, l_last_update_login);
1707
1708 ELSIF p_entity_name = C_PER THEN
1709 copy_per_extension(
1710 p_parent_request_id, l_work_unit_number,
1711 l_created_by, l_last_updated_by, l_last_update_login);
1712
1713 END IF;
1714
1715 -- set status to 'C' for complete
1716 --
1717 update_staging_status(p_entity_name, l_work_unit_number, 'C');
1718
1719 EXCEPTION
1720 WHEN OTHERS THEN
1721 -- stop the processing
1722 --
1723 retcode := 2;
1724 RETURN;
1725 END;
1726
1727 COMMIT;
1728
1729 END LOOP;
1730
1731 EXCEPTION
1732 WHEN OTHERS THEN
1733 retcode := 2;
1734
1735 END copy_conc_sub;
1736
1737
1738 /**
1739 * PUBLIC PROCEDURE copy_conc_main
1740 *
1741 * DESCRIPTION
1742 * Main program to copy extension data
1743 *
1744 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1745 *
1746 * IN:
1750 *
1747 * p_entity_name Entity Name
1748 * p_batch_size Batch Size
1749 * p_number_of_worker Number of Worker
1751 * MODIFICATION HISTORY
1752 *
1753 * 03-15-2005 Jianying Huang o Created.
1754 */
1755
1756 PROCEDURE copy_conc_main (
1757 errbuf OUT NOCOPY VARCHAR2,
1758 retcode OUT NOCOPY VARCHAR2,
1759 p_entity_name IN VARCHAR2,
1760 p_batch_size IN NUMBER,
1761 p_number_of_worker IN NUMBER
1762 ) IS
1763
1764 CURSOR c_org_extension_exists IS
1765 SELECT 'Y'
1766 FROM hz_org_profiles_ext_b
1767 WHERE rownum = 1;
1768
1769 CURSOR c_per_extension_exists IS
1770 SELECT 'Y'
1771 FROM hz_per_profiles_ext_b
1772 WHERE rownum = 1;
1773
1774 l_owner VARCHAR2(100);
1775 l_do_copy VARCHAR2(1);
1776 l_batch_size NUMBER;
1777 l_number_of_worker NUMBER;
1778 l_total NUMBER;
1779 l_parent_request_id NUMBER;
1780 l_sub_conc_program VARCHAR2(30);
1781 l_sub_request_id NUMBER;
1782 l_sql VARCHAR2(200);
1783
1784 BEGIN
1785
1786 retcode := 0;
1787
1788 -- return if no history tracking
1789 --
1790 IF fnd_profile.value('HZ_PROFILE_VERSION') = 'NO_VERSION' THEN
1791 Write_Log('Profile HZ_PROFILE_VERSION has NO_VERSION.');
1792 Write_Log('Quit. No records need to be processed.');
1793 RETURN;
1794 END IF;
1795
1796 -- return if extension table is empty
1797 --
1798 IF p_entity_name = C_ORG THEN
1799 OPEN c_org_extension_exists;
1800 FETCH c_org_extension_exists INTO l_do_copy;
1801 IF c_org_extension_exists%NOTFOUND THEN
1802 l_do_copy := 'N';
1803 END IF;
1804 CLOSE c_org_extension_exists;
1805
1806 ELSIF p_entity_name = C_PER THEN
1807 OPEN c_per_extension_exists;
1808 FETCH c_per_extension_exists INTO l_do_copy;
1809 IF c_per_extension_exists%NOTFOUND THEN
1810 l_do_copy := 'N';
1811 END IF;
1812 CLOSE c_per_extension_exists;
1813
1814 END IF;
1815
1816 IF l_do_copy = 'N' THEN
1817 Write_Log('No records in extension table. Quit.');
1818 RETURN;
1819 END IF;
1820
1821 -- truncate staging table
1822 --
1823 Write_Log('Truncating staging table ...');
1824 l_owner := hz_utility_v2pub.Get_SchemaName('AR');
1825 Write_Log('l_owner = '||l_owner);
1826
1827 l_sql := 'truncate table '||l_owner||'.';
1828 IF p_entity_name = C_ORG THEN
1829 l_sql := l_sql||'HZ_ORG_PROFILES_EXT_SG';
1830 ELSE
1831 l_sql := l_sql||'HZ_PER_PROFILES_EXT_SG';
1832 END IF;
1833 execute immediate l_sql;
1834
1835 -- validate parameters
1836 --
1837 IF (p_batch_size IS NULL OR p_batch_size < 1000) THEN
1838 l_batch_size := 1000;
1839 ELSE
1840 l_batch_size := p_batch_size;
1841 END IF;
1842 Write_Log('p_batch_size = '||p_batch_size);
1843 Write_Log('l_batch_size = '||l_batch_size);
1844
1845 IF (p_number_of_worker IS NULL OR p_number_of_worker < 1) THEN
1846 l_number_of_worker := 1;
1847 ELSE
1848 l_number_of_worker := p_number_of_worker;
1849 END IF;
1850 Write_Log('p_number_of_worker = '||p_number_of_worker);
1851 Write_Log('l_number_of_worker = '||l_number_of_worker);
1852
1853 -- fetch records need to be processed into staging table and
1854 -- split the staging table into multiple segments based on batch size
1855 --
1856 Write_Log('Populate staging table ...');
1857 populate_staging_table(p_entity_name, l_batch_size, l_total);
1858 Write_Log('l_total = '||l_total);
1859
1860 IF (l_total = 0) THEN
1861 Write_Log('No records in staging table. Quit.');
1862 RETURN;
1863 END IF;
1864
1865 -- get parent request id
1866 --
1867 l_parent_request_id := hz_utility_v2pub.request_id;
1868 Write_Log('l_parent_request_id = '||l_parent_request_id);
1869
1870 -- submit sub requests
1871 --
1872 IF (l_number_of_worker = 1) THEN
1873 copy_conc_sub(errbuf, retcode, p_entity_name, l_parent_request_id);
1874 ELSE
1875 FOR i IN 1..l_number_of_worker LOOP
1876 IF p_entity_name = C_ORG THEN
1877 l_sub_conc_program := 'ARHCOEXS';
1878 ELSIF p_entity_name = C_PER THEN
1879 l_sub_conc_program := 'ARHCPEXS';
1880 END IF;
1881
1882 l_sub_request_id :=
1883 FND_REQUEST.SUBMIT_REQUEST(
1884 'AR', l_sub_conc_program, '',
1885 SYSDATE, FALSE,
1886 TO_CHAR(l_parent_request_id));
1887
1888 IF l_sub_request_id = 0 THEN
1889 Write_Log('Failed to submit concurrent request.');
1890 retcode := 2;
1891 RETURN;
1892 ELSE
1893 Write_Log('l_sub_request_id = '||l_sub_request_id);
1894 END IF;
1895 END LOOP;
1896 END IF;
1897
1898 EXCEPTION
1899 WHEN OTHERS THEN
1900 retcode := 2;
1901
1902 END copy_conc_main;
1903
1904
1905 /**
1906 * PUBLIC PROCEDURE copy_org_conc_main
1907 *
1908 * DESCRIPTION
1909 * Main concurrent program to copy organization extension data
1910 *
1911 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1912 *
1913 * IN:
1914 * p_batch_size Batch Size
1918 *
1915 * p_number_of_worker Number of Worker
1916 *
1917 * MODIFICATION HISTORY
1919 * 03-15-2005 Jianying Huang o Created.
1920 */
1921
1922 PROCEDURE copy_org_conc_main (
1923 errbuf OUT NOCOPY VARCHAR2,
1924 retcode OUT NOCOPY VARCHAR2,
1925 p_batch_size IN NUMBER,
1926 p_number_of_worker IN NUMBER
1927 ) IS
1928 BEGIN
1929
1930 copy_conc_main (
1931 errbuf, retcode,
1932 C_ORG,
1933 p_batch_size,
1934 p_number_of_worker
1935 );
1936
1937 END copy_org_conc_main;
1938
1939
1940 /**
1941 * PUBLIC PROCEDURE copy_per_conc_main
1942 *
1943 * DESCRIPTION
1944 * Main concurrent program to copy person extension data
1945 *
1946 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1947 *
1948 * IN:
1949 * p_batch_size Batch Size
1950 * p_number_of_worker Number of Worker
1951 *
1952 * MODIFICATION HISTORY
1953 *
1954 * 03-15-2005 Jianying Huang o Created.
1955 */
1956
1957 PROCEDURE copy_per_conc_main (
1958 errbuf OUT NOCOPY VARCHAR2,
1959 retcode OUT NOCOPY VARCHAR2,
1960 p_batch_size IN NUMBER,
1961 p_number_of_worker IN NUMBER
1962 ) IS
1963 BEGIN
1964
1965 copy_conc_main (
1966 errbuf, retcode,
1967 C_PER,
1968 p_batch_size,
1969 p_number_of_worker
1970 );
1971
1972 END copy_per_conc_main;
1973
1974
1975 /**
1976 * PUBLIC PROCEDURE copy_org_conc_sub
1977 *
1978 * DESCRIPTION
1979 * Sub concurrent program to copy organization extension data
1980 *
1981 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1982 *
1983 * IN:
1984 * p_parent_request_id Parent Request ID
1985 *
1986 * MODIFICATION HISTORY
1987 *
1988 * 03-15-2005 Jianying Huang o Created.
1989 */
1990
1991 PROCEDURE copy_org_conc_sub (
1992 errbuf OUT NOCOPY VARCHAR2,
1993 retcode OUT NOCOPY VARCHAR2,
1994 p_parent_request_id IN NUMBER
1995 ) IS
1996 BEGIN
1997
1998 copy_conc_sub (
1999 errbuf, retcode,
2000 C_ORG,
2001 p_parent_request_id
2002 );
2003
2004 END copy_org_conc_sub;
2005
2006
2007 /**
2008 * PUBLIC PROCEDURE copy_per_conc_sub
2009 *
2010 * DESCRIPTION
2011 * Sub concurrent program to copy person extension data
2012 *
2013 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2014 *
2015 * IN:
2016 * p_parent_request_id Parent Request ID
2017 *
2018 * MODIFICATION HISTORY
2019 *
2020 * 03-15-2005 Jianying Huang o Created.
2021 */
2022
2023 PROCEDURE copy_per_conc_sub (
2024 errbuf OUT NOCOPY VARCHAR2,
2025 retcode OUT NOCOPY VARCHAR2,
2026 p_parent_request_id IN NUMBER
2027 ) IS
2028 BEGIN
2029
2030 copy_conc_sub (
2031 errbuf, retcode,
2032 C_PER,
2033 p_parent_request_id
2034 );
2035
2036 END copy_per_conc_sub;
2037
2038 END HZ_EXTENSIBILITY_PVT;