[Home] [Help]
PACKAGE BODY: APPS.CSD_RULES_ENGINE_PVT
Source
4 -- Package name : CSD_RULES_ENGINE_PVT
1 PACKAGE BODY CSD_RULES_ENGINE_PVT as
2 /* $Header: csdvrulb.pls 120.16.12020000.5 2013/04/04 11:06:09 subhat ship $ */
3 -- Start of Comments
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 G_INCIDENT_ID NUMBER;
14 -- bug#14029797, subhat. Associative array cache for associating SR Ship to city --> ship_to_site_use_id
15 TYPE SHIP_TO_CITY_CACHE IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
16 G_SHIP_TO_CITY_CACHE SHIP_TO_CITY_CACHE;
17
18 FUNCTION CHECK_MTL_TXN_ITEM_CAT(p_mtl_txn_item_id IN NUMBER,
19 p_wip_entity_id IN NUMBER,
20 p_operator IN VARCHAR2,
21 p_criterion IN NUMBER
22 )
23 RETURN VARCHAR2
24 IS
25 l_return_val VARCHAR2(1);
26 BEGIN
27 BEGIN
28 SELECT fnd_api.g_true
29 INTO l_return_val
30 FROM mtl_item_categories mic,wip_entities we
31 WHERE mic.inventory_item_id = p_mtl_txn_item_id
32 AND mic.category_id = p_criterion
33 AND mic.organization_id = we.organization_id
34 AND we.wip_entity_id = p_wip_entity_id;
35 EXCEPTION
36 WHEN no_data_found THEN
37 l_return_val := fnd_api.g_false;
38 END;
39 if p_operator = G_NOT_EQUALS
40 then
41 if l_return_val = fnd_api.g_true
42 then
43 l_return_val := fnd_api.g_false;
44 else
45 l_return_val := fnd_api.g_true;
46 end if;
47 end if;
48
49 return l_return_val;
50
51 END CHECK_MTL_TXN_ITEM_CAT;
52
53
54 /*--------------------------------------------------------------------*/
55 /* procedure name: PROCESS_RULE_MATCHING */
56 /* description : procedure used to Match Rules with input data */
57 /* */
58 /* */
59 /* */
60 /* Called from : Depot Repair Bulletins */
61 /* Input Parm : */
62 /* p_api_version NUMBER Req Api Version number */
63 /* p_init_msg_list VARCHAR2 Opt Initialize message stack */
64 /* p_commit VARCHAR2 Opt Commits in API */
65 /* p_validation_level NUMBER Opt validation steps */
66 /* px_rule_matching_rec CSD_RULE_MATCHING_REC_TYPE */
67 /* Output Parm : */
68 /* x_return_status VARCHAR2 Return status after the call. */
69 /* x_msg_count NUMBER Number of messages in stack */
70 /* x_msg_data VARCHAR2 Mesg. text if x_msg_count >= 1 */
71 /* Change Hist : Jan-14-08 rfieldma created */
72 /* */
73 /* */
74 /* */
75 /*--------------------------------------------------------------------*/
76 PROCEDURE PROCESS_RULE_MATCHING(
77 p_api_version_number IN NUMBER,
78 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
79 p_commit IN VARCHAR2 := FND_API.G_FALSE,
80 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
81 px_rule_matching_rec IN OUT NOCOPY CSD_RULE_MATCHING_REC_TYPE,
82 x_return_status OUT NOCOPY VARCHAR2,
83 x_msg_count OUT NOCOPY NUMBER,
84 x_msg_data OUT NOCOPY VARCHAR2
85 )
86 IS
87 ---- local constants ----
88 c_TEMP_CHAR CONSTANT VARCHAR(1) := 'B'; -- used to pass as attribute type/code for bulletin
89 c_API_NAME CONSTANT VARCHAR2(30) := 'PROCESS_RULE_MATCHING';
90 c_API_VERSION_NUMBER CONSTANT NUMBER := G_L_API_VERSION_NUMBER;
91
92 ---- local variables ----
93 l_rule_type VARCHAR2(30) := NULL;
94 l_attr_type VARCHAR2(30) := NULL;
95 l_attr_code VARCHAR2(30) := NULL;
96 l_event_type VARCHAR2(30) := NULL;
97 l_match_condition_ret_val VARCHAR2(1) := FND_API.G_FALSE;
98 l_is_match VARCHAR2(1) := FND_API.G_FALSE;
99 l_tbl_last_ind NUMBER := 1;
103 --* Cursor: cur_get_rules *--
100 l_repair_line_id NUMBER := NULL;
101
102 ---- cursors ----
104 --* return rules that match rule_type, attr_type, attr_code *--
105 CURSOR cur_get_rules(p_rule_type VARCHAR2,
106 p_attr_type VARCHAR2,
107 p_attr_code VARCHAR2,
108 p_event_type VARCHAR2) IS
109 SELECT attribute1, attribute2, attribute3, attribute4, rule_id, value_type_code, attribute_category
110 FROM csd_rules_b
111 where rule_type_code = p_rule_type
112 AND NVL(entity_attribute_type, c_TEMP_CHAR)=NVL(p_attr_type, c_TEMP_CHAR)
113 AND NVL(entity_attribute_code, c_TEMP_CHAR)=NVL(p_attr_code, c_TEMP_CHAR)
114 AND attribute1 = decode(p_rule_type,
115 'SOO', p_event_type,
116 attribute1)
117 ORDER BY precedence
118 ; --* end CURSOR cur_get_rules(..) *--
119
120 l_rule_rec cur_get_rules%ROWTYPE;
121
122 --* Cursor: cur_get_rule_conditions *--
123 --* return all conditions for a given rule *--
124 CURSOR cur_get_rule_conditions(p_rule_id NUMBER, p_rule_type VARCHAR2) IS
125 SELECT decode(p_rule_type, 'SOO', soo_attribute_category, attribute_category) attribute_category,
126 decode(p_rule_type, 'SOO', soo_attribute1, attribute1) attribute1,
127 decode(p_rule_type, 'SOO', soo_attribute2, attribute2) attribute2
128 FROM csd_rule_conditions_b
129 WHERE rule_id = p_rule_id
130 ; --* end CURSOR cur_get_rule_conditions(..) *--
131
132 l_rule_cond_rec cur_get_rule_conditions%ROWTYPE;
133
134 BEGIN
135 --* Standard Start of API savepoint
136 SAVEPOINT PROCESS_RULE_MATCHING_PVT;
137
138 --* Standard call to check for call compatibility.
139 IF NOT FND_API.Compatible_API_Call ( c_API_VERSION_NUMBER,
140 p_api_version_number,
141 c_API_NAME,
142 G_PKG_NAME)
143 THEN
144 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
145 END IF;
146
147 -- Initialize message list if p_init_msg_list is set to TRUE.
148 IF FND_API.to_Boolean( p_init_msg_list )
149 THEN
150 FND_MSG_PUB.initialize;
151 END IF;
152
153 -- Initialize API return status to SUCCESS
154 x_return_status := FND_API.G_RET_STS_SUCCESS;
155
156 --* logic starts here *--
157
158 --* populate p_rule_input_rec
159 l_repair_line_id := PX_RULE_MATCHING_REC.RULE_INPUT_REC.repair_line_id;
160 IF ( l_repair_line_id IS NOT NULL) THEN
161 POPULATE_RULE_INPUT_REC(PX_RULE_MATCHING_REC.RULE_INPUT_REC, l_repair_line_id);
162 END IF; --* end IF ( l_repair_line_id IS NULL) *--
163
164 /* BEGIN: Algorithm:
165 * (1) Query for all ACTIVE rules that match:
166 * px_rule_matching_rec.ENTITY_ATTRIBUTE_CODE
167 * and px_rule_matching_rec.ENTITY_ATTRIBUTE_TYPE
168 * and px_rule_matching_rec.RULE_TYPE
169 * order by PRECEDENCE (ascending).
170 * (2) For each rule:
171 * (a) Query the list of the conditions for the rule
172 * (b) l_is_match := 'T' -- assume the condition is match unless proven otherwise.
173 * (c) for each condition,
174 * if (match_condition ( p_parameter_type => attribute_category,
175 * p_operator => attribute1,
176 * p_criterion => attribute2,
177 * p_rule_input_rec => px_rule_matching_rec.RULE_INPUT_REC
178 * ) = G_MISS_G_FALSE )then
179 * l_is_match := 'F'
180 * break; --condition doesn't match, so rule doesn't match.
181 * end if;
182 * end for loop (looping through conditions for a single rule)
183 * (d) if (l_is_match = 'T') then
184 * if (px_csd_rule_matching_rec_type = 1) then
185 * -- we only need to find the first match, so we can exit out of the loop.
186 * px_csd_rule_matching_rec_type.RULE_RESULTS_TBL(1).rule_id = current rule
187 * px_csd_rule_matching_rec_type.RULE_RESULTS_TBL(1).defaulting_value = current rule's attribute1
188 * px_csd_rule_matching_rec_type.RULE_RESULTS_TBL(1).value_type = current rule's value_type_code
189 * break;
190 * end if;
191 * -- if not a match, keep looping through the rules for a match.
192 * end for loop (looping through rules for given defaulting attribute)
193 * END: Algorithm*/
194 l_rule_type := px_rule_matching_rec.RULE_TYPE;
195 l_attr_type := px_rule_matching_rec.ENTITY_ATTRIBUTE_TYPE;
196 l_attr_code := px_rule_matching_rec.ENTITY_ATTRIBUTE_CODE;
197 l_event_type := px_rule_matching_rec.EVENT_TYPE;
198
199 FOR l_rule_rec IN cur_get_rules(l_rule_type, l_attr_type, l_attr_code, l_event_type) LOOP
200 --** debug starts!!
201 --dbms_output.put_line('PROCESS_RULE_MATCHING - LP - get_rules - rule id = ' || l_rule_rec.rule_id || '<-----');
202 --** debug ends!!
203 --* default to false, assume not match unless otherwise returned by match_condition
204 l_is_match := FND_API.G_FALSE;
205 FOR l_rule_cond_rec IN cur_get_rule_conditions(l_rule_rec.rule_id, l_rule_type) LOOP
206 --** debug starts!!
207 --dbms_output.put_line('> PROCESS_RULE_MATCHING - LP - get_rule_cond - attr_cat = ' || l_rule_cond_rec.attribute_category);
208 --dbms_output.put_line('> PROCESS_RULE_MATCHING - LP - get_rule_cond - attr1 = ' || l_rule_cond_rec.attribute1);
212 l_is_match := match_condition(p_parameter_type => l_rule_cond_rec.attribute_category,
209 --dbms_output.put_line('> PROCESS_RULE_MATCHING - LP - get_rule_cond - attr2 = ' || l_rule_cond_rec.attribute2);
210 --** debug ends!!
211
213 p_operator => l_rule_cond_rec.attribute1,
214 p_criterion => l_rule_cond_rec.attribute2,
215 p_rule_input_rec => px_rule_matching_rec.RULE_INPUT_REC
216 );
217
218
219 IF (l_is_match = FND_API.G_FALSE )THEN
220 --** debug starts!!
221 --dbms_output.put_line('PROCESS_RULE_MATCHING -> l_match_condition_ret_val = FALSE - l_is_match = ' || l_is_match);
222 --** debug ends!!
223
224 EXIT; --* a condition didn't match, so no need to go on
225 END IF; --* end IF (l_match_condition_ret_val = FND_API.G_FALSE ) *--
226
227 END LOOP; --* end FOR l_rule_cond_rec... *--
228
229 IF (l_is_match = FND_API.G_TRUE) THEN
230 -- we only need to find the first match, so we can exit out of the loop.
231 l_tbl_last_ind := px_rule_matching_rec.RULE_RESULTS_TBL.COUNT;
232 l_tbl_last_ind := l_tbl_last_ind+1;
233 --** debug starts!!
234 --dbms_output.put_line('PROCESS_RULE_MATCHING -> l_is_match is true, table_count ' || px_rule_matching_rec.RULE_RESULTS_TBL.COUNT);
235 --dbms_output.put_line('PROCESS_RULE_MATCHING -> l_is_match is true, table_ind ' || l_tbl_last_ind);
236 --** debug ends!!
237
238 IF l_rule_type <> 'SOO' THEN
239 px_rule_matching_rec.RULE_RESULTS_TBL(l_tbl_last_ind).rule_id := l_rule_rec.rule_id;
240 px_rule_matching_rec.RULE_RESULTS_TBL(l_tbl_last_ind).defaulting_value := l_rule_rec.attribute1;
241 px_rule_matching_rec.RULE_RESULTS_TBL(l_tbl_last_ind).value_type := l_rule_rec.value_type_code;
242 ELSE
243 px_rule_matching_rec.RULE_RESULTS_TBL(l_tbl_last_ind).rule_id := l_rule_rec.rule_id;
244 px_rule_matching_rec.RULE_RESULTS_TBL(l_tbl_last_ind).ro_flow_status_id := to_number(l_rule_rec.attribute2);
245 px_rule_matching_rec.RULE_RESULTS_TBL(l_tbl_last_ind).flow_status_reason_code := l_rule_rec.attribute3;
246 px_rule_matching_rec.RULE_RESULTS_TBL(l_tbl_last_ind).action := l_rule_rec.attribute4;
247 END IF;
248
249 IF (px_rule_matching_rec.RULE_MATCH_CODE = G_RULE_MATCH_ONE) THEN
250 EXIT;
251 END IF; --* end I(px_rule_matching_rec.RULE_MATCH_CODE = G_RULE_MATCH_ONE) *--
252 END IF; --* end IF (l_is_match = FND_API.G_TRUE) *--
253 --** debug starts!!
254 --dbms_output.put_line('PROCESS_RULE_MATCHING -> before exiting rules loop');
255 --** debug ends!!
256
257 END LOOP; --* end FOR l_rule_rec ... *--
258
259 --* logic ends here *--
260
261 --* Standard check for p_commit
262 IF FND_API.to_Boolean( p_commit ) THEN
263 COMMIT WORK;
264 END IF;
265
266 --** debug starts!!
267 --dbms_output.put_line('PROCESS_RULE_MATCHING -> after commit work');
268 --** debug ends!!
269
270 --* Standard call to get message count and if count is 1, get message info.
271 FND_MSG_PUB.Count_And_Get(
272 p_count => x_msg_count,
273 p_data => x_msg_data
274 );
275
276 --** debug starts!!
277 --dbms_output.put_line('PROCESS_RULE_MATCHING -> after standard call to message count');
278 --** debug ends!!
279
280 EXCEPTION
281 WHEN FND_API.G_EXC_ERROR THEN
282 x_return_status := FND_API.G_RET_STS_ERROR;
283 --** debug starts!!
284 --dbms_output.put_line('PROCESS_RULE_MATCHING -> exception 1');
285 --** debug ends!!
286
287 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
288 P_API_NAME => c_API_NAME
289 ,P_PKG_NAME => G_PKG_NAME
290 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
291 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
292 ,X_MSG_COUNT => X_MSG_COUNT
293 ,X_MSG_DATA => X_MSG_DATA
294 ,X_RETURN_STATUS => X_RETURN_STATUS);
295
296 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
297 x_return_status := FND_API.G_RET_STS_ERROR;
298 --** debug starts!!
299 --dbms_output.put_line('PROCESS_RULE_MATCHING -> exception 2');
300 --** debug ends!!
301
302 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
303 P_API_NAME => c_API_NAME
304 ,P_PKG_NAME => G_PKG_NAME
305 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
306 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
307 ,X_MSG_COUNT => X_MSG_COUNT
308 ,X_MSG_DATA => X_MSG_DATA
309 ,X_RETURN_STATUS => X_RETURN_STATUS);
310
311 WHEN OTHERS THEN
312 x_return_status := FND_API.G_RET_STS_ERROR;
313 --** debug starts!!
314 --dbms_output.put_line('PROCESS_RULE_MATCHING -> exception 3');
315 --** debug ends!!
316
317 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
318 P_API_NAME => c_API_NAME
319 ,P_PKG_NAME => G_PKG_NAME
320 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
321 ,P_SQLCODE => SQLCODE
322 ,P_SQLERRM => SQLERRM
323 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
324 ,X_MSG_COUNT => X_MSG_COUNT
325 ,X_MSG_DATA => X_MSG_DATA
326 ,X_RETURN_STATUS => X_RETURN_STATUS);
327 END PROCESS_RULE_MATCHING;
328
329 PROCEDURE SOO_DEFAULTING (
330 p_api_version_number IN NUMBER,
334 p_event_type IN VARCHAR2,
331 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
332 p_commit IN VARCHAR2 := FND_API.G_FALSE,
333 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
335 p_rule_input_rec IN CSD_RULE_INPUT_REC_TYPE,
336 x_rule_id OUT NOCOPY NUMBER,
337 x_return_status OUT NOCOPY VARCHAR2,
338 x_msg_count OUT NOCOPY NUMBER,
339 x_msg_data OUT NOCOPY VARCHAR2
340 )
341 IS
342 ---- local constants ----
343 c_API_NAME CONSTANT VARCHAR2(30) := 'SOO_DEFAULTING';
344 c_API_VERSION_NUMBER CONSTANT NUMBER := G_L_API_VERSION_NUMBER;
345
346 ---- local variables ----
347 l_rule_matching_rec CSD_RULE_MATCHING_REC_TYPE;
348 l_flow_status_id NUMBER;
349 l_ro_status_reason VARCHAR2(30);
350 l_action VARCHAR2(150);
351 l_tbl_count NUMBER := 0;
352 l_default_rule_id NUMBER := NULL;
353
354 BEGIN
355 --* Standard Start of API savepoint
356 SAVEPOINT GET_SOO_VALUES;
357
358 --* Standard call to check for call compatibility.
359 IF NOT FND_API.Compatible_API_Call ( c_API_VERSION_NUMBER,
360 p_api_version_number,
361 c_API_NAME,
362 G_PKG_NAME)
363 THEN
364 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
365 END IF;
366
367 --* Initialize message list if p_init_msg_list is set to TRUE.
368 IF FND_API.to_Boolean( p_init_msg_list )
369 THEN
370 FND_MSG_PUB.initialize;
371 END IF;
372
373 --* Initialize API return status to SUCCESS
374 x_return_status := FND_API.G_RET_STS_SUCCESS;
375
376 --* logic starts here *--
377
378 --* init l_rule_matching_rec
379 l_rule_matching_rec.rule_match_code := G_RULE_MATCH_ONE;
380 l_rule_matching_rec.rule_type := G_RULE_TYPE_SOO;
381 l_rule_matching_rec.event_type := p_event_type;
382 l_rule_matching_rec.rule_input_rec := p_rule_input_rec;
383
384 PROCESS_RULE_MATCHING(
385 p_api_version_number => p_api_version_number,
386 p_commit => fnd_api.g_false,
387 p_init_msg_list => p_init_msg_list,
388 p_validation_level => p_validation_level,
389 px_rule_matching_rec => l_rule_matching_rec,
390 x_return_status => x_return_status,
391 x_msg_count => x_msg_count,
392 x_msg_data => x_msg_data
393 );
394
395 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
396 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
397 FND_MESSAGE.Set_Name('CSD', 'CSD_RULE_MATCH_FAILED');
398 FND_MSG_PUB.Add;
399 END IF;
400 RAISE FND_API.G_EXC_ERROR;
401 END IF; --* end IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) *--
402
403 l_tbl_count := l_rule_matching_rec.RULE_RESULTS_TBL.COUNT;
404
405 IF (l_tbl_count > 0) THEN
406 l_default_rule_id := l_rule_matching_rec.RULE_RESULTS_TBL(1).RULE_ID;
407 l_flow_status_id := l_rule_matching_rec.RULE_RESULTS_TBL(1).RO_FLOW_STATUS_ID;
408 l_ro_status_reason := l_rule_matching_rec.RULE_RESULTS_TBL(1).FLOW_STATUS_REASON_CODE;
409 l_action := l_rule_matching_rec.RULE_RESULTS_TBL(1).ACTION;
410
411 PROCESS_SOO_RULE(p_api_version_number => p_api_version_number,
412 p_commit => fnd_api.g_false,
413 p_init_msg_list => p_init_msg_list,
414 p_validation_level => p_validation_level,
415 p_ro_flow_status_id => l_flow_status_id,
416 p_flow_status_reason_code => l_ro_status_reason,
417 p_action => l_action,
418 p_rule_input_rec => l_rule_matching_rec.rule_input_rec,
419 x_return_status => x_return_status,
420 x_msg_count => x_msg_count,
421 x_msg_data => x_msg_data);
422
423 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
424 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
425 FND_MESSAGE.Set_Name('CSD', 'CSD_PROCESS_SOO_RULE_FAILED');
426 FND_MSG_PUB.Add;
427 END IF;
428 RAISE FND_API.G_EXC_ERROR;
429 END IF; --* end IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) *--
430
431 END IF; --* end IF (l_tbl_count > 0) *--
432
433 x_rule_id := l_default_rule_id;
434 --* logic ends here *--
435
436 -- Standard check of p_commit.
437 IF FND_API.To_Boolean( p_commit ) THEN
438 COMMIT WORK;
439 END IF;
440
441 x_msg_count := fnd_msg_pub.count_msg;
442
443 EXCEPTION
444 WHEN FND_API.G_EXC_ERROR THEN
445 x_return_status := FND_API.G_RET_STS_ERROR;
446 x_msg_count := fnd_msg_pub.count_msg;
447 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
448 x_return_status := FND_API.G_RET_STS_ERROR;
449 x_msg_count := fnd_msg_pub.count_msg;
450 WHEN OTHERS THEN
451 x_return_status := FND_API.G_RET_STS_ERROR;
452 x_msg_count := fnd_msg_pub.count_msg;
453
454 END SOO_DEFAULTING;
455
456 PROCEDURE PROCESS_SOO_RULE(
457 p_api_version_number IN NUMBER,
458 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
462 p_flow_status_reason_code IN VARCHAR2,
459 p_commit IN VARCHAR2 := FND_API.G_FALSE,
460 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
461 p_ro_flow_status_id IN NUMBER,
463 p_action IN VARCHAR2,
464 p_rule_input_rec IN CSD_RULE_INPUT_REC_TYPE,
465 x_return_status OUT NOCOPY VARCHAR2,
466 x_msg_count OUT NOCOPY NUMBER,
467 x_msg_data OUT NOCOPY VARCHAR2
468 ) IS
469
470 c_API_NAME CONSTANT VARCHAR2(30) := 'PROCESS_SOO_RULE';
471
472 ---- local constants ----
473 c_BEGIN VARCHAR2(6) := 'BEGIN ';
474 c_END VARCHAR2(6) := '; END;';
475
476 ---- local variables ----
477 l_sql_stmt VARCHAR2(2000) := NULL;
478 l_pos NUMBER;
479
480 l_object_version_number NUMBER;
481 x_object_version_number number;
482
483 l_repair_status_rec CSD_REPAIRS_PUB.REPAIR_STATUS_REC_TYPE;
484 l_status_upd_control_rec CSD_REPAIRS_PUB.STATUS_UPD_CONTROL_REC_TYPE;
485
486 BEGIN
487 SAVEPOINT PROCESS_SOO_RULE;
488
489 --* Standard call to check for call compatibility.
490 IF NOT FND_API.Compatible_API_Call ( G_L_API_VERSION_NUMBER,
491 p_api_version_number,
492 c_API_NAME,
493 G_PKG_NAME)
494 THEN
495 RAISE FND_API.G_EXC_ERROR;
496 END IF;
497
498 --* Initialize message list if p_init_msg_list is set to TRUE.
499 IF FND_API.to_Boolean( p_init_msg_list )
500 THEN
501 FND_MSG_PUB.initialize;
502 END IF;
503
504 --* Initialize API return status to SUCCESS
505 x_return_status := FND_API.G_RET_STS_SUCCESS;
506
507
508 IF p_ro_flow_status_id is not null THEN
509 select cr.object_version_number
510 into l_object_version_number
511 from csd_repairs cr
512 where cr.repair_line_id = p_rule_input_rec.repair_line_id;
513
514 l_repair_status_rec.repair_line_id := p_rule_input_rec.repair_line_id;
515 l_repair_status_rec.repair_status_id := p_ro_flow_status_id;
516 l_repair_status_rec.reason_code := p_flow_status_reason_code;
517 l_repair_status_rec.object_version_number := l_object_version_number;
518
519 l_status_upd_control_rec.check_task_wip := 'N';
520
521 csd_repairs_pvt.update_ro_status(p_api_version => p_api_version_number,
522 p_commit => fnd_api.g_false,
523 p_init_msg_list => p_init_msg_list,
524 p_validation_level => p_validation_level,
525 x_return_status => x_return_status,
526 x_msg_count => x_msg_count,
527 x_msg_data => x_msg_data,
528 p_repair_status_Rec => l_repair_status_rec,
529 p_status_control_rec => l_status_upd_control_rec,
530 x_object_version_number => x_object_version_number);
531 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
532 RAISE FND_API.G_EXC_ERROR;
533 END IF;
534 END IF;
535 IF p_action is not null THEN
536 l_sql_stmt := c_BEGIN
537 || p_action
538 || c_END;
539
540 l_pos := instr(l_sql_stmt, G_REPAIR_LINE_ID_PARAM);
541
542 IF l_pos <> 0 THEN
543 l_sql_stmt := replace(l_sql_stmt, G_REPAIR_LINE_ID_PARAM, ':1');
544 EXECUTE IMMEDIATE (l_sql_stmt) USING p_rule_input_rec.repair_line_id;
545 ELSE
546 EXECUTE IMMEDIATE (l_sql_stmt);
547 END IF;
548
549 END IF;
550
551 -- Standard check of p_commit.
552 IF FND_API.To_Boolean( p_commit ) THEN
553 COMMIT WORK;
554 END IF;
555
556 x_msg_count := fnd_msg_pub.count_msg;
557
558 EXCEPTION
559 WHEN FND_API.G_EXC_ERROR THEN
560 x_return_status := FND_API.G_RET_STS_ERROR;
561 x_msg_count := fnd_msg_pub.count_msg;
562 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
563 x_return_status := FND_API.G_RET_STS_ERROR;
564 x_msg_count := fnd_msg_pub.count_msg;
565 WHEN OTHERS THEN
566 x_return_status := FND_API.G_RET_STS_ERROR;
567 x_msg_count := fnd_msg_pub.count_msg;
568
569 END PROCESS_SOO_RULE;
570
571 /*--------------------------------------------------------------------*/
572 /* procedure name: GET_DEFAULT_VALUE_FROM_RULE (overloaded) */
573 /* description : procedure used to get default values from rules */
574 /* default value = VARCHAR2 data type */
575 /* */
576 /* */
577 /* Called from : Depot Repair Workbench defaulting */
578 /* Input Parm : */
579 /* p_api_version NUMBER Req Api Version number */
580 /* p_init_msg_list VARCHAR2 Opt Initialize message stack */
581 /* p_commit VARCHAR2 Opt Commits in API */
582 /* p_validation_level NUMBER Opt validation steps */
583 /* p_entity_attribute_type VARCHAR2 Req */
587 /* x_return_status VARCHAR2 Return status after the call. */
584 /* p_entity_attribute_code VARCHAR2 Req */
585 /* p_rule_input_rec CSD_RULE_INPUT_REC_TYPE Req */
586 /* Output Parm : */
588 /* x_msg_count NUMBER Number of messages in stack */
589 /* x_msg_data VARCHAR2 Mesg. text if x_msg_count >= 1 */
590 /* x_default_value VARCHAR2 */
591 /* x_rule_id NUMBER Rule ID that determined value */
592 /* if null, then no rule used */
593 /* Change Hist : Jan-14-08 rfieldma created */
594 /* Aug-20-08 swai added param x_rule_id */
595 /* */
596 /* */
597 /*--------------------------------------------------------------------*/
598 PROCEDURE GET_DEFAULT_VALUE_FROM_RULE (
599 p_api_version_number IN NUMBER,
600 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
601 p_commit IN VARCHAR2 := FND_API.G_FALSE,
602 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
603 p_entity_attribute_type IN VARCHAR2,
604 p_entity_attribute_code IN VARCHAR2,
605 p_rule_input_rec IN CSD_RULE_INPUT_REC_TYPE,
606 x_default_value OUT NOCOPY VARCHAR2,
607 x_rule_id OUT NOCOPY NUMBER, -- swai: 12.1.1 ER 7233924
608 x_return_status OUT NOCOPY VARCHAR2,
609 x_msg_count OUT NOCOPY NUMBER,
610 x_msg_data OUT NOCOPY VARCHAR2
611 )
612 IS
613 ---- local constants ----
614 c_API_NAME CONSTANT VARCHAR2(30) := 'GET_DEFAULT_VALUE_FROM_RULE';
615 c_API_VERSION_NUMBER CONSTANT NUMBER := G_L_API_VERSION_NUMBER;
616
617 ---- local variables ----
618 l_rule_matching_rec CSD_RULE_MATCHING_REC_TYPE;
619 l_default_val_str VARCHAR2(150) := NULL;
620 l_tbl_count NUMBER := 0;
621 l_value_type VARCHAR(30) := NULL;
622 l_defaulting_value VARCHAR(150) := NULL;
623 l_default_rule_id NUMBER := NULL; -- swai: added for 12.1.1
624 BEGIN
625 --* Standard Start of API savepoint
626 SAVEPOINT GET_DFLT_VAL_FROM_RULE_PVT;
627
628 --* Standard call to check for call compatibility.
629 IF NOT FND_API.Compatible_API_Call ( c_API_VERSION_NUMBER,
630 p_api_version_number,
631 c_API_NAME,
632 G_PKG_NAME)
633 THEN
634 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
635 END IF;
636
637 --* Initialize message list if p_init_msg_list is set to TRUE.
638 IF FND_API.to_Boolean( p_init_msg_list )
639 THEN
640 FND_MSG_PUB.initialize;
641 END IF;
642
643 --* Initialize API return status to SUCCESS
644 x_return_status := FND_API.G_RET_STS_SUCCESS;
645
646 --* logic starts here *--
647
648 /* BEGIN: Algorithm:
649 * (1) initialize a local record l_rule_matching_rec of type CSD_RULE_MATCHING_REC_TYPE:
650 * l_rule_matching_rec.rule_match_code := 1;
651 * l_rule_matching_rec.rule_type = DEFAULTING;
652 * l_rule_matching_rec.entity_attribute_type := p_entity_attribute_type;
653 * l_rule_matching_rec.entity_attribute_code := p_entity_attribute_code;
654 * l_rule_matching_rec.rule_input_rec := p_rule_input_rec;
655 *
656 * (2) Call procedure PROCESS_RULE_MATCHING
657 * (3) Check if any retrieval needs to be done for default value:
658 * IF l_rule_matching_rec.RULE_RESULTS_TBL(1).VALUE_TYPE = ATTRIBUTE THEN
659 * DEFAULT_VALUE := l_rule_matching_rec.RULE_RESULTS_TBL(1).defaulting_value
660 * ELSIF l_rule_matching_rec.RULE_RESULTS_TBL(1).VALUE_TYPE = PROFILE THEN
661 * DEFAULT_VALUE := get fnd_profile value for profile name stored in
662 * l_rule_matching_rec.RULE_RESULTS_TBL(1).defaulting_value
663 * ELSIF l_rule_matching_rec.RULE_RESULTS_TBL(1).VALUE_TYPE = PLSQL THEN
664 * DEFAULT_VALUE := execute PL/SQL API.
665 * END IF;
666 * END: Algorithm*/
667
668 --* init l_rule_matching_rec
669 l_rule_matching_rec.rule_match_code := G_RULE_MATCH_ONE;
670 l_rule_matching_rec.rule_type := G_RULE_TYPE_DEFAULTING;
671 l_rule_matching_rec.entity_attribute_type := p_entity_attribute_type;
672 l_rule_matching_rec.entity_attribute_code := p_entity_attribute_code;
673 l_rule_matching_rec.rule_input_rec := p_rule_input_rec;
674
675 --** debug starts!!
676 --dbms_output.put_line('GET_DEFAULT_VALUE_FROM_RULE before PROCESS_RULE_MATCHING, x_return_status ' || x_return_status);
677 --** debug ends!!
678
679 PROCESS_RULE_MATCHING(
680 p_api_version_number => p_api_version_number,
681 p_commit => p_commit,
682 p_init_msg_list => p_init_msg_list,
683 p_validation_level => p_validation_level,
684 px_rule_matching_rec => l_rule_matching_rec,
685 x_return_status => x_return_status,
686 x_msg_count => x_msg_count,
687 x_msg_data => x_msg_data
688 );
689 --** debug starts!!
693 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
690 --dbms_output.put_line('GET_DEFAULT_VALUE_FROM_RULE -> x_return_status = ' || x_return_status);
691 --** debug ends!!
692
694 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
695 FND_MESSAGE.Set_Name('CSD', 'CSD_RULE_MATCH_FAILED');
696 FND_MSG_PUB.Add;
697 END IF;
698 RAISE FND_API.G_EXC_ERROR;
699 END IF; --* end IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) *--
700
701 l_tbl_count := l_rule_matching_rec.RULE_RESULTS_TBL.COUNT;
702
703 --** debug starts!!
704 --dbms_output.put_line('GET_DEFAULT_VALUE_FROM_RULE -> l_tbl_count = ' || l_tbl_count);
705 --** debug ends!!
706
707 IF (l_tbl_count > 0) THEN
708 l_value_type := l_rule_matching_rec.RULE_RESULTS_TBL(1).VALUE_TYPE;
709 l_defaulting_value := l_rule_matching_rec.RULE_RESULTS_TBL(1).DEFAULTING_VALUE;
710 l_default_rule_id := l_rule_matching_rec.RULE_RESULTS_TBL(1).RULE_ID; -- swai: 12.1.1 ER 7233924
711 --** debug starts!!
712 --dbms_output.put_line('GET_DEFAULT_VALUE_FROM_RULE , value_type ' || l_value_type);
713 --dbms_output.put_line('GET_DEFAULT_VALUE_FROM_RULE , defaulting_value ' || l_defaulting_value);
714 --** debug ends!!
715 END IF; --* end IF (l_tbl_count > 0) *--
716
717 --* GET_DEFAULT_VALUE must be always called because if l_defaulting_value is null,
718 --* then the value would be returned from a profile option
719 l_default_val_str := GET_DEFAULT_VALUE(
720 p_value_type => l_value_type,
721 p_defaulting_value => l_defaulting_value,
722 p_attribute_type => p_entity_attribute_type,
723 p_attribute_code => p_entity_attribute_code,
724 p_rule_input_rec => p_rule_input_rec --vicli: bug 12536477
725 );
726
727 --** debug starts!!
728 --dbms_output.put_line('GET_DEFAULT_VALUE_FROM_RULE , after GET_DEFAULT_VALUE, l_default_val_str = ' || l_default_val_str);
729 --** debug ends!!
730
731 x_rule_id := l_default_rule_id; -- swai: 12.1.1 ER 7233924
732 x_default_value := l_default_val_str;
733 --* logic ends here *--
734
735
736 --* Standard call to get message count and if count is 1, get message info.
737 FND_MSG_PUB.Count_And_Get(
738 p_count => x_msg_count,
739 p_data => x_msg_data
740 );
741
742 EXCEPTION
743 WHEN FND_API.G_EXC_ERROR THEN
744 x_return_status := FND_API.G_RET_STS_ERROR;
745 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
746 P_API_NAME => c_API_NAME
747 ,P_PKG_NAME => G_PKG_NAME
748 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
749 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
750 ,X_MSG_COUNT => X_MSG_COUNT
751 ,X_MSG_DATA => X_MSG_DATA
752 ,X_RETURN_STATUS => X_RETURN_STATUS);
753
754 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
755 x_return_status := FND_API.G_RET_STS_ERROR;
756 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
757 P_API_NAME => c_API_NAME
758 ,P_PKG_NAME => G_PKG_NAME
759 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
760 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
761 ,X_MSG_COUNT => X_MSG_COUNT
762 ,X_MSG_DATA => X_MSG_DATA
763 ,X_RETURN_STATUS => X_RETURN_STATUS);
764
765 WHEN OTHERS THEN
766 x_return_status := FND_API.G_RET_STS_ERROR;
767 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
768 P_API_NAME => c_API_NAME
769 ,P_PKG_NAME => G_PKG_NAME
770 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
771 ,P_SQLCODE => SQLCODE
772 ,P_SQLERRM => SQLERRM
773 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
774 ,X_MSG_COUNT => X_MSG_COUNT
775 ,X_MSG_DATA => X_MSG_DATA
776 ,X_RETURN_STATUS => X_RETURN_STATUS);
777 END GET_DEFAULT_VALUE_FROM_RULE;
778
779
780 /*--------------------------------------------------------------------*/
781 /* procedure name: GET_DEFAULT_VALUE_FROM_RULE (overloaded) */
782 /* description : procedure used to get default values from rules */
783 /* default value = NUMBER data type */
784 /* */
785 /* */
786 /* Called from : Depot Repair Workbench defaulting */
787 /* Input Parm : */
788 /* p_api_version NUMBER Req Api Version number */
789 /* p_init_msg_list VARCHAR2 Opt Initialize message stack */
790 /* p_commit VARCHAR2 Opt Commits in API */
791 /* p_validation_level NUMBER Opt validation steps */
792 /* p_entity_attribute_type VARCHAR2 Req */
793 /* p_entity_attribute_code VARCHAR2 Req */
794 /* p_rule_input_rec CSD_RULE_INPUT_REC_TYPE Req */
795 /* Output Parm : */
796 /* x_return_status VARCHAR2 Return status after the call. */
797 /* x_msg_count NUMBER Number of messages in stack */
798 /* x_msg_data VARCHAR2 Mesg. text if x_msg_count >= 1 */
799 /* x_default_value NUMBER */
800 /* x_rule_id NUMBER Rule ID that determined value */
801 /* if null, then no rule used */
802 /* Change Hist : Jan-14-08 rfieldma created */
806 /*--------------------------------------------------------------------*/
803 /* Aug-20-08 swai added param x_rule_id */
804 /* */
805 /* */
807 PROCEDURE GET_DEFAULT_VALUE_FROM_RULE (
808 p_api_version_number IN NUMBER,
809 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
810 p_commit IN VARCHAR2 := FND_API.G_FALSE,
811 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
812 p_entity_attribute_type IN VARCHAR2,
813 p_entity_attribute_code IN VARCHAR2,
814 p_rule_input_rec IN CSD_RULE_INPUT_REC_TYPE,
815 x_default_value OUT NOCOPY NUMBER,
816 x_rule_id OUT NOCOPY NUMBER, -- swai: 12.1.1 ER 7233924
817 x_return_status OUT NOCOPY VARCHAR2,
818 x_msg_count OUT NOCOPY NUMBER,
819 x_msg_data OUT NOCOPY VARCHAR2
820 )
821 IS
822 ---- local constants ----
823 c_API_NAME CONSTANT VARCHAR2(30) := 'GET_DEFAULT_VALUE_FROM_RULE';
824
825 ---- local variables ----
826 l_default_val_str VARCHAR2(150) := NULL;
827 BEGIN
828 --* Standard Start of API savepoint
829 SAVEPOINT GET_DFLT_VAL_FROM_RULE_PVT;
830
831 --* call the string function
832 GET_DEFAULT_VALUE_FROM_RULE (
833 p_api_version_number => p_api_version_number,
834 p_init_msg_list => p_init_msg_list,
835 p_commit => p_commit,
836 p_validation_level => p_validation_level,
837 p_entity_attribute_type => p_entity_attribute_type,
838 p_entity_attribute_code => p_entity_attribute_code,
839 p_rule_input_rec => p_rule_input_rec,
840 x_default_value => l_default_val_str,
841 x_rule_id => x_rule_id, -- swai: 12.1.1 ER 7233924
842 x_return_status => x_return_status,
843 x_msg_count => x_msg_count,
844 x_msg_data => x_msg_data
845 );
846
847 --* convert value to number
848 x_default_value := to_number(l_default_val_str);
849
850 --* logic ends here *--
851 EXCEPTION
852 WHEN FND_API.G_EXC_ERROR THEN
853 x_return_status := FND_API.G_RET_STS_ERROR;
854 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
855 P_API_NAME => c_API_NAME
856 ,P_PKG_NAME => G_PKG_NAME
857 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
858 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
859 ,X_MSG_COUNT => X_MSG_COUNT
860 ,X_MSG_DATA => X_MSG_DATA
861 ,X_RETURN_STATUS => X_RETURN_STATUS);
862
863 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
864 x_return_status := FND_API.G_RET_STS_ERROR;
865 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
866 P_API_NAME => c_API_NAME
867 ,P_PKG_NAME => G_PKG_NAME
868 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
869 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
870 ,X_MSG_COUNT => X_MSG_COUNT
871 ,X_MSG_DATA => X_MSG_DATA
872 ,X_RETURN_STATUS => X_RETURN_STATUS);
873
874 WHEN OTHERS THEN
875 x_return_status := FND_API.G_RET_STS_ERROR;
876 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
877 P_API_NAME => c_API_NAME
878 ,P_PKG_NAME => G_PKG_NAME
879 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
880 ,P_SQLCODE => SQLCODE
881 ,P_SQLERRM => SQLERRM
882 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
883 ,X_MSG_COUNT => X_MSG_COUNT
884 ,X_MSG_DATA => X_MSG_DATA
885 ,X_RETURN_STATUS => X_RETURN_STATUS);
886 END GET_DEFAULT_VALUE_FROM_RULE;
887
888
889 /*--------------------------------------------------------------------*/
890 /* procedure name: GET_DEFAULT_VALUE_FROM_RULE (overloaded) */
891 /* description : procedure used to get default values from rules */
892 /* default value = DATE data type */
893 /* */
894 /* */
895 /* Called from : Depot Repair Workbench defaulting */
896 /* Input Parm : */
897 /* p_api_version NUMBER Req Api Version number */
898 /* p_init_msg_list VARCHAR2 Opt Initialize message stack */
899 /* p_commit VARCHAR2 Opt Commits in API */
900 /* p_validation_level NUMBER Opt validation steps */
901 /* p_entity_attribute_type VARCHAR2 Req */
902 /* p_entity_attribute_code VARCHAR2 Req */
903 /* p_rule_input_rec CSD_RULE_INPUT_REC_TYPE Req */
904 /* Output Parm : */
905 /* x_return_status VARCHAR2 Return status after the call. */
906 /* x_msg_count NUMBER Number of messages in stack */
907 /* x_msg_data VARCHAR2 Mesg. text if x_msg_count >= 1 */
908 /* x_default_value DATE */
909 /* x_rule_id NUMBER Rule ID that determined value */
910 /* if null, then no rule used */
911 /* Change Hist : Jan-14-08 rfieldma created */
912 /* Aug-20-08 swai added param x_rule_id */
916 PROCEDURE GET_DEFAULT_VALUE_FROM_RULE (
913 /* */
914 /* */
915 /*--------------------------------------------------------------------*/
917 p_api_version_number IN NUMBER,
918 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
919 p_commit IN VARCHAR2 := FND_API.G_FALSE,
920 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
921 p_entity_attribute_type IN VARCHAR2,
922 p_entity_attribute_code IN VARCHAR2,
923 p_rule_input_rec IN CSD_RULE_INPUT_REC_TYPE,
924 x_default_value OUT NOCOPY DATE,
925 x_rule_id OUT NOCOPY NUMBER, -- swai: 12.1.1 ER 7233924
926 x_return_status OUT NOCOPY VARCHAR2,
927 x_msg_count OUT NOCOPY NUMBER,
928 x_msg_data OUT NOCOPY VARCHAR2
929 )
930 IS
931 ---- local constants ----
932 c_API_NAME CONSTANT VARCHAR2(30) := 'GET_DEFAULT_VALUE_FROM_RULE';
933
934 ---- local variables ----
935 l_default_val_str VARCHAR2(150) := NULL;
936
937 BEGIN
938 --* Standard Start of API savepoint
939 SAVEPOINT GET_DFLT_VAL_FROM_RULE_PVT;
940 --* call the string function
941 GET_DEFAULT_VALUE_FROM_RULE (
942 p_api_version_number => p_api_version_number,
943 p_init_msg_list => p_init_msg_list,
944 p_commit => p_commit,
945 p_validation_level => p_validation_level,
946 p_entity_attribute_type => p_entity_attribute_type,
947 p_entity_attribute_code => p_entity_attribute_code,
948 p_rule_input_rec => p_rule_input_rec,
949 x_default_value => l_default_val_str,
950 x_rule_id => x_rule_id, -- swai: 12.1.1 ER 7233924
951 x_return_status => x_return_status,
952 x_msg_count => x_msg_count,
953 x_msg_data => x_msg_data
954 );
955
956 --* convert value to date
957 x_default_value := to_date(l_default_val_str, 'DD-MM-YY HH:MI:SS');
958
959
960 --* logic ends here *--
961 EXCEPTION
962 WHEN FND_API.G_EXC_ERROR THEN
963 x_return_status := FND_API.G_RET_STS_ERROR;
964 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
965 P_API_NAME => c_API_NAME
966 ,P_PKG_NAME => G_PKG_NAME
967 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
968 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
969 ,X_MSG_COUNT => X_MSG_COUNT
970 ,X_MSG_DATA => X_MSG_DATA
971 ,X_RETURN_STATUS => X_RETURN_STATUS);
972
973 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
974 x_return_status := FND_API.G_RET_STS_ERROR;
975 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
976 P_API_NAME => c_API_NAME
977 ,P_PKG_NAME => G_PKG_NAME
978 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
979 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
980 ,X_MSG_COUNT => X_MSG_COUNT
981 ,X_MSG_DATA => X_MSG_DATA
982 ,X_RETURN_STATUS => X_RETURN_STATUS);
983
984 WHEN OTHERS THEN
985 x_return_status := FND_API.G_RET_STS_ERROR;
986 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
987 P_API_NAME => c_API_NAME
988 ,P_PKG_NAME => G_PKG_NAME
989 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
990 ,P_SQLCODE => SQLCODE
991 ,P_SQLERRM => SQLERRM
992 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
993 ,X_MSG_COUNT => X_MSG_COUNT
994 ,X_MSG_DATA => X_MSG_DATA
995 ,X_RETURN_STATUS => X_RETURN_STATUS);
996 END GET_DEFAULT_VALUE_FROM_RULE;
997
998 /*--------------------------------------------------------------------*/
999 /* procedure name: MATCH_CONDITION */
1000 /* description : procedure used to match parameter to criterion based */
1001 /* on operatior */
1002 /* Calls overloaded function - CHECK_CONDITION_MATCH */
1003 /* */
1004 /* */
1005 /* Called from : PROCEDURE PROCESS_RULE_MATCHING */
1006 /* Input Parm : */
1007 /* p_parameter_type VARCHAR2 Req */
1008 /* p_operator VARCHAR2 Req */
1009 /* p_criterion VARCHAR2 Req */
1010 /* p_rule_input_rec CSD_RULE_INPUT_REC_TYPE Req */
1011 /* Return Val : */
1012 /* VARCHAR2 G_MISS.G_TRUE/G_MISS.G_FALSE */
1013 /* Change Hist : Jan-14-08 rfieldma created */
1014 /* */
1015 /* */
1016 /* */
1017 /*--------------------------------------------------------------------*/
1018 FUNCTION MATCH_CONDITION (
1019 p_parameter_type IN VARCHAR2,
1020 p_operator IN VARCHAR2,
1021 p_criterion IN VARCHAR2,
1022 p_rule_input_rec IN CSD_RULE_INPUT_REC_TYPE
1023 ) RETURN VARCHAR2 IS
1024 ---- local variables ----
1028 l_short_string_input VARCHAR2(30) := NULL;
1025 l_return_val VARCHAR2(1) := Fnd_Api.G_FALSE;
1026 l_rule_input_rec CSD_RULE_INPUT_REC_TYPE;
1027 l_number_input NUMBER := -1;
1029 l_repair_line_id NUMBER := NULL;
1030 l_date_field DATE := NULL;
1031 l_city VARCHAR2(60);
1032
1033 ---- cursors ----
1034
1035 BEGIN
1036 l_repair_line_id := p_rule_input_rec.repair_line_id;
1037 COPY_RULE_INPUT_REC_VALUES(p_rule_input_rec, l_rule_input_rec);
1038
1039 IF (p_parameter_type = 'USER_ID') THEN -- to test
1040 --** debug starts!!
1041 --dbms_output.put_line('>>>MATCH_CONDITION - USER_ID');
1042 --** debug ends!!
1043
1044 l_number_input := FND_GLOBAL.USER_ID;
1045 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_number_input,
1046 p_operator => p_operator,
1047 p_criterion => to_number(p_criterion));
1048 ELSIF (p_parameter_type = 'USER_RESPONSIBILITY') THEN -- to test
1049 --** debug starts!!
1050 --dbms_output.put_line('>>>MATCH_CONDITION - USER_RESPONSIBILITY');
1051 --** debug ends!!
1052 l_number_input := FND_GLOBAL.RESP_ID;
1053 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_number_input,
1054 p_operator => p_operator,
1055 p_criterion => to_number(p_criterion));
1056 ELSIF (p_parameter_type = 'USER_INV_ORG') THEN -- to test
1057 --** debug starts!!
1058 --dbms_output.put_line('>>>MATCH_CONDITION - USER_INV_ORG');
1059 --** debug ends!!
1060
1061 l_number_input := FND_PROFILE.VALUE(G_PROFILE_INV_ORG); -- get user inventory org id
1062 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_number_input,
1063 p_operator => p_operator,
1064 p_criterion => to_number(p_criterion));
1065 ELSIF (p_parameter_type = 'USER_OU') THEN -- to test
1066 --** debug starts!!
1067 --dbms_output.put_line('>>>MATCH_CONDITION - USER_OU');
1068 --** debug ends!!
1069
1070 IF l_repair_line_id IS NOT NULL AND G_INCIDENT_ID IS NOT NULL
1071 THEN
1072 l_number_input := csd_process_util.get_org_id(p_incident_id => G_INCIDENT_ID);
1073 ELSE
1074 l_number_input := FND_GLOBAL.ORG_ID;
1075 END IF;
1076 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_number_input,
1077 p_operator => p_operator,
1078 p_criterion => to_number(p_criterion));
1079 ELSIF (p_parameter_type = 'SR_CUSTOMER_ID') THEN -- to test
1080 --** debug starts!!
1081 --dbms_output.put_line('>>>MATCH_CONDITION - SR_CUSTOMER_ID');
1082 --** debug ends!!
1083
1084 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.SR_CUSTOMER_ID,
1085 p_operator => p_operator,
1086 p_criterion => to_number(p_criterion));
1087 ELSIF (p_parameter_type = 'SR_CUSTOMER_ACCOUNT_ID') THEN -- to test
1088 --** debug starts!!
1089 --dbms_output.put_line('>>>MATCH_CONDITION - SR_CUSTOMER_ACCOUNT_ID ');
1090 --** debug ends!!
1091
1092 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.SR_CUSTOMER_ACCOUNT_ID,
1093 p_operator => p_operator,
1094 p_criterion => to_number(p_criterion));
1095
1096 ELSIF (p_parameter_type = 'SR_BILL_TO_COUNTRY') THEN -- to test
1097 --** debug starts!!
1098 --dbms_output.put_line('>>>MATCH_CONDITION - SR_BILL_TO_COUNTRY');
1099 --** debug ends!!
1100
1101 --* site_use_id -> site_id hz_party_site_uses
1102 --* site_id -> location_id hz_party_sites_v or hz_party_sites
1103 --* location has country code hz_locations, use CSDSERVC.pld as example
1104 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
1105 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_short_string_input,
1106 p_operator => p_operator,
1107 p_criterion => p_criterion);
1108 ELSIF (p_parameter_type = 'SR_SHIP_TO_COUNTRY') THEN -- to test
1109 --** debug starts!!
1110 --dbms_output.put_line('>>>MATCH_CONDITION - SR_SHIP_TO_COUNTRY ');
1111 --** debug ends!!
1112
1113 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
1114 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_short_string_input,
1115 p_operator => p_operator,
1116 p_criterion => p_criterion);
1117 ELSIF (p_parameter_type = 'SR_ITEM_ID') THEN -- OK --
1118 --** debug starts!!
1119 --dbms_output.put_line('>>>MATCH_CONDITION - SR_ITEM_ID');
1120 --** debug ends!!
1121
1122 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.SR_ITEM_ID,
1123 p_operator => p_operator,
1124 p_criterion => to_number(p_criterion));
1125 ELSIF (p_parameter_type = 'SR_ITEM_CATEGORY_ID') THEN -- to test
1126 --** debug starts!!
1127 --dbms_output.put_line('>>>MATCH_CONDITION - SR_ITEM_CATEGORY_ID');
1128 --** debug ends!!
1129
1133
1130 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.SR_ITEM_CATEGORY_ID,
1131 p_operator => p_operator,
1132 p_criterion => to_number(p_criterion));
1134 ELSIF (p_parameter_type = 'SR_CONTRACT_ID') THEN -- to test
1135 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.SR_CONTRACT_ID,
1136 p_operator => p_operator,
1137 p_criterion => to_number(p_criterion));
1138 ELSIF (p_parameter_type = 'SR_PROBLEM_CODE') THEN -- to test
1139 --** debug starts!!
1140 --dbms_output.put_line('>>>MATCH_CONDITION - SR_PROBLEM_CODE');
1141 --** debug ends!!
1142
1143 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.SR_PROBLEM_CODE,
1144 p_operator => p_operator,
1145 p_criterion => p_criterion);
1146 -- swai: 12.1.1 ER 7233924
1147 ELSIF (p_parameter_type = 'RO_ITEM_ID') THEN
1148 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.RO_ITEM_ID,
1149 p_operator => p_operator,
1150 p_criterion => to_number(p_criterion));
1151 -- swai: 12.1.1 ER 7233924
1152 ELSIF (p_parameter_type = 'RO_ITEM_CATEGORY_ID') THEN -- to test
1153 l_return_val := CHECK_RO_ITEM_CATEGORY(p_ro_item_id => l_rule_input_rec.RO_ITEM_ID,
1154 p_operator => p_operator,
1155 p_criterion => to_number(p_criterion));
1156
1157 ELSIF (p_parameter_type = 'RO_PROMISE_DATE_THRESHOLD') THEN -- to test
1158 --* get # days for sysdate promise_date
1159 l_return_val := CHECK_PROMISE_DATE(p_repair_line_id => l_repair_line_id,
1160 p_operator => p_operator,
1161 p_criterion => p_criterion);
1162 ELSIF (p_parameter_type = 'RO_RESOLVE_BY_DATE_THRESHOLD') THEN -- to test
1163 l_return_val := CHECK_RESOLVE_BY_DATE(p_repair_line_id => l_repair_line_id,
1164 p_operator => p_operator,
1165 p_criterion => p_criterion);
1166 ELSIF (p_parameter_type = 'RO_THIRD_PTY_THRESHOLD') THEN -- to test
1167 --* get # days a third party based on repair line id (return by date on 3rd party return logistics line)
1168 l_return_val := CHECK_RETURN_BY_DATE(p_repair_line_id => l_repair_line_id,
1169 p_action_type => G_ACTION_TYPE_RMA_THIRD_PTY,
1170 p_action_code => '%',
1171 p_operator => p_operator,
1172 p_criterion => p_criterion);
1173 ELSIF (p_parameter_type = 'RO_EXCHANGE_THRESHOLD') THEN -- to test
1174 --* get # days exchange is out based on repair line id
1175 l_return_val := CHECK_RETURN_BY_DATE(p_repair_line_id => l_repair_line_id,
1176 p_action_type => G_ACTION_TYPE_RMA,
1177 p_action_code => G_ACTION_CODE_EXCHANGE,
1178 p_operator => p_operator,
1179 p_criterion => p_criterion);
1180 ELSIF (p_parameter_type = 'RO_LOANER_THRESHOLD') THEN -- to test
1181 --*get # days loaner is out based on repair line id (return by date on loaner line)
1182 l_return_val := CHECK_RETURN_BY_DATE(p_repair_line_id => l_repair_line_id,
1183 p_action_type => G_ACTION_TYPE_RMA,
1184 p_action_code => G_ACTION_CODE_LOANER,
1185 p_operator => p_operator,
1186 p_criterion => p_criterion);
1187 -- swai: bug 7524870 - add new condition
1188 ELSIF (p_parameter_type = 'RO_RMA_CUST_PROD_THRESHOLD') THEN
1189 --*get # days until customer product is due based on repair line id (return by date on rma line)
1190 l_return_val := CHECK_RETURN_BY_DATE(p_repair_line_id => l_repair_line_id,
1191 p_action_type => G_ACTION_TYPE_RMA,
1192 p_action_code => G_ACTION_CODE_CUST_PROD,
1193 p_operator => p_operator,
1194 p_criterion => p_criterion);
1195 -- end swai: bug 7524870
1196 ELSIF (p_parameter_type = 'RO_REPEAT_REPAIR_THRESHOLD') THEN -- code, need to find out how to get ship date
1197 --*get # days since last repair based on instance_id
1198 l_return_val := CHECK_REPEAT_REPAIR (p_repair_line_id => l_repair_line_id,
1199 p_operator => p_operator,
1200 p_criterion => p_criterion);
1201 ELSIF (p_parameter_type = 'RO_CHRONIC_REPAIR_THRESHOLD') THEN -- code, need to find out how to get ship date
1202 --* quality check period is a new profile option, it's a number uom (day)
1203 --* number of repairs during the check period per instance
1204 --* get # repair orders within quality check period, for instance_id
1205 l_return_val := CHECK_CHRONIC_REPAIR(p_repair_line_id => l_repair_line_id,
1206 p_operator => p_operator,
1207 p_criterion => p_criterion);
1208 ELSIF (p_parameter_type = 'RO_CONTRACT_EXP_THRESHOLD') THEN -- to test, what does grace period mean?
1209 --* get # days until contract expires
1210 l_return_val := CHECK_CONTRACT_EXP_DATE(p_repair_line_id => l_repair_line_id,
1214 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.WIP_MTL_TXN_ITEM_ID,
1211 p_operator => p_operator,
1212 p_criterion => p_criterion);
1213 elsif p_parameter_type = 'WIP_MTL_TXN_ITEM_ID' then
1215 p_operator => p_operator,
1216 p_criterion => to_number(p_criterion));
1217 elsif p_parameter_type = 'WIP_ENTITY_ITEM_ID' then
1218 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.WIP_ENTITY_ITEM_ID,
1219 p_operator => p_operator,
1220 p_criterion => to_number(p_criterion));
1221 elsif p_parameter_type = 'WIP_INV_ORG_ID' then
1222 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.WIP_INV_ORG_ID,
1223 p_operator => p_operator,
1224 p_criterion => to_number(p_criterion));
1225
1226 -- start vicli bug#14155233 defaulting service warranty
1227 elsif p_parameter_type = 'RO_REPAIR_TYPE_ID' then
1228 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.RO_REPAIR_TYPE_ID,
1229 p_operator => p_operator,
1230 p_criterion => to_number(p_criterion));
1231 elsif p_parameter_type = 'WIP_MTL_DISP_CODE_ID' then
1232 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.WIP_MTL_DISP_CODE_ID,
1233 p_operator => p_operator,
1234 p_criterion => to_number(p_criterion));
1235 -- end vicli bug#14155233 defaulting service warranty
1236
1237 -- bug#14029797, subhat
1238 elsif p_parameter_type = 'SR_SHIP_TO_CITY' then
1239 if NOT G_SHIP_TO_CITY_CACHE.exists(l_rule_input_rec.SR_SHIP_TO_SITE_USE_ID)
1240 then
1241 begin
1242 if l_rule_input_rec.SR_SHIP_TO_SITE_USE_ID IS NOT NULL
1243 then
1244 SELECT hl.city
1245 INTO G_SHIP_TO_CITY_CACHE(l_rule_input_rec.SR_SHIP_TO_SITE_USE_ID)
1246 FROM hz_locations hl, hz_party_sites hps,
1247 hz_party_site_uses hpsu
1248 WHERE hpsu.party_site_use_id = l_rule_input_rec.SR_SHIP_TO_SITE_USE_ID
1249 AND hpsu.site_use_type = 'SHIP_TO'
1250 AND hpsu.party_site_id = hps.party_site_id
1251 AND hps.location_id = hl.location_id;
1252
1253 l_city := G_SHIP_TO_CITY_CACHE(l_rule_input_rec.SR_SHIP_TO_SITE_USE_ID);
1254 end if;
1255 exception
1256 WHEN no_data_found THEN
1257 l_city := null;
1258 end;
1259 else
1260 l_city := G_SHIP_TO_CITY_CACHE(l_rule_input_rec.SR_SHIP_TO_SITE_USE_ID);
1261 end if;
1262 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_city,
1263 p_operator => p_operator,
1264 p_criterion =>p_criterion);
1265
1266 elsif p_parameter_type = 'WIP_MTL_TXN_ITEM_CAT_ID' then
1267 l_return_val := CHECK_MTL_TXN_ITEM_CAT(p_mtl_txn_item_id => l_rule_input_rec.WIP_MTL_TXN_ITEM_ID,
1268 p_wip_entity_id => l_rule_input_rec.WIP_ENTITY_ID,
1269 p_operator => p_operator,
1270 p_criterion => p_criterion);
1271
1272 -- new SOO rule conditions
1273 elsif p_parameter_type = 'POST_STATUS_SR' then
1274 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.SR_CURR_STATUS_ID,
1275 p_operator => p_operator,
1276 p_criterion => to_number(p_criterion));
1277 elsif p_parameter_type = 'POST_STATUS_SR_TASK' then
1278 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.SR_TASK_CURR_STATUS_ID,
1279 p_operator => p_operator,
1280 p_criterion => to_number(p_criterion));
1281 elsif p_parameter_type = 'POST_STATUS_WIP_JOB' then
1282 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.JOB_CURR_STATUS_ID,
1283 p_operator => p_operator,
1284 p_criterion => to_number(p_criterion));
1285 elsif p_parameter_type = 'POST_STATUS_IPR' then
1286 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.IR_CURR_STATUS_CODE,
1287 p_operator => p_operator,
1288 p_criterion => p_criterion);
1289 elsif p_parameter_type = 'POST_STATUS_PR' then
1290 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.PR_CURR_STATUS_CODE,
1291 p_operator => p_operator,
1292 p_criterion => p_criterion);
1293 elsif p_parameter_type = 'POST_STATUS_RMA_OM' then
1294 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.RMA_CURR_STATUS_CODE,
1295 p_operator => p_operator,
1296 p_criterion => p_criterion);
1297 elsif p_parameter_type = 'POST_STATUS_SHIP_OM' then
1298 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.SHIP_CURR_STATUS_CODE,
1299 p_operator => p_operator,
1300 p_criterion => p_criterion);
1301 elsif p_parameter_type = 'POST_STATUS_RMA_TP_OM' then
1302 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.RMA_TP_CURR_STATUS_CODE,
1303 p_operator => p_operator,
1304 p_criterion => p_criterion);
1308 p_criterion => p_criterion);
1305 elsif p_parameter_type = 'POST_STATUS_SHIP_TP_OM' then
1306 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.SHIP_TP_CURR_STATUS_CODE,
1307 p_operator => p_operator,
1309 elsif p_parameter_type = 'POST_STATUS_SHIP_DELIVERY' then
1310 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.DELIVERY_CURR_STATUS_CODE,
1311 p_operator => p_operator,
1312 p_criterion => p_criterion);
1313 elsif p_parameter_type = 'POST_STATUS_ISO' then
1314 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.INT_SO_CURR_STATUS_CODE,
1315 p_operator => p_operator,
1316 p_criterion => p_criterion);
1317 elsif p_parameter_type = 'PRE_STATUS_SR' then
1318 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.SR_PREV_STATUS_ID,
1319 p_operator => p_operator,
1320 p_criterion => to_number(p_criterion));
1321 elsif p_parameter_type = 'PRE_STATUS_SR_TASK' then
1322 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.SR_TASK_PREV_STATUS_ID,
1323 p_operator => p_operator,
1324 p_criterion => to_number(p_criterion));
1325 elsif p_parameter_type = 'PRE_STATUS_WIP_JOB' then
1326 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.JOB_PREV_STATUS_ID,
1327 p_operator => p_operator,
1328 p_criterion => to_number(p_criterion));
1329 elsif p_parameter_type = 'PRE_STATUS_IPR' then
1330 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.IR_PREV_STATUS_CODE,
1331 p_operator => p_operator,
1332 p_criterion => p_criterion);
1333 elsif p_parameter_type = 'PRE_STATUS_PR' then
1334 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.PR_PREV_STATUS_CODE,
1335 p_operator => p_operator,
1336 p_criterion => p_criterion);
1337 elsif p_parameter_type = 'PRE_STATUS_RMA_OM' then
1338 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.RMA_PREV_STATUS_CODE,
1339 p_operator => p_operator,
1340 p_criterion => p_criterion);
1341 elsif p_parameter_type = 'PRE_STATUS_SHIP_OM' then
1342 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.SHIP_PREV_STATUS_CODE,
1343 p_operator => p_operator,
1344 p_criterion => p_criterion);
1345 elsif p_parameter_type = 'PRE_STATUS_RMA_TP_OM' then
1346 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.RMA_TP_PREV_STATUS_CODE,
1347 p_operator => p_operator,
1348 p_criterion => p_criterion);
1349 elsif p_parameter_type = 'PRE_STATUS_SHIP_TP_OM' then
1350 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.SHIP_TP_PREV_STATUS_CODE,
1351 p_operator => p_operator,
1352 p_criterion => p_criterion);
1353 elsif p_parameter_type = 'PRE_STATUS_SHIP_DELIVERY' then
1354 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.DELIVERY_PREV_STATUS_CODE,
1355 p_operator => p_operator,
1356 p_criterion => p_criterion);
1357 elsif p_parameter_type = 'PRE_STATUS_ISO' then
1358 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.INT_SO_PREV_STATUS_CODE,
1359 p_operator => p_operator,
1360 p_criterion => p_criterion);
1361 elsif p_parameter_type = 'RO_CONTRACT_ID' then
1362 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.RO_CONTRACT_ID,
1363 p_operator => p_operator,
1364 p_criterion => to_number(p_criterion));
1365 elsif p_parameter_type = 'RO_FLOW_STATUS' then
1366 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.RO_FLOW_STATUS_ID,
1367 p_operator => p_operator,
1368 p_criterion => to_number(p_criterion));
1369 elsif p_parameter_type = 'RO_OWNER' then
1370 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.RO_OWNER_ID,
1371 p_operator => p_operator,
1372 p_criterion => to_number(p_criterion));
1373 elsif p_parameter_type = 'RO_PRIORITY' then
1374 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.RO_PRIORITY_CODE,
1375 p_operator => p_operator,
1376 p_criterion => p_criterion);
1377 elsif p_parameter_type = 'WIP_COUNTRY' then
1378 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.WIP_COUNTRY,
1379 p_operator => p_operator,
1380 p_criterion => p_criterion);
1381 elsif p_parameter_type = 'WIP_OU' then
1382 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.WIP_OPERATING_UNIT_ID,
1383 p_operator => p_operator,
1384 p_criterion => to_number(p_criterion));
1385 elsif p_parameter_type = 'RO_VENDOR_ACCOUNT' then
1386 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_rule_input_rec.RO_VENDOR_ACCOUNT_ID,
1390
1387 p_operator => p_operator,
1388 p_criterion => to_number(p_criterion));
1389
1391 END IF; --* end IF (p_parameter_type = 'USER_ID') *--
1392
1393 RETURN l_return_val;
1394 END MATCH_CONDITION;
1395
1396
1397 /*--------------------------------------------------------------------*/
1398 /* procedure name: CHECK_CONDITION_MATCH (overloaded) */
1399 /* description : procedure used to check if parameter matches */
1400 /* criterion based on operator */
1401 /* parameter, criterion = NUMBER data type */
1402 /* */
1403 /* */
1404 /* Called from : FUNCTION MATCH_CONDITION */
1405 /* Input Parm : */
1406 /* p_parameter_type NUMBER Req */
1407 /* p_operator NUMBER Req */
1408 /* p_criterion NUMBER Req */
1409 /* Return Val : */
1410 /* VARCHAR2 G_MISS.G_TRUE/G_MISS.G_FALSE */
1411 /* Change Hist : Jan-14-08 rfieldma created */
1412 /* */
1413 /* */
1414 /* */
1415 /*--------------------------------------------------------------------*/
1416 FUNCTION CHECK_CONDITION_MATCH (
1417 p_input_param IN NUMBER,
1418 p_operator IN VARCHAR2,
1419 p_criterion IN NUMBER
1420 ) RETURN VARCHAR2 IS
1421 l_return_val VARCHAR2(1):= FND_API.G_FALSE;
1422 BEGIN
1423
1424 --** debug starts!!
1425 --dbms_output.put_line('NNN CHECK_CONDITION_MATCH_N top p_input_param = ' || p_input_param);
1426 --dbms_output.put_line('NNN CHECK_CONDITION_MATCH_N top p_operator = ' || p_operator);
1427 --dbms_output.put_line('NNN CHECK_CONDITION_MATCH_N top p_criterion = ' || p_criterion);
1428 --** debug ends!!
1429
1430
1431 IF (p_operator = G_EQUALS AND p_input_param = p_criterion) THEN
1432 l_return_val := FND_API.G_TRUE;
1433 ELSIF (p_operator = G_NOT_EQUALS AND p_input_param <> p_criterion) THEN
1434 l_return_val := FND_API.G_TRUE;
1435 ELSIF (p_operator = G_LESS_THAN AND p_input_param < p_criterion) THEN
1436 l_return_val := FND_API.G_TRUE;
1437 ELSIF (p_operator = G_GREATER_THAN AND p_input_param > p_criterion) THEN
1438 l_return_val := FND_API.G_TRUE;
1439 END IF;
1440
1441 --** debug starts!!
1442 --dbms_output.put_line('NNN CHECK_CONDITION_MATCH_N l_return_val = ' || l_return_val);
1443 --** debug ends!!
1444
1445 --* if all of the above cases fail, then there is no match.
1446 RETURN l_return_val;
1447 END CHECK_CONDITION_MATCH;
1448
1449
1450 /*--------------------------------------------------------------------*/
1451 /* procedure name: CHECK_CONDITION_MATCH (overloaded) */
1452 /* description : procedure used to check if parameter matches */
1453 /* criterion based on operator */
1454 /* parameter, criterion = VARCHAR2 data type */
1455 /* varchar type only matches = and <> */
1456 /* */
1457 /* */
1458 /* Called from : FUNCTION MATCH_CONDITION */
1459 /* Input Parm : */
1460 /* p_parameter_type VARCHAR2 Req */
1461 /* p_operator VARCHAR2 Req */
1462 /* p_criterion VARCHAR2 Req */
1463 /* Return Val : */
1464 /* VARCHAR2 G_MISS.G_TRUE/G_MISS.G_FALSE */
1465 /* Change Hist : Jan-14-08 rfieldma created */
1466 /* */
1467 /* */
1468 /* */
1469 /*--------------------------------------------------------------------*/
1470 FUNCTION CHECK_CONDITION_MATCH (
1471 p_input_param IN VARCHAR2,
1472 p_operator IN VARCHAR2,
1473 p_criterion IN VARCHAR2
1474 ) RETURN VARCHAR2 IS
1475 l_return_val VARCHAR2(1) := FND_API.G_FALSE;
1476 BEGIN
1477 --** debug starts!!
1478 --dbms_output.put_line('VVV CHECK_CONDITION_MATCH_V top p_input_param = ' || p_input_param);
1479 --dbms_output.put_line('VVV CHECK_CONDITION_MATCH_V top p_operator = ' || p_operator);
1480 --dbms_output.put_line('VVV CHECK_CONDITION_MATCH_V top p_criterion = ' || p_criterion);
1481 --** debug ends!!
1482
1483 IF (p_operator = G_EQUALS AND p_input_param = p_criterion) THEN
1484 l_return_val := FND_API.G_TRUE;
1485 ELSIF (p_operator = G_NOT_EQUALS AND p_input_param <> p_criterion) THEN
1486 l_return_val := FND_API.G_TRUE;
1487 END IF;
1488
1489 --** debug starts!!
1490 --dbms_output.put_line('VVV CHECK_CONDITION_MATCH_V l_return_val = ' || l_return_val);
1491 --** debug ends!!
1492
1493 --* if all of the above cases fail, then there is no match.
1494 RETURN l_return_val; -- FND_API.G_FALSE
1495 END CHECK_CONDITION_MATCH;
1496
1497
1501 /* criterion based on operator */
1498 /*--------------------------------------------------------------------*/
1499 /* procedure name: CHECK_CONDITION_MATCH (overloaded) */
1500 /* description : procedure used to check if parameter matches */
1502 /* parameter, criterion = DATE data type */
1503 /* */
1504 /* */
1505 /* Called from : FUNCTION MATCH_CONDITION */
1506 /* Input Parm : */
1507 /* p_parameter_type DATE Req */
1508 /* p_operator VARCHAR2 Req */
1509 /* p_criterion DATE Req */
1510 /* Return Val : */
1511 /* VARCHAR2 G_MISS.G_TRUE/G_MISS.G_FALSE */
1512 /* Change Hist : Jan-14-08 rfieldma created */
1513 /* */
1514 /* */
1515 /* */
1516 /*--------------------------------------------------------------------*/
1517 FUNCTION CHECK_CONDITION_MATCH (
1518 p_input_param IN DATE,
1519 p_operator IN VARCHAR2,
1520 p_criterion IN DATE
1521 ) RETURN VARCHAR2 IS
1522 l_return_val VARCHAR2(1) := FND_API.G_FALSE;
1523 BEGIN
1524 --** debug starts!!
1525 --dbms_output.put_line('DDD CHECK_CONDITION_MATCH_D top p_input_param = ' || p_input_param);
1526 --dbms_output.put_line('DDD CHECK_CONDITION_MATCH_D top p_operator = ' || p_operator);
1527 --dbms_output.put_line('DDD CHECK_CONDITION_MATCH_D top p_criterion = ' || p_criterion);
1528 --** debug ends!!
1529
1530 IF (p_operator = G_EQUALS AND p_input_param = p_criterion) THEN
1531 l_return_val := FND_API.G_TRUE;
1532 ELSIF (p_operator = G_NOT_EQUALS AND p_input_param <> p_criterion) THEN
1533 l_return_val := FND_API.G_TRUE;
1534 ELSIF (p_operator = G_LESS_THAN AND p_input_param < p_criterion) THEN
1535 l_return_val := FND_API.G_TRUE;
1536 ELSIF (p_operator = G_GREATER_THAN AND p_input_param > p_criterion) THEN
1537 l_return_val := FND_API.G_TRUE;
1538 END IF;
1539
1540 --** debug starts!!
1541 --dbms_output.put_line('DDD CHECK_CONDITION_MATCH_D l_return_val_D = ' || l_return_val);
1542 --** debug ends!!
1543
1544 --* if all of the above cases fail, then there is no match.
1545 RETURN l_return_val; -- FND_API.G_FALSE
1546 END CHECK_CONDITION_MATCH;
1547
1548
1549 /*--------------------------------------------------------------------*/
1550 /* procedure name: COPY_RULE_INPUT_REC_VALUES */
1551 /* description : copies source rec into dest rec */
1552 /* rec typ = CSD_RULE_INPUT_REC_TYPE */
1553 /* */
1554 /* */
1555 /* Called from : FUNCTION MATCH_CONDITION */
1556 /* Input Parm : */
1557 /* p_s_rec CSD_RULE_INPUT_REC_TYPE Req */
1558 /* p_d_Rec CSD_RULE_INPUT_REC_TYPE VARCHAR2 Req */
1559 /* Change Hist : Jan-14-08 rfieldma created */
1560 /* */
1561 /* */
1562 /* */
1563 /*--------------------------------------------------------------------*/
1564 PROCEDURE COPY_RULE_INPUT_REC_VALUES(
1565 p_s_rec IN CSD_RULE_INPUT_REC_TYPE, -- source rec
1566 px_d_rec IN OUT NOCOPY CSD_RULE_INPUT_REC_TYPE -- destination rec
1567 ) IS
1568 BEGIN
1569 px_d_rec.SR_CUSTOMER_ID := p_s_rec.SR_CUSTOMER_ID;
1570 px_d_rec.SR_CUSTOMER_ACCOUNT_ID := p_s_rec.SR_CUSTOMER_ACCOUNT_ID;
1571 px_d_rec.SR_BILL_TO_SITE_USE_ID := p_s_rec.SR_BILL_TO_SITE_USE_ID;
1572 px_d_rec.SR_SHIP_TO_SITE_USE_ID := p_s_rec.SR_SHIP_TO_SITE_USE_ID;
1573 px_d_rec.SR_ITEM_ID := p_s_rec.SR_ITEM_ID;
1574 px_d_rec.SR_ITEM_CATEGORY_ID := p_s_rec.SR_ITEM_CATEGORY_ID;
1575 px_d_rec.SR_CONTRACT_ID := p_s_rec.SR_CONTRACT_ID;
1576 px_d_rec.SR_PROBLEM_CODE := p_s_rec.SR_PROBLEM_CODE;
1577 px_d_rec.SR_INSTANCE_ID := p_s_rec.SR_INSTANCE_ID;
1578 px_d_rec.RO_ITEM_ID := p_s_rec.RO_ITEM_ID; -- swai: 12.1.1 ER 7233924
1579 px_d_rec.WIP_ENTITY_ID := p_s_rec.WIP_ENTITY_ID;
1580 px_d_rec.WIP_MTL_TXN_ITEM_ID := p_s_rec.WIP_MTL_TXN_ITEM_ID;
1581 px_d_rec.WIP_ENTITY_ITEM_ID := p_s_rec.WIP_ENTITY_ITEM_ID;
1582 px_d_rec.WIP_INV_ORG_ID := p_s_rec.WIP_INV_ORG_ID;
1583 px_d_rec.WIP_MTL_DISP_CODE_ID := p_s_rec.WIP_MTL_DISP_CODE_ID; -- bug#14155233 defaulting service warranty
1584 px_d_rec.RO_REPAIR_TYPE_ID := p_s_rec.RO_REPAIR_TYPE_ID; -- bug#14155233 defaulting service warranty
1585
1586 -- SOO Rule Conditions
1587 px_d_rec.SR_PREV_STATUS_ID := p_s_rec.SR_PREV_STATUS_ID;
1588 px_d_rec.SR_TASK_PREV_STATUS_ID := p_s_rec.SR_TASK_PREV_STATUS_ID;
1589 px_d_rec.JOB_PREV_STATUS_ID := p_s_rec.JOB_PREV_STATUS_ID;
1590 px_d_rec.PR_PREV_STATUS_CODE := p_s_rec.PR_PREV_STATUS_CODE;
1591 px_d_rec.IR_PREV_STATUS_CODE := p_s_rec.IR_PREV_STATUS_CODE;
1592 px_d_rec.RMA_PREV_STATUS_CODE := p_s_rec.RMA_PREV_STATUS_CODE;
1596 px_d_rec.DELIVERY_PREV_STATUS_CODE := p_s_rec.DELIVERY_PREV_STATUS_CODE;
1593 px_d_rec.SHIP_PREV_STATUS_CODE := p_s_rec.SHIP_PREV_STATUS_CODE;
1594 px_d_rec.RMA_TP_PREV_STATUS_CODE := p_s_rec.RMA_TP_PREV_STATUS_CODE;
1595 px_d_rec.SHIP_TP_PREV_STATUS_CODE := p_s_rec.SHIP_TP_PREV_STATUS_CODE;
1597 px_d_rec.INT_SO_PREV_STATUS_CODE := p_s_rec.INT_SO_PREV_STATUS_CODE;
1598 px_d_rec.SR_CURR_STATUS_ID := p_s_rec.SR_CURR_STATUS_ID;
1599 px_d_rec.SR_TASK_CURR_STATUS_ID := p_s_rec.SR_TASK_CURR_STATUS_ID;
1600 px_d_rec.JOB_CURR_STATUS_ID := p_s_rec.JOB_CURR_STATUS_ID;
1601 px_d_rec.PR_CURR_STATUS_CODE := p_s_rec.PR_CURR_STATUS_CODE;
1602 px_d_rec.IR_CURR_STATUS_CODE := p_s_rec.IR_CURR_STATUS_CODE;
1603 px_d_rec.RMA_CURR_STATUS_CODE := p_s_rec.RMA_CURR_STATUS_CODE;
1604 px_d_rec.SHIP_CURR_STATUS_CODE := p_s_rec.SHIP_CURR_STATUS_CODE;
1605 px_d_rec.RMA_TP_CURR_STATUS_CODE := p_s_rec.RMA_TP_CURR_STATUS_CODE;
1606 px_d_rec.SHIP_TP_CURR_STATUS_CODE := p_s_rec.SHIP_TP_CURR_STATUS_CODE;
1607 px_d_rec.DELIVERY_CURR_STATUS_CODE := p_s_rec.DELIVERY_CURR_STATUS_CODE;
1608 px_d_rec.INT_SO_CURR_STATUS_CODE := p_s_rec.INT_SO_CURR_STATUS_CODE;
1609 px_d_rec.RO_VENDOR_ACCOUNT_ID := p_s_rec.RO_VENDOR_ACCOUNT_ID;
1610 px_d_rec.RO_CONTRACT_ID := p_s_rec.RO_CONTRACT_ID;
1611 px_d_rec.RO_FLOW_STATUS_ID := p_s_rec.RO_FLOW_STATUS_ID;
1612 px_d_rec.RO_OWNER_ID := p_s_rec.RO_OWNER_ID;
1613 px_d_rec.RO_PRIORITY_CODE := p_s_rec.RO_PRIORITY_CODE;
1614 px_d_rec.WIP_COUNTRY := p_s_rec.WIP_COUNTRY;
1615 px_d_rec.WIP_OPERATING_UNIT_ID := p_s_rec.WIP_OPERATING_UNIT_ID;
1616 --** debug starts!!
1617 -- dbms_output.put_line('in COPY_RULE_INPUT_REC_VALUES - SR_ITEM_ID = ' || px_d_rec.SR_ITEM_ID);
1618 --** debug ends!!
1619
1620 END COPY_RULE_INPUT_REC_VALUES;
1621
1622 /*--------------------------------------------------------------------*/
1623 /* procedure name: COPY_RULE_INPUT_REC_VALUES */
1624 /* description : copies source rec into dest rec */
1625 /* rec typ = CSD_RULE_INPUT_REC_TYPE */
1626 /* */
1627 /* */
1628 /* Called from : FUNCTION MATCH_CONDITION */
1629 /* Input Parm : */
1630 /* p_s_rec CSD_RULE_INPUT_REC_TYPE Req */
1631 /* p_d_Rec CSD_RULE_INPUT_REC_TYPE VARCHAR2 Req */
1632 /* Change Hist : Jan-14-08 rfieldma created */
1633 /* */
1634 /* */
1635 /* */
1636 /*--------------------------------------------------------------------*/
1637 PROCEDURE POPULATE_RULE_INPUT_REC(
1638 px_rule_input_rec IN OUT NOCOPY CSD_RULE_INPUT_REC_TYPE,
1639 p_repair_line_id IN NUMBER
1640 )IS
1641 ---- cursors ----
1642 CURSOR cur_get_rec_info (p_repair_line_id NUMBER) IS
1643 SELECT a.customer_id,
1644 a.account_id,
1645 a.bill_to_site_use_id,
1646 a.ship_to_site_use_id,
1647 a.inventory_item_id,
1648 c.category_id,
1649 a.contract_id,
1650 a.problem_code,
1651 a.customer_product_id,
1652 b.inventory_item_id, -- swai: 12.1.1 ER 7233924i
1653 b.incident_id,
1654 c.account_id,
1655 b.contract_line_id,
1656 b.flow_status_id,
1657 b.resource_id,
1658 b.ro_priority_code
1659 FROM CSD_INCIDENTS_V a, CSD_REPAIRS b, CS_INCIDENTS_B_SEC c
1660 WHERE a.incident_id = b.incident_id
1661 AND a.incident_id = c.incident_id
1662 AND b.repair_line_Id = p_repair_line_id;
1663
1664 CURSOR cur_get_wip_info (p_wip_entity_id NUMBER) IS
1665 SELECT we.primary_item_id,
1666 we.organization_id,
1667 hrla.country,
1668 orgv.operating_unit
1669 FROM wip_entities we, org_organization_definitions orgv,
1670 PER_ORGANIZATION_UNITS porg, hr_locations_all hrla
1671 WHERE we.wip_entity_id = p_wip_entity_id
1672 and we.organization_id = orgv.organization_id
1673 and we.organization_id = porg.organization_id
1674 and hrla.location_id = porg.location_id
1675 ;
1676
1677 BEGIN
1678 OPEN cur_get_rec_info(p_repair_line_id);
1679 FETCH cur_get_rec_info INTO
1680 px_rule_input_rec.SR_CUSTOMER_ID,
1681 px_rule_input_rec.SR_CUSTOMER_ACCOUNT_ID,
1682 px_rule_input_rec.SR_BILL_TO_SITE_USE_ID,
1683 px_rule_input_rec.SR_SHIP_TO_SITE_USE_ID,
1684 px_rule_input_rec.SR_ITEM_ID,
1685 px_rule_input_rec.SR_ITEM_CATEGORY_ID,
1686 px_rule_input_rec.SR_CONTRACT_ID,
1687 px_rule_input_rec.SR_PROBLEM_CODE,
1688 px_rule_input_rec.SR_INSTANCE_ID,
1689 px_rule_input_rec.RO_ITEM_ID, -- swai: 12.1.1 ER 7233924
1690 G_INCIDENT_ID,
1691 px_rule_input_rec.RO_VENDOR_ACCOUNT_ID,
1692 px_rule_input_rec.RO_CONTRACT_ID,
1693 px_rule_input_rec.RO_FLOW_STATUS_ID,
1694 px_rule_input_rec.RO_OWNER_ID,
1695 px_rule_input_rec.RO_PRIORITY_CODE
1696 ;
1697 CLOSE cur_get_rec_info;
1698
1699 IF (px_rule_input_rec.wip_entity_id is not null) THEN
1700 OPEN cur_get_wip_info (px_rule_input_rec.wip_entity_id);
1701 FETCH cur_get_wip_info INTO
1702 px_rule_input_rec.WIP_ENTITY_ITEM_ID,
1703 px_rule_input_rec.WIP_INV_ORG_ID,
1704 px_rule_input_rec.WIP_COUNTRY,
1708
1705 px_rule_input_rec.WIP_OPERATING_UNIT_ID;
1706 CLOSE cur_get_wip_info;
1707 END IF;
1709
1710 --** debug starts!!
1711 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC P_REPAIR_LINE_ID = ' || p_repair_line_id);
1712 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC SR_CUSTOMER_ID = ' || px_rule_input_rec.SR_CUSTOMER_ID);
1713 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC SR_CUSTOMER_ACCOUNT_ID = ' || px_rule_input_rec.SR_CUSTOMER_ACCOUNT_ID);
1714 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC SR_BILL_TO_SITE_USE_ID = ' || px_rule_input_rec.SR_BILL_TO_SITE_USE_ID);
1715 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC SR_SHIP_TO_SITE_USE_ID = ' || px_rule_input_rec.SR_SHIP_TO_SITE_USE_ID);
1716 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC SR_ITEM_ID = ' || px_rule_input_rec.SR_ITEM_ID);
1717 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC SR_ITEM_CATEGORY_ID = ' || px_rule_input_rec.SR_ITEM_CATEGORY_ID);
1718 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC SR_CONTRACT_ID = ' || px_rule_input_rec.SR_CONTRACT_ID);
1719 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC SR_PROBLEM_CODE = ' || px_rule_input_rec.SR_PROBLEM_CODE);
1720 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC SR_INSTANCE_ID = ' || px_rule_input_rec.SR_INSTANCE_ID);
1721 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC RO_ITEM_ID = ' || px_rule_input_rec.RO_ITEM_ID);
1722 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC RO_REPAIR_TYPE_ID = ' || px_rule_input_rec.RO_REPAIR_TYPE_ID);
1723 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC G_INCIDENT_ID = ' || G_INCIDENT_ID);
1724 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC RO_CONTRACT_ID = ' || px_rule_input_rec.RO_CONTRACT_ID);
1725 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC RO_FLOW_STATUS_ID = ' || px_rule_input_rec.RO_FLOW_STATUS_ID);
1726 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC RO_OWNER_ID = ' || px_rule_input_rec.RO_OWNER_ID);
1727 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC RO_PRIORITY_CODE = ' || px_rule_input_rec.RO_PRIORITY_CODE);
1728 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC WIP_ENTITY_ITEM_ID = ' || px_rule_input_rec.WIP_ENTITY_ITEM_ID);
1729 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC WIP_INV_ORG_ID = ' || px_rule_input_rec.WIP_INV_ORG_ID);
1730 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC WIP_COUNTRY = ' || px_rule_input_rec.WIP_COUNTRY);
1731 --dbms_output.put_line('***** POPULATE_RULE_INPUT_REC WIP_OPERATING_UNIT_ID = ' || px_rule_input_rec.WIP_OPERATING_UNIT_ID);
1732 --** debug ends!!
1733
1734
1735 END POPULATE_RULE_INPUT_REC;
1736
1737
1738 /*--------------------------------------------------------------------*/
1739 /* procedure name: GET_DEFAULT_VALUE */
1740 /* description : retrieves default value based on type */
1741 /* ATTRIBUTE -> return default value as is */
1742 /* PROFILE -> return profile (default value) */
1743 /* PLSQL -> execute function call stored in default */
1744 /* value and cast return value to string */
1745 /* and return that string value */
1746 /* */
1747 /* */
1748 /* Called from : FUNCTION GET_DEFAULT_VALUE_FROM_RULE */
1749 /* Input Parm : */
1750 /* p_value_type VARCHAR2 Req */
1751 /* p_defaulting_value VARCHAR2 Req */
1752 /* p_attribute_type VARCHAR2 Req */
1753 /* p_attribute_code VARCHAR2 Req */
1754 /* x_return_status VARCHAR2 Req */
1755 /* x_msg_count VARCHAR2 Req */
1756 /* x_msg_data VARCHAR2 Req */
1757 /* Return Val : */
1758 /* VARCHAR2 - the actual default value */
1759 /* */
1760 /* Change Hist : Jan-14-08 rfieldma created */
1761 /* */
1762 /* */
1763 /* */
1764 /*--------------------------------------------------------------------*/
1765 FUNCTION GET_DEFAULT_VALUE(
1766 p_value_type IN VARCHAR2,
1767 p_defaulting_value IN VARCHAR2,
1768 p_attribute_type IN VARCHAR2,
1769 p_attribute_code IN VARCHAR2,
1770 p_rule_input_rec IN CSD_RULE_INPUT_REC_TYPE --bug#12536477
1771 ) RETURN VARCHAR2 IS
1772 ---- local constants ----
1773 c_SELECT VARCHAR2(7) := 'SELECT ';
1774 c_FROM_DUAL VARCHAR2(10) := ' FROM DUAL';
1775
1776 ---- local variables ----
1777 l_return_val VARCHAR2(150) := NULL; -- size of flex field
1778 l_sql_stmt VARCHAR2(2000) := NULL;
1779 l_fdbk NUMBER := NULL;
1780 l_cursor NUMBER := NULL;
1781
1782 BEGIN
1783
1784 --** debug starts!!
1785 --dbms_output.put_line('~~~ GET_DEFAULT_VALUE top, p_value_type =' || p_value_type);
1786 --dbms_output.put_line('~~~ GET_DEFAULT_VALUE top, p_defaulting_value =' || p_defaulting_value);
1787 --dbms_output.put_line('~~~ GET_DEFAULT_VALUE top, p_attribute_type =' || p_attribute_type);
1788 --dbms_output.put_line('~~~ GET_DEFAULT_VALUE top, p_attribute_code =' || p_attribute_code);
1789 --** debug ends!!
1790
1791 -- if defaulting value is null, the try to find profile value if applies
1795 --** debug starts!!
1792 IF (p_defaulting_value IS NULL) THEN -- get profile values and put them in
1793 IF (p_attribute_type = G_ATTR_TYPE_RO) AND (p_attribute_code = G_ATTR_CODE_REPAIR_ORG) THEN
1794 l_return_val := FND_PROFILE.VALUE(G_PROFILE_REPAIR_ORG);
1796 --dbms_output.put_line('~~~ GET_DEFAULT_VALUE , G_ATTR_CODE_REPAIR_ORG, l_return_val from profile = ' || l_return_val);
1797 --** debug ends!!
1798
1799 ELSIF (p_attribute_type = G_ATTR_TYPE_RO) AND (p_attribute_code = G_ATTR_CODE_REPAIR_OWNER) THEN
1800 --* no profile
1801 l_return_val := NULL;
1802 --** debug starts!!
1803 --dbms_output.put_line('~~~ GET_DEFAULT_VALUE , G_ATTR_CODE_REPAIR_OWNER, l_return_val set to null ');
1804 --** debug ends!!
1805
1806 ELSIF (p_attribute_type = G_ATTR_TYPE_RO) AND (p_attribute_code = G_ATTR_CODE_INV_ORG) THEN
1807 l_return_val := FND_PROFILE.VALUE(G_PROFILE_INV_ORG);
1808 ELSIF (p_attribute_type = G_ATTR_TYPE_RO) AND (p_attribute_code = G_ATTR_CODE_RMA_RCV_ORG) THEN
1809 --* no profile
1810 l_return_val := NULL;
1811 ELSIF (p_attribute_type = G_ATTR_TYPE_RO) AND (p_attribute_code = G_ATTR_CODE_RMA_RCV_SUBINV) THEN
1812 --* no profile
1813 l_return_val := NULL;
1814 ELSIF (p_attribute_type = G_ATTR_TYPE_RO) AND (p_attribute_code = G_ATTR_CODE_PRIORITY) THEN
1815 --* no profile, place holder for if other defaulting logic is needed.
1816 l_return_val := NULL;
1817 ELSIF (p_attribute_type = G_ATTR_TYPE_RO) AND (p_attribute_code = G_ATTR_CODE_REPAIR_TYPE) THEN
1818 l_return_val := FND_PROFILE.VALUE(G_PROFILE_REPAIR_TYPE);
1819 ELSIF (p_attribute_type = G_ATTR_TYPE_RO) AND (p_attribute_code = G_ATTR_CODE_SHIP_FROM_ORG) THEN
1820 --* no profile
1821 l_return_val := NULL;
1822 ELSIF (p_attribute_type = G_ATTR_TYPE_RO) AND (p_attribute_code = G_ATTR_CODE_SHIP_FROM_SUBINV) THEN
1823 --* no profile
1824 l_return_val := NULL;
1825 ELSIF (p_attribute_type = G_ATTR_TYPE_RO) AND (p_attribute_code = G_ATTR_CODE_VENDOR_ACCOUNT) THEN
1826 --* no profile, no logic needed
1827 l_return_val := NULL;
1828 ELSE
1829 -- the cases below are handled by the ELSE clause:
1830
1831 -- if p_attribute_type = G_ATTR_TYPE_WIP and p_attribute_code = G_ATTR_CODE_REASON_CODE
1832
1833 -- if p_attribute_type = G_ATTR_TYPE_WIP and p_attribute_code = G_ATTR_CODE_CREATE_RO_FLAG
1834
1835 -- if p_attribute_type = G_ATTR_TYPE_WIP and p_attribute_code = G_ATTR_CODE_SERV_WARRANTY
1836
1837 l_return_val := NULL;
1838 END IF; --*end IF (p_attribute_type = G_ATTR_TYPE_RO) ...*--
1839
1840 ELSE
1841 IF (p_value_type IS NULL) OR (p_value_type = FND_API.G_MISS_CHAR) --* bulletin rules does not specify this value
1842 OR (p_value_type = G_VALUE_TYPE_ATTRIBUTE) THEN
1843 l_return_val := p_defaulting_value;
1844 ELSIF (p_value_type = G_VALUE_TYPE_PROFILE) THEN
1845 l_return_val := FND_PROFILE.VALUE(p_defaulting_value);
1846 ELSIF (p_value_type = G_VALUE_TYPE_PLSQL) THEN
1847 l_sql_stmt := c_SELECT ||
1848 replace(p_defaulting_value, G_REPAIR_LINE_ID_PARAM, to_char(p_rule_input_rec.repair_line_id)) || --bug#12536477
1849 c_FROM_DUAL;
1850
1851 --** debug starts!!
1852 --dbms_output.put_line('~~~ GET_DEFAULT_VALUE , G_VALUE_TYPE_PLSQL, l_sql_stmt ' || l_sql_stmt);
1853 --** debug ends!!
1854
1855 l_cursor := DBMS_SQL.OPEN_CURSOR;
1856
1857 --* Parse the query with a dynamic WHERE clause
1858 DBMS_SQL.PARSE (l_cursor, l_sql_stmt, DBMS_SQL.NATIVE);
1859
1860 --* Define the columns in the cursor for this query
1861 DBMS_SQL.DEFINE_COLUMN (l_cursor, 1, l_return_val,150);
1862
1863
1864 --* Now I can execute the query
1865 l_fdbk:= DBMS_SQL.EXECUTE (l_cursor);
1866 LOOP
1867 --* Try to fetch next row. If done, then exit the loop.
1868 EXIT WHEN DBMS_SQL.FETCH_ROWS (l_cursor) = 0;
1869
1870 --* Retrieve data via calls to COLUMN_VALUE and place those
1871 --* values in a new record in the block.
1872
1873 DBMS_SQL.COLUMN_VALUE (l_cursor, 1, l_return_val);
1874
1875 END LOOP;
1876
1877 /* Clean up the cursor */
1878 DBMS_SQL.CLOSE_CURSOR (l_cursor);
1879
1880 ELSE --* unrecognized type, so return null
1881 l_return_val := NULL;
1882 END IF; --* end IF (p_value_type = G_VALUE_TYPE_ATTRIBUTE) *--
1883 END IF; --* end IF (p_defaulting_value IS NULL) *--
1884
1885 RETURN l_return_val;
1886 END GET_DEFAULT_VALUE;
1887
1888 /*--------------------------------------------------------------------*/
1889 /* procedure name: GET_COUNTRY_CODE */
1890 /* description : returns country code based on site_useid */
1891 /* */
1892 /* Called from : FUNCTION MATCH_CONDITION */
1893 /* Input Parm : */
1894 /* p_site_use_id NUMBER Req */
1895 /* Return Val : */
1896 /* VARCHAR2 - COUNTRY code */
1897 /* */
1898 /* Change Hist : Jan-14-08 rfieldma created */
1899 /* */
1900 /* */
1904 p_site_use_id IN NUMBER
1901 /* */
1902 /*--------------------------------------------------------------------*/
1903 FUNCTION GET_COUNTRY_CODE(
1905 ) RETURN VARCHAR2 IS
1906 ---- local variables ----
1907 l_country_code VARCHAR2(60) := NULL;
1908
1909 ---- cursors ----
1910 CURSOR cur_get_country_code(p_site_use_id NUMBER) IS
1911 SELECT b.country
1912 FROM hz_party_sites a,
1913 hz_locations b,
1914 hz_party_site_uses c
1915 WHERE a.location_id = b.location_id
1916 AND a.party_site_id = c.party_site_id
1917 AND c.party_site_use_id = p_site_use_id
1918 ; --* end CURSOR cur_get_country_code *--
1919 BEGIN
1920 OPEN cur_get_country_code(p_site_use_id);
1921 FETCH cur_get_country_code INTO l_country_code;
1922 CLOSE cur_get_country_code;
1923
1924 --** debug starts!!
1925 --dbms_output.put_line('***GET_COUNTRY_CODE , p_site_use_id = ' || p_site_use_id);
1926 --dbms_output.put_line('***GET_COUNTRY_CODE , l_country_code =' || l_country_code);
1927 --** debug ends!!
1928
1929
1930
1931 RETURN l_country_code;
1932 END GET_COUNTRY_CODE;
1933
1934
1935 /*--------------------------------------------------------------------*/
1936 /* procedure name: CHECK_RO_ITEM_CATEGORY */
1937 /* description : checks if the RO item is in the specified category */
1938 /* */
1939 /* Called from : FUNCTION MATCH_CONDITION */
1940 /* Input Parm : */
1941 /* p_ro_item_id NUMBER Req RO Inventory Item Id */
1942 /* p_operator VARCHAR2 Req 'EQUALS': check item is in category */
1943 /* 'NOT_EQUALS': check item is not in */
1944 /* item category */
1945 /* p_criterion NUMBER Req Item Category Id */
1946 /* Return Val : */
1947 /* VARCHAR2 - G_TRUE or G_FALSE */
1948 /* */
1949 /* Change Hist : Aug-18-08 swai created for 12.1.1 ER 7233924 */
1950 /* */
1951 /*--------------------------------------------------------------------*/
1952 FUNCTION CHECK_RO_ITEM_CATEGORY(
1953 p_ro_item_id IN NUMBER,
1954 p_operator IN VARCHAR2,
1955 p_criterion IN NUMBER
1956 ) RETURN VARCHAR2 IS
1957
1958 -- cursors --
1959 CURSOR cur_is_item_in_cat (p_inventory_item_id NUMBER,
1960 p_category_id NUMBER)
1961 IS
1962 SELECT 'X'
1963 FROM mtl_item_categories_v
1964 WHERE inventory_item_id = p_inventory_item_id
1965 and category_id = p_category_id
1966 and organization_id = cs_std.get_item_valdn_orgzn_id;
1967
1968 -- variables --
1969 l_item_is_in_cat VARCHAR2(1);
1970 l_return_val VARCHAR2(1) := FND_API.G_FALSE;
1971
1972 BEGIN
1973 OPEN cur_is_item_in_cat (p_ro_item_id, p_criterion);
1974 FETCH cur_is_item_in_cat into l_item_is_in_cat;
1975 CLOSE cur_is_item_in_cat;
1976
1977 CASE p_operator
1978 when G_EQUALS then
1979 if (l_item_is_in_cat is null) then
1980 l_return_val := FND_API.G_FALSE;
1981 else
1982 l_return_val := FND_API.G_TRUE;
1983 end if;
1984 when G_NOT_EQUALS then
1985 if (l_item_is_in_cat is null) then
1986 l_return_val := FND_API.G_TRUE;
1987 else
1988 l_return_val := FND_API.G_FALSE;
1989 end if;
1990 else
1991 l_return_val := FND_API.G_FALSE;
1992 END CASE;
1993 return l_return_val;
1994
1995 END CHECK_RO_ITEM_CATEGORY;
1996
1997
1998 /*--------------------------------------------------------------------*/
1999 /* procedure name: CHECK_PROMISE_BY_DATE */
2000 /* description : retrieves RO promise by date */
2001 /* compare threshold with promise_date - sysdate */
2002 /* */
2003 /* Called from : FUNCTION MATCH_CONDITION */
2004 /* Input Parm : */
2005 /* p_repair_line_id NUMBER Req */
2006 /* Return Val : */
2007 /* VARCHAR2 - G_TRUE or G_FALSE */
2008 /* */
2009 /* Change Hist : Jan-14-08 rfieldma created */
2010 /* */
2011 /* */
2012 /* */
2013 /*--------------------------------------------------------------------*/
2014 FUNCTION CHECK_PROMISE_DATE(
2015 p_repair_line_id IN NUMBER,
2016 p_operator IN VARCHAR2,
2017 p_criterion IN VARCHAR2
2018 ) RETURN VARCHAR2 IS
2019 ---- local variables ----
2020 l_return_val VARCHAR2(1) := FND_API.G_FALSE;
2021 l_number_input NUMBER := NULL;
2022 l_date_field DATE := NULL;
2023
2024 ---- cursors ----
2028 WHERE repair_line_id = p_repair_line_id
2025 CURSOR cur_get_promise_date(p_repair_line_id NUMBER) IS
2026 SELECT promise_date
2027 FROM csd_repairs
2029 ; --* end CURSOR get_promise_date *--
2030 BEGIN
2031 IF (p_repair_line_id IS NOT NULL) THEN
2032 OPEN cur_get_promise_date (p_repair_line_id);
2033 FETCH cur_get_promise_date into l_date_field;
2034 CLOSE cur_get_promise_date;
2035
2036 --** debug starts!!
2037 --dbms_output.put_line('=== CHECK_PROMISE_DATE , l_date_field = ' || l_date_field);
2038 --** debug ends!!
2039
2040 IF (l_date_field IS NOT NULL) AND (l_date_field <> FND_API.G_MISS_DATE) THEN
2041 l_number_input := l_date_field - sysdate;
2042 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_number_input,
2043 p_operator => p_operator,
2044 p_criterion => to_number(p_criterion));
2045 END IF; --* IF (l_date_field IS NOT NULL) ... *--
2046 END IF; --* end IF ( l_repair_line_id IS NOT NULL) *--
2047
2048 RETURN l_return_val;
2049 END CHECK_PROMISE_DATE;
2050
2051
2052 /*--------------------------------------------------------------------*/
2053 /* procedure name: CHECK_RESOLVE_BY_DATE */
2054 /* description : retrieves RO resolve by date */
2055 /* compare threshold with resolve_by_date - sysdate */
2056 /* */
2057 /* Called from : FUNCTION MATCH_CONDITION */
2058 /* Input Parm : */
2059 /* p_repair_line_id NUMBER Req */
2060 /* Return Val : */
2061 /* VARCHAR2 - G_TRUE or G_FALSE */
2062 /* */
2063 /* Change Hist : Jan-14-08 rfieldma created */
2064 /* */
2065 /* */
2066 /* */
2067 /*--------------------------------------------------------------------*/
2068 FUNCTION CHECK_RESOLVE_BY_DATE(
2069 p_repair_line_id IN NUMBER,
2070 p_operator IN VARCHAR2,
2071 p_criterion IN VARCHAR2
2072 ) RETURN VARCHAR2 IS
2073 ---- local variables ----
2074 l_return_val VARCHAR2(1) := FND_API.G_FALSE;
2075 l_number_input NUMBER := NULL;
2076 l_date_field DATE := NULL;
2077 ---- cursors ----
2078 CURSOR cur_get_resolve_by_date(p_repair_line_id NUMBER) IS
2079 SELECT resolve_by_date
2080 FROM csd_repairs
2081 WHERE repair_line_id = p_repair_line_id
2082 ; --* end CURSOR get_resolve_by_date *--
2083
2084 BEGIN
2085 IF (p_repair_line_id IS NOT NULL) THEN
2086 OPEN cur_get_resolve_by_date (p_repair_line_id);
2087 FETCH cur_get_resolve_by_date into l_date_field;
2088 CLOSE cur_get_resolve_by_date;
2089
2090 --** debug starts!!
2091 --dbms_output.put_line('==== CHECK_RESOLVE_BY_DATE , l_date_field = ' || l_date_field);
2092 --** debug ends!!
2093
2094 IF (l_date_field IS NOT NULL) AND (l_date_field <> FND_API.G_MISS_DATE) THEN
2095 l_number_input := l_date_field - sysdate; --get # days for sysdate resolve by date
2096 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_number_input,
2097 p_operator => p_operator,
2098 p_criterion => to_number(p_criterion));
2099 END IF; --* end IF (l_date_field IS NOT NULL) ...*--
2100 END IF; -- end IF ( l_repair_line_id IS NOT NULL) *--
2101
2102 RETURN l_return_val;
2103 END CHECK_RESOLVE_BY_DATE;
2104
2105
2106 /*--------------------------------------------------------------------*/
2107 /* procedure name: CHECK_RETURN_BY_DATE */
2108 /* description : retrieves return by date on logistics line */
2109 /* '%' => RMA_THIRD_PARTY line */
2110 /* loaner => RMA line */
2111 /* exchange => RMA line */
2112 /* compare threshold with return by date - sysdate */
2113 /* -- swai: bug 7524870 - only return a match if the */
2114 /* line has not been received AND the condition matches */
2115 /* */
2116 /* Called from : FUNCTION MATCH_CONDITION */
2117 /* Input Parm : */
2118 /* p_repair_line_id NUMBER Req */
2119 /* p_action_type VARCHAR2 Req */
2120 /* p_action_code VARCHAR2 Req */
2121 /* Return Val : */
2122 /* VARCHAR2 - G_TRUE or G_FALSE */
2123 /* */
2124 /* Change Hist : Jan-14-08 rfieldma created */
2125 /* */
2126 /* */
2127 /* */
2128 /*--------------------------------------------------------------------*/
2129 FUNCTION CHECK_RETURN_BY_DATE(
2130 p_repair_line_id IN NUMBER,
2131 p_action_type IN VARCHAR2,
2132 p_action_code IN VARCHAR2,
2133 p_operator IN VARCHAR2,
2134 p_criterion IN VARCHAR2
2138 l_number_input NUMBER := NULL;
2135 ) RETURN VARCHAR2 IS
2136 ---- local variables ----
2137 l_return_val VARCHAR2(1) := FND_API.G_FALSE;
2139 l_date_field DATE := NULL;
2140 l_prod_txn_status VARCHAR(30) := NULL;
2141 ---- cursors ----
2142 CURSOR cur_get_return_by_date(p_repair_line_id NUMBER,
2143 p_action_type VARCHAR2,
2144 p_action_code VARCHAR2) IS
2145 SELECT return_by_date, prod_txn_status
2146 FROM csd_product_txns_v
2147 WHERE action_type = p_action_type
2148 AND action_code LIKE p_action_code -- for 3rd party, pass in '%'
2149 AND repair_line_id = p_repair_line_id
2150 ; --* end CURSOR get_return_by_date *--
2151
2152 BEGIN
2153 IF (p_repair_line_id IS NOT NULL) THEN
2154 OPEN cur_get_return_by_date (p_repair_line_id, p_action_type, p_action_code);
2155 FETCH cur_get_return_by_date into l_date_field, l_prod_txn_status;
2156 CLOSE cur_get_return_by_date;
2157
2158 --** debug starts!!
2159 --dbms_output.put_line('+++ CHECK_REPEAT_REPAIR , l_date_field = ' || l_date_field);
2160 --** debug ends!!
2161
2162 IF (l_date_field IS NOT NULL) AND (l_date_field <> FND_API.G_MISS_DATE) THEN
2163 -- swai: bug 7524870 if the line has been received, do not return a match
2164 if (l_prod_txn_status = 'RECEIVED') then
2168 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_number_input,
2165 l_return_val := FND_API.G_FALSE;
2166 else
2167 l_number_input := l_date_field - sysdate;
2169 p_operator => p_operator,
2170 p_criterion => to_number(p_criterion));
2171 end if;
2172 END IF; --* end IF (l_date_field IS NOT NULL) ...*--
2173 END IF; --* end IF (l_repair_line_id IS NOT NULL) *--
2174
2175 RETURN l_return_val;
2176
2177 END CHECK_RETURN_BY_DATE;
2178
2179 /*--------------------------------------------------------------------*/
2180 /* procedure name: CHECK_REPEAT_REPAIR */
2181 /* description : 1) get instance id based on repair_line_id */
2182 /* 2) get the lastest repair based on the instance id */
2183 /* (order by closed_date desc ) */
2184 /* NOTE: ideally, we would like to use the ship date */
2185 /* on the logistics line. But due to the */
2186 /* complexity, we are using closed_date for */
2187 /* this release. */
2188 /* Called from : FUNCTION MATCH_CONDITION */
2189 /* Input Parm : */
2190 /* p_repair_line_id NUMBER Req */
2191 /* Return Val : */
2192 /* VARCHAR2 - G_TRUE or G_FALSE */
2193 /* */
2194 /* Change Hist : Jan-14-08 rfieldma created */
2195 /* */
2196 /* */
2197 /* */
2198 /*--------------------------------------------------------------------*/
2199 FUNCTION CHECK_REPEAT_REPAIR(
2200 p_repair_line_id IN NUMBER,
2201 p_operator IN VARCHAR2,
2202 p_criterion IN VARCHAR2
2203 ) RETURN VARCHAR2 IS
2204 ---- local variables ----
2205 l_return_val VARCHAR2(1) := FND_API.G_FALSE;
2206 l_number_input NUMBER := NULL;
2207 l_date_field DATE := NULL;
2211 -- need to redo this query based on ship date, could return
2208 l_creation_date DATE := NULL;
2209 l_instance_id NUMBER := NULL;
2210 ---- cursors ----
2212 CURSOR cur_get_latest_repair_date(p_instance_id NUMBER) IS
2213 SELECT MAX(a.date_closed)
2214 FROM csd_repairs a
2215 WHERE a.customer_product_id = p_instance_id
2216 AND a.date_closed IS NOT NULL
2217 ; --* end cur_get_latest_repair_date *--
2218
2219 CURSOR cur_get_creation_date(p_repair_line_id NUMBER) IS
2220 SELECT creation_date
2221 FROM csd_repairs
2222 WHERE repair_line_id = p_repair_line_id
2223 ; -- end* cur_get_creation_date*--
2224 BEGIN
2225 IF (p_repair_Line_id IS NOT NULL) THEN
2226 OPEN cur_get_creation_date (p_repair_line_id);
2227 FETCH cur_get_creation_date INTO l_creation_date;
2228 CLOSE cur_get_creation_date;
2229
2230 IF (l_creation_date IS NOT NULL) AND (l_creation_date <> FND_API.G_MISS_DATE) THEN
2231 l_instance_id := GET_RO_INSTANCE_ID(p_repair_line_id);
2232
2233 IF (l_instance_id IS NOT NULL) AND (l_instance_id <> FND_API.G_MISS_NUM) THEN
2234 -- found instance id, so get latest repair date
2235 OPEN cur_get_latest_repair_date(l_instance_id);
2236 FETCH cur_get_latest_repair_date INTO l_date_field;
2237 CLOSE cur_get_latest_repair_date;
2238
2239 --** debug starts!!
2240 --dbms_output.put_line('+++ CHECK_REPEAT_REPAIR , l_date_field = ' || l_date_field);
2241 --** debug ends!!
2242
2243 IF (l_date_field IS NOT NULL) AND (l_date_field <> FND_API.G_MISS_DATE) THEN
2244 l_number_input := sysdate - l_date_field;
2245 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_number_input,
2246 p_operator => p_operator,
2247 p_criterion => to_number(p_criterion));
2248 END IF; --* end IF (l_date_field IS NOT NULL).. *--
2249 END IF; --* end IF(l_instance_id IS NOT NULL).. *--
2250 END IF; --* end IF (l_createion_date IS NOT NULL)...*--
2251 END IF;--* end IF (p_repair_Line_id IS NOT NULL).. *--
2252
2253 RETURN l_return_val;
2254 END CHECK_REPEAT_REPAIR;
2255
2256 /*--------------------------------------------------------------------*/
2257 /* procedure name: CHECK_CHRONIC_REPAIR */
2258 /* description : 1) get instance id based on repair_line_id */
2259 /* 2) get profile option CSD_QUALITY_CHECK_PERIOD value */
2260 /* 3) query # of repair orders during this period */
2261 /* (closed_date) */
2262 /* NOTE: ideally, we would like to use the ship date */
2263 /* on the logistics line. But due to the */
2264 /* complexity, we are using closed_date for */
2265 /* this release. */
2266 /* Called from : FUNCTION MATCH_CONDITION */
2267 /* Input Parm : */
2268 /* p_repair_line_id NUMBER Req */
2269 /* Return Val : */
2270 /* VARCHAR2 - G_TRUE or G_FALSE */
2271 /* */
2272 /* Change Hist : Jan-14-08 rfieldma created */
2273 /* */
2274 /* */
2275 /* */
2276 /*--------------------------------------------------------------------*/
2277 FUNCTION CHECK_CHRONIC_REPAIR(
2278 p_repair_line_id IN NUMBER,
2279 p_operator IN VARCHAR2,
2280 p_criterion IN VARCHAR2
2281 )RETURN VARCHAR2 IS
2282 ---- local variables ----
2283 l_return_val VARCHAR2(1) := FND_API.G_FALSE;
2284 l_number_input NUMBER := NULL;
2285 l_instance_id NUMBER := NULL;
2286 l_period NUMBER := NULL;
2287
2288 ---- cursors ----
2289 -- need to redo this query based on ship date
2290 CURSOR cur_get_chronic_repairs(p_instance_id NUMBER,
2291 p_period NUMBER) IS
2292 SELECT count(a.repair_line_id)
2293 FROM csd_repairs a
2294 WHERE a.customer_product_id = p_instance_id
2295 AND a.date_closed BETWEEN sysdate - p_period
2296 AND sysdate
2297 ; --* end cur_get_chronic_repairs *--
2298 BEGIN
2299 IF (p_repair_line_id IS NOT NULL) THEN
2300 l_period := FND_PROFILE.VALUE(G_PROFILE_QUALITY_CHECK_PERIOD);
2301
2302
2303 --** debug starts!!
2304 --dbms_output.put_line('+++>> CHECK_CHRONIC_REPAIR , l_period = ' || l_period);
2305 --** debug ends!!
2306
2307 l_instance_id := GET_RO_INSTANCE_ID(p_repair_line_id);
2308 IF (l_instance_id IS NOT NULL) AND (l_instance_id <> FND_API.G_MISS_NUM) THEN
2309 --* found instance id, so get the number of repairs in period
2310 OPEN cur_get_chronic_repairs(l_instance_id, l_period);
2311 FETCH cur_get_chronic_repairs into l_number_input;
2312 CLOSE cur_get_chronic_repairs;
2313 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_number_input,
2314 p_operator => p_operator,
2315 p_criterion => to_number(p_criterion));
2316 END IF; --* end (l_instance_id IS NOT NULL).. *--
2320
2317 END IF; --* end IF (p_repair_Line_id IS NOT NULL).. *--
2318
2319 RETURN l_return_val;
2321 END;
2322
2323
2324 /*--------------------------------------------------------------------*/
2325 /* procedure name: CHECK_CONTRACT_EXP_DATE */
2326 /* description : calls OKS_ENTITLEMENTS_PUB.Get_Contracts_Expiration */
2327 /* checks threshold with exp date - sysdate */
2328 /* */
2329 /* Called from : FUNCTION MATCH_CONDITION */
2330 /* Input Parm : */
2331 /* p_repair_line_id NUMBER Req */
2332 /* */
2333 /* */
2334 /* Return Val : */
2335 /* VARCHAR2 - G_TRUE or G_FALSE */
2336 /* */
2337 /* Change Hist : Jan-14-08 rfieldma created */
2338 /* */
2339 /* */
2340 /* */
2341 /*--------------------------------------------------------------------*/
2342 FUNCTION CHECK_CONTRACT_EXP_DATE(
2343 p_repair_line_id IN NUMBER,
2344 p_operator IN VARCHAR2,
2345 p_criterion IN VARCHAR2
2346 ) RETURN VARCHAR2 IS
2347 ---- local variables ----
2348 l_return_val VARCHAR2(1) := FND_API.G_FALSE;
2349 l_number_input NUMBER := NULL;
2350 l_ro_contract_id NUMBER := NULL;
2351
2352 l_return_status VARCHAR2(1) := NULL;
2353 l_msg_count NUMBER := NULL;
2354 l_msg_data VARCHAR2(2000) := NULL;
2355 l_contract_end_date DATE := NULL;
2356
2357 ---- cursors ----
2358 CURSOR cur_get_ro_contract_id(p_repair_line_id NUMBER) IS
2359 SELECT contract_line_id
2360 FROM csd_repairs
2361 WHERE repair_line_id = p_repair_line_id
2362 ; --* end cur_get_ro_contract_id *--
2363
2364 -- bug 7323831 - contract line can expire if either end date or termination date
2365 -- has passed. If either date is null, do not consider it as a valid date for.
2366 -- comparison. If both end and termination dates are null, then return null.
2367 CURSOR cur_get_contract_end_date(p_contract_line_id NUMBER) IS
2368 SELECT least(nvl(end_date, date_terminated), nvl(date_terminated, end_date))
2369 FROM OKC_K_Lines_B
2370 WHERE id = p_contract_line_id
2371 ;
2372
2373 BEGIN
2374 IF (p_repair_Line_id IS NOT NULL) THEN
2375 OPEN cur_get_ro_contract_id (p_repair_line_id);
2376 FETCH cur_get_ro_contract_id INTO l_ro_contract_id;
2377 CLOSE cur_get_ro_contract_id;
2378
2379 --** debug starts!!
2380 --dbms_output.put_line('+++-- CHECK_CONTRACT_EXP_DATE , l_ro_contract_id = ' || l_ro_contract_id);
2381 --** debug ends!!
2382
2383 IF (l_ro_contract_id IS NOT NULL) AND (l_ro_contract_id <> FND_API.G_MISS_NUM) THEN
2384 OPEN cur_get_contract_end_date (l_ro_contract_id);
2385 FETCH cur_get_contract_end_date INTO l_contract_end_date;
2386 CLOSE cur_get_contract_end_date;
2387
2388 --** debug starts!!
2389 --dbms_output.put_line('+++-- CHECK_CONTRACT_EXP_DATE , l_contract_end_date = ' || l_contract_end_date);
2390 --** debug ends!!
2391
2392
2393 IF (l_contract_end_date IS NOT NULL) AND (l_contract_end_date <> FND_API.G_MISS_DATE) THEN
2394 l_number_input := l_contract_end_date - sysdate;
2395 l_return_val := CHECK_CONDITION_MATCH(p_input_param => l_number_input,
2396 p_operator => p_operator,
2397 p_criterion => to_number(p_criterion));
2398 END IF; --* end IF (l_contract_end_date IS NOT NULL)...*--
2399 END IF; --* end IF (l_ro_contract_id IS NOT NULL)... *--
2400 END IF; --* end IF (p_repair_Line_id IS NOT NULL)... *--
2401
2402
2403 RETURN l_return_val;
2404 END CHECK_CONTRACT_EXP_DATE;
2405
2406
2407 /* probably should be moved to util package */
2408 /*--------------------------------------------------------------------*/
2409 /* procedure name: GET_RO_INSTANCE_ID */
2410 /* description : returns customer_producet_id of the SR header for */
2411 /* the repair line */
2412 /* */
2413 /* Called from : FUNCTION MATCH_CONDITION */
2414 /* Input Parm : */
2415 /* p_contract_id NUMBER Req */
2416 /* */
2417 /* */
2418 /* Return Val : */
2419 /* NUMBER - Instance ID */
2420 /* */
2421 /* Change Hist : Jan-14-08 rfieldma created */
2422 /* */
2423 /* */
2427 p_repair_line_id IN NUMBER
2424 /* */
2425 /*--------------------------------------------------------------------*/
2426 FUNCTION GET_RO_INSTANCE_ID(
2428 ) RETURN NUMBER IS
2429 ---- local variables ----
2430 l_instance_id NUMBER := NULL;
2431 ---- cursors ----
2432 CURSOR cur_get_instance_id(p_repair_line_id NUMBER) IS
2433 SELECT customer_product_id
2434 FROM csd_repairs
2435 WHERE repair_line_id = p_repair_line_id
2436 ;--* end cur_get_instance_id *--
2437 BEGIN
2438 IF (p_repair_Line_id IS NOT NULL) THEN
2439 OPEN cur_get_instance_id(p_repair_line_id);
2440 FETCH cur_get_instance_id INTO l_instance_id;
2441
2442 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
2443 l_instance_id := NULL; -- force value to null
2444 END IF;
2445 CLOSE cur_get_instance_id;
2446 END IF; --* end IF (p_repair_Line_id IS NOT NULL) *--
2447
2448 --** debug starts!!
2449 --dbms_output.put_line(' *helper* GET_RO_INSTANCE_ID l_instance_id = ' || l_instance_id);
2450 --** debug ends!!
2451
2452 RETURN l_instance_id;
2453 END GET_RO_INSTANCE_ID;
2454
2455
2456 /*--------------------------------------------------------------------*/
2457 /* function name: GET_RULE_SQL_FOR_RO */
2458 /* description : Given a single rule, generate a sql query */
2459 /* that will match all repair orders for all its */
2460 /* conditions */
2461 /* */
2462 /* Called from : PROCEDURE LINK_BULLETIN_FOR_RULE */
2463 /* Input Parm : */
2464 /* l_rule_condition_rec CSD_RULE_CONDITION_REC_TYPE Req */
2465 /* */
2466 /* */
2467 /* Return Val : */
2468 /* VARCHAR2 - SQL Query to get ROs for rule condition */
2469 /* */
2470 /*--------------------------------------------------------------------*/
2471 FUNCTION GET_RULE_SQL_FOR_RO(
2472 p_rule_id IN NUMBER
2473 ) RETURN VARCHAR2
2474 IS
2475 -- CURSORS --
2476 CURSOR c_rule_conditions (p_rule_id number) IS
2477 SELECT rule_condition_id,
2478 rule_id,
2479 attribute_category,
2480 attribute1,
2481 attribute2,
2482 attribute3,
2483 attribute4,
2484 attribute5,
2485 attribute6,
2486 attribute7,
2487 attribute8,
2488 attribute9,
2489 attribute10,
2490 attribute11,
2491 attribute12,
2492 attribute13,
2493 attribute14,
2494 attribute15
2495 FROM CSD_RULE_CONDITIONS_B
2496 WHERE rule_id = p_rule_id;
2497
2498 -- VARIABLES --
2499 l_sql_query VARCHAR2(32767) := null;
2500 l_rule_condition_rec CSD_RULES_ENGINE_PVT.CSD_RULE_CONDITION_REC_TYPE;
2501 l_join_stmt VARCHAR2(3000) := null;
2502 l_operator VARCHAR2 (2);
2503 l_num_condition VARCHAR2(3000); -- for conditions that match a number type
2504 l_str_condition VARCHAR2(3000); -- for conditions that match a string type
2505 l_condition_count NUMBER :=0;
2506
2507 BEGIN
2508 l_sql_query := 'select dra.repair_line_id from csd_repairs dra, cs_incidents_b_sec csb '
2509 || 'where csb.incident_id = dra.incident_id';
2510 OPEN c_rule_conditions(p_rule_id);
2511 LOOP
2512 FETCH c_rule_conditions into l_rule_condition_rec;
2513 EXIT WHEN c_rule_conditions%NOTFOUND;
2514
2515 -- if there is a condition to be processed, then increment the count
2516 l_condition_count := l_condition_count + 1;
2517
2518 -- try to build the the join statement
2519 l_join_stmt := null;
2520 l_operator := GET_SQL_OPERATOR(l_rule_condition_rec.attribute1);
2521 IF (l_operator is not null) AND (l_rule_condition_rec.attribute2 is not null) THEN
2522 l_num_condition := l_operator || ' ' || l_rule_condition_rec.attribute2;
2523 l_str_condition := l_operator || ' ''' || l_rule_condition_rec.attribute2 || '''';
2524
2525 case l_rule_condition_rec.attribute_category
2526 when 'USER_ID' then
2527 l_join_stmt := 'FND_GLOBAL.USER_ID ' || l_num_condition;
2528 when 'USER_RESPONSIBILITY' then
2529 l_join_stmt := 'FND_GLOBAL.RESP_ID ' || l_num_condition;
2530 when 'USER_INV_ORG' then
2531 l_join_stmt := 'FND_PROFILE.VALUE(''' || G_PROFILE_INV_ORG || ''') '
2532 || l_num_condition;
2533 when 'USER_OU' then
2534 l_join_stmt := 'FND_GLOBAL.ORG_ID ' || l_num_condition;
2535 when 'SR_CUSTOMER_ID' then
2536 l_join_stmt := 'csb.customer_id ' || l_num_condition;
2537 when 'SR_CUSTOMER_ACCOUNT_ID' then
2538 l_join_stmt := 'csb.account_id ' || l_num_condition;
2539 when 'SR_BILL_TO_COUNTRY' then
2540 l_join_stmt := 'csb.bill_to_site_use_id in (select hpsu.party_site_use_id'
2541 || ' from hz_party_sites hps, hz_locations hl, hz_party_site_uses hpsu'
2545 || ' and hl.country '
2542 || ' where hps.location_id = hl.location_id'
2543 || ' and hps.party_site_id = hpsu.party_site_id'
2544 || ' and hpsu.party_site_use_id = csb.bill_to_site_use_id'
2546 || l_str_condition || ')';
2547 when 'SR_SHIP_TO_COUNTRY' then
2548 l_join_stmt := 'csb.ship_to_site_use_id in (select hpsu.party_site_use_id'
2549 || ' from hz_party_sites hps, hz_locations hl, hz_party_site_uses hpsu'
2550 || ' where hps.location_id = hl.location_id'
2551 || ' and hps.party_site_id = hpsu.party_site_id'
2552 || ' and hpsu.party_site_use_id = csb.ship_to_site_use_id'
2553 || ' and hl.country '
2554 || l_str_condition || ')';
2555 when 'SR_ITEM_ID' then
2556 l_join_stmt := 'csb.inventory_item_id ' || l_num_condition;
2557 when 'SR_ITEM_CATEGORY_ID' then
2558 l_join_stmt := 'csb.category_id ' || l_num_condition;
2559 when 'SR_CONTRACT_ID' then
2560 l_join_stmt := 'csb.contract_id ' || l_num_condition;
2561 when 'SR_PROBLEM_CODE' then
2562 l_join_stmt := 'csb.problem_code ' || l_str_condition;
2563 -- swai: 12.1.1 ER 7233924
2564 when 'RO_ITEM_ID' then
2565 l_join_stmt := 'dra.inventory_item_id ' || l_num_condition;
2566 -- swai: 12.1.1 ER 7233924
2567 when 'RO_ITEM_CATEGORY_ID' then
2568 if (l_rule_condition_rec.attribute1 = 'EQUALS') then
2569 l_join_stmt := 'exists';
2570 elsif(l_rule_condition_rec.attribute1 = 'NOT_EQUALS') then
2571 l_join_stmt := 'not exists';
2572 else
2573 l_join_stmt := null;
2574 end if;
2575
2576 if (l_join_stmt is not null) then
2577 l_join_stmt := l_join_stmt || ' (select ''X'''
2578 || ' from mtl_item_categories_v cat'
2579 || ' where cat.inventory_item_id = dra.inventory_item_id'
2580 || ' and cat.organization_id = cs_std.get_item_valdn_orgzn_id'
2581 || ' and cat.category_id = '
2582 || l_rule_condition_rec.attribute2 || ')';
2583 end if;
2584
2585 when 'RO_PROMISE_DATE_THRESHOLD' then
2586 l_join_stmt := '(dra.promise_date - sysdate) ' || l_num_condition;
2587 when 'RO_RESOLVE_BY_DATE_THRESHOLD' then
2588 l_join_stmt := '(dra.resolve_by_date - sysdate) ' || l_num_condition;
2589 when 'RO_EXCHANGE_THRESHOLD' then
2590 l_join_stmt := 'dra.repair_line_id in (select prod.repair_line_id'
2591 || ' from csd_product_txns_v prod'
2592 || ' WHERE prod.action_type = ''' || G_ACTION_TYPE_RMA || ''''
2593 || ' AND prod.action_code = ''' || G_ACTION_CODE_EXCHANGE || ''''
2594 || ' AND nvl(prod.prod_txn_status, '''') <> ''RECEIVED''' -- swai: bug 7524870
2595 || ' AND (prod.return_by_date - sysdate) '
2596 || l_num_condition || ')';
2597 when 'RO_LOANER_THRESHOLD' then
2598 l_join_stmt := 'dra.repair_line_id in (select prod.repair_line_id'
2599 || ' from csd_product_txns_v prod'
2600 || ' WHERE prod.action_type = ''' || G_ACTION_TYPE_RMA || ''''
2601 || ' AND prod.action_code = ''' || G_ACTION_CODE_LOANER || ''''
2602 || ' AND nvl(prod.prod_txn_status, '''') <> ''RECEIVED''' -- swai: bug 7524870
2603 || ' AND (prod.return_by_date - sysdate) '
2604 || l_num_condition || ')';
2605 when 'RO_THIRD_PTY_THRESHOLD' then
2606 l_join_stmt := 'dra.repair_line_id in (select prod.repair_line_id'
2607 || ' from csd_product_txns_v prod'
2608 || ' WHERE prod.action_type = ''' || G_ACTION_TYPE_RMA_THIRD_PTY || ''''
2609 || ' AND nvl(prod.prod_txn_status, '''') <> ''RECEIVED''' -- swai: bug 7524870
2610 || ' AND (prod.return_by_date - sysdate) '
2611 || l_num_condition || ')';
2612 -- swai: bug 7524870 - add new condition
2613 when 'RO_RMA_CUST_PROD_THRESHOLD' then
2614 l_join_stmt := 'dra.repair_line_id in (select prod.repair_line_id'
2615 || ' from csd_product_txns_v prod'
2616 || ' WHERE prod.action_type = ''' || G_ACTION_TYPE_RMA || ''''
2617 || ' AND prod.action_code = ''' || G_ACTION_CODE_CUST_PROD || ''''
2618 || ' AND nvl(prod.prod_txn_status, '''') <> ''RECEIVED'''
2619 || ' AND (prod.return_by_date - sysdate) '
2620 || l_num_condition || ')';
2621 -- end swai: bug 7524870
2622 when 'RO_REPEAT_REPAIR_THRESHOLD' then
2623 l_join_stmt := 'sysdate - ( SELECT MAX(dra2.date_closed)'
2624 || ' FROM csd_repairs dra2 '
2625 || ' WHERE dra2.customer_product_id = dra.customer_product_id '
2629 l_join_stmt := '(SELECT count(dra2.repair_line_id) '
2626 || ' AND dra2.date_closed IS NOT NULL) '
2627 || l_num_condition;
2628 when 'RO_CHRONIC_REPAIR_THRESHOLD' then
2630 || ' FROM csd_repairs dra2 '
2631 || ' WHERE dra2.customer_product_id = dra.customer_product_id '
2632 || ' AND dra2.date_closed BETWEEN sysdate - '
2633 || ' nvl(FND_PROFILE.VALUE(''CSD_QUALITY_CHECK_PERIOD''), 0) '
2634 || ' AND sysdate) ' || l_num_condition;
2635 -- bug 7323831 - contract line can expire if either end date or termination date
2636 -- has passed. If either date is null, do not consider it as a valid date.
2637 when 'RO_CONTRACT_EXP_THRESHOLD' then
2638 l_join_stmt := 'dra.contract_line_id in (select okl.id'
2639 || ' from okc_k_lines_b okl'
2640 || ' where okl.id = dra.contract_line_id'
2641 || ' AND (least(nvl(end_date, date_terminated), nvl(date_terminated, end_date)) - sysdate)'
2642 -- || ' AND (okl.end_date - sysdate) '
2643 || l_num_condition || ')';
2644 end case;
2645 end if;
2646
2647 -- If unsuccessful in building join statement, create one
2648 -- that will always make this query return no rows
2649 IF (l_join_stmt is null) THEN
2650 l_join_stmt := ' 1=0 ';
2651 END IF;
2652
2653 -- append the join statement to the existing query
2654 l_sql_query := l_sql_query || ' AND ' || l_join_stmt;
2655
2656 END LOOP;
2657
2658 -- if there were no conditions in the rule, then ensure that the query
2659 -- returns no rows, since a rule without conditions is not applicable
2660 -- to any repair order.
2661 IF (l_condition_count = 0) THEN
2662 l_sql_query := l_sql_query || ' AND 1=0';
2663 END IF;
2664
2665 RETURN l_sql_query;
2666 END GET_RULE_SQL_FOR_RO;
2667
2668
2669
2670 /*--------------------------------------------------------------------*/
2671 /* function name: GET_SQL_OPERATOR */
2672 /* description : Turns the given operator into the corresponding */
2673 /* operator symbol used in a sql query */
2674 /* */
2675 /* Called from : FUNCTION GET_RULE_SQL_FOR_RO */
2676 /* Input Parm : */
2677 /* p_operator VARCHAR2 Req */
2678 /* */
2679 /* */
2680 /* Return Val : */
2681 /* VARCHAR2 - Operator Lookup code from CSD_RULE_OPERATORS */
2682 /* */
2683 /*--------------------------------------------------------------------*/
2684 FUNCTION GET_SQL_OPERATOR (
2685 p_operator IN VARCHAR2
2686 ) RETURN VARCHAR2
2687 IS
2688 BEGIN
2689 CASE P_OPERATOR
2690 when G_EQUALS then
2691 return '=';
2692 when G_NOT_EQUALS then
2693 return '<>';
2694 when G_GREATER_THAN then
2695 return '>';
2696 when G_LESS_THAN then
2697 return '<';
2698 else
2699 return null;
2700 END CASE;
2701 END GET_SQL_OPERATOR;
2702
2703 END CSD_RULES_ENGINE_PVT; /* package ends here */