Data Sources¶

Premia UAT Database¶

All documentation in this guide is extracted from the Premia UAT (User Acceptance Testing) database.

Database Connection¶

Host: 192.168.151.137
Port: 1521
Service: UAT
Schema: PREMIA_UAT

Extraction Method¶

Data extraction uses direct Oracle database queries via Python's oracledb library:

import oracledb
conn = oracledb.connect('premia_uat/premia_uat@192.168.151.137:1521/UAT')

Professional Indemnity - Completed¶

Extraction Date¶

October 5, 2025

Policies Analyzed¶

Product 5050 (Professional Indemnity General): 1,251 policies
Product 5060 (Medical Malpractice): 26 policies
Product 5070 (Directors & Officers Liability): 10 policies
Total: 1,287 policies

Data Extracted¶

1. Policy Statistics¶

Sum insured ranges
Premium ranges
Premium rate distributions
Policy date ranges

Source Query:

SELECT POL_PROD_CODE, COUNT(*),
       MIN(POL_SI_LC_1), MAX(POL_SI_LC_1), AVG(POL_SI_LC_1),
       MIN(POL_PREM_LC_1), MAX(POL_PREM_LC_1), AVG(POL_PREM_LC_1)
FROM PGIT_POLICY
WHERE POL_PROD_CODE IN ('5050', '5060', '5070')
GROUP BY POL_PROD_CODE

2. Field Usage Analysis¶

Mandatory fields (>90% usage)
Optional fields (>25% usage)
Field types and purposes

Source Query:

SELECT COUNT(*) as total,
       COUNT(PRAI_SI_FC) as si_usage,
       COUNT(PRAI_DATE_01) as retro_date_usage,
       COUNT(PRAI_NUM_01) as num_01_usage,
       COUNT(PRAI_NUM_02) as num_02_usage
FROM PGIT_POL_RISK_ADDL_INFO
WHERE PRAI_POL_SYS_ID IN (
    SELECT POL_SYS_ID FROM PGIT_POLICY
    WHERE POL_PROD_CODE IN ('5050', '5060', '5070')
)

3. Premium Rates¶

Actual rates calculated: Premium ÷ Sum Insured
Rate ranges by product
Average and median rates

Source Query:

SELECT POL_PROD_CODE,
       MIN(POL_PREM_LC_1 / POL_SI_LC_1 * 100) as min_rate,
       MAX(POL_PREM_LC_1 / POL_SI_LC_1 * 100) as max_rate,
       AVG(POL_PREM_LC_1 / POL_SI_LC_1 * 100) as avg_rate
FROM PGIT_POLICY
WHERE POL_PROD_CODE IN ('5050', '5060', '5070')
AND POL_SI_LC_1 > 0 AND POL_PREM_LC_1 > 0
GROUP BY POL_PROD_CODE

4. Endorsement Data¶

Endorsement frequency: 146 policies (11.3%)
Endorsement types and codes
Record type distribution

Source Query:

SELECT PER_CODE, PER_CODE_DESC, PER_REC_TYPE, COUNT(*)
FROM PGIT_ENDT_REASON
WHERE PER_POL_SYS_ID IN (
    SELECT POL_SYS_ID FROM PGIT_POLICY
    WHERE POL_PROD_CODE IN ('5050', '5060', '5070')
)
GROUP BY PER_CODE, PER_CODE_DESC, PER_REC_TYPE

5. Commission Data¶

Commission field analysis
Profit commission usage (0%)
Commission table structure

Finding: Standard commission data not stored in main policy table, requires separate commission table analysis.

Other Products - Pending¶

Products Awaiting Extraction¶

The following products require data extraction:

Contractors All Risk (CAR)
Product code identification needed
Policy analysis pending
Fire Insurance
Product code identification needed
Policy analysis pending
Property All Risk (PAR)
Product code identification needed
Policy analysis pending
Workmen Compensation (WC)
Product code identification needed
Policy analysis pending
Third Party Liability (TPL)
Product code identification needed
Policy analysis pending
SME Package
Product code identification needed
Policy analysis pending

Extraction Process¶

For each product: 1. Identify product codes in PGIT_POLICY.POL_PROD_CODE 2. Extract policy count and statistics 3. Analyze field usage in PGIT_POL_RISK_ADDL_INFO 4. Calculate actual premium rates 5. Extract endorsement patterns 6. Analyze commission structure

Database Tables Used¶

Core Policy Tables¶

Table	Purpose	Key Columns
PGIT_POLICY	Main policy data	POL_PROD_CODE, POL_SI_LC_1, POL_PREM_LC_1
PGIT_POL_RISK_ADDL_INFO	Risk details	PRAI_SI_FC, PRAI_DATE_01, PRAI_NUM_01/02
PGIT_ENDT_REASON	Endorsements	PER_CODE, PER_CODE_DESC, PER_REC_TYPE
PGIT_PREM_COLL	Premium collection	(Structure requires analysis)

System Tables¶

Table	Purpose	Key Columns
PCOM_APP_PARAMETER	System parameters	PAP_TYPE, PAP_CODE, PAP_VALUE
ALL_TAB_COLUMNS	Column metadata	TABLE_NAME, COLUMN_NAME, DATA_TYPE
ALL_TABLES	Table metadata	TABLE_NAME, OWNER
USER_SOURCE	PL/SQL packages	NAME, TYPE, TEXT

PL/SQL Packages¶

Premium Calculation¶

Business logic exists in PL/SQL packages (naming patterns identified but source code requires extraction): - PGIPK_CALC_PREMIUM_* - Premium calculation packages - *_RATING_ENGINE_* - Rating engine packages - *_PREM_CALC_* - Premium calculation logic

Validation¶

AIGPK_VAL_* - Validation packages
Field validation rules
Business rule enforcement

Data Quality Notes¶

What We Know with Certainty¶

✓ Policy Counts: Exact counts from database ✓ Premium Ranges: Actual min/max/average from policies ✓ Field Usage: Precise usage percentages ✓ Endorsement Frequency: Real transaction counts

What Requires Further Analysis¶

⚠ Rating Formulas: Stored in PL/SQL packages (source code extraction needed) ⚠ Validation Rules: Business rules in packages ⚠ Commission Rates: Separate commission tables (identification needed) ⚠ Refund Calculations: Endorsement calculation packages

What Is NOT Available¶

✗ Test/Sample Data: All data is from real UAT policies ✗ Estimated Rates: No assumptions or industry standards used ✗ Invented Fields: Only documented fields that exist and are used

Extraction Tools¶

Location¶

/home/ubuntu/premia_tools/extraction/

Key Scripts¶

extract_pi_real_data.py - Professional Indemnity extraction
extract_pi_real_business_logic.py - Comprehensive PI analysis
Future: Product-specific extraction scripts

Methodology Documentation¶

Complete extraction methodology: /home/ubuntu/PREMIA_EXTRACTION_COMPLETE_GUIDE.md

Update History¶

Date	Product	Changes
2025-10-05	Professional Indemnity	Initial extraction - 1,287 policies analyzed
TBD	Contractors All Risk	Pending
TBD	Fire Insurance	Pending
TBD	Property All Risk	Pending
TBD	Workmen Compensation	Pending
TBD	Third Party Liability	Pending
TBD	SME Package	Pending

Note: All data in this guide comes from real Premia UAT database analysis. No estimates, assumptions, or industry standards are used as substitutes for actual Premia data.