1 PACKAGE CSC_Profile_Engine_PKG AS
2 /* $Header: csccpeps.pls 120.7 2006/07/10 06:55:15 vshastry noship $ */
3
4 /* Added G_PKG_NAME for JIT enhancement -- Bug 4535407 */
5 G_PKG_NAME VARCHAR2(100) := 'CSC_PROFILE_ENGINE_PKG';
6
7 --
8 -- to be used if block id is passed
9 --
10 TYPE r_block_rectype IS RECORD
11 ( p_block_id NUMBER);
12
13 TYPE t_BlockTable IS TABLE of r_block_rectype
14 INDEX BY BINARY_INTEGER;
15
16 Block_TBL t_BlockTable;
17
18 --
19 -- to be used if check id is passed
20 --
21 TYPE r_check_rectype IS RECORD
22 ( p_check_id NUMBER);
23
24 TYPE t_CheckTable IS TABLE of r_check_rectype
25 INDEX BY BINARY_INTEGER;
26
27 Check_TBL t_CheckTable;
28
29 -- p_old_check_id NUMBER;
30 -- p_old_check_cid NUMBER;
31 -- p_old_block_id NUMBER;
32 -- p_old_block_cid NUMBER;
33
34
35 --
36 -- Global Tables
37 --
38 -- tables for updating party results table
39 --
40 TYPE UP_Block_Id_Tab Is Table of NUMBER Index By Binary_Integer;
41 TYPE UP_Check_Id_Tab Is Table of NUMBER Index By Binary_Integer;
42 TYPE UP_Party_Id_Tab Is Table of NUMBER Index By Binary_Integer;
43 TYPE UP_Account_Id_Tab Is Table of NUMBER Index By Binary_Integer;
44 TYPE UP_Value_Tab Is Table of Varchar2(240) Index By Binary_Integer;
45 TYPE UP_Currency_Tab is Table of Varchar2(15) Index By Binary_Integer;
46 TYPE UP_Grade_Tab is Table of Varchar2(9) Index By Binary_Integer;
47 TYPE UP_Rating_Tab is Table of Varchar2(240) Index By Binary_Integer;
48 TYPE UP_Color_Tab is Table of Varchar2(240) Index By Binary_Integer;
49 TYPE UP_Results_Tab Is Table of Varchar2(3) Index By Binary_Integer;
50
51 UP_Block_Id UP_Block_Id_Tab;
52 UP_Check_Id UP_Check_Id_Tab;
53 UP_Party_Id UP_Party_Id_Tab;
54 UP_Account_Id UP_Account_Id_Tab;
55 UP_Value UP_Value_Tab;
56 UP_Currency UP_Currency_Tab;
57 UP_Grade UP_Grade_Tab;
58 UP_Rating_code UP_Rating_Tab;
59 UP_Color_code UP_Color_Tab;
60 UP_Results UP_Results_Tab;
61
62 --
63 -- tables for inserting party results table
64 --
65 TYPE IP_Block_Id_Tab Is Table of NUMBER Index By Binary_Integer;
66 TYPE IP_Check_Id_Tab Is Table of NUMBER Index By Binary_Integer;
67 TYPE IP_Party_Id_Tab Is Table of NUMBER Index By Binary_Integer;
68 TYPE IP_Account_Id_Tab Is Table of NUMBER Index By Binary_Integer;
69 TYPE IP_Value_Tab Is Table of Varchar2(240) Index By Binary_Integer;
70 TYPE IP_Currency_Tab is Table of Varchar2(15) Index By Binary_Integer;
71 TYPE IP_Grade_Tab is Table of Varchar2(9) Index By Binary_Integer;
72 TYPE IP_Rating_Tab is Table of Varchar2(240) Index By Binary_Integer;
73 TYPE IP_Color_Tab is Table of Varchar2(240) Index By Binary_Integer;
74 TYPE IP_Results_Tab Is Table of Varchar2(3) Index By Binary_Integer;
75
76 IP_Block_Id IP_Block_Id_Tab;
77 IP_Check_Id IP_Check_Id_Tab;
78 IP_Party_Id IP_Party_Id_Tab;
79 IP_Account_Id IP_Account_Id_Tab;
80 IP_Value IP_Value_Tab;
81 IP_Currency IP_Currency_Tab;
82 IP_Grade IP_Grade_Tab;
83 IP_Rating_code IP_Rating_Tab;
84 IP_Color_code IP_Color_Tab;
85 IP_Results IP_Results_Tab;
86
87 --
88 -- tables for updating party account results table
89 --
90 TYPE UA_Block_Id_Tab Is Table of NUMBER Index By Binary_Integer;
91 TYPE UA_Check_Id_Tab Is Table of NUMBER Index By Binary_Integer;
92 TYPE UA_Party_Id_Tab Is Table of NUMBER Index By Binary_Integer;
93 TYPE UA_Account_Id_Tab Is Table of NUMBER Index By Binary_Integer;
94 TYPE UA_Value_Tab Is Table of Varchar2(240) Index By Binary_Integer;
95 TYPE UA_Currency_Tab is Table of Varchar2(15) Index By Binary_Integer;
96 TYPE UA_Grade_Tab is Table of Varchar2(9) Index By Binary_Integer;
97 TYPE UA_Rating_Tab is Table of Varchar2(240) Index By Binary_Integer;
98 TYPE UA_Color_Tab is Table of Varchar2(240) Index By Binary_Integer;
99 TYPE UA_Results_Tab Is Table of Varchar2(3) Index By Binary_Integer;
100
101 UA_Block_Id UA_Block_Id_Tab;
102 UA_Check_Id UA_Check_Id_Tab;
103 UA_Party_Id UA_Party_Id_Tab;
104 UA_Account_Id UA_Account_Id_Tab;
105 UA_Value UA_Value_Tab;
106 UA_Currency UA_Currency_Tab;
107 UA_Grade UA_Grade_Tab;
108 UA_Rating_code UA_Rating_Tab;
109 UA_Color_code UA_Color_Tab;
110 UA_Results UA_Results_Tab;
111
112 --
113 -- tables for inserting party account results table
114 --
115 TYPE IA_Block_Id_Tab Is Table of NUMBER Index By Binary_Integer;
116 TYPE IA_Check_Id_Tab Is Table of NUMBER Index By Binary_Integer;
117 TYPE IA_Party_Id_Tab Is Table of NUMBER Index By Binary_Integer;
118 TYPE IA_Account_Id_Tab Is Table of NUMBER Index By Binary_Integer;
119 TYPE IA_Value_Tab Is Table of Varchar2(240) Index By Binary_Integer;
120 TYPE IA_Currency_Tab is Table of Varchar2(15) Index By Binary_Integer;
121 TYPE IA_Grade_Tab is Table of Varchar2(9) Index By Binary_Integer;
122 TYPE IA_Rating_Tab Is Table of Varchar2(240) Index By Binary_Integer;
123 TYPE IA_Color_Tab Is Table of Varchar2(240) Index By Binary_Integer;
124 TYPE IA_Results_Tab Is Table of Varchar2(3) Index By Binary_Integer;
125
126 IA_Block_Id IA_Block_Id_Tab;
127 IA_Check_Id IA_Check_Id_Tab;
128 IA_Party_Id IA_Party_Id_Tab;
129 IA_Account_Id IA_Account_Id_Tab;
130 IA_Value IA_Value_Tab;
131 IA_Currency IA_Currency_Tab;
132 IA_Grade IA_Grade_Tab;
133 IA_Rating_Code IA_Rating_Tab;
134 IA_Color_Code IA_Color_Tab;
135 IA_Results IA_Results_Tab;
136
137 --
138 -- Tables for Relationship Plan Engine
139 --
140
141 plan_id_plan_table csc_plan_assignment_pkg.csc_plan_id_tbl_type;
142 party_id_plan_table csc_plan_assignment_pkg.csc_party_id_tbl_type;
143 account_id_plan_table csc_plan_assignment_pkg.csc_cust_id_tbl_type;
144 --org_id_plan_table csc_plan_assignment_pkg.csc_cust_org_tbl_type;
145 check_id_plan_table csc_plan_assignment_pkg.csc_check_id_tbl_type;
146
147 -- For Relationship party profile checks
148 g_dashboard_for_contact VARCHAR2(1) := NULL;
149
150 -- R12 Employee HelpDesk Modifications
151 -- Flag For Employee level profile checks
152 g_dashboard_for_employee VARCHAR2(1) := NULL;
153
154 -- for profiles with no batch sql
155 g_check_no_batch VARCHAR2(1) := NULL;
156
157 Type checks_cur_var IS REF CURSOR;
158
159 TYPE r_blk_rectype IS RECORD
160 ( block_id NUMBER,
161 sql_stmnt VARCHAR2(4000),
162 currency VARCHAR2(30));
163
164 TYPE blocks_curtype IS
165 REF CURSOR RETURN r_blk_rectype;
166
167
168 TYPE r_chk_rectype IS RECORD
169 ( check_id NUMBER,
170 select_type VARCHAR2(3),
171 select_block_id NUMBER,
172 data_type VARCHAR2(30),
173 format_mask VARCHAR2(30),
174 check_upper_lower_flag VARCHAR2(3),
175 threshold_grade VARCHAR2(9),
176 check_level VARCHAR2(10)
177 );
178
179
180 TYPE checks_curtype IS
181 REF CURSOR RETURN r_chk_rectype;
182
183 --
184 -- Insert or Update Records into Csc_Prof_Block_Results Table
185 -- IN
186 -- p_count - the number of records to be inserted or updated
187 -- p_for_insert - flag to check if for insert or update.
188 -- p_for_party - flag to check if the insert or update is for party or account
189 --
190 PROCEDURE Insert_Update_Block_Results
191 ( p_count IN Number,
192 p_for_insert IN Varchar2,
193 p_for_party IN Varchar2);
194
195
196 --
197 -- Insert or Update Records into Csc_Prof_Check_Results Table
198 -- IN
199 -- p_count - the number of records to be inserted or updated
200 -- p_for_insert - flag to check if for insert or update.
201 -- p_for_party - flag to check if the insert or update is for party or account
202 --
203
204 PROCEDURE Insert_Update_Check_Results
205 ( p_count IN Number,
206 p_for_insert IN Varchar2,
207 p_for_party IN Varchar2);
208
209 /*******************************************
210 UPDATE_JIT_STATUS
211 Added this procedure for JIT enhancement -- Bug 4535407
212 Updates the columns jit_status, jit_err_code in CCT_MEDIA_ITEMS.
213 Called from CSC_PROF_JIT
214 ********************************************/
215
216 PROCEDURE update_jit_status
217 ( p_status VARCHAR2 DEFAULT NULL,
218 p_err_code NUMBER DEFAULT NULL,
219 p_media_item_id NUMBER );
220
221
222 /*******************************************
223 CSC_PROF_JIT
224 Added this procedure for JIT enhancement -- Bug 4535407
225 Called from OTM java code
226 Calls profile engine for the party_id passed from OTM.
227 OTM passes the key-value pair in a VARRAY (cct_keyvalue_varr)
228 ********************************************/
229
230 PROCEDURE csc_prof_jit
231 ( p_key_value_varr IN cct_keyvalue_varr,
232 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
233 p_critical_flag IN VARCHAR2 DEFAULT NULL,
234 x_return_status OUT NOCOPY VARCHAR2,
235 x_msg_count OUT NOCOPY NUMBER,
236 x_msg_data OUT NOCOPY VARCHAR2 );
237
238
239 /*******************************************
240 GET_PROFILE_VALUE_RESP
241 Added this procedure for JIT enhancement -- Bug 4535407
242 Returns Profile Option value at the responsibility level.
243 This function has been added as API fnd_profile.value_specific is
244 not defined at form level FND_PROFILE package (pld)
245 ********************************************/
246
247 FUNCTION get_profile_value_resp
248 ( p_profile_name VARCHAR2,
249 p_resp_id NUMBER,
250 p_resp_appl_id NUMBER,
251 p_user_id NUMBER
252 )
253 RETURN VARCHAR2 ;
254
255 --
256 -- Build_Rule
257 -- Construct the rule as a SQL statement for an indicator check
258 -- IN
259 -- check_id - profile check identifier
260 -- OUT
261 -- rule - sql statement that returns 0 or 1 row (0 for false, 1 for true)
262 --
263 PROCEDURE Build_Rule
264 ( acct_flag IN VARCHAR2,
265 chk_id IN NUMBER,
266 chk_level IN VARCHAR2,
267 rule OUT NOCOPY VARCHAR2);
268
269 --
270 -- Set_Context
271 -- Set procedure context (for stack trace).
272 -- IN
273 -- proc_name - procedure/function name
274 -- arg1 - first IN argument
275 -- argn - n'th IN argument
276 --
277 PROCEDURE Set_Context
278 ( proc_name IN VARCHAR2,
279 arg1 IN VARCHAR2 DEFAULT '*none*',
280 arg2 IN VARCHAR2 DEFAULT '*none*',
281 arg3 IN VARCHAR2 DEFAULT '*none*',
282 arg4 IN VARCHAR2 DEFAULT '*none*',
283 arg5 IN VARCHAR2 DEFAULT '*none*' );
284
285 --
286 -- to run engine as a concurrent program
287 --
288 PROCEDURE Run_Engine (p_errbuf OUT NOCOPY VARCHAR2,
289 p_retcode OUT NOCOPY NUMBER,
290 p_party_id IN NUMBER,
291 p_acct_id IN NUMBER,
292 p_group_id IN NUMBER );
293
294
295 --
296 -- Bug 1942032 to run engine as a concurrent program - overloaded procedure when
297 -- Account Id is removed from conc. program parameters.
298 --
299 PROCEDURE Run_Engine (p_errbuf OUT NOCOPY VARCHAR2,
300 p_retcode OUT NOCOPY NUMBER,
301 p_party_id IN NUMBER,
302 p_group_id IN NUMBER );
303
304 /* added the overloaded procedure for JIT enhancement -- Bug 4535407 */
305 PROCEDURE Run_Engine_jit (p_party_id IN NUMBER,
306 p_acct_id IN NUMBER,
307 p_group_id IN NUMBER,
308 p_critical_flag IN VARCHAR2,
309 p_party_type IN VARCHAR2 DEFAULT 'CUSTOMER' );
310
311
312 /* R12 Employee HelpDesk Modifications -overloaded procedure */
313 PROCEDURE Run_Engine_All (p_errbuf OUT NOCOPY VARCHAR2,
314 p_retcode OUT NOCOPY NUMBER,
315 p_party_type IN VARCHAR2,
316 p_party_id IN NUMBER,
317 p_group_id IN NUMBER );
318
319
320 --
321 -- Modified for batch sql changes
322 -- Evaluate_Checks1_Var
323 -- Loop through all checks and evaluate the results
324 -- for each customer and account for Variable type
325 -- if check_id is null, party_id is null, account_id is null
326 -- and block_id is null
327 --
328 PROCEDURE Evaluate_Checks1_Var;
329
330 --
331 -- Evaluate_Checks1_Rule
332 -- Added for batch sql changes
333 -- Loop through all checks and evaluate the results
334 -- for each customer and account for Rule type
335 -- if check_id is null, party_id is null, account_id is null
336 -- and block_id is null
337
338 PROCEDURE Evaluate_Checks1_Rule
339 ( errbuf OUT NOCOPY VARCHAR2,
340 retcode OUT NOCOPY NUMBER );
341
342 -- Evaluate_Checks1_No_Batch
343 -- This procedure evaluates the checks
344 -- for which the batch sql statement is NULL
345 --
346 PROCEDURE Evaluate_Checks1_No_Batch
347 ( errbuf OUT NOCOPY VARCHAR2,
348 retcode OUT NOCOPY NUMBER ) ;
349 --
350 --
351 -- Evaluate_Checks2
352 -- Loop through all checks and evaluate the results
353 -- for each customer and account.
354 -- if check_id is null, party_id is not null or account_id is not null
355 -- and block_id is null
356 --
357 PROCEDURE Evaluate_Checks2
358 ( p_party_id IN NUMBER,
359 p_acct_id IN NUMBER,
360 p_group_id IN NUMBER,
361 p_critical_flag IN VARCHAR2 DEFAULT 'N', /* added for JIT enhancement */
362 errbuf OUT NOCOPY VARCHAR2,
363 retcode OUT NOCOPY NUMBER );
364
365 --
366 -- Evaluate_Checks3
367 -- Loop through all checks and evaluate the results
368 -- for each customer and account.
369 -- if check_id is null, party_id is not null or account_id is not null
370 -- and block_id is null
371 --
372 PROCEDURE Evaluate_Checks3
373 ( p_party_id IN NUMBER,
374 p_acct_id IN NUMBER,
375 p_group_id IN NUMBER,
376 errbuf OUT NOCOPY VARCHAR2,
377 retcode OUT NOCOPY NUMBER );
378
379 --
380 -- Evaluate_Checks4_Var
381 -- Modified for batch sql changes
382 -- Loop through Checks in this group and evaluate the results
383 -- for all Parties and Accounts.
384 -- if Party_id is null, Account_id is null and Group_id is not null
385 --
386 PROCEDURE Evaluate_Checks4_Var
387 (p_group_id IN NUMBER);
388
389 --
390 -- Evaluate_Checks4_No_Batch
391 -- This procedure evaluates the checks
392 -- for which the batch sql statement is NULL
393 --
394 PROCEDURE Evaluate_Checks4_No_Batch
395 (errbuf OUT NOCOPY VARCHAR2,
396 retcode OUT NOCOPY NUMBER ,
397 p_group_id IN NUMBER);
398
399 --
400 -- Evaluate_Checks4_Rule -- Added for 1850508
401 -- Loop through Checks in this group and evaluate the results
402 -- for all Parties and Accounts.
403 -- if Party_id is null, Account_id is null and Group_id is not null
404 --
405 PROCEDURE Evaluate_Checks4_Rule
406 ( errbuf OUT NOCOPY VARCHAR2,
407 retcode OUT NOCOPY NUMBER ,
408 p_group_id IN NUMBER);
409
410
411 --
412 -- Evaluate_One_Check
413 -- Evaluate the given profile check and store the result in the
414 -- CS_PROF_CHECK_RESULTS table. Also store the grade if ranges are
415 -- specified.
416 -- IN
417 -- chk_id - profile check identifier
418 -- cust_id - customer identifier for which check is evaluated
419 -- acct_id - customer's account identifier
420 -- sel_type - 'B' for block; 'T' for true or false ("indicator" check)
421 -- sel_blk_id - building block identifier (required if select type is
422 -- block)
423 -- data_type - data type of check result (used for applying format mask)
424 -- fmt_mask - format mask for check result (ignored if data type is
425 -- char or currency code is present)
426 -- rule - sql statement that returns 0 or 1 row for an indicator check
427 -- P_CID - Cursor passed from calling routine to avoid re-parsing the same sql statement (1850508)
428 PROCEDURE Evaluate_One_Check
429 ( p_truncate_flag IN VARCHAR2,
430 p_chk_id IN NUMBER,
431 p_party_id IN NUMBER,
432 p_acct_id IN NUMBER DEFAULT NULL,
433 p_check_level IN VARCHAR2 DEFAULT NULL,
434 p_sel_type IN VARCHAR2,
435 p_sel_blk_id IN NUMBER DEFAULT NULL,
436 p_data_type IN VARCHAR2 DEFAULT NULL,
437 p_fmt_mask IN VARCHAR2 DEFAULT NULL,
438 p_chk_u_l_flag IN VARCHAR2 DEFAULT NULL,
439 p_thd_grade IN VARCHAR2 DEFAULT NULL,
440 p_rule IN VARCHAR2 DEFAULT NULL,
441 p_blk_id IN NUMBER DEFAULT NULL,
442 p_sql_stmt IN VARCHAR2 DEFAULT NULL,
443 p_curr_code IN VARCHAR2 DEFAULT NULL,
444 p_up_total IN OUT NOCOPY NUMBER ,
445 p_ip_total IN OUT NOCOPY NUMBER ,
446 p_ua_total IN OUT NOCOPY NUMBER ,
447 p_ia_total IN OUT NOCOPY NUMBER ,
448 p_cid IN NUMBER);
449
450 --
451 -- Evaluate_Blocks1
452 -- Loop through all the effective building blocks and evaluate the results
453 -- for each customer.
454 -- if block_id is null, party_id is null and account_id is null
455 --
456 PROCEDURE Evaluate_Blocks1
457 (
458 p_up_total IN OUT NOCOPY NUMBER ,
459 p_ip_total IN OUT NOCOPY NUMBER ,
460 p_ua_total IN OUT NOCOPY NUMBER ,
461 p_ia_total IN OUT NOCOPY NUMBER );
462
463
464 --
465 -- Evaluate_Blocks1_No_Batch
466 -- This procedure evaluates the blocks
467 -- for which the batch sql statement is NULL
468 PROCEDURE Evaluate_Blocks1_No_Batch
469 (
470 p_up_total IN OUT NOCOPY NUMBER ,
471 p_ip_total IN OUT NOCOPY NUMBER ,
472 p_ua_total IN OUT NOCOPY NUMBER ,
473 p_ia_total IN OUT NOCOPY NUMBER );
474
475 --
476 -- Evaluate_Blocks2
477 -- Loop through all the effective building blocks and evaluate the results
478 -- for each customer.
479 -- if block_id is not null, party_id is not null or account_id is not null
480 --
481 PROCEDURE Evaluate_Blocks2
482 ( p_block_id IN NUMBER,
483 p_party_id IN NUMBER,
484 p_acct_id IN NUMBER,
485 p_up_total IN OUT NOCOPY NUMBER ,
486 p_ip_total IN OUT NOCOPY NUMBER ,
487 p_ua_total IN OUT NOCOPY NUMBER ,
488 p_ia_total IN OUT NOCOPY NUMBER );
489
490
491 --
492 -- Evaluate_Blocks4 -- Added for 1850508
493 -- Calculate for All Parties, Accounts * For all checks present in the group
494 -- When Group Id is given but not party_id or Account_id
495 PROCEDURE Evaluate_Blocks4
496 ( p_up_total IN OUT NOCOPY NUMBER ,
497 p_ip_total IN OUT NOCOPY NUMBER ,
498 p_ua_total IN OUT NOCOPY NUMBER ,
499 p_ia_total IN OUT NOCOPY NUMBER ,
500 p_group_id IN NUMBER);
501
502
503 --
504 -- Evaluate_Blocks4_No_Batch
505 -- This procedure evaluates the blocks
506 -- for which the batch sql statement is NULL
507 PROCEDURE Evaluate_Blocks4_No_Batch
508 ( p_up_total IN OUT NOCOPY NUMBER ,
509 p_ip_total IN OUT NOCOPY NUMBER ,
510 p_ua_total IN OUT NOCOPY NUMBER ,
511 p_ia_total IN OUT NOCOPY NUMBER ,
512 p_group_id IN NUMBER);
513
514 --
515 -- Evaluate_One_Block
516 -- Execute dynamic SQL to evaluate the given building block and store the
517 -- result in the CS_PROF_BLOCK_RESULTS table.
518 -- IN
519 -- blk_id - profile check building block identifier
520 -- party_id - customer identifier for which building block is evaluated
521 -- acct_id - account id
522 -- sql_stmt - sql statement to execute dynamically
523 -- P_CID - Cursor passed from calling routine to avoid re-parsing the same sql statement (1850508)
524 PROCEDURE Evaluate_One_Block
525 ( p_truncate_flag IN VARCHAR2,
526 p_blk_id IN NUMBER,
527 p_party_id IN NUMBER,
528 p_acct_id IN NUMBER,
529 p_sql_stmt IN VARCHAR2,
530 p_curr_code IN VARCHAR2,
531 p_up_total IN OUT NOCOPY NUMBER ,
532 p_ip_total IN OUT NOCOPY NUMBER ,
533 p_ua_total IN OUT NOCOPY NUMBER ,
534 p_ia_total IN OUT NOCOPY NUMBER ,
535 p_cid IN NUMBER);
536
537 PROCEDURE Evaluate_Blocks5
538 ( p_block_id IN NUMBER,
539 p_party_id IN NUMBER,
540 p_up_total IN OUT NOCOPY NUMBER ,
541 p_ip_total IN OUT NOCOPY NUMBER ,
542 p_ua_total IN OUT NOCOPY NUMBER ,
543 p_ia_total IN OUT NOCOPY NUMBER );
544
545 PROCEDURE Evaluate_Blocks_Rel
546 ( p_up_total IN OUT NOCOPY NUMBER ,
547 p_ip_total IN OUT NOCOPY NUMBER ,
548 p_ua_total IN OUT NOCOPY NUMBER ,
549 p_ia_total IN OUT NOCOPY NUMBER,
550 p_group_id IN NUMBER DEFAULT NULL,
551 p_no_batch_sql IN VARCHAR2 DEFAULT NULL,
552 p_rule_only IN VARCHAR2 DEFAULT NULL);
553
554 PROCEDURE Evaluate_Checks_Rel
555 ( errbuf OUT NOCOPY VARCHAR2,
556 retcode OUT NOCOPY NUMBER ,
557 p_group_id IN NUMBER DEFAULT NULL,
558 p_no_batch_sql IN VARCHAR2 DEFAULT NULL,
559 p_rule_only IN VARCHAR2 DEFAULT NULL);
560
561
562 PROCEDURE Evaluate_Blocks_Emp
563 ( p_up_total IN OUT NOCOPY NUMBER ,
564 p_ip_total IN OUT NOCOPY NUMBER ,
565 p_ua_total IN OUT NOCOPY NUMBER ,
566 p_ia_total IN OUT NOCOPY NUMBER,
567 p_group_id IN NUMBER DEFAULT NULL,
568 p_no_batch_sql IN VARCHAR2 DEFAULT NULL,
569 p_rule_only IN VARCHAR2 DEFAULT NULL);
570
571 PROCEDURE Evaluate_Checks_Emp
572 ( errbuf OUT NOCOPY VARCHAR2,
573 retcode OUT NOCOPY NUMBER ,
574 p_group_id IN NUMBER DEFAULT NULL,
575 p_no_batch_sql IN VARCHAR2 DEFAULT NULL,
576 p_rule_only IN VARCHAR2 DEFAULT NULL);
577
578
579 /* added the below 3 functions and handle_exception for batch sql changes */
580 FUNCTION Calc_Threshold
581 ( p_val VARCHAR2,
582 p_low_val VARCHAR2,
583 p_high_val VARCHAR2,
584 p_data_type VARCHAR2,
585 p_chk_u_l_flag VARCHAR2
586 )
587 RETURN VARCHAR2;
588
589
590 FUNCTION Format_Mask
591 ( p_curr_code VARCHAR2,
592 p_data_type VARCHAR2,
593 p_val VARCHAR2,
594 p_fmt_mask VARCHAR2
595 )
596 RETURN VARCHAR2 ;
597
598 TYPE r_rating IS RECORD
599 ( rating_code VARCHAR2(240), grade VARCHAR2(240),
600 color_code VARCHAR2(240), low_value VARCHAR2(240), high_value VARCHAR2(240));
601
602 TYPE t_rating IS TABLE of r_rating
603 INDEX BY BINARY_INTEGER;
604
605 Rating_TBL t_rating;
606
607 g_check_id NUMBER;
608 g_party_id NUMBER;
609 g_account_id NUMBER;
610 g_value VARCHAR2(240);
611 g_color_code VARCHAR2(240);
612 g_rating VARCHAR2(240);
613 g_grade VARCHAR2(240);
614
615 FUNCTION rating_color
616 ( p_chk_id NUMBER,
617 p_party_id NUMBER,
618 p_account_id NUMBER,
619 p_val VARCHAR2,
620 p_data_type VARCHAR2,
621 p_column VARCHAR2,
622 p_count NUMBER
623 )
624 RETURN VARCHAR2;
625
626 PROCEDURE Handle_Exception;
627
628 END CSC_Profile_Engine_PKG;
[Home] [Help]