SQL-037: Create Location

This ORM statement performs a create operation on Location via CreateDebtor, SaveDebtor, executed from Services/SAIF.Services/Collections/CollectionsService.cs (L576), Services/SAIF.Services/Collections/CollectionsService.cs (L665).

Classification

Field	Value
SQL ID	SQL-037
Operation type	Create
Execution method	ORM (IRepository.Add)
Database	Oracle
Schema	[unqualified]**
Command type	N/A (ORM-generated)
Business object	Location
Criteria property	N/A — ORM
Lifecycle method	CreateDebtor, SaveDebtor

Parameters

Parameter	Direction	Type	Max length	Description
:Id	Input	long	N/A	Identifier from LOCATION_SEQ.NEXTVAL
:PolicyNum	Input	long?	N/A
:BusId	Input	long?	N/A
:AgentNum	Input	long?	N/A
:ParentId	Input	long?	N/A
:EffectiveDate	Input	DateTime	N/A
:ExpirationDate	Input	DateTime?	N/A
:LocationCode	Input	string	N/A
:LocationName	Input	string	N/A
:AddressLine1	Input	string	N/A
:AddressLine2	Input	string	N/A
:City	Input	string	N/A
:Country	Input	string	N/A
:PhoneNumber	Input	string	N/A
:PhoneExtension	Input	string	N/A
:Fax	Input	string	N/A
:State	Input	string	N/A
:Zip	Input	string	N/A
:Zip4	Input	string	N/A
:SicCode	Input	string	N/A
:ProviderNum	Input	long?	N/A
:CreateUser	Input	string	N/A
:CreateDate	Input	DateTime	N/A
:UpdateUser	Input	string	N/A
:UpdateDate	Input	DateTime?	N/A
:UspsBarcode	Input	string	N/A
:EmailAddress	Input	string	N/A
:EmployeeCount	Input	long?	N/A
:AddressLine3	Input	string	N/A
:IsPrimaryLocation	Input	bool	N/A	Bound as 'Y'/'N' (YesNoType)
:CellPhone	Input	string	N/A

Tables

Table	Access	Columns used
LOCATION	INSERT, UPDATE	LOC_SEQ_NUM, POLICY_NUM, BUS_SEQ_NUM, AGENT_NUM, PARENT_LOC_SEQ_NUM, LOC_EFF_DATE, LOC_EXP_DATE, LOCATION_CODE, LOCATION_NAME, LOC_ADDR1, LOC_ADDR2, LOC_CITY, LOC_COUNTRY, LOC_PHONE, LOC_PHONE_EXT, LOC_FAX, LOC_STATE, LOC_ZIP, LOC_ZIP4, SIC_CODE, PROVIDER_NUM, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, USPS_BARCODE, EMAIL_ADDR, EMPLOYEE_COUNT, LOC_ADDR3, PRIMARY_LOCATION_FLAG, CELL_PHONE (identifier from sequence LOCATION_SEQ.NEXTVAL)

LINQ statement

Source: Services/SAIF.Services/Collections/CollectionsService.cs:576, Services/SAIF.Services/Collections/CollectionsService.cs:665.

// Services/SAIF.Services/Collections/CollectionsService.cs:576
UnitOfWork.GetRepository<LocationDataModel.Location>().Add(locData);

// Services/SAIF.Services/Collections/CollectionsService.cs:665
UnitOfWork.GetRepository<LocationDataModel.Location>().Add(locData);

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
-- New location (CreateDebtor :576, and SaveDebtor :665 when debtorModel.LocID is not set)
SELECT LOCATION_SEQ.NEXTVAL
FROM DUAL

INSERT INTO LOCATION (
  LOC_SEQ_NUM,
  POLICY_NUM,
  BUS_SEQ_NUM,
  AGENT_NUM,
  PARENT_LOC_SEQ_NUM,
  LOC_EFF_DATE,
  LOC_EXP_DATE,
  LOCATION_CODE,
  LOCATION_NAME,
  LOC_ADDR1,
  LOC_ADDR2,
  LOC_CITY,
  LOC_COUNTRY,
  LOC_PHONE,
  LOC_PHONE_EXT,
  LOC_FAX,
  LOC_STATE,
  LOC_ZIP,
  LOC_ZIP4,
  SIC_CODE,
  PROVIDER_NUM,
  CREATE_USER,
  CREATE_DATE,
  UPDATE_USER,
  UPDATE_DATE,
  USPS_BARCODE,
  EMAIL_ADDR,
  EMPLOYEE_COUNT,
  LOC_ADDR3,
  PRIMARY_LOCATION_FLAG,
  CELL_PHONE)
VALUES (
  :Id,
  :PolicyNum,
  :BusId,
  :AgentNum,
  :ParentId,
  :EffectiveDate,
  :ExpirationDate,
  :LocationCode,
  :LocationName,
  :AddressLine1,
  :AddressLine2,
  :City,
  :Country,
  :PhoneNumber,
  :PhoneExtension,
  :Fax,
  :State,
  :Zip,
  :Zip4,
  :SicCode,
  :ProviderNum,
  :CreateUser,
  :CreateDate,
  :UpdateUser,
  :UpdateDate,
  :UspsBarcode,
  :EmailAddress,
  :EmployeeCount,
  :AddressLine3,
  :IsPrimaryLocation,
  :CellPhone)

-- Existing location (SaveDebtor :665 when debtorModel.LocID > 0; locData loaded by GetDebtorLocation at line 652)
UPDATE LOCATION
SET
  POLICY_NUM = :PolicyNum,
  BUS_SEQ_NUM = :BusId,
  AGENT_NUM = :AgentNum,
  PARENT_LOC_SEQ_NUM = :ParentId,
  LOC_EFF_DATE = :EffectiveDate,
  LOC_EXP_DATE = :ExpirationDate,
  LOCATION_CODE = :LocationCode,
  LOCATION_NAME = :LocationName,
  LOC_ADDR1 = :AddressLine1,
  LOC_ADDR2 = :AddressLine2,
  LOC_CITY = :City,
  LOC_COUNTRY = :Country,
  LOC_PHONE = :PhoneNumber,
  LOC_PHONE_EXT = :PhoneExtension,
  LOC_FAX = :Fax,
  LOC_STATE = :State,
  LOC_ZIP = :Zip,
  LOC_ZIP4 = :Zip4,
  SIC_CODE = :SicCode,
  PROVIDER_NUM = :ProviderNum,
  CREATE_USER = :CreateUser,
  CREATE_DATE = :CreateDate,
  UPDATE_USER = :UpdateUser,
  UPDATE_DATE = :UpdateDate,
  USPS_BARCODE = :UspsBarcode,
  EMAIL_ADDR = :EmailAddress,
  EMPLOYEE_COUNT = :EmployeeCount,
  LOC_ADDR3 = :AddressLine3,
  PRIMARY_LOCATION_FLAG = :IsPrimaryLocation,
  CELL_PHONE = :CellPhone
WHERE LOC_SEQ_NUM = :Id

Translation notes: - :Id is the value fetched from sequence LOCATION_SEQ (GeneratedBy.Native("LOCATION_SEQ")) before the INSERT; NHibernate assigns it to Location.Id. - :IsPrimaryLocation are bound as 'Y' / 'N' (YesNoType). - Excluded from the INSERT column list by mapping flags: LOC_COUNTY, LOC_STATE (County reference, Not.Insert; LOC_STATE is still written through its own Map). - SaveDebtor (:665) demonstrably handles both cases: locData is a new Location unless debtorModel.LocID > 0, in which case it is loaded by GetDebtorLocation (lines 649-653). Both the INSERT and the SaveOrUpdate UPDATE are therefore shown, each labelled. LocationMap declares no Version, so the UPDATE has no version predicate. - GetDebtorLocation (line 652) is a separate SELECT, not part of this SQL ID.

Operations and processes

• CollectionsService.CreateDebtor — see Operations and processes
• CollectionsService.SaveDebtor — see Operations and processes

Note: Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources

• Services/SAIF.Services/Collections/CollectionsService.cs (L576, L665)