SQL-038: Retrieve C B P Service

Retrieves c b p service data from Oracle.

Classification

Field	Value
SQL ID	SQL-038
Operation type	Read
Execution method	Inline
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsCBPService
Criteria property	RetrieveSQL
Lifecycle method	BeforeRetrieve

Parameters

Parameter	Direction	Type	Max length	Description
:Code	Input	String	8	Value from MyCriteria.Code
:EffDate	Input	Date	N/A	Value from MyCriteria.EffDate

Tables

Table	Access	Columns used
CBP_SERVICE_CODE	SELECT	See business-object criteria for key columns.
CBP_SERVICE_DESCRIPTION	SELECT	See business-object criteria for key columns.

Sample SQL

SELECT /*+FIRST_ROWS*/ CBP_SERVICE_CODE.CODE as Code, CBP_SERVICE_CODE.BILL_SERVICE_TYPE_CODE as BillServiceTypeCode, CBP_SERVICE_CODE.EFF_DATE as EffDate, CBP_SERVICE_CODE.EXP_DATE as ExpDate, CBP_SERVICE_DESCRIPTION.SERVICE_DESCR_TEXT as ServiceDescrText, CBP_SERVICE_DESCRIPTION.SERVICE_DESCR_EFF_DATE as ServiceDescrEffDate  From CBP_SERVICE_CODE LEFT OUTER JOIN CBP_SERVICE_DESCRIPTION ON (CBP_SERVICE_CODE.code = CBP_SERVICE_DESCRIPTION.BILL_SERVICE_CODE) WHERE CBP_SERVICE_CODE.code = :Code    AND :EffDate BETWEEN CBP_SERVICE_CODE.EFF_DATE AND NVL(CBP_SERVICE_CODE.EXP_DATE, '31-Dec-2999')  ORDER BY CBP_SERVICE_DESCRIPTION.SERVICE_DESCR_EFF_DATE DESC

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

Sources

• Business\SAIF.Business.Provider\clsCBPService.vb