--- title: "pk73211_premium_audit.GetOffCyclePolicies stored procedure documentation" summary: "Analysis of Oracle package/procedure including business rules and operations." status: draft owner: "Technical Writer" last_reviewed: "2026-07-31" library: SAIF.Business.Policy.Audit.Premium reference_type: package source_schema: "`WCIS_DBA`" source_name: "pk73211_premium_audit.GetOffCyclePolicies" source_type: "Package procedure (ref cursor out)" oracle_status: "found" --- # pk73211_premium_audit.GetOffCyclePolicies ## Overview | Field | Value | | --- | --- | | **Schema** | `WCIS_DBA` | | **Type** | Package procedure (ref cursor out) | | **Database** | Oracle / WCIS | | **Calling libraries** | `SAIF.Business.Policy.Audit.Premium` | | **Calling classes** | `clsAuditSelectionPolicyPeriod`, `clsAuditSelectionPolicyPeriodCollection` | ## Purpose Unions four candidate populations — new-business `P401` endorsements, batch payroll audits, leasing-parent payroll audits and 15-day-delayed mid-term cancellations — into one ref cursor ## Procedures / Methods ### GetOffCyclePolicies | Field | Value | | --- | --- | | **Returns** | See Oracle signature / caller behavior | | **Called by** | `RetrieveSQL` (SQL-060, SQL-061) | **Description:** Unions four candidate populations — new-business `P401` endorsements, batch payroll audits, leasing-parent payroll audits and 15-day-delayed mid-term cancellations — into one ref cursor ## Business Rules | Rule ID | Rule | Condition | Action | | --- | --- | --- | --- | | PROC-RULE-006 | An unusable last-run date suppresses the entire off-cycle run | `in_lastrundate <= '01-JAN-1900'` | The `OPEN … FOR` is skipped | | PROC-RULE-007 | Off-cycle candidates are drawn from exactly four disjoint populations, unioned | Always | New-business endorsements, batch payroll audits, leasing-parent payroll audits, and mid-term cancellations | | PROC-RULE-008 | New-business selection applies only to active or pending-cancel policies | `psc.policy_status_group_code IN ('ACTIVE','PENDCA')` | Row is excluded otherwise | | PROC-RULE-009 | New-business selection considers only the policy's *first* non-flat-cancelled period | `pp.policy_period_eff_date = (SELECT MIN(policy_period_eff_date) … WHERE policy_period_status_code <> 'FC')` | Later periods are excluded | | PROC-RULE-010 | New business qualifies only when a `P401` endorsement was created inside the run window | `EXISTS (… endorse_code = 'P401' AND create_date > in_lastrundate AND create_date <= in_currentrundate)` | Row is tagged `'NEW'` | | PROC-RULE-011 | A payroll audit only becomes an off-cycle candidate once its period is final-audited or fully reconciled | `pp.policy_period_status_code = 'FA' OR pk73211_premium_audit.is_reconciled(policy_num, eff_date) = 'Y'` | Row is excluded otherwise | | PROC-RULE-012 | Only batch-status payroll audits are eligible | `audit_status_code = 'B'` | Row is excluded otherwise | | PROC-RULE-013 | Physical and physical-revision audit types are never re-selected off-cycle | `ac.audit_type_code NOT IN ('PHYS','PHYSR')` | Row is excluded | | PROC-RULE-014 | A leasing client's payroll audit is attributed to its leasing **parent** policy | `p.policy_relationship_code = 'LEASE'`, joined to the period of `p.policy_relationship_policy_num` that spans the audit's effective date | `policy_relationship_policy_num` is projected as the candidate policy number | | PROC-RULE-015 | Cancellation-driven candidates use only the policy's latest cancellation | `c.cancellation_seq_num = (SELECT MAX(cancellation_seq_num) …)` | Earlier cancellations are ignored | | PROC-RULE-016 | Only mid-term cancellations of cancelled policies qualify | `p2.policy_status_code = '5'` and `TRUNC(c.cancel_date)` strictly between period effective and expiration dates | Row is included, tagged `'OTHER'` | | PROC-RULE-017 | Cancellation candidates are deliberately delayed by 15 days | `c.cancel_update_date > in_lastrundate - 15 AND c.cancel_update_date <= in_currentrundate - 15` | The run window is shifted back a fortnight so that reinstatements can settle first | ## Operations | Operation | Description | Trigger | | --- | --- | --- | | Retrieve off-cycle policy periods for audit selection | Unions four candidate populations — new-business `P401` endorsements, batch payroll audits, leasing-parent payroll audits and 15-day-delayed mid-term cancellations — into one ref cursor | `clsAuditSelectionPolicyPeriodCollection.RetrieveSQL` with `AuditSelectionType.OffCyclePolicies` (SQL-060/061); OP-003 | ## Source Code
PL/SQL package or method excerpt ```sql PROCEDURE getoffcyclepolicies(in_lastrundate IN DATE , in_currentrundate IN DATE , out_recordset OUT ref_cursor) IS BEGIN IF in_lastrundate > '01-JAN-1900' THEN OPEN out_recordset FOR SELECT DISTINCT pp.policy_num AS policynum , pp.policy_period_eff_date AS policyperiodeffdate , pp.policy_period_exp_date AS policyperiodexpdate , pp.market_type_code AS markettypecode , 'NEW' AS selectiontype FROM policy_period pp , policy p , policy_status_code psc WHERE pp.policy_num = p.policy_num AND p.policy_status_code = psc.code AND psc.policy_status_group_code IN ('ACTIVE' , 'PENDCA') AND pp.policy_period_eff_date = (SELECT MIN( pmin.policy_period_eff_date) FROM policy_period pmin WHERE pmin.policy_num = pp.policy_num AND PMIN.POLICY_PERIOD_STATUS_CODE <> 'FC') AND EXISTS (SELECT 1 FROM endorsement e WHERE e.policy_num = p.policy_num AND e.policy_period_eff_date = pp.policy_period_eff_date AND e.endorse_code = 'P401' AND e.create_date > in_lastrundate AND e.create_date <= in_currentrundate) UNION SELECT DISTINCT a.policy_num AS policynum , a.policy_period_eff_date AS policyperiodeffdate , pp.policy_period_exp_date AS policyperiodexpdate , pp.market_type_code AS markettypecode , 'OTHER' AS selectiontype FROM policy_period_payroll_audit a JOIN policy_period pp ON a.policy_num = pp.policy_num AND a.policy_period_eff_date = pp.policy_period_eff_date AND ( pp.policy_period_status_code = 'FA' OR pk73211_premium_audit.is_reconciled(pp.policy_num , pp.policy_period_eff_date) = 'Y') JOIN audit_code ac ON a.audit_code = ac.code WHERE audit_status_code = 'B' AND ac.audit_type_code NOT IN ('PHYS' , 'PHYSR') AND a.update_date > in_lastrundate AND a.update_date <= in_currentrundate UNION SELECT DISTINCT p.policy_relationship_policy_num AS policynum , pp.policy_period_eff_date AS policyperiodeffdate , pp.policy_period_exp_date AS policyperiodexpdate , pp.market_type_code AS markettypecode , 'OTHER' AS selectiontype FROM policy_period_payroll_audit pppa JOIN policy p ON pppa.policy_num = p.policy_num AND p.policy_relationship_code = 'LEASE' JOIN policy_period pp ON pp.policy_num = p.policy_relationship_policy_num AND pp.policy_period_eff_date <= pppa.policy_period_eff_date AND pp.policy_period_exp_date > pppa.policy_period_eff_date AND ( pp.policy_period_status_code = 'FA' OR pk73211_premium_audit.is_reconciled(pp.policy_num , pp.policy_period_eff_date) = 'Y') JOIN audit_code ac ON pppa.audit_code = ac.code WHERE audit_status_code = 'B' AND ac.audit_type_code NOT IN ('PHYS' , 'PHYSR') AND pppa.update_date > in_lastrundate AND pppa.update_date <= in_currentrundate UNION SELECT DISTINCT pp2.policy_num AS policynum , pp2.policy_period_eff_date AS policyperiodeffdate , pp2.policy_period_exp_date AS policyperiodexpdate , pp2.market_type_code AS markettypecode , 'OTHER' AS selectiontype FROM policy_period pp2 , policy p2 , cancellation c WHERE pp2.policy_num = p2.policy_num AND p2.policy_status_code = '5' AND c.policy_num = p2.policy_num AND c.cancellation_seq_num = (SELECT MAX( cancellation_seq_num) FROM cancellation c2 WHERE c2.policy_num = c.policy_num) AND TRUNC( c.cancel_date) > pp2.policy_period_eff_date AND TRUNC( c.cancel_date) < pp2.policy_period_exp_date AND NVL(c.cancel_date, pp2.policy_period_exp_date) > pp2.policy_period_eff_date AND c.cancel_update_date > in_lastrundate - 15 AND c.cancel_update_date <= in_currentrundate - 15 ORDER BY 1 , 2; END IF; END getoffcyclepolicies; /******************************************************************** | Name : get_pa_pol_per_by_seq_num | Prepared By : carade | Created : 09/18/2008 | Description : Get pa_policy_period by pa seq num. | Modification History | ------------------------------------------------------------------*/ ```
## Related documentation - [Procedure catalog](index.md) - [Business rules](../business-rules.md) - [Operations and processes](../operations-processes.md) - [SQL catalog](../sql/index.md) > **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 - Oracle DBA_SOURCE: pk73211_premium_audit.GetOffCyclePolicies - Calling code: ``analysis\oracle-source\pk73211_premium_audit.sql`; `clsAuditSelectionPolicyPeriodCollection.vb`