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