1 PACKAGE IEX_SCORE_NEW_PVT AS
2 /* $Header: iexvscfs.pls 120.8.12000000.2 2007/02/19 19:57:52 ehuh 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 x_return_status OUT NOCOPY VARCHAR2,
171 x_msg_count OUT NOCOPY NUMBER,
172 x_msg_data OUT NOCOPY VARCHAR2);
173
174 /*
175 || Overview: score a single object given it's ID, it's Type, and it's Scoring Engine
176 ||
177 || Parameter: p_score_id => scoring engine ID
178 ||
179 || Source Tables: IEX_SCORES, IEX_SCORE_COMPONENTS_VL, IEX_SCORE_COMP_TYPES, IEX_SCORE_COMP_DET,
180 || IEX_OBJECT_FILTERS
181 ||
182 || Target Tables: will return a -1 in case of error
183 ||
184 || Creation date: 01/22/02 3:14:PM
185 ||
186 || Major Modifications: when who what
187 || 01/22/02 raverma created
188 */
189 function scoreObject(p_commit IN VARCHAR2 ,
190 P_OBJECT_ID IN NUMBER,
191 P_OBJECT_TYPE IN VARCHAR2,
192 P_SCORE_ID IN NUMBER) RETURN NUMBER;
193
194 /* this will be called by the concurrent program to score customers
195 */
196 Procedure Score_Concur(ERRBUF OUT NOCOPY VARCHAR2,
197 RETCODE OUT NOCOPY VARCHAR2,
198 P_ORG_ID IN NUMBER default null, --Added for MOAC
199 P_SCORE_ID1 IN NUMBER,
200 P_Score_ID2 IN NUMBER default null,
201 P_Score_ID3 IN NUMBER default null,
202 P_Score_ID4 IN NUMBER default null,
203 P_Score_ID5 IN NUMBER default null);
204
205 /*
206 || Overview: delete rows from IEX_SCORE_HISTORIES to improve performance
207 ||
208 || Parameter: p_score_object_code => score_object_code to erase
209 || p_from_date => remove from this date
210 || p_to_Date => remove up to this date
211 || p_request_id => remove this request
212 || p_save_last_run => save the last run of the object type
213 ||
214 || Source Tables:
215 ||
216 || Target Tables: IEX_SCORE_HISTORIES
217 ||
218 || Creation date: 01/28/03 3:14:PM
219 ||
220 || Major Modifications: when who what
221 || 01/28/03 raverma created
222 */
223 Procedure eraseScores(ERRBUF OUT NOCOPY VARCHAR2,
224 RETCODE OUT NOCOPY VARCHAR2,
225 P_TRUNCATE IN VARCHAR2, -- fix a bug 5765878 to truncate table to perform better by Ehuh 02.19.2007
226 P_SCORE_OBJECT_ID IN NUMBER default null,
227 P_SCORE_OBJECT_CODE IN VARCHAR2 default null,
228 P_FROM_DATE IN varchar2 default null,
229 P_TO_DATE IN varchar2 default null,
230 P_REQUEST_ID IN NUMBER default null,
231 P_SAVE_LAST_RUN IN VARCHAR2 ,
232 P_BATCH_SIZE IN NUMBER);
233
234 /*
235 || Overview: Returns an array of score values for a given objectID/Type
236 ||
237 || Parameter: p_score_object_id object scored in IEX_SCORE_HISTORIES required
238 || p_score_object_code object_code in IEX_SCORE_HISTORIES required
239 || p_begin_Date begin date restriction optional
240 || p_end_date end date restriction optional
241 || p_scoreID scoreEngineID used to score object optional
242 ||
243 || Return value: SCORE_HISTORY_ID -> PK to IEX_SCORE_HISTORIES
244 || SCORE_ID -> scoreEngine used to calculate score
245 || SCORE_VALUE -> score of object
246 || CREATION_DATE -> date object was scored
247 ||
248 || Source Tables: IEX_SCORE_HISTORIES
249 ||
250 || Target Tables: NA
251 ||
252 || Creation date: 04/22/2003 4:03PM
253 ||
254 || Major Modifications: when who what
255 || 04/22/2003 4:03PM raverma created
256 */
257 function getScoreHistory (p_score_object_id IN NUMBER,
258 p_score_object_code IN VARCHAR2,
259 p_from_date IN DATE default null,
260 p_to_date IN DATE default null,
261 p_score_id IN NUMBER default null) return IEX_SCORE_NEW_PVT.SCORE_HISTORY_TBL;
262
263
264 --
265 --- Begin - Andre Araujo - 11/02/2004 - New storage mode, this one respects the commit size - TAR 4040621.994
266 --
267 /*
268 || Overview: Stores the score history given a table of records
269 ||
270 || Parameter:
271 || p_scoreID scoreEngineID used to score object optional
272 ||
273 || Return value:
274 ||
275 || Source Tables: None
276 ||
277 || Target Tables: IEX_SCORE_HISTORIES
278 ||
279 || Creation date: 11/02/2004
280 ||
281 || Major Modifications: when who what
282 ||
283 */
284 procedure storeScoreHistory ( p_score_id IN NUMBER default null,
285 p_objects_tbl IN IEX_SCORE_NEW_PVT.SCORE_OBJECTS_TBL,
286 p_scores_tbl IN IEX_SCORE_NEW_PVT.NEW_SCORES_TBL) ;
287
288 /*
289 || Overview: Stores the score history given a table of records
290 ||
291 || Parameter:
292 || p_scoreID scoreEngineID used to score object optional
293 ||
294 || Return value:
295 ||
296 || Source Tables: None
297 ||
298 || Target Tables: IEX_SCORE_HISTORIES
299 ||
300 || Creation date: 11/02/2004
301 ||
302 || Major Modifications: when who what
303 ||
304 */
305 procedure storeDelBuffers ( p_score_id IN NUMBER default null,
306 p_objects_tbl IN IEX_SCORE_NEW_PVT.SCORE_OBJECTS_TBL,
307 p_scores_tbl IN IEX_SCORE_NEW_PVT.NEW_SCORES_TBL,
308 p_bridge IN NUMBER default null) ;
309
310 /*
311 || Overview: Scores 1 item and returns the value
312 ||
313 || Parameter:
314 || p_scoreID scoreEngineID used to score object optional
315 ||
316 || Return value:
317 ||
318 || Source Tables: None
319 ||
320 || Target Tables: None
321 ||
322 || Creation date: 11/03/2004
323 ||
324 || Major Modifications: when who what
325 ||
326 */
327 function get1Score ( p_score_comp_tbl IN IEX_SCORE_NEW_PVT.SCORE_ENG_COMP_TBL, p_object_id IN NUMBER ) return NUMBER;
328
329 IEX_UNIVERSE_SIZE_ZERO_ERROR EXCEPTION;
330
331
332
333 END IEX_SCORE_NEW_PVT;
334