150: EDI Gateway data dictionary against what the database
151: actually has.
152:
153: If bErrors_found returns a TRUE, then the calling procedure
154: should so a select from ECE_OUTPUT where run_id = iRun_id
155: to see the errors.
156:
157: The calling routine should also clean up the ECE_OUTPUT
158: table when finished by deleting the records where
153: If bErrors_found returns a TRUE, then the calling procedure
154: should so a select from ECE_OUTPUT where run_id = iRun_id
155: to see the errors.
156:
157: The calling routine should also clean up the ECE_OUTPUT
158: table when finished by deleting the records where
159: run_id = iRun_id.
160: *********************************************************/
161: PROCEDURE SEED_DATA_CHECK (
229:
230: begin
231: /****************************************************
232: Get the next run id. This is the key for this run
233: in the ece_output table.
234: ****************************************************/
235: select ece_output_runs_s.nextval
236: into iRun_id from dual;
237:
231: /****************************************************
232: Get the next run id. This is the key for this run
233: in the ece_output table.
234: ****************************************************/
235: select ece_output_runs_s.nextval
236: into iRun_id from dual;
237:
238: begin
239: select direction
266: bErrors_found := TRUE;
267: x_msg := 'SEED: Document '||cTransaction_code||' not defined in ece_lookup_values';
268:
269: if bInsertErrors then
270: insert into ece_output (run_id,line_id,text)
271: values (iRun_id,
272: ece_output_lines_s.nextval,
273: x_msg);
274: end if;
268:
269: if bInsertErrors then
270: insert into ece_output (run_id,line_id,text)
271: values (iRun_id,
272: ece_output_lines_s.nextval,
273: x_msg);
274: end if;
275: end;
276:
290: when NO_DATA_FOUND then
291: bErrors_found := TRUE;
292: if bInsertErrors then
293: x_msg := 'SEED: Document types for '||cTransaction_code||' not defined in ece_lookup_values';
294: insert into ece_output (run_id,line_id,text)
295: values (iRun_id,
296: ece_output_lines_s.nextval,
297: x_msg);
298: end if;
292: if bInsertErrors then
293: x_msg := 'SEED: Document types for '||cTransaction_code||' not defined in ece_lookup_values';
294: insert into ece_output (run_id,line_id,text)
295: values (iRun_id,
296: ece_output_lines_s.nextval,
297: x_msg);
298: end if;
299:
300: when TOO_MANY_ROWS then
332: WHEN no_output_level then
333: bErrors_found := TRUE;
334: if bInsertErrors then
335: x_msg := 'SEED: Output level not defined in ECE_LOOKUP_VALUES: '||eit_rec.output_level;
336: insert into ece_output (run_id,line_id,text)
337: values (iRun_id,
338: ece_output_lines_s.nextval,
339: x_msg);
340: end if;
334: if bInsertErrors then
335: x_msg := 'SEED: Output level not defined in ECE_LOOKUP_VALUES: '||eit_rec.output_level;
336: insert into ece_output (run_id,line_id,text)
337: values (iRun_id,
338: ece_output_lines_s.nextval,
339: x_msg);
340: end if;
341: WHEN others then
342: bErrors_found := TRUE;
341: WHEN others then
342: bErrors_found := TRUE;
343: if bInsertErrors then
344: x_msg := 'SEED: Err in output_level check: '||SQLERRM;
345: insert into ece_output (run_id,line_id,text)
346: values (iRun_id,
347: ece_output_lines_s.nextval,
348: x_msg);
349: end if;
343: if bInsertErrors then
344: x_msg := 'SEED: Err in output_level check: '||SQLERRM;
345: insert into ece_output (run_id,line_id,text)
346: values (iRun_id,
347: ece_output_lines_s.nextval,
348: x_msg);
349: end if;
350: end;
351:
399: if bInsertErrors then
400: x_errtbl := eic_rec.base_table_name;
401: x_errcol := eic_rec.base_column_name;
402: x_msg := 'SEED: Column not found for '||x_errtbl ||'.'||x_errcol;
403: insert into ece_output (run_id,line_id,text)
404: values (iRun_id,
405: ece_output_lines_s.nextval,
406: x_msg);
407: end if;
401: x_errcol := eic_rec.base_column_name;
402: x_msg := 'SEED: Column not found for '||x_errtbl ||'.'||x_errcol;
403: insert into ece_output (run_id,line_id,text)
404: values (iRun_id,
405: ece_output_lines_s.nextval,
406: x_msg);
407: end if;
408: END;
409: end if;
450: if bInsertErrors then
451: x_errtbl := eic_rec.interface_table_name;
452: x_errcol := eic_rec.interface_column_name;
453: x_msg := 'SEED: Column not found for '||x_errtbl ||'.'||x_errcol;
454: insert into ece_output (run_id,line_id,text)
455: values (iRun_id,
456: ece_output_lines_s.nextval,
457: x_msg);
458: end if;
452: x_errcol := eic_rec.interface_column_name;
453: x_msg := 'SEED: Column not found for '||x_errtbl ||'.'||x_errcol;
454: insert into ece_output (run_id,line_id,text)
455: values (iRun_id,
456: ece_output_lines_s.nextval,
457: x_msg);
458: end if;
459: WHEN wrong_datatype THEN
460: bErrors_found := TRUE;
466: x_msg := 'SEED: Wrong data type for '||x_errtbl ||'.'||
467: x_errcol||' Seeded datatype: '||x_errdatatype||' Database datatype: '||
468: x_datatype;
469:
470: insert into ece_output (run_id,line_id,text)
471: values (iRun_id,
472: ece_output_lines_s.nextval,
473: x_msg);
474: end if;
468: x_datatype;
469:
470: insert into ece_output (run_id,line_id,text)
471: values (iRun_id,
472: ece_output_lines_s.nextval,
473: x_msg);
474: end if;
475:
476: WHEN bad_width THEN
481:
482: x_msg := 'SEED: Interface column '||x_errtbl ||'.'||
483: x_errcol||' too short. Seeded: '||eic_rec.width||' Database: '||x_width;
484:
485: insert into ece_output (run_id,line_id,text)
486: values (iRun_id,
487: ece_output_lines_s.nextval,
488: x_msg);
489: end if;
483: x_errcol||' too short. Seeded: '||eic_rec.width||' Database: '||x_width;
484:
485: insert into ece_output (run_id,line_id,text)
486: values (iRun_id,
487: ece_output_lines_s.nextval,
488: x_msg);
489: end if;
490: WHEN key_col_not_defined then
491: bErrors_found := TRUE;
492: if bInsertErrors then
493: xCurTable := eic_rec.interface_table_name;
494: x_errtbl := eic_rec.interface_table_name;
495: x_msg := 'SEED: Key column not defined for '||x_errtbl;
496: insert into ece_output (run_id,line_id,text)
497: values (iRun_id,
498: ece_output_lines_s.nextval,
499: x_msg);
500: end if;
494: x_errtbl := eic_rec.interface_table_name;
495: x_msg := 'SEED: Key column not defined for '||x_errtbl;
496: insert into ece_output (run_id,line_id,text)
497: values (iRun_id,
498: ece_output_lines_s.nextval,
499: x_msg);
500: end if;
501:
502: END;
538: if bInsertErrors then
539: x_errtbl := eic_rec.base_table_name;
540: x_errcol := eic_rec.base_column_name;
541: x_msg := 'SEED: Seeded column '||x_errtbl ||'.'||x_errcol||' not found.';
542: insert into ece_output (run_id,line_id,text)
543: values (iRun_id,
544: ece_output_lines_s.nextval,
545: x_msg);
546: end if;
540: x_errcol := eic_rec.base_column_name;
541: x_msg := 'SEED: Seeded column '||x_errtbl ||'.'||x_errcol||' not found.';
542: insert into ece_output (run_id,line_id,text)
543: values (iRun_id,
544: ece_output_lines_s.nextval,
545: x_msg);
546: end if;
547:
548: WHEN wrong_datatype THEN
552: x_errcol := eic_rec.base_column_name;
553: x_msg := 'SEED: Wrong data type for '||x_errtbl ||'.'||
554: x_errcol||' Seeded datatype: '||x_errdatatype||' Database datatype: '||
555: x_datatype;
556: insert into ece_output (run_id,line_id,text)
557: values (iRun_id,
558: ece_output_lines_s.nextval,
559: x_msg);
560: end if;
554: x_errcol||' Seeded datatype: '||x_errdatatype||' Database datatype: '||
555: x_datatype;
556: insert into ece_output (run_id,line_id,text)
557: values (iRun_id,
558: ece_output_lines_s.nextval,
559: x_msg);
560: end if;
561:
562: WHEN bad_width THEN
567:
568: x_msg := 'SEED: Interface column '||x_errtbl ||'.'||
569: x_errcol||' too short. Seeded: '||eic_rec.width||' Database: '||x_width;
570:
571: insert into ece_output (run_id,line_id,text)
572: values (iRun_id,
573: ece_output_lines_s.nextval,
574: x_msg);
575: end if;
569: x_errcol||' too short. Seeded: '||eic_rec.width||' Database: '||x_width;
570:
571: insert into ece_output (run_id,line_id,text)
572: values (iRun_id,
573: ece_output_lines_s.nextval,
574: x_msg);
575: end if;
576: END;
577: END IF; -- if ic_rec.base_table_name is NOT NULL
698:
699: end TEST_LOCATION_CODE;
700:
701: /*******************************************************************
702: This procedure will read a flatfile and create records in ECE_OUTPUT
703: with the values of the data based on the EDI Gateway data dictionary.
704: *******************************************************************/
705: PROCEDURE verify_flatfile(
706: p_run_id IN NUMBER,
856: l_data_value;
857: EC_DEBUG.PL(3, 'l_insert_stmt: ', l_insert_stmt);
858:
859: xProgress := 'RDATA-10-1140';
860: INSERT into ece_output( run_id, line_id, text)
861: VALUES(p_run_id, ece_output_runs_s.nextval, l_insert_stmt);
862:
863: xProgress := 'RDATA-10-1150';
864: l_insert_stmt := NULL;
857: EC_DEBUG.PL(3, 'l_insert_stmt: ', l_insert_stmt);
858:
859: xProgress := 'RDATA-10-1140';
860: INSERT into ece_output( run_id, line_id, text)
861: VALUES(p_run_id, ece_output_runs_s.nextval, l_insert_stmt);
862:
863: xProgress := 'RDATA-10-1150';
864: l_insert_stmt := NULL;
865: l_prv_record_num := interface_data_rec.Record_number;