[Home] [Help]
PACKAGE BODY: APPS.OCM_ADD_DATA_POINTS
Source
1 Package Body OCM_ADD_DATA_POINTS AS
2 /* $Header: OCMGTDPB.pls 120.9 2008/03/26 16:09:48 mraymond ship $ */
3
4
5 PROCEDURE GenarateCaseFolderDetails (
6 p_case_folder_id IN NUMBER,
7 p_data_point_id IN NUMBER,
8 p_sequence_number IN NUMBER,
9 p_parent_data_point_id IN NUMBER,
10 p_parent_cf_detail_id IN NUMBER,
11 p_data_point_value IN VARCHAR2,
12 p_mode IN VARCHAR2,
13 p_score IN NUMBER default NULL,
14 p_included_in_checklist IN VARCHAR2 default NULL,
15 p_data_point_value_id IN NUMBER default NULL,
16 p_case_folder_detail_id IN OUT NOCOPY NUMBER,
17 p_errmsg OUT NOCOPY VARCHAR2,
18 p_resultout OUT NOCOPY VARCHAR2) IS
19 BEGIN
20 p_resultout := 0;
21 -- Run always in create mode.
22 -- Ignore the mode. because we are always deleting the additional data records
23 AR_CMGT_CONTROLS.POPULATE_CF_ADP_DETAILS (
24 p_case_folder_id => p_case_folder_id,
25 p_data_point_id => p_data_point_id,
26 p_sequence_number => p_sequence_number,
27 p_parent_data_point_id => p_parent_data_point_id,
28 p_parent_cf_detail_id => p_parent_cf_detail_id,
29 p_data_point_value => p_data_point_value,
30 p_score => p_score,
31 p_included_in_checklist => p_included_in_checklist,
32 p_data_point_value_id => p_data_point_value_id,
33 p_case_folder_detail_id => p_case_folder_detail_id,
34 p_errmsg => p_errmsg,
35 p_resultout => p_resultout );
36
37 IF p_resultout <> 0
38 THEN
39 p_errmsg := 'Error while calling AR_CMGT_CONTROLS.POPULATE_CF_ADP_DETAILS for Data Point Id: '||
40 p_data_point_id ||'Error :'|| p_errmsg;
41 return;
42 END IF;
43 /*ELSIF p_mode = 'REFRESH'
44 THEN
45 AR_CMGT_CONTROLS.UPDATE_CF_ADP_DETAILS (
46 p_case_folder_id => p_case_folder_id,
47 p_data_point_id => p_data_point_id,
48 p_sequence_number => p_sequence_number,
49 p_parent_data_point_id => p_parent_data_point_id,
50 p_parent_cf_detail_id => p_parent_cf_detail_id,
51 p_data_point_value => p_data_point_value,
52 p_score => p_score,
53 p_included_in_checklist => p_included_in_checklist,
54 p_case_folder_detail_id => p_case_folder_detail_id,
55 x_errmsg => p_errmsg,
56 x_resultout => p_resultout );
57
58 END IF; */
59 END;
60
61 PROCEDURE BuildExecuteSql (
62 p_package_name IN VARCHAR2,
63 p_function_name IN VARCHAR2,
64 p_result_value OUT NOCOPY VARCHAR2,
65 p_error_msg OUT NOCOPY VARCHAR2,
66 p_resultout OUT NOCOPY VARCHAR2 ) IS
67
68 l_sql_statement VARCHAR2(2000);
69
70 x_resultout VARCHAR2(1);
71 x_errormsg VARCHAR2(2000);
72 l_result_date_value DATE;
73
74
75 BEGIN
76 p_resultout := 0;
77
78 IF p_package_name IS NOT NULL AND
79 p_function_name IS NOT NULL
80 THEN
81 -- first clear the global pl/sql table
82 pg_ocm_dp_values_tbl.delete;
83
84 -- first check whether the data point
85
86 l_sql_statement := 'BEGIN :1 := '|| p_package_name ||'.'|| p_function_name ||
87 '( :2 , :3 ); END;';
88
89 IF pg_ocm_add_dp_param_rec.p_return_data_type = 'D' -- date format
90 THEN
91 EXECUTE IMMEDIATE l_sql_statement USING OUT l_result_date_value, OUT x_resultout, OUT x_errormsg ;
92 p_result_value := to_char(l_result_date_value); --, pg_ocm_add_dp_param_rec.p_return_date_format);
93 ELSE
94 EXECUTE IMMEDIATE l_sql_statement USING OUT p_result_value, OUT x_resultout, OUT x_errormsg ;
95 END IF;
96
97
98 IF x_resultout <> FND_API.G_RET_STS_SUCCESS
99 THEN
100 p_resultout := 1;
101 p_error_msg := 'Package '||p_package_name|| ' Function '||p_function_name ||
102 ' Failed. Error '|| x_errormsg;
103 return;
104 END IF;
105 ELSE
106 p_result_value := NULL;
107 END IF;
108
109 EXCEPTION
110 WHEN OTHERS THEN
111 p_resultout := 1;
112 p_error_msg := 'Sql Error While Calling Sql Function '||sqlerrm ||
113 'Package Name:'
114 || p_package_name ||' Function Name:'|| p_function_name;
115
116 END;
117
118
119 PROCEDURE GetChildDataPoints (
120 p_mode IN VARCHAR2,
121 p_parent_data_point_id IN NUMBER,
122 p_parent_cf_detail_id IN NUMBER,
123 p_data_point_value IN VARCHAR2,
124 p_error_msg OUT NOCOPY VARCHAR2,
125 p_resultout OUT NOCOPY VARCHAR2 ) IS
126
127 l_case_folder_id NUMBER;
128 l_data_point_id NUMBER;
129 l_parent_data_point_id NUMBER;
130
131
132 CURSOR getChildDataPointsC IS
133 SELECT data_point_id, package_name, function_name,
134 parent_data_point_id,
135 return_data_type, return_date_format
136 FROM ar_cmgt_data_points_vl
137 where enabled_flag = 'Y'
138 start with parent_data_point_id = p_parent_data_point_id
139 connect by prior data_point_id = parent_data_point_id
140 order by level;
141
142
143 CURSOR getAllParentValues IS
144 SELECT case_folder_detail_id, case_folder_id,
145 data_point_id, data_point_value, sequence_number,
146 data_point_value_id
147 FROM ar_cmgt_cf_dtls
148 WHERE case_folder_id = l_case_folder_id
149 AND data_point_id = l_parent_data_point_id;
150
151 l_return_value ar_cmgt_cf_dtls.data_point_value%type;
152 l_case_folder_detail_id NUMBER;
153 l_parent_cf_detail_id NUMBER;
154 l_mode VARCHAR2(30) := p_mode;
155
156 BEGIN
157 FOR getChildDataPointsRec IN getChildDataPointsC
158 LOOP
159
160 --dbms_output.put_line ( 'In Child DP '|| getChildDataPointsRec.data_point_id );
161 --dbms_output.put_line ( 'In Child Parent DP'|| getChildDataPointsRec.parent_data_point_id );
162 --dbms_output.put_line ( 'In Child Parent DP value'|| p_data_point_value );
163
164 -- Now for all values in case folder table
165 l_case_folder_id := pg_ocm_add_dp_param_rec.p_case_folder_id;
166 l_parent_data_point_id := getChildDataPointsRec.parent_data_point_id;
167
168 FOR getAllParentValuesRec IN getAllParentValues
169 LOOP
170 pg_ocm_add_dp_param_rec.p_data_point_value :=
171 getAllParentValuesRec.data_point_value;
172 pg_ocm_add_dp_param_rec.p_data_point_id := getChildDataPointsRec.data_point_id;
173 pg_ocm_add_dp_param_rec.p_parent_data_point_id :=
174 getChildDataPointsRec.parent_data_point_id;
175 pg_ocm_add_dp_param_rec.p_return_data_type :=
176 getChildDataPointsRec.return_data_type;
177 pg_ocm_add_dp_param_rec.p_return_date_format :=
178 getChildDataPointsRec.return_date_format;
179 pg_ocm_add_dp_param_rec.p_data_point_value_id :=
180 getAllParentValuesRec.data_point_value_id;
181 l_case_folder_detail_id := null;
182
183 BuildExecuteSql (
184 p_package_name => getChildDataPointsRec.package_name,
185 p_function_name => getChildDataPointsRec.function_name,
186 p_result_value => l_return_value,
187 p_error_msg => p_error_msg,
188 p_resultout => p_resultout ) ;
189
190 IF p_resultout <> 0
191 THEN
192 return;
193 END IF;
194
195 pg_ocm_add_dp_param_rec.p_data_point_value := l_return_value;
196
197 IF l_return_value IS NOT NULL
198 THEN
199 GenarateCaseFolderDetails (
200 p_case_folder_id => pg_ocm_add_dp_param_rec.p_case_folder_id,
201 p_data_point_id => getChildDataPointsRec.data_point_id,
202 p_sequence_number => 1,
203 p_parent_data_point_id => getChildDataPointsRec.parent_data_point_id,
204 p_parent_cf_detail_id => getAllParentValuesRec.case_folder_detail_id,
205 p_data_point_value => l_return_value,
206 p_mode => l_mode,
207 p_score => NULL,
208 p_included_in_checklist => 'N',
209 p_data_point_value_id => NULL,
210 p_case_folder_detail_id => l_case_folder_detail_id,
211 p_errmsg => p_error_msg,
212 p_resultout => p_resultout );
213
214 IF p_resultout <> 0
215 THEN
216 return;
217 END IF;
218
219 ELSIF l_return_value IS NULL
220 THEN
221 -- check whether the global pl/sql table is populated or not
222 IF pg_ocm_dp_values_tbl.count = 0
223 THEN
224 GenarateCaseFolderDetails (
225 p_case_folder_id => pg_ocm_add_dp_param_rec.p_case_folder_id,
226 p_data_point_id => getChildDataPointsRec.data_point_id,
227 p_sequence_number => 1,
228 p_parent_data_point_id => getChildDataPointsRec.parent_data_point_id,
229 p_parent_cf_detail_id => getAllParentValuesRec.case_folder_detail_id,
230 p_data_point_value => l_return_value,
231 p_mode => p_mode,
232 p_score => NULL,
233 p_included_in_checklist => 'N',
234 p_data_point_value_id => NULL,
235 p_case_folder_detail_id => l_case_folder_detail_id,
236 p_errmsg => p_error_msg,
237 p_resultout => p_resultout );
238
239 IF p_resultout <> 0
240 THEN
241 return;
242 END IF;
243
244 ELSIF pg_ocm_dp_values_tbl.count > 0
245 THEN
246 -- first insert all the values to table
247 FOR i in 1 .. pg_ocm_dp_values_tbl.count
248 LOOP
249 GenarateCaseFolderDetails (
250 p_case_folder_id => pg_ocm_add_dp_param_rec.p_case_folder_id,
251 p_data_point_id => getChildDataPointsRec.data_point_id,
252 p_sequence_number => pg_ocm_dp_values_tbl(i).p_sequence_number,
253 p_parent_data_point_id => getChildDataPointsRec.parent_data_point_id,
254 p_parent_cf_detail_id => getAllParentValuesRec.case_folder_detail_id,
255 p_data_point_value => pg_ocm_dp_values_tbl(i).p_data_point_value,
256 p_mode => p_mode,
257 p_score => NULL,
258 p_included_in_checklist => 'N',
259 p_data_point_value_id => pg_ocm_dp_values_tbl(i).p_data_point_value_id,
260 p_case_folder_detail_id => l_case_folder_detail_id,
261 p_errmsg => p_error_msg,
262 p_resultout => p_resultout );
263
264 IF p_resultout <> 0
265 THEN
266 return;
267 END IF;
268 END LOOP;
269 END IF;
270 END IF;
271
272 END LOOP; -- end of getAllParentValuesRec
273 END LOOP;
274 END;
275
276
277 PROCEDURE GetParentDataPoints (
278 p_mode IN VARCHAR2,
279 p_error_msg OUT NOCOPY VARCHAR2,
280 p_resultout OUT NOCOPY VARCHAR2 ) IS
281
282
283 l_case_folder_id NUMBER;
284 l_data_point_id NUMBER;
285 CURSOR getParentDataPoints IS
286 SELECT data_point_id,package_name, function_name, scorable_flag,
287 application_id, return_data_type, return_date_format
288 FROM ar_cmgt_data_points_vl
289 where enabled_flag = 'Y'
290 and data_point_category = 'ADDITIONAL'
291 and parent_data_point_id IS NULL
292 and ( application_id = pg_ocm_add_dp_param_rec.p_SOURCE_RESP_APPLN_ID
293 OR application_id = 222 );
294
295 CURSOR getAllValues IS
296 SELECT case_folder_detail_id, case_folder_id,
297 data_point_id,
298 data_point_value, sequence_number
299 FROM ar_cmgt_cf_dtls
300 WHERE case_folder_id = l_case_folder_id
301 AND data_point_id = l_data_point_id;
302
303 l_return_value ar_cmgt_cf_dtls.data_point_value%type;
304 l_sequence_num NUMBER := 1;
305 l_case_folder_detail_id NUMBER;
306 BEGIN
307 p_resultout := 0;
308
309 FOR getParentDataPointsRec IN getParentDataPoints
310 LOOP
311 pg_ocm_add_dp_param_rec.p_data_point_application_id :=
312 getParentDataPointsRec.application_id;
313 pg_ocm_add_dp_param_rec.p_data_point_id :=
314 getParentDataPointsRec.data_point_id;
315 pg_ocm_add_dp_param_rec.p_return_data_type :=
316 getParentDataPointsRec.return_data_type;
317 pg_ocm_add_dp_param_rec.p_return_date_format :=
318 getParentDataPointsRec.return_date_format;
319 pg_ocm_add_dp_param_rec.p_data_point_value := NULL;
320 l_case_folder_detail_id := null;
321 pg_ocm_dp_values_tbl.delete;
322
323 BuildExecuteSql (
324 p_package_name => getParentDataPointsRec.package_name,
325 p_function_name => getParentDataPointsRec.function_name,
326 p_result_value => l_return_value,
327 p_error_msg => p_error_msg,
328 p_resultout => p_resultout );
329
330 IF p_resultout <> 0
331 THEN
335 pg_ocm_add_dp_param_rec.p_data_point_value := l_return_value;
332 return;
333 END IF;
334
336
337 IF l_return_value IS NOT NULL
338 THEN
339 GenarateCaseFolderDetails (
340 p_case_folder_id=>pg_ocm_add_dp_param_rec.p_case_folder_id,
341 p_data_point_id=>getParentDataPointsRec.data_point_id,
342 p_sequence_number => 1,
343 p_parent_data_point_id => NULL,
344 p_parent_cf_detail_id => NULL,
345 p_data_point_value => l_return_value,
346 p_mode => p_mode,
347 p_score => NULL,
348 p_included_in_checklist => 'N',
349 p_data_point_value_id => NULL,
350 p_case_folder_detail_id => l_case_folder_detail_id,
351 p_errmsg => p_error_msg,
352 p_resultout => p_resultout );
353
354 IF p_resultout <> 0
355 THEN
356 return;
357 END IF;
358
359 ELSIF l_return_value IS NULL
360 THEN
361 -- check whether the global pl/sql table is populated or not
362 IF pg_ocm_dp_values_tbl.count = 0
363 THEN
364 GenarateCaseFolderDetails (
365 p_case_folder_id => pg_ocm_add_dp_param_rec.p_case_folder_id,
366 p_data_point_id =>
367 getParentDataPointsRec.data_point_id,
368 p_sequence_number => 1,
369 p_parent_data_point_id => NULL,
370 p_parent_cf_detail_id => NULL,
371 p_data_point_value => l_return_value,
372 p_mode => p_mode,
373 p_score => NULL,
374 p_included_in_checklist => 'N',
375 p_data_point_value_id => NULL,
376 p_case_folder_detail_id => l_case_folder_detail_id,
377 p_errmsg => p_error_msg,
378 p_resultout => p_resultout );
379
380 IF p_resultout <> 0
381 THEN
382 return;
383 END IF;
384 ELSIF pg_ocm_dp_values_tbl.count > 0
385 THEN
386 -- first insert all the values to table
387 FOR i in 1 .. pg_ocm_dp_values_tbl.count
388 LOOP
389 GenarateCaseFolderDetails (
390 p_case_folder_id => pg_ocm_add_dp_param_rec.p_case_folder_id,
391 p_data_point_id => getParentDataPointsRec.data_point_id,
392 p_sequence_number => pg_ocm_dp_values_tbl(i).p_sequence_number,
393 p_parent_data_point_id => NULL,
394 p_parent_cf_detail_id => NULL,
395 p_data_point_value => pg_ocm_dp_values_tbl(i).p_data_point_value,
396 p_mode => p_mode,
397 p_score => NULL,
398 p_included_in_checklist => 'N',
399 p_data_point_value_id => pg_ocm_dp_values_tbl(i).p_data_point_value_id,
400 p_case_folder_detail_id => l_case_folder_detail_id,
401 p_errmsg => p_error_msg,
402 p_resultout => p_resultout );
403
404 IF p_resultout <> 0
405 THEN
406 return;
407 END IF;
408 END LOOP;
409
410 -- now for each value see any child record exists and
411 -- keep continue
412 l_case_folder_id := pg_ocm_add_dp_param_rec.p_case_folder_id;
413 l_data_point_id := getParentDataPointsRec.data_point_id;
414
415 END IF;
416
417 END IF;
418
419 GetChildDataPoints (
420 p_mode => p_mode,
421 p_parent_data_point_id => getParentDataPointsRec.data_point_id,
422 p_parent_cf_detail_id => NULL, --getAllValuesRec.case_folder_detail_id,
423 p_data_point_value => NULL, --getAllValuesRec.data_point_value,
424 p_error_msg => p_error_msg,
425 p_resultout => p_resultout);
426
427 IF p_resultout <> 0
428 THEN
429 return;
430 END IF;
431 END LOOP;
432
433 END;
434
435 PROCEDURE GetAdditionalDataPoints (
436 p_credit_request_id IN NUMBER,
437 p_case_folder_id IN NUMBER,
438 p_mode IN VARCHAR2 DEFAULT 'CREATE',
439 p_error_msg OUT NOCOPY VARCHAR2,
440 p_resultout OUT NOCOPY VARCHAR2 ) IS
441
442 CURSOR creditRequestC IS
443 SELECT *
444 FROM ar_cmgt_credit_requests
445 WHERE credit_request_id = p_credit_request_id;
446
447 l_exchange_rate_type ar_cmgt_setup_options.default_exchange_rate_type%type;
448
449 BEGIN
450 p_resultout := 0;
451
452 FOR creditRequestRec IN creditRequestC
453 LOOP
454 pg_ocm_add_dp_param_rec.p_CASE_FOLDER_ID := p_case_folder_id;
455 pg_ocm_add_dp_param_rec.p_CREDIT_REQUEST_ID := p_CREDIT_REQUEST_ID;
456 pg_ocm_add_dp_param_rec.p_APPLICATION_NUMBER := creditRequestRec.APPLICATION_NUMBER;
457 pg_ocm_add_dp_param_rec.p_TRX_CURRENCY := creditRequestRec.TRX_CURRENCY;
458 pg_ocm_add_dp_param_rec.p_LIMIT_CURRENCY := creditRequestRec.LIMIT_CURRENCY;
462 pg_ocm_add_dp_param_rec.p_REQUESTOR_ID := creditRequestRec.REQUESTOR_ID;
459 pg_ocm_add_dp_param_rec.p_PARTY_ID := creditRequestRec.PARTY_ID;
460 pg_ocm_add_dp_param_rec.p_CUST_ACCOUNT_ID := creditRequestRec.CUST_ACCOUNT_ID;
461 pg_ocm_add_dp_param_rec.p_SITE_USE_ID := creditRequestRec.SITE_USE_ID;
463 pg_ocm_add_dp_param_rec.p_CREDIT_ANALYST_ID := creditRequestRec.CREDIT_ANALYST_ID;
464 pg_ocm_add_dp_param_rec.p_CHECK_LIST_ID := creditRequestRec.CHECK_LIST_ID;
465 pg_ocm_add_dp_param_rec.p_SCORE_MODEL_ID := creditRequestRec.SCORE_MODEL_ID;
469 pg_ocm_add_dp_param_rec.p_SOURCE_NAME := creditRequestRec.SOURCE_NAME;
466 pg_ocm_add_dp_param_rec.p_REVIEW_TYPE := creditRequestRec.REVIEW_TYPE;
467 pg_ocm_add_dp_param_rec.p_CREDIT_CLASSIFICATION := creditRequestRec.CREDIT_CLASSIFICATION;
468 pg_ocm_add_dp_param_rec.p_CREDIT_TYPE := creditRequestRec.CREDIT_TYPE;
470 pg_ocm_add_dp_param_rec.p_SOURCE_USER_ID := creditRequestRec.SOURCE_USER_ID;
471 pg_ocm_add_dp_param_rec.p_SOURCE_RESP_ID := creditRequestRec.SOURCE_RESP_ID;
472 pg_ocm_add_dp_param_rec.p_SOURCE_RESP_APPLN_ID := creditRequestRec.SOURCE_RESP_APPLN_ID;
473 pg_ocm_add_dp_param_rec.p_SOURCE_SECURITY_GROUP_ID := creditRequestRec.SOURCE_SECURITY_GROUP_ID;
474 pg_ocm_add_dp_param_rec.p_SOURCE_ORG_ID := creditRequestRec.SOURCE_ORG_ID;
475 pg_ocm_add_dp_param_rec.p_SOURCE_COLUMN1 := creditRequestRec.SOURCE_COLUMN1;
476 pg_ocm_add_dp_param_rec.p_SOURCE_COLUMN2 := creditRequestRec.SOURCE_COLUMN2;
477 pg_ocm_add_dp_param_rec.p_SOURCE_COLUMN3 := creditRequestRec.SOURCE_COLUMN3;
478 END LOOP;
479 BEGIN
480 SELECT default_exchange_rate_type
481 INTO l_exchange_rate_type
482 FROM ar_cmgt_setup_options;
483
484 pg_ocm_add_dp_param_rec.p_exchange_rate_type := l_exchange_rate_type;
485
486 EXCEPTION
487 WHEN OTHERS THEN
488 p_resultout := 1;
489 p_error_msg := 'Error while getting Exchange Rate '|| sqlerrm;
490 END;
491
492 -- First check the mode. If the program runs on Refresh mode
493 -- then first delete all the muliple records in case_folder details
494 -- table. This logic is required because there is no way we will identify a
495 -- unique record for updation.
496 IF p_mode = 'REFRESH' OR p_mode = 'CREATE'
497 THEN
498 BEGIN
499 DELETE FROM ar_cmgt_cf_dtls
500 WHERE case_folder_id = p_case_folder_id
501 AND data_point_id >= 20000;
502 EXCEPTION
503 WHEN OTHERS THEN
504 p_resultout := 1;
505 p_error_msg := 'SQL Error while deleting in OCM_ADD_DATA_POINTS '|| sqlerrm;
506 END;
507 END IF;
508
509 IF p_resultout <> 0
510 THEN
511 return;
512 END IF;
513
514 GetParentDataPoints (
515 p_mode => p_mode,
516 p_error_msg => p_error_msg,
517 p_resultout => p_resultout);
518
519 IF p_resultout <> 0
520 THEN
521 return;
522 END IF;
523
524 EXCEPTION
525 WHEN OTHERS THEN
526 p_resultout := 1;
527 p_error_msg := 'SQL Error in OCM_ADD_DATA_POINTS '|| sqlerrm;
528
529 END;
530
531 END OCM_ADD_DATA_POINTS;