Skip Headers

Oracle Credit Management User Guide
Release 12.1
Part Number E13502-04
Go to Table of Contents
Contents
Go to previous page
Previous
Go to next page
Next

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:

	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;