Integrated Application Queries

---

The following queries are used by the Database Container Provider to retrieve relevant information about the container when needed by XDOC. The examples below assume the Application is a loan processing system (LPS), but apply in general to any 3rd party Integrated Application that you wish to attach documents to.

Container Attributes By Key (containerGetByKey)

Retrieves the container attributes given the unique key of the Application. NOTE: Column aliases beginning with an “x” are the internal XDOC mapped columns that must be present. All other attributes are dependent on what is needed for the XDOC user interfaces, bundling templates, archive set searches, etc.

select
    l.xLoanId as "xContainerKey"
    ,l.xParentId as "xParentKey"
    ,l.xLoanNbr as "xContainerRef"
    ,l.xLoanNbr as "xContainerName"
    ,l.xLoanNbr as "xContainerLabel"
    ,l.xLoanNbr as "LoanNumber"
    ,l.xInvestorLoanNbr as "InvestorLoanNumber"
    ,l.xLoanStage as "LoanStageCode"
    ,l.xLoanStage as "LoanStageLabel"
    ,l.xLoanType as "LoanType"
    ,l.xLoanAmount  as "LoanAmount"
    ,l.xInterestRate as "InterestRate"
    ,d.xDateSubmitted as "SubmittedDate"
    ,b.xSSN           as "BorrowerSSN"
    ,b.xLastName      as "BorrowerLastName"
    ,b.xFirstName     as "BorrowerFirstName"
    ,b.xFirstName + ' ' + b.xLastName  as "BorrowerName"
    ,p.xPropertyCity  as "PropertyCity"
    ,p.xPropertyState as "PropertyState"
    ,p.xPropertyZip   as "PropertyZip"
    ,  isNull(p.xPropertyAddr1,'') + ' ' + isNull(p.xPropertyAddr2,'') + ' '
        + isNull(p.xPropertyCity,'')  + ', ' + isNull(p.xPropertyState,'') 
        + ' ' + isNull(p.xPropertyZip,'')   as "PropertyAddress"
    from axtapp_mtg_loan l
        join axtapp_mtg_date d on (d.xLoanId = l.xLoanId)
        join axtapp_mtg_borr b on (b.xBorrId = l.xPrimaryBorrId)
        join axtapp_mtg_prop p on (p.xPropertyId = l.xPropertyId)
    where l.xLoanId = @container

Container Attributes By Ref (containerGetByRef)

Same as the above query, but the where clause uses the container alternate (“friendly”) unique key (Eg: Loan number).

select …
from   …
where  l.xLoanNbr = @container

Container Keys By Group (containerGetByGroup)

Queries the list of container objects given a group Id. E.g.: a group of loans identified by a Trade. Most Integrated Applications do not use this query.

select xLoanId from axtapp_mtg_trade_loan 
where xTradeId = @group

Last Container by User (containerGetLastForUser)

Retrieves the last container the user was viewing in the Integrated Application. If the Integrated Application keeps track of this information, it is used by various XDOC UIs (Document Viewer, File Upload, etc.) if no container is specified when the UI is launched.

select xValue 
from axtapp_mtg_option WITH (NOLOCK) 
where xOwnerId = @userid
    and xOptionId = ‘LAST_LOAN’

Container Search Results (containerSearchResults)

Retrieves the attributes used for display in the Search Results Page. Similar to the Container Info queries, but usually a smaller set of columns returned.

select
    l.xLoanId as "xContainerKey"
    ,l.xLoanNbr as "xContainerRef"
    ,l.xLoanNbr as "xContainerLabel"
    ,l.xLoanNbr as "LoanNumber"
    ,l.xLoanStage as "LoanStageLabel"
    ,l.xLoanType as "LoanType"
    ,l.xLoanAmount  as "LoanAmount"
    ,b.xSSN           as "BorrowerSSN"
    ,b.xLastName      as "BorrowerLastName"
    ,b.xFirstName     as "BorrowerFirstName"
    ,b.xFirstName + ' ' + b.xLastName  as "BorrowerName"
    ,p.xPropertyCity  as "PropertyCity"
    ,p.xPropertyState as "PropertyState"
    ,p.xPropertyZip   as "PropertyZip"
    ,  isNull(p.xPropertyAddr1,'') + ' ' + isNull(p.xPropertyAddr2,'') + ' '
    + isNull(p.xPropertyCity,'')  + ', ' + isNull(p.xPropertyState,'') + ' '
    + isNull(p.xPropertyZip,'')   as "PropertyAddress"
from axtapp_mtg_loan l
    join axtapp_mtg_date d on (d.xLoanId = l.xLoanId)
    join axtapp_mtg_borr b on (b.xBorrId = l.xPrimaryBorrId)
    join axtapp_mtg_prop p on (p.xPropertyId = l.xPropertyId)
where 1=1

Container Search Keys (containerSearchKeys)

Retrieves only the ContainerKey based on ad-hoc search criteria. Not used for most Projects,

select 
    l.xLoanId as xContainerKey
from axtapp_mtg_loan l
    join axtapp_mtg_date d on (d.xLoanId = l.xLoanId)
    join axtapp_mtg_borr b on (b.xBorrId = l.xPrimaryBorrId)
    join axtapp_mtg_prop p on (p.xPropertyId = l.xPropertyId)
where
    d.xLoanId = l.xLoanId
    and b.xBorrId = l.xPrimaryBorrId
    and p.xPropertyId = l.xPropertyId

Container Lists <containerList> section

Some Integrated Applications have “lists of values” that are dependent on the particular Container being viewed. For example, a Loan has a list of Borrowers for that particular loan, as well as a list of Conditions that must be met during loan processing.

When you attach a document to a Container in XDOC, you can set additional document level “fields / indexes” on the document. A document field can reference a specific “list of values” specific to the Container. For example, a W2 document for a Loan will be indexed against the Borrower that the W2 is for … by using the BORROWER list of values specific to that Loan.

Define the queries for all “container” specific lists that are used to further qualify / index a document in XDOC. . Each query will be passed the container key (@containerKey) from the Integrated Application.

List: BORROWER: All borrowers for the loan

select xBorrId as "value", xFirstName + ' ' + xLastName as "name"
from axtapp_mtg_borr
where xLoanId = @container
order by xLastName,xFirstName

List: BORROWER-PRIMARY: Only the primary borrower on each Application (1003)

select xBorrId as "value", xFirstName + ' ' + xLastName as "name"
from axtapp_mtg_borr
where xLoanId = @container and xIsPrimary = 1
order by xLastName, xFirstName

Mapped Emails for Notifications (containerGetMappedEmails)

When a document arrives in XDOC and is assigned to a Container, XDOC workflow can send email notifications to any appropriate party that is “working” on that Container. XDOC allows notification rules to be setup in order to determine who should receive the email notifications that a document has arrived. These rules can be based on the “status / stage” of the Container (E.g.: Milestone, Lifecycle, Etc), as well as the different Actor Roles of the various users working on the loan (E.g.: Underwriter, Manager, etc).

In many Integrated Applications, there are multiple users that are working on a Container (Loan), and each of these people has a different role they are performing (E.g.: Underwrite, Funder, Manager, etc.). Additionally, the Container typically goes through a “Processing Workflow” identified by stages, milestones, lifecycles, etc.

There are 2 queries that XDOC uses to determine the users that should receive notification emails:

Current Container Stage (containerGetCurrentStage)

select xLoanStage
from axtapp_mtg_loan
where xLoanId = @container

ContainerActorRoleUsers (containerGetRoleEmails)

select
    r.xRole as "Role"
    ,ltrim(rtrim(isNull(u.xFirstName,'') + ' ' + isnull(u.xLastName,''))) as "Name"
    ,u.xEmail as "Email"                 
from axtapp_mtg_loan_role r
    join axtapp_mtg_user u on (u.xUserId = r.xUserId)
where r.xLoanId = @container