1 PACKAGE BODY OCM_DATA_POINTS_PUB AS
2 /*$Header: ARCMLDPB.pls 120.4 2011/04/20 22:57:16 rravikir ship $ */
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 sysdate between start_date and nvl(end_date, 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 ORDER BY dp.DATA_POINT_NAME; -- Fix for bug 12359921
73
74 BEGIN
75 IF pg_debug = 'Y'
76 THEN
77 debug ( 'GET_DATA_POINTS(+)');
78 debug ( 'Credit Classification =' || p_credit_classification);
79 debug ( 'Review Type =' || p_review_type);
80 debug ( 'Data Point Category =' ||p_data_point_category);
81 debug ( 'Data Point Sub Category =' ||p_data_point_sub_category);
82 END IF;
83 x_return_status := FND_API.G_RET_STS_SUCCESS;
84
85 IF FND_API.to_Boolean( p_init_msg_list )
86 THEN
87 FND_MSG_PUB.initialize;
88 END IF;
89 IF p_data_point_sub_category IS NOT NULL
90 AND p_data_point_category IS NULL
91 THEN
92 IF pg_debug = 'Y'
93 THEN
94 debug ( 'Data Point category IS NULL and data Point Sub Category IS not null');
95 END IF;
96 x_return_status := FND_API.G_RET_STS_ERROR;
97 x_msg_data := 'Data Point category IS NULL and data Point Sub Category IS not null';
98 return;
99 END IF;
100
101
102 IF p_data_point_sub_category IS NOT NULL
103 THEN
104 BEGIN
105 SELECT 'x' INTO l_check_flag
106 FROM ar_lookups
107 WHERE lookup_type = 'OCM_USER_DATA_POINT_CATEGORIES'
108 AND lookup_code = p_data_point_sub_category
109 AND enabled_flag = 'Y'
110 AND trunc(sysdate) between trunc(start_date_active)
111 and nvl(trunc(end_date_active), trunc(sysdate));
112
113 EXCEPTION
114 WHEN NO_DATA_FOUND THEN
115 x_msg_data := 'Invalid Data Point Sub Category';
116 x_return_status := FND_API.G_RET_STS_ERROR;
117 return;
118 WHEN OTHERS THEN
119 x_msg_data := Sqlerrm;
120 x_return_status := FND_API.G_RET_STS_ERROR;
121 return;
122 END;
123 END IF;
124 IF pg_debug = 'Y'
125 THEN
126 debug ( 'Valid data point sub category');
127 END IF;
128 IF p_data_point_category IS NOT NULL
129 THEN
130 BEGIN
131 SELECT 'x' INTO l_check_flag
132 FROM ar_lookups
133 WHERE lookup_type = 'AR_CMGT_DATA_POINT_CATEGORY'
134 AND lookup_code = p_data_point_category
135 AND enabled_flag = 'Y'
136 AND trunc(sysdate) between trunc(start_date_active)
137 and nvl(trunc(end_date_active), trunc(sysdate));
138
139 EXCEPTION
140 WHEN NO_DATA_FOUND THEN
141 x_msg_data := 'Invalid Data Point Category';
142 x_return_status := FND_API.G_RET_STS_ERROR;
143 return;
144 WHEN OTHERS THEN
145 x_msg_data := Sqlerrm;
146 x_return_status := FND_API.G_RET_STS_ERROR;
147 return;
148 END;
149 END IF;
150
151 IF pg_debug = 'Y'
152 THEN
153 debug ( 'Valid data point category');
154 END IF;
155 FOR getDataPointsRec IN getDataPointsC
156 LOOP
157 IF pg_debug = 'Y'
158 THEN
159 debug ( 'data_point_id ' || getDataPointsRec.data_point_id);
160 END IF;
161 p_datapoints_tbl(i).data_point_id := getDataPointsRec.data_point_id;
162 p_datapoints_tbl(i).data_point_name := getDataPointsRec.data_point_name;
163 p_datapoints_tbl(i).data_point_code := getDataPointsRec.data_point_code;
164 p_datapoints_tbl(i).data_point_category := getDataPointsRec.data_point_category;
165 p_datapoints_tbl(i).data_point_sub_category := getDataPointsRec.data_point_sub_category;
166 p_datapoints_tbl(i).description := getDataPointsRec.description;
167 p_datapoints_tbl(i).scorable_flag := getDataPointsRec.scorable_flag;
168 p_datapoints_tbl(i).application_id := getDataPointsRec.application_id;
169 p_datapoints_tbl(i).package_name := getDataPointsRec.package_name;
170 p_datapoints_tbl(i).function_name := getDataPointsRec.function_name;
171 p_datapoints_tbl(i).parent_data_point_id := getDataPointsRec.parent_data_point_id;
172 p_datapoints_tbl(i).function_type := getDataPointsRec.function_type;
173 p_datapoints_tbl(i).return_data_type := getDataPointsRec.return_data_type;
174 p_datapoints_tbl(i).return_date_format := getDataPointsRec.return_date_format;
175 i := i + 1;
176 END LOOP;
177
178 END;
179 END OCM_DATA_POINTS_PUB;
[Home] [Help]