[Home] [Help]
PACKAGE BODY: APPS.CSD_RULES_ENGINE_PVT
Source
1 PACKAGE BODY CSD_RULES_ENGINE_PVT as
2 /* $Header: csdvrulb.pls 120.7.12010000.6 2008/11/11 01:46:12 swai ship $ */
3 -- Start of Comments
4 -- Package name : CSD_RULES_ENGINE_PVT
5 -- Purpose : Jan-14-2008 rfieldma created
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSD_RULES_ENGINE_PVT';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csdvrulb.pls';
12
13
14 /*--------------------------------------------------------------------*/
15 /* procedure name: PROCESS_RULE_MATCHING */
16 /* description : procedure used to Match Rules with input data */
17 /* */
18 /* */
19 /* */
20 /* Called from : Depot Repair Bulletins */
21 /* Input Parm : */
22 /* p_api_version NUMBER Req Api Version number */
23 /* p_init_msg_list VARCHAR2 Opt Initialize message stack */
24 /* p_commit VARCHAR2 Opt Commits in API */
25 /* p_validation_level NUMBER Opt validation steps */
26 /* px_rule_matching_rec CSD_RULE_MATCHING_REC_TYPE */
27 /* Output Parm : */
28 /* x_return_status VARCHAR2 Return status after the call. */
29 /* x_msg_count NUMBER Number of messages in stack */
30 /* x_msg_data VARCHAR2 Mesg. text if x_msg_count >= 1 */
31 /* Change Hist : Jan-14-08 rfieldma created */
32 /* */
33 /* */
34 /* */
35 /*--------------------------------------------------------------------*/
36 PROCEDURE PROCESS_RULE_MATCHING(
37 p_api_version_number IN NUMBER,
38 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
39 p_commit IN VARCHAR2 := FND_API.G_FALSE,
40 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
41 px_rule_matching_rec IN OUT NOCOPY CSD_RULE_MATCHING_REC_TYPE,
42 x_return_status OUT NOCOPY VARCHAR2,
43 x_msg_count OUT NOCOPY NUMBER,
44 x_msg_data OUT NOCOPY VARCHAR2
45 )
46 IS
47 ---- local constants ----
48 c_TEMP_CHAR CONSTANT VARCHAR(1) := 'B'; -- used to pass as attribute type/code for bulletin
49 c_API_NAME CONSTANT VARCHAR2(30) := 'PROCESS_RULE_MATCHING';
50 c_API_VERSION_NUMBER CONSTANT NUMBER := G_L_API_VERSION_NUMBER;
51
52 ---- local variables ----
53 l_rule_type VARCHAR2(30) := NULL;
54 l_attr_type VARCHAR2(30) := NULL;
55 l_attr_code VARCHAR2(30) := NULL;
56 l_match_condition_ret_val VARCHAR2(1) := FND_API.G_FALSE;
57 l_is_match VARCHAR2(1) := FND_API.G_FALSE;
58 l_tbl_last_ind NUMBER := 1;
59 l_repair_line_id NUMBER := NULL;
60
61 ---- cursors ----
62 --* Cursor: cur_get_rules *--
63 --* return rules that match rule_type, attr_type, attr_code *--
64 CURSOR cur_get_rules(p_rule_type VARCHAR2,
65 p_attr_type VARCHAR2,
66 p_attr_code VARCHAR2) IS
67 SELECT attribute1, rule_id, value_type_code, attribute_category
68 FROM csd_rules_b
69 where rule_type_code = p_rule_type
70 AND NVL(entity_attribute_type, c_TEMP_CHAR)=NVL(p_attr_type, c_TEMP_CHAR)
71 AND NVL(entity_attribute_code, c_TEMP_CHAR)=NVL(p_attr_code, c_TEMP_CHAR)
72 ORDER BY precedence
73 ; --* end CURSOR cur_get_rules(..) *--
74
75 l_rule_rec cur_get_rules%ROWTYPE;
76
77 --* Cursor: cur_get_rule_conditions *--
78 --* return all conditions for a given rule *--
79 CURSOR cur_get_rule_conditions(p_rule_id NUMBER) IS
80 SELECT attribute_category,
81 attribute1,
82 attribute2
83 FROM csd_rule_conditions_b
84 WHERE rule_id = p_rule_id
85 ; --* end CURSOR cur_get_rule_conditions(..) *--
86
87 l_rule_cond_rec cur_get_rule_conditions%ROWTYPE;
88
89 BEGIN
90 --* Standard Start of API savepoint
91 SAVEPOINT PROCESS_RULE_MATCHING_PVT;
92
93 --* Standard call to check for call compatibility.
94 IF NOT FND_API.Compatible_API_Call ( c_API_VERSION_NUMBER,
95 p_api_version_number,
96 c_API_NAME,
97 G_PKG_NAME)
98 THEN
99 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
100 END IF;
101
102 -- Initialize message list if p_init_msg_list is set to TRUE.
103 IF FND_API.to_Boolean( p_init_msg_list )
104 THEN
105 FND_MSG_PUB.initialize;
106 END IF;
107
108 -- Initialize API return status to SUCCESS
109 x_return_status := FND_API.G_RET_STS_SUCCESS;
110
111 --* logic starts here *--
112
113 --* populate p_rule_input_rec
114 l_repair_line_id := PX_RULE_MATCHING_REC.RULE_INPUT_REC.repair_line_id;
115 IF ( l_repair_line_id IS NOT NULL) THEN
116 POPULATE_RULE_INPUT_REC(PX_RULE_MATCHING_REC.RULE_INPUT_REC, l_repair_line_id);
117 END IF; --* end IF ( l_repair_line_id IS NULL) *--
118
119
120 /* BEGIN: Algorithm:
121 * (1) Query for all ACTIVE rules that match:
122 * px_rule_matching_rec.ENTITY_ATTRIBUTE_CODE
123 * and px_rule_matching_rec.ENTITY_ATTRIBUTE_TYPE
124 * and px_rule_matching_rec.RULE_TYPE
125 * order by PRECEDENCE (ascending).
126 * (2) For each rule:
127 * (a) Query the list of the conditions for the rule
128 * (b) l_is_match := 'T' -- assume the condition is match unless proven otherwise.
129 * (c) for each condition,
130 * if (match_condition ( p_parameter_type => attribute_category,
131 * p_operator => attribute1,
132 * p_criterion => attribute2,
133 * p_rule_input_rec => px_rule_matching_rec.RULE_INPUT_REC
134 * ) = G_MISS_G_FALSE )then
135 * l_is_match := 'F'
136 * break; --condition doesn't match, so rule doesn't match.
137 * end if;
138 * end for loop (looping through conditions for a single rule)
139 * (d) if (l_is_match = 'T') then
140 * if (px_csd_rule_matching_rec_type = 1) then
141 * -- we only need to find the first match, so we can exit out of the loop.
142 * px_csd_rule_matching_rec_type.RULE_RESULTS_TBL(1).rule_id = current rule
143 * px_csd_rule_matching_rec_type.RULE_RESULTS_TBL(1).defaulting_value = current rule's attribute1
144 * px_csd_rule_matching_rec_type.RULE_RESULTS_TBL(1).value_type = current rule's value_type_code
145 * break;
146 * end if;
147 * -- if not a match, keep looping through the rules for a match.
148 * end for loop (looping through rules for given defaulting attribute)
149 * END: Algorithm*/
150 l_rule_type := px_rule_matching_rec.RULE_TYPE;
151 l_attr_type := px_rule_matching_rec.ENTITY_ATTRIBUTE_TYPE;
152 l_attr_code := px_rule_matching_rec.ENTITY_ATTRIBUTE_CODE;
153 FOR l_rule_rec IN cur_get_rules(l_rule_type, l_attr_type, l_attr_code) LOOP
154 --** debug starts!!
155 --dbms_output.put_line('PROCESS_RULE_MATCHING - LP - get_rules - rule id = ' || l_rule_rec.rule_id || '<-----');
156 --** debug ends!!
157 --* default to false, assume not match unless otherwise returned by match_condition
158 l_is_match := FND_API.G_FALSE;
159 FOR l_rule_cond_rec IN cur_get_rule_conditions(l_rule_rec.rule_id) LOOP
160 --** debug starts!!
161 --dbms_output.put_line('> PROCESS_RULE_MATCHING - LP - get_rule_cond - attr_cat = ' || l_rule_cond_rec.attribute_category);
162 --dbms_output.put_line('> PROCESS_RULE_MATCHING - LP - get_rule_cond - attr1 = ' || l_rule_cond_rec.attribute1);
163 --dbms_output.put_line('> PROCESS_RULE_MATCHING - LP - get_rule_cond - attr2 = ' || l_rule_cond_rec.attribute2);
164 --** debug ends!!
165
166 l_is_match := match_condition(p_parameter_type => l_rule_cond_rec.attribute_category,
167 p_operator => l_rule_cond_rec.attribute1,
168 p_criterion => l_rule_cond_rec.attribute2,
169 p_rule_input_rec => px_rule_matching_rec.RULE_INPUT_REC
170 );
171
172
173 IF (l_is_match = FND_API.G_FALSE )THEN
174 --** debug starts!!
175 --dbms_output.put_line('PROCESS_RULE_MATCHING -> l_match_condition_ret_val = FALSE - l_is_match = ' || l_is_match);
176 --** debug ends!!
177
178 EXIT; --* a condition didn't match, so no need to go on
179 END IF; --* end IF (l_match_condition_ret_val = FND_API.G_FALSE ) *--
180
181 END LOOP; --* end FOR l_rule_cond_rec... *--
182
183 IF (l_is_match = FND_API.G_TRUE) THEN
184 -- we only need to find the first match, so we can exit out of the loop.
185 l_tbl_last_ind := px_rule_matching_rec.RULE_RESULTS_TBL.COUNT;
186 l_tbl_last_ind := l_tbl_last_ind+1;
187 --** debug starts!!
188 --dbms_output.put_line('PROCESS_RULE_MATCHING -> l_is_match is true, table_count ' || px_rule_matching_rec.RULE_RESULTS_TBL.COUNT);
189 --dbms_output.put_line('PROCESS_RULE_MATCHING -> l_is_match is true, table_ind ' || l_tbl_last_ind);
190 --** debug ends!!
191
192 px_rule_matching_rec.RULE_RESULTS_TBL(l_tbl_last_ind).rule_id := l_rule_rec.rule_id;
193 px_rule_matching_rec.RULE_RESULTS_TBL(l_tbl_last_ind).defaulting_value := l_rule_rec.attribute1;
194 px_rule_matching_rec.RULE_RESULTS_TBL(l_tbl_last_ind).value_type := l_rule_rec.value_type_code;
195
196 IF (px_rule_matching_rec.RULE_MATCH_CODE = G_RULE_MATCH_ONE) THEN
197 EXIT;
198 END IF; --* end I(px_rule_matching_rec.RULE_MATCH_CODE = G_RULE_MATCH_ONE) *--
199 END IF; --* end IF (l_is_match = FND_API.G_TRUE) *--
200 --** debug starts!!
201 --dbms_output.put_line('PROCESS_RULE_MATCHING -> before exiting rules loop');
202 --** debug ends!!
203
204 END LOOP; --* end FOR l_rule_rec ... *--
205
206 --* logic ends here *--
207
208 --* Standard check for p_commit
209 IF FND_API.to_Boolean( p_commit ) THEN
210 COMMIT WORK;
211 END IF;
212
213 --** debug starts!!
214 --dbms_output.put_line('PROCESS_RULE_MATCHING -> after commit work');
215 --** debug ends!!
216
217 --* Standard call to get message count and if count is 1, get message info.
218 FND_MSG_PUB.Count_And_Get(
219 p_count => x_msg_count,
220 p_data => x_msg_data
221 );
222
223 --** debug starts!!
224 --dbms_output.put_line('PROCESS_RULE_MATCHING -> after standard call to message count');
225 --** debug ends!!
226
227 EXCEPTION
228 WHEN FND_API.G_EXC_ERROR THEN
229 x_return_status := FND_API.G_RET_STS_ERROR;
230 --** debug starts!!
231 --dbms_output.put_line('PROCESS_RULE_MATCHING -> exception 1');
232 --** debug ends!!
233
234 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
235 P_API_NAME => c_API_NAME
236 ,P_PKG_NAME => G_PKG_NAME
237 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
238 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
239 ,X_MSG_COUNT => X_MSG_COUNT
240 ,X_MSG_DATA => X_MSG_DATA
241 ,X_RETURN_STATUS => X_RETURN_STATUS);
242
243 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
244 x_return_status := FND_API.G_RET_STS_ERROR;
245 --** debug starts!!
246 --dbms_output.put_line('PROCESS_RULE_MATCHING -> exception 2');
247 --** debug ends!!
248
249 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
250 P_API_NAME => c_API_NAME
251 ,P_PKG_NAME => G_PKG_NAME
252 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
253 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
254 ,X_MSG_COUNT => X_MSG_COUNT
255 ,X_MSG_DATA => X_MSG_DATA
256 ,X_RETURN_STATUS => X_RETURN_STATUS);
257
258 WHEN OTHERS THEN
259 x_return_status := FND_API.G_RET_STS_ERROR;
260 --** debug starts!!
261 --dbms_output.put_line('PROCESS_RULE_MATCHING -> exception 3');
262 --** debug ends!!
263
264 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
265 P_API_NAME => c_API_NAME
266 ,P_PKG_NAME => G_PKG_NAME
267 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
268 ,P_SQLCODE => SQLCODE
269 ,P_SQLERRM => SQLERRM
270 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
271 ,X_MSG_COUNT => X_MSG_COUNT
272 ,X_MSG_DATA => X_MSG_DATA
273 ,X_RETURN_STATUS => X_RETURN_STATUS);
274 END PROCESS_RULE_MATCHING;
275
276
277 /*--------------------------------------------------------------------*/
278 /* procedure name: GET_DEFAULT_VALUE_FROM_RULE (overloaded) */
279 /* description : procedure used to get default values from rules */
280 /* default value = VARCHAR2 data type */
281 /* */
282 /* */
283 /* Called from : Depot Repair Workbench defaulting */
284 /* Input Parm : */
285 /* p_api_version NUMBER Req Api Version number */
286 /* p_init_msg_list VARCHAR2 Opt Initialize message stack */
287 /* p_commit VARCHAR2 Opt Commits in API */
288 /* p_validation_level NUMBER Opt validation steps */
289 /* p_entity_attribute_type VARCHAR2 Req */
290 /* p_entity_attribute_code VARCHAR2 Req */
291 /* p_rule_input_rec CSD_RULE_INPUT_REC_TYPE Req */
292 /* Output Parm : */
293 /* x_return_status VARCHAR2 Return status after the call. */
294 /* x_msg_count NUMBER Number of messages in stack */
295 /* x_msg_data VARCHAR2 Mesg. text if x_msg_count >= 1 */
296 /* x_default_value VARCHAR2 */
297 /* x_rule_id NUMBER Rule ID that determined value */
298 /* if null, then no rule used */
299 /* Change Hist : Jan-14-08 rfieldma created */
300 /* Aug-20-08 swai added param x_rule_id */
301 /* */
302 /* */
303 /*--------------------------------------------------------------------*/
304 PROCEDURE GET_DEFAULT_VALUE_FROM_RULE (
305 p_api_version_number IN NUMBER,
306 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
307 p_commit IN VARCHAR2 := FND_API.G_FALSE,
308 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
309 p_entity_attribute_type IN VARCHAR2,
310 p_entity_attribute_code IN VARCHAR2,
311 p_rule_input_rec IN CSD_RULE_INPUT_REC_TYPE,
312 x_default_value OUT NOCOPY VARCHAR2,
313 x_rule_id OUT NOCOPY NUMBER, -- swai: 12.1.1 ER 7233924
314 x_return_status OUT NOCOPY VARCHAR2,
315 x_msg_count OUT NOCOPY NUMBER,
316 x_msg_data OUT NOCOPY VARCHAR2
317 )
318 IS
319 ---- local constants ----
320 c_API_NAME CONSTANT VARCHAR2(30) := 'GET_DEFAULT_VALUE_FROM_RULE';
321 c_API_VERSION_NUMBER CONSTANT NUMBER := G_L_API_VERSION_NUMBER;
322
323 ---- local variables ----
324 l_rule_matching_rec CSD_RULE_MATCHING_REC_TYPE;
325 l_default_val_str VARCHAR2(150) := NULL;
326 l_tbl_count NUMBER := 0;
327 l_value_type VARCHAR(30) := NULL;
328 l_defaulting_value VARCHAR(150) := NULL;
329 l_default_rule_id NUMBER := NULL; -- swai: added for 12.1.1
330 BEGIN
331 --* Standard Start of API savepoint
332 SAVEPOINT GET_DFLT_VAL_FROM_RULE_PVT;
333
334 --* Standard call to check for call compatibility.
335 IF NOT FND_API.Compatible_API_Call ( c_API_VERSION_NUMBER,
336 p_api_version_number,
337 c_API_NAME,
338 G_PKG_NAME)
339 THEN
340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
341 END IF;
342
343 --* Initialize message list if p_init_msg_list is set to TRUE.
344 IF FND_API.to_Boolean( p_init_msg_list )
345 THEN
346 FND_MSG_PUB.initialize;
347 END IF;
348
349 --* Initialize API return status to SUCCESS
350 x_return_status := FND_API.G_RET_STS_SUCCESS;
351
352 --* logic starts here *--
353
354 /* BEGIN: Algorithm:
355 * (1) initialize a local record l_rule_matching_rec of type CSD_RULE_MATCHING_REC_TYPE:
356 * l_rule_matching_rec.rule_match_code := 1;
357 * l_rule_matching_rec.rule_type = DEFAULTING;
358 * l_rule_matching_rec.entity_attribute_type := p_entity_attribute_type;
359 * l_rule_matching_rec.entity_attribute_code := p_entity_attribute_code;
360 * l_rule_matching_rec.rule_input_rec := p_rule_input_rec;
361 *
362 * (2) Call procedure PROCESS_RULE_MATCHING
363 * (3) Check if any retrieval needs to be done for default value:
364 * IF l_rule_matching_rec.RULE_RESULTS_TBL(1).VALUE_TYPE = ATTRIBUTE THEN
365 * DEFAULT_VALUE := l_rule_matching_rec.RULE_RESULTS_TBL(1).defaulting_value
366 * ELSIF l_rule_matching_rec.RULE_RESULTS_TBL(1).VALUE_TYPE = PROFILE THEN
367 * DEFAULT_VALUE := get fnd_profile value for profile name stored in
368 * l_rule_matching_rec.RULE_RESULTS_TBL(1).defaulting_value
369 * ELSIF l_rule_matching_rec.RULE_RESULTS_TBL(1).VALUE_TYPE = PLSQL THEN
370 * DEFAULT_VALUE := execute PL/SQL API.
371 * END IF;
372 * END: Algorithm*/
373
374 --* init l_rule_matching_rec
375 l_rule_matching_rec.rule_match_code := G_RULE_MATCH_ONE;
376 l_rule_matching_rec.rule_type := G_RULE_TYPE_DEFAULTING;
377 l_rule_matching_rec.entity_attribute_type := p_entity_attribute_type;
378 l_rule_matching_rec.entity_attribute_code := p_entity_attribute_code;
379 l_rule_matching_rec.rule_input_rec := p_rule_input_rec;
380
381 --** debug starts!!
382 --dbms_output.put_line('GET_DEFAULT_VALUE_FROM_RULE before PROCESS_RULE_MATCHING, x_return_status ' || x_return_status);
383 --** debug ends!!
384
385
386 PROCESS_RULE_MATCHING(
387 p_api_version_number => p_api_version_number,
388 p_commit => p_commit,
389 p_init_msg_list => p_init_msg_list,
390 p_validation_level => p_validation_level,
391 px_rule_matching_rec => l_rule_matching_rec,
392 x_return_status => x_return_status,
393 x_msg_count => x_msg_count,
394 x_msg_data => x_msg_data
395 );
396 --** debug starts!!
397 --dbms_output.put_line('GET_DEFAULT_VALUE_FROM_RULE -> x_return_status = ' || x_return_status);
398 --** debug ends!!
399
400 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
401 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
402 FND_MESSAGE.Set_Name('CSD', 'CSD_RULE_MATCH_FAILED');
403 FND_MSG_PUB.Add;
404 END IF;
405 RAISE FND_API.G_EXC_ERROR;
406 END IF; --* end IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) *--
407
408 l_tbl_count := l_rule_matching_rec.RULE_RESULTS_TBL.COUNT;
409
410 --** debug starts!!
411 --dbms_output.put_line('GET_DEFAULT_VALUE_FROM_RULE -> l_tbl_count = ' || l_tbl_count);
412 --** debug ends!!
413
414 IF (l_tbl_count > 0) THEN
415 l_value_type := l_rule_matching_rec.RULE_RESULTS_TBL(1).VALUE_TYPE;
416 l_defaulting_value := l_rule_matching_rec.RULE_RESULTS_TBL(1).DEFAULTING_VALUE;
417 l_default_rule_id := l_rule_matching_rec.RULE_RESULTS_TBL(1).RULE_ID; -- swai: 12.1.1 ER 7233924
418 --** debug starts!!
419 --dbms_output.put_line('GET_DEFAULT_VALUE_FROM_RULE , value_type ' || l_value_type);
420 --dbms_output.put_line('GET_DEFAULT_VALUE_FROM_RULE , defaulting_value ' || l_defaulting_value);
421 --** debug ends!!
422 END IF; --* end IF (l_tbl_count > 0) *--
423
424 --* GET_DEFAULT_VALUE must be always called because if l_defaulting_value is null,
425 --* then the value would be returned from a profile option
426 l_default_val_str := GET_DEFAULT_VALUE(
427 p_value_type => l_value_type,
428 p_defaulting_value => l_defaulting_value,
429 p_attribute_type => p_entity_attribute_type,
430 p_attribute_code => p_entity_attribute_code
431 );
432
433 --** debug starts!!
434 --dbms_output.put_line('GET_DEFAULT_VALUE_FROM_RULE , after GET_DEFAULT_VALUE, l_default_val_str = ' || l_default_val_str);
435 --** debug ends!!
436
437 x_rule_id := l_default_rule_id; -- swai: 12.1.1 ER 7233924
438 x_default_value := l_default_val_str;
439 --* logic ends here *--
440
441
442 --* Standard call to get message count and if count is 1, get message info.
443 FND_MSG_PUB.Count_And_Get(
444 p_count => x_msg_count,
445 p_data => x_msg_data
446 );
447
448 EXCEPTION
449 WHEN FND_API.G_EXC_ERROR THEN
450 x_return_status := FND_API.G_RET_STS_ERROR;
451 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
452 P_API_NAME => c_API_NAME
453 ,P_PKG_NAME => G_PKG_NAME
454 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
455 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
456 ,X_MSG_COUNT => X_MSG_COUNT
457 ,X_MSG_DATA => X_MSG_DATA
458 ,X_RETURN_STATUS => X_RETURN_STATUS);
459
460 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
461 x_return_status := FND_API.G_RET_STS_ERROR;
462 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
463 P_API_NAME => c_API_NAME
464 ,P_PKG_NAME => G_PKG_NAME
465 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
466 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
467 ,X_MSG_COUNT => X_MSG_COUNT
468 ,X_MSG_DATA => X_MSG_DATA
469 ,X_RETURN_STATUS => X_RETURN_STATUS);
470
471 WHEN OTHERS THEN
472 x_return_status := FND_API.G_RET_STS_ERROR;
473 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
474 P_API_NAME => c_API_NAME
475 ,P_PKG_NAME => G_PKG_NAME
476 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
477 ,P_SQLCODE => SQLCODE
478 ,P_SQLERRM => SQLERRM
479 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
480 ,X_MSG_COUNT => X_MSG_COUNT
481 ,X_MSG_DATA => X_MSG_DATA
482 ,X_RETURN_STATUS => X_RETURN_STATUS);
483 END GET_DEFAULT_VALUE_FROM_RULE;
484
485
486 /*--------------------------------------------------------------------*/
487 /* procedure name: GET_DEFAULT_VALUE_FROM_RULE (overloaded) */
488 /* description : procedure used to get default values from rules */
489 /* default value = NUMBER data type */
490 /* */
491 /* */
492 /* Called from : Depot Repair Workbench defaulting */
493 /* Input Parm : */
494 /* p_api_version NUMBER Req Api Version number */
495 /* p_init_msg_list VARCHAR2 Opt Initialize message stack */
496 /* p_commit VARCHAR2 Opt Commits in API */
497 /* p_validation_level NUMBER Opt validation steps */
498 /* p_entity_attribute_type VARCHAR2 Req */
499 /* p_entity_attribute_code VARCHAR2 Req */
500 /* p_rule_input_rec CSD_RULE_INPUT_REC_TYPE Req */
501 /* Output Parm : */
502 /* x_return_status VARCHAR2 Return status after the call. */
503 /* x_msg_count NUMBER Number of messages in stack */
504 /* x_msg_data VARCHAR2 Mesg. text if x_msg_count >= 1 */
505 /* x_default_value NUMBER */
506 /* x_rule_id NUMBER Rule ID that determined value */
507 /* if null, then no rule used */
508 /* Change Hist : Jan-14-08 rfieldma created */
509 /* Aug-20-08 swai added param x_rule_id */
510 /* */
511 /* */
512 /*--------------------------------------------------------------------*/
513 PROCEDURE GET_DEFAULT_VALUE_FROM_RULE (
514 p_api_version_number IN NUMBER,
515 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
516 p_commit IN VARCHAR2 := FND_API.G_FALSE,
517 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
518 p_entity_attribute_type IN VARCHAR2,
519 p_entity_attribute_code IN VARCHAR2,
520 p_rule_input_rec IN CSD_RULE_INPUT_REC_TYPE,
521 x_default_value OUT NOCOPY NUMBER,
522 x_rule_id OUT NOCOPY NUMBER, -- swai: 12.1.1 ER 7233924
523 x_return_status OUT NOCOPY VARCHAR2,
524 x_msg_count OUT NOCOPY NUMBER,
525 x_msg_data OUT NOCOPY VARCHAR2
526 )
527 IS
528 ---- local constants ----
529 c_API_NAME CONSTANT VARCHAR2(30) := 'GET_DEFAULT_VALUE_FROM_RULE';
530
531 ---- local variables ----
532 l_default_val_str VARCHAR2(150) := NULL;
533 BEGIN
534 --* Standard Start of API savepoint
535 SAVEPOINT GET_DFLT_VAL_FROM_RULE_PVT;
536
537 --* call the string function
538 GET_DEFAULT_VALUE_FROM_RULE (
539 p_api_version_number => p_api_version_number,
540 p_init_msg_list => p_init_msg_list,
541 p_commit => p_commit,
542 p_validation_level => p_validation_level,
543 p_entity_attribute_type => p_entity_attribute_type,
544 p_entity_attribute_code => p_entity_attribute_code,
545 p_rule_input_rec => p_rule_input_rec,
546 x_default_value => l_default_val_str,
547 x_rule_id => x_rule_id, -- swai: 12.1.1 ER 7233924
548 x_return_status => x_return_status,
549 x_msg_count => x_msg_count,
550 x_msg_data => x_msg_data
551 );
552
553 --* convert value to number
554 x_default_value := to_number(l_default_val_str);
555
556 --* logic ends here *--
557 EXCEPTION
558 WHEN FND_API.G_EXC_ERROR THEN
559 x_return_status := FND_API.G_RET_STS_ERROR;
560 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
561 P_API_NAME => c_API_NAME
562 ,P_PKG_NAME => G_PKG_NAME
563 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
564 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
565 ,X_MSG_COUNT => X_MSG_COUNT
566 ,X_MSG_DATA => X_MSG_DATA
567 ,X_RETURN_STATUS => X_RETURN_STATUS);
568
569 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
570 x_return_status := FND_API.G_RET_STS_ERROR;
571 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
572 P_API_NAME => c_API_NAME
573 ,P_PKG_NAME => G_PKG_NAME
574 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
575 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
576 ,X_MSG_COUNT => X_MSG_COUNT
577 ,X_MSG_DATA => X_MSG_DATA
578 ,X_RETURN_STATUS => X_RETURN_STATUS);
579
580 WHEN OTHERS THEN
581 x_return_status := FND_API.G_RET_STS_ERROR;
582 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
583 P_API_NAME => c_API_NAME
584 ,P_PKG_NAME => G_PKG_NAME
585 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
586 ,P_SQLCODE => SQLCODE
587 ,P_SQLERRM => SQLERRM
588 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
589 ,X_MSG_COUNT => X_MSG_COUNT
590 ,X_MSG_DATA => X_MSG_DATA
591 ,X_RETURN_STATUS => X_RETURN_STATUS);
592 END GET_DEFAULT_VALUE_FROM_RULE;
593
594
595 /*--------------------------------------------------------------------*/
596 /* procedure name: GET_DEFAULT_VALUE_FROM_RULE (overloaded) */
597 /* description : procedure used to get default values from rules */
598 /* default value = DATE data type */
599 /* */
600 /* */
601 /* Called from : Depot Repair Workbench defaulting */
602 /* Input Parm : */
603 /* p_api_version NUMBER Req Api Version number */
604 /* p_init_msg_list VARCHAR2 Opt Initialize message stack */
605 /* p_commit VARCHAR2 Opt Commits in API */
606 /* p_validation_level NUMBER Opt validation steps */
607 /* p_entity_attribute_type VARCHAR2 Req */
608 /* p_entity_attribute_code VARCHAR2 Req */
609 /* p_rule_input_rec CSD_RULE_INPUT_REC_TYPE Req */
610 /* Output Parm : */
611 /* x_return_status VARCHAR2 Return status after the call. */
612 /* x_msg_count NUMBER Number of messages in stack */
613 /* x_msg_data VARCHAR2 Mesg. text if x_msg_count >= 1 */
614 /* x_default_value DATE */
615 /* x_rule_id NUMBER Rule ID that determined value */
616 /* if null, then no rule used */
617 /* Change Hist : Jan-14-08 rfieldma created */
618 /* Aug-20-08 swai added param x_rule_id */
619 /* */
620 /* */
621 /*--------------------------------------------------------------------*/
622 PROCEDURE GET_DEFAULT_VALUE_FROM_RULE (
623 p_api_version_number IN NUMBER,
624 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
625 p_commit IN VARCHAR2 := FND_API.G_FALSE,
626 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
627 p_entity_attribute_type IN VARCHAR2,
628 p_entity_attribute_code IN VARCHAR2,
629 p_rule_input_rec IN CSD_RULE_INPUT_REC_TYPE,
630 x_default_value OUT NOCOPY DATE,
631 x_rule_id OUT NOCOPY NUMBER, -- swai: 12.1.1 ER 7233924
632 x_return_status OUT NOCOPY VARCHAR2,
633 x_msg_count OUT NOCOPY NUMBER,
634 x_msg_data OUT NOCOPY VARCHAR2
635 )
636 IS
637 ---- local constants ----
638 c_API_NAME CONSTANT VARCHAR2(30) := 'GET_DEFAULT_VALUE_FROM_RULE';
639
640 ---- local variables ----
641 l_default_val_str VARCHAR2(150) := NULL;
642
643 BEGIN
644 --* Standard Start of API savepoint
645 SAVEPOINT GET_DFLT_VAL_FROM_RULE_PVT;
646 --* call the string function
647 GET_DEFAULT_VALUE_FROM_RULE (
648 p_api_version_number => p_api_version_number,
649 p_init_msg_list => p_init_msg_list,
650 p_commit => p_commit,
651 p_validation_level => p_validation_level,
652 p_entity_attribute_type => p_entity_attribute_type,
653 p_entity_attribute_code => p_entity_attribute_code,
654 p_rule_input_rec => p_rule_input_rec,
655 x_default_value => l_default_val_str,
656 x_rule_id => x_rule_id, -- swai: 12.1.1 ER 7233924
657 x_return_status => x_return_status,
658 x_msg_count => x_msg_count,
659 x_msg_data => x_msg_data
660 );
661
662 --* convert value to date
663 x_default_value := to_date(l_default_val_str, 'DD-MM-YY HH:MI:SS');
664
665
666 --* logic ends here *--
667 EXCEPTION
668 WHEN FND_API.G_EXC_ERROR THEN
669 x_return_status := FND_API.G_RET_STS_ERROR;
670 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
671 P_API_NAME => c_API_NAME
672 ,P_PKG_NAME => G_PKG_NAME
673 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
674 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
675 ,X_MSG_COUNT => X_MSG_COUNT
676 ,X_MSG_DATA => X_MSG_DATA
677 ,X_RETURN_STATUS => X_RETURN_STATUS);
678
679 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
680 x_return_status := FND_API.G_RET_STS_ERROR;
681 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
682 P_API_NAME => c_API_NAME
683 ,P_PKG_NAME => G_PKG_NAME
684 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
685 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
686 ,X_MSG_COUNT => X_MSG_COUNT
687 ,X_MSG_DATA => X_MSG_DATA
688 ,X_RETURN_STATUS => X_RETURN_STATUS);
689
690 WHEN OTHERS THEN
691 x_return_status := FND_API.G_RET_STS_ERROR;
692 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
693 P_API_NAME => c_API_NAME
694 ,P_PKG_NAME => G_PKG_NAME
695 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
696 ,P_SQLCODE => SQLCODE
697 ,P_SQLERRM => SQLERRM
698 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
699 ,X_MSG_COUNT => X_MSG_COUNT
700 ,X_MSG_DATA => X_MSG_DATA
701 ,X_RETURN_STATUS => X_RETURN_STATUS);
702 END GET_DEFAULT_VALUE_FROM_RULE;
703
704 /*--------------------------------------------------------------------*/
705 /* procedure name: MATCH_CONDITION */
706 /* description : procedure used to match parameter to criterion based */
707 /* on operatior */
708 /* Calls overloaded function - CHECK_CONDITION_MATCH */
709 /* */
710 /* */
711 /* Called from : PROCEDURE PROCESS_RULE_MATCHING */
712 /* Input Parm : */
713 /* p_parameter_type VARCHAR2 Req */
714 /* p_operator VARCHAR2 Req */
715 /* p_criterion VARCHAR2 Req */
716 /* p_rule_input_rec CSD_RULE_INPUT_REC_TYPE Req */
717 /* Return Val : */
718 /* VARCHAR2 G_MISS.G_TRUE/G_MISS.G_FALSE */
719 /* Change Hist : Jan-14-08 rfieldma created */
720 /* */
721 /* */
722 /* */
723 /*--------------------------------------------------------------------*/
724 FUNCTION MATCH_CONDITION (
725 p_parameter_type IN VARCHAR2,
726 p_operator IN VARCHAR2,
727 p_criterion IN VARCHAR2,
728 p_rule_input_rec IN CSD_RULE_INPUT_REC_TYPE
729 ) RETURN VARCHAR2 IS
730 ---- local variables ----
731 l_return_val VARCHAR2(1) := Fnd_Api.G_FALSE;
732 l_rule_input_rec CSD_RULE_INPUT_REC_TYPE;
733 l_number_input NUMBER := -1;
734 l_short_string_input VARCHAR2(30) := NULL;
735 l_repair_line_id NUMBER := NULL;
736 l_date_field DATE := NULL;
737
738 ---- cursors ----
739
740 BEGIN
741 l_repair_line_id := p_rule_input_rec.repair_line_id;
742 COPY_RULE_INPUT_REC_VALUES(p_rule_input_rec, l_rule_input_rec);
743
744
745 IF (p_parameter_type = 'USER_ID') THEN -- to test
746 --** debug starts!!
747 --dbms_output.put_line('>>>MATCH_CONDITION - USER_ID');
748 --** debug ends!!
749
750 l_number_input := FND_GLOBAL.USER_ID;
751 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_number_input,
752 p_operator => p_operator,
753 p_criterion => to_number(p_criterion));
754 ELSIF (p_parameter_type = 'USER_RESPONSIBILITY') THEN -- to test
755 --** debug starts!!
756 --dbms_output.put_line('>>>MATCH_CONDITION - USER_RESPONSIBILITY');
757 --** debug ends!!
758 l_number_input := FND_GLOBAL.RESP_ID;
759 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_number_input,
760 p_operator => p_operator,
761 p_criterion => to_number(p_criterion));
762 ELSIF (p_parameter_type = 'USER_INV_ORG') THEN -- to test
763 --** debug starts!!
764 --dbms_output.put_line('>>>MATCH_CONDITION - USER_INV_ORG');
765 --** debug ends!!
766
767 l_number_input := FND_PROFILE.VALUE(G_PROFILE_INV_ORG); -- get user inventory org id
768 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_number_input,
769 p_operator => p_operator,
770 p_criterion => to_number(p_criterion));
771 ELSIF (p_parameter_type = 'USER_OU') THEN -- to test
772 --** debug starts!!
773 --dbms_output.put_line('>>>MATCH_CONDITION - USER_OU');
774 --** debug ends!!
775
776 l_number_input := FND_GLOBAL.ORG_ID; -- ou id
777 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_number_input,
778 p_operator => p_operator,
779 p_criterion => to_number(p_criterion));
780 ELSIF (p_parameter_type = 'SR_CUSTOMER_ID') THEN -- to test
781 --** debug starts!!
782 --dbms_output.put_line('>>>MATCH_CONDITION - SR_CUSTOMER_ID');
783 --** debug ends!!
784
785 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.SR_CUSTOMER_ID,
786 p_operator => p_operator,
787 p_criterion => to_number(p_criterion));
788 ELSIF (p_parameter_type = 'SR_CUSTOMER_ACCOUNT_ID') THEN -- to test
789 --** debug starts!!
790 --dbms_output.put_line('>>>MATCH_CONDITION - SR_CUSTOMER_ACCOUNT_ID ');
791 --** debug ends!!
792
793 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.SR_CUSTOMER_ACCOUNT_ID,
794 p_operator => p_operator,
795 p_criterion => to_number(p_criterion));
796
797 ELSIF (p_parameter_type = 'SR_BILL_TO_COUNTRY') THEN -- to test
798 --** debug starts!!
799 --dbms_output.put_line('>>>MATCH_CONDITION - SR_BILL_TO_COUNTRY');
800 --** debug ends!!
801
802 --* site_use_id -> site_id hz_party_site_uses
803 --* site_id -> location_id hz_party_sites_v or hz_party_sites
804 --* location has country code hz_locations, use CSDSERVC.pld as example
805 l_short_string_input := GET_COUNTRY_CODE(p_site_use_id => l_rule_input_rec.SR_BILL_TO_SITE_USE_ID); --get sr bill to country from l_rule_input_rec.SR_BILL_TO_SITE_USE_ID
806 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_short_string_input,
807 p_operator => p_operator,
808 p_criterion => p_criterion);
809 ELSIF (p_parameter_type = 'SR_SHIP_TO_COUNTRY') THEN -- to test
810 --** debug starts!!
811 --dbms_output.put_line('>>>MATCH_CONDITION - SR_SHIP_TO_COUNTRY ');
812 --** debug ends!!
813
814 l_short_string_input := GET_COUNTRY_CODE(p_site_use_id => l_rule_input_rec.SR_SHIP_TO_SITE_USE_ID); --get sr bill to country from l_rule_input_rec.SR_BILL_TO_SITE_USE_ID
815 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_short_string_input,
816 p_operator => p_operator,
817 p_criterion => p_criterion);
818 ELSIF (p_parameter_type = 'SR_ITEM_ID') THEN -- OK --
819 --** debug starts!!
820 --dbms_output.put_line('>>>MATCH_CONDITION - SR_ITEM_ID');
821 --** debug ends!!
822
823 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.SR_ITEM_ID,
824 p_operator => p_operator,
825 p_criterion => to_number(p_criterion));
826 ELSIF (p_parameter_type = 'SR_ITEM_CATEGORY_ID') THEN -- to test
827 --** debug starts!!
828 --dbms_output.put_line('>>>MATCH_CONDITION - SR_ITEM_CATEGORY_ID');
829 --** debug ends!!
830
831 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.SR_ITEM_CATEGORY_ID,
832 p_operator => p_operator,
833 p_criterion => to_number(p_criterion));
834
835 ELSIF (p_parameter_type = 'SR_CONTRACT_ID') THEN -- to test
836 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.SR_CONTRACT_ID,
837 p_operator => p_operator,
838 p_criterion => to_number(p_criterion));
839 ELSIF (p_parameter_type = 'SR_PROBLEM_CODE') THEN -- to test
840 --** debug starts!!
841 --dbms_output.put_line('>>>MATCH_CONDITION - SR_PROBLEM_CODE');
842 --** debug ends!!
843
844 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.SR_PROBLEM_CODE,
845 p_operator => p_operator,
846 p_criterion => p_criterion);
847 -- swai: 12.1.1 ER 7233924
848 ELSIF (p_parameter_type = 'RO_ITEM_ID') THEN
849 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.RO_ITEM_ID,
850 p_operator => p_operator,
851 p_criterion => to_number(p_criterion));
852 -- swai: 12.1.1 ER 7233924
853 ELSIF (p_parameter_type = 'RO_ITEM_CATEGORY_ID') THEN -- to test
854 l_return_val := CHECK_RO_ITEM_CATEGORY(p_ro_item_id => l_rule_input_rec.RO_ITEM_ID,
855 p_operator => p_operator,
856 p_criterion => to_number(p_criterion));
857
858 ELSIF (p_parameter_type = 'RO_PROMISE_DATE_THRESHOLD') THEN -- to test
859 --* get # days for sysdate promise_date
860 l_return_val := CHECK_PROMISE_DATE(p_repair_line_id => l_repair_line_id,
861 p_operator => p_operator,
862 p_criterion => p_criterion);
863 ELSIF (p_parameter_type = 'RO_RESOLVE_BY_DATE_THRESHOLD') THEN -- to test
864 l_return_val := CHECK_RESOLVE_BY_DATE(p_repair_line_id => l_repair_line_id,
865 p_operator => p_operator,
866 p_criterion => p_criterion);
867 ELSIF (p_parameter_type = 'RO_THIRD_PTY_THRESHOLD') THEN -- to test
868 --* get # days a third party based on repair line id (return by date on 3rd party return logistics line)
869 l_return_val := CHECK_RETURN_BY_DATE(p_repair_line_id => l_repair_line_id,
870 p_action_type => G_ACTION_TYPE_RMA_THIRD_PTY,
871 p_action_code => '%',
872 p_operator => p_operator,
873 p_criterion => p_criterion);
874 ELSIF (p_parameter_type = 'RO_EXCHANGE_THRESHOLD') THEN -- to test
875 --* get # days exchange is out based on repair line id
876 l_return_val := CHECK_RETURN_BY_DATE(p_repair_line_id => l_repair_line_id,
877 p_action_type => G_ACTION_TYPE_RMA,
878 p_action_code => G_ACTION_CODE_EXCHANGE,
879 p_operator => p_operator,
880 p_criterion => p_criterion);
881 ELSIF (p_parameter_type = 'RO_LOANER_THRESHOLD') THEN -- to test
882 --*get # days loaner is out based on repair line id (return by date on loaner line)
883 l_return_val := CHECK_RETURN_BY_DATE(p_repair_line_id => l_repair_line_id,
884 p_action_type => G_ACTION_TYPE_RMA,
885 p_action_code => G_ACTION_CODE_LOANER,
886 p_operator => p_operator,
887 p_criterion => p_criterion);
888 -- swai: bug 7524870 - add new condition
889 ELSIF (p_parameter_type = 'RO_RMA_CUST_PROD_THRESHOLD') THEN
890 --*get # days until customer product is due based on repair line id (return by date on rma line)
891 l_return_val := CHECK_RETURN_BY_DATE(p_repair_line_id => l_repair_line_id,
892 p_action_type => G_ACTION_TYPE_RMA,
893 p_action_code => G_ACTION_CODE_CUST_PROD,
894 p_operator => p_operator,
895 p_criterion => p_criterion);
896 -- end swai: bug 7524870
897 ELSIF (p_parameter_type = 'RO_REPEAT_REPAIR_THRESHOLD') THEN -- code, need to find out how to get ship date
898 --*get # days since last repair based on instance_id
899 l_return_val := CHECK_REPEAT_REPAIR (p_repair_line_id => l_repair_line_id,
900 p_operator => p_operator,
901 p_criterion => p_criterion);
902 ELSIF (p_parameter_type = 'RO_CHRONIC_REPAIR_THRESHOLD') THEN -- code, need to find out how to get ship date
903 --* quality check period is a new profile option, it's a number uom (day)
904 --* number of repairs during the check period per instance
905 --* get # repair orders within quality check period, for instance_id
906 l_return_val := CHECK_CHRONIC_REPAIR(p_repair_line_id => l_repair_line_id,
907 p_operator => p_operator,
908 p_criterion => p_criterion);
909 ELSIF (p_parameter_type = 'RO_CONTRACT_EXP_THRESHOLD') THEN -- to test, what does grace period mean?
910 --* get # days until contract expires
911 l_return_val := CHECK_CONTRACT_EXP_DATE(p_repair_line_id => l_repair_line_id,
912 p_operator => p_operator,
913 p_criterion => p_criterion);
914
915 END IF; --* end IF (p_parameter_type = 'USER_ID') *--
916
917 RETURN l_return_val;
918 END MATCH_CONDITION;
919
920
921 /*--------------------------------------------------------------------*/
922 /* procedure name: CHECK_CONDITION_MATCH (overloaded) */
923 /* description : procedure used to check if parameter matches */
924 /* criterion based on operator */
925 /* parameter, criterion = NUMBER data type */
926 /* */
927 /* */
928 /* Called from : FUNCTION MATCH_CONDITION */
929 /* Input Parm : */
930 /* p_parameter_type NUMBER Req */
931 /* p_operator NUMBER Req */
932 /* p_criterion NUMBER Req */
933 /* Return Val : */
934 /* VARCHAR2 G_MISS.G_TRUE/G_MISS.G_FALSE */
935 /* Change Hist : Jan-14-08 rfieldma created */
936 /* */
937 /* */
938 /* */
939 /*--------------------------------------------------------------------*/
940 FUNCTION CHECK_CONDITION_MATCH (
941 p_input_param IN NUMBER,
942 p_operator IN VARCHAR2,
943 p_criterion IN NUMBER
944 ) RETURN VARCHAR2 IS
945 l_return_val VARCHAR2(1):= FND_API.G_FALSE;
946 BEGIN
947
948 --** debug starts!!
949 --dbms_output.put_line('NNN CHECK_CONDITION_MATCH_N top p_input_param = ' || p_input_param);
950 --dbms_output.put_line('NNN CHECK_CONDITION_MATCH_N top p_operator = ' || p_operator);
951 --dbms_output.put_line('NNN CHECK_CONDITION_MATCH_N top p_criterion = ' || p_criterion);
952 --** debug ends!!
953
954
955 IF (p_operator = G_EQUALS AND p_input_param = p_criterion) THEN
956 l_return_val := FND_API.G_TRUE;
957 ELSIF (p_operator = G_NOT_EQUALS AND p_input_param <> p_criterion) THEN
958 l_return_val := FND_API.G_TRUE;
959 ELSIF (p_operator = G_LESS_THAN AND p_input_param < p_criterion) THEN
960 l_return_val := FND_API.G_TRUE;
961 ELSIF (p_operator = G_GREATER_THAN AND p_input_param > p_criterion) THEN
962 l_return_val := FND_API.G_TRUE;
963 END IF;
964
965 --** debug starts!!
966 --dbms_output.put_line('NNN CHECK_CONDITION_MATCH_N l_return_val = ' || l_return_val);
967 --** debug ends!!
968
969 --* if all of the above cases fail, then there is no match.
970 RETURN l_return_val;
971 END CHECK_CONDITION_MATCH;
972
973
974 /*--------------------------------------------------------------------*/
975 /* procedure name: CHECK_CONDITION_MATCH (overloaded) */
976 /* description : procedure used to check if parameter matches */
977 /* criterion based on operator */
978 /* parameter, criterion = VARCHAR2 data type */
979 /* varchar type only matches = and <> */
980 /* */
981 /* */
982 /* Called from : FUNCTION MATCH_CONDITION */
983 /* Input Parm : */
984 /* p_parameter_type VARCHAR2 Req */
985 /* p_operator VARCHAR2 Req */
986 /* p_criterion VARCHAR2 Req */
987 /* Return Val : */
988 /* VARCHAR2 G_MISS.G_TRUE/G_MISS.G_FALSE */
989 /* Change Hist : Jan-14-08 rfieldma created */
990 /* */
991 /* */
992 /* */
993 /*--------------------------------------------------------------------*/
994 FUNCTION CHECK_CONDITION_MATCH (
995 p_input_param IN VARCHAR2,
996 p_operator IN VARCHAR2,
997 p_criterion IN VARCHAR2
998 ) RETURN VARCHAR2 IS
999 l_return_val VARCHAR2(1) := FND_API.G_FALSE;
1000 BEGIN
1001 --** debug starts!!
1002 --dbms_output.put_line('VVV CHECK_CONDITION_MATCH_V top p_input_param = ' || p_input_param);
1003 --dbms_output.put_line('VVV CHECK_CONDITION_MATCH_V top p_operator = ' || p_operator);
1004 --dbms_output.put_line('VVV CHECK_CONDITION_MATCH_V top p_criterion = ' || p_criterion);
1005 --** debug ends!!
1006
1007 IF (p_operator = G_EQUALS AND p_input_param = p_criterion) THEN
1008 l_return_val := FND_API.G_TRUE;
1009 ELSIF (p_operator = G_NOT_EQUALS AND p_input_param <> p_criterion) THEN
1010 l_return_val := FND_API.G_TRUE;
1011 END IF;
1012
1013 --** debug starts!!
1014 --dbms_output.put_line('VVV CHECK_CONDITION_MATCH_V l_return_val = ' || l_return_val);
1015 --** debug ends!!
1016
1017 --* if all of the above cases fail, then there is no match.
1018 RETURN l_return_val; -- FND_API.G_FALSE
1019 END CHECK_CONDITION_MATCH;
1020
1021
1022 /*--------------------------------------------------------------------*/
1023 /* procedure name: CHECK_CONDITION_MATCH (overloaded) */
1024 /* description : procedure used to check if parameter matches */
1025 /* criterion based on operator */
1026 /* parameter, criterion = DATE data type */
1027 /* */
1028 /* */
1029 /* Called from : FUNCTION MATCH_CONDITION */
1030 /* Input Parm : */
1031 /* p_parameter_type DATE Req */
1032 /* p_operator VARCHAR2 Req */
1033 /* p_criterion DATE Req */
1034 /* Return Val : */
1035 /* VARCHAR2 G_MISS.G_TRUE/G_MISS.G_FALSE */
1036 /* Change Hist : Jan-14-08 rfieldma created */
1037 /* */
1038 /* */
1039 /* */
1040 /*--------------------------------------------------------------------*/
1041 FUNCTION CHECK_CONDITION_MATCH (
1042 p_input_param IN DATE,
1043 p_operator IN VARCHAR2,
1044 p_criterion IN DATE
1045 ) RETURN VARCHAR2 IS
1046 l_return_val VARCHAR2(1) := FND_API.G_FALSE;
1047 BEGIN
1048 --** debug starts!!
1049 --dbms_output.put_line('DDD CHECK_CONDITION_MATCH_D top p_input_param = ' || p_input_param);
1050 --dbms_output.put_line('DDD CHECK_CONDITION_MATCH_D top p_operator = ' || p_operator);
1051 --dbms_output.put_line('DDD CHECK_CONDITION_MATCH_D top p_criterion = ' || p_criterion);
1052 --** debug ends!!
1053
1054 IF (p_operator = G_EQUALS AND p_input_param = p_criterion) THEN
1055 l_return_val := FND_API.G_TRUE;
1056 ELSIF (p_operator = G_NOT_EQUALS AND p_input_param <> p_criterion) THEN
1057 l_return_val := FND_API.G_TRUE;
1058 ELSIF (p_operator = G_LESS_THAN AND p_input_param < p_criterion) THEN
1059 l_return_val := FND_API.G_TRUE;
1060 ELSIF (p_operator = G_GREATER_THAN AND p_input_param > p_criterion) THEN
1061 l_return_val := FND_API.G_TRUE;
1062 END IF;
1063
1064 --** debug starts!!
1065 --dbms_output.put_line('DDD CHECK_CONDITION_MATCH_D l_return_val_D = ' || l_return_val);
1066 --** debug ends!!
1067
1068 --* if all of the above cases fail, then there is no match.
1069 RETURN l_return_val; -- FND_API.G_FALSE
1070 END CHECK_CONDITION_MATCH;
1071
1072
1073 /*--------------------------------------------------------------------*/
1074 /* procedure name: COPY_RULE_INPUT_REC_VALUES */
1075 /* description : copies source rec into dest rec */
1076 /* rec typ = CSD_RULE_INPUT_REC_TYPE */
1077 /* */
1078 /* */
1079 /* Called from : FUNCTION MATCH_CONDITION */
1080 /* Input Parm : */
1081 /* p_s_rec CSD_RULE_INPUT_REC_TYPE Req */
1082 /* p_d_Rec CSD_RULE_INPUT_REC_TYPE VARCHAR2 Req */
1083 /* Change Hist : Jan-14-08 rfieldma created */
1084 /* */
1085 /* */
1086 /* */
1087 /*--------------------------------------------------------------------*/
1088 PROCEDURE COPY_RULE_INPUT_REC_VALUES(
1089 p_s_rec IN CSD_RULE_INPUT_REC_TYPE, -- source rec
1090 px_d_rec IN OUT NOCOPY CSD_RULE_INPUT_REC_TYPE -- destination rec
1091 ) IS
1092 BEGIN
1093 px_d_rec.SR_CUSTOMER_ID := p_s_rec.SR_CUSTOMER_ID;
1094 px_d_rec.SR_CUSTOMER_ACCOUNT_ID := p_s_rec.SR_CUSTOMER_ACCOUNT_ID;
1095 px_d_rec.SR_BILL_TO_SITE_USE_ID := p_s_rec.SR_BILL_TO_SITE_USE_ID;
1096 px_d_rec.SR_SHIP_TO_SITE_USE_ID := p_s_rec.SR_SHIP_TO_SITE_USE_ID;
1097 px_d_rec.SR_ITEM_ID := p_s_rec.SR_ITEM_ID;
1098 px_d_rec.SR_ITEM_CATEGORY_ID := p_s_rec.SR_ITEM_CATEGORY_ID;
1099 px_d_rec.SR_CONTRACT_ID := p_s_rec.SR_CONTRACT_ID;
1100 px_d_rec.SR_PROBLEM_CODE := p_s_rec.SR_PROBLEM_CODE;
1101 px_d_rec.SR_INSTANCE_ID := p_s_rec.SR_INSTANCE_ID;
1102 px_d_rec.RO_ITEM_ID := p_s_rec.RO_ITEM_ID; -- swai: 12.1.1 ER 7233924
1103
1104 --** debug starts!!
1105 -- dbms_output.put_line('in COPY_RULE_INPUT_REC_VALUES - SR_ITEM_ID = ' || px_d_rec.SR_ITEM_ID);
1106 --** debug ends!!
1107
1108 END COPY_RULE_INPUT_REC_VALUES;
1109
1110 /*--------------------------------------------------------------------*/
1111 /* procedure name: COPY_RULE_INPUT_REC_VALUES */
1112 /* description : copies source rec into dest rec */
1113 /* rec typ = CSD_RULE_INPUT_REC_TYPE */
1114 /* */
1115 /* */
1116 /* Called from : FUNCTION MATCH_CONDITION */
1117 /* Input Parm : */
1118 /* p_s_rec CSD_RULE_INPUT_REC_TYPE Req */
1119 /* p_d_Rec CSD_RULE_INPUT_REC_TYPE VARCHAR2 Req */
1120 /* Change Hist : Jan-14-08 rfieldma created */
1121 /* */
1122 /* */
1123 /* */
1124 /*--------------------------------------------------------------------*/
1125 PROCEDURE POPULATE_RULE_INPUT_REC(
1126 px_rule_input_rec IN OUT NOCOPY CSD_RULE_INPUT_REC_TYPE,
1127 p_repair_line_id IN NUMBER
1128 )IS
1129 ---- cursors ----
1130 CURSOR cur_get_rec_info (p_repair_line_id NUMBER) IS
1131 SELECT a.customer_id,
1132 a.account_id,
1133 a.bill_to_site_use_id,
1134 a.ship_to_site_use_id,
1135 a.inventory_item_id,
1136 c.category_id,
1137 a.contract_id,
1138 a.problem_code,
1139 a.customer_product_id,
1140 b.inventory_item_id -- swai: 12.1.1 ER 7233924
1141 FROM CSD_INCIDENTS_V a, CSD_REPAIRS b, CS_INCIDENTS_B_SEC c
1142 WHERE a.incident_id = b.incident_id
1143 AND a.incident_id = c.incident_id
1144 AND b.repair_line_Id = p_repair_line_id;
1145 BEGIN
1146 OPEN cur_get_rec_info(p_repair_line_id);
1147 FETCH cur_get_rec_info INTO
1148 px_rule_input_rec.SR_CUSTOMER_ID,
1149 px_rule_input_rec.SR_CUSTOMER_ACCOUNT_ID,
1150 px_rule_input_rec.SR_BILL_TO_SITE_USE_ID,
1151 px_rule_input_rec.SR_SHIP_TO_SITE_USE_ID,
1152 px_rule_input_rec.SR_ITEM_ID,
1153 px_rule_input_rec.SR_ITEM_CATEGORY_ID,
1154 px_rule_input_rec.SR_CONTRACT_ID,
1155 px_rule_input_rec.SR_PROBLEM_CODE,
1156 px_rule_input_rec.SR_INSTANCE_ID,
1157 px_rule_input_rec.RO_ITEM_ID; -- swai: 12.1.1 ER 7233924
1158 CLOSE cur_get_rec_info;
1159
1160 --** debug starts!!
1161 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC P_REPAIR_LINE_ID = ' || p_repair_line_id);
1162 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC SR_CUSTOMER_ID = ' || px_rule_input_rec.SR_CUSTOMER_ID);
1163 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC SR_CUSTOMER_ACCOUNT_ID = ' || px_rule_input_rec.SR_CUSTOMER_ACCOUNT_ID);
1164 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC SR_BILL_TO_SITE_USE_ID = ' || px_rule_input_rec.SR_BILL_TO_SITE_USE_ID);
1165 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC SR_SHIP_TO_SITE_USE_ID = ' || px_rule_input_rec.SR_SHIP_TO_SITE_USE_ID);
1166 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC SR_ITEM_ID = ' || px_rule_input_rec.SR_ITEM_ID);
1167 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC SR_ITEM_CATEGORY_ID = ' || px_rule_input_rec.SR_ITEM_CATEGORY_ID);
1168 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC SR_CONTRACT_ID = ' || px_rule_input_rec.SR_CONTRACT_ID);
1169 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC SR_PROBLEM_CODE = ' || px_rule_input_rec.SR_PROBLEM_CODE);
1170 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC SR_INSTANCE_ID = ' || px_rule_input_rec.SR_INSTANCE_ID);
1171 --** debug ends!!
1172
1173
1174 END POPULATE_RULE_INPUT_REC;
1175
1176
1177 /*--------------------------------------------------------------------*/
1178 /* procedure name: GET_DEFAULT_VALUE */
1179 /* description : retrieves default value based on type */
1180 /* ATTRIBUTE -> return default value as is */
1181 /* PROFILE -> return profile (default value) */
1182 /* PLSQL -> execute function call stored in default */
1183 /* value and cast return value to string */
1184 /* and return that string value */
1185 /* */
1186 /* */
1187 /* Called from : FUNCTION GET_DEFAULT_VALUE_FROM_RULE */
1188 /* Input Parm : */
1189 /* p_value_type VARCHAR2 Req */
1190 /* p_defaulting_value VARCHAR2 Req */
1191 /* p_attribute_type VARCHAR2 Req */
1192 /* p_attribute_code VARCHAR2 Req */
1193 /* x_return_status VARCHAR2 Req */
1194 /* x_msg_count VARCHAR2 Req */
1195 /* x_msg_data VARCHAR2 Req */
1196 /* Return Val : */
1197 /* VARCHAR2 - the actual default value */
1198 /* */
1199 /* Change Hist : Jan-14-08 rfieldma created */
1200 /* */
1201 /* */
1202 /* */
1203 /*--------------------------------------------------------------------*/
1204 FUNCTION GET_DEFAULT_VALUE(
1205 p_value_type IN VARCHAR2,
1206 p_defaulting_value IN VARCHAR2,
1207 p_attribute_type IN VARCHAR2,
1208 p_attribute_code IN VARCHAR2
1209 ) RETURN VARCHAR2 IS
1210 ---- local constants ----
1211 c_SELECT VARCHAR2(7) := 'SELECT ';
1212 c_FROM_DUAL VARCHAR2(10) := ' FROM DUAL';
1213
1214 ---- local variables ----
1215 l_return_val VARCHAR2(150) := NULL; -- size of flex field
1216 l_sql_stmt VARCHAR2(2000) := NULL;
1217 l_fdbk NUMBER := NULL;
1218 l_cursor NUMBER := NULL;
1219
1220 BEGIN
1221
1222 --** debug starts!!
1223 --dbms_output.put_line('~~~ GET_DEFAULT_VALUE top, p_value_type =' || p_value_type);
1224 --dbms_output.put_line('~~~ GET_DEFAULT_VALUE top, p_defaulting_value =' || p_defaulting_value);
1225 --dbms_output.put_line('~~~ GET_DEFAULT_VALUE top, p_attribute_type =' || p_attribute_type);
1226 --dbms_output.put_line('~~~ GET_DEFAULT_VALUE top, p_attribute_code =' || p_attribute_code);
1227 --** debug ends!!
1228
1229 -- if defaulting value is null, the try to find profile value if applies
1230 IF (p_defaulting_value IS NULL) THEN -- get profile values and put them in
1231 IF (p_attribute_type = G_ATTR_TYPE_RO) AND (p_attribute_code = G_ATTR_CODE_REPAIR_ORG) THEN
1232 l_return_val := FND_PROFILE.VALUE(G_PROFILE_REPAIR_ORG);
1233 --** debug starts!!
1234 --dbms_output.put_line('~~~ GET_DEFAULT_VALUE , G_ATTR_CODE_REPAIR_ORG, l_return_val from profile = ' || l_return_val);
1235 --** debug ends!!
1236
1237 ELSIF (p_attribute_type = G_ATTR_TYPE_RO) AND (p_attribute_code = G_ATTR_CODE_REPAIR_OWNER) THEN
1238 --* no profile
1239 l_return_val := NULL;
1240 --** debug starts!!
1241 --dbms_output.put_line('~~~ GET_DEFAULT_VALUE , G_ATTR_CODE_REPAIR_OWNER, l_return_val set to null ');
1242 --** debug ends!!
1243
1244 ELSIF (p_attribute_type = G_ATTR_TYPE_RO) AND (p_attribute_code = G_ATTR_CODE_INV_ORG) THEN
1245 l_return_val := FND_PROFILE.VALUE(G_PROFILE_INV_ORG);
1246 ELSIF (p_attribute_type = G_ATTR_TYPE_RO) AND (p_attribute_code = G_ATTR_CODE_RMA_RCV_ORG) THEN
1247 --* no profile
1248 l_return_val := NULL;
1249 ELSIF (p_attribute_type = G_ATTR_TYPE_RO) AND (p_attribute_code = G_ATTR_CODE_RMA_RCV_SUBINV) THEN
1250 --* no profile
1251 l_return_val := NULL;
1252 ELSIF (p_attribute_type = G_ATTR_TYPE_RO) AND (p_attribute_code = G_ATTR_CODE_PRIORITY) THEN
1253 --* no profile, place holder for if other defaulting logic is needed.
1254 l_return_val := NULL;
1255 ELSIF (p_attribute_type = G_ATTR_TYPE_RO) AND (p_attribute_code = G_ATTR_CODE_REPAIR_TYPE) THEN
1256 l_return_val := FND_PROFILE.VALUE(G_PROFILE_REPAIR_TYPE);
1257 ELSIF (p_attribute_type = G_ATTR_TYPE_RO) AND (p_attribute_code = G_ATTR_CODE_SHIP_FROM_ORG) THEN
1258 --* no profile
1259 l_return_val := NULL;
1260 ELSIF (p_attribute_type = G_ATTR_TYPE_RO) AND (p_attribute_code = G_ATTR_CODE_SHIP_FROM_SUBINV) THEN
1261 --* no profile
1262 l_return_val := NULL;
1263 ELSIF (p_attribute_type = G_ATTR_TYPE_RO) AND (p_attribute_code = G_ATTR_CODE_VENDOR_ACCOUNT) THEN
1264 --* no profile, no logic needed
1265 l_return_val := NULL;
1266 ELSE
1267 l_return_val := NULL;
1268 END IF; --*end IF (p_attribute_type = G_ATTR_TYPE_RO) ...*--
1269
1270 ELSE
1271 IF (p_value_type IS NULL) OR (p_value_type = FND_API.G_MISS_CHAR) --* bulletin rules does not specify this value
1272 OR (p_value_type = G_VALUE_TYPE_ATTRIBUTE) THEN
1273 l_return_val := p_defaulting_value;
1274 ELSIF (p_value_type = G_VALUE_TYPE_PROFILE) THEN
1275 l_return_val := FND_PROFILE.VALUE(p_defaulting_value);
1276 ELSIF (p_value_type = G_VALUE_TYPE_PLSQL) THEN
1277 l_sql_stmt := c_SELECT || p_defaulting_value || c_FROM_DUAL;
1278
1279 --** debug starts!!
1280 --dbms_output.put_line('~~~ GET_DEFAULT_VALUE , G_VALUE_TYPE_PLSQL, l_sql_stmt ' || l_sql_stmt);
1281 --** debug ends!!
1282
1283 l_cursor := DBMS_SQL.OPEN_CURSOR;
1284
1285 --* Parse the query with a dynamic WHERE clause
1286 DBMS_SQL.PARSE (l_cursor, l_sql_stmt, DBMS_SQL.NATIVE);
1287
1288 --* Define the columns in the cursor for this query
1289 DBMS_SQL.DEFINE_COLUMN (l_cursor, 1, l_return_val,150);
1290
1291
1292 --* Now I can execute the query
1293 l_fdbk:= DBMS_SQL.EXECUTE (l_cursor);
1294 LOOP
1295 --* Try to fetch next row. If done, then exit the loop.
1296 EXIT WHEN DBMS_SQL.FETCH_ROWS (l_cursor) = 0;
1297
1298 --* Retrieve data via calls to COLUMN_VALUE and place those
1299 --* values in a new record in the block.
1300
1301 DBMS_SQL.COLUMN_VALUE (l_cursor, 1, l_return_val);
1302
1303 END LOOP;
1304
1305 /* Clean up the cursor */
1306 DBMS_SQL.CLOSE_CURSOR (l_cursor);
1307
1308 ELSE --* unrecognized type, so return null
1309 l_return_val := NULL;
1310 END IF; --* end IF (p_value_type = G_VALUE_TYPE_ATTRIBUTE) *--
1311 END IF; --* end IF (p_defaulting_value IS NULL) *--
1312
1313 RETURN l_return_val;
1314 END GET_DEFAULT_VALUE;
1315
1316 /*--------------------------------------------------------------------*/
1317 /* procedure name: GET_COUNTRY_CODE */
1318 /* description : returns country code based on site_useid */
1319 /* */
1320 /* Called from : FUNCTION MATCH_CONDITION */
1321 /* Input Parm : */
1322 /* p_site_use_id NUMBER Req */
1323 /* Return Val : */
1324 /* VARCHAR2 - COUNTRY code */
1325 /* */
1326 /* Change Hist : Jan-14-08 rfieldma created */
1327 /* */
1328 /* */
1329 /* */
1330 /*--------------------------------------------------------------------*/
1331 FUNCTION GET_COUNTRY_CODE(
1332 p_site_use_id IN NUMBER
1333 ) RETURN VARCHAR2 IS
1334 ---- local variables ----
1335 l_country_code VARCHAR2(60) := NULL;
1336
1337 ---- cursors ----
1338 CURSOR cur_get_country_code(p_site_use_id NUMBER) IS
1339 SELECT b.country
1340 FROM hz_party_sites a,
1341 hz_locations b,
1342 hz_party_site_uses c
1343 WHERE a.location_id = b.location_id
1344 AND a.party_site_id = c.party_site_id
1345 AND c.party_site_use_id = p_site_use_id
1346 ; --* end CURSOR cur_get_country_code *--
1347 BEGIN
1348 OPEN cur_get_country_code(p_site_use_id);
1349 FETCH cur_get_country_code INTO l_country_code;
1350 CLOSE cur_get_country_code;
1351
1352 --** debug starts!!
1353 --dbms_output.put_line('***GET_COUNTRY_CODE , p_site_use_id = ' || p_site_use_id);
1354 --dbms_output.put_line('***GET_COUNTRY_CODE , l_country_code =' || l_country_code);
1355 --** debug ends!!
1356
1357
1358
1359 RETURN l_country_code;
1360 END GET_COUNTRY_CODE;
1361
1362
1363 /*--------------------------------------------------------------------*/
1364 /* procedure name: CHECK_RO_ITEM_CATEGORY */
1365 /* description : checks if the RO item is in the specified category */
1366 /* */
1367 /* Called from : FUNCTION MATCH_CONDITION */
1368 /* Input Parm : */
1369 /* p_ro_item_id NUMBER Req RO Inventory Item Id */
1370 /* p_operator VARCHAR2 Req 'EQUALS': check item is in category */
1371 /* 'NOT_EQUALS': check item is not in */
1372 /* item category */
1373 /* p_criterion NUMBER Req Item Category Id */
1374 /* Return Val : */
1375 /* VARCHAR2 - G_TRUE or G_FALSE */
1376 /* */
1377 /* Change Hist : Aug-18-08 swai created for 12.1.1 ER 7233924 */
1378 /* */
1379 /*--------------------------------------------------------------------*/
1380 FUNCTION CHECK_RO_ITEM_CATEGORY(
1381 p_ro_item_id IN NUMBER,
1382 p_operator IN VARCHAR2,
1383 p_criterion IN NUMBER
1384 ) RETURN VARCHAR2 IS
1385
1386 -- cursors --
1387 CURSOR cur_is_item_in_cat (p_inventory_item_id NUMBER,
1388 p_category_id NUMBER)
1389 IS
1390 SELECT 'X'
1391 FROM mtl_item_categories_v
1392 WHERE inventory_item_id = p_inventory_item_id
1393 and category_id = p_category_id
1394 and organization_id = cs_std.get_item_valdn_orgzn_id;
1395
1396 -- variables --
1397 l_item_is_in_cat VARCHAR2(1);
1398 l_return_val VARCHAR2(1) := FND_API.G_FALSE;
1399
1400 BEGIN
1401 OPEN cur_is_item_in_cat (p_ro_item_id, p_criterion);
1402 FETCH cur_is_item_in_cat into l_item_is_in_cat;
1403 CLOSE cur_is_item_in_cat;
1404
1405 CASE p_operator
1406 when G_EQUALS then
1407 if (l_item_is_in_cat is null) then
1408 l_return_val := FND_API.G_FALSE;
1409 else
1410 l_return_val := FND_API.G_TRUE;
1411 end if;
1412 when G_NOT_EQUALS then
1413 if (l_item_is_in_cat is null) then
1414 l_return_val := FND_API.G_TRUE;
1415 else
1416 l_return_val := FND_API.G_FALSE;
1417 end if;
1418 else
1419 l_return_val := FND_API.G_FALSE;
1420 END CASE;
1421 return l_return_val;
1422
1423 END CHECK_RO_ITEM_CATEGORY;
1424
1425
1426 /*--------------------------------------------------------------------*/
1427 /* procedure name: CHECK_PROMISE_BY_DATE */
1428 /* description : retrieves RO promise by date */
1429 /* compare threshold with promise_date - sysdate */
1430 /* */
1431 /* Called from : FUNCTION MATCH_CONDITION */
1432 /* Input Parm : */
1433 /* p_repair_line_id NUMBER Req */
1434 /* Return Val : */
1435 /* VARCHAR2 - G_TRUE or G_FALSE */
1436 /* */
1437 /* Change Hist : Jan-14-08 rfieldma created */
1438 /* */
1439 /* */
1440 /* */
1441 /*--------------------------------------------------------------------*/
1442 FUNCTION CHECK_PROMISE_DATE(
1443 p_repair_line_id IN NUMBER,
1444 p_operator IN VARCHAR2,
1445 p_criterion IN VARCHAR2
1446 ) RETURN VARCHAR2 IS
1447 ---- local variables ----
1448 l_return_val VARCHAR2(1) := FND_API.G_FALSE;
1449 l_number_input NUMBER := NULL;
1450 l_date_field DATE := NULL;
1451
1452 ---- cursors ----
1453 CURSOR cur_get_promise_date(p_repair_line_id NUMBER) IS
1454 SELECT promise_date
1455 FROM csd_repairs
1456 WHERE repair_line_id = p_repair_line_id
1457 ; --* end CURSOR get_promise_date *--
1458 BEGIN
1459 IF (p_repair_line_id IS NOT NULL) THEN
1460 OPEN cur_get_promise_date (p_repair_line_id);
1461 FETCH cur_get_promise_date into l_date_field;
1462 CLOSE cur_get_promise_date;
1463
1464 --** debug starts!!
1465 --dbms_output.put_line('=== CHECK_PROMISE_DATE , l_date_field = ' || l_date_field);
1466 --** debug ends!!
1467
1468 IF (l_date_field IS NOT NULL) AND (l_date_field <> FND_API.G_MISS_DATE) THEN
1469 l_number_input := l_date_field - sysdate;
1470 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_number_input,
1471 p_operator => p_operator,
1472 p_criterion => to_number(p_criterion));
1473 END IF; --* IF (l_date_field IS NOT NULL) ... *--
1474 END IF; --* end IF ( l_repair_line_id IS NOT NULL) *--
1475
1476 RETURN l_return_val;
1477 END CHECK_PROMISE_DATE;
1478
1479
1480 /*--------------------------------------------------------------------*/
1481 /* procedure name: CHECK_RESOLVE_BY_DATE */
1482 /* description : retrieves RO resolve by date */
1483 /* compare threshold with resolve_by_date - sysdate */
1484 /* */
1485 /* Called from : FUNCTION MATCH_CONDITION */
1486 /* Input Parm : */
1487 /* p_repair_line_id NUMBER Req */
1488 /* Return Val : */
1489 /* VARCHAR2 - G_TRUE or G_FALSE */
1490 /* */
1491 /* Change Hist : Jan-14-08 rfieldma created */
1492 /* */
1493 /* */
1494 /* */
1495 /*--------------------------------------------------------------------*/
1496 FUNCTION CHECK_RESOLVE_BY_DATE(
1497 p_repair_line_id IN NUMBER,
1498 p_operator IN VARCHAR2,
1499 p_criterion IN VARCHAR2
1500 ) RETURN VARCHAR2 IS
1501 ---- local variables ----
1502 l_return_val VARCHAR2(1) := FND_API.G_FALSE;
1503 l_number_input NUMBER := NULL;
1504 l_date_field DATE := NULL;
1505 ---- cursors ----
1506 CURSOR cur_get_resolve_by_date(p_repair_line_id NUMBER) IS
1507 SELECT resolve_by_date
1508 FROM csd_repairs
1509 WHERE repair_line_id = p_repair_line_id
1510 ; --* end CURSOR get_resolve_by_date *--
1511
1512 BEGIN
1513 IF (p_repair_line_id IS NOT NULL) THEN
1514 OPEN cur_get_resolve_by_date (p_repair_line_id);
1515 FETCH cur_get_resolve_by_date into l_date_field;
1516 CLOSE cur_get_resolve_by_date;
1517
1518 --** debug starts!!
1519 --dbms_output.put_line('==== CHECK_RESOLVE_BY_DATE , l_date_field = ' || l_date_field);
1520 --** debug ends!!
1521
1522 IF (l_date_field IS NOT NULL) AND (l_date_field <> FND_API.G_MISS_DATE) THEN
1523 l_number_input := l_date_field - sysdate; --get # days for sysdate resolve by date
1524 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_number_input,
1525 p_operator => p_operator,
1526 p_criterion => to_number(p_criterion));
1527 END IF; --* end IF (l_date_field IS NOT NULL) ...*--
1528 END IF; -- end IF ( l_repair_line_id IS NOT NULL) *--
1529
1530 RETURN l_return_val;
1531 END CHECK_RESOLVE_BY_DATE;
1532
1533
1534 /*--------------------------------------------------------------------*/
1535 /* procedure name: CHECK_RETURN_BY_DATE */
1536 /* description : retrieves return by date on logistics line */
1537 /* '%' => RMA_THIRD_PARTY line */
1538 /* loaner => RMA line */
1539 /* exchange => RMA line */
1540 /* compare threshold with return by date - sysdate */
1541 /* -- swai: bug 7524870 - only return a match if the */
1542 /* line has not been received AND the condition matches */
1543 /* */
1544 /* Called from : FUNCTION MATCH_CONDITION */
1545 /* Input Parm : */
1546 /* p_repair_line_id NUMBER Req */
1547 /* p_action_type VARCHAR2 Req */
1548 /* p_action_code VARCHAR2 Req */
1549 /* Return Val : */
1550 /* VARCHAR2 - G_TRUE or G_FALSE */
1551 /* */
1552 /* Change Hist : Jan-14-08 rfieldma created */
1553 /* */
1554 /* */
1555 /* */
1556 /*--------------------------------------------------------------------*/
1557 FUNCTION CHECK_RETURN_BY_DATE(
1558 p_repair_line_id IN NUMBER,
1559 p_action_type IN VARCHAR2,
1560 p_action_code IN VARCHAR2,
1561 p_operator IN VARCHAR2,
1562 p_criterion IN VARCHAR2
1563 ) RETURN VARCHAR2 IS
1564 ---- local variables ----
1565 l_return_val VARCHAR2(1) := FND_API.G_FALSE;
1566 l_number_input NUMBER := NULL;
1567 l_date_field DATE := NULL;
1568 l_prod_txn_status VARCHAR(30) := NULL;
1569 ---- cursors ----
1570 CURSOR cur_get_return_by_date(p_repair_line_id NUMBER,
1571 p_action_type VARCHAR2,
1572 p_action_code VARCHAR2) IS
1573 SELECT return_by_date, prod_txn_status
1574 FROM csd_product_txns_v
1575 WHERE action_type = p_action_type
1576 AND action_code LIKE p_action_code -- for 3rd party, pass in '%'
1577 AND repair_line_id = p_repair_line_id
1578 ; --* end CURSOR get_return_by_date *--
1579
1580 BEGIN
1581 IF (p_repair_line_id IS NOT NULL) THEN
1582 OPEN cur_get_return_by_date (p_repair_line_id, p_action_type, p_action_code);
1583 FETCH cur_get_return_by_date into l_date_field, l_prod_txn_status;
1584 CLOSE cur_get_return_by_date;
1585
1586 --** debug starts!!
1587 --dbms_output.put_line('+++ CHECK_REPEAT_REPAIR , l_date_field = ' || l_date_field);
1588 --** debug ends!!
1589
1590 IF (l_date_field IS NOT NULL) AND (l_date_field <> FND_API.G_MISS_DATE) THEN
1591 -- swai: bug 7524870 if the line has been received, do not return a match
1592 if (l_prod_txn_status = 'RECEIVED') then
1593 l_return_val := FND_API.G_FALSE;
1594 else
1595 l_number_input := l_date_field - sysdate;
1596 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_number_input,
1597 p_operator => p_operator,
1598 p_criterion => to_number(p_criterion));
1599 end if;
1600 END IF; --* end IF (l_date_field IS NOT NULL) ...*--
1601 END IF; --* end IF (l_repair_line_id IS NOT NULL) *--
1602
1603 RETURN l_return_val;
1604
1605 END CHECK_RETURN_BY_DATE;
1606
1607 /*--------------------------------------------------------------------*/
1608 /* procedure name: CHECK_REPEAT_REPAIR */
1609 /* description : 1) get instance id based on repair_line_id */
1610 /* 2) get the lastest repair based on the instance id */
1611 /* (order by closed_date desc ) */
1612 /* NOTE: ideally, we would like to use the ship date */
1613 /* on the logistics line. But due to the */
1614 /* complexity, we are using closed_date for */
1615 /* this release. */
1616 /* Called from : FUNCTION MATCH_CONDITION */
1617 /* Input Parm : */
1618 /* p_repair_line_id NUMBER Req */
1619 /* Return Val : */
1620 /* VARCHAR2 - G_TRUE or G_FALSE */
1621 /* */
1622 /* Change Hist : Jan-14-08 rfieldma created */
1623 /* */
1624 /* */
1625 /* */
1626 /*--------------------------------------------------------------------*/
1627 FUNCTION CHECK_REPEAT_REPAIR(
1628 p_repair_line_id IN NUMBER,
1629 p_operator IN VARCHAR2,
1630 p_criterion IN VARCHAR2
1631 ) RETURN VARCHAR2 IS
1632 ---- local variables ----
1633 l_return_val VARCHAR2(1) := FND_API.G_FALSE;
1634 l_number_input NUMBER := NULL;
1635 l_date_field DATE := NULL;
1636 l_creation_date DATE := NULL;
1637 l_instance_id NUMBER := NULL;
1638 ---- cursors ----
1639 -- need to redo this query based on ship date, could return
1640 CURSOR cur_get_latest_repair_date(p_instance_id NUMBER) IS
1641 SELECT MAX(a.date_closed)
1642 FROM csd_repairs a
1643 WHERE a.customer_product_id = p_instance_id
1644 AND a.date_closed IS NOT NULL
1645 ; --* end cur_get_latest_repair_date *--
1646
1647 CURSOR cur_get_creation_date(p_repair_line_id NUMBER) IS
1648 SELECT creation_date
1649 FROM csd_repairs
1650 WHERE repair_line_id = p_repair_line_id
1651 ; -- end* cur_get_creation_date*--
1652 BEGIN
1653 IF (p_repair_Line_id IS NOT NULL) THEN
1654 OPEN cur_get_creation_date (p_repair_line_id);
1655 FETCH cur_get_creation_date INTO l_creation_date;
1656 CLOSE cur_get_creation_date;
1657
1658 IF (l_creation_date IS NOT NULL) AND (l_creation_date <> FND_API.G_MISS_DATE) THEN
1659 l_instance_id := GET_RO_INSTANCE_ID(p_repair_line_id);
1660
1661 IF (l_instance_id IS NOT NULL) AND (l_instance_id <> FND_API.G_MISS_NUM) THEN
1662 -- found instance id, so get latest repair date
1663 OPEN cur_get_latest_repair_date(l_instance_id);
1664 FETCH cur_get_latest_repair_date INTO l_date_field;
1665 CLOSE cur_get_latest_repair_date;
1666
1667 --** debug starts!!
1668 --dbms_output.put_line('+++ CHECK_REPEAT_REPAIR , l_date_field = ' || l_date_field);
1669 --** debug ends!!
1670
1671 IF (l_date_field IS NOT NULL) AND (l_date_field <> FND_API.G_MISS_DATE) THEN
1672 l_number_input := sysdate - l_date_field;
1673 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_number_input,
1674 p_operator => p_operator,
1675 p_criterion => to_number(p_criterion));
1676 END IF; --* end IF (l_date_field IS NOT NULL).. *--
1677 END IF; --* end IF(l_instance_id IS NOT NULL).. *--
1678 END IF; --* end IF (l_createion_date IS NOT NULL)...*--
1679 END IF;--* end IF (p_repair_Line_id IS NOT NULL).. *--
1680
1681 RETURN l_return_val;
1682 END CHECK_REPEAT_REPAIR;
1683
1684 /*--------------------------------------------------------------------*/
1685 /* procedure name: CHECK_CHRONIC_REPAIR */
1686 /* description : 1) get instance id based on repair_line_id */
1687 /* 2) get profile option CSD_QUALITY_CHECK_PERIOD value */
1688 /* 3) query # of repair orders during this period */
1689 /* (closed_date) */
1690 /* NOTE: ideally, we would like to use the ship date */
1691 /* on the logistics line. But due to the */
1692 /* complexity, we are using closed_date for */
1693 /* this release. */
1694 /* Called from : FUNCTION MATCH_CONDITION */
1695 /* Input Parm : */
1696 /* p_repair_line_id NUMBER Req */
1697 /* Return Val : */
1698 /* VARCHAR2 - G_TRUE or G_FALSE */
1699 /* */
1700 /* Change Hist : Jan-14-08 rfieldma created */
1701 /* */
1702 /* */
1703 /* */
1704 /*--------------------------------------------------------------------*/
1705 FUNCTION CHECK_CHRONIC_REPAIR(
1706 p_repair_line_id IN NUMBER,
1707 p_operator IN VARCHAR2,
1708 p_criterion IN VARCHAR2
1709 )RETURN VARCHAR2 IS
1710 ---- local variables ----
1711 l_return_val VARCHAR2(1) := FND_API.G_FALSE;
1712 l_number_input NUMBER := NULL;
1713 l_instance_id NUMBER := NULL;
1714 l_period NUMBER := NULL;
1715
1716 ---- cursors ----
1717 -- need to redo this query based on ship date
1718 CURSOR cur_get_chronic_repairs(p_instance_id NUMBER,
1719 p_period NUMBER) IS
1720 SELECT count(a.repair_line_id)
1721 FROM csd_repairs a
1722 WHERE a.customer_product_id = p_instance_id
1723 AND a.date_closed BETWEEN sysdate - p_period
1724 AND sysdate
1725 ; --* end cur_get_chronic_repairs *--
1726 BEGIN
1727 IF (p_repair_line_id IS NOT NULL) THEN
1728 l_period := FND_PROFILE.VALUE(G_PROFILE_QUALITY_CHECK_PERIOD);
1729
1730
1731 --** debug starts!!
1732 --dbms_output.put_line('+++>> CHECK_CHRONIC_REPAIR , l_period = ' || l_period);
1733 --** debug ends!!
1734
1735 l_instance_id := GET_RO_INSTANCE_ID(p_repair_line_id);
1736 IF (l_instance_id IS NOT NULL) AND (l_instance_id <> FND_API.G_MISS_NUM) THEN
1737 --* found instance id, so get the number of repairs in period
1738 OPEN cur_get_chronic_repairs(l_instance_id, l_period);
1739 FETCH cur_get_chronic_repairs into l_number_input;
1740 CLOSE cur_get_chronic_repairs;
1741 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_number_input,
1742 p_operator => p_operator,
1743 p_criterion => to_number(p_criterion));
1744 END IF; --* end (l_instance_id IS NOT NULL).. *--
1745 END IF; --* end IF (p_repair_Line_id IS NOT NULL).. *--
1746
1747 RETURN l_return_val;
1748
1749 END;
1750
1751
1752 /*--------------------------------------------------------------------*/
1753 /* procedure name: CHECK_CONTRACT_EXP_DATE */
1754 /* description : calls OKS_ENTITLEMENTS_PUB.Get_Contracts_Expiration */
1755 /* checks threshold with exp date - sysdate */
1756 /* */
1757 /* Called from : FUNCTION MATCH_CONDITION */
1758 /* Input Parm : */
1759 /* p_repair_line_id NUMBER Req */
1760 /* */
1761 /* */
1762 /* Return Val : */
1763 /* VARCHAR2 - G_TRUE or G_FALSE */
1764 /* */
1765 /* Change Hist : Jan-14-08 rfieldma created */
1766 /* */
1767 /* */
1768 /* */
1769 /*--------------------------------------------------------------------*/
1770 FUNCTION CHECK_CONTRACT_EXP_DATE(
1771 p_repair_line_id IN NUMBER,
1772 p_operator IN VARCHAR2,
1773 p_criterion IN VARCHAR2
1774 ) RETURN VARCHAR2 IS
1775 ---- local variables ----
1776 l_return_val VARCHAR2(1) := FND_API.G_FALSE;
1777 l_number_input NUMBER := NULL;
1778 l_ro_contract_id NUMBER := NULL;
1779
1780 l_return_status VARCHAR2(1) := NULL;
1781 l_msg_count NUMBER := NULL;
1782 l_msg_data VARCHAR2(2000) := NULL;
1783 l_contract_end_date DATE := NULL;
1784
1785 ---- cursors ----
1786 CURSOR cur_get_ro_contract_id(p_repair_line_id NUMBER) IS
1787 SELECT contract_line_id
1788 FROM csd_repairs
1789 WHERE repair_line_id = p_repair_line_id
1790 ; --* end cur_get_ro_contract_id *--
1791
1792 -- bug 7323831 - contract line can expire if either end date or termination date
1793 -- has passed. If either date is null, do not consider it as a valid date for.
1794 -- comparison. If both end and termination dates are null, then return null.
1795 CURSOR cur_get_contract_end_date(p_contract_line_id NUMBER) IS
1796 SELECT least(nvl(end_date, date_terminated), nvl(date_terminated, end_date))
1797 FROM OKC_K_Lines_B
1798 WHERE id = p_contract_line_id
1799 ;
1800
1801 BEGIN
1802 IF (p_repair_Line_id IS NOT NULL) THEN
1803 OPEN cur_get_ro_contract_id (p_repair_line_id);
1804 FETCH cur_get_ro_contract_id INTO l_ro_contract_id;
1805 CLOSE cur_get_ro_contract_id;
1806
1807 --** debug starts!!
1808 --dbms_output.put_line('+++-- CHECK_CONTRACT_EXP_DATE , l_ro_contract_id = ' || l_ro_contract_id);
1809 --** debug ends!!
1810
1811 IF (l_ro_contract_id IS NOT NULL) AND (l_ro_contract_id <> FND_API.G_MISS_NUM) THEN
1812 OPEN cur_get_contract_end_date (l_ro_contract_id);
1813 FETCH cur_get_contract_end_date INTO l_contract_end_date;
1814 CLOSE cur_get_contract_end_date;
1815
1816 --** debug starts!!
1817 --dbms_output.put_line('+++-- CHECK_CONTRACT_EXP_DATE , l_contract_end_date = ' || l_contract_end_date);
1818 --** debug ends!!
1819
1820
1821 IF (l_contract_end_date IS NOT NULL) AND (l_contract_end_date <> FND_API.G_MISS_DATE) THEN
1822 l_number_input := l_contract_end_date - sysdate;
1823 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_number_input,
1824 p_operator => p_operator,
1825 p_criterion => to_number(p_criterion));
1826 END IF; --* end IF (l_contract_end_date IS NOT NULL)...*--
1827 END IF; --* end IF (l_ro_contract_id IS NOT NULL)... *--
1828 END IF; --* end IF (p_repair_Line_id IS NOT NULL)... *--
1829
1830
1831 RETURN l_return_val;
1832 END CHECK_CONTRACT_EXP_DATE;
1833
1834
1835 /* probably should be moved to util package */
1836 /*--------------------------------------------------------------------*/
1837 /* procedure name: GET_RO_INSTANCE_ID */
1838 /* description : returns customer_producet_id of the SR header for */
1839 /* the repair line */
1840 /* */
1841 /* Called from : FUNCTION MATCH_CONDITION */
1842 /* Input Parm : */
1843 /* p_contract_id NUMBER Req */
1844 /* */
1845 /* */
1846 /* Return Val : */
1847 /* NUMBER - Instance ID */
1848 /* */
1849 /* Change Hist : Jan-14-08 rfieldma created */
1850 /* */
1851 /* */
1852 /* */
1853 /*--------------------------------------------------------------------*/
1854 FUNCTION GET_RO_INSTANCE_ID(
1855 p_repair_line_id IN NUMBER
1856 ) RETURN NUMBER IS
1857 ---- local variables ----
1858 l_instance_id NUMBER := NULL;
1859 ---- cursors ----
1860 CURSOR cur_get_instance_id(p_repair_line_id NUMBER) IS
1861 SELECT customer_product_id
1862 FROM csd_repairs
1863 WHERE repair_line_id = p_repair_line_id
1864 ;--* end cur_get_instance_id *--
1865 BEGIN
1866 IF (p_repair_Line_id IS NOT NULL) THEN
1867 OPEN cur_get_instance_id(p_repair_line_id);
1868 FETCH cur_get_instance_id INTO l_instance_id;
1869
1870 IF ( cur_get_instance_id%NOTFOUND) OR (l_instance_id IS NULL) OR (l_instance_Id = FND_API.G_MISS_NUM) THEN -- no instance, do nothing
1871 l_instance_id := NULL; -- force value to null
1872 END IF;
1873 CLOSE cur_get_instance_id;
1874 END IF; --* end IF (p_repair_Line_id IS NOT NULL) *--
1875
1876 --** debug starts!!
1877 --dbms_output.put_line(' *helper* GET_RO_INSTANCE_ID l_instance_id = ' || l_instance_id);
1878 --** debug ends!!
1879
1880 RETURN l_instance_id;
1881 END GET_RO_INSTANCE_ID;
1882
1883
1884 /*--------------------------------------------------------------------*/
1885 /* function name: GET_RULE_SQL_FOR_RO */
1886 /* description : Given a single rule, generate a sql query */
1887 /* that will match all repair orders for all its */
1888 /* conditions */
1889 /* */
1890 /* Called from : PROCEDURE LINK_BULLETIN_FOR_RULE */
1891 /* Input Parm : */
1892 /* l_rule_condition_rec CSD_RULE_CONDITION_REC_TYPE Req */
1893 /* */
1894 /* */
1895 /* Return Val : */
1896 /* VARCHAR2 - SQL Query to get ROs for rule condition */
1897 /* */
1898 /*--------------------------------------------------------------------*/
1899 FUNCTION GET_RULE_SQL_FOR_RO(
1900 p_rule_id IN NUMBER
1901 ) RETURN VARCHAR2
1902 IS
1903 -- CURSORS --
1904 CURSOR c_rule_conditions (p_rule_id number) IS
1905 SELECT rule_condition_id,
1906 rule_id,
1907 attribute_category,
1908 attribute1,
1909 attribute2,
1910 attribute3,
1911 attribute4,
1912 attribute5,
1913 attribute6,
1914 attribute7,
1915 attribute8,
1916 attribute9,
1917 attribute10,
1918 attribute11,
1919 attribute12,
1920 attribute13,
1921 attribute14,
1922 attribute15
1923 FROM CSD_RULE_CONDITIONS_B
1924 WHERE rule_id = p_rule_id;
1925
1926 -- VARIABLES --
1927 l_sql_query VARCHAR2(32767) := null;
1928 l_rule_condition_rec CSD_RULES_ENGINE_PVT.CSD_RULE_CONDITION_REC_TYPE;
1929 l_join_stmt VARCHAR2(3000) := null;
1930 l_operator VARCHAR2 (2);
1931 l_num_condition VARCHAR2(3000); -- for conditions that match a number type
1932 l_str_condition VARCHAR2(3000); -- for conditions that match a string type
1933 l_condition_count NUMBER :=0;
1934
1935 BEGIN
1936 l_sql_query := 'select dra.repair_line_id from csd_repairs dra, cs_incidents_b_sec csb '
1937 || 'where csb.incident_id = dra.incident_id';
1938 OPEN c_rule_conditions(p_rule_id);
1939 LOOP
1940 FETCH c_rule_conditions into l_rule_condition_rec;
1941 EXIT WHEN c_rule_conditions%NOTFOUND;
1942
1943 -- if there is a condition to be processed, then increment the count
1944 l_condition_count := l_condition_count + 1;
1945
1946 -- try to build the the join statement
1947 l_join_stmt := null;
1948 l_operator := GET_SQL_OPERATOR(l_rule_condition_rec.attribute1);
1949 IF (l_operator is not null) AND (l_rule_condition_rec.attribute2 is not null) THEN
1950 l_num_condition := l_operator || ' ' || l_rule_condition_rec.attribute2;
1951 l_str_condition := l_operator || ' ''' || l_rule_condition_rec.attribute2 || '''';
1952
1953 case l_rule_condition_rec.attribute_category
1954 when 'USER_ID' then
1955 l_join_stmt := 'FND_GLOBAL.USER_ID ' || l_num_condition;
1956 when 'USER_RESPONSIBILITY' then
1957 l_join_stmt := 'FND_GLOBAL.RESP_ID ' || l_num_condition;
1958 when 'USER_INV_ORG' then
1959 l_join_stmt := 'FND_PROFILE.VALUE(''' || G_PROFILE_INV_ORG || ''') '
1960 || l_num_condition;
1961 when 'USER_OU' then
1962 l_join_stmt := 'FND_GLOBAL.ORG_ID ' || l_num_condition;
1963 when 'SR_CUSTOMER_ID' then
1964 l_join_stmt := 'csb.customer_id ' || l_num_condition;
1965 when 'SR_CUSTOMER_ACCOUNT_ID' then
1966 l_join_stmt := 'csb.account_id ' || l_num_condition;
1967 when 'SR_BILL_TO_COUNTRY' then
1968 l_join_stmt := 'csb.bill_to_site_use_id in (select hpsu.party_site_use_id'
1969 || ' from hz_party_sites hps, hz_locations hl, hz_party_site_uses hpsu'
1970 || ' where hps.location_id = hl.location_id'
1971 || ' and hps.party_site_id = hpsu.party_site_id'
1972 || ' and hpsu.party_site_use_id = csb.bill_to_site_use_id'
1973 || ' and hl.country '
1974 || l_str_condition || ')';
1975 when 'SR_SHIP_TO_COUNTRY' then
1976 l_join_stmt := 'csb.ship_to_site_use_id in (select hpsu.party_site_use_id'
1977 || ' from hz_party_sites hps, hz_locations hl, hz_party_site_uses hpsu'
1978 || ' where hps.location_id = hl.location_id'
1979 || ' and hps.party_site_id = hpsu.party_site_id'
1980 || ' and hpsu.party_site_use_id = csb.ship_to_site_use_id'
1981 || ' and hl.country '
1982 || l_str_condition || ')';
1983 when 'SR_ITEM_ID' then
1984 l_join_stmt := 'csb.inventory_item_id ' || l_num_condition;
1985 when 'SR_ITEM_CATEGORY_ID' then
1986 l_join_stmt := 'csb.category_id ' || l_num_condition;
1987 when 'SR_CONTRACT_ID' then
1988 l_join_stmt := 'csb.contract_id ' || l_num_condition;
1989 when 'SR_PROBLEM_CODE' then
1990 l_join_stmt := 'csb.problem_code ' || l_str_condition;
1991 -- swai: 12.1.1 ER 7233924
1992 when 'RO_ITEM_ID' then
1993 l_join_stmt := 'dra.inventory_item_id ' || l_num_condition;
1994 -- swai: 12.1.1 ER 7233924
1995 when 'RO_ITEM_CATEGORY_ID' then
1996 if (l_rule_condition_rec.attribute1 = 'EQUALS') then
1997 l_join_stmt := 'exists';
1998 elsif(l_rule_condition_rec.attribute1 = 'NOT_EQUALS') then
1999 l_join_stmt := 'not exists';
2000 else
2001 l_join_stmt := null;
2002 end if;
2003
2004 if (l_join_stmt is not null) then
2005 l_join_stmt := l_join_stmt || ' (select ''X'''
2006 || ' from mtl_item_categories_v cat'
2007 || ' where cat.inventory_item_id = dra.inventory_item_id'
2008 || ' and cat.organization_id = cs_std.get_item_valdn_orgzn_id'
2009 || ' and cat.category_id = '
2010 || l_rule_condition_rec.attribute2 || ')';
2011 end if;
2012
2013 when 'RO_PROMISE_DATE_THRESHOLD' then
2014 l_join_stmt := '(dra.promise_date - sysdate) ' || l_num_condition;
2015 when 'RO_RESOLVE_BY_DATE_THRESHOLD' then
2016 l_join_stmt := '(dra.resolve_by_date - sysdate) ' || l_num_condition;
2017 when 'RO_EXCHANGE_THRESHOLD' then
2018 l_join_stmt := 'dra.repair_line_id in (select prod.repair_line_id'
2019 || ' from csd_product_txns_v prod'
2020 || ' WHERE prod.action_type = ''' || G_ACTION_TYPE_RMA || ''''
2021 || ' AND prod.action_code = ''' || G_ACTION_CODE_EXCHANGE || ''''
2022 || ' AND nvl(prod.prod_txn_status, '''') <> ''RECEIVED''' -- swai: bug 7524870
2023 || ' AND (prod.return_by_date - sysdate) '
2024 || l_num_condition || ')';
2025 when 'RO_LOANER_THRESHOLD' then
2026 l_join_stmt := 'dra.repair_line_id in (select prod.repair_line_id'
2027 || ' from csd_product_txns_v prod'
2028 || ' WHERE prod.action_type = ''' || G_ACTION_TYPE_RMA || ''''
2029 || ' AND prod.action_code = ''' || G_ACTION_CODE_LOANER || ''''
2030 || ' AND nvl(prod.prod_txn_status, '''') <> ''RECEIVED''' -- swai: bug 7524870
2031 || ' AND (prod.return_by_date - sysdate) '
2032 || l_num_condition || ')';
2033 when 'RO_THIRD_PTY_THRESHOLD' then
2034 l_join_stmt := 'dra.repair_line_id in (select prod.repair_line_id'
2035 || ' from csd_product_txns_v prod'
2036 || ' WHERE prod.action_type = ''' || G_ACTION_TYPE_RMA_THIRD_PTY || ''''
2037 || ' AND nvl(prod.prod_txn_status, '''') <> ''RECEIVED''' -- swai: bug 7524870
2038 || ' AND (prod.return_by_date - sysdate) '
2039 || l_num_condition || ')';
2040 -- swai: bug 7524870 - add new condition
2041 when 'RO_RMA_CUST_PROD_THRESHOLD' then
2042 l_join_stmt := 'dra.repair_line_id in (select prod.repair_line_id'
2043 || ' from csd_product_txns_v prod'
2044 || ' WHERE prod.action_type = ''' || G_ACTION_TYPE_RMA || ''''
2045 || ' AND prod.action_code = ''' || G_ACTION_CODE_CUST_PROD || ''''
2046 || ' AND nvl(prod.prod_txn_status, '''') <> ''RECEIVED'''
2047 || ' AND (prod.return_by_date - sysdate) '
2048 || l_num_condition || ')';
2049 -- end swai: bug 7524870
2050 when 'RO_REPEAT_REPAIR_THRESHOLD' then
2051 l_join_stmt := 'sysdate - ( SELECT MAX(dra2.date_closed)'
2052 || ' FROM csd_repairs dra2 '
2053 || ' WHERE dra2.customer_product_id = dra.customer_product_id '
2054 || ' AND dra2.date_closed IS NOT NULL) '
2055 || l_num_condition;
2056 when 'RO_CHRONIC_REPAIR_THRESHOLD' then
2057 l_join_stmt := '(SELECT count(dra2.repair_line_id) '
2058 || ' FROM csd_repairs dra2 '
2059 || ' WHERE dra2.customer_product_id = dra.customer_product_id '
2060 || ' AND dra2.date_closed BETWEEN sysdate - '
2061 || ' nvl(FND_PROFILE.VALUE(''CSD_QUALITY_CHECK_PERIOD''), 0) '
2062 || ' AND sysdate) ' || l_num_condition;
2063 -- bug 7323831 - contract line can expire if either end date or termination date
2064 -- has passed. If either date is null, do not consider it as a valid date.
2065 when 'RO_CONTRACT_EXP_THRESHOLD' then
2066 l_join_stmt := 'dra.contract_line_id in (select okl.id'
2067 || ' from okc_k_lines_b okl'
2068 || ' where okl.id = dra.contract_line_id'
2069 || ' AND (least(nvl(end_date, date_terminated), nvl(date_terminated, end_date)) - sysdate)'
2070 -- || ' AND (okl.end_date - sysdate) '
2071 || l_num_condition || ')';
2072 end case;
2073 end if;
2074
2075 -- If unsuccessful in building join statement, create one
2076 -- that will always make this query return no rows
2077 IF (l_join_stmt is null) THEN
2078 l_join_stmt := ' 1=0 ';
2079 END IF;
2080
2081 -- append the join statement to the existing query
2082 l_sql_query := l_sql_query || ' AND ' || l_join_stmt;
2083
2084 END LOOP;
2085
2086 -- if there were no conditions in the rule, then ensure that the query
2087 -- returns no rows, since a rule without conditions is not applicable
2088 -- to any repair order.
2089 IF (l_condition_count = 0) THEN
2090 l_sql_query := l_sql_query || ' AND 1=0';
2091 END IF;
2092
2093 RETURN l_sql_query;
2094 END GET_RULE_SQL_FOR_RO;
2095
2096
2097
2098 /*--------------------------------------------------------------------*/
2099 /* function name: GET_SQL_OPERATOR */
2100 /* description : Turns the given operator into the corresponding */
2101 /* operator symbol used in a sql query */
2102 /* */
2103 /* Called from : FUNCTION GET_RULE_SQL_FOR_RO */
2104 /* Input Parm : */
2105 /* p_operator VARCHAR2 Req */
2106 /* */
2107 /* */
2108 /* Return Val : */
2109 /* VARCHAR2 - Operator Lookup code from CSD_RULE_OPERATORS */
2110 /* */
2111 /*--------------------------------------------------------------------*/
2112 FUNCTION GET_SQL_OPERATOR (
2113 p_operator IN VARCHAR2
2114 ) RETURN VARCHAR2
2115 IS
2116 BEGIN
2117 CASE P_OPERATOR
2118 when G_EQUALS then
2119 return '=';
2120 when G_NOT_EQUALS then
2121 return '<>';
2122 when G_GREATER_THAN then
2123 return '>';
2124 when G_LESS_THAN then
2125 return '<';
2126 else
2127 return null;
2128 END CASE;
2129 END GET_SQL_OPERATOR;
2130
2131 END CSD_RULES_ENGINE_PVT; /* package ends here */