pk72846_view_policy¶

Overview¶

Field	Value
Schema	`WCIS_DBA`
Type	Package
Database	Oracle
Calling libraries	`SAIF.Business.View.Policy`
Calling classes	`clsAgency`, `clsMCOCollection`, `clsPolicyMCOCollection`, `clsAffiliatedPolicyCollection`, `clsPolicyAddress`, `clsPolicyPeriodCollection`, `clsTeamAssignmentCollection`, `clsPolicyData`

Purpose¶

pk72846_view_policy is the libraries main read-model package for policy-view screens. The retrieved body returns policy summary/detail rows, agency and address data, team assignments, policy-period history, gap indicators, and several affiliation and wrap-up helper results that the library layers onto clsPolicyData and related collections.

Procedures / Methods¶

getviewdetail¶

Field	Value
Returns	`viewpolicydetail_cursor`
Called by	`clsPolicyData` ad hoc retrieval
Description	Returns the primary view-policy summary row for a policy, including entity, classification, balance, premium, gap, group, reimbursement, sales-plan, vendor, and web-address data.

Parameters¶

Parameter	Direction	Type	Default	Description
`apolicynum`	IN	`NUMBER`	—	Policy number to retrieve.
`recordset`	IN OUT	`viewpolicydetail_cursor`	—	Cursor populated with the summary detail row.

team_assign_query¶

Field	Value
Returns	`team_assign_cursor`
Called by	`clsTeamAssignmentCollection.Criteria.RetrieveSQL`
Description	Returns ordered team-assignment rows plus synthesized office, customer-service-team, agency, and producer rows.

Parameters¶

Parameter	Direction	Type	Default	Description
`apolicynum`	IN	`NUMBER`	—	Policy number to retrieve.
`recordset`	IN OUT	`team_assign_cursor`	—	Cursor populated with team-assignment rows.

getpolicyperiods¶

Field	Value
Returns	`ref_cursor`
Called by	`clsPolicyPeriodCollection.Criteria.RetrieveSQL`
Description	Returns the policy-period list used by View Policy, including masking for assigned-risk rows and effective-dated plan and rating fields.

Parameters¶

Parameter	Direction	Type	Default	Description
`apolicynum`	IN	`NUMBER`	—	Policy number to retrieve.
`atopnpolper`	IN	`NUMBER`	—	Positive values request all periods; zero keeps the default current-period behavior.
`recordset`	IN OUT	`ref_cursor`	—	Cursor populated with policy-period rows.

getpolicyaddress¶

Field	Value
Returns	`ref_cursor`
Called by	`clsPolicyAddress.Criteria.RetrieveSQL`
Description	Returns the preferred current policy or claim address and contact row, with optional fallback from `CLM` to `POL`.

Parameters¶

Parameter	Direction	Type	Default	Description
`inpolicynum`	IN	`NUMBER`	—	Policy number to retrieve.
`inaddresstype`	IN	`VARCHAR2`	—	Requested location code, such as `CLM` or `POL`.
`incontacttype`	IN	`VARCHAR2`	—	Contact type code used when joining `CONTACT`.
`ingetmailaddrwhennotfound`	IN	`VARCHAR2`	—	`Y` enables claim-address fallback to `POL`.
`recordset`	IN OUT	`ref_cursor`	—	Cursor populated with the selected address row.

getpolicyaddressbylocseqnum¶

Field	Value
Returns	`ref_cursor`
Called by	`clsPolicyAddress` ad hoc retrieval
Description	Returns the preferred address and contact row for an explicit location sequence number.

Parameters¶

Parameter	Direction	Type	Default	Description
`inlocationseqnum`	IN	`NUMBER`	—	Location sequence number to retrieve.
`recordset`	IN OUT	`ref_cursor`	—	Cursor populated with the selected address row.

getagency¶

Field	Value
Returns	`agency_cursor`
Called by	`clsAgency.Criteria.RetrieveSQL`
Description	Returns agency and producer contact information for the current agent of record.

Parameters¶

Parameter	Direction	Type	Default	Description
`apolicynum`	IN	`NUMBER`	—	Policy number to retrieve.
`recordset`	IN OUT	`agency_cursor`	—	Cursor populated with agency contact data.

getgaps¶

Field	Value
Returns	`gap_cursor`
Called by	`clsPolicyData` ad hoc retrieval
Description	Returns coverage-gap rows derived from adjacent policy periods and explicit `POLICY_COVERAGE_GAP` rows.

Parameters¶

Parameter	Direction	Type	Default	Description
`apolicynum`	IN	`NUMBER`	—	Policy number to inspect.
`recordset`	IN OUT	`gap_cursor`	—	Cursor populated with gap rows.

getpolicyaffiliatetype¶

Field	Value
Returns	`VARCHAR2`
Called by	`clsPolicyData` obsolete helper
Description	Classifies the policy as a manual-affiliation, relationship, combination, premium-discount, or non-affiliated policy.

Parameters¶

Parameter	Direction	Type	Default	Description
`in_policy_num`	IN	`NUMBER`	—	Policy number to classify.
`in_is_policyholder_user`	IN	`VARCHAR2`	—	`TRUE` enables manual policyholder-affiliation checks before other families.

getpolicyaffiliatelist¶

Field	Value
Returns	`ref_cursor`
Called by	`clsAffiliatedPolicyCollection.Criteria.RetrieveSQL`
Description	Returns affiliated-policy members and an optional totals row for the requested affiliation family.

Parameters¶

Parameter	Direction	Type	Default	Description
`in_policy_num`	IN	`NUMBER`	—	Anchor policy number.
`in_affiliate_type`	IN	`VARCHAR2`	—	Requested affiliation family.
`in_include_affil_totals`	IN	`VARCHAR2`	—	`TRUE` includes a totals row for supported families.
`recordset`	OUT	`ref_cursor`	—	Cursor populated with affiliate rows.

getparentpolicy¶

Field	Value
Returns	`NUMBER`
Called by	`clsPolicyData` obsolete helper
Description	Returns the parent policy number that corresponds to the resolved affiliation type.

Parameters¶

Parameter	Direction	Type	Default	Description
`in_policy_num`	IN	`NUMBER`	—	Policy number to evaluate.
`in_is_policyholder_user`	IN	`VARCHAR2`	—	`TRUE` enables manual policyholder-affiliation checks first.

getwrapuplegalname¶

Field	Value
Returns	`VARCHAR2`
Called by	`clsPolicyData` ad hoc retrieval
Description	Returns the wrap-up legal name, escalating from the member row to the relationship policy and then the rate account when present.

Parameters¶

Parameter	Direction	Type	Default	Description
`in_policy_num`	IN	`NUMBER`	—	Policy number to evaluate.

Business Rules¶

The retrieved database source embeds the following server-side rules.

Rule ID	Summary
BR-VP-018	Coverage-gap detection excludes flat-cancel and `PPROS` periods before comparing adjacent periods.
BR-VP-019	`RF` policy periods blank risk-type and plan output until `SP72347_INFO_PAGE_PRINTED_YN` returns `Y`.
BR-VP-020	When callers allow fallback and no active `CLM` location exists, address retrieval switches to `POL`.
BR-VP-021	Address lookups limit results to active contacts and the most recent effective matching location, then return only the first ranked row.
BR-VP-022	Affiliate detection checks policyholder or manual affiliations first, then relationship, combination, and premium-discount affiliations.
BR-VP-023	Parent-policy lookup chooses `policy_relationship`, `policy_relationship_policy_num`, `rate_account`, or premium-member logic based on affiliate type.
BR-VP-024	Affiliate lists add a totals row only when `in_include_affil_totals = 'TRUE'`, using family-specific labels.
BR-VP-025	Wrap-up legal-name resolution starts on the member row, then follows the relationship policy and rate account when present.

BR-VP-018 Gap detection ignores flat-cancel and prospect periods¶

Field	Value
Statement	Gap detection ignores policy periods with status `FC` or `PPROS` before comparing one periods cancel or expiration date to the next periods effective date.
Trigger	`getgaps` or `getgapindicator` executes.
Enforcement	Both routines filter out `policy_period_status_code` values `FC` and `PPROS` and compare only the remaining periods.
Outcome	Only meaningful coverage gaps are returned or counted.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-019 Assigned-risk policy periods mask risk-type and plan data until the info page prints¶

Field	Value
Statement	For policy periods with status `RF`, the package suppresses `risk_type_code` and plan description values until `SP72347_INFO_PAGE_PRINTED_YN(policy_num, policy_period_eff_date)` returns `Y`.
Trigger	`getpolicyperiod` or `getpolicyperiods` retrieves an `RF` policy period.
Enforcement	Both procedures use `CASE` expressions that return empty strings for `RF` rows when `SP72347_INFO_PAGE_PRINTED_YN` is not `Y`.
Outcome	Assigned-risk policy-period detail stays masked until the information page is printed.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-020 Claim-address requests may fall back to the policy address¶

Field	Value
Statement	If a claim-address request finds no active claim location and `ingetmailaddrwhennotfound = 'Y'`, the procedure changes the requested location code to `POL` before opening the cursor.
Trigger	`getpolicyaddress` executes for address type `CLM` and no current `CLM` location exists.
Enforcement	The procedure counts qualifying `CLM` locations, then assigns `v_addr_type := 'POL'` when fallback is enabled and the count is zero.
Outcome	Callers receive the policy mailing or location address instead of an empty claim-address result.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-021 Address retrieval prefers current contacts and the latest effective location row¶

Field	Value
Statement	Policy-address lookups join only active contacts, constrain location rows to the latest qualifying effective date, and return a single top-ranked row; location-sequence lookups also rank contact types before applying `ROWNUM <= 1`.
Trigger	`getpolicyaddress` or `getpolicyaddressbylocseqnum` executes.
Enforcement	`getpolicyaddress` filters `contact_exp_date IS NULL`, constrains the effective date with a `MAX(loc_eff_date)` subquery, and limits the result with `ROWNUM <= 1`; `getpolicyaddressbylocseqnum` orders contact types and also applies `ROWNUM <= 1`.
Outcome	The package returns one current address and contact record rather than multiple stale rows.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-022 Affiliate-type classification follows a fixed hierarchy¶

Field	Value
Statement	Affiliate-type resolution checks for manual policyholder affiliations first, then wrap and leasing relationships, then subaccount and state-agency relationships, then experience-combination memberships, and finally premium-discount combinations.
Trigger	`getpolicyaffiliatetype` executes.
Enforcement	The function evaluates each affiliation family in sequence and returns immediately on the first matching `SELECT ... INTO`.
Outcome	A policy receives the first matching affiliate classification in the packages explicit precedence order.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-023 Parent-policy resolution depends on the resolved affiliate type¶

Field	Value
Statement	Parent-policy resolution chooses the parent lookup strategy from the affiliate type returned by `getpolicyaffiliatetype`, including manual affiliation, relationship, combination, premium-discount, or self-parent fallback paths.
Trigger	`getparentpolicy` executes.
Enforcement	The function branches on `v_affil_type` and queries `policy_relationship`, `policy_relationship_policy_num`, `rate_account`, `combo_policy_period`, or the input policy number.
Outcome	Callers receive the appropriate parent policy number for the resolved affiliation model.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-024 Affiliate total rows are optional and vary by affiliation family¶

Field	Value
Statement	Affiliate lists add a single totals row only when `in_include_affil_totals = 'TRUE'`, and the totals label and selection logic vary by affiliate family.
Trigger	`getpolicyaffiliatelist` executes.
Enforcement	The procedure builds `v_totals_description` and `v_affil_type`, then includes totals-row `UNION` branches only when `UPPER(in_include_affil_totals) = 'TRUE'`.
Outcome	Callers can opt into or suppress the family-specific totals row for affiliate displays.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

BR-VP-025 Wrap-up legal-name lookup climbs from member to relationship policy to rate account¶

Field	Value
Statement	When a wrap-up member references a different relationship policy, `getwrapuplegalname` first loads that policy legal name and then, if a rate account exists, replaces it with the rate-account policy legal name.
Trigger	`getwrapuplegalname` executes for a wrap-up member policy.
Enforcement	The function selects `policy_relationship_policy_num` and `legal_name`, then conditionally selects the relationship policy and finally the `rate_account` policy.
Outcome	The returned legal name reflects the top-level wrap-up or rate-account owner when available.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

Operations¶

The retrieved database source implements the following internal operations.

Operation	Description	Trigger
Retrieve agency of record contact	Return the agency, producer, phone, address, and vendor data for the current agent of record.	Caller invokes `getagency`.
Detect policy coverage gaps	Return gap rows sourced from adjacent policy periods and explicit `POLICY_COVERAGE_GAP` records.	Caller invokes `getgaps`.
Retrieve view-policy summary detail	Return the primary view-policy summary row including entity, NAICS, premium, gap, group, reimbursement, and sales-plan data.	Caller invokes `getviewdetail`.
Build ordered team-assignment rows	Return policy-team members plus synthesized office, customer-service-team, agency, and producer rows sorted by display order.	Caller invokes `team_assign_query`.
Retrieve policy-period history with masking rules	Return policy-period rows while applying assigned-risk masking and current-period selection rules.	Caller invokes `getpolicyperiod` or `getpolicyperiods`.
Resolve policy address rows	Return the preferred policy or claim address and contact row by address type or location sequence.	Caller invokes `getpolicyaddress` or `getpolicyaddressbylocseqnum`.
Classify and list affiliated policies	Classify a policy affiliation family, then return member and optional totals rows for that family.	Caller invokes `getpolicyaffiliatetype` or `getpolicyaffiliatelist`.
Resolve parent policy number	Return the parent policy number that corresponds to the resolved affiliate type.	Caller invokes `getparentpolicy`.
Resolve wrap-up legal name	Return the wrap-up owner legal name, escalating to the relationship policy and rate account when necessary.	Caller invokes `getwrapuplegalname`.

Retrieve agency of record contact¶

Field	Value
Description	Return the agency, producer, phone, address, and vendor data for the current agent of record.
Trigger	Caller invokes `getagency`.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

Detect policy coverage gaps¶

Field	Value
Description	Return gap rows sourced from adjacent policy periods and explicit `POLICY_COVERAGE_GAP` records.
Trigger	Caller invokes `getgaps`.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

Retrieve view-policy summary detail¶

Field	Value
Description	Return the primary view-policy summary row including entity, NAICS, premium, gap, group, reimbursement, and sales-plan data.
Trigger	Caller invokes `getviewdetail`.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

Build ordered team-assignment rows¶

Field	Value
Description	Return policy-team members plus synthesized office, customer-service-team, agency, and producer rows sorted by display order.
Trigger	Caller invokes `team_assign_query`.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

Retrieve policy-period history with masking rules¶

Field	Value
Description	Return policy-period rows while applying assigned-risk masking and current-period selection rules.
Trigger	Caller invokes `getpolicyperiod` or `getpolicyperiods`.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

Resolve policy address rows¶

Field	Value
Description	Return the preferred policy or claim address and contact row by address type or location sequence.
Trigger	Caller invokes `getpolicyaddress` or `getpolicyaddressbylocseqnum`.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

Classify and list affiliated policies¶

Field	Value
Description	Classify a policy affiliation family, then return member and optional totals rows for that family.
Trigger	Caller invokes `getpolicyaffiliatetype` or `getpolicyaffiliatelist`.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

Resolve parent policy number¶

Field	Value
Description	Return the parent policy number that corresponds to the resolved affiliate type.
Trigger	Caller invokes `getparentpolicy`.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

Resolve wrap-up legal name¶

Field	Value
Description	Return the wrap-up owner legal name, escalating to the relationship policy and rate account when necessary.
Trigger	Caller invokes `getwrapuplegalname`.
Source	Oracle retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json`

Calling code references¶

Class	Method	Source File
`clsAgency`	`Criteria.RetrieveSQL (`getAgency`)`	`SAIF.Business.View.Policy\clsAgency.vb`
`clsMCOCollection`	`Criteria.RetrieveSQL (`getmco`)`	`SAIF.Business.View.Policy\clsMCOCollection.vb`
`clsPolicyMCOCollection`	`Criteria.RetrieveSQL (`getmco`)`	`SAIF.Business.View.Policy\clsPolicyMCOCollection.vb`
`clsAffiliatedPolicyCollection`	`Criteria.RetrieveSQL (`getpolicyaffiliatelist`)`	`SAIF.Business.View.Policy\clsAffiliatedPolicyCollection.vb`
`clsPolicyAddress`	`Criteria.RetrieveSQL (`getPolicyAddress`)`	`SAIF.Business.View.Policy\clsPolicyAddress.vb`
`clsPolicyAddress`	`ad hoc method (`getPolicyAddressByLocSeqNum`)`	`SAIF.Business.View.Policy\clsPolicyAddress.vb`
`clsPolicyData`	`ad hoc method (`getgaps`)`	`SAIF.Business.View.Policy\clsPolicyData.vb`
`clsPolicyData`	`ad hoc method (`getviewdetail`)`	`SAIF.Business.View.Policy\clsPolicyData.vb`
`clsPolicyData`	`obsolete ad hoc method (`getParentPolicy`)`	`SAIF.Business.View.Policy\clsPolicyData.vb`
`clsPolicyData`	`obsolete ad hoc method (`getpolicyaffiliatetype`)`	`SAIF.Business.View.Policy\clsPolicyData.vb`
`clsPolicyData`	`ad hoc method (`getwrapuplegalname`)`	`SAIF.Business.View.Policy\clsPolicyData.vb`
`clsPolicyPeriodCollection`	`Criteria.RetrieveSQL (`getPolicyPeriods`)`	`SAIF.Business.View.Policy\clsPolicyPeriodCollection.vb`
`clsTeamAssignmentCollection`	`Criteria.RetrieveSQL (`team_assign_query`)`	`SAIF.Business.View.Policy\clsTeamAssignmentCollection.vb`

Source Code¶

Representative package body excerpt for agency and gap retrieval

   PROCEDURE getagency (apolicynum IN NUMBER, recordset IN OUT agency_cursor)
   AS
   BEGIN

      OPEN recordset FOR
         SELECT agency.agency_seq_num AS agency_num,
                agency.agency_name AS agency_name,
                agency.policy_holder_paperless_flag,
                AGENT.agent_num AS producer_num,
                AGENT.agent_name AS producer_name,
                l1.loc_phone AS producer_phone,
                l1.loc_addr1,
                l1.loc_addr2,
                l1.loc_city,
                l1.loc_country,
                l1.loc_county,
                case when agency.statement_addr_loc_seq_num is null then l2.loc_phone else la.loc_phone end loc_phone,
                case when agency.statement_addr_loc_seq_num is null then l2.loc_phone_ext else la.loc_phone_ext end loc_phone_ext,
                l1.loc_fax,
                l1.loc_state,
                l1.loc_zip,
                l1.loc_zip4,
                agency.vendor_num,
                l1.email_addr
           FROM AGENT
                join agency on agency.agency_seq_num = agent.agency_seq_num
                left outer join LOCATION_table l1 on AGENT.mail_addr_loc_seq_num = l1.loc_seq_num
                left outer join AGENT a2 on a2.agent_num = AGENT.agency_seq_num
                left outer join LOCATION_table l2 on a2.mail_addr_loc_seq_num = l2.loc_seq_num
                left outer join LOCATION_table la on agency.statement_addr_loc_seq_num = la.loc_seq_num
          WHERE AGENT.agent_num = pk72838_comm_authorization.sp72850_get_agent_of_rec(apolicynum)
            AND agent.agent_num <> 0;
   END getagency;

   PROCEDURE getgaps (apolicynum IN NUMBER, recordset IN OUT gap_cursor)
   AS
   BEGIN
      OPEN recordset FOR
         SELECT   'GAP' AS rec_type,
                  TO_CHAR (NVL (p1.cancel_date, p1.policy_period_exp_date),
                           'MM/dd/yyyy'
                          ) cancel_date,
                  TO_CHAR (p2.policy_period_eff_date,
                           'MM/dd/yyyy'
                          ) policy_period_eff_date,
                  TO_CHAR (p2.policy_period_eff_date, 'yyyyMMdd') sort_date
             FROM policy_period_all p1, policy_period_all p2
            WHERE p1.policy_num = apolicynum
              AND NVL (p1.cancel_date, p1.policy_period_exp_date) <>
                                                     p2.policy_period_eff_date
              -- exclude flat cancel period and pms prospect periods
              AND p1.policy_period_status_code <> 'FC'
              AND p1.policy_period_status_code <> 'PPROS'
              AND p1.policy_period_eff_date <>
                     (SELECT MAX (policy_period_eff_date)
                        FROM policy_period_all
                       WHERE policy_num = p1.policy_num
                         AND policy_period_status_code <> 'FC'
                         AND policy_period_status_code <> 'PPROS')
              AND p1.policy_num = p2.policy_num
              AND p2.policy_period_eff_date =
                     (SELECT MIN (policy_period_eff_date)
                        FROM policy_period_all
                       WHERE policy_num = p1.policy_num
                         AND policy_period_eff_date >
                                                     p1.policy_period_eff_date
                         AND policy_period_status_code <> 'FC'
                         AND policy_period_status_code <> 'PPROS')
         UNION
         SELECT   'GAP', TO_CHAR (gap_eff_date, 'MM/dd/yyyy'),
                  TO_CHAR (gap_exp_date, 'MM/dd/yyyy'),
                  TO_CHAR (gap_eff_date, 'yyyyMMdd') sort_date
             FROM policy_coverage_gap
            WHERE policy_num = apolicynum
         ORDER BY 4 DESC;
   END getgaps;

Representative package body excerpt for view detail and team assignments

   PROCEDURE getviewdetail (
      apolicynum   IN       NUMBER,
      recordset    IN OUT   viewpolicydetail_cursor
   )
   AS
   BEGIN
      OPEN recordset FOR
         SELECT p.policy_num, e.code_descr,
         --obsolete
          s.code_descr as sic_descr,
         --10/29/2012 jertod ADDED
         n.code_descr as naics_descr,

                CASE source_system
                   WHEN 'WCIS'
                      THEN sp72658_get_gov_class
                                    (p.policy_num,
                                     ppa.policy_period_eff_date
                                    )
                   WHEN 'PMS'
                      THEN sp73029_get_nonwcis_gov_class
                                                   (p.policy_num,
                                                    ppa.policy_period_eff_date
                                                   )
                   ELSE 'NONE'
                END payroll_class,
                NVL (p.current_balance, 0) current_balance, p.wcd_num,
                --obsolete
                p.sic_code as sic_code,
                --10/29/2012 jertod ADDED
                p.naics_code, p.ncci_risk_id, p.interstate_risk_id_flag,
                b.feid_num, NVL (p.premium_estimate, 0) AS premium_estimate,
                TO_CHAR (p.policy_orig_eff_date,
                         'MM/dd/yyyy'
                        ) AS policy_orig_eff_date,
                TO_CHAR
                   (sp72814_get_cont_cov_date (p.policy_num),
                    'MM/dd/yyyy'
                   ) AS policy_cont_cov_date,
                DECODE (getgapindicator (p.policy_num),
                        0, '',
                        '- GAP'
                       ) AS gap_in_cov,
                ga.code_descr AS group_name, gt.code_descr AS group_type,
                NVL (g.chapter_code, ' ') AS group_chapter,
                ppa.five_reimb_flag, ppa.five_eff_date, ppa.five_exp_date,
                ppa.policy_period_exp_date
--################################################
--, ppa.PSC
--06/09/2006 JERTOD added to mask data from RF status policies
                ,
                CASE ppa.psc
                   WHEN 'RF'
                      THEN ''
                   ELSE CASE pc.code
                   WHEN 'RETRO'
                      THEN (SELECT    rptc.code_descr_short
                                   || ' Retro'
                              FROM retro_plan_policy_period rppp JOIN retro_plan_period rpp
                                   ON (rppp.retro_plan_period_seq_num =
                                                 rpp.retro_plan_period_seq_num
                                      )
                                   JOIN retro_rating_plan rrp
                                   ON (rpp.retro_rating_plan_seq_num =
                                                 rrp.retro_rating_plan_seq_num
                                      )
                                   JOIN retro_payment_type_code rptc
                                   ON (rrp.retro_payment_type_code = rptc.code
                                      )
                             WHERE rppp.policy_num = p.policy_num
                               AND rppp.policy_period_eff_date =
                                                    ppa.policy_period_eff_date)
                   ELSE pc.code_descr
                END
                END sales_plan_name
                ,
                NVL
                   ((SELECT DISTINCT ('Y')
                                FROM POLICY p2 JOIN er_combination_policy ecp
                                     ON (p2.policy_num = ecp.policy_num)
                               WHERE ecp.er_combination_seq_num IN (
                                          SELECT ecp2.er_combination_seq_num
                                            FROM er_combination_policy ecp2
                                           WHERE ecp2.policy_num =
                                                                  p.policy_num)
                                 AND p2.policy_relationship_code = 'WRAP'),
                    'N'
                   ) combo_wrap_sub_flag,
                p.vendor_num,
                b.web_addr
           FROM POLICY p,
                entity_code e,
                business b,
                policy_period_group_assoc g,
                group_association ga,
                group_type_code gt,
                --obsolete
                sic_code s,
                naics_code n,
                plan_code pc,
                (SELECT ppm.policy_num
                        , pp.policy_period_status_code psc
                        ,
                        ppm.policy_period_eff_date,
                        TO_CHAR
                           (NVL
                           (pp.policy_period_exp_date,
                            ppnw.policy_period_exp_date
                           ),
                            'MM/dd/yyyy'
                           ) policy_period_exp_date,
                        ppm.cancel_date,   -->3/7/2012 added a table clarifier
                        ppm.policy_sfx,
                        NVL (pp.plan_code, ppnw.plan_code) plan_code,
                        DECODE
                           (NVL (pp.claims_reimbursement_flag,
                                 ppnw.claims_reimb_flag
                                ),
                            'Y', 1,
                            0
                           ) AS five_reimb_flag,
                        TO_CHAR
                           (NVL (pp.claims_reimb_eff_date,
                                 ppnw.claims_reimb_eff_date
                                ),
                            'MM/dd/yyyy'
                           ) AS five_eff_date,
                        TO_CHAR
                           (NVL (pp.claims_reimb_exp_date,
                                 ppnw.claims_reimb_exp_date
                                ),
                            'MM/dd/yyyy'
                           ) AS five_exp_date,
                        ppm.source_system
                   FROM policy_period_master ppm LEFT OUTER JOIN policy_period pp
                        ON (    ppm.policy_num = pp.policy_num
                            AND ppm.policy_period_eff_date =
                                                     pp.policy_period_eff_date
                           )
                        LEFT OUTER JOIN policy_period_non_wcis ppnw
                        ON (    ppm.policy_num = ppnw.policy_num
                            AND ppm.policy_period_eff_date =
                                                   ppnw.policy_period_eff_date
                           )
                  WHERE ppm.policy_num = apolicynum
                    AND ppm.policy_period_eff_date =

                    (

SELECT MIN( pdate) FROM
(SELECT MAX (policy_period_eff_date) pdate
FROM policy_period_master
WHERE policy_num = apolicynum
AND policy_period_eff_date <= TRUNC (SYSDATE)
UNION
SELECT MAX (policy_period_eff_date) pdate
FROM policy_period_master
WHERE policy_num = apolicynum )   )



                                   ) ppa
          WHERE p.policy_num = apolicynum
            AND p.policy_num = b.policy_num(+) and nvl(b.primary_bus_flag, 'Y') = 'Y'
            AND b.entity_code = e.code(+)
            AND p.policy_num = ppa.policy_num
            AND ppa.policy_num = g.policy_num(+)
            AND ppa.policy_period_eff_date = g.policy_period_eff_date(+)
/* JERTOD CHANGED TO INLCUDE FUTURE TERMINATING GROPS
            AND g.group_mbrshp_exp_date(+) IS NULL*/
            AND SYSDATE < NVL (g.group_mbrshp_exp_date(+), SYSDATE + 1)
            AND g.group_association_code = ga.code(+)
            AND ga.group_type_code = gt.code(+)
            AND p.sic_code = s.code(+)
            --obsolete
            --10/29/2012 JERTOD added
            AND p.naics_code=n.code(+)
            AND ppa.plan_code = pc.code(+);
   END getviewdetail;

   PROCEDURE team_assign_query (
      apolicynum   IN       NUMBER,
      recordset    IN OUT   team_assign_cursor
   )
   AS
   BEGIN
      OPEN recordset FOR
         SELECT   CASE
                     WHEN user_alias.user_name IS NOT NULL
                        THEN NVL
                               ((SELECT employee_status_code
                                   FROM wcis_employee
                                  WHERE user_name = user_alias.user_name),
                                w.employee_status_code
                               )
                     ELSE w.employee_status_code
                  END employee_status_code,
                  p.policy_num,
                  NVL (user_alias.user_name, p.user_name) user_name,
                     w.employee_first_name
                  || DECODE (w.employee_initial,
                             '', ' ',
                             ' ' || w.employee_initial || ' '
                            )
                  || w.employee_last_name AS full_name,
                  w.phone_num, w.phone_ext, t.code, t.code_descr,
                  t.screen_order + 10 AS screen_order
             FROM policy_team_function p LEFT OUTER JOIN wcis_employee w
                  ON (trim(p.user_name) = trim(w.user_name))
                  LEFT OUTER JOIN team_function_code t
                  ON (p.team_function_code = t.code)
                  LEFT OUTER JOIN user_alias
                  ON (    RTRIM (p.user_name) =
                                            RTRIM (user_alias.user_alias_name)
                      AND RTRIM (p.team_function_code) =
                                                 RTRIM (user_alias.alias_code)
                     )
            WHERE p.policy_num = apolicynum AND screen_order > 0
         -- get the office
         UNION
         SELECT   'OTHER', l.policy_num, l.office_code, o.code_descr, ' ',
                  ' ', 'Office', 'Office', 1
             FROM POLICY l, office_code o
            WHERE l.policy_num = apolicynum AND l.office_code = o.code
         UNION
         -- get the customer service team
        SELECT   'OTHER', p.policy_num, null, c.code_descr, null,null,
                  'CST', 'Customer Service Team', 2
             FROM customer_service_team_code c, POLICY p
            WHERE p.customer_service_team_code = c.code
              AND p.policy_num =  apolicynum
         UNION
         -- get the agency name and phone #
         SELECT   'OTHER', apolicynum, TO_CHAR (agency.agency_seq_num),
                  agency.agency_name AS full_name,
                  case when agency.statement_addr_loc_seq_num is null then l2.loc_phone else la.loc_phone end phone_num,
                  case when agency.statement_addr_loc_seq_num is null then l2.loc_phone_ext else la.loc_phone_ext end phone_ext,
                  'Agency' code,
                  'Agency' code_descr, 3
             FROM AGENT
                  join agency on agency.agency_seq_num = agent.agency_seq_num
                  left outer join location_table la on agency.statement_addr_loc_seq_num = la.loc_seq_num
                  left outer join AGENT a2 on agent.agency_seq_num = a2.agent_num
                  left outer join LOCATION_table l2 on a2.mail_addr_loc_seq_num = l2.loc_seq_num
            WHERE AGENT.agent_num = pk72838_comm_authorization.sp72850_get_agent_of_rec(apolicynum)
              AND agent.agent_num <> 0
         UNION
         -- get the producer name and phone #
         -- this sql is dependent on wcis release 7.1.1
         SELECT   'OTHER', apolicynum, TO_CHAR (AGENT.agent_num),
                  AGENT.agent_name AS full_name, l1.loc_phone phone_num,
                  l1.loc_phone_ext phone_ext, 'Producer' code,
                  'Producer' code_descr, 4
             FROM AGENT
                  left outer join LOCATION_table l1 on AGENT.mail_addr_loc_seq_num = l1.loc_seq_num
            WHERE AGENT.agent_num = pk72838_comm_authorization.sp72850_get_agent_of_rec(apolicynum)
              AND agent.agent_num <> 0
         ORDER BY 9;
   END team_assign_query;

Representative package body excerpt for policy periods and addresses

   PROCEDURE getpolicyperiods (
      apolicynum    IN       NUMBER,
      atopnpolper   IN       NUMBER,
      recordset     IN OUT   ref_cursor
   )
   AS
      vretrieveall   NUMBER := 0;
   BEGIN
      IF atopnpolper > 0
      THEN
         vretrieveall := atopnpolper;
      END IF;

      OPEN recordset FOR
         SELECT *
           FROM (SELECT   p.policy_num,
                          TO_CHAR (p.policy_period_eff_date,
                                   'MM/dd/yyyy'
                                  ) AS pp_eff_date,
                          TO_CHAR (p.policy_period_exp_date,
                                   'MM/dd/yyyy'
                                  ) AS pp_exp_date,
                          TO_CHAR
                             (NVL (p.cancel_date,
                                   (SELECT cancel_date
                                      FROM policy_period_all
                                     WHERE policy_num = p.policy_num
                                       AND policy_period_eff_date =
                                                      p.policy_period_exp_date
                                       AND cancel_date =
                                                        policy_period_eff_date)
                                  ),
                              'MM/dd/yyyy'
                             ) AS cancel_date,
                         'WCIS' AS source_system, 'WCIS' AS source_system_code, p.financial_system_code,
--###########################################
--added 6/9/2006 by JERTOD TO MASK FOR RF POLICIES
--11/7/11 valrey updated masking logic to add check if info page printed                          ,
                        CASE p.policy_period_status_code
                           WHEN 'RF' then
                                case SP72347_INFO_PAGE_PRINTED_YN(p.policy_num,p.policy_period_eff_date)
                                    when 'Y'
                                        then p.risk_type_code
                                    else
                                        ''
                                end
                           ELSE p.risk_type_code
                        END AS risk_type_code
   --, p.risk_type_code  <--   ELIMINATED
--#################################################
                          ,
                          p.payroll_report_frequency_code,
                          TO_CHAR
                             (p.anniversary_rate_date,
                              'MM/dd/yyyy'
                             ) AS anniversary_rate_date
                                                       --########### JERTOD ADDED 6/9/2006 TO MASK FOR RF POLICIES
                          ,
                          TO_CHAR
                             (p.rating_effective_date,
                              'MM/dd/yyyy'
                             ) AS rating_effective_date
                          ,
                        --valrey 11/7/11 replaced masking for RF to check if the infopage has printed
                          CASE p.policy_period_status_code
                           WHEN 'RF' then
                              case SP72347_INFO_PAGE_PRINTED_YN(p.policy_num,p.policy_period_eff_date)
                                    when 'Y'
                                        then pc.code_descr
                                    else
                                        ''
                              end
                           ELSE CASE pc.code
                                   WHEN 'RETRO'
                                      THEN (SELECT rptc.code_descr_short
                                                   || ' Retro'
                                              FROM retro_plan_policy_period rppp JOIN retro_plan_period rpp
                                                   ON (rppp.retro_plan_period_seq_num =
                                                                 rpp.retro_plan_period_seq_num
                                                      )
                                                   JOIN retro_rating_plan rrp
                                                   ON (rpp.retro_rating_plan_seq_num =
                                                                 rrp.retro_rating_plan_seq_num
                                                      )
                                                   JOIN retro_payment_type_code rptc
                                                   ON (rrp.retro_payment_type_code = rptc.code
                                                      )
                                             WHERE rppp.policy_num = p.policy_num
                                               AND rppp.policy_period_eff_date =
                                                                      p.policy_period_eff_date)
                                    ELSE
                                        pc.code_descr
                                  END
                          END plan_code,

--############################################################
                          p.policy_period_eff_date, r.rating_plan_factor,
                             r.rating_plan_code
                          || DECODE (tentative_rating_flag, 'Y', '*', '')

   PROCEDURE getpolicyaddress (
      inpolicynum                 IN       NUMBER,
      inaddresstype               IN       VARCHAR2,
      incontacttype               IN       VARCHAR2,
      ingetmailaddrwhennotfound   IN       VARCHAR2,
      recordset                   IN OUT   ref_cursor
   )
   AS
      v_addr_cnt    NUMBER       := 0;
      v_addr_type   VARCHAR2 (5);
   BEGIN
      v_addr_type := inaddresstype;

      BEGIN
         IF inaddresstype = 'CLM'
         THEN
            SELECT COUNT (*)
              INTO v_addr_cnt
              FROM LOCATION l
             WHERE l.policy_num = inpolicynum
               AND l.location_code = v_addr_type
               AND l.loc_exp_date IS NULL
               AND l.loc_eff_date =
                      (SELECT MAX (l2.loc_eff_date)
                         FROM LOCATION l2
                        WHERE l2.policy_num = inpolicynum
                          AND l2.location_code = v_addr_type
                          AND l2.loc_exp_date IS NULL);
         END IF;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            v_addr_cnt := 0;
      END;

      IF ingetmailaddrwhennotfound = 'Y'
      THEN
         IF inaddresstype = 'CLM' AND v_addr_cnt = 0
         THEN
            v_addr_type := 'POL';
         END IF;
      END IF;

      BEGIN
         OPEN recordset FOR
            SELECT   pol.legal_name AS company_name,
                     DECODE (RTRIM (c.contact_first_name),
                             NULL, 'Policyholder',
                                RTRIM (c.contact_first_name)
                             || ' '
                             || DECODE (c.contact_initial,
                                        '', NULL,
                                        ' ', NULL,
                                        c.contact_initial || ' '
                                       )
                             || RTRIM (c.contact_last_name)
                            ) contact_name,
                     location_code, l.location_name, loc_eff_date,
                     loc_exp_date, loc_addr1 address_line1,
                     loc_addr2 address_line2, loc_addr3 address_line3,
                     DECODE (loc_city,
                             NULL, NULL,
                                loc_city
                             || ', '
                             || loc_state
                             || ' '
                             || loc_zip
                             || DECODE (loc_zip4,
                                        NULL, NULL,
                                        '-' || loc_zip4
                                       )
                            ) address_line4,
                     l.loc_seq_num, l.loc_city, l.loc_country, l.loc_state,
                     l.loc_county, l.loc_zip, l.loc_zip4, l.loc_phone,
                     l.loc_phone_ext, l.loc_fax, l.primary_location_flag,
                     c.contact_type_code, c.contact_first_name,
                     c.contact_initial, c.contact_last_name, c.contact_title,
                     c.email_addr contact_email_addr, c.contact_phone,
                     c.contact_phone_ext, l.email_addr loc_email_addr
                FROM LOCATION l, contact c, POLICY pol
               WHERE pol.policy_num = inpolicynum
                 AND pol.policy_num = l.policy_num
                 AND l.location_code = v_addr_type
                 AND l.loc_seq_num = c.loc_seq_num(+)
                 AND c.contact_type_code(+) = incontacttype
                 AND c.contact_exp_date(+) IS NULL
                 AND (v_addr_type = 'CLM' OR l.loc_eff_date =
                        (SELECT MAX (l2.loc_eff_date)
                           FROM LOCATION l2
                          WHERE l2.policy_num = inpolicynum
                            AND l2.location_code = v_addr_type
                            AND NVL(l2.loc_exp_date, SYSDATE) <> l2.loc_eff_date))
            AND NVL(l.LOC_EXP_DATE,SYSDATE + 1) > SYSDATE
            AND ROWNUM <=1
            ORDER BY loc_eff_date DESC;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            NULL;
      END;
   END getpolicyaddress;


   PROCEDURE getpolicyaddressbylocseqnum (
      inlocationseqnum            IN       NUMBER,
      recordset                   IN OUT   ref_cursor
   )
   AS
   BEGIN

         OPEN recordset FOR
            SELECT *
            FROM (
                SELECT
                    pol.legal_name AS company_name,
                     DECODE (RTRIM (c.contact_first_name),
                             NULL, 'Policyholder',
                                RTRIM (c.contact_first_name)
                             || ' '
                             || DECODE (c.contact_initial,
                                        '', NULL,
                                        ' ', NULL,
                                        c.contact_initial || ' '
                                       )
                             || RTRIM (c.contact_last_name)
                            ) contact_name,
                     location_code, l.location_name, loc_eff_date,
                     loc_exp_date, loc_addr1 address_line1,
                     loc_addr2 address_line2, loc_addr3 address_line3,
                     DECODE (loc_city,
                             NULL, NULL,
                                loc_city
                             || ', '
                             || loc_state
                             || ' '
                             || loc_zip
                             || DECODE (loc_zip4,
                                        NULL, NULL,
                                        '-' || loc_zip4
                                       )
                            ) address_line4,
                     l.loc_seq_num, l.loc_city, l.loc_country, l.loc_state,
                     l.loc_county, l.loc_zip, l.loc_zip4, l.loc_phone,
                     l.loc_phone_ext, l.loc_fax, l.primary_location_flag,
                     c.contact_type_code, c.contact_first_name,
                     c.contact_initial, c.contact_last_name, c.contact_title,
                     c.email_addr contact_email_addr, c.contact_phone,
                     c.contact_phone_ext, l.email_addr loc_email_addr
                FROM LOCATION l, contact c, POLICY pol
               WHERE l.loc_seq_num = inLocationSeqNum
                 AND pol.policy_num = l.policy_num
                 AND l.loc_seq_num = c.loc_seq_num(+)
                 AND c.contact_exp_date(+) IS NULL
               ORDER BY CASE c.contact_type_code
                        WHEN 'AUD' THEN 1
                        WHEN 'PRL' THEN 2
                        WHEN 'BKP' THEN 3
                        WHEN 'CPA' THEN 4
                        WHEN 'ACT' THEN 5
                        WHEN 'CTR' THEN 6
                        WHEN 'TAX' THEN 7
                        ELSE 8
                        END
                      , c.contact_eff_date DESC)
            WHERE ROWNUM <=1;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            NULL;
   END getpolicyaddressbylocseqnum;

   PROCEDURE getpolicyteamuser (
      in_policy_num           IN       NUMBER,
      in_team_function_code   IN       VARCHAR2,
      recordset               OUT      ref_cursor
   )
   IS
   BEGIN
      CASE in_team_function_code
         WHEN 'AGT'
         THEN
            OPEN recordset FOR
               SELECT 'Agency' AS mailid, agency.agency_name AS full_name,
                      case when agency.statement_addr_loc_seq_num is null then l2.loc_phone || NVL2 (l2.loc_phone_ext, 'x' || l2.loc_phone_ext, '')
                                                                          else la.loc_phone || NVL2 (la.loc_phone_ext, 'x' || la.loc_phone_ext, '') end
                                                              AS phone_num
                 FROM AGENT
                      join agency on agency.agency_seq_num = agent.agency_seq_num
                      left outer join LOCATION_table la on agency.statement_addr_loc_seq_num = la.loc_seq_num
                      left outer join AGENT a2 on a2.agent_num = AGENT.agency_seq_num
                      left outer join location_table l2 on a2.mail_addr_loc_seq_num = l2.loc_seq_num
                WHERE AGENT.agent_num = pk72838_comm_authorization.sp72850_get_agent_of_rec(in_policy_num)
                  AND agent.agent_num <> 0;
         WHEN 'PRD'

Representative package body excerpt for affiliation helpers

   FUNCTION getpolicyaffiliatetype (
      in_policy_num             IN   NUMBER,
      in_is_policyholder_user   IN   VARCHAR2
   )
      RETURN VARCHAR2
   IS
      v_affiliate_type   VARCHAR2 (20) := 'NONAFFILIATED';
   BEGIN
      -- First check for a manual parent affiliation
      -- only when is_policyholder_user = TRUE
      IF UPPER (in_is_policyholder_user) = 'TRUE'
      THEN
         BEGIN
            SELECT 'AFFILIATIONPARENT'
              INTO v_affiliate_type
              FROM policy_relationship
             WHERE UPPER (affil_typ_cd) = 'PHP'
               AND parent_policy_num = in_policy_num
               AND ROWNUM = 1;

            RETURN v_affiliate_type;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               NULL;
         END;

         -- Next check for a manual member affiliation
         -- only when is_policyholder_user = TRUE
         BEGIN
            SELECT 'AFFILIATIONMEMBER'
              INTO v_affiliate_type
              FROM policy_relationship
             WHERE UPPER (affil_typ_cd) = 'PHP'
               AND mbr_policy_num = in_policy_num;

            RETURN v_affiliate_type;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               NULL;
         END;
      END IF;



      --  Policy Relationship
      -- Next check for a wrapup or leasing parent affiliation
      BEGIN
         SELECT (CASE
                    WHEN policy_relationship_code = 'WRAP'
                       THEN 'WRAPUPPARENT'
                    WHEN policy_relationship_code = 'LEASE'
                       THEN 'LEASINGPARENT'
                 END
                )
           INTO v_affiliate_type
           FROM POLICY
          WHERE policy_relationship_code IN ('WRAP', 'LEASE')
            AND policy_num = in_policy_num
            AND policy_relationship_policy_num = in_policy_num
            AND ROWNUM = 1;

         RETURN v_affiliate_type;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            NULL;
      END;

      -- Next check for a wrapup or leasing member affiliation
      BEGIN
         SELECT (CASE
                    WHEN policy_relationship_code = 'WRAP'
                       THEN 'WRAPUPMEMBER'
                    WHEN policy_relationship_code = 'LEASE'
                       THEN 'LEASINGMEMBER'
                 END
                )
           INTO v_affiliate_type
           FROM POLICY
          WHERE policy_relationship_code IN ('WRAP', 'LEASE')
            AND policy_num = in_policy_num
            AND policy_relationship_policy_num <> in_policy_num;

         RETURN v_affiliate_type;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            NULL;
      END;



      --  Policy Relationship
      -- Next check for a subaccount or state agency affiliation
      BEGIN
         SELECT (CASE
                    WHEN policy_relationship_code = 'SUBAC'
                       THEN 'SUBACPARENT'
                    WHEN policy_relationship_code = 'OREGN'
                       THEN 'OREGNPARENT'
                 END
                )
           INTO v_affiliate_type
           FROM POLICY
          WHERE policy_relationship_code IN ('SUBAC', 'OREGN')
            AND policy_num = in_policy_num
            AND policy_relationship_policy_num = in_policy_num
            AND ROWNUM = 1;

         RETURN v_affiliate_type;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            NULL;
      END;

      -- Next check for a wrapup or leasing member affiliation
      BEGIN
         SELECT (CASE
                    WHEN policy_relationship_code = 'SUBAC'
                       THEN 'SUBACMEMBER'
                    WHEN policy_relationship_code = 'OREGN'
                       THEN 'OREGNMEMBER'
                 END
                )
           INTO v_affiliate_type
           FROM POLICY
          WHERE policy_relationship_code IN ('SUBAC', 'OREGN')
            AND policy_num = in_policy_num
            AND policy_relationship_policy_num <> in_policy_num;

         RETURN v_affiliate_type;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            NULL;
      END;



      -- Experience Combination
      -- Existing, but moved up in heirarchy
      -- Next check for a Combination parent affiliation
      BEGIN
         SELECT 'COMBOPARENT'
           INTO v_affiliate_type
           FROM POLICY
          WHERE rate_account = in_policy_num AND ROWNUM = 1;

         RETURN v_affiliate_type;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            NULL;
      END;

      -- Next check for a Combination member affiliation
      BEGIN
         SELECT 'COMBOMEMBER'
           INTO v_affiliate_type
           FROM POLICY
          WHERE policy_num = in_policy_num
            AND rate_account IS NOT NULL
            AND rate_account <> in_policy_num;

         RETURN v_affiliate_type;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            NULL;
      END;



      -- VALREY added
      -- Premium Discount Combination
      -- Next check for a Premium discount combination parent affiliation
      BEGIN
         SELECT 'PREMIUMPARENT'
           INTO v_affiliate_type
           FROM POLICY p, combo_policy_period cpp
          WHERE p.policy_num = in_policy_num
            AND p.policy_num = cpp.policy_num
            AND cpp.policy_period_eff_date =
                                       (SELECT MAX (policy_period_eff_date)
                                          FROM policy_period pp
                                         WHERE pp.policy_num = cpp.policy_num)
            AND cpp.active_member_flag = 'Y'
            AND cpp.min_premium_member_flag = 'Y';

      RETURN v_affiliate_type;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            NULL;
      END;

      -- Next check for a Premium combination member affiliation
      BEGIN
         SELECT 'PREMIUMMEMBER'
           INTO v_affiliate_type
           FROM POLICY p, combo_policy_period cpp
          WHERE p.policy_num = in_policy_num
            AND p.policy_num = cpp.policy_num
            AND cpp.policy_period_eff_date =
                                       (SELECT MAX (policy_period_eff_date)
                                          FROM policy_period pp
                                         WHERE pp.policy_num = cpp.policy_num)
            AND cpp.active_member_flag = 'Y'
            AND NVL (cpp.min_premium_member_flag, 'N') = 'N';

         RETURN v_affiliate_type;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            NULL;
      END;


      RETURN v_affiliate_type;
   END getpolicyaffiliatetype;

/*
  This procedure will return the list of affiliated policy numbers
  for the passed in policy number and affiliation type.
  if the include_affil_totals parm is TRUE then it will include
  a totals line at the end of the list.
 MODIFIED 09/10/2007 by MARBUR modified GetPolicyAffiliateList to return the agent and agency numbers
 MODIFIED 08/03/2010 by MARBUR added an additional select for sub accounts which have no rate account.
*/
   PROCEDURE getpolicyaffiliatelist (
      in_policy_num             IN       NUMBER,
      in_affiliate_type         IN       VARCHAR2,
      in_include_affil_totals   IN       VARCHAR2,
      recordset                 OUT      ref_cursor
   )
   IS
      v_totals_description   VARCHAR2 (50);
      v_affil_type           VARCHAR2 (10) := '';
   BEGIN
      -- build the Totals line description
      IF UPPER (in_affiliate_type) LIKE 'AFFIL%'
      THEN
         v_totals_description := '* * * AFFILIATION TOTALS * * *';
         v_affil_type := 'PHP';
      ELSE
         IF UPPER (in_affiliate_type) LIKE 'LEAS%' THEN
            v_totals_description := '* * * LEASING COMPANY TOTALS * * *';
            v_affil_type := 'LEASE';
         ELSE
            IF UPPER (in_affiliate_type) LIKE 'WRAP%' THEN
               v_totals_description := '* * * AFFILIATION TOTALS * * *';
               v_affil_type := 'WRAP';
            ELSE
                IF UPPER (in_affiliate_type) LIKE 'SUBAC%' THEN
                   v_totals_description := '* * * COMBINATION TOTALS * * *';
                   v_affil_type := 'SUBAC';
                ELSE
                    IF UPPER (in_affiliate_type) LIKE 'STATE%' THEN
                       v_totals_description := '* * * COMBINATION TOTALS * * *';
                       v_affil_type := 'OREGN';
                    ELSE
                       IF UPPER (in_affiliate_type) LIKE 'COMB%' THEN
                  v_totals_description := '* * * COMBINATION TOTALS * * *';
                  v_affil_type := 'COMBO';
                       ELSE
                          IF UPPER (in_affiliate_type) LIKE 'PREM%' THEN
                             v_totals_description := '* * * PREMIUM DISCOUNT TOTALS * * *';
                             v_affil_type := 'PREMIUM';
               END IF;
                       END IF;
                    END IF;
                END IF;
            END IF;
         END IF;
      END IF;

      -- Select for the manual affiliation list
      IF v_affil_type = 'PHP'
      THEN
         OPEN recordset FOR
            -- get affiliation members
            SELECT   policy_num || ' - '
                     || policy_name AS policy_num_and_name,
                     TO_CHAR (policy_num) AS policy_key, policy_num,
                     policy_name
                     ,agt.agent_num, agt.agency_seq_num,
                     p.policy_status_code
                FROM POLICY p JOIN policy_relationship pr
                     ON (   p.policy_num = mbr_policy_num
                         OR p.policy_num = parent_policy_num
                        )
                     LEFT OUTER JOIN AGENT agt ON ( p.AGENT_NUM = agt.AGENT_NUM )
               WHERE UPPER (pr.affil_typ_cd) = v_affil_type
                 AND (   pr.parent_policy_num = in_policy_num
                      OR pr.parent_policy_num =
                                       (SELECT parent_policy_num
                                          FROM policy_relationship
                                         WHERE mbr_policy_num = in_policy_num)
                     )
            UNION
            -- get Totals line
            SELECT      policy_num
                     || ' - '
                     || v_totals_description AS policy_num_and_name,
                     TO_CHAR (policy_num) || '*' AS policy_key, policy_num,
                     ' '
                     ,agt.agent_num, agt.agency_seq_num,
                     p.policy_status_code
                FROM POLICY p JOIN policy_relationship pr
                     ON (p.policy_num = parent_policy_num)
                     LEFT OUTER JOIN AGENT agt ON ( p.AGENT_NUM = agt.AGENT_NUM )
               WHERE UPPER (pr.affil_typ_cd) = v_affil_type
                 AND (   pr.mbr_policy_num = in_policy_num
                      OR pr.parent_policy_num = in_policy_num
                     )
                 AND UPPER (in_include_affil_totals) = 'TRUE'
                 AND ROWNUM = 1
            ORDER BY 4, 2;

         RETURN;
      END IF;

      -- Select for the leasing company / wrapup list
      IF v_affil_type IN ('LEASE', 'WRAP')
      THEN
         OPEN recordset FOR
            -- get leasing company / wrapup members
            SELECT   policy_num || ' - '
                     || policy_name AS policy_num_and_name,
                     TO_CHAR (policy_num) AS policy_key, policy_num,
                     policy_name
                     ,agt.agent_num, agt.agency_seq_num,
                     p.policy_status_code
                FROM POLICY p
                LEFT OUTER JOIN AGENT agt ON ( p.AGENT_NUM = agt.AGENT_NUM )
               WHERE p.policy_relationship_code = v_affil_type
                 AND (   p.policy_relationship_policy_num = in_policy_num
                      OR p.policy_relationship_policy_num =
                                      (SELECT policy_relationship_policy_num
                                         FROM POLICY
                                        WHERE policy_num = in_policy_num)
                     )
            UNION
            -- get the Totals line
            SELECT      policy_relationship_policy_num
                     || ' - '
                     || v_totals_description AS policy_num_and_name,
                        TO_CHAR (policy_relationship_policy_num)
                     || '*' AS policy_key,
                     policy_relationship_policy_num, ' '
                     ,agt.agent_num, agt.agency_seq_num,
                     p.policy_status_code
                FROM POLICY p
                LEFT OUTER JOIN AGENT agt ON ( p.AGENT_NUM = agt.AGENT_NUM )
               WHERE p.policy_relationship_code = v_affil_type
                 AND (   p.policy_relationship_policy_num = in_policy_num
                      OR p.policy_relationship_policy_num =
                                      (SELECT policy_relationship_policy_num
                                         FROM POLICY
                                        WHERE policy_num = in_policy_num)
                     )
                 AND UPPER (in_include_affil_totals) = 'TRUE'
                 AND ROWNUM = 1
            ORDER BY 4, 2;

         RETURN;
      END IF;

      -- Select for the combination / subaccount / state agencies
      IF v_affil_type  IN ('COMBO', 'SUBAC', 'OREGN')
      THEN
         OPEN recordset FOR
            -- get combo members
            SELECT   policy_num || ' - '
                     || policy_name AS policy_num_and_name,
                     TO_CHAR (policy_num) AS policy_key, policy_num,
                     policy_name
                     ,agt.agent_num, agt.agency_seq_num,
                     p.policy_status_code
                FROM POLICY p
                LEFT OUTER JOIN AGENT agt ON ( p.AGENT_NUM = agt.AGENT_NUM )
               WHERE (   p.rate_account = in_policy_num
                      OR p.rate_account = (SELECT rate_account
                                             FROM POLICY
                                            WHERE policy_num = in_policy_num)
                     )
            UNION
            -- get the Totals line
            SELECT      rate_account
                     || ' - '
                     || v_totals_description AS policy_num_and_name,
                     TO_CHAR (rate_account) || '*' AS policy_key,
                     rate_account, ' '
                     ,agt.agent_num, agt.agency_seq_num,
                     p.policy_status_code
                FROM POLICY p
                LEFT OUTER JOIN AGENT agt ON ( p.AGENT_NUM = agt.AGENT_NUM )
               WHERE (   p.rate_account = in_policy_num
                      OR p.rate_account = (SELECT rate_account
                                             FROM POLICY
                                            WHERE policy_num = in_policy_num)
                     )
                 AND UPPER (in_include_affil_totals) = 'TRUE'
                 AND ROWNUM = 1
            UNION
            -- added additional select for when subaccounts have no rate account number
            SELECT   policy_num || ' - '
                     || policy_name AS policy_num_and_name,
                     TO_CHAR (policy_num) AS policy_key, policy_num,
                     policy_name
                     ,agt.agent_num, agt.agency_seq_num,
                     p.policy_status_code
                FROM POLICY p
                LEFT OUTER JOIN AGENT agt ON ( p.AGENT_NUM = agt.AGENT_NUM )
               WHERE p.policy_num = in_policy_num
                    AND p.rate_account IS NULL
            ORDER BY 4, 2;

         RETURN;
      END IF;


      -- Select for the premium discount combination list
      IF v_affil_type = 'PREMIUM'
      THEN
         OPEN recordset FOR
            -- get combo members
            SELECT      cpp2.policy_num
                     || ' - '
                     || policy_name AS policy_num_and_name
                    ,TO_CHAR (cpp2.policy_num) AS policy_key
                    ,cpp2.policy_num, policy_name, agt.agent_num
                    ,agt.agency_seq_num,
                     p.policy_status_code
                FROM POLICY p
                    LEFT OUTER JOIN AGENT agt ON ( p.AGENT_NUM = agt.AGENT_NUM )
                    ,combo_policy_period cpp
                    ,combo_policy_period cpp2
                    ,policy_status_code psc
               WHERE cpp.policy_num = in_policy_num
                 AND cpp.policy_period_eff_date =
                                       (SELECT MAX (policy_period_eff_date)
                                          FROM policy_period pp
                                         WHERE pp.policy_num = cpp.policy_num)
                 AND cpp.combo_period_seq_num = cpp2.combo_period_seq_num
                 AND cpp2.active_member_flag = 'Y'
                 AND cpp2.policy_num = p.policy_num
                 and p.POLICY_STATUS_CODE = psc.CODE
            UNION
            -- get the Totals line
            SELECT      cpp2.policy_num
                     || ' - ' ||
                      v_totals_description AS policy_num_and_name
                    , TO_CHAR (cpp2.policy_num) || '*' AS policy_key
                    ,cpp2.policy_num, ' ', agt.agent_num, agt.agency_seq_num,
                     p.policy_status_code
                FROM POLICY p
                    LEFT OUTER JOIN AGENT agt ON ( p.AGENT_NUM = agt.AGENT_NUM )
                    ,combo_policy_period cpp
                    ,combo_policy_period cpp2
               WHERE cpp.policy_num = in_policy_num
                 AND cpp.policy_period_eff_date =
                                       (SELECT MAX (policy_period_eff_date)
                                          FROM policy_period pp
                                         WHERE pp.policy_num = cpp.policy_num)
                 AND cpp.combo_period_seq_num = cpp2.combo_period_seq_num
                 AND cpp2.active_member_flag = 'Y'
                 and cpp2.MIN_PREMIUM_MEMBER_FLAG = 'Y'
                 AND cpp2.policy_num = p.policy_num
                 AND UPPER (in_include_affil_totals) = 'TRUE'
                 AND ROWNUM = 1
            ORDER BY 4, 2;

         RETURN;
      END IF;
   END getpolicyaffiliatelist;

   -- return the parent policy number for the passed policy.
   FUNCTION getparentpolicy (
      in_policy_num             IN   NUMBER,
      in_is_policyholder_user   IN   VARCHAR2
   )
      RETURN NUMBER
   IS
      v_parent_policy   NUMBER;
      v_affil_type      VARCHAR (20);
   BEGIN
      -- get the affiliation type for this policy
      v_affil_type :=
              getpolicyaffiliatetype (in_policy_num, in_is_policyholder_user);

      -- if we get here then we need to query for the parent policy number.
      IF v_affil_type = 'AFFILIATIONMEMBER'
      THEN
         -- get the manual affiliation parent
         SELECT parent_policy_num
           INTO v_parent_policy
           FROM policy_relationship
          WHERE mbr_policy_num = in_policy_num;
      ELSE
         IF v_affil_type IN ('LEASINGMEMBER', 'WRAPUPMEMBER', 'SUBACMEMBER', 'OREGNMEMBER')
         THEN
            -- get the leasing/wrapup parent
            SELECT policy_relationship_policy_num
              INTO v_parent_policy
              FROM POLICY
             WHERE policy_num = in_policy_num;
         ELSE
            IF v_affil_type = 'COMBOMEMBER'
            THEN
               -- get the combo parent (rate account)
               SELECT rate_account
                 INTO v_parent_policy
                 FROM POLICY
                WHERE policy_num = in_policy_num;
            ELSE
               IF v_affil_type = 'PREMIUMMEMBER'
               THEN
                  -- get the minimum premium policy in the combo_policy_period
                  SELECT cpp2.policy_num
                    INTO v_parent_policy
                    FROM combo_policy_period cpp, combo_policy_period cpp2
                   WHERE cpp.policy_num = in_policy_num
                     AND cpp.policy_period_eff_date =
                                       (SELECT MAX (policy_period_eff_date)
                                          FROM policy_period pp
                                         WHERE pp.policy_num = cpp.policy_num)
                     AND cpp.combo_period_seq_num = cpp2.combo_period_seq_num
                     AND cpp2.active_member_flag = 'Y'
                     AND cpp2.min_premium_member_flag = 'Y';
               ELSE
               -- When non of the above affiliation types
               -- then set the parent policy = input policy.
               v_parent_policy := in_policy_num;
               END IF;
            END IF;
         END IF;
      END IF;

      RETURN v_parent_policy;
   END getparentpolicy;

   FUNCTION sp73111_get_wcis_std_premium
/****************************************************************
*Procedure Title  : Get WCIS Standard Premium
*Name             : SP73111_GET_WCIS_STD_PREMIUM
*Called by        : Business Online
*****************************************************************
* Description: This procedure gets the Standardized Standard Premium
*               based on the 9/8/07 New Standard Premium Project.
*
*               THIS LOGIC IS ALSO USED IN BDS.  IF YOU CHANGE THIS
*               YOU MUST CORRODINATE WITH THE BDS TEAM.
*
*               It gets the max premium history set for the policy
*               and policy period passed in.  It uses the Prem_Summary
*               view to define the correct set.
*
* Maintenance Log:
*   Name            CSR        Date     Description
* John Frey                 09/08/2007  Initial Release
******************************************************************/
   (
      in_policy_num         IN   NUMBER,
      in_pol_per_eff_date   IN   DATE
   )
      RETURN NUMBER
   IS
   BEGIN
      RETURN pk73059_prem_hist_summary.get_standard_premium(
                     pk73059_prem_hist_summary.get_max_ph_set_id(in_policy_num,in_pol_per_eff_date));
   END sp73111_get_wcis_std_premium;

/* Formatted on 4/23/2020 4:26:52 PM (QP5 v5.256.13226.35510) */
FUNCTION getwrapuplegalname (in_policy_num IN NUMBER)
   RETURN VARCHAR2
IS
   v_wrapup_legal_name   VARCHAR2 (80);
   v_rel_policy_num      NUMBER;
   v_rate_account        NUMBER;
BEGIN
   BEGIN
      SELECT policy_relationship_policy_num, legal_name
        INTO v_rel_policy_num, v_wrapup_legal_name
        FROM policy
       WHERE policy_relationship_code = 'WRAP' AND policy_num = in_policy_num;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         NULL;
   END;

   -- Are the policy_num and Policy_Relationship_policy_num different
   IF v_rel_policy_num IS NOT NULL AND in_policy_num <> v_rel_policy_num
      THEN
      BEGIN
         SELECT rate_account, legal_name
           INTO v_rate_account, v_wrapup_legal_name
           FROM policy
          WHERE policy_num = v_rel_policy_num;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            NULL;
      END;

      IF v_rate_account IS NOT NULL
      THEN
         BEGIN
            SELECT legal_name
              INTO v_wrapup_legal_name
              FROM policy
             WHERE policy_num = v_rate_account;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               NULL;
         END;
      END IF;
   END IF;
   RETURN v_wrapup_legal_name;
END getwrapuplegalname;

Sources¶

Oracle retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk72846_view_policy.json
Calling code: SAIF.Business.View.Policy\clsAgency.vb
Calling code: SAIF.Business.View.Policy\clsMCOCollection.vb
Calling code: SAIF.Business.View.Policy\clsPolicyMCOCollection.vb
Calling code: SAIF.Business.View.Policy\clsAffiliatedPolicyCollection.vb
Calling code: SAIF.Business.View.Policy\clsPolicyAddress.vb
Calling code: SAIF.Business.View.Policy\clsPolicyData.vb
Calling code: SAIF.Business.View.Policy\clsPolicyPeriodCollection.vb
Calling code: SAIF.Business.View.Policy\clsTeamAssignmentCollection.vb

pk72846_view_policy¶

Overview¶

Purpose¶

Procedures / Methods¶

getviewdetail¶

Parameters¶

team_assign_query¶

Parameters¶

getpolicyperiods¶

Parameters¶

getpolicyaddress¶

Parameters¶

getpolicyaddressbylocseqnum¶

Parameters¶

getagency¶

Parameters¶

getgaps¶

Parameters¶

getpolicyaffiliatetype¶

Parameters¶

getpolicyaffiliatelist¶

Parameters¶

getparentpolicy¶

Parameters¶

getwrapuplegalname¶

Parameters¶

Business Rules¶

BR-VP-018 Gap detection ignores flat-cancel and prospect periods¶

BR-VP-019 Assigned-risk policy periods mask risk-type and plan data until the info page prints¶

BR-VP-020 Claim-address requests may fall back to the policy address¶

BR-VP-021 Address retrieval prefers current contacts and the latest effective location row¶

BR-VP-022 Affiliate-type classification follows a fixed hierarchy¶

BR-VP-023 Parent-policy resolution depends on the resolved affiliate type¶

BR-VP-024 Affiliate total rows are optional and vary by affiliation family¶

BR-VP-025 Wrap-up legal-name lookup climbs from member to relationship policy to rate account¶

Operations¶

Retrieve agency of record contact¶

Detect policy coverage gaps¶

Retrieve view-policy summary detail¶

Build ordered team-assignment rows¶

Retrieve policy-period history with masking rules¶

Resolve policy address rows¶

Classify and list affiliated policies¶

Resolve parent policy number¶

Resolve wrap-up legal name¶

Calling code references¶

Source Code¶

Related documentation¶

Sources¶