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