Follow

How to run a pre/post PMR validation query

You will typically want to run a pre PMR validation query to find out whether accounts will appear in a PMR to be generated, and if they won't appear, why they won't appear.

You will typically want to run a post PMR validation query to find out whether accounts did appear in a PMR that has been generated, and if they didn't appear, why they didn't appear. (Note: Post-validation reports are not currently supported.)

 

Here are the instructions for running a pre PMR validation query:

Ensure that you have the list of accounts that you want to pre-validate created as a comma-separated string with no spaces between the numbers and the commas; for example, if you wish to pre-validate the following accounts:
- ACCT0001
- ACCT0002
- ACCT0003
- ACCT0004

then create this comma-separated string: ACCT0001,ACCT0002,ACCT0003,ACCT0004

- Also ensure that you know the following:
- the report date for which the report is to be generated;
- the registry ID of the subscriber for whom the report is to be generated

- Open up Microsoft SQL Server Management Studio.

- In the Connect to SQL Server dialog box, ensure that CRLiveSQL is selected and click 'Connect'.

- Click 'File' > 'New' > 'Query with current connection' to open a connection window.

- Click 'Query' > 'Results To' > 'Results To Grid' to ensure that the output is in a grid format that can easily be copied and pasted to Excel

- Assuming the following:

  - Report Date is 31 Mar 2017

  - Subscriber Registry ID is 731102333444555666

  - Accounts to validate are ACCT0001,ACCT0002,ACCT0003,ACCT0004

Then type in the following:
spGeneratePreValidationReport '31 Mar 2017', 731102333444555666, 'ACCT0001,ACCT0002,ACCT0003,ACCT0004'

- Click 'Query' > 'Execute', and wait for the report to be generated.

 

If you wish to paste the results into Excel, do the following:

- Once the query returns with results, switch to the Results tab.

- Click anywhere in the results, then press they Ctrl and A at the same time to select all the results.

- Right click the selected results, then click 'Copy with Headers' to copy the results

- You can then paste the results into an Excel sheet.

 

The instructions for running a post PMR validation query are identical to the instructions above, except that instead of running

spGeneratePreValidationReport '31 Mar 2017', 731102333444555666, 'ACCT0001,ACCT0002,ACCT0003,ACCT0004'

you should run 

spGenerateValidationReport '31 Mar 2017', 731102333444555666, 'ACCT0001,ACCT0002,ACCT0003,ACCT0004', 0

Note the different procedure name and the 0 you add at the end of the procedure. Also, at this time, this post-validation procedure is not supported.

.

Here are the descriptions you can get from the validation reports, and what they mean:

'Excluded because of account status'
- This means that the account status either 700, 900 or 999 as of the report date.
- The action item here is for the subscriber to either:
  - update the account status for the account in the bureau as of the report date (e.g. by provding a CDT credit information file or getting PSG to create a Balance and Account Status Update Task), or
  - remove the account from the list of accounts they expect to see in the PMR.
 
'Excluded because of balance'
- This means that the account balance was 0 (or close to 0) as of the report date.
- The action item here is for the subscriber to either:
  - update the account status for the account in the bureau as of the report date (e.g. by provding a CDT credit information file or getting PSG to create a Balance and Account Status Update Task), or
  - remove the account from the list of accounts they expect to see in the PMR.
 
'Excluded because not in bureau on report date, although present later'
- This means that there was no information about the account in the bureau as of the report date.
- The action item here is for the subscriber to provide information to the bureau about the account as of the report date (e.g. by provding a CDT credit information file).
 
'Excluded because, although present in bureau, balance date could not be determined'
- This means that there was no information provided about the balance date for the account in the bureau at any time.
- The action item here is for the subscriber to provide information to the bureau about the account as of the report date (e.g. by provding a CDT credit information file).
 
'Excluded because not in bureau on any date'
- This means that there was no information provided about the account in the bureau at any time.
- The action item here is for the subscriber to provide information to the bureau about the account as of the report date (e.g. by provding a CDT credit information file).
 
'Excluded because no account owner found'
- This means that there was no information provided about the owner of the account in the bureau.
- The action item here is for the subscriber to provide information to the bureau about the owner of the account (e.g. by provding a CDT individual or borrower information file that contains details of the owners of those accounts).
 
'Excluded because there is no name for the account owner'
- This means that there was no name provided for the owner of the account in the bureau.
- The action item here is for the subscriber to provide information to the bureau about the owner of the account (e.g. by provding a CDT individual or borrower information file that contains details of the owners of those accounts).
 

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk