Additional Data Point PL/SQL Guidelines

This appendix describes guidelines for PL/SQL packages and functions that derive data point values.

This appendix covers the following topics:

Guidelines for Deriving Data Point Values

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:

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;