SQL-131: Retrieve CbpPrimaryProcedureCode

This ORM statement performs a read operation on CbpPrimaryProcedureCode via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/Claims/Bills/BillProcedureService.cs (L64, L115, L155).

Classification

Field	Value
SQL ID	SQL-131
Operation type	Read
Execution method	ORM (NHibernate LINQ)
Database	Oracle
Schema	[unqualified]**
Command type	N/A (ORM-generated)
Business object	CbpPrimaryProcedureCode
Criteria property	N/A — ORM
Lifecycle method	CreateProcedures, GetPrimaryProcedureCodes, UpdateProcedures

Parameters

Parameter	Direction	Type	Max length	Description
newProcedure.PrimaryProcedure	Input	**	N/A	y.Code == newProcedure.PrimaryProcedure → column CODE
updatedProcedure.PrimaryProcedure	Input	**	N/A	y.Code == updatedProcedure.PrimaryProcedure → column CODE

Tables

Table	Access	Columns used
CBP_PRIMARY_PROCEDURE_CODE	SELECT	CODE, UPDATE_NUM, CODE_DESCR, UPDATE_USER, UPDATE_DATE

LINQ statement

Source: Services/SAIF.Services/Claims/Bills/BillProcedureService.cs:64

// Services/SAIF.Services/Claims/Bills/BillProcedureService.cs:64-65
                dataProcedure.PrimaryProcedure = unitOfWork.GetRepository<CbpPrimaryProcedureCode>()
                    .Query().FirstOrDefault(y => y.Code == newProcedure.PrimaryProcedure);

// Services/SAIF.Services/Claims/Bills/BillProcedureService.cs:115-116
                dataProcedure.PrimaryProcedure = unitOfWork.GetRepository<CbpPrimaryProcedureCode>()
                   .Query().FirstOrDefault(y => y.Code == updatedProcedure.PrimaryProcedure);

// Services/SAIF.Services/Claims/Bills/BillProcedureService.cs:155
            return unitOfWork.GetRepository<CbpPrimaryProcedureCode>().Query().ToList();

Sample SQL

Inferred SQL: This SQL was inferred from the LINQ statement above and the NHibernate mappings. It is not concrete SQL from source code; NHibernate generates the executed statement at runtime, so aliases, column order, join syntax, and bind-parameter names may differ.

-- Inferred from LINQ (NHibernate mapping translation); not concrete SQL
-- Services/SAIF.Services/Claims/Bills/BillProcedureService.cs:64
SELECT *
FROM (
  SELECT
    CPPC.CODE,
    CPPC.UPDATE_NUM,
    CPPC.CODE_DESCR,
    CPPC.UPDATE_USER,
    CPPC.UPDATE_DATE
  FROM CBP_PRIMARY_PROCEDURE_CODE CPPC
  WHERE CPPC.CODE = :PrimaryProcedure
)
WHERE ROWNUM <= 1

-- Services/SAIF.Services/Claims/Bills/BillProcedureService.cs:115
SELECT *
FROM (
  SELECT
    CPPC.CODE,
    CPPC.UPDATE_NUM,
    CPPC.CODE_DESCR,
    CPPC.UPDATE_USER,
    CPPC.UPDATE_DATE
  FROM CBP_PRIMARY_PROCEDURE_CODE CPPC
  WHERE CPPC.CODE = :PrimaryProcedure
)
WHERE ROWNUM <= 1

-- Services/SAIF.Services/Claims/Bills/BillProcedureService.cs:155
SELECT
  CPPC.CODE,
  CPPC.UPDATE_NUM,
  CPPC.CODE_DESCR,
  CPPC.UPDATE_USER,
  CPPC.UPDATE_DATE
FROM CBP_PRIMARY_PROCEDURE_CODE CPPC

Translation notes: - newProcedure.PrimaryProcedure / updatedProcedure.PrimaryProcedure -> bind :PrimaryProcedure.

Operations and processes

• BillProcedureService.CreateProcedures — see Operations and processes
• BillProcedureService.GetPrimaryProcedureCodes — see Operations and processes
• BillProcedureService.UpdateProcedures — see Operations and processes

Business rules

• SVC-BR-BIL-110 ProcedureCode required (ProcedureCodeRequiredMessage)
• SVC-BR-BIL-111 ProcedureCode length 1-8 (ProcedureCodeToLongMessage)
• SVC-BR-BIL-112 PrimaryProcedure required and must be a valid code (PrimaryProcedureCodeRequired/InvalidMessage)
• SVC-BR-BIL-113
• SVC-BR-BIL-114 ProcedureDate ≤ today (ProcedureDateInvalidMessage)

Sources

• Services/SAIF.Services/Claims/Bills/BillProcedureService.cs (L64, L115, L155)