[Home] [Help]
PACKAGE BODY: APPS.OCM_ADD_DATA_POINTS
Source
1 Package Body OCM_ADD_DATA_POINTS AS
2 /* $Header: OCMGTDPB.pls 120.15 2012/05/18 11:55:09 kgnanase ship $ */
3
4 pg_wf_debug VARCHAR2(1) := ar_cmgt_util.get_wf_debug_flag;
5
6 PROCEDURE GenarateCaseFolderDetails (
7 p_case_folder_id IN NUMBER,
8 p_data_point_id IN NUMBER,
9 p_sequence_number IN NUMBER,
10 p_parent_data_point_id IN NUMBER,
11 p_parent_cf_detail_id IN NUMBER,
12 p_data_point_value IN VARCHAR2,
13 p_mode IN VARCHAR2,
14 p_score IN NUMBER default NULL,
15 p_included_in_checklist IN VARCHAR2 default NULL,
16 p_data_point_value_id IN NUMBER default NULL,
17 p_case_folder_detail_id IN OUT NOCOPY NUMBER,
18 p_errmsg OUT NOCOPY VARCHAR2,
19 p_resultout OUT NOCOPY VARCHAR2) IS
20 BEGIN
21 p_resultout := 0;
22 -- Run always in create mode.
23 -- Ignore the mode. because we are always deleting the additional data records
24 AR_CMGT_CONTROLS.POPULATE_CF_ADP_DETAILS (
25 p_case_folder_id => p_case_folder_id,
26 p_data_point_id => p_data_point_id,
27 p_sequence_number => p_sequence_number,
28 p_parent_data_point_id => p_parent_data_point_id,
29 p_parent_cf_detail_id => p_parent_cf_detail_id,
30 p_data_point_value => p_data_point_value,
31 p_score => p_score,
32 p_included_in_checklist => p_included_in_checklist,
33 p_data_point_value_id => p_data_point_value_id,
34 p_case_folder_detail_id => p_case_folder_detail_id,
35 p_errmsg => p_errmsg,
36 p_resultout => p_resultout );
37
38 IF p_resultout <> 0
39 THEN
40 p_errmsg := 'Error while calling AR_CMGT_CONTROLS.POPULATE_CF_ADP_DETAILS for Data Point Id: '||
41 p_data_point_id ||'Error :'|| p_errmsg;
42 return;
43 END IF;
44 /*ELSIF p_mode = 'REFRESH'
45 THEN
46 AR_CMGT_CONTROLS.UPDATE_CF_ADP_DETAILS (
47 p_case_folder_id => p_case_folder_id,
48 p_data_point_id => p_data_point_id,
49 p_sequence_number => p_sequence_number,
50 p_parent_data_point_id => p_parent_data_point_id,
51 p_parent_cf_detail_id => p_parent_cf_detail_id,
52 p_data_point_value => p_data_point_value,
53 p_score => p_score,
54 p_included_in_checklist => p_included_in_checklist,
55 p_case_folder_detail_id => p_case_folder_detail_id,
56 x_errmsg => p_errmsg,
57 x_resultout => p_resultout );
58
59 END IF; */
60 END;
61
62 PROCEDURE BuildExecuteSql (
63 p_package_name IN VARCHAR2,
64 p_function_name IN VARCHAR2,
65 p_result_value OUT NOCOPY VARCHAR2,
66 p_error_msg OUT NOCOPY VARCHAR2,
67 p_resultout OUT NOCOPY VARCHAR2 ) IS
68
69 l_sql_statement VARCHAR2(2000);
70
71 x_resultout VARCHAR2(1);
72 x_errormsg VARCHAR2(2000);
73 l_result_date_value DATE;
74
75
76 BEGIN
77 p_resultout := 0;
78
79 IF p_package_name IS NOT NULL AND
80 p_function_name IS NOT NULL
81 THEN
82 IF pg_wf_debug = 'Y'
83 THEN
84 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,'before call:');
85 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,'p_data_point_value = ' ||
86 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value);
87 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,'p_data_point_value_id = ' ||
88 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
89 END IF;
90
91 -- first clear the global pl/sql table
92 pg_ocm_dp_values_tbl.delete;
93
94 -- first check whether the data point
95
96 l_sql_statement := 'BEGIN :1 := '|| p_package_name ||'.'|| p_function_name ||
97 '( :2 , :3 ); END;';
98
99 IF pg_ocm_add_dp_param_rec.p_return_data_type = 'D' -- date format
100 THEN
101 EXECUTE IMMEDIATE l_sql_statement USING OUT l_result_date_value, OUT x_resultout, OUT x_errormsg ;
102 p_result_value := to_char(l_result_date_value); --, pg_ocm_add_dp_param_rec.p_return_date_format);
103 ELSE
104 EXECUTE IMMEDIATE l_sql_statement USING OUT p_result_value, OUT x_resultout, OUT x_errormsg ;
105 END IF;
106
107
108 IF x_resultout <> FND_API.G_RET_STS_SUCCESS
109 THEN
110 p_resultout := 1;
111 p_error_msg := 'Package '||p_package_name|| ' Function '||p_function_name ||
112 ' Failed. Error '|| x_errormsg;
113 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id, p_error_msg);
114 return;
115 END IF;
116 ELSE
117 p_result_value := NULL;
118 END IF;
119
120 EXCEPTION
121 WHEN OTHERS THEN
122 p_resultout := 1;
123 p_error_msg := 'Sql Error While Calling Sql Function '||sqlerrm ||
124 'Package Name:'
125 || p_package_name ||' Function Name:'|| p_function_name;
126 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id, p_error_msg);
127 END;
128
129
130 PROCEDURE GetChildDataPoints (
131 p_mode IN VARCHAR2,
132 p_parent_data_point_id IN NUMBER,
133 p_parent_cf_detail_id IN NUMBER,
134 p_data_point_value IN VARCHAR2,
135 p_error_msg OUT NOCOPY VARCHAR2,
136 p_resultout OUT NOCOPY VARCHAR2 ) IS
137
138 l_case_folder_id NUMBER;
139 l_data_point_id NUMBER;
140 l_parent_data_point_id NUMBER;
141
142
143 CURSOR getChildDataPointsC IS
144 SELECT data_point_id, package_name, function_name,
145 parent_data_point_id,
146 return_data_type, return_date_format
147 FROM ar_cmgt_data_points_vl
148 where enabled_flag = 'Y'
149 start with parent_data_point_id = p_parent_data_point_id
150 connect by prior data_point_id = parent_data_point_id
151 order by level;
152
153 CURSOR getAllParentValues IS
154 SELECT case_folder_detail_id, case_folder_id,
155 data_point_id, data_point_value, sequence_number,
156 data_point_value_id
157 FROM ar_cmgt_cf_dtls
158 WHERE case_folder_id = l_case_folder_id
159 AND data_point_id = l_parent_data_point_id;
160
161 /* Added for bug 9899841 */
162 CURSOR isDataPointExist(p_data_point_id IN NUMBER) IS
163 SELECT 'Y'
164 FROM ar_cmgt_cf_dtls
165 WHERE case_folder_id = l_case_folder_id
166 AND data_point_id = p_data_point_id;
167
168 l_return_value ar_cmgt_cf_dtls.data_point_value%type;
169 l_case_folder_detail_id NUMBER;
170 l_parent_cf_detail_id NUMBER;
171 l_mode VARCHAR2(30) := p_mode;
172 l_data_point_exist VARCHAR2(1) := 'N'; /* Added for bug 9899841 */
173
174 BEGIN
175 IF pg_wf_debug = 'Y'
176 THEN
177 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,
178 'ocm_add_data_points_pkg.getchilddatapoints()+');
179 END IF;
180
181 FOR getChildDataPointsRec IN getChildDataPointsC
182 LOOP
183
184
185 -- Now for all values in case folder table
186 l_case_folder_id := pg_ocm_add_dp_param_rec.p_case_folder_id;
187 l_parent_data_point_id := getChildDataPointsRec.parent_data_point_id;
188
189 FOR getAllParentValuesRec IN getAllParentValues
190 LOOP
191 pg_ocm_add_dp_param_rec.p_data_point_value :=
192 getAllParentValuesRec.data_point_value;
193 pg_ocm_add_dp_param_rec.p_data_point_id := getChildDataPointsRec.data_point_id;
194 pg_ocm_add_dp_param_rec.p_parent_data_point_id :=
195 getChildDataPointsRec.parent_data_point_id;
196 pg_ocm_add_dp_param_rec.p_return_data_type :=
197 getChildDataPointsRec.return_data_type;
198 pg_ocm_add_dp_param_rec.p_return_date_format :=
199 getChildDataPointsRec.return_date_format;
200 pg_ocm_add_dp_param_rec.p_data_point_value_id :=
201 getAllParentValuesRec.data_point_value_id;
202 l_case_folder_detail_id := null;
203 IF pg_wf_debug = 'Y'
204 THEN
205 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,
206 'par_dp_id=' || getChildDataPointsRec.parent_data_point_id ||
207 ' dp_id=' || getChildDataPointsRec.data_point_id ||
208 ' pkg.fun()=' || getChildDataPointsRec.package_name ||
209 '.' || getChildDataPointsRec.function_name);
210 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,'before call:');
211 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,'p_data_point_value = ' ||
212 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value);
213 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,'p_data_point_value_id = ' ||
214 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
215 END IF;
216
217 BuildExecuteSql (
218 p_package_name => getChildDataPointsRec.package_name,
219 p_function_name => getChildDataPointsRec.function_name,
220 p_result_value => l_return_value,
221 p_error_msg => p_error_msg,
222 p_resultout => p_resultout ) ;
223
224 IF pg_wf_debug = 'Y'
225 THEN
226 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,
227 ' return_value = ' || l_return_value || ' p_resultout = ' || p_resultout);
228 END IF;
229
230 IF p_resultout <> 0
231 THEN
232 return;
233 END IF;
234
235 pg_ocm_add_dp_param_rec.p_data_point_value := l_return_value;
236
237 IF l_return_value IS NOT NULL
238 THEN
239 GenarateCaseFolderDetails (
240 p_case_folder_id => pg_ocm_add_dp_param_rec.p_case_folder_id,
241 p_data_point_id => getChildDataPointsRec.data_point_id,
242 p_sequence_number => 1,
243 p_parent_data_point_id => getChildDataPointsRec.parent_data_point_id,
244 p_parent_cf_detail_id => getAllParentValuesRec.case_folder_detail_id,
245 p_data_point_value => l_return_value,
246 p_mode => l_mode,
247 p_score => NULL,
248 p_included_in_checklist => 'N',
249 p_data_point_value_id => NULL,
250 p_case_folder_detail_id => l_case_folder_detail_id,
251 p_errmsg => p_error_msg,
252 p_resultout => p_resultout );
253
254 IF p_resultout <> 0
255 THEN
256 return;
257 END IF;
258
259 ELSIF l_return_value IS NULL
260 THEN
261 -- check whether the global pl/sql table is populated or not
262 IF pg_ocm_dp_values_tbl.count = 0
263 THEN
264
265 /* Added for bug 9899841 - Start*/
266 OPEN isDataPointExist(p_data_point_id => getChildDataPointsRec.data_point_id);
267 FETCH isDataPointExist INTO l_data_point_exist;
268 CLOSE isDataPointExist;
269
270 IF (l_data_point_exist = 'N') THEN
271 /* Added for bug 9899841 - End*/
272 GenarateCaseFolderDetails (
273 p_case_folder_id => pg_ocm_add_dp_param_rec.p_case_folder_id,
274 p_data_point_id => getChildDataPointsRec.data_point_id,
275 p_sequence_number => 1,
276 p_parent_data_point_id => getChildDataPointsRec.parent_data_point_id,
277 p_parent_cf_detail_id => getAllParentValuesRec.case_folder_detail_id,
278 p_data_point_value => l_return_value,
279 p_mode => p_mode,
280 p_score => NULL,
281 p_included_in_checklist => 'N',
282 p_data_point_value_id => NULL,
283 p_case_folder_detail_id => l_case_folder_detail_id,
284 p_errmsg => p_error_msg,
285 p_resultout => p_resultout );
286
287 IF p_resultout <> 0
288 THEN
289 return;
290 END IF;
291 END IF; /* Added for bug 9899841 */
292
293 ELSIF pg_ocm_dp_values_tbl.count > 0
294 THEN
295 -- first insert all the values to table
296 FOR i in 1 .. pg_ocm_dp_values_tbl.count
297 LOOP
298 IF pg_wf_debug = 'Y'
299 THEN
300 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,
301 ' return_value(' || i || ') = ' ||
302 pg_ocm_dp_values_tbl(i).p_data_point_value);
303 END IF;
304
305 GenarateCaseFolderDetails (
306 p_case_folder_id => pg_ocm_add_dp_param_rec.p_case_folder_id,
307 p_data_point_id => getChildDataPointsRec.data_point_id,
308 p_sequence_number => pg_ocm_dp_values_tbl(i).p_sequence_number,
309 p_parent_data_point_id => getChildDataPointsRec.parent_data_point_id,
310 p_parent_cf_detail_id => getAllParentValuesRec.case_folder_detail_id,
311 p_data_point_value => pg_ocm_dp_values_tbl(i).p_data_point_value,
312 p_mode => p_mode,
313 p_score => NULL,
314 p_included_in_checklist => 'N',
315 p_data_point_value_id => pg_ocm_dp_values_tbl(i).p_data_point_value_id,
316 p_case_folder_detail_id => l_case_folder_detail_id,
317 p_errmsg => p_error_msg,
318 p_resultout => p_resultout );
319 IF p_resultout <> 0
320 THEN
321 return;
322 END IF;
323 END LOOP;
324 END IF;
325 END IF;
326
327 IF pg_wf_debug = 'Y'
328 THEN
329 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,'after call:');
330 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,'p_data_point_value = ' ||
331 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value);
332 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,'p_data_point_value_id = ' ||
333 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
334 END IF;
335
336 END LOOP; -- end of getAllParentValuesRec
337 END LOOP;
338 IF pg_wf_debug = 'Y'
339 THEN
340 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,
341 'ocm_add_data_points_pkg.getchilddatapoints()-');
342 END IF;
343 END;
344
345 PROCEDURE GetParentDataPoints (
346 p_mode IN VARCHAR2,
347 p_error_msg OUT NOCOPY VARCHAR2,
348 p_resultout OUT NOCOPY VARCHAR2 ) IS
349
350
351 l_case_folder_id NUMBER;
352 l_data_point_id NUMBER;
353 CURSOR getParentDataPoints IS
354 SELECT data_point_id,package_name, function_name, scorable_flag,
355 application_id, return_data_type, return_date_format
356 FROM ar_cmgt_data_points_vl
357 where enabled_flag = 'Y'
358 and data_point_category = 'ADDITIONAL'
359 and parent_data_point_id IS NULL
360 and ( application_id = 206 -- Added for bug 10304146
361 OR application_id = pg_ocm_add_dp_param_rec.p_SOURCE_RESP_APPLN_ID
362 OR application_id = 222 );
363
364 CURSOR getAllValues IS
365 SELECT case_folder_detail_id, case_folder_id,
366 data_point_id,
367 data_point_value, sequence_number
368 FROM ar_cmgt_cf_dtls
369 WHERE case_folder_id = l_case_folder_id
370 AND data_point_id = l_data_point_id;
371
372 l_return_value ar_cmgt_cf_dtls.data_point_value%type;
373 l_sequence_num NUMBER := 1;
374 l_case_folder_detail_id NUMBER;
375 l_dp_exists VARCHAR2(1) := 'N';
376 BEGIN
377 IF pg_wf_debug = 'Y'
378 THEN
379 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,
380 'ocm_add_data_points_pkg.getparentdatapoints()+');
381 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,
382 ' p_mode = ' || p_mode);
383 END IF;
384
385
386 p_resultout := 0;
387
388 FOR getParentDataPointsRec IN getParentDataPoints
389 LOOP
390 pg_ocm_add_dp_param_rec.p_data_point_application_id :=
391 getParentDataPointsRec.application_id;
392 pg_ocm_add_dp_param_rec.p_data_point_id :=
393 getParentDataPointsRec.data_point_id;
394 pg_ocm_add_dp_param_rec.p_return_data_type :=
395 getParentDataPointsRec.return_data_type;
396 pg_ocm_add_dp_param_rec.p_return_date_format :=
397 getParentDataPointsRec.return_date_format;
398 pg_ocm_add_dp_param_rec.p_data_point_value := NULL;
399 l_case_folder_detail_id := null;
400 pg_ocm_dp_values_tbl.delete;
401
402 IF pg_wf_debug = 'Y'
403 THEN
404 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,
405 'app_id=' || getParentDataPointsRec.application_id ||
406 ' dp_id=' || getParentDataPointsRec.data_point_id ||
407 ' pkg.fun()=' || getParentDataPointsRec.package_name ||
408 '.' || getParentDataPointsRec.function_name);
409 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,'before call:');
410 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,'p_data_point_value = ' ||
411 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value);
412 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,'p_data_point_value_id = ' ||
413 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
414 END IF;
415
416 BuildExecuteSql (
417 p_package_name => getParentDataPointsRec.package_name,
418 p_function_name => getParentDataPointsRec.function_name,
419 p_result_value => l_return_value,
420 p_error_msg => p_error_msg,
421 p_resultout => p_resultout );
422
423 IF pg_wf_debug = 'Y'
424 THEN
425 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,
426 ' return_value = ' || l_return_value || ' p_resultout = ' || p_resultout);
427 END IF;
428
429 IF p_resultout <> 0
430 THEN
431 return;
432 END IF;
433
434 pg_ocm_add_dp_param_rec.p_data_point_value := l_return_value;
435
436 IF l_return_value IS NOT NULL
437 THEN
438 GenarateCaseFolderDetails (
439 p_case_folder_id => pg_ocm_add_dp_param_rec.p_case_folder_id,
440 p_data_point_id => getParentDataPointsRec.data_point_id,
441 p_sequence_number => 1,
442 p_parent_data_point_id => NULL,
443 p_parent_cf_detail_id => NULL,
444 p_data_point_value => l_return_value,
445 p_mode => p_mode,
446 p_score => NULL,
447 p_included_in_checklist => 'N',
448 p_data_point_value_id => NULL,
449 p_case_folder_detail_id => l_case_folder_detail_id,
450 p_errmsg => p_error_msg,
451 p_resultout => p_resultout );
452
453 IF p_resultout <> 0
454 THEN
455 return;
456 END IF;
457
458 ELSIF l_return_value IS NULL
459 THEN
460 -- check whether the global pl/sql table is populated or not
461 IF pg_ocm_dp_values_tbl.count = 0
462 THEN
463
464 /* 7416921 - This point executes if buildexecsql returns ok,
465 but the return_value is NULL (either a function that returns
466 null intentionally, or a non-function ADP)
467
468 In the case of a non-function ADP, we should only create
469 this record if it does not already exist */
470
471 IF p_mode = 'REFRESH'
472 THEN
473 IF getParentDataPointsRec.function_name IS NULL
474 THEN
475 BEGIN
476
477 SELECT 'Y'
478 INTO l_dp_exists
479 FROM ar_cmgt_cf_dtls cfd
480 WHERE cfd.case_folder_id =
481 pg_ocm_add_dp_param_rec.p_case_folder_id
482 AND cfd.data_point_id =
483 getParentDataPointsRec.data_point_id;
484
485 EXCEPTION
486 WHEN NO_DATA_FOUND THEN
487 l_dp_exists := 'N';
488 END;
489 END IF;
490 ELSE
491 l_dp_exists := 'N';
492 END IF;
493
494 IF pg_wf_debug = 'Y'
495 THEN
496 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,
497 ' l_dp_exists = ' || l_dp_exists);
498 END IF;
499
500 IF l_dp_exists = 'N'
501 THEN
502 GenarateCaseFolderDetails (
503 p_case_folder_id => pg_ocm_add_dp_param_rec.p_case_folder_id,
504 p_data_point_id => getParentDataPointsRec.data_point_id,
505 p_sequence_number => 1,
506 p_parent_data_point_id => NULL,
507 p_parent_cf_detail_id => NULL,
508 p_data_point_value => l_return_value,
509 p_mode => p_mode,
510 p_score => NULL,
511 p_included_in_checklist => 'N',
512 p_data_point_value_id => NULL,
513 p_case_folder_detail_id => l_case_folder_detail_id,
514 p_errmsg => p_error_msg,
515 p_resultout => p_resultout );
516
517 IF p_resultout <> 0
518 THEN
519 return;
520 END IF;
521 END IF; -- l_dp_exists
522 ELSIF pg_ocm_dp_values_tbl.count > 0
523 THEN
524 -- first insert all the values to table
525 FOR i in 1 .. pg_ocm_dp_values_tbl.count
526 LOOP
527
528 IF pg_wf_debug = 'Y'
529 THEN
530 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,
531 ' return_value(' || i || ') = ' ||
532 pg_ocm_dp_values_tbl(i).p_data_point_value);
533 END IF;
534
535 GenarateCaseFolderDetails (
536 p_case_folder_id => pg_ocm_add_dp_param_rec.p_case_folder_id,
537 p_data_point_id => getParentDataPointsRec.data_point_id,
538 p_sequence_number => pg_ocm_dp_values_tbl(i).p_sequence_number,
539 p_parent_data_point_id => NULL,
540 p_parent_cf_detail_id => NULL,
541 p_data_point_value => pg_ocm_dp_values_tbl(i).p_data_point_value,
542 p_mode => p_mode,
543 p_score => NULL,
544 p_included_in_checklist => 'N',
545 p_data_point_value_id => pg_ocm_dp_values_tbl(i).p_data_point_value_id,
546 p_case_folder_detail_id => l_case_folder_detail_id,
547 p_errmsg => p_error_msg,
548 p_resultout => p_resultout );
549
550 IF p_resultout <> 0
551 THEN
552 return;
553 END IF;
554 END LOOP;
555
556 -- now for each value see any child record exists and
557 -- keep continue
558 l_case_folder_id := pg_ocm_add_dp_param_rec.p_case_folder_id;
559 l_data_point_id := getParentDataPointsRec.data_point_id;
560
561 END IF;
562
563 END IF;
564
565 GetChildDataPoints (
566 p_mode => p_mode,
567 p_parent_data_point_id => getParentDataPointsRec.data_point_id,
568 p_parent_cf_detail_id => NULL,
569 p_data_point_value => NULL,
570 p_error_msg => p_error_msg,
571 p_resultout => p_resultout);
572
573 IF p_resultout <> 0
574 THEN
575 return;
576 END IF;
577
578 IF pg_wf_debug = 'Y'
579 THEN
580 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,'after call:');
581 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,
582 'p_data_point_value = ' ||
583 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value);
584 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,
585 'p_data_point_value_id = ' ||
586 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_value_id);
587 END IF;
588
589 END LOOP; /* get parents loop */
590
591 IF pg_wf_debug = 'Y'
592 THEN
593 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,
594 'ocm_add_data_points_pkg.getparentdatapoints()-');
595 END IF;
596 END;
597
598 PROCEDURE GetAdditionalDataPoints (
599 p_credit_request_id IN NUMBER,
600 p_case_folder_id IN NUMBER,
601 p_mode IN VARCHAR2 DEFAULT 'CREATE',
602 p_error_msg OUT NOCOPY VARCHAR2,
603 p_resultout OUT NOCOPY VARCHAR2 ) IS
604
605 CURSOR creditRequestC IS
606 SELECT *
607 FROM ar_cmgt_credit_requests
608 WHERE credit_request_id = p_credit_request_id;
609
610 l_exchange_rate_type ar_cmgt_setup_options.default_exchange_rate_type%type;
611
612 BEGIN
613 IF pg_wf_debug = 'Y'
614 THEN
615 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,
616 'ocm_add_data_points_pkg.getadditionaldatapoints()+');
617 END IF;
618
619 p_resultout := 0;
620
621 FOR creditRequestRec IN creditRequestC
622 LOOP
623 pg_ocm_add_dp_param_rec.p_CASE_FOLDER_ID := p_case_folder_id;
624 pg_ocm_add_dp_param_rec.p_CREDIT_REQUEST_ID := p_CREDIT_REQUEST_ID;
625 pg_ocm_add_dp_param_rec.p_APPLICATION_NUMBER := creditRequestRec.APPLICATION_NUMBER;
626 pg_ocm_add_dp_param_rec.p_TRX_CURRENCY := creditRequestRec.TRX_CURRENCY;
627 pg_ocm_add_dp_param_rec.p_LIMIT_CURRENCY := creditRequestRec.LIMIT_CURRENCY;
628 pg_ocm_add_dp_param_rec.p_PARTY_ID := creditRequestRec.PARTY_ID;
629 pg_ocm_add_dp_param_rec.p_CUST_ACCOUNT_ID := creditRequestRec.CUST_ACCOUNT_ID;
630 pg_ocm_add_dp_param_rec.p_SITE_USE_ID := creditRequestRec.SITE_USE_ID;
631 pg_ocm_add_dp_param_rec.p_REQUESTOR_ID := creditRequestRec.REQUESTOR_ID;
632 pg_ocm_add_dp_param_rec.p_CREDIT_ANALYST_ID := creditRequestRec.CREDIT_ANALYST_ID;
633 pg_ocm_add_dp_param_rec.p_CHECK_LIST_ID := creditRequestRec.CHECK_LIST_ID;
634 pg_ocm_add_dp_param_rec.p_SCORE_MODEL_ID := creditRequestRec.SCORE_MODEL_ID;
635 pg_ocm_add_dp_param_rec.p_REVIEW_TYPE := creditRequestRec.REVIEW_TYPE;
636 pg_ocm_add_dp_param_rec.p_CREDIT_CLASSIFICATION := creditRequestRec.CREDIT_CLASSIFICATION;
637 pg_ocm_add_dp_param_rec.p_CREDIT_TYPE := creditRequestRec.CREDIT_TYPE;
638 pg_ocm_add_dp_param_rec.p_SOURCE_NAME := creditRequestRec.SOURCE_NAME;
639 pg_ocm_add_dp_param_rec.p_SOURCE_USER_ID := creditRequestRec.SOURCE_USER_ID;
640 pg_ocm_add_dp_param_rec.p_SOURCE_RESP_ID := creditRequestRec.SOURCE_RESP_ID;
641 pg_ocm_add_dp_param_rec.p_SOURCE_RESP_APPLN_ID := creditRequestRec.SOURCE_RESP_APPLN_ID;
642 pg_ocm_add_dp_param_rec.p_SOURCE_SECURITY_GROUP_ID := creditRequestRec.SOURCE_SECURITY_GROUP_ID;
643 pg_ocm_add_dp_param_rec.p_SOURCE_ORG_ID := creditRequestRec.SOURCE_ORG_ID;
644 pg_ocm_add_dp_param_rec.p_SOURCE_COLUMN1 := creditRequestRec.SOURCE_COLUMN1;
645 pg_ocm_add_dp_param_rec.p_SOURCE_COLUMN2 := creditRequestRec.SOURCE_COLUMN2;
646 pg_ocm_add_dp_param_rec.p_SOURCE_COLUMN3 := creditRequestRec.SOURCE_COLUMN3;
647 END LOOP;
648
649 IF pg_wf_debug = 'Y'
650 THEN
651 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,
652 'content of pg_ocm_add_dp_param_rec');
653 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,'p_credit_request_id = ' ||
654 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_credit_request_id);
655 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,'p_case_folder_id = ' ||
656 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_case_folder_id);
657 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,'p_application_number = ' ||
658 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_application_number);
659 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,'p_source_column1 = ' ||
660 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column1);
661 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,'p_source_column2 = ' ||
662 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column2);
663 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,'p_source_column3 = ' ||
664 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_source_column3);
665 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,'p_data_point_application_id = ' ||
666 OCM_ADD_DATA_POINTS.pg_ocm_add_dp_param_rec.p_data_point_application_id);
667 END IF;
668
669 BEGIN
670 SELECT default_exchange_rate_type
671 INTO l_exchange_rate_type
672 FROM ar_cmgt_setup_options;
673
674 pg_ocm_add_dp_param_rec.p_exchange_rate_type := l_exchange_rate_type;
675
676 EXCEPTION
677 WHEN OTHERS THEN
678 p_resultout := 1;
679 p_error_msg := 'Error while getting Exchange Rate '|| sqlerrm;
680 END;
681
682 -- First check the mode. If the program runs on Refresh mode
683 -- then first delete all the muliple records in case_folder details
684 -- table. This logic is required because there is no way we will identify a
685 -- unique record for updation.
686 IF p_mode = 'REFRESH' OR p_mode = 'CREATE'
687 THEN
688 BEGIN
689 /* 7416921 - preserve non-function ADPs if
690 then are already visible from the checklist */
691 DELETE FROM ar_cmgt_cf_dtls cfd
692 WHERE cfd.case_folder_id = p_case_folder_id
693 AND cfd.data_point_id >= 20000
694 AND (cfd.included_in_checklist = 'N' OR
695 (cfd.included_in_checklist = 'Y' AND
696 NOT EXISTS
697 (SELECT 'populated NF/ADP'
698 FROM AR_CMGT_DATA_POINTS_B dp
699 WHERE dp.data_point_id = cfd.data_point_id
700 AND dp.function_name IS NULL)));
701 EXCEPTION
702 WHEN OTHERS THEN
703 p_resultout := 1;
704 p_error_msg :=
705 'SQL Error while deleting in OCM_ADD_DATA_POINTS '|| sqlerrm;
706 END;
707 END IF;
708
709 IF p_resultout <> 0
710 THEN
711 return;
712 END IF;
713
714 GetParentDataPoints (
715 p_mode => p_mode,
716 p_error_msg => p_error_msg,
717 p_resultout => p_resultout);
718
719 IF p_resultout <> 0
720 THEN
721 return;
722 END IF;
723
724 IF pg_wf_debug = 'Y'
725 THEN
726 ar_cmgt_util.wf_debug(pg_ocm_add_dp_param_rec.p_case_folder_id,
727 'ocm_add_data_points_pkg.getadditionaldatapoints()-');
728 END IF;
729
730 EXCEPTION
731 WHEN OTHERS THEN
732 p_resultout := 1;
733 p_error_msg := 'SQL Error in OCM_ADD_DATA_POINTS '|| sqlerrm;
734
735 END;
736
737 END OCM_ADD_DATA_POINTS;