Difference between revisions of "M4 CDR Disputes"

From Kolmisoft Wiki
Jump to navigationJump to search
 
(23 intermediate revisions by 4 users not shown)
Line 1: Line 1:
=What is a CDR Dispute?=
=What is a CDR Dispute?=
A telecom business often faces various disputes over the inconsistency of data between the Call Detail Records (CDR). Such data deviations include Call Price or Duration mismatches and is usually pointed out by the telecom's Suppliers/Providers or Clients. Such a phenomenon can be a result of a variety of reasons including different billing systems.
A telecom business often faces various disputes over the inconsistency of data between the Call Detail Records (CDRs). Such data deviations include Call Price or Duration mismatches and are usually pointed out by the telecom's Suppliers/Providers or Clients. Such a phenomenon can be a result of a variety of reasons including different billing systems.


=What is a CDR Dispute resolution tool provided by M2?=
<br><br>
The CDR Comparison Tool automatically analyzes the Call Detail Records provided by the Clients or the Suppliers/Providers. The mechanism compares the Local CDRs present in the Database with the imported external CDRs and highlights the discrepancies. The result is a generated and comprehensive report.
=What is a CDR Dispute resolution tool provided by M4?=
The CDR comparison tool automatically analyzes the Call Detail Records provided by the Clients or the Suppliers/Providers. The mechanism compares the local CDRs present in the database with the imported external CDRs and highlights the discrepancies. The result is a generated report.


<br><br>
=Typical CDR comparison scenario=
=Typical CDR comparison scenario=
==Preparations==
==Preparations==
First a CDR file (CSV) from the Client or the Supplier/Provider involved in a Dispute has to be retrieved. The CSV file for the current version of the CDR comparison tool should contain the following information:<br>
First, a CDR file (CSV) from the Client or the Supplier/Provider involved in a Dispute has to be retrieved. The CSV file for the current version of the CDR comparison tool should contain the following information:<br>


{| style="text-align: center;" border=1
{| style="text-align: center;" border="1" cellpadding="5" cellspacing="0"
|+ style="text-align: left;" | '''Table 1.''' Structure of the CSV file required for the comparison
|+ style="text-align: left;" | '''Table 1.''' Structure of the CSV file required for the comparison
|-
|-
Line 34: Line 36:
* Order of the CSV columns is not important.
* Order of the CSV columns is not important.
* In order to work between the different billing systems correctly the CSV should be as generic as possible i.e. Source and Destination should be simple numbers.
* In order to work between the different billing systems correctly the CSV should be as generic as possible i.e. Source and Destination should be simple numbers.
* The file should contain a sufficient amount of CDRs in order to resolve a Dispute.
* Make sure the prices in the CSV file are precise enough when exported. The suggested precision is 8 decimal spaces.
* The file should contain a sufficient amount of CDRs in order to resolve a Dispute (Recommended - 500).
* In order to use the Dispute functionality properly the M4 '''database has to be up to date''' (answer_time and end_time columns must be present in the calls table).


Finally a CDR import template needs to be created in order to correctly import the retrieved CSV file. More on how to create a template can be found here [[M2 CDR Import Templates|CDR Import Templates]].
'''IT IS IMPORTANT:''' Finally a CDR import template needs to be created in order to correctly import the retrieved CSV file. More on how to create a template can be found here [[M4 CDR Import Templates|CDR Import Templates]].


<br><br>
==Creating a new Dispute==
==Creating a new Dispute==
A Dispute can be created by opening the '''BILLING > CDR > Disputes''' page from the '''System Admin's''' menu.<br><br>
A Dispute can be created by opening the '''BILLING > CDR > Disputes''' page from the '''System Admin's''' menu.<br><br>
[[File:M2_CDR_Disputes_menu.png]]<br><br>
[[File:M2_CDR_Disputes_menu.png]]<br><br>
Shortly after, a list with the information about the Disputes is present indicating the Client or Supplier/Provider involved, the period of the CDRs compared, the differences between the imported external CDRs and the corresponding local ones, and the amounts of the compared local and external CDRs.<br><br>
Shortly after, a list with information about the Disputes is presented indicating the Client or the Supplier/Provider involved, the period of the CDRs compared, the differences between the imported external CDRs and the corresponding local ones, and the amounts of the compared local and external CDRs.<br><br>
[[File:M2_CDR_Disputes_list.png]]<br><br>
[[File:M2_CDR_Disputes_list.png]]<br><br>
'''NOTE:''' Once completed a Dispute cannot be edited thus a comprehensive tooltip with a detailed Dispute information is provided.<br>
'''NOTE:''' Once completed a Dispute cannot be edited thus a comprehensive tooltip with detailed Dispute information is provided.<br>
By clicking the '''ADD NEW''' button a Dispute creation form is opened:
<br><br>
[[File:M2_CDR_Dispute_from.png|1113px]]
<br><br>
The form provides the options required by the Dispute:
* '''Direction''' determines whether the Dispute involves an Originator or a Terminator (Provider/Supplier).
* '''User''' selector depending on the Dispute direction. '''NOTE:''' Only Users with Connection Points can be selected here.
* '''Periods Start/End''' determine the time interval which the Dispute is organized for.
* '''Save as Template''' allows the sections '''Dispute Options''' and '''Tolerance Options''' to be saved for later reuse.
* '''Template name''' allows to set the name for the Template. The field is visible only if '''Save as Template''' option is enabled. '''NOTE:''' Template name must be unique in the system and it cannot be edited.
* '''Compare last SRC/DST digits''' determine how many last digits of a Source/Destination Number are compared.
* '''External Currency''' determines the Currency of the external CDRs.
* '''Exchange rate''' determines the exchange rate of the external CDRs. '''NOTE:''' This field is automatically computed depending on the selected Currency and the exchange rate information present in the system. However, it can be edited when necessary.
* '''Check only ANSWERED Calls''' option allows to check only against answered calls. When this option is set, External calls in [[M4_CDR_Disputes#CDR_Dispute_Report|CDR Dispute Report]] still show all calls, but non-answered calls will not be compared and will be included in '''Not Compared''' column.
* '''Billsec tolerance''' determines how much the Billsec shift between the two CDRs is tolerated (in seconds).
* '''Price tolerance''' determines how much the Price shift between the two CDRs is tolerated (in Currency).
<br>
By clicking the '''SAVE AND PROCEED TO CDR IMPORT''' button the Dispute is saved and the CDR import page is opened. If there were any errors during the saving procedure, messages are displayed.
<br>
'''NOTE:''' If the import procedure (described below) is interrupted or canceled, the Dispute remains editable and it is possible to perform the CDR import later when desired. Edit form is adjusted with two additional buttons '''IMPORT CDR''' and '''UPDATE''':
<br><br>
[[File:M2_CDR_Dispute_edit_form.png]]
<br>
 
<br><br>
==Importing external CDR==
After successfully saving a Dispute or from the edit (as described above) the next step of the CDR Dispute is to create [[M4_CDR_Import_Templates | CDR Import Template]].
 
The next step is the CDR Import where the external CDR file is uploaded:
<br><br>[[File:M2_CDR_Dispute_apply_Template.png]]<br><br>
'''NOTE:''' before proceeding make sure to read the [[M4_CDR_Disputes#Preparations|Preparations]] section above.<br>
When the system is done parsing the file it is necessary to confirm whether the template is applied correctly:
<br><br>[[File:M2_CDR_Dispute_confirm_columns.png]]<br><br>
Ater the successful import the Disputes list is rendered once again indicating that the Dispute is in progress. The task is run in the background thus it might take some time to be put in a queue and completed. The task progress can be monitored on the [[Background_Tasks|Background Tasks]] page.<br>
'''NOTE:''' When in progress a Dispute can neither be edited nor deleted.<br>
 
<br><br>
==Comparison Logic==
* Firstly, the comparison module retrieves the first CDR Dispute present in a Background Tasks queue.
* Due to the Dispute settings (such as Periods, Direction, User, etc.) the local and external CDRs are retrieved from the Database.
* Next, the time shift between the local and external CDRs is determined. It is done by finding pairs of CDRs by Source and Destination Numbers and by checking the CDR Start Time difference in seconds. If a difference that is dominating more than 50% of the CDRs being compared is found, it is elected to be the time shift between the local and external CDRs.
* Next, CDRs are compared in pairs. Each pair is compared only when the Source and Destination Numbers together with the CDR Start Time + time shift are exactly matched. Else the CDR pair is non-matching. '''NOTE:''' CDRs that don't follow the dominating time shift pattern will be interpreted as having no match.
* Finally, a report is generated based on the comparison results.
 
<br><br>
==CDR Dispute Report==
After the CDR Dispute is completed it is indicated in the Disputes list. It is now possible to see the Details or Delete the Dispute. <br>
'''NOTES''':
*Completed Disputes cannot be edited.
*When deleting a Dispute related external CDRs are deleted too.
===CDR Dispute Report Summary===
By clicking on a '''DETAILS''' button the Dispute Report Summary is opened (as below):
<br><br>[[File:M2_CDR_Disputes_report.png]]<br><br>
The Report Summary page is organized into columns:
*'''Code''' indicates the comparison result status,
*'''Mismatch type''' is a more detailed representation of the '''Code''',
*'''Local+External''' is an aggregation for both external and local sets of CDRs,
*'''Calls''' is an aggregation of the CDRs count,
*'''Billsec''' is an aggregation of the CDRs Billsec,
*'''Price''' is an aggregation of the CDR's Price.
Each line represents an aggregation of CDRs by the comparison result '''Code'''. Partial aggregations of several groups of '''Codes''' (e.g. '''Total Calls''', '''Mismatch''') are also provided in this page.<br>
Several columns are split into aggregations for local and external sets separately together with '''Delta''' aggregations (a.k.a. differences).
===Report Code meanings===
*'''Total Calls''' - Total CDRs in a Dispute.
*'''Total Connected''' - Total CDRs that are '''ANSWERED'''.
*'''Tolerated Mismatch''' - Total CDRs with Codes from range 21-23.
*'''Mismatch''' - Total CDRs with Codes greater than or equal to 31.
*'''00 Not Compared''' - CDRs that were not compared (on success should be 0). If option '''Check only ANSWERED Calls''' is enabled, this will include all CDRs which have type other than "ANSWERED".
*'''10 Exact Match''' - CDRs that were exactly matched by the Source and Destination Numbers, Start, Answer, and End Times, Billsec, Price, and Disposition.
*'''21 Tolerated mismatch by Price''' - CDRs that have exactly the same Billsec but the Price is within the tolerance range (as set in the Dispute settings).
*'''22 Tolerated mismatch by Billsec''' - CDRs that have exactly the same Price but the Billsec is within the tolerance range.
*'''23 Tolerated mismatch by Price and Billsec''' - CDRs that have both Billsec and Price within the tolerance range.
*'''31 Mismatch by Price''' - CDRs that have a Price outside the tolerance range.
*'''32 Mismatch by Billsec''' - CDRs that have a Billsec outside the tolerance range.
*'''33 Mismatch by Price and Billsec''' - CDRs that have both Billsec and Price outside the tolerance range.
*'''40 Connected only locally''' - CDRs that are '''ANSWERED''' inside the local CDRs set but non-answered inside the external one.
*'''42 Connected only externally''' - CDRs that are '''ANSWERED''' inside the external CDRs set but non-answered inside the local one.
*'''70 Local duplicate''' - CDRs that are duplicate inside the local CDRs set by the attributes mentioned in the Code '''10''' description.
*'''72 External duplicate''' - CDRs that are duplicate inside the local CDRs set by the attributes mentioned in the Code '''10''' description.
*'''90 Not matched by any field''' - CDRs that have to match by Source and/or Destination Numbers and/or by the shifted Start Time.
*'''99 Errors''' - Errors happen when required CDR attributes are invalid or empty (see [[M4_CDR_Disputes#Preparations|Preparations]]).
 
===Detailed CDR Dispute Report===
By clicking on any of the '''DETAILS''' buttons in the [[M4_CDR_Disputes#CDR_Dispute_Report_Summary|CDR Report Summary]] page a Detailed CDR Dispute Report is opened. On this page, it is possible to investigate specific CDRs by filtering or sorting them in the desired way.
<br><br>[[File:M2_CDR_Disputes_detailed.png]]<br><br>
 
<br><br>
=See Also=
* [[M4 CDR Import Templates|CDR Import Templates]]

Latest revision as of 12:36, 29 November 2022

What is a CDR Dispute?

A telecom business often faces various disputes over the inconsistency of data between the Call Detail Records (CDRs). Such data deviations include Call Price or Duration mismatches and are usually pointed out by the telecom's Suppliers/Providers or Clients. Such a phenomenon can be a result of a variety of reasons including different billing systems.



What is a CDR Dispute resolution tool provided by M4?

The CDR comparison tool automatically analyzes the Call Detail Records provided by the Clients or the Suppliers/Providers. The mechanism compares the local CDRs present in the database with the imported external CDRs and highlights the discrepancies. The result is a generated report.



Typical CDR comparison scenario

Preparations

First, a CDR file (CSV) from the Client or the Supplier/Provider involved in a Dispute has to be retrieved. The CSV file for the current version of the CDR comparison tool should contain the following information:

Table 1. Structure of the CSV file required for the comparison
Source* Destination* Start Time* Disposition* Billsec* Price* Answer Time End Time
Number Number Datetime ANSWERED|NO ANSWER|BUSY|FAILED Integer Number Datetime Datetime
... ... ... ... ... ... ... ...

NOTES:

  • Columns marked with an asterisk (*) are required.
  • Order of the CSV columns is not important.
  • In order to work between the different billing systems correctly the CSV should be as generic as possible i.e. Source and Destination should be simple numbers.
  • Make sure the prices in the CSV file are precise enough when exported. The suggested precision is 8 decimal spaces.
  • The file should contain a sufficient amount of CDRs in order to resolve a Dispute (Recommended - 500).
  • In order to use the Dispute functionality properly the M4 database has to be up to date (answer_time and end_time columns must be present in the calls table).

IT IS IMPORTANT: Finally a CDR import template needs to be created in order to correctly import the retrieved CSV file. More on how to create a template can be found here CDR Import Templates.



Creating a new Dispute

A Dispute can be created by opening the BILLING > CDR > Disputes page from the System Admin's menu.

M2 CDR Disputes menu.png

Shortly after, a list with information about the Disputes is presented indicating the Client or the Supplier/Provider involved, the period of the CDRs compared, the differences between the imported external CDRs and the corresponding local ones, and the amounts of the compared local and external CDRs.

M2 CDR Disputes list.png

NOTE: Once completed a Dispute cannot be edited thus a comprehensive tooltip with detailed Dispute information is provided.
By clicking the ADD NEW button a Dispute creation form is opened:

M2 CDR Dispute from.png

The form provides the options required by the Dispute:

  • Direction determines whether the Dispute involves an Originator or a Terminator (Provider/Supplier).
  • User selector depending on the Dispute direction. NOTE: Only Users with Connection Points can be selected here.
  • Periods Start/End determine the time interval which the Dispute is organized for.
  • Save as Template allows the sections Dispute Options and Tolerance Options to be saved for later reuse.
  • Template name allows to set the name for the Template. The field is visible only if Save as Template option is enabled. NOTE: Template name must be unique in the system and it cannot be edited.
  • Compare last SRC/DST digits determine how many last digits of a Source/Destination Number are compared.
  • External Currency determines the Currency of the external CDRs.
  • Exchange rate determines the exchange rate of the external CDRs. NOTE: This field is automatically computed depending on the selected Currency and the exchange rate information present in the system. However, it can be edited when necessary.
  • Check only ANSWERED Calls option allows to check only against answered calls. When this option is set, External calls in CDR Dispute Report still show all calls, but non-answered calls will not be compared and will be included in Not Compared column.
  • Billsec tolerance determines how much the Billsec shift between the two CDRs is tolerated (in seconds).
  • Price tolerance determines how much the Price shift between the two CDRs is tolerated (in Currency).


By clicking the SAVE AND PROCEED TO CDR IMPORT button the Dispute is saved and the CDR import page is opened. If there were any errors during the saving procedure, messages are displayed.
NOTE: If the import procedure (described below) is interrupted or canceled, the Dispute remains editable and it is possible to perform the CDR import later when desired. Edit form is adjusted with two additional buttons IMPORT CDR and UPDATE:

M2 CDR Dispute edit form.png



Importing external CDR

After successfully saving a Dispute or from the edit (as described above) the next step of the CDR Dispute is to create CDR Import Template.

The next step is the CDR Import where the external CDR file is uploaded:

M2 CDR Dispute apply Template.png

NOTE: before proceeding make sure to read the Preparations section above.
When the system is done parsing the file it is necessary to confirm whether the template is applied correctly:

M2 CDR Dispute confirm columns.png

Ater the successful import the Disputes list is rendered once again indicating that the Dispute is in progress. The task is run in the background thus it might take some time to be put in a queue and completed. The task progress can be monitored on the Background Tasks page.
NOTE: When in progress a Dispute can neither be edited nor deleted.



Comparison Logic

  • Firstly, the comparison module retrieves the first CDR Dispute present in a Background Tasks queue.
  • Due to the Dispute settings (such as Periods, Direction, User, etc.) the local and external CDRs are retrieved from the Database.
  • Next, the time shift between the local and external CDRs is determined. It is done by finding pairs of CDRs by Source and Destination Numbers and by checking the CDR Start Time difference in seconds. If a difference that is dominating more than 50% of the CDRs being compared is found, it is elected to be the time shift between the local and external CDRs.
  • Next, CDRs are compared in pairs. Each pair is compared only when the Source and Destination Numbers together with the CDR Start Time + time shift are exactly matched. Else the CDR pair is non-matching. NOTE: CDRs that don't follow the dominating time shift pattern will be interpreted as having no match.
  • Finally, a report is generated based on the comparison results.



CDR Dispute Report

After the CDR Dispute is completed it is indicated in the Disputes list. It is now possible to see the Details or Delete the Dispute.
NOTES:

  • Completed Disputes cannot be edited.
  • When deleting a Dispute related external CDRs are deleted too.

CDR Dispute Report Summary

By clicking on a DETAILS button the Dispute Report Summary is opened (as below):

M2 CDR Disputes report.png

The Report Summary page is organized into columns:

  • Code indicates the comparison result status,
  • Mismatch type is a more detailed representation of the Code,
  • Local+External is an aggregation for both external and local sets of CDRs,
  • Calls is an aggregation of the CDRs count,
  • Billsec is an aggregation of the CDRs Billsec,
  • Price is an aggregation of the CDR's Price.

Each line represents an aggregation of CDRs by the comparison result Code. Partial aggregations of several groups of Codes (e.g. Total Calls, Mismatch) are also provided in this page.
Several columns are split into aggregations for local and external sets separately together with Delta aggregations (a.k.a. differences).

Report Code meanings

  • Total Calls - Total CDRs in a Dispute.
  • Total Connected - Total CDRs that are ANSWERED.
  • Tolerated Mismatch - Total CDRs with Codes from range 21-23.
  • Mismatch - Total CDRs with Codes greater than or equal to 31.
  • 00 Not Compared - CDRs that were not compared (on success should be 0). If option Check only ANSWERED Calls is enabled, this will include all CDRs which have type other than "ANSWERED".
  • 10 Exact Match - CDRs that were exactly matched by the Source and Destination Numbers, Start, Answer, and End Times, Billsec, Price, and Disposition.
  • 21 Tolerated mismatch by Price - CDRs that have exactly the same Billsec but the Price is within the tolerance range (as set in the Dispute settings).
  • 22 Tolerated mismatch by Billsec - CDRs that have exactly the same Price but the Billsec is within the tolerance range.
  • 23 Tolerated mismatch by Price and Billsec - CDRs that have both Billsec and Price within the tolerance range.
  • 31 Mismatch by Price - CDRs that have a Price outside the tolerance range.
  • 32 Mismatch by Billsec - CDRs that have a Billsec outside the tolerance range.
  • 33 Mismatch by Price and Billsec - CDRs that have both Billsec and Price outside the tolerance range.
  • 40 Connected only locally - CDRs that are ANSWERED inside the local CDRs set but non-answered inside the external one.
  • 42 Connected only externally - CDRs that are ANSWERED inside the external CDRs set but non-answered inside the local one.
  • 70 Local duplicate - CDRs that are duplicate inside the local CDRs set by the attributes mentioned in the Code 10 description.
  • 72 External duplicate - CDRs that are duplicate inside the local CDRs set by the attributes mentioned in the Code 10 description.
  • 90 Not matched by any field - CDRs that have to match by Source and/or Destination Numbers and/or by the shifted Start Time.
  • 99 Errors - Errors happen when required CDR attributes are invalid or empty (see Preparations).

Detailed CDR Dispute Report

By clicking on any of the DETAILS buttons in the CDR Report Summary page a Detailed CDR Dispute Report is opened. On this page, it is possible to investigate specific CDRs by filtering or sorting them in the desired way.

M2 CDR Disputes detailed.png



See Also