[Home] [Help]
PACKAGE BODY: APPS.HZ_EXTENSIBILITY_PVT
Source
1 PACKAGE BODY HZ_EXTENSIBILITY_PVT AS
2 /* $Header: ARHEXTCB.pls 120.4.12010000.2 2010/03/24 08:44:36 rgokavar ship $ */
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 --
292 ---------------------------------------------------------------
293 EGO_USER_ATTRS_DATA_PUB.Get_User_Attrs_Data(
294 p_api_version => p_api_version
295 ,p_object_name => 'EGO_ITEM'
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;
433 l_last_update_login := hz_utility_v2pub.last_update_login;
434 l_last_updated_by := hz_utility_v2pub.last_updated_by;
435
436 -- This code will copy the existing profile extension records over
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,
627 tl.tl_ext_attr19,
628 tl.tl_ext_attr20
629 FROM HZ_PER_PROFILES_EXT_B b,
630 HZ_PER_PROFILES_EXT_TL tl
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,
805 n_ext_attr13,
806 n_ext_attr14,
807 n_ext_attr15,
808 n_ext_attr16,
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 * 03-15-2010 Sudhir Gokavarapu Added Content Source Security
949 * Disable and Enable statements to process other profiles
950 * Instead of only SST records.
951 * Considering only SST and USER_ENTERED profiles to avoid all other ACS types.
952 */
953
954 PROCEDURE populate_staging_table (
955 p_entity_name IN VARCHAR2,
956 p_batch_size IN NUMBER,
957 x_total OUT NOCOPY NUMBER
958 ) IS
959
960 BEGIN
961 hz_common_pub.disable_cont_source_security;
962 IF p_entity_name = C_ORG THEN
963 -- insert into org staging table
964 --
965 INSERT INTO hz_org_profiles_ext_sg (
966 old_profile_id,
967 new_profile_id,
968 work_unit_number,
969 status
970 )
971 SELECT old_profile_id,
972 new_profile_id,
973 round(ROWNUM/p_batch_size + 0.5) work_unit_number,
974 'N' status
975 FROM (
976 SELECT /*+ parallel(p1) parallel(p2) */
977 max(p2.organization_profile_id) old_profile_id, p1.organization_profile_id new_profile_id
978 FROM hz_organization_profiles p1,
979 hz_organization_profiles p2
980 WHERE p1.effective_end_date is null
981 AND p1.actual_content_source IN ('SST','USER_ENTERED')
982 AND p2.actual_content_source IN ('SST','USER_ENTERED')
983 AND NOT EXISTS (
984 SELECT null
985 FROM hz_org_profiles_ext_b ext
986 WHERE p1.organization_profile_id = ext.organization_profile_id)
987 AND p2.party_id = p1.party_id
988 AND p2.organization_profile_id <> p1.organization_profile_id
989 AND EXISTS (
990 SELECT null
991 FROM hz_org_profiles_ext_b ext
992 WHERE p2.organization_profile_id = ext.organization_profile_id)
993 GROUP BY p1.organization_profile_id);
994
995 ELSIF p_entity_name = C_PER THEN
996 -- insert into person staging table
997 --
998 INSERT INTO hz_per_profiles_ext_sg (
999 old_profile_id,
1000 new_profile_id,
1001 work_unit_number,
1002 status
1003 )
1004 SELECT old_profile_id,
1005 new_profile_id,
1006 round(ROWNUM/p_batch_size + 0.5) work_unit_number,
1007 'N' status
1008 FROM (
1009 SELECT /*+ parallel(p1) parallel(p2) */
1010 max(p2.person_profile_id) old_profile_id, p1.person_profile_id new_profile_id
1011 FROM hz_person_profiles p1,
1012 hz_person_profiles p2
1013 WHERE p1.effective_end_date is null
1014 AND p1.actual_content_source IN ('SST','USER_ENTERED')
1015 AND p2.actual_content_source IN ('SST','USER_ENTERED')
1016 AND NOT EXISTS (
1017 SELECT null
1018 FROM hz_per_profiles_ext_b ext
1019 WHERE p1.person_profile_id = ext.person_profile_id)
1020 AND p2.party_id = p1.party_id
1021 AND p2.person_profile_id <> p1.person_profile_id
1022 AND EXISTS (
1023 SELECT null
1024 FROM hz_per_profiles_ext_b ext
1025 WHERE p2.person_profile_id = ext.person_profile_id)
1026 GROUP BY p1.person_profile_id);
1027
1028 END IF;
1029
1030 x_total := SQL%ROWCOUNT;
1031
1032 COMMIT;
1033 hz_common_pub.enable_cont_source_security;
1034 END populate_staging_table;
1035
1036
1037 /**
1038 * PRIVATE PROCEDURE update_staging_status
1039 *
1040 * DESCRIPTION
1041 * Update staging table status column
1042 *
1043 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1044 *
1045 * IN:
1046 * p_entity_name Entity Name
1047 * p_work_unit_number Work Unit Number
1048 * p_status Status
1049 *
1050 * MODIFICATION HISTORY
1051 *
1052 * 03-15-2005 Jianying Huang o Created.
1053 */
1054
1055 PROCEDURE update_staging_status (
1056 p_entity_name IN VARCHAR2,
1057 p_work_unit_number IN NUMBER,
1058 p_status IN VARCHAR2
1059 ) IS
1060 BEGIN
1061
1062 IF p_entity_name = C_ORG THEN
1063 -- update org staging table
1064 --
1065 UPDATE hz_org_profiles_ext_sg
1066 SET status = p_status
1067 WHERE work_unit_number = p_work_unit_number;
1068
1069 ELSIF p_entity_name = C_PER THEN
1070 -- update per staging table
1071 --
1072 UPDATE hz_per_profiles_ext_sg
1073 SET status = p_status
1074 WHERE work_unit_number = p_work_unit_number;
1075
1076 END IF;
1077
1078 END update_staging_status;
1079
1080
1081 /**
1082 * PRIVATE PROCEDURE copy_org_extension
1083 *
1084 * DESCRIPTION
1085 * Copy organization extension data
1086 *
1087 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1088 *
1089 * IN:
1090 * p_parent_request_id Parent Request ID
1091 * p_work_unit_number Work Unit Number
1092 * p_created_by Created By
1093 * p_last_updated_by Last Updated By
1094 * p_last_update_login Last Update Login
1095 *
1096 * MODIFICATION HISTORY
1097 *
1098 * 03-15-2005 Jianying Huang o Created.
1099 */
1100
1101 PROCEDURE copy_org_extension (
1102 p_parent_request_id IN NUMBER,
1103 p_work_unit_number IN NUMBER,
1104 p_created_by IN NUMBER,
1105 p_last_updated_by IN NUMBER,
1106 p_last_update_login IN NUMBER
1107 ) IS
1108 BEGIN
1109
1110 -- insert into _b table
1111 --
1112 INSERT INTO hz_org_profiles_ext_b ext (
1113 extension_id,
1114 organization_profile_id,
1115 attr_group_id,
1116 created_by,
1117 creation_date,
1118 last_updated_by,
1119 last_update_date,
1120 last_update_login,
1121 c_ext_attr1,
1122 c_ext_attr2,
1123 c_ext_attr3,
1124 c_ext_attr4,
1125 c_ext_attr5,
1126 c_ext_attr6,
1127 c_ext_attr7,
1128 c_ext_attr8,
1129 c_ext_attr9,
1130 c_ext_attr10,
1131 c_ext_attr11,
1132 c_ext_attr12,
1133 c_ext_attr13,
1134 c_ext_attr14,
1135 c_ext_attr15,
1136 c_ext_attr16,
1137 c_ext_attr17,
1138 c_ext_attr18,
1139 c_ext_attr19,
1140 c_ext_attr20,
1141 n_ext_attr1,
1142 n_ext_attr2,
1143 n_ext_attr3,
1144 n_ext_attr4,
1145 n_ext_attr5,
1146 n_ext_attr6,
1147 n_ext_attr7,
1148 n_ext_attr8,
1149 n_ext_attr9,
1150 n_ext_attr10,
1151 n_ext_attr11,
1152 n_ext_attr12,
1153 n_ext_attr13,
1154 n_ext_attr14,
1155 n_ext_attr15,
1156 n_ext_attr16,
1157 n_ext_attr17,
1158 n_ext_attr18,
1159 n_ext_attr19,
1160 n_ext_attr20,
1161 d_ext_attr1,
1162 d_ext_attr2,
1163 d_ext_attr3,
1164 d_ext_attr4,
1165 d_ext_attr5,
1166 d_ext_attr6,
1167 d_ext_attr7,
1168 d_ext_attr8,
1169 d_ext_attr9,
1170 d_ext_attr10,
1171 old_extension_id )
1172 SELECT
1173 ego_extfwk_s.nextval,
1174 sg.new_profile_id,
1175 attr_group_id,
1176 p_created_by,
1177 SYSDATE,
1178 p_last_updated_by,
1179 SYSDATE,
1180 p_last_update_login,
1181 c_ext_attr1,
1182 c_ext_attr2,
1183 c_ext_attr3,
1184 c_ext_attr4,
1185 c_ext_attr5,
1186 c_ext_attr6,
1187 c_ext_attr7,
1188 c_ext_attr8,
1189 c_ext_attr9,
1190 c_ext_attr10,
1191 c_ext_attr11,
1192 c_ext_attr12,
1193 c_ext_attr13,
1194 c_ext_attr14,
1195 c_ext_attr15,
1196 c_ext_attr16,
1197 c_ext_attr17,
1198 c_ext_attr18,
1199 c_ext_attr19,
1200 c_ext_attr20,
1201 n_ext_attr1,
1202 n_ext_attr2,
1203 n_ext_attr3,
1204 n_ext_attr4,
1205 n_ext_attr5,
1206 n_ext_attr6,
1207 n_ext_attr7,
1208 n_ext_attr8,
1209 n_ext_attr9,
1210 n_ext_attr10,
1211 n_ext_attr11,
1212 n_ext_attr12,
1213 n_ext_attr13,
1214 n_ext_attr14,
1215 n_ext_attr15,
1216 n_ext_attr16,
1217 n_ext_attr17,
1218 n_ext_attr18,
1219 n_ext_attr19,
1220 n_ext_attr20,
1221 d_ext_attr1,
1222 d_ext_attr2,
1223 d_ext_attr3,
1224 d_ext_attr4,
1225 d_ext_attr5,
1226 d_ext_attr6,
1227 d_ext_attr7,
1228 d_ext_attr8,
1229 d_ext_attr9,
1230 d_ext_attr10,
1231 extension_id
1232 FROM hz_org_profiles_ext_b b,
1233 hz_org_profiles_ext_sg sg
1234 WHERE
1235 sg.work_unit_number = p_work_unit_number
1236 AND b.organization_profile_id = sg.old_profile_id;
1237
1238 Write_Log(SQL%ROWCOUNT||' records inserted into org base table.');
1239
1240 IF (SQL%ROWCOUNT IS NULL) OR (SQL%ROWCOUNT = 0) THEN
1241 RETURN;
1242 END IF;
1243
1244 -- gather table statistics
1245 --
1246 fnd_stats.gather_table_stats('AR', 'HZ_ORG_PROFILES_EXT_B');
1247
1248 -- insert into _tl table
1249 --
1250 INSERT INTO hz_org_profiles_ext_tl ext (
1251 extension_id,
1252 organization_profile_id,
1253 attr_group_id,
1254 source_lang,
1255 language,
1256 created_by,
1257 creation_date,
1258 last_updated_by,
1259 last_update_date,
1260 last_update_login,
1261 tl_ext_attr1,
1262 tl_ext_attr2,
1263 tl_ext_attr3,
1264 tl_ext_attr4,
1265 tl_ext_attr5,
1266 tl_ext_attr6,
1267 tl_ext_attr7,
1268 tl_ext_attr8,
1269 tl_ext_attr9,
1270 tl_ext_attr10,
1271 tl_ext_attr11,
1272 tl_ext_attr12,
1273 tl_ext_attr13,
1274 tl_ext_attr14,
1275 tl_ext_attr15,
1276 tl_ext_attr16,
1277 tl_ext_attr17,
1278 tl_ext_attr18,
1279 tl_ext_attr19,
1280 tl_ext_attr20
1281 )
1282 SELECT
1283 b.extension_id,
1284 sg.new_profile_id,
1285 tl.attr_group_id,
1286 tl.source_lang,
1287 tl.language,
1288 p_created_by,
1289 SYSDATE,
1290 p_last_updated_by,
1291 SYSDATE,
1292 p_last_update_login,
1293 tl.tl_ext_attr1,
1294 tl.tl_ext_attr2,
1295 tl.tl_ext_attr3,
1296 tl.tl_ext_attr4,
1297 tl.tl_ext_attr5,
1298 tl.tl_ext_attr6,
1299 tl.tl_ext_attr7,
1300 tl.tl_ext_attr8,
1301 tl.tl_ext_attr9,
1302 tl.tl_ext_attr10,
1303 tl.tl_ext_attr11,
1304 tl.tl_ext_attr12,
1305 tl.tl_ext_attr13,
1306 tl.tl_ext_attr14,
1307 tl.tl_ext_attr15,
1308 tl.tl_ext_attr16,
1309 tl.tl_ext_attr17,
1310 tl.tl_ext_attr18,
1311 tl.tl_ext_attr19,
1312 tl.tl_ext_attr20
1313 FROM hz_org_profiles_ext_b b,
1314 hz_org_profiles_ext_tl tl,
1315 hz_org_profiles_ext_sg sg
1316 WHERE
1317 sg.work_unit_number = p_work_unit_number
1318 AND b.organization_profile_id = sg.new_profile_id
1319 AND tl.extension_id = b.old_extension_id;
1320
1321 Write_Log(SQL%ROWCOUNT||' records inserted into org tl table.');
1322
1323 -- gather table statistics
1324 --
1325 fnd_stats.gather_table_stats('AR', 'HZ_ORG_PROFILES_EXT_TL');
1326
1327 END copy_org_extension;
1328
1329
1330 /**
1331 * PRIVATE PROCEDURE copy_per_extension
1332 *
1333 * DESCRIPTION
1334 * Copy person extension data
1335 *
1336 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1337 *
1338 * IN:
1339 * p_parent_request_id Parent Request ID
1340 * p_work_unit_number Work Unit Number
1341 * p_created_by Created By
1342 * p_last_updated_by Last Updated By
1343 * p_last_update_login Last Update Login
1344 *
1345 * MODIFICATION HISTORY
1346 *
1347 * 03-15-2005 Jianying Huang o Created.
1348 */
1349
1350 PROCEDURE copy_per_extension (
1351 p_parent_request_id IN NUMBER,
1352 p_work_unit_number IN NUMBER,
1353 p_created_by IN NUMBER,
1354 p_last_updated_by IN NUMBER,
1355 p_last_update_login IN NUMBER
1356 ) IS
1357 BEGIN
1358
1359 -- insert into _b table
1360 --
1361 INSERT INTO hz_per_profiles_ext_b ext (
1362 extension_id,
1363 person_profile_id,
1364 attr_group_id,
1365 created_by,
1366 creation_date,
1367 last_updated_by,
1368 last_update_date,
1369 last_update_login,
1370 c_ext_attr1,
1371 c_ext_attr2,
1372 c_ext_attr3,
1373 c_ext_attr4,
1374 c_ext_attr5,
1375 c_ext_attr6,
1376 c_ext_attr7,
1377 c_ext_attr8,
1378 c_ext_attr9,
1379 c_ext_attr10,
1380 c_ext_attr11,
1381 c_ext_attr12,
1382 c_ext_attr13,
1383 c_ext_attr14,
1384 c_ext_attr15,
1385 c_ext_attr16,
1386 c_ext_attr17,
1387 c_ext_attr18,
1388 c_ext_attr19,
1389 c_ext_attr20,
1390 n_ext_attr1,
1391 n_ext_attr2,
1392 n_ext_attr3,
1393 n_ext_attr4,
1394 n_ext_attr5,
1395 n_ext_attr6,
1396 n_ext_attr7,
1397 n_ext_attr8,
1398 n_ext_attr9,
1399 n_ext_attr10,
1400 n_ext_attr11,
1401 n_ext_attr12,
1402 n_ext_attr13,
1403 n_ext_attr14,
1404 n_ext_attr15,
1405 n_ext_attr16,
1406 n_ext_attr17,
1407 n_ext_attr18,
1408 n_ext_attr19,
1409 n_ext_attr20,
1410 d_ext_attr1,
1411 d_ext_attr2,
1412 d_ext_attr3,
1413 d_ext_attr4,
1414 d_ext_attr5,
1415 d_ext_attr6,
1416 d_ext_attr7,
1417 d_ext_attr8,
1418 d_ext_attr9,
1419 d_ext_attr10,
1420 old_extension_id )
1421 SELECT
1422 ego_extfwk_s.nextval,
1423 sg.new_profile_id,
1424 attr_group_id,
1425 p_created_by,
1426 SYSDATE,
1427 p_last_updated_by,
1428 SYSDATE,
1429 p_last_update_login,
1430 c_ext_attr1,
1431 c_ext_attr2,
1432 c_ext_attr3,
1433 c_ext_attr4,
1434 c_ext_attr5,
1435 c_ext_attr6,
1436 c_ext_attr7,
1437 c_ext_attr8,
1438 c_ext_attr9,
1439 c_ext_attr10,
1440 c_ext_attr11,
1441 c_ext_attr12,
1442 c_ext_attr13,
1443 c_ext_attr14,
1444 c_ext_attr15,
1445 c_ext_attr16,
1446 c_ext_attr17,
1447 c_ext_attr18,
1448 c_ext_attr19,
1449 c_ext_attr20,
1450 n_ext_attr1,
1451 n_ext_attr2,
1452 n_ext_attr3,
1453 n_ext_attr4,
1454 n_ext_attr5,
1455 n_ext_attr6,
1456 n_ext_attr7,
1457 n_ext_attr8,
1458 n_ext_attr9,
1459 n_ext_attr10,
1460 n_ext_attr11,
1461 n_ext_attr12,
1462 n_ext_attr13,
1463 n_ext_attr14,
1464 n_ext_attr15,
1465 n_ext_attr16,
1466 n_ext_attr17,
1467 n_ext_attr18,
1468 n_ext_attr19,
1469 n_ext_attr20,
1470 d_ext_attr1,
1471 d_ext_attr2,
1472 d_ext_attr3,
1473 d_ext_attr4,
1474 d_ext_attr5,
1475 d_ext_attr6,
1476 d_ext_attr7,
1477 d_ext_attr8,
1478 d_ext_attr9,
1479 d_ext_attr10,
1480 extension_id
1481 FROM hz_per_profiles_ext_b b,
1482 hz_per_profiles_ext_sg sg
1483 WHERE
1484 sg.work_unit_number = p_work_unit_number
1485 AND b.person_profile_id = sg.old_profile_id;
1486
1487 Write_Log(SQL%ROWCOUNT||' records inserted into per base table.');
1488
1489 IF (SQL%ROWCOUNT IS NULL) OR (SQL%ROWCOUNT = 0) THEN
1490 RETURN;
1491 END IF;
1492
1493 -- gather table statistics
1494 --
1495 fnd_stats.gather_table_stats('AR', 'HZ_PER_PROFILES_EXT_B');
1496
1497 -- insert into _tl table
1498 --
1499 INSERT INTO hz_per_profiles_ext_tl ext (
1500 extension_id,
1501 person_profile_id,
1502 attr_group_id,
1503 source_lang,
1504 language,
1505 created_by,
1506 creation_date,
1507 last_updated_by,
1508 last_update_date,
1509 last_update_login,
1510 tl_ext_attr1,
1511 tl_ext_attr2,
1512 tl_ext_attr3,
1513 tl_ext_attr4,
1514 tl_ext_attr5,
1515 tl_ext_attr6,
1516 tl_ext_attr7,
1517 tl_ext_attr8,
1518 tl_ext_attr9,
1519 tl_ext_attr10,
1520 tl_ext_attr11,
1521 tl_ext_attr12,
1522 tl_ext_attr13,
1523 tl_ext_attr14,
1524 tl_ext_attr15,
1525 tl_ext_attr16,
1526 tl_ext_attr17,
1527 tl_ext_attr18,
1528 tl_ext_attr19,
1529 tl_ext_attr20
1530 )
1531 SELECT
1532 b.extension_id,
1533 sg.new_profile_id,
1534 tl.attr_group_id,
1535 tl.source_lang,
1536 tl.language,
1537 p_created_by,
1538 SYSDATE,
1539 p_last_updated_by,
1540 SYSDATE,
1541 p_last_update_login,
1542 tl.tl_ext_attr1,
1543 tl.tl_ext_attr2,
1544 tl.tl_ext_attr3,
1545 tl.tl_ext_attr4,
1546 tl.tl_ext_attr5,
1547 tl.tl_ext_attr6,
1548 tl.tl_ext_attr7,
1549 tl.tl_ext_attr8,
1550 tl.tl_ext_attr9,
1551 tl.tl_ext_attr10,
1552 tl.tl_ext_attr11,
1553 tl.tl_ext_attr12,
1554 tl.tl_ext_attr13,
1555 tl.tl_ext_attr14,
1556 tl.tl_ext_attr15,
1557 tl.tl_ext_attr16,
1558 tl.tl_ext_attr17,
1559 tl.tl_ext_attr18,
1560 tl.tl_ext_attr19,
1561 tl.tl_ext_attr20
1562 FROM hz_per_profiles_ext_b b,
1563 hz_per_profiles_ext_tl tl,
1564 hz_per_profiles_ext_sg sg
1565 WHERE
1566 sg.work_unit_number = p_work_unit_number
1567 AND b.person_profile_id = sg.new_profile_id
1568 AND tl.extension_id = b.old_extension_id;
1569
1570 Write_Log(SQL%ROWCOUNT||' records inserted into per tl table.');
1571
1572 -- gather table statistics
1573 --
1574 fnd_stats.gather_table_stats('AR', 'HZ_PER_PROFILES_EXT_TL');
1575
1576 END copy_per_extension;
1577
1578
1579 /**
1580 * PUBLIC PROCEDURE copy_conc_sub
1581 *
1582 * DESCRIPTION
1583 * Sub concurrent program to copy org extension data
1584 *
1585 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1586 *
1587 * IN:
1588 * p_parent_request_id Parent Request ID
1589 *
1590 * MODIFICATION HISTORY
1591 *
1592 * 03-15-2005 Jianying Huang o Created.
1593 */
1594
1595 PROCEDURE copy_conc_sub (
1596 errbuf OUT NOCOPY VARCHAR2,
1597 retcode OUT NOCOPY VARCHAR2,
1598 p_entity_name IN VARCHAR2,
1599 p_parent_request_id IN NUMBER
1600 ) IS
1601
1602 CURSOR c_org_work_unit IS
1603 SELECT work_unit_number
1604 FROM hz_org_profiles_ext_sg
1605 WHERE status = 'N'
1606 AND ROWNUM =1;
1607
1608 CURSOR c_per_work_unit IS
1609 SELECT work_unit_number
1610 FROM hz_per_profiles_ext_sg
1611 WHERE status = 'N'
1612 AND ROWNUM =1;
1613
1614 CURSOR c_lock_org_records (
1615 p_work_unit_number NUMBER
1616 ) IS
1617 SELECT *
1618 FROM hz_org_profiles_ext_sg
1619 WHERE status = 'N'
1620 AND work_unit_number = p_work_unit_number
1621 FOR UPDATE NOWAIT;
1622
1623 CURSOR c_lock_per_records (
1624 p_work_unit_number NUMBER
1625 ) IS
1626 SELECT *
1627 FROM hz_per_profiles_ext_sg
1628 WHERE status = 'N'
1629 AND work_unit_number = p_work_unit_number
1630 FOR UPDATE NOWAIT;
1631
1632 resource_busy EXCEPTION;
1633 PRAGMA EXCEPTION_INIT(resource_busy, -54);
1634
1635 l_created_by NUMBER;
1636 l_last_updated_by NUMBER;
1637 l_last_update_login NUMBER;
1638 l_work_unit_number NUMBER;
1639
1640 BEGIN
1641
1642 retcode := 0;
1643
1644 -- retrieve who information
1645 --
1646 l_created_by := hz_utility_v2pub.created_by;
1647 l_last_update_login := hz_utility_v2pub.last_update_login;
1648 l_last_updated_by := hz_utility_v2pub.last_updated_by;
1649
1650 -- find out if there is any work units need to be processed
1651 --
1652 LOOP
1653
1654 <<next_fetch>>
1655
1656 -- get work unit number
1657 --
1658 IF p_entity_name = C_ORG THEN
1659 OPEN c_org_work_unit;
1660 FETCH c_org_work_unit INTO l_work_unit_number;
1661 IF c_org_work_unit%NOTFOUND THEN
1662 l_work_unit_number := 0;
1663 END IF;
1664 CLOSE c_org_work_unit;
1665
1666 ELSIF p_entity_name = C_PER THEN
1667 OPEN c_per_work_unit;
1668 FETCH c_per_work_unit INTO l_work_unit_number;
1669 IF c_per_work_unit%NOTFOUND THEN
1670 l_work_unit_number := 0;
1671 END IF;
1672 CLOSE c_per_work_unit;
1673
1674 END IF;
1675
1676 IF l_work_unit_number = 0 THEN
1677 Write_Log('No more records need to be processed. Quit.');
1678 RETURN;
1679 END IF;
1680
1681 Write_Log('l_work_unit_number = '||l_work_unit_number);
1682
1683 -- lock records
1684 --
1685 BEGIN
1686 IF p_entity_name = C_ORG THEN
1687 OPEN c_lock_org_records(l_work_unit_number);
1688 CLOSE c_lock_org_records;
1689
1690 ELSIF p_entity_name = C_PER THEN
1691 OPEN c_lock_per_records(l_work_unit_number);
1692 CLOSE c_lock_per_records;
1693
1694 END IF;
1695
1696 EXCEPTION
1697 WHEN resource_busy THEN
1698 GOTO next_fetch;
1699 END;
1700
1701 -- update status to 'P' for processing
1702 --
1703 update_staging_status(p_entity_name, l_work_unit_number, 'P');
1704 Write_Log(SQL%ROWCOUNT||' records have been locked.');
1705
1706 COMMIT;
1707
1708 -- insert into extension tables
1709 --
1710 BEGIN
1711 IF p_entity_name = C_ORG THEN
1712 copy_org_extension(
1713 p_parent_request_id, l_work_unit_number,
1714 l_created_by, l_last_updated_by, l_last_update_login);
1715
1716 ELSIF p_entity_name = C_PER THEN
1717 copy_per_extension(
1718 p_parent_request_id, l_work_unit_number,
1719 l_created_by, l_last_updated_by, l_last_update_login);
1720
1721 END IF;
1722
1723 -- set status to 'C' for complete
1724 --
1725 update_staging_status(p_entity_name, l_work_unit_number, 'C');
1726
1727 EXCEPTION
1728 WHEN OTHERS THEN
1729 -- stop the processing
1730 --
1731 retcode := 2;
1732 RETURN;
1733 END;
1734
1735 COMMIT;
1736
1737 END LOOP;
1738
1739 EXCEPTION
1740 WHEN OTHERS THEN
1741 retcode := 2;
1742
1743 END copy_conc_sub;
1744
1745
1746 /**
1747 * PUBLIC PROCEDURE copy_conc_main
1748 *
1749 * DESCRIPTION
1750 * Main program to copy extension data
1751 *
1752 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1753 *
1754 * IN:
1755 * p_entity_name Entity Name
1756 * p_batch_size Batch Size
1757 * p_number_of_worker Number of Worker
1758 *
1759 * MODIFICATION HISTORY
1760 *
1761 * 03-15-2005 Jianying Huang o Created.
1762 */
1763
1764 PROCEDURE copy_conc_main (
1765 errbuf OUT NOCOPY VARCHAR2,
1766 retcode OUT NOCOPY VARCHAR2,
1767 p_entity_name IN VARCHAR2,
1768 p_batch_size IN NUMBER,
1769 p_number_of_worker IN NUMBER
1770 ) IS
1771
1772 CURSOR c_org_extension_exists IS
1773 SELECT 'Y'
1774 FROM hz_org_profiles_ext_b
1775 WHERE rownum = 1;
1776
1777 CURSOR c_per_extension_exists IS
1778 SELECT 'Y'
1779 FROM hz_per_profiles_ext_b
1780 WHERE rownum = 1;
1781
1782 l_owner VARCHAR2(100);
1783 l_do_copy VARCHAR2(1);
1784 l_batch_size NUMBER;
1785 l_number_of_worker NUMBER;
1786 l_total NUMBER;
1787 l_parent_request_id NUMBER;
1788 l_sub_conc_program VARCHAR2(30);
1789 l_sub_request_id NUMBER;
1790 l_sql VARCHAR2(200);
1791
1792 BEGIN
1793
1794 retcode := 0;
1795
1796 -- return if no history tracking
1797 --
1798 IF fnd_profile.value('HZ_PROFILE_VERSION') = 'NO_VERSION' THEN
1799 Write_Log('Profile HZ_PROFILE_VERSION has NO_VERSION.');
1800 Write_Log('Quit. No records need to be processed.');
1801 RETURN;
1802 END IF;
1803
1804 -- return if extension table is empty
1805 --
1806 IF p_entity_name = C_ORG THEN
1807 OPEN c_org_extension_exists;
1808 FETCH c_org_extension_exists INTO l_do_copy;
1809 IF c_org_extension_exists%NOTFOUND THEN
1810 l_do_copy := 'N';
1811 END IF;
1812 CLOSE c_org_extension_exists;
1813
1814 ELSIF p_entity_name = C_PER THEN
1815 OPEN c_per_extension_exists;
1816 FETCH c_per_extension_exists INTO l_do_copy;
1817 IF c_per_extension_exists%NOTFOUND THEN
1818 l_do_copy := 'N';
1819 END IF;
1820 CLOSE c_per_extension_exists;
1821
1822 END IF;
1823
1824 IF l_do_copy = 'N' THEN
1825 Write_Log('No records in extension table. Quit.');
1826 RETURN;
1827 END IF;
1828
1829 -- truncate staging table
1830 --
1831 Write_Log('Truncating staging table ...');
1832 l_owner := hz_utility_v2pub.Get_SchemaName('AR');
1833 Write_Log('l_owner = '||l_owner);
1834
1835 l_sql := 'truncate table '||l_owner||'.';
1836 IF p_entity_name = C_ORG THEN
1837 l_sql := l_sql||'HZ_ORG_PROFILES_EXT_SG';
1838 ELSE
1839 l_sql := l_sql||'HZ_PER_PROFILES_EXT_SG';
1840 END IF;
1841 execute immediate l_sql;
1842
1843 -- validate parameters
1844 --
1845 IF (p_batch_size IS NULL OR p_batch_size < 1000) THEN
1846 l_batch_size := 1000;
1847 ELSE
1848 l_batch_size := p_batch_size;
1849 END IF;
1850 Write_Log('p_batch_size = '||p_batch_size);
1851 Write_Log('l_batch_size = '||l_batch_size);
1852
1853 IF (p_number_of_worker IS NULL OR p_number_of_worker < 1) THEN
1854 l_number_of_worker := 1;
1855 ELSE
1856 l_number_of_worker := p_number_of_worker;
1857 END IF;
1858 Write_Log('p_number_of_worker = '||p_number_of_worker);
1859 Write_Log('l_number_of_worker = '||l_number_of_worker);
1860
1861 -- fetch records need to be processed into staging table and
1862 -- split the staging table into multiple segments based on batch size
1863 --
1864 Write_Log('Populate staging table ...');
1865 populate_staging_table(p_entity_name, l_batch_size, l_total);
1866 Write_Log('l_total = '||l_total);
1867
1868 IF (l_total = 0) THEN
1869 Write_Log('No records in staging table. Quit.');
1870 RETURN;
1871 END IF;
1872
1873 -- get parent request id
1874 --
1875 l_parent_request_id := hz_utility_v2pub.request_id;
1876 Write_Log('l_parent_request_id = '||l_parent_request_id);
1877
1878 -- submit sub requests
1879 --
1880 IF (l_number_of_worker = 1) THEN
1881 copy_conc_sub(errbuf, retcode, p_entity_name, l_parent_request_id);
1882 ELSE
1883 FOR i IN 1..l_number_of_worker LOOP
1884 IF p_entity_name = C_ORG THEN
1885 l_sub_conc_program := 'ARHCOEXS';
1886 ELSIF p_entity_name = C_PER THEN
1887 l_sub_conc_program := 'ARHCPEXS';
1888 END IF;
1889
1890 l_sub_request_id :=
1891 FND_REQUEST.SUBMIT_REQUEST(
1892 'AR', l_sub_conc_program, '',
1893 SYSDATE, FALSE,
1894 TO_CHAR(l_parent_request_id));
1895
1896 IF l_sub_request_id = 0 THEN
1897 Write_Log('Failed to submit concurrent request.');
1898 retcode := 2;
1899 RETURN;
1900 ELSE
1901 Write_Log('l_sub_request_id = '||l_sub_request_id);
1902 END IF;
1903 END LOOP;
1904 END IF;
1905
1906 EXCEPTION
1907 WHEN OTHERS THEN
1908 retcode := 2;
1909
1910 END copy_conc_main;
1911
1912
1913 /**
1914 * PUBLIC PROCEDURE copy_org_conc_main
1915 *
1916 * DESCRIPTION
1917 * Main concurrent program to copy organization extension data
1918 *
1919 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1920 *
1921 * IN:
1922 * p_batch_size Batch Size
1923 * p_number_of_worker Number of Worker
1924 *
1925 * MODIFICATION HISTORY
1926 *
1927 * 03-15-2005 Jianying Huang o Created.
1928 */
1929
1930 PROCEDURE copy_org_conc_main (
1931 errbuf OUT NOCOPY VARCHAR2,
1932 retcode OUT NOCOPY VARCHAR2,
1933 p_batch_size IN NUMBER,
1934 p_number_of_worker IN NUMBER
1935 ) IS
1936 BEGIN
1937
1938 copy_conc_main (
1939 errbuf, retcode,
1940 C_ORG,
1941 p_batch_size,
1942 p_number_of_worker
1943 );
1944
1945 END copy_org_conc_main;
1946
1947
1948 /**
1949 * PUBLIC PROCEDURE copy_per_conc_main
1950 *
1951 * DESCRIPTION
1952 * Main concurrent program to copy person extension data
1953 *
1954 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1955 *
1956 * IN:
1957 * p_batch_size Batch Size
1958 * p_number_of_worker Number of Worker
1959 *
1960 * MODIFICATION HISTORY
1961 *
1962 * 03-15-2005 Jianying Huang o Created.
1963 */
1964
1965 PROCEDURE copy_per_conc_main (
1966 errbuf OUT NOCOPY VARCHAR2,
1967 retcode OUT NOCOPY VARCHAR2,
1968 p_batch_size IN NUMBER,
1969 p_number_of_worker IN NUMBER
1970 ) IS
1971 BEGIN
1972
1973 copy_conc_main (
1974 errbuf, retcode,
1975 C_PER,
1976 p_batch_size,
1977 p_number_of_worker
1978 );
1979
1980 END copy_per_conc_main;
1981
1982
1983 /**
1984 * PUBLIC PROCEDURE copy_org_conc_sub
1985 *
1986 * DESCRIPTION
1987 * Sub concurrent program to copy organization extension data
1988 *
1989 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1990 *
1991 * IN:
1992 * p_parent_request_id Parent Request ID
1993 *
1994 * MODIFICATION HISTORY
1995 *
1996 * 03-15-2005 Jianying Huang o Created.
1997 */
1998
1999 PROCEDURE copy_org_conc_sub (
2000 errbuf OUT NOCOPY VARCHAR2,
2001 retcode OUT NOCOPY VARCHAR2,
2002 p_parent_request_id IN NUMBER
2003 ) IS
2004 BEGIN
2005
2006 copy_conc_sub (
2007 errbuf, retcode,
2008 C_ORG,
2009 p_parent_request_id
2010 );
2011
2012 END copy_org_conc_sub;
2013
2014
2015 /**
2016 * PUBLIC PROCEDURE copy_per_conc_sub
2017 *
2018 * DESCRIPTION
2019 * Sub concurrent program to copy person extension data
2020 *
2021 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2022 *
2023 * IN:
2024 * p_parent_request_id Parent Request ID
2025 *
2026 * MODIFICATION HISTORY
2027 *
2028 * 03-15-2005 Jianying Huang o Created.
2029 */
2030
2031 PROCEDURE copy_per_conc_sub (
2032 errbuf OUT NOCOPY VARCHAR2,
2033 retcode OUT NOCOPY VARCHAR2,
2034 p_parent_request_id IN NUMBER
2035 ) IS
2036 BEGIN
2037
2038 copy_conc_sub (
2039 errbuf, retcode,
2040 C_PER,
2041 p_parent_request_id
2042 );
2043
2044 END copy_per_conc_sub;
2045
2046 END HZ_EXTENSIBILITY_PVT;