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