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:
CREATE OR REPLACE PACKAGE ocm_data_points AS FUNCTION get_data_points( x_resultout OUT NOCOPY VARCHAR2, x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 AS BEGIN x_resultout := FND_API.G_RET_STS_SUCCESS; BEGIN SELECT credit_classification INTO l_credit_classification FROM hz_customer_profile WHERE party_id = OCM_ADD_DP_PARAM_REC_TYPE.p_party_id; 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; OCM_ADD_DATA_PARAM_REC_TYPE.P_data_point_value := l_credit_classification; RETURN l_credit_classification; END;
This is an example of a function that populates a global PL/SQL table. When the source product populates the PL/SQL table, then the source product should return a NULL value to indicate to Credit Management that the data is contained in the PL/SQL table.
CREATE OR REPLACE PACKAGE ocm_data_points AS FUNCTION get_data_points( x_resultout OUT NOCOPY VARCHAR2, x_errormsg OUT NOCOPY VARCHAR2 ) RETURN VARCHAR2 AS CURSOR cPaymetTerm IS SELECT TRX_NUMBER FROM RA_CUSTOMER_TRX_ALL Where bill_to_customer_id = OCM_ADD_DP_PARAM_REC_TYPE.P_cust_account_id; L_seq_number NUMBER :=1; BEGIN x_resultout := FND_API.G_RET_STS_SUCCESS; FOR cPaymetTermRec IN cPaymetTerm LOOP OCM_DP_VALUES_TBL.P_data_point_id := OCM_ADD_DP_PARAM_REC_TYPE.P_data_point_id; OCM_DP_VALUES_TBL.P_parent_data_point_id := OCM_ADD_DP_PARAM_REC_TYPE.P_parent_data_point_id; OCM_DP_VALUES_TBL.P_sequence_number := L_seq_number; OCM_DP_VALUES_TBL.P_data_point_value := CPaymetTermRec.trx_number; L_seq_number := L_seq_number + 1; END LOOP; RETURN NULL; END;