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
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¶
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