[Home] [Help]
PACKAGE: APPS.CSD_RULES_ENGINE_PVT
Source
1 PACKAGE CSD_RULES_ENGINE_PVT as
2 /* $Header: csdvruls.pls 120.1.12010000.4 2008/11/10 20:15:09 swai ship $ */
3 -- Start of Comments
4 -- Package name : CSD_RULES_ENGINE_PVT
5 -- Purpose : Jan-14-2008 rfieldma created
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 -- Default number of records fetch per call
11 G_DEFAULT_NUM_REC_FETCH CONSTANT NUMBER := 30;
12 G_EQUALS CONSTANT VARCHAR2(6) := 'EQUALS';
13 G_NOT_EQUALS CONSTANT VARCHAR2(10) := 'NOT_EQUALS';
14 G_LESS_THAN CONSTANT VARCHAR2(9) := 'LESS_THAN';
15 G_GREATER_THAN CONSTANT VARCHAR2(12) := 'GREATER_THAN';
16 G_RULE_TYPE_BULLETIN CONSTANT VARCHAR2(8) := 'BULLETIN';
17 G_RULE_TYPE_DEFAULTING CONSTANT VARCHAR2(10) := 'DEFAULTING';
18 G_RULE_MATCH_ONE CONSTANT NUMBER := 1; -- used by CSD_RULE_MATCHING_REC_TYPE.RULE_MATCH_CODE
19 G_RULE_MATCH_ALL CONSTANT NUMBER := 2; -- used by CSD_RULE_MATCHING_REC_TYPE.RULE_MATCH_CODE
20 G_VALUE_TYPE_ATTRIBUTE CONSTANT VARCHAR2(9) := 'ATTRIBUTE';
21 G_VALUE_TYPE_PROFILE CONSTANT VARCHAR2(7) := 'PROFILE';
22 G_VALUE_TYPE_PLSQL CONSTANT VARCHAR2(9) := 'PLSQL_API';
23 G_L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
24
25
26 G_ATTR_TYPE_RO CONSTANT VARCHAR2(22) := 'CSD_DEF_ENTITY_ATTR_RO';
27 G_ATTR_CODE_REPAIR_ORG CONSTANT VARCHAR2(10) := 'REPAIR_ORG';
28 G_ATTR_CODE_REPAIR_OWNER CONSTANT VARCHAR2(12) := 'REPAIR_OWNER';
29 G_ATTR_CODE_INV_ORG CONSTANT VARCHAR2(7) := 'INV_ORG';
30 G_ATTR_CODE_RMA_RCV_ORG CONSTANT VARCHAR2(11) := 'RMA_RCV_ORG';
31 G_ATTR_CODE_RMA_RCV_SUBINV CONSTANT VARCHAR2(14) := 'RMA_RCV_SUBINV';
32 G_ATTR_CODE_PRIORITY CONSTANT VARCHAR2(8) := 'PRIORITY';
33 G_ATTR_CODE_REPAIR_TYPE CONSTANT VARCHAR2(11) := 'REPAIR_TYPE';
34 G_ATTR_CODE_SHIP_FROM_ORG CONSTANT VARCHAR2(13) := 'SHIP_FROM_ORG';
35 G_ATTR_CODE_SHIP_FROM_SUBINV CONSTANT VARCHAR2(16) := 'SHIP_FROM_SUBINV';
36 G_ATTR_CODE_VENDOR_ACCOUNT CONSTANT VARCHAR2(14) := 'VENDOR_ACCOUNT';
37
38 G_PROFILE_REPAIR_ORG CONSTANT VARCHAR2(22) := 'CSD_DEFAULT_REPAIR_ORG';
39 G_PROFILE_REPAIR_TYPE CONSTANT VARCHAR2(23) := 'CSD_DEFAULT_REPAIR_TYPE';
40 G_PROFILE_INV_ORG CONSTANT VARCHAR2(19) := 'CSD_DEF_REP_INV_ORG';
41 G_PROFILE_QUALITY_CHECK_PERIOD CONSTANT VARCHAR2(24) := 'CSD_QUALITY_CHECK_PERIOD';
42
43 G_ACTION_TYPE_RMA CONSTANT VARCHAR2(3) := 'RMA';
44 G_ACTION_TYPE_RMA_THIRD_PTY CONSTANT VARCHAR2(13) := 'RMA_THIRD_PTY';
45
46 G_ACTION_CODE_EXCHANGE CONSTANT VARCHAR2(8) := 'EXCHANGE';
47 G_ACTION_CODE_LOANER CONSTANT VARCHAR2(6) := 'LOANER';
48 G_ACTION_CODE_CUST_PROD CONSTANT VARCHAR2(9) := 'CUST_PROD'; -- swai: bug 7524870
49
50 /*--------------------------------------------------------------------*/
51 /* Record name: CSD_RULE_CONDITION_REC_TYPE */
52 /* Description : Record used for single match from rules engine */
53 /* */
54 /* */
55 /* Called from : Depot Repair Rule Engine */
56 /* */
57 /*--------------------------------------------------------------------*/
58 TYPE CSD_RULE_CONDITION_REC_TYPE IS RECORD
59 (
60 RULE_CONDITION_ID NUMBER
61 , RULE_ID NUMBER
62 , ATTRIBUTE_CATEGORY VARCHAR2(30)
63 , ATTRIBUTE1 VARCHAR2(150)
64 , ATTRIBUTE2 VARCHAR2(150)
65 , ATTRIBUTE3 VARCHAR2(150)
66 , ATTRIBUTE4 VARCHAR2(150)
67 , ATTRIBUTE5 VARCHAR2(150)
68 , ATTRIBUTE6 VARCHAR2(150)
69 , ATTRIBUTE7 VARCHAR2(150)
70 , ATTRIBUTE8 VARCHAR2(150)
71 , ATTRIBUTE9 VARCHAR2(150)
72 , ATTRIBUTE10 VARCHAR2(150)
73 , ATTRIBUTE11 VARCHAR2(150)
74 , ATTRIBUTE12 VARCHAR2(150)
75 , ATTRIBUTE13 VARCHAR2(150)
76 , ATTRIBUTE14 VARCHAR2(150)
77 , ATTRIBUTE15 VARCHAR2(150)
78 );
79
80 /*--------------------------------------------------------------------*/
81 /* Type to hold multiple rule conditions */
82 /*--------------------------------------------------------------------*/
83 TYPE CSD_RULE_CONDITION_TBL_TYPE IS TABLE OF CSD_RULE_CONDITION_REC_TYPE
84 INDEX BY BINARY_INTEGER;
85
86 /*--------------------------------------------------------------------*/
87 /* Record name: CSD_RULE_INPUT_REC_TYPE */
88 /* Description : Record used for Input values into rules engine */
89 /* */
90 /* The following are valid criteria for the rules engine: */
91 /* User */
92 /* User Responsibility */
93 /* User Inventory Org */
94 /* User Operating Unit */
95 /* SR Customer */
96 /* SR Customer Account */
97 /* SR Bill to country */
98 /* SR Ship to country */
99 /* SR Item */
100 /* SR Item Category */
101 /* SR Contract Entitlement */
102 /* SR Problem Code */
103 /* */
104 /* Called from: Depot Repair Rules Engine */
105 /* Change Hist : Jan-14-08 rfieldma created */
106 /*--------------------------------------------------------------------*/
107 TYPE CSD_RULE_INPUT_REC_TYPE IS RECORD
108 (
109 REPAIR_LINE_ID NUMBER
110 , SR_CUSTOMER_ID NUMBER
111 , SR_CUSTOMER_ACCOUNT_ID NUMBER
112 , SR_BILL_TO_SITE_USE_ID NUMBER
113 , SR_SHIP_TO_SITE_USE_ID NUMBER
114 , SR_ITEM_ID NUMBER
115 , SR_ITEM_CATEGORY_ID NUMBER
116 , SR_CONTRACT_ID NUMBER
117 , SR_PROBLEM_CODE VARCHAR2(30)
118 , SR_INSTANCE_ID NUMBER
119 , RO_ITEM_ID NUMBER -- swai: 12.1.1 ER 7233924
120 );
121
122
123 /*--------------------------------------------------------------------*/
124 /* Record name: CSD_RULE_RESULTS_REC_TYPE */
125 /* Description : Record used for single match from rules engine */
126 /* */
127 /* */
128 /* Called from : Depot Repair Rule Engine */
129 /* Change Hist : Jan-14-08 rfieldma created */
130 /* */
131 /*--------------------------------------------------------------------*/
132 TYPE CSD_RULE_RESULTS_REC_TYPE IS RECORD
133 (
134 RULE_ID NUMBER
135 , DEFAULTING_VALUE VARCHAR2(150)
136 , VALUE_TYPE VARCHAR2(30)
137
138 );
139
140 /*--------------------------------------------------------------------*/
141 /* Type to Return multiple results from rules engine */
142 /* */
143 /* */
144 /* Called from : Depot Repair */
145 /* Change Hist : Jan-14-08 rfieldma created */
146 /*--------------------------------------------------------------------*/
147 TYPE CSD_RULE_RESULTS_TBL_TYPE IS TABLE OF CSD_RULE_RESULTS_REC_TYPE
148 INDEX BY BINARY_INTEGER;
149
150
151 /*--------------------------------------------------------------------*/
152 /* Record name: CSD_RULE_MATCHING_REC_TYPE */
153 /* Description : Record used for Input values into rules engine */
154 /* */
155 /* */
156 /* RULE_MATCH_CODE has the following meanings: */
157 /* 1 - Find the first matching rule in order of precedence */
158 /* 2 - Find all matching rules regardless of precedence */
159 /* */
160 /* RULE_TYPE - lookup code from CSD_RULE_TYPES of rule type match */
161 /* */
162 /* DEFAULTING_ATTRIBUTE_ID */
163 /* Primary key from CSD_DEFAULTING_ATTRIBUTES_B to match */
164 /* with rules */
165 /* */
166 /* */
167 /* Called from : Depot Repair Rules Engine */
168 /* Change Hist : Jan-14-08 rfieldma created */
169 /* */
170 /* */
171 /* */
172 /*--------------------------------------------------------------------*/
173 TYPE CSD_RULE_MATCHING_REC_TYPE IS RECORD
174 (
175 RULE_MATCH_CODE NUMBER
176 , RULE_TYPE VARCHAR2(30)
177 , ENTITY_ATTRIBUTE_TYPE VARCHAR2(30)
178 , ENTITY_ATTRIBUTE_CODE VARCHAR2(30)
179 , RULE_INPUT_REC CSD_RULE_INPUT_REC_TYPE
180 , RULE_RESULTS_TBL CSD_RULE_RESULTS_TBL_TYPE
181 );
182
183
184 /*--------------------------------------------------------------------*/
185 /* procedure name: PROCESS_RULE_MATCHING */
186 /* description : procedure used to Match Rules with input data */
187 /* */
188 /* */
189 /* */
190 /* Called from : Depot Repair Bulletins */
191 /* Input Parm : */
192 /* p_api_version NUMBER Req Api Version number */
193 /* p_init_msg_list VARCHAR2 Opt Initialize message stack */
194 /* p_commit VARCHAR2 Opt Commits in API */
195 /* p_validation_level NUMBER Opt validation steps */
196 /* px_rule_matching_rec CSD_RULE_MATCHING_REC_TYPE */
197 /* Output Parm : */
198 /* x_return_status VARCHAR2 Return status after the call. */
199 /* x_msg_count NUMBER Number of messages in stack */
200 /* x_msg_data VARCHAR2 Mesg. text if x_msg_count >= 1 */
201 /* Change Hist : Jan-14-08 rfieldma created */
202 /* */
203 /* */
204 /* */
205 /*--------------------------------------------------------------------*/
206 PROCEDURE PROCESS_RULE_MATCHING(
207 p_api_version_number IN NUMBER,
208 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
209 p_commit IN VARCHAR2 := FND_API.G_FALSE,
210 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
211 px_rule_matching_rec IN OUT NOCOPY CSD_RULE_MATCHING_REC_TYPE,
212 x_return_status OUT NOCOPY VARCHAR2,
213 x_msg_count OUT NOCOPY NUMBER,
214 x_msg_data OUT NOCOPY VARCHAR2
215 );
216
217
218 /*--------------------------------------------------------------------*/
219 /* procedure name: GET_DEFAULT_VALUE_FROM_RULE (overloaded) */
220 /* description : procedure used to get default values from rules */
221 /* default value = VARCHAR2 data type */
222 /* */
223 /* */
224 /* Called from : Depot Repair Workbench defaulting */
225 /* Input Parm : */
226 /* p_api_version NUMBER Req Api Version number */
227 /* p_init_msg_list VARCHAR2 Opt Initialize message stack */
228 /* p_commit VARCHAR2 Opt Commits in API */
229 /* p_validation_level NUMBER Opt validation steps */
230 /* p_entity_attribute_type VARCHAR2 Req */
231 /* p_entity_attribute_code VARCHAR2 Req */
232 /* p_rule_input_rec CSD_RULE_INPUT_REC_TYPE Req */
233 /* Output Parm : */
234 /* x_return_status VARCHAR2 Return status after the call. */
235 /* x_msg_count NUMBER Number of messages in stack */
236 /* x_msg_data VARCHAR2 Mesg. text if x_msg_count >= 1 */
237 /* x_default_value VARCHAR2 */
238 /* x_rule_id NUMBER Rule ID that determined value */
239 /* if null, then no rule used */
240 /* Change Hist : Jan-14-08 rfieldma created */
241 /* Aug-20-08 swai added param x_rule_id */
242 /* */
243 /* */
244 /*--------------------------------------------------------------------*/
245 PROCEDURE GET_DEFAULT_VALUE_FROM_RULE (
246 p_api_version_number IN NUMBER,
247 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
248 p_commit IN VARCHAR2 := FND_API.G_FALSE,
249 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
250 p_entity_attribute_type IN VARCHAR2,
251 p_entity_attribute_code IN VARCHAR2,
252 p_rule_input_rec IN CSD_RULE_INPUT_REC_TYPE,
253 x_default_value OUT NOCOPY VARCHAR2,
254 x_rule_id OUT NOCOPY NUMBER, -- swai: 12.1.1 ER 7233924
255 x_return_status OUT NOCOPY VARCHAR2,
256 x_msg_count OUT NOCOPY NUMBER,
257 x_msg_data OUT NOCOPY VARCHAR2
258 );
259
260 /*--------------------------------------------------------------------*/
261 /* procedure name: GET_DEFAULT_VALUE_FROM_RULE (overloaded) */
262 /* description : procedure used to get default values from rules */
263 /* default value = NUMBER data type */
264 /* */
265 /* */
266 /* Called from : Depot Repair Workbench defaulting */
267 /* Input Parm : */
268 /* p_api_version NUMBER Req Api Version number */
269 /* p_init_msg_list VARCHAR2 Opt Initialize message stack */
270 /* p_commit VARCHAR2 Opt Commits in API */
271 /* p_validation_level NUMBER Opt validation steps */
272 /* p_entity_attribute_type VARCHAR2 Req */
273 /* p_entity_attribute_code VARCHAR2 Req */
274 /* p_rule_input_rec CSD_RULE_INPUT_REC_TYPE Req */
275 /* Output Parm : */
276 /* x_return_status VARCHAR2 Return status after the call. */
277 /* x_msg_count NUMBER Number of messages in stack */
281 /* if null, then no rule used */
278 /* x_msg_data VARCHAR2 Mesg. text if x_msg_count >= 1 */
279 /* x_default_value NUMBER */
280 /* x_rule_id NUMBER Rule ID that determined value */
282 /* Change Hist : Jan-14-08 rfieldma created */
283 /* Aug-20-08 swai added param x_rule_id */
284 /* */
285 /* */
286 /*--------------------------------------------------------------------*/
287 PROCEDURE GET_DEFAULT_VALUE_FROM_RULE (
288 p_api_version_number IN NUMBER,
289 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
290 p_commit IN VARCHAR2 := FND_API.G_FALSE,
291 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
292 p_entity_attribute_type IN VARCHAR2,
293 p_entity_attribute_code IN VARCHAR2,
294 p_rule_input_rec IN CSD_RULE_INPUT_REC_TYPE,
295 x_default_value OUT NOCOPY NUMBER,
296 x_rule_id OUT NOCOPY NUMBER, -- swai: 12.1.1 ER 7233924
297 x_return_status OUT NOCOPY VARCHAR2,
298 x_msg_count OUT NOCOPY NUMBER,
299 x_msg_data OUT NOCOPY VARCHAR2
300 );
301
302 /*--------------------------------------------------------------------*/
303 /* procedure name: GET_DEFAULT_VALUE_FROM_RULE (overloaded) */
304 /* description : procedure used to get default values from rules */
305 /* default value = DATE data type */
306 /* */
307 /* */
308 /* Called from : Depot Repair Workbench defaulting */
309 /* Input Parm : */
310 /* p_api_version NUMBER Req Api Version number */
311 /* p_init_msg_list VARCHAR2 Opt Initialize message stack */
312 /* p_commit VARCHAR2 Opt Commits in API */
313 /* p_validation_level NUMBER Opt validation steps */
314 /* p_entity_attribute_type VARCHAR2 Req */
315 /* p_entity_attribute_code VARCHAR2 Req */
316 /* p_rule_input_rec CSD_RULE_INPUT_REC_TYPE Req */
317 /* Output Parm : */
318 /* x_return_status VARCHAR2 Return status after the call. */
319 /* x_msg_count NUMBER Number of messages in stack */
320 /* x_msg_data VARCHAR2 Mesg. text if x_msg_count >= 1 */
321 /* x_default_value DATE */
322 /* x_rule_id NUMBER Rule ID that determined value */
323 /* if null, then no rule used */
324 /* Change Hist : Jan-14-08 rfieldma created */
325 /* Aug-20-08 swai added param x_rule_id */
326 /* */
327 /* */
328 /*--------------------------------------------------------------------*/
329 PROCEDURE GET_DEFAULT_VALUE_FROM_RULE (
330 p_api_version_number IN NUMBER,
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,
334 p_entity_attribute_type IN VARCHAR2,
335 p_entity_attribute_code IN VARCHAR2,
336 p_rule_input_rec IN CSD_RULE_INPUT_REC_TYPE,
337 x_default_value OUT NOCOPY DATE,
338 x_rule_id OUT NOCOPY NUMBER, -- swai: 12.1.1 ER 7233924
339 x_return_status OUT NOCOPY VARCHAR2,
340 x_msg_count OUT NOCOPY NUMBER,
341 x_msg_data OUT NOCOPY VARCHAR2
342 );
343
344 /*--------------------------------------------------------------------*/
345 /* procedure name: MATCH_CONDITION */
346 /* description : procedure used to match parameter to criterion based */
347 /* on operatior */
348 /* Calls overloaded function - CHECK_CONDITION_MATCH */
349 /* */
350 /* */
351 /* Called from : PROCEDURE PROCESS_RULE_MATCHING */
352 /* Input Parm : */
353 /* p_parameter_type VARCHAR2 Req */
354 /* p_operator VARCHAR2 Req */
355 /* p_criterion VARCHAR2 Req */
356 /* p_rule_input_rec CSD_RULE_INPUT_REC_TYPE Req */
357 /* Return Val : */
358 /* VARCHAR2 G_MISS.G_TRUE/G_MISS.G_FALSE */
359 /* Change Hist : Jan-14-08 rfieldma created */
360 /* */
361 /* */
365 p_parameter_type IN VARCHAR2,
362 /* */
363 /*--------------------------------------------------------------------*/
364 FUNCTION MATCH_CONDITION (
366 p_operator IN VARCHAR2,
367 p_criterion IN VARCHAR2,
368 p_rule_input_rec IN CSD_RULE_INPUT_REC_TYPE
369 ) RETURN VARCHAR2;
370
371
372 /*--------------------------------------------------------------------*/
373 /* procedure name: CHECK_CONDITION_MATCH (overloaded) */
374 /* description : procedure used to check if parameter matches */
375 /* criterion based on operator */
376 /* parameter, criterion = NUMBER data type */
377 /* */
378 /* */
379 /* Called from : FUNCTION MATCH_CONDITION */
380 /* Input Parm : */
381 /* p_parameter_type NUMBER Req */
382 /* p_operator NUMBER Req */
383 /* p_criterion NUMBER Req */
384 /* Return Val : */
385 /* VARCHAR2 G_MISS.G_TRUE/G_MISS.G_FALSE */
386 /* Change Hist : Jan-14-08 rfieldma created */
387 /* */
388 /* */
389 /* */
390 /*--------------------------------------------------------------------*/
391 FUNCTION CHECK_CONDITION_MATCH (
392 p_input_param IN NUMBER,
393 p_operator IN VARCHAR2,
394 p_criterion IN NUMBER
395 ) RETURN VARCHAR2;
396
397
398 /*--------------------------------------------------------------------*/
399 /* procedure name: CHECK_CONDITION_MATCH (overloaded) */
400 /* description : procedure used to check if parameter matches */
401 /* criterion based on operator */
402 /* parameter, criterion = VARCHAR2 data type */
403 /* */
404 /* */
405 /* Called from : FUNCTION MATCH_CONDITION */
406 /* Input Parm : */
407 /* p_parameter_type VARCHAR2 Req */
408 /* p_operator VARCHAR2 Req */
409 /* p_criterion VARCHAR2 Req */
410 /* Return Val : */
411 /* VARCHAR2 G_MISS.G_TRUE/G_MISS.G_FALSE */
412 /* Change Hist : Jan-14-08 rfieldma created */
413 /* */
414 /* */
415 /* */
416 /*--------------------------------------------------------------------*/
417 FUNCTION CHECK_CONDITION_MATCH (
418 p_input_param IN VARCHAR2,
419 p_operator IN VARCHAR2,
420 p_criterion IN VARCHAR2
421 ) RETURN VARCHAR2;
422
423
424
425 /*--------------------------------------------------------------------*/
426 /* procedure name: CHECK_CONDITION_MATCH (overloaded) */
427 /* description : procedure used to check if parameter matches */
428 /* criterion based on operator */
429 /* parameter, criterion = DATE data type */
430 /* */
431 /* */
432 /* Called from : FUNCTION MATCH_CONDITION */
433 /* Input Parm : */
434 /* p_parameter_type DATE Req */
435 /* p_operator VARCHAR2 Req */
436 /* p_criterion DATE Req */
437 /* Return Val : */
438 /* VARCHAR2 G_MISS.G_TRUE/G_MISS.G_FALSE */
439 /* Change Hist : Jan-14-08 rfieldma created */
440 /* */
441 /* */
442 /* */
443 /*--------------------------------------------------------------------*/
444 FUNCTION CHECK_CONDITION_MATCH (
445 p_input_param IN DATE,
446 p_operator IN VARCHAR2,
447 p_criterion IN DATE
448 ) RETURN VARCHAR2;
449
450
451 /*--------------------------------------------------------------------*/
452 /* procedure name: COPY_RULE_INPUT_REC_VALUES */
453 /* description : copies source rec into dest rec */
454 /* rec typ = CSD_RULE_INPUT_REC_TYPE */
455 /* */
456 /* */
457 /* Called from : FUNCTION MATCH_CONDITION */
458 /* Input Parm : */
459 /* p_s_rec CSD_RULE_INPUT_REC_TYPE Req */
460 /* p_d_Rec CSD_RULE_INPUT_REC_TYPE VARCHAR2 Req */
461 /* Change Hist : Jan-14-08 rfieldma created */
462 /* */
463 /* */
464 /* */
465 /*--------------------------------------------------------------------*/
466 PROCEDURE COPY_RULE_INPUT_REC_VALUES(
467 p_s_rec IN CSD_RULE_INPUT_REC_TYPE, -- source rec
468 px_d_rec IN OUT NOCOPY CSD_RULE_INPUT_REC_TYPE -- destination rec
469 );
470
471 /*--------------------------------------------------------------------*/
472 /* procedure name: COPY_RULE_INPUT_REC_VALUES */
473 /* description : copies source rec into dest rec */
474 /* rec typ = CSD_RULE_INPUT_REC_TYPE */
475 /* */
476 /* */
477 /* Called from : FUNCTION MATCH_CONDITION */
478 /* Input Parm : */
479 /* p_s_rec CSD_RULE_INPUT_REC_TYPE Req */
480 /* p_d_Rec CSD_RULE_INPUT_REC_TYPE VARCHAR2 Req */
481 /* Change Hist : Jan-14-08 rfieldma created */
482 /* */
483 /* */
484 /* */
485 /*--------------------------------------------------------------------*/
486 PROCEDURE POPULATE_RULE_INPUT_REC(
487 px_rule_input_rec IN OUT NOCOPY CSD_RULE_INPUT_REC_TYPE,
488 p_repair_line_id IN NUMBER
489 );
490
491 /*--------------------------------------------------------------------*/
492 /* procedure name: GET_DEFAULT_VALUE */
493 /* description : retrieves default value based on type */
494 /* ATTRIBUTE -> return default value as is */
495 /* PROFILE -> return profile (default value) */
496 /* PLSQL -> execute function call stored in default */
497 /* value and cast return value to string */
498 /* and return that string value */
499 /* */
500 /* */
501 /* Called from : FUNCTION GET_DEFAULT_VALUE_FROM_RULE */
502 /* Input Parm : */
503 /* p_value_type VARCHAR2 Req */
504 /* p_defaulting_value VARCHAR2 Req */
505 /* p_attribute_type VARCHAR2 Req */
506 /* p_attribute_code VARCHAR2 Req */
507 /* x_return_status VARCHAR2 Req */
508 /* x_msg_count VARCHAR2 Req */
509 /* x_msg_data VARCHAR2 Req */
510 /* Return Val : */
511 /* VARCHAR2 - the actual default value */
512 /* */
513 /* Change Hist : Jan-14-08 rfieldma created */
514 /* */
515 /* */
516 /* */
517 /*--------------------------------------------------------------------*/
518 FUNCTION GET_DEFAULT_VALUE(
519 p_value_type IN VARCHAR2,
520 p_defaulting_value IN VARCHAR2,
521 p_attribute_type IN VARCHAR2,
522 p_attribute_code IN VARCHAR2
523 ) RETURN VARCHAR2;
524
525
529 /* */
526 /*--------------------------------------------------------------------*/
527 /* procedure name: GET_COUNTRY_CODE */
528 /* description : returns country code based on site_useid */
530 /* Called from : FUNCTION MATCH_CONDITION */
531 /* Input Parm : */
532 /* p_site_use_id NUMBER Req */
533 /* Return Val : */
534 /* VARCHAR2 - COUNTRY code */
535 /* */
536 /* Change Hist : Jan-14-08 rfieldma created */
537 /* */
538 /* */
539 /* */
540 /*--------------------------------------------------------------------*/
541 FUNCTION GET_COUNTRY_CODE(
542 p_site_use_id IN NUMBER
543 ) RETURN VARCHAR2;
544
545 /*--------------------------------------------------------------------*/
546 /* procedure name: CHECK_RO_ITEM_CATEGORY */
547 /* description : checks if the RO item is in the specified category */
548 /* This function assumes the service validation */
549 /* inventory org */
550 /* */
551 /* Called from : FUNCTION MATCH_CONDITION */
552 /* Input Parm : */
553 /* p_ro_item_id NUMBER Req RO Inventory Item Id */
554 /* p_operator VARCHAR2 Req 'EQUALS': check item is in category */
555 /* 'NOT_EQUALS': check item is not in */
556 /* item category */
557 /* p_criterion NUMBER Req Item Category Id */
558 /* Return Val : */
559 /* VARCHAR2 - G_TRUE or G_FALSE */
560 /* */
561 /* Change Hist : Aug-18-08 swai created for 12.1.1 ER 7233924 */
562 /* */
563 /*--------------------------------------------------------------------*/
564 FUNCTION CHECK_RO_ITEM_CATEGORY(
565 p_ro_item_id IN NUMBER,
566 p_operator IN VARCHAR2,
567 p_criterion IN NUMBER
568 ) RETURN VARCHAR2;
569
570
571 /*--------------------------------------------------------------------*/
572 /* procedure name: CHECK_PROMISE_DATE */
573 /* description : retrieves RO promise by date */
574 /* compare threshold with promise_date - sysdate */
575 /* */
576 /* Called from : FUNCTION MATCH_CONDITION */
577 /* Input Parm : */
578 /* p_repair_line_id NUMBER Req */
579 /* Return Val : */
580 /* VARCHAR2 - G_TRUE or G_FALSE */
581 /* */
582 /* Change Hist : Jan-14-08 rfieldma created */
583 /* */
584 /* */
585 /* */
586 /*--------------------------------------------------------------------*/
587 FUNCTION CHECK_PROMISE_DATE(
588 p_repair_line_id IN NUMBER,
589 p_operator IN VARCHAR2,
590 p_criterion IN VARCHAR2
591 ) RETURN VARCHAR2;
592
593
594 /*--------------------------------------------------------------------*/
595 /* procedure name: CHECK_RESOLVE_BY_DATE */
596 /* description : retrieves RO resolve by date */
597 /* compare threshold with resolve_by_date - sysdate */
598 /* */
599 /* Called from : FUNCTION MATCH_CONDITION */
600 /* Input Parm : */
601 /* p_repair_line_id NUMBER Req */
602 /* Return Val : */
603 /* VARCHAR2 - G_TRUE or G_FALSE */
604 /* */
605 /* Change Hist : Jan-14-08 rfieldma created */
606 /* */
607 /* */
608 /* */
609 /*--------------------------------------------------------------------*/
610 FUNCTION CHECK_RESOLVE_BY_DATE(
611 p_repair_line_id IN NUMBER,
612 p_operator IN VARCHAR2,
613 p_criterion IN VARCHAR2
614 ) RETURN VARCHAR2;
615
616
617 /*--------------------------------------------------------------------*/
618 /* procedure name: CHECK_RETURN_BY_DATE */
619 /* description : retrieves return by date on logistics line */
620 /* '%' => RMA_THIRD_PARTY line */
621 /* loaner => RMA line */
622 /* exchange => RMA line */
623 /* compare threshold with return by date - sysdate */
624 /* */
625 /* Called from : FUNCTION MATCH_CONDITION */
626 /* Input Parm : */
627 /* p_repair_line_id NUMBER Req */
628 /* p_action_type VARCHAR2 Req */
629 /* p_action_code VARCHAR2 Req */
630 /* Return Val : */
631 /* VARCHAR2 - G_TRUE or G_FALSE */
632 /* */
633 /* Change Hist : Jan-14-08 rfieldma created */
634 /* */
635 /* */
636 /* */
637 /*--------------------------------------------------------------------*/
638 FUNCTION CHECK_RETURN_BY_DATE(
639 p_repair_line_id IN NUMBER,
640 p_action_type IN VARCHAR2,
641 p_action_code IN VARCHAR2,
642 p_operator IN VARCHAR2,
643 p_criterion IN VARCHAR2
644 ) RETURN VARCHAR2;
645
649 /* 2) get the lastest repair based on the instance id */
646 /*--------------------------------------------------------------------*/
647 /* procedure name: CHECK_REPEAT_REPAIR */
648 /* description : 1) get instance id based on repair_line_id */
650 /* (order by closed_date desc ) */
651 /* NOTE: ideally, we would like to use the ship date */
652 /* on the logistics line. But due to the */
653 /* complexity, we are using closed_date for */
654 /* this release. */
655 /* Called from : FUNCTION MATCH_CONDITION */
656 /* Input Parm : */
657 /* p_repair_line_id NUMBER Req */
658 /* Return Val : */
659 /* VARCHAR2 - G_TRUE or G_FALSE */
660 /* */
661 /* Change Hist : Jan-14-08 rfieldma created */
662 /* */
663 /* */
664 /* */
665 /*--------------------------------------------------------------------*/
666 FUNCTION CHECK_REPEAT_REPAIR(
667 p_repair_line_id IN NUMBER,
668 p_operator IN VARCHAR2,
669 p_criterion IN VARCHAR2
670 ) RETURN VARCHAR2;
671
672 /*--------------------------------------------------------------------*/
673 /* procedure name: CHECK_CHRONIC_REPAIR */
674 /* description : 1) get instance id based on repair_line_id */
675 /* 2) get profile option CSD_QUALITY_CHECK_PERIOD value */
676 /* 3) query # of repair orders during this period */
677 /* (closed_date) */
678 /* NOTE: ideally, we would like to use the ship date */
679 /* on the logistics line. But due to the */
680 /* complexity, we are using closed_date for */
681 /* this release. */
682 /* Called from : FUNCTION MATCH_CONDITION */
683 /* Input Parm : */
684 /* p_repair_line_id NUMBER Req */
685 /* Return Val : */
686 /* VARCHAR2 - G_TRUE or G_FALSE */
687 /* */
688 /* Change Hist : Jan-14-08 rfieldma created */
689 /* */
690 /* */
691 /* */
692 /*--------------------------------------------------------------------*/
693 FUNCTION CHECK_CHRONIC_REPAIR(
694 p_repair_line_id IN NUMBER,
695 p_operator IN VARCHAR2,
696 p_criterion IN VARCHAR2
697 )RETURN VARCHAR2;
698
699 /*--------------------------------------------------------------------*/
700 /* procedure name: CHECK_CONTRACT_EXP_DATE */
701 /* description : calls OKS_ENTITLEMENTS_PUB.Get_Contracts_Expiration */
702 /* checks threshold with exp date - sysdate */
703 /* */
704 /* Called from : FUNCTION MATCH_CONDITION */
705 /* Input Parm : */
706 /* p_repair_line_id NUMBER Req */
707 /* */
708 /* */
709 /* Return Val : */
710 /* VARCHAR2 - G_TRUE or G_FALSE */
711 /* */
712 /* Change Hist : Jan-14-08 rfieldma created */
713 /* */
714 /* */
715 /* */
716 /*--------------------------------------------------------------------*/
717 FUNCTION CHECK_CONTRACT_EXP_DATE(
718 p_repair_line_id IN NUMBER,
719 p_operator IN VARCHAR2,
720 p_criterion IN VARCHAR2
721 ) RETURN VARCHAR2;
722
723 /* probably should be moved to util package */
724 /*--------------------------------------------------------------------*/
725 /* procedure name: GET_RO_INSTANCE_ID */
726 /* description : returns customer_producet_id instance id of RO */
727 /* */
728 /* Called from : FUNCTION MATCH_CONDITION */
729 /* Input Parm : */
730 /* p_contract_id NUMBER Req */
731 /* */
732 /* */
733 /* Return Val : */
734 /* NUMBER - Instance ID */
735 /* */
736 /* Change Hist : Jan-14-08 rfieldma created */
737 /* */
741 FUNCTION GET_RO_INSTANCE_ID(
738 /* */
739 /* */
740 /*--------------------------------------------------------------------*/
742 p_repair_line_id IN NUMBER
743 ) RETURN NUMBER;
744
745
746
747 /*--------------------------------------------------------------------*/
748 /* function name: GET_RULE_SQL_FOR_RO */
749 /* description : Given a single rule, generate a sql query */
750 /* that will match all repair orders for all the rule */
751 /* conditions */
752 /* */
753 /* Called from : PROCEDURE LINK_BULLETIN_FOR_RULE */
754 /* Input Parm : */
755 /* p_rule_id NUMBER Req */
756 /* */
757 /* */
758 /* Return Val : */
759 /* VARCHAR2 - SQL Query to get ROs for rule */
760 /* */
761 /*--------------------------------------------------------------------*/
762 FUNCTION GET_RULE_SQL_FOR_RO(
763 p_rule_id IN NUMBER
764 ) RETURN VARCHAR2;
765
766
767 /*--------------------------------------------------------------------*/
768 /* function name: GET_SQL_OPERATOR */
769 /* description : Turns the given operator into the corresponding */
770 /* operator symbol used in a sql query */
771 /* */
772 /* Called from : FUNCTION GET_RULE_SQL_FOR_RO */
773 /* Input Parm : */
774 /* p_operator VARCHAR2 Req */
775 /* */
776 /* */
777 /* Return Val : */
778 /* VARCHAR2 - Operator Lookup code from CSD_RULE_OPERATORS */
779 /* */
780 /*--------------------------------------------------------------------*/
781 FUNCTION GET_SQL_OPERATOR (
782 p_operator IN VARCHAR2
783 ) RETURN VARCHAR2;
784
785 END CSD_RULES_ENGINE_PVT;