General Description

The PRC interface manages the exchange of data between Crawl and PRC. PRC is a third-party software and company used to perform credit checking on a company before contract signing.

The PRC process is a credit review process used by Crawl before contract signing.

The word “PRC” can be used in many different ways: the credit review process, or Crawl’s PRC interface, or the PRC software platform.

The “PRC software platform” stands for Platform Credit Risk, which is an in-house, Sidewalk solution to manage risk prior to contract signing. PRC is used by Sidewalk employees and interfaces, among others, with Nasaw and Crawl.

1.1 Definitions

Crawl

Includes the Crawl database, Oracle Forms, and all related processes.

TIBCO interfaces

The middle layer between Crawl and PRC that manages the messages and data sent between these two systems. Crawl does not connect directly to PRC. For any detailed discussion of Tibco, you will have to seek other documentation.

PRC Study Request

The first part of the PRC workflow is to create a PRC request, associated with a request id.

An initial set of client information sent to the PRC platform.

The COLLECT

The gathering of additional client data – usually financial and contractual – collected and grouped together, then sent to the PRC platform.

PRC Decision

Final stage in the PRC process, Yes or No. There are partial decisions as well.

1.2 Systems Used

The PRC process uses a number of coordinating technologies, all of which will be discussed in detail as part of the PRC process:

PRC Java web service

Exposes certain operations to help save and send PRC-related information.

Oracle packages

Saves PRC data, generates PRC financial data, and sends PRC data to the PRC platform.

Oracle Triggers

Manage the Crawl-to-PRC platform process.

iSee

Can also initiale a PRC request, which is then managed by 988.

Crawl (its Forms and their functionality)

  • Crawl988 is where the user actually formulates the request, and initiates and manages the relationship with PRC.
  • Crawl196 initiates a PRC request, via 988.
  • Crawl 435 can also initiate a request, also via 988.
  • Crawl908 manages the Monaco links between companies, which impacts the financial data  received and sent during the PRC

The PRC third-party software

TIBCO interfaces

2 SOAP Messages

2.1 INBOUND flows

<requestCollect >

Calls PL/SQL prc_interfaces.study_request_collect

See Request Collect below.

<requestStatusUpdate >

Calls PL/SQL prc_interfaces.study_request_status_update

See Request Status below.

<requestDecision >

Calls PL/SQL prc _interfaces.study_request_decision

See Request Decision below.

<updateLegalSituation>

Calls PL/SQL PRC_INTERFACES.update_legal_situation.

Not discussed in this documentation.

<updatePaymentCollectionCode>

Calls PL/SQL <PRC_INTERFACES.update_payment_coll_code

Not discussed in this documentation.

Inbound Acknowledgements

For all INBOUND processes, there is an acknowledgement.

OK, or KO + Error.

The Error code in PRC is important, as it uses an object, PRCException, to encapsulate all error codes and descriptions.

2.2 OUTBOUND flows

<RiskStudy>

<create_study_request>

See Study Request below.

The Payload Trigger

  • There is one trigger in PRC that sends a Payload.
  • The process is as follows; When a request is created, and the status is REQUEST_SENT, the Study Request is then sent to PRC. (Note that the Collect process can also create a request on the spot, and therefore the collect data becomes the first contact with PRC.)

Other triggers

There is a trigger that sends activity messages based on status changes.

There is a trigger that updates comments.

3 Process Described

The credit checking process follows a precise workflow. See image below. As can be seen, there is a linear process that must be followed, with each step (highlighted in yellow) moving the request forward. There is the initial request; maybe followed by one or more iterations of requests for status or information; and finally there is the decision.

What follows is a short description of the various steps managed by the PRC Interface.

3.1 The Crawl-to-PRC Study Request

A Crawl study request, when sent to PRC, initiates PRC’s credit checking process. This is Crawl’s first contact with PRC within the larger Crawl credit checking workflow.

As already described, this is not Crawl’s starting point in the credit checking workflow. Already, by this point, a request has been established in one or more forms – either linked to a quotation, a customer, or a broker.

The message sent to PRC is in XML format. The Study Request XML (SR XML) contains all the information that PRC needs to start performing a credit check.

To create the SR XML, a Request Id needs to have already been generated via iSee or iForm 988. The Request Id enables the software to find and consolidate the customer’s financial data, and to package that information into the SR XML format required by PRC.

Once this message is created and sent to PRC, the Request Status changes to RST006 (=Sent to PRC).

3.2 Study Request details

The SR XML contains a large amount of information (last count, 280 fields), most of which will not be described in this document. If you need to know more about individual fields, please browse the PRC Interface Library.

Here is a list of some key fields.

<ThirdPartyType>

Company Registration Number or Codice Fiscale.

<externalSystemRiskStudy>

Crawl’s internal reference ID (the word External is from the point of view of PRC, which considers Crawl an <externalSystem>).

<studyType>

The kind of PRC request being made. Almost all requests have the value RQTLIM, which indicates a Limit credit check. Very few have RQTNTT, which indicates a Rating credit check. Rating is used to get a company’s overall credit rating, which Crawl does not currently use.

<status>

The status indicates the current state of the Crawl workflow. For Crawl, The initial study request is at status RST006, which means the request is “Sent to PRC”. This status will change during the Crawl-to-PRC workflow. See below for a full discussion of statuses.

<quoteNumber>

This is the value associated with a quotation. If there is no quotation, then it will be blank. Not every credit check involves a quotation.

<riskThirdPartyInfo>

Contains the internal and external reference numbers, usually corresponding to the internal Crawl company code and the “external code”, if there is one. The external code might be PRC’s own internal code used for that same company, or it might be the company’s registration number. This Internal/External link is made using the table crchreq_rtpi_ext_refs.

3.3 Request Status

This message does 2 things:

1) It allows PRC to tell Crawl the status of a request within PRC. As already mentioned, the request goes to PRC with a status of RST006. This will be changed by PRC’s below responses:

  • If PRC sends an RST002 to Crawl, this means it has received the request but it is not currently being analyzed.
  • If PRC sends an RST003 to Crawl, this means the request is being analysed.

The various meanings of status is discussed further below.

2) It also communicates to Crawl, for the first time, PRC’s own Internal ID for the Study Request. From this point forward, there will be 2 IDs used: the “request id” and the “study id”. Note that the words “study ID” can also refer to Crawl’s Request ID. Context will make things clear. For example, whenever PRC sends Crawl a message, the combo “External Study ID ” refers to Crawl’s internal request id.

3.4 Request Collect

In order for PRC to analyse a request, it needs to interface with a number of external systems (external to PRC) to obtain further information about the company and about the particular request. Much of this information is financial, but there is perhaps insurance related information sought after, or legal information about the company.

Crawl is one of the external systems that PRC uses to collect information. This is done via the Request Collect to Crawl.

3.4.1 Referential data for the collect

The request collect takes two important identifying parameters: request id and company registration number.

If there is an existing request (request id is not null), the collect is easy enough to perform, as the request already contains an id – and therefore sufficient identifying information – to obtain the collect information.

If there is no request (request id is null), then the collect becomes a conduit to start the credit checking within Crawl and to then to perform the collect right away. This is called a DIRECT request type. In other words, at this point, the request collect process actually creates the credit checking header records (in tables CRCHREQ_HEADERS and CRCHREQ_COMPANY_CONTRACTS  as described above) and then performs the collect at the same time.

3.4.2 The COLLECT details

In both cases, the collect is performed by a combination of generating all of the information necessary to perform the collect (in PRC_COLLECT) and saving all that information in a set of staging tables (CRCHREQ_COLLECT tables), , and then to return the data from those tables in a structured XML format (structured according to precise business / logical groupings).

The PRC_COLLECT is a PL/SQL function within the CREDIT_CHECKING package. This package contains all of the business rules used to calculate the financial and other information required in the collect. While the nature and meaning of this information can remain, in theory, a black box for the interface developer, too many defects and enhancements require the interface developer to nonetheless analyse and understand some of the details of the black box in order to correctly obtain and send the information to PRC.

Some of the most important information:

  • Arrears – Money due to Crawl
  • Monaco Company identifier – Customer center which should be company code or company registration number or codice fiscal (see the Monaco section below)

The process builds a list of relevant companies within the requesting company’s hierarchy and within monaco groupings.

For each relevant company, get the pay behaviour, exposure and arrears, consolidated data, contacts and delegations and guarantors, and group summaries, putting all of this data in a series of staging tables named CRCHREQ_COLLECT_%:

  • the number of contracts, number of leases, amount overdue, or not, account status, payment dates
  • arrears – amounts delivered and owed
  • total cars, total amounts, total values, risk amounts, etc..
  • individual and group amounts

3.4.3 The COLLECT return data

Once this data is calculated in CREDIT_CHECKING, and saved to the COLLECT_% tables, the interface then reads exclusively from these staging tables, reformatting the table data into a structured XML.

Just to recap, the process can be described as follows:

  • A request for a collect is made.
  • If there is no study request yet, create the study request using the company identifier.
  • Calculate all data asked for by PRC and save it all to staging tables
  • Send this data back to PRC in an XML format.

The PRC_INTERFACES package is responsable for the last part: it restructures the COLLECT data and sends it back to the Java Web Services, which had been waiting synchronously. The Java Web Service then distributes that data into its own classes before sending it back to PRC (via Tibco).

One thing to keep in mind about the format of the data: the functional structure of the table does not in all cases match the structure of the final XML. This is most evident in the way the COLLECT_HEADER data is broken up into even smaller units like forecastFleet, commercialGroup, customerData, etc.. The important thing to keep in mind is that the data from the staging tables needs to find its place in the XML.

Now the Java Web Service comes into play, taking that set of packaged data and finding a place among its own classes. These classes more or less match the grouping done by PRC_INTERFACES but with some small exceptions, so be careful. Again, it is important that the data in the collect tables find their proper place within the Java Classes, for it is Java and these classes That will ultimately create the final format or the sent XML.

A note on the method to read the COLLECT tables. This is contained in a recursive cursor within the return_cur() function in PRC_INTERFACES. At first sight, this is odd, but after understanding how it functions, it’s efficient. Essentially, all of the groupings of data are contained within a returned cursor, which is itself formed by several inner cursors. Each CURSOR  represents a group of data. Some of the cursors are taken from the same table (collect_hearders) and other are taken from the different collect tables mentioned above. This cursor of cursors represents the full XML that is sent to PRC.

3.5 Request Decision

The decision taken by PRC regarding the request is communicated to Crawl via this message. This message is unique in PRC because it’s really the only message that we receive from PRC with new information (apart from the status changes in Request Status). PRC can send Crawl an Yes (Approval) or No (Decline), with some subtle distinctions like Outright Decline, or Approved as Proposed, a Conditional Approval. Each of these PRC_DECISION_SUB_CODES have a different functional meaning that is not coded but forms part of the manual functional process.

The code in the interface, during this decision event, is simply a passive receptor of data, with little business logic except for some mapping logics to change incoming values into values expected by Crawl.

The decision process also puts an end to the Interface with PRC, change the status to COMPLETED.

4 Workflow, Step-by-Step

4.1 First, Send a Study Request to PRC

Preliminaries (request header is created)

In forms, Crawl 435 or Crawl 196 gather data about the initial request, which they then send (via Forms Context) to Form 988. At some point in Form 988, a request header is created.

ISee is another starting point, creating a PRC request via its proposal screen. This request is created via the prc_interfaces package, in populate_crchreq_struct, wherein a request header is created.

Where 116311 is a quote id that does not already have a Quote ID.

Note that the quote ID needs to have all proper info filled out, for example CCIA. Here is a query that can be used to find a Quote that has not been sent to PRC:

Trigger (CRCHREQ_ARIU)

At this point, the field status is updated and should have the value NEW or REQUEST_SENT (maybe also PENDING_SENT or PENDING_SEND). Whatever the status’ value, when it eventually gets to be REQUEST_SENT, this will trigger the involvement of the PRC platform by formulating a Risk Study message to be sent to PRC.

The RiskStudy message

This is the payload, as described above. For this to occur, there has to be a request header (id).

4.2 Next, Get a Status Update

This is the first inbound message received by PRC.

PRC now has the request, and it should respond by sending a status of Received (RST002) and/or Allocated or Working on it (RST003). It will also send Crawl it’s PRC Study ID.

As mentioned elsewhere, this will be usually an RST002 or RST003.

4.3 Next, Process a Collect Request

The collect can come in with 2 significant sets of parameters.

One set can come in without a request id, and this not only collects information, but also creates the request header for the company using the incoming parameter ‘company registration number’. This scenario, described elsewhere in this document, will not be shown here.

The more common set of parameters is where the request collect contains a valid request ID.

Another parameter of importance is the collect type. While this field is currently ignored, it is useful to know that every collect request come in with a specific collect type, such as LEADS or EXPOSURE_DETAILS or ARREARS_DETAILS, etc., which  are actually sent as codes, like CTP004, or CTP011, etc. Using these codes enables PRC to ask for specific information from Crawl and get a smaller response. For now, we turn every request into a full request (“ALL_DETAILS”). On the other side, within Tibco, I believe that Tibco does in fact look at the collect type and performs a filtering that sends back only the information that PRC asked for. If that is indeed the process, then an EXPOSURE_DETAILS request will return ALL_DETAILS  but Tibco will then filter out the exposure details and send PRC these filtered results. This is useful information because it explains why we can receive 10 or more collect requests for the same request id.

To run the collect request in PL/SQL, here is the call: 

However, to view the results, you will need to parse the massive cursor that gets returned.

4.4 Last, Process a Decision

This message is the final inbound message sent by PRC. It will contain a number of parameters. We do very little business logic within this function, we simply take all of the parameters and put them into the CRCHREQ_DECISIONS table.

5 Related Topics

5.1 Starting the PRC Process

The first step in the PRC process has some particularities that need to be understood.

Within Forms, there are two starting points: Form 196 and Form 435. Outside of Forms, there is iSee.

Form 196 starts with an existing customer (a company in the companies table). The user of Form 196 can send a PRC request for any specified amount of money. This amount may or may not be associated with a quotation.

Form 435 starts with a quotation. In this scenario, the amount requested is the amount of the quotation, and it can be linked to an existing customer or it can be requested for a potential customer (a company not yet in the companies table).

Eventually, in the non-customer 435 scenario, a company record is created but it will have very little associated information. It will be a skeletal company, with the minimum required to be saved to the database (a name, a company type and code). PRC will use whatever information we send to identify the company, such as company name.

ISee, like 196, can send a request for an existing customer.

ISee can also send a request based on a proposal for a new customer called a prospect. This request is always linked to a quotation / proposal.

In the quotation / proposal scenario, a prospect is inserted into the companies table on the spot but is given a different status than a customer (contract not signed). The company created via iSee proposals will have more information than that of 435, because the proposal process gathers quite a lot more information about the prospect-company.

So we can consider the above as having 3 distinct starting points:

  1. PRC request on an existing customer (via 196, 435 and iSee)
  2. PRC request on a skeletal company (via 435).
  3. PRC request based on a proposal for a new company as prospect (via iSee).

Scenario 1 may be further broken down into an existing customer with a proposal (iSee) or no proposal (196, 435)

All of this can help us understand how to handle identity issues when dealing with later topics such as creating a company or grouping data based on the Monaco database. Note that the existence of a company registration number is also significant, as discussed below in the Referential Data section.

For example, for each scenario, the identifying key for a company is different. For existing companies, the company code is used to identify the company. However, for new customers (via 435 and iSee proposals), there are some complications on how to identify the company. In these scenarios, there is the company registration number.

This has particular relevance during the Monaco grouping logic, as described below.

5.2 MONACO database

Companies in the Companies database can be related to each other in many ways. There can be hierarchical or ownership relationships, departments within the same company, or the company can be simply repeated (for a variety of reasons, Crawl requires that we create new companies records for the same company; for example, in order to have several brokers make a request for the same customer and not to allow those brokers to see each others’ requests, Crawl will create the same company for each broker.

However, there is nothing in Crawl itself that knows which companies are related to each other. Crawl therefore relies on an external database called Monaco to maintain these links.

There is Level 1 grouping, which links a company to other companies within the same group. Monaco probably does this for departmental and repeated companies.

There is Level 2 grouping which groups all Level 1 groups into a bigger group. This probably groups together companies linked by hierarchical or ownership relationships.

Users view and manage this information in Crawl Form 908.

5.3 Referential Data

Crawl’s basic referential key is company type/code.

Outside of the Crawl context, a company registration number is the most accurate referential key.

  • For the UK, the company registration number is exactly that, a number used to register a company in the UK.
  • Note that in the UK, not all entities doing business in the UK require a registration number. In that case, there will be no registration number. This will obviously impact any part of Crawl that uses registration number exclusively as a referential field. In most cases, however, Crawl relies on its own identity in company type/code.
  • For Italy, the company registration number should always be filled because all companies in Italy require some form of unique business ID. This ID is called the Codice Fiscale.

In terms of the PRC process, the company registration number can be used to obtain the correct link to the Monaco database (client number). It can also be used, along with the request id, to follow and link the different PRC requests (study, status, collect, decision). Again, if there is a company type/code, this too can be used, and probably should be the first place to look for a key, and only when that fails should company registration be used.

The scenarios where this becomes complicated are for new companies (prospects in the companies table) or non-existing customers (not present in the companies table).

A note on the customer centre field in the companies table:

  • This field has a legacy meaning. Originally, when Crawl was designed, it took its data from another system. That system’s unique identifier for companies was put in the customer centre field. At that time, then, companies had two unique IDs: company code (generated by Crawl) and customer centre (migrated from an older system).
  • Now, for all new customers, with only one exception, the value in customer centre is the company code. This is ensured in Forms, iSee and the SFA Interface, in that both company code and customer centre fields are automatically generated and are not available for the user to change.
  • The only exception is for “invoicing companies”, which are automatically created. They are given unique IDs in the customer centre that differ from their company codes.

5.4 STATUS

The status of a PRC request is very important for several reasons:

  • it changes according to what part of the workflow the request is in
  • Its value, or a change to its value, can trigger actions that push the workflow forward
  • Its value is displayed to the user, but not exactly like it appears in the database, but a more user friendly version of it
NEW New
REQUEST_SENT Proposal Sent
RST006 Proposal in transit / TIBCO
RST002 Received by PRC
RST003 Allocated to Analyst
COMPLETED Completed
PENDING_SEND Pending Send
REQUEST_SENT Proposal Sent
RST006 Proposal in transit / TIBCO
RST002 Received by PRC
RST003 Allocated to Analyst
COMPLETED Completed
Preceded by NEW or PENDING_SENT
RST005 Proposal Cancelled
Sometimes followed by REQUEST_SENT
NEW New
SAVED Saved for later transmission
Sometimes followed by REQUEST_SENT

 

5.5 The ISee Web Application

*Note, iSee as an application is fully covered in other documents. This document only discusses iSee’s role in initiating a Study Request.

There are perhaps two ways of looking at iSee and how it relates to PRC. One is what sort of process is begun via iSee (functional view). The other is how it accomplishes this process (code analysis).

The functional view

Once a Proposal is reached with a customer, iSee will offer the broker to save and submit the proposal. This submittal process will actually save the proposal and create a study request (in the CRCHREQ_% tables). The request status will be changed to REQUEST_SENT, which will then trigger a study request to be sent to PRC.

As noted elsewhere, the identity of the company will be either a company code (for existing companies), or for any “anonymous” companies in which there is known only the Company Registration Number, or not even that.

Code Analysis

The final point of the iSee-to-PRC process is found in the populate_crchreq_struct  function in PRC_INTERFACES, which requires a Quote ID and a Quote Revision. This function will create a header record in CRCHREQ_HEADERS. The code here approximates what is already being done in 988.

This creates a new study request. ISee does nothing more with the request, or with PRC in general. The rest of the workflow is between Crawl Forms and PRC.

As already indicated, iSee involves PRC requests that are associated with a Quote, and that this originates within the proposal screen. 

Done.