SQL-038: Criteria¶

This document records the SQL used by Criteria in SAIF.Business.Claim.Bill.

Classification¶

Field	Value
SQL ID	SQL-038
Operation type	Update
Execution method	Inline
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	`Criteria`
Criteria property	`UpdateSQL`
Lifecycle method	`BeforeUpdate`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:PrescribingProviderLocationID`	Input	Number	N/A	Binds PrescribingProviderLocationID from active calling code or Oracle signature.
`:ServicingProviderLocationID`	Input	Number	N/A	Binds ServicingProviderLocationID from active calling code or Oracle signature.
`:ServiceDate`	Input	Date	N/A	Binds ServiceDate from active calling code or Oracle signature.
`:ServiceEndDate`	Input	Date	N/A	Binds ServiceEndDate from active calling code or Oracle signature.
`:ProviderServiceCode`	Input	Number	N/A	Binds ProviderServiceCode from active calling code or Oracle signature.
`:ServiceModifierCode1`	Input	String	N/A	Binds ServiceModifierCode1 from active calling code or Oracle signature.
`:ServiceModifierCode2`	Input	String	N/A	Binds ServiceModifierCode2 from active calling code or Oracle signature.
`:ServiceModifierCode3`	Input	String	N/A	Binds ServiceModifierCode3 from active calling code or Oracle signature.
`:ServiceModifierCode4`	Input	String	N/A	Binds ServiceModifierCode4 from active calling code or Oracle signature.
`:ServiceQuantity`	Input	String	N/A	Binds ServiceQuantity from active calling code or Oracle signature.
`:ChargeAmount`	Input	Number	N/A	Binds ChargeAmount from active calling code or Oracle signature.
`:MsrpInvoiceAmount`	Input	Number	N/A	Binds MsrpInvoiceAmount from active calling code or Oracle signature.
`:MsrpInvoiceCode`	Input	String	N/A	Binds MsrpInvoiceCode from active calling code or Oracle signature.
`:ToothNumber`	Input	Number	N/A	Binds ToothNumber from active calling code or Oracle signature.
`:ServiceLocationCode`	Input	String	N/A	Binds ServiceLocationCode from active calling code or Oracle signature.
`:DaysSupplyQuantity`	Input	Number	N/A	Binds DaysSupplyQuantity from active calling code or Oracle signature.
`:ServiceTypeCode`	Input	String	N/A	Binds ServiceTypeCode from active calling code or Oracle signature.
`:UpdateUser`	Input	Date	N/A	Binds UpdateUser from active calling code or Oracle signature.
`:UpdateDate`	Input	Date	N/A	Binds UpdateDate from active calling code or Oracle signature.
`:DispenseASWrittenCode`	Input	String	N/A	Binds DispenseASWrittenCode from active calling code or Oracle signature.
`:SecondaryProviderServiceCode`	Input	Number	N/A	Binds SecondaryProviderServiceCode from active calling code or Oracle signature.
`:DiagnosisPointerCode1`	Input	String	N/A	Binds DiagnosisPointerCode1 from active calling code or Oracle signature.
`:DiagnosisPointerCode2`	Input	String	N/A	Binds DiagnosisPointerCode2 from active calling code or Oracle signature.
`:DiagnosisPointerCode3`	Input	String	N/A	Binds DiagnosisPointerCode3 from active calling code or Oracle signature.
`:DiagnosisPointerCode4`	Input	String	N/A	Binds DiagnosisPointerCode4 from active calling code or Oracle signature.
`:SecondaryServiceTypeCode`	Input	String	N/A	Binds SecondaryServiceTypeCode from active calling code or Oracle signature.
`:CompoundDrugFlag`	Input	String	N/A	Binds CompoundDrugFlag from active calling code or Oracle signature.
`:TaxChargedAmount`	Input	Number	N/A	Binds TaxChargedAmount from active calling code or Oracle signature.
`:PrescribingProviderID`	Input	Number	N/A	Binds PrescribingProviderID from active calling code or Oracle signature.
`:PrescribingProviderCode`	Input	Number	N/A	Binds PrescribingProviderCode from active calling code or Oracle signature.
`:PrescriberLastName`	Input	String	N/A	Binds PrescriberLastName from active calling code or Oracle signature.
`:PrescriberFirstName`	Input	String	N/A	Binds PrescriberFirstName from active calling code or Oracle signature.
`:PrescriptionWrittenDate`	Input	Date	N/A	Binds PrescriptionWrittenDate from active calling code or Oracle signature.
`:BillID`	Input	Number	N/A	Binds BillID from active calling code or Oracle signature.
`:ServiceID`	Input	Number	N/A	Binds ServiceID from active calling code or Oracle signature.
`:UpdateNumber`	Input	Date	N/A	Binds UpdateNumber from active calling code or Oracle signature.

Tables¶

Table	Access	Columns used
CBP_BILL_SERVICE	UPDATE	A, AND, ASW, B, BILL_ID, BILL_SERVICE_DATE, BILL_SERVICE_END_DATE, BILL_SERVICE_SEQ_NUM, BILL_SERVICE_TYPE_CODE, C, CBP_BILL_SERVICE, CHARGE_AMT

Sample SQL¶

Update CBP_BILL_SERVICE 
SET PRESC_PROVIDER_LOC_SEQ_NUM = :PrescribingProviderLocationID, SERV_PROVIDER_LOC_SEQ_NUM = :ServicingProviderLocationID, BILL_SERVICE_DATE = :ServiceDate, BILL_SERVICE_END_DATE = :ServiceEndDate, PROVIDER_SERVICE_CODE = :ProviderServiceCode, SERVICE_MODIFIER_CODE1 = :ServiceModifierCode1, SERVICE_MODIFIER_CODE2 = :ServiceModifierCode2, SERVICE_MODIFIER_CODE3 = :ServiceModifierCode3, SERVICE_MODIFIER_CODE4 = :ServiceModifierCode4, SERVICE_QUANTITY = :ServiceQuantity, CHARGE_AMT = :ChargeAmount, MSRP_INVOICE_AMT = :MsrpInvoiceAmount, MSRP_INVOICE_CODE = :MsrpInvoiceCode, TOOTH_NUM = :ToothNumber, SERVICE_LOCATION_CODE = :ServiceLocationCode, DAYS_SUPPLY_QTY = :DaysSupplyQuantity, BILL_SERVICE_TYPE_CODE = :ServiceTypeCode, UPDATE_USER = :UpdateUser, UPDATE_NUM = UPDATE_NUM + 1, UPDATE_DATE = :UpdateDate, DISPENSE_AS_WRITTEN_CODE = :DispenseASWrittenCode, SECONDARY_PROV_SERVICE_CODE = :SecondaryProviderServiceCode, DIAGNOSIS_POINTER_CODE1 = :DiagnosisPointerCode1, DIAGNOSIS_POINTER_CODE2 = :DiagnosisPointerCode2, DIAGNOSIS_POINTER_CODE3 = :DiagnosisPointerCode3, DIAGNOSIS_POINTER_CODE4 = :DiagnosisPointerCode4, SECONDARY_BILL_SERV_TYPE_CODE = :SecondaryServiceTypeCode, COMPOUND_DRUG_FLAG = :CompoundDrugFlag, TAX_CHARGED_AMT = :TaxChargedAmount, PRESCRIBING_PROVIDER_ID = :PrescribingProviderID, PRESCRIBING_PROVIDER_CODE = :PrescribingProviderCode, PRESCRIBER_LAST_NAME = :PrescriberLastName, PRESCRIBER_FIRST_NAME = :PrescriberFirstName, PRESCRIPTION_WRITTEN_DATE = :PrescriptionWrittenDate 
WHERE BILL_ID = :BillID 
  AND BILL_SERVICE_SEQ_NUM = :ServiceID 
  AND UPDATE_NUM = :UpdateNumber

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.Claim.Bill\Core\clsBillService.vb