1 PACKAGE BODY CSD_PARAMETER_EFFECTIVITY_PVT as
2 /* $Header: csdvpreb.pls 120.2 2011/04/18 10:19:48 subhat noship $ */
3 -- Start of Comments
4 -- Package name : CSD_PARAMETER_EFFECTIVITY_PVT
5 -- Purpose : Mar-04-2011 subhat created
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_PARAMETER_EFFECTIVITY_PVT';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdvpreb.pls';
12 G_LEVEL_PROCEDURE NUMBER := FND_LOG.LEVEL_PROCEDURE;
13 G_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
14
15 G_RET_STS_SUCCESS VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
16 G_RET_STS_ERROR VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
17
18 /****************** private routines ****************************************/
19 FUNCTION is_rule_condition_match(
20 p_parameter_type IN VARCHAR2,
21 p_operator IN VARCHAR2,
22 p_criterion IN VARCHAR2,
23 p_rule_input_rec IN RULE_INPUT_REC_TYPE
24 ) RETURN BOOLEAN;
25
26 FUNCTION is_rule_condition_match(
27 p_parameter_type IN VARCHAR2,
28 p_operator IN VARCHAR2,
29 p_criterion IN VARCHAR2,
30 p_rule_input_rec IN RULE_INPUT_REC_TYPE
31 ) RETURN BOOLEAN
32 IS
33 l_return_value BOOLEAN := FALSE;
34 l_input_criteria VARCHAR2(30);
35
36 BEGIN
37 CASE p_parameter_type
38 WHEN 'RETURNTYPE' THEN l_input_criteria := p_rule_input_rec.RETURN_TYPE;
39 WHEN 'ITEM' THEN l_input_criteria := TO_CHAR(p_rule_input_rec.ITEM_ID);
40 WHEN 'CUSTOMER' THEN l_input_criteria := TO_CHAR(p_rule_input_rec.CUSTOMER_ID);
41 WHEN 'ITEM_CATEGORY' THEN l_input_criteria := TO_CHAR(p_rule_input_rec.ITEM_CATEGORY_ID);
42 WHEN 'CONTRACT' THEN l_input_criteria := TO_CHAR(p_rule_input_rec.CONTRACT_ID);
43 END CASE;
44
45 IF ( p_operator = G_EQUALS AND l_input_criteria = p_criterion) THEN
46 l_return_value := TRUE;
47 ELSIF (p_operator = G_NOT_EQUALS AND l_input_criteria <> p_criterion) THEN
48 l_return_value := TRUE;
49 END IF;
50 RETURN l_return_value;
51 END is_rule_condition_match;
52
53 PROCEDURE PARAMETER_VALUE(
54 p_api_version IN NUMBER,
55 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
56 p_commit IN VARCHAR2 := FND_API.G_FALSE,
57 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
58 p_param_id IN NUMBER,
59 p_rule_input_rec IN RULE_INPUT_REC_TYPE,
60 x_param_value OUT NOCOPY VARCHAR2,
61 x_return_status OUT NOCOPY VARCHAR2,
62 x_msg_count OUT NOCOPY NUMBER,
63 x_msg_data OUT NOCOPY VARCHAR2
64 )
65 IS
66 lc_api_name CONSTANT VARCHAR2(80) := 'CSD_PARAMETER_EFFECTIVITY_PVT.parameter_value';
67 lc_api_version CONSTANT NUMBER := 1.0;
68 l_param_value_tbl PARAM_VALUE_TBL_TYPE;
69 l_always_applicable VARCHAR2(2);
70 l_rule_condition_tbl RULE_CONDITION_TBL_TYPE;
71 l_match BOOLEAN := FALSE;
72
73 BEGIN
74 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
75 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Begin API');
76 END IF;
77
78 -- standard check for API compatibility.
79 IF NOT Fnd_Api.Compatible_API_Call
80 (lc_api_version,
81 p_api_version,
82 lc_api_name,
83 G_PKG_NAME)
84 THEN
85 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
86 END IF;
87
88 IF Fnd_Api.to_Boolean(p_init_msg_list) THEN
89 Fnd_Msg_Pub.initialize;
90 END IF;
91
92 x_return_status := G_RET_STS_SUCCESS;
93
94 -- find out if the param_id passed has a always applicable value.
95 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
96 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Checking for the default value. Step-1. Param_id ='||p_param_id);
97 END IF;
98
99 BEGIN
100 SELECT value,NVL(applicable,'N')
101 INTO x_param_value,l_always_applicable
102 FROM csd_parameter_values
103 WHERE parameter_id = p_param_id;
104
105 IF x_param_value IS NOT NULL AND l_always_applicable = 'Y' THEN
106 RETURN;
107 END IF;
108 EXCEPTION
109 WHEN NO_DATA_FOUND THEN
110 -- do nothing here. Since no_data_found is not an actual error
111 -- here.
112 NULL;
113 END;
114
115 -- we have rules(possibly) defined for the parameter.
116 -- fetch all the rules and their corresponding values.
117 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
118 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Bulk fetching all the rules. Step-2. Param_id ='||p_param_id);
119 END IF;
120
121 SELECT rule_id,value
122 BULK COLLECT INTO l_param_value_tbl
123 FROM csd_return_rules_b
124 WHERE source_type = 'RETURN'
125 AND source_id = p_param_id
126 ORDER BY precedence;
127
128 IF l_param_value_tbl.COUNT = 0 THEN
129 RETURN;
130 END IF;
131
132 -- we will check to see if there are any rule conditions exist for the rules specified.
133 -- this is row by row processing.
134 IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
135 Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Checking for rule conditions');
136 END IF;
137 FOR i IN 1 ..l_param_value_tbl.COUNT
138 LOOP
139 SELECT rule_condition_id,
140 return_rule_id,
141 attribute_category,
142 attribute1,
143 attribute2,
144 attribute3,
145 attribute4,
146 attribute5,
147 attribute6,
148 attribute7,
149 attribute8,
150 attribute9,
151 attribute10,
152 attribute11,
153 attribute12,
154 attribute13,
155 attribute14,
156 attribute15
157 BULK COLLECT INTO l_rule_condition_tbl
158 FROM csd_ret_rule_conditions_b
159 WHERE return_rule_id = l_param_value_tbl(i).rule_id;
160
161 FOR j IN 1 ..l_rule_condition_tbl.COUNT
162 LOOP
163 l_match := is_rule_condition_match(l_rule_condition_tbl(j).attribute_category,
164 l_rule_condition_tbl(j).attribute1,
165 l_rule_condition_tbl(j).attribute2,
166 p_rule_input_rec
167 );
168 IF NOT l_match THEN
169 EXIT;
170 END IF;
171 END LOOP;
172 IF l_match THEN
173 x_param_value := l_param_value_tbl(i).value;
174 RETURN;
175 END IF;
176 END LOOP;
177
178 -- by now we have finished all the rule condition processing. We dont find any matches.
179 -- so for this API, the return value is something which cannot be determined. We return null
180 -- as the value and let the caller interprete it the way they like.
181 IF NOT l_match THEN
182 x_param_value := NULL;
183 END IF;
184
185 END PARAMETER_VALUE;
186 /*--------------------------------------------------------------------*/
187 /* function name: GET_SQL_OPERATOR */
188 /* description : Turns the given operator into the corresponding */
189 /* operator symbol used in a sql query */
190 /* */
191 /* Called from : FUNCTION GET_RULE_SQL_FOR_RO */
192 /* Input Parm : */
193 /* p_operator VARCHAR2 Req */
194 /* */
195 /* */
196 /* Return Val : */
197 /* VARCHAR2 - Operator Lookup code from CSD_RULE_OPERATORS */
198 /* */
199 /*--------------------------------------------------------------------*/
200 FUNCTION GET_SQL_OPERATOR (
201 p_operator IN VARCHAR2
202 ) RETURN VARCHAR2
203 IS
204 BEGIN
205 CASE P_OPERATOR
206 when G_EQUALS then
207 return '=';
208 when G_NOT_EQUALS then
209 return '<>';
210 when G_GREATER_THAN then
211 return '>';
212 when G_LESS_THAN then
213 return '<';
214 else
215 return null;
216 END CASE;
217 END GET_SQL_OPERATOR;
218
219 END CSD_PARAMETER_EFFECTIVITY_PVT; /* package ends here */
[Home] [Help]