Deep Dive: Remaining Modules
1. Shipping / Document Tracking (pwrpclos + doctrac)
Narrative
The Shipping module manages post-closing operations: tracking required documents per loan and tracking shipment tasks per trade. It ensures all required documents are received, reviewed, and shipped before investor delivery deadlines.
Document Tracking Workflow
Business Rules
- Default documents are determined by product type, investor, guarantor, and counterparty
- Documents come from 4 sources:
pscat_products_documents_rel,pscat_trades_documents_rel,pscat_guar_doc_relation,pscat_trade_cntrprtys_doc_rel - Initial status per document type is configured via
n_cst_document_tracking.of_get_initial_status_for_document() - Default document retrieval runs only once per session (
ib_allow_default_doc_retrieval = FALSEafter first run) - Batch mode supports updating statuses across multiple loans simultaneously
Shipment Task Tracking
- Tracks shipping tasks per trade (not per loan)
- Tasks have statuses and target dates
- Shared UI component
uo_tab_trackinghandles both document (dt) and shipment (st) tracking via context parameter
Data Model
| Table | Purpose |
|---|---|
pscat_loans_documents_relation | Loan-to-document status tracking (PK: loan_id + document_name + status + status_date) |
pscat_documents | Document definitions |
pscat_document_types | Document type categories |
pscat_document_status_relation | Valid status transitions |
pscat_products_documents_rel | Default documents by product |
pscat_trades_documents_rel | Default documents by trade |
pscat_guar_doc_relation | Default documents by guarantor |
pscat_trade_cntrprtys_doc_rel | Default documents by counterparty |
2. Bid Packaging (bid_pkg)
Narrative
The Bid Packaging module supports bulk loan sales by creating bid packages, soliciting investor bids, and selecting winning bids. Pools of loans are offered to multiple investors who submit competing prices.
Bid Packaging Workflow
Business Rules
- Bid packages reference pools marked with
bulk_sale_flag = 'y' - Each package can have multiple investor bids
- Bid components: bid_price, bid_msr_price, price_adjustment, handling_fees
- Weighted average price computed across instruments in the package
- Run analysis uses the same pricing engine infrastructure as Best Execution
Data Model
| Table | Purpose |
|---|---|
rmcat_bidpkg_investor_bids | Investor bid prices per package (PK: package_name + investor_id + profile_name) |
rmcat_bidpkg_investor_inst | Bid instrument details (PK: package_name + investor_id + bid_price_component + instrument_name + profile_name) |
d_bulk_packaging_investors | Investors eligible for bulk packaging |
d_list_open_pools_for_bid_pkg | Available pools for packaging |
3. Import/Export (import + app_extf)
Narrative
The Import module loads loan and entity data into PowerSeller from external sources (files, databases, XML). The Export module writes data out in 11 formats. Both use configurable mapping systems to translate between external and internal field names.
Import Validation Pipeline
Export Formats
| Format | Extension | Notes |
|---|---|---|
| CSV | .csv | Configurable separator/delimiter |
| DBase3 | .dbf | Legacy format |
| Excel8 | .xls | Excel 97-2003 |
| XLSX | .xlsx | Excel 2007+ |
| HTML | .html | Table format |
| Report format | ||
| PSR | .psr | PowerBuilder saved report |
| SQL Insert | .sql | INSERT statements |
| Text | .txt | Fixed-width or delimited |
| XML | .xml | Custom XML structure |
| XSL-FO | .fo | Formatting objects |
ULDD/XML Import-Export
- Supports MISMO 3.0, ULDD Phase 4a/5, Ginnie Mae PDD 3.0
- Uses .NET interop (
nvo_ddxml→PowerSeller.DDXml.dll) - Staging tables:
psuldd_* - Maps: FRE ULDD 4A/5, FNM ULDD 4A/5, GNM PDD 3.0
4. Archive (archive)
Narrative
The Archive module manages data lifecycle by moving aged records from active tables to archive tables, and optionally restoring or purging them.
Archive/Restore/Purge Workflow
Archive Contexts
| Context | Active Tables | Archive Tables | Description |
|---|---|---|---|
| shipped | pscat_trades → psarc_trades + related | Trade settlement data | |
| doctrac | pscat_loans_documents_relation | psarc_loans_documents_relation | Document tracking |
| loan_mod | loan_modifications | psarc_loan_modifications | Loan modifications |
| exp_option | Expired option trades | Archive tables | Expired options |
| bestex | BestEx analysis results | Archive tables | Pricing analysis |
| inquiry | Inquiry results | Archive tables | Rate sheet inquiries |
| notional | Notional results | Archive tables | Notional analysis |
| riskprices | Risk prices | rmarc_prices | Historical prices |
Business Rules
- Table structures are verified before archive/restore (synced if mismatched)
- Unsettled trades are excluded from shipped archive (logged in excluded records)
- Archive uses date-based WHERE clauses
- Primary key columns are auto-detected for cursor/WHERE generation
5. Security (security + app_sec + app_cyph)
Narrative
The Security module implements group-based role-based access control (RBAC), site licensing, user management, and encryption for sensitive configuration data.
RBAC Model
Access Control Rules
- Admin user gets full access to everything
- secadmin user gets full access to security-related menus
- Regular users get access via group membership
n_cst_user_security.of_get_menu_access(menu_name, user_name)returns 'w', 'r', or 'n'n_cst_user_security.of_get_control_access(control_id, user_name)returns same levelsib_override_securityflag can bypass security checks
Licensing
- Site information in
pxcat_site_info(company, modules, user_limit, expiration, site_key) - Module licensing: Risk Manager, Data Manager, Secondary Manager, Post Closing, App Runner
- Concurrent user limits enforced via
pxcat_user_registrations - Plugin licensing: per-plugin expiration dates in
pxcat_site_plugins
Encryption
- Custom hash-based substitution cipher in
n_cst_encryption of_encrypt_value()/of_decrypt_value()for application-level encryption- Optional machine binding (machine name embedded in encrypted value)
- SQL Server
EncryptByPassPhrase/DecryptByPassPhrasefor database-level encryption of plugin configs
Login Flow
- Standard login: username + password
- Integrated security: Windows authentication
- Admin recovery: GoldKey master password
- Password history:
pxcat_password_historytracks changes - Login attempts: logged for audit
6. Macros (app_macr)
Narrative
The Macro module provides automation capabilities, allowing users to define and schedule batch operations across all PowerSeller modules.
Macro System
Macro Module Types
| Module | Operations |
|---|---|
| Data Manager | Database import, file export/import, process pipeline, reports, stored procedure, SQL file, email report, XML export/import, VMD data pipeline |
| Risk Manager | Risk analysis, risk analytics, store prior risk results |
| Secondary Manager | Pools, unpool, pricing best execution, rate sheet inquiry/notional, set futures dates, best efforts process/settle, reservation import, update issue date principal balance |
| DDE | DDE pricing execution |
| Loan Eligibility | Loan eligibility analysis |
| Plugins | Custom plugin modules (from pxcat_macro_module_plugins) |
Business Rules
of_user_has_access_to_module(module_name)checks both service licensing and menu access- Macro groups can run at specified times and intervals
- Command-line support:
-exit(exit after run),-start(auto-start),-interval(repeat interval) - Macros execute in the sequence defined within the group
7. Reports (reports)
Narrative
The Reports module provides DataWindow-based reporting with print preview and batch printing capabilities.
Key Reports
| Report | Description |
|---|---|
| Risk Position Reconciliation Summary | Aggregated risk exposure by profile/segment |
| Risk Position Reconciliation Detail | Detailed position breakdown |
| Cumulative Value Change (SEC 105/109) | Regulatory reporting for cumulative value changes |
| Cost of Hedging | Analysis of hedging costs over time |
| Cross Ratios | Cross-instrument ratio analysis |
Reporting Infrastructure
- Reports are DataWindow objects (
.srd) with embedded SQL, bands, and formatting n_cst_reportshandles report execution, formatting, and output- Reports accessible from every module's menu via
w_pick_multiple_reports_sheet - Batch printing support via
w_modal_print_mod_win_batch - Reports can be triggered via the macro system for automated execution
f_print_report_headergenerates standard report headers
Data Sources
- Risk reports use
rmcat_risk_pos_reconand related tables - Pipeline reports use
loanand related tables - Trade reports use
pscat_tradesand related tables - Reports can use VMD queries for custom data retrieval