[Home] [Help]
PACKAGE BODY: APPS.HZ_IMP_LOAD_CONTACT_ROLE_PKG
Source
4 l_action_mismatch_errors FLAG_ERROR;
1 PACKAGE BODY HZ_IMP_LOAD_CONTACT_ROLE_PKG AS
2 /*$Header: ARHLOCRB.pls 120.13 2005/12/06 13:56:34 vravicha noship $*/
3
5 l_error_flag NUMBER_COLUMN;
6 l_role_type_error LOOKUP_ERROR;
7 l_owner_table_error FLAG_ERROR;
8
9 l_row_id ROWID;
10 l_batch_id BATCH_ID;
11 l_cp_orig_system CONT_ORIG_SYSTEM;
12 l_cp_orig_system_reference CONT_ORIG_SYSTEM_REFERENCE;
13 l_controle_orig_system CONT_ORIG_SYSTEM;
14 l_controle_orig_system_ref CONT_ORIG_SYSTEM_REFERENCE;
15 l_insert_update_flag INSERT_UPDATE_FLAG;
16 l_interface_status INTERFACE_STATUS;
17 l_action_flag ACTION_FLAG;
18 l_error_id ERROR_ID;
19 l_org_contact_id ORG_CONTACT_ID;
20 l_org_contact_role_id ORG_CONTACT_ROLE_ID;
21 l_created_by_module CREATED_BY_MODULE;
22 l_role_type ROLE_TYPE;
23 l_rerun_flag varchar2(1);
24 l_errm varchar2(100);
25
26 l_num_row_processed NUMBER_COLUMN;
27
28 l_user_id NUMBER;
29 l_user_name varchar2(100);
30
31 l_last_update_login NUMBER;
32 l_program_id NUMBER;
33 l_program_application_id NUMBER;
34 l_request_id NUMBER;
35 l_program_update_date DATE;
36 l_no_end_date DATE;
37 g_debug_count NUMBER := 0;
38 g_debug BOOLEAN := FALSE;
39
40 l_createdby_errors LOOKUP_ERROR;
41
42 PROCEDURE populate_error_table(
43 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
44 P_DUP_VAL_EXP IN VARCHAR2,
45 P_SQL_ERRM IN VARCHAR2 ) IS
46
47 dup_val_exp_val VARCHAR2(1) := null;
48 other_exp_val VARCHAR2(1) := 'Y';
49 l_debug_prefix VARCHAR2(30) := '';
50 BEGIN
51
52 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
53 hz_utility_v2pub.debug(p_message=>'ROLE: populate_error_table()+',
54 p_prefix=>l_debug_prefix,
55 p_msg_level=>fnd_log.level_procedure);
56 END IF;
57 IF(P_DUP_VAL_EXP = 'Y') then
58 other_exp_val := null;
59 IF(instr(P_SQL_ERRM, '_U1')<>0) THEN
60 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
61 hz_utility_v2pub.debug(p_message=>'ROLE: HZ_ORG_CONTACT_ROLES_U1 violated',
62 p_prefix =>l_debug_prefix,
63 p_msg_level=>fnd_log.level_statement);
64 END IF;
65
66 dup_val_exp_val := 'A';
67 ELSE -- '_U2'
68 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
69 hz_utility_v2pub.debug(p_message=>'ROLE: HZ_ORG_CONTACT_ROLES_U2 violated',
70 p_prefix =>l_debug_prefix,
71 p_msg_level=>fnd_log.level_statement);
72 END IF;
73
74 dup_val_exp_val := 'B';
75 END IF;
76 END IF;
77
78 insert into hz_imp_tmp_errors
79 (
80 request_id,
81 batch_id,
82 int_row_id,
83 interface_table_name,
84 error_id,
85 creation_date,
86 created_by,
87 last_update_date,
88 last_updated_by,
89 last_update_login,
90 program_application_id,
91 program_id,
92 program_update_date,
93 e1_flag,
94 e2_flag,
95 e3_flag,
96 ACTION_MISMATCH_FLAG,
97 MISSING_PARENT_FLAG,
98 DUP_VAL_IDX_EXCEP_FLAG,
99 OTHER_EXCEP_FLAG
100 )
101 (
102 select P_DML_RECORD.REQUEST_ID,
103 P_DML_RECORD.BATCH_ID,
104 p_sg.int_row_id,
105 'HZ_IMP_CONTACTROLES_INT',
106 HZ_IMP_ERRORS_S.NextVal,
107 P_DML_RECORD.SYSDATE,
108 P_DML_RECORD.USER_ID,
109 P_DML_RECORD.SYSDATE,
110 P_DML_RECORD.USER_ID,
111 P_DML_RECORD.LAST_UPDATE_LOGIN,
112 P_DML_RECORD.PROGRAM_APPLICATION_ID,
113 P_DML_RECORD.PROGRAM_ID,
114 P_DML_RECORD.SYSDATE,
115 'Y',
116 'Y',
117 'Y',
118 'Y',
119 'Y',
120 dup_val_exp_val,
121 other_exp_val
122 from hz_imp_contactroles_int int,hz_imp_contactroles_sg p_sg
123 where int.rowid = p_sg.int_row_id
124 and p_sg.action_flag = 'I'
125 and p_sg.batch_id = P_DML_RECORD.BATCH_ID
129 );
126 and int.sub_orig_system = P_DML_RECORD.OS
127 and int.sub_orig_system_reference
128 between P_DML_RECORD.FROM_OSR and P_DML_RECORD.TO_OSR
130 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
131 hz_utility_v2pub.debug(p_message=>'ROLE:populate_error_table()-',
132 p_prefix=>l_debug_prefix,
133 p_msg_level=>fnd_log.level_procedure);
134 END IF;
135 END populate_error_table;
136
137 --------------------------------------
138 -- private procedures and functions
139 --------------------------------------
140 --------------------------------------
141 /*PROCEDURE enable_debug IS
142 BEGIN
143 g_debug_count := g_debug_count + 1;
144
145 IF g_debug_count = 1 THEN
146 IF fnd_profile.value('HZ_API_FILE_DEBUG_ON') = 'Y' OR
147 fnd_profile.value('HZ_API_DBMS_DEBUG_ON') = 'Y'
148 THEN
149 hz_utility_v2pub.enable_debug;
150 g_debug := TRUE;
151 END IF;
152 END IF;
153 END enable_debug;
154 */
155 --------------------------------------
156 --------------------------------------
157 /*PROCEDURE disable_debug IS
158 BEGIN
159
160 IF g_debug THEN
161 g_debug_count := g_debug_count - 1;
162 IF g_debug_count = 0 THEN
163 hz_utility_v2pub.disable_debug;
164 g_debug := FALSE;
165 END IF;
166 END IF;
167
168 END disable_debug;
169 */
170
171 /********************************************************************************
172 *
173 * process_insert_contactroles
174 *
175 ********************************************************************************/
176
177 PROCEDURE process_insert_contactroles (
178 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
179 ,x_return_status OUT NOCOPY VARCHAR2
180 ,x_msg_count OUT NOCOPY NUMBER
181 ,x_msg_data OUT NOCOPY VARCHAR2
182 ) IS
183
184 c_handle_insert RefCurType;
185
186 l_insert_sql varchar2(15000) :=
187 '
188 insert all
189 when (action_mismatch_error is not null
190 and role_type_error is not null
191 and owner_table_error is not null
192 and createdby_error is not null
193 and controle_osr_mismatch_err is not null ) then
194 into hz_org_contact_roles (
195 application_id,
196 created_by,
197 creation_date,
198 last_updated_by,
199 last_update_date,
200 last_update_login,
201 program_application_id,
202 program_id,
203 program_update_date,
204 request_id,
205 org_contact_role_id,
206 org_contact_id,
207 role_type,
208 role_level,
209 primary_flag,
210 object_version_number,
211 created_by_module,
212 status)
213 values(
214 :application_id,
215 :user_id,
216 :l_sysdate,
217 :user_id,
218 :l_sysdate,
219 :last_update_login,
220 :program_application_id,
221 :program_id,
222 :l_sysdate,
223 :request_id,
224 contact_role_id,
225 contact_id,
226 role_type,
227 ''N'',
228 ''N'',
229 1,
230 nvl(nullif(created_by_module, :p_gmiss_char), ''HZ_IMPORT''),
231 ''A'')
232 else
233 into hz_imp_tmp_errors (
234 created_by,
235 creation_date,
236 last_updated_by,
237 last_update_date,
238 last_update_login,
239 program_application_id,
240 program_id,
241 program_update_date,
242 error_id,
243 batch_id,
244 request_id,
245 int_row_id,
246 interface_table_name,
247 e1_flag,
248 e2_flag,
249 e3_flag,
250 ACTION_MISMATCH_FLAG,
251 MISSING_PARENT_FLAG)
252 values (
253 :user_id,
254 :l_sysdate,
255 :user_id,
256 :l_sysdate,
257 :last_update_login,
258 :program_application_id,
259 :program_id,
260 :l_sysdate,
261 HZ_IMP_ERRORS_S.nextval,
262 :p_batch_id,
263 :request_id,
264 row_id,
265 ''HZ_IMP_CONTACTROLES_INT'',
266 role_type_error,
267 controle_osr_mismatch_err,
268 createdby_error,
269 action_mismatch_error,
270 owner_table_error)
271 select /*+ leading(crs) use_nl(role_type_l) */
272 cri.rowid row_id,
273 -- cri.contact_orig_system,
274 -- cri.contact_orig_system_reference,
275 -- cri.sub_orig_system,
276 -- cri.sub_orig_system_reference,
277 -- cri.insert_update_flag,
278 cri.role_type,
279 -- cri.interface_status,
280 -- crs.action_flag,
281 nvl(nullif(cri.created_by_module,:p_gmiss_char),''HZ_IMPORT'') created_by_module,
282 crs.contact_id,
283 crs.contact_role_id,
284 nvl2(nullif(cri.role_type, :p_gmiss_char), nvl2(role_type_l.lookup_code, ''Y'', null), null) role_type_error,
285 nvl2(nullif(nullif(insert_update_flag, :p_gmiss_char), action_flag), null, ''Y'') action_mismatch_error,
286 nvl2(mosr.owner_table_id,''Y'',null) owner_table_error ,
287 --nvl2(nullif(mosr.party_id,mosr_party.owner_table_id),null,''Y'') controle_osr_mismatch_err
288 nvl2(nullif(mosr.orig_system_reference,cri.contact_orig_system_reference),null,''Y'') controle_osr_mismatch_err,
292 hz_imp_contactroles_sg crs,
289 nvl2(nullif(cri.created_by_module,:p_gmiss_char),createdby_l.lookup_code,''Y'') createdby_error
290
291 from hz_imp_contactroles_int cri,
293 hz_orig_sys_references mosr,
294 --hz_orig_sys_references mosr_party,
295 hz_org_contacts org_cont,
296 --hz_relationships rel,
297 fnd_lookup_values role_type_l,
298 fnd_lookup_values createdby_l
299 where cri.rowid = crs.int_row_id
300 and org_cont.org_contact_id = crs.contact_id
301 and mosr.orig_system (+) = cri.contact_orig_system
302 and mosr.orig_system_reference (+) = cri.contact_orig_system_reference
303 and mosr.status (+) = ''A''
304 and mosr.owner_table_name (+) = ''HZ_ORG_CONTACTS''
305 --and mosr.party_id = rel.party_id
306 --and rel.subject_table_name = ''HZ_PARTIES''
307 --and rel.directional_flag = ''F''
308 --and rel.subject_id = mosr_party.owner_table_id
309 --and mosr_party.orig_system (+) = cri.sub_orig_system
310 --and mosr_party.orig_system_reference (+) = cri.sub_orig_system_reference
311 --and mosr_party.status (+) = ''A''
312 --and mosr_party.owner_table_name (+) = ''HZ_PARTIES''
313 and role_type_l.lookup_code (+) = cri.role_type
314 and role_type_l.lookup_type (+) = ''CONTACT_ROLE_TYPE''
315 and role_type_l.language (+) = userenv(''LANG'')
316 and role_type_l.view_application_id (+) = 222
317 and role_type_l.security_group_id (+) =
318 fnd_global.lookup_security_group(''CONTACT_ROLE_TYPE'', 222)
319 and createdby_l.lookup_code (+) = cri.created_by_module
320 and createdby_l.lookup_type (+) = ''HZ_CREATED_BY_MODULES''
321 and createdby_l.language (+) = userenv(''LANG'')
322 and createdby_l.view_application_id (+) = 222
323 and createdby_l.security_group_id (+) =
324 fnd_global.lookup_security_group(''HZ_CREATED_BY_MODULES'', 222)
325 and crs.action_flag = ''I''
326 and crs.batch_id = :p_batch_id
327 and crs.sub_orig_system = :p_wu_os
328 and crs.sub_orig_system_reference between :p_from_osr and :p_to_osr
329 and crs.batch_mode_flag = :p_batch_mode_flag ';
330
331 l_where_first_run_sql varchar2(35) := ' AND cri.interface_status is null';
332 l_where_rerun_sql varchar2(35) := ' AND cri.interface_status = ''C''';
333 l_where_enabled_lookup_sql varchar2(1000) :=
334 ' AND ( role_type_l.ENABLED_FLAG(+) = ''Y'' AND
335 TRUNC(SYSDATE) BETWEEN
336 TRUNC(NVL( role_type_l.START_DATE_ACTIVE,SYSDATE ) ) AND
337 TRUNC(NVL( role_type_l.END_DATE_ACTIVE,SYSDATE ) ) )
338 AND ( createdby_l.ENABLED_FLAG(+) = ''Y'' AND
339 TRUNC(SYSDATE) BETWEEN
340 TRUNC(NVL( createdby_l.START_DATE_ACTIVE,SYSDATE ) ) AND
341 TRUNC(NVL( createdby_l.END_DATE_ACTIVE,SYSDATE ) ) )';
342
343 l_final_sql VARCHAR2(15000);
344 l_dml_exception varchar2(1) := 'N';
345 l_debug_prefix VARCHAR2(30) := '';
346
347 BEGIN
348
349 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
350 hz_utility_v2pub.debug(p_message=>'ROLE:process_insert_contactroles+',
351 p_prefix=>l_debug_prefix,
352 p_msg_level=>fnd_log.level_procedure);
353 END IF;
354 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
355 hz_utility_v2pub.debug(p_message=>'ROLE:RERUN:' || P_DML_RECORD.RERUN,
356 p_prefix =>l_debug_prefix,
357 p_msg_level=>fnd_log.level_statement);
358 hz_utility_v2pub.debug(p_message=>'ROLE:ALLOW_DISABLED_LOOKUP:' || P_DML_RECORD.ALLOW_DISABLED_LOOKUP,
359 p_prefix =>l_debug_prefix,
360 p_msg_level=>fnd_log.level_statement);
361 END IF;
362
363 savepoint load_contactroles_pvt;
364
365 FND_MSG_PUB.initialize;
366
367 --Initialize API return status to success.
368 x_return_status := FND_API.G_RET_STS_SUCCESS;
369
370 IF P_DML_RECORD.ALLOW_DISABLED_LOOKUP = 'Y' THEN
371 IF P_DML_RECORD.RERUN = 'N' THEN
372 l_final_sql := l_insert_sql || l_where_first_run_sql;
373 ELSE
374 l_final_sql := l_insert_sql || l_where_rerun_sql;
375 END IF;
376 ELSE
377 IF P_DML_RECORD.RERUN = 'N' THEN
378 l_final_sql := l_insert_sql || l_where_first_run_sql || l_where_enabled_lookup_sql;
379 ELSE
380 l_final_sql := l_insert_sql || l_where_rerun_sql || l_where_enabled_lookup_sql;
381 END IF;
382 END IF;
383
384 EXECUTE IMMEDIATE l_final_sql using
385 P_DML_RECORD.APPLICATION_ID,
386 P_DML_RECORD.USER_ID,
387 P_DML_RECORD.SYSDATE,
388 P_DML_RECORD.USER_ID,
389 P_DML_RECORD.SYSDATE,
390 P_DML_RECORD.LAST_UPDATE_LOGIN,
391 P_DML_RECORD.PROGRAM_APPLICATION_ID,
392 P_DML_RECORD.PROGRAM_ID,
393 P_DML_RECORD.SYSDATE,
394 P_DML_RECORD.REQUEST_ID,
395 P_DML_RECORD.GMISS_CHAR,
396
397 P_DML_RECORD.USER_ID,
398 P_DML_RECORD.SYSDATE,
399 P_DML_RECORD.USER_ID,
400 P_DML_RECORD.SYSDATE,
401 P_DML_RECORD.LAST_UPDATE_LOGIN,
402 P_DML_RECORD.PROGRAM_APPLICATION_ID,
403 P_DML_RECORD.PROGRAM_ID,
404 P_DML_RECORD.SYSDATE,
405
406 P_DML_RECORD.BATCH_ID,
407 P_DML_RECORD.REQUEST_ID,
408 P_DML_RECORD.GMISS_CHAR,
409 P_DML_RECORD.GMISS_CHAR,
410 P_DML_RECORD.GMISS_CHAR,
411 P_DML_RECORD.GMISS_CHAR,
412 P_DML_RECORD.BATCH_ID,
413 P_DML_RECORD.OS,
414 P_DML_RECORD.FROM_OSR,
415 P_DML_RECORD.TO_OSR,
416 P_DML_RECORD.BATCH_MODE_FLAG;
417
418 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
419 hz_utility_v2pub.debug(p_message=>'ROLE:process_insert_contactroles-',
420 p_prefix=>l_debug_prefix,
421 p_msg_level=>fnd_log.level_procedure);
422 END IF;
423
424 EXCEPTION
425 WHEN DUP_VAL_ON_INDEX THEN
426 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert contactroles dup val exception: ' || SQLERRM);
427 ROLLBACK to load_contactroles_pvt;
428 populate_error_table(P_DML_RECORD, 'Y', SQLERRM);
429 x_return_status := FND_API.G_RET_STS_ERROR;
430 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
431 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
432 FND_MSG_PUB.ADD;
433 FND_MSG_PUB.Count_And_Get(
434 p_encoded => FND_API.G_FALSE,
435 p_count => x_msg_count,
436 p_data => x_msg_data);
437
438 WHEN OTHERS THEN
439 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert contactroles other exception: ' || SQLERRM);
440 ROLLBACK TO load_contactroles_pvt;
441 populate_error_table(P_DML_RECORD, 'N', SQLERRM);
442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
443 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
444 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
445 FND_MSG_PUB.ADD;
446 FND_MSG_PUB.Count_And_Get(
447 p_encoded => FND_API.G_FALSE,
448 p_count => x_msg_count,
449 p_data => x_msg_data);
450
451 END process_insert_contactroles;
452
453 /********************************************************************************
454 *
455 * load_contactroles
456 *
457 ********************************************************************************/
458
459 PROCEDURE load_contactroles (
460 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
461 ,x_return_status OUT NOCOPY VARCHAR2
462 ,x_msg_count OUT NOCOPY NUMBER
463 ,x_msg_data OUT NOCOPY VARCHAR2
464 ) IS
465 l_debug_prefix VARCHAR2(30) := '';
466 BEGIN
467
468 savepoint load_contactroles_pvt;
469
470 -- Check if API is called in debug mode. If yes, enable debug.
471 --enable_debug;
472
473 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
474 hz_utility_v2pub.debug(p_message=>'ROLE:process_insert_contactroles+',
475 p_prefix=>l_debug_prefix,
476 p_msg_level=>fnd_log.level_procedure);
477 END IF;
478
479 FND_MSG_PUB.initialize;
480
481 --Initialize API return status to success.
482 x_return_status := FND_API.G_RET_STS_SUCCESS;
483
484 process_insert_contactroles
485 (
486 P_DML_RECORD => P_DML_RECORD
487 ,x_return_status => x_return_status
488 ,x_msg_count => x_msg_count
489 ,x_msg_data => x_msg_data
490 );
491
492 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
493 hz_utility_v2pub.debug(p_message=>'ROLE:load_contactroles-',
494 p_prefix=>l_debug_prefix,
495 p_msg_level=>fnd_log.level_procedure);
496 END IF;
497 -- if enabled, disable debug
498 --disable_debug;
499
500 EXCEPTION
501 WHEN FND_API.G_EXC_ERROR THEN
502 ROLLBACK TO load_contactroles_pvt;
503 x_return_status := FND_API.G_RET_STS_ERROR;
504 FND_MSG_PUB.Count_And_Get(
505 p_encoded => FND_API.G_FALSE,
506 p_count => x_msg_count,
507 p_data => x_msg_data);
508
509 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
510 ROLLBACK TO load_contactroles_pvt;
511 FND_FILE.put_line(fnd_file.log,'Unexpected error occurs while loading contactroles');
512 FND_FILE.put_line(fnd_file.log, l_errm);
513 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
514 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
515 FND_MESSAGE.SET_TOKEN('ERROR' ,l_errm);
516 FND_MSG_PUB.ADD;
517 FND_MSG_PUB.Count_And_Get(
518 p_encoded => FND_API.G_FALSE,
519 p_count => x_msg_count,
520 p_data => x_msg_data);
521
522 WHEN OTHERS THEN
523 ROLLBACK TO load_contactroles_pvt;
524 FND_FILE.put_line(fnd_file.log,'Unexpected error occurs while loading contactroles');
525 FND_FILE.put_line(fnd_file.log, l_errm);
526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
527 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
528 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
529 FND_MSG_PUB.ADD;
530 FND_MSG_PUB.Count_And_Get(
531 p_encoded => FND_API.G_FALSE,
532 p_count => x_msg_count,
533 p_data => x_msg_data);
534
535 END load_contactroles;
536
537 END HZ_IMP_LOAD_CONTACT_ROLE_PKG;