Follow

PMR Parameters and Stored Procedures

PMR Detail Query Parameters

The parameters to pass to spGetPortfolioMonitoringDetailData_V3, the detail query, are as follows:

  • @ReportDate - this is the date for which the report is to be generated. The data in the report will be the most recent data before this date.
  • @CreditorRegistryID - this is the registry id of the creditor requesting the report.
  • @AccountTypes - these are the types of the account to be included in the report. The accounts should be listed as a comma-separated string with no spaces between the commas and the types. By default, this is NULL.
  • @MinimumBalance - this is the minimum balance that the accounts in the report should have. By default, this is NULL, meaning there should be no minimum balance restrictions.
  • @FilterOutZeroCreditLimitRegistrants - if this is 1, then filter out account owners with a credit limit of 0; else don't filter. By default, this is 0.
  • @RefreshHistoricalData - if this is 1, then regenerate the data in the table that holds the most recent account data before the report data. Note that doing this will cause the stored procedure to take a long time to complete. If it is not 1, do not regenerate the data. By default, this is 0.

If you have previously run a report for any subscriber for a particular ReportDate, you should not need to regenerate the data by setting this field to 1, unless a subscriber has since submitted data that pertains to the ReportDate that you are generating data for.

  • @CustomersWithInternalAndExternalAccountsOnly - if this is 1, then only include customers with internal and external accounts. By default, this is 0, meaning that customers with internal accounts only will be included.
  • @FilterOutExcludedCustomers - if this is 0, then include all customers, whether they qualify to be in the report or not. If it is 1, then filter them out. (This should usually be set to 1, which is the default.)
  • @NumberOfCustomers - this is the number of customers to be in the report. If this is NULL, then include as many customers as fit the other criteria specified.

If the number of customers that meet the other criteria is not up to @NumberOfCustomers, then the number of customers is all that will be returned (e.g. if @NumberOfCustomers is 200, but only 60 customers meet the criteria specified in the other parameters, then only 60 customers will be returned). By default, this is NULL.

  • @PersonsOnly - if 1, then only persons will be returned; if 0, then only businesses will be returned; if NULL, then both persons/businesses will be returned. By default, this is NULL.
  • @AccountsToMonitor - this is a comma-separated string that contains a list of account numbers. Each number in this list is an account number that the subscriber wants to be monitored. In other words, if an account number is not in this list, the account will not be monitored.

The comma-separated list must not have any spaces or any other kind of whitespace character (e.g. newlines or tabs) between the commas and the account numbers - it should be like this, for example: ACCT0001,ACCT0002,ACCT0003,ACCT0004.

Also, the accounts should meet any other criteria specified in the query (for example, if a minimum balance of N500,000 is specified, but ACCT0003 has only a balance of N200,000, then it will not be included in the PMR). By default, this is NULL.

  • @InsertIntoTable - if this is 0, then output the results of the PMR query directly to the caller. However, if this is 1, then output the results of the PMR query to a special table - tblPMRCreditorAccountsTemp. By default, this is 0.

 

PMR Summary Query Parameters

The parameters to pass to spGetPortfolioMonitoringSummaryData_V3, the detail query, are as follows:

  • @ReportDate - this is the date for which the report is to be generated. The data in the report will be the most recent data before this date.
  • @CreditorRegistryID - this is the registry id of the creditor requesting the report.
  • @AccountTypes - these are the types of the account to be included in the report. The accounts should be listed as a comma-separated string with no spaces between the commas and the types. By default, this is NULL.
  • @MinimumBalance - this is the minimum balance that the accounts in the report should have. By default, this is NULL, meaning there should be no minimum balance restrictions.
  • @FilterOutZeroCreditLimitRegistrants - if this is 1, then filter out account owners with a credit limit of 0; else don't filter. By default, this is 0.
  • @RefreshHistoricalData - if this is 1, then regenerate the data in the table that holds the most recent account data before the report data. Note that doing this will cause the stored procedure to take a long time to complete. If it is not 1, do not regenerate the data. By default, this is 0.
  • @CustomersWithInternalAndExternalAccountsOnly - if this is 1, then only include customers with internal and external accounts. By default, this is 0, meaning that customers with internal accounts only will be included.
  • @NumberOfCustomers - this is the number of customers to be in the report. If this is NULL, then include as many customers as fit the other criteria specified.

If the number of customers that meet the other criteria is not up to @NumberOfCustomers, then the number of customers is all that will be returned (e.g. if @NumberOfCustomers is 200, but only 60 customers meet the criteria specified in the other parameters, then only 60 customers will be returned). By default, this is NULL.

  • @PersonsOnly - if 1, then only persons will be returned; if 0, then only businesses will be returned; if NULL, then both persons/businesses will be returned. By default, this is NULL
  • @AccountsToMonitor - this is a comma-separated string that contains a list of account numbers. Each number in this list is an account number that the subscriber wants to be monitored. In other words, if an account number is not in this list, the account will not be monitored.

The comma-separated list must not have any spaces between the commas and the account numbers - it should be like this, for example: ACCT0001,ACCT0002,ACCT0003,ACCT0004.

Also, the accounts should meet any other criteria specified in the query (for example, if a minimum balance of N500,000 is specified, but ACCT0003 has only a balance of N200,000, then it will not be included in the PMR). By default, this is NULL.

  • @InsertIntoTable - if this is 0, then output the results of the PMR query directly to the caller. However, if this is 1, then output the results of the PMR query to a special table - tblPMRCreditorAccountsSummaryTemp. By default, this is 0.

 

Sample procedure calls

 

The bolded parts of each sample procedure call indicates where the criteria for the query has been specified as a parameter.

  • Exact number: 1700 customers

IMPORTANT: The eighth parameter (FilterOutExcludedCustomers) should always be 1 when you are calling spGetPortfolioMonitoringDetailData_V3. (This parameter means that customers who don't meet the other criteria you specify will be excluded.)

 

exec spGetPortfolioMonitoringDetailData_V3 '17 JUL 2013',732692732692029601, NULL, NULL, 0, 0, 0, 1, 1700, NULL, NULL, 0

exec spGetPortfolioMonitoringSummaryData_V3 '17 JUL 2013', 732692732692029601, NULL, NULL, 0, 0, 0, 1700, NULL, NULL, 0

 

  • Refresh database

exec spGetPortfolioMonitoringDetailData_V3 '17 JUL 2013',732692732692029601, NULL, NULL, 0, 1, 0, 1, NULL, NULL, NULL, 0

exec spGetPortfolioMonitoringSummaryData_V3 '17 JUL 2013', 732692732692029601, NULL, NULL, 0, 1, 0, NULL, NULL, NULL, 0

 

  • Customers with balance equal or above N50000

exec spGetPortfolioMonitoringDetailData_V3 '17 JUL 2013',732692732692029601, NULL, 50000, 0, 0, 0, 1, NULL, NULL, NULL, 0

exec spGetPortfolioMonitoringSummaryData_V3 '17 JUL 2013', 732692732692029601, NULL, 50000, 0, 0, 0, NULL, NULL, NULL, 0

 

  • Customers that have the following accounts: 0001112233, 0001112244, 0001112255

exec spGetPortfolioMonitoringDetailData_V3 '17 JUL 2013',732692732692029601, NULL, NULL, 0, 0, 0, 1, NULL, NULL, '0001112233,0001112244,0001112255', 0

exec spGetPortfolioMonitoringSummaryData_V3 '17 JUL 2013', 732692732692029601, NULL, NULL, 0, 0, 0, NULL, NULL, '0001112233,0001112244,0001112255', 0

 

FLAT FILES:

After uploading the files via SQL or CMD prompt, use PMR excel or $M Tableau to generate the report.

Excel:

  • exec spGetPortfolioMonitoringDetailDataInTempTable_V3
  • exec spGetPortfolioMonitoringSummaryDataInTempTable_V3

-----------

 

  • Customers that have the following account types (with the data to be written to tables): CA01, CA02, CA03

exec spGetPortfolioMonitoringDetailData_V3 '17 JUL 2013', 732692732692029601, 'CA01,CA02,CA03', NULL, 0, 0, 0, 1, NULL, NULL, NULL, 1

exec spGetPortfolioMonitoringSummaryData_V3 '17 JUL 2013', 732692732692029601, 'CA01,CA02,CA03', NULL, 0, 0, 0, NULL, NULL, NULL, 1

 

PMR Parameter Descriptions

 

Parameter Position in Detail Query

Parameter Position in Summary Query

Parameter Name

Parameter Example (default in brackets where applicable)

Notes

1

1

Balance Date

'30 JUN 2011'

Accounts with balances on or before this date will be included in the report.

2

2

Subscriber ID

733733730341525909

subscriber ID

3

3

Account Type

PL01 PL01,CL01,PA01 (NULL)

Only ‘Account Types’ listed will be monitored. NULL means that the report will show accounts with all account types. For more than one account type, separate with a comma (PL01,CL01,PA01). Note there are no spaces on either side of the comma. A NULL value means that the report will show customers with all kinds of account types.

4

4

Balance Amount

1,000,000 (NULL)

Minimum balance amount to monitor. Accounts whose balance is equal or greater than this will be included in the report. A NULL value means that the report will show all customers, irrespective of balance.

5

5

Filter Out Zero Credit Limit Customers

1 (0)

NULL means that the report will show accounts with credit limit of 0. A non-NULL value (e.g. 1) means that the report will not show accounts with credit limit of 0

6

6

Refresh Historical Data

1 (0)

A NULL value means that the report will not refresh historical data upon which the report is based. A non-NULL (e.g. 1) means that the report will refresh historical data.

7

7

Internal / External Accounts

NULL (0)

A NULL value means that the report will show all obligors, including those with internal accounts only. A non NULL value (e.g. 1) means that the report will show only obligors who have both internal and external accounts

8

N/A        

Exclude Customers

1 (1)

If this is 0, then the report will include all customers, whether they qualify to be in the report or not. If it is 1, then the report will filter them out. (This should usually be set to 1.)

9

8

Number of Customers

3,000 (NULL)

A NULL value means that the report will include as many customers as fit the other criteria specified. Otherwise, the report will only show the number of customers specified in this value.

10

9

Customer Type

0 (NULL)

A NULL value means that the report will show both persons/businesses; 1 means that only persons will be shown; 0 means that only businesses will be shown.

11

10

Accounts to Monitor

'ML002383467,ML002383521' (NULL)

A NULL value means all accounts meeting any other specified criteria will be monitored. If a comma separated string is provided for this parameter, then in addition to any other specified criteria, only owners of accounts specified in the comma separated string will be monitored.

12

11

Insert into Table

1 (0)

A 0 means that the data generated from the query will be output to the caller; a 1 means that the data will be inserted into a temporary table.

 

Account Type Codes

 

Account Type Code

Abbreviation

Description

AR00

Account Receivable Debt

Account receivable debt owed to general service providers (lawyers, architects, management consultants, businesses, etc.)

CA00

Savings

Commercial savings account

CA01

Current

Commercial current account

CA02

Debit Card

Commercial/business debit card account

CA03

Domiciliary

Commercial/business domiciliary/foreign currency account

CA04

Fixed Deposit

Certificate or fixed deposit account (commercial account)

CL00

Overdraft

Commercial Overdraft

CL01

Credit Card

Commercial Credit Card Loan

CL02

Revolving

Commercial revolving renewable short-term credit

CL03

Secured Loan

Commercial secured loan

CL10

Mortgage

Commercial mortgage

CL11

Mortgage Ref.

Commercial mortgage refinance

CL12

Rent Loan

Commercial rent facility

CL13

Land Purch. Loan

Commercial Land purchase financing

CL20

Auto loan

Commercial/Business Auto Loan

CL21

Asset Finance

Commercial/Business equipment asset/leasing/financing

CL30

30-day loan

Commercial/Business 30-day loan

CL31

Term Loan

Commercial/Business fixed term loan

CL32

Loan

Commercial Loan

CL33

Bullet Loan

Commercial bullet or one-time loan payment

CL34

Forgn. Curr. Loan

Foreign-denominated loan

CL35

Investment Loan

Commercial investment loan

CL40

Local LPO

Local Purchase Order (LPO)/supply contract financing

CL41

Supplier Finance

Distributor/supplier credit finance

CL42

A/R Finance

Invoice discounting/account receivable finance

CL43

Bill Discounting

Bill discounting

CL44

Warehouse Fin.

Warehouse finance

CL45

Oil/Gas Contract Fin.

Oil and Gas contract finance

CL50

Project Fin.

Project, Long-term infrastructure, Industrial, Public Service finance, Non-Recourse Debt, Non-Recourse Finance

CL51

BOT Contract Fin.

Build-Operate-Transfer (BOT) Contract/Finance

CL52

Municipal Bond

Combination/municipal/double barrel bond

CL60

Bankers' Facility

Banker's acceptance facility

CL61

Commercial Paper

Commercial paper

CL62

Letter of Credit

Letter of credit

CL63

Intl Trade Fin.

International trade finance

CL64

Import Fin.

Import Finance/Stock replacement facility

CL65

Export Fin.

Export Finance

CL66

Bond/Guaranty

Bond and guarantees (bid bonds, Customs & Excise bonds, Advance payment guarantee, performance bonds, etc.)

CL69

Loan Syndication

Loan syndication; Competitive Bid Option

CL80

Travel/Vacation

Business travel or vacation loan

CL90

Agric Loan

Business agriculture finance

GL00

T-Bill

Treasury bills

GL10

FG Loan

Federal government finance lease

GL20

State Gov Loan

State government finance lease

GL30

Local Gov Loan

Local government finance lease

GL40

Gov-backed Loan

Government-backed loan

IN10

Mortgage Insurance

Mortgage Insurance Policy

IN20

Household Insurance

Household Insurance Policy

IN30

Vehicle Insurance

Vehicle Insurance Policy

IN40

Flood Insurance

Flood Insurance Policy

IN45

Fire Insurance

Fire Insurance Policy

IN50

Flood and Fire Insurance

Flood and Fire Insurance Policy

IN70

Equipment Insurance

Equipment Insurance Policy

ML00

MFB loan

Microfinance or small proprietor loan

PA00

Savings

Personal savings account

PA01

Current

Personal current account

PA02

Debit Card

Personal debit card account

PA03

Domiciliary

Personal domiciliary/foreign currency account

PA04

Fixed Deposit

Certificate or fixed deposit account (personal account)

PD00

Package Delivery

Package delivery credit

PL00

Overdraft

Personal Overdraft

PL01

Credit Card

Personal Credit Card Loan

PL02

Revolving Loan

Personal revolving renewable short-term credit

PL03

Secured Loan

Personal secured loan

PL10

Mortgage

Personal home mortgage

PL11

Mortgage Ref.

Personal home mortgage refinance

PL12

Rent Loan

Residential rent facility

PL13

Land Purchase

Personal land purchase financing

PL14

NHF Loan

National Housing Fund Loan

PL20

Auto Loan

Personal Auto Loan

PL21

Asset Finance

Personal/consumer equipment/asset hire purchase/leasing/financing

PL30

30-day loan

30-day loan

PL31

Term Loan

Personal fixed term loan

PL32

Salary Advance

Payday Loan or salary advance

PL33

Bullet Loan

Personal bullet or one-time loan payment

PL34

Forgn. Curr. Loan

Foreign-denominated personal loan

PL35

Investment Loan

Personal investment loan

PL40

Education Loan

Education or student loan

PL50

Share Purchase

Personal share acquisition/purchase or margin loan

PL80

Travel/Vacation

Personal travel or vacation loan

PL90

Agric Loan

Personal agriculture finance

RR00

Apartment Rental Debt

Flat or apartment rental debt

RR10

Equipment Rental Debt

Equipment rental debt

RR20

Rental Debt (Other)

Other (not flat or equipment) rental debt

TC00

Pre-paid Mobile

Pre-paid mobile phone service

 

 

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