1 PACKAGE BODY OCM_DATA_POINTS_PUB AS
2 /*$Header: ARCMLDPB.pls 120.2 2006/04/03 17:16:18 bsarkar noship $ */
3 /*#
4 * This API is used for entering User Defined Data Points at the time of
5 * Credit Request Submission
6 * @rep:scope public
7 * @rep:doccd 115ocmug.pdf Credit Management API User Notes, Oracle Credit Management User Guide
8 * @rep:product OCM
9 * @rep:lifecycle active
10 * @rep:displayname Get Data Points
11 * @rep:category BUSINESS_ENTITY OCM_GET_DATA_POINTS
12 */
13
14 /*#
15 * Use this procedure to retreive data points based on a checklist.
16 * @rep:scope public
17 * @rep:lifecycle active
18 * @rep:displayname Get Data Points
19 */
20 pg_debug VARCHAR2(1) := nvl(fnd_profile.value('AFLOG_ENABLED'),'N');
21
22 PROCEDURE debug (
23 p_message_name IN VARCHAR2 ) IS
24 BEGIN
25 ar_cmgt_util.debug (p_message_name, 'ar.cmgt.plsql.OCM_DATA_POINTS_PUB' );
26 END;
27
28 PROCEDURE GET_DATA_POINTS (
29 p_api_version IN NUMBER,
30 p_init_msg_list IN VARCHAR2,
31 p_commit IN VARCHAR2,
32 p_validation_level IN VARCHAR2,
33 p_credit_classification IN VARCHAR2,
34 p_review_type IN VARCHAR2,
35 p_data_point_category IN VARCHAR2,
36 p_data_point_sub_category IN VARCHAR2,
37 x_return_status OUT NOCOPY VARCHAR2,
38 x_msg_count OUT NOCOPY NUMBER,
39 x_msg_data OUT NOCOPY VARCHAR2,
40 p_datapoints_tbl OUT NOCOPY data_points_tbl ) IS
41
42 l_status VARCHAR2(2000);
43 l_check_flag VARCHAr2(60);
44 l_check_list_id NUMBER(15);
45 i NUMBER :=1;
46
47 CURSOR getDataPointsC IS
48 SELECT dp.DATA_POINT_ID,
49 dp.DATA_POINT_CODE,
50 dp.DATA_POINT_NAME ,
51 dp.DATA_POINT_CATEGORY ,
52 dp.DESCRIPTION ,
53 dp.SCORABLE_FLAG ,
54 dp.APPLICATION_ID ,
55 dp.PACKAGE_NAME ,
56 dp.FUNCTION_NAME ,
57 dp.PARENT_DATA_POINT_ID ,
58 dp.DATA_POINT_SUB_CATEGORY ,
59 dp.FUNCTION_TYPE ,
60 dp.RETURN_DATA_TYPE ,
61 dp.RETURN_DATE_FORMAT
62 FROM ar_cmgt_data_points_vl dp,
63 ar_cmgt_check_lists chklist,
64 ar_cmgt_check_list_dtls chkdtls
65 WHERE chklist.credit_classification = p_credit_classification
66 AND chklist.review_type = p_review_type
67 AND trunc(sysdate) between trunc(start_date) and nvl(trunc(end_date), trunc(sysdate))
68 AND chklist.check_list_id = chkdtls.check_list_id
69 AND chkdtls.data_point_id = dp.data_point_id
70 AND dp.data_point_category = nvl(p_data_point_category, data_point_category)
71 AND dp.data_point_sub_category = nvl(p_data_point_sub_category, data_point_sub_category);
72
73 BEGIN
74 IF pg_debug = 'Y'
75 THEN
76 debug ( 'GET_DATA_POINTS(+)');
77 debug ( 'Credit Classification =' || p_credit_classification);
78 debug ( 'Review Type =' || p_review_type);
79 debug ( 'Data Point Category =' ||p_data_point_category);
80 debug ( 'Data Point Sub Category =' ||p_data_point_sub_category);
81 END IF;
82 x_return_status := FND_API.G_RET_STS_SUCCESS;
83
84 IF FND_API.to_Boolean( p_init_msg_list )
85 THEN
86 FND_MSG_PUB.initialize;
87 END IF;
88 IF p_data_point_sub_category IS NOT NULL
89 AND p_data_point_category IS NULL
90 THEN
91 IF pg_debug = 'Y'
92 THEN
93 debug ( 'Data Point category IS NULL and data Point Sub Category IS not null');
94 END IF;
95 x_return_status := FND_API.G_RET_STS_ERROR;
96 x_msg_data := 'Data Point category IS NULL and data Point Sub Category IS not null';
97 return;
98 END IF;
99
100
101 IF p_data_point_sub_category IS NOT NULL
102 THEN
103 BEGIN
104 SELECT 'x' INTO l_check_flag
105 FROM ar_lookups
106 WHERE lookup_type = 'OCM_USER_DATA_POINT_CATEGORIES'
107 AND lookup_code = p_data_point_sub_category
108 AND enabled_flag = 'Y'
109 AND trunc(sysdate) between trunc(start_date_active)
110 and nvl(trunc(end_date_active), trunc(sysdate));
111
112 EXCEPTION
113 WHEN NO_DATA_FOUND THEN
114 x_msg_data := 'Invalid Data Point Sub Category';
115 x_return_status := FND_API.G_RET_STS_ERROR;
116 return;
117 WHEN OTHERS THEN
118 x_msg_data := Sqlerrm;
119 x_return_status := FND_API.G_RET_STS_ERROR;
120 return;
121 END;
122 END IF;
123 IF pg_debug = 'Y'
124 THEN
125 debug ( 'Valid data point sub category');
126 END IF;
127 IF p_data_point_category IS NOT NULL
128 THEN
129 BEGIN
130 SELECT 'x' INTO l_check_flag
131 FROM ar_lookups
132 WHERE lookup_type = 'AR_CMGT_DATA_POINT_CATEGORY'
133 AND lookup_code = p_data_point_category
134 AND enabled_flag = 'Y'
135 AND trunc(sysdate) between trunc(start_date_active)
136 and nvl(trunc(end_date_active), trunc(sysdate));
137
138 EXCEPTION
139 WHEN NO_DATA_FOUND THEN
140 x_msg_data := 'Invalid Data Point Category';
141 x_return_status := FND_API.G_RET_STS_ERROR;
142 return;
143 WHEN OTHERS THEN
144 x_msg_data := Sqlerrm;
145 x_return_status := FND_API.G_RET_STS_ERROR;
146 return;
147 END;
148 END IF;
149
150 IF pg_debug = 'Y'
151 THEN
152 debug ( 'Valid data point category');
153 END IF;
154 FOR getDataPointsRec IN getDataPointsC
155 LOOP
156 IF pg_debug = 'Y'
157 THEN
158 debug ( 'data_point_id ' || getDataPointsRec.data_point_id);
159 END IF;
160 p_datapoints_tbl(i).data_point_id := getDataPointsRec.data_point_id;
161 p_datapoints_tbl(i).data_point_name := getDataPointsRec.data_point_name;
162 p_datapoints_tbl(i).data_point_code := getDataPointsRec.data_point_code;
163 p_datapoints_tbl(i).data_point_category := getDataPointsRec.data_point_category;
164 p_datapoints_tbl(i).data_point_sub_category := getDataPointsRec.data_point_sub_category;
165 p_datapoints_tbl(i).description := getDataPointsRec.description;
166 p_datapoints_tbl(i).scorable_flag := getDataPointsRec.scorable_flag;
167 p_datapoints_tbl(i).application_id := getDataPointsRec.application_id;
168 p_datapoints_tbl(i).package_name := getDataPointsRec.package_name;
169 p_datapoints_tbl(i).function_name := getDataPointsRec.function_name;
170 p_datapoints_tbl(i).parent_data_point_id := getDataPointsRec.parent_data_point_id;
171 p_datapoints_tbl(i).function_type := getDataPointsRec.function_type;
172 p_datapoints_tbl(i).return_data_type := getDataPointsRec.return_data_type;
173 p_datapoints_tbl(i).return_date_format := getDataPointsRec.return_date_format;
174 i := i + 1;
175 END LOOP;
176
177 END;
178 END OCM_DATA_POINTS_PUB;
[Home] [Help]