---
title: "SQL-074: Retrieve policy collection"
summary: "Retrieve policy collection"
status: draft
owner: Technical Writer
last_reviewed: 2026-07-31
library: SAIF.Business.Policy.Core
reference_type: package
---

# SQL-074: Retrieve policy collection

Retrieve policy collection in the context of `clsPolicyCollection`.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-074 |
| **Operation type** | Read |
| **Execution method** | IDataRequest.SQLStatement (CommandType.Text) |
| **Database** | Oracle |
| **Schema** | [unqualified]** |
| **Command type** | Text |
| **Business object** | `clsPolicyCollection` |
| **Criteria property** | `RetrieveSQL` |
| **Lifecycle method** | `BeforeRetrieve / AfterRetrieve` |

## Parameters

This statement has no bind parameters.

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [POLICY](../database-tables.md#policy) | SELECT | See sample SQL |

## Sample SQL

```sql
SELECT
  POLICY.POLICY_NUM as PolicyNumber,
  POLICY.POLICY_NAME as PolicyName,
  POLICY.POLICY_STATUS_CODE as PolicyStatusCode,
  POLICY.LEGACY_POLICY_NUM as LegacyPolicyNumber,
  POLICY.APPLICATION_DATE as ApplicationDate,
  POLICY.APPLICATION_NUM as ApplicationNumber,
  POLICY.APP_POSTMARK_DATE as ApplicaionPostmarkDate,
  POLICY.APP_RECEIVED_DATE as ApplicationReceivedDate,
  POLICY.HOLD_CANCEL_DATE as HoldCancelDate,
  POLICY.CURRENT_BALANCE as CurrentBalance,
  POLICY.ENTITY_CODE as EntityCode,
  POLICY.FINALIZE_USER as FinalizeUser,
  POLICY.FINALIZE_DATE as FinalizeDate,
  POLICY.SEASONAL_FLAG as SeasonalFlag,
  POLICY.POLICY_ORIG_EFF_DATE as OriginalEffectiveDate,
  POLICY.HOLD_CANCEL_USER as HoldCancelUser,
  POLICY.HOLD_CANCEL_REASON_CODE as HoldCancelReasonCode,
  POLICY.CREATE_DATE as CreateDate,
  POLICY.CREATE_USER as CreateUser,
  POLICY.UPDATE_USER as UpdateUser,
  POLICY.UPDATE_DATE as UpdateDate,
  POLICY.APPLICATION_SIGNED_FLAG as ApplicationSignedFlag,
  POLICY.GROUP_ENROLLMENT_FLAG as GroupEnrollmentFlag,
  POLICY.MCO_RESTRICT_FLAG as MCORestrictFlag,
  POLICY.WCD_NUM as WCDNumber,
  POLICY.VOLUNTARY_DATE as VoluntaryDate,
  POLICY.REJECTION_REASON_CODE as RejectionReasonCode,
  POLICY.REJECTION_DATE as RejectionDate,
  POLICY.QUOTE_REQ_TYPE as QuoteRequestType,
  POLICY.TAIL_COVERAGE_FLAG as TailCoverageFlag,
  POLICY.OTHER_CARRIER_DATA_REQ_FLAG as OtherCarrierDataRequiredFlag,
  POLICY.MARKET_TYPE_CODE as MarketTypeCode,
  POLICY.LAST_RATE_SURVEY_DATE as LastRateSurveyDate,
  POLICY.OFFICE_CODE as OfficeCode,
  POLICY.CUSTOMER_SERVICE_TEAM_CODE as CustomerServiceTeamCode,
  POLICY.SALES_REP as SalesRep,
  POLICY.QUOTE_UNDERWRITER as QuoteUnderwriter,
  POLICY.PROPOSAL_DATE as ProposalDate,
  POLICY.AGENT_DIRECT_SPLIT_FLAG as AgentDirectSplitFlag,
  POLICY.PREMIUM_ESTIMATE as PremiumEstimate,
  POLICY.PMS_ACCOUNT_SUBCD as PmsAccountSubcd,
  POLICY.HOLD_INVOICE_FLAG as HoldInvoiceFlag,
  POLICY.MANAGEMENT_EXPERIENCE_DATE as ManagementExperienceDate,
  POLICY.LEGAL_NAME as LegalName,
  POLICY.TRADING_SEQ_NUM as TradingSeqNum,
  POLICY.AOI_EXCLUSION_FLAG as AoiExclusionFlag,
  POLICY.RATE_ACCOUNT as RateAccount,
  POLICY.NCCI_RISK_ID as NcciRiskID,
  POLICY.INTERSTATE_RISK_ID_FLAG as InterstateRiskIDFlag,
  POLICY.LAST_PVT_CARRIER_COVER_EFF_DTE as LastPvtCarrierCoverEffDte,
  POLICY.SURETY_BOND as SuretyBond,
  POLICY.POLICY_RELATIONSHIP_POLICY_NUM as PolicyRelationshipPolicyNum,
  POLICY.POLICY_RELATIONSHIP_CODE as PolicyRelationshipCode,
  POLICY.POLICY_RELATIONSHIP_PRT_DTL_FL as PolicyRelationshipPrtDtlFL,
  POLICY.AGENT_NUM as AgentNum,
  POLICY.VENDOR_NUM as VendorNum,
  POLICY.HIST_CONT_COVERAGE_DATE as HistContCoverageDate,
  POLICY.DONOT_AUTO_REINSTATE_FLAG as DonotAutoReinstateFlag,
  POLICY.UPDATE_NUM as UpdateNum,
  POLICY.NCCI_BINDER_NUM as NcciBinderNum,
  POLICY.BYPASS_AUTO_PRICING_FLAG as BypassAutoPricingFlag,
  POLICY.DUPLICATE_CLAIM_CHECK_FLAG as DuplicateClaimCheckFlag,
  POLICY.PAY_RPT_PRINT_COPIES as PayRptPrintCopies,
  POLICY.CANCELLATION_TRACK_CODE as CancellationTrackCode,
  POLICY.INCLUDE_MANUAL_CANCEL_FLAG as IncludeManualCancelFlag,
  POLICY.SECURE_POLICY_CODE as SecurePolicyCode,
  POLICY.SUB_REQUEST_REASON_CODE as SubRequestReasonCode,
  POLICY.account_num,
  POLICY.gw_conversion_eff_Date,
  POLICY.billing_level_code as BillingLevelCode,
  sp72814_get_cont_cov_date(policy.policy_num) as ContinuousCoverageDate
FROM
  POLICY
```

## Operations and processes
- [Retrieve affiliated policies / policies for an account](../operations/retrieve-affiliated-policies-policies-for-an-account.md)

## Business rules
- [BR-020: When masking is requested, policyholder names, contact identity/contact-method](../business-rules.md#br-020-when-masking-is-requested-policyholder-names-contact-identitycontact-method)

> **Note:** Statements marked with * were inferred from code structure, naming conventions, or
> contextual evidence where direct confirmation was not available in source artifacts. Statements
> marked with ** could not be confirmed; treat as unknown until verified.

## Sources
- `Business\SAIF.Business.Policy.Core\clsPolicyCollection.vb`