SQL-029: Read Pa Audit Data

This statement supports the premium-audit library by read Pa Audit Data.

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
—	—	—	—	No bound parameters detected in source.

Tables

Table	Access	Columns used
PA_AUDIT	JOIN	See sample SQL.
POLICY	JOIN	See sample SQL.

Sample SQL

SELECT
  PA.APPEAL_HEARING_DATE as AppealHearingDate,
  PA.APPEAL_NOTICE_DATE as AppealNoticeDate,
  PA.APPEAL_REQUEST_DATE as AppealRequestDate,
  PA.APPOINTMENT_DATE as AppointmentDate,
  PA.APPOINTMENT_LOC_SEQ_NUM as AppointmentLocationSeqNum,
  PA.ASSIGNMENT_DATE as AssignmentDate,
  PA.ASSIGNMENT_WEIGHT_COUNT as AssignmentWeightCount,
  PA.AUDIT_HOURS_COUNT as AuditHoursCount,
  PA.AUDIT_HOURS as AuditHours,
  PA.PA_METHOD_CODE AS AuditMethodCode,
  PA.TRAVEL_HOURS as TravelHours,
  PA.MISC_HOURS as MiscHours,
  PA.AUDIT_INSTRUCTIONS_TEXT as AuditInstructionsText,
  pk73211_premium_audit.GetAuditLocationSeqNum(PA.POLICY_NUM) as AuditLocationSeqNum,
  PA.AUDIT_OFFICE_CODE as AuditOfficeCode,
  PA.CANCEL_DATE as CancelDate,
  PA.COMPLETED_DATE as CompletedDate,
  PA.CREATE_DATE as CreateDate,
  PA.CREATE_USER as CreateUser,
  PA.DISPUTED_AMOUNT as DisputedAmount,
  PA.DUE_DATE as DueDate,
  PA.PROGRAM_DUE_DATE as ProgramDueDate,
  PA.END_DATE as EndDate,
  PA.FOLLOWUP_DATE as FollowupDate,
  PA.PA_AUDITOR_ID as AuditorID,
  PA.PA_AUDIT_SEQ_NUM as AuditSeqNum,
  PA.PA_CANCEL_REASON_CODE as CancelReasonCode,
  PA.PA_REASON_CODE as ReasonCode,
  PA.PA_REVIEW_STATUS_CODE as ReviewStatusCode,
  PA.PA_REVISES_SEQ_NUM as RevisesSeqNum,
  PA.PA_SLCTN_CATEGORY_GROUP_CODE as SelectionCategoryGroupCode,
  PA.PA_STATUS_CODE as StatusCode,
  PA.PA_TEST_AUDIT_APPEAL_CODE as TestAuditAppealCode,
  PA.PA_TEST_AUDIT_RESPONSE_CODE as TestAuditResponseCode,
  PA.PA_TEST_AUDIT_RESULT_CODE as TestAuditResultCode,
  PA.PA_TEST_AUDIT_TYPE_CODE as TestAuditTypeCode,
  PA.PA_TYPE_CODE as TypeCode,
  PA.PA_WAIVER_REASON_CODE as WaiverReasonCode,
  PA.POLICY_NUM as PolicyNum,
  PA.REVIEW_DATE as ReviewDate,
  PA.SELECTION_STD_PREM_AMOUNT as SelectionStdPremAmount,
  PA.SCHEDULING_INPROGRESS_FLAG AS SchedulingInProgressFlag,
  PA.START_DATE as StartDate,
  PA.SUBMITTED_DATE as SubmittedDate,
  PA.TEST_AUDITOR_NAME as TestAuditorName,
  PA.TEST_AUDIT_DIFFERENCE_AMOUNT as TestAuditDifferenceAmount,
  PA.UPDATE_DATE as UpdateDate,
  PA.UPDATE_SEQ_NUM as UpdateSeqNum,
  PA.UPDATE_USER as UpdateUser,
  PA.WAIVER_END_DATE as WaiverEndDate,
  PA.WAIVER_START_DATE as WaiverStartDate,
  PA.WARNING_LETTER_DATE as WarningLetterDate,
  pol.SECURE_POLICY_CODE as SecurePolicyCode,
  PA2.PA_AUDIT_SEQ_NUM AS NextAuditSeqNum,
  PA.WORKBOOK_STATUS_CODE AS WorkbookStatusCode,
  PA.WORKBOOK_FILE_NAME AS WorkbookFileName,
  PA.WORKBOOK_IMAGE_SEQ_NUM AS WorkbookImageSeqNum
FROM
  PA_AUDIT PA
JOIN
  policy pol on (pa.policy_num = pol.policy_num)
LEFT OUTER JOIN
  pa_audit pa2 on (pa2.pa_revises_seq_num = pa.pa_audit_seq_num and pa2.pa_status_code <> 'CANC')

Operations and processes

• Select policies for premium audit (batch/expiring)
• Select policies for premium audit (off-cycle/daily)
• Assign renewal (batch) audits to auditors
• Assign off-cycle audits to auditors

Business rules

• BR-020
• BR-021

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

Sources

• Business/SAIF.Business.Policy.Audit.Premium/clsAuditCollection.vb:1208