Administration Guide for Oracle Self-Service E-Billing > Running the ETL Jobs Using Oracle Workflow Manager >

Resolving Checksum Errors

The processes for loading unbilled and prepaid data, EBILL_UBDATALOAD and EBILL_PPDATALOAD, perform a checksum error analysis. For each process, the checksum determines whether the number of records loaded into the staging table is the same as the number finally loaded into the production table.

The ETL log table, EDX_RPT_ETL_LOG, shows the checksum result. For prepaid data loads, the checksum error log also provides information about what was missing or incorrect.

You can run the EDX_ETL_CHECKSUM_TROUBLESHOOT process to determine which records caused the checksum errors in the unbilled or prepay data load files. The EDX_ETL_CHECKSUM_TROUBLESHOOT process populates the EDX_RPT_ETL_CHECKSUM_ERRORS table, which you can then use to review the problem records.

You can use the EDX_ETL_CHECKSUM_TROUBLESHOOT process only if the IN_11_AUTO_REJECT process parameter in the unbilled or prepaid load process is set to N.

To determine which records caused the checksum errors

1. Log into SQL*Plus as the schema owner.
2. Run the EDX_ETL_CHECKSUM_TROUBLESHOOT process as follows:

   set serveroutput on

   declare

   vresult int;

   vtablename varchar2(30);

   vfilename varchar2(255);

   begin

   -- Call the procedure

   edx_etl_checksum_troubleshoot(pfilename => vfilename,

   ptablename => vtablename,

   presult => vresult);

   dbms_output.put_line(vresult);

   end;

   /

   where:

   • pfilename is the name of the unbilled or prepaid data input file that produced the checksum error.
   • ptablename is the table name to perform the checks against, either the EDX_RPT_PREPAID_DETAIL_FACT or EDX_RPT_UNBILLED_DETAIL_FACT table. You can also specify no value.
   • presult is the return code: 0 = success; -1 = failure. Check the EDX_RPT_ETL_LOG file in the OLAP database for details about the failure.
3. Review the EDX_RPT_ETL_CHECKSUM_ERRORS table, which was populated by the EDX_ETL_CHECKSUM_TROUBLESHOOT process.