[Home] [Help]
PACKAGE BODY: APPS.HZ_IMP_LOAD_FINREPORTS_PKG
Source
1 PACKAGE BODY HZ_IMP_LOAD_FINREPORTS_PKG AS
2 /*$Header: ARHLFNRB.pls 120.5 2006/01/17 08:46:06 vravicha noship $*/
3
4
5
6 g_debug_count NUMBER := 0;
7 --g_debug BOOLEAN := FALSE;
8
9 l_parent_party_id PARENT_PARTY_ID;
10 l_fr_id FR_ID;
11 l_party_id PARTY_ID;
12 l_audit_ind AUDIT_IND;
13 l_consolidated_ind CONSOLIDATED_IND;
14 l_date_rpt_issued DATE_REPORT_ISSUED;
15 l_doc_ref DOCUMENT_REFERENCE;
16 l_estimated_ind ESTIMATED_IND;
17 l_final_ind FINAL_IND;
18 l_fiscal_ind FISCAL_IND;
19 l_forecast_ind FORECAST_IND;
20 l_issued_period ISSUED_PERIOD;
21 l_opening_ind OPENING_IND;
22 l_proforma_ind PROFORMA_IND;
23 l_qualified_ind QUALIFIED_IND;
24 l_rpt_start_date REPORT_START_DATE;
25 l_rpt_end_date REPORT_END_DATE;
26 l_req_auth REQUIRING_AUTHORITY;
27 l_restated_ind RESTATED_IND;
28 l_signed_by_prin SIGNED_BY_PRINCIPALS_IND;
29 l_trial_balance_ind TRIAL_BALANCE_IND;
30 l_type_of_finreport TYPE_OF_FINANCIAL_REPORT;
31 l_unbal_ind UNBALANCED_IND;
32 l_created_by_module CREATED_BY_MODULE;
33
34
35 l_audit_ind_err FLAG_ERROR;
36 l_consolidated_ind_err FLAG_ERROR;
37 l_estimated_ind_err FLAG_ERROR;
38 l_final_ind_err FLAG_ERROR;
39 l_fiscal_ind_err FLAG_ERROR;
40 l_forecast_ind_err FLAG_ERROR;
41 l_opening_ind_err FLAG_ERROR;
42 l_proforma_ind_err FLAG_ERROR;
43 l_qualified_ind_err FLAG_ERROR;
44 l_restated_ind_err FLAG_ERROR;
45 l_signed_by_prin_err FLAG_ERROR;
46 l_trial_balance_ind_err FLAG_ERROR;
47 l_unbal_ind_err FLAG_ERROR;
48 l_date_err FLAG_ERROR;
49 l_action_error_flag FLAG_ERROR;
50 l_error_flag FLAG_ERROR;
51 l_date_comb_flag FLAG_ERROR;
52 l_rpt_date_flag FLAG_ERROR;
53 l_action_flag ACTION_FLAG;
54
55 /* Keep track of rows that do not get inserted or updated successfully.
56 Those are the rows that have some validation or DML errors.
57 Use this when inserting into or updating other tables so that we
58 do not need to check all the validation arrays. */
59 l_exception_exists FLAG_ERROR;
60 l_num_row_processed NUMBER_COLUMN;
61 l_row_id ROWID;
62 l_errm varchar2(100);
63
64 --------------------------------------
65 -- forward declaration of private procedures and functions
66 --------------------------------------
67
68 /*PROCEDURE enable_debug;
69 PROCEDURE disable_debug;
70 */
71
72 PROCEDURE process_insert_reports (
73 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
74 x_return_status OUT NOCOPY VARCHAR2,
75 x_msg_count OUT NOCOPY NUMBER,
76 x_msg_data OUT NOCOPY VARCHAR2 );
77
78 PROCEDURE process_update_reports (
79 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
80 x_return_status OUT NOCOPY VARCHAR2,
81 x_msg_count OUT NOCOPY NUMBER,
82 x_msg_data OUT NOCOPY VARCHAR2 );
83
84 PROCEDURE open_update_cursor (
85 update_cursor IN OUT NOCOPY update_cursor_type,
86 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE);
87
88 PROCEDURE report_errors(
89 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
90 P_DML_EXCEPTION IN VARCHAR2);
91
92 PROCEDURE populate_error_table(
93 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
94 P_DUP_VAL_EXP IN VARCHAR2,
95 P_SQL_ERRM IN VARCHAR2 );
96
97 PROCEDURE load_finreports (
98 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
99 x_return_status OUT NOCOPY VARCHAR2,
100 x_msg_count OUT NOCOPY NUMBER,
101 x_msg_data OUT NOCOPY VARCHAR2 )
102 IS
103 l_debug_prefix VARCHAR2(30) := '';
104 BEGIN
105 savepoint load_finreports_pvt;
106 FND_MSG_PUB.initialize;
107 x_return_status := FND_API.G_RET_STS_SUCCESS;
108
109 --enable_debug;
110 -- Debug info.
111 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
112 hz_utility_v2pub.debug(p_message=>'FR:load_finreports()+',
113 p_prefix=>l_debug_prefix,
114 p_msg_level=>fnd_log.level_procedure);
115 END IF;
116
117 process_insert_reports(P_DML_RECORD,
118 x_return_status, x_msg_count, x_msg_data );
119
120 IF x_return_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
121 process_update_reports(P_DML_RECORD,
122 x_return_status, x_msg_count, x_msg_data );
123 END IF;
124
125 -- Debug info.
126 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
127 hz_utility_v2pub.debug(p_message=>'FR:load_finreports()-',
128 p_prefix=>l_debug_prefix,
129 p_msg_level=>fnd_log.level_procedure);
130 END IF;
131 --disable_debug;
132
133 EXCEPTION
134
135 WHEN FND_API.G_EXC_ERROR THEN
136 --dbms_output.put_line('load finreport exception ' || SQLERRM);
137
138 FND_FILE.put_line(fnd_file.log,'Execution error occurs while loading financial reports');
139 FND_FILE.put_line(fnd_file.log, SQLERRM);
140 ROLLBACK TO load_finreports_pvt;
141 x_return_status := FND_API.G_RET_STS_ERROR;
142 FND_MSG_PUB.Count_And_Get(
143 p_encoded => FND_API.G_FALSE,
144 p_count => x_msg_count,
145 p_data => x_msg_data);
146
147 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
148 --dbms_output.put_line('load finreport exception ' || SQLERRM);
149
150 ROLLBACK TO load_finreports_pvt;
151 FND_FILE.put_line(fnd_file.log,'Unexpected error occurs while loading financial reports');
152 FND_FILE.put_line(fnd_file.log, SQLERRM);
153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
154 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
155 FND_MESSAGE.SET_TOKEN('ERROR', SQLERRM);
156 FND_MSG_PUB.ADD;
157 FND_MSG_PUB.Count_And_Get(
158 p_encoded => FND_API.G_FALSE,
159 p_count => x_msg_count,
160 p_data => x_msg_data);
161
162 WHEN OTHERS THEN
163 --dbms_output.put_line('load finreport exception ' || SQLERRM);
164
165 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
166 hz_utility_v2pub.debug(p_message=>'load_finreports Exception:',
167 p_prefix=>'SQL ERROR',
168 p_msg_level=>fnd_log.level_error);
169 hz_utility_v2pub.debug(p_message=>SQLERRM,
170 p_prefix=>'SQL ERROR',
171 p_msg_level=>fnd_log.level_error);
172 END IF;
173
174 ROLLBACK TO load_finreports_pvt;
175 FND_FILE.put_line(fnd_file.log,'Unexpected error occurs while loading financial reports');
176 FND_FILE.put_line(fnd_file.log, SQLERRM);
177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
178 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
179 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
180 FND_MSG_PUB.ADD;
181 FND_MSG_PUB.Count_And_Get(
182 p_encoded => FND_API.G_FALSE,
183 p_count => x_msg_count,
184 p_data => x_msg_data);
185
186 END load_finreports;
187
188
189 PROCEDURE process_insert_reports (
190 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
191 x_return_status OUT NOCOPY VARCHAR2,
192 x_msg_count OUT NOCOPY NUMBER,
193 x_msg_data OUT NOCOPY VARCHAR2 ) IS
194
195 l_sql_query VARCHAR2(15000) := 'begin insert all
196 when (audit_ind_err is not null -- include all the validation
197 and consolidated_ind_err is not null
198 and estimated_ind_err is not null
199 and final_ind_err is not null
200 and fiscal_ind_err is not null
201 and forecast_ind_err is not null
202 and opening_ind_err is not null
203 and proforma_ind_err is not null
204 and qualified_ind_err is not null
205 and restated_ind_err is not null
206 and signed_by_prin_err is not null
207 and trial_balance_ind_err is not null
208 and unbal_ind_err is not null
209 and date_err is not null
210 and action_mismatch_error is not null
211 and parent_party_id is not null
212 and party_id is not null
213 and date_comb_err is not null
214 and rpt_date_err is not null
215 and createdby_error is not null
216 ) then
217 into hz_financial_reports (
218 actual_content_source,
219 content_source_type,
220 created_by,
221 creation_date,
222 last_updated_by,
223 last_update_date,
224 last_update_login,
225 program_id,
226 program_application_id,
227 program_update_date,
228 application_id,
229 request_id,
230 FINANCIAL_REPORT_ID,
231 PARTY_ID,
232 STATUS,
233 OBJECT_VERSION_NUMBER,
234 AUDIT_IND,
235 CONSOLIDATED_IND,
236 CREATED_BY_MODULE,
237 DATE_REPORT_ISSUED,
238 DOCUMENT_REFERENCE,
239 ESTIMATED_IND,
240 FINAL_IND,
241 FISCAL_IND,
242 FORECAST_IND,
243 ISSUED_PERIOD,
244 OPENING_IND,
245 PROFORMA_IND,
246 QUALIFIED_IND,
247 REPORT_END_DATE,
248 REPORT_START_DATE,
249 REQUIRING_AUTHORITY,
250 RESTATED_IND,
251 SIGNED_BY_PRINCIPALS_IND,
252 TRIAL_BALANCE_IND,
253 TYPE_OF_FINANCIAL_REPORT,
254 UNBALANCED_IND)
255 values (
256 :1, -- actual_content_source
257 ''USER_ENTERED'', -- content_source_type
258 :2, -- created_by
259 :3, -- creation_date
260 :2, -- last_updated_by
261 :3, -- last_update_date
262 :4, -- last_update_login
263 :5, -- program_id
264 :6, -- program_application_id
265 :3, -- program_update_date
266 :7, -- application_id
267 :8, -- request_id
268 financial_report_id,
269 party_id,
270 ''A'',
271 1,
272 audit_ind,
273 consolidated_ind,
274 created_by_module,
275 date_report_issued,
276 document_reference,
277 estimated_ind,
278 final_ind,
279 fiscal_ind,
280 forecast_ind,
281 issued_period,
282 opening_ind,
283 proforma_ind,
284 qualified_ind,
285 report_end_date,
286 report_start_date,
287 requiring_authority,
288 restated_ind,
289 signed_by_principals_ind,
290 trial_balance_ind,
291 type_of_financial_report,
292 unbalanced_ind)
293 else
294 into hz_imp_tmp_errors (
295 error_id,
296 created_by,
297 creation_date,
298 last_updated_by,
299 last_update_date,
300 last_update_login,
301 program_id,
302 program_application_id,
303 program_update_date,
304 batch_id,
305 request_id,
306 int_row_id,
307 interface_table_name,
308 ACTION_MISMATCH_FLAG,
309 MISSING_PARENT_FLAG,
310 e1_flag,
311 e2_flag,
312 e3_flag,
313 e4_flag,
314 e5_flag,
315 e6_flag,
316 e7_flag,
317 e8_flag,
318 e9_flag,
319 e10_flag,
320 e11_flag,
321 e12_flag,
322 e13_flag,
323 e14_flag,
324 e15_flag,
325 e16_flag,
326 e17_flag)
327 values (
328 hz_imp_errors_s.nextval,
329 :2, -- created_by
330 :3, -- creation_date
331 :2, -- last_updated_by
332 :3, -- last_update_date
333 :4, -- last_update_login
334 :5, -- program_id
335 :6, -- program_application_id
336 :3, -- program_update_date
337 :9,
338 :8,
339 row_id,
340 ''HZ_IMP_FINREPORTS_INT'',
341 action_mismatch_error,
342 nvl2(parent_party_id, ''Y'', null),
343 date_err,
344 audit_ind_err,
345 consolidated_ind_err,
346 estimated_ind_err,
347 final_ind_err,
348 fiscal_ind_err,
349 forecast_ind_err,
350 opening_ind_err,
351 proforma_ind_err,
352 qualified_ind_err,
353 restated_ind_err,
354 signed_by_prin_err,
355 trial_balance_ind_err,
356 unbal_ind_err,
357 date_comb_err, -- date combination error
358 rpt_date_err, -- only one report date provided
359 createdby_error
360 )
361 select row_id,
362 financial_report_id,
363 parent_party_id,
364 party_id,
365 audit_ind,
366 consolidated_ind,
370 estimated_ind,
367 created_by_module,
368 date_report_issued,
369 document_reference,
371 final_ind,
372 fiscal_ind,
373 forecast_ind,
374 issued_period,
375 opening_ind,
376 proforma_ind,
377 qualified_ind,
378 report_end_date,
379 report_start_date,
380 requiring_authority,
381 restated_ind,
382 signed_by_principals_ind,
383 trial_balance_ind,
384 type_of_financial_report,
385 unbalanced_ind,
386 decode(audit_ind, null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null) audit_ind_err,
387 decode(consolidated_ind, null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null) consolidated_ind_err,
388 decode(estimated_ind, null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null) estimated_ind_err,
389 decode(final_ind, null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null) final_ind_err,
390 decode(fiscal_ind, null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null) fiscal_ind_err,
391 decode(forecast_ind, null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null) forecast_ind_err,
392 decode(opening_ind, null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null) opening_ind_err,
393 decode(proforma_ind, null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null) proforma_ind_err,
394 decode(qualified_ind, null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null) qualified_ind_err,
395 decode(restated_ind, null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null) restated_ind_err,
396 decode(signed_by_principals_ind, null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null) signed_by_prin_err,
397 decode(trial_balance_ind, null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null) trial_balance_ind_err,
398 decode(unbalanced_ind, null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null) unbal_ind_err,
399 nvl2(report_start_date, nvl2(report_end_date, decode(sign(report_end_date-report_start_date), -1, null, ''Y''), ''Y''), ''Y'') date_err,
400 nvl2(nullif(insert_update_flag, action_flag), null, ''Y'') action_mismatch_error,
401 nvl2(ISSUED_PERIOD, nvl2(REPORT_START_DATE, null, ''Y''), nvl2(REPORT_START_DATE, ''Y'', null)) date_comb_err,
402 nvl2(REPORT_START_DATE, nvl2(REPORT_END_DATE, ''Y'', null), nvl2(REPORT_END_DATE, null, ''Y'')) rpt_date_err,
403 createdby_error
404 from (
405 select /*+ leading(fr_sg) use_nl(fr_int) rowid(fr_int) */
406 hp.party_id parent_party_id,
407 fr_int.rowid row_id,
408 nullif(fr_int.insert_update_flag, :10) insert_update_flag,
409 fr_sg.error_flag,
410 fr_sg.action_flag,
411 fr_sg.financial_report_id,
412 fr_sg.party_id,
413 nullif(fr_int.audit_ind, :10) audit_ind,
414 nullif(fr_int.consolidated_ind, :10) consolidated_ind,
415 nvl(nullif(fr_int.created_by_module, :10), ''HZ_IMPORT'') created_by_module,
416 nvl2(nullif(fr_int.created_by_module, :10), nvl2(createdby_l.lookup_code,''Y'',null), ''Y'') createdby_error,
417 nullif(fr_int.date_report_issued, :11) date_report_issued,
418 fr_int.document_reference, -- logical key, cannot be null or g-miss
419 nullif(fr_int.estimated_ind, :10) estimated_ind,
420 nullif(fr_int.final_ind, :10) final_ind,
421 nullif(fr_int.fiscal_ind, :10) fiscal_ind,
422 nullif(fr_int.forecast_ind, :10) forecast_ind,
423 nullif(fr_int.issued_period, :10) issued_period,
424 nullif(fr_int.opening_ind, :10) opening_ind,
425 nullif(fr_int.proforma_ind, :10) proforma_ind,
426 nullif(fr_int.qualified_ind, :10) qualified_ind,
427 nullif(fr_int.report_end_date, :11) report_end_date,
428 nullif(fr_int.report_start_date, :11) report_start_date,
429 nullif(fr_int.requiring_authority, :10) requiring_authority,
430 nullif(fr_int.restated_ind, :10) restated_ind,
431 nullif(fr_int.signed_by_principals_ind, :10) signed_by_principals_ind,
432 nullif(fr_int.trial_balance_ind, :10) trial_balance_ind,
433 fr_int.type_of_financial_report, -- logical key, cannot be null or g-miss
434 nullif(fr_int.unbalanced_ind, :10) unbalanced_ind
435 FROM hz_imp_finreports_int fr_int,
436 hz_imp_finreports_sg fr_sg,
437 hz_parties hp,
438 fnd_lookup_values createdby_l
439 WHERE fr_sg.party_orig_system = :12
440 AND fr_sg.party_orig_system_reference between :13 AND :14
441 AND fr_int.rowid = fr_sg.int_row_id
442 AND fr_sg.action_flag = ''I''
443 AND fr_sg.batch_mode_flag = :15
444 AND fr_sg.batch_id = :9
445 AND hp.party_id (+) = fr_sg.party_id
446 AND hp.status (+) = ''A''
447 AND createdby_l.lookup_code (+) = fr_int.created_by_module
448 AND createdby_l.lookup_type (+) = ''HZ_CREATED_BY_MODULES''
449 AND createdby_l.language (+) = userenv(''LANG'')
450 AND createdby_l.view_application_id (+) = 222
451 AND createdby_l.security_group_id (+) =
452 fnd_global.lookup_security_group(''HZ_CREATED_BY_MODULES'', 222)
453 ' ;
454
455 l_where_enabled_lookup_sql varchar2(3000) :=
456 ' AND ( createdby_l.ENABLED_FLAG(+) = ''Y'' AND
457 TRUNC(:3) BETWEEN
458 TRUNC(NVL( createdby_l.START_DATE_ACTIVE,:3 ) ) AND
459 TRUNC(NVL( createdby_l.END_DATE_ACTIVE,:3 ) ) )
460 ';
461 /*
462 Fix bug 4175285: Remove duplicate selection.Since parties with same OS+OSR but different
463 party_id can exist in a batch, when we querying, duplicate records may be created.
464 E.g. There are 2 parties in a DNB batch:
465 OS OSR PID STATUS
466 ---------------------------
467 DNB 456 1002 A
468 DNB 456 1003 A
469
470 The Status will set to 'I' after stage 3. Without this where clause:
471 'AND party_mosr.party_id = nvl(fr_sg.party_id,party_mosr.party_id)'
472 The above query will return duplicate records for the same fin report and raise
473 _U1 Unique index constraint error.
474
475 */
476 l_sql_query_end varchar2(15000):= '); end;';
480 l_debug_prefix VARCHAR2(30) := '';
477 l_first_run_clause varchar2(40) := ' AND fr_int.interface_status is null';
478 l_re_run_clause varchar2(40) := ' AND fr_int.interface_status = ''C''';
479 l_final_qry varchar2(15000);
481 BEGIN
482 -- Debug info.
483
484 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
485 hz_utility_v2pub.debug(p_message=>'FR:process_insert_reports()+',
486 p_prefix=>l_debug_prefix,
487 p_msg_level=>fnd_log.level_procedure);
488 END IF;
489
490 savepoint process_insert_finreports_pvt;
491 x_return_status := FND_API.G_RET_STS_SUCCESS;
492
493 -- add clause for first-run/re-run
494 IF P_DML_RECORD.ALLOW_DISABLED_LOOKUP = 'Y' THEN
495 if(P_DML_RECORD.RERUN='N') then
496 l_final_qry := l_sql_query || l_first_run_clause;
497 else
498 l_final_qry := l_sql_query || l_re_run_clause;
499 end if;
500 ELSE
501 if(P_DML_RECORD.RERUN='N') then
502 l_final_qry := l_sql_query || l_first_run_clause || l_where_enabled_lookup_sql ;
503 else
504 l_final_qry := l_sql_query || l_re_run_clause || l_where_enabled_lookup_sql ;
505 end if;
506 END IF;
507
508
509
510 l_final_qry := l_final_qry || l_sql_query_end;
511
512 execute immediate l_final_qry
513 using
514 P_DML_RECORD.ACTUAL_CONTENT_SRC,
515 P_DML_RECORD.USER_ID, P_DML_RECORD.SYSDATE,
516 P_DML_RECORD.LAST_UPDATE_LOGIN, P_DML_RECORD.PROGRAM_ID,
517 P_DML_RECORD.PROGRAM_APPLICATION_ID, P_DML_RECORD.APPLICATION_ID,
518 P_DML_RECORD.REQUEST_ID, P_DML_RECORD.BATCH_ID,
519 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_DATE,
520 P_DML_RECORD.OS, P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR,
521 P_DML_RECORD.BATCH_MODE_FLAG;
522
523 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
524 hz_utility_v2pub.debug(p_message=>'FR:process_insert_reports()-',
525 p_prefix=>l_debug_prefix,
526 p_msg_level=>fnd_log.level_procedure);
527 END IF;
528
529 EXCEPTION
530 WHEN DUP_VAL_ON_INDEX THEN
531
532 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert fin reports dup val exception: ' || SQLERRM);
533 ROLLBACK to process_insert_finreports_pvt;
534
535 populate_error_table(P_DML_RECORD, 'Y', sqlerrm);
536 x_return_status := FND_API.G_RET_STS_ERROR;
537
538 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
539 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
540 FND_MSG_PUB.ADD;
541
542 WHEN OTHERS THEN
543
544 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert fin reports other exception: ' || SQLERRM);
545 ROLLBACK to process_insert_finreports_pvt;
546
547 populate_error_table(P_DML_RECORD, 'N', sqlerrm);
548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
549
550 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
551 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
552 FND_MSG_PUB.ADD;
553
554 END process_insert_reports;
555
556
557 PROCEDURE populate_error_table(
558 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
559 P_DUP_VAL_EXP IN VARCHAR2,
560 P_SQL_ERRM IN VARCHAR2 ) IS
561
562 dup_val_exp_val VARCHAR2(1) := null;
563 other_exp_val VARCHAR2(1) := 'Y';
564 l_debug_prefix VARCHAR2(30) := '';
565 BEGIN
566
567 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
568 hz_utility_v2pub.debug(p_message=>'FR:populate_error_table()+',
569 p_prefix=>l_debug_prefix,
570 p_msg_level=>fnd_log.level_procedure);
571 END IF;
572
573 /* other entities need to add checking for other constraints */
574 if (P_DUP_VAL_EXP = 'Y') then
575 other_exp_val := null;
576 if(instr(P_SQL_ERRM, '_U1')<>0) then
577 dup_val_exp_val := 'A';
578 else -- '_U2'
579 dup_val_exp_val := 'B';
580 end if;
581 end if;
582
583 insert into hz_imp_tmp_errors
584 (
585 request_id,
586 batch_id,
587 int_row_id,
588 interface_table_name,
589 error_id,
590 creation_date,
591 created_by,
592 last_update_date,
593 last_updated_by,
594 last_update_login,
595 program_application_id,
596 program_id,
597 program_update_date,
598 DUP_VAL_IDX_EXCEP_FLAG,
599 e1_flag,e2_flag,e3_flag,e4_flag,e5_flag,
600 e6_flag,e7_flag,e8_flag,e9_flag,e10_flag,
601 e11_flag,e12_flag,e13_flag,e14_flag,
602 e15_flag, e16_flag,
603 e17_flag,
604 OTHER_EXCEP_FLAG,
605 missing_parent_flag
606 )
607 (
608 select P_DML_RECORD.REQUEST_ID,
609 P_DML_RECORD.BATCH_ID,
610 fr_sg.int_row_id,
611 'HZ_IMP_FINREPORTS_INT',
612 HZ_IMP_ERRORS_S.NextVal,
613 P_DML_RECORD.SYSDATE,
614 P_DML_RECORD.USER_ID,
615 P_DML_RECORD.SYSDATE,
616 P_DML_RECORD.USER_ID,
617 P_DML_RECORD.LAST_UPDATE_LOGIN,
618 P_DML_RECORD.PROGRAM_APPLICATION_ID,
619 P_DML_RECORD.PROGRAM_ID,
620 P_DML_RECORD.SYSDATE,
621 dup_val_exp_val,
622 -- this function report errors for exception
623 -- not checking all other potential errors
624 'Y','Y','Y','Y','Y','Y','Y', 'Y',
625 'Y','Y','Y','Y','Y','Y','Y', 'Y',
629 where fr_sg.action_flag = 'I'
626 'Y',
627 other_exp_val, 'Y'
628 from hz_imp_finreports_sg fr_sg
630 and fr_sg.batch_id = P_DML_RECORD.BATCH_ID
631 and fr_sg.party_orig_system = P_DML_RECORD.OS
632 and fr_sg.party_orig_system_reference
633 between P_DML_RECORD.FROM_OSR and P_DML_RECORD.TO_OSR
634 );
635
636 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
637 hz_utility_v2pub.debug(p_message=>'FR:populate_error_table()-',
638 p_prefix=>l_debug_prefix,
639 p_msg_level=>fnd_log.level_procedure);
640 END IF;
641
642 END populate_error_table;
643
644
645 PROCEDURE process_update_reports (
646 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
647 x_return_status OUT NOCOPY VARCHAR2,
648 x_msg_count OUT NOCOPY NUMBER,
649 x_msg_data OUT NOCOPY VARCHAR2 ) IS
650 c_update_cursor update_cursor_type;
651 l_dml_exception varchar2(1) := 'N';
652 l_debug_prefix VARCHAR2(30) := '';
653 BEGIN
654 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
655 hz_utility_v2pub.debug(p_message=>'process_update_reports()+',
656 p_prefix=>l_debug_prefix,
657 p_msg_level=>fnd_log.level_procedure);
658 END IF;
659 savepoint process_update_reports_pvt;
660 FND_MSG_PUB.initialize;
661 --Initialize API return status to success.
662 x_return_status := FND_API.G_RET_STS_SUCCESS;
663
664 open_update_cursor(c_update_cursor, P_DML_RECORD);
665 fetch c_update_cursor bulk collect into
666 l_row_id,
667 l_action_error_flag,
668 l_action_flag,
669 l_fr_id,
670 l_party_id,
671 l_audit_ind,
672 l_consolidated_ind,
673 l_created_by_module,
674 l_date_rpt_issued,
675 l_doc_ref,
676 l_estimated_ind,
677 l_final_ind,
678 l_fiscal_ind,
679 l_forecast_ind,
680 l_issued_period,
681 l_opening_ind,
682 l_proforma_ind,
683 l_qualified_ind,
684 l_rpt_end_date,
685 l_rpt_start_date,
686 l_req_auth,
687 l_restated_ind,
688 l_signed_by_prin,
689 l_trial_balance_ind,
690 l_type_of_finreport,
691 l_unbal_ind,
692 l_audit_ind_err,
693 l_consolidated_ind_err,
694 l_estimated_ind_err,
695 l_final_ind_err,
696 l_fiscal_ind_err,
697 l_forecast_ind_err,
698 l_opening_ind_err,
699 l_proforma_ind_err,
700 l_qualified_ind_err,
701 l_restated_ind_err,
702 l_signed_by_prin_err,
703 l_trial_balance_ind_err,
704 l_unbal_ind_err,
705 l_date_err,
706 l_action_error_flag,
707 l_error_flag,
708 l_date_comb_flag,
709 l_rpt_date_flag;
710 close c_update_cursor;
711
712 forall j in 1..l_fr_id.count
713 update hz_financial_reports set
714 PROGRAM_ID = P_DML_RECORD.PROGRAM_ID,
715 PROGRAM_APPLICATION_ID = P_DML_RECORD.PROGRAM_APPLICATION_ID,
716 PROGRAM_UPDATE_DATE = P_DML_RECORD.SYSDATE,
717 REQUEST_ID = P_DML_RECORD.REQUEST_ID,
718 LAST_UPDATE_LOGIN = P_DML_RECORD.LAST_UPDATE_LOGIN,
719 LAST_UPDATE_DATE = P_DML_RECORD.SYSDATE,
720 LAST_UPDATED_BY = P_DML_RECORD.USER_ID,
721 OBJECT_VERSION_NUMBER = NVL(OBJECT_VERSION_NUMBER,1)+1,
722 APPLICATION_ID = NVL(APPLICATION_ID, P_DML_RECORD.APPLICATION_ID),
723 -- don't modify old value if new one is null
724 REQUIRING_AUTHORITY = DECODE(l_req_auth(j), NULL, REQUIRING_AUTHORITY, P_DML_RECORD.GMISS_CHAR, NULL, l_req_auth(j)),
725 AUDIT_IND = DECODE(l_audit_ind(j), NULL, AUDIT_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_audit_ind(j)),
726 CONSOLIDATED_IND = DECODE(l_consolidated_ind(j), NULL, CONSOLIDATED_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_consolidated_ind(j)),
727 ESTIMATED_IND = DECODE(l_estimated_ind(j), NULL, ESTIMATED_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_estimated_ind(j)),
728 FINAL_IND = DECODE(l_final_ind(j), NULL, FINAL_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_final_ind(j)),
729 FISCAL_IND = DECODE(l_fiscal_ind(j), NULL, FISCAL_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_fiscal_ind(j)),
730 FORECAST_IND = DECODE(l_forecast_ind(j), NULL, FORECAST_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_forecast_ind(j)),
731 OPENING_IND = DECODE(l_opening_ind(j), NULL, OPENING_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_opening_ind(j)),
732 PROFORMA_IND = DECODE(l_proforma_ind(j), NULL, PROFORMA_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_proforma_ind(j)),
733 QUALIFIED_IND = DECODE(l_qualified_ind(j), NULL, QUALIFIED_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_qualified_ind(j)),
734 RESTATED_IND = DECODE(l_restated_ind(j), NULL, RESTATED_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_restated_ind(j)),
735 TRIAL_BALANCE_IND = DECODE(l_trial_balance_ind(j), NULL, TRIAL_BALANCE_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_trial_balance_ind(j)),
736 UNBALANCED_IND = DECODE(l_unbal_ind(j), NULL, UNBALANCED_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_unbal_ind(j)),
737 SIGNED_BY_PRINCIPALS_IND = DECODE(l_signed_by_prin(j), NULL, SIGNED_BY_PRINCIPALS_IND, P_DML_RECORD.GMISS_CHAR, NULL, l_signed_by_prin(j))
738 -- 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)))
739 where FINANCIAL_REPORT_ID = l_fr_id(j)
740 and l_audit_ind_err(j) is not null
741 and l_consolidated_ind_err(j) is not null
742 and l_estimated_ind_err(j) is not null
746 and l_opening_ind_err(j) is not null
743 and l_final_ind_err(j) is not null
744 and l_fiscal_ind_err(j) is not null
745 and l_forecast_ind_err(j) is not null
747 and l_proforma_ind_err(j) is not null
748 and l_qualified_ind_err(j) is not null
749 and l_restated_ind_err(j) is not null
750 and l_signed_by_prin_err(j) is not null
751 and l_trial_balance_ind_err(j) is not null
752 and l_unbal_ind_err(j) is not null
753 and l_action_error_flag(j) is not null
754 and l_error_flag(j) is null
755 and l_date_comb_flag(j) is not null
756 and l_rpt_date_flag(j) is not null
757 ;
758
759 report_errors(P_DML_RECORD, l_dml_exception);
760
761 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
762 hz_utility_v2pub.debug(p_message=>'process_update_reports()-',
763 p_prefix=>l_debug_prefix,
764 p_msg_level=>fnd_log.level_procedure);
765 END IF;
766
767 EXCEPTION
768 WHEN OTHERS THEN
769 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Update reports other exception: ' || SQLERRM);
770
771 ROLLBACK to process_update_reports_pvt;
772 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
773
774 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
775 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
776 FND_MSG_PUB.ADD;
777
778 END process_update_reports;
779
780
781 PROCEDURE open_update_cursor (update_cursor IN OUT NOCOPY update_cursor_type,
782 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE
783 ) IS
784 l_sql_query varchar2(11000) :=
785 'SELECT /*+ leading(fr_sg) use_nl(fr_int) rowid(fr_int) */
786 fr_int.ROWID,
787 fr_int.insert_update_flag,
788 fr_sg.action_flag,
789 fr_sg.financial_report_id,
790 fr_sg.party_id,
791 fr_int.AUDIT_IND,
792 fr_int.CONSOLIDATED_IND,
793 fr_int.CREATED_BY_MODULE,
794 fr_int.DATE_REPORT_ISSUED,
795 fr_int.DOCUMENT_REFERENCE,
796 fr_int.ESTIMATED_IND,
797 fr_int.FINAL_IND,
798 fr_int.FISCAL_IND,
799 fr_int.FORECAST_IND,
800 fr_int.ISSUED_PERIOD,
801 fr_int.OPENING_IND,
802 fr_int.PROFORMA_IND,
803 fr_int.QUALIFIED_IND,
804 fr_int.REPORT_END_DATE,
805 fr_int.REPORT_START_DATE,
806 fr_int.REQUIRING_AUTHORITY,
807 fr_int.RESTATED_IND,
808 fr_int.SIGNED_BY_PRINCIPALS_IND,
809 fr_int.TRIAL_BALANCE_IND,
810 fr_int.TYPE_OF_FINANCIAL_REPORT,
811 fr_int.UNBALANCED_IND,
812 decode(fr_int.AUDIT_IND, :G_MISS_CHAR, ''Y'', null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null),
813 decode(fr_int.CONSOLIDATED_IND, :G_MISS_CHAR, ''Y'', null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null),
814 decode(fr_int.ESTIMATED_IND, :G_MISS_CHAR, ''Y'', null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null),
815 decode(fr_int.FINAL_IND, :G_MISS_CHAR, ''Y'', null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null),
816 decode(fr_int.FISCAL_IND, :G_MISS_CHAR, ''Y'', null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null),
817 decode(fr_int.FORECAST_IND, :G_MISS_CHAR, ''Y'', null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null),
818 decode(fr_int.OPENING_IND, :G_MISS_CHAR, ''Y'', null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null),
819 decode(fr_int.PROFORMA_IND, :G_MISS_CHAR, ''Y'', null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null),
820 decode(fr_int.QUALIFIED_IND, :G_MISS_CHAR, ''Y'', null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null),
821 decode(fr_int.RESTATED_IND, :G_MISS_CHAR, ''Y'', null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null),
822 decode(fr_int.SIGNED_BY_PRINCIPALS_IND, :G_MISS_CHAR, ''Y'', null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null),
823 decode(fr_int.TRIAL_BALANCE_IND, :G_MISS_CHAR, ''Y'', null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null),
824 decode(fr_int.UNBALANCED_IND, :G_MISS_CHAR, ''Y'', null, ''Y'', ''Y'', ''Y'', ''N'', ''Y'', null),
825 decode(fr_int.REPORT_END_DATE, :P_G_MISS_DATE, ''Y'',
826 decode(fr_int.REPORT_START_DATE, :P_G_MISS_DATE, ''Y'',
827 decode( nvl(fr_int.REPORT_END_DATE, hz_fr.REPORT_END_DATE) , null, ''Y'',
828 decode( nvl(fr_int.REPORT_START_DATE, hz_fr.REPORT_START_DATE) , null, ''Y'',
829 decode(sign(nvl(fr_int.REPORT_END_DATE, hz_fr.REPORT_END_DATE)- nvl(fr_int.REPORT_START_DATE, hz_fr.REPORT_START_DATE)), -1, null, ''Y''))))),
830 decode(nvl(fr_int.insert_update_flag, fr_sg.action_flag), fr_sg.action_flag, ''Y'', null) action_mismatch_error,
831 fr_sg.error_flag,
832 nvl2(fr_int.ISSUED_PERIOD, nvl2(fr_int.REPORT_START_DATE, null, ''Y''), nvl2(fr_int.REPORT_START_DATE, ''Y'', null)) date_comb_err,
833 nvl2(fr_int.REPORT_START_DATE, nvl2(fr_int.REPORT_END_DATE, ''Y'', null), nvl2(fr_int.REPORT_END_DATE, null, ''Y'')) rpt_date_err
834 FROM hz_imp_finreports_int fr_int,
835 hz_imp_finreports_sg fr_sg,
836 hz_financial_reports hz_fr
837 WHERE fr_sg.batch_id = :P_BATCH_ID
838 AND fr_sg.batch_mode_flag = :BATCH_MODE_FLAG
839 AND fr_sg.party_orig_system = :P_OS
840 AND fr_sg.party_orig_system_reference between :P_FROM_OSR AND :TO_OSR
841 AND fr_sg.action_flag = ''U''
842 AND fr_int.rowid = fr_sg.int_row_id
843 and hz_fr.financial_report_id = fr_sg.financial_report_id';
844
845 l_first_run_clause varchar2(40) := ' AND fr_int.interface_status is null';
846 l_re_run_clause varchar2(40) := ' AND fr_int.interface_status = ''C''';
847 l_debug_prefix VARCHAR2(30) := '';
848 BEGIN
849
850 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
851 hz_utility_v2pub.debug(p_message=>'FR:open_update_cursor()+',
852 p_prefix=>l_debug_prefix,
853 p_msg_level=>fnd_log.level_procedure);
854 END IF;
855
859 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
856 if(P_DML_RECORD.RERUN='Y') then
857 OPEN update_cursor FOR l_sql_query || l_re_run_clause
858 USING P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
860 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
861 P_DML_RECORD.GMISS_DATE, P_DML_RECORD.GMISS_DATE,
862 P_DML_RECORD.BATCH_ID, P_DML_RECORD.BATCH_MODE_FLAG,
863 P_DML_RECORD.OS, P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR;
864 else
865 OPEN update_cursor FOR l_sql_query || l_first_run_clause
866 USING P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
867 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
868 P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR, P_DML_RECORD.GMISS_CHAR,
869 P_DML_RECORD.GMISS_DATE, P_DML_RECORD.GMISS_DATE,
870 P_DML_RECORD.BATCH_ID, P_DML_RECORD.BATCH_MODE_FLAG,
871 P_DML_RECORD.OS, P_DML_RECORD.FROM_OSR, P_DML_RECORD.TO_OSR;
872 end if;
873
874 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
875 hz_utility_v2pub.debug(p_message=>'FR:open_update_cursor()-',
876 p_prefix=>l_debug_prefix,
877 p_msg_level=>fnd_log.level_procedure);
878 END IF;
879
880 END open_update_cursor;
881
882
883 PROCEDURE report_errors(
884 P_DML_RECORD IN HZ_IMP_LOAD_WRAPPER.DML_RECORD_TYPE,
885 P_DML_EXCEPTION IN VARCHAR2
886 ) IS
887
888 num_exp NUMBER;
889 exp_ind NUMBER := 1;
890 l_debug_prefix VARCHAR2(30) := '';
891 BEGIN
892 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
893 hz_utility_v2pub.debug(p_message=>'FR:report_errors()+',
894 p_prefix=>l_debug_prefix,
895 p_msg_level=>fnd_log.level_procedure);
896 END IF;
897
898 /**********************************/
899 /* Validation and Error reporting */
900 /**********************************/
901 IF l_fr_id.count = 0 THEN
902 return;
903 END IF;
904
905 l_num_row_processed := null;
906 l_num_row_processed := NUMBER_COLUMN();
907 l_num_row_processed.extend(l_fr_id.count);
908 l_exception_exists := null;
909 l_exception_exists := FLAG_ERROR();
910 l_exception_exists.extend(l_fr_id.count);
911 num_exp := SQL%BULK_EXCEPTIONS.COUNT;
912
913 FOR k IN 1..l_fr_id.count LOOP
914
915 IF SQL%BULK_ROWCOUNT(k) = 0 THEN
916 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
917 hz_utility_v2pub.debug(p_message=>'DML fails at ' || k,
918 p_prefix=>'ERROR',
919 p_msg_level=>fnd_log.level_error);
920 END IF;
921 l_num_row_processed(k) := 0;
922
923 /* Check for any exceptions during DML */
924 /* Note: Financial number update would not cause any */
925 /* dup val exception, other entities copying */
926 /* the code may need to take care of that. */
927 IF P_DML_EXCEPTION = 'Y' THEN
928 /* determine if exception at this index */
929 FOR i IN exp_ind..num_exp LOOP
930 IF SQL%BULK_EXCEPTIONS(i).ERROR_INDEX = k THEN
931 l_exception_exists(k) := 'Y';
932 ELSIF SQL%BULK_EXCEPTIONS(i).ERROR_INDEX > k THEN
933 EXIT;
934 END IF;
935 END LOOP;
936 END IF; /* P_DML_EXCEPTION = 'Y' */
937
938 ELSE
939 l_num_row_processed(k) := 1;
940 END IF; /* SQL%BULK_ROWCOUNT(k) = 0 */
941 END LOOP;
942
943 /* insert into tmp error tables */
944 forall j in 1..l_fr_id.count
945 insert into hz_imp_tmp_errors
946 (
947 request_id,
948 batch_id,
949 int_row_id,
950 interface_table_name,
951 error_id,
952 creation_date,
953 created_by,
954 last_update_date,
955 last_updated_by,
956 last_update_login,
957 program_application_id,
958 program_id,
959 program_update_date,
960 ACTION_MISMATCH_FLAG,
961 e1_flag,e2_flag,e3_flag,e4_flag,e5_flag,
962 e6_flag,e7_flag,e8_flag,e9_flag,e10_flag,
963 e11_flag,e12_flag,e13_flag,e14_flag,e15_flag,e16_flag,
964 e17_flag,
965 OTHER_EXCEP_FLAG,
966 MISSING_PARENT_FLAG
967 )
968 (
969 select P_DML_RECORD.REQUEST_ID,
970 P_DML_RECORD.BATCH_ID,
971 l_row_id(j),
972 'HZ_IMP_FINREPORTS_INT',
973 HZ_IMP_ERRORS_S.NextVal,
974 P_DML_RECORD.SYSDATE,
975 P_DML_RECORD.USER_ID,
976 P_DML_RECORD.SYSDATE,
977 P_DML_RECORD.USER_ID,
978 P_DML_RECORD.LAST_UPDATE_LOGIN,
979 P_DML_RECORD.PROGRAM_APPLICATION_ID,
980 P_DML_RECORD.PROGRAM_ID,
981 P_DML_RECORD.SYSDATE,
982 l_action_error_flag(j),
983 l_date_err(j), -- e1
984 l_audit_ind_err(j),
985 l_consolidated_ind_err(j),
986 l_estimated_ind_err(j),
987 l_final_ind_err(j),
988 l_fiscal_ind_err(j),
989 l_forecast_ind_err(j),
990 l_opening_ind_err(j),
991 l_proforma_ind_err(j),
992 l_qualified_ind_err(j),
993 l_restated_ind_err(j), -- e11
994 l_signed_by_prin_err(j),
995 l_trial_balance_ind_err(j),
999 'Y',
996 l_unbal_ind_err(j),
997 l_date_comb_flag(j),
998 l_rpt_date_flag(j),
1000 l_exception_exists(j), 'Y'
1001 from dual
1002 where l_num_row_processed(j) = 0
1003 );
1004
1005 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1006 hz_utility_v2pub.debug(p_message=>'FR:report_errors()-',
1007 p_prefix=>l_debug_prefix,
1008 p_msg_level=>fnd_log.level_procedure);
1009 END IF;
1010
1011 END report_errors;
1012
1013 --------------------------------------
1014 -- private procedures and functions
1015 --------------------------------------
1016 --------------------------------------
1017 /*PROCEDURE enable_debug IS
1018 BEGIN
1019 g_debug_count := g_debug_count + 1;
1020
1021 IF g_debug_count = 1 THEN
1022 IF fnd_profile.value('HZ_API_FILE_DEBUG_ON') = 'Y' OR
1023 fnd_profile.value('HZ_API_DBMS_DEBUG_ON') = 'Y'
1024 THEN
1025 hz_utility_v2pub.enable_debug;
1026 g_debug := TRUE;
1027 END IF;
1028 END IF;
1029 END enable_debug; -- end procedure
1030 */
1031 --------------------------------------
1032 --------------------------------------
1033 /*PROCEDURE disable_debug IS
1034 BEGIN
1035
1036 IF g_debug THEN
1037 g_debug_count := g_debug_count - 1;
1038 IF g_debug_count = 0 THEN
1039 hz_utility_v2pub.disable_debug;
1040 g_debug := FALSE;
1041 END IF;
1042 END IF;
1043
1044 END disable_debug;
1045 */
1046
1047
1048 END HZ_IMP_LOAD_FINREPORTS_PKG;