[Home] [Help]
PACKAGE BODY: APPS.HZ_IMP_LOAD_FINNUMBERS_PKG
Source
1 PACKAGE BODY HZ_IMP_LOAD_FINNUMBERS_PKG AS
2 /*$Header: ARHLFNNB.pls 120.3 2006/01/17 08:36:17 vravicha noship $*/
3
4
5 c_end_date DATE := to_date('4712.12.31 00:01','YYYY.MM.DD HH24:MI');
6 g_debug_count NUMBER := 0;
7 --g_debug BOOLEAN := FALSE;
8
9 l_fn_id FN_ID;
10 l_fr_id FR_ID;
11 l_tca_fr_id TCA_FR_ID;
12
13 l_fin_num FINANCIAL_NUMBER;
14 l_fin_num_name FINANCIAL_NUMBER_NAME ;
15 l_fin_num_cur FINANCIAL_NUMBER_CURRENCY;
16 l_proj_act_flag PROJECTED_ACTUAL_FLAG;
17 l_fin_units_applied FINANCIAL_UNITS_APPLIED;
18 l_created_by_module CREATED_BY_MODULE;
19 l_action_flag ACTION_FLAG;
20 --l_fin_num_name_err LOOKUP_ERROR;
21 l_action_error_flag FLAG_ERROR;
22 l_error_flag FLAG_ERROR;
23
24 l_exception_exists FLAG_ERROR;
25 l_num_row_processed NUMBER_COLUMN;
26 l_row_id ROWID;
27
28 l_createdby_errors LOOKUP_ERROR;
29
30 PROCEDURE open_update_cursor (
31 update_cursor IN OUT NOCOPY update_cursor_type,
32 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE );
33
34
35 PROCEDURE process_insert_finnumbers (
36 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
37 x_return_status OUT NOCOPY VARCHAR2,
38 x_msg_count OUT NOCOPY NUMBER,
39 x_msg_data OUT NOCOPY VARCHAR2 );
40
41
42 PROCEDURE process_update_finnumbers (
43 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
44 x_return_status OUT NOCOPY VARCHAR2,
45 x_msg_count OUT NOCOPY NUMBER,
46 x_msg_data OUT NOCOPY VARCHAR2 );
47
48
49 PROCEDURE report_errors(
50 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
51 P_DML_EXCEPTION IN VARCHAR2);
52
53
54 PROCEDURE populate_error_table(
55 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
56 P_DUP_VAL_EXP IN VARCHAR2 );
57
58 --------------------------------------
59 -- forward declaration of private procedures and functions
60 --------------------------------------
61
62 /*PROCEDURE enable_debug;
63 PROCEDURE disable_debug;
64 */
65
66 PROCEDURE load_finnumbers (
67 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
68 x_return_status OUT NOCOPY VARCHAR2,
69 x_msg_count OUT NOCOPY NUMBER,
70 x_msg_data OUT NOCOPY VARCHAR2 )
71 IS
72 l_debug_prefix VARCHAR2(30) := '';
73 BEGIN
74 savepoint load_finnumbers_pvt;
75 FND_MSG_PUB.initialize;
76 x_return_status := FND_API.G_RET_STS_SUCCESS;
77
78 --enable_debug;
79 -- Debug info.
80 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
81 hz_utility_v2pub.debug(p_message=>'FN:load_finnumbers()+',
82 p_prefix=>l_debug_prefix,
83 p_msg_level=>fnd_log.level_procedure);
84 END IF;
85
86 process_insert_finnumbers(P_DML_RECORD,
87 x_return_status, x_msg_count, x_msg_data );
88
89 IF x_return_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
90 process_update_finnumbers(P_DML_RECORD,
91 x_return_status, x_msg_count, x_msg_data );
92 END IF;
93
94 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
95 hz_utility_v2pub.debug(p_message=>'FN:load_finnumbers()-',
96 p_prefix=>l_debug_prefix,
97 p_msg_level=>fnd_log.level_procedure);
98 END IF;
99 --disable_debug;
100
101 EXCEPTION
102 WHEN FND_API.G_EXC_ERROR THEN
103 ----dbms_output.put_line('G_EXC_ERROR');
104
105 ROLLBACK TO load_finnumbers_pvt;
106 FND_FILE.put_line(fnd_file.log,'Execution error occurs while loading financial numbers');
107 FND_FILE.put_line(fnd_file.log, SQLERRM);
108 x_return_status := FND_API.G_RET_STS_ERROR;
109 FND_MSG_PUB.Count_And_Get(
110 p_encoded => FND_API.G_FALSE,
111 p_count => x_msg_count,
112 p_data => x_msg_data);
113
114 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
115 ----dbms_output.put_line('Unexpected error occurs while loading financial numbers');
116
117 ROLLBACK TO load_finnumbers_pvt;
118 FND_FILE.put_line(fnd_file.log,'Unexpected error occurs while loading financial numbers');
119 FND_FILE.put_line(fnd_file.log, SQLERRM);
120 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
121 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
122 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
123 FND_MSG_PUB.ADD;
124 FND_MSG_PUB.Count_And_Get(
125 p_encoded => FND_API.G_FALSE,
126 p_count => x_msg_count,
127 p_data => x_msg_data);
128
129 WHEN OTHERS THEN
130 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
131 hz_utility_v2pub.debug(p_message=>'load_finnumbers Exception: ',
132 p_prefix=>'SQL ERROR',
133 p_msg_level=>fnd_log.level_error);
134 hz_utility_v2pub.debug(p_message=>SQLERRM,
135 p_prefix=>'SQL ERROR',
136 p_msg_level=>fnd_log.level_error);
137 END IF;
138 ----dbms_output.put_line('load_finnumbers Exception: ');
139
140 ROLLBACK TO load_finnumbers_pvt;
141 FND_FILE.put_line(fnd_file.log,'Unexpected other errors occurs while loading financial numbers');
142 FND_FILE.put_line(fnd_file.log, SQLERRM);
143 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
144 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
145 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
146 FND_MSG_PUB.ADD;
147 FND_MSG_PUB.Count_And_Get(
148 p_encoded => FND_API.G_FALSE,
149 p_count => x_msg_count,
150 p_data => x_msg_data);
151
152 END load_finnumbers;
153
154
155 PROCEDURE open_update_cursor ( update_cursor IN OUT NOCOPY update_cursor_type,
156 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE) IS
157 l_sql_query VARCHAR2(11000) :=
158 'SELECT fn_int.ROWID,
159 fn_sg.action_flag,
160 fn_sg.financial_report_id,
161 fn_sg.financial_number_id,
162 fn_int.FINANCIAL_NUMBER,
163 fn_int.FINANCIAL_NUMBER_NAME,
164 fn_int.FINANCIAL_NUMBER_CURRENCY,
165 fn_int.PROJECTED_ACTUAL_FLAG,
166 fn_int.FINANCIAL_UNITS_APPLIED,
167 fn_int.CREATED_BY_MODULE,
168 decode(nvl(fn_int.insert_update_flag, fn_sg.action_flag), fn_sg.action_flag, ''Y'', null),
169 fn_sg.error_flag
170 FROM hz_imp_finnumbers_int fn_int,
171 hz_imp_finnumbers_sg fn_sg
172 WHERE fn_sg.batch_id = :BATCH_ID
173 and fn_sg.batch_mode_flag = :BATCH_MODE_FLAG
174 and fn_sg.party_orig_system = :WU_OS
175 and fn_sg.party_orig_system_reference between :FROM_OSR AND :TO_OSR
176 and fn_sg.action_flag = ''U''
177 and fn_int.rowid = fn_sg.int_row_id';
178
179 l_first_run_clause varchar2(40) := ' AND fn_int.interface_status is null';
180 l_re_run_clause varchar2(40) := ' AND fn_int.interface_status = ''C''';
181 l_debug_prefix VARCHAR2(30) := '';
182 --l_where_enabled_lookup_sql varchar2(3000) := ' AND ( fin_num_l.ENABLED_FLAG(+) = ''Y'' )';
183
184 BEGIN
185
186 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
187 hz_utility_v2pub.debug(p_message=>'FN:open_update_cursor()+',
188 p_prefix=>l_debug_prefix,
189 p_msg_level=>fnd_log.level_procedure);
190 END IF;
191
192 if(P_DML_RECORD.RERUN='Y') then
193 l_sql_query := l_sql_query || l_re_run_clause;
194 else
195 l_sql_query := l_sql_query || l_first_run_clause;
196 end if;
197
198 OPEN update_cursor FOR l_sql_query
199 USING --P_DML_RECORD.GMISS_CHAR,
200 P_DML_RECORD.BATCH_ID, P_DML_RECORD.BATCH_MODE_FLAG,
201 P_DML_RECORD.OS, P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR;
202
203
204 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
205 hz_utility_v2pub.debug(p_message=>'FN:open_update_cursor()-',
206 p_prefix=>l_debug_prefix,
207 p_msg_level=>fnd_log.level_procedure);
208 END IF;
209 END open_update_cursor;
210
211
212 PROCEDURE process_insert_finnumbers (
213 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
214 x_return_status OUT NOCOPY VARCHAR2,
215 x_msg_count OUT NOCOPY NUMBER,
216 x_msg_data OUT NOCOPY VARCHAR2 ) IS
217
218 l_sql_query VARCHAR2(15000) :=
219 'begin insert all
220 when (action_flag = ''I''
221 and fin_num_name_err is not null
222 and action_mismatch_error is not null
223 and createdby_error is not null
224 and tca_fr_id is not null) then
225 into hz_financial_numbers (
226 created_by,
227 creation_date,
228 last_updated_by,
229 last_update_date,
230 last_update_login,
231 program_update_date,
232 request_id,
233 actual_content_source,
234 application_id,
235 content_source_type,
236 program_application_id,
237 program_id,
238 FINANCIAL_NUMBER_ID,
239 FINANCIAL_REPORT_ID,
240 FINANCIAL_NUMBER,
241 FINANCIAL_NUMBER_NAME,
242 FINANCIAL_NUMBER_CURRENCY,
243 PROJECTED_ACTUAL_FLAG,
244 FINANCIAL_UNITS_APPLIED,
245 STATUS,
246 OBJECT_VERSION_NUMBER,
247 CREATED_BY_MODULE)
248 values (
249 :1,
250 :2,
251 :1,
252 :2,
253 :3,
254 :2,
255 :4,
256 :5,
257 :6,
258 ''USER_ENTERED'',
259 :7,
260 :8,
261 fn_id,
262 fr_id,
263 fin_num,
264 fin_num_name,
265 fin_num_cur,
266 nvl(proj_act_flag, ''A''),
267 fin_units_applied,
268 ''A'',
269 1,
270 created_by_module)
271 else
272 into hz_imp_tmp_errors (
273 created_by,
274 creation_date,
275 last_updated_by,
276 last_update_date,
277 last_update_login,
278 program_update_date,
279 program_application_id,
280 program_id,
281 error_id,
282 batch_id,
283 request_id,
284 int_row_id,
285 interface_table_name,
286 ACTION_MISMATCH_FLAG,
287 MISSING_PARENT_FLAG,
288 e1_flag,
289 e2_flag)
290 values (
291 :1,
292 :2,
293 :1,
294 :2,
295 :3,
296 :2,
297 :7,
298 :8,
299 hz_imp_errors_s.nextval,
300 :9,
301 :4,
302 row_id,
303 ''HZ_IMP_FINNUMBERS_INT'',
304 action_mismatch_error,
305 nvl2(tca_fr_id, ''Y'', null),
306 fin_num_name_err,
307 createdby_error)
308 select row_id, action_flag, fr_id, nvl2(ranking, tca_fr_id, null) tca_fr_id, fn_id, fin_num, fin_num_name,
309 fin_num_cur, proj_act_flag, fin_units_applied, created_by_module,
310 fin_num_name_err, action_mismatch_error, error_flag, createdby_error
311 from (
312 select row_id, action_flag, fr_id, tca_fr_id, fn_id, fin_num, fin_num_name,
313 fin_num_cur, proj_act_flag, fin_units_applied, created_by_module,
314 nvl2(lkup, ''Y'', null) fin_num_name_err,
315 action_mismatch_error, error_flag, createdby_error,
316 rank() over
317 (partition by row_id order by ranking nulls last) new_rank,
318 ranking
319 from (
320 select /*+ use_nl(fin_num_l) */ fn_int.rowid row_id,
321 fn_sg.action_flag,
322 fn_sg.financial_report_id fr_id, -- logical key
323 hz_fr.financial_report_id tca_fr_id,
324 fn_sg.financial_number_id fn_id,
325 nullif(fn_int.financial_number_name, :10) fin_num_name, -- logical key
326 nullif(fn_int.financial_number, :11) fin_num,
327 nullif(fn_int.financial_number_currency, :10) fin_num_cur,
328 nullif(fn_int.projected_actual_flag, :10) proj_act_flag,
329 nullif(fn_int.financial_units_applied, :11) fin_units_applied,
330 nvl(nullif(fn_int.created_by_module, :10), ''HZ_IMPORT'') created_by_module,
331 nvl2(nullif(fn_int.created_by_module, :10), nvl2(createdby_l.lookup_code,''Y'',null), ''Y'') createdby_error,
332 fin_num_l.lookup_code lkup,
333 nvl2(nullif(nullif(fn_int.insert_update_flag, :10), fn_sg.action_flag), null, ''Y'') action_mismatch_error,
334 fn_sg.error_flag error_flag,
335 case when fn_sg.ISSUED_PERIOD = hz_fr.ISSUED_PERIOD then 1
336 when trunc(fn_sg.REPORT_START_DATE) =
337 trunc(hz_fr.REPORT_START_DATE)
338 and trunc(fn_sg.REPORT_END_DATE) =
339 trunc(hz_fr.REPORT_END_DATE) then 2 end ranking
340 from hz_imp_finnumbers_int fn_int,
341 hz_imp_finnumbers_sg fn_sg,
342 fnd_lookup_values fin_num_l,
343 hz_financial_reports hz_fr,
344 fnd_lookup_values createdby_l
345 where fn_sg.batch_id = :9
346 and fn_sg.party_orig_system = :12
347 and fn_sg.party_orig_system_reference between :13 and :14
348 and fn_sg.action_flag = ''I''
349 and fn_int.rowid = fn_sg.int_row_id
350 and fin_num_l.lookup_code(+) = fn_int.financial_number_name
351 and fin_num_l.lookup_type(+) = ''FIN_NUM_NAME''
352 and fin_num_l.language (+) = userenv(''LANG'')
353 and fin_num_l.view_application_id (+) = 222
354 and fin_num_l.security_group_id (+) =
355 fnd_global.lookup_security_group(''FIN_NUM_NAME'', 222)
356 AND createdby_l.lookup_code (+) = fn_int.created_by_module
357 AND createdby_l.lookup_type (+) = ''HZ_CREATED_BY_MODULES''
358 AND createdby_l.language (+) = userenv(''LANG'')
359 AND createdby_l.view_application_id (+) = 222
360 AND createdby_l.security_group_id (+) =
361 fnd_global.lookup_security_group(''HZ_CREATED_BY_MODULES'', 222)
362 and fn_sg.party_id = hz_fr.party_id (+)
363 and nvl(trunc(fn_sg.DATE_REPORT_ISSUED), :15) =
364 nvl(trunc(hz_fr.DATE_REPORT_ISSUED (+) ) , :15)
365 and fn_sg.type_of_financial_report = hz_fr.type_of_financial_report (+)
366 and fn_sg.document_reference = hz_fr.document_reference (+)
367 and hz_fr.ACTUAL_CONTENT_SOURCE (+) = :5
368 and fn_sg.batch_mode_flag = :16';
369
370 l_sql_query_end varchar2(15000):= ' )) where new_rank = 1; end;';
371 l_first_run_clause varchar2(40) := ' AND fn_int.interface_status is null';
372 l_re_run_clause varchar2(40) := ' AND fn_int.interface_status = ''C''';
373 l_final_qry varchar2(15000);
374 l_debug_prefix VARCHAR2(30) := '';
375 --l_where_enabled_lookup_sql varchar2(3000) := ' AND fin_num_l.ENABLED_FLAG(+) = ''Y''';
376 l_where_enabled_lookup_sql varchar2(3000) := ' AND ( fin_num_l.ENABLED_FLAG(+) = ''Y'' AND
377 TRUNC(:17) BETWEEN
378 TRUNC(NVL( fin_num_l.START_DATE_ACTIVE,:17 ) ) AND
379 TRUNC(NVL( fin_num_l.END_DATE_ACTIVE,:17 ) ) )
380 AND ( createdby_l.ENABLED_FLAG(+) = ''Y'' AND
381 TRUNC(:17) BETWEEN
382 TRUNC(NVL( createdby_l.START_DATE_ACTIVE,:17 ) ) AND
383 TRUNC(NVL( createdby_l.END_DATE_ACTIVE,:17 ) ) )';
384
385 BEGIN
386 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
387 hz_utility_v2pub.debug(p_message=>'process_insert_finnumbers()+',
388 p_prefix=>l_debug_prefix,
389 p_msg_level=>fnd_log.level_procedure);
390 END IF;
391 savepoint process_insert_finnumbers_pvt;
392 x_return_status := FND_API.G_RET_STS_SUCCESS;
393
394 if(P_DML_RECORD.RERUN='N') then
395 l_final_qry := l_sql_query || l_first_run_clause;
396 else
397 l_final_qry := l_sql_query || l_re_run_clause;
398 end if;
399
400 -- add clause for filtering out disabled lookup
401 if P_DML_RECORD.ALLOW_DISABLED_LOOKUP <> 'Y' then
402 l_final_qry := l_final_qry || l_where_enabled_lookup_sql;
403 l_final_qry := l_final_qry || l_sql_query_end;
404 execute immediate l_final_qry using
405 P_DML_RECORD.USER_ID, P_DML_RECORD.SYSDATE,
406 P_DML_RECORD.LAST_UPDATE_LOGIN, P_DML_RECORD.REQUEST_ID,
407 P_DML_RECORD.ACTUAL_CONTENT_SRC, P_DML_RECORD.APPLICATION_ID,
408 P_DML_RECORD.PROGRAM_APPLICATION_ID, P_DML_RECORD.PROGRAM_ID,
409 P_DML_RECORD.BATCH_ID, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_NUM,
410 P_DML_RECORD.OS, P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR,
411 c_end_date, P_DML_RECORD.BATCH_MODE_FLAG,P_DML_RECORD.SYSDATE;
412 else
413 l_final_qry := l_final_qry || l_sql_query_end;
414 execute immediate l_final_qry using
415 P_DML_RECORD.USER_ID, P_DML_RECORD.SYSDATE,
416 P_DML_RECORD.LAST_UPDATE_LOGIN, P_DML_RECORD.REQUEST_ID,
417 P_DML_RECORD.ACTUAL_CONTENT_SRC, P_DML_RECORD.APPLICATION_ID,
418 P_DML_RECORD.PROGRAM_APPLICATION_ID, P_DML_RECORD.PROGRAM_ID,
419 P_DML_RECORD.BATCH_ID, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_NUM,
420 P_DML_RECORD.OS, P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR,
421 c_end_date, P_DML_RECORD.BATCH_MODE_FLAG;
422 end if;
423
424 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
425 hz_utility_v2pub.debug(p_message=>'process_insert_finnumbers()-',
426 p_prefix=>l_debug_prefix,
427 p_msg_level=>fnd_log.level_procedure);
428 END IF;
429
430 EXCEPTION
431 WHEN DUP_VAL_ON_INDEX THEN
432 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert finnumbers dup val exception: ' || SQLERRM);
433 ROLLBACK to process_insert_finnumbers_pvt;
434
435 populate_error_table(P_DML_RECORD, 'Y');
436 x_return_status := FND_API.G_RET_STS_ERROR;
437
438 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
439 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
440 FND_MSG_PUB.ADD;
441 WHEN OTHERS THEN
442
443 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert finnumbers other exception: ' || SQLERRM);
444 ROLLBACK to process_insert_finnumbers_pvt;
445
446 populate_error_table(P_DML_RECORD, 'N');
447 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
448
449 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
450 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
451 FND_MSG_PUB.ADD;
452 END process_insert_finnumbers;
453
454
455 PROCEDURE populate_error_table(
456 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
457 P_DUP_VAL_EXP IN VARCHAR2 ) IS
458
459 dup_val_exp_val VARCHAR2(1) := null;
460 other_exp_val VARCHAR2(1) := 'Y';
461 l_debug_prefix VARCHAR2(30) := '';
462 BEGIN
463 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
464 hz_utility_v2pub.debug(p_message=>'FN:populate_error_table()+',
465 p_prefix=>l_debug_prefix,
466 p_msg_level=>fnd_log.level_procedure);
467 END IF;
468
469 if (P_DUP_VAL_EXP = 'Y') then
470 dup_val_exp_val := 'Y';
471 other_exp_val := null;
472 end if;
473
474 insert into hz_imp_tmp_errors
475 (
476 request_id,
477 batch_id,
478 int_row_id,
479 interface_table_name,
480 error_id,
481 creation_date,
482 created_by,
483 last_update_date,
484 last_updated_by,
485 last_update_login,
486 program_application_id,
487 program_id,
488 program_update_date,
489 DUP_VAL_IDX_EXCEP_FLAG,
490 OTHER_EXCEP_FLAG,
491 e1_flag,
492 e2_flag,
493 missing_parent_flag
494 )
495 (
496 select P_DML_RECORD.REQUEST_ID,
497 P_DML_RECORD.BATCH_ID,
498 fn_sg.int_row_id,
499 'HZ_IMP_FINNUMBERS_INT',
500 HZ_IMP_ERRORS_S.NextVal,
501 P_DML_RECORD.SYSDATE,
502 P_DML_RECORD.USER_ID,
503 P_DML_RECORD.SYSDATE,
504 P_DML_RECORD.USER_ID,
505 P_DML_RECORD.LAST_UPDATE_LOGIN,
506 P_DML_RECORD.PROGRAM_APPLICATION_ID,
507 P_DML_RECORD.PROGRAM_ID,
508 P_DML_RECORD.SYSDATE,
509 dup_val_exp_val,
510 other_exp_val,
511 -- this function report errors for exception
512 -- not checking all other potential errors
513 'Y', 'Y', 'Y'
514 from hz_imp_finnumbers_sg fn_sg
515 where fn_sg.action_flag = 'I'
516 and fn_sg.batch_id = P_DML_RECORD.BATCH_ID
517 and fn_sg.party_orig_system = P_DML_RECORD.OS
518 and fn_sg.party_orig_system_reference
519 between P_DML_RECORD.FROM_OSR and P_DML_RECORD.TO_OSR
520 );
521
522 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
523 hz_utility_v2pub.debug(p_message=>'FN:populate_error_table()-',
524 p_prefix=>l_debug_prefix,
525 p_msg_level=>fnd_log.level_procedure);
526 END IF;
527 END populate_error_table;
528
529
530 PROCEDURE process_update_finnumbers (
531 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
532 x_return_status OUT NOCOPY VARCHAR2,
533 x_msg_count OUT NOCOPY NUMBER,
534 x_msg_data OUT NOCOPY VARCHAR2 ) IS
535 c_update_cursor update_cursor_type;
536 l_dml_exception varchar2(1) := 'N';
537 l_debug_prefix VARCHAR2(30) := '';
538 BEGIN
539
540 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
541 hz_utility_v2pub.debug(p_message=>'FN:process_update_finnumbers()+',
542 p_prefix=>l_debug_prefix,
543 p_msg_level=>fnd_log.level_procedure);
544 END IF;
545 savepoint process_update_finnumbers_pvt;
546 FND_MSG_PUB.initialize;
547 --Initialize API return status to success.
548 x_return_status := FND_API.G_RET_STS_SUCCESS;
549
550 open_update_cursor(c_update_cursor, P_DML_RECORD);
551 fetch c_update_cursor bulk collect into
552 l_row_id,
553 l_action_flag,
554 l_fr_id, -- logical key
555 l_fn_id,
556 l_fin_num,
557 l_fin_num_name, -- logical key
558 l_fin_num_cur,
559 l_proj_act_flag,
560 l_fin_units_applied,
561 l_created_by_module,
562 --l_fin_num_name_err,
563 l_action_error_flag,
564 l_error_flag;
565 close c_update_cursor;
566
567 --begin
568 forall j in 1..l_fn_id.count
569 update hz_financial_numbers set
570 PROGRAM_APPLICATION_ID = PROGRAM_APPLICATION_ID ,
571 PROGRAM_ID = PROGRAM_ID ,
572 PROGRAM_UPDATE_DATE = P_DML_RECORD.SYSDATE,
573 FINANCIAL_NUMBER = decode(l_fin_num(j), P_DML_RECORD.GMISS_CHAR, null, l_fin_num(j)),
574 FINANCIAL_NUMBER_CURRENCY = decode(l_fin_num_cur(j), P_DML_RECORD.GMISS_CHAR, null, l_fin_num_cur(j)),
575 PROJECTED_ACTUAL_FLAG = decode(l_proj_act_flag(j), P_DML_RECORD.GMISS_CHAR, 'A', null, 'A', l_proj_act_flag(j)),
576 FINANCIAL_UNITS_APPLIED = decode(l_fin_units_applied(j), P_DML_RECORD.GMISS_CHAR, null, l_fin_units_applied(j)),
577 REQUEST_ID = P_DML_RECORD.REQUEST_ID,
578 LAST_UPDATE_LOGIN = P_DML_RECORD.USER_ID,
579 LAST_UPDATE_DATE = P_DML_RECORD.SYSDATE,
580 LAST_UPDATED_BY = P_DML_RECORD.USER_ID,
581 OBJECT_VERSION_NUMBER = NVL(OBJECT_VERSION_NUMBER,1)+1,
582 -- CREATED_BY_MODULE = NVL(CREATED_BY_MODULE, decode(l_created_by_module(j),P_DML_RECORD.GMISS_CHAR, CREATED_BY_MODULE, null, CREATED_BY_MODULE,l_created_by_module(j))),
583 -- do not update application_id if old value exists
584 APPLICATION_ID = NVL(APPLICATION_ID, P_DML_RECORD.APPLICATION_ID)
585 where FINANCIAL_NUMBER_ID = l_fn_id(j)
586 --and l_fin_num_name_err(j) is not null
587 and l_action_error_flag(j) is not null
588 and l_error_flag(j) is null;
589
590 report_errors(P_DML_RECORD, l_dml_exception);
591
592 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
593 hz_utility_v2pub.debug(p_message=>'FN:process_update_finnumbers()-',
594 p_prefix=>l_debug_prefix,
595 p_msg_level=>fnd_log.level_procedure);
596 END IF;
597
598 EXCEPTION
599 WHEN OTHERS THEN
600 ----dbms_output.put_line('Update finnumbers other exception: ' || SQLERRM);
601 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Update finnumbers other exception: ' || SQLERRM);
602
603 ROLLBACK to process_update_finnumbers_pvt;
604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
605
606 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
607 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
608 FND_MSG_PUB.ADD;
609
610 END process_update_finnumbers;
611
612
613 PROCEDURE report_errors(
614 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
615 P_DML_EXCEPTION IN VARCHAR2
616 ) IS
617
618 num_exp NUMBER;
619 exp_ind NUMBER := 1;
620 l_debug_prefix VARCHAR2(30) := '';
621 BEGIN
622 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
623 hz_utility_v2pub.debug(p_message=>'FN:report_errors()+',
624 p_prefix=>l_debug_prefix,
625 p_msg_level=>fnd_log.level_procedure);
626 END IF;
627
628 /**********************************/
629 /* Validation and Error reporting */
630 /**********************************/
631 IF l_fn_id.count = 0 THEN
632 return;
633 END IF;
634
635 l_num_row_processed := null;
636 l_num_row_processed := NUMBER_COLUMN();
637 l_num_row_processed.extend(l_fr_id.count);
638 l_exception_exists := null;
639 l_exception_exists := FLAG_ERROR();
640 l_exception_exists.extend(l_fr_id.count);
641 num_exp := SQL%BULK_EXCEPTIONS.COUNT;
642
643 FOR k IN 1..l_fn_id.count LOOP
644 IF SQL%BULK_ROWCOUNT(k) = 0
645 THEN
646 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
647 hz_utility_v2pub.debug(p_message=>'DML fails at ' || k,
648 p_prefix=>'ERROR',
649 p_msg_level=>fnd_log.level_error);
650 END IF;
651 l_num_row_processed(k) := 0;
652
653 /* Check for any exceptions during DML */
654 IF P_DML_EXCEPTION = 'Y' THEN
655 /* determine if exception at this index */
656 FOR i IN exp_ind..num_exp LOOP
657 IF SQL%BULK_EXCEPTIONS(i).ERROR_INDEX = k THEN
658 l_exception_exists(k) := 'Y';
659 ELSIF SQL%BULK_EXCEPTIONS(i).ERROR_INDEX > k THEN
660 EXIT;
661 END IF;
662 END LOOP;
663 END IF; /* P_DML_EXCEPTION = 'Y' */
664 ELSE
665 l_num_row_processed(k) := 1;
666 END IF; /* SQL%BULK_ROWCOUNT(k) = 0 */
667 END LOOP;
668
669 /* insert into tmp error tables */
670 forall j in 1..l_fr_id.count
671 insert into hz_imp_tmp_errors
672 (
673 request_id,
674 batch_id,
675 int_row_id,
676 interface_table_name,
677 error_id,
678 creation_date,
679 created_by,
680 last_update_date,
681 last_updated_by,
682 last_update_login,
683 program_application_id,
684 program_id,
685 program_update_date,
686 ACTION_MISMATCH_FLAG,
687 e1_flag,
688 e2_flag,
689 OTHER_EXCEP_FLAG,
690 MISSING_PARENT_FLAG
691 )
692 (
693 select P_DML_RECORD.REQUEST_ID,
694 P_DML_RECORD.BATCH_ID,
695 l_row_id(j),
696 'HZ_IMP_FINNUMBERS_INT',
697 HZ_IMP_ERRORS_S.NextVal,
698 P_DML_RECORD.SYSDATE,
699 P_DML_RECORD.USER_ID,
700 P_DML_RECORD.SYSDATE,
701 P_DML_RECORD.USER_ID,
702 P_DML_RECORD.LAST_UPDATE_LOGIN,
703 P_DML_RECORD.PROGRAM_APPLICATION_ID,
704 P_DML_RECORD.PROGRAM_ID,
705 P_DML_RECORD.SYSDATE,
706 l_action_error_flag(j),
707 'Y', --l_fin_num_name_err(j),
708 'Y',
709 l_exception_exists(j), 'Y'
710 from dual
711 where l_num_row_processed(j) = 0
712 );
713
714 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
715 hz_utility_v2pub.debug(p_message=>'FN:report_errors()-',
716 p_prefix=>l_debug_prefix,
717 p_msg_level=>fnd_log.level_procedure);
718 END IF;
719 END report_errors;
720
721 --------------------------------------
722 -- private procedures and functions
723 --------------------------------------
724 --------------------------------------
725 /*PROCEDURE enable_debug IS
726 BEGIN
727 g_debug_count := g_debug_count + 1;
728
729 IF g_debug_count = 1 THEN
730 IF fnd_profile.value('HZ_API_FILE_DEBUG_ON') = 'Y' OR
731 fnd_profile.value('HZ_API_DBMS_DEBUG_ON') = 'Y'
732 THEN
733 hz_utility_v2pub.enable_debug;
734 g_debug := TRUE;
735 END IF;
736 END IF;
737 END enable_debug; -- end procedure
738 */
739 --------------------------------------
740 --------------------------------------
741 /*PROCEDURE disable_debug IS
742 BEGIN
743
744 IF g_debug THEN
745 g_debug_count := g_debug_count - 1;
746 IF g_debug_count = 0 THEN
747 hz_utility_v2pub.disable_debug;
748 g_debug := FALSE;
749 END IF;
750 END IF;
751
752 END disable_debug;
753 */
754
755
756 END HZ_IMP_LOAD_FINNUMBERS_PKG;