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;