SQL-055: Retrieve Provider Relationship

Retrieves provider relationship data from Oracle.

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:ProviderLocationSeqNum	Input	Decimal	N/A	Value from MyCriteria.ProviderLocationSeqNum

Tables

Table	Access	Columns used
PROVIDER_RELATIONSHIP	SELECT	See business-object criteria for key columns.
PROVIDER_LOCATION	SELECT	See business-object criteria for key columns.

Sample SQL

Select  PROVIDER_RELATIONSHIP.PROVIDER_LOCATION_SEQ_NUM as ProviderLocationSeqNum,  PROVIDER_RELATIONSHIP.PARENT_PROVIDER_LOC_SEQ_NUM as ParentProviderLocSeqNum,  trim(PROVIDER_RELATIONSHIP.PROVIDER_RELATIONSHIP_CODE) as ProviderRelationshipCode,  PROVIDER_RELATIONSHIP.EFF_DATE as EffDate,  PROVIDER_RELATIONSHIP.EXP_DATE as ExpDate,  PROVIDER_RELATIONSHIP.UPDATE_USER as UpdateUser,  PROVIDER_RELATIONSHIP.UPDATE_NUM as UpdateNum,  PROVIDER_RELATIONSHIP.UPDATE_DATE as UpdateDate,  DECODE(RTRIM(PL1.BUSINESS_FLAG), 'N', NVL(RTRIM(pl1.LAST_NAME), '') || ', ' || NVL(RTRIM(pl1.FIRST_NAME), '') || ' ' || NVL(RTRIM(pl1.MIDDLE_NAME), ''),         RTRIM(PL1.BUSINESS_NAME)) AS FullNameParent,  DECODE(RTRIM(PL2.BUSINESS_FLAG), 'N', NVL(RTRIM(pl2.LAST_NAME), '') || ', ' || NVL(RTRIM(pl2.FIRST_NAME), '') || ' ' || NVL(RTRIM(pl2.MIDDLE_NAME), ''),         RTRIM(PL2.BUSINESS_NAME)) AS FullNameChild  From  PROVIDER_RELATIONSHIP  LEFT OUTER JOIN provider_location PL1 ON (PROVIDER_RELATIONSHIP.PARENT_PROVIDER_LOC_SEQ_NUM = PL1.PROVIDER_LOCATION_SEQ_NUM)  LEFT OUTER JOIN provider_location PL2 ON (PROVIDER_RELATIONSHIP.PROVIDER_LOCATION_SEQ_NUM = PL2.PROVIDER_LOCATION_SEQ_NUM)  Where  PROVIDER_RELATIONSHIP.PROVIDER_LOCATION_SEQ_NUM = :ProviderLocationSeqNum  AND  PROVIDER_RELATIONSHIP.PARENT_PROVIDER_LOC_SEQ_NUM = :ParentProviderLocSeqNum  AND  PROVIDER_RELATIONSHIP.EFF_DATE = :EffDate

Operations and processes

• Maintain provider relationship

Business rules

• BR-013: Relationship participants must exist and be distinct
• BR-014: Relationship dates must be valid and non-overlapping

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

Sources

• Business\SAIF.Business.Provider\clsProviderRelationship.vb