1 PACKAGE IEX_SCORE_NEW_PVT AUTHID CURRENT_USER AS
2 /* $Header: iexvscfs.pls 120.13 2011/11/08 10:09:45 pnaveenk ship $ */
3
4 type Scores_tbl is table of number
5 index by binary_integer;
6
7 -- this will be passed back by the get_components procedure
8 TYPE SCORE_ENG_COMP_REC IS RECORD(
9 SCORE_ID NUMBER ,
10 SCORE_COMPONENT_ID NUMBER ,
11 SCORE_COMP_WEIGHT NUMBER ,
12 SCORE_COMP_VALUE VARCHAR2(2000) ,
13 FUNCTION_FLAG VARCHAR2(1));
14
15 -- this will be used for new getScoreHistory function
16 Type SCORE_HISTORY_REC IS RECORD
17 (SCORE_HISTORY_ID NUMBER,
18 SCORE_ID NUMBER,
19 SCORE_VALUE NUMBER,
20 CREATION_DATE DATE);
21
22 TYPE SCORE_ENG_COMP_TBL IS TABLE OF SCORE_ENG_COMP_REC INDEX BY binary_integer;
23 TYPE SCORE_HISTORY_TBL IS TABLE OF SCORE_HISTORY_REC INDEX BY binary_integer;
24
25 type SCORE_OBJECTS_TBL is table of number index by binary_integer;
26 type NEW_SCORES_TBL is table of number index by binary_integer;
27
28 /*
29 || Overview: validates any given objectID/Object_type pair
30 ||
31 || Parameter: Object_ID PK of object you wish to score
32 || Object_Type Type of Object you wish to score
33 || Alternatively if you wish to score another TYPE of object not listed pass the following as well:
34 || p_col_name name of colum you wish to select on
35 || p_table_name name of table to select from
36 ||
37 || Return value: True =OK; Falso=Error
38 ||
39 || Source Tables: IEX_DELINQUENCIES_ALL, IEX_CASES_B_ALL, HZ_PARTIES, AR_PAYMENT_SCHEDULES
40 ||
41 || Target Tables:
42 ||
43 || Creation date: 01/14/02 3:25:PM
44 ||
45 || Major Modifications: when who what
46 || 01/14/02 raverma created
47 */
48 function validateObjectID (p_object_id in number,
49 p_object_type in varchar2,
50 p_col_name in varchar2 default null,
51 p_table_name in varchar2 default null) return BOOLEAN;
52
53 /*
54 || Overview: compares whether the score engine being used for this object is of valid type
55 ||
56 || Parameter: p_score_id => scoring engine; p_object_type => type of object you wish to score
57 ||
58 || Return value: true=OK; FALSE=error
59 ||
60 || Source Tables: IEX_SCORES
61 ||
62 || Target Tables: NA
63 ||
64 || Creation date: 01/14/02 4:47:PM
65 ||
66 || Major Modifications: when who what
67 || 01/14/02 raverma created
68 */
69 function checkObject_Compatibility(p_score_id in number,
70 p_object_type in varchar2) return BOOLEAN;
71
72
73 /*
74 || Overview: Validate Score_Engine
75 ||
76 || Parameter:
77 ||
78 || Source Tables:
79 ||
80 || Target Tables:
81 ||
82 || Creation date: 01/14/02 3:08:PM
83 ||
84 || Major Modifications: when who what
85 || 01/14/02 raverma created
86 */
87 PROCEDURE Validate_Score_Engine(p_score_id in number);
88
89
90 /*
91 * clchang added this new procedure 10/18/04 for 11.5.11.
92 * this procedure will get the score_range_low, score_range_high,
93 * out_of_range_rule for a given score engine, and update the
94 * global variables: G_MIN_SCORE, G_MAX_SCORE, G_RULE.
95 *
96 * Parameter: P_SCORE_ID Scoring_Engine
97 * Major Modifications:
98 * when who what
99 * 10/18/04 clchang created
100 ******/
101 PROCEDURE getScoreRange(P_SCORE_ID IN NUMBER);
102
103
104
105 /*
106 || this procedure will get all components for a given score engine
107 || and return them as a tbl
108 ||
109 || Parameter: P_SCORE_ID Scoring_Engine
110 || X_SCORE_COMP_TBL = table of components attached to the Scoring engine
111 ||
112 || Return value: select statement for the Universe
113 ||
114 || Source Tables: IEX_SCORES, IEX_SCORE_COMPONENTS, IEX_SCORE_COMP_TYPES_B/TL
115 ||
116 || Target Tables: none
117 ||
118 || Creation date: 01/14/02 1:55:PM
119 ||
120 || Major Modifications: when who what
121 || 01/14/02 raverma created
122 || 03/12/02 raverma added function_flag to return tbl
123 */
124 PROCEDURE getComponents(P_SCORE_ID IN NUMBER,
125 X_SCORE_COMP_TBL OUT NOCOPY IEX_SCORE_NEW_PVT.SCORE_ENG_COMP_TBL);
126
127
128 /*
129 || Overview: This is the "main" scoring function. It will score any objects using the
130 || table of components passed to. The assumption is that any validation has been done already AND
131 || the table of components passed here is appropriate for the universe of IDS
132 ||
133 || Parameter: p_score_comp_tbl => components to use for scoring execution
134 || t_object_ids => universe of object_ids you wish to score
135 || (this universe MUST be valid for the components definition)
136 || x_scores_tbl => table of scores for the IDs passed
137 ||
138 || Source Tables: iex_score_comp_det
139 ||
140 || Target Tables: NA
141 ||
142 || Creation date: 01/14/02 5:27:PM
143 ||
144 || Major Modifications: when who what
145 ||
146 */
147 procedure getScores(p_score_comp_tbl IN IEX_SCORE_NEW_PVT.SCORE_ENG_COMP_TBL,
148 t_object_ids IN IEX_FILTER_PUB.UNIVERSE_IDS,
149 x_scores_tbl OUT NOCOPY IEX_SCORE_NEW_PVT.SCORES_TBL);
150
151 /*
152 || Overview: score all objects for a given scoring engine
153 ||
154 || Parameter: p_score_id => scoring engine ID
155 ||
156 || Source Tables: IEX_SCORES, IEX_SCORE_COMPONENTS_VL, IEX_SCORE_COMP_TYPES, IEX_SCORE_COMP_DET,
157 || IEX_OBJECT_FILTERS
158 ||
159 || Target Tables:
160 ||
161 || Creation date: 01/22/02 3:14:PM
162 ||
163 || Major Modifications: when who what
164 || 01/22/02 raverma created
165 */
166 procedure scoreObjects(p_api_version IN NUMBER := 1.0,
167 p_init_msg_list IN VARCHAR2 ,
168 p_commit IN VARCHAR2 ,
169 P_SCORE_ID IN NUMBER,
170 p_unv_obj_id in varchar2 default null,--Added for Bug 8933776 17-Dec-2009 barathsr
171 p_limit_rows_val in number default null,--Added for Bug 8933776 17-Dec-2009 barathsr
172 x_return_status OUT NOCOPY VARCHAR2,
173 x_msg_count OUT NOCOPY NUMBER,
174 x_msg_data OUT NOCOPY VARCHAR2);
175
176 /*
177 || Overview: score a single object given it's ID, it's Type, and it's Scoring Engine
178 ||
179 || Parameter: p_score_id => scoring engine ID
180 ||
181 || Source Tables: IEX_SCORES, IEX_SCORE_COMPONENTS_VL, IEX_SCORE_COMP_TYPES, IEX_SCORE_COMP_DET,
182 || IEX_OBJECT_FILTERS
183 ||
184 || Target Tables: will return a -1 in case of error
185 ||
186 || Creation date: 01/22/02 3:14:PM
187 ||
188 || Major Modifications: when who what
189 || 01/22/02 raverma created
190 */
191 function scoreObject(p_commit IN VARCHAR2 ,
192 P_OBJECT_ID IN NUMBER,
193 P_OBJECT_TYPE IN VARCHAR2,
194 P_SCORE_ID IN NUMBER) RETURN NUMBER;
195
196 /* this will be called by the concurrent program to score customers
197 */
198 Procedure Score_Concur(ERRBUF OUT NOCOPY VARCHAR2,
199 RETCODE OUT NOCOPY VARCHAR2,
200 P_ORG_ID IN NUMBER default null, --Added for MOAC
201 P_SCORE_ID1 IN NUMBER,
202 P_Score_ID2 IN NUMBER default null,
203 P_Score_ID3 IN NUMBER default null,
204 P_Score_ID4 IN NUMBER default null,
205 P_Score_ID5 IN NUMBER default null,
206 p_show_output in varchar2 default null, --Added for Bug 8933776 30-Nov-2009 barathsr
207 p_object_id in varchar2 default null,--Added for Bug 8933776 17-Dec-2009 barathsr
208 p_limit_rows in number default null);--Added for Bug 8933776 17-Dec-2009 barathsr
209
210
211 /*
212 || Overview: delete rows from IEX_SCORE_HISTORIES to improve performance
213 ||
214 || Parameter: p_score_object_code => score_object_code to erase
215 || p_from_date => remove from this date
216 || p_to_Date => remove up to this date
217 || p_request_id => remove this request
218 || p_save_last_run => save the last run of the object type
219 ||
220 || Source Tables:
221 ||
222 || Target Tables: IEX_SCORE_HISTORIES
223 ||
224 || Creation date: 01/28/03 3:14:PM
225 ||
226 || Major Modifications: when who what
227 || 01/28/03 raverma created
228 */
229 Procedure eraseScores(ERRBUF OUT NOCOPY VARCHAR2,
230 RETCODE OUT NOCOPY VARCHAR2,
231 P_ORG_ID IN NUMBER,
232 P_TRUNCATE IN VARCHAR2, -- fix a bug 5765878 to truncate table to perform better by Ehuh 02.19.2007
233 P_SCORE_OBJECT_ID IN NUMBER default null,
234 P_SCORE_OBJECT_CODE IN VARCHAR2 default null,
235 P_FROM_DATE IN varchar2 default null,
236 P_TO_DATE IN varchar2 default null,
237 P_REQUEST_ID IN NUMBER default null,
238 P_SAVE_LAST_RUN IN VARCHAR2 ,
239 P_BATCH_SIZE IN NUMBER);
240
241 /*
242 || Overview: Returns an array of score values for a given objectID/Type
243 ||
244 || Parameter: p_score_object_id object scored in IEX_SCORE_HISTORIES required
245 || p_score_object_code object_code in IEX_SCORE_HISTORIES required
246 || p_begin_Date begin date restriction optional
247 || p_end_date end date restriction optional
248 || p_scoreID scoreEngineID used to score object optional
249 ||
250 || Return value: SCORE_HISTORY_ID -> PK to IEX_SCORE_HISTORIES
251 || SCORE_ID -> scoreEngine used to calculate score
252 || SCORE_VALUE -> score of object
253 || CREATION_DATE -> date object was scored
254 ||
255 || Source Tables: IEX_SCORE_HISTORIES
256 ||
257 || Target Tables: NA
258 ||
259 || Creation date: 04/22/2003 4:03PM
260 ||
261 || Major Modifications: when who what
262 || 04/22/2003 4:03PM raverma created
263 */
264 function getScoreHistory (p_score_object_id IN NUMBER,
265 p_score_object_code IN VARCHAR2,
266 p_from_date IN DATE default null,
267 p_to_date IN DATE default null,
268 p_score_id IN NUMBER default null) return IEX_SCORE_NEW_PVT.SCORE_HISTORY_TBL;
269
270
271 --
272 --- Begin - Andre Araujo - 11/02/2004 - New storage mode, this one respects the commit size - TAR 4040621.994
273 --
274 /*
275 || Overview: Stores the score history given a table of records
276 ||
277 || Parameter:
278 || p_scoreID scoreEngineID used to score object optional
279 ||
280 || Return value:
281 ||
282 || Source Tables: None
283 ||
284 || Target Tables: IEX_SCORE_HISTORIES
285 ||
286 || Creation date: 11/02/2004
287 ||
288 || Major Modifications: when who what
289 ||
290 */
291 procedure storeScoreHistory ( p_score_id IN NUMBER default null,
292 p_objects_tbl IN IEX_SCORE_NEW_PVT.SCORE_OBJECTS_TBL,
293 p_scores_tbl IN IEX_SCORE_NEW_PVT.NEW_SCORES_TBL) ;
294
295 /*
296 || Overview: Stores the score history given a table of records
297 ||
298 || Parameter:
299 || p_scoreID scoreEngineID used to score object optional
300 ||
301 || Return value:
302 ||
303 || Source Tables: None
304 ||
305 || Target Tables: IEX_SCORE_HISTORIES
306 ||
307 || Creation date: 11/02/2004
308 ||
309 || Major Modifications: when who what
310 ||
311 */
312 procedure storeDelBuffers ( p_score_id IN NUMBER default null,
313 p_objects_tbl IN IEX_SCORE_NEW_PVT.SCORE_OBJECTS_TBL,
314 p_scores_tbl IN IEX_SCORE_NEW_PVT.NEW_SCORES_TBL,
315 p_bridge IN NUMBER default null) ;
316
317 /*
318 || Overview: Scores 1 item and returns the value
319 ||
320 || Parameter:
321 || p_scoreID scoreEngineID used to score object optional
322 ||
323 || Return value:
324 ||
325 || Source Tables: None
326 ||
327 || Target Tables: None
328 ||
329 || Creation date: 11/03/2004
330 ||
331 || Major Modifications: when who what
332 ||
333 */
334 function get1Score ( p_score_comp_tbl IN IEX_SCORE_NEW_PVT.SCORE_ENG_COMP_TBL, p_object_id IN NUMBER ) return NUMBER;
335
336 function getComponentScore(p_scorecomp_id in number,p_object_id in number) return varchar; --Added for Bug 10354240 27-Sep-2010 barathsr
337
338 IEX_UNIVERSE_SIZE_ZERO_ERROR EXCEPTION;
339
340
341
342 END IEX_SCORE_NEW_PVT;
343
[Home] [Help]