SQL-033 Trading-as by policy number¶

This SQL retrieves trading-as records for a policy number so the library can build the policy's trading-as collection.

Classification¶

Field	Value
SQL ID	SQL-033
Operation type	Read
Execution method	Inline
Database	Oracle
Schema	`[unqualified]**`
Command type	Text
Business object	`clsTradingAsCollection`
Criteria property	`RetrieveSQL`
Lifecycle method	`BeforeRetrieve` / `AfterRetrieve`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:PolicyNumber`	Input	Decimal	N/A	Policy number whose trading-as records are requested.

Tables¶

Table	Access	Columns used
TRADING_AS	SELECT	`bus_seq_num`, `policy_num`, `trading_seq_num`, `feid_num`, `trading_as_cancel_date`, `trading_as_name`, `uitr_number`, `trading_as_name_eff_date`, `affiliate_ssn`, `create_user`, `create_date`, `update_user`, `update_date`, `registration_date`, `trading_as_type_code`

Sample SQL¶

SELECT
  TRADING_AS.BUS_SEQ_NUM AS BusSeqNum,
  TRADING_AS.POLICY_NUM AS PolicyNum,
  TRADING_AS.TRADING_SEQ_NUM AS TradingSeqNum,
  TRADING_AS.FEID_NUM AS FeidNum,
  TRADING_AS.TRADING_AS_CANCEL_DATE AS TradingASCancelDate,
  TRADING_AS.TRADING_AS_NAME AS TradingASName,
  TRADING_AS.UITR_NUMBER AS UitrNumber,
  TRADING_AS.TRADING_AS_NAME_EFF_DATE AS TradingASNameEffDate,
  TRADING_AS.AFFILIATE_SSN AS AffiliateSsn,
  TRADING_AS.CREATE_USER AS CreateUser,
  TRADING_AS.CREATE_DATE AS CreateDate,
  TRADING_AS.UPDATE_USER AS UpdateUser,
  TRADING_AS.UPDATE_DATE AS UpdateDate,
  TRADING_AS.REGISTRATION_DATE AS RegistrationDate,
  TRADING_AS.TRADING_AS_TYPE_CODE AS TradingASTypeCode
FROM TRADING_AS
WHERE TRADING_AS.POLICY_NUM = :PolicyNumber

Operations and processes¶

None.

Business rules¶

BR-VP-027: Create/Insert/Update/Delete SQL are invalid

Sources¶

SAIF.Business.View.Policy/clsTradingAsCollection.vb