SQL-037: Read COMPANY, COMPANY_PERSON for clsCustomerCollection
This statement supports clsCustomerCollection by executing a read operation against COMPANY, COMPANY_PERSON.
Classification
| Field |
Value |
| SQL ID |
SQL-037 |
| Operation type |
Read |
| Execution method |
Dynamic inline |
| Database |
Oracle |
| Schema |
[unqualified]** |
| Command type |
Text |
| Business object |
clsCustomerCollection |
| Criteria property |
SearchForCustomers |
| Lifecycle method |
SearchForCustomers |
Parameters
| Parameter |
Direction |
Type |
Max length |
Description |
:NameSearchString |
Input |
String |
var |
Formatted customer/contact name search string |
:RefString |
Input |
String |
var |
Formatted internal/external reference search string |
:CompanyCode |
Input |
String |
5 |
Customer type code |
:RelCode |
Input |
String |
5 |
Relationship type filter |
Tables
| Table |
Access |
Columns used |
| COMPANY |
SELECT / JOIN |
ACTIVE_FLAG, COMPANY_CODE, COMPANY_NAME, COMPANY_SEQ_NUM, COMPANY_SHORT_DESCR, CONTACT_NAME, EFF_DT, EXP_DT, EXTERNAL_REFERENCE_ID, INTERNAL_REFERENCE_ID, NOTE_TEXT, PERSON_FLAG, PRIMARY_FLAG, TAX_ID, UPDATE_DATE, UPDATE_USER |
| COMPANY_PERSON |
SELECT / JOIN |
See sample SQL** |
Sample SQL
Select COMPANY.COMPANY_SEQ_NUM as CustomerID, COMPANY.EFF_DT as EffectiveDate, COMPANY.EXP_DT as ExpirationDate, COMPANY.COMPANY_NAME as CustomerName, COMPANY.COMPANY_SHORT_DESCR as CustomerShortDescription, COMPANY.TAX_ID as TaxID, COMPANY.CONTACT_NAME as ContactName, COMPANY.COMPANY_CODE as CustomerTypeCode, COMPANY.ACTIVE_FLAG as IsActive, COMPANY.PRIMARY_FLAG as IsPrimary, COMPANY.PERSON_FLAG as IsPerson, COMPANY.INTERNAL_REFERENCE_ID as InternalReferenceID, COMPANY.EXTERNAL_REFERENCE_ID as ExternalReferenceID, COMPANY.NOTE_TEXT as NoteText, COMPANY.UPDATE_DATE as UpdateDate, COMPANY.UPDATE_USER as UpdateUser From COMPANY WHERE ROWNUM < [runtime CustomerLimit + 1]** AND UPPER(COMPANY.COMPANY_NAME) like :NameSearchString AND (UPPER(COMPANY.INTERNAL_REFERENCE_ID) like :RefString OR UPPER(COMPANY.EXTERNAL_REFERENCE_ID) like :RefString) AND UPPER(COMPANY.COMPANY_CODE) = :CompanyCode AND COMPANY_SEQ_NUM in (SELECT COMPANY_SEQ_NUM from COMPANY_PERSON Where RELATIONSHIP_CODE = :RelCode)
Operations and processes
Business rules
Note: Statements marked with ** could not be confirmed; treat as unknown until verified.
Sources
Business\SAIF.Business.CustomerContact\Customer\clsCustomer.vb