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

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:

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;