SQL-112: Retrieve CbpAuthorization

This ORM statement performs a read operation on CbpAuthorization via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/Claims/Bills/AuthorizationService.cs (L47, L164, L288).

Classification

Field	Value
SQL ID	SQL-112
Operation type	Read
Execution method	ORM (NHibernate LINQ)
Database	Oracle
Schema	[unqualified]**
Command type	N/A (ORM-generated)
Business object	CbpAuthorization
Criteria property	N/A — ORM
Lifecycle method	DeleteAuthorization, GetAuthorizations, UpdateAuthorization

Parameters

Parameter	Direction	Type	Max length	Description
claimId	Input	**	N/A	x.ClaimId == claimId → column CLAIM_ID
authorization.Id	Input	**	N/A	x.Id == authorization.Id → column CBP_AUTHORIZATION_SEQ_NUM
authorizationId	Input	**	N/A	x.Id == authorizationId → column CBP_AUTHORIZATION_SEQ_NUM

Tables

Table	Access	Columns used
CBP_AUTHORIZATION	SELECT	CBP_AUTHORIZATION_SEQ_NUM, UPDATE_NUM, CLAIM_ID, RECEIVED_TIMELY_FLAG_CODE, AUTH_FROM_DATE, AUTH_THRU_DATE, MAXIMUM_VISITS_QTY, UPDATE_DATE, UPDATE_USER, CBP_AUTHORIZATION_TYPE_CODE, CBP_SERVICE_PM_GROUP_CODE, BILL_PROVIDER_LOCATION_SEQ_NUM

LINQ statement

Source: Services/SAIF.Services/Claims/Bills/AuthorizationService.cs:47

// Services/SAIF.Services/Claims/Bills/AuthorizationService.cs:47-48
            var authorizations = unitOfWork.GetRepository<CbpAuthorization>().Query()
                     .Where(x => x.ClaimId == claimId); //.ToList();

// Services/SAIF.Services/Claims/Bills/AuthorizationService.cs:50
            var items = Mapper.Map<AuthorizationItem[]>(authorizations);

// Services/SAIF.Services/Claims/Bills/AuthorizationService.cs:164
            var authorize = unitOfWork.GetRepository<CbpAuthorization>().Query().Single(x => x.Id == authorization.Id);

// Services/SAIF.Services/Claims/Bills/AuthorizationService.cs:288-289
            var authorization = unitOfWork.GetRepository<CbpAuthorization>().Query().SingleOrDefault(x => x.Id == authorizationId);
            unitOfWork.GetRepository<CbpAuthorization>().Delete(authorization);

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/AuthorizationService.cs:47
SELECT
  CA.CBP_AUTHORIZATION_SEQ_NUM,
  CA.UPDATE_NUM,
  CA.CLAIM_ID,
  CA.RECEIVED_TIMELY_FLAG_CODE,
  CA.AUTH_FROM_DATE,
  CA.AUTH_THRU_DATE,
  CA.MAXIMUM_VISITS_QTY,
  CA.UPDATE_DATE,
  CA.UPDATE_USER,
  CA.CBP_AUTHORIZATION_TYPE_CODE,
  CA.CBP_SERVICE_PM_GROUP_CODE,
  CA.BILL_PROVIDER_LOCATION_SEQ_NUM
FROM CBP_AUTHORIZATION CA
WHERE CA.CLAIM_ID = :claimId

-- Services/SAIF.Services/Claims/Bills/AuthorizationService.cs:164
SELECT
  CA.CBP_AUTHORIZATION_SEQ_NUM,
  CA.UPDATE_NUM,
  CA.CLAIM_ID,
  CA.RECEIVED_TIMELY_FLAG_CODE,
  CA.AUTH_FROM_DATE,
  CA.AUTH_THRU_DATE,
  CA.MAXIMUM_VISITS_QTY,
  CA.UPDATE_DATE,
  CA.UPDATE_USER,
  CA.CBP_AUTHORIZATION_TYPE_CODE,
  CA.CBP_SERVICE_PM_GROUP_CODE,
  CA.BILL_PROVIDER_LOCATION_SEQ_NUM
FROM CBP_AUTHORIZATION CA
WHERE CA.CBP_AUTHORIZATION_SEQ_NUM = :Id
-- NHibernate expects at most one row

-- Services/SAIF.Services/Claims/Bills/AuthorizationService.cs:288
SELECT
  CA.CBP_AUTHORIZATION_SEQ_NUM,
  CA.UPDATE_NUM,
  CA.CLAIM_ID,
  CA.RECEIVED_TIMELY_FLAG_CODE,
  CA.AUTH_FROM_DATE,
  CA.AUTH_THRU_DATE,
  CA.MAXIMUM_VISITS_QTY,
  CA.UPDATE_DATE,
  CA.UPDATE_USER,
  CA.CBP_AUTHORIZATION_TYPE_CODE,
  CA.CBP_SERVICE_PM_GROUP_CODE,
  CA.BILL_PROVIDER_LOCATION_SEQ_NUM
FROM CBP_AUTHORIZATION CA
WHERE CA.CBP_AUTHORIZATION_SEQ_NUM = :authorizationId
-- NHibernate expects at most one row

Translation notes: - L47-48 is materialized by Mapper.Map<AuthorizationItem[]>(authorizations) (L50); the //.ToList() is commented out. - L288 is followed by Delete(authorization) (L289) - a separate CRUD statement not documented under this SQL ID. - authorization.Id (L164) -> bind :Id.

Operations and processes

• AuthorizationService.DeleteAuthorization — see Operations and processes
• AuthorizationService.GetAuthorizations — see Operations and processes
• AuthorizationService.UpdateAuthorization — see Operations and processes

Business rules

• SVC-BR-BIL-142 ClaimId NotNull - 'Error - Claim is required.'
• SVC-BR-BIL-143 BillingProviderName NotNull - 'Error - Billing Provider is required.'

Sources

• Services/SAIF.Services/Claims/Bills/AuthorizationService.cs (L47, L164, L288)