This appendix describes guidelines for PL/SQL packages and functions that derive data point values.
This appendix covers the following topics:
You can define your own data points for inclusion on credit checklists and scoring models for credit analysis. When defining new data points, you can associate a PL/SQL package and function to derive a value for that data point.
When coding the PL/SQL functions, follow these guidelines:
The function cannot have parameters, or commit or rollback commands.
The function must return either a VARCHAR2 or a NULL value.
If a data point returns multiple values, populate the OCM_DP_VALUES_TBL_TYPE table. The value must be Null.
The function should include exceptions handling. For errors, include the error message in the OUT variable. Workflow will use the message to send an appropriate notification to the credit analyst.
The function signature must match the coding examples below.
This is an example of a function that returns a single value:
Package Specifications:
CREATE OR REPLACE PACKAGE ocm_data_points AS FUNCTION get_data_points( x_resultout OUT NOCOPY VARCHAR2, x_errormsg OUT NOCOPY VARCHAR2) return varchar2; END ocm_data_points;
Package Body:
CREATE OR REPLACE PACKAGE BODY ocm_data_points AS FUNCTION get_data_points( x_resultout OUT NOCOPY VARCHAR2, x_errormsg OUT NOCOPY VARCHAR2) RETURN VARCHAR2 AS l_credit_classification varchar2(30); pg_ocm_add_dp_param_rec OCM_ADD_DATA_POINTS.OCM_ADD_DP_PARAM_REC_TYPE; BEGIN x_resultout := FND_API.G_RET_STS_SUCCESS; SELECT credit_classification INTO l_credit_classification FROM hz_customer_profiles WHERE party_id = pg_ocm_add_dp_param_rec.p_party_id; pg_ocm_add_dp_param_rec.P_data_point_value := l_credit_classification; RETURN l_credit_classification; EXCEPTION WHEN NO_DATA_FOUND THEN l_credit_classification := NULL; WHEN OTHERS THEN x_resultout := FND_API.G_RET_STS_UNEXP_ERROR; x_errormsg := sqlerrm; END get_data_points; END ocm_data_points;